Third Normal Form (3NF) with Example

Third Normal Form (3NF)

Third Normal Form is about eliminating Transitive Functional Dependency, if any. It means, we do not like non key attributes depend on other non-key attributes.

The following are the properties to be satisfied by a table for 3NF;

Property 1: The table should be in 2NF

Property 2: There should not be any Transitive Functional Dependency, i.e., there should not be any functional dependencies like a non-key (non-prime) attribute depends on another non-key (non-prime) attributes. Simply, we need all the non-key attributes must depend on the primary key only.

Let us discuss further with the following table;

RegNo	SName	Gen	PR	Phone	PManager
R1	Sundar	M	BTech	9898786756	Kumar
R2	Ram	M	MS	9897786776	Kumar
R3	Karthik	M	MCA	8798987867	Steve
R4	John	M	BSc	7898886756	Badrinath
R5	Priya	F	MS	9809780967	Kumar
R6	Ram	M	MTech	9876887909	Jagdeesh

Table 1 – STUDENT

Table 1 STUDENT stores information about a student like his register number, name, gender, phone, program joined, and the program manager. RegNo is the Primary Key for the above table, because using RegNo we are able to uniquely identify any records of this table. As the table has single attribute as primary key, it is clear that the table is in 2NF.

Is the table satisfies 3NF? - For answering this question, let us check the properties satisfied by the table.

Attribute RegNo uniquely determines all the other attributes. Hence, property 1 of 3NF is satisfied.

For checking Property 2, let us identify the Functional Dependencies (FDs) of table 1.

Table 1 holds the following set of FDs;

{(RegNo → SName Gen PR Phone PManager),

(Phone → RegNo SName Gen PR PManager),

(PR → PManager)}

In the above set of FDs, RegNo uniquely identify all the other attributes and Phone also uniquely identify all the other attributes. In the future, possibly same phone number might be provided for two students. Hence, having phone as the Primary key would violate the key constraint in the future. So, RegNo is the Primary key for this relation.

The FD PR → PManager is valid one, because one program can have one program manager at most. But, this FD leads to Transitive FD. That is,

RegNo → PR and PR → PManager, then RegNo → PManager.

where PR and PManager are both non-key attributes. Because of this Transitive FD, the table STUDENT is not in 3NF.

How do we convert a table into 3NF if Transitive Dependency exists?

The solution can be given through decomposition. The table STUDENT can be decomposed into two tables using the following two functional dependencies;

RegNo → SName Gen PR Phone PManager

PR → PManager

As PR can uniquely determine PManager, both can form a table as follows;

PROGRAM(PR, PManager)

The other table can be constructed using the first FD as follows;

STUDENT(RegNo, SName, Gen, PR, Phone)

Here, PR is the Foreign key in STUDENT relation and Primary key in PROGRAM relation. It is due to the fact that the relationship between these two tables is One-To-Many from PROGRAM to STUDENT. That is, one program can have many students.

At the end, tables PROGRAM and STUDENT are in 3NF as they are not violating the properties of 3NF. There is no Transitive FD in these relations.

What would be the problem with Transitive FD?

Transitive Functional Dependency causes duplication (redundancy). In Table 1, wherever program BTech occurs, the program manager is Kumar. The value combination (‘BTech’, ‘Kumar’) occurs for all the students registered ‘BTech’ program. Ultimately, this redundancy would lead to inconsistency.

For other simple definition on Third Normal Form (3NF), click here.

*********

Related Links

• Go to Normal Form - Home page

• Go to Normalization - Home page

• Go to Normalization Solved Exercises - Home page

• Go to Comparison of All Normal Forms page

Third normal form 3NF in DBMS explained