Answer to Question #156871 in Databases | SQL | Oracle | MS Access for RAFI

Question

Consider the relation schema R=(A, B, C, D) with functional dependency F as follows

A BC

B D

D B

Show a decomposition of R into R1 and R2 so that each relation of the decomposition is

in BCNF and also dependency preservation is achieved.

Answer 1

Functional Dependency:

If an attribute value is determined by another attribute which is called functional dependency

Ex X->Y X attribute determining value of Y

Dependency Preservation:

Consider a relation R with set of dependencies F

R is divided into R1,R2,R3.............Rn h

Let F1 is set of Functional dependencies in R1

Let F2 is set of Functional dependencies in R2

Let F3 is set of Functional dependencies in R3

Let Fn is set of Functional dependencies in Rn

The R is said to be dependency preserving if F1 U F2 U F3 U .........Fn = F

Answer: given R(A,B,C,D)  

A-> BC  B->D D-> B

let divide R into R1(A,B,C) and R2 (B,D)

set of dependencies (F1) in R1,by attribute closure(+) we can write, The attributes are A,B,C,

attribute A+

A+ = A

A+= ABC [ A-> BC ]

A+= ABCD [ B->D ] but remove D ,because there is no D in R1

only { A-> BC } holds

attribute B+

B+ = B and B->D

only { B->D } holds no D remove this

C+ we can not deter mine because no dependency on C

set of dependencies in R1 F1= { A-> BC }

set of dependencies (F2) in R2,by attribute closure(+) we can write, The attributes are B ,D

B+ = B and B->D

only { B->D } holds

D+ = B and D->B

only { D->B } holds

set of dependencies in R2 F2= { B->D, D->B }

Now check Dependency preserving property

F1 U F2 = F  

{ A-> BC } U  { B->D, D->B } = F these are equal to F so it is Dependency Preserving

Both relations are R1(A,B,C) and R2(B,D) in BCNF

In R1 A is super key In R2 BD is super key

In both relations Super key is determining attribute values hence it iks in BCNF.