Finding the Primary Key and Normalising a Relation with Functional Dependencies

Question

I am studying for my databases exam and I've realized my professor did not teach a section of the normalization lecture notes, but glossed over them so I've been self studying and there is this example without solutions in the notes and I was wondering if I have been doing it right:

Given Relation R = {A,B,C,D,E,F,G,H,I,J}

And functional dependencies:

A,B -> C
A -> D,E
B -> F
F -> G,H
D -> I,J

1. Determine the primary key
2. Decompose R so it is in 2NF then show it in 3NF.

So, I got the primary key to be (A, B, D, F)

And then I tried to convert it to 2NF and I got relations:

(ABC), (DIJ), (ADE), (BF), (FGH)

And I honestly have no idea if this is right or how to then put it in 3NF... or if I've just skipped 2NF and already put it in 3NF. Any help?

Answer 1

It appears to me that you have skipped the NF2 and normalised the relation straight into the 3NF :)

The primary key for the original relation should be (A,B) as by inference rules (transitivity, such as A->D,E and D->I,J therefore A->I,J) it determines all other attributes. From this point onwards we have that:

• FD1: A,B -> C
• FD2: A -> D,E (Partial)
• FD3: B -> F (Partial)
• FD4: F -> G,H
• FD5: D -> I,J

2NF (No partial dependencies allowed)

Now we can decompose the relation in three relations moving partial FDs to separate relations but preserving other FDs which might depend on those partial FDs, such as FD2 and FD5. This would give us the following results:

• R1(A,D,E,I,J) -- FD2, FD5 (transitive)
• R2(B,F,G,H) -- FD3 FD4 (transitive)
• R3(A,B,C) -- FD1

Next, to achieve 3NF, transitive dependencies would have to be removed into separate relations in the same manner as NF2. Which, in turn, would result in the set of relations which you have already derived.

Good luck with your exams!