ISI2012-PCB-CS-6a

Question

Consider a LIBRARY database consisting of the following entity sets:

• Book (bookid, title, publishername)
• Book authors (bookid, authorname)
• Publisher (publishername, address, phonenumber)
• Bookcopies (bookid, accessionnumber)
• Book loans (bookid, cardnumber, issuedate, duedate)
• Borrower (cardnumber, name, address, phonenumber)

Write a relational algebra expression for retrieving the names of the borrowers who do not have any book issued. Hence write an equivalent SQL statement for the above query.

Comments

join then ”— “ 

Answer 1

Select br.borrower_name

from borrower br

where NOT EXISTS( select * from Book Loans bl where br.cardnumber=bl.cardnumber)

It basically says that if there does not exist any tuple for a cardnumber which means that no book has been issued to him, then select such borrower name

Answer 2

π_name(σ_bookid=NULL(borrower (left outer join) book loans))

Answer 3

Relational algebra query-

$\pi {Borrower.name }(Borrower - \pi Borrower.cardnumber,Borrower.name,Borrower.address,Borrower.phonenumber(\sigma Brrower.cardnumber = BookLoan.cardnumber(Brrower\times BookLoan)))$

SQL

select Borrower.name

from(select * from Borrower)

minus

(select Borrower.(*)

from Borrower W,BookLoan B

where W.cardnumber=B.cardnumber)