Examples of Third normal form

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.

examples of transitive dependcy diagram

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.

 

SPECIALIZATION CODE SPECIALIZATION NAME COURSE CODE COURSE NAME
1 Computing T4TUTORIALS-CS1 BATABASE
2 Programming T4TUTORIALS-CS2 C++
3 Computing T4TUTORIALS-CS3 OPERATING SYSTEM
4 Programming T4TUTORIALS-CS4 OOP

 

SPECIALIZATION CODE SPECIALIZATION NAME COURSE CODE
1 Computing T4TUTORIALS-CS1
2 Programming T4TUTORIALS-CS2
3 Computing T4TUTORIALS-CS3
4 Programming T4TUTORIALS-CS4

 

COURSE CODE COURSE NAME
T4TUTORIALS-CS1 BATABASE
T4TUTORIALS-CS2 C++

 


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

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

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)