Page 1 (of 8 pages)


	Page 1











Computer Science 455


Second Hour Exam




Name _______________________________


















Thursday, Mar. 21
100 pts.


I.	Top-down design.

	A customer (customer number, customer name) has a series 
of orders (order number, date).  An order consists of a number 
of line items, each of which is an entry from the catalog 
(catalog number, description, unit price), and the amount 
ordered of that item.

a.	(20 pts.)	Construct an entity-relationship diagram for this.













	(problem I continued)

b.	(20 pts.)	Convert the E-R diagram to a relational schema.  
Indicate, for each table, the primary keys and foreign keys in 
each table.

II.	Bottom-up design.

	Consider the (partial) form on the last page of this exam.  You 
may want to tear off the form to look at as you work the rest 
of the exam, but please do not write anything on that page (it 
could easily get separated from your exam).  If you do tear off 
that page, you probably will not want to turn it in with the 
rest of your exam.

a.	(5 pts.)	Briefly list the entities you see represented on the 
form.






b.	(10 pts.)	List the fields of the form in un-normalized 
form, and list the functional dependencies between the fields 
in the form.

c.	(15 pts.)	Briefly define first normal form, and bring the un-
normalized structure of part (b) into first normal form.

d.	(15 pts.)	Briefly define second normal form, and bring the 
tables in part (c) into second normal form.  Note that they may 
already meet this requirement.

e.	(15 pts.)	Briefly define third normal form, and bring the 
tables in part (d) into third normal form.  Note that they may 
already meet this requirement.


					
		YOYODyne Suppliers			
					
Order number: 12345				Date 04/01/96	
					
Customer number:			Salesperson number:		
Customer name:			Salesperson name:		
Billing address:					
					
item code	description		Unit cost	amt	cost
					
c46534	Widget		12.34	2	24.68
					
					
					
					
					
					
					
					
					
					
					
			Total Order Cost:		xxx.xx