Extended ER: Generalisation, Specialisation and Aggregation

Concept

The basic ER model cannot express two situations well: an entity type that has several distinct varieties, and a relationship that itself takes part in another relationship. The extended entity relationship model adds three features to handle them.

Specialisation

Specialisation is top down. You start with one entity type and identify subgroups that have attributes or relationships the others do not.

              +------------+
              |   PERSON   |     emp_id, name, dob   <- shared
              +-----+------+
                    |
                   / 
                  /ISA                      <- triangle
                 +-----+
                /       
        +----------+  +----------+
        | STUDENT  |  | LECTURER |
        +----------+  +----------+
         roll_no,       staff_no,
         programme      designation, salary

  Shared attributes live once, on PERSON.
  Specific attributes live on the subclass that needs them.
  A subclass INHERITS every attribute and relationship of
  its superclass.

Generalisation

Generalisation is bottom up and is the same structure reached from the other direction. You notice that CAR and TRUCK share registration, model and year, so you factor the common part out into VEHICLE.

Constraints on a hierarchy

Two independent constraints apply, giving four combinations.

  disjoint + total     every vehicle is a car or a truck,
                       never both
  disjoint + partial   an account is savings or current, and
                       some accounts are neither
  overlapping + total  every person is a student or staff,
                       and some are both
  overlapping+partial  a person may be a student, a staff
                       member, both, or neither

Aggregation

Aggregation solves the second problem: a relationship needs to take part in another relationship, and the ER model has no way to connect a diamond to a diamond.

  THE PROBLEM

    EMPLOYEE ---< WORKS_ON >--- PROJECT

  Now: a MANAGER supervises an employee working on a
  particular project. Not the employee in general, and not
  the project in general - the COMBINATION.

  THE SOLUTION: treat the relationship as one abstract entity

    +-------------------------------------+
    |  EMPLOYEE ---< WORKS_ON >--- PROJECT |   <- aggregated
    +------------------+------------------+
                       |
                 < SUPERVISES >
                       |
                   +--------+
                   | MANAGER|
                   +--------+

  The dashed box is the aggregation. It says: this whole
  relationship is now a single thing that can participate
  in another relationship.

Converting to tables

Aggregation converts simply: the aggregated relationship becomes a table with its own primary key, and the outer relationship refers to that key.

Example

  A bank

    ACCOUNT ( acc_no, balance, opened_on )
       ISA, disjoint, total
       SAVINGS ( interest_rate )
       CURRENT ( overdraft_limit )

  Superclass plus subclass tables:

    accounts ( acc_no PK, balance, opened_on, acc_type )
    savings  ( acc_no PK and FK -> accounts, interest_rate )
    current  ( acc_no PK and FK -> accounts, overdraft_limit )

  Disjoint is enforced by acc_type plus a check that a row
  exists in only one subclass table. Total is enforced by
  requiring every account to appear in one of them.

Common mistakes

• Treating generalisation and specialisation as different structures. The result is the same hierarchy; only the direction of thought differs.
• Forgetting the constraints. A hierarchy without disjointness and completeness marked is incomplete, and marks are lost.
• Using aggregation where a plain ternary relationship would do. Aggregation is for when a relationship must participate in another relationship.
• Confusing aggregation here with the aggregate functions of query languages. Same word, unrelated meanings.
• Creating a hierarchy for two attributes. If the subclasses barely differ, one table with a type column is simpler and faster.

Exam and interview questions

1. Differentiate generalisation and specialisation.
2. Explain disjoint and overlapping, total and partial, with an example of each combination.
3. What is aggregation in the ER model, and which problem does it solve?
4. Describe three ways to convert an ISA hierarchy into tables, with the trade offs.
5. Give an example of an overlapping specialisation.

Practice

1. Draw a specialisation of EMPLOYEE into PERMANENT and CONTRACT, and state both constraints.
2. Model a university where a person may be both a student and a staff member. Which disjointness constraint applies?
3. Give a case needing aggregation and convert it to tables.

Conclusion

Specialisation goes top down, generalisation bottom up, and both produce an ISA hierarchy constrained by disjointness and completeness. Aggregation lets an entire relationship behave as one entity so it can participate in another — the one thing basic ER cannot express.