Po osiągnięciu trzeciej postaci normalnej (3NF) nasze bazy danych są już w dużej mierze wolne od anomalii. Istnieje jednak rzadszy, ale wciąż istotny problem, którym zajmuje się czwarta postać normalna (4NF): zależności wielowartościowe (multi-valued dependencies).
Zależność wielowartościowa występuje, gdy obecność jednego wiersza z wartościami (A, B) implikuje obecność innego wiersza z wartościami (A, C), a wartości B i C są od siebie niezależne. Problem ten pojawia się w tabelach, które próbują opisać dwie lub więcej niezależnych relacji "jeden-do-wielu" w jednej tabeli.
Tabela jest w czwartej postaci normalnej (4NF), jeśli:
W praktyce oznacza to, że tabela nie powinna przechowywać dwóch lub więcej niezależnych, wielowartościowych faktów na temat encji. Jeśli tak jest, należy ją zdekomponować.
Zrozumienie problemu zależności wielowartościowej poprzez analizę tabeli, która celowo łamie zasady 4NF.
Wyobraźmy sobie, że chcemy rozszerzyć naszą bazę danych o informacje na temat umiejętności (np. języków programowania) oraz hobby naszych pracowników. Jeden pracownik może mieć wiele umiejętności i jednocześnie wiele hobby. Umiejętności i hobby są od siebie całkowicie niezależne. Naiwne podejście polegałoby na stworzeniu jednej tabeli `pracownicy_rozwój` do przechowywania tych informacji.
Spójrzmy na proponowaną strukturę tabeli `pracownicy_rozwój`:
+---------------+----------------+---------------+ | id_pracownika | umiejetnosc | hobby | +---------------+----------------+---------------+ | 1 | SQL | Wspinaczka | | 1 | SQL | Żeglarstwo | | 1 | Python | Wspinaczka | | 1 | Python | Żeglarstwo | | 2 | Java | Fotografia | +---------------+----------------+---------------+
W tej tabeli kluczem kandydującym jest cała trójka kolumn `(id_pracownika, umiejetnosc, hobby)`. Tabela jest w 3NF, ale łamie 4NF. Dlaczego?
Mamy tu dwie niezależne zależności wielowartościowe:
id_pracownika ->> umiejetnosc (jeden pracownik może mieć wiele umiejętności)id_pracownika ->> hobby (jeden pracownik może mieć wiele hobby)Ponieważ umiejętności i hobby pracownika nr 1 są od siebie niezależne, musimy stworzyć kartezjański iloczyn wszystkich jego umiejętności i wszystkich hobby, aby zapisać kompletny fakt. To prowadzi do ogromnej redundancji i anomalii:
-- Tworzenie tabeli z zależnością wielowartościową
CREATE TABLE pracownicy_rozwoj (
id_pracownika_fk INT,
umiejetnosc VARCHAR(100),
hobby VARCHAR(100),
PRIMARY KEY (id_pracownika_fk, umiejetnosc, hobby),
FOREIGN KEY (id_pracownika_fk) REFERENCES pracownicy(id_pracownika)
);
-- Wstawienie danych demonstrujących problem
INSERT INTO pracownicy_rozwoj (id_pracownika_fk, umiejetnosc, hobby) VALUES
(1, 'SQL', 'Wspinaczka'),
(1, 'SQL', 'Żeglarstwo'),
(1, 'Python', 'Wspinaczka'),
(1, 'Python', 'Żeglarstwo'),
(2, 'Java', 'Fotografia');
W tym zadaniu zidentyfikowaliśmy problem zależności wielowartościowej. Zrozumieliśmy, że próba przechowywania dwóch lub więcej niezależnych, wielowartościowych atrybutów w jednej tabeli prowadzi do redundancji i poważnych anomalii, nawet jeśli tabela spełnia warunki 3NF.
Praktyczne zastosowanie zasad 4NF poprzez dekompozycję tabeli z zależnością wielowartościową na dwie osobne tabele.
Rozwiązaniem problemu zidentyfikowanego w zadaniu 1 jest dekompozycja. Musimy rozbić tabelę `pracownicy_rozwój` na dwie osobne tabele, z których każda będzie przechowywać tylko jeden wielowartościowy fakt. Stworzymy tabelę `pracownicy_umiejetnosci` do przechowywania umiejętności oraz tabelę `pracownicy_hobby` do przechowywania hobby. Każda z tych tabel będzie miała złożony klucz główny składający się z ID pracownika i odpowiedniego atrybutu.
+-------------------+ +---------------------------+
| pracownicy | | pracownicy_umiejetnosci |
+-------------------+ +---------------------------+
| PK| id_pracownika |------>| PK,FK| id_pracownika_fk |
| | imie | | PK | umiejetnosc |
| | nazwisko | +---------------------------+
| | ... |
+-------------------+ +---------------------------+
| | pracownicy_hobby |
| +---------------------------+
+-------------->| PK,FK| id_pracownika_fk |
| PK | hobby |
+---------------------------+
-- Krok 1: Tworzenie tabeli na umiejętności
CREATE TABLE pracownicy_umiejetnosci (
id_pracownika_fk INT,
umiejetnosc VARCHAR(100),
PRIMARY KEY (id_pracownika_fk, umiejetnosc),
FOREIGN KEY (id_pracownika_fk) REFERENCES pracownicy(id_pracownika)
);
-- Krok 2: Tworzenie tabeli na hobby
CREATE TABLE pracownicy_hobby (
id_pracownika_fk INT,
hobby VARCHAR(100),
PRIMARY KEY (id_pracownika_fk, hobby),
FOREIGN KEY (id_pracownika_fk) REFERENCES pracownicy(id_pracownika)
);
-- Krok 3: Wypełnienie nowych tabel
INSERT INTO pracownicy_umiejetnosci (id_pracownika_fk, umiejetnosc)
SELECT DISTINCT id_pracownika_fk, umiejetnosc FROM pracownicy_rozwój;
INSERT INTO pracownicy_hobby (id_pracownika_fk, hobby)
SELECT DISTINCT id_pracownika_fk, hobby FROM pracownicy_rozwój;
-- Krok 4: Usunięcie starej tabeli
DROP TABLE pracownicy_rozwój;
W tym zadaniu pomyślnie doprowadziliśmy tabelę do czwartej postaci normalnej. Kluczowym krokiem była dekompozycja tabeli zawierającej niezależne zależności wielowartościowe na dwie osobne tabele. Każda z nowych tabel przechowuje teraz jeden, spójny fakt, co eliminuje redundancję i anomalie. Baza danych jest teraz bardziej logiczna i łatwiejsza w zarządzaniu.
Analiza tabel znormalizowanych w poprzednich laboratoriach (`autorzy`, `ksiazki`, `czytelnicy`, `pracownicy`, `dzialy`, `kody_pocztowe`) w celu potwierdzenia ich zgodności z czwartą postacią normalną.
Dobra wiadomość jest taka, że jeśli proces normalizacji do 3NF został przeprowadzony poprawnie, większość tabel w typowej bazie danych jest już zgodna z 4NF. Problemy z zależnościami wielowartościowymi są stosunkowo rzadkie i pojawiają się w specyficznych przypadkach. Twoim zadaniem jest przeanalizowanie naszych istniejących, znormalizowanych tabel i uzasadnienie, dlaczego spełniają one warunki 4NF.
Wszystkie nasze tabele opisują jeden, konkretny fakt lub encję. Tabela `pracownicy` opisuje pracownika, tabela `ksiazki` opisuje książkę, a tabela `ksiazki_autorzy` opisuje pojedynczy fakt powiązania między książką a autorem. Żadna z nich nie próbuje przechowywać dwóch niezależnych, wielowartościowych informacji jednocześnie, tak jak robiła to problematyczna tabela `pracownicy_rozwój`.
W tym laboratorium nauczyłeś się rozpoznawać i eliminować zależności wielowartościowe, które są kluczowym elementem czwartej postaci normalnej. Zrozumiałeś, że poprawne modelowanie wymaga, aby każda tabela przechowywała informacje na jeden, konkretny temat, a niezależne fakty wielowartościowe powinny być rozdzielone do osobnych tabel. Potwierdziłeś również, że dobrze zaprojektowane tabele, które przeszły normalizację do 3NF, bardzo często automatycznie spełniają również warunki 4NF.