Transcript lecture08

Lecture 08:
E/R Diagrams and Functional
Dependencies
Friday, October 15, 2004
1
Outline
• Finish E/R diagrams (Chapter 2)
– And E/R diagrams to relations (3.2, 3.3)
• The relational data model: 3.1
• Functional dependencies: 3.4
2
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.
3
Keys in E/R Diagrams
name
Underline:
category
price
No formal way
to specify multiple
keys in E/R diagrams
Product
Person
address
name
ssn
4
Single Value Constraints
makes
v. s.
makes
5
Referential Integrity Constraints
Product
makes
Company
Each product made by at most one company.
Some products made by no company
Product
makes
Each product made by exactly one company.
Company
6
Other Constraints
<100
Product
makes
Company
What does this mean ?
7
Weak Entity Sets
Entity sets are weak when their key comes from other
classes to which they are related.
affiliation
Team
sport
number
University
name
Notice: we encountered this when converting
multiway relationships to binary relationships (last lecture)
8
Handling Weak Entity Sets
affiliation
Team
sport
number
University
name
Convert to a relational schema (in class)
9
The Relational Data Model
Data
Modeling
Relational
Schema
Physical
storage
Have seen
this in SQL
E/R diagrams
Have seen
this too
Tables:
column names: attributes
rows: tuples
Complex
file organization
and index
structures.
Discuss next
10
Terminology
Table name or relation name
Products:
Name
Category
Manufacturer
$19.99
gadgets
GizmoWorks
Power gizmo $29.99
gadgets
GizmoWorks
SingleTouch $149.99
photography
Canon
MultiTouch
household
Hitachi
gizmo
Price
Attribute names
$203.99
Tuples or rows or records
11
Schemas
Relational Schema:
– Relation name plus attribute names
– E.g. Product(Name, Price, Category, Manufacturer)
– In practice we add the domain for each attribute
Database Schema
– Set of relational schemas
– E.g. Product(Name, Price, Category, Manufacturer),
Company(Name, Address, Phone),
.......
This is all mathematics, not to be confused with SQL tables !12
Instances
• Relational schema = R(A1,…,Ak):
Instance = relation with k attributes (of “type” R)
– values of corresponding domains
• Database schema = R1(…), R2(…), …, Rn(…)
Instance = n relations, of types R1, R2, ..., Rn
13
Example
Relational schema:Product(Name, Price, Category, Manufacturer)
Instance:
Name
Price
Category
Manufacturer
gizmo
$19.99
gadgets
GizmoWorks
Power gizmo $29.99
gadgets
GizmoWorks
SingleTouch $149.99
photography
Canon
MultiTouch
household
Hitachi
$203.99
14
First Normal Form (1NF)
• A database schema is in First Normal Form
Student
if all tables are flat
Student
Name
GPA
Courses
Math
Alice
Bob
Carol
3.8
3.7
3.9
Name
GPA
Alice
3.8
Bob
3.7
Carol
3.9
DB
Takes
OS
Student
Course
Alice
Math
Course
Carol
Math
Math
Alice
DB
DB
Bob
DB
OS
Alice
OS
Carol
OS
DB
Course
OS
Math
OS
May need
to add keys
15
Functional Dependencies
• A form of constraint
– hence, part of the schema
• Finding them is part of the database design
• Also used in normalizing the relations
16
Functional Dependencies
• Warning: this is the most abstract, and “hardest”
part of the course.
• This chapter in the book (3.4) is not very good
• COME TO CLASS, PAY ATTENTION, READ
THE LECTURE NOTES !
17
Functional Dependencies
Definition:
If two tuples agree on the attributes
A1, A2, …, An
then they must also agree on the attributes
B1, B2, …, Bm
Formally:
A1, A2, …, An  B1, B2, …, Bm
18
Typical Examples of FDs
Product:
name  price, manufacturer
Person:
ssn  name, age
zip  city
city, state  zip
19
Examples
Formally, an FD holds, or does not hold on an instance:
EmpID
E0045
E1847
E1111
E9999
Name
Smith
John
Smith
Mary
Phone
1234
9876
9876
1234
Position
Clerk
Salesrep
Salesrep
Lawyer
EmpID  Name, Phone, Position
Position  Phone
but not Phone  Position
20
In General
• To check A  B, erase all other columns
… A … B
X1
Y1
X2
Y2
…
…
• check if the remaining relation is many-one
(called functional in mathematics)
Note: this is the mathematical definition of a function.
Book is wrong.
21
Example
EmpID
E0045
E1847
E1111
E9999
Name
Smith
John
Smith
Mary
Phone
1234
9876
9876
1234
Position
Clerk
Salesrep
Salesrep
Lawyer
22
Example
FD’s are constraints:
• On some instances they hold
• On others they don’t
name  color
category  department
color, category  price
name
category
color
department
price
Gizmo
Gadget
Green
Toys
49
Tweaker
Gadget
Green
Toys
99
Does this instance satisfy all the FDs ?
23
Example
name  color
category  department
color, category  price
name
category
color
department
price
Gizmo
Gadget
Green
Toys
49
Tweaker
Gadget
Black
Toys
99
Gizmo
Stationary
Green
Office-supp.
59
What about this one ?
24
An Interesting Observation
If all these FDs are true:
name  color
category  department
color, category  price
Then this FD also holds:
name, category  price
Why ??
25
Inference Rules for FD’s
A1, A2, …, An  B1, B2, …, Bm
Splitting rule
and
Combing rule
Is equivalent to
A1, A2, …, An  B1
A1, A2, …, An  B2
.....
A1, A2, …, An  Bm
A1
...
Am
B1
...
Bm
26
Inference Rules for FD’s
(continued)
Trivial Rule
A1, A2, …, An  Ai
where i = 1, 2, ..., n
A1
…
Am
Why ?
27
Inference Rules for FD’s
(continued)
Transitive Closure Rule
If
A1, A2, …, An  B1, B2, …, Bm
and
B1, B2, …, Bm  C1, C2, …, Cp
then
A1, A2, …, An  C1, C2, …, Cp
Why ?
28
A1
…
Am
B1
…
Bm
C1
...
Cp
29
Example (continued)
Start from the following FDs:
1. name  color
2. category  department
3. color, category  price
Infer the following FDs:
Inferred FD
Which Rule
did we apply ?
4. name, category  name
5. name, category  color
6. name, category  category
7. name, category  color, category
8. name, category  price
30
Example (continued)
Answers:
1. name  color
2. category  department
3. color, category  price
Inferred FD
Which Rule
did we apply ?
4. name, category  name
5. name, category  color
6. name, category  category
Trivial rule
Transitivity on 4, 1
Trivial rule
7. name, category  color, category Split/combine on 5, 6
8. name, category  price
Transitivity on 3, 7
THIS IS TOO HARD ! Let’s see an easier way.
31
Closure of a set of Attributes
Given a set of attributes A1, …, An
The closure, {A1, …, An}+ , is the set of attributes B
s.t. A1, …, An  B
Example:
name  color
category  department
color, category  price
Closures:
name+ = {name, color}
{name, category}+ = {name, category, color, department, price}
color+ = {color}
32
Closure Algorithm
Start with X={A1, …, An}.
Example:
Repeat until X doesn’t change do:
name  color
category  department
color, category  price
B1, …, Bn  C is a FD and
B1, …, Bn are all in X
then add C to X.
if
{name, category}+ =
{ name, category, color, department, price }
Hence:
name, category  color, department, price
33
Another Example
• Enrollment(student, major, course, room, time)
student  major
major, course  room
course  time
What else can we infer ? [in class, or at home]
34
Example
In class:
R(A,B,C,D,E,F)
A, B 
A, D 
B

A, F 
C
E
D
B
Compute {A,B}+
X = {A, B,
}
Compute {A, F}+
X = {A, F,
}
35
Using Closure to Infer ALL FDs
Example:
A, B  C
A, D  B
B
 D
Step 1: Compute X+, for every X:
A+ = A, B+ = BD, C+ = C, D+ = D
AB+ = ABCD, AC+ = AC, AD+ = ABCD
ABC+ = ABD+ = ACD+ = ABCD (no need to compute– why ?)
BCD+ = BCD, ABCD+ = ABCD
Step 2: Enumerate all FD’s X  Y, s.t. Y  X+ and XY = :
AB  CD, ADBC, ABC  D, ABD  C, ACD  B
36