Examination Cover Page Examination Period: 2018 HE Term 3 Academic Institution: Central Queensland University Academic Group: Higher Education Division Academic Career: Postgraduate Examination Type:...

Examination Cover Page
Examination Period: 2018 HE Term 3
Academic Institution: Central Queensland University
Academic Group: Higher Education Division
Academic Career: Postgraduate
Examination Type: 2018 HE Term 3 Standard
Affix Student ID Sticker here
I have read and understood the penalties involved if I do not abide by the rules outlined on the back of this examination
paper.
Student Signature: _______________________________________ Student ID Number:
Unit: Database Design and Development
Subject Area: COIT
Catalog Number: 20247
Paper Number: 1
Component: ALL Components
Duration 120 minutes Exam Conditions Closed Book
Perusal Time 15 minutes
First Contact Michael Li Contact Number XXXXXXXXXX
Second Contact Umapathy Venugopal Contact Number XXXXXXXXXX
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).
No Calculators Permitted
Special Instructions to Students:
Please see instruction sheet on first page of the examination paper.
Examination Office Supplied Materials
1 x Rough Pape
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 unit concerned.
Term 3 Standard Examination 2018
Database Design and Development – COIT20247
Page 1 of 9
Instructions Sheet

1. Write all answers in the Examination Answer Booklet provided.
2. This examination comprises three parts, Part A, B and C.
3. Students should attempt ALL questions in All parts.
4. The total marks available in the examination are 35.




Term 3 Standard Examination 2018
Database Design and Development – COIT20247
Page 2 of 9
























































PART A 13 MARKS
DATA MODELLING QUESTIONS
Students are required to answer ALL questions from this part.
All questions in this part relate to the ER model given below. The ER model illustrates
the entities and relationships for a large motel. The motel has a number of buildings,
each with a number of rooms. Customers make bookings for rooms. Examine the ER
model below and answer all the questions that follow.



ER model:



























Term 3 Standard Examination 2018
Database Design and Development – COIT20247
Page 3 of 9
Question 1 4 Marks

Data Modelling
According to the ER model given above, answer either yes or no to the following
questions:

(a) Can a room have more than one facility specified in its description? (1 mark)
(b) Does CorporateDiscount apply to every customer? (1 mark)
(c) Can a customer be both a corporate customer and a private customer? (1 mark)
(d) Is it possible for a building to have no rooms? (1 mark)

Question 2 5 Marks

Converting ER models
Convert the ER model given in Part A into a set of relations that satisfy Third Normal
Form (3NF). You do not need to show your workings. You do not need to justify
that they are in 3NF at this stage. You do not need to show sample data. Just show
your relations. You should write your relations in either format shown below:

Student (StudentID, StudentName, DateOfBirth)
Enrolment (EnrolmentID, StudentID, DateOfEnrolment)
or:
Student (StudentID, StudentName, DateOfBirth)
Enrolment (EnrolmentID, StudentID, DateOfEnrolment)
Foreign key (StudentID) references Student




Term 3 Standard Examination 2018
Database Design and Development – COIT20247
Page 4 of 9
Question 3 4 Marks

Relational model and Normalisation
An inco
ect transformation of the Booking entity type (from the ER model given at the
start of Part A) to a Booking relation is represented below. The primary key of this
elation is BookingID. Examine the relation and answer the questions that follow.

Booking
BookingID Booking Date
From
Date
To
Date
N
Of CustomerID CustomerName
Guests
1 01/4/18 10/4/18 12/4/18 1 1 Raj Singh
2 15/4/18 15/4/18 16/4/18 2 3 Caroline Catz
3 11/5/18 17/5/18 25/5/18 1 4 Jo Large
4 02/6/18 03/6/18 05/6/18 3 2 Martin Clunes
5 02/8/18 02/8/18 12/8/18 2 3 Caroline Catz
6 03/8/18 23/8/18 03/9/18 2 4 Jo Large

(a) On the above table, if we want to add a record related to a customer, what kind
of anomaly will happen? Justify your answer (1 mark)
(b) What is the highest normal form that this relation satisfies and why? (1 mark)
(c) Normalise the relation into a set of relations that satisfy 3NF using the format as
follows:
Customer(CustomerID, CustomerName)
Order(OrderID, Amount, Date, CustomerID)
Foreign key (CustomerID) references Customer
XXXXXXXXXXmarks)
Term 3 Standard Examination 2018
Database Design and Development – COIT20247

Page 5 of 9

PART B 10 MARKS
STRUCTURED QUERY LANGUAGE QUESTIONS
Students are required to answer ALL questions from this part.
Each question is worth two marks (2 x 5 = 10 marks).


Formulate SQL queries to answer the following information requests. Use the
elations CUSTOMER, BOOKING, FLIGHT and AIRLINE provided below. These
elations describe customers booking flights that they want to take. These flights are
operated by airlines as shown in the tables.

Relations
CUSTOMER(CustomerID, FirstName, LastName, Address, Phone)
BOOKING(ReservationID, FlightNo, CustomerID, TravelDate)
XXXXXXXXXXForeign key (FlightNo) references FLIGHT
XXXXXXXXXXForeign key (CustomerID) references CUSTOMER
FLIGHT(FlightNo, DepartureTime, A
ivalTime, DepartureCity, A
ivalCity, Price,
XXXXXXXXXXAirlineCode)
XXXXXXXXXXForeign key (AirlineCode) references AIRLINE
AIRLINE(AirlineCode, AirlineName, CustomerServicePhone)
Note: In the table of Flight, the DepartureCity and A
ivalCity are the city names
which are a
eviated as three letters such as SYD refe
ing to Sydney.

Tables
Term 3 Standard Examination 2018
Database Design and Development – COIT20247

Page 6 of 9
CUSTOMER
CUSTOMER
CustomerID FirstName LastName Address Phone
1 Phil Hope 101 Yammba road, Rockhampton XXXXXXXXXX
2 John Sydney 98 South street, Melbourne XXXXXXXXXX
3 Issac Newton 90 Heaven road, Sydney XXXXXXXXXX
4 Suzan Karl 101 St Lucia Garden, Brisbane XXXXXXXXXX
5 Kate Wesley 345 Illinois road, Brisbane XXXXXXXXXX
6 Michael Lee 201 South port road, Gold Coast XXXXXXXXXX
BOOKING
BOOKING
ReservationID FlightNo CustomerID TravelDate
CFD123 QF XXXXXXXXXX/11/2018
DKK980 QF XXXXXXXXXX/11/2018
HQA090 VA XXXXXXXXXX/11/2018
YXW234 VA XXXXXXXXXX/11/2018
ZSS909 VA XXXXXXXXXX/11/2018
AIRLINE
AIRLINE
AirlineCode AirlineName CustomerServicePhone
QF Qantas XXXXXXXXXX
VA Virgin XXXXXXXXXX
FLIGHT
FLIGHT
FlightNo DepartureTime A
ivalTime DepartureCity A
ivalCity Price AirlineCode
QF2353 10:10:00 AM 11:25:00 AM ROK BNE $130.00 QF
QF2359 4:55:00 PM 6:10:00 PM ROK BNE $149.00 QF
QF506 7:30:00 AM 8:00:00 AM SYD BNE $145.00 QF
QF507 6:35:00 AM 9:10:00 AM BNE SYD $99.00 QF
QF509 7:05:00 AM 9:40:00 AM BNE SYD $145.00 QF
QF533 1:05:00 PM 3:40:00 PM BNE SYD $199.00 QF
QF539 2:35:00 PM 5:10:00 PM BNE SYD $299.00 QF
VA1237 9:00:00 AM 10:15:00 AM BNE ROK $190.00 VA
VA1238 10:45:00 AM 11:55:00 AM ROK BNE $120.00 VA
VA1244 2:40:00 PM 3:50:00 PM ROK BNE $160.00 VA
VA1251 6:45:00 PM 8:00:00 PM BNE ROK $185.00 VA
VA916 7:05:00 AM 9:40:00 AM BNE SYD $169.00 VA
VA932 9:05:00 AM 11:40:00 AM BNE SYD $155.00 VA
VA950 1:05:00 PM 3:40:00 PM BNE SYD $289.00 VA
Term 3 Standard Examination 2018
Database Design and Development – COIT20247

Page 7 of 9
Note that:
• You are asked to provide a general solution to each request. If the database
contents change, each of your queries should continue to answer the
information requested co
ectly.
• Simple queries are prefe
ed; if your queries are unnecessarily complex you
may lose marks.
• For the given sample data, your queries should be able to generate the same
data and column names as shown in the result table for each request.
• You are not required to sort the results in any order unless requested.
• State any assumptions that you make to clarify your understanding of the
information request.

Question XXXXXXXXXXmarks)
Show customers who have not booked any flights.

CustomerID FirstName LastName Address Phone
3 Issac Newton 90 Heaven road, Sydney XXXXXXXXXX
6 Michael Lee 201 South port road, Gold Coast XXXXXXXXXX

Question XXXXXXXXXXmarks)
List all flights and customers who have departed from Brisbane.

FlightNo FirstName LastName
QF507 Phil Hope

Question XXXXXXXXXXmarks)
Show details of all flights between