Postać normalna Boyce'a-Codda (BCNF): definicja, przykład i dekompozycja krok po kroku

Postać normalna Boyce’a-Codda (BCNF) to warunek, zgodnie z którym w tabeli każda nietrywialna zależność funkcyjna X → Y musi mieć po lewej stronie nadklucz. Mówiąc prościej: jeśli jakaś kolumna (lub zestaw kolumn) jednoznacznie wyznacza inną kolumnę, to sama musi jednoznacznie identyfikować cały wiersz.

BCNF to nieco ostrzejsza wersja trzeciej postaci normalnej (3NF). W większości praktycznych schematów tabela w 3NF jest od razu w BCNF, a różnica pojawia się tylko w specyficznym przypadku: gdy tabela ma kilka złożonych kluczy kandydujących, które mają wspólne kolumny. Poniżej znajdziesz definicję, przykład tabeli łamiącej BCNF, algorytm dekompozycji i gotowy schemat w SQL.

Pojęcia potrzebne do zrozumienia BCNF

Definicja BCNF opiera się na kilku terminach z teorii relacyjnych baz danych. Bez nich łatwo się pogubić, więc krótko je przypomnę.

• Zależność funkcyjna X → Y oznacza, że dwie krotki (wiersze) o tych samych wartościach X muszą mieć te same wartości Y. Przykład: pesel → data_urodzenia.
• Zależność trywialna to taka, w której Y jest podzbiorem X, np. {imie, nazwisko} → imie. Jest prawdziwa zawsze i nie niesie informacji.
• Nadklucz to zbiór atrybutów, który jednoznacznie wyznacza cały wiersz. Każdy zbiór zawierający klucz też jest nadkluczem.
• Klucz kandydujący to minimalny nadklucz: nie da się z niego usunąć żadnego atrybutu bez utraty unikalności. Tabela może mieć kilka kluczy kandydujących, a jeden z nich wybierasz na klucz główny.
• Atrybut kluczowy (ang. prime attribute) to atrybut, który wchodzi w skład dowolnego klucza kandydującego.
• Domknięcie X⁺ to zbiór wszystkich atrybutów, które da się wyprowadzić z X, stosując znane zależności funkcyjne. Jeśli X⁺ obejmuje wszystkie kolumny tabeli, X jest nadkluczem.

Ważne: zależności funkcyjne wynikają z reguł biznesowych, a nie z aktualnej zawartości tabeli. To, że w pięciu wierszach przypadkiem nie ma powtórzeń, niczego nie dowodzi.

Definicja postaci normalnej Boyce’a-Codda

Relacja R jest w postaci normalnej Boyce’a-Codda, jeśli dla każdej nietrywialnej zależności funkcyjnej X → Y zachodzącej w R zbiór X jest nadkluczem R.

Dla porównania, definicja 3NF brzmi: dla każdej nietrywialnej zależności X → Y albo X jest nadkluczem, albo każdy atrybut z Y (poza tymi z X) jest atrybutem kluczowym. Ten drugi warunek to jedyna furtka, którą BCNF zamyka. 3NF toleruje sytuację, w której atrybut niebędący kluczem wyznacza fragment klucza; BCNF nie toleruje jej wcale.

Postać tę zaproponowali w 1974 roku Raymond F. Boyce i Edgar F. Codd (twórca modelu relacyjnego), stąd nazwa. Bywa nazywana „3,5NF”, bo stoi między 3NF a 4NF.

Postacie normalne w jednej tabeli

Jeśli chcesz odświeżyć sobie wcześniejsze postacie i zrozumieć, kiedy celowo się z nich wycofać, zajrzyj do artykułu o normalizacji i denormalizacji baz danych.

Przykład tabeli, która jest w 3NF, ale nie w BCNF

Weźmy uczelnianą tabelę zapisów na zajęcia. Obowiązują w niej trzy reguły biznesowe:

1. Student zapisuje się na dany przedmiot do dokładnie jednego wykładowcy.
2. Każdy wykładowca prowadzi tylko jeden przedmiot.
3. Ten sam przedmiot może prowadzić kilku wykładowców.

Z reguł wynikają dwie zależności funkcyjne:

• {student, przedmiot} → wykladowca (reguła 1),
• wykladowca → przedmiot (reguła 2).

Krok 1: wyznacz klucze kandydujące

Policz domknięcia. {student, przedmiot}⁺ = {student, przedmiot, wykladowca}, czyli to klucz. {student, wykladowca}⁺: z wykladowca → przedmiot dostajesz przedmiot, więc też wszystkie kolumny. To drugi klucz kandydujący. Pojedyncze kolumny kluczami nie są.

Tabela ma więc dwa złożone klucze kandydujące, które dzielą kolumnę student. To dokładnie ten przypadek, w którym 3NF i BCNF się rozjeżdżają.

Krok 2: sprawdź 3NF

Wszystkie trzy kolumny są atrybutami kluczowymi (każda należy do któregoś klucza). Nie ma więc atrybutów niekluczowych, które mogłyby zależeć częściowo lub przechodnio od klucza. Tabela spełnia 2NF i 3NF.

Krok 3: sprawdź BCNF

Zależność wykladowca → przedmiot jest nietrywialna, a wykladowca nie jest nadkluczem (nie identyfikuje wiersza, bo dr Nowak ma wielu studentów). Warunek BCNF jest złamany.

Jakie anomalie to powoduje

Na pierwszy rzut oka tabela wygląda niewinnie, ale ma klasyczne anomalie:

• Anomalia modyfikacji: informacja „dr Nowak prowadzi Bazy danych” powtarza się w wierszu każdego studenta. Gdy zmienisz ją tylko w części wierszy, baza zacznie twierdzić, że dr Nowak prowadzi dwa przedmioty.
• Anomalia wstawiania: nie zapiszesz, że nowy wykładowca dr Zieliński prowadzi Algorytmy, dopóki nie zapisze się do niego żaden student, bo student jest częścią każdego klucza i nie może być pusty.
• Anomalia usuwania: jeśli Piotr wypisze się z Baz danych, znika jedyna informacja, że dr Kowalska w ogóle prowadzi ten przedmiot.

Jak sprowadzić tabelę do BCNF: algorytm dekompozycji

Standardowy algorytm jest prosty i działa dla dowolnej relacji:

1. Wyznacz klucze kandydujące relacji R.
2. Znajdź nietrywialną zależność X → Y, w której X nie jest nadkluczem. Jeśli takiej nie ma, R jest w BCNF i kończysz.
3. Policz domknięcie X⁺.
4. Rozbij R na dwie relacje: R1 = X⁺ oraz R2 = X ∪ (R − X⁺). Wspólną częścią obu jest X, które w R1 jest kluczem.
5. Rzutuj zależności funkcyjne na R1 i R2 i powtórz algorytm dla każdej z nich.

Rozbicie w kroku 4 jest zawsze bezstratne: złączenie R1 i R2 po X odtworzy dokładnie oryginalne wiersze, bez fałszywych kombinacji. Wynika to z tego, że wspólny zbiór atrybutów X jest kluczem jednej z części.

Dla naszego przykładu: X = wykladowca, X⁺ = {wykladowca, przedmiot}. Powstają dwie tabele:

• prowadzacy(wykladowca, przedmiot) z kluczem wykladowca,
• zapisy(student, wykladowca) z kluczem {student, wykladowca}.

Obie są w BCNF. Informacja o tym, kto prowadzi jaki przedmiot, jest zapisana raz, można dodać wykładowcę bez studentów, a wypisanie studenta niczego nie kasuje.

Haczyk: utrata zależności funkcyjnej

Po rozbiciu zależność {student, przedmiot} → wykladowca nie mieści się w żadnej z tabel, bo student i przedmiot są teraz w różnych miejscach. Nic nie przeszkodzi wstawić do zapisy wiersza (Anna, dr Kowalska), choć Anna jest już zapisana na Bazy danych do dr. Nowaka. Reguła biznesowa nr 1 przestaje być pilnowana przez schemat.

To nie jest wada tego konkretnego przykładu, tylko znana właściwość BCNF: zawsze istnieje dekompozycja bezstratna, ale nie zawsze istnieje taka, która jednocześnie zachowuje wszystkie zależności. Dla 3NF obie własności da się zagwarantować jednocześnie (algorytm syntezy Bernsteina), dlatego część projektantów świadomie zatrzymuje się na 3NF.

Schemat w SQL: BCNF i kompromis z kluczem obcym

Czysta dekompozycja do BCNF w SQL (składnia działa w PostgreSQL, MySQL/MariaDB i SQL Server z drobnymi zmianami typów):

CREATE TABLE prowadzacy (
    wykladowca  VARCHAR(100) PRIMARY KEY,
    przedmiot   VARCHAR(100) NOT NULL
);

CREATE TABLE zapisy (
    student     VARCHAR(100) NOT NULL,
    wykladowca  VARCHAR(100) NOT NULL REFERENCES prowadzacy (wykladowca),
    PRIMARY KEY (student, wykladowca)
);

Jeżeli reguła „jeden wykładowca na przedmiot dla studenta” jest dla Ciebie krytyczna, możesz ją wymusić kosztem odrobiny kontrolowanej redundancji. Dodajesz przedmiot do tabeli zapisy, ograniczenie UNIQUE (student, przedmiot) oraz złożony klucz obcy, który nie pozwoli wpisać przedmiotu niezgodnego z wykładowcą:

CREATE TABLE prowadzacy (
    wykladowca  VARCHAR(100) PRIMARY KEY,
    przedmiot   VARCHAR(100) NOT NULL,
    UNIQUE (wykladowca, przedmiot)
);

CREATE TABLE zapisy (
    student     VARCHAR(100) NOT NULL,
    wykladowca  VARCHAR(100) NOT NULL,
    przedmiot   VARCHAR(100) NOT NULL,
    PRIMARY KEY (student, wykladowca),
    UNIQUE (student, przedmiot),
    FOREIGN KEY (wykladowca, przedmiot)
        REFERENCES prowadzacy (wykladowca, przedmiot)
);

Formalnie tabela zapisy znów nie jest w BCNF, bo zawiera zależność wykladowca → przedmiot. W praktyce redundancja jest jednak pod kontrolą: klucz obcy odrzuci każdy niespójny wiersz, a UNIQUE (student, przedmiot) pilnuje reguły nr 1. To typowy przykład, w którym świadomie wybierasz integralność danych zamiast teoretycznej czystości. W prawdziwej bazie zamiast nazwisk użyjesz oczywiście identyfikatorów liczbowych, ale zasada pozostaje ta sama.

Jak szybko sprawdzić, czy tabela jest w BCNF

Przy projektowaniu nie musisz za każdym razem przechodzić pełnej teorii. Wystarczy taki test:

1. Wypisz wszystkie zależności funkcyjne wynikające z reguł biznesowych (nie z przykładowych danych).
2. Dla każdej nietrywialnej zależności X → Y policz X⁺.
3. Jeśli dla każdej z nich X⁺ obejmuje wszystkie kolumny tabeli, tabela jest w BCNF.

Przydają się też dwa skróty:

• Każda relacja z dokładnie dwiema kolumnami jest w BCNF.
• Tabela w 3NF, która ma jeden klucz kandydujący (albo kilka, ale bez wspólnych atrybutów), jest automatycznie w BCNF.

W typowej aplikacji z kluczami zastępczymi (id SERIAL, AUTO_INCREMENT, IDENTITY) BCNF łamie się najczęściej wtedy, gdy obok sztucznego klucza istnieje naturalny klucz złożony, o którym nikt nie pomyślał, np. kombinacja {sala, termin} w systemie rezerwacji. Dlatego warto definiować ograniczenia UNIQUE także dla kluczy naturalnych, a nie tylko klucz główny na kolumnie id.

Kiedy BCNF ma sens, a kiedy wystarczy 3NF

BCNF warto stosować domyślnie w bazach transakcyjnych (OLTP), gdzie dane są często modyfikowane i każda anomalia aktualizacji oznacza realne błędy. W zdecydowanej większości tabel nie wymaga to żadnej dodatkowej pracy, bo 3NF i BCNF są tam równoważne.

Zatrzymanie się na 3NF jest uzasadnione, gdy dekompozycja do BCNF zgubiłaby zależność, którą musisz egzekwować w bazie, a nie chcesz przenosić jej do kodu aplikacji ani wyzwalaczy. Z kolei w hurtowniach danych i raportach (OLAP) często idzie się w przeciwną stronę i świadomie denormalizuje schemat, np. do modelu gwiazdy, bo liczy się szybkość odczytu, a dane rzadko się zmieniają.

Wybór silnika ma tu drugorzędne znaczenie: zasady normalizacji są takie same w PostgreSQL, MySQL, SQL Server czy Oracle. Różnią się możliwości wymuszania reguł (np. ograniczenia CHECK, wyzwalacze, ograniczenia odroczone), o czym więcej w przeglądzie najpopularniejszych systemów baz danych. Więcej materiałów o projektowaniu baz i SQL znajdziesz w kategorii Programowanie.