What is 3rd normal form in DBMS?

3 NF----------------------------------------
The third normal form (3NF) is a normal form used in database normalization. 3NF was

originally defined by E.F. Codd[1] in 1971. Codd's definition states that a table is in 3NF if and

only if both of the following conditions hold:

* The table is in second normal form (2NF)
* No non-prime attribute of the table is transitively dependent on a candidate key

A non-prime attribute is an attribute that does not belong to any candidate key. A transitive

dependency is a functional dependency X → Z in which Z is not immediately dependent on X,

but rather on a third set of attributes Y which depends on X. That is, X → Z by virtue of X → Y

and Y → Z.

An alternative formulation of Codd's definition, given by Carlo Zaniolo[2] in 1982, is this: a table

is in 3NF if and only if, for each of its functional dependencies X → A, at least one of the

following conditions holds:

* X contains A, or
* X is a superkey, or
* A is a prime attribute (i.e., A is contained within a candidate key)

Zaniolo's definition has the advantage of giving a clear sense of the difference between 3NF

and the more stringent Boyce-Codd normal form (BCNF). BCNF simply eliminates the third

alternative ("A is a prime attribute").

Example

An example of a 2NF table that fails to meet the requirements of 3NF is:
Tournament Winners TABLE

Tournament Year Winner Winner Date of Birth
Indiana Invitational 1998 Al Fredrickson 21 July 1975
Cleveland Open 1999 Bob Albertson 28 September 1968
Des Moines Masters 1999 Al Fredrickson 21 July 1975
Indiana Invitational 1999 Chip Masterson 14 March 1977

The only candidate key is {Tournament, Year}.

The breach of 3NF occurs because the non-prime attribute Winner Date of Birth is transitively

dependent on {Tournament, Year} via the non-prime attribute Winner. The fact that Winner Date

of Birth is functionally dependent on Winner makes the table vulnerable to logical

inconsistencies, as there is nothing to stop the same person from being shown with different

dates of birth on different records.

In order to express the same facts without violating 3NF, it is necessary to split the table into

two:
Tournament Winners TABLE

Tournament Year Winner
Indiana Invitational 1998 Al Fredrickson
Cleveland Open 1999 Bob Albertson
Des Moines Masters 1999 Al Fredrickson
Indiana Invitational 1999 Chip Masterson

Player Dates of Birth TABLE
Player Date of Birth
Chip Masterson 14 March 1977
Al Fredrickson 21 July 1975
Bob Albertson 28 September 1968

Update anomalies cannot occur in these tables, which are both in 3NF.