Normalisation - 2NF vs 3NF

database database-design database-normalization
Marcus picture Marcus · May 16, 2011 · Viewed 35.1k times · Source

Struggling to see the differences between them. I know we say 2NF is "the whole key" and 3NF "nothing but the key".

Referencing this great answer by Smashery: What are 1NF, 2NF and 3NF in database design?

The example used for 3NF is exactly the same as 2NF - its a field which is dependant on only one key attribute. How is the example for 3NF different from the one for 2NF?

Thanks

Answer

nvogel picture nvogel · May 17, 2011

Suppose that some relation satisifies a non-trivial functional dependency of the form A->B, where B is a nonprime attribute.

2NF is violated if A is not a superkey but is a proper subset of a candidate key

3NF is violated if A is not a superkey

You have spotted that the 3NF requirement is just a special case (but not really so special) of the 2NF requirement. 2NF in itself is not very important. The important issue is whether A is a superkey, not whether A just happens to be some part of a candidate key.

Related questions

What are database normal forms and can you give examples?

In relational database design, there is a concept of database normalization or simply normalization, which is a process of organizing columns (attributes) and tables (relations) to reduce data redundancy and improve data integrity. (as written on Wikipedia). As most articles …

Read More
Is storing a delimited list in a database column really that bad?

Imagine a web form with a set of check boxes (any or all of them can be selected). I chose to save them in a comma separated list of values stored in one column of the database table. Now, I …

Read More
Normalization: What does "repeating groups" mean?

I have read different tutorials and seen different examples of normalization, specially the notion of "repeating groups" in the first normal form. From them I have have gathered that repeating groups are "kind-of" multi-valued attributes (e.g. here and here). …

Read More