One suggestion is:
The assumption here is that each course can have multiple teachers but only one of those can teach any particular class.
We also assume that a teacher can have at most one title (e.g. Professor) and that there is no fixed list of allowed titles. If one wanted the possibility to have more than one title for teachers, TitledTeacher should be replaced by an entity Title with a single key attribute title, and a many-to-many relationship to replace the ISA.
Translating the E-R diagram except for the titles gives:
For the subentity we can choose either the null approach or the ER-approach. The null approach would require adding a special attribute to Teachers that (unlike other attributes) can be null. Using the E-R approach, we get an extra relation that looks like
There is some harmful redundancy between HoldsClass and Holds, where teachers
that hold classes are expected to be in the Holds relation for that course.
This can cause an update anomaly if a teacher is removed from the course but not
from the from it's corresponding classes.
We can prevent that by adding the following reference constraint to Classes:
(course, teacher) → Holds(course,teacher)
This means that we can also remove the other reference constraints in Classes
because they are implied by this one.
Arguably, there is a deletion anomaly in titles. For instance if the last Professor is removed then the Professor title disappears completely. If we wanted to keep track of titles independently we could use a Title entity related to teacher by a many to at most one relationship.
If we would have had a direct relationship between Classes and Rooms, there would be redundancy since there would be two ways of determining the room of a class - either by looking in class directly or by checking which room the teacher of the class resides in. This could lead to update anomalies if a teacher switches rooms.
Note that there are many additional constraints we would like on this schema, like preventing double booking courses, rooms or teachers. Typically altering the diagram to ensure this will make us looks other constraints, for instance currently we do prevent double booking of courses (no two classes in the same course can occur at the same time). If Teacher was the supporting entity of Class (instead of Course), we could not double book teachers. In the tutorial we added a unique constraint like this to the schema: (weekday,hour,teacher) unique. This prevents double booking teachers without enabling double booking courses.