Normalization: 1NF, 2NF, 3NF

A well-designed Library does not store the same piece of information in many places. Normalization is the art of splitting data into tables so as to avoid repetition and errors. Let us walk through the three basic levels.

1NF - atomic values

The first normal form requires one value in each cell. A bad table:

| id | tytul | autorzy | |----|-------|---------| | 1 | Anthology | Homer; Plato |

The

autorzy

column breaks 1NF - it holds two values. In 1NF we split them so that each cell keeps a single piece of data.

2NF - no dependency on part of the key

The second normal form concerns tables with a composite key (made of several columns). It requires every non-key column to depend on the whole key, not on a part of it. If in the

wypozyczenia

table (key:

ksiazka_id

+

czytelnik_id

) we kept the book's title, it would depend only on

ksiazka_id

- which breaks 2NF. The title belongs in the

ksiazki

table.

3NF - no transitive dependencies

The third normal form forbids a non-key column to depend on another non-key column. An example of a bad table:

| id | tytul | kategoria_id | nazwa_kategorii | |----|-------|--------------|------------------|

Here

nazwa_kategorii

depends on

kategoria_id

, not directly on the

id

key. That is a transitive dependency. The solution - splitting the table:

1SELECT k.tytul, kat.nazwa
2FROM ksiazki k
3JOIN kategorie kat ON k.kategoria_id = kat.id;

We keep the category name once, in the

kategorie

table, and only

kategoria_id

stays in

ksiazki

. That is exactly how our database is built - each fact written in one place.