Wykad 2 Projektowanie relacyjnych baz danych postacie normalne

Wykład 2 Projektowanie relacyjnych baz danych – postacie normalne L. Banachowski 1

Projektowanie schematu bazy danych Celem procesu projektowania schematu bazy danych jest: 1. wyspecyfikowanie wymagań użytkowników przyszłej bazy danych, dokonanie analizy tych wymagań; 2. utworzenie schematu bazy danych spełniającego wymagania użytkowników i jednocześnie gwarantującego poprawne funkcjonowanie bazy danych w ramach całego systemu informacyjnego, zaprojektowanie schematu bazy danych. L. Banachowski 2

Pośredni model - diagram związków encji ERD • Ten pośredni model powinien w sposób jednoznaczny określać wymagania użytkowników - umożliwiając im sprawdzenie czy analityk systemu rzeczywiście dobrze zrozumiał ich intencje i specyfikę działania firmy. • Ten pośredni model powinien być istotnie prostszy od schematu bazy danych, abstrahując od szczegółów implementacyjnych, które muszą być później opracowane przez projektanta bazy danych aby baza danych mogła powstać i spełniać postawione przed nią zadania. L. Banachowski 3

Przykłady związków trzy-argumentowych “student chodzi na ćwiczenia prowadzone przez asystenta” “pracownik pracuje w projekcie w roli” “towar jest sprowadzany od dostawcy do użycia w projekcie” L. Banachowski 4

Notacja Chena - MS Visio L. Banachowski 5

Reprezentowanie związku nie-jednoznacznego 1. Związek >2 argumentowy. 2. Związek binarny wieloznaczny. Metoda: Do reprezentowania takiego związku Z(E 1, E 2, …, En) używamy dodatkowej encji nazywanej asocjacyjną, którą łączymy związkami jednoznacznymi (encja asocjacyjna po stronie wiele tych związków) z encjami E 1, E 2, …. , En. L. Banachowski 6

Reprezentacja związku 3 argumentowego między studentami, przedmiotami i asystentami (Notacja IDEF 1 X w MS Visio) L. Banachowski 7

Normalizacja bazy danych Każdy fakt przechowywany w bazie danych powinien być wyrażalny w niej tylko na jeden sposób. • Informacja o przedmiocie powinna być zapisana tylko w jednym miejscu, a nie przy każdym studencie, który uczęszcza na zajęcia z tego przedmiotu. • Jeśli w bazie danych zapisujemy informację o rodzicach każdej osoby, nie ma już potrzeby zapisywać informacji o dziadkach, gdyż ta informacja daje się wyprowadzić z informacji o rodzicach. • W modelu firmy lotniczej (przykład rozważany w następnym punkcie) Pasażerowie i Personel to dwie odrębne encje. Jeśli jedna osoba będzie figurować w bazie danych - raz jako pasażer, a raz jako pracownik firmy lotniczej, wówczas dane dotyczące takiej osoby (np. adres zamieszkania) będą zapisane w dwóch różnych miejscach. L. Banachowski 8

Pożądane cechy modelu danych • poprawność modelu; • istotność każdego elementu modelu dla funkcjonowania firmy (organizacji); • pełność modelu – to znaczy, gwarancja, że żaden element modelu danych - istotny dla funkcjonowania firmy (organizacji), nie został pominięty. Użytkownicy muszą: • rozumieć tworzony model danych i • po dokładnym przeanalizowaniu wszystkich szczegółów, biorąc za to odpowiedzialność, zaakceptować go. Projektant bazy danych dla tego modelu buduje bazę danych i aplikację bazy danych. L. Banachowski 9

Model relacyjnych baz danych • W modelu relacyjnym przyjmuje się, że w tabeli w bazie danych kolejność wierszy i kolejność kolumn są nieistotne. • Dwa wiersze zawierające te same wartości są uznawane za identyczne. • Relacja jest abstrakcyjnym, matematycznym pojęciem zawierającym w sobie istotę modelu relacyjnego. Relacyjna baza danych to zbiór relacji. • Tabela jest konkretną reprezentacją relacji – jedna relacja ma możliwych wiele różnych reprezentacji za pomocą tabel. L. Banachowski 10

Przykład relacji - Loty Numer Skąd Dokąd Odlot Przylot 83 Warszawa Moskwa 11: 30 13: 43 84 Moskwa Warszawa 15: 00 17: 55 109 Warszawa Nowy Jork 09: 50 16: 52 213 Warszawa Frankfurt 11: 43 12: 45 214 Frankfurt Warszawa 14: 20 15: 29 115 Nowy Jork Warszawa 18: 12 07: 10 515 Nowy Jork Frankfurt 22: 00 09: 15 516 Frankfurt Nowy Jork 13: 20 19: 15 711 Warszawa Tokio 18: 00 09: 10 L. Banachowski 11

Schemat relacji R = {A 1, A 2, . . . , An} gdzie A 1, A 2, . . . , An są atrybutami (nazwami kolumn). Na przykład, Loty = {Numer, Skąd, Dokąd, Odlot, Przylot} L. Banachowski 12

Dziedzina atrybutu Każdemu atrybutowi A przyporządkowana jest dziedzina Dom(A) czyli zbiór dopuszczalnych wartości. Np. Dom(Numer) = NUMBER(3) Dom(Skąd) = CHAR(15) Dom(Dokąd) = CHAR(15) Dom(Odlot) = CHAR(5) Dom(Przylot) = CHAR(5) L. Banachowski 13

Dziedzina relacji • Dziedziną relacji o schemacie R = {A 1, A 2, . . . , An} nazywamy sumę dziedzin wszystkich atrybutów relacji Dom(R) = Dom(A 1) Dom(A 2) . . . Dom(An) L. Banachowski 14

Definicja relacji • Relacja o schemacie R = {A 1, A 2, . . . , An} jest to skończony zbiór r = {t 1, t 2, . . . , tm} odwzorowań ti: R Dom(R) takich, że dla każdego j, 1 <= j <= n, ti(Aj) Dom(Aj) • Każde takie odwzorowanie nazywa się krotką (lub wierszem). L. Banachowski 15

Przykład krotki (elementu relacji) • Krotka odpowiada wierszowi (rekordowi) w tabeli, np. : t(Numer) = 83, t(Skąd) = 'Warszawa', t(Dokąd) = 'Moskwa', t(Odlot)= '11: 30', t(Przylot) = '13: 43' • Graficznie: Numer Skąd Dokąd Odlot Przylot 83 Warszawa Moskwa 11: 30 13: 43 L. Banachowski 16

Operacja ograniczenia krotki • Ograniczeniem krotki t relacji r o schemacie R do zbioru atrybutów X R nazywamy odwzorowanie będące ograniczeniem krotki t do zbioru atrybutów X t|X: X Dom(X) • Na przykład, gdy X = {Skąd, Dokąd}, t z poprzedniego przykładu to t|X (Skąd)='Warszawa', t|X (Dokąd) = 'Moskwa' L. Banachowski 17

Zależność funkcyjna • Relacja r o schemacie R = {A 1, A 2, . . . , An} spełnia zależność funkcyjną X Y (X, Y R) jeśli dla każdych dwóch krotek t, u r zachodzi warunek: jeśli t|X = u|X to t|Y = u|Y tzn. w ramach krotek relacji r wartości atrybutów zbioru X determinują jednoznacznie wartości atrybutów zbioru Y. • Numer {Skąd, Dokąd, Odlot, Przylot} L. Banachowski 18

Identyfikacja zależności funkcyjnych • W procesie projektowania dla każdego schematu relacji identyfikujemy zbiór spełniających ją zależności funkcyjnych (zależny od konkretnego zastosowania). Np. Numer {Skąd, Dokąd, Odlot, Przylot} {Skąd, Dokąd, Odlot} {Numer, Przylot} {Skąd, Dokąd, Przylot} {Numer, Odlot} • alternatywna konwencja zapisu: Numer Skąd Dokąd Odlot Przylot Skąd Dokąd Odlot Numer Przylot L. Banachowski 19

Nadklucz relacji • Nadklucz relacji r o schemacie R = {A 1, A 2, . . . , An} - dowolny zbiór atrybutów X R taki, że zachodzi zależność funkcyjna X R tzn. wartość każdego atrybutu jest jednoznacznie zdeterminowana przez wartości atrybutów zbioru X. • Jednym z nadkluczy jest zawsze zbiór wszystkich atrybutów R. L. Banachowski 20

Klucz relacji • Klucz relacji r o schemacie R = {A 1, A 2, . . . , An} - każdy minimalny nadklucz (nie zawierający żadnego nadklucza), tzn. zbiór atrybutów X jest kluczem jeśli wartość każdego atrybutu w R jest jednoznacznie zdeterminowana przez wartości atrybutów zbioru X i żaden podzbiór zbioru X nie ma już tej własności. • Zawsze istnieje co najmniej jeden klucz, a może być ich więcej jak to pokazuje przykład relacji Loty. L. Banachowski 21

Klucze i klucz główny • Zależności funkcyjne podane poprzednio określają trzy klucze: {Numer} {Skąd, Dokąd, Odlot} {Skąd, Dokąd, Przylot} • Wyróżniony klucz nazywa się kluczem głównym. Wchodzące w jego skład atrybuty są podkreślane. • Dla relacji Loty wybieramy jako klucz główny klucz Numer: Loty = {Numer, Skąd, Dokąd, Odlot, Przylot} L. Banachowski 22

Dlaczego schemat jest zły? Redundancja Anomalie przy modyfikacji Nazwa_dostawcy Adres_dostawcy Nazwa_towaru Cena Kowalski Aleja Postępu 102 Modem 1000 Kowalski Aleja Postępu 102 Wtyczka 5 Jaworski Unii Europejskiej 34 Kabel 10 Jaworski Unii Europejskiej 34 Telewizor 1500 Kowalski Aleja Postępu 102 Komplet gier 200 Marciniak …. . … …. Anomalie przy wstawianiu Anomalie przy usuwaniu L. Banachowski 23

Przyczyna trudności • Mamy do czynienia ze złączeniem dwóch różnych rodzajów obiektów (encji), które byłoby lepiej określić jako osobne relacje: Dostawcy = {Nazwa_dostawcy, Adres_dostawcy} Towary = {Nazwa_dostawcy, Nazwa_towaru, Cena} L. Banachowski 24

Dlaczego schemat jest zły? Redundancja Id_pracownika Nazwisko Nazwa_instytucji Adres_instytucji Anomalie przy modyfikacji 101 Kowalski PJWSTK Koszykowa 86 108 Kalinowski PJWSTK Koszykowa 86 107 Jaworski UW Banacha 2 103 Makowski UW Banacha 2 105 Rudziak PJWSTK Koszykowa 86 199 …. . … …. Anomalie przy wstawianiu Anomalie przy usuwaniu L. Banachowski 25

Przyczyna trudności • Mamy do czynienia ze złączeniem dwóch różnych rodzajów obiektów (encji), które byłoby lepiej określić jako osobne relacje: Pracownicy={Id_prac, Nazwisko, Nazwa_instytucji} Instytucje={Nazwa_instytucji, Adres_instytucji} • Przyczynę trudności można też wyjaśnić za pomocą pojęcia zależności funkcyjnej. L. Banachowski 26

Zależności od części klucza • Dostawcy={Nazwa_dostawcy, Adres_dostawcy, Nazwa_towaru, Cen a} • zależności funkcyjne: Nazwa_dostawcy Adres_dostawcy Nazwa_dostawcy, Nazwa_towaru Cena • • Kluczem jest para atrybutów: Nazwa_dostawcy, Nazwa_towaru. Pierwsza zależność jest zależnością od części klucza - tego typu zależności są przyczyną redundancji i anomalii! L. Banachowski 27

Zależności przechodnie od klucza ü Pracownicy={Id_prac, Nazwisko, Nazwa_instytucji, Adres_instytucji} ü zależności funkcyjne: ü Id_prac Nazwisko, Nazwa_instytucji ü Nazwa_instytucji Adres_instytucji ü ü Kluczem jest atrybut Id_prac. Druga zależność jest zależnością przechodnią od klucza - tego typu zależności są też przyczyną redundancji i anomalii! L. Banachowski 28

"Złe" zależności funkcyjne zależności nie od klucza • Zależność funkcyjna X Y jest zależnością od klucza jeśli zbiór atrybutów X jest nadkluczem. • Zależność funkcyjna X Y jest zależnością nie od klucza jeśli: 1. jest nietrywialna tzn. zbiór Y nie jest podzbiorem X; 2. nie jest zależnością od klucza. • Są dwa typy zależności nie od klucza: 1. zależności od części klucza, 2. zależności przechodnie od klucza. L. Banachowski 29

"Złe" zależności funkcyjne zależności nie od klucza • Zależność nie od klucza wprowadza wewnętrzną zależność między atrybutami relacji, możliwość przewidywania wartości jednych atrybutów przez inne, co oznacza redundancję i anomalie. Jedyną “zdrową” zależnością funkcyjną jest zależność od klucza. X Y X Y x …. . y x …. . ? L. Banachowski 30

Metoda eliminacji zależności nie od klucza • Metoda eliminacji zależności nie od klucza polega na wprowadzeniu dla niej osobnej relacji i usunięcie atrybutu stojącego po prawej stronie zależności nie od klucza z oryginalnego schematu relacji. • Dla schematu dostawców: dodajemy schemat relacji {Nazwa_dostawcy, Adres_dostawcy} i usuwamy z oryginalnego schematu atrybut Adres_dostawcy otrzymując schemat {Nazwa_dostawcy, Nazwa_towaru, Cena}. • Dla schematu pracowników: dodajemy schemat relacji {Nazwa_instytucji, Adres_instytucji} i usuwamy z oryginalnego schematu atrybut Adres_instytucji otrzymując schemat {Id_prac, Nazwisko, Nazwa_instytucji}. L. Banachowski 31

Postać normalna Boyce'a-Codda • Dla każdej zależności w schemacie relacji X A (X R, A R) zachodzi albo 1. A X (zależność trywialna), albo 2. X jest nadkluczem. • Jeśli schemat relacji znajduje się w postaci Boyce'a-Codda, nie ma redundancji - nie można przewidzieć jednych wartości w oparciu o inne. L. Banachowski 32

Przykłady 1. F = Id_prac {Nazwisko, Funkcja, Stanowisko} - BC 2. F = Numer {Skąd, Dokąd, Odlot, Przylot} {Skąd, Dokąd, Odlot} {Numer, Przylot} {Skąd, Dokąd, Przylot} {Numer, Odlot} - BC 3. R = {M, U, K} (Miasto, Ulica, Kod) F = MU K; K M - nie BC Są dwa klucze MU i KU. Ze względu na zależność K M schemat nie jest w postaci normalnej Boyce'a-Codda i nie da się go rozłożyć z zachowaniem zależności funkcyjnych. L. Banachowski 33

Trzecia postać normalna • A jest atrybutem głównym (kluczowym) jeśli istnieje klucz K taki, że A K; w przeciwnym przypadku A jest atrybutem niegłównym (niekluczowym). Trzecia postać normalna Dla każdej zależności w schemacie relacji X A (X R, A R) zachodzi albo 1. A X (zależność trywialna), albo 2. X jest nadkluczem, albo 3. A jest atrybutem głównym. L. Banachowski 34

Przykłady 1. R = {M, U, K}, (Miasto, Ulica, Kod) F = MU K; K M Dwa klucze MU i KU Schemat R jest w trzeciej postaci normalnej (gdyż M jest atrybutem głównym). 2. R = {A, B, C, D}, F = AB C; B D; BC A Kluczami są AB i BC. D jest atrybutem niegłównym. Schemat R nie jest w trzeciej postaci normalnej. L. Banachowski 35

Zależności wielowartościowe (czwarta postać normalna) Student (Nr_stud, Przedmiot, Sport) 100 Bazy danych Tenis 100 Bazy danych Pływanie 100 Assemblery Tenis 100 Assemblery Pływanie 200 Bazy danych Boks • Schemat jest w postaci normalnej Boyce’a-Codda (jedynym kluczem są wszystkie trzy atrybuty) a w tabeli jest redundancja i możliwe są anomalie! L. Banachowski 36

Zależności wielowartościowe W relacji R = {Nr_stud, Przedmiot, Sport} mamy do czynienia z zależnościami wielowartościowymi: F = {Nr_stud Kurs; Nr_stud Sport} Aby je wyeliminować rozkładamy R na dwie relacje: {Nr_stud, Przedmiot} i {Nr_stud, Sport}. L. Banachowski 37

Zależności złączeniowe (piąta postać normalna) Zależność złączeniowa jest uogólnieniem zależności wielowartościowej w tym sensie, że jej eliminacja polega na rozbiciu relacji na więcej niż dwie relacje. Rozważmy relację między dostawcami, częściami i projektami. L. Banachowski 38

Nazwa_dost Nazwa_części Nazwa_projektu Kowalski Śrubka Wenus Kowalski Nakrętka Mars Kowalski Śrubka Mars Jankowski Gwóźdź Wenus Jankowski Śrubka Wenus Misiak Nakrętka Neptun L. Banachowski 39

Zasady biznesowe Załóżmy, ze zasady działania firmy określają, że jeśli (1) dostawca X dostarcza część Y, (2) dostawca X pracuje dla projektu Z, (3) projekt Z używa części Y to (4) dostawca X dostarcza część Y dla projektu Z. L. Banachowski 40

Przy tych zasadach zapis informacji w tabeli jest redundantny bo skoro (1) Kowalski jest dostawcą śrubek (dostarcza dla projektu Wenus), (2) Kowalski pracuje dla projektu Mars (dostarcza nakrętki), (3) projekt Mars używa śrubek (od dostawcy Jankowskiego) to redundantna jest już informacja, że: (4) Kowalski dostarcza śrubek dla projektu Mars. L. Banachowski 41

Rozwiązanie problemu Rozwiązaniem problemu jest podział relacji na trzy relacje: (1) Dostawcy-części, (2) Dostawcy-projekty, (3) Projekty-części. Podział na tylko dwie nie jest wystarczający! W relacjach (1)-(3) nie ma już redundancji, a ich złączenie daje wyjściową relację. L. Banachowski 42

Przy małej zmianie Sytuacja uległaby zmianie gdybyśmy chcieli przechowywać informację o ilości zamówionych części w danym projekcie u konkretnego dostawcy. Wówczas rozbicie na trzy relacje już nie byłoby możliwe. L. Banachowski 43

Granice normalizacji Normalizacji nie doprowadza się czasem do końca, np: (1) gdy stosuje się replikacje danych; (2) gdy funkcje na danych preferują nieznormalizowane schematy relacji np. gdy przy każdym wypisywaniu informacji o towarze załączamy także adres dostawcy. Jeśli tak postępujemy, to wszystkie zależności nie od klucza muszą być sprawdzane przy każdej modyfikacji bazy danych i wszystkie pozostające anomalie muszą mieć przygotowane specjalne traktowanie (np. przy użyciu dodatkowych tabel do obsługi anomalii wstawiania i usuwania). L. Banachowski 44