Page 1 (of 15 pages)


	Page 1











Computer Science 455


Final Exam








Name _______________________________












Tuesday, May 7
8:00 AM
200 pts.


I

I.	Basic ideas

a.	(25 pts.)	Your boss has sent you an email asking you to give a 
concise definition of a database management system, 
explaining why a database management system is better than a 
series of files.  Do this on this and the following page.  A 
complete answer will include justifications for using a 
database systems, problems resolved and created by using a 
database approach, architectural details, etc.  Please do not 
include your name in the response.



	(additional work space for problem 1 (a).

b.	(10 pts.)	Give a definition of a data model for a database 
management system.











c.	(15 pts.)	Give a concise definition of the hierarchical data 
model for a database management system.  As an example, 
give an example of a relationship that would be easily modeled 
using a hierarchical database system.  

d.	(15 pts.)	Give a concise definition of a relational data 
model for a database management system.  As an example, 
indicate how the example you used in the preceding question 
would be modeled in a relational database system.

e.	(5 pts.)	Object oriented database models are less 
comprehensively defined than are the other three models.  Give 
an example of an object oriented data model used this term.





2.	Design

a.	(20 pts.)	Consider the following entity-relationship diagram 

                                                    

	On the following page, reduce this to a relational schema and 
write CREATE TABLE statements for the tables created.  
Identify primary and foreign keys.  Use reasonable data types 
for each field.


	Workspace for problem 2 (a)


b.	(20 pts.)	Consider a relation R(A,B,C,D,E) with functional 
dependencies as given in the following diagram.  R is the name 
of the relation, and A, B, C, D, and E are attribute (column) 
names.

                                           


	(see the next page for questions on this diagram)

	(Problem 2 (b) continued)

i.	What is the key of the relation?





ii.	Identify a violation of second normal form in the relation







iii.	Identify a violation of third normal form in the relation which 
is not also a violation of second normal form








iv.	Decompose the relation into relations that are in Boyce-Codd 
Normal Form.





3.	SQL and algebra

	With respect to the SPJ database (a copy of which appears on 
the last page of the exam), write both SQL and ALGEBRA 
queries for the following:

a.	(20 pts.)	Find the names of projects that use RED parts

SQL













ALGEBRA


b.	(20 pts.)	Find the names of projects that use no RED parts

SQL















ALGEBRA


c.	(20 pts.)	Find the names of projects that use every RED part 
(i.e., if there is a RED part, then this project uses some of that 
part).

SQL














ALGEBRA



d.	(10 pts.)	Create a view (from the SPJ table) consisting only 
of part numbers and project numbers and the total number of 
the part supplied to the project.  For example, if (P2,J4,600) is 
in the view, then 600 of part P2 is supplied to project J4.













4.	Tools  (20 pts.)	The form DATA_ENTRY has a block named 
SBLOCK based on the STUDENT table:  STUDENT(SNO, SNAME, 
MAJOR, ADVISOR).  Advisor is to be an FNO value from the table 
FACULTY.

	a.	What is a trigger?









b.	Write the PL/SQL code for a POSTCHANGE trigger on the 
ADVISOR field in the form to ensure that the value 
entered is actually the FNO value of a current entry in 
the FACULTY table.  If no such FNO exists, print an error 
message an cause the form to fail.





SPJ database schema:

S(SNO, SNAME, STATUS, WEIGHT, CITY)
P(PNO, PNAME, COLOR, WEIGHT, CITY)
J(JNO, JNAME, CITY)
SPJ(SNO, PNO, JNO, QTY)