Decomposition in DBMS – Lossless and Lossy

Decomposition means dividing a large and complex table into multiple small and easy tables. This removes redundancy, anomalies, and inconsistency in a database. This is the first stage of normalization.

Suppose we have a relational schema R, in which we have attributes as given below:

A1, A2, A3…………An

So R = {A1, A2, A3…………An}

If we decompose it into small parts then R will be divided into the following parts:

R1, R2……..Rx

These all relational schemas belong to the original one R.

R1, R2……..Rx = R

Also, we can write that union of all these subsets belongs to the original set R.

R1 U R2 U R3 ……..U Rx = R

Here R1, R2……..Rx <= R

Also 1<= i <= x      (i= number of relation like 1,2,3…..x)

Decomposition is further divided into two parts Lossless and Lossy. Let’s discuss them one by one in detail.

Lossless Decomposition

Loss means data loss while decomposing a relational table. A lossless decomposition is somewhat in which data is not lost because JOIN is used.

First, we decompose a large table into small appropriate tables, then apply natural join to reconstruct the original table.

This is a student database relational table:

Student Details

SidName (Not Null)Subject (Not Null)MobileAddress
1RajEnglish6546815451, Vaishalinagar
2JyotiHome Science876685454a, Sukhsagar
3VikashMaths26865948H7, Civil Lines
1HarshMathsNullR32, Gokul Villa
3AjayScience8651652926, Karoli

We can decompose it into two simple tables as given below:

Student Subject Details:

SidName (Not Null)Subject (Not Null)
1RajEnglish
2JyotiHome Science
3VikashMaths
1HarshMaths
3AjayScience

Student Personal Details:

SidMobileAddress
16546815451, Vaishalinagar
2876685454a, Sukhsagar
326865948H7, Civil Lines
1NullR32, Gokul Villa
38651652926, Karoli

If we want to see a common table then we can apply Natural JOIN between both tables like this:

Student Subject Details ⋈ Student Personal Details

SidName (Not Null)Subject (Not Null)MobileAddress
1RajEnglish6546815451, Vaishalinagar
2JyotiHome Science876685454a, Sukhsagar
3VikashMaths26865948H7, Civil Lines
1HarshMathsNullR32, Gokul Villa
3AjayScience8651652926, Karoli

In this operation, no data loss occurs, so this is a good option to consider for decomposition.

Lossy Decomposition

In this, the decomposition is performed in such a manner that the data will be lost. Let’s take an example:

Student Details

SidName (Not Null)Subject (Not Null)MobileAddress
1RajEnglish6546815451, Vaishalinagar
2JyotiHome Science876685454a, Sukhsagar
3VikashMaths26865948H7, Civil Lines
1HarshMathsNullR32, Gokul Villa
3AjayScience8651652926, Karoli

If we divide this student details table into two sections as given below:

Student Subject Details:

SidName (Not Null)Subject (Not Null)
1RajEnglish
2JyotiHome Science
3VikashMaths
1HarshMaths
3AjayScience

Student Personal Details:

MobileAddress
6546815451, Vaishalinagar
876685454a, Sukhsagar
26865948H7, Civil Lines
NullR32, Gokul Villa
8651652926, Karoli

In this Student Personal Details table, the SID column is not included, so now we don’t know that these mobiles numbers and address belongs to whom.

So always decompose a table in such a manner that the data may be easily reconstructed and retrieved.

Leave a Comment

Your email address will not be published. Required fields are marked *