E/R Model Considerations and Relationships
Lecture 9:
The E/R Model II
Lecture and activity contents are based on what Prof Chris Ré
used in his CS 145 in the fall 2016 term with permission.
2. E/R Design Considerations
2
What you will learn about in this section
1.
Relationships cont’d: multiplicity, multi-way
2.
Design considerations
3.
Conversion to SQL
4.
ACTIVITY: Crayon Time! Drawing E/R diagrams Pt. II
3
4
Multiplicity of E/R Relationships
5
address
name
ssn
Person
buys
makes
employs
Company
Product
name
category
stockprice
name
price
What does
this say?
6
Multi-way Relationships
How do we model a purchase relationship between buyers,
products and stores?
7
Q
: What does the arrow mean ?
Arrows in Multiway Relationships
8
Q
: What does the arrow mean ?
Arrows in Multiway Relationships
Purchase
Product
Person
Store
9
Q
: How do we say that every person shops in at most one store ?
Arrows in Multiway Relationships
A
: Cannot. This is the best approximation.
(Why only approximation ?)
10
Converting Multi-way Relationships
to Binary
From what we
had on
previous slide
to this - what
did we do?
11
Converting Multi-way Relationships
to New Entity + Binary Relationships
Purchase
Person
Store
Product
StoreOf
ProductOf
BuyerOf
date
Decision: Multi-way or New Entity + Binary?
12
Multi-way Relationship
Entity + Binary
Decision: Multi-way or New Entity + Binary?
13
(A) Multi-way Relationship
(B) Entity + Binary
•
Covered earlier:
(B) is useful if we want to have multiple instances of the
“relationship” per entity combination
Decision: Multi-way or New Entity + Binary?
14
(A) Multi-way Relationship
(B) Entity + Binary
•
(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”
Decision: Multi-way or New Entity + Binary?
15
(A) Multi-way Relationship
(B) Entity + Binary
•
(A) is useful when a relationship really is between multiple entities
-
Ex: A three-party legal contract
16
3. Design Principles
17
Design Principles:
What’s Wrong?
Purchase
Product
Store
date
personName
personAddr
18
Design Principles:
What’s Wrong?
Purchase
Product
Person
Store
date
Dates
Examples: Entity vs. Attribute
19
Should address (A)
be an attribute?
Or (B) be an entity?
Examples: Entity vs. Attribute
20
Should address (A)
be an attribute?
How do we handle employees
with multiple addresses here?
How do we handle addresses
where internal structure of the
address (e.g. zip code, state) is
useful?
Examples: Entity vs. Attribute
21
Or (B) be an entity?
Should address (A)
be an attribute?
From E/R Diagrams to Relational Schema
•
Key concept:
Both
Entity sets
and
Relationships
become relations
(tables in RDBMS)
22
From E/R Diagrams to Relational Schema
23
Product
•
An entity set becomes a relation
(multiset of tuples / table)
–
Each tuple is one entity
–
Each tuple is composed of the entity’s
attributes, and has the same primary
key
From E/R Diagrams to Relational Schema
24
Product
price
category
name
Product
CREATE TABLE
Product(
name CHAR(50) PRIMARY KEY,
price DOUBLE,
category VARCHAR(30)
)
From E/R Diagrams to Relational Schema
25
Purchased
•
A relation
between entity sets A
1
, …, A
N
also
becomes a multiset of tuples / a table
–
Each row/tuple is one relation, i.e. one
unique combination of entities (a
1
,…,a
N
)
–
Each row/tuple is
•
composed of the
union of the entity sets’ keys
•
has the entities’ primary keys as foreign keys
•
has the union of the entity sets’ keys as primary
key
From E/R Diagrams to Relational Schema
26
Purchased
lastname
CREATE TABLE
Purchased(
name CHAR(50),
firstname CHAR(50),
lastname CHAR(50),
date DATE,
PRIMARY KEY (name, firstname, lastname),
FOREIGN KEY (name)
REFERENCES Product,
FOREIGN KEY (firstname, lastname)
REFERENCES Person
)
From E/R Diagram to Relational Schema
27
Purchased
Product
name
category
price
Person
firstname
date
lastname
Store
name
address
How do we represent this as a
relational schema?
ACTIVITY: E/R Diagrams Part II
28
Add arrows to your E/R diagram!
29
A player can only
belong to one
team, a play can
only be in one
game, a pass/run..?
P
l
a
y
e
r
s
c
a
n
a
c
h
i
e
v
e
a
P
e
r
s
o
n
a
l
R
e
c
o
r
d
l
i
n
k
e
d
t
o
a
s
p
e
c
i
f
i
c
G
a
m
e
a
n
d
P
l
a
y
A
l
s
o
m
a
k
e
s
u
r
e
t
o
a
d
d
(
n
e
w
c
o
n
c
e
p
t
s
u
n
d
e
r
l
i
n
e
d
)
:
P
l
a
y
e
r
s
h
a
v
e
a
w
e
i
g
h
t
w
h
i
c
h
c
h
a
n
g
e
s
i
n
o
n
v
s
.
o
f
f
-
s
e
a
s
o
n
Explore the E/R model considerations and relationships like multiplicity, multi-way, conversion to SQL, and more. Learn about modeling purchase relationships and the significance of arrows in multi-way relationships. Understand the challenges in expressing constraints like every person shopping at most one store and converting multi-way relationships to binary form.
Uploaded on Sep 14, 2024 | 0 Views
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
Lecture 9: The E/R Model II Lecture and activity contents are based on what Prof Chris R used in his CS 145 in the fall 2016 term with permission.
What you will learn about in this section 1. Relationships cont d: multiplicity, multi-way 2. Design considerations 3. Conversion to SQL 4. ACTIVITY: Crayon Time! Drawing E/R diagrams Pt. II 3
Multiplicity of E/R Relationships a b c d 1 2 3 One-to-one: Indicated using arrows a b c d 1 2 3 Many-to-one: X -> Y means there exists a there exists a function mapping function mapping from X to Y from X to Y (recall the definition of a function) a b c d 1 2 3 One-to-many: a b c d 1 2 3 Many-to-many: 4
name category name price makes Company Product stockprice What does this say? buys employs Person name ssn address 5
Multi-way Relationships How do we model a purchase relationship between buyers, products and stores? Product Purchase Store Person NB: Can still model as a mathematical set (how?) 6
Arrows in Multiway Relationships Q: What does the arrow mean ? Product Store Purchase Person 7
Arrows in Multiway Relationships Q: What does the arrow mean ? Product Store Purchase Person 8
Arrows in Multiway Relationships Q: How do we say that every person shops in at most one store ? Product Store Purchase Person A: Cannot. This is the best approximation. (Why only approximation ?) 9
Converting Multi-way Relationships to Binary From what we had on previous slide to this - what did we do? date Product ProductOf Purchase Store StoreOf BuyerOf Person 10
Converting Multi-way Relationships to New Entity + Binary Relationships Side note: What arrows should be added here? Are these correct? ProductOf Product date Purchase StoreOf Store BuyerOf Person 11
Decision: Multi-way or New Entity + Binary? Entity + Binary Multi-way Relationship ProductOf Product Product date Store Purchase Purchase StoreOf Store BuyerOf Person Person Should we use a single m multi ulti- -way relationship way relationship or a new entity with binary relations? binary relations? new entity with 12
Decision: Multi-way or New Entity + Binary? (B) Entity + Binary (A) Multi-way Relationship ProductOf Product Product date Store Purchase Purchase StoreOf 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 13
Decision: Multi-way or New Entity + Binary? (B) Entity + Binary (A) Multi-way Relationship ProductOf Product Product date Store Purchase Purchase StoreOf 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 14
Decision: Multi-way or New Entity + Binary? (B) Entity + Binary (A) Multi-way Relationship ProductOf Product Product date Store Purchase Purchase StoreOf Store BuyerOf Person Person (A) is useful when a relationship really is between multiple entities - Ex: A three-party legal contract 15
3. Design Principles What s wrong with these examples? Product Purchase Person President Country Person 16
Design Principles: What s Wrong? date Product Purchase Store personAddr personName 17
Design Principles: What s Wrong? date Dates Product Purchase Store Person 18
Examples: Entity vs. Attribute Should address (A) be an attribute? Or (B) be an entity? Street Addr ZIP Addr 1 Addr 2 Address AddrOf Employee Employee 19
Examples: Entity vs. Attribute Should address (A) be an attribute? How do we handle employees 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 useful? Employee 20
Examples: Entity vs. Attribute Or (B) be an entity? Should address (A) be an attribute? Street Addr ZIP Addr 1 Address Addr 2 AddrOf Employee Employee In general, when we want to record several values, we choose new entity 21
From E/R Diagrams to Relational Schema Key concept: Both Entity sets and Relationships become relations (tables in RDBMS) 22
From E/R Diagrams to Relational Schema price category An entity set becomes a relation (multiset of tuples / table) name Product Each tuple is one entity Product Each tuple is composed of the entity s attributes, and has the same primary key name price category Gizmo1 99.99 Camera Gizmo2 19.99 Edible 23
From E/R Diagrams to Relational Schema 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 24
From E/R Diagrams to Relational Schema date firstname lastname name A relation between entity sets A1, , ANalso becomes a multiset of tuples / a table category price Product Person Purchased Each row/tuple is one relation, i.e. one unique combination of entities (a1, ,aN) Purchased Each row/tuple is composed of the union of the entity sets keys has the entities primary keys as foreign keys has the union of the entity sets keys as primary key name firstname lastname date Gizmo1 Bob Joe 01/01/15 Gizmo2 Joe Bob 01/03/15 Gizmo1 JoeBob Smith 01/05/15 25
From E/R Diagrams to Relational Schema date firstname lastname CREATE TABLE Purchased( name CHAR(50), firstname CHAR(50), lastname CHAR(50), date DATE, PRIMARY KEY (name, firstname, lastname), FOREIGN KEY (name) REFERENCES Product, FOREIGN KEY (firstname, lastname) REFERENCES Person ) name category price Product Purchased Person Purchased name firstname lastname date Gizmo1 Bob Joe 01/01/15 Gizmo2 Joe Bob 01/03/15 Gizmo1 JoeBob Smith 01/05/15 26
From E/R Diagram to Relational Schema How do we represent this as a relational schema? date firstname lastname name category price Product Purchased Person name address Store 27
Add arrows to your E/R diagram! Also make sure to add (new concepts underlined): (new concepts underlined): A player can only belong to one team, a play can only be in one game, a pass/run..? Players can achieve a Personal Record Personal Record linked to a specific Game and Play Players have a weight weight which changes in on vs. off-season 29
