Question

Consider a relation schema R with attributes ABCDEFGH with functional dependencies S:

S={B→CD, BF→H, C→AG, CEH→F, CH→B}

Employ the BCNF decomposition algorithm to obtain a lossless decomposition of R into a collection of relations that are in BCNF. Make sure it is clear which relations are in the final decomposition and project the dependencies onto each relation in that final decomposition.

Answer 1

To make a BCNF relation, we need to find a candidate key.

For the candidate key, we need to find closure.

"The Closure Of Functional Dependency means the complete set of all possible attributes that can be functionally derived from given functional dependency."

Our FDs are,

B   ->CD

BF ->H

C   ->AG

CEH->F

CH ->B

Finding it, we get CEH -> CEHFBDAG.

So CEH is our candidate key. (BEH is also candidate key)

R = (ABCDEFGH)

By making CEH->F BCNF,

R1 = (CHEF) R2=(CEHBDAG)

By making C   ->AG BCNF,

R1 = (CHEF) R21=(CAG) R22=(CEHBD)

By making CH ->B BCNF,

R1 = (CHEF) R21=(CAG) R221=(CHB) R222=(CEHD)

By making CH ->B and B->CD BCNF,

R1 = (CHEF) R21=(CAG) R221=(CHB) R2221=(HD) R2222=(CED)

SO Final answer is,

R1 = (CHEF) R21=(CAG) R221=(CHB) R2221=(HD) R2222=(CED)