Zadanie 8 - Normalizacja i relacje w bazie danych

Wprowadzenie: Wielkie porządki w danych

Analogia: Chaotyczny arkusz kalkulacyjny

Wyobraź sobie, że prowadzisz ewidencję w jednym, wielkim arkuszu Excela. W jednej komórce masz "Jan Kowalski, tel. 500-600-700", a w innej "Władca Pierścieni (J.R.R. Tolkien)". Co się stanie, jeśli Jan zmieni numer telefonu? Musisz ręcznie edytować tekst w komórce. A jeśli Tolkien napisał 10 książek i chcesz poprawić literówkę w jego nazwisku? Musisz to zrobić w 10 różnych miejscach! To strata czasu i prosta droga do błędów.

Normalizacja to proces "sprzątania" tego bałaganu. Polega na rozbiciu jednej dużej tabeli na kilka mniejszych, tematycznych tabel (osobna dla czytelników, osobna dla autorów, osobna dla książek) i połączeniu ich ze sobą za pomocą relacji. Dzięki temu dane się nie powtarzają, a zmiana w jednym miejscu (np. numeru telefonu czytelnika) jest natychmiast widoczna wszędzie.

Cel zadania

To najważniejsze zadanie w dotychczasowym kursie. Nauczysz się, jak przekształcić naszą "brudną" tabelę czytelnicy_denormalized w zestaw mniejszych, logicznie powiązanych tabel. Wprowadzimy kluczowe pojęcie klucza obcego (Foreign Key), który działa jak "klej" łączący nasze dane. Stworzymy w pełni relacyjny, profesjonalny model bazy danych.

Krok 1: Normalizacja danych czytelników (osiągnięcie 1NF)

Pierwsza Postać Normalna (1NF) mówi, że każda komórka musi zawierać jedną, niepodzielną informację. Nasza stara tabela łamała tę zasadę. Czas to naprawić.

Zadanie: Stworzenie nowej, "czystej" tabeli czytelnicy

CREATE TABLE czytelnicy (
  id_czytelnika INT AUTO_INCREMENT PRIMARY KEY,
  imie VARCHAR(100) NOT NULL,
  nazwisko VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  data_rejestracji DATE NOT NULL DEFAULT (CURDATE())
);
Co tu się stało?

Krok 2: Normalizacja danych o książkach (osiągnięcie 3NF)

W tabeli ksiazki mamy problem: nazwisko autora jest powtórzone przy każdej jego książce. To redundancja. Rozwiązaniem jest stworzenie osobnej tabeli dla autorów.

Zadanie: Stworzenie tabeli autorzy i powiązanie jej z książkami
  1. Stwórz tabelę autorzy: Będzie ona przechowywać unikalne informacje o każdym autorze.
    2. 
CREATE TABLE autorzy (
  id_autora INT AUTO_INCREMENT PRIMARY KEY,
  imie_nazwisko VARCHAR(255) NOT NULL UNIQUE
);
  2. Wypełnij tabelę autorzy unikalnymi danymi z tabeli ksiazki:
    3. 
INSERT INTO autorzy (imie_nazwisko)
SELECT DISTINCT autor FROM ksiazki;

    Polecenie SELECT DISTINCT autor wybiera wszystkich autorów z tabeli ksiazki, ale każdy autor jest zwracany tylko raz, nawet jeśli napisał wiele książek.

  3. Przebuduj tabelę ksiazki, aby używała relacji: Dodamy kolumnę id_autora i usuniemy starą kolumnę autor.
    4. 
-- Dodajemy nową kolumnę na klucz obcy
ALTER TABLE ksiazki ADD COLUMN id_autora INT NULL;

-- Uzupełniamy ją poprawnymi ID z tabeli autorzy, łącząc tabele po nazwisku
UPDATE ksiazki k JOIN autorzy a ON k.autor = a.imie_nazwisko
SET k.id_autora = a.id_autora;

-- Usuwamy starą, niepotrzebną już kolumnę z tekstem
ALTER TABLE ksiazki DROP COLUMN autor;

-- Na koniec formalnie definiujemy relację (klucz obcy)
ALTER TABLE ksiazki
ADD CONSTRAINT fk_ksiazki_autorzy
FOREIGN KEY (id_autora) REFERENCES autorzy(id_autora);

Krok 3: Stworzenie centralnej tabeli "wypozyczenia"

To najważniejsza tabela w naszym systemie. Będzie ona łączyć wszystkie pozostałe, tworząc tzw. tabelę asocjacyjną (łączącą). Każdy wiersz w tej tabeli będzie reprezentował jedno konkretne zdarzenie wypożyczenia.

Zadanie: Stworzenie i opisanie tabeli wypozyczenia

CREATE TABLE wypozyczenia (
  id_wypozyczenia INT AUTO_INCREMENT PRIMARY KEY,
  id_ksiazki INT NOT NULL,
  id_czytelnika INT NOT NULL,
  id_pracownika_wyp INT NOT NULL,
  data_wypozyczenia DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  termin_zwrotu DATE NOT NULL,
  data_faktycznego_zwrotu DATETIME NULL,
  id_pracownika_zwrot INT NULL,
  uwagi TEXT NULL,

  -- Definicja wszystkich relacji (kluczy obcych)
  FOREIGN KEY (id_ksiazki) REFERENCES ksiazki(id_ksiazki),
  FOREIGN KEY (id_czytelnika) REFERENCES czytelnicy(id_czytelnika),
  FOREIGN KEY (id_pracownika_wyp) REFERENCES pracownicy(id_pracownika),
  FOREIGN KEY (id_pracownika_zwrot) REFERENCES pracownicy(id_pracownika)
);
Co oznaczają klucze obce?

Linijka FOREIGN KEY (id_ksiazki) REFERENCES ksiazki(id_ksiazki) tworzy "magiczną" więź. Mówi serwerowi: "Wartość w kolumnie id_ksiazki w tej tabeli musi istnieć jako id_ksiazki w tabeli ksiazki". Dzięki temu nie da się zarejestrować wypożyczenia nieistniejącej książki! To jest właśnie integralność referencyjna.

Ostateczny, znormalizowany schemat bazy danych

Po wykonaniu wszystkich powyższych kroków, nasza chaotyczna struktura zamieniła się w elegancki model relacyjny, który można przedstawić na poniższym diagramie:

+-----------+      +----------------+      +---------------+
|  AUTORZY  |      |     KSIAZKI    |      |  PRACOWNICY   |
+-----------+      +----------------+      +---------------+
| id_autora (PK)|--<| id_autora (FK) |      |id_pracownika(PK)|
| imie_nazwisko|   | id_ksiazki (PK)|      | imie          |
+-----------+      | tytul          |      | nazwisko      |
                   +----------------+      +---------------+
                          |                      |
                          |                      |
                   .------'                      '------.
                   |                                    |
                   V                                    V
+------------+  <--. [ WYPOZYCZENIA ] .-->  +------------+
| CZYTELNICY |     +----------------+      |  (zwrot)   |
+------------+     |id_wypozyczenia(PK)|      +------------+
|id_czytelnika(PK)|--<|id_czytelnika(FK) |
| imie       |     | id_ksiazki   (FK)  |
| nazwisko   |     |id_pracownika_wyp(FK)|
+------------+     |id_pracownika_zwrot(FK)|
                   +----------------+
Wnioski i ogromne korzyści z normalizacji

Gratulacje! Przeszedłeś przez najważniejszy proces projektowania relacyjnej bazy danych. Twoja struktura jest teraz logiczna, wydajna i gotowa do pracy w prawdziwej aplikacji.