

Computer Science 455


Third Hour Exam


Name _______________________________


Thursday, April 24
100 pts.


I.	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 are not required to turn it in with the rest of your exam.

a.	(10 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, making reasonable assumptions.  For the purposes of this exam, assume that each salesperson may work with a number of customers, but that each customer has only one salesperson with whom that customer works.

c.	(10 pts.)	Bring the un-normalized structure of part (b) into first normal form, and clearly identify any violations of second normal form in the resulting tables.

d.	(10 pts.)	Bring the tables in part (c) into second normal form, and clearly identify any violations of third normal form in the resulting tables.  Note that they may already be in second or third normal form.

e.	(10 pts.)	Bring the tables in part (d) into third normal form.  Note that they may already meet this requirement.


f.	(10 pts.)	Sketch an entity-relationship diagram of the resulting relational schema.

g.	(15 pts.)	Assuming that you have constructed (using SQL*Forms) a form for this example, write a trigger which will be executed when the user types in a customer number and moves to the next field which will put the customer's name into the appropriate field on the form.  Assume that there is a table called CUSTOMER(CNO, CNAME, ADDR, TELNO), and that the customer block in the form is called MAIN.  (CNO - customer number, etc.).  Make use of no_records_found and form_trigger_failure to take care of the case when an incorrect customer number is entered.


II.	(15 pts.)	What is the impedance mismatch problem, and how does PL/SQL get around it (include some details - simply listing a word or two will not receive full credit.)


III.	10 pts.)	Given EMP(ENO, ENAME, SALARY, SUPER), write the SQL statement which will produce a list of supervisors (by supervisor number) together with the average salary of employees supervised by that supervisor.


	Sample form for question I

	Please feel free to remove this form and take it with you, but
	please do not write anything on it if you do remove the form
	(it would easily be lost)


BOB'S VIDEO STORE


Customer Number:


Salesperson ID:


Customer Name:


Salesperson Name:


Address:


Telephone Number:


Tape Number
Name

Rating
Out
In
Charge


T1001
Attack Killer Tomatoes

PG
4/5/97
4/7/97
$2.00 

T3002
Plan 9 Outer Space

PG
4/5/97
4/6/97
$1.00 

T2145
Bye-Bye Braverman

PG
4/5/97
4/9/97
$3.00 


	Page  


	Page  