0

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)
4
  • 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.) Apr 18, 2015 at 13:26
  • @ypercube Please can you tell me primary keys and foreign keys for tables R1 to R8 ?
    – user119249
    Apr 18, 2015 at 13:46
  • @ypercube I edited the tables after normalisation. But am not sure of foreign and primary keys
    – user119249
    Apr 18, 2015 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, 2015 at 8:52

1 Answer 1

1

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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