Question

You are creating an enhanced entity relationship model for a campus information system. A junior team member who is helping you asks you to describe the following terms with examples of relevant entities from the system, for example, students, activities etc.

1.       Supertype and Subtype

2.       Disjoint and Overlapping Discriminator

3.       Total and Partial Completeness

4.       Attribute Inheritance

Answer 1

Supertype and Subtype in Entity Relationship Model (ER-Model) :

It can considered as a Extended Entity Relationship Model - An Introduction

• It is the Result of adding more & more semantic constructs to the original entity relationship (ER) model

•It’s a Diagram based. Using this model is called - an Enhanced ER Diagram or EER diagram (EERD)

•It Cartels nearly of the Object-oriented notions along with Entity Relationship notions.

About Entity Supertypes & Subtypes

Entity supertype – It’s a Generic entity type related to one or more entity based subtypes – It Contains common characteristics like

Entity subtype – It contains some exclusive features of every entity subtype and also it is used to keep a side unnecessary null(o) attributes when not shared by all other super entity types.

What is Supertype/Subtype Relationship in an ERD Diagram –

For Example -

Employee (Super type) & Professor (Sub type) with reference to the cardinality for (one : one) relationships

Entity Supertypes and Subtypes

• It’s a example data set with and/or without sub-type entity if present, where certain employees contain additional data as well

Disjoint and Overlapping Constraints

1. Disjoint subtypes –

· Also referred as non-overlapping subtypes

· It’s a subtypes that contains sole subset of supertype entity sets

· It’s a single attribute is coded for the type as well

Overlapping subtypes –

· It’s a subtypes that contains non-unique subsets of supertype entity set defined

· Having several attributes it’s a must in this case, also each represents a possible type.

Applying Overlapping subtypes – The supertype entity can hold multiple discriminators

Example - Discriminator Attributes with overlapping subtypes( in CMAPUS MIS)

Tables – PERSON / EMPLOYEE / STUDENT

Specialization hierarchy with overlapping subtypes

Specialization hierarchy constraint related situations

Type	DISJOINT CONSTRAINT	OVERLAPPING CONSTRAINT
PARTIAL	SUPERTYPE HAS ELECTIVE SUBTYPES SBUTYPE DISCRIMINATOR CAN BE NULL	SUPERTYPE HAS ELECTIVE SUBTYPES SUBTYPE DISCRIMINATORS CAN BE NULL
TOTAL	EVERY SUPERTYPE OCCURANCE IS MEMBER OF (AT LEAST ONE) SUBTYPE. SUBTYPE SETS ARE UNIQUE	EVERY SUPERTYPE OCCURANCE IS MEMBER OF (AT LEAST ONE) SUBTYPE. SUBTYPE SETS ARE NOT UNIQUE

Completeness Constraint

• Specifies whether entity supertype existence must be a member of at least one subtype

• Partial completeness – As supertype occurrences are not members of any subtype – therefore it is signified by a circle over a single line as shown above.

• Total completeness – Every supertype occurrence must be member of at least one subtype – signified by a circle over a double line as shown above

Completeness Constraint •

Partial or Total Completeness with Disjoint or Overlapping constraints (A Comparison).

Type	DISJOINT CONSTRAINT	OVERLAPPING CONSTRAINT
PARTIAL	· SUPERTYPE HAS ELECTIVE SUBTYPES · SUBTYPE DISCRIMINATOR CAN BE NULL(0) · SUBTYPE SETAS ARE EXCLUSIVE	· SUPERTYPE HAS ELECTIVE SUBTYPES · SUBTYPE DISCRIMINATOR CAN BE NULL(0) · SUBTYPE SETAS ARE EXCLUSIVE
TOTAL	· EACH SUPERTYPE INSTANCE IS A MEMBER OF A ( AT LEAT ONE) SUBTYPE · SUBTYPE DISCRIMINATOR CANOT BE NULL · SUBTYPE SETS ARE UNIQUE	· EACH SUPERTYPE INSTANCE IS A MEMBER OF A ( AT LEAT ONE) SUBTYPE · SUBTYPE DISCRIMINATOR CANOT BE NULL · SUBTYPE SETS ARE NOT UNIQUE

Attribute Inheritance

• it Enables entity subtype to inherit attributes and relationships of supertype easily.

• All entity subtypes inherit primary key attribute from their supertype as well

• At the time of deploying it , supertype and its subtype(s) used to maintain (one : one) relationship for sure

• Entity subtypes inherit all relationships in which supertype entity for sure