3

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?

3

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.

1

Yes, Korth's definition for 3NF implies 2NF.

Assume there is a relation Books

Books { pub_id, auth_id, publisher_name, author_name}

with the set of functional dependencies:

pub_id -> publisher_name
auth_id -> author_name

The candidate key here is {pub_id, auth_id}.

Obviously, this relation is not in 2NF because publisher_name is dependent on pub_id, which is a subset of the candidate key (therefore it is not fully functionally dependent on the only candidate key).

Now, let us try and apply Korth's definition of 3NF.

  • Neither of the the FDs are trivial.
  • Nor is {pub_id} or {auth_id} a superkey.
  • However, either attribute is indeed a contained in the candidate key.

Thus, even though no transitive dependencies exist, this relation is not in 3NF.

Moreover, if we attempt to normalize this relation to 3NF, we will be forced to decompose the relation as follows:

Publishers { pub_id, publisher_name}
Authors {auth_id, author_name}

Which is equivalent to the 2NF decomposition of Books.

  • This example is a bit off: basically you are showing a 2NF decomposition which results in a 3NF, because there is only one non-key attribute. – MJQZ1347 Jun 12 '17 at 13:03
-3

By trying to implement 2nf and 3nf you might yet a better idea. In real time you can't differentiate between these two as they happen at the same time. When you are 2nf-ing you just can't stop there and w 3nf is not itself if it doesn't have primary keys or super keys defined.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.