Vorlesung Data Warehouses

Wissensmanagement in der
Bioinformatik
Ulf Leser
Relationale Datenbanken
Data Warehousing
Ulf Leser: Data Warehousing, Vorlesung, SoSe 2003
2
Relationale Datenbanken
Relationales Modell und relationale Operatoren
SQL
Anfragebearbeitung
Indexstrukturen
ER-Modellierung
Normalformen
• Wer das kennt ...
•
•
•
•
•
•
•
Inhalt dieser Vorlesung
Speicherverwaltung
Transaktionsmanagement
Anfragebearbeitung
Userverwaltung
Ulf Leser: Data Warehousing, Vorlesung, SoSe 2003
• Oracle, DB2, Informix, Sybase, NCR Terada, SQL-Server
• PostGreSQL, Interbase, mySQL, ...
– Systeme
•
•
•
•
– Serverbasierte Software
– Ein RDBMS – viele RDB
– Aufgaben
3
• Relationale Datenbank Management Systeme
(RDBMS)
Relationale Datenbank
Externes
Schema
Ulf Leser: Data Warehousing, Vorlesung, SoSe 2003
4
Physisches Modell
(Indexierung, Speicherung)
Internes
Schema
Sichten
Logisches Modell (Tabellen,
Attribute, Anfragen
Externes
Schema
Konzeptionelles
Schema
Externes
Schema
Schichtenmodell
Listener
Server
Konsistenz
Multiplexing
Parallelisierung
Lastverteilung
Verwaltung
....
Ulf Leser: Data Warehousing, Vorlesung, SoSe 2003
Andere
Datenbank
Native (SQL*Plus,
Native (SQL*Plus,
OCI)
Native
(SQL*Plus,
OCI)
OCI)
JAVA (JDBC)
JAVA (JDBC)
JAVA (JDBC)
Client-Server Konzept
Sekundärspeicher
5
Tupel
Rows
Zeilen
Stefanie
Petra
Andreas
2
5
7
...
Zwickel
Weger
Meier
Müller
Nachname
Spalten
Ulf Leser: Data Warehousing, Vorlesung, SoSe 2003
...
Peter
Vorname
1
...
P_ID
Mitarbeiter
– Tabellenname
– Attribute
– Datentypen
...
44
28
34
32
Alter
...
6
80443 München
80223 München
11202 Berlin
10101 Berlin
Adresse
Attribute
• Repräsentation aller Daten in Tabellen
Relationales Datenmodell
Andreas
7
7
5
...
p_id
SAP
...
eCommerce
DWH
name
...
Zwickel
Weger
Meier
Müller
nachname
...
RAG
Metro
BMW
kunde
...
44
28
34
32
alter
Ulf Leser: Data Warehousing, Vorlesung, SoSe 2003
1
2
...
1
1
proj_id
projekte
Petra
5
...
Stefanie
2
...
Peter
vorname
1
p_id
mitarbeiter
Tabellen
...
Abgeschlossen
Läuft
Akquisition
status
...
80443 München
80223 München
11202 Berlin
10101 Berlin
adresse
7
Ulf Leser: Data Warehousing, Vorlesung, SoSe 2003
8
– Die Spalten Vorname und Nachname aller Zeilen des
kartesischen Produkts von Mitarbeiter und Projekte, bei
denen Mitarbeiter.P_ID=Projekte.P_ID und
Status=„Läuft“
• Komposition von Operationen
– Alle Zeilen von Mitarbeiter verknüpft mit jeweils allen Zeilen
von Projekte
• Kartesisches Produkt
– Alle Mitarbeiter-Spalten Vorname, Nachname
• Projektion
– Alle Zeilen von Mitarbeiter mit Alter>40 und
Name=„Müller“
• Selektion
Operationen
•
•
•
•
•
•
M.nachname, M.vorname
mitarbeiter M, projekte P
M.p_id = P.p_id AND
P.status=„läuft“
Ulf Leser: Data Warehousing, Vorlesung, SoSe 2003
SQL: Structured Query Language
ANSI-SQL, SQL-92, SQL-3
Deklarativer Charakter: Was, nicht wie
Vier Grundbefehle: Insert, Update, Delete, Select
DDL versus DML
Andere Sprachen: Tupel/Domänenkalkül, relationale
Algebra, QBE
SELECT
FROM
WHERE
SQL – Grundkonzepte
9
Ulf Leser: Data Warehousing, Vorlesung, SoSe 2003
– Bulk-Insert
INSERT INTO ... SELECT ...
– Insert in mehrere Tabellen:
INSERT INTO ... INTO ... INTO ...
• Erweiterungen
INSERT INTO projekte (proj_id,p_id,name) VALUES
(1, 3, „BMW“);
INSERT INTO mitarbeiter VALUES
(1, „Peter“, „Müller“, 38, „10101 Berlin“);
• Einfügen von Werten in Tabelle
• Prinzipiell ein Tupel pro Insert
Insert
10
Ulf Leser: Data Warehousing, Vorlesung, SoSe 2003
– UPSERT
• Erweiterungen
UPDATE tabelle
SET ... = (SELECT ... FROM ... WHERE)
WHERE id in (SELECT ... FROM ... WHERE)
• Typisches Muster
UPDATE projekte
SET status = „abgebrochen“,
kunde = kunde||“-insolvent“
WHERE kunde=„Grundig“
• Ändern von Werten in Tabellen
• Ändert mehrere Werte in einer Tabelle
Update
11
Ulf Leser: Data Warehousing, Vorlesung, SoSe 2003
12
• Löschen ist eine performancekritische Operation
DELETE FROM projekte
WHERE id in (SELECT ... FROM ... WHERE)
• Typisches Muster
DELETE FROM projekte
WHERE status=„abgeschlossen“
• Löschen von Tupeln in einer Tabellen
Delete
M.nachname, M.vorname
mitarbeiter M, projekte P
M.p_id = P.p_id AND
P.status=„läuft“
M.nachname, M.vorname
mitarbeiter M, projekte P
M.p_id = P.status
SELECT
FROM
WHERE
SELECT
FROM
WHERE
?
Ulf Leser: Data Warehousing, Vorlesung, SoSe 2003
13
• Ergebnis ist Tabelle
• Ausführung ist Sache des RDBMS - Optimierung
vorname, nachname
mitarbeiter
alter<30
SELECT
FROM
WHERE
• Selektieren von Werten aus mehreren Tabellen
Select
vorname, nachname
mitarbeiter M
EXISTS (
proj_id
projekt P
P.p_id = M.p_id
14
SELECT X.nachname, X.status
FROM
(
SELECT *
FROM mitarbeiter M, projekte P
WHERE M.p_id=P.p_id) X
WHERE X.status=„Akquisition“
SELECT P1.vorname, P1.nachname
FROM
projekte P1, projekte P2
WHERE P1.vorgaenger=p2.proj_id AND
P2.status=„abgeschlossen“
SELECT
FROM
WHERE
SELECT
FROM
WHERE
Ulf Leser: Data Warehousing, Vorlesung, SoSe 2003
– „In-Line Views“
– Nützlich bei Top-Ten /
Sortieranfragen
• SQL in FROM Klausel
– „Begrenzte Rekursion“
• Self-Join
– Korreliert oder nicht
– Umschreiben möglich?
• Subqueries
Varianten
name, nachname, vorname
mitarbeiter M, projekte P
M.p_id=P.p_id
P.name, M.nachname,M.vorname
COUNT(*), SUM(alter)/COUNT(*)
mitarbeiter M, projekte P
M.p_id=P.p_id
P.proj_id
Ulf Leser: Data Warehousing, Vorlesung, SoSe 2003
SELECT
FROM
WHERE
ORDER BY
• ORDER BY
SELECT
FROM
WHERE
GROUP BY
• Aggregation und GROUP BY
Weitere Operationen
15
Ulf Leser: Data Warehousing, Vorlesung, SoSe 2003
16
CREATE TABLE mitarbeiter (
p_id
NUMBER,
vorname
VARCHAR2(100),
nachname
VARCHAR2(100),
alter
NUMBER(2) CHECK (alter>0 AND alter<150),
adresse
VARCHAR2(1000)
);
– Tabellen, Indexen, Views, ...
– Administration: Tablespaces, Segmente, Rollen
– Benutzerverwaltung: User, Gruppen, Rechte, ...
• DML: Data Manipulation Language
• DDL: Data Definition Language
• Definition von
DDL
• Exponentiell viele
Expandieren von Views
Parsen der Anfrage (Syntax)
Überprüfen der Elemente (Semantik)
Berechnung von Ausführungsplänen
Ulf Leser: Data Warehousing, Vorlesung, SoSe 2003
– Ausführung
• Regelbasierter Optimierer
• Kostenbasierter Optimierer
– Wahl des optimalen Ausführungsplans
–
–
–
–
• Prinzipieller Ablauf
Anfrageübersetzung und -optimierung
17
mitarbeiter
projekte
σ(status=„abg.“)
18
mitarbeiter
π(p_id,nachname,
vrname)
NLJ(p_id=p_id)
π(name,nachname,
vorname)
name, nachname, vorname
mitarbeiter M, projekte P
M.p_id=P.p_id AND
P.status=„abgeschlossen“
Ulf Leser: Data Warehousing, Vorlesung, SoSe 2003
projekte
NLJ(p_id=p_id)
σ(status=„abg.“)
π(name, nachname)
SELECT
FROM
WHERE
Ausführungspläne
Algebraische Anfrageumformung
Joinreihenfolge
Joinmethode (Nested Loop, Sort-Merge, Hash ...)
Indexzugriff oder Full-Table-Scan (7% Regel)
Operatorreihenfolge
...
Ulf Leser: Data Warehousing, Vorlesung, SoSe 2003
– Minimierung von Zwischenergebnissen
– Minimierung von Sekundärspeicherzugriff
• Heuristische Ziele
19
– Einbeziehung von Werteverteilungen, Tabellengrößen, Anzahl
NULL-Werten, Histogrammen, Selektivität, ...
• Kostenbasierter Optimierung
–
–
–
–
–
–
• Freiheitsgrade
Ausführungspläne
AB
GHIJ
LMN
Ulf Leser: Data Warehousing, Vorlesung, SoSe 2003
DE
CF
K
PQRS
OT
UVW
20
• Knoten enthalten höchstens 2m Schlüssel
• Knoten enthalten mindestens m Schlüssel, die Wurzel
mindestens 1 Schlüssel
• Knoten mit x Schlüsseln - x+1 Kinder
• Balancierter Baum: Alle Blätter haben gleiche Tiefe
• B*: Daten nur in Blättern
Balancierter B und B* Baum
-
Ulf Leser: Data Warehousing, Vorlesung, SoSe 2003
21
Elemente müssen geordnet sein
B Bäume degenerieren bei vielen Duplikaten
Sequentieller Blockzugriff wird verhindert (7%)
Zusammengesetzte Indexe duplizieren Tabellen
Indexaktualisierung kostet Zeit
+ Sehr schneller Zugriff auf einzelne Elemente
(O(log(n))
+ Bereichsanfragen durch B* Baum
+ Indexierung von Attributkombinationen
Indexstrukturen
nachname
arbeitet_in
22
kunde
p_id
vorname
Ulf Leser: Data Warehousing, Vorlesung, SoSe 2003
– Beziehungen
– Attribute
proj_id
Projekte
p_id
Mitarbeiter
Relationales Modell sehr semantikarm
Modellieren in Tabellen und Joins wenig intuitiv
Modellierungssprachen: ER, EER, UML, ...
Entity-Relationship Modell
– Entities
•
•
•
•
ER-Modellierung
Student
Professor
name
...
...
s_id
1
2
name
...
...
student
p_id
10
11
professor
...
...
...
...
...
...
s_id
1
2
1
2
lehrt
P_id
10
10
11
11
23
Verschmelzen der Tabelle oder FK-Beziehung
Fremdschlüsselbeziehung
Brückentabelle mit zwei Fremdschlüsselbeziehungen
Ulf Leser: Data Warehousing, Vorlesung, SoSe 2003
lehrt
– 1:1
– 1:n
– m:n
• Entity – Tabelle
• Attribute – Attribute dieser Tabelle
• Beziehung
Übersetzung in relationales Modell
Ulf Leser: Data Warehousing, Vorlesung, SoSe 2003
– Datenbankentwurf
– Armstrongkalkül
– Ziel: Redundanzfreies Schema
• Zerlegung nach funktionalen Abhängigkeiten
– Minimale Menge von Attributen, die alle anderen
Attribute einer Tabelle funktional bestimmen
• Schlüsselkandidaten
– p_id
→ Vorname, Nachname, Alter ...
– proj_id → Kunde, Status, ...
• Attribute hängen funktional voneinander ab
Normalformen
24
Ulf Leser: Data Warehousing, Vorlesung, SoSe 2003
– R in 1NF
– Kein Attribut A hängt von A‘∉P ab
– Schlecht: mitarbeiter(p_id,plz,ort)
• Dritte Normalform (3NF)
– R ist in 1NF
– Kein Attribut A hängt von P‘ ⊂ P funktional ab
– Schlecht: lehrt(p_id,s_id,termin,stud_name)
• Zweite Normalform (2NF)
– Alle Attribute von R sind atomar (Adresse!)
• Relationenschema R, Primärschlüssel P
• Erste Normalform (1NF)
Normalformen (nicht exakt ...)
25
Ulf Leser: Data Warehousing, Vorlesung, SoSe 2003
26
• Transaktionsverarbeitung, ACID
• Datenintegrität
• Theorie: Relationenkalkül, algebraische
Umformungen, Armstrongkalkül, ...
• Datensicherheit
• Views
• Block- / Buffer- / Speichermanagement, Caching
• Datenmodellerweiterungen: EER, NF2,
objektrelationale und objektorientierte Systeme
• Programmierung: Trigger, PL/SQL, JDBC, ...
Weitere Themen