Decompose table into third normal form

database-designdependenciesnormalization

Tables X and Y are given as below :

X (A, B, D, E, F, G, H, J) with A and B as primary key
Y (K, L, M, N, O, P, Q, R) with K and L as primary key

Now we need to decompose these tables into tables in the third normal form using the functional dependencies specified below:

  1. A -> G, H
  2. B -> D, E, F
  3. E -> F
  4. H -> G
  5. L -> N, O, P, Q
  6. N -> Q

I need to specify primary keys and foreign keys also. Please help to solve this problem.

I am newbie to normalization. So please help to solve this problem.If I normalize to 2NF I loose some relations which doesn't suit to 3NF. So I am confused. Any help will be appreciated. Because to convert to 3NF I need to go through 2NF too.

I had changed it to 3NF. Please help in finding if am right in doing it

R1 (E, F)
R2 (H, G)
R3 (A, B, L)
R4 (A, H)
R5 (B, D, E)

R6 (N, Q)
R7 (K, L, M, R)
R8 (L, N, O, P)

Best Answer

We have

X (A, B, D, E, F, G, H, J) with A and B as primary key
Y (K, L, M, N, O, P, Q, R) with K and L as primary key

With functional dependencies:

A -> G, H
B -> D, E, F
E -> F
H -> G
L -> N, O, P, Q
N -> Q

2NF Normalization

R1((A,B) (PK), J)
R2(A (PK) ,G,H)
R3(B (PK) ,D,E,F)
R4((K,L) (PK), M,R)
R5(L (PK) ,N,O,P,Q)

3NF Normalization

Ra((A,B) (PK), J)
Rb(A (PK) ,H)
Rc(H(PK), G)
Rd(B(PK),D,E)
Re(E(PK),F)
Rf((K,L) (PK), M,R)
Rg(L (PK),N,O,P)
Rh(N (PK), Q)
Related Question