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)
  • Should be R3 (A,B,J). And R7 (K,L,M,R). Otherwise, I think you have it all right! Also specify which is the primary key in each relation (R1 has E, R2 has H, R3 has (A,B), etc.) – ypercubeᵀᴹ Apr 18 '15 at 13:26
  • @ypercube Please can you tell me primary keys and foreign keys for tables R1 to R8 ? – user119249 Apr 18 '15 at 13:46
  • @ypercube I edited the tables after normalisation. But am not sure of foreign and primary keys – user119249 Apr 18 '15 at 13:57
  • 1
    Hi @user119249 if this or any answer has solved your question please consider accepting it by clicking the check-mark. This indicates to the wider community that you've found a solution and gives some reputation to both the answerer and yourself. There is no obligation to do this. – EagleOne May 1 '15 at 8:52

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)
Rf((K,L) (PK), M,R)
Rg(L (PK),N,O,P)
Rh(N (PK), Q)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.