Computer Science 455

Second Hour Exam

Name _______________________________

Friday, March 29

90 pts.

I. Design.

a. Definitions (5 pts. each)

In a relation R(A,B,C,D), what does it mean for A to functionally determine B?

What is a determinant (in relational database terms)? Give an example from the SPJ database.

What does it mean for a relation R to be in second normal form?

What does it mean for a relation R to be in third normal form?

What does it mean (formally, not Kent's great but informal definition) for a relation to be in BCNF?

b. (20 pts.) One of the two relations below is not in second normal form, and the other is in 2NF but not in third normal form.

R1(A,B,C,D) with functional dependencies (A,B) =>C,

A => D

R2(X,Y,Z) with functional dependencies X => Y => Z.

For each relation, identify the problem (2NF or 3NF violation), clearly indicating the offending functional dependencies, and decompose the relation appropriately into relations which are in BCNF.

2. SQL DDL and DML

The Hoh River College database includes the following two tables:

STUDENT(sno, sname, major, advisor) (key: sno)

FACULTY(fno, fname, dept) (key: fno)

advisor in STUDENT is the fno value of the student's advisor.

a. (10 pts.) Write the CREATE TABLE statement for STUDENT using appropriate data types for the fields. Include key and referential constraints (for sno and advisor) on the table.

b. (5 pts.) Write the appropriate SQL statement to create a (non-unique) index over advisor in the STUDENT table.

c. (5 pts.) Write the SQL statement necessary to create a view on the STUDENT and FACULTY table which will include all of the information currently in the STUDENT table (sno, sname, major, advisor) together with the name of the student's advisor.

d. (5 pts.) Write the SQL statement necessary to insert student S12, named POIROT, major ARISTOLOGY, advisor F42, into the database.

e. (5 pts.) Write the SQL statement necessary to delete all of the students advised by the member of the faculty whose faculty number is F57.

f. (5 pts.) Write the SQL statement necessary to change the advisor of everyone majoring in ARISTOLOGY to F98.

g. (10 pts.) Write the SQL statement necessary to find the names of students who are advised by the advisor of student S55 (there are at least three ways to do this, two of which have been covered in material for this exam. Please feel free to use any method you prefer.)