Constraints of specialization and generalization in DBMS


There are three constraints that may apply to specialization/generalization which are as follows:

  1. Membership constraints
    • Condition-defined membership constraint
    • User-defined membership constraint
  2. Disjoint constraints
    • Disjoint constraint
    • Overlapping constraint
  3. Completeness constraints
    • Total completeness constraint
    • Partial completeness constraint

Membership constraints:

Condition-defined member constraint

  • In condition-defined lower-level entity sets, membership is evaluated on the basis of whether or not an entity satisfies an explicit condition or predicate.
  • For example, if the higher level-entity set employee has an attribute job_type, all entities that satisfy the condition job_type= “secretary” is included in secretary, and job_type= “instructor” is included in instructor.
  • Since all the lower-level entities are evaluated on the basis of the same attribute (job_type), this type of generalization is said to be attribute-defined.

User-defined member constraint

  • User-defined lower-level entity sets are not considered by a membership condition; rather, the database user assigns entities to a given entity set.
  • For example, after 3 months of employment, if university employees are supposed to be assigned to one of four work teams, we represent the teams as four lower-level entity sets of the higher-level employee entity set.
  • A given employee is not assigned to a specific team entity automatically based on an explicit defining condition.
  • Instead, the user in charge of this decision makes the team assignment on an individual basis.

Disjoint constraints:

Disjoint constraints

  • It refers that an entity belongs to no more than one lower-level entity set i.e, it specifies that the subclass of the specialization must be disjoint.

Overlapping constraints

  • It refers to that the same entity may belong to more than one lower-level entity set i.e, it specifies that the subclasses are not constrained to be disjoint.

Completeness constraint:

Total completeness constraint

  • Each higher-level entity must belong to a lower-level entity set.
  • It is represented by a double line in the EER diagram.

Partial completeness constraint

  • Some higher-level entities may not belong to any lower-level entity set.
  • It is represented by a single line in the EER diagram.

Read more on Database: Database Blogs