6

Więzy i wyzwalacze w języku SQL

W bieżącym rozdziale opiszemy te aspekty języka SQL, które umożliwiają tworzenie elementów „aktywnych". Elementy aktywne są to takie wyrażenia lub instrukcje, które raz zapisane w bazie danych są automatycznie uruchamiane w odpowiedniej chwili. Czas wykonania zależy od zajścia pewnego zdarzenia, na przykład próby wstawienia rekordu do określonej tabeli, lub od takiego stanu bazy danych, kiedy określony warunek logiczny zaczyna być spełniony.

Programiści, tworzący oprogramowanie modyfikujące stan bazy danych, muszą uporać się z tym, że nowe dane mogą się okazać niewłaściwe w bardzo różny sposób. Najprostszą metodą uniknięcia wstawienia do bazy danych błędnego rekordu jest taka konstrukcja programu, która każdą operację wpi­sywania danych do bazy, usuwania lub modyfikacji wiąże z warunkami opi­sującymi ich poprawność. Niestety warunki poprawności często są skompli­kowane, a ich sprawdzanie musi być powtarzane - program użytkowy musi przetwarzać te same testy przy każdej modyfikacji.

Na szczęście język SQL2 jest wyposażony w narzędzia pozwalające dołączać więzy integ~alhości do schematu bazy danych. W tym rozdziale omówimy podstawowe metody. Najpierw opiszemy więzy kluczy - czyli więzy określone na atrybutach lub zbiorach atrybutów pełniących funkcje kluczy tabeli. Następnie rozważymy więzy integralności referencyjnej, tzn. wymagania polegające na tym, żeby wartości atrybutu lub zbioru atrybutów jednej tabeli (np. prezC# w tabeli Studio) występowały również w innej tabeli (np. cert# w FilmDyr). Potem przyjrzymy się więzom, które można nakładać na domeny, włączając w to niepowtarzalność pewnych wartości (,jednoznaczność"), ograniczenia zakresu domen do zadanych wartości oraz ochronę przed występowaniem wartości pustych NULL. Rozważymy również więzy dla rekordów lub tabel oraz związki między tabelami, nazywane ase~­cjami. Zachowanie więzów jest zawsze testowane, gdy modyfikacja dotyczy tabeli, na której są one określone.

W końcu zostaną omówione „wyzwalacze", stanowiące taką formę ele­mentów aktywnych, która umożliwia inicjowanie wykonania szeregu czyn­

6.1. KLUCZE W 1ĘZYKU SQL 379

ności na skutek zajścia określonego zdarzenia, na przykład wprowadzenia nowych danych do określonej tabeli. W standardzie SQL2 nie ma wyzwala­czy, ale następna wersja SQL3 już je zawiera. Mimo że definicja standardu SQL3 w czasie pisania tej książki jeszcze nie istnieje, to kilka komercyjnych systemów baz danych już wyposażono w pewne wersje wyzwalaczy.

6.1. Klucze w j ęzyku SQL

Prawdopodobnie najważniejszym rodzajem więzów w bazach danych jest określenie, który atrybut albo które atrybuty tworzą klucz relacji. Oznacza to, że żadne dwie krotki tej samej relacji nie mogąmieć takiej samej wartości atrybutu określonego jako klucz lub identycznych wartości atrybutów tworzących złożo­ny klucz. Więzy klucza, tak jak wiele imych więzów, są określane w instrukcji języka SQL - CREATE TABLE. Klucz można zadeklarować na dwa sposoby: przez użycie slowa kluczowego PRIMARY KEy lub slowa kluczowego u1VIQUE. Jednak w tabeli może wystąpić tylko jeden klucz glówny, natomiast kolumn mających tę cechę, że wartości w nich zawarte są różne, może być wiele.

6.1.1. Definiowanie kluczy

Klucz glówny może skladać się z jednego lub kilku atrybutów relacji. Klucz główny tabeli opisującej przechowywaną relację można deklarować w instrukcji CREATE TABLE na dwa różne sposoby.

1. Można zadeklarować, że dany atrybut jest kluczem glównym przy opisie tego atrybutu w schemacie relacji.

2. Do zbioru atrybutów zadeklarowanych w schemacie relacji (które są tylko zwykłymi atrybutami) możemy dodać deklarację, która określa, że uprzednio zdefiniowany atrybut lub zbiór atrybutów tworzy klucz główny.

Korzystając z pierwszego sposobu, dopisujemy slowo kluczowe PRIMA­RY KEY po nazwie atrybutu i jego typu. Postępując według drugiego sposobu, wprowadzamy do listy atrybutów nowy atrybut, złożony ze slowa PRIMARY KEY oraz ujętej w nawiasy nazwy atrybutu lub listy nazw atrybutów, które tworzą klucz główny. Należy dostrzec, że jeśli klucz składa się z więcej niż jednego atrybutu, to należy skorzystać z drugiego sposobu.

PRZYKŁAD 6.1

Rozważmy ponownie schemat relacji GwiazdaFilmowa, opisany w przykła­dzie 5.32. Kluczem głównym tej relacji jest nazwisko. Możemy ten fakt

3gO 6. WIĘZY I WYZWALACZE W JĘZYKU SQL

zdefiniować w wierszu deklaracji atrybutu nazwisko. Rysunek 6.1 zawiera obraz z rys. 5.13 z uwzględnieniem opisanej zmiany. Można skorzystać jed­nak z drugiego sposobu - odrębnego definiowania klucza głównego. Wów­czas w tekście z rys. 5.13 po wierszu 5) pojawi się deklaracja klucza główne­go i nie ma potrzeby definiowania tej cechy w wierszu 2). Wynikowy obraz deklaracji schematu relacji zawiera rys. 6.2.

1) CREATE TABLE GwiazdaFilmowa

2) nazwisko CHAR(30) PRIMARY KEY, 3) adres VARCHAR(255),

4) płeć CHAR(1),

5) dataUrodzenia DATE );

RYSUNEK 6.1

Zdefiniowanie nazwiska jako klucza głównego

1) CREATE TABLE GwiazdaFilmowa 2) nazwisko CHAR(30),

3) adres VARCHAR(255), 4) płeć CHAR(1),

5) dataUrodzenia DATE, 6) PRIMARY KEY(nazwisko) );

RYSUNEK 6.2

Oddzielna deklaracja klucza głównego

O

Zauważmy, że w przykładzie 6.1 obie postaci, ta z rys. 6.1 i ta z rys. 6.2, są jednakowo poprawne, ponieważ klucz główny składa się z jednego atry­butu. Jednak w sytuacji, gdy klucz główny składa się z więcej niż jednego atrybutu, należy korzystać ze stylu z rys. 6.2. Na przykład w deklaracji sche­matu relacji Film, której kluczem jest para atrybutów tytuł oraz rok, trzeba na końcu listy atrybutów dopisać wiersz:

PRIMARY KEY (tytuł, rok)

Jeszcze inaczej można określać klucz, korzystając ze słowa kluczowego UIVIQUE. Ten wyraz może pojawić się w takich samych kontekstach jak określenie PRIMARY KEY: albo po zdefiniowaniu atrybutu i jego typu, albo jako oddzielny element instrukcji CREATE TABLE. Ma on takie same zna­czenie jak określenie PRIMARY KEY. Jednak w instrukcji CREATE TABLE może wystąpić wiele specyfikacji UNIQUE, natomiast tylko jedna PRIMARY KEY.

6.1. KLUCZE W JĘZYKU SQL 3 g 1

PRZYKŁAD 6.2

Wiersz 2) z rys. 6.1 mógłby zostać zapisany następująco:

2) nazwisko CHAR(30) UNIQUE,

Można również zmienić wiersz 3):

3) adres VARCHAR(255) UNIQUE,

jeśli mamy przeświadczenie, że dwie gwiazdy filmowe nie mogą mieć takiego samego adresu (założenie budzące wątpliwości). Podobnie można zmodyfi­kować wiersz 6) z rys. 6.2, nadając mu postać:

6) UNIQUE (nazwisko)

0

Więzy klucza są to więzy deklarowane albo jako PRIMARY KEY, albo ja­ko UNIQUE. Formalna różnica między tymi dwoma typami deklaracji została opisana w ramce w p. 6.2.2.

6.1.2. Wymuszanie więzów klucza

Przypomnijmy rozważania z p. 5.7.7, z których wynika, że każda imple­mentacja języka SQL zawiera mechanizmy definiowania indeksów w sche­macie bazy danych, mimo że nie są one elementem standardu SQL. W natu­ralny sposób buduje się indeks na polach kluczy po to, by ułatwić typowe zapytania korzystające z ich wartości. Można także określać indeksy na in­nych atrybutach, specyfikowanych jako UNIQUE.

Stąd też, jeśli w zapytaniu klauzula wHERE zawiera porównanie klucza z konkretną wartością, np. w relacji GwiazdaFilmowa z przykładu 6.1: na­zwisko = ~Audrey Hepburn~, to właściwe krotki zostaną bardzo szybko wybrane, bowiem nie trzeba będzie przeglądać wszystkich krotek relacji.

W wielu implementacjach języka SQL instrukcja, która jest przeznaczona do tworzenia indeksu, może być użyta również do określenia klucza przez dopi­sanie słowa kluczowego UNIQUE. Na przykład użycie instrukcji

CREATE UNIQUE INDEX RokIndeks On Film (rok);

dałoby ten sam wynik, co wykonanie instrukcji zakładania indeksu z przykła­du 5.7.7, a ponadto spowodowałoby określenie więzów jednoznaczności atry­butu rok w relacji Film (niezbyt rozsądne założenie).

Rozważmy przez chwilę, w jaki sposób więzy klucza są wymuszane przez system SQL. W zasadzie więzy muszą być sprawdzane zawsze wtedy,

3Ó2 6. WIĘZY I WYZWALACZE W JĘZYKU SQL

kiedy następuje próba zmiany zawartości bazy danych. Oczywiście jednak naruszenie więzów klucza relacji R może zaistnieć wyłącznie wówczas, gdy ma nastąpić modyfikacja danych właśnie tej relacji. A poza tym usunięcie danych nie może powodować naruszenia więzów klucza relacji R, niebez­pieczne mogą być tylko operacje wstawiania danych lub ich modyfikacji. Dlatego standardowa praktyka w systemie SQL polega na sprawdzaniu wię­zów klucza tylko podczas operacji wstawiania i modyfikacji danych w relacji.

Indeksy dla atrybutów kluczowych są potrzebne po to, by system SQL korzystał efektywnie z więzów klucza. Jeśli indeks jest dostępny, to zawsze gdy dołączamy do relacji nową krotkę albo modyfikujemy klucz istniejącej, korzystamy z indeksu, aby sprawdzić, czy przypadkiem nie ma już innej krot­ki o danej wartości atrybutu deklarowanego jako klucz. Jeśli taka krotka wy­stępuje, to system nie może dopuścić do wykonania działań.

Jednak nawet jeśli na atrybucie klucza nie założono indeksu, to i tak można przestrzegać ograniczeń wynikających z więzów klucza. Trzeba wów­czas sprawdzać całą relację w poszukiwaniu krotki o określonej wartości klu­cza. Taki proces jest nadzwyczaj czasochłonny i w przypadku dużych baz danych modyfikacja może się okazać niewykonalna.

6.1.3. Ćwiczenia do podrozdziału 6.1

Ćwiczenie 6.1.1. W naszej przykładowej bazie danych filmów z podrozdziału 3.9 zdefiniowano klucze dla wszystkich relacji. Należy tak zmodyfikować deklaracje z ćwiczenia 5.7.1, aby włączyć deklaracje kluczy dla wszystkich tych relacji. Przy­pomnijmy, że wszystkie trzy atrybuty sąkluczem relacji GwiazdyW.

Ćwiczenie 6.1.2. Zaproponuj właściwe klucze relacji w bazie danych komputerów osobistych PC z ćwiczenia 4.1.1. Włącz deklaracje t ych kluczy do zapisanego w SQL schematu z ćwiczenia 5.7.2.

Ćwiczenie 6.1.3. Zaproponuj właściwe klucze relacji w bazie danych bitew morskich z ćwiczenia 4.1.3. Włącz deklaracje tych kluczy do zapisanego w SQL schematu z ćwiczenia 5.7.3.

6.2. Integralność referencyjna i klucze obce

Inny ważny rodzaj więzów w schemacie bazy danych wynika stąd, że wartości poszczególnych atrybutów muszą zachowywać pewne właściwości. Na przykład taki atrybut jak prezc# relacji studio musi wskazywać na właściwego dyrektora produkcji. Wynika stąd warunek integralności referen­cyjnej polegający na tym, że jeśli w krotce studia występuje określona war­tość atrybutu prezC#, to nie wzięła się ona z powietrza, a stanowi ona numer

6.2. INTEGRALNOŚĆ REFERENCYJNA I KLUCZE OBCE

383

certyfikatu rzeczywistego dyrektora produkcji. W sensie bazy danych „rze­czywisty" dyrektor to taki, który występuje w relacji FilmDyr. Oznacza to, że w relacji FilmDyr występuje krotka z wartościąc w polu cert#.

6.2.1. Deklarowanie więzów klucza obcego

W języku SQL można zadeklarować, że atrybut lub atrybuty z jednej re­lacji stanowią klucz obcy, odpowiadający pewnym atrybutom z innej relacji (a czasem tej samej relacji). Wynikają stąd dwa wnioski:

1. Atrybuty klucza obcego muszą być zadeklarowane jako klucz główny w oryginalnej relacji.

2. Każda wartość występująca jako atrybut klucza obcego musi wystąpić również jako wartość odpowiedniego atrybutu w oryginalnej relacji. Oznacza to istnienie więzów integralności referencyjnej łączących dwa atrybuty lub ich zbiory.

Tak samo jak w przypadku klucza głównego, klucz obcy można dekla­rować na dwa sposoby:

a) Jeśli klucz obcy jest pojedynczym atrybutem, to możemy określić jego nazwę i typ, deklarując go przez odwołanie do innego atrybutu (klu­cza głównego), w pewnej tabeli. Postać deklaracji jest następująca:

REFERENCES <tabela> (<atrybut>)

b) Alternatywnie można do listy atrybutów w instrukcji CREATE TABLE dołączyć jedną lub więcej deklaracji określające, że atrybut, lub ich zbiór, jest kluczem obcym. Potem należy wymienić nazwy tabeli oraz atrybutów (muszą one stanowić klucz główny), do których odwołuje się klucz obcy. Oto postać takiej deklaracji:

FOREIGN KEY <atrybuty> REFERENCES <tabela> (<atrybuty> )

PRZYKŁAD 6.3

Załóżmy, że deklarowana relacja

Studio (nazwa, adres, prezC#)

zawiera klucz główny nazwa oraz klucz obcy prezC#, określający odwołanie do atrybutu cert# w relacji

FilmDyr (nazwisko, adres, cert#, cenaSieci)

384 6. WIĘZY 1 WYZWALACZE W JĘZYKU SQL

Odpowiedniość między atrybutami prezC# i cert# można deklarować bez­pośrednio w sposób następujący:

CREATE TABLE Studio

nazwa CHAR(30) PRIMARY KEY, adres VARCHAR(255),

prezC# INT REFERENCES FilmDyr(cert#) );

Alternatywny sposób deklarowania klucza polega na włączeniu oddzielnej deklaracji klucza do instrukcji:

CREATE TABLE Studio

nazwa CHAR(30) PRIMARY KEY, adres VARCHAR(255),

prezC# INT,

FOREIGN KEY prezC# REFERENCES FilmDyr(cert#) ):

Należy zauważyć, że klucz obcy cert#, do którego wystąpiło odwołanie, jest w rzeczywistości kluczem głównym relacji FilmDyr. To, że atrybut cert# jest kluczem głównym relacji FilmDyr, gwarantuje poprawność określenia atrybutu prezC, który się do niego odwołuje jako do klucza obcego.

Klucze główne i atrybuty różnowartościowe

Deklaracja PRIMARY KEY jest nieomal synonimem deklaracji UNIQUE. Najważniejsza różnica polega na tym, że jest jeden tylko klucz główny dla całej tabeli, a atrybutów różnowartościowych typu UNIQuE może być wiele. Jednak istniejąrównież subtelniejsze różnice:

1. Klucz obcy może wskazywać wyłącznie klucz główny relacji.

2. Do implementacji systemu zarządzania bazami danych można dołączyć szereg mechanizmów, które rozszerzają zestaw właściwości klucza głównego. Nadają one temu pojęciu nowe znaczenie, które nie jest zawarte w standardzie SQL2. Na przykład, jak już wspomniano w p. 6.1.2, producent systemu baz danych może zawsze dla klu­cza głównego konstruować indeks (nawet jeśli jest to klucz złożony), a w przypadku innych atrybutów pozostawić użytkownikowi decyzję o założeniu indeksu. lany sposób polega na tym, że tabela może być utrzymana w postaci posortowanej według klucza głównego, o ile taki istnieje.

6.2. INTEGRALNOŚĆ REFERENCYJNA I KLUCZE OBCE 3 óS

Każda z tych dwóch deklaracji klucza obcego oznacza, że dowolna war­tość występująca w polu prezC# pewnej krotki relacji studio występuje także w jakiejś krotce relacji FilmDyr w polu cert#. Jedyny wyjątek stano­wi wartość NULL, której wystąpienie w relacji studio nie wymusza istnienia odpowiednika w polu cert# relacji FilmDyr (w praktyce nie dopuszcza się bowiem wartości NULL jako wartości klucza glównego, zob. rozdz. 6.3.1).

6.2.2. Przestrzeganie zasad więzów integralności referencyjnej

Wiadomo już, w jaki sposób deklarować klucz obcy, a także to, że w wyniku jego użycia każda wartość klucza obcego różna od NULL musi mieć swój odpowiednik we wskazywanej relacji. Ale w jaki sposób przestrzegać tej zasady w obliczu konieczności wprowadzania zmian do bazy danych? Można tu wyróżnić trzy rodzaje postępowania:

Postępowanie standardowe: odrzucanie zmian naruszających więzy

W języku SQL przyjmuje się przez domniemanie, że o ile nie zostanie wybrana inna strategia, to każda próba naruszenia więzów integralności refe­rencyjnej będzie powodowała automatyczne odrzucenie polecenia przez sys­tem. Rozważmy ponownie przykład 6.3, w którym wartość prezC# relacji studio musi wystąpić również w polu cert# relacji FilmDyr. Zatem przy stosowaniu standardowej procedury nie powiedzie się wykonanie następują­cych dzialań (tzn. zostanie wygenerowany błąd wykonania):

1. Próbujemy wstawić nową krotkę do relacji studio, której wartość prezC# jest różna od Nt1LL i która nie występuje w polu cert# rela­cji FilmDyr. Próba wstawienia nie powiedzie się, krotka nie zostanie wstawiona.

2. Próbujemy zmienić określoną krotkę relacji studio, tak aby wartość w polu prezC# byla różna od NULL. Wstawiana wartość nie wystę­puje w polu cert# żadnej istniejącej krotki relacji FilmDyr. Modyfi­kacja zostaje odrzucona i krotka pozostaje bez zmian.

3. Próbujemy usunąć krotkę z relacji FilmDyr, ale wartość w polu cert# występuje również w polu prezc# pewnej krotki relacji stu­dio. Ta próba również nie powiedzie się i krotka pozostanie w relacji FilmDyr.

4. Próbujemy zmienić wartość atrybutu cert# w relacji FilmDyr, ale stara wartość cert# występuje w polu prezC# relacji Studio. Tak samo jak poprzednio próba nie powiedzie się, a krotka pozostanie ta­ka, jaka była.

386 6 WIĘZY I WYZWALACZE W JĘZYKU SQL

Procedura kaskadowa

Obok standardowej procedury istnieje również inny sposób postępowa­nia w przypadku wprowadzania zmian (typu 3 i 4, które opisano powyżej) powiązanych ze sobą relacji, takich jak studio i FilmDyr; jest to procedura kaskadowa. Polega ona na tym, że w przypadku usuwania krotki opisującej prezesa studia w relacji FilmDyr, system automatycznie usuwa powiązane krotki z relacji studio, co zapewnia ochronę więzów integralności. Analo­gicznie postępuje się w przypadkach modyfikacji. Jeśli zmieniamy wartość atrybutu cert# z cI na cZ, a wartość cI występuje w którejś krotce relacji studio w polu prezC#, to nowa wartość c2 zostanie wpisana również za­miast cI w polu prezC#.

Procedura wstawiania wartości NULL

Inny sposób rozwiązania rozważanego problemu polega na przypisaniu atrybutowi prezC# wartości NULL w powiązanej krotce relacji studio. Ta­kie postępowanie nazywa się wstaw-null (set-hull).

Tę opcję można określić oddzielnie dla usuwania i zmian, przyjmując właściwy sposób deklarowania klucza obcego. Używamy w tym celu słów kluczowych ON DELETE lub ON UPDATE, po których wybieramy opcję SET NULL Iub CASCADE.

PRZYKŁAD 6.4

Spróbujmy zmienić deklarację relacji

Studio (nazwa, adres, prezC#)

z przykładu 6.3, tak żeby usunięcia i modyfikacje krotek relacji:

FilmDyr(nazwisko, adres, cert#, cenaSieci)

były obsługiwane we wkaściwy sposób. Na rysunku 6.3 pierwsza z instrukcji CREATE TABLE została rozszerzona o klauzule ON DELETE i ON UPDATE. W wierszu 5) określa się, że po usunięciu krotki z relacji FilmDyr atrybutowi

1) CREATE TABLE Studio

2) nazwa CHAR(30)PRIMARY KEY, 3) adres VARCHAR(255),

4) prezC# INT REFERENCES FilmDyr(cert#), 5) ON DELETE SET NULL,

6) ON UPDATE CASACADE );

RYSUNEK 6.3

Wybór sposobu przestrzegania integralności referencyjnej

6.2. INTEGRALNOŚĆ REFERENCYJNA I KLUCZE OBCE 3Ó%

prezC# we wszystkich odpowiadających krotkach relacji studio zostanie nadana wartość NuLL. Z kolei w wierszu 6) ustalono, że przy modyfikacjach wartości atrybutu cert# w relacji FilmDyr odpowiadające wartości atrybutu prezC# w krotkach relacji studio zostanąteż w ten sam sposób zmienione.

Wyraźnie widać, że jest rozsądnie stosować strategię wstaw-pull przy operacjach usuwania, a strategię kaskadową podczas modyfikowania warto­ści. Co prawda można by oczekiwać, że jeśli na przykład prezes studia przej­dzie na emeryturę, to przez pewien czas studio nie będzie miało prezesa, czyli w bazie będzie występować wartość „null". Jednakże zmiana certyfikatu, która może też trwać jakiś czas, nie może mieć takiego następstwa w bazie. Przecież prezes istnieje i w dalszym ciągu jest prezesem studia, a więc należy we wła­ściwym momencie zmienić wartość atrybutu prezC# w relacji Studio.

0

6.2.3. Ćwiczenia do podrozdzia~u 6.2

Ćwiczenie 6.2.1. Dla filmowej bazy danych o następującym schemacie:

Film (tytuł, rok, długość, czyKolor, nazwaStudia, produ­centC#)

GwiazdyW (tytułFilmu, rokFilmu, nazwiskoGwiazdy) GwiazdaFilmowa(nazwisko, adres, płeć, dataUrodzenie) FilmDyr(nazwisko, adres, cert#, cenaSieci)

Studio (nazwisko, adres prezC#)

należy zadeklarować następujące więzy integralności referencyjnej:

a) Producent filmu musi występować w relacji FilmDyr. Wszystkie modyfika­cje relacji FilmDyr, które powodują naruszenie tego ograniczenia nie będą wykonywane.

b) Ograniczenie jest takie same jak w punkcie a). Jednakże tym razem modyfi­kacja powoduje wstawienie w relacji Film wartości NuLL dla atrybutu pro­ducentC#.

c) Więzy są takie jak w punkcie a), ale w przypadku ich naruszenia krotka, któ­rej to naruszenia dotyczy, ma zostać usunięta z relacji Film.

d) Każdy film który występuje w relacji Gwiazdyw musi być zapisany również w relacji Film. Obsługa naruszenia polega na tym, że polecenie które powo­duje naruszenia nie zostaje wykonane.

e) Gwiazda, która należy do relacji Gwiazdyw także musi być elementem relacji Gwiazda. Modyfikowane krotki w przypadku naruszenia tego warunku mają zostać usunięte.

*!Ćwiczenie 6.2.2. Należy zadeklarować więzy, które polegają na tym, że każdy film zapisany w relacji Film, musi być powiązany z co najmniej jedną gwiazdą w relacji Gwiazdyw. Czy można to osiągnąć przez określenie klucza obcego? Dlaczego tak albo dlaczego nie?

3ÓÓ 6. WIĘZY 1 WYZWALACZE W JĘZYKU SQL

Krotki wiszące oraz strategie modyfikacji

Krotkę z wartością klucza obcego, którego nie ma już w macierzystej relacji, nazywamy krotkc~ wiszc~cc~. Krotkę, która nie spełnia warunku złączenia, także nazywamy krotką wiszącą. Te dwa przypadki są sobie bardzo bliskie. Zauważmy bowiem, że jeśli nie ma krotki definiującej klucz, to krotka za­wierająca tę wartość jako klucz obcy nie może uczestniczyć w złączeniu.

Krotki wiszące w oczywisty sposób naruszająwięzy klucza obcego.

• Standardowa strategia usuwania i modyfikacji polega na tym, że polecenie nie zostaje wykonane wtedy i tyllco wtedy, gdy jego wykonanie spowodowaloby powstanie jednej lub kilku krotek wiszących.

• Strategia kaskadowa w tym przypadku polega na usuwaniu lub modyfikacji wszystkich powstających krotek wiszących (w za­leżności od typu polecenia, a więc w zależności od tego czy jest to polecenie usunięcia, czy modyfikacji).

• Strategia wstaw-null polega tu na wstawieniu w krotkach wiszą cydr zamiast klucza obcego wartości NULL.

Ćwiczenie 6.2.3. Dla bazy danych okrętów wojennych o podanym niżej schemacie:

Klasy (klasa, typ, kraj, liczbaDział, działo, wyporność) Okręt (nazwa, klasa, wodowanie)

Bitwa (nazwa, data)

Rezultat (okręt, bitwa, wynik)

Należy zdefiniować następujące więzy integralności referencyjnej. Należy przyjąć naturalne zalożenia na temat kluczy, a naruszenie warunku obshzgiwać przez wsta­wienie wartości NuLL.

*a) Każda klasa, która zostala zapisana w relacji okręty musi występować w relacji Klasy.

b) Jeśli bitwa występuje w relacji Rezultat, to musi być zapisana w relacji Bitwa.

c) Każdy okręt wymieniony w relacji Rezultat musi być zapisany w relacji Okręty.

6.3. Więzy wartości atrybutów

Do tej pory mieliśmy do czynienia z więzami klucza, które polegają na jednoznaczności pewnych atrybutów w poszczególnych krotkach relacji, oraz z więzami klucza obcego, które powodują wymuszenie więzów integralności

6.3. WIĘZY WARTOŚCI ATRYBUTÓW 389

między atrybutami dwóch relacji. Teraz zajmiemy się jeszcze innymi ważny­mi więzami, Tymi które mogą być określone dla skladowych atrybutów. Takie więzy można określić albo:

1) w definicji schematu relacji jako warunek nałożony na atrybut, albo jako

2) ograniczenie dziedziny, która zostaje następnie określona jako dzie­dzina atrybutu.

W punkcie 6.3.1 zostaną wprowadzone proste więzy wartości atrybutów: więzy polegające na tym, że atrybut nie może przyjmować wartości NULL. Następnie, w p. 6.3.2, przedstawimy podstawowe więzy tego typu, a miano­wicie więzy typu cHECK. Natomiast więzy ograniczające dziedzinę zostaną przedstawione w p. 6.3.3. Poza tym istnieją jeszcze inne rodzaje więzów, będzie o nich mowa w podrozdziale 6.4. Właśnie one dużą do rozszerzenia ograniczeń na całą krotkę, relację, a nawet na kilka relacji, ale mogą być użyte także do ograniczania wartości pojedynczego atrybutu.

6.3.1. Więzy NOT-NULL

Najprostsze więzy nakładane na atrybuty nazywają się NOT NULL. W ich wyniku w żadnej krotce w miejscu ograniczonego atrybutu nie może pojawić się wartość NULL. Deklaracja tych więzów występuje w instrukcji CREATE TABLE, w której po słowie kluczowym NOT NULL podaje się listę atrybutów.

PRZYKŁAD 6.5

Załóżmy, że atrybut prezC# w relacji studio nie może mieć wartości NULL. Można takie więzy zdefiniować zmieniając na rys. 6.3 wiersz 4), który przyj­mie postać następującą:

4) prezC# INT REFERENCES FilmDyr(cert#) NOT NULL

Taka zmiana powoduje szereg następstw:

• Wartość składowej prezc# w żadnej krotce nie może być zmieniona na NULL.

• Do relacji studio nie można wstawić krotki zawierającej tylko war­tości atrybutów nazwisko i adres, ponieważ wówczas w skladowej prezC# musiałaby wystąpić wartość NULL.

• W sytuacji takiej jak w wierszu 5) na rys. 6.3 nie można stosować strategii NoT NULL, która powoduje, że przy naruszeniu więzów klu­cza obcego atrybut prezc# powinien mieć nadanąwartość NULL.

390

6. WIĘZY I WYZWALACZE W JĘZYKU SQL

6.3.2. Więzy CHECK

W deklaracji atrybutu można określić bardziej skomplikowane więzy, umieszczając tam słowo kluczowe CHECK, po którym podaje się w nawiasach warunek, który muszą spełniać wszystkie wartości tego atrybutu we wszyst­kich krotkach. W praktyce więzy typu cHECK nakładają bardzo proste warun­ki na wartości atrybutu, na przykład zawierają listę poprawnych lub niepo­prawnych wartości. Jednakże warunek może być dowolny, jeśli tylko jego postać jest dopuszczalna w klauzuli WHERE zapytania SQL. W warunku moż­na używać nazwy atrybutu, którego wartości mają być ograniczane przez ten warunek. Jednakże, jeśli występują w nim inne relacje lub atrybuty relacji, to te relacje muszą być wymienione w klauzuli FROM podzapytania (nawet jeśli ograniczany atrybut należy do tej relacji).

Więzy typu CHECK są sprawdzane zawsze wtedy, kiedy jest określana nowa wartość ograniczanego atrybutu. Nowa wartość może być nadawana w wyniku modyfikacji krotki lub w przypadku dołączania nowej krotki. Jeśli nowa wartość nie spełnia warunku więzów, to polecenie nie zostaje wykona­ne. W przykładzie 6.7 przekonamy się, że jeśli modyfikacja bazy danych nie dotyczy atrybutu, dla którego utworzono więzy CHECK, to te więzy nie są sprawdzane i może to spowodować ich naruszenie. Na razie jednak zajmijmy się prostym przykładem więzów CHECK.

PRZYKŁAD 6.6

Nasze więzy polegają na tym, że numery certyfikatów muszą składać się co najmniej z sześciu cyfr. Deklaracja schematu relacji

Studio (nazwisko, adres, prezC#)

z wiersza 4) na rys. 6.3 zostanie wówczas zapisana w następującej postaci:

4) prezC# INT REFERENCES FilmDyr(cert#)

CHECK (prezC# >= 100000)

Z kolei rozpatrzmy inny przykład: typ atrybutu płeć w relacji

GwiazdaFilmowa(nazwisko, adres, płeć, dataUrodzenia)

został na rys. 5.13 określony jako cHAR ( I ) , tzn. jako jeden znak. Ale ograni­czenie jest jeszcze mocniejsze, bowiem ten znak może być tylko albo znakiem `K' albo znakiem `M' . Aby nalożyć właśnie takie więzy należy w wierszu 4) na rys. 5.13 umieścić następującą deklarację CHECK:

4) płeć CHAR(1) CHECK (płeć IN (`K', `M')),

6.3. WIĘZY WARTOŚCI ATRYBUTÓW 391

W tym warunku występuje jawnie relacja złożona z dwóch wartości `K~ oraz `M', a powoduje on, że wartości atrybutu pleć musząnależeć do tego zbioru.

0

W warunku CHECK mogą występować nazwy dowolnych relacji lub atry­butów, ale wówczas w tym warunku musi być zdefiniowane podzapytanie, w którym te nazwy są wymienione. Wspominaliśmy już, że warunek może być dowolny, a jego postać jest określona podobnie jak postać warunków klauzuli WHERE instrukcji SELECT-EROM-WHERE. Jednakże trzeba pamiętać, że warunek zasadniczo dotyczy atrybutu, który jest definiowany, a nie wszystkich relacji i atrybutów występujących w warunku. Dlatego, jeśli jakiś element występujący w warunku, różny od definiowanego atrybutu, ulegnie modyfikacji, to warunek może przestać być spełniony.

PRZYKŁAD 6.7

Czy daje się wyrazić więzy integralności referencyjnej za pomocą więzów typu CHECK, narzucających warunek obligatoryjnego istnienia wartości po­wiązanej?. Przedstawiamy próbę określenia wymogu, aby wartość prezC# z pewnej krotki relacji

Studio (nazwisko, adres, prezC#)

występowała w składowej cert# jakiejś krotki w relacji

FilmDyr(nazwisko, adres, cert#, cenaSieci)

Sprawdźmy, jaki efekt spowoduje następująca modyfikacja w wierszu 4) na rys. 6.3:

4) prezC# INT CHECK

(prezC# IN (SELECT cert# EROM FilmDyr))

Powyższa deklaracja jest prawidłowo zapisanym warunkiem typu CHECK, a w jego konsekwencji wystąpią następujące sytuacje:

• Próba wstawienia nowej krotki do relacji studio, w której wartość atrybutu prezC# nie jest numerem certyfikatu żadnego prezesa, nie powiedzie się.

• Próba zmodyfikowania wartości atrybutu prezC# relacji studio, w której nowa wartość atrybutu prezC# nie występuje jako wartość atrybutu cert# w relacji FilmDyr, nie powiedzie się.

• Jednakże, jeśli ulegnie zmianie relacja FilmDyr, np. w ten sposób, że zostanie z niej usunięta jakaś krotka opisująca pewnego prezesa, to pozostanie ona niezauważona przez zadeklarowane więzy cHECK.

392 6. WIĘZY 1 WYZWALACZE W JĘZYKU SQL

A więc usunięcie zosta~~ie wykonane, nawet jeśli w jego wyniku wię­zy CHECK zostaną naruszone.

W punkcie 6.4.2 opiszemy, w jaki sposób można poprawnie wyrazić stawiane warunki.

0

6.3.3. Więzy dziedziny

Inny sposób narzucenia ograniczeń na wartości atrybutu polega na zade­klarowaniu tych ograniczeń dla dziedziny atrybutu (o dziedzinach była już mowa w p. 5.7.6), a następnie przypisaniu tej dziedziny typowi atrybutu. Różnica ujawnia się tylko wówczas, gdy ograniczenie dziedziny polega na wypisaniu dopuszczalnych wartości, ale nie ma się jak do nich odwoływać. Dla porównania: jeśli więzy są przypisane bezpośrednio do atrybutu, to wów­czas możemy dowiedzieć się, jaka jest wartość poprzez atrybut, któremu ją przypisano. W SQL2 ten problem rozwiązano, wprowadzając specjalne słowo kluczowe VALUE, dzięki któremu można odwoływać się do wartości przypi­sanych do dziedziny.

PRZYKŁAD 6.8

Następująca deklaracja może posłużyć do określenia, że w dziedzinie Dzie­dzinaPłci wartościami mogąbyć tylko dwa pojedyncze znaki ' K' oraz ' M'

CREATE DOMAIN DziedzinaPłci CHAR(1) CHECK (VALUE IN ('K', 'M'));

Po zapisaniu takiej deklaracji wiersz 4) z rys. 5.13 może przybrać następującą postać:

4) płeć DziedzinaPłci,

Analogiczny efekt uzyskuje się dla przykładu 6.6, gdzie jest stawiany wymóg sześciocyfrowego numeru certyfikatu dla atrybutu prezC#, jeśli utworzy się dziedzinę:

CREATE DOMAIN DziedzinaCert INT CHECK (VALUE >= 100000);

a z kolei deklaracja atrybutu prezc# zostanie zapisana następująco:

4) prezC# DziedzinaCert REFERENCES FilmDyr (cert#)

0

6.3. WIĘZY WARTOŚCI ATRYBUTÓW 393

Kiedy należy sprawdzać więzy

W normalnych okolicznościach system SQL nie dopuszcza do takich mody­fikacji danych w bazie danych, które prowadzą do naruszenia więzów. Może się jednak zdarzyć, że trzeba wykonać działania na danych i któreś z nich prowadzi do naruszenia więzów, ale kolejne poprawia ten stan. Rozważmy przykład 6.3, w którym ustaliliśmy, że atrybut prezc# jest kluczem obcym w relacji studio, dziedziczonym z atrybutu cert# z relacji FilmDyr. Jeśli do bazy zamierzamy dołączyć nowe studio oraz jego prezesa, to rozpoczy­nając od wpisywania studia, zostaną naruszone więzy klucza obcego.

Wydaje się, że sytuację można uzdrowić, jeśli w pierwszej kolejności wprowadzi się dane prezesa do relacji FilmDyr. Ale jeśli dołączymy wy­maganie, żeby prezes, identyfikowany w relacji FilmDyr poprzez cert#, miał wpisany ten numer certyfikatu w relacji film albo Studio, to już żadna kolejność nie będzie właściwa.

Na szczęście w SQL2 można określać więzy typu DEFERRED. Takie więzy są sprawdzane dopiero po zakoiiczeniu wykonywania całej „trans­akcji" (podstawowej jednostki działania na bazach danych - transakcje opisano w podrozdziale 7.2). Dzięki temu studio i jego prezydenta może­my dopisać do bazy danych, nie powodując przy tym naruszenia więzów.

6.3.4. Ćwiczenia do podrozdziaku 6.3

Ćwiczenie 6.3.1. Dla relacji:

Film(tytuł, rok, długość, czyKolor, nazwaStudia, producentC#)

Zadeklarować następujące więzy:

*a) Rok nie może być wcześniejszy niż 1895.

b) Długość nie może wynosić mniej niż 60 ani więcej niż 250. *c) Nazwą studia musi być Disney, Fox, MGM albo Paramount.

Ćwiczenie 6.3.2. Dla przykładowego schematu bazy danych z ćwiczenia 4.1.l

Produkt (producent, model, typ)

PC (model, szybkość, ram, hd, cd, cena)

Laptop (model, szybkość, ram, hd, ekran, cena) Drukarka (model, kolor, typ, cena)

Należy zadeklarować następujące więzy atrybutów:

a) Częstotliwość zegara laptopa nie może być mniejsza niż 100. b) Szybkość CD może być równa wyłącznie: 4x, 6x, 8x lub 12x.

394 6, WIĘZY I WYZWALACZE W JĘZYKU SQL

c) Drukarki można określić wykącznie jako: laserowa, atramentowa lub igłowa. d) Produktami są wyłącznie PC, laptopy i drukarki.

e) Pojemność pamięci operacyjnej musi stanowić co najmniej 1% objętości dysku twardego.

6.4. Więzy globalne

Teraz zajmiemy się deklarowaniem bardziej złożonych więzów, które dotyczą właściwości związków zachodzących między atrybutami lub relacja­mi. Ten temat obejmuje dwie części:

1. Krotkowe więzy CHECK nakładają ograniczenia na krotki pojedyn­czych relacji.

2. Asercje są to więzy nakładane jednocześnie na kilka relacji lub na zmienne krotkowe z określonej jednej relacji.

6.4.1. Krotkowe więzy CHECK

Aby zadeklarować więzy krotkowe dla pojedynczej relacji R należy do definicji tabeli w instrukcji CREATE TABLE dołączyć do listy atrybutów, klu­czy i kluczy obcych jeszcze słowo kluczowe CHECK, a po nim warunek ujęty w nawiasy. Postać wyrażenia opisującego warunek jest taka sama jak wyrażeń dopuszczalnych w klauzuli WHERE. Warunek dotyczy każdej pojedynczej krotki w relacji R. I tak samo jak w przypadku więzów atrybutów, w warunku może wystąpić podzapytanie, w którym są odwołania do innych relacji lub innych krotek tej samej relacji R.

i Warunek opisany w krotkowych więzach CHECK jest sprawdzany zawsze przy wstawianiu i modyfikowaniu krotki relacji R. Jest on sprawdzany dla nowych wartości wstawianych do relacji poprzez polecenia INSERT lub

I,r UPDATE. Jeśli w takiej sytuacji warunek jest fałszywy dla danej krotki, to więzy zostają naruszone i polecenie nie zostanie wykonane. Jednak, jeśli 'i, w podzapytaniu warunku występuje R lub jeszcze inne relacje, których zmia­

na spowoduje, że warunek stanie się fałszywy dla pewnej krotki R, to więzy nie zostaną naruszone i polecenie powiedzie się. A więc podobnie jak w przy­padku więzów CHECK dla atrybutów, które były omawiane w podrozdzia­~I, le 6.7, krotkowe więzy CHECK nie są widoczne z poziomu innych relacji.

Mimo że w krotkowych więzach CHECK można zapisywać dowolnie zło­żone warunki sprawdzeń, to jest lepiej w takich przypadkach stosować aser­cje, które omówimy w p. 6.4.2. Wynika to z opisanego wcześniej efektu ubocznego, polegającego na możliwości niezauważalnego naruszenia tego typu więzów. Ale jeśli w podzapytaniu więzów krotkowych występują wy­

6.4. WIĘZY GLOBALNE 39S

łącznie atrybuty z krotki i nie ma tam podzapytań, to więzów nie da się naru­szyć w sposób uboczny. Poniżej został przedstawiony prosty przykład krot­kowych więzów CHECK, które dotycząwielu atrybutów.

PRZYKŁAD 6.9

W przykładzie 5.32 został określony schemat tabeli GwiazdaFilmowa. Na rysunku 6.4 została zapisana instrukcja CREATE TABLE, która zawiera defini­cję kluczy (poprzez słowo kluczowe UNIQUE) oraz więzy nadzorujące jeden z możliwych warunków spójności logicznej danych. Polega on na tym, że jeśli gwiazda jest mężczyzną, to jego nazwiska nie może poprzedzać słowo ' Pani' .

1) CREATE TABLE GwiazdaFilmowa 2) nazwisko CHAR(30) UNIQUE, 3) adres VARCHAR(255),

4) płeć CHAR(1),

5) dataUrodzenia DATE,

6) CHECK (płeć = `F' OR nazwisko NOT LIKE `Pani%')

); RYSUNEK 6.4

Więzy w tabeli GwiazdaFilmowa

W wierszu 2) opisano atrybut nazwisko jako klucz relacji. W wierszu 6) z kolei są zapisane więzy spójności. Warunek sprawdzenia tych więzów jest spełniony, jeśli GwiazdaFilmowa jest kobietą lub jeśli nazwisko gwiazdy filmowej nie poprzedza słowo Pani. Jedynie te krotki, w których atrybut płeć opisuje mężczyznę, a równocześnie nazwisko jest poprzedzone słowem Pani nie są akceptowane. I takich krotek nie zamierzamy zapisywać w relacji GwiazdaFilmowa.

0

6.4.2. Asercje

Dotychczas prowadziliśmy rozważania na temat rozmaitych rodzajów więzów: od więzów atrybutów do więzów definiowanych dla krotek. Ale i takie postacie więzów bywają nieefektywne. Czasami trzeba, aby więzy dotyczyły relacji jako całości, na przykład określały ograniczenie na pewien agregat, powiedzmy sumę wartości w kolumnie. Można także mieć pożytek z więzów obejmujących więcej niż jedną relację. Takim przykładem są więzy klucza obcego, które zawsze dotyczą dwóch relacji, jednak mają one ograni­czone stosowanie i postać.

Do określenia dowolnego warunku (wyrażenia, które może występować w klauzuli wHERE) służą w SQL2 asercje, nazywane inaczej „więzami głów­

396 6. WIĘZY I WYZWALACZE W JĘZYKU SQL

nymi". Podczas gdy pozostałe typy więzów są tylko określane dla pewnych elementów schematu, najczęściej dla tabel lub dziedzin, to asercje stanowią niezależne elementy schematu.

Poprawny zapis więzów

Wiele więzów przypomina te z przykładu 6.9, które polegają na zabronie­niu wstawiania krotek spełniających pewne warunki. Po słowie kluczo­wym CHECK występuje zazwyczaj alternatywa negacji warunków lub wa­runków przeciwnych. W przykładzie 6.9 pierwszy warunek określał, że gwiazda jest mężczyzną, a korzystaliśmy z warunku przeciwnego płeć = ' K' (chociaż być może warunek płeć < > ' M' byłby tu stosowniejszy w charakterze negacji). Drugi warunek określa, że nazwisko nie może zaczynać się od Pani, tutaj określiliśmy negację za pomocą porównania NOT LIKE. To porównanie zawiera negację, sam warunek zostałby bo­wiem zapisany w SQL jako nazwisko LIKE ' Ms . ~' .

Tak jak inne elementy schematu asercje są definiowane za pomocą in­strukcji CREATE. Postać tej instrukcji składa się z następujących części:

1) słowa kluczowego CREATE ASSERTION, 2) nazwy asercji,

3) skowa kluczowego CHECK, 4) warunku ujętego w nawiasy.

A więc postać definicji asercji można ująć w następujący schemat:

CREATE ASSERTION <nazwa> CHECK (<warunek>)

Warunek zapisany w asercji musi być zawsze spełniony i każda modyfi­kacja, która zaburza ten stan, nie jest wykonywana. Przypomnijmy, że inne więzy CHECK, jeśli tylko zawierały podzapytania, mogły być naruszane w pewnych szczególnych okolicznościach.

W inny sposób zapisuje się krotkowe więzy CHECK, a inaczej asercje. W definicji więzów krotkowych występują nazwy atrybutów relacji, której te więzy dotyczą. Na przykład w wierszu 6) z rys. 6.4 używaliśmy atrybutów płeć oraz nazwisko, nie wskazując skąd one pochodzą. Wiadomo jednak, że odnosiły się one do krotek tabeli GwiazclaFilmowa dzięki temu, że tylko ta tabela była deklarowana w omawianej instrukcji CREATE.

Warunek występujący w asercji nie ma takich zalet. Każdy atrybut, który występuje w warunku, musi występować w asercji, zazwyczaj zaznaczenie tej okoliczności następuje przez określenie relacji, z której pochodzi ten atrybut, w klauzuli select-from-where. Ponieważ z natury rzeczy warunkowi od­powiada wartość typu logicznego, więc dotyczy to także warunku występują­

6.4. WIĘZY GLOBALNE 397

cego w asercji. Na przykład warunek może obejmować wyrażenie definiujące relację, do którego dopisany jest operator NOT EXISTS; takie więzy określa­ją, że relacja jest pusta. Innym przykładem może być określenie agregatu sumowania dla pewnej kolumny w tabeli, a następnie w warunku porównanie tej wartości z określoną stałą. W ten sposób można by wymusić, aby taka suma zawsze była większa od pewnej wartości zadanej z góry.

Ograniczenia w sprawdzaniu więzów: błąd czy właściwość? Zastanowienie może budzić fakt, że dopuszcza się takie sytuacje, w któ­rych więzy atrybutów lub krotkowe są naruszane, jeśli odnoszą się do in­nej relacji lub do innych krotek. Powodem tego jest potrzeba zapewnienia efektywności implementacji, która w takich przypadkach okazuje się znacznie większa niż w przypadku asercji. Więzy atrybutów i krotkowe są sprawdzane tylko przy wstawianiu i modyfikacji określonych krotek. Na­tomiast warunki z asercji są sprawdzane przy każdej próbie modyfikacji relacji, dla której asercja została określona. O tym, jaki rodzaj więzów za­stosować w konkretnym przypadku, decyduje projektant bazy, analizując, czy warto w celach bezpieczeństwa dodatkowo przeglądać dane w bazie. Ze względu na zachowanie długoterminowej niezawodności kodu zaleca się, aby projektant nie stosował więzów krotkowych ani atrybutowych, ponieważ mogą się one okazać nieskuteczne.

PRZYKŁAD 6.10

Załóżmy, że nikt nie może zostać prezesem studia, o ile jego sieć jest warta mniej niż 10 000 000 $. Warunek asercji określimy w ten sposób, że będzie spełniony, jeśli zbiór studiów filmowych, których prezesi mają sieci warte mniej niż 10 000 000 $, jest pusty. Asercja dotyczy dwóch relacji:

FilmDyr(nazwisko, adres, cert#, cenaSieci) Studio (nazwa, adres, prezC#)

Asercję przedstawiono na rys. 6.5.

CREATE ASSERTION BogatyPrez CHECK (NOT EXISTS

(SELECT

EROM Studio, FilmDyr WHERE prezC# = cert# AND

cenaSieci < 10000000

); RYSUNEK 6.5

Asercja zapewniąjąca, że prezesi studiów są bogaci

39ó 6. WIĘZY I WYZWALACZE W JĘZYKU SQL

Mimo że asercja obejmuje dwie relacje, to można takie więzy wyrazić również za pomocą więzów krotkowych typu CHECK, zdefiniowanych osobno dla poszczególnych relacji. Możemy na przykład do definicji CREATE TABLE z przykładu 6.3 dołączyć więzy relacji studio, tak jak pokazano na rys. 6.6.

1) CREATE TABLE Studio

2) nazwa CHAR(30) PRIMARY KEY, 3) adres VARCHAR(255),

4) prezC# INT REFERENCES FilmDyr(cert#), 5) CHECK (prezC# NOT IN

6) (SELECT cert# EROM FilmDyr 7) WHERE cenaSieci < 10000000) );

RYSUNEK 6.6

Więzy w relacji studio, równoważne asercji

Zauważmy jednak, że więzy przedstawione na rys. 6.6 będą sprawdzane jedynie wtedy, kiedy zajdzie zmiana dotycząca relacji studio. Wobec tego nie zostanie na przykład wykryta sytuacja polegająca na tym, że wartość sieci pewnego prezesa zapisana w relacji FilmDyr spada poniżej 10 000 000 $. Aby uzyskać kompletny efekt działania asercji, trzeba by dołączyć do dekla­racji tabeli FilmDyr jeszcze jedne więzy, które sprawdzają, czy jeśli dyrektor jest prezesem studia, to wartość jego sieci nie jest niższa niż 10 000 000 $.

0

PRZYKŁAD 6.11

A oto jeszcze jeden przykład asercji. Dotyczy ona wyłącznie relacji

Film (tytuł, rok, długość, czyKolor, nazwaStudia, producentC#)

i określa, że całkowita długość wszystkich filmów w danym studiu nie prze­kracza 10 000 minut.

CREATE ASSERTION SumDługość CHECK (10000>=ALL (SELECT SUM (długość)FROM Movie GROUP BY nazwaStudia);

Tak się składa, że więzy dotyczą tylko relacji Film. Można wyrazić je także za pomocą więzów krotkowych typu CHECK w schemacie relacji Film. Definicja tych więzów polegałaby na dołączeniu do schematu relacji Film następującej deklaracji:

6.4. WIĘZY GLOBALNE

CHECK (10000 >= ALL

(SELECT SUM (długość) EROM Film GROUP BY nazwaStudia));

399

Zauważmy, że w zasadzie ten warunek stosuje się do każdej krotki relacji Film. Jednakże nie wspomina się tu o żadnym atrybucie i przetwarzanie jest skupione w podzapytaniu.

Porównanie Więzów

W tabeli przedstawionej poniżej zilustrowano różnice pomiędzy wię-

zami atrybutów, więzami krotkowymi i asercjami.

Typ więzów Gdzie są deklarowane Kiedy uruchamiane Czy zachodzd

Atrybutowe Z atrybutami Przy wstawianiu do Nie w podza-

CHECK relacji lub zmianie pytaniach

wartości atr butu

Krotkowe Element schematu Przy wstawianiu do Nie w podza-

cHECK relacji relacji lub zmianie pytaniach

wartości w krotce

Asercje Element schematu Przy modyfikacji Tak

bazy danych w relacji

Zauważmy, że w przypadku deklarowania więzów krotkowych spraw­dzenie nie wykona się przy usuwaniu krotek z relacji Film. W omawianym przykkadzie nie ma to akurat znaczenia, ponieważ, jeśli więzy są tutaj speł­nione przed usuwaniem, pozostaną one spełnione tym bardziej po usunięciu krotki. Jednakże, jeśli więzy ograniczałyby długość wyprodukowanych fil­mów z dołu, a nie z góry, to więzy CHECK byłyby przez usuwanie naruszane, a asercja nie.

0

6.4.3. Ćwiczenia do podrozdziału 6.4

Ćwiczenie 6.4.1. W podrozdziale 6.10 wspominaliśmy, że krotkowe więzy CHECK z rys. 6.6 zapewniają wykonanie tylko połowy tego co asercja z rys. 6.5. Należy napi­sać więzy typu cHECK dla relacji FilmDyr, które będąrównoważne tej asercji.

Ćwiczenie 6.4.2. Dla przykładu filmowego, który obejmuje relacje:

Film (tytuł, rok, długość, czyKolor, nazwaStudia, producentC#)

400

GwiazdyW(tytułFilmu, rokFilmu, GwiazdaFilmowa(nazwisko, adres, FilmDyr(nazwisko, adres, cert#, Studio (nazwisko, adres, prezC#)

6. WIĘZY I WYZWALACZE W JĘZYKU SQL

nazwiskoGwiazdy) płeć, dataUrodzenie) cenaSieci)

należy zadeklarować więzy krotkowe typu GHECK, które obejmujątylko jednąrelację. Jeśli więzy dotyczą więcej niż jednej relacji, to muszą one być zdefiniowane w obu relacjach i wykonywać się przy wstawieniach i zmianach wartości. Zakładamy, że krotki nie będą usuwane, poniewaź nie da się kontrolować więzów krotkowych dla operacji usuwania.

*a) Żaden film wyprodukowany przed 1939 r. nie jest kolorowy.

b) Żadna gwiazda nie występowała w filmach wyprodukowanych w latach przed datą jej urodzenia.

!c) Żadne dwa studia nie majątego samego adresu.

*!d)Nazwisko, które występuje w relacji GwiazdaFilmowa, występuje także w relacji FilmDyr.

!e) Nazwa studia, która jest w relacji studio, występuje także w co najmniej jednej krotce relacji Film.

!!f) Jeśli producent pewnego filmu jest prezesem studia, to musi to być studio produkujące ten film.

Ćwiczenie 6.4.3. Należy przedstawić w postaci asercji wymagania sformułowane dla schematu bazy danych z ćwiczenia 4.1.1:

Produkt (producent, model, typ)

PC (model, szybkość, ram, hd, cd, cena)

Laptop (model, szybkość, ram, hd, ekran, cena) Drukarka (model, kolor, typ, cena)

*a) Żaden z producentów PC nie produkuje laptopów.

*!b) Każdy producent PC produkuje również laptopy o co najmniej takiej samej częstotliwości zegara.

!c) Jeśli laptop ma większą pamięć operacyjną, to ma także wyższą cenę.

!!d) Żaden numer modelu nie może się powtarzać w relacjach Pc, Laptop 1 Drukarka.

!!e) Jeśli w relacji Produkt występuje model i jego typ, to ten model występuje również w odpowiedniej do typu relacji.

Ćwiczenie 6.4.4. Dla schematu bazy PC napisać deklaracje poniższych więzów krot­kowych CHECK.

a) Komputer PC o częstotliwości zegara mniejszej niż 150 nie może być droż­szy niż 1500 $.

b) Laptop z ekranem o przekątnej krótszej niż 11 cali ma co najmniej 1 gigabajt twardego dysku lub kosztuje mniej niż 2000 $.

6.5. MODYFIKOWANIE WIĘZÓW 4O1 Ćwiczenie 6.4.5. Dla schematu bazy z ćwiczenia 4.1.3

Klasy (klasa, typ, kraj, liczbaDział, działo, wyporność) Okręt (nazwa, klasa, wodowanie)

Bitwa (nazwa, data)

Rezultat (okręt, bitwa, wynik)

należy wyrazić przedstawione poniżej warunki w postaci asercji.

a) W żadnej klasie nie występuje więcej niż dwa okręty.

!b) Żaden kraj nie posiada zarówno okrętów liniowych, jak i krążowników li­niowych.

!c) Żaden okręt mający więcej niż 9 dział nie uczestniczył w bitwie, w której zatonął okręt o liczbie dział mniejszej niż 9.

!d) Żaden okręt nie mógł być zwodowany wcześniej niż okręt, który w tej kla­sie dziedziczy nazwę klasy.

!e) W każdej klasie występuje okręt, który ma taką samą nazwę jak klasa.

Ćwiczenie 6.4.6, DIa schematu „Okręty" należy zadeklarować następujące więzy krotkowe typu CHECK.

a) W żadnej klasie nie ma okrętów z działami o wyporności większej niż 16 cali.

b) Jeśli w pewnej klasie okręty mają więcej niż 9 dział, to ich wyporność nie przekracza 14 cali.

!c) Żaden okręt nie uczestniczy w bitwie zanim zostanie zwodowany.

6.5. Modyfikowanie więzów

W każdej chwili można zmienić więzy, tzn. można usunąć, zmienić lub dołączyć nowe więzy. Sposób opisania takich poleceń zależy od tego, czy więzy są określone dla dziedziny, atrybutu, tabeli lub schematu bazy da­nych.

6.5.1. Nadawanie nazw więzom

Aby moźna było usunąć lub zmienić właściwości więzów, muszą one mieć nadaną nazwę. Asercje, które zawsze stanowią element schematu bazy danych, otrzymują nazwę w chwili definiowania, tzn. w poleceniu CREATE AssERTION. Innym więzom też czasami trzeba nadawać nazwy. Aby tak się stało, więzy powinny zostać poprzedzane słowem kluczowym CoNST~1NT oraz nazwą.

4O2 6. WIĘZY I WYZWALACZE W JĘZYKU SQL

PRZYKŁAD 6.12

Można nawet określić nazwę kluczy zarówno głównych, jak i obcych. Na przykład, aby nazwać więzy klucza głównego określone na rys. 6.1, możemy w następujący sposób zapisać treść z wiersza 2):

2) nazwisko CHAR(30) CONSTRAINT NazwiskoToKlucz PRIMARY KEY,

W podobny sposób można zdefiniować więzy atrybutów CHECK, które występują w przykładzie 6.6:

4) płeć CHAR(1) CONSTRAINT NieNijaki

CHECK (płeć IN ('K', 'M')),

Z kolei nazwę dla więzów dziedziny z przykładu 6.8 można określić w następujący sposób:

CREATE DOMAIN DziedzinaCert INT

CONSTRAINT sześćCyfr CHECK (VALUE >= 100000);

W końcu następująca definicja:

6) CONSTRAINT PrawoDoTytułu

CHECK (płeć = 'F' OR nazwisko NOT LIKE 'Ms.\~')

służy do nazwania więzów krotkowych CHECK, określonych poprzednio w wierszu 6) na rys. 6.4.

0

6.5.2. Modyfikowanie więzów określonych dla tabel

Więzy określone dla tabel, dziedzin lub atrybutów można zmieniać za pomocą instrukcji ALTER. Niektóre aspekty stosowania tej instrukcji przed­stawiono już w p. 5.7.4 przy okazji usuwania atrybutów z tabel i dołączania nowych; korzystaliśmy wówczas z instrukcji ALTER TAELE. Z kolei w p. 5.7.6 opisano użycie instrukcji ALTER DOMAIN przy dołączaniu wartości domniemanych.

Tego typu instrukcji można również użyć w przypadku zmian więzów, przy czym zarówno do modyfikowania więzów atrybutów, jak i więzów kro­tek używa się postaci ALTER TAELE. Usunięcia więzów dokonuje się, stosu­jąc słowo kluczowe DROP, po którym podaje się nazwę usuwanych więzów,

6.5. MODYFIKOWANIE WIFZÓW 403

a z kolei dołączenie nowych więzów wyraża się przez słowo kluczowe ADD, po którym definiuje się nowe więzy.

PRZYKŁAD 6.13

Dokączanie i usuwanie prześledzimy na więzach z przykładu 6.12. Najpierw zostaną usunięte więzy klucza głównego nazwisko w relacji GwiazdaFil­mowa:

ALTER TABLE GwiazdaFilmowa DROP CONSTRAINT NazwiskoToKlucz;

Potem usuniemy więzy atrybutu typu CHECK, które służą do ograniczenie zakresu wartości atrybutu płeć:

ALTER TABLE GwiazdaFilmowa DROP CONSTRAINT NieNijaki;

Można także usunąć więzy, które nadają prawo korzystania z tytułu ' Ms . ' wyłącznie kobietom:

ALTER TABLE GwiazdaFilmowa DROP CONSTRAINT PrawoDoTytułu;

Można przywrócić te więzy, dołączając je ponownie do schematu relacji GwiazdaFilmowa.

ALTER TABLE GwiazdaFilmowa ADD CONSTRAINT NazwiskoToKlucz

PRIMARY KEY (nazwisko);

ALTER TABLE GwiazdaFilmowa ADD CONSTRAINT NieNijaki CHECK(płeć IN ('K', 'M'));

ALTER TABLE GwiazdaFilmowa ADD CONSTRAINT PrawoDoTytułu

CHECK (płeć = 'K' OR nazwisko NOT LIKE 'Ms. \~');

Teraz wszystkie więzy są więzami krotkowymi, a nie atrybutowymi. Nie można im przywrócić charakteru więzów atrybutowych, gdyby natomiast to były więzy dziedziny, to można by zmieniać w całej dziedzinie, a nie tylko w relacji GwiazdaFilmowa.

W powyższych definicjach nazwa więzów nie jest parametrem obowiąz­kowym. Jednakże nie można polegać na SQL, że definicje więzów sprzed ich usunięcia są pamiętane i mogą zostać odtworzone. A zatem trzeba podać defi­nicję więzów, a nie tylko ich nazwę.

0

4O4 6. WIĘZY I WYZWALACZE W JĘZYKU SQL

Więzy należy określać nazwami

Pamiętajmy o tym, iż nazywanie więzów jest sensowne, nawet gdy nie przewiduje się, że będzie to kiedykolwiek potrzebne. Jeśli więzy zostaną zdefiniowane bez nazwy, to już nigdy nie uda się ich zmienić i gdyby się okazało, że są na przykład niepotrzebne, wówczas trzeba definiować na nowo schemat.

6.5.3. Zmiany więzów dziedziny

Dołączanie i usuwanie więzów dziedziny polega na tym samym co mo­dyfikowanie więzów krotkowych typu CHECK. Aby usunąć więzy dziedziny, stosuje się instrukcje ALTER i słowo kluczowe DROP, po którym podaje się nazwę więzów. Przy dołączaniu nowych więzów za skowem ALTER wystę­puje słowo kluczowe ADD, nazwa więzów oraz warunek CHECK określający więzy.

PRZYKŁAD 6.14

Więzy dziedziny, które ograniczają numery certyfikatów do liczb sześciocy­frowych, można usunąć w sposób następujący:

ALTER DOMAIN DziedzinaCert DROP CONSTRAINT sześćCyfr;

Przywraca je natomiast następująca instrukcja:

ALTER DOMAIN DziedzinaCert ADD CONSTRAINT sześćCyfr CHECK (VALUE >= 100000);

6.5.4. Modyfikowanie asercji

Asercję można usunąć, stosując instrukcję złożona ze słowa kluczowego DROP ASSERTION, po którym podaje się nazwę asercji.

PRZYKŁAD 6.15

Asercję z przykładu 6.15 można usunąć, podając następujące polecenie:

DROP ASSERTION BogatyPrezes;

Aby przywrócić tę asercję, deklaruje się ją ponownie, tak jak to zrobiono w przykładzie 6.10.

0

6.6. WYZWALACZE W JĘZYKU SQL3 4OS

6.5.5. Ćwiczenia do podrozdzia~u 6.5

Ćwiczenie 6.5.1. Dla przykładu filmowego:

Film (tytuł, rok, długość, czyKolor, nazwaStudia, producentC#)

GwiazdyW (tytułFilmu, rokFilmu, nazwiskoGwiazdy) GwiazdaFilmowa (nazwisko, adres, płeć, dataUrodzenie) FilmDyr(nazwisko, adres, cert#, cenaSieci)

Studio (nazwisko, adres prezC#)

należy pokazać, w jaki sposób do schematu wprowadzić następujące zmiany:

*a) Atrybuty tytuł i rok mają stać się kluczem relacji Film.

b) Nałożyć więzy integralności referencyjnej, które polegają na tym, że każdy producent jest zapisany w relacji FilmDyr.

c) Nałożyć więzy, które polegają na tym, że żaden film nie może trwać krócej niż 60 minut ani dłużej niż 250 minut.

*!d) Narzucić warunek polegający na tym, żeby żadne nazwisko nie byto rów­nocześnie nazwiskiem gwiazdy filmowej i dyrektora produkcji ( te więzy nie powinny być sprawdzane w przypadku usuwania krotek).

!e) Dwa różne studia nie mogąmieć tego samego adresu.

Ćwiczenie 6.5.2. Dla przykladu „okręty":

Klasy (klasa, typ, kraj, liczbaDział, działo, wyporność) Okręt (nazwa, klasa, wodowanie)

Bitwa (nazwa, data)

Rezultat (okręt, bitwa, wynik)

należy określić następujące krotkowe więzy typu cHECK:

a) Klasa i kraj tworząklucz w relacji Klasy.

b) Każdy okręt, który występuje w relacji Bitwa, występuje także w relacji Okręt.

c) Każdy okręt, który występuje w relacji Rezultat, występuje także w rela­cji Okręt.

d) Żaden okręt nie ma więcej niż 14 dział.

!e) Nie dopuszczać okrętów, które uczestniczą w bitwach, zanim zostaną zwo­dowane.

6.6. Wyzwalacze w j ęzyku SQL3

Rozmaite więzy opisane w bieżącym rozdziale są zawarte w standardzie SQL2. Z modelu realizacji tych więzów wynika, że są one wykonywane zaw­sze wtedy, kiedy ma nastąpić zmiana wartości elementu, który jest przez nie

4OC) 6. WIĘZY I WYZWALACZE W JĘZYKU SQL

ograniczany. Na przykład warunki więzów CHECK są sprawdzane zawsze, gdy zmiana dotyczy krotki zawierającej wartość ograniczanego atrybutu (nawet gdy operacja polega na dołączaniu nowej krotki).

Ponieważ implementacja więzów polega na „wyzwoleniu" sprawdzenia warunków na skutek zajścia określonych zdarzeń, więc nasuwa się pytanie, czy to nie programista, zamiast systemu, powinien określać zdarzenie wyzwalane przy sprawdzeniu warunku. Przy takim podejściu użytkownik miałby szersze możliwości wyboru operacji wykonywanych w bazie danych i mógłby je okre­ślać nie tylko w celu ochrony przed naruszeniem ograniczeń. Dlatego w stan­dardzie SQL3 umieszczono również „wyzwalacze", które są pochodną więzów, ale umożliwiają określenie zarówno konkretnych zdarzeń kontekstowych dla warunku więzów, jak i operacji, które mają być przetworzone w określonych sytuacjach. Może wydać się interesujące, że systemy oferowane obecnie na rynku są, w zakresie elementów aktywnych, bliższe standardowi SQL3 niż SQL2. Prawdopodobnie dzieje się tak dlatego, że komercyjnym producentom jest w pewnym sensie latwiej implementować wyzwalacze niż asercje.

6.6.1. Wyzwalacze a więzy

Wyzwalacze, czasami nazywane regułami zda>^zenie-warunek-akcja (ECA rules, czyli event-condition-action), różnią się od omawianych poprzednio więzów na trzy sposoby:

1. Wyzwalacze są testowane tylko przy zajściu określonego przez pro­gramistę zdarzenia. Typy takich zdarzeń obejmują zazwyczaj dolą czapie, modyfikacje lub usuwanie krotek danej relacji. W niektórych implementacjach SQL występuje również inny rodzaj zdarzeń: zakoń­czenie transakcji (w podrozdziale 7.2 omówiono atomowe jednostki przetwarzania, nazywane transakcjami, używane jako niepodzielne operacje w bazie danych).

2. Wyzwalacze testują warunek w chwili zajścia zdarzenia, a nie uprze­dzając je. Jeśli warunek nie jest spelniony, to w odpowiedzi na zda­rzenie nic się nie wykona.

3. Jeśli warunek wyzwalacza zostanie spelniony, to DBMS przetwarza akcję związaną z wyzwalaczem. Akcja może chronić przed zajściem zdarzenia w bazie lub może zmienić wynik zdarzenia (np. usunąć wprowadzoną krotkę). Akcja może polegać na przetworzeniu calego ciągu operacji w bazie danych, nawet takich, które nie mają żadnego związku z wyzwalanym zdarzeniem.

W dalszym ciągu najpierw omówimy wyzwalacze z SQL3. Potem krótko opiszemy rozszerzenia w SQL3 więzów z SQL2, nazywanych asercjami. Właśnie tego typu więzy mają pewne cechy wyzwalaczy.

6.6. WYZWALACZE W JĘZYKU SQL3

407

6.6.2. Wyzwalacze w języku SQL3

Instrukcja wyzwalacza w SQL3 udostępnia użytkownikowi wiele opcji w zakresie każdego elementu: zdarzenia, warunku i akcji. Podstawowe wła­ściwości są następujące:

1. Akcja może być wykonana przed, po i w chwili zajścia zdarzenia.

2. Akcja może korzystać zarówno z wartości sprzed zajścia zdarzenia, jak i z nowych wartości powstałych w wyniku wstawienia, modyfika­cji lub usunięcia krotki w trakcie zdarzenia.

3. Zdarzenia mogą wprowadzać modyfikacje do określonej kolumny lub do zbioru kolumn.

4. Warunek można określać w klauzuli WHEN i akcja jest wykonywana przy wyzwoleniu reguly oraz warunek jest spelniony, gdy zajdze wy­zwalane zdarzenie.

5. Programista może sam określić, czy akcja ma być wykonana: a) zawsze dla każdej modyfikowanej krotki,

b) raz dla wszystkich modyfikowanych krotek w pojedynczej operacji w bazie danych.

Zanim przedstawimy szczególowy opis syntaktyki wyzwalaczy, rozwa­żymy przyklad ilustrujący najważniejsze właściwości wyzwalaczy zarówno składniowe, jak i semantyczne. W tym przykładzie wyzwalacz wykonuje się osobno dla każdej modyfikowanej krotki.

PRZYKŁAD 6.16

Napiszemy wyzwalacz w SQL3, który będzie stosowany dla relacji:

FilmDyr(nazwisko, adres, cert#~ cenaSieci)

Akcja jest wyzwalana przy próbie modyfikacji atrybutu cenasieci. W wy­niku powinna zostać uniemożliwiona każda próba obniżenia ceny sieci preze­sa studia. Deklarację wyzwalacza pokazano na rys. 6.7.

W wierszu 1) zapisano deklarację złożoną ze słowa kluczowego CREATE TRZGGER oraz nazwy wyzwalacza. W drugim wierszu określa się wyzwalane zdarzenie, którym jest modyfikacja wartości atrybutu cenasieci w relacji FilmDyr. W wierszach od 3) do 5) zapisano elementy warunków i akcji wy­zwalacza, które obejmują zarówno stare wartości krotki (krotka przed mody­fikacją), jak i nową krotkę ze zmodyfikowanymi wartościami. Te krotki są identyfikowane poprzez nazwy staraKrotka i NowaKrotka, odpowiednio do deklaracji w wierszach 4) i 5). Można tych nazw używać w warunkach i akcjach tak samo jak nazw zmiennych dla krotek, występujących w zwy­czajnej klauzuli EROM języka SQL.

4OÓ 6. WIĘZY I WYZWALACZE W JĘZYKU SQL

1) CREATE TRIGGER CenaSieciWyzw

2) AFTER UPDATE OF cenaSieci ON FilmDyr 3) REFERENCING

4) OLD AS StaraKrotka, 5) NEW AS NowaKrotka

6) WHEN(StaraKrotka.cenaSieci > NowaKrotka.cenaSieci) 7) UPDATE FilmDyr

8) SET cenaSieci = StaraKrotka.cenaSieci 9) WHERE cert# = NowaKrotka.cert#

10) FOR FACH ROW;

RYSUNEK 6.7 Wyzwalacz SQL3

W wierszu 6) zapisano warunek wyzwalania. Oznacza on, że działania mają być przetworzone przy próbie wprowadzenia niższej ceny sieci, tzn. gdy cena sieci prezesa spada.

Wiersze od 7) do 9) tworzą element działania; są to zwykłe instrukcje modyfikacji w SQL, a mają na celu odtworzenie ceny sieci sprzed modyfika­cji. Trzeba zauważyć, że w zasadzie jest przeglądana każda krotka relacji FilmDyr, ale klauzula WHERE zapewnia, że akcja obejmie tylko modyfiko­wane krotki (te z właściwą wartością atrybutu cert#).

W końcu, w wierszu 10) precyzuje się wymaganie użycia wyzwalacza dla każdej modyfikowanej krotki. Jeśliby go nie było, to wyzwalacz byłby zastosowany tylko raz podczas przetwarzania instrukcji SQL, bez względu na to,jak wiele razy zaszłoby zdarzenie wchodzące w skład wyzwalacza.

0

Oczywiście w przykładzie 6.16 zilustrowano tylko niektóre właściwości wyzwalaczy z SQL3. Poniżej opisujemy inne możliwości wyzwalaczy oraz sposoby ich stosowania.

Z tekstu w wierszu 2) na rys. 6.7 wynika, że akcje są przetwarzane po zajściu zdarzenia wyzwalania, na co wskazuje specyfikacja AFTER. Alternatywę dla AFTER stanowią:

1. BEFORE. Warunek WHEN sprawdza się przed zajściem zdarzenia. Akcje wyzwalacza zostają przetworzone, o ile warunek jest speł­niony. Wówczas zdarzenie, które powoduje modyfikację, zachodzi, bez względu na to, czy warunek jest spełniony, czy nie.

2. INSTEAD OF. Akcja jest wykonywana (przy spełnieniu warunku WHEN), ale zdarzenie nigdy nie następuje.

Poza UPDATE innymi zdarzeniami wyzwalanymi mogą być INSERT oraz DELETE. Klauzula OF cenaSieci w wierszu 2) na rys. 6.7 mo­że (ale nie musi) wystąpić dla zdarzenia UPDATE, a jeśli występuje, to określa po wyrazie oF te atrybuty, których ma dotyczyć modyfikacja. Klauzula OF nie jest dopuszczalna dla zdarzeń DELETE oraz INSERT, ponieważ te operacje mają sens wyłącznie dla całych krotek.

6.6. WYZWALACZE W JĘZYKU SQL3

409

Jeśli akcje są opisane pojedynczymi instrukcjami SQL, to wówczas . oddziela się je średnikami.

Jeśli zdarzenie polega na modyfikacji, to są z nim związane dwie krotki, stara i nowa, odpowiednio sprzed modyfikacji i po modyfika­cji. Knotkom tym nadaje się nazwy w klauzulach oLD AS i NEw AS, a przykład stosowania umieszczono w wierszach 4) i 5). Jeśli zdarze­nie polega na dopisaniu knotki, to można użyć klauzuli NEW AS do na­zwania wstawianej knotki, klauzula oLD AS nie jest dopuszczalna w takiej sytuacji. Odwrotnie postępujemy w przypadku usuwania knotek, wówczas korzystamy z klauzuli oLD AS do nazwania usuwa­nej knotki, natomiast klauzuli NEw As nie stosuje się.

Jeśli opuścimy opcję FOR EACH ROW w wierszu 10), to wyzwalacz poziomu-wierszy (row-level trigger) stanie się wyzwalaczem poziomu­-instrukcji (statement-level trigger). Wyzwalacz poziomu-instrukcji wykonuje się jeden raz przy instrukcji powodującej jedno lub więcej zdarzeń wyzwalacza. Na przykład, jeśli instrukcja SQL polega na mo­dyfikacji całej tabeli, to wyzwalacz poziomu-instrukcji wykona się tylko jeden raz, a wyzwalacz poziomu-wierszy wykona się przy mo­dyfikacji każdej knotki. W przypadku wyzwalaczy poziomu-instrukcji nie można bezpośrednio korzystać ze starych i nowych knotek (wier­sze 4) i 5)). Można wówczas mówić raczej o zbiorze starych knotek (knotkach usuniętych lub starych wersjach knotek zmodyfikowanych) i o zbiorze nowych knotek (knotek wstawianych lub nowych wersji knotek modyfikowanych) jako o dwóch relacjach. Stosujemy zatem, zamiast deklaracji z wierszy 4) i 5) z rys. 6.7, deklaracje OLD-TABLE AS Testare oraz NEW_TABLE AS TeNowe. Wyraz Testare ozna­cza nazwę relacji zawierającej wszystkie stare knotki, a TeNowe ozna­cza relację zawierającą nowe knotki.

PRZYKŁAD 6.17

Załóżmy, że należy ochronić wartość sieci prezesów od spadku poniżej 500 000 $. Takie ograniczenie może zostać naruszone przez każdą operację: wstawianie, usuwanie oraz modyfikację w kolumnie cenasieci relacji:

FilmDyr(nazwisko, adres, cert#, cenasieci)

Trzeba więc utworzyć wyzwalacze dla każdego z tych trzech zdarzeń. Na rysunku 6.8 pokazano przypadek modyfikacji. Wyzwalacze dla wstawiania i usuwania są podobne, ale nieco prostsze.

W wierszach od 3) do 5) określono, że TeNowe i Testare są nazwami relacji zawierających odpowiednio stare i nowe knotki uzyskane w wyniku operacji wyzwalania na bazie danych. Trzeba zauważyć, że jedna instrukcja SQL może spowodować modyfikację szeregu knotek relacji i wówczas TeNo­we i Testare będązawierać wiele knotek.

41 O 6. WIĘZY 1 WYZWALACZE W JĘZYKU SQL

1) CREATE TRIGGER WyzwalaczŚrCenySieci

2) INSTEAD OF UPDATE OF cenaSieci ON FilmDyr 3) REFERENCING

4) OLD_TABLE AS TeStare 5) NEW_TABLE AS TeNowe 6) WHEN (500000<=

7) (SELECT AVG(cenaSieci)

8) EROM ((FilmDyr EXCEPT TeStare) UNION TeNowe)) 9) DELETE EROM FilmDyr

10) WHERE (nazwisko, adres, cert#, cenaSieci) IN TeStare;

11) INSERT INTO FilmDyr

12) (SELECT * EROM TeNowe); RYSUNEK 6.8

Wyzwalacz dla wartości średniej ceny sieci

Gdy operacja polega na modyfikacji, to Testare i TeNowe zawierają odpowiednio nowe i stare wersje modyfikowanych krotek. Analogiczny wy­zwalacz, utworzony dla operacji usuwania, umożliwiałby dostęp do usunię­tych krotek z relacji TeStare, a nie dawałby możliwości deklarowania od­powiednika relacji TeNowe typu NEW_TABLE. Podobnie analogiczny wyzwa­lacz dla wstawiania dołączałby nowe krotki do relacji TeNowe, a nie byłoby w nim deklaracji Testare.

W wierszach od 6) do 8) określono warunek. Jest on spełniony, jeżeli średnia wartość ceny sieci po modyfikacji wynosi co najmniej 500 000 $. Zauważmy, że wyrażenie w wierszu 8) opisuje, jaka będzie wartość relacji FilmDyr w przypadku wykonania modyfikacji.

Jednakże, ponieważ w wierszu 2) występuje klauzula INSTEAD oF, więc żadna próba wykonania modyfikacji w kolumnie cenasieci nie powiedzie się. Modyfikacja nie dojdzie nigdy do skutku. Zamiast tego warunek zawarty w wyzwalaczu rozstrzyga, jakie akcje zostaną przetworzone. W naszym przy­kładzie, jeśli modyfikacja nie powoduje obniżenia wartości sieci poniżej 500 000 $, to w wyniku działania wyzwalacza modyfikacja zostanie wpro­wadzona do tabeli. W wierszach 9) i 10) zapisano instrukcje usunięcia tych wierszy, które zostałyby wprowadzone w wyniku modyfikowania relacji, a w wierszach 11) i 12) wstawiania nowych wersji tych knotek.

0

6.6.3. Asercje w języku SQL3

Zakres stosowania asercji w SQL3 jest rozszerzony w stosunku do SQL2 w następujący sposób:

6.6. WYZWALACZE W JĘZYKU SQL3

411

1. W przypadku naruszenia więzów asercje są wyzwalane przez zdarze­nia, które określa programista, a nie system.

2. Asercja może obejmować poszczególne krotki, nie tylko całątabelę.

PRZYKŁAD 6.18

Na rysunku 6.9 zapisano w SQL3 asercję BogatyPrezes z przykładu 6.10. Jak zwykle w wierszu 1) jest początek deklaracji. W wierszach od 2) do 6) opisano zdarzenia, które mogą wyzwalać poszczególne asercje.

1) CREATE ASSERTION BogatyPrezes 2) AFTER

3) INSERT ON Studio,

4) UPDATE OF prezC# ON STUDIO,

5) UPDATE OF cenaSieci ON FilmDyr, 6) DELETE ON FilmDyr

7) CHECK (NOT EXISTS

8) (SELECT * EROM Studio, FilmDyr

9) WHERE prezC# = cert# AND cenaSieci <10 000 000

RYSUNEK 6.9 Asercja w SQL3

Zauważmy, że aby przewidzieć wszystkie potencjalne zmiany w bazie, które moglyby naruszyć więzy określone w wierszach od 7) do 9), należy sprawdzać każdego nowego prezesa lub zmianę ceny sieci należących do dyrektorów produkcji. Stąd też polecenia zapisane w wierszach 3) i 4) spowo­dują, że asercja będzie sprawdzana za każdym razem, gdy będzie wstawiana krotka do relacji studio lub gdy nastąpi żądanie zmiany numeru certyfikatu prezesa studia (tzn. modyfikacja prezesa). Z kolei w wierszach 5) i 6) ustalo­no, że sprawdzanie warunków nastąpi albo gdy zajdzie zmiana ceny sieci dyrektora, albo gdy będzie usuwana krotka z jego danymi. Sprawdzane wa­runki zostały sformułowane w wierszach od 7) do 9) i są one dokładnie takie same jak w przykladzie 6.10.

0

Zasadnicza różnica między koncepcją asercji w SQL2 i SQL3 polega na tym, że w SQL3 jawnie wpisuje się, w jakich sytuacjach mają być sprawdzane warunki, co widać na rys. 6.9. W SQL3 jest zatem łatwiej zaim­plementować asercje, ale z kolei jest trudniej z nich korzystać. Użytkownik musi bowiem:

1. przewidzieć sytuacje, w których będą wyzwalane więzy;

2. ponosić ryzyko pozostawienia bazy w stanie niespójnym, jeśli zdarze­nia zostały dobrane w niewłaściwy sposób.

412 6. WIĘZY I WYZWALACZE W JĘZYKU SQL

6.6.4. Ćwiczenia do podrozdzia~u 6.6

Ćwiczenie 6.6.1. Należy napisać w SQL3 wyzwalacze podobne do deklaracji z rys. 6.5, określone dla zdarzeń wstawiania i usuwania w relacji FilmDyr.

Ćwiczenie 6.6.2. Napisać w SQL3 następujące wyzwalacze i asercje dla schematu z przykładu „PC" z ćwiczenia 4.1.1:

Produkt (producent, model, typ)

PC (model, szybkość, ram, hd, cd, cena)

Laptop (model, szybkość, ram, hd, ekran, cena) Drukarka (model, kolor, typ, cena)

*a) Przy zmianie ceny PC sprawdzać, że nie ma żadnego PC o takiej samej czę­stotliwości zegara, ale o niższej cenie.

b) Przy wstawianiu nowej drukarki sprawdzać, że numer modelu istnieje w ta­beli Produkt.

!c) Przy każdej zmianie w relacji Laptop sprawdzać, czy średnia cena laptopów każdego producenta nie jest wyższa niż 2000 $.

!d) Przy zmianie RAM lub twardego dysku w PC należy sprawdzić, czy ten modyfikowany PC ma co najmniej 100 razy więcej pamięci zewnętrznej niż operacyjnej .

!e) Przy wstawianiu nowego PC, laptopa lub nowej drukarki należy sprawdzić, że numer modelu nie występuje wśród istniejących produktów odpowied­niego typu.

Ćwiczenie 6.6.3. Dla schematu bazy danych:

Klasy (klasa, typ, kraj, liczbaDział, działo, wyporność) Okręt (nazwa, klasa, wodowanie)

Bitwa (nazwa, data)

Rezultat (okręt, bitwa, wynik)

z ćwiczenia 4.1.3 należy w SQL3 zapisać wyzwalacze i asercje, które są określone w sposób następujący:

*a) Przy wstawianiu nowej krotki do relacji Klasy wstawiać jednocześnie okręt o nazwie klasy, z datą wodowania równą NULL.

b) Jeśli atrybut wyporność we wstawianej klasie ma wartość ponad 35 000, to należy do bazy wstawić krotkę z wartością 35 000.

!c) Jeśli do relacji Rezultat wstawia się krotkę, to należy sprawdzać, czy okręt i bitwa są wstawione do odpowiednich relacji okręt i Bitwa, a jeśli nie, to wstawić brakujące krotki. W przypadkach koniecznych skorzystać z wartości IVULL.

!d) Przy wstawianiu krotek do relacji okręt oraz przy zmianach wartości atry­butu klasa w relacji okręt sprawdzać, czy żaden kraj nie ma więcej niż 20 okrętów, a jeśli ma, to nie dopuścić do wstawienia nowej krotki.

!!e) Sprawdzić, czy w okolicznościach mogących naruszać więzy, że żaden sta­tek, który uczestniczył w bitwie, nie zatonął wcześniej. Jeśli tak, to nie do­puścić do modyfikacji, która może naruszyć ten warunek.

6.7. PODSUMOWANIE ROZDZIAŁU 6

413

!Ćwiczenie 6.6.4. Następujące warunki zapisać w SQL3 albo w postaci wyzwalaczy, albo w postaci asercji. Zadania dotycząprzykładu filmowego:

Film (tytuł, rok, długość, czyKolor, nazwaStudia, producentC#)

GwiazdyW (tytułFilmu, rokFilmu, nazwiskoGwiazdy) GwiazdaFilmowa(nazwisko, adres, płeć, dataUrodzenie) FilmDyr(nazwisko, adres, cert#, cenaSieci)

Studio (nazwisko, adres prezC#)

Można założyć, że przed zmianami bazy warunki są spełnione. Należy raczej starać się, aby dopuścić wykonanie polecenia, nawet kosztem wstawienia krotek z warto­ściami NULL lub domniemanymi, niż odrzucać żądanie modyfikacji.

a) Każda gwiazda z relacji Gwiazdyw musi występować w relacji Gwiazda­Filmowa.

b) Każdy dyrektor produkcji jest albo prezesem studia, albo producentem filmu, albo jednym i drugim.

c) W każdym filmie występuje co najmniej jedna aktorka i jeden aktor.

d) Żadne studio w jednym roku nie wyprodukowało więcej niż 100 filmów.

e) Średnia długość wszystkich filmów wyprodukowanych w ciągu jednego roku nie przekracza 120.

!Ćwiczenie 6.6.5. W przykładzie 6.17 błędy modyfikacji były obsługiwane w ten sposób, że najpierw był sprawdzany warunek i, jeśli więzy nie były naruszone, to dopiero wtedy następowała modyfikacja. Inne podejście polega na tym, że dopuszcza się modyfikacje, a potem, jeśli okaże się, że narusza to więzy, anuluje się tę modyfi­kację. Należy napisać takie wyzwalacze.

6.7. Podsumowanie rozdziaw 6

• Więzy klucza (key constraints): Slowa kluczowe PRIMARY KEY lub oN1QUE określają w deklaracji schematu bazy danych ten atrybut lub zbiór atrybutów, które stanowią klucz relacji.

• Więzy integralności referencyjnej (referential integrity constraints): Korzystając ze słowa kluczowego REFERENCES lub FOREIGN KEY, można zadeklarować, że pewna wartość (lub zbiór wartości) z jednej relacji występuje jako wartość (wartości) klucza głównego pewnej knotki innej relacji.

• Więzy typu CHECK dla wartości (value-based check constraints): Można sprawdzać warunki określane dla wartości atrybutów przez umieszczenie w definicji schematu relacji przy tym atrybucie słowa CHECK oraz warunku, który ma być sprawdzany. Drugi sposób spraw­dzania warunków dla wartości atrybutów polega na wpisaniu tego wa­runku w definicję dziedziny atrybutu.

414 6. WIĘZY I WYZWALACZE W JĘZYKU SQL

1 Więzy krotkowe typu CHECK (tuple-based check constraints): Spraw­dzenie, że wartości składowej lub składowych krotek pewnej relacji zawsze spełniają określone warunki, może następować w wyniku do­łączenia do deklaracji relacji słowa kluczowego CHECK oraz warunku, który ma być przestrzegany.

~ Asercje (assertions): Do schematu bazy danych można dołączyć asercje jako element schematu, korzystając ze słowa kluczowego cHECK oraz określenia warunku. W warunku może występować więcej niż jedna nazwa relacji ze schematu bazy danych, a także asercja może dotyczyć relacji jako całości, jeśli warunek dotyczy np. pewnego agregatu.

1 Wykonywanie sprawdzeń (invoking the checks): Asercje są sprawdzane zawsze wtedy, kiedy następuje jakakolwiek modyfikacja relacji, która może naruszać warunek asercji. Warunki określone dla wartości atry­butów oraz dla krotek są sprawdzane tylko wówczas, gdy zmienia się wartość atrybutu lub składowej relacji, którą obejmują więzy, w wyniku wstawiania nowej krotki lub zmiany wartości w istniejącej krotce. Za­tem te więzy mogą zostać naruszone, jeśli zawierają podzapytania od­noszące się do innych relacji lub innych krotek tej samej relacji.

1 Wyzwalacze (triggers) w SQL3: W standardzie SQL3 zostały zawarte wyzwalacze, które mogą zawierać określenie zdarzeń (np. wstawień, usunięć lub zmian w określonej relacji) powodujących wykonanie sprawdzenia. Przy spełnieniu warunku wyzwalacze powodują wyko­nanie wyspecyfikowanego ciągu akcji (zapisanych jako ciąg instrukcji SQL, takich jak zapytania lub modyfikacje bazy danych).

~ Asercje w ,SQL3: Pojęcie asercji w SQL3 rożni się od koncepcji z SQL2. Podobnie jak wyzwalacze w SQL3, tak i asercje mogą dzia­łać pod wpływem zajścia określonych zdarzeń, np. wstawienia krotki do relacji. Po uruchomieniu asercji jest sprawdzany warunek dotyczą cy albo relacji, albo krotek, i jeśli nie jest on spełniony, to nie docho­dzi do wykonania modyfikacji.

6.8. Literatura do rozdzia~u 6

Sposób dostępu do dokumentacji standardu SQL2 i SQL3 zostal zawarty w bibliografii przedstawionej po rozdziale 5. Pozycja [4] jest źródłem informacji o wszystkich elementach aktywnych w systemach baz danych. Z kolei w [1] przedstawiono rozważania na temat ele­mentów aktywnych w SQL3 i pomyslów następnych standardów. W [2J i [3] omówiono HiPAC - system w fazie wczesnego prototypu, który zawiera elementy aktywne bazy danych.

1. Cochrane R.J., Pirahesh H., Mattos N.: Integrating triggers and declarative constraints in SQL database systems. Intl. Conf on nery Large Database Systems, s. 567-579, 1996.

2. Dayal U. i inni: The HiPAC project: combining active databases and timing constraints. SIGMOD Record 17:1, s. 51-70, 1988.

3. McCarthy D.R., Dayal U.: The architecture of an active database management system. Proc. ACM SIGMOD Intl. Conf on Management of Data, s. 215-224, 1989.

4. Widom J., Ceri S.: Active Database Systems. Morgan-Kaufmann, San Francisco, 1996.

---