INF.03 · Bazy danych i SQL

Normalizacja: jedna informacja, jedno miejsce

Wyobraź sobie dziennik, w którym nazwisko ucznia jest przepisane przy każdej ocenie. Uczeń zmienia nazwisko i musisz poprawić sto wierszy. Jeśli pominiesz jeden, w bazie są dwa nazwiska tej samej osoby. Normalizacja to sposób, żeby tak się nie działo. Nie chodzi o robienie jak największej liczby tabel, tylko o usunięcie konkretnych błędów.

  • około 35 minut
  • 1NF · 2NF · 3NF
  • przykłady ze sklepu muzycznego

Po tej lekcji: wskażesz anomalie w źle zaprojektowanej tabeli, sprawdzisz 1NF, 2NF i 3NF oraz rozdzielisz dane na tabele z kluczami.

Laboratorium · ćwiczenie w przeglądarce, do którego odsyłają rozgrzewki na końcu lekcji

1 · Zacznij od problemu

Co się dzieje, gdy wszystko leży w jednej tabeli?

To zamówienia ze sklepu muzycznego zapisane w jednej wielkiej tabeli. Zmień miasto Jana tylko w jednym wierszu i zobacz, co się stanie. Potem rozdziel kolumny na trzy tabele: Klienci, Produkty i Zamowienia.

Ta interakcja wymaga włączonego JavaScriptu.

Powtórzone dane to nie tylko marnowanie miejsca. To ryzyko, że ta sama informacja zapisana w dwóch miejscach zacznie sobie przeczyć.

2 · Co może się zepsuć

Trzy anomalie, czyli trzy rodzaje szkód

Gdy ta sama informacja leży w wielu miejscach, baza może się „rozjechać”. Specjaliści rozróżniają trzy sytuacje.

Aktualizacji

Zmieniasz dane w jednym wierszu, a w innym zostaje stara wartość.

Na przykład Olsztyn zmieniony tylko w jednym zamówieniu Anny.

Dodawania

Nie możesz zapisać jednej informacji, dopóki nie pojawi się inna.

Na przykład nowy klient nie trafi do bazy, dopóki nie złoży zamówienia.

Usuwania

Usuwasz jedną rzecz i przy okazji tracisz inną.

Na przykład usunięcie ostatniego zamówienia kasuje jedyne dane klienta.

3 · Postacie normalne

Trzy pytania, które zadajesz tabeli

Postać normalna to po prostu zestaw pytań kontrolnych. Zadajesz je kolejno. Gdy odpowiedź brzmi „nie”, wydzielasz dane do osobnej tabeli.

1NFjedna wartość w komórce

Czy każda komórka zawiera jedną wartość? Jeśli w komórce jest lista, rozdziel ją na osobne wiersze.

Na przykład komórka „Gitara, Kostka” łamie 1NF. Każdy produkt zamówienia dostaje osobny wiersz.

2NFcały klucz, nie jego kawałek

Czy każda kolumna zależy od całego klucza? Ma to znaczenie, gdy klucz główny składa się z kilku kolumn. Dane zależne tylko od części klucza wydziel do osobnej tabeli.

Na przykład w tabeli PozycjeZamowienia z kluczem (id_zamowienia, id_produktu) nazwa produktu zależy tylko od id_produktu, więc należy do tabeli Produkty.

3NFnic poza kluczem

Czy żadna kolumna niekluczowa nie zależy od innej kolumny niekluczowej? Takie zależności przechodnie wydziel do osobnej tabeli.

Na przykład w tabeli Produkty nazwa_kategorii wynika z kod_kategorii, a nie z klucza produktu. Kategorie dostają własną tabelę.

4 · Diagnoza

Którą postać normalną łamie ta tabela?

Cztery tabele, cztery diagnozy. Jedna z nich jest zupełnie w porządku.

Ta interakcja wymaga włączonego JavaScriptu.

5 · Efekt końcowy

Tabela łącząca z kluczem złożonym

Gdy jedno zamówienie może mieć wiele produktów, a ten sam produkt trafia do wielu zamówień, potrzebujesz tabeli łączącej. Każdy produkt zamówienia zajmuje osobny wiersz, więc lista w komórce znika. Widok dla użytkownika odtwarzasz potem przez JOIN.

pozycje.sqlsql
CREATE TABLE PozycjeZamowienia (
    id_zamowienia INT NOT NULL,
    id_produktu INT NOT NULL,
    ilosc INT NOT NULL,
    PRIMARY KEY (id_zamowienia, id_produktu),
    FOREIGN KEY (id_zamowienia) REFERENCES Zamowienia(id_zamowienia),
    FOREIGN KEY (id_produktu) REFERENCES Produkty(id_produktu)
);
  1. PRIMARY KEY (id_zamowienia, id_produktu) to klucz złożony. Pilnuje, żeby ten sam produkt nie został dopisany dwa razy do tego samego zamówienia.
  2. Dwa klucze obce. Wskazują zamówienie i produkt. Dzięki nim baza pilnuje, że nie da się wpisać produktu, którego nie ma.
  3. ilosc leży w tej tabeli, bo opisuje konkretną pozycję: ile sztuk tego produktu w tym zamówieniu.

6 · Zanim się pomylisz

Trzy pułapki normalizacji

Rozwiń każdą z nich.

„Mam klucz główny, więc jest 3NF”
Tak nie
Produkty(id_produktu PK, nazwa,
         kod_kategorii, nazwa_kategorii)

Klucz jest, ale nazwa_kategorii zależy od kod_kategorii. Zmiana nazwy kategorii wymaga poprawienia wielu wierszy.

Tak
Produkty(id_produktu PK, nazwa, id_kategorii FK)
Kategorie(id_kategorii PK, nazwa_kategorii)

Nazwa kategorii zapisana jest raz, w Kategorie.

Lista w jednej komórce zamiast relacji
Tak nie
Uczniowie(id_ucznia PK, imie,
          gry VARCHAR(200))   -- 'Minecraft, FIFA'

Przecinki w komórce nie tworzą relacji wiele-do-wielu. Nie policzysz graczy FIFA ani nie sprawdzisz, czy gra istnieje.

Tak
Uczniowie(id_ucznia PK, imie)
Gry(id_gry PK, nazwa)
UczniowieGry(id_ucznia FK, id_gry FK)

Jeden zapis „uczeń gra w grę” to jeden wiersz w tabeli łączącej.

Podział bez kluczy obcych
Tak nie
Klienci(id_klienta PK, imie)
Zamowienia(id_zamowienia PK, data)

Tabele są osobno, ale nie wiadomo, który klient złożył które zamówienie. Dane przestały się łączyć.

Tak
Klienci(id_klienta PK, imie)
Zamowienia(id_zamowienia PK, data,
           id_klienta FK)

Po wydzieleniu tabel zawsze dodaj klucze obce, które odtwarzają połączenia.

7 · Sprawdź sam siebie

Jak uzasadnić projekt na egzaminie

Na egzaminie samo hasło „3NF” nie pokazuje poprawnego projektu. Trzeba nazwać problem, wskazać zależność, zaproponować tabele i zaznaczyć klucze.

Podsumowanie

Co warto zapamiętać

  1. Normalizacja usuwa powtórzenia i anomalie: aktualizacji, dodawania i usuwania.
  2. 1NF: jedna wartość w komórce. 2NF: cały klucz, nie jego kawałek. 3NF: nic poza kluczem.
  3. 2NF ma znaczenie przy kluczu złożonym.
  4. Po podziale dodaj klucze obce, a widok odtwórz przez JOIN.
  5. Nie dziel na siłę: każda tabela ma opisywać jedną rzecz.

Dalej na swoim koncie

Rozgrzewki, quiz i zadania są w biurku ucznia.

Teoria jest otwarta dla wszystkich. Żeby rozwiązywać zadania, zapisywać szkice i widzieć swój postęp, zaloguj się.

Zaloguj się →
Źródła i dalsza nauka