Page 1


	Page 1


	Page 1











Computer Science 455


Third Hour Exam




Name _______________________________


















Friday, April 29
100 pts.


I.	(25 pts.)	Examine the following form:

                                                                      



(Problem I continued)

1.	Write down the fields in the form, indicating any repeating 
groups.
















2.	Identify and remove partial key dependencies and translate the 
form into tables in second normal form

(problem I continued)

3.	Identify and remove any transitive key dependencies from the 
tables in part 2 and write the information in the form in tables 
in third normal form.   Are any of the entities weak entities?















4.	What recommendations would you make about the form on the 
basis of the analysis above?


II.	(10 pts. each)

a.	What is a trigger, and how is it implemented in SQL*Forms?  
Include in your answer the code for a trigger to enforce 
referential integrity.

b.	What is a master-detail report?  Describe, and as a part of 
your answer give an example.


c.	The impedance mismatch in embedded SQL comes from the fact 
that relational queries return tables, whereas programs 
process one record at a time.  Explain in some detail how SQL 
gets around this problem, including a discussion of the 
embedded SQL commands that are used.

d.	What is the data dictionary?  How does the data dictionary in a 
relational database management system differ from one in a 
hierarchical or CODASYL database management system?  As a 
part of your answer, include a discussion of the Oracle data 
dictionary.


III.	(40 pts.)	With respect to the SPJ database (except for the 
last problem), write SQL queries for the following:

a.	Use sub queries to write a query to find the names of 
suppliers of part 'P3'











b.	Find the names of projects that use no blue parts.











(problem III, continued)

c.	Find the names of projects that use every blue part.  Note 
that this means that if there is a blue part in the P table, 
there is an entry in the SPJ table saying that this project 
uses some of that part.














d.	List the names of suppliers and the total quantity of 
parts supplied by those suppliers, but only for those 
suppliers whose total quantity is not less than 1000.

e.	Build a view consisting of London or Paris suppliers of 
blue parts.















f.	Suppose that an employee table is described by
		EMP(ENO, ENAME, SALARY, SUPERVISOR)
	where SUPERVISOR is the ENO of the employee's 
supervisor.  Write a query which will return the names 
(only) of employees who make more than their supervisor 
makes.