BOOK ( Bookid , Title , Publishername )
BOOK_AUTHORS ( Bookid , Authorname )
PUBLISHER ( Publishername , Address , Phone )
BOOK_COPIES ( Bookid , Branchid , No_Of_Copies )
LIBRARY_BRANCH ( Branchid , Branchname , Address )
BOOK_LOANS ( Bookid , Branchid , Cardno , DataOut , Duedata )
BORROWER ( Cardno , Name , Address , Phone )
a) Write appropriate SQL DDL statements for declaring the BOOK_AUTHORS relation.
b) Give an expressions in relational algebra to express the following queries :
Q1: Retrieve the name of all borrowers who do not have any books checked out.
Q2: For each book that is loaned out from the “sharpstown” branch and whose DueDate is today , retrieve the book title , the borrower’s name , and the borrower’s address .
c) Give an expressions in SQL to express the following queries :
Q1: How many copies of the book titled The Lost Tribe are owned by the library branch whose name is “sharpstown” ?
Q2: For each library branch , retrieve the branch name and that the total number of books loaned out rom that branch .
Q3: Retrieve the name , address , and number of books checked out for all borrowers who have more than five books checked out .
Q4: For each book authored ( or co-authored ) by “ Stephen King “ , retrieve the title and the number of copies owned by the library branch whose name is “ central ” .
d) Record the fact that the manager didn’t maintain information about the book named “ T&G ” ,i.e. remove information about “ T&G ” .
