Homework 8 (due 6/3)
CSC 355

We talked about constraints and triggers (Chapter 7). Next week, we will complete triggers, and talk about views and indices (Chapter 8). Time permitting, we will move on to database programming (Chapter 9).
Handing it in: Submit to the d2l dropbox. Use some standard format for your homework (Word or pdf, for example, but avoid zip), and make sure to include your name and assignment number. Include screenshots for code and testruns.

1. (Reading Assignment) Read Chapter 7. Sections 7.1-7.3 are review of earlier material. If you want to read ahead, move on to Sections 8.1, and 8.2.

2. (Checks and Assertions, 12pt) For the following tasks write attribute/tuple level check constraints or assertions as appropriate  (so points will be given for picking the right type). Only submit the relevant lines of code. Since assertions are not supported, you do not have to submit testruns for this problem.

  1. Ensure that no more than 3 students enroll in each class in any quarter (your query should be easibly modifiable to change 3 to any other number).

  2. Ensure that every PhD student has 'GRD' as career.

  3. Ensure that Department labels in course are restricted to 'CSC', 'DC', 'IT', 'GAM', and 'SE'.

3. (Triggers, 18pt) Write triggers for the following tasks. Test the triggers by performing operations that activate the triggers and submit outputs.

  1. When a student's SID is changed (so the new SID is different from the old SID), print a warning message to the screen: "Student's SID was changed". Hint: use DBMS_OUTPUT.PUT_LINE as we've seen in class. Be sure to issue a SET SERVEROUTPUT ON; before testing the trigger so you see the message printed.

  2. When a student is added as a member of a student group, automatically set the joined year to the current year. Hint: you can use select extract(year from sysdate) into ... from dual; as part of the trigger's PL/SQL block.

  3. We do not want courses with CourseNr less than 100 or larger than 699. If a course is inserted or updated to result in a course number outside the acceptable range, the operation should proceed, but the CourseNr should be set to null and a warning should be issued. Hint: use dbms_output.put_line to create the warning message. You'll need to run set serveroutput on; before running the other code to see the output. To set a variable to null in PL/SQL use select null into ... from dual;

4. (Extra Credit, 15pt) In 2a we wanted to ensure that no class has more than 3 students enrolled in any class in any quarter. We couldn't implement that. Write a trigger that actually implements this constraints on our university database. This will require some additional research on triggers and PL/SQL, for example Ullmans documentation on triggers/constraints and PL/SQL is a good starting point).
Marcus Schaefer
Last updated: May 29th, 2014.