Functional Dependencies and Lossless Decomposition in Database Systems, Slides of Database Management Systems (DBMS)

Download Functional Dependencies and Lossless Decomposition in Database Systems and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Decomposition

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

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,

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 b R2(AB) a1 a2 b23 b24 b R3(BE) b31 a2 b33 b34 a R4(CDE) b41 b42 a3 a4 a R5(AE) a1 b52 b53 b54 a

Lossless Decomposition Testing (3) Cont. C->D, we get

A B C D E

R1(AD) a1 b12 b13 a4 b R2(AB) a1 a2 b13 a4 b R3(BE) b31 a2 b13 a4 a R4(CDE) b41 b42 a3 a4 a R5(AE) a1 b52 b13 a4 a DE->C A B C D E R1(AD) a1 b12 b13 a4 b R2(AB) a1 a2 b13 a4 b R3(BE) b31 a2 a3 a4 a R4(CDE) b41 b42 a3 a4 a R5(AE) a1 b52 a3 a4 a

Lossless Decomposition Testing (3) Cont. CE->A A B C D E R1(AD) a1 b12 b13 a4 b R2(AB) a1 a2 b23 a4 b R3(BE) a1 a2 a3 a4 a R4(CDE) a1 b42 a3 a4 a R5(AE) a1 b52 a3 a4 a

The third tuple has a1, a2, a3 ,a4 and a5, so

it is a lossless decomposition.

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.

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