Need DB answers for
1) COIT20247_T318_DB_Exam paper 1,
2) DB_ExamFrmt Paper 2,
3) COIT20247_T217_DB_exam paper 3
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 (07) 4930 6337 Second Contact Umapathy Venugopal Contact Number (02) 9324 5789 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 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 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 incorrect 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 relation is BookingID. Examine the relation and answer the questions that follow. Booking BookingID Booking Date From Date To Date NbrOf 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 (2 marks) 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 relations CUSTOMER, BOOKING, FLIGHT and AIRLINE provided below. These relations 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) Foreign key (FlightNo) references FLIGHT Foreign key (CustomerID) references CUSTOMER FLIGHT(FlightNo, DepartureTime, ArrivalTime, DepartureCity, ArrivalCity, Price, AirlineCode) Foreign key (AirlineCode) references AIRLINE AIRLINE(AirlineCode, AirlineName, CustomerServicePhone) Note: In the table of Flight, the DepartureCity and ArrivalCity are the city names which are abbreviated as three letters such as SYD referring 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 0749008877 2 John Sydney 98 South street, Melbourne 0490787772 3 Issac Newton 90 Heaven road, Sydney 0299001122 4 Suzan Karl 101 St Lucia Garden, Brisbane 0733099000 5 Kate Wesley 345 Illinois road, Brisbane 0739000554 6 Michael Lee 201 South port road, Gold Coast 0745676766 BOOKING BOOKING ReservationID FlightNo CustomerID TravelDate CFD123 QF507 1 17/11/2018 DKK980 QF506 1 22/11/2018 HQA090 VA1238 2 29/11/2018 YXW234 VA1244 4 25/11/2018 ZSS909 VA1238 5 29/11/2018 AIRLINE AIRLINE AirlineCode AirlineName CustomerServicePhone QF Qantas 1300456700 VA Virgin 1300766550 FLIGHT FLIGHT FlightNo DepartureTime ArrivalTime DepartureCity ArrivalCity 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 correctly. • Simple queries are preferred; 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 1 (2 marks) Show customers who have not booked any flights. CustomerID FirstName LastName Address Phone 3 Issac Newton 90 Heaven road, Sydney 0299001122 6 Michael Lee 201 South port road, Gold Coast 0745676766 Question 2 (2 marks) List all flights and customers who have departed from Brisbane. FlightNo FirstName LastName QF507 Phil Hope Question 3 (2 marks) Show details of all flights between