My DB exam (COIT20247) will begin 2022/10/14 - Friday 10.00 am AEST time and will finish it on 15.00 pm AEST time. Please allocate someone to do this new exam paper in a word document on that day. I will send the exam paper on Friday at 10.00am AEST time and send me the answers before 15.00 pm AEST time.
Semester Examinations PAST EXAM DATABASE DISIGN & DEVELOPMENT — COIT20247 Page 1 of 23 INSTRUCTIONS SHEET 1. Write your answers in the answer spaces/boxes provided for all the questions in the examination paper. 2. This examination comprises four (4) parts, Part A, B, C and D. 3. Students should answer ALL questions in Parts A and B. 4. In Part C, students should answer five (5) of the seven (7) questions. If more than five questions are answered, only the first five will be marked. 5. In Part D, students should answer two (2) of the three (3) questions. If all three questions are answered, only the first two will be marked. 6. Write clearly, use headings or subheadings. Example: Part B – Question 2. PAST EXAM DATABASE DISIGN & DEVELOPMENT — COIT20247 Page 2 of 23 PART A 10 MARKS APPLICATION QUESTIONS Students are to answer ALL questions from this part. Questions 1 and 2 in this part relate to the ER model given below. The ER model illustrates the entities and relationships for a large bank. Customers have accounts. Accounts have transactions. Examine the ER model below and answer all the questions that follow. ER model ACCOUNT AccountID AccountName AccountType CurrentBalance CUSTOMER CustomerID CustomerName PostalAddress (Street, City, State, PostCode) {Phone} TRANSACTION TransactionID TimeStamp TransactionType Amount d SAVINGS MonthlyFee CREDIT CARD CreditLimit HOME LOAN InterestRate owns has PAST EXAM DATABASE DISIGN & DEVELOPMENT — COIT20247 Page 3 of 23 Question 1 2 Marks Data Modelling According to the ER model given above, answer either yes or no to the following questions: (a) Can an account be both a savings account and a credit card account? (0.5 mark) (b) Does every customer have an account? (0.5 mark) (c) Is it possible for an account to have no transactions? (0.5 mark) (d) Are there any other account types besides savings, credit card and home loan? (0.5 mark) Your answer: Your answer: Your answer: Your answer: PAST EXAM DATABASE DISIGN & DEVELOPMENT — COIT20247 Page 4 of 23 Question 2 4 Marks Converting ER models Convert the ER model given at the start of 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 Question 2 continued over next page PAST EXAM DATABASE DISIGN & DEVELOPMENT — COIT20247 Page 5 of 23 Question 2 (continued) Answer: ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… PAST EXAM DATABASE DISIGN & DEVELOPMENT — COIT20247 Page 6 of 23 Question 3 4 Marks Relational model and Normalization The following table identifies the information that doctors in a hospital made treatments for patients. You may assume that the combination of PatientID, DoctorID and TreatDate uniquely identify each row/tuple in this relation Treatment PatientID PatientName DoctorID DoctorName TreatDesc TreatDate P101 Phil London D001 Peter Peterson Take panadol 12-1-2011 P101 Phil London D002 Tennis Bonn Brain surgery 15-1-2011 P102 Shirley Wu D001 Peter Peterson Take antibiotics 15-1-2011 P102 Shirley Wu D003 Amanda Lee Heart surgery 18-1-2011 P103 Victor Smith D004 John Lee Physio- therapy 19-2-2011 P104 Sam Sydney D002 Tennis Boon Blood- transfusion 19-2-2011 (a) On the above table, if we want to delete a record related to the treatment of the patient – Victor Smith, what kind of anomaly will happen and why? (1 mark) Question 3 continued over next page PAST EXAM DATABASE DISIGN & DEVELOPMENT — COIT20247 Page 7 of 23 Question 3 (continued) Answer: ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… (b) What is the highest normal form (e.g. 1NF, 2NF, 3NF etc) that the relation Treatment satisfies? Explain your answer. (1 mark) Answer: ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… (Question 3 continued over page) PAST EXAM DATABASE DISIGN & DEVELOPMENT — COIT20247 Page 8 of 23 (Question 3 continued) (c) Decompose the relation Treatment into a set of relations which satisfy 3NF. (2 marks) Answer: ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… PAST EXAM DATABASE DISIGN & DEVELOPMENT — COIT20247 Page 9 of 23 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). Formulate SQL queries to answer the following information requests. Use the relations AGENT, PROPERTY, CLIENT, and VIEWING provided below. These relations describe clients viewing properties that they intend to rent. These properties are managed or looked after by specific agents in a property management company as shown in the tables. 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. • 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. PAST EXAM DATABASE DISIGN & DEVELOPMENT — COIT20247 Page 10 of 23 TABLES AGENT AAggeennttNNoo FFNNaammee LLNNaammee PPoossiittiioonn PPhhoonnee 100 Michael Wu Manager 49001122 120 Ann Beech Assistant 49778819 140 David Ford Supervisor 49232300 160 Mary Howe Supervisor 49018765 180 Susan Hall Manager 48090914 200 Julie Lee Assistant 48003355 Notes about the Agent table • Each row describes an agent. • The primary key is AgentNo. PROPERTY PPrrooppeerrttyyNNoo SSttrreeeett SSuubbuurrbb RReenntt AAggeennttNNoo P14 16 Holhead Frenchville 660 160 P94 6 Argyll St North Rocky 400 180 P4 6 LawrenceSt South Rocky 335 P36 2 Manor Rd South Rocky 390 120 P21 18 Dale Rd Frenchville 450 120 P16 5 Novar Dr South Rocky 400 140 Notes about the Property table • Each row describes a property under management by an agent. • The primary key is PropertyNo. • AgentNo is a foreign key reference to the Agent table; it indicates the ID of the agent who is managing the property. CLIENT PAST EXAM DATABASE DISIGN & DEVELOPMENT — COIT20247 Page 11 of 23 CClliieennttNNoo FFNNaammee LLNNaammee PPhhoonnee C76 John Kay 49000111 C56 Aline Stuart 49889977 C74 Mike Ritchie 49012288 C62 Mary Tegear 49883344 Notes about the Client table • Each row describes a client. • The primary key is ClientNo. VIEWING CClliieennttNNoo PPrrooppeerrttyyNNoo VViieewwDDaattee CCoommmmeenntt C56 P14 24-May-12 Too small C76 P4 20-April-12 Too remote C56 P4 26-May-12 C62 P14 14-May-12 No dining room C56 P36 28-April-12 C74 P16 15-April-12 OK Notes about the Viewing table • Each row describes a viewing inspection that has been made by a client. • The primary key is the combination of ClientNo, PropertyNo and ViewDate. Note that the same client may have another look at the same property. • PropertyNo is a foreign key reference to the Property table. • ClientNo is a foreign key reference to the Client table. PAST EXAM DATABASE DISIGN & DEVELOPMENT — COIT20247 Page 12 of 23 Question 1 List details of the agents who have not been arranged to manage any of properties. (2 marks) AgentID FName LName Position Phone 100 Michael Wu Supervisor 49001122 200 Julie Lee Assistant 48003355 Answer: ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… …………………………………………………………………………. Question 2 How many properties does each agent manage? Note: You will need to use the count() function. (2 marks) AgentID NbrPropertyManaged 120 2 140 1 160 1 180 1 Question 2 continued over next page PAST EXAM DATABASE DISIGN & DEVELOPMENT — COIT20247 Page 13 of 23 Question 2 (continued) Answer: ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… …………………………………………………………………… Question 3 List the details for those properties that have been viewed and have the comment OK. (2 marks) PropertyNo Street Suburb Rent AgentID P16 5 Novar Dr South Rocky 400 140 Answer: ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… PAST EXAM DATABASE DISIGN & DEVELOPMENT — COIT20247 Page 14 of 23 Question 4 Which property has the most expensive rent? Note: You will need to use the max() function. (2 marks) PropertyNo Street Suburb Rent AgentID P14 16 Holhead Frenchville 660 160 Answer: ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… ………………………………………………………………………………………………… PAST EXAM DATABASE DISIGN & DEVELOPMENT — COIT20247 Page 15 of 23 Question 5 List