Tuesday, 18 February 2014

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. a non-key dependency over other non-key dependency while there is a Primary key).

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.

No comments:

Post a Comment

Popular Posts

Loading...