这是一个澳洲的数据库系统的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.