Page 1


	Page 1











Computer Science 455


Second Hour Exam




Name _______________________________


















Tuesday, April 12
100 pts.


I.	Examine the following entity-relationship diagram:

                                                          


a.	(20 pts.)	Decompose the entity-relationship diagram on page 
2 into a series of tables.  Identify (list) primary keys and 
foreign keys.


b.	(15 pts.)	Using reasonable data types, write the CREATE 
TABLE statement for the ORDER table.  Assume that the 
relationship between orders and customers is mandatory.  Be 
sure to enforce this along with entity integrity.












c.	(10 pts.)	Write the CREATE INDEX statement necessary to 
create a unique index over the CNO field of the CUSTOMER 
table.




II.	Examine the following form:

                                                                      


(Problem II continued)

a.	(10 pts.)	Analyze the diagram on page 5.  List the data items 
on the form in a  table in  un-normalized form.

















(Problem II continued)

b.	(10 pts.)	Briefly summarize the entities and relationships 
that appear to be indicated by this form.  You do not need (for 
this exam) to carry through the details of bottom-up design.  
Your answer may be in the form of an entity-relationship 
diagram..

III.

c.	(5 pts.)	Define (informally) Boyce-Codd normal form.









d.	(10 pts.)	With respect to the SPJ database, how does the 
following table fail to be in BCNF?  List all violations, and 
assume (for the following) that STATUS depends on CITY.  What 
is the key of the relation?

BAD_R(SNO, SNAME, STATUS, S_CITY, PNO, JNO, QTY)


IV.	(20 pts.)	With respect to the SPJ database, write SQL 
queries for the following:

a.	Find all details of London Projects






b.	Find the names (only) of blue parts






c.	Find the names of suppliers who supply project J3







d.	Find the names of suppliers who supply Paris projects 
with blue parts.