Computer Science 455

Second Hour Exam

 

 

 

Name ___________________________

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Friday, March 26

3:00

  2. SQL stuff

With respect to the SPJ database (see the last page for a reminder of the tables and relations), write SQL queries for the following
    1. (10 pts.)Find the names of PARIS suppliers

 

 

 

 

 

 

 

 

 

 

 

 

b. (10 pts.) Find the names of Paris suppliers who supply a RED part to a LONDON project.

 

 

 

 

 

 

 

 

 

 

 

 

 
    1. (10 pts.) Insert a new supplier into the database: (S6, MATTHEWS, TACOMA, 30)

 

 

 

 

 

 

 

 

 

 

d. (10 pts.) Delete parts (i.e., delete entries from the P table) which are supplied by supplier S03.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

e. (10 pts.) Student records consist of a SNO field (primary key), SNAME, MAJOR, and ADVISOR (foreign key). Assuming reasonable data types and lengths, write a CREATE TABLE statement for the table, taking into account primary and foreign keys.

 

 

 

 

 

 

 

 

 

 

 

 

 
  1. Theory:

a. (5 pts.) Describe what it means in a table R(A, B, C) for an attribute A to functionally determine an attribute B.

 

 

 

 

 

 

 

b. Suppose that we have a table R(A,B,C,D) with functional dependencies

(A,B) => C, B => D.

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

 

 

 

 

 

 

 

 

2. (5 pts.) What is the key of R?

 

 

3. (5 pts.) Give a brief definition of 2NF.

 

 

 

 

 

 

 

4. (5 pts.) Identify a violation of 2NF in R(A,B,C,D) and decompose R into two relations in 2NF.

 
    2. Suppose that we have a table R(A,B,C) with functional dependencies A => B and B=> C.
    1. (5 pts.) What is the key of R?

      2.  

       

       

       

       

    2. (10 pts.) This relation is in violation of 3NF and also BCNF. Give a brief definition of BCNF (defining any terms you may use).

 

 

 

 

 

 

 

 

 

 

3. (5 pts.) Decompose this relation so that it is in 3NF.

 

 

 

 

 

 

 

 

 

 

4. (5 pts) Give reasons that the resulting relations are in BCNF.

 

 

S(S#, SNAME, STATUS, CITY)

P(P#, PNAME, COLOR, WEIGHT, CITY)

J(J#, JNAME, CITY)

SPJ(S#, P#, J#, QTY)