3NF in DBMS

Third Normal Form (3NF) is a further step in database normalization that ensures data is stored efficiently by eliminating transitive dependencies. A table is in 3NF if it satisfies the following conditions:

• It is in 2NF: The table must first meet all the requirements of Second Normal Form (2NF).

• No Transitive Dependency: There should be no transitive dependency, which means a non-prime attribute should not depend on another non-prime attribute.

A transitive dependency is an indirect dependency in which a non-prime attribute depends on another non-prime attribute rather than directly on the candidate key. Example: Consider the following functional dependencies X → Y Y → Z where X is a candidate key (or super key). Y is a non-prime attribute. Z is a non-prime attribute. Then, X → Z is a transitive dependency because Z is transitively dependent on X through Y.

A table may satisfy 1NF or 2NF but still violate 3NF due to transitive dependency. However, every relation in 3NF is always in both 1NF and 2NF.

 

Conditions for a Relation to be in 3NF

A relation is in Third Normal Form (3NF) if it satisfies the following conditions:

Condition 01: The relation must already be in 1NF and 2NF.

Condition 02: For Every Functional Dependency (X → Y), at Least One of the Following Must Be True

• X is a candidate key or a super key.
• Y is a prime attribute (Part of a candidate key).

Example 1: Checking if a Relation is in 3NF?

Consider the relation R(A, B, C, D), which is already in 2NF with the functional dependencies (FDs). Determine whether it is in 3NF or not.

• AB → C
• C → D

Solution

Let’s calculate the candidate key, prime, and non-prime attributes from the given FD’s

• Candidate Key (CK): {AB}
• Prime Attributes: {A, B} (attributes that are part of the candidate key).
• Non-Prime Attributes: {C, D} (attributes that are not part of the candidate key).

Following the diagram, explain Example 1 in detail

  

Let’s explain the above diagram

FD 1: AB → C

• LHS (AB) is a candidate key. So, it is a valid FD for 3NF.
• Checking the RHS is optional. No need to check because the LHS is true.

This functional dependency satisfies the condition for 3NF.

FD 2: C → D

• LHS (C) is not a candidate key or super key.
• Checking the RHS, where D is a non-prime attribute. Since D is not a prime attribute.

This functional dependency does not satisfy the condition for 3NF.

Result:

Since FD 2 does not satisfy the 3NF condition, the relation is not in 3NF.

Example 2: Checking if Another Relation is in 3NF?

Consider the relation R (ABCD) which is already in 2NF with the functional dependencies (FDs):

• AB → CD
• D → A

Solution

Let’s calculate the candidate key, prime, and non-prime attributes from the given FD’s

• Candidate Keys (CK): {AB, DB} (candidate keys).
• Prime Attributes: {A, B, D} (attributes that are part of the candidate key).
• Non-Prime Attribute: {C} (attributes that are not part of the candidate key).

Following the diagram, explain Example 2 in detail

Let’s explain the above diagram

FD 1:  AB → CD

• LHS (AB) is a candidate key (valid for 3NF).

This functional dependency satisfies the condition for 3NF.

Note: As LHS is true, we do not need to check RHS. Optionally, let’s check: As D is a prime attribute, the presence of the non-prime attribute C on the RHS means AB → CD does not satisfy 3NF.

FD 2:  D → A

• LHS (D) is not a candidate key, but RHS (A) is a prime attribute
• This functional dependency satisfies the condition for 3NF.

Result:

Since all the functional dependencies in the relation meet the conditions for 3NF, this relation is in 3NF.

Example 3: Checking if Another Relation is in 3NF?

Consider the relation R (ABCD) which is already in 2NF with the following functional dependencies (FDs):

• B → ACD
• ACD → B

Solution

Let’s calculate the candidate key, prime, and non-prime attributes from the given FDs.

• Candidate Keys (CK): {B, ACD}
• Prime Attributes: {A, B, C, D} (all attributes are part of at least one candidate key)
• Non-Prime Attributes: None

Following the diagram, explain Example 3 in detail

Let’s explain the above diagram

FD 1: B → ACD

• LHS (B) is a candidate key (valid for 3NF).
• This functional dependency satisfies the condition for 3NF.

Note: Since the LHS is a candidate key, there is no need to check the RHS. Optionally, if we check the RHS, all attributes (A, C, and D) are prime attributes, so this FD also satisfies 3NF from the RHS perspective.

FD 2: ACD → B

• LHS (ACD) is also a candidate key (valid for 3NF).
• This functional dependency satisfies the condition for 3NF.

Note: Since the LHS is a candidate key, there is no need to check the RHS. Optionally, the RHS (B) is also a prime attribute, so this FD also satisfies the 3NF condition.

Result

Since all the functional dependencies satisfy the conditions of Third Normal Form (3NF), the given relation R (ABCD) is in 3NF.

Reason: Both functional dependencies have a candidate key on the left-hand side, and every attribute in the relation is a prime attribute. Therefore, there is no violation of 3NF.

Example 4: Checking if Another Relation is in 3NF?

Consider the relation R (ABCD) which is already in 2NF with the following functional dependencies (FDs):

• A → B
• B → C
• C → A

Solution

Let’s calculate the candidate key, prime, and non-prime attributes from the given FDs.

• Candidate Keys (CK): {AD, BD, CD}
• Prime Attributes: {A, B, C, D} (all attributes are part of at least one candidate key)
• Non-Prime Attributes: None

Following the diagram, explain Example 4 in detail

Let’s explain the above diagram

FD 1: A → B

• LHS (A) is not a candidate key.
• However, RHS (B) is a prime attribute.
• Therefore, this functional dependency satisfies the condition for 3NF.

FD 2: B → C

• LHS (B) is not a candidate key.
• However, RHS (C) is a prime attribute.
• Therefore, this functional dependency satisfies the condition for 3NF.

FD 3: C → A

• LHS (C) is not a candidate key.
• However, RHS (A) is a prime attribute.
• Therefore, this functional dependency satisfies the condition for 3NF.

Result

Since all the functional dependencies satisfy the conditions of Third Normal Form (3NF), the given relation R (ABCD) is in 3NF.

Reason: Although A, B, and C are not candidate keys, the right-hand side of every functional dependency is a prime attribute. Also, all attributes are prime attributes, so there is no violation of 3NF.

Example 5: Checking if Another Relation is in 3NF?

Consider the relation R (ABCD) which is already in 2NF with the following functional dependencies (FDs):

• AB → CD
• D → A

Solution

Let’s calculate the candidate key, prime, and non-prime attributes from the given FDs.

• Candidate Keys (CK): {AB, BD}
• Prime Attributes: {A, B, D} (attributes that are part of at least one candidate key)
• Non-Prime Attribute: {C}

Following the diagram, explain Example 5 in detail

Let’s explain the above diagram

FD 1: AB → CD

• LHS (AB) is a candidate key (valid for 3NF).
• Therefore, this functional dependency satisfies the condition for 3NF.

Note: Since the LHS is a candidate key, there is no need to check the RHS. Although C is a non-prime attribute, the FD still satisfies 3NF because the left-hand side is a candidate key.

FD 2: D → A

• LHS (D) is not a candidate key.
• However, RHS (A) is a prime attribute.
• Therefore, this functional dependency satisfies the condition for 3NF.

Result

Since all the functional dependencies satisfy the conditions of Third Normal Form (3NF), the given relation R (ABCD) is in 3NF.

Reason: The first functional dependency satisfies 3NF because its LHS is a candidate key, while the second functional dependency satisfies 3NF because its RHS is a prime attribute. Therefore, there is no violation of 3NF.

Example 6: Checking if Another Relation is in 3NF?

Consider the relation R (ABCDEF) which is already in 2NF with the following functional dependencies (FDs):

• AB → C
• C → D
• B → AE

Solution

Let’s calculate the candidate key, prime, and non-prime attributes from the given FDs.

• Candidate Key (CK): {BF}
• Prime Attributes: {B, F} (attributes that are part of the candidate key)
• Non-Prime Attributes: {A, C, D, E}

Following the diagram, explain Example 6 in detail

Let’s explain the above diagram

FD 1: AB → C

• LHS (AB) is not a candidate key (the candidate key is BF).
• RHS (C) is a non-prime attribute.
• Therefore, this functional dependency does not satisfy the conditions for 3NF.

FD 2: C → D

• LHS (C) is not a candidate key.
• RHS (D) is a non-prime attribute.
• Therefore, this functional dependency does not satisfy the conditions for 3NF.

FD 3: B → AE

• LHS (B) is not a candidate key.
• RHS (A, E) are non-prime attributes.
• Therefore, this functional dependency does not satisfy the conditions for 3NF.

Result

Since one or more functional dependencies violate the conditions of Third Normal Form (3NF), the given relation R (ABCDEF) is not in 3NF.

Reason: None of the given functional dependencies have a candidate key on the left-hand side, and their right-hand side contains non-prime attributes. Therefore, they violate the 3NF condition.

Example 7: Checking if Another Relation is in 3NF?

Consider the relation R (ABCD) which is already in 2NF with the following functional dependencies (FDs):

• AB → CD
• C → A
• D → B

Solution

Let’s calculate the candidate key, prime, and non-prime attributes from the given FDs.

• Candidate Keys (CK): {AB, BC, CD, AD}
• Prime Attributes: {A, B, C, D} (all attributes are part of at least one candidate key)
• Non-Prime Attributes: None

Following the diagram, explain Example 7 in detail

Let’s explain the above diagram

FD 1: AB → CD

• LHS (AB) is a candidate key (valid for 3NF).
• This functional dependency satisfies the condition for 3NF.

Note: Since the LHS is a candidate key, there is no need to check the RHS. Optionally, C and D are also prime attributes, so this FD satisfies 3NF from the RHS perspective as well.

FD 2: C → A

• LHS (C) is not a candidate key.
• However, RHS (A) is a prime attribute.
• Therefore, this functional dependency satisfies the condition for 3NF.

FD 3: D → B

• LHS (D) is not a candidate key.
• However, RHS (B) is a prime attribute.
• Therefore, this functional dependency satisfies the condition for 3NF.

Result

Since all the functional dependencies satisfy the conditions of Third Normal Form (3NF), the given relation R (ABCD) is in 3NF.

Reason: The functional dependency AB → CD satisfies 3NF because AB is a candidate key. The functional dependencies C → A and D → B satisfy 3NF because their right-hand side attributes (A and B) are prime attributes. Therefore, there is no violation of 3NF.

Example 8: Checking if Another Relation is in 3NF?

Consider the relation R (ABCDE) which is already in 2NF with the following functional dependencies (FDs):

• AB → CD
• D → A
• BC → DE

Solution

Let’s calculate the candidate key, prime, and non-prime attributes from the given FDs.

• Candidate Keys (CK): {AB, BD, BC}
• Prime Attributes: {A, B, C, D} (attributes that are part of at least one candidate key)
• Non-Prime Attribute: {E} (attribute that is not part of any candidate key)

Following the diagram, explain Example 8 in detail

Let’s explain the above diagram

FD 1: AB → CD

• LHS (AB) is a candidate key (valid for 3NF).
• This functional dependency satisfies the condition for 3NF.

Note: Since the LHS is a candidate key, there is no need to check the RHS. Optionally, although C is a prime attribute and D is also a prime attribute, the FD satisfies 3NF because its left-hand side is a candidate key.

FD 2: D → A

• LHS (D) is not a candidate key.
• However, RHS (A) is a prime attribute.
• This functional dependency satisfies the condition for 3NF.

FD 3: BC → DE

• LHS (BC) is a candidate key (valid for 3NF).
• This functional dependency satisfies the condition for 3NF.

Note: Since the LHS is a candidate key, there is no need to check the RHS. Optionally, although E is a non-prime attribute, the FD satisfies 3NF because its left-hand side is a candidate key.

Result

Since all the functional dependencies in the relation satisfy the conditions for Third Normal Form (3NF), the given relation R (ABCDE) is in 3NF.

Reason: The functional dependencies AB → CD and BC → DE satisfy 3NF because their left-hand sides are candidate keys, while D → A satisfies 3NF because its right-hand side is a prime attribute. Therefore, there is no violation of 3NF.

 

Example 9: Checking if Another Relation is in 3NF?

Consider the relation R (W, X, Y, Z) which is already in 2NF with the following functional dependencies (FDs):

• Z → W
• Y → XZ
• XW → Y

Solution

Let’s calculate the candidate key, prime, and non-prime attributes from the given FDs.

• Candidate Keys (CK): {Y, XW, ZW}
• Prime Attributes: {W, X, Y, Z} (all attributes are part of at least one candidate key)
• Non-Prime Attributes: None

Following the diagram, explain Example 9 in detail

Let’s explain the above diagram

FD 1: Z → W

• LHS (Z) is not a candidate key.
• However, RHS (W) is a prime attribute.
• This functional dependency satisfies the condition for 3NF.

FD 2: Y → XZ

• LHS (Y) is a candidate key (valid for 3NF).
• This functional dependency satisfies the condition for 3NF.

Note: Since the LHS is a candidate key, there is no need to check the RHS. Optionally, both X and Z are prime attributes, so this FD also satisfies 3NF from the RHS perspective.

FD 3: XW → Y

• LHS (XW) is a candidate key (valid for 3NF).
• This functional dependency satisfies the condition for 3NF.

Note: Since the LHS is a candidate key, there is no need to check the RHS. Optionally, Y is a prime attribute, so this FD also satisfies 3NF from the RHS perspective.

Result

Since all the functional dependencies in the relation satisfy the conditions for Third Normal Form (3NF), the given relation R (W, X, Y, Z) is in 3NF.

Reason: The functional dependencies Y → XZ and XW → Y satisfy 3NF because their left-hand sides are candidate keys, while Z → W satisfies 3NF because its right-hand side is a prime attribute. Therefore, there is no violation of 3NF.

Example 10: Checking if Another Relation is in 3NF?

Consider the relation R (A, B, C, D, E, F) which is already in 2NF with the following functional dependencies (FDs):

• AB → C
• C → DE
• E → F
• D → A
• C → B

Solution

Let’s calculate the candidate key, prime, and non-prime attributes from the given FDs.

• Candidate Keys (CK): {AB, BD, C}
• Prime Attributes: {A, B, C, D} (attributes that are part of at least one candidate key)
• Non-Prime Attributes: {E, F} (attributes that are not part of any candidate key)

Following the diagram, explain Example 10 in detail

Let’s explain the above diagram

FD 1: AB → C

• LHS (AB) is a candidate key (valid for 3NF).

This functional dependency satisfies the condition for 3NF.

Note: Since the LHS is a candidate key, there is no need to check the RHS. Optionally, C is also a prime attribute, so this FD satisfies 3NF.

FD 2: C → DE

• LHS (C) is a candidate key (valid for 3NF).

This functional dependency satisfies the condition for 3NF.

Note: Since the LHS is a candidate key, there is no need to check the RHS. Optionally, although E is a non-prime attribute, the FD satisfies 3NF because its left-hand side is a candidate key.

FD 3: E → F

• LHS (E) is not a candidate key.
• RHS (F) is a non-prime attribute.

This functional dependency does not satisfy the condition for 3NF.

FD 4: D → A

• LHS (D) is not a candidate key.
• However, RHS (A) is a prime attribute.

This functional dependency satisfies the condition for 3NF.

FD 5: C → B

• LHS (C) is a candidate key (valid for 3NF).

This functional dependency satisfies the condition for 3NF.

Note: Since the LHS is a candidate key, there is no need to check the RHS. Optionally, B is also a prime attribute, so this FD satisfies 3NF.

Result

Since the functional dependency E → F violates the conditions of Third Normal Form (3NF), the given relation R (A, B, C, D, E, F) is not in 3NF.

Reason: The functional dependencies AB → C, C → DE, and C → B satisfy 3NF because their left-hand sides are candidate keys, while D → A satisfies 3NF because its right-hand side is a prime attribute. However, E → F violates 3NF because E is not a candidate key and F is a non-prime attribute. Therefore, the relation is not in 3NF.

3NF Decomposition Example

Consider the following relation: R(A, B, C, D, E, F) with Functional Dependencies (FDs)

• A → BC
• B → D
• CD → E
• E → F

Step 1: Find the Candidate Key

• Candidate Key = {A}
• Prime Attribute: A
• Non-Prime Attributes: B, C, D, E, F

Step 2: Check for 3NF Violations

A relation is in 3NF if for every FD X → Y, at least one of the following holds:

• X is a super key, or
• Every attribute in Y is a prime attribute.

FD	LHS is CK or Super Key?	RHS Prime?	3NF
A → BC	Yes	No Need to Check	Yes
B → D	No	No	Violates
CD → E	No	No	Violates
E → F	No	No	Violates

Since three FDs violate 3NF, decomposition is required.

Step 3: Decompose into 3NF

Create one relation for each violating dependency.

• First violating dependency: B → D
  • First Relation R1 will be  R1 (B, D), where the candidate key will be {B}
• Second violating dependency: CD → E
  • Second Relation R2 will be  R2(C, D, E) where candidate key will be {C,D}
• Third violating dependency: E → F
  • Third Relation R3 will be  R2(E,F) where candidate key will be {E}

Create one relation for the remaining FD by keeping the original candidate key to ensure a lossless join.

• So, by using the FD which is A → BC, we create a table that is R₄(A, B, C), where the candidate key is {A}

Final 3NF Decomposition

• R₁(B, D)
• R₂(C, D, E)
• R₃(E, F)
• R₄(A, B, C)

Verify Each Relation

Relation	Functional Dependency	Candidate Key	In 3NF?
R₁(B, D)	B → D	{B}	Yes
R₂(C, D, E)	CD → E	{CD}	Yes
R₃(E, F)	E → F	{E}	Yes
R₄(A, B, C)	A → BC	{A}	Yes

Important: This decomposition is lossless (because it includes a relation containing the candidate key A) and dependency-preserving (each original functional dependency appears in one of the decomposed relations).

Does 3NF Always Have Lossless Join and Dependency Preservation?

Yes. When we decompose a relation into 3NF using the 3NF Synthesis Algorithm, it always satisfies these two properties:

• Lossless Join
• Dependency Preservation

1. Lossless Join

A lossless join means that after splitting a relation into smaller tables, you can join those tables again and get the same original relation. In simple words: Splitting the table does not lose any data.

2. Dependency Preservation

A dependency-preserving decomposition means that all the original functional dependencies (FDs) are still available in the new tables. In simple words: All the original rules (FDs) are preserved after decomposition.

3NF vs BCNF

Property	3NF	BCNF
Removes redundancy	Yes	Yes (more completely)
Lossless Join	Always (using the standard 3NF synthesis algorithm)	Always
Dependency Preservation	Always (using the standard 3NF synthesis algorithm)	Not always

Benefits of Third Normal Form (3NF)

Third Normal Form (3NF) improves database design by removing transitive dependencies. This makes the database more efficient, accurate, and easier to maintain.

Benefit	Description
Reduces Data Redundancy	Eliminates unnecessary duplicate data.
Eliminates Transitive Dependency	Non-prime attributes depend only on candidate keys.
Prevents Update Anomalies	Data is updated in only one place.
Prevents Insertion Anomalies	New data can be inserted without unrelated information.
Prevents Deletion Anomalies	Deleting records does not unintentionally remove important data.
Improves Data Integrity	Ensures accurate and consistent data.
Easier Maintenance	Simplifies database updates and modifications.
Improves Data Consistency	Reduces inconsistent values caused by duplication.
Better Database Design	Creates well-organized and logically structured tables.
Efficient Storage	Saves storage space by removing duplicate data.

Conclusion

Third Normal Form (3NF) is an important step in database normalization, as it removes transitive dependencies, ensuring that non-prime attributes depend only on the primary key. This leads to reduced data redundancy, improved data integrity, and better performance for updates and queries.

• A table is in 3NF if:

  • It is in 2NF.

  • There are no transitive dependencies.

By converting to 3NF, we ensure that the database is logically organized and optimized for data consistency and query efficiency.