Transcript Multivalued Dependencies

Multivalued Dependencies
Intuition
name
street
city
title
year
C. Fisher
123 Map St.
Hollywood
Star Wars
1977
C. Fisher
5 Locust Ln.
Malibu
Star Wars
1977
C. Fisher
123 Map St.
Hollywood
Empire Strikes Back
1980
C. Fisher
5 Locust Ln.
Malibu
Empire Strikes Back
1980
C. Fisher
123 Map St.
Hollywood
Return of the Jedi
1983
C. Fisher
5 Locust Ln.
Malibu
Return of the Jedi
1983
An instance of the Stars_in relation with address (street, city)
• Redundancy: addresses, title repeated several times
– because a star might have several addresses and stars in several
movies
– yet, the relation is in BCNF (no nontrivial functional dependency)
• Reason: address and name are independent on each other
Intuition
Can we reduce Stars_in to the following relation?
name
street
city
title
year
C. Fisher
123 Map St.
Hollywood
Star Wars
1977
C. Fisher
5 Locust Ln.
Malibu
Empire Strikes Back
1980
What are the addresses of C. Fisher when he stars in Star Wars?
How to avoid redundancy, yet still get the correct answer?
DECOMPOSITION
name
street
city
name
title
year
C. Fisher
123 Map St.
Hollywood
C. Fisher
Star Wars
1977
C. Fisher
5 Locust Ln.
Malibu
C. Fisher
Empire Strikes Back
1980
WHAT IS THE GROUND FOR THIS DECOMPOSITION?
Multivalued Dependencies (MVD)
A multivalued dependency is a statement about the
fact that a set of attributes is independent from
another set of attributes.
Notation: A1A2…An   B1B2…Bm
Meaning: for each pair of tuples t and u of R that
agree on the A’s, we can find in R some tuple v
that agrees:
1. With both t and u on the A’s
2. With t on the B’s
3. With u on the attributes that are not A’s or B’s
Illustration
If
A1A2…An   B1B2…Bm
then for each pair t and u
that agree on A’s
we will find v such that
v agrees with
1. t and u on A’s (green)
2. t on B’s (blue)
3. u on all others (red)
A’s
B’s
t
u
v
MVD in picture
Others
Example
name   street city is a MVD of ‘Stars_in’ with address (street, city)
(the A’s: name, the B’s {street, city}, others {title, year})
name
t  C. Fisher
u  C. Fisher
street
city
title
year
123 Map St.
Hollywood
Star Wars
1977
5 Locust Ln.
Malibu
Empire Strikes Back
1980
Because of t and u, the relation needs to contain the following tuple
v  C. Fisher
123 Map St.
Hollywood
Empire Strikes Back
1980
Switch t and u, we can conclude that the relation also needs to contain the
following tuple:
v’  C. Fisher
5 Locust Ln.
Malibu
We can then reconstruct the first relation!
Star Wars
1977
Nontrivial MVD
• A MVD of relation R A1…An   B1…Bm
is nontrivial if
– None of the B’s is among the A’s
– Not all attributes of R belong to
{A1,…,An}{B1,…,Bm}
Reasoning about MVD
• Trivial dependencies rule:
– if A1…An   B1…Bm then
A1…An   C1…Ck where the C’s are the B’s plus
some of the A’s
– if A1…An   B1…Bm then
A1…An   D1…Dr where the D’s are those B’s that
are not among the A’s
• Transitive rule: if A1…An   B1…Bm and
B1…Bm   C1…Ck then A1…An   D1…Dl
where {D1,…,Dl} = {C1,…,Ck } \ {B1,…,Bm}
Reasoning about MVD
• If A1…An  B1…Bm then A1…An B1…Bm
• If A1…An   B1…Bm then
A1…An   C1…Ck where the C’s are all
other attributes other than A’s and B’s
(Complementation rule)
• NOTE: splitting/combining rules do not hold.
Fourth Normal Form (4NF)
• A relation R is in fourth normal form (4NF)
if whenever there is a nontrivial MVD
A1…An   B1…Bm, then {A1,…,An} is a
superkey.
Decomposition into 4NF
• If R is not in 4NF then there exists a MVD
A1…An   B1…Bm that violates the 4NF
condition, we decompose R into 2
relations with the following schemas:
– {A1,…,An,B1,…,Bm}
– {A1,…,An,C1,…,Ck} where {C1,…,Ck} are those
attributes of R that are not among the A’s and
the B’s.
Example
name
street
city
title
year
C. Fisher
123 Map St.
Hollywood
Star Wars
1977
C. Fisher
5 Locust Ln.
Malibu
Empire Strikes Back
1980
DECOMPOSITION
name
street
city
name
title
year
C. Fisher
123 Map St.
Hollywood
C. Fisher
Star Wars
1977
C. Fisher
5 Locust Ln.
Malibu
C. Fisher
Empire Strikes Back
1980
WHAT IS THE GROUND FOR THIS DECOMPOSITION?
ANSWER: Because the MVD name   street city
Relationship among Normal Forms
• 4NF  BCNF  3NF
• Properties of NF and their decomposition
Property
3NF
4NF
BCNF
Eliminates redundancy due to FD’s
Most
Yes
Yes
Eliminates redundancy due to MVD’s
No
Yes
No
Preserves FD’s
Yes
Maybe
Maybe
Preserves MVD’s
Maybe
Maybe
Maybe
Homework 4 (Due Oct. 8)
3.6.1 For each of the following relation schemas and sets of
FDs:
a. R(A,B,C,D) with FDs ABC, CD, DA
b. R(A,B,C,D) with FDs BC, BD
c. R(A,B,C,D) with FDs ABC, BCD, CDA, ADB
do the following:
i.
Indicate all the BCNF violations. Decompose the relations,
as necessary, into collections of BCNF relations. (10pt)
ii. Indicate all the 3NF violations. Decompose the relations ,
as necessary, into collections of 3NF relations. (10pt)
Homework 4 (Due Oct. 8)
3.7.2 For each of the following relation schema
and dependencies:
a. R(A,B,C,D) with MVDs A C, A  B
b. R(A,B,C,D) with MVDs A B, B CD
c. R(A,B,C,D) with MVDs AB C and FD
BD
do the following:
i. Indicate all the 4NF violations.
ii. Decompose the relations, as necessary, into
collections of 4NF relations. (10pt)
Text for next class
• New edition (DBS the complete book):
Chapter 4
• Old edition (First course in DBS):
– Section 2.1
– Section 2.3.4
– Section 2.4.1, 2.4.2
– Section 2.5.2
– Section 3.2 (ALL)