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".
Academic Honesty!
It is not our intention to break the school's academic policy. Projects posted are only used as a reference and should not be submitted as is. We are not held liable for any misuse of the solutions. Please see the frequently asked questions page for further questions and inquiries.
Kindly fill out the form. Please provide a valid email address and we'll get back to you in less than 24 hours. We will be sending an invoice through PayPal upon confirmation. We are a non profit organization however we need an amount to keep this organization running, and to be able to complete our research and development.