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
nonkey 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 nonkey (nonprime) attribute depends on another nonkey (nonprime) attributes. Simply, we need all the nonkey 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 nonkey 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 OneToMany 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.
For other simple definition on Third Normal Form (3NF), click here.
No comments:
Post a Comment