COMP210: Database Design (F'06)
Week 3 Lecture Notes: Ternary Relationships
All of the relationships we've seen thus far are binary relationships: they connect two entities.
For example, the following binary many-to-many relationship for some enrollment system between a course offering and a person
(most attributes are omitted to simplify the diagrams):
A course offering must have at least one person, but may have many (at least two one would think: the instructor and a lonely
student), and a person may be involved in some way with many courses, or none at all.
The role attribute of the relationship indicates that person's role in the offering: student, instructor, etc.
This state of affairs may be acceptable, but if the list of roles is to be encoded as a reference entity's instances,
then a ROLE entity is needed and a ternary relationship exists between PERSON, ROLE, and COURSE_OFFERING:
The ternary relationship represents a single person's single role in a single course offering.
Trouble is, the cardinality and participation of this relationship can't be drawn in crow's foot notation.
If an "optional many" symbol is drawn next to COURSE_OFFERING, does this mean that many people, or none, may be involved with
the offering, or that many roles, or none, may be involved?
Even if we could employ an acceptable notation, there is still the problem that the next step of the design process, schema
conversion, only concerns itself with binary relationships.
Therefore, we need to address the ternary relationship in the entity-relationship model.
The technique is simple: replace the ternary relationship with a new entity that has binary relationships with the three
Choosing a name for this entity is key to understanding its role;
for this example, we might choose ENROLLMENT, POSITION, or PARTICIPATION:
An instance of the PARTICIPATION entity means, just as the did the ternary relationship:
a single person's single role in a single course offering.
This time, though, we can properly draw the crow's foot symbols on the three one-to-many binary relationships that result.
Every replacement of a ternary (or quaternary, quinary, etc.) relationship produces three (or four, or five, etc.) one-to-many
relationships with the "many" side oriented toward the new entity.
The opposite ends of the binary relationships always have a cardinality of one and a mandatory participation.
The participation of the "many" side is dictated by the requirements.
In this case, we see that a person may not choose to participate, and that a role may not be used by any participants, but that
a course offering must have at least one participant.
Astute designers may realize that, if there are only the two roles instructor and student, a more simple design is possible:
This model enjoys the benefit of a more rigourous specification of the minimum number of instructors and students: one in each
The COURSE_OFFERING side of each relationship must have optional participation, to indicate people that are not participants and
also people that do participate as students but not as instructors, and vice versa.
Now imagine that the requirements have expanded to include a final grade for a course offering.
The former ternary model simply needs add an attribute to the new PARTICIPATION entity:
The grade attribute must be optional because a person involved with the course in a non-student role will not receive a final
The same attribute can be added to the many-to-many relationship of the non-ternary model:
The attribute does not have to be optional here because all students enrolled in course offerings will have final grades
(we can assume this system is only used at the end of term when final grades are known).
As one final modification to the model, we can add a LETTER_GRADE reference entity used to look up each student's final letter
grade (e.g. A+, A, B-, C+) and GPA from their final grade in a course offering.
The relationship between PARTICIPATION and LETTER_GRADE must be optional on the side leading to LETTER_GRADE to cope with
the fact that not all participants are graded: "a participant will receive one letter grade if they are graded at all, and
a letter grade may be used by many graded participants, or none at all (maybe nobody has ever received a D+ at this institution).
It now sort of looks like there is a quaternary relationship between COURSE_OFFERING, ROLE, PERSON, and LETTER_GRADE:
a single person's role in a single course offering that results in a single, though optional, letter grade.
It is a teensy bit different than a normal quaternary relationship because of the optional "one" side of the relationship to
LETTER_GRADE, but now we're just splitting hairs.
To duplicate the LETTER_GRADE relationship to the final grade in the non-ternary model would require drawing a relationship
to the attribute of another relationship--not possible.
So, the only option remaining is to recognize the relationship between a student, course offering, and letter grade is ternary,
necessitating the insertion of a new entity:
Note the difference of the participation of the relationship between STUDENT_PARTICIPATION and LETTER_GRADE: mandatory on
the "one" side, unlike the similar relationship in the previous example.
It is mandatory simply because all student participants receive final grades and so will each be assigned a single letter grade.
The question of the day is: which design is better?
If you can guarantee (and get in writing from the client) that the system will never have more than two roles, then the
second has more data integrity protection built in: course offerings must have an instructor, and students must receive a final
grade and therefore a letter grade also.
However, if other roles emerge (e.g. auditing student, guest lecturer, lab monitor, course supervisor) then the first is the
obvious choice: it can accommodate new roles simply by adding new ROLE instances.
The second model must have new relationships added to do the same.
One rule in database design is that a running system should never require a design change just to add data (changes to the
design do occur, of course, just not in response to a new data value).