4

My informal representation of these are:

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

2NF: I need a clear definition

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?

Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60
Christopher Markieta
  • 5,674
  • 10
  • 43
  • 60
  • 3
    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

3 Answers3

5

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 illustrate concept, let's use a table for an inventory of toys adapted from Head First SQL:

TOY_ID| STORE_ID| INVENTORY| STORE_ADDRESS

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

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

An Inventory table: TOY_ID| STORE_ID| INVENTORY

and a Store table: STORE_ID| STORE_ADDRESS

Mehdi Charife
  • 722
  • 1
  • 7
  • 22
Carlos Gavidia-Calderon
  • 7,145
  • 9
  • 34
  • 59
  • 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
  • The quoted characterization is wrong. (In both parts of its OR). So is "the non-prime attribute STORE_ADDRESS only depends on the attribute STORE_ID", since it too "depends on TOY_ID and STORE_ID at the same time". (And other attribute sets.) Although also it depends on TOY_IDO by itself. ("only" is not being used correctly.) Also "is related to" doesn't mean anything in particular. Although replacing it by "is determined by" gives a correct statement. – philipxy May 02 '23 at 05:09
3

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

Shaun
  • 43
  • 4
  • "if" should be iff to give a definition & "key" should be CK to be unambiguous (as it also gets used for superkey). – philipxy May 02 '23 at 05:22
0

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.

Casebash
  • 114,675
  • 90
  • 247
  • 350
  • This does not answer the question. Also the writing is very unclear. Including, "secondary key" is not a normalization term (you might mean alternate), and it's not clear whether unadorned "key" means CK or superkey. – philipxy May 02 '23 at 05:19