Chapter 7
Data Modeling
Using the Entity-
Relationship
(ER) Model
Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley
Chapter 7 Outline
Using High-Level Conceptual Data Models
for Database Design
A Sample Database Application
Entity Types, Entity Sets, Attributes, and
Keys
Relationship Types, Relationship Sets,
Roles, and Structural Constraints
Weak Entity Types
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Chapter 7 Outline (cont’d.)
Refining the ER Design for the COMPANY
Database
ER Diagrams, Naming Conventions, and
Design Issues
Example of Other Notation: UML Class
Diagrams
Relationship Types of Degree Higher than
Two
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Data Modeling Using the
Entity-Relationship (ER) Model
Entity-Relationship (ER) model
Popular high-level conceptual data model
ER diagrams
Diagrammatic notation associated with the ER
model
Unified Modeling Language (UML)
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Using High-Level
Conceptual Data Models
for Database Design
• Requirements collection and
analysis
•Database designers interview
prospective database users to
understand and document data
requirements
•Result: data requirements
•Functional requirements of the
application
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Using High-Level Conceptual
Data Models (cont’d.)
Conceptual schema
Conceptual design
Description of data requirements
Includes detailed descriptions of the entity
types, relationships, and constraints
Transformed from high-level data model into
implementation data model
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Using High-Level Conceptual
Data Models (cont’d.)
Logical design or data model mapping
Result is a database schema in implementation
data model of DBMS
Physical design phase
Internal storage structures, file organizations,
indexes, access paths, and physical design
parameters for the database files specified
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
A Sample Database Application
COMPANY
Employees, departments, and projects
Company is organized into departments
Department controls a number of projects
Employee: store each employee’s name,
Social Security number, address, salary, sex
(gender), and birth date
Keep track of the dependents of each
employee
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Entity Types, Entity Sets,
Attributes, and Keys
ER model describes data as:
Entities
Relationships
Attributes
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Entities and Attributes
Entity
Thing in real world with independent existence
Attributes
Particular properties that describe entity
Types of attributes:
• Composite versus simple (atomic) attributes
• Single-valued versus multivalued attributes
• Stored versus derived attributes
• NULL values
• Complex attributes
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Entities and Attributes (cont’d.)
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Entity Types, Entity Sets, Keys,
and Value Sets
Entity type
Collection (or set) of entities that have the
same attributes
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Entity Types, Entity Sets, Keys,
and Value Sets (cont’d.)
Key or uniqueness constraint
Attributes whose values are distinct for each
individual entity in entity set
Key attribute
• Uniqueness property must hold for every entity set
of the entity type
Value sets (or domain of values)
Specifies set of values that may be assigned to
that attribute for each individual entity
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Initial Conceptual Design of the
COMPANY Database
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Relationship Types, Relationship
Sets, Roles, and Structural
Constraints
Relationship
When an attribute of one entity type refers to
another entity type
Represent references as relationships not
attributes
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Relationship Types, Sets, and
Instances
Relationship type R among n entity types
E1, E2, ..., En
Defines a set of associations among entities
from these entity types
Relationship instances ri
Each ri associates n individual entities (e1,
e2, ..., en)
Each entity ej in ri is a member of entity set Ej
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Relationship Degree
Degree of a relationship type
Number of participating entity types
Binary, ternary
Relationships as attributes
Think of a binary relationship type in terms of
attributes
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Role Names and Recursive
Relationships
Role names and recursive relationships
Role name signifies role that a participating
entity plays in each relationship instance
Recursive relationships
Same entity type participates more than once
in a relationship type in different roles
Must specify role name
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Constraints on Binary
Relationship Types
Cardinality ratio for a binary relationship
Specifies maximum number of relationship
instances that entity can participate in
Participation constraint
Specifies whether existence of entity depends
on its being related to another entity
Types: total and partial
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Attributes of Relationship Types
Attributes of 1:1 or 1:N relationship types
can be migrated to one entity type
For a 1:N relationship type
Relationship attribute can be migrated only to
entity type on N-side of relationship
For M:N relationship types
Some attributes may be determined by
combination of participating entities
Must be specified as relationship attributes
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Weak Entity Types
Do not have key attributes of their own
Identified by being related to specific entities
from another entity type
Identifying relationship
Relates a weak entity type to its owner
Always has a total participation constraint
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Refining the ER Design for the
COMPANY Database
Change attributes that represent
relationships into relationship types
Determine cardinality ratio and participation
constraint of each relationship type
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
ER Diagrams, Naming
Conventions, and Design Issues
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Proper Naming of Schema
Constructs
Choose names that convey meanings
attached to different constructs in schema
Nouns give rise to entity type names
Verbs indicate names of relationship types
Choose binary relationship names to make
ER diagram readable from left to right and
from top to bottom
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Design Choices for ER
Conceptual Design
Model concept first as an attribute
Refined into a relationship if attribute is a
reference to another entity type
Attribute that exists in several entity types
may be elevated to an independent entity
type
Can also be applied in the inverse
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Alternative Notations for ER
Diagrams
Specify structural constraints on
relationships
Replaces cardinality ratio (1:1, 1:N, M:N) and
single/double line notation for participation
constraints
Associate a pair of integer numbers (min, max)
with each participation of an entity type E in a
relationship type R, where 0 ≤ min ≤ max and
max ≥ 1
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Relationship Types of Degree
Higher than Two
Degree of a relationship type
Number of participating entity types
Binary
Relationship type of degree two
Ternary
Relationship type of degree three
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Choosing between Binary and
Ternary (or Higher-Degree)
Relationships
Some database design tools permit only
binary relationships
Ternary relationship must be represented as a
weak entity type
No partial key and three identifying relationships
Represent ternary relationship as a regular
entity type
By introducing an artificial or surrogate key
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Constraints on Ternary (or
Higher-Degree) Relationships
Notations for specifying structural
constraints on n-ary relationships
Should both be used if it is important to fully
specify structural constraints
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Summary
Basic ER model concepts of entities and
their attributes
Different types of attributes
Structural constraints on relationships
ER diagrams represent E-R schemas
UML class diagrams relate to ER modeling
concepts
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
