DBMS Notes

Decomposition Without Dependency Preserving

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

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

In simple words we can also say as,

After decomposition, IF All FD’s of original table are not preserved through FD’s of Decomposed tables then it is called decomposition of table without dependency preserving.

Dependency Preserving Example

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

Solution

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

Step 02: Find all Non-Trivial FD’s of Decomposed Relations (R1 and R2) as given under.  

Step 03: Find all those Non-trivial FD’s which are not determined from 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 original table R(ABCD) Because Closure of A in Original Table cannot determining “D”. So this is in-valid Dependency.

ii. Check (D→A):

As D→A of Relation R1 (AD), is directly given in the FD’s of Relation R(ABCD). So, This Dependency can determined from FD’s of original table R(ABCD) Because Closure of D in 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 Original Table cannot determining “CD”. So this is 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 Original Table cannot determining “BD”. So this is 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 Original Table cannot determining “CB”. So this is in-valid Dependency.

vi. Check (BC→D):

As BC→D of Relation R2(BCD), cannot determined from original table R(ABCD) Because Closure of BC in Original Table cannot determining “D”. So this is 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 Original Table can determine “C”. So this is 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 Closure of CD in Original Table cannot determining “B”. So this is in-valid Dependency.

So, Just two Non-trivial FD’s 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 given relation are preserved through all valid non-trivial dependencies of its decomposed tables, then the original table will be preserved. 

Explanation of Above diagram

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

As, All two FD’s of original Relation are not preserve through valid Non-trivial FD’s of decompose tables. So, it is an example of decomposition of tables without dependency preserving.

Help Other’s By Sharing…

Contact Us

Burewala, Vehari, Punjab, Pakistan

cstaleem1@gmail.com

Website: CStaleem.com

Pin It on Pinterest