In Entity-Relationship (E-R) modeling, a supertype is a generalized entity type that contains common attributes shared by two or more subtypes.
A subtype is a specialized subgroup of a supertype entity that has its own unique attributes in addition to inheriting the attributes of the supertype.
- E-R modeling uses supertypes and subtypes to efficiently represent these relationships through inheritance.
- This structure supports reusability, clarity, and reduction of redundancy in database design by organizing data in a hierarchical manner.
Key Concepts:
1.) Supertype:
- A parent entity that represents a broad or generic concept.
- Contains attributes and relationships that are common to all subtypes.
Example: Employee is a supertype entity because all employees have attributes like Employee_ID, Name, and Hire_Date.
2.) Subtype:
- A child entity that represents a more specific category of the supertype.
- Inherits attributes from the supertype and can have its own unique attributes.
Example: Manager, Engineer, and Technician could be subtypes of Employee, with unique attributes like Department_Managed, Specialization, etc.
Note: The primary key of a subtype is identical to the primary key of the supertype, emphasizing that a subtype is a kind of the supertype.
Rules for Supertype/Subtype Relationships:
There are specific rules that govern how instances of supertypes and subtypes are related. These rules help define the specialization constraints:
1.) Total Specialization Rule:
- Every instance of the supertype must belong to one of the subtypes.
- Represented with a double line connecting the supertype to the circle in an E-R diagram.
- Example: Every Employee must be either a Full-Time Employee or a Part-Time Employee.
2.) Partial Specialization Rule:
- An instance of the supertype may or may not belong to any subtype.
- Represented with a single line to the specialization circle.
- Example: Some Vehicles may be categorized into Cars and Trucks, while others are uncategorized.
3.) Disjoint Rule:
- An instance of the supertype can only belong to one subtype.
- Example: A Person can either be a Student or an Instructor, but not both.
4.) Overlap Rule:
- An instance of the supertype can belong to multiple subtypes simultaneously.
- Example: A Person could be both a Customer and an Employee.
Example: Employee Tax Types:
Let’s consider an example using Employee as the supertype:
1.) Supertype: Employee
- Common attributes: Employee_ID, Name, Hire_Date
2.) Subtypes:
- Full-Time Employee W2 – Has attributes like Salary, Benefits_Eligibility
- Hourly Employee 1099 – Has attributes like Hourly_Rate, Contract_End_Date
In this case:
- Employee is the supertype entity.
- Full-Time Employee W2 and Hourly Employee 1099 are the subtypes.
- This model shows how both subtypes inherit shared attributes from Employee, while also maintaining their own distinct characteristics.
Advantages of Using Supertypes and Subtypes:
1.) Eliminates Redundancy:
- Common attributes are defined only once in the supertype.
2.) Improves Clarity:
- Highlights the hierarchical structure and relationships between general and specific entities.
3.) Supports Inheritance:
- Encourages modular and scalable database design.
4.) Facilitates Maintenance:
- Changes to shared attributes only need to be made in the supertype.
