Lossless DecompositionLossless Decomposition

By Chi-Shu Ho

For CS157A

Prof. Sin-Min Lee

DecompositionDecomposition

Goal: Eliminate redundancy by decomposing a relation into several relations in a higher normal form.

It is important to check that a decomposition does not introduce new problems.

-- A good decomposition allows us to recover the original relation

Decompositions in GeneralDecompositions in General

Let R be a relation with attributes A1 ,A2 ,…An

Create two relations R1 and R2 with attributes B1 , B2 ,…Bm C1 ,C2 ,…Ci

Such that:B1 , B2 ,…Bm ∪ C1 ,C2 ,…Ci = A1 ,A2 ,…AN

AndR1 is the projection of R on B1 , B2 ,…Bm

R2 is the projection of R on C1 ,C2 ,…Ci

Problems with DecompositionProblems with Decomposition

Some queries become more expensiveGiven instances of the decomposed

relations, we may not be able to reconstruct the corresponding instance of the original relation – information loss.

Certain Decomposition May Cause ProblemCertain Decomposition May Cause Problem

Name Price CategoryGizmo 19.99 Gadget

OneClick 24.99 CameraDoubleClick 29.99 Camera

Price Category

19.99 Gadget

24.99 Camera

29.99 Camera

Name Category

Gizmo Gadget

OneClick Camera

DoubleClick Camera

R

R1 R2

Name Price CategoryGizmo 19.99 Gadget

OneClick 24.99 CameraOneClick 29.99 Camera

DoubleClick 29.99 Camera

DoubleClick 29.99 Camera

R’

Name Price CategoryGizmo 19.99 Gadget

OneClick 24.99 CameraDoubleClick 29.99 Camera

R

Lossy Lossy DecompositionDecomposition

Employee Project Branch

Brown Mars L.A.

Green Jupiter San Jose

Green Venus San Jose

Hoskins Saturn San Jose

Hoskins Venus San Jose

Functional dependencies:

Employee Branch, Project Branch

T

Lossy Lossy DecompositionDecomposition

Decomposition of the previous relation

Employee Branch

Brown L.A

Green San Jose

Hoskins San Jose

Project Branch

Mars L.A.

Jupiter San Jose

Saturn San Jose

Venus San Jose

T1 T2

Lossy DecompositionLossy Decomposition

After Natural Join

Employee Project Branch

Brown Mars L.A.

Green Jupiter San Jose

Green Venus San Jose

Hoskins Saturn San Jose

Hoskins Venus San Jose

Green Saturn San Jose

Hoskins Jupiter San Jose

Employee Project Branch

Brown Mars L.A.

Green Jupiter San Jose

Green Venus San Jose

Hoskins Saturn San Jose

Hoskins Venus San Jose

Original Relation

The result is different from the original relation: the information can not be reconstructed.

Lossless DecompostionLossless Decompostion

A decomposition is lossless if we can recover:

R(A, B, C)

Decompose

R1(A, B) R2(A, C)

Recover

R’(A, B, C)

R’ = R

What is lossless decomposition?What is lossless decomposition?

The decomposition of a relation R on X1 and X2 is lossless if the join of the projections of R on X1 and X2 is equal to R itself (that is, not containing false tuples).

Lossless Decomposition PropertyLossless Decomposition Property

The decomposition of R into X and Y is lossless with respect to F if and only if the closure of F contains either:– X ∩ Y (X intersect Y) X, that is: all

attributes common to both X and Y functionally determine ALL the attributes in X OR

– X ∩ Y (X intersect Y) Y, that is: all attributes common to both X and Y functionally determine ALL the attributes in Y

Armstrong’s Axioms

X, Y, Z are sets of attributes

1. Reflexivity: If X Y, then X Y

2. Augmentation: If X Y, then XZ YZ for any Z

3. Transitivity: If X Y and Y Z, then X Z

4. Union: If X Y and X Z, then X YZ

5. Decomposition: If X YZ, then X Y and X Z

Example of Lossless DecompositionExample of Lossless Decomposition

GIVEN: LENDINGSCHEME=(BRANCHNAME, ASSETS, BRANCHCITY, LOANNUMBER, CUSTOMERNAME, AMOUNT)REQUIRED FD'S:

BRANCHNAME ASSETS BRANCHCITY LOANNUMBER AMOUNT BRANCHNAME

DECOMPOSE LENDINGSCHEME INTO:1. BRANCHSCHEME=(BRANCHNAME, ASSETS, BRANCHCITY)2. BORROWSCHEME=(BRANCHNAME, LOANNUMBER, CUSTOMERNAME, AMOUNT)

Example of Lossless DecompositionExample of Lossless Decomposition

SHOW THAT THE DECOMPOSITION IS A LOSSLESS DECOMPOSITION

1. USE AUGMENTATION RULE ON FIRST FD TO OBTAIN: BRANCHNAME BRANCHNAME ASSETS

BRANCHCITY 2. INTERSECTION OF BRANCHSCHEME AND

BORROWSCHEME IS BRANCHNAME3. BRANCHNAME BRANCHSCHEME4. SO, INITIAL DECOMPOSITION IS A LOSSLESS

Example 2Example 2

GIVEN: BORROWSCHEME=(BRANCHNAME, LOANNUMBER,

CUSTOMERNAME, AMOUNT) REQUIRED FD'S:

LOANNUMBER AMOUNT BRANCHNAMEDECOMPOSE LENDINGSCHEME INTO:

1. LOAN-INFO-SCHEME=(BRANCHNAME, LOANNUMBER, AMOUNT)

2. CUSTOMER-LOAN-SCHEME=(LOANNUMBER, CUSTOMERNAME)

Example 2 (con’t)Example 2 (con’t)

SHOW THAT THE DECOMPOSITION IS A LOSSLESS DECOMPOSITION

1. USE AUGMENTATION RULE ON FD TO OBTAIN: LOANNUMBER LOANNUMBER AMOUNT BRANCHNAME

2. INTERSECTION OF LOAN-INFO-SCHEME AND CUSTOMER-LOAN-SCHEME IS LOANNUMBER

3. LOANNUMBER LOAN-INFO-SCHEME 4. SO, INITIAL DECOMPOSITION IS A LOSSLESS

Example

R1 (A1, A2, A3, A5)

R2 (A1, A3, A4)

R3 (A4, A5)

FD1: A1 A3 A5

FD2: A5 A1 A4

FD3: A3 A4 A2

A1 A2 A3 A4 A5

R1 a(1) a(2) a(3) b(1,4) a(5)

R2 a(1) b(2,2) a(3) a(4) b(2,5)

R3 b(3,1) b(3,2) b(3,3) a(4) a(5)

Example (con’t)

By FD1: A1 A3 A5

A1 A2 A3 A4 A5

R1 a(1) a(2) a(3) b(1,4) a(5)

R2 a(1) b(2,2) a(3) a(4) b(2,5)

R3 b(3,1) b(3,2) b(3,3) a(4) a(5)

Example (con’t)

By FD1: A1 A3 A5

we have a new result table

A1 A2 A3 A4 A5

R1 a(1) a(2) a(3) b(1,4) a(5)

R2 a(1) b(2,2) a(3) a(4) a(5)

R3 b(3,1) b(3,2) b(3,3) a(4) a(5)

Example (con’t)

By FD2: A5 A1 A4

A1 A2 A3 A4 A5

R1 a(1) a(2) a(3) b(1,4) a(5)

R2 a(1) b(2,2) a(3) a(4) a(5)

R3 b(3,1) b(3,2) b(3,3) a(4) a(5)

Example (con’t)

By FD2: A5 A1 A4

we have a new result table

A1 A2 A3 A4 A5

R1 a(1) a(2) a(3) a(4) a(5)

R2 a(1) b(2,2) a(3) a(4) a(5)

R3 a(1) b(3,2) b(3,3) a(4) a(5)

Example (con’t)

ConclusionsConclusions

Decompositions should always be lossless:

-- Lossless decomposition ensure that the information in the original relation can be accurately reconstructed based on the information represented in the decomposed relations.