The Entity-Relationship Model
(ER Model) - Part 1
By Michael Hahsler
Based on slides for CS145 Introduction to Databases (Stanford)
Lecture 4
Introduction to Database Design
2
Lecture 4 > Section 1
3
Database Design
• Database design: Why do we need it?
• Agree on structure of the database before deciding on a particular
implementation

• Consider issues such as:
• What entities to model
• How entities are related
• What constraints exist in the domain
• How to achieve good designs

• Several formalisms exist
• We discuss one flavor of ER diagrams
Lecture 4 > Section 1 > Motivation
Database Design Process
1. Requirements analysis

• What is going to be stored?

• How is it going to be used?

• What are we going to do with the data?

• Who should access the data?




4
Lecture 4 > Section 1 > Motivation
1. Requirements Analysis 2. Conceptual Design 3. Logical, Physical, Security, etc.
2. Conceptual Design

• A high-level description of the database

• Sufficiently precise that technical people can understand it

• But, not so precise that non-technical people cannot
participate




5
Lecture 4 > Section 1 > Motivation
Database Design Process
1. Requirements Analysis 2. Conceptual Design 3. Logical, Physical, Security, etc.
6
Lecture 4 > Section 1 > Motivation
Database Design Process
1. Requirements Analysis 2. Conceptual Design 3. Logical, Physical, Security, etc.
3. Implementation:

• Logical Database Design

• Physical Database Design

• Security Design




7
ER is a visual syntax for DB design which is precise enough for
technical points, but abstracted enough for non-technical people.
Lecture 4 > Section 1 > Motivation
Database Design Process
1. Requirements Analysis 2. Conceptual Design 3. Logical, Physical, Security, etc.
Makes Product
name category
price
Company
name
ER Model & Diagrams
Impact of the ER model
• The ER model is one of the most cited articles in Computer
Science
• “The Entity-Relationship model – toward a unified view of data”
Peter Chen, 1976

• Used by companies big and small


8
Lecture 4 > Section 1 > Motivation
1. ER Basics: Entities & Relations
9
Lecture 4 > Section 1
Entities and Entity Sets
• Entities & entity types are the primitive
units of the ER model

• Entities are the individual objects (instances),
which are members of entity types
• Entity type are the classes or types of objects
in our model
• Example: Person is an entity type while
Michael is an entity.
• We use entity types in ER models

10
Product
Lecture 4 > Section 1 > Entities
Person
Entities and Entity Types
• An entity type has attributes
represented by ovals attached to an
entity type

11
Product
name category
price
Shapes are
important. Colors
used here are not.
Lecture 4 > Section 1 > Entities
Entities vs. Entity Sets
Example:
12
Product
name category
price
Lecture 4 > Section 1 > Entities
Entity type
Product
Name: Xbox
Category: Gamming
Console
Price: $250
Name: My Little Pony Doll
Category: Toy
Price: $25
Entity
Entity
Attribute
Entities are not explicitly
represented in ER diagrams!
Keys
A key is a minimal set of attributes that uniquely identifies an entity.
13
Product
name category
price
Denote elements of
the primary key by
underlining.
Here, {name, category} is not a
key (it is not minimal).

If it were, what would it mean?
Lecture 4 > Section 1 > Entities
The ER model forces us to designate a single primary key, though
there may be multiple candidate keys. Often, we introduce an artificial
key attribute (also called a synthetic or surrogate key).
Entity Types Define Relations
14
Product
name category
price
Lecture 4 > Section 1 > Entities
name category price
Gizmo Electronics $9.99
GizmoLite Electronics $7.50
Gadget Toys $5.50
Product
The R in ER: Relationships
• A relationship type is between two entity types
15
Product
name category
price
Company
name
Makes
Lecture 4 > Section 1 > Relationships
How to read a relationship in both directions:
1. A product is made by a company
2. A company makes a product
16


makes
buys
Product
name category
price
Person
address name ssn
Company
stockprice
name
Lecture 4 > Section 1 > Relationships
17
What is a Relationship?
• A mathematical definition (called “Relation” in Math):

• Let A, B be sets
• A={1,2,3}, B={a,b,c,d}

Lecture 4 > Section 1 > Relationships
1
2
3
a
b
c
d
A=
B=
18
What is a Relationship?
• A mathematical definition:

• Let A, B be sets
• A={1,2,3}, B={a,b,c,d}

• A x B (the cross-product) is the set of all pairs
(a,b)
• A × B = {(1,a), (1,b), (1,c), (1,d), (2,a), (2,b), (2,c),
(2,d), (3,a), (3,b), (3,c), (3,d)}

1
2
3
a
b
c
d
A=
B=
Lecture 4 > Section 1 > Relationships
19
What is a Relationship?
• A mathematical definition:

• Let A, B be sets
• A={1,2,3}, B={a,b,c,d}

• A x B (the cross-product) is the set of all pairs
(a,b)
• A × B = {(1,a), (1,b), (1,c), (1,d), (2,a), (2,b), (2,c),
(2,d), (3,a), (3,b), (3,c), (3,d)}


1
2
3
a
b
c
d
A=
B=
Lecture 4 > Section 1 > Relationships
• We define a relationship (relation) to be a subset of A x B
R = {(1,a), (2,c), (2,d), (3,b)}
20
What is a Relationship?
• A mathematical definition:
• Let A, B be sets
• A x B (the cross-product) is the set of all pairs
• A relationship (relation) is a subset of A x B

• Example: Makes is a relationship. It is a
subset of Product × Company:
makes Company
Product
Lecture 4 > Section 1 > Relationships
1
2
3
a
b
c
d
A=
B=
What is a Relationship?
21
Lecture 4 > Section 1 > Relationships
name category price
Gizmo Electronics $9.99
GizmoLite Electronics $7.50
Gadget Toys $5.50
name
GizmoWorks
GadgetCorp
Product Company
Makes Product
name category
price
Company
name
A relationship between entity sets P and
C is a subset of all possible pairs of
entities in P and C, with tuples uniquely
identified by P and C’s keys
What is a Relationship?
22
Lecture 4 > Section 1 > Relationships
name category price
Gizmo Electronics $9.99
GizmoLite Electronics $7.50
Gadget Toys $5.50
name
GizmoWorks
GadgetCorp
Product Company
C.name P.name P.category P.price
GizmoWorks Gizmo Electronics $9.99
GizmoWorks GizmoLite Electronics $7.50
GizmoWorks Gadget Toys $5.50
GadgetCorp Gizmo Electronics $9.99
GadgetCorp GizmoLite Electronics $7.50
GadgetCorp Gadget Toys $5.50
Company C × Product P
Makes Product
name category
price
Company
name
A relationship between entity sets P and
C is a subset of all possible pairs of
entities in P and C, with tuples uniquely
identified by P and C’s keys
What is a Relationship?
23
Lecture 4 > Section 1 > Relationships
name category price
Gizmo Electronics $9.99
GizmoLite Electronics $7.50
Gadget Toys $5.50
name
GizmoWorks
GadgetCorp
Product Company
C.name P.name P.category P.price
GizmoWorks Gizmo Electronics $9.99
GizmoWorks GizmoLite Electronics $7.50
GizmoWorks Gadget Toys $5.50
GadgetCorp Gizmo Electronics $9.99
GadgetCorp GizmoLite Electronics $7.50
GadgetCorp Gadget Toys $5.50
Company C × Product P
C.name P.name
GizmoWorks Gizmo
GizmoWorks GizmoLite
GadgetCorp Gadget
Makes Makes Product
name category
price
Company
name
A relationship between entity sets P and
C is a subset of all possible pairs of
entities in P and C, with tuples uniquely
identified by P and C’s keys
What is a Relationship?
• There can only be one relationship for every
unique combination of entities

• This also means that the relationship is
uniquely determined by the keys of its entities

• Example: the key for Makes (to right) is
{Product.name, Company.name}

24
Lecture 4 > Section 1 > Relationships
This follows from our
mathematical definition of a
relationship (it is a set)
Makes Product
name category
price
Company
name
KeyMakes = KeyProduct ∪ KeyCompany
25
Product
name category
price
Company
name
Makes
since
Relationships may have attributes as well.
For example: “since”
records when company
started making a product
Note: For each product/company pair
there is automatically only a single
since value since there can only be
one unique product/company pair in
makes.
Lecture 4 > Section 1 > Relationships
Relationships and Attributes
Decision: Relationship vs. Entity?
Q: What does this say?






A: A person can only buy a specific product once per day (date)
26
Lecture 4 > Section 1 > Relationships
Purchased Product
name category
price
Person
name
date
Modeling something as a relationship makes it unique.
What if this is not appropriate?
Decision: Relationship vs. Entity?
What about this way?





Now we can have multiple purchases per product, person pair!



27
Product
name category
price
Person
name
date
Purchase
quantity PID#
ProductOf BuyerOf
We can always use a new entity instead of a relationship.
For example, to permit multiple instances of each entity combination!
Lecture 4 > Section 1 > Relationships
Note on Relationships vs. Relation
28
Lecture 4 > Section 1 > Entities
name category price
Gizmo Electronics $9.99
GizmoLite Electronics $7.50
Gadget Toys $5.50
Product
Makes Product
name category
price
Company
name
Relationship/Relationship type Relation
ER Model: How do Entity types relate to each other Math: A Relation
Relational Algebra: A table with data (a set)
Relations are used to implement entity types and certain relationship types!
How to Create an ER Diagram
“Rules of thumb" for mapping natural language descriptions
into ER diagrams:
29
Lecture 4 > Section 2
English grammar structure ER structure

Common noun Entity type
Proper noun Entity
Verb Relationship type
Adjective Attribute for entity
Adverb Attribute for relationship
Example: How to Create an ER Diagram
Here is what the person in charge said:

“Our company is called PowerSeller and we sell health
products on Ebay. Our products are made by different
manufacturers. Products belong to different product
categories (e.g., supplements, cosmetics, etc.) and each
product is sold at a fixed price. We use customer IDs for our
customers, and we know for all of them the shipping address
and name, and for most we also know a phone number.”
30
Lecture 4 > Section 2
Example: How to Create an ER Diagram
Here is what the person in charge said:

“Our company is called PowerSeller and we sell health
products on Ebay. Our products are made by different
manufacturers. Products belong to different product
categories (e.g., supplements, cosmetics, etc.) and each
product is sold at a fixed price. We use customer IDs for our
customers, and we know for all of them the shipping address
and name, and for most we also know a phone number.”
31
Lecture 4 > Section 2
Noun
Verb
Example: How to Create an ER Diagram
Identify entity types and attributes (nouns):
• company, PowerSeller
• health product, product, product category, price, manufacturer
• customerID , customer, shipping address, name, phone number

Identify relationship types (verbs):
• know
• belongs to
• sell
• make
32
Lecture 4 > Section 2
Example: How to Create an ER Diagram
Identify Entity types (bold) and attributes:
• company, PowerSeller
• health product, product, product category, price, manufacturer
• customerID , customer, shipping address, name, phone number

Identify relationship types:
• know
• belongs to
• sell, buy
• make
33
Lecture 4 > Section 2
Make its own
entity type
34


make
buy
Product
name category
price
Customer
address name ID
Manufacturer
name
Lecture 4 > Section 1 > Relationships
phone
Draw an ER diagram for football
35
Lecture 4 > Section 1 > ACTIVITY
Teams play
each other in
Games. Each
pair of teams
can play each
other multiple
times
Players belong
to Teams
(assume no
trades /
changes)
A Play will contain
either a Pass from
one player to
another, or a Run
by one player
A Game is made
up of Plays that
result in a
yardage
gain/loss, and
potentially a
touchdown