Difference between Lossless and Lossy Join Decomposition
The process of breaking up of a relation into smaller subrelations is called Decomposition. Decomposition is required in DBMS to convert a relation into a specific normal form which further reduces redundancy, anomalies, and inconsistency in the relation.
There are mainly two types of decompositions in DBMS-
- Lossless Decomposition
- Lossy Decomposition
Difference Between Lossless and Lossy Join Decomposition :
Lossless | Lossy |
---|---|
The decompositions R1, R2, R2…Rn for a relation schema R are said to be Lossless if there natural join results the original relation R. | The decompositions R1, R2, R2…Rn for a relation schema R are said to be Lossy if there natural join results into addition of extraneous tuples with the original relation R. |
Formally, Let R be a relation and R1, R2, R3 … Rn be it’s decomposition, the decomposition is lossless if – R1 ⨝ R2 ⨝ R3 .... ⨝ Rn = R
|
Formally, Let R be a relation and R1, R2, R3 … Rn be its decomposition, the decomposition is lossy if – R ⊂ R1 ⨝ R2 ⨝ R3 .... ⨝ Rn
|
There is no loss of information as the relation obtained after natural join of decompositions is equivalent to original relation. Thus, it is also referred to as non-additive join decomposition | There is loss of information as extraneous tuples are added into the relation after natural join of decompositions. Thus, it is also referred to as careless decomposition. |
The common attribute of the sub relations is a superkey of any one of the relation. | The common attribute of the sub relation is not a superkey of any of the sub relation. |
Example-1:
Example to check whether given Decomposition Lossless Join Decomposition.
Let there be a relational schema Student(Roll No., S_name, S_dept). StudentDetails(Roll No., S_name) and Dept(Roll No., S_dept) be it’s decompositions.
Roll No. | S_name | S_dept |
---|---|---|
1 | Raju | CSE |
2 | Raju | Quantum Computing |
Roll No. | S_name |
---|---|
1 | Raju |
2 | Raju |
Roll No. |
S_dept |
---|---|
1 |
CSE |
2 |
Quantum Computing |
Now for the decomposition to be lossless,
StudentDetails ⨝ Dept = Student then, StudentDetails ⨝ Dept is
Roll No. | S_name | S_dept |
---|---|---|
1 | Raju | CSE |
2 | Raju | Quantum Computing |
As, StudentDetails ⨝ Dept = Student,
This decomposition is Lossless.
Example-2:
Example to check whether given Decomposition Lossy Join Decomposition.
Let there be a relational schema Student(Roll No., S_name, S_dept). StudentDetails(Roll No., S_name) and Dept(S_name, S_dept) be it’s decompositions.
Roll No. | S_name | S_dept |
---|---|---|
1 | Raju | CSE |
2 | Raju | Quantum Computing |
Roll No. | S_name |
---|---|
1 | Raju |
2 | Raju |
S_name | S_dept |
---|---|
Raju | CSE |
Raju | Quantum Computing |
Now for the decomposition to be lossy,
Student ⊂ StudentDetails ⨝ Dept then, StudentDetails ⨝ Dept is
Roll No. | S_name | S_dept |
---|---|---|
1 | Raju | CSE |
1 | Raju | Quantum Computing |
2 | Raju | CSE |
2 | Raju | Quantum Computing |
As, Student ⊂ StudentDetails ⨝ Dept,
This decomposition is Lossy.
Thus, we can figure out whether decomposition is lossless or lossy.