What is Lossless Join Decomposition in DBMS?

Whenever we design a database, there are a lot of things that need to be taken into consideration. When database queries are written one of the factors is relations, 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.

So, 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 lossless join decomposition, a relation is decomposed into two or more relations. 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.

As discussed earlier, lossless join decomposition in DBMS is used to eliminate redundancy. Another name for lossless join decomposition is non-additive join decomposition.

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.

What are the Various Examples of a Lossless Join Decomposition?

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 it there is a presence of a duplicate value, it is not possible for lossless join decomposition to take place.

R = (D, E, F)

It is decomposed as follows-

R1(D, E)

R2(E, F)

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

The relation obtained above is 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.