Supertype and SubType

SuperType and SubType in Data Modeling:

At times, few entities in a data model may share some common properties (attributes) within themselves apart from having one or more distinct attributes. Based on the attributes, these entities are categorized as Supertype and Subtype entities.

Supertype is an entity type that has got relationship (parent to child relationship) with one or more subtypes and it contains attributes that are common to its subtypes.

Subtypes are subgroups of the supertype entity and have unique attributes, but they will be different from each subtype.

Supertypes and Subtypes are parent and child entities respectively and the primary keys of supertype and subtype are always identical.

E.g. People, Bank Account, Insurance, Asset, Liability, Credit Card.

When designing a data model for PEOPLE, you can have a supertype entity of PEOPLE and its subtype entities can be vendor, customer, and employee. People entity will have attributes like Name, Address, and Telephone number, which are common to its subtypes and you can design entities employee, vendor, and consumer with their own unique attributes. Based on this scenario, employee entity can be further classified under different subtype entities like HR employee, IT employee etc. Here employee will be the superset for the entities HR Employee and IT employee, but again it is a subtype for the PEOPLE entity.

A person can open a savings account or a certificate deposit (fixed deposit) in a bank. These accounts have attributes like account number, account opening date, account expiry date, principal amount, maturity amount, account balance, interest rate, checks issued, pre-cancellation fee etc. While designing a data model, you can create supertype parent entity as “Account” and subtype entities as Savings Account and Certificate Deposit. Account entity will store attributes like account number, interest rate that are common to savings account and certificate deposit entity. Savings account entity will have attributes like account balance and checks issued. While fixed deposit entity will have attributes like account opening, account expiry date, principal amount, maturity amount, pre-cancellation fee etc. When you design a logical data model in this manner, it provides more meaning to the business and the attributes are not cluttered in one table.

Supertype & Subtype Example:

One good example for explaining this SuperType & SubType is describing the Tax Terms related to Employees. Here Employee is the SuperType or the Parent Entity whereas the two Child Entities “FULL TIME EMPLOYEE W2” & “HOURLY EMPLOYEE 1099” are the SubTypes.

More Examples:

Insurance entity can act as supertype and entities like health insurance, life insurance, auto insurance, liability insurance, malpractice insurance etc., can be subtype entities.

Asset entity can be the supertype and entities like bank, real estate, auto, retirement fund, stocks, and life insurance etc., can be subtype entities.

Liability entity may be the supertype and entities like real estate, auto loan, alimony, other debts etc., may be subtype entities.

Credit card entity may be the supertype and entities like balance transfer cards, cash back credit cards, business credit card, student credit card, secured credit card etc., may be the subtype entities.

Reach US!!!

 

• We provide online training in advanced OLTP Data Modeling and DIMENSIONAL Data Modeling.
• We also teach the data structures with Data Analytics Software “R”.
• We provide online Data Modeling Project Support when you get struck with projects that you are involved.
• We can analyse your Business Requirements, understand and suggest solutions to create OLTP Data Models and Dimensional Data models.
• We provide Data Modeling Interview Preparation Sessions with a lot of Data Modeling Interview Questions/Answers, which will help you to clear any interview.

 

If you are interested, please reach us at Training@LearnDataModeling.com or 91-9080157239