Due: Tuesday, May 8

Please upload your solutions to CSNS. Note that file uploading will be disabled automatically after 11:59PM of the due date, so please turn in your work on time.

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. (60pt) Write triggers to enforce the following data constraints in the University database (university-create.sql, university-drop.sql).

• Exercise 5.6 (b), (c), (d), (e)

For this exercises please submit two SQL scripts: hw3-triggers.sql and hw3-drop.sql. hw3-triggers.sql should include the code to create the triggers, as well as some insert statements to test whether the triggers work properly. hw3-drop.sql should include statements to remove the triggers and the associated functions.