11. Integrität und Trigger Grundlagen und Begriffe Transaktion

11. Integrität und Trigger
■
Grundlagen und Begriffe
■
Transaktionen
■
Integritätsbedingungen
■
Integritätsregeln in SQL
■
Trigger
VL Datenbanken I – 11–1
Grundlagen und Begriffe
Integritätsbedingung (IB) = Bedingung für die „Zulässigkeit“
(„Korrektheit“) betreffend
■
(einzelne) Datenbankzustände σ ,
■
Zustandsübergänge hσalt , σneu i oder auch
■
langfristige Datenbankentwicklungen
Bezeichnungen:
Bedingungsklasse
zeitlicher Kontext
statisch
Datenbankzustand
dynamisch transitional Zustandsübergang
temporal
Zustandsfolge
VL Datenbanken I – 11–2
Transaktion
Eine Transaktion ist eine Folge von Operationen (Aktionen), die die Datenbank von einem konsistenten
Zustand in einen konsistenten, eventuell veränderten, Zustand überführt, wobei das ACID-Prinzip eingehalten werden muß.
Aspekte:
■
Semantische Integrität: Korrekter (konsistenter)
DB-Zustand nach Ende der Transaktion
■
Ablaufintegrität: Fehler durch „gleichzeitigen“ Zugriff
mehrerer Benutzer auf dieselben Daten vermeiden
VL Datenbanken I – 11–3
ACID-Eigenschaften
■
Atomicity (Atomarität):
Transaktion wird entweder ganz oder gar nicht
ausgeführt
■
Consistency (Konsistenz oder auch Integritätserhaltung):
Datenbank ist vor Beginn und nach Beendigung einer
Transaktion jeweils in einem konsistenten Zustand
■
Isolation (Isolation):
Nutzer, der mit einer Datenbank arbeitet, sollte den
Eindruck haben, daß er mit dieser Datenbank alleine
arbeitet
■
Durability (Dauerhaftigkeit / Persistenz):
nach erfolgreichem Abschluß einer Transaktion muß das
Ergebnis dieser Transaktion „dauerhaft“ in der
Datenbank gespeichert werden
VL Datenbanken I – 11–4
Kommandos einer Transaktionssprache
■
Beginn einer Transaktion:
Begin-of-Transaction-Kommando BOT (in SQL implizit!)
■
commit: die Transaktion soll erfolgreich beendet werden
■
abort: die Transaktion soll abgebrochen werden
VL Datenbanken I – 11–5
Transaktion: Integritätsverletzung
■
Beispiel:
Übertragung eines Betrages B von einem
Haushaltsposten K1 auf einen anderen Posten K2
◆ Bedingung: Summe der Kontostände der
Haushaltsposten bleibt konstant
◆
■
vereinfachte Notation
Transfer = < K1:=K1-B; K2:=K2+B >;
■
Realisierung in SQL: als Sequenz zweier elementarer
Änderungen ; Bedingung ist zwischen den einzelnen
Änderungsschritten nicht unbedingt erfüllt!
VL Datenbanken I – 11–6
Architekturen zur Integritätssicherung
Anwendung 1
Anwendung n
DBMS
Datenbank
Vereinfachte Datenbank-Anwendungsarchitektur
VL Datenbanken I – 11–7
Integritätssicherung durch Anwendung
Anwendung 1
Routinen
Anwendung n
Routinen
DBMS
Datenbank
VL Datenbanken I – 11–8
Integritätsmonitor
Anwendung 1
Anwendung n
DBMS
Monitor
Datenbank
VL Datenbanken I – 11–9
Einkapselung
Anwendung 1
Anwendung n
Kapselung
DBMS
Datenbank
VL Datenbanken I – 11–10
Inhärente Integritätsbedingungen im RM
1. Typintegrität:
■ SQL erlaubt Angabe von Wertebereichen zu
Attributen
■ Erlauben oder Verbieten von Nullwerten
2. Schlüsselintegrität:
■ Angabe eines Schlüssels für eine Relation
3. Referentielle Integrität:
■ die Angabe von Fremdschlüsseln
VL Datenbanken I – 11–11
Integritätsbedingungen in SQL-DDL
■
not null: Nullwerte verboten
■
default: Angabe von Default-Werten
■
check ( search-condition ): Attributspezifische
Bedingung
(in der Regel Ein-Tupel-Integritätsbedingung)
■
primary key: Angabe eines Primärschlüssel
■
foreign key ( Attribut(e))
references Tabelle( Attribut(e) ):
Angabe der referentiellen Integrität
VL Datenbanken I – 11–12
Erhaltung der referentiellen Integrität
■
Überprüfung der Fremdschlüsselbedingungen nach
Datenbankänderungen
■
für πA (r1 ) ⊆ πK (r2 ),
z.B. πVerlagsname (Bücher) ⊆ πVerlagsname (Verlage)
◆ Tupel t wird eingefügt in r1 ⇒ überprüfen, ob t0 ∈ r2
existiert mit: t0 (K) = t(A), d.h. t(A) ∈ πK (r2 )
falls nicht ⇒ abweisen
◆ Tupel t0 wird aus r2 gelöscht ⇒ überprüfen, ob
σA=t0 (K) (r1 ) = {}, d.h. kein Tupel aus r1 refernziert t0
falls nicht leer ⇒ abweisen oder Tupel aus r1 , die t0
referenzieren, löschen (bei kaskadierendem Löschen)
VL Datenbanken I – 11–13
Überprüfungsmodi von Bedingungen
■
on update | delete
Angabe eines Auslöseereignisses, das die Überprüfung
der Bedingung anstößt
■
cascade | set null | set default | no action
Kaskadierung: Behandlung einiger
Integritätsverletzungen pflanzt sich über mehrere Stufen
fort, z.B. Löschen als Reaktion auf Verletzung der
referentieller Integrität
■
deferred | immediate legt Überprüfungszeitpunkt für
eine Bedingung fest
◆ deferred: Zurückstellen an das Ende der Transaktion
◆ immediate: sofortige Prüfung bei jeder relevanten
Datenbankänderung
VL Datenbanken I – 11–14
Überprüfungsmodi: Beispiel
Kaskadierendes Löschen
create table Bücher (
ISBN char(10) not null,
Titel varchar(200),
Verlagsname varchar(30),
primary key (ISBN),
foreign key (Verlagsname)
references Verlage (Verlagsname)
on delete cascade
)
VL Datenbanken I – 11–15
Die assertion-Klausel
■
Assertion: Prädikat, das eine Bedingung ausdrückt, die
von der Datenbank immer erfüllt sein muß
■
Syntax (SQL:1999)
create assertion name check ( prädikat )
■
Beispiele:
create assertion Preise check
( ( select sum (Preis)
from Buch) < 10000 )
create assertion Preise2 check
( not exists (
select Preis from Buch ) > 200 )
VL Datenbanken I – 11–16
Beispiele für Integritätsbedingungen
1. Das Konto von Weiß darf nicht überzogen werden.
2. Kein Kundenkonto darf unter -100 absinken.
3. Kunden sind durch ihre Namen identifiziert, d.h., kein
Kundenname darf mehrfach vorkommen.
4. Der Durchschnittspreis für Karotten muß unter dem für
Spargel liegen.
5. Nur solche Waren dürfen bestellt werden, für die es
mindestens einen Lieferanten gibt.
VL Datenbanken I – 11–17
Beispiele für Integritätsbedingungen (II)
6. Der Brotpreis darf nicht erhöht werden.
7. Kunden dürfen nur gelöscht werden, wenn sie keine
Waren mehr bestellt haben.
8. Der Preis eines Grundnahrungsmittels darf innerhalb
von drei Jahren höchstens um 15% steigen.
9. Kunden müssen gelöscht werden, wenn sie keine
Waren mehr bestellt haben.
VL Datenbanken I – 11–18
Mögliche Klassifikationen
Kriterium
Klassen
Granularität
Attribut
Tupel
Relation
Datenbank
Ausdrucksfähigkeit elementare Prädikate
Relationale Algebra
SQL
SQL + transitive Hülle
berechnungsvollständig
VL Datenbanken I – 11–19
Mögliche Klassifikationen (II)
Kriterium
Klassen
zeitlicher Kontext
statisch
transitional
temporal
Überprüfungszeitpunkt Einzeländerung
Operationsende
Transaktionsende
Reaktion
Zurückweisung (reject)
Korrektur (repair)
VL Datenbanken I – 11–20
Integritätsregeln
IR = hB, O, A, Ri
B
O
A
R
Integritätsbedingung
Menge von Datenbankobjekten, auf
die sich B bezieht
Auslöser, wann B zu überprüfen ist
Reaktionen, falls B verletzt ist
VL Datenbanken I – 11–21
Integritätsregeln (II)
■
im usprünglichen SQL-Sprachvorschlag
assert <Regelname>
[immediate | deferred] [ on <Op> ]
[for <Rel>] :
<Bedingung wie in where-Klausel>
[else ( <Folge von SQL-Anweisungen> )]
wobei
(A)
(O )
(O, B )
(R )
◆ <Op> ::= { insertion | deletion } of <Rel> |
update of <Rel> [ (<Attribut>)]
◆ <Rel> ::= <Relationsname> [ <Variablenname> ]
VL Datenbanken I – 11–22
Integritätsregeln in SQL: Beispiel I
Kunden sind durch ihre Namen eindeutig identifiziert:
assert IR1
for KUNDE K1, KUNDE K2:
not( K1.KName = K2.KName ) or
(K1.KAdr = K2.KAdr and K1.Kto = K2.Kto)
VL Datenbanken I – 11–23
Integritätsregeln in SQL: Beispiel II
Kein Kundenkonto darf unter -100 absinken:
assert IR2 for KUNDE : Kto >= -100
bzw. inklusive Angabe von Operationen:
assert IR2’
on insertion of KUNDE, update of KUNDE
for KUNDE : Kto >= -100
VL Datenbanken I – 11–24
Integritätsregeln in SQL: Beispiel III
Das Konto von Weiß darf nicht überzogen werden:
assert IR3 for KUNDE :
not KName = ’Weiß’ or Kto >= 0
VL Datenbanken I – 11–25
Integritätsregeln in SQL: Beispiel IV
Der Durchschnittspreis für Karotten muß unter dem für
Spargel liegen:
assert IR4 deferred
( select avg (Preis) from LIEFERANT
where Ware = ’Karotten’)
<= (select avg (Preis) from LIEFERANT
where Ware = ’Spargel’)
VL Datenbanken I – 11–26
Integritätsregeln in SQL: Beispiel V
Nur solche Waren dürfen bestellt werden, für die es
mindestens einen Lieferanten gibt:
assert IR5 for AUFTRAG A:
exists (select * from LIEFERANT
where Ware = A.Ware)
VL Datenbanken I – 11–27
Integritätsregeln in SQL: Beispiel VI
Der Brotpreis darf nicht erhöht werden:
assert IR6 (on update of LIEFERANT (Preis))
for LIEFERANT:
not Ware = ’Brot’ or
new Preis <= old Preis
VL Datenbanken I – 11–28
Integritätsregeln in SQL: Beispiel VII
Kunden dürfen nur gelöscht werden, wenn sie keine Waren
mehr bestellt haben:
assert IR7 deferred on deletion of KUNDE
for KUNDE K:
not exists (select * from AUFTRAG
where KName = old K.KName)
VL Datenbanken I – 11–29
Integritätsregeln in SQL: Beispiel VIII
Der Preis eines Grundnahrungsmittels darf innerhalb von
drei Jahren höchstens um 15% steigen.
⇒ kann in diesem Sprachvorschlag nicht ausgedrückt
werden!
VL Datenbanken I – 11–30
Integritätsregeln in SQL: Beispiel IX
Kunden müssen gelöscht werden, wenn sie keine Waren
mehr bestellt haben:
assert IR9 on deletion of AUFTRAG
for AUFTRAG A1:
exists (select * from AUFTRAG A2
where A2.KName = old A1.KName)
else (
delete KUNDE K
where K.KName = old A1.KName );
VL Datenbanken I – 11–31
Trigger
■
Trigger: Anweisung/Prozedur, die bei Eintreten eines
bestimmten Ereignisses automatisch vom DBMS
ausgeführt wird
■
Anwendung:
◆ Erzwingen von Integritätsbedingungen
(„Implementierung“ von Integritätsregeln)
◆ Auditing von DB-Aktionen
◆
Propagierung von DB-Änderungen
VL Datenbanken I – 11–32
Trigger (II)
create trigger ...
after <Operation>
<Anweisungen>
entspricht einer speziellen Integritätsregel:
assert ...on <Operation> :
false else ( <Anweisungen> )
VL Datenbanken I – 11–33
Trigger: Entwurf und Implementierung
■
Spezifikation von
◆ Ereignis und Bedingung für Aktivierung des Triggers
◆ Aktion(en) zur Ausführung
■
Syntax in SQL:1999 festgelegt
■
verfügbar in den meisten kommerziellen Systemen (aber
mit anderer Syntax)
VL Datenbanken I – 11–34
SQL:1999-Trigger
■
Syntax:
create trigger <Name>
after | before <Ereignis>
on <Relation>
[ when <Bedingung> ]
begin atomic < SQL-Anweisungen > end
■
Ereignis:
◆ insert
◆ update [ of <Liste von Attributen> ]
◆ delete
VL Datenbanken I – 11–35
Weitere Angaben bei Triggern
■
for each row bzw. for each statement: Aktivierung des
Triggers für jede Einzeländerungen einer
mengenwertigen Änderung oder nur einmal für die
gesamte Änderung
■
before bzw. after: Aktivierung vor oder nach der
Änderung
■
referencing new as bzw. referencing old as: Binden
einer Tupelvariable an die neu eingefügten bzw. gerade
gelöschten („alten’“) Tupel einer Relation
; Tupel der Differenzrelationen
VL Datenbanken I – 11–36
Beispiel für Trigger (I)
Kein Kundenkonto darf unter -100 absinken:
create trigger bad_account
after update of Kto on KUNDE
referencing new as INSERTED
when (exists
(select * from INSERTED where Kto < -100)
)
begin atomic
rollback;
end
; ähnlicher Trigger für insert
VL Datenbanken I – 11–37
Beispiel für Trigger (II)
Nur solche Waren dürfen bestellt werden, für die es
mindestens einen Lieferanten gibt:
create trigger bad_order
after insert on AUFTRAG
referencing new as INSERTED
when (exists (
select * from INSERTED I
where not exists (
select * from LIEFERANT
where Ware = I.Ware))
begin atomic
rollback;
end
VL Datenbanken I – 11–38
Beispiel für Trigger (III)
Kunden müssen gelöscht werden, wenn sie keine Waren
mehr bestellt haben:
create trigger bad_customer
delete on AUFTRAG
referencing old as O
for each row
when (not exists
(select * from AUFTRAG A
where A.KName = O.KName))
begin atomic
delete from KUNDE where KName = O.KName;
end
VL Datenbanken I – 11–39
Methoden der Integritätssicherung
■
■
Integritätssicherung durch Änderungstransformationen
◆
transformiert Änderungsanweisungen in einer
Hochsprache wie SQL so, daß das Ergebnis der
Transformation integritätsrespektierend ist
◆
Integritätsbedingungen werden in Änderungen
„hineinkompiliert“
Generierung von Triggern bzw. ECA-Regeln aus
deskriptiven Integritätsbedingungen
VL Datenbanken I – 11–40
Integritätssicherung durch Trigger
1. Bestimme Objekt oi , für das die Bedingung φ überwacht
werden soll
■ i.d.R. mehrere oi betrachten, wenn Bedingung
relationsübergreifend ist
■ Kandidaten für oi sind Tupel der Relationsnamen, die
in φ auftauchen
2. Bestimme die elementaren Datenbankänderungen u ij
auf Objekten oi , die φ verletzen können
■ Regeln: z.B. Existenzforderungen beim Löschen und
Ändern prüfen, jedoch nicht beim Einfügen etc.
VL Datenbanken I – 11–41
Integritätssicherung durch Trigger (II)
4. Bestimme je nach Anwendung die Reaktion ri auf
Integritätsverletzung
■ Rücksetzen der Transaktion (rollback)
■ korrigierende Datenbankänderungen
5. Formuliere folgende Trigger:
create trigger t-phi-ij after uij on oi
when ¬φ
begin ri end
6. Wenn möglich, vereinfache entstandenen Trigger
VL Datenbanken I – 11–42