Mod4 Advanced-data Modeling Flashcards
Result of adding more semantic constructs to the original
entity-relationship (ER) model
EERM
Diagram using this model
EERD
Contains unique characteristics of each entity subtype
Entity subtype
◆ Generic entity type related to one or more entity subtypes
◆ Contains common characteristics
Entity supertype
✓Depicts arrangement of higher-level entity supertypes and lower-level entity
subtypes
✓Relationships described in terms of “IS-A” relationships
✓Subtype exists only within the context of the supertype
✓Every subtype has only one supertype to which it is directly related
✓Can have many levels of supertype/subtype relationships
Specialization heirarchy
✓Enables 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, the supertype and its subtype(s) maintain a 1:1
relationship
✓Entity subtypes inherit all relationships in which the supertype entity participates
✓Lower-level subtypes inherit all attributes and relationships from all upper-level
supertypes
inheritance
✓ An attribute in the supertype entity
✓ Determines to which entity subtype each supertype occurrence is
related
✓ Default comparison condition for subtype discriminator attribute
is equality comparison
✓ Subtype discriminator may be based on other comparison
condition
subtype discriminator
◆ Also called non-overlapping subtypes.
◆ Subtypes that contain a unique subset of the supertype
entity set
disjoint subtypes
Subtypes that contain non-unique subsets of the supertype
entity set.
overlapping subtypes
◆ Specifies whether entity supertype occurrence must be a member of at least
one subtype
completeness constraints
◆ Symbolized by a circle over a single line
◆ Some supertype occurrences are not members of any subtype
partial completeness
◆ Symbolized by a circle over a double line
◆ Every supertype occurrence must be a member of at least one subtype
total completeness
- Identifies more specific entity subtypes from higher-level entity supertype
- Top-down process
- Based on grouping unique characteristics and relationships of the subtypes
Specialization
- Identifies more generic entity supertypes from lower-level entity subtypes
- Bottom-up process
- Based on grouping common characteristics and relationships of the
subtypes
generalization
the most important characteristic of an entity
primary key
a real-world identifier used to uniquely identify real-world objects
◆ Familiar to end users and forms part of their day-to-day business
vocabulary
◆ Generally, data modeler uses natural identifier as primary key of entity being
modeled
◆ May instead use composite primary key or surrogate key
Natural keys
As identifiers of composite entities
◇ In which each primary key combination is allowed once in M:N
relationship
◆ As identifiers of weak entities
◇ In which a weak entity has a strong identifying relationship with the
parent entity
◆ Automatically provides the benefit of ensuring that there cannot be duplicate
values
composite primary keys
When used as identifiers of weak entities normally used to represent:
◆ Real-world object that is existent-dependent on another realworld object
◆ Real-world object that is represented in the data model as two
separate entities in a strong identifying relationship
◆ Dependent entity exists only when it is related to the parent entity
composite primary keys
Especially helpful when there are:
◆ No natural key
◆ Selected candidate key has embedded semantic contents
◆ Selected candidate key is too long or cumbersome
composite primary keys
require skills acquired through experience
Data modeling and design
◆ Foreign keys work with primary keys to properly implement relationships in the
relational model
◆ Put the primary key of the “one” side on the “many” side as a foreign key
◆ Primary key: parent entity
◆ Foreign key: dependent entity
◆ In a 1:1 relationship, there are two options:
◆ Place a foreign key in both entities (not recommended)
◆ Place a foreign key in one of the entities
◇ Primary key of one of the two entities appears as the foreign key of
other
Implementing 1:1 relationships
◆ Values change over time
◆ Must keep a history of data changes
◆ Must create new entity in 1:M relationships with original entity
◆ New entity contains a new value, date of the change
time-variant data
occurs when the relationship is improperly or incompletely
identified
◆ Represented in a way not consistent with the real world
design trap
Most common design trap
occurs when one entity is in two 1:M relationships with other
entities
◆ Produces an association among other entities not expressed in the
model
Fan trap
Occurs when there are multiple relationship paths between related entities
◆ Main concern is that redundant relationships remain consistent across model
◆ Some designs use redundant relationships to simplify the design
Redundant relationships
