Homework 3
CS422, Spring 2012

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:

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

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.