SELECT

5.4.3.1 Anfragen auf einer Relation
A1:
Gib alle Angestellten aus, die von Beruf
´Hundezüchter´ sind
Selektion
a) In „Langform“
SELECT ALL ANGNR, Name, Wohnort, Beruf, AbtNr
FROM
Angest
WHERE
Beruf = ´Hundezüchter´
ALL ist
„default“, kann also weggelassen werden.
b) „1. Kurzform“
SELECT
ANGNR, Name, Wohnort, Beruf, AbtNr
FROM
Angest
WHERE
Beruf = ´Hundezüchter´
Enthält die Zielliste alle Attribute der Eingangsrelation, darf statt
dessen
geschrieben werden.
c) „2. Kurzform“
SELECT
FROM
Angest
WHERE
Beruf = ´Hundezüchter´
Datenbanksysteme 1
16.01.2012
273
Frage: DISTINCT als Spezifikation in der SELECT-Klausel sorgt für
Duplikatelimination.
Hätten wir im vorliegenden Fall (a-c) auch SELECT DISTINCT
schreiben können?
Was hätte das DBVS jeweils „daraus machen“ können/sollen?
(hoffentlich) gar nichts, will heißen, die DISTINCT-Spezifikation
ignorieren!
A2:
Gib für alle Angestellten Beruf und Wohnort aus und zwar in
dieser Reihenfolge
Projektion
„mehr“ als Algebra
SELECT
Beruf, Wohnort
FROM
Angest
• WHERE-Klausel weggelassen (keine Selektion), da alle Angestellten
erfragt sind. Alternative:
SELECT
Beruf, Wohnort
FROM
Angest
WHERE
true
Datenbanksysteme 1
16.01.2012
274
• Attributreihenfolge (Spaltenreihenfolge) in Ergebnisrelation aus
„streng relationaler Sicht“ eigentlich bedeutungslos (Attribut/Spaltenmenge!), aber für Ausgabe (Bildschirmdarstellung) für
Benutzer natürlich manchmal schon wesentlich aus Gründen der
Übersichtlichkeit etc.
Frage: Würde diesmal DISTINCT semantisch einen Unterschied
bedeuten?
SELECT DISTINCT
Beruf, Wohnort
FROM
Angest
Was müsste das DBVS hier tun und wie?
 Duplikate eliminieren, d.h. i.d.R. sortieren
(Ausgabe erfolgt dann vermutlich ebenfalls sortiert, obwohl nicht
explizit via ORDER BY spezifiziert ...
auf so etwas darf man sich als
Benutzer/Anwendungsentwickler aber nicht verlassen, sondern wenn
Ausgabesortierung benötigt wird, dann stets explizit mit ORDER BY
(siehe unten) durchführen!!!!)
Datenbanksysteme 1
16.01.2012
275
A3:
Gib die Namen aller Angestellten mit AbtNr zwischen 2 und 6 aus
Projektion und Selektion
a) SELECT
Name
FROM
Angest
WHERE
AbtNr >= 2 AND AbtNr <= 6
b) oder ...
WHERE
AbtNr BETWEEN 2 AND 6
c) oder ...
WHERE
AbtNr IN (2,3,4,5,6) !
Bemerkungen zu c)
• Nach IN folgt Multimenge atomarer Werte (die natürlich auch
einelementig oder leer sein darf)
• Typischerweise Ersatz (Kurzschreibweise) für OR-Verknüpfung:
... AbtNr = 16 OR Abt = 95 OR ...
AbtNr IN (16,95,...)
• Nach IN darf auch SFW-Ausdruck ( Tupelmenge) stehen
betrachten wir später Subquery oder Subselect genannt
Datenbanksysteme 1
16.01.2012
276
A4:
Erzeugung eines sortierten Anfrageergebnisses:
Gib die Angest-Relation (Attribute: Name, Wohnort) aufsteigend
sortiert nach den Werten des Attributs Name aus
SELECT
Name, Wohnort
FROM
Angest
ORDER BY Name ASC
Die ASC-Angabe kann weggelassen werden, da „default“.
Frage: In welcher Reihenfolge erscheinen die Werte für Wohnort in der
Ausgabe?
Name
Abeln
Abeln
Dittrich
Dittrich
Dittrich
Kühne
Kühne
Kühne
Datenbanksysteme 1
Wohnort
Karlsruhe
Mannheim
Jena
Augsburg
Zittau
Zwickau
Leipzig
Leipzig
16.01.2012
unsortiert
(„zufällige“ Reihenfolge)
277
A5:
Erzeuge eine Ausgabe wie bei A4, jedoch sollen innerhalb einer
Namensangabe in der Ausgabe die Wohnortangaben absteigend sortiert sein
SELECT
Name, Wohnort
FROM
Angest
ORDER BY Name, Wohnort DESC
Wir hätten auch formulieren können: Name sei
1. Sortierkriterium (1. Priorität), Wohnort
2. Sortierkriterium (2. Priorität)
Name
Abeln
Abeln
Dittrich
Dittrich
Dittrich
Kühne
Kühne
Kühne
Datenbanksysteme 1
Wohnort
Mannheim
Karlsruhe
Zittau
Jena
Augsburg
Zwickau
Leipzig
Leipzig
16.01.2012
Reihenfolge der
Sortierkriterien in
der ORDER BY
Klausel ist wesentlich
(hier: ERST nach Name,
DANN „lokal“ nach Wohnort)
278
Hinweis: In attr_ref in der ORDER BY-Klausel ist es auch gestattet, eine
Attributnummer statt eines Attributnamens zu verwenden (Position
des Attributs in der Zielliste)
Im Beispiel:
SELECT
Name, Wohnort
FROM
Angest
ORDER BY 1, 2 DESC
Macht das so üblicherweise Sinn?
 Nein!!!
aber ...
A6: Gib die Namen aller Angestellten aus aufsteigend nach der Summe
aus Jahresurlaub und Resturlaub (angenommen, die beiden
Attribute existieren in Angest)
SELECT
Name, Jahresurlaub + Resturlaub
FROM
Angest
arith. Ausdruck
ORDER BY 2
Datenbanksysteme 1
16.01.2012
279
A7: Benutzung von eingebauten Funktionen
(„built-in functions“) in SQL
(auch „set functions“)
SQL kennt u.a. die eingebauten Funktionen
COUNT, SUM, AVG, MIN, MAX, die somit innerhalb von SQLAnweisungen benutzt werden können
Wieviele Mitarbeiter arbeiten in unserer Firma?
Schöner/klarer wäre:
SELECT
COUNT( )
COUNT(SELECT FROM Angest)
FROM
Angest
COUNT(Angest)
A8: Wieviele Mitarbeiter wohnen in Erfurt?
Später: Zusätzlich zu den eingeb.
SELECT
COUNT( )
Funktionen gibt es noch sog.
FROM
Angest
User-defined Functions (UDF)
WHERE
Wohnort = ´Erfurt´
A9: Wie lautet die maximale Abteilungsnummer?
Wenn´s gut läuft, kann AnfrageSELECT
MAX(AbtNr)
ergebnis aus entsprechendem
FROM
Angest
Index entnommen werden 
entsprechendes ist mit MIN, SUM und AVG möglich (wenngleich bei
unserer Angest-Relation nicht immer semantisch sinnvoll anwendbar)
Datenbanksysteme 1
16.01.2012
280
A10: Benutzung von eingebauten Funktionen in Zshg. mit dem
GROUP BY Operator
Gib eine Liste der Wohnorte aus zusammen mit dem mittleren
Resturlaub der Mitarbeiter, die jeweils an diesem Ort wohnen
SELECT
Wohnort, AVG (Resturlaub)
FROM
Angest
GROUP BY
Wohnort
die Durchschnittsbildung AVG wird hier nicht auf die gesamte
Angest-Relation einmal angewandt, sondern jeweils für eine Gruppe
von Mitarbeitern mit gleichem Wohnort.
Man kann sich hier vorstellen, dass eine solche Gruppierung vom
DBVS intern als Zwischenergebnis* erzeugt wird.
1. Gruppe
2. Gruppe
3. Gruppe
Wohnort
Aachen
Aachen
Berlin
Erfurt
Erfurt
Erfurt
Resturlaub
14
10
8
6
12
9
AVG = 12
AVG = 8
AVG = 9
* ob das DBVS wirklich ein Zwischenergebnis dieser Art materialisiert,
sei einmal dahingestellt (nach Möglichkeit eher nicht ...)
Datenbanksysteme 1
16.01.2012
281
A11: Formulierung von Gruppierungs-Nebenbedingungen /
Auswahl bestimmter Gruppen
Anfrage wie bei A10, es soll aber die Durchschnittsbildung AVG – und
zusätzlich eine Summenbildung SUM – nur für solche Gruppen
erfolgen, die mindestens 3 Elemente! besitzen und außerdem sollen
Mitarbeiter mit Beruf Ingenieur nicht einbezogen werden
SELECT
Wohnort, AVG (Resturlaub), SUM (Resturlaub)
FROM
Angest
WHERE
Beruf <> ´Ingenieur´
GROUP BY
Wohnort
HAVING COUNT ( ) >= 3
Ausführungsreihenfolge (vgl. Folie 262):
1. SELECT ... FROM ... WHERE ... (ohne „built-in functions“)
2. GROUP BY ...
3. „built-in functions“
4. HAVING ...
5. ORDER BY ...
Datenbanksysteme 1
16.01.2012
282
A12: Gib mir jenen Angestellten, der den geringsten Resturlaub aufweist
(soll für eine Beförderung vorgeschlagen werden):
1. Lösung
SELECT

FROM
Angest
ORDER BY
Resturlaub
liefert (auch) den gesuchten Angestellten, sogar als erstes
Ergebnistupel, aber auch alle anderen Angestelltentupel
2. Lösung
SELECT
FROM
WHERE
liefert 1 Wert
Angest
Resturlaub =
subquery
in der WHERE-Klausel, die
Bezug auf eine Relation nimmt,
hier „zufälligerweise“ auf die
gleiche Relation, wie in der
Anfrage selbst (Angest)
leitet über auf Anfragen,
die auf mehr als eine
Relation Bezug nehmen
(siehe 5.4.3.2 unten)
Datenbanksysteme 1
(SELECT MIN (Resturlaub)
FROM
Angest)
16.01.2012
283
A13: Formulierung von unscharfen Suchanfragen, „pattern matching“
Was ist das Problem / die Fragestellung?
Gib mir den Wohnort des Angestellten Graf
SELECT
Wohnort
FROM
Angest
WHERE
Name = ´Graf´
Angest
ANGNR
Name
Wohnort
Beruf
AbtNr
...
4711
...
...
Graf, P.
...
...
Mannheim
...
...
Jongleur
...
...
...
...
Anfrage liefert keinen Treffer, da ´Graf´ ´Graf, P.´
statt dessen wie folgt schreiben:
SELECT
Wohnort
FROM
Angest
Wertpräfix
WHERE
Name LIKE ´Graf%´
sucht in Spalte Name nach Attributwerten, die mit ´Graf´ beginnen
(findet somit z.B. auch ´Grafinski´ u.ä.)
Datenbanksysteme 1
16.01.2012
284
SELECT
FROM
WHERE
Wohnort
Angest
Name = ´Graf´ OR
Name LIKE ´Graf%´
Allgemeines zum Vergleichoperator LIKE
• Anwendbar auf Character-Attribute fester oder variabler Länge
• Zwei Markierungszeichen („wild cards“):
% an der durch % markierten Stelle können beliebig viele (auch
keine!) zu ignorierende Zeichen stehen
_ („underscore“) an der durch _ markierten Stelle wird genau ein
Zeichen ignoriert
• % und _ dürfen in einem Suchausdruck in beliebiger Weise gemischt
vorkommen
• Beispiele a) ... Wohnort LIKE ´H%b_rg´
liefert Treffer bei Hamburg, Hammelburg, Heidelberg,
Humburg, Hbirg, nicht aber bei Hbrg oder Humbug
b) ... Name LIKE ´M_ _er´
liefert Treffer bei Meier, Maier, Meyer, Mayer, aber auch
bei Mauer etc.
Datenbanksysteme 1
16.01.2012
285
• LIKE darf negiert werden
NOT LIKE
• LIKE kennt keine Wortgrenzen (Textsemantik: Wo hört ein Wort auf,
wo ein Satz etc.)
reines „pattern matching“, das den gesamten
Character-Attributwert als eine (unstrukturierte) Zeichenkette auffasst
und durchsucht; Anfragen
1.
der Art „Finde alle Angestellten, wo im Attribut Stellenbeschreibung
(Character) das Wort Datenbank auftritt“ nicht so einfach zu stellen
LIKE erfordert häufig sequentielle Suche im Datenbestand seitens
des DBVS (ein Tupel nach dem anderen ...), lediglich bei
2.
Endmarkierung (à la ´Datenbank%´ oder ´Datenban_´ ...) kann BaumIndex benutzt werden, falls vorhanden
• Phonetische Suche (ähnliche Aussprache) nicht abgedeckt, aber in
3.
Produkten teils vorhanden
Merken bspw.: DB2 Text Extender
Datenbanksysteme 1
16.01.2012
286
5.4.3.2 Anfragen auf mehreren Relationen
A14: Bilde das Kreuzprodukt der Relationen Angest und Projekt
SELECT
FROM Angest, Projekt
Wohnort
Beruf
AbtNr
PRONR
PName
PBeschr
PLeiter
112
112
112
205
Müller
Müller
Müller
Winter
Erfurt
Erfurt
Erfurt
Zwickau
Ingenieur
Ingenieur
Ingenieur
Programmierer
3
3
3
3
27
16
84
27
Pkw 2000
Wankel 99
Trabbixx
Pkw 2000
xyz...
yxz...
zyx...
xyz...
205
117
117
205
...
...
...
...
...
...
...
...
Name
...
ANGNR
es wird ein Anfrageergebnis erzeugt, das alle Spalten der beteiligten
Relationen umfasst und in dem jedes Tupel der einen Relation mit
jedem Tupel der anderen Relation zu jeweils einem Ergebnistupel
verknüpft („konkateniert“) wird
im Beispiel 4 Tupel in Angest, 3 in
Projekt
12 Tupel im Anfrageergebnis
dieses Kreuzprodukt wird in der Realität weniger gebraucht, sondern Join mit genauerer Angabe der Bedingung, unter der Tupel
verknüpft werden sollen (vgl. Relationenalgebra,
Relationenkalkül)
Kreuzprodukt ist Spezialfall des allg. Joins
Datenbanksysteme 1
16.01.2012
287
A15:
Gib für jeden Mitarbeiter (Name) aus, in welchen Projekten (
PRONR) er mit welchem Prozentsatz mitarbeitet
Join zwischen der Angest- und der Mitarbeit-Relation
SELECT
Name, PRONR, Prozent
FROM
Angest, Mitarbeit
WHERE
Angest.ANGNR = Mitarbeit.ANGNR
oder auch:
SELECT
Angest.Name, Mitarbeit.PRONR, Mitarbeit.Prozent
FROM
Angest, Mitarbeit
WHERE
Angest.ANGNR = Mitarbeit.ANGNR
oder auch:
SELECT
a.Name, m.PRONR, m.Prozent
FROM
Angest a, Mitarbeit m
WHERE
a.ANGNR = m.ANGNR
häufig
so
benutzt
mit Tupelvariablen a und m
Datenbanksysteme 1
16.01.2012
288
Bemerkungen dazu:
• Eine genauere Bezeichnung der Attributnamen – durch Voranstellen
des Relationsnamens oder einer entsprechend vereinbarten
Tupelvariablen (s.o.) – muss nur dann vorgenommen werden, wenn
ansonsten keine Eindeutigkeit in der Bezeichnung (welches Attribut in
welcher Relation ist gemeint?) gegeben ist
• In einigen Fällen muss mit Tupelvariablen gearbeitet werden, z.B.
wenn eine Relation mit sich selbst „gejoined“ wird (z.B. bei
Modellierung von rekursiven 1:n-Beziehungen / Abfragen darauf)
A16:
Wie bei A15, aber die Ausgabe soll die Attribute Name, PName,
Prozent umfassen
SELECT
a.Name, p.PName, m.Prozent
FROM
Angest a, Projekt p, Mitarbeit m
WHERE
a.ANGNR = m.ANGNR
AND
p.PRONR = m.PRONR
Join über 3 Tabellen
Datenbanksysteme 1
16.01.2012
289
Allgemeine syntaktische Struktur des Join (vereinfacht)
SELECT [ALL | DISTINCT] Attributliste
FROM
Rel1, Rel2, ..., Reln (n 2)
WHERE Reli.Attributname θ1 Relj.Attributname
AND Relk.Attributname θ2 Rell.Attributname
AND
...
AND Rely.Attributname θx Relz.Attributname
mit θx є {=, <>, >, >=, <, <=} Hinweis: Statt Reli...z können in WHERE-Klausel
auch Tupelvariablen benutzt werden oder
können ganz entfallen (vgl. vorherige Folien)
Der bei weitem häufigste Vergleichsoperator ist das "=" (Equi-Join)!!
A17:
Wie bei A16, aber es sollen nur die in Erfurt wohnenden
Mitarbeiter und nur die Projekte, in deren Beschreibung nicht
Wartburg auftritt, berücksichtigt werden
SELECT
...
FROM
...
WHERE
...
AND a.Wohnort = ´Erfurt´
AND p.PBeschr NOT LIKE ´%Wartburg´
Datenbanksysteme 1
16.01.2012
290
Soweit betrachtet: Join, wie auch im Zshg. mit Relationenalgebra
eingeführt
sog. „inner join“
Nachteil/Effekt: Tupel in einer Relation, für die es kein „Gegenstück“ in
einer anderen Relation gibt (m.a.W.: für die die Join-Bedingung nie
erfüllt ist), tauchen im Join-Ergebnis nicht auf, „gehen also verloren“.
Im Beispiel: Angestellte, die in keinem Projekt mitarbeiten bzw.
Projekte ohne Mitarbeiter.
Lösung (SQL92ff.!)
Outer Join
left outer
Join
full outer
Join
right outer
Join
Semantik (am Beispiel)
a) left outer Join zwischen Angest und Mitarbeit
linker Operand
Datenbanksysteme 1
16.01.2012
rechter Operand
291
Mitarbeiter 205 sei aktuell in keinem Projekt tätig
im Join-Ergebnis taucht er trotzdem auf, wobei die undefinierten Werte
(PRONR, Prozent) mit NULL belegt sind
Join-Ergebnis („left outer join“)
ANGNR
Name
Wohnort
Beruf
AbtNr
PRONR
Prozent
27
NULL
NULL
100
NULL
NULL
198
198
Schumann
Jena
Jena
Kaufmann
Kaufmann
4
4
27
16
70
30
...
...
...
3
3
5
...
Ingenieur
Programmierer
Hundezüchter
...
Erfurt
Zwickau
Weimar
...
Müller
Winter
Rüllich
...
112
205
117
diese beiden
Tupel wären beim
„inner join“ im Ergebnis nicht vorhanden
Schreibweise in SQL92-Notationff.*:
SELECT a.ANGNR, a.Name, a.Wohnort, a.Beruf, a.AbtNr,
m.PRONR, m.Prozent
expliziter Join-Operator
FROM
(Angest a
LEFT OUTER JOIN
LEFT OUTER
Variante
Mitarbeit m
des Natural Join
ON
a.ANGNR = m.ANGNR)
!
* wird so heute nicht von allen DBMS-Produkten syntaktisch verstanden
Datenbanksysteme 1
16.01.2012
292
b) right outer Join zwischen Mitarbeit und Projekt
linker Operand
rechter Operand
Projekt 84 sei aktuell ohne Mitarbeiter, da neu initiiert
SELECT m.ANGNR, m.PRONR, p.PName
FROM
(Mitarbeit m LEFT OUTER JOIN Projekt p
ON
m.PRONR = p.PRONR)
Join-Ergebnis („right outer join“)
ANGNR
PRONR
PName
112
198
198
NULL
27
27
16
84
Pkw 2000
Pkw 2000
Wankel 99
Trabbixx
RIGHT OUTER
Variante des
Natural Join
Tupel wäre beim „inner join“
im Ergebnis nicht vorhanden
c) full outer Join: lässt Tupel sowohl aus linkem als auch aus rechtem
Operanden bei Join-Ausführung „überleben“ im Join-Ergebnis. [Im Fall
unserer Bsp.-Tabellen (Folie .......?) nicht sinnvoll einsetzbar.]
Datenbanksysteme 1
16.01.2012
293
Abschließende Bemerkungen zum Thema Join (in SQL92ff.)
SQL92 hat – neben full/left/right outer join – noch weitere syntaktische und
semantische Erweiterungen bzgl. Join-Möglichkeiten gebracht (hier nur
kurz erwähnt):
• CROSS JOIN: Explizite Notation für kartesisches Produkt
SELECT . . .
FROM
Angest CROSS JOIN Projekt
in beiden Fällen wird jedes Tupel
SELECT
FROM
des linken Operanden mit jedem
Tupel des rechten Operanden
verknüpft (vgl. auch Folie 278)
...
Angest, Projekt
• UNION JOIN:
SELECT
FROM
Angest UNION JOIN Projekt
ANGNR Name
Wohnort
Beruf
AbtNr PRONR PName
PBeschr PLeiter
112
205
117
198
NULL
NULL
NULL
Erfurt
Zwickau
Weimar
Jena
NULL
NULL
NULL
Ingenieur
Programmierer
Hundezüchter
Kaufmann
NULL
NULL
NULL
3
3
5
4
NULL
NULL
NULL
NULL
NULL
NULL
NULL
xyz...
yxz...
zyx...
Müller
Winter
Rüllich
Schumann
NULL
NULL
NULL
Datenbanksysteme 1
16.01.2012
NULL
NULL
NULL
NULL
27
16
84
NULL
NULL
NULL
NULL
Pkw 2000
Wankel 99
Trabbixx
NULL
NULL
NULL
NULL
205
117
117
294
• NATURAL JOIN: Explizite Notation für natürlichen Verbund (Equi-Join
über namensgleiche Attribute in linkem und rechtem
Join-Operanden)
SELECT Name, PRONR, Prozent
FROM
Angest NATURAL JOIN Mitarbeit
SELECT
FROM
WHERE
Name, PRONR, Prozent
Angest, Mitarbeit
Angest.ANGNR = Mitarbeit.ANGNR
SELECT
FROM
Name, PRONR, Prozent
(Angest JOIN Mitarbeit
ON Angest.ANGNR = Mitarbeit.ANGNR )
SELECT
FROM
Name, PRONR, Prozent
(Angest JOIN Mitarbeit
USING ANGNR )
Explizite Join-Notation (d.h. mit ...JOIN-Operator)
- erspart Schreibarbeit / erhöht Lesbarkeit
- reduziert Fehlermöglichkeiten
- ist näher an Relationenalgebraschreibweise
- eröffnet neue semantische Möglichkeiten, etwa beim Outer Join
Datenbanksysteme 1
16.01.2012
295
Formulierung von Subqueries/Subselects (vgl. auch Folie 274)
und Existenzbedingungen in Zusammenhang mit SQL-Anfragen
3 verschiedene syntaktische/semantische Formen*:
a)
SELECT Attributliste
FROM
R1, R2, ..., Rn
WHERE [NOT] EXISTS
Subquery !! (SELECT-FROM-WHERE-Ausdruck)
b)
SELECT
FROM
WHERE
Subquery
c)
Attributliste
R1, R2, ..., Rn
Ri.Attributname
<
<=
=
<>
>=
>
ANY
ALL
!! (SELECT-FROM-WHERE-Ausdruck)
SELECT Attributliste
FROM
R1, R2, ..., Rn
WHERE Ri.Attributname IN
Subquery !! (SELECT-FROM-WHERE-Ausdruck)
• Wir sprechen von einer Subquery, wenn in der WHERE-Klausel eines
SFW-Ausdrucks wieder ein SFW-Ausdruck auftritt (darf auch beliebig
weiter geschachtelt werden!)
• Wir unterscheiden unkorrelierte und korrelierte Subqueries ;
bei korrelierten Subqueries „nimmt die innere Query (d.h. die
Subquery) Bezug auf die äußere“ (siehe Bsp. unten)
* etwas vereinfacht
Datenbanksysteme 1
16.01.2012
296
Beispiel:
Gib die Nummern (ANGNR) und Namen aller Angestellten aus, die in
(irgend)einem Projekt zu 100% mitarbeiten
0. Möglichkeit (JOIN)
SELECT a.ANGNR, a.Name
FROM
Angest a, Mitarbeit m
WHERE a.ANGNR = m.ANGNR AND m.Prozent = 100
[Nebenbemerkung/Frage: Treten im Anfrageergebnis evtl. Duplikate
auf?]
1. Möglichkeit (mit EXISTS)
quadratische Komplexität
SELECT a.ANGNR, a.Name
FROM
Angest a
stellt Korrelation her zwischen innerer
WHERE EXISTS
Query (Subquery) und äußerer Query!
(SELECT
„Nested-Loop-Modell“
FROM
Mitarbeit m
WHERE
a. ANGNR = m.ANGNR AND m.Prozent = 100)
• Korrelierte Subquery wg. Bezug „nach außen“
• Es wird – durch EXISTS – lediglich getestet, ob Subquery – Ergebnis
leer oder nicht leer
• Pro Tupel „außen“ 1 Durchlauf durch komplette innere Relation“ (kann
DBMS-intern aber durchaus ggf. effizienter realisiert sein)
Datenbanksysteme 1
16.01.2012
297
2. Möglichkeit (mit ANY)
SELECT a.ANGNR, a.Name
FROM
Angest a
WHERE
a.ANGNR = ANY
(SELECT ANGNR
FROM
Mitarbeit
WHERE
Prozent = 100)
lineare Komplexität
ermittelt die Nummern all
jener Angestellten, die in
(irgend)einem Projekt zu
100% mitarbeiten
• Unkorrelierte Subquery, da kein Bezug „nach außen“
• Innere Query (Subquery) muss nur einmal (!) ausgeführt werden
Zwischenergebnis. Anschließend wird für jeden ANGNR-Wert der
„äußeren Relation“ getestet, ob in diesem Zwischenergebnis enthalten [ob´s
ein reales DBVS genau so tut, sei einmal offengelassen]
• ANY prüft, ob irgendein Ergebniswert der Subquery (Zwischenergebnis) mit
dem „außen“ gegebenen Wert – hier : a.ANGNR – übereinstimmt
• Bei ALL müsste der „außen“ gegebene Wert mit allen durch die Subquery
gelieferten Ergebniswerten übereinstimmen.
• Ohne ALL/ANY: Subquery muss genau einen Wert (Tupel) zurückliefern
Datenbanksysteme 1
16.01.2012
298
3. Möglichkeit (mit IN)
SELECT a.ANGNR, a.Name
FROM
Angest a
WHERE
a.ANGNR IN
(SELECT ANGNR
FROM
Mitarbeit
WHERE
Prozent = 100)
lineare Komplexität
• Äquivalent zu Formulierung mit ANY (obige 2. Möglichkeit)
• Unkorrelierte Subquery
• Innere Query muss nur einmal(!) ausgeführt werden
Hinweis: Die obigen Beispiele (Möglichkeiten 0-3) könnten den Eindruck
erwecken, jede Anfrage ließe sich stets auf diese 4 (äquivalenten) Arten
in SQL formulieren. Dem ist in der Allgemeinheit aber nicht so!!
• Wo es mehrere Möglichkeiten zur Formulierung gibt:
Die wählen, die einem am „natürlichsten“ erscheint.
Datenbanksysteme 1
16.01.2012
299
5.4.3.3 Sichten (Views) in SQL
• Erzeugung „virtueller Relationen“ aufbauend auf Basisrelationen*
sowie ggf. anderen, vorher bereits erzeugten virtuellen Relationen
• Beliebig „schachtelbar“ (Sichten über Sichten ...)
• Zweck: Sprachmittel zur Realisierung der Externen Ebene gemäß
ANSI/SPARC-Architektur
- „Entkopplung“ der Benutzer auf der Externen Ebene von den tiefer
liegenden Ebenen ( Datenunabhängigkeit!!)
- Benutzer brauchen gesamtes, oftmals sehr komplexes
Datenbankschema (= konzeptuelle Ebene) nicht oder nicht im
Detail zu kennen
- Zugeschnittene Sichten auf individuelle Benutzergruppen und
seine/deren Aufgaben
- Änderungen der konzeptuellen Ebene „schlagen“ nicht
* Basisrelationen sind Relationen, die mit create table erzeugt wurden
Datenbanksysteme 1
16.01.2012
300
unbedingt mehr unmittelbar zum Benutzer hin durch (ggf. lediglich
Anpassung der Sichtdefinition durch Datenbankadministrator/Anwendungsadministrator; Benutzer muss dies gar nicht unbedingt
mitbekommen, arbeitet unbeeinflusst weiter)
- Möglichkeit zur zielgerichteten Vergabe von Zugriffsrechten für
den Benutzer durch den Administrator (Datensicherheitaspekt)
[Bsp.: Ein Angestellter soll stets nur seinen eigenen Datensatz in
der Angest-Relation sehen dürfen, nicht aber die Datensätze
anderer Mitarbeiter]
• Sichten – „virtuelle Relationen“ – werden durch SQL-Queries (SFWKonstrukt) „on top“ von Basisrelationen und/oder anderen Sichten
definiert
• Sichten unterscheiden sich – einmal definiert – für den Benutzer
nicht (wesentlich) von Basisrelationen
Anfragen gegen Sichten
sind unbeschränkt/beliebig erlaubt, UPDATE/INSERT/DELETE
allerdings eingeschränkt
Datenbanksysteme 1
16.01.2012
301
Syntax zur Sichtdefinition in SQL* **
CREATE VIEW
sicht
AS
(
attribut
,
)
subselect
zur Umbenennung
von Attributen
(siehe unten)!
Entsprechend zum Löschen von Sichten:
DROP VIEW
sicht
Beispiele:
• Definition einer Sicht auf Angest, die die Spalte Beruf ausgeblendet
(Projektion) und zudem die Tupel mit AbtNr>7:
CREATE VIEW MeineSichtderDinge AS
definiert Rel.schema
SELECT ANGNR, Name, Wohnort, AbtNr
der VIEW
FROM
Angest
WHERE
AbtNr<=7
* entnommen aus: K. Neumann ...
Datenbanksysteme 1
** etwas vereinfacht
16.01.2012
302
gibt es Situationen,
wo (Um)Benennung
nötig ist?
• Definition einer Sicht auf Angest, die (nur) die Nummern und Namen
jener Angestellten umfasst, die in (irgend) einem Projekt zu 100%
mitarbeiten (vgl. Folie 288):
CREATE VIEW Hundertprozentige AS
´Im Prinzip´ Bsp.
SELECT a.ANGNR, a.Name
für updatable view
FROM
Angest a, Mitarbeit m
WHERE
a.ANGNR = m.ANGNR AND m.Prozent = 100
• Sichten dürfen auch Daten aus mehreren (Basis-)Relationen und/oder
anderen Sichten umfassen:
CREATE VIEW HundertprozentigePlus AS
SELECT h.ANGNR, h.Name, m.PRONR
FROM
Hundertprozentige h, Mitarbeit m
Beliebige Schachtelbarkeit
WHERE
h.ANGNR = m.ANGNR
• ... und es dürfen Attribute umbenannt werden:
CREATE VIEW HundertprozentigePlusPlus (A, N, P) AS
SELECT h.ANGNR, h.Name, m.PRONR
FROM
...
WHERE . . .
Datenbanksysteme 1
16.01.2012
303
• Realisierung virtueller Attribute zu Relationen mittels einer Sicht
(virtuelle Attribute können beim E/R-Modell vereinbart werden und
müssen bei der Umsetzung E/R
relational berücksichtigt werden):
Name als virtuelles Attribut zur Relation Mitarbeit:
CREATE VIEW MitarbeitErweitert AS
SELECT m.PRONR, m.ANGNR, m.Prozent, a.Name
FROM
Mitarbeit m, Angest a
WHERE
m.ANGNR = a.ANGNR
Benutzer sieht MitarbeitErweitert als (virtuelle) Relation mit einer
zusätzlichen Spalte gegenüber Mitarbeit
Noch interessanter: Spaltenwert wird berechnet
Abschließende Bemerkungen zu Sichten
• Sehr mächtiges Konzept zur Beschreibung der Externen Ebene /
Externer Schemata, viel mächtiger als entsprechende Möglichkeiten
bei IMS/CODASYL! (keine Werteabhängigkeit bei Definition)
• Probleme im Zusammenhang mit Änderungen auf Sichten (SQLINSERT, -UPDATE, -DELETE)
Rückabbildung auf
zugrundeliegende Basisrelationen nicht immer eindeutig möglich!!
Datenbanksysteme 1
16.01.2012
304
(Extremes) Beispiel (vgl. Folie 272):
CREATE VIEW
Resturlaubsstatistik
(Wohnort, DurchschnittlResturlaub AS
SELECT
Wohnort, AVG(Resturlaub)
FROM
Angest
GROUP BY Wohnort
INSERT
INTO
Resturlaubsstatistik
VALUES (´Jena´, 24)

SQL92ff. schränkt „updatability“ von Sichten stark ein (u.a. kein
Vorkommen von DISTINCT in Sichtdefinition, keine arith. Ausdrücke /
keine Aggregatfunktionen in Zielliste, kein GROUP BY / HAVING,
kein Join als Grundlage der Sichtfunktion)
reale Systeme weichen davon oft teils wiederum stark ab (lassen
mehr/weniger/anderes zu).
im Einzelfall in Systembeschreibung nachlesen / „ausprobieren“ 
Datenbanksysteme 1
16.01.2012
305
CHECK OPTION als Zusatz zur Sichtdefinition
INSERT
INTO
MeineSichtderDinge
VALUES (666, ´Müller´, ´Lüdenscheid´, 9)
Will man dies erlauben?
ist jedenfalls zunächst nicht verboten! Warum?
Explizites Verbieten mittels
CREATE VIEW MeinewasserdichteSichtderDinge AS
SELECT . . .
FROM
...
WHERE . . .
WITH CHECK OPTION
Änderungen der Sicht, die den in ihr nicht sichtbaren Teil der Datenbank
beeinflussen, werden erkannt und zurückgewiesen. Bildlich:
DB
INSERT

Sicht
UPDATE

Datenbanksysteme 1
16.01.2012
306
5.4.3.4 Vergabe von Zugriffsrechten
• Zugriff zu einer Relation / ihren Daten hat zunächst nur der Benutzer,
der sie erzeugt hat
besitzt alle Rechte
• In realen Anwendungen: Datenbankadministrator ist „Eigentümer“
der DB und damit Inhaber aller Rechte
selektive, zielgerichtete Weitergabe erforderlich!
Syntax zur Vergabe von Zugriffsberechtigungen
relation
GRANT
ALL
ON
PUBLIC
TO
sicht!
SELECT
benutzer
,
INSERT
WITH GRANT OPTIOM
DELETE
UPDATE
(
attribut
REVOKE dient
entsprechend zur
Zurücknahme von Rechten
)
/
,
Datenbanksysteme 1
16.01.2012
307
Beispiele/Bemerkungen:
• Benutzer KK soll die Berechtigung zum Lesen der Angest-Relation
erhalten und ihm soll auch gestattet werden, diese Berechtigung
weiterzugeben:
GRANT SELECT ON Angest TO KK
WITH GRANT OPTION
• Benutzer NOWI soll nur die Spalte Wohnort von MeineSichtderDinge
ändern dürfen (wäre sonst ja auch noch schöner ...), Weitergabe
dieser Berechtigung sei unzulässig:
GRANT UPDATE (Wohnort) ON MeineSichtderDinge TO NOWI
• Jedermann soll das Recht erhalten, die Projekt-Relation zu lesen:
GRANT SELECT ON Projekt TO Public
Datenbanksysteme 1
16.01.2012
308
6. Einführung zum Thema Transaktionsverwaltung /
Fehlerbehandlung (/ Synchronisation in Datenbanksystemen)
6.1 Was ist eine Transaktion und wozu braucht man sie?
Transaktion = Folge zusammengehöriger Operationen
(bei relationalen Datenbanksystemen: SQL-Anweisungen)
auf der Datenbank – wobei es sich um Lese- und Schreiboperationen
handeln kann – für die bestimmte Eigenschaften gelten sollen (s.u.)
M.a.W.: Eine Art „Klammerung“ von Operationen, wobei die „öffnende“
(Begin of Transaction) und „schließende“ (End of Transaction,
Commit Transaction,
Abort Transaction ) Klammer vom Benutzer bzw. der Anwendung –
ROLLBACK
bei eingebettetem SQL – gesetzt wird.
OP1 OP2
OP3 OP4
OP5
OP6
BOT
OP7 OP8 OP9
t
EOT
Die Operationen (SQL-Anweisungen) bilden eine Transaktion
Datenbanksysteme 1
16.01.2012
309
Operationen auf Datenbanken finden ausschließlich im Rahmen von
Transaktionen statt, d.h. es gibt keine Operationen „außerhalb“ von TAen!
(Im Extremfall bildet jede Operation für sich eine Transaktion.)
implizite Op-Klammerung als Transaktion, DBMS veranlasst
OP1
OP2
OP3
OP4
OP5
OP6
OP7
t
eine eigene Transaktion pro Operation (der nicht so interessante Fall ...)
Transaktionseigenschaften (ACID-Eigenschaften (Härder/Reuter 1983))
• A = Atomarität (atomicity)
Eine Transaktion wird entweder komplett oder gar nicht ausgeführt: all
or nothing – Prinzip
M.a.W.: Es darf nicht passieren, dass eine Transaktion im „halbfertigen“
Zustand verbleibt (durch Abbruch, „Absturz“ o.ä.), d.h. einige
Änderungen auf der Datenbank ausgeführt wurden, andere hingegen
nicht
Datenbanksysteme 1
16.01.2012
310
• C = Konsistenz (consistency)
Eine Transaktion überführt die Datenbank per Definition von einem
logisch konsistenten Zustand (alle Integritätsregeln gelten) in einen
logisch konsistenten Zustand (alle Integritätsregeln gelten), bildlich:
OP1
OP2
OP3
OP4
OP5
BOT
Datenbank
logisch
konsistent
(integer)
mindestens 2 OPi
ändern (sonst langweilig)
OP6
t
EOT
Datenbank hier möglicherweise inkonsistent
(nicht alle Integritätsregeln erfüllt)
Bsp.: Umbuchung von einem Konto auf ein anderes
Datenbank
logisch
konsistent
(integer)
• I = Isolation Im Mehrbenutzerbetrieb
Das, was innerhalb einer Transaktion geschieht (d.h. die
durchgeführten Datenbankänderungen), wird erst bei EOT nach
außen hin sichtbar (d.h. für andere, parallel ablaufende Transaktionen im Mehrbenutzerbetrieb)
Datenbanksysteme 1
16.01.2012
311
Bsp.:
a) Transaktion T1 macht Umbuchung von einem Konto auf ein
anderes, T2 läuft parallel
T1
OP1
OP2
ändert
OP1
T2
liest
...
t

ohne Isolation würde T2 hier
inkonsistente Datenbank sehen!!
b) Gleiches Szenarium wie bei a), aber Transaktion T1 wird nach
OP2 abgebrochen (Abort)
T1
T2
OP1
OP2

Abort
OP1
...
ändert
liest
t
Isolation ermöglicht
u.a. das isolierte Zurücksetzen einer Transaktion im Fehlerfall!!
Datenbanksysteme 1
ohne Isolation würde T2 hier Datenbankzustand sehen, der
von T1 anschließend (durch Abort) wieder „zurückgenommen“
wurde, also so nicht für die Außenwelt bestimmt war
T2 wäre u.U. ebenfalls zum Abort (Transaktionsabbruch)
gezwungen!!
16.01.2012
312
• D = Dauerhaftigkeit (durability)
Wenn eine Transaktion erfolgreich zum Ende gekommen ist, d.h. mit
commit transaction abgeschlossen hat, dann müssen die von ihr
durchgeführten Änderungen auch im evtl. nachfolgenden Fehlerfall
(„Systemabsturz“, Platten-Crash) überleben*
erfolgreiche Ende
OP1
OP2
OP3
OP4
OP5
BOT
t
Commit
*bzw. vom DBVS automatisch rekonstruierbar sein
M.a.W.: Benutzer muss sich darauf verlassen können, dass seine
durchgeführten und mit Commit „freigegebenen“/„festgeschriebenen“ Änderungen auch wirklich Bestand haben
ACID-Eigenschaften von Transaktionen
erstmals formuliert von Härder/Reuter 1983 (ACM Computing Surveys)
Jim Gray = „Erfinder“ des Transaktionskonzept bzw. erste
wissenschaftliche Veröffentlichungen dazu in den 1970ern
Datenbanksysteme 1
16.01.2012
313
6.2 Fehlerszenarien
zunehmende
Disaster
Transaktionsversagen
a)
Systemversagen
b)
Externspeicherversagen
c)
a) Transaktionsversagen (TA-Abbruch, Transaction Failure)
Eine einzelne Transaktion erreicht nicht ihr normales Ende, sondern
bricht ab (Abort Transaction durch Benutzer/Anwendung oder
durch DBVS)
häufig
hoffentlich nicht so häufig
Gründe:
- Benutzer hat sich´s anders überlegt, hat Fehler bemerkt o.ä.
- „Absturz“ eines Anwendungsprogramms (mit eingebettetem SQL)
- Inkorrekte Eingabedaten
Benutzer/Anwendung beschließt
daraufhin Abbruch
- Anwendungsprogramm „meldet sich nicht mehr“
- Deadlock (Verklemmung) o.ä.
- ...
Datenbanksysteme 1
16.01.2012
314
b) Systemversagen (Systemausfall, System Failure (Crash))
Das DBVS „stürzt ab“, d.h. die gesamte DB-Verarbeitung wird abrupt
unterbrochen
(alle lfd. Transaktionen (und „mehr“) betroffen, ggf. auch bereits
abgeschlossene TA betroffen)
Gründe:
- DBVS-Fehler
- BS-Fehler
- Hardware-Versagen (Defekt, Stromausfall ...)
Konsequenz: Hauptspeicherinhalt geht verloren (Systempuffer, Sperrtabellen ...), Platteninhalte bleiben jedoch erhalten + intakt (Annahme)
c) Externspeicherversagen (Media Failure)
Datenverlust auf Externspeicher; Datenbank ganz oder teilweise zerstört
Gründe:
- Head Crash, „magnetische Alterung“, alle möglichen Ursachen für
Plattendefekt
Frage nun: Wie lassen sich ACID-Eigenschaften trotzdem garantieren
(vor allem „A“ und „D“)
Fehlerbehandlungsmaßnahmen Datenbank-Recovery
Datenbanksysteme 1
16.01.2012
315
6.3 Klassifikation von Fehlerbehandlungsmaßnahmen Datenbank-Recovery
a) Fehlerbehandlung für Transaktionsversagen
DBVS setzt betroffene TA auf ihren Anfangszustand zurück, d.h. alle
Änderungen am DB-Zustand, die von der TA bereits vorgenommen
wurden, werden rückgängig gemacht (Transaction Rollback)
Warum?: Garantieren von A tomarität
Wie?: DBVS protokolliert i.d.R. die Änderungen am Datenbankzustand
(z.B. Tupelmodifikation) in Form von before images (Tupel vor der
Modifikation) und after images (Tupel nach der Modifikation)
sog. Protokoll(datei) (Log[File]) auf Platte
 before images aus Protokoll(datei) können zum Rücksetzen
der Änderungen (Rollback) benutzt werden: automatisch durchs
DBVS!!!
Datenbankfehlerbehandlung erfordert Redundanz (natürlich!)
Datenbanksysteme 1
16.01.2012
316
b) Fehlerbehandlung für Systemversagen
Szenarium: (Bsp.)
D
T1
D!
T2
T3
T4
„Nachgefahren“
CP
CP-Int.
Crash
CP
t
DBVS führt (automatisch oder vom DBA veranlasst) Wiederanlauf
(Restart, ´Warmstart´) durch verbunden mit Crash Recovery
Was ist zu tun?
- T3 und T4 müssen zurückgesetzt werden wg. A tomarität
mit
Hilfe der before images in der Protokolldatei
- T1 und T2 müssen (u.U.) „nachgefahren“ (REDO) werden, d.h. die
von ihnen vorgenommenen Änderungen am DB-Zustand müssen
persistent gemacht, d.h. auf Platte geschrieben werden (sie
können! nämlich – trotz Commit – ggf. noch im Systempuffer
stehen und (noch) nicht auf Platte geschrieben worden sein)
„Nachfahren“ (wg. D auerhaftigkeit) mit Hilfe der after images in der
Protokolldatei
Datenbanksysteme 1
16.01.2012
317
Problem hinsichtlich des „Nachfahrens“: Wie weit in der Vergangenheit muss begonnen werden?
Ab Zeitpunkt des DBVS-Starts?
sehr aufwendig!
Lösung: Sog. Checkpoints (Sicherungspunkte) in regelmäßigen Zeitabständen (z.B. alle 10 Minuten ... (sog. Checkpoint-Intervall))
Pufferinhalt wird komplett in die Datenbank geschrieben
(„flush buffer“)
„Nachfahren“ braucht nicht über den
aktuellsten
Checkpoint hinaus in die Vergangenheit gehen (z.B.
alle
Änderungen von T1 in obigem Bild bereits auf Platte)
R2-Aufwandsbegrenzung.
c) Fehlerbehandlung für Externspeicherversagen
Ausgangspunkt für Fehlerbehandlung:
- Gelegentliches Erstellen von Datenbankkopien (sog.
„Archivkopien“)
Backup-Durchführung
- Protokollieren der Änderungen aller (zumindest aller
abgeschlossenen) Transaktionen in Form von after images in
Protokolldatei
Datenbanksysteme 1
16.01.2012
318
T1
T2
„D“
T3
T4
müssen zur R4-Recovery
(Behandlung von Externspeicherversagen) „nachgefahren“ werden.
...
t
Externspeicherversagen
Zeitpunkt des Erstellens
einer Archivkopie der Datenbank
DBA lädt Archivkopie (i.d.R. Band
Platte) und veranlasst das DBVSseitige Nachfahren aller abgeschlossenen Transaktionen ( R4-Recovery)
wg. D auerhaftigkeit
DB anschließend wieder in aktuellem konsistentem
Zustand (es sind keine Änderungen abgeschlossener TAen verlorengegangen!)
Hinweis:
Die Fehlerbehandlungsmaßnahmen sind etwas vereinfacht dargestellt
worden (z.B. R2- und R3-Recovery bei bestimmten DBVS-Implementierungen nicht erforderlich)
SoSe 2006!
Datenbanksysteme 1
16.01.2012
319