Skip links
Drag

数据库代写|INFO20003 Database Systems SAMPLE EXAM

这是一个澳洲的数据库系统的Online Exam代考

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
mild).

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
phone numbers.

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.
(20 marks)

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.

Leave a comment