There are three constraints that may apply to specialization/generalization which are as follows:
- Membership constraints
- Condition-defined membership constraint
- User-defined membership constraint
- Disjoint constraints
- Disjoint constraint
- Overlapping constraint
- Completeness constraints
- Total completeness constraint
- Partial completeness constraint
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.
- 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.
- 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.
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