Progression to 3NF

normalizationrelational-theory

my relation is:

H = {A,B,C,D,E}
A -> C
{A,D} -> E
B -> C
{B,D} -> E
C -> B
E -> C

I have to convert this relation to 3NF.
The candidate key : {A,D}

First step is minimalization. In minimalization i can drop {A,D} -> E out of fds.

H = {A,B,C,D,E}
A -> C
B -> C
{B,D} -> E
C -> B
E -> C

Decomposition:

H1 = {A,C} with key A
H2 = {B,C} with key B and FD: B -> C
H3 = {B,D,E} with key {B,D}
H4 = {C,B}  with key C and FD: C ->B
H5 = {E,C} with key E
H6 = {A,D} with key A // added because one relation must contains key

I got confused when i saw H2 and H4. They looks almost similar. So, should merge both into one ? To get one relation:

H2 = {B,C} with keys {B},{C} and fds: B -> C, C -> B?

Since relations H1 and H6 have the same key, can i merge them to?

Thanks for help

Best Answer

H2 and H4 are exactly the same relation, since the order of the attributes does not matter. In general the synthesis algorithm requires that, after the first phase, you should remove all the relations contained in others. So, the answer to the first question is yes, and of course the relation {B, C} has two dependencies B → C and C → B, and two candidate keys, B and C.

On the other hand, H1 and H6 are different relations, which should not be merged together (and, moreover, they do not have the same keys, since H1 has key A, while H6 has key {A, D} (and no dependencies)).