- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Lossless and Lossy Decomposition in DBMS
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.