Relationships, Relationship Sets and Degree

Concept

A relationship is an association among two or more entities. As with entities, there is a definition and a collection.

A relationship type is drawn as a diamond connected by lines to the participating entity rectangles.

Degree of a relationship

The overwhelming majority of relationships are binary. If a diagram is full of ternary diamonds, an entity type has usually been missed.

Recursive relationships and roles

        +------------+
        |  EMPLOYEE  |
        +-----+------+
          |        |
   manager|        |subordinate      <- ROLE NAMES
          |        |
        +-v--------v-+
          MANAGES  /
                  /
          +-------+

  One entity type appears twice in the same relationship, so
  ROLE NAMES are compulsory. Without them the diagram cannot
  say which side is the manager.

Attributes on a relationship

A relationship type may have attributes of its own. The test is precise: the value depends on the combination of entities, not on either one alone.

   STUDENT ----<  ENROLS  >---- COURSE
                    |
              ( marks ) ( semester )

  marks belongs to neither entity:
    it is not a property of the student  - they have many marks
    it is not a property of the course   - it has many marks
    it exists only for the PAIR
  therefore marks is an attribute of the RELATIONSHIP.

This is one of the highest value ideas in ER modelling. When a many to many relationship is converted to tables, the relationship becomes a table and its attributes become its columns — which is precisely why the enrolment table holds the marks.

Example

   A fragment with all three degrees

   BINARY
     STUDENT ----< ENROLS >---- COURSE          + marks

   UNARY
     COURSE  ----< PREREQUISITE >---- COURSE
       roles: requires / required_by

   TERNARY
     SUPPLIER ----< SUPPLIES >---- PART
                       |
                    PROJECT                     + quantity

   The ternary case is genuine here: quantity depends on all
   three together. A supplier supplies a part FOR a project,
   and the quantity is meaningless without all three.

When a relationship should become an entity

Promote a relationship to an entity type when:

• It needs a key of its own, because the same pair may be associated more than once — a student may enrol in the same course in two semesters.
• It participates in further relationships. Aggregation, covered later in this phase, exists for exactly this.
• It accumulates many attributes and starts to feel like a thing in its own right — an ENROLMENT, a LOAN, a BOOKING.

Common mistakes

• Confusing degree with cardinality. Degree counts participating entity types; cardinality counts how many entities may participate. Different questions.
• Omitting role names on a recursive relationship. The diagram is then ambiguous, and marks are lost.
• Attaching a relationship attribute to an entity. Putting marks on STUDENT is wrong — a student has many marks.
• Using a ternary relationship where two binaries would do. Only use ternary when the fact genuinely requires all three at once.
• Naming relationships with nouns. Use verbs: ENROLS, TEACHES, SUPPLIES.

Exam and interview questions

1. Define relationship, relationship type and relationship set.
2. What is the degree of a relationship? Give an example of each degree.
3. What is a recursive relationship and why are role names required?
4. When does an attribute belong to a relationship rather than an entity? Give the test.
5. Give three signs that a relationship should be promoted to an entity type.

Practice

1. For a hospital, write three binary relationships, one recursive and one that genuinely needs three entity types.
2. Decide where each attribute belongs: date of issue for a book loan, salary of an employee, quantity supplied, room number of a patient.
3. Explain in three sentences why a relationship attribute becomes a column of the junction table.

Conclusion

Relationships associate entities; degree counts how many entity types take part; role names disambiguate recursion; and an attribute belongs to the relationship whenever it depends on the combination rather than on either side alone.