CSCI585 Fall 18 Midterm Exam October 19th 2018 CLOSED book and notes No electronic devices DO YOUR OWN WORK Duration 1 hour If you are discovered to have cheated in any manner you will get a 0 and be reported to SJACS If you continue working on the exam after time is up you will get a 0 This document contains 12 pages including this one Signature Number of Points Problem Set 5 5 6 7 7 4 1 35 Q1 Q2 Q3 Q4 Q5 Q6 Q7 Total Q1 5 points total ER MODELING You are required to fill in the five blanks in the ER Diagram of a library database so it meets the following requirements For blanks 1 and 2 please write the key type For blanks 3 4 and 5 please draw an edge to represent the relationship between its entities Feel free to draw edges on the diagram but please copy them on the blanks as well to be graded The library has two types of items to check out books and media For each item the database needs to record its unique Item ID title contributor and publisher Each item is also assigned one category like Science Art History and so on and each category is assigned to one or more items Each item can be checked out by at most one student and the database should record who borrowed the item and due date for return For a book the database should record its number of pages For a media item the database should record number of CDs contained in it All items should be in the database regardless whether they are available or have been already checked out Students can borrow zero one or more items from the library Each student has a unique Student ID The database should record all students Student IDs and names Solution PK FK1 FK2 or just FK B 3 points Sketch the basic ER diagram schema show entities attributes and A 2 points Write a brief description of what the following query does The semantics Q2 5 points total SQL should be straightforward but you can make any reasonable assumptions ie Viterbi is a school within USC etc connections between them relationships Table names are uscstudent course coursedescription uscschool semester and studentsemesterenrollment SELECT stu student id stu fname stu lname stu email totalunits FROM uscstudent stu JOIN SELECT uscstudent student id Sum course course numofunits AS totalunits FROM SELECT FROM studentsemesterenrollment sse JOIN uscstudent scs ON sse student id scs student id JOIN semester sem ON sse semester id sem course id sem JOIN course c ON sem semester code c semester code JOIN coursedescription cd ON c course id cd course id JOIN uscschool sch ON sch school id cd school id WHERE uscschool school name VITERBI AND semester semester date BETWEEN 01 JAN 18 AND 31 DEC 18 GROUP BY uscstudent student id tommy ON stu student id tommy student id WHERE totalunits SELECT Max totalunits FROM SELECT uscstudent student id Sum course course numofunits AS totalunits FROM SELECT FROM studentsemesterenrollment sse JOIN uscstudent scs ON sse student id scs student id JOIN semester sem ON sse semester id sem course id JOIN course c ON sem semester code c semester code JOIN coursedescription cd ON c course id cd course id JOIN uscschool sch ON sch school id cd school id WHERE uscschool school name VITERBI AND semester semester date BETWEEN 01 JAN 18 AND 31 DEC 18 GROUP BY uscstudent student id sem Q2 Solution This is a query to display the student id student first name student last name e mail and total course units taken for the student who took the most Viterbi school classes between January 1 2018 and December 31 2018 The following sub query FROM SELECT FROM studentsemesterenrollment sse JOIN uscstudent scs ON sse student id scs student id JOIN semester sem ON sse semester id sem course id sem Is a bridge table that links the uscstudent and semester tables with M N relationship The rest should be clear with the following diagram Q3 6 points total NORMALIZATION Show dependency diagram and normalize the following table in 3 NF StudentID Name Age Course ID Course Name 12 Alex 19 CSCI 511 C CSCI 510 Java 20 CSCI 511 C Bin CSCI 670 Algorithms Registered On 08 11 2018 08 12 2018 08 05 2018 08 05 2018 08 15 2018 CSCI 511 C 08 11 2018 CSCI 585 Database Systems 08 11 2018 08 09 2018 08 09 2018 Tracy 20 CSCI 520 Math CSCI 510 Java Young 18 CSCI 550 Data Structures 123 32 133 Solution Q4 7 points TRANSACTION MANAGEMENT You are given the example tables that represent information of a factory a retailer and a customer Each table has information of products and their counts Also provided is a transaction log on the next page which contains 2 transactions one represents production of 100 products from factory to retailer the other represents a purchase of 150 products by a customer 1 Consider the case that locking is not properly implemented in the DBMS Discuss whether the results of the two transactions are deterministic No need to consider other external transaction but failure or roll back can happen 2 Consider that the DBMS in use is implementing a locking mechanism Is two phase locking required to ensure correctness of the two transactions State your reasons No need to consider other external transactions but failure or roll back can happen 3 Consider that pessimistic locking is implemented with two phase locking protocol Create a chronological list of locking unlocking and data manipulation activity that would occur during the completion of the two given transactions No step fails and no rollback happens Example tables FACTORY PRODUCT ID PRODUCT COUNT 42 1000 PRODUCT ID PRODUCT COUNT 42 58 RETAILER CUSTOMER CUSTOMER ID PRODUCT ID PRODUCT COUNT 1007 42 3 Q4 Continued Transaction log TRL ID TRX NUM 225 101 214 216 233 220 227 101 101 101 105 105 239 105 243 105 252 105 Solution Null 214 216 225 Null 220 227 239 243 PREV PTR NEXT PTR OPERATION DESCRIPTION Start Transaction 225 233 Null 239 243 252 Null Update RETAILER table on the row with PRODUCT ID 42 and add 100 to PRODUCT COUNT Update FACTORY table on the row with PRODUCT ID 42 and subtract 100 from PRODUCT COUNT End of Transaction Start Transaction Check that PRODUCT ID 42 in RETAILER table has PRODUCT COUNT 150 and wait until the condition is met Update RETAILER table on the row with PRODUCT ID 42 and subtract 150 to PRODUCT COUNT Update CUSTOMER table on the row with PRODUCT ID 42 and CUSTOMER ID 1007 and then add 100 to PRODUCT COUNT End of Transaction 1 The result will be non deterministic if no locking is implemented Even if transaction 105 checks that PRODUCT ID 42 should have at least 150 items before proceeding