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.

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

## Recommended Posts:

- Database Management System | Dependency Preserving Decomposition
- Difference between Lossless and Lossy Join Decomposition
- Lossless Decomposition in DBMS
- SQL | Join (Cartesian Join & Self Join)
- Difference between Inner Join and Outer Join in SQL
- Difference between Natural join and Inner Join in SQL
- Difference between Natural join and Cross join in SQL
- Full join and Inner join in MS SQL Server
- Left join and Right join in MS SQL Server
- Difference between Nested Loop Join and Hash Join
- Difference between Nested Loop join and Sort Merge Join
- Difference between Hash Join and Sort Merge Join
- Self Join and Cross Join in MS SQL Server
- Inner Join vs Outer Join
- Functional Dependency and Attribute Closure
- Armstrong's Axioms in Functional Dependency in DBMS
- Multivalued Dependency (MVD) in DBMS
- Properties of Relational Decomposition
- Difference between JOIN and UNION in SQL
- Difference between Left, Right and Full Outer Join