Jest to najważniejsze i najbardziej koncepcyjne zadanie w całym cyklu. Jego nadrzędnym celem jest nauczenie studenta teoretycznych podstaw i praktycznych technik normalizacji bazy danych – procesu organizacji danych w celu zminimalizowania redundancji (powtórzeń) i wyeliminowania niepożądanych anomalii (problemów z wstawianiem, aktualizacją i usuwaniem danych). Student przejdzie krok po kroku przez proces transformacji "płaskich", nieznormalizowanych tabel do postaci zgodnej z Trzecią Postacią Normalną (3NF). Kulminacyjnym i najważniejszym punktem zadania będzie zrozumienie i zaimplementowanie relacji między tabelami za pomocą kluczy obcych (FOREIGN KEY). Student nauczy się tworzyć tabele łączące (asocjacyjne), aby poprawnie modelować związki typu wiele-do-wielu. Po ukończeniu tego zadania student będzie posiadał fundamentalną wiedzę, która pozwoli mu projektować wydajne, spójne i skalowalne schematy relacyjnych baz danych, co jest jedną z najważniejszych umiejętności w świecie IT.
Student, mając już w bazie trzy odizolowane tabele (`czytelnicy_import`, `ksiazki`, `pracownicy`), staje przed zadaniem przekształcenia tego zbioru danych w spójny, logiczny i wydajny system. Zaczyna od analizy tabeli `czytelnicy_import`, która jest w najgorszym stanie. Wie, że musi osiągnąć Pierwszą Postać Normalną (1NF), która mówi, że każda komórka tabeli musi zawierać pojedynczą, niepodzielną (atomową) wartość. Kolumny `imie_nazwisko`, `adres_pelny` i `wypozyczone_ksiazki` w rażący sposób łamią tę zasadę. Student tworzy więc nową, docelową tabelę o nazwie `czytelnicy`. Definiuje w niej osobne kolumny: `imie`, `nazwisko`, `ulica`, `kod_pocztowy`, `miasto`. Następnie pisze serię zapytań `INSERT INTO ... SELECT ...` z użyciem funkcji do manipulacji tekstem (takich jak `SUBSTRING_INDEX`), aby "wyciąć" odpowiednie fragmenty danych ze starej tabeli i wstawić je w odpowiednie miejsca w nowej. Kolumnę `wypozyczone_ksiazki` całkowicie ignoruje, wiedząc, że te informacje muszą być zamodelowane w zupełnie inny sposób. Po pomyślnej migracji danych, usuwa starą tabelę `czytelnicy_import`. Następnie przygląda się tabeli `ksiazki`. Zauważa, że przechowywanie imienia i nazwiska autora w jednej kolumnie jest nieoptymalne i może prowadzić do niespójności. Aby osiągnąć Drugą (2NF) i Trzecią Postać Normalną (3NF), które eliminują zależności funkcyjne od części klucza i zależności przechodnie, decyduje się na wydzielenie autorów do osobnej tabeli `autorzy` (`id_autora`, `imie`, `nazwisko`). Ponieważ jedna książka może mieć wielu autorów i jeden autor może napisać wiele książek (relacja wiele-do-wielu), tworzy trzecią tabelę, tzw. tabelę łączącą: `ksiazki_autorzy`. Zawiera ona tylko dwa klucze obce: `ksiazka_id` i `autor_id`. Teraz dochodzi do kluczowego momentu: zamodelowania procesu wypożyczenia. Student rozumie, że wypożyczenie to zdarzenie, które łączy dokładnie jednego czytelnika, jedną książkę i jednego pracownika. Tworzy więc centralną tabelę w swoim systemie: `wypozyczenia`. Definiuje w niej kolumny na klucze obce: `czytelnik_id`, `ksiazka_id`, `pracownik_id`, oraz dodatkowe atrybuty, jak `data_wypozyczenia` i `data_zwrotu`. Używając składni `FOREIGN KEY (kolumna) REFERENCES tabela(kolumna)`, definiuje więzy integralności referencyjnej. To potężny mechanizm, który od teraz będzie pilnował spójności danych – serwer nie pozwoli na przykład na dodanie wypożyczenia dla nieistniejącego czytelnika. Na koniec, aby przetestować cały model, student dodaje kilka rekordów do tabeli `wypozyczenia` i wykonuje swoje pierwsze zapytanie typu `JOIN`, które łączy informacje z czterech tabel, aby wyświetlić pełny raport o tym, kto, co i kiedy wypożyczył.
Naszym pierwszym celem jest rozbicie nieatomowych danych z tabeli `czytelnicy_import` do nowej, poprawnie zaprojektowanej tabeli `czytelnicy`.
USE biblioteka;
-- Krok 1: Tworzymy nową, znormalizowaną tabelę.
CREATE TABLE czytelnicy (
id INT AUTO_INCREMENT PRIMARY KEY,
imie VARCHAR(100),
nazwisko VARCHAR(150),
email VARCHAR(150) UNIQUE
);
-- Krok 2: Migrujemy dane, rozbijając kolumnę 'imie_nazwisko'.
-- SUBSTRING_INDEX to bardzo użyteczna funkcja do dzielenia tekstu.
INSERT INTO czytelnicy (imie, nazwisko, email)
SELECT
SUBSTRING_INDEX(imie_nazwisko, ' ', 1) AS imie,
SUBSTRING_INDEX(imie_nazwisko, ' ', -1) AS nazwisko,
email
FROM czytelnicy_import;
-- Krok 3: Po udanej migracji, usuwamy starą, niepotrzebną tabelę.
DROP TABLE czytelnicy_import;
Wydzielamy autorów do osobnej tabeli, aby uniknąć powtarzania danych i umożliwić przypisanie wielu autorów do jednej książki.
-- Krok 1: Tworzymy tabelę 'autorzy'.
CREATE TABLE autorzy (
id INT AUTO_INCREMENT PRIMARY KEY,
imie VARCHAR(100),
nazwisko VARCHAR(150) NOT NULL
);
-- Krok 2: Tworzymy tabelę łączącą (asocjacyjną) dla relacji wiele-do-wielu.
-- Klucz główny składa się z dwóch kolumn, co zapobiega duplikatom (jeden autor nie może być dwa razy przypisany do tej samej książki).
CREATE TABLE ksiazki_autorzy (
ksiazka_id INT,
autor_id INT,
PRIMARY KEY (ksiazka_id, autor_id),
FOREIGN KEY (ksiazka_id) REFERENCES ksiazki(id),
FOREIGN KEY (autor_id) REFERENCES autorzy(id)
);
-- Krok 3: Usuwamy starą kolumnę 'autor' z tabeli 'ksiazki'.
ALTER TABLE ksiazki DROP COLUMN autor;
To jest serce naszego systemu. Ta tabela połączy wszystkie pozostałe, tworząc spójny model relacyjny.
CREATE TABLE wypozyczenia (
id INT AUTO_INCREMENT PRIMARY KEY,
data_wypozyczenia DATE NOT NULL,
przewidywana_data_zwrotu DATE,
data_rzeczywistego_zwrotu DATE,
status ENUM('wypozyczona', 'zwrocona', 'przetrzymana') NOT NULL,
ksiazka_id INT,
czytelnik_id INT,
pracownik_id INT,
-- Definicja więzów integralności referencyjnej
FOREIGN KEY (ksiazka_id) REFERENCES ksiazki(id) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (czytelnik_id) REFERENCES czytelnicy(id) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (pracownik_id) REFERENCES pracownicy(id) ON DELETE SET NULL ON UPDATE CASCADE
);
Dodajmy przykładowe dane i wykonajmy zapytanie, które pokaże siłę modelu relacyjnego.
-- (Zakładamy, że wcześniej dodaliśmy dane do tabel ksiazki, czytelnicy, pracownicy i autorzy)
INSERT INTO wypozyczenia (data_wypozyczenia, status, ksiazka_id, czytelnik_id, pracownik_id)
VALUES ('2023-10-01', 'wypozyczona', 1, 1, 2);
-- Zapytanie JOIN łączące 4 tabele w celu uzyskania pełnego raportu.
SELECT
c.imie,
c.nazwisko,
k.tytul,
w.data_wypozyczenia,
p.imie AS imie_pracownika
FROM wypozyczenia AS w
JOIN czytelnicy AS c ON w.czytelnik_id = c.id
JOIN ksiazki AS k ON w.ksiazka_id = k.id
JOIN pracownicy AS p ON w.pracownik_id = p.id;
Normalizacja nie jest akademicką, teoretyczną koncepcją – to absolutnie fundamentalny i praktyczny proces, który leży u podstaw projektowania zdrowych, wydajnych i niezawodnych baz danych. Przechodząc przez to zadanie, na konkretnym przykładzie zobaczyliśmy, jak transformacja chaotycznej struktury w uporządkowany, znormalizowany model przynosi wymierne korzyści. Wyeliminowaliśmy redundancję danych – informacja o autorze czy czytelniku jest teraz przechowywana tylko w jednym miejscu, co gwarantuje spójność i oszczędza miejsce. Zapobiegliśmy anomaliom – nie możemy już przypadkowo usunąć książki, która jest aktualnie wypożyczona, ponieważ chronią nas przed tym więzy integralności. Kluczowym wnioskiem jest zrozumienie, że relacje, implementowane za pomocą kluczy obcych, są "klejem", który spaja całą bazę danych w logiczną całość. Pozwalają one na odzwierciedlenie złożonych powiązań z realnego świata w ustrukturyzowany sposób. Umiejętność myślenia w kategoriach encji, atrybutów i relacji oraz biegłość w implementacji zasad normalizacji to cechy, które odróżniają początkującego twórcę baz danych od profesjonalnego architekta systemów informatycznych. Stworzony w tym zadaniu model relacyjny jest solidnym fundamentem, na którym można bezpiecznie budować logikę aplikacji.