INFO90002 A1 2023s2 © The University of Melbourne v1b
INFO90002 2023 Semester 1 - ASSIGNMENT 1
Weighting: 20% of your total assessment
Group Assessment: Groups of four (4) students from the same tutorial
Assignment deadline: Check Canvas
Assignment submission: Canvas
CASE STUDY: Day Surgery Medical Centre
Josh Schwartz is an experienced surgeon who decided to open his own day surgery centre. The goal
is to perform small non-invasive surgeries using modern equipment. Patients would be able to go
home on the same day. He plans to hire several surgeons, anaesthesiologists, nurses and
pharmacists. His business will need a well designed database to keep track of the centre operations.
He plans to buy or rent a suitable building where some rooms will be equipped as designated
operation theatres, recovery rooms with medical beds, a lounge for patients to be discharged with
comfortable armchairs and a room or rooms with ultrasound equipment. All these dedicated rooms
will be tracked with room numbers, names, and comments. E.g. room no 3, theatre 1, “standard
theatre”; room no 4, recovery, “connected to theatres 3 and 4, capacity - 6 beds”.
The centre will need medical equipment which will be allocated to rooms, e.g. blood pressure
monitors, oxygen balloons and pumps, hospital beds, surgical table beds, etc. Each item will have
item number, name, description and supplier details (supplier id, company name, phone number
and email). Some suppliers will be marked as preferred suppliers.
For medical staff there will be a need to store name (first and last), email, mobile, and qualifications.
Every staff member will be allocated a unique ID. A medical staff member will typically have several
qualifications, completion of which must be recorded including completion year, institution that
awarded the qualification and finally a tick that the original was sighted, scanned and stored by a
person in charge of this process (one tick for all three steps). For example, all surgeons must have
Bachelor and/or Doctor of Medicine, registration with the Medical Board of Australia, registration as
a Fellow of the Royal Australasian College of Surgeons (FRACS). Anaesthesiologists will need to have
a Bachelor of Medicine and membership of the Australian and New Zealand College of Anaesthetists
(ANZCA). Similarly there are various levels of qualifications and registrations for nurses who may be
qualified to admit and discharge patients, and/or assist during surgery, and/or monitor patients in
recovery based on qualifications and completed training and registrations. All nurses must have a
Registration to practice as a Registered Nurse (RN) from the Nursing and Midwifery Board of
Australia, however nurses in the last year of Bachelor of Nursing can still be hired to assist with
admission and discharge of patients.
Patient details to be stored are name (first and last), date of birth, gender, address, mobile, and
email. For every patient there will be a record of allergies (separated by comma, e.g. eggs, gluten,
sulphur) and additional relevant comments (unstructured). Every new patient gets a patient ID.
For each surgical procedure the centre offers, there is procedure ID, name, approximate duration,
cost to a patient and scheduling preference (e.g. “morning only” or “no later than 2 pm”). Each
procedure requires several staff roles to be allocated (e.g. a surgeon, a nurse, an anaesthesiologist
for procedures requiring general anaesthetic). For some procedures a medical role requires more
INFO90002 A1 2023s2 © The University of Melbourne v1b
than one person to be allocated (e.g. a procedure may require 2 nurses). Not every role is needed in
a procedure (e.g. procedures performed under local anaesthetic do not need an anaesthesiologist).
Every patient procedure is scheduled for a particular day and time and in a particular operation
theatre. A patient procedure will have a particular staff member associated with each required role.
The centre will have a supply of medication and the pharmacists will be in charge of keeping track
and re-ordering medical supplies. For each medical supply item there will be itemID, name, brand
and comments. For medication with narcotic ingredients, there will be a note to keep locked. When
items quantities are running low, the pharmacists will create item orders (separate for each
supplier). Suppliers of medication must be registered with a licence to carry on a pharmacy business
so for medication suppliers the centre will record company code and name, phone, email, their
license registration number and their pharmacist name who is the primary contact at the
pharmaceutical supplier. For each order there will be internal order number, date, approver name,
supplier this order is going to, ordered items and their quantities. When each order is fulfilled (fully
or partially), the supplier will send a delivery slip stating delivery slip number, date, order being
fulfilled, and which items with the corresponding quantities and batch numbers where applicable
are included in this delivery. So for each order there may be more than one delivery if fulfilment
happens in instalments (based on stock availability on supplier’s side). When delivery arrives, a
record of delivery is created with all delivery note details. For each delivered/received medication
item there will be a record of batch number1, delivery date and quantity in stock per batch.
Every procedure has a standard list of medications and non-drug pharmaceuticals to be prepared.
Non-drugs could be different types of bandages, dressings and sterile surgical threads for closing
cuts. This list is a guidance for the nurse preparing the operation theatre for the procedure and for
the pharmacist to know what will need to be dispensed. The actual patient procedure may have
deviations (e.g. additional medication for patients with allergies).
For each procedure local or general anaesthetic is dispensed as well as other medication and non-
drugs. Each dispensing procedure is recorded, including which drugs (with batch numbers) and non-
drugs were dispensed and in what quantities, as well as requesting staff and dispensing staff.
At the end of the patient procedure the surgeon adds notes, especially if additional medication was
required or complications happened.
For legal reasons each patient procedure is video recorded. Each recording has an ID, date (the same
as patient procedure scheduled date), start time (which is not the same as patient procedure
schedule time), and stop time.
After the procedure nurses look after each patient in recovery rooms, making a record of blood
pressure and administered medication (e.g. painkillers, even if it’s simple paracetamol). Blood
pressure may be measured several times after the procedure, especially for the patients who had
anaesthesiology (their blood pressure is taken when they wake up – it is expected to be low, then
possibly at 30 minutes intervals, until the results are normal; this usually means at least 2 readings).
1 Batch numbers are used by manufacturers of drugs in case a problem is discovered and needs to be tracked
to manufacturing stage. Batch numbers help distinguish between otherwise almost identical products. Product
Batch Numbers are printed on labels added to boxes or bottle lids or sometimes on the box or can itself. A
typical batch number includes digits referring to the date of manufacturing, e.g. 036521 028 where 028 is the
day number in the year of production (28th day of the year is 28 January). Other coded values could include the
last digit of the year of manufacture and the production line.
INFO90002 A1 2023s2 © The University of Melbourne v1b
Every record must have relevant details. For blood pressure measuring2, the apparatus shows 3
important parameters: top (systolic) number, bottom (diastolic) number and concentration of
oxygen in blood. In addition, nurses record date and time when the measurements were taken.
Similarly for medication, nurses record when and in what quantities medication was administered.
At discharge time, an invoice to the patient is generated. The invoice contains id, date, procedure
name and cost, anaesthesiologist services costs, possibly other charges and total amount due. The
medical centre accepts payments by card only. The payment record of the invoice stores date and
time of payment, card number, expiry date, CVV and amount paid. It is possible to pay the invoice in
instalments or by 2 (or more) different cards.
If you are not sure not sure how to interpret any part of the case study, do not guess, post a
question on Ed Discussions.
TASK: Your group needs to provide:
I) The record of group members contribution (see Appendix).
II) A physical Entity Relationship model using Crows Foot notation suitable for a MySQL relational
database version 8.0 or higher. Export an image of the ER model (you can export as pdf).
III) Brief explanatory notes (optional). For example, if you are replacing a very complex PFK with a
surrogate key, you need to specify what would be PFK in that entity that you replaced.
IV) A copy of your final workbench file (format .mwb).
Assignment Submission:
ONE GROUP MEMBER should submit the assignment via the CANVAS LMS
https://lms.unimelb.edu.au
ONE PDF document named as your Group number id (e.g. Wed10-1.pdf) on or before the deadline,
containing:
• Student name and Student Number of every student in your group
• Work breakdown per team member (contribution listed and also measured 1-100% per
team member)
• Legible image of your Physical ER Model in Crows foot notation (missing image and only
mwb results in 10% penalty)
• Explanatory notes (maximum 150 words) – optional since your model should be sufficiently
self-explanatory. For example, if you are replacing a very complex PFK with a surrogate key,
you need to specify what would be PFK in that entity that you replaced.
Your statements and design decisions must not contradict the case study and the discussions
on Ed Discussions Forum.
• (Optional) You may provide some limited explanation behind your design decisions (e.g.
where you replace ‘too many’ natural keys with one surrogate key)
2 For more details on blood pressure measuring, see
https://www.betterhealth.vic.gov.au/health/conditionsandtreatments/blood-pressure
INFO90002 A1 2023s2 © The University of Melbourne v1b
N.B. If you fail to submit legible models you will be penalised 10% of your total grade for this
assignment.
ONE COPY of your team's final MySQL Workbench modelling file (with an .mwb extension) of the
Physical ER model on or before the deadline. If this file is not submitted, the penalty can be up to
20% (higher if the marker has difficulty reading your diagram).
Submissions via email will not be accepted.
Late Submissions
Assignments that are late without a formal granted deadline extension from the subject coordinator
will attract a penalty of 10% for each Academic Day as per the School of Computing and Information
Systems policy.
Appendix
Assignment 1 group members contribution/ Work breakdown
Tute Group no.
Group members list (names and IDs and %% contributed); put the team leader as the first one.
1.
2.
3.
4.
Workload breakdown
(Add rows to the table as needed)
Activity
(e.g. brainstorm the
initial list of entities)
Planned
due date
Actual
delivery
Team
member
responsible
Comment
(e.g. why the task was reallocated
to another team member or
discussions with the team member
in charge of the task)
The first draft of this table must be done at the first group meeting, then update the table as the
group progresses through the assignment. Keep notes of every member contribution or lack of
response.
INFO90002 A1 2023s2 © The University of Melbourne v1b
Contribution to group work rules:
%% is reflection how much the member contribution to the group’s work improved the outcome so
only contribution to developing the solution counts. E.g. a member attending all meetings but not
providing any parts of the solution contributed 0%.
In case of equal contribution by all group members, everyone’s contribution is 100%; for members
with lower contribution, estimate their contribution in relation to members contributing 100%.
In case of an unresponsive group member, contact your tutor and subject coordinator immediately,
do not wait till closer to the deadline. In case of group issues, if they cannot be resolved within the
group, please contact your tutor ASAP.
Marking rubric is provided separately.