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 ) : TargetListe,
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), AAttributmenge
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,BAttributmengen
 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ülDatenbankanfrage:
SQLDatenbankanfrage:
SELECT
FROM
WHERE
{ (t1 , ... , t n ) : F }
mit (t1 , ... , t n ) : TargetListe,
F : Formel
select-list
table-reference
search-condition
Abbildung SQLKalkül
TargetListe
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
nichtkorreliert
korreliert
 ungeschachtelte Anfrage
Anfrage ohne innere SELECTAnweisung (Subanfrage)
 Anfrage mit nichtkorrelierter 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 WHEREBedingung 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 nichtkorrelierter 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
 INSERTTrigger
 UPDATETrigger
 DELETETrigger
1. nach dem Aktivierungszeitpunkt des Triggers
 BeforeTrigger
 AfterTrigger
1. nach der Granularität des Triggers
 ZeilenTrigger
 AnweisungsTrigger
(Hinweis: in DB2 dürfen BeforeTrigger keine AnweisungsTrigger sein)
 12 
Datenbanken
 13 
2.1 Erzeugen und Löschen
Syntax
triggerdefinition ::=
CREATE TRIGGER triggername
[ NO CASCADE BEFORE | AFTER ] triggerevent ON tablename
[ REFERENCING referencingspecification ]
FOR EACH [ STATEMENT | ROW ] MODE DB2SQL1
[ WHEN (searchcondition) ]
triggeredaction
triggerevent ::=
INSERT
| DELETE
| UPDATE [ OF columnname [{ , columnname }...] ]
referencingspecification ::=
OLD [AS] rangevariable
| NEW [AS] rangevariable
| OLD_TABLE [AS] tableidentifier
| NEW_TABLE [AS] tableidentifier
| referencingspecification referencingspecification2
triggeredaction ::=
triggeredstatement
| BEGIN ATOMIC triggeredstatement; [{ triggeredstatement; }...] END
 BEFORE/AFTER: legt Aktivierungszeitpunkt fest
 triggerevent ON tablename: INSERT, DELETE oder UPDATEOperationen (auslösen
de Ereignisse) auf eine Relation tablename
 FOR EACH STATEMENT / FOR EACH ROW: Granularität des Triggers
Einschränkung für DB2: BeforeTrigger dürfen keine Anweisungstrigger sein
 triggeredaction: eine einzelne Anweisung (triggeredstatement) 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 searchcondition: TriggerAktion 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 rangevariable: Tupelvariable repräsentiert ein durch ein Ereignis zu modifizieren
des Tupel vor der Modifikation ('altes Tupel')
NEW rangevariable: Tupelvariable repräsentiert ein durch ein Ereignis zu modifizie
rendes Tupel nach der Modifikation ('neues Tupel')
OLD_TABLE tableidentifier: Name einer (hypothetischen) Relation, die alle zu modi
fizierenden Tupel vor der Modifikation enthält ('alte Relation')
NEW_TABLE tableidentifier: 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
droptriggerstatement ::=
DROP TRIGGER triggername
Datenbanken
 15 
2.2 Spezielle Trigger-Aktionen
SET-Anweisung
Syntax
setstatement ::=
SET columnname = rowvalueconstructor [{ , columnname = rowvalueconstructor}...]
 entspricht der SETKlausel einer UPDATEAnweisung
 columnname: Attribut des 'neuen Tupels' eines INSERT oder UPDATETriggers
 jeder Trigger, der eine SETAnweisung enthält, muß eine NEW rangevariable 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
signalstatement ::=
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)
triggeredstatement::=
Datenbanken
 16 
setstatement
| signalstatement
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 BildTupels 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
 AfterTrigger werden erst dann ausgeführt, wenn die auslösende Manipulationsanweisung
und alle kaskadierenden Folgeanweisungen (Integritätsbedingungen) erfolgreich ausgeführt
wurden
Syntax (After-Trigger)
triggeredstatement::=
insertstatement
| deletestatement
| updatestatement
| signalstatement
triggernde SQLAnweisung
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_Bilder1
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 TABLEBedingungen sind deskriptiver als TRIGGERBedingungen
 CREATE TABLEBedingungen werden zum Zeitpunkt ihrer Definition für alle existieren
den Daten durchgesetzt
 TRIGGERBedingungen werden nur bei Änderungen durch INSERT/UPDATE/DELETE
ausgelöst
 TRIGGER sind mächtiger als CREATE TABLEBedingungen
Datenbanken
 20 
Interaktionen zwischen CREATE TABLE-Bedingungen und Triggern
1. SQLAnweisung (triggerevent) produziert eine Änderungsliste mit den alten Werten
(OLD_ROW) und/oder den neuen Werten (NEW_ROW)
2. Die von der SQLAnweisung aktivierten BeforeTrigger 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 BeforeTrigger 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 SQLAnweisung gemischt. Integritätsbedin
gungen werden überprüft, Rücksetzen und Kaskadieren wie unter 3.
4. AfterTrigger, die von der ursprünglichen SQLAnweisung 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
tyTyp 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 EntityTyp E ist eine Generalisierung zweier EntityTypen 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 ISABeziehung genannt und im ERDiagramm dargestellt
durch
E
S
A
ISA
E1
E2
A1
A2
Folgerung
 Zwischen einem Subtyp und einem Supertyp herrscht eine 1:1Beziehung
 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 ISABeziehung 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
ISA
Frau
Mann
Boot
n,p
ISA
Segelboot
Motorboot
Fahrzeug
d,p
ISA
Auto
Fahrrad
Sänger
n,t
ISA
Tenor
Bassist
Datenbanken
 36 
Abbildung von IS-A-Beziehungen
1. nicht disjunkte, partielle IS-A-Beziehungen
E
n,p
ISA
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
ISA
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
ISA
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
ISA
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