Third normal form Examples

By: Prof. Fazal Rehman Shamil
Last modified on August 12th, 2020

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.

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.

examples of transitive dependcy diagram

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

 

What is Transitive Dependencyin database 3NF

third normal form transitive dependency in database

Explanation of the figure:

Attribute names with a red background are the attribute with the primary key.

Attribute names with a green background are the non-key attributes.

If we know the city name then we can easily get the country name. Similarly, if we know the registration_no of the student then we can easily get his/her city. So this is the situation where transitive dependency occurs.

The country depends upon the city and the city depends upon the Registration_no(Primary Key Attribute).

So we need to remove this transitive dependency. To satisfy this requirement of eliminating the transitive dependency, we decompose the big table into two simpler normalized tables.

After converting this big table into two efficient tables, now we can say that our tables are satisfying the third normal form.

Comparison of 3NF and 3.5 NF BCNF

Properties 3NF BCNF
Introduced by Edgar F. Codd Raymond F. Boyce and Edgar F. Codd jointly proposed
Pre-requisite Tables must  be in the 2nd Normal Form.

 

Tables must  be in the Third Normal Form.

 

Ensure Avoids transitive dependency. If A determines B, then A must be a super key.
Quality of the tables Less More
Non-key Determinants Can have non-key attributes as determinants Cannot have non-key attributes as determinants
Decomposition Loss-less join decomposition can be achieved Sometimes loss-less join decomposition cannot be achieved
Achievability Always achievable Not always Always achievable

Video Lecture

Examples of 3rd Normal Form

Example 2: 3NF

Normalize the following table into 3NF.

UNIT CODE UNIT NAME COURSE CODE COURSE NAME
1234 DATABASE T4TUTORIALS-CS1 COMPUTING
5678 C++ T4TUTORIALS-CS1 COMPUTING
7895 OPERATING SYSTEM T4TUTORIALS-CS1 COMPUTING
4765 OOP T4TUTORIALS-CS2 BUSINESS&

COMPUTING

 TRUE TABLE OF 3RD NORMAL FORM IS:

UNIT CODE UNIT NAME COURSE NAME
1234 BATABASE T4TUTORIALS-CS1
5678 C++ T4TUTORIALS-CS1
7895 OPERATING SYSTEM T4TUTORIALS-CS1
4765 OOP T4TUTORIALS-CS2

 

COURSE CODE COURSE NAME
T4TUTORIALS-CS1 COMPUTING
T4TUTORIALS-CS2 BUSINESS

 

Example 2: from 1NF to 3NF

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

Com id Com Name Prod id Prod Name Prod Quantity
1. New Electronics T4Tutorials1 LCD 333
T4Tutorials2 LED 100
2. Khan Electronic T4Tutorials3 Monitor 140
3. Neon Electronics T4Tutorials3 UPS 565

FIRST NORMAL FORM

In first normal form, the duplicate columns are removed.

First Normal form
Com id Com Name Prod id Prod Name Prod Quantity
1. New Electronics T4Tutorials1 LCD 333
1. New Electronics T4Tutorials2 LED 100
2. Khan Electronic T4Tutorials3 Monitor 140
3. Neon Electronics T4Tutorials3 UPS 565

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

Company Table
Com id Com Name
1.      New Electronics
1. New Electronics
2.      Khan Electronic
3.      Neon Electronics

 

PRODUCT TABLE

Product Table
Prod id Prod Name Prod Quantity
T4Tutorials1 LCD 333
T4Tutorials2 LED 100
T4Tutorials3 Monitor 140
T4Tutorials3 UPS 565

 

Company Table
Com id Prod id
1.      T4Tutorials1
1. T4Tutorials2
2.      T4Tutorials3
3.      T4Tutorials3

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

Company Table
Com id Com Name
1.      New Electronics
1. New Electronics
2.      Khan Electronic
3.      Neon Electronics

 

PRODUCT TABLE

Product Table
Prod id Prod Name Prod Quantity
T4Tutorials1 LCD 333
T4Tutorials2 LED 100
T4Tutorials3 Monitor 140
T4Tutorials3 UPS 565

COMPANY_PRODUCT TABLE

Company_Poduct Table
Com id Prod id
1.      T4Tutorials1
1. T4Tutorials2
2.      T4Tutorials3
3.      T4Tutorials3

Example 4 : 3NF

 

Student ID Student Name Subject ID Subject Address
18-Uni-205 Ali 01 DATABASE California
19-Uni-335 Rashid 02 DLD Pakistan
17-Uni-832 Zafar 03 C++ United States
18-Uni-192 Asad 04 DLD United Kingdom

 

Subject ID Subject
01 DATABASE
02 DLD
03 C++
04 DLD
Student ID Student Name Student ID Address
18-Uni-205 Ali 01 California
19-Uni-335 Rashid 02 Pakistan
17-Uni-832 Zafar 03 United States
18-Uni-192 Asad 04 United Kingdom

Example 5: 3NF

Id Name Region Country
1 T4Tutorials A Region A US
2 T4Tutorials B Region B UK
3 T4Tutorials C Region C France
4 T4Tutorials D Region D Pakistan
5 T4Tutorials E Region E Pakistan
Id Name Region
1 T4Tutorials A Region A
2 T4Tutorials B Region B
3 T4Tutorials C Region C
4 T4Tutorials D Region D
5 T4Tutorials E Region E
Region Country
Region A US
Region B UK
Region C France
Region D Pakistan
Region E Pakistan

Quiz of 3NF

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

Answer - Click Here:

No

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

Answer - Click Here:

Yes

 

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

Excercise 1NF to 5NF

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.

Prof. Fazal Rehman Shamil
Latest posts by Prof. Fazal Rehman Shamil (see all)