Proces normalizacji bazy danych: krok po kroku

Normalizacja to proces organizowania kolumn i tabel w relacyjnej bazie danych w celu zminimalizowania redundancji (powtarzania się) danych. Jej głównym celem jest podział dużych, problematycznych tabel na mniejsze, dobrze ustrukturyzowane tabele oraz zdefiniowanie relacji między nimi. Prześledźmy ten proces na przykładzie naszej tabeli `czytelnicy` z biblioteki.

Uwaga: w tym artykule opisano tylko optymalizacje do: 1, 2 i 3 postaci normalnej.

Stan wyjściowy: tabela nieznormalizowana

Zaczynamy od tabeli `czytelnicy_archiwum` (oryginalna tabela z zadania 6), która celowo została źle zaprojektowana.

Struktura tabeli `czytelnicy_archiwum`

CREATE TABLE czytelnicy_archiwum (
  id_czytelnika INT PRIMARY KEY,
  imie_nazwisko VARCHAR(255),
  adres_zamieszkania VARCHAR(255),
  telefony VARCHAR(100)
);
  

Przykładowe dane:

id_czytelnika	imie_nazwisko	adres_zamieszkania	telefony
1	Anna Kowalska	ul. Kwiatowa 1, 00-001 Warszawa	123-456-789; 987-654-321
2	Jan Nowak	ul. Leśna 2, 30-002 Kraków	555-666-777

Pierwsza postać normalna (1NF)

Zasada: Tabela jest w 1NF, jeśli wszystkie jej atrybuty są atomowe (niepodzielne), a każda komórka zawiera tylko jedną wartość. Nie ma grup powtarzalnych.

Nowe struktury po transformacji do 1NF:

  Tabela: czytelnicy_1nf
+--------------+---------------+-------------+------------------+
| id_czytelnika| imie          | nazwisko    | email            | ...
+--------------+---------------+-------------+------------------+
| 1            | Anna          | Kowalska    | a.k@example.com  |
| 2            | Jan           | Nowak       | j.n@example.com  |
+--------------+---------------+-------------+------------------+

  Tabela: adresy
+-----------+---------------+---------+--------------+--------+
| id_adresu | id_czytelnika | ulica   | kod_pocztowy | miasto |
+-----------+---------------+---------+--------------+--------+
| 1         | 1             |Kwiatowa 1| 00-001       |Warszawa|
| 2         | 2             |Leśna 2  | 30-002       |Kraków  |
+-----------+---------------+---------+--------------+--------+

  Tabela: telefony
+-------------+---------------+---------------+
| id_telefonu | id_czytelnika | numer         |
+-------------+---------------+---------------+
| 1           | 1             | 123-456-789   |
| 2           | 1             | 987-654-321   |
| 3           | 2             | 555-666-777   |
+-------------+---------------+---------------+

Teraz każda komórka przechowuje jedną, atomową wartość. Dane są znacznie łatwiejsze do przeszukiwania i zarządzania. Nasza baza jest w Pierwszej Postaci Normalnej.

Druga postać normalna (2NF)

Zasada: Tabela jest w 2NF, jeśli jest w 1NF i każdy atrybut niebędący kluczem jest w pełni funkcyjnie zależny od całego klucza głównego. Ta zasada ma znaczenie tylko wtedy, gdy mamy klucz główny złożony z wielu kolumn.

W naszym przypadku, wszystkie tabele (`czytelnicy_1nf`, `adresy`, `telefony`) mają proste klucze główne (składające się z jednej kolumny: `id_czytelnika`, `id_adresu`, `id_telefonu`). W takiej sytuacji, jeśli tabela jest w 1NF, automatycznie spełnia również warunki 2NF, ponieważ nie ma częściowych zależności od klucza.

Przykład hipotetyczny łamania 2NF:

Wyobraźmy sobie tabelę `wypozyczenia_z_autorem`, gdzie kluczem głównym jest (`id_czytelnika`, `id_ksiazki`):

  Tabela: wypozyczenia_z_autorem (ŹLE ZAPROJEKTOWANA)
+---------------+------------+---------------------+-------------------+
| id_czytelnika | id_ksiazki | data_wypozyczenia   | autor_ksiazki     |  <-- Klucz główny: (id_czytelnika, id_ksiazki)
+---------------+------------+---------------------+-------------------+
| 1             | 10         | 2023-01-15          | Andrzej Sapkowski |
| 1             | 12         | 2023-02-20          | Stanisław Lem     |
| 2             | 10         | 2023-03-10          | Andrzej Sapkowski |
+---------------+------------+---------------------+-------------------+

Trzecia postać normalna (3NF)

Zasada: Tabela jest w 3NF, jeśli jest w 2NF i nie istnieją w niej zależności przechodnie. Oznacza to, że żaden atrybut niebędący kluczem nie może zależeć od innego atrybutu niebędącego kluczem.

Spójrzmy na naszą tabelę `adresy`. Czy jest w 3NF?

  Tabela: adresy
+-----------+---------------+---------+--------------+--------+
| id_adresu | id_czytelnika | ulica   | kod_pocztowy | miasto |
+-----------+---------------+---------+--------------+--------+

W tej strukturze `ulica`, `kod_pocztowy` i `miasto` zależą bezpośrednio od `id_adresu` (klucza głównego). Jednak można argumentować, że `miasto` zależy od `kodu_pocztowego`. To jest zależność przechodnia: `id_adresu` -> `kod_pocztowy` -> `miasto`.

Finalna, znormalizowana struktura (3NF):

  Tabela: czytelnicy (ostateczna)
+--------------+--------+----------+-----------------+
| id_czytelnika| imie   | nazwisko | ...             |
+--------------+--------+----------+-----------------+

  Tabela: adresy (ostateczna)
+-----------+---------------+----------+--------------+
| id_adresu | id_czytelnika | ulica    | kod_pocztowy | --> (Klucz obcy)
+-----------+---------------+----------+--------------+
                                             |
  Tabela: miasta (nowa)                        |
+--------------+---------------+ <------------'
| kod_pocztowy | nazwa_miasta  |
+--------------+---------------+
| 00-001       | Warszawa      |
| 30-002       | Kraków        |
+--------------+---------------+

  Tabela: telefony (ostateczna, bez zmian)
+-------------+---------------+-------------+
| id_telefonu | id_czytelnika | numer       |
+-------------+---------------+-------------+

Podsumowanie i schemat końcowy

Po przejściu przez wszystkie trzy postacie normalne, nasza początkowo jedna, duża tabela została przekształcona w zestaw mniejszych, połączonych relacjami tabel. Każda tabela opisuje teraz jeden, konkretny aspekt (osoby, adresy, telefony, miasta).

+----------------+      +----------------+      +----------------+
|    CZYTELNICY  |      |     ADRESY     |      |     MIASTA     |
+----------------+      +----------------+      +----------------+
| PK id_czytelnika|<--1--| PK id_adresu   |      | PK kod_pocztowy|
|    imie        |      | FK id_czytelnika|--N-->|    nazwa_miasta|
|    nazwisko    |      |    ulica       |      +----------------+
+----------------+      | FK kod_pocztowy|
       ^                +----------------+
       |
      1|
       |
      N|
+----------------+
|    TELEFONY    |
+----------------+
| PK id_telefonu |
| FK id_czytelnika|
|    numer       |
+----------------+