The University of Melbourne
School of Computing and Information Systems INFO20003 Database Systems
Practice data modelling task: The Medicare system
The Australian Government Department of Health and Human Services (DHHS) tracks medical records for every person who is eligible to hold a Medicare card. Each Medicare card number is associated with only one family (see 10-digit number in Figure 1). Individuals and children over the age of 15 are considered as a one-person family. For families living at the same residential address, children under the age of 15 are stored on the family card.
Copyright By PowCoder代写 加微信 powcoder
Every Medicare card has a ¡®valid to¡¯ date stored as a month and year on the card (refer Figure 1). Each family member holds a position number on the card. For example, to identify both her Medicare number (1234567890) and position (4) on the Medicare card would be required.
Figure 1: Australian Medicare card (DHHS, Australia). John and Helen are parents, and James and Jessica are their children.
Each Medicare card is attached to one residential address, contact email, and phone number. However, multiple Medicare cards can be associated with the same address (e.g. university students in a shared house).
For all patients listed on a Medicare card we record their gender, birthdate, first name and last name.
For every patient, DHHS wishes to record if they have any known allergies to medications (e.g. penicillin, cortisone, codeine). If patients do have an allergy we need to know the type of allergy, and if known, typical medication remedy and dosage. For every allergic reaction event the database will record the date when it happened, seriousness of the allergic reaction, medication remedy and dosage (the particular remedy and amount actually given to the patient), and a short descriptive paragraph that describes the details of the reaction. Allergic reaction seriousness is graded ¡®Strong¡¯, ¡®Moderate¡¯ or ¡®Weak¡¯.
In Case of Emergency (ICE) contact
For each patient, DHHS may record an In Case of Emergency (ICE) contact. The ICE contact includes their full name, work, home and mobile contact number and their home residential address. Many
Page 1 of 4
ý The University of Melbourne
INFO20003 Practice data modelling task: The Medicare system Page 2 of 4
patients (potentially across more than one family) may list the same ICE contact. Patients on the same Medicare card may list a different ICE contact. An ICE contact is not mandatory but is preferred by DHHS.
Vaccinations
DHHS needs to record mandatory vaccinations (e.g. measles, polio, whooping cough, diphtheria, tuberculosis and tetanus) as well as optional vaccinations (e.g. HPV, Flu, Hepatitis A, Hepatitis B, Cholera, Typhoid, Yellow Fever) and be able to identify any child under the age of 15 who has not received any of the mandatory vaccinations.
For each vaccination event given to patients we must record the vaccine (e.g. tetanus), date of vaccination and vaccine batch number. Vaccine producers can produce many types of vaccines (e.g. tetanus, flu, cholera) and each vaccine can have many batches. DHHS must know which company produced the vaccine, vaccine¡¯s batch number, batch manufacture date and batch expiry date. Each vaccination event is for one vaccine.
Patients can receive their vaccination from any registered doctor. Every registered doctor is identified by a unique medical practitioner number (MPN). We record the medical practitioner¡¯s title (Dr, Mr, Mrs, Ms, Prof), first name, last name, registered business address, email, and business phone numbers.
Organ donation
DHHS records whether any person over the age of 16 has opted to be an organ and tissue donor. For each person that opts in we record their details, and the organs and tissues they have agreed to donate. The potential organ and tissue that can be donated are as follows: heart, lung, kidney, liver, pancreas, pancreas islet, eye tissue, bone tissue, skin tissue and heart tissue. Some patients may opt in to only donate organs (e.g. heart, lung, kidney, pancreas) or only donate tissues.
Analyse this business case and design a conceptual ER model in Chen¡¯s notation and a physical model modelled with MySQL Workbench. Additionally, list any assumptions you have made about the model on a separate page. There is a 400-word limit for assumptions. Assumptions must not be used to simplify the assignment, but only to justify your decision about any ambiguity in the case study.
Practice data modelling task: The Medicare system
Page 3 of 4
Here is one possible solution as a conceptual model:
Vaccine type
organ/tissue
mandatory/ optional
work phone home phone
mobile phone
first name last name
Patient allergy
Donatable organ or tissue
batch number
patient opted in to donate
manufactured into
recorded for
manufacture date
date of birth
first name
Medicare number
ICE contact
typical remedy
vaccine administered
typical dosage
expiry date
Vaccine batch
Medical practitioner
first name
suffers from
implicated in
Medicare card
Patient allergy event
email address
remedy given
email address
¡°valid to¡± date
reaction (strong/medium/ weak)
reaction details
INFO20003 Practice data modelling task: The Medicare system Page 4 of 4
And a physical model:
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com