David M. Kroenke and David J.

Auer
Database Processing:
Fundamentals, Design, and Implementation

Chapter Five:
Data Modeling with the
Entity-Relationship Model
 Chapter Objectives
• Tunderstand the two-phase data modeling/database
design process
• Tunderstand the purpose of the data modeling process
• Tunderstand entity-relationship (E-R) diagrams
• Tbe able tdetermine entities, attributes, and relationships
• Tbe able tcreate entity identifiers
• Tbe able tdetermine minimum and maximum
cardinalities

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-2

© 2016 Pearson Education, Ltd.
 Chapter Objectives
• Tunderstand variations of the E-R model
• Tunderstand and be able tuse ID-dependent and other
weak entities
• Tunderstand and be able tuse supertype/subtype entities
• Tunderstand and be able tuse strong entity patterns
• Tunderstand and be able tuse the ID-dependent
association pattern
• Tunderstand and be able tuse the ID-dependent
multivalued attribute pattern

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-3

© 2016 Pearson Education, Ltd.
 The Data Model
• A data model is a plan or blueprint for a
database design.
• A data model is more generalized and
abstract than a database design.
• It is easier tchange a data model than it is
tchange a database design, sit is the
appropriate place twork through
conceptual database problems.

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-4

© 2016 Pearson Education, Ltd.
KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-5
© 2016 Pearson Education, Ltd.
 Entity-Relationship (E-R) Model
• The Entity-Relationship (E-R) model is a set of
concepts and graphical symbols that can be used tcreate
conceptual schemas.
• Versions:
– Original E-R model—by Peter Chen (1976)
– Extended E-R model—extensions tthe Chen model
– Information Engineering (IE)—by James Martin (1990); uses
“crow’s foot” notation, is easier tunderstand, and we will use it
– IDEF1X—a national standard developed by the National Institute
of Standards and Technology [see Appendix C]
– Unified Modeling Language (UML)—by the Object
Management Group; it supports object-oriented methodology
[see Appendix D]

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-6

© 2016 Pearson Education, Ltd.
 Entities
• Something that can be identified and the
users want ttrack:
– Entity class—a collection of entities of a
given type
– Entity instance—the occurrence of a
particular entity
• There are usually many instances of an
entity in an entity class.

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-7

© 2016 Pearson Education, Ltd.
 CUSTOMER:
The Entity Class and TwEntity Instances

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-8

© 2016 Pearson Education, Ltd.
 Attributes
• Attributes describe an entity’s
characteristics.
• All entity instances of a given entity class
have the same attributes but vary in the
values of those attributes.
• Originally shown in data models as
ellipses.
• Data modeling products today commonly
show attributes in rectangular form.
KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-9
© 2016 Pearson Education, Ltd.
 EMPLOYEE:
Attributes in Ellipses

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-10

© 2016 Pearson Education, Ltd.
 EMPLOYEE:
Attributes in Entity Rectangle

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-11

© 2016 Pearson Education, Ltd.
 Identifiers
• Identifiers are attributes that name, or identify, entity
instances.
• The identifier of an entity instance consists of one or
more of the entity’s attributes.
• Composite identifiers are identifiers that consist of twor
more attributes.
• Identifiers in data models become keys in database
designs.
– Entities have identifiers.
– Tables (or relations) have keys.

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-12

© 2016 Pearson Education, Ltd.
 Entity Attribute Display
in Data Models

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-13

© 2016 Pearson Education, Ltd.
 Relationships
• Entities can be associated with one another in
relationships:
– Relationship classes: associations among entity classes
– Relationship instances: associations among entity instances
• In the original E-R model, relationships could
have attributes, but today this is nlonger done.
• A relationship class can involve twor more entity
classes.

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-14

© 2016 Pearson Education, Ltd.
 Degree of the Relationship
• The degree of the relationship is the number of
entity classes in the relationship:
– Twentities have a binary relationship of degree two.
– Three entities have a ternary relationship of degree
three.

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-15

© 2016 Pearson Education, Ltd.
 Binary Relationship

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-16

© 2016 Pearson Education, Ltd.
 Ternary Relationship

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-17

© 2016 Pearson Education, Ltd.
 Entities and Tables
• The principle difference between an entity
and a table (relation) is that you can
express a relationship between entities
without using foreign keys.
• This makes it easier twork with entities in
the early design process where the very
existence of entities and the relationships
between them is uncertain.

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-18

© 2016 Pearson Education, Ltd.
 Cardinality
• Cardinality means “count,” and is
expressed as a number.
• Maximum cardinality is the maximum
number of entity instances that can
participate in a relationship.
• Minimum cardinality is the minimum
number of entity instances that must
participate in a relationship.

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-19

© 2016 Pearson Education, Ltd.
 Maximum Cardinality
• Maximum cardinality is the maximum
number of entity instances that can
participate in a relationship.
• There are three types of maximum
cardinality:
– One-to-One [1:1]
– One-to-Many [1:N]
– Many-to-Many [N:M]

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-20

© 2016 Pearson Education, Ltd.
 The Three Types of
Maximum Cardinality

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-21

© 2016 Pearson Education, Ltd.
 Parent and Child Entities
• In a one-to-many relationship:
– The entity on the one side of the relationship is called
the parent entity or just the parent.
– The entity on the many side of the relationship is
called the child entity or just the child.
• In the figure below, EMPLOYEE is the parent
and COMPUTER is the child:

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-22

© 2016 Pearson Education, Ltd.
 HAS-A Relationships
• The relationships we have been
discussing are known as HAS-A
relationships:
– Each entity instance has a relationship with
another entity instance.
• An EMPLOYEE has one or more COMPUTERs.
• A COMPUTER has one assigned EMPLOYEE.

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-23

© 2016 Pearson Education, Ltd.
 Minimum Cardinality
• Minimum cardinality is the minimum number of
entity instances that must participate in a
relationship.
• Minimums are generally stated as either zeror
one:
– IF zer[0] THEN participation in the relationship by the
entity is optional, and nentity instance must
participate in the relationship.
– IF one [1] THEN participation in the relationship by
the entity is mandatory, and at least one entity
instance must participate in the relationship.

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-24

© 2016 Pearson Education, Ltd.
 Indicating Minimum Cardinality
• As shown in the examples in a following
slide:
– Minimum cardinality of zer[0] indicating
optional participation is indicated by placing
an oval next tthe optional entity.
– Minimum cardinality of one [1] indicating
mandatory (required) participation is
indicated by placing a vertical hash mark
next tthe required entity.

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-25

© 2016 Pearson Education, Ltd.
 Reading Minimum Cardinality
• Look toward the entity in question:
– IF you see an oval THEN that entity is
optional (minimum cardinality of zer[0]).
– IF you see a vertical hash mark THEN that
entity is mandatory (required) (minimum
cardinality of one [ 1]).

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-26

© 2016 Pearson Education, Ltd.
 The Three Types of
Minimum Cardinality

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-27

© 2016 Pearson Education, Ltd.
 Recall: Versions
• Original E-R model—by Peter Chen (1976)
• Extended E-R model—extensions tthe Chen model
• Information Engineering (IE)—by James Martin (1990);
uses “crow’s foot” notation, is easier tunderstand, and
we will use it
• IDEF1X—a national standard developed by the National
Institute of Standards and Technology [see Appendix C]
• Unified Modeling Language (UML)—by the Object
Management Group; it supports object-oriented
methodology [see Appendix D]
KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-28
© 2016 Pearson Education, Ltd.
 Data Modeling Notation:
IE Crow’s Foot

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-29

© 2016 Pearson Education, Ltd.
 Data Modeling Notation:
IE Crow’s Foot 1:N

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-30

© 2016 Pearson Education, Ltd.
 Data Modeling Notation:
IE Crow’s Foot N:M

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-31

© 2016 Pearson Education, Ltd.
 ID-Dependent Entities
• An ID-dependent entity is an entity (child)
whose identifier includes the identifier of another
entity (parent).
• The ID-dependent entity is a logical extension or
subunit of the parent:
– BUILDING : APARTMENT
– PAINTING : PRINT
• The minimum cardinality from the ID-dependent
entity tthe parent is always one.

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-32

© 2016 Pearson Education, Ltd.
 ID-Dependent Entities

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-33

© 2016 Pearson Education, Ltd.
 Weak Entities
• A weak entity is an entity whose
existence logically depends upon another
entity.
• All ID-Dependent entities are weak.
• There are alsnon-ID-dependent weak
entities.
– The identifier of the parent does not appear in
the identifier of the weak child entity.

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-34

© 2016 Pearson Education, Ltd.
 Weak Entities

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-35

© 2016 Pearson Education, Ltd.
 Identifying Relationships
• The solid line connecting an ID-
dependent entity and its parent is called
an identifying relationship.
• The dashed line connecting strong
entities or a non-ID-dependent weak entity
tits parent is called a non-identifying
relationship.

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-36

© 2016 Pearson Education, Ltd.
 ID-Dependent and Weak Entities

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-37

© 2016 Pearson Education, Ltd.
 IE Crow’s Foot Symbol Summary I

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-38

© 2016 Pearson Education, Ltd.
 Strong Entity Patterns:
1:1 Strong Entity Relationships II

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-39

© 2016 Pearson Education, Ltd.
 Strong Entity Patterns:
1:N Strong Entity Relationships III

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-40

© 2016 Pearson Education, Ltd.
 Strong Entity Patterns:
N:M Strong Entity Relationships III

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-41

© 2016 Pearson Education, Ltd.
 ID-Dependent Relationships:
The Association Pattern II

PartNumber and
CompanyName are the
identifiers of the parent
entities, and are needed
because QUOTATION is ID-
dependent on both PART
and COMPANY

An entity that holds one or

more additional attributes
beyond the identifiers of the
parent entities is called an
associative entity (or
association entity)─in this
case Price is the additional
attribute

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-42

© 2016 Pearson Education, Ltd.
 ID-Dependent Relationships:
The Multivalued Attribute Pattern II

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-43

© 2016 Pearson Education, Ltd.
 ID-Dependent Relationships:
The Multivalued Attribute Pattern IV

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-44

© 2016 Pearson Education, Ltd.
 ID-Dependent Relationships:
Composite Multivalued Attributes II

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-45

© 2016 Pearson Education, Ltd.
 Highline University
Creating a Data Model
• Suppose the administration at a hypothetical university
named Highline University wants tcreate a database
ttrack colleges, departments, faculty, and students.
• Tdthis, a data modeling team has collected a series of
reports as part of its requirements determination.

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-46

© 2016 Pearson Education, Ltd.
 Highline University
The College Report

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-47

© 2016 Pearson Education, Ltd.
 Highline University
First Data Model

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-48

© 2016 Pearson Education, Ltd.
 Highline University
The Department Report

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-49

© 2016 Pearson Education, Ltd.
 Highline University
Second Data Model I

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-50

© 2016 Pearson Education, Ltd.
 Highline University
Second Data Model II

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-51

© 2016 Pearson Education, Ltd.
 Highline University
Second Data Model III

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-52

© 2016 Pearson Education, Ltd.
 Highline University
Second Data Model IV

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-53

© 2016 Pearson Education, Ltd.
 Highline University
The Department Student Report

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-54

© 2016 Pearson Education, Ltd.
 David Kroenke and David Auer
Database Processing
Fundamentals, Design, and Implementation
(14th Edition, Global Edition)

End of Presentation:
Chapter Five

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-55

© 2016 Pearson Education, Ltd.
 Strong & Weak Entities
• Strong entity is an entity that represents
something that can exist on its own.

• a weak entity is an entity whose existence

depends on the presence of another entity
(must logically depend on another entity).

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 6-56

© 2016 Pearson Education, Ltd.
 ID-dependent
• ID-dependent entity is an entity
whose identifier includes the
identifier of another entity. ID-
dependent entity cannot exist
unless the parent also exists.

• All ID-dependent entities are weak

entities.

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 6-57

© 2016 Pearson Education, Ltd.
 ID-dependent Restrictions
• ID-dependent entities pose restrictions
on the processing of the database that is
constructed from them.
• The row that represents the parent entity
must be created before any ID-dependent
child row can be created.
• Further, when a parent row is deleted, all
child rows must be deleted as well

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 6-58

© 2016 Pearson Education, Ltd.
 Representation
• use rounded corners to represent the ID-
dependent entity.
• use a solid line to represent the
relationship between the ID-dependent
entity and its parent.
• dashed line is used between strong
entities and is called a non-identifying
relationship.

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 6-59

© 2016 Pearson Education, Ltd.
KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 6-60
© 2016 Pearson Education, Ltd.
 non-ID-dependent
• Some weak entities are not ID-dependent.
• Most data modeling tools cannot model
non-ID-dependent entities.
• So, to indicate such situations, we will use
a non-identifying relationship with a note
added to the data model indicating that the
entity is weak

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 6-61

© 2016 Pearson Education, Ltd.
KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 6-62
© 2016 Pearson Education, Ltd.
 SAMPLE QUESTION 1

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-63

© 2016 Pearson Education, Ltd.
UPS prides itself on having up-to-date information on the processing
and current location of each shipped item. Tdthis, UPS relies on a
company-wide information system. Shipped items are the heart of the
UPS product tracking information system. Shipped items can be
characterized by item number (unique), weight, dimensions, insurance
amount, destination, and final delivery date. Shipped items are
received intthe UPS system at a single retail center. Retail centers are
characterized by their type, uniqueID, and address. Shipped items
make their way ttheir destination via one or more standard UPS
transportation events (i.e., flights, truck deliveries). These
transportation events are characterized by a unique scheduleNumber,
a type (e.g, flight, truck), and a deliveryRoute.
Please create an Entity Relationship diagram that captures this
information about the UPS system. Be certain tindicate identifiers and
cardinality constraints.

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-64

© 2016 Pearson Education, Ltd.
 SAMPLE QUESTION 2

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-65

© 2016 Pearson Education, Ltd.
Production tracking is important in many manufacturing environments
(e.g., the pharmaceuticals industry, children’s toys, etc.). The following
ER diagram captures important information in the tracking of
production. Specifically, the ER diagram captures relationships
between production lots (or batches), individual production units, and
raw materials.
convert the ER diagram
inta relational database
schema. Be certain
tindicate primary keys and
referential integrity
constraints.

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-66

© 2016 Pearson Education, Ltd.
• Please identify an attribute in the above ER diagram that might
represent a composite attribute, and explain why/how it might
represent a composite attribute
• identify an attribute in the ER diagram that could represent a
derived attribute and explain why/how it might represent a derived
attribute
• The ER diagram/relational database schema contains several
instances of data redundancy. identify one instance where a data
redundancy issue exists.
• The current ER diagram has the following relationship, “raw
materials are used in 0 tmany lots.” Please explain, in the context of
the manufacturing environment, how the meaning changed if the
minimal cardinality is changed t“1” (i.e., the relationship becomes
"raw materials are used in 1 tmany lots.”)

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-67

© 2016 Pearson Education, Ltd.
 SAMPLE QUESTION 3

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-68

© 2016 Pearson Education, Ltd.
Assume we have the following application that models soccer teams,
the games they play, and the players in each team. In the design, we
want to capture the following:
• We have a set of teams, each team has an ID (unique identifier),
name, main stadium, and to which city this team belongs.
• Each team has many players, and each player belongs tone team.
Each player has a number (unique identifier), name, DoB, start year,
and shirt number that he uses.
• Teams play matches, in each match there is a host team and a
guest team. The match takes place in the stadium of the host team.

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-69

© 2016 Pearson Education, Ltd.
• For each match we need to keep track of the following:
– The date on which the game is played
– The final result of the match
– The players participated in the match. For each player, how many goals
he scored, whether or not he took yellow card, and whether or not he
took red card.
– During the match, one player may substitute another player. We want to
capture this substitution and the time at which it took place.
• Each match has exactly three referees. For each referee we have
an ID (unique identifier), name, DoB, years of experience. One
referee is the main referee and the other to ware assistant referee.
Design an ER diagram to capture the above requirements. State
any assumptions you have that affects your design (use the back
of the page if needed). Make sure cardinalities and primary keys
are clear.
KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-70
© 2016 Pearson Education, Ltd.
 Put in 3NF?
SKU_DATA (SKU, SKU_Description, Department, Dept_Budget, Buyer)

SKU --> (SKU_Description, Department, Dept_Budget, Buyer) 

SKU_Description --> (SKU, Department, Dept_Budget, Buyer)
Buyer --> (Department, Dept_Budget)
Department --> Dept_Budget
SKU SKU_Description Department Dept_Budget Buyer
1 fdggf ddd 5566 xgfh
2 Dgfadf gfdgd dfffff 545 ssdd

1NF, 2NF if and only if it is in 1NF and all non-key attributes are determined by
the primary key
3NF:

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE

© 2016 Pearson Education, Ltd.
SKU_DATA (SKU, SKU_Description, Department, Dept_Budget, Buyer)
SKU --> (SKU_Description, Department, Dept_Budget, Buyer)
SKU_Description --> (SKU, Department, Dept_Budget, Buyer)
Buyer --> (Department, Dept_Budget)
Department --> Dept_Budget

SKU_DATA2 (SKU, SKU_Description, Buyer)

Buyer (Buyer, Department)
Department (Department, Dept_Budget)

SKU SKU_Description Buyer Buyer Department

1 fdggf xgfh xgfh
2 Dgfadf gfdgd ssdd ssdd

Department Dept_Budget
xgfh
ssdd

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE

© 2016 Pearson Education, Ltd.
 Put in 3NF?
SKU SKU_Description Department Dept_Budget Buyer
1 Fdggf kkjkj Sales 25 Salem
2 Dgfadf gfdgd XYZ 5 Ali
3 Dsffd fdsfdsfd Sales 25 Hasan

SKU SKU_Description Buyer Buyer Department

1 fdggf xgfh xgfh
2 Dgfadf gfdgd ssdd ssdd

Department Dept_Budget
Sales 25
XYZ 5

KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE

© 2016 Pearson Education, Ltd.
SELECT sku, buyer, department, AVG()
FROM sku_data2, Buyer Buyer Department
ssdd
Xgfh

WHERE sku_data2.Buyer = [Link]

OREDER BY department DESC
GROUP BY sku, buyer, department ;
SKU SKU_Description Buyer
1 fdggf xgfh
2 Dgfadf gfdgd ssdd
KROENKE AND AUER - DATABASE PROCESSING, 14/e, GE 5-74
© 2016 Pearson Education, Ltd.