Database Comprehensive CSD Exam Fall 1996.  30 minutes total (6+5+6+3 = 20).
                                                                
Question 1.   Relational Algebra  (6 minutes)
--------------------------------

Consider the following two relations:

      Enroll(studentID, course#)  // <studentID,course#> is the key
      Course(course#, dept)       // course# is the key

Write a relational algebra expression to find the IDs of all students
enrolled in courses in at least two different departments.  You may
use a "relation renaming" operator if you find it helpful.
-------------------------------------------------------------------------------

Question 2.  SQL 1    (5 minutes)
------------------

Given the same relations used in Question 1, write an SQL query reporting 
	the studentID's of students whose list of classes enrolled 
	does not include a class in the physics department.
-------------------------------------------------------------------------------

Question 3.  SQL 2    (6 minutes)
------------------

Consider the simple relational schema R(A,B), where both A and B are integers,
and A is a key but B is not.  Give the simplest SQL query you can think of over 
this schema such that an equivalent query cannot be expressed in relational 
algebra. The simpler the query, the more credit given.
-------------------------------------------------------------------------------

Question 4.  Database Design  (3 minutes)
----------------------------
Given the Entity-relationship models on the attached page determine
the minimum number of 3NF relations needed to contain all of key and 
dependent data described in each of the following relationships:

 4.a:	one-to-one relationship between 
		Employee (name and address) and ID (ID# and position)
 4.b:	many-to-one relationship between 
		Employee (name and address) and Department (dept# and manager)
 4.c:	many-to-many relationship between 
		Employee (name and address) and Project (project# and customer)
-------------------------------------------------------------------------------
Question 5:  Dependencies  (10 minutes)
-------------------------

Consider a relation R with attributes A, B, C, and D and the functional
	dependencies AB->C, C->D, and D->A.

a) Find all the (minimal) keys for R.

b) Are there any 3NF violations?  Give one example if so.

c) Are there any BCNF violations? Give one example if so.
--------------------------------------------------------------------------






+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
========================================================================
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Database Comprehensive Exam, October 18 1996

Answer 1.
-------

Relational Algebra
(a) PROJECT[E1.studentID] (
        SELECT[E1.studentID = E2.studentID and
               E1.course# = C1.course# and
               E2.course# = C2.course# and
               C1.dept <> C2.dept] (
                    (enroll{E1} X enroll{E2} X course{C1} X course{C2})))

where rel-name{R} renames relation rel-name as R.

---------------------------------------------------------------------------------
Answer 2.   SQL 1:

	SELECT DISTINCT studentID
	FROM Enroll
	WHERE NOT EXIST
		(SELECT *
		FROM Course
		WHERE Enroll.course#=Course.course# and dept="physics")

-------------------------------------------------------------------------------
Answer 3.  SQL 2:

2.a	Best: SELECT B FROM R; (since duplicates may be returned)
2.b     Next best: select avg(A) from R; (since avg can't be expressed)

-------------------------------------------------------------------------------
Answers 4.

Answer 4.a: 1.  ID or name can be key
Answer 4.b: 2.  Need name (or Id) and dept
Answer 4.c: 3.  Also need partial crossproduct
-------------------------------------------------------------------------------
Answers 5:

Answer 5.a: AB, BC, and BD.

Answer 5.b: no.  All attributes are in some key, so there cannot be a 3NF
violation.

Answer 5.c: C->D or D->A are examples of BCNF violations.
In each case the left side does not include a key.

--------------------------------------------------------------------------------