Bazy danych 3 Zalenoci funkcyjne Postaci normalne P

Bazy danych 3. Zależności funkcyjne Postaci normalne P. F. Góra http: //th-www. if. uj. edu. pl/zfs/gora/ semestr letni 2007/08 Bazy danych - wykład 3

• W bardzo sformalizowanych wykładach baz danych jest to jedno z najważniejszych zagadnień. • W wykładach (kursach) pisania kodu w SQL jest to zagadnienie całkowicie pomijane. • Proste (kilkutabelowe) bazy można z powodzeniem projektować (i optymalizować) bez znajomości tego tematu. Złożonych baz bez tego zaprojektować się praktycznie da. Bazy danych - wykład 3 2

Definicja zależności funkcyjnej Jeśli dwie krotki pewnej tabeli (relacji) są zgodne w atrybutach A 1, A 2, …An, to muszą być zgodne w pewnym innym atrybucie B. Zapisujemy to A 1, A 2, …An B Bazy danych - wykład 3 3

A B Bazy danych - wykład 3 4

PESEL Nazwisko Bazy danych - wykład 3 5

• Zależności funkcyjne są matematycznym modelem więzów jednoznaczności w modelu relacyjnym baz danych. • Zależności funkcyjne należą do schematu bazy. Nie można o nich wnioskować jedynie na podstawie instancji (faktycznych wystąpień) tabel. Jeżeli zachodzi A 1, A 2, …, An B 1 , …, A 1, A 2 , …, An Bm piszemy w skrócie A 1, A 2 , …, An B 1 , …, Bm Bazy danych - wykład 3 6

Przykład W używanym w podręczniku przykładzie tabeli Filmy występują następujące zależności funkcyjne: tytuł rok długość tytuł rok Typ. Filmu tytuł rok Nazwa. Studia Jednak na przykład zdanie tytuł rok Nazwisko. Gwiazdy jest fałszywe: w jedym filmie, identyfikowanym przez zbiór atrybutów {tytuł, rok}, może występować wiele gwiazd. Bazy danych - wykład 3 7

Reguły dotyczące zależności funkcyjnych Przykład: Załóżmy, że w pewnej tabeli z atrybutami A, B, C zachodzą zależności funkcyjne A B, B C. Wnioskujemy sąd, iż zachodz także A C. Dowód: Niech dwie krotki zgodne dla A mają postać (a, b 1, c 1), (a, b 2, c 2). Ponieważ zachodzi A B, krotki zgodne dla A muszą być też zgodne dla B, a zatem b 1 = b 2. Krotki mają więc postać (a, b, c 1), (a, b, c 2). Ponieważ zachodzi B C, musi być spełnione c 1 = c 2, co kończy dowód. Jest to przykład reguły przechodniości. Bazy danych - wykład 3 8

Zależności trywialne • Zależność nazywam trywialną, jeśli atrybut B jest równy któremuś atrybutowi A. • Jeśli przyjmiemy „skrótowy” zapis zależności z wieloczłonową prawą stroną, zależność jest – Trywialna, jeśli zbiór złożony z atrybutów B jest podzbiorem zbioru złożonego z atrybutów A – Nietrywialna, jeśli co najmniej jeden B nie jest A – Całkowicie nietrywialna, jeśli żadnen B nie jest A. Bazy danych - wykład 3 9

Zależności trywialne Reguły wnioskowania (aksjomaty Armstronga) 1. Zwrotność: Jeżeli {B 1, B 2, …, Bm} {A 1, A 2, …An}, to A 1 A 2…An B 1 B 2…Bm 2. Rozszerzenie: Jeżeli A 1 A 2…An B 1 B 2…Bm, to A 1 A 2…An. C 1 C 2…Ck B 1 B 2…Bm. C 1 C 2…Ck dla dowolnych C 1 C 2…Ck. 3. Przechodniość: Jeżeli A 1 A 2…An B 1 B 2…Bm oraz B 1 B 2…Bm C 1 C 2…Ck, to A 1 A 2…An C 1 C 2…Ck. Bazy danych - wykład 3 10

Domknięcia Niech {A 1, A 2, …An} będzie pewnym zbiorem atrybutów, S niech będzie zbiorem zależności funkcyjnych. Domknięciem zbioru {A 1, A 2, …An} nad S nazywamy taki zbiór atrybutów B, że jeśli jego elementy spełniają wszystkie zależności funkcyjne z S, to spełniają także zależność A 1 A 2…An B, a zatem zależność A 1 A 2…An B wynika z S. Domknięcie oznaczam cl {A 1, A 2, …An}. Bazy danych - wykład 3 11

Mówiąc niezbyt ściśle, domknięcie to zbiór wszystkich atrybutów determinowanych, w sensie zależności funkcyjnych, przez atrybuty zbioru wyjściowego. Ściśle było na poprzednim ekranie Bazy danych - wykład 3 12

Algorytm obliczania domknięcia 1. 2. 3. 4. Na początku X oznacza zbiór {A 1, A 2, …, An}. Znajdujemy wszystkie zależności funkcyjne postaci B 1 B 2…Bm C, gdzie Bi należą do X, a C nie należy. Dołączamy C do X. Powtarzamy krok 2 tak długo, jak długo do X nie można dołączyć żadnego nowego atrybutu. Ponieważ X może się tylko rozszerzać, zaś zbiór atrybutów jest skończony, po skończonej ilości kroków nastąpi moment, w którym do X nie da się niczego dołączyć. X=cl {A 1, A 2, …, An}. Bazy danych - wykład 3 13

Przykład (najprostszy) Dane mamy atrybuty A, B, C, D i zbiór zależności funkcyjnych (zbiór S) następującej postaci: B C, AB D. Ile wynosi cl({B}) nad podanym zbiorem zależności funkcyjnych? Na początku X ={B}. Na podstawie zależności B C, do zbioru X dołączam atrybut C. X={B, C}. W podanym zbiorze zależności funkcyjnych nie ma żadnej innej zależności, której poprzednik obejmowałby elementy zbioru X, a więc nie ma sposobu, aby do X coś jeszcze dołączyć. Ostatecznie cl({B})={B, C}. Bazy danych - wykład 3 14

Caveat emptor! Powyżej przedstawiony algorytm jest poprawny i intuicyjnie prosty, ale nie jest efektywny – może być algorytmem kwadratowym (w czasie) w najgorszym przypadku. Znacznie rozsądniej jest tak uporządkować zależności funkcyjne, aby każda była używana („odpalana”) dokładnie w tym momencie, w którym wszystkie atrybuty jej lewej strony znajdą się w „kandydacie” X. Dla dużych baz (i tabel) domknięć nie oblicza się ręcznie, ale komputerowo! Bazy danych - wykład 3 15

Przykład: Rozważmy zbiór atrybutów {A, B, C, D, E, F}. Załóżmy, że w tym zbiorze zachodzą zależności AB C, BC AD, D E, CF B. Obliczmy cl{A, B}. X={A, B}. Wszystkie atrybuty zależności AB C są w X, więc do X dołączamy C. X={A, B, C}. Lewa strona zależności BC AD jest w X, A jest już w X, więc do X dołączamy D. X={A, B, C, D}. Na mocy zależności D E, dołączamy do X atrybut E. X={A, B, C, D, E}. Zleżności CF B nie możemy zastosować, ponieważ F X i nie ma jak F do X dołożyć. Ostatecznie cl{A, B} = {A, B, C, D, E}. Bazy danych - wykład 3 16

Bazy relacji Każdy zbiór zależności funkcyjnych pewnego zbioru atrybutów, z którego można wyprowadzić wszystkie inne zależności funkcyjne zachodzące pomiędzy elementami tego zbioru, nazywam bazą relacji. Jeśli żaden podzbiór bazy nie jest bazą (nie umożliwia wyprowadzenia wszystkich relacji), bazę tę nazywam bazą minimalną. Przykład: Mam atrybuty A, B, C i zależności A B, A C, B A, B C, C A, C B. Można teraz wyprowadzić zależności nietrywialne AB C, AC B, BC A (oraz zależności trywialne). Bazą minimalną jest zbiór {A B, B A, B C, C B} Inną bazą minimalną jest {A B, B C, C A} Bazy danych - wykład 3 17

Po co jemy tę żabę? ! Bazy danych - wykład 3 18

Klucze relacji (tabeli) Mówimy, że zbiór atrybutów {A 1, A 2, …, An} tworzy klucz tabeli, jeśli 1. Wszystkie pozostałe atrybuty są funkcyjnie zależne od tych atrybutów, a zatem dwie różne krotki nie mogą być zgodne dla atrybutów A 1, A 2, …, An. 2. Nie istnieje taki podzbiów właściwy zbioru {A 1, A 2, …, An} , od którego pozostałe atrybuty są zależne funkcyjnie. Bazy danych - wykład 3 19

Nadzbiór klucza nazywa się nadkluczem. Terminologia alternatywna: „Nadklucz” nazywa się kluczem. To, co na poprzednim ekranie było nazwane „kluczem”, nazywa się „kluczem minimalnym”. Bazy danych - wykład 3 20

Zauważmy, że zbiór cl{A 1, A 2, …, An} zawiera wszystkie atrybuty pewnej tabeli R wtedy i tylko wtedy, gdy elementy A 1, A 2, …, An są nadkluczem R. Stwierdzenie, czy zbiór {A 1, A 2, …, An} stanowi klucz tabeli (klucz minimalny w terminologii alternatywnej), polega na 1. Sprawdzeniu, czy wszystkie atrybuty R należą do cl {A 1, A 2, …, An}. 2. Sprawdzenie, czy domknięcie zbioru powstałego przez usunięcie choćby jednego elementu Ai nie zawiera wszystkich atrybutów R. Bazy danych - wykład 3 21

„Żaba” służy zatem jako formalne narzędzie do identyfikowania kluczy. Identyfikacja kluczy jest elementem niezbędnym przy normalizacji bazy danych. Bez formalnego zastosowania mechanizmu relacji funkcyjnych i domknięć, normalizacja dużych baz jest praktycznie niemożliwa. Bazy danych - wykład 3 22

Cel normalizacji baz danych – unikanie anomalii Rodzaje anomalii Te same dane niepotrzebnie powtarzają się w kilku krotkach 1. Redundancja 2. Anomalie modyfikacji 3. Anomalie usuwania Odwrotność: anomalie dołączania Wartość tej samej danej zostanie zmodyfikowana w jednej krotce, w innej zaś nie. Która wartość jest wówczas poprawna? Utrata części danych, gdy dla pewnego atrybutu zacznie obowiązywać wartość pusta (null) Bazy danych - wykład 3 23

Pierwsza postać normalna: Tabele zawierają wyłącznie dane atomowe. Każda tabela ma klucz. Zakaz duplikowania wierszy. Tabela jest zbiorem wierszy. Bazy danych - wykład 3 24

Problem: Jednemu numerowi zamówienia odpowiada zbiór identyfikatorów części Bazy danych - wykład 3 25

Tabela w 1 PN — „podzielone” niektóre wiersze. Bazy danych - wykład 3 26

Nie jest w 1 PN Jest w 1 PN — podzielone niektóre kolumny Bazy danych - wykład 3 27

Druga postać normalna: Tabela jest w 1 PN. Każdy atrybut niekluczowy zależy funkcyjnie od pełnego klucza. • Od pełnego klucza, a nie tylko od podzbioru właściwego klucza. • Każda tabela, której wszystkie klucze są jednokolumnowe, jest w 2 PN. Kontynuujemy konsumpcję płazów Bazy danych - wykład 3 28

Nie jest w 2 PN: nazwa dostawcy adres, id dostawcy; nazwa części id części Anomalie: 1. Redundancja: adres Seagate w czterech (sic!) krotkach. 2. Anomalia modyfikacji: Jeśli Seagate zmieni siedzibę, czy na pewno poprawimy to we wszystkich miejscach? 3. Anomalia usuwania: Jeśli usuniemy zamówienie nr 002, stracimy informacjęo siedzibie Toshiby. 4. Anomalia dołączania: Nie da się dołączyć nowej firmy, dopóki nie złożymy w niej zamówienia. Bazy danych - wykład 3 29

Sprowadzenie do 2 PN: podział tabeli Bazy danych - wykład 3 30

Trzecia postać normalna: Tabela jest w 2 PN. Jeśli A 1 A 2…An B, to albo {A 1, A 2, …, An} jest nadkluczem, albo B jest elementem pewnego klucza. Dotyczy tylko zależności nietrywialnych! Bazy danych - wykład 3 31

Trzecia postać normalna jest postacią najczęściej występującą w praktyce. Istnieją specjalne algorytmy i specjalne narzędzia CASE do „przeprojektowywania” bazy do 3 PN. W specjalistycznych zastosowaniach (np. hurtownie danych) niekiedy korzystnie jest pozostawić bazę w 2 PN lub nawet 1 PN. Bazy danych - wykład 3 32

Często mówi się, że 3 PN wyklucza zależności przechodnie (typu A B, B C, zatem A C). Jeśli jednak C jest elementem klucza (być może obejmującego jakieś atrybuty A), to dalej jest 3 PN. Przykład: Tabela z trzema atrybutami i zależnościami Bo każde kino jest w jakimś mieście. kino miasto tytuł miasto kino Kluczem jest {tytuł, miasto}. Innym kluczem jest {kino, tytuł}. Tabela jest w 3 PN: chociaż samo kino nie jest nadkluczem, miasto jest elementem klucza. Zwanego „kluczem kandydującym”. Bo dystrybutor ogranicza rozpowszechnianie do jednego kina w danym mieście. Bazy danych - wykład 3 33

Procedura postępowania: 1. 2. 3. Dany jest zbiór atrybutów, które chcemy reprezentować, i zbiór zależności funkcyjnych pomiędzy atrybutami. Znajdujemy bazę minimalną (bazy minimalne) zbioru zależności funkcyjnych. 1. Eliminujemy symetryczne zależności funkcyjne. Dla (pewnej) bazy minimalnej sumujemy zależności funkcyjne o takich samych lewych stronach i dla każdej wysumowanej zależności tworzymy tabelę z odpowiednią lewą stroną zależności jako kluczem. Bazy danych - wykład 3 34

Procedura szukania bazy minimalnej: 1. 2. 3. Każdy atrybut musi występować z lewej lub z prawej strony jednej zależności funkcyjnej w zbiorze. Jeśli jakaś zależność funkcyjna jest włączona do zbioru, nie wszystkie zależności funkcyjne potrzebne do jej wyprowadzenia mogą występować w tym zbiorze. Jeśli jakaś zależność funkcyjna zostaje wyłączona ze zbioru, zależności funkcyjne potrzebne do jej wyprowadzenia muszą zostać doń dołączone. Bazy danych - wykład 3 35

Jeśli mamy wiele atrybutów i wiele zależności, normalizacji „ręcznie” przeprowadzić się nie da. Powyższa procedura jest dostosowana do projektowania „od zera”. Jeśli mamy wstępny projekt w postaci diagramu E/R, projektowanie jest znacznie prostsze. Bazy danych - wykład 3 36

Przykład Firma transportowa posiada bazę danych, w której zachodzą następujące zależności funkcyjne: Nr. Rejestracyjny Data Kierowca Nr. Rejestracyjny Data Odbiorca Odległość Kierowca Stawka Odległość Stawka Koszt Odbiorca Kierowca Koszt Nr. Rejestracyjny Data Koszt Bazy danych - wykład 3 37

Tabele: (Nr. Rejestracyjny, Data, Kierowca, Odbiorca) (Kierowca, Stawka) (Odbiorca, Odległość) (Odległość, Stawka, Koszt) Bazy danych - wykład 3 38

Postać normalna Boyce’a-Coda (BCNF, PNBC) Tabela jest w BCNF wtedy i tylko wtedy, gdy dla każdej zależności nietrywialnej A 1 A 2…An B, zbiór {A 1, A 2, …, An} jest nadkluczem Bazy danych - wykład 3 39

Używa się także wyższych postaci normalnych (czwartej, piątej i szóstej), ale nie będą one omawiane na tym wykładzie. Wyższe postaci normalne są potrzebne, ale do bardzo wielu zastosowań praktycznych postaci trzecia i BCNF wystarczają. Niekiedy wygodniej jest używać niższych postaci normalnych, drugiej i pierwszej. Bazy danych - wykład 3 40

Bezstratne złączenia Normalizacja ma na celu unikanie redundancji i innych anomalii. Trzeba jednak być ostrożnym: Powstające tabele muszą zapewniać bezstratność złączeń – tak, aby informacja nie została utracona. Bazy danych - wykład 3 41

Dekompozycję tabeli R na tabele R 1, R 2, …, Rn nazywamy dekompozycją bezstratnego złączenia (ze względu na pewien zbiór relacji funkcyjnych), jeśli naturalne złączenie R 1, R 2, …, Rn jest równe tabeli R. Formalną definicję złączenia poznamy później Bazy danych - wykład 3 42

Dekompozycja tabeli R na dwie tabele R 1, R 2 jest dekompozycją bezstratnego złączenia, jeśli spełniony jest jeden z dwu warunków: (R 1 R 2) (R 1 -R 2) lub (R 1 R 2) (R 2 -R 1) Innymi słowy, wspólna część atrybutów R 1, R 2 musi zawierać klucz kandydujący R 1 lub R 2 Bazy danych - wykład 3 43

Przykład negatywny Dana jest tabela Zapisy(Nr. Studenta, Nr. Kursu, Data. Zapisu, Sala, Prowadzący) Przykładowa instancja może wyglądać tak: Nr. Studenta Nr. Kursu Data. Zapisu Sala Prowadzący 830057 K 202 1. 10. 05 A 1 Góra 830057 K 203 1. 10. 05 A 1 Oramus 820159 K 202 1. 02. 06 A 1 Góra 825678 K 204 1. 10. 05 C 1 Oramus 826789 K 205 12. 06 C 2 Bogacz Bazy danych - wykład 3 44

Rozbijamy tabelę Zapisy na dwie tabele: Z 1(Nr. Studenta, Nr. Kursu, Data. Zapisu) Z 2(Data. Zapisu, Sala, Prowadzący) To jest dość kiepski podział: Data. Zapisu nie jest kluczem kandydującym Instancje tych tabel wyglądają tak: Nr. Studenta Nr. Kursu Data. Zapisu Sala Prowadzący 830057 K 202 1. 10. 05 A 1 Góra 830057 K 203 1. 10. 05 A 1 Oramus 820159 K 202 1. 02. 06 A 1 Góra 825678 K 204 1. 10. 05 C 1 Oramus 826789 K 205 12. 02. 06 C 2 Bogacz Bazy danych - wykład 3 45

Chcemy się dowiedzieć jakich studentów naucza Góra. W tym celu robimy złączenie Z 1 Z 2 Wspólną kolumną obu tabel jest Data. Zapisu Dostajemy Nr. Studenta Nr. Kursu Data. Zapisu Sala Prowadzący 830057 K 202 1. 10. 05 A 1 Góra 830057 K 203 1. 10. 05 A 1 Góra 825678 K 204 1. 10. 05 C 1 Góra etc Zaznaczone krotki są fałszywe – nie występują w pierwotnej tabeli. Zaproponowana dekompozycja spowodowała, że po wykonaniu złączenia tracimy informację o tym, kto kogo naucza. Bazy danych - wykład 3 46

Uwaga! Przeprowadzając normalizację, staramy się zachować trzy warunki: 1. BCNF 2. Zachowanie zależności funkcyjnych 3. Bezstratne złączenia Czy zawsze da się to zrobić? Bazy danych - wykład 3 47

Rozważmy tabelę T(A, B, C) z następującymi relacjami funkcyjnymi: C A AB C Tabela ta nie jest w postaci BCNF, gdyż C nie jest nadkluczem. Na przykład: A – nazwa banku B – nazwisko klienta C – nazwisko bankiera Bazy danych - wykład 3 48

Próbujemy następującej dekompozycji: T 1(A, C) T 2(B, C) W naszym przykładzie byłoby to T 1(Nazwa. Banku, Nazwisko. Bankiera) T 2(Nazwisko. Klienta, Nazwisko. Bankiera) Ta dekompozycja nie zachowuje relacji AB C i nie jest dekompozycją bezstratnego złączenia Bazy danych - wykład 3 49

Jak widać, nie zawsze udaje się przeprowadzić normalizację do postaci BCNF tak, aby zachować komplet relacji funkcyjnych i aby dekompozycja pozwalała na bezstratne złączenia. W takiej sytuacji rezygnujemy z postaci BCNF, zadowalając się jakąś „niższą” postacią normalną i dopuszczając pewne anomalie. Zachowanie kompletu zależności funkcyjnych oraz bezstratność złączeń muszą mieć priorytet! Bazy danych - wykład 3 50