Third Normal Form in DBMS

It is a normalization level in DBMS. A relation is said to be in 3rd normal form in DBMS (or 3NF) when it is in the second normal form, but no transitive dependency exists for a non-prime attribute.

In this article, we will take a look at the Third Normal Form in DBMS and its uses according to the GATE Syllabus for CSE (Computer Science Engineering). Read ahead to learn more.

Table of Contents

• What is the Third Normal Form in DBMS?
• Rules Followed in 3rd Normal Form in DBMS
• Uses of Third Normal Form in DBMS
• How to Normalize 1NF and 2NF to 3NF?
  • Example #1
  • Example #2
• Practice Problems on Third Normal Form in DBMS
• FAQs

What is the Third Normal Form in DBMS?

A given relation is said to be in its third normal form when it’s in 2NF but has no transitive partial dependency. Meaning, when no transitive dependency exists for the attributes that are non-prime, then the relation can be said to be in 3NF.

In simpler words,

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

Rules Followed in 3rd Normal Form in DBMS

We can say that a relation is in the third normal form when it holds any of these given conditions in case of a functional dependency P -> Q that is non-trivial:

1. P acts as a super key.
2. Q acts as a non-prime attribute. Meaning, every element of Q forms a part of a candidate key.

Uses of Third Normal Form in DBMS

We use the 3NF to reduce any duplication of data and achieve data integrity in a database. The third normal form is fit for the designing of normal relational databases. It is because a majority of the 3NF tables are free from the anomalies of deletion, updates, and insertion. Added to this, a 3NF would always ensure losslessness and preservation of the functional dependencies.

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

To normalize a 2NF to 3NF, we have to determine if we have a transitive dependency in the table. In case a transitive dependency exists, then we remove those attributes that are transitively dependent from the relations. We do this by placing these attributes in a separate, new relation. We also place the determinant’s copy along with it.

Note: If P -> Q and Q -> R are two functional dependencies, then P -> R is known as a transitive dependency. When normalizing a 2NF relation to 3NF, we remove these transitive dependencies.

Example #1

Look at the table given below for the relation CANDIDATE:

In the relation CANDIDATE given above:

Functional dependency Set:

{CAND_NO -> CAND_NAME, CAND_NO ->CAND_STATE, CAND_STATE -> CAND_CUNTRY, CAND_NO -> CAND_AGE}

So, Candidate key here would be:

{CAND_NO}

For the relation given here in the table, CAND_NO -> CAND_STATE and CAND_STATE -> CAND_COUNTRY are actually true. Thus, CAND_COUNTRY depends transitively on CAND_NO. This transitive relation violates the rules of being in the 3NF. So, if we want to convert it into the third normal form, then we have to decompose the relation CANDIDATE (CAND_NO, CAND_NAME, CAND_STATE, CAND_COUNTRY, CAND_AGE) as:

CANDIDATE (CAND_NO, CAND_NAME, CAND_STATE, CAND_STATE, CAND_AGE)

STATE_COUNTRY (STATE, COUNTRY)

Example #2

Take a look at these functional dependencies in the relation A (P, Q, R, S, T)

Here,

P -> QR,

RS -> T,

Q -> S,

T -> P

In the relation given above, all the possible candidate keys would be {P, T, RS, QR}. In this case, the attributes that exist on the right sides of all the functional dependencies are prime.

Practice Problems on Third Normal Form in DBMS

1. Decompose the following table into 3NF:

Answer: The super key in the table mentioned above would be:

{CAND_ID}, {CAND_ID, CAND_NAME}, {CAND_ID, CAND_NAME, CAND_ZIP} …. and so on

The candidate key here is: {CAND_ID}

Non-prime attributes: All the attributes in the table mentioned above are non-prime instead of CAND_ID.

Notice that CAND_CITY & CAND_STATE are dependent on CAND_ZIP, and CAND_ZIP is dependent on the CAND_ID. Here, all the non-prime attributes (CAND_CITY, CAND_STATE) are dependent transitively on the super key (CAND_ID). The transitive dependency here would violate the rules of the third normal form.

Thus, we must move the CAND_CITY and the CAND_STATE to the new table of <CANDIDATE_ZIP>, and the primary key here is CAND_ZIP.

Thus,

2. Decompose the following table into 3NF:

Answer:

Keep learning and stay tuned to get the latest updates on GATE Exam along with GATE Eligibility Criteria, GATE 2023, GATE Admit Card, GATE Syllabus for CSE (Computer Science Engineering), GATE CSE Notes, GATE CSE Question Paper, and more.

Also Explore,

• Introduction to DBMS
• Decomposition in DBMS
• First Normal Form in DBMS
• Second Normal Form in DBMS
• Functional Dependency in DBMS
• Normal Forms in DBMS
• Partial Dependency in DBMS
• Relational Model in DBMS