What is Decomposition?

Decomposition means replacing a relation with a collection of smaller relations. The decomposition can summarize as;

•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

The Characteristics of Good Decompositions are;
•1) Lossless
•2) Preserve dependencies
What is lossless/Non-Less Decomposions?
Lossless means functioning without a loss.  In other words, retain everything.
It is Important for databases to have this feature.
Formal Definition of Decomposiotion
•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 à R1
  2) R1 ∩ R2 à R2  
In Simpler Terms we can define Decomposion as;
•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 
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.
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)

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)

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 variables

   1)2 or more rows with distinguished variables on LHS

   2)1 or more rows with distinguished variables on RHS

   3)1 or more rows with non-distinguished variables on RHS

Conclusion

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