Question

Database Design PRACTICE DATABASE DESIGN PROBLEM Imagine that your town library wants to keep track of its business in a database, and that you have been called in to build the database. You talk to the town librarian, review the old paper-based records, and watch people use the library for a few days. You learn the following about the library: 1. Any resident of the town can get a library card simply by asking for one. The library considers each cardholder a member of the library. The librarian wants to be able to contact members by telephone and by mail. She calls members when books are overdue or when requested materials become available. She likes to mail a thank-you note to each patron on his or her anniversary of becoming a member of the library Without a database, contacting members efficiently can be difficult; for example, multiple members can have the same name. Also, a parent and a child might have the same first and last name, live at the same address, and share a phone. The librarian tries to keep track of each member's reading interests. When new books come in the librarian alerts members whose interests match those books. For example, long-time member Sue Doaks is interested in reading Western novels, growing orchids, and baking bread. There must be some way to mateh her interests with available books. One complication is that, although the librarian wants to track all of a member's reading interests, she wants to classify each book as being in just one category of interest. For example, the classic gardening book Orchids of France would be classified as a book about orchids or a book about France, but not both. 2. 3. The library stocks thousands of books. Each book has a title and any number of authors. Also more than one book in the library might have the same title. Similarly, multiple authors might have the same name A writer could be the author of more than one book 4. 6. A book will be checked out repeatedly as time goes on. For example, Orchids of France could be checked out by one member in March, by another member in July, and by another member in September 7. The library must be able to identify whether a book is checked out 8. A member can check out any number of books in one visit. Also, a member might visit the library more than once a day to check out books All books that are checked out are due back in two weeks, with no exceptions. The librarian would like to have an automated way of generating an overdue book list each day so she can telephone offending members The library has a number of employees. Each employee has a job title. The librarian is paid a salary, but other employees are paid by the hour. Employees clock in and out each day. Assume that all employees work only one shift per day and that all are paid weekly. Pay is deposited directly into an employee's checking account-no checks are hand-delivered. The data needs to include the librarian and all other employee 10. Design the library's database, following the rules set forth in this tutorial. Your instructor will specify th rmat of your work. Here are a few hints in the form of questions A book can hav e more than one author. An author can write more than one book. How would . u describe the relationship between books and authors? The library lends books for free, transaction for zero revenue, how would you handle the library's revenue-generating event? . of course. If you were to think of checking out a book as a sales .A member can borrow any number of books at one checkout. A book can be checked out more than once. How would you describe the relationship between checkouts and books?

Answer 1

use this as before to get the final result:

SELECT Branch_id, Branch_name, Book_id, Title, No_of_copies-NumCheckedOut AS OnShelf

FROM LIBRARY_BRANCH NATURAL JOIN BOOK_COPIES NATURAL JOIN

( ( SELECT Branch_id, Book_id, COUNT(*) AS NumCheckedOut

FROM BOOK_LOANS

WHERE Date_out < '2006-10-14' AND Due_date > '2006-10-14'

GROUP BY Branch_id, Book_id )

UNION

( SELECT Branch_id, Book_id, 0 AS NumCheckedOut

FROM BOOK_COPIES BC

WHERE NOT EXISTS ( SELECT *

FROM BOOK_LOANS BL

WHERE BC.Branch_id=BL.Branch_id AND BC.Book_id=BL.Book_id AND

Date_out < '2006-10-14' AND Due_date > '2006-10-14') ) ) A NATURAL JOIN BOOK

Delete any entries in BOOK_COPIES where the number of copies is 0.

DELETE

FROM BOOK_COPIES

WHERE No_of_copies = 0

Add 2 copies to each book owned by Wood Library.

The following works as long as there is only one library branch named 'Wood Library'. Use IN or = ANY to account for the possibility of multiple branches with the same name.

UPDATE BOOK_COPIES

SET No_of_copies = No_of_copies+2

WHERE Branch_id = ( SELECT Branch_id

FROM LIBRARY_BRANCH

WHERE Branch_name='Wood Library' )

Change the due date of any book checked out from Wood Library that is due before 2006-11-05 to 2006-12-31.

The following works as long as there is only one library branch named 'Wood Library'. Use IN or = ANY to account for the possibility of multiple branches with the same name.

UPDATE BOOK_LOANS

SET Due_date='2006-12-31'

WHERE Due_date < '2006-11-05' AND Branch_id = ( SELECT Branch_id

FROM LIBRARY_BRANCH

WHERE Branch_name='Wood Library' )