Relacyjne Bazy Danych
Europejska Wyższa Szkoła Informatyczno-
Ekonomiczna
Mgr inż. Piotr Greniewski
Wykład 9: - Normalizacja
Copyright by Piotr Greniewski
2
Spis treści
Bazy danych wstęp
Geneza relacyjnych baz danych
Podstawy relacyjnych baz danych
Podstawy-SQL-1
Podstawy-SQL-2
Podstawy-SQL-3
Operacje na danych
Transakcje i blokady
Normalizacja
Copyright by Piotr Greniewski
3
Powtórka z terminologii
Relacja
– jest podzbiorem iloczynu
kartezjańskiego reprezentowanego przez zbiór
krotek. Reprezentacją relacji w relacyjnych
bazach danych jest tablica.
Krotka
– oznacza wiersz w tablicy.
Atrybut
– oznacza kolumnę tablicy (dokładnie
różne wystąpienia tego samego atrybutu)
Stopień relacji
– liczba atrybutów w relacji.
Liczność relacji
– liczba krotek w relacji
Copyright by Piotr Greniewski
4
Powtórka z terminologii
Klucz główny (PK)
– kolumna lub kombinacja
kolumn, których wartości jednoznacznie
identyfikują wiersze w tablicy.
Klucz obcy (FK)
– kolumna lub kombinacja
kolumn, których wartości określają klucz
główny innej tablicy.
Dziedzina (atrybutu)
– lista dostępnych
wartości atrybutu.
Copyright by Piotr Greniewski
5
Powtórka z terminologii
W relacyjnych bazach danych powiązania
pomiędzy krotkami w tabeli i pomiędzy
tabelami są zrealizowane za pomocą kluczy
głównych i kluczy obcych.
Istnieją cztery rodzaje kluczy:
Kandydaci na klucze
Klucze alternatywne
Klucze obce
Sztuczne klucze
Copyright by Piotr Greniewski
6
Powtórka z terminologii
Klucze kandydujące
– jest to dowolna kolumna
lub kombinacja kolumn, których zawartość
może być użyta dla identyfikacji wierszy w
tablicy
Kluczami kandydującymi na PK są id_pracownika, pesel
i nazwisko.
Wybieramy id_pracownika na PK.
Pesel i nazwisko są kluczami alternatywnymi
kod_wydziału jest FK
pracownicy
id_pracownika
kod_wydzialu (FK)
pesel
nazwisko
adres
wydzial
kod_wydzialu
nazwa_wydzialu
Copyright by Piotr Greniewski
7
Powtórka z terminologii
Jeśli klucze kandydujące istnieją, projektant
wybiera jeden z nich na klucz główny. Powinien
on spełniać dodatkowe warunki:
unikalność
być minimalnym (tzn. bez nadmiarowych pól)
Klucz alternatywny
– może być używany jako
alternatywny dostęp do danych.
Klucz obcy
– jest kolumną i kombinacją kolumn,
którego wartości są kluczami głównymi innej
tablicy.
Oba klucze FK i PK powinny mieć tę samą
dziedzinę.
Copyright by Piotr Greniewski
8
Powtórka z terminologii
Sztuczne klucze główne
– jeżeli w tabeli nie
istnieje naturalny klucz główny to należy
utworzyć sztuczny.
Zalety stosowania sztucznych kluczy:
klucze sztuczne prowadzą do prostych rozwiązań w
przypadku nie istnienia kluczy naturalnych
złożony klucz główny, występujący jako klucz obcy w
innej tabeli wprowadza redundancję pól, której
unikniemy wprowadzając klucz obcy.
operacje wyszukiwania SELECT będzie krótsza w
przypadku klucza sztucznego.
Copyright by Piotr Greniewski
9
Zależności atrybutów
Normalizacja
jest oparta na idei funkcjonalnej
zależności niektórych atrybutów. Istnieją dwa rodzaje
zależności atrybutów, zależność
funkcjonalna
i
zależność
związana z wielowartościowością.
Zależność funkcjonalna
: w relacji zawierającej
atrybuty A i B, B jest funkcjonalnie zależny od A
wtedy i tylko wtedy, gdy za każdym razem, kiedy
atrybut A ma tę samą wartość a
i
, atrybut B musi mieć
tę samą wartość b
i
.
uwagi do definicji:
A i B mogą być złożone
jeśli B jest funkcjonalnie zależne od A to mówimy że A jest
wyznacznikiem B
wszystkie pola są funkcjonalnie zależne od klucza głównego.
Copyright by Piotr Greniewski
10
Funkcjonalna zależność atrybutów
NAZWA_WYDZ jest
funkcjonalnie zależna
od NR_WYDZ
NR_WYDZ
jest wyznacznikiem
NAZWA_WYDZ
NAZWISKO
NR_WYDZ NAZWA_WYD
Z
Kowalski
10
Badania
Bogdanowicz
20
Księgowość
Jonasz
10
Badania
Król
10
Badania
Milewski
30
Marketing
Copyright by Piotr Greniewski
11
Zależności atrybutów
Wielowartościowa zależność – w relacji
zawierającej atrybuty A i B, B jest
wielowartościowo zależne od A wtedy i tylko
wtedy, gdy jednej wartości atrybutu A
odpowiada wiele wartości atrybutu B
Copyright by Piotr Greniewski
12
Wielowartościowa zależność atrybutów
Atrybuty software i hardware są
wielowartościowo
zależne
od atrybutu nazwisko.
nazwisk
o
software
hardwar
e
Bielecki
SQL*Form
s
IBM
Bielecki
SQL*Form
s
VAX
Bielecki
SQL*QMX
IBM
Bielecki
SQL*QMX
VAX
Bielecki
SQL*Plus
IBM
Bielecki
SQL*Plus
VAX
nazwisk
o
hardwar
e
Bielecki
IBM
Bielecki
VAX
nazwisk
o
software
Bielecki
SQL*Form
s
Bielecki
SQL*QMX
Bielecki
SQL*Plus
Copyright by Piotr Greniewski
13
Związki pomiędzy tabelami (relacjami) A i B
jeden
do
zero, jeden lub wiele
jeden
do
jeden lub wiele
Tabela-A
Tabela-B
Tabela-A
Tabela-B
Copyright by Piotr Greniewski
14
Związki pomiędzy tabelami (relacjami) A i B
jeden
do
zero, lub jeden
jeden
do
jeden
Tabela-A
Tabela-B
Tabela-A
Tabela-B
Copyright by Piotr Greniewski
15
Opis normalizacji
Normalizacja
to proces organizacji danych w bazie
danych. Polega on na tworzeniu tabel i ustanawianiu
pomiędzy nimi powiązań według reguł obowiązujących
zarówno przy ochronie danych, jak i uelastycznianiu
bazy danych przez eliminowanie
powtarzających się
i
niespójnych zależności
.
Powtarzające się
dane niepotrzebnie zajmują miejsce
na dysku i są przyczyną powstawania problemów z
obsługą. Jeśli konieczna jest zmiana danych
istniejących w więcej niż jednej lokalizacji, musi być
ona przeprowadzona we wszystkich lokalizacjach w
ten sam sposób.
Implementacja zmiany adresu klienta jest o wiele
łatwiejsza, jeśli dane są przechowywane tylko w tabeli
Klienci i w żadnym innym miejscu bazy danych.
Copyright by Piotr Greniewski
16
Opis normalizacji
Co to jest „
niespójna zależność
” ? O ile
przeglądanie tabeli Klienci w poszukiwaniu
adresu konkretnego klienta można nazwać
zachowaniem intuicyjnym, to poszukiwanie w tej
tabeli pensji, której wypłaty wymaga pracownik
od klienta, nie ma żadnego sensu.
Pensja pracownika jest związana z pracownikiem
lub zależy od pracownika i dlatego powinna być
przeniesiona do tabeli Pracownicy.
Niespójne zależności mogą utrudniać dostęp do
danych, ponieważ ścieżka ich odnajdywania
może zostać utracona lub uszkodzona
Copyright by Piotr Greniewski
17
Opis normalizacji
Istnieje kilka reguł normalizacji baz danych.
Każda reguła nosi nazwę „
postać normalna
”.
Jeśli przestrzegana jest pierwsza reguła, o
postaci bazy danych mówi się, że jest „pierwszą
postacią normalną”.
Jeśli przestrzegane są pierwsze trzy reguły,
postać bazy danych przyjmuje się za „trzecią
postać normalną”.
Chociaż możliwe są inne poziomy normalizacji,
trzecia postać normalna uważana jest za
najwyższy poziom wymagany przez większość
aplikacji
Copyright by Piotr Greniewski
18
Opis normalizacji
Jak to bywa z wieloma formalnymi regułami i
specyfikacjami, rzeczywistość nie zawsze
pozwala na ich dokładne odwzorowanie.
Ogólnie, do odnalezienia niedogodności
normalizacja wymaga dodatkowych tabel i dla
niektórych osób jest to uciążliwe.
Przed podjęciem decyzji o złamaniu jednej z
pierwszych trzech reguł normalizacji należy
upewnić się, że projekt aplikacji przewiduje
występowanie problemów, takich jak
powtarzające się dane lub niespójne zależności.
Copyright by Piotr Greniewski
19
Pierwsza postać normalna - 1NF
Pierwsza postać normalna
: brak
powtarzających się grup
W poszczególnych tabelach wyeliminuj
powtarzające się grupy.
Dla każdego zestawu danych pokrewnych
utwórz oddzielną tabelę.
Dla każdego zestawu danych pokrewnych
określ klucz podstawowy.
Copyright by Piotr Greniewski
20
Pierwsza postać normalna - 1NF
Do przechowywania podobnych danych w
jednej tabeli nie należy używać wielu pól.
Na przykład rekord służący do śledzenia
pozycji inwentarzowej, która może pochodzić
z dwóch różnych źródeł, może zawierać pola
Kod sprzedawcy 1 oraz Kod sprzedawcy 2.
Co się zdarzy po dodaniu trzeciego
sprzedawcy? Dodawanie pola nie dostarcza
odpowiedzi.
Copyright by Piotr Greniewski
21
Pierwsza postać normalna - 1NF
Wymaga to modyfikacji programu i tabel oraz
nie umożliwia obsługi zmieniającej się
dynamicznie liczby sprzedawców.
Zamiast tego należy umieścić wszystkie
informacje o sprzedawcach w oddzielnej tabeli
o nazwie Sprzedawcy, a następnie połączyć
magazyn ze sprzedawcami za pomocą klucza z
numerem pozycji, albo sprzedawców z
magazynem za pomocą klucza z kodem
sprzedawcy
Copyright by Piotr Greniewski
22
Nieznormalizowana tabela opisująca studenta
Co zrobić jeśli zmienią się przedmioty?
Jak zapisać powtarzanie semestru?
itd.
index
nazwisk
o
imie
przedmio
t-1
ocena-1
przedmio
t-2
ocena-2
32567
Kowals
ki
Jan
A100
3.0
A1001
4.5
przedmio
t-3
ocena-3 przedmio
t-4
...
...
przedmio
t-n
ocena-n
A103
5.0
A1004
A10n
5.0
Copyright by Piotr Greniewski
23
Nieznormalizowana tabela opisująca studenta
SREDNIA= (SELECT ocena1+ocena2+...+ocenan)/n
studenci
index
nazwisko
imie
semestr
przedmiot1
ocena1
przedmiot2
ocena2
przedmiot3
ocena3
przedmiot4
ocena4
------------
przedmiotn
ocenan
Copyright by Piotr Greniewski
24
Pierwsza postać normalna
Tabele wpis i studenci spełniają pierwszą postać
normalną
Znikają niedogodności związane np. z wyliczaniem
średniej ocen ze studiów.
Student może być wpisany na semestr dowolną ilość
razy.
wpis
nr_wpisu
semestr
przedmiot
ocena
studenci
index
nazwisko
imie
Copyright by Piotr Greniewski
25
Pierwsza postać normalna
wpis
nr_wpisu
index (FK)
semestr
przedmiot
ocena
studenci
index
nazwisko
imie
Dla każdej tabeli musi istnieć klucz główny
(PK)
Połączenie za pomocą klucza obcego (FK)
Copyright by Piotr Greniewski
26
Tabela nieznormalizowana (zerowa postać
normalna)
nr
nazwis
ko
nr_w
nazwa_w
nr_k
kierowni
k
nr_p
projekt
data_ro
z
wynag
902
Kowals
ki
10
sprzedaż
988
Janowski
15
ocena
10-07-
96
1000
35
testowani
e
29-07-
96
1000
45
przekazan
ie
20-
08096
1500
988
Janows
ki
20
marketin
g
699
Walasik
15
ocena
15-07-
96
2000
25
analiza
14-07-
96
2500
45
przekazan
ie
20-08-
96
2000
562
Kowals
ki
10
sprzedaż
099
Filipowic
z
25
analiza
20-05-
96
1500
Copyright by Piotr Greniewski
27
Tabela nieznormalizowana (zerowa postać
normalna)
Tabela pracownicy zawiera powtarzające się grupy
wartości atrybutów (i dlatego nie jest relacją).
Rozwiązaniem jest dekompozycja (nie powodująca utraty
danych na dwie tabele pracownicy i przypisania.
Dekompozycja usunie powtarzające się grupy.
pracownicy
nr
nazwisko
nr_wydzialu
nazwa_wydzialu
nr_kierownika
nazwa_kierownika
nr_projektu
nazwa_projektu
data_rozp
wynagrodzenie
Copyright by Piotr Greniewski
28
Pierwsza postać normalna
nr
nazwis
ko
nr_w
nazwa_w
nr_k
kierowni
k
nr_p
projekt
data_ro
z
wynag
902
Kowals
ki
10
sprzedaż
988
Janowski
15
ocena
10-07-
96
1000
902
Kowals
ki
10
sprzedaż
988
Janowski
35
testowani
e
29-07-
96
1000
902
Kowals
ki
10
sprzedaż
988
Janowski
45
przekazan
ie
20-
08096
1500
988
Janows
ki
20
marketin
g
699
Walasik
15
ocena
15-07-
96
2000
988
Janows
ki
20
marketin
g
699
Walasik
25
analiza
14-07-
96
2500
988
Janows
ki
20
marketin
g
699
Walasik
45
przekazan
ie
20-08-
96
2000
562
Kowals
ki
10
sprzedaż
099
Filipowic
z
25
analiza
20-05-
96
1500
nr
nazwis
ko
nr_w
nazwa_w
nr_k
kierowni
k
902
Kowals
ki
10
sprzedaż
988
Janowski
988
Janows
ki
20
marketin
g
699
Walasik
562
Kowals
ki
10
sprzedaż
099
Filipowic
z
nr
nr_p
projekt
data_ro
z
wynag
902
15
ocena
10-07-
96
1000
902
35
testowani
e
29-07-
96
1000
902
45
przekazan
ie
20-
08096
1500
988
15
ocena
15-07-
96
2000
988
25
analiza
14-07-
96
2500
988
45
przekazan
ie
20-08-
96
2000
562
25
analiza
20-05-
96
1500
pracownicy - 1NF
przypisania - 1NF
Copyright by Piotr Greniewski
29
Pierwsza postać normalna
pracownicy
nr
nazwisko
nr_wydzialu
nazwa_wydzialu
nr_kierownika
nazwa_kierownika
nr_projektu
nazwa_projektu
data_rozp
wynagrodzenie
zerowa postać normalna
1 NF
1 NF
pracownicy
nr
nazwisko
nr_wydzialu
nazwa_wydzialu
nr_kierownika
nazwa_kierownika
przypisania
nr
nr_projektu
nazwa_projektu
data_rozp
wynagrodzenie
Copyright by Piotr Greniewski
30
Druga postać normalna – 2NF
Druga postać normalna
: zależność wszystkich
atrybutów od jednoznacznego identyfikatora.
Druga postać normalna stosuje się do kluczy
złożonych.
Utwórz oddzielne tabele dla zestawów
wartości, odnoszących się do wielu rekordów.
Ustal powiązania tabel za pomocą klucza
obcego
Copyright by Piotr Greniewski
31
Druga postać normalna – 2NF
Rekordy nie powinny zależeć od niczego innego
niż klucz podstawowy tabeli (w razie potrzeby
może to być klucz złożony).
Na następnym slajdzie kluczem głównym w
tablicy przypisania jest nr i nr_projektu.
Jednakże kolumna projekt jest funkcjonalnie
zależna od nr_projektu to znaczy od części
klucza głównego.
Rozwiązaniem jest dekompozycja, polegająca na:
usunięciu atrybutu funkcjonalnego związanego
(projekt) do osobnej tabeli (projekty)
zabranie wraz z nim wyznacznika (nr_projektu)
Copyright by Piotr Greniewski
32
Druga postać normalna – 2NF
nr
nazwis
ko
nr_w
nazwa_w
nr_k
kierowni
k
902
Kowals
ki
10
sprzedaż
988
Janowski
988
Janows
ki
20
marketin
g
699
Walasik
562
Kowals
ki
10
sprzedaż
099
Filipowic
z
nr
nr_p
projekt
data_ro
z
wynag
902
15
ocena
10-07-
96
1000
902
35
testowani
e
29-07-
96
1000
902
45
przekazan
ie
20-
08096
1500
988
15
ocena
15-07-
96
2000
988
25
analiza
14-07-
96
2500
988
45
przekazan
ie
20-08-
96
2000
562
25
analiza
20-05-
96
1500
pracownicy - 2NF
przypisania - 1NF
nr
nr_p
data_ro
z
wynag
902
15
10-07-
96
1000
902
35
29-07-
96
1000
902
45
20-
08096
1500
988
15
15-07-
96
2000
988
25
14-07-
96
2500
988
45
20-08-
96
2000
562
25
20-05-
96
1500
nr_p
projekt
15
ocena
35
testowani
e
45
przekazan
ie
25
analiza
przypisania - 2NF
projekt - 2NF
Copyright by Piotr Greniewski
33
Druga postać normalna – 2NF
pracownicy
nr
nazwisko
nr_wydzialu
nazwa_wydzialu
nr_kierownika
nazwa_kierownika
przypisania
nr
nr_projektu (FK)
data_rozp
wynagrodzenie
projekty
nr_projektu
nazwa_projektu
2 NF
2 NF
2 NF
Copyright by Piotr Greniewski
34
Trzecia postać normalna – 3NF
Trzecia postać normalna
: żaden atrybut nie
będący częścią klucza nie zależy od innego
atrybutu nie będącego częścią klucza.
Wyeliminuj pola, które nie zależą od klucza.
Wartości rekordu, które nie są częścią jego
klucza, nie należą do tabeli. Zazwyczaj, jeśli
zawartość grupy pól odnosi się do więcej niż
jednego rekordu tabeli, należy rozważyć
umieszczenie tych pól w oddzielnej tabeli.
Copyright by Piotr Greniewski
35
Trzecia postać normalna – 3NF
W tablicy pracownicy nr_wydziału jest
funkcjonalnie zależny od pola
nazwa_wydziału, który nie jest kluczem
głównym.
Z atrybutów tych tworzy się nową tabelę
wydziały. Atrybut nazwa_wydziału zostaje
usunięty z tabeli pracownicy, natomiast nr_w
pozostaje w niej jako klucz obcy.
Copyright by Piotr Greniewski
36
Trzecia postać normalna – 3NF
nr
nr_p
data_ro
z
wynag
902
15
10-07-
96
1000
902
35
29-07-
96
1000
902
45
20-
08096
1500
988
15
15-07-
96
2000
988
25
14-07-
96
2500
988
45
20-08-
96
2000
562
25
20-05-
96
1500
nr_p
projekt
15
ocena
35
testowani
e
45
przekazan
ie
25
analiza
przypisania - 3NF
nr
nazwis
ko
nr_w
nazwa_w
nr_k
kierowni
k
902
Kowals
ki
10
sprzedaż
988
Janowski
988
Janows
ki
20
marketin
g
699
Walasik
562
Kowals
ki
10
sprzedaż
099
Filipowic
z
pracownicy - 2NF
projekt - 3NF
nr
nazwis
ko
nr_w
nr_k
902
Kowals
ki
10
988
988
Janows
ki
20
699
562
Kowals
ki
10
099
nr_w
nazwa_w
10
sprzedaż
20
marketin
g
10
sprzedaż
pracownicy - 3NF
wydziały - 3NF
Copyright by Piotr Greniewski
37
Trzecia postać normalna – 3NF
pracownicy
nr
nr_wydzialu (FK)
nr_kierownika (FK)
nazwisko
przypisania
nr (FK)
nr_projektu (FK)
nr_wydzialu (FK)
nr_kierownika (FK)
data_rozp
wynagrodzenie
projekty
nr_projektu
nazwa_projektu
wydzialy
nr_wydzialu
nr_kierownika (FK)
nazwa_wydzialu
kierownicy
nr_kierownika
nazwa_kierownika
Copyright by Piotr Greniewski
38
Trzecia postać normalna – 3NF
Wyjątek
: Stosowanie reguł trzeciej postaci
normalnej, chociaż teoretycznie wskazane, nie
zawsze jest praktyczne.
Chcąc wyeliminować wszystkie możliwe wewnętrzne
zależności pomiędzy polami tabeli Klienci, należy
utworzyć oddzielne tabele dla miast, kodów
pocztowych, przedstawicieli handlowych, klas klienta
i innych czynników, które mogą być zduplikowane w
wielu rekordach.
Normalizacja oznacza teoretycznie poprawę
wydajności. Jednak wiele mniejszych tabel może
spowodować spadek wydajności lub brak możliwości
otwarcia pliku i przekroczenie pojemności pamięci.
Copyright by Piotr Greniewski
39
Trzecia postać normalna – 3NF
Bardziej realne może okazać się zastosowanie
trzeciej postaci normalnej tylko do często
zmienianych danych.
Pozostawiając niektóre pola zależne, zmień
projekt aplikacji tak, aby po zmianie
dowolnego pola wymagała od użytkownika
sprawdzenia wszystkich pól pokrewnych.
Copyright by Piotr Greniewski
40
Inne postacie normalne
Istnieje czwarta postać normalna, zwana
również postacią normalną Boyce'a-Codda
(BCNF) oraz piąta postać normalna, ale są one
rzadko wykorzystywane w praktyce.
Zlekceważenie tych reguł może skutkować
mniej doskonałym projektem bazy danych, ale
nie powinno ono wpływać na funkcjonalność.
Copyright by Piotr Greniewski
41
Postać normalna Boyce-Codda
Postać normalna Boyce-Codda (BCNF)
- Ta
postać normalna jest uzupełniająca w
stosunku do trzeciej postaci normalnej. Jest
ona niezbędna w przypadku, gdy atrybuty
będące kandydatami na klucze są:
wielokrotne
złożone
nakładające się na siebie
Copyright by Piotr Greniewski
42
Postać normalna Boyce-Codda
Weźmy pod uwagę dwóch nakładających się
kandydatów na klucze w tablicy regiony a mianowicie
(region, gmina) oraz (kod_regionu,gmina).
Region jest związany funkcjonalnie tylko z częścią
klucza (kod_regionu, gmina). Nie jest to naruszeniem
3NF ani 2NF ponieważ region sam jest częścią klucza.
Relacja jest w BCNF wtedy i tylko wtedy gdy każdy
wyznacznik jest kandydatem na klucz.
kod_regio
nu
region
gmina
województw
o
20
Północny
4
szczecińskie
20
Północny
9
słupskie
20
Północny
7
gdańskie
10
Centralny 4
warszawskie
15
Wschodni 2
lubelskie
Regiony – 3NF
Copyright by Piotr Greniewski
43
Postać normalna Boyce-Codda
kod_regio
nu
region
gmina
województw
o
20
Północny
4
szczecińskie
20
Północny
9
słupskie
20
Północny
7
gdańskie
10
Centralny 4
warszawskie
15
Wschodni 2
lubelskie
regiony – 3NF
kod_regio
nu
region
20
Północny
10
Centralny
15
Wschodni
kod_regio
nu
gmina
województw
o
20
4
szczecińskie
20
9
słupskie
20
7
gdańskie
10
4
warszawskie
15
2
lubelskie
województwa – BCNF
regiony – BCNF
Copyright by Piotr Greniewski
44
Czwarta postać normalna – 4NF
Czwarta postać normalna
: odnosi się do
wielowartościowej zależności atrybutów i jest
naruszona wtedy gdy taka zależność występuje w
tej samej tabeli.
Relacja (tabela) jest w 4NF wtedy i tylko wtedy
gdy jest w 3NF i nie zawiera wielowartościowej
zależności atrybutów.
Tabela umiejętności (następny slajd) nie zawiera
żadnych funkcjonalnie zależnych atrybutów i
dlatego nie narusza 3NF.
Aby sprowadzić ją do 4NF należy wyłączyć
niezależne wielowartościowe zależności do tabel:
umiejętności_software i umiejętności_hardware
Copyright by Piotr Greniewski
45
Czwarta postać normalna – 4NF
nazwisk
o
software
hardwar
e
Bielecki
SQL*Form
s
IBM
Bielecki
SQL*Form
s
VAX
Bielecki
SQL*QMX
IBM
Bielecki
SQL*QMX
VAX
Bielecki
SQL*Plus
IBM
Bielecki
SQL*Plus
VAX
nazwisk
o
hardwar
e
Bielecki
IBM
Bielecki
VAX
nazwisk
o
software
Bielecki
SQL*Form
s
Bielecki
SQL*QMX
Bielecki
SQL*Plus
3NF
4NF
4NF
Copyright by Piotr Greniewski
46
Piąta postać normalna – 5NF
Piąta postać normalna
: tabela jest w 5NF
wtedy, gdy jest w 4NF, a jej dekompozycja i
ponowne połączenie (join) nie są operacjami
symetrycznymi (tzn. nie prowadzą do
rekonstrukcji tablicy wyjściowej)
Zagadnienie zilustrowano na następnym
slajdzie.
Copyright by Piotr Greniewski
47
Piąta postać normalna – 5NF
podział pierwotnej tabeli a
następnie jej złączenie
produkuje fałszywy wniosek
pierwotna tabela jest w 5NF
osoba
gra
kraj
Natasza
poker
Rosja
Natasza
szachy
USA
Borys
szachy
Rosja
gra
kraj
poker
Rosja
szachy
USA
szachy
Rosja
osoba
kraj
Natasza
Rosja
Natasza
USA
Borys
Rosja
osoba
gra
Natasza
poker
Natasza
szachy
Borys
szachy
osoba
gra
kraj
Natasza
poker
Rosja
Natasza
szachy
USA
Borys
szachy
Rosja
Natasza
szachy
Rosja
Copyright by Piotr Greniewski
48
Normalizowanie tabeli przykładowej
W poniższych krokach przedstawiono proces
normalizacji fikcyjnej tabeli student.
Mamy tabelę student w postaci
nieznormalizowanej
Tabela student
Nr_Stude
nta
Opieku
n
Pokój
Klasa_1
Klasa_2
Klasa_3
1022
Nowak
412
101-07
143-01
159-02
4123
Kowals
ki
216
201-01
211-02
214-01
Copyright by Piotr Greniewski
49
Normalizowanie tabeli przykładowej
Pierwsza postać normalna:
brak powtarzających się grup
Tabele powinny mieć tylko dwa wymiary.
Ponieważ jeden student ma kilka klas, klasy
powinny znajdować się w oddzielnej tabeli.
Występowanie pól Klasa_1, Klasa_2 i Klasa_3
w powyższych rekordach jest oznaką
problemów podczas projektowania
Copyright by Piotr Greniewski
50
Normalizowanie tabeli przykładowej
Arkusze kalkulacyjne często wykorzystują
trzeci wymiar, ale tabele nie powinny.
Innym podejściem do problemu jest związek
(relationship) jeden-do-wielu, w którym nie
należy strony jeden i strony wielu umieszczać
w tej samej tabeli.
Zamiast tego, należy utworzyć inną tabelę w
pierwszej postaci normalnej, eliminując
powtarzające się grupy (Nr_Klasy), tak jak to
przedstawiono poniżej:
Copyright by Piotr Greniewski
51
Normalizowanie tabeli przykładowej
Tabela student w 1NF
Tabela student
Nr_Studenta
(PK)
Opiekun Pokój
Nr_Klas
y
1022
Nowak
412
101-07
1022
Nowak
412
143-01
1022
Nowak
412
159-02
4123
Kowalsk
i
216
201-01
4123
Kowalsk
i
216
211-02
4123
Kowalsk
i
216
214-01
Copyright by Piotr Greniewski
52
Normalizowanie tabeli przykładowej
Druga postać normalna:
eliminowanie powtarzających się danych
W powyższej tabeli dla każdego pola
Nr_Studenta istnieje wiele wartości w polach
Nr_Klasy.
Pole Nr_Klasy nie jest czynnościowo zależne od
pola Nr_Studenta (klucz podstawowy), dlatego
ta relacja nie znajduje się w drugiej postaci
normalnej.
Drugą postać normalną przedstawiono na
następujących dwóch tabelach: studenci i
rejestracja
Copyright by Piotr Greniewski
53
Normalizowanie tabeli przykładowej
Tabele student i rejestracja w 2NF
Tabela student
Nr_Studenta
(PK)
Opiekun Pokój
1022
Nowak
412
4123
Kowalski 216
Tabela rejestracja
Nr_Student
a
Nr_Klasy
(PK)
1022
101-07
1022
143-01
1022
159-02
4123
201-01
4123
211-02
4123
214-01
Copyright by Piotr Greniewski
54
Normalizowanie tabeli przykładowej
Trzecia postać normalna:
eliminowanie danych, które nie zależą od
klucza
W ostatnim przykładzie pole Pokój (numer
pokoju opiekuna) jest czynnościowo zależne
od atrybutu Opiekun.
Rozwiązaniem jest przeniesienie tego atrybutu
z tabeli Studenci do tabeli Wydział, tak jak to
przedstawiono poniżej:
Copyright by Piotr Greniewski
55
Normalizowanie tabeli przykładowej
Tabele student, rejestracja i wydział w 3NF
Tabela wydział
Opiekun Pokój
Wydział
(PK)
Nowak
412
40
Kowalsk
i
216
41
Tabela student
Nr_Studenta
(PK)
Wydział
(FK)
1022
40
4123
41
Tabela rejestracja
Nr_Studenta
(FK)
Nr_Klasy(P
K)
1022
101-07
1022
143-01
1022
159-02
4123
201-01
4123
211-02
4123
214-01