use the following database: BOOK(pages,title,isbn,category,author,year,press) PA
ID: 3804185 • Letter: U
Question
use the following database:
BOOK(pages,title,isbn,category,author,year,press)
PATRON(name,id,age,address)
BORROWS(bisbn,pid,day,month,year)
Assume that isbn is the primary key in BOOK and id is the primary key in PATRON. The category of a book can be one of ’fiction’, ’non-fiction’, ’comedy’, ’biography’, or similar; author is the name of the author of the book (note that this design assumes each book has only one author).
In BORROWS, bisbn is a foreign key to BOOK and pid is a foreign key to PATRON, and together they form the primary key. This means that a patron may borrow several books and a book may be borrowed by several patrons.
1. Relational Algebra Write the queries below in relational algebra. You can use (and reuse) temporary relations, but no grouping or aggregates.
(a) List the authors who have written a comedy and a biography.
(b) List the authors who have written a comedy or a biography.
(c) List the names of patrons who have borrowed two or more books.
(d) List the isbn of books that have never been borrowed by a patron over 50 years old.
(e) List the names of patrons who have borrowed one of the books that Jim Jones (another patron) has borrowed.
Explanation / Answer
Please find the required SQL queries below:
a) SELECT b1.author FROM BOOK b1, BOOK b2 WHERE b1.category = 'comedy' and b2.category = 'biography' and b1.author = b2.author;
b) SELECT author FROM BOOK WHERE category in ('comedy','biography');
c) SELECT p.name,p.id FROM PATRON p, BORROWS b WHERE p.id = b.pid GROUP BY p.id WHERE COUNT(p.id) >= 2;
d) SELECT b.isbn FROM BOOK b, PATRON p, BORROWS br WHERE p.id = br.pid and p.age > 50 and b.isbn not in (SELECT bisbn FROM BORROWS);
e) SELECT p.name FROM BOOK b, PATRON p, BORROWS br WHERE b.isbn = br.bisbn and p.id = br.pid and b.isbn in (SELECT br2.bisbn FROM PATRON p1, BORROWS br2 WHERE p1.name = 'Jim Jones' and p1.id = br2.pid);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.