Normalizacja baz danych — lekcja z poprawiania błędnego projektu

Tabela, w której adres klienta powtarza się przy każdym zamówieniu, a cena produktu przy każdej sprzedaży, wygląda niewinnie — dopóki nie trzeba coś zmienić. Wtedy łatwo o niespójne dane: adres w jednym wierszu zmieniony, a w drugim nie. Normalizacja to uporządkowanie projektu tak, aby każdy fakt zapisany był w jednym miejscu. Na lekcji uczniowie poprawiają błędnie zaprojektowaną tabelę zamówień.

Scenariusz jest przeznaczony na 45 minut dla klasy znającej GROUP BY, JOIN i klucze obce — na przykład po lekcji o kluczach.

Przygotowanie stanowisk

Potrzebna jest baza MySQL 8 lub MariaDB (na przykład w XAMPP) i narzędzie do wykonywania zapytań, np. phpMyAdmin. Utwórz pustą bazę ćwiczeniową i zaimportuj raz plik baza.sql z pakietu. Plik nie usuwa tabel, więc ponowny import zgłosi błąd istniejącej tabeli. Zapytania zapisuj w start.sql; rozwiązania są w rozwiazania.sql.

Cele i plan lekcji

Po zajęciach uczeń potrafi:

• wskazać dane powtarzające się w tabeli i opisać grożące anomalie (aktualizacji, wstawiania, usuwania);
• rozdzielić dane na tabele z kluczami głównymi i obcymi, tak by każdy fakt występował jeden raz;
• odtworzyć pierwotne dane zapytaniem JOIN i sprawdzić zgodność;
• wykazać, że zmiana adresu wymaga teraz jednej poprawki.

Błędny projekt

Tabela zamowienia_zle zawiera w każdym wierszu pełne dane klienta i produktu:

Taki projekt powoduje anomalie:

• aktualizacji — zmiana adresu Anny wymaga poprawienia dwóch wierszy; pominięcie jednego daje sprzeczne dane;
• wstawiania — nie da się zapisać nowego klienta ani produktu, który jeszcze nic nie zamówił;
• usuwania — usunięcie jedynego zamówienia Celiny usuwa też wiedzę o niej i o cenie kabla.

Normalizacja

Rozwiązaniem jest rozdzielenie danych według tego, czego dotyczą: klienci (adres klienta), produkty (cena produktu) i zamowienia (kto, co i ile), które odwołuje się do dwóch pierwszych kluczami obcymi. Każdy fakt — adres klienta, cena produktu — jest zapisany raz. To mniej więcej odpowiada trzeciej postaci normalnej: kolumny niekluczowe zależą od klucza, całego klucza i niczego innego. Pierwotne zestawienie można odtworzyć w każdej chwili zapytaniem JOIN.

Zadania

Zadanie 1. Powtórzenia. Policz zamówienia każdego klienta w tabeli zamowienia_zle. Wskaż powtarzające się dane i opisz trzy anomalie.

Zadanie 2. Projekt. Utwórz tabele klienci, produkty i zamowienia z kluczami obcymi i wstaw do nich dane z tabeli zamowienia_zle.

Zadanie 3. Odtworzenie. Zapytaniem z dwoma JOIN odtwórz zestawienie klient, adres, produkt, cena, ilość. Porównaj z pierwotną tabelą.

Zadanie 4. Zmiana adresu. Zmień adres klientki Anna na „Lipowa 5, Gniezno” i sprawdź zapytaniem, że oba jej zamówienia pokazują nowy adres. Ile instrukcji UPDATE było potrzeba?

Dla chętnych: dodaj nowego klienta, który nic jeszcze nie zamówił, i wyjaśnij, dlaczego w pierwotnej tabeli nie dałoby się tego zrobić.

Rozwiązanie i wyniki

-- Zadanie 1
SELECT klient, COUNT(*) AS wierszy
FROM zamowienia_zle
GROUP BY klient
ORDER BY klient;

-- Zadanie 2
CREATE TABLE klienci (
  id INTEGER PRIMARY KEY,
  nazwa VARCHAR(20) NOT NULL,
  adres VARCHAR(40) NOT NULL
);
CREATE TABLE produkty (
  id INTEGER PRIMARY KEY,
  nazwa VARCHAR(20) NOT NULL,
  cena INTEGER NOT NULL
);
CREATE TABLE zamowienia (
  id INTEGER PRIMARY KEY,
  klient_id INTEGER NOT NULL,
  produkt_id INTEGER NOT NULL,
  ilosc INTEGER NOT NULL,
  FOREIGN KEY (klient_id) REFERENCES klienci (id),
  FOREIGN KEY (produkt_id) REFERENCES produkty (id)
);
INSERT INTO klienci (id, nazwa, adres) VALUES
(1, 'Anna', 'Lipowa 3, Gniezno'),
(2, 'Bartek', 'Polna 8, Poznan'),
(3, 'Celina', 'Dluga 1, Gniezno');
INSERT INTO produkty (id, nazwa, cena) VALUES
(1, 'Mysz', 35),
(2, 'Kabel', 15),
(3, 'Monitor', 650);
INSERT INTO zamowienia (id, klient_id, produkt_id, ilosc) VALUES
(1, 1, 1, 2),
(2, 1, 2, 5),
(3, 2, 1, 1),
(4, 2, 3, 1),
(5, 3, 2, 10);

-- Zadanie 3
SELECT k.nazwa, k.adres, p.nazwa, p.cena, z.ilosc
FROM zamowienia z
JOIN klienci k ON z.klient_id = k.id
JOIN produkty p ON z.produkt_id = p.id
ORDER BY z.id;

-- Zadanie 4
UPDATE klienci
SET adres = 'Lipowa 5, Gniezno'
WHERE nazwa = 'Anna';
SELECT k.nazwa, k.adres
FROM zamowienia z
JOIN klienci k ON z.klient_id = k.id
WHERE k.nazwa = 'Anna'
ORDER BY z.id;

Wyniki (sprawdzone na SQLite):

Zadanie 1 — Anna i Bartek mają po dwa zamówienia, Celina jedno; adres klienta powtarza się przy każdym jego zamówieniu, a cena produktu — przy każdej sprzedaży.

Zadanie 3 — zapytanie zwraca pięć wierszy dokładnie takich jak w tabeli zamowienia_zle (kolejność kolumn: klient, adres, produkt, cena, ilość):

Zadanie 4 — wystarczy jedno UPDATE, a zapytanie pokazuje nowy adres w obu zamówieniach Anny:

Typowe błędy

Pytania kontrolne i punktacja

1. Na czym polega anomalia aktualizacji?
2. Gdzie w nowym projekcie zapisana jest cena produktu?
3. Jak odtworzyć pierwotne zestawienie z tabel po normalizacji?

Odpowiedzi: Zmiana jednego faktu (np. adresu) wymaga poprawienia wielu wierszy, a pominięcie któregoś daje dane sprzeczne. W tabeli produkty, jeden raz. Zapytaniem z JOIN, które łączy tabele po kluczach obcych.

Proponowana punktacja (razem 10 punktów): zadanie 1 — liczba zamówień 1 pkt, wskazanie powtórzeń i anomalii 2 pkt; zadanie 2 — trzy tabele z kluczami 2 pkt, poprawne wstawienie danych 1 pkt; zadanie 3 — dwa JOIN 2 pkt; zadanie 4 — jedno UPDATE i weryfikacja 2 pkt. Zadanie dla chętnych nie jest wymagane do pełnej oceny.

Materiały do pobrania

• Pakiet SQL: materiały do lekcji, rozwiązanie i karta pracy
• Karta pracy ucznia — otwórz i wydrukuj

Rozpakuj ZIP przed otwarciem plików. Materiały są dostępne bez logowania; publiczne rozwiązanie służy nauce i samodzielnemu sprawdzeniu, a nie poufnemu sprawdzianowi.

Co dalej?

Następny krok to projekt bazy dla sklepu internetowego z diagramem związków encji i zapytaniami. Ten materiał rozwija dział o projektowaniu baz w kursie SQL w TablicaPro, gdzie znajdziesz prezentacje, ćwiczenia i testy do prowadzenia lekcji.

Źródła techniczne: normalizacja w Wikipedii. Zobacz też lekcję o JOIN.