1) Retrieve the names of customers who borrowed Harry Potter book from Towson Br

Question

1) Retrieve the names of customers who borrowed Harry Potter book from Towson Branch

2) List the authors with books published by Pearson

3) Find the list of books including authors name borrowed by customer name Stewart

4) For each book, list the book title and the total number of authors.

5) Retrieve the names of customer who borrow book from all the branches.

6) Retrieve the names of authors whose books have never been borrowed.

7) For each branch, retrieve the branch name, and the average number of book copies in that branch.

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

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

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

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

BOOK Book_idTitle Publisher name BOOK AUTHORS Book idAuthor name PUBLISHER Name Address Phone BOOK_COPIES Book id Branch id No of copies BOOK LOANS Book id Branch_idCardno 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

Explanation / Answer

The below symbols used in the expressions are to be read as

S - Select
P - Project
* - Natural Join
- - Set Difference
F - Aggregate Function

(a)
A <-- P Number_Of_Copies ( ( S Branch_Name='Sharpstown' (LIBRARY_BRANCH) ) (BOOK_COPIES ( S Title='The Lost Tribe' (BOOK) ) ) )
  
First do a Natural join between BOOK_COPIES and BOOK to get number of copies with the title 'The Lost Tribe'.
Then do Natural join again between result obtained in earlier step and Branch_Name to get all the copies of book owned by
library branch 'Sharpstown' and project the result.
  
(b) Question not given

(c)
NO_CHECKOUT_BORROWERS <-- P Card_No (BORROWER) - P Card_No (BOOK_LOANS)
RESULT <-- P Name (BORROWER * NO_CHECKOUT_BORROWERS)
  
First get the difference between BORROWER table and BOOKLOANS to get borrowers who have not taken the books.
Then do a natural join with BORROWER table and project the names of all borrowers without books checked out.
  
(d)
S <-- P Branch_Id ( S Branch_Name='Sharpstown' (LIBRARY-BRANCH) )
B_FROM_S <-- P Book_Id,Card_No ( ( S DueDate='today' (BOOKLOANS) ) * S )
RESULT <-- P Title,Name,Address ( BOOK BORROWER B_FROM_S )
  
First find the branch Id by taking the set difference between LIBRARY and BRANCH table to get all the branches owned by Sharpstown.
From the result obtained from previous step, do a Natural join BOOKLOANS table to get the entries with DueDate as today.
Finally, do Natural join between previous result and BORROWER and BOOK to and project thr result.
(e)
R(Branch_Id,Total) <-- Branch_Id F COUNT(Book_Id, Card_No) (BOOK_LOANS)
RESULT <-- P Branch_Name, Total (R * LIBRARY_BRANCH)
  
First, get the Branch_Id and Total books rented out by Sharpstown by taking the Aggregate function COUNT.
Do a natural join of LIBRARY_BRANCH with the previous result and project the Branch_Name.
  
(f)
B(Card_No,Total_Checkout) <-- Card_No F COUNT(Book_Id) (BOOK_LOANS)
FivePeople <-- S Total_Checkout > 5 (B)
RESULT <-- P Name,Address,Total_Checkout ( FivePeople * BORROWER)
  
First find a aggregate count on BOOKS_Id and BOOKLOANS to get Card_No and Total_Checkout.
Now, select borrowers who have more than 5 books checked out.
Project name, address and total checkout by doing a natural join between earlier result and BORROWER table.

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

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