Zadanie 8 — Normalizacja (1NF, 2NF, 3NF) i relacje

Cel zadania

Celem tego zadania jest zrozumienie i praktyczne zastosowanie procesu normalizacji bazy danych. Nauczysz się, jak przekształcić nasze istniejące, nieoptymalne tabele w strukturę zgodną z trzema pierwszymi postaciami normalnymi (1NF, 2NF, 3NF). Zrozumiesz, dlaczego normalizacja jest kluczowa dla utrzymania integralności danych, redukcji redundancji i zwiększenia wydajności bazy. Wprowadzisz także relacje między tabelami za pomocą kluczy obcych (FOREIGN KEY) i stworzysz tabelę łączącą, aby modelować wypożyczenia książek.

Scenariusz / opis zadania

Nasza baza danych biblioteki działa, ale jest zbudowana na kruchych fundamentach. Tabela czytelnicy przechowuje adresy i telefony w pojedynczych polach, co utrudnia wyszukiwanie i aktualizację. W tabeli ksiazki nazwisko autora i kategoria powtarzają się przy każdym tytule, co jest marnotrawstwem miejsca i prowadzi do błędów przy wprowadzaniu danych. Twoim zadaniem jest przeprowadzenie gruntownej restrukturyzacji bazy, aby wyeliminować te problemy. Będziesz działał jak chirurg bazodanowy, precyzyjnie "operując" na istniejących tabelach. Najpierw doprowadzisz tabelę czytelnicy do pierwszej postaci normalnej (1NF), dzieląc złożone dane na atomowe części i tworząc osobną tabelę na numery telefonów. Następnie, zajmiesz się tabelą ksiazki, doprowadzając ją do drugiej (2NF) i trzeciej (3NF) postaci normalnej poprzez wydzielenie autorów i kategorii do osobnych tabel. Kluczowym elementem tej operacji będzie wprowadzenie relacji za pomocą kluczy obcych, które połączą nowe tabele z istniejącymi. Na koniec stworzysz zupełnie nową tabelę wypozyczenia, która połączy czytelników z książkami, pozwalając na rejestrowanie, która książka została wypożyczona przez kogo i kiedy. To zadanie pokaże Ci, jak z chaosu stworzyć logiczny, wydajny i spójny model danych.

Pomocnik wykonania

Będziemy modyfikować istniejące tabele. W środowisku produkcyjnym takie operacje zawsze należy poprzedzić wykonaniem kopii zapasowej!

Krok 1: Pierwsza Postać Normalna (1NF) - Tabela czytelnicy

Zasada 1NF: Każda komórka w tabeli musi zawierać pojedynczą, atomową (niepodzielną) wartość, a każdy wiersz musi być unikalny.

  1. Problem: Kolumny imie_nazwisko, pelny_adres i telefony łamią tę zasadę.
  2. Rozwiązanie: Podzielimy te kolumny na mniejsze części i stworzymy nową tabelę dla telefonów.
  3. Modyfikacja tabeli czytelnicy: Dodajmy nowe kolumny na imię, nazwisko i części adresu.
    ALTER TABLE czytelnicy ADD COLUMN imie VARCHAR(50) AFTER id_czytelnika, ADD COLUMN nazwisko VARCHAR(50) AFTER imie, ADD COLUMN ulica_numer VARCHAR(100) AFTER pelny_adres, ADD COLUMN kod_pocztowy VARCHAR(10) AFTER ulica_numer, ADD COLUMN miasto VARCHAR(50) AFTER kod_pocztowy;
  4. Migracja danych (imiona i nazwiska): Użyjemy funkcji SUBSTRING_INDEX do podziału.
    UPDATE czytelnicy SET imie = SUBSTRING_INDEX(imie_nazwisko, ' ', 1), nazwisko = SUBSTRING_INDEX(imie_nazwisko, ' ', -1);
  5. Utworzenie tabeli telefony:
    CREATE TABLE telefony ( id_telefonu INT AUTO_INCREMENT PRIMARY KEY, id_czytelnika INT NOT NULL, numer VARCHAR(20) NOT NULL, FOREIGN KEY (id_czytelnika) REFERENCES czytelnicy(id_czytelnika) ON DELETE CASCADE );
  6. Usunięcie starych kolumn: Po migracji danych możemy usunąć stare, niepotrzebne kolumny.
    ALTER TABLE czytelnicy DROP COLUMN imie_nazwisko, DROP COLUMN pelny_adres, DROP COLUMN telefony;

Krok 2: Druga (2NF) i Trzecia (3NF) Postać Normalna - Tabela ksiazki

Zasada 2NF: Tabela musi być w 1NF, a wszystkie atrybuty niekluczowe muszą być w pełni zależne od całego klucza głównego. (Dotyczy głównie kluczy złożonych).

Zasada 3NF: Tabela musi być w 2NF, a wszystkie atrybuty muszą zależeć tylko od klucza głównego, a nie od innych atrybutów niekluczowych.

  1. Problem: W tabeli ksiazki, autor i kategoria są powtarzającymi się tekstami. Zmiana nazwiska autora wymagałaby aktualizacji wielu wierszy. To naruszenie 3NF.
  2. Rozwiązanie: Wydzielimy autorów i kategorie do osobnych tabel.
  3. Utworzenie tabeli autorzy:
    CREATE TABLE autorzy ( id_autora INT AUTO_INCREMENT PRIMARY KEY, nazwisko_imie VARCHAR(255) UNIQUE NOT NULL );
  4. Wypełnienie tabeli autorzy unikalnymi wartościami:
    INSERT INTO autorzy (nazwisko_imie) SELECT DISTINCT autor FROM ksiazki;
  5. Utworzenie tabeli kategorie i jej wypełnienie:
    CREATE TABLE kategorie ( id_kategorii INT AUTO_INCREMENT PRIMARY KEY, nazwa VARCHAR(50) UNIQUE NOT NULL ); INSERT INTO kategorie (nazwa) SELECT DISTINCT kategoria FROM ksiazki WHERE kategoria IS NOT NULL;
  6. Modyfikacja tabeli ksiazki: Dodamy kolumny na klucze obce i usuniemy stare.
    ALTER TABLE ksiazki ADD COLUMN id_autora INT NULL AFTER autor, ADD COLUMN id_kategorii INT NULL AFTER kategoria; UPDATE ksiazki k JOIN autorzy a ON k.autor = a.nazwisko_imie SET k.id_autora = a.id_autora; UPDATE ksiazki k JOIN kategorie kat ON k.kategoria = kat.nazwa SET k.id_kategorii = kat.id_kategorii; ALTER TABLE ksiazki ADD FOREIGN KEY (id_autora) REFERENCES autorzy(id_autora), ADD FOREIGN KEY (id_kategorii) REFERENCES kategorie(id_kategorii); ALTER TABLE ksiazki DROP COLUMN autor, DROP COLUMN kategoria;

Krok 3: Tworzenie relacji - Tabela wypozyczenia

Teraz, gdy mamy znormalizowane tabele, możemy poprawnie modelować relację "wielu-do-wielu" między czytelnikami a książkami. Jeden czytelnik może wypożyczyć wiele książek, a jedna książka może być wypożyczana przez wielu czytelników (w różnym czasie).

  1. Utworzenie tabeli wypozyczenia:
    CREATE TABLE wypozyczenia ( id_wypozyczenia INT AUTO_INCREMENT PRIMARY KEY, id_ksiazki INT NOT NULL, id_czytelnika INT NOT NULL, data_wypozyczenia DATE NOT NULL, termin_zwrotu DATE NOT NULL, data_zwrotu DATE NULL, FOREIGN KEY (id_ksiazki) REFERENCES ksiazki(id_ksiazki), FOREIGN KEY (id_czytelnika) REFERENCES czytelnicy(id_czytelnika) );
  2. Omówienie kluczy obcych (FOREIGN KEY): Klucz obcy to pole (lub zestaw pól) w jednej tabeli, które jednoznacznie identyfikuje wiersz w innej tabeli. Tworzy to więzy integralności, zapewniając, że nie można np. dodać wypożyczenia dla nieistniejącej książki.
  3. Opcje kaskadowe (Cascade Options):
  4. Import danych do tabeli wypozyczenia: Przygotuj i zaimportuj plik wypozyczenia.sql z przykładowymi danymi.

Ilustracja schematu po normalizacji

+-------------+      +----------------+      +-----------------+
|   autorzy   |      |    ksiazki     |      |    kategorie    |
+-------------+      +----------------+      +-----------------+
| PK id_autora|--<{--| FK id_autora   |--}-->| PK id_kategorii |
| nazwisko_imie|      | PK id_ksiazki  |      | nazwa           |
+-------------+      | tytul, ...     |      +-----------------+
                     | FK id_kategorii|
                     +----------------+
                            |
                            {
                            |
+-------------+      +-----------------+
|  czytelnicy |      |  wypozyczenia   |
+-------------+      +-----------------+
|PK id_czytelnika|--<{--|FK id_czytelnika |
| imie, ...   |      |FK id_ksiazki    |
+-------------+      |PK id_wypozyczenia|
      |              | daty, ...       |
      {              +-----------------+
      |
+-------------+
|   telefony  |
+-------------+
|PK id_telefonu|
|FK id_czytelnika|
| numer       |
+-------------+

Informacje dodatkowe / uwagi