# Lossless Join and Dependency Preserving Decomposition

Decomposition of a relation is done when a relation in relational model is not in appropriate normal form. Relation R is decomposed into two or more relations if decomposition is lossless join as well as dependency preserving.

**Lossless Join Decomposition**

If we decompose a relation R into relations R1 and R2,

- Decomposition is lossy if R1 ⋈ R2 ⊃ R
- Decomposition is lossless if R1 ⋈ R2 = R

** To check for lossless join decomposition using FD set, following conditions must hold: **

- Union of Attributes of R1 and R2 must be equal to attribute of R. Each attribute of R must be either in R1 or in R2.
Att(R1) U Att(R2) = Att(R)

- Intersection of Attributes of R1 and R2 must not be NULL.
Att(R1) ∩ Att(R2) ≠ Φ

- Common attribute must be a key for at least one relation (R1 or R2)
Att(R1) ∩ Att(R2) -> Att(R1) or Att(R1) ∩ Att(R2) -> Att(R2)

For Example, A relation R (A, B, C, D) with FD set{A->BC} is decomposed into R1(ABC) and R2(AD) which is a lossless join decomposition as:

- First condition holds true as Att(R1) U Att(R2) = (ABC) U (AD) = (ABCD) = Att(R).
- Second condition holds true as Att(R1) ∩ Att(R2) = (ABC) ∩ (AD) ≠ Φ
- Third condition holds true as Att(R1) ∩ Att(R2) = A is a key of R1(ABC) because A->BC is given.

**Dependency Preserving Decomposition**

If we decompose a relation R into relations R1 and R2, All dependencies of R either must be a part of R1 or R2 or must be derivable from combination of FD’s of R1 and R2.

For Example, A relation R (A, B, C, D) with FD set{A->BC} is decomposed into R1(ABC) and R2(AD) which is dependency preserving because FD A->BC is a part of R1(ABC).

**GATE Question: Consider a schema R(A,B,C,D) and functional dependencies A->B and C->D. Then the decomposition of R into R1(AB) and R2(CD) is [GATE-CS-2001]**

A. dependency preserving and lossless join

B. lossless join but not dependency preserving

C. dependency preserving but not lossless join

D. not dependency preserving and not lossless join

**Answer: ** For lossless join decomposition, these three conditions must hold true:

- Att(R1) U Att(R2) = ABCD = Att(R)
- Att(R1) ∩ Att(R2) = Φ, which violates the condition of lossless join decomposition. Hence the decomposition is not lossless.

For dependency preserving decomposition,

A->B can be ensured in R1(AB) and C->D can be ensured in R2(CD). Hence it is dependency preserving decomposition.

So, the correct option is C.

This article is contributed by **Sonal Tuteja**. If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.

Don’t stop now and take your learning to the next level. Learn all the important concepts of Data Structures and Algorithms with the help of the most trusted course: **DSA Self Paced**. Become industry ready at a student-friendly price.

## Recommended Posts:

- Database Management System | Dependency Preserving Decomposition
- Difference between Lossless and Lossy Join Decomposition
- Lossless Decomposition in DBMS
- Difference between Natural join and Cross join in SQL
- Difference between Natural join and Inner Join in SQL
- Full join and Inner join in MS SQL Server
- Left join and Right join in MS SQL Server
- Difference between Inner Join and Outer Join in SQL
- SQL | Join (Cartesian Join & Self Join)
- Multivalued Dependency (MVD) in DBMS
- Inner Join vs Outer Join
- Functional Dependency and Attribute Closure
- Armstrong's Axioms in Functional Dependency in DBMS
- Properties of Relational Decomposition
- Join statement in JCL
- Difference between JOIN and UNION in SQL
- Join algorithms in Database
- SQL | Join (Inner, Left, Right and Full Joins)
- Join operation Vs Nested query in DBMS
- What is PJNF(Project-Join Normal Form)?