DB Normal Forms (primitive description)

 Normal Forms (NF) helps to resolve possible problems with insert, update and delete DB operations. 

To be in the next level NF DB must already be in all previous levels of NF (to be in 3NF DB must already be in 1NF and 2NF).

There are plenty of normal forms but in most practical situations first three forms are enough to prevent possible problems.

1NF (first normal form)

1NFexample1: You have "children" table with "child" column storing something like "Child'sNameSurname"

1NFexample2: You have "children" table with "parent" column storing something like "Mom'sNameSurname, Dad'sNameSurname"

1NFexample3: You have "children" table with "parent1_name" and "parent2_name" columns storing something like "Mom'sName" and "Dad'sName"

To be in 1NF:

1. The relation has a Primary Key (PK), which uniquely identifies each row in the relation.
1. As in 1NFexample1 "child" may not be unique so you need to add "row_id" as PK
2. Every column stores Atomic Values
1. As in 1NFexample2 non-atomic values are in "parent" column, so we need to split this into to rows, each with "parent" column containing name of one parent
2. Also "parent" column must be separated into "parent_name" and "parent_surname" columns
3. There are no Repeating Groups.
1. As in 1NFexample3 "parent1_name" and "parent_name" are repeating groups, so we need to add "parent_name" column and for each parent add one row

2NF (first normal form)

2NFexample1: You have "children" table with CPK "child_name"+"zip" and also having "city" column

To be in 2NF:

1. DB must already be in 1NF
2. No partial dependencies (also called partial functional dependencies) are in table
1. That is, each non-key attribute is functionally dependent on the full PK (PK for 2NF can be only multi column PK - also called composite PK. If table is in 1NF and having single-column PK, then this table is also automatically in 2NF):
1. As in 2NFexample2 "city" is related to "zip" (part of CPK), but not related to the "child_name", so we must add new table "locations" with "location_id", "zip", "city" columns. And use "location_id instead of "zip" as part of CPK

3NF (first normal form)

3NFexample1: You have "children" table with "row_id" PK column and "city" and "zip" columns storing something like "Child'sCity" and "Child'sZip"

To be in 3NF:

1. DB must already be in 2NF
2. No transitive functional dependencies - non-key column depends (functionally) on another non-key column, which is (functionally) dependent on the PK
1. As in 3NFexample1 "zip" and "city" are related to "row_id" (PK), but also depend on each other, so we must add new table "locations" with "location_id", "zip", "city" columns. After that in "children" table we'll use "location_id" instead of "city" and "zip"