Chapter 7

Entity Relationship (ER) Modeling

 Learning Objectives

• After completing this chapter, you will be able to:

• Identify the main characteristics of entity relationship components
• Describe how relationships between entities are defined, refined, and
incorporated into the database design process
• See how ERD components affect database design and implementation
• Understand that real-world database design often requires the reconciliation of
conflicting goals

2
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
 The Entity Relationship Model (ERM)

• Forms the basis of an entity relationship diagram (ERD)

• Conceptual database as viewed by end user
• Database’s main components
• Entities
• Attributes
• Relationships

3
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
 Entities

• Object of interest to the end user

• Refers to the entity set and not to a single entity occurrence
• ERM corresponds to a table—not to a row—in the relational environment
• ERM refers to a table row as an entity instance or entity occurrence
• In Chen, Crow’s Foot, and UML notations, an entity is represented by a
rectangle that contains the entity’s name
• The entity name, a noun, is usually written in all capital letters

4
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
 Attributes (1 of 7)

• Characteristics of entities
• Required attribute: must have a value and cannot be left empty
• Optional attribute: does not require a value and can be left empty
• Domain: set of possible values for a given attribute
• Identifier: one or more attributes that uniquely identify each entity instance
• Composite identifier: primary key composed of more than one attribute
• Composite attribute: attribute that can be subdivided to yield additional
attributes
• Simple attribute: attribute that cannot be subdivided
• Single-valued attribute: attribute that has only a single value
• Multivalued attributes: attributes that have many values

5
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
Attributes (2 of 7)

6
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
Attributes (3 of 7)

7
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
 Attributes (4 of 7)

• Requirements of multivalued attributes

• Create several new attributes, one for each component of the original
multivalued attribute
• Develop a new entity composed of the original multivalued attribute’s
components
• Derived attribute: attribute whose value is calculated from other attributes
• Derived using an algorithm

8
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
Attributes (5 of 7)

9
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
Attributes (6 of 7)

10
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
 Attributes (7 of 7)

Table 4.2 Advantages and

Disadvantages of Storing
Derived Attributes
Derived Attribute: Stored Derived Attribute: Not
Stored
Advantage Saves CPU processing cycles Saves storage space
Saves data access time Computation always yields
Data value is readily available current value
Can be used to keep track of
historical data
Disadvantage Requires constant maintenance Uses CPU processing cycles
to ensure derived value is Increases data access time
current, especially if any values Adds coding complexity to
used in the calculation change queries

11
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
 Relationships, Connectivity, and Cardinality

• Association between entities that always operate in both directions

• Participants: entities that participate in a relationship
• Connectivity: describes the relationship classification
• Include 1:1, 1:M, and M:N
• Cardinality: expresses the minimum and maximum number of entity
occurrences associated with one occurrence of related entity
• In the ERD, cardinality is indicated by placing the appropriate numbers beside
the entities, using the format (x, y)

12
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
 Existence Dependence

• Existence dependence
• Entity exists in the database only when it is associated with another related
entity occurrence
• Existence independence
• Entity exists apart from all of its related entities
• Referred to as a strong entity or regular entity

13
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
 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

14
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
 Weak Entities (1 of 3)

• Conditions of a weak entity

• 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

15
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
Weak Entities (2 of 3)

16
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
Weak Entities (3 of 3)

17
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
 Relationship Participation (1 of 3)

• Optional participation
• One entity occurrence does not require a corresponding entity occurrence in a
particular relationship
• Mandatory participation
• One entity occurrence requires a corresponding entity occurrence in a
particular relationship

18
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
Relationship Participation (2 of 3)

19
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
Relationship Participation (3 of 3)

20
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
 Relationship Degree (1 of 2)

• Indicates the number of entities or participants associated with a

relationship
• Unary relationship: association is maintained within a single entity
• Binary relationship: two entities are associated
• Ternary relationship: three entities are associated
• Recursive relationship: relationship exists within a single entity type

21
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
Relationship Degree (2 of 2)

22
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
 Recursive Relationships (1 of 2)

• Relationship can exist between occurrences of the same entity set

• Naturally, such a condition is found within a unary relationship
- Common in manufacturing industries

• One common pitfall when working with unary relationships is to confuse

participation with referential integrity
• Similar because they are both implemented through constraints on the same
set of attributes

23
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
Recursive Relationships (2 of 2)

24
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
 Associative (Composite) Entities (1 of 2)

• Used to represent an M:N relationship between two or more entities

• Has a 1:M relationship with the parent entities
• Composed of the primary key attributes of each parent entity
• May also contain additional attributes that play no role in connective
process

25
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
Associative (Composite) Entities (2 of 2)

26
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
 Developing an ER Diagram (1 of 11)

• Activities involved in building and ERD

• Create a detailed narrative of the organization’s description of operations
• Identify business rules based on the descriptions
• Identify main entities and relationships from the business rules
• Develop the initial ERD
• Identify the attributes and primary keys that adequately describe entities
• Revise and review ERD

27
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
Developing an ER Diagram (2 of 11)

28
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
Developing an ER Diagram (3 of 11)

29
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
Developing an ER Diagram (4 of 11)

30
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
Developing an ER Diagram (5 of 11)

31
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
Developing an ER Diagram (6 of 11)

32
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
Developing an ER Diagram (7 of 11)

33
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
Developing an ER Diagram (8 of 11)

34
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
Developing an ER Diagram (9 of 11)

35
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
Developing an ER Diagram (10 of 11)

36
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
 Developing an ER Diagram (11 of 11)

Table 4.4 Components of the ERM

Entity Relationship Connectivity Entity

SCHOOL operates 1:M DEPARTMENT

DEPARTMENT has 1:M STUDENT

DEPARTMENT employs 1:M PROFESSOR

DEPARTMENT offers 1:M COURSE

COURSE generates 1:M CLASS

SEMESTER includes 1:M CLASS
PROFESSOR is dean of 1:1 SCHOOL

PROFESSOR chairs 1:1 DEPARTMENT

PROFESSOR teaches 1:M CLASS

PROFESSOR advises 1:M STUDENT

STUDENT enrolls in M:N CLASS

BUILDING contains 1:M ROOM
ROOM is used for 1:M CLASS
Note: ENROLL is the composite entity that implements the
M:N relationship “STUDENT enrolls in CLASS.”

37
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
 Database Design Challenges: Conflicting Goals (1 of 2)

• Database designers must often make design compromises that are

triggered by conflicting goals
• Database design must conform to design standards
• High processing speed may limit the number and complexity of logically
desirable relationships
• Maximum information generation may lead to loss of clean design structures
and high transaction speed

38
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
Database Design Challenges: Conflicting Goals (2 of 2)

39
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.
 Summary

• The ERM uses ERDs to represent the conceptual database as viewed by

the end user
• Connectivity describes the relationship classification (1:1, 1:M, or M:N)
• In the ERM, an M:N relationship is valid at the conceptual level
• ERDs may be based on many different ERMs
• Unified Modeling Language (UML) class diagrams are used to represent
the static data structures in a data model
• Database designers, no matter how well they can produce designs that
conform to all applicable modeling conventions, are often forced to make
design compromises

40
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-p
rotected website for classroom use.