Questions

1. A library (such as Vancouver Island Regional Library) has many branches, each branch has a unique name and an address. All branches collectively owns a collection of books. Each book has a unique book id, a title, a genre, and a publication year. Each book is written by one or many authors. Each author has a unique id and a name. The library may own multiple copies of a book and wants to keep track of where these book copies are located, or when they are due if they are checked out.
   Currently, the library uses one database table, called BookData, to store these data.
   This table/relation includes the following columns (the explanation of each column is shown in its corresponding brackets):
   • bid - (book id)
   • title - (book's title)
   • aid - (id of this book's author)
   • name - (corresponding author's name)
   • genre - (book's genre)
   • pubYear - (publication year)
   • pages - (number of pages in the book)
   • cid - (id of the copy of this book)
   • libName - (name of the library branch, in which the book copy belongs)
   • libAddress - (corresponding library branch's address)
   • dueDate - (due date if this book copy is currently checked out)
   Your tasks:
   1. Based on the domain knowledge description, identify a complete and minimum set of non-trivial functional dependencies that should hold on the relation BookData.
   2. Is the current one-relation database design used by the library a good one? Justify your answer.
   3. Design an improved database conceptual schema that captures the specification implied by the domain description and the schema of the relation BookData.
   4. Assuming all the relations proposed in your previous design step are successfully created in the same database where table BookData resides, describe how you can losslessly transfer all the data in the table BookData to these newly created tables.
2. An operation R_i(x) means transaction i wants to read the value of database object x, and W_i(x) means transaction i wants to write a value to database object x.
   Suppose that a scheduler receives the following sequence of operation request from some transactions:

   R_1(x) R_2(y) R_3(y) R_2(x) W_1(y) R_2(z) R_3(x) W_3(z) R_1(z)

   1. If the scheduler executes each request in the order it receives them (that is, the generated schedule looks exactly the same as the request sequence), is the generated schedule conflict serializable? Justify your answer.
   2. Is the above generated schedule cascadeless? Justify your answer.
   3. Suppose each transaction commits right after their last operation request in the given sequence, and the scheduler uses strict two phase locking protocol, show the schedule generated by such scheduler based on the given sequence of operation request.
      If a deadlock happened in the process of generating the schedule, make the scheduler adopt the wait-die protocol to prevent the deadlock situation from happening by assuming that transaction T_1 is older than transaction T_2, and transaction T_2 is older than transaction T_3.

How to Submit

You can submit your assignment solutions using one of the following two ways:

• Submit a hard copy before the due date/time.
• Login to your VIU Learn account, find the CSCI 370 course page, click on the "Assessment" drop-down menu, click on the "Assignments" item, then click on the folder named "A9". Then you can click on the "Add a File" button to browse and upload your solution file(s).