This preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentThis preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentThis preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentThis preview has intentionally blurred sections. Sign up to view the full version.
View Full Document
Unformatted text preview: Multivalued Dependencies
Fourth Normal Form Reasoning About FD’s + MVD’s 1 Definition of MVD
x A multivalued dependency (MVD) on R, X >>Y , says that if two tuples of R agree on all the attributes of X, then their components in Y may be swapped, and the result will be two tuples that are also in the relation. x i.e., for each value of X, the values of Y are independent of the values of RXY.
2 Example: MVD
Drinkers(name, addr, phones, beersLiked) x A drinker’s phones are independent of the beers they like. x Thus, each of a drinker’s phones appears with each of the beers they like in all combinations. x This repetition is unlike FD redundancy.
name>addr is the only FD.
3 name>>phones and name >>beersLiked. Tuples Implied by name>>phones
If we have tuples: name sue sue sue sue addr a a a a phones beersLiked p1 b1 p2 b2 p2 b1 p1 b2 Then these tuples must also be in the relation. 4 Picture of MVD X >>Y
X equal exchange Y others 5 MVD Rules
x Every FD is an MVD (promotion ).
If X >Y, then swapping Y ’s between two tuples that agree on X doesn’t change the tuples. Therefore, the “new” tuples are surely in the relation, and we know X >>Y. x Complementation : If X >>Y, and Z is all the other attributes, then X >>Z.
6 Splitting Doesn’t Hold
x Like FD’s, we cannot generally split the left side of an MVD. x But unlike FD’s, we cannot split the right side either sometimes you have to leave several attributes on the right side. 7 Example: Multiattribute Right Sides
Drinkers(name, areaCode, phone, beersLiked, manf) x A drinker can have several phones, with the number divided between areaCode and phone (last 7 digits). x A drinker can like several beers, each with its own manufacturer.
8 Example Continued
x Since the areaCodephone combinations for a drinker are independent of the beersLikedmanf combinations, we expect that the following MVD’s hold: name >> areaCode phone name >> beersLiked manf
9 Example Data
Here is possible data satisfying these MVD’s: name Sue Sue Sue Sue areaCode 650 650 415 415 phone 5551111 5551111 5559999 5559999 beersLiked Bud WickedAle Bud WickedAle manf A.B. Pete’s A.B. Pete’s But we cannot swap area codes or phones by themselves. That is, neither name>>areaCode nor name>>phone holds for this relation.
10 Fourth Normal Form
x The redundancy that comes from MVD’s is not removable by putting the database schema in BCNF. x There is a stronger normal form, called 4NF, that (intuitively) treats MVD’s as FD’s when it comes to decomposition, but not when determining keys of the relation.
11 4NF Definition
x A relation R is in 4NF if: whenever X >>Y is a nontrivial MVD, then X is a superkey.
1. Y is not a subset of X, and 2. X and Y are not, together, all the attributes. Nontrivial MVD means that: Note that the definition of “superkey” still depends on FD’s only.
12 BCNF Versus 4NF
x Remember that every FD X >Y is also an MVD, X >>Y. x Thus, if R is in 4NF, it is certainly in BCNF. x But R could be in BCNF and not 4NF, because MVD’s are “invisible” to BCNF.
13 Because any BCNF violation is a 4NF violation (after conversion to an MVD). Decomposition and 4NF
x If X >>Y is a 4NF violation for relation R, we can decompose R using the same technique as for BCNF. 1. XY is one of the decomposed relations. 2. All but Y – X is the other. 14 Example: 4NF Decomposition
Drinkers(name, addr, phones, beersLiked) FD: name > addr MVD’s: name >> phones name >> beersLiked x Key is {name, phones, beersLiked}. x All dependencies violate 4NF.
15 Example Continued
x Decompose using name > addr: 1. Drinkers1(name, addr) 2. Drinkers2(name, phones, beersLiked) x In 4NF; only dependency is name > addr. x Not in 4NF. MVD’s name >> phones and name >> beersLiked apply. No FD’s, so all three attributes form the key.
16 Example: Decompose Drinkers2
x Either MVD name >> phones or name >> beersLiked tells us to decompose to:
Drinkers3(name, phones) Drinkers4(name, beersLiked) 17 Reasoning About MVD’s + FD’s
x Problem: given a set of MVD’s and/or FD’s that hold for a relation R, does a certain FD or MVD also hold in R ? x Solution: Use a tableau to explore all inferences from the given set, to see if you can prove the target dependency. 18 Why Do We Care?
1. 4NF technically requires an MVD violation.
2. When we decompose, we need to project FD’s + MVD’s. Need to infer MVD’s from given FD’s and MVD’s that may not be violations themselves. 19 Example: Chasing a Tableau With MVD’s and FD’s
x To apply a FD, equate symbols, as before. x To apply an MVD, generate one or both of the tuples we know must also be in the relation represented by the tableau. x We’ll prove: if A>>BC and D>C, then A>C.
20 The Tableau for A>C
Goal: prove that c1 = c2. A a a a B b1 b2 b2 C c1 c2 c2 c2 D d1 d2 d1 Use A>>BC (first row’s D with second row’s BC ). Use D>C (first and third row agree on D, 21 therefore agree on C ). Example: Transitive Law for MVD’s
x If A>>B and B>>C, then A>>C.
Obvious from the complementation rule if the Schema is ABC. But it holds no matter what the schema; we’ll assume ABCD. 22 The Tableau for A>>C
Goal: derive tuple (a,b1,c2,d1). A a a a a B b1 b2 b1 b1 C c1 c2 c2 c2 D d1 d2 d2 d1 Use A>>B to swap B from the first row into the second. Use B>>C to swap C from the third row into the first.3 2 Rules for Inferring MVD’s + FD’s
x Start with a tableau of two rows.
These rows agree on the attributes of the left side of the dependency to be inferred. And they disagree on all other attributes. Use unsubscripted variables where they agree, subscripts where they disagree. 24 Inference: Applying a FD
x Apply a FD X>Y by finding rows that agree on all attributes of X. Force the rows to agree on all attributes of Y. Replace one variable by the other. If the replaced variable is part of the goal tuple, replace it there too. 25 Inference: Applying a MVD
x Apply a MVD X>>Y by finding two rows that agree in X. Add to the tableau one or both rows that are formed by swapping the Ycomponents of these two rows. 26 Inference: Goals
x To test whether U>V holds, we succeed by inferring that the two variables in each column of V are actually the same. x If we are testing U>>V, we succeed if we infer in the tableau a row that is the original two rows with the components of V swapped.
27 Inference: Endgame
x Apply all the given FD’s and MVD’s until we cannot change the tableau. x If we meet the goal, then the dependency is inferred. x If not, then the final tableau is a counterexample relation.
Satisfies all given dependencies. Original two rows violate target dependency.
28 ...
View
Full
Document
This note was uploaded on 02/21/2011 for the course CS 147 taught by Professor Dr.jeff during the Fall '07 term at SUNY Buffalo.
 Fall '07
 Dr.Jeff

Click to edit the document details