CS422 MIDTERM

MIDTERM
CS422, Summer 2009

[Part 1] Please write your answers on paper and submit it at the end of the exam.

1. (30pt) A hospital keeps a database on patients and their stays in the hospital. The specifics are:

Each patient is assigned a doctor who handles the hospital stay of the patient.
Each doctor has a name and an office phone number.
For each patient visit to the hospital, the database records the date the patient entered, the date the patient left, and the room that the patient stayed in. If the patient is still in the hospital, the "date left" field is null.
The patient is charged per day spent in the hospital. Each room has a price per day.

(a) Draw an ER diagram for this database.

(b) Convert your ER diagram to a relational schema.

2. (20pt) Suppose our department uses the following table to keep track of the faculty and the courses they teach:

faculty	room	phone	quarter	course	section_number	office_hours
C. Sun	E&T A317	x6697	Summer 2009	CS422	1	MTR 3-5pm
C. Sun	E&T A317	x6697	Summer 2009	CS454	1	MTR 3-5pm
C. Sun	E&T A317	x6697	Summer 2009	CS491A	1	MTR 3-5pm
C. Sun	E&T A317	x6697	Spring 2009	CS520	1	WR 3-5pm
C. Sun	E&T A317	x6697	Spring 2009	CS520	2	WR 3-5pm
V. Crespi	E&T A318	x4596	Fall 2008	CS512	1	MTW 2-4pm

(a) List all the non-trivial FDs of the table.

(b) List the key(s) of the table.

[Part 2] This part is to be completed on a computer using the sample database University (university-create.sql, university-drop.sql). Please put all the queries, stored procedures, and triggers in an SQL script file midterm.sql and upload it CSNS at the end of the exam. Note that file uploading will be disabled automatically after 8:10PM, so please turn in your work on time.

3. (30pt) Complete the following queries:

(a) List the titles of the courses offered by the Computer Science Department in 2009.

(b) List the names of the professor(s) who have taught the course Databases. The results should not contain duplicates.

(d) List the professors and the average grade points they gave. Order the results by average grade points in descending order.

(e) List the number of students graduated in 2009 by department. The results should show 0 for the departments that do not have any student graduated in 2009.

(f) List the students and their major GPA. The results do not need to include the students who have not taken any classes in their major.

4. (20pt) Write a trigger grad_check to enforce the constraint that a student must complete at least 20 units of course work in order to graduate. Note that a student is considered graduated if the graduation_date field of the student record is not NULL, and only the units of the classes from which the student received a passing grade (i.e. C or above, or CR) should be counted.