Bazy danych

1. Bazy danych 05: Normalizacja Krzysztof Stencel

2. Normalizacja = Sprowadzenie tabel do odpowiedniej postaci normalnej • Sprowadzenie dokonuje się poprzez podział tabel na mniejsze części: • R = kol(r), podziałem nazwiemy ciąg zbiorów P1,P2,…,Pn, taki że: P1  P2  …  Pn= R • Nie jest i (nie może być!) rozłączny ze względu na zachowanie informacji.

3. Zachowywanie informacji • Podział P1,P2,…,Pn schematu R,  zachowuje informacje wtw. dla każdej relacji r o schemacie R,  zachodzi warunek: P1 (r) ⋈P2 (r) ⋈… ⋈ Pn(r) = r • Warunek konieczny sensowności normalizacji. • Można z niego zrezygnować, gdy w tabeli połączono niezwiązane ze sobą dane.

4. Zachowywanie zależności funkcyjnych • ↾P to podzbiór  zawierający wszystkie zależności funkcyjne, które nie zawierają odwołania do kolumn spoza P • Podział P1,P2,…,Pn schematu R,  zachowuje zależności funkcyjne wtw. (*↾P1  *↾P2  … *↾Pn)* = * • Dobrze jest zachować zależności funkcyjne (bo stanowią semantykę danych), ale nie jest to konieczne.

5. Właściwości normalizacji 3NF iBCNF • Każdy schemat można sprowadzić do 3NF z zachowaniem zależności funkcyjnych i informacji • Każdy schemat można sprowadzić do BCNF z zachowaniem informacji • Przejście od 3NF do BCNF nie usuwa żadnych anomalii • Za to jest prostsze

6. Tabela w 3NF bez „taniego” przejścia do BCNF • R = { Kino, Miasto, Film } • Kino  Miasto • Miasto, Film  Kino • Klucze: { Kino, Film }, { Miasto, Film } • Wszystkie atrybuty są kluczowe, więc 3NF • Nie da się podzielić (przejść do BCNF) bez utraty drugiej zależności funkcyjnej • Takie tabele są na szczęście rzadkie

7. Normalizacja BCNF • Weź zależność funkcyjną X  Y (XY = ), która powoduje „problemy” i podziel tabelę R na dwie części: • X  Y • R – Y • Rób tak aż do chwili, w której wszystkie fragmenty są BCNF • Informacje są zachowane, bo obie te tabele złączone naturalnie dają wyjściową tabelę (klejem jest X występujący w obu tabelach)

8. Przykład normalizacji BCNF • R={Wykładowca,Krwawość,Wykład,Trudność,Termin} • Wykładowca  Krwawość • Wykład  Trudność • Wykładowca,Wykład  Termin • Krok 1: Wykładowca  Krwawość • R1 = { Wykładowca, Krwawość } • R2 = { Wykładowca, Wykład, Trudność, Termin } • Krok 2: Wykład  Trudność • R21 = { Wykład, Trudność } • R22 = { Wykładowca, Wykład, Termin }

9. Wynik normalizacji BCNF • Podział schematu R = {Wykładowca,Krwawość,Wykład,Trudność,Termin} • R1 = { Wykładowca, Krwawość } • R21 = { Wykład, Trudność } • R22 = { Wykładowca, Wykład, Termin } • Zachowano też zależności funkcyjne (widać!) • Każda powstała tabelka jest już w BCNF • Zależności funkcyjne w niej obowiązujące mają reprezentację złożoną z jednej z.f. z wszystkimi kolumnami • Przy normalizacji BCNF konieczna jest staranność, żeby nie „zrywać” zależności funkcyjnych.

10. Minimalna reprezentacja Zbiór z.f.  to minimalna reprezentacja zbioru z.f.  wtw. • Dla każdej z.f. X  Y   zbiór Y ma jeden element • Dla każdej z.f. X  Y  ,  ⊢ X  Y • Jeśli X  A  , to nie jest prawdą, że  – {X  A} ⊢ X  A • Jeśli XZ  A   i Z , to nie jest prawdą, że  ⊢ X  A • Minimalna reprezentacja jest potrzebna do normalizacji 3NF, bo ta polega na wygenerowaniu tabeli dla każdej zależności funkcyjnej

11. Normalizacja 3NF • Mając dany schemat R,  wyznacz jego minimalną reprezentację . • Dla każdej z.f. X  A  , utwórz tabelę o kolumnach X  A • Dla zachowania zależności funkcyjnych • Wybierz K – jeden z kluczy schematu R,  i utwórz tabelę o kolumnach K • Dla zachowania informacji

12. Przykład normalizacji 3NF • R={ Wykładowca,Krwawość,Wykład,Trudność } • Wykładowca  Krwawość • Wykład  Trudność • Jest to też minimalna reprezentacja • Krok 1: tabela dla każdej zależności funkcyjnej • R1 = { Wykładowca, Krwawość } • R2 = { Wykład, Trudność } • Krok 2: Tabela dla jednego z kluczy • R3 = { Wykład, Wykładowca } • Bez kroku 2 nie zachowujemy informacji o tym, kto prowadzi wykłady

13. Normalizacja 3NF: Optymalizacja 1 • Usuń każdą tabelę, której zbiór kolumn jest podzbiorem zbioru kolumn jakiejś innej tabeli • R={ Wykładowca,Krwawość,Wykład,Trudność,Termin } • Wykładowca  Krwawość • Wykład  Trudność • Wykład, Wykładowca  Termin • R1 = { Wykładowca, Krwawość } (z.f.) • R2 = { Wykład, Trudność } (z.f.) • R3 = { Wykład, Wykładowca, Termin } (z.f.) • R4 = { Wykład, Wykładowca } (klucz)

14. Normalizacja 3NF: Optymalizacja 2 • Połącz tabelę pochodzące od zależności funkcyjnych z tym samymi lewymi stronami • R={ Wykładowca,Krwawość,Zasięg,Wykład,Trudność } • Wykładowca  Krwawość • Wykładowca  Zasięg • Wykład  Trudność • R1 = { Wykładowca, Krwawość } (z.f.) • R2 = { Wykładowca, Zasięg } (z.f.) • R3 = { Wykład, Trudność } (z.f.) • R4 = { Wykład, Wykładowca } (klucz) • R12 = { Wykładowca, Krwawość, Zasięg } (z.f.)

15. Optymalizacja 2 jest poprawna • Weźmy tabele o schemacie R = { A, B, C, … } i minimalnej reprezentacji zawierającej • A  B • A  C • Czy { A, B, C } może nie być 3NF? Wtedy musiałoby być, np. B  C i też jakoś musiałoby wynikać z minimalnej reprezentacji • Ale wtedy A  C i A  B nie mogą być razem w minimalnej reprezentacji • Porządny dowód: przez indukcję

16. Inne redundancje

17. To było BCNF • Nie ma tu żadnej nietrywialnej zależności funkcyjnej • Klucz składa się ze wszystkich kolumn • Generują się jednak lokalnie iloczyny kartezjańskie na kolumnach Lubi i Uprawia • Takie redundancje występują jednak niezwykle rzadko i zdarza się to tylko wyjątkowo przemęczonym projektantom

18. Zależność wielowartościowa • X, Y  R – podzbiory kolumn • Oznaczenie Z := R – X – Y • Zależność wielowartościowa X ↠Y zachodzi w relacji r wtedy i tylko wtedy, gdy  t, u  r : t↾X=u↾X   w  r (w↾X=t↾X  w↾Y=t↾Y  w↾Z=u↾Z)

19. X ↠Y – wyjaśnienie  t, u  r : t↾X=u↾X   w  r (w↾X=t↾X  w↾Y=t↾Y  w↾Z=u↾Z) t : XXXXXX YYYYYYZZZZZZ u : XXXXXX YYYYYYZZZZZZ  w : XXXXXX YYYYYYZZZZZZ

20. Zależność funkcyjna jest też wielowartościowa • Zakładamy, że zachodzi X  Y (X, Y  R) • Weźmy dwie krotki t i u, takie, że t↾X=u↾X t : XXXXXX YYYYYYZZZZZZ u : XXXXXX YYYYYYZZZZZZ • Czy istnieje w? w: XXXXXX YYYYYYZZZZZZ • Tak, jest nią u • Bo przecież XY, a więc YYYYYY = YYYYYY YYYYYY

21. Czwarta postać normalna (4NF) • Schemat jest 4NF, wtw. jest 1NF oraz każda zależność wielowartościowa X↠A jest również zależnością funkcyjną od nadklucza • Normalizacja jak do BCNF: • Weź z.w. X ↠Y (XY = ), która powoduje „problemy” i podziel tabelę R na dwie części: • X  Y • R – Y

22. W 4NF też mogą być redundancje • Schemat R = { Projekt, Towar, Dostawca } • Jeśli: • Dostawca D dostarcza towar T dla jakiegoś projektu • Dostawca D dostarcza jakiś towar dla projektu P • Jakiś dostawca dostarcza towar T dla projektu P • To: • Dostawca D dostarcza towar T dla projektu P

23. Bazowe fakty • Fakty biznesowe sklejone w tej tabeli to • Dostawca D jest w stanie dostarczyć towar T • Dostawca D pracuje dla projektu P • Projekcie P zużywa towar T ParysJabłka Eris Parys Wiśnie Afrodyta Hektor JabłkaAfrodyta Parys Jabłka Afrodyta Wiersz nadmiarowy

24. Zależność złączeniowa • P1,P2,…,Pn – podział R • Zależność złączeniowa ⋈(P1,P2,…,Pn) zachodzi w relacji r wtedy i tylko wtedy, gdy spełniony jest warunek: P1 (r) ⋈P2 (r) ⋈… ⋈ Pn(r) = r • Tzn. istnienie zależności złączeniowej jest równoważne z zachowywaniem informacji przez podział

25. Piąta postać normalna (5NF) • Schemat jest 5NF, wtw. jest 1NF oraz każda zależność złączeniowa ⋈(P1,P2,…,Pn) jest indukowana przez nadklucz, tj. P1  P2  …  Pnjest nadkluczem • Jeśli tabela jest 5NF, to żaden jej podział zachowujący informacje nie ma sensu (bo i tak w każdym fragmencie będzie ten sam klucz)

26. Normalizacja 5NF • Jeśli ⋈(P1,P2,…,Pn) nie jest indukowana przez nadklucz, to podziel R na P1,P2,…,Pn . • 5NF jest ostateczną postacią normalną • Dalej już się normalizować się nie da (gdy zakładamy normalizację przez podział!)

27. Przykład zależności złączeniowej i normalizacji • Schemat R = { Projekt, Towar, Dostawca } ⋈( { Projekt, Towar }, { Projekt, Dostawca }, { Towar, Dostawca } ) • Podziel R na: { Projekt, Towar }, { Projekt, Dostawca }, { Towar, Dostawca }

28. Obiecanka relacyjna (przełożył Lech Banachowski) Bez powtórzeń, dane zależą od klucza, od całego klucza i tylko od klucza. Tak mi dopomóż Codd! • The Key, the whole Key, and nothing but the Key, so help me Codd.