=========================preview======================
(COMP231)comp231_97s_midterm.pdf
Back to COMP231 Login to download
======================================================
COMP 231 --- Spring 1997
MID-TERM EXAMINATION
March 20, 1997


I hereby declare that no illegal aids were used in completing this examination. And I understand that any act of cheating will result in severe penalty, including expulsion from the university.
Name:
Student ID:

Section: Lee / Papadias (please circle)
All questions should be answered within the space provided after each problem .
Problem 1 (15%)

A distribution company receives orders from customers and fills the orders from the stock. Design an ER diagram for the database according to the following description. Identify all constraints and keys. Write down all assumptions you made with justifications.
1.
Each customer has a customer number which is unique, a name and an address.

2.
Each item has a unique item number. The unit-price, and quantity in stock are recorded for each item.

3.
Each order has an order number assigned by the customer. While order numbers from the same customer is unique, different customers may use the same order number.

4.
The date the order is received and the quantity ordered for each item are recorded.

5.
Orders must be issued from existing customers.



Problem 2 (28%)
Let R(A,B,C) and S(D,E,F) be two union compatible relation schemas. Specify the equivalent, SQL, algebra, tuple calculus, domain calculus and QBE expressions for the following queries (answer inside the table):
1.
Retrieve all As in R (projection)

2.
Retrieve all tuples of R where C=12

3.
Retrieve all tuples of R and all tuples of S

4.
Retrieve all As and Fs where R.C=S.D (join)

5.
Retrieve all As where A does not exist in the D column of S.


SQL algebra tuple calculus domain calculus QBE
1 1% each R A B C
2 1% each R A B C
3 1% each R A B C S D E F
4 1% each R A B C S D Result E F
5 2% each
do not answer



Problem 3 (7%)
Using the relations R and S of problem 2 express in SQL the following queries (answer below each query)
retrieve the average value of C for retrieve the maximum value of B for retrieve the average value of C for
each group based on A tuples which have A=12 each group based on A after
2% 2% eliminating all tuples with B less
than 12 3%



Problem 4 (10%)
Assume that the following table contains the only set of tuples that appear in R(X,Y,V,W).
tuple X Y V W
1 x1 y1 v1 w1
2 x1 y1 v2 w2
3 x2 y1 v1 w3
4 x2 y1 v3 w4


Complete the dependencies from sets of attributes to attributes. Fill T for True (e.g., the dependency XX) and F for False (the already completed dependencies are trivial, i.e., from a set of attributes to its subsets).
X Y V W
X T
Y T
V T
W T
XY T T
XV T T
XW T T
YV T T
YW T T
VW T T
XYV T T T
XYW T T T
XVW T T T
YVW T T T



Problem 5 (20%)
Consider a relation R(X,Y,U,V,W) wit