Supertype and Subtype Entities

---

An entity type is a set of entity instances.

A subtype entity is a subset of another entity type, called the supertype entity.

• e.g.,
  • Managers are a subset of employees
    • Manager is a subtype entity of Employee
    • Employee is a supertype entity
• supertype entity usually has several subtypes
• Attributes of the supertype apply to all subtypes
• Attributes of a subtype do not apply to other subtypes or the supertype

Benefits of Supertype and Subtype Entities

• subtypes highlight subsets of data with different attributes and relationships than the supertype
  • clarifies database semantics
• optional supertype attributes become required subtype attributes
  • results in compact tables with fewer NULL values
• an attribute that is repeated in several subtypes becomes a single supertype attribute

Similar Entities and Optional Entities

Supertype and subtype entities are often created from similar entities and optional attributes.

Similar entities are entities that have many common attributes and relationships.

• become subtypes of a new supertype entity
• Common attributes and relationships move to the new supertype entity
• Attributes and relationships that are not shared remain with the subtype entities

An entity with many optional attributes also suggests new supertype and subtype entities.

• entity becomes a supertype entity and retains all required attributes
• Optional attributes become required attributes of new subtype entities

Partitions

A partition of a supertype entity is a group of mutually exclusive subtype entities.

• can have several partitions
• Subtype entities within each partition are disjoint and do not share instances
  • mutually exclusive
• Subtype entities in different partitions overlap and do share instances
• In diagrams, subtype entities within each partition are vertically aligned
• Subtype entities in different partitions are horizontally aligned
• Each partition corresponds to an optional partition attribute of the supertype entity
  • indicates which subtype entity is associated with each supertype instance

Partition Example

IsA Relationship

The identifying relationship between a supertype entity and a subtype entity is called an IsA relationship.

• e.g., Manager-IsA-Employee relates each manager instance to the corresponding employee instance
  • employee numbers, which identify all employees, also identify managers
• every subtype has an IsA relationship to the supertype
• this relationship is assumed
  • may be omitted from an ER diagram
• takes the place of an identifying attribute

Database Design

Create Supertype and Subtype Entities

Step	Activity
4A	Identify supertype and subtype entities.
4B	Replace similar entities and optional attributes with supertype and subtype entities.
4C	Identify partitions and partition attributes.
4D	Document supertypes, subtypes, and partitions in glossary and ER diagram.

Creating supertype and subtype entities is the last of four conceptual design steps:

1. Discover entities, relationships, and attributes
2. Determine cardinality
3. Distinguish strong and weak entities
4. Create supertype and subtype entities