Book id Title Publisher name BOOK AUTHORS Book id Author name PUBLISHER Name T A

Question

Book id Title Publisher name BOOK AUTHORS Book id Author name PUBLISHER Name T Address Phone BOOK COPIES Book id Branch id No of copies BOOK LOANS Book id Branch id Card no T Date out Due date T LIBRARY BRANCH Branch id Branch name Address BORROWER Figure 6.14 Card no Name Address Phone A relational database schema for a LIBRARY database. Consider the LIBRARY relational database schema shown in Figure 6.14, which is used to keep track of books, borrowers, and book loans. Referential integrity constraints are shown as directed arcs in Figure 6.14. Write down relational expressions for the following queries: a. How many copies of the book titled The Lost Tribe are owned by the library branch whose name is Sharpstown'?

Explanation / Answer

Happy New Year

NOTE:
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 B
• Subjects

---