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, the usual medication + dosage the patient takes (a patient might take different medications to another patient even if they have the same allergy). For every allergic reaction event the database will record the date when it happened, seriousness of the allergic reaction, actual administered medication and dosage (the remedy and amount actually given to the patient during the event), and a short descriptive paragraph that describes the details of the reaction. Allergic reaction seriousness is graded ¡®Strong¡¯, ¡®Moderate¡¯ or ¡®Weak¡¯.pdf
In Case of Emergency (ICE) contact
Page 1 of 3
ý The University of Melbourne
INFO20003 Practice data modelling task: The Medicare system Page 2 of 3
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 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.
INFO20003 Practice data modelling task: The Medicare system Page 3 of 3
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com