Dependency Preserving Decomposition

Dependency Preserving Decomposition DAVID DENG CS157B MARCH 23, 2010

Intro • Decomposition help us eliminate redundancy, root of data anomalies. • Decomposition should be: • 1. Lossless • 2. Dependency Preserving

What’s Dependency Preservation? • When the decomposition of a relational scheme preserved the associated set of functional dependencies. Formal Definition: If R is decomposed into R1, R2,…, Rn, then {F1F2…Fn}+ = F+

Algorithm to check for Dependency Preservation begin; for each X  Y in F and with R (R1, R2, …, Rn) { let Z = X; while there are changes in Z { from i=1 to n Z = Z  ((Z  Ri)+ Ri) w.r.t to F; } if Y is a proper subset of Z, current fd is preserved else decomposition is not dependency preserving; } this is a dependency preserving decomposition; end;

Explain Algorithm Part 1 1. Choose a functional dependency in set F, say you choose X  Y. 2. Let set Z to the “left hand side” of the functional dependency, X such Z = X Starting with R1 in the decomposed set {R1, R2,…Rn) 3. Intersect Z with R1, Z  R1 4. Find the closure of the result from step 3 (Z  R1) using original set F 5. Intersect the result from step 4 ((Z  R1)+) with R1 again.

Explain Algorithm Part 2 6. Updated Z with new attribute in the result from step 5. 7. Repeat step 3-6 from R2, R3, …, Rn. 8. If there’s any changes between original Z before step 3 and after step 7, repeat step 3-7. 9. Check whether Y is a proper subset of current Z. If it is not, this decomposition is a violation of dependency preservation. You can stop now. 10. If Y is a proper subset of current Z, repeat 1-9 until you check ALL functional dependencies in set F.

Another look at Algorithm Test each X  Y in F for dependency preservation result = X while (changes to result) do for each Ri in decomposition t = (result  Ri)+  Ri result = result  t if Y  result, return true; else, return false; [Note: If any false is returned for algorithm, whole decomposition is not dependency preserving.]

Let’s walk through an example of using this algorithm.

Example using Algorithm • Given the following: R(A,B,C,D,E) F = {ABC, CE, BD, EA} R1(B,C,D) R2(A,C,E) • Is this decomposition dependency preserving?

Example R(A,B,C,D,E) F = {ABC, CE, BD, EA} Decomposition: R1(B,C,D) R2(A,C,E) Z=AB For Z  R1 = AB  BCD = B {B}+ = BD {B}+ R1 = BD  BCD = BD Update Z = AB  BD = ABD, continue

Example R(A,B,C,D,E) F = {ABC, CE, BD, EA} Decomposition: R1(B,C,D) R2(A,C,E) Z=ABD For Z  R2 = ABD  ACE = A {A}+ = A {A}+ R2 = A  ACE = A Update Z, Z is still ABD Since Z changed, repeat checking R1 to R2.

Example R(A,B,C,D,E) F = {ABC, CE, BD, EA} Decomposition: R1(B,C,D) R2(A,C,E) Z=ABD For Z  R1 = ABD  BCD = BD {BD}+ = BD {BD}+ R1 = BD  BCD = BD Update Z = ABD  BD = ABD, so Z hasn’t changed but you still have to continue.

Example R(A,B,C,D,E) F = {ABC, CE, BD, EA} Decomposition: R1(B,C,D) R2(A,C,E) Z=ABD and checking R2 was done 2 slides ago Z will still be ABD. Since Z hasn’t change, you can conclude ABC is not preserved. Let’s practice with other functional dependencies.

Example R(A,B,C,D,E) F = {ABC, CE, BD, EA} Decomposition: R1(B,C,D) R2(A,C,E) Z=X=B For Z  R1 = B  BCD = B {B}+ = BD {B}+ R1 = BD  BCD = BD Update Z = B  BD = BD Since Y=D is proper subset of BD, BD is preserved.

Example R(A,B,C,D,E) F = {ABC, CE, BD, EA} Decomposition: R1(B,C,D) R2(A,C,E) Z=X=C For Z  R2 = C  ACE = C {C}+ = CEA {C}+ R1 = CEA  ACE = ACE Update Z = C ACE= ACE Since Y=E is proper subset of ACE, CE is preserved.

Example R(A,B,C,D,E) F = {ABC, CE, BD, EA} Decomposition: R1(B,C,D) R2(A,C,E) Z=X=E For Z  R1 = E  ACE = E {E}+ = EA {E}+ R1 = EA  ACE = EA Update Z = E  EA= EA Since Y=A is proper subset of EA, EA is preserved.

Example R(A,B,C,D,E) F = {ABC, CE, BD, EA} Decomposition: R1(B,C,D) R2(A,C,E) Shortcut: For any functional dependency, if both LHS and RHS collectively are within any of the sub scheme Ri. Then this functional dependency is preserved.

Exercise #1 Let R{A,B,C,D} and F={AB, BC, CD, DA} Let’s decomposed R into R1 = AB, R2 = BC, and R3 = CD Is this a dependency preserving decomposition?

Answer to Exercise #1 R{A,B,C,D} F={AB, BC, CD, DA} Decomposition: R1 = AB, R2 = BC, and R3 = CD Yes it is. You can immediately see that AB, BC, CD are preserved for R1, R2, R3 The key is to check whether DA is preserved. Let’s walk through the algorithm.

Answer to Exercise #1 R{A,B,C,D} F={AB, BC, CD, DA} Decomposition: R1 = AB, R2 = BC, and R3 = CD Z = X = D For Z  R1 = D  AB = empty set For Z  R2 = D  BC = empty set For Z  R3 = D  CD = D Find {D}+ = DABC Find {D}+ R3 = DABC  CD = CD Update Z to CD. Since Z changed, repeat.

Answer to Exercise #1 R{A,B,C,D} F={AB, BC, CD, DA} Decomposition: R1 = AB, R2 = BC, and R3 = CD Z = CD For Z  R1 = CD  AB = empty set For Z  R2 = CD  BC = C Find {C}+ = CDAB Find {C}+ R2 = CDAB  BC = BC Update Z = CD  BC = BCD

Answer to Exercise #1 R{A,B,C,D} F={AB, BC, CD, DA} Decomposition: R1 = AB, R2 = BC, and R3 = CD Z = BCD For Z  R3 = BCD  CD = CD Find {CD}+ = CDAB Find {CD}+  R3 = CDAB  CD = CD Update Z is still BCD. Since Z changed, repeat going trough R1 to R3.

Answer to Exercise #1 R{A,B,C,D} F={AB, BC, CD, DA} Decomposition: R1 = AB, R2 = BC, and R3 = CD Z = BCD For Z  R1 = BCD  AB = B Find {B}+ = BCDA Find {B}+ R1 = BCDA  AB = AB Update Z = BCD  AB = ABCD. Since Y = A is a subset of ABCD, function DA is preserved.

Exercise #2 R{A,B,C,D,E) F={ABD, BE} Decomposition: R1{A,B,C} R2{A,D} R3{B,D,E} Is this a dependency preserving decomposition?

Answer to Exercise #2 R{A,B,C,D,E) F={ABD, BE} Decomposition: R1{A,B,C} R2{A,D} R3{B,D,E} Let’s start with ABD: Z = A Z  R1 = A  ABC = A {A}+ = ABDE {A}+  R1 = ABDE  ABC = AB Update Z = A  AB = AB

Answer to Exercise #2 R{A,B,C,D,E) F={ABD, BE} Decomposition: R1{A,B,C} R2{A,D} R3{B,D,E} Z = AB Z  R2 = A  AD = A {A}+ = ABDE {A}+  R1 = ABDE  AD = AD Update Z = AB  AD = ABD Thus A BD preserved

Answer to Exercise #2 R{A,B,C,D,E) F={ABD, BE} Decomposition: R1{A,B,C} R2{A,D} R3{B,D,E} Based on R3, BE is preserved. Check B  E: Z = B Z  R1 = B  ABC = B {B}+ = BE {B}+  R1 = BE  ABC = B Update Z = B still the same

Answer to Exercise #2 R{A,B,C,D,E) F={ABD, BE} Decomposition: R1{A,B,C} R2{A,D} R3{B,D,E} Z = B Z  R2 = B  AD = empty set Z  R3 = B  BDE = B {B}+ = BE {B}+  R3 = BE  BDE = BE Update Z = B  BE = BE Thus BE preserved

End Reference: • Yu Hung Chen, “Decomposition”, http://www.cs.sjsu.edu/faculty/lee/cs157/Decomposition_YuHungChen.ppt, SJSU (lol), 2005 • Gary D. Boetticher, “Preserving Dependencies”, http://www.youtube.com/watch?v=olgwucI3thI, University of Houston Clear Lake, 2009 • Dr. C. C. Chan, “Example of Dependency Preserving Decomposition”, http://www.cs.uakron.edu/~chan/cs475/Fall2000/ExampleDp%20decomposition.htm, University of Akron, Fall 2000 • Tang Nan, “Functional Dependency”, http://www.se.cuhk.edu.hk/~seg3550/2006/tutorial/t9.ppt

Dependency Preserving Decomposition