CS422 HW3

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

Students( sid, sname, major, class_level, age )
Faculty( fid, fname, department )
Classes( cid, cname, day_of_week, start_time, end_time, room, fid )
Enrollment( sid, cid )

The class_level attribute in the Students relation could be B1, B2, B3, B4, or G, which represents freshman, sophomore, junior, senior, or graduate student. The day_of_week attribute in the Classes relation could be SUN, MON, TUE, WED, THR, FRI, or SAT.

1. (20pt) Create the relations in the Oracle database with proper attribute types and constraints (primary key, unique, 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.

(a) Given a major, returns the average number classes taken by a student in that major. Note that students who have not enrolled in any class should be included in the calculation (their number of classes taken is 0).
(b) Given a class id, outputs the number of students in the class who are in the following age groups: under 18, 18-22, 22-30, above 30.
(c) Given a faculty id, output the number of days and the number of hours the faculty teaches per week.
(d) Given a room number and a day of week, output the time slots between 8AM and 8PM when the room is available on that day. For example, if there are two classes scheduled on Tuesday in room A210, one from 8:30AM to 11AM, and the other from 4:30PM to 6PM, then the output of the procedure for room A210 on Tuesday should be 8:00AM-8:30AM, 11:00AM-4:30PM, and 6:00PM-8:00PM.

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 maximum enrollment of 30 students.
(b) No two classes can be scheduled in the same room at the same time.
(c) Faculty from different departments cannot teach in the same room.
(d) A student cannot take more than 20 hours of class per week.

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.