Normalizacja w SQL: 1NF, 2NF, 3NF i BCNF

Normalizacja to systematyczny proces zapewniający, że model relacyjnej bazy danych jest wydajny, odpowiedni do wykonywania zapytań ogólnego przeznaczenia i wolny od niepożądanych cech, takich jak anomalie wstawiania, aktualizowania i usuwania, co prowadzi do utraty integralności danych. Ten proces normalizacji pomaga również wyeliminować nadmiarowość danych i zmniejsza ryzyko niespójności po każdej operacji wstawiania, aktualizacji lub usuwania.

Dla lepszego zrozumienia rozważ następujący schemat: Student (imię i nazwisko, adres, przedmiot, klasa)

W tym schemacie jest kilka problemów lub nieefektywności.

1) Redundancja : Adres studenta jest powtarzany dla każdego przedmiotu, na który jest zarejestrowany.

2) Aktualizowanie anomalii : Mogliśmy zaktualizować adres w jednej krotce (wierszu), pozostawiając go niezmienionym w pozostałych wierszach. W ten sposób nie mielibyśmy spójnie unikalnego adresu dla każdego ucznia.

3) Anomalia wstawiania : Nie zarejestrujemy adresu ucznia bez zarejestrowania się na co najmniej jeden przedmiot. Podobnie, gdy uczeń chce zapisać się na nowy przedmiot, możliwe jest wstawienie innego adresu.

4) Anomalia usunięcia : Jeśli uczeń zdecyduje się przerwać wszystkie zapisane przedmioty, adres ucznia również zostanie utracony w procesie usuwania.

Dlatego ważne jest, aby reprezentować dane użytkownika za pomocą relacji, które nie tworzą anomalii po operacjach dodawania, usuwania lub aktualizowania krotek. Można to osiągnąć jedynie poprzez dokładną analizę ograniczeń integralności, zwłaszcza zależności danych w bazie danych.

Relacje powinny być zaprojektowane tak, aby grupować tylko te atrybuty, które naturalnie występują razem. Można to zrobić głównie poprzez podstawowe zrozumienie znaczenia wszystkich atrybutów danych. Jednak nadal potrzebujemy pewnych formalnych środków, aby zapewnić nasz cel projektowy.

Normalizacja jest tym środkiem formalnym. Odpowiada na pytanie, dlaczego dana grupa atrybutów będzie lepsza niż jakakolwiek inna.

Na dzień dzisiejszy istnieje siedem normalnych form:

• Pierwsza forma normalna (1NF)
• Druga forma normalna (2NF)
• Trzecia forma normalna (3NF)
• Postać normalna Boyce-Codda (BCNF)
• Czwarta forma normalna (4NF)
• Piąta forma normalna (5NF)
• Szósty lub normalny formularz klucza domeny (6NF)

Przeczytaj: Rodzaje widoków w SQL

Pierwsza forma normalna (1NF lub forma minimalna)

• Nie ma kolejności od góry do dołu w wierszach i od lewej do prawej w kolumnach.
• Brak zduplikowanych wierszy.
• Każde przecięcie wiersza i kolumny zawiera dokładnie jedną wartość z odpowiedniej domeny lub wartość null. Ten warunek wskazuje, że wszystkie wartości kolumn powinny być niepodzielne, skalarne lub zawierać tylko jedną wartość. W tym miejscu nie jest dozwolone powtarzanie informacji ani wartości w wielu kolumnach.
• Wszystkie kolumny są regularne (tzn. wiersze nie mają ukrytych elementów, takich jak identyfikatory wierszy, identyfikatory obiektów lub ukryte znaczniki czasu).

Weźmy przykład schematu, który nie jest znormalizowany. Załóżmy, że projektant chce rejestrować nazwiska i numery telefonów klientów. Definiują tabelę klientów, jak pokazano:

Tutaj nie ma go w 1 NF. Kolumna Numery telefonów nie jest niepodzielna lub nie ma wartości skalarnej, tj. ma więcej niż jedną wartość, co nie może być dozwolone w 1 NF.

Aby to zrobić 1 NF

• Najpierw podzielimy (dekomponujemy) nasz pojedynczy stół na dwa.
• Każda tabela powinna zawierać informacje tylko o jednej jednostce.

W tym projekcie nie występują powtarzające się grupy numerów telefonów. Zamiast tego każde łącze Klient-numer telefonu pojawia się we własnym rekordzie.

Kasa: najpopularniejsze pytania i odpowiedzi dotyczące wywiadu SQL

Druga forma normalna

Każda forma normalna ma więcej ograniczających kryteriów niż jej poprzedniczka. Tak więc każda tabela, która ma drugą postać normalną (2NF) lub wyższą, jest z definicji również w 1NF. Z drugiej strony stół, który jest w 1NF, może, ale nie musi, znajdować się w 2NF; jeśli jest w 2NF, może, ale nie musi, być w 3NF i tak dalej.

Mówi się, że tabela 1NF znajduje się w 2NF wtedy i tylko wtedy, gdy żaden z jej atrybutów innych niż pierwsze nie jest funkcjonalnie zależny od części (właściwego podzbioru) klucza kandydującego. (Atrybut inny niż podstawowy nie należy do żadnego klucza kandydującego).

Należy zauważyć, że gdy tabela 1NF nie ma złożonych kluczy kandydujących (klucze kandydujące składające się z więcej niż jednego atrybutu), tabela automatycznie jest w 2NF.

Sprawdź, czy relacja R (A, B, C, D, E) z FD Ustaw jako { BC ? D, AC? BYĆ, B? E } jest w 2NF?

• Jak widać, domknięcie AC to (AC)+ = {A, C, B, E, D} przy zastosowaniu algorytmu przynależności. Ale żaden z jego podzbiorów nie może sam określić wszystkich atrybutów relacji, więc AC jest kluczem kandydującym dla tej relacji. Co więcej, ani A ani C nie mogą być wyprowadzone z żadnego innego atrybutu relacji, więc będzie tylko jeden klucz kandydujący, którym jest {AC}.
• Tutaj {A, C} to atrybuty pierwsze, a {B, D, E} to atrybuty inne niż pierwsze.
• Relacja R jest już w pierwszej postaci normalnej, ponieważ relacyjny DBMS w 1NF nie zezwala na atrybuty wielowartościowe lub złożone.

PNE ? D jest w drugiej postaci normalnej, ponieważ BC nie jest właściwym podzbiorem klucza kandydującego AC,

AC ? BE ma drugą postać normalną, ponieważ sam AC jest kluczem kandydującym, a

B ? E jest w drugiej postaci normalnej B nie jest właściwym podzbiorem klucza kandydującego AC.

Zatem dana relacja R jest w 2. Postaci Normalnej.

Trzecia forma normalna

Mówi się, że tabela jest w 3NF wtedy i tylko wtedy, gdy dla każdej z jej zależności funkcjonalnych.

X → A spełniony jest co najmniej jeden z następujących warunków:

• X zawiera A (czyli X → A jest trywialną zależnością funkcjonalną), lub
• X to super klucz, lub
• A jest atrybutem głównym (tj. A jest obecne w kluczu kandydującym)

Inna definicja 3NF stwierdza, że ​​każdy atrybut niekluczowy w R jest nieprzechodnie zależny (tj. bezpośrednio zależny) od klucza podstawowego w R. Oznacza to, że żaden atrybut inny niż główny (nie będący częścią klucza kandydującego) nie jest funkcjonalnie zależny od innych atrybutów niepierwotnych. Jeśli istnieją dwie zależności takie, że A ? B i BC, to z tych FD możemy wyprowadzić A ? C. Ta zależność AC jest przechodnia.

Przykład 3NF:

Rozważ następującą relację Order (Nr zamówienia, Część, Dostawca, CenaJednostkowa, IlośćZamówiona) z podanym zestawem FD:

Zamówienie# ? Część, dostawca, zamówiona ilość i dostawca, część? Cena jednostkowa)

Tutaj Order# jest kluczem do relacji.

Używając aksjomatów Amstronga, otrzymujemy

Zamówienie# ? Część, Zamówienie ? Dostawca i Zamówienie? IlośćZamówiona.

Zamówienie# ? Część, dostawca i dostawca, część ? Cena jednostkowa, oba podają numer zamówienia ? Cena jednostkowa.

Widzimy więc, że wszystkie atrybuty inne niż pierwsze zależą od klucza (Order#). Istnieje jednak przechodnia zależność między numerem zamówienia a ceną jednostkową. Więc tej relacji nie ma w 3NF. Jak to zrobić w 3NF?

Nie możemy przechowywać Ceny Jednostkowej jakiejkolwiek Części dostarczonej przez dowolnego Dostawcę, chyba że ktoś złoży zamówienie na tę Część. Więc będziemy musieli rozłożyć tabelę, aby była zgodna z 3NF w następujący sposób.

Zamówienie (nr zamówienia, część, dostawca, zamówiona ilość) i wzorzec ceny (część, dostawca, cena jednostkowa).

Teraz nie ma żadnych przechodnich zależności. Relacja jest w 3NF.

Przeczytaj także: SQL dla nauki o danych

Ucz się kursów oprogramowania online z najlepszych światowych uniwersytetów. Zdobywaj programy Executive PG, Advanced Certificate Programs lub Masters Programs, aby przyspieszyć swoją karierę.

Wniosek

Normalizacja to coś więcej, jak BCNF, 4NF, 5NF i 6NF. Krótko mówiąc, BCNF to nic innego jak rozszerzenie 3NF, ponieważ ostatnia zasada 3NF nie ma tutaj zastosowania. Wszystkie zależności funkcjonalne muszą mieć kluczowe atrybuty po lewej stronie, a żadne po prawej stronie. (BCNF jest również nazywany 3.5NF). Jednak normalne formy z 4NF i dalej są rzadko wdrażane w regularnej praktyce.

Jeśli chcesz dowiedzieć się więcej o tworzeniu pełnego stosu, sprawdź program Executive PG UpGrad i IIIT-B w zakresie tworzenia pełnego stosu oprogramowania, który jest przeznaczony dla pracujących profesjonalistów i oferuje ponad 500 godzin rygorystycznych szkoleń, ponad 9 projektów, i zadania, status absolwentów IIIT-B, praktyczne praktyczne projekty zwieńczenia i pomoc w pracy z najlepszymi firmami.