1 / 17

Decomposition

Decomposition. Liangsheng Deng Section 2 11/15/2005. Recall Functional Dependencies. A->B holds iff for any pairs of tuples t1 and t2 such that if t1[A] = t2[A], then t1[B] = t2[B]. Armstong’s axioms: Reflexivity rule. If a is a set of attributes and b is a subset of a, then a->b holds.

niveditha
Télécharger la présentation

Decomposition

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Decomposition Liangsheng Deng Section 2 11/15/2005

  2. Recall Functional Dependencies • A->B holds iff for any pairs of tuples t1 and t2 such that if t1[A] = t2[A], then t1[B] = t2[B]. • Armstong’s axioms: • Reflexivity rule. If a is a set of attributes and b is a subset of a, then a->b holds. • Augmentation rule. If a->b holds and c is a set of attributes, then ac->bc holds. • Transitivity rule. If a->b holds and b->c holds, then a->c holds.

  3. Closure of a Set of FD • Given f, a set of FD of a relational schema, we can use Armstrong axioms to derive other possible FD implied by f. • The closure of f is the set of all FD implied by f. • Determine whether S, a set of attributes, is a superkey by computing the closure of S.

  4. Closure Computing Example • Given R(A, B, C, D, E, H) with FD1. BD->A, FD2. AC->H, FD3. D->C, FD4. E->D. Is BE a superkey of R? {BE}+ = {BDE} because E->D {BE}+ = {BCDE} because D->C {BE}+ = {ABCDE} because BD->A {BE}+ = {ABCDEH} because AC->H BE is a superkey of R.

  5. Lossless Decomposition The purpose of using lossless decomposition: • Reduce unnecessary redundancy. • Retrieve information efficiently • How do we decide a decomposition is a lossless decomposition? • Let R be a relation schema. • Let F be a set of functional dependencies on R. • Let R1 and R2 form a decomposition of R. • The decomposition is a lossless-join decomposition of R if at least one of the following functional dependencies holds. • R1 ∩ R2 -> R1 or • R2 ∩ R1 -> R2

  6. Lossless Decomposition Testing (1) • Given R1(A, B), R2(A, C, D) and F = {A->C; D->B; AC->B}. Is the join of R1 and R2 lossless? • R1 ∩ R2 = {A} If we can prove A->B, then the answer is yes. Proof: A->AC because A->C A->B because A->AC and AC->B So, it is a lossless cecomposition.

  7. Lossless Decomposition Testing (2) • To test whether R1 ∩ R2 -> R1 or R2, we can test whether R1 ∩ R2 is a superkey of R1 or R2 by computing the closure of R1 ∩ R2. • Same question in the previous slide. • {R1 ∩ R2}+ = {A}+, {A}+ = {AC} because A->C {A}+ = {ABC} because AC->B Conclusion: A is a superkey of R1. Therefore, it is a lossless decomposition.

  8. Lossless Decomposition Testing (3) • For the case of decomposition of a schema into more than 2 schemas, we can use the chase matrix algorithm. • For example, F = {A->C, B->C, C->D, DE->C, CE->A}, R1(AD), R2(AB), R3(BE), R4(CDE) and R5(AE). A B C D E R1(AD) a1 b12 b13 a4 b15 R2(AB) a1 a2 b23 b24 b25 R3(BE) b31 a2 b33 b34 a5 R4(CDE) b41 b42 a3 a4 a5 R5(AE) a1 b52 b53 b54 a5

  9. Lossless Decomposition Testing (3) Cont. A->C, we get A B C D E R1(AD) a1 b12 b13 a4 b15 R2(AB) a1 a2 b13 b24 b25 R3(BE) b31 a2 b33 b34 a5 R4(CDE) b41 b42 a3 a4 a5 R5(AE) a1 b52 b13 b54 a5 B->C, we get A B C D E R1(AD) a1 b12 b13 a4 b15 R2(AB) a1 a2 b13 b24 b25 R3(BE) b31 a2 b13 b34 a5 R4(CDE) b41 b42 a3 a4 a5 R5(AE) a1 b52 b13 b54 a5

  10. Lossless Decomposition Testing (3) Cont. C->D, we get A B C D E R1(AD) a1 b12 b13 a4 b15 R2(AB) a1 a2 b13 a4 b25 R3(BE) b31 a2 b13 a4 a5 R4(CDE) b41 b42 a3 a4 a5 R5(AE) a1 b52 b13 a4 a5 DE->C A B C D E R1(AD) a1 b12 b13 a4 b15 R2(AB) a1 a2 b13 a4 b25 R3(BE) b31 a2 a3 a4 a5 R4(CDE) b41 b42 a3 a4 a5 R5(AE) a1 b52 a3 a4 a5

  11. Lossless Decomposition Testing (3) Cont. CE->A A B C D E R1(AD) a1 b12 b13 a4 b15 R2(AB) a1 a2 b23 a4 b25 R3(BE) a1 a2 a3 a4 a5 R4(CDE) a1 b42 a3 a4 a5 R5(AE) a1 b52 a3 a4 a5 The third tuple has a1, a2, a3 ,a4 and a5, so it is a lossless decomposition.

  12. Dependency Preservation • Let F be a set of functional dependencies on schema R. • Let {R1,R2,…Rn} be a decomposition of R. • The restriction of F to Ri is the set of all functional dependencies in F+ that include only attributes of Ri. • Functional dependencies in a restriction can be tested in one relation, as they involve attributes in one relation schema.

  13. Dependency Preservation Cont. • The set of restrictions F1,F2,…Fn is the set of dependencies that can be checked efficiently. • We need to know whether testing only the restrictions is sufficient. • Let F’ = F1 U F2 U….Fn. • F’ is a set of functional dependencies on schema R, but in general, F’ != F.

  14. Dependency Preservation Cont. • A decomposition having the property that is a dependency-preserving decomposition.

  15. Dependency Preservation Cont. • An Easier Way To Test For Dependency Preservation. • Rather than compute F+ and F’+ , and see whether they are equal, we can do this: • Find F – F’, the functional dependencies not checkable in one relation. • See whether this set is obtainable from F' by using Armstrong's Axioms. • This should take a great deal less work, as we have (usually) just a few functional dependencies to work on.

  16. Dependency Preservation Cont. • For example, given F={AB->C, C->A}, R1(AC) and R2(BC). • F’ = {C->A}. F – F’ = {AB->C}. AB->C can not be derived from F’. So, it is not a dependency-preserving decomposition.

  17. Reference • http://www.cs.sfu.ca/CC/354/zaiane/material/notes/Chapter7/node1.html

More Related