Handout (zwei Seiten auf einer) - WI-Labor

Skript zur Vorlesung
Datenbanksysteme 2
Wintersemester 2011/2012
Christian Pape
01. September 2011
1 / 277
Einführung
Schedule
1. Veranstaltung
1. Veranstaltung
2. Veranstaltung
2. Veranstaltung
3. Veranstaltung
3. Veranstaltung
4. Veranstaltung
4. Veranstaltung
5. Veranstaltung
5. Veranstaltung
6. Veranstaltung
6. Veranstaltung
Weihnachtspause
6. Veranstaltung
7. Veranstaltung
7. Veranstaltung
8. Veranstaltung
Datum
06.10.2011
13.10.2011
20.10.2011
27.10.2011
03.11.2011
10.11.2011
17.11.2011
24.11.2011
01.12.2011
08.12.2011
15.12.2011
22.12.2011
29.12.2011
05.01.2012
12.01.2012
19.01.2012
26.01.2012
Übung
DB2
DB2
DB2
DB2
DB2
C
C
C
C
C
C
C
Vorlesung
DB2
DB2
DB2
C
DB2
C
C
C
C
C
C
DB2
DB2
DB2
DB2
C
DB2
DB2
DB2
C
2 / 277
Einführung
Inhalt
I
Relationenalgebra
I
I
I
I
I
I
Mengenoperationen
Kartesisches Produkt
Projektion, Selektion, Verbund, Division
Operatorbäume
Algebraische Optimierung
SQL
I
I
I
I
I
I
I
Infrastruktur HS-Fulda
SELECT-Syntax
Funktionen
Sub-Queries
Aggregatfunktionen
Gruppierung
Suchbedingungen
3 / 277
Einführung
Inhalt (2)
I
SQL (Fortsetzung)
I
I
I
I
I
I
I
I
I
I
I
Prädikate
NULL-Werte
Joins
Transitive Hülle
Datenmanipulation
Erzeugung Basisrelationen
Fremdschlüsselbeziehungen
Integritätsbedingungen
Sichten
Anwendungsprogrammierung
Datenkontrolle
I
I
I
Trigger
Zugriffskontrolle
Transaktionen
4 / 277
Einführung
Informationen
Informationen zu Vorlesung bzw. Übung:
http://wi-labor.informatik.hs-fulda.de/dbs2
5 / 277
Relationenalgebra
Relation
Eine Relation R ist eine Menge von Tupeln:
R = {t1 , t2 , . . . , tn } mit ti = (v1 , v2 , . . . , vr )
Dabei heißt r der Grad von R. Ausserdem definieren wir die
Attributmenge AR
Definitionsbereich dom(a)
R
AR
= {a1 , a2 , . . . , ar } und den
= {d1 , d2 , . . . , dm } mit a ∈ AR
= {(a, 0, 43), (x, 1, 36), (b, 1, 38)}
= {A, B, C }
dom(A)
= {0 a0 ,0 b 0 , . . . ,0 z 0 }
dom(B)
=
{0, 1}
dom(C )
=
N
R
A
a
x
b
B
0
1
1
C
43
36
38
6 / 277
Relationenalgebra
Vereinigung (Union)
R ∪S
= {t|t ∈ R ∨ t ∈ S}
Beispiel:
R = {(a, 1), (b, 2), (c, 3)}
S = {(c, 3), (d , 4)}
R ∪ S =?
7 / 277
Relationenalgebra
Vereinigung (Union)
R ∪S
= {t|t ∈ R ∨ t ∈ S}
Beispiel:
R = {(a, 1), (b, 2), (c, 3)}
S = {(c, 3), (d , 4)}
R ∪ S = {(a, 1), (b, 2), (c, 3), (d , 4)}
8 / 277
Relationenalgebra
Beispiel Vereinigung
R
A
a
d
b
B
x
y
z
C
1
2
3
R ∪S
S
A
b
d
B
x
y
C
3
2
A
a
d
b
b
B
x
y
z
x
C
1
2
3
3
9 / 277
Relationenalgebra
Durchschnitt (Intersect)
R ∩S
= {t|t ∈ R ∧ t ∈ S}
= R − (R − S)
Beispiel:
R = {(a, 1), (b, 2), (c, 3)}
S = {(c, 3), (d , 4)}
R ∩ S =?
10 / 277
Relationenalgebra
Durchschnitt (Intersect)
R ∩S
= {t|t ∈ R ∧ t ∈ S}
= R − (R − S)
Beispiel:
R = {(a, 1), (b, 2), (c, 3)}
S = {(c, 3), (d , 4)}
R ∩ S = {(c, 3)}
11 / 277
Relationenalgebra
Beispiel Durchschnitt
R
A
a
d
b
B
x
y
z
C
1
2
3
S
A
b
d
B
x
y
C
3
2
R ∩S
A
d
B
y
C
2
12 / 277
Relationenalgebra
Differenz
R −S
= {t|t ∈ R ∧ t ∈
/ S}
Beispiel:
R = {(a, 1), (b, 2), (c, 3)}
S = {(c, 3), (d , 4)}
R − S =?
13 / 277
Relationenalgebra
Differenz
R −S
= {t|t ∈ R ∧ t ∈
/ S}
Beispiel:
R = {(a, 1), (b, 2), (c, 3)}
S = {(c, 3), (d , 4)}
R − S = {(a, 1), (b, 2)}
14 / 277
Relationenalgebra
Beispiel Differenz
R
A
a
d
b
B
x
y
z
C
1
2
3
S
A
b
d
B
x
y
C
3
2
R −S
A
a
b
B
x
z
C
1
3
15 / 277
Relationenalgebra
Symmetrische Differenz
R4S
= (R ∪ S) − (R ∩ S)
= ((R ∪ S) − (R − (R − S)))
= {t|t ∈ R ⊕ t ∈ S}
Beispiel:
R = {(a, 1), (b, 2), (c, 3)}
S = {(c, 3), (d , 4)}
R4S =?
16 / 277
Relationenalgebra
Symmetrische Differenz
= (R ∪ S) − (R ∩ S)
R4S
= ((R ∪ S) − (R − (R − S)))
= {t|t ∈ R ⊕ t ∈ S}
Beispiel:
R = {(a, 1), (b, 2), (c, 3)}
S = {(c, 3), (d , 4)}
R4S = {(a, 1), (b, 2), (d , 4)}
17 / 277
Relationenalgebra
Beispiel Symmetrische Differenz
R
A
a
d
b
B
x
y
z
C
1
2
3
S
A
b
d
B
x
y
C
3
2
R4S
A
a
b
b
B
x
z
x
C
1
3
3
18 / 277
Relationenalgebra
Erweitertes Kartesisches Produkt
Sei
r der Grad von R, d.h. R = {(a1 , a2 , . . . , ar )} und
s der Grad von S, d.h. S = {(b1 , b2 , . . . , bs )} dann gilt
R × S = {(a1 , a2 , . . . , ar , b1 , b2 , . . . , bs ) | (a1 , a2 , . . . , ar ) ∈ R ∧
(b1 , b2 , . . . , bs ) ∈ S}
Beispiel:
R = {(a, 1), (b, 2), (c, 3)}
S = {(c, 3), (d , 4)}
R × S =?
19 / 277
Relationenalgebra
Erweitertes Kartesisches Produkt
Sei
r der Grad von R, d.h. R = {(a1 , a2 , . . . , ar )} und
s der Grad von S, d.h. S = {(b1 , b2 , . . . , bs )} dann gilt
R × S = {(a1 , a2 , . . . , ar , b1 , b2 , . . . , bs ) | (a1 , a2 , . . . , ar ) ∈ R ∧
(b1 , b2 , . . . , bs ) ∈ S}
Beispiel:
R = {(a, 1), (b, 2), (c, 3)}
S = {(c, 3), (d , 4)}
R ×S =
{(a, 1, c, 3), (a, 1, d , 4), (b, 2, c, 3), (b, 2, d , 4), (c, 3, c, 3), (c, 3, d , 4)}
20 / 277
Relationenalgebra
Beispiel Kartesisches Produkt
R
A
a
d
b
B
x
y
z
C
1
2
3
S
D
b
d
E
x
y
F
3
2
R ×S
A
a
a
d
d
b
b
B
x
x
y
y
z
z
C
1
1
2
2
3
3
D
b
d
b
d
b
d
E
x
y
x
y
x
y
F
3
2
3
2
3
2
21 / 277
Relationenalgebra
Projektion
Sei r der Grad von R und
Y = {ji |i ∈ {1, 2, . . . , k}, ji ∈ {1, 2, . . . , r }}, dann heißt
πY (R) die Projektion von R auf Y mit
πY (R) = {p[Y ]|p ∈ R}.
Dabei ist p[Y ] die Einschränkung des Tupels p auf die
Attributmenge Y .
22 / 277
Relationenalgebra
Beispiel Projektion
π1,2 (R)
R
NAME
Meier
Schneider
Weber
Schmidt
VORNAME
Karl
Herbert
Heinz
Anette
NAME
Meier
Schneider
Weber
Schmidt
ALTER
43
36
38
πALTER (R)
36
VORNAME
Karl
Herbert
Heinz
Anette
ALTER
43
36
38
23 / 277
Relationenalgebra
Selektion
Auswahl von Zeilen einer Relation R über Prädikate P. P ist eine
logische Formel (ohne Quantoren) zusammengestellt aus:
a.) Operanden (Konstanten/Spaltennummern
oder auch Attributnamen)
b.) θ ∈ {<, =, >, ≤, 6=, ≥}
c.) ∨, ∧, ¬
Dann ist die Selektion definiert als:
σP (R) = {t|∃t ∈ R ∧ P(t)}
24 / 277
Relationenalgebra
Beispiel Selektion
R
NAME
Meier
Schneider
Weber
Schmidt
VORNAME
Karl
Herbert
Heinz
Anette
σNAME =0 Meier 0 (R)
σALTER>36 (R)
NAME
Meier
NAME
Meier
Weber
ALTER
43
36
38
36
VORNAME
Karl
VORNAME
Karl
Heinz
ALTER
43
ALTER
43
38
25 / 277
Relationenalgebra
Verbund (Join)
Kartesisches Produkt zwischen zwei Relationen R (Grad r ) und S
(Grad s) eingeschränkt durch θ-Bedingung zwischen i-Spalte von R
und j-Spalte von S mit θ ∈ {<, =, >, ≤, 6=, ≥}.
Dann ist der Verbund definiert als:
R iθj S = σiθr +j (R × S)
Wenn θ dem Gleichheitsoperator entspricht, so spricht man auch
von einem Gleichheitsverbund (Equijoin).
26 / 277
Relationenalgebra
Beispiel Gleichverbund
R
A
a1
a2
B
b1
b2
C
c1
c2
S
D
c1
c3
E
d1
d2
F
e1
e2
R C =D S
A
a1
B
b1
C
c1
D
c1
E
d1
F
e1
27 / 277
Relationenalgebra
Verbund (Join)
Ein Gleichverbund zwischen zwei Relationen R und S heißt
verlustfrei, wenn alle Tupel von R und S am Verbund teilnehmen.
Die inverse Operation Projektion erzeugt dann wieder R und S
(lossless join).
28 / 277
Relationenalgebra
Intervall-Verbund (Band Join)
Anstatt einer θ-Bedingung wird hier eine Intervallbedingung
I = [c1 , c2 ] überprüft.
V
= R iIj S
= σiIj (R × S)
= σR.i−c1 ≤S.j≤R.i+c2 (R × S)
Ein Tupel aus S kombiniert mit einem Tupel aus R nur, wenn der
Wert der j-Spalte von S im Intervall der Größe c1 + c2 um den
Wert der i-Spalte von R liegt.
29 / 277
Relationenalgebra
Natürlicher Verbund (Natural Join)
Gleichverbund über alle gleichen Attribute und Ausblendung
gleicher Spalten.
R mit AR = {(a1 , . . . , ar −j+1 , . . . , ar )}
S mit AS = {(b1 , . . . , bj , . . . , bs )} mit
b1 = ar −j+1 , b2 = ar −j+2 , . . . , bj = ar
Dann gilt
R S = πa1 ,...,ar ,bj+1 ,...,bs σ(R.ar −j+1 =S.b1 )∧···∧(R.ar =S.bj ) (R × S)
30 / 277
Relationenalgebra
Beispiel Natürlicher Verbund
R
A
a1
a2
B
b1
b2
C
c1
c2
S
C
c1
c3
D
d1
d2
E
e1
e2
R S
A
a1
B
b1
C
c1
D
d1
E
e1
31 / 277
Relationenalgebra
Äußerer Verbund (Outer Join)
Verlustfreier Verbund soll erzwungen werden. Seien A die
Verbundattribute und ≡ der undefinierte Wert:
R 0 = R ∪ ((πA (S) − πA (R)) × {≡} × · · · × {≡})
S 0 = S ∪ ((πA (R) − πA (S)) × {≡} × · · · × {≡})
Äußerer Gleichverbund:
R][(R.A=S.A) S = R 0 (R 0 .A=S 0 .A) S 0
Äußerer natürlicher Verbund:
R][S = R 0 S 0
32 / 277
Relationenalgebra
Beispiel Äußerer Verbund
R
A
a1
a2
B
b1
b2
C
c1
c2
S
C
c1
c3
D
d1
d2
E
e1
e2
R][S
A
a1
a2
≡
B
b1
b2
≡
C
c1
c2
c3
D
d1
≡
d2
E
e1
≡
e2
33 / 277
Relationenalgebra
Linker äußerer Verbund (Left Outer Join)
Bei dieser Operation bleibt die linke Relation verlustfrei, d.h. bei
Bedarf wird ein Tupel durch den ≡-Wert ergänzt.
R](R.A=S.A) S = R (R.A=S 0 .A) S 0
34 / 277
Relationenalgebra
Beispiel Linker äußerer Verbund
R
A
a1
a2
B
b1
b2
C
c1
c2
S
C
c1
c3
D
d1
d2
E
e1
e2
R] S
A
a1
a2
B
b1
b2
C
c1
c2
D
d1
≡
E
e1
≡
35 / 277
Relationenalgebra
Rechter äußerer Verbund (Right Outer Join)
Bei dieser Operation bleibt die rechte Relation verlustfrei, d.h. bei
Bedarf wird ein Tupel durch den ≡-Wert ergänzt.
R [(R.A=S.A) S = R 0 (R 0 .A=S.A) S
36 / 277
Relationenalgebra
Beispiel Rechter äußerer Verbund
R
A
a1
a2
B
b1
b2
C
c1
c2
S
C
c1
c3
D
d1
d2
E
e1
e2
R [S
A
a1
≡
B
b1
≡
C
c1
c3
D
d1
d2
E
e1
e2
37 / 277
Relationenalgebra
Division
Beantwortung von Fragen, bei denen eine ganze Relation zur
Qualifikation herangezogen wird. Ein Tupel aus R steht mit allen
Tupeln aus S in einer bestimmten Beziehung. Sei R vom Grad r
und S vom Grad s mit s 6= 0 und AS ⊂ AR . Dann gilt:
R ÷ S = {(t1 , . . . , tr −s )|∀u ∈ S : (t1 , . . . , tr −s , u1 , . . . , us ) ∈ R}
38 / 277
Relationenalgebra
Division (Beschreibung mit Grundoperationen)
T = π1,2,...,r −s (R)
W = (T × S) − R
V = π1,2,...,r −s (W )
R ÷S =T −V
= π1,2,...,r −s (R) − π1,2,...,r −s ((π1,2,...,r −s (R) × S) − R)
Es gilt:
(R × S) ÷ S = R
39 / 277
Relationenalgebra
Beispiel Division
Frage: Welcher Lieferant liefert alle Teile?
LIEF
TEIL
LNR
L1
L1
L2
L3
L3
L3
TNR
T1
T2
T2
T1
T2
T3
TNR
T1
T2
T3
TNAME
NAGEL
BOLZEN
MUTTER
πTNR (TEIL)
TNR
T1
T2
T3
LIEF ÷ (πTNR (TEIL))
LNR
L3
GEWICHT
12
12
15
40 / 277
Relationenalgebra
Operatorbaum
Finde alle Schauspieler (NAME, W-ORT), die bei in Weimar
uraufgeführten Dramen an ihrem Wohnort als "Held" mitgespielt
haben.
=πNAME ,W −ORT (σW −ORT =A−ORT (SCHAUSPIELER (
πPNR,A−ORT (DARSTELLER (πFIGUR (
(σGESCHLECHT =0 HELD 0 ROLLE ) (σU−ORT =0 WEIMAR 0 DRAMA)))))))
41 / 277
Relationenalgebra
Operatorbaum
πNAME ,W −ORT
σW −ORT =A−ORT
πPNR,A−ORT
πFIGUR
SCHAUSPIELER
DARSTELLER
σR−GESCHLECHT =0 HELD 0
σU−ORT =0 WEIMAR 0
ROLLE
DRAMA
42 / 277
Relationenalgebra
Rewrite Regeln (1)
1. Kommutatives Gesetz für Verbunde und Produkte
R1 F R2 = R2 F R1
R1 R2 = R2 R1
R1 × R2 = R2 × R1
2. Assoziatives Gesetz für Verbunde und Produkte
(R1 F 1 R2) F 2 R3 = R1 F 1 (R2 F 2 R3)
(R1 × R2) × R3 = R1 × (R2 × R3)
43 / 277
Relationenalgebra
Rewrite Regeln (2)
3. Folgen von Projektionen
πa1 ,...,ai (πa1 ,...,ai ,b1 ,...,bj (R)) = πa1 ,...,ai (R)
4. Folgen von Selektionen
σF 1 (σF 2 (R)) = σF 1∧F 2 (R)
Wegen F 1 ∧ F 2 = F 2 ∧ F 1 folgt dann auch
σF 1 (σF 2 (R)) = σF 2 (σF 1 (R))
44 / 277
Relationenalgebra
Rewrite Regeln (3)
5. Vertauschung von Projektionen und Selektionen
F enthält nur Attribute aus a1 , . . . , ai
σF (πa1 ,...,ai (R)) = πa1 ,...,ai (σF (R))
F enthält zusätzlich Attribute aus b1 , . . . , bj
πa1 ,...,ai (σF (R)) = πa1 ,...,ai (σF (πa1 ,...,ai ,b1 ,...,bj (R))
45 / 277
Relationenalgebra
Rewrite Regeln (4)
6. Vertauschung von Selektionen und Kartesischem Produkt
F enthält nur Attribute aus R1
σF (R1 × R2) = σF (R1) × R2
Allgemeiner:
F = F1 ∧ F2 ∧ F3
F 1 nur Attribute aus R1
F 2 nur Attribute aus R2
F 3 Attribute aus beiden
σF (R1 × R2) = σF 1 (R1) F 3 σF 2 (R2)
46 / 277
Relationenalgebra
Algebraische Optimierung
Problem: gegebener Ausdruck in der Relationenalgebra
Ziel: äquivalenter, möglichst effizient auszuführender
Ausdruck der Relationenalgebra
Bestimmung einer möglichst guten Ausführungsreihenfolge durch
die Beachtung von Heuristiken für:
I
unäre Operationen π, σ
I
binäre Operationen ∪, ∩, −, ×, 47 / 277
Relationenalgebra
Ausgangslösung
Finde Name und Beruf von Angestellten, deren
Abteilung in KL ist und die Projekte in KL durchführen.
ERGEBNIS
N
200
πNAME ,BERUF
ABT(ANR,BUDGET,A-ORT)
PERS(PNR,NAME,BERUF,GEHALT,ALTER,ANR)
PM(PNR,JNR,DAUER,ANTEIL)
PROJ(JNR,BEZEICHNUNG,SUMME,P-ORT)
N
200
Annahmen:
N
5
ABT:
I
PERS: N Tupel
I
PM: 5 · N Tupel
· 14
σA−ORT =0 KL0
Tupel
5 · N · 14
5 · N · 11
I
PROJ: M Tupel
I
Gleichverteilung der Attributwerte:
A-ORT: 10 Werte
P-ORT: 100 Werte
N
5
ABT
· 14
σP−ORT =0 KL0
N
2
I
·2
·3
M ·4
N ·8
5·N ·4
N ·6
PERS
PM
PROJ
48 / 277
Relationenalgebra
1. Verschieben von Selektionen
ERGEBNIS
N
200
·2
πNAME ,BERUF
N
200
N
2
· 14
· 11
Führe Selektionen so früh wie möglich aus!
N
10
N
50
·3
M
100
·4
N ·5·4
N ·6
σA−ORT =0 KL0
N
5
·8
σP−ORT =0 KL0
·3
M ·4
ABT
PERS
PM
PROJ
49 / 277
Relationenalgebra
2. Verschieben von Projektionen
ERGEBNIS
N
200
·2
πNAME ,BERUF
N
200
N
2
·5
·5
Führe Projektionen frühzeitig aus!
N
10
N
50
ABT
·3
·1
N ·5·2
πJNR
M
100
·3
σA−ORT =0 KL0
N
5
M
100
N ·4
πANR
N
50
·1
·4
πPNR,ANR,NAME ,BERUF
N ·6
PERS
πPNR,JNR
N ·5·4
PM
·4
σP−ORT =0 KL0
M ·4
PROJ
50 / 277
Relationenalgebra
3. Verknüpfung von Folgen unärer Operationen
ERGEBNIS
N
200
·2
πNAME ,BERUF
N
200
N
2
·7
·7
Verknüpfen von Selektion und Projektion!
N
10
N
50
·1
M
100
·1
N ·5·2
N ·6
πANR (σA−ORT =0 KL0 )
N
5
·6
πPNR,JNR
·3
πJNR (σP−ORT =0 KL0 )
N ·5·4
ABT
PERS
PM
M ·4
PROJ
51 / 277
Relationenalgebra
4. Zusammenfassung von Selektionen
ERGEBNIS
T
σGEHALT >50.000∧BERUF =0 Programmierer 0
Alle Programmierer mit mehr
als 50K Gehalt, und zwar aus
den Abteilungen K51 und K55.
PERS
S
σANR=0 K 510
σANR=0 K 550
PERS
PERS
ERGEBNIS
σGEHALT >50.000∧BERUF =0 Programmierer 0 ∧(ANR=0 K 510 ∨ANR=0 K 550 )
PERS
52 / 277
Relationenalgebra
5. Einmalige Berechnung gemeinsamer Teilbäume
Voraussetzung:
Zwischenspeicherung der Ergebnisse ist nicht zu ”teuer”.
ERGEBNIS
ERGEBNIS
πANR,A−ORT (σA−ORT =0 F 0 )
ABT
πANR,A−ORT (σA−ORT =0 F 0 )
πPNR,NAME ,ANR
πANR,A−ORT (σA−ORT =0 F 0 )
πPNR,NAME ,ANR
ABT
PERS
ABT
PERS
53 / 277
Relationenalgebra
6. Kombination von Verbundoperationen
Ausnutzung der Assoziativität und Kommutativität von
Vereinigung, Durchschnitt und Verbund zur Optimierung.
Beispiel: Seien R und S zwei Relationen und T = R S.
Annahmen: Verlustfreier Verbund, j entspricht der Anzahl der
gleichverteilten Werte des Verbundattributes
Erwartungswerte: Jedes Tupel aus S wird mit |R|
j Tupeln aus R
verbunden.
(n:m) Verbund: |R| > j ⇒ |T | = |S| · |R|
j
(1:n) Verbund: |R| = j ⇒ |T | = |S|
Die Verbundreihenfolge sollte so gewählt werden, dass die Anzahl
und Größe der Zwischenobjekte minimiert wird.
54 / 277
Relationenalgebra
6. Zusammenfassung von Verbundoperationen
ERGEBNIS
N
200
·2
πNAME ,BERUF
N
200
N
10
N
50
·4
N
20
·2
·1
N ·4
πANR (σA−ORT =0 KL0 )
N
5
·5
N ·5·2
πPNR,ANR,NAME ,BERUF
·3
N ·6
ABT
πPNR,JNR
M
100
·1
πJNR (σP−ORT =0 KL0 )
N ·5·4
PERS
M ·4
PM
PROJ
55 / 277
Relationenalgebra
7. Reihenfolge von Mengenoperationen
Kardinalität der Vereinigung:
R1
R1
R2
R2
max(|R1|, |R2|) ≤ |R1 ∪ R2| ≤ |R1| + |R2|
Kardinalität des Durchschnitts:
R1
R1
R2
R2
0 ≤ |R1 ∩ R2| ≤ min(|R1|, |R2|)
56 / 277
Relationenalgebra
7. Reihenfolge von Mengenoperationen (2)
Erwartung:
R1
R2
Bei Mengenoperationen immer zuerst die kleinsten Relationen
verknüpfen.
57 / 277
Relationenalgebra
8. Kartesisches Produkt mit Selektion
Verknüpfung bestimmter Selektionen mit vorausgehenden
kartesischen Produkt zu einem Verbund.
σA=B
×
R
A=B
S
A,B Verbundattribute
⇒
R
S
58 / 277
Relationenalgebra
Zusammenfassung Algebraische Optimierung
1. Führe Selektionen so früh wie möglich aus
2. Führe Projektion frühzeitig aus
3. Verknüpfe Folgen von unären Operatoren wie Projektion
und Selektion
4. Fasse einfache Selektionen zusammen
5. Berechne gemeinsame Teilbäume nur einmal
6. Bestimme Verbundreihenfolge so, daß die Anzahl und
Größe der Zwischenobjekte minimiert wird
7. Verknüpfe bei Mengenoperationen immer zuerst die
kleinsten Relationen
8. Verknüpfe bestimmte Selektionen mit einem
vorausgehenden kartesischen Produkt zu einem Verbund
59 / 277
Relationenalgebra
Zusammenfassung (Mengenoperationen)
I
∪ Vereinigung
I
∩ Durchschnitt
I
− Differenz
I
4 Symmetrische Differenz
60 / 277
Relationenalgebra
Zusammenfassung (Projektion/Selektion/Division)
I
Projektion π: Einschränkung von Tupeln auf eine
Attributmenge, d.h. Ausblendung von Spalten
I
Selektion σ: Auswahl von Tupeln anhand eines Prädikates
bzw. eines logischen Ausdrucks
I
Division ÷: Auswertung einer Beziehung eines Tupels zu einer
gesamten Relation
61 / 277
Relationenalgebra
Zusammenfassung (Produkt/Verbund)
I
I
Kartesisches Produkt ×, Bildung aller möglichen
Kombinationen von Tupeln aus zwei Mengen
Verbunde
I
I
I
I
Allgemein: Kartesisches Produkt zweier Mengen eingeschränkt
durch eine Vergleichsbedingung zwischen zwei Attributen
Gleichheitsverbund: Spezialfall, wenn die Vergleichsbedingung
dem Gleichheitsoperator (=) entspricht
Natürlicher Verbund: Verbund, bei dem alle gleich benannten
Attribute zur Kombination herangezogen werden
Äußerer Verbund: verlustfreier Verbund, bei dem nicht
kombinierbare Tupel durch NULL-Werte (≡) ergänzt werden
62 / 277
Relationenalgebra
Beispiele
a.) Welche Schauspieler (PNR) haben im Pfalztheater gespielt?
63 / 277
Relationenalgebra
Beispiele
a.) Welche Schauspieler (PNR) haben im Pfalztheater gespielt?
A = πPNR (σTHEATER=0 Pfalztheater 0 (DARSTELLER))
A
PNR
4
5
64 / 277
Relationenalgebra
Beispiele
b.) Welche Dramen von Schiller wurden nach 1800 uraufgeführt?
65 / 277
Relationenalgebra
Beispiele
b.) Welche Dramen von Schiller wurden nach 1800 uraufgeführt?
B = πTITEL (σAUTOR=0 Schiller 0 ∧U_JAHR>1800 (DRAMA))
B
TITEL
Tell
66 / 277
Relationenalgebra
Beispiele
c.) Welche Schauspieler (NAME, W_ORT) haben im ’Faust’
mitgespielt?
67 / 277
Relationenalgebra
Beispiele
c.) Welche Schauspieler (NAME, W_ORT) haben im ’Faust’
mitgespielt?
C = πNAME ,W _ORT (SCHAUSPIELER (DARSTELLER (σTITEL=0 Faust 0 (ROLLE ))))
C
NAME
Schön
Müller
Schiller
W_ORT
Frankfurt
Frankfurt
Jena
68 / 277
Relationenalgebra
Beispiele
d.) Welche Schauspieler (NAME, W_ORT) haben in Dramen von
Schiller mitgespielt?
69 / 277
Relationenalgebra
Beispiele
d.) Welche Schauspieler (NAME, W_ORT) haben in Dramen von
Schiller mitgespielt?
D = πNAME ,W _ORT (SCHAUSPIELER(DARSTELLER
(ROLLE (σAUTOR=0 Schiller 0 (DRAMA)))))
D
NAME
Schön
Müller
Schiller
George
W_ORT
Frankfurt
Frankfurt
Jena
Berlin
70 / 277
Relationenalgebra
Beispiele
e.) Welche Schauspieler (PNR) sind nie aufgetreten?
71 / 277
Relationenalgebra
Beispiele
e.) Welche Schauspieler (PNR) sind nie aufgetreten?
E = (πPNR (SCHAUSPIELER)) − (πPNR (DARSTELLER))
E
PNR
3
6
7
8
72 / 277
Relationenalgebra
Beispiele
f.) Zeichnen Sie den Operatorbaum für folgende Anfrage:
πNAME (SCHAUSPIELER (πPNR,FIGUR (DARSTELLER) ÷ πFIGUR (ROLLE )))
73 / 277
Relationenalgebra
Beispiele
f.) Zeichnen Sie den Operatorbaum für folgende Anfrage:
πNAME (SCHAUSPIELER (πPNR,FIGUR (DARSTELLER) ÷ πFIGUR (ROLLE )))
πNAME
÷
SCHAUSPIELER
πPNR,FIGUR
πFIGUR
DARSTELLER
ROLLE
74 / 277
Infrastruktur
Datenbankserver
I
mtsthelens
I
krakatau
I
pinatubo
75 / 277
Infrastruktur
mtsthelens.informatik.hs-fulda.de
Plattform: Sun Fire V445
Betriebssystem: Solaris 10
Datenbank: Oracle 10g
SID: ORALV9A
Kapazität: ca. 584 GB
76 / 277
Infrastruktur
krakatau.informatik.hs-fulda.de
Plattform: FSC Primergy
200TX
Betriebssystem: Solaris 10 x86
Datenbank: Oracle 10g
SID: ORALV10A
Kapazität: ca. 60 GB
77 / 277
Infrastruktur
pinatubo.informatik.hs-fulda.de
Plattform: FSC Primergy
200TX
Betriebssystem: Solaris 10 x86
Datenbank: Oracle 10g
SID: ORALV8A
Kapazität: ca. 60 GB
78 / 277
Infrastruktur
Möglichkeiten des Zugriffs
I
Oracle Client (Windows, Unix, Mac PPC)
I
Oracle SQL Developer (Windows, Unix, Mac PPC + Intel)
I
iSQL*Plus (Zugang über Webbrowser)
79 / 277
Infrastruktur
Oracle Client
I
Beinhaltet SQLPlus und
SQLPlus Worksheet
I
Benötigt TNSNAMES.ORA
Anleitung unter
http://wi-labor.informatik.hs-fulda.de
80 / 277
Infrastruktur
Oracle SQL Developer
I
Verfügbar auch für
Mac OS X Intel
81 / 277
Infrastruktur
Oracle iSQL*Plus
I
Zugriff auf Datenbanken
über Webbrowser
Zugang über
http://isqlplus.informatik.hs-fulda.de
82 / 277
SQL
SELECT
SelectExpression::=
SELECT [ALL|DISTINCT] SelectItemCommaList
FROM TableReferenceCommalist
[WHERE ConditionExpression]
[GROUP BY ColumnReferenceCommaList
[HAVING ConditionExpression]]
[ORDER BY ColumnReferenceCommaList]
83 / 277
SQL
SELECT
SelectExpression
SELECT ALL DISTINCT TableReferenceCommaList
GROUP
ORDER
BY
BY
SelectItemCommaList
FROM WHERE ColumnReferenceCommaList
ColumnReferenceCommaList
ConditionExpression
HAVING ConditionExpression
84 / 277
SQL
SELECT (2)
I
Mit SELECT * werden komplette Tupel ausgewählt
I
FROM-Klausel spezifiziert das Objekt (Relation, Sicht), das
durch SELECT verarbeitet werden soll
I
WHERE-Klausel kann eine Sammlung von Prädikaten enthalten,
die mit AND oder OR verknüpft sein können
I
Prädikate (Verbundterme) können sich folgendermaßen
zusammensetzen:
Ai θan ,θ ∈ {=, ! =, <, <=, >, >=}
Ai θAj ,θ ∈ {=, ! =, <, <=, >, >=}
85 / 277
SQL
SELECT *
Finde alle Dichter (mit allen Attributen):
SELECT * FROM DICHTER
AUTOR
Schiller
Goethe
Kleist
Lessing
G_ORT
Marbach
Frankfurt
Kamenz
Frankfurt
G_JAHR
1759
1749
1777
1729
86 / 277
SQL
SELECT Kartesisches Produkt
Finde alle Kombinationen Dichtern und Dramen mit allen
Attributen (d.h. Kartesisches Produkt):
SELECT * FROM DICHTER, DRAMA
AUTOR
Schiller
Goethe
Kleist
Lessing
Schiller
Goethe
Kleist
Lessing
Schiller
Goethe
..
.
G_ORT
Marbach
Frankfurt
Kamenz
Frankfurt
Marbach
Frankfurt
Kamenz
Frankfurt
Marbach
Frankfurt
..
.
G_JAHR
1759
1749
1777
1729
1759
1749
1777
1729
1759
1749
..
.
TITEL
Maria Stuart
Maria Stuart
Maria Stuart
Maria Stuart
Wallenstein
Wallenstein
Wallenstein
Wallenstein
Tell
Tell
..
.
U_ORT
Weimar
Weimar
Weimar
Weimar
Jena
Jena
Jena
Jena
Weimar
Weimar
..
.
U_JAHR
1800
1800
1800
1800
1799
1799
1799
1799
1804
1804
..
.
AUTOR
Schiller
Schiller
Schiller
Schiller
Schiller
Schiller
Schiller
Schiller
Schiller
Schiller
..
.
87 / 277
SQL
WHERE Bedingung
Welche Dramen (TITEL) wurden weder in Weimar noch in
Frankfurt uraufgeführt?
SELECT TITEL FROM DRAMA WHERE U_ORT!=’Weimar’
AND U_ORT!=’Frankfurt’
. . . oder auch:
SELECT TITEL FROM DRAMA WHERE NOT
(U_ORT=’Weimar’ OR U_ORT=’Frankfurt’)
TITEL
Wallenstein
Iphigenie
Nathan
88 / 277
SQL
SELECT Beispiel
Welche Dramen von Schiller wurden nach 1800 uraufgeführt?
SELECT TITEL FROM DRAMA WHERE AUTOR=’Schiller’
AND U_JAHR>1800
TITEL
Tell
89 / 277
SQL
SELECT DISTINCT
Welche Schauspieler (PNR) haben am Schloßtheater gespielt?
Jeder Schauspieler soll nur einmal ausgegeben werden!
SELECT DISTINCT PNR FROM DARSTELLER WHERE
THEATER=’Schloßtheater’
PNR
2
I Duplikate werden in der Ausgabeliste normalerweise nicht eliminiert
I DISTINCT erzwingt Duplikateleminierung
90 / 277
SQL
SELECT Benennung von Ergebnisspalten
I
Ausgabe von Attributen, Text oder Ausdrücken durch SELECT
I
Spalten der Ergebnisrelation können umbenannt werden
I
Spaltennamen sensitiv für Groß- bzw. Kleinschreibung wenn in
Anführungszeichen eingeschlossen ("Ort" 6= "ORT")
SELECT TITEL AS "Titel", ’uraufgeführt in:’
AS TEXT, U_ORT AS "Ort", ’im Jahr:’ AS TEXT,
U_JAHR-1800 AS AUSDRUCK, ’nach 1800’ AS TEXT
FROM DRAMA WHERE AUTOR=’Schiller’
Titel
Maria Stuart
Wallenstein
Tell
TEXT
uraufgeführt in:
uraufgeführt in:
uraufgeführt in:
Ort
Weimar
Jena
Weimar
TEXT
im Jahr:
im Jahr:
im Jahr:
AUSDRUCK
0
-1
4
TEXT
nach 1800
nach 1800
nach 1800
91 / 277
SQL
SELECT Benennung von Ergebnisspalten
I
Konkatenation von Attributen und Text
SELECT TITEL || ’ wurde im Jahr ’ || U_JAHR ||
’ in ’ || U_ORT || ’ uraufgeführt.’ AS
"Überschrift" FROM DRAMA WHERE
AUTOR=’Schiller’
Überschrift
Maria Stuart wurde im Jahr 1800 in Weimar uraufgeführt.
Wallenstein wurde im Jahr 1799 in Jena uraufgeführt.
Tell wurde im Jahr 1804 in Weimar uraufgeführt.
92 / 277
SQL
Funktionen in Ausdrücken
Welche Dramen (TITEL, U_JAHR) wurden am Anfang eines
Jahrzehnts uraufgeführt?
SELECT TITEL, U_JAHR FROM DRAMA WHERE
MOD(U_JAHR,10)=0
. . . oder unter Benutzung der TRUNC-Funktion:
SELECT TITEL, U_JAHR FROM DRAMA WHERE
U_JAHR=TRUNC(U_JAHR/10)*10
. . . oder unter Benutzung der FLOOR-Funktion:
SELECT TITEL, U_JAHR FROM DRAMA WHERE
U_JAHR=FLOOR(U_JAHR/10)*10
TITEL
Maria Stuart
Faust
U_JAHR
1800
1790
93 / 277
SQL
Arithmetische Funktionen
Funktion
ABS(n)
Bedeutung
(Absolut-)Betrag
CEIL(n)
kleinste ganze Zahl größer oder gleich n
COS(n)
COSH(n)
EXP(n)
Kosinus
Kosinus hyperbolicus
Expontentialfunktion (Euler’sche Zahl e)
FLOOR(n)
größte ganze Zahl kleiner oder gleich n
LN(n)
Logarithmus naturalis
LOG(m,n)
Logarithmus (zur Basis m)
MOD(n,m)
Modulo (m)-Funktion, Rest bei
zahliger Division
Expotentialfunktion (zur Basis n)
POWER(n,m)
ganz-
ROUND(m[,m])
Rundung (auf m (>0) Stellen nach dem
Komma bzw.
auf Vielfache der m-ten
Potenz (m<0) von 10)
SIGN(N)
Signum (Vorzeichen)
SIN(n)
SINH(n)
SQRT(n)
TAN(n)
TANH(n)
TRUNC(n[,m])
Sinus
Sinus hyperbolicus
Quadratwurzel
Tangens
Tanges hyperbolicus
Abschneiden (bis auf m (>0) Stellen nach
dem Komma bzw. auf Vielfache der m-ten
Potenz (m<0) von 10)
Beispiele
ABS(-5)
ABS(33)
CEIL(-3.8)
CEIL(4.5)
COS(0)
COS(3.141592)
EXP(1)
EXP(-1)
FLOOR(-3.8)
FLOOR(-2)
LN(1)
LN(EXP(1))
LOG(5,125)
LOG(2,1024)
MOD(8,5)
MOD(10,3)
Ergebnisse
5
33
-3
5
1
-1
2.7182818
0.36787944
-4
-2
0
1
3
10
3
1
POWER(4,3)
POWER(12,2)
ROUND(3.75)
ROUND(3.75,1)
ROUND(-3.759
ROUND(-3.75,1)
ROUND(4771.3,2)
SIGN(3)
SIGN(0)
SIGN(-2)
SIN(0)
SINH(1.570796)
SQRT(36)
TAN(0)
TAN(0.785398163)
TRUNC(12.2082)
TRUNC(12.2082,2)
TRUNC(1209,-2)
64
144
4
3.8
-4
-3.8
4800
1
0
-1
0
1
6
0
1
12
12.2
1200
94 / 277
SQL
Zeichenketten-Funktionen
Funktion
ASCII(c)
CHR(n)
CONCAT(str1,str2)
INITCAP(str)
INSTR(str1,str2)
LENGTH(str)
LOWER(str)
LPAD(str,n[,padstr])
LTRIM(str[,trimset])
Bedeutung
Dezimalcode
(des
ASCII-Zeichens)
ASCII-Zeichen
(mit
dezimalen Code n)
Verkettung (Konkatenation)
zweier
Zeichenketten
Umsetzung der ersten
Buchstaben
jedes
Wortes in Großschreibung
Position
von
Zeichenkette
str2
als
Bestandteil
von
Zeichkette str1
Länge der Zeichenkette (str) in Zeichen
Umsetzung in Kleinbuchstaben
Zeichenkette str nach
links bis Maximallänge
n mit Zeichenkette
padstr auffüllen
Entfernen aller Zeichen (trimset) vom
Anfang der Zeichenkette str
Beispiele
ASCII(’7’)
ASCII(’N’)
CHR(55)
CHR(78)
Ergebnisse
55
78
CONCAT(’SQL’,’-Standard’)
CONCAT(’SQL’,CONCAT(’’,’Standard’))
INITCAP(’Aller Anfang ist schwer’)
INITCAP(’Aller*Anfang;ist+schwer’)
’SQL-Standard’
’SQL-Standard’
INSTR(’Pineapple’,’ea’)
INSTR(’Pineapple’,’P’)
INSTR(’bubblegum’,’BB’)
4
1
0
LENGTH(’potato’)
LENGTH(’bubblegum’)
LOWER(’SMALL
IS
FUL’)
LPAD(’Pad’,10,’_#’)
LPAD(’Pad’,10)
6
9
’7’
’N’
BEAUTI-
LTRIM(’bubblegum’,’b’)
LTRIM(’bubblegum’,’bu’)
’Aller Anfang Ist Schwer’
’Aller*Anfang;Ist+Schwer’
’small is beautiful’
’_#_#_#_Pad’
’
Pad’
’ubblegum’
’legum’
95 / 277
SQL
Zeichenketten-Funktionen (2)
Funktion
REPLACE(str,
search-str[,replstr])
Bedeutung
Suchen aller Vorkommen von search-str in
str und Ersetzen durch
repl-str, wenn spezifiziert; sonst Löschen
aller Vorkommen von
search-str in str
RPAD(str,n[,padstr]) Zeichenkette str nach
rechts bis Maximallänge n mit Zeichenkette padstr auffüllen
RTRIM(str[,trimset]) Entfernen aller Zeichen
trimset
vom
ende der Zeichenkette
str
SOUNDEX(str)
Phonetische
Repräsentation
von
str zum Vergleich mit
anderen Schreibweisen
SUBSTR(str,m[,n])
Bestimmung
einer
SUBSTRB(str,m[,n]) Teil-Zeichenkette
von str,
beginnend
mit dem m-ten Zeichen; der Länge n,
wenn angegeben; alle
restlichen
Zeichen,
wenn nicht spzifiziert
TRANSLATE(str,from-Zeichenweise Umsetset,to-set)
zung von str; kommt
ein zeichen c aus str
als i-tes Zeichen in
from-set vor, so wird
es in das i-te Zeichen
aus to-set umgesetzt
UPPER(str)
Umsetzung
in
Großbuchstaben
Beispiele
REPLACE(’banana’,’an’)
REPLACE(’banana’,’an’,’el’)
Ergebnisse
’ba’
’belela’
RPAD(’Pad’,10,’_#’)
RPAD(’Pad’,10)
’Pad_#_#_#_’
’Pad
’
RTRIM(’Risotto’,’o’)
RTRIM(’Risotto’,’ot’)
’Risott’
’Ris’
SOUNDEX(’trick’)
SOUNDEX(’trik’)
SOUNDEX(’trikk’)
’T620’
’T620’
’T620’
SUBSTR(’Pineapple’,5)
SUBSTR(’Pineapple’1,4)
SUBSTR(’Pineapple’,-4)
SUBSTR(’Pineapple’,-6,2)
’apple’
’Pine’
’pple’
’ea’
TRANSLATE(’xkhxcecbkm’,
’ehxvkc’, ’lmsbia’)
’simsalabim’
UPPER(’the big apple’)
UPPER(’the<big>apple’)
’THE BIG APPLE’
’THE<BIG>APPLE’
96 / 277
SQL
Datums- und Zeitfunktionen
Datentyp(en) zur Speicherung von Zeitgrößen
I
Oracle kennt nur einen Datentyp DATE der sowohl Datum als
auch Uhrzeit beinhaltet
I
Standard-SQL kennt DATE für ein Datum, TIME für eine
Uhrzeit und TIMESTAMP für einen Zeitstempel
I
der Oracle-Datentyp DATE ist weitestgehend äquivalent zum
Typ TIMESTAMP von Standard-SQL
Konvertierungs- und Formatierungsfunktionen
Zeitgröße → Zeichenkette:
TO_CHAR(time, format-string)
Zeichenkette → Zeitgröße:
TO_DATE(string, format-string)
97 / 277
SQL
Datums- und Zeitfunktionen (2)
Formatelement
-,/;:
"text"
AD
A.D.
AM
A.M.
BC
B.C.
CC
SCC
DAY
D
DD
DDD
DY
IW
IYY
IY
I
IYYY
HH
HH12
Bedeutung
einstreubarer Text
Beispiele
MM". Monat"
Ergebnisse
9. Monat
AD-Indikator (anno domini - nach Chr.)
YYYYAD
YYYYA.D.
HH:MIAM
HH:MIA.M.
YYYYBC
YYYYB.C.
CC". century"
1995AD
1995A.D.
10:30AM
10:30A.M.
1995BC
1995B.C.
20. century
DAY
D
DD
DDD
DY
IW". week of "YYYY
IYY
IY
I
IYYY
HH
HH12
FRIDAY
6
01
244
FRI
35. week of 95
995
95
5
1995
10
2
ante meridian - vor Mittag)
BC-Indikator (before Christ - vor Chr.)
Jahrhundert mit ’-’ für vor Chr.
ausgeschriebener Tag
Wochentag (1-7
Tag des Monats (1-31)
Tag des Jahres (1-366)
dreibuchstabiges Kürzel
Woche es Jahres (ISO)
letzte 3, 2 oder 1 Stellen des ISO-Jahres
ISO-Jahr
Stunde am Tag (1-12)
98 / 277
SQL
Datums- und Zeitfunktionen (3)
Formatelement
HH24
Bedeutung
Stunde am Tag (0-23)
Beispiele
HH24
J
MI
MM
MONTH
MON
RM
Q
RR
W
WW
PM
P.M.
SS
SSSSS
YEAR
SYEAR
YYYY
SYYYY
YYY
YY
Y
julianischer Tag (seit 01.01.4712 vor Chr.)
Minute (0-59)
Monat (1-12)
ausgeschriebener Monat
3-buchstabiges Kürzel
Monat (römische Zahl)
Quartal (1-4)
Notlösung für Jahr > 2000
Woche des Monats
Woche des Jahres
PM-Indikator (post meridian - nach Mittag)
Sekunde (0-59)
Sekunden am Tag (0-86399)
ausgeschriebene jahreszahl (v.Chr. mit ’-’)
J" julian day"
HH24:MI
DD-MM-YY
MONTH
DD-MON-YY
DD-RM-YY
Q". quarter "YYYY
Jahreszahl
Jahreszahl (v.Chr. mit ’-’)
letzte 3, 2 oder 1 Stellen der Jahreszahl
YYYY
SYYYY
YYY
YY
Y
W". week in"MON
WW
HH:MIPM
HH:MIP.M.
SS
SSSSS
YEAR
Ergebnisse
10
14
2449962 julian day
10:30
01-09-95
SEPTEMBER
01-SEP-95
01-IX-95
3. quarter 1995
1. week in SEP
35
10:30PM
10:30P.M.
00
37800
NINETEEN
NINETY-FIVE
1995
1995
995
95
5
99 / 277
SQL
Beispiel Datums- und Zeitfunktionen
Gib von allen Projekten, deren Nummer höchstens 4 ist, Nummer,
Projektbeginn und den wöchentlichen Termin der Projektsitzung
aus?
SELECT JNR, JBEGINN,
TO_CHAR(JSITZUNG,’HH24:MI’) AS JSITZUNG FROM
PROJ WHERE JNR<=4
JNR
1
2
3
4
JBEGINN
01-SEP-95
01-FEB-94
05-JUN-96
01-JAN-94
JSITZUNG
10:30
11:00
9:00
100 / 277
SQL
Beispiel Datums- und Zeitfunktionen (2)
Welche Projekte wurden vor dem 30.09.1995 beendet?
Projektnummer, Projektname und Projektende sollen unter den
genannten Überschriften ausgegeben werden. Das Projektende soll
in der Form Wochentag als Text, Bindestrich, Tag als Zahl, Punkt,
Monat als Zahl, Punkt, Jahr (vierstellige Zahl) ausgegeben werden.
SELECT JNR "Projektnummer", JNAME
"Projektname", TO_CHAR(JENDE,’DAY-DD.MM.YYYY’)
"Projektende" FROM PROJ WHERE JENDE <
TO_DATE(’30.09.1995’,’DD.MM.YYYY’)
Projektnummer
4
Projektname
ABC-Analyse
Projektende
TUESDAY-20.06.1995
101 / 277
SQL
Vergleich von Datums- und Zeittypen
I
Zeichenketten (z.B. ’30.09.1995’) müssen vor dem Vergleich
mit einem Zeitwert zunächst in den Typ DATE umgewandelt
werden.
I
Ein Vergleich von JENDE < ’30.09.1995’ führt zu falschen
Ergebnissen, da Zeichenkettentypen (CHAR, VARCHAR) einer
anderen Ordnung unterliegen als der Typ DATE.
102 / 277
SQL
Überprüfung eines Attributs mit einer Menge
Das Prädikat IN kann in einer WHERE-Klausel ein Attribut auf
Zugehörigkeit zu einer Menge prüfen.
I
Ai IN (a1 , . . . , ak )
I
Ai IN (SELECT ...)
Finde die Schauspieler (PNR, FIGUR), die Faust, Hamlet oder
Wallenstein gespielt haben.
SELECT PNR, FIGUR FROM DARSTELLER WHERE FIGUR
IN (’Faust’,’Hamlet’,’Wallenstein’)
PNR
1
1
2
FIGUR
Faust
Wallenstein
Faust
103 / 277
SQL
Überprüfung eines Attributs mit einer Menge (2)
Finde die Figuren, die in Dramen von Schiller oder Goethe
vorkommen.
SELECT FIGUR FROM ROLLE WHERE TITEL IN
(SELECT TITEL FROM DRAMA WHERE AUTOR IN
(’Goethe’,’Schiller’))
FIGUR
Faust
Mephisto
Gretchen
Wallenstein
Piccolomini
Tell
Geßler
Maria Stuart
Elisabeth
Iphigenie
104 / 277
SQL
Überprüfung eines Attributs mit einer Menge (3)
Finde die Schauspieler (PNR) die nie gespielt haben.
SELECT PNR FROM SCHAUSPIELER WHERE PNR NOT IN
(SELECT PNR FROM DARSTELLER)
PNR
3
6
7
8
105 / 277
SQL
Symmetrische Notation (Realisierung von Joins)
Finde die Schauspieler (NAME) aus Berlin und die Theater, an
denen sie gespielt haben.
SELECT NAME, THEATER
FROM SCHAUSPIELER, DARSTELLER WHERE
SCHAUSPIELER.PNR=DARSTELLER.PNR
AND W_ORT=’Berlin’
NAME
George
George
THEATER
Theater des Westens
Pfalztheater
Äquivalente vollqualifizierte Anfrage:
SELECT SCHAUSPIELER.NAME, DARSTELLER.THEATER
FROM SCHAUSPIELER, DARSTELLER WHERE
SCHAUSPIELER.PNR=DARSTELLER.PNR
AND SCHAUSPIELER.W_ORT=’Berlin’
106 / 277
SQL
Symmetrische Notation (Tupelvariablen)
Finde die Figuren und ihre Autoren, die in Dramen von Schiller und
Goethe vorkommen.
SELECT FIGUR, AUTOR FROM ROLLE R, DRAMA D
WHERE R.TITEL=D.TITEL AND AUTOR IN
(’Goethe’,’Schiller’)
FIGUR
Faust
Mephisto
Gretchen
Wallenstein
Piccolomini
Tell
Geßler
Maria Stuart
Elisabeth
Iphigenie
AUTOR
Goethe
Goethe
Goethe
Schiller
Schiller
Schiller
Schiller
Schiller
Schiller
Goethe
107 / 277
SQL
Beispiel Symmetrische Notation
Finde alle Schurken aus Dramen von Dichtern, die nicht in
Frankfurt geboren sind. Auszugeben sind die Figur, das
Uraufführungsjahr des Stückes, der Autor und dessen Geburtsjahr.
SELECT R.FIGUR, D.U_JAHR, A.AUTOR, A.G_JAHR
FROM ROLLE R, DRAMA D, DICHTER A
WHERE R.TITEL=D.TITEL
AND A.AUTOR=D.AUTOR
AND A.G_ORT<>’Frankfurt’
AND R.R_GESCHLECHT=’Schurke’
FIGUR
Piccolomini
Geßler
Elisabeth
U_JAHR
1799
1804
1800
AUTOR
Schiller
Schiller
Schiller
G_JAHR
1759
1759
1759
108 / 277
SQL
Korrelierende Sub-Queries
Finde die Mitarbeiter (PNR, PNAME, PALTER), die älter als ihre
Vorgesetzten sind.
SELECT P.PNR, P.PNAME, P.PALTER FROM PERS P
WHERE P.PALTER>(SELECT M.PALTER FROM PERS M
WHERE M.PNR=P.MNR)
PNR
PNAME
PALTER
102
Mohican
30
I pro Ausprägung (Tupel) der äußeren Abfrage wird die innere Anfrage genau
einmal ausgewertet.
I dabei werden die aktuellen Werte des Tupels der äußeren Anfrage als
Konstanten in die jeweilige innere Anfrage eingesetzt
I die tatsächliche Auswertungsstrategie wird durch den Anfrage-Optimierer
bestimmt
I Überdeckungen beachten!
109 / 277
SQL
Korrelierende Sub-Queries (2)
Finde die Projekte (JNR, JNAME, JMNR, JBEGINN, JENDE,
JSITZUNG), in denen der Projektmanager nicht zu 100 Prozent
mitarbeitet.
SELECT JNR, JNAME, JMNR, JBEGINN, JENDE,
JSITZUNG FROM PROJ WHERE PROJ.JMNR IN (SELECT
MITARB.PNR FROM MITARB WHERE
MITARB.JNR=PROJ.JNR AND
MITARB.ZEIT_ANTEIL!=100)
JNR
2
3
JNAME
NT 4.0
JMNR
100
100
JBEGINN
01-FEB-94
05-JUN-96
JENDE
JSITZUNG
01-MAY-97
110 / 277
SQL
Reihenfolge der Ausgabe / Sortierung
Gib Name, Alter und ANR aller Angestellten aufsteigend sortiert
nach Alter aus. Gleich alte Personen sollen aufsteigend nach ANR
erscheinen.
SELECT PNAME, PALTER, ANR FROM PERS ORDER BY
PALTER ASC, ANR ASC
PNAME
Gerneboss
Cherokee
Goldon
Mohican
Adlatus
Miniboss
Apache
Sioux
Bigboss
PALTER
25
25
28
30
30
40
40
40
50
ANR
55
55
100
55
56
45
45
45
56
I
Aufsteigende (ASC) und
absteigende (DESC)
Sortierung möglich
I
Mehrere Kriterien möglich
I
Spaltennamen und
positionale Angaben möglich
111 / 277
SQL
Reihenfolge der Ausgabe / Sortierung (2)
Gib Name, Gehalt, Bonus und Gesamtbezüge (Gehalt+Bonus)
aufsteigend sortiert nach Gesamtbezügen aus. Gleiche
Gesamtbezüge sollen absteigend nach dem Verhältnis von Bonus zu
Gehalt ausgegeben werden.
SELECT PNAME, PGEHALT, PBONUS, PGEHALT+PBONUS
"GESAMTBEZÜGE" FROM PERS ORDER BY 4,
PBONUS/PGEHALT DESC
oder auch:
SELECT PNAME, PGEHALT, PBONUS, PGEHALT+PBONUS
"GESAMTBEZÜGE" FROM PERS ORDER BY
PGEHALT+PBONUS ASC, PBONUS/PGEHALT DESC
112 / 277
SQL
Aggregatfunktionen
Bestimme die Anzahl, das Spitzengehalt, das durchschnittliche
Monatsgehalt aller Angestellten, sowie die Gesamtbezüge (Gehälte
plus Boni).
SELECT
COUNT(*) "Anzahl Angestellte",
MAX(PGEHALT) "Spitzengehalt",
AVG(PGEHALT/12) "mittleres Monatsgehalt",
SUM(PGEHALT+PBONUS) "Summe Bezüge"
FROM PERS
Anzahl Angestellte
9
Spitzengehalt
100000
mittleres Monatsgehalt
4953.7037
Summe Bezüge
610000
113 / 277
SQL
Aggregatfunktionen (2)
I
Built-In-Funktionen AVG, MAX und SUM werden angewendet
auf einstellige Ergebnislisten
I
COUNT(*) zählt die Anzahl der qualifizierten Tupel
I
per Default keine Eliminierung von Duplikaten (ALL)
I
mit DISTINCT werden Duplikate eliminiert
114 / 277
SQL
Aggregatfunktionen (3)
Bestimme die Anzahl aller Angestellten, die Anzahl
unterschiedlicher Gehaltswerte (Gehaltsstufen) und den Mittelwert
aller Gehaltsstufen.
SELECT
COUNT(*) "Anzahl Angestellte",
COUNT(DISTINCT PGEHALT) "Gehaltsstufen",
AVG(DISTINCT PGEHALT) "Mittelwert
Gehaltsstufen"
FROM PERS
Anzahl Angestellte
9
Gehaltsstufen
6
Mittelwert Gehaltsstufen
69166.667
115 / 277
SQL
Aggregatfunktionen (4)
Finde alle Angestellten (PNR, PNAME, PGEHALT), deren Gehalt
um mehr als 40% vom Durchschnittsgehalt der Firma abweicht.
SELECT PNR, PNAME, PGEHALT FROM PERS WHERE
PGEHALT<(SELECT 0.6*AVG(PGEHALT) FROM PERS) OR
PGEHALT>(SELECT 1.4*AVG(PGEHALT) FROM PERS)
PNR
1
300
PNAME
Bigboss
Goldon
PGEHALT
100000
85000
116 / 277
SQL
Partitionierung einer Relation in Gruppen
Liste alle Abteilungen (ANR) sowie das Durchschnittsgehalt ihrer
Angestellten auf.
SELECT ANR, AVG(PGEHALT) DURCHSCHNITTSGEHALT
FROM PERS GROUP BY ANR
ANR
45
55
56
100
DURCHSCHNITTSGEHALT
53333.333
50000
70000
85000
117 / 277
SQL
Partitionierung einer Relation in Gruppen (2)
Liste alle Abteilungen (ANR) sowie das Durchschnittsgehalt ihrer
Angestellten auf. zusätzlich soll der Abteilungsname angegeben
werden.
SELECT A.ANR ANR, A.ANAME ANAME, AVG(PGEHALT)
DURCHSCHNITTSGEHALT FROM PERS P, ABT A WHERE
P.ANR=A.ANR GROUP BY A.ANR, A.ANAME
ANAME muss als zweites Gruppierungsattribbut hinzugenommen
werden, da in der SELECT-Klausel nur Attribute aus der
GROUP-BY-Klausel, Konstanten oder Built-In-Funktionen
angegeben werden dürfen.
ANR
45
55
56
100
ANAME
Beschaffung
Produktion
Leitung
Vertrieb
DURCHSCHNITTSGEHALT
53333.333
50000
70000
85000
118 / 277
SQL
Auswahl von Gruppen
Liste die Abteilungen zwischen 50 und 60 auf, bei denen das
Durchschnittsalter ihrer Angestellten kleiner als 30 ist.
SELECT ANR, AVG(PALTER) DURCHSCHNITTSALTER
FROM PERS WHERE ANR<=60 AND ANR>=50 GROUP BY
ANR HAVING AVG(PALTER)<30
ANR
55
DURCHSCHNITTSALTER
26.666667
I HAVING-Klausel wird immer im Zusammenhang mit einer GROUP-BY-Klausel
verwendet
I die Bedingung wird auf Tupel einer Gruppe angewendet, d.h. muss für die
gesamte Gruppe erfüllt sein
I Bedingung mit gruppenweise auswertbar sein, d.h. Gruppierungsspalten,
Konstanten, Build-In-Funktionen
119 / 277
SQL
Auswahl von Gruppen (2)
Finde die Schauspieler (PNR, NAME), die mindestens 2
verschiedene Rollen dargestellt haben und dabei an einem Theater
jeweils genau einmal aufgetreten sind.
SELECT S.PNR PNR, S.NAME NAME FROM
SCHAUSPIELER S, DARSTELLER D
WHERE S.PNR=D.PNR
GROUP BY S.PNR, S.NAME
HAVING COUNT(*)>=2 AND COUNT(*)=COUNT(DISTINCT
THEATER)
PNR
1
4
5
NAME
Schön
Schiller
George
120 / 277
SQL
Auswertung von SQL-Anweisungen
1. Die auszuwertenden Relationen werden durch die
FROM-Klausel bestimmt
2. Das Kartesische Produkt aller Relationen der
FROM-Klausel wird gebildet
3. Tupel werden anhand der WHERE-Klausel selektiert
4. Aus den übrig gebliebenden Tupeln werden Gruppen
gemäß der GROUP-BY-Klausel gebildet, d.h. die
Tupel einer Gruppe haben hinsichtlich der
GROUP-BY-Klausel den gleichen Wert
121 / 277
SQL
Auswertung von SQL-Anweisungen (2)
5. es werden die Gruppen gewählt, die die
HAVING-Klausel erfüllen
- dabei dürfen sich die Prädikate der HAVING-Klausel
nur auf Gruppeneigenschaften beziehen
6. Die Ausgabe wird durch die Auswertung der
SELECT-Klausel bestimmt
- bei Verwendung von GROUP-BY-Klausel dürfen nur
Ausdrücke genutzt werden, die für eine Gruppe nur
genau einen Wert haben
7. Die Ausgabereihenfolge wird durch die
ORDER-BY-Klausel bestimmt
- wird keine ORDER-BY-Klausel verwendet, so ist das
Ergebnis (in der Reihenfolge) nicht deterministisch
122 / 277
SQL
Beispiel Auswertung von SQL-Anweisungen (1)
SELECT A, SUM(B), 12 FROM R WHERE B<=50 GROUP
BY A HAVING MAX(C)>=50 ORDER BY A
R
A
Rot
Rot
Rot
Gelb
Gelb
Blau
Blau
Blau
B
10
20
10
10
80
10
80
20
C
10
10
20
50
180
10
10
200
123 / 277
SQL
Beispiel Auswertung von SQL-Anweisungen (2)
SELECT A, SUM(B), 12 FROM R WHERE B<=50 GROUP
BY A HAVING MAX(C)>=50 ORDER BY A
R0
A
Rot
Rot
Rot
Gelb
Gelb
Blau
Blau
Blau
B
10
20
10
10
80
10
80
20
C
10
10
20
50
180
10
10
200
124 / 277
SQL
Beispiel Auswertung von SQL-Anweisungen (3)
SELECT A, SUM(B), 12 FROM R WHERE B<=50 GROUP
BY A HAVING MAX(C)>=50 ORDER BY A
R00
A
Rot
Rot
Rot
Gelb
Blau
Blau
B
10
20
10
10
10
20
C
10
10
20
50
10
200
125 / 277
SQL
Beispiel Auswertung von SQL-Anweisungen (4)
SELECT A, SUM(B), 12 FROM R WHERE B<=50 GROUP
BY A HAVING MAX(C)>=50 ORDER BY A
R000
A
Rot
Rot
Rot
Gelb
Blau
Blau
B
10
20
10
10
10
20
C
10
10
20
50
10
200
126 / 277
SQL
Beispiel Auswertung von SQL-Anweisungen (5)
SELECT A, SUM(B), 12 FROM R WHERE B<=50 GROUP
BY A HAVING MAX(C)>=50 ORDER BY A
R0000
A
Gelb
Blau
SUM(B)
10
30
12
12
12
127 / 277
SQL
Beispiel Auswertung von SQL-Anweisungen (6)
SELECT A, SUM(B), 12 FROM R WHERE B<=50 GROUP
BY A HAVING MAX(C)>=50 ORDER BY A
R00000
A
Blau
Gelb
SUM(B)
30
10
12
12
12
128 / 277
SQL
Dynamisch erzeugte Tabellen
In der FROM-Klausel sind drei Arten von Tabellenreferenzen
erlaubt:
I
gespeicherte persistente Tabellen
I
logische Sichten auf Tabellen und Sichten mit gespeicherter
persistenter Sichtendefinition (Views)
I
dynamisch berechnete Tabellen, deren Schema und Inhalt
durch eine SELECT-Anweisung in der FROM-Klausel
festgelegt wird
129 / 277
SQL
Beispiel Dynamisch erzeugte Tabellen
Es soll eine Statistik erstellt werden, aus der für jede Abteilung ihre
Nummer, das durchschnittliche bezahlte Gehalt, sowie der Anteil
(in Prozent) an der Gehaltssumme der Firma hervorgeht.
SELECT ANR, AVG(PGEHALT) DURCHSCHNITTSGEHALT,
SUM(PGEHALT)/GEHSUM*100 "ANTEIL GEHALTSSUMME"
FROM PERS, (SELECT SUM(PGEHALT) GEHSUM FROM
PERS) GROUP BY ANR, GEHSUM
ANR
45
55
56
100
DURCHSCHNITTSGEHALT
53333.333
50000
70000
85000
ANTEIL GEHALTSSUMME
29.906542
28.037383
26.168224
15.88785
130 / 277
SQL
Dynamisch erzeugte Tabellen / Tupelvariablen
Bei dynamisch berechneten Tabellen
I
können die Spalten in der SELECT-Klausel neu bzw. alternativ
benannt werden
I
können Tupelvariablen zur Berechnung in der üblichen Weise
benutzt werden
SELECT P.ANR, AVG(P.PGEHALT)
DURCHSCHNITTSGEHALT,
SUM(P.PGEHALT)/S.GEHSUM*100 "ANTEIL
GEHALTSSUMME" FROM PERS P, (SELECT
SUM(Q.PGEHALT) GEHSUM FROM PERS Q) S GROUP BY
P.ANR, S.GEHSUM
131 / 277
SQL
Suchbedingungen
Sammlung von Prädikaten
I
Verknüpfung mit AND, OR, NOT
I
Auswertungsreihenfolge bestimmbar durch Klammerung
nicht-quantifizierte Prädikate
I
Vergleichsprädikate θ
I
LIKE-, BETWEEN-, IN-Prädikate
I
Test auf Nullwert
I
MATCH-Prädikate
I
UNIQUE-Prädikate
quantifizierte Prädikate
I
ALL, ANY, EXISTS
132 / 277
SQL
Tupel-Konstruktor
Finde die Angestellten (PNR, PNAME), die das gleiche Alter und
Gehalt sowie den gleichen Manager haben wie der Angestellte
Sioux.
SELECT PNR, PNAME FROM PERS WHERE (PALTER,
PGEHALT, MNR) IN (SELECT PALTER, PGEHALT, MNR
FROM PERS WHERE PNAME=’Sioux’)
PNR, PNAME
46
47
Apache
Sioux
133 / 277
SQL
LIKE-Prädikat
I
Unterstützung der Suche nach Daten, von denen nur Teile des
Inhaltes bekannt sind
I
LIKE-Prädikate vergleichen einen Datenwert mit einem Muster
bzw. Maske
dabei können zwei spezielle Symbole verwendet werden
I
I
I
% "null oder mehr beliebige Zeichen"
_ "genau ein beliebiges Zeichen"
LIKE-Prädikat ist wahr, wenn der Datenwert auf das entsprechende
Muster mit zulässigen Substitutionen für % und _ passt.
134 / 277
SQL
LIKE-Prädikat (2)
NAME LIKE ’%ERD%’
wird z.B. erfüllt von "ERDMANN", "WERDER", "HERD", "ERD"
ANR LIKE ’_7%’
wird erfüllt von Abteilungen mit 7 als zweitem Zeichen
NAME NOT LIKE ’%-%’
wird erfüllt von Namen ohne Bindestrich
Suche nach ’%’ oder ’_’ durch Voranstellen eines
ESCAPE-Zeichens möglich:
STRING LIKE ’%\_%’ ESCAPE ’\’
wird erfüllt von Zeichenketten mit Unterstrich
Keine allgemeinen regulären Ausdrücke möglich → REGEXP_LIKE
135 / 277
SQL
Beispiel LIKE-Prädikat
Finde alle Theater, die in ihrem Namen die Zeichenkette ’Theater’
oder ’theater’ enthalten.
SELECT DISTINCT THEATER FROM DARSTELLER WHERE
THEATER LIKE ’%Theater%’ OR THEATER LIKE
’%theater%’
THEATER
Schillertheater
Schloßtheater
Theater des Westens
Pfalztheater
136 / 277
SQL
Beispiel LIKE-Prädikat (2)
Finde alle Theater, deren Name aus mindestens drei (durch
Leerzeichen getrennte) Worten besteht. Das mittlere Wort soll drei
Zeichen lang sein und mit ’d’ beginnen.
SELECT DISTINCT THEATER FROM DARSTELLER WHERE
THEATER LIKE ’_% d__ _%’
THEATER
Theater des Westens
137 / 277
SQL
BETWEEN-Prädikat
Finde die Angestellten, die zwischen 80000 und 150000 verdienen.
SELECT PNAME FROM PERS WHERE PGEHALT BETWEEN
80000 AND 150000
PNAME
Bigboss
Miniboss
Goldon
y BETWEEN x AND z ⇔ x<=y AND y<=z
y NOT BETWEEN x AND z ⇔ NOT (y BETWEEN x AND z)
138 / 277
SQL
IN-Prädikat
Finde die Nummern der Schauspieler, die den Faust gespielt haben.
SELECT S.NAME FROM SCHAUSPIELER S WHERE
’Faust’ IN (SELECT FIGUR FROM DARSTELLER D
WHERE D.PNR=S.PNR)
oder auch
SELECT S.NAME FROM SCHAUSPIELER S WHERE S.PNR
IN (SELECT D.PNR FROM DARSTELLER D WHERE
FIGUR=’Faust’)
NAME
Schön
Müller
x IN (a,b,...,z) ⇔ x=a OR x=b OR...OR x=z
x IN (...) ⇔ x = ANY (...)
x NOT IN y ⇔ NOT (x IN y)
139 / 277
SQL
IN-Prädikat (2)
Finde die Nummern der Schauspieler, die den Faust gespielt haben.
SELECT NAME FROM SCHAUSPIELER WHERE PNR =
(SELECT PNR FROM DARSTELLER WHERE
FIGUR=’Faust’)
Fehler in Zeile 1:
ORA-01427: Unterabfrage für eine Zeile liefert mehr als eine Zeile
Vergleich mit IN-Prädikat:
I Prädikat ist erfüllt, wenn Gleichheit für mindestens eines der Tupel des
Sub-Selects festgestellt wird
I Prädikat ist nicht erfüllt, wenn Sub-Select die leere Menge liefert
Operator-Vergleich (z.B. ’=’):
I wahr, wenn Sub-Select genau ein Tupel liefert und dieses die
Vergleichsbedingung erfüllt, undefiniert bei mehreren Tupeln
140 / 277
SQL
NULL-Werte
Bei der Spezifikation kann für jedes Attribut festgelegt werden, ob
NULL-Werte zugelassen sind oder nicht.
I
das Ergebnis einer arithmetischen Operation (+,-,*,/) mit
einem NULL-Wert ist ein NULL-Wert.
I
in der Ausgabe kein Wert, wenn Attribut in SELECT-Klausel
NULL-Wert annimmt
I
ein NULL-Term in einem komplexen Ausdruck impliziert NULL
als Ergebnis des Ausdrucks
I
die Auswertung eines NULL-Wertes in einem
Vergleichsprädikat mit irgendeinem Wert ist UNKNOWN (?)
141 / 277
SQL
NULL-Werte (2)
Bei der Auswertung von Booleschen Ausdrücken wird eine
dreiwertige Logik verwendet:
NOT
T
F
?
F
T
?
AND
T
F
?
T
T
F
?
F
F
F
F
?
?
F
?
OR
T
F
?
T
T
T
T
F
T
F
?
?
T
?
?
142 / 277
SQL
NULL-Werte (3)
Erstelle eine Liste der Projekte (JNR, JNAME), die auch den
Namen des Projektmanagers umfaßt.
SELECT PNAME, JNR, JNAME FROM PERS, PROJ WHERE
PNR=JMNR
Tupel mit NULL-werten im Verbundattribut nehmen am Verbund
nicht teil.
PNAME
Goldon
Gerneboss
Gerneboss
Apache
JNR
1
2
3
4
JNAME
Jahresabschluß
NT 4.0
ABC-Analyse
143 / 277
SQL
NULL-Werte (4)
Finde die Personen, die älter als 30 Jahre sind oder deren Gehalt
kleiner als 40000 ist.
SELECT * FROM PERS WHERE PALTER>30 OR
PGEHALT<40000
PERS-Tupel wird ausgegeben, wenn eines von folgendem gilt:
I
PALTER nicht NULL und größer 30 (PGEHALT darf NULL
sein)
I
PGEHALT nicht NULL und kleiner 40000 (PALTER darf
NULL sein)
144 / 277
SQL
Prädikat zum Testen auf NULL-Werte
Finde die Projekte, denen kein Manager zugeordnet ist.
SELECT JNR, JNAME FROM PROJ WHERE JMNR IS NULL
JNR
5
6
JNAME
ISO 9000
Arithmetischer Vergleich mit NULL ergibt immer NULL:
SELECT JNR, JNAME FROM PROJ WHERE JMNR=NULL
Es wurden keine Zeilen gefunden.
145 / 277
SQL
NULL-Werte und Aggregatsfunktionen
Berechnung von Built-In-Funktionen ignoriert NULL-Werte
I
COUNT(*) zählt alle Zeilen
I
AVG, MIN, MAX, SUM berücksichtigen keine Zeilen mit
NULL-Werten
I
DISTINCT zählt NULL nicht als Wert
146 / 277
SQL
NULL-Werte und Aggregatsfunktionen (2)
Erstellen einer Statistik der unterschiedlichen Zeitanteile, die
durchschnittliche Stundenzahl und den Durchschnitt der
unterschiedlichen Stunden.
SELECT COUNT(*), COUNT(DISTINCT ZEITANTEIL),
AVG(STUNDEN), AVG(DISTINCT STUNDEN) FROM
MITARB
COUNT(*)
14
COUNT(DISTINCT ZEITANTEIL)
6
AVG(STUNDEN)
131.25
AVG(DISTINCT STUNDEN)
133.33333
...WHERE STUNDEN IS NOT NULL
COUNT(*)
8
COUNT(DISTINCT ZEITANTEIL)
4
AVG(STUNDEN)
131.25
AVG(DISTINCT STUNDEN)
133.33333
147 / 277
SQL
NULL-Werte sichtbar machen
Spezielle Built-In-Funktion NVL(Argument, Default) von
ORACLE zum Sichtbarmachen von NULL-Werten.
I
Funktionswert ist Wert von Argument, falls nicht NULL-Wert
I
Funktionswert ist Default, falls Argument NULL-Wert
SELECT JNR, NVL(JNAME,’#undefined#’) JNAME,
NVL(JMNR,-1) JMNR FROM PROJ WHERE JMR>2
JNR
3
4
5
6
JNAME
#undefined#
ABC-Analyse
ISO 9000
#undefined#
JMNR
100
46
-1
-1
148 / 277
SQL
Quantifizierte Prädikate
row-constr θ (ALL|ANY) (table-exp)
θ ALL: Prädikat wird als wahr ausgewertet, wenn der
θ-Vergleich für alle Ergebniswerte von table-exp
wahr ist.
θ ANY: wahr, wenn der θ-Vergleich für einen Ergebniswert
wahr ist.
149 / 277
SQL
Beispiel ANY-Prädikat
Finde die Mitarbeiter (PNR, PNAME) aus anderen Abteilungen, die
jünger sind als irgendein Mitarbeiter aus Abteilung 55.
SELECT PNR, PNAME FROM PERS WHERE ANR!=55 AND
PALTER < ANY (SELECT PALTER FROM PERS WHERE
ANR=55)
PNR
300
PNAME
Goldon
I Existenzquantor wird erfüllt, wenn der Vergleich für mindestens eines der durch
das geschachtelte SELECT gefundene Tupel den Wert wahr ergibt
I Existenzquantor kann nicht erfüllt werden, wenn geschachteltes SELECT kein
Tupel findet
I Existanzquantor kann nicht erfüllt werden, wenn geschachteltes SELECT nur
NULL-Werte liefert
150 / 277
SQL
Beispiel ANY-Prädikat (2)
Finde die Nummern der Schauspieler, die den Faust gespielt haben.
Formulierung mit quantifiziertem Präfikat:
SELECT NAME FROM SCHAUSPIELER WHERE PNR = ANY
(SELECT PNR FROM DARSTELLER WHERE
FIGUR=’Faust’)
NAME
Schön
Müller
151 / 277
SQL
Beispiel ALL-Prädikat
Finde die Projekte (JNR, JNAME), die vor allen OS/2-Projekten
("OS/2" Bestandteil des Projektnamens) beendet wurden.
SELECT JNR, JNAME FROM PROJ WHERE JENDE < ALL
(SELECT JENDE FROM PROJ WHERE JNAME LIKE
’%OS/2%’)
JNR
1
2
3
4
5
6
JNAME
Jahresabschluß
NT 4.0
ABC-Analyse
ISO 9000
I Allquantor wird erfüllt, wenn der
Vergleich für alle durch das
geschachtelte SELECT gefundenen
Tupel den Wert wahr ergibt
I Allquantor wird immer erfüllt, wenn
geschachteltes SELECT kein Tupel
findet
I Allquantor kann nicht erfüllt werden,
wenn geschachteltes SELECT
mindestens einen NULL-Wert
zurückgibt
152 / 277
SQL
Test auf Existenz
[NOT] EXISTS (table-exp)
I
Prädikat wir als falsch ausgewertet, wenn table-exp auf die
leere Menge führt, ansonsten wahr
Semantik
x θ ANY (SELECT y FROM T WHERE p) ⇔
EXISTS (SELECT * FROM T WHERE p AND x θ T.y
x θ ALL (SELECT y FROM T WHERE p) ⇔
NOT EXISTS (SELECT * FROM T WHERE p AND NOT (x
θ T.y))
153 / 277
SQL
Beispiel EXISTS-Prädikat
Name, Alter und Gehalt aller Manager sollen ausgegeben werden.
SELECT PNAME, PALTER, PGEHALT FROM PERS M
WHERE EXISTS (SELECT * FROM PERS P WHERE
P.MNR=M.PNR)
PNAME
Bigboss
Miniboss
Gerneboss
PALTER
50
40
25
PGEHALT
100000
80000
50000
I Spezieller Existenzquantor wird erfüllt, wenn durch das geschachtelte SELECT
mindestens ein Tupel gefunden wird
154 / 277
SQL
Mengentheoretische Operatoren
Vereingung (UNION), Durchschnitts- (INTERSECT) und
Differenzbildung (MINUS) von Relationen bzw. Query-Ergebnissen.
(table-exp) {UNION|INTERSECT|MINUS} [ALL]
(table-exp)
I
vor Ausführung werden alle Duplikate aus den Operanden
eliminiert, außer wenn ALL spezifiziert wurde
I
für die Operanden wird Vereinigungsverträglichkeit gefordert
155 / 277
SQL
Beispiel UNION
Finde die Gesamtmenge der Orte in der Dichter-DB, und zwar
Wohnorte, Aufführungsorte und Geburtsorte.
(SELECT
UNION
(SELECT
UNION
(SELECT
UNION
(SELECT
G_ORT ORTE FROM DICHTER)
U_ORT FROM DRAMA)
A_ORT FROM DARSTELLER)
W_ORT FROM SCHAUSPIELER)
ORTE
Berlin
Bonn
Dresden
Frankfurt
Fulda
Jena
Kaiserslautern
Kamenz
Mannheim
Marbach
Weimar
156 / 277
SQL
JOIN-Ausdrücke
SELECT * FROM SCHAUSPIELER S, DARSTELLER D
WHERE S.PNR=D.PNR
ist äquivalent zu:
SELECT * FROM SCHAUSPIELER NATURAL JOIN
DARSTELLER
SELECT * FROM SCHAUSPIELER JOIN DARSTELLER
USING (PNR)
SELECT * FROM SCHAUSPIELER S JOIN DARSTELLER D
ON S.PNR=D.PNR
I
Outer Join: LEFT JOIN, RIGHT JOIN, FULL JOIN
I
Kartesisches Produkt: CROSS JOIN
A CROSS JOIN B ⇔ SELECT * FROM A,B
157 / 277
SQL
JOIN-Ausdrücke (ORACLE Syntax)
Äußerer Verbund wird in ORACLE abweichend vom SQL-Standard
durch den Ausdruck (+) spezifiziert
I
R.A=S.B(+) ergibt linken äußeren Verbund (alle Tupel der
Relation R nehmen am Verbund teil; in der mit (+)
bezeichneten Realtion werden ggf. NULL-Werte ergänzt)
I
R.A(+)=S.B ergibt rechten äußeren Verbund (alle Tupel der
Relation S nehmen am Verbund teil)
158 / 277
SQL
JOIN-Ausdrücke (ORACLE Syntax) (2)
Eine Liste aller Projekte ist zu erstellen (JNR, JNAME); der Name
des Projektmanagers (MGRNAME) soll, wo möglich, ebenfalls
ausgegeben werden.
SELECT JNR, JNAME, PNAME MGRNAME FROM PROJ,
PERS WHERE JMNR=PNR(+)
JNR
1
2
3
4
5
6
JNAME
Jahresabschluß
NT 4.0
ABC-Analyse
ISO 9000
MGRNAME
Goldon
Gerneboss
Gerneboss
Apache
159 / 277
SQL
JOIN-Ausdrücke (ORACLE Syntax) (3)
Finde die Namen (Ang.-Name) und das Alter (Ang.-Alter) aller
Angestellten, die älter als 35 sind, sowie die Namen ihrer
Personalvorgesetzten (Vorg.-name).
SELECT P.PNAME "Ang.-Name", P.PALTER
"Ang.-Alter", D.PNAME "Vorg.-Name" FROM PERS
P, PERS D WHERE P.MNR=D.PNR(+) AND P.PALTER>35
Ang.-Name
Bigboss
Miniboss
Apache
Sioux
Ang.-Alter
50
40
40
40
Vorg.-Name
Bigboss
Miniboss
Miniboss
160 / 277
SQL
Wiederholung/Beispiele
Gegeben sei folgende Anfrage und die resultierenden Ergebnisse:
Welche Mitarbeiter (PNR, PNAME, PALTER) sind älter
als ihre Vorgesetzten?
PNR
102
PNAME
Mohican
PALTER
30
Welche der folgenden SQL-Statements liefern die richtigen
Ergebnisse?
161 / 277
SQL
Wiederholung/Beispiele (1)
SELECT PNR, PNAME, PALTER FROM PERS
WHERE MNR=(SELECT PNR FROM PERS
WHERE PNR=MNR
AND PALTER<PALTER);
162 / 277
SQL
Wiederholung/Beispiele (2)
SELECT PNR, PNAME, PALTER FROM PERS
WHERE MNR=(SELECT PNR FROM PERS
WHERE PNR=MNR
AND PALTER<PALTER);
I
die Tabelle PERS im geschachtelten SELECT überdeckt
äußere Tabelle PERS, daher Bezug auf äußere Tabelle PERS
im geschachtelten SELECT-Block unmöglich
I
es werden keine Tupel gefunden!
163 / 277
SQL
Wiederholung/Beispiele (3)
SELECT PNR, PNAME, PALTER FROM PERS
WHERE MNR=(SELECT M.PNR FROM PERS M
WHERE M.PNR=MNR
AND M.PALTER<PALTER);
164 / 277
SQL
Wiederholung/Beispiele (4)
SELECT PNR, PNAME, PALTER FROM PERS
WHERE MNR=(SELECT M.PNR FROM PERS M
WHERE M.PNR=MNR
AND M.PALTER<PALTER);
I
Bezeichner für äußere Tabelle ist PERS, für die innere Tabelle
M
I
unqualifizierte Attribute werden mit M ergänzt, daher ist die
Anfrage inkorrekt
I
es werden keine Tupel gefunden!
165 / 277
SQL
Wiederholung/Beispiele (5)
SELECT PNR, PNAME, PALTER FROM PERS
WHERE MNR=(SELECT M.PNR FROM PERS M
WHERE M.PNR=PERS.MNR
AND M.PALTER<PERS.PALTER);
166 / 277
SQL
Wiederholung/Beispiele (6)
SELECT PNR, PNAME, PALTER FROM PERS
WHERE MNR=(SELECT M.PNR FROM PERS M
WHERE M.PNR=PERS.MNR
AND M.PALTER<PERS.PALTER);
I
Bezeichner für äußere Tabelle ist PERS, für die innere Tabelle
M
I
es gibt keine unqualifizierten Attribute in der inneren Anfrage,
also werden keine Attribute mit M ergänzt
I
es werden die korrekten Tupel gefunden!
167 / 277
SQL
Wiederholung/Beispiele (7)
Finde die Schauspieler (alle Attribute), deren Wohnort nicht mit
dem Buchstaben ’n’ endet. Die Länge des Namens der gesuchten
Schauspieler soll gleich der irgendeiner Figur sein.
168 / 277
SQL
Wiederholung/Beispiele (8)
Finde die Schauspieler (alle Attribute), deren Wohnort nicht mit
dem Buchstaben ’n’ endet. Die Länge des Namens der gesuchten
Schauspieler soll gleich der irgendeiner Figur sein.
SELECT * FROM SCHAUSPIELER WHERE LENGTH(NAME) IN
(SELECT LENGTH(FIGUR) FROM ROLLE)
AND SUBSTR(W_ORT,-1)!=’n’
PNR
1
2
4
6
7
W_ORT
Frankfurt
Frankfurt
Jena
Frankfurt
Marbach
NAME
Schön
Müller
Schiller
Mann
Krug
169 / 277
SQL
Wiederholung/Beispiele (9)
Finde Titel und Uraufführungsort der Stücke, die höchstens 35
Jahre nach der Geburt ihres Autor uraufgeführt wurden.
170 / 277
SQL
Wiederholung/Beispiele (10)
Finde Titel und Uraufführungsort der Stücke, die höchstens 35
Jahre nach der Geburt ihres Autor uraufgeführt wurden.
SELECT TITEL, U_ORT FROM DRAMA D
WHERE U_JAHR<=(SELECT G_JAHR+35 FROM DICHTER A
WHERE A.AUTOR=D.AUTOR)
TITEL
Iphigenie
Zerbrochene Krug
U_ORT
Mannheim
Weimar
171 / 277
SQL
Wiederholung/Beispiele (11)
Finde die Angestellten, die mehr als ihre (direkten) Manager
verdienen (Ausgabe: PNAME, PGEHALT, PNAME des Managers).
172 / 277
SQL
Wiederholung/Beispiele (12)
Finde die Angestellten, die mehr als ihre (direkten) Manager
verdienen (Ausgabe: PNAME, PGEHALT, PNAME des Managers).
SELECT P.PNAME, P.PGEHALT, M.PNAME FROM PERS P, PERS M
WHERE P.MNR=M.PNR
AND P.PGEHALT>M.PGEHALT
P.PNAME
Cherokee
P.PGEHALT
60000
M.PNAME
Gerneboss
173 / 277
SQL
Transitive Hülle
Z
5
2
1
I
B
3
E
STRUK
A
3
5
8
C
3
D
1
10
F
G
3
H
OTNR
Z
Z
A
A
A
B
B
C
C
D
G
UTNR
I
B
B
C
D
E
F
F
G
G
H
MENGE
5
1
2
5
8
3
3
3
1
10
3
174 / 277
SQL
Transitive Hülle (2)
Finde alle Teile, die bei der Montage des Teils X benötigt werden.
Probleme:
I
beliebig tiefe Schachtelung möglich
I
Schachtelungstiefe nicht bekannt
I
im SQL-Standard eigentlich nicht unterstützt
175 / 277
SQL
Transitive Hülle (2)
Finde alle Teile, die bei der Montage des Teils X benötigt werden.
Behelfslösung für fest Schachtelungstiefe 2:
(SELECT UTNR FROM STRUK WHERE OTNR=’A’)
UNION
(SELECT UTNR FROM STRUK WHERE OTNR IN (SELECT
UTNR FROM STRUK WHERE OTNR=’A’))
UTNR
B
C
D
E
F
G
176 / 277
SQL
Transitive Hülle / ORACLE Erweiterung
Finde alle Teile, die bei der Montage des Teils X benötigt werden.
Lösung mit ORACLE-Erweiterung möglich:
SELECT UTNR FROM STRUK START WITH OTNR=’A’
CONNECT BY PRIOR UTNR=OTNR
UTNR
B
C
D
E
F
G
H
177 / 277
SQL
Transitive Hülle / ORACLE Erweiterung (2)
I
CONNECT BY-Klausel zur Spezifikation einer Rekursion auf
einer Relation.
I
I
Prädikat zur Beschreibung der Ausgangsmenge der Rekursion
(START WITH)
Rekursionsbedingung
CONNECT BY PRIOR A θ B; θ ∈ {=, <, . . . }
I
I
I
Restriktion der im Verlauf der Rekursion gefundenen Werte
durch WHERE-Bedingung möglich
keine Join-Bedingung möglich
inkompatibel mit GROUP BY und HAVING
178 / 277
SQL
Transitive Hülle / Beispiel
Finde alle Teile, in die Teil G eingebaut wird, mit Angabe der
Ebene in der Baugruppenhierarchie.
SELECT OTNR, LEVEL FROM STRUK START WITH
UTNR=’G’ CONNECT BY UTNR=PRIOR OTNR
LEVEL-Pseudospalte gibt Auskunft über die aktuelle Tiefe der
Rekursion
I
kann zur Ausgabe genutzt werden
I
kann zur Auswahl genutzt werden (in CONNECT BY-Klausel
oder auch WHERE-Bedingung)
OTNR
C
A
D
A
LEVEL
1
2
1
2
179 / 277
SQL
Datenmanipulation
Füge den Schauspieler Garfield mit der PNR 4711 ein (satzweises
Einfügen).
INSERT INTO SCHAUSPIELER (PNR, NAME) VALUES
(4711, ’Garfield’)
I
falls die Werte in der richtigen Reihenfolge angegeben werden,
kann die Attributliste wegfallen
180 / 277
SQL
Datenmanipulation
Füge den Schauspieler Garfield mit der PNR 4711 ein (satzweises
Einfügen).
INSERT INTO SCHAUSPIELER VALUES (4711, NULL,
’Garfield’)
I
alle nicht angesprochenen Attribute erhalten NULL-Wert
181 / 277
SQL
Datenmanipulation
Füge die Schauspieler aus Frankfurt in die Relation TEMP ein.
INSERT INTO TEMP (SELECT * FROM SCHAUSPIELER
WHERE W_ORT=’Frankfurt’)
I
mengenorientiertes Einfügen ist möglich, wenn die
einzufügenden Tupel aus einer anderen Relation mit Hilfe einer
SELECT-Anweisung ausgewählt werden können
I
eine leere Relation TEMP sei vorhanden
I
Datentypen der Attribute müssen kompatibel sein
I
die eingefügten Tupel sind unabhängig von denen, von denen
sie abgeleitet/kopiert wurden
182 / 277
SQL
Datenmanipulation
Füge Forschung als Abteilung 200 und die Mitarbeiter Newboss,
Kiowa und Shoshone mit den Nummern 301, 310 und 320, dem
Alter 35, 32 und 30 in die Datenbank ein. Newboss arbeitet für den
Bigboss, die beiden anderen Neulinge arbeiten für Newboss. Alle
erhalten ein Gehalt von 60000, der Bonus wird auf 0 gesetzt.
INSERT INTO
INSERT INTO
ANR) VALUES
INSERT INTO
ANR) VALUES
INSERT INTO
ANR) VALUES
ABT (ANR, ANAME) VALUES (200, ’Forschung’);
PERS (PNR, PNAME, PALTER, PGEHALT, PBONUS, MNR,
(301, ’Newboss’, 35, 60000, 0, 1, 200);
PERS (PNR, PNAME, PALTER, PGEHALT, PBONUS, MNR,
(310, ’Kiowa’, 32, 60000, 0, 301, 200);
PERS (PNR, PNAME, PALTER, PGEHALT, PBONUS, MNR,
(320, ’Shoshone’, 30, 60000, 0, 301, 200);
183 / 277
SQL
Löschen von Tupeln
Lösche den Schauspieler mit der PNR 8.
DELETE FROM SCHAUSPIELER WHERE PNR=8
I
Aufbau der WHERE-Klausel entspricht dem in der
SELECT-Anweisung
184 / 277
SQL
Löschen von Tupeln (2)
Lösche alle Schauspieler, die nie gespielt haben.
DELETE FROM SCHAUSPIELER WHERE PNR NOT IN
(SELECT PNR FROM DARSTELLER)
185 / 277
SQL
Modifikation von Tupeln
Gib den Angestellten aus Abteilung 55, die zur Zeit weniger als
55000 verdienen, eine Gehaltserhöhung von 10%.
UPDATE PERS SET PGEHALT=PGEHALT*1.1 WHERE
ANR=55 AND PGEHALT<55000
I
Aufbau der WHERE-Klausel entspricht dem in der
SELECT-Anweisung
186 / 277
SQL
Modifikation von Tupeln (2)
Gib den am schlechtesten verdienenden Angestellten aller
Abteilungen eine Gehaltserhöhung von 10% und setze ihren Bonus
auf 20000.
UPDATE PERS P SET PGEHALT=PGEHALT*1.1,
PBONUS=20000 WHERE PGEHALT=(SELECT
MIN(S.PGEHALT) FROM PERS S WHERE S.ANR=P.ANR)
I
gemäß Standard, ist eine Referenzierung der Relation einer
Änderungs- bzw. Löschanweisung im FROM-Klausel nicht
erlaubt
I
ORACLE erlaubt dies!
187 / 277
SQL
Modifikation von Tupeln (3)
Angestellten aus den Abteilungen 55 und 56, die zur Zeit noch
keinen Bonus erhalten, soll ab sofort ein solcher gewährt werden,
und zwar in Höhe von 50% des durchschnittlich in der Abteilung
gezahlten Bonus.
UPDATE PERS P SET BONUS=(SELECT
0.5*AVG(PBONUS) FROM PERS B WHERE B.ANR=P.ANR)
WHERE ANR IN (55,56) AND (PBONUS=0 OR PBONUS
IS NULL)
188 / 277
SQL
Übung Gruppierung
Bestimmen Sie die Ergebnisse für die folgenden Anfragen auf der
gegebenen Relation R.
R
A
1
2
3
4
5
6
7
8
9
10
11
B
rot
rot
rot
blau
blau
grün
grün
gelb
gelb
gelb
gelb
C
alt
alt
neu
neu
neu
alt
alt
neu
neu
neu
alt
D
10
20
10
20
30
20
30
20
20
30
10
189 / 277
SQL
Übung Gruppierung (2)
SELECT SUM(D) FROM R WHERE MOD(A,6)=0
SUM(D)
190 / 277
SQL
Übung Gruppierung (3)
SELECT SUM(D) FROM R WHERE MOD(A,6)=0
SUM(D)
20
191 / 277
SQL
Übung Gruppierung (4)
SELECT B, C, SUM(A) FROM R GROUP BY B, C HAVING
COUNT(*)=2
B
C
SUM(A)
192 / 277
SQL
Übung Gruppierung (5)
SELECT B, C, SUM(A) FROM R GROUP BY B, C HAVING
COUNT(*)=2
B
C
SUM(A)
rot
alt
3
blau
neu
9
grün
alt
13
193 / 277
SQL
Übung Gruppierung (6)
SELECT B, SUM(A) FROM R WHERE MOD(A,10)!=0 GROUP BY B,
C HAVING MAX(D)<30
B
SUM(A)
194 / 277
SQL
Übung Gruppierung (7)
SELECT B, SUM(A) FROM R WHERE MOD(A,10)!=0 GROUP BY B,
C HAVING MAX(D)<30
B
rot
SUM(A)
3
gelb
11
gelb
17
rot
3
195 / 277
SQL
Konzepte von SQL-Datenbanken
Daten-Objekte:
Tabellen, Spalten,
Schemata, Kataloge
Benutzer
Wertebereiche
SQL-Datenbank
Rechte
Integritätsbedingungen
196 / 277
SQL
Konzepte von SQL-Datenbanken (2)
SQL-Umgebung - "Die Datenbank"
Katalog
Schema
Tabellen und Spalten
Zeilen und Spalten
197 / 277
SQL
Konzepte von SQL-Datenbanken (3)
I
Begriff der Datenbank ist in SQL2 nicht normiert
I
SQL-Umgebung umfaßt alle von einem DBMS verwalteten
Daten
I
organisatorische Trennung bei SQL2 in Kataloge (ORACLE
spricht hier von "Datenbank-Instanz")
I
SQL-Umgebung enthält Daten und Meta-Daten, wobei alles in
Tabellenform
198 / 277
SQL
Aufbau eines Katalogs (ORACLE)
Tabellenname
CATALOG
COL_COMMENTS
COL_PRIVS
CONSTRAINTS
INDEXES
IND_COLUMNS
OBJECTS
SEQUENCES
SYNONYMS
TABLES
TAB_COLUMNS
TAB_COMMENTS
TAB_PRIVS
TRIGGERS
USERS
VIEWS
Inhaltsbeschreibung
Tabellen, Sichten, Synonyme, Sequenzen
Kommentare auf Tabellenspalten
Rechte auf Tabellenspalten
Integritätsbedingungen
Indices
Tabellenspalten als Teil von Indices
SQL-Objekte (Tabellen, Synonyme, Spalten,. . . )
Sequenzen (Zähler-Objekte mit spezieller
Mehrbenutzersemantik)
Synonyme (oder Alias-Namen)
Tabellen
Tabellenspalten
Kommentare auf Tabellen
Rechte auf Tabellen
Trigger
Benutzer
Sichten
199 / 277
SQL
Datentypen (ORACLE)
Datentyp
Kommentar
VARCHAR2(size)
NVARCHAR2(size)
Character-String mit variabler Länge bis zu 4000 Zeichen
Character-String mit variabler Länge im nationalem Zeichensatz bis zu 4000 Zeichen
Numerischer Datentyp mit Gesamtstellen size und
Nachkommastellen d
Character-Daten variabler Länge bis zu 2GB, keine
Vewendung in WHERE-Klausel, Funktionen, Indizes oder
ähnlichem
Datumswerte inlusive Zeitangabe
Zeitstempel, Genauigkeit precision gibt die Anzahl der
Nachkommastellen für die Sekunden an
Zeitstempel mit Wert für Zeitzonenverschiebung
NUMBER(size,d)
LONG
DATE
TIMESTAMP(precision)
TIMESTAMP(precision)
WITH TIME ZONE
TIMESTAMP(precision)
WITH LOCAL TIME ZONE
Zeitstempel, normalisiert auf die lokale Zeitzone
200 / 277
SQL
Datentypen (ORACLE) (2)
Datentyp
Kommentar
INTERVAL
YEAR(precision) TO
MONTH
INTERVAL
DEAY(day_precision) TO
SECOND(second_precision)
RAW(size)
LONG RAW
ROWID
Periode in Jahren und Monaten, wobei precision die
Anzahl der Ziffern des Jahres spezifiziert
CHAR(size)
NCHAR(size)
CLOB
NCLOB
BLOB
BFILE
Periode in Tagen, Stunden, Minuten und Sekunden
Binäre Daten bis maximal 255 Bytes
Binäre Daten bis zu 2GB
Wert, der eine Zeile in einer ORACLE-Datenbank eindeutig identifiziert
Character-Daten mit fixer Länge bis zu 2000 Zeichen
Multibyte-Zeichensatzversion von CHAR
Große alphanumerische Daten bis zu 4GB
wie CLOB nur für Mehrbyte-Zeichensätze
Binärdaten bis zu 4GB
Zeiger auf eine binäre Systemdatei
201 / 277
SQL
Erzeugung von Basisrelationen (ORACLE)
Die Erzeugung von Basisrelationen umfaßt
I
Spaltennamen und Datentyp
I
Definition des Primärschlüssels
I
Fremdschlüsselbeziehungen
I
Integritätsbedingungen
202 / 277
SQL
Erzeugung von Basisrelationen Syntax (ORACLE)
CREATE TABLE base-table
[(base-table-element-commalist)]
[{ENABLE|DISABLE}CONSTRAINT constraint]
[AS select-exp]
base-table-element::=
column-def|base-table-constraint-def
base-table-constraint-def::=
[CONSTRAINT constraint]
{{PRIMARY KEY|UNIQUE}(column-commalist)|
FOREIGN KEY (column-commalist) references-def|
CHECK(cond-exp)}[DISABLE]
203 / 277
SQL
Erzeugung von Spalten (ORACLE)
CREATE TABLE STUDENT (
MATRIKELNR INT,
NAME VARCHAR(40),
VORNAME VARCHAR(40),
GESCHLECHT VARCHAR(1)
);
204 / 277
SQL
Definition des Primärschlüssels (ORACLE)
CREATE TABLE STUDENT (
MATRIKELNR INT PRIMARY KEY,
NAME VARCHAR(40),
VORNAME VARCHAR(40),
GESCHLECHT VARCHAR(1)
);
205 / 277
SQL
Fremdschlüsselbeziehungen (ORACLE)
CREATE TABLE STUDENT (
MATRIKELNR INT PRIMARY KEY,
NAME VARCHAR(40),
VORNAME VARCHAR(40),
GESCHLECHT VARCHAR(1)
);
CREATE TABLE PRUEFUNG (
MATRIKELNR INT,
LEHRVERANSTALTUNG VARCHAR(40),
NOTE DECIMAL(2,1),
VERSUCH INT,
CONSTRAINT PK PRIMARY KEY(MATRIKELNR, VERSUCH, LEHRVERANSTALTUNG),
CONSTRAINT FK_MATRIKELNR FOREIGN KEY (MATRIKELNR)
REFERENCES STUDENT(MATRIKELNR) ON DELETE CASCADE
);
I Benennung von Integritätsbedingungen kann weggelassen werden
I Benennung wird dringend empfohlen (Testbarkeit, Dokumentation)
206 / 277
SQL
Integritätsbedingungen (ORACLE)
CREATE TABLE STUDENT (
MATRIKELNR INT PRIMARY KEY,
NAME VARCHAR(40) NOT NULL,
VORNAME VARCHAR(40) NOT NULL,
GESCHLECHT VARCHAR(1) NOT NULL,
CONSTRAINT CHK_MATRIKELNR CHECK (MATRIKELNR>=100000 AND MATRIKELNR<=999999),
CONSTRAINT CHK_GESCHLECHT CHECK (GESCHLECHT=’w’ OR GESCHLECHT=’m’)
);
CREATE TABLE PRUEFUNG (
MATRIKELNR INT,
LEHRVERANSTALTUNG VARCHAR(40),
NOTE DECIMAL(2,1),
VERSUCH INT,
CONSTRAINT PK PRIMARY KEY(MATRIKELNR, VERSUCH, LEHRVERANSTALTUNG),
CONSTRAINT FK_MATRIKELNR FOREIGN KEY (MATRIKELNR)
REFERENCES STUDENT(MATRIKELNR) ON DELETE CASCADE
);
Integritätsbedingungen können bei der Definition deaktiviert (DISABLE) oder aktiviert
(ENABLE) werden.
207 / 277
SQL
Integritätsbedingungen (2)
Transaktionskonzept
I
Verarbeitungsfenster für die Einhaltung von semantischen
Integritätsbedingungen
I
Verdeckung von Nebenläufigkeit (concurrency isolation)
I
Verdeckung von Fehlerfällen (failure isolation)
I
Logging und Recovery (COMMIT WORK, ROLLBACK
WORK)
208 / 277
SQL
Integritätsbedingungen (3)
SET AUTOCOMMIT OFF
INSERT INTO STUDENT
INSERT INTO STUDENT
INSERT INTO STUDENT
INSERT INTO STUDENT
COMMIT
(MATRIKELNR,
(MATRIKELNR,
(MATRIKELNR,
(MATRIKELNR,
NAME,
NAME,
NAME,
NAME,
VORNAME,
VORNAME,
VORNAME,
VORNAME,
GESCHLECHT)
GESCHLECHT)
GESCHLECHT)
GESCHLECHT)
VALUES
VALUES
VALUES
VALUES
(...
(...
(...
(...
Abschließen einer Transaktion:
I
Transaktion erfolgreich, persistent speichern (COMMIT)
I
Fehler, zurücknehmen der Änderungen (ROLLBACK)
209 / 277
SQL
Integritätsbedingungen
I
CHECK-Bedingungen
I
Verbot von NULL-Werten, UNIQUE, PRIMARY KEY
I
Fremdschlüsselbedingungen
Dabei Festlegung des Überprüfungszeitpunktes möglich:
I
IMMEDIATE: am Ende einer Änderungsoperation (Default)
I
DEFFERED: am Transaktionsende (COMMIT)
Steuerung des Überprüfungszeitpunktes:
SET CONSTRAINTS {ALL|constraint-commalist}
{DEFERRED|IMMEDIATE}
210 / 277
SQL
Überprüfungszeitpunkt Beispiel
CREATE TABLE A (ID_A NUMBER PRIMARY KEY, CONTENT_A VARCHAR2(255));
CREATE TABLE B (ID_B NUMBER PRIMARY KEY, CONTENT_B VARCHAR2(255));
ALTER TABLE A ADD REF_B NUMBER NOT NULL;
ALTER TABLE A ADD CONSTRAINT FKAB
FOREIGN KEY (REF_B) REFERENCES B(ID_B) DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE B ADD REF_A NUMBER NOT NULL;
ALTER TABLE B ADD CONSTRAINT FKBA
FOREIGN KEY (REF_A) REFERENCES A(ID_A) DEFERRABLE INITIALLY IMMEDIATE;
211 / 277
SQL
Überprüfungszeitpunkt Beispiel (2)
INSERT INTO A (ID_A, REF_B, CONTENT_A) VALUES (1, 2, ’textA’);
INSERT INTO B (ID_B, REF_A, CONTENT_B) VALUES (2, 1, ’textB’);
SELECT * FROM A;
SELECT * FROM B;
INSERT INTO A (ID_A, REF_B, CONTENT_A) VALUES (1, 2, ’textA’)
*
FEHLER in Zeile 1:
ORA-02291: Integritäts-Constraint (DBS2P20.FKAB) verletzt - übergeordneter
Schlüssel nicht gefunden
INSERT INTO B (ID_B, REF_A, CONTENT_B) VALUES (2, 1, ’textB’)
*
FEHLER in Zeile 1:
ORA-02291: Integritäts-Constraint (DBS2P20.FKBA) verletzt - übergeordneter
Schlüssel nicht gefunden
Es wurden keine Zeilen ausgewählt
Es wurden keine Zeilen ausgewählt
212 / 277
SQL
Überprüfungszeitpunkt Beispiel (3)
SET AUTOCOMMIT OFF;
SET CONSTRAINT FKAB DEFERRED;
SET CONSTRAINT FKBA DEFERRED;
INSERT INTO A (ID_A, REF_B, CONTENT_A) VALUES (1, 2, ’textA’);
INSERT INTO B (ID_B, REF_A, CONTENT_B) VALUES (2, 1, ’textB’);
COMMIT
SELECT * FROM A;
SELECT * FROM B;
Constraint wurde festgelegt.
Constraint wurde festgelegt.
1 Zeile wurde erstellt.
1 Zeile wurde erstellt.
ID_A
CONTENT_A
REF_B
---------- ------------ ---------1 textA
2
1 Zeile wurde ausgewählt.
ID_B CONTENT_B
REF_A
---------- ------------ ---------2 textB
1
1 Zeile wurde ausgewählt.
213 / 277
SQL
Erzeugung einer Relation durch Anfrage Beispiel (ORACLE)
CREATE TABLE C (
C1 PRIMARY KEY,
C2 DEFAULT 5,
C3 CHECK (C3>0) DISABLE)
AS SELECT B1,B2,B3 FROM B
Die SELECT-Klausel
I
definiert Datentypen
I
definiert Spaltennamen, wenn nicht angegeben
I
füllt die neue Relation
214 / 277
SQL
Änderung einer Relation (ORACLE)
ALTER TABLE base-table
{ADD (column data-type [DEFAULT default-expr]
[column-constraint-def]...)|
MODIFY (column [data-type][DEFAULT default-expr]
[column-constraint-def]...)|
ADD (base-table-constraint-def)}
[ENABLE enable-clause]...
[DISABLE disable-clause]...
.
.
.
Hinzufügen einer deaktivierten Integritätsbedingung und anschließende Aktivierung:
ALTER TABLE PRUEFUNG
ADD (CONSTRAINT CHK_VERSUCH CHECK (VERSUCH IN (1,2,3)))
ALTER TABLE PRUEFUNG
DISABLE CONSTRAINT CHK_VERSUCH;
215 / 277
SQL
Änderung einer Relation Beispiele (ORACLE)
Hinzufügen von zwei Spalten:
ALTER TABLE STUDENT
ADD (SEMESTER INT, WOHNORT VARCHAR(40));
Modifizierung von Spalten
ALTER TABLE PRUEFUNG
MODIFY (WOHNORT NOT NULL);
I
I
Hinzufügen von Integritätsbedingungen
eingeschränkte Änderung des Datentyps
I
I
Erhöhung der Präzision bei numerischen Datentypen
Vergrößerung der maximalen Länge bei Zeichenketten
216 / 277
SQL
Löschen einer Relation (ORACLE)
DROP {TABLE base-table [CASCADE CONSTRAINTS]|
VIEW view}
I
mit CASCADE CONSTRAINTS werden referentielle
Integritätsbedingungen automatisch mitentfernt
I
fehlt die Option CASCADE CONSTRAINTS wird das Löschen
verhindert, falls noch Abhängigkeiten bestehen
I
Sichten werden generell durch das Löschen nicht
beeinträchtigt, aber intern als ungültig markiert
217 / 277
SQL
Sichtkonzept (ORACLE)
CREATE [OR REPLACE]
[FORCE|NOFORCE] VIEW view [(column-commalist)]
AS table-exp
[WITH CHECK OPTION [CONSTRAINT constraint]|
WITH READ ONLY]
I Modifikation einer bestehenden Sicht möglich (OR REPLACE)
I Sichten können unabhängig von der Existenz der Basisrelationen erzeugt werden
(FORCE)
I Verbot von Änderungsoperationen (WITH READ ONLY)
I Änderungen auf eine Sicht, die zum "Herausfallen" der geänderten Tupel aus
der Sicht führen würden
I
I
können unterbunden werden (WITH CHECK OPTION)
die entsprechende Integritätsbedingung kann benannt werden
(CONSTRAINT)
218 / 277
SQL
Beispiel Sichtkonzept (ORACLE)
Auf der Relation PERS soll eine strukturgleiche, jedoch nicht
änderbare Sicht READPERS erzeugt werden.
CREATE VIEW READPERS AS
SELECT * FROM PERS
WITH READ ONLY
Die Sicht READPERS soll unter Beibehaltung ihrer Struktur jetzt
als änderbar definiert werden.
CREATE OR REPLACE VIEW READPERS AS
SELECT * FROM PERS
219 / 277
SQL
Beispiel Sichtkonzept (ORACLE) (2)
Es soll eine Sicht KEINBONUS aller Mitarbeiter erzeugt werden, so
daß Änderungen nicht "aus der Sicht heraus" führen können.
CREATE VIEW KEINBONUS AS
SELECT * FROM PERS
WHERE PBONUS IS NULL OR PBONUS=0
WITH CHECK OPTION CONSTRAINT KBON
220 / 277
SQL
Beispiel Sichtkonzept (ORACLE) (3)
Es soll die Sicht PRODBESCH aller Mitarbeiter der Abteilungen
Produktion und Beschaffung definiert werden. Änderungen dürfen
nicht "aus der Sicht heraus" führen.
CREATE VIEW PRODBESCH AS
SELECT * FROM PERS
WHERE ANR IN (SELECT ANR FROM ABT
WHERE ANAME IN (’Produktion’,’Beschaffung’))
WITH CHECK OPTION CONSTRAINT BRBE
221 / 277
SQL
Beispiel Sichtkonzept (ORACLE) (4)
Es soll die Sicht PRODBESCH aller Mitarbeiter der Abteilungen
Produktion und Beschaffung definiert werden. Änderungen dürfen
nicht "aus der Sicht heraus" führen. (Alternative Definition)
CREATE VIEW PRODBESCH AS
SELECT P.* FROM PERS P, ABT A
WHERE P.ANR=A.ANR
AND A.ANAME IN (’Produktion’,’Beschaffung’)
WITH CHECK OPTION CONSTRAINT BRBE
222 / 277
SQL
Erzeugung von Indexstrukturen (ORACLE)
I
Einrichtung von Indexstrukturen durch DBA oder Benutzer
I
Nutzung eines Index wird durch den DBS-Optimizer
entschieden
I
Im SQL-Standard keine Anweisung vorgesehen
223 / 277
SQL
Erzeugung von Indexstrukturen (ORACLE) (2)
Erzeugung eines Index:
CREATE [UNIQUE|BITMAP] INDEX index
ON base-table (column [ORDER][, column [ORDER]...)
Beispiel:
CREATE UNIQUE INDEX PERSIND1 ON PERS (PNR)
I
Realisierung bspw. durch B-Bäume
I
keine Duplikate erlaubt
224 / 277
SQL
Erzeugung von Indexstrukturen (ORACLE) (3)
Annahme: In der PERS-Tabelle existiert zusätzlich eine Spalte
PGESCHLECHT, in der nur die Werte ’w’ und ’m’ vorkommen.
Außerdem enthalte die Tabelle ca. 2 Millionen Zeilen.
Indexgröße
Laufzeit (COUNT)
B-Baum
29,69 MB
1,01 sek
Bitmap
753 KB
0,03 sek
Bei wenigen Werten und wenigen nachträglichen Änderungen sollte
statt dem B-Baum (Default) ein Bitmap-Index genutzt werden.
225 / 277
SQL
EXPLAIN PLAN
Analyse von SQL-Anfragen:
I
Untersuchung der Kosten einer Abfrage
I
Anzeige des Ausführungsplans
I
abhängig von Größe der beteiligten Relationen
226 / 277
SQL
EXPLAIN PLAN Beispiel
Welche Schauspieler haben die Rolle ’Faust’ gespielt?
Alternative 1: SELECT S.NAME FROM SCHAUSPIELER S WHERE
’Faust’ IN (SELECT FIGUR FROM DARSTELLER D
WHERE D.PNR=S.PNR);
Alternative 2: SELECT S.NAME FROM SCHAUSPIELER S WHERE
S.PNR IN (SELECT D.PNR FROM DARSTELLER D
WHERE FIGUR=’Faust’);
227 / 277
SQL
EXPLAIN PLAN Beispiel (2)
EXPLAIN PLAN SET STATEMENT_ID=’stmt1’ FOR
SELECT S.NAME FROM SCHAUSPIELER S
WHERE ’Faust’ IN
(SELECT FIGUR FROM DARSTELLER D WHERE D.PNR=S.PNR);
SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, ’stmt1’, ’TYPICAL’));
228 / 277
SQL
EXPLAIN PLAN Beispiel (3)
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------Plan hash value: 451918592
----------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
|
----------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
32 |
800 | 2953
(1)| 00:00:36 |
|* 1 | FILTER
|
|
|
|
|
|
|
2 |
TABLE ACCESS FULL| SCHAUSPIELER | 91478 | 2233K|
87
(5)| 00:00:02 |
|* 3 |
INDEX UNIQUE SCAN| SYS_C0055527 |
1 |
25 |
1
(0)| 00:00:01 |
----------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------1 - filter( EXISTS (SELECT /*+ */ 0 FROM "DARSTELLER" "D" WHERE
"FIGUR"=’Faust’ AND "D"."PNR"=:B1))
3 - access("D"."PNR"=:B1 AND "FIGUR"=’Faust’)
Note
----- dynamic sampling used for this statement
21 Zeilen ausgewählt.
229 / 277
SQL
EXPLAIN PLAN Beispiel (4)
EXPLAIN PLAN SET STATEMENT_ID=’stmt2’ FOR
SELECT S.NAME FROM SCHAUSPIELER S
WHERE S.PNR IN
(SELECT D.PNR FROM DARSTELLER D WHERE FIGUR=’Faust’);
SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, ’stmt2’, ’TYPICAL’));
230 / 277
SQL
EXPLAIN PLAN Beispiel (5)
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------Plan hash value: 587324159
----------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
|
----------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
| 1090 | 54500 |
119 (31)| 00:00:02 |
|
1 | NESTED LOOPS SEMI |
| 1090 | 54500 |
119 (31)| 00:00:02 |
|
2 |
TABLE ACCESS FULL| SCHAUSPIELER | 91478 | 2233K|
87
(5)| 00:00:02 |
|* 3 |
INDEX UNIQUE SCAN| SYS_C0055527 |
13 |
325 |
0
(0)| 00:00:01 |
----------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------3 - access("S"."PNR"="D"."PNR" AND "FIGUR"=’Faust’)
Note
----- dynamic sampling used for this statement
19 Zeilen ausgewählt.
231 / 277
Programmierschnittstellen
Übersicht
Beispiele für Programmierschnittstellen:
I
PL/SQL
I
Embedded SQL, Oracle Call Interface (OCI)
I
Open Database Connectivity (Microsoft)
I
Java Database Connectivity (JDBC)
232 / 277
Programmierschnittstellen
PL/SQL
PL/SQL (Procedural Language/SQL)
I
proprietäre Programmiersprache von Oracle
I
ausgelegt auf die Arbeit mit der Datenbank
I
SQL-Statements nicht als Zeichenketten eingebunden, sondern
gelten als Programmcode
I
Korrektheit kann vor der Laufzeit verifiziert werden
233 / 277
Programmierschnittstellen
PL/SQL Grundlegender Aufbau
DECLARE
-- Deklarationsblock
BEGIN
-- Eigentliches Programm
EXCEPTION
-- Ausnahmeverarbeitung
END;
234 / 277
Programmierschnittstellen
PL/SQL Cursor-Konzept
I
Cursor ist symbolischer Name, der einer Abfrage zugeordnet ist
I
Abfrage der Ergebnistupel mit dem Cursor (one tuple at a
time)
I
Übergabe von Werten in Variablen der Wirtssprache mit INTO
I
Kovertierung der Datentypen
Beispiel:
OPEN c1;
FETCH C1 INTO VAR1, VAR2, ..., VARN
CLOSE c1;
235 / 277
Programmierschnittstellen
PL/SQL Beispiel Cursor
CREATE OR REPLACE PROCEDURE testCursor (autor_in IN VARCHAR2) IS
titel VARCHAR(30);
u_ort VARCHAR(30);
u_jahr INT;
CURSOR myCursor IS
SELECT TITEL, U_ORT, U_JAHR FROM DRAMA WHERE AUTOR=autor_in;
BEGIN
OPEN myCursor;
LOOP
FETCH myCursor INTO titel, u_ort, u_jahr;
EXIT WHEN myCursor%NOTFOUND;
dbms_output.put(titel);
dbms_output.put(’ ’);
dbms_output.put(u_ort);
dbms_output.put(’ ’);
dbms_output.put(u_jahr);
dbms_output.new_line;
END LOOP;
CLOSE myCursor;
END testCursor;
236 / 277
Programmierschnittstellen
PL/SQL Beispiel Schleife
Auch Schleifen ohne explizite Deklaration eines Cursors möglich:
CREATE OR REPLACE PROCEDURE testPlSql (autor_in IN VARCHAR2) IS
BEGIN
FOR row IN (SELECT TITEL, U_ORT, U_JAHR FROM DRAMA WHERE AUTOR=autor_in)
LOOP
dbms_output.put(row.TITEL); dbms_output.put(’ ’);
dbms_output.put(row.U_ORT); dbms_output.put(’ ’);
dbms_output.put(row.U_JAHR); dbms_output.new_line;
END LOOP;
END testPlSql;
Aufruf:
EXEC testCursor(’Schiller’);
Maria Stuart Weimar 1800
Wallenstein Jena 1799
Tell Weimar 1804
237 / 277
Programmierschnittstellen
PL/SQL Beispiel Funktion
CREATE OR REPLACE FUNCTION getAuthor (drama_in IN VARCHAR2)
RETURN VARCHAR2 IS autor_out VARCHAR2(30);
BEGIN
SELECT AUTOR INTO autor_out FROM DRAMA WHERE TITEL=drama_in;
RETURN autor_out;
END getAuthor;
Aufruf der Funktion:
SELECT * FROM DICHTER WHERE AUTOR=getAuthor(’Faust’);
238 / 277
Programmierschnittstellen
Embedded SQL
Embedded SQL
I
Präcompiler, erzeugt ORACLE-spezifischen Code
I
ermöglicht einfache Erstellung von DB-gestützten
Programmen, ohne Detail-Wissen über die ORACLE-Api zu
haben
239 / 277
Programmierschnittstellen
Embedded SQL - Funktionsweise
240 / 277
Programmierschnittstellen
Embedded SQL Beispiel
#include <stdio.h>
#include <sqlca.h>
void sqlerror();
// Definition von Variablen
EXEC SQL BEGIN DECLARE SECTION;
// Connection-String Benutzername/Passwort
char *connstr = "dbs2p20/dbs2p20";
// Variable zur Aufnahme von TITEL
char db_titel[30];
// Variable zur Aufnahme von U_ORT
char db_uort[30];
// Variable zur Aufnahme von U_JAHR
int db_ujahr;
EXEC SQL END DECLARE SECTION;
241 / 277
Programmierschnittstellen
Embedded SQL Beispiel (2)
//Main-Methode
int main() {
// bei jedem Fehler die Methode sqlerror() aufrufen
EXEC SQL WHENEVER SQLERROR DO sqlerror();
// bei Warnungen einfach fortfahren
EXEC SQL WHENEVER SQLWARNING CONTINUE;
// Verbindung zu Datenbank aufbauen
EXEC SQL CONNECT :connstr;
// Cursor mit entsprechendem SELECT-Statement definieren
EXEC SQL DECLARE drama_cursor CURSOR FOR
SELECT TITEL, U_ORT, U_JAHR
FROM DRAMA
WHERE AUTOR = ’Schiller’;
242 / 277
Programmierschnittstellen
Embedded SQL Beispiel (3)
//Main-Methode Fortsetzung
// Cursor "öffnen" um die Ergebnisse zu durchlaufen
EXEC SQL OPEN drama_cursor;
// wenn keine Ergebnisse mehr, mit break die Schleife verlassen
EXEC SQL WHENEVER NOT FOUND DO break;
for (;;) {
// jedes Tupel in die entsprechenden Variablen schreiben...
EXEC SQL FETCH drama_cursor INTO :db_titel, :db_uort, :db_ujahr;
// ...und ausgeben
printf("\t%s\t%s\t%i\n", db_titel, db_uort, db_ujahr);
}
// Cursor schliessen
EXEC SQL CLOSE drama_cursor;
// Transaktion wurde erfolgreich ausgeführt
EXEC SQL COMMIT WORK RELEASE;
// Main-Methode ohne Fehlercode verlassen
return 0;
}
243 / 277
Programmierschnittstellen
Embedded SQL Beispiel (4)
//Methode sqlerror() zur Ausgabe von Fehlerinformationen
void sqlerror() {
// Fehlernummer ausgeben
printf("Oracle error code: %i\nOracle error description:
sqlca.sqlcode,sqlca.sqlerrm.sqlerrmc);
// hier einfach weiter machen
EXEC SQL WHENEVER SQLERROR CONTINUE;
// Rollback durchführen...
EXEC SQL ROLLBACK WORK RELEASE;
// ...und Programm verlassen
exit(1);
return;
}
%s\n",
244 / 277
Programmierschnittstellen
Embedded SQL Beispiel Kompilierung
-bash-3.00$ proc iname=testProC.pc
-bash-3.00$ gcc testProC.c -o testProC -lclntsh -L/opt/oracle/oracle/product/
10.2.0/db_1/lib32 -I/opt/oracle/oracle/product/10.2.0/db_1/precomp/public
-bash-3.00$ ./testProC
Maria Stuart
Weimar
1800
Wallenstein
Jena
1799
Tell
Weimar
1804
245 / 277
Programmierschnittstellen
ORACLE Call Interface
OCI
I
Mächtige Schnittstelle zur ORACLE-Datenbank
I
relativ komplex, viele Funktionen und Parameter
I
hoher Programmieraufwand, selbst bei einfachen Anfragen
I
sehr performante Applikationen möglich die ORACLE-Api zu
haben
246 / 277
Programmierschnittstellen
OCI Beispiel
#include
#include
#include
#include
<stdio.h>
<stdlib.h>
<string.h>
<oci.h>
/* Definition von Variablen */
static OCIEnv *p_env;
static OCIError *p_err;
static OCISvcCtx *p_svc;
static OCIStmt *p_sql;
static OCIDefine *p_dfn = (OCIDefine *) 0;
static OCIBind *p_bnd = (OCIBind *) 0;
247 / 277
Programmierschnittstellen
OCI Beispiel (2)
/* Main-Methode */
int main()
{
int p_bvi;
char p_sli1[40];
char p_sli2[40];
int p_sli3;
int rc;
char errbuf[100];
int errcode;
/* OCI initialisieren */
rc = OCIInitialize((ub4) OCI_DEFAULT, (dvoid *)0,
(dvoid * (*)(dvoid *, size_t)) 0,
(dvoid * (*)(dvoid *, dvoid *, size_t))0,
(void (*)(dvoid *, dvoid *)) 0 );
248 / 277
Programmierschnittstellen
OCI Beispiel (3)
/* Umgebung initialisieren */
rc = OCIEnvInit( (OCIEnv **) &p_env, OCI_DEFAULT, (size_t) 0, (dvoid **) 0 );
/* Handler initialisieren */
rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_err, OCI_HTYPE_ERROR,
(size_t) 0, (dvoid **) 0);
rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_svc, OCI_HTYPE_SVCCTX,
(size_t) 0, (dvoid **) 0);
/* Verbindung zu Datenbank herstellen */
rc = OCILogon(p_env, p_err, &p_svc, "dbs2p20",7, "dbs2p20",7, "oralv10a",8);
if (rc != 0) {
OCIErrorGet((dvoid *)p_err, (ub4) 1, (text *) NULL, &errcode, errbuf,
(ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
printf("Error - %.*s\n", 512, errbuf);
exit(8);
}
249 / 277
Programmierschnittstellen
OCI Beispiel (4)
/* Statement vorbereiten */
rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_sql,
OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0);
rc = OCIStmtPrepare(p_sql, p_err,
"SELECT TITEL, U_ORT, U_JAHR FROM DRAMA WHERE AUTOR=’Schiller’",
(ub4) 61, (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
/* Variable für TITEL binden */
rc = OCIDefineByPos(p_sql, &p_dfn, p_err,
(sword) 40, SQLT_STR, (dvoid *) 0, (ub2
(ub2 *)0, OCI_DEFAULT);
/* Variable für U_ORT binden */
rc = OCIDefineByPos(p_sql, &p_dfn, p_err,
(sword) 40, SQLT_STR, (dvoid *) 0, (ub2
(ub2 *)0, OCI_DEFAULT);
/* Variable für U_JAHR binden */
rc = OCIDefineByPos(p_sql, &p_dfn, p_err,
(sword) sizeof(sword), SQLT_INT, (dvoid
(ub2 *)0, OCI_DEFAULT);
1, (dvoid *) &p_sli1,
*)0,
2, (dvoid *) &p_sli2,
*)0,
3, (dvoid *) &p_sli3,
*) 0, (ub2 *)0,
250 / 277
Programmierschnittstellen
OCI Beispiel (5)
/* Statement ausführen */
rc = OCIStmtExecute(p_svc, p_sql, p_err, (ub4) 1, (ub4) 0,
(CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT);
/* Ergebnisse ausgeben */
while (rc != OCI_NO_DATA) {
printf("%s %s %i\n",p_sli1, p_sli2, p_sli3);
rc = OCIStmtFetch(p_sql, p_err, 1, 0, 0);
}
/* Verbindung beenden und Handler freigeben */
rc = OCILogoff(p_svc, p_err);
rc = OCIHandleFree((dvoid *) p_sql, OCI_HTYPE_STMT);
rc = OCIHandleFree((dvoid *) p_svc, OCI_HTYPE_SVCCTX);
rc = OCIHandleFree((dvoid *) p_err, OCI_HTYPE_ERROR);
return 0;
}
251 / 277
Programmierschnittstellen
ODBC
Open Database Connectivity
I
ursprünglich von Microsoft entwickelt
I
standardisierte Datenbankschnittstelle für SQL
I
Programmierschnittstelle um eine Anwendung relativ
unabhängig vom DBMS zu entwickeln
252 / 277
Programmierschnittstellen
ODBC C# Beispiel
using
using
using
using
System;
System.Collections.Generic;
System.Text;
System.Data.Odbc;
namespace TestODBC
{
class Program
{
static void Main(string[] args)
{
// Datenbankverbindung erzeugen...
OdbcConnection DbConnection = new OdbcConnection(
"Driver=Microsoft ODBC for Oracle;Server=ORALV9A;UID=dbs2p20;PWD=dbs2p20;");
// ...und öffnen
DbConnection.Open();
// Statement erzeugen
OdbcCommand DbCommand = DbConnection.CreateCommand();
DbCommand.CommandText =
"SELECT TITEL, U_JAHR, U_ORT FROM DRAMA WHERE AUTOR=’Schiller’";
253 / 277
Programmierschnittstellen
ODBC C# Beispiel (2)
// Statement ausführen
OdbcDataReader DbReader = DbCommand.ExecuteReader();
// Ergebniszeilen ausgeben
while( DbReader.Read())
{
for (int i = 0; i < DbReader.FieldCount; i++)
{
String col = DbReader.GetString(i);
Console.Write(col + "\t");
}
Console.WriteLine();
}
// Aufräumen
DbReader.Close();
DbCommand.Dispose();
DbConnection.Close();
}
}
}
254 / 277
Programmierschnittstellen
JDBC
Java Database Connectivity
I
entwickelt von Sun Microsystems
I
Datenbankschnittstelle der Java-Plattform
I
JDBC-ODBC-Bridge
255 / 277
Programmierschnittstellen
JDBC Beispiel
import java.sql.*;
public class TestJDBC {
public static void main(String[] args) {
try {
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
Connection connection = DriverManager.getConnection(
"jdbc:oracle:thin:@mtsthelens.informatik.hs-fulda.de:1521:ORALV9A",
"DBS2P20","DBS2P20");
Statement stmt = connection.createStatement();
ResultSet resultSet = stmt.executeQuery(
"SELECT TITEL, U_JAHR, U_ORT FROM DRAMA WHERE AUTOR=’Schiller’");
while (resultSet.next()) {
System.out.println(resultSet.getString("TITEL") + "\t"
+ resultSet.getInt("U_JAHR") + "\t"
+ resultSet.getString("U_ORT"));
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
256 / 277
Datenkontrolle
Möglichkeiten
Semantische Integritätskontrolle
I
Kontrolle, welche Daten "sinnvoll" und "gültig" sind (NOT
NULL, UNIQUE, . . . )
Zugriffkontrolle
I
Maßnahmen zur Datensicherung
I
Definition von Zugriffsobjekten
I
Vergabe und Kontrolle von Zugriffsrechten
257 / 277
Datenkontrolle
Konsistenz - Integrität
Konsistenz
beschreibt die Korrektheit der DB-internen
Speicherstrukturen, Zugriffspfade und sonstigen
Verwaltungsinformationen.
Integrität
beschreibt die Korrektheit der Abbildung der Miniwelt auf
die in der DB gespeicherten Daten
⇒ die Integrität kann verletzt sein, obwohl die Konsistenz der
Datenbank gewahrt bleibt
⇒ ein DBMS kann nur die Konsistenz der Daten sichern
258 / 277
Datenkontrolle
Trigger-Konzept
I
"Anstossen" von Aktionen vor bzw. nach Änderung von Daten
I
mehrere Trigger pro Tabelle erlaubt
I
Rekursionen müssen vermieden werden
259 / 277
Datenkontrolle
Trigger-Konzept in ORACLE
CREATE [OR REPLACE] TRIGGER trigger
{BEFORE|AFTER}
{DELETE|INSERT|UPDATE[OF column-commalist]}
[OR {DELETE|INSERT|UPDATE[OF column-commalist]}] ...
ON table
[ [REFERENCING {OLD [AS] old|NEW [AS] new}...]
FOR EACH ROW[WHEN (condition)]]
pl/sql-block
260 / 277
Datenkontrolle
Beispiel Trigger
CREATE TABLE LEHRVERANSTALTUNG (
NAME VARCHAR(40) PRIMARY KEY,
DOZENT VARCHAR(40),
RAUM VARCHAR(40)
);
CREATE TABLE PRUEFUNG (
MATRIKELNR INT,
LEHRVERANSTALTUNG VARCHAR(40),
NOTE DECIMAL(2,1),
VERSUCH INT,
CONSTRAINT PK PRIMARY KEY(MATRIKELNR, VERSUCH, LEHRVERANSTALTUNG),
CONSTRAINT CHK_VERSUCH CHECK (VERSUCH IN (1,2,3)),
CONSTRAINT CHK_NOTE CHECK (NOTE IN (1.0,1.3,1.7,2.0,2.3,2.7,3.0,3.3,3.7,4.0,5.0)),
CONSTRAINT FK_MATRIKELNR FOREIGN KEY (MATRIKELNR)
REFERENCES STUDENT(MATRIKELNR) ON DELETE CASCADE,
CONSTRAINT FK_LVA FOREIGN KEY (LEHRVERANSTALTUNG)
REFERENCES LEHRVERANSTALTUNG(NAME)
);
261 / 277
Datenkontrolle
Beispiel Trigger (2)
CREATE OR REPLACE TRIGGER erzeugeLVA
BEFORE INSERT ON PRUEFUNG FOR EACH ROW
DECLARE
rowCount NUMBER;
BEGIN
SELECT COUNT(*) INTO rowCount FROM LEHRVERANSTALTUNG
WHERE NAME=:new.LEHRVERANSTALTUNG;
IF (rowCount=0) THEN
INSERT INTO LEHRVERANSTALTUNG (NAME, DOZENT, RAUM)
VALUES (:new.LEHRVERANSTALTUNG, ’Unbekannt’, ’Unbekannt’);
END IF;
END erzeugeLVA;
/
262 / 277
Datenkontrolle
Beispiel Trigger (3)
CREATE TABLE LOG (
ZEITPUNKT DATE,
VORGANG VARCHAR(255)
);
CREATE OR REPLACE TRIGGER schreibeLog AFTER UPDATE ON PRUEFUNG FOR EACH ROW
BEGIN
INSERT INTO LOG (ZEITPUNKT, VORGANG) VALUES (SYSDATE(),
’Eintrag (’ ||
:old.MATRIKELNR || ’/’ || :old.LEHRVERANSTALTUNG || ’/’ ||
:old.VERSUCH || ’/’ || :old.NOTE ||
’) wurde geändert zu (’ ||
:new.MATRIKELNR || ’/’ || :new.LEHRVERANSTALTUNG || ’/’ ||
:new.VERSUCH || ’/’ || :new.NOTE || ’)’);
END schreibeLog;
/
263 / 277
Datenkontrolle
Beispiel Trigger (4)
UPDATE PRUEFUNG SET NOTE=1.3 WHERE MATRIKELNR=100010
AND VERSUCH=3 AND LEHRVERANSTALTUNG=’Prog I’;
1 rows updated
SELECT * FROM LOG;
ZEITPUNKT VORGANG
––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––
19.12.07
Eintrag (100010/Prog I/3/5) wurde geändert zu (100010/Prog I/3/1,3)
1 rows selected
264 / 277
Datenkontrolle
Steuerung Trigger
I
Entfernen von Triggern:
DROP TRIGGER trigger
I
Aktivierung bzw. Deaktivierung:
ALTER TRIGGER trigger {ENABLE|DISABLE}
I
Alle Trigger einer Tabelle aktivieren/deaktivieren:
ALTER TABLE ...[{ENABLE|DISABLE} ALL TRIGGERS]
265 / 277
Datenkontrolle
Zugriffskontrolle
GRANT {privileges-commalist|ALL PRIVILEGES}
ON accessible-object TO grantee-commalist
[WITH GRANT OPTION]
REVOKE[GRANT OPTION FOR] privileges-commalist
ON accessible-object FROM grantee-commalist
I Zugriffsrechte: SELECT, INSERT, UPDATE, DELETE, REFERENCES
I Erzeugung einer abhängigen Relation benötigt REFERENCES-Recht
I Weitergabe von Zugriffsrechten mit WITH GRANT OPTION
Beispiele:
GRANT SELECT, INSERT ON PERS TO DBS2P19;
REVOKE SELECT, INSERT ON PERS FROM DBS2P19;
266 / 277
Datenkontrolle
Transaktionsparadigma
Eine Transaktion ist eine ununterbrechbare Folge von Befehlen,
welcher eine Datenbank von einem konsistenten Zustand in einen
neuen konsistenten Zustand überführt. Man spricht dabei auch
vom ACID-Prinzip:
Atomicity ’Alles oder Nichts’-Eigenschaft (Fehlerisolierung)
Consistency eine erfolgreiche TA erhält die DB-Konsistenz
Isolation alle Aktionen innerhalb einer TA müssen vor parallel
ablaufenden TA verborgen werden
Durability sobald eine TA ihre Änderungen freigegeben hat,
muss das System das Überleben dieser Änderungen
trotz beliebiger Fehler garantieren (Persistenz)
267 / 277
Datenkontrolle
Anomalien - Lost Update
read A(100)
A(110):=A+10
T1
COMMIT
read A(100)
A(150):=A+50
T2
COMMIT
ZEIT
A(100)
A(150)
A(110)
268 / 277
Datenkontrolle
Anomalien - Dirty Read
read A(100)
A(110):=A+10
read B(20)
T1
ABORT
read A(110)
read C(50)
C(160):=C+A
T2
COMMIT
ZEIT
A(100)
A(110)
A(100)
B(20)
C(50)
C(160)
269 / 277
Datenkontrolle
Anomalien - Non-repeatable Read
A(150):=150
T1
read A(100)
Liste
read A(150)
Details
T2
ZEIT
A(100)
A(150)
270 / 277
Datenkontrolle
Anomalien - Inkonsistente Analyse
A(110):=110
B(60):=60
C(30):=30
T1
COMMIT
read A(100)
S(100):=S+A
read B(60)
S(160):=S+B
read C(30)
S(190):=S+C
T2
COMMIT
ZEIT
A(100)
A(110)
B(50)
B(60)
C(20)
C(30)
S(0)
S(100)
S(160)
S(190)
271 / 277
Datenkontrolle
RX Sperrverfahren
I
Sperrmodus eines Objektes:
I
I
I
I
NL (no lock)
R (read)
X (exclusive/write)
Sperranforderung einer Transaktion:
I
I
R (read)
X (exclusive/write)
Kompatibilitätsmatrix:
R
X
NL
+
+
R
+
-
X
-
272 / 277
Datenkontrolle
Zwei-Phasen-Sperrverfahren (2PL)
Sperren
W
S
Zeit
Wachstumsphase (W): Sperren werden nur gesetzt, aber keine freigegeben
Schrumpfungsphase (S): Sperren werden freigegeben, aber keine angefordert
273 / 277
Datenkontrolle
Konservatives Zwei-Phasen-Sperrverfahren (C2PL)
Sperren
W
S
Zeit
I alle Sperren werden vor Beginn der Transaktion gesetzt
I bereits vor dem Ende der Transaktion können Sperren freigegeben werden
I keine Deadlocks möglich
I Verlust von Parallelität
274 / 277
Datenkontrolle
Striktes Zwei-Phasen-Sperrverfahren (S2PL)
Sperren
W
S
Zeit
I alle Sperren werden erst am Ende der Transaktion freigegeben
I lange Wartezeit von blockierten Transaktionen
275 / 277
Quellen
Christian Böhm
Skript zur Vorlesung Datenbanksysteme
Universität Heidelberg, Institut für Informatik, Lehrstuhl für Datenbanksysteme,
2005
Prof. Dr. Dietmar Seipel
Skript Vorlesung Datenbanken
Universität Würzburg, Institut für Informatik, Lehrstuhl für Informatik I, 2006
Prof. Dr. Gottfried Vossen
Datenmodelle, Datenbanksprachen und Datenbankmanagementsysteme
4. korrigierte und ergänzte Auflage, Oldenbourg Wissenschaftsverlag GmbH,
München, 2000
Prof. Dr.-Ing. Stefan Deßloch
Skript zur Vorlesung Informationssysteme
Technische Universität Kaiserslautern, Fachbereich Informatik, AG Heterogene
Informationssysteme, 2007
276 / 277
Quellen (2)
Prof. Dr. Peter Peinl
Skript zur Vorlesung Datenbanksysteme II
Hochschule Fulda, Fachbereich Angewandte Informatik, 2006
Kevin Loney, George Koch
ORACLE 9i - Die umfassende Referenz
Carl Hanser Verlag, München, Wien, 2003
Badran Farwati
Renntrimm - Tipps für das Datenbank-Tuning
Linux Magazin, 04/2005
277 / 277