Database Design Database Design Process
Transcript Of Database Design Database Design Process
Introduction to Data Management CSE 344
Lecture 14: E/R Diagrams
Magda Balazinska - CSE 344, Fall 2012
1
Today: E/R Diagrams
Motivating scenario
• Customer asks you to help them setup a DBMS
• They want to store information about
– Companies and various branches inside companies
• Each company has a name, an address, and a CEO
• Each company also has a list of key employees
• Each branch has a name and a market share in $$$
– Products manufactured by these companies
• Each product has a name and a description
• Products are manufactured by different branches
Magda Balazinska - CSE 344, Fall 2012
2
Database Design
• Why do we need it?
– Need a way to model real world entities in terms of relations – Not easy to go from real-world entities to a database schema
• 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 exists
– We discuss E/R diagrams
Magda Balazinska - CSE 344, Fall 2012
3
Database Design Process
Conceptual Model:
name Patient
patien_of
Doctor
zip
name
dno
Relational Model: Tables + constraints And also functional dep.
Normalization: Eliminates anomalies
Conceptual Schema
Physical storage details Physical Schema
Entity / Relationship Diagrams
This is an entity set
Objects Classes
entities entity sets
Product
Attributes are like in ODL
address
(ODL = Object Definition Language)
Relationships: like in ODL except
buys
- first class citizens (not associated with classes) - not necessarily binary
Magda Balazinska - CSE 344, Fall 2012
5
name category
price Product
makes
buys address
Person name
name Company
stockprice employs
ssn
6
1
Keys in E/R Diagrams
• Every entity set must have a key
name category price
Product
Magda Balazinska - CSE 344, Fall 2012
7
What is a Relation ?
• A mathematical definition:
– if A, B are sets, then a relation R is a subset of A × B
• A={1,2,3}, B={a,b,c,d},
A × B = {(1,a),(1,b), . . ., (3,d)}
1
a
R = {(1,a), (1,c), (3,b)}
A= 2 b
3
c
• makes is a subset of Product × CompanyB:= d
Product
makes
Company
Magda Balazinska - CSE 344, Fall 2012
8
Multiplicity of E/R Relations
• one-one:
1
a
2
b
3
c
• many-one d
1
a
2
b
3
c
d
• many-many
1
a
2
b
3
c
d
Magda Balazinska - CSE 344, Fall 2012
9
name category
price Product
makes
buys
What does this say ?
Person
name Company
stockprice employs
address
name
ssn
10
Multi-way Relationships
How do we model a purchase relationship between buyers, products and stores?
Product
Purchase
Store
Person
Can still model as a mathematical set (how ?)
11
Arrows in Multiway Relationships
Q: What does the arrow mean ?
Product
Purchase
Store
Person
A: A given person buys a given product from at most one store
Magda Balazinska - CSE 344, Fall 2012
12
2
Arrows in Multiway Relationships
Q: What does the arrow mean ? Product
Purchase
Store
Person
A: A given person buys a given product from at most one store AND every store sells to every person at most one product
Magda Balazinska - CSE 344, Fall 2012
13
Arrows in Multiway Relationships
Q: How do we say that every person shops at at most one store ?
Product Purchase
Store
Person
A: Cannot. This is the best approximation. (Why only approximation ?)
Magda Balazinska - CSE 344, Fall 2012
14
Converting Multi-way Relationships to Binary
date ProductOf
Product
Purchase
StoreOf
Store
BuyerOf Arrows go in which direction?
Person
15
Converting Multi-way Relationships to Binary
date ProductOf
Product
Purchase
StoreOf
Store
BuyerOf Make sure you understand why!
Person
16
3. Design Principles
What’s wrong?
Product
Purchase
Person
Country
President
Person
Moral: be faithful to the specifications of the app!
Magda Balazinska - CSE 344, Fall 2012
17
Product
Design Principles: What’s Wrong?
date
Purchase
Store
personAddr
Moral: pick the right kind of entities.
personName
Magda Balazinska - CSE 344, Fall 2012
18
3
Design Principles: What’s Wrong?
Product
Dates
date
Purchase
Moral: don’t complicate life more than it already is.
Person
Store
19
From E/R Diagrams to Relational Schema
• Entity set à relation • Relationship à relation
Magda Balazinska - CSE 344, Fall 2012
20
Entity Set to Relation
prod-ID category price
Product
Product(prod-ID, category, price)
prod-ID
category
price
Gizmo55
Camera
99.99
Pokemn19 Toy
29.99
21
Create Table (SQL)
CREATE TABLE Product ( prod-ID CHAR(30) PRIMARY KEY, category VARCHAR(20), price double)
Magda Balazinska - CSE 344, Fall 2012
22
N-N Relationships to Relations
prod-ID cust-ID date
date
name
Orders
Shipment
Shipping-Co
address
Represent that in relations!
Magda Balazinska - CSE 344, Fall 2012
23
N-N Relationships to Relations
prod-ID cust-ID date
date
name
Orders
Shipment
Shipping-Co
Orders(prod-ID,cust-ID, date) Shipment(prod-ID,cust-ID, name, date) Shipping-Co(name, address) prod-ID cust-ID
Gizmo55 Joe12
Gizmo55 Joe12
address
name UPS FEDEX
date
4/10/2011 24 4/9/2011
4
Create Table (SQL)
CREATE TABLE Shipment( name CHAR(30) REFERENCES Shipping-Co, prod-ID CHAR(30), cust-ID VARCHAR(20), date DATETIME,
PRIMARY KEY (name, prod-ID, cust-ID), FOREIGN KEY (prod-ID, cust-ID)
REFERENCES Orders )
N-1 Relationships to Relations
prod-ID cust-ID date
date
name
Orders
Shipment
Shipping-Co
address
Represent this in relations!
Magda Balazinska - CSE 344, Fall 2012
26
N-1 Relationships to Relations
prod-ID cust-ID date
date
name
Orders
Shipment
Shipping-Co
Orders(prod-ID,cust-ID, date1, name, date2) Shipping-Co(name, address)
address
Remember: no separate relations for many-one relationship
Multi-way Relationships to
Product
Relations
name
address
prod-ID price Purchase
Store
Person
Represent this in relations!
ssn
name
Magda Balazinska - CSE 344, Fall 2012
28
Modeling Subclasses
Some objects in a class may be special • define a new class • better: define a subclass
Products
Software products
Educational products
So --- we define subclasses in E/R
Magda Balazinska - CSE 344, Fall 2012
29
Subclasses
name
category
price
Product
isa
isa
Software Product
Educational Product
platforms
Magda Balazinska - CSE 344, Fall 2012
Age Group
5
Understanding Subclasses
• Think in terms of records:
– Product
field1 field2
– SoftwareProduct – EducationalProduct
field1
field2
field3
field1
field2
field4 field5
Magda Balazinska - CSE 344, Fall 2012
31
Subclasses to Relations
name category
price
Product
isa
isa
Product
Name Gizmo Camera
Toy
Price 99 49 39
Category gadget photo gadget
Sw.Product
Name Gizmo
platforms unix
Software Product platforms
Educational Product Age Group
Ed.Product
Name
Age Group
Other ways to convert are possible
Magda Balazinska - CSE 344, Fall 2012
Gizmo Toy
toddler retired
32
Modeling UnionTypes With Subclasses
FurniturePiece
Person
Company
Say: each piece of furniture is owned either by a person or by a company
Magda Balazinska - CSE 344, Fall 2012
33
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.
Magda Balazinska - CSE 344, Fall 2012
34
Modeling Union Types with Subclasses
Solution 2: better, more laborious
Owner
isa Person
ownedBy
isa Company
FurniturePiece
Magda Balazinska - CSE 344, Fall 2012
35
Weak Entity Sets
Entity sets are weak when their key comes from other classes to which they are related.
Team
affiliation
sport
number
University name
Team(sport, number, universityName) University(name)
Magda Balazinska - CSE 344, Fall 2012
36
6
What Are the Keys of R ?
A
B
R
H
S
T
C
W
U
V
V FL K
E
Q
D
G
Z
Constraints in E/R Diagrams
Finding constraints is part of the modeling process. Commonly used constraints:
Keys: social security number uniquely identifies a person.
Single-value constraints: a person can have only one father.
Referential integrity constraints: if you work for a company, it must exist in the database.
Other constraints: peoples’ ages are between 0 and 150.
Magda Balazinska - CSE 344, Fall 2012
38
Keys in E/R Diagrams
Underline:
name price
category
No formal way to specify multiple keys in E/R diagrams
Product Person
address
name
ssn
Single Value Constraints
makes v. s. makes
Magda Balazinska - CSE 344, Fall 2012
40
Referential Integrity Constraints
Product
makes
Company
Each product made by at most one company. Some products made by no company
Product
makes
Company
Each product made by exactly one company.
Magda Balazinska - CSE 344, Fall 2012
41
Other Constraints
Product
<100 makes
Company
Q: What does this mean ? A: A Company entity cannot be connected by relationship to more than 99 Product entities
Magda Balazinska - CSE 344, Fall 2012
42
7
Lecture 14: E/R Diagrams
Magda Balazinska - CSE 344, Fall 2012
1
Today: E/R Diagrams
Motivating scenario
• Customer asks you to help them setup a DBMS
• They want to store information about
– Companies and various branches inside companies
• Each company has a name, an address, and a CEO
• Each company also has a list of key employees
• Each branch has a name and a market share in $$$
– Products manufactured by these companies
• Each product has a name and a description
• Products are manufactured by different branches
Magda Balazinska - CSE 344, Fall 2012
2
Database Design
• Why do we need it?
– Need a way to model real world entities in terms of relations – Not easy to go from real-world entities to a database schema
• 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 exists
– We discuss E/R diagrams
Magda Balazinska - CSE 344, Fall 2012
3
Database Design Process
Conceptual Model:
name Patient
patien_of
Doctor
zip
name
dno
Relational Model: Tables + constraints And also functional dep.
Normalization: Eliminates anomalies
Conceptual Schema
Physical storage details Physical Schema
Entity / Relationship Diagrams
This is an entity set
Objects Classes
entities entity sets
Product
Attributes are like in ODL
address
(ODL = Object Definition Language)
Relationships: like in ODL except
buys
- first class citizens (not associated with classes) - not necessarily binary
Magda Balazinska - CSE 344, Fall 2012
5
name category
price Product
makes
buys address
Person name
name Company
stockprice employs
ssn
6
1
Keys in E/R Diagrams
• Every entity set must have a key
name category price
Product
Magda Balazinska - CSE 344, Fall 2012
7
What is a Relation ?
• A mathematical definition:
– if A, B are sets, then a relation R is a subset of A × B
• A={1,2,3}, B={a,b,c,d},
A × B = {(1,a),(1,b), . . ., (3,d)}
1
a
R = {(1,a), (1,c), (3,b)}
A= 2 b
3
c
• makes is a subset of Product × CompanyB:= d
Product
makes
Company
Magda Balazinska - CSE 344, Fall 2012
8
Multiplicity of E/R Relations
• one-one:
1
a
2
b
3
c
• many-one d
1
a
2
b
3
c
d
• many-many
1
a
2
b
3
c
d
Magda Balazinska - CSE 344, Fall 2012
9
name category
price Product
makes
buys
What does this say ?
Person
name Company
stockprice employs
address
name
ssn
10
Multi-way Relationships
How do we model a purchase relationship between buyers, products and stores?
Product
Purchase
Store
Person
Can still model as a mathematical set (how ?)
11
Arrows in Multiway Relationships
Q: What does the arrow mean ?
Product
Purchase
Store
Person
A: A given person buys a given product from at most one store
Magda Balazinska - CSE 344, Fall 2012
12
2
Arrows in Multiway Relationships
Q: What does the arrow mean ? Product
Purchase
Store
Person
A: A given person buys a given product from at most one store AND every store sells to every person at most one product
Magda Balazinska - CSE 344, Fall 2012
13
Arrows in Multiway Relationships
Q: How do we say that every person shops at at most one store ?
Product Purchase
Store
Person
A: Cannot. This is the best approximation. (Why only approximation ?)
Magda Balazinska - CSE 344, Fall 2012
14
Converting Multi-way Relationships to Binary
date ProductOf
Product
Purchase
StoreOf
Store
BuyerOf Arrows go in which direction?
Person
15
Converting Multi-way Relationships to Binary
date ProductOf
Product
Purchase
StoreOf
Store
BuyerOf Make sure you understand why!
Person
16
3. Design Principles
What’s wrong?
Product
Purchase
Person
Country
President
Person
Moral: be faithful to the specifications of the app!
Magda Balazinska - CSE 344, Fall 2012
17
Product
Design Principles: What’s Wrong?
date
Purchase
Store
personAddr
Moral: pick the right kind of entities.
personName
Magda Balazinska - CSE 344, Fall 2012
18
3
Design Principles: What’s Wrong?
Product
Dates
date
Purchase
Moral: don’t complicate life more than it already is.
Person
Store
19
From E/R Diagrams to Relational Schema
• Entity set à relation • Relationship à relation
Magda Balazinska - CSE 344, Fall 2012
20
Entity Set to Relation
prod-ID category price
Product
Product(prod-ID, category, price)
prod-ID
category
price
Gizmo55
Camera
99.99
Pokemn19 Toy
29.99
21
Create Table (SQL)
CREATE TABLE Product ( prod-ID CHAR(30) PRIMARY KEY, category VARCHAR(20), price double)
Magda Balazinska - CSE 344, Fall 2012
22
N-N Relationships to Relations
prod-ID cust-ID date
date
name
Orders
Shipment
Shipping-Co
address
Represent that in relations!
Magda Balazinska - CSE 344, Fall 2012
23
N-N Relationships to Relations
prod-ID cust-ID date
date
name
Orders
Shipment
Shipping-Co
Orders(prod-ID,cust-ID, date) Shipment(prod-ID,cust-ID, name, date) Shipping-Co(name, address) prod-ID cust-ID
Gizmo55 Joe12
Gizmo55 Joe12
address
name UPS FEDEX
date
4/10/2011 24 4/9/2011
4
Create Table (SQL)
CREATE TABLE Shipment( name CHAR(30) REFERENCES Shipping-Co, prod-ID CHAR(30), cust-ID VARCHAR(20), date DATETIME,
PRIMARY KEY (name, prod-ID, cust-ID), FOREIGN KEY (prod-ID, cust-ID)
REFERENCES Orders )
N-1 Relationships to Relations
prod-ID cust-ID date
date
name
Orders
Shipment
Shipping-Co
address
Represent this in relations!
Magda Balazinska - CSE 344, Fall 2012
26
N-1 Relationships to Relations
prod-ID cust-ID date
date
name
Orders
Shipment
Shipping-Co
Orders(prod-ID,cust-ID, date1, name, date2) Shipping-Co(name, address)
address
Remember: no separate relations for many-one relationship
Multi-way Relationships to
Product
Relations
name
address
prod-ID price Purchase
Store
Person
Represent this in relations!
ssn
name
Magda Balazinska - CSE 344, Fall 2012
28
Modeling Subclasses
Some objects in a class may be special • define a new class • better: define a subclass
Products
Software products
Educational products
So --- we define subclasses in E/R
Magda Balazinska - CSE 344, Fall 2012
29
Subclasses
name
category
price
Product
isa
isa
Software Product
Educational Product
platforms
Magda Balazinska - CSE 344, Fall 2012
Age Group
5
Understanding Subclasses
• Think in terms of records:
– Product
field1 field2
– SoftwareProduct – EducationalProduct
field1
field2
field3
field1
field2
field4 field5
Magda Balazinska - CSE 344, Fall 2012
31
Subclasses to Relations
name category
price
Product
isa
isa
Product
Name Gizmo Camera
Toy
Price 99 49 39
Category gadget photo gadget
Sw.Product
Name Gizmo
platforms unix
Software Product platforms
Educational Product Age Group
Ed.Product
Name
Age Group
Other ways to convert are possible
Magda Balazinska - CSE 344, Fall 2012
Gizmo Toy
toddler retired
32
Modeling UnionTypes With Subclasses
FurniturePiece
Person
Company
Say: each piece of furniture is owned either by a person or by a company
Magda Balazinska - CSE 344, Fall 2012
33
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.
Magda Balazinska - CSE 344, Fall 2012
34
Modeling Union Types with Subclasses
Solution 2: better, more laborious
Owner
isa Person
ownedBy
isa Company
FurniturePiece
Magda Balazinska - CSE 344, Fall 2012
35
Weak Entity Sets
Entity sets are weak when their key comes from other classes to which they are related.
Team
affiliation
sport
number
University name
Team(sport, number, universityName) University(name)
Magda Balazinska - CSE 344, Fall 2012
36
6
What Are the Keys of R ?
A
B
R
H
S
T
C
W
U
V
V FL K
E
Q
D
G
Z
Constraints in E/R Diagrams
Finding constraints is part of the modeling process. Commonly used constraints:
Keys: social security number uniquely identifies a person.
Single-value constraints: a person can have only one father.
Referential integrity constraints: if you work for a company, it must exist in the database.
Other constraints: peoples’ ages are between 0 and 150.
Magda Balazinska - CSE 344, Fall 2012
38
Keys in E/R Diagrams
Underline:
name price
category
No formal way to specify multiple keys in E/R diagrams
Product Person
address
name
ssn
Single Value Constraints
makes v. s. makes
Magda Balazinska - CSE 344, Fall 2012
40
Referential Integrity Constraints
Product
makes
Company
Each product made by at most one company. Some products made by no company
Product
makes
Company
Each product made by exactly one company.
Magda Balazinska - CSE 344, Fall 2012
41
Other Constraints
Product
<100 makes
Company
Q: What does this mean ? A: A Company entity cannot be connected by relationship to more than 99 Product entities
Magda Balazinska - CSE 344, Fall 2012
42
7