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

• Each team has a name (e.g. Lakers) and is located in a city (e.g. Los Angeles).
• For each player, the database keeps track of the player's name, the team he or she plays for, and the position of the player (e.g. center, power forward etc.). We assume that a player always plays the same position.
• Two teams may trade players. Note that a trade may involve more than two players, e.g. Lakers traded two players to Grizzlies for Pau Gasol. The database needs to record the teams and the players involved in the trade as well as the date of the trade.
• For each game, the database stores the home team, the away team, the date of the game, and the scores.

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

• A student cannot register for more than 20 units per year.
• A student cannot register for more than 8 non-major units per year.