# Database Management System | Dependency Preserving Decomposition

Dependency Preservation: A Decomposition D = { R1, R2, R3…Rn } of R is dependency preserving wrt a set F of Functional dependency if

```(F1 ? F2 ? â€¦ ? Fm)+ = F+.
Consider a relation R
R ---> F{...with some functional dependency(FD)....}

R is decomposed or divided into R1 with FD { f1 } and R2 with { f2 }, then
there can be three cases:

f1 U f2 = F -----> Decomposition is dependency preserving.
f1 U f2 is a subset of F -----> Not Dependency preserving.
f1 U f2 is a super set of F -----> This case is not possible.```

Problem:

Let a relation R (A, B, C, D ) and functional dependency {AB –> C, C –> D, D –> A}. Relation R is decomposed into R1( A, B, C) and R2(C, D). Check whether decomposition is dependency preserving or not.

Solution:

```R1(A, B, C) and R2(C, D)

Let us find closure of F1 and F2
To find closure of F1, consider all combination of
ABC. i.e., find closure of A, B, C, AB, BC and AC
Note ABC is not considered as it is always ABC

closure(A) = { A }  // Trivial
closure(B) = { B }  // Trivial
closure(C) = {C, A, D} but D can't be in closure as D is not present R1.
= {C, A}
C--> A   // Removing C from right side as it is trivial attribute

closure(AB) = {A, B, C, D}
= {A, B, C}
AB --> C  // Removing AB from right side as these are trivial attributes

closure(BC) = {B, C, D, A}
= {A, B, C}
BC --> A  // Removing BC from right side as these are trivial attributes

closure(AC) = {A, C, D}
NULL SET

F1 {C--> A, AB --> C, BC --> A}.
Similarly F2 { C--> D }

In the original Relation Dependency { AB --> C , C --> D , D --> A}.
AB --> C is present in F1.
C --> D is present in F2.
D --> A is not preserved.

F1 U F2 is a subset of F. So given decomposition is not dependency preserving.```

Question 1: Let R (A, B, C, D) be a relational schema with the following functional dependencies:

```A ? B, B ? C,
C ? D and D ? B.

The decomposition of R into
(A, B), (B, C), (B, D)```

(A) gives a lossless join, and is dependency preserving

(B) gives a lossless join, but is not dependency preserving

(C) does not give a lossless join, but is dependency preserving

(D) does not give a lossless join and is not dependency preserving

Refer to this for a solution.

Question 2 R(A, B, C, D) is a relation. Which of the following does not have a lossless join, dependency-preserving BCNF decomposition?

(A) A->B, B->CD

(B) A->B, B->C, C->D

(D) A ->BCD

Refer to this for a solution. Below is the Quiz of the previous year’s GATE Questions. https://www.geeksforgeeks.org/dbms-gq/database-design-normal-forms-gq/

Imp: The 1NF, 2NF, and 3NF are valid for dependency-preserving decomposition.

Dependency Preserving Decomposition is a technique used in Database Management System (DBMS) to decompose a relation into smaller relations while preserving the functional dependencies between the attributes. The goal is to improve the efficiency of the database by reducing redundancy and improving query performance.

1. In this technique, the original relation is decomposed into smaller relations in such a way that the resulting relations preserve the functional dependencies of the original relation. This is important because if the decomposition results in losing any of the original functional dependencies, it can lead to data inconsistencies and anomalies.
2. To achieve dependency preserving decomposition, there are various algorithms available, such as the Boyce-Codd Normal Form (BCNF) decomposition and the Third Normal Form (3NF) decomposition. These algorithms are based on the concept of functional dependencies and are used to identify the attributes that should be grouped together to form smaller relations.
3. The BCNF decomposition algorithm is used to decompose a relation into smaller relations in such a way that each resulting relation is in BCNF. BCNF is a higher normal form than 3NF and is used when there are multiple candidate keys in a relation.
4. The 3NF decomposition algorithm is used to decompose a relation into smaller relations in such a way that each resulting relation is in 3NF. 3NF is a normal form that ensures that there are no transitive dependencies between the attributes of a relation.

Overall, dependency preserving decomposition is an important technique in DBMS for improving database efficiency while maintaining data consistency and integrity. It is important to choose the right decomposition algorithm based on the specific requirements of the database to achieve the desired results.

Previous
Next