Given the following database: Book(Id, Title, Publisher) BookCopies(Bookld, Bran

Question

Given the following database: Book(Id, Title, Publisher) BookCopies(Bookld, Branchld, NumCopies) Branch(Branchld, BranchName, Address) Author(Bookld, authorName) write the following queries using relational algebra. You can use intermediate relations in the ease of harder expressions. Notice that there might be books with the same title published by different publishers. (a) List the names of the branches that have copies of books published by Addison-Wesley. (b) For each book and branch, list the name of the book, the name of the branch, and the number of copies of the book in the branch. (c) List the author's name for each book that has copies in the NW branch (BranchID is NW).

Explanation / Answer

Given databse is:

Book(Id, Title,Publisher)
BookCopies(BookId,BranchId,NumCopies)
Branch(BranchId,Branchname,Address)
Author(BookId,authorName)

a)List the names of all branches that have copies of books published by Addison-Weslwy.

Select BR.Branchname from Book BO,BookCopies BC,Branch BR where BO.Id=Bc.BookId AND BC.BranchId=BR.BranchId AND BO.Publisher= 'Addison-Weslwy';

b)For each book and branch list the name of the boook,the name of branch and the number of copies odf the book in te branch.

Select Distinct BO.Title,BR.Branchname,BC.NumCopies from Book BO,BookCopies BC,Branch BR where BO.Id=Bc.BookId AND BC.BranchId=BR.BranchId Order by BookId;

c)List the author's name for each book that has copies in the NW branch.

Select A.authorName from Book BO,BookCopies BC,Author A where BO.Id=Bc.BookId AND BO.Id=A.BookId AND BC.BranchId='NW';

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

---

---

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