1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams) 1/22 By submitting this assessment, I agree to the following declaration: As a member of the University’s student body, I will complete this assessment in a fair, honest, responsible and trustworthy manner. This means that: · I will not seek out any unauthorised help in completing this assessment. (NB. Unauthorised help includes a tutorial or answer service whether in person or online, friends or family, etc.) · I will not discuss or share the content of the assessment with anyone else in any form, including but not limited to, Canvas, Piazza, Chegg, Facebook, Twitter, Discord or any other social media within the assessment period. · I will not reproduce and/or share the content of this assessment in any domain or in any form where it may be accessed by a third party. · I am aware the University of Auckland may use Turnitin or any other plagiarism detecting methods to check my content. · I declare that this assessment is my own work, except where acknowledged appropriately (e.g., use of referencing). · I declare that this work has not been submitted for academic credit in another University of Auckland course, or elsewhere. Note: Include as applicable e.g., STEM subjects (Science, Technology, Engineering and Mathematics) I declare that I generated the calculations and data in this assessment independently, using only the tools and resources defined for use in this assessment. I declare that I composed the writing and/or translations in this assessment independently, using only the tools and resources defined for use in this assessment. I understand the University expects all students to complete coursework with integrity and honesty. I promise to complete all online assessment with the same academic integrity standards and values. Any identified form of poor academic practice or academic misconduct will be followed up and may result in disciplinary action. I agree 1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams) 2/22 1 Under the relational data model concepts, provide the definitions of superkey, key, primary key and candidate key. What are the differences among these four terminologies [4 marks] Fill in your answer here Maximum marks: 4 1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams) 3/22 2 Consider the partial ER diagram below, in which the ‘enrols’, ‘teaches’ and ‘uses’ relationship types record the current enrolment, teaching and textbook adoption information correspondingly with the following restrictions. Assume that a course may or may not use a textbook, but a textbook by definition is a book that is used in some courses. A course can not use more than three textbooks. An instructor teaches from one to five courses at any time. A course must have at least one instructor, and it can be taught by up to four instructors at any time. A course must have students enrol in it. A student may not enrol in any course, or can enrol in up to 6 courses at any time. Specify the (min, max) constraints for three relationship types of the ER diagram. [6 marks] 1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams) 4/22 Fill in your answer here. Note that you can use the ‘Insert Drawing’ function (the pen icon) below to write the answer. Format Σ Words: 0 Maximum marks: 6 1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams) 5/22 3 Using the algorithm taught in the course, map the following simplified ‘MOVIES’ ER diagram into a relational database schema. Specify all primary keys and foreign keys. [8 marks] Fill in your answer here. Note that you can use the ‘Insert Drawing’ function (the pen icon) below to write the answer. Format Σ Words: 0 1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams) 6/22 Maximum marks: 8 1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams) 7/22 4 The following questions use the LIBRARY database schema shown below. Suppose that the above database has been created and populated with valid data, define the following queries using SQL statements: i. List all the overdue books as per today (‘2021-06-25’) and their borrower information (show attributes such as Book_id, Title, Branch_id, Due_date, Card_no and Name). Sort the query output tuples by their due dates in descending order. [4 marks] Fill in your answer here 1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams) 8/22 ii. List how many different books each library branch has (show attributes such as Branch_id, Branch_name, Number_of_books). Note that multiple copies of the same book in a branch should be counted only once. [3 marks] Fill in your answer here Maximum marks: 7 1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams) 9/22 5 The following questions use the LIBRARY database schema shown below. Suppose that the above database has been created and populated with valid data. Define the following queries using Relational Algebra expressions: i. For each branch, retrieve the number of times books authored by ‘Isaac Asimov’ have been loaned. [3 marks] ii. Retrieve the title of books that have never been loaned. [2 marks] 1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams) 10/22 Fill in your answer here. Note that you can use the ‘Insert Drawing’ function (the pen icon) below to write the answers. Please label your answers for each sub-questions. Format Σ Words: 0 Maximum marks: 5 1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams) 11/22 6 Consider the relation schema Supplies = {Article, Supplier, Location, Cost} that lists the cost of articles supplied by a supplier from a location. Consider also the set of functional dependencies F = {{Supplier, Location} → {Article}, {Article} → {Cost}, {Article} → {Supplier}} (a) Find a Third Normal Form decomposition of Supplies with respect to the given set of functional dependencies. Is the decomposition lossless [5 marks] (b) Find a Boyce-Codd Normal Form decomposition of Supplies with respect to
the given set of functional dependencies. Is the decomposition lossless [5 marks] Fill in your answer here. Please label your answers for each sub-questions. Format Σ Words: 0 Maximum marks: 10 1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams) 12/22 7 Assume for our enrolment system that if a course has labs, then students must be enrolled in both the course and the lab in order to participate. Let’s assume both labs and course are restricted to a maximum number of enrolments. Discuss in about 10 sentences whether one should use transaction processing to manage such an enrolment system and discuss what could go wrong without transaction processing. Refer to the ACID properties where it is helpful. [10 marks] Fill in your answer here Format Σ Words: 0 Maximum marks: 10 1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams) 13/22 8 Consider the following schedule, s: r1[x], r2[x], r1[y], r3[y], c2, w1[x], w1[y], c1, r3[x], w3[x], r3[y], c3 (a) which phenomenon is appearing in this schedule, if any [2 marks] Select one alternative: (b) which operation violates the rules of the common scheduler first, if any [2 marks] Select one alternative lost update fuzzy read write skew dirty read none of the others r2[x] None of the others w1[y] r3[y] after w3[x] w1[x] Maximum marks: 4 1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams) 14/22 9 Consider the following schedule, s: r1[x], r2[x], r1[y], r3[x], c2, w1[x], w1[y], c1, r3[y], w3[x], w3[y], c3 (a) which phenomenon is appearing in this schedule, if any [2 marks] Select one alternative: (b) which operation violates the rules of the common scheduler first, if any [2 marks] Select one alternative none of the others fuzzy read dirty read write skew lost update w1[x] w1[y] w3[y] r2[x] None of the others Maximum marks: 4 1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams) 15/22 10 Crash recovery for steal, no-force policy. Our database, which follows the steal, no-force policy, encounters a system crash. After the crash, the stable database contains the following pages, objects on these pages and their values: Page 1: x = 67 Page 2: y = 61, z = 8 The stable log records contains the following last entries: [nr: 718, ta :32, BOT] [nr: 719, ta :32, obj:z, b:7, a:8] [nr: 720, ta :33, BOT] [nr: 721, ta :34, BOT] [nr: 722, ta :33, obj:y, b:47, a:61] [nr: 723, ta :32, obj:x, b:24, a:38] [nr: 724, ta :32, commit] [nr: 725, ta :33, obj:x, b:38, a:67] [nr: 726, ta :34, obj:z, b:8, a:72] (a) What is the state of Stable Database Page 2 after crash recovery [2 marks] Select one alternative: (b) What is the state of Stable Database Page 1 after crash recovery [2 marks] y = 61, z = 72 y = 47, z = 7 y = 61, z = 7 None of the others y = 47, z = 8 1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams) 16/22 Select one alternative (c) Which pages on the stable database had uncommitted writes on it at the time of the crash [3 marks] Select one alternative x=24 x=67 x=38 None of the others x=61 Page 1 only No page Page 2 only None of the others Both Page 1 and Page2 Maximum marks: 7 1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams) 17/22 11 In this question, 1 point is equivalent to 1 mark. 1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams) 18/22 Select one alternative: 1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams) 19/22 Select one alternative Select one alternative Select one alternative 6 5 4 2 3 Right after inserting 8 None of these. Right after inserting 1 Right after inserting 7 Right after inserting 5 15 75 150 30 375 Maximum marks: 10 1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams) 20/22 12 In this question, 1 point is equivalent to 1 mark. i) (2 points) What is the global depth of the resulting directory table ii) (2 points) What is the local depth of the bucket containing 78 iii) (2 points) What is the local depth of the bucket containing 117 i) (2 points) Which key will first make the directory double in size ii) (2 points) How many different block pointers are there in the resulting directory Maximum marks: 10 1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams) 21/22 13 In this question, 1 point is equivalent to 1 mark. i. (2 points) How many passes (including pass 0) are engaged by the sorting ii. (2 points) How many I/Os are engaged by the sorting Maximum marks: 8 1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams) 22/22 14 A popular social app has three functions, i.e., sending messages to contacts, receiving message from contacts and displaying the number of unread messages on the upper right corner of the app logo. However, users are reflecting the inconsistency between the displayed number and the number of unread messages. Specifically, when the number displayed is (updated) increased, one usually has to wait for a long time to see the new messages. (a) Among the following statements, which can be the possible reason(s) for the inconsistency [3 marks] Select one or more alternatives: (b) Which of the following statements is (are) correct [4 marks] Select one or more alternatives The app treats user with a high number of contacts differently with the other users. The app only keeps a list of sent messages for each user. None of these The app has a cache for some of the user in storing their received message. The app only keeps a list of received messages for each user. The app cannot handle the load produced by the current users while a more scalable architecture should be deployed. Data stored in columns (column store) cannot use B-tree to speed up searching. LSM-tree is better than B-tree in searching efficiency. LSM-tree is ideal for write-intensive applications because it extensively engages sequential writing. RAID can speed up data read by introducing redundancy, which will slow down the data writing. Maximum marks: 7