**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 f2is a subset of F -----> Not Dependency preserving.f1 U f2is 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} AC --> D // Removing AC from right side as these are trivial attributes 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. Sogiven decomposition is not dependency preservingg.

**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 this for 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

(C) AB->C, C->AD

(D) A ->BCD

Refer this for solution.

Below is the Quiz of previous year GATE Questions.

http://quiz.geeksforgeeks.org/dbms/database-design-normal-forms/

Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above

## Recommended Posts:

- DBMS | Minimum relations satisfying 1NF
- Canonical Cover
- Lossless Join and Dependency Preserving Decomposition
- Functional Dependency and Attribute Closure
- Database Management System | Lossless Decomposition
- ACID Properties in DBMS
- Indexing in Databases | Set 1
- Last Minute Notes - DBMS
- DBMS | Concurrency Control -Introduction
- DBMS | How to find the highest normal form of a relation
- Finding Attribute Closure and Candidate Keys using Functional Dependencies
- Structured Query Language (SQL)
- Database Normalization | Normal Forms
- Database Normalization | Introduction
- Commonly asked DBMS interview questions | Set 1