4. Relationen-Algebra Basisoperationen des relationalen Modells

4. Relationen-Algebra
Basisoperationen des relationalen Modells
Kriterien für Anfragesprachen
Operationen der Relationen-Algebra
Beispiel komplexer Algebra-Ausdrücke
Übersicht zu Äquivalenz-Umformungen
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 122
4.1 Basisoperationen des relationalen Modells
Eigenschaften von Sprachen für relationales Modell
- Mengenorientiert und deskriptiv
- Anfrage-Spezifikation von WAS statt WIE
- Verarbeitung von Tupelmengen mit einer Anweisung (lesen, ändern, ...)
Basisoperationen
- Selektion: Auswahl von Tupeln einer Relation mit gewissen
Eigenschaften (Zeilenauswahl)
- Projektion: Auswahl von Attributen einer Relation (Spaltenauswahl)
- Verbund: Verknüpfung von Tabellen aufgrund von AttributwertBeziehungen, Konkatenation von Tupeln aus mehreren Relationen
(Zeilenverkettung)
- Mengenoperationen:
• Vereinigung, Durchschnitt, Differenz (Operationen der Mengenlehre)
• Ausführung auf verschiedenen Relationen aber nur mit gleicher
Struktur möglich (Homogenität)
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 123
4.2 Kriterien für Anfragesprachen
Quelle: Heuer/Saake, Datenbanken – Konzepte und Sprachen
Ad-hoc-Formulierung:
- Anfrage durch den Benutzer jederzeit möglich
- Programmerstellung nicht notwendig
Mengenorientiertheit:
- Jede Operation soll auf Mengen von Daten gleichzeitig arbeiten
- Nicht navigierend auf einzelnen Elementen ("one tuple at a time")
Deskriptivität:
- Benutzer-Anfrage der Form "Was will ich haben?" und nicht "Wie komme
ich an das, was ich haben will?"
- Hinweis: Formulierung des WAS erfolgt auch mit Basisoperationen in
einzelnen Schritten, allerdings nicht prozedural/navigierend
Abgeschlossenheit:
- Ergebnis einer Anfrage ist wieder eine Relation
- Verwendung als Eingabe für nächste Anfrage
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 124
4.2 Kriterien für Anfragesprachen
Adäquatheit:
- Unterstützung aller Konstrukte des zugrundeliegenden Modells
Orthogonalität:
- Sprachkonstrukte sind weitgehend frei kombinierbar
- Voraussetzung ist Abgeschlossenheit
- Erleichtert Umgang/Implementierung der Sprache (weniger Sonderfälle)
Optimierbarkeit:
- Sprache besteht aus wenigen Operationen mit Optimierungsregeln
- Semantisch äquivalente Umformung von Sprachausdrücken hinsichtlich
Minimierung der Ausführungskosten
- Wesentliche Unterstützung durch
• Formale Sprachdefinition (Umformungsvorschriften, Beweisbarkeit)
• Orthogonalität (Umformung erzeugt gültigen Ausdruck)
Effizienz:
- Operationen sind effizient ausführbar, d.h. maximal in O(n2) für n Tupel
(kein exponentielles Wachstum)
- Ausführungskosten möglichst auf O(n) oder O(n*logn) reduzieren
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 125
4.2 Kriterien für Anfragesprachen
Sicherheit:
- Keine Endlosschleifen für syntaktisch korrekte Anfragen
- Kein unendliches Ergebnis für syntaktisch korrekte Anfragen
Eingeschränktheit:
- Anfragesprache darf keine komplette Programmiersprache sein
- Folgt aus Effizienz, Optimierbarkeit und Sicherheit (z.B. lassen sich sehr
wohl Endlosschleifen in Programmiersprachen implementieren)
Vollständigkeit:
- Anfragesprache muss mindestens die Anfragen einer "Standardsprache"
(z.B. Relationen-Algebra) ausdrücken können
Relationen-Algebra und -kalkül erfüllen obige Kriterien
Praxisrelevante Anfragesprachen (SQL) erfüllen Kritierien weitgehend
- Teilweise Schwächen aufgrund Historie (z.B. Orthogonalität)
- Teilweise bewusste Erweiterung (z.B. Eingeschränktheit)
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 126
4.3 Operationen der Relationen-Algebra
Begriff der Algebra in der Mathematik:
- Nichtleere Menge von Werten
- Operationen, die auf dem Wertebereich definiert sind
- Bsp: Menge der natürlichen Zahlen mit Operationen +, -, *, :
Übertragung auf relationales Datenmodell:
- Werte = Relationen
- Operationen = Basisoperationen (siehe Folie 123)
- Anfrageergebnisse werden durch Hintereinanderausführung
(Schachtelung) von Basisoperationen bestimmt
Abgeschlossenheit als wichtige Eigenschaft:
- Operationsdurchführung erzeugen stets wieder Relationen, d.h. nur ein
Wertebereich (einsortige Algebra)
- Bsp: Operation ZÄHLE
• gibt für eine Relation die Zahl ihrer Tupel als natürliche Zahl zurück
• würde gegen Abgeschlossenheit verstoßen (mehrsortige Algebra)
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 127
4.3 Operationen der Relationen-Algebra
Erläuterung der Operationen an folgendem Beispiel:
- Relation BUCH (InvNr, Titel, ISBN, Autor)
- Relation AUSLEIHE (InvNr, Name)
- InvNr ist Primär- und Fremdschlüssel in AUSLEIHE bezüglich
Primärschlüssel InvNr in BUCH
BUCH
InvNr
0007
1201
4711
4712
4717
AUSLEIHE InvNr
4711
1201
0007
4712
Datenbanksysteme
Titel
Dr. No
Objektbanken
Datenbanken
Datenbanken
Pascal
ISBN
3-125
3-111
3-765
3-891
3-999
Autor
James B.
Heuer
Vossen
Ullman
Wirth
Name
Meyer
Schulz
Müller
Meyer
Friedrich-Schiller-Universität Jena
Seite 128
4.3.1 Projektion
Definition: PJAttr R bzw. (πAttr R)
- Attr steht für eine nichtleere Menge von Attributen der Relation R
- PJAttr R wählt jene Spalten von R aus, die in Attr angegeben sind, d.h. es
wird auf jene Spalten projiziert
Beispiele:
- PJ{Name} Ausleihe
• "Teure" Projektion
• Duplikate werden eleminiert wegen
Mengeneigenschaft, Abgeschlossenheit
- PJ{InvNr, ISBN} Buch
• "Billige Projektion"
• Keine Duplikate eleminiert
• Duplikatelemination nie nötig,
wenn Attr einen Schlüssel enthält
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Name
Meyer
Schulz
Müller
InvNr
0007
1201
4711
4712
4717
ISBN
3-125
3-111
3-765
3-891
3-999
Seite 129
4.3.2 Selektion
Definition: SLF R bzw. (δF R)
- F steht für eine Selektionsformel (Bedingung, Prädikat)
- SLF R wählt jene Zeilen von R aus, die der Bedingung F genügen
Bemerkungen zur Selektionsformel:
- Unterscheidung verschiedener Arten von Selektionsformeln
• Konstantenselektion: Vergleich von Attribut(wert) und Konstante
• Attributselektion: Vergleich von Attribut(wert) und Attribut(wert)
• Logische Verknüpfungen von Konstanten- und/oder Attributselektion
- Wertebereich der im Vergleich stehenden Attribute/Konstanten müssen
gleich oder kompatibel sein
- Auswertung jeweils durch "lokale" Betrachtung eines Tupels, auf diesem
Wege keine Möglichkeit für "tupelübergreifende" Vergleiche
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 130
4.3.2 Selektion
Beispiele:
- SLInvNr>3333 Buch
• Konstantenselektion
• =,≠,<,≤,≥ ebenso verwendbar
InvNr
Titel
4711 Datenbanken
4712 Datenbanken
4717 Pascal
ISBN
Autor
3-765 Vossen
3-891 Ullman
3-999 Wirth
- SLTitel=Autor Buch
• Attributselektion
• ≠,<,>,≤,≥ ebenso verwendbar
InvNr
ISBN
- SL(InvNr>3333)and(Autor≠“Wirth“) Buch
• Logische Verknüpfung
• or,¬ ebenso verwendbar
InvNr
Titel
ISBN
Autor
4711 Datenbanken 3-765 Vossen
4712 Datenbanken 3-891 Ullman
Datenbanksysteme
Titel
Autor
leeres Anfrageergebnis
Friedrich-Schiller-Universität Jena
Seite 131
4.3.3 Verbund (JOIN)
Definition: R JNF S
- F steht für eine Verbundbedingung
- R JNF S "konkateniert" die Tupel der Relationen R und S, die die
Verbundbedingung erfüllen (Kartesisches Produkt + Selektion)
Bemerkungen zur Verbundbedingung
- F ist Bedingung/Prädikat analog zur Selektion
- Equi-Join: Verwendung der Gleichheits-Bedingung "=" (wichtigster Fall!)
- Natural Join: Equi-Join auf gleichnamigen Attributen in R und S
- Inner Join:
• Tupel ohne "Gegenstück" (Verbundbedingung = false) sind im JoinErgebnis nicht enthalten
• Ausblick: Outer Join (Left, Right, Full) übernimmt auch Tupel mit
nichterfüllter Verbundbedingung ins Join-Ergebnis, fehlende
Attributwerte bekommen den Wert NULL
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 132
4.3.3 Verbund (JOIN)
Beispiel:
- Buch JNInvNr=InvNr Ausleihe
• Equi-Join und sogar Natural Join (≠,<,>,≤,≥ ebenso verwendbar)
• Verkettung jener Tupel aus den Relationen Buch und Ausleihe, bei
denen Werteübereinstimmung bei InvNr vorliegt (Attribut InvNr nur
einmal im Ergebnisrelations-Schema)
• Pascal-Buch von Hr. Wirth (InvNr=4717) nicht im Ergebnis enthalten
InvNr
0007
1201
4711
4712
Titel
Dr. No
Objektbanken
Datenbanken
Datenbanken
Mögliche JN-Ausführungslogik:
- Algorithmus "nested loop"
- Aufwand: n*m mit
• n=card(Buch)
• m=card(Ausleihe)
Datenbanksysteme
ISBN
3-125
3-111
3-765
3-891
Autor
James B.
Heuer
Vossen
Ullman
Name
Müller
Schulz
Meyer
Meyer
for each Tupel x in Buch do {
for each Tupel y in Ausleihe do {
if x.InvNr = y.InvNr then {
Erzeuge Ergebnistupel durch
"Verkettung" von x und y
}
}
}
Friedrich-Schiller-Universität Jena
Seite 133
4.3.4 Vereinigung (UNION)
Definition: R UN S bzw. (R ∪ S)
- Vereinigung ist bei gleichen Relationsschemata von R und S möglich,
d.h. Attributnamen und Wertebereiche stimmen überein
- Vereinigung bei unterschiedlichen Relationsschemata aber gleicher
Attributanzahl (Spaltenzahl) in R und S und komaptiblen Wertebereichen
durch Attribut-Umbenennung möglich
- Vereinigung kann Duplikatelimination erfordern
• Wegen Abgeschlossenheit / Mengeneigenschaft
• Erledigt der UNION-Operator selbst
Beispiel:
- (SLInvNr>3333 Buch) UN (SLTitel=“∗banken“ Buch)
InvNr
1201
4711
4712
4717
Datenbanksysteme
Titel
Objektbanken
Datenbanken
Datenbanken
Pascal
ISBN
3-111
3-765
3-891
3-999
Autor
Heuer
Vossen
Ullman
Wirth
InvNr > 3333
Friedrich-Schiller-Universität Jena
Titel = "*banken"
Seite 134
4.3.5 Durchschnitt (INTERSECTION)
Definition: R IN S bzw. (R ∩ S)
- Durchschnitt ist bei gleichen Relationsschemata von R und S möglich,
d.h. Attributnamen und Wertebereiche stimmen überein
- Durchschnitt bei unterschiedlichen Relationsschemata aber gleicher
Attributanzahl (Spaltenzahl) in R und S und komaptiblen Wertebereichen
durch Attribut-Umbenennung möglich
- Durchschnitt kann Duplikatelimination erfordern
• Wegen Abgeschlossenheit / Mengeneigenschaft
• Erledigt der INTERSECTION-Operator selbst
Beispiel:
- (SLInvNr>3333 Buch) IN (SLTitel=“∗banken“ Buch)
InvNr
1201
4711
4712
4717
Datenbanksysteme
Titel
Objektbanken
Datenbanken
Datenbanken
Pascal
ISBN
3-111
3-765
3-891
3-999
Autor
Heuer
Vossen
Ullman
Wirth
InvNr > 3333
Friedrich-Schiller-Universität Jena
Titel = "*banken"
Seite 135
4.3.6 Differenz
Definition: R DF S bzw. (R \ S)
- Differenz der Relationen R und S, d.h. in der Ergebnismenge der DFOperation sind alle Tupel von R enthalten, die nicht auch in S existieren
- Differenz ist bei gleichen Relationsschemata von R und S möglich, d.h.
Attributnamen und Wertebereiche stimmen überein
- Differenz bei unterschiedlichen Relationsschemata aber gleicher
Attributanzahl (Spaltenzahl) in R und S und komaptiblen Wertebereichen
durch Attribut-Umbenennung möglich
- Differenz erfordert keine Duplikatelimination
Beispiel:
- (SLInvNr>3333 Buch) DF (SLTitel=“∗banken“ Buch)
InvNr
1201
4711
4712
4717
Datenbanksysteme
Titel
Objektbanken
Datenbanken
Datenbanken
Pascal
ISBN
3-111
3-765
3-891
3-999
Autor
Heuer
Vossen
Ullman
Wirth
InvNr > 3333
Friedrich-Schiller-Universität Jena
Titel = "*banken"
Seite 136
4.3.7 Bemerkungen zu den Basisoperationen
Grundsätzlich gibt es weitere Basisoperationen:
- Bsp: Kartesisches Produkt (CROSS JOIN in SQL)
- Dadurch jedoch keine zusätzliche Ausdrucksmöglichkeit gegenüber den
bisher genannten Basisoperationen (beweisbar)
- Keine weiteren eigenständigen Basisoperationen notwendig
Für die Basisoperationen PJ, SL, JN, UN, DF und RE ("rename",
Umbenennung) kann gezeigt werden, dass diese eine minimale
Relationenalgebra bilden, d.h.
- Weglassen einer Operation reduziert die Mächtigkeit
- Hinzufügen einer Operation bringt keine Steigerung der Mächtigkeit
- Darstellung aller weiteren Operationen mit Hilfe der Basisoperationen
=
(R UN S) DF ((R DF S) UN (S DF R))
• Bsp: R IN S
R
S
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 137
4.3.7 Bemerkungen zu den Basisoperationen
Oftmals existieren verschiedene Möglichkeiten zur Formulierung einer
Datenbankanfrage, abhängig von
- Verwendungs-Reihenfolge der Basisoperationen
- Häufigkeit des Auftretens einer Basisoperation
Ausführungskosten (Anfragebearbeitung durch das DBVS) der einzelnen
Möglichkeiten können stark variieren
- Bsp: Selektion vor Join erscheint günstiger als umgekehrt
- Anwender sollte dennoch die Anfrage so formulieren, dass sie leicht
verständlich ist ("reine Lehre")
- Aufgabe des relationalen DBVS, eine Anfrage intern hinsichtlich minimaler
Ausführungskosten zu transformieren (Optimizer)
• Berechnung der zu erwartenden Ausführungskosten für die
verschiedenen Lösungsvarianten (basierend auf Schätzwerten und
Heuristiken)
• Auswahl der kostenminimalen Lösungsvariante
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 138
4.4 Beispiel komplexer Algebra-Ausdrücke
Relationen:
- Angest (AngNr, Name, Wohnort, Beruf, AbtNr)
- Projekt (ProNr, PName, PBeschr, PLeiter)
- Mitarbeit (ProNr, AngNr, Prozent)
• ProNr ist Fremdschlüssel auf Projekt.ProNr
• AngNr ist Fremdschlüssel auf Angest.AngNr
Tabelleninhalte:
Angest AngNr
112
205
117
198
...
Name
Müller
Winter
Rüllich
Schumann
...
Wohnort
Erfurt
Zwickau
Weimar
Jena
...
Projekt ProNr PName PBeschr
27
xyz...
Pkw2000
16
xyz...
Wankel99
84
xyz...
Trabbxx
...
...
...
Datenbanksysteme
Beruf
Ingenieur
Programmierer
Hundezüchter
Kaufmann
...
PLeiter
205
117
117
...
AbtNr
3
3
5
4
...
Mitarbeit ProNr AngNr
27
112
27
198
16
198
...
...
Friedrich-Schiller-Universität Jena
Prozent
100
70
30
...
Seite 139
4.4 Beispiel komplexer Algebra-Ausdrücke
Anfrage: "Finde die Namen aller Angestellten, die am Projekt 27 mitarbeiten"
- Variante 1: PJ{Name} (Angest JNAngNr=AngNr (SLProNr=27 Mitarbeit))
- Variante 2: PJ{Name} (Angest JNAngNr=AngNr (PJ{AngNr} (SLProNr=27 Mitarbeit)))
- Variante 3: PJ{Name} (SLProNr=27 (Angest JNAngNr=AngNr Mitarbeit))
Aufwandsbetrachtungen
- Annahme: Ausführung erfolgt genau so wie hingeschrieben
- Welche Variante lässt die geringsten Ausführungskosten erwarten?
• Vermutlich Variante 2, weil die Projektion auf Mitarbeit.AngNr vor dem
JOIN unnötigen "Ballast" abwirft und billig ist
• ABER: Abhängig von Relationsgrößen, Selektivität der
Selektionsformeln, Verbundbedingungen, Tupellängen usw.
• DBVS kann Variante 1 und 3 in Variante 2 umbauen (Optimizer)
- Frage: was ist mit folgender Variante?
• SLPRONR=27 (PJ{Name} (Angest JNANGNR=ANGNR Mitarbeit))
• Syntaktisch nicht möglich (Selektionskriterium passt nicht auf das
Projektionsergebnis!)
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 140
4.5 Übersicht zu Äquivalenz-Umformungen
SLF1 (SLF2 R) ≡ SLF2 (SLF1 R)
- Selektionen dürfen in ihrer Reihenfolge vertauscht werden
- Relevant für kostenbasierte Anfrageoptimierung auf Daten-Statistiken
- Bsp:
• F1 hat hohe Selektivität ("wirft 90% der Tupel raus")
• F2 hat niedrige Selektivität ("wirft 10% der Tupel raus")
• Anfangs 1000 Tupel in R vorhanden
• F1 und F2 seien zudem unabhängig voneinander
• SLF1 (SLF2 R): 1000 SLF2 900 SLF1 90 (∑ 1900 Tupel gelesen)
• SLF2 (SLF1 R): 1000 SLF1 100 SLF2 90 (∑ 1100 Tupel gelesen)
SLF R ≡ SLF1 (SLF2 R) mit F = F1 ∧ F2
- Mehrere "einfache" hintereinander ausgeführte Selektionen dürfen durch
eine "komplexe" Selektion ersetzt werden
- Bsp: Annahmen wie oben
• SLF2∧ F1 R: 1000 SLF1 ∧ F2 90 (∑ 1000 Tupel gelesen)
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 141
4.5 Übersicht zu Äquivalenz-Umformungen
SLF (PJAttr R) ≡ PJAttr (SLF R) wenn Attribute (F) ⊆ Attr
- Selektion und Projektion dürfen in ihrer Reihenfolge vertauscht werden,
wenn die in F angesprochenen Attribute in der Projektion enthalten sind
- Bsp: "Finde die Namen und AngNr aller Angestellten mit AngNr>1111"
• SLANGNR>1111 (PJ{Name,ANGNR} Angest) ≡ PJ{Name,ANGNR} (SLANGNR>1111 Angest)
R UN S ≡ S UN R
- Kommutativität der Mengenvereinigung, gilt auch für den Durchschnitt
(R UN S) UN T ≡ R UN (S UN T)
- Assoziativität der Mengenvereinigung, gilt auch für den Durchschnitt
R JNF S ≡ S JNF' R
- Reihenfolge der Attribute im Ergebnis-Tupel ohne Bedeutung, damit auch
bedeutungslos, wer bei "Konkatenation" linker und rechter Operand ist
- Verbundbedingung muss dann umgedreht werden F ↔ F'
- Bsp: R JNA1=A4 S ≡ S JNA4=A1 R
• A1 ist Attribut in R, A4 ist Attribut in S
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 142