Normalizacja

Wykład 4Prowadzący: dr Paweł Drozda

dr Paweł Drozda

Pojęcie normalizacji Reguły projektowania relacyjnych baz

danych Dekompozycja schematu relacji w celu

pozbycia się niepożądanych cech Pierwotnie – trzy postacie normalne

wprowadzone przez Codda Później - postać normalna Boyce’a –

Codda, czwarta i piąta postać normalna wprowadzona przez Fagina

dr Paweł Drozda

Po co normalizować? (1)

Student

nrindeksu nazwisko adres przedmiot ocena

127000 Maliniak Świerkowa 6 Analiza 2

127000 Maliniak Świerkowa 6 Algebra 3

127000 Maliniak Świerkowa 6 Bazy danych

3

127000 Maliniak Świerkowa 6 W-F 5

123123 Kowal Akacjowa 1 Algebra 4

123123 Kowal Akacjowa 1 Bazy danych

5

123123 Kowal Akacjowa 1 W-F 3

666555 Nowak Różana 4/78 PTO 3

666555 Nowak Różana 4/78 Sieci 4

dr Paweł Drozda

Po co normalizować? (2)

nrindeksu, przedmiot – pole unikalne Problemy (anomalie):

Redundancja Przy wprowadzaniu danych Przy usuwaniu danych Przy aktualizacji

Rozwiązanie – rozkład relacji na relacje Student oraz Egzamin

dr Paweł Drozda

Po co normalizować? (3)

Student

nrindeksu nazwisko adres

127000 Maliniak Świerkowa 6

123123 Kowal Akacjowa 1

666555 Nowak Różana 4/78

Egzamin

nrindeksu przedmiot ocena

127000 Analiza 2

127000 Algebra 3

127000 Bazy danych 3

127000 W-F 5

123123 Algebra 4

123123 Bazy danych 5

123123 W-F 3

666555 PTO 3

666555 Sieci 4

Rozwiązanie:

dr Paweł Drozda

Po co normalizować? (4)

adres i nazwisko – tylko w jednej krotce (rozwiązanie redeundancji)

przy wstawianiu nowego studenta – niekoniecznie przedmiot i ocena (rozwiązanie problemu wstawiania)

usunięcie egzaminu nie usuwa studenta (rozwiązanie problemu usuwania)

aktualizacja adresu, nazwiska – tylko raz (rozwiązanie problemu aktualizacji)

dr Paweł Drozda

Zależności funkcyjne (1)

Definicja Jeśli dwie krotki relacji R są zgodne dla

atrybutów A1,…,An to muszą być zgodne w pewnym atrybucie B

Zapis

A1,…,An B

Gdy: A1,…,An B1 … A1,…,An Bm, to zapisujemy

A1,…,An B1,…,Bm

dr Paweł Drozda

Zależności funkcyjne – przykład (1)

Student

nrindeksu nazwisko adres przedmiot ocena

127000 Maliniak Świerkowa 6 Analiza 2

127000 Maliniak Świerkowa 6 Algebra 3

127000 Maliniak Świerkowa 6 Bazy danych 3

127000 Maliniak Świerkowa 6 W-F 5

123123 Kowal Akacjowa 1 Algebra 4

123123 Kowal Akacjowa 1 Bazy danych 5

123123 Kowal Akacjowa 1 W-F 3

666555 Nowak Różana 4/78 PTO 3

666555 Nowak Różana 4/78 Sieci 4

dr Paweł Drozda

Zależności funkcyjne – przykład (2)

Zależności funkcyjne nrindeksu nazwisko nrindeksu adres

ponieważ:

127000 Maliniak, Świerkowa 6

123123 Kowal, Akacjowa 1

666555 Nowak, Różana 4/78

dr Paweł Drozda

Klucze Nadklucz – podzbiór atrybutów w relacji od

którego wszystkie pozostałe zależą funkcyjnie

Zbiór atrybutów (A1,…,An) tworzy klucz relacji jeżeli: Pozostałe atrybuty są funkcyjnie zależne od

(A1,…,An) Nie istnieje podzbiór zbioru {A1,…,An}, od

którego pozostałe atrybuty są funkcyjnie zależne (klucz musi być minimalny)

dr Paweł Drozda

Klucze – przykład (1)

Zaliczenie

nrindeksu przedmiot Data ocena

12345 Analiza 2008-02-21 3,5

12345 Algebra 2008-02-21 3,5

12345 PTO 2008-02-01 5

12345 Bazy Danych 2008-02-01 5

54321 Bazy Danych 2008-02-08 5

54321 Algebra 2008-02-21 3,5

54321 PTO 2008-02-05 3

66666 Bazy Danych 2008-02-01 2

66666 PTO 2008-02-11 3

dr Paweł Drozda

Klucze – przykład (2)

Klucz – (nrindeksu, przedmiot) Pozostałe dwa pola zależne funkcyjnie

od nrindeksu i przedmiot nrindeksu, przedmiot data, ocena

(przy założeniu że dany student może mieć jedną ocenę z zaliczenia)

dr Paweł Drozda

Wykrywanie kluczy Dla relacji stworzonej ze zbioru encji –

atrybuty kluczowe tego zbioru encji Związki binarne:

Związek wiele do wiele – klucz tworzony z kluczy obu zbiorów encji

Związek wiele do jeden – klucze pochodzą ze zbioru encji po stronie wiele

Związek jeden do jeden – klucz dowolnego ze zbiorów encji brany pod uwagę

Związki wieloargumentowe – nie da się jednoznacznie określić klucza

dr Paweł Drozda

Reguły dotyczące zależności funkcyjnych (1)

Poprzez zastosowanie reguł otrzymane zbiory są równoważne z wejściowymi

Zasady podziału i łączenia Podział

A1,…,An B1,…,Bm A1,…,An Bi, i=1,…,m

nrindeksu nazwisko, adres można podzielić:

nrindeksu nazwisko, nrindeksu adres Łączenie – odwrotność podziału

dr Paweł Drozda

Reguły dotyczące zależności funkcyjnych (2)

Reguła zależności trywialnych Typy zależności dla A1,…,An B1,…,Bm:

Trywialna – gdy B jest podzbiorem A Nietrywialna – gdy choć jeden element ze zbioru

B nie należy do A Całkowicie nietrywialna – gdy żaden atrybut

zbioru B nie należy do zbioru A Reguła zależności:

Dla A1,…,An B1,…,Bm zbiór B można ograniczyć do podzbioru elementów nie należących do A

dr Paweł Drozda

Reguły dotyczące zależności funkcyjnych (3)

Domknięcie zbioru atrybutów nad zbiorem zależności S – ozn. {A1,…,An}+

- zbiór atrybutów spełniających wszystkie zależności ze zbioru S (zakładając występowanie zależności trywialnych)

dr Paweł Drozda

Reguły dotyczące zależności funkcyjnych (4)

Tworzenie domknięcia Start: X={A1,…,An}

X – zbiór domknięcia Dla dowolnych B1,…,Bk C, gdzie B1,…,Bk

należą do X a C nie należy do X, C zostanie dołączone do X

Poprzednia procedura powtarzana aż do momentu gdy nie da się dodać żadnego atrybutu do X – wtedy X={A1,…,An}+

dr Paweł Drozda

Reguły dotyczące zależności funkcyjnych (5)

Domknięcie – przykładRelacja z atrybutami A,B,C,D,E,F,GSzukane domknięcie dla zbioru {A,B} z zależnościami funkcyjnymi:AB D, ACD E, D C, EF G1. X={A,B} dodane D z zależności ABD2. X={A,B,D} dodane C z zależności DC3. X={A,B,D,C} dodane E z zależności ACDE4. X={A,B,D,C,E}={A,B}+ - kolejnych atrybutów nie można już dodać

dr Paweł Drozda

Reguły dotyczące zależności funkcyjnych (6)

Domknięcie informuje, czy dana zależność funkcyjna wynika z S

Reguła przechodniościJeśli w relacji R zachodzą zależnościA1,…,An B1,…,Bm i B1,…,Bm C1,…,Ck

to również w tej relacji zachodzi:A1,…,An C1,…,Ck

Klucz relacji zbiór {A1,…,An}+ zawiera wszystkie elementy

relacji usunięcie elementu ze zbioru {A1,…,An} skutkuje

zmniejszeniem się zbioru {A1,…,An}+

dr Paweł Drozda

Normalizacja

Dekompozycja relacji, aż do osiągnięcia pożądanych cech schematu – różnych dla każdej postaci normalnej

Własności normalizacji: zachowania atrybutów zachowania informacji zachowania zależności

dr Paweł Drozda

Pierwsza postać normalna – 1NF (1)

Definicja – relacja jest w pierwszej postaci normalnej wtw gdy każdy atrybut jest zależny funkcyjnie od klucza relacji

Oceny

Student Ocena

Nowak 2;4;3,5

Kowal 2;5

Oceny w 1NF

Student Ocena

Nowak 2

Nowak 4

Nowak 3,5

Kowal 2

Kowal 5

dr Paweł Drozda

Druga postać normalna - 2NF

Definicja – relacja jest w drugiej postaci normalnej wtw gdy jest w pierwszej postaci normalnej oraz każdy atrybut niekluczowy jest w pełni funkcyjnie zależny od klucza głównego (zależy o całego klucza, a nie od jego części)

dr Paweł Drozda

Przekształcenie do 2NF - przykład

Zaliczenie w 1NF

Nrindeksu przedmiot Nazwisko ocena

12345 Analiza Kowal 3,5

12345 Algebra Kowal 3,5

54321 Bazy Danych Nowak 5

54321 Algebra Nowak 3,5

Student w 2NF

nrindeksu Nazwisko

12345 Kowal

54321 Nowak

Zaliczenie w 2NF

nrindeksu przedmiot ocena

12345 Analiza 3,5

12345 Algebra 3,5

54321 Bazy Danych 5

54321 Algebra 3,5

dr Paweł Drozda

Trzecia postać normalna – 3NF

Definicja – relacja jest w trzeciej postaci normalnej wtw gdy jest w drugiej postaci normalnej oraz gdy każdy niekluczowy atrybut relacji jest bezpośrednio zależny od klucza relacji

dr Paweł Drozda

Przekształcenie do 3NF - przykładPrzedmioty w 2 NF

przedmiot pesel Nazwisko

Analiza 78071103350

Kowal

Algebra 78071103350

Kowal

Bazy Danych 68121103312

Nowak

Wykładowcy w 3NF

pesel Nazwisko

78071103350

Kowal

78071103350

Kowal

68121103312

Nowak

Przedmioty w 3NF

przedmiot pesel

Analiza 78071103350

Algebra 78071103350

Bazy Danych 68121103312

dr Paweł Drozda

Dekompozycja relacji

Podział atrybutów na dwa schematy nowych relacji

Podział krotek przez rzutowanie {A1,…,An}={B1,…,Bm} suma {C1,…,Ck}

dr Paweł Drozda

Postać normalna Boyce’a - Codda

Definicja – relacja jest w postaci normalnej Boyce’a – Codda wtw gdy dla każdej zależności nietrywialnej A1,…,An B zbiór {A1,…,An} jest nadkluczem tej relacji

dr Paweł Drozda

BCNF – przykład (1)

Relacja: adresmiasta(kod,miasto,ulicanr)

Zależności funkcyjnekod miasto, miasto,ulica kodKlucz (miasto, ulica)

Relacja nie jest w BCNF ponieważ pierwsza zależność jest niezgodna z definicją

dr Paweł Drozda

BCNF – przykład (2)

Wykładowcy w BCNF

pesel Nazwisko

78071103350

Kowal

78071103350

Kowal

68121103312

Nowak

Przedmioty w BCNF

przedmiot pesel

Analiza 78071103350

Algebra 78071103350

Bazy Danych 68121103312

dr Paweł Drozda

BCNF - dekompozycja Odnalezienie nietrywialnej zależności funkcyjnej:

A1A2...An B1B2...Bn, która narusza BCNF – tzn. A1A2...An nie jest nadkluczem

Dodanie do prawej strony wszystkich atrybutów zależnych funkcyjnie od A1A2...An – w ten sposób powstaje nowa relacja

Druga relacja będzie się składała z atrybutów A1A2...An oraz z pozostałych (poza B1B2...Bn) atrybutów relacji

dr Paweł Drozda

BCNF – dekompozycja - przykład

Zaliczenie

nrindeksu przedmiot Nazwisko ocena

Student w BCNF

nrindeksu Nazwisko

Zaliczenie BCNF

nrindeksu przedmiot ocena

Nrindeksu, przedmiot ocena nrindeksu nazwisko

dr Paweł Drozda

Zależności wielowartościowe (1) Oznaczenie:

A B – dla każdego zbioru argumentów A istnieje wiele różnych argumentów z Bnp. dla relacji osoba:Pesel kat.prawajazdy

Jeśli w relacji są co najmniej dwie zależności wielowartościowe - redundancja

dr Paweł Drozda

Zależności wielowartościowe (2)

Loty

Lot Dzień Typsamolotu

100 Poniedziałek 111

100 Wtorek 111

100 Poniedziałek 222

100 Wtorek 222

na jednej trasie – dwa typy samolotów

Modyfikacja – dodatkowy dzień (czwartek) dodatkowy typ samolotów (333)

dr Paweł Drozda

Zależności wielowartościowe (3)

Loty

Lot Dzień Typsamolotu

100 Poniedziałek 111

100 Wtorek 111

100 Czwartek 111

100 Poniedziałek 222

100 Wtorek 222

100 Czwartek 222

100 Poniedziałek 333

100 Wtorek 333

100 Czwartek 333

Doszło 5 krotek.

Rozwiązanie:

DekompozycjaLoty 1

Lot Dzień

100 Poniedziałek

100 Wtorek

100 Czwartek

Loty 2

Lot Typsamolotu

100 111

100 222

100 333

dr Paweł Drozda

Zależności wielowartościowe (4)

Konsekwencja 1NF – nie dopuszcza aby krotki zawierały atrybuty wielowartościowe

Zależność trywialnaXY jest trywialna, gdy:Y jest podzbiorem X lub X suma Y =R

dr Paweł Drozda

Czwarta postać normalna

Definicja:Relacja jest w 4NF wtw gdy:relacja jest w 3NF i każda zależnośćA1,…,An B1,…,Bm jest trywialna lub {A1,…,An} jest nadkluczem

dr Paweł Drozda

Dekompozycja na relacje w 4NF

Relacja R dekomponuje się na relacje R1 i R2 bez utraty informacji, gdy:

R1 iloczyn R2 (R1-R2) lubR1 iloczyn R2 (R2-R1)