Search code examples
database3nfcanonical-form

What happens when functional dependency is circular?

I am trying to decompose the following relationships in to 3NF:

A -> BCD
BC -> DE
C -> D
D -> A

So I eliminated the redundancy to get the canonical cover:

A -> BC
B -> E
C -> D
D -> A

And now I am trying to decompose this into 3NF.

Should I decompose into r1(A, B, C) r2(B, D), r3(C, D). Then what do I do with D -> A?

The fact that A -> B -> D -> A is throwing me off.

Solution

  • Given

    A -> BCD
BC -> DE
C -> D
D -> A

    to obtain a canonical cover we first remove D from BC->DE:

    A -> BC
BC -> E
C -> D
D -> A

    Next, we observe that C->D, D->A, A->BC and if we know B->E, then we also know C->E. Hence,

    A -> BC
B -> E
C -> D
D -> A

    3NF decomposition works as follows:

    1) Create tables for each dependency in the canonical cover

    R1(A,B,C) R2(B,E) R3(C,D) R4(A,D)

    2) Determine candidate keys of R. If no candidate keys are contained in the tables of step 1, add a new table containing only attributes of a candidate key.

    Here A is a candidate key and it is contained in R1 (and R4), so no new tables should be added.

    3) If there is a table such that its attributes are a subset of attributes of another table, remove the "contained" table.

    This is not applicable, so the 3NF decomposition remains unchanged.

    As you see, circular dependencies are not problematic.