Tutorial 4 Solutions

Creating tables

The order in which tables are created is important here, otherwise references won't work.

 CREATE TABLE Rooms (
    name            VARCHAR(30),
    nrSeats         INT,
    CONSTRAINT RoomKey PRIMARY KEY (name),
    CONSTRAINT ValidNrSeats CHECK (nrSeats > -2 AND nrSeats != 13)
 );
 
 CREATE TABLE Teachers (
    name            VARCHAR(50),
    room,
    CONSTRAINT TeacherKey PRIMARY KEY (name),
    CONSTRAINT TeacherRoom FOREIGN KEY (room) REFERENCES Rooms(name)
 );
 
 CREATE TABLE TeacherTitles (
    name,
    title   VARCHAR(20),
    CONSTRAINT TTKey PRIMARY KEY (name, title),
    CONSTRAINT TTTeacher FOREIGN KEY (name) REFERENCES Teachers(name)
 );
 
 CREATE TABLE Courses (
    name            VARCHAR(50),
    teacher,
    nrStudents      INT,
    CONSTRAINT CourseKey PRIMARY KEY (name),
    CONSTRAINT CourseTeacher FOREIGN KEY (teacher) REFERENCES Teachers(name)
 );
 
 CREATE TABLE Classes (
    course,
    day             CHAR(9),
    hour INT,
    CONSTRAINT ClassKey PRIMARY KEY (course, day, hour),
    CONSTRAINT ClassCourse FOREIGN KEY (course) REFERENCES Courses(name),
    CONSTRAINT ValidDay CHECK (day IN 
    ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday',
            'Saturday', 'Someday'))
 );

Views

 CREATE VIEW SomedayTeachers AS
   SELECT teacher
   FROM   Courses, Classes
   WHERE  course = name
          AND day = 'Someday';
 
 -- help view for the next one
 CREATE VIEW ClassesInRooms AS
   SELECT C.name, A.day, A.hour, T.room
   FROM   Courses C, Classes A, Teachers T
   WHERE  C.name = A.course
          AND C.teacher = T.name;
 
 CREATE VIEW ClassClashRooms AS
   SELECT A.room
   FROM   ClassesInRooms A, ClassesInRooms B
   WHERE  A.room = B.room
          AND A.day = B.day
          AND A.hour = B.hour
          AND A.name != B.name;

Assertions (not Oracle)

We'll borrow the code from the last view:

 CREATE ASSERTION NoClashes CHECK
   ( NOT EXISTS
     ( SELECT A.room
       FROM   ClassesInRooms A, ClassesInRooms B
       WHERE  A.room = B.room
              AND A.day = B.day
              AND A.hour = B.hour
              AND A.name != B.name
              AND A.day != 'Someday'
     )
   )
 );

Triggers

Note that this trigger is written in SQL – not in Oracle!

 CREATE TRIGGER FixClashes
 BEFORE INSERT ON Classes
 REFERENCING NEW ROW AS new
 FOR EACH ROW
 WHEN (EXISTS 
  (SELECT *
   FROM ClassesInRooms A
   WHERE A.room = new.room
     AND A.day = new.day
     AND A.hour = new.hour))
 SET new.day = 'Someday';

And here is an Oracle trigger with the same functionality

 CREATE OR REPLACE TRIGGER FixClashes2
   BEFORE INSERT ON Classes
   FOR EACH ROW
 DECLARE
   n integer;
   r Teachers.room%TYPE;
 BEGIN
   SELECT room INTO r
   FROM Teachers t, Courses c
   WHERE t.name = c.teacher and c.name = :NEW.course;
   SELECT COUNT(*) INTO n
   FROM ClassesInRooms
   WHERE room = r and day = :NEW.day and hour = :NEW.hour;
   IF (n > 0) THEN
     :NEW.day := 'Someday';
   END IF;
 END;
 .
 RUN;
 SHOW ERRORS;