Given the following database: Book(Id,Title,Publisher) BookCopies(BookId,BranchI

Question

Given the following database:

Book(Id,Title,Publisher)

BookCopies(BookId,BranchId,NumCopies)

Branch(BranchId,BranchName,Address)

Author(BookId,authorName)

write the following queries using relational algebra. You can use intermediate relations in the case 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

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

select distinct Branch.BranchName
from Branch, BookCopies, Book
where Branch.BranchId = BookCopies.BranchId
and BookCopies.BookId = Book.Id
and Book.Publisher = '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.

select Branch.BranchName, Book.Title, BookCopies.NumCopies
from Branch, BookCopies, Book
where Branch.BranchId = BookCopies.BranchId
and BookCopies.BookId = Book.Id

(c) List the author’s name for each book that has copies in the NW branch (BranchID is NW)

select Book.Title, Author.authorName
from BookCopies, Book, Author
where BookCopies.BranchId = 'NW'
and BookCopies.BookId = Book.Id
and Author.BookId = Book.Id

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

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