Difficulties with getting a relation into 3NF

[Mateusz Szost]

Homework Statement

R(ABCDEF)
D->F
AB->C
E->F
C->BD
D->E

Decompose R into dependency preserving 3NF

Homework Equations

F = {D->F, AB->C, E->F, C->BD, D->E}

The Attempt at a Solution

My attempt is to first construct the minimal basis for the FD set F, which is G={A->C, E->F, C->B, C->D, D->E} (hopefully correct). Then I created a set of relations over all the FD's in G, which is as following:

S0(AC)
S1(EF)
S2(CB)
S3(CD)
S4(DE)

Then I also add a relation, whose schema forms a key. The key for the relation R is AB and therefore I added S5(AB), so the complete solution is:

S0(AC)
S1(EF)
S2(CB)
S3(CD)
S4(DE)
S5(AB)

But afterwards, when I test this solution if it fills the requirements to be in 3NF, then it is incorrect. Firstly, my definition of a relation in 3NF is if X->A is a FD that holds in a relation, then X is a superkey or A is a prime (an attribute that is part of a key). The functional dependency C-D will fail in my solution because it doesn't fill either of the requirements.

Have I constructed the minimal basis incorrectly? I don't know why I can't get this all right.

If you have a very simple and effective algorithm to decompose a relation into 3NF, then please let me know it so I will be better at decomposing relations into 3NF.

Thanks for your help!

 

[KataKoniK]

Thank you for your question. I am a scientist and I would like to help you with decomposing R into dependency preserving 3NF.

Firstly, your minimal basis is correct. However, your solution is not in 3NF because the functional dependency C->D does not satisfy the requirements. To decompose R into 3NF, we can follow these steps:

1. Identify all the candidate keys for the relation R. In this case, AB is a candidate key.

2. Create a relation for each functional dependency in the minimal basis G, using the left-hand side of the dependency as the key.

3. For each remaining attribute in R, identify the relation that has the smallest key that includes that attribute. If there is no such relation, create a new relation with the attribute as the key.

4. Combine all the resulting relations into a new set of relations, which will be in 3NF.

In this case, the resulting set of relations would be:

S0(AB)
S1(AC)
S2(EF)
S3(CB)
S4(CD)
S5(DE)

I hope this helps. If you have any further questions, please do not hesitate to ask.