Question 1: 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.
Question 2: Define the two principal integrity rules for the relational model. Discuss why it is desirable to enforce these rules? (3 Marks)
Question 3: What is a view? Discuss the difference between a view and a base relation. Explain what happens when a user accesses a database through a view.
Question 4: Discuss the advantages and disadvantages of hierarchical database management system in comparison with RDBMS. Discuss types of applications suitable for hierarchical DBMS and RDBMS.
Question 5: Produce an E-R diagram, which documents the entities, attributes, relationships and cardinality involved in the Life Insurance Management System. Create a relational schema to hold the necessary information. Identify the tables and perform normalization to the tables to the required normal forms. Note: Assumptions can be made wherever necessary.