Page 1













COMPUTER SCIENCE 455



FINAL EXAM




NAME    _________________________________















Tuesday, May 9
8:00 AM
200 pts.

1.	(40 pts.)	In the form of a memorandum to your boss, justify 
the acquisition of a database management system.  List the 
advantages (both technical and managerial) that a database 
management system would have over a system of files and 
programs.  Give definitions of all of the terms that you use, 
being sure to give a description of database architecture, 
definitions of data abstraction, data independence, and a 
description of the role of the database administrator.  List some 
of the advantages of using a database management system of 
the relational model, but be candid regarding some of the 
problems (in addition to the expense!).


(additional workspace for problem #1)



2.	(20 pts.)

a.	Describe the difference between a procedural language (such as 
the relational algebra) and a non-procedural language (such as 
the relational calculus)












b.	Give an example (a query on the spj database) of a relational 
calculus expression in SQL








c.	Give an example (a query on the spj database) of a relational 
algebra expression in SQL.


3.	The following questions are with respect to the COLLEGE 
database, described as

	STUDENT(SNO, SNAME, MAJOR, FNO) (FNO is the faculty
		number of the student's advisor)
	FACULTY(FNO, FNAME)
	CLASS(CNO, DEPT, CNAME, FNO) (FNO is the faculty number
		of the teacher of the class)
	ENROLL(SNO, CNO, GRADE)
		(GRADE is a numeric value.  A 'B', for example, is
		stored as 3.0)

a.	(10 pts.)	Write a SQL statement to find the names of 
students whose advisor is 'KERRICK' (i.e., FACULTY.FNAME = 
'KERRICK').











b.	(10 pts.)	Write a relational algebra or relational calculus 
statement to find the names of students whose advisor is 
'KERRICK' (i.e., FACULTY.FNAME = 'KERRICK').  Use either the 
calculus or algebra, but say which one you are using.  All 
relational algebra expressions should be using the full 
relational calculus (i.e., not the EASYALG form used at the start 
of the term).



(problem 3 continued)

c.	(10 pts.)	Write a SQL statement to find the names of 
students who have taken all of the courses in the MATH 
department (i.e., courses with DEPT='MATH').   














(problem 3 continued)

d.	(10 pts.)	Write a relational algebra or relational calculus 
statement to find the names of students who have taken all of 
the courses in the MATH department (i.e., courses with 
DEPT='MATH').  Use either the calculus or algebra, but say 
which one you are using. 


(problem 3 continued)

e.	(10 pts.)	Write a SQL statement to find the average grade of
students in the class with CNO = 'M12345'.












4.	(10 pts.)	Give a brief definition of the data dictionary for a 
database management system.  How (again, briefly) is the data 
dictionary implemented in Oracle?


5.	(30 pts.)	We have discussed four models for database 
systems in this course:  hierarchical, network/CODASYL, 
relational, and object.  Describe each of them briefly, giving an 
underlying data model and integrity constraints as appropriate.

(additional work space for problem 5)

6.	(30 pts.)	Consider the following entity-relationship diagram:

                                                        


(problem 6 continued)

a.	Convert the E-R diagram to a relational schema.














b.	Write the CREATE TABLE statements for the relational schema.  
Invent appropriate data types.


(problem 6 continued)

c.	Identify candidates for indexes in your relational schema, and 
say why you have picked them (very briefly).  Write the 
CREATE INDEX statement for one of them.


7.	(20 pts.)	Consider the following functional dependency 
diagram:

                                               

a.	What is the key of this relationship (i.e., which attribute or 
attributes)?






b.	Identify a violation of 2NF, and say why it is a violation.








(problem 7 continued)


c.	Identify a violation of 3NF which is not also a violation of 2NF, 
and say why it is a violation.








d.	Decompose the diagram into a series of relations that are in 
BCNF.