I need help with part 5 Q.11 Consider the following data requirements of a libra

Question

I need help with part 5

Q.11 Consider the following data requirements of a library database for keeping track of library members' borrowing books from a few library branches The data requirements of the library application are summarized as follows BOOK entity is identified by BookId, it has title, publisher name, and multiple author names PUBLISHER entity consists of Name, Address, and Phone attributes. Name is the key for the PUBLISHER. Note: Phone number is unique LIBRARY BRANCH entity has Branchld as a key and other attributes such as Library Banch Name, Branch Address, Branch Phone Number. Note: Phone number is unique BORROWER (or Library Member) entity has MemberCID 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 library member at a specific library branch When a library member checks out a book, checking out date and time and due date and time must be stored in the database

Explanation / Answer

a. Select * from CheckOutTable where noOfBooksCheckedOut is not null;

b. It can be divided into two pieces, one is find the Id of the book where a title is given, second is retrieve the row in NoOfCopies table for given branch with bookId in first part.

select NoOfCopies.branchId, NoOfCopies.noOfCopies, Book.title from NoOfCopies inner join Book on Book.bookId = NoOfCopies.bookId where bookId in (select bookId from Book where title = 'a book title');

(inner join because we want to retrieve details of Book too, in this case bookTitle)

c. select branchId, noOfBooksCheckedOut from CheckOutTable where noOfBooksCheckedOut is not null;

d. select Borrower.MemberCID, Borrower.name from CheckOutTable join Borrower on CheckOutTable.MemberCID = Borrower.MemberCID where exists (select * from CheckOutTable where noOfBooksCheckedOut is not null)

(exists is to use as true/false condition in if block)

e. select Borrower.MemberCID, Borrower.name from CheckOutTable join Borrower on CheckOutTable.MemberCID = Borrower.MemberCID where exists (select * from CheckOutTable where noOfBooksCheckedOut is null)

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.