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.

• add a field called house to the student table

• implement a trigger and a sequence that for each insert on student picks the next house for the student

• updates to the house field are ignored (without warning)

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:

• An INSERT on the student table which sets an SID value should be honored, that is, if we INSERT INTO STUDENT (SID, Lastname) VALUES (8871, 'Pendleton') we do not try to second-guess the user, and let this command pass unchanged (at which point the insert will fail, because we already have a student with SID 8871).

• If the INSERT is successful, however, for example INSERT INTO student (SID, Lastname) VALUES (12345, 'Pendleton'), then the sequence we use for creating the autonumber needs to be updated, in case 12345 is larger than the next value in the sequence (which is not the case for 12345). Hint: This is tricky. In SQL you can restart a sequence using ALTER SEQUENCE seq_name RESTART WITH 12345, but in PL/SQL you cannot write SQL directly. However, you can use the EXECUTE IMMEDIATE command to run SQL commands in PL/SQL specified as a string (the SQL command in the string should not end in a ; that will give you an error message).

• If we perform an INSERT with SID specified, then we use the sequence to pull the next number for SID.