Normalizacja: porządek w danych

Problem: wiele faktów w jednej tabeli

Załóżmy tabelę sprzedaz(zamowienie_id, produkt_id, nazwa_produktu, klient_id, imie_klienta, data, ilosc). Kluczem jest para (zamowienie_id, produkt_id). Każdy produkt może wystąpić najwyżej raz w danym zamówieniu, ale zamówienie może mieć wiele produktów.

Imię Ali i nazwa Myszy się powtarzają. Samo powtórzenie wartości nie dowodzi błędu — wiele osób może mieć to samo imię. Problemem jest wielokrotne zapisanie tego samego faktu, np. nazwy jednego konkretnego produktu.

Trzy anomalie

Zależność funkcyjna: co wyznacza co?

Zapis X → Y oznacza, że dla jednej wartości X może istnieć tylko jedna wartość Y. To reguła dziedziny, a nie przypadkowa obserwacja kilku rekordów. W naszym przykładzie:

produkt_id → nazwa_produktu
zamowienie_id → klient_id, data
klient_id → imie_klienta
(zamowienie_id, produkt_id) → ilosc

Ten sam produkt ma tę samą nazwę w aktualnym katalogu, zamówienie należy do jednego klienta, a ilość zależy od produktu w konkretnym zamówieniu. Nie można wnioskować, że imię wyznacza klienta tylko dlatego, że w małej próbce imiona się nie powtarzają.

1NF — pierwsza postać normalna

W podstawowym ujęciu każda komórka powinna zawierać pojedynczą wartość z ustalonej dziedziny, bez grup powtarzalnych. Kolumny produkt1, produkt2, produkt3 oraz lista identyfikatorów w jednym polu produkty = '1,2,3' utrudniają pracę i zwykle sygnalizują potrzebę wydzielenia pozycji.

Atomowość zależy od przyjętego modelu. Nazwa miejscowości może być pojedynczą wartością tekstową, choć składa się z kilku słów. Nie trzeba mechanicznie rozbijać każdego napisu. Kluczowe jest to, jakich operacji potrzebuje aplikacja.

2NF — zależność od całego klucza

Tabela jest w 2NF, gdy jest w 1NF i każdy atrybut niekluczowy zależy od całego każdego klucza kandydującego, bez zależności od jego właściwej części. Problem jest szczególnie widoczny przy kluczu złożonym.

W tabeli sprzedaz nazwa_produktu zależy tylko od produkt_id, a data i klient_id tylko od zamowienie_id. Rozdzielamy więc:

produkty(produkt_id PK, nazwa_produktu)
zamowienia(zamowienie_id PK, klient_id, imie_klienta, data)
pozycje(zamowienie_id PK/FK, produkt_id PK/FK, ilosc)

Ilość zostaje w pozycjach: zależy od całej pary. Jeżeli wszystkie klucze kandydujące mają po jednej kolumnie, problem zależności od części klucza nie występuje. Samo dodanie sztucznego id nie usuwa jednak istniejących zależności ani innych kluczy kandydujących.

3NF — usuń niewłaściwe zależności przechodnie

W prostym szkolnym przykładzie szukamy atrybutu niekluczowego zależnego od innego atrybutu niekluczowego. W zamówieniu występuje łańcuch zamowienie_id → klient_id → imie_klienta. Imię opisuje klienta, dlatego przenosimy je do tabeli klientów.

klienci(klient_id PK, imie_klienta)
zamowienia(zamowienie_id PK, klient_id FK, data)
produkty(produkt_id PK, nazwa_produktu)
pozycje(zamowienie_id PK/FK, produkt_id PK/FK, ilosc)

Dokładna definicja 3NF uwzględnia wszystkie klucze kandydujące: dla każdej nietrywialnej zależności X → A, X jest nadkluczem albo A należy do któregoś klucza kandydującego. Szkolne hasło „zależy od klucza, całego klucza i tylko klucza” jest pomocą pamięciową, nie pełnym zastępstwem definicji.

Po podziale musisz odzyskać te same informacje

Dobra dekompozycja nie gubi informacji i nie tworzy fałszywych kombinacji rekordów po połączeniu tabel. Zachowujemy klucze i odpowiednie odwołania. Nie dziel tabel wyłącznie po to, aby miały mniej kolumn.

Ten sam projekt w różnych środowiskach

1NF, 2NF i 3NF są własnościami projektu, a nie funkcjami MySQL, SQL Server, Oracle czy Access. Ten sam logiczny podział na klienci, zamowienia, produkty i pozycje może być wdrożony w każdym z tych systemów. Różnić się będą typy, generowanie id i szczegóły DDL.

Nie istnieje uniwersalne polecenie NORMALIZE DATABASE, które rozumie reguły Twojej firmy i automatycznie naprawi model. Denormalizacja bywa świadomą optymalizacją, ale powinna wynikać z pomiaru i planu utrzymania spójności.

Ćwiczenia

1. W tabeli zapisy(uczen_id, kurs_id, nazwisko_ucznia, nazwa_kursu, ocena) kluczem jest para (uczen_id, kurs_id). Wskaż zależności częściowe i zaproponuj podział.
2. W tabeli pracownicy(id, dzial_id, nazwa_dzialu) każdy dział ma jedną nazwę. Co zmienić?
3. Czy zapisanie kilku numerów telefonu oddzielonych przecinkiem jest dobrym modelem, jeśli każdy numer ma być unikalny i oddzielnie wyszukiwany?

Zakres INF.03.4: projektowanie i normalizacja baz danych. Podstawa programowa ORE. Następna część: przygotowanie środowiska i rozpoznanie prawdziwego silnika.