Dependency Preservation in DBMS

About Dependency Preserving Decomposition in DBMS

In this article, we will learn about dependency preserving decomposition in DBMS.
Dependency preservation decomposition is another property of decomposed relational database schema D in which each functional dependency X -> Y specified in F either appeared directly in one of the relation schemas Ri in the decomposed D.

 

Dependency preserving decomposition in dbms

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.

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).


Theory

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)


Example

Let a relation R(A,B,C,D) and set a FDs F = { A -> B ,  A -> C  , C -> D}  are given.
A relation R is decomposed into –
 

R1 = (A, B, C) with FDs F1 = {A -> B, A -> C}, and
R2 = (C, D) with FDs F2 = {C -> D}.
      F' = F1 ∪ F2 = {A -> B, A -> C, C -> D}
      so, F' = F. 
      And so, F'+ = F+.

 

Thus, the decomposition is dependency preserving decomposition.

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

Checkout list of all the video courses in PrepInsta Prime Subscription

Checkout list of all the video courses in PrepInsta Prime Subscription