Book Database

The data requirements of the library application are summarized as follows: BOOK entity is identified by BookId, it has title and multiple author names. PUBLISHER entity consists of Name, Address, and Phone attributes. Name is the key for the PUBLISHER. LIBRARY_BRANCH entity has BranchId as a key and Branchname attribute additionally. BORROWER entity has BrowerId as key and additionally has name, address, phone attributes. Each LIBRARY_BRANCH has one or more copies of the same book. In such a case, noOfCopies attribute needs to be maintained by the relationship. A book is published by only one publisher. A book can be loaned to a borrower at a specific library branch.

• Draw ER diagram for the conceptual schema of the library database application.
• Map the conceptual schema to database relations.
• Explain at least two referential integrity constraints.
• Write SQL queries for the following queries on the library database:
  • How many copies of the book title "Database Systems" are owned by the library branch "Lehman"? (Use natural join)
  • Retrieve the names of all borrowers who do not have any books checked out.
  • For each library branch, retrieve the name and the total number of books loaned out from the branch.
  • For each book authored (co-authored) by "Jeff Ullman", retrieve the title and the number of copies owned by the library branch "Columbia".