Desirable Properties of Decomposition –
If we apply the normal forms or normalization or schema refinement technique – Decomposition to the universal table, then it may be splitted up into different fragments.
At any stage, if we combine the fragments (denormalization), it should give the original table in terms of columns and rows and it will be described as the following properties :
Lossless Join Decomposition
Definition 1 :
Let R be the relational schema with instance r is decomposed into R_{1},R_{2},….,R_{n} with instance r_{1},r_{2},…..,r_{n}.
If r_{1} ⋈ r_{2} ⋈ ……. ⋈ r_{n} = r , then it is called Lossless Join Decomposition.
i.e. if natural joins of all the decompositions gives the original relation, then it is said to be Lossless Join Decomposition.
Definition 2: Another Definition or To check whether a Decomposition is a lossless or lossy decomposition –
Let R be a relation schema, F be a set of functional dependencies on R. Let R is decomposed in R_{1}, R_{2},….,R_{n}.
The decomposition is a losslessjoin decomposition of R if
(a) R_{1} ∪ R_{2} ∪ ......∪ R_{n} ≡ R and (b) Let R_{i} and R_{j} be the any two subrelations, R_{i} and R_{j} can be merge into single relation R_{ij} with attribute set R_{i} ∪ R_{j} only if (i) R_{i} ∩ R_{j} ≠ Φ (ii) R_{i} ∩ R_{j} → R_{j} {R_{i} and R_{j} should be super key of R_{i}} and R_{i} ∩ R_{j} → R_{i} {R_{i} and R_{j} should be super key of R_{j}} (c) Repeat (a) untin N relations become single relation. If is possible to merge into single relation, then decomposition is losless, otherwise lossy.
Example : How to Find Lossless Join Decomposition –
Method 1 : (Not Useful for Gate Students)
Consider the previous example Supplier_Parts which is decomposed into supplier and parts relation but doing the decomposition in a different way :
Supplier_Parts :


⇓  
Parts :

Supplier :

The above decomposition is a Lossless join Decomposition. Because, Let us apply natural join operation on the decomposed relations.
Parts ⋈ Supplier
Parts ⋈ Supplier :

= Supplier_Parts 
Hence the Decomposition is lossless join decomposition.
Method 2 : (Useful for Gate Students)
The above method is very time consuming for the gate students. The method 2 is very simple and fast. →
Consider again the relation Supplier_Parts. Try to decompose the relation so that the common attribute in the tables is a key for atleast one table. Here,
In Supplier relation Supplier(S#,Sname,City): S# → Sname S# → City In parts relation Parts(S#,P#,Qty) : (S#,P#) → Qty
Click Here for Questions on Lossless Join
What is a Lossy Join ?? – Click to Know
Previous  Home  Next 
Normalisation – Types of Anomalies & their Solution  Dependency Preserving Decomposition 
Incoming search terms:
 decomposition in dbms
 properties of decomposition in dbms
 Desirable properties of decomposition
 properties of decomposition
 Types of decomposition in dbms
 decomposition in dbms tutorial
 desirable properties of decomposition in dbms
 decomposition properties in dbms
 properties of decomposition in dbms ppt
 decomposition in database
Pingback: replica mens bags
Nice useful
Thank you 🙂