What is Lossless Join Decomposition in DBMS?
FREE
When the relational model is not in an appropriate normal form, then the decomposition of a relationship is required. A table is broken into multiple tables which is known as decomposition. It is done to eliminate redundancy and inconsistency. Decomposition is categorized into two types- lossless join decomposition and dependency preserving.
In lossless join decomposition in DBMS, a relation R has to undergo decomposition into relations R1, and R2 and if we perform a natural join on the relations R1 and R2, the relation R which is the original relation is obtained. In short, the original relation can be obtained by using joins on the decomposed relations. Here the original data is preserved and it is ensured that the original data and the data after reconstruction should be the same.
Criteria of Lossless Join Decomposition in DBMS?
For lossless join decomposition, we select a common attribute. Attributes in DBMS are the descriptive properties which describe an entity. The criteria for selecting the same is that it has to be a candidate key or super key in either relation R1, R2, or both. In other words, the lossless join decomposition will take place when a minimum of one of these functional dependencies is in F+ (Functional dependency closure).
In addition, the following conditions must also be satisfied-
- The union of the sub-relations Relation-1 and Relation-2 should be containing all the attributes that were present in the original relation R which got decomposed.
- The intersection of sub-relations Relation-1 and Relation-2 is not allowed to be null. It is necessary for the sub-relation to contain a common attribute.
Transform Your Career
Choose from our industry-leading programs designed for career success
Modern Software and AI Engineering Program
Master full-stack development with AI integration
+1000 moreModern Data Science and ML with specialisation in AI
Advanced data science techniques with AI specialization
+1000 moreAdvanced AIML with Specialisation in Agentic AI
Deep dive into AIML with focus on Agentic systems
+1000 moreDevOps, Cloud & AI Platform Engineering
Build and manage AI-powered cloud infrastructure
+1000 moreAI Engineering Advanced Certification by IIT-Roorkee
Premier AI engineering certification from IIT-Roorkee
What are the Various Examples of a Lossless Join Decomposition?
Transform Your Career
Choose from our industry-leading programs designed for career success
Modern Software and AI Engineering Program
Master full-stack development with AI integration
+1000 moreModern Data Science and ML with specialisation in AI
Advanced data science techniques with AI specialization
+1000 moreAdvanced AIML with Specialisation in Agentic AI
Deep dive into AIML with focus on Agentic systems
+1000 moreDevOps, Cloud & AI Platform Engineering
Build and manage AI-powered cloud infrastructure
+1000 moreAI Engineering Advanced Certification by IIT-Roorkee
Premier AI engineering certification from IIT-Roorkee
Example 1
Consider the following relations- R = (D, E, F)
R1 = (D, E)
R2 = (E, F)
The relation R has 3 attributes D, E, and F. The relation R is decomposed into two relations Relation-1 and Relation-2. Relation-1 and Relation-2 both have two attributes. Both have a common attribute 'E'.
Now, let us draw a table of Relation R with raw data −
Also, it is important to remember that the value present in Column E should be unique. If there is a presence of a duplicate value, it is not possible for lossless join decomposition to take place.
R = (D, E, F)
| D | E | F |
|---|---|---|
| 78 | 19 | 16 |
| 39 | 76 | 91 |
| 78 | 29 | 44 |
It is decomposed as follows-
R1(D, E)
| D | E |
|---|---|
| 78 | 19 |
| 39 | 76 |
| 78 | 29 |
R2(E, F)
| E | F |
|---|---|
| 19 | 16 |
| 76 | 91 |
| 29 | 44 |
Let us check the first condition. It was The union of the sub-relations R1 and R2 must contain all the attributes that are available in the original relation R before decomposition.
So, R1 U R2= R
| D | E | F |
|---|---|---|
| 78 | 19 | 16 |
| 39 | 76 | 91 |
| 78 | 29 | 44 |
The relation obtained above is the same as the original relation R. We can say that it is an example of Lossless-join decomposition.
Learn more:
- Learn more about Functional Dependency in DBMS, by clicking on this article.
- Learn more about Join Operation in DBMS here.
- Check out this article to learn more about Redundancy in DBMS.
Conclusion
- So, in lossless join decomposition in DBMS, a relation R is decomposed into relations R1, R2, and if we perform a natural join on the relations R1 and R2, the relation R which is the original relation is obtained.
- Here the original data is preserved.
- It mitigates redundancy.
- It is also known as non-additive join decomposition.
- The two main conditions for lossless join decomposition are- The union of the sub-relations R1 and R2 must contain all the attributes that are available in the original relation R before decomposition and the intersection of sub-relations R1 and R2 cannot be null.
- The sub-relation must contain a common attribute and the common attribute must be unique also.