Entity Relationship Diagram

Lecture
Lecture 4: The E/R Model

Lecture 4
Today’s Lecture
1. E/R Basics: Entities & Relations

• ACTIVITY: Crayon time!
2. E/R Design considerations

• ACTIVITY: Crayon time pt. II
3. Advanced E/R Concepts

• ACTIVITY: E/R Translation
2
Lecture 4 > Section 1
1. E/R Basics: Entities & Relations
3
What you will learn about in this section

1. High-level motivation for the E/R model
2. Entities
3. Relations
4. ACTIVITY: Crayon Time! Drawing E/R diagrams
4
Lecture 4 > Section 1 > Motivation
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: This the first project

• 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 E/R diagrams
5
Database Design Process

1. Requirements Analysis 2. Conceptual Design 3. Logical, Physical, Security, etc.
1. Requirements analysis Technical and non-

technical people are
• What is going to be stored? involved
• How is it going to be used?
• What are we going to do with the data?
• Who should access the data?

6

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 can’t participate
This is where E/R fits in. 7


3. More:
• Logical Database Design
• Physical Database Design
• Security Design
8

This process is
E/R Model & Diagrams used
iterated many
name times
name category
price
Product Makes Company
E/R is a visual syntax for DB design which is precise enough for

technical points, but abstracted enough for non-technical people
9
Interlude: Impact of the ER model

• The E/R 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

• You’ll know it soon enough
10
Lecture 4 > Section 1 > Entities
Entities and Entity Sets
• Entities & entity sets are the primitive unit of

the E/R model
• Entities are the individual objects, which are members

of entity sets
• Ex: A specific person or product
Product
• Entity sets are the classes or types of objects in our Person
model
• Ex: Person, Product
• These are what is shown in E/R diagrams - as rectangles These represent entity sets
• Entity sets represent the sets of all possible entities
11
Entities and Entity Sets

• An entity set has attributes
• Represented by ovals attached to an entity
set
Shapes are important.
Colors are not.
name category
price
Product
12
Entities vs. Entity Sets

Entities are not explicitly
Example:
represented in E/R diagrams!
Entity
name category
Name: Xbox
price
Category: Total Name: My Little Pony Doll Entity
Product
Multimedia System
Price: $250
Category: Toy
Price: $25
Attribute
Product
Entity Set
13
Keys
• A key is a minimal set of attributes that uniquely identifies an entity.
Denote elements of the Here, {name, category} is not a key (it

primary key by underlining. is not minimal).
name category If it were, what would it mean?

price
Product
The E/R model forces us to designate a single primary key, though there
may be multiple candidate keys
14
Lecture 4 > Section 1 > Relationships
The R in E/R: Relationships

• A relationship is between two entities
name name
category
price
15
name category
name
price
makes Company
Product
stockprice
buys
employs
Person
address name ssn

16
What is a Relationship?
• A mathematical definition:
B=
• Let A, B be sets A= 1 a
• A={1,2,3}, B={a,b,c,d} b
2
c
3
d
17
B=
• A={1,2,3}, B={a,b,c,d} b
2
• A x B (the cross-product) is the set of all pairs c

3
(a,b) d
• 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)}
18
B=
• A={1,2,3}, B={a,b,c,d}, b
2
• A x B (the cross-product) is the set of all pairs (a,b) c

3
• A ´ B = {(1,a), (1,b), (1,c), (1,d), (2,a), (2,b), (2,c), (2,d), d
(3,a), (3,b), (3,c), (3,d)}
• We define a relationship to be a subset of A x B

• R = {(1,a), (2,c), (2,d), (3,b)}
19
• Let A, B be sets B=
• A x B (the cross-product) is the set of all pairs A= 1 a
• A relationship is a subset of A x B 2 b
c
• Makes is relationship- it is a subset of 3
Product ´ Company: d
makes Company
Product
20
name name
category
price
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
21
Company Product
name name category price
GizmoWorks Gizmo Electronics $9.99
GadgetCorp GizmoLite Electronics $7.50
Gadget Toys $5.50
name category name

price

22
Product Company C × Product P
Company
name name category price C.name P.name P.category P.price
GizmoWorks Gizmo Electronics $9.99 GizmoWorks Gizmo Electronics $9.99
GadgetCorp GizmoLite Electronics $7.50 GizmoWorks GizmoLite Electronics $7.50

Gadget Toys $5.50 GizmoWorks Gadget Toys $5.50
GadgetCorp Gizmo Electronics $9.99
GadgetCorp Gadget Toys $5.50
name category name
price

23
Product Company C × Product P
Company
name name category price C.name P.name P.category P.price
GizmoWorks Gizmo Electronics $9.99 GizmoWorks Gizmo Electronics $9.99
GadgetCorp GizmoLite Electronics $7.50 GizmoWorks GizmoLite Electronics $7.50

Gadget Toys $5.50 GizmoWorks Gadget Toys $5.50
GadgetCorp Gizmo Electronics $9.99
GadgetCorp Gadget Toys $5.50
name category name
price
Makes
C.name P.name
A relationship between entity sets P and C is a GizmoWorks Gizmo
subset of all possible pairs of entities in P and C, GizmoWorks GizmoLite
with tuples uniquely identified by P and C’s keys GadgetCorp Gadget
24
• There can only be one relationship for every This follows from our
unique combination of entities mathematical
definition of a
relationship- it’s a SET!
• This also means that the relationship is uniquely

determined by the keys of its entities
KeyMakes = KeyProduct ∪ KeyCompany
• Example: the “key” for Makes (to right) is since

{Product.name, Company.name}
name
name category
price
Why does this make sense?

25
Relationships and Attributes

• Relationships may have attributes as well.
since
name
name category
price
For example: “since” Note: “since” is Note #2: Why not

implicitly unique per “how long”?
records when company
pair here! Why?
started making a product
26
Decision: Relationship vs. Entity?

• Q: What does this say?
date
name category name
price
Product Purchased Person
• A: A person can only buy a specific product once (on one date)
Modeling something as a relationship makes it unique; what if

not appropriate?
27
Decision: Relationship vs. Entity?
• What about this way?

date PID# quantity
name
name category Purchase
price
ProductOf BuyerOf
Product Person
• Now we can have multiple purchases per product, person pair!
We can always use a new entity instead of a relationship. For example, to

permit multiple instances of each entity combination!
28
Lecture 4 > Section 1 > ACTIVITY
ACTIVITY: E/R Diagrams Pt. I
29
Draw an E/R diagram for football

Use the following simplified model of a football season
(concepts to include are underlined):
Teams play each Players belong to A Game is made up A Play will contain
other in Games. Teams (assume of Plays that result either a Pass from
Each pair of teams no trades / in a yardage one player to
can play each other changes). gain/loss, and another, or a Run by
multiple times potentially a one player
touchdown
30
2. E/R Design Considerations
31

1. Relationships cont’d: multiplicity, multi-way
2. Design considerations
3. Conversion to SQL
4. ACTIVITY: Crayon Time! Drawing E/R diagrams Pt. II
32
Lecture 4 > Section 2 > Relationships- Multiplicity & Multi-way
Multiplicity of E/R Relationships

1 a
One-to-one: 2
3
b
c
d Indicated using
arrows
1 a
Many-to-one: 2
3
b
c
d
X -> Y means
1 a there exists a
One-to-many: 2
3
b
c
function mapping
d from X to Y (recall
the definition of a
1 a function)
Many-to-many: 2
3
b
c
d
33
name category
name
price
makes Company
Product
stockprice
What does
buys this say?
employs
Person
address name ssn

34
Multi-way Relationships
How do we model a purchase relationship between buyers,
products and stores?
Product
Purchase Store
Person
35
Arrows in Multiway Relationships

Q: What does the arrow mean ?
Product
Purchase Store
Person
36

Q: What does the arrow mean ?
Product
Purchase Store
Person
37

Q: How do we say that every person shops in at most one store ?
Product
Purchase Store
Person
A: Cannot. This is the best approximation.

(Why only approximation ?) 38
Converting Multi-way Relationships

to Binary
date From what we
ProductOf Product had on
previous slide
to this - what
did we do?
Purchase
StoreOf Store
BuyerOf Person
39
Converting Multi-way Relationships

to New Entity + Binary Relationships
Side note:
What arrows
should be ProductOf Product
added here? date
Are these
correct?
Purchase StoreOf Store
BuyerOf Person
40
Lecture 4 > Section 2 > Design decisions
Decision: Multi-way or New Entity + Binary?

Multi-way Relationship Entity + Binary
ProductOf Product
Product
date
Purchase Store
BuyerOf Person
Person
Should we use a single multi-way relationship or a new entity with

binary relations?
41

(A) Multi-way Relationship (B) Entity + Binary
ProductOf Product
Product
date
Purchase Store
BuyerOf Person
Person
Multiple purchases per
(product, store, person)
combo possible here!
• Covered earlier: (B) is useful if we want to have multiple instances of the

“relationship” per entity combination
42

ProductOf Product
Product
date
Purchase Store
BuyerOf Person
Person
We can add more-fine-
grained constraints here!
• (B) is also useful when we want to add details (constraints or attributes) to the
relationship
- “A person who shops in only one store”
- “How long a person has been shopping at a store”
43

ProductOf Product
Product
date
Purchase Store
BuyerOf Person
Person
• (A) is useful when a relationship really is between multiple entities

- Ex: A three-party legal contract
44
3. Design Principles
What’s wrong with these examples?
Product Purchase Person
Country President Person
45
Design Principles:
What’s Wrong?
date
Product
Purchase Store
personAddr
personName
46
Design Principles:
What’s Wrong?
Dates date
Product
Purchase Store
Person
47
Examples: Entity vs. Attribute

Should address (A) Or (B) be an entity?
be an attribute?
Street Addr ZIP

Addr 1 Addr 2
Address
AddrOf
Employee
Employee
48

Should address (A) How do we handle employees
be an attribute? with multiple addresses here?
Addr 1 Addr 2
How do we handle addresses
where internal structure of the
address (e.g. zip code, state) is
Employee
useful?
49

Or (B) be an entity?
Should address (A)
be an attribute?
Street Addr ZIP
Addr 1 Addr 2 Address
AddrOf
Employee
Employee
In general, when we want to record several values,

we choose new entity 50
Lecture 4 > Section 2 > Conversion to SQL
From E/R Diagrams to Relational Schema

• Key concept:
Both Entity sets and Relationships become relations

(tables in RDBMS)
51

price category
• An entity set becomes a relation name
(multiset of tuples / table)

Product
– Each tuple is one entity

Product
– Each tuple is composed of the entity’s name price category
attributes, and has the same primary Gizmo1 99.99 Camera
key Gizmo2 19.99 Edible
52

price category
name
Product
CREATE TABLE Product(
name CHAR(50) PRIMARY KEY,
price DOUBLE,
category VARCHAR(30)
)
Product
name price category
Gizmo1 99.99 Camera
Gizmo2 19.99 Edible
53

date firstname lastname
• A relation between entity sets A1, …, AN also name category

price
becomes a multiset of tuples / a table Product Purchased Person
– Each row/tuple is one relation, i.e. one

unique combination of entities (a1,…,aN)
Purchased
– Each row/tuple is name firstname lastname date
• composed of the union of the entity sets’ keys
Gizmo1 Bob Joe 01/01/15
• has the entities’ primary keys as foreign keys
Gizmo2 Joe Bob 01/03/15
• has the union of the entity sets’ keys as primary
Gizmo1 JoeBob Smith 01/05/15
key
54

date firstname lastname
CREATE TABLE Purchased( name category
name CHAR(50), price
firstname CHAR(50),
lastname CHAR(50),
date DATE,
PRIMARY KEY (name, firstname, lastname),
FOREIGN KEY (name)
REFERENCES Product,
FOREIGN KEY (firstname, lastname) Purchased
REFERENCES Person name firstname lastname date
) Gizmo1 Bob Joe 01/01/15
Gizmo2 Joe Bob 01/03/15
Gizmo1 JoeBob Smith 01/05/15
55
From E/R Diagram to Relational Schema

How do we represent this as a
relational schema?
date
firstname lastname
name category
price
name address
Store
56
ACTIVITY: E/R Diagrams Pt. II
57
Add arrows to your E/R diagram!

Also make sure to add (new concepts underlined):
A player can only Players can achieve a Players have a

belong to one Personal Record weight which
team, a play can linked to a specific changes in on vs.
only be in one Game and Play off-season
game, a pass/run..?
58
[If time]: Can you write queries to:

• Calculate W/L percentage?
• Calculate average game outcome?
• Calculate HIGHEST and LOWEST

ranked teams?
• Calculate the WORST team in the

2014 NFL season if bye weeks did
not exist?
• New! Calculate only team with

suspended QB for first four games.
59
3. Advanced E/R Concepts
60

1. Subclasses & connection to OO
2. Constraints
3. Weak entity sets
4. ACTIVITY: Crayon Time! Drawing E/R diagrams Pt. III
61
Lecture 4 > Section 3 > Subclasses & OO
Modeling Subclasses
• Some objects in a class may be special, i.e. worthy of their own class
• Define a new class?

• But what if we want to maintain connection to current class?
• Better: define a subclass

• Ex: Products
Software Educational
products products
We can define subclasses in E/R!

62
Modeling Subclasses
name
price Child subclasses contain

all the attributes of all of
Product their parent classes plus
the new attributes
shown attached to them
in the E/R diagram
isA
Software Product Educational Product
platforms ageGroup
63
Understanding Subclasses
• Think in terms of records; ex:
name Child subclasses contain
name
• Product all the attributes of all of
price price their parent classes plus
Product the new attributes
name shown attached to them
isA • SoftwareProduct in the E/R diagram
Software Product Educational Product
price
platforms
platforms ageGroup
name
• EducationalProduct
price
ageGroup
64
Product
Think like tables…
name price category
Gizmo 99 gadget
name Camera 49 photo
price Toy 39 gadget
Product
Sw.Product name platforms
isA Gizmo unix

Ed.Product
Software Product Educational Product name ageGroup
platforms ageGroup Gizmo toddler

Toy retired
65
Difference between OO and E/R inheritance
• OO: Classes are disjoint (same for Java, C++)

OO = Object Oriented.
E.g. classes as
fundamental building
block, etc…
Product
p1 p2
p3 ep1 EducationalProduct
sp1
ep2
SoftwareProduct
sp2 ep3
66
• E/R: entity sets overlap
Product
p1 p2
p3 ep1
sp1 EducationalProduct
ep2
SoftwareProduct sp2 ep3
67

We have three entity sets, but four different kinds
of objects
Product
p1 p2
p3 ep1
sp1 EducationalProduct
ep2
SoftwareProduct sp2
esp1 esp2 ep3
No need for multiple inheritance in E/R

68
IsA Review
• If we declare A IsA B then every A is a B
• We use IsA to
• Add descriptive attributes to a subclass
• To identify entities that participate in a relationship
• No need for multiple inheritance
69
Modeling UnionTypes With Subclasses
Person FurniturePiece Company
Suppose each piece of furniture is owned

either by a person, or by a company. How do
we represent this?
70
Modeling Union Types with Subclasses

Say: each piece of furniture is owned either by a person, or
by a company
Solution 1. Acceptable, but imperfect (What’s wrong ?)
Person FurniturePiece Company
ownedByPerson ownedByComp
71
Modeling Union Types with Subclasses

Solution 2: better (though more laborious)
FurniturePiece
What is happening here?
ownedBy
Person Owner Company
isa 72
Lecture 4 > Section 3 > Constraints
Constraints in E/R Diagrams

• Finding constraints is part of the E/R modeling process. Commonly used
constraints are:
• Keys: Implicit constraints on uniqueness of entities

• Ex: An SSN uniquely identifies a person
• Single-value constraints:
• Ex: a person can have only one father
• Referential integrity constraints: Referenced entities must exist Recall

• Ex: if you work for a company, it must exist in the database FOREIGN
KEYs!
• Other constraints:
• Ex: peoples’ ages are between 0 and 150 73
Participation Constraints: Partial v. Total
Product makes Company
Are there products made by no company?

Companies that don’t make a product?
Bold line indicates total participation (i.e. here: all products are made by a company)
74
Keys in E/R Diagrams
Underline keys: name category
price
Note: no formal
way to specify
Product multiple keys in
E/R diagrams…
Person
address name ssn

75
Single Value Constraints
See previous section!
makes
v. s.
makes
76
Referential Integrity Constraints
Each product made by at most one company.

Some products made by no company?
Each product made by exactly one company.

77
Lecture 4 > Section 3 > Weak Entity Sets
Weak Entity Sets

Entity sets are weak when their key comes from other
classes to which they are related.
Team affiliation University
sport number name
“Football team” v. “The Stanford

Football team” (E.g., Berkeley has
a football team too, sort of)
78
Lecture 4 > Section 3 > Weak Entity Sets
Weak Entity Sets

Entity sets are weak when their key comes from other
classes to which they are related.
Team affiliation University
sport number name
• number is a partial key. (denote with dashed underline).

• University is called the identifying owner.
79
• Participation in affiliation must be total. Why?
Lecture 4 > Summary
E/R Summary
• E/R diagrams are a visual syntax that allows
technical and non-technical people to talk
• For conceptual design
• Basic constructs: entity, relationship, and

attributes
• A good design is faithful to the constraints of the

application, but not overzealous
80

Entity Relationship Diagram

Uploaded by

Copyright:

Available Formats

Entity Relationship Diagram

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Entity Relationship Diagram

Uploaded by

Copyright:

Available Formats

Lecture

Lecture 4: The E/R Model

1. E/R Basics: Entities & Relations

2. E/R Design considerations

3. Advanced E/R Concepts

1. E/R Basics: Entities & Relations

What you will learn about in this section

4. ACTIVITY: Crayon Time! Drawing E/R diagrams

• Consider issues such as: This the first project

• Several formalisms exist

Database Design Process

1. Requirements analysis Technical and non-

• How is it going to be used?

• What are we going to do with the data?

• Who should access the data?

Database Design Process

• A high-level description of the database

• Sufficiently precise that technical people can understand it

• But, not so precise that non-technical people can’t participate

This is where E/R fits in. 7

Database Design Process

• Physical Database Design

Database Design Process

E/R is a visual syntax for DB design which is precise enough for

Interlude: Impact of the ER model

• Used by companies big and small

Entities and Entity Sets

• Entities & entity sets are the primitive unit of

• Entities are the individual objects, which are members

Entities and Entity Sets

Entities vs. Entity Sets

Denote elements of the Here, {name, category} is not a key (it

name category If it were, what would it mean?

The R in E/R: Relationships

address name ssn

• A x B (the cross-product) is the set of all pairs c

• A x B (the cross-product) is the set of all pairs (a,b) c

• We define a relationship to be a subset of A x B

A relationship between entity sets P and C is a

name category name

A relationship between entity sets P and C is a

GizmoWorks Gizmo Electronics $9.99 GizmoWorks Gizmo Electronics $9.99

GadgetCorp GizmoLite Electronics $7.50 GizmoWorks GizmoLite Electronics $7.50

A relationship between entity sets P and C is a

GizmoWorks Gizmo Electronics $9.99 GizmoWorks Gizmo Electronics $9.99

GadgetCorp GizmoLite Electronics $7.50 GizmoWorks GizmoLite Electronics $7.50

• This also means that the relationship is uniquely

• Example: the “key” for Makes (to right) is since

Why does this make sense?

Relationships and Attributes

For example: “since” Note: “since” is Note #2: Why not

Decision: Relationship vs. Entity?

name category name

Product Purchased Person

Modeling something as a relationship makes it unique; what if

Decision: Relationship vs. Entity?

• What about this way?

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

We can always use a new entity instead of a relationship. For example, to