Document Sample

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 XZ 4. Union: If X Y and X Z, then X YZ 5. Decomposition: If X YZ, then X Y and XZ 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 |

OTHER DOCS BY ert554898

How are you planning on using Docstoc?
BUSINESS
PERSONAL

By registering with docstoc.com you agree to our
privacy policy and
terms of service, and to receive content and offer notifications.

Docstoc is the premier online destination to start and grow small businesses. It hosts the best quality and widest selection of professional documents (over 20 million) and resources including expert videos, articles and productivity tools to make every small business better.

Search or Browse for any specific document or resource you need for your business. Or explore our curated resources for Starting a Business, Growing a Business or for Professional Development.

Feel free to Contact Us with any questions you might have.