NAME ____________________________________

Monday, March 27

100 pts.

1. (15 pts.) Convert the following entity-relationship diagram to a relational schema, identifying primary and foreign keys for each table.

2. Consider the STUDENT(SNO,SNAME, MAJOR, ADVISOR) table in the Hoh River College database

a. (10 pts.) Write the CREATE TABLE statement for the table, assuming reasonable data types. The key is SNO, and ADVISOR is a foreign key (referencing the FNO field of the FACULTY table).

b. (10 pts.) Write a view which will retrieve students advised by the member of the faculty with FNAME=’KERRICK”.

c. (10 pts.) We could also create a table using CREATE TABLE AS SELECT to solve problem (b) above. What is the difference between doing this and using a view?

3. (5 pts. ea.) Give definitions of

a. First Normal Form (1NF)

b. Second Normal Form (2NF)

c. Third Normal Form (3NF)

d. Determinant

e. BCNF

4. (15 pts.) The relation R(A,B,C,D) has functional dependencies A=>C, (A,B)=>D.

a. Sketch a functional dependency diagram for this relation

b. Identify a violation of 2NF (marking it in the diagram), and decompose the relation so that the resulting relations are in 2NF.

5. (15 pts.) In the SPJ database

S(sno, sname, status, city)

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

J(jno, jname, city)

Write SQL queries to

a. (5 pts.) List supplier numbers and the total number of projects supplied by each supplier.

b. (10 pts) Give the names of parts that are used on all London projects (i.e., PNAME for parts for which there is not a project with city=’LONDON’ for which there is not an spj record indicating that the London project uses this part).