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.
Decompositionof 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.
Learn more about Other Decompositions here on this page.
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)
Prime Course Trailer
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
Login/Signup to comment