Microsoft PowerPoint – 10- DatabaseDesign_ER_FDs
© 2018 A. Alawini & A. Parameswaran
Database Design:
ER and UML Diagrams
Abdu Alawini
University of Illinois at Urbana-Champaign
CS411: Database Systems
October 3, 2018
1
© 2018 A. Alawini & A. Parameswaran
Announcements
•HW 2 will be posted today
•Project Track 1 – Stage 1 is due today
2
© 2018 A. Alawini & A. Parameswaran
3
© 2018 A. Alawini & A. Parameswaran
4
© 2018 A. Alawini & A. Parameswaran
•Conceptual design: (ER & UML Models are used
for this.)
• What are the entities and relationships we need?
•Logical design:
• Transform ER design to Relational Schema
•Schema Refinement: (Normalization)
• Check relational schema for redundancies and
related anomalies.
•Physical Database Design and Tuning:
• Consider typical workloads; (sometimes) modify the
database design; select file types and indexes.
Overview of Database Design
5
We’ll cover
this today
And
introduce this
© 2018 A. Alawini & A. Parameswaran
Agenda
• Finish with weak entity sets
• Design principles
• Translating basic ER diagrams to a relational
schema
• Functional dependences
6
© 2018 A. Alawini & A. Parameswaran
7
Weak Entity Sets
• Occasionally, entities of an entity set need “help” to identify them
uniquely.
• Entity set E is weak if in order to identify entities of E uniquely,
we need to follow one or more many-one relationships from E
and include the key of the related entity sets.
• Note: not an is-a relationship because E is not a “subclass” of F:
Univ and Team
© 2018 A. Alawini & A. Parameswaran
8
Notations for weak entity set
UniversityTeam affiliation
numbersport name
UniversityCourse OfferedAt
numberTitle name
• “University” is a “supporting entity set” for “Team”.
• “Affiliation” is a “supporting relationship”.
)
)
© 2018 A. Alawini & A. Parameswaran
9
Weak entity set in UML
Course University
Num PK
Title
Name PKPK
© 2018 A. Alawini & A. Parameswaran
Another scenario where weak e.s. arises
10
Purchase
Product
Person
Store
date
A Multi-way relationship …
© 2018 A. Alawini & A. Parameswaran
Another scenario where weak e.s. arises
11
Person
Store
Product
StoreOf
ProductOf
BuyerOf
date
Purchase
… converted to binary relationships
Remember this is what we had …
… This is not quite accurate
Not sufficient to
identify purchase via
just dates
© 2018 A. Alawini & A. Parameswaran
Another scenario where weak e.s. arises
12
Person
Store
Product
StoreOf
ProductOf
BuyerOf
date
Purchase
address name ssn
name
categoryprice
nameaddress
3 Supporting Entity Sets &
3 Supporting Relationships
)
)
)
© 2018 A. Alawini & A. Parameswaran
Agenda
Finish with weak entity sets
• Design principles
• Translating basic ER diagrams to a relational
schema
• Functional dependences
13
© 2018 A. Alawini & A. Parameswaran
Design Principles
•Be Faithful to Reality
•Avoid Redundancy
•Pick the Right Kind of Element
Also:
•Simplicity
•Choose the Right Relationships
(See Textbook for examples…)
14
© 2018 A. Alawini & A. Parameswaran 15
Design Principles:
Principle 1: Be Faithful To Reality
PurchaseProduct Person
President PersonCountry
Teaches CourseInstructor
No: A Person may purchase multiple Products
No: A Country can have at most one President
Yes if multiple instructors, No if not.
© 2018 A. Alawini & A. Parameswaran
16
Avoiding Redundancy
• Redundancy occurs when we say the same thing in two different
ways.
• Redundancy wastes space (as we will see) and (more importantly)
encourages inconsistency.
• The two instances of the same fact may become inconsistent if we
change one & forget to change the other.
© 2018 A. Alawini & A. Parameswaran 17
Example: Good
Drinks ManfsManfBy
name
This design gives the address of each
manufacturer exactly once.
name addr
© 2018 A. Alawini & A. Parameswaran 18
Example: Bad
Drinks ManfsManfBy
name
This design states the name of the manufacturer of a
drink twice: as an attribute and as a related entity.
Update issues, Wasteful, …
name
manf
addr
© 2018 A. Alawini & A. Parameswaran 19
Example: Bad
Drinks
name
This design repeats the manufacturer’s address
once for each beer (wasteful, update anomalies);
Also loses the address if there are temporarily no
drinks for a manufacturer.
manf manfAddr
© 2018 A. Alawini & A. Parameswaran
20
Principle 2: Entity Sets Versus Attributes
• An entity set should satisfy at least one of the following
conditions:
• It is more than the name of something;
• i.e., it has at least one non-key attribute.
Or
• It is the “many” in a many-one or many-many relationship.
Examples will illustrate why, but also think why each of these
rules actually make sense.
© 2018 A. Alawini & A. Parameswaran 21
Example: Good
Drinks ManfsManfBy
name
Manfs deserves to be an entity set because of the nonkey
attribute addr.
Drinks deserves to be an entity set because it is the “many”
of the many-one relationship ManfBy. Can you see why?
name addr
An E.S. is more than the name of something;
i.e., it has at least one non-key attribute.
OR
An E.S is the “many” in a many-one or many-many relationship.
© 2018 A. Alawini & A. Parameswaran 22
Example: Good
Drinks
name
If we had no manufacturer address information…
There is no need to make the manufacturer an entity set,
because we record nothing about manufacturers besides their
name.
manf
An E.S. is more than the name of something;
i.e., it has at least one non-key attribute.
OR
An E.S is the “many” in a many-one or many-many relationship.
© 2018 A. Alawini & A. Parameswaran 23
Example: Bad
Drinks ManfsManfBy
name
Since the manufacturer is nothing but a name, and is
not at the “many” end of any relationship, it should not
be an entity set.
name
An E.S. is more than the name of something;
i.e., it has at least one non-key attribute.
OR
An E.S is the “many” in a many-one or many-many relationship.
© 2018 A. Alawini & A. Parameswaran
Construct an E-R diagram for a car-insurance company
whose customers own one or more cars each. Each car
has associated with it zero to any number of recorded
accidents.
ERD Exercise
24
© 2018 A. Alawini & A. Parameswaran
ERD Exercise Student Solution 1
25
© 2018 A. Alawini & A. Parameswaran
ERD Exercise Student Solution 2
26
© 2018 A. Alawini & A. Parameswaran
ERD Exercise Student Solution 3
27
© 2018 A. Alawini & A. Parameswaran
28
ER Review
•Basics of ER and UML
• entity, attribute, entity set
• relation: binary, multiway, converting from multiway
• relationship roles, attributes on relationships
• subclasses (is-a)
•Constraints
• on relations
• many-one, one-one, many-many
• limitations of arrows
• keys, single-valued, ref integrity, domain & general constraints
© 2018 A. Alawini & A. Parameswaran
Agenda
Finish with weak entity sets
Design principles
• Translating basic ER diagrams to a relational
schema
• Functional dependences
29
© 2018 A. Alawini & A. Parameswaran
Converting ER to Relational Schema
30
Project
P-number
P-name
Due-Date
Employee
SSN
E-Name
Office
0..* Assignment 0..*
0..* Manager 1..1
© 2018 A. Alawini & A. Parameswaran
1. Translate each entity set into a table,
with keys.
• Entity set:
•can be represented as
a table in the
relational model
•has a key … which
becomes a key for the
table
31
CREATE TABLE Employee
(SSN CHAR(11) NOT NULL,
E-Name CHAR(20),
Office INTEGER,
PRIMARY KEY (SSN))
Employee
SSN
E-Name
Office
© 2018 A. Alawini & A. Parameswaran
Multi-valued Attribute
Didn’t see this case when discussing ER diagrams
One or more values of same attribute for an entity
32
Employee
SSN Office
E-Name
Office Office
© 2018 A. Alawini & A. Parameswaran
2. Create a table for the multi-valued
attribute.
33
How many offices can one employee have?
Project(P-number, P-name, Due-Date)
Employee(SSN, E-Name, Office)
vs.
Project(P-number, P-name, Due-Date)
Employee(SSN, E-Name)
Office-Assignment(SSN, Office)
Most relational DBMSs do not allow multi-valued
attributes.
© 2018 A. Alawini & A. Parameswaran
Project(P-number, P-name, Due-Date)
Employee(SSN, E-Name, Office)
12 Smith O-105
15 Wei O-110
Project(P-number, P-name, Due-Date)
Employee(SSN, E-Name)
12 Smith
15 Wei
Office-Assignment(SSN, Office)
12 O-105
12 O-106
15 O-110
Sample Data
34
© 2018 A. Alawini & A. Parameswaran
3. Translate each many-to-many
relationship set into a table
3535
What are the attributes and what is the key for Assignment?
Project(P-number, P-name, Due-Date)
Employee(SSN, E-Name, Office)
Project
P-number
P-name
Due-Date
Employee
SSN
E-Name
Office
0..* Assignment 0..*
0..* Manager 1..1
© 2018 A. Alawini & A. Parameswaran
Answer: Assignment(P-Number, SSN)
P-Number is a foreign key for Project
SSN is a foreign key for Employee
Project(P-Number, P-Due-Date)
Employee(SSN, E-Name, Office)
Project
P-number
P-name
Due-Date
Employee
SSN
E-Name
Office
0..* Assignment 0..*
0..* Manager 1..1
36
3. Translate each many-to-many
relationship set into a table
© 2018 A. Alawini & A. Parameswaran
What should we do with each one-to-many
relationship set?
37
Manager (?)
Project(P-number, P-name, Due-Date)
Employee(SSN, E-Name, Office)
Project
P-number
P-name
Due-Date
Employee
SSN
E-Name
Office
0..* Assignment 0..*
0..* Manager 1..1
© 2018 A. Alawini & A. Parameswaran
4. Create a foreign key for a 1-to-many
relationship set.
38
Project(P-number, P-name, Due-Date, MgrSSN)
Employee(SSN, E-Name, Office)
MgrSSN is a foreign key (referencing the Employee relation)
value of Manager must match an SSN
Project
P-number
P-name
Due-Date
Employee
SSN
E-Name
Office
0..* Assignment 0..*
0..* Manager 1..1
© 2018 A. Alawini & A. Parameswaran
4. Create a foreign key for a 1-to-many
relationship set.
39
Project(P-number, P-name, Due-Date, MgrSSN)
Employee(SSN, E-Name, Office)
vs.
Project(P-number, P-name, Due-Date)
Employee(SSN, E-Name, Office)
Manager(P-number, SSN)
What are the tradeoffs between these two?
Note:
P-number
is the key
for Manager
Project
P-number
P-name
Due-Date
Employee
SSN
E-Name
Office
0..* Assignment 0..*
0..* Manager 1..1
© 2018 A. Alawini & A. Parameswaran
What do we do when a many-to-many
relationship set has an attribute?
40
Assignment(P-number, SSN)
Project(P-number, P-name, Due-Date)
Employee(SSN, E-Name, Office)
Project
P-number
P-name
Due-Date
Employee
SSN
E-Name
Office
role
start-date
end-date
0..* Assignment 0..*
© 2018 A. Alawini & A. Parameswaran 41
Assignment(P-number, SSN, role, start-date, end-date)
Project(P-number, P-name, Due-Date)
Employee(SSN, E-Name, Office)
Project
P-number
P-name
Due-Date
Employee
SSN
E-Name
Office
role
start-date
end-date
What do we do when a many-to-many
relationship set has an attribute?
0..* Assignment 0..*
© 2018 A. Alawini & A. Parameswaran
What do we do when a 1-to-many
relationship set has an attribute?
42
Project(P-number, P-name, Due-Date, MgrSSN)
Employee(SSN, E-Name, Office)
Employee
SSN
E-Name
Office
start-date
end-date
Project
P-number
P-name
Due-Date
0..* Manager 1..1
© 2018 A. Alawini & A. Parameswaran
43
Project(P-number, P-name, Due-Date, MgrSSN,
start-date, end-date)
Employee(SSN, E-Name, Office )
Employee
SSN
E-Name
Office
start-date
end-date
Project
P-number
P-name
Due-Date
What do we do when a 1-to-many
relationship set has an attribute?
0..* Manager 1..1
© 2018 A. Alawini & A. Parameswaran
Weak Entity Sets
44
Policy
dep-name
cost
Insures
supporting
entity set
supporting
relationship
set
weak
Entity set
Employee
SSN
name
office
© 2018 A. Alawini & A. Parameswaran
• Weak entity sets and supporting relationship sets are
translated into a single table. Must include key of
supporting entity set, as a foreign key.
• When the owner entity is deleted, all owned weak
entities must also be deleted.
Translating Weak Entity Sets
45
CREATE TABLE Insurance_Policy (
dep-name CHAR(20),
cost REAL,
ssn CHAR(11) NOT NULL,
PRIMARY KEY (dep-name, ssn),
FOREIGN KEY (ssn) REFERENCES Employee,
ON DELETE CASCADE)
© 2018 A. Alawini & A. Parameswaran
Agenda
Finish with weak entity sets
Design principles
Translating basic ER diagrams to a relational
schema
• Functional dependences
46