SQL and MySQL course Β· Module 9: Indexes and normalization

Normalization: 1NF, 2NF, 3NF

2 min read Β·
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:

idtytulautorzy
1AnthologyHomer; 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:

idtytulkategoria_idnazwa_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. 1. What does the first normal form (1NF) require?

  2. 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.

  1. QuestionFirst normal form
  2. QuestionThird normal form
  3. Put in orderThe successive normal forms
  4. Put in orderSplitting a table that breaks 3NF
  5. Coding taskFetch the category name via JOIN

Useful articles