CODEWITHSUNDEEP
databasedatabase-normalization
Christopher Markieta
Christopher Markieta

Reputation: 5913

What is the 2nd normal form?

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?

Upvotes: 4

Views: 2934

Answers (3)

Carlos Gavidia-Calderon
Carlos Gavidia-Calderon

Reputation: 7253

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

Upvotes: 5

Casebash
Casebash

Reputation: 118742

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.

Upvotes: 0

Shaun
Shaun

Reputation: 43

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

Upvotes: 3

Related Questions