10
Assume a bookshop has database with the following schema of six relations:
Authors (au_id, au_lname, au_fname, phone, address)
Publishers (pub_id, pub_name, city, state, country)
Sales (stor_id, ord_num, qty, title_id)
Stores (stor_id, stor_name, stor_address, city, state, zip)
Titleauthor (au_id, title_id, royaltyper, au_ord)
Titles (title_id, title, type, pub_id, price, pubdate)
Write the following queries in relational algebra:
i. List the authors first and last names for the book titled ‘Life Without Fear’
ii List the titles of the books sold at Barnum’s. (stor_name)
iii. Give the last names (au_lname) and au_ids of the authors whose books have sold in the store named Barnum’s. Assign an alias to your answer.
iv. Using your alias from the previous problem, give the last names and au_ids of the authors whose books have not sold in the store named Barnum’s
v. List the titles of books, which have not sold any copies at any store (no books sold at all.)
vi. List the titles of the books, which have only sold at Barnum’s. (Do not list the books, which have not sold anywhere.)
vii. List the titles of the books, which have sold at both Barnum’s and Bookbeat. (Two stor_names)
viii. List the titles and stor_names for each book when the book has sold and the title only (with null for the stor_name) if the book has not sold. (Use an outer join.)
ix. List the stor_ids of the stores which have sold all books published by New Moon Books (pub_name)
x. List the titles of the books, which have sold at all stores.
