Crashkurs: Relationale Datenbanksysteme

Crashkurs:
Relationale
Datenbanksysteme
13.10.2004
Felix Naumann
Überblick – Vormittag
z
Motivation
z
z
Warum sind RDBMS und XML Systeme für die
Informationsintegration wichtig?
RDBMS – Relational Database Management
System
z
z
z
z
Relationale Datenbanken
Relationales Modell (Datenmodell)
Relationale Algebra (Datenanfrage)
SQL (Anfragesprache)
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
2
1
Überblick – Nachmittag
z
RDBMS
z
z
z
Anfragebearbeitung
Anfrageoptimierung
XML
z
z
Grundkonzepte
XML Schema
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
3
Quellen
z
RDBMS Folien u.a. in Anlehnung an
z
z
VL “Data Warehousing”, Prof. Leser
VL “Einführung in Datenbanken”, Prof. Freytag
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
4
2
Was ist
Informationsintegration?
z
Informationsintegration ist die Zusammenführung
von Daten und Inhalt verschiedener Quellen zu
einer einheitlichen Informationsmenge.
z
Informationsintegration ist die korrekte, vollständige
und effiziente Zusammenführung von Daten und
Inhalt verschiedener, heterogener Quellen zu einer
einheitlichen und strukturierten Informationsmenge
zur effektiven Interpretation durch Nutzer und
Anwendungen.
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
5
Integrierte
Informationssysteme
Anfrage
Integriertes Informationssystem
Oracle,
DB2…
13.10.2004
Dateisystem
Web
Service
Anwendung
Felix Naumann, CK Informationssysteme, WS 04/05
HTML
Form
Integriertes
Info.-system
6
3
Motivation – RDBMS
Am weitesten verbreitetes
Datenbankmodell
Oracle,
DB2…
Oft selbst ein
RDBMS
Gut verstanden,
weitgehend erforscht
Anfrage
Integriertes Informationssystem
Dateisystem
13.10.2004
Web
Service
Anwendung
HTML
Form
Integriertes
Info.-system
Felix Naumann, CK Informationssysteme, WS 04/05
7
Motivation – XML
Anfrage
Integriertes Informationssystem
Oracle,
DB2…
Dateisystem
Transformation
nach XML
13.10.2004
Web
Service
HTML
Form
Anwendung
Input/Output
XML
Gute XML
Werkzeuge
(SAX/DOM)
Felix Naumann, CK Informationssysteme, WS 04/05
Integriertes
Info.-system
Einfache
Kommunikation,
Interoperabilität
8
4
Was sind Daten?
z
Digitale Repräsentation von
z
z
z
z
z
z
Dingen
Entitäten
Wissen
Information
in der wirklichen Welt.
Kernfragen:
z
z
z
z
Welche Daten speichere ich?
Wie speichere ich die Daten?
Wie frage ich Daten ab?
Wie erledige ich all dies effizient?
13.10.2004
Unterstützung durch
Datenbanksystem
Felix Naumann, CK Informationssysteme, WS 04/05
9
Datenbanksysteme
z
Bestandteile
z
Datenbank
z
z
z
Die Daten selbst
+ Metadaten (Beschreibung der Daten)
Datenbankmanagementsystem (DBMS)
z
z
Softwarekomponente zum Zugriff auf eine oder
mehrere Datenbanken.
Server-basiert
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
10
5
DBMS Beispiele
z
DBMS
z
z
z
z
z
z
z
Oracle, DB2
Informix, Sybase
NCR Terada, SQLServer
PostGreSQL, Interbase
mySQL, ...
Data Warehouses
z
z
Alle großen
Softwaresysteme
nutzen ein, mehrere
oder sehr viele DBMS
z
z
z
z
OLTP
OLAP
13.10.2004
SAP
Siebel
SRS
Sogar das geplante
Microsoft Windows FileSystem (Longhorn)
Felix Naumann, CK Informationssysteme, WS 04/05
11
DBMS Aufgaben
z
z
z
Unterstützung des Datenmodells
Bereitstellung einer Anfragesprache (DDL & DML)
Robustheit
z
z
z
z
Speicherverwaltung
Transaktionsmanagement
z
z
z
Wahrung der Datenintegrität (Konsistenz etc.)
Abfangen von Systemfehlern
Auch im Mehr-Benutzer-Betrieb
Effiziente Anfragebearbeitung
Userverwaltung & Zugangskontrolle
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
12
6
Schichtenmodell
(ANSI/X3/SPARC)
Externes
Schema
13.10.2004
Externes
Schema
Externes
Schema
Sichten (Anwendungen,
Nutzer)
Konzeptionelles
Schema
Logisches Modell (Tabellen,
Attribute, Anfragen
Internes
Schema
Physisches Modell
(Indexierung, Speicherung)
Felix Naumann, CK Informationssysteme, WS 04/05
13
Schichtenmodell
z
Interne (physische) Sicht
z
z
z
Konzeptionelle (logische) Sicht
z
z
z
z
Speichermedium (Tape, Festplatte)
Speicherort (Zylinder, Block)
Unabhängig von physischer Sicht
Definiert durch Datenmodell
Stabiler Bezugspunkt für interne und externe
Sichten
Externe (logische) Sicht
z
z
z
Anwendungsprogramme
Nur auf die relevanten Daten
Enthält Aggregationen und Transformationen
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
14
7
Schichtenmodell – Objekte
z
Interne (physische) Sicht
z
z
Dateien
Konzeptionelle (logische) Sicht
z
Schema
z
z
Relationen, Typen,
Integritätsbedingungen
Externe (logische) Sicht
z
„Views“
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
15
Schichtenmodell – Beispiel
(Vorschau)
z
Konzeptionelle (logische) Sicht
z
z
Externe (logische) Sicht
z
z
CREATE TABLE Book(
ISBN VARCHAR(100)
Title VARCHAR(100)
Price DECIMAL(6,2))
CREATE VIEW cheapBooks AS (
SELECT * FROM Book
WHERE Price < 10,00 )
Interne (physische) Sicht
z
CREATE INDEX BookIndex ON Book (ISBN)
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
16
8
Datenbankentwurf
Konzeptioneller Entwurf
1.
z
z
z
ER-Modell & ER-Diagramm
Relationenmodell
...
Physischer Entwurf
2.
z
HW und SW abhängig
3.
Konzeptioneller Entwurf der externen
Anwendungen
z
Konzeptioneller Entwurf ist unabhängig vom
physischen Entwurf!
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
17
Überblick
z
RDBMS – Relational Database Management
System
z Relationale Datenbanken
z Relationales Modell (Datenmodell)
z Relationale Algebra (Datenanfrage)
z SQL (Anfragesprache)
z Anfragebearbeitung
z Optimierung
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
18
9
Relationales Modell
z
Auch
z
z
z
Relationenmodell
Relationales Datenmodell
Ziele
z
z
Daten-Unabhängigkeit
Trennung vom hierarchischen und NetzwerkModellen
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
19
Relationales Modell –
Definitionen
z
Domäne
z
z
z
z
Integer, Real, String
{rot, grün, blau}
Relation (Tabelle)
z
z
z
Seien D1,..,Dn Domänen
Relation R ist Teilmenge
aus D1×D2 ×... ×Dn
...vom Grad n
13.10.2004
Tupel (Zeile)
z
z
z
Attribut (Spalte)
Relationenschema
z
z
Ein Element aus R
R(A1:D1, ..., An:Dn)
Kardinalität
z
|R| ist die (aktuelle)
Anzahl der Tupel in R
Felix Naumann, CK Informationssysteme, WS 04/05
20
10
Relationales Datenmodell –
Extension
z
Extension einer Relation
z
z
z
z
Die Menge der momentanen Tupel
Kann sich durch Operationen ändern
Kann als Tabelle dargestellt werden
Beispiel
z
z
Domänen:
D1 = Integer, D2 = String, D3 = String, D4 = Integer
Relationenschema:
Mitarbeiter(P_ID: D1, Vorname: D2, Nachname: D3, Alter:
D4 )
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
21
Relationales Datenmodell
Spalten/Attribute
Mitarbeiter
P_ID
Zeilen/
Tupel
Nachname
Alter
1
Peter
Müller
32
2
Stefanie
Meier
34
5
Petra
Weger
28
Andreas
Zwickel
44
7
...
13.10.2004
Vorname
...
...
Felix Naumann, CK Informationssysteme, WS 04/05
...
22
11
Relationales Datenmodell vs.
XML
z
Relationen sind Mengen
z
z
Keine Duplikate
z
z
Tupel sind ungeordnet
z
z
Attributwerte sind Atomar
z
13.10.2004
XML ist Graphbasiert
Duplikate erlaubt (bzw.
nicht definiert)
In XML Dokumenten ist
Reihenfolge wichtig
Elemente sind
geschachtelt
Felix Naumann, CK Informationssysteme, WS 04/05
23
Relationales Modell –
Definitionen
z
Datenbankschema
z
z
Menge von Relationenschemata
Relationale Datenbank
z
Datenbankschema und alle zu dem Schema
gehörenden Extensionen
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
24
12
Relationales Modell –
Schlüssel
z
z
Sei R(A1:D1, ..., An:Dn) ein Relationenschema
und sei S ⊆ {A1, ..., An}
S ist Schlüssel, falls
z
z
Eindeutigkeit:
Falls zwei Tupel such unterscheiden,
unterscheiden sie auch in den Attributen des
Schlüssels.
Minimalität:
Es gibt kein S‘⊆ S, welches Eindeutigkeit erfüllt.
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
25
Relationales Modell –
Schlüssel
P_ID
Vorname
Nachname
Alter
1
Peter
Müller
32
2
Stefanie
Meier
34
5
Petra
Weger
28
7
Andreas
Müller
44
11
Peter
Schmidt
32
Schlüssel: {PID}, {Vorname, Nachname}
Nicht Schlüssel (z.B.):
13.10.2004
{Vorname}: Nicht eindeutig
{Vorname, Alter}: Nicht eindeutig
{PID, Vorname}: Nicht minimal
{Vorname, Nachname, Alter}: Nicht minimal
Felix Naumann, CK Informationssysteme, WS 04/05
26
13
Relationales Modell –
Schlüssel
z
Primärschlüssel
z
z
Ein „ausgezeichneter“ Schlüssel
Schlüssel als Meta-Information
z
z
Kann nicht aus Extension abgelesen werden
Wichtige Entwurfsentscheidung
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
27
Relationales Modell –
Fremdschlüssel
z
z
Seien R(A1, ..., An) und S(B1, ..., Bm) zwei
Relationenschemata.
Sei F ein Schlüssel für S
z
z
Also F = {Bd1, ..., Bdl} ⊆ {B1, ..., Bm}
Falls zusätzlich F ⊆ {A1, ..., An}, heißt F
Fremdschlüssel von S in R.
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
28
14
Relationales Modell –
Fremdschlüssel
mitarbeiter
p_id
vorname
nachname
alter
1
Peter
Müller
32
2
Stefanie
Meier
34
5
Petra
Weger
28
7
Andreas
Zwickel
44
...
...
...
...
projekte
proj_id
p_id
name
1
DWH
BMW
2
1
eCommerce
Metro
5
7
SAP
RAG
13.10.2004
...
...
proj_id ist
Schlüssel
kunde
1
...
p_id ist
Schlüssel
...
p_id ist
Fremdschlüssel
Felix Naumann, CK Informationssysteme, WS 04/05
29
Relationales Modell –
Integritätsbedingungen
z
z
z
z
z
Jede Relation hat mindestens einen
Schlüssel.
Attribute im Schlüssel dürfen nicht NULL
sein.
Attributwerte in Domäne
Zu jedem Fremdschlüsselwert muss es einen
entsprechenden Schlüsselwert geben.
Zusicherungen (Assertions)
z
Das Alter von Angestellten ist immer >15.
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
30
15
Relationales Modell –
Schemata
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
31
Relationales Modell –
Schemata
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
32
16
Pause
Überblick
z
RDBMS – Relational Database Management
System
z Relationale Datenbanken
z Relationales Modell (Datenmodell)
z Relationale Algebra (Datenanfrage)
z SQL (Anfragesprache)
z Anfragebearbeitung
z Optimierung
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
34
17
Relationale Algebra
z
z
Anfragesprache
5 Grundoperationen
z
z
z
z
z
Vereinigung
Differenz
Kartesisches Produkt
Projektion
Selektion
z
Abgeleitete
Operationen
z
z
z
Join, Natural Join,
Semi-Join
Division
Schnittmenge
Operationen bilden von Relation zu Relation ab (Abgeschlossenheit).
Operationen können zu Ausdrücken zusammengesetzt werden.
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
35
Relationale Algebra –
Vereinigung
z
z
R∪S
Attribute von R und S
z
z
z
Gleiche Anzahl
Gleiche Domänen
Ergebnis
z
z
z
z
Mengentheoretische Vereinigung
gleiche Struktur wie R bzw. S
Duplikat-frei
|R ∪ S| ≤ |R|+|S|
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
36
18
Relationale Algebra –
Vereinigung
Mitarbeiter m
p_id
1
2
vorname
Peter
nachname
Müller
Stefanie Meier
alter
m∪e
32
p_id
vorname
nachname
alter
34
1
Peter
Müller
32
2
Stefanie Meier
34
5
Petra
Weger
28
7
Andreas
Zwickel
44
Employees e
p_id vorname nachname
5
Petra
7
Andreas Zwickel
13.10.2004
Weger
alter
28
44
Felix Naumann, CK Informationssysteme, WS 04/05
37
Relationale Algebra
Vereinigung
z
Jetzt: Kleine Vorschau, warum
Informationsintegration so schwierig ist
z
z
z
z
Anderes Schema
Null Werte
Gleiche IDs
Gleiche real-world Objekte
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
38
19
Relationale Algebra –
Vereinigung
Mitarbeiter m
p_id
vorname
nachname
alter
1
Peter
Müller
32
2
Stefanie Meier
34
Anderes Schema:
Employees e
p_id vorname nachname
5
Petra
7
Andreas Zwickel
13.10.2004
Weger
m ∪ e nicht
definiert!
age
28
44
Felix Naumann, CK Informationssysteme, WS 04/05
39
Relationale Algebra –
Vereinigung
Mitarbeiter m
p_id
vorname
nachname
alter
1
Peter
Müller
32
7
Andreas
Zwickel
34
5
7
Petra
Weger
Andreas Zwickel
13.10.2004
m∪e
p_id
vorname
nachname
alter
1
Peter
Müller
32
7
Andreas
Zwickel
34
28
5
Petra
Weger
28
NULL
7
Andreas
Zwickel
NULL
Employees e
p_id vorname nachname
Null-Werte
alter
Felix Naumann, CK Informationssysteme, WS 04/05
40
20
Relationale Algebra –
Vereinigung
Mitarbeiter m
p_id
vorname
nachname
alter
1
Peter
Müller
32
7
Stefanie Meier
34
Employees e
p_id vorname nachname
5
Petra
7
Andreas Zwickel
13.10.2004
Weger
age
28
44
IDS: Schlüsseleigenschaft
gilt nicht mehr!
m∪e
p_i
vorname
nachname
alter
1d
Peter
Müller
32
7
Stefani
e
Petra
Meier
34
5
Weger
28
7
Andreas
Zwickel
44
Felix Naumann, CK Informationssysteme, WS 04/05
41
Relationale Algebra –
Vereinigung
m∪e
Mitarbeiter m
p_id
vorname
nachname
alter
1
Peter
Müller
32
8
Andreas
Zwickel
44
Employees e
p_id vorname nachname
5
Petra
7
Andreas Zwickel
13.10.2004
Weger
age
28
44
p_i
d
vorname
nachname
alter
1
Peter
Müller
32
8
Andreas
Zwickel
44
5
Petra
Weger
28
7
Andreas
Zwickel
44
IDS: Schlüsseleigenschaft
gilt zwar, aber Duplikate!
Felix Naumann, CK Informationssysteme, WS 04/05
42
21
Relationale Algebra – Differenz
z
z
R/S
Attribute von R und S
z
z
z
Gleiche Anzahl
Gleiche Domänen
Ergebnis
z
z
z
Mengentheoretische Differenz
gleiche Struktur wie R bzw. S
|R| - |S| ≤ |R / S| ≤ |R|
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
43
Relationale Algebra – Differenz
Mitarbeiter m
p_id
vorname
nachname
alter
1
Peter
Müller
32
7
Andreas
Zwickel
34
Employees e
p_id vorname nachname
7
Andreas Zwickel
13.10.2004
m/e
p_id
vorname
nachname
alter
1
Peter
Müller
32
alter
34
Felix Naumann, CK Informationssysteme, WS 04/05
44
22
Relationale Algebra –
Kartesisches Produkt
z
z
R×S
Attribute
z
z
z
Unterschiedliche Anzahl
Unterschiedliche Domäne
Ergebnis
z
z
z
z
Keine doppelten Attributnamen
Alle Kombinationen von Tupeln in R und S
Größe: |R| ⋅ |S|
Eher von theoretischem Interesse
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
45
Relationale Algebra –
Kartesisches Produkt
mitarbeiter
projekte
p_id
vorname
nachname alter
proj_id p_id
1
Peter
Müller
32
1
1
DWH
name
kunde
2
Stefanie Meier
34
2
1
eCommerce Metro
BMW
mitarbeiter x projekte
p_id
vorname
nachname alter proj_id
p_id
1
Peter
Müller
32
1
1
DWH
1
Peter
Müller
32
2
1
eCommerce Metro
2
Stefanie Meier
34
1
1
DWH
2
Stefanie Meier
34
2
1
eCommerce Metro
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
name
kunde
BMW
BMW
46
23
Relationale Algebra –
Projektion
z
z
z
z
∏proj(R)
Sei R(A1, ..., An) ein Relationenschema.
Proj = {Aj1, ..., Ajn} ⊆ {A1, ..., An}
Ergebnis
z
z
z
Hat Struktur Aj1, ..., Ajn
Es können Duplikate entstehen, die entfernt
werden müssen.
| ∏proj(R) | ≤ |R|
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
47
Relationale Algebra –
Projektion
mitarbeiter
mitarbeiter
p_i
vorname
32
1d
Peter
34
2
Stefani
e
p_id
vorname
nachname alter
1
Peter
Müller
2
Stefanie Meier
nachnam
e
Müller
alte
32r
Müller
34
Πvorname,nachname(mitarbeiter)
Πnachname(mitarbeiter)
vorname
nachname
Peter
Müller
nachnam
e
Müller
Stefanie Meier
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
48
24
Relationale Algebra –
Selektion
z
z
z
σcond(R)
Sei R(A1:D1, ..., An:Dn) ein Relationenschema
cond ist „Selektionsbedingung“
z Form: „Ai op Aj“ oder „Ai op const“
z
z
z
z
Boolescher Ausdruck
z
z
Ai, Aj ∈ {A1, ..., An}
Di = Dj, und const ∈ Di
Op ∈ {=,≠,≤,≥,<,>}
Junktoren: UND, ODER, NICHT
Ergebnis
z Alle Tupel aus, die cond erfüllen
z Gleiche Struktur wie R
z |σcond(R)| = sfcond ⋅ |R| ≤ |R|
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
49
Relationale Algebra –
Selektion
mitarbeiter
σalter>33(mitarbeiter)
p_id
vorname
nachname alter
p_id
vorname
1
Peter
Müller
32
2
Stefanie Meier
34
2
Stefani
e
Meier
34
7
Andreas
44
5
Petra
Weger
28
σalter>33, nachname = „Meier“(mitarbeiter)
7
Andreas
Zwickel
44
p_id
vorname
2
Stefanie Meier
nachname alter
Zwickel
nachname alter
34
σalter= p_id(mitarbeiter)
p_id
13.10.2004
vorname
Felix Naumann, CK Informationssysteme, WS 04/05
nachname alter
50
25
Relationale Algebra – Join
z
z
z
R ><cond S
Seien R(A1:D1, ..., An:Dn) und S(B1:E1, ..., Bm:Em)
Relationenschemata.
cond ist „Joinbedingung“
z
z
z
Wichtigster, schwierigster
Form: „Ai op Bj“
und somit interessantester
z Op ∈ {=,≠,≤,≥,<,>}
Operator!
z Di und Ej „kompatibel“
Boolescher Ausdruck
z Junktoren: UND, ODER, NICHT
R ><cond S = σcond(R X S)
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
51
Relationale Algebra – Join
mitarbeiter
projekte
p_id
vorname
nachname alter
proj_id p_id
1
Peter
Müller
32
1
1
DWH
2
Stefanie Meier
34
2
1
eCommerce Metro
5
Petra
Weger
28
5
7
SAP
7
Andreas
Zwickel
44
name
kunde
BMW
RAG
mitarbeiter ><p_id = p_id projekte
p_id
vorname
nachname Alter proj_id
p_id
1
Peter
Müller
32
1
1
DWH
1
Peter
Müller
32
2
1
eCommerce Metro
7
Andreas
Zwickel
44
5
7
SAP
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
name
kunde
BMW
RAG
52
26
Relationale Algebra –
Schnittmenge
z
z
R∩S
Attribute von R und S
z
z
z
Ergebnis
z
z
z
z
Gleiche Anzahl
Gleiche Domänen
Mengentheoretische Schnittmenge
gleiche Struktur wie R bzw. S
Duplikat-frei
R ∩ S = R / (R / S)
13.10.2004
nachdenken!
Felix Naumann, CK Informationssysteme, WS 04/05
53
Relationale Algebra –
Schnittmenge
Mitarbeiter m
p_id
vorname
nachname
alter
1
Peter
Müller
32
7
Andreas
Zwickel
34
Employees e
p_id vorname nachname
5
Petra
7
Andreas Zwickel
13.10.2004
Weger
m∩e
p_id
vorname
nachname
alter
7
Andreas
Zwickel
34
alter
28
34
Felix Naumann, CK Informationssysteme, WS 04/05
54
27
Relationale Algebra – Weitere
Operatoren
z
Join Varianten
z
z
z
z
z
Semi-Join
Natural Join
θ-Join
Outer Join (Left-, Right- und Full-Outer Join)
Division
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
55
Relationale Algebra - Anfragen
mitarbeiter
p_id
vorname
nachname
alter
1
Peter
Müller
32
2
Stefanie
Meier
34
5
Petra
Weger
28
Andreas
Zwickel
44
7
...
...
...
Vor- und Nachname aller
Mitarbeiter älter als 40?
...
Πvorn., nachn.(σ“alter > 40“(mitarbeiter))
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
56
28
Relationale Algebra - Anfragen
mitarbeiter
p_id
vorname
nachname
alter
1
Peter
Müller
32
2
Stefanie
Meier
34
5
Petra
Weger
28
7
Andreas
Zwickel
44
...
...
...
projekte
proj_id
p_id
name
1
1
DWH
2
1
eCommerce
5
7
...
13.10.2004
...
Πm.nachn.,p.name
(σ“m.p_id = p.p_id“(mit. x proj.))
kunde
BMW
SAP
...
Nachname und
Projektname aller
Mitarbeiter
Πm.nachn.,p.name
(mit. ><m.p_id = p.p_id proj.)
Metro
RAG
...
...
Πm.nachn.,p.name(mit.
>< proj.)
Felix Naumann, CK Informationssysteme, WS 04/05
57
Überblick
z
RDBMS – Relational Database Management
System
z Relationale Datenbanken
z Relationales Modell (Datenmodell)
z Relationale Algebra (Datenanfrage)
z SQL (Anfragesprache)
z Anfragebearbeitung
z Optimierung
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
58
29
SQL – Structured Query
Language
z
z
z
z
Erstmals: System R, IBM Almaden
ANSI-SQL (89), SQL-2(92), SQL-3(98)
Sehr weit verbreitet.
Deklarativer Charakter:
z
z
z
Fünf Grundbefehle:
z
z
Was soll das DBMS zeigen?
Nicht: Wie soll das DBMS das zu Zeigende finden
(prozedural)?
Create, Insert, Update, Delete, Select
DDL versus DML
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
59
SQL – Anfragen
z
Grundform
z
z
SELECT
FROM
WHERE
Ri1.A1, Ri2.A2, …, Rim.Am
Ri1, Ri2, …, Rim
<cond>
Beispiel
z
SELECT
FROM
WHERE
13.10.2004
Vorname, Nachname
mitarbeiter
Alter > 40
Felix Naumann, CK Informationssysteme, WS 04/05
60
30
SQL – Beispiele
z
z
z
z
SELECT
FROM
SELECT
FROM
WHERE
SELECT
FROM
SELECT
FROM
WHERE
13.10.2004
Name
Projektion
projekt
Selektion
*
project
Kartesisches
Budget < 40.000
Produkt
mitarbeiter.Nachname, projekt.Name
mitarbeiter, projekt
mitarbeiter.Nachname, projekt.Name
mitarbeiter, projekt
Join
mitarbeiter.p_id = projekt.p_id
Felix Naumann, CK Informationssysteme, WS 04/05
61
SQL – Beispiele
z
z
z
SELECT
FROM
WHERE
AND
SELECT
FROM
WHERE
SELECT
FROM
WHERE
AND
13.10.2004
mitarbeiter.Nachname, projekt.Name
mitarbeiter, projekt
mitarbeiter.p_id = projekt.p_id
projekt.Budget < 40.000
m.Nachname, p.Name
mitarbeiter m, projekt p
Abkürzung
m.p_id = p.p_id
m.Nachname
mitarbeiter m, projekt p
m.p_id = p.p_id
Es muss nicht jede
p.Budget < 40.000
Relation im SELECT
auftauchen.
Felix Naumann, CK Informationssysteme, WS 04/05
62
31
SQL – Weitere Funktionen
z
DISTINCT
ORDER BY
UNION, DIFFERENCE, INTERSECTION
Schachtelung
Aggregation
GROUP BY
z
Alle sind für die Informationsintegration wichtig.
z
z
z
z
z
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
63
SQL – Beispiele
z
z
SELECT DISTINCT mitarbeiter.Vorname
FROM
mitarbeiter, projekt
WHERE mitarbeiter.p_id = projekt.p_id
AND
projekt.Budget < 40.000
SELECT m.Nachname, p.Name
FROM
mitarbeiter m, projekt p
WHERE m.p_id = p.p_id
ORDER BY p.budget ASC (bzw. DESC)
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
64
32
SQL – Beispiele
z
SELECT
FROM
WHERE
AND
UNION
SELECT
FROM
WHERE
AND
13.10.2004
mitarbeiter.Vorname, projekt.Name
mitarbeiter, projekt
mitarbeiter.p_id = projekt.p_id
projekt.Budget < 40.000
mitarbeiter.Vorname, projekt.Name
mitarbeiter, projekt
mitarbeiter.p_id = projekt.p_id
projekt.Budget > 100.000
Felix Naumann, CK Informationssysteme, WS 04/05
65
SQL – Beispiele
z
z
SELECT
FROM
WHERE
AND
m.Vorname, p.Name
mitarbeiter m, projekt p
m.p_id = p.p_id
p.partner IN
(SELECT company.Name
FROM company
WHERE company.country = “DE”)
Schachtelung
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
66
33
SQL – Beispiele
z
Aggregation
z
z
z
z
AVG, COUNT, SUM, MIN, MAX
SELECT
FROM
SELECT
FROM
WHERE
AND
SELECT
FROM
13.10.2004
AVG(Budget)
projekt
SUM(p.Budget), MAX(p.Budget)
mitarbeiter m, projekt p
m.p_id = p.p_id
m.Nachname = “Schmidt”
COUNT(*)
mitarbeiter
Felix Naumann, CK Informationssysteme, WS 04/05
67
SQL – Beispiele
z
z
SELECT m.Nachname, SUM(p.Budget)
FROM
mitarbeiter m, projekt p
WHERE m.p_id = p.p_id
GROUP BY m.Nachname
SELECT m.Nachname, AVG(p.Budget)
FROM
mitarbeiter m, projekt p
WHERE m.p_id = p.p_id
GROUP BY m.Nachname
HAVING MIN(p.Budget) > 50,000
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
68
34
SQL – DDL
z
z
DDL: Data Definition Language
Definition von
z
z
z
Tabellen, Indexen, Views, ...
Administration: Tablespaces, Segmente,
Parameter
Benutzerverwaltung: User, Gruppen, Rechte, ...
CREATE TABLE mitarbeiter (
p_id
NUMBER,
vorname
VARCHAR2(100),
nachname
VARCHAR2(100),
alter
NUMBER(2) CHECK (alter>0 AND alter<150),
adresse
VARCHAR2(1000)
); 13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
69
DQL – DDL
z
Datentypen
z
z
z
z
z
z
INTEGER, SMALLINT
REAL, DOUBLE, NUMERIC(a,b), DECIMAL(a,b)
CHAR(n), VARCHAR(n)
BIT
DATE, TIME, TIMESTAMP, INTERVAL
Attributnamen eindeutig
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
70
35
Datentypen
z
z
CHAR(n)
z n maximal 256
VARCHAR(n)
z n maximal 4000
z Falls n > 254: Kein
z
z
z
z
z
z
Group By
Order By
DISTINCT
Mengenoperationen (UNION, DIFFERENCE, INTERSECTION)
BLOB
z Binary large object
z Max 2GB Binärdaten
CLOB
z Character large object
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
71
SQL – Insert
z
z
Einfügen von Werten in Tabelle
Prinzipiell ein Tupel pro Insert:
INSERT INTO mitarbeiter VALUES
(1, ‚Peter‘, ‚Müller‘, 38, ‚10101 Berlin‘);
INSERT INTO projekte (proj_id,p_id,name) VALUES
(1, 3, ‚BMW‘);
z
Erweiterungen
z
z
Bulk-Insert
INSERT INTO ... SELECT ...
Insert in mehrere Tabellen:
INSERT INTO ... INTO ... INTO ...
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
72
36
SQL – Update
z
z
Ändern von Werten in Tabellen
Ändert mehrere Werte in einer Tabelle
UPDATE projekte
SET status = „abgebrochen“,
kunde = kunde||“-insolvent“
WHERE kunde=„Grundig“
z
Typisches Muster
UPDATE tabelle
SET ... = (SELECT ... FROM ... WHERE)
WHERE id in (SELECT ... FROM ... WHERE)
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
73
Delete
z
Löschen von Tupeln in einer Tabellen
DELETE FROM projekte
WHERE status=„abgeschlossen“
z
Typisches Muster
DELETE FROM projekte
WHERE id in (SELECT ... FROM ...
WHERE)
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
74
37
Pause & Übung
Überblick
z
RDBMS – Relational Database Management
System
z Relationale Datenbanken
z Relationales Modell (Datenmodell)
z Relationale Algebra (Datenanfrage)
z SQL (Anfragesprache)
z Anfragebearbeitung
z Optimierung
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
76
38
Anfragebearbeitung - Problem
z
Anfragen sind deklarativ.
Anfragen müssen in ausführbare (prozedruale)
Form transformiert werden.
z
Ziele
z
QEP – prozeduraler Query Execution Plan
Effizienz
z Schnell
z Wenig Ressourcenverbrauch (CPU, I/O, RAM, Bandbreite)
z
z
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
77
Anfragebearbeitung
z
Prinzipieller Ablauf
Parsen der Anfrage (Syntax)
Überprüfen der Elemente (Semantik)
Berechnung von Ausführungsplänen
1.
2.
3.
Exponentiell viele
–
Wahl des optimalen Ausführungsplans
4.
Regelbasierter Optimierer
Kostenbasierter Optimierer
–
–
5.
Anfrageausführung
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
78
39
Abfragebearbeitung – Syntax
z
SQL Anfrage wird in interne Darstellung
übersetzt:
z
z
Z.B: Relationale Algebra
Syntaktische Korrektheit prüfen
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
79
Anfragebearbeitung –
Semantik
z
Während der Übersetzung semantische
Korrektheit prüfen
z
z
z
z
Existieren die Relationen/Attribute?
Korrekte Typen für Vergleiche?
Aggregation korrekt?
...
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
80
40
Anfragebearbeitung – Beispiel
z
z
SELECT m.Nachname
FROM
mitarbeiter m, projekt p
WHERE m.p_id = p.p_id
AND
p.Budget < 40.000
Πm.Nachname(σ“m.p_id = p.p_id, p.Budget < 40.000“(m x p))
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
81
Anfragebearbeitung –
Anfragebaum
z
Πm.Nachname(σ“m.p_id = p.p_id, p.Budget < 40.000“(m x p))
π(Nachname)
σ(p.Budget < 40.000)
σ(m.p_id = p.p_id)
X
mitarbeiter
13.10.2004
projekte
Felix Naumann, CK Informationssysteme, WS 04/05
82
41
Anfragebearbeitung –
Ausführung
Interpretation des
Baums als
Ausführungsplan
z
σ(p.Budget < 40.000)
Kartesisches Produkt
Zwei Selektionen
Projektion
1.
2.
3.
Sehr aufwendig!
z
π(Nachname)
13.10.2004
σ(m.p_id = p.p_id)
X
mitarbeiter
projekte
Felix Naumann, CK Informationssysteme, WS 04/05
83
Anfragebearbeitung –
Transformationsregeln
z
Transformation der internen Darstellung
z
z
Ohne Semantik zu verändern
Zur effizienteren Ausführung
z
z
Insbesondere: Kleine Zwischenergebnisse
Äquivalente Ausdrücke
z
Zwei Ausdrücke der relationalen Algebra heißen
äquivalent, falls
z
z
Gleiche Operanden (= Relationen)
Stets gleiche Antwortrelation
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
84
42
Anfragebearbeitung – einige
Transformationsregeln
z
Kommutativität
z
z
Assoziativität
z
z
(m x p) x q = m x (p x q)
Kaskade von Projektionen
z
z
mxp=pxm
Πm.Nachname(Πm.Vorname, m.Nachname m) = Πm.Nachname(m)
Kaskade von Selektionen
z
σ“m.p_id = p.p_id“(σ“p.Budget < 40.000“(m))
= σ“m.p_id = p.p_id AND p.Budget < 40.000“(m)
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
85
Anfragebearbeitung – einige
Transformationsregeln
z
Zusammenfassen von Selektion und Kartesischen
Produkt zu Join
z
z
Vertauschen von Selektion und Projektion
z
z
z
σ“m.p_id = p.p_id“(m x p) = m ><“m.p_id = p.p_id“ p
Πm.Nachname,m(σ“m.p_id = p.p_id“(m x p))
= Πm.Nachname(σ“m.p_id = p.p_id“(Πm.Nachname,m.p_id, p.p_id(m x p)))
Vertauschen von Selektion und Join
z σ“p.Budget < 40.000“(p ><“m.p_id = p.p_id“ m)
= σ“p.Budget < 40.000“(p) ><“m.p_id = p.p_id“ m
Vertauschen von Projektion und Join
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
86
43
Anfragebearbeitung – Beispiel
π(Nachname)
π(Nachname)
σ(p.Budget < 40.000)
σ(p.Budget < 40.000)
σ(m.p_id = p.p_id)
X
><m.p_id = p.p_id
mitarbeiter
projekte
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
mitarbeiter
projekte
87
Anfragebearbeitung – Beispiel
π(Nachname)
π(Nachname)
σ(p.Budget < 40.000)
><m.p_id = p.p_id
><m.p_id = p.p_id
σ(p.Budget < 40.000)
mitarbeiter
13.10.2004
projekte
mitarbeiter
Felix Naumann, CK Informationssysteme, WS 04/05
projekte
88
44
Anfragebearbeitung – Beispiel
π(Nachname)
π(Nachname)
><m.p_id = p.p_id
><m.p_id = p.p_id
π(m.Nachname, m.p_id, p.p_id)
σ(p.Budget < 40.000)
mitarbeiter
13.10.2004
projekte
σ(p.Budget < 40.000)
mitarbeiter
projekte
Felix Naumann, CK Informationssysteme, WS 04/05
89
Überblick
z
RDBMS – Relational Database Management
System
z Relationale Datenbanken
z Relationales Modell (Datenmodell)
z Relationale Algebra (Datenanfrage)
z SQL (Anfragesprache)
z Anfragebearbeitung
z Optimierung
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
90
45
Anfragebearbeitung Optimierung
z
Regelbasierte Optimierung
z
z
z
z
Fester Regelsatz schreibt Transformationen
gemäß der genannten Regeln vor.
Prioritäten unter den Regeln
Nicht hier.
Kostenbasierte Optimierung
z
z
Kostenmodell
Transformationen um Kosten zu verkleinern
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
91
Anfragebearbeitung Optimierung
z
Kostenmodell
z
z
z
z
Kardinalitäten der
Relationen
Werteverteilungen
(Histogramme)
Selektivität
z Selektionen
z Joins
13.10.2004
Hardware
z
z
z
Speichergröße
Bandwidth
I/O Zeiten
Felix Naumann, CK Informationssysteme, WS 04/05
92
46
Anfragebearbeitung –
Optimierung
π(Nachname)
π(Nachname)
[20] σ(p.Budget < 40.000)
[990] σ(m.p_id = p.p_id)
[100.000]
[990]
><m.p_id = p.p_id
X
mitarbeiter
projekte
[1.000]
[100]
13.10.2004
σ(p.Budget < 40.000) [20]
mitarbeiter
projekte
[1.000]
[100]
Felix Naumann, CK Informationssysteme, WS 04/05
93
Anfragebearbeitung –
Optimierung
π(Nachname)
π(Nachname)
σ(p.Budget < 40.000) [20]
><m.p_id = p.p_id
><m.p_id = p.p_id
[20]
[990]
σ(p.Budget < 40.000) [5]
mitarbeiter
projekte
[1.000]
[100]
13.10.2004
mitarbeiter
[1.000]
Felix Naumann, CK Informationssysteme, WS 04/05
projekte
[100]
94
47
Anfragebearbeitung –
Optimierung
z
Freiheitsgrade:
z
z
z
Algebraische Anfrageumformung
Joinreihenfolge
Joinmethode
z
z
z
z
Nested Loop, Sort-Merge, Hash ...
Indexzugriff oder Full-Table-Scan
Operatorreihenfolge
...
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
95
Anfragebearbeitung –
Optimierung
z
Joinreihenfolge
z
z
z
z
z
Join ist i.d.R. teuerster Operator.
Optimierung konzentriert sich auf beste Reihenfolge.
Bei n Relationen n! Alternativen
z Aber: Die meisten haben kartesisches Produkt.
Viele Algorithmen
z Dynamische Programmierung (in VL)
Bei mehr-Prozessor Systemen nicht nur Reihenfolge
sondern auch Parallelisierung.
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
96
48
Anfragebearbeitung –
Optimierung
z
Joinmethode
z
z
z
Nested Loops Join
z Zwei geschachtelte Schleifen
z Innere und äußere Relation
z Aufwand: n ⋅ m
Sort Merge Join
z Sortiere jede Relation
z Ein mischender Cursor-Durchlauf durch jede Relation
z Aufwand: n log n + m log m + n + m
z Günstig, falls Relationen sowieso sortiert, oder bei Index
Hash Join
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
97
Anfragebearbeitung –
Optimierung
z
Nested Loops – Äußere vs. Innere Relation
z
FOR EACH r in R
z FOR EACH s in S
z
z
z
IF(r.A = s.A) THEN Output (r,s)
Aufwandsunterschied R und S?
Ja, wegen Organisation der Tupel als Seiten
auf Festplatte...
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
98
49
Anfragebearbeitung –
Optimierung
R:
...
S:
...
PR = Anzahl Seiten R
PS = Anzahl Seiten S
R außen: PR + TR · PS = 8 + 48 · 6 = 296
S außen: PS + TS · PR = 6 + 18 · 8 = 150
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
99
Literatur
z
RDBMS
z
z
Principles of Database Systems, Jeffrey Ullman, Computer
Science Press, 1988
Informationsintegration
z
z
Principles of Distributed Database Systems
M. Tamer Özsu, Patrick Valduriez
ISBN: 0136597076
Mediators in the Architecture of Future Information
Systems";
Gio Wiederhold
IEEE Computer, March 1992, pages 38-49.
13.10.2004
Felix Naumann, CK Informationssysteme, WS 04/05
100
50