Module 5
Advanced Data Modeling
.
Learning Objectives
In this Module, you will learn to discuss the steps
you might take to design a data model based on
business rules for a project modeled after a realistic
business or personal project. :
About the extended entity relationship (EER) model
How entity clusters are used to represent multiple
entities and relationships
The characteristics of good primary keys and how to
select them
How to use flexible solutions for special data-modeling
cases
2
Learning Objectives
Upon Successful Completion of this Module, you should be able to
discuss the steps you might take to design a data model based on
business rules for a project modeled after a realistic business or
personal project.
Data models describe the things that are important in a domain or solution, and
their attributes (or columns), including their types and the relationships between
them. Data modeling can be done for a number of reasons, including to clarify
and communicate and also to implement a solution on a particular technology
platform. Data modeling can occur at a number of different levels, from the
conceptual data models that are analogous to concept models and are used for
clarifying and communicating, through logical data models that include data
normalization to physical models used for implementation. There are a number
of diagrams such as the Class diagram and the Data Modeling diagram that can
be used to visualize the models, and a number of purpose built tools such as the
Database Builder and the Schema Composer that will assist a modeler to be
highly productive.
3
Database Normalization and MyOCC
Can you determine the business rules for MyOCC
4
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
Diagrams are super important to showing non-
technical people how the database is organized
5
Entity Supertypes and Subtypes
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
6
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
7
Specialization Hierarchy
8
Decision Tables
The Decision Table can be used simply to record the
conditions and the conclusions that form the basis of
decision making. Alternatively, implementation code can be
generated using code generation macros. It uses a clear and
understandable interface allowing the analyst to enter
conditions, condition value columns, defined values that act
as a decision point, and one or more conclusions.
9
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
10
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
11
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
12
Specialization Hierarchy with Overlapping Subtypes
13
Discriminator Attributes with Overlapping
Subtypes
14
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
15
Specialization Hierarchy Constraint
Scenarios
16
Specialization and Generalization
Specialization Generalization
• Top-down process • Bottom-up process
• Identifies lower-level, more • Identifies a higher-level, more
specific entity subtypes from a generic entity supertype from
higher-level entity supertype lower-level entity subtypes
• Based on grouping unique • Based on grouping common
characteristics and characteristics and
relationships of the subtypes relationships of the subtypes
17
Entity Cluster
Virtual entity type used to represent multiple entities
and relationships in ERD
Avoid the display of attributes to eliminate
complications that result when the inheritance rules
change
18
Tiny College ERD Using Entity Clusters
19
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
20
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
21
Desirable Primary Key Characteristics
Non intelligent
No change over time
Preferably single-attribute
Preferably numeric
Security-compliant
22
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
23
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
24
The M:N Relationship between
STUDENT and CLASS
25
Surrogate Primary 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
26
Data Used to Keep Track of Events
27
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
28
Selection of Foreign Key in a 1:1
Relationship
29
The 1:1 Relationship between
Department and Employee
30
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
31
Maintaining Salary History
32
Maintaining Manager History
33
Maintaining Job History
34
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
35
Incorrect ERD with Fan Trap Problem
Corrected ERD After Removal of the
Fan Trap
36
ERD For Kayak Browser
Redundant Relationships
Occur when there are multiple relationship paths
between related entities
Need to remain consistent across the model
Help simplify the design
38