SL - Fakultät für Mathematik und Informatik

Relationenalgebra
• Begriff der Algebra in der Mathematik:
(Nichtleere) Menge von Werten mit Operationen, die auf dem
Wertebereich definiert sind.
Z.B.: Menge der natürlichen Zahlen mit Operationen Addition,
Multiplikation etc.
• Übertragung auf relationales Datenmodell:
- Werte
Relationen
- Operationen
Basisoperationen (wie oben erwähnt,
aber noch „erweiterungsfähig“)
• Anfrageergebnisse werden ggf. durch Schachtelung (Hintereinanderausführung) von Basisoperationen bestimmt
• Wichtig: Abgeschlossenheit, d.h. durch Operationsdurchführung
werden stets wieder Relationen erzeugt (nur ein Wertebereich,
einsortige Algebra) Operation ZÄHLE (gibt für eine Relation die Zahl
ihrer Tupel zurück als natürliche Zahl) würde dagegen verstoßen
mehrsortige Algebra
Datenbanksysteme 1
07.01.2013
209
Beispielrelation im folgenden:
[vgl. Heuer/Saake, S.228]
Buch
ISBN
3-125
3-111
3-765
3-891
3-999
InvNr
0007
1201
4711
4712
4717
Titel
Dr. No
Objektbanken
Datenbanken
Datenbanken
Pascal
Autor
James B.
Heuer
Vossen
Ullman
Wirth
Weitere Schlüssel(kandidaten)?
Ausleihe
InvNr
4711
1201
0007
4712
Name
Meyer
Schulz
Müller
Meyer
Fremdschlüssel?
 Was wäre grundsätzlich „syntaktisch“ möglich, was ist semantisch
sinnvoll?
Datenbanksysteme 1
07.01.2013
210
1. Projektion PJAttr R (πAttr R)
• Attr steht für eine nichtleere Menge von Attributen der Relation
(Tabelle) R
• PJAttr R wählt jene Spalten von R aus, die in Attr angegeben sind (es
wird auf jene Spalten projiziert)
• Beispiele:
1. PJ{Name} Ausleihe
„Teure
Projektion“
Duplikate wurden durch PJ
eliminiert (Aufrechterhaltung
der Mengeneigenschaft, Abgeschlossenheit)
Name
Meyer
Schulz
Müller
2. PJ{InvNr, ISBN} Buch
„Billige
Projektion“
Datenbanksysteme 1
InvNr
0007
1201
4711
4712
4717
ISBN
3-125
3-111
3-765
3-891
3-999
07.01.2013
Keine Duplikate eliminiert,
warum?
Duplikateelim. nie nötig, wenn
Attr Schlüssel enthält
211
2. Selektion SLF R (δF R)
Zeilenauswahl
• F steht für eine Selektionsformel (Bedingung, Prädikat), Details s.u.
• SLF R wählt jene Zeilen von R aus, die der Bedingung F genügen
• Unterscheidung zwischen verschiedenen Arten von Selektionsformeln,
Beispiele:
1. Vergleich von Attribut(wert) und Konstante (Konstantenselektion)
SLInvNr>3333 Buch
InvNr
0007
1201
4711
4712
4717
(=, ,<, ,
Titel
Dr. No
Objektbanken
Datenbanken
Datenbanken
Pascal
ISBN
3-125
3-111
3-765
3-891
3-999
Autor
James B.
Heuer
Vossen
Ullman
Wirth
Ergebnisrelationsschema
Rel.
ebenso verwendbar)
2. Vergleich von Attribut(wert) und Attribut(wert) (Attributselektion)
SLTitel=Autor Buch
InvNr
Titel
ISBN
Autor
leeres
Anfrageergebnis
( ,<, ,>, ebenso verwendbar)
Wertebereiche der Attribute müssen gleich oder kompatibel sein
Datenbanksysteme 1
07.01.2013
212
3. Logische Verknüpfungen von Konstanten- und/oder
Attributselektionen
SL(InvNr>3333)and(Autor
InvNr
4711
4712
“Wirth“)
Buch
Titel
Datenbanken
Datenbanken
ISBN
3-765
3-891
Autor
Vossen
Ullman
Negation etc.
(or-Verknüpfung ebenso verwendbar)
Selektionsformel wird jeweils durch Betrachtung eines Tupels „lokal“
ausgewertet (wie wir oben gesehen haben); es lassen sich auf diesem
einfachen Weg keine Selektionsformeln angeben, die in irgendeiner
Weise „tupelübergreifend“ vergleichen
Datenbanksysteme 1
07.01.2013
213
3. Verbund (Join) R JNF S
• F steht für eine Verbundbedingung, Details s.u.
• R JNF S „konkateniert“ die Tupel der Relationen R und S, die die
Verbundbedingung erfüllen
• Beispiele und weitere Erläuterungen:
Attribut von Buch
Attribut von Ausleihe
Natural Join
1. Buch JNInvNr=InvNr Ausleihe
Es werden jene Tupel aus den Relationen Buch und Ausleihe
„konkateniert“, bei denen Werteübereinstimmung bei InvNr vorliegt
InvNr
0007
1201
4711
4712
Titel
Dr. No
Objektbanken
Datenbanken
Datenbanken
ISBN
3-125
3-111
3-765
3-891
Autor
James B.
Heuer
Vossen
Ullman
Name
Müller
Schulz
Meyer
Meyer
- Gleichheitsbedingung "=": sog. Equi-Join
(mit Abstand wichtigster Fall)
- InvNr als Spalte im Ergebnis(schema) nur einmal vorhanden
(„konkateniert“ stimmt somit nicht ganz)
Datenbanksysteme 1
07.01.2013
214
„inner-join“
• Tupel, zu denen es kein „Gegenstück“ gibt, bei denen die Verbundbedingung also nie zu „true“ ausgewertet wird, sind im Join-Ergebnis
nicht enthalten (im konkreten Fall das Pascal-Buch von Herrn
Wirth)!!!*
• JN-Ausführungslogik (nur zum Verständnis, soll nicht heißen,
dass das DBVS den Join unbedingt so ausführt): !!!
for each Tupel x in Buch do
for each Tupel y in Ausleihe do
if x.InvNr = y.InvNr then
F
erzeuge Ergebnistupel durch „Konkat.“ der Tupel x und y
(„nested loop“) kann auch der realen Ausführung des Join zugrunde
liegen (Effizienz?)
 Aufwand n m für n=card (Buch)
m=card (Ausleihe)
Eine Alt. sort-merge join
* der sog. „outer join“ (kommt irgendwann später in der Vorlesung) übernimmt auch solche Tupel in des Join-Ergebnis und setzt die fehlenden
Attributswerte auf „undef“ NULL
Datenbanksysteme 1
07.01.2013
215
2. InvNr in Ausleihe sei umbenannt in InvNrAus (Umbenennung kann
als eine der Basisoperationen angesehen werden (Attributname als
Attributname neu), werden wir im folgenden aber nicht detailliert
diskutieren)
Buch JNInvNr>InvNrAus Ausleihe
InvNr
1201
4711
4711
4712
4712
4712
4717
4717
4717
4717
Titel
Objektbanken
Datenbanken
Datenbanken
Datenbanken
Datenbanken
Datenbanken
Pascal
Pascal
Pascal
Pascal
ISBN
3-111
3-765
3-765
3-891
3-891
3-891
3-999
3-999
3-999
3-999
Autor
Heuer
Vossen
Vossen
Ullman
Ullman
Ullman
Wirth
Wirth
Wirth
Wirth
InvNrAus
0007
0007
1201
0007
1201
4711
0007
1201
4711
4712
Name
Müller
Müller
Schulz
Müller
Schulz
Meyer
Müller
Schulz
Meyer
Meyer
(<, , , ebenso verwendbar)
Datenbanksysteme 1
07.01.2013
216
4. Vereinigung (UNION)
R UN S
• Vereinigung ist bei gleichen Relationsschemata von R und S möglich
(Attributnamen und Wertebereiche stimmen überein)
• Falls unterschiedliche Relationsschemata vorliegen, aber die
Anzahl der Attribute (Spaltenzahl) in R und S gleich ist und die
Wertebereiche gleich oder kompatibel sind, können durch
Umbenennung von Attributen (Attributname alt
Attributname neu)
die Voraussetzungen für die Vereinigungsdurchführung geschaffen
werden
• Beispiel: (SLInvNr 3333 Buch) UN (SLInvNr
liefert alle Tupel der Buch-Relation
3333
Buch)
• Im Zusammenhang mit der Vereinigung kann Duplikatelimination
erforderlich werden (erledigt der UNION-Operator selbst)
Beispiel: (SLInvNr>3333 Buch) UN (SLTitel=“ banken“ Buch)
Bücher mit InvNr=4711 und 4712 sind doppelt vorhanden, Duplikate
werden vom UN-Operator eliminiert
Datenbanksysteme 1
07.01.2013
217
5. Durchschnitt (INTERSECTION) R IN S
• Die Voraussetzungen für die Anwendung der In-Operation sind völlig
identisch zu denen der UN-Operation, d.h. es müssen für R und S
entweder gleiche Relationsschemata vorliegen oder es muss
Kompatibilität der Attributwertebereiche gegeben sein und durch
Umbenennung von Attributen Namensgleichheit herbeigeführt werden
Beispiel: Gegeben seien die Relationsschemata
Buch (InvNr, Titel, ISBN, Autor)
und Zeitschrift (InvNr, Titel, ISBN, Herausgeber)
- Buch IN Zeitschrift ist verboten 
- Schritte:
a) Vergleich der Relationsschemata:
+ Attributanzahl in beiden Relationen ist gleich
+ Wertebereiche der Attribut-Paare stimmen ebenfalls überein
(nehmen wir an)
b) Umbenennung von Herausgeber in Autor (z.B.)
c) Buch IN Zeitschrift zulässig (ob semantisch sinnvoll, ist andere
Frage ...)
Datenbanksysteme 1
07.01.2013
218
\
6. Differenz R DF S
• Bildet die Differenz der Relationen R und S, d.h. in der Ergebnismenge der DF-Operation sind alle Tupel von R enthalten, die nicht
auch in S vorkommen
• Duplikatelimination nötig? nein
• Voraussetzungen für die Anwendung wie bei UN und IN
Bemerkungen zu den Basisoperationen
• Grundsätzlich lassen sich weitere Basisoperationen aufzählen, so
etwa das kartesische Produkt (CROSS JOIN in SQL). Man kann
jedoch zeigen, dass hierdurch gegenüber den o.g. 6 Basisoperationen
keine zusätzliche Ausdrucksmächtigkeit erreicht wird
kartesisches
Produkt als eigenständige Basisoperation nicht nötig
• Für die Basisoperationen PJ, SL, JN, UN, DF und RE („rename“,
Umbenennung) kann gezeigt werden, dass diese eine minimale
Relationenalgebra bilden, d.h.
- Weglassen einer Operation reduziert die Mächtigkeit
- Hinzufügen einer Operation bringt keine Steigerung der Mächtigkeit
Datenbanksysteme 1
07.01.2013
219
Beispiele zu zusammengesetzten Algebra-Ausdrücken
Angest (ANGNR, Name, Wohnort, Beruf, AbtNr)
Projekt (PRONR, PName, PBeschr, PLeiter)
Mitarbeit (PRONR, ANGNR, Prozent)
vgl. Folie 193 ff.
„Finde die Namen aller Angestellten, die am Projekt 27 mitarbeiten“
a) PJ{Name} (Angest JNANGNR=ANGNR (SLPRONR=27 Mitarbeit))
oder
b) PJ{Name} (Angest JNANGNR=ANGNR (PJ{ANGNR} (SLPRONR=27 Mitarbeit)))
diese Projektion wirft „Ballast“ ab und
ist billig in der Ausführung!!
oder
c) PJ{Name} (SLPRONR=27 (Angest JNANGNR=ANGNR Mitarbeit))
Welche von diesen drei Schreibweisen lässt (wenn man sie genau so
ausführt, wie hingeschrieben) die geringsten Ausführungskosten
erwarten?
... vermutlich b!! aber: hängt auch ab von Relationsgrößen, Selektivität
von Selekt.formeln, Verbundbedingungen, Tupellängen etc. etc.
DBVS kann a) und c) so umbauen, dass Ausführung gemäß b)
Datenbanksysteme 1
07.01.2013
220
Frage: Geht auch
SLPRONR=27 (PJ{Name} (Angest JNANGNR=ANGNR Mitarbeit))
geht schon allein syntaktisch nicht!
Vertauschen von Operationen nicht „beliebig“ möglich!
Übersicht zu Äquivalenz-Umformungen von Relationenalgebraausdrücken
syntaktische Transformation ohne
Veränderung der Anfragesemantik
Das folgende ist eine Auswahl:
1. SLF1 (SLF2 R) SLF2 (SLF1 R)
d.h. Selektionen dürfen in ihrer Reihenfolge vertauscht werden.
Nutzen?
siehe nächste Folie
10. SLF R SLF1 (SLF2 R)
mit F = F1 F2
d.h. mehrere ´einfache´ Selektionen dürfen bei dieser Art des
Auftretens (Hintereinanderausführung) durch eine ´komplexe´
Selektion ersetzt werden.
Nutzen?
siehe nächste Folie
Datenbanksysteme 1
07.01.2013
221
Veranschaulichung zu 1 (Vertauschen von Selektionen)
Angenommen, F1 hat hohe Selektivität („wirft 90% der Tupel raus“), F2
niedrige Selektivität („wirft 10% der Tupel raus“). Anfangs seien 1000
Tupel in R vorhanden.
(F1 und F2 seien zudem unabhängig voneinander)
SLF2 (SLF1 R)
1000
SL
100
SL
90
1100 Tupel werden gelesen
SLF1 (SLF2 R)
1000
SL
900
Kostenbasierte
SL
Anfrageoptim.
90
unter Verwendung von
Statistiken über den Daten
1900 Tupel werden gelesen
Veranschaulichung zu 10 (Bilden von ´komplexen´ Selektionen)
F1 F2
SLF R
1000
SL
90
1000 Tupel werden gelesen
F1
F2
F2
F1
F
Datenbanksysteme 1
07.01.2013
222
2. SLF (PJAttr R) PJAttr (SLF R) wenn Attribute (F) Attr
d.h. Selektion und Projektion dürfen in ihrer Reihenfolge vertauscht
werden, wenn die in F angesprochenen Attribute in Projektion enthalten sind (nicht „wegprojiziert“ wurden)
3. R UN S S UN R
klar, da Kommutativität der Mengenvereinigung bekannt ist
5. R JNF S S JNF´ R
JN-Operation „konkateniert“ Tupel, wenn die Verbundbedingung ´wahr´
ist. Reihenfolge der Attribut(wert)e im Tupel ist ohne Bedeutung, damit ist auch ohne Bedeutung, wer linker und wer rechter Operand ist
bei „Konkatenation“.
Hinweis: Verbundbedingung muss umgedreht werden in der
Angabe F
F , z.B.
R JNA1=A4 S
Attribut in R
Attribut in S
S JNA4=A1 R
Attribut in S
Attribut in R
6. (R UN S) UN T R UN (S UN T)
klar, da Assoziativität der Mengenvereinigung bekannt ist
Datenbanksysteme 1
07.01.2013
223
Beispiel zu 2 (Vertauschen von Projektion/Selektion)
„Finde die Namen und Angestelltennummern aller Angestellten mit
ANGNR>1111“
SLANGNR>1111 (PJ{Name,ANGNR} Angest)
PJ{Name,ANGNR} (SLANGNR>1111 Angest)
In unserem Beispiel sind bei der Projektion keine Duplikate zu eliminieren
(warum?). Man kann sich überlegen – und auch beweisen -, dass die
Äquivalenzumformung 2. auch dann erlaubt ist, wenn Duplikate zu
eliminieren sind.
Datenbanksysteme 1
07.01.2013
224
Relationenkalkül
• Relationen-Algebra: Konstruktion des Anfrageergebnisses
(Ergebnisrelation) durch sukzessive (geschachtelte) Anwendung
von Algebraoperatoren auf die Ausgangsrelation(en)
„proze-
durale (schrittweise) Vorgehensweise, das WAS (man lesen will)
zu beschreiben“
• Relationen-Kalkül: Es wird beschrieben, welche Bedingungen
( Prädikate ) die Tupel der Ergebnisrelation erfüllen müssen
„rein
deklarative Vorgehensweise, das WAS (man lesen will) zu
beschreiben“.
Datenbanksysteme 1
Tupelkalkül
07.01.2013
225
Hinweise vorab:
• Es gibt beim Relationenkalkül viele verschiedene
Darstellungsformen zwischen „rein mathematischer“ Notation und
Notation in vergleichsweise benutzerfreundlicher Datenbanksprache
• Eine konkrete Datenbanksprache (Prototyp, Produkt) war/ist eng
ans Relationenkalkül angelehnt: QUEL (QUEry Language) des
relationalen Datenbanksystems Ingres (ursprünglich entwickelt als
Prototyp von M. Stonebraker an der Univ. of Calif., Berkeley, später
dann auch kommerzielles Produkt).
QUEL hat sich jedoch in der Praxis nicht durchgesetzt, auch Ingres
bietet – neben?/nach QUEL – mittlerweile die Sprache SQL an!
• Darstellungsform für Relationenkalkül im folgenden in Anlehnung an
[Lang/Lockemann: Datenbankeinsatz, Springer-Verlag]
Datenbanksysteme 1
07.01.2013
226
Grundlagen und Begriffe des Relationenkalküls
• Tupelvariablen:
- Tupelvariable wird jeweils einer Relation zugeordnet und
bezeichnet dann irgendein Tupel dieser Relation
- Tupelvariablen müssen also definiert werden vor ihrer Benutzung
und können anschließend in sog. Formeln (s.u.) verwendet
werden
- Einer Relation können mehrere Tupelvariablen zugeordnet werden
- Wir bezeichnen Tupelvariablen im folgenden mit Großbuchstaben
(z.B. U, V, W etc.)
• Tupelkomponenten:
Sei U der Relation R zugeordnet und A ein Attribut von R, so bezeichnet U.A den Attributwert von A
R
A
in irgendeinem Tupel von R
Tupelkomponente U.A
Datenbanksysteme 1
07.01.2013
17
227
•
Bedingungen:
Sind x,y Konstanten oder Tupelkomponenten, so spezifiziert x θ y
mit θє{=, ,<, ,>, } eine (gültige) Bedingung (die Wertebereiche von
x und y seien gleich oder zumindest kompatibel)
•
Formeln:
Formeln werden durch folgende Konstruktionsvorschriften
definiert:
1. Basis: Jede Bedingung ist eine Formel
2. Klammerung, Negation: Ist f eine Formel, so ist auch (f) und (f)
eine Formel
3. Boolesche Operationen: Sind f und g Formeln, so sind auch f g
und f g Formeln
4. Quantoren: Ist f Formel und T (Tupel-)Variable in f, so sind auch
T(f) und T(f) Formeln
5. Abschluss: Nur die durch obige Vorschriften 1-4 erzeugbaren
Ausdrücke sind Formeln
Datenbanksysteme 1
07.01.2013
228
• Freie und gebundene (Tupel-)Variablen:
- In einer Bedingung treten alle Tupelvariablen U frei auf
- In (f), (f), f g, f g tritt U frei auf, wenn es auch in f bzw. g frei
auftritt
- In (f), (f), f g, f g tritt U gebunden auf, wenn es auch in f bzw. g
gebunden auftritt
M.a.W.: Klammerung, Negation und boolesche Operationen verändern
den Status einer Tupelvariable bzgl. frei/gebunden nicht
- Tritt U in f frei auf, so tritt es in T(f) und T(f) gebunden auf
M.a.W.: Tupelvariablen werden durch Quantoren gebunden; eine
Tupelvariable kann aber innerhalb eines Ausdrucks nur einmal
gebunden werden
!
Beispiele: vgl. Buch-Relation, Folie 202
- Sei die Tupelvariable U der Relation Buch zugeordnet
- U.Autor="Heuer" ist eine Bedingung mit der freien Variablen U
- (U.Autor="Heuer") (U.Titel="Objektbanken") ist eine Formel mit
der freien Variablen U
Datenbanksysteme 1
07.01.2013
229
-
U((U.Autor="Heuer") (U.Titel="Objektbanken"))
ist eine Formel mit der gebundenen Variablen U
- U((U.Autor="Heuer") (U.Titel="Objektbanken")) ebenso
• Ausdrücke:
- Ein Ausdruck über einer relationalen Datenbank wird wie folgt
spezifiziert Struktur der Ergebnisrelation
dargestellt:
einer Anfrage
U.A, V.B, W.C . . . where f
Ausgabeliste
mit Formel f, U,V,W ... als Tupelvariablen
Relationen RU, RV, RW ... zugeordnet und
A,B,C ... Attribute aus RU, RV, RW ...
- Falls f stets true ist, kann die where-Klausel weggelassen werden
- Ein Ausdruck definiert ein Anfrageergebnis gegen eine
relationale Datenbank,
d.h. eine Teilmenge des kartesischen Produkts
PJ{A} RU
PJ{B} RV
PJ{C} RX ...
f bestimmt, welche Tupel im Anfrageergebnis enthalten sind
Datenbanksysteme 1
07.01.2013
230
- Die Tupelvariablen U,V,W ... sind in f frei und durch den Ausdruck –
implizit
Auftreten in der ´Ausgabeliste´- automatisch gebunden. Enthält f
weitere Tupelvariablen, so müssen diese dort jeweils explizit durch
einen Quantor gebunden werden.
M.a.W.: Jede Tupelvariable in einem Ausdruck muss entweder in
der ´Ausgabeliste´ auftauchen – und dadurch implizit gebunden
werden - oder in f explizit gebunden werden.
Datenbanksysteme 1
07.01.2013
231
Beispielanfragen im Relationenkalkül… endlich 
Die Zuordnung von Tupelvariablen zu Relationen erfolgt vor dem
Ausdruck durch eine Definition der Art: U in R
(„die Tupelvariable U ist der Relation R zugeordnet“, „die Tupelvariable U
´bewegt sich´ innerhalb der Relation R“)
1. Beispiel: Gesucht sind die Titel und Autoren aller Bücher
B in Buch
B.Titel, B.Autor
liefert
Titel
Dr. No
Objektbanken
Datenbanken
Datenbanken
Pascal
Autor
James B.
Heuer
Vossen
Ullman
Wirth
also die Projektion der Relation Buch auf die Attribute Titel und Autor
where-Klausel weggelassen, d.h. true
Datenbanksysteme 1
07.01.2013
232
2. Beispiel: Gesucht sind die Titel aller Bücher
B in Buch
B.Titel
liefert
Titel
Dr. No
Objektbanken
Datenbanken
Pascal
3. Beispiel: Gesucht sind die Titel und Autoren der Bücher mit InvNr>4710
B in Buch
Bem.:
B.Titel, B.Autor
B durch Auftreten in der
where B.InvNr>4710
Ausgabeliste implizit
liefert
gebunden
Formel f (=Bedingung)
Titel
Datenbanken
Datenbanken
Pascal
Datenbanksysteme 1
Autor
Vossen
Ullman
Wirth
07.01.2013
233
4. Beispiel: Gesucht sind die Titel und Autoren der Bücher mit
InvNr>4710 und ISBN "3-891"
B in Buch
B.Titel, B.Autor
where (B.InvNr>4710) (B.ISBN "3-891")
liefert
Titel
Datenbanken
Pascal
Autor
Vossen
Wirth
5. Beispiel: Gesucht sind alle Bücher mit Titel=Autor
B in Buch
B.InvNr, B.Titel, B.ISBN, B.Autor
where B.Titel=B.Autor
liefert
InvNr
Datenbanksysteme 1
Titel
ISBN
07.01.2013
Autor
234
6. Beispiel: Gesucht sind???
B in Buch
B.InvNr
where B.Titel B.Titel
liefert
InvNr
(DBVS braucht Ausdruck gar nicht auf der Datenbank auszuwerten, da
f stets false)! Leeres Anfrageergebnis schon bei Anfrageübersetzung
zu erkennen … erwartet man von einem „intelligenten“ Optimizer
7. Beispiel: Gesucht sind die Titel aller ausgeliehenen Bücher
2
1
B in Buch, A in Ausleihe
B.Titel
3
where A(B.InvNr=A.InvNr)
liefert
f
Dupl.elim. wegen
Abgeschlossenheit
Titel
Dr. No
Objektbanken
Datenbanken
Datenbanksysteme 1
07.01.2013
235
8. Beispiel: Gesucht sind die Titel aller von Schulz ausgeliehenen Bücher
B in Buch, A in Ausleihe
B.Titel
where A(B.InvNr=A.InvNr) (A.Name="Schulz")
liefert
Titel
Objektbanken
9. Beispiel: Gesucht sind die Titel aller Bücher, die nicht von Schulz
ausgeliehen sind
B in Buch, A in Ausleihe
B.Titel
where ( A((B.InvNr=A.InvNr) (A.Name="Schulz")))
liefert
Titel
Dr. No
Datenbanken
Pascal
Datenbanksysteme 1
07.01.2013
236
10. Beispiel: Gesucht ist das Buch mit der kleinsten Inventarnummer
B1 in Buch, B2 in Buch
geht! 
B1.InvNr, B1.Titel, B1.ISBN, B1.Autor
adäquat? 
where B2(B2.InvNr B1.InvNr)
liefert
f
InvNr
Titel
ISBN
Autor
0007
Dr. No
3-125
James B.
Was wäre, wenn in der where-Klausel ">" stünde?
11. Beispiel: Gesucht sind die Titel aller ausgeliehenen Bücher
zusammen mit dem Namen des Ausleihers
B in Buch, A in Ausleihe
B.Titel, A.Name
where B.InvNr=A.InvNr
liefert
Datenbanksysteme 1
Titel
Dr. No
Objektbanken
Datenbanken
Name
Müller
Schulz
Meyer
07.01.2013
A und B durch Auflistung in
Ausgabe automatisch gebunden,
deshalb kein Quantor nötig & erlaubt!!
237
Zusammenfassung / ´Hilites´ zu Kap. 5.1 – 5.3
• Grundlagen des relationalen Modells nach E.F. Codd (1970, eine der
fundamentalsten Datenbank-/Informatikpublikationen), Codd auch Turing-AwardGewinner
5.2
• Große Einfachheit des relat. Modells im Vergleich insb. zu hier./Netzwerk-Modell:
Alle Daten in (einfachen) Tabellen befindlich!! Mengen, Tupel, atomare Attribute
(1NF).
• Abstraktion von Implementierung! Gute Basis für Datenunabhängigkeit!
• Wichtig: Abbilderei von E/R-Diagrammen auf Relationsschemata
• Relationenalgebra und –kalkül als mögliche DB-Sprachen
Gegensatz zu vorrelationalen DB-Sprachen. Vorteile:
math. Unterbau, im
• Wohldefinierte formale Semantik
5.3
• Grundlage für Anfrageoptimierung bei Relationenalgebra (Äquivalenztransformationen auf Algebraausdrücken) auch in realen DBMS-Produkten
(Optimizer)
• Algebra/Kalkül sind keine Endbenutzersprachen (im Gegensatz zu SQL). Aber:
Viele Algebra- und Kalkül-Konstrukte wiedererkennbar in SQL. SQL hat Algebra/Kalkül-Prinzipien übernommen und „benutzbar gemacht“.
Datenbanksysteme 1
07.01.2013
238
5.4 Structured Query Language (SQL)
DIE ISO/DIN/... Norm-Datenbanksprache für relationale
Datenbanksysteme
Deckt alle drei Ebenen (Schemata) der ANSI/SPARC-Architektur ab
(vgl. Kap. 1.5.1):
• Externes Schema über sog. Schichten (Views)
• Konzeptuelles Schema
• Internes Schema (teilweise)
(Nochmals) etwas kurz zur Entstehungsgeschichte der Sprache:
• Entstanden in der ersten Hälfte der 1970er Jahre im Rahmen des
System R Projekts (IBM Research Lab, San Jose, Cal.) als Sprache
SEQUEL (Structured English QUEry Language). Don Chamberline et al.
Ziel: Entwicklung einer benutzerfreundlichen Datenbanksprache
großer Mächtigkeit, basierend auf den Erfahrungen mit Relationenalgebra und Relationenkalkül (siehe Ähnlichkeiten im folgenden)
Datenbanksysteme 1
07.01.2013
239
• Weiterentwicklung 1975/76 bei IBM zu SEQUEL2
• Umbenennung zu SQL, erste Produkte ab Ende der 70er Jahre / Anfang
der 80er:
- Oracle (entwickelt von Ex-IBMern ...)
- System R
SQL/DS (SQL Data System, IBM Produkt ab ´81)
DB2
• In der Folge viele weitere Produkte mit SQL-Schnittstelle, teils auch
SQL-Schnittstellenaufsätze für „vorrelationale“ Datenbanksysteme wie
UDS, ADABAS etc.
Ingres (QUEL
SQL!!), Sybase, Informix, DB2, ADABAS D, MS
Access etc. etc. etc.
• SQL-Normungsaktivitäten ab 1982 in ANSI (American National
Standards Institute), ISO und DIN
 SQL0 1. Norm 1987 (ISO und DIN 9075)
 SQL1 2. Norm 1989 („SQL-89“)
Level 1
Level 2
Level 2 mit IEF
Weiterentwicklung
Weiterentwicklung
 SQL2 3. Norm 1992 („SQL-92“, SQL2)
- Entry Level
- Intermediate Level
- Full Level
Weiterentwicklung
*IEF=Integrity Enhancement Feature
Datenbanksysteme 1
07.01.2013
240
 SQL3: SQL:1999
 SQL4: SQL:2003
 Aktuelle Normungsaktivitäten laufen Richtung SQL5 (SQL:2007?)
• Heutige Produkte
- sind weitgehend SQL-89 konform, teils SQL-92 Entry Level (nur
knapp über SQL-89!), NICHT SQL:1999
- bieten darüber hinaus zahlreiche „Spezialitäten“ und
Eigenheiten, die nicht normkonform sind bzw. vielleicht später von
der Norm erfasst werden (und dann in der Norm mit hoher Ws.
anders aussehen werden, als heute im Produkt x oder y!!)
Vorgehensweise im folgenden
DDL - Datendefinition mit SQL
- Datenänderung mit SQL (INSERT, DELETE, UPDATE)
DML
- Datenzugriff mit SQL (SELECT)
Datenbanksysteme 1
07.01.2013
241
5.4.1 Datendefinition mit SQL
N.B.: Vorrelationale Datenbanksysteme (z.B. IMS, ADABAS ...) besitzen
unterschiedliche Sprachen für Datendefinition/-manipulation,
vgl. DBD-Definition und DL/1 bei IMS
In SQL wurde erstmals der Versuch unternommen, Datendefinitionssprache und Datenmanipulationssprache „aus einem Guss“ zu entwerfen
grober Überblick zu den Datendefinitionsanweisungen in SQL
SQL92/
3
Datenbanksysteme 1
1
07.01.2013
(interne Ebene)
create index
alter index
drop index
...
konzeptuelle Ebene
create table
alter table
drop table
create domain
alter domain
drop domain
...
externe Ebene
create view
drop view
SQL99/...
2
242
CREATE TABLE Anweisung
Syntax in einfachster Form (Erweiterungen s.u.):
create table basisrelationenname
(spaltenname_1 wertebereich_1 [not null]
...
spaltenname_k wertebereich_k [not null])
• Erzeugen einer k-spaltigen Tabelle (Relation mit k Attributen) mit über
spaltenname_i/wertebereich_i definiertem Relationenschema (inkl.
„leerer Relation“)
• Tabelle ist nach dem create zunächst leer, d.h. enthält (natürlich)
keine Tupel
• Schemainformation wird im Datenbankkatalog abgelegt (teils, etwas
„großspurig“, auch als Data Dictionary bezeichnet)
- Datenbankkatalog besteht ebenfalls aus Tabellen (Verzeichnis
aller (mit create table erzeugten) Tabellen, aller Attribute, aller
benutzerdefinierten Wertebereiche etc. etc.)
- Katalogtabellen können mit SQL vom Benutzer gelesen werden
wie „normale“, benutzereigene Tabellen
Datenbanksysteme 1
07.01.2013
243
wesentlicher Vorteil von relationalen Datenbanksystemen im
Vergleich zu vorrelationalen, wo die Kataloginformation in
„kryptischer“, interner Form abgelegt ist!!!
„Kataloge sind ´im Prinzip´ normiert“ siehe LV Datenbankanwendungsentwicklung (i.w.Praktikum) im SoSe 2007
Sommersemester
• Wertebereiche (Datentypen) :
- integer (auch integer4, int)
- smallint (auch integer2)
- float(p) (auch kurz float)
- decimal (p[,q]) mit q Nachkommastellen
- character(n) (auch kurz char(n), bei n=1 auch char) für Strings
fester Länge n
- character varying(n) (auch kurz varchar(n)) für Strings variabler
Länge bis zur Max.länge n
- bit(n) oder bit varying(n) entsprechend für Bitfolgen
- date, time, timestamp für verschiedene Datums- und Zeitangaben
+ LOBs
Datenbanksysteme 1
07.01.2013
244
Bemerkung: Bzgl. der Datentypen sollte beim konkreten relationalen
Datenbanksystem (Produkt) immer die Produktliteratur zu Rate gezogen
werden. Oft existieren zusätzliche Datentypen (die dann aber (noch!?)
nicht von der SQL-Norm erfasst werden!!)
• Die not null Klausel verbietet das Auftreten von Nullwerten (Null
Values) in jenen Spalten, wo spezifiziert (standardmäßig, also wenn
not null nicht angegeben, sind Nullwerte erlaubt)
• Beispiel (vgl. Beispielrelation auf Folie 202):
create table Buch
(InvNr integer not null,
Titel varchar(30),
ISBN char(5),
SQL erzwingt
keine Schlüssel!
Autor varchar(40))
create table Ausleihe
(InvNr integer not null,
Name varchar(20))
Was fehlt gegenüber Folie 202?
- Primärschlüsseldefinition
- Fremdschlüsseldefinition(en)
Datenbanksysteme 1
07.01.2013
245
Merke:
- Die SQL-Norm erzwingt keine Primärschlüsseldefinition!!
- Tabellen dürfen also Duplikate aufweisen (Tupel, die in allen
ihren Attributwerten übereinstimmen)
Multimengeneigenschaft, „bags“ statt „sets“
- Duplikatfreiheit muss, wenn gewünscht, explizit durch
entsprechende Klausel (primary key, s.u., als eine Möglichkeit)
festgelegt werden, wird dann vom DBVS überwacht
Abweichen von der „reinen (relationalen) Lehre“!
• Beispiel (Erweiterung obigen Beispiels)
create table Buch
create table Ausleihe
(InvNr integer
(InvNr integer
Titel varchar(30),
Name varchar(20),
ISBN char(5),
primary key (InvNr),
Autor varchar(40),
foreign key (InvNr),
primary key (InvNr))
references Buch (InvNr))
vielfältige Semantiken spezifizierbar (später bzw. kennen wir schon )
Not null implizit durch die primary key-Klausel festgelegt (seit SQL-92)
Datenbanksysteme 1
07.01.2013
246
Weitere Zusätze zur CREATE TABLE Anweisung
• Primärschlüssel-/Fremdschlüsseldefinition zielt auf Integritätssicherung
ab (wobei Primärschlüssel-/[Fremdschlüssel]eigenschaft im relationalen
modellinhärent ist, „ohne“ sollte also gar nicht zulässig sein ...)
• Auch weitere Zusätze auf Integritätssicherung abzielend:
(eines der Themen in Zshg. mit relationalen Datenbanksystemen in den
letzten Jahren!!)
SQL92ff.!!
a) default-Klausel zur expliziten, benutzerseitigen Festlegung eines
Default-Werts für eine Tabellenspalte
Beispiel:
create table Buch
(InvNr integer primary key,
Titel varchar(30),
ISBN char(5),
Autor varchar(40) default ´Küspert´)
• Falls Primärschlüssel nur aus einem Attribut besteht, kann primary key
Klausel „in-line“ (wie oben) angegeben werden
• Immer, wenn ein Buch eingefügt wird, ohne dass der Autor spezifiziert
ist, wird vom DBVS der Default ´Küspert´ eingetragen 
Datenbanksysteme 1
07.01.2013
247
b) check-Klausel zur Spezifikation von Integritätsbedingungen
(nicht nur) für einzelne Attributwerte
Beispiel:
create table Buch
(InvNr integer primary key
check (InvNr between 5 and 9999),
Titel varchar(30),
ISBN char(5),
Autor varchar(40) default ´Küspert´
check (Autor ´Kujau´))
• Check-Klausel ermöglicht Angabe von Prädikaten, die den zulässigen
Wertebereich für Tabellenspalten weiter einschränken
• Prüfung erfolgt durch das DBVS bei Tupeleinfügung (SQL Insert)
sowie Wertänderung in Tupel (SQL Update)
im Fall einer drohenden
Konsistenzverletzung wird die verursachende Anweisung (Insert,
Update) nicht ausgeführt, also zurückgewiesen (zurückgesetzt)
Anweisungen sind stets atomar (auszuführen)
Hinweis: Die Check-Klausel gibt´s auch noch in anderer Erscheinungsform, indem sie der Tabellendefinition insgesamt zugeordnet ist und
nicht nur einem Attribut
Datenbanksysteme 1
07.01.2013
248
CREATE DOMAIN Anweisung (SQL92+)
ermöglicht die Vereinbarung benutzerdefinierter Wertebereiche
(Domänen):
• Check-Klausel (wie oben beschrieben): Schränkt Wertebereich für
einen Standarddatentyp weiter ein
• CREATE DOMAIN Anweisung: Erlaubt Definition neuer
(benutzerdefinierter) Datentypen!!
 wie man´s ja z.B. aus Programmiersprachen kennt)
• Vereinbarung eines benutzerdefinierten Datentyps darf zusammen mit
default-Klausel und check-Klausel auftreten.
• Beispiel:
create domain Fachgebiet varchar(20)
default ´Informatik´
create table buch
(InvNr integer primary key,
Titel varchar(30),
Gebiet Fachgebiet,
ISBN char(5),
Autor varchar(40))
d.h. das Attribut Gebiet ist vom (benutzerdefinierten) Typ Fachgebiet
Datenbanksysteme 1
07.01.2013
249
Verwendung einer CREATE DOMAIN Anweisung zusammen mit einer
check-Klausel:
create domain Fachgebiet varchar(20)
default ´Informatik´
check (value in (´Informatik´, ´Mathematik´, ´BWL´, ´VWL´))
create table buch
(...
Gebiet Fachgebiet,
...)
Was wäre, wenn wir in der obigen Werteauflistung (value in ...) ´Informatik´
vergessen hätten?
Einfügung eines Buch-Tupels ohne Angabe eines Werts für Gebiet
scheitert: DBVS generiert Default-Wert ´Informatik´, kann diesen aber nicht
einfügen, da check-Klausel dem entgegensteht
Hinweis nochmals: default-Klausel, check-Klausel und create domain
Anweisung sind erst ab SQL92 in die SQL-Norm aufgenommen
worden, d.h. nicht in allen relationalen Datenbanksystemen in
dieser Form realisiert
Datenbanksysteme 1
07.01.2013
250
ALTER TABLE Anweisung
Zur Änderung von mit create table angelegten Relationsschemata, d.h. zur
Schemaänderung (Schemaevolution)
• Hinzufügen neuer Attribute (Spalten) zu einer Tabelle
• Löschen von Tabellenspalten
• (Sehr eingeschränktes) Ändern von Eigenschaften vorhandener Attribute
Im einzelnen
a) alter table basisrelationenname
add spaltenname wertebereich
- Einfachste Form des Hinzufügens neuer Spalten
- Bereits in SQL89 vorhanden
- Beispiel:
alter table Buch add Einkaufspreis decimal (5,2)
Wirkung:
• Tabelle Buch erhält neue Spalte Einkaufspreis
• Spalte wird bei allen in der Tabelle aktuell vorhandenen Tupeln mit
null besetzt (zumindest „logisch“)
• Spalte (Name, Wertebereich, Tabellenzuordnung) wird im Katalog
eingetragen
Datenbanksysteme 1
07.01.2013
251
b) Weitere Zusätze zum alter table ... add ...
erlaubt seit SQL92: default-Klausel, check-Klausel (wie oben eingeführt).
• Beispiel:
alter table Buch add Gebiet varchar(20)
default ´Informatik´
check (Gebiet in (´Informatik´, ´Mathematik´, ´BWL´, ´VWL´))
(vgl. Folie 241)
c) alter table basisrelationenname
drop spaltenname [...]
• Löschen einer Tabellenspalte
• Erst SQL92-Bestandteil
• Mit weiterem Zusatz (...) versehen, den wir hier noch nicht diskutieren
• Beispiel:
alter table Buch drop ISBN
Wirkung:
„Umgekehrt“ zu add (s.o.)
Datenbanksysteme 1
07.01.2013
252
d) alter table basisrelationenname
alter spaltenname
{set default_definition | drop default}
•Anbringen/Ändern einer Default-Angabe
•Wegnehmen einer Default-Angabe
•Beispiele:
alter table Buch alter Gebiet
set default ´BWL´
Der Default-Wert für die Spalte Gebiet wird (von ´Informatik´) nach
´BWL´ geändert
alter table Buch alter ISBN
set default ´0-000´
Die Spalte ISBN erhält erstmals einen Default-Wert
alter table Buch alter Gebiet
drop default
Der Spalte Gebiet wird der Default-Wert weggenommen
Datenbanksysteme 1
07.01.2013
253
Abschließende Bemerkungen zu ALTER TABLE
• Man würde sich viel mehr Möglichkeiten wünschen, vor allem für das
alter table ... alter ...
Änderung von Datentypen!!
 in der SQL-Norm bisher nicht unterstützt!! (warum wohl nicht?)
• Die meisten/viele existierenden relationalen Datenbanksysteme
unterstützen nur alter table ... add ...
„unterhalb“ der SQL92-Norm
• Schemaänderung sind generell ein heikles Thema bei (relationalen
und nichtrelationalen) Datenbanksystemen:
- Was geschieht mit den vorhandenen Daten (Tupeln in Tabellen),
wenn das Schema sich ändert?
• Sofortige Transformation in neues Format
• Verzögerte Transformation in neues Format
• Was, wenn Transformation scheitert / nicht automatisch
durchführbar?
- Auswirkungen auf vorhandene Anwendungen (Programme,
gespeicherte SQL-Anfragen)
Sichten (Views) können teilweise
helfen (s.u.)
Datenbanksysteme 1
07.01.2013
254
DROP TABLE Anweisung
Syntax:
drop table basisrelationenname {restrict|cascade}
SQL92/99/ff.
(üblicherweise nicht als „normale“ DB-Veränderung in Trans. erlaubt)
• Wirkung:
- Die Tabelle basisrelationenname wird gelöscht
(Relationsschema und Daten (Tupel))
- Die entsprechenden Einträge zur Schemabeschreibung
(Relationsname, Attributnamen, Wertebereichsangaben ...) werden
aus dem Katalog entfernt
• restrict-Angabe: Das Löschen unterbleibt, wenn noch „Bezugnahme“
von außen auf die Tabelle via Integritätsbedingungen/Sichtdefinitionen
• cascade-Angabe: Bezugnehmende Integritätsbedingungen/Sichten
werden mitgelöscht
• Beispiele:
drop table Buch restrict
drop table Buch cascade
Datenbanksysteme 1
07.01.2013
255
CREATE / DROP INDEX Anweisung
Internes Schema
( SQL-Norm)
• Erzeugen/Löschen eines Zugriffspfads (Index), der (u.a.) zum
schnellen Zugriff auf bestimmte Tupel dient bei gegebenem
Attributwert
• Syntax:
create [unique] index indexname
on basisrelationenname
(spaltenname_1 ordnung_1,
Baumförmige
Indexe
(B*-Baum)
...
spaltenname_k ordnung_k)
wobei ordnung_i die Werte asc (für „ascending“)
und desc (für „descending“) annehmen kann
unique-Angabe spezifiziert, dass die Werte im Index eindeutig
sein müssen (keine Duplikate)
• Eine primary key Angabe beim create table erzeugt implizit einen
unique index auf dem Primärschlüssel (Attribut oder Attributkombination)!
(dies ist keine Forderung der (SQL-)Norm, aber Realität
in Produkten (warum??)
Effizienz beim Überprüfen der Werteeindeutigkeit!!
Datenbanksysteme 1
07.01.2013
256
• Beispiel (vgl. Folie 202):
Index erzeugt
zum Primärschlüssel
InvNr von Buch
B*-Baum
z.B.
0007
...Dr. No...
1201
4711
4712
4717
...Datenbanken...
...Datenbanken...
...Objektbanken...
...Pascal...
Nur zur Veranschaulichung! Indexrealisierung ist auf der konzeptuellen
und externen Ebene (3-Ebenen-Architektur) nicht sichtbar! „Im Grunde“
ist sogar die Existenz/Nichtexistenz eines Index auf der konzeptuellen/
externen Ebene nicht sichtbar! Vorteil?
Datenunabhängigkeit: Indexe können erzeugt/gelöscht
werden, ohne dass Anwender etwas davon merkt (außer
am Leistungsverhalten (Performance)!)
Datenbanksysteme 1
07.01.2013
257
Beispiele (Fortsetzung)
• create unique index Titel_Index
on Buch (Titel asc)
kann nicht erfolgreich durchgeführt werden, da keine
Werteeindeutigkeit in Spalte Titel
 unique-Angabe weglassen
• create index Autoren_Index on Buch (Autor asc)
ermöglicht schnellen Zugriff auf die zu einem Autor gehörigen Bücher
• create index Autoren_Titel_Index
on Buch (Autor asc, Titel asc)
Index enthält Werte-Paare (Autor, Titel); gut etwa, wenn Anfragen
häufig mit Autoren- und Titelangabe erfolgen („ist das Pascal-Buch
von Wirth in der Bibliothek vorhanden?“)
• create index InvNr-Index on Ausleihe (InvNr asc)
unnötig, da InvNr Primärschlüssel in Ausleihe und Index somit
vorhanden (sogar unique Index)
Datenbanksysteme 1
07.01.2013
258
• Angenommen, InvNr wäre nicht als Primärschlüssel in Ausleihe
vereinbart, warum erscheint Index auf InvNr trotzdem sehr sinnvoll?
InvNr ist Fremdschlüssel in Ausleihe mit Bezug auf den
Primärschlüssel (InvNr) von Buch; DBVS muss deshalb
Integritätsüberwachung vornehmen, z.B. sicherstellen, dass ein
Buch nicht gelöscht werden darf, wenn noch ausgeliehen
erfordert aus Effizienzgründen Index
von realen DBVSen üblicherweise nicht erzwungen!
• drop index Autoren_Titel_Index
Index wird gelöscht
Abschließende Bemerkungen zur Index-Thematik
• Seit SQL92 gibt es eine unique-Spezifikation, die genau so eingesetzt
werden darf, wie die primary key-Spezifikation (vgl. Folie 237f.)
adäquates Mittel zur Spezifikation von Schlüsselkandidaten (aber nicht
in allen Produkten heute unterstützt
Vereinbarung von
Schlüsselkandidaten mittels create unique index
unschön!!)
Datenbanksysteme 1
07.01.2013
259
5.4.2 Datenänderung mit SQL
Datenänderungsanweisungen in SQL
(DML)
insert
update
delete
3
1
2
Alle Änderungsanweisungen sind dazu in der Lage, mengenorientiert
zu arbeiten (d.h. mehr als ein Tupel mit einer Anweisung einzufügen, zu
ändern, zu löschen)
Benutzungsvorteile und Effizienzvorteile
UPDATE-Anweisung
Syntax (in vereinfachter Form):
update basisrelationenname
set spaltenname_1 = ausdruck_1
...
set spaltenname_k = ausdruck_k
[where bedingung]
In allen Tupeln der basisrelation, die die bedingung erfüllen, werden die
Attributwerte wie angegeben ersetzt („überschrieben“).
Datenbanksysteme 1
07.01.2013
260
MengenUpdate
Beispiele:
• Der Titel des Buchs mit der InvNr 0007 (´Dr. No´) soll in ´Dr. Yes´
geändert werden:
update Buch
set
Titel = ´Dr. Yes´
where InvNr = 7
oder
update Buch
set
Titel = ´Dr. Yes´
where Titel = ´Dr. No´
Ein einzelnes Tupel wird geändert (weil in unserem konkreten Beispiel
(Folie 202) genau ein Tupel die Bedingung (where-Klausel) erfüllt).
• Für alle Bücher mit InvNr > 4710 soll der Autor auf ´Dr. Seltsam´
gesetzt werden:
update Buch
set
Autor = ´Dr. Seltsam´
where InvNr > 4710
Datenbanksysteme 1
07.01.2013
261
• Für alle Bücher soll die InvNr auf 9999 gesetzt werden:
update Buch
set InvNr = 9999
(where-Klausel nicht benötigt, da alle Tupel der Relation geändert
werden sollen)
Was passiert?
Operation wird vom DBVS nicht ausgeführt / wieder ungeschehen gemacht, da sonst Konsistenzverletzung („duplicate key“)
fürs DBVS nicht
ganz trivial
Entsprechend z.B. bei:
(Fehlerbehandlungsupdate Buch
maßnahme)
set
InvNr = 9999
where InvNr = 0007
• Gegeben sei Angest-Relation mit Attributen Name, Gehalt ... Gib allen
Angestellten eine Gehaltserhöhung um EUR 1000,update Angest
set
Gehalt = Gehalt + 1000
Datenbanksysteme 1
07.01.2013
262
DELETE-Anweisung
Syntax (in vereinfachter Form):
delete
from basisrelationenname
[where bedingung]
Alle Tupel der basisrelation, die die bedingung erfüllen, werden gelöscht.
Beispiele:
• Das (von Herrn Meyer ausgeliehene) Buch mit der InvNr 4711 wurde
zurückgegeben und soll deshalb aus der Relation Ausleihe gelöscht
werden:
delete
from Ausleihe
where InvNr = 4711
(könnte man alternativ schreiben
where Name = ´Meyer´?)
 würde in unserem Beispiel 2 Tupel löschen!
Datenbanksysteme 1
07.01.2013
263
• Die Bibliothek hat die Hoffnung aufgegeben, die ausgeliehenen Bücher
jemals zurückzuerhalten und löscht deshalb den gesamten Inhalt der
Relation Ausleihe:
delete
from Ausleihe
(eigentlich müsste man die entsprechenden Bücher auch noch aus der
Relation Buch löschen, da „unwiderruflich verschwunden“)
• Kann die Ausführung einer delete-Anweisung zur Verletzung von
Integritätsbedingungen führen?
(vgl. update-Anweisung mit „duplicate key“ oben)
delete
from Buch
where Titel = ´Dr. No´
verletzt Fremdschlüsselbedingung zwischen Ausleihe und Buch, da
Fremdschlüsselwert 0007 für InvNr in Ausleihe anschließend „ins
Leere“ zeigen würde. Was tun?
Datenbanksysteme 1
07.01.2013
264
INSERT-Anweisung
Erscheinungsformen
Einfügen von Tupeln
als Konstanten
Einfügen von Tupeln
als ´berechnete Werte´
aus (anderen) Relationen
a
b
a) Syntax (in vereinfachter Form):
insert
into basisrelationenname [(spaltenname_1, ..., spaltenname_k)]
values (konstante_1, ..., konstante_k)
Beispiele:
Einfügen eines neuen Tupels in die Relation Buch:
insert
into
Buch (InvNr, ISBN)
values (4867, ´3-222´)
Was geschieht mit den Werten von Titel und Autor?
null gesetzt
Datenbanksysteme 1
07.01.2013
werden auf
265
• Einfügen eines neuen Tupels in die Relation Buch wie folgt:
insert
into
Buch (Titel, ISBN, Autor)
values (´Modula-4´, ´3-222´, ´N. Wirth Nachf.´)
Was passiert?
 InvNr-Wert fehlt, null-Setzung jedoch nicht erlaubt wg. Primärschlüssel
insert-Anweisung wird vom DBVS zurückgewiesen
mit Fehlermeldung
• ... und noch ein Versuch:
(klappt nur weil SQL keine
insert
Spaltenmengensemantik)
into
Buch
values (4711, ´Modula-4´, ´3-222´, ´N. Wirth Nachf.´)
Attributliste in into-Klausel weggelassen
(spaltenname_1, ..., spaltenname_k)
Werte der values-Klausel
werden Attributen in der Reihenfolge zugeordnet, wie die Attribute
beim create aufgeführt worden waren (4711
InvNr, ´Modula-4´
Titel, ´3-222´
ISBN, ´N. Wirth Nachf.´
Autor)
nicht ganz im Sinn der „reinen Lehre“ ...
Datenbanksysteme 1
07.01.2013
266
Funktioniert die insert-Anweisung?
 nein, „duplicate key“
• ... (vor)letzter Versuch:
insert
into
Ausleihe
values (4718, ´Küspert´)
scheitert, da kein zugehöriger Primärschlüsselwert (InvNr = 4718) in
Relation Buch existiert
(verletzt Fremdschlüsselbedingung)
b) Syntax (in vereinfachter Form)
insert
into basisrelationenname [(spaltenname_1, ..., spaltenname_k)]
lesende SQL-Anfrage
Bedeutung der Spaltennamenliste in der into-Klausel wie bei a),
einzufügende Tupelmenge kann aber über SQL-Anfrage beliebig
„berechnet“ werden
Datenbanksysteme 1
07.01.2013
267
• Beispiele: Angenommen, es gibt (zusätzlich zu Buch/Ausleihe) eine
Relation Ausleihe_uralt, in der jene Bücher verzeichnet sind, die
schon länger als 2 Jahre ausgeliehen sind. Der Inhalt dieser Relation
soll nun, damit er nicht in Vergessenheit gerät, wieder in Ausleihe
übernommen werden (Ausleihe und Ausleihe_uralt mögen strukturell
identisch (= gleiches Relationsschema) sein):
insert
into
Ausleihe
(select from Ausleihe_uralt)
alle Tupel aus Ausleihe_uralt werden komplett ( = alle Spalten)
nach Ausleihe übernommen (kopiert)
• „Tabellenverdoppler“
insert
into
Ausleihe
(select from Ausleihe)
Was passiert?
 scheitert wegen „duplicate key“!
Datenbanksysteme 1
07.01.2013
268
5.4.3 Datenbankzugriff (Lesen) mit SQL
• Fundamental für den Datenbankzugriff mit SQL ist das SELECTFROM-WHERE-Konstrukt (kurz SFW-Konstrukt), das durch weitere
Klauseln (GROUP BY, HAVING; ORDER BY) ergänzt wird.
- SELECT-Klausel legt die Ergebnisstruktur (Projektionsliste) einer
Anfrage fest
- FROM-Klausel legt fest, woher die Anfrage die Daten holen soll,
d.h. aus welchen Relationen
besser: welche Relationen zur Beantwortung der Anfrage
gebraucht werden!
- WHERE-Klausel legt Selektionsbedingung fest (welche Daten
genau geholt werden sollen, Auswahl) und wie ggf. eine Datenverknüpfung über mehrere Relationen dabei erfolgen soll (Join)
 SQL kennt seit SQL:1992 hierfür – zusätzlich – eigenen
versteckter
JOIN-Operator à la Relationenalgebra
expliziter
Datenbanksysteme 1
07.01.2013
269
Einordnung von SQL bzgl. Normungsstands*
typischer heutiger SQL-Dialekt
in Produkten
SQL3
(SQL-99)
SQL2
(SQL-92)
SQL1
(SQL-89)
SQL0
(SQL-87)
• Die von den Normungsgremien (ISO, DIN ...) verabschiedeten bzw.
noch zu verabschiedenden SQL-Sprachumfänge werden immer
umfangreicher; SQLi+1 schließt jeweils SQLi ein (wg. Aufwärtskompatibilität)
• Heutige Produkte bieten typischerweise Obermenge von SQL-89, aber
noch kein volles SQL-92; dafür ist aber meist etwas Funktionalität
vorhanden, die schon über SQL-92 hinausreicht (Bsp.: Trigger)
* entnommen aus: K. Neumann: Datenbanktechnik für Anwender.
Carl Hanser Verlag, München Wien, 1996
Datenbanksysteme 1
07.01.2013
270
Syntax der SQL-SELECT-Anweisung* **
SELECT
ziel-liste
FROM
relation
ALL
variable
,
DISTINCT
WHERE
where-Bedingung
GROUP BY
attr-ref
attr-ref
,
HAVING
nur die Ergebnisdarstellung betreffend
have-Bedingung
ORDER BY
attr-ref
ASC
DESC
* entnommen aus: K. Neumann:
Datenbanktechnik für Anwender.
Carl Hanser Verlag, München Wien, 1996
** vereinfacht!!
Datenbanksysteme 1
07.01.2013
attr-ref
,
271
Syntaktischer Aufbau einer Beispielanfrage
Gesucht sind alle Angestellten (vgl. Angest-Projekt-Mitarbeit-Datenbank
von Folie 193)
und zwar jeweils Name und Wohnort -, die in einer Abteilung mit AbtNr>3
arbeiten. Die Ausgabe soll nach Name absteigend sortiert erfolgen,
Duplikate sollen nicht eliminiert werden.
SELECT ALL
Name, Wohnort
FROM
Angest
WHERE
AbtNr > 3
ORDER BY
Name
ziel-liste
relation
where-Bedingung
DESC
attr-ref
Ergebnis ist somit eine Multimenge („bag“), die Duplikate erlaubt
streng genommen liegt das Anfrageergebnis somit nicht mehr im „rein
relationalen“ Modell (Mengen!)
es war aber schon erwähnt worden,
dass dieses „rein relationale“ Modell von SQL und zugehörigen
Produkten nicht erzwungen wird (warum?)
Funktionalität!
Performance!!
Datenbanksysteme 1
07.01.2013
272