Computer Science 455

Second Hour Exam

Name _______________________________

Friday, March 23

100 pts.

(please note: relational schema for the spj and Hoh River databases can be found on the last page)

I. Normalization.

a. (20 pts.) Give brief definitions of the following terms (4 pts each)

Functional Dependency (as in A functionally determines B in the relation R)

Second Normal Form

Determinant

Data Manipulation Language

Boyce-Codd Normal Form (BCNF)

b. (20 pts.) Suppose that in a relation R(A,B,C,D,E) we have the following functional dependencies:

(A,B) => C

A => D

D => E

i. Sketch a functional dependency diagram (5 pts.)

ii. Identify the key and non-trivial violations of second and third normal form (identifying them as 2NF or 3NF violations) (10 pts.)

iii. Decompose appropriately into BCNF relations.

III. SQL

a. Write SQL queries for each of the following (10 pts. each)

1. Find the names of parts used on no London project (j.city = 'LONDON')

2. Find the names of parts used on all London projects.

3. (III.a continued) Find the names of parts whose total quantity (over all suppliers and projects) is greater than 500

b. (refer to the Hoh River description on the last page) (10 pts.)

Write the create table statement for the STUDENT table, using reasonable data types. Recall that ADVISOR is a foreign key, and that we would like to enforce referential integrity and entity integrity.

b. (5 pts.) Make a non-unique index on the STUDENT table over ADVISOR.

c. (10 pts.) Create a view from the student table which includes the name of the student's advisor as well as the advisors faculty number. That is, the view should have the form

StudentView(sno, sname, major, advisorno, advisorname)

d. (5 pts.) What is the difference between create table as select and a create view statement?

e. (5 pts. extra credit) What is a materialized view?

Relational schema for the spj and Hoh River databases (this sheet may be torn off for use in the exam)

SPJ

s(sno, sname, status, city)

p(pno, pname, color, weight, city)

j(jno, jname, city)

spj(sno, pno, jno, qty)

Hoh River

student(sno, sname, major, advisorno)

faculty(fno, fname, dept)

class(cno, cname)

enroll(sno, cno, grade)

teaches(fno, cno)

Notes on Hoh River:

sno is key of student, and advisorno is a foreign key containing the fno of the student's advisor

fno is key of faculty

cno is key of class

(sno, cno) is key of enroll, indicating that a student (with sno) is enrolled in the class given by cno, obtaining a grade.

(fno, cno) is key of teaches, indicating that the faculty whose number is fno teaches the class whose key is cno.