Databases (HT2014)

Tutorial 1 Solutions

E-R diagrams

One suggestion is:

E-R Diagram

The question doesn't say whether each course is taught by one teacher, or whether more than one teacher can teach on a course. Your solution will depend on the assumptions that you make. This diagram assumes that each course is taught by one teacher. One of the relationships shown in the diagram is redundant (which one?), so could be removed.

If we assume that more than one teacher can teach on a course, then the diagram would be different.

The subclassing could be discussed, since it means that a teacher can only have at most one title (e.g. Professor). 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.

Transation to relations

Translating the E-R diagram except for the specialisation gives:

For the specialisation we can choose one of three different approaches. Suppose we choose the E-R approach, which means we get an extra relation that looks like

This solution suffers from redundancy since given a course, a weekday and an hour, it is possible to find two occurences of rooms, one directly in Classes and one going via joins with Courses and Teachers, and we have no guarantees that these two will be the same. We can see formally why this is so when we discuss functional dependencies on the next exercises.


Last Modified: 17 September 2014 by Graham Kemp