QUESTIONS ON NORMALIZATION

Question on Second Normal Form (2NF):

1. Given a relation R( A, B, C, D) and Functional Dependency set FD = { AB → CD, B → C }, determine whether the given R is in 2NF? If not convert it into 2 NF.

Solution: Let us construct an arrow diagram on R using FD to calculate the candidate key.

QUESTIONS ON NORMALIZATION

From above arrow diagram on R, we can see that an attributes AB is not determined by any of the given FD, hence AB will be the integral part of the Candidate key, i.e. no matter what will be the candidate key, and how many will be the candidate key, but all will have W compulsory attribute.

Let us calculate the closure of AB

AB + = ABCD (from the method we studied earlier)

Since the closure of AB contains all the attributes of R, hence AB is Candidate Key

From the definition of Candidate Key(Candidate Key is a Super Key whose no proper subset is a Super key)

Since all key will have AB as an integral part, and we have proved that AB is Candidate Key, Therefore, any superset of AB will be Super Key but not Candidate key.

Hence there will be only one candidate key AB

Definition of 2NF: No non-prime attribute should be partially dependent on Candidate Key

Since R has 4 attributes: - A, B, C, D, and Candidate Key is AB, Therefore, prime attributes (part of candidate key) are A and B while a non-prime attribute are C and D

a) FD: AB → CD satisfies the definition of 2NF, that non-prime attribute(C and D) are fully dependent on candidate key AB

b) FD: B → C does not satisfy the definition of 2NF, as a non-prime attribute(C) is partially dependent on candidate key AB( i.e. key should not be broken at any cost)

As FD B → C, the above table R( A, B, C, D) is not in 2NF

Convert the table R(A, B, C, D) in 2NF:

Since FD: B → C, our table was not in 2NF, let's decompose the table

R1(B, C)

Since the key is AB, and from FD AB → CD, we can create R2(A, B, C, D) but this will again have a problem of partial dependency B → C, hence R2(A, B, D).

Finally, the decomposed table which is in 2NF

a) R1( B, C)

b) R2(A, B, D)

2. Given a relation R( P, Q, R, S, T) and Functional Dependency set FD = { PQ → R, S → T }, determine whether the given R is in 2NF? If not convert it into 2 NF.

Solution: Let us construct an arrow diagram on R using FD to calculate the candidate key.

QUESTIONS ON NORMALIZATION

From above arrow diagram on R, we can see that an attributes PQS is not determined by any of the given FD, hence PQS will be the integral part of the Candidate key, i.e., no matter what will be the candidate key, and how many will be the candidate key, but all will have PQS compulsory attribute.

Let us calculate the closure of PQS

PQS + = PQSRT (from the method we studied earlier)

Since the closure of PQS contains all the attributes of R, hence PQS is Candidate Key

From the definition of Candidate Key (Candidate Key is a Super Key whose no proper subset is a Super key)

Since all key will have PQS as an integral part, and we have proved that PQS is Candidate Key. Therefore, any superset of PQS will be Super Key but not Candidate key.

Hence there will be only one candidate key PQS

Definition of 2NF: No non-prime attribute should be partially dependent on Candidate Key.

Since R has 5 attributes: - P, Q, R, S, T and Candidate Key is PQS, Therefore, prime attributes (part of candidate key) are P, Q, and S while a non-prime attribute is R and T

a) FD: PQ → R does not satisfy the definition of 2NF, that non-prime attribute( R) is partially dependent on part of candidate key PQS.

b) FD: S → T does not satisfy the definition of 2NF, as a non-prime attribute(T) is partially dependent on candidate key PQS (i.e., key should not be broken at any cost).

Hence, FD PQ → R and S → T, the above table R( P, Q, R, S, T) is not in 2NF

Convert the table R( P, Q, R, S, T) in 2NF:

Since due to FD: PQ → R and S → T, our table was not in 2NF, let's decompose the table

R1(P, Q, R) (Now in table R1 FD: PQ → R is Full F D, hence R1 is in 2NF)

R2( S, T) (Now in table R2 FD: S → T is Full F D, hence R2 is in 2NF)

And create one table for the key, since the key is PQS.

R3(P, Q, S)

Finally, the decomposed tables which is in 2NF are:

a) R1( P, Q, R)

b) R2(S, T)

c) R3(P, Q, S)

3. Given a relation R( P, Q, R, S, T, U, V, W, X, Y) and Functional Dependency set FD = { PQ → R, PS → VW, QS → TU, P → X, W → Y }, determine whether the given R is in 2NF? If not convert it into 2 NF.

Solution: Let us construct an arrow diagram on R using FD to calculate the candidate key.

QUESTIONS ON NORMALIZATION

From above arrow diagram on R, we can see that an attributes PQS is not determined by any of the given FD, hence PQS will be the integral part of the Candidate key, i.e. no matter what will be the candidate key, and how many will be the candidate key, but all will have PQS compulsory attribute.

Let us calculate the closure of PQS

PQS + = P Q S R T U V W X Y (from the closure method we studied earlier)

Since the closure of PQS contains all the attributes of R, hence PQS is Candidate Key

From the definition of Candidate Key(Candidate Key is a Super Key whose no proper subset is a Super key)

Since all key will have PQS as an integral part, and we have proved that PQS is Candidate Key, Therefore, any superset of PQS will be Super Key but not a Candidate key.

Hence there will be only one candidate key PQS

Definition of 2NF: No non-prime attribute should be partially dependent on Candidate Key

Since R has 10 attributes: - P, Q, R, S, T, U, V, W, X, Y, and Candidate Key is PQS calculated using FD = { PQ → R, PS → VW, QS → TU, P → X, W → Y }. Therefore, prime attribute(part of candidate key) are P, Q, and S while non-prime attribute are R, T, U, V, W, X and Y

  1. FD: PQ → R does not satisfy the definition of 2NF, that non-prime attribute( R) is partially dependent on part of candidate key PQS
  2. FD: PS → VW does not satisfy the definition of 2NF, that non-prime attribute( VW) is partially dependent on part of candidate key PQS
  3. FD: QS → TU does not satisfy the definition of 2NF, that non-prime attribute( TU) is partially dependent on part of candidate key PQS
  4. FD: P → X does not satisfy the definition of 2NF, that non-prime attribute( X) are partially dependent on part of candidate key PQS
  5. FD: W → Y does not violate the definition of 2NF, as the non-prime attribute(Y) is dependent on the non-prime attribute(W), which is not related to the definition of 2NF.

Hence because of FD: PQ → R, PS → VW, QS → TU, P → X the above table R( P, Q, R, S, T, U, V, W, X, Y) is not in 2NF

Convert the table R( P, Q, R, S, T, U, V, W, X, Y) in 2NF:

Since due to FD: PQ → R, PS → VW, QS → TU, P → X our table was not in 2NF, let's decompose the table

R1(P, Q, R) (Now in table R1 FD: PQ → R is Full F D, hence R1 is in 2NF)

R2( P, S, V, W) (Now in table R2 FD: PS → VW is Full F D, hence R2 is in 2NF)

R3( Q, S, T, U) (Now in table R3 FD: QS → TU is Full F D, hence R3 is in 2NF)

R4( P, X) (Now in table R4 FD : P → X is Full F D, hence R4 is in 2NF)

R5( W, Y) (Now in table R5 FD: W → Y is Full F D, hence R2 is in 2NF)

And create one table for the key, since the key is PQS.

R6(P, Q, S)

Finally, the decomposed tables which is in 2NF are:

R1(P, Q, R)

R2( P, S, V, W)

R3( Q, S, T, U)

R4( P, X)

R5( W, Y)

R6(P, Q, S)

4. Given a relation R( A, B, C, D, E) and Functional Dependency set FD = { A → B, B → E, C → D}, determine whether the given R is in 2NF? If not convert it into 2 NF.

Solution: Let us construct an arrow diagram on R using FD to calculate the candidate key.

QUESTIONS ON NORMALIZATION

From above arrow diagram on R, we can see that an attributes AC is not determined by any of the given FD, hence AC will be the integral part of the Candidate key, i.e. no matter what will be the candidate key, and how many will be the candidate key, but all will have W compulsory attribute.

Let us calculate the closure of AC

AC + = ACBED( from the closure method we studied earlier)

Since the closure of AC contains all the attributes of R, hence AC is Candidate Key

From the definition of Candidate Key(Candidate Key is a Super Key whose no proper subset is a Super key)

Since all key will have AC as an integral part, and we have proved that AC is Candidate Key, Therefore, any superset of AC will be Super Key but not Candidate key.

Hence there will be only one candidate key AC

Definition of 2NF: No non-prime attribute should be partially dependent on Candidate Key

Since R has 5 attributes: - A, B, C, D, E and Candidate Key is AC, Therefore, prime attribute (part of candidate key) are A and C while the non-prime attribute are B D and E

  1. FD: A → B does not satisfy the definition of 2NF, as a non-prime attribute(B) is partially dependent on candidate key AC (i.e., key should not be broken at any cost).
  2. FD: B → E does not violate the definition of 2NF, as a non-prime attribute(E) is dependent on the non-prime attribute(B), which is not related to the definition of 2NF.
  3. FD: C → D does not satisfy the definition of 2NF, as a non-prime attribute(D) is partially dependent on candidate key AC (i.e., key should not be broken at any cost)

Hence because of FD A → B and C → D, the above table R( A, B, C, D, E) is not in 2NF

Convert the table R(A, B, C, D, E) in 2NF:

Since due to FD: A →B and C → D our table was not in 2NF, let's decompose the table

R1(A, B, E) ( from FD: A → B and B → E and both are violating 2 NF definition)

R2( C, D) (Now in table R2 FD: C → D is Full F D, hence R2 is in 2NF)

And create one table for candidate key AC

R3 ( A, C)

Finally, the decomposed tables which are in 2NF:

  1. R1( A, B, E)
  2. R2( C, D)
  3. R3( A, C)

Procedure: To verify that given relational schema R is in 2NF or NOT, If NOT then Convert it to 2NF:

STEP 1: Calculate the Candidate Key of given R by using an arrow diagram on R.

STEP 2: Verify each FD with Definition of 2NF (No non-prime attribute should be partially dependent on Candidate Key)

STEP 3: Make a set of FD which do not satisfy 2NF, i.e. all those FD which are partial.

STEP 4: Convert the table R in 2NF by decomposing R such that each decomposition based on FD should satisfy the definition of 2NF:

STEP 5: Once the decomposition based on FD is completed, create a separate table of attributes in the Candidate key.

STEP 6: All the decomposed R obtained from STEP 4 and STEP 5 forms the required decomposition where each decomposition is in 2NF.






Contact US

Email:[email protected]

QUESTIONS ON NORMALIZATION
10/30