Tematem często pomijanym podczas prowadzenia ćwiczeń z baz danych jest projektowanie. Studenci cenią sobie wiedzę praktyczną i starają się zapoznać z aplikacjami służącymi do wykonywania baz danych lub całych systemów informatycznych, ale nie są zainteresowani teorią, której znajomość jest niezbędna do wykonania nowej bazy danych (stąd później liczne próby przechowania danych niemal tak jak w Excelu). Żeby uzupełnić nieco tę lukę, przedstawię tu w dużym uproszczeniu jeden ze sposobów wykonania nowej bazy danych: diagram związków encji (z ang. ERD - entity relationship diagram).

      Diagram związków encji jest jednym ze sposobów przedstawienia modelu logicznego bazy danych. Jest to rysunek encji i związków pomiędzy nimi, który ułatwia analizę wybranego obszaru świata rzeczywistego modelowanego najczęściej (można modelować także inne światy np.: wirtualne o ile przyszły użytkownik bazy zapłaci za pracę rzeczywistymi pieniędzmi) w bazie danych. Wykonywanie diagramów związków encji umożliwiają aplikacje będące narzędziami CASE, do których zaliczyć można m.in. ER/1 firmy Embarcadero Technologies (wersji trial ER/1 rozpowszechniana jest na płycie CD załączonej do książki "SQL. Księga eksperta"). Oprócz ER/1 istnieją również inne aplikacje umożliwiające wykonywanie diagramów związków encji (np.: System Engineer firmy Learmonth & Burchett Management Systems Inc. lub Power Designer firmy PowerSoft) i wiele innych. W zależności od użytej aplikacji diagram związków encji może być inaczej przedstawiony i w odmienny sposób konstruowany. Tu postaram się przybliżyć tylko ogólną ideę wykonania takiego diagramu.

      Jak już wiadomo (z poprzedniego semestru) baza danych jest najczęściej modelem pewnego fragmentu otaczającego nas świata rzeczywistego. Ów fragment nazywany jest obszarem analizy i podczas tych zajęć interesuje nas uproszczony model składania zamówień przy sprzedaży bombonierek. Można ów model przekształcić na diagram związków encji i na jego podstawie przeanalizować, czy baza danych będzie poprawnie odzwierciedlać zdarzenia zachodzące w świecie rzeczywistym (i to zanim baza zostanie wykonana, co zmniejsza koszty produkcji oprogramowania).

	Prowadzący zajęcia zdają sobie sprawę, że ten model jest najczęściej omawiany w książkach o bazach danych, ale też najczęściej modelujemy działalność firmy, która prowadzi sprzedaż towarów lub usług. Z tego więc powodu omówimy sobie podczas zajęć model przyjmowania w firmie zamówień na towar (lub usługę).

      Zanim powstanie diagram, należy poznać obszar analizy. W tym celu przeprowadza się wywiady z odbiorcami bazy danych, z ekspertami, analizuje się przepisy prawne, przegląda zasoby Sieci (np.: Firma Cukiernicza "Solidarność"), analizuje dokumenty firmy itp. Zainteresowanych tą tematyką odsyłam do książki "Tworzenie hurtowni danych". Mając na uwadze ograniczenia czasowe w prowadzeniu zajęć opierać się będziemy tylko na krótkim opisie:

"Klient może kupić (lub zamówić do późniejszego odbioru) wiele, różnych bombonierek. Klient może dostać fakturę VAT jako dokument płatniczy do zamówienia lub transakcja kupna-sprzedaży może zostać zafiskalizowana (wtedy, gdy klient przy zakupie płaci gotówką). Klientem może być firma (podmiot gospodarczy) lub osoba fizyczna. Jeżeli zamówienie zostanie źle przyjęte, a wystawiono już do niego fakturę VAT, to wystawiamy do zamówienia kolejną fakturę: fakturę korygującą, a następnie przyjmujemy ponownie zamówienie. Przyjmujemy, że zamówienie dotyczy tylko jednego klienta (w rzeczywistości może to być: osoba zamawiająca, odbiorca i płatnik) i jest ono obsługiwane przez jednego pracownika. Niekiedy musimy informować klientów o zawartości bombonierek (o czekoladkach), jeżeli wymagają oni tych informacji."

      Spróbujmy wyodrębnić z tego krótkiego opisu istotne obiekty funkcjonujące w świecie rzeczywistym. W tym celu osoby początkujące mogą posłużyć się diagramem Venna (zaawansowani potrafią z tego opisu stworzyć diagram związków encji). Diagram Venna to rysunek obiektów świata rzeczywistego (nie musi to byś wierny rysunek - wystarczą jakieś symbole).
Zauważmy, że w podanym opisie pojawiają się (tu sztucznie wyróżnione) następujące obiekty: klienci, pracownicy, zamówienia, bombonierki, czekoladki, faktury. Można wyróżnić w tym modelu więcej obiektów, ale utrudniłoby to jego zarówno przedstawienie jak i zrozumienie (np.: nasza firma może być kilkoma podmiotami gospodarczymi). Zaobserwowane obiekty odrysowujemy na diagramie Venna i obiekty tej samej klasy grupujemy w zbiory (tu 6 zbiorów: klientów, pracowników, zamówień, bombonierek, czekoladek i faktur). Pomiędzy poszczególnymi obiektami rysujemy linie, które symbolizują zależności pomiędzy tymi obiektami zachodzące w świecie rzeczywistym.

Rys. 1. Diagram Venna

      Przyjrzyjmy się dokładnie powyższemu rysunkowi i przeanalizujmy związki, które zachodzę pomiędzy poszczególnymi obiektami.

•     Jak widać na przykładzie klienta "Robert Miakowski": jeden klient może złożyć wiele zamówień (oczywiście może też złożyć jedno zamówienie). Czy poprawną sytuacją w świecie rzeczywistym jest znajomość klienta, który nie składał żadnego zamówienia? Z pozoru wydaje się, że to błędna sytuacja: kto podawałby swoje dane osobowe, a nic nie zamawiał (na diagramie taką osobą jest "Mariusz Białek")? Jednak okazuje się, że taka sytuacja może mieć miejsce, gdy firma zakupi dane osobowe potencjalnych klientów (np.: osób mieszkających w okolicy).
  Analizujmy dalej związki między klientami, a zamówieniami, ale tym razem od strony zamówień. Okazuje się, że dane zamówienie jest składane przez najwyżej jednego klienta (to efekt wcześniejszego założenia) i że mogą być zamówienia takie, że klient jest nieznany, a sytuacja taka ma miejsce wtedy, gdy anonimowa osoba kupuje towar i płaci gotówką, a fakt ten jest rejestrowany tylko w urządzeniu fiskalnym - drukarce lub kasie (więcej o tych urządzeniach tutaj i tutaj).

•     Podobne zależności występują pomiędzy pracownikami, a zamówieniami: dany pracownik może obsługiwać wiele zamówień (gdy jest on kasjerem lub sprzedawcą np. "Stanisław Kowalski", ale nie musi - gdy jest on załóżmy dyrektorem np. "Jan Nowak") oraz dane zamówienie MUSI być obsłużone przez jednego pracownika (też efekt założenia, że pracownik jest jeden, jednak musi ktoś w imieniu firmy zamówienie przyjąć).

•     Ciekawe zależności występują pomiędzy bombonierkami, a zamówieniami. Okazuje się, że KAŻDE zamówienie dotyczy przynajmniej jednego towaru, ale może też dotyczyć wielu towarów (np.: "zamówienie 301"), a z kolei towar może być zamawiany wielokrotnie w różnych zamówieniach (np.: bombonierka "Czekoladowe party"), ale też może istnieć towar, który nie był nigdy zamawiany (np.: dopiero się pojawił w sprzedaży lub jest zbyt drogi, aby był kupiony - tu: bombonierka "Czekoladowy słoń").

•     Podobne do powyższych zależności występują pomiędzy bombonierkami, a czekoladkami - KAŻDA bombonierka zawiera w sobie przynajmniej jeden typ czekoladki, a każda z czekoladek danego typu może być zapakowana do różnych typów bombonierek. Sytuacja dotyczaca czekoladki "Czekoladka mleczna" może sygnalizować stan polegający na tym, że znamy już co będzie składnikiem nowego typu bombonierki, który jest dopiero w produkcji.

•     Ostatnimi interesującymi zależnościami są te zachodzące pomiędzy zamówieniem, a fakturą. Jak widać z diagramu Venna do zamówienia możemy wystawić maksymalnie dwie faktury, ale każda faktura musi dotyczyć jednego zamówienia (nie wykonujemy faktur zbiorczych - uproszczenie projektu). Faktury VAT i korygujące mogą mieć wspólną numerację (to zastosujemy), ale mogą być oddzielnie numerowane.

      W praktyce diagram Venna nie jest wykonywany, gdyż byłby niecztelny (zbyt wiele zależności pomiędzy obiektami utrudnia odczytanie diagramu) dla większej ilości obiektów przy złożonych modelach wykonywanych w zastosowaniach profesjonalnych. Ta technika została przedstawiona dla ułatwienia zrozumienia bardzo prostego projektu bazy danych.

      Jeżeli dobrze zastanowiliśmy się nad zależnościami w diagramie Venna, to możemy przekształcić go na diagram związków encji. Podczas tej konwersji zbiory obiektów przekształcamy na encje, a zależności pomiędzy obiektami na związki encji:

Rys. 2. Diagram związków encji (przed usunięciem związków wiele do wielu)

      Przyjmuje się, że nazwa encji jest rzeczownikiem w licznie pojedynczej (nie jest to jednak obowiązkowe), a tabeli w liczbie mnogiej. Z tego powodu na powyższym diagramie w prostokątach symbolizujących encje pojawiły się nazwy takie jak: "klient", "pracownik", "zamówienie" i inne. Zauważmy też, że pomiędzy encjami pojawiły się specjalnie oznakowane kreski, które symbolizują związki encji. Jak już wspominałem, sposobów na ich oznakowanie jest kilka. Tu użyte jest oznakowanie wg notacji Martin-McClure'a, czyli: "okrąg" oznacza słowo "może", "kreska" - musi, a "wronie pazury" na zakończeniu symbolu - "do wielu". Odczyt powyższego diagramu jest prostszy niż diagramu Venna. Czytając związki encji (czytamy w obie strony związku) widzimy, że:

• "Jeden klient może złożyć wiele zamówień"
• "Zamówienie może być złożone przez maksymalnie jednego klienta"
  
  
• "Pracownik może przyjąć wiele zamówień"
• "Zamówienie musi być przyjęte przez jednego pracownika"
  
  
• "Każde z zamówień dotyczy przynajmniej jednego typu bombonierki, ale może wielu"
• "Każdy typ bombonierki może być składnikiem wielu zamówień".
  
  
• "Każdy typ bombonierki zawiera przynajmniej jednen typ czekoladki"
• "Każdy typ czekoladki może być składnikiem wielu typów bombonierek".
  
  
• "Do każdego zamówienia możemy wystawić maksymalnie dwie faktury"
• "Każda faktura musi być wystawiona tylko do jednego zamówienia".

      Tu jeszcze raz zaznaczę, że analizowany podczas zajęć projekt nie nadaje się do zastosowań profesjonalnych, a dzieje się tak m.in. dlatego, że nie uwzględniamy w nim konkretnych dostaw towaru. Przyjmujemy, że mamy zawsze pełne magazyny niepsujących się bombonierek i z tego powodu mówiąc bombonierka mamy na myśli raczej typ bombonierki, a nie konktetną bombonierkę z konktetnej dostawy. Z tego powodu przeczytałem związki encji mówiąc o typach bombonierek, czy typach czekoladek. W przyjętym modelu bombonierka ma zawsze taką samą cenę (pamiętaną dla bombonierki), a tymczasem powinien być do niej przypisany cennik, który obowiązuje w danym okresie czasu. Pomijamy ten problem w celu uproszczenia projektu.

      Nie istnieje taki RDBMS, który obsługuje związki wiele do wielu, a takie właśnie związki zachodzą pomiędzy bombonierką i zamówieniem oraz czekoladką i bombonierką. Związki te muszą zostać usunięte z diagramu w wyniku zastąpienia ich dwoma związkami jeden do wielu i encją przejściową (tu warto zaznaczyć, że istnieją wprawdzie odpowiednie narzędzia do projektowania, które zrobią to automatycznie, ale nadają one niezrozumiałe nazwy tworzonym encjom przejściowym i potrafią też robić błędy - z tego powodu warto wszystkie modyfikacje wykonać samodzielnie).

Rys. 3. Diagram związków encji

      W tym wypadku zostanią dodane encje przejściowe "szczegół zamówienia" (właściwie powinna się ona nazywać "pozycja zamówienia" mówimy przecież "pozycja pierwsza", a nie "szczegół pierwszy") oraz "zawartość bombonierki", a także odpowiednie związki jeden do wielu (wiele po stronie encji przejściowej!), które należy czytać:

• "Każde zamówienie musi posiadać przynajmniej jeden szczegół zamówienia"
• "Każdy szczegół zamówienia musi być fragmentem jednego zamówienia"
  
  
• "Każdy szczegół zamówienia musi dotyczyć jednego typu bomonierki"
• "Każdy typ bombonierki może być elementem wielu szczegółów zamówienia"
  
  
• "Każda zawartość bombonierki musi opisywać część jednego typu bomonierki"
• "Każdy typ bombonierki musi składać się z przynajmniej jednej zawartości bombonierki"
  
  
• "Każda zawartość bombonierki musi dotyczyć jednego typu czekoladki"
• "Typ czekoladki może być składnikiem wielu zawartości bombonierek".

      Po wykonaniu diagramu (już bez związków wiele do wielu) określamy jeszcze jakie atrybuty powinna posiadać każda z encji (czyli określamy cechy obiektów ze świata rzeczywistego). Dodatkowo określmy, czy podanie wartości do danego atrybutu jest wymagane (NOT NULL), czy nie (NULL).
      Przy projektowaniu ważne jest opisanie znaczenia zarówno encji jak i ich atrybutów. Te opisy to fragment tzw. metadanych (dane o danych), a bez nich wybieranie informacji z bazy jest mocno utrudnione (w wypadku dużych, złożonych systemów - niemożliwe, ponieważ nie wiemy jakie informacje wybieramy). Nie każdy SZRBD posiada możliwość zapamiętania dodatkowych opisów tabel i kolumn (np.: poleceniem COMMENT ON w przypadku Oracle lub opis pola, czy właściwości tabeli w MS Access) - z tego powodu opisy te są ważne.
      Zarówno przy wykonywaniu diagramu związków encji jak i przy określaniu atrybutów encji przydatna jest znajomość dokumentów, które używane są w firmie. Z tych dokumentów możemy się dowiedzieć jakie właściwości obiektów musimy znać, aby można było wykonywać takie dokumenty używając danych z bazy.

      Wiekszość atrybutów encji Klient możemy odczytać wprost z faktury VAT (na niebiesko zaznaczono wartości, które należy na fakturze uzupełnić - uwaga! przyjęto wiele uproszczeń).

W poniższych encjach zaznaczyłem klucze, które warto wykonać podczas konstrukcji tabel (podstawowe, obce, alternatywne). Nie są to elementy obowiązkowe przy projektowaniu encji. Projektując encję powinniśmy raczej skupić się na ustaleniu identyfikatora instancji encji, jednak ten termin na tyle jest mało popularny, że zdecydowałem się opisać klucze. Więcej o kluczach można dowiedzieć się ze składni instrukcji CREATE TABLE i używanych tam ograniczeń.

Opis również nie jest elementem obowiązkowym. Warto jednak przestudiować dla języków t-SQL, czy PL/SQL takie polecenia jak add_extendedproperty, czy COMMENT, gdyż dodatkowy opis zapisany przy strukturze tabeli jest niestety bardzo często jedyną dokumentacją.

Ważną cechą każdego z atrybutów będzie jego dziedzina (z ang. domain - niekiedy tłumaczona jako domena, ale to może być mylone instukcją CREATE DOMAIN, więc będę używać terminu: dziedzina), czyli zbiór dozwolonych wartości, jakie dany artybut może przyjąć.

      Tak określone encje i ich atrybuty to odpowiedniki tabel i ich kolumn. Związki encji, to odpowiedniki kluczy obcych. Przed przekształceniem diagramu związków encji w układ tabel wybieramy SZBD, w którym będzie wykonana nowa baza danych i określamy typ danych dla każdej kolumny (domenę atrybutu encji).

Rys. 5. Okno relacji z MS Access

      Zdaję sobie sprawę, że wykonując ten model przyjęto wiele istotnych uproszczeń względem świata rzeczywistego. Z tego powodu prezentowany model nie nadaje się do profesjonalnych zastosowań. Tu proszę o wyrozumiałość, ale pominięcie uproszczeń rozbuduje ten model do ogromnych rozmiarów, co uniemożliwi przedstawienie go podczas zajęć.

      Opracowując model oparto się na pomyśle z książki "Microsoft Access 7 dla Windows 95 - krok po kroku".