## QUESTIONS ON THIRD NORMAL FORMTo solve the question on 3 NF, we must understand it's both definitions:
If X → Y and Y → Z exist then X → Z also exists which is a transitive dependency, and it should not hold.
- Either X is Super Key
- Or Y is a prime attribute.
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.
X + = XYZ (from the closure method we studied earlier) Since the closure of X contains all the attributes of R, hence From the definition of Candidate 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
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 → Y is in 2NF ( as Key is not breaking and its Fully functional dependent )
- FD: Y → Z is also in 2NF( as it does not violate the definition of 2NF)
- Either X is Super Key
- Or Y is a prime attribute.
- FD: X → Y is in 3NF (as X is a super Key)
- 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.
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:
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.
XZ + = XZYPW (from the closure method that we studied earlier) Since the closure of XZ contains all the attributes of R, hence From the definition of Candidate 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
- Either X is Super Key
- 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 - FD:
**X → Y**does not satisfy the definition of 3NF, that neither X is Super Key nor Y is a prime attribute. - FD:
**Y → P**does not satisfy the definition of 3NF, that neither Y is Super Key nor P is a prime attribute. - FD:
**Z → W**satisfies the definition of 3NF, that neither Z is Super Key nor W is a prime attribute.
And create one table for 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:
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.
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 From the definition of Candidate 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
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 - FD:
**PQ → R**satisfy the definition of 3NF, as PQ Super Key - FD:
**P → ST**does not satisfy the definition of 3NF, that neither P is Super Key nor ST is the prime attribute - FD:
**Q → U**does not satisfy the definition of 3NF, that neither Q is Super Key nor U is a prime attribute - FD:
**U → VW**does not satisfy the definition of 3NF, that neither U is Super Key nor VW is a prime attribute - FD:
**S → XY**does not satisfy the definition of 3NF, that neither S is Super Key nor XY is a prime attribute
All the decomposed tables R1, R2, R3, R4, and R5 are in 2NF( as there is no partial dependency) as well as in 3NF.
Next TopicEQUIVALENCE OF FUNCTIONAL DEPENDENCY |