Branch(BranchID, Address, City)

Book(Call#, Seq#, BranchID, PatronID, DueDate)

BookTitle(Call#, Title)

TitleAuthor(Call#, AuthorName)

TitleSubject(Call#, Subject)

Patron(PatronID, Name, Address, BranchID, FinesDue)

Seq# is a unique number for each copy of the same book.

BranchID in Patron refers to the branch where a person got his/her library card.

The row totals are as follows:

Currently, the database is centralized and standard disk storage is being used.

The Director of the Library System has come to you because they are experiencing performance problems. Patrons are calling for the return of the card catalog system saying that it would be faster than the computer system that they use to locate their books.

1. Create a denormalized schema that would improve the performance of the "typical" patron queries: search for a book in the current branch by title, subject, or author with call number returned.

2. Five "warnings" about denormalization were provided in class. Apply these to the denormalized schema you developed for #1. Assume you were drafting a memo to the Director of the Library System. Use the warnings to explain the cost and impact of denormalization to the director.

3. Assume that the database must remain centralized (stored on a server at the main branch in Oakland). Show how you would use vertical and/or horizontal partitioning of the original database schema to improve patron query performance. What are the costs or impacts of your partitioning plan?

4. The Director has read a few books on SQL and written the following query. She wants a count of the number of over-due books on the subjects related to procrastination checked out by people who have more than $5 in fines. Currently, she manually counts the books by person that are returned. She complains to you that it "never seems to finish." You also discover that the last time she ran the query was when there were complaints from almost every branch about the performance of the database.

SELECT *

FROM Branch as Br, Book as B, BookTitle as BT, TitleAuthor as TA,

TitleSubject as TS, Patron as P

WHERE Br.BranchID = B.BranchID AND B.Call# = BT.Call# AND

BT.Call# = TA.Call# AND BT.Call# = TS.Call# AND

4. Explain why this query suffers from performance problems.

5. Assume that the DBMS being used does not enforce concurrency control. One patron had unpaid fines in the amount of $10 when the following three transactions related to this patron were processed at the same time:

(T1) Payment of $5 received in the mail

(T2) Payment of $3.50 made in person at the library

(T3) A book thought to still be checked out was found on the shelves and the patron was credited for a $6.00 fine.

Each of these transactions read the patron record when the total fines due was $10 (before any were committed). The updated patron record was returned to the database in the order shown above.