We talked about database connectivity (which we did in Python, the book discusses JDBC and PHP in Sections 9.6/9.7) , views, and started on indexes (Chapter 8).
1. (Reading Assignment) Read Sections 8.1-8.2, 8.5 of the textbook. If you want to If you want to read ahead, read the remaining Sections of Chapter 8.
2. (Views and Instead of Triggers, 12pt) In class, we created a view enrollment as follows:
CREATE VIEW enrollment(SID, LName, CID, CNR, Dpt) AS
SELECT SID, LastName, CID, CourseNr, Department
FROM student, enrolled, course
WHERE SID = studentID AND CourseID = CID;
and a corresponding trigger to handle inserts (since the view is not updatable).
CREATE TRIGGER enrollmentinsert
INSTEAD OF INSERT ON
enrollment
FOR EACH ROW
BEGIN
INSERT INTO
enrolled(StudentID, CourseID)
VALUES
(:new.SID, :new.CID);
END;
Improve this trigger so it checks that the CNR and DPT supplied in an INSERT command are actually correct for the supplied courseID. If not, the INSERT should not be performed. E.g.
INSERT INTO enrollment
VALUES (8871, 'Snowdon', 1020, 123, 'ABC');
should be rejected, i.e. 8871 should not be enrolled in 1020, and an application error should be raised (recall RAISE_APPLICATION_ERROR). Include screenshots of testruns. Hint: you'll need an if statement. Use SELECT INTO to get the info you need.
3. (Views, Assertions, and Check Option, 13pt) We want to enforce the following rule using a view with check option: a president of a student group has to be a member of that group. Create a view v_studentgroup so that if we use inserts and updates on v_studentgroup instead of studentgroup, this rule gets enforced. In particular, if we insert a new group (so without members) and presidentid, that change should get rejected; also change the president_id in the v_studentgroup table to a student who is not member of the group should be rejected.
Testrun your view by trying to insert a new studentgroup without members, but an existing president, and by chanding the president of 101 to 11035 (reject) and 32105 (accept). Note: be sure to remove/disable any triggers you have on memberof or studentgroup so they don't interfere with your work here. Hint: it's possible that you don't quite see the behavior you expect with the president of 101. If that happens, look at your records in v_studentgroup; is one of them missing? Why?
4. (Views and Instead of Triggers II, 15pt) Your boss decided that having a Started year in the student table is useless, instead he wants to see which year the student is in at the university, so the first row, for example, should be
('Snowdon', 'Jonathan', 9981, 123123123, 'GRD', 'INFO-SYS', 'Springfield', 2)
(Since Jonathan started in 2017, so 2018 is his second year). The fact that if you implemented the base table like that you'd have to update all the records next year (in 2019), and every year after that, doesn't bother your boss. Fortunately, you've heard of INSTEAD OF views and triggers.
a) Create a view v_student which is just like the student table, but instead of containing a column Started contains a column Year, which contains the year the student is in (a value you can calculate from started and the usual extract expression).
b) Create an INSTEAD of trigger that handles an INSERT on the view v_student. If the year is 1 or null, you set the started value to the current year. If it's any other value, you reject the INSERT with a warning message that the year value is incorrect. Hint: Raise an application error to stop the actual insert.
c) Extend the INSTEAD of trigger (use UPDATING/INSERTING to distinguish which case you're in) so it handles an UPDATE on the YEAR column. Calculate the new STARTED value of the student and update the student table.