Consider a relational database for a university registrar's office. The relations are student, class, instructor, teaches, and taking. The schemes of these relations are:

Student-scheme = (name, standing, student-num, gpa)
Class-scheme = (schedule-num, semester, department, number, days, time, place, enrollment)
Instructor-scheme = (name, department, office)
Teaches-scheme = (name, office, schedule-num, semester)
Taking-scheme = (student-num, schedule-num, semester, grade)

where the value of attribute standing is one of {freshman, sophomore, junior, senior}, and the schedule-num attribute of Class-scheme, Teaches-scheme and Taking-scheme is a unique number used to identify a particular section of a course (i.e. each course or section of a course has a unique schedule number) within a single semester, and the semester attribute of these relations identifies the year and semester of the course (i.e. Spring 1997). In other words, the primary key of class is {schedule-num, semester}, and other relations refer to a class via these two attributes. The teaches relation is intended to represent relationships between instructors and classes, and the taking relation is intended to represent relationships between students and classes. The domain of gpa is real numbers from 0.0 to 4.0.

1. Express each of the following in SQL-92:
   1. (4 pts) Find the natural join of student and taking. You are not allowed to use a where clause in your query.
   2. (6 pts) Find all tuples in student and taking that do not participate in the natural join of student and taking. The result of your query should be a single relation (as always), with tuples from student and taking padded with nulls appropriately.
2. (8 pts) List all of the referential integrity constraints that the relations in this database should satisfy. Use the notation to express the constraints.
3. (3 pts) If the tuple ("Tim Wahls", "COMP", "E258") is deleted from the instructor relation, what test needs to be performed to ensure that referential integrity is maintained?
4. (3 pts) When a student's standing is updated in the student relation, what test needs to be performed to ensure that referential integrity is maintained?
5. Consider the following relation:

   

   1. (2 pts) Does the functional dependency A BC hold?
   2. (2 pts) Does the functional dependency B C hold?
   3. (2 pts) Does the functional dependency C B hold?

• About this document ...