lossless decomposition
DESCRIPTION
Lossless Decomposition. Elias Aseged SE 157B - DB 2. What is Decomposition?. Decomposition – the process of breaking down in parts or elements. Decomposition in database means breaking tables down into multiple tables From Database perspective means going to a higher normal form. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Lossless Decomposition](https://reader035.vdocuments.us/reader035/viewer/2022062309/56815adc550346895dc8a7c5/html5/thumbnails/1.jpg)
Lossless Decomposition
Elias AsegedSE 157B - DB 2
![Page 2: Lossless Decomposition](https://reader035.vdocuments.us/reader035/viewer/2022062309/56815adc550346895dc8a7c5/html5/thumbnails/2.jpg)
What is Decomposition?
• Decomposition – the process of breaking down in parts or elements.
• Decomposition in database means breaking tables down into multiple tables
• From Database perspective means going to a higher normal form
![Page 3: Lossless Decomposition](https://reader035.vdocuments.us/reader035/viewer/2022062309/56815adc550346895dc8a7c5/html5/thumbnails/3.jpg)
Decomposition
Important that decompositions are “good”,
Two Characteristics of Good Decompositions•1) Lossless•2) Preserve dependencies
![Page 4: Lossless Decomposition](https://reader035.vdocuments.us/reader035/viewer/2022062309/56815adc550346895dc8a7c5/html5/thumbnails/4.jpg)
What is lossless?
Lossless means functioning without a loss.In other words, retain everything.
Important for databases to have this feature.
![Page 5: Lossless Decomposition](https://reader035.vdocuments.us/reader035/viewer/2022062309/56815adc550346895dc8a7c5/html5/thumbnails/5.jpg)
Formal Definition
• Let R be a relation schema.• Let F be a set of functional dependencies on R.• Let and form a decomposition of R.• The decomposition is a lossless-join
decomposition of R if at least one of the following functional dependencies are in F+
1) R1 ∩ R2 R12) R1 ∩ R2 R2
![Page 6: Lossless Decomposition](https://reader035.vdocuments.us/reader035/viewer/2022062309/56815adc550346895dc8a7c5/html5/thumbnails/6.jpg)
In Simpler Terms…
• R1 ∩ R2 R1• R1 ∩ R2 R2
If R is split into R1 and R2, for the decomposition to be lossless then at least one of the two should hold true.
Projecting on R1 and R2, and joining back, results in the relation you started with
![Page 7: Lossless Decomposition](https://reader035.vdocuments.us/reader035/viewer/2022062309/56815adc550346895dc8a7c5/html5/thumbnails/7.jpg)
Why lossless?
Ensures that attributes involved in the natural join (R1 ∩ R2) are a candidate key for at least one of the two relations.
This ensures we can never get the situation where false tuples are generated, as for any value on the join attributes there will be a unique tuple in one of the relations.
![Page 8: Lossless Decomposition](https://reader035.vdocuments.us/reader035/viewer/2022062309/56815adc550346895dc8a7c5/html5/thumbnails/8.jpg)
A decomposition is lossless if we can recover: R(A,B,C)
R1(A,B) R2(A,C)
R’(A,B,C) should be the same as R(A,B,C)
Must ensure R’ = R
Decompose
Recover
Lossless Decomposition
![Page 9: Lossless Decomposition](https://reader035.vdocuments.us/reader035/viewer/2022062309/56815adc550346895dc8a7c5/html5/thumbnails/9.jpg)
Lossless Decomposition• Sometimes the same set of data is reproduced:
• (Word, 100) + (Word, WP) (Word, 100, WP)• (Oracle, 1000) + (Oracle, DB) (Oracle, 1000, DB)• (Access, 100) + (Access, DB) (Access, 100, DB)
Name Price CategoryWord 100 WP
Oracle 1000 DB
Access 100 DB
Name PriceWord 100
Oracle 1000
Access 100
Name CategoryWord WP
Oracle DB
Access DB
![Page 10: Lossless Decomposition](https://reader035.vdocuments.us/reader035/viewer/2022062309/56815adc550346895dc8a7c5/html5/thumbnails/10.jpg)
Lossy Decomposition• Sometimes it’s not:
• (Word, WP) + (100, WP) = (Word, 100, WP)• (Oracle, DB) + (1000, DB) = (Oracle, 1000, DB)• (Oracle, DB) + (100, DB) = (Oracle, 100, DB)• (Access, DB) + (1000, DB) = (Access, 1000, DB)• (Access, DB) + (100, DB) = (Access, 100, DB)
Name Price CategoryWord 100 WP
Oracle 1000 DB
Access 100 DB
Category NameWP Word
DB Oracle
DB Access
Category PriceWP 100
DB 1000
DB 100
What’swrong?
![Page 11: Lossless Decomposition](https://reader035.vdocuments.us/reader035/viewer/2022062309/56815adc550346895dc8a7c5/html5/thumbnails/11.jpg)
Ensuring lossless decomposition
R(A1, ..., An, B1, ..., Bm, C1, ..., Cp)
If A1, ..., An B1, ..., Bm or A1, ..., An C1, ..., Cp
Then the decomposition is lossless
R1(A1, ..., An, B1, ..., Bm) R2(A1, ..., An, C1, ..., Cp)
Note: don’t need both
![Page 12: Lossless Decomposition](https://reader035.vdocuments.us/reader035/viewer/2022062309/56815adc550346895dc8a7c5/html5/thumbnails/12.jpg)
Identifying a Loss Decomposition
• Make a table for sub schemas of R• Fill in table with distinguished variables
(corresponding to the sub schemas)– If one row is full of distinguished variables, it’s lossless– If no one row is full, add distinguished variables• To add distinguished variables1) 2 or more rows with distinguished variables on LHS2) 1 or more rows with distinguished variables on RHS3) 1 or more rows with non-distinguished variables on RHS
![Page 13: Lossless Decomposition](https://reader035.vdocuments.us/reader035/viewer/2022062309/56815adc550346895dc8a7c5/html5/thumbnails/13.jpg)
Example 1 (From Class)
R(A B C D E)•FD1 = (A B)•FD2 = (BC E)•FD3 = (ED A)R1=(AB);
R2=(ACDE);
![Page 14: Lossless Decomposition](https://reader035.vdocuments.us/reader035/viewer/2022062309/56815adc550346895dc8a7c5/html5/thumbnails/14.jpg)
Answer
a a
a a a A a
A B C D ER1R2
*This decomposition is lossless
![Page 15: Lossless Decomposition](https://reader035.vdocuments.us/reader035/viewer/2022062309/56815adc550346895dc8a7c5/html5/thumbnails/15.jpg)
Example 2Is this decomposition lossless?
•R (A B C D E)FD1 – AB CFD2 – C EFD3 – BDFD4 – EA
R1=(BCD); R2=(ACE);
![Page 16: Lossless Decomposition](https://reader035.vdocuments.us/reader035/viewer/2022062309/56815adc550346895dc8a7c5/html5/thumbnails/16.jpg)
Answer
• If you do this procedure and you don’t have one row full of distinguished variables, then the decomposition is lossy.
a a a a aa a a
A B C D ER1R2
*This decomposition is lossless
![Page 17: Lossless Decomposition](https://reader035.vdocuments.us/reader035/viewer/2022062309/56815adc550346895dc8a7c5/html5/thumbnails/17.jpg)
• R(A B C D E)FD1: A BCFD2: BD CEFD3: E ADFD4: CE A
R1(ABC) = R2 (BCDE) =
![Page 18: Lossless Decomposition](https://reader035.vdocuments.us/reader035/viewer/2022062309/56815adc550346895dc8a7c5/html5/thumbnails/18.jpg)
ConclusionDecomposing is the act of breaking tables down in order to achieve higher normal form.
Decompositions should always be lossless.•This confirms that information in the original relation can be accurately reconstructed based on the decomposed relations.
Remember that for a decomposition to be considered “GOOD” it must also preserve functional dependencies.
![Page 19: Lossless Decomposition](https://reader035.vdocuments.us/reader035/viewer/2022062309/56815adc550346895dc8a7c5/html5/thumbnails/19.jpg)
Questions?
![Page 20: Lossless Decomposition](https://reader035.vdocuments.us/reader035/viewer/2022062309/56815adc550346895dc8a7c5/html5/thumbnails/20.jpg)
References
• http://infolab.stanford.edu/~ullman/fcdb/spr99/lec14.pdf
• http://www.cs.sfu.ca/CC/354/zaiane/material/notes/Chapter7/node7.html
• http://www.cs.sjsu.edu/~lee/cs157b/29SpCS157BL14HuffmanCode&LosslessDecomposition.ppt