Advanced Database Management System - Tutorials and Notes: Third Normal Form (3NF) with Example

## Search Engine

Please visit, subscribe and share 10 Minutes Lectures in Computer Science

## 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.

*********