Decomposition in DBMS- check the lossless and lossy concept

Decomposition in DBMS- LOSSLESS AND LOSSY DECOMPOSITION

1. DECOMPOSITION DATABASE MANAGEMENT

2. Module-I Suggested books: 1. Database System Concepts Henry F. Korth and Silberschatz Abraham, Mc.Graw Hill. 2. Fundamentals of Database Systems”, Ramez Elmasri , Shamkant B.Navathe , Addison Wesley

3. Agenda Decomposition Dependency Preservation Lossless and Lossy decomposition Examples

4. DECOMPOSITION Definition :The decomposition of a schema R=A1…An is its replacement by a collection DR = {R1 , R2 , …, Rm } of subsets of R such that R = R1 Ս R2 Ս … Ս Rm Example : Assume the schema R=ABCD. The following are possible decompositions of R. R1 = {AB, CD} R2 = {AB, ACD} R3 = {A, BCD} R4 = {AB, BC, CD, AD} Decomposition are of two types: Lossless decomposition Lossy decomposition

5. Dependency Preservation in Decomposition

6. Lossless Decomposition Example : Consider a relation R(A,B,C,D) having the FD(s), F={AB C,BC D). The relation is decomposed into two relation: R1(A,B,C) and R2(B,C,D). Prove that the decomposition is lossless decomposition. Solution: Step 1: Create initial table, entering b ij values in each cell. i -means row index and j-means column index

7. Lossless Decomposition Step 2: For each attribute mentioned in each partition R i introduce a j on column j Step 3:Apply FDs to unify a j ,b ij symbols.. Using AB C we cannot consider because, R2 consist of b 21 for A. But using BCD we discover {a 2 ,a 3 } {b 14 ,a 4 }, hence b 14 can be replaced by a 4 . As the first row now has aj values in each cell. Stop! Decomposition R1 and R2 are lossless. Decomposed Relation: R1(A,B,C) and R2(B,C,D).

8. Lossy Decomposition Example : Consider a relation R(A,B,C,D) having the FD(s), F={B C,D A). The relation is decomposed into two relation: R1(B,C) and R2(A,D). Prove that the decomposition is lossy decomposition. Solution: Step 1: Create initial table, entering b ij values in each cell. Step 2: For each attribute mentioned in each partition R i introduce a j on column j Step 3:Apply FDs to unify a j ,b ij symbols.. Using B C we cannot consider because, R2 consist of b 22 for B. We also cannot use DA because R1 consist of b 14 for D. ** LHS must have similar value of a j then only RHS can be changed. We cannot derive a full row with aj values- decomposition is lossy. i -means row index and j-means column index

9. Sample Example- Try it R = (A, B, C, D, E). We decompose it into R1 = (A, B, C), R2 = (A, D, E). The set of functional dependencies is: A → BC, CD → E, B → D, E → A. Show that this decomposition is a lossless-join decomposition. Consider a schema R(A,B,C,D) and functional dependencies A->B and C->D. Then the decomposition of R into R1(A,B ) and R2(C,D ). Check whether the decomposition is lossless or lossy. Consider a relational schema R with attributes A, B, C, D, E and the set of functional dependencies A → CD, B → CE, E → B. Decomposed relations are R1 = (A, B, E), R2 = (A, C, D). Check whether the decomposition is lossless or lossy.

10. THANK YOU