QUESTIONS ON NORMALIZATIONQuestion 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. 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 nonprime 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 nonprime attribute are C and D a) FD: AB → CD satisfies the definition of 2NF, that nonprime attribute(C and D) are fully dependent on candidate key AB b) FD: B → C does not satisfy the definition of 2NF, as a nonprime 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. 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 nonprime 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 nonprime attribute is R and T a) FD: PQ → R does not satisfy the definition of 2NF, that nonprime attribute( R) is partially dependent on part of candidate key PQS. b) FD: S → T does not satisfy the definition of 2NF, as a nonprime 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. 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 nonprime 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 nonprime attribute are R, T, U, V, W, X and Y
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. 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 nonprime 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 nonprime attribute are B D and E
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:
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 nonprime 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. Next TopicQUESTIONS ON THIRD NORMAL FORM
