=========================preview======================
(comp231)[2005](s)mid~PPSpider^_10177.pdf
Back to COMP231 Login to download
======================================================
COMP 231 - Spring 2005
MIDTERM EXAM
Name: Student ID:
Lecture Section: Tutorial Section:

All questions should be answered within the space provided after each problem .

PROBLEM 1 (15%) - ER Diagrams
Suppose a bookstore has the following five relational tables:
BOOK (BID, TITLE, AID, SUBJECT, QUANTITY-IN-STOCK)
AUTHOR (AID, FIRST-NAME, LAST-NAME)
CUSTOMER (CID, FIRST-NAME, LAST-NAME)
ORDER-DETAILS (OID, BID, QUANTITY)
ORDER (OID, CID, ORDER-YEAR)
ASSUMPTIONS:

Keys are underlined and foreign keys are in italics. Each author has authored at least one book in the store. Each book has exactly one author. Each order is made by exactly one customer and has one or more associated record in ORDER-DETAILS (e.g., an order may contain different books).
Draw an E-R diagram that results in these five tables.

State your assumptions (if any):
PROBLEM 2 (48%) - SQL, Algebra, Calculus
Using the tables of Problem 1, write the following queries in SQL, relational algebra, and domain relational calculus. Query 1: Find all distinct book titles of the author whose last name is Luo.
SQL (3%): SELECT DISTINCT B.TITLE FROM BOOK B, AUTHOR A WHERE A.LAST-NAME = Luo AND A.AID = B.AID
algebra (3%): TITILE (LAST-NAME = Luo (BOOK JOIN AUTHOR.AID = BOOK.AID AUTHOR))
calculus (3%): {<T> | . B,A, S, Q (<B,T,A,S,Q>BOOK AND . F, L (<A, F, L>AUTHOR AND L=Luo) )}

Query 2: Find the last name and first name of all authors who wrote books in both the subjects of ART and BUSINESS.
SQL (4%): SELECT A1.LASTNAME, A1.FIRSTNAME FROM AUTHOR A1, BOOK B1 WHERE A1.AID = B1.AID AND B1.SUBJECT = ART AND A1.AID IN (SELECT A2.AID FROM AUTHOR A2, BOOK B2 WHERE A2.AID = B2.AID AND B2.SUBJECT = BUSINESS)
algebra (4%): LAST-NAME,FIRST-NAME ( (AID, LAST-NAME, FIRST-NAME (BOOK.SUBJECT = ART (BOOK JOIN AUTHOR.AID = BOOK.AID AUTHOR))) (AID, LAST-NAME, FIRST-NAME (BOOK.SUBJECT =BUSINESS (BOOK JOIN AUTHOR.AID = BOOK.AID AUTHOR))))
calculus (4%): {<L,F> | . A (<A,F,L>AUTHOR AND . B1, T1, S1, Q1 (<B1,T1,A,S1,Q1>BOOK AND S1=ART) AND S2, Q2 (<B2,T2,A,S2,Q2>BOOK AND S2=BUSINESS))} . B2, T2,

Query 3: Find the ID of the book(s) with the largest quantity in stock. Query 4: Find the IDs of customers that have ordered all books in the subject of ART. (HINT: this is a division query)
SQL (4%): SELECT B1.BID FROM BOOK B1 WHERE B1.QUANTITY-IN-STOCK = (SELECT MAX(B2. QUANTITY-IN-STOCK) FROM BOOK B2)
algebra (4%): BID (BOOK) C B1.BID ( (B1.QUANTITY-IN-STOCK < B2. QUANTITY-IN-STOCK ( B1 (BOOK) X P B2 (BOOK)))
calculus (4%): {<B> | . T,A,S,Q (<B,T,A,S,Q>BOOK AND. . B1,T1,A1,S1,Q1 (<B1,T1,A1,S1,Q1>BOOK AND Q1>Q))}

SQL (5%): SELECT CID FROM CUSTOMER WHERE NOT EXISTS ((SELECT BID FROM BOOK WHERE SUBJECT = ART) EXCEPT (SELECT ORDERDETAILS.BID FROM ORDER, ORDERDETAILS WHERE ORDER.CID=CUSTOMER.CID AND ORDER.OID=ORDERDETAILS.OID))
algebra (5%): CID, BID (OR