=========================preview======================
(COMP231)eaaddf - midterm_fall06_solution.pdf
Back to COMP231 Login to download
======================================================
COMP 231 - Fall 2006
MIDTERM EXAM

Name: SOLUTION

ITSC Account:

Student ID:

Program:




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

PROBLEM 1 (15%) - ER Diagrams
Suppose an art gallery has a database system containing the following five relational tables:

PAINTING (PID, TITLE, AID, CATEGORY, PRICE)

ARTIST (AID, NAME, CITY)

CUSTOMER (CID, NAME, CREDIT)

PURCHASE (CID, PID, YEAR)

INTEREST (CID, AID)

ASSUMPTIONS:
Keys are underlined and foreign keys are in italics. Each artist has painted at least one painting. Each painting is painted by exactly one artist. Each customer has purchased at least one painting and each painting may be purchased by zero or more customers. A customer can be interested in the work of any number of artists and an artist may draw interest from any number of customers.

Draw an E-R diagram that results in these five tables.


State your assumptions (if any):

Attributes are omitted in the diagram.PROBLEM 2 (48%) - SQL, Algebra, Calculus
Painting
Artist
Customer
Interest
Purchase
Paint

Using the tables of Problem 1, write the following queries in SQL, relational algebra, and domain relational calculus.

Query 1: Find the distinct titles of all paintings painted by artists from the city of Monterey.
SQL (3%):
SELECT DISTINCT P.TITLE
FROM PAINTING P, ARTIST A
WHERE A.CITY = Monterey AND A.AID = P.AID




algebra (3%):

TITILE (CITY = Monterey (PAINTING JOIN ARTIST))


calculus (3%):

{<T> | . I,A, C, P (<I,T,A,C,P>PAINTING AND . N, CI (<A, N, CI>ARTIST AND CI=Monterey) )}





Query 2: Find the unique IDs of all customers who bought paintings from both PORTRAIT and STILL LIFE categories.
SQL (4%):
SELECT DISTINCT PU1.CID
FROM PURCHASE PU1, PAINTING PA1
WHERE PU1.PID = PA1.PID
AND PA1.CATEGORY = PORTRAIT
AND PU1.CID IN
(SELECT PU2.CID
FROM PURCHASE PU2, PAINTING PA2
WHERE PU2.PID = PA2.PID AND PA2.CATEGORY = STILL LIFE)


algebra (4%):
(CID (CATEGORY= PORTRAIT PAINTING JOIN PURCHASE))

(CID (CATEGORY =STILL LIFE PAINTING JOIN PURCHASE))


calculus (4%):
{<CID> | .PI1,Y1 <PI1,CID,Y1>PURCHASE AND . PI2, Y2 <PI2,CID,Y2>PURCHASE AND .T1, A1, CA1, PR1 (<PI1,T1,A1,CA1,PR1>PAINTING AND CA1=PORTRAIT) AND .T2,A2,CA2,PR2 (<PI2,T2,A2,CA2,PR2>PAINTING AND CA2=STILL LIFE)}





Query 3: Find the unique IDs of the customers with the highest credit.
SQL (4%):

SELECT C1.CID
FROM CUSTOMER C1
WHERE C1.CREDIT =
(SELECT MAX(C2. CREDIT) FROM CUSTOMER C2)


algebra (4%):

CID (CUSTOMER) C C1.CID ( (C1.CREDIT < C2. CREDIT ( C1 (CUSTOMER) X P C2 (CUSTOMER)))


calculus (4%):

{<CID> | . N,C (<CID,N,C>CUSTOMER AND. . CID1,N1,C1 (<CID1,N1,C1>CUSTOMER AND C1>C))}




Query 4: Fi