Normalizacja bazy danych - Database normalization

Normalizacja bazy danych to proces strukturyzacji bazy danych , zwykle relacyjnej bazy danych , zgodnie z szeregiem tzw. normalnych form w celu zmniejszenia nadmiarowości danych i poprawy integralności danych . Po raz pierwszy został zaproponowany przez Edgara F. Codda jako część jego modelu relacyjnego .

Normalizacja polega na zorganizowaniu kolumn (atrybutów) i tabel (relacji) bazy danych, aby zapewnić, że ich zależności są odpowiednio wymuszane przez ograniczenia integralności bazy danych. Jest to osiągane przez zastosowanie pewnych reguł formalnych w procesie syntezy (tworzenie nowego projektu bazy danych) lub dekompozycji (ulepszenie istniejącego projektu bazy danych).

Cele

Podstawowym celem pierwszej postaci normalnej zdefiniowanej przez Codda w 1970 roku było umożliwienie odpytywania danych i manipulowania nimi przy użyciu „uniwersalnego podjęzyka danych” opartego na logice pierwszego rzędu . ( SQL jest przykładem takiego podjęzyka danych, aczkolwiek takiego, który Codd uznał za poważnie wadliwy.)

Cele normalizacji poza 1NF (pierwsza postać normalna) zostały określone przez Codda:

1. Aby uwolnić kolekcję relacji od niepożądanych zależności wstawiania, aktualizacji i usuwania.
2. Aby zmniejszyć potrzebę restrukturyzacji kolekcji relacji, w miarę wprowadzania nowych typów danych, a tym samym zwiększyć żywotność programów użytkowych.
3. Aby model relacyjny był bardziej informacyjny dla użytkowników.
4. Aby zbieranie relacji było neutralne dla statystyk zapytań, które mogą się zmieniać w miarę upływu czasu.

—  EF Codd, „Dalsza normalizacja relacyjnego modelu bazy danych”

Anomalia aktualizacji . Pracownik 519 jest pokazany jako mający różne adresy w różnych rekordach.

Wstawiania anomalii . Dopóki nowy członek wydziału, dr Newsome, nie zostanie przydzielony do prowadzenia co najmniej jednego kursu, jego szczegóły nie mogą być rejestrowane.

Usunięcie nieprawidłowości . Wszystkie informacje o dr Giddensie zostaną utracone, jeśli tymczasowo przestaną być przypisywane do jakichkolwiek kursów.

Podczas próby zmodyfikowania (aktualizacji, wstawienia lub usunięcia) relacji, w relacjach, które nie zostały wystarczająco znormalizowane, mogą pojawić się następujące niepożądane efekty uboczne:

• Zaktualizuj anomalię. Te same informacje mogą być wyrażone w wielu wierszach; dlatego aktualizacje relacji mogą powodować logiczne niespójności. Na przykład każdy rekord w relacji „Umiejętności pracowników” może zawierać identyfikator pracownika, adres pracownika i umiejętność; w związku z tym zmiana adresu konkretnego pracownika może wymagać zastosowania do wielu rekordów (po jednym dla każdej umiejętności). Jeżeli aktualizacja zakończy się sukcesem tylko częściowo – adres pracownika jest aktualizowany na niektórych rekordach, ale nie na innych – to relacja pozostaje w stanie niespójnym. W szczególności relacja dostarcza sprzecznych odpowiedzi na pytanie, jaki jest adres tego konkretnego pracownika. Zjawisko to jest znane jako anomalia aktualizacji.
• Anomalia wstawiania. Istnieją okoliczności, w których pewnych faktów w ogóle nie można zarejestrować. Na przykład każdy rekord w relacji „Wydział i ich kursy” może zawierać identyfikator wydziału, nazwę wydziału, datę zatrudnienia wydziału i kod kursu. W związku z tym można rejestrować dane każdego członka wydziału, który prowadzi co najmniej jeden kurs, ale nowo zatrudniony członek wydziału, który nie został jeszcze przydzielony do nauczania żadnych kursów, nie może być rejestrowany, z wyjątkiem ustawienia Kodu kursu na null . Zjawisko to jest znane jako anomalia wstawiania.
• Anomalia usunięcia. W pewnych okolicznościach usunięcie danych reprezentujących pewne fakty wiąże się z koniecznością usunięcia danych reprezentujących zupełnie inne fakty. Relacja „Wydział i ich kursy” opisana w poprzednim przykładzie cierpi z powodu tego typu anomalii, ponieważ jeśli członek wydziału tymczasowo przestaje być przypisywany do jakichkolwiek kursów, ostatni z rekordów, na których ten członek wydziału się pojawia, musi zostać skutecznie usunięty. także usunięcie członka wydziału, chyba że pole Kod kursu jest ustawione na null. Zjawisko to jest znane jako anomalia delecji.

Zminimalizuj przeprojektowanie przy rozszerzaniu struktury bazy danych

W pełni znormalizowana baza danych umożliwia rozszerzenie jej struktury w celu dostosowania do nowych typów danych bez zbytniego zmieniania istniejącej struktury. W rezultacie wpływ na aplikacje współpracujące z bazą danych jest minimalny.

Relacje znormalizowane i relacje między jedną znormalizowaną relacją a drugą odzwierciedlają koncepcje świata rzeczywistego i ich wzajemne powiązania.

Formy normalne

Codd wprowadził pojęcie normalizacji i to, co jest obecnie znane jako pierwsza forma normalna (1NF) w 1970 roku. Codd zdefiniował drugą formę normalną (2NF) i trzecią formę normalną (3NF) w 1971 roku, a Codd i Raymond F. Boyce zdefiniował postać normalną Boyce-Codda (BCNF) w 1974 roku.

Nieformalnie relacja relacyjnej bazy danych jest często opisywana jako „znormalizowana”, jeśli spełnia trzecią formę normalną. Większość relacji 3NF jest wolna od anomalii wstawiania, aktualizowania i usuwania.

Formy normalne (od najmniej znormalizowanej do najbardziej znormalizowanej) to:

	UNF (1970)	1PN (1970)	2PN (1971)	3PN (1971)	EKNF (1982)	BCNF (1974)	4NF (1977)	ETNF (2012)	5NF (1979)	DKNF (1981)	6NF (2003)
Klucz podstawowy (bez duplikatów krotek )
Kolumny atomowe (komórki nie mogą mieć tabel jako wartości)
Każda nietrywialna zależność funkcjonalna albo nie zaczyna się odpowiednim podzbiorem klucza kandydującego, albo kończy się atrybutem podstawowym (brak częściowych zależności funkcjonalnych atrybutów innych niż podstawowe od kluczy kandydujących)
Każda nietrywialna zależność funkcjonalna albo zaczyna się od superklucza, albo kończy się atrybutem podstawowym (brak przechodnich zależności funkcjonalnych atrybutów innych niż podstawowe od kluczy kandydujących)
Każda nietrywialna zależność funkcjonalna albo zaczyna się od superklucza, albo kończy na elementarnym atrybucie pierwszym											Nie dotyczy
Każda nietrywialna zależność funkcjonalna zaczyna się od superklucza											Nie dotyczy
Każda nietrywialna zależność wielowartościowa zaczyna się od superklucza											Nie dotyczy
Każda zależność łączenia ma komponent superklucza											Nie dotyczy
Każda zależność łączenia ma tylko komponenty superklucza											Nie dotyczy
Każde ograniczenie jest konsekwencją ograniczeń domeny i kluczowych ograniczeń
Każda zależność przyłączenia jest trywialna

Przykład normalizacji krok po kroku

Normalizacja to technika projektowania bazy danych, która służy do projektowania tabeli relacyjnej bazy danych do wyższej postaci normalnej. Proces jest progresywny i nie można osiągnąć wyższego poziomu normalizacji bazy danych, jeśli nie zostały spełnione poprzednie poziomy.

Oznacza to, że mając dane w postaci nieznormalizowanej (najmniej znormalizowanej) i dążąc do osiągnięcia najwyższego poziomu normalizacji, pierwszym krokiem byłoby zapewnienie zgodności z pierwszą postacią normalną , drugim krokiem byłoby zapewnienie spełnienia drugiej postaci normalnej , i tak dalej w kolejności wymienionej powyżej, aż dane będą zgodne z szóstą postacią normalną .

Warto jednak zauważyć, że formy normalne poza 4NF są głównie przedmiotem zainteresowania akademickiego, ponieważ problemy, które mają do rozwiązania, rzadko pojawiają się w praktyce.

Należy pamiętać, że dane w poniższym przykładzie zostały celowo zaprojektowane tak, aby były sprzeczne z większością normalnych formularzy. W prawdziwym życiu całkiem możliwe jest pominięcie niektórych kroków normalizacji, ponieważ tabela nie zawiera niczego, co byłoby sprzeczne z daną formą normalną. Często zdarza się również, że naprawienie naruszenia jednej formy normalnej naprawia również naruszenie wyższej formy normalnej w procesie. Również jedna tabela została wybrana do normalizacji na każdym kroku, co oznacza, że ​​na końcu tego przykładowego procesu mogą nadal istnieć tabele nie spełniające najwyższej normalnej postaci.

Wstępne dane

Niech istnieje tabela bazy danych o następującej strukturze:

W tym przykładzie zakłada się, że każda książka ma tylko jednego autora.

Warunkiem zgodności z modelem relacyjnym jest to, że tabela musi mieć klucz podstawowy , który jednoznacznie identyfikuje wiersz. Dwie książki mogą mieć ten sam tytuł, ale numer ISBN jednoznacznie identyfikuje książkę, więc może być używany jako klucz podstawowy:

Zadowalająca 1NF

Aby spełnić First normal form , każda kolumna tabeli musi mieć jedną wartość. Kolumny zawierające zestawy wartości lub rekordy zagnieżdżone są niedozwolone.

W początkowej tabeli Temat zawiera zestaw wartości podmiotu, co oznacza, że ​​nie jest zgodny.

Aby rozwiązać problem, tematy są wyodrębniane do osobnej tabeli Temat :

Do tabeli Temat dodawana jest kolumna klucza obcego , która odnosi się do klucza podstawowego wiersza, z którego wyodrębniono temat. W związku z tym reprezentowane są te same informacje, ale bez użycia nieprostych domen.

Zamiast jednej tabeli w nieznormalizowanej formie są teraz dwie tabele zgodne z 1NF.

Zadowalająca 2NF

Book tabela ma jeden klucz potencjalny (który jest zatem klucz podstawowy ), ten klucz kompozytowego {Title, Format} . Rozważ następujący fragment tabeli:

Wszystkie atrybuty, które nie są częścią klucza kandydującego, zależą od tytułu , ale tylko cena zależy również od formatu . Aby zapewnić zgodność z 2NF i usunąć duplikaty, każdy atrybut klucza niebędącego kandydatem musi zależeć od całego klucza kandydującego, a nie tylko jego części.

Aby znormalizować tę tabelę, ustaw {Title} jako (prosty) klucz kandydujący (klucz podstawowy), tak aby każdy atrybut klucza niebędącego kandydatem zależał od całego klucza kandydującego, i usuń cenę do osobnej tabeli, aby jego zależność od formatu mogła być zachowane:

Teraz tabela Książka jest zgodna z 2NF .

Zadowalająca 3NF

Zarezerwuj stolik nadal ma przechodnia zależność funkcjonalna ({Autor Narodowość} zależy od {} Autor, który jest zależny od {title}). Podobne naruszenie występuje w przypadku gatunku ({Nazwa gatunku} jest zależny od {Identyfikatora gatunku}, który jest zależny od {Tytułu}). Stąd tabela Księga nie znajduje się w 3NF. Aby zrobić to w 3NF, użyjmy następującej struktury tabeli, eliminując w ten sposób przechodnie zależności funkcjonalne, umieszczając {Narodowość autora} i {Nazwa gatunku} w ich własnych odpowiednich tabelach:

Zadowalający EKNF

Podstawowa kluczowa postać normalna (EKNF) mieści się ściśle pomiędzy 3NF a BCNF i nie jest zbytnio omawiana w literaturze. Ma on na celu „uchwycenie istotnych cech zarówno 3NF, jak i BCNF”, przy jednoczesnym uniknięciu problemów obu (mianowicie, że 3NF jest „zbyt wyrozumiały”, a BCNF jest „podatny na złożoność obliczeniową”). Ponieważ jest rzadko wspominany w literaturze, nie został uwzględniony w tym przykładzie.

Zadowalająca 4NF

Załóżmy, że baza danych należy do franczyzy księgarni, która ma kilku franczyzobiorców posiadających sklepy w różnych lokalizacjach. Dlatego sprzedawca zdecydował się dodać tabelę zawierającą dane o dostępności książek w różnych lokalizacjach:

Ponieważ ta struktura tabeli składa się ze złożonego klucza podstawowego , nie zawiera żadnych atrybutów niekluczowych i znajduje się już w BCNF (a zatem spełnia również wszystkie poprzednie formy normalne ). Jednak zakładając, że wszystkie dostępne książki są dostępne w każdej dziedzinie, tytuł nie jest jednoznacznie związana z pewnym Położenie i dlatego tabela nie spełniają 4nF .

Oznacza to, że aby spełnić czwartą postać normalną , ta tabela również musi zostać rozłożona:

Teraz każdy rekord jest jednoznacznie identyfikowany przez nadkluczem więc 4NF jest spełniony.

Zadowalający ETNF

Załóżmy, że franczyzobiorcy mogą również zamawiać książki od różnych dostawców. Niech relacja podlega również następującemu ograniczeniu:

• Jeśli określony dostawca dostarcza określony tytuł
• a tytuł jest dostarczany franczyzobiorcy
• a franczyzobiorca jest zaopatrywany przez dostawcę,
• wówczas dostawca zaopatruje tytuł do franczyzobiorcy .

Ta tabela jest w 4NF , ale identyfikator dostawcy jest równy sprzężeniu jej projekcji: {{ID dostawcy, książka}, {książka, identyfikator franczyzobiorcy}, {identyfikator franczyzobiorcy, identyfikator dostawcy}}. Żaden składnik tej zależności łączenia nie jest superkluczem (jedynym superkluczem jest cały nagłówek), więc tabela nie spełnia wymagań ETNF i może być dalej rozłożona:

Rozkład zapewnia zgodność z ETNF.

Zadowalająca 5NF

Aby wykryć tabelę, która nie spełnia 5NF , zwykle konieczne jest dokładne zbadanie danych. Załóżmy tabelę z przykładu 4NF z niewielką modyfikacją danych i sprawdźmy, czy spełnia ona wymagania 5NF :

Rozkładanie tej tabeli zmniejsza nadmiarowość, czego wynikiem są następujące dwie tabele:

Zapytanie łączące te tabele zwróciłoby następujące dane:

JOIN zwraca trzy wiersze więcej niż powinno; dodanie kolejnej tabeli w celu wyjaśnienia relacji powoduje powstanie trzech oddzielnych tabel:

Co teraz zwróci JOIN? Właściwie nie można dołączyć do tych trzech stołów. Oznacza to, że nie można było rozłożyć lokalizacji książki franczyzobiorcy bez utraty danych, dlatego tabela już spełnia wymagania 5NF .

CJ Date twierdzi, że tylko baza danych w 5NF jest naprawdę „znormalizowana”.

Zadowalający DKNF

Przyjrzyjmy się tabeli Książka z poprzednich przykładów i zobaczmy, czy jest ona zgodna z normalną formą klucza domeny :

Logicznie rzecz biorąc, grubość zależy od liczby stron. Oznacza to, że zależy to od stron, które nie są kluczem. Ustawmy przykładową konwencję mówiącą, że książka do 350 stron jest uważana za „wąską”, a książkę powyżej 350 stron uważa się za „grubą”.

Ta konwencja jest technicznie ograniczeniem, ale nie jest ani ograniczeniem domeny, ani ograniczeniem kluczowym; dlatego nie możemy polegać na ograniczeniach domeny i kluczowych ograniczeniach, aby zachować integralność danych.

Innymi słowy — nic nie stoi na przeszkodzie, aby wstawić np. „Gruby” dla książki mającej tylko 50 stron — a to sprawia, że ​​tabela narusza DKNF .

Aby rozwiązać ten problem, tworzona jest tabela zawierająca wyliczenie, które definiuje Grubość , a ta kolumna jest usuwana z oryginalnej tabeli:

W ten sposób naruszenie integralności domeny zostało wyeliminowane, a tabela znajduje się w DKNF .

Zadowalająca 6NF

Prostą i intuicyjną definicją szóstej postaci normalnej jest to, że „tabela jest w 6NF, gdy wiersz zawiera klucz główny i co najwyżej jeden inny atrybut” .

Oznacza to na przykład tabelę Publishera zaprojektowaną podczas tworzenia 1NF

należy dodatkowo rozłożyć na dwie tabele:

Oczywistą wadą 6NF jest mnogość tabel wymaganych do reprezentowania informacji o jednym podmiocie. Jeśli tabela w 5NF ma jedną kolumnę klucza podstawowego i N atrybutów, reprezentowanie tych samych informacji w 6NF będzie wymagało N tabel; aktualizacje wielopolowe do jednego rekordu koncepcyjnego będą wymagały aktualizacji wielu tabel; a operacje wstawiania i usuwania będą podobnie wymagały operacji w wielu tabelach. Z tego powodu w bazach danych przeznaczonych do obsługi potrzeb przetwarzania transakcji online nie należy stosować 6NF.

Jednak w hurtowniach danych , które nie pozwalają na interaktywne aktualizacje i które są wyspecjalizowane do szybkich zapytań dotyczących dużych ilości danych, niektóre DBMS używają wewnętrznej reprezentacji 6NF — znanej jako kolumnowy magazyn danych . W sytuacjach, gdy liczba unikalnych wartości w kolumnie jest znacznie mniejsza niż liczba wierszy w tabeli, przechowywanie zorientowane na kolumny pozwala na znaczne oszczędności miejsca dzięki kompresji danych. Przechowywanie kolumnowe umożliwia również szybkie wykonywanie zapytań o zakres (np. pokaż wszystkie rekordy, w których dana kolumna znajduje się między X a Y lub mniej niż X).

Jednak we wszystkich tych przypadkach projektant bazy danych nie musi ręcznie wykonywać normalizacji 6NF, tworząc oddzielne tabele. Niektóre systemy DBMS, które specjalizują się w magazynowaniu, takie jak Sybase IQ , domyślnie używają pamięci kolumnowej, ale projektant nadal widzi tylko jedną tabelę wielokolumnową. Inne DBMS, takie jak Microsoft SQL Server 2012 i nowsze, pozwalają określić „indeks magazynu kolumn” dla określonej tabeli.

Zobacz też

• Denormalizacja
• Refaktoryzacja bazy danych

• Bezstratna dekompozycja złączenia

Uwagi i referencje

Dalsza lektura

• Data, CJ (1999), Wprowadzenie do systemów baz danych (wyd. 8). Addisona-Wesleya Longmana. ISBN  0-321-19784-4 .
• Kent, W. (1983) A Simple Guide to Five Normal Forms in Relational Database Theory , Communications of the ACM, tom. 26, s. 120–125
• H.-J. Schek, P. Pistor Struktury danych dla zintegrowanego systemu zarządzania bazą danych i wyszukiwania informacji

Zewnętrzne linki

• Kent, William (luty 1983). „Prosty przewodnik po pięciu formach normalnych w teorii relacyjnych baz danych” . Komunikaty ACM . 26 (2): 120-125. doi : 10.1145/358024.358054 . S2CID  9195704 .
• Podstawy normalizacji baz danych autorstwa Mike'a Chapple'a (About.com)
• Wprowadzenie do normalizacji baz danych , część 2
• Wprowadzenie do normalizacji baz danych autorstwa Mike'a Hillyera.
• Samouczek dotyczący pierwszych 3 normalnych form autorstwa Freda Coulsona
• Opis podstaw normalizacji baz danych firmy Microsoft
• Normalizacja w DBMS przez Chaitanya (beginnersbook.com)
• Przewodnik krok po kroku dotyczący normalizacji baz danych
• ETNF – zasadnicza krotka postać normalna