Lossless Decomposition

1
Lossless Decomposition

• By Chi-Shu Ho
• For CS157A
• Prof. Sin-Min Lee

2
Decomposition

• 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

3
Decompositions 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
• And
• R1 is the projection of R on B1 , B2 ,Bm
• R2 is the projection of R on C1 ,C2 ,Ci

4
Problems with Decomposition

• Some queries become more expensive
• Given instances of the decomposed relations, we
  may not be able to reconstruct the corresponding
  instance of the original relation information
  loss.

5
Certain Decomposition May Cause Problem
R
R1
R2
6
R
R
7
Lossy Decomposition
T
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
8
Lossy Decomposition
Decomposition of the previous relation
T1
T2
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
9
Lossy Decomposition
After Natural Join
Original Relation
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
The result is different from the original
relation the information can not be
reconstructed.
10
Lossless 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

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

12
Lossless 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 n Y (X intersect Y) ? X, that is all
  attributes common to both X and Y functionally
  determine ALL the attributes in X OR
• X n Y (X intersect Y) ? Y, that is all
  attributes common to both X and Y functionally
  determine ALL the attributes in Y

13
Armstrongs Axioms

• X, Y, Z are sets of attributes
• Reflexivity If X ? Y, then X ? Y
• Augmentation If X ? Y, then XZ ? YZ for
  any Z
• Transitivity If X ? Y and Y ? Z, then
• X ? Z
• Union If X ? Y and X ? Z, then X ? YZ
• Decomposition If X ? YZ, then X ? Y and
• X ? Z

14
Example 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)

15
Example of Lossless Decomposition

• SHOW THAT THE DECOMPOSITION IS A LOSSLESS
• DECOMPOSITION
• USE AUGMENTATION RULE ON FIRST FD TO OBTAIN
• BRANCHNAME ? BRANCHNAME ASSETS BRANCHCITY
• INTERSECTION OF BRANCHSCHEME AND BORROWSCHEME IS
  BRANCHNAME
• BRANCHNAME ? BRANCHSCHEME
• SO, INITIAL DECOMPOSITION IS A LOSSLESS

16
Example 2

• GIVEN
• BORROWSCHEME(BRANCHNAME, LOANNUMBER,
  CUSTOMERNAME, AMOUNT)
• REQUIRED FD'S
• LOANNUMBER ? AMOUNT BRANCHNAME
• DECOMPOSE LENDINGSCHEME INTO
• 1. LOAN-INFO-SCHEME(BRANCHNAME, LOANNUMBER,
  AMOUNT)
• 2. CUSTOMER-LOAN-SCHEME(LOANNUMBER, CUSTOMERNAME)

17
Example 2 (cont)

• SHOW THAT THE DECOMPOSITION IS A LOSSLESS
• DECOMPOSITION
• USE AUGMENTATION RULE ON FD TO OBTAIN
• LOANNUMBER ? LOANNUMBER AMOUNT
  BRANCHNAME
• INTERSECTION OF LOAN-INFO-SCHEME AND
  CUSTOMER-LOAN-SCHEME IS LOANNUMBER
• LOANNUMBER ? LOAN-INFO-SCHEME
• SO, INITIAL DECOMPOSITION IS A LOSSLESS

18
(No Transcript)
19
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
20
Example (cont)
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)
21
Example (cont)
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)
22
Example (cont)
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)
23
Example (cont)
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)
24
Example (cont)
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)
25
Conclusions

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