Examination Cover Page
AFFIX
STUDENT ID
STICKER HERE
Examination Period: 2016 Term 1
Academic Institution: Central Queensland University
Academic Group: Higher Education Division
Academic Career: Postgraduate
Examination Type: Standard
I have read and understood the penalties involved if I do not abide by the rules outlined on the back of this examination paper.

Course: Database Design and Development
Subject Area: COIT
Catalog Number: 20247
Paper Number: 1
Component: ALL Components

Duration: 120 minutes Restrictions: Closed Book
Perusal Time: 15 minutes
First Contact: Wei Li Contact Number: 07 49309686
Second Contact: Umapathy Venugopal - uvenu Contact Number: 02 93245789
Office Use: Release examination paper via the CQ University Past Exams website two weeks after the DE/SE examination period? Yes

Instructor Authorised/Allowed Materials
Dictionary - non-electronic, concise, direct translation only (dictionary must not contain any notes or comments).
Calculator - non-programmable, no text retrieval, silent only.
Student Calculator - Make:____________________ / Model: ____________________
Special Instructions to Students:
Please see instruction sheet on first page of the examination paper.
Examination Office Supplied Materials
3 x Rough Paper
1 x Exam Answer Booklet
QUESTIONS ANSWERED MARKS QUESTIONS ANSWERED MARKS

Number of examination answer booklets used:
Number of separate sheets attached (Do not include rough paper):
This examination paper is not to be released to the student at the conclusion of the examination. Central Queensland University considers improper conduct in examinations to be a serious offence. Penalties for cheating are exclusion from the University and cancellation with academic penalty from the course concerned.

Instructions Sheet
1. Write all answers in the Examination Answer Booklet provided.
2. This examination comprises three parts: Parts A, B and C.
3. The total marks available in the examination are 35.
4. Students should attempt ALL questions in ALL parts.
PART A 15 MARKS
DATA MODELLING QUESTIONS
Students are to answer ALL questions from this part.
Question 1 and Question 2 are based on the following ER model, which is illustrated in standard ER notations but not normalised yet.
Question 1 4 Marks
Answer the following questions based on the given ER model. You need to briefly justify your answers.
(a) Does every customer have an account? (1 mark)
(b) Does every account have a transaction? (1 mark)
(c) What is the relationship between ACCOUNT and SAVINGS or CREDIT or HOME LOAN? Why is such a relationship used? (1 mark)
(d) Are there any other possible types of account? (1 mark)
Question 2 5 Marks
Define the primary keys and foreign keys for all the entities in Question 1. To do that, you need to redraw the ER diagram in Question 1 and clearly write the keys in the diagram. You do not need to show your working.
Question 3 6 Marks
Assume the following relation includes all the current available information about patients, doctors and appointments.
PatientID PatientName DoctorID DoctorName AppointmentID Task DateTime
P000001 Peter Smith D000001 John Smith A10022 Surgery 9:30, 10/11/2012
P000001 Peter Smith D000002 Kylie Wells A10025 Consultation 12:00,
07/12/2012
P000002 Tony Pearson D000001 John Smith A20443 Consultation and possible surgery 10:00,
08/01/2013
P000002 Tony Pearson D000003 Paul Fisher A20125 Annual check 2:00, 05/02/2013
P000003 Lisa Clinton D000004 Andrew
Robertson A13535 Surgery 3:50, 25/12/2012
P000004 Mary Jackman D000002 Kylie Wells A23007 Consultation for test results 11:05,
15/01/2013
(a) What can be the primary key for the relation? (1 mark)
(b) If deleting the appointment of 3:50 on 25th Dec 2012 for patient Lisa Clinton, justify what anomaly will happen. (1 mark)
(c) Justify the highest normal form (e.g. 1NF, 2NF, 3NF etc) that the relation satisfies. (1 mark)
(d) Normalise the relation into a set of relations that satisfy 3NF using the format like the following:
Relation1 (ID1, Abc, Def, …)
Relation2 (ID2, ID1, Hij, Klm, …)
Foreign key (ID1) references Relation1
(3 marks) PART B 10 MARKS
STRUCTURED QUERY LANGUAGE QUESTIONS
Students are to answer ALL questions from this part.
Each question is worth two (2) marks (2 x 5 = 10 marks).
The following relations of an online sell database contain the information about sellers, buyers, smartphones and sale transactions. Formulate SQL queries to answer the questions in this part.
SELLER
Note: the primary key is SellerID.
BUYER
Note: the primary key is BuyerID.
Part B continued over next page
Part B (continued)
SMARTPHONE
Note:
• The primary key is ProductID.
• SellerID is the foreign key referencing to the SELLER relation.
TRANSACTION
Note:
• The primary key is TransactionID.
• ProductID is the foreign key referencing to the SMARTPHONE relation.
• BuyerID is the foreign key referencing to the BUYER relation.
Question 1 2 Marks
List the details of the smartphone that has the biggest display size. The result would be like the following based on the current database given above.
Question 2 2 Marks
List the details of a particular smartphone. You will need to use the Brand and the Model as the parameters for this query like the following.
Question 2 continued over next page
Question 2 (continued)
The result would be like the following based on the current database given above.
Question 3 2 Marks
List the details of buyers who haven’t bought anything yet. The result would be like the following based on the current database given above.
Question 4 2 Marks
List the seller names and the numbers of different types of smartphone that they sell. The results should be sorted in descendent order of Number of Phone Types. Please note that smartphones with different ProductIDs are treated as different types of smartphone. The result would be like the following based on the current database given above.
Question 5 2 Marks
List the buyer name, seller name, transaction ID for a transaction, which has ‘quick delivery’ or ‘fast delivery’ feedback. The result would be like the following based on the current database given above.

PART C 10 MARKS
SHORT-ANSWER THEORY QUESTIONS
Students are to answer ALL questions from this part.
Question 1 2 Marks
There are 2 transactions to access the same bank account. The tasks of each transaction happen in the time sequence as shown in the following diagram. Answer the following questions based on the given scenario.
(a) At the end of this time sequence there is an error (problem) happening,
describe the problem. (1 mark)
(b) Discuss how to prevent the problem by using exclusive locks. (1 mark)
Question 2 2 Marks
Use an example to describe why referential integrity should be maintained for a database.
Question 3 2 Marks
Briefly compare and contrast distributed database and decentralised database.
Question 4 2 Marks
Describe the ACID properties of a transaction.
Question 5 2 Marks
Describe about the 4 features of data warehouse.
- End of paper -