Decomposition Without Dependency Preserving

If the decomposition of relation R with FDs (F), into R1 and R2 with their FDs (F1) and (F2), respectively, will be decomposition without dependency preservation if.

Closure of F (F+) ≠ Closure of F1 (F1+) U Closure of F2 (F2+)

In simple words, we can also say,

After decomposition, IF all FDs of the original table are not preserved through FDs of decomposed tables, then it is called decomposition of the table without dependency preservation.

Dependency Preserving Example

Suppose a relation R with A, B, C, and D, and its FDs are AB→CD, D→A. This Relation R is decomposed into tables R1 with A and D attributes and R2 with B, C, and D attributes.

Solution

Step 01: Find the closure of each attribute given in left hand side of given FD’s of Relation R(ABCD). As given in the following diagram.

Step 02: Find all Non-Trivial FDs of Decomposed Relations (R1 and R2) as given below.  

Step 03: Find all those Non-trivial FDs which are not determined from the given Relation R(ABCD)

Let’s check one by one all Non-Trivial FD’s of all decomposed relations R1 and R2.

i. Check (A→D):

As A→D of Relation R1(AD) cannot determined from the original table R(ABCD) Because Closure of A in the Original Table cannot determine “D,” this is an in-valid Dependency.

ii. Check (D→A):

As D→A of Relation R1 (AD) is directly given in the FDs of Relation R(ABCD). So, This Dependency can determined from the original table R(ABCD) Because the Closure of D in the Original Table can determine “A”. So, this is a valid Dependency.

iii. Check (B→CD):

As B→CD of Relation R2(BCD) cannot determined from original table R(ABCD) Because Closure of B in the Original Table cannot determine “CD”. So, this is an in-valid Dependency.

iv. Check (C→BD):

As C→BD of Relation R2(BCD) cannot determined from original table R(ABCD) Because Closure of C in the Original Table cannot determine “BD”. So, this is an in-valid Dependency.

v. Check (D→CB):

As D→CB of Relation R2(BCD) cannot determined from original table R(ABCD) Because Closure of D in the Original Table cannot determine “CB”. So, this is an in-valid Dependency.

vi. Check (BC→D):

As BC→D of Relation R2(BCD) cannot determined from the original table R(ABCD), Because Closure of BC in the Original Table cannot determine “D,” this is an in-valid Dependency.

vii. Check (BD→C):

As BD→C of Relation R2(BCD) can be determined from original table R(ABCD) Because Closure of BD in the Original Table can determine “C”. So, this is a valid Dependency.

Note: As BD determines itself (BD), D determines A, and AB can determine C in Original table R (ABCD).

viii. Check (CD→B):

As CD→B of Relation R2(BCD) cannot determined from original table R(ABCD) Because the Closure of CD in the Original Table cannot determine “B”. So, this is an in-valid Dependency.

So, just two non-trivial FDs are valid, which are given below.

Step 04: Find the closure of all valid Non-trivial FD’s of Decomposed Relations (R1 and R2) as given below

Step 05: If all dependencies of a given relation are preserved through all valid non-trivial dependencies of its decomposed tables, then the original table will be preserved. 

Explanation of the Above diagram

We will check all valid Non-trivial FDs of decomposed tables (one by one) and see whether these FDs can preserve all FDs of original Relation. If it preserves, then the decomposition is dependency-preserving; otherwise, it will not be dependency-preserving decomposition. 

All two FDs of original Relation are not preserved through valid Non-trivial FDs of decompose tables. So, it is an example of the decomposition of tables without dependency preserving.