Tutorial 4

Creating tables

Returning to the same domain once again, you have identified the following relations:

• Teachers(name, room)
  room → Rooms.name
• TeacherTitles(teacher, title)
  teacher → Teachers.name
• Rooms(name, nrSeats)
  nrSeats != 13
  nrSeats > -2
  
• Courses(name, teacher, nrStudents)
  teacher → Teachers.name
• Classes(course, day, hour)
  course → courses.name
  day is any normal weekday except Sunday, or it could be Someday.

Write SQL statements that would create these relations as tables. Remember to include all constraints in your tables, including the (somewhat non-ordinary) value constraints given in the schema above.

Also do the following exercises from "Database Systems: The Complete Book, 2/E":

• 7.2.1 a
• 7.2.3 a,c

If you have the first edition of the textbook, the following exercises are similar:

• 7.2.1 b
• 7.2.4 b,c

Database modifications

Do the following exercises from "Database Systems: The Complete Book, 2/E":

• 6.5.1

If you have the first edition of the textbook, the following exercises are similar:

• 6.5.1

Views

With the tables created above, create the views:

• All teachers that hold lectures on Someday.
• All rooms where, at any time, two classes take place at the same time.

Assertions

• Write an assertion that guarantees that there are no colliding courses except on Somedays.

Also do the following exercises from "Database Systems: The Complete Book, 2/E":

• 7.4.1 a (as assertion)

If you have the first edition of the textbook, the following exercises are similar:

• 7.4.2 b (as assertion)

Triggers

Write a trigger that, whenever a class is inserted that would collide with another class in the same room, instead schedule that class to be on Someday.

Also do the following exercises from "Database Systems: The Complete Book, 2/E":

• 7.4.1 a (as trigger)

If you have the first edition of the textbook, the following exercises are similar:

• 7.4.2 b (as trigger)