CS422, Spring 2012

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

1. (35pt) A basketball league uses a database to keep track of the information about teams, players, trades, and games. The specifics are:

(a) Draw an ER diagram for this database.

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

[Part II] 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 11:30AM, so please turn in your work on time.

2. (20pt) Complete the following queries:

(a) List the years in which the course Databases were offered. The result should not contain duplicates.

(b) List the names of the students who took Computer Science courses but were not Computer Science majors.

(c) List the names of the professors and the number of sections they taught. The results should show 0 for the professor(s) who did not teach any section.

(d) List the names of the students who have never failed any class. A student is considered failed a class if the student received C- or worse (in a letter graded class) or NC (in a Credit/No Credit class).

3 (20pt) Write a function honor_students() that returns the students who maintained 3.5 or above GPA in every year since they started to attend classes in this university.

4. (25pt)  Write a trigger enrollment_check to enforce the following constraints: