Homework 3
CS422, Summer 2005

Due: Tuesday, August 16

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

[Reading]
[Stored Procedures and Triggers]

Consider the following schema:

Students( sid, sname, major, level, age )
Faculty( fid, fname, department )
Classes( cid, cname, start_time, end_time, room, fid )
Enrolled( sid, cid )

1. (20pt) Create the relations in the Oracle database with proper attribute types and constraints (primary key, foreign key, and not null). Populate the tables so that you have sufficient data to test the procedures and triggers you'll implement in the next two exercises. For this exercise, turn in an SQL script file hw3-tables.sql which include the SQL statements you use to create and populate the tables.

2. (40pt) Implement the following procedures/functions using PL/SQL:

(a)  Given a class id, returns the percentage of the graduate students in the class.
(b)  Given a class id, output the demographical information about the class, for example, the number of students in the following age groups: under 18, 18-24, 24-30, above 30.
(c)  Given a faculty id, output the number of students and the number of hours the faculty teaches.
(d)  Given a room number, returns the "utilization" of room, which is the total time the room is occupied for classes over 12 hours (from 8am to 8pm). 

For this exercise, turn in a script file hw3-procedures.sql which includes the code to implement the procedures/functions, as well as test code to show that they work properly.

3. (40pt) Implement the following integrity constraints. For each constraint, briefly explain what operation(s) (inserts, deletes, and updates on specific relations) must be monitored to enforce the constraint.

(a) Every class has a minimum enrollment of 5 students and a maximum enrollment of 30 students.
(b) A CS major must enroll in at least one class that is taught by a CS faculty.A student cannot enroll in a class that does not have a instructor (e.g. the fid field of the class is NULL). Note that this also implies that when a faculty is deleted from the Faculty table, all the students who were enrolled in this faculty's classes should be dropped from the Enrolled table.
(c) Faculty members from different departments cannot teach in the same room.
(d) There must be a minimum 10 minutes interval between any two classes that are scheduled in the same room.

For this exercise, turn in a script file hw3-triggers.sql which includes the PL/SQL code to implement the constraints, test code to demonstrate the constraints are enforced properly, and the discussion about what operation(s) to monitor as code comments.