Constraints on Generalizations in DBMS

 Constraints on Generalizations:

 

For modeling an enterprise more accurately, the DB designer may place certain constraints on a particular generalization. One type of constraint involves determining which entities can be members of a given lower-level entity set. The membership may be one of the following:

a)    Condition-defined:

• 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 ex: suppose that a higher-level entity set account has an attribute account-type. All account entities are evaluated on the defining account-type attribute.
• Only those entities that satisfy the condition account-type = “savings account” are allowed to belong to the lower-level entity set person.
• Similarly, all entities that satisfy the condition account-type = “checking account” are included in the checking account.
• Since all the lower-level entities are evaluated on the basis of the same attribute (on account-type), this type of generalization is said to be attribute-defined.

 b)   User-defined:

• User-defined lower-level entity sets are not constrained by a membership condition but, the database user assigns entities to a given entity set.
• For ex: assume that, after 3 months of employment, bank employees are assigned to one of four work teams.
• We therefore 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 on the basis of an explicit defining condition.
• Instead, the user in charge of this decision makes the team assignment on an individual basis.
• The assignment is implemented by an operation that adds an entity to an entity set.

A second type of constraint relates to whether or not entities may belong to more than one lower-level entity set within a single generalization. The lower-level entity sets may be one of the following:

a)    Disjoint:

• This constraint requires that an entity belong to no more than one lower level entity set.
• In our ex: an account entity can satisfy only one condition for the account-type attribute.
• It can be either a savings account or a checking account, but cannot be both.

b)   Overlapping:

• In overlapping generalizations, the same entity may belong to more than one lower-level entity set within a single generalization.
• Consider the employee work team example, and assume that certain managers participate in more than one work team.
• A given employee may therefore appear in more than one of the team entity sets that are lower-level entity sets of employee. Thus, the generalization is overlapping.
• As another ex: suppose generalization applied to entity sets customer and employee leads to a higher-level entity set person.
• The generalization is overlapping if an employee can also be a customer.

Lower-level entity overlap is the default case. A disjointness constraint must be placed explicitly on a generalization or specialization. We can note a disjointedness constraint in an E-R diagram by adding the word disjoint next to the triangle symbol.

 

A final constraint, the completeness constraint on a generalization or specialization, specifies whether or not an entity in the higher-level entity set must belong to at least one of the lower-level entity sets within the generalization/specialization. This constraint may be one of the following:

 a)    Total generalization or specialization:

• Each higher-level entity must belong to a lower-level entity set.

 

b)   Partial generalization or specialization:

• Some higher-level entities may not belong to any lower-level entity set.
• Partial generalization is the default.
• The total generalization in an E-R diagram is specified by using a double line to connect the box representing the higher-level entity set to the triangle symbol.

 

The account generalization is total: All account entities must be either a savings account or a checking account. When the generalization is partial, a higher-level entity is not constrained to appear in a lower-level entity set. The work team entity sets illustrate a partial specialization. The generalization of checking-account and savings-account into account is a total, disjoint generalization.