CODEWITHSUNDEEP
databasedatabase-normalizationfunctional-dependencies
Sam Anderson
Sam Anderson

Reputation: 11

Finding out 2NF for Functional Dependency

I have a relation R consisting of {A,B,C,D,E,F} and the below are the dependencies that follow :

{A} -> {C,F}
{C} -> {D}
{B} -> {E}

This has to be converted to 2NF and 3NF accordingly.

I just converted them accordingly.

Could you please validate and let me know if the below forms are valid ?

2NF :

{A,C,F,D}
{B,E}

3NF :

{A,C,F}
{C,D}
{B,E}

Upvotes: 1

Views: 423

Answers (1)

Renzo
Renzo

Reputation: 27424

The conversion in 3NF is not a correct one, since you have a decomposition that is not “data preserving” (that is, if you have an instance of the relation R, and project it over the attributes of the decomposed relations, when you do the natural join of them you obtain a relation which is different from R).

The reason is that the (only) candidate key of R is {A,B}, and this two attributes must be both present in a decomposed relation. So, in the synthesis algorithm to decompose a schema in 3NF, the last step says that if no candidate key of the original relation is present in any decomposed relation, one must add a schema with a key. So the correct decomposition is the following:

R1 < (A C F), { A → C F } >

R2 < (B E), { B → E } >

R3 < (C D), { C → D } >

R4 < (A B), { } >

Upvotes: 1

Related Questions