SQL and MySQL course Β· Module 9: Indexes and normalization
Normalization: 1NF, 2NF, 3NF
In this lesson3
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:
SELECT k.tytul, kat.nazwa
FROM ksiazki k
JOIN 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.
Spotted a mistake in this lesson?
Check yourself
Answer the questions from this lesson. Pick an answer to see right away whether it is correct.
1. What does the first normal form (1NF) require?
2. What does the third normal form (3NF) forbid?
Hands-on tasks in the game
- Vertical ordering
Arrange the normalization levels in the order we apply them - from the basic to the highest:
- Vertical ordering
Arrange the steps of splitting a table that breaks 3NF (where the category name depends on kategoria_id) - from the problem to the solution:
- Code editor
Write a query that retrieves tytul from the ksiazki table and nazwa from the kategorie table, joining them on the kategoria_id column (ksiazki.kategoria_id = kategorie.id). Use JOIN.