Database Normalization: 3NF Explained with Examples
Understanding 3NF (Third Normal Form) in Databases
This document explains the concept of Third Normal Form (3NF) in database normalization, including examples and step-by-step conversion.
Question 1: 3NF Determination and Conversion
Given a relation R(X, Y, Z) and Functional Dependency set FD = {X → Y and Y → Z}, determine if R is in 3NF. If not, convert it into 3NF.
Candidate Key Identification
Let’s calculate the closure of X: X+ = XYZ (from the closure method). Since the closure of X contains all attributes of R, X is a Candidate Key. A candidate key is a super key whose no proper subset is a super key.
Since all keys will have X as an integral part, and we’ve proven X is a Candidate Key, any superset of X will be a Super Key but not a Candidate Key. Therefore, there is only one candidate key: X.
3NF Definition
A relational schema R is in 3NF if:
- It is in 2NF.
- No non-prime attribute is 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.
Analysis of R(X, Y, Z)
R has 3 attributes: X, Y, Z. The Candidate Key is X. Therefore, the prime attribute (part of the candidate key) is X, while non-prime attributes are Y and Z.
Given FDs: X → Y and Y → Z. We can derive X → Z (transitive dependency).
In FD X → Z, a non-prime attribute (Z) is transitively dependent on the key (X). Therefore, as per the 3NF definition, it is not in 3NF because no non-prime attribute should be transitively dependent on the key.
2NF Check
The table is in 2NF:
- FD: X → Y is in 2NF (Key is not broken, and it’s fully functionally dependent).
- FD: Y → Z is also in 2NF (it does not violate the definition of 2NF).
3NF Check (Definition 2)
FD: X → Y is in 3NF (as X is a super key). FD: Y → Z is not in 3NF (neither Y is a key nor Z is a prime attribute).
Because of Y → Z, the table R is not in 3NF.
Converting to 3NF
Due to FD: Y → Z, the table was not in 3NF. Decompose the table. FD: Y → Z was the issue, so create table R1(Y, Z). Create a table for key X, R2(X, Y), since X → Y.
Decomposed tables in 3NF are: R1(Y, Z), R2(X, Y)
Question 2: 3NF Determination and Conversion
Given a relation R(X, Y, Z, W, P) and FD set FD = {X → Y, Y → P, and Z → W}, determine if R is in 3NF. If not, convert it into 3NF.
Candidate Key Identification
Let’s calculate the closure of XZ: XZ+ = XZYPW. Since the closure of XZ contains all attributes of R, XZ is a Candidate Key.
Since all keys will have XZ as an integral part, and we’ve proven XZ is a Candidate Key, any superset of XZ will be a Super Key but not a Candidate Key. Therefore, there is only one candidate key: XZ.
Analysis of R(X, Y, Z, W, P)
R has 5 attributes: X, Y, Z, W, P. The Candidate Key is XZ. Therefore, prime attributes (part of the candidate key) are X and Z, while non-prime attributes are Y, W, and P.
Given FDs: X → Y, Y → P, and Z → W, and Super Key / Candidate Key is XZ.
- FD: X → Y does not satisfy the 3NF definition (neither X is a Super Key nor Y is a prime attribute).
- FD: Y → P does not satisfy the 3NF definition (neither Y is a Super Key nor P is a prime attribute).
- FD: Z → W satisfies the 3NF definition (neither Z is a Super Key nor W is a prime attribute).
Converting to 3NF
Since all FDs = {X → Y, Y → P, and Z → W} were not in 3NF, convert R to 3NF:
R1(X, Y) {Using FD X → Y}. R2(Y, P) {Using FD Y → P}. R3(Z, W) {Using FD Z → W}. Create a table for Candidate Key XZ: R4(X, Z) {Using Candidate Key XZ}
All decomposed tables R1, R2, R3, and R4 are in 2NF (as there is no partial dependency) and 3NF.
Decomposed tables are: R1(X, Y), R2(Y, P), R3(Z, W), and R4(X, Z)
Question 3: 3NF Determination and Conversion
Given a relation R(P, Q, R, S, T, U, V, W, X, Y) and FD set FD = {PQ → R, P → ST, Q → U, U → VW, and S → XY}, determine if R is in 3NF. If not, convert it into 3NF.
Candidate Key Identification
Let’s calculate the closure of PQ: PQ+ = PQRSTUVWXY. Since the closure contains all attributes of R, PQ is a Candidate Key.
Since all keys will have PQ as an integral part, and we’ve proven PQ is a Candidate Key, any superset of PQ will be a Super Key but not a Candidate Key. Therefore, there is only one candidate key: PQ.
Analysis of R(P, Q, R, S, T, U, V, W, X, Y)
R has 10 attributes: P, Q, R, S, T, U, V, W, X, Y. The Candidate Key is PQ. Therefore, prime attributes (part of the candidate key) are P and Q, while non-prime attributes are R, S, T, U, V, W, X, and Y.
Given FDs: {PQ → R, P → ST, Q → U, U → VW and S → XY} and Super Key / Candidate Key is PQ.
- FD: PQ → R satisfies the 3NF definition, as PQ is a Super Key.
- FD: P → ST does not satisfy the 3NF definition (neither P is a Super Key nor ST is a prime attribute).
- FD: Q → U does not satisfy the 3NF definition (neither Q is a Super Key nor U is a prime attribute).
- FD: U → VW does not satisfy the 3NF definition (neither U is a Super Key nor VW is a prime attribute).
- FD: S → XY does not satisfy the 3NF definition (neither S is a Super Key nor XY is a prime attribute).
Converting to 3NF
Since all FDs = {P → ST, Q → U, U → VW, and S → XY} were not in 3NF, convert R to 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 decomposed tables R1, R2, R3, R4, and R5 are in 2NF (as there is no partial dependency) and 3NF.
Decomposed tables are: R1(P, S, T), R2(Q, U), R3(U, V, W), R4(S, X, Y), and R5(P, Q, R)
Conclusion: Steps to 3NF Conversion
From the examples, the following steps are used to check if a relational schema R is in 3NF and, if not, how to decompose it into 3NF:
- STEP 1: Calculate the Candidate Key of R using an arrow diagram and attribute closure. Identify prime and non-prime attributes.
- STEP 2: Verify each FD with the 3NF definition: If X → Y, then either X is a Super Key or Y is a prime attribute.
- STEP 3: Identify FDs that violate 3NF (left side is not a super key, and right side is not a prime attribute).
- STEP 4: Convert R to 3NF by decomposing R based on the violating FDs. Each decomposition should satisfy the 3NF definition.
- STEP 5: Create a separate table for the attributes in the Candidate Key.
- STEP 6: The decomposed relations from STEP 4 and STEP 5 form the 3NF decomposition.