Bazy danych – wykład szósty Normalizacja modelu bazy danych

Bazy danych – wykład szósty
Normalizacja modelu bazy danych
Konrad Zdanowski
Uniwersytet Kardynała Stefana Wyszyńskiego, Warszawa
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
1 / 52
Przykład
Rozważmy tabele˛ Ksiazki:
imie
S.
P.
A.
S.
nazwisko
Cenckiewicz
Gontarczyk
Nowak
Cenckiewicz
tytul
SB a Lech
SB a Lech
Od Polski ...
Długie ramie˛ ...
rok
2009
2009
2010
2011
wydawca
Arcana
Arcana
Arcana
Zysk i Sk-a
adres
Kraków
Kraków
Kraków
Warszawa
W tabeli wystepuje
˛
redundancja informacji.
Skutkuje to trudnościami przy dodawaniu, aktualizowaniu lub
usuwaniu krotek z tabeli.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
2 / 52
Przykład
imie
S.
P.
A.
S.
nazwisko
Cenckiewicz
Gontarczyk
Nowak
Cenckiewicz
tytul
SB a Lech
SB a Lech
Od Polski do
Długie ramie˛
rok
2009
2009
2010
2011
wydawca
Arcana
Arcana
Arcana
Zysk i Sk-a
adres
Kraków
Kraków
Kraków
Warszawa
Dodanie krotki (P., Zyzak, ..., Arcana, Warszawa) rozspójni nam
informacje w tabeli.
Wydawnictwo Arcana bedzie
˛
miało dwa adresy.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
3 / 52
Przykład
imie
S.
P.
A.
S.
nazwisko
Cenckiewicz
Gontarczyk
Nowak
Cenckiewicz
tytul
SB a Lech
SB a Lech
Od Polski do
Długie ramie˛
rok
2009
2009
2010
2011
wydawca
Arcana
Arcana
Arcana
Zysk i Sk-a
adres
Kraków
Kraków
Kraków
Warszawa
Modyfkujac
˛ adres wydawnictwa musimy zrobić to we wszystkich
krotkach, w których ono wystepuje
˛
Zmiana jednej informacji wymaga zmiany wielu krotek w bazie
danych
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
4 / 52
Przyklad
imie
S.
P.
A.
S.
nazwisko
Cenckiewicz
Gontarczyk
Nowak
Cenckiewicz
tytul
SB a Lech
SB a Lech
Od Polski do
Długie ramie˛
rok
2009
2009
2010
2011
wydawca
Arcana
Arcana
Arcana
Zysk i Sk-a
adres
Kraków
Kraków
Kraków
Warszawa
Usuwajac
˛ ksiażk˛
˛ e Długie ramie˛ Moskwy stracimy wszystkie
informacje o wydawnictwie Zysk i Sk-a.
Usuwajać
˛ ksiażk˛
˛ e tracimy informacje o wydawnictwie.
Jedna krotka zawiera dwie, niezależne od siebie informacje. Nie
można usunać
˛ jednej nie usuwajac
˛ drugiej.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
5 / 52
Anomalie projektu BD
Nadmiarowość – powtarzanie sie˛ wiele razy tej samej informacji.
Anomalie aktualizacji – modyfikacja jednej informacji może
pominać krotki.
Anomalie usuwania – usuniecie
˛
krotek pozbawia nas informacji,
które chcielibyśmy zachować.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
6 / 52
Cechy optymalnego modelu BD
Ta sama informacja w BD nie powtarza sie˛ wiele razy.
Informacje w różnych krotkach nie sa˛ od siebie zależne.
Informacja w jednej krotce jest „atomowa”.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
7 / 52
Cechy optymalnego modelu BD
Podczas procesu normalizacji usuwamy anomalie, które moga˛
wystepować
˛
w BD.
Wyróżnimy pewne postacie normalne oraz omówimy jak je
uzyskać.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
8 / 52
Pierwsza postać normalna
Definicja 1
Relacja jest w pierwszej postaci normalnej (1NF), jeśli wszystkie
atrybuty tej relacji maja˛ atomowe typy danych.
Pierwsza postać normalna wyklucza sytuacje,
˛ w której typ danych
danego atrybutu to zbiór wartości, struktura lub inna relacja.
Dlatego atrybut imiona, który zawierałby wszytkie imiona danej osoby
nie jest zgodny z 1NF.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
9 / 52
Druga postać normalna
Definicja 2
Relacja jest w drugiej postaci normalnej (2NF), jeśli jest w 1NF oraz
żaden atrybut który nie wchodzi w skład klucza nie zależy funkcyjnie
od atrybutów bed
˛ acych
˛
właściwym podzbiorem klucza.
Druga postać normalna wyklucza sytuacje,
˛ w której atrybut X nie
wchodzacy
˛ w skład klucza zależy funkcyjnie od właściwej cz˛eści
klucza. Jeśli tak by było, to możemy stworzyć nowa˛ tabele˛ z ta˛
zależnościa˛ usuwajac
˛ X z oryginalnej tabeli.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
10 / 52
Dekompozycja relacji
Definicja 3
Rozważmy relacje˛ R(A1 , . . . , An ) oraz zbiory atrybutów
{B1 , . . . , Bk } ∪ {C1 , . . . , Cm } = {A1 , . . . , An }.
Dekompozycja R na relacje S(B1 , . . . , Bk ) i T (C1 , . . . , Cm ) zachodzi
gdy
S = πB1 ,...,Bk (R) oraz T = πC1 ,...,Cm (R).
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
11 / 52
Przykład
Relacje˛ Ksiazki możemy zdekomponować na Wydanie(imie, nazwisko,
tytul, rok, wydawca) oraz Wydawnictwo(wydawca, adres).
imie
S.
P.
A.
S.
nazwisko
Cenckiewicz
Gontarczyk
Nowak
Cenckiewicz
tytul
SB a Lech
SB a Lech
Od Polski do
Długie ramie˛
rok
2009
2009
2010
2011
wydawca
Arcana
Arcana
Arcana
Zysk i Sk-a
imie
S.
P.
A.
S.
nazwisko
Cenckiewicz
Gontarczyk
Nowak
Cenckiewicz
tytul
SB a Lech
SB a Lech
Od Polski do
Długie ramie˛
rok
2009
2009
2010
2011
wydawca
Arcana
Arcana
Arcana
Zysk i Sk-a
wydawca
Arcana
Zysk i Sk-a
adres
Kraków
Kraków
Kraków
Warszawa
adres
Kraków
Warszawa
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
12 / 52
Dekompozycja relacji
Majac
˛ dana˛ dekompozycje˛ R(A1 , . . . , An ) na S(B1 , . . . , Bk ) i
T (C1 , . . . , Cm ) możemy zdefiniować złożenie relacji S i T .
Definicja 4
Niech D1 , . . . , Dr bedzie
˛
cz˛eścia˛ wspólna˛ B1 , . . . , Bk i C1 , . . . , Cm .
Możemy zdefiniować złaczenie
˛
tych relacji
R0 = S o
nD1 ,...,Dr T .
Relacje˛ R 0 tworzymy jako S natural join T (czyli S join T on
D1 , . . . , Dr ).
Ale czy R 0 = R?
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
13 / 52
Zależności funkcyjne
Definicja 5
Atrybuty B1 , . . . , Bm sa˛ zależne funkcyjnie od atrybutów A1 , . . . , An w
relacji R jeśli dowolne dwie krotki t, t 0 z relacji R, które zgadzaja˛ sie˛ na
atrybutach A1 , . . . , An musza˛ zgadzać sie˛ na atrybutach B1 , . . . , Bm .
Zależność funkcyjna˛ oznaczamy przez A1 . . . An → B1 . . . Bm .
Definicja 6
Zależnośc funkcyjna˛ A1 . . . An → A nazywamy trywialna˛ jeśli
A ∈ {A1 , . . . , An }.
Zależność funkcyjna˛ należy traktować jako zależność dotyczac
˛ a˛
schematu relacji a nie jej szczególnego wystapienia.
˛
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
14 / 52
Zależności funkcyjne
Dekomponujac
˛ relacje˛ R na relacje S i T dzielimy zbiór zależności
funkcyjnych relacji R na dwa zbiory dla S i T w zależności od tego,
która z relacji posiada wszystkie atrybuty wystepuj
˛ ace
˛ w danej ZF.
Pewne zależności moga˛ pozostać nie przydzielone – takich
przypadków chcemy uniknać,
˛ gdyż tracimy wtedy informacje o
schemacie BD.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
15 / 52
Własności dekompozycji
Twierdzenie 7
Niech S(B1 , . . . , Bn ) i T (C1 , . . . , Cm ) beda˛ dekompozycja˛
R(A1 , . . . , Ak ). Dekompozycja nie traci informacji o relacji R jeśli jest
spełniony przynajmniej jeden z warunków:
{B1 , . . . , Bn } ∩ {C1 , . . . , Cm } → {B1 , . . . , Bn },
{B1 , . . . , Bn } ∩ {C1 , . . . , Cm } → {C1 , . . . , Cm }.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
16 / 52
Własności dekompozycji
Dekompozycje możemy wykonać gdy:
eliminuje to anomalie omówione wcześniej,
możemy odtworzyć pierwotna˛ relacje˛ z nowych relacji (nie tracimy
informacji),
dowolne relacje spełniajace
˛ zależności funkcyjne obowiazuj
˛ ace
˛ w
zdekomponowanych relacjach można połaczyć
˛
w relacje˛
stanowiac
˛ a˛ przypadek relacji oryginalnej.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
17 / 52
Przykład
imie
S.
P.
A.
S.
nazwisko
Cenckiewicz
Gontarczyk
Nowak
Cenckiewicz
tytul
SB a Lech
SB a Lech
Od Polski do
Długie ramie˛
rok
2009
2009
2010
2011
wydawca
Arcana
Arcana
Arcana
Zysk i Sk-a
adres
Kraków
Kraków
Kraków
Warszawa
Możemy założyć, że wydawnictwo ma jedna˛ siedzibe˛ a wiec
˛
wystepuje
˛
zależność funkcyjna: wydawca → adres.
Podobnie, jeśli założymy, że w danym roku tylko jedno
wydawnictwo wydaje ksiażk˛
˛ e o danym tytule, to otrzymamy
zależność funkcyjna:
˛ tytul, rok → wydawca.
Zauważmy, że powyższe zależności pociagaj
˛ a˛ za soba˛ zależność:
tytul, rok → adres.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
18 / 52
Zależności funkcyjne
Fakt 8
Zależność funkcyjna A1 . . . An → B1 . . . Bm jest równoważna zbiorowi
zależności A1 . . . An → Bi , dla i ¬ m.
Dlatego od teraz zakładamy, że każda ZF ma tylko jeden atrybut po
prawej stronie.
Fakt 9
Jeśli A1 . . . An → Bi , dla i ¬ m, oraz B1 . . . Bm → C, to A1 . . . An → C.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
19 / 52
Zależności funkcyjne
Majac
˛ dany zbiór zależności funkcyjnych F = {F1 , . . . , Fn } oraz zbiór
atrybutów A1 , . . . , Am możemy obliczyć zbiór wszystkich atrybutów X
zależnych funkcyjnie od A1 , . . . , Am :
X ← {A1 , . . . , Am }
while isnieja˛ B1 , . . . , Bk ∈ X oraz C 6∈ X ( (B1 . . . Bk → C) ∈ F) do
X ← X ∪ {C}
end while
Definicja 10
Dla ustalonego F, zbiór atrybutów zależnych funkcynjnie od A1 , . . . , An
bedziemy
˛
oznaczali przez {A1 , . . . , An }+ .
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
20 / 52
Klucze
Definicja 11
Zbiór atrybutów {A1 , . . . , An } jest kluczem w relacji R jeśli
1
2
{A1 , . . . , An }+ to zbiór wszystkich atrybutów R,
żaden właściwy podzbiór zbioru {A1 , . . . , A − n} nie ma powyższej
własności.
Definicja 12
Jeśli zbiór atrybutów X zawiera klucz relacji R to mówimy, że X jest
nadkluczem R.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
21 / 52
Postać normalna Boyce’a–Coda — BCNF
Definicja 13
Relacja R jest w BCNF jeśli dla jej każdej nietrywialnej ZF,
A1 . . . An → A, zbiór {A1 , . . . , An } jest nadkluczem R.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
22 / 52
Przykład
imie
S.
P.
A.
S.
nazwisko
Cenckiewicz
Gontarczyk
Nowak
Cenckiewicz
tytul
SB a Lech
SB a Lech
Od Polski do
Długie ramie˛
rok
2009
2009
2010
2011
wydawca
Arcana
Arcana
Arcana
Zysk i Sk-a
adres
Kraków
Kraków
Kraków
Warszawa
Relacja nie jest w BCNF bo atrybut wydawca nie jest kluczem w relacji
Ksiazki ale wystepuje zaleznosc wydawca → adres.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
23 / 52
Dekompozycja do BCNF
Jeśli relacja R(A1 , . . . , An ) nie jest w BCNF to istnieja˛ zbiór
atrybutów X oraz atrybut A takie, że
I
I
X nie jest nadkluczem w R,
X → A jest nietrywialna˛ zależnościa˛ funkcyjna.
˛
Możemy wtedy zdekomponować R do relacji S zawierjacej
˛
atrybuty X + oraz T zawierajac
˛ a˛ atrybuty X oraz atrybuty R spoza
X +.
Powstałe relacje „dziedzicza”
˛ zależnosci funkcyjne z R
Jeśli powstałe relacje nie sa˛ w BCNF, możemy wykonać także ich
dekompozycje.
˛
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
24 / 52
Przykład
imie
S.
P.
A.
S.
nazwisko
Cenckiewicz
Gontarczyk
Nowak
Cenckiewicz
tytul
SB a Lech
SB a Lech
Od Polski do
Długie ramie˛
rok
2009
2009
2010
2011
wydawca
Arcana
Arcana
Arcana
Zysk i Sk-a
adres
Kraków
Kraków
Kraków
Warszawa
Możemy zdekomponować Ksiazki na Wydanie(imie, nazwisko, tytul,
rok, wydawca) oraz Wydawnictwo(wydawca, adres).
imie
S.
P.
A.
S.
nazwisko
Cenckiewicz
Gontarczyk
Nowak
Cenckiewicz
wydawca
Arcana
Zysk i Sk-a
tytul
SB a Lech
SB a Lech
Od Polski do
Długie ramie˛
rok
2009
2009
2010
2011
wydawca
Arcana
Arcana
Arcana
Zysk i Sk-a
adres
Kraków
Warszawa
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
25 / 52
Dekompozycja do BCNF
Twierdzenie 14
Niech X → A jest nietrywialna˛ zależnościa˛ funkcyjna˛ w R(A) oraz
niech S i T sa˛ dekompozycja˛ R taka,
˛ że S = πX + (R) i T = πY (R), dla
Y = X ∪ ({A} \ X + ). Wtedy
So
n T = R.
Możemy odtworzyć relacje˛ R z dwóch relacji, na które rozłozylismy R
podczas algorytmu dekompozycji.
Jest tak dlatego, że każdej krotce z T odpowiada dokładnie jedna
krotka z S.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
26 / 52
Problemy z BCNF – przykład
Rozważmy relacje˛ Sieci(sprzedawca, miasto, marka), o której
założymy, że
w każdym mieście jest najwyżej jeden sprzedaca danej marki,
sprzedawca ma siedzibe˛ dokładnie w jednym mieście (nie tworzy
sieci).
Możemy opisać to jako zależności funkcyjne
miasto, marka → sprzedawca,
sprzedawca → miasto.
Uwaga. Sprzedawcy moga˛ sprzedawać różne marki ale na każda˛
mark˛e maja˛ monopol w swoim mieście.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
27 / 52
Problemy z BCNF – przykład
Sieci(sprzedawca, miasto, marka):
miasto, marka → sprzedawca,
sprzedawca → miasto.
Klucze tej relacji to:
miasto, marka;
sprzedawca, marka;
Możemy znaleźć ZF: sprzedawca → miasto, która sugeruje
dekompozycje na relacje
S(sprzedawca, miasto), ZF: sprzedawca → miasto.
T (sprzedawca, marka).
Gdzie jest problem?
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
28 / 52
Problemy z BCNF – przykład
Sieci(sprzedawca, miasto, marka):
miasto, marka → sprzedawca,
sprzedawca → miasto.
Dekompozycja:
S(sprzedawca, miasto), ZF: sprzedawca → miasto.
T (sprzedawca, marka).
Chcielibyśmy, żeby dowolne relacje S i T spełniajace
˛ wyliczone ZF-y
dały sie˛ skomponować w relacje˛ Sieci.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
29 / 52
Problemy z BCNF – przykład
Chcielibyśmy, żeby dowolne relacje S i T spełniajace
˛ wyliczone ZF-y
dały sie˛ skomponować w relacje˛ Sieci.
S(sprzedawca, miasto), ZF: sprzedawca → miasto.
T (sprzedawca, marka).
Rozważmy
sprzedawca
Kulczyk
Solorz
miasto
Poznan
Poznan
sprzedawca
Kulczyk
Solorz
miasto
audi
audi
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
30 / 52
Problemy z BCNF – przykład
sprzedawca
Kulczyk
Solorz
miasto
Poznan
Poznan
sprzedawca
Kulczyk
Solorz
marka
audi
audi
Połaczenie
˛
tych dwóch relacji po atrybucie sprzedawca daje
sprzedawca miasto marka
Kulczyk
Poznan audi
Solorz
Poznan audi
Nie jest zachowana ZF: miasto, marka → sprzedawca!
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
31 / 52
Trzecia postać normalna – 3NF
Definicja 15
Relacja R o zależnościach funkcyjnych F jest w trzeciej postaci
normalnej (3NF) jeśli dla dowolnej ZF, A1 . . . Ak → B, która˛ można
wyprowadzić z F zachodzi przynajmniej jeden warunek:
zbiór A1 , . . . , Ak jest nadkluczem R,
B wchodzi w skład pewnego klucza w R.
W warunku dla 3NF ograniczamy zbiór ZF, które „psuja”
˛ postać w
stosunku do BCNF.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
32 / 52
Dekompozycja do 3NF
Definicja 16
Niech F bedzie zbiorem zależności funkcyjnych dla relacji R. Zbiór
zależności funkcyjnych G jest baza˛ dla F jeśli każda ZF z F wynika z
zależności funkcyjnych w G i nic wiecej
˛
nie można wywnioskować z G.
Zbiór G jest baza˛ minimalna˛ dla F jeśli
G jest baza˛ dla F,
usuniecie
˛
dowolnego G ∈ G powoduje, że nowy zbiór ZF przestaje
być baza,
˛
dla dowolnej A1 . . . Ak → B ∈ G usuniecie
˛
jednego z Ai powoduje,
że nowy zbiór przestaje być baza.
˛
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
33 / 52
Dekompozycja do 3NF
Algorytm dekompozycji R i zależności funkcyjnych F do 3NF.
1
wyznacz baz˛e minimalna˛ G dla F,
2
dla każdej X → A ∈ G tworzymy nowa˛ relacje˛ o atrybutach
X ∪ {A}.
3
jesli nie stworzyliśmy relacji, która zawiera klucz R to dodajemy
taka˛ relacje.
˛
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
34 / 52
Dekompozycja do 3NF – przyklad
Rozważmy relacje˛ Samochody(wlasciciel, samochod, pojemnosc) oraz
zależności
wlasciciel → samochod,
samochod → pojemnosc,
wlasciciel → pojemnosc.
wlasciciel
Kowalski
Nowak
Turek
Gołab
˛
Szpak
samochod
audi
audi
ford
fiat
trabant
pojemnosc
1.8
1.8
1.8
1.4
1.4
Jedynym kluczem tej relacji jest atrybut wlasciciel.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
35 / 52
Dekompozycja do 3NF
Relacje˛ Samochody rozkładamy na
wlasciciel
Kowalski
Nowak
Turek
Gołab
˛
Szpak
samochod
audi
audi
ford
fiat
trabant
samochod
audi
ford
fiat
trabant
pojemnosc
1.8
1.8
1.4
1.4
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
36 / 52
Przykład
Rozważmy relacje˛ Filmy(aktor, adres, tytul).
aktor
Pitt
Pitt
Jolie
Jolie
Jolie
Jolie
adres
L.A
Londyn
Paryż
L.A
Paryż
L.A
tytul
Mr & Mrs Smith
Mr & Mrs Smith
Rybki z Ferajny
Rybki z Ferajny
Mr & Mrs Smith
Mr & Mrs Smith
W tej relacji nie zachodzi żadna nietrywialna ZF.
Ale dodajac
˛ nowy film z Pittem należy dodać dwie krotki. Nie ma
powodu, żeby opuścić jeden z adresów Pitta. Podobnie usuwajać
˛
aktora z filmu lub adres aktora trzeba usunać
˛ wiele krotek.
W relacji wystepuj
˛ a˛ anomalie ale jest ona w BCNF.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
37 / 52
Zależności wielowartościowe
Niech A1 , . . . , An i B1 , . . . , Bk to atrybuty relacji R. Zależność
wielowartościowa pomiedzy
˛
A1 , . . . , An i B1 , . . . , Bk zachodzi jeśli po
wybraniu krotek z R o ustalonej wartości atrybutów A1 , . . . , An ,
wartości atrybutów B1 , . . . , Bk nie zależa˛ od pozostałych atrybutów
relacji.
aktor
Pitt
Pitt
Jolie
Jolie
Jolie
Jolie
adres
L.A
Londyn
Paryż
L.A
Paryż
L.A
tytul
Mr & Mrs Smith
Mr & Mrs Smith
Rybki z Ferajny
Rybki z Ferajny
Mr & Mrs Smith
Mr & Mrs Smith
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
38 / 52
Zależności wielowartościowe
Definicja 17
Niech A1 , . . . , An i B1 , . . . , Bk to atrybuty relacji R i niech C1 , . . . , Cm to
pozostałe atrybuty R.
Zależność wielowartościowa w R pomiedzy
˛
A1 , . . . , An i B1 , . . . , Bk ,
ozn. A1 . . . An B1 . . . Bk , zachodzi jeśli dla każdej pary krotek
t, s ∈ R takich, że
t(A1 , . . . , An ) = s(A1 , . . . , An )
istnieje krotka v ∈ R taka, że
v (A1 , . . . , An ) = s(A1 , . . . , An ) (= t(A1 , . . . , An )),
v (B1 , . . . , Bk ) = s(B1 , . . . , Bk ),
v (C1 , . . . , Cm ) = s(C1 , . . . , Cm ).
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
39 / 52
Zależności wielowartościowe
Fakt 18
Jeśli A1 . . . An → B1 . . . Bk , to A1 . . . An B1 . . . Bk .
Fakt 19
Niech A1 , . . . , An i B1 , . . . , Bk to atrybuty relacji R i niech C1 , . . . , Cm to
pozostałe atrybuty R. Jeśli A1 . . . An B1 . . . Bk , to
A1 . . . An C1 . . . Cm
Fakt 20
Jeśli A1 , . . . , An , B1 , . . . , Bk to wszystkie atrybuty relacji R, to zachodzi
A1 . . . An B1 . . . Bk .
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
40 / 52
Zależności wielowartościowe
Definicja 21
Zależność wielowartościowa A1 . . . An B1 . . . Bk jest trywialna jeśli
{B1 , . . . , Bk } ⊆ {A1 , . . . , An } lub A1 , . . . , An , B1 , . . . , Bk to wszystkie
atrybuty relacji.
Fakt 22
Dla każdej relacji R zachodza˛ wszystkie trywialne zależności
wielowartościowe.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
41 / 52
Czwarta postać normalna – 4NF
Definicja 23
Relacja R jest w czwartej postaci normalnej (4NF) jeśli dla dowolnej
nietrywialnej zależności wielowartościowej A1 . . . An B1 . . . Bk w R,
zbiór atrybutów {A1 , . . . , An } jest nadkluczem.
Obserwacja. Warunek na 4NF jest powtórzeniem warunku dla BCNF
ale dla zależności wielowartościowych.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
42 / 52
Czwarta postać normalna – 4NF
Ponieważ każda zależności funkcyjna jest też wielowartościowa,
˛ to z
ostatniej obserwacji wynika.
Fakt 24
Jeśli relacja R jest w 4NF to jest też w BCNF.
Zachodza˛ wiec
˛ nastepujace
˛ wynikania:
R jest w 4NF =⇒ R jest w BCNF =⇒ R jest w 3NF.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
43 / 52
4NF – przykład dekompozycji
Relacje˛ Filmy
aktor
Pitt
Pitt
Jolie
Jolie
Jolie
Jolie
adres
L.A
Londyn
Paryż
L.A
Paryż
L.A
tytul
Mr & Mrs Smith
Mr & Mrs Smith
Rybki z Ferajny
Rybki z Ferajny
Mr & Mrs Smith
Mr & Mrs Smith
możemy zdekomponować używajac
˛ zależności aktor adres do
Adresy(aktor, adres) i Wystapil(aktor, tytul).
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
44 / 52
4NF – przykład dekompozycji
Adresy:
aktor
Pitt
Pitt
Jolie
Jolie
Wystapil:
adres
L.A
Londyn
L.A
Paryż
aktor
Pitt
Jolie
Jolie
tytul
Mr & Mrs Smith
Mr & Mrs Smith
Rybki z Ferajny
Oryginalna˛ relacje˛ Filmy możemy odtworzyć jako Adresy o
n Wystapil
czyli
s e l e c t A . a k t o r , A . adres , W. t y t u l
from Adresy A , W y s t a p i l W
where A . a k t o r = W. a k t o r ;
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
45 / 52
Dekompozycja do 4NF
Aby zdekomponować relacje˛ R do 4NF musimy:
1
2
znaleźć nietrywialna˛ zależność wielowartościowa˛
A1 . . . An B1 . . . Bk taka,
˛ że {A1 , . . . , An } nie jest nadkluczem R,
jeśli C1 , . . . , Cm to pozostałe atrybuty R, to dekomponujemy R na:
I
I
3
S(A1 , . . . , An , B1 , . . . , Bk )
T (A1 , . . . , An , C1 , . . . , Cm ).
powtarzamy dekompozycje˛ dla nowych relacji.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
46 / 52
Do zrozumienia
na czym polegaja˛ anomalie, zależności funkcyjne, klucze, 1NF, 2NF,
dekompozycja, 3NF
może nawet:
BCNF, zależnosci wielowartościowe, 4NF, ...
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
47 / 52
Przykład
Chcemy stworzyć BD dla sklepu "Mydło i powidło". Możemy
zdefiniować jedna˛ tabele˛ Zamówienia o jednej kolumnie zawierajac
˛ a˛
wartości takie jak np.
dane klienta – imie,
˛ nazwisko, jego id, ...
liste˛ przedmiotów które zakupił, nazwy, ilość, ceny, kwota zakupu,
...
dane do rachunku i dane do wysyłki.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
48 / 52
Przykład
Możemy zdefiniować jedna˛ tabele˛ Zamówienia o jednej kolumnie
zawierajac
˛ a˛ wartości takie jak np.
Adam Abacki, adres do rachunku, adres do wysyłki, nożyczki, kod
nożyczek, ilość, cena, VAT, klej, kod kleju, ilość, cena, VAT, wartość
zakupów, data.
Taki schemat nie jest w 1NF, jest prosty lecz trudno wydobyć z niego
informacje. Dlatego, rozdzielamy dane na oddzielne kolumny.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
49 / 52
Przykład
Ponieważ ilość artykułów w zamówieniu może być różna a mamy
tylko stała˛ liczbe˛ kolumn musimy stworzyć dwie tabele:
I
I
Zamówienia: id_zamowienia (PK), data, id_klienta, imie, nazwisko,
adres do rachunku, adres do wysyłki.
Towary: id_zamowienia (PK, FK), id_artykulu (PK), ilosc, cena,
VAT, wartość zakupów.
Jak tylko rozdzielimy dane na odrebne
˛
atrybuty, staniemy przed
pytaniem, co może być kluczem w naszej tabeli.
Zauważmy, że cz˛eśc danych w tabeli Towary zależy tylko od
id_artykulu (cena, VAT). Schemat BD nie jest w 2NF bo
id_artykulu to tylko cz˛eść klucza.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
50 / 52
Przykład
Mamy tabele:
I
I
Zamówienia: id_zamowienia (PK), data, imie, nazwisko, adres do
rachunku, adres do wysyłki.
Towary: id_zamowienia (PK, FK), id_artykulu (PK), ilosc, cena,
VAT, wartość zakupów.
Aby uzyskać 2NF rozdzielamy tabele˛ Towary na
I
I
Towary_zamowione: id_zamowienia (PK, FK), data, imie,
nazwisko, adres do rachunku, adres do wysyłki, id_artykulu (PK,
FK), ilosc, wartosc zakupów.
Asortyment (czyli towary, którymi sklep handluje): id_artykułu
(PK), cena, VAT.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
51 / 52
Przykład
Zamówienia: id_zamowienia (PK), data, id_klienta, imie,
nazwisko, adres do rachunku, adres do wysyłki.
Pola imie, nazwisko, adres do rachunku zależa˛ tylko od id_klienta,
który nie jest kluczem w tabeli. Aby uzyskać 3NF tworzymy nowa˛
tabele.
˛
Zamówienia: id_zamowienia (PK), data, id_klienta (FK), adres do
wysyłki.
Klienci: id_klienta (PK), imie, nazwisko, adres do rachunku.
Konrad Zdanowski ( Uniwersytet Kardynała Stefana
Bazy danych
Wyszyńskiego,
– wykładWarszawa)
szósty Normalizacja modelu bazy danych
52 / 52