Supertype and Subtype

Supertype and Subtype 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. 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. 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. 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/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.

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.
Example : In following Fig. A PATIENT must either be an OUTPATIENT or a RESIDENT PATIENT.

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.

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

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

]]>