What is Third Normal Form in DBMS? - Condition, Uses, Practice Problems

By Anjnee Bhatnagar|Updated : September 2nd, 2022

In DBMS, we use normalization to minimize the redundancy or repetition of data. It is defined as dividing extensive relations (tables) into smaller ones and organizing them to eliminate database anomalies. There exists First Normal Form (1 NF), Second Normal Form (2NF), Third Normal Form in DBMS (3NF), and Boyce Codd Normal Form (BCNF).

In this article, we will explain the third normal form in DBMS in detail. A relation is said to be in the third normal form (3 NF) if it is in the second normal form and there exists no transitive dependency for non-prime attributes.

Table of Content

What is the Third Normal Form in DBMS?

A relation is said to be in the third normal form only if it is in the second normal form and has no transitive dependencies for non-prime attributes. If, for instance, a relation is not in the first normal form, then it can never be in the second normal form and, in turn, in the third normal form. Also, if a relationship is in the first normal form and not in the second normal form, it can never be in the third normal form.

The third normal form is considered the safest as it satisfies lossless join and dependency preserving conditions for decomposing a relation. The relationship among the normal forms is depicted by the Venn diagram below:

Third Normal Form in DBMS

Conditions Followed for Third Normal Form in DBMS

A relation schema R is said to be in the third normal form if it is in the second normal form and follows the below conditions for every non-trivial functional dependency XY in R:

  • X must be the Super Key OR
  • Y must be the prime attribute.

Also, the following dependencies of the form:

(Proper subset of candidate keys) (Proper subset of other candidate keys)

forms redundancy and is still allowed in the third normal form.

Uses of Third Normal Form in DBMS

We employ the 3NF to minimize data duplication and achieve database integrity. The third normal form is appropriate for the design of typical relational databases. Most 3NF tables aren't affected by deletion, update, and insertion anomalies. A 3NF would additionally always guarantee lossless and the dependency preservation of the functional dependencies.

Moreover, among all the normal forms, the third normal form is considered the most accurate normal form because lossless join and dependency preserving is always possible. Not every relation can decompose into BCNF with dependency preserving.

How to Normalize 1NF and 2NF to 3NF?

We must ascertain whether the table contains a transitive dependency to normalize a 2NF to a 3NF. If a transitive dependency is found, the transitively dependent attributes are eliminated from the relations. This is accomplished by putting these attributes in a distinct, new relation. We also include a copy of the determinant.

Note: X → Z is referred to be a transitive dependency if X → Y and Y → Z are two functional dependencies. We eliminate these transitive dependencies while normalizing a 2NF relation to 3NF.

Practice Problem on Third Normal Form in DBMS

We have discussed the following conditions to achieve the third normal form for a relation. Now, to understand better, we will see an example.

Example:

Consider the following functional dependencies for relation R (A, B, C, D, E, F) and find the normal form.

AB C

C D

CD BE

DE F

EF A

Solution:

To begin with, we first find the candidate keys for the relation. The candidate keys are: {C, AB, BEF, BDE}

Now we divide the attributes into prime and non-prime attributes.

Prime attribute set: {A, B, C, D, E, F }, all the attributes are prime. Therefore, the relation is in the third normal form or 3NF. The relation follows the condition to be in the third normal form that is:

  • X must be the Super Key OR
  • Y must be the prime attribute.

Comments

write a comment

FAQs on Third Normal Form in DBMS

  • When a given relation is in 2NF but lacks a transitive partial dependency, it is said to be in  its third normal form. Meaning that the relation can be stated to be in 3NF when there is no transitive dependency for the non-prime attributes.

    In other words, we can state that a relation is in the third normal form or 3NF when none of the non-primary key attributes transitively depend on their primary keys in a relationship that is in 1NF or 2NF.

  • The third normal form of a given relation is when it is in 2NF but lacks transitive partial dependencies. It can be claimed that a relation is in 3NF when there is no transitive dependency for the non-prime attributes.

  • The basic normal forms used in database normalization are the first, second, and third normal forms: Each attribute in the relation is atomic, according to the relation's first normal form (1NF). Non-prime attributes must be functionally dependent on the entire candidate key, according to the second normal form (2NF). And a relation in third normal form should either have a super key on LHS or a prime attribute on RHS for every functional dependency.

  • The amount of redundant data is minimized with 3NF. Additionally, it is employed to ensure data integrity. The relation must be in third normal form if non-prime characteristics do not have transitive dependencies.

  • We say that a relation is in the third normal form when it holds the given conditions in case of a functional dependency X → Y that is non-trivial:

    • X acts as a super key.
    • Y acts as a non-prime attribute. Meaning that every element of Y forms a part of a candidate key.

Follow us for latest updates