Question

BOOK Book id Title Publisher_name BOOK_AUTHORS Book idAuthor name PUBLISHER Name Address Phone BOOK COPIES Book_idBranch_id| No_of_copies BOOK LOANS Book id Branch id Card no Date out Due_date LIBRARY BRANCH Branch_id Branch_name Address BORROWER Figure 4.6 A relational database schema for a LIBRARY database. Card no Name Address Phone

Specify relational calculus expression for the following descriptions on the Library database schema shown in Figure 4.6.

a) Retrieve Publishers’ name and phone number whose books where borrowed from Baltimore Branch.

b) List the book title, book id and branch name with less than 10 copies

c) List customer card number, customer name, branch name and book title for books which are currently on loan.

Answer 1

TIPUBLISHER Name PUBLISHER.Phone (PUBLISHER 0 PUBLISHER.Name=BOOK Publisher_name (BOOK BOOK Book_id=OOK_LOANS Book_id OLIBRARY BRANCH.Branch name=BaltimoreBranch' DOO _ BOOK LOANS Branch_id=LIBRARY_BRANCH Branch_id LIBRARY_BRANCH))) Explanation: According to the above expression, the rows which have the Branch name as Baltimore Branch are selected by joining BOOK LOANS and LIBRARY BRANCH, then the join is applied on the result obtained with BOOK such that the Book id column is same. Finally, the PROJECT operator is used to select the Publisher name and Publisher Phone by joining the result wit PUBLISHER, such that the Publisher name is the same.

B) (ONO "BOOK.Title, BOOK Book_id, LIBRARY_BRANCH.Branch_name (No_of_copies<10 (BOOK COPIES) BOOK_COPIES Branch_id=LBRARY_BRANCH.Branch_id (LIBRARY_BRANCH)). OS DADOOK Best in (BOOK)) BOOK_COPIES B ook_id=BOOK Book_id Explanation: First, the join is applied on the Book id column in LIBRARY BRANCH and BOOK. The result is then joined with BOOK_COPIES on the column Branch_id and then those rows are selected in which the number of copies are less than 10. The columns, Title, Book id and Branch name are selected from the result.

BORROWER.Card no, BORROWER.Name, LIBRARY_BRANCH.Branch name BOOK.Title DUN RY BRANCH.Branch name BOOK Title (BORROWER BORROWER.Card_no=BOOK_LOANS. Card_no (LIBRARY_BRANCH LIBRARY_BRANCH.Branch_id=BOOK_LOANS.Branch_id (BOOKS BOOK LOANS BOOKS.Book_id=BOOK LOANS.Book_id Explanation: In the above expression, BOOKS and BOOK_LOANS are joined on Book_id. The result is then joined with LIBRARY BRANCH on Branch id and then with BORROWER on Card no. Finally, Card no, Borrower name, Branch name and Title are selected.