#43
Rozdział 2.
Projektowanie bazy danych

Projektowanie bazy danych musimy podzielić na kilka etapów. Na początku należy określić, w jakim celu tworzymy naszą bazę danych. Pozwoli to odpowiedzieć na pytanie, jakie dane mają być w bazie przechowywane. Następnie należy określić powiązania zachodzące pomiędzy naszymi danymi i sposób ich organizacji. Kolejny krok to stworzenie bazy danych przy pomocy instrukcji SQL, według uprzednio przygotowanego projektu - o tym będzie rozdział 3. W tym rozdziale zajmiemy się elementami projektu, które muszą być ustalone przed pisaniem kodu programu. Są tu do rozwiązania ważne problemy, które decydują w przyszłości o tym, że baza danych spełni nasze oczekiwania, będzie wydajna i integralna.
Przed przystąpieniem do pracy chciałbym jeszcze uprzedzić początkujących projektantów, że projektowanie bazy danych nie jest ani szybkie, ani łatwe. W pracy tej - poza sztywnymi regułami - pojawia się miejsce dla inicjatywy z pogranicza magii i sztuki. Jest też pewne, że pierwsza baza danych nie będzie doskonała. Nawet starzy wyjadacze ciągle się uczą i stwierdzają, że można zawsze coś ulepszyć.
Dysponujemy jednak pewną metodyką przydatną podczas tworzenia baz danych. Modelowanie związków encji to proces, podczas którego wyznaczany jest podział danych na tabele lub encje oraz określane są relacje między nimi. Normalizacja to metoda organizacji danych w tabelach, tak aby spełnić reguły postaci normalnych. Postacie normalne wyznaczają sprawdzone standardy organizacji danych zapewniające eliminację problemów z integralnością danych.

==============
RADA
Reguły wspomagające projektowanie bazy danych są podobne bardziej do zasad gramatycznych, niż do ścisłych, sformalizowanych praw matematycznych. Innymi słowy, należy je traktować bardziej jako wskazówki, niż niezmienne prawa. Wcale nie powinny więc dziwić przypadki, kiedy w projekcie postępujemy wbrew tym zasadom, kierując się intuicją czy zdrowym rozsądkiem. Cały kunszt doświadczonego projektanta polega na tym, że wie on, kiedy te zasady mogą być złamane.
===============

Zanim omówię metody projektowania baz danych, przypomnę pojęcia wprowadzone w poprzednim rozdziale.
#44
Struktura bazy danych

Najistotniejszym postulatem, wynikającym z rozdziału pierwszego, było pokazanie, że istota relacyjnych baz danych opiera się na gromadzeniu danych w tabelach. Stanowią one źródło danych dla zapytań, te z kolei zwracają wyniki jako tabele. Poniżej zebrano podstawowe zasady modelu relacyjnego:
* Dane prezentowane są użytkownikom w formie tabel.
* Komórki tabel zawierają wartości niepodzielne na mniejsze porcje informacji (ang. atomic values).
* Każdy wiersz tabeli posiada wartość (lub grupę wartości), która go jednoznacznie identyfikuje (klucz główny).
* W przypadku określenia relacji między tabelami dane z kolumny połączonej relacją muszą mieć swoje odpowiedniki w kolumnie w drugiej tabeli.
Trzecia i czwarta zasada jest często w bazie danych opcjonalna. To znaczy, że nie wszystkie wiersze w tabeli muszą być unikalne oraz nie ma konieczności określania relacji między tabelami. Jednak na dłuższą metę stosowanie tych reguł jest zalecane, gdyż zapewnia zachowanie spójności danych.
Wartość lub grupa wartości jednoznacznie identyfikująca każdy wiersz nazywana jest kluczem głównym (ang. primary key). Odpowiadająca mu kolumna lub grupa kolumn w drugiej tabeli nazywana jest kluczem obcym (ang. foreign key), a takie powiązanie kolumn z dwóch tabel nazywamy relacją.
Kolejnym pojęciem związanym z projektem bazy danych jest schemat. Schemat określa strukturę danych, w tym listę tabel wraz z charakterystyką poszczególnych kolumn i kluczy. Opis schematu bazy danych zawiera nazwę tabeli z następującą dalej listą kolumn wypisaną w nawiasach, jak w przykładzie:

jakaś_tabela (klucz_glówny, kolumna1, kolumna2, kolumna3)

Dobre i złe bazy danych

Opis metod projektowania chciałbym rozpocząć od wskazania kilku cech, które powodują, że jedna baza jest uważana za dobrą, inna za złą. Mam nadzieję, że w trakcie pracy nauczysz się rozpoznawać te dobre i złe właściwości, aby w twoich bazach zawsze przeważały te dobre.

Charakterystyka dobrej bazy danych

Trudno nie rozpoznać dobrej bazy danych. Praca z niąjest szybka i niezawodna, łatwo znajdujemy potrzebne nam dane, które zachowują spójność, mimo ciągłej aktualizacji.
#45
Oczywiście, chociaż dobra baza spełnia wszystkie te kryteria jednocześnie, to przede wszystkim zachowuje między nimi równowagę. Na przykład przechowywanie danych w jednej tabeli zapewnia łatwy dostęp do nich, jednak za cenę słabej wydajności i słabej integralności - powoduje to bowiem duplikowanie się wielu informacji. Z kolei zbytnie rozdzielenie danych pomiędzy wiele tabel zapewnia zachowanie spójności, ale utrudnia dostęp do danych, zmuszając do pisania bardziej rozbudowanych i skomplikowanych zapytań, może też mieć negatywny wpływ na szybkość pracy. Tak samo projektowanie bazy tylko pod kątem wydajności, kosztem jej spójności i dobrej struktury organizacyjnej, też nie prowadzi do satysfakcjonujących rezultatów.

Do projektanta należy znalezienie właściwej recepty na dobrą bazę danych. Musi on mieć na względzie wszystkie trzy ważne przymioty: organizację, spójność i wydajność bazy danych oraz wyważyć je odpowiednio do tworzonej aplikacji. Ostateczną oceną bazy danych jest to, czy zdobywa pozytywną ocenę użytkowników, ich zaufanie i satysfakcję.

Jak poznać złą bazę danych

Często łatwiej powiedzieć, co zostało zrobione źle, niż wskazać, co zrobić, żeby było dobrze. Mało odkrywcze, ale prawdziwe jest stwierdzenie, że złe cechy bazy danych są przeciwieństwem tych dobrych. Niestety, łatwo zaprojektować marną bazę, w której wydajność i integralność danych nie będzie zadowalająca. Przestrzeganie poniższych wskazówek pomoże nam to zagrożenie oddalić od naszego projektu.
Oto cechy złej bazy danych:
* Tabele i kolumny mają niejasne nazwy.
* Ta sama informacja musi być wprowadzona do bazy wielokrotnie, a w przypadku modyfikacji, musi być zmieniana w kilku miejscach.
* Zapytania wyszukujące tę samą informację zwracają różny wynik.
* Działanie bazy danych jest powolne. Niepoprawnie zaprojektowana baza danych zwraca wyniki zapytań zbyt wolno, co znacznie utrudnia korzystanie z niej. W rozdziale 12. omówiłem zagadnienia wydajności baz danych.
* Trudno określić relacje pomiędzy danymi.
* W tabelach występują duplikujące się wiersze.

Projektowanie

Chcąc pokazać, jak odbywa się projektowanie bazy danych, prześledzimy krok po kroku projekt pewnej bazy danych, gromadzącej informacje o produkcji filmowej. Baza ta będzie używana we wszystkich przykładach w tej książce.
#46
Projektowanie możemy podzielić na kilka etapów.

1. Zbieranie danych. Musimy określić wymagania wobec tworzonej bazy i na tej podstawie odpowiedzieć na pytanie, jakie dane będziemy w naszej bazie danych przechowywać.
2. Sporządzenie listy danych. Po określeniu, jakie informacje będą gromadzone w bazie, należy określić typy danych oraz ich atrybuty.
3. Modelowanie danych na przykład metodą diagramów związków encji. Należy pogrupować dane w encje, określić atrybuty encji i związki encji.
4. Przeprowadzenie normalizacji.
5. Napisanie kodu SQL tworzącego bazę i stworzenie bazy w konkretnym systemie relacyjnych baz danych.
6. Zdefiniowanie użytkowników bazy danych i nadanie im określonych praw dostępu.
7. Wypełnienie bazy danych informacjami.

Tak wyglądają, ogólnie, najważniejsze etapy projektowania bazy danych. Nie jest to, oczywiście, jedyna prawidłowa metoda tworzenia bazy danych, a każda inna, która prowadzi do celu i daje pożądane rezultaty, jest równie dobra.

Zanim przystąpisz do projektu
======================
Rada
Przed przystąpieniem do pisania kodu i projektowania architektury bazy danych należy określić przeznaczenie gromadzonych w niej informacji. Większość baz danych jest projektowana w celu zastąpienia istniejących systemów przechowywania informacji lub jako składnica danych konkretnej aplikacji.
=======================

Bardzo ważne w pierwszej fazie projektu są rozmowy z przyszłymi użytkownikami systemu. Przed rozpoczęciem projektowania należy jasno określić, jakie dane powinny być przechowywane w bazie danych i w jaki sposób będą udostępniane. Najlepiej informacje te zdobyć właśnie od osób, które będą używały aplikacji i dokładnie wiedzą, jakie są ich wymagania i oczekiwania wobec projektowanej bazy.
Wróćmy do naszego przykładu bazy danych o filmach. Chcemy przechowywać informacje na temat filmów, które co roku wchodzą na ekrany. Informacji związanych z każdym filmem jest znacznie więcej, niż można by się spodziewać. To, które dane są istotne, a które możemy pominąć, zależy od wielu czynników.
W przypadku, gdy nowa baza danych zastępuje stary system, decydujące jest zapewnienie zgodności z poprzednim systemem. W przypadku nowego produktu bardzo istotne są wymagania projektanta aplikacji wykorzystującej bazę danych oraz przyszłych użytkowników.
#47
=======================
Rada
Nie mniej ważne od tego, co przechowujemy w bazie danych, jest to, co możemy z niej wydobyć przy pomocy zapytań i raportów. Użytkownicy właśnie pod tym kątem ocenią nasz produkt.
==========================

Baza danych, mająca być składnicą informacji o przemyśle filmowym, powinna gromadzić dane o studiach filmowych, filmach, obsadzie, ekipie filmowej, plenerach filmowych, efektach finansowych i jeszcze wielu innych, nie wymienionych tutaj szczegółach. Już na tym etapie projektu widać, jak ogromny zasięg tematyczny ma nasza baza danych. W przykładzie omawianym w tej książce zasięg tematyczny zostanie zawężony tylko do niektórych tematów.
Postanowiłem, że nasza baza będzie gromadzić dane dotyczące filmów, studiów filmowych, reżyserów, członków obsady i ich ról w filmach. Oczywiście, w przypadku rzeczywistej bazy danych takie decyzje powinny być podjęte w wyniku konsultacji z przyszłymi użytkownikami. Po podjęciu tych decyzji można przystąpić do określania, jakie konkretnie informacje będą potrzebne i jaka powinna być ich struktura.

Modelowanie danych

Po zapoznaniu się z wymaganiami aplikacji współpracującej z bazą danych, po wstępnym wyborze danych, które będą w niej gromadzone i określeniu oczekiwań na temat danych, które chcemy z bazy wybierać w postaci raportów, pora zastanowić się nad strukturą naszej bazy danych.
Rozpoczynamy od podziału logicznego, wybieramy obiekty wyższego rzędu i określamy ich atrybuty. Pierwszy krok polega na rozpoznaniu obiektów wyższego rzędu i budowaniu bazy danych wokół nich.

==================
Rada
Kilka terminów użytych w poprzedniej części książki miało znaczenie ściśle specjalistyczne, ale tutaj zostały wykorzystane w znaczeniu bardziej ogólnym. W kontekście tej dyskusji, obiekty są elementami na tyle ważnymi dla projektu bazy danych, by zostały wyróżnione jako samodzielne jednostki - encje. Właściwości to szczegółowe informacje opisujące poszczególne obiekty. Te ogólne pojęcia zostaną następnie przełożone na relacyjny model bazy danych.
===================

W większości wypadków dane, którymi dysponujemy wyodrębniają się w odrębne kategorie logiczne prawie bez wysiłku z naszej strony. Wróćmy do przykładowej bazy o filmach. Wydaje się oczywiste, że dla każdego filmu zostanie przeznaczony jeden rekord. Kilka właściwości każdego filmu prezentuje następująca lista:
* tytuł filmu,
* obsada,
* reżyser,
#48
* studio filmowe,
* data wejścia na ekrany,
* dochód TL filmu.

Z każdym filmem związanych może być oczywiście jeszcze więcej informacji, które zostały celowo pominięte w naszym przykładzie. Jak łatwo zauważyć, niektórym elementom naszej listy można przypisać dodatkowe atrybuty. Na przykład każdego członka obsady - aktora - charakteryzują następujące właściwości:

* imię,
* nazwisko,
* rola,
* wynagrodzenie,
* numer telefonu,
* kod pocztowy,
* numer ubezpieczenia,
* przynależność do związku zawodowego,
* adres zamieszkania.

Przedstawiono przykładowe atrybuty dla członka obsady filmu. Podobne cechy można przypisać reżyserowi. Listę potencjalnych atrybutów można również utworzyć dla każdego studia filmowego.
Jak widać, po podjęciu decyzji, jakie dane będą w systemie gromadzone, musimy sporządzić wiele list, opisujących obiekty z naszej bazy danych. Mając listę wszystkich encji i opisujących je atrybutów możemy rozpocząć modelowanie.

Zależności funkcyjne i klucze kandydujące

W momencie, gdy dane są podzielone na obiekty, kolej na określenie struktury bazy danych. Po wyodrębnieniu obiektów i określeniu ich atrybutów, pora na ustalenie tabel i ich kolumn oraz relacji między tabelami. Przy okazji spostrzegamy problem znalezienia unikalnego identyfikatora lub klucza głównego dla każdej z tabel.
Pojawia się wiele pytań, które decydują o poprawności naszego projektu. Jak ustalić, które kolumny mają tworzyć klucz główny tabeli i które atrybuty należą do których encji? Odpowiedzi na te pytania (ustalenie powiązań między danymi) to rozwiązanie problemu zależności funkcyjnych.
#49
Zależności funkcyjne

Przed podaniem definicji tego pojęcia posłużę się przykładem. Przykładowa tabela o nazwie rzeczy (ang. things) składa się z 3 kolumn: nazwa, cena, kolor. Każda rzecz w tabeli ma swoją cenę i występuje w kilku kolorach. W tabeli cena szklanej kulki niezależnie od koloru wynosi 99 groszy. To oznacza, że cena zależy funkcyjnie od nazwy, a nazwa od ceny. Cena każdej kulki jest taka sama, tak samo jak cena każdej szczoteczki do zębów czy świeczki.
Tabela 2.1 zawiera kilka wierszy z tabeli rzeczy.

Tabela 2.1. Kilka przykładowych wierszy z tabeli things

Nazwa Cena Kolor
Szklana kulka 0.99 Biały
Szklana kulka 0.99 Niebieski
Szklana kulka 0.99 Czerwony
Szczoteczka do zębów 2.99 Zielony
Szczoteczka do zębów 2.99 Biały
Łopata 10.99 Brązowy
Świeczka 1.99 Różowy
Świeczka 1.99 Niebieski

Definicja zależności funkcyjnej

Jedna grupa wartości zależy funkcyjnie od drugiej, 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.
Taki przypadek zachodzi dla wartości z tabeli 2.1. Jedna cena odpowiada jednej nazwie. Dla danych z tabeli 2.1 zachodzi również przypadek odwrotny, to znaczy jednej nazwie odpowiada jedna cena.
Zobaczmy, jak zmienią się zależności po dodaniu do tabeli następującego rekordu:

nazwa = widelec, kolor = srebrny, cena = 2,99.

======================
Rada
Najistotniejsza zasada dotycząca zależności funkcyjnych polega na tym, że jeśli jeden element zależy funkcyjnie od drugiego, to zależność ta musi być spełniona dla każdej możliwej wartości obiektu, a nie tylko aktualnie istniejącej. Potwierdza to przypadek opisany wcześniej, gdy do tabeli 2.1 dodałem rekord z pozycją widelec. Z faktu, że widelec i szczoteczka do zębów mają te same ceny wynika, że od tego momentu kolumna nazwa nie zależy funkcyjnie od kolumny cena.
====================
#50
W dalszym ciągu pozostanie prawdziwa zależność, że jedna cena odpowiada jednej nazwie, ale nie będzie prawdziwe stwierdzenie, że jednej cenie odpowiada jedna nazwa, ponieważ istnieją dwie różne rzeczy (szczoteczka do zębów, widelec) w tej samej cenie. Nie jest spełniona zależność, że nazwa zależy od ceny.
Używa się specjalnego zapisu do wskazania zależności funkcyjnej. Znak strzałki wskazuje, że jeden zespół wartości zależy funkcyjnie od drugiego. Na przykład aby zapisać, że cena zależy od nazwy używamy następującego zapisu:

{nazwa} -> {cena}

Prowadząc nasze rozważania dalej zauważmy, że kolor nie zależy funkcyjnie ani od nazwy, ani od ceny, nie zachodzi też zależność w drugą stronę. Jednak możemy utworzyć zależność funkcyjną dla grupy wartości, jak w poniższym zapisie:

{nazwa, kolor} -> {cena}

Tak jak cena zależy jedynie od nazwy, tak cena zależy również od kombinacji nazwa, kolor (dla każdej pary nazwa, kolor istnieje tylko jedna cena). Prawdziwe są również poniższe związki funkcyjne:

{nazwa, kolor} -> {nazwa}
{nazwa, kolor} -> {kolor}

Określane są one mianem zależności trywialnych, gdyż stanowią podzbiór wartości, od których zależą. Jednak co się stanie, gdy nagle będzie brakowało szklanych kulek w kolorze niebieskim i wzrośnie ich cena? Pokazuje to tabela 2.2.

Tabela 2.2. Nowa wersja tabeli things
Nazwa Cena Kolor
Szklana kulka 0.99 Biały
Szklana kulka 5.99 Niebieski
Szklana kulka 0.99 Czerwony
Szczoteczka do zębów 2.99 Zielony
Szczoteczka do zębów 2.99 Biały
Łopata 10.99 Brązowy
Świeczka 1.99 Różowy
Świeczka 1.99 Niebieski

Jak widać, niebieska szklana kulka kosztuje 5.99 zł, podczas gdy kulka w innym kolorze dalej kosztuje 0.99 zł. Nie jest już prawdziwe stwierdzenie, że cena zależy tylko od nazwy, prawdziwa natomiast pozostaje zależność:

{nazwa, kolor) -> {cena}

Podczas projektowania tabel, rozważając, które kolumny powinny pojawić się w tabeli warto zawsze pamiętać o występujących zależnościach funkcyjnych między danymi. Oczywiście, wymyślenie tego przy pierwszym projekcie sprawia znacznie więcej trudności niż później.
#51
Klucze kandydujące i klucze główne

Wiemy już, że klucz główny to grupa wartości (z jednej kolumny lub kilku) jednoznacznie identyfikująca dany rekord z tabeli. Klucz kandydujący spełnia te same warunki co klucz główny. W tabeli może istnieć wiele kluczy kandydujących, jednym z nich jest klucz główny. Pozostałe klucze kandydujące nazywamy zastępczymi.
Istnieją dwie reguły, które określają czy wybrana grupa wartości spełnia warunki klucza kandydującego:
1. Żadne dwa rekordy z tabeli nie mogą mieć tych samych kombinacji wartości w kolumnach wybranych na klucz kandydujący.
2. Z klucza kandydującego nie można wyodrębnić podgrupy kolumn również zapewniających unikatowość danego rekordu.
Aby wyjaśnić praktyczne zastosowanie przedstawionych reguł, wróćmy do tabeli 2.2. Można wyróżnić dwie grupy kolumn (nazwa, cena, kolor}, {nazwa, kolor}, które w sposób unikalny identyfikują wybrany rekord tabeli. Jednak tylko grupa {nazwa, kolor} spełnia założenia reguły 2, może więc być kluczem kandydującym. Z grupy {nazwa, cena, kolor} można wydzielić podgrupę (nazwa, kolor}, która nie spełnia założeń reguły 2.
Wracając do zależności funkcyjnych pozostaje stwierdzić, że kolumny tabeli, które nie należą do klucza kandydującego muszą być funkcyjnie od niego zależne. Jeśli ten związek nie jest spełniony, to klucz kandydujący jest określony błędnie.

Droga na skróty

Po przedstawieniu szczegółów dotyczących definiowania kluczy kandydujących i kluczy głównych, chciałbym pokazać pewną drogę na skróty. Metoda ta polega na zdefiniowaniu w tabeli nowej kolumny, która zawiera unikalną wartość dla każdego rekordu. Najprostszą metodą jest zdefiniowanie pola z numerem porządkowym, którego wartość zwiększa się o 1 dla każdego nowego rekordu. Wiele baz danych umożliwia zdefiniowanie pola typu licznik, które zostało wprowadzone właśnie w tym celu.
Oczywiście, są argumenty przeciwko używaniu tego typu kluczy. Mogą one powodować problemy z utrzymaniem integralności danych w tabeli. Każdy rekord jest jednoznacznie identyfikowany przez sztuczny klucz ID, jednak inne kolumny mogą zawierać identyczne wartości dla różnych rekordów, choć z punktu widzenia zachowania integralności danych nie powinny.
Jest również wiele argumentów za stosowaniem tego rozwiązania. Klucz w postaci kolumny ID ułatwia korzystanie z kluczy obcych, ułatwia tworzenie połączeń między tabelami. Nie należy jednak upatrywać w kluczu ID metody na rozwiązanie wszystkich problemów, pomijając analizę zależności funkcyjnych oraz kluczy kandydujących. Dopuszczalne jest użycie klucza ID, jednak zaleca się najpierw przejście przez wszystkie omówione dotąd etapy analizy danych.
#52
======================
Rada
Użycie auto-inkrementującego pola jako klucza głównego wpływa również pozytywnie na wydajność bazy danych, jeśli używamy go zamiast klucza złożonego. Inna zaleta polega na znacznym uproszczeniu operacji łączenia tabel, w języku SQL wystarcza jedna instrukcja porównania w klauzuli WHERE. Pole ID zamiast klucza złożonego ogranicza również wielkość tabeli przy określaniu relacji.
=======================

W praktyce często wykorzystuje się pola ID wszędzie tam, gdzie nie może być zdefiniowany jednokolumnowy klucz główny. Również w przykładach zawartych w niniejszej książce przyjąłem tę zasadę, szczególnie, że ułatwia to znacznie pracę z tabelami.

Modelowanie relacji

Po wyjaśnieniu podstawowych pojęć teoretycznych, możemy wrócić do analizy naszego przykładu. Obecnie problem sprowadza się do określenia, jakie dane ma zawierać każda tabela w naszej bazie danych. Zrozumienie zasad zależności funkcyjnych oraz problematyki kluczy kandydujących, wydaje się dla tego zagadnienia decydujące.
Modelowanie związków encji jest jedną z metod projektowania struktury bazy danych. Wykorzystujemy do tego diagramy ER, które w sposób graficzny przedstawiają zależności między obiektami bazy danych. Metoda ta składa się z trzech etapów:
* Wybieramy niepodzielne obiekty.
* Określamy właściwości obiektów.
* Rozpoznajemy relacje między obiektami.

Wybieranie obiektów

Obiektami bazy są tabele. Wróćmy do listy obiektów sporządzonej wcześniej:
* Filmy.
* Studia filmowe.
* Plenery.
* Aktorzy.
* Reżyserzy.

===================
Rada
Można rozróżnić obiekty słabe i mocne. Słabe to te, które istnieją tylko w obecności innych. W naszej bazie danych studia, Filmy i Ludzie to obiekty mocne. Obiektem słabym są Plenery, ponieważ ma on znaczenie jedynie w połączeniu z filmem, który był tam kręcony.
==================
#53
Zastanówmy się nad dwoma obiektami: aktorzy i reżyserzy. Po pierwsze, zarówno jedni jak i drudzy to ludzie, będziemy więc gromadzić podobne dane. Ponadto są przypadki, że aktor może być również reżyserem. Logiczne wydaje się połączenie tych tabel w jedną wspólną. W związku z tym nowa lista wygląda następująco:
* Filmy.
* Studia filmowe.
* Plenery.
* Ludzie.
Rysując diagram E-R obiekty pojawiają się wewnątrz prostokątów. Stałe obiekty reprezentowane są przez prostokąty w pojedynczej ramce, słabe otoczone są podwójną ramką.

Filmy
Ludzie Studia
Plenery
Rysunek 2.1. Diagram E-R pokazujący obiekty bazy danych

Rozpoznawanie własności

Po sporządzeniu listy obiektów nadszedł czas na określenie własności poszczególnych obiektów. Wcześniej w tym rozdziale sporządziliśmy już listę własności, związaną z wybranymi obiektami. Teraz dokonamy szczegółowej analizy.
Na początek zajmiemy się obiektem Filmy. Przynależne mu własności to: data wejścia na ekrany, budżet, przychody. Ponadto każdy film ma reżysera oraz studio filmowe. Inne ważne cechy to obsada czyli aktorzy i grane przez nich role oraz plenery, w których film jest kręcony.
Obsada i Plenery to własności, które ze względu na to, że nie są opisane jedną wartością, nie znajdują się bezpośrednio wewnątrz obiektu Filmy. Po przeprowadzonej analizie ustalamy, że z obiektem film związane są następujące własności:

Data wejścia na ekrany
Budżet
Widownia
Reżyser
Studio

W przypadku obiektu studio określenie własności jest znacznie prostsze:

Nazwa studia
Miasto studia
Stan
#54
Ludzie to nasz kolejny obiekt:

Nazwa
Adres
Telefon
Nr ubezpieczenia
Przynależność do związku zawodowego

Nazwa i adres to właściwie cechy złożone, które składają się z kilku pól. Dla nazwy jest to odpowiednio imię i nazwisko, dla adresu to pola kod pocztowy, miasto, ulica, stan. Plenery mają swoje własności jak film, który był tam kręcony, miasto, stan. W diagramie E-R własności znajdują się wewnątrz elips i są połączone z obiektami pojedynczą linią tak jak pokazano na rysunku 2.2.

[Filmy](odchodzi od bloku pięć kresek do:)
{studio}
{data}
{budżet}
{dochód}
{reżyser}

[Ludzie](odchodzi od bloku pięć kresek do:)
{nazwa}
{adres}
{telefon}
{numer ubezpieczenia}
{członek związku}

[Studio]
{nazwa}
{miasto}
{stan}

[Plenery]

Rysunek 2.2.Diagram E-R z uwzględnieniem własności obiektów

=========================
Rada
Do każdej z tabel reprezentującej odpowiedni obiekt zostanie dodana kolumna ID w celu uproszczenia operacji łączenia. Prawdziwe pozostają wcześniejsze wskazówki ostrzegające przed możliwością utraty integralności danych.
==========================

Rozpoznawanie relacji między obiektami

Wyróżniamy trzy typy relacji: jeden-do-jeden, jeden-do-wiele oraz wiele-do-wiele. Każda z nich zostanie omówiona oddzielnie.

Relacja jeden-do-jeden

Relacja ta zachodzi wówczas, gdy jeden element wykazuje zależność funkcyjną od drugiego. W przypadku wystąpienia tej relacji między danymi zwykle umieszcza się je w jednej tabeli.
#55
Na przykład teoretycznie można by stworzyć specjalną tabelę przechowującą daty wejścia na ekran dla poszczególnych filmów, ale nie ma żadnych przesłanek uzasadniających ten podział. Data wejścia na ekran jest bezpośrednio związana z konkretnym filmem i najprościej zawrzeć tę informację w tabeli filmy.

Relacja jeden-do-wiele

Relacja jeden-do-wiele zachodzi, gdy jeden element jest powiązany z kilkoma innymi.
Taka relacja zachodzi na przykład pomiędzy filmami i studiami filmowymi oraz pomiędzy filmami i reżyserami. Każde studio filmowe może wyprodukować wiele filmów, a każdy reżyser może reżyserować wiele filmów. W naszej bazie danych klucz główny z tabeli ludzie jest kluczem obcym w tabeli filmy (kolumna reżyser). Podobnie jest w przypadku tabeli studio - własność studio w obiekcie filmy jest tam też kluczem obcym.
Podobnie relacja jeden do wiele zachodzi pomiędzy obiektem filmy i plenery, film jest własnością obiektu plenery, ponieważ film może być kręcony w wielu plenerach - tutaj kluczem obcym jest własność film. Sposób zapisu zachodzących relacji wygląda następująco:

reżyser (z filmy do ludzie)
studio (z filmy do studio)
film ( z plenery do film)

Relacja wiele-do-wiele

Utworzenie relacji wiele-do-wiele jest bardziej skomplikowane niż relacja jeden-do-wiele, ponieważ nie wystarczy zdefiniowanie klucza obcego. Określenie tej relacji między obiektami wymaga utworzenia specjalnej tabeli łączącej.

==================
Rada
W przypadku, gdy nie jesteś pewny, że relacja między analizowanymi obiektami jest typu jeden-do-jeden lub jeden-do-wiele powinieneś założyć, że występuje relacja wiele-do-wiele.
Konsekwencje zbyt późnego odkrycia relacji wiele-do-wiele mogą być dla programisty bardzo trudne do naprawienia.
======================

Wróćmy do naszego przykładu. Podczas ostatniej analizy danych nie określiliśmy, jak będą gromadzone informacje na temat obsady filmu. Teraz, gdy znamy pojęcie relacji wiele-do-wiele, możemy się tym problemem zająć.
Każdy aktor może grać w wielu filmach, a w każdym filmie gra wielu aktorów. Przy założeniu, że każde pole tabeli przechowuje informację niepodzielną, nie istnieje metoda zapisu obsady filmu w jednym rekordzie. Analogicznie nie można zapisać w jednym rekordzie wszystkich filmów, w których grała jedna osoba. Trzeba stworzyć nowy obiekt obsada, który będzie zawierał aktorów ze wszystkich filmów. Relacja między obiektem filmy i obsada oraz ludzie i obsada jest typu jeden-do-wiele. W tabeli obsada pojawią się również nowe własności: nazwa roli oraz gaża aktora za wybraną rolę.
#56
Kluczem głównym jest tutaj kombinacja nazwy filmu oraz aktora grającego. Nie może być kluczem głównym nazwa roli, ponieważ jest wiele filmów, gdzie występuje rola policjanta lub świadka.
Nowy obiekt obsada dołączony jest do diagramu E-R, łączy się z jednej strony z obiektem ludzie, z drugiej strony z obiektem filmy.

Schemat relacji

Rysunek 2.3 zawiera diagram E-R z nowym obiektem obrazującym relację wiele-do-wiele i jest to ostateczna wersja modelu bazy danych.

[Filmy]
____{studio}
____{data}
____{budżet}
____{dochód}
____{reżyser}
==M==[[filmy-obsada]]==M==[Ludzie]__
--1--[[filmy-plenery]]==M==[plenery]
--1--[Studio]
__{nazwa}
__{miasto}
__{stan}
(od filmy do tego momentu odchodzą:___;===)
od[Ludzie]
__{nazwa}
__{adres}
__{telefon}
__{numer ubezpieczenia}
__{członek związku}

Rysunek 2.3. Diagram E-R ilustrujący relacje między obiektami

Relacje są na rysunku zobrazowane według następującego schematu:

Relację przedstawia romb łączący dwie tabele, wewnątrz którego znajduje się nazwa relacji. Relacja między obiektami filmy, a studia filmowe jest oznaczona jako film_studio. Relacja między obiektem słabym, a mocnym znajduje się w podwójnej ramce. Nad linią łączącą relację z obiektem pojawia się dodatkowy opis "1" lub "M", "1" pojawia się gdy opisywana relacja jest typu jeden-do-wiele, "M" w przypadku relacji wiele-do-wiele.
Na częściowe uczestnictwo w relacji wskazuje pojedyncza linia łącząca; pełne uczestnictwo wskazuje linia podwójna.
Po zakończeniu etapu modelowania danych diagramami E-R jesteśmy już o krok od powołania do życia tabel w naszej bazie danych. Ostatni krok to normalizacja, której celem jest zapewnienie integralności danych.
#57
Normalizacja

Normalizacja polega na wykryciu w obiektach bazy nadmiarowych danych. Jakkolwiek przeprowadzone modelowanie E-R powinno zapewnić poprawność danych, dla sprawdzenia struktury bazy danych wykonujemy normalizację.

=============
Rada
Pojęcie normalizacja właściwie odnosi się do podporządkowania danych pierwszej formie normalnej. Często nadużywa się tej nazwy w celu określenia operacji podporządkowania wyższym postaciom normalnym.
==============

Jest wiele postaci normalnych opisujących nasze dane. W tym rozdziale opiszemy pierwszą postać normalną (1PN), drugą postać normalną (2PN), trzecią postać normalną (3PN) oraz postać normalną Boyce'a-Codda (BCPN).
Każda wyższa postać normalna musi spełniać kryteria wszystkich postaci niższych. Na przykład baza danych spełniająca trzecią postać normalną definicji, spełnia również pierwszą i drugą postać normalną. Normalizacja to operacja, której podlegają tablice bazy danych, aby spełniać coraz wyższe formy normalne. Dla większości baz danych wystarcza spełnienie trzeciej postaci normalnej, by uniknąć problemów związanych z wstawianiem, modyfikowaniem i usuwaniem danych. Problemy, jakie mogą się pojawić nazywamy anomaliami. Każda z postaci normalnych eliminuje pewne anomalia. W trakcie normalizacji będziemy dokonywali dekompozycji bez straty informacji.

Dekompozycja bez straty danych

Dekompozycja polega na dzieleniu tabeli na kilka tabel w taki sposób, by żadne dane zgromadzone w tabeli źródłowej nie zostały stracone. Wszystkie zmiany w tabelach dokonane podczas dekompozycji są zmianami odwracalnymi.
Posłużymy się przykładem zastosowania dekompozycji bez straty danych w odniesieniu do tabeli studio filmowe. Tabela studio filmowe składa się z kolumny nazwa studia, miasto studia, stan. Kolumny miasto studia, stan, są funkcyjnie zależne od nazwy studia, które występuje w tej tabeli jako klucz główny. Rysunek 2.4 przedstawia 4 tabele.
W pierwszym przypadku tabela studio jest podzielona na dwie tabele, w których znajduje się klucz główny tabeli źródłowej.
W drugim przypadku w wyniku podziału powstała jedna tabela zawierająca klucz główny i druga bez klucza głównego tablicy źródłowej. Tylko w pierwszym przypadku mamy do czynienia z dekompozycją bez straty informacji.
Dla zrozumienia zasad dekompozycji, przypomnijmy sobie omówione wcześniej zależności funkcyjne. Stwierdziliśmy, że w tabeli powinna zachodzić zależność funkcyjna pomiędzy kluczem kandydującym, a pozostałymi kolumnami tabeli. W przypadku, gdy w wyniku podziału tabeli zależność ta zostaje stracona, podział ten nie spełnia warunków dekompozycji bez straty informacji.
#58
Metoda 1

{[Studia_ Miasta]
[Nazwa] [Miasto]
[Giant] [Los Angeles]
[MPM] [Burbank]}

{[Studia_Stany]
[Nazwa] [Stan]
[Giant] [CA]
[MPM] [CA]}

Metoda 2

{[Studia_Miasta]
[Nazwa] [Miasto]
[Giant] [MPM]
[LOS Angeles] [Burbank]}

{[Miasta_Stany]
[Nazwa] [Stan]
[Burbank] [CA]
[Los Angeles] [CA]}
{..}-blok większy w którym mniejsze [..]

Rysunek 2.4. Dwie metody podziału tabeli

Nowe tabele studia_Miasta oraz studia_stany mają klucz główny taki sam, jak w źródłowej tablicy studio. Dzięki temu utrzymana jest zależność funkcyjna nazwa->miasto oraz nazwa->stan i to zapewnia już, że dekompozycja została przeprowadzona bez straty danych. W przypadku drugiego podziału nie zachowano powiązania pomiędzy nazwą a stanem, bo druga tabela zawiera tylko kolumny miasto i stan. Utracona zależność nazwa->stan spowodowała utratę danych w procesie dekompozycji. Wiadomo, że może być kilka studiów filmowych w jednym mieście czy stanie, a miasta o tej samej nazwie mogą być również w innych stanach. W przypadku podziału drugiego nie zostały zapewnione jednoznaczne powiązania między danymi, określające jak połączyć, np. nazwę studia ze stanem.
Szybki test sprawdzający, czy dekompozycja została wykonana prawidłowo, polega na próbie utworzenia tabeli źródłowej z tabel powstałych w wyniku podziału. Jeśli taka operacja jest możliwa, to na pewno dekompozycję można wykonać bez straty informacji.

Pierwsza postać normalna 1PN

Tabela spełnia założenia pierwszej postaci normalnej, jeżeli posiada tylko pola zawierające wartości skalarne (niepodzielne).
Gdyby wszystkie tabele bazy danych spełniały pierwszą postać normalną, to relacje typu jeden-do-wiele byłyby określone zawsze pomiędzy kilkoma tabelami (nigdy w obrębie jednej tabeli), a relacja typu jeden-do-jeden mogłaby się zawierać w jednej tabeli.
Zwróćmy uwagę na następujący przykład. Niedawno analizowałem bazę danych składającą się między innymi z dwóch tabel: uczestnicy i wydziały. Każdy wydział może mieć więcej niż jednego uczestnika, a każdy uczestnik może należeć do kilku wydziałów. Zachodzi tu relacja wiele-do-wiele, więc połączenie między tabelami następuje poprzez tablicę łączącą.
Niestety, analizowana przeze mnie baza była zaprojektowana dość ułomnie. Wydziały oznaczono jednoliterowymi skrótami. Przynależność uczestnika do kilku wydziałów była oznaczana przez złożenie pierwszych liter wydziałów, a takie wyrażenie było przechowywane w odpowiednim polu tabeli. Na przykład członek wydziałów SPRZĘT, OPROGRAMOWANIE, INTERNET był identyfikowany przy pomocy łańcucha SOI. Metoda ta ma wiele wad, które poniżej wyliczam:
#59
Mimo stwierdzenia relacji wiele-do-wiele, taka struktura określa tylko powiązanie uczestnika z wieloma wydziałami, lecz nie na odwrót (wydział z wieloma uczestnikami).
Nie ma możliwości wyrażenia bezpośredniego związku uczestnika z konkretnym wydziałem. Wymaga to najpierw wyszukania odpowiedniej litery skrótu w łańcuchu, a następnie odwołania do rekordu tabeli wydziały.
W celu zmiany lub dopisania listy wydziałów dla wybranego uczestnika, musimy wykonać operacje związane z programowaniem wykraczającym poza proste instrukcje SQL.
Przedstawiony przykład nie spełnia pierwszej postaci normalnej. Wynika to stąd, że łańcuch SOI zawiera wskaźniki do aż trzech rekordów w innej tabeli. Dlatego możemy powiedzieć, że pole to przechowuje trzy wartości, co oczywiście stoi w sprzeczności z wymogiem przechowywania w polu wartości niepodzielnych.

Druga postać normalna 2PN

Tabela odpowiada drugiej postaci normalnej, jeżeli spełnia dwa założenia:
1. Tabela jest 1PN.
2. Kolumny nie wchodzące w skład klucza są zależne od całego klucza (nie od jego części).
Drugą postać normalną wyjaśnimy na kolejnym przykładzie marnej bazy danych. Załóżmy, że w wyniku modelowania danych doszliśmy do tabeli w postaci:
Kolumna śr_temp zawiera średnią roczną temperaturę dla miasta znajdującego się w kolumnie miasto_studio. Klucz główny naszej tabeli składa się z dwóch kolumn studio i film. Tabela 2.3. zawiera przykładowe dane.

Tabela 2.3. Przykładowa zawartość tabeli Filmy (ang. movies)

Studio Film Budżet Studio_miasto Śr_temp
Giant Gwiezdne wojny 180 Milpitas 73
Giant Odyseja kosmiczna 50 Milpitas 73
Wheatstone Robocop 12 Vancouver 60
MPM Rambo 32 Austin 82
MPM Rambo II 72 Austin 82

Rozpatrzmy zależności funkcyjne w tabeli. Kolumna budżet zależy od całego klucza głównego, kolumna studio_miasto zależy tylko od kolumny studio, śr_temp zależy z kolei tylko od kolumny studio_miasto. Już na pierwszy rzut oka widać, że prezentowana tabela nie spełnia 2PN.
#60
Podstawowy problem polega tutaj na tym, że brak normalizacji powoduje anomalia aktualizacji.
* Instrukcja INSERT wymaga dodatkowych danych. Do bazy nie możemy wprowadzić danych bez podania klucza głównego. Na przykład chcemy zapamiętać, że w Gliwicach w województwie śląskim średnia roczna temperatura wynosi 9 stopni. Aby dokonać takiego wpisu do naszej tabeli musimy najpierw założyć fikcyjne studio filmowe oraz produkowany przez nie film. Jak widać taka struktura powoduje konieczność wprowadzania wielu niepotrzebnych danych.
* Instrukcja DELETE powoduje usuwanie dodatkowych danych. Chcemy usunąć z bazy film "Sami swoi". Ta operacja usunie również informacje na temat lokalizacji studia filmowego i średniej temperatury w Warszawie.
* Instrukcja UPDATE posługuje się nadmiarowymi danymi. Średnia temperatura w Warszawie i Łodzi pojawia się w bazie kilka razy. W przypadku zmiany temperatury jesteśmy zmuszeni dokonać aktualizacji w kilku miejscach, aby zapewnić spójność gromadzonych danych.
Jak rozwiązać te problemy? Rozwiązanie polega na podzieleniu tabeli na kilka tabel zgodnie z zasadami dekompozycji bez straty informacji.
Po pierwsze utworzymy tabelę miasta zawierającą dwie kolumny miasto oraz śr_temp. Następnie rozwiążemy problem związany z kolumną studio_miasto. W naszej tabeli źródłowej klucz główny składa się z dwóch kolumn studio i film podczas, gdy kolumna studio_miasto zależy tylko od kolumny studio. Z tego względu kolumna studio_miasto musi zostać usunięta z tabeli filmy. Zostaje przeniesiona do nowej tabeli składającej się z dwóch kolumn studio i miasto.
Tak powstały trzy tabele:

Miasta (miasto, śr_temp)
Studia filmowe (studio, miasto)
Filmy (film, studio, budżet)

Każda z tych tabel spełnia założenia drugiej postaci normalnej. Nowy projekt pokazuje rysunek 2.5.
Procedura została przeprowadzona metodą dekompozycji bez straty informacji, żadne zależności między danymi nie zostały utracone, a anomalia aktualizacji wyeliminowane.

Trzecia postać normalna 3PN

Trzecia postać normalna wymaga, aby wszystkie kolumny nie należące do klucza, zależały funkcyjnie od klucza kandydującego. Nie są wymagane zależności pomiędzy kolumnami poza kluczem. Rozważmy tabelę filmy o następujących kolumnach: studio, studio_miasto, śr_temp.

Studia filmowe (studio, studio_miasto, śr_temp)
#61
{[Filmy]
[Film] [Studio] [Budżet]
[Powrót Jedi] [Delighted Artist] [5.0]
[Dzień niepodległości] [Giant] [15.0]
[Brudny Harry] [FKG] [77.0]}

{[Studia
[Studio] [Miasto
[Delighted Artisl] [Austin]
[Giant] [Los Angeles]
[FKG] [Apex]}

{[Miasta]
[Miasto [Śr_temp]
[Austin] [76]
[Los Angeles] [72]
[Apex] [60]}

Rysunek 2.5 Tabela Filmy zredukowana do 2PN

Tabela spełnia 2PN. Kolumna śr_temp jest funkcyjnie zależna od kolumny studio - zachodzi tu relacja przechodniości, to znaczy, że jeżeli kolumna studio_miasto zależy od kolumny studio, a śr_temp zależy od studio_miasto, to z tego wynika zależność śr_temp od studio.
Zależność przechodnia powoduje wystąpienie anomalii aktualizacji. Zwróćmy uwagę, co się stanie w przypadku próby wprowadzenia zmian do tabeli studia filmow:
* Nie można wprowadzić nowego miasta ani temperatury bez wpisania nowego studia.
* Poprawianie danych wymaga pracy z danymi nadmiarowymi. Na przykład: jeżeli w jakimś mieście jest kilka studiów filmowych, to chcąc zmienić temperaturę dla tego miasta musimy aktualizować dane w kilku rekordach.
* Z powodu, że więcej informacji jest ze sobą związana bezpośrednimi zależnościami, usuwanie rekordu z bazy danych powoduje usunięcie również informacji, której normalnie nie chcielibyśmy się pozbyć. Na przykład: usunięcie rekordu ze studiem filmowym w Warszawie powoduje utratę informacji o średniej temperaturze w tym mieście.
Rozwiązanie tego problemu polega znowu na podziale tabeli na mniejsze części według następującego schematu:

Studia filmowe (studio, miasto_studio)
Miasta (miasto, śr_temp)

Rozdzielenie danych pomiędzy dwie tabele eliminuje problemy przynależne danym zgromadzonym w jednej tabeli. W tym rozwiązaniu nie ma potrzeby dopisywania
#62
nowego studia, żeby dopisać nowe miasto, również nie zaginie informacja o temperaturze, kiedy usuniemy rekord dotyczący wybranego studia filmowego, a zgromadzone dane nie są nadmiarowe.
Można by uważać, że w obiekcie plenery zachodzi przypadek przechodniości i w związku z tym, obiekt powinien zostać podzielony, aby spełnić 3PN. Występują trzy własności obiektu plenery: film, miasto, stan. Może się wydawać, że własność stan zależy od własności miasto. Naprawdę stan, tak jak miasto, zależy od własności film. Rozważmy przypadek, w którym jeden film jest kręcony w Spring-filed i Illinois, a drugi w Springfield i Missouri. Wartość w kolumnie stan nie zależy od miasta, ale filmu, z którym jest związana. Te trzy kolumny stanowią klucz kandydujący i jednocześnie klucz główny. Kolumna film nie może być samodzielnie kluczem głównym, ponieważ jeden film może być kręcony w kilku plenerach. Schemat tabeli wygląda następująco: plenery (film, miasto, stan).

Boyce-Codd'a postać normalna BCPN

Postać normalna Boyce-Codd'a jest udoskonaleniem 3PN. Dla większości tabel 3PN oraz BCPN jest taka sama. Jednak 3PN nie rozwiązuje pewnych specyficznych przypadków, gdzie jest wymagana dalsza normalizacja. Można powiedzieć, że BCPN jest bardziej restrykcyjna niż 3PN.

Na czym więc polegają różnice pomiędzy 3PN, BCPN? W 3PN kolumny nie należące do klucza muszą być funkcyjnie związane z kluczem kandydującym. W przypadku BCPN, gdy występuje kilka kluczy kandydujących zależność ta dotyczy także kolumn, które należą do innego klucza kandydującego. 3PN i BCPN różni się dla tabeli, w której spełnione są następujące warunki:
* Tabela posiada kilka kluczy kandydujących.
* Przynajmniej dwa klucze kandydujące składają się z więcej niż jednej kolumny.
* Złożone klucze kandydujące mają wspólną kolumnę.
Prześledźmy na przykładzie różnice między 3PN, a BCPN. Posłużmy się przykładową tabelą zgodnie z podaną definicją:

obsada (film_tytuł, film_ID, nazwa_aktora, rola, gaża)

W tabeli występują dwa klucze kandydujące {film_ID, nazwa_aktora} oraz (film_ tytuł, nazwa_aktora}. Zgodnie z wymaganiami 3PN oraz BCPN kolumny rola, gaża wykazują zależność funkcyjną od dwóch kluczy kandydujących. Problem tutaj polega na wzajemnej zależności pomiędzy kolumnami film_tytuł, a film_ID. Takiej zależności nie dopuszcza BCPN. Tabela 2.4 zawiera kilka przykładowych rekordów:
#63

Film_tytuł Film_id Nazwa_aktora Rola Gaża
Hard Code 9 Paul Monk Złota rączka 10000
Hard Code 9 Brian Smith Świadek 500
Bili Durham 4 Carol Delano Pielęgniarka 7500

Tabela 2.4. Przykładowe rekordy

Jak widać, w tabeli zachodzi problem związany z anomalią aktualizacji oraz pojawia się nadmiarowość związana z kolumnami film_tytuł, film_ID. Rozwiązanie polega na zastąpieniu jednej tabeli dwiema zgodnie z definicją:

filmy (film_id, film_tytuł)
obsada (film_id, nazwa_aktora, rola, gaża)

Wyższe postacie normalne

Istnieją oczywiście definicje wyższych postaci normalnych. Na przykład czwarta postać normalna (4PN) eliminuje relacje typu jeden-do-wiele zachodzące między niezależnymi kolumnami.
Posłużmy się przykładem:

plenery (film, miasto, stan)

W tej tabeli dane nie są niezależne, bo miasto i stan jest zależne od filmu i zachodzi też zależność stanu od miasta.
Jeśli jednak zmienimy w tabeli ostatnią kolumnę na gatunek filmu, a tabelę nazwiemy Film_info to możemy poddać ją normalizacji do 4PN.

Film_info (film, miasto, gatunek)

Tabela ta jest zgodna z BCPN. Jednak występuje nadmiarowość danych.

Film Miasto Gatunek
Hard Code Los Angeles Komedia
Hard Code Nowy Jork Komedia
Bili Durham Santa Cruz Dramat
Bili Durham Durham Dramat
The Code Warrior Nowy Jork Horror

Tabela 2.5. Tabela film_info

Zgodnie z 4PN kolumny miasto oraz gatunek są niezależne od siebie, więc ta tabela może być zastąpiona dwoma według schematu:

Film_miasto_info (film, miasto)
Film_gatunek_info (film, gatunek)
#64
Nadmiarowość danych została tym sposobem usunięta.

Piąta postać normalna polega na podziale tabeli źródłowej na maksymalną ilość tabel w celu uniknięcia nadmiarowości danych.
===============
Rada
Problem z postaciami normalnymi, np. 5PN - polega na tym, że stosunkowo proste zapytania wymagają użycia wielu połączeń. Z punktu widzenia wydajności lepiej pozostawić niższą postać normalną, która również chroni dane przed anomalią aktualizacji.
=============

Denormalizacja

Bardzo często, bazy danych są normalizowane do postaci 3PN lub BCPN. Dalsza normalizacja zazwyczaj nie jest potrzebna. Z drugiej strony, rozmiar normalizacji często wynika z dbałości o wydajność systemu. Na przykład: normalizacja do postaci 5PN, powoduje, że nawet przy prostych zapytaniach ilość złączeń tabel jest duża, co znacznie wpływa na obniżenie prędkości.
Jak widać, na tym etapie projektu dochodzimy do ustalenia równowagi między wydajnością bazy danych, a jej integralnością. Normalizacja zapewnia integralność danych oraz łatwość aktualizacji, wprowadzania i usuwania danych. Optymalizacja danych w tym kierunku stoi w sprzeczności z wydajnością. Denormalizacja z kolei zapewnia wyższą wydajność za cenę zagrożenia integralności danych oraz możliwych anomalii.

W praktyce

Kiedy odwiedzasz stronę amazon.com, aby zamówić książkę, twoje zamówienie jest obsługiwane przez system OLTP (Online Transaction Processing). Systemy OLTP są projektowane po to, aby wiele aplikacji mogło jednocześnie wprowadzać i wyszukiwać informacje z bazy danych bez z zachowaniem spójności danych. System zamówień, system ich realizacji, system magazynowy i jeszcze kilka innych wykorzystują zasoby bazy danych pod różnym kątem.
Kluczem do sukcesu w tworzeniu takich systemów jest umożliwienie wszystkim aplikacjom tworzenia zapytań, aktualizacji tabel bez zawieszania systemu, strat informacji lub spójności danych.
Systemy te wymagają użycia wysokich postaci normalnych w projekcie bazy danych, aby uniknąć nadmiarowości i związanych z nimi anomalii. Są projektowane z myślą o tym, że rekordy będą równocześnie wprowadzane, usuwane i poprawiane, zaś większość transakcji będzie krótka. Tak zaprojektowane systemy są stworzone na miarę konkretnego zadania. Niestety, w bazie danych wykonujemy również inne zadania, które są całkowicie niedopasowane do modelu OLTP.
#65
Posiadając system, który obsługuje tysiące (miliony) transakcji każdego dnia, można go również wykorzystać do prognozowania. Jeff Bazos, szef amazon.com prawdopodobnie chce wiedzieć, czy klienci, którzy kupują książki, również kupują filmy oraz czy, zachodzi związek pomiędzy kupowanymi książkami i filmami. Na przykład, jeśli wielu klientów kupuje powieści Stephena Kinga i adaptacje filmowe jego powieści to warto promować filmy bezpośrednio na stronach internetowych książek i na odwrót.
Jeśli klienci pochodzący z Los Angeles kupują zwykle książki z serii Zrób to Sam to amazon.com powinien próbować ustalić pochodzenie klienta, podsuwać mu interesujące pozycje. Amazon.com wprowadził wszystkie z zaleceń, które teoretycznie pomagają klientowi znalezienie poszukiwanej pozycji.
Niestety te funkcje są po prostu niezgodne z modelem OLTP. Jak już wiesz z tego rozdziału zachowanie zgodności bazy danych z wysoką postacią normalną wiąże się podziałem dużych tabel na mniejsze i łączeniem ich przez relacje klucza obcego. Na przykład wprowadzenie zamówienia na stronie amazon.com powoduje odwołanie do wielu następujących tabel:
* Klient. Każdy klient, który kiedykolwiek kupował w amazon.com rejestrując się podaje swoje dane.
* Zamówienie. Decyzja o zakupie to z kolei wprowadzenie odpowiedniej informacji do tej tabeli. Szczegóły zamówienia przechowywane są w tabelach powiązanych relacjami poprzez klucze obce (sposób zapłaty, sposób dostawy, adres dostawy).
* Adres. W tabeli przechowywany jest adres dotyczący miejsca dostawy.
* Płatność. Tabela określa sposób zapłaty za każde zamówienie.
* Towary_zamówione. Tabela zawiera przyjęte zamówienia, w każdym rekordzie zapisany jest jeden towar.
* stany magazynowe. Tabela zawiera stany magazynowe wszystkich oferowanych towarów.

W celu prognozowania pewnych trendów i wspomagania decyzji wymagane są ogromne zapytania złączające. Amazon.com wprowadza codziennie tysiące rekordów do każdej z tych tabel, a niektóre z zapytań mogą wymagać połączenia niemal wszystkich sześciu tabel. Wykonanie tak złożonego zapytania przez system OLTP może zablokować wszystkie aplikacje wykorzystujące bazę danych, uniemożliwiając wykonywanie podstawowego zadania systemu.
W tym celu powstała alternatywna teoria projektowania hurtowni danych. Hurtownie danych są tworzone w celu umożliwienia analizy ogromnych ilości danych i wyłapywania na ich podstawie trendów. Funkcjonuje anegdota, która opisuje pewną sieć sprzedaży detalicznej (jej nazwa jest zmieniana w zależności od tego, kto opowiada anegdotę), jak to na podstawie analiz hurtowni danych wymyślono system, który gwarantuje, że człowiek, który przyszedł do sklepu wieczorem po zakup pieluszek, kupił jednocześnie piwo dzięki umieszczeniu tych dwóch towarów obok siebie.
#66
Hurtownie danych są projektowane dla pracowników szczebla kierowniczego do wyciągania zbiorczych informacji, gdzie szczegóły nie są tak ważne, jak wydajność. Z tego więc względu, zamiast normalizacji tabel zapewniających wysoką integralność danych, hurtownie danych denormalizują je po to, aby mogły być wykonane odpowiednie zapytania z dobrą wydajnością.
W hurtowni danych amazon.com nie jest istotne rozdzielenie tabel zamówienia i towary_zamówione. Zamiast łączyć te dwie tabele, system gromadzi dane z tych tabel w jednej dużej. Prawdopodobnie również tabela stany_magazynowe może być dołączona do tej tabeli. Trudno tutaj podawać ogólne założenia, ponieważ denormalizacja tabel zależy od informacji, jakie chcemy uzyskać z hurtowni danych.
Coraz powszechniejsze zakupy przez Internet opierają się również na systemach relacyjnych baz danych. OLTP - Online Transaction Processing to system zaprojektowany do obsługi dużej ilości danych napływających z różnych aplikacji. Stawia mu się wymagania dotyczące zachowania integralności danych, stabilności, łatwego modyfikowania, dodawania i usuwania danych.
Takie wymagania, zgodnie z teorią relacyjnych baz danych, spełnione są przez obiekty poddane normalizacji. Niestety, z normalizacją związane jest obniżenie wydajności przetwarzania danych, co powoduje, że niektóre operacje w takiej bazie nie mogą być wykonywane.
Duża baza danych, w której przeprowadzane są tysiące lub miliony transakcji dziennie dostarcza również bardzo ciekawych informacji statystycznych.
Niestety baza typu OLTP nie jest przystosowana do obróbki statystycznej i jakościowej. Jak wiadomo z tego rozdziału, dążenie projektantów do zachowania zgodności tabel z jak najwyższą postacią normalną, powoduje dzielenie ich na wiele mniejszych części powiązanych odpowiednimi kluczami.
Chcąc wykorzystać dane zgromadzone w systemie wspomagającym decyzje (DSS Decision Support System) musiałaby być wykonana olbrzymia ilość połączeń między tabelami. Stworzenie odpowiednich zapytań często wymagałoby połączeń między wszystkimi tabelami. Wykonanie tak złożonego zapytania stanowi zagrożenie dla stabilności pracy systemu.
W celu rozwiązania zasygnalizowanego problemu, powstała odrębna teoria projektowania baz danych dla hurtowni danych. Hurtownie te tworzone są jako narzędzia do analizy ogromnych ilości danych w celu prognozowania.
Hurtownie danych projektowane są z myślą o przetwarzaniu ogromnych ilości informacji i tworzeniu na tej podstawie prognoz i wyłapywaniu trendów. W takim systemie najważniejsza jest wydajność przetwarzania, możliwość uzyskania szybko odpowiedzi na wprowadzone zapytanie. W związku z tym, odwrotnie jak przy systemie zamówień, w celu podniesienia wydajności baza danych podlega operacji denormalizacji.