Decomposition to BCNF and set of super key

Question

So I have this set of relation

AB->CDEF

G->H,I

ABJ->K

C->L

How should I decompose this? I am so confused. Am I supposed to find the set of super key first?

Answer 1

We can first convert the relation R to 3NF and then to BCNF.

To convert a relation R and a set of functional dependencies(FD's) into 3NF you can use Bernstein's Synthesis. To apply Bernstein's Synthesis -

• First we make sure the given set of FD's is a minimal cover
• Second we take each FD and make it its own sub-schema.
• Third we try to combine those sub-schemas

For example in your case:

R = {A,B,C,D,E,F,G,H,I,J,K,L}
FD's = {AB->CDEF,G->HI,ABJ->K,C->L}

First we check whether the FD's is a minimal cover (singleton right-hand side , no extraneous left-hand side attribute, no redundant FD)

• Singleton RHS: We write the FD's with singleton RHS. So now we have FD's as {AB->C, AB->D, AB->E, AB->F, G->H, G->I, ABJ->K, C->L}
• No extraneous LHS attribute: We remove the extraneous LHS attribute if any. There are no extraneous LHS attributes here.
• No redundant FD's: We remove the redundant dependencies if any. Now FD's are {AB->C, AB->D, AB->E, AB->F, G->H, G->I, ABJ->K, C->L}

Second we make each FD its own sub-schema. So now we have - (the keys for each relation are in bold)

R₁={A,B,C}
R₂={A,B,D}
R₃={A,B,E}
R₄={A,B,F}
R₅={G,H}
R₆={G,I}
R₇={A,B,J,K}
R₈={C,L}

Third we combine all sub-schemas with the same LHS. So now we have -

S₁ = {A,B,C,D,E,F}
S₂ = {G,H,I}
S₃ = {A,B,J,K}
S₄ = {C,L}

Since none of the above decomposed relations contain contain a key of R, we need to create an additional relation schema that contains attributes that form of a key of R. This is to ensure lossless join decomposition that preserves dependencies. So we add -

S₅ = {A,B,G,J}

ABGJ is the key of the original relation R

This is in 3NF. Now to check for BCNF we check if any of these relations (S₁,S₂,S₃,S₄,S₅) violate the conditions of BCNF (i.e. for every functional dependency X->Y the left hand side (X) has to be a superkey) . In this case none of these violate BCNF and hence it is also decomposed to BCNF.

Note - The importance of some of these steps may not be clear in this example. Have a look at other examples here and here.