Please enable JavaScript.
Coggle requires JavaScript to display documents.
Data Modeling Advanced Concepts (Extended Entity Relationship Model (EERM)…
Data Modeling Advanced Concepts
Extended Entity Relationship Model (EERM)
Result of adding more semantic constructs to the original entity relationship (ER) model
Entity Supertypes and Subtypes
Entity supertype: Generic entity type related to one or more entity subtypes
Entity subtype: Contains unique characteristics of each entity subtype
Specialization Hierarchy
•Depicts arrangement of higher-level entity supertypes and lower-level entity subtypes
•Relationships are described in terms of “is-a” relationships
•Subtype exists within the context of a supertype
•Every subtype has one supertype to which it is directly related
•Supertype can have many subtypes
•Provides the means to:
–Support attribute inheritance
–Define a special supertype attribute known as the subtype discriminator
–Define disjoint/overlapping constraints and complete/partial constraints
Inheritance
•Enables an entity subtype to inherit attributes and relationships of the supertype
•All entity subtypes inherit their primary key attribute from their supertype
•At the implementation level, supertype and its subtype(s) maintain a 1:1 relationship
•Entity subtypes inherit all relationships in which supertype entity participates
•Lower-level subtypes inherit all attributes and relationships from its upper-level supertypes
Subtype Discriminator
•Attribute in the supertype entity that determines to which entity subtype the supertype occurrence is related
•Default comparison condition is the equality comparison
Disjoint and Overlapping Constraints
•Disjoint subtypes: Contain a unique subset of the supertype entity set
•Overlapping subtypes: Contain nonunique subsets of the supertype entity set
Completeness Constraint
Specifies whether each supertype occurrence must also be a member of at least one subtype
•Types
–Partial completeness: Not every supertype occurrence is a member of a subtype
–Total completeness: Every supertype occurrence must be a member of any subtype
Specialization and Generalization
Specialization
•Top-down process
•Identifies lower-level, more specific entity subtypes from a higher-level entity supertype
•Based on grouping unique characteristics and relationships of the subtypes
Generalization
•Bottom-up process
•Identifies a higher-level, more generic entity supertype from lower-level entity subtypes
•Based on grouping common characteristics and relationships of the subtypes
Primary Keys
Natural Keys or Natural Identifier
•Real-world identifier used to uniquely identify real-world objects
–Familiar to end users and forms part of their day-to-day business vocabulary
–Also known as natural identifier
–Used as the primary key of the entity being modeled
Desirable Primary Key Characteristics
Non intelligent
No change over time
Preferably single-attribute
Preferably numeric
Security-compliant
Use of Composite Primary Keys
•Identifiers of composite entities
–Each primary key combination is allowed once in M:N relationship
•Identifiers of weak entities
–Weak entity has a strong identifying relationship with the parent entity
Surrogate Keys
•Primary key used to simplify the identification of entity instances are useful when:
–There is no natural key
–Selected candidate key has embedded semantic contents or is too long
•Require ensuring that the candidate key of entity in question performs properly
–Use unique index and not null constraints
Design
Design Case 1: Implementing 1:1 Relationships
•Foreign keys work with primary keys to properly implement relationships in relational model
•Rule
–Put primary key of the parent entity on the dependent entity as foreign key
•Options for selecting and placing the foreign key:
–Place a foreign key in both entities
–Place a foreign key in one of the entities
Design Case 2: Maintaining History of Time-Variant Data
•Time-variant data: Data whose values change over time and for which a history of the data changes must be retained
–Requires creating a new entity in a 1:M relationship with the original entity
–New entity contains the new value, date of the change, and other pertinent attribute
Design Case 3: Fan Traps
•Design trap: Occurs when a relationship is improperly or incompletely identified
–Represented in a way not consistent with the real world
•Fan trap: Occurs when one entity is in two 1:M relationships to other entities
–Produces an association among other entities not expressed in the model
Design Case 4: Redundant Relationships
•Occur when there are multiple relationship paths between related entities
•Need to remain consistent across the model
•Help simplify the design