Supertype and Subtype with Example

In the previous post (entity and its types) we define the concept of super and sub types. Let us thoroughly understand this concept.

  • Let us assume an entity named EMPLOYEE. The entity employee has some attributes-empID, Name, Birth Date, Age etc.
    Employee ERD-Supertype and Subtype
  • But sometimes we will have special employees that have some different attributes to other employees.
  • Imagine the employees at airline company, there are different type of employees including Accountants, Mechanics, Pilots, Flight attendants etc. A quick solution would be to include another attribute named “type” to the entity EMPLOYEE.
    Employee ERD with type-Supertype and Subtype
  • But the type attribute would work for some cases and in some cases, it doesn’t work as it create problems.
    For example, an accountant has a CPA(Certified Public Accountant) that no other employee type has.
    A pilot has a flying qualification and a pilot licence that no other employee has.
    A mechanic has a qualification of aircraft engineering that no other employee has.
  • So, this type of situation can be handled using the concept of sub-type and super type because we can use sub-types to give unique attributes to those types of employees.
    Supertype and Subtype ERD
  • Each sub-type(child) will automatically inherit all the attributes from the super type(parent) and each sub-type have some unique attributes that a parent and the other sub-type doesn’t have.
  • So EMPLOYEE-super-type(parernt)
    and PILOT, ACCOUNTANT, MECHANICS, FLIGHT_ATTENDANTS-sub-types(child)

Business Rules for Supertype and subtype Relationships (or) Constraints in Supertype and Subtype Discriminators :

  • Total Specialization
  • Partial Specialization
  • Disjoint Rule
  • Overlap Rule

The first two rules i.e. Total Specialization Rule and Partial Specialization Rule are Completeness Constraints.
The Last two rules i.e. Disjoint Rule and Overlap Rule are Disjoint Constraints.

Completeness Constraints :
1) Total specialization specifies that each entity instance of the supertype must be a member of some subtype in the specialization/generalization. Total Specialization is shown in EER Diagrams by a double line.
ExampleIn following Fig. A PATIENT must either be an OUTPATIENT or a RESIDENT PATIENT.
Total Specialization- Supertype and Subtype
2) Partial specialization specifies that an entity instance of the supertype does not have to belong to any subtype, and may or may not be an instance of one of the subtypes. Partial Specialization is shown in EER Diagrams by a single line.
Example : In the following Fig. If a VEHICLE is a car it will appear as an instance of CAR, and if a truck as an instance of TRUCK. However, if the vehicle is a motorcycle it cannot appear as an instance of any subtype.
Partial Specialization-(Supertype and Subtype)
Disjoint Constraints :
3) Disjoint rule specifies that if an entity instance of the supertype is a member of one subtype, it cannot simultaneously be a member of any other subtype. The overlap is specified by placing an ‘d’ in the circle.

Example : Following Fig. shows that at any one time a PATIENT must be either an outpatient or a resident patient but cannot be both

Disjoint-(Supertype and Subtype)
4) Overlap rule specifies that an entity instance can simultaneously be a member of two (or more) subtypes. The overlap is specified by placing an ‘o’ in the circle.
Example : A part may be both purchased and manufactured

Overlap-(Supertype and Subtype)

Previous Home Next
Composite Entity Identifying and Non Identifying Relationships

     

Incoming search terms:

  • disjoint constraints in dbms
  • disjoint constraint in dbms
  • supertype and subtype in dbms
  • disjoint constraint
  • supertype and subtype relationships examples
  • disjoint and overlapping constraints in dbms
  • disjoint and overlapping constraints examples
  • disjoint constarints
  • disjoint in dbms
  • disjoint constraints

Leave a Reply