QUESTIONS ON THIRD NORMAL FORM

To 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 non-prime 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 non-trivial dependency between two sets of attributes X and Y such that X → Y (i.e., Y is not a subset of X) then

1. Either X is Super Key
2. Or Y is a prime attribute.

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 non-prime 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 non-prime 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 non-prime 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 non-prime attribute should be transitively dependent on the key of the table.

Now check the above table is in 2 NF.

1. FD: X → Y is in 2NF ( as Key is not breaking and its Fully functional dependent )
2. FD: Y → Z is also in 2NF( as it does not violate the definition of 2NF)

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 non-trivial dependency between two sets of attributes X and Y such that X → Y (i.e., Y is not a subset of X) then

1. Either X is Super Key
2. Or Y is a prime attribute.

Since we have just proved that above table R is in 2 NF. Let's check it for 3NF using definition 2.

1. FD: X → Y is in 3NF (as X is a super Key)
2. FD: Y → Z is not in 3NF (as neither Y is Key nor Z is a prime attribute)

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 non-trivial dependency between two sets of attributes X and Y such that X → Y ( i.e., Y is not a subset of X) then

1. Either X is Super Key
2. Or Y is a prime attribute.

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 non-prime attribute are Y, W, and P

Given FD are X → Y, Y → P, and Z → W and Super Key / Candidate Key is XZ

1. FD: X → Y does not satisfy the definition of 3NF, that neither X is Super Key nor Y is a prime attribute.
2. FD: Y → P does not satisfy the definition of 3NF, that neither Y is Super Key nor P is a prime attribute.
3. FD: Z → W satisfies the definition of 3NF, that neither Z is Super Key nor W is a prime attribute.

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 non-trivial 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 non-prime 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

1. FD: PQ → R satisfy the definition of 3NF, as PQ Super Key
2. FD: P → ST does not satisfy the definition of 3NF, that neither P is Super Key nor ST is the prime attribute
3. FD: Q → U does not satisfy the definition of 3NF, that neither Q is Super Key nor U is a prime attribute
4. FD: U → VW does not satisfy the definition of 3NF, that neither U is Super Key nor VW is a prime attribute
5. FD: S → XY does not satisfy the definition of 3NF, that neither S is Super Key nor XY is a prime attribute

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 non-prime attributes.

STEP 2: Verify each FD with Definition of 3NF (First it should be in 2NF and if there exist a non-trivial 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.