Docstoc

Lossless Decomposition

Document Sample
Lossless Decomposition Powered By Docstoc
					Lossless Decomposition




       By Chi-Shu Ho
        For CS157A
      Prof. Sin-Min Lee
              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
   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
    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.
Certain Decomposition May Cause Problem

           R         Name         Price        Category
                     Gizmo        19.99         Gadget
                    OneClick      24.99         Camera
                   DoubleClick    29.99         Camera




R1     Name            Category           R2      Price   Category
       Gizmo            Gadget                    19.99    Gadget
      OneClick          Camera                    24.99    Camera
     DoubleClick        Camera                    29.99    Camera
R’     Name        Price   Category
       Gizmo       19.99    Gadget
      OneClick     24.99    Camera
      OneClick     29.99    Camera
     DoubleClick   29.99    Camera
     DoubleClick   29.99    Camera




 R     Name        Price   Category
       Gizmo       19.99    Gadget
      OneClick     24.99    Camera
     DoubleClick   29.99    Camera
       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
          Lossy Decomposition
     Decomposition of the previous relation

          T1                          T2
Employee        Branch      Project        Branch
Brown          L.A        Mars         L.A.
Green          San Jose   Jupiter      San Jose
Hoskins        San Jose   Saturn       San Jose
                          Venus        San Jose
                       Lossy Decomposition
        After Natural Join                 Original Relation
Employee       Project       Branch    Employee     Project     Branch
Brown        Mars        L.A.         Brown       Mars         L.A.
Green        Jupiter     San Jose     Green       Jupiter      San Jose
Green        Venus       San Jose     Green       Venus        San Jose
Hoskins      Saturn      San Jose     Hoskins     Saturn       San Jose
Hoskins      Venus       San Jose     Hoskins     Venus        San Jose
Green        Saturn      San Jose
Hoskins      Jupiter     San Jose

 The result is different from the original relation: the information
 can not be reconstructed.
       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
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 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 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 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 BRANCHNAME
 3.   BRANCHNAME  BRANCHSCHEME
 4.   SO, INITIAL DECOMPOSITION IS A LOSSLESS
             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)
           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
            Example (con’t)

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

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:7
posted:5/3/2012
language:
pages:25