Enhanced ER Model and Transformation
Tags: #◇Problem Solving #◇CCINFOM #◇EERD #◇Database Design #◇Supertype #◇Subtype #◇M6
◇Key Definitions
Supertype: A generic entity type that has a relationship with one or more subtypes. Subtype: A subgrouping of the entities in an entity type that has distinct attributes from other subgroupings. Attribute Inheritance: Subtypes inherit all attributes of the supertype (including the primary key) and participate in relationships defined at the supertype level.
◇Theoretical Framework
Generalization vs. Specialization Generalization: The bottom-up process of defining a more general entity type from a set of more specialized ones. Specialization: The top-down process of defining one or more subtypes from a supertype.
Supertype Constraints Completeness Constraint:
Total Specialization (double line): An instance of the supertype MUST be a member of at least one subtype.
Partial Specialization (single line): An instance of the supertype does NOT have to be a member of any subtype. Disjointness Constraint:
Disjoint Rule (d): An instance can be only ONE of the subtypes.
Overlap Rule (o): An instance can be more than one of the subtypes simultaneously. Subtype Discriminators: Attributes in the supertype that dictate which subtype(s) the instance belongs to.
◇Worked Examples: Transforming EERD to Relations
- Regular Entities: Simple attributes map directly. Composite attributes are broken down into their simple components. Multi-valued attributes become a separate relation with a foreign key back to the original entity.
- Weak Entities: Become a separate relation. The primary key is a composite of its own partial identifier and the primary key of its identifying strong entity.
- 1:M Relationships: The primary key on the "one" side becomes a foreign key on the "many" side.
- M:N Relationships: Create a new relation (intersection table) whose primary key is the composite of the primary keys of the two entities.
- 1:1 Relationships: The primary key of the mandatory side becomes a foreign key on the optional side.
- Associative Entities: Like M:N, unless a specific surrogate identifier is assigned.
- Unary 1:M Relationships: A recursive foreign key is placed in the same relation.
- Unary M:N Relationships: Requires two relations—one for the entity and one associative relation.
- Supertype/Subtype: Create one relation for the supertype and one for each subtype. The supertype's primary key also becomes the primary key of each subtype relation (establishing a 1:1 relationship).
◇References & Navigation
- Previous Topic: ◇Modeling Data
- Next Topic: ◇Relational Model and Normalization
- Related Notes: ◇Introduction to Databases
ACTIVE RECALL VERIFICATION
Select an answer to verify conceptual comprehension and reveal the technical rationale.
What is the defining characteristic of a Supertype in an Enhanced ER Model?
Which term describes the process where subtypes automatically possess all attributes and relationships of their supertype?
What is the top-down process of defining one or more subtypes from a single supertype called?
According to the Completeness Constraint, what does Total Specialization require?
Under the Disjointness Constraint, the Overlap Rule (o) specifies that:
When transforming a 1:M relationship into relational tables, where is the foreign key placed?
How is a Many-to-Many (M:N) relationship resolved during the transformation to relations?
When transforming a Weak Entity into a relation, what forms its primary key?
How are multi-valued attributes handled when transforming an EERD to relations?
What is a Subtype Discriminator?