Data Modeling
Advanced Concepts
TMF2034 Database Concept and Design 1
Objectives
• By the end of the class, the student is able to
• Translate M:M relationship into a composite entity
• Understand strong and weak relationship
• Adopt EERM into database conceptual design
• Select good primary keys
• DBMS Integrity
TMF2034 Database Concept and Design 2
Translating M:M relationship into a composite entity
TMF2034 Database Concept and Design 3
TMF2034 Database Concept and Design 4
TMF2034 Database Concept and Design 5
Relationship Strength
TMF2034 Database Concept and Design 6
TMF2034 Database Concept and Design 7
A Weak (Non-Identifying) Relationship
between COURSE and CLASS
TMF2034 Database Concept and Design 8
A Strong (Identifying) Relationship between
COURSE and CLASS
TMF2034 Database Concept and Design 9
Relationship Strength
Weak (non-identifying) relationship
• Primary key of the related entity does not contain a primary
key component of the parent entity
Strong (identifying) relationships
• Primary key of the related entity contains a primary key
component of the parent entity
TMF2034 Database Concept and Design 10
Weak Entities
• Conditions
• Existence-dependent
• Has a primary key that is partially or totally derived from parent entity in the
relationship
• Database designer determines whether an entity is weak based on
business rules
TMF2034 Database Concept and Design 11
A Weak Entity in an ERD
TMF2034 Database Concept and Design 12
A Weak Entity in a Strong Relationship
TMF2034 Database Concept and Design 13
Extended Entity Relational
Model
(EERM)
TMF2034 Database Concept and Design 14
Extended Entity Relationship Model (EERM)
• Result of adding more semantic constructs to the original entity
relationship (ER) model
• EER diagram (EERD): Uses the EER model
TMF2034 Database Concept and Design 15
TMF2034 Database Concept and Design 16
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
TMF2034 Database Concept and Design 17
TMF2034 Database Concept and Design 18
TMF2034 Database Concept and Design 19
Entity Supertypes and Subtypes (1)
• 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
TMF2034 Database Concept and Design 20
Entity Supertypes and Subtypes (2)
• Entity supertype: Generic entity type related to one or more entity
subtypes
• Contains common characteristics
• Entity subtype: Contains unique characteristics of each entity
subtype
• Criteria to determine the usage
• There must be different, identifiable kinds of the entity in the user’s
environment
• The different kinds of instances should each have one or more attributes that
are unique to that kind of instance
TMF2034 Database Concept and Design 21
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
TMF2034 Database Concept and Design 22
Disjoint and Overlapping Constraints
• Disjoint subtypes: Contain a unique subset of the supertype entity set
• Known as nonoverlapping subtypes
• Implementation is based on the value of the subtype discriminator attribute
in the supertype
• Overlapping subtypes: Contain nonunique subsets of the supertype
entity set
• Implementation requires the use of one discriminator attribute for each
subtype
TMF2034 Database Concept and Design 23
Discriminator Attributes with Overlapping Subtypes
TMF2034 Database Concept and Design 24
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
TMF2034 Database Concept and Design 25
Primary Keys
TMF2034 Database Concept and Design 26
Primary Keys
• Single attribute or a combination of attributes, which
uniquely identifies each entity instance
• Guarantees entity integrity
• Works with foreign keys to implement relationships
TMF2034 Database Concept and Design 27
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
TMF2034 Database Concept and Design 28
Desirable Primary Key Characteristics
Non intelligent
No change over time
Preferably single-attribute
Preferably numeric
Security-compliant
TMF2034 Database Concept and Design 29
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
TMF2034 Database Concept and Design 30
Use of Composite Primary Keys
• When used as identifiers of weak entities, represent a real-world
object that is:
• Existence-dependent on another real-world object
• Represented in the data model as two separate entities in a strong identifying
relationship
TMF2034 Database Concept and Design 31
The M:N Relationship between STUDENT and CLASS
TMF2034 Database Concept and Design 32
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
TMF2034 Database Concept and Design 33
Data Used to Keep Track of Events
• What is the primary key?
TMF2034 Database Concept and Design 34
