Database Normalization – Does 3NF Require 2NF?

dbmsnormalization

I am a bit confused with the definition of 3NF.

According to Wikipedia:

The third normal form (3NF) is a normal form used in database
normalization. 3NF was originally defined by E.F. Codd in 1971. Codd's
definition states that a table is in 3NF if and only if both of the
following conditions hold:

  • The relation R (table) is in second normal form (2NF)
  • Every non-prime attribute of R is non-transitively dependent on every superkey of R.

But, according to book the Database System Concepts 6th Edition by Abraham Silberschatz, Henry F.Korth, S.Sudarshan:

A relation schema R is in third normal form (3NF) with respect to a
set F of functional dependencies if, for all functional dependencies
in F+ of the form α → β, where α ⊆ R and β ⊆ R, at least one of the
following holds:

  • α → β is a trivial functional dependency.
  • α is a superkey for R.
  • Each attribute A in β − α is contained in a candidate key for R.

Also, according to Database Systems 6th Edition by Ramez Elmasri Shamkant B. Navathe:

A relation schema R is in third normal form (3NF) if, whenever a
nontrivial functional dependency X→A holds in R, either (a) X is a
superkey of R, or (b) A is a prime attribute of R.

After reading the above definitions, I am not sure if a relation schema has to be in 2NF in order to be in 3NF. Also, does a relation schema have to be in 3NF in order to be in BCNF?

Best Answer

The normal forms are specified in such a way that for any normal form, the model is also meets the criteria for the lower numbered normal forms. So, yes if your model is in 3rd normal form, it is also in 2nd normal form and 1st normal form.

When normalizing a model, you normally work at resolving one normal form before the next. This makes normalization simpler and less error prone as you are only applying a few rules at at time. It is possible that an entity at a lower normal form is also at a higher normal form. Most 3rd normal form entities are also in 4th and 5th normal form.

4th, 5th, and 6th normal forms go beyond the functional dependence. Where applicable, they further simplify the data, at the expense of more tables. They reduce redundancy, and increase data integrity.

Related Question