Homework 6 (due 5/16)
CSC 453

 We talked about 4NF (3.6), and then moved on to constraints and triggers (Chapter 7). Next topic after that will be views & indexes (Chapter 8).

Handing it in: Submit to the d2l dropbox. Use some standard format for your homework (Word or pdf, for example), and make sure to include your name and assignment number. Include screenshots for code and testruns.

1. (Reading Assignment) Read Sections 3.6, and Chapter 7 of the textbook.

2. (Checks and Assertions, 12pt) For the following tasks write an attribute or a tuple level check constraint or an assertion 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 (your code 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 have length two or three. Hint: you can do this with the tools we've seen, but you can also use the length function.

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

  1. [6pt] When a student is added as a member of a student group, automatically set the joined year to the current year. Hint: recall select extract(year from sysdate) from dual;

  2. [6pt] When a student's program is changed from PhD to anything other than PhD, print a warning, since the change 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; use RUN SCRIPT, rather than RUN STATEMENT, that'll force the output buffer to flush at the end of the script. Concatenation in PL/SQL is done using || .

  3. [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 this 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. (Triggers and Sequences, 10pt) At our university students are assigned to one of four houses: Gryffindor, Hufflepuff, Ravenclaw, and Slytherin. As a new student gets added to the student table, they get assigned to the next house in the sequence, starting over at the beginning when we've reached the end. So the first student added goes to Gryffindor, the fourth to Slytherin, and the fifth to Gryffindor again.

Hint: this will pull together a couple of different ideas including triggers, sequences, case expressions, and modular arithmetic. Students already in the database do not need to get assigned houses.

Show your code for all the sequences/triggers you created, and include screenshots of testruns. Test with around 10 inserts. If you have the autonumber trigger running you can just keep inserting the same student name over and over again, and see whether the houses cycle through the list appropriately.

5. (Extra Credit, 7pt) In class we wrote a trigger to turn the SID field in student into an autonumber field. We found two problems with that: a) when we specified an actual value for SID, it got replaced with the autonumber, and b) the automatically generated number may conflict with values already in the table. We fixed a), for extra credit, fix a) and b) both by implementing the following logic:

Marcus Schaefer
Last updated: May 11th, 2018.