# Lossless join and Dependency preserving Decomposition in DBMS

## Lossless Join and Dependency preserving Decomposition in DBMS

In this article, we will learn about Lossless Join and Dependency preserving Decomposition in DBMS. `Decomposition` of a relation in relational model is done to convert it into appropriate normal form. A relation R is decomposed into two or more only if the decomposition is both lossless join and dependency preserving. • `Decomposition` of a relation in relational model is done to convert it into appropriate normal form
• A relation R is decomposed into two or more only if the decomposition is both lossless join and dependency preserving.

### Lossless join decomposition

There are two possibilities when a relation R is decomposed into R1 and R2.They are
• Lossy decomposition i.e., R1⋈R2⊃R
• Lossless decomposition i.e., R1⋈R2=R

#### For a decomposition to be lossless, it should hold the following conditions

• Union of attributes of R1 and R2 must be equal to attribute R. each attribute of R must be either in R1 or in R2 i.e., Att(R1) ⋃ Att(R2) = Att(R)
• Intersection of attributes of R1 and R2 must not be null i.e., Att(R1) ⋂ Att(R2) ≠ Ø
• Common attribute must be a key for atleast one relation(R1 or R2) i.e., Att(R1) ⋂ Att(R2) -> Att(R1) or Att(R1) ⋂ Att(R2)->Att(R2)

#### Example

A relation R(A,B,C,D) with FD set {A->BC} is decomposed into R1(ABC) and R2(AD). This is lossless join decomposition because
• First rule holds true as Att(R1) ⋃ Att(R2)=(ABC) ⋃ (AD)= (ABCD) = Att(R)
• Second rule holds true as Att(R1) ⋂ Att(R2) = (ABC) ⋂ (AD) ≠ Ø
• Third rule 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 must be part of either R1 or R2 or must be derivable from combination of functional dependencies(FD) of R1 and R2
• Suppose 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)

#### Example

Consider a schema R(A,B,C,D) and functional dependencies A->B and C->D which is decomposed into R1(AB) and R2(CD)

This decomposition is dependency preserving decompostion because

• A->B can be ensured in R1(AB)
• C->D can be ensured in R2(CD)

### Related Banners

Get PrepInsta Prime & get Access to all 200+ courses offered by PrepInsta in One Subscription

## Get over 200+ course One Subscription

Courses like AI/ML, Cloud Computing, Ethical Hacking, C, C++, Java, Python, DSA (All Languages), Competitive Coding (All Languages), TCS, Infosys, Wipro, Amazon, DBMS, SQL and others