What is the 2nd normal form?

Question

My informal representation of these are:

1NF: The table is divided so that no item will appear more than once.

2NF: ?

3NF: Values can only be determined by the primary key.

I cannot make sense of it from the excerpts I found online or in my book. How do I differentiate between 1NF and 2NF?

Take a look at this post: http://stackoverflow.com/questions/723998/can-someone-please-give-an-example-of-1nf-2nf-and-3nf-in-plain-english — David Gorsline, Apr 11 '12 at 20:59
Took me a while but I think I understand now; 2NF: it relies on the whole key, but in 3NF it is "nothing but the key". :) — Christopher Markieta, Apr 11 '12 at 21:10

score 3 · Answer 1 · answered Apr 11 '12 at 20:58

3

A relation schema is in 2NF if every non-prime attribute is fully functionally dependent on every key.

answered Apr 11 '12 at 20:58

Shaun

43
4

score 2 · Answer 2 · answered Apr 11 '12 at 21:14

Wikipedia says:

A table is in 2NF if and only if, it is in 1NF and every non-prime attribute of the table is either dependent on the whole of a candidate key, or on another non prime attribute.

To explain the concept, let's use a table for a inventory of toys adapted from Head First SQL:

TOY_ID| STORE_ID| INVENTORY| STORE_ADDRESS

The primary key is composed by the attributes TOY_ID and STORE_ID. If we analize the non-prime attribute INVENTORY we see that in depends on TOY_ID and STORE_ID at the same time. That's cool.

But on the other side, the non-prime attribue STORE_ADDRESS only depends on the STORE_ID attribute (i.e it's not related to the primary key attribute TOY_ID). That's a clear violation of 2NF, so to complain to the 2NF our schema must be like this:

An Inventory table: TOY_ID| STORE_ID| INVENTORY

and an Store table: STORE_ID| STORE_ADDRESS

A 3NF schema has no transitive dependencies (a non-key column that depends on another non-key column). The example that I used only have one non-key attribute so it's already in 3NF — Carlos Gavidia-Calderon, Apr 11 '12 at 23:04

score 0 · Answer 3 · answered May 04 '20 at 06:54

Some columns are part of a key (primary or secondary). We'll call these prime attributes.

For second normal form we'll consider the non-prime attributes and see if they should be moved to another table. We might find that some attributes don't require the full key for us to be able to identify what value they hold for at least one candidate key. That is, there is a candidate key where we could still determine the value of that attribute given the candidate key even if the values in one column of that candidate key were erased.

What is the 2nd normal form?

3 Answers3

Linked