Systemy baz danych

l

Krzysztof Myszkorowski

Systemy baz danych

Wydział Fizyki Technicznej Informatyki i Matematyki Stosowanej Politechniki Łódzkiej Łódź 2002

Systemy baz danych

Krzysztof Myszkorowski

Treści

JKIEM ZARZĄDZANIA BAZĄ DANYCH ZWOŻENIA SYSTEMÓW BAZ DANYCH

7

\BSTRAKCJADANYCH

8

:• NIEZALEŻNOŚĆ DANYCH

10

- JĘZYK DEFINIOWANIA DANYCH

10

: JĘZYK MANIPULOWANIA DANYMI

10

- ADMINISTRATOR BAZY DANYCH - UŻYTKOWNICY BAZ DANYCH

11 11

! ARCHITEKTURA SYSTEMU ZARZĄDZANIA BAZĄ DANYCH

12

l

. MODELE DANYCH 1.

7

14

MODEL HIERARCHICZNY

15

li MODEL SIECIOWY

18

13. MODEL RELACYJNY

21

%*ODEL ZWIĄZKÓW ENCJI..........:........^..;.!.....

22

3L1.ENCJE I ZBIORY ENCJI

22

12. ZWIĄZKI MIĘDZY ENCJAMI

22

13. SILNE i SŁABE ENCJE

24

3.4. DIAGRAMY ZWIĄZKÓW ENCJI

...24

3 J. SPECJALIZACJA i GENERALIZACJA

26

3.5.1. Specjalizacja 3.5.2. Generalizacja 3.5.3. Więzy integralności 3.5.4. Dziedziczenie

26 27 27 28

3.6. AGREGACJA 4 RELACYJNY MODEL DANYCH

31 .......................i..............„...:...^......™..

33

4.1. DEFINICJE WSTĘPNE

33

42. ALGEBRA RELACJI

36

4.2.1. Operacje podstawowe 4.2.2. Dodatkowe operatory algebraiczne 4.2.3. Przykłady zastosowań

36 39 43

4.3. JĘZYK MANIPULOWANIA DANYMI SQL

45

4.4. PERSPEKTYWY

54

4.5. OSADZONY SQL

55

4.6. PROJEKTÓW ANIE MODELU RELACYJNEGO NA PODSTAWIE MODELU ZWIĄZKÓW ENCJI

56

;.-&'

-l

5. NORMALIZACJA RELACJI

60

5.1. ZALEŻNOŚCI FUNKCYJNE

61

5.2. TRZECIA POSTAĆ NORMALNA

62

5.3. ROZKŁADY RELACJI 5.4. POSTAĆ NORMALNA BOYCE'A-CODDA

66 68

5.5. CZWARTA POSTAĆ NORMALNA 5.6. PIĄTA POSTAĆ NORMALNA

69 72

6. MODEL OBIEKTOWO ZORIENTOWANEJ BAZY DANYCH

74

6.1. KRÓTKI PRZEGLĄD MODELU COCOON

74

6.2. UOGÓLNIONE OPERACJE AKTUALIZUJĄCE

77

6.3. WARUNKI INTEGRALNOŚCI

78

6.4. ODWZOROWANIE MODELU COCOON NA MODEL RELACYJNY

78

6. 5. TWORZENIE REGUŁ i PROCEDUR

80

LITERATURA

83

l

O

Wstęp

Zagadnienia związane z wykorzystaniem maszyn cyfrowych do przechowywania informacji stanowią jeden z najważniejszych nurtów współczesnej informatyki. Już w momencie udostępnienia komputerów szerokiej grupie użytkowników stało się jasne, że mogą być one znakomitym narzędziem do gromadzenia dużej ilości danych. Szczególne znaczenie miała tu możliwość zapisu informacji w pojemnej pamięci zewnętrznej i wykorzystanie szybkości pracy maszyny do przetwarzania danych, Powstało nowe pole zastosowań komputerów. W latach 1962 1963 sformułowano po raz pierwszy pojecie bazy danych w sposób następujący [4]: Bazą danych nazywamy zbiór danych o określonej strukturze, zapisany na zewnętrznym nośniku maszynowym komputera, mogący zaspokoić potrzeby wielu użytkowników korzystających z niego w sposób selektywny w dogodnym dla siebie czasie. a -R -^g&zr, «b/r Bazy danych umożliwiają wielokrotne odtwarzanie i analizę informacji o pewnych faktach i zdarzeniach mających miejsce w jakimś wycinku rzeczywistości. Jak można się domyśleć, pomiędzy użytkownikiem komputera, a zbiorem danych znajdującym się w pamięci masowej musi istnieć element pośredni. Taki pomost stanowi System Zarządzania Bazą Danych (SZBD). Jest to specjalistyczne oprogramowanie, które umożliwia użytkownikowi wprowadzanie, wyszukiwanie oraz modyfikację danych. System taki realizuje podstawowe zadania związane z zapisem, odczytem i aktualizacją informacji w pamięci masowej. Przez lata w teorii baz danych następowała swoista ewolucja modelu danych w kierunku coraz bardziej naturalnego i uniwersalnego sposobu kodowania informacji. Problematyka obejmująca zapis informacji w pamięci masowej, definicje modelu danych, czy metody komunikacji z użytkownikiem jest niezwykle szeroka. Dlatego też teoria baz danych i przetwarzania informacji w ciągu ostatnich trzydziestu lat stała się jedną z podstawowych dziedzin informatyki i jest szeroko badana przez wielu naukowców na całym świecie. W procesie projektowania można wyróżnić trzy etapy [9]: , , 1. 2. 3.

Specyfikacja wymagań użytkownika; Zapisanie danych w formie akceptowanej przez komputer; Wprowadzanie danych.

. •'

•

•

•

-

"

- -

Pierwszy etap nazywa się konceptualizacją świata rzeczywistego. Etap drugi polega na utworzeniu schematu danych, to znaczy zapisaniu ich w odpowiednim języku. Schemat danych odpowiada określonemu modelowi danych. Etap trzeci oznacza założenie bazy danych. Właściwy kontakt z użytkownikami jest bardzo ważną umiejętnością informatyka. Budowanie systemu zaraz po wysłuchaniu zamówienia klienta wiąże się z ryzykiem. Konieczne jest ustalenie szczegółów. Rozmowy z użytkownikami mogą być trudne, zwłaszcza gdy nie mają oni orientacji w dziedzinie informatyki oraz gdy nie umieją określić precyzyjnie własnych potrzeb. Zaimplementowany system może nie spełniać oczekiwań. Wykonanie projektu daje możliwość pokonania luki w komunikacji z użytkownikiem. Proces projektowania, choć czasochłonny, zapewnia znaczne korzyści. Projektant może przedstawić użytkownikowi swoje zrozumienie tematu i swoje wizje dotyczące przyszłego systemu. Modyfikowanie projektu systemu, a także opracowanie jego kolejnej wersji, celem spełnienia wymagań użytkownika, jest dużo tańsze i mniej kłopotliwe niż poprawianie gotowych i wdrożonych już aplikacji. Czas, który projektant zainwestował w stworzenie właściwej struktury systemu nie jest stracony. Przeciwnie, osiągnięcie celów wyznaczonych przez poprawne metodologie projektowania oznacza oszczędność czasu, ponieważ nie występuje stała potrzeba poprawy źle zaprojektowanej struktury. Praca przy komputerze powinna być dobrze przygotowana. Powinna być finałem wcześniejszych wysiłków i wynikać z dokładnej analizy postawionego zadania i dobrze opracowanego projektu. Szczególne umiejętności są potrzebne, gdy przyszły użytkownik obawia się nowej technologii i jest przeciwny jej wprowadzeniu. Bardzo ważny jest zatem aspekt psychologiczny. Niewłaściwe podejście może całkowicie przekreślić możliwość wdrożenia systemu informatycznego. Potrzebna jest zatem delikatna sztuka dyplomacji oraz prowadzenia negocjacji. Jasna komunikacja z użytkownikami oraz wyjaśnienie ich oczekiwań prowadzi do sukcesu systemu, sprzyja wzrostowi jego wydajności dzięki eliminacji falstartów i nieporozumień. O sukcesie decydują cechy osobowościowe informatyka, a właściwie wszystko to, co rozumie się pod pojęciem kreatywności lub „otwartego umysłu". Właśnie takich ludzi oczekują od szkół wyższych pracodawcy. Znajomość poszczególnych narzędzi informatycznych jest dla nich mniej istotna. Opanowanie nowego narzędzia jest znacznie mniej czasochłonne niż ukształtowanie w sobie cech osobowościowych, które decydują o przydatności dla przedsiębiorstwa. Jest to szczególnie ważne w przypadku informatyków, którzy oprócz znajomości technologii informatycznej muszą dysponować wiedzą z innych dziedzin lub być w stanie i chcieć ją opanować. „Informatyk powinien mieć racjonalny, logiczny umysł, potrafić spojrzeć na system z różnych stron, umieć podzielić go na poziomy podsystemów oraz być w stanie myśleć o systemie zarówno w terminach abstrakcyjnych, jak i fizycznych" [17]. Niniejsze opracowanie dotyczy zagadnień związanych z procesem projektowania baz danych. Nie jest natomiast omawiana fizyczna organizacja baz danych, jak również operacje współbieżne. Rozdział pierwszy zawiera podstawową

terminologię używaną w systemach zarządzania bazą danych. Rozdział drugi opisuje klasyczne modele danych: hierarchiczny, sieciowy oraz relacyjny. Dwa pierwsze są rzadko stosowane. Stanowiły jednak podstawę pierwszych komercyjnie dostępnych systemów. Mają dzisiaj znaczenie historyczne [15]. Omawiany w rozdziale trzecim model związków encjijesi często używany w procesie projektowania. Może stanowić wygodne narzędzie komunikacji między projektantem i użytkownikiem. Daje możliwość abstrakcyjnego, wysokopoziomowego spojrzenia na dane i na problemy, które napotyka informatyk podczas projektowania. Szczegóły transformacji modelu związków encji na model relacyjny zostały przedstawione w rozdziale czwartym. Poza tym rozdział czwarty zawiera omówienie podstawowej terminologii relacyjnego modelu danych, operacji algebry relacji oraz języka SQL (ang. Structured Query Language), który stał się powszechnie przyjętym interfejsem użytkownika. Ze względu na swoją prostotę i przejrzysty sposób reprezentowania informacji model relacyjny zyskał szeroką aprobatę. Relacyjne bazy danych stały się standardem w systemach komercyjnych w latach osiemdziesiątych, wypierając całkowicie systemy sieciowe i hierarchiczne. Był to pierwszy model danych zdefiniowany za pomocą pojęć matematycznych. Skonstruowanie prawidłowej logicznej struktury danych wymaga uwzględnienia zależności między danymi. Teoria projektowania baz danych, zwana również normalizacją, została omówiona w rozdziale piątym. Wreszcie ostatni rozdział opisuje przykład zastosowania do modelowania baz danych podejścia obiektowego.

1. System zarządzania bazą danych

System zarządzania bazą danych (SZBD, ang. database management system - DBMS) składa się ze zbioru powiązanych ze sobą danych oraz zbioru programów zapewniających do nich dostęp. Zbiór tych danych jest zwykle nazywany bazą danych. Głównym celem systemu zarządzania bazą danych jest zapewnienie wygodnego i wydajnego środowiska przy otrzymywaniu informacji z bazy danych oraz przy ich magazynowaniu. Systemy baz danych są budowane celem zarządzania wielkimi zbiorami informacji. Zarządzanie danymi obejmuje zarówno definicję struktur pamiętanych informacji, jak i mechanizmy manipulowania danymi. Ponadto system zarządzania bazą danych musi zapewnić bezpieczeństwo danych przy awariach lub próbach niedozwolonego dostępu. Jeżeli dane mają być używane jednocześnie przez wielu użytkowników (współdzielenie danych), system musi uwzględniać niebezpieczeństwo pojawienia się różnorodnych anomalii. Ze względu na znaczenie informacji dla przedsiębiorstw bazy danych należą do bardzo cennych zasobów. Wynika stąd rozwój dużej ilości koncepcji oraz technik wydajnego zarządzania danymi. W tym rozdziale podany zostanie krótki opis zasad działania systemów baz danych. », - ••**'< -

1.1. Założenia systemów baz danych Rozważmy informacje przechowywane przez bank o swoich klientach i ich oszczędnościach. Rekordy kont oszczędnościowych oraz klientów są zawarte w odpowiednich plikach. Poza tymi plikami system posiada programy umożliwiające manipulację danymi, na przykład zmianę wartości konta, wprowadzanie nowego konta, generowanie miesięcznych raportów. Wymagania stawiane systemowi stale się zmieniają. W miarę upływu czasu, wraz z pojawianiem się nowych potrzeb do systemu będą wprowadzane nowe programy i nowe pliki. Zmiany te zachodzą w długim okresie czasu i są z reguły dokonywane przez różnych programistów. Pliki mogą mieć różne formaty, a programy mogą być napisane w różnych językach. Opisane środowisko jest typowym systemem przetwarzania plików posiadającym następujące wady: Redundancja danych i brak spójności. Przykładowo adres klienta może wystąpić zarówno w pliku zawierającym rekordy kont oszczędnościowych, jak i w pliku zawierającym rekordy kont kredytowych. Mówimy wtedy o redundancji (ang. redundancy) lub nadmiarowości. Redundancja zwiększa koszty przechowywania oraz dostępu. Może także powodować niespójność danych, to znaczy występowanie sprzecznych informacji. Brak zgodności danych powoduje utratę zaufania do systemu. Jeżeli adres klienta się zmieni, to zmiana ta powinna zostać uwzględniona w obu plikach. Koszty redundancji zwiększają się wraz ze wzrostem liczby operacji aktualizujących. Redundancja może być wprowadzona celowo dla zmniejszenia czasu dostępu, umożliwienia zastosowania prostszych metod adresowania lub odtworzenia w przypadku awarii. Wymagany jest zatem kompromis. Często używany jest termin redundancja kontrolowana lub minimalna. Przyjmuje się, że w dobrze zaprojektowanej bazie danych wyeliminowano redundancję szkodliwą. Trudności w dostępie do danych. Przypuśćmy, że urzędnik banku chce znaleźć wszystkich klientów mieszkających w rejonie miasta o kodzie pocztowym 91-123. Telefonuje do działu informatyki i prosi o sporządzenie odpowiedniej listy. Żądanie jest dość niezwykłe. Nie zostało przewidziane wcześniej i nie istnieje odpowiedni program. Jest jednak program generujący listę wszystkich klientów. Urzędnik ma dwie możliwości. Może poprosić swoją sekretarkę, by na podstawie otrzymanej listy podała potrzebne nazwiska. Może także poprosić informatyków o napisanie odpowiedniej aplikacji. Nie są to satysfakcjonujące rozwiązania. Przypuśćmy bowiem, że odpowiedni program został napisany i że kilka dni później ten sam urzędnik potrzebuje, by w tej samej liście wyróżnić klientów, których stan konta przekracza 10 000 PLN. Urzędnik znowu ma dwie możliwości, z których żadna nie jest satysfakcjonująca. System nie pozwala bowiem na wyszukiwanie potrzebnych danych w wygodny i wydajny sposób. Nie nadąża za zmianami zachodzącymi w jego otoczeniu, co jest jego istotną wadą. Dostęp współbieżny. Wzrost wydajności systemu wymaga umożliwienia jednoczesnej pracy wielu użytkownikom. Jeżeli dwóch użytkowników usiłuje w tym samym czasie uzyskać dostęp do tych samych danych, powstaje niebezpieczeństwo niezgodności danych. Przypuśćmy, że dwóch użytkowników usiłuje w tym samym czasie zmniejszyć stan konta o odpowiednio 100 i 200 PLN. Załóżmy, że stan początkowy konta wynosi 1000 PLN. W zależności od momentu uzyskania dostępu przez obu użytkowników stan końcowy konta może wynosić 700, 800 lub 900 PLN. Tylko pierwsza z tych wartości jest prawidłowa. Opisaną sytuację nazywamy konfliktową. System powinien posiadać mechanizmy umożliwiające wykrywanie sytuacji konfliktowych i odpowiednio na nie reagować, by nie dopuścić do niezgodności danych. Porządek wykonania opisanych operacji powinien być równoważny porządkowi sekwencyjnemu. Stan końcowy konta wyniesie wtedy 700 PLN.

Separacja danych. Ze względu na rozproszenie danych w różnych plikach, które mogą występować w różnych formatach, tworzenie nowych aplikacji wyszukujących odpowiednie dane jest pracochłonne. Problemy bezpieczeństwa. Nie każdy użytkownik powinien mieć dostęp do systemu. Na przykład osoba przygotowująca listę płac w banku może tylko widzieć tę część bazy danych, która dotyczy pracowników. Nie może natomiast mieć dostępu do kont klientów. W omawianym systemie nowe aplikacje dodawane są ad hoc i wymuszenie odpowiednich więzów bezpieczeństwa jest trudne. Problemy integralności Dane przechowywane w systemie muszą spełniać określone warunki zwane warunkami integralności (ang. integrity constraints). Na przykład stan określonego typu konta może wynosić poniżej 100 PLN co najwyżej przez okres jednego miesiąca. Taki warunek musi być wbudowany do systemu. Wprowadzanie nowych warunków wiąże się z koniecznością kosztownych zmian odpowiednich programów. Trudności te stały się przyczyną powstania systemów zarządzania bazą danych. Elementami, które umożliwiają realizację stawianych im zadań są [9]: Trwałość danych (ang. data persistency). Oznacza to, że dane są stale dostępne (nie znikają po zakończeniu pracy z komputerem); 2. Efektywny dostęp do dużej liczby danych; 3. Możliwość opisu wycinka rzeczywistości w postaci schematu danych; 4. Języki wyższego rzędu umożliwiające opis schematu danych oraz manipulację danymi (wyszukiwanie, aktualizacja, wprowadzanie, usuwanie); 5. Zarządzanie transakcjami (ang. transaction management). Oznacza to zapewnienie dostępu do danych wielu użytkownikom pracującym w tym samym czasie; . , 6. Syntaktyczna kontrola danych; -*.„.,«», ^-«r.» ^ 7. Semantyczna kontrola danych; 8. Niezależność danych (ang. data independence). Oznacza to, że zmiany definicji danych nie powinny prowadzić do zmian w eksploatacji bazy danych; 9. Ochrona danych; 10. Skuteczne odtwarzanie danych po awariach; 1 1 . Obserwacja, monitorowanie i ocena procesów zachodzących w bazie. • *" • - " ' **>., l.

1.2. Abstrakcja danych Osiągnięcie wysokiej wydajności prowadzi do projektowania złożonych struktur modelujących dane w bazie. Złożoność ta powinna być ukryta przed użytkownikami. System zarządzania bazą danych dostarcza użytkownikom abstrakcyjny obraz danych, ukrywając szczegóły ich przechowywania. Zostało to osiągnięte poprzez zdefiniowanie trzech poziomów abstrakcji: Poziom wewnętrzny. Jest to najniższy poziom abstrakcji określający sposób pamiętania danych. Na poziomie tym opisuje się model wewnętrzny bazy danych, który składa się z wielu wystąpień różnych typów rekordu wewnętrznego. Model wewnętrzny jest zdefiniowany za pomocą schematu wewnętrznego. Poziom ten jest także w literaturze nazywany/eyczwy/H. „,;, ,, ______ Poziom pojęciowy. Jest to wyższy poziom abstrakcji definiujący dane oraz związki między nimi. Opisany na tym poziomie model pojęciowy (zwany również modelem konceptualnym) przedstawia logiczną strukturę bazy danych. Model pojęciowy jest zdefiniowany za pomocą schematu pojęciowego (konceptualnego), który zawiera definicje różnych typów rekordu pojęciowego. Poziom ten opisuje całą bazę danych za pomocą niewielkiej ilości stosunkowo prostych struktur. Implementacja tych struktur może być związana ze złożonymi strukturami poziomu wewnętrznego. Użytkownik poziomu pojęciowego nie potrzebuje ich znać. Model pojęciowy jest używany przez administratora bazy danych, który decyduje o jej zawartości informacyjnej. >? ; J o_» , ; Poziom zewnętrzny. Jest to najwyższy poziom abstrakcji danych. Jest to poziom najbliższy użytkownikowi. Opisuje model zewnętrzny, to znaczy sposób w jaki dane są widziane przez użytkownika. Pomimo użycia prostszych struktur, na poziomie pojęciowym istnieje dość znaczny stopień złożoności wynikający z dużych rozmiarów bazy danych. Wielu użytkowników nie potrzebuje wszystkich zgromadzonych informacji. Odpowiadający potrzebom danego użytkownika obraz danych definiuje się za pomocą schematu zewnętrznego zwanego również podschematem [8]. Dla tej samej bazy danych istnieje jeden model pojęciowy oraz wiele podschematów. Dla zilustrowania różnicy między poziomami abstrakcji danych zastosujemy analogię do koncepcji typów danych w językach programowania. W Pascalu można zadeklarować następujący rekord:

typ klient = record nazwisko: string; ulica: string; miasto: string; end;

**>;r.^s

£

W przypadku banku można zdefiniować kilka typów rekordów opisujących miedzy innymi konta (z polami numer i saldo) oraz pracowników (z polami nazwisko i zarobki). Rekordy na poszczególnych poziomach nie muszą być identyczne. Na poziomie fizycznym rekordy mogą być opisane jako bloki kolejnych komórek pamięci (bajtów). Na poziomie pojęciowym każdy rekord jest opisany przez swoją definicję w postaci podanej wyżej i jednocześnie zdefiniowane są związki między typami rekordów. W końcu, na poziomie zewnętrznym opisane są obrazy danych. Przykładowo osoby przygotowujące listę płac mogą widzieć jedynie tę część bazy danych, która opisuje pracowników banku. Nie mogą oni mieć dostępu do kont klientów. Zupełnie innych danych potrzebują kasjerzy. Mają oni dostęp do kont klientów. Nie mogą mieć natomiast dostępu do informacji dotyczącej zarobków innych pracowników. Między poszczególnymi poziomami muszą istnieć odwzorowania. Odwzorowanie między poziomem pojęciowym i wewnętrznym opisuje sposób w jaki rekordy pojęciowe są odwzorowywane na rekordy fizyczne. Odpowiedniość między modelem zewnętrznym i pojęciowym jest również zdefiniowana za pomocą odpowiedniego odwzorowania. Rysunek l. l przedstawia trzy poziomy abstrakcji danych.

Dostęp do danych zapewnia użytkownikowi język programowania, w którym jest zawarty subjęzyk danych służący do wyszukiwania i pamiętania informacji w bazie danych. Podczas wyszukiwania danych wykonywane są następujące czynności [3]: 1.

Użytkownik zgłasza żądanie dostępu do rekordu zewnętrznego.

2.

System zarządzania bazą danych określa na podstawie schematu zewnętrznego, odwzorowania zewnętrzny/pojęciowy oraz schematu konceptualnego, które rekordy pojęciowe zawierają poła żądanego rekordu zewnętrznego.

3.

System zarządzania bazą danych określa na podstawie schematu konceptualnego, odwzorowania pojęciowy/wewnętrzny oraz schematu wewnętrznego, które rekordy wewnętrzne zawierają pola potrzebnych rekordów pojęciowych.

4.

Po przeczytaniu rekordów wewnętrznych system zarządzania bazą danych buduje wystąpienia rekordów pojęciowych.

5.

Na podstawie rekordów pojęciowych system zarządzania bazą danych buduje wystąpienie rekordu zewnętrznego żądanego przez użytkownika.

6.

System zarządzania bazą danych przesyła odpowiedź do obszaru roboczego użytkownika.

1.3. Niezależność danych

c^ o «

'~

Możliwość modyfikacji definicji schematu na danym poziomie abstrakcji bez konieczności zmian definicji schematu na poziomie wyższym nazywamy niezależnością danych. Wyróżniamy dwa poziomy niezależności danych [8]: Fizyczna niezależność danych. Oznacza możliwość modyfikacji schematu fizycznego bez naruszania schematu pojęciowego i bez konieczności dokonywania zmian w programach użytkowych. Modyfikacje na poziomie fizycznym są przeprowadzane celem zwiększenia wydajności systemu. Logiczna niezależność danych. Oznacza możliwość modyfikacji schematu konceptualnego bez konieczności dokonywania zmian w programach użytkowych. Modyfikacje na poziomie konceptualnym są spowodowane koniecznością zmiany logicznej struktury danych. Logiczna niezależność danych jest trudniejsza do osiągnięcia, ponieważ programy użytkowe silnie zależą od logicznej struktury danych, na której operują. Pojęcie niezależności danych jest podobne do pojęcia abstrakcyjnych typów danych w nowoczesnych językach programowania. W obu przypadkach szczegóły implementacyjne nie są widziane przez użytkowników. Pozwala to koncentrować wysiłek na ogólnej strukturze danych, a nie na szczegółach implementacyjnych.

1.4. Jeżyk definiowania danych

» * , . „ - ,

Schemat bazy danych jest określony za pomocą zbioru definicji wyrażonych w specjalnym języku zwanym językiem definiowania danych (ang. DDL — Data Definition Language). Nie ma on charakteru proceduralnego. Wynikiem kompilacji zdań tego języka jest zestaw tabel przechowywanych w specjalnym pliku zwanym słownikiem danych, który zawiera metadane, czyli dane o danych. Plik ten jest sprawdzany przed odczytem lub modyfikacją danych. Struktura pamięci oraz metody dostępu używane w systemie zarządzania bazą danych są zdefiniowane w specjalnym typie języka definicji danych (ang. data storage and de/lnition language). Wynikiem kompilacji jest zbiór instrukcji określających szczegóły implementacji schematów baz danych, które zwykle są ukryte przed użytkownikiem.

1.5. Jeżyk manipulowania danymi Omówione w punkcie l .2 poziomy abstrakcji odnoszą się nie tylko do definicji danych, ale także do manipulowania nimi, które obejmuje następujące operacje: • • • •

Wyszukiwanie informacji; Wprowadzanie nowych informacji; Modyfikacja istniejących informacji; Usuwanie istniejących informacji.

»

; :

•

Na poziomie fizycznym trzeba zdefiniować algorytmy, które umożliwiają wydajny dostęp do danych. Na wyższych poziomach abstrakcji ważniejsza jest wygoda w użyciu. Język manipulowania danymi (ang. DMŁ — Data Manipulation Language) umożliwia użytkownikom dostęp i manipulowanie danymi zorganizowanymi w odpowiednim modelu. Poza wymienionymi operacjami język manipulowania danymi umożliwia przetwarzanie danych (wykonywanie na nich operacji arytmetycznych i logicznych) oraz tworzenie nowych komponentów bazy danych na podstawie znalezionych informacji. Pytanie o dane jest także nazywane kwerendą (ang. ąuery). Ta część DML, która dotyczy wyszukiwania informacji, nazywa się językiem kwerend (ang. ąuery language). Chociaż technicznie nie jest to poprawne, w praktyce przyjęto te dwa terminy jako synonimy. Do najważniejszych cech języka zapytań należą: • • •

Siła ekspresji. Określa zakres tego, co można w nim wyrazić. Znacznym zwiększeniem siły ekspresji języka jest „osadzenie" go w jakimś języku programowania (np. C, Pascal). Pełność. Oznacza, że można zawsze zapisać kwerendę, która pozwoli odszukać dowolną informację. Łatwość rozszerzeń: (1) Możliwość wykonywania działań arytmetycznych; (2) Możliwość wykonywania operacji agregujących, np. obliczanie średniej arytmetycznej. Osiąga się to przez dołączenie odpowiednich funkcji bibliotecznych.

10

• •

Prostota

• • - * . « . > -4.

Efektywność. (Krótki czas oczekiwania na odpowiedź i minimalny obszar pamięci roboczej). Można wyróżnić dwa główne typy języków manipulowania danymi:

•

Proceduralne (ang. procedural languages), które wymagają od użytkownika określenia jakie dane są potrzebne i jak je znaleźć. Użytkownik musi zbudować kwerendę w postaci procedury będącej opisem przedmiotu pytania oraz metody prowadzącej do uzyskania odpowiedzi. Takie proceduralne kwerendy przypominają programy komputerowe.

•

Nieproceduralne, które wymagają od użytkownika określenia jakie dane są potrzebne bez konieczności określania sposobu ich znalezienia. W językach tych, zwanych także deklaratywnymi (ang. declarative languageś), kwerenda składa się z dwóch części, z których pierwsza opisuje żądany schemat postaci wynikowej, a druga specyfikuje warunki jakie muszą spełniać dane stanowiące odpowiedź na zapytanie. Za wykonanie kwerendy odpowiedzialny jest system zarządzania bazą danych.

Większość komercyjnych systemów baz danych proponuje języki zawierające zarówno elementy proceduralne, jak i nieproceduralne. Nieproceduralne języki manipulowania danymi są zwykle łatwiejsze w użyciu. Mogą jednak generować kod mniej wydajny niż języki proceduralne. Efektywność kodu można zwiększyć poprzez zastosowanie technik optymalizacyjnych.

1.6. Administrator bazy danych Jednym z głównych zadań systemu zarządzania bazą danych jest możliwość kontroli zarówno danych, jak i programów mających do nich dostęp. Osoba (lub grupa osób) sprawująca taką kontrolę jest nazywana administratorem bazy danych. Administrator jest odpowiedzialny za szeroko rozumiany stan bazy danych i reprezentuje interesy właściciela systemu zarządzania bazą danych. Zadania administratora obejmują [3]: „,„„^ Definiowanie schematów; • Ł;, Definiowanie struktury pamięci i metod dostępu; •-•' Modyfikowanie schematów i fizycznej organizacji danych; Specyfikację warunków integralności; , " "" ' (Utrzymywanie kontaktu z użytkownikami; Badanie wydajności i reagowanie na zmiany wymagań; ~St Udzielanie zezwoleń na dostęp do danych; . , . : Bezpieczeństwo bazy danych; j l Kontrolę współbieżności; t Wykonywanie archiwalnych kopii bazy danych; •>•Odtwarzanie poprawnego stanu po awarii; Nadzorowanie pracy programistów systemowych i aplikacyjnych.

~—~„.—„...4 t„,JL".', ~

\ j ----•--"—---——>_*,„-,,*,.„„, j j

JL 1.7. Użytkownicy baz danych Ze względu na sposób współdziałania z systemem wyróżniamy trzy rodzaje użytkowników: •

Programiści zastosowań (ang. application programmers). Są to profesjonaliści, którzy współdziałają z systemem za pomocą komend języka manipulowania danymi, osadzonych w programie napisanym w języku macierzystym (Cobol, PL/1, C). Ze względu na różnice gramatyki DML i języka macierzystego, komendy DML są przetwarzane przez specjalny preprocesor zwany prekompilatorem DML, który je konwertuje na wywołania zwykłych procedur w języku macierzystym. Otrzymany program jest następnie przetwarzany przez kompilator języka macierzystego.

•

Użytkownicy doraźni (ang. casual users). Współpracują oni z systemem tylko za pomocą języka kwerend. Sformułowana przez nich kwerenda jest przetwarzana przez procesor kwerend do postaci zrozumiałej dla systemu zarządzania bazą danych.

•

Użytkownicy parametryczni (ang. parametric users). Nie dysponują oni żadnym językiem. Ich współpraca z systemem polega na wykorzystywaniu napisanych wcześniej aplikacji.

n

1.8. Architektura systemu zarządzania bazą danych

\,;

System zarządzania bazą danych (rys. 1.2) składa się z modułów realizujących poszczególne funkcje. Niektóre z tych fiłnkcji mogą być realizowane przez system operacyjny. W większości przypadków system operacyjny świadczy jedynie najbardziej podstawowe usługi i dlatego projekt musi zawierać interfejs między systemem zarządzania bazą danych i systemem operacyjnym. Do funkcjonalnych elementów systemu zarządzania bazą danych należą:

12

•

Program obsługi plików (ang. flle manager). Jest to program zarządzający alokacją przestrzeni dyskowej i strukturami danych używanych do reprezentowania informacji magazynowanej na dysku. Program zarządzający (ang. database manager). Jest to moduł stanowiący interfejs między przechowywanymi w bazie danymi niskiego poziomu i programami aplikacyjnymi oraz kwerendami zgłaszanymi do systemu. Do zadań tego modułu należą: interakcja z programem obsługi plików, sprawdzanie warunków integralności, zapewnienie ochrony przed niedozwolonym dostępem, odtwarzanie danych po awariach oraz kontrola współbieżnych operacji dokonywanych na bazie danych.

•

Procesor kwerend (ang. ąuery processor). Procesor ten tłumaczy zlecenia wyrażone w języku kwerend na instrukcje niskiego poziomu zrozumiałe dla programu zarządzającego. Ponadto procesor kwerend usiłuje dokonać przekształcenia zapytania użytkownika na równoważną, ale bardziej wydajną postać. Ma to na celu znalezienie lepszej strategii prowadzącej do uzyskania odpowiedzi. Proces ten nazywamy optymalizacją zapytania.

•

Prekompilator DML. Dokonuje on konwersji zleceń języka manipulowania danymi występujących w programach aplikacyjnych do zwykłych wywołań procedur w języku macierzystym. Prekompilator musi współdziałać z procesorem kwerend celem wygenerowania właściwego kodu.

•

Kompilator DDL. Przetwarza zdania wyrażone w języku definiowania danych na postać pozwalającą zorganizować fizyczne struktury bazy danych. Ponadto implementacja systemu musi zawierać następujące elementy:

• • •

Pliki danych; Słownik danych, który zawiera informacje o strukturze bazy danych; Indeksy, które zapewniają szybkie wyszukiwanie danych.

•

,

. Zapis { < NUMERJDENTYFIKACYJNY, 12-3456 >, < NAZWISKO, Kowalski >, < MIASTO, Łódź >, < ULICA, Narutowicza > } przedstawia pracownika o nazwisku Kowalski, numerze identyfikacyjnym 12-3456, mieszkającego w Łodzi przy ulicy Narutowicza. Zbiór encji oraz encja odpowiadają odpowiednio pojęciom typu oraz zmiennej w językach programowania. Baza danych składa się zatem z powiązanych ze sobą zbiorów, z których każdy zawiera dowolną liczbę encji tego samego typu.

3.2. Związki między encjami Między encjami mogą istnieć powiązania. Tworzą one zbiory związków encji. Formalnie związek taki jest matematyczną relacją określoną na n >1 zbiorach encji. Jeśli E\, E2,..., En są zbiorami encji, to zbiór związków encji R jest podzbiorem następującego zbioru: { < ei, e2,..., ea> \ ev e Ei,e2 e E2,... ,ea e £„ }. Tabele PROJEKTY oraz PRACOWNICY na rysunku 2.2 przedstawiają zbiory encji. Tabela KONTRAKTY (rys. 2.3) zawiera zbiór związków między pracownikami oraz projektami, w których realizacji oni uczestniczą. Jest to związek binarny, co oznacza, że uczestniczą w nim dwa zbiory encji. Ogólnie w związkach może występować więcej zbiorów encji. W związku KONTRAKTY mógłby uczestniczyć jeszcze jeden zbiór encji KLIENCI. Zapis < Kowalski, l, ABC > oznaczałby, że pracownik Kowalski bierze udział w realizacji projektu nr l, który jest wykonywany dla klienta ABC. Między tymi samymi encjami mogą istnieć różnego rodzaju związki. Obok związku KONTRAKTY między zbiorami encji PROJEKTY oraz PRACOWNICY można również zdefiniować związek ZARZĄDZA, który określałby kierownika danego projektu. Funkcja, którą encja spełnia w związku, jest nazywana rolą. Najczęściej role występują w postaci niejawnej i nie są definiowane. W pewnych przypadkach jednak określenie ich jest konieczne. Dotyczy to sytuacji, gdy zbiory encji w związku są nierozróżnialne. Klasycznym przykładem jest tu związek PRACUJE DLA między pracownikami przedsiębiorstwa, przedstawiający zależności służbowe. Jest to związek unarny, ponieważ biorą w nim udział encje pochodzące z jednego zbioru. Związek ten można przedstawić w postaci zbioru uporządkowanych par encji należących do zbioru PRACOWNICY. Pierwszy element pary pełni rolę kierownika, a drugi jego podwładnego. Związki tego typu nazywamy rekursywnymi.

22

Związek może być opisany za pomocą swoich atrybutów. Są one nazywane atrybutami powiązań, w odróżnieniu od atrybutów charakteryzujących encje. Atrybutem związku KONTRAKTY jest WYNAGRODZENIE. Atrybut ten określa, ile pieniędzy otrzyma dany pracownik za swój udział w danym projekcie. Zwróćmy uwagę, że WYNAGRODZENIE nie należy do opisu pracownika lub projektu. Opisuje jedynie związek miedzy nimi. Powiązania między encjami można charakteryzować od strony ilościowej. Charakterystyki takie określają minimalną i maksymalną liczbę wystąpień encji w danym związku. Należą one do warunków integralności, które muszą spełniać dane. Niech Z oznacza związek między zbiorami encji EI, E2, ... , £„ •

Z=<

£2,

£„>.

Charakterystykę ilościową związku Z oznaczamy przez Q i definiujemy następująco:

' , ,V

Q (Z) = < (a,, AX («2, &),..., (,

,

*,s

gdzie o/ oraz $ oznaczają odpowiednio minimalną i maksymalną ilość wystąpień encji ze zbioru E> w związku Z. Wielkości te określają liczebność (ang. cardinality) związku. Tytułem przykładu rozpatrzmy związek KONTRAKTY = < PROJEKTY, PRACOWNICY>. W biurze projektów mogą istnieć pracownicy, którzy nie biorą udziału w żadnym projekcie. Nie będą oni uczestniczyć w związku KONTRAKTY. Minimalna liczba wynosi więc 0. Trudniej jest określić wartość maksymalną ilości wystąpień danego pracownika w związku. W ilu projektach maksymalnie może on brać udział? Liczba ta może być ograniczona przez zarząd przedsiębiorstwa. Rozpatrzmy teraz zbiór encji PROJEKTY. W bazie danych mogą istnieć projekty, do których nie dokonano jeszcze przydziału kadry. Minimalna liczba również jest równa 0. Trudno jest natomiast jednoznacznie określić wartość maksymalną ilości pracowników zatrudnionych przy realizacji projektów. Mamy więc:

\

Q (KONTRAKTY) = < (O, *), (O, *) > .

Dla związku ZARZĄDZA naturalnym byłoby przyjęcie zasady jednoosobowego kierownictwa i odpowiedzialności. Oznacza to, że jeden projekt byłby związany dokładnie z jednym pracownikiem spełniającym rolę kierownika. Maksymalna wartość wynosiłaby w tym przypadku 1. (Minimalna, jak to wykazano wyżej, jest równa 0). Z iloma natomiast projektami byłby związany pracownik? Wartość minimalna jest równa O (nie każdy musi być kierownikiem). Wartość maksymalna zależy od wielkości obciążenia związanego z kierowaniem projektem. Otrzymujemy: SU"?

4.3*

%

*~

-

3 r , tł ~\ . Pomiędzy zbiorami encji A i B mogą istnieć następujące powiązania AB = < A, B >: •

Jeden-do-jednego. Encja ze zbioru A jest związana co najwyżej z jedną encją ze zbioru B oraz encja ze zbioru B jest związana co najwyżej z jedną encją ze zbioru A. Q(AB) = ,

•

gdzie k = O lub k = l .

Jeden-do-wielu. Encja ze zbioru A jest związana z dowolną liczbą encją ze zbioru B. Encja ze zbioru B może być natomiast związana z co najwyżej jedna encją ze zbioru A. Q (AB) = ,

gdzie k = O lub k = l .

Wiele-do-wiełu. Encja ze zbioru A jest związana z dowolną liczbą encji ze zbioru B i encja ze zbioru B jest Ł związana z dowolną liczbą encji ze zbioru A. :w

, --;;-"""" — • -

Q(AB)~• ~ " • " . " '

'••""•

Jeśli w związkach jeden-do-wielu i wiele-do-jednego k = l, to mówimy o związku ściśle hierarchicznym.

J

3.3. Silne i słabe encie _ -ł

0-1

- I Ł

ł

•

-«**•» • f .

,

-

\

**

!

,

^ "i ~ X

.

.

,

f

Identyfikację encji uzyskuje się za pomocą ich atrybutów. W zbiorze PRACOWNICY do odróżnienia poszczególnych encji wystarczy atrybut E#. Podobną rolę może także spełniać atrybut złożony E#, NAZWISKO. Sam atrybut NAZWISKO nie jest identyfikatorem, ponieważ mogą istnieć pracownicy o tych samych nazwiskach. Minimalny zbiór atrybutów posiadający własność identyfikacji w zbiorze nazywamy kluczem kandydującym (ang. candidate key). Zbiór atrybutów zawierający dowolny klucz kandydujący nazywa się nadkluczem (ang. superkey). Zbiór encji może posiadać więcej niż jeden klucz kandydujący. Wybrany przez projektanta bazy danych klucz kandydujący nazywamy kluczem głównym (ang. primary key). Istnieją zbiory encji, dla których nie istnieje klucz główny. Tytułem przykładu rozpatrzmy zbiór transakcji bankowych. Transakcja posiada trzy atrybuty: NUMERJTRANSAKCJI, DATA oraz KWOTA. Transakcje na różnych kontach mogą mieć te same numery. Na podstawie zbioru atrybutów nie można więc zbudować klucza głównego. Takie zbiory nazywamy zbiorami encji słabych (ang. weak entity). Encję, która posiada klucz główny nazywamy encją silną (ang. strong entity). Pojęcie silnych i słabych encji jest związane z pojęciem „zależności egzystencji" (ang. existence dependency). Zależność ta należy do warunków integralności. Mówimy, że między encjami x i y występuje zależność egzystencji, jeśli istnienie encji x zależy od istnienia encji y. Oznacza to, że jeśli encja y jest usuwana, to również encja x musi być usunięta. Mówimy, że encja y jest nadrzędna w stosunku do encji x lub że jest jej właścicielem. W powyższym przykładzie transakcje są encjami podrzędnymi (egzystencjalnie zależnymi) w stosunku do kont (encji silnych). Atrybut identyfikujący encje podrzędne w stosunku do określonej encji silnej nazywamy wyróżnikiem (ang. discriminator) lub kluczem cząstkowym (ang. partial key). W zbiorze transakcji bankowych rolę te pełni NUMERJTRANSAKCJI. Klucz główny zbioru encji słabych składa się z klucza głównego nadrzędnej encji silnej oraz wyróżnika.

3.4. Diagramy związków encji Ogólna logiczna struktura bazy danych może być przedstawiona graficznie za pomocą diagramu związków encji, który składa się z następujących elementów: • • • •

Prostokąty, które reprezentują zbiory encji; < . t :* Elipsy, które reprezentują atrybuty; Romby, które reprezentują związki między zbiorami encji; "*' * ^ "m- "Linie, które łączą atrybuty ze zbiorami encji oraz zbiory encji ze związkami.

Każdy składnik modelu ma określoną nazwę. Atrybuty będące kluczami głównymi są podkreślane. Model związków encji bazy danych dla biura projektów przedstawia rysunek 3.1. Diagram składa się z dwóch zbiorów encji: PROJEKTY z atrybutami: P#, NAZWA, BUDŻET, RODZAJ, PRIORYTET i MIASTO oraz PRACOWNICY z atrybutami: E#, NAZWISKO, WYKSZTAŁCENIE i WIEK. Między zbiorami istnieje związek wiele-do-wielu KONTRAKTY z atrybutem WYNAGRODZENIE. Kluczem głównym tego związku jest atrybut złożony P#, E#. - —-—-w- -

Rys. 3.1. Diagram związków encji dla danych z przykładu 2.1

-24

W związku mogą występować więcej niż dwa zbiory encji. Sytuację taką przedstawiono na rysunku 3.2. DCK oznacza związek między trzema zbiorami encji: DOSTAWCY, CZĘŚCI oraz KLIENCI. Do identyfikacji wystąpienia związku potrzebne są klucze główne wszystkich uczestniczących w nim zbiorów encji: D#, C# i K#. Dwa atrybuty nie wystarczą, na przykład parze wartości D#, C# mogą odpowiadać różne wartości K#, ponieważ dostawca może dostarczać daną część wielu klientom (podobnie dla pozostałych par atrybutów kluczowych: D#, K# oraz C#, K#). Na rysunku 3.2 niekluczowe atrybuty zbiorów encji zostały pominięte celem zwiększenia czytelności diagramu. Atrybut ILOŚĆ charakteryzuje związek DCK. Określa ilość danej części dostarczonej danemu klientowi przez określonego dostawcę. ;. •-. •••,.-: -.-..;;;?.>;. v -•.-,•,.£

•

•

;

•-

-

x

... J -;.;;--.--.-

—- ,,-:-:..- t ,

, . . . _ , - - - ^ . -. ;»...-.. ,:;.•..:-,•.:,.-

Role są przedstawiane na diagramach przez etykietowanie linii łączących prostokąty z rombami. Rysunek 3.3 przedstawia zbiór encji PRACOWNICY oraz zależność rekursywną PRACUJE_DLA. ' • ! .'.-

---'

f

Encje słabe oznacza się za pomocą podwójnych prostokątów. Odpowiadające im związki są reprezentowane przez podwójne romby. Rysunek 3.4 przedstawia diagram dla transakcji bankowych. Zbiór encji słabych TRANSAKCJE jest powiązany ze zbiorem encji silnych KONTA przez związek KONTRAN. Wyróżniki zbiorów encji słabych podkreśla się linią przerywaną. ^?• •

25

3.5. Specjalizacja i generalizacja

^ -t..-.

,, .. ( • • * , *.-

„ ->

„,.,.._

W modelu związków encji można uwzględniać poza omówionymi w poprzednich punktach inne, szczególne typy powiązań między danymi. Przy ich konstrukcji stosuje się specjalizację, generalizację i agregację [12]. 3.5.1. Specjalizacja Do zbioru encji mogą należeć podgrupy, które wyraźnie różnią się od pozostałych elementów. Mogą one posiadać swoje specyficzne atrybuty. Proces wyodrębniania grup w zbiorze encji nazywamy specjalizacją. Przykład 3.1. Rozważmy zbiór encji PRACOWNICY z atrybutami NUMER_PRACOWNIKA oraz NAZWISKO (rys. 3.5). Załóżmy, że każdy pracownik należy do jednego z następujących zbiorów: • •

PRACOWNICY_ETATOWI; ZLECENIOBIORCY.

Każda grupa jest opisana przez atrybuty encji zbioru PRACOWNICY plus dodatkowe atrybuty. Atrybutami pracownika etatowego są STANOWISKO oraz URLOP, a zleceniobiorcy - NUMER_ZLECENIA oraz WARTOŚĆ_ZLECENIA. Podstawą podziału jest tryb zatrudnienia.

26 f

Specjalizację można przeprowadzać wielokrotnie. Powstaje wtedy drugi poziom hierarchii. Wśród pracowników etatowych można wyodrębnić grupy w zależności od sposobu obliczania wynagrodzenia: • PRACOWNICY_AKORDOWI; • PRACOWNICY_DNIÓWKOWI; • PRACOWMCYJJMYSŁOWI.

'

•'"*" ' . « • t- *

3.5.2.Generalizacja

t

- ,

^ "

. » '

' \ ' ,'

e

'

Zbiory encji występujące w związku ISA można rozpatrywać jako zbiory wyższego i niższego rzędu. Podział początkowego zbioru w procesie specjalizacji jest wykonywany metodą zstępującą (ang. top*dawri). Projektowanie związku ISA może również przebiegać w odwrotnym kierunku. W metodzie wstępującej (ang. bottom-up) poszczególne zbiory encji łączy się w jeden na podstawie ich cech wspólnych. Proces ten nazywamy generalizacja. Początkowymi zbiorami dla projektanta mogą być PRACOWNICY_ETATOWI z atrybutami: NUMERJPRACOWNIKA, NAZWISKO, NUMERJCONTRAKTU i URLOP oraz ZLECENIOBIORCY z atrybutami NUMERJPRACOWNIKA, NAZWISKO, NUMER_ZLECENIA i WARTOŚĆJZLECENIA (przykład 3.1). Między zbiorami istnieją podobieństwa wyrażone występowaniem takich samych atrybutów. Na ich podstawie można utworzyć zbiór wyższego rzędu PRACOWNICY. W obu metodach stosowane są różne podejścia. Specjalizacja bazuje na różnicach między encjami danego zbioru. Utworzone w wyniku specjalizacji encje niższego rzędu mają inne atrybuty i uczestniczą w innych związkach niż pozostałe encje zbioru początkowego. Podział tego zbioru encji nie musi obejmować wszystkich jego elementów. Oznacza to, że encja zbioru wyższego rzędu nie musi mieć swojego odpowiednika w zbiorze niższego rzędu. W odróżnieniu od specjalizacji generalizacja bazuje na podobieństwach między zbiorami encji. Zbiór encji wyższego rzędu jest sumą zbiorów początkowych. Każda encja wyższego rzędu musi mieć więc swój odpowiednik w zbiorach niższego rzędu. 3.5.3. Więzy integralności Przy modelowaniu związków ISA można wprowadzać ograniczenia zgodne z wymaganiami użytkownika. Pierwszy typ ograniczeń dotyczy sposobu określenia które encje mogą należeć do zbiorów niższego rzędu. Wyróżnia się tu dwie możliwości: •

Przynależność zdefiniowana za pomocą warunku. Przy wyborze tego wariantu encja należy do zbioru niższego rzędu, jeśli jej atrybuty spełniają zdefiniowany warunek. Załóżmy, że zbiór encji PRACOWNICY z przykładu 3.1 posiada atrybut TRYBJZATRUDNIENIA. Wartość tego atrybutu definiuje przynależność do zbiorów niższego rzędu. Specjalizacja jest przeprowadzana automatycznie. • Przynależność zależna od użytkownika. W tej sytuacji nie istnieje jednoznaczny warunek przynależności. Dodanie encji do zbioru musi być dokonane przez użytkownika za pomocą odpowiedniej operacji. Drugi rodzaj ograniczeń określa czy encja może należeć do więcej niż jednego zbioru niższego rzędu. Zbiory niższego rzędu mogą więc być: •

Rozłączne. Specjalizacja zbioru PRACOWNICYJETATOWI z przykładu 3.1 prowadzi do powstania zbiorów rozłącznych. • Przecinające się. Zbiory PRACOWNICY ETATOWI oraz ZLECENIOBIORCY mogą zawierać te same encje. Etatowy pracownik przedsiębiorstwa (zbiór PRACOWNICYJETATOWI) może wykonywać dodatkową pracę w ramach umowy-zlecenia (zbiór ZLECENIOBIORCY). Ostatnie ograniczenie określa czy każda encja wyższego rzędu musi należeć do przynajmniej jednego zbioru encji niższego rzędu. Mamy więc przynależność: • •

Całkowitą. Obie specjalizacje z przykładu 3. l narzucają przynależność całkowitą. Częściową. Specjalizacja zbioru PRACOWNICY na podzbiory EKONOMIŚCI oraz INFORMATYCY nie narzuca przynależności każdego pracownika do jednego nich. W przedsiębiorstwie mogą być również zatrudnieni pracownicy z innym wykształceniem.

27

Przedstawione ograniczenia narzucają pewne wymagania dotyczące operacji wprowadzania i usuwania encji. Przykładowo warunek przynależności całkowitej wymaga, aby operacja wprowadzania nowej encji wyższego rzędu była związana z wprowadzeniem tej encji do co najmniej jednego zbioru niższego rzędu oraz aby operacja usuwania encji ze zbioru niższego rzędu powodowała jej usunięcie ze zbioru nadrzędnego. Analogiczne reguły obowiązują dla przynależności zdefiniowanej za pomocą warunku. Dla każdego związku ISA usunięcie encji ze zbioru wyższego rzędu wymaga jej usunięcia ze wszystkich zbiorów niższego rzędu, do których należy. 3.5.4. Dziedziczenie Encje niższego rzędu mają własność dziedziczenia cech encji nadrzędnych. Encje zbiorów PRACOWNICY ETATOWI oraz ZLECENIOBIORCY z przykładu 3.1 dziedziczą więc atrybuty NUMER PRACOWNIKA oraz NAZWISKO nadrzędnego zbioru PRACOWNICY. Zbiory te mają zatem po cztery atrybuty: PRACOWNICY_ETATOWI - NUMER_PRACOWNIKA, NAZWISKO, STANOWISKO, URLOP; ZLECENIOBIORCY - NUMER_PRACOWNIKA, NAZWISKO, NUMER_ZLECENIA, WARTOŚĆ_ZLECENIA. Oprócz atrybutów encje niższego rzędu dziedziczą także związki swoich encji nadrzędnych. Model danych z przykładu 3.1 można uzupełnić o związek PRACUJE_W zbioru encji PRACOWNICY ze zbiorem encji DZIAŁY (rysunek 3.6). Jest to związek jeden-do-wielu, ponieważ każdy pracownik może być zatrudniony tylko w jednym dziale. Encje zbiorów PRACOWNICY_ETATOWI oraz ZLECENIOBIORCY biorą udział w związku PRACUJE_W, czyli dziedziczą powiązanie swojego zbioru nadrzędnego PRACOWNICY.

Rys. 3.6. Związek hierarchiczny między zbiorami encji DZIAŁY i PRACOWNICY Zbiory encji z przykładu 3.1 tworzyły hierarchię, to znaczy każdy zbiór mógł występować jako podrzędny tylko w jednym związku ISA. Istnieją jednak sytuacje, w których zbiór encji powinien posiadać więcej niż jeden zbiór nadrzędny. Przykład 3.2. Rozważmy zbiór OSOBY z atrybutami NAZWISKO oraz WIEK (rys. 3.7). Encje tego zbioru oznaczają osoby związane z wyższą uczelnią, czyli pracowników i studentów. Obydwu grupom odpowiadają zbiory encji: PRACOWNICY z atrybutami NUMERJ>RACOWNIKA i STANOWISKO oraz STUDENCI z atrybutami NUMER ALBUMU i ROK STUDIÓW. Są to zbiory podrzędne w stosunku do zbioru OSOBY i dziedziczą one jego atrybuty. Przynależność do jednego ze zbiorów nie wyklucza przynależności do drugiego. Osoba będąca zarówno pracownikiem, jak i studentem posiada wszystkie atrybuty zbiorów OSOBY, PRACOWNICY oraz STUDENCI. Nie występuje potrzeba utworzenia dodatkowego zbioru. Czy na pewno? Jest tak, jeżeli nie istnieją specyficzne cechy osób jednocześnie pracujących i studiujących, cechy których nie mają ani pracownicy, ani studenci. W przeciwnym razie należy utworzyć nowy zbiór PRACOWNICYjSTUDENCI podrzędny w stosunku do zbiorów PRACOWNICY i STUDENCI. Może przy tym powstać zjawisko konfliktu nazw, ponieważ atrybuty są dziedziczone od dwóch zbiorów nadrzędnych - dziedziczenie wielokrotne. W omawianym przykładzie problem ten nie występuje.

Przykład 3.3. Za pomocą związku ISA można wyeliminować encje z atrybutami, których wartości są nieokreślone. Rozpatrzmy zbiór encji OSOBY o atrybutach: NAZWISKO, MIASTO oraz KOD. Załóżmy, że nie znamy kodu wszystkich osób. Wprowadźmy zbiór encji OSOBY_KOD podrzędny w stosunku do zbioru OSOBY (rys. 3.8). Należą do niego encje odpowiadające osobom, których kod jest znany. Mają one tylko jeden atrybut KOD. Pozostałe atrybuty są dziedziczone ze zbioru nadrzędnego. ,_ , ,c

Rys. 3.8. Diagram związków encji do przykładu 3.3

29

T Przykład 3.4.

,

„,-«_-—

Rysunek 3.9 przedstawia model związku unaraego MAŁŻEŃSTWO. Przez zastosowanie związku ISA diagram uzyskuje bardziej czytelną postać (rys. 3.10).

Przykład 3.5. Za pomocą związku ISA można przekształcić kardynalności związku z < (O, *), (O, *) > na < (l, *), (l, *) >. Rozważmy zbiory encji DOSTAWCY i CZĘŚCI oraz ich związek DOSTAWY (rys. 3.11). Powiązanie zbiorów jest typu wiele-do-wiełu: •--.... .„,..-•—— Q (DOSTAWY) = < (O, *),((),*)>.

Istnieją zatem dostawcy, którzy nie dostarczają żadnej części oraz części, które nie są dostarczane. Diagram na rysunku 3.12 przedstawia ten sam związek w innej postaci. Do zbioru D należą tylko encje oznaczające dostawców, którzy dostarczają przynajmniej jedną część. Analogicznie do zbioru C należą encje oznaczające części, które są dostarczane przynajmniej przez jednego dostawcę. Zbiory D i C nie mają dodatkowych atrybutów. Wszystkie ich atrybuty są dziedziczone.

30

3.6. Agregacja Rozpatrzmy diagram związków encji przedstawiony na rysunku 3.13. Między zbiorami encji PRACOWNICY i MASZYNY istnieje związek PRACUJE. Jest to powiązanie wiele-do-wielu. Parze pracownik-maszyna mogą odpowiadać produkty, będące rezultatem pracy. Wydaje się więc, że związki PRACUJE oraz PRODUKUJE można zastąpić jednym z trzema zbiorami encji. Uzyskalibyśmy znaczne uproszczenie diagramu. Niestety taka modyfikacja prowadziłaby do błędnego modelu. W wyniku utworzenia jednego związku każdej parze pracownik-maszyna musi odpowiadać produkt, co jest sprzeczne założeniem.

Model z rysunku 3.13 zawiera redundancję, ponieważ każda para pracownik-maszyna w związku PRODUKUJE występuje także w związku PRACUJE. Do modelowania takiego przypadku można zastosować agregację. Agregacja jest abstrakcją, w której związki są traktowane jak encje wyższego rzędu. W omawianym przykładzie zbiory PRACOWNICY i MASZYNY oraz związek PRACUJE można zastąpić zbiorem encji JEDNOSTKIJPRACY. Rysunek 3.14 przedstawia zmodyfikowany diagram po zastosowaniu agregacji.

31

4. Relacyjny model danych ftf

.- '•••«

»- >,

-.-,-

•

,

, ^, ,,

»-

-,-«,

_.

&0i

4.1. Definicje wstępne Relacyjną bazę danych można nieformalnie traktować jak parę złożoną z następujących składników [9]: • •

?A

Zbioru tabel, w których zawarte są informacje o obiektach i zwiajzkach między nimi; Zbioru zależności semantycznych.

»tł

Opis obiektu lub związku ma postać wiersza, którego składnikami są wartości odpowiednio wybranych w procesie projektowania atrybutów. Wartości pojawiające się w kolumnie muszą należeć do dziedziny atrybutu. Wiersze tabel nazywane są krotkami (ang. tupie). Definicja 4.1. Iloczynem kartezjańskim zbiorów D\, D2,..., DK nazywamy zbiór wszystkich uporządkowanych wartości < d\, d2,..., ^ . da >, takich że d\ eDj, d2 eĄ.,..., d^ e A . '«S

Definicja 4.2. Niech dany będzie skończony zbiór SCH- {A^,A2,..., An }, którego elementy będziemy nazywać atrybutami i niech każdemu atrybutowi A\ e SCH przyporządkowany będzie zbiór wartości A = DOM (A$ zwany dziedziną atrybutu. Relacją 7? rozpiętą na zbiorze SCF/nazywamy dowolny podzbiór iloczynu kartezjańskiego zbiorów D\,D2, ... , D„. Definicja 4.3.

s

~*

• •*:-"s--••

Zbiór nazw atrybutów SCH= {Ai,A2, ..., Aa] nazywamy schematem relacji.

'

J

Relację R o schemacie SCH zapisujemy w postaci R (SCH). Do oznaczenia krotki stosujemy zapis t (SCH). Zbiór atrybutówXbędący podzbiorem schematu Xc SCH nazywamy atrybutem złożonym. Zapis t (X) oznacza więc wartość atrybutu złożonego. Definicja 4.4. Schematem relacyjnej bazy danych określonym na zbiorze atrybutów U nazywamy zbiór schematów relacji { SCHi, SCH2, ..., SCH0}, przy czym SCHV u SCH2 u ... u SCHa = U. Szczególne znaczenie mają atrybuty, na podstawie których można jednoznacznie zidentyfikować krotki relacji., Nazywamy je kluczami. ,,»,„_.•..„, „„„. * * •> . ., . . - . .. .,-...,,%-. f f.-.,rr .•>t~~f>t>--iltjf T,- • f> ,-,; Definicja 4.5.

'

Kluczem A;relacji/? o schemacie ^.'//nazywamy minimalny zbiór atrybutów, taki że:

i

• •

KcSCH; Dla dowolnych krotek t\, (K) = t2(K)=> f, (SCH) = t^(SCH).

Oznacza to, że w relacji ^? nie ma dwóch krotek, które miałyby tę samą wartość klucza. Kluczem relacji PROJEKTY jest P#. Nie istnieją dwa projekty o tym samym numerze. Analogiczną rolę w relacji PRACOWNICY spełnia atrybut E#. W relacji KONTRAKTY nie można jednoznacznie zidentyfikować krotki na podstawie jednego tylko atrybutu. Klucz składa się z dwóch atrybutów: P# i E#. Własność klucza mogą posiadać różne zbiory atrybutów. Nazywamy je kluczami kandydującymi (ang. candidate key). Atrybuty należące do klucza kandydującego nazywamy kluczowymi. W relacji PRACOWNICY rolę klucza, oprócz atrybutu E#, może także spełniać atrybut NAZWISKO, jeśli nie ma dwóch pracowników o tym samym nazwisku. Klucz wybrany przez projektanta bazy danych nazywamy kluczem głównym (ang. primary key). W notacji używanej dla zapisu relacji atrybuty należące do klucza głównego podkreśla się, np. KONTRAKTY (P#, E#, WYNAGRODZENIE).

n

Definicja 4.6.

'.,

;

Nadkluczem relacji R o schemacie SCH nazywamy dowolny zbiór atrybutów X, taki że: •

K c X, gdzieś jest kluczem relacji R.

-

'"

- !

Każdy klucz jest nadkluczem. Nadkluczem jest zbiór atrybutów tworzących schemat relacji. Atrybut (lub grupa atrybutów) jednej relacji może stanowić klucz innej relacji. Nazywamy go kluczem obcym (ang. foreignkey). , Definicja 4.7.

" "

-- ; - -

^^-''-^^

' ^u - •- -

Atrybut X relacji # nazywamy jej kluczem obcym, jeżeli: • •

X nie jest kluczem relacji R; • Wartości atrybutu X są wartościami klucza innej relacji.

-

•» .,» y. , , f ."

W relacji KONTRAKTY kluczami obcymi są atrybuty P# i E#. Ich wartości są wartościami kluczy relacji PROJEKTY i PRACOWNICY. Unikalność klucza relacji należy do warunków integralności, które stanowią zbiór ograniczeń narzuconych na dane zapisane w bazie. Niektórzy autorzy włączają je do definicji schematu relacji. Definicja 4.8. Niech W oznacza zbiór warunków integralności dotyczących relacji R o schemacie SCH. Schematem semantycznym relacji R nazywamy parę ę - (SCH, W) . %,, :; R Przy rozpatrywaniu zbioru relacji (zbioru schematów semantycznych) należy uwzględnić warunki integralności dotyczące wzajemnej zgodności tych relacji.

"

Definicja 4.9.

'

Niech będzie dany zbiór schematów semantycznych relacji 0 = { ę\ , , * ,

X

Y

X OR Y

X AND Y

NOT X

0

0

0

0

1

0

'/2

%

0

1

0

1

1

1

Vi

0

'/2

0 0

Vi

Vi

Vi

Vi

Vi

Vi

Vi

1

Vi 0

Vi

Vi

0

1 I 1

0 Vi

1

1

1 1 1

1

Rys. 4.1. Operacje logiczne z wartością UNKNOWN

l

0 0

£1

4.2. Algebra relacji ^.:-.--,

t

-,»«ł-v

Rozważmy kwerendę: „Podaj nazwiska pracowników biorących udział w realizacji projektu Pl". Do znalezienia odpowiedzi trzeba użyć dwóch relacji: KONTRAKTY, gdzie znajdują się numery pracowników pracujących nad danym projektem oraz PRACOWNICY, gdzie znajdują się ich nazwiska. Pierwszym krokiem jest zbudowanie iloczynu kartezjańskiego tych relacji, następnie dokonanie selekcji krotek według odpowiedniego warunku i w końcu zastosowanie operacji projekcji na atrybut NAZWISKO. Można to wyrazić następująco: ONAZWISKO (^(PRACOWNICY x KONTRAKTY)) , gdzie F=KONTRAKTY.P# = 'P1' A KONTRAKTY.E# = PRACOWNICY.E# . ^•złączenie (0-join) jest kombinacją selekcji i iloczynu kartezjańskiego. Oznaczamy je przez *& gdzie * jest symbolem złączenia, a 6 oznacza predykat selekcji. Złączenie relacji według warunku równości atrybutów o tych samych dziedzinach nazywa się rawnozłączeniem (ang. eąuijoin). Operację 0-złączenia dwóch relacji R i S można przedstawić w postaci:

39*

NAZWISKO (PRACOWNICY V KONTRAKTY) NAZWISKO Kowalski Abowski Nowak Majewski Pawlak Grabowski Markiewicz Rys. 4.6. Nazwiska pracowników biorących udział w realizacji projektu P l - przykład 4.6 Kwerenda z przykładu 4.6 przyjmuje postać (rys. 4.6): ONAZWISKO (PRACOWNICY *F KONTRAKTY). Często warunek F wymaga równości atrybutów, które mają takie same dziedziny i nazwy. ^-złączenie dla takiego warunku nazywa się złączeniem naturalnym (ang. natural joiri). Jest ono oznaczane przez R * S. Przy wykonywaniu złączenia naturalnego, jeden z powtarzających się atrybutów jest usuwany z relacji wynikowej. Przebieg złączenia naturalnego relacji R i S jest następujący: l. 2. 3.

Wyznaczenie iloczynu kartezjańskiego relacji RiS; Wybór z relacji R x S krotek, których wartości wspólnych atrybutów relacji R i S są równe; Wykonanie projekcji usuwającej powtarzający się atrybut .

Złączenie naturalne można przedstawić za pomocą operacji podstawowych:

gdzie Z jest sumą schematów relacji R oraz S , a F jest formułą określoną następująco: F = R.Ai=

A

A

A

Warunek F wymaga równości wszystkich wspólnych atrybutów obu relacji. Zapis R,A\ oznacza atrybut A \ relacji R. Na rysunku 4.7 przedstawiono złączenie naturalne relacji PRACOWNICY i KONTRAKTY. W szczególnym przypadku, gdy relacje R oraz S nie mają wspólnych atrybutów, złączenie naturalne jest ich iloczynem kartezjańskim. .• <

Własności operacji złączenia: • • • •

Idempotentność: R*R = R; Łączność: R* (S* T) = (R* S)* T; Przemienność: R*S = S*R; Prawa rozdzielności:

,

Dla relacji R i S określonych na tych samych schematach:

Jeżeli ponadto relacja T ma taki sam schemat, to:

Niech relacje R oraz S będą określone na schematach odpowiednio X oraz Y. Niech W oraz Z oznaczają zbiory atrybutów. (l)Tlx(R*S)cR oraz Tly(R*S)cS; (2) Jeżeli Z e X, to Uz (R) *R = R;

(3) Jeżeli WuZ=X, to R c UW(R} * Uz (R) ;

{

^ n

. ~

-

-.

-

(4) Jeżeli 7=X, to R*S = Jeżeli ponadto relacje R i S nie mają wspólnych atrybutów, czyli Xr\Y-0, ioR*S = RxS.

PRACOWNICY * KONTRAKTY

E#

NAZWISKO

El El El E2 E2 E2 E3 E4 E4 E6 E6 E6 E7 E8 E9 E10 E10 E14 E18 E18 E19 E19 E20

Kowalski Kowalski

WYKSZTAŁCENIE

WIEK

P#

WYNAGRODZENIE

Ekonomista

43 43 43 53 53 53 34 41 41 23 23 23 38

Pl P4 P5 Pl P3 P5 P3 P2

1000 2000

P4 Pl P3 P4 Pl P3 P2 Pl P2 P2 Pl P4 P4 P5 Pl

4000

Kowalski

Ekonomista Ekonomista

Abowski

Prawnik

Abowski

Prawnik

Abowski

Prawnik

Azowski

Matematyk

Dakowski

Informatyk

Dakowski

Informatyk

Nowak

Elektronik

Nowak

Elektronik

Nowak

Elektronik

Majewski

Ekonomista

Abacki

Matematyk

Olczak

Informatyk

Pawlak

Ekonomista

Pawlak

Ekonomista

Nowakowski

Ekonomista

Grabo wski

Informatyk

Grabowski

Informatyk

Jodłowski

Informatyk

Jodłowski

Informatyk

Markiewicz

Elektronik

35 27 58 58 30 36 36 28 28 25

2000 1000

7000 1000

5000 5000 2000 3000 1000 1000 1000

8000 4000 3000 4000 2000 4000 2000 8000 6000

Rys. 4.7. Złączenie naturalne relacji PRACOWNICY i KONTRAKTY. Realizacja kwerendy z przykładu 4.6 za pomocą złączenia naturalnego wymaga połączenia relacji PRACOWNICY i KONTRAKTY, a następnie wykonania operacji selekcji celem wybrania krotek spełniających warunek P# = 'Pl'. Połączenie następuje względem wspólnego atrybutu E#. Operacja złączenia naturalnego łączy krotki z tą sama wartością E#. Rezultatem złączenia PRACOWNICY * KONTRAKTY jest relacja określona na schemacie (rys. 4.7): X= (E#, NAZWISKO, WYKSZTAŁCENIE, P#, WYNAGRODZENIE}.

*

'

'

W wyniku operacji złączenia powstają rozbudowane relacje, co zwiększa koszt wyszukiwania danych. Rozmiary relacji wynikowych mogą zostać zmniejszone przez taką zmianę kolejności wykonywanych działań, która spowodowałaby zmniejszenie rozmiarów relacji będących argumentami operacji złączenia. W tym celu w kwerendzie z przykładu 4.6 należy najpierw wybrać odpowiednie krotki z relacji KONTRAKTY, czyli wykonać operację selekcji przy warunku P# = 'Pl', a następnie wykonać projekcję na atrybut E#. Otrzymamy wtedy relację: *-• 'nvsm^ «:eto«d rw '-•

*i

=

HĘ* (ap# - TV (KONTRAKTY)) .

,-(t

rf-'-«S8Są;iw*>CI

"'i>ff

Relacja RI ma tylko jeden atrybut i zawiera numery pracowników biorących udział w realizacji projektu Pl. Następnym krokiem jest jej złączenie z relacją PRACOWNICY i wykonanie projekcji na atrybut NAZWISKO. Otrzymujemy relację: ^

= ONAZWISKO (*. * PRACOWNICY) . Wniosek: Celem optymalizacji algorytmów wyszukiwania informacji żądań operacje selekcji oraz projekcji powinny być wykonywane jak najwcześniej. Zmniejsza się w ten sposób liczbę danych, na których jest kontynuowana realizacja kwerendy. • " .\ - , ł ,

(3) Dzielenie

4. ^ f

Operacja dzielenia jest stosowana do wyrażenia kwerend, w których występuje fraza „dla wszystkich". Przykład 4.7.

"

"*~

J

- - "f''"-**?v'*

*

Rozważmy kwerendę: „Znajdź numery pracowników, którzy uczestniczą w realizacji wszystkich projektów, których budżet przekracza 100 000". W wyniku wykonania projekcji jrip#,E# (KONTRAKTY)

"i J.

p# Pl

Pl Pl

Pl Pl Pl Pl

P2 P2 P2 P2 P3 P3 P3 P3 P4 P4 P4 P4 P4 P5 P5 P5

E#

P#

E#

El E2 E6 E7 ElO E18 E20 E4 E9 ElO E14 E2 E3 E6 E8 El E4 E6 E18 E19 El E2 E19

Pl P4

El E6 E18

*

*. i„

Rys. 4.8. Iloraz relacji z przykładu 4.7 otrzymuje się zbiór powiązanych ze sobą par atrybutów P# i E#. Numery projektów o budżecie większym niż 100 000 można uzyskać za pomocą wyrażenia: R2 ~ Tlf# (^BUDŻET > 100 000 (PROJEKTY)) .

: należy znaleźć numery pracowników, które występują w relacji R} z każdym numerem projektu zawartym w R2. j ia to operacja dzielenia. 4 SSedi relacje R i S będą określone na schematach odpowiednio X i Y oraz niech Y c X. Krotka t należy do ilorazu ? -=- S relacji R i S, jeżeli dla każdej krotki s e S istnieje w R krotka r spełniająca następujące warunki: ,-t,„m~v „*>

r W = j(y)

oraz

/-(A^-IO = /(A r -y). - --

--..--...;.,«;-,---.

,,Q

lonz jest zbiorem takich krotek /, dla których konkatenacja krotek t i s należy do relacji R dla każdej krotki s występującej w S. Schemat ilorazu jest różnicą schematów relacji R oraz S. Za pomocą operacji podstawowych iloraz ji /? i S można przedstawić za pomocą następującego wzoru: ' - - ".; *M , | /J * 5 = Or-r W - IL--r ((Fb--r W x S) -/?) . IL-- K(/?) zawiera krotki spełniające warunek r (X- Y) = t (X- Y). Drugi człon wzoru służy do eliminacji autek, które nie spełniają warunku r (Y) = s (Y) . Wyrażenie FLy- r (K) x S określone na schemacie Xłączy każdą j attkę występującą w \\x-y (R) z każdą krotką występującą w S. Wobec tego wyrażenie (Ylx-r(R) x S)- R zawiera z pary krotek z Tix-r(R) i S, które nie występują w R. Jeżeli krotka t' występuje w HX-Y ((Y\x-r(R) * S)- R), to śmieje krotka s w S, która nie tworzy z krotką t' krotki r w R. Zatem t' zawiera wartości atrybutów ze zbioru X - Y, iJóre nie występują w R 4- S. Są to wartości eliminowane z relacji Hx- r (R) • Na rysunku 4.8 przedstawiono relacje R i i ? .?2 z przykładu 4.7 oraz ich iloraz. t ;:

„v"- v;j;„. , , , " . " • ,

^

423. Przykłady zastosowań * tym podpunkcie omówione wcześniej operacje algebry relacyjnej zostaną zilustrowane przykładami wyszukiwania Mnych. Wszystkie przykłady dotyczą modelu danych dla biura projektów (rys. 2.2 i 2.3). Przykład 4.8. j.

"""

' '

', ,

'?

&c ,>. 100000 (PROJEKTY)))) . 6.

Podaj numery pracowników, którzy uczestniczą w realizacji przynajmniej tych projektów, nad którymi pracuje Kowalski.

Znalezienie odpowiedzi wymaga realizacji następujących kroków: 1.

*A

Wyznaczenie numeru pracownika o nazwisku Kowalski: *6i = FU (CTNAZWISKO= -KO**M (PRACOWNICY)) .

2.

Wyznaczenie numerów projektów nad którymi on pracuje:

" ..... ^ ^K-^4^'1 « ,*

• .

RV = nP# (KONTRAKTY * R6r) . 3.

Wyznaczenie wszystkich par P#, E#, w których występują numery projektów znalezione w kroku drugim: *63 = nP#>E* (KONTRAKTY*^).

4.

Wyznaczenie numerów poszukiwanych pracowników:

Ostatecznie otrzymujemy:

,

r/.^r-.-^i, ,v

5l

.,ifp

_ . ,,

R; -

...

Numery pracowników biorących udział w realizacji danego projektu nie powtarzają się. W relacji KONTRAKTY nie mogą istnieć krotki o takiej samej wartości atrybutu złożonego P#, E# - klucza głównego relacji. Nie występuje zatem potrzeba eliminacji duplikatów. 3.

Podaj numery projektów w kolejności malejących budżetów. SELECT P# FROM PROJEKTY ORDER BY BUDŻET DESC

n

Klauzula ORDER BY określa uporządkowanie wyników zapytania. ASC oznacza kierunek rosnący, a DESC malejący. Uporządkowanie może być określone za pomocą więcej niż jednego atrybutu. Po klauzuli ORDER BY występuje wtedy lista, której każdy element zawiera nazwę atrybutu oraz określenie kierunku (ASC lub DESC). Porządek sortowania odpowiada kolejności atrybutów. 4.

Podaj pełną informację o wszystkich projektach. SELECT * FROM PROJEKTY

Gwiazdka zastępuje nazwy wszystkich atrybutów relacji PROJEKTY. 5.

Podaj nazwiska pracowników, którzy biorą udział w realizacji projektu Pl (przykład 4.8 - pytanie 2). SELECT NAZWISKO FROM PRACOWNICY WHERE E# IN (SELECT E# FROM KONTRAKTY WHERE P# = 'P1')

46

Wyznaczenie odpowiedzi wymaga wykorzystania relacji KONTRAKTY zawierającej numery pracowników -eałizujących projekty oraz relacji PRACOWNICY, w której znajdują się ich nazwiska. Numer poszukiwanego rracownika musi należeć do zbioru, który można opisać za pomocą bloku SELECT - FROM - WHERE. Operator IN >jpowiada matematycznemu symbolowi e należenia do zbioru. Opis zbioru przyjmuje postać ujętego w nawiasy wyrażenia zwanego podzapytaniem. Odpowiedź na podzapytanie w omawianej kwerendzie nie zależy od wartości występujących w zapytaniu głównym. Takie podzapytanie nazywamy zwykłym. Odpowiedź na omawiane pytanie można także wyrazić za pomocą pytania skorelowanego, którego wynik zależy od wierszy zapytania głównego: -• "• ••!''" - V >•"' . . ,

ą

-,..-j .• r^-ft •h.w* K Ł t

f",«

Klauzula WHERE może również zawierać porównania logiczne oraz konstrukcje: BETWEEN - określenie przedziału wartości atrybutu (np. WYNAGRODZENIE BETWEEN 3000 AND 5000), LIKE - określenie wzorca tekstu (np. NAZWA LIKE 'Fin%' - zostaną wyszukane krotki, w których wartość atrybutu NAZWA zaczyna się od 'Fin') oraz NULL - zbadanie czy wartość atrybutu jest określona (np. WYNAGRODZENIE IS NULL) [16]. Wyrażenia logiczne we frazie WHERE mogą być łączone za pomocą operatorów AND, OR i NOT. 6. Podaj numery pracowników z nieokreślonym wynagrodzeniem. SELECT DISTINCTE# FROM KONTRAKTY WHERE WYNAGRODZENIE IS NULL

,

'

' ' *fi •"*. , '

v

_

*1

... • .T

; , ;-*'-;

Przed słowem NULL można umieścić operator negacji NOT, na przykład dla realizacji kwerendy: „Podaj numery projektów, dla których został ustalony budżet". 7.

Podaj numery projektów, których budżety są większe niż budżet dowolnego projektu realizowanego w Warszawie. * SELECT P# FROM PROJEKTY WHERE BUDŻET > ANY (SELECT BUDŻET , FROM PROJEKTY WHERE MIASTO ='Warszawa')

>.

'^^ . • «,J.u.^--

-'

r- i , ^.. ^

Słowo kluczowe ANY stosuje się przy porównywaniu danej wartości z każdym elementem zbioru stanowiącego odpowiedź na podzapytanie. W zbiorze musi istnieć przynajmniej jeden element spełniający warunek, aby wyrażenie zawierające słowo ANY było prawdziwe. Odpowiada to wyrażeniu logicznemu, w którym zastosowano kwantyfikator szczegółowy.

47

8.

Podaj numery projektów, których budżety są większe niż budżet każdego projektu realizowanego w Warszawie. SELECT P# •;••-•.:... FROM PROJEKTY WHERE BUDŻET > ALL (SELECT BUDŻET * -^ FROM PROJEKTY WHERE MIASTO = 'Warszawa')

. .-

t

,

,-,_,,,

• , -.- . • , «-,j ^„./ , „ , > , ,...„.,-.,,

,

Słowo kluczowe ALL stosuje się przy porównywaniu danej wartości ze wszystkimi elementami zbioru stanowiącego odpowiedź na podzapytanie. Wszystkie elementy zbioru muszą spełniać warunek, aby wyrażenie zawierające słowo ALL było prawdziwe Odpowiada to wyrażeniu logicznemu, w którym zastosowano kwantyfikator ogólny. 9.

Podaj nazwiska pracowników biorących udział w realizacji przynajmniej jednego projektu, którego budżet przekracza 100 000 (przykład 4.8 -pytanie 5). ,, , .... „ ••:,»- 100 000))

Podzapytanie może być zawarte w innym podzapytaniu. Mówimy, że podzapytania są zagnieżdżone. 10. Podaj numery pracowników, którzy aktualnie nie pracują nad żadnym projektem (przykład 4.8 - pytanie 3). (SELECT E# FROM PRACOWNICY) EXCEPT (SELECTE# FROM KONTRAKTY)

: : . . , . ^ . . , v. N - . - * , — *

.

'

^-a^i"; ' ". ' '

SQL zawiera operacje na relacjach: UNION, INTERSECT oraz EXCEPT, które odpowiadają operatorom algebry relacyjnej: sumy, iloczynu oraz różnicy. To samo pytanie można także wyrazić następująco: SELECT E#

,

FROM PRACOWNICY WHERE NOT EXISTS . . . (SELECT * FROM KONTRAKTY WHERE E # = PRACOWNICY.Ef)

,.;;:,

. " • _ , - -

Wartością logiczną wyrażenia ( EXISTS podzapytanie ) jest TRUE, jeżeli odpowiedź na podzapytanie zawiera przynajmniej jedną krotkę. 1 1 . Podaj numery pracowników z wykształceniem ekonomicznym, którzy uczestniczą w realizacji projektu Pl . (SELECT E# FROM PRACOWNICY WHERE WYKSZTAŁCENIE = 'Ekonomista') . „,.. „ %-^, INTERSECT (SELECT E# ...,* . , . . . . . . . . • ' , : . , , , ; - -eW ^, =^.. FROM KONTRAKTY -^ .-. ' ' WHERE P# = 'P1') -. , . 12. Podaj numery pracowników z wykształceniem ekonomicznym lub którzy uczestniczą w realizacji projektu Pl.

;pr — ™

(SELECT E # ' . - , < • FROM PRACOWNICY WHERE WYKSZTAŁCENIE = 'Ekonomista') UNION • .' - - •-- • (SELECT E# FROM KONTRAKTY WHERE P# = 'Pl')

v. ;

^-

(]

,

, j .,

. ,, f ,

- ti

* , ' . . • j H*.-.,-.;-•*'

/'..

18. Podaj liczbę wszystkich projektów. • V"O#.,T:

SELECT COUNT (*) FROM PROJEKTY

-ł-, ; ,

Wynikiem kwerendy jest ilość krotek relacji PROJEKTY. 19. Podaj liczbę pracowników biorących udział w realizacji przynajmniej jednego projektu. SELECT COUNT (DISTINCT E#) FROM KONTRAKTY Użycie słowa kluczowego DISTINCT jest konieczne, ponieważ dany pracownik może uczestniczyć w realizacji wielu projektów i odpowiadające mu krotki mogą wystąpić wielokrotnie. Liczba krotek relacji KONTRAKTY może zatem różnić się od liczby pracowników. * „"!"•

; ».-

, "»«,- FROM PRACOWNICY WHERE WYKSZTAŁCENIE = 'Informatyk')

- ,• -.. ... ,. ..-.--^

...

.,

*,. . (.

Działanie operatora SUM obejmuje zbiór krotek, dla których wartość atrybutu E# należy do zbioru wyznaczonego przez podzapytanie. 23. Podaj numery pracowników pracujących nad projektem Pl, których wynagrodzenie jest najwyższe. SELECT E# ' ' -; - '' • / - ^J-", kśv „ • FROM KONTRAKTY WHERE P# = 'Pl1 AND WYNAGRODZENIE = (SELECT MAX (WYNAGRODZENIE) FROM KONTRAKTY WHERE P# = 'Pl') ~

. " - ->'. - ,: , .ttóm^^yy

Termin ,/iajwyższe wynagrodzenie" oznacza najwyższą wartość w ramach projektu Pl. Podzapytanie bez frazy WHERE oznaczałoby wybór najwyższej wartości spośród wynagrodzeń dotyczących wszystkich projektów. 24. Dla każdego realizowanego projektu podaj jego numer i liczbę pracowników biorących udział w jego realizacji. SELECT P#, COUNT (E#) FROM KONTRAKTY GROUP BY P#

zastosowaniu klauzuli GROUP BY relacja KONTRAKTY została podzielona na grupy z tą samą wartością go atrybutu (w tym przypadku P#). Wartość funkcji COUNT jest obliczana oddzielnie dla każdej grupy, |25, Podaj numery pracowników biorących udział w realizacji więcej niż jednego projektu (pytanie 16). : SELECT E# " FROM KONTRAKTY GROUP BY E# HAYING COUNT (*)> l

"""^ ' ""

r

'

'' -iMW« NJ;, : .

v

,

-

a

" 'A!Sv ^f ,„-,'

;

li GROUP BY często towarzyszy klauzula HAYING. Zawiera ona predykat odnoszący się do grup wierszy (jest riednikiem frazy WHERE dla grup wierszy). W powyższym pytaniu predykat ten zawiera funkcję COUNT, której cią jest liczba krotek w każdej grupie. Jeśli grupa posiada więcej niż jedną krotkę, jest akceptowana i odpowiedni •HBCT pracownika należy do odpowiedzi. '. - Podaj numery pracowników, którzy uczestniczą w realizacji przynajmniej jednego projektu, nad którym pracuje Kowalski (przykład 4.8 - pytanie 7). SELECT DIST1NCT E# i- : FROM KONTRAKTY WHERE P# IN (SELECT P# ' > ' FROM KONTRAKTY WHERE E# IN " ' " ' " "' •• •" 100 000 •>• ~t--j..- >*, „ •-, v , „ ; - , ' > . «c

-,,

* ^ „

Zadanie polega na przepisaniu odpowiednich krotek relacji PROJEKTY do relacji PROJ_100. Zbiór kopiowanych krotek zdefiniowano za pomocą kwerendy. Do usuwania informacji z bazy danych służy operacja DELETE. Usuwać można tylko pełne krotki. .^-..^y.J^.^f,

'"

-.,:. i.-^f.r,

, ,.,,„ ,, ,:,v , „ „^ ^, . ^„^ ^.^

rfax

.j^-f^ s, . .f'ff,t •

Przykładali. 1.

Usuń krotki relacji KONTRAKTY. DELETE FROM KONTRAKTY

2.

*a T

V s

.. l -: j- •> ttucJO i>---

Usuń z relacji PROJEKTY krotki dotyczące projektów, których budżet przekracza 1 00 000 PLN. DELETE FROM PROJEKTY WHERE BUDŻET > 1 00 000

3.

"s

Usuń z relacji KONTRAKTY wszystkie krotki dotyczące projektów, których budżet przekracza 100 000 PLN. DELETE FROM KONTRAKTY WHERE P# IN (SELECT P# FROM PROJEKTY WHERE BUDŻET > 1 00 000)

'

"

' '; '

* '«

,.,',,,

Aktualizację danych przeprowadza się za pomocą operacji UPDATE. Przykład 4.12. 1.

Zwiększyć wartości atrybutu BUDŻET w relacji PROJEKTY o 10%.

UPDATE PROJEKTY SET BUDŻET = BUDŻET * 1.1 2.

,.

~ ~

•

..^X-. -. -

Zwiększyć o 10% wynagrodzenia pracowników biorących udział w realizacji projektu Pl, jeśli nie przekraczają one 1000 PLN. , '" t

UPDATE KONTRAKTY ~. SET WYNAGRODZENIE = WYNAGRODZENIE* 1.1 WHERE WYNAGRODZENIE < 1000 AND P# = T l 1

>

- - •-, • •r .-'•" -L

*„. *" .-^ ,

Wszystkie relacje znajdujące się w bazie danych muszą zostać wcześniej zdefiniowane za pomocą instrukcji CREATE TABLE. Instrukcja ta może zawierać opcje definiujące warunki integralności, które stanowią zabezpieczenie przed niedozwolonymi zmianami. Do modyfikacji definicji służy instrukcja ALTER TABLE, a do usuwania relacji instrukcja DROP TABLE. Przykład 4.13. 1.

a

Utwórz relację KONTA z atrybutami: NUMER, NAZWISKO, DATA, TYP, PROCENT i SALDO oraz ustal atrybut NUMER kluczem głównym. J

' "

,, ń

l ^

CREATE TABLE KONTA (NUMER INTEGER NOT NULL, NAZWISKO CHAR (20), DATA DATĘ, TYP INTEGER, PROCENT DECIMAL (4,2), SALDO FLOAT, PRIMARY KEY (NUMER))

t *i; '

t- ' \<

~

'r'

-

,

/

'

.

f

: - "

•••• • J

-' "i

' •• •"

Ł

"

. ,

;

- , ' • - ' --.,-. .„>« M ~ * . - X - .;

-

' - » : * .

Do typów danych, które mogą występować w instrukcji CREATE należą: • • •

CHAR (n) - typ znakowy o stałej długości; YARCHAR (n) - typ znakowy o zmiennej długości, gdzie liczba n oznacza maksymalną ilość znaków; INTEGER - typ całkowity; SHORTINT - podzbiór typu całkowitego zależny od implementacji; ,«

DECIMAL (p, d) - typ numeryczny, gdzie p oznacza całkowitą ilość cyfr, a d - ilość cyfr po kropce oddzielającej część całkowitą od ułamkowej; ., ,. , ...... , , , , REAL - typ rzeczywisty zmiennopozycyjny; FLOAT - to samo co REAL; DOUBLE PRECISION - typ rzeczywisty podwójnej dokładności; • - . •«- - , t A^ BIT (n) - ciąg bitów o długości n; BIT YARYING (n) - ciąg bitów o długości co najwyżej n; DATĘ -typ dla określania daty, zawierający rok, miesiąc i dzień, oddzielone myślnikiem, np. 1999-12-29; TIME - typ dla określania czasu, zawierający godzinę, minutę i sekundę, oddzielone dwukropkiem, np. 11:07:25; Atrybuty typu DATĘ oraz TIME mogą być porównywane w zwykły sposób, to znaczy za pomocą operatorów nżywanych przy porównywaniu atrybutów liczbowych oraz znakowych. Zapis D ATA l < DATA2 oznacza, że wartość trybutu DATA1 jest datą wcześniejszą od wartości atrybutu DATA2. Porównywanie atrybutów typu TIME jest malogiczne. • •- - -,,- ,-< . Ł » : Dodaj do relacji KONTA atrybut PEŁNOMOCNIK.

-'i' '

ALTER TABLE KONTA ADD PEŁNOMOCNIK* INTEGER .:

••*" *

- •

' • > i,-;*- - -- -•• -.*•

Usuń relację KONTA. DROP TABLE KONTA

'-*^ „:.,'.

" ,4-

•-,,,-.;

Zwróćmy uwagę na różnicę między operacjami: DROP TABLE KONTA oraz DELETE FROM KONTA, Drugie polecenie usuwa wszystkie krotki z relacji KONTA. Definicja relacji zostaje jednak zachowana i można do niej wprowadzać nowe krotki. Operacja DROP TABLE natomiast powoduje usunięcie nie tylko zawartości relacji, lecz także definicji jej schematu. Nowe krotki do relacji KONTA można będzie wprowadzać dopiero po ponownym jej zdefiniowaniu za pomocą instrukcji CREATE TABLE. Oprócz klucza głównego (wiersz PRIMARY KEY w relacji KONTA) można także definiować klucze obce. Załóżmy, że atrybut PEŁNOMOCNIK* w relacji KONTA przyjmuje wartości klucza głównego PK# relacji KLIENCI, w której są opisani wszyscy klienci banku. Powiązanie takie definiuje się przez dodanie wiersza: \t i " FOREIGN KEY PEŁNOMOCNIK* REFERENCES KLIENCI (PK#). Wartości atrybutu można narzucić umieszczając w jego definicji, po słowie kluczowym CHECK, warunek, który -nuszą one spełniać, na przykład: '•"

"

.."-.-••-

PROCENT INTEGER CHECK (PROCENT > 0) . Innym rozwiązaniem jest zdefiniowanie dziedziny atrybutu za pomocą instrukcji CREATE DOMAIN: CREATE DOMAIN OPROCENTOWANIE INTEGER CHECK (YALUE > 0)

" . - * . * *

* *"

• '• , i "

*

umieszczenie w definicji relacji KONTA wiersza: -~ -'-i

PROCENT OPROCENTOWANIE.

• ł -

-

r;,,-',-,

, r ^

n

.,

' . -'

,

Bardziej złożone warunki definiuje się za pomocą asercji zwanych także więzami głównymi [15]. •i- --s»*n r^VN*J!'? -£s.*a^,~;.;

Przykład 4.14. Dla bazy danych biura projektów zdefiniuj następujący warunek: łączna liczba zatrudnionych prawników nie może przekraczać 10 Definicję asercji zapisuje się za pomocą instrukcji CREATE ASSERTION: CREATE ASSERTION PRAWO . CHECK (10 >= SELECT COUNT (*) FROM PRACOWNICY WHERE WYKSZTAŁCENIE ='Prawnik')

-

• - -

Innym aspektem ochrony danych jest zabezpieczenie przed nielegalnym dostępem. Administrator bazy danych ma możliwość przydzielania użytkownikom uprawnień pozwalających na wykonywanie poszczególnych operacji. Są one nadawane za pomocą instrukcji: GRANT listajpraw ON nazwa_relacji TO listajużytkowników .-*<

.-.- 1.. . •

Użytkownicy są rozpoznawani przez swoje identyfikatory. Instrukcja może zawierać frazę WITH GRANT OPTION, która umożliwia użytkownikowi przekazywanie posiadanych uprawnień innym użytkownikom. Do odwoływania uprawnień służy instrukcja REYOKE: REYOKE listajpraw ON nazwa jrelacji FROM listaj Przykład 4.15.

•- ••-«••--» . i r , _w -:S

,

-- *, ..H , .

definiuje kursor o nazwie C, którego zakresem jest relacja z atrybutami P# oraz BUDŻET. Relacja ta zawiera krotki odpowiadające projektom o budżetach przekraczających wartość S, gdzie 5 jest zmienną języka macierzystego. Zmienne używane zarówno języku macierzystym, jak i w SQL nazywa się dzielonymi [15]. W instrukcjach SQL są one poprzedzone dwukropkiem. Do otworzenia i zamknięcia kursora służą instrukcje: EXEC SQL OPEN

oraz

EXEC SQL CLOSE

Wartości wyznaczane przez kwerendę stają się dostępne za pomocą instrukcji: EXEC SQL FETCH FROM INTO Instrukcja FETCH wymaga po jednej zmiennej dla każdego atrybutu. Po jej wykonaniu zmiennym zostaną przypisane znalezione wartości atrybutów. W ten sposób wartości krotek stają się dostępne w programie. Dla zdefiniowanego wyżej kursora C należy zastosować następującą instrukcję: EXEC SQL FETCH FROM C INTO :PR_NUM, PRJBUD Instrukcja FETCH zwraca tylko jedną krotkę i przesuwa kursor do następnej. Po otwarciu kursor jest ustawiony na pierwszej krotce. Jeżeli są wymagane wszystkie krotki, to przetwarzanie zakresu kursora musi odbywać się w pętli. Przykład 4.20.

* , .\ „

Obliczyć sumę budżetów projektów realizowanych w Warszawie.

;i?pife'SXifrir

Definicja kursora:

Ą:l» !

.,* łL-:»; /^

59

5. Normalizacja relacji

Podczas projektowania relacyjnej bazy danych powstaje problem odpowiedniego wyboru różnych schematów relacji. Właściwy projekt powinien zapewnić możliwość zapisywania danych bez redundancji oraz łatwego wyszukiwania żądanych informacji. Najważniejszą metodą prowadzącą do uzyskania projektu o korzystnych właściwościach jest procedura normalizacji. Polega ona na projektowaniu relacji w odpowiednich postaciach normalnych. Do określenia czy relacja znajduje się w jednej ze zdefiniowanych postaci normalnych potrzebne są dodatkowe informacje o modelowanym wycinku rzeczywistości. Są to nałożone na dane więzy zwane zależnościami funkcyjnymi (sng.functional dependencieś). Muszą one zostać zidentyfikowane podczas projektowania. Błędnie zaprojektowane bazy danych posiadają niepożądane właściwości. Ich ilustracją jest następujący przykład: Przykład 5.1. Rozważmy relację UMOWY przedstawioną na rysunku 5.1. Jej schemat powstał w wyniku rozszerzenia schematu relacji KONTRAKTY z przykładu 2.1 o atrybuty NAZWA, BUDŻET, RODZAJ, PRIORYTET i MIASTO. Klucze główne obu relacji są takie same. ,

UMOWY

p#

E#

NAZWA

BUDŻET

RODZAJ

Pl

El E2 E6 E7 E10 E18 E20 E4

Kredyt Kredyt

110000 110000

Bankowość Bankowość

Warszawa Warszawa

1000 1000

Kredyt

110000

Bankowość

Warszawa

2000

Kredyt

110000

Bankowość

Warszawa

1000

Kredyt

110000

Bankowość

Warszawa

4000

Kredyt

110000

Bankowość

Warszawa

2000

Kredyt

110000

Bankowość

Finn

50000

Rachunkowość

P2 P2 P2 P3 P3 P3 P3 P4

E9 E10 E14 E2 E3

Firm

50000

Rachunkowość

Finn

50000

Rachunkowość

Pl Pl Pl Pl Pl

Pl

P2

PRIORYTET

MIASTO

WYNAGRODZENIE

Warszawa

6000

3 3 3 3 4 4 4 4 2

Łódź

5000

Łódź

8000

Łódź

3000

Łódź

4000

Łódź

7000

Łódź

5000

Łódź

3000

Łódź

1000

Poznań

2000

2

Poznań

4000

2

Poznań

1000

2

Poznań

4000

Finn

50000

Rachunkowość

Polisa

40000

Ubezpieczenia

Polisa

40000

Ubezpieczenia

E6 E8 El

Polisa

40000

Ubezpieczenia

Polisa

40000

Makler

30000

P4

E4

Makler

30000

P4

E6

Makler

30000

P4

E18

Makler

30000

Ubezpieczenia Rynek kapitałowy Rynek kapitałowy Rynek kapitałowy Rynek kapitałowy

P4

E19

Makler

30000

Rynek kapitałowy

2

Poznań

2000

P5 P5 P5

El E2 E19

Visa

200000

Bankowość

Kraków

2000

Visa

200000

Bankowość

Visa

200000

Bankowość

1 1 1

Kraków

1000

Kraków

8000

Rys. 5.1. Relacja UMOWY z przykładu 5.1

60?

Zauważmy, że relacja UMOWY powstała w wyniku złączenia relacji KONTRAKTY i PROJEKTY względem atrybutu c ?=. Model danych dla biura projektów wykorzystujący relację UMOWY zawiera redundancję. Informacje o projekcie « powtarzane wielokrotnie. Ilość powtórzeń jest równa liczbie pracowników uczestniczących w jego realizacji. xiczas wykonywania operacji zmieniających zawartość bazy danych występują następujące anomalie: ^ •

Każda aktualizacja danych projektu wymaga przejrzenia całej relacji celem znalezienia wszystkich krotek, które go opisują i dokonania odpowiednich zmian. W przeciwnym razie relacja będzie zawierać sprzeczne dane.

•

Do relacji UMOWY nie można wprowadzić informacji o projekcie przed przypisaniem do niego przynajmniej jednego pracownika.

•

Usuniecie z relacji UMOWY jedynej krotki dotyczącej danego projektu powoduje utratę informacji o tym \ projekcie. r

•lożna je wyeliminować, jeśli zamiast relacji UMOWY zastosujemy relacje o innych schematach. Przy ich : rojektowaniu należy uwzględnić zależności funkcyjne między atrybutami.

5.1. Zależności funkcyjne '*łt^~

l

,\

'^- '

*^ Y

' ,'•

''.r

- T''-, '^^'-'

::^;~i

':•

i

Krotka relacji UMOWY informuje, że pracownik o numerze E# bierze udział w realizacji projektu o atrybutach: P#, \AZWA, BUDŻET, RODZAJ, PRIORYTET, MIASTO i otrzymuje za to określone WYNAGRODZENIE. Dla każdej •artości atrybutu P# istnieje dokładnie jedna wartość każdego z atrybutów: NAZWA, BUDŻET, RODZAJ, -RIORYTET i MIASTO. O atrybutach NAZWA, BUDŻET, RODZAJ, PRIORYTET i MIASTO mówimy, że są --^fikcyjnie zależne od atrybutu P# lub że są przez niego identyfikowane. Definicja 5.1. Niech będzie dany zbiór atrybutów SCH oraz jego podzbiory X i Y. Mówimy, że Y jest funkcyjnie zależny od X, co zapisujemy X -> Y, wtedy i tylko wtedy, gdy dla każdej relacji R rozpiętej na schemacie SCH i dla każdych dwóch krotek t\ oraz *2 należących do R spełniony jest warunek: , , >••;>!•.!.

.v.

/••/}•*'".,-.,«-.,,

"

- '

'

'

'

•,

,

Niech SCH ~ { A, B, C, D}iF={A-+B, C - > D } . N a rysunku 5.8 przedstawiono relację R rozpiętą na tym schemacie oraz jej rozkład na relacje S i T o schematach SCHS = { A, B } i SCHj = { C, D } . Między atrybutami relacji S oraz. T istnieją zależności funkcyjne F s = { / 4 - » f i } i Fr={C-»£>}.

A

B

C

D

a! ai a2

b, bi b.

C]

d, d2 d,

C2 Cl

fVt "•"

,

A

B

A

C

ai a2

b, b2

Cl

d, d2

Rys. 5.8. Relacje dla przykładu 5.3

66

C2

Dokonany rozkład zachowuje zależności funkcyjne. Nie spełnia jednak warunku definicji 5.9. Relacja W, otrzymana w wyniku złączenia relacji S i T, zawiera fałszywą informację w postaci krotki, której nie ma w relacji R (rys. 5.9). Przykład 5.4. Rozłóżmy relację R o schemacie { A, B, C } i zależnościach funkcyjnych F = { AB ->• C, C -» B } na relacje: S o schemacie { A, C } i To schemacie { B, C }. Rozkład ten nie zachowuje zależności. Mamy bowiem: Fs = { } oraz FT = { C -> B }. Zależność AB-* C została stracona. '.;• "T , \ ,,,.,,,..,.., . Zawsze jednak można rozłożyć relację R o schemacie SCH na relacje w trzeciej postaci normalnej. Rozkład taki spełnia warunki definicji 5.9 i 5.10. Transformację przeprowadza się względem istniejących zależności funkcyjnych.

l

'V

J

A

B

C

D

ai »i a2 a2

b, b! b2 b.

Cl

d, d2 dt d2

C2 Cl C2

{3:» !£?,

W=S*T Rys. 5.9. Złączenie naturalne rzutów relacji R ~ przykład 5.3

Twierdzenie 5.2.

l

Jeżeli między atrybutami X oraz A relacji R o schemacie SCH istnieje zależność funkcyjna X -» A, przy czym X n A = 0, to relację tę można rozłożyć na relacje:

Dowód

•

" " ' . . . . :

Hf^^t »••>••'*

v

*' -

'

* f

Vr

- ^r ,

A -*

Definicja czwartej postaci normalnej różni się od definicji postaci normalnej BCNF tylko użyciem zależności wielowartościowych zamiast zależności funkcyjnych. Jedynymi nietrywialnymi zależnościami wielowartościowymi między atrybutami relacji w czwartej postaci normalnej są zależności funkcyjne. Każda relacja w 4NF jest również w BCNF. Załóżmy, że relacja R o schemacie SCH jest w postaci 4NF i nie jest w postaci BCNF. Istnieje więc nietrywialna zależność X ~> Y, gdzie X nie zawiera klucza. Ze względu na to, że X -> Y implikuje zależność wielowartościowąX ->-> Y, relacja R nie może być w 4NF, co jest sprzeczne z założeniem.

K#

P#

K#

MIASTO

Kl Kl

Pl

Łódź

K2

Pl

Kl Kl K2

K2

P3

K2

Wrocław

K3

P3

K2

Kraków

K3

Warszawa

P2

Warszawa Poznań

Rys. 5.15. Relacje KP i KM w czwartej postaci normalnej

,

Relację R z przykładu 5.7 można przekształcić do czwartej postaci normalnej za pomocą operacji projekcji. Otrzymujemy (rys. 5.15): •'^^••.,^;-„.~,T.,,^„^.^I.^

.y"'-

KP = OKUENT, PROJEKT (R)

oraz

KM = OKLENT, MIASTO (R) •

Relacje KP oraz KM są w czwartej postaci normalnej. Transformację przeprowadza się stosując rozkład tak długo, aż znikną „rzeczywiste" zależności wielowartościowe i powstaną dla nich oddzielne relacje. Należy jednak zaznaczyć, że procedura transformacji nie zawsze zachowuje zależności.

5.6. Piąta postać normalna

i

Poza zależnościami funkcyjnymi oraz wielowartościowymi schematy relacji mogą zawierać zależności związane z odwracalnością ich rozkładów.

R

A

B

C

at ai ai a2

bi

Cl

bt

C2

b2 bi

Cl Cl

Rys. 5.16. Relacja R do przykładu 5.8 Przykład 5.8. [4]

,

,

^_.

Rozważmy relację R przedstawioną na rysunku 5.16. Między jej atrybutami nie istnieją zależności funkcyjne i wielowartościowe. Wykonajmy projekcje (rys. 5.17):

/» = IŁ. B w, e=n*,c(«), S=IL,C(«). Relację T otrzymaną w wyniku złączenia relacji P i Q przedstawiono na rysunku 5.18. Zawiera ona wszystkie krotki relacji R oraz krotkę (a2, b\, c2). Jeżeli złączymy ją z relacją S, to otrzymamy relację początkową R. Relacja R jest więc złączeniem swoich trzech rzutów: .

*-

.

,

_

•

•

•

.

:

•

•

<

.

.

-

.,

-*,»,..:.-•-„

*-*;.*,

...

jt

^.^ - . -

-

.-^^v -

..

Mówimy, że dla relacji R jest spełniona zależność złączeniowa (&ag.join dependency).

p B

B

b,

b, b, b2

C

A

C

Cl

3i

Cl

C2

3i

C2

Cl

a2

Cl

Rys. 5.17. Projekcje relacji R z przykładu 5.8

A

B

C

3l

b, b! b2 bi b,

Cl

aj ai a2 aż

C2 C] Cl C2

Rys. 5.18. Złączenie relacji P i Q do przykładu 5.8 Definicja 5.14.

-*

Niech będzie dany schemat SCH oraz zbiór schematów SCHi, 5O/2, ... , SCHa, takich że SCHt c SCH oraz

^

SCH}\JSCH2V ... uSC/4 = SCH. W schemacie SCH zachodzi zależność złączeniowa *(SCHl,SCH2, ... ,SCHn), jeżeli dla każdej relacji R rozpiętej na schemacie SC7/jest spełniony warunek: P— — KI j? K

$ K.2 p it

...

# KU D ,

gdzie R{ jest relacją schemacie SCH\, otrzymaną w wyniku projekcji relacji R na zbiór SCH\. Warunek w definicji 5.14 oznacza, że relację R można rozłożyć na relacje o mniejszej ilości atrybutów. Jeśli jeden ze schematów SCH, jest równy SCH, to taką zależność złączeniową nazywamy trywialną. Zależności złączeniowe oznaczają sprzężenia między atrybutami. Tytułem przykładu rozważmy relację LPS (L#, P#, S#), gdzie atrybuty L#, P# i S# oznaczają odpowiednio numer lekarza, pacjenta oraz urządzenia medycznego. Krotka relacji LPS oznacza, że lekarz L# bada pacjenta P# za pomocą urządzenia S#. Między atrybutami L#, P# i S# występuje zależność złączeniowa, gdy prawdziwe jest następujące wnioskowanie: Jeśli: • • •

ai',