Decomposition

1 / 17

# Decomposition - PowerPoint PPT Presentation

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.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about 'Decomposition' - niveditha

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

### 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.
• 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.

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.
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.

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
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.

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.

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,

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

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

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

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.

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.
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.
Dependency Preservation Cont.
• A decomposition having the property that

is a dependency-preserving decomposition.

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.
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.

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