Decomposition in DBMS – Lossless and Lossy Jitendra Ajmera The Crazy Programmer

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₁, A₂, A₃…………A_n

So R = {A₁, A₂, A₃…………A_n}

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

R₁, R₂……..Rx

These all relational schemas belong to the original one R.

R₁, R₂……..R_x = R

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

R₁ U R₂ U R₃ ……..U R_x = R

Here R₁, R₂……..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.

The post Decomposition in DBMS – Lossless and Lossy appeared first on The Crazy Programmer.

from The Crazy Programmer https://ift.tt/wMRP0Qm