Decomposition in DBMS – Lossless and Lossy | ninjasquad

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:

A_{1}, A_{2}, A_{3}…………A_{n}

So R = {A_{1}, A_{2}, A_{3}…………A_{n}}

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

R_{1}, R_{2}……..Rx

These all relational schemas belong to the original one R.

R_{1}, R_{2}……..R_{x} = R

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

R_{1} U R_{2 }U R_{3 }……..U R_{x} = R

Here R_{1}, R_{2}……..R^{x} <= 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**

Sid |
Name (Not Null) |
Subject (Not Null) |
Mobile |
Address |

1 | Raj | English | 65468154 | 51, Vaishalinagar |

2 | Jyoti | Home Science | 87668545 | 4a, Sukhsagar |

3 | Vikash | Maths | 26865948 | H7, Civil Lines |

1 | Harsh | Maths | Null | R32, Gokul Villa |

3 | Ajay | Science | 86516529 | 26, Karoli |

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

**Student Subject Details:**

Sid |
Name (Not Null) |
Subject (Not Null) |

1 | Raj | English |

2 | Jyoti | Home Science |

3 | Vikash | Maths |

1 | Harsh | Maths |

3 | Ajay | Science |

**Student Personal Details:**

Sid |
Mobile |
Address |

1 | 65468154 | 51, Vaishalinagar |

2 | 87668545 | 4a, Sukhsagar |

3 | 26865948 | H7, Civil Lines |

1 | Null | R32, Gokul Villa |

3 | 86516529 | 26, 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**

Sid |
Name (Not Null) |
Subject (Not Null) |
Mobile |
Address |

1 | Raj | English | 65468154 | 51, Vaishalinagar |

2 | Jyoti | Home Science | 87668545 | 4a, Sukhsagar |

3 | Vikash | Maths | 26865948 | H7, Civil Lines |

1 | Harsh | Maths | Null | R32, Gokul Villa |

3 | Ajay | Science | 86516529 | 26, 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**

Sid |
Name (Not Null) |
Subject (Not Null) |
Mobile |
Address |

1 | Raj | English | 65468154 | 51, Vaishalinagar |

2 | Jyoti | Home Science | 87668545 | 4a, Sukhsagar |

3 | Vikash | Maths | 26865948 | H7, Civil Lines |

1 | Harsh | Maths | Null | R32, Gokul Villa |

3 | Ajay | Science | 86516529 | 26, Karoli |

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

**Student Subject Details:**

Sid |
Name (Not Null) |
Subject (Not Null) |

1 | Raj | English |

2 | Jyoti | Home Science |

3 | Vikash | Maths |

1 | Harsh | Maths |

3 | Ajay | Science |

**Student Personal Details:**

Mobile |
Address |

65468154 | 51, Vaishalinagar |

87668545 | 4a, Sukhsagar |

26865948 | H7, Civil Lines |

Null | R32, Gokul Villa |

86516529 | 26, 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.

Source: Internet