Section 1 – ER Modelling
The Australian Department of Health and Human Services (DHHS) has a requirement to store
the vaccination record of every individual (known as a patient) that resides in Australia who is
eligible for a Medicare card. For families, children under the age of 15 are stored on the family
card (refer Figure 1). Each Medicare card number is associated with only one family. 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 Jessica
Smith (refer Figure 1) both her Medicare number (1234567890) and position (4) on the
Medicare card would be required.
Each Medicare card is attached to one residential address, contact email, and phone number.
For all patients listed on a Medicare card we record their gender, birthdate, first name, last
name, and if they have any known allergies (e.g. Penicillin, Cortisone, Codeine). If patients do
have an allergy we need to know what the allergy is and the reaction (severe, moderate or
The DHHS needs to record mandatory vaccinations (e.g. Measles, Polio, Whooping Cough,
Diphtheria, Tuberculosis and Tetanus) as well as optional vaccination types (e.g. HPV, Flu,
Hepatitis A, Hepatitis B, Cholera, Typhoid, Yellow Fever).
For each vaccination event that is given to patients we must record the vaccination type, date
of vaccination and the vaccination batch number. Vaccine producers can produce many
different types of vaccines and each vaccine can have many batches.
Patients can receive their vaccination from any registered doctor. Every 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
Q1. Draw a conceptual model in either Crow’s Foot or Chen’s notation for this case study
(in your script book). Be sure to write down any assumptions you make.
Section 2 – SQL-DDL
Q2. Write SQL statements to create the tables for the above data model. Be sure to specify
primary and foreign keys. You do not need to specify whether the fields are NULL/NOT NULL.
Choose appropriate data types for attributes that are not obvious.