AFFIX
STUDENT ID
STICKER HERE
Examination Cover Page
Examination Period: 2016 Term 2
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.
Student
Signature: Student ID Number
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: Umapathy Venugopal - uvenu Contact Number: 02 93245789
Second Contact: Michael Li Contact Number: 07 49306337
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.
Term 2 Standard Examination 2016
DATABASE DESIGN AND DEVELOPMENT - COIT20247
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.
Page 1 of 6
Term 2 Standard Examination 2016
DATABASE DESIGN AND DEVELOPMENT - COIT20247
PART A 13 MARKS
DATA MODELLING QUESTIONS
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 has been drawn incorrectly and contains many errors.
Question 1 4 Marks
The above ER diagram is incomplete and has been drawn incorrectly due to the following reasons:
(a) A COMPLAINT is not related to an advertisement but related to a DEVELOPMENT_APPLICATION.
(b) Each OPEN_COMPLAINT is a COMPLAINT and they should be related to each other.
(c) Each NEWS_PAPER charges differently for the same ADVERTISEMENT.
(d) Relationship names are missing in the ER diagram.
Complete and correct the errors in the given ER diagram and redraw it using standard ER notations only.
Page 2 of 6
Question 2 4 Marks
Answer the following questions based on the given ER model. You need to briefly justify your answers.
(a) How many DEVELOPMENT_APPLICATIONS can be placed in any one
ADVERTISEMENT? (1 mark)
(b) Can an ADVERTISEMENT appear in more than one NEWS_PAPER? (1 mark)
(c) As per the ER diagram, a DEVELOPMENT_APPLICATION may not appear in any of the ADVERTISEMENTS. What could be the reason to model it in that way? (1 mark)
(d) Which type of relationship is the most appropriate for the relationship between
COMPLAINT and OPEN_COMPLAINT? Why? (1 mark)
Question 3 5 Marks
The following relation provides the information related to tours and tour guides. One tour can have one guide only; one tour guide can guide more than one tour.
GuideID GuideName TourId TourName GuidePhone
1 John Smith T1001 Bondi Beach 93245000
1 John Smith T1002 Harbour Bridge 93245000
2 Mary Smith T1003 Luna Park 91256666
2 Mary Smith T1005 Jenolan Caves 91256666
3 Mary May T1006 Opera House 87551234
4 Mona Kumar T1004 Darling Harbour 98251478
(a) What is the primary key of the above relation? (1 mark)
(b) Explain any one anomaly that exists in the above relation. (1 mark)
(c) What is the highest normal form (e.g. 1NF or 2NF or 3NF) that the above relation satisfies? Note: You need not show the work out. (1 mark)
(d) Normalize the above relation into a set of relations that satisfy 3NF. You need not provide the work out to arrive at the 3NF relation but provide the final 3NF relations as per the following format: Relation1 (ID1, aaa, bbb, …)
Relation2 (ID2, ID1, ccc, ddd, …)
Foreign key (ID1) references Relation1 (2 marks)
PART B 10 MARKS
STRUCTURED QUERY LANGUAGE QUESTIONS
Answer ALL questions from this part.
Each question is worth two (2) marks (2 x 5 = 10 marks).
TABLES:
Guide(GuideId, GivenName, FamilyName, MobileNo)
Tour(TourId, TourName, TourPrice, TourStartTime, GuideId) Foreign key GuideId references Guide
Location(LocationName, PostCode)
TourLocations(TourId, LocationName) Foreign key (TourId) references Tour
Foreign key (LocationName) references Location
Guide
GuideId GivenName FamilyName MobileNo
1 John Smith 0403111222
2 Mary Smith 0403444333
3 Lisa Taylor 0411223355
4 Mona Kumar 0422555666
Tour
TourId TourName TourPrice TourStartTime GuideId
1 Blue
Mountains
$200.00
7:30 am 1
2 Sydney $145.00 8:15 am 2
3 Beach $45.00 5:30 am 2
Location
LocationName PostCode
Bondi Beach 2026
Darling Harbour 2000
Harbour Bridge 2061
Jenolan caves 2780
Luna park 2061
Opera House 2000
TourLocations
TourId LocationName
1 Jenolan caves
2 Darling Harbour
2 Harbour Bridge
2 Opera House
3 Bondi Beach
Write SQL queries to answer the following information requests given below:
Note: You need to check the column headings; grouping and sorting of data; removal of data; and other aspects of query as shown in the expected output provided in each question.
Question 1 2 Marks
Display the details of the guides who guide the tour that covers more than one location.
GuideId GivenName FamilyName MobileNo
2 Mary Smith 0403444333

Question 2 2 Marks
Display the total number of locations in each tour in ascending order of tour name.
TourName NumberOfLocations
Beach 1
Blue Mountains 1
Sydney 3
Question 3 2 Marks
List the details of Guide(s) who have not been allocated to any tour.
GuideId GivenName FamilyName MobileNo
3 Lisa Taylor 0411223355
4 Mona Kumar 0422555666

Question 4 2 Marks
Display the tour name and tour price in ascending order of tour price.
TourName TourPrice
Beach $45.00
Sydney $145.00
Blue Mountains $200.00
Question 5 2 Marks
Which location name(s) contains the word ‘cave’? Show the location name and postcode in descending order of postcode.
LocationName PostCode
Jenolan caves 2780
PART C 12 MARKS
SHORT-ANSWER THEORY QUESTIONS
Answer ALL questions from this part.
Question 1 2 Marks
What is difference between Data Warehouse and Data Mart?
Question 2 2 Marks
What is meant by deadlock?
Question 3 2 Marks
List any four (4) properties of Relation.
Note: You do not have to explain those properties. Relation is not relationship.
Question 4 2 Marks
How does the database overcome the problems faced by the file based (information) systems?
Question 5 2 Marks
Differentiate decentralized database and distributed database.
Question 6 2 Marks
Describe the ACID properties of Transaction.
- End of Examination -