Lossless and Lossy Decomposition in DBMS

DBMSDatabaseMySQL

Decomposition in DBMS removes redundancy, anomalies and inconsistencies from a database by dividing the table into multiple tables.

The following are the types −

Lossless Decomposition

Decomposition is lossless if it is feasible to reconstruct relation R from decomposed tables using Joins. This is the preferred choice. The information will not lose from the relation when decomposed. The join would result in the same original relation.

Let us see an example −

<EmpInfo>

Emp_ID
Emp_Name
Emp_Age
Emp_Location
Dept_ID
Dept_Name
E001
Jacob
29
Alabama
Dpt1
Operations
E002
Henry
32
Alabama
Dpt2
HR
E003
Tom
22
Texas
Dpt3
Finance


Decompose the above table into two tables:

<EmpDetails>

Emp_ID
Emp_Name
Emp_Age
Emp_Location
E001
Jacob
29
Alabama
E002
Henry
32
Alabama
E003
Tom
22
Texas


<DeptDetails>

Dept_ID
Emp_ID
Dept_Name
Dpt1
E001
Operations
Dpt2
E002
HR
Dpt3
E003
Finance


Now, Natural Join is applied on the above two tables −

The result will be −

Emp_ID
Emp_Name
Emp_Age
Emp_Location
Dept_ID
Dept_Name
E001
Jacob
29
Alabama
Dpt1
Operations
E002
Henry
32
Alabama
Dpt2
HR
E003
Tom
22
Texas
Dpt3
Finance


Therefore, the above relation had lossless decomposition i.e. no loss of information.

Lossy Decomposition

As the name suggests, when a relation is decomposed into two or more relational schemas, the loss of information is unavoidable when the original relation is retrieved.

Let us see an example −

<EmpInfo>

Emp_ID
Emp_Name
Emp_Age
Emp_Location
Dept_ID
Dept_Name
E001
Jacob
29
Alabama
Dpt1
Operations
E002
Henry
32
Alabama
Dpt2
HR
E003
Tom
22
Texas
Dpt3
Finance


Decompose the above table into two tables −

<EmpDetails>

Emp_ID
Emp_Name
Emp_Age
Emp_Location
E001
Jacob
29
Alabama
E002
Henry
32
Alabama
E003
Tom
22
Texas


<DeptDetails>

Dept_ID
Dept_Name
Dpt1
Operations
Dpt2
HR
Dpt3
Finance


Now, you won’t be able to join the above tables, since Emp_ID isn’t part of the DeptDetails relation.

Therefore, the above relation has lossy decomposition.

raja
Published on 03-Jul-2018 14:38:51
Advertisements