Normalizacja bazy danych to proces organizowania kolumn i tabel w relacyjnej bazie danych w celu zminimalizowania redundancji (powtarzania się) danych. Głównym celem normalizacji jest podział większych, nieporęcznych tabel na mniejsze, dobrze zorganizowane tabele oraz zdefiniowanie relacji między nimi. Prawidłowo przeprowadzona normalizacja zapobiega anomaliom danych (problemom z wstawianiem, aktualizacją i usuwaniem danych) oraz zapewnia ich integralność.
Proces ten opisany jest za pomocą tzw. postaci normalnych (NF). W tym laboratorium skupimy się na pierwszych dwóch postaciach normalnych.
Zrozumienie, czym jest pierwsza postać normalna (1NF) poprzez stworzenie i analizę tabeli, która celowo łamie jej zasady. Zadanie ma na celu pokazanie problemów wynikających z przechowywania wielu wartości w jednym polu.
Wyobraź sobie, że otrzymałeś zadanie szybkiego zaprojektowania tabeli do przechowywania danych czytelników. Aby "uprościć" strukturę, decydujesz się umieścić imię i nazwisko w jednej kolumnie, a cały adres (kod pocztowy, miasto, ulica, numer) w drugiej. Stworzysz tabelę `czytelnicy_nn` (nn - nieznormalizowana), która będzie miała tylko dwie kolumny: `czytelnik` i `adres`. Następnie zaimportujesz do niej dane z przygotowanych plików `czytelnicy_nn.csv` i `czytelnicy_nn.sql`. Po zaimportowaniu danych spróbujesz wykonać proste zapytania, takie jak znalezienie wszystkich czytelników z Warszawy lub posortowanie ich alfabetycznie po nazwisku. Szybko odkryjesz, że tak prosta struktura prowadzi do ogromnych problemów z wyszukiwaniem, sortowaniem i zarządzaniem danymi, co doskonale ilustruje, dlaczego normalizacja jest tak ważna.
Tabela jest w pierwszej postaci normalnej (1NF), jeśli spełnia dwa podstawowe warunki:
Nasza tabela `czytelnicy_nn` łamie pierwszy warunek w obu kolumnach: pole `czytelnik` zawiera zarówno imię, jak i nazwisko, a pole `adres` zawiera kod pocztowy, miasto i ulicę z numerem. Są to wartości złożone, a nie atomowe.
CREATE TABLE czytelnicy_nn (
czytelnik VARCHAR(255),
adres VARCHAR(255)
);
-- Import z CSV
LOAD DATA INFILE 'C:/temp/czytelnicy_nn.csv'
INTO TABLE czytelnicy_nn
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';
-- Import z SQL
SOURCE C:/temp/czytelnicy_nn.sql;
-- Próba wyszukania czytelników z Krakowa
SELECT * FROM czytelnicy_nn WHERE adres LIKE '%Kraków%';
+-----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+-------+ | czytelnik | varchar(255) | YES | | NULL | | | adres | varchar(255) | YES | | NULL | | +-----------+--------------+------+-----+---------+-------+
To zadanie było praktyczną lekcją na temat Pierwszej Postaci Normalnej. Zobaczyłeś, że przechowywanie złożonych, wieloczęściowych danych (jak imię i nazwisko, czy pełny adres) w jednym polu jest bardzo złym pomysłem. Taka struktura łamie zasadę atomowości danych (niepodzielności), co prowadzi do anomalii i ekstremalnie utrudnia operacje na danych. W kolejnym zadaniu naprawimy ten projekt.
Praktyczne zastosowanie zasad Pierwszej Postaci Normalnej poprzez transformację nieznormalizowanej tabeli `czytelnicy_nn` do nowej, poprawnej struktury.
Po bolesnej lekcji z poprzedniego zadania, wiesz już, że obecny projekt tabeli `czytelnicy_nn` jest nie do przyjęcia. Twoim zadaniem jest stworzenie nowej tabeli, `czytelnicy`, która będzie spełniać zasady 1NF. Musisz rozbić kolumny `czytelnik` i `adres` na atomowe części. Kolumna `czytelnik` powinna zostać podzielona na `imie` i `nazwisko`. Kolumna `adres` na `kod_pocztowy`, `miasto` i `ulica`. Co więcej, nowa tabela musi mieć klucz główny, aby jednoznacznie identyfikować każdy wiersz. Po stworzeniu nowej, znormalizowanej tabeli, nie będziemy migrować do niej danych automatycznie, ale zastanowimy się, jak takie zapytania mogłyby wyglądać i jakie korzyści płyną z nowej struktury.
Aby przekształcić tabelę do pierwszej postaci normalnej, należy:
CREATE TABLE czytelnicy (
id_czytelnika INT AUTO_INCREMENT PRIMARY KEY,
imie VARCHAR(100) NOT NULL,
nazwisko VARCHAR(150) NOT NULL,
kod_pocztowy VARCHAR(6),
miasto VARCHAR(100),
ulica VARCHAR(200)
);
-- Wstawienie przykładowych danych
INSERT INTO czytelnicy (imie, nazwisko, kod_pocztowy, miasto, ulica) VALUES
('Jan', 'Kowalski', '00-123', 'Warszawa', 'ul. Prosta 1'),
('Anna', 'Nowak', '30-059', 'Kraków', 'ul. Akademicka 5');
-- Proste i precyzyjne wyszukiwanie
SELECT * FROM czytelnicy WHERE miasto = 'Kraków';
-- Proste i poprawne sortowanie
SELECT * FROM czytelnicy ORDER BY nazwisko;
+---------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+----------------+ | id_czytelnika | int(11) | NO | PRI | NULL | auto_increment | | imie | varchar(100) | NO | | NULL | | | nazwisko | varchar(150) | NO | | NULL | | | kod_pocztowy | varchar(6) | YES | | NULL | | | miasto | varchar(100) | YES | | NULL | | | ulica | varchar(200) | YES | | NULL | | +---------------+--------------+------+-----+---------+----------------+
W tym zadaniu z sukcesem przekształciłeś źle zaprojektowaną tabelę do Pierwszej Postaci Normalnej. Zobaczyłeś na własne oczy, jak podział danych na atomowe części i dodanie klucza głównego radykalnie upraszcza i usprawnia pracę z danymi. Sortowanie, filtrowanie i zarządzanie danymi stało się precyzyjne i wydajne. To fundament, na którym buduje się solidne i skalowalne bazy danych.
Zrozumienie, dlaczego niektóre tabele z prostym kluczem głównym automatycznie spełniają warunki 2NF i na czym polegałby problem, gdyby klucz był złożony.
Przeanalizujemy teraz tabelę `ksiazki` z poprzedniego laboratorium. Posiada ona prosty klucz główny (`id_ksiazki`). Twoim zadaniem jest zrozumienie, dlaczego taka struktura automatycznie spełnia zasady Drugiej Postaci Normalnej. Następnie, w ramach ćwiczenia myślowego, wyobrazisz sobie hipotetyczną tabelę `wypozyczenia_egzemplarza`, gdzie klucz główny jest złożony (np. z `id_ksiazki` i `nr_egzemplarza`). Na tym przykładzie zidentyfikujesz potencjalną zależność częściową, która łamałaby 2NF, np. gdyby w tej tabeli znajdował się atrybut `tytul`, który zależy tylko od `id_ksiazki`, a nie od całego klucza złożonego. To ćwiczenie ugruntuje Twoją wiedzę na temat 2NF bez konieczności tworzenia skomplikowanych struktur.
Tabela jest w drugiej postaci normalnej (2NF), jeśli:
Problem zależności częściowej, który rozwiązuje 2NF, dotyczy wyłącznie tabel z kluczem głównym złożonym z wielu kolumn. Jeśli klucz główny jest prosty (jednokolumnowy), jak w tabeli `ksiazki`, nie może istnieć zależność od "części" klucza. Dlatego każda tabela w 1NF z prostym kluczem głównym jest automatycznie w 2NF.
Spójrzmy na strukturę tabeli `ksiazki`:
+-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | id_ksiazki | int(11) | NO | PRI | NULL | auto_increment | | tytul | varchar(255) | NO | | NULL | | | autor | varchar(255) | NO | | NULL | | | rok_wydania | int(11) | YES | | NULL | | | isbn | varchar(13) | YES | UNI | NULL | | +-------------+--------------+------+-----+---------+----------------+
Klucz główny to `id_ksiazki`. Wszystkie pozostałe atrybuty (`tytul`, `autor`, `rok_wydania`, `isbn`) opisują konkretną książkę identyfikowaną przez ten klucz. Są one w pełni zależne od całego (i jedynego) klucza głównego. Tabela jest więc w 2NF.
Wyobraźmy sobie tabelę, która łamie 2NF:
+-------------+----------------+------------+--------------------+ | id_ksiazki | nr_egzemplarza | stan | tytul | <-- Klucz główny: (id_ksiazki, nr_egzemplarza) +-------------+----------------+------------+--------------------+ | 1 | 1 | Dostępny | Władca Pierścieni | | 1 | 2 | Wypożyczony| Władca Pierścieni | | 2 | 1 | Dostępny | Lalka | +-------------+----------------+------------+--------------------+
W tej tabeli atrybut `stan` zależy od całego klucza (od konkretnego egzemplarza konkretnej książki), ale atrybut `tytul` zależy tylko od części klucza (`id_ksiazki`). To jest właśnie zależność częściowa. Powoduje ona redundancję (tytuł się powtarza) i anomalie. Poprawne rozwiązanie to pozostawienie `tytulu` w tabeli `ksiazki` i łączenie tabel za pomocą relacji.
W tym zadaniu nauczyłeś się, że Druga Postać Normalna jest istotna głównie w kontekście kluczy złożonych. Zrozumiałeś, dlaczego tabele z prostym kluczem głównym, które są w 1NF, automatycznie spełniają warunki 2NF. Analiza hipotetycznego przypadku pokazała Ci, na czym polega problem zależności częściowej i dlaczego należy go unikać, aby zapobiegać redundancji danych.
Utrwalenie wiedzy na temat 2NF poprzez analizę kolejnej tabeli z prostym kluczem głównym.
Podobnie jak w przypadku tabeli `ksiazki`, teraz przyjrzymy się znormalizowanej tabeli `czytelnicy`. Twoim zadaniem jest potwierdzenie, że ta tabela również automatycznie spełnia zasady 2NF i uzasadnienie dlaczego. Analiza kolejnego przykładu pomoże Ci ugruntować wiedzę, że problem zależności częściowych nie dotyczy tabel z prostym kluczem głównym, co jest bardzo częstym przypadkiem w projektowaniu baz danych.
Oto struktura tabeli `czytelnicy`, którą utworzyliśmy w zadaniu 2:
+---------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+----------------+ | id_czytelnika | int(11) | NO | PRI | NULL | auto_increment | | imie | varchar(100) | NO | | NULL | | | nazwisko | varchar(150) | NO | | NULL | | | kod_pocztowy | varchar(6) | YES | | NULL | | | miasto | varchar(100) | YES | | NULL | | | ulica | varchar(200) | YES | | NULL | | +---------------+--------------+------+-----+---------+----------------+
Kluczem głównym jest `id_czytelnika`. Wszystkie pozostałe atrybuty (`imie`, `nazwisko`, `kod_pocztowy`, `miasto`, `ulica`) opisują cechy konkretnego czytelnika, jednoznacznie identyfikowanego przez `id_czytelnika`. Nie ma tu żadnych atrybutów, które zależałyby od "części" klucza, ponieważ klucz jest niepodzielny. W związku z tym tabela `czytelnicy` jest w 2NF.
Analizując tabelę `czytelnicy`, po raz kolejny potwierdziłeś zasadę, że każda tabela w 1NF z prostym (jednokolumnowym) kluczem głównym jest automatycznie w 2NF. To ważna i użyteczna reguła, która upraszcza proces normalizacji w wielu praktycznych przypadkach.
Świadome zaprojektowanie i analiza tabeli, która łamie trzy pierwsze postacie normalne, aby zrozumieć bardziej złożone problemy z redundancją i zależnościami danych.
Twoim kolejnym zadaniem jest stworzenie tabeli `pracownicy_nn` (nieznormalizowana), która będzie jeszcze gorszym przykładem projektu niż `czytelnicy_nn`. Tabela ta będzie celowo łamać nie tylko pierwszą, ale także drugą i trzecią postać normalną. Będzie zawierać dane złożone (jak `imie_nazwisko`), dane powtarzające się (redundantne) oraz zależności przechodnie. Po stworzeniu tabeli zaimportujesz do niej 50 rekordów z pliku `pracownicy_nn_import.sql`. Następnie przeanalizujesz jej strukturę i wskażesz konkretne problemy, które uniemożliwiają jej zgodność z 1NF, 2NF i 3NF. To ćwiczenie przygotuje Cię do zrozumienia, dlaczego wyższe postacie normalne są potrzebne do tworzenia profesjonalnych baz danych.
CREATE TABLE pracownicy_nn (
imie_nazwisko VARCHAR(255),
stanowisko_dzial VARCHAR(255),
dane_kontaktowe VARCHAR(255),
data_zatrudnienia DATE,
pensja DECIMAL(10, 2),
kierownik_dzialu VARCHAR(255)
);
-- Import danych
SOURCE C:/temp/pracownicy_nn_import.sql;
-- Wyświetlenie danych do analizy
SELECT * FROM pracownicy_nn LIMIT 10;
+-------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+---------------+------+-----+---------+-------+ | imie_nazwisko | varchar(255) | YES | | NULL | | | stanowisko_dzial | varchar(255) | YES | | NULL | | | dane_kontaktowe | varchar(255) | YES | | NULL | | | data_zatrudnienia | date | YES | | NULL | | | pensja | decimal(10,2) | YES | | NULL | | | kierownik_dzialu | varchar(255) | YES | | NULL | | +-------------------+---------------+------+-----+---------+-------+
W tym zadaniu świadomie stworzyłeś tabelę z licznymi wadami projektowymi, łamiącą trzy pierwsze postacie normalne. Zidentyfikowałeś problemy wynikające z nieatomowych danych (problem 1NF), zależności częściowych (problem 2NF) i zależności przechodnich (problem 3NF). Zrozumienie tych problemów jest kluczowe przed przystąpieniem do procesu normalizacji, który przeprowadzisz w kolejnych zadaniach.
Przeprowadzenie pierwszego kroku normalizacji tabeli `pracownicy_nn` – doprowadzenie jej do Pierwszej Postaci Normalnej (1NF).
Twoim zadaniem jest naprawienie tabeli `pracownicy_nn` tak, aby była zgodna z 1NF. Musisz rozbić wszystkie kolumny zawierające dane złożone na kolumny atomowe. Oznacza to, że `imie_nazwisko` musi stać się dwiema osobnymi kolumnami, `stanowisko_dzial` również, a `dane_kontaktowe` zostaną podzielone na `email` i `telefon`. Stworzysz nową tabelę `pracownicy_1nf` o poprawnej strukturze. Na tym etapie celowo nie dodasz jeszcze klucza głównego, aby w pełni zrozumieć, że 1NF dotyczy głównie atomowości danych. Zwrócisz uwagę studentom, że brak unikalnego identyfikatora jest nadal poważnym problemem, którym zajmiemy się w następnym kroku.
CREATE TABLE pracownicy_1nf (
imie VARCHAR(100),
nazwisko VARCHAR(150),
stanowisko VARCHAR(100),
dzial VARCHAR(100),
email VARCHAR(150),
telefon VARCHAR(20),
data_zatrudnienia DATE,
pensja DECIMAL(10, 2),
kierownik_dzialu VARCHAR(255)
);
+-------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+---------------+------+-----+---------+-------+ | imie | varchar(100) | YES | | NULL | | | nazwisko | varchar(150) | YES | | NULL | | | stanowisko | varchar(100) | YES | | NULL | | | dzial | varchar(100) | YES | | NULL | | | email | varchar(150) | YES | | NULL | | | telefon | varchar(20) | YES | | NULL | | | data_zatrudnienia | date | YES | | NULL | | | pensja | decimal(10,2) | YES | | NULL | | | kierownik_dzialu | varchar(255) | YES | | NULL | | +-------------------+---------------+------+-----+---------+-------+
Pomyślnie doprowadziłeś tabelę do Pierwszej Postaci Normalnej, rozwiązując problem atomowości danych. Jednakże, jak zauważyłeś, tabela nadal nie jest idealna. Brak klucza głównego oznacza, że nie ma gwarancji unikalności wierszy. Moglibyśmy mieć w firmie dwóch Janów Kowalskich zatrudnionych tego samego dnia, a bez unikalnego identyfikatora nie bylibyśmy w stanie ich rozróżnić. Ten problem rozwiążemy w kolejnym zadaniu, dążąc do Drugiej Postaci Normalnej.
Doprowadzenie tabeli `pracownicy` do Drugiej Postaci Normalnej (2NF) poprzez dodanie unikalnego identyfikatora (klucza kandydującego) i omówienie korzyści z tego wynikających.
Wiesz już, że Twoja tabela `pracownicy_1nf` potrzebuje unikalnego identyfikatora. W tym zadaniu rozszerzysz jej schemat, dodając kolumnę `pesel`, która będzie pełnić rolę klucza kandydującego (a docelowo głównego). PESEL jest idealnym kandydatem, ponieważ jest unikalny dla każdej osoby. Zmodyfikujesz strukturę tabeli, dodając nową kolumnę z ograniczeniem `UNIQUE`. Następnie, aby wypełnić tę kolumnę danymi, zaimportujesz przygotowane wcześniej pliki `pracownicy_pesel.csv` i `pracownicy_pesel.sql`. Na koniec omówisz, dlaczego ten krok był niezbędny do osiągnięcia 2NF i jakie korzyści płyną z posiadania klucza głównego, pokazując to na 5 nowych przykładach zapytań SQL.
-- Finalna, poprawna struktura tabeli (bez zależności przechodnich)
CREATE TABLE pracownicy (
id_pracownika INT AUTO_INCREMENT PRIMARY KEY,
pesel VARCHAR(11) UNIQUE NOT NULL,
imie VARCHAR(100) NOT NULL,
nazwisko VARCHAR(150) NOT NULL,
stanowisko VARCHAR(100),
dzial VARCHAR(100),
email VARCHAR(150),
telefon VARCHAR(20),
data_zatrudnienia DATE,
pensja DECIMAL(10, 2)
);
-- Załóżmy, że tabela jest już wypełniona danymi z poprzedniego kroku
-- Aktualizacja PESELi za pomocą skryptu SQL
SOURCE C:/temp/pracownicy_pesel.sql;
-- 1. Znajdź konkretnego pracownika po jego unikalnym PESELu
-- Dzięki normalizacji i kluczowi kandydującemu, mamy pewność, że znajdziemy dokładnie jedną osobę.
SELECT * FROM pracownicy WHERE pesel = '85010112345';
-- 2. Zlicz pracowników w każdym dziale
-- Atomowa kolumna 'dzial' pozwala na łatwe grupowanie danych, co było niemożliwe w tabeli `pracownicy_nn`.
SELECT dzial, COUNT(id_pracownika) AS liczba_pracownikow FROM pracownicy GROUP BY dzial;
-- 3. Znajdź pracowników, którzy mają podany numer telefonu
-- Oddzielenie telefonu od e-maila (1NF) umożliwia precyzyjne wyszukiwanie po jednym z mediów kontaktowych.
SELECT imie, nazwisko FROM pracownicy WHERE telefon = '123-456-789';
-- 4. Wyświetl listę pracowników posortowaną alfabetycznie wg nazwiska, a następnie imienia
-- Dzięki osobnym kolumnom dla imienia i nazwiska (1NF), możemy tworzyć zaawansowane, wielopoziomowe sortowanie.
SELECT nazwisko, imie, stanowisko FROM pracownicy ORDER BY nazwisko ASC, imie ASC;
-- 5. Zaktualizuj pensję konkretnego pracownika, używając jego ID
-- Klucz główny (id_pracownika) gwarantuje, że zaktualizujemy dane tylko jednej, właściwej osoby, nawet jeśli w firmie jest dwóch Janów Kowalskich.
UPDATE pracownicy SET pensja = 5000.00 WHERE id_pracownika = 1;
W tym zadaniu doprowadziłeś tabelę do stanu, w którym spełnia ona zasady Drugiej Postaci Normalnej. Najważniejszym krokiem było dodanie klucza głównego (`id_pracownika`) oraz klucza kandydującego (`pesel`), co zapewniło unikalność każdemu rekordowi. Przeanalizowałeś również praktyczne korzyści płynące z normalizacji, takie jak precyzyjne wyszukiwanie, łatwe grupowanie i sortowanie danych oraz bezpieczna aktualizacja rekordów. Zauważ jednak, że problem zależności przechodniej (kierownik zależny od działu) nadal istnieje - tym zajmuje się Trzecia Postać Normalna (3NF), którą poznasz w przyszłości.