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:

(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.

(c) Is the table BCNF or 3NF? If not, normalize it to BCNF or 3NF.

[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.

(c) Find the three professors who have taught the most number of sections.

(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.