|CPSC 343||Database Theory and Practice||Fall 2017|
You will once again be working with the library database, this time with one additional table to keep track of borrowers' unpaid fines.
BOOK(Book_id,Title,Publisher_name) BOOK_AUTHORS(Book_id,Author_name) PUBLISHER(Name,Address,Phone) BOOK_COPIES(Book_id,Branch_id,No_of_copies) BOOK_LOANS(Book_id,Branch_id,Card_no,Date_out,Due_date) LIBRARY_BRANCH(Branch_id,Branch_name,Address) BORROWER(Card_no,Name,Address,Phone) FINES(Card_no,Amount)
Key attributes are underlined. The foreign key constraints are the following:
Assume that BOOK_LOANS contains only information about books currently checked out - when a book is returned, the corresponding entry is removed from BOOK_LOANS.
For each of the following, explain how the constraint is already or could be enforced in the database: via column-based settings (by choosing the domain or, default value, or by setting primary key, NOT NULL, or UNIQUE tags), foreign keys, or triggers. Be specific; don't just say "domain", for example, but indicate which column and what particular domain. For triggers, give the full CREATE TRIGGER command to accomplish the task. Modifications that violate the constraint should be disallowed unless otherwise specified. If there is more than one way to enforce a constraint, choose the best or most appropriate solution. (Only use triggers if there isn't another alternative.)
Treat each question separately, that is, don't assume that constraints imposed by earlier questions are in place when you answer a later question.
If you want to test your solutions, you can work with the username_library3 database. This version has the additional FINES table and is set up with some sample data. Note: once you've connected to the server with MySQL Workbench, make sure that username_library3 is set as the default schema so that any changes you make will apply to the correct database. To define a trigger in MySQL Workbench, right-click on the table name in the "Schemas" tab, choose "Alter Table...", click on the "Triggers" tab, and then right-click on the desired "when" and "event". You will need to "Add new trigger" to get an editor window for the trigger, then "Apply" when you have finished specifying the trigger action.
Every book loan must refer to a book in BOOK, a branch in LIBRARY_BRANCH, and a borrower in BORROWER.
A book that is loaned out must have both the date it was checked out and the due date recorded.
The check out date defaults to the current date and the due date to one month after the check out date.
A book's due date must be after the date it is checked out.
A book cannot be loaned out for more than one month. Adjust the due date accordingly.
A book can only be loaned out from a branch that has at least one copy of that book.
Book IDs uniquely identify each book.
Every book must have a title.
There cannot be two different books with the same title.
There cannot be two different books with the same title from one publisher.
The number of copies for an entry in BOOK_COPIES must be non-negative.
The number of copies for an entry in BOOK_COPIES must be at least 1.
A borrower cannot have more than 10 books checked out at a time from a single branch.
A borrower may not check out a book if he or she has any overdue books at any branch (as of the check out date) or any unpaid fines.
There must be an entry in FINES for every borrower. (Add an entry to FINES when needed; the amount will be 0 if there are no unpaid fines.)
When an overdue book is returned, the amount of the fine (25 cents per day) is added to the borrower's unpaid fines.
Unpaid fines max out at $200. Adjust the amount accordingly.
A borrower cannot be deleted if he or she still has books checked out.
A borrower cannot be deleted if he or she has any unpaid fines.
Check back for solutions.