Kurs języka HTML i CSS

Poradnik webmastera

  • Zwiększ rozmiar czcionki
  • Domyślny  rozmiar czcionki
  • Zmniejsz rozmiar czcionki

Projektowanie relacyjnych baz danych

Email Drukuj PDF

Mam nadzieję, że do tej pory przyzwyczaiłeś się do tabelarycznego układu danych i poznałeś sposoby odczytywania i modyfikowania tak zapisanych danych. W tym odcinku poznasz nieco teorii relacyjnych baz danych, w tym algorytmom przekształcania relacji do kolejnych postaci normalnych, czyli dowiesz się, jak zaprojektować podstawowe obiekty baz danych — tabele.

Wprowadzenie

Celem procesu projektowania bazy danych jest utworzenie poprawnego i spełniającego wymagania użytkowników logicznego schematu bazy danych. Ponieważ cały proces jest dosyć skomplikowany, w przypadku rozbudowanych baz danych dzieli się go na kilka etapów:

  1. Przygotowanie diagramu związków E/R.

  2. Normalizacja projektu.

  3. Implementacja zasad wymuszających integralność danych.

Skoro baza danych to zbiór uporządkowanych danych, to praca projektanta sprowadza się do opracowania struktury, w której będą przechowywane dane, oraz wyboru informacji, jakie powinny znaleźć się w bazie danych.

W pierwszej kolejności należy określić schemat (strukturę) bazy danych i dopiero wtedy, dysponując gotowym schematem, wybrać te informacje, jakie będą przechowywane w bazie danych. Tworząc schemat, należy kierować się ogólną regułą, na podstawie której:

  1. W otaczającym nas świecie można wyróżnić mniej lub bardziej trwałe, ale będące logicznymi całościami obiekty różnych typów.

  2. Obiekty poszczególnych typów mogą być określone za pomocą właściwych im cech (atrybutów, metod i zdarzeń).

Wybór typów obiektów oraz określenie, jakie informacje powinny być przechowywane w bazie, jest podstawą diagramu związku E/R (encja/relacja). Zanim opiszę tworzenie takiego modelu, przedstawię podstawy teorii relacyjnych baz danych.

Model relacyjnych baz danych

Termin relacyjna baza danych oznacza bazę zbudowaną z relacji. Podstawowy obiekt takiej bazy danych, tabela, jest konkretną reprezentacją relacji — technicznego pojęcia matematyki. Wynika z tego, że oba terminy nie są jednoznaczne, a jedna relacja może być odwzorowana za pomocą wielu różnych tabel.

Relacyjny model baz danych został stworzony przez E.F. Codda w 1970 roku i przedstawiony w pracy Relacyjny model danych dla dużych banków danych. Nie używa się tam pojęć tabela, kolumna i wiersz, lecz relacja, atrybut i krotka.

Każda tabela składa się z pewnej liczby wierszy i kolumn. Na przecięciu wiersza z kolumną znajduje się pole. W modelu relacyjnym przyjmuje się, że:

  1. kolejność wierszy i kolumn w tabelach jest nieistotna,

  2. wiersze zawierające takie same dane są identyczne.

Natomiast w tabeli przedstawiającej konkretny przypadek relacji identyczne dane (wartości pól) będą przechowywane w różnych wierszach. Pole zawiera najmniejszą niepodzielną wartość, czyli taką część informacji, która nie może być dalej dzielona ze względu na spójność logiczną.

Podstawowe definicje

Przed podaniem definicji relacji — podstawowego terminu opisywanej teorii — musimy poznać definicje schematu relacji i dziedziny relacji.

Definicja 1. Schemat relacji

Schematem relacji nazywamy zbiór R = {A1, A2, …, An}, gdzie: A1, A2, …, An są atrybutami, reprezentowanymi w tabeli poprzez nazwy kolumn.

Przykładowym schematem relacji item będzie zbiór: item {item_id, description, cost_price, sell_price}. Każdemu atrybutowi (A1, A2, …, An) przyporządkowana jest dziedzina (zakres dopuszczalnych wartości atrybutu) reprezentowana przez typ danych. W tym przypadku dla poszczególnych kolumn zdefiniowano następujące typy danych:

item_id

int,

description

varchar(64)

cost_price

numeric(7,2)

sell_price

numeric(7,2)

czyli:

  1. Wartości atrybutu item_id będą liczbami całkowitymi z zakresu od –2 147 483 648 do 2 147 483 647 lub od 0 do 4 294 967 295 (w przypadku liczb bez znaku).

  2. Wartości atrybutu description będą ciągami 64 znaków.

  3. Wartości atrybutu cost_price będą liczbami dziesiętnymi o precyzji 7 i skali 2 (precyzja oznacza liczbę cyfr przed przecinkiem, skala — liczbę miejsc po przecinku).

  4. Wartości atrybutu sell_price będą liczbami dziesiętnymi o precyzji 7 i skali 2.

Definicja 2. Dziedzina relacji

Dziedziną relacji o schemacie R = {A1, A2, …, An} nazywamy sumę dziedzin wszystkich jej atrybutów Dom(R)=Dom(A1) ∪ Dom(A2) Dom(An).

Przykładową dziedziną relacji item będzie dziedzina Dom(item) = int varchar (64) numeric(7,2) numeric(7,2).

Teraz można już podać definicję podstawowego obiektu relacyjnych baz danych — relacji.

Definicja 3. Relacja

Relacją o schemacie R = {A1, A2, …, An} nazywamy skończony zbiór r = {t1, t2, …, tm} odwzorowań ti: R Dom(R) takich, że dla każdego j z zakresu 1 <= j <= n zachodzi zależność: ti(Aj) Dom(Aj).

Tak zdefiniowane pojedyncze odwzorowanie nosi nazwę krotki i odpowiada mu pojedynczy wiersz tabeli. Wartością krotki jest suma wartości poszczególnych atrybutów. Na przykład wartością pierwszej krotki opisywanej tabeli jest zbiór: 1 ' Wood Puzzle' 15.23 21.95. Krotka może zostać ograniczona do wartości wybranych atrybutów.

Lemat 1. Ograniczenie 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 tIX: X Dom(X).

Przykład. Wartością ograniczenia X = { item_id, description } przykładowej krotki będzie zbiór wartości { 1' Wood Puzzle' }.

Podstawowe zasady implementacji modelu relacyjnych baz danych można podzielić na trzy grupy:

Zasady dotyczące struktury danych.

Zasady dotyczące przetwarzania danych.

Zasady dotyczące integralności danych.

Zasady dotyczące struktury danych

W modelu relacyjnych baz danych informacja o poszczególnych obiektach zapisana jest w tabelach. Jest to model abstrakcyjny, zawsze obsługiwany jednakowo i niezwiązany ze sposobem przechowywania danych. Dzięki temu możliwe jest oddzielenie danych od aplikacji klienckiej (interfejsu użytkownika) i platformy sprzętowej. Serwer bazodanowy zarządza rozmieszczeniem danych w plikach znajdujących się na dysku bądź w pamięci podręcznej oraz metodami dostępu do tych danych.

Cechą charakterystyczną modelu jest wymóg przechowywania w bazie danych tylko konkretnych wartości. W relacyjnych bazach danych nie możemy posługiwać się wskaźnikami do danych.

Twórca relacyjnego modelu baz danych, E.F. Codd, przedstawił zbiór dwunastu postulatów, które powinny być uwzględnione przez projektantów systemów zarządzania relacyjnymi bazami danych. Zamieszczone niżej postulaty dotyczą struktury danych; ich znajomość może okazać się przydatna podczas projektowania baz.

  • Postulat informacyjny. Na poziomie logicznym dane reprezentowane są wyłącznie za pomocą tabel wartości.
  • Postulat dostępu. Do każdej pojedynczej danej jest dostęp za pomocą nazwy tabeli, kolumn i wartości kluczy głównych.
  • Postulat fizycznej niezależności danych. Zmiany w sposobie przechowywania danych i dostępu do nich nie wpływają na aplikację kliencką.
  • Postulat logicznej niezależność danych. Zmiany w tabelach, zachowujące informację i dopuszczalne semantycznie, nie mają wpływu na aplikację kliencką.
  • Postulat niezależności dystrybucyjnej. System i jego język umożliwiają dostęp do danych zapisanych w różnych miejscach, np. na wielu komputerach w sieci.
  • Postulat zabezpieczania przed operacjami na niższym poziomie abstrakcji. Jeśli system zarządzania bazą danych umożliwia bezpośrednie operacje na niższych poziomach abstrakcji, nie mogą one naruszać reguł relacyjnego modelu baz danych, w szczególności nie mogą pomijać ograniczeń określonych przez więzy spójności.

Zasady dotyczące przetwarzania danych

Dane przechowywane w bazie danych nie są niezmienne. Wręcz przeciwnie — aby baza danych była przydatna, przechowywane w niej informacje muszą odpowiadać stanowi faktycznemu, a więc muszą być cały czas aktualizowane. Operacje modyfikowania danych nie mogą jednak naruszać struktury danych. Wynika z tego, że przekształcenie danych dokonywane bądź to w celu ich modyfikacji, bądź pobrania danych, musi przebiegać z zachowaniem wewnętrznej logiki (struktury) powiązań istniejących pomiędzy danymi.

Pierwszy typ przekształceń, które można wykonywać na wartościach atrybutów, wynika z definicji relacji jako zbioru. Operatorom algebry zbiorów, czyli , odpowiadają operatory algebry relacji UNION (suma relacji), INTERSECT (część wspólna relacji, przecięcie, przekrój relacji) i EXCEPT (dopełnienie relacji, różnica relacji).

MySQL pozwala na łączenie wyników dwóch zapytań jedynie za pomocą operatora UNION. Aby obliczyć część wspólną lub dopełnienie wyników zapytań, należy posłużyć się podzapytaniem powiązanym.

Na sumę dwóch relacji r s składają się wszystkie elementy relacji r i wszystkie elementy relacji s (rysunek 9.1).

Rysunek 9.1. Suma relacji

Wynikiem przecięcia relacji r s jest relacja składająca się z elementów wspólnych relacji r i s (rysunek 9.2).

Rysunek 9.2. Przecięcie relacji

Wynikiem odjęcia relacji r - s jest relacja powstała na skutek usunięcia z relacji r wszystkich elementów wchodzących w skład relacji s (rysunek 9.3).

Rysunek 9.3. Dopełnienie relacji

Oprócz operatorów teoriomnogościowych dla operacji na relacjach zdefiniowane są następujące operatory: selekcji, projekcji, iloczynu kartezjańskiego i złączenia naturalnego. Dwa pierwsze dotyczą wyników zapytania odwołującego się do pojedynczej relacji, dwa kolejne — wyników odwołania się do wielu relacji.

Definicja 4. Selekcja relacji

Selekcją relacji r o schemacie R = {A1, A2, …, An} nazywamy:

  1. Zbiór tych krotek relacji r, w których wartość atrybutu A wynosi a, przy założeniu, że A R i a Dom(A), co zapisujemy σA=a(r) = {t r | t(A) = a}.

  2. Dla dowolnego warunku logicznego F — zbiór krotek relacji r spełniających warunek F, co zapisujemy σF(r) = {t r | t spełnia warunek F}.

Przykład:

σitem_id=1(item) = {1 ∪'Wood Puzzle' ∪ 15.23 ∪ 21.95}

Dla operatora selekcji prawdziwe są następujące twierdzenia:

Twierdzenie 1. Selekcja sumy

σF (r s ) = σF (r ) σF (s ) — selekcja sumy relacji równa jest sumie selekcji.

Twierdzenie 2. Selekcja przecięcia

σF (r s) = σF (r) σF (s) — selekcja przecięcia relacji równa jest przecięciu selekcji.

Twierdzenie 3. Selekcja różnicy

σF (r - s) = σF (r) - σF (s) — selekcja różnicy relacji równa jest różnicy selekcji.

Na podstawie tych trzech twierdzeń możemy w dowolny sposób zmieniać kolejność operacji teoriomnogościowych względem operacji selekcji. Zmiana kolejności wykonania operacji nie wpłynie na ich wynik.

Dodatkowo — dzięki temu, że w relacyjnej bazie danych wyniki wszystkich zapytań (wyniki instrukcji SELECT) są zwracane w postaci tabel — możliwe jest zagnieżdżanie zapytań (kierowanie zapytań nie do tabel czy widoków, ale do wyników innych zapytań).

Definicja 5. Projekcja relacji

Projekcją relacji r na zbiór atrybutów X R nazywamy zbiór ograniczeń do atrybutów zbioru X wszystkich krotek relacji r, co zapisujemy πX(r) = {t|X: t r}.

Projekcja nazywana jest czasami selekcją pionową lub rzutem — tak jak selekcja ogranicza liczbę wierszy wyniku, projekcja ogranicza liczbę kolumn wyniku.

Przykład:

p{description}(item) = {'Wood Puzzle' 'Rubik Cube' 'Linux CD' 'Tissues' 'Picture Frame' 'Fan Small' 'Fan Large' 'Toothbrush' 'Roman Coin' 'Carrier Bag' 'Speakers' 'SQL Server 2005'}

Dla operatora projekcji prawdziwe są następujące twierdzenia:

Twierdzenie 4. Projekcja projekcji

πX(πY(r)) = πX(r) przy założeniu, że X Y — projekcja wyniku szerszej projekcji równa jest wynikowi bardziej restrykcyjnej projekcji.

Twierdzenie 5. Projekcja sumy

πX(r s) = πX(r) πX(s) — projekcja sumy relacji równa jest sumie projekcji.

Twierdzenie 6. Selekcja projekcji

σF(πX(r)) = πX(σF(r)) — selekcja projekcji równa jest projekcji selekcji.

Na podstawie twierdzeń 4. – 6. możemy w dowolny sposób zmieniać kolejność operacji sumy, selekcji i projekcji. Zmiana kolejności operacji nie wpłynie na ich wynik.

Definicja 6. Iloczyn kartezjański

Iloczynem kartezjańskim dwóch relacji: r o schemacie R = {A1, A2, …, An} i s o schemacie S = {B1, B2, …, Bm}, przy założeniu, że R S = ∅ nazywamy relację q = r s   o schemacie Q = {A1, A2, …, An, B1, B2, …, Bm}, składającą się ze wszystkich krotek t q, dla których istnieją krotki u r i v s takie, że: t(Ai) = u(Ai) dla 1 <= i <= n oraz t(Bi) = v(Bi) dla 1 <= i <= m.

Iloczyn kartezjański dwóch relacji jest zbiorem wszystkich możliwych połączeń krotek obu relacji. Jeżeli schematy relacji nie są rozłączne (istnieje niepusty zbiór powstały w wyniku przecięcia obu relacji), najpierw zmienia się nazwy atrybutów jednej z relacji, a następnie stosuje się powyższą definicję.

Dla operatora iloczynu kartezjańskiego prawdziwe są następujące twierdzenia:

Twierdzenie 7. Iloczyn sumy

(r s) q = (r q) (s q) — iloczyn kartezjański sumy równy jest sumie iloczynów kartezjańskich.

Twierdzenie 8. Selekcja iloczynu

σF^G(r s) = σF(r) σG(s) przy założeniu, że F(R), G(S) — selekcja iloczynu kartezjańskiego równa jest iloczynowi kartezjańskiemu selekcji.

Twierdzenie 9. Projekcja iloczynu

πX Y(r s) = πX(r) πY(s) przy założeniu, że X R i Y S — projekcja iloczynu kartezjańskiego równa jest iloczynowi kartezjańskiemu projekcji.

Jak już miałeś okazję się przekonać, wynikiem iloczynu kartezjańskiego jest zbiór wielu krotek, z których nie wszystkie zawierają sensowne dane. Dlatego w produkcyjnych bazach danych bardzo rzadko wykorzystuje się tę metodę łączenia relacji.

Definicja 7. Złączenie naturalne

Złączeniem naturalnym relacji r o schemacie R = {A1, A2, …, An} i s o schemacie S = {B1, B2, …, Bm} jest relacja q o schemacie Q = R S taka, że q = {t: u r, i v s takie, że u|R = t i v|S = t}. Tak więc złączeniem naturalnym relacji jest zbiór wszystkich możliwych połączeń krotek relacji, przy których ich wspólne atrybuty mają takie same wartości.

Wynikiem złączenia naturalnego dwóch relacji jest zbiór powiązanych ze sobą krotek obu relacji.

Dla operatora złączenia naturalnego prawdziwe są następujące twierdzenia:

Twierdzenie 10. Złączenie relacji z samą sobą

q q = q — wynikiem złączenia relacji z samą sobą jest dana relacja.

Twierdzenie 11. Naprzemienność złączenia

q r = r q — zmiana kolejności łączonych relacji nie wpływa na wynik złączenia.

Twierdzenie 12. Przechodniość złączenia

(q r) s = q (r s) — zmiana kolejności operacji złączenia naturalnego kilku relacji nie wpływa na wynik złączenia.

Twierdzenie 13. Projekcja złączenia

πR(r s) r oraz π(r s) s — projekcja złączenia naturalnego dwóch relacji zawiera się w jednej z tych relacji (a w pewnych okolicznościach jest jej równa).

Twierdzenie 14. Rozkład złączenia

q πR(q) πS(q) — relacja zawiera się w złączeniu naturalnym dwóch projekcji tej relacji (a w pewnych okolicznościach jest mu równa).

Trzy kolejne postulaty Codda bezpośrednio dotyczą zasad związanych z przetwarzaniem danych:

  1. Postulat pełnego języka danych. W systemie zaimplementowany jest pełny język obejmujący:

    1. Definiowanie danych.

    2. Definiowanie perspektyw (widoków lub kwerend, które nie przechowują danych, ale pobierają i porządkują dane zapisane w tabelach, dzięki czemu pełnią funkcje „okularów”, przez które możemy „spoglądać” na dane).

    3. Definiowanie więzów spójności (ograniczeń uniemożliwiających wprowadzenie niepoprawnych danych).

    4. Modyfikowanie danych.

    5. Nadawanie uprawnień użytkownikom.

    6. Implementację transakcyjnego modelu przetwarzania danych.

  2. Postulat modyfikowania bazy danych przez perspektywy. System umożliwia modyfikowanie danych poprzez perspektywy, o ile modyfikacja taka jest semantycznie sensowna.

  3. Postulat modyfikowania danych na wysokim poziomie abstrakcji. System umożliwia modyfikowanie danych za pomocą operacji, których argumentami są tabele i perspektywy.

Zasady dotyczące integralności danych

Zasady dotyczące integralności danych zapewniają zachowanie logicznej spójności informacji przechowywanych w bazie danych. Podstawową zasadą tego typu jest wymóg jednoznacznego identyfikowania wszystkich atrybutów dowolnego obiektu na podstawie jednego jego atrybutu (lub kilku).

W teorii relacyjnych baz danych występuje pojęcie zależności funkcyjnej, za pomocą którego opisuje się wspomnianą zasadę. Atrybuty zależą funkcyjnie od pewnej grupy atrybutów, jeżeli każdemu zespołowi wartości z pierwszej grupy przyporządkowany jest jeden i tylko jeden zespół wartości z drugiej grupy. Zależność funkcyjną zapisuje się za pomocą znaku strzałki, która wskazuje, że jeden zespół wartości zależy funkcyjnie od drugiego.

Dla relacji reprezentowanej przez tabelę items prawdziwe są m.in. następujące zależności funkcyjne:

item_id {description}

description { item_id, cost_price, sell_price}

{item_id, description } {cost_price, sell_price}

{item_id, description, sell_price } {cost_price}

{item_id, sell_price } { description, cost_price}

Czyli:

  1. Na podstawie identyfikatora możemy jednoznacznie określić krotkę tabeli.

  2. Na podstawie nazwy możemy jednoznacznie określić krotkę tabeli.

  3. Na podstawie identyfikatora i nazwy możemy jednoznacznie określić krotkę tabeli.

  4. Na podstawie identyfikatora, nazwy i ceny sprzedaży możemy jednoznacznie określić krotkę tabeli.

  5. Na podstawie identyfikatora i ceny sprzedaży możemy jednoznacznie określić krotkę tabeli.

Istotne jest odróżnienie zależności spełnianych przez istniejące rekordy tabeli od uniwersalnych zależności, które będą spełnione nawet po dodaniu nowych wierszy. W tym przypadku w tabeli item znajduje się jedynie kilkanaście wierszy i możemy jednoznacznie określić towar zarówno na podstawie identyfikatora, jak i na podstawie nazwy czy kombinacji nazwy i ceny zakupu. Jednak nie można wykluczyć, że do tabeli zostaną dodane nowe towary o nazwie takiej samej jak jedna z istniejących i w ten sposób druga wypisana zależność przestanie być prawdziwa.

Definicja 8. Zależność funkcyjna

Relacja o schemacie R = {A1, A2, …, An} spełnia zależność funkcyjną XY (X, YR), jeżeli dla każdych dwóch krotek t, ur zachodzi warunek: jeśli t|X = u|X to t|Y = u|Y. Innymi słowy, atrybuty zależą funkcyjnie od pewnej grupy atrybutów, jeżeli dla każdego zespołu wartości z pierwszej grupy jest przyporządkowany jeden i tylko jeden zespół wartości w drugiej grupie.

Zbiory atrybutów, na podstawie wartości których możemy jednoznacznie określić obiekt, nazywane są kluczami kandydującymi lub nadkluczami. Prawie dla każdej relacji można znaleźć więcej niż jeden klucz kandydujący (w każdym przypadku kluczem kandydującym dowolnej relacji jest zbiór wszystkich jej atrybutów). Jednak identyfikowanie obiektu za pomocą dużej liczby parametrów świadczy o błędnie zaprojektowanej bazie danych i niekorzystnie wpływa na jej wydajność. Dlatego należy dążyć do odkrycia pojedynczego atrybutu jednoznacznie identyfikującego wszystkie obiekty danego typu. Wielu projektantów baz danych dodaje do każdej relacji atrybut jednoznacznie identyfikujący pozostałe. Wartości tego atrybutu są z reguły automatycznie generowane przez serwer i ukrywane przed użytkownikiem. Taki atrybut pełni funkcję klucza głównego obiektu.

Definicja 9. Nadklucz

Nadkluczem relacji r o schemacie R = {A1, A2, …, An} nazywamy dowolny zbiór atrybutów XR taki, że zachodzi zależność funkcyjna XY.

Definicja 10. Klucz

Kluczem relacji r o schemacie R = {A1, A2, …, An} nazywamy każdy minimalny nadklucz (nadklucz niezawierający żadnego innego nadklucza).

Klucze główne, jako jednoznacznie identyfikujące pozostałe atrybuty, są wykorzystywane przy łączeniu relacji ze sobą.

Dla prawie każdej tabeli (wyjątkiem są specjalne tabele reprezentujące związek typu „wiele do wielu” i niezawierające żadnych dodatkowych kolumn) należy określić klucz główny. W większości przypadków najlepszym rozwiązaniem jest utworzenie prostego klucza (klucza odpowiadającego jednej wybranej kolumnie).

Dodatkowym mechanizmem wymuszającym integralność danych przechowywanych w bazie są zawężenia (ang. Constraints). Jeżeli do definicji określonej kolumny tabeli dodamy ograniczenie, to ilekroć będą modyfikowane lub dodawane nowe wartości tego atrybutu, serwer bazodanowy sprawdzi, czy wprowadzane lub modyfikowane wartości są zgodne z określonym ograniczeniem. W przypadku, gdy nowe wartości nie będą zgodne z zadanym ograniczeniem, serwer zwróci komunikat o błędzie i zatrzyma wykonywanie takiego polecenia.

Poniżej opisano pięć rodzajów zawężeń (jak definiować zawężenia, dowiesz się w następnym odcinku, poświęconym tworzeniu tabel).

Ograniczenie NOT NULL

Jeżeli dla jakiegoś atrybutu zdefiniujemy ograniczenie NOT NULL, w odpowiadającej atrybutowi kolumnie nie będzie możliwe przechowywanie pól nieposiadających żadnej wartości (pól niewypełnionych). Domyślnie MySQL pozwala na niewypełnienie danymi każdej z tworzonych kolumn, ale ponieważ obsługa wartości NULL komplikuje przygotowanie interfejsu bazy danych, a dodatkowo niewypełnienie wartości niektórych atrybutów powoduje wadliwe wyniki przeszukiwania bazy danych, powinniśmy uniemożliwić niewypełnienia niektórych (najważniejszych) kolumn danymi.

Ograniczenie UNIQUE

Ograniczenie wymusza przechowywanie niepowtarzalnych wartości atrybutu. Za jego pomocą definiujemy klucz unikalny tabeli. W kolumnach, na które nałożymy ten warunek, MySQL będzie przechowywał wyłącznie niepowtarzalne (unikalne) wartości.

W ramach jednej tabeli można zdefiniować dowolną liczbę zawężeń tego samego typu, na przykład zawężeń UNIQUE lub NOT NULL. Wyjątkiem od tej zasady jest ograniczenie PRIMARY KEY — tabela może posiadać tylko jeden klucz główny.

Ograniczenie PRIMARY KEY (klucz główny)

To ograniczenie jest równoważne ograniczeniom NOT NULL i UNIQUE. Dla każdej tabeli możemy utworzyć tylko jeden klucz główny, za pomocą którego MySQL będzie mógł jednoznacznie identyfikować wszystkie pozostałe atrybuty danego obiektu.

Ograniczenie REFERENCES (klucz zewnętrzny)

Poprzez ograniczenie REFERENCES możemy zdefiniować klucz zewnętrzny, czyli powiązanie relacji z inną relacją. W wyniku nadania tego zawężenia danemu atrybutowi ograniczymy listę dopuszczalnych dla niego wartości do wartości przechowywanych w odpowiadającej jej kolumnie w powiązanej relacji. Atrybuty z nadanym ograniczeniem REFERENCES mogą posiadać wartości NULL (niewypełnione pola), o ile nie zabraniają tego inne nadane zawężenia. Aby MySQL mógł utworzyć klucz zewnętrzny, musi wcześniej zostać utworzona tabela, do której klucz będzie się odwoływał. Dodatkowo w tabeli tej musi być utworzona odpowiednia kolumna (lub kolumny), dla której zdefiniowano taki sam typ danych jak dla kolumny z nałożonym warunkiem REFERENCES. Na kolumnę taką musi być także nałożone jedno z dwóch zawężeń: PRIMARY KEY lub UNIQUE.

Jeżeli pewnej kolumnie nałożymy ograniczenie REFERENCES, powinniśmy pamiętać, że:

  1. W tabeli z kluczem zewnętrznym nie można wstawić wiersza o wartościach klucza zewnętrznego, które nie mają odpowiedników w powiązanej tabeli.

  2. W tabeli z kluczem zewnętrznym nie można bezpośrednio zmodyfikować wiersza klucza zewnętrznego, nadając mu wartości, które nie mają odpowiedników w powiązanej tabeli, o ile podczas tworzenia zawężenia nie została podana dyrektywa ON UPDATE {CASCADE | SET NULL}.

  3. Z powiązanej tabeli nie można bezpośrednio usunąć wiersza, do którego odwołują się wartości klucza zewnętrznego innej tabeli. Można zażądać usuwania wraz z wierszem wszystkich wierszy w tabeli z kluczem zewnętrznym, do których ten wiersz się odwołuje. W tym celu przy klauzuli definiującej klucz zewnętrzny należy umieścić dyrektywę ON DELETE CASCADE. Aby podczas usuwania wiersza w powiązanych tabelach zamienić wartość klucza zewnętrznego na wartość nieokreśloną, należy posłużyć się dyrektywą ON DELETE SET NULL.

Ograniczenie CHECK

Ograniczenie CHECK ma najbardziej ogólne zastosowanie ze wszystkich omówionych zawężeń. Jest ono wyrażeniem, które zwraca wartość True lub False (prawda lub fałsz). Jeżeli ograniczenie CHECK zwróci wartość False, instrukcja języka SQL, w ramach której próbowano zmodyfikować dane, zostanie wycofana, a MySQL zwróci komunikat błędu. Kryteria zawężenia określane są poprzez podanie warunku logicznego. Listę dopuszczalnych operatorów zamieszczono w tabeli 9.1.

Tabela 9.1. Lista operatorów porównania języka SQL

Operator

  Prawda, gdy

=

  Równy

<>, !=

  Nierówny

  Większy niż

  Mniejszy niż

>=

  Nie mniejszy

<=

  Nie większy

IN

  Należy do

BETWEEN … AND

  Zawiera się pomiędzy

LIKE

  Jest zgodny z wzorcem

IS

  Istnieje

NOT <operator algebry relacji>

  Warunek nie jest spełniony

Dodatkowo możliwe jest definiowanie złożonych warunków — warunków składających się z kilku warunków cząstkowych połączonych jednym z operatorów logicznych. Wynik takiego warunku obliczany jest na podstawie wyników warunków cząstkowych i tabel prawdziwości operatora łączącego te warunki (tabela 9.2).

Tabela 9.2. Tabele prawdziwości operatorów logicznych

a

NOT a

 

a

b

a OR b

 

a

b

a AND b

1

0

 

1

1

1

 

1

1

1

0

1

 

1

0

1

 

1

0

0

 

 

 

0

1

1

 

0

1

0

 

 

 

0

0

0

 

0

0

0

Implementacja warunku logicznego użytego jako parametr zawężenia CHECK musi spełniać kilka ograniczeń:

  1. Nie może odwoływać się do wartości kolumn z innych tabel.

  2. Nie może używać funkcji niedeterministycznych (funkcji, które zwracają różne wartości w zależności od okoliczności ich wywołania).

  3. Musi być prosty — niedozwolone jest stosowanie podzapytań (zapytań zagnieżdżonych).

Ograniczenie DEFAULT

Język SQL umożliwia automatyczne wprowadzenie określonej wartości do kolumny, jeżeli użytkownik pozostawi ją nieuzupełnioną. Na implementację ograniczenia DEFAULT nałożone są ograniczenia bardzo podobne do tych, jakie nałożone są na ograniczenie CHECK:

  1. Wyrażenie nie może odwoływać się do kolumn z innych tabel.

  2. Wyrażenie musi być proste — niedozwolone jest używanie podzapytań (zapytań zagnieżdżonych).

Natomiast dozwolone jest stosowanie funkcji, które zwracają różne wartości, w zależności od okoliczności ich wywołania, np. dozwolone jest użycie funkcji NOW() zwracającej aktualną datę i czas zegara systemowego.

Ostatnie trzy z dwunastu postulatów Codda związane są z zasadami dotyczącymi integralności danych. Są to:

  1. Postulat wartości NULL. W systemie jest dostępna specjalna wartość reprezentująca wartość nieokreśloną, brakującą lub nieznaną. Jest to wartość różna od wszelkich konkretnych wartości, w szczególności od ciągu pustego ("") i zera (0).

  2. Postulat słownika danych. Informacje o obiektach bazy danych tworzących jej schemat (metainformacje) są zgrupowane w tabelach i dostępne w taki sam sposób jak każde inne dane.

  3. Niezależność więzów spójności. Więzy spójności są definiowane w języku bazy danych i nie muszą być wyrażane w aplikacji.

Diagram związków E/R

Przygotowanie diagramu związków E/R (encja/relacja) jest pierwszym zadaniem projektanta bazy danych. Na podstawie tego diagramu określimy:

  1. Typy obiektów, o których będziemy przechowywać informacje w bazie danych.

  2. Liczbę i typ atrybutów opisujących obiekty każdego typu.

  3. Wzajemne powiązania pomiędzy danymi dotyczącymi obiektów różnego typu.

Określanie typów obiektów

Podstawą diagramu E/R są encje. Encja reprezentuje określony typ obiektów. Załóżmy, że mamy zaprojektować bazę danych na zlecenie niewielkiego wydawnictwa sprzedającego swoje książki za pośrednictwem internetu.

Podstawowym typem obiektów (encją) naszej bazy danych będzie książka.

Z każdą książką związany jest jej autor. Ponieważ trudno uznać dane o nazwisku czy imieniu autora za cechy napisanej przez niego książki, kolejną encją bazy będzie autor.

Na tej samej zasadzie jako odrębny typ obiektu należy uznać dane wydawnictwa, które wydało książkę: w przypadku oryginału będzie to wydawnictwo zagraniczne, w przypadku książki wydanej po polsku — firma OCO lub wydawnictwo konkurencyjne. Trzecią encją bazy danych będzie wydawnictwo.

Firma OCO, prowadząc sprzedaż za pośrednictwem internetu, ma możliwość zbierania podstawowych danych marketingowych o swoich klientach. Dane te, jako powiązane z książkami, również powinny znaleźć się w naszej bazie — czwartą encją będzie klient.

Podczas implementacji modelu okaże się, że encje odpowiadają tabelom tworzonej bazy danych. Okaże się również, że kompletna lista tabel bazy będzie dłuższa niż lista typów obiektów.

Określanie atrybutów obiektów poszczególnych typów

Atrybut jest właściwością, cechą obiektu, która jest przechowywana w bazie danych jako wartość pewnego typu, np. tytuł książki przechowywany jest jako wartość tekstowa, a informacja o liczbie stron jako liczba całkowita.

Po wyliczeniu typów obiektów należy wypisać listę atrybutów obiektów każdego typu. Na tym etapie projektowania trzeba kierować się zasadą: atrybut powinien opisywać obiekt, przy którym się go umieszcza.

Określmy listę atrybutów obiektów każdego typu:

książka — tytuł, numer ISBN, liczba stron, rok wydania, seria wydawnicza, dołączony nośnik, numer ISBN oryginału, notka redakcyjna,

autor — imię, nazwisko, adres, numer telefonu, e-mail, numer konta bankowego,

wydawnictwo — nazwa, adres, osoba kontaktowa, prezes, numer telefonu, e-mail, numer konta bankowego,

klient — imię, nazwisko, e-mail, data urodzenia, wykształcenie, stanowisko, miasto.

Wyodrębnianie danych elementarnych

Charakterystyczny dla relacyjnych baz danych jest wymóg, aby każdy z atrybutów przechowywany był w bazie danych jako niepodzielna, atomowa wartość. Atomową wartością danego atrybutu jest taka jego część, która nie jest dalej dzielona w celu uzyskania bardziej szczegółowych informacji. Jeżeli na przykład planujemy znaleźć dane o imieniu autora pewnej książki, powinniśmy dane o imieniu traktować jako pojedynczy atrybut (a nie w połączeniu z informacjami o nazwisku). Innym przykładem jest adres wydawnictwa — o ile chcemy wykorzystywać jakikolwiek fragment informacji składającej się na pełny adres firmy, dane takie powinny zostać zapisane jako odrębny atrybut. Na tym etapie projektowania powinniśmy kierować się zasadą: każdy atrybut opisujący dowolny obiekt powinien przyjmować atomową, niepodzielną wartość.

Wypiszmy listę danych elementarnych opisujących obiekty każdego typu:

książka {tytuł, numer ISBN, liczba stron, rok wydania, seria wydawnicza, dołączony nośnik, numer ISBN oryginału, notka redakcyjna, uwagi}

autor {imię, nazwisko, miasto, kod, ulica i nr domu, numer telefonu, e-mail, bank, numer konta bankowego, uwagi}

wydawnictwo {nazwa, kraj, miasto, kod, ulica i nr domu, osoba kontaktowa, prezes, numer telefonu, e-mail, bank, numer konta bankowego, uwagi}

klient {imię, nazwisko, e-mail, data urodzenia, wykształcenie, stanowisko, miasto, uwagi}

Określanie zależności funkcyjnych zachodzących pomiędzy atrybutami

Kolejnym etapem projektowania relacyjnej bazy danych jest określenie zależności funkcyjnych zachodzących pomiędzy atrybutami poszczególnych encji. Przypomnijmy — atrybuty zależą funkcyjnie od pewnej grupy atrybutów, jeżeli dla każdego zbioru wartości z pierwszej grupy jest przyporządkowany jeden i tylko jeden zespół wartości z grupy drugiej.

Na tym etapie należy kierować się zasadą: dla każdego typu obiektów (w każdej tabeli) należy zdefiniować jeden klucz główny jednoznacznie wskazujący na pozostałe atrybuty wszystkich obiektów danego typu.

W przypadku projektu bazy wydawnictwa dla każdej encji utworzymy dodatkowy atrybut o nazwie id_nazwa obiektu. Wartościami tego atrybutu będą niepowtarzalne, automatycznie generowane przez MySQL liczby całkowite.

Zauważyliśmy już, że z każdym schematem relacji związany jest pewien zbiór zależności funkcyjnych określanych dużą literą F. Dla zbioru atrybutów relacji r przyjmuje się założenie, na podstawie którego dla dowolnego podzbioru zbioru wszystkich atrybutów relacji r można określić zależności funkcyjne, niezależnie od tego, w jakich relacjach atrybuty te występują.

Lemat 2. Założenie o relacji uniwersalnej

Dla zadanego zbioru atrybutów R istnieje jedna relacja r o schemacie R = {A1, A2, …, An}, a wszystkie relacje s o schemacie S = {B1, B2, …, Bm} (SR) uzyskiwane są w wyniku projekcji relacji r. Zależność funkcyjna XY wynika logicznie ze zbioru zależności funkcyjnych F, co zapisujemy F |= X → Y, jeżeli każda relacja r spełniająca wszystkie zależności ze zbioru F spełnia również zależność XY.

Na podstawie lematu 2. możemy mówić o istnieniu uniwersalnego zbioru zależności funkcyjnych dla relacji r. Lemat 2., choć ma podstawowe znaczenie dla teorii relacyjnych baz danych, nie jest spełniony automatycznie i w praktyce nie zawsze jest przyjmowany, ponieważ wymagałoby to dodawania do schematu relacji dodatkowych atrybutów, zbędnych z punktu widzenia użytkownika bazy danych.

Na przykładzie relacji reprezentowanej przez tabelę items zauważyliśmy, że dla każdej relacji można wskazać niepusty zbiór zależności funkcyjnych spełnianych przez tę relację. Dodatkowo ze zbioru zależności funkcyjnych, przyjętego na podstawie natury odzwierciedlanej przez relację rzeczywistości, można wyprowadzać nowe zależności.

Zagadnienie generowania pochodnych zależności funkcyjnych zostało opracowane przez Armstronga, który udowodnił, że możliwe jest wyprowadzanie jednych zależności funkcyjnych z drugich na podstawie analizy czysto syntaktycznej, bez konieczności odwoływania się do samej relacji.

Zagadnienie rozkładalności schematów relacji i związanie z nim aksjomaty Armstronga są opisane w dalszej części odcinka.

Lemat 2. jest podstawą definicji domknięcia zbioru zależności funkcyjnych, wykorzystywanej w dalszej części odcinka.

Definicja 11. Domknięcie zależności funkcyjnych

Domknięciem zbioru zależności funkcyjnych F nazywamy zbiór wszystkich zależności logicznych wynikających logicznie z zależności funkcyjnych F. Domknięcie zależności funkcyjnych oznacza się symbolem F+.

Analizując model bazy danych, należy brać pod uwagę zbiór wszystkich zależności funkcyjnych występujących pomiędzy atrybutami, a więc zbiór F+, nie F.

Posługując się definicją 11., możemy rozszerzyć podane wcześniej definicje nadklucza i klucza relacji:

Definicja 9a. Nadklucz

Nadkluczem relacji r o schemacie R = {A1, A2, …, An} i zbiorze zależności funkcyjnych F nazywamy dowolny zbiór atrybutów XR taki, że XRF+.

Definicja 10a. Klucz

Kluczem relacji r o schemacie R = {A1, A2, …, An} i zbiorze zależności funkcyjnych F nazywamy każdy minimalny nadklucz, tj. dowolny zbiór atrybutów XR taki, że XRF+ i że dla żadnego YX, YX nie zachodzi YRF+.

Grupowanie danych w tabelach

Tworząc projekt przykładowej bazy danych, założyliśmy, że dane powinny być przechowywane w kilku powiązanych ze sobą tabelach. Innymi słowy, uznaliśmy, że relacyjny model bazy danych jest najlepszym wyborem w naszej sytuacji. I chociaż prawie na pewno mieliśmy rację, należy wiedzieć, że oprócz relacyjnych baz danych szeroko wykorzystywane są jednorodne i obiektowe bazy danych. Przykładem jednorodnej bazy danych (ang. Flat File) jest arkusz z danymi programu Microsoft Excel czy pojedyncza tabela utworzona w Wordzie.

Zalety grupowania danych w odrębnych tabelach najłatwiej opisać, porównując relacyjny i jednorodny model baz danych.

Podstawowym powodem grupowania danych o obiektach różnych typów w odrębnych tabelach jest nadmiarowość danych przechowywanych w bazach jednorodnych. O ile bardzo proste (opisujące niewielką liczbę atrybutów obiektów tego samego typu) bazy danych można tworzyć, implementując model jednorodny, to każda próba zapisania w takiej bazie większej liczby danych o obiektach różnego typu (np. książkach i autorach) skończy się wielokrotnym przechowywaniem tych samych danych w bazie. Innymi słowy, te same wartości zapisane będą nie raz, ale wiele razy, co nie tylko niepotrzebnie zwiększa rozmiar bazy danych, ale niekorzystnie wpływa na wydajność i utrudnia zarządzanie bazą. Gdybyśmy na przykład przechowywali dane o autorach razem z danymi o książkach, informacje o imieniu, nazwisku, adresie itd. każdego autora musiałyby zostać zapisane tyle razy, ile książek napisał dany autor.

Konsekwencjami nadmiarowości danych są:

  1. Anomalie występujące podczas modyfikacji danych. W modelu jednorodnym stosunkowo trudno jest zachować spójność i adekwatność danych. Gdyby w zmodyfikowanej (uzupełnionej o informacje o autorach) tabeli książka należało uaktualnić nasze uwagi o autorze, to informację tę musielibyśmy modyfikować tyle razy, ile książek napisał dany autor. W nieco bardziej skomplikowanych przypadkach mogłoby się okazać, że jedna drobna zmiana pociąga za sobą modyfikacje kilkudziesięciu czy kilkuset rekordów. A implementacja mechanizmu niedopuszczającego do sytuacji, w której dane o autorze zmienimy 66 razy, a w 13 komórkach pozostaną niezmienione, może okazać się dość skomplikowana.

  2. Anomalie występujące podczas dodawania danych. Kolejnym ograniczeniem modelu jednorodnego jest konieczność wprowadzania nowych danych o jednym obiekcie do wielu rekordów. Gdybyśmy w omawianym przypadku w końcu poznali adres e-mail jednego z autorów, SQL Server musiałby wpisać tę informację w liczbę pól równą liczbie książek tego autora.

  3. Anomalie występujące podczas usuwana danych to kolejny powód, dla którego powinno się grupować w osobnych tabelach dane opisujące obiekty różnego rodzaju. W omawianym przykładzie nie byłoby możliwości wprowadzenia do bazy informacji o autorze, którego książek wydawnictwo nie ma jeszcze w swojej ofercie. I odwrotnie — gdybyśmy usunęli dane o wszystkich książkach danego autora, automatycznie utracilibyśmy również dane o jego nazwisku, imieniu itd.

Kwestia grupowania danych w odrębnych tabelach jest dokładnie opisana w dalszej części odcinka; w tym miejscu podaję jedynie intuicyjne wskazówki, według których przygotujemy model encja/relacja przykładowej bazy danych.

Zagadnieniu grupowania danych w tabelach poświęcony jest podrozdział „Normalizacja”. Celem normalizacji jest wyeliminowanie wszystkich zależności funkcyjnych nie od klucza.

Określanie związków (relacji) zachodzących między encjami

Dotychczas przygotowaliśmy cztery zbiory atrybutów opisujących poszczególne typy obiektów. Jednak w bazie danych oprócz atrybutów opisujących obiekty (atrybutów jednoargumentowych) powinny znaleźć się informacje o związkach zachodzących pomiędzy obiektami różnego typu (atrybuty wieloargumentowe).

W przykładowej bazie danych, tak jak w zdecydowanej większości relacyjnych baz danych, obiekty poszczególnych typów powiązane są ze sobą za pomocą związków dwuargumentowych, np.: autor napisał książkę, wydawnictwo wydało książkę, książka została kupiona przez klienta.

Wyróżnia się trzy typy związków dwuargumentowych:

  1. Związek wieloznaczny typu „wiele do wielu”. Powiązania tego typu podczas implementacji projektu muszą być zamienione na równoważne związki typu „jeden do wielu”. W przykładowej bazie danych w ten sposób powiązane są encje książka i autor (jeden autor może napisać wiele książek i jedna książka może mieć wielu autorów).

  2. Związek jednoznaczny typu „jeden do wielu”. Związki jednoznaczne w przeciwieństwie do związku wieloznacznego są bezpośrednio reprezentowane w bazie danych poprzez powiązanie odpowiednich atrybutów kluczowych obiektów.

  3. Związek jednoznaczny typu „jeden do jednego”. Ten typ powiązania odpowiada funkcji jedno-jednoznacznej. Powiązania tego typu z reguły są wynikiem optymalizacji bazy danych, ponieważ na etapie projektowania najczęściej tak powiązane atrybuty opisują obiekty tego samego typu.

Sklasyfikujmy typ związków zachodzących pomiędzy przykładowymi tabelami.

  1. Każda książka może być napisana przez wielu autorów i jednocześnie każdy autor może napisać dowolną liczbę książek — tabele autor i książka łączy związek typu „wiele do wielu”.

  2. Każda książka może być wydana przez dokładnie jedno wydawnictwo, ale każde wydawnictwo może wydać dowolną liczbę książek — tabele wydawnictwo i książka łączy związek typu „jeden do wielu”.

  3. Każdy klient może kupić dowolną liczbę książek i ta sama książka może być kupiona przez wielu klientów — tabele klient i książka łączy związek typu „wiele do wielu”.

Jako pierwszy zaimplementujemy związek zachodzący pomiędzy tabelami wydawnictwo i książka — do tabeli książka dodamy kolumnę przechowującą wartości kluczy głównych tabeli wydawnictwo (rysunek 9.4).

Rysunek 9.4. Połączenie tabel wydawnictwo i książka

Ponieważ związki wielo-wieloznaczne nie mogą być bezpośrednio odwzorowane w relacyjnych bazach danych, do połączenia pozostałych tabel musimy utworzyć dodatkową tabelę łączącą odpowiednie tabele. Za pomocą tabeli przechowującej jedynie informacje o powiązanych ze sobą wartościach kluczy głównych obu tabel połączymy związkami typu „jeden do wielu” obie tabele nadrzędne (rysunek 9.5).

Rysunek 9.5. Połączenie tabel autor i książka

Łącząc tabele klient i książka, możemy dodatkowo wykorzystać fakt, że i tak utworzymy tabelę łącznikową. W takiej tabeli oprócz kluczy głównych obu tabel możemy przechowywać dodatkowe informacje opisujące bezpośrednio samą operację kupna książki, takie jak data transakcji czy liczba zakupionych egzemplarzy (rysunek 9.6).

Rysunek 9.6. Połączenie tabel klient i książka poprzez tabelę zamówienie

W tym uproszczonym modelu pojedyncze zamówienie może być złożone przez dokładnie jednego klienta, a w ramach takiego zamówienia możliwe jest wyszczególnienie tylko jednej pozycji (tytułu). W tym przypadku operacja zamówienia różnych książek przez tego samego klienta w tym samym czasie musi być zapisana jako zbiór pojedynczych zamówień. Rozwiązaniem tego problemu byłoby np. utworzenie powiązanych związkiem typu „jeden do wielu” tabel zamówienie i szczegóły zamówienia.

Końcowa lista encji bazy danych wydawnictwo wygląda następująco:

książka {id_książki, id_wydawnictwa, tytuł, numer ISBN, liczba stron, rok wydania, seria wydawnicza, dołączony nośnik, numer ISBN oryginału, notka redakcyjna, uwagi}

autor {id_autora, imię, nazwisko, miasto, kod, ulica i nr domu, numer telefonu, e-mail, bank, numer konta bankowego, uwagi}

wydawnictwo {id_wydawnictwa, nazwa, kraj, miasto, kod, ulica i nr domu, osoba kontaktowa, prezes, numer telefonu, e-mail, bank, numer konta bankowego, uwagi}

klient {id_klienta, imię, nazwisko, e-mail, data urodzenia, wykształcenie, stanowisko, miasto, uwagi}

autor_książka {id_autora, id_książki)

zamówienie (id_zamowienia, id_klienta, id_książki, data, egzemplarzy}

Normalizacja

Normalizacja modelu bazy danych polega na przekształcaniu danych do kolejnych, coraz wyższych postaci normalnych (schematów relacji spełniających coraz bardziej restrykcyjne kryteria logiczne). Każda wyższa postać normalna musi spełniać kryteria wszystkich postaci niższych. Tak więc baza danych spełniająca trzecią postać normalną z definicji spełnia również normy pierwszej i drugiej postaci normalnej. Aby uniknąć problemów związanych ze wstawianiem, modyfikowaniem i usuwaniem danych, dla większości baz danych wystarcza znormalizowanie projektu do trzeciej postaci normalnej (3PN).

Pierwsza postać normalna 1PN

Mówimy, że encja znajduje się w pierwszej postaci normalnej, jeśli wszystkie jej atrybuty mają pojedynczą, niepodzielną wartość. Jeśli jakikolwiek atrybut składa się z kilku wartości, encja nie znajduje się w pierwszej postaci normalnej. Innymi słowy, baza danych spełnia założenia pierwszej postaci normalnej, jeżeli posiada tylko pola zawierające wartości atomowe (niepodzielne). Gdyby wszystkie tabele bazy danych spełniały pierwszą postać normalną, to związki typu „jeden do wielu” byłyby określone zawsze pomiędzy kilkoma tabelami (nigdy w obrębie jednej tabeli), a związek typu „jeden do jednego” mógłby zawierać się w jednej tabeli.

Pierwsza postać anormalna

Encje opisywane przez atrybuty posiadające złożone wartości (encję niespełniającą wymogów 1PN) nazywane są encjami znajdującymi się w pierwszej postaci anormalnej. Chociaż możliwe jest świadome zaplanowanie i utworzenie tabeli reprezentującej taką encję, to w większości przypadków jest to wynikiem błędu projektanta.

Druga postać normalna 2PN

Encja odpowiada wymogom drugiej postaci normalnej, jeżeli spełnia dwa założenia:

  1. Tabela jest w 1PN.

  2. Kolumny niewchodzące w skład klucza głównego są zależne od całego klucza (a nie od dowolnej jego części).

Tak więc encja jest w drugiej postaci normalnej, jeśli znajduje się w pierwszej postaci normalnej i wszystkie zależne funkcyjnie atrybuty są zależne od całego niepowtarzalnego identyfikatora (klucza głównego). Jeśli jakikolwiek atrybut nie jest całkowicie zależny od klucza głównego, należy zmodyfikować projekt bazy danych. Atrybuty takie normalizuje się albo poprzez odnalezienie encji, którą bezpośrednio opisują te atrybuty, albo przez utworzenie dodatkowej encji, do której atrybut powinien zostać dodany.

Trzecia postać normalna 3PN

Encja jest w 3PN, jeżeli wszystkie atrybuty spełniają założenia dwóch poprzednich postaci, a ponadto każdy z atrybutów nienależących do klucza głównego nie zależy funkcyjnie od jakiegokolwiek nadklucza. Innymi słowy, encja znajduje się w trzeciej postaci normalnej, kiedy jest już w drugiej postaci normalnej i żadne nieidentyfikujące atrybuty nie są zależne od innych nieidentyfikujących atrybutów (nie występują zależności przechodnie). Atrybuty, które są zależne od innych nieidentyfikujących atrybutów, są normalizowane przez przeniesienie do nowej encji zarówno zależnego atrybutu, jak i atrybutu, od którego jest on zależny.

Przekształcenie modelu do 3PN często sprowadza się do wyodrębnienia encji do tej pory traktowanych jako zbiory atrybutów opisujących encje nadrzędne i utworzenia dla nich odrębnych tabel słownikowych.

Tworzenie tabel słownikowych

Tabele słownikowe to tabele pomocnicze przechowujące wartości poszczególnych atrybutów opisujących jeden z typów obiektów. Na tabele słownikowe nadają się te atrybuty, których zakres dopuszczalnych wartości jest ściśle określony. Na przykład atrybut może przyjąć jedną z następujących wartości: od A do Z, Tylko dla orłów, Nie panikuj!, Pisma i traktaty. Tworząc odrębną tabelę przechowującą dopuszczalne wartości tego atrybutu:

  1. Ułatwiamy użytkownikowi korzystanie z bazy danych. Zamiast wielokrotnie wpisywać zwrot nie panikuj!, użytkownik będzie mógł wybrać go z listy wszystkich serii wydawniczych.

  2. Zmniejszamy ryzyko utraty spójności danych. Wprowadzając nowe informacje, użytkownicy często popełniają błędy i np. zamiast nie panikuj! mogliby wpisać: nie panikuj, nie panikój! itp. Gdybyśmy chcieli na podstawie takich danych przygotowywać statystyki, otrzymane wyniki byłyby nieprawdziwe.

  3. Poprawiamy wydajność bazy danych. Przenosząc wartości tekstowe pozą główną tabelę i wiążąc ją za pomocą kluczy zdefiniowanych jako liczby całkowite, poprawiamy wydajność operacji odczytu oraz modyfikacji danych w tabeli. Dodatkowo niektóre raporty będą mogły być obliczane na podstawie zawartości tabeli słownikowej i nie będą wymagały odczytu danych przechowywanych w tabeli głównej.

W przykładowej bazie danych można wyodrębnić tabele słownikowe dla wszystkich czterech początkowo wyróżnionych encji.

Dla tabeli książka należy zdefiniować dwie tabele słownikowe: tabelę seria wydawnicza i nośnik. Projektując tabele słownikowe, będziemy kierować się uwagami zawartymi wcześniej w bieżącym odcinku:

seria wydawnicza: {id_serii, seria, uwagi}

nośnik: {id_nośnika, nośnik, uwagi}

Tabele słownikowe powiązane są z tabelą nadrzędną związkiem typu „jeden do wielu” (książka może być wydana tylko w jednej serii, ale w ramach każdej serii można wydać dowolną liczbę książek). Związki tego typu implementuje się, dodając do listy kolumn tabeli nadrzędnej tabelę zawierającą klucz główny tabeli słownikowej, czyli tworząc klucz zewnętrzny dla tej tabeli. Końcowy projekt tabeli książka będzie wyglądał następująco:

książka: {id_książki, tytuł, isbn, liczba_stron, rok_wydania, isbn_oryginału, notka_redakcyjna, uwagi, id_serii, id_nośnika}

Dla tabeli autor można znaleźć tylko jeden atrybut nadający się do przeniesienia do tabeli słownikowej — miasto. Dodatkową korzyścią z utworzenia tej tabeli słownikowej będzie to, że będzie ona użyteczna podczas projektowania tabel wydawnictwo i klient:

miasto: {id_miasta, miasto, mieszkańców, uwagi}

autor: {id_autora, imię, nazwisko, kod, ulica_nr_domu, telefon, e-mail, bank, konto, uwagi, id_miasta}

Projektując tabelę wydawnictwo, na liście atrybutów opisujących wydawnictwo umieściliśmy m.in. miasto, kraj, osobę kontaktową i prezesa. Każdy z tych atrybutów powinien zostać przeniesiony poza tabelę nadrzędną. Chwilę uwagi powinniśmy poświęcić dwóm ostatnim z wymienionych atrybutów — prawdopodobnie lista danych o osobie kontaktowej (takich jak imię czy nazwisko) będzie identyczna z listą danych o prezesie danego wydawnictwa. Skoro tak, to zamiast tworzyć dwie tabele, możemy utworzyć jedną, a do listy kolumn dodamy wtedy kolumnę określającą, czy dana osoba jest osobą kontaktową, czy prezesem wydawnictwa:

osoba: {id_osoby, imię, nazwisko, telefon, e-mail, prezes, uwagi}

kraj: {id_kraju, kraj, uwagi}

wydawnictwo: {id_wydawnictwa, nazwa, kod, ulica_nr_domu, telefon, e-mail, bank, konto, uwagi, id_osoby, id_prezesa, id_kraju, id_miasta}

Ostatnią tabelą nadrzędną jest tabela klient. Na liście atrybutów, które powinny być przeniesione do tabel słownikowych, znajdują się wykształcenie, stanowisko oraz miasto. Pierwsze dwie tabele będą typowymi tabelami słownikowymi, przechowującymi wyłącznie listę pojedynczych wartości, trzecia tabela została utworzona już wcześniej:

wykształcenie: {id_wykształcenia, wykształcenie}

stanowisko: {id_stanowiska, stanowisko}

klient: {id_klienta, imię, nazwisko, e-mail, data_urodzenia, uwagi, id_wykształcenia, id_stanowiska, id_miasta}

Algorytmy sprowadzania relacji do wyższej postaci normalnej

Do tej pory, częściowo dzięki intuicji, utworzyliśmy poprawny model bazy danych i sprowadziliśmy go do 3PN. Bieżący punkt zawiera bardziej formalne podejście do zagadnienia normalizacji modeli baz danych.

Pierwszą czynnością, którą należy wykonać, przekształcając relację do poprawnych postaci, jest sprawdzenie, czy dana zależność funkcyjna wynika logicznie ze zbioru zależności funkcyjnych F.

Definicja 12. Domknięcie tranzytywne

Domknięciem tranzytywnym zbioru XR względem zbioru zależności funkcyjnych F jest zbiór atrybutów X+ = {AR: XAF+}. Oznacza to, że domknięcie tranzytywne jest zbiorem wszystkich tych atrybutów, których wartości są zdeterminowane przez wartości atrybutów należących do zbioru X.

Twierdzenie 15. Wynikanie logiczne zależności funkcyjnych

Zależność funkcyjna XY wynika ze zbioru zależności funkcyjnych F wtedy i tylko wtedy, gdy zbiór atrybutów Y należy do domknięcia tranzytywnego zbioru X, co zapisujemy XYF+ wtedy i tylko wtedy, gdy YX+.

Na mocy twierdzenia 15. sprawdzenie, czy dana zależność funkcyjna wynika logicznie ze zbioru zależności F, sprowadza się do wyznaczenia domknięcia tranzytywnego zbioru X. Do wyznaczenia domknięcia tranzytywnego zbioru X posłużymy się poniższym algorytmem.

Konstruujemy następujący ciąg zbiorów:

  1. X0 = X,

  2. Xi+1 = Xi plus zbiór atrybutów A takich, że istnieje zależność funkcyjna YZF, gdzie AZ oraz YXi,

  3. krok 2. powtarzamy, dopóki nie zajdzie równość Xi = Xi+1. Wtedy szukany zbiór X+ = Xi.

Przykład:

Dla relacji r o schemacie R = {A, B, C, , E} i zbiorze zależności funkcyjnych F = {B, EC, CA, A, B, CD, B, CD, B, DE} i dla zbioru X = {B, D} wyznaczamy:

X0 = {B, D},

X1 = {B, D, E}; ponieważ istnieje zależność B, DE spełniona przez wygenerowane już atrybuty zbiory Y, mamy prawo wygenerować wszystkie atrybuty zbioru Z, w tym wypadku E,

X2 = {B, C, D, E} ponieważ istnieje zależność B, EC,

X3 = {A, B, C, D, E} ponieważ istnieje zależność CA.

Okazuje się, że zbiór X+ = R, co oznacza, że zbiór X = {B, D} jest nadkluczem relacji. Dodatkowo, ponieważ nie istnieje żaden nadklucz będący podzbiorem zbiory X, zbiór ten jest kluczem relacji.

Podczas tworzenia modelu E/R przykładowej bazy danych dzieliliśmy atrybuty pomiędzy odrębne tabele, kierując się intuicyjną wskazówką, mówiącą, że atrybut powinien opisywać encję, przy której się go umieszcza. Istnieją określone warunki, przy których taki podział (rozkład) relacji jest logicznie poprawny.

Zależności funkcyjne z istniejącego zbioru zależności dla danej relacji możemy wyprowadzać, posługując się aksjomatami Armstronga.

Aksjomat 1. Zwrotność zależności funkcyjnych

Dla relacji r o schemacie R = {A1, A2, …, An} i zbiorze zależności funkcyjnych F będącym podzbiorem wszystkich zależności funkcyjnych o postaci XY (X, YR), jeżeli YX, to XYF+.

Aksjomat 2. Poszerzalność zależności funkcyjnych

Dla relacji r o schemacie R = {A1, A2, …, An} i zbiorze zależności funkcyjnych F będącym podzbiorem wszystkich zależności funkcyjnych o postaci XY (X, YR), jeżeli XYF+, to XZYZF+.

Aksjomat 3. Przechodniość zależności funkcyjnych

Dla relacji r o schemacie R = {A1, A2, …, An} i zbiorze zależności funkcyjnych F będącym podzbiorem wszystkich zależności funkcyjnych o postaci XY (X, YR), jeżeli XYF+ i y → ZF+, to XZF+.

Z aksjomatów Armstronga można wyprowadzić kolejne trzy reguły przekształcania zależności funkcyjnych.

Aksjomat 4. Pseudoprzechodniość zależności funkcyjnych

Dla relacji r o schemacie R = {A1, A2, …, An} i zbiorze zależności funkcyjnych F będącym podzbiorem wszystkich zależności funkcyjnych o postaci XY (X, YR), jeżeli XYF+ i YWZF+, to XW → Z ∈ F+.

Aksjomat 5. Addytywność zależności funkcyjnych

Dla relacji r o schemacie R = {A1, A2, …, An} i zbiorze zależności funkcyjnych F będącym podzbiorem wszystkich zależności funkcyjnych o postaci XY (X, YR), jeżeli XYF+ i XZF+, to XYZF+.

Aksjomat 6. Dekompozycyjność zależności funkcyjnych

Dla relacji r o schemacie R = {A1, A2, …, An} i zbiorze zależności funkcyjnych F będącym podzbiorem wszystkich zależności funkcyjnych o postaci XY (X, YR), jeżeli XYZF+, to XYF+ i XZF+.

Ponieważ z powyższych aksjomatów wynika, że zbiór F+ może zostać utworzony (wygenerowany) na podstawie różnych zbiorów zależności funkcyjnych, wprowadzono pojęcie minimalnego generatora zbioru F+.

Definicja 13. Minimalny generator domknięcia tranzytywnego

Minimalnym generatorem zbioru F+ nazywamy taki najmniejszy podzbiór F0 zbioru F (F0 Í F), dla którego F0+ = F+. Poprzednio zaznaczyłem, że proces normalizacji polega na wyeliminowaniu ze zbioru F+ wszystkich zależności funkcyjnych nie od klucza. Teraz widzimy, że polega to na wyznaczeniu minimalnego generatora zbioru F+.

Lemat 3. Rozkład relacji

Rozkład relacji jest poprawny logicznie wtedy i tylko wtedy, gdy w jego wyniku zachowane są zarówno informacje, jak i zależności funkcyjne istniejące w relacji początkowej.

Definicja 14. Poprawny rozkład relacji

Niech πZ(F) = {XYF+: XYZ}. Rozkład Q relacji R i S, co zapisujemy Q = RS, zachowuje:

  1. informacje, jeżeli dla każdej relacji q spełniającej zależności funkcyjne F zachodzi równość q = πR(q) ⊕ πS(q),

  2. zależności funkcyjne, jeżeli zachodzi równość F+ = (πR(F) ∪ πS(F))+.

Twierdzenie 16. Rozkład bez straty danych

Jeżeli relacja r o schemacie R = {A1, A2, …, An} i zbiorze zależności funkcyjnych F rozkłada się bez straty danych na dwa schematy R = XY i R = XZ, gdzie XYZ = {A1, A2, …, An} i YZ = ∅, to XY ∈ F+ lub X → ∈ F+. Ponieważ nie każdy rozkład bez straty zależności jest rozkładem bez straty danych, w procesie normalizacji dąży się do rozłożenia schematu relacji na składowe niezależne.

Definicja 15. Rozkład na składowe niezależne

Mówimy, że relacja r o schemacie R = {A1, A2, …, An} i zbiorze zależności funkcyjnych F rozkłada się na relacje niezależne s o schemacie S = {B1, B2, …, Bm} i zbiorze zależności funkcyjnych G oraz t o schemacie T = {C1, C2, …, Cl} i zbiorze zależności funkcyjnych H wtedy i tylko wtedy, gdy:

  1. B C = A,

  2. F+ = (G H)+,

  3. każda relacja rR = {A1, A2, …, An} rozkłada się bez straty danych na dwie relacje: sS = {B1, B2, …, Bm} i tT = {C1, C2, …, Cl}.

Na podstawie wymienionych praw możliwe jest przekształcenie schematu relacji na dowolną postać normalną. Postacie wyższe od 3PN (postać normalna Boyce’a-Codda oraz 4PN) są stosunkowo rzadko spotykane w produkcyjnych bazach danych.

Metoda dekompozycji Codda-Fagina

Metoda dekompozycji polega na kolejnym wykonaniu poniższych operacji.

Określenie zbioru atrybutów kluczowych relacji r o schemacie R = {A1, A2, …, An}.

Wykrycie niepełnych zależności funkcyjnych i rozłożenie relacji r o schemacie R = {A1, A2, …, An} na zbiór relacji znajdujących się w 2PN.

Określenie zbioru atrybutów kluczowych każdej z otrzymanych relacji.

Wykrycie tranzytywnych (przechodnich) zależności funkcyjnych w każdej z otrzymanych relacji i sprowadzenie ich do 3PN.

Metoda ta gwarantuje rozkład z zachowaniem danych, ale w pewnych sytuacjach może prowadzić do utraty zależności funkcyjnych.

Uproszczona metoda Bernsteina

Normalizacja projektu bazy danych według metody Bernsteina polega kolejno na:

  1. wyeliminowaniu zbędnych atrybutów z lewych stron zależności funkcyjnych;

  2. znalezieniu minimalnego generatora zbioru zależności funkcyjnych otrzymanego w poprzednim punkcie (na podstawie wymienionych aksjomatów eliminuje się te zależności, które można wyprowadzić z zależności podstawowych);

  3. takim podziale znalezionego minimalnego generatora na rozłączne zbiory zależności, aby wszystkie otrzymane zależności cząstkowe miały identyczne lewe strony;

  4. pogrupowaniu tych z otrzymanych w poprzednim punkcie zależności, dla których zarówno X à Y Î F+, jak i Y à X Î F+;

  5. skonstruowaniu schematów relacji dla wszystkich otrzymanych grup zależności funkcyjnych; w tak otrzymanym schemacie relacji atrybuty znajdujące się po lewej stronie zależności funkcyjnych pełnią funkcję klucza głównego.

Metoda Niekludowej-Calenki

Postępowanie według algorytmu Niekludowej-Calenki gwarantuje, w przeciwieństwie do poprzednio przedstawionych metod, uzyskanie minimalnego rozkładu poprawnego, tj. rozkładu zachowującego zarówno zależności funkcyjne, jak i dane. Kolejno należy przeprowadzić następujące operacje:

  1. Utworzyć zbiór wszystkich atrybutów kluczowych.

  2. Dokonać projekcji zależności funkcyjnych na otrzymany zbiór atrybutów kluczowych.

  3. Usunąć ze zbioru atrybutów kluczowych wszystkie zbędne atrybuty.

  4. Usunąć z pozostałego zbioru wszystkie atrybuty zależne od atrybutów kluczowych.

  5. Utworzyć zbiór U1, dołączając do niego atrybuty występujące po prawej stronie zależności funkcyjnych usuniętych w punkcie 4. Jeżeli zbiór U1 będzie równy zbiorowi wszystkich atrybutów, to otrzymano schemat będący w 3PN, co kończy działanie algorytmu. W przeciwnym razie należy przejść do punktu 6.

  6. Sprawdzić, czy zachodzi równość U1 = {A}, gdzie A jest jedną z zależności funkcyjnych występujących w zbiorze otrzymanym w punkcie 4. Jeżeli zbiór U1 będzie równy zbiorowi wszystkich atrybutów, to należy rozłożyć schemat relacji według metody Bernsteina, w przeciwnym razie należy powtórzyć kroki od 1. do 5.

Postać normalna Boyce’a-Codda

Bardziej restrykcyjna niż 3PN jest postać normalna Boyce’a-Codda. Jeżeli schemat relacji spełnia warunki tej postaci, niemożliwe jest określenie wartości jednych danych na podstawie innych danych, czyli w bazie nie ma informacji nadmiarowych (redundantnych). Ponieważ głównym celem projektanta jest opracowanie wydajnych i poprawnych logicznie modeli bazy danych, w praktyce rzadko doprowadza się normalizację projektu do postaci zgodnej z wymogami postaci normalnej Boyce’a-Codda. Ponadto niektórych relacji o bardzo prostych schematach nie można rozłożyć (przekształcić) bez straty zależności funkcyjnych na relacje znajdujące się w postaci Boyce’a-Codda.

Encja jest w postaci normalnej Boyce’a-Codda, jeżeli dla każdej zależności X Y F+, gdzie X R, A R albo zachodzi AX (zależność trywialna), albo X jest nadkluczem.

 

PHP, MySQL i MVC. Tworzenie witryn WWW opartych na bazie danych

PHP, MySQL i MVC.
Tworzenie witryn WWW
opartych na bazie danych

PHP i MySQL. Tworzenie stron WWW. Vademecum profesjonalisty.

PHP i MySQL. Tworzenie stron WWW.
Vademecum profesjonalisty.

PHP i MySQL. Projekty do wykorzystania

PHP i MySQL.
Projekty do wykorzystania

Nowości Helionu

Statystyki

Użytkowników : 766
Artykułów : 513
Zakładki : 28
Odsłon : 10527724