Computer Science 455
Second Hour Exam
Name ______________________
The supplier-parts-projects (SPJ) database:
s(sno, sname, status, city)
p(pno, pname, color, weight, city)
j(jno, jname, city)
spj(sno, pno, jno, qty)
90 pts (will be normalized to 100 pts. in the gradebook)
I. Some SQL DDL
a. (5 pts.) Consider the SPJ supplier table s(sno, sname, status, city). Build a non-unique index over the sname field.
b. (10 pts.) Create a view (called redparts) consisting of sno and sname values for suppliers of red parts together with the pno and pname values for the red parts supplied by that supplier. The view should have the schema
redparts(sno, sname, pno, pname)
c. (5 pts) Suppose after making the view described in (b) above, we change the part name of a red part supplied by one of the suppliers. Will this change be reflected in the view we just defined? Why or why not.
II. Some more SQL DML
a. (5 pts.)In the supplier table s, double the status of all
b. (5 pts.) Again in the supplier table s, delete any suppliers in
c. (5 pts.) Add a new supplier in
III. Some data normalization questions
a. (5 pts.) In a relation R, what does it mean (formally) to say that attribute A functionally determines attribute B?
b. (5 pts) In a relation R, what is a determinant?
c. (5 pts) Give a definition of 2NF
d. (5 pts) Give a formal definition of BCNF.
e. In a relation R(A, B, C) we have functional dependencies as follows:
A => B and
B => C
i. (5 pts.) What is the key of R?
ii. (5 pts) List any determinants in R.
iii. (5 pts.) Is R in BCNF? If not, why not?
f. Now consider the relation R(A, B, C, D) with functional dependencies
(A, B) => C and
B => D.
i. (5 pts.) Draw a functional dependency diagram (as we have done in class)
ii. (5 pts.) Identify the key of R
iii. (5 pts.) Identify a 2NF violation in R
iv. (5 pts.) Decompose R into relations that are in 2NF. Give the relational schema for the derived relations.