Q1. Describe the mapping of the given to the relational database schema.
a) Aggregation
b) Generalization or Specialization
c) Strong or weak entity sets
d) Ternary Relationship
Q2. Consider the given LIBRARY relational schema:
Book (Book_id, Title, Publisher_name)
Book_Authors (Book_id, Author_name)
Publisher (Name, Address, Phone)
Book_copies (Book_id, Branch_id, No_of_copies)
Book_loans (Book_id, Branch_id, Card_no, Date_out, Due_date)
Library_branch (Branch_id, Branch_name, Address)
Borrower (Card_no, Name, Address, Phone)
Write down the relational algebra expression for the given queries:
a) Determine the number of copies of the book titled TLT owned by library branch whose name is "S"?
b) Determine the number of copies of the book titled TLT owned by each and every library branch?
c) Determine the names of all borrowers who don’t have any books checked out.
d) Determine the book title, the borrower's name, and the borrower's address for each book which is loaned out from branch named "S" and whose Due Date is today.
e) For each and every library branch, determine the branch name and the total number of books loaned from that branch.