Projektowanie relacyjnych baz danych

BAZY DANYCH – wykład 7
Projektowanie
relacyjnych baz danych
Dr hab. Sławomir Zadrożny, prof. PR
Zależności funkcyjne
Niech X i Y oznaczają zbiory atrybutów
relacji R
Powiemy, że dla relacji R obowiązuje
zależność funkcyjna, oznaczana jako
X Y, wtedy gdy jeśli dwie krotki relacji
R mają identyczne wartości wszystkich
atrybutów ze zbioru X, to mają również
identyczne wartości wszystkich
atrybutów ze zbioru Y
Bazy danych – wykład 7
2
Zależności funkcyjne
Functional dependency (FD)
Konwencja oznaczeń: X, Y, Z
oznaczają zbiory atrybutów, zaś A, B,
C,… pojedyncze atrybuty; będziemy
często opisywać zbiór atrybutów jako
np. ABC, zamiast {A,B,C }
Bazy danych – wykład 7
3
Równoważność zapisu
X A1A2…An obowiązuje dla relacji R wtedy
i tylko wtedy gdy dla R zachodzą wszystkie z
poniższych zależności funkcyjnych:
X A1, X A2,…, X An
Przykład: zależność A BC jest równoważna
zależnościom A B i A C
Zazwyczaj będziemy posługiwać się
zależnościami funkcyjnymi z pojedynczymi
atrybutami po prawej stronie
Bazy danych – wykład 7
4
Przykład zależności funkcyjnych
Załóżmy, że reprezentujemy informację o markach
piwa lubianych przez poszczególne osoby z użyciem
relacji o następującym schemacie:
Drinkers(name, addr, beersLiked, manf, favBeer)
Można oczekiwać, że powinny dla niej obowiązywać
następujące zależności funkcyjne:
1. name addr favBeer
równoważnie: name addr i name favBeer
2. beersLiked manf
Bazy danych – wykład 7
5
Przykład: możliwa zawartość relacji
name
Janeway
Janeway
Spock
addr
Voyager
Voyager
Enterprise
beersLiked manf
Bud
A.B.
WickedAle Pete’s
Bud
A.B.
favBeer
WickedAle
WickedAle
Bud
ponieważ name favBeer
ponieważ name addr
ponieważ beersLiked manf
Bazy danych – wykład 7
6
Klucze relacji
K jest nadkluczem relacji R jeśli
dla każdego atrybutu A relacji R
obowiązuje zależność funkcyjna K A
K jest kluczem relacji R jeśli K jest
nadkluczem i żaden podzbiór właściwy
K nie jest nadkluczem
Bazy danych – wykład 7
7
Przykład: Nadklucz
Drinkers(name, addr, beersLiked, manf,
favBeer)
{name, beersLiked} jest nadkluczem,
ponieważ obydwa te atrybuty łącznie
wyznaczają wartości wszystkich
pozostałych atrybutów
name addr favBeer
beersLiked manf
Bazy danych – wykład 7
8
Przykład: Klucz
{name, beersLiked} jest kluczem bo ani
{name} ani {beersLiked} nie są
nadkluczami
nie zachodzi name manf ani
beersLiked addr.
Nie ma żadnych innych kluczy, ale jest
wiele innych nadkluczy; każdy nadzbiór
{name, beersLiked} jest nadkluczem
Bazy danych – wykład 7
9
Skąd się biorą klucze ?
1. Można dodać klucz K i przyjąć
zależności funkcyjne K A dla
wszystkich atrybutów A
2. Można przyjąć zbiór zależności
funkcyjnych i na ich podstawie
wywnioskować co może być kluczem
Bazy danych – wykład 7
10
Zależności funkcyjne jako ograniczenia
Przykład:
jeśli mamy ograniczenie: “dwa wykłady
nie mogą się odbywać w tej samej Sali
w tym samym czasie”
to wyznaczają one:
hour room course
Bazy danych – wykład 7
11
Zależności funkcyjne wynikające z
innych zależności funkcyjnych
Dla relacji R zadane są FD X1 A1, X2 A2,…, Xn An ; chcemy wiedzieć czy FD
Y B obowiązuje w relacji R ?
Przykład: jeśli A B i B C zachodzą, to
na pewno zachodzi A C
Wynikanie zależności funkcyjnych jest
istotne z punktu widzenia projektowania
dobrych schematów relacji
Bazy danych – wykład 7
12
Sprawdzanie wynikania FD
Aby sprawdzić czy Y B wynika z pewnego
zbioru zależności funkcyjnych F, rozważmy
dwie krotki o identycznych wartościach
wszystkich atrybutów ze zbioru Y
Y
0000000. . . 0
00000?? . . . ?
Bazy danych – wykład 7
13
Sprawdzanie wynikania FD – (2)
Użyjmy wszystkich założonych zależności
funkcyjnych ze zbioru F do określenia
wszystkich innych atrybutów, dla których te
dwie krotki muszą mieć również identyczne
wartości
Jeśli B należy do takich atrybutów, to Y B
zachodzi
W przeciwnym przypadku, takie dwie krotki
stanowią przykład relacji zaprzeczającej wynikaniu
Y B ze zbioru F
Bazy danych – wykład 7
14
Domknięcie zbioru atrybutów
Łatwiejszym sposobem sprawdzenie wynikania
zależności Y B ze zbioru zależności F jest
określenie domknięcia (ang. closure) zbioru
atrybutów Y względem zbioru zależności
funkcyjnych F, oznaczanego Y +
1. Y + = Y
2. Wybieramy z F taką zależność funkcyjną X A, że
X ⊆Y+
3. Y + = Y + ∪ A
4. Wracamy do 2. lub kończymy jeśli nie ma więcej FD
Bazy danych – wykład 7
15
Domknięcie zbioru atrybutów
X
Y+
A
nowy Y+
Bazy danych – wykład 7
16
Określanie FD dla rzutu relacji
Motywacja: dla celów normalizacji –
rzutu/rozbicia (schematu) relacji na kilka
(schematów) relacji
Przykład: relacja o schemacie ABCD z
zależnościami funkcyjnymi AB C, C D,
iDA
Rzuty ABCD na ABC i na AD. Jakie zależności
funkcyjne obowiązują w ABC ?
Nie tylko AB C, ale również C A !
Bazy danych – wykład 7
17
Dlaczego C A?
ABCD
a1b1cd1
a2b2cd2
pochodzi
z
ABC
a1b1c
d1=d2 gdyż
CD
a1=a2 gdyż
DA
a2b2c
Stąd, krotki w rzucie relacji ABCD na ABC o równych
wartościach atrybutu C mają również równe wartości
atrybutu A, czyli C A
Bazy danych – wykład 7
18
Ogólna idea domknięcia zbioru FD
1. Dla danego zbioru FD poszukujemy
wszystkich wynikających z nich,
nietrywialnych FD
nietrywialna zależność funkcyjna to taka, w
której prawa strona nie jest zawarta w lewej
2. Ograniczamy się do tych zależności, w
których występują wyłącznie interesujące
nas atrybuty (z danego rzutu schematu
oryginalnego)
Bazy danych – wykład 7
19
Prosty algorytm (duża złożoność)
1. Dla każdego zbioru atrybutów X, wylicz X +
2. Dodaj X A dla każdego A w X + - X.
3. Usuń XY A po wykryciu X A
ponieważ XY A wynika z X A dla
dowolnego rzutu
4. Ostatecznie, pozostaw tylko te FD, które
odnoszą się do atrybutów występujących w
schemacie rozważanego rzutu relacji
Bazy danych – wykład 7
20
Kilka uproszczeń algorytmu
Nie trzeba obliczać domknięcia ani dla
pustego zbioru atrybutów ani dla zbioru
wszystkich atrybutów
Po stwierdzeniu, że X + = zbiorowi
wszystkich atrybutów, wiadomo że to
samo dotyczy każdego nadzbioru zbioru X
Bazy danych – wykład 7
21
Przykład: określanie FD dla rzutu
Rozważmy schemat ABC z zależnościami
funkcyjnymi A B i B C, i jego rzut na AC
A +=ABC ; co daje FD A B i A C
• nie trzeba wyliczać ani {A,B}
+
ani {A,C} +.
B +=BC ; co daje B C
C +=C ; co nie prowadzi do nowych FD
{B,C }+={B,C }; co nie prowadzi do nowych FD
Bazy danych – wykład 7
22
Przykład -- kontynuacja
Wynikowe FD: A B, A C, i B C
Rzut na AC : pozostaje A C
jedyna FD, która odnosi się do zbioru
atrybutów {A,C }
Bazy danych – wykład 7
23
Geometryczna interpretacja FD
Wyobraźmy sobie przestrzeń wszystkich
instancji pewnego schematu relacji,
czyli wszystkie skończone zbiory krotek
zgodne z danym schematem
Każda instancja jest punktem w takiej
przestrzeni
Bazy danych – wykład 7
24
Przykład: R(A,B)
{(1,2), (3,4)}
{(5,1)}
{}
{(1,2), (3,4), (1,3)}
Bazy danych – wykład 7
25
FD jest podzbiorem instancji
Dla każdej zależności funkcyjnej X A
istnieje podzbiór instancji schematu relacji
takich, w których zależność ta obowiązuje
Tak więc możemy utożsamić (reprezentować)
FD z pewnym regionem tej przestrzeni
Trywialna FD = FD, która reprezentowana jest
przez całą przestrzeń
Przykład: A A
Bazy danych – wykład 7
26
Przykład: A B dla schenatu R(A,B)
{(1,2), (3,4)}
AB
{(5,1)}
{}
{(1,2), (3,4), (1,3)}
Bazy danych – wykład 7
27
Reprezentacja zbiorów FD
Jeśli każda FD jest reprezentowana
przez zbiór instancji pewnego schematu
relacji, to zbiór tych FD reprezentowany
jest przez przecięcie tych zbiorów
instancji
To przecięcie obejmuje wszystkie instancje,
w których obowiązują wszystkie FD
Bazy danych – wykład 7
28
Przykład
Instancje spełniające
AB, BC i CD->A
AB
BC
CDA
Bazy danych – wykład 7
29
Wynikanie zależności funkcyjnych
Jeśli FD Y B wynika ze zbioru FD
X1 A1,…,Xn An , to region w
przestrzeni instancji reprezentujący YB
musi zawierać przecięcie regionów
reprezentujących zbiór FD Xi Ai
Czyli każda instancja spełniająca wszystkie
FD Xi Ai z pewnością spełnia Y B.
Jednak, instancja spełniająca Y B nie
musi należeć do tego przecięcia
Bazy danych – wykład 7
30
Przykład
A->B A->C B->C
Bazy danych – wykład 7
31
Projektowanie schematu relacji
Celem jest uniknięcie redundancji
informacji i związanych z nią anomalii:
anomalie aktualizacji : pewne wystąpienia
danej wartości są modyfikowane, ale nie
wszystkie
anomalie usuwania : pewne wartości zostają
utracone jako efekt uboczny usunięcia danej
krotki
Bazy danych – wykład 7
32
Przykład złego projektu
Drinkers(name, addr, beersLiked, manf, favBeer)
name
Janeway
Janeway
Spock
addr
Voyager
???
Enterprise
beersLiked
Bud
WickedAle
Bud
manf
A.B.
Pete’s
???
favBeer
WickedAle
???
Bud
Występuje redundancja danych: każde z ??? może być
wywnioskowane na podstawie zależności funkcyjnych
name addr favBeer oraz beersLiked manf
Bazy danych – wykład 7
33
Anomalie związane z redundancją
name
Janeway
Janeway
Spock
addr
Voyager
Voyager
Enterprise
beersLiked
Bud
WickedAle
Bud
manf
A.B.
Pete’s
A.B.
favBeer
WickedAle
WickedAle
Bud
• anomalia aktualizacji: jeśli Janeway zostanie przeniesiona na
Intrepid to trzeba pamiętać o zmianie wszystkich związanych z
nią krotek!
• anomalia usuwania: jeśli nikt w danej chwili (w danej
instancji relacji) nie lubi piwa Bud, to stracimy informację o tym,
że browar Anheuser-Busch jest producentem tej marki piwa
Bazy danych – wykład 7
34
Postać normalna Boyce’a-Codd’a
Powiemy, że relacja R (jej schemat) jest w
postaci normalnej Boyce’a-Codd’a (BCNF) jeśli
tylko takie nietrywialne zależności funkcyjne
X Y w niej obowiązują, w których X jest
nadkluczem
dla przypomnienia: nietrywialna zależność funkcyjna
X Y to taka, w której Y nie zawiera się w X
dla przypomnienia: nadklucz to dowolny nadzbiór
klucza
Bazy danych – wykład 7
35
Przykład
Drinkers(name, addr, beersLiked, manf, favBeer)
FD: nameaddr favBeer, beersLikedmanf
jedynym kluczem jest {name, beersLiked}
w każdej z FD lewa strona nie jest nadkluczem
tak więc każda z tych FD wskazuje, że relacja
Drinkers nie jest w postaci BCNF
Bazy danych – wykład 7
36
Inny przykład
Beers(name, manf, manfAddr)
FD: namemanf, manfmanfAddr
jedynym kluczem jest {name}
namemanf nie narusza warunków
postaci BCNF, ale manfmanfAddr
narusza
Bazy danych – wykład 7
37
Dekompozycja relacja do postaci BCNF
Wejście: relacja R, dla której obowiązuje zbiór
zależności funkcyjnych F
Znajdź wśród FD ∈ F takie X Y, dla których
występuje naruszenie własności BCNF.
Jeśli, któraś z FD wynikających z F narusza BCNF,
to na pewno wśród FD ∈ F jest taka zależność,
która sama narusza BCNF
Oblicz X
+
Musi być różne od zbioru wszystkich atrybutów, bo
w przeciwnym przypadku X byłby nadkluczem, a
skoro X Y, z założenia narusza BNCF, to X nie
jest nadkluczem
Bazy danych – wykład 7
38
Dekomponuj R z użyciem X Y
Zastąp R relacjami o schematach:
1. R1 = X +
2. R2 = R – (X
+
– X ).
Rzutuj zależności funkcyjne F na te
dwie nowe relacje
Bazy danych – wykład 7
39
Ilustracja dekompozycji
R1
R-X +
X
X +- X
R2
R
Bazy danych – wykład 7
40
Przykład:dekompozycja do postaci BCNF
Drinkers(name, addr, beersLiked, manf, favBeer)
F = nameaddr,
name favBeer,
beersLiked manf
Weźmy FD nameaddr naruszającą BNCF
Domknijmy lewą stronę:
{name}+ = {name, addr, favBeer}.
Relacje powstałe w wyniku dekompozycji:
1. Drinkers1(name, addr, favBeer)
2. Drinkers2(name, beersLiked, manf)
Bazy danych – wykład 7
41
Przykład: ciąg dalszy
To nie koniec – należy jeszcze zbadać czy
Drinkers1 i Drinkers2 sa w postaci BCNF
Rzutowanie zależności jest w tym
przypadku proste
Dla Drinkers1(name, addr, favBeer),
obowiązują zależności nameaddr i
namefavBeer.
{name} jest jedynym kluczem, a więc Drinkers1
jest w postaci BCNF
Bazy danych – wykład 7
42
Przykład: ciąg dalszy
Dla Drinkers2(name, beersLiked, manf),
jedyną zależnością funkcyjną jest
beersLikedmanf, i jedynym kluczem jest
{name, beersLiked}.
Naruszenie BCNF!
beersLiked+ = {beersLiked, manf}, a więc
dekomponujemy Drinkers2 do:
1. Drinkers3(beersLiked, manf)
2. Drinkers4(name, beersLiked)
Bazy danych – wykład 7
43
Przykład: konkluzja
Finalna dekompozycja relacji Drinkers :
1. Drinkers1(name, addr, favBeer)
2. Drinkers3(beersLiked, manf)
3. Drinkers4(name, beersLiked)
Zauważmy, że: Drinkers1 dotyczy klientów,
Drinkers3 dotyczy marek piwa, i Drinkers4
dotyczy powiązania pomiędzy klientami i
markami piwa, które lubią
Bazy danych – wykład 7
44
Trzecia postać normalna - motywacja
Dla pewnej kombinacji zależności
funkcyjnych występują problemy przy
dekompozycji
AB C i C B
Przykład: A = street address, B = city,
C = zip code.
Istnieją tu dwa klucze {A,B } i {A,C }
C B narusza BCNF, a więc musimy
dokonać dekompozycji do AC i BC
Bazy danych – wykład 7
45
Zależność, której nie możemy wymusić
street
zip
545 Tech Sq. 02138
545 Tech Sq. 02139
city
Cambridge
Cambridge
zip
02138
02139
Połączmy krotki o równych kodach pocztowych (zip)
street
city
545 Tech Sq. Cambridge
545 Tech Sq. Cambridge
zip
02138
02139
Chociaż żadna FD nie była naruszona w zdekomponowanych
relacjach, to FD street city zip jest naruszona w bazie jako
całości
Bazy danych – wykład 7
46
3NF: uniknięcie tego problemu
3rd Normal Form (3NF) stanowi modyfikację
wymogów BCNF tak, że nie musimy dokonywać
dekompozycji w tej problematycznej sytuacji
Atrybut nazwiemy kluczowym jeśli jest członkiem
jakiegokolwiek klucza
X A narusza 3NF wtedy i tylko wtedy gdy X
nie jest nadkluczem i jednocześnie A nie jest
atrybutem kluczowym
Bazy danych – wykład 7
47
Przykład: 3NF
Przy zależnościach funkcyjnych AB C i
C B, mamy klucze AB i AC
Tak więc każdy z atrybutów A, B, i C jest
kluczowy
Chociaż C B narusza BCNF, to nie narusza
3NF
Bazy danych – wykład 7
48
Jaka jest korzyść z 3NF i BCNF ?
Dwie ważne własności dekompozycji:
1. bezstratność informacji (ang. lossless join) :
możliwość odtworzenia oryginalnej relacji po
jej zdekomponowaniu
2. zachowanie zależności funkcyjnych (ang.
dependency preservation ): powinno być
możliwe wyrażenie oryginalnych zależności
funkcyjnych w zdekomponowanych relacjach
Bazy danych – wykład 7
49
3NF i BCNF: ciąg dalszy
Własność 1. zawsze obowiązuje dla
dekompozycji opartej na BCNF , ale własność
2. nie zawsze obowiązuje !
Własności 1. i 2.obowiązują dla dekompozycji
opartej 3NF
Bazy danych – wykład 7
50
Bezstratność dekompozycji
Czy po wykonaniu rzutu relacji R na relacje
R1, R2,…, Rk , możemy odtworzyć R złączając
R1, R2,…, Rk ?
Każdą krotkę można odtworzyć z jej
fragmentów powstałych w wyniku rzutów.
Pozostaje jednak wątpliwość: czy przy
złączaniu nie dostaniemy czasami krotek,
które nie występują w dekomponowanej
relacji ?
Bazy danych – wykład 7
51
Sprawdzenie bezstratności
dekompozycji
Przyjmijmy, że krotka t występuje w
relacji będącej wynikiem złączenia relacji
powstałych w wyniku dekompozycji relacji
R
Biorąc pod uwagę zależności funkcyjne
obowiązujące w R chcemy pokazać, że t
musiała należeć do R
Bazy danych – wykład 7
52
Sprawdzenie bezstratności
dekompozycji
Niech t = abc… .
Dla każdego i istnieje krotka si należąca do R ,
która ma wartości a, b, c,… dla atrybutów relacji
Ri - bo t jest wynikiem złączenia krotek z Ri
si może mieć dowolne inne wartości pozostałych
atrybutów – będziemy je oznaczali jak wartości
tych atrybutów w krotce t, ale z odpowiednimi
indeksami
(np. a1 zamiast a )
Bazy danych – wykład 7
53
Krotki relacji R, które po
zrzutowaniu na AB, BC, CD
dały następnie po złączeniu t
A
a
a2 a
a3
B
b
b
b3
C
c1
c
c
D
d1
d2 d
d
na podstawie CD
na podstawie B A
Tak więc, drugą krotką musi
być t
Bazy danych – wykład 7
54
Sprawdzenie bezstratności
dekompozycji: przykład
Niech R = ABCD, będzie zdekomponowana
na trzy relacje o schematach AB, BC, i CD
Niech w R obowiązują następujące
zależności funkcyjne C D i B A
Niech krotka t = abcd należy do wyniku
złączenia relacji AB, BC, CD.
Bazy danych – wykład 7
55
Sprawdzanie bezstratności:
podsumowanie
1.
2.
3.
4.
Jeśli dwa wiersze mają identyczne wartości atrybutów
występujących po lewej stronie pewnej zależności
funkcyjnej, to uczyń identycznymi również wartości
atrybutów występujących po prawej stronie tej zależności
Zawsze zastępuj symbol z indeksem symbolem bez
indeksu, o ile to możliwe.
Jeśli otrzymasz wiersz z wartościami bez indeksów, to
dowodzi że każda krotka uzyskana w sekwencji operacji
rzut-złączenie występuje w dekomponowanej relacji
(dekompozycja jest bezstratna).
W przeciwnym przypadku, uzyskany zestaw krotek stanowi
kontrprzykład na bezstratność
Bazy danych – wykład 7
56
Przykład dekompozycji stratnej
Rozważmy tę samą relację R = ABCD i
tę samą jej dekompozycję na trzy
relacje o schematach AB, BC, i CD
Ale tym razem załóżmy tylko jedną
zależność funkcyjną: C D
Bazy danych – wykład 7
57
Krotki relacji R, które po
zrzutowaniu na AB, BC, CD
dały następnie po złączeniu t
A
a
a2
a3
B
b
b
b3
C
c1
c
c
Te trzy krotki stanowią
kontrprzykład na bezstratność
dekompozycji: stanowią przykład
instancji relacji R, w której nie
występuje krotka abcd, ale po rzucie
i złączeniu pojawia się ona
w relacji
Bazy danych
– wykład 7
wynikowej
D
d1
d2 d
d
na podstawie C D
58
Algorytm normalizacji do 3NF
Zawsze można zdekomponować relację do
trzeciej postaci normalnej (3NF) w sposób
bezstratny i zachowujący zależności funkcyjne
Należy w tym celu najpierw sprowadzić zbiór
zależności funkcyjnych do takiej równoważnej
minimalnej postaci
(znaleźć minimalne pokrycie), że:
1. prawe strony są pojedynczymi atrybutami
2. żadna z zależności nie może być usunięta
3. żaden z atrybutów nie może być usunięty z lewej
strony zależności
Bazy danych – wykład 7
59
Określanie minimalnego pokrycia FD
1. Rozszczepić zależności z wieloma
atrybutami po prawej stronie na wiele
zależności z jednym atrybutem po prawej
stronie
2. Próbować usunąć kolejne FD i sprawdzać
czy zbiór pozostałych FD jest równoważny
pierwotnemu zbiorowi FD
3. Próbować usuwać kolejne atrybuty z lewej
strony poszczególnych FD i sprawdzać czy
uzyskany zbiór FD jest równoważny
oryginalnemu.
Bazy danych – wykład 7
60
Algorytm normalizacji do 3NF (2)
Utworzyć oddzielną relację dla każdej z
tak uzyskanych zależności funkcyjnych.
Schematem takiej relacji jest suma
atrybutów występujących po lewej i prawej
stronie zależności
Jeśli nie ma klucza wśród zależności
funkcyjnych, to dodać jedną relację,
której schematem jest jakiś klucz.
Bazy danych – wykład 7
61
Algorytm normalizacji do 3NF: przykład
Relacja R = ABCD
Zależności funkcyjne: A B i A C
Dekompozycja: AB i AC na podstawie
zależności funkcyjnych oraz AD jako
klucz
Bazy danych – wykład 7
62