We talked about constraints and triggers (Sections 7.4, 7.5). Next week, we will talk about Views and Indices (Chapter 8), and database programming in PL/SQL. The book talks about PSM in Chapter 9, in particular Section 9.4, but there is earlier material in Sections 9.1-9.3 on other forms of communication with the database.
1. (Reading Assignment) Read Sections 7.4, 7.5.
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.
[4pt] Ensure that no more than 3 students enroll in each class (your query should be easibly modifiable to change 3 to any other number).
[4pt] Ensure that every PhD student has 'GRD' as career.
[4pt] Ensure that department labels in course are restricted to 'CSC', 'DC', 'IT', 'GAM', and 'SE'.
3. (Triggers, 24pt) Write triggers for the following tasks. Test the triggers by performing operations that activate the triggers and include screenshots of outputs.
[6pt] When a student's program is changed from PhD to anything else, print a warning, since this is probably a mistake; the warning message should include the SID and name of the student and the program they were changed to: e.g., "Student Abigail Winter, SID 11035, was changed from PhD to COMP-SCI". 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. Concatenation in PL/SQL is done using || .
[6pt] When a student is added to the database, automatically set the started 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.
[6pt] When a PhD student is inserted into the student table, or an existing student's program is changed to PhD, ensure that the career is set as 'GRD'.
[6pt] 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;