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.

fragment - Desirable Properties of Decomposition

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 R1,R2,….,Rn with instance r1,r2,…..,rn
If r1 ⋈ r2 ⋈ ……. ⋈ rn = 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 R1, R2,….,Rn.

The decomposition is a lossless-join decomposition of R  if

(a) R1 ∪ R2 ∪ ......∪ Rn ≡ R 
lossless
and
(b) Let Ri and Rj be the any two subrelations, Ri and Rj can be merge into single relation Rij with attribute set Ri ∪ Rj only if 
(i) Ri ∩ Rj ≠ Φ

Intersection lossless
(ii) Ri ∩ Rj → Rj {Ri and Rj should be super key of Ri} and
     Ri ∩ Rj → Ri {Ri and Rj should be super key of Rj}

(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 :

S# Sname City P# Qty
3 Smith London 301 20
5 Nick NY 500 50
2 Steve Boston 20 10
5 Nick NY 400 40
5 Nick NY 301 10

Parts :

S# P# Qty
3 301 20
5 500 50
2 20 10
5 400 40
5 301 10

Supplier :

S# Sname City Qty
3 Smith London 20
5 Nick NY 50
2 Steve Boston 10
5 Nick NY 40
5 Nick NY 10

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

                            Parts  ⋈ Supplier

Parts  ⋈ Supplier :

S# Sname City P# Qty
3 Smith London 301 20
5 Nick NY 500 50
2 Steve Boston 20 10
5 Nick NY 400 40
5 Nick NY 301 10

= 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

Lossless Example Solved

 

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
  • lossy and lossless decomposition in dbms
  • properties of decomposition in dbms ppt

This article has 3 comments

  1. Pingback: replica mens bags

Leave a Reply