QUESTIONS ON THIRD NORMAL FORMTo solve the question on 3 NF, we must understand it's both definitions: Definition 1: A relational schema R is said to be in 3NF, First, it should be in 2NF and, no nonprime attribute should be transitively dependent on the Key of the table. If X → Y and Y → Z exist then X → Z also exists which is a transitive dependency, and it should not hold. Definition 2: First it should be in 2NF and if there exists a nontrivial dependency between two sets of attributes X and Y such that X → Y (i.e., Y is not a subset of X) then
Question 1: Given a relation R( X, Y, Z) and Functional Dependency set FD = { X → Y and Y → Z }, determine whether the given R is in 3NF? If not convert it into 3 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 attribute X is not determined by any of the given FD, hence X 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 X compulsory attribute. Let us calculate the closure of X X + = XYZ (from the closure method we studied earlier) Since the closure of X contains all the attributes of R, hence X 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 X as an integral part, and we have proved that X is Candidate Key, Therefore, any superset of X will be Super Key but not the Candidate key. Hence there will be only one candidate key X Definition of 3NF: A relational schema R is said to be in 3NF, First, it should be in 2NF and, no nonprime attribute should be transitively dependent on the Key of the table. If X → Y and Y → Z exist then X → Z also exists which is a transitive dependency, and it should not hold. Since R has 3 attributes:  X, Y, Z, and Candidate Key is X, Therefore, prime attribute (part of candidate key) is X while a nonprime attribute are Y and Z Given FD are X → Y and Y → Z So, we can write X → Z (which is a transitive dependency) In above FD X → Z, a nonprime attribute( Z) is transitively depending on the key of the table( X ) hence as per the definition of 3NF it is not in 3 NF, because no nonprime attribute should be transitively dependent on the key of the table. Now check the above table is in 2 NF.
Hence above table R( X, Y, Z ) is in 2NF but not in 3NF. We can also prove the same from Definition 2: First, it should be in 2NF and if there exists a nontrivial dependency between two sets of attributes X and Y such that X → Y (i.e., Y is not a subset of X) then
Since we have just proved that above table R is in 2 NF. Let's check it for 3NF using definition 2.
Hence because of Y → Z using definition 2 of 3NF, we can say that above table R is not in 3NF. Convert the table R( X, Y, Z) into 3NF: Since due to FD: Y → Z, our table was not in 3NF, let's decompose the table FD: Y → Z was creating issue, hence one table R1(Y, Z) Create one Table for key X, R2(X, Y), since X → Y Hence decomposed tables which are in 3NF are: R1(X, Y) R2(Y, Z) Question 2: Given a relation R( X, Y, Z, W, P) and Functional Dependency set FD = { X → Y, Y → P, and Z → W}, determine whether the given R is in 3NF? If not convert it into 3 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 XZ is not determined by any of the given FD, hence XZ 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 XZ compulsory attribute. Let us calculate the closure of XZ XZ + = XZYPW (from the closure method that we studied earlier) Since the closure of XZ contains all the attributes of R, hence XZ 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 XZ as an integral part, and we have proved that XZ is Candidate Key, Therefore, any superset of XZ will be Super Key but not the Candidate key. Hence there will be only one candidate key XZ Definition of 3NF: First it should be in 2NF and if there exists a nontrivial dependency between two sets of attributes X and Y such that X → Y ( i.e., Y is not a subset of X) then
Since R has 5 attributes:  X, Y, Z, W, P and Candidate Key is XZ, Therefore, prime attribute (part of candidate key) are X and Z while a nonprime attribute are Y, W, and P Given FD are X → Y, Y → P, and Z → W and Super Key / Candidate Key is XZ
Convert the table R( X, Y, Z, W, P) into 3NF: Since all the FD = { X → Y, Y → P, and Z → W} were not in 3NF, let us convert R in 3NF R1(X, Y) {Using FD X → Y} R2(Y, P) {Using FD Y → P} R3(Z, W) {Using FD Z → W} And create one table for Candidate Key XZ R4( X, Z) { Using Candidate Key XZ } All the decomposed tables R1, R2, R3, and R4 are in 2NF( as there is no partial dependency) as well as in 3NF. Hence decomposed tables are: R1(X, Y), R2(Y, P), R3( Z, W), and R4( X, Z) Question 3: Given a relation R( P, Q, R, S, T, U, V, W, X, Y) and Functional Dependency set FD = { PQ → R, P → ST, Q → U, U → VW, and S → XY}, determine whether the given R is in 3NF? If not convert it into 3 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 attribute PQ is not determined by any of the given FD, hence PQ 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 PQ compulsory attribute. Let us calculate the closure of PQ PQ + = P Q R S T U X Y V W (from the closure method we studied earlier) Since the closure of XZ contains all the attributes of R, hence PQ 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 PQ as an integral part, and we have proved that XZ is Candidate Key, Therefore, any superset of PQ will be Super Key but not Candidate key. Hence there will be only one candidate key PQ Definition of 3NF: First it should be in 2NF and if there exists a nontrivial dependency between two sets of attributes X and Y such that X → Y (i.e., Y is not a subset of X) then c) Either X is Super Key d) Or Y is a prime attribute. Since R has 10 attributes:  P, Q, R, S, T, U, V, W, X, Y, V, W and Candidate Key is PQ, Therefore, prime attribute (part of candidate key) are P and Q while a nonprime attribute are R S T U V W X Y V W Given FD are {PQ → R, P → ST, Q → U, U → VW and S → XY} and Super Key / Candidate Key is PQ
Convert the table R( X, Y, Z, W, P) into 3NF: Since all the FD = { P → ST, Q → U, U → VW, and S → XY } were not in 3NF, let us convert R in 3NF R1(P, S, T) {Using FD P → ST } R2(Q, U) {Using FD Q → U } R3( U, V, W) { Using FD U → VW } R4( S, X, Y) { Using FD S → XY } R5( P, Q, R) { Using FD PQ → R, and candidate key PQ } All the decomposed tables R1, R2, R3, R4, and R5 are in 2NF( as there is no partial dependency) as well as in 3NF. Hence decomposed tables are: R1(P, S, T), R2(Q, U), R3(U, V, W), R4( S, X, Y), and R5( P, Q, R) Conclusion: From the above three examples, we can conclude that the following steps are followed to check whether the given relational schema R is in 3 NF or not? If not, how to decompose it into 3 NF. STEP 1: Calculate the Candidate Key of given R by using an arrow diagram and then using the closure of an attribute on R, such that from the calculated candidate key, we can separate the prime attributes and nonprime attributes. STEP 2: Verify each FD with Definition of 3NF (First it should be in 2NF and if there exist a nontrivial dependency between two sets of attributes X and Y such that X → Y (i.e., Y is not a subset of X) then Either X is Super Key or Y is a prime attribute). STEP 3: Make a set of FD which does not satisfy 3NF, i.e. all those FD which do not have an attribute on the left side of FD as a super key or attribute on the right side of FD as a prime attribute. STEP 4: Convert the table R in 3NF by decomposing R such that each decomposition based on FD should satisfy the definition of 3NF. 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 3NF.
Next TopicEQUIVALENCE OF FUNCTIONAL DEPENDENCY
