Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: THEend8_
INFO20003 Sample Exam
INFO20003 Database Systems
SAMPLE EXAM
Reading Time: 15 minutes
Writing time: 120 minutes
This paper has 14 pages including this page
Authorised Materials
Calculators: Casio fx82 calculators are permitted
Instructions to Invigilators
• The examination paper IS TO REMAIN in the examination room
• Students are to be provided with 1 script book
• Provide extra script books on request
Instructions to Students
• The total mark for this paper is 120
• Ensure your student number is written on all script books and answer sheets during writing
time
• Attempt all questions in all 10 sections, which are of unequal marks value
• Answer all questions on the right-hand lined pages of the script book
• Start the answer to each question on a new page in the script book
• The left-hand unlined pages of script books are for draft working and notes and will not be
marked
• State clearly and justify any assumptions made
• Write legibly in blue or black pen
• Mobile phones, tablets, laptops, and other electronic devices, wallets and purses must be
placed beneath your desk
• All electronic devices (including mobile phones and phone alarms) must be switched off
and remain under your desk until you leave the examination venue. No items may be taken
to the toilet
INFO20003 Sample Exam Page 2 of 14
This page is left intentionally blank.
INFO20003 Sample Exam Page 3 of 14
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.
Figure 1: Australian Medicare card (DHHS, Australia)
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)
INFO20003 Sample Exam Page 4 of 14
This page is left intentionally blank
INFO20003 Sample Exam Page 5 of 14
Section 2 – SQL-DDL
Figure 2: Data model for 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.
(5 Marks)
INFO20003 Sample Exam Page 6 of 14
Section 3 – SQL-DML
Figure 3 shows the schema for a small business database that contains data about
employees, products, customers and orders.
Figure 3: Data model for SQL DML
Write a single SQL statement to correctly answer each of the following questions (3A – 3D).
DO NOT USE VIEWS or VARIABLES to answer questions. Query nesting is allowed.
INFO20003 Sample Exam Page 7 of 14
The relations are repeated here for your convenience.
employees (empid, lastname, firstname, hiredate, address, phone,
FK
managerid )
orders (orderid,
FK
custid ,
FK
empid , orderdate, shippeddate, freight, shipname)
customers (custid, companyname, contactname, address, phone)
orderdetails (
FK
orderid ,
FK
productid , quantity, discount)
products (productid, productname, unitprice, discontinued)
Q3A. Write a query that returns customers (company names) and the details of their orders
(orderid and orderdate), including customers who placed no orders.
(3 marks)
Q3B. Write a query that returns the first name and last name of employees whose manager
was hired prior to 01/01/2002.
(4 marks)