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.

Express each of the following in SQL-92:

1. (5 pts) You've manages to ``hack in" to the university database and want to have some fun. What SQL expression would you use to increase the gpa of each student by 0.5? How could you modify this expression to ensure that no updated gpa exceeded 4.0?
2. The following questions all refer to the same view:
   1. (5 pts) Create a view report-card with attributes name, department, number, and grade - in other words, that gives the grade that each student received or is receiving in each course (where the course is identified by department and number only). If a student has taken the same course multiple times (e.g. in different semesters), all grades received for the course should appear in this view.
   2. (3 pts) Suppose a naive instructor tries to assign a grade as follows:
      
      insert into report-card
      values (``Donny Osmond", ``Comp", 430, 0.67)
      
      If the insertion is not rejected, what tuples must be inserted into which actual relations to account for this insertion?
   3. (3 pts) Is the newly inserted tuple in the result of:
      
      select *
from report-card
      
      Why or why not?

3. (5 pts) Give the command to create the instructor relation. Your command must specify that {name, office} is the primary key for instructor. Additionally, your command must specify that the only legal values for the office attribute are ``E251" and ``W252".

• About this document ...