Third normal form addresses the tables that are already in second normal form.

What is 3rd normal form

A database table is in 3rd normal form if the following two conditions are true;

1. A database table is in second normal form and is in first normal form.
2. There is no transitive dependency. If A->B, then A can be a super key or candidate key.

What is a transitive dependency?

Transitive dependency means that a non-prime attribute A( other than candidate key) depending on another non-prime attribute B and that B is entirely dependent on the candidate key.

In the left figure, the primary key is only on “A” and on the right figure, in the first relation, the primary key is on “A” and in 2nd relation, the primary key is on B.

• A transitive dependency is an indirect dependency. Let’s see one example of transitive dependency.
  • X depends on Y
  • Y depends on Z
  • So we can say that X depends on Z

A transitive dependency exists in how many tables?
A Transitive dependency in a database is a relationship between values in the same table.
How many attributes are required for Transitive dependency?
By nature, a transitive dependency requires three or more attributes.
Transitive dependency causes what kind of anomalies?
Transitive dependency creates deletion, updating, and insertion anomalies in the database and is considered as a bad database design.

Examples of 3rd Normal Form

Normalize the following table into 3NF.

 

 

 

 

---

Example 2: from 1NF to 3NF

Normalize the following table into 1NF, 2NF and 3NF.

FIRST NORMAL FORM

In first normal form, the duplicate columns are removed.

SECOND NORMAL FORM

In case of second normal form, it contains step of first normal form in addition to removal of duplicate data which is placed in a child table

COMPANY TABLE

 

PRODUCT TABLE

 

THIRD NORMAL FORM

The third normal form include 2nd normal form and further steps are carried out. In this form the columns are removed which are not dependent on primary key columns

COMPANY TABLE

 

PRODUCT TABLE

COMPANY_PRODUCT TABLE

---

Example 4: 3NF

 

---

Example 5: 3NF

Quiz of 3NF

1. If there exists transitive dependency, then the database is in 3rd normal form?

2. Dependency shown below is the transitive dependency? Yes, No

X depends on Y,

Y depends on Z.

Z depends on Y. so X depends on Z

 

What are characteristics of third normal form?

A database table is in second normal form and there is no transitive dependency.

Third normal form addresses which dependency?

Third normal form addresses the transitive dependency.

Third normal form is based on  what concept?

Third normal form is based on the concept of removing transitive dependencies.

What is the difference between third normal form and BCNF?

In third normal, the concept is to remove the transitive dependency and in BCNF Non-prime attribute never determines prime attribute.

Converting to third normal form always avoids the problems related to dependencies. True/False

Answer: F

___ __ __ converts a table that is in third normal form to a table no longer in third normal form.​

A. ​Conversion

B. ​Denormalization

C. ​Normalization

D. Replication

Answer: B. ​De-normalization

Data in third normal form (3NF) contains which of the following?

A. partial dependencies
B. transitive dependencies
C. repeating groups
D. none of the above
Answer: D. none of the above

SQL third normal form and third normal form are same or different?

Answer: Both are same.

Converting to third normal form always avoids the problems related to dependencies.
A.True
B.False
Answer: B.False

Comparison of 3NF and 3.5 NF BCNF

Topic Covered

Why is the third normal form important? | 3rd normal form examples | 3nf in DBMS with examples | explanation of the third normal form | how to identify transitive dependency | transitive dependency diagram | transitive dependency in DBMS | transitive dependency in database normalization.

Foreign Key behaving differently on  Cascade strict and SET Null 

 

Read Tutorials about Normalization in DBMS

1. First Normal form in DBMS
2. Second normal form Examples in DBMS
3. Third normal form
4. Boyce-Codd Normal Form (3.5 NF)
5. Fourth Normal Form
6. Fifth Normal Form (5NF)