Relationale Datenbanken

Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
Kapitel 05
Relationale Datenbanken
5 Relationale Datenbanken
5
Relationale Datenbanken ....................................................1
5.1
Das relationale Datenmodell........................................3
5.2
Modellierung im relationalen
5.3
Schlüssel......................................................................5
5.4
Arbeiten mit relationalen Datenbanken........................6
5.5
Sprachen für relationale Datenbanken ........................8
Datenmodell...........5
5.5.2 Relationenalgebra ....................................................9
5.5.3 Relationenkalkül .....................................................16
5.6
Beispiel für relationale Abfragesprachen ...................23
5.6.1 Die Abfragesprache SQL .......................................23
5.6.2 Die Abfragesprache Query-by-Example.................30
Seite 1
Prof. Dr. M.-R. Wolff
5.7
BWL / Wirtschaftsinformatik
Optimierung von Abfragen .........................................34
5.7.1 Algebraische Optimierung ......................................35
5.7.2 Optimierung auf der physischen Ebene .................38
5.7.3 Ausnutzung von Sekundärindexen.........................39
Seite 2
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
5.1 Das relationale Datenmodell
In einer relationalen Datenbank werden die Daten als eine
Sammlung von Relationen dargestellt, die dann auch die
Objekte für die Datenmanipulation sind.
Definition:
1. Sind D1, D2, ..., Dn Mengen von Werten, so ist die
Teilmenge
R ⊆ D1 × D2 × ... × Dn
eine n-stellige Relation über den Mengen (domains) D1,
D2, ..., Dn. Die Zahl n gibt dabei den Grad (degree) der
Relation an.
2. Ein Element r = (d1, d2, ..., dn) ∈ R mit di ∈ Di für i=1, ...,
n ist ein Tupel der Relation R, auch n-Tupel genannt.
Der Eintrag di ist dabei die i-te Komponente des Tupels.
Ein Entity-Typ wird durch eine Attributkombination beschrieben,
ein Entity von diesem Typ wird dann durch eine entsprechende
Attributwertkombination charakterisiert. Man kann somit in
natürlicher Weise die Entities eines bestimmten Typs als
Relation über den Wertebereichen der entsprechenden
Attribute auffassen.
Seite 3
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
Beispiel:
NAME
BERUF
WOHNORT
GEB.JAHR
Weber
Dreher
München
1927
Müller
Boxer
Berlin
1948
Mayer
Schneider
Köln
1943
Relationenschema:
Die Definition eines Relations-Typs R erfolgt im
Relationenschema. Ein Relationenschema besteht aus
folgenden Punkten:
• einem Relationennamen
• einer Liste von Attributnamen
• der Beschreibung der Wertmengen der Attribute
• der Beschreibung der Integritätsbedingungen
Seite 4
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
5.2 Modellierung im relationalen
Datenmodell
Die Entity-Typen ANGESTELLTER und PROJECT können wir
durch die Relationenschemata ANGEST und PROJEKT
darstellen:
ANGEST (ANGNR, ANG-NAME, WOHNORT, BERUF, ABT-NR)
PROJEKT (PNAME, PNR, P-BESCHR, P-LEITER)
Die Beziehung zwischen beiden stellen wir durch ein weiteres
Relationenschema ANG-PRO dar:
ANG-PRO (PNR, ANGNR, PROZ-ARB)
5.3 Schlüssel
Da eine Relation eine Menge ist, sind die Tupel unterscheidbar.
Es gibt also für jedes Relationenschema R eine Menge von
Attributen - im Extremfall alle Attribute von R-, deren Werte für
die Tupel identifizierend sind. Solche Attributmengen nennen
wir Schlüssel
Seite 5
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
5.4 Arbeiten mit relationalen Datenbanken
Da wir alle Daten in der Datenbank als Relationen ansehen, ist
auch das Ergebnis einer Anfrage (Query) eine Relation.
Relationsschema ANGEST
AN- ANG-
NAME
WOHNORT
BERUF
GEST NR
ABT
-NR
112 MUELLER KARLSRUHE
PROGR
3
205
WINTER
HANAU
ORGANISATOR
3
117
SEELER
MANNHEIM
ING
5
KAUFMANN
4
.....
.....
198 SCHMIDT KARLSRUHE
.....
.....
.....
Relationsschema PROJEKT
PROJEKT
PNAME PNR P-BESCHR
P2
12
.....
P4
18
.....
P3
17
.....
P5
33
.....
Seite 6
P-LEITER
205
198
198
117
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
Relationsschema ANG-PRO
ANG-PRO PNR ANGNR PROZ-ARB
12
205
100
18
117
20
33
117
80
33
400
100
17
198
30
18
198
70
17
112
100
Relationsschema A-P-A
A-P-A
ANGNR
NAME
112
MUELLER
205
WINTER
117
SEELER
117
SEELER
198
SCHMIDT
198
SCHMIDT
Seite 7
PNR
17
12
18
33
17
18
PROZ-ARB
100
100
20
80
30
70
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
5.5 Sprachen für relationale Datenbanken
Es gibt eine ganze Reihe von Vorschlägen für relationale
Datenmanipulationssprachen. Alle diese Sprachen lassen sich
jedoch auf einen der beiden folgenden grundlegenden Ansätze
zurückführen, oder sind Mischformen hiervon:
• Relationenalgebra
• Relationenkalkül
Relationenalgebra
Spezifikation von gewünschten Relationen durch Angabe einer
Folge von Operationen, mit der die Relationen aufgebaut
werden sollen. Der Benutzer wendet spezielle Operationen für
Relationen an, um seine gewünschte Relation zu konstruieren.
Relationenkalkül
Spezifikation von gewünschten Relationen in deskriptiver
Weise, d.h. ohne Angabe, welche Operationen zum Aufbau der
Relation verwendet werden sollen. Mit Hilfe des
Prädikatenkalküls wird die Menge der gewünschten Tupel
beschrieben. Dazu wird ein Prädikat bzw. eine Bedingung
angegeben, das die Tupel erfüllen müssen.
Seite 8
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
5.5.2 Relationenalgebra
Grundlegender Satz von Operationen für die Relationenalgebra
1. Vereinigung:
Vereinigung der Tupelmengen zweier Relationen.
2. Mengendifferenz:
Die Relation R-S ist die Menge der Tupel in R, die nicht auch in
S enthalten sind.
3. Kartesisches Produkt:
Sind R und S Relationen vom Grad gR bzw. gS, so ist R × S die
Menge der (gR + gS)-Tupel, deren erste gR Komponenten einem
Tupel in R, und deren letzte gS Komponenten einem Tupel in S
entsprechen.
4. Projektion:
Die Projektion dient dazu, Spalten aus Tabellen zu entfernen
und Spalten umzuordnen.
Wir betrachten eine Relation R vom Grad n und sei
r = (a1, ..., an) ∈ R.
Seite 9
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
Dann wird mit r[j] wird die j-te Komponente von r bezeichnet.
Wenn man mit
L = (i1, ..., is) für ik∈{1, ..., n} mit k=1, ..., s
eine Liste von Spaltennummern bezeichnet, so ist r[L] definiert
als:
r[L] = (r[i1], ..., r[is])
Die Projektion von R auf L ist dann definiert als:
R[L] = {r[L] | r ∈ R}
Beispiel zur Projektion:
Man betrachte in diesem Beispiel die folgende Relation R:
R
A
B
C
a
b
c
d
e
f
d
g
f
Die Relation R’ = R[ C, B ] hat dann die folgende Form:
R‘
C
B
c
b
f
e
f
g
Seite 10
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
5. Selektion:
Die Selektionsoperation selektiert aus einer Relation alle Tupel,
die eine gegebene Bedingung erfüllen. Darstellen lässt sich
eine solche Selektion auf folgende Weise:
R[Bedingung] = {r ∈ R | r erfüllt Bedingung}
Beispiel zur Selektion
Für die Relation R (A,B,C) ergibt R[A = d] schließlich die
folgende Relation R":
R‘‘ A
B
C
d
e
f
d
g
f
6. Verbund (join):
Wird benötigt um zwei Relationen bezüglich zweier Attribute mit
gleichen Wertmengen miteinander zu verbinden. Es seien A in
der Relation R und B in der Relation S diejenigen Attribute,
nach denen die beiden Relationen R und S verknüpft werden
sollen. Es muss daher für die Wertemengen der beiden
Attribute A und B die Bedingung D(A) = D (B) gelten.
Seite 11
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
Dann ist der Verbund R[AΘ B]S folgendermaßen definiert:
R[A Θ B]S = {r-s | r ∈ R, s ∈ S, r[A] Θ s[B]}
Die Notation r • s stellt die Konkatenation dar, d.h. es gilt:
(a1, ..., an) • (b1,...,bm) = (a1, ..., an, b1, ..., bm).
Θ steht stellvertretend für einen der arith. Vergleichsoperatoren.
Beispiel zum Join
Die beiden Relationen R und S seien wie folgt definiert.
R
A
1
6
8
1
S
B
2
5
8
1
C
3
5
9
7
D
E
9
3
5
7
Es werden nun R [A>E] S und R [C=E] S gebildet.
Seite 12
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
R [A>E] S A
6
8
8
B
5
8
8
C
6
9
9
D
9
9
5
E
3
3
7
R [C=E] S A B C D E
1
2
3
9
3
1
1
7
5
7
7. Natürlicher Verbund (natural join):
Sind R und S zwei Relationenschemata und A1, A2, ..., Ap
Attributnamen, die sowohl in der Relation R als auch in der
Relation S auftreten, so ist der natürliche Verbund von R und S
definiert als:
R NATJOIN S: (R × S)[R.A1 = S.A1 ∧... ∧ R.Ap = S.Ap]
[Ai1, Ai2, ..., Aiw]
Dabei stellt R.Aj mit j∈{1, ..., p} das Attribut in R × S mit dem
Namen Aj, das dem Attribut Aj in R entspricht. Entsprechendes
gilt auch für S.Aj. Man sagt dabei, das Attribut A wird mit dem
jeweiligen Relationennamen qualifiziert.
Die Liste der Attribute von R × S, d.h. alle Attribute von R und
S, ohne die Attribute S.A1, ..., S.Ap wird beschrieben durch Ai1,
Ai2, ..., Aiw.
Seite 13
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
R NATJOIN S kann man bei Verwendung der
relationenalgebraischen Grundoperationen wie folgt bilden:
Zunächst bildet man R × S und selektiert daraus alle
Tupel, bei denen die Werte für R.A1 und S.A1 und zugleich
auch die Werte für R.A2 und S.A2 usw. gleich sind.
Schließlich streicht man aus der so erhaltenen Relation die
Spalten für S.A1, ..., S.Ap.
Erstes Beispiel zum Natural Join
Es seien die Relation R und S gegeben
A
a
b
c
d
B
b
b
b
a
C
c
c
d
b
B
b
b
a
C
c
c
b
D
x
y
z
Seite 14
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
Es entsteht die Relation R NATJOIN S
A
a
a
b
b
d
B
b
b
b
b
a
C
c
c
c
c
b
D
x
y
x
y
z
Zweites Beispiel zum Natural Join
Finde die Namen aller Angestellten, die am Projekt mit der Nummer
17 mitarbeiten.
auf folgende Weise in Relationenalgebra formulieren:
ANGEST[ANGNR = ANGNR]ANG-PRO
[PNR = 17] [NAME]
Oder:
JOIN ANGEST, ANG-PRO BY ANGNR
GIVING R1
SELECT R1 WHERE PNR = 17 GIVING R2
PROJECT R2 OVER NAME GIVING RESULT.
Seite 15
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
5.5.3 Relationenkalkül
Bei Sprachen auf der Basis des Relationenkalküls gibt der
Benutzer die Definition einer Relation an, die aus den
vorhandenen Relationen seines externen Modells abgeleitet
werden soll.
Einführende Beispielabfragen
Finde die Wohnorte aller Angestellten, die Programmierer sind.
Diese Abfrage lässt sich dann formulieren als:
"Ermittle aus ANGEST eine Relation W mit dem Attribut WOHNORT.
W soll die Wohnorte derjenigen Tupel von ANGEST enthalten, für die
Beruf = 'Programmierer' gilt."
Im Relationenkalkül definiert man die gewünschte Relation
schließlich durch folgenden Ausdruck:
{ANGEST.WOHNORT | ANGEST.BERUF = 'Programmierer'}
Seite 16
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
Allgemeine Form des Relationkalküls
Ein Ausdruck im Relationenkalkül hat also allgemein die Form:
{t | q}
Das Prädikat q ist ein logischer Ausdruck von beliebiger
Komplexität, der in üblicher Weise aufgebaut ist aus:
1. Attributnamen
2. Konstanten
3. Vergleichsoperatoren (=, ≠, ...)
4. Booleschen Operatoren (∧, ∨ , bzw. AND, OR, NOT)
5. Existenzquantor ∃ ("es existiert")
6. Allquantor ∀ ("für alle")
7. Tupelvariablen
Freie und gebundene Variablen
Man kann sich die Begriffe der freien und gebundenen
Variablen etwas veranschaulichen mit folgendem Vergleich:
1. freie Variablen entsprechen globalen Variablen, die in
einem Programm außerhalb der betrachteten Prozedur
deklariert sind
2. gebundene Variablen entsprechen lokalen Variablen,
die in einem Programm innerhalb der betrachteten
Prozedur deklariert sind
3. die Quantifizierung entspricht der Variablendeklaration
Seite 17
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
Weiteres Beispiel
Finde den (oder die) Programmierer mit dem höchsten Gehalt
Wir formulieren diese Anfrage nun wie folgt:
RANGE ANGEST X
{ANGEST. NAME | (ANGEST.BERUF = 'PROGRAMMIERER') ∧
¬ ∃ (X . BERUF = 'PROGRAMMIERER' ∧ X . GEHALT
> ANGEST.GEHALT)}
Beispiel (Einfaches Retrieval)
Es soll die folgende Abfrage durchgeführt werden:
Finde die Nummern aller Angestellten der Abteilung 6, die in
Dortmund wohnen.
Die Umsetzung dieser Abfrage sieht dann folgendermaßen aus:
{ANGEST.ANGNR | ANGEST.ABT-NR = 6 ∧
ANGEST.WOHNORT = '4600 DO'}
Seite 18
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
Beispiel (Retrieval mit Existenz-Quantor)
Es soll die folgende Abfrage durchgeführt werden:
Finde die Namen der Angestellten, die an einem Projekt mitarbeiten.
Die Umsetzung dieser Abfrage sieht dann folgendermaßen aus:
RANGE ANG-PRO X
{ANGEST.NAME | ∃ X ( X.ANGNR = ANGEST.ANGNR)}
Beispiel 1 (Retrieval mit mehreren Existenzquantoren)
Es soll die folgende Abfrage durchgeführt werden:
Finde die Namen der Angestellten, die an einem Projekt mitarbeiten,
an dem auch der Angestellte mit der Nummer 10 mitarbeitet.
Die Umsetzung dieser Abfrage sieht dann folgendermaßen aus:
RANGE ANG-PRO X
RANGE ANG-PRO Y
{ANGEST.NAME | ∃ X (X.ANGNR = ANGEST.ANGNR ∧
∃ Y (Y.PNR = X.PNR ∧ Y.ANGNR = 10))}
Seite 19
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
Beispiel 2 (Retrieval mit mehreren Existenzquantoren)
Es soll die folgende Abfrage durchgeführt werden:
Finde die Namen der Angestellten, die an einem Projekt unter der
Leitung des Angestellten mit der Nr. 30 arbeiten.
Die Umsetzung dieser Abfrage sieht dann folgendermaßen aus:
RANGE PROJEKT X
RANGE ANG-PRO Y
{ANGEST.NAME | ∃ Y(Y.ANGNR = ANGEST.ANGNR ∧
∃ X (X.PNR = Y.PNR ∧ X.P-LEITER = 30))}
In einer realen Abfragesprache könnte man diese Anfrage
natürlich auch in einfachen überschaubaren Schritten
folgendermaßen formulieren:
W: {PROJEKT.PNR | PROJEKT.P-LEITER = 30}
RANGE W X
W1: {ANG-PRO.ANGNR | ∃ X (X.PNR = ANG-PRO.PNR)}
RANGE W1 Y
{Angest.NAME | ∃ Y (Y.ANGNR = ANGEST.ANGNR)}
Seite 20
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
Beispiel (Retrieval mit Allquantor)
Es soll die folgende Abfrage durchgeführt werden:
Finde die Namen der Angestellten, die an keinem Projekt mitarbeiten.
Die Umsetzung dieser Abfrage sieht dann folgendermaßen aus:
RANGE ANG-PRO X
{ANGEST.NAME | ∀ X (X . ANGNR ≠ ANGEST.ANGNR)}
Vollständigkeit einer Abfragesprache
Das Relationenkalkül wird als Maßstab für die Beurteilung der
Mächtigkeit relationaler Abfragesprachen angesehen
Erweiterungen
Für die praktische Anwendung sind Sprachmöglichkeiten
wichtig, die im reinen Kalkül und in der einen Algebra nicht
vorhanden sind. Hierzu gehören:
1. Möglichkeiten zur sortierten Ausgabe
2. arithmetische Operationen etwa bei Vergleichen
(A<B+10)
3. Funktionen wie Anzahl der Tupel einer Relation,
Durchschnitt, Summe, Maximalwert, Minimalwert, usw.
Seite 21
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
Update
Neben dem reinen Retrieval muss eine Abfragesprache
natürlich Möglichkeiten zur Veränderung (Update) von
Relationen bieten.
Beispiel zum Update
Es soll die folgende Abfrage betrachtet werden:
Verändere den Beruf des Angestellten 20 zu ‚Übersetzer‘.
Im Sprachvorschlag ALPHA würde diese Aufgabe wie folgt
gelöst:
HOLD X {ANGEST.ANG-NR, ANGEST.BERUF)
ANGEST.ANGNR = 20}
X.BERUF:='ÜBERSETZER'
UPDATE X
Seite 22
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
5.6 Beispiel für relationale
Abfragesprachen
In diesem Kapitel diskutieren wir kurz zwei implementierte
relationale Abfragesprachen:
• SQL (früher SEQUEL)
• QUERY BY EXAMPLE.
5.6.1 Die Abfragesprache SQL
Mit SQL wurde versucht, eine Abfragesprache für den NichtProgrammierer zu entwickeln, die ohne mathematische
Notation, wie z.B. Quantoren, auskommt.Eines der zentralen
Konzepte von SQL ist das der Abbildung (mapping), womit eine
spezielle Art der Selektion gefolgt von einer Projektion gemeint
ist. Die allgemeine Form einer Abbildung ist die folgende:
SELECT A1, ..., An
FROM R
WHERE Prädikat (R).
Seite 23
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
Beispiel (Einfache Abfrage)
Es soll die folgende Abfrage in SQL formuliert werden:
Finde die Namen der Angestellten in Abteilung 10.
Die Umsetzung in der SQL-Abfragesprache lautet dann:
SELECT NAME
FROM ANGEST
WHERE ABT-NR = 10
Zweites Beispiel (Einfache Abfrage)
Es soll die folgende Abfrage in SQL formuliert werden:
Finde die Namen der Angestellten in den Abteilungen 10, 20 und 30.
Die Umsetzung in der SQL-Abfragesprache lautet dann:
SELECT NAME
FROM ANGEST
WHERE ABT-NR IN (10, 20, 30)
Alternativ hierzu könnte man natürlich auch anstatt unter
Verwendung der Mengenschreibweise schreiben:
WHERE ABT-NR = 10 OR ABT-NR = 20 OR ABT-NR = 30
Seite 24
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
Beispiel (Geschachtelte Abfrage)
Es soll die folgende Abfrage in SQL formuliert werden:
Finde die Namen aller Angestellten, die an Projekt 10 arbeiten.
Die Umsetzung in der SQL-Abfragesprache lautet dann:
SELECT NAME
FROM ANGEST
WHERE ANGNR IN
SELECT ANGNR
FROM ANG-PRO
WHERE PNR = 10
Seite 25
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
Beispiel (Vergleichende Abfragen)
Es soll die folgende Abfrage in SQL formuliert werden:
Finde die Namen aller Angestellten, die denselben Beruf haben und in
derselben Abteilung arbeiten wie der Angestellte 127.
Die Umsetzung in der SQL-Abfragesprache lautet dann:
SELECT NAME
FROM ANGEST
WHERE <BERUF, ABT-NR> =
SELECT BERUF, ABT-NR
FROM ANGEST
WHERE ANGNR = 127
Beispiel
Es soll die folgende Abfrage in SQL formuliert werden:
Finde für jedes Projekt den Namen des Projektleiters.
Die Umsetzung in der SQL-Abfragesprache lautet dann:
SELECT PROJEKT.PNR, ANGEST.NAME
FROM PROJEKT, ANGEST
WHERE PROJEKT.P-LEITER = ANGEST.ANGNR
Seite 26
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
Beispiel
Es soll die folgende Abfrage in SQL formuliert werden:
Finde die Namen der Angestellten, die mehr verdienen als ihr
Manager.
Wir nehmen dazu an, dass die Relation ANGEST erweitert ist
um die Attribute MGR-NR (Angestelltennummer des Managers)
und GEHALT des Angestellten. Damit lautet dann die
Umsetzung in der SQL-Abfragesprache:
SELECT NAME
FROM ANGEST AX
WHERE GEHALT >
SELECT GEHALT
FROM ANGEST
WHERE ANGNR = AX.MGR-NR
Seite 27
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
Beispiel
Es soll die folgende Abfrage in SQL formuliert werden:
Finde die Namen der Angestellten, die zu weniger als 50 % an
Projekten mitarbeiten.
Die Umsetzung in der SQL-Abfragesprache lautet dann:
SELECT NAME
FROM ANGEST T
WHERE 50 >
SELECT SUM (PROZ-ARB)
FROM ANG-PRO
WHERE ANGNR = T.ANGNR
Beispiel (Group-By)
Es soll die folgende Abfrage in SQL formuliert werden:
Gib für jede Abteilung deren Nummer und das Durchschnittsgehalt
aller Angestellten dieser Abteilung aus.
Die Umsetzung in der SQL-Abfragesprache lautet dann:
SELECT ABT-NR, AVG(GEHALT)
FROM ANGEST
GROUP BY ABT-NR
Seite 28
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
Beispiel (Update)
In diesem Beispiel wird der folgende SQL-Ausdruck betrachtet:
UPDATE ANGEST
SET GEHALT = GEHALT x 1.1
WHERE ABT-NR = 30
Einbettung von SQL in eine Wirtsprache
Bemerkenswert ist, dass SQL im System R nicht nur als
selbständige Sprache, sondern auch mit PL/I als Wirtssprache
verwendet werden kann. SQL-Ausdrücke können in diesem
Falle Variablen des umgebenden PL/I-Programmes enthalten.
SELECT ...
FROM ...
WHERE ANG-NR = $ Z
Aufbau von Sichten
DEFINE VIEW ZDA 100 (ABT-NR, ANZAHL) AS
SELECT ABT-NR, ANZAHL
FROM ZAHL-DER-ANGEST
WHERE ANZAHL > 100
Seite 29
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
5.6.2 Die Abfragesprache Query-by-Example
Query-by-Example wurde als Sprache entworfen, bei der der
Benutzer seine Anfrage nicht in einer linearen Notation
aufschreibt, sondern im Dialog am Bildschirm erstellt, wobei er
einen speziellen graphischen Editor benutzt. Dieser Editor stellt
dem Benutzer auf Anforderung eine oder mehrere
Tabellenskelette zur Verfügung, in die er dann alle benötigten
Einträge macht.
Für den
Für Attribut- Für Attribut- Für Attribut-
Relationennamen namen
namen
namen
Für Befehle
Für
Für
Für
Beispieltupel Beispieltupel Beispieltupel
Die Anfrage wird nun dadurch spezifiziert, dass ein
Beispieltupel für eine mögliche Antwort in diese Tabelle
eingesetzt wird. Man betrachte dazu etwa die folgende
Anfrage:
Seite 30
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
Finde die Namen aller Angestellten mit Beruf = INGENIEUR in der
Abteilung 30.
ANGEST ANGNR NAME
ANSCHRIFT BERUF
P._MEYER
ABT-NR
INGENEUR 30
In gleicher Weise geht man vor, wenn mehrere Relationen
angesprochen sind, wie es bei der folgenden Anfrage zu sehen
ist.
Finde die Namen aller Mitarbeiter an Projekt 10.
ANGEST ANGNR NAME
_300
...
P._MEYER
ANG-PRO PNR ANGNR
10
_300
Finde für alle Angestellten, die an Projekt 77 mitarbeiten: Name,
Anschrift und Prozent-Arbeitszeit.
Seite 31
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
ANGEST ANGNR NAME
_300
ANG-PRO
ANSCHRIFT ...
_MEYER
_KA
PNR
ANGNR PROZ-ARBEZEIT
77
_300
_10
Da keine der Relationen alle auszugebenden Größen liefern
kann, verlangen wir vom System ein neues Relationenskelett
und tragen dort die auszugebenden Größen folgendermaßen
ein.
P._MEYER
P._KA
P._10
Finde die Angestellten mit einem Gehalt > DM 2000.
Diese Anfrage wird in Query-by-Example folgendermaßen
formuliert:
ANGEST NAME
ANGNR
P._PETER
Seite 32
GEHALT
> 2000
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
Der Operator UN. (unique) wandelt eine durch ALL. erzeugte
Sammlung von Werten in eine Menge um, d.h. er eliminiert
Duplikate.
NAME
WOHNORT
...
P.CNT.ALL._D
ALL. würde alle Wohnorte (einschließlich der Duplikate)
ermitteln, CNT. (COUNT, zähle) würde diese Orte zählen.
NAME WOHNORT
P.CNT.UN.ALL._D
Finde den Angestellten mit dem größten Gehalt.
Zur Formulierung dieser Anfrage in Query-by-Example könnten
wir die Maximumfunktion MAX verwenden, wir können aber
auch den Negationsoperator wie folgt heranziehen.
ANGEST NAME
ANG-NR
P._NAME
GEHALT
_1000
>_1000
Seite 33
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
5.7 Optimierung von Abfragen
Anfrageoptimierung ist ein sehr komplexes Problem - und in
Wirklichkeit optimieren die Systeme nicht, sondern ermitteln
lediglich eine mit großer Wahrscheinlichkeit gute
Ausführungsstrategie. Schon das kleine Beispiel zeigt, welche
Faktoren bei der Optimierung zu berücksichtigen sind:
• Wie kann die Anfrage umformuliert werden, so dass sie
äquivalent zur ursprünglichen Anfrage ist: das System
muss erkennen, dass R[A=B]S[C=10] äquivalent ist zu
(S[C=10])[B=A]R.
• Die Statistik der Datenbank (wieviele Tupel in jeder
Relation, Prozentsatz der Tupel in S mit C=10, wieviele
Tupel mit A=B in R und S, usw.)
• Sind die Tupel einer Relation sortiert?
• Welche Zugriffspfade gibt es (können wir die Selektion
oder den Verbund Sekundärindexe oder Verbindungen
ausnützen)?
Seite 34
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
5.7.1 Algebraische Optimierung
Wir betrachten in diesem Unterkapitel die Relationenalgebra.
Für andere Sprachen gelten die folgenden Ausführungen
sinngemäß
SEL
C = 10
JOIN
A=B
R
S
B=A
JOIN
R
SEL
S
Seite 35
C = 10
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
Einige heuristische Regeln für die algebraische Optimierung
lassen sich folgendermaßen zusammenfassen:
1. Selektionen auf dem gleichen Operanden werden zu
komplexen Selektionen zusammengefasst.
Die folgende Anfrage
(S[C=20][B=A] R)[B=10]
wird einfach umgeformt zu
(S [C=20 ∧ B=10]) [B=A]R
SEL
SEL
JOIN
JOIN
SEL
SEL
Alle Selektionen, die sich auf jeweils eine Relation
beziehen, werden vor dem Join ausgeführt.
Seite 36
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
2. Projektionen, die keine Eliminierung von Duplikaten
erfordern, werden so früh wie möglich, jedoch nicht vor
einer Selektion durchgeführt. Sie werden soweit wie
möglich zu den Blättern des Operatorbaumes
verschoben.
Eine Projektion erfordert dann keine Elimination von
Duplikaten, wenn dabei zumindest ein Schlüssel des
Relationenschemas erhalten bleibt. Ist z.B. gegeben
R(A,B,C,D)
ein Relationenschema mit (A,B) Schlüssel, so kann
R[B,C,D]
identische Tupel enthalten, die dann entfernt werden
müssen. Da das Auffinden identischer Tupel im
allgemeinen aufwendig ist, wird in diesem Falle die
Projektion möglichst spät (d.h. meist auf kleinen
Relationen) durchgeführt.
Seite 37
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
3. Projektionen, die eine Eliminierung von Duplikaten
erfordern, sind also soweit als möglich zur Wurzel des
Operatorbaumes zu verschieben.
Suche gemeinsame Teilbäume des Operatorbaums.
Wenn das Ergebnis des gemeinsamen Teilausdruckes
nicht eine große Relation ist, und wenn diese Relation
vom Sekundärspeicher in sehr viel kürzerer Zeit
gelesen werden kann, als zu ihrer Berechnung
notwendig ist, so lohnt es sich, diese Zwischenrelation
nur einmal zu berechnen und abzuspeichern. (Hier
kommt es also nicht auf eine Umstrukturierung der
Anfrage an, sondern auf das Erkennen gleicher
Teilbäume).
5.7.2 Optimierung auf der physischen Ebene
Neben der Umformung algebraischer Ausdrücke ist natürlich
zur Ermittlung einer günstigen Abfragestrategie die physische
Organisation der Daten zu berücksichtigen.
Seite 38
Prof. Dr. M.-R. Wolff
BWL / Wirtschaftsinformatik
5.7.3 Ausnutzung von Sekundärindexen
Existiert ein Index für B, so ist für jedes Tupel in R sofort die
zugehörige Tupelmenge in S feststellbar - und damit die
Tupelmenge des Verbundes konstruierbar. Existiert kein Index
für A oder B, so lohnt es sich im allgemeinen, einen solchen
Index für die gerade auszuführende Verbundoperation
anzulegen.
Seite 39