Skript - Labor - Fachhochschule Stralsund

Skript zur Vorlesung
Datenbanken II
Fachbereich Elektrotechnik & Informatik
Fachhochschule Stralsund
Prof. Dr. Uwe Hartmann
Datenbanken
­2­
1. Komplexe Anfragen
Beispiel
"Gesucht sind die ANR der Ausstellungen, die keine Bilder des Malers 'K1' zeigen."
R ANR (A) 
 B))
ANR ( KNR='K1' (A BNR
SELECT ANR
FROM A
EXCEPT
SELECT ANR
FROM A x, B y
WHERE x.BNR=y.BNR
WHERE y.KNR='K1'
SELECT x.ANR
FROM A x
WHERE NOT EXISTS
(SELECT *
FROM A y, B z
WHERE y.BNR=z.BNR
AND x.ANR=y.ANR
AND z.KNR='K1')
Beispiel
"Gesucht sind die ANR der Ausstellungen, in denen alle Bilder des Malers 'K1' gezeigt wer­
den"
SELECT x.ANR
FROM A x
WHERE NOT EXISTS
(SELECT *
FROM B y
WHERE y.KNR='K1'
AND NOT EXISTS
(SELECT *
FROM A z
WHERE y.BNR=z.BNR
AND z.ANR=x.ANR))
Datenbanken
­3­
1.1 Relationenalgebra: Division
 Division
Seien R(A, B) und S(B) zwei Relationen. Die Division
T = R/S
ist eine Relation, wobei für jedes Tupel t  T gilt:
Es existiert für jedes Tupel aus s S ein Tupel r  R mit r = (t, s)
R
A
a
a
a
b
b
c
c
B
x
y
z
y
z
x
z
S
B
x
z
T=R/S
A1
a
c
Beispiel
"Gesucht sind die ANR der Ausstellungen, in denen alle Bilder des Malers 'K1' gezeigt wer­
den"
R ANR,BNR (A) /
BNR ( KNR='K1' (B))
Realisierung der Division durch andere Algebraoperationen
Seien R(A, B) und S(B) zwei Relationen. Die Division T = R/S ist eine Folge von Operatio­
nen:
R1 
A (R)
R2 = R1  S
R3 = R2 ­ R
R4 =
A (R3)
T = R1 ­ R4
Datenbanken
­4­
1.2 Relationantupelkalkül
 prozedurale Sprache
Formulierung der Prozedur (Weg), wie ein System von einem Ausgangszustand in einen
Endzustand gelangen soll.
 deskriptive Sprache
Formulierung der Eigenschaften des Endzustandes (Ziel), in den ein System von einem
Ausgangszustand gelangen soll.
 Grundbegriffe der Prädikatenlogik
 Terme
 Formeln
 Quantoren
 freie/gebundene Variable
Beispiel
Unterscheidung zwischen Aussageformen und Aussagen
Aussageform
"Der Student Meier hört die Vorlesung x"
Aussage
"Der Student Meier hört die Vorlesung Datenbanken"
Aussage
"Der Student Meier hört eine Vorlesung"
Definition des Relationentupelkalküls
 Alphabet
Das Alphabet enthält
 Relationennamen
 Attributnamen
 Konstante
 Tupelvariable
 Vergleichssymbole ( =, , <, , >, )
 logische Symbole (  ,  ,  , ,  )
 Klammern
 Terme
1. Eine Konstante ist ein Term.
2. Ist a ein Attributname und x eine Tupelvariable, dann ist x.a ein Term.
 Rangeformeln
3. Ist x eine Tupelvariable und R ein Relationenname, dann ist x / R eine Rangeformel.
Datenbanken
­5­
4. Sind U und V Rangeformeln, die keine gemeinsame Tupelvariable besitzen, dann ist
U  V eine Rangeformel.
 Subformeln
5. Sind t1 und t 2 Terme und ist  ein Vergleichssysmbol, dann ist t1 t 2 eine Subfor­
mel.
6. Sind S und T Subformeln, dann sind  S, S  T, S  T und S  T Subformeln.
7. Ist S eine Subformel, x eine Tupelvariable und R ein Relationenname, dann sind
(x / R)S und ( x / R)S Subformeln.
 Formeln
8. Eine Rangeformel ist eine Formel.
9. Eine Subformel, die keine freien Variablen besitzt, ist eine Formel.
10. Ist U eine Rangeformel und S eine Subformel und kommen alle freien Variablen von S
in U vor, dann ist S  U eine Formel.
11. Sind F und G Formeln, dann ist F  G eine Formel.
12. Die und nur die durch 8 ... 11 definierten Formeln sind Formeln.
 Eine Integritätsbedingung ist eine Formel, die keine freien Variablen besitzt (Aussage)
Beispiel
( x / Verkauf)( y / Artikel)(x.ANR y.ANR)
 Eine Datenbankanfrage ist ein Paar
{ (t1 , ... , t n ) : F }
mit (t1 , ... , t n ) : Target­Liste,
t i : Terme,
F : Formel,
bei dem alle in F vorkommenden freien Variablen in der Targetliste enthalten sein müssen.
Ergebnis einer Datenbankanfrage ist die Menge von Tupeln der Struktur (t1 , ... , t n ) , für
die F wahr ist.
Beispiel
R
A
a1
a1
a2
a3
B
b1
b2
b3
b3
S
B
b1
b1
b3
b4
C
c1
c2
c3
c4
{ (x.A, x.B) : x / R  ( y / S)(x.B y.B) }
Äquivalenz von Algebraoperationen und Kalkülausdrücken
geg. 2 Relationen R(A), S(A), A­Attributmenge
Datenbanken
­6­
 Vereinigung
R  S  { (x.A) : x / R  x / S }
 Durchschnitt
R  S  { (x.A) : x / R  ( y / S)(x.A  y.A) }
 Differenz
R  S  { (x.A) : x / R   ( y / S)(x.A  y.A) }
geg. 2 Relationen R(A), S(B), A,B­Attributmengen
 Projektion
A1 ,...,A n (R)
 { (x.A1 ,..., x.A n ) : x / R }
 Selektion
 P (R)
 { (x.A) : x / R  P(x) }
 Produkt
R  S  { (x.A, y.B) : x / R  y / S }
 Join
R

S  { (x.A, y.B) : x / R  y / S  x.A i  y.B j }
Ai  B j
Beispiel
"Gesucht sind Titel und Wert aller Bilder."
{ (x.Titel, x.Wert) : x / B }
Beispiel
"Gesucht sind die Namen aller Pariser Galerien."
{ (x. Bezeichnung) : x / G  x.Sitz =' Paris' }
Beispiel
"Gesucht sind die Orte, an denen sich Museen oder Galerien befinden."
{ (x.Sitz) : x / G  x / M }
Datenbanken
­7­
Beispiel
"Gesucht sind die Namen von Museen, die Bilder besitzen, sowie deren Titel und Wert."
{ (x. Bezeichnung, y.Titel, y.Wert) : x / M  y / B  x.ENR = y.ENR }
Beispiel
"Gesucht sind die Name der Galerien, in deren Besitz sich Bilder befinden"
{ (x. Bezeichnung) : x / G  ( y / B)(x.ENR = y.ENR) }
Beispiel
"Gesucht sind die Namen der Galerien, die keine Bilder besitzen."
{ (x. Bezeichnung) : x / G   ( y / B)(x.ENR = y.ENR) }
Transformationsregeln
R
x
P,Q
P(x)
­
­
­
­
Relation
Tupelvariable
Subformeln
Subformel, in der x vorkommt

( x / R) P(x)

 ( x / R)  P(x)

 (x / R) P(x)

( x / R)  P(x)

 ( x / R) P(x)

( x / R)  P(x)

( x / R) P1 (x)  (x / R) P2 (x) 

( x / R) P1(x)  ( x / R) P2 (x) 

P  Q

P  Q
(x / R) (P1 (x)  P2 (x))
( x / R) (P1 (x)  P2 (x))
Datenbanken
­8­
 Division
R / S  { (x.A) : x / R  (y / S)( z / R)(y.B  z.B  z.A = x.A) }
Beispiel
"Gesucht sind die Titel der Ausstellungen, die alle Bilder ausstellen"
{ (x.A_ Titel) : x / A  ( y / B)( z / A)(y.BNR  z.BNR  z.ANR = x.ANR) }
Beispiel
"Gesucht sind die Titel der Ausstellungen, die alle Bilder des Malers mit der KNR = 'K2' aus­
stellen"
{ (x.A_ Titel) : x / A  ( k / B)(k.KNR =' K2' ) 
(y / B)(y.KNR =' K2'  ( z / A)(y.BNR z.BNR  z.ANR = x.ANR)) }
Beispiel
'Gesucht sind die Titel der Ausstellungen, die (genau) die gleichen Bilder ausstellen, die auch
in der Ausstellung 'A1' ausgestellt werden.'
{ (x.A_Titel) : x/A  (y/A)(y.ANR =' A1'  (z/A)(y.BNR z.BNR  z.ANR = x.ANR))
 (u/A)(u.ANR = x.ANR  (v/A)(u.BNR v.BNR  v.ANR =' A1' ))}
{ (x.A_Titel) : x/A   (y/A)(y.ANR =' A1' (z/A)(y.BNR z.BNR  z.ANR = x.ANR))
  (u/A)(u.ANR = x.ANR   (v/A)(u.BNR  v.BNR  v.ANR =' A1' ))}
{ (x.A_Titel) : x/A  (y/A)((y.ANR =' A1'  (z/A)(y.BNR z.BNR  z.ANR = x.ANR))
 (y.ANR = x.ANR  (v/A)(y.BNR v.BNR  v.ANR =' A1' )))}
{ (x.A_Titel) : x/A   (y/A)((y.ANR =' A1' (z/A)(y.BNR z.BNR  z.ANR = x.ANR))
 (y.ANR = x.ANR   (v/A)(y.BNR v.BNR  v.ANR =' A1' )))}
Datenbanken
­9­
1.3 Abbildung von Kalkülausdrücken in SQL
Kalkül­Datenbankanfrage:
SQL­Datenbankanfrage:
SELECT
FROM
WHERE
{ (t1 , ... , t n ) : F }
mit (t1 , ... , t n ) : Target­Liste,
F : Formel
select-list
table-reference
search-condition
Abbildung SQL­Kalkül
Target­Liste
Rangeformel
(restl.) Formel



select-list
table-reference
search-condition
Beispiel
"Gesucht sind die Namen von Museen, die Bilder besitzen, sowie deren Titel und Wert."
{ (x. Bezeichnung, y.Titel, y.Wert) : x / M  y / B  x.ENR = y.ENR }

SELECT x.Bezeichnung, y.Titel, y.Wert
FROM M x, B y
WHERE x.ENR=y.ENR
Beispiel
"Gesucht sind die Name der Galerien, in deren Besitz sich Bilder befinden"
{ (x. Bezeichnung) : x / G  ( y / B)(x.ENR = y.ENR) }

SELECT x.Bezeichnung
FROM G x
WHERE EXISTS
(SELECT *
FROM B y
WHERE x.ENR=y.ENR)
Datenbanken
­ 10 ­
Beispiel
"Gesucht sind die Namen der Maler, deren Bilder sich in Museumsbesitz befinden."
{ (x.Name) : x/K  (y/B)(x.KNR = y.KNR  (z/M)(y.ENR = z.ENR)) }

SELECT x.Bezeichnung
FROM K x
WHERE EXISTS
(SELECT *
FROM B y
WHERE x.KNR=y.KNR
AND EXISTS
(SELECT *
FROM M z
WHERE y.ENR=z.ENR))
Transformation von Anfragen
 Anfragetypen
Anfragen
ungeschachtelt
geschachtelt
nicht­korreliert
korreliert
 ungeschachtelte Anfrage
Anfrage ohne innere SELECT­Anweisung (Subanfrage)
 Anfrage mit nicht­korrelierter Subanfrage
Anfrage, bei der eine Subanfrage keinen Bezug auf eine in einer äußeren Anfrage definierte
Rangevariable nimmt
 Anfrage mit korrelierter Subanfrage
Anfrage, bei der eine Subanfrage Bezug auf eine in einer äußeren Anfrage definierte Ran­
gevariable nimmt
 negierte Subanfrage
Subanfrage, bei der die zugehörige WHERE­Bedingung der äußeren durch NOT negiert
wird (NOT IN, NOT EXISTS)
Regeln zur Transformation zwischen join-, IN- und EXISTS-Anfragen
Datenbanken
­ 11 ­
1. Jede ungeschachtelte Anfrage (join) läßt sich in eine geschachtelte Anfrage (IN, EXISTS)
transformieren.
2. Eine geschachtelte Anfrage läßt sich nur dann in eine ungeschachtelte Anfrage transformie­
ren, wenn die Subanfrage nicht negiert ist.
3. Jede geschachtelte Anfrage mit nicht­korrelierter Subanfrage läßt sich in eine mit korrelier­
ter Subanfrage transformieren.
4. Eine geschachtelte Anfrage mit korrelierter Subanfrage läßt sich nur dann in eine ohne kor­
relierte Subanfrage transformieren, wenn die Korrelation sich auf die nächstäußere Anfrage
bezieht.
Beispiel
ungeschachtelt
SELECT x.Titel
FROM B x, A y
WHERE x.BNR=y.BNR
­­­
­­­
2. Trigger
TRIGGER
 Auslösendes Ereignis
nicht-korreliert
korreliert
SELECT x.Titel
FROM B x
WHERE x.BNR IN
(SELECT y.BNR
FROM A y)
SELECT x.Titel
FROM B x
WHERE EXISTS
(SELECT *
FROM A y
WHERE x.BNR=y.BNR)
SELECT x.Titel
FROM B x
WHERE x.BNR NOT IN
(SELECT y.BNR
FROM A y)
SELECT x.Titel
FROM B x
WHERE NOT EXISTS
(SELECT *
FROM A y
WHERE x.BNR=y.BNR)
­­­
SELECT x.Titel
FROM B x
WHERE NOT EXISTS
(SELECT *
FROM A y
WHERE NOT EXISTS
(SELECT *
FROM A z
WHERE y.ANR=z.ANR
AND z.BNR=x.BNR))
Datenbanken
 Ausgelöste Aktion
(wenn auslösendes Ereignis eingetreten, dann Aktion auslösen)
Klassifikation von Triggern
1. nach dem auslösenden Ereignis
 INSERT­Trigger
 UPDATE­Trigger
 DELETE­Trigger
1. nach dem Aktivierungszeitpunkt des Triggers
 Before­Trigger
 After­Trigger
1. nach der Granularität des Triggers
 Zeilen­Trigger
 Anweisungs­Trigger
(Hinweis: in DB2 dürfen Before­Trigger keine Anweisungs­Trigger sein)
­ 12 ­
Datenbanken
­ 13 ­
2.1 Erzeugen und Löschen
Syntax
trigger­definition ::=
CREATE TRIGGER trigger­name
[ NO CASCADE BEFORE | AFTER ] trigger­event ON table­name
[ REFERENCING referencing­specification ]
FOR EACH [ STATEMENT | ROW ] MODE DB2SQL1
[ WHEN (search­condition) ]
triggered­action
trigger­event ::=
INSERT
| DELETE
| UPDATE [ OF column­name [{ , column­name }...] ]
referencing­specification ::=
OLD [AS] range­variable
| NEW [AS] range­variable
| OLD_TABLE [AS] table­identifier
| NEW_TABLE [AS] table­identifier
| referencing­specification referencing­specification2
triggered­action ::=
triggered­statement
| BEGIN ATOMIC triggered­statement; [{ triggered­statement; }...] END
 BEFORE/AFTER: legt Aktivierungszeitpunkt fest
 trigger­event ON table­name: INSERT­, DELETE­ oder UPDATE­Operationen (auslösen­
de Ereignisse) auf eine Relation table­name
 FOR EACH STATEMENT / FOR EACH ROW: Granularität des Triggers
Einschränkung für DB2: Before­Trigger dürfen keine Anweisungstrigger sein
 triggered­action: eine einzelne Anweisung (triggered­statement) oder ein Block von mehre­
ren Anweisungen in BEGIN ... END, die beim Feuern des Triggers ausgeführt werden.
ATOMIC: alle Anweisungen im Block bilden eine Transaktion
 WHEN search­condition: Trigger­Aktion wird nur ausgeführt, wenn die Bedingung erfüllt
ist
1
2
MODE DB2SQL nur für DB2
Wiederholung siehe Erläuterung
Datenbanken
­ 14 ­
 REFERENCING: Definition von Bezeichnern für Tupel/die Relation vor bzw. nach ihrer
Modifikation durch das auslösende Ereignis
OLD range­variable: Tupelvariable repräsentiert ein durch ein Ereignis zu modifizieren­
des Tupel vor der Modifikation ('altes Tupel')
NEW range­variable: Tupelvariable repräsentiert ein durch ein Ereignis zu modifizie­
rendes Tupel nach der Modifikation ('neues Tupel')
OLD_TABLE table­identifier: Name einer (hypothetischen) Relation, die alle zu modi­
fizierenden Tupel vor der Modifikation enthält ('alte Relation')
NEW_TABLE table­identifier: Name einer (hypothetischen) Relation, die alle zu modi­
fizierenden Tupel nach der Modifikation enthält ('neue Relation')
Beispiel
REFERENCING NEW AS neuezeile
WHEN (neuezeile.Attribut NOT IN
(SELECT Attribut
FROM Relation2)
REFERENCING OLD AS altezeile NEW AS neuezeile OLD_TABLE AS altetabelle
WHEN (neuezeile.Attribut > altezeile.Attribut
AND 100 >
(SELECT COUNT(*)
FROM altetabelle))
REFERENCING OLD AS altezeile
INSERT INTO Relation2 VALUES
(altezeile.Attribut1, altezeile.Attribut2)
FOR EACH ROW
FOR EACH STATEMENT
BEFORE INSERT
NEW
(unzulässig)
BEFORE UPDATE
OLD, NEW
(unzulässig)
BEFORE DELETE
OLD
(unzulässig)
AFTER INSERT
NEW
NEW_TABLE
OLD, NEW
OLD_TABLE, NEW_TABLE
OLD
OLD_TABLE
­
NEW_TABLE
­
OLD_TABLE, NEW_TABLE
­
OLD_TABLE
AFTER UPDATE
AFTER DELETE
Syntax
drop­trigger­statement ::=
DROP TRIGGER trigger­name
Datenbanken
­ 15 ­
2.2 Spezielle Trigger-Aktionen
SET-Anweisung
Syntax
set­statement ::=
SET column­name = row­value­constructor [{ , column­name = row­value­constructor}...]
 entspricht der SET­Klausel einer UPDATE­Anweisung
 column­name: Attribut des 'neuen Tupels' eines INSERT­ oder UPDATE­Triggers
 jeder Trigger, der eine SET­Anweisung enthält, muß eine NEW range­variable definieren.
Beispiel
REFERENCING NEW AS neuezeile OLD AS altezeile
...
SET neuezeile.Attribut1=wert
REFERENCING NEW AS neuezeile
...
SET Attribut1=
(SELECT MAX(x.Attribut1)
FROM Relation2 x
WHERE x.Attribut2=Attribut2)
SIGNAL-Anweisung
Syntax
signal­statement ::=
SIGNAL SQLSTATE state ( message )
 löst eine Fehlerbedingung aus und setzt alle Änderungen, die zum Feuern des Triggers ge­
führt haben einschließlich bisheriger Aktionen des Triggers zurück
 state: String aus 5 Zeichen (Ziffern oder Großbuchstaben), wird als SQLSTATE ausgege­
ben
 message: String mit max. 70 Zeichen wird als Fehlernachricht ausgegeben
Beispiel
SIGNAL SQLSTATE '7000I' ('INSERT: Tupel bereits vorhanden')
SIGNAL SQLSTATE '7000I' ('INSERT: Wert ' || char(neuezeile.Attribut) || 'unzulässig')
2.3 Before-Trigger
Syntax (Before-Trigger)
triggered­statement::=
Datenbanken
­ 16 ­
set­statement
| signal­statement
Beispiel
Maler(KNR, Geburt, Geb_Ort, Tod), Bild(BNR, Titel, Jahr, Wert, KNR, ENR)
Integritätsbedingung:
Ein Bild, das ein Maler gemalt hat, muß zu seinen Lebzeiten entstanden sein. Wird diese
Bedingung verletzt, soll Fehlermeldung ausgegeben werden
CREATE TRIGGER INS_B
NO CASCADE BEFORE INSERT ON Bild
REFERENCING NEW AS new_row
FOR EACH ROW MODE DB2SQL
WHEN (EXISTS
(SELECT *
FROM Maler
WHERE KNR=new_row.KNR
AND new_row.jahr NOT BETWEEN Geburt AND Tod))
SIGNAL SQLSTATE 'INS01' ('INSERT Fehler: Entstehungsjahr '||char(new_row.Jahr));
INSERT INTO Bild VALUES
('B18','Bild1',1920,1000,'K5','E7');
INSERT INTO Bild VALUES
('B19','Bild2',1920,1000,null,'E7');
INSERT INTO Bild VALUES
('B20','Bild3',null,1000,'K5','E7');
UPDATE Bild
SET Jahr=1920
WHEN BNR='B2'
CREATE TRIGGER UPD_B
NO CASCADE BEFORE UPDATE OF Jahr ON Bild
REFERENCING NEW AS new_row
FOR EACH ROW MODE DB2SQL
WHEN (EXISTS
(SELECT *
FROM Maler
WHERE KNR=new_row.KNR
AND new_row.jahr NOT BETWEEN Geburt AND Tod))
SIGNAL SQLSTATE 'UPD01' ('UPDATE Fehler: Entstehungsjahr '||char(new_row.Jahr));
Beispiel
Bild(BNR, Titel, Jahr, Wert, KNR, ENR)
Aktion:
Wurde beim Hinzufügen eines Bild­Tupels kein Wert angegeben, wird der Wert auf den
aktuell kleinsten aller Bildwerte festgesetzt
CREATE TRIGGER INS_B
Datenbanken
­ 17 ­
NO CASCADE BEFORE INSERT ON Bild
REFERENCING NEW AS new_row
FOR EACH ROW MODE DB2SQL
WHEN (new_row.Wert IS NULL)
SET new_row.Wert =
(SELECT MIN(Wert)
FROM Bild);
INSERT INTO Bild VALUES
('B17','Bild1',1880,null,'K7','E7');
2.3 After-Trigger
 After­Trigger werden erst dann ausgeführt, wenn die auslösende Manipulationsanweisung
und alle kaskadierenden Folgeanweisungen (Integritätsbedingungen) erfolgreich ausgeführt
wurden
Syntax (After-Trigger)
triggered­statement::=
insert­statement
| delete­statement
| update­statement
| signal­statement
triggernde SQL­Anweisung
Trigger1: Anweisung1
Trigger2
Trigger3
Trigger1: Anweisung2
Trigger4
Beispiel
CREATE TRIGGER INS_B
AFTER INSERT ON Bild
REFERENCING NEW AS new_row
FOR EACH ROW MODE DB2SQL
WHEN (new_row.Wert IS NULL)
UPDATE Bild
SET Wert=
(SELECT MIN(Wert)
FROM Bild)
Datenbanken
­ 18 ­
WHERE BNR=new_row.BNR;
INSERT INTO Bild VALUES
('B17','Bild1',1880,null,'K7','E7');
Beispiel
Einrichtung(ENR, Bezeichnung, Sitz, Anzahl_Bilder)
Bild(BNR, Titel, Jahr, Wert, KNR, ENR)
Aktion:
Für INSERT und DELETE in Bild soll Anzahl_Bilder in Einrichtung aktualisiert werden
CREATE TRIGGER INS_B
AFTER INSERT ON Bild
REFERENCING NEW AS new_row
FOR EACH ROW MODE DB2SQL
UPDATE Einrichtung
SET Anzahl_bilder=Anzahl_Bilder+1
WHERE ENR=new_row.ENR;
CREATE TRIGGER DEL_B
AFTER DELETE ON Bild
REFERENCING OLD AS old_row
FOR EACH ROW MODE DB2SQL
UPDATE Einrichtung
SET Anzahl_bilder=Anzahl_Bilder­1
WHERE ENR=old_row.ENR;
INSERT INTO BIld VALUES
('B17','Bild 17',1910,600,'K7','E7');
DELETE FROM Bild
WHERE ENR='E1';
Datenbanken
­ 19 ­
Rekursion
 Bei der Definition rekursiver Trigger sind durch geeignete Abbruchbedingungen unendli­
che Rekursionen zu vermeiden
Beispiel
Männer
(0,n)
(0,m)
Frauen
Partner
Aktion:
Bei Eintrag eines Partners in Mann/Frau soll der entsprechende Eintrag in der referenzier­
ten Relation erfolgen:
CREATE TRIGGER INS_M
AFTER UPDATE OF Partner ON Mann
REFERENCING NEW AS new_row
FOR EACH ROW MODE DB2SQL
UPDATE Frau
SET Partner=new_row.MNR
WHERE FNR=new_row.Partner;
CREATE TRIGGER INS_F
AFTER UPDATE OF Partner ON Frau
REFERENCING NEW AS new_row
FOR EACH ROW MODE DB2SQL
UPDATE Mann
SET Partner=new_row.FNR
WHERE MNR=new_row.Partner;
2.4 Trigger und Integritätsbedingungen
Unterschiede zwischen CREATE TABLE-Bedingungen und Triggern
 CREATE TABLE­Bedingungen sind deskriptiver als TRIGGER­Bedingungen
 CREATE TABLE­Bedingungen werden zum Zeitpunkt ihrer Definition für alle existieren­
den Daten durchgesetzt
 TRIGGER­Bedingungen werden nur bei Änderungen durch INSERT/UPDATE/DELETE
ausgelöst
 TRIGGER sind mächtiger als CREATE TABLE­Bedingungen
Datenbanken
­ 20 ­
Interaktionen zwischen CREATE TABLE-Bedingungen und Triggern
1. SQL­Anweisung (trigger­event) produziert eine Änderungsliste mit den alten Werten
(OLD_ROW) und/oder den neuen Werten (NEW_ROW)
2. Die von der SQL­Anweisung aktivierten Before­Trigger werden in der Reihenfolge ihrer
Erzeugung auf die Änderungslisten ausgeführt.
3. Änderungen der Änderungsliste werden in der Datenbank durchgeführt. Integritätsbedin­
gungen werden überprüft, und alle Datenbankänderungen für die betreffende Anweisung
werden zurückgesetzt, falls eine Verletzung entdeckt wird. Fremdschlüsselbedingungen
mit Löschaktion wie CASCADE oder SET NULL können weiter Datenbankänderungen
verursachen. Jede dieser sekundären Änderungen wird ausgeführt:
a) Eine Sekundäränderung wird ausgeführt und produziert eine weitere Änderungslisten.
b) Von der Sekundäränderung aktivierte Before­Trigger werden in der Reihenfolge ihrer
Erzeugung auf die Änderungslisten ausgeführt
c) Änderungen der Änderungsliste werden in der Datenbank durchgeführt und außerdem
mit der Änderungsliste der ursprünglichen SQL­Anweisung gemischt. Integritätsbedin­
gungen werden überprüft, Rücksetzen und Kaskadieren wie unter 3.
4. After­Trigger, die von der ursprünglichen SQL­Anweisung oder einer der sekundären Än­
derungen ausgelöst wurden, werden ausgeführt.
Datenbanken
­ 21 ­
3. Erweitertes Entity-Relationship-Modell
3.1 Überdeckung
Überdeckung
Steht jedes Entity e1 E1in einer Beziehung R, so heißt die Beziehung R bezüglich E1
überdeckend.
Erweiterung des ER-Diagramms
(m1,m2)
E1
S1
A1
R
(n1,n2)
A3
E2
S2
A2
mit m1,n1 {0,1} und m2,n2  {1, m/n}
 Ein Entity e1 E1 steht mit mindestens n1 und höchstens n2 Entities e2 E2 in einer Bezie­
hung R und
Ein Entity e2 E2 steht mit mindestens m1 und höchstens m2 Entities e1 E1 in einer Be­
ziehung R.
Beispiel
Firma
(0,m)
(0,n)
Artikel
Verkauf
Student
(0,m)
(1,1)
Studiengang
immatrikuliert
Absolvent
(1,m)
(1,1)
Diplomarbeit
angefertigt
Mieter
Fahrzeug
(0,m)
(1,n)
(1,1)
(1,1)
Wohnung
Fahrzeugbrief
Datenbanken
­ 22 ­
Existenzabhängigkeit
Ein Entity eines Typs E1 (e1 E1), der in einer überdeckenden Beziehung R zu einem Enti­
ty­Typ E2 steht, kann nur existieren, wenn wenigstens eine Beziehung r R zu einem
e2 E2 existiert
Folgerung
Existiert für ein solches Entity keine Beziehung (mehr), wir es gelöscht (ON DELETE
CASCADE)
 Besetzung eines Entitätstyps E1
Bes(E1) = Anzahl der erwarteten Entitäten eines Typs E1, die in einer Beziehung R stehen
 Relative Besetzung eines Entitätstyps E1
RelBes(E1) = Bes(E1)/card(E1)
Abbildung von 1:1-Beziehungen
1. (0,1):(0,1)-Beziehungen
E1
E1
E2
R
E1
E2
(0,1)
R
(0,1)
E2
S1 (NOT NULL)
A1
PK S1
S2 (NOT NULL)
A2
PK S2
S1 (NOT NULL), UNIQUE
S2 (NOT NULL), UNIQUE
A3
PK S1,S2
FK S1 REF E1 ON DELETE CASCADE
FK S2 REF E2 ON DELETE CASCADE
S1 (NOT NULL)
A1
S2
A3
PK S1
FK S2 REF E2 ON DELETE SET NULL
IB S2 IS UNIQUE
IB CHECK(S2 IS NOT NULL OR A3 IS NULL )
S2 (NOT NULL)
A2
PK S2
2. (0,1):(1,1)-Beziehungen
Datenbanken
E1
E1
E2
­ 23 ­
(0,1)
R
(1,1)
E2
S1 (NOT NULL)
A1
S2 UNIQUE, NOT NULL
A3
PK S1
FK S2 REF E2 ON DELETE CASCADE
S2 (NOT NULL)
A2
PK S2
3. (1,1):(1,1)-Beziehungen
E1
(1,1)
R
(1,1)
E2
E
S1 (NOT NULL)
A1
S2 UNIQUE, NOT NULL
A2
A3
PK S1
E
S1 (NOT NULL), UNIQUE
A1
S2 (NOT NULL), UNIQUE
A2
A3
PK S1,S2
E
S1 (NOT NULL)
A1
A2
A3
PK S1
Abbildung von 1:n-Beziehungen
1. (0,1):(0,n)-Beziehungen
Datenbanken
E1
E1
E2
R
E1
E2
­ 24 ­
(0,1)
R
(0,n)
E2
S1 (NOT NULL)
A1
PK S1
S2 (NOT NULL)
A2
PK S2
S1 (NOT NULL)
S2 (NOT NULL), UNIQUE
A3
PK S1,S2
FK S1 REF E1 ON DELETE CASCADE
FK S2 REF E2 ON DELETE CASCADE
S1 (NOT NULL)
A1
PK S1
S2 (NOT NULL)
A2
S1
A3
PK S2
FK S1 REF E1 ON DELETE SET NULL
IB CHECK(S1 IS NOT NULL OR A3 IS NULL )
Datenbanken
­ 25 ­
2. (0,1):(1,n)-Beziehungen
E1
E1
E2
(0,1)
R
(1,n)
E2
S1 (NOT NULL)
A1
PK S1
IB INSERT(E1)
S2 (NOT NULL)
A2
S1
A3
PK S2
FK S1 REF E1 ON DELETE SET NULL
IB CHECK(S1 IS NOT NULL OR A3 IS NULL )
IB DELETE(E2)
INSERT(E1)
insert_e1(s1, a1, s2, a3)
{
BEGIN TRANSACTION
INSERT INTO E1 VALUES (s1, a1);
UPDATE E2 SET S1=s1, A3=a3
WHERE S2=s2 AND S1 IS NULL;
END TRANSACTION
}
DELETE(E2):
delete_e2(s2)
{
BEGIN TRANSACTION
if( SELECT * FROM E2 x WHERE S2=s2
AND x.S1 IS NOT NULL
AND NOT EXISTS
(SELECT * FROM E2 y WHERE y.S1=x.S1
AND y.S2<>x.S2))
{
DELETE E1 WHERE S1 = (SELECT S1 FROM E2 WHERE S2=s2);
}
DELETE E2 WHERE S2=s2;
END TRANSACTION
}
E1
S1
A1
S2
(NOT NULL)
(NOT NULL), UNIQUE
Datenbanken
E2
­ 26 ­
A3
PK S1, S2
FK S2 REF E2 ON DELETE CASCADE
IB Eindeutigkeit von A1 zu S1
S2 (NOT NULL)
A2
PK S2
3. (1,1):(0,n)-Beziehungen
E1
E1
E2
(1,1)
R
(0,n)
E2
S1 (NOT NULL)
A1
PK S1
S2 (NOT NULL)
A2
S1 NOT NULL
A3
PK S2
FK S1 REF E1 ON DELETE CASCADE
Datenbanken
­ 27 ­
4. (1,1):(1,n)-Beziehungen
E1
E1
E2
E
(1,1)
R
(1,n)
E2
S1 (NOT NULL)
A1
PK S1
IB INSERT(E1)
S2 (NOT NULL)
A2
S1 NOT NULL
A3
PK S2
FK S1 REF E1 ON DELETE CASCADE
IB DELETE(E2)
IB INSERT(E2)
S1 (NOT NULL)
A1
S2 (NOT NULL), UNIQUE
A2
A3
PK S1,S2
IB Eindeutigkeit von A1 zu S1
Datenbanken
­ 28 ­
Abbildung von m:n-Beziehungen
1. (0,m):(0,n)-Beziehungen
E1
E1
E2
R
(0,m)
R
(0,n)
E2
S1 (NOT NULL)
A1
PK S1
S2 (NOT NULL)
A2
PK S2
S1 (NOT NULL)
S2 (NOT NULL)
A3
PK S1,S2
FK S1 REF E1 ON DELETE CASCADE
FK S2 REF E2 ON DELETE CASCADE
Datenbanken
­ 29 ­
2. (0,m):(1,n)-Beziehungen
E1
E1
E2
R
E1
E2
(0,m)
R
(1,n)
E2
S1 (NOT NULL)
A1
PK S1
IB INSERT(E1)
S2 (NOT NULL)
A2
PK S2
IB DELETE(E2)
S1 (NOT NULL)
S2 (NOT NULL)
A3
PK S1,S2
FK S1 REF E1 ON DELETE CASCADE
FK S2 REF E2 ON DELETE CASCADE
S1 (NOT NULL)
A1
S2 (NOT NULL)
A3
PK S1,S2
FK S2 REF E2 ON DELETE CASCADE
IB Eindeutigkeit von A1 zu S1
S2
A2
PK S2
Datenbanken
­ 30 ­
3. (1,m):(1,n)-Beziehungen
E1
E1
E2
R
E
(1,m)
R
(1,n)
E2
S1 (NOT NULL)
A1
PK S1
IB INSERT(E1)
IB DELETE(E1)
S2 (NOT NULL)
A2
PK S2
IB INSERT(E2)
IB DELETE(E2)
S1 (NOT NULL)
S2 (NOT NULL)
A3
PK S1,S2
FK S1 REF E1 ON DELETE CASCADE
FK S2 REF E2 ON DELETE CASCADE
S1 (NOT NULL)
A1
S2 (NOT NULL)
A2
A3
PK S1,S2
IB Eindeutigkeit von A1 zu S1
IB Eindeutigkeit von A2 zu S2
Datenbanken
­ 31 ­
Abbildung schwacher Entity-Typen
E1
E1
E1
E2
E2
(1,1)
(0,n)
E2
S1 (NOT NULL)
A1
PK S1
S2 (NOT NULL), UNIQUE
A2
S1 (NOT NULL)
A3
PK S2,S1
FK S1 REF E1 ON DELETE CASCADE
Datenbanken
­ 32 ­
Abbildung rekursiver Beziehungen
Rekursive Beziehung
E
S
R
A
A1
Beispiel
Verkäufer
n
Verkauf
m
Firma
Käufer
ist Chef von
1
Angestellter
Chef
m
hat Chef
1
Person
1
Ehe
 Rekursive Beziehungen sind in der Regel nicht überdeckend (keine (1,1)­, (1,n)­Beziehun­
gen).
1. rekursive (0,n):(0,m)-Beziehungen
E
R
S
(NOT NULL)
A
PK S
S1 (NOT NULL)
S2 (NOT NULL)
A1
PK S1,S2
FK S1 REF E1(S) ON DELETE CASCADE
FK S2 REF E2(S) ON DELETE CASCADE
Datenbanken
­ 33 ­
2. rekursive (0,1):(0,m)-Beziehungen
E
R
E
S
(NOT NULL)
A
PK S
S1 (NOT NULL)
S2 NOT NULL
A1
PK S1
FK S1 REF E1(S) ON DELETE CASCADE
FK S2 REF E2(S) ON DELETE CASCADE
S
(NOT NULL)
A
S1
A1
PK S
FK S1 REF E(S) ON DELETE SET NULL
IB CHECK(S1 IS NOT NULL OR A1 IS NULL )
3. rekursive (0,1):(0,1)-Beziehungen
E
R
S
(NOT NULL)
A
PK S
S1 (NOT NULL), UNIQUE
S2 (NOT NULL), UNIQUE
A1
PK S1,S2
FK S1 REF E1(S) ON DELETE CASCADE
FK S2 REF E2(S) ON DELETE CASCADE
Datenbanken
­ 34 ­
3.2 Generalisierung
Generalisierung/Spezialisierung
Ein Entity­Typ E ist eine Generalisierung zweier Entity­Typen E1 und E2, analog: E1 und
E2 sind Spezialisierungen von E, wenn gilt
1. Alle Elemente von E1 und E2 kommen in E vor und
2. Jedes Element von E1 und E2 besitzt neben seinen eigenen Attributen zusätzlich die At­
tribute genau eines Elements von E
kurz: E1  E und E2  E.
Eine solche Beziehung wird IS­A­Beziehung genannt und im ER­Diagramm dargestellt
durch
E
S
A
IS­A
E1
E2
A1
A2
Folgerung
 Zwischen einem Subtyp und einem Supertyp herrscht eine 1:1­Beziehung
 Ein Subtyp ist existenzabhängig von seinem Supertyp (ON DELETE CASCADE).
Disjunkte/nicht disjunkte Subtypen
Zwei Subtypen E1 und E2 eines Supertyps E heißen disjunkt (d), wenn gilt: E1  E2 =  ,
sonst nicht disjunkt (n)
Totale/Partielle Beziehungen
Eine IS­A­Beziehung zwischen zwei Subtypen E1 und E2 zu einem Supertyps E heißt
total (t), wenn gilt: E1  E2 = E, sonst partiell (p).
Datenbanken
­ 35 ­
Beispiel
Person
d,t
IS­A
Frau
Mann
Boot
n,p
IS­A
Segelboot
Motorboot
Fahrzeug
d,p
IS­A
Auto
Fahrrad
Sänger
n,t
IS­A
Tenor
Bassist
Datenbanken
­ 36 ­
Abbildung von IS-A-Beziehungen
1. nicht disjunkte, partielle IS-A-Beziehungen
E
n,p
IS­A
E1
E
E1
E2
E2
S
(NOT NULL)
A
PK S
S
(NOT NULL)
A1
PK S
FK S REF E ON DELETE CASCADE
S
(NOT NULL)
A2
PK S
FK S REF E ON DELETE CASCADE
Datenbanken
­ 37 ­
2. disjunkte, partielle IS-A-Beziehungen
E
d,p
IS­A
E1
E
E1
E2
E2
S
(NOT NULL)
A
PK S
S
(NOT NULL)
A1
PK S
FK S REF E ON DELETE CASCADE
IB CHECK(S NOT IN (SELECT S FROM E2))
S
(NOT NULL)
A2
PK S
FK S REF E ON DELETE CASCADE
IB CHECK(S NOT IN (SELECT S FROM E1))
Datenbanken
­ 38 ­
3. disjunkte, totale IS-A-Beziehungen
E1
d,t
IS­A
E1
E
E2
S
(NOT NULL)
A
Z
NOT NULL
A1
A2
PK S
IB CHECK Z IN ('E1','E2')
IB CHECK (Z<>'E1' OR A2 IS NULL)
IB CHECK (Z<>'E2' OR A1 IS NULL)
Datenbanken
­ 39 ­
4. nicht disjunkte, totale IS-A-Beziehungen
E
n,t
IS­A
E1
E
E2
S
(NOT NULL)
A
Z
(NOT NULL)
A1
A2
PK S, Z
IB CHECK Z IN ('E1','E2')
IB CHECK (Z<>'E1' OR A2 IS NULL)
IB CHECK (Z<>'E2' OR A1 IS NULL)
IB Eindeutigkeit von A zu S