Anfrageoptimierung - Fachbereich Mathematik und Informatik

Anfrageoptimierung
Bernhard Seeger
Fachbereich Mathematik und Informatik
Universität Marburg
Seite 1
Organisation
Voraussetzungen
‰
‰
‰
Datenstrukturen und Algorithmen
Java
Wünschenswert: Datenbanksysteme I , Compilerbau
Vorlesungenstermine
‰
Dienstag 9-11h, Hörsaal IV
Übungen
‰
‰
Tutor: Michael Cammert
Termine: ?
Homepage der Vorlesung
‰
http://www.mathematik.uni-marburg.de/~seeger/vor04SSopti2.shtml
Seite 2
Übersicht
‰
‰
‰
‰
‰
‰
‰
‰
Architekturen von DBS
Einführung in die relationale Algebra und SQL
Übersetzung von SQL
Logische und physische Anfrageoptimierung
Implementierung physischer Operatoren
Parallele Anfrageverarbeitung
Anfrageoptimierung zur Laufzeit
Anfrageverarbeitung auf Datenströmen
Seite 3
Literaturliste
‰
‰
‰
‰
T. Härder, E. Rahm: "Datenbanksysteme - Konzepte und Techniken der Implementierung",
Springer, 2001
H. Garcia-Molina, J. Ullman, J. Widom: "Database System Implementation", Prentice Hall,
1999
A. Kemper, A. Eickler: "Datenbanksysteme'', Oldenbourg, 4. Auflage, 2001.
G. Saake, A. Heuer: "Datenbanken - Implementierungstechniken", MITP-Verlag, 1999
Seite 4
Zeitschriften und Tagungsbände
ACM TODS Transactions on Database Systems, ACM-Publikation (vierteljährl.)
THE VLDB Journal
VLDB Foundation (vierteljährlich)
IEEE Trans. on Knowledge and Data Engineering (6-mal pro Jahr)
Information Systems
Pergamon Press (6-mal pro Jahr)
ACM Computing Surveys
ACM-Publikation (vierteljährlich)
Tagungen im Bereich Datenbanksysteme
SIGMOD Proceedings, jährliche Konferenz der ACM Special Interest Group on
Management of Data
VLDB Proceedings, jährliche Konferenz “Very Large Data Bases” (ACM, IEEE, …)
IEEE Data Engineering, jährliche Konferenz der Datenbankgruppe von IEEE
GI Tagungsbände der jährlichen Tagungen der Gesellschaft für Informatik,
sowie Tagungen innerhalb des FA 2.5 Informationssysteme
Seite 5
Einführung
Datenbanktechnologie
Konzepte, Methoden, Werkzeuge und Systeme für die
‰ dauerhafte
Lebensdauer Daten > Dauer Erzeugungsprozeß
‰ zuverlässige
Integrität, Konsistenz, Verlustsicherheit
‰ unabhängige
wechselseitige Änderungsimmunität AWP-DB
Verwaltung und
‰ komfortable
“höhere” abstrakte Schnittstelle
‰ flexible
Ad-hoc-Zugriffsmöglichkeit
Benutzung von
‰ großen
Größe Daten >> Größe Hauptspeicher
‰ integrierten
von/für mehrere Anwendungen,
kontrollierte Redundanz
‰ mehrfachbenutzbaren
paralleler Zugriff
Datenbasen
Seite 6
DBS-Konzepte
‰
‰
‰
‰
Ad-hoc-Anfragen
Zugriffspfade
Speicherungsstrukturen
Verteilung
‰
‰
Zugriffskontrolle
Programmkopplung
Datenschutz,
Spracheneinbettung
‰
‰
‰
Datenintegration
anwendungsorientierte Datenbeschreibung
Datenunabhängigkeit
Datenmodelle,
Schemata,
Sichten
‰
‰
‰
‰
Konsistenzkontrolle
Mehrbenutzerbetrieb
Datenintegrität
Wiederanlauf
Transaktionen
Leistungsaspekte,
Verteilung
Seite 7
Relationale Datenbanksysteme
Verwendung von Relationen als die zentrale Datenstruktur
‰
‰
Modellierung
Anfrageverarbeitung
Anfragesprache SQL
‰
‰
‰
Eine Anfrage
–
Verarbeitung von Relationen
–
Ergebnis ist wieder eine Relation
deskriptive Sprache
–
Beschreibung des Ergebnis
–
keine explizite Vorschrift für die Berechnung des Ergebnis
Anfrageübersetzung und -optimierung
–
Transformation der SQL-Anfrage in einen Anfrageplan, der aus
physischen Operatoren der relationalen Algebra besteht.
Seite 8
AnfrageoptimierungUniversität Marburg
Warum sind Kenntnisse in dem Bereich wichtig?
‰
Typische Problemstellung aus der Praxis:
–
Effiziente Abbildung eines komplexen Modells (OO, XML) auf ein
relationales DBMS
–
Übersetzung einer Anfragesprache nach SQL (“Cross-Compiler)
Anforderung dabei:
–
‰
Erfolgreiche Bearbeitung einer solchen Aufgabe erfordert
–
9.
Effizienz
Kenntnisse über die Interna der relationalen Datenbanksysteme
Das Relationale Datenmodell
1. Das Relationale Datenmodell
‰
geht zurück auf Codd (1970):
E. F. Codd: A Relational Model of Data for Large Shared Data Banks. Comm. of the ACM
13(6): 377-387(1970)
‰ Kommerzielle DBMS wie z. B.
–
Oracle, SQL Server, Sybase, Informix, DB2
und nicht-kommerzielle Systeme wie z. B.
– Interbase, mySQL, PostgreSQL
basieren in ihren Grundzügen auf dem relationalen Datenmodell
‰
Gründe für den Erfolg des relationalen Datenmodells
–
Einfache Datenstruktur: Relation (vergleichbar mit einer Tabelle)
– mengenorientierte Verarbeitung der Daten
–
wenig Grundoperationen zur Verabeitung und dadurch eine klare Semantik
–
formale Theorie zur Modellierung und Anfrageverarbeitung
Seite 10
Das Relationale Datenmodell
1.1 Grundlegende Strukturen
Eine Relation besteht aus folgenden Komponenten:
‰
‰
einem Namen R,
ein Relationenschema RSR
–
Ein Relationenschema definiert die spezifische Struktur der Relation. RSR wird durch
eine Liste von k Attributen (A1,…,Ak) beschrieben, wobei zu jedem Attribut Aj es einen
Wertebereich Dj = dom(Aj) gibt. Der Wertebereich der Relation ist dann
dom(R) = D 1 × D 2 … × D k , k ≥ 1
–
‰
Der Wertebereich eines Attributs ist endlich und besteht aus atomaren Elementen, die
keine weitere Struktur besitzen. Beispiele hierfür sind Elemente vom Typ int oder String.
eine Instanz IR einer Relation R ist eine Teilmenge des kartesischen Produkts der
Wertebereiche:
I R ⊆ dom(R)
Oft sind weitere semantische Eigenschaften bekannt, welche die Anzahl der möglichen
Instanzen reduzieren.
–
Ein Tupel (Zeile, Datensatz) entspricht einem Element einer Relationeninstanz. Ein
Tupel hat somit für jedes Attribut genau einen Wert.
Seite 11
Das Relationale Datenmodell
Notation
‰
‰
Tupel: (Franz, 2000, 1966)
Dies könnte ein Tupel einer Relation Personal mit den Attributen Name, Gehalt und
Geburtsjahr sein.
Relationenschema einer Relation R: RSR = (A1,…,Ak)
–
–
‰
Für eine Relation R mit k Attributen bezeichnet k die Stelligkeit (Grad) von R.
In der Literatur findet man auch häufig die Schreibweise R(A1,…,Ak).
Relationeninstanz einer Relation R: IR = {t1,…,tn}, wobei ti die Tupel sind.
Oft verwendete Sprechweisen
‰
‰
Der Begriff Relation wird oft auch für die Instanz einer Relation verwendet.
Ist klar, um welche Relation es sich handelt, wird der Index bei der Instanz und beim Schema
einfach weggelassen.
‰ Häufig wird eine Relation als Tabelle dargestellt:
Pers1
Name
Gehalt GebJahr
Carlo
2000
1981
Beate
3000
1977
Pers1 ist der Relationennamen.Name, Gehalt, Gebjahr sind die Attributsnamen.
Seite 12
Das Relationale Datenmodell
Gleichheit von Relationen
‰
Seien R und S zwei Relationen. Dann sind R und S schemakonform, falls
– dom(R) = dom(S)
– und RSR = RSS.
‰
Zwei Relationen R und S sind gleich, wenn R und S schemakonform sind und IR = IS.
Bemerkungen
‰
Relationen entsprechen dem Variablenbegriff einer Programmiersprache. Relationen sind
Speicherbereiche im Rechner, die über den Relationennamen angesprochen werden.
Seite 13
Das Relationale Datenmodell
Reihenfolge der Attribute
‰
Bei unserem bisherigen Relationenbegriff hat die Reihenfolge der Zeilen (Tupel) keine
Bedeutung. Eine Instanz wird immer als eine Menge von Tupeln betrachtet. Die Reihenfolge
der Attribute ist aber von Bedeutung, was insbesondere dazu führt:
Pers1
‰
Name
Carlo
Beate
Pers2
=
Gehalt Name
2000
Carlo
3000
Beate
GebJahr
1981
1977
Um die Gleichheit solcher Relationen zu erzielen, betrachten wir folgende Definition für eine
Relationeninstanz:
–
–
Sei R eine Relation und RS R = { A 1, …, A k } das zugehörige Schema. Wir gehen jetzt
von einer Menge von Attributen aus.
Die zu R gehörende Instanz IR ist eine endliche Menge von totalen Abbildungen der
k
Form t: RS R →
‰
Gehalt GebJahr
2000
1981
3000
1977
∪ dom(Ai) , wobei t(Aj) ∈ dom(Aj) gilt.
i=1
Diese Definition liefert uns den gewünschten Effekt! Je nach Sachverhalt werden wir auf die
eine oder die andere Defintion zurückgreifen (siehe auch Literatur).
Seite 14
Das Relationale Datenmodell
Beispiel:
Städte Name
Einwohner Land
München 1.211.617 Bayern
Bremen 535.058
Bremen
‰ Relationenschema der Relation Städte:
{Name, Einwohner, Land} mit dom(Name) = String[40], dom(Einwohner) =
INTEGER und dom(Land) = String[40]
‰ Ausprägungen: t1 und t2
t1(Name) = München, t1(Einwohner) = 1211617 und t1(Land) = Bayern
t2(Name) = Bremen, t2(Einwohner) = 535058 und t2(Land) = Bremen
Datenbank:
‰
‰
Analog zu einer Relation läst sich eine Datenbank durch drei Eigenschaften beschreiben:
–
Name
–
Datenbankschema: Menge der Relationenschemata
–
Datenbankinstanz: Menge der Instanzen der Relationen
Viele der somit beschreibaren Datenbankinstanzen können nicht auftreten. Es ist deshalb
sinnvoll die möglichen Datenbankinstanzen durch semantische Bedingungen einzuschränken.
Seite 15
Das Relationale Datenmodell
Einfache Integritätsbedingungen
‰
Integritätsbedingungen sind semantische Eigenschaften einer Relation, welche die Menge der
möglichen Instanzen einschränken. Im Folgenden werden wir zwei wichtige Bedingungen
erläutern. Eine detailierte Diskussion folgt später.
Notation
‰
Sei R eine Relation und RS das zugehörige Schema. Sei X ⊆ RS . Dann bezeichnet t[X] das
Tupel t eingeschränkt auf X. Ist X = {A}, so schreiben wir kurz t[A] (statt t[{A}].
Nullwerte
‰
‰
Jeder Wertebereich eines Attributs enthält den Wert NULL.
Gilt für ein Tupel t und einem Attribut A einer Relation t[A] == NULL ist dies
folgendermaßen zu interpretieren, dass ein gültiger Wert nicht existiert oder nicht bekannt ist.
–
Es gilt für t 1, t 2 ∈ I R mit t 1 [ A ] = NULL und t 2 [ A ] = NULL stets:
t1 ≠ t2
‰
Sei R eine Relation und A ∈ RS R . Dann kann explizit gefordert werden, dass NULL nicht in
dom(A) liegt.
Seite 16
Das Relationale Datenmodell
Schlüssel
‰
X ⊆ RS wird als Schlüssel bezeichnet, wenn folgende Bedingungen erfüllt sind:
–
Eindeutigkeit: Für alle (real möglichen) Relationeninstanzen I der Relation R gilt: Für
zwei beliebige Tupel t1 und t2 aus I gilt:
t1 [ X ] = t2 [ X ] ⇒ t1 = t2
‰
‰
–
Minimalität: es gibt kein Y ⊂ X ( Y ≠ X ), so dass die Eindeutigkeit erfüllt ist.
Wenn mehrere Teilmengen eines Relationenschemas Schlüssel sind, wird einer von diesen als
Primärschlüssel ausgezeichnet.
–
Die Attribute des Primärschlüssels werden im Schema durch Unterstreichen
hervorgehoben.
Es folgt aus der Eindeutigkeit, dass Nullwerte in Schlüsselattributen nicht erlaubt sind.
Seite 17
Das Relationale Datenmodell
1.2 Die relationale Algebra
Algebra:
‰
gegeben eine Menge N (“Anker der Algebra”)
‰
Menge von Operationen { σ 1 ,… ,σ n } der Form σ j :N k → N
Relationale Algebra
‰
‰
Anker ist die Menge aller Relationen
Insgesamt gibt es 6 Grundoperationen.
Seite 18
Das Relationale Datenmodell
1.2.1 Grundoperationen der relationalen
Algebra
‰
Gegeben zwei Relationen R(A1,…,Ar) und S(B1,…,Bs) mit Stelligkeit r und s.
–
‰
Vereinigung: R ∪ S
–
Voraussetzung: R und S sind schemakonform.
–
‰
RS R ∪ S = RS R . und I R ∪ S = I R ∪ I S
Differenz: R – S
–
Voraussetzung: R und S sind schemakonform.
–
‰
Zur Erleichterung der Definition sei hier angenommen, daß die Attribute der Relationen
geordnet sind. Dann gilt:
IR = {(a1,…,ar) | ai ∈ dom(Ai), 1 ≤ i ≤ r} und IS = { (b1,…,bs) | bi ∈ dom(Bi), 1 ≤ i ≤ s}
RS R – S = RS R . und I R – S = I R – I S
Kartesisches Produkt: R × S
–
Ann.: RS R ∩ RS S = ∅
–
RS R × S = RS R ∪ RS S , I R × S = I R × I S
Seite 19
Das Relationale Datenmodell
‰
Projektion: π i
–
RS π
–
Iπ
1, …, i m
i 1, …, i m ( R )
( R ) mit 1 ≤ i j ≤ r .
= { A i , …, A i }
1
m


=
∃
∈
:
c
=
a
für
j
=
1…m
(
c
,
…
,
c
)
(
a
,
…
,
a
)
I
 1

R
j
ij
m
1
r
i 1, …, i m ( R )


Statt Indizes können auch Attributsnamen verwendet werden (X = {A1,A2}):
π X ( R ) = π A , A ( R ) = π 1, 2 ( R )
1 2
‰
Selektion: σ F ( R )
Dabei ist F eine Boolesche Funktion (Prädikat), die sich folgendermaßen zusammensezt:
a) Operanden: Konstanten oder Name eines Attributs
b) Vergleichsoperatoren: = , ≠ , < , ≤ , > , ≥
c) Boolesche Operatoren: ∧ , ∨ , ¬
–
RS σ
–
Iσ
F(R )
F( R)
= RS R
= { t | t ∈ I R ∧ F(t) }
Seite 20
Das Relationale Datenmodell
‰
Umbennen von Relationen und Attributen (“+ 1- Operation”)
ρ S ( R ) : Relation R wird in Relation S umbenannt
ρ B ← A ( R ) : Attribut A der Relation R wird umbenannt in B
–
–
Umbennung unterscheidet sich von den anderen Operatoren dadurch, daß keine neue
Instanz erzeugt wird, sondern nur das Schema der Relation verändert wird.
Operator ist notwendig, wenn eine Relation mehrfach in einer Anfrage vorkommt.
Seite 21
Das Relationale Datenmodell
Beispiele
R
R∪S
A
a
d
c
B
b
a
b
C
c
f
d
S
R×S
R –S
a
d
c
b
π A, C ( R )
b
a
b
g
c
f
d
a
A C
a c
d f
c d
D E F
b g a
d a f
a b c
c b d
σB = b ( R )
A
a
a
d
d
c
c
B
b
b
a
a
b
b
C
c
c
f
f
d
d
D
b
d
b
d
b
d
E
g
a
g
a
g
a
F
a
f
a
f
a
f
A B C
a b c
c b d
ρB ← D ( S ) B E F
b g a
d a f
Seite 22
Das Relationale Datenmodell
Beispiele für Anfragen
‰
Relationenschemata: Städte ( SName, SEinw, LName ) Länder ( LName, LEinw, Partei )
‰
Bestimme alle Großstädte und ihre Einwohnerzahlen
π SName, SEinw ( σ SEinw ≥ 500.000 ( Städte ) )
‰
In welchem Lande liegt die Stadt Passau?
π LName ( σ SName = Passau ( Städte ) )
‰
Bestimme die Namen aller Städte, deren Einwohnerzahl die eines Landes übersteigt.
π SName ( σ SEinw > LEinw ( Städte × Länder ) )
‰
Finde alle Städtenamen in CDU-regierten Ländern.
π SName ( σ Städte.LName = Länder.LName ( Städte × σ Partei = CDU ( Länder ) ) )
‰
Gib alle Städte, die es nur in Hessen gibt?
π SName ( σ LName = Hessen ( Städte ) ) – π SName ( σ LName ≠ Hessen ( Städte ) )
Seite 23
Das Relationale Datenmodell
1.2.2 Abgeleitete Operationen
Durchschnitt: R ∩ S
‰
‰
R ∩ S = R – (R – S)
Beispiel:
R
B
b
b
b
a
C
c
c
f
d
D
a
d
b
c
S
B
b
b
a
C
c
c
d
D
d
e
b
R∩S
B C D
b c d
Seite 24
Das Relationale Datenmodell
Quotient (Division): R ÷ S
‰
vereinfachende Annahme:
‰
r > s, S ≠ ∅ und Ar = Bs, Ar-1 = Bs-1,…, Ar-s+1 = B1
Relationenschema = {A1,…,Ar-s}
‰
Resultatsinstanz des Quotienten:
( R ÷ S ) := { ( a 1, …, a r – s ) ∀( b 1, …, b s ) ∈ I S : ( a 1, …, a r – s, b 1, …, b s ) ∈ I R }
‰
‰
Ableitung des Quotienten durch die Basisoperationen (siehe Übung)
Beispiel:
R
A
a
a
b
e
e
a
B
b
b
c
d
d
b
C
c
e
e
c
e
d
D
d
f
f
d
f
e
S
C D
c d
e f
R÷S
A B
a b
e d
Seite 25
Das Relationale Datenmodell
Theta-Join (Verbund):
‰
Auswahl bestimmter Tupel aus dem kartesischen Produkt R × S :
R iθj S := σ A θ B ( R × S )
i
mit
‰
‰
R iθj S
j
θ∈ { = ,≠,<,≤,>,≥ }
für θ = “=“ wird der Join auch als “Equijoin” bezeichnet
Beispiel:
R
A
1
4
7
B
2
5
8
C
3
6
9
S
D E
3 1
6 2
RB < D S
A B C D E
1 2 3 3 1
1 2 3 6 2
4 5 6 6 2
Seite 26
Das Relationale Datenmodell
Natürlicher Verbund (natural join): R
‰
‰
S
wichtigste Operation neben der Selektion
vereinfachende Annahme:
A 1 = B 1 , …, A k = B k und A j ≠ B i für alle j und i mit k < j ≤ r und k < i ≤ s
‰
σ

(
R
×
S
)
Dann ist: ( R S ) := π i

k + 1, i k + 2, …, i r + s  R.A 1 = S.B 1 ∧ … ∧ R.A k = S.B k
‰
Beispiel:
R
B
b
b
b
a
C
c
c
f
d
A
a
d
b
c
S
B
b
b
a
C
c
c
d
D
d
e
b
R
S
B
b
b
b
b
a
C
c
c
c
c
d
A
a
a
d
d
c
D
d
e
d
e
b
Seite 27
Das Relationale Datenmodell
Beispiele
Datenbankschema:
Abteilungsleiter:
P-M-Zuteilung:
pnr
mnr
Personal:
Fähigke
it
abtnr
pnr
pnr
PName
Vorname
abtnr
Lohn
B10
67
67
Meier
Helmut
B10
L4
67
84
3
A63
333
73
Müller
Margot
B10
L5
67
93
2
A64
51
114
Bayer
Martin
A63
L6
67
101
3
51
Daum
Birgit
A64
L7
73
84
5
69
Störmer
Willi
A64
L6
333
Haar
Hans
A63
L6
Abteilung:
114
93
5
114
101
3
abtnr
AName
701
Reiner
Willi
A64
L6
51
93
2
B10
Spielzeug
82
Just
Michael
A64
L6
69
101
2
A63
Computer
333
84
3
A64
Suppen
701
84
2
mnr
MName
701
101
2
84
Presse
82
101
2
93
Füllanlage
101
Säge
Maschinen:
Seite 28
Das Relationale Datenmodell
Anfragen
‰
Gib alle Namen von Personen, die an einer Maschine ausgebildet sind.
π PName ( Personal
‰
P-M-Zuteilung )
Gib alle Namen der Personen, die an keiner Maschine genügend gut ausgebildet sind.
π PName ( ( π pnr ( Personal ) – π pnr ( σ Fähigkeit < 5 ( P-M-Zuteilung ) ) )
‰
Gib die Namen der Personen aus Abteilung “Suppen”, die an der Maschine mit mnr = 93
ausgebildet sind.
π PName ( ( ( σ AName = Suppen ( Abteilung ) )
‰
Personal )
Personal )
σ mnr = 93 ( P-M-Zuteilung ) )
Gib die Namen der Personen, die an der gleichen Maschine ausgebildet sind wie die Person mit
pnr = 114.
π PName ( Personal
( ( π mnr ( σ pnr = 114 ( P-M-Zuteilung ) ) )
P-M-Zuteilung ) )
Seite 29
Das Relationale Datenmodell
Weitere Join-Operatoren
‰
bisherige Join-Operatoren werden auch als innere Joins bezeichnet
–
Datensätze ohne Join-Partner gehen verloren
äußere Join-Operatoren (engl.: outer joins):
‰
‰
‰
Schema des äußeren Joins entspricht dem des inneren Joins
Datensätze ohne Join-Partner werden (teilweise) berücksichtigt und mit Nullwerten aufgefüllt.
Varianten
–
linker äußerer Join R
S: Tupel von R bleiben erhalten
–
recher äußerer Join R
S: Tupel von S bleiben erhalten
–
vollständiger äußerer Join R
S: Tupel von S und R bleiben erhalten
Semi-Join
‰
Sei RS das Schema der Relation R. Dann ist der Semi-Join von R und S folgendermaßen
definiert:
R
–
S = Π RS ( R
S)
enthält alle Tupel der Relation R, die an dem Join mit der Relation S beteiligt sind.
Seite 30
Das Relationale Datenmodell
Beispiele
R
A B C
a1 b1 c1
a2 b2 c2
S
C D E
c1 d1 e1
c3 d2 e2
‰ linker äußerer Join
R
S
A B C D
E
e1
a1 b1 c1 d1
a2 b2 c2 NULL NULL
‰ Semi-Join
R
S
A B C
a1 b1 c1
Seite 31
Das Relationale Datenmodell
3.4 Das Relationenkalkül
bisher:
‰
‰
Benutzung einer prozeduralen Anfragesprache
explizite Beschreibung, wie das Ergebnis berechnet wird
zugrundeliegende Idee beim Relationenkalkül:
‰
‰
‰
Ergebnis einer Anfrage ist eine Menge von Tupeln
Beschreibung der Ergebnisrelation ohne dabei explizit eine Vorschrift für die
Konstruktion des Ergebnisses anzugeben.
Prädikatenlogik erster Stufe
Zwei verschiedene Techniken
‰
‰
Tupelkalkül
Domänenkalkül
Seite 32
Das Relationale Datenmodell
3.4.1 Das Tupelkalkül
‰
eine Anfrage im Tupelkalkül wird wie folgt formuliert:
{ t ( RS ) | ψ ( t ) }
–
–
Ψ ist hierbei eine Boolsche Funktion.
Die Ergebnismenge der Anfrage ist durch die Tupel t aus dem Schema RS
gegeben mit ψ ( t ) = true
–
Häufig ergibt sich das Schema aus der verbalen Formulierung der Anfrage. Das
Schema wird deshalb bei den Formeln nicht immer explizit angegeben.
Beispiele:
‰
Was sind die Namen der Mitarbeiter aus Abteilung A63?
{ t | ∃s ∈ Personal mit t[PName] = s[PName] und s[abtnr] = “A63“ }
‰
Quotient der Relationen R mit Schema A und S mit Schema B, A ⊆ B :
{ t | ∀t r ∈ R ∃t s ∈ S mit t s ( B – A ) = t und t s ( A ) = t r }
Seite 33
Das Relationale Datenmodell
Formeln des Tupelkalküls
‰
Eine Formel Ψ setzt sich zusammen aus Atomen der Form
– R(s): s ist Element der Relation R (s ist Tupelvariable)
–
s[i] θ u[j] mit
–
–
s[i] θ a
θ∈{ = ,≠,<,≤,>,≥ }
Beispiele:
‰
Personal(t), t[Fähigkeit] > 4, t[abtnr] = u[abtnr]
Eine Formel ist gegeben durch
‰
‰
‰
ein Atom
Ψ 1 ∧ Ψ 2 , Ψ 1 ∨ Ψ 2 , ¬Ψ 1 , ( Ψ 1 )
∀s ( RS )Ψ , ∃s ( RS )Ψ , wobei s eine Tupelvariable in Ψ und RS eine Menge von Attributen
ist, auf der das Tupel definiert ist.
– RS wird auch als Schema der Variable s bezeichnet
Seite 34
Das Relationale Datenmodell
Bemerkung:
‰
Reihenfolge der Ausführung: ∀ und ∃; ¬; ∧ ; ∨
‰
Klammern setzten die Reihenfolge außer Kraft
Beispiele:
Annahme: Tupelvariable x ist aus dem Schema {pnr,mnr,Fähigkeit}
‰
‰
¬x [ Fähigkeit ] > 4
( x [ pnr ] = y [ pnr ] ) ∨ ¬x [ Fähigkeit ] > 4
‰
P-M-Zuteilung ( x ) ∧ ( x [ pnr ] = y [ pnr ] ) ∨ ¬x [ Fähigkeit ] > 4
‰
∃x ( P-M-Zuteilung ( x ) ∧ ( x [ pnr ] = y [ pnr ] ) ∨ ¬x [ Fähigkeit ] > 4 )
Seite 35
Das Relationale Datenmodell
Freie und gebundene Tupelvariablen
‰
‰
‰
entspricht dem Prinzip globaler und lokaler Variablen in einem Programm
falls ein Quantor vor einer Variablen steht, wird diese zu einer gebundenen Variablen
folgende Bedingungen gelten:
–
das Auftreten einer Tupelvariablen in einem Atom ist stets frei
–
für f = ¬g und f = ( g ) sind alle freien Variablen von g auch frei in f
–
für f = g ∧ h und f = g ∨ h sind die Variablen in f frei, falls sie es in g und h sind
–
für f = ∃x ( RS ) ( g ) und f = ∀x (RS) ( g ) muß x eine freie Variable in g sein, die in
f gebunden ist. Dabei ist RS das Schema der Variable x.
Beispiel:
∀x ( { pnr, mnr, Fhigkeit } ) ( ¬P-M-Zuteilung ( x ) ∨ x [ Fähigkeit ] > 4 )
Seite 36
Das Relationale Datenmodell
Berechnung der Formeln des Tupelkalkül
‰
ein Ausdruck des Tupelkalküls hat die Form
{ t ( RS ) | Ψ ( t ) }
wobei t (aus dem Schema RS) die einzig freie Variable in Ψ ist.
Substitution:
‰
Sei Ψ ( s ) eine Formel. Dann ist Ψ ( t/s ) die Substitution von der Tupelvariablen s in Ψ
durch das Tupel t, falls in jedem Atom, das ein freies Auftreten von s enthält, wie folgt
verfahren wird:
–
R(s) wird ersetzt durch “wahr”, falls t ∈ R . Andernfalls durch “falsch”.
–
s[i] θ u[j] wird ersetzt durch
c θ u[j]
mit c = t[i] (Ann.: u ≠ s )
–
s[i] θ c wird ersetzt durch “wahr”, falls t [ i ] θ c gilt.
Bemerkung:
‰
Durch Substitution gewinnt man eine Formel die nur noch Konstanten der Form
“wahr” und “falsch” und Atome mit gebundenen Variablen enthält.
Seite 37
Das Relationale Datenmodell
Beispiele
‰
‰
u und t aus dem Schema {pnr,mnr, Fähigkeit}
gegeben sei die Formel.
∀u ( ¬P-M-Zuteilung(u) ∨ ¬u ( pnr ) = t ( pnr ) ∨ u ( Fähigkeit ) < t ( Fähigkeit ) )
für das Tupel t = (73,84,5) gilt:
∀u ( ¬P-M-Zuteilung(u) ∨ ¬u ( pnr ) = 73 ∨ u ( Fähigkeit ) < 5 )
‰
gegeben sei die Formel:
¬P-M-Zuteilung(u) ∨ ¬u ( pnr ) = 73 ∨ u ( Fähigkeit ) < 5
Für das Tupel u = (51,93,2) gilt:
¬true ∨ ¬false ∨ true
Seite 38
Das Relationale Datenmodell
Interpretation der Formel
‰
‰
Sei f eine Formel ohne freie Variablen. Die Interpretation I(f) ist wie folgt definiert:
–
Falls f = “wahr”, dann ist I(f) := true. Andernfalls, I(f) := false.
–
…
–
…
–
Sei f = ∃x ( RS ) ( g ( x ) ) . Dann ist I(f) := true, falls es mindestens ein Tupel t aus dem
Schema RS gibt, so daß I(g(t/x)) = wahr ist. Andernfalls, I(f) := false
–
Sei f = ∀x ( RS ) ( g ( x ) ) . Dann ist I(f) := true, falls für alle t aus dem Schema RS I(g(t/x))
= true gilt. Andernfalls, I(f) := false.
Sei E = { x ( RS ) | Ψ ( x ) } ein Ausdruck des Tupelkalküls und sei RS = {A1,…,An} das
Schema von x und D 1 × D 2 × … × D n der Wertebereich. Der Wert von E zu einer
gegebenen Datenbank besteht aus allen Tupel
t ∈ D 1 × D 2 × … × D n , die
I ( Ψ ( t/x ) ) = true
erfüllen.
Seite 39
Das Relationale Datenmodell
Beispiele
‰
Gib alle Personalnummern von Personen, die an einer Maschine ausgebildet sind.
{ t ( { pnr } ) | ∃u ( P-M-Zuteilung ( u ) ∧ u [ pnr ] = t [ pnr ] ) }
‰
Gib alle Personalnummern der Personen, die an keiner Maschine genügend gut
ausgebildet sind.
{ t ( { pnr } ) | ∀u ( ¬P-M-Zuteilung ( u ) ∨ u [ Fähigkeit ] ≥ 5 ∨ ¬u [ pnr ] = t [ pnr ] ) }
Menge der möglichen Werte
für u (t[pnr] = 1):
P-M-Zuteilung
u[pnr] = 1
alle möglichen Werte
für u
Seite 40
Das Relationale Datenmodell
Einführung von Kurzschreibweisen:
‰
∃u ∈ R ( ψ ( u ) ) := ∃u ( R ( u ) ∧ ψ ( u ) )
‰
∀u ∈ R ( ψ ( u ) ) := ∀u ( ¬R ( u ) ∨ ψ ( u ) )
‰
( Ψ 1 ⇒ Ψ 2 ):= ( ¬Ψ 1 ∨ Ψ 2 )
Beispiel
‰
Berechne die Namen der Personen, die an keiner Maschine genügend gut
ausgebildet sind (Schema der Tupelvariablen t ist {PName}).
{ t | ∃x ∈ Personal ( Ψ 1 ( x, t ) ) ∧ ∃y ( { pnr } ) ( y [ pnr ] = x [ pnr ] ∧ Ψ 2 ( y ) ) ) }
–
Ψ 1 ( x, t ) = ( x [ PName ] = t [ PName ] )
–
Ψ 2 ( y ) = ∀u ∈ P-M-Zuteilung ( u [ Fähigkeit ] ≥ 5 ∨ ¬u [ pnr ] = y [ pnr ] )
Vereinfachen der Formel Ψ 2 :
Ψ 2 ( y ) = ∀u ∈ P-M-Zuteilung ( ( u [ pnr ] = y [ pnr ] ) ⇒ ( u [ Fähigkeit ] ≥ 5 ) )
Seite 41
Das Relationale Datenmodell
Sichere Ausdrücke
‰
Probleme des Tupelkalküls:
– Beschreibung unendlich großer Relationen
–
keine effektive Berechnung möglich (d.h. nur durch Testen jedes Elements aus dem
Wertebereich)
‰
Idee:
–
Beschränkung der Wertebereiche nur auf die Werte, die tatsächlich in vorhandenen
Relationen vorkommen.
–
–
DOM ( ψ ) :
Menge aller Werte, die explizit in Ψ vorkommen, oder in Relationen, die in Ψ erwähnt
werden (zur Erinnerung: Relationen sind hier stets endlich).
Beispiel:
Dann gilt:
ψ = “t [ 2 ] = 7 ∨ R ( t )“ mit
R
a
b
5
3
.
DOM ( ψ ) = { a, b } × { 3, 5, 7 }
Seite 42
Das Relationale Datenmodell
Ein Ausdruck
–
{ t(RS) ψ ( t ) }
ist sicher, falls gilt:
I ( ψ ( u ⁄ t ) ) = true ⇒ u ∈ DOM ( ψ )
–
für jede Teilformel ∃u ( ω ( u ) ) gilt:
I ( ω ( v ⁄ u ) ) = true ⇒ v ∈ DOM ( ω )
–
für jede Teilformel ∀u ( ω ( u ) ) gilt:
v ∉ DOM ( ω ) ⇒ I ( ω ( v ⁄ u ) ) = true
Beispiele:
‰
{ t | Personal ( t ) ∧ t [ Vorname ] = “Willi“ }
ist ein sicherer Ausdruck
‰
{ t | ( Personal ( t ) ∨ t [ Vorname ] = “Willi“ ) }
ist nicht sicher.
‰
für eine Relation R und eine Formel Ψ sind die folgenden Ausdrücke sicher:
–
∃u ∈ R ( ψ ( u ) )
–
∀u ∈ R ( ψ ( u ) )
Seite 43
3.5 Erweiterung der relationalen Algebra
Probleme mit der relationalen Algebra und dem Tupelkalkül
‰
Das bisherige Konzept der relationalen Algebra unterstützt nicht die Formulierung von
wichtigen Anfragen.
– Datentyp Relation (Menge von Tupeln) nicht ausreichend.
(i) Sortieren der Daten (insbesondere beim Ergebnis einer Anfrage)
(ii) Abspeicherung von Duplikaten (die z. B. durch Projektion entstehen)
– Funktionalität der relationalen Algebra nicht ausreichend.
(iii) Verdichtung der Daten einer Relation durch Aggregation (Summe, Durchschnitt)
Anmerkung
‰
Wir werden später bei der tatsächlichen Anfragesprache relationaler Systeme (SQL) sehen,
dass diese Anforderungen beim Entwurf von SQL berücksichtigt wurden.
Seite 44
M-Relation
‰
Eine Multi-Relation (M-Relation) R besteht aus einem Relationenschema RSR und einer
Instanz IR, wobei als Instanz eine Multimenge zulässig ist.
‰
Zwei M-Relationen heißen schemaverträglich, falls die Attribute der beiden Mengen gleich
sind, und der Wertebereich der Attribute in beiden Relationen gleich ist.
Sei M eine Multi-Relation. Dann bezeichnet V(M,x) die Anzahl der Vorkommen eines Tupels
x in der Instanz von M.
–
Instanzen von Multi-Relationen werden als Multimengen von Tupeln repräsentiert, wobei
Tupel mehrfach vorkommen können.
–
Um den Unterschied zu Mengen klar zu machen, verwenden wir statt Mengenklammern
“<“ und “>”.
–
Gewöhnliche Relationen können als Spezialfall von M-Relationen betrachtet werden, für
deren Tupel t stets V(M,t) = 1 gilt.
Um die Semantik von Operationen zu definieren, verwenden wir die Funktionen V. Seien M
und N schemaverträgliche Multimengen. Dann gilt
‰
‰
–
M = N genau dann, falls ∀x : V ( M, x ) = V ( N, x ) .
–
M ⊆ N genau dann, falls ∀x : V ( M, x ) ≤ V ( N, x )
Seite 45
Erweiterung der relationalen Operatoren
‰
Selektion: MRel -> MRel
–
RS σ
–

Es gilt: V ( σ F ( R ), x ) =  V ( R, x )
0

–
Die Selektion auf M-Relationen entspricht damit der Selektion auf Relationen.
–
Beispiel: Sei RSR = {A,B} und I R = ⟨ ( 1, 1 ), ( 1, 2 ), ( 1, 3 ), ( 1, 2 )⟩ . Dann ist
Iσ
‰
F(R)
B = 2(R)
= RS R
falls F ( x ) .
sonst
= ⟨ ( 1, 2 ), ( 1, 2 )⟩
Projektion: MRel -> MRel
–
Das Relationenschema X der Projektion auf einer Relation R wird explizit beim
Operationsaufruf definiert, wobei X ⊆ RS R .
–
Es gilt: V ( π X ( R ), x ) =
∑
V ( R, x' ) . Bei der Projektion wird also jedes Tupel
x' ∈ I R ∧ x' [ X ] = x
der Eingabe in ein Tupel der Ausgabe überführt. Gleiche Tupel werden verschmolzen und
die entsprechenden Zähler aufaddiert.
–
Beispiel: Für I R = ⟨ ( 1, 1 ), ( 1, 2 ), ( 2, 3 )⟩ ist I π
1(R)
= ⟨ ( 1 ), ( 1 ), ( 2 )⟩ .
Seite 46
‰
‰
Umbenennung: MRel -> MRel
Entspricht der Operation, wie sie für Relationen definiert ist.
Kartesisches Produkt: MRel x MRel -> MRel
–
RS R × S = RS R ∪ RS S (Ann.: RS R ∩ RS S = ∅ ).
–
V ( R × S, x ) = V ( R, x [ RS R ] ) ⋅ V ( S, x [ RS S ] ) für x ∈ Dom ( R × S ) .
Es wird analog zu Mengen das kartesische Produkt bei Multimengen gebildet.
–
Beispiel: I R = ⟨ ( 1 ), ( 2 ), ( 1 )⟩ und I S = ⟨ ( 1 ), ( 2 )⟩ . Dann ist
I R × S = ⟨ ( 1, 1 ), ( 1, 2 ), ( 2, 1 ), ( 2, 2 ), ( 1, 2 ), ( 1, 2 )⟩ .
‰
Vereinigung: MRel x MRel -> MRel
–
Seien R und S schemaverträglich.
–
Die Instanz ergibt sich aus folgender Bedingung: V ( R ∪+S , x ) = V ( R, x ) + V ( S, x ) .
Bei der Vereinigung werden also Duplikate nicht beseitigt.
–
Beispiel: Seien I R = ⟨ ( 1 ), ( 2 ), ( 1 )⟩ und I S = ⟨ ( 2 ), ( 3 )⟩ . Dann ist
I R ∪ S = ⟨ ( 1 ), ( 2 ) , ( 1 ) , ( 2 ) , ( 3 )⟩ .
+
–
Diese Vereinigung wird auch als Summenvereinigung bezeichnet. Zusätzlich wird auch
noch die Maximumvereinigung benötigt.
Seite 47
‰
Differenz: MRel x MRel -> MRel
–
Annahme: R und S sind schemaverträglich. Somit ist RS R – S = RS S ( = RS R ) .
–
Die Instanz ergibt sich aus folgender Bedingung:
–
–

V ( R – S, x ) =  V ( R, x ) – V ( S, x ) falls V ( R, x ) > V ( S, x ) .
0
sonst

Die Differenz entfernt somit nicht alle Instanzen von einem Element aus der ersten
Multimenge, das in der zweiten Multimenge vorkommt.
Beispiel: I R = ⟨ ( 2 ), ( 1 ), ( 2 ), ( 1 )⟩ und I S = ⟨ ( 2 ), ( 3 )⟩ . Dann ist
I R – S = ⟨ ( 2 ), ( 1 ), ( 1 )⟩ .
–
Als Alternative könnte man auch die Differenz strikt definieren, indem jedes Tupel aus
der Relation R entfernt wird, das in der Relation S liegt.
Seite 48
Aggregation
Motivation
‰
‰
Um schnell einen Überblick der Daten einer Multi-Relation zu bekommen, soll in einer
Anfragesprache auch die Berechnung wichtiger Kennzahlen unterstützt werden. Insbesondere
im betriebswirtschaftlichen Umfeld sind dabei die Aggregationsoperationen Summe (sum),
Durchschnitt (avg), Anzahl (count), Minimum (min) und Maximum (max) von Bedeutung.
In der relationalen Algebra sind solche Operationen bisher noch nicht berücksichtigt worden.
Erweiterung der relationalen Algebra
‰
Eine Aggregationsfunktion agg berechnet zu einer Multi-Relation einen Wert aus einem
Wertebereich D. Ganz allgemein ist agg: MRel → D .
‰
Die relevanten Aggregationsfunktionen sind avg, sum, count: MRel → D und min und max,
die eine Multi-Relation auf den Wert eines Attributs abbilden.
–
Bei der Anwendung von sum und avg muß noch zusätzlich ein Attribut aus dem Schema
der Relation angegeben werden, auf welches das Aggregat abgebildet wird.
–
Die Operation count liefert die Anzahl der Tupel in der Instanz der Relation.
Aggregate finden zunächst nur Verwendung in Kombination mit einem Gruppierungsoperator,
den wir folgendermaßen einführen.
‰
Seite 49
Gruppierung
‰
‰
Um gleich mehrere Kennzahlen für eine Multi-Relation zu berechnen, kann diese in Klassen
(Partitionen) aufgeteilt und für jede dieser Partitionen eine Kennzahl berechnet werden.
Eine Partition ist wiederum eine Relation, die das Schema von der Quelle erbt.
–
Bei einer Relation R wird eine Partitionierung durch { A 1 ,… ,A n } ⊆ RS R definiert. Eine
‰
Partition enthält alle Tupel aus IR, die bzgl. der Attribute { A 1 ,… ,A n } den gleichen Wert
besitzen.
Zu einer Aggregatoperation (und einem ausgezeichneten Attribut) wird nun für jede Partition
eine Kennzahl berechnet. Diese Kennzahl wird zusammen mit den Werten der
Partitionierungsattribute in der Ergebnisrelation eingetragen.
‰
Sei R eine Multi-Relation und A = { A 1 ,… ,A n } ⊆ RS R . Desweiteren seien B1,…,Bm Attribute
und agg1,…,aggm Aggregatsfunktionen mit agg i : MRel → dom(B i) .Dann ist der
Gruppierungsoperator S = γ A, B
1
= agg 1, …, B m = agg m(R)
folgendermaßen definiert:
–
RSS = A ∪ { B 1, …, B m } .(Ann.: B i ∉ A , 1 ≤ i ≤ m )
–

V ( S, x ) =  1 falls ( V ( R, x [ A ] ) > 0 ) und x[Bi] = aggi( σ t [ A ] = x [ A ] ( R ) ).
sonst
 0
Seite 50
Beispiel:
‰
Betrachten wir die Relationeninstanz I R = ⟨ ( 1, 1 ), ( 1, 2 ), ( 1, 3 ), ( 2, 4 )⟩ . Dann ist
γ A, C = count(R) = ⟨ ( 1, 3 ), ( 2, 1 )⟩ . Es treten also keine Tupel mehrfach auf.
Spezialfall (Duplikateliminierung)
‰
Ein wichtiger Spezialfall des Gruppierungsoperators ist die Duplikateliminierung δ . Dabei
setzen wir A = RSR und berechnen keine Aggregationsfunktion (m=0).
‰
Durch eine Duplikateliminierung werden alle Zähler von Tupeln der Relation R auf den Wert 1
gesetzt. Eine solche Multi-Relation entspricht einer gewöhnlichen Relation.
Beispiele:
‰
–
Sei R = ⟨ ( 1 ), ( 2 ), ( 1 )⟩ eine M-Relation. Dann ist δ ( R ) = ⟨ ( 1 ), ( 2 )⟩ .
–
Relationenschemata: Städte ( SName, SEinw, LName ) Länder ( LName, LEinw, Partei ) .
Berechne die Namen der Städte in allen Bundesländern.
δ ( π SName ( σ SEinw ≥ 8000 ( Städte ) ) )
Seite 51
Abgeleitete Operationen
‰
‰
Man hat bereits bemerkt, dass die Unterstützung von Multi-Relationen zu einem wesentlich
komplexeren Modell führt.
– Insbesondere gelten die Mengengesetze für die Instanzen der Multi-Relationen nicht.
Deshalb werden noch weitere Operationen für die Vereinigung und Differenz benötigt.
–
Schnitt zweier schemaverträglicher Relationen R und S
R ∩ S = R – (R – S)
–
Maximumvereinigung von R und S
R ∪maxS ist die kleinste Multimenge T mit T ⊇ R und T ⊇ S
–
‰
Strikte Differenz
R- strict S enthält alle Elemente aus R mit der entsprechenden Vielfachheit, falls dies nicht
in S vorkommt.
Anmerkung
Leider ist dieser Grad an Präzision notwendig, da nur dadurch die Semantik von Anfragesprachen wie SQL klar definiert werden kann.
Seite 52
Verallgemeinerung der Projektion
‰
‰
Die Projektion und auch die Umbenennung sind bei M-Relationen Operatoren, die zu jedem
Tupel der Eingabe ein Tupel der Ausgabe erzeugen. Solche Abbildungen werden auch als map
bezeichnet.
Seien R eine M-Relationen, RST ein Relationenschema und f: (RSR -> Dom(R)) -> (RST ->
Dom(T)) eine Abbildung. Dann wird durch
µf ( R )
die relationale Map definiert, wobei
‰
–
RS µ ( R ) = RS T
–
V ( I µ ( R ), x ) =
f
f
∑
V ( R, y )
y ∈ IR ∧ x = f(y)
Man beachte dabei, dass f eine Funktion ist, die ein Tupel der Relation R auf ein Tupel der
Relation T abbildet. Dabei haben wir die Definition des Tupels als eine Abbildung benutzt.
Beispiel
Sei R eine Relation mit RSR = {A,B} und I R = ⟨ ( 1, 2 ), ( 2, 1 ), ( 1, 2 ), ( 2, 1 ), ( 4, 6 )⟩ . Sei f(t)
= (t[A]*t[B], t[A] + t[B]) eine Abbildung. Dann ist
·
I µ ( R ) = ⟨ ( 2, 3 ), ( 2, 3 ), ( 2, 3 ), ( 2, 3 ), ( 24, 10 )⟩ .
f
Seite 53
S-Relation
‰
Eine S-Relation besteht aus (R, <R), wobei
–
–
‰
Durch
–
‰
R eine M-Relation
<R eine Ordnungsrelation auf dom(R)
ω < : MRel -> SRel
wird eine M-Relation R auf eine S-Relation S = (R, <) abgebildet, wobei < eine
Ordnungsrelation auf R definiert.
Beispiel:
R
A
4
9
7
B
1
2
8
C
3
3
4
Seite 54
Seien
–
<1 = {((a,b,c), (d,e,f)) | a < d}
–
<2 = {((a,b,c), (d,e,f)) | c < f oder (c = f) und b > e}
Ordnungsrelationen. Dann sind
ω< ( R ) A B C
1
4 1 3
7 8 4
9 2 3
ω< ( R )
A B C
2
9 2 3
4 1 3
7 8 4
Achtung:
‰
‰
Durch die Operation ω wird eine S-Relation erzeugt. Da aber die Operationen der rel. Algebra
nicht für S-Relationen definiert sind, ist es in einem Ausdruck nur möglich, ω als letzte
Operation anzuwenden.
Berechne die Liste der Namen aller Städte, deren Einwohnerzahl die eines Landes übersteigt,
und sortiere die Liste aufsteigend bzgl. SEinw.
ω < ( π SName ,LName ,SEinw ( σ SEinw > LEinw ( Städte × Länder ) ) )
Man beachte, dass dieser Ausdruck zuviel an Information liefert, da zusätzlich zum Namen der
Stadt auch noch der Name des Lands und die Einwohnerzahl zurückgegeben wird.
Seite 55
Zusammenfassung
‰
‰
‰
‰
‰
Begriff der Relation
– Relationenschema und Relationeninstanz
– Zwei Definitionen basierend auf Attributlisten / Attributmengen
– einfache Integritätsbedingungen
Abbildung von ER-Modell in ein relationales Modell
Relationale Algebra
– Mengenalgebra
Tupelkalkül
– Prädikatenlogik erster Stufe
Erweiterte relationale Algebra
–
Algebra für Multimengen
–
Geordnete Mengen
–
weitere Operationen: Gruppierung
Seite 56
SQL: Die Sprache relationaler DBMS
2. SQL: Die Sprache relationaler DBMS
‰ Basierend auf dem Tupelkalkül und der relationalen Algebra wurden mit dem
Aufkommen relationaler DBMS auch spezielle Sprachen entwickelt.
–
SQL ist die derzeit marktbeherrschende Anfragesprache
–
Von praktischer Bedeutung waren auch noch bis Ende der 80er Jahre:
a) QBE (Query by Example, basierend auf dem Domainkalkül)
b) Quel (Anfragesprache von Ingres basierend auf dem Tupelkalkül).
Bei Anfragesprachen wird unterschieden zwischen der
‰ Datendefinitionssprache (DDL)
– Anlegen und Ändern der Datenstrukturen für die drei Ebenen einer Datenbank
(externe Ebenen, konzeptionelle Ebene, physische Ebene)
–
Festlegen von Integritätsbedingungen
– Festlegen der Zugriffsrechte
‰ Datenmanipulationssprache (DML)
–
Einfügen, Ändern und Löschen von Datenobjekten
– Formulierung von Anfragen
Seite 57 von 109
SQL: Die Sprache relationaler DBMS
Historie
‰ SQL (structured query language) wurde bei IBM als Sprache des relationalen DBMS
System R entwickelt (1974, D.D. Chamberlin et al.)
‰ Heute ist SQL quasi der Standard für Sprachen relationaler DBMS
–
SQL1, 1985
–
SQL2, 1992 (wird auch als SQL92 bezeichnet)
–
SQL3 (bzw. SQL:1999)
‰ SQL wird als interaktive Sprache eingesetzt, kann aber auch durch eine geeignete
Kopplung in einer Programmiersprachen wie z. B. C und Java genutzt werden.
‰ SQL kann als eine Mischform aus einer erweiterten relationalen Algebra und dem
Tupelkalkül verstanden werden.
Anmerkung
‰ Das offizielle Dokument, in dem der Standard von SQL beschrieben wird, ist sehr groß,
so dass im Rahmen dieser Vorlesung deshalb nur die wichtigsten Konzepte von SQL
vorgestellt werden können.
‰ Viele Hersteller wie Oracle haben in ihren Systemen Erweiterungen von SQL
implementiert. Andererseits wurde in Oracle SQL2 nicht vollständig, sondern nur der
sogenannte Entry-Level implementiert.
Seite 58 von 109
SQL: Die Sprache relationaler DBMS
Lexikalische Elemente
‰ Wie jede andere Programmiersprache besitzt SQL lexikalische Elemente
–
Leerzeichen, Zeilenumbruch und Tabulatoren trennen lexikalische Elemente
Bezeichner
‰ Bezeichner werden benutzt, um Namen an Datenbanken, Relationen und Attribute zu
vergeben.
‰ Aufbau eines Bezeichners
–
Erstes Zeichen ist ein Buchstabe.
–
Weiterhin können darin Zahlen und _ enthalten sein.
–
Bezeichner müssen sich von einem Schlüsselwort unterscheiden
Konstanten (Literale)
‰ sehr ähnlich zu den Konstanten in üblichen Programmiersprachen
‰ Gewöhnungsbedürftig sind Konstanten von Zeichenketten:
–
´Gerd´ ist eine Konstante
–
Einige DBMS unterstützen auch andere Formate.
Seite 59 von 109
SQL: Die Sprache relationaler DBMS
2.1 DDL
Vorbemerkungen
‰ Im Folgenden werden nun einige wichtige Aspekte bei der Datendefinition erläutert ohne
jedoch auf viele der angebotenen Optionen einzugehen.
‰ Wir werden beispielhaft die Definition von Datenstrukturen der konzeptionellen Ebene
(Relation) erläutern
–
Definition von Datenstrukturen der internen Ebene (Index) und der externen Ebene
(View) erfolgt später.
Aufbau des Kapitels:
‰ Datentypen
‰ Definition von Relationen
‰ Einfache Integritätsbedingungen
Seite 60 von 109
SQL: Die Sprache relationaler DBMS
2.1.1 Datentypen
‰ Datenbanksysteme bieten eine sehr reichhaltige Palette von Datentypen an, die sich bis
auf einen Kern erheblich unterscheiden.
–
Zum Teil findet man gleiche Datentypen mit unterschiedlichen Namen.
–
Auch wenn die Namen gleich sind, werden nicht die gleichen Operationen
angeboten oder unterschiedliche Namen für die gleiche Methode.
‰ Heutige Systeme unterstützen auch die Definition benutzerdefinierter Datentypen
–
Interbase bietet hierfür einen speziellen Befehl an.
‰ Klassifizierung
–
Zeichenketten
–
Zahlen
–
Individuelle Erweiterungen, die es in nahezu allen DBMS gibt:
BLOB, CLOB, Date
–
DBMS spezifische Datentypen
‰ Die unterschiedlichen Datentypen tragen insbesondere dazu bei, dass eine Kopplung von
SQL-Datenbanken nicht so einfach ist.
Seite 61 von 109
SQL: Die Sprache relationaler DBMS
Standardtypen
Zeichenketten
‰ char(size)
Zeichenkette mit konstanter Länge size
–
Maximallänge ist abhängig vom System.
–
char spezifiziert eine Zeichenkette mit einem Zeichen.
‰ varchar(size)
variabel lange Zeichenkette mit maximaler Länge size
– Bedarfsorientierter Speicherplatzverbrauch
Operationen auf Zeichenketten
‰ relationale Operatoren: =, <>, <, <=, =>, > sind überall verfügbar, aber die Semantik ist
oft unterschiedlich.
‰ Weitere Operatoren im SQL92 Standard (der aber in dieser Form nicht von allen DBMS
unterstützt wird):
–
|| ist z. B. die Verknüpfung von zwei Zeichenketten
Seite 62 von 109
SQL: Die Sprache relationaler DBMS
Zahlen
‰ numeric(g,d) bzw. decimal(g,d)
–
Gleitkommazahlen mit: g = #Gesamtstellen, d = #Nachkommastellen
‰ Darüber hinaus gibt es die üblichen Datentypen:
integer, smallint, real, double precision
‰ Operationen
– *, /, +, –
und noch viele andere. Z. B.: abs
spezielle Datentypen
‰ Die oben genannten Datentypen sind in ihrer Größe erheblich eingeschränkt. Anfang der
90er Jahre wurde dieses Defizit durch Einführung neuer Datentypen gemildert.
long:
–
variabel lange Zeichenkette mit maximal 2 GB.
–
Erhebliche Einschränkungen bei der Anfragebearbeitung
clob / blob: variabel lange Zeichenfolge / Bytefolge mit maximal 4 GB
‰ date/time
Datentypen für Datum und Uhrzeit
–
date unterstützt ein Datum bis zum Jahr 9999
Seite 63 von 109
SQL: Die Sprache relationaler DBMS
Definition neuer Datentypen
‰ In SQL lassen sich neue Wertebereiche anlegen, indem bestehende Wertebereiche
eingeschränkt werden.
– Syntax:
create domain <Name> [as] <Datentyp> [<Defaultwert>] [<Integritätsbedingung>]
–
Beispiel:
create domain Adresse varchar(50) default 'Marburg'
‰ Weiterhin können Datentypen zur Laufzeit verändert
–
alter domain …
und gelöscht werden.
–
drop domain Adresse
Bemerkung
‰ Es handelt sich hierbei um eine sehr eingeschränkte Form der Definition von Datentypen.
Es ist dadurch z. B. nicht möglich, strukturierte Datentypen zu definieren.
Seite 64 von 109
SQL: Die Sprache relationaler DBMS
2.1.2 Anlegen eines Relationenschemas
Eine Beispielgrammatik zur Definition eines Subsets
‰ create table <Relationen-Name>
(<Relationenkomponente>[,<Relationenkomponente>]*)
‰ <Relationenkomponente> ::= <Spaltendefinition> | <Integritätsbedingung>
‰ <Spaltendefinition>::= <Attributname> <Typ> [<Defaultwert>] [not null | unique]
‰ <Defaultwert>::= default <Literal> | null
‰ Die genaue Behandlung von Integritätsbedingungen erfolgt später. Im weiteren werden
wir einige Möglichkeiten exemplarisch erläutern.
Beispiele basieren auf dem folgenden Datenbankschema:
Kunde (KName, KAdr, Kto)
Auftrag (KName, Ware, Menge)
Lieferant (LName, LAdr, Ware, Preis)
Seite 65 von 109
SQL: Die Sprache relationaler DBMS
Beispiel:
create table Kunde(
KName char (20) not null,
KAdresse varchar (50) not null unique,
Kto
decimal (7) not null, /* decimal(7) ist eine Kurzform von decimal(7,0) */
primary key (KName)
)
Anmerkungen zu den Integritätsbedingungen
‰ unique drückt aus, dass dieses Attribut ein Schlüsselkandidat ist. Wird ein
Schlüsselkandidat durch mehrere Attribute A1, …, An gebildet, so wird dies durch die
Integritätsbedingung unique (A1,…,An) angegeben.
‰ not null sagt aus, dass das Attribut explizit belegt werden muss. Es dürfen keine NullWerte auftreten.
‰ Durch primary key (A1,…,An) wird festgelegt, dass die Attributmenge {A1,…,An} der
Primärschlüssel der Relation ist.
‰ Durch Angabe eines Defaultwertes wird beim Einfügen eines Tupels dieser Wert zur
Initialisierung benutzt, wenn explizit keine Wertzuweisung vorgenommen wurde.
Seite 66 von 109
SQL: Die Sprache relationaler DBMS
Beispiel:
create table Auftrag(
KName char(20) not null,
Ware
varchar(50) not null,
Menge decimal(7) default 100,
primary key (KName, Ware),
foreign key(KName) references Kunde(KName)
)
Anmerkungen zu den Integritätsbedingungen
‰ Ein Fremdschlüssel kann über die Intergritätsbedingung foreign key angegeben werden.
Damit wird sichergestellt, dass das Tupel mit dem Schlüssel tatsächlich in der Relation
(in unserem Beispiel ist das die Relation Kunde) existiert.
–
Beim Einfügen eines neuen Tupels muss deshalb eine entsprechende Überprüfung
stattfinden (was zu einem hohen Berechnungsaufwand führen kann).
–
Entsprechend muss beim Löschen eines Tupels aus einer Relation geprüft werden,
ob eine Referenz auf dieses Tupel existiert.
Seite 67 von 109
SQL: Die Sprache relationaler DBMS
Ändern/Löschen eines Relationenschema
Ändern eines Relationenschemas
alter table <Relationen-Name> add <Relationenkomponente>
Besonderheiten
‰ Prinzipiell ist die Syntax und Semantik solcher Befehle stark von dem jeweiligen
Systemhersteller abhängig.
Löschen eines Relationenschemas
drop table <Relationen-Name>
Seite 68 von 109
SQL: Die Sprache relationaler DBMS
2.2 DML
‰ Anfragen an die Datenbank werden in der DML formuliert
‰ Grundschema:
z.B.
select KName
select < Liste von Attributsnamen >
from Kunde
from < ein oder mehrere Relationennamen >
where Kto < 1000
[ where< Bedingung > ]
Bemerkungen:
‰ Die select-Klausel entspricht der Projektion in der relationalen Algebra (und nicht der
Selektion).
‰ Die Bedingung nach der where-Klausel enthält
1. Vergleichsoperatoren (<, >, = ... )
2. boolesche Operatoren (and, or , not)
3. Mengenoperatoren (in, not in) und Quantoren (exists, any, some, all)
Reihenfolge der Ausführung wird durch Klammern bestimmt.
‰ Attribute mit gleichen Namen, die zu verschiedenen Relationen gehören, werden mittels
des Relationennamen unterschieden.
Seite 69 von 109
SQL: Die Sprache relationaler DBMS
Algebra-Operationen in SQL
‰ Relation R
select *
from R
Bei Angabe von “*” in der select-Klausel werden alle Attribute der Relation aus der
from-Klausel ausgegeben.
‰ Projektion π A, C ( R )
select distinct A, C
from R
Ohne das Schlüsselwort distinct würde als Ergebnis eine M-Relation erzeugt.
‰ Selektion σ B = b ( R )
select *
from R
where B = b
‰ kartesisches Produkt R × S
select *
from R, S
Seite 70 von 109
SQL: Die Sprache relationaler DBMS
‰ Theta-Join auf Relationen R(A,B) und S(C,D) R BθD S
select *
from R, S
where B θ D
‰ Vereinigung der Relationen R(A,B) und T(A,B)
select * from R
union
select * from T
‰ Differenz der Relationen R und T
select * from R /* Dies wird nicht in Interbase unterstützt */
except
select * from T
‰ Allgemeine Bedeutung der “select … from … where”-Klausel in der relationalen
Algebra:
select distinctA,B,C,..
π A,B,C,… ( σ F ( R × S × T × … ) )
R,S,T,...
from
F
where
Damit ist insbesondere die Reihenfolge bei der Verarbeitung der Klausel bestimmt.
Seite 71 von 109
SQL: Die Sprache relationaler DBMS
Bemerkungen zu der Duplikatbeseitigung
‰ Die gewöhnliche select-Klausel beseitigt keine Duplikate in der Ergebnisrelation. Dies ist
aber durch Hinzufügen des Schlüsselworts distinct möglich:
select distinct A, B, C, …
from R,S,T,…
where Bedingung
Durch distinct wird als Ausgabe eine Relation erzeugt. Ansonsten wird eine M-Relation
ausgegeben.
‰ Die minus-Operation auf zwei Multi-Mengen entspricht der Semantik, wie wir sie bereits
bei der erweiterten relationalen Algebra kennengelernt haben. Das Schlüsselwort minus
wird nur von Oracle benutzt. In SQL92 wird stattdessen das Schlüsselwort except
benutzt.
–
except all entspricht der Summendifferenz der erweiterten relationalen Algebra
‰ Bei der Vereinigung auf Relationen werden automatisch Duplikate beseitigt. Dies gilt
auch für M-Relationen. Sollen Duplikate nicht beseitigt werden, muss hinter dem
Schlüsselwort union das Schlüsselwort all folgen.
Seite 72 von 109
SQL: Die Sprache relationaler DBMS
Beispielanfragen
Datenbankschema (zur Erinnerung):
Kunde (KName, KAdr, Kto)
Auftrag (KName, Ware, Menge)
Lieferant (LName, LAdr, Ware, Preis)
Welche Lieferanten liefern Milch oder Mehl?
select distinct LName
from Lieferant
where Ware = 'Mehl' or Ware = 'Milch'
Welche Lieferanten liefern irgendetwas, das Huber bestellt hat?
select distinct LName
from Lieferant, Auftrag
where Lieferant.Ware = Auftrag.Ware and KName = 'Huber'
Seite 73 von 109
SQL: Die Sprache relationaler DBMS
From-Klausel
‰ Innerhalb einer From-Klausel können auch Tupelvariablen definiert werden.
…
from Lieferant L /* L Ist eine Tupelvariable */
…
‰ Damit kann nun über die Tupelvariable die Attribute in der Relation L angesprochen
werden.
–
Dies ist notwendig, wenn Attribute mit gleichem Namen in den Relationen
existieren. Z. B. dann, wenn ein Join einer Relation mit sich selbst ausgeführt wird.
select distinct L1.LName, L2.LName, L1.Adresse
from Lieferant L1, Lieferant L2
where L1.Adresse = L2.Adresse
–
Man beachte, dass bei Angabe eines Attributs die Tupelvariable (bzw.
Relationennamen) angegeben werden muss, es sei denn das Attribut kann eindeutig
einer Relation in der from-Klausel zugeordnet werden.
‰ In einer from-Klausel können nicht nur physische Relationen angegeben werden, sondern
auch wiederum Anfragen auftreten:
select L1.LName
from (select LName, Ware from Lieferant where Ware = 'Kaffee') L1
Seite 74 von 109
SQL: Die Sprache relationaler DBMS
–
Dies wird aber nicht in Interbase unterstützt
Joins in der From-Klausel
‰ Die Joinbedingung kann sowohl direkt in der from-Klausel als auch in der where-Klausel
angegeben werden. Letzteres wird in allen Systemen unterstützt.
‰ Natural Join /* Wird nicht von Interbase, SQL Server, … unterstützt */
… from R natural join S …
oder
… from R join S using (
θ -Join (auch als inner join bezeichnet)
… from R join S on A θ B …
‰ left outer join
… from T left join S on A θ B …
‰ right outer join
… from T right join S on A θ B …
‰
Bemerkung
‰ Im wesentlichen wurde diese Notation zur Formulierung von äußeren Joins eingeführt.
Seite 75 von 109
SQL: Die Sprache relationaler DBMS
Einfache Formeln in Where-Klauseln
‰ In einer where-Klausel wird eine Boolesche Funktion angegeben, die im wesentlichen
einer Formel des Tupelkalküls entspricht. Wir wollen zunächst einfache Formeln
betrachten.
Im wesentlichen setzen sich Formeln aus Atomen der Form A op B zusammen,
‰ op ist die Menge relationaler Operatoren
‰ Diese Atome können mit den Operatoren not, or und and zu komplexeren Formeln
verknüpft werden.
‰ Dabei können sich A und B nicht nur auf Attribute bzw. Konstanten beziehen, sondern
können komplexere Terme sein, die durch Verwendung der üblichen Operationen
definiert werden.
–
Bei numerischen Werten sind dies unter anderem die 4 Grundoperationen.
Weiterhin können alle im DBS angebotenen Operationen wie z. B. abs verwendet
werden.
–
Bei Zeichenketten umfasst dies insbesondere die Konkatenation “||”.
Darüberhinaus gibt es noch weitere Varianten von atomaren Formeln:
‰ Das Schlüsselwort and kommt nochmals als Bestandteil eines anderen Operators vor:
Seite 76 von 109
SQL: Die Sprache relationaler DBMS
–
A between B and C
–
Diese Boolesche Funktion ist äquivalent zu B <= A and A <= C
‰ Ein im Zusammenhang mit Strings wichtiger Operator in der where-Klausel ist like.
–
A like B
Hierbei wird überprüft, ob die Zeichenkette A gleich der Zeichenkette B ist. Der Term
kann nicht nur eine gewöhnliche Zeichenkette sein, sondern auch Wildcards enthalten:
–
% bedeutet, dass hier beliebig viele Zeichen stehen können.
–
_ bedeutet, dass genau ein beliebiges Zeichen hier steht.
Beispiel:
select KName
from Kunde
where KAdresse like '%b_rg'
‰ Eine weitere atomare Formel benutzt das Schlüsselwort in:
–
A in (b,c,…,z)
Dabei ist A ein beliebiger Ausdruck und b,…,z Konstanten.
Dieser Ausdruck is äquivalent zu
–
A = b or A = c or … or A = z
Seite 77 von 109
SQL: Die Sprache relationaler DBMS
Nullwerte
‰ SQL benutzt zur Unterstützung von Nullwerten in Anfragen eine dreiwertige Logik.
‰ Beispiel
–
Annahme: Es gibt ein Tupel ('Schneider', 'Lampen', NULL) in Relation Auftrag.
–
Sowohl
select * from Auftrag where Menge > 100
als auch
select * from Auftrag where not Menge > 100
liefert uns nicht das Tupel als Ergebnis.
‰ Wir benötigen noch eine Möglichkeit, um Tupel mit Nullwerten zu finden. Dazu bedient
man sich in SQL92 des Schlüsselworts IS:
–
select * from Auftrag where Menge is null
liefert die Tupel, deren Attribut Menge keinen Wert besitzt.
Bemerkung
‰ Man könnte auch ein relationales Modell ohne die Verwendung von NULL-Werten
entwickeln.
‰ Die Semantik von NULL-Werten ist sehr unterschiedlich in den kommerziellen DBMS.
Seite 78 von 109
SQL: Die Sprache relationaler DBMS
Select-Klausel
‰ Die Select-Klausel wird als letztes bei der SQL-Anfrage ausgeführt.
‰ Wurde eine Relation R berechnet, so werden durch
select * …
die Tupel mit allen Attributen der Relation R ausgegeben.
‰ Entsprechend können alle Attribute eine am Join beteiligten Relation S durch
select S.* from S,… where …
ausgegeben werden.
‰ Prinzipiell kann als Ergebnis in der select-Klausel ein Ausdruck stehen, der von mehreren
Attributen abhängt. Man kann nun durch das Schlüsselwort as einer Spalte explizit einen
neuen Namen zuweisen.
select A*B as X from R
Man kann dabei das Schlüsselwort as auch einfach weglassen.
‰ Zur Wiederholung: select distinct beseitigt Duplikate, wohingegen select all dies nicht
macht (Default ist select all)
Seite 79 von 109
SQL: Die Sprache relationaler DBMS
Aggregatfunktionen
‰ In SQL werden folgende Aggregatfunktionen angeboten: count, sum, avg, min und max.
–
Aggregate dürfen nur in der select-Klausel einer Anfrage auftreten. Man beachte,
dass die select-Klausel zuletzt angewendet wird und dass zuvor eine Relation R als
Zwischenergebnis bereits berechnet wurde.
–
Wird ein Aggregat berechnet, ist das Ergebnis eine Relation mit genau einem Tupel.
–
Als Parameter eines Aggregats ist ein Ausdruck erlaubt. Das Aggregat count kann
auch als Parameter einen “*” besitzen. Dann wird als Ergebnis die Anzahl der Tupel
der Relation R geliefert.
–
Bei Angabe des Schlüsselworts distinct vor dem Term werden zunächst die
Duplikate beseitigt, die durch Auswertung des Terms auf der Relation R entstehen,
bevor das eigentliche Aggregat berechnet wird.
‰ Die Aggregatfunktionen min, max und count können auf beliebige Ausdrücke
angewendet werden. Die Funktionen sum und avg erwarten als Eingabe eine Zahl.
–
min berechnet das Minimum
–
max berechnet das Maximum
–
count die Anzahl der Terme
– sum berechnet die Summe aller Terme
–
avg berechnet den Durchschnitt über alle Terme.
Seite 80 von 109
SQL: Die Sprache relationaler DBMS
Beispiele
‰ Wieviele Liter Milch wurden insgesamt bestellt?
select sum (Menge)
from Auftrag
where Ware = 'Milch'
‰ Wieviele Lieferanten mit verschiedenen Namen gibt es?
select count (distinct LName)
from Lieferant
‰ Berechne die Anzahl der Lieferanten, die Milch liefern, sowie deren durchschnittliche,
minimale und maximale Liefermenge.
select count(KName), min(Menge), avg(Menge), max(Menge)
from Auftrag
where Ware = 'Milch'
Nullwerte und Aggregate
‰ Nullwerte werden bei der Berechnung eines Aggregats nicht berücksichtigt, mit
Ausnahme von count(*).
‰ Ist die Eingabe für das Aggregat leer, wird als Ergebnis null geliefert. Nur bei count wird
der Wert 0 zurück gegeben.
Seite 81 von 109
SQL: Die Sprache relationaler DBMS
Gruppierung
Allgemeinere Form der “select...from...where”-Klausel:
select .....
from.....
[where.....]
[group by <group-by-expression>[,<group-by-expression>]*]
[having < Bedingung>]
[order by <order-expression>]
“group by”-Klausel
‰ Diese wird nach der where-Klausel ausgeführt, aber noch vor der select-Klausel.
‰ Zunächst werden anhand der Attribute in der Klausel Äquivalenzklasse gebildet.
–
Zwei Tupel sind in der gleichen Klasse, wenn diese bzgl. der in der Klausel
spezifizierten Attribute gleich sind.
‰ Pro Klasse wird dann durch die select-Klausel ein Tupel erzeugt. Die select-Klausel
besteht dabei nur aus
– Aggregaten, die dann auf die Gruppen angewendet werden.
– Attributen, die bereits in der group_by-Klausel aufgetreten sind.
Seite 82 von 109
SQL: Die Sprache relationaler DBMS
Bemerkung
‰ Wird für das group-by Attribut der Wert NULL angenommen, so wird eine
entsprechende Gruppe eröffnet.
“having”-Klausel
‰ Filtern der durch die group_by-Klausel erzeugten Gruppen anhand einer Bedingung
– Es dürfen nur Argumente mit einem Wert pro Gruppe auftreten.
–
Innerhalb einer having-Klausel sind Aggregate erlaubt (im Gegensatz zur whereKlausel)
Beispiel:
select LName
from Lieferant
where Preis > 100
group by LName
having count (*) > 5
Seite 83 von 109
SQL: Die Sprache relationaler DBMS
“order by”-Klausel
order by [asc|desc] A1,…,[asc|desc] An
‰ Durch diese Klausel wird die Ausgabe des SQL-Befehls sortiert ausgegeben, wobei die
Sortierreihenfolge bzgl. den angegebenen Attributen erfolgt (absteigend: desc oder
aufsteigend: asc). Statt eines Attributs kann auch ein Ausdruck benutzt werden.
‰ Sind mehrere Kriterien angegeben, so wird nach deren lexikographischer Ordnung
sortiert.
‰ Die order-by Klausel ist die letzte Klausel in einem SQL-Befehl
‰ Unterschied zwischen SQL92 und dem SQL von Oracle
– Bei SQL92 kann nur ein Attribut (Ausdruck) in der order-by Klausel verwendet
werden, das auch in der select-Klausel vorzufinden ist.
–
Bei Oracle und Interbase kann auch bzgl. Attributen sortiert werden, die nicht in der
select-Klausel auftreten.
‰ Beim Sortieren wird null entweder stets als höchster oder kleinster Wert interpretiert
(unabhängig ob asc oder desc angegeben wurde).
–
Welche der beiden Möglichkeiten gewählt wird, hängt vom zugrundeliegenden
DBMS ab.
Seite 84 von 109
SQL: Die Sprache relationaler DBMS
Beispiele
‰ Erstelle eine alphabetisch geordnete Liste aller Waren, in der für jede Ware der minmale,
der maximale und der Durchschnittspreis angegeben ist!
select
Ware, min (Preis) as MinP, max (Preis) as MaxP, avg (Preis) as AvgP
Lieferant
from
Ware
group by
order by
Ware
‰ Welche Waren werden nur von einem Lieferanten geliefert?
Ware
select
Lieferant
from
Ware
group by
count(*) = 1
having
‰ Sortiere die Bestellungen nach Waren, für jede Ware die Kunden nach der Größe der
Bestellung!
*
select
Auftrag
from
Ware, Menge desc
order by
Seite 85 von 109
SQL: Die Sprache relationaler DBMS
2.2.1 Geschachtelte Anfragen
‰ In einer SQL-Anweisung können in der where-, from- und select-Klausel wieder SQLAnweisungen auftreten. Man spricht dann von einer geschachtelten Anfrage oder
Unteranfrage.
‰ Unteranfrage in der from-Klausel (ist nicht in Interbase möglich)
– Wieviel Stück werden von einer Ware im Durchschnitt bestellt?
select avg(T.summe)
from (select Ware, count(*) as summe from Auftrag group by Ware) T
–
Solche geschachtelten Aggregate lassen sich ansonsten in SQL nicht in einer
Anfrage ausdrücken.
‰ Unteranfragen in der select-Klausel (in Interbase eingeschränkt möglich)
–
Damit ist es möglich nach einem Attribut zu gruppieren und gleichzeitig im
Aggregat verschiedene where-Bedingungen zu berücksichtigen.
–
Beispiel:
select Ware,
(select count(*) from Auftrag A where A.Ware = B.Ware and Menge > 20),
(select count(*) from Auftrag A where A.Ware = B.Ware and Menge <= 20)
from Auftrag B
Seite 86 von 109
SQL: Die Sprache relationaler DBMS
Unteranfragen in der where-Klausel
‰ In der where-Klausel wird dabei noch unterschieden, ob das Resultat der Unteranfrage
einen skalaren Wert oder eine Relation zurückliefert. Wir betrachten zunächst den ersten
Fall:
Skalare Unteranfragen
‰ Welche Lieferanten liefern Lampen, deren Preis 50% unter dem Durschnittspreis für
Lampen liegen?
select LName, Preis
from Lieferant
where Ware = 'Lampe' and Preis < (
select avg(Preis)/2
from Lieferant
where Ware = 'Lampe')
Skalare Unteranfragen mit Exists
‰ In der where Klausel werden auch Unteranfragen erlaubt, die einen Booleschen Wert
zurückliefern. Diese sind durch das Schlüsselwort exists gekennzeichnet. Dabei ist die
Bedingung
–
exists <Subquery>
wahr, falls die Unteranfrage nicht leer ist.
Seite 87 von 109
SQL: Die Sprache relationaler DBMS
‰ Welche Lieferanten liefern irgendetwas, das Huber bestellt hat?
select distinct LName
from Lieferant L
where exists (select Ware
from Auftrag
where L.Ware = Ware and KName = 'Huber')
Gültigkeit von Tupelvariablen in Unteranfragen
‰ Bei dieser Unteranfrage wird Bezug genommen auf eine Tupelvariable, die in der
äußeren Anfrage definiert wurde. Bei der Auswertung der Anfrage wird entsprechend so
wie beim Tupelkalkül vorgegangen (“von außen nach innen”). Man spricht dann auch
von einer korrelierten Unteranfrage.
–
Eine unkorrelierte Unteranfrage braucht nur einmal ausgewertet zu werden.
Dagegen muss eine korrelierte Anfrage für jedes äußere Tupel ausgewertet werden.
‰ Eine Tupelvariable ist in allen zugehörigen Unteranfragen gültig. Eine Redeklaration der
gleichen Tupelvariable überdeckt die äußere Deklaration der Variablen.
–
Sucht man die Deklaration einer Tupelvariable geht man also von innen nach außen.
Es wird die Deklaration benutzt, die zuerst gefunden wird (und alle anderen
ignoriert).
Seite 88 von 109
SQL: Die Sprache relationaler DBMS
Mengenwertige Unteranfragen
‰ Durch Verwendung vom Schlüsselwort in kann getestet werden, ob ein Attribut einen
Wert in einer Menge annimmt (oder auch nicht)
select KName, KAdr
from Kunde
where Kunde in
(select KName from Auftrag)
Wie kann die Anfrage formuliert werden, ohne dabei eine Unteranfrage zu benutzen?
‰ Soll nun getestet werden, ob ein Attribut mit allen Elementen einer Menge in einer
bestimmten Beziehung steht, kann das Schlüsselwort all benutzt werden.
Suche für alle Waren die Namen der günstigsten Lieferanten!
select LName, Ware
from Lieferant L
where Preis <= all(
select Preis
from Lieferant
where Ware = L.Ware)
Seite 89 von 109
SQL: Die Sprache relationaler DBMS
Differenz durch Unteranfragen
‰ Eine Differenz zwischen Relationen kann über except ausgedrückt werden (wird nicht
von allen DBMS unterstützt) oder durch eine Unteranfrage mit not in und not exists.
‰ Beispiele:
Berechne alle Kunden, die derzeit keine Waren bestellt haben.
select *
from Kunde
where Kname not in (select Kname from Auftrag)
oder
select *
from Kunde K
where not exists (select KName from Auftrag where KName = K.KName)
Seite 90 von 109
SQL: Die Sprache relationaler DBMS
Anfragen mit Allquantoren
‰ Da ∀x ( ψ ( x ) ) ⇔ ¬∃x ( ¬ψ ( x ) ) , können alle Anfragen mit einem Allquantor in
äquivalente Anfragen umgeformt werden, die nur noch Existenzquantoren benutzen.
‰ Welche Lieferanten liefern alles, was Bercken bestellt hat?
select distinct LName
from Lieferant L
where not exists( select Ware
from Auftrag
where KName = 'Bercken' and not Ware in (select Ware
from Lieferant
where LName =L.LName))
Seite 91 von 109
SQL: Die Sprache relationaler DBMS
2.2.2 Ändern einer Relationeninstanz
‰ Tupelweises Einfügen:
insert
into <Relationen-Name> [(<Attributname> [, <Attributname>]*)]
values (<Konstante> [, Konstante]*)
oder mengenweises Einfügen
insert
into <Relationen-Name> [(<Attributname> [, <Attributname>]*)]
select ... from ... where ....
‰ Löschen:
delete
from <Relationen-Name>
[where <Bedingung>]
‰ Verändern:
update <Relationen-Name>
set <Attributname> = <Ausdruck> [, <Attributname> = <Ausdruck>]*
[where <Bedingung>]
Seite 92 von 109
SQL: Die Sprache relationaler DBMS
Beispiele
‰ Füge den Kunden Zeitler mit dem Kontostand 0 DM ein!
insert into Kunde (KName, Kto)
values ('Zeitler', 0)
/* KAdresse wird automatisch auf den Defaultwertauf, in diesem Fall null, gesetzt */
‰ Erhöhe den Kontostand von Zeitler um 200!
update Kunde
set Kto = Kto + 200
where KName = 'Zeitler'
Seite 93 von 109
SQL: Die Sprache relationaler DBMS
2.3 Datenintegrität
Integritätsbedingungen
‰ dienen zur Einschränkung der Datenbankzustände auf diejenigen, die tatsächlich in der
realen Welt existieren.
‰ sind aus dem erstellten Datenmodell ableitbar (semantisch) und können deshalb bei der
Erstellung des Schemas bereits angegeben werden. Folgende Vorteile ergeben sich:
–
Konsistenzbedingungen werden nur einmal angegeben
–
AWPs sind befreit von der lästigen Überprüfung der Bedingungen
–
z. T. kann bei einer Masseneingabe aus Effizienzgründen auf eine Prüfung der
Bedingungen verzichtet werden
‰ statische Bedingungen
– sind definiert bzgl. Datenbankzuständen
‰ dynamische Bedingungen
– sind definiert bzgl. Zustandsänderungen
Seite 94 von 109
SQL: Die Sprache relationaler DBMS
2.3.1 Schlüssel
Schlüssel in einer Relation (mittels primary key)
‰ Syntax
–
Wenn der Schlüssel nur ein Attribut umfaßt kann primary key an das Ende der
Attributdeklaration geschrieben werden.
–
Wenn der Schlüssel mehrere Attribute umfaßt, gibt es eine eigene Klausel
primary key (A1,…,An)
–
Es gibt höchstens einen Primärschlüssel in einer Relation.
‰ Semantik
–
Die als Primärschlüssel spezifizierten Attribute sind eindeutig.
–
Die attribute dürfen nicht den Wert null annehmen.
Schlüssel (mittels unique)
‰ Syntaktisch wird nun das Schlüsselwort unique benutzt.
‰ Semantik (SQL´92, nicht in Interbase)
–
Im Gegensatz zum Primärschlüssel ist der Wert null erlaubt.
–
Es können mehrere Tupel existieren, die im unique-Attribut den Wert null besitzen.
Seite 95 von 109
SQL: Die Sprache relationaler DBMS
Fremdschlüssel
‰ sind Bedingungen an Relationen, die (insbesondere) eine Beziehung modellieren
‰ Semantik:
Seien R1 und R2 Relationen mit dem Schema RS1 und RS2. Sei K ( ⊆ RS1). Schlüssel
von R1 (gekennzeichnet durch primary key oder unique). Dann wird F ⊆ RS2
Fremdschlüssel von R2 genannt, falls zu jedem Datensatz s aus der Relation R2 eine der
folgenden Bedingungen gilt:
–
s[F] = NULL
–
es gibt einen Datensatz r aus R1, so daß s[F] = r[K] gilt.
‰ Mögliche Probleme, wenn referentielle Integrität nicht erfüllt ist:
–
Kunde bestellt eine Ware, die es nicht gibt.
–
Waren können von Kunden bestellt werden, der nicht existieren.
‰ Definition einer Fremdschlüsselbedingung:
KNr muß bereits in der Relation Kunde vorher als Primärschlüssel oder mit dem Schlüsselwort unique gekennzeichnet worden sein.
–
create table Bestellt(KNr int,,…,
foreign key(KNr) references Kunde(Knr))
Seite 96 von 109
SQL: Die Sprache relationaler DBMS
oder
–
create table Bestellt(KNr int references Kunde(Knr), …)
Löschoperationen
Dies ist nur möglich, wenn die referentielle Integrität auf einem Attribut definiert ist.
oder
–
create table Bestellt(KNr int,,…,
constraint test foreign key(KNr) references Kunde(Knr))
In diesem Fall wird die Bedingung mit einem Namen versehen, was insbesondere die
Flexibilität bei Änderungsoperationen erhöht.
Seite 97 von 109
SQL: Die Sprache relationaler DBMS
Einhaltung referentieller Integrität
‰ in einer Relation, die eine Beziehung modelliert, sollte gewährleistet sein, daß die
Fremdschlüssel mit Werten belegt sind.
‰ relationale Algebra:
– Relation R mit Primärschlüssel K
–
Relation S mit Fremdschlüssel F (bezieht sich auf K)
πF ( S ) ⊆ πK ( R )
‰ erlaubte Änderungen
–
Einfügen eines Tupels s in S, wenn s [ F ] ∈ π K ( R )
–
Verändern eines Attributwerts eines Tupels s, wenn …
–
Verändern von r [ K ] eines Tupels r, wenn σ F = r [ K ] ( S ) = ∅
Löschen eines Tupels r aus R, wenn …
–
Seite 98 von 109
SQL: Die Sprache relationaler DBMS
Überwachung der referentiellen Integrität
Defaulteinstellung
‰ Löschen eines Tupels r aus einer Relation R ist i.a. nicht möglich, falls es noch Tupel aus
anderen Relationen gibt, die über einen Fremdschlüssel an r gebunden sind.
Kaskadierendes Löschen/Ändern
‰ Wenn ein Tupel r aus einer Relation R gelöscht/geändert wird, können auch Datensätze
aus anderen Relationen automatisch gelöscht/geändert werden, die sich über einen
Fremdschlüssel auf das Tupel r beziehen.
‰ Bei der Definition des Fremdschlüssels kann diese Einstellung mitaufgenommen werden:
Setzen auf Null
‰ Wird ein Tupel gelöscht, wird der Wert aller davon abhängigen Attribute auf null gesetzt.
‰ create table Bestellt(
KName char(20) constraint fk_kunde references kunde(KName)
on delete cascade)
on update set null,
…
)
Seite 99 von 109
SQL: Die Sprache relationaler DBMS
Zeitpunkt der Überprüfung
‰ In SQL gibt es noch die Möglichkeit den Zeitpunkt der Überprüfung einer
Integritätsbedingung zu beeinflussen.
‰ Motivation
–
Eine Überweisung soll 2000 EURO von einem Konto K1 eines Kunden auf ein
anderes Konto K2 des gleichen Kunden übertragen.
–
Eine Integritätsbedingung gewährleistet, dass die Kontostände über -5000 liegen.
–
Was passiert bei einem Kontostand von jeweils -2000 Euro?
‰ Anforderung
–
Bündelung von mehreren Operationen zu einer Einheit (Transaktion)
–
Überprüfung der Integritätsbedingung am Ende einer Transaktion
‰ Eine Integritätsbedingung kann nun mit folgenden Schlüsselworten versehen werden:
–
not deferrable: Die Integritätsbedingung wird direkt hinter einer Änderung
überprüft.
–
deferrable: Möglichkeit der verzögerten Überprüfung ist vorhanden.
– deferrable initially deferred: Überprüfung erfolgt am Ende der Transaktion.
–
deferrable initially immediate: Überprüfung vor der Änderung.
Seite 100 von 109
SQL: Die Sprache relationaler DBMS
‰ Eine mit dem Schlüsselwort deferrable versehene Bedingung mit einem Namen, sagen
wir einfach MeineBedingung, kann noch zur Laufzeit angepaßt werden:
–
set constraint MeineBedingung deferred
– set constraint MeineBedingung immediate
Seite 101 von 109
SQL: Die Sprache relationaler DBMS
2.3.2 Statische Integritätsbedingungen
Attributs-Bedingungen
‰ sind Bedingungen, die sich auf ein Attribut einer Relation beziehen.
‰ Ausschluß von Nullwerten
create table Kunde (KName char(20) not null, …)
–
Werte des entsprechenden Attribute müssen bei jedem Tupel vorliegen.
‰ Check-Bedingungen
–
sind beliebig komplexe Bedingungen, die entsprechend einer where-Klausel von
SQL angegeben werden können. Dabei kann man sich direkt auf das Attribut
beziehen.
–
Beispiel:
create table Kunde( KName char(20)
check(100 > (select count(*) from Auftrag A where A.KName = KName)),
…
)
–
Die Überprüfung dieser Bedingung findet immer dann statt, wenn das Attribut durch
eine Änderung betroffen ist. Dies sorgt aber nicht dafür, dass auch tatsächlich die
Bedingung stets erfüllt bleibt (wenn z. B. die Bedingung noch von einem Attribut
oder einer anderen Relation abhängt).
Seite 102 von 109
SQL: Die Sprache relationaler DBMS
Relationen-Bedingungen
‰ Einschränkung der Werte, die ein Tupel bzgl. seiner verschiedenen Attribute annehmen
darf.
–
In SQL kann eine Tupel-Bedingung durch die check-Klausel beim Anlegen des
Schemas angegeben werden.
– In der Bedingung kann man sich auf alle Attribut der zugehörigen Relation
beziehen.
‰ Diese Bedingung wird immer dann überprüft, wenn ein Tupel in die Relation eingefügt
wird oder ein bestehendes Tupel sich ändert.
‰ Die Deklaration einer solchen Bedingung erfolgt direkt bei der Deklaration der Relation
oder zu einem späteren Zeitpunkt.
Beispiele:
–
Stelle sicher, dass ein Kunde derzeit nicht mehr als 100 Waren bestellt hat.
alter table Auftrag add
check(100 > (select count(*) from Auftrag A where A.KName = KName))
–
Stelle sicher, dass die Summe der Kontostände aller Kunden über 500 liegt.
alter table Kunde add check(500 < (select sum(KTO) from Kunde K))
Seite 103 von 109
SQL: Die Sprache relationaler DBMS
2.3.3 Verwalten von Integritätsbedingungen
‰ Integritätsbedingungen können in SQL durch Verwendung des Schlüsselworts constraint
implementiert und dabei mit einem Namen versehen werden.
Hinzufügen/Löschen von Integritätsbedingungen
‰ alter table Bestellt
add constraint plus_const check (Preis*Anzahl < 10000)
–
Dies ist eine Tupel-Bedingung. Ein Hinzufügen von Attribut-Bedingungen ist nicht
möglich.
‰ alter table Kunde
add constraint name_unique unique KName
‰ alter table <Name> drop constraint <CName>
–
Löschen wird für beliebige Bedingungen unterstützt.
Seite 104 von 109
SQL: Die Sprache relationaler DBMS
Schema-Bedingungen
‰ Hierbei handelt sich um die mächtigste Form von Integritätsbedingungen.
create assertion <name> check <condition>
–
Im Gegensatz zu den vorherigen check-Klauseln gibt es nicht die Möglichkeit sich
direkt auf ein Attribut einer Relation zu beziehen.
–
Die Deklaration erfolgt außerhalb einer Deklaration einer Relation.
–
Die Überprüfung der Bedingung erfolgt immer dann, wenn eine der beteiligten
Relationen geändert wird.
Vergleich von verschiedenen Bedingungen
Typ
Attribut-Bedingung
Ort der
Deklaration
Attribut
Auslösen der
Überprüfung
Gültigkeit
Einfügen in die Relation Nein bei UnterÄndern des Attributs
anfragen
Relationen-Bedingung Relationenschema Einfügen in die Relation Nein bei UnterÄndern eines Tupels
anfragen
Schema-Bedingung
Datenbankschema Änderung einer der
beteiligten Relationen
Ja
Seite 105 von 109
SQL: Die Sprache relationaler DBMS
2.4 Anlegen anderer Strukturen
Anlegen eines Index
‰ Indexe sind Datenstrukturen der physischen Ebene. Sie dienen “nur” zur Verbesserung
der Anfragezeit und haben insbesondere keinen Einfluß auf die Anfragesemantik.
‰ Ein Index bezieht sich auf ein Attribut, bzw. eine Folge von Attributen
‰ Maß für die Effizienz ist i. A. die Anzahl der Plattenzugriffe, sowie die Größe des Index
‰ In kommerziellen Systemen: B-Bäume und Hashverfahren
create [unique] index <Index-Name> on <Relationen-Name>
(<Attributname> [<Ordnung>] [,<Attributname>[<Ordnung>]]*)
<Ordnung>::= Asc | Desc
‰ unique: Für alle Attributsnamen keine zwei Tupel mit gleichen Werten
erlaubt ⇒ erfüllt Schlüsselbedingung.
Seite 106 von 109
SQL: Die Sprache relationaler DBMS
‰ In anderen Datenbanksystemen, wie z. B. Oracle, können noch weitere Optionen beim
Erzeugen von Indexen angegeben werden.
–
Ein Clusterindex bestimmt die physische Ordnung der Daten.
– Ein “gewöhnlicher” Index hat keinen Einfluß auf die Ordnung. Man spricht dann
auch von einem Sekundärindex.
– Es gibt in der Literatur leider sehr unterschiedliche Begriffe für die verschiedenen
Varianten von Indexen.
Beispiel:
create unique index Kundenindex on Kunde (KName,KAdresse)
Löschen eines Index
drop index <Index-Name>
Seite 107 von 109
SQL: Die Sprache relationaler DBMS
Anlegen von Sichten
‰ Sichten entsprechen den externen DB-Schemata.
‰ In relationalen Systemen werden Sichten als (abgeleitete) Relationen aufgefaßt, die durch
Anfragen definiert werden.
create view <Sichtname> [(<Attributname>[,<Attributname>]*)] as <Subquery> [with check
option]
Beispiel:
create view Gute_Kunden as
select * from Kunde where Kto > 100
with check option
Sichten löschen:
drop view <Sicht-Name>
Seite 108 von 109
SQL: Die Sprache relationaler DBMS
Ändern einer Sichteninstanz
‰ Durch das Schlüsselwort with check option können nur Datensätze in eine Sicht eingefügt
werden, die bei einer Suche auf der Sicht auch wieder gefunden werden können.
–
Dies ist die einzig sinnvolle Variante einer View.
‰ Beim Einfügen eines Tupels in einer Sicht müssen die Basisrelationen angepaßt werden.
die zur Definition der Sicht benutzt wurden.
–
In einer Sicht werden keine Daten gespeichert!
‰ Die Zuordnung zu den Basisrelationen ist aber nicht immer möglich!
–
z. B. wenn ein Attribut einer Sicht durch eine Aggregatfunktion berechnet wird.
‰ Sichten sind in Oracle / Interbase veränderbar, wenn folgende Bedingungen gelten:
–
keine Aggregatfunktionen
–
keine Anweisungen mit distinct, group by, having, union und minus
–
from-Klausel enthält nur eine Relation
–
ein Schüssel der Basisrelationen muss in der select-Klausel enthalten sein.
‰ Es gibt aber durchaus veränderbare Sichten, die aber nicht alle vier der oben genannten
Bedingungen, erfüllen.
Seite 109 von 109