Third normal form Examples

Download This Tutorial in PDF

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

Properties3NFBCNF
Introduced byEdgar F. CoddRaymond F. Boyce and Edgar F. Codd jointly proposed
Pre-requisiteTables must  be in the 2nd Normal Form.

 

Tables must  be in the Third Normal Form.

 

EnsureAvoids transitive dependency.If A determines B, then A must be a super key.
Quality of the tablesLessMore
Non-key DeterminantsCan have non-key attributes as determinantsCannot have non-key attributes as determinants
DecompositionLoss-less join decomposition can be achievedSometimes loss-less join decomposition cannot be achieved
AchievabilityAlways achievableNot always Always achievable

Video Lecture

Examples of 3rd Normal Form

Example 2: 3NF

Normalize the following table into 3NF.

UNIT CODEUNIT NAMECOURSE CODECOURSE NAME
1234DATABASET4TUTORIALS-CS1COMPUTING
5678C++T4TUTORIALS-CS1COMPUTING
7895OPERATING SYSTEMT4TUTORIALS-CS1COMPUTING
4765OOPT4TUTORIALS-CS2BUSINESS&

COMPUTING

 TRUE TABLE OF 3RD NORMAL FORM IS:

UNIT CODEUNIT NAMECOURSE NAME
1234BATABASET4TUTORIALS-CS1
5678C++T4TUTORIALS-CS1
7895OPERATING SYSTEMT4TUTORIALS-CS1
4765OOPT4TUTORIALS-CS2

 

COURSE CODECOURSE NAME
T4TUTORIALS-CS1COMPUTING
T4TUTORIALS-CS2BUSINESS

 

Example 2: from 1NF to 3NF

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

Com idCom NameProd idProd NameProd Quantity
1.New ElectronicsT4Tutorials1LCD333
T4Tutorials2LED100
2.Khan ElectronicT4Tutorials3Monitor140
3.Neon ElectronicsT4Tutorials3UPS565

FIRST NORMAL FORM

In first normal form, the duplicate columns are removed.

First Normal form
Com idCom NameProd idProd NameProd Quantity
1.New ElectronicsT4Tutorials1LCD333
1.New ElectronicsT4Tutorials2LED100
2.Khan ElectronicT4Tutorials3Monitor140
3.Neon ElectronicsT4Tutorials3UPS565

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 idCom Name
1.     New Electronics
1.New Electronics
2.     Khan Electronic
3.     Neon Electronics

 

PRODUCT TABLE

Product Table
Prod idProd NameProd Quantity
T4Tutorials1LCD333
T4Tutorials2LED100
T4Tutorials3Monitor140
T4Tutorials3UPS565

 

Company Table
Com idProd 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 idCom Name
1.     New Electronics
1.New Electronics
2.     Khan Electronic
3.     Neon Electronics

 

PRODUCT TABLE

Product Table
Prod idProd NameProd Quantity
T4Tutorials1LCD333
T4Tutorials2LED100
T4Tutorials3Monitor140
T4Tutorials3UPS565

COMPANY_PRODUCT TABLE

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

Example 4 : 3NF

 

Student IDStudent NameSubject IDSubjectAddress
18-Uni-205Ali01DATABASECalifornia
19-Uni-335Rashid02DLDPakistan
17-Uni-832Zafar03C++United States
18-Uni-192Asad04DLDUnited Kingdom

 

Subject IDSubject
01DATABASE
02DLD
03C++
04DLD
Student IDStudent NameStudent IDAddress
18-Uni-205Ali01California
19-Uni-335Rashid02Pakistan
17-Uni-832Zafar03United States
18-Uni-192Asad04United Kingdom

Example 5: 3NF

IdNameRegionCountry
1T4Tutorials ARegion AUS
2T4Tutorials BRegion BUK
3T4Tutorials CRegion CFrance
4T4Tutorials DRegion DPakistan
5T4Tutorials ERegion EPakistan
Id Name Region
1T4Tutorials ARegion A
2T4Tutorials BRegion B
3T4Tutorials CRegion C
4T4Tutorials DRegion D
5T4Tutorials ERegion E
Region Country
Region AUS
Region BUK
Region CFrance
Region DPakistan
Region EPakistan

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.

Subscribe for Friendship

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

Buy advertisement space on T4Tutorials

For more details email [email protected]