Kapitel1_SQL - auf der Homepage des Lehrstuhls für

Modul „Data and Knowledge Management“ MW31.6
SS 2016
Prof. Dr. Johannes Ruhland
Lehrstuhl für Wirtschaftsinformatik
Friedrich-Schiller-Universität Jena
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
1
FAZ, 12. Nov 2014
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
3
Organisation
• Die Vorlesungs- und Übungsunterlagen finden Sie
auf http://www.wiinf.uni-jena.de/Lehre/Download.html
• M.Sc. Wirtschaftsinformatik - Pflichtmodul
• M.Sc. Betriebswirtschaftslehre Schwerpunkt Decision & Risk –
Wahlpflichtmodul
• Die Modulnote setzt sich zusammen aus:
– Projekt (40%) (weitere Informationen in der Übung)
– Klausur (60%)
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
4
Themengebiete der Veranstaltung
• Relationale Datenbanken und SQL (Whg. und Vertiefung )
• XML (Whg. und Vertiefung )
• Data Warehouse & OLAP (MDX, Sternschema,
Schneeflockenschema)
• Spaltenorientierte Datenbanken
• Basistechnologien von NoSQL DB: MapReduce, REST
• Dokumentenorientierte Datenbanken: Beispiel CouchBase
• Graph oriented Databases
• Semantische Datenbanken und SPARQL
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
5
Kapitel 1
RELATIONALE DATENBANKEN UND
SQL (VERTIEFT)
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
6
Basisliteratur
Andreas Heuer, Gunter Saake : Datenbanken Konzepte und Sprachen , International Thomson Publishing, Bonn 2., aktualisierte und erweiterte Auflage, 704
Seiten, Januar 2000, ISBN 3-8266-0619-1
Carlo Batini, Stefano Ceri, Shamkant B. Navathe: Conceptual Database Design: An Entity-Relationship Approach. Benjamin/Cummings 1992
Gottfried Vossen: Datenmodelle, Datenbanksprachen und Datenbankmanagementsysteme, 5. Auflage Oldenbourg 2008
Ramez Elmasri und Shamkant B. Navathe: Grundlagen von Datenbanksystemen, Pearson Studium, 2002, ISBN 9783827370211
Kemper, A. Eickler: Datenbanksysteme – Eine Einführung, 6. Auflage Oldenburg Verlag, 2006, ISBN 3-486-57690-9
Ramez Elmasri und Shamkant B. Navathe: Fundamentals of Database Systems, Addison-Wesley Longman, 2003, ISBN 9780321204486
•
•
•
•
•
•
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
7
http://db-engines.com/de/ranking @ March 2015
„relational
DMBS
rules“
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
8
Online-Literatur
Inhalte dieser Vorlesung entstammen z.T. aus folgenden auch online verfügbaren Quellen:
–
http://www.informatik.uni-frankfurt.de/~prg2/SS2009/folien/zicari/zicari-db_teil1.pdf
–
http://www.iai.uni-bonn.de/III//lehre/vorlesungen/Informationssysteme/WS02/folien/DB3.pdf
–
http://www3.in.tum.de/research/publications/books/DBMSeinf/EIS_4_Auflage/Kapitel4.pps
–
http://glossar.hs-augsburg.de/Verbundoperatoren_%28Join%29
–
http://v.hdm-stuttgart.de/~riekert/lehre/db-kelz/chap7.htm
–
http://www.thomaskaelin.ch/media/archive1/zusammenfassungen/db1.pdf
–
http://www.postgresql.org/docs/8.3/interactive/queries-table-expressions.html
–
http://www.tinohempel.de/info/info/datenbank/operation.htm
–
http://aktuell.de.selfhtml.org/artikel/datenbanken/
–
http://rowa.giso.de/oracle/latex/Komplexere_SQL_Abfragen.html
–
http://www.informatik.uni-bonn.de/III/lehre/vorlesungen/Informationssysteme/WS02/folien/DB3e-1.pdf
–
http://www2.informatik.uni-halle.de/lehre/db/heusa.html
–
http://www.roro-seiten.de/info/db/05Relationenmodell/Relationenmodell.html
•
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
9
Agenda
•
•
•
•
•
•
WI
Zentrale Datenhaltung! Warum?
Entity-Relationship-Modell
Relationenmodell
Structured Query Language (SQL)
Einbindung von Datenbanken in Programme
Verteilte Datenbanken
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
10
Warum benötigen wir Relationale Datenbanken?
Nachteile der Speicherung der Daten für jede Anwendung in einzelnen Dateien
•
Redundanz und Inkonsistenz
–
•
Beschränkte Zugriffsmöglichkeiten
–
•
Mehrbenutzerbetrieb wird von
Dateisystemen nicht unterstützt
Integritätsverletzung
–
•
Informationen können bei
isolierten Dateien schwer
miteinander verknüpft werden
Eingeschränkter
Mehrbenutzerbetrieb
–
•
Informationen werden mehrfach
gespeichert
Einschränkende Bedingungen
(Constraints) sind schwer zu
überprüfen
Sicherheitsprobleme
–
Nicht alle Benutzer sollen Zugriff
auf alle Daten haben
Eigene Darstellung in Anlehnung an: http://www.informatik.uni-frankfurt.de/~prg2/SS2009/folien/zicari/zicari-db_teil1.pdf
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
11
Warum benötigen wir Relationale Datenbanken?
Zentrale Datenverwaltung und Datenhaltung mit einem DBS
•
•
•
•
Ein Datenbanksystem besteht aus
einem DBMS und den dazugehörigen
Datenbanken
Zentraler Zugriff auf die Datenbank von
verschiedenen Applikationen
Zugriff über standardisierte Sprache (de
facto fast immer SQL)
Einbettung von SQL in Anwendungen
Eigene Darstellung in Anlehnung an: http://www.informatik.uni-frankfurt.de/~prg2/SS2009/folien/zicari/zicari-db_teil1.pdf
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
12
Warum benötigen wir Relationale Datenbanken?
Vorteile einer zentralen Datenverwaltung und Datenhaltung mit einem DBS
Die relationalen Datenbanksysteme wurden entwickelt (seit ca. 1975 von IBM
Research), um die Probleme mit der getrennten Dateihaltung zu überwinden:
• Redundanz und Inkonsistenz
• Werden durch die zentrale Datenverwaltung und Datenhaltung vermieden
• Beschränkung der Zugriffsmöglichkeiten
• Durch verschiedene Konzepte können Informationen miteinander verknüpft
werden (z.B. relationales Modell).
• Eingeschränkter Mehrbenutzerbetrieb
• Durch die zentrale Benutzerverwaltung einfach zu realisieren
• Integritätsverletzungen
• Durch Constraints in der zentralen Datenverwaltung gut umzusetzen
• Sicherheitsprobleme
• Durch die zentrale Benutzerverwaltung können Zugriffsrechte gut kontrolliert
werden
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
13
Warum benötigen wir Relationale Datenbanken?
Vorteile einer zentralen Datenverwaltung und Datenhaltung mit einem DBS
• Physische Datenunabhängigkeit
– Anwendungen von Modifikationen an der physischen Speicherstruktur nicht
betroffen
• Logische Datenunabhängigkeit
– Änderungen an der logischen Datenstruktur beeinflussen Anwendungen nicht
•
•
•
•
WI
Dauerhafte Speicherung von großen Datenbeständen
Bereitstellung einer Anfragesprache zum einfachen Umgang mit der Datenbank
Sicherheit gegenüber Hard- und Softwareausfällen
Effizient, möglichst schnell unter Benutzung weniger Ressourcen.
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
14
Alternative Datenbankparadigmen
•
•
•
•
Relationales Datenmodell
Hierarchisches Datenmodell (hier nicht behandelt)
Netzwerk Datenmodell (hier nicht behandelt)
Semistrukturierte Datenbestände XML
– Nicht alle Entitäten haben exakt die gleiche Information zu speichern →
gewisse Flexibilität gefordert (z.B. Rechnung, Datenbank der Freunde, … )
– (siehe später)
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
15
Alternative Datenbankparadigmen
Beispiel zum Relationalen Modell und dessen Flexibilität
halter
idHalter
vname
nname
12
Georg
Müller
56
Hasso
Plattner
89
Nino
Sashi
…
…
…
…
besitzdaten
…
IDHund
IDHalter
seit
bis
312
12
Dec2000
Dec2002
312
56
Jan2003
891
89
Mar2009
…
…
hund
idHund
rufname
rasse
312
Lumpi
Pudel
891
Hasso
Rottw.
0121
NULL
Dackel
…
…
…
WI
…
…
…
…
•Wem
gehört
welcher Hund?
•Wer heißt wie sein Hund?
•Wer heißt wie irgend ein Hund?
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
16
Alternative Datenbankparadigmen
Abfragebeispiel: Flexibilität des relationalen Modells
•
Wer hat den gleichen Vornamen wie sein Hund ?
– Abfrage:
SELECT DISTINCT vname, nname FROM halter ▶◀ besitzdaten ▶◀ hund
WHERE halter.vname = hund.rufname;
•
Wer hat einen Vornamen, den auch irgendein Hund hat?
– Abfrage:
SELECT DISTINCT vname, nname FROM halter, hund
WHERE halter.vname = hund.rufname
•
Das Symbol ▶◀ steht für den NATURAL INNER JOIN (siehe später)
– halter ▶◀ besitzdaten
↔
halter INNER JOIN besitzdaten ON halter.idHalter = besitzdaten.IDHalter
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
17
Alternative Datenbankparadigmen
Sonderformen
• Geokodierte Datenbanken → Sonderrolle der Geokoordinaten
berücksichtigen
– Abfrage z.B. „alle Gasleitungen, die näher als 10 Meter an Grundstück
2739 heranreichen“
• Multimedia-Datenbanken
– „Query by humming“ http://www.musicline.de/de/melodiesuche/input
– Extrem lange Info-Teile (Video-Spur) mit Suchfunktion innerhalb
• Mehrdimensionale Datenspeicherung
– (siehe später unter OLAP-Systeme)
• Semantische Datenbanken
– (siehe später)
• …
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
18
Größenklassen von Datenbanksystemen
•
Desktop-Datenbanken
–
–
•
High-End-Datenbanken
–
–
–
•
ACCESS, SQLIte, …
Ein Nutzer bzw. ein paar Nutzer, kleine Tabellen, wenig Sicherungsmechanismen
ORACLE, SQLServer von MS, DB2 von IBM; (Postgresql, Ingres, Informix, MaxDB)
Sehr viele simultane Nutzer, ausgefeiltes Rollenkonzept, automatische Datensicherung, ggf. „verteilte
Speicherung“ ( eine Tabelle wird aufgespalten und die Teile an verschiedenen Standorten gehalten), Query
Optimizer, …
Erweiterbar um Sonderleistungen wie „spatial queries“ oder XML durch add-ons (sog „blades“ oder
„cartridges“)
Zwischenlösungen
–
–
mySQL, Firebird …
Häufig sehr kostengünstig verfügbar (open source)
SQL als Abfragesprache unterscheidet sich in seinem Kern
nur sehr wenig zwischen den Größenklassen
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
19
Research Datenbanksysteme
•
Shore
–
•
BerkleyDB
–
•
–
–
WI
http://www.oracle.com/technetwor
k/database/berkeleydb/overview/i
ndex.html
SQLITE
–
–
•
http://research.cs.wisc.edu/shoremt/
http://www.sqlite.org/
Siehe auch
http://de.wikipedia.org/wiki/SQLite
SQLite Manager
https://addons.mozilla.org/enUS/firefox/addon/5817
…
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
20
DBS Nutzung in Projekten
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
21
Gartner 2008
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
22
Kapitel 1.1
DAS ENTITY-RELATIONSHIPMODELLL ALS GRUNDLEGENDES
MODELLIERUNGSTOOL
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
23
Das Entity-Relationship-Modell
• Datenbank zeigt Ausschnitt aus der realen Welt
• Modelliere die Diskurswelt nach Entitäten (die Realisationen von
Entitäts-Typen sind), Relationen und Attributen
• Ein Datenbankmodell ist ein System von Konzepten zur
Beschreibung von Datenbanken. Es legt Syntax und Semantik von
Datenbankbeschreibungen für ein Datenbanksystem fest.
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
24
Das Entity-Relationship-Modell
Notation und Syntax
• Entwickelt von P. P. Chen im Jahre 1976.
•
Peter Pin-Shan Chen: The Entity-Relationship Model--Toward a Unified View of Data. In: ACM Transactions on
Database Systems 1/1/1976 ACM-Press ISSN , S. 9–36
• Entity: Objekt der realen oder der Vorstellungswelt, über das
Informationen zu speichern sind z.B. Vorlesungsveranstaltung, Buch,
Lehrperson etc. Auch Informationen über Ereignisse: Prüfungen, ...
• Relationship: Beziehung zwischen Entities, z.B. eine Lehrperson hält eine
Vorlesung
• Attribut: Eigenschaft von Entities oder Beziehungen, z.B. die ISBN eines
Buchs, der Titel einer Vorlesung, oder das Semester, in dem eine
Vorlesung gehalten wird
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
25
Schlüssel im Entity-Relationship-Modell
(erste Einführung)
• Eine minimale Menge von Attributen, welche das zugeordnete Entity
eindeutig innerhalb aller Entities seiner Relation identifiziert, nennt man
Schlüssel oder auch Schlüsselkandidaten.
• Gibt es mehrere solcher Schlüsselkandidaten, wird einer als
Primärschlüssel ausgewählt.
• Oft gibt es künstlich eingeführte Attribute, wie z.B. Personalnummer
(PersNr), die als Primärschlüssel dienen.
• Schlüsselattribute werden durch Unterstreichung gekennzeichnet.
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
26
Das Entity-Relationship-Modell
Beziehungstypen
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
27
Das Entity-Relationship-Modell
Beziehungstypen
•
Man kann Beziehungstypen hinsichtlich ihrer Funktionalität charakterisieren. Ein binärer Beziehungstyp R
zwischen den Entity-Typen E1 und E2 heißt
•
1:1-Beziehung (one-one)
–
–
•
1:N-Beziehung (one-many)
–
–
•
falls analoges zu obigem gilt.
Beispiel: beschäftigt_bei
N:M-Beziehung (many-many)
–
–
WI
falls jedem Entity e1 aus E1 beliebig viele (also keine oder mehrere) Entities aus E2 zugeordnet sind, aber
jedem Entity e2 aus E2 höchstens ein Entity e1 aus E1 zugeordnet ist.
Beispiel: beschäftigen.
N:1-Beziehung (many-one),
–
–
•
falls jedem Entity e1 aus E1 höchstens ein Entity e2 aus E2 zugeordnet ist und umgekehrt jedem Entity e2
aus E2 höchstens ein Entity e1 aus E1 zugeordnet ist.
Beispiel: verheiratet_mit.
wenn keinerlei Restriktionen gelten, d.h. jedes Entity aus E1 kann mit beliebig vielen Entities aus E2 in
Beziehung stehen und umgekehrt kann jedes Entity e2 aus E2 mit beliebig vielen Entities aus E1 in
Beziehung stehen.
Beispiel: befreundet_mit.
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
28
Das Standard- Entity-Relationship-Modell (ERD)
Ein Beispiel aus der Universität
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
29
Das Entity-Relationship-Diagramm
Notation und Syntax
•
Verschiedene Notationen für ein ERD möglich
– die Chen-Notation von Peter Chen, dem
Entwickler der ER-Diagramme, 1976; erweitert
durch die Modifizierte Chen-Notation (MCNotation)
– die IDEF1X als langjähriger De-FactoStandard bei US-amerikanischen Behörden;
– die Bachman-Notation von Charles Bachman
als weit verbreitete Werkzeug-DiagrammSprache;
– die Martin-Notation (Krähenfuß-Notation) als
weit verbreitete Werkzeug-Diagramm-Sprache
(Information Engineering);
– die (min, max)-Notation von Jean-Raymond
Abrial, 1974.
– UML als Standard, den selbst ISO in eigenen
Normen als Ersatz für ER-Diagramme
verwendet.
Inhalte entnommen von:
http://de.wikipedia.org/wiki/Entity-Relationship-Modell
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
30
Erweiterung SERM nach Sinz
•
•
quasihierarchische Anordnung des
Datenschemas
Erkennung von Existenzabhängigkeiten
durch Beziehungssemantik
– Ein Auftrag kann nur angelegt
werden, wenn ein entsprechender
Kunde gepflegt wurde
Inhalte entnommen von:
http://de.wikipedia.org/wiki/Structured-Entity-Relationship-Modell
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
31
• eine Anordnung der Symbole („topologie“), die die
Existenzabhängigkeiten bei weak entities
berücksichtigt (unten)
nach http://udoo.uni-muenster.de/downloads/publications/1308.pdf
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
32
http://www.informatik.unijena.de/dbis/lehre/ss2008/dbs/dbs_lehrer_04.pdf
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
33
Kapitel 1.2
VOM ERD ZUM TABELLENENTWURF /
RELATIONENMODELL
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
34
Grundkomponenten des Tabellenentwurfs
Vom ERD zum Tabellenentwurf
die typischen Komponenten eines RDBMS (als Software)
• Tabellen
• Spalten mit einigen wenigen Datentypen und Constraints
• Id-Spalten und Fremdschlüssel, die nicht wesensverschieden von anderen
Spalten sind
– composite keys
– autogenerated keys
• NULL im key bei 1:N (c) Beziehungen
• Constraints zur Sicherstellung der der Referentiellen Integrität
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
35
Vom ERD zum Tabellenentwurf
Regeln für die Umwandlung
Eine Modellierung muss in die genannten Strukturen der Software gewandelt werden
•
•
•
•
•
WI
Jede Entity gibt eine Tabelle mit (fast immer) Primärschlüssel
Zwischen zwei Tabellen kann via Fremdschlüssel nur eine 1:N oder 1:N(c) Beziehung abgebildet
werden
Eine weak entity = existenzabhängige entity  Abbildung von N:M Beziehungen (oder Relationen
mit „mehr als 2 Beinchen“ (arity >2)) kann als Primärschlüssel den composite key der Elterntabellen
erhalten (oft aber auch eigener Primärschlüssel)
Fremdschlüsselbeziehungen können unter die Constraint der „Referentiellen Integrität“ gestellt
werden
–
On delete cascade
–
•
On delete abort
…
Tabellen können verbunden werden, wobei dies meist längs der Schlüssel 
Fremdschlüsselbeziehungen geschieht, aber das ist nicht zwingend (siehe Einführungsbeispiel: „Wer
heißt wie irgend ein Hund?“)
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
36
Der Tabellenentwurf
Universitätsbeispiel (wird später häufig verwendet)
hören
Studenten
Vorlesungen
voraussetzen
Semester MatrNr VorlNr
VorlNr
Titel
18
26120 5001
5001
Grundzüge
4
2137
5001
5041
2125
Sokrates
C4
226
Jonas
12
27550 5001
5041
Ethik
4
2125
5001
5043
2126
Russel
C4
232
26120
Fichte
10
27550 4052
5043
Erkenntnistheorie
3
2126
5001
5049
2127 Kopernikus C3
310
26830
Aristoxenos
8
28106 5041
5049
Mäeutik
2
2125
5041
5216
6
28106 5052
4052
Logik
4
2125
5043
5052
2133
C3
52
28106 5216
5052 Wissenschaftstheorie
3
2126
5041
5052
2134 Augustinus C3
309
5052
5259
5216
Bioethik
2
2126
36
5259
Der Wiener Kreis
2
2133
2136
Curie
C4
Assistenten
2137
Kant
C4
Name
Fachgebiet
7
Boss
2
2134
3002
Platon
Ideenlehre
2125
3003
Aristoteles
Syllogistik
2125
Sprachtheorie
2126
MatrNr
Name
24002
Xenokrates
25403
27550 Schopenhauer
28106
Carnap
3
29120 Theophrastos
29555
2
Feuerbach
prüfen
2
28106 5001
2126
1
25403 5041
2125
2
27550 4630
2137
2
WI
Vorgänger Nachfolger PersNr
28106 5259
29120 5001
29120 5041
29120 5049
MatrNr VorlNr PersNr Note
SWS gelesenVon
Professoren
29555 5022
5022 Glaube und Wissen
4630
Die 3 Kritiken
4
Rang Raum
Popper
2137
25403 5022
Beispiel entnommen aus
http://www3.in.tum.de/research/publications/books/DBMSeinf/EIS_4_Auflage/Kapitel4.pps
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
PerslNr
Name
3004 Wittgenstein
3005
Rhetikus
Planetenbewegung 2127
37
Vom ERD zum Tabellenentwurf
Schlüssel
Primärschlüssel
•
– Um einen Datensatz (ein Tupel) eindeutig
beschreiben zu können, gibt es drei
Möglichkeiten:
• Ein vorhandenes Attribut ist bereits eindeutig,
z.B. der Name einer Pizza
• Man kombiniert mehrere Attribute, z.B. Datum
und Uhrzeit bei der Bestellung
• Man führt eine Zahl ein, die mit jedem neu
erzeugten Tupel steigt (id).
Fremdschlüssel
•
– Ist der referenzierter Primärschlüssel von
einer anderen Tabelle
Inhalte entnommen von:
http://de.wikipedia.org/wiki/Schl%C3%BCssel_%28Datenbank%29#Fremdschl.C3.BCssel_u
nd_Beziehungstypen
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
38
Beispiel ERD: Uni-Schema
MatrNr
Name
Studenten
Semester
N
N
voraussetzen
Nachfolger
Vorgänger
hören
N
M
Vorlesungen
M
Note
M
N
lesen
prüfen
1
1
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J.
N
arbeitenFür
Fachgebiet
WI Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
SWS
Titel
PersNr
Name| SS2015
Assistenten
Ruhland
VorlNr
1
Professoren
PersNr
Name
Rang
Raum
39
Relationen-Theorie
Grundlagen
•
Mathematische Fundierung des Arbeitens mit Tabellen, Relationen und dem Select
•
•
Basiselement : jede Relation (vulgo: Tabelle) ist Menge von „Tupeln“.
Jedes Tupel ist geordnete Folge von Einträgen („Attributen“) aus jeweils einer Domäne
‡. Jedes Attribut trägt einen Namen
Relation künstler(Rang, Name, Geburtsdatum, lfdNummer, lfdNr2)
•
–
Tupel sind z.B.
(12, „Hansi Hinterseer“,12.Mai1976, 898 , 3)
– Relation ist Menge (im mathem. Sinn) von Tupeln; damit wird keine Reihenfolge der
Tupel garantiert
•
Kombinationen von Attributen, die innerhalb der Tupelmenge eindeutig sind, heißen
Schlüsselkandidaten (candidate key), der tatsächlich gewählte Kandidat Primärschlüssel
–
(Name ,lfdNr2) und
(Geburtsdatum, lfdNummer) sind Schlüsselkandidaten
– der zweite Kandidat ist nicht „minimal“
‡ schließt oft den speziell behandelten Wert NULL ein
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
40
Relationentheorie
Grundlegende Operationen auf Tupeln
π eliminiert Attribute
σ wählt Tupel aus
ρ benennt Attribute oder Relationen um
•
•
•
Projektion
Selektion
Umbenennung
•
•
•
•
Vereinigung
Durchschnitt
Differenz
Kartesisches
•
alle SELECT-Befehle (ohne Aggregation) können aus diesen Basisoperationen aufgebaut
werden
Natural Join wählt aus dem kartesischen Produkt diejenigen Tupel aus, bei denen die
Primäschlüssel  Fremdschlüssel-Beziehungen „passen“
Left Outerjoins u.ä. lassen sich ebenso aus den Basisoperationen definieren
•
•
WI
R1 ∪ R2 bildet die Vereinigungsmenge typkompatibler Tupel
R1 ∩ R2 entsprechend die Durchschnittsmenge
R1 ∖ R2 eliminiert Tupel von R2 aus R1
Produkt R1 X R2 bildet die Menge aller Tupel, deren erster
Teil aus R1 und zweiter Teil aus R2 stammt
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
41
Relationentheorie
Grundlegende Operationen auf Tupeln
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
42
Theorie der Normalformen
Kernfrage: was kann man rein aus Kenntnis der momentanen Tupel machen, um
änderungsfreundliche, redundanzarme Darstellungen in Tabellen (Relationen) zu erhalten ?
•
•
•
1 NF Spalten so definieren, dass „alle Attribute elementar“ sind
2 NF bei allen möglichen zusammengesetzten Schlüsselkandidaten: nirgends Abhängigkeit
von Teilen des Schlüssels (nur bei zusammengesetzten Schlüsseln)
3 NF Die dritte Normalform ist erreicht, wenn sich das Relationenschema in 2NF befindet, und
jedes Nichtschlüsselattribut von keinem Schlüsselkandidaten transitiv abhängt.
Ein Attribut A ist vom Schlüsselkandidaten P transitiv abhängig, wenn es ein Attribut B gibt, sodass P  B und B  A
Vulgo: Ein Nichtschlüsselattribut darf nicht von einer Menge aus Nichtschlüsselattributen abhängig sein. Ein
Nichtschlüsselattribut darf also nur direkt von einem Schlüssel abhängen.
•
•
•
WI
minimal schärfer: BCNF (every non key field depends on the key , the full key and nothing but
the key)
4 NF nicht behandelt
5 NF ebenso
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
43
Notwendigkeit der Normalisierung
 Lösch-, Update und
Einfügeanomalien sollen
verhindert werden
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
44
Beispiel zur 3. NF
(aus http://www.teialehrbuch.de/Kostenlose-Kurse/SQL/14675-Die-dritte-Normalform.html)
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
45
Boyce-Codd Normalform
Boyce-Codd-Normalform (BCNF)
• Eine Relation ist in BCNF, wenn sie die Voraussetzungen der 3NF erfüllt, und jede Determinante
(Attributmenge, von der irgendwelche andere Attribute funktional abhängen) ein Superschlüssel ist
(oder die Abhängigkeit ist trivial). Ein Superschlüssel ist eine Menge von Attributen, von der alle
Attribute der Relation funktional abhängig sind.
• Die BCNF (nach Raymond F. Boyce und Edgar F. Codd) verhindert, dass Teile zweier aus mehreren
Feldern (nicht überlappungsfrei) zusammengesetzten Schlüsselkandidaten voneinander abhängig
sind
• Die BCNF ist höchstens im Fall zusammengesetzter Schlüssel von der 3NF verschieden, und auch
dort nur höchstens, falls gilt
– der Primärschlüssel ist eine Zusammensetzung aus mehreren sog. Schlüsselattributen
– es gibt mehr als einen Schlüsselkandidaten
– die Schlüssel sind nicht disjunkt zueinander, d.h. mehrere Attribute kommen in mehr als einem
Schlüsselkandidaten vor
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
46
Beispiel zur BCNF
In diesem Beispiel gibt es eine einfache Datenbank, in der die Vereinszugehörigkeit von
Sportlern gespeichert wird. Es sollen die folgenden Bedingungen gelten:
jeder Verein bietet nur eine Sportart an.
ein Sportler kann in verschiedenen Vereinen spielen, aber nur, wenn diese Vereine
unterschiedliche Sportarten betreiben. (Damit wird sichergestellt, dass der Sportler nie gegen einen
Verein spielt, in dem er selbst Mitglied ist.) (*)
Quelle: http://de.wikipedia.org/wiki/Normalisierung_(Datenbank)#Boyce-Codd-Normalform_.28BCNF.29
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
47
Beispiel
Verletzung der BCNF
• Aus den oben genannten Bedingungen folgt, dass das Attribut Sportart funktional
abhängig vom Attribut Verein ist, d. h. Verein ist eine Determinante. Jedoch ist Verein
kein Schlüsselkandidat. Mögliche Schlüsselkandidaten sind {Name,Verein} und
{Name,Sportart}. Wie man sieht, kommt jedes Attribut in einem Schlüsselkandidat vor,
wodurch die Relation in 3NF, allerdings nicht in BCNF ist. Jedoch ist eine Konvertierung in
BCNF möglich, indem (Name, Verein) als Primärschlüssel verwendet und die Relation
aufgeteilt wird:
wenn EC Beispielstadt jetzt
ein Fußballverein wird,
können wir (*) NUR NACH
JOIN verifizieren
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
48
Literatur zur Technik der Normalisierung
•
•
•
WI
http://citeseerx.ist.psu.edu/viewdoc/download?doi=10.1.1.87.6890&rep=rep1&type=pdf
http://de.wikipedia.org/wiki/Normalisierung_%28Datenbank%29#Zerlegungsalgorithmus
http://de.wikipedia.org/wiki/Synthesealgorithmus-Normalform
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
49
Kapitel 1.3
STRUCTURED QUERY LANGUAGE
(SQL)
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
50
Structured-Query-Language (SQL)
•
•
•
•
WI
Strukturierte Ablage von Daten
ist schön
Genauso wichtig ist aber auch
die Möglichkeit der effizienten
Abfrage bzw. Extraktion von
Daten!!!
SQL enthält Sprachelemente
zum Erzeugen/Ändern von
Tabellen und zu deren Abfrage
… und ist sehr umfassend
verbreitet (in verschiedenen
Dialekten)
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
51
Structured-Query-Language (SQL)
• SQL = DDL (incl. SDL und VDL) + DML + DCL
• Weiterentwicklung des SQL Standards im Laufe der Zeit (SQL 92 als
verbreitetster) + viele Dialekte je nach Hersteller
• Sehr oft wird nur eine Teilmenge des SQL Standards implementiert
• SQL ist zeichenorientiert (character-orientierte 4 GL):
– Benutzer schreibt SQL entweder selbst oder …
– ein GUI erzeugt im Hintergrund SQL, das an die Datenbank gesandt wird
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
52
Grundkomponenten
• DDL (Data Definition Language)
– CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX ,…
• DML (Data Manipulation Language)
– INSERT INTO, DELETE, UPDATE
– Abfragen
• INNER und OUTER JOINS über Tabellen
• Nested queries (subqueries)
• Correlated and uncorrelated
• IN, ANY , ALL als modifiers im WHERE Teil
• Aggregation
• Details gibt es auf den nachfolgenden Folien!
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
53
Structured-Query-Language (SQL)
Elemente der Data Definition Language (DDL)
•
•
•
•
CREATE TABLE
ALTER TABLE
DROP TABLE
Datentypen für die Spalten
– „NULL“ / NOT NULL / UNIQUE
– Wertebereiche und referentielle Integrität
• etliche andere Befehle(Trigger etc. siehe unten)
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
54
Structured-Query-Language (SQL)
CREATE TABLE und ALTER TABLE
•
•
CREATE TABLE „Tabellen_Name“
(„Spalte 1“
„Datentyp_für_Spalte1“,
„Spalte 2“
„Datentyp_für_Spalte2“,
... )
Zur Erstellung einer Kundentabelle
würden wir also eingeben:
–
•
WI
CREATE TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)
•
ALTER TABLE "Tabellen_Name“ [Alter Spezifikation]
•
[Alter Spezifikation] hängt von der Art der
gewünschten Änderung ab. Kernbeispiele:
–
–
–
–
•
Spalte hinzufügen:
ADD "Spalte 1" "Datentyp_für_Spalte1“
Spalte löschen: DROP "Spalte 1“
Spaltenname ändern:
CHANGE "alter Spaltenname" "neuer Spaltenname"
"Datentyp für neuen Spaltennamen“
Datentyp einer Spalte ändern:
MODIFY "Spalte 1" "neuer Datentyp"
ALTER TABLE customer add Gender char(1)
Ergänzung um Information zu
Schlüsseln möglich
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
55
Structured-Query-Language (SQL)
DDL Referentielle Integrität: Eigene und Fremdschlüssel
•
MySQL:
CREATE TABLE Customer
(SID integer,
Last_Name varchar(30),
First_Name varchar(30),
PRIMARY KEY (SID));
•
Oracle:
CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));
SQL Server:
•
CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));
•
•
•
•
WI
MySQL:
CREATE TABLE ORDERS
(Order_ID integer,
Order_Date date,
Customer_SID integer,
Amount double,
Primary Key (Order_ID),
Foreign Key (Customer_SID) references CUSTOMER(SID));
Oracle:
CREATE TABLE ORDERS
(Order_ID integer primary key,
Order_Date date,
Customer_SID integer references CUSTOMER(SID),
Amount double);
SQL Server:
CREATE TABLE ORDERS
(Order_ID integer primary key,
Order_Date datetime,
Customer_SID integer references CUSTOMER(SID),
Amount double);
Unique und (NOT) NULL als Spaltenmodifier
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
56
Structured-Query-Language (SQL)
Datentypen
• Datentypen
–
–
–
–
–
–
character (n), char (n)
character varying (n), varchar (n)
numeric (p,s), integer
blob oder raw für sehr große binäre Daten
clob für sehr große String-Attribute
date für Datumsangaben
• Anlegen einer Tabelle
CREATE TABLE Professoren
(PersNr
Name
Rang
WI
integer not null,
varchar (30) not null
character (2));
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
57
Structured-Query-Language (SQL)
CREATE TABLE Beispiel in SQLite
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
58
Structured-Query-Language (SQL)
Tabelle löschen versus Inhalte löschen
• DROP TABLE "Tabellen_Name“
– Löscht die gesamte Tabelle aus der Datenbank
– DDL
• TRUNCATE TABLE "Tabellen_Name“
– Leert die Tabelle von Inhalten
– Struktur bleibt erhalten!
– DML
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
59
Structured-Query-Language (SQL)
CREATE VIEW
•
CREATE VIEW "SICHT_NAME" AS "SQLAnweisung"
•
CREATE VIEW V_Customer
AS SELECT First_Name, Last_Name, Country
FROM Customer
•
CREATE VIEW V_REGION_SALES
AS SELECT A1.region_name REGION,
SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
WHERE A1.store_name = A2.store_name
GROUP BY A1.region_name
Views lassen sich an allen Stellen einsetzen, an
denen Tabellen erwartet werden. Sie werden
zur Laufzeit aktualisiert
•
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
60
Structured-Query-Language (SQL)
Elemente der Data Manipulation Language (DML)
•
•
•
•
INSERT INTO
UPDATE
DELETE
SELECT
• Details zu den Befehlen der DML auf den Folgeseiten!
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
61
Structured-Query-Language (SQL)
INSERT INTO
•
INSERT INTO "Tabellen_Name"
("Spalte1", "Spalte2", ...) VALUES
("Wert1", "Wert2", ...)
•
INSERT INTO Store_Information
(store_name, Sales, Date) VALUES
('Los Angeles', 900, '10.Jan.1999')
WI
•
INSERT INTO Store_Information (store_name, Sales,
Date) SELECT store_name, Sales, Date FROM
Sales_Information WHERE Year(Date) = 1998
•
!! Das ist ein Befehl, der einen sog. „materialized view“
generiert!!
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
62
Structured-Query-Language (SQL)
Fortführung Universitätsbeispiel
•
•
INSERT INTO hören
SELECT MatrNr, VorlNr
FROM Studenten, Vorlesungen
WHERE Titel= `Logik‘ ;
INSERT INTO Studenten (MatrNr,
Name)
VALUES (28121, `Archimedes‘);
MatrNr
Studenten
Name
Semester
…
…
29120 Theophrastos
…
2
29555 Feuerbach
28121 Archimedes
2
NULL
Null-Wert!!
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
63
Structured-Query-Language (SQL)
DML-UPDATE / DELETE
•
•
•
WI
UPDATE "Tabellen_Name"
SET "Spalte1" = [Wert]
WHERE {Bedingung}
UPDATE Store_Information
SET Sales = 500
WHERE store_name = "Los
Angeles"
AND Date = "08.Jan.1999“
UPDATE "Tabellen_Name"
SET Spalte1 = [Wert1], Spalte2 =
[Wert2]
WHERE {Bedingung}
•
DELETE FROM "Tabellen_Name"
WHERE {Bedingung}
•
DELETE FROM Store_Information
WHERE store_name = "Los Angeles“
•
Achtung: Auf SQL-Ebene gibt es kein
UNDO !
•
Updates generieren aus der alten Tabelle
Änderungsdatensätze, die nach Ausführen aller
Änderungen eingearbeitet werden
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
64
Structured-Query-Language (SQL)
Fortführung Universitätsbeispiel
• Löschen von Tupeln
DELETE FROM Studenten
WHERE Semester > 13;
• Verändern von Tupeln
UPDATE Studenten
SET Semester= Semester + 1;
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
65
Structured-Query-Language (SQL)
DML - SELECT
• Grundlogik des SELECT Befehls:
Aus einer oder mehrerer Tabelle entsteht durch ein SELECT
Statement immer eine Tabelle, die u.U. in weitergehenden SQL
Statements verwendet werden kann
Tabelle
WI
Tabelle
…
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
SELECT
über
Tabelle(n)
Ergebnis
Tabelle
66
Structured-Query-Language (SQL)
Grundelemente des SELECT (Details siehe Folgefolien)
•
Quelle: Tupelmenge
•
– Einzeltabelle oder
– JOIN über mehrere Tabellen
•
•
•
•
•
–
–
–
–
•
Union ‡
Union all
Intersect ‡
Minus (=Difference) ‡
(Division)
Projektion: Spaltenauswahl
WHERE
AND OR BETWEEN
DISTINCT
TOP n
Gruppierung
– Aggregat-Funktionen
Mengentheoretische
Operatoren
–
–
–
–
•
INNER JOIN
OUTER JOIN
FULL JOIN
…
Selektion
• count, sum, avg, max, min
– Selektion auf das Aggregat („HAVING“)
•
„Utilities“
– Sortieren, Berechnen, Alias-Namen für
Tabellen und Spalten
– Funktionen
•
NULLS
– SELECT «spaltenliste»
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
‡ stets mit implizitem „distinct“
67
Das Relationenmodell
Fortführung Universitätsbeispiel
hören
Studenten
Semester MatrNr VorlNr
MatrNr
Name
24002
Xenokrates
18
25403
Jonas
12
26120
Fichte
10
26830
Aristoxenos
8
26120 5001
5001
Grundzüge
4
2137
27550 5001
5041
Ethik
4
2125
27550 4052
5043
Erkenntnistheorie
3
2126
28106 5041
5049
Mäeutik
2
2125
28106 5052
4052
Logik
4
2125
5052 Wissenschaftstheorie
3
2126
5216
Bioethik
2
2126
5259
Der Wiener Kreis
2
2133
PerslNr
Name
Fachgebiet
Boss
5022 Glaube und Wissen
2
2134
3002
Platon
Ideenlehre
2125
4630
4
2137
3003
Aristoteles
Syllogistik
2125
Sprachtheorie
2126
28106
3
28106 5216
29120 Theophrastos
2
28106 5259
29555
2
29120 5001
SWS gelesenVon
Vorgänger Nachfolger PersNr
29120 5041
prüfen
29120 5049
MatrNr VorlNr PersNr Note
28106 5001
2126
1
25403 5041
2125
2
27550 4630
2137
2
WI
Professoren
Titel
6
Feuerbach
voraussetzen
VorlNr
27550 Schopenhauer
Carnap
Vorlesungen
29555 5022
Die 3 Kritiken
25403 5022
Beispiel entnommen aus
http://www3.in.tum.de/research/publications/books/DBMSeinf/EIS_4_Auflage/Kapitel4.pps
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
Name
Rang Raum
5001
5041
2125
Sokrates
C4
226
5001
5043
2126
Russel
C4
232
5001
5049
2127 Kopernikus C3
310
5041
5216
2133
C3
52
5043
5052
2134 Augustinus C3
309
5041
5052
2136
Curie
C4
36
5052
5259
2137
Kant
C4
7
Popper
Assistenten
3004 Wittgenstein
3005
Rhetikus
Planetenbewegung 2127
3006
Newton
Keplersche Gesetze 2127
3007
Spinoza
Gott und Natur
2126
68
Structured-Query-Language (SQL)
Eine Einfache SQL Anfrage über eine Tabelle
• Ziel: “Selektiere die Personalnummer und den Namen der Professoren mit Rang
C4”
• Lösung:
PersNr Name
SELECT PersNr, Name
FROM Professoren
WHERE Rang= ´C4´;
WI
2125 Sokrates
2126
Russel
2136
Curie
2137
Kant
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
69
Structured-Query-Language (SQL)
Eine Einfache SQL Anfrage über eine Tabelle mit Sortierung der Ergebnisse
• Ziel: Selektiere die Personalnummer, den Namen und den Rang aller
Professoren und ordne die Ergebnisse absteigend nach dem Rang sowie
anschließend aufsteigend nach dem Name
PersNr
Name
• Lösung
SELECT PersNr, Name, Rang
FROM Professoren
ORDER BY Rang desc, Name asc;
2136
Curie
C4
2137
Kant
C4
2126
Russel
C4
2125
Sokrates
C4
2134
Augustinus C3
2127
Kopernikus C3
2133
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
Rang
Popper
C3
70
Structured-Query-Language (SQL)
Duplikateleminierung
• Ziel: Zeige alle vorhandenen Ränge von Professoren
• Lösung:
Rang
SELECT DISTINCT Rang
FROM Professoren
C3
C4
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
71
Structured-Query-Language (SQL)
Fortführung Universitätsbeispiel
Studenten
hören
MatrNr
Name
Semester MatrNr VorlNr
24002
Xenokrates
18
25403
Jonas
26120
26830
Vorlesungen
voraussetzen
VorlNr
Titel
26120 5001
5001
Grundzüge
4
2137
12
27550 5001
5041
Ethik
4
2125
Fichte
10
27550 4052
5043
Erkenntnistheorie
3
2126
Aristoxenos
8
28106 5041
5049
Mäeutik
2
2125
27550 Schopenhauer
6
28106 5052
4052
Logik
4
2125
28106
3
28106 5216
5052 Wissenschaftstheorie 3
2126
29120 Theophrastos
2
28106 5259
29555
2
Carnap
Feuerbach
29120 5001
29120 5041
prüfen
29120 5049
MatrNr VorlNr PersNr Note
29555 5022
28106 5001 2126
1
25403 5022
25403 5041 2125
2
27550 4630 2137
2
WI
Professoren
SWS gelesenVon Vorgänger Nachfolger PersNr
Name
Rang Raum
5001
5041
2125
Sokrates
C4
226
5001
5043
2126
Russel
C4
232
5001
5049
2127 Kopernikus C3
310
5041
5216
2133
C3
52
5043
5052
2134 Augustinus C3
309
5041
5052
2136
Curie
C4
36
5052
5259
2137
Kant
C4
7
Popper
5216
Bioethik
2
2126
5259
Der Wiener Kreis
2
2133
PerslNr
Name
Fachgebiet
Boss
5022 Glaube und Wissen
2
2134
3002
Platon
Ideenlehre
2125
4630
4
2137
3003
Aristoteles
Syllogistik
2125
Sprachtheorie
2126
Die 3 Kritiken
Beispiel entnommen aus
http://www3.in.tum.de/research/publications/books/DBMSeinf/EIS_4_Auflage/Kapitel4.pps
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
Assistenten
3004 Wittgenstein
3005
Rhetikus
Planetenbewegung 2127
3006
Newton
Keplersche Gesetze 2127
3007
Spinoza
Gott und Natur
2126
72
Structured-Query-Language (SQL)
Anfragen über mehrere Relationen
• Ziel: Welcher Professor liest "Mäeutik"?
• Lösung:
SELECT Name, Titel
FROM Professoren, Vorlesungen
WHERE PersNr = gelesenVon and Titel = `Mäeutik‘ ;
• Anfrage in Relationen-Algebra
∏
WI
Name, Titel
(σ
PersNr = gelesenVon ∧ Titel = ' Mäeutik'
(Professoren × Vorlesungen))
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
73
Structured-Query-Language (SQL)
Fortführung des Beispiels für Anfragen über mehrere Relationen
Professoren
PersNr Name Rang Raum VorlNr
PersNr Name Rang Raum
2125 Sokrates C4
226
2126
Russel
C4
232
…
…
…
…
2137
Kant
C4
7
Titel
5001
Grundzüge
4
2137
5041
Ethik
4
2125
…
…
…
…
5049
Mäeutik
2
2125
…
…
…
…
4
2137
4630 Die 3 Kritiken
226
5001
Grundzüge
4
2137
2125 Sokrates C4
226
5041
Ethik
4
2125
…
…
…
…
…
226
5049
Mäeutik
2
2125
…
…
…
2125 Sokrates C4
VorlNr
…
…
…
…
…
…
…
…
2126
Russel
C4
232
5001
Grundzüge
4
2137
2126
Russel
C4
232
5041
Ethik
4
2125
…
…
…
…
2137
Kant
C4
7
4
2137
SWS gelesenVon
4630 Die 3 Kritiken
Auswahl
PersNr
Name
Rang
2125
Sokrates
C4
Raum VorlNr
226
Titel
5049 Mäeutik
SWS gelesen Von
2
2125
Name
Projektion
WI
SWS gelesenVon
2125 Sokrates C4
Verknüpfung
Vorlesungen
Titel
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
Titel
Sokrates Mäeutik
74
Structured-Query-Language (SQL)
Data Manipulation Language (DML) – Varianten von JOINS
• JOINS werden benötigt für Anfragen über mehrere Relationen
(Tabellen)
• Die Art des JOINS bestimmt wie die Zeilen aus den Relationen
miteinander verknüpft werden!
• CROSS JOIN
– Der CROSS JOIN (auch als Kartesisches Produkt oder Kreuzprodukt
bezeichnet)verbindet jede Zeile der ersten Tabelle mit jeder Zeile der
zweiten Tabelle. Die Ergebnistabelle eines Kreuzproduktes kann sehr
groß werden und ist häufig nutzlos.
– SELECT * FROM TabelleA CROSS JOIN TabelleB
– SELECT * FROM TabelleA, Tabelle B
Entnommen aus http://glossar.hs-augsburg.de/Verbundoperatoren_%28Join%29
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
75
Structured-Query-Language (SQL)
Data Manipulation Language (DML) – Varianten von JOINS
• INNER JOIN
– Der INNER JOIN verbindet Datensätze aus zwei Tabellen, welche in
beiden Tabellen denselben Werte enthalten. Die Spalten die in beiden
Tabellen verglichen werden sollen, muss explizit angegeben werden.
– SELECT * FROM TabelleA INNER JOIN TabelleB ON TabelleA.Index
= TabelleB.Index
Entnommen aus http://glossar.hs-augsburg.de/Verbundoperatoren_%28Join%29
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
76
Structured-Query-Language (SQL)
Data Manipulation Language (DML) – Varianten von JOINS
• NATURAL JOIN
– Der NATURAL JOIN verknüpft die beiden Tabellen über die
Gleichheit der Felderwerte in Spalten mit gleichem Namen.
Spalten mit gleichem Namen werden im Ergebnis nur einmal
angezeigt. Haben die Tabellen keine Spalten mit gleichem Namen,
wird der NATURAL JOIN automatisch zum CROSS JOIN
SELECT * FROM TabelleA NATURAL JOIN TabelleB
Entnommen aus http://glossar.hs-augsburg.de/Verbundoperatoren_%28Join%29
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
77
Structured-Query-Language (SQL)
Data Manipulation Language (DML) – Varianten von JOINS
•
LEFT JOIN
– Der Left Join (auch Left Outer Join genannt) erstellt eine so genannte linke
Inklusionsverknüpfung. Diese schließt alle Datensätze aus der ersten (linken) Tabelle
ein, auch wenn keine entsprechenden Werte für die Datensätze in der zweiten
(rechten) Tabelle existieren. Die zu vergleichenden Spalten müssen explizit Angegeben
werden.
–
•
SELECT * FROM TabelleA LEFT JOIN TabelleB ON TabelleA.Index = TabelleB.Index
RIGHT JOIN
– Der Right Join (auch Right Outer Join genannt) erstellt eine so genannte rechte
Inklusionsverknüpfung. Diese schließt alle Datensätze aus der zweiten (rechten)
Tabelle ein, auch wenn keine entsprechenden Werte für die Datensätze in der ersten
(linken) Tabelle existieren. Die zu vergleichenden Spalten müssen explizit Angegeben
werden.
–
SELECT * FROM TabelleA RIGHT JOIN TabelleB ON TabelleA.Index = TabelleB.Index
Entnommen aus http://glossar.hs-augsburg.de/Verbundoperatoren_%28Join%29
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
78
Structured-Query-Language (SQL)
Data Manipulation Language (DML) – Varianten von JOINS
•
FULL JOIN
–
•
UNION JOIN
–
•
Der Union Join nimmt die Datensätze beider Tabellen auf und gibt sie in einer Tabelle aus. Der Union Join steht nicht
immer zur Verfügung, da er zum SQL 92 Intermediate Level gehört. In PostgreSQL steht der Union Join nicht zur
Verfügung.
SELF JOIN
–
•
Der Full Join (auch Full Outer Join genannt) ist eine Kombination von Left Join und Right Join. Die zu vergleichenden
Spalten müssen explizit Angegeben werden.
Der Self Join dient dazu, um in bestimmten Situationen einen Verbund innerhalb einer einzigen Tabelle bilden zu
können. Dazu muss man der Tabelle zwei verschiedene Aliasnamen geben. Self Joins können mit allen Joins
durchgeführt werden. Ein Beispiel für den Gebrauch eines Self Joins ist zum Beispiel ein Vergleich innerhalb einer
Tabelle.
THETA JOIN
–
Der Theta Join ist eine Verallgemeinerung des Inner Join. Beim Inner Join wird die Gleichheit des Inhalts zweier
Attribute verglichen und beim Theta Join wird der Inhalt der Attribute x und y mit einer beliebigen Formel, zum
Beispiel "<", "<=", ">", ">=", "<>" verglichen.
Entnommen aus http://glossar.hs-augsburg.de/Verbundoperatoren_%28Join%29
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
79
Structured-Query-Language (SQL)
Anfragen über mehrere Relationen
• Welche Studenten hören welche Vorlesungen?
select Name, Titel
from Studenten, hören, Vorlesungen
where Studenten.MatrNr = hören.MatrNr and
hören.VorlNr = Vorlesungen.VorlNr;
• Alternativ:
select s.Name, v.Titel
from Studenten s, hören h, Vorlesungen v
where s. MatrNr = h. MatrNr and
h.VorlNr = v.VorlNr
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
80
Structured-Query-Language (SQL)
• Mengenoperationen UNION, INTERSECT, MINUS
( select Name
from Assistenten )
union
( select Name
from Professoren);
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
81
Aggregationen
• Aggregatfunktionen avg, max, min, count, sum
select avg (Semester)
from Studenten;
select gelesenVon, sum (SWS)
from Vorlesungen
group by gelesenVon;
select gelesenVon, Name, sum (SWS)
from Vorlesungen, Professoren
where gelesenVon = PersNr and Rang = ´C4´
group by gelesenVon, Name
having avg (SWS) >= 3;
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
Siehe hierzu die
Detailerläuterung
auf den Folgefolien
82
Aggregationen
• SQL erzeugt pro Gruppe ein Ergebnistupel
• Deshalb müssen alle in der select-Klausel aufgeführten Attribute - außer
den aggregierten – auch in der group by-Klausel aufgeführt werden
• Nur so kann SQL sicherstellen, dass sich das Attribut nicht innerhalb der
Gruppe ändert
select gelesenVon, Name, sum (SWS)
from Vorlesungen inner join Professoren on gelesenVon = PersNr
where Rang = ´C4´
group by gelesenVon, Name
having avg (SWS) >= 3;
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
83
Aggregationen
Fortführung Universitätsbeispiel
hören
Studenten
Vorlesungen
voraussetzen
Semester MatrNr VorlNr
VorlNr
Titel
18
26120 5001
5001
Grundzüge
4
2137
5001
5041
2125
Sokrates
C4
226
Jonas
12
27550 5001
5041
Ethik
4
2125
5001
5043
2126
Russel
C4
232
26120
Fichte
10
27550 4052
5043
Erkenntnistheorie
3
2126
5001
5049
2127 Kopernikus C3
310
26830
Aristoxenos
8
28106 5041
5049
Mäeutik
2
2125
5041
5216
6
28106 5052
4052
Logik
4
2125
5043
5052
2133
C3
52
28106 5216
5052 Wissenschaftstheorie
3
2126
5041
5052
2134 Augustinus C3
309
5052
5259
5216
Bioethik
2
2126
36
5259
Der Wiener Kreis
2
2133
2136
Curie
C4
Assistenten
2137
Kant
C4
Name
Fachgebiet
7
Boss
2
2134
Platon
Ideenlehre
2125
Syllogistik
2125
Sprachtheorie
2126
MatrNr
Name
24002
Xenokrates
25403
27550 Schopenhauer
28106
Carnap
3
29120 Theophrastos
29555
2
Feuerbach
prüfen
2
SWS gelesenVon
Professoren
Vorgänger Nachfolger PersNr
28106 5259
29120 5001
29120 5041
29120 5049
5022 Glaube und Wissen
MatrNr VorlNr PersNr Note
28106
3002
29555 5022
4630
DieManagement"
3 Kritiken
4
2137
Vorlesung "Data
and Knowledge
(MW31.6)
| Prof.
Dr. J.
3003 Aristoteles
25403
5022
5001 2126
Ruhland1| SS2015
Beispiel entnommen aus
25403 5041
2125
2
27550 4630
2137
2
WI
PerslNr
http://www3.in.tum.de/research/publications/books/DBMSeinf/EIS_4_Auflage/Kapitel4.pps
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
3004 Wittgenstein
3005
Rhetikus
Name
Rang Raum
Popper
Planetenbewegung 2127
84
Aggregationen
Fortführung Universitätsbeispiel
VorlNr
Titel
5001
Grundzüge
4
2137
2137
5041
Ethik
4
2125
2125 Sokrates C4
226
5043
Erkenntnistheorie
3
2126
2126
C4
232
5049
Mäeutik
2
2125
2125 Sokrates C4
226
4052
Logik
4
2125
2125 Sokrates C4
226
5052 Wissenschaftstheorie
3
2126
2126
Russel
C4
232
5216
Bioethik
2
2126
2126
Russel
C4
232
4630
Die 3 Kritiken
4
2137
2137
Kant
C4
7
WI
SWS gelesen Von PersNr Name Rang Raum
Kant
Russel
C4
7
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
85
Aggregationen
Fortführung Universitätsbeispiel
Vorlesung x Professoren
VorlNr
Titel
5001
Grundzüge
4
2137
2125 Sokrates C4
226
5041
Ethik
4
2125
2125 Sokrates C4
226
...
...
...
...
...
...
...
...
4
2137
2137
Kant
C4
7
4630 Die 3 Kritiken
SWS gelesen Von PersNr Name Rang Raum
Aggregation und
Having Bedingung
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
86
Aggregationen
Fortführung Universitätsbeispiel
gelesenVon Name sum (SWS)
WI
2125
Sokrates
10
2137
Kant
8
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
87
Fortgeschrittene Elemente der Abfrage
•
•
„LIKE“ mit _ und % Wildcards
NULL Behandlung  Folgeseiten
– NVL(expr1, expr2)
•
Ersetzt in expr1 möglicherweise vorkommende NULL-Werte durch expr2
Siehe Folgeseite
– COALESCE (expr1,expr 2… expr n) gibt ersten NON_NULL zurück
• SELECT ProductNumber,COALESCE(Class, Name, ProductNumber) AS
FirstNotNull FROM Production.Product ;
•
WI
Subqueries  Folgeseiten
– Uncorrelated
– Correlated
– Funktionen mit besonderer Bedeutung für Subqueries
• EXISTS
• IN
• Ein SELECT darf überall stehen, wo eine Table erwartet wird; und außerdem
ein select, das sicher nur einen Wert ausgibt, überall, wo ein Wert erwartet wird
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
88
NULL- Werte
•
•
•
•
Steht für: „unbekannter Wert
…wird vielleicht später nachgereicht“
Nullwerte können auch im Zuge der Anfrageauswertung entstehen (Bsp. äußere Joins)
Und sind als Fremdschlüssel bei 1:n (c) Beziehungen häufig
•
manchmal sehr überraschende Anfrageergebnisse, wenn Nullwerte vorkommen
select count (*)
from Studenten
where Semester < 13 or Semester > =13
•
Wenn es Studenten gibt, deren Semester-Attribut den Wert null hat, werden diese nicht
mitgezählt
Der Grund liegt in folgenden Regeln für den Umgang mit Null-Werten begründet:
•
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
89
Auswertung bei NULL-Werten
1. In arithmetischen Ausdrücken werden Nullwerte propagiert, d.h. sobald ein Operand null
ist, wird auch das Ergebnis null. Dementsprechend wird z.B.
null + 1 zu null ausgewertet
aber auch null * 0 wird zu null ausgewertet.
2.
SQL hat eine dreiwertige Logik, die nicht nur true und false kennt, sondern auch einen
dritten Wert unknown. Diesen Wert liefern Vergleichsoperationen zurück, wenn
mindestens eines ihrer Argumente null ist. Beispielsweise wertet SQL das Prädikat
(PersNr=...) immer zu unknown aus, wenn die PersNr des betreffenden Tupels den Wert
null hat.
3. Logische Ausdrücke werden nach den folgenden Tabellen berechnet:
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
90
not
true
false
unknown unknown
false
and
true
unknown
false
true
true
unknown
false
unknown
unknown
unknown
false
false
false
false
false
true
or
true
unknown
false
true
true
true
true
unknown
true
unknown
unknown
false
true
unknown
false
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J.
Ruhland | SS2015
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
91
Auswertung bei NULL-Werten
Diese Berechnungsvorschriften sind recht intuitiv. Unknown or true wird z.B. zu
true - die Disjunktion ist mit dem true-Wert des rechten Arguments immer
erfüllt, unabhängig von der Belegung des linken Arguments. Analog ist unknown
and false automatisch false - keine Belegung des linken Arguments könnte die
Konjunktion mehr erfüllen.
4. In einer where-Bedingung werden nur Tupel weitergereicht, für die die
Bedingung true ist. Insbesondere werden Tupel, für die die Bedingung zu
unknown auswertet, nicht ins Ergebnis aufgenommen.
5. Bei einer Gruppierung wird null als ein eigenständiger Wert aufgefasst und in
eine eigene Gruppe eingeordnet.
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
92
Besonderheiten bei NULL und AVG
•
•
Die Funktion AVG zieht in die Berechnung des Durchschnittes lediglich die Zeilen ein, die nicht NULL
in der entsprechenden Spalte sind.
Dies führt zu folgendem Ergebnis:
select avg(comm) from emp;
–
•
•
•
Es gibt jedoch nur 4 Datensätze, in denen die Spalte Comm nicht NULL ist.
In diesen Datensätzen ist die Spalte Comm 300, 500, 1400 und 0. Das Ergebnis von 550 ergibt sich
demnach aus (300+500+1400+0) / 4.
Sollen jedoch auch die Datensätze einbezogen werden, in denen Comm NULL ist, so können Sie
diese mit der Funktion NVL durch eine 0 ersetzen lassen.
–
•
WI
AVG(COMM) --------- 550
select avg(nvl(comm,0)) from emp;
AVG(NVL(COMM,0)) ---------------- 157,142857
Durch die Funktion NVL(comm,0) werden NULL-Werte in der Spalte comm als 0 interpretiert.
Demnach wird nun (300+500+1400+0+0+0+0+0+0+0+0+0+0+0) / 14 gerechnet.
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
93
Geschachtelte Anfragen
• Unteranfrage in der where-Klausel
• Welche Prüfungen sind besser als durchschnittlich verlaufen?
select *
from prüfen
where Note < ( select avg (Note)
from prüfen );
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
94
Geschachtelte Anfragen
• Unteranfrage in der select-Klausel
• Für jedes Ergebnistupel wird die Unteranfrage ausgeführt
• Man beachte, dass hier die Unteranfrage korreliert ist (greift auf
Attribute der umschließenden Anfrage zu)
select PersNr, Name, ( select sum (SWS) as Lehrbelastung
from Vorlesungen
where gelesenVon=PersNr )
from Professoren;
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
95
Geschachtelte Anfragen
• unkorrelierte Formulierung
select s.*
from Studenten s
where s.GebDatum <
(select max (p.GebDatum)
from Professoren p);
• Vorteil: Unteranfrageergebnis kann materialisiert werden
• Unteranfrage braucht nur einmal ausgewertet zu werden
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
96
Korreliert versus Unkorreliert
•
Korreliert: wer ist gebildeter als der
Durchschnitt seiner Abteilung ?
SELECT EMPNO, LASTNAME,
WORKDEPT, EDLEVEL
FROM EMPLOYEE X
WHERE EDLEVEL >
(SELECT AVG(EDLEVEL)
FROM EMPLOYEE Y
WHERE Y.WORKDEPT =
X.WORKDEPT)
•
Unkorreliert: wer ist gebildeter als der
Durchschnitt der Abteilungen c11,d13
SELECT EMPNO, LASTNAME,
WORKDEPT, EDLEVEL
FROM EMPLOYEE X
WHERE EDLEVEL >
(SELECT AVG(EDLEVEL)
FROM EMPLOYEE Y
WHERE Y.WORKDEPT IN (‘c11‘,
‘d13‘))
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
97
Weitere Abfragen mit Unterabfragen
Ein Select darf überall vorkommen, wo eine Tabelle erwartet wird, aber Problem:
Ich möchte, dass ein WERT kleiner ist als alle EINZELWERTE, die eine Unterabfrage
liefert, was eine Liste sein kann
Select … where alter > select avg(alter) from TblMitarbeiter

Select … where alter > ANY(select alter from TblMitarbeiter)
Avg liefert eine Zahl  any kann gegen eine Liste vergleichen
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
98
Weitere Abfrageelemente insbes. Bei Unterabfragen
select Name
from Professoren
where PersNr not in ( select gelesenVon
from Vorlesungen );
select Name
from Studenten
where Semester > = all ( select Semester
from Studenten );
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
99
Weitere Abfragen mit Unterabfragen
• Existenzquantor: exists
select Name
from Professoren
where not exists ( select *
from Vorlesungen
where gelesen Von = PersNr );
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
100
Verwertung der Ergebnisse einer Unterabfrage
select tmp.MatrNr, tmp.Name, tmp.VorlAnzahl
from (select s.MatrNr, s.Name, count(*) as VorlAnzahl
from Studenten s, hören h
where s.MatrNr=h.MatrNr
group by s.MatrNr, s.Name) tmp
where tmp.VorlAnzahl > 2;
WI
MatrNr
Name
VorlAnzahl
28106
Carnap
4
29120
Theophrastos
3
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
101
„echte“ Allquantifizierung z.B. mittels „count“
•
•
Allquantifizierung im Sinne eines Pendants zum „exists“ kann immer durch eine countAggregation ausgedrückt werden
Wir betrachten dazu eine Anfrage, in der wir die (MatrNr der) Studenten ermitteln
wollen, die alle Vorlesungen hören:
select h.MatrNr
from hören h
group by h.MatrNr
having count (*) = (select count (*) from Vorlesungen);
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
102
Herausforderung und Aufgabe
• Wie formuliert man die komplexere Anfrage: Wer hat alle
vierstündigen Vorlesungen gehört
• Grundidee besteht darin, vorher durch einen JOIN die
Studenten/Vorlesungs-Paare einzuschränken und danach das
Zählen durchzuführen
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
103
Wer hat alle vierstündigen Vorlesungen gehört
STRATEGIE
• Count der 4-Stünder (†) versus Count der 4-Stünder pro Student ( „group by …. Having …“,
(‡))
• Brauchen für † nur Tabelle Vorlesungen
• Brauchen für ‡ die Tabellen Vorlesungen hören (tabelle Studenten ggf für die Namen der
Studenten)
REALISIERUNG
• † select count(*) from vorlesungen where SWS = 4
• ‡ select H.MatrNr, count(*)
from hören AS H inner join Vorlesungen AS V on H.VorlNr=V.VorlNR
where SWS = 4
group by H.MatrNR
having count(*) = † (select count(*) from vorlesungen where SWS = 4)
Das ist eine uncorrelated sub-Query
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
104
Übungsfrage: Länderdatenbank und Anrainer
•
•
•
WI
Welche Länder sind
Binnenstaaten?
Welche Länder sind doppelt
eingeschlossen „doppelter
Binnenstaat“ = auch seine
Nachbarn haben keine
„InternationalWaters“ als
Grenze? Z.B. Liechtenstein
Welches Land hat Nachbarn, die
alle jeweils mehr Nachbarn
haben als wir. zB Andorra,
Lesotho,..
•
Table LÄNDER (LandID, NachbarId, Länge der Grenze)
•
NachbarId = IW = „dummyland international Waters“
•
Annahme: Wir haben alle Grenzen genau 2 Mal drin
DtAu 344km und AuDt 344 km.
•
Sollte das nicht der Fall sein: baue eine „union“-view
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
105
Lösung: Binnenländer
Select L.Landid from Länder AS L
MINUS --- als Mengenoperation beinhaltet das ein SORT und DISTINCT
Select L.Landid from Länder AS L where
L.Nachbarid = ‘IW‘
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
106
Lösung: doppelte Binnenländer
Select L.Landid from Länder AS L where
not exists (select * from Länder as own where own.Landid = L.Landid and
own.nachbarid = ‚IW‘ ) ‡
AND
not exists (select * from Länder as neighb where
neighb.Landid = L.nachbarid and neighb.nachbarid = ‚IW‘ ) †
ALTERNATIVE
Create view binnenländer as select * from Länder where ‡
Select * from binnenländer where †
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
107
Lösung: Anrainer mit vielen Nachbarn
Create view ANRAINER as select landid, count(*) as anzneigh from LÄNDER group
by landid
Select landid
from anrainer AS A INNER JOIN länder AS L on A.landId = L.landid
where anzneigh > ALL (select (n.anzneigh from anrainer as n
where n.landid = L.nachbarId ) )
Alternativen mit count(*) sind möglich
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
108
Recursive Queries mit with
•
Problem:
– Stücklisten, Organigramme, Graphen u.ä. Konstrukte gestatten im Prinzip eine
undefinierte Anzahl von Rekursions- und Join-Operationen
– Das ist in SQL zu beschreiben
– … und ggf. die Tiefe erfassen und beschränken
•
Lösung (im Standard-SQL)
– Rekursion mit Verankerung und Rekursionsschritt
– Wobei das Ergebnis wie eine virtuelle Tabelle beschrieben wird
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
109
Referenzen
•
•
•
•
•
•
•
•
WI
msdn.microsoft.com/en-us/magazine/cc794278.aspx Excellent on hierachy traversal
www.mssqltips.com/sqlservertip/1520/recursive-queries-using-common-table-expressions-cte-insql-server/ Into to CTE Usage
www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_2186-Is-there-the-bestway-to-find-a-sub-tree-in-a-self-referencing-table.html on All Tree Handling In SQL
archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=RecursiveCTE CTE Syntax
http://www.ibm.com/developerworks/data/library/techarticle/dm-0510rielau/index.html Very
Advanced Recursive
http://www.ibm.com/developerworks/data/library/techarticle/0307steinbach/0307steinbach.html
the Maths Of Recursion
http://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL und Referenzen
http://www.youtube.com/watch?v=AY2ztQ-khHM Movie on RecursiveSQL
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
110
Recursive Query im Einsatz
(vgl http://www.ibm.com/developerworks/data/library/techarticle/0307steinbach/0307steinbach.html) CTE Syntax
DROP TABLE #ProjectMemberDetails
-- create temporary table called ProjectMemberDetails
CREATE TABLE #ProjectMemberDetails
(
ID int Identity(1,1) PRIMARY KEY,
TeamMemNm varchar(100),
Designation varchar(50),
ManagerID int NULL
)
-- Insert some sample records in the ProjectMemberDetails table
INSERT INTO #ProjectMemberDetails VALUES('Tim',‘CEO',NULL);
INSERT INTO #ProjectMemberDetails VALUES('Kathy','Sr. Project Manager',1);
INSERT INTO #ProjectMemberDetails VALUES('Levonca','Project Manager',2);
INSERT INTO #ProjectMemberDetails VALUES('Sid','Project Manager',2);
Etc ---INSERT INTO #ProjectMemberDetails VALUES('Steve','Developer',15);
INSERT INTO #ProjectMemberDetails VALUES('Rachael','Developer',15);
INSERT INTO #ProjectMemberDetails VALUES('Sally','Tester',16);
INSERT INTO #ProjectMemberDetails VALUES('Peter','Tester',16);
Das Beispiel ist didaktisch
unschön; wir kennen
„irgendwie“ den Wert des
auto-idFelds
-- continue on next slide ….
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
111
Recursive Query im Einsatz
SELECT * FROM #ProjectMemberDetails; -- only a check
-- And NOW : Use Recursive CTE to find out the Level of each ProjectMemberDetails in the project
WITH TEM_TA (ID, TeamMemberName, Designation, ManagerID, ProjectLevel)
AS
(
SELECT ID, TeamMemNm, Designation, ManagerID, 1 as ProjectLevel
From #ProjectMemberDetails WHERE ManagerID is NULL
UNION ALL --es muss an dieser Stelle praktisch IMMER union all heißen
SELECT e.ID, e.TeamMemNm, e.Designation, e.ManagerID, c.ProjectLevel + 1
FROM #ProjectMemberDetails e
INNER JOIN TEM_TA c
ON e.ManagerID = c.ID
)
SELECT ID, TeamMemberName, Designation, ManagerID, ProjectLevel
FROM TEM_TA Order BY ID -- we could also integrate other tables
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
Virtuelle Tabelle TEM_TA
kann NUR onTheFly
definiert werden
Verankerung
liefert T0
Rekursion
(dieser Teil verwendet
(auch) TEM_TA den Teil i
Und liefert aus Ti die Ti+1
Verwendung
dieser Teil verwendet (auch)
TEM_TA , die durch
Union All der Ti gebildet wurde
112
on recursion
http://de.urbandictionary.com/define.php?term=recursion
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
113
From http://msdn.microsoft.com/en-us/library/ms186243.aspx
for in_depth Analysis
A recursive CTE consists of three elements:
Invocation of the routine.
The first invocation of the recursive CTE consists of one or more
CTE_query_definitions joined by UNION ALL, UNION, EXCEPT, or
INTERSECT operators. Because these query definitions form the base
result set of the CTE structure, they are referred to as anchor members.
CTE_query_definitions are considered anchor members unless they
reference the CTE itself. All anchor-member query definitions must be
positioned before the first recursive member definition, and a UNION
ALL operator must be used to join the last anchor member with the
first recursive member.
Recursive invocation of the routine.
The recursive invocation includes one or more CTE_query_definitions
joined by UNION ALL operators that reference the CTE itself. These
query definitions are referred to as recursive members.
Note
An incorrectly composed recursive CTE may cause an infinite
loop. For example, if the recursive member query definition
returns the same values for both the parent and child columns,
an infinite loop is created. When testing the results of a
recursive query, you can limit the number of recursion levels
allowed for a specific statement by using the
MAXRECURSION hint and a value between 0 and 32,767 in
the OPTION clause of the INSERT, UPDATE, DELETE, or
SELECT statement. For more information, see Query Hints
(Transact-SQL) and WITH common_table_expression
(Transact-SQL).
Termination check.
The termination check is implicit; recursion stops when no rows are
returned from the previous invocation.
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
114
http://www.youtube.com/watch?v=AY2ztQ-khHM Movie on RecursiveSQL
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
115
Weitere Literatur zum Nachlesen
•
SQL Einführung mit MS Access Beispielen
–
•
Einführung von Jeckle
–
–
•
http://www3.in.tum.de/research/publications/books/DBMSeinf/
In allen Details SEHR ausführlicher Kurs, aber gut
–
WI
http://electures.informatik.uni-freiburg.de/portal/download/17/8479/10_SQLAggregierung-4auf1.pdf AGG, Exists,
Nested !! §§ (etwa advanced)
Datenbanksysteme eine Einführung
–
•
http://www3.in.tum.de/research/publications/books/DBMSeinf/EIS_4_Auflage/Kapitel4.pps
Tabellenausdrücke und Aggregationen
–
•
www.dbs.ifi.lmu.de/Lehre/DBS/WS-2009/Skript/Kapitel5.pdf
Beispiele zum Nachvollziehen
–
•
www.jeckle.de/vorlesung/datenbanken/script.html
auch www.jeckle.de/files/db.pdf --> Jekcle Vorlesung§§
LMU München
–
•
http://www.i4ds.ch/fileadmin/user_upload/vorlesungen/programm_und_datenban/untericht/le06-SQL.pdf
http://www.iai.uni-bonn.de/III//lehre/vorlesungen/Informationssysteme/WS02/
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
116
MERGE-Operation
(aus www.sigmod.org/publications/sigmod-record/1203/pdfs/10.industry.zemke.pdf korrigiert)
•
Matche 2 Tabellen und mache ggf. in
jeder row unterschiedliches
• Inventory(Part, Qty)
soll angepasst werden durch
• Changes(Part, Qty, Action)
wobei Action =
–
–
–
„Mod“
„Dis“
„New“
MERGE INTO Inventory AS I
USING Changes AS C
ON I.Part = C.Part
WHEN MATCHED AND C.Action = 'Mod'
THEN UPDATE SET Qty = Qty + C.Qty
WHEN MATCHED AND C.Action = 'Dis'
THEN DELETE
WHEN NOT MATCHED AND C.Action = ‚New‘
THEN INSERT VALUES (C.Part, C.Qty)
NB: es kann auch Einträge zum Ändern
von nicht-existenten Parts geben
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
118
limited FETCH -Operation
(aus www.sigmod.org/publications/sigmod-record/1203/pdfs/10.industry.zemke.pdf)
•
•
•
hole nur die „ersten“ Einträge
nach Anzahl oder %
und wenn an der Abschneidekante
„ties“ auftreten, kann man entweder
die Anzahl streng nehmen oder oder
die Grenze des Werts
SELECT Name, Salary FROM Emp ORDER BY Salary Desc
FETCH FIRST 10 {ROWS | PERCENT}
{ONLY | WITH TIES}
fetch first 10 percent with ties  alle „oberen 10%-Top-
Verdiener“, auch wenn das wg. ties mehr als 10% der Verdiener sind
die nächsten Einträge via:
SELECT Name, Salary FROM Emp ORDER BY Salary Desc
OFFSET 10 ROWS
FETCH NEXT 10 {ROWS | PERCENT}
{ONLY | WITH TIES}
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
119
Windows and Partitions
typische Probleme
• running sums
• running avgs
• lead/lags
• alles, was über die „eigene“ Row
hinausschaut
waren im SQL schwierig zu
berechnen
•
•
neues Konzept 1: PARTITION BY rechnet alle
Aggregate völlig getrennt in jeder Partition
weiteres neues Konzept 2: Window-Funktionen
definieren für jede einzelne Row in einer
Partition ihre Umgebung (z.B. 5er MittelwertFenster 2 davor und 2 dahinter)
SELECT Acctno, Transdate, SUM (Amount) OVER
(PARTITION BY Acctno
ORDER BY TransDate
ROWS BETWEEN UNBOUNDED PRECEEDING
AND CURRENT ROW)
FROM Accounts
gleitende („yearToDate“) Umsatzsumme für jeden
einzelnen Kunden. Lies: „SUM OVER PARTITION“
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
120
Windows and Partitions (Überblick)
SELECT Acctno, Transdate,
AVG (Amount) OVER
(PARTITION BY Acctno
ORDER BY TransDate
ROWS BETWEEN 3
PRECEEDING
AND 3 FOLLOWING)
FROM Accounts
WI
•
SELECT Price AS CurPrice, LAG(Price,1) OVER
(PARTITION BY ProdID ORDER BY Tstamp) AS
PrevPrice
FROM Data
•
-------- komplexere Optionen in der angegebenen
Quelle oder (besser) bei www.simpletalk.com/sql/learn-sql-server/window-functionsin-sql-server-part-2-the-frame/
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
121
Windows and Partitons and CTEs
aus http://stackoverflow.com/questions/4740748/when-to-use-common-table-expression-cte
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
122
Temporale Aspekte im SQL:2011
Teil1: „Gültigkeitsdauern“ (siehe für das Beispiel
cs.ulb.ac.be/public/_media/teaching/infoh415/tempfeaturessql2011.pdf
•
•
•
BeginnZeitpunktDerGültigkeit (per
Konvention incl.)
EndeZeitpunkt (per Konvention excl.)
Zeitpunkt des Eintrags (Kann z.B. bei
Versicherungen differieren)
SQL:2011: das sind explizit definierte und
„user-accessible“ Spalten (mit Datentyp
„Date“ oder „Timestamp“)
• für Berechnungen und Selects kann
das System als eine Art „virtuelle
Spalte“ die Dauer berechnen
UPDATES können dazu führen, dass das
System automatisch 3 neue Einträge
anlegt (siehe das Beispiel)
WI
CREATE TABLE Emp (
Eno INTEGER,
Estart DATE,
Eend DATE,
EDept INTEGER,
PERIOD FOR Eperiod (Estart, Eend)
)
INSERT INTO Emp VALUES
(22217, DATE ‚2010-01-01‘,DATE `2011-11-12` ,3)
UPDATE Emp
FOR PORTION OF Eperiod
FROM DATE `2011-02-03‘ TO DATE `2011-09-10‘
SET Edept= 4 WHERE Eno = 22217
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
123
Temporale Aspekte im SQL:2011
Teil1: „Gültigkeitsdauern“ (Fortsetzung)
cs.ulb.ac.be/public/_media/teaching/infoh415/tempfeaturessql2011.pdf
Uniqueness / Primary Key
Funktionen zum Rechnen mit Zeiträumen
(Beispiele)
es soll zB. ein Mitarbeiter zu jedem
Zeitpunkt eindeutig in 1 Abteilung
arbeiten; also sollen 2 Einträge der Art
Eno
Estart
Eend
Edept
22217
2010-01-01
2011-09-10
3
22217
2010-02-03
2011-11-12
4
ausgeschlossen sein 
ALTER TABLE Emp
ADD PRIMARY KEY (Eno,
Eperiod WITHOUT OVERLAPS )
WI
SELECT Name, Edept FROM EMP
WHERE Eno = 22217 AND
EPeriod CONTAINS DATE ‚2011-01-02‘
ODER AUCH
…
EPeriod OVERLAPS
PERIOD(DATE `2010-01-01‘ ,
DATE `2011-01-01‘ )
ODER AUCH
…
EPeriod PRECEDES
PERIOD(DATE `2010-01-01‘ ,
DATE `2011-01-01‘ )
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
124
Temporale Aspekte im SQL:2011
Teil2: System versioned tables cs.ulb.ac.be/public/_media/teaching/infoh415/tempfeaturessql2011.pdf
„Journalisierung“: Das System
dokumentiert automatisch die
Änderungen. Der Benutzer kann nur die
aktuelle Version ändern, die dadurch als
unveränderbare Kopie weiterbesteht.
Primärschlüssel und Constraints werden
nur auf der aktiven Kopie geprüft
Auf den alten Stand kann lesend
zurückgegriffen werden
… weitere Möglichkeiten; siehe z.B.
https://wiki.postgresql.org/wiki/SQL2011
Temporal
WI
CREATE TABLE Emp (
Eno INTEGER,
Sys_start TIMESTAMP(12) GENERATED ALWAYS AS ROW
START,
Sys_end TIMESTAMP(12) GENERATED ALWAYS AS ROW
END,
Ename VARCHR(30),
PERIOD FOR SYSTEM_TIME (Sys_start,Sys_end)
PRIMARY KEY (Eno)
-- und sonst nichts im Schlüssel
) WITH SYSTEM VERSIONING
SELECT Eno, Ename, Sys_start, Sys_end FROM Emp
FOR SYSTEM_TIME AS OF TIMESTAMP `2011-01-02 00:01:11‘
---- und mit Intervallen:
FOR SYSTEM_TIME BETWEEN …. AND
---- default: AS OF CURRENT_TIMESTAMP
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
125
Übersicht zu temporalen Aspekten in SQL 2011
http://en.wikipedia.org/wiki/SQL:2011
•
Time Period definitions use two standard table columns as the start and end of a named time period, with closed-open semantics.
This provides compatibility with existing data models, application code, and tools
•
•
•
Definition of application time period tables (elsewhere called valid time tables), using the PERIOD FOR annotation
Update and deletion of application time rows with automatic time period splitting
Temporal primary keys incorporating application time periods with optional non-overlapping constraints via the WITHOUT
OVERLAPS clause
Temporal referential integrity constraints for application time tables
•
•
•
•
•
WI
Application time tables are queried using regular query syntax or using new temporal predicates for time periods including
CONTAINS, OVERLAPS, EQUALS, PRECEDES, SUCCEEDS, IMMEDIATELY PRECEDES, and
IMMEDIATELY SUCCEEDS (which are modified versions of Allen’s interval relations)
Definition of system-versioned tables (elsewhere called transaction time tables), using the PERIOD FOR SYSTEM_TIME
annotation and WITH SYSTEM VERSIONING modifier. System time periods are maintained automatically. Constraints for
system-versioned tables are not required to be temporal and are only enforced on current rows
Syntax for time-sliced and sequenced queries on system time tables via the AS OF SYSTEM TIME and VERSIONS
BETWEEN SYSTEM TIME ... AND ... clauses
Application time and system versioning can be used together to provide bitemporal tables
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
126
Temporale Aspekte im SQL:2011
Teil 3: weitere Elemente
cs.ulb.ac.be/public/_media/teaching/infoh415/tempfeaturessql2011.pdf
• siehe die Quelle
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
127
Kapitel 1.4
ERWEITERTE SQL
FUNKTIONALITÄTEN
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
128
Benutzerverwaltung
• Zentral für alle DBMS
• Rollenkonzept
– Rechte werden an Rollen vergeben
– Benutzer sind (n:m) Rollen zugeteilt
• Genehmigung gewisser Aktivitäten + logging
– GRANT privilege_name
• ON object_name
• TO {user_name |PUBLIC |role_name}
• [WITH GRANT OPTION];
– CREATE ROLE verwalter;
– GRANT verwalter TO joerg, sabine, harald;
– GRANT INSERT, SELECT, UPDATE(gehalt) ON mitarbeiter
• TO verwalter WITH GRANT OPTION;
• Einschränkung mittels VIEWS und Updateable Views
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
129
Updateable Views
•
•
In besonderen Fällen, in denen das DBMS eine eindeutige Zuordnung zwischen den in
der Sicht zu ändernden Daten und einer physikalischen Tabelle, zu der sie gehören,
herstellen kann, ist ein Update möglich.
Beispiel für so eine Updateable View wäre folgende triviale Sicht:
–
•
Ein Update auf SoftwareVerkaeufe2 kann hier eindeutig
–
WI
CREATE VIEW ''SoftwareVerkaeufe2'' AS 'SELECT verkaeufe.kaeufer FROM verkaeufe
SELECT verkaeufe.kaeufer FROM verkaeufe zugeordnet werden.
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
130
Updateable Views
• Im Beispiel unten ist eine eindeutige Zuordnung nicht möglich, da produkt_id in
beiden Quellrelationen enthalten ist, ...
CREATE VIEW SoftwareVerkaeufe AS
SELECT v.kaeufer, v.verkaeufer,p.product_id
FROM produkte p, verkaeufe v
WHERE p.produkt_id = v.produkt_id -- ← Achtung!
AND p.produkt = "Software"
• ... und z. B. bei einer Löschung wie in ...
• DELETE FROM SoftwareVerkaeufe WHERE produkt_id = 123456
• ... nicht entscheidbar ist, ob Datensätze aus Produkte oder Verkaeufe gelöscht
werden sollen
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
131
Updateable Views
In welchen Situationen sind Updateable Views verboten?
Die geschilderte Anomalie entsteht generell in einer Situation, wo eine Durchführung der Änderung
nicht den Erwartungen des Benutzers entspricht oder nicht entscheidbar ist, welche Änderungen genau
durchzuführen sind. Man kann sie folgendermaßen einteilen:
• In einer Selektionssicht können Datensätze aus dem sichtbaren Bereich verschwinden, wenn ein in
der Sicht vorhandener Datensatz so geändert wird, dass er aus der Sicht herausfällt.
• In einer Projektionssicht kann eine Einfügeoperation dann problematisch werden, wenn in der
Originalrelation Felder vorhanden sind, die belegt sein müssen (NOT NULL), aber nicht in der Sicht
vorkommen, oder wenn die Sicht durch die Angabe von DISTINCT gleiche Ergebnistupel zu einem
zusammenfasst.
• In einer Verbundsicht ist nicht immer entscheidbar, auf welcher Originalrelation die Operation
auszuführen ist.
• In einer Aggregationssicht kann nicht entschieden werden, wie die Operation umzusetzen ist. Zum
Beispiel ist nicht klar, wie eine Halbierung aller Verkaufszahlen auf die Originalrelation umzusetzen
ist: Entweder kann die Hälfte der Verkäufe gelöscht oder die einzelnen Verkäufe halbiert werden.
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
132
Updateable Views
In welchen Situationen sind Updateable Views verboten?
•
•
Aus der mySQL Referenz
A view is not updatable if any of the following conditions are true:
–
–
–
–
–
the keyword DISTINCT is used in the view definition
the select list contains components other than column specifications, or contains more than one specification of the same
column
the FROM clause specifies more than one table reference or refers to a non-updatable view
the GROUP BY clause is used in the view definition
the HAVING clause is used in the view definition
•
Ähnlich DB2
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.sqlref/db2z_sql_cre
ateview.htm
•
Im Prinzip sollten Views mit 2+ Tabellen den gleichen Regeln wie Single Table Views unterliegen, sofern alle
Primär- und Fremdschlüssel im View vorkommen
Aber: Die Entscheidung, ob ein View updateable ist, wird letztlich vom DBMS getroffen
Zusätzlich muss der Benutzer / die Rolle auch noch die entsprechenden Rechte auf den View besitzen
•
•
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
133
Metadaten und Data Dictionary
im Relationalen Modell
•
•
•
•
ERD zum Aufbau „jedes“ ERD → Meta-ERD
Meta-Tabellen in einem Datenbanksystem
Ein ERD lässt sich aus den Systemtabellen (ggf. mit graphischer Position im
gezeichneten ERD) jederzeit rekonstruieren
Ausschnitte aus einem ERD lassen sich als SQL-Abfragen gegen die Meta-Tabellen
formulieren
– Beispiel: welche Tabellen sind in einer Relation mit „HAUPTTABELLE“ verbunden?
•
Beobachtung: die Metatabellenstruktur ist für alle ERDs (und damit alle RDBMS)
gleich
– Die Namen und die Struktur der Metatabellen sind zwischen Herstellern unterschiedlich
•
•
•
WI
Schreibende Zugriffe sind in aller Regel dem RDBMS vorbehalten
Oft spezielle Befehle zum Lesen der Tabellen (statt select … from sysTables ..)
Auch andere Entwurfsmethoden (z.B. klassische Flussdiagramme) lassen sich via
Meta-ERD syntaktisch beschreiben
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
134
Metadaten und Data Dictionary im Relationalen Modell
Quelle: http://www.infforum.de/themen/anwendungsentwicklung/thema_SE-methode_esa.htm
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
135
Metadaten und Data Dictionary im Relationalen
Modell
Extraktion der Metadaten aus SQL Lite
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
136
Systemtabellen beschreiben Metadaten
aus der DB2-beschreibung
publib.boulder.ibm.com/infocenter/iadthelp/v7r0/index.jsp?topic=/com.ibm.etools.iseries.langref2.doc/rbafzmst970.htm
SYSCOLUMNS
The SYSCOLUMNS view contains one row for every column of each table
and view in the SQL schema (including the columns of the SQL catalog). The
following table describes the columns in the SYSCOLUMNS view:
Table 115. SYSCOLUMNS viewColumn name System Column Name
Data Type
Description
COLUMN_NAME
NAME
VARCHAR(128)
Name of the column. This will be the SQL column name if
one exists; otherwise, it will be the system column name.
TABLE_NAME
TBNAME
VARCHAR(128)
Name of the table or view that contains the column. This will
be the SQL table or view name if one exists; otherwise, it will be the system
table or view name.
TABLE_OWNER
TBCREATOR VARCHAR(128)
The owner of the table or view.
….. etc …….
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
137
Systemtabellen beschreiben Metadaten (ibd)
•
SYSTABLES
•
The SYSTABLES view contains one row for every table, view or alias in the SQL schema, including the tables and views of the SQL catalog. The following table
describes the columns in the SYSTABLES view:
•
SYSTABLES viewColumn
name
System Column Name
Data Type
Description
-----------------------------------------------------------------------------------------------------------------------------------------------------------TABLE_NAME
NAME
VARCHAR(128) Name of the table, view or alias. This is the SQL table, view or alias name if it exists; otherwise, it is the system
table, view or alias name.
TABLE_OWNER CREATOR
VARCHAR(128) Owner of the table, view or alias
TABLE_TYPE
TYPE
CHAR(1)
If the row describes a table, view, or alias:
A Alias ; L Logical file ; M Materialized query table ; P Physical file ; T Table; V View
COLUMN_COUNT
COLCOUNT
INTEGER
Number of columns in the table or view. Zero for an alias.
ROW_LENGTH RECLENGTH 114 INTEGER
Maximum length of any record in the table. Zero for an alias
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
138
Parametrisierte Queries
Motivation
• ? Welche Bücher haben die „Opuszko“s ausgeliehen?
SELECT BuchTitel, PLZ,… FROM Bücher INNER JOIN Personen ON
Bücher.AusleiherID = Personen.PersID WHERE Personen.Nachname =„Opuszko“
• Oder parametrisiert
SELECT BuchTitel, PLZ, … FROM Bücher INNER JOIN Personen ON
Bücher.AusleiherID = Personen.PersID WHERE Personen.Nachname =
[Nachname des Ausleihers:]
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
139
Parametrisierte Queries
•
•
•
•
Abfrageparameter werden erst zur Laufzeit eingegeben
Struktur der Abfrage bleibt
z.B. Between [Start date:] And [End date:]
Vorteile:
– Vorformulierung durch den Abfragedesigner
– “Voroptimierung” der Abfrage durch das RDBMS
– Sicherheit vor Manipulationen (insbesondere sogenannte Query
Injection)
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
140
Stored Procedures
•
•
•
•
•
•
WI
Programme, die die Datenbank abfragen und ihr Ergebnis i.d.R. als Tabelle abliefern (und
auch SQL-Teile enthalten)
Gespeichert im DBMS und in der Quelle nur bei entsprechender Berechtigung
zugänglich
„precompiled“ im Datenbankserver
Eigene Programmiersprache (namens „T-SQL“ oder ähnlich)
Oft unmittelbar aufgerufen vom Benutzer (bei entsprechender Berechtigung)
Von sehr vielen RDBMS unterstützt
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
141
Stored Procedures
Ein Beispiel
CREATE PROCEDURE sp_GetInventory
@location varchar(10)
AS
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = @location
•
Our Florida warehouse manager can then access inventory levels by issuing the command
–
•
The New York warehouse manager can use the same stored procedure to access that area's inventory.
–
WI
EXECUTE sp_GetInventory 'FL'
EXECUTE sp_GetInventory 'NY'
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
142
Stored Procedures
Weitere Beispiele
CREATE PROCEDURE uspGetAddress @City
nvarchar(30)
AS
SELECT *
FROM AdventureWorks.Person.Address
WHERE City = @City
CREATE PROCEDURE uspGetAddress @City nvarchar(30)
AS
SELECT *
FROM AdventureWorks.Person.Address
WHERE City LIKE @City + '%'
EXEC uspGetAddress @City = 'New York'
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
143
Stored Procedures
Ein Beispiel mit 2 Parametern und Default-Werten
CREATE PROCEDURE uspGetAddress @City
nvarchar(30) = NULL, @AddressLine1
nvarchar(60) = NULL
AS
SELECT *
FROM AdventureWorks.Person.Address
WHERE City = ISNULL(@City,City)
AND AddressLine1 LIKE '%' + ISNULL(@AddressLine1
,AddressLine1) + '%’
WI
EXEC uspGetAddress @City = 'Calgary', @AddressLine1 = 'A'
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
144
Stored Procedures
Vorteile
•
•
Effizienz durch Prä-Compilation
Information Hiding: die Namen der Tabellen, joins etc interessieren den Anwender nicht
– Sicherheit
– einfachere Wartung
•
•
•
•
WI
Stored Procedure sitzt in der Datenbank und ist benutzer-unabhängig zu warten
Stored Procedure hat u.U. gegenüber dem Aufrufer erweiterte Rechte
Vorteile der SP: in jüngster Zeit nicht unumstritten
viele ähnliche Alternativen verfügbar (z.B. ein C#-Programm anbinden)
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
145
Integritätsbedingungen, Regeln, Trigger
Idee: Lege in der Datenbank Eigenschaften fest, die legale Einträge erfüllen müssen 

Constraint
– AnzahlBücher integer <50 OR NULL
– PersID
Start mit 1 oder 2, dann eine gültige 2 stellige Ländernummer, dann 4 beliebige Ziffern
– PersID in BÜCHER
gültige PersID in PERSONEN or NULL
– „referentielle Integrität“

Aktionen (meist stored procedures), die von Ereignissen ausgelöst werden: Trigger
– Bei update PERSON.gehalt durch Benutzerrolle „Sachbearbeiter“ schreibe einen Eintrag in eine spezielle log-Tabelle
…
• Neben der Gehaltsänderung
• … oder an Stelle der Gehaltsänderung (der Chef stößt die eigentliche Änderung dann später an)
– Bei ungültigem PersID -Eintrag: Warne Benutzer und biete Hilfe an
– On delete cascade | on delete abort | on delete set Null | on delete exec procedureName ( typische Aktionen bei
Verletzung der referentiellen Integrität )
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
146
Integritätsbedingungen, Regeln, Trigger
•
Inhärente Integritätsbedingungen des Relationenmodells (modellinhärent)
– Typenintegrität (Wertebereiche von Attributen)
– Schlüsselintegrität
– Referentielle Integrität
•
Erweiterungen durch SQL-92
–
–
–
–
•
not null
check
primary key
foreign key
Komplexe Integritätsregeln in Relationalen Datenbanken
– kaum genutzt
– Nutzung von Triggern (da einfacher und variabler)
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
147
Integritätsbedingungen, Regeln, Trigger
•
Beispiel Referentielle Integrität
CREATE TABLE track(
trackid INTEGER,
trackname TEXT,
trackartist INTEGER Not Null,
FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);
CREATE TABLE song(
songid INTEGER,
songartist TEXT,
songalbum TEXT,
songname TEXT Not Null,
FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist, albumname)
);
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
148
Probleme der referentiellen Integrität
•
•
•
Befüllungsprobleme einer Relation
•
MaritalTable (PersID, Gender, marriedTo
references MartialTable(persID) )
Maggie heiratet Hans  OK ( HINWEIS: die referentielle Integrität
sichert nicht den Fall, dass ein Systemabsturz auftritt, nachdem die
Heirat bei Hans schon eingetragen ist und bei Maggie noch nicht)
•
Aber das Eintragen der schon immer verheirateten Grete macht
Probleme, da ihr Tupel bis zum Eintrag von Egon die Referentielle
Integrität verletzt!
Einzeleintrag eines Paares muss der 1.
Partner mit NULL eingetragen werden
Beim anfänglichen Befüllen der Tabelle („bulk
load“) zunächst alle Ehepartner auf NULL
–
–
–
•
Unterschiedliche Mechanismen nach DBMS
–
–
WI
??? Dubiose Lösung
Bessere Lösung: Ausschalten der
referentiellen Integrität „für gewisse Zeit“
… was mittlerweile auch im SQL:2011
Standard vorgsehen ist
SQLite ‹marriedTo references
MaritalTable(persID) DEFERRED
INITIALLY DEFERRED›
SQLServer, DB2 ,,, *‘ BCP- Befehl zum
„bulk copy“
persID
First name
Gender
marriedTo
10
Guido
M
NULL
11
Hans
M
NULL
12
Grete
F
43
13
Maggie
F
NULL
14
Michael
M
NULL
15
George
M
NULL
43
Egon
M
12
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
149
Trigger
Ein Beispiel
CREATE TRIGGER salary_trigger
BEFORE UPDATE ON employee_table
REFERENCING NEW ROW AS n, OLD ROW AS o
FOR EACH ROW
IF n.salary <> o.salary THEN
do something
END IF;
;
•
WI
Quelle: http://en.wikipedia.org/wiki/Database_trigger
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
150
Trigger
Weitere Beispiele
•
Assuming that customer records are stored in the "customers" table, and that order records
are stored in the "orders" table, the following trigger ensures that all associated orders are
redirected when a customer changes his or her address:
CREATE TRIGGER update_customer_address UPDATE OF address ON customers
BEGIN
UPDATE orders SET address = new.address WHERE customer_name = old.name;
END;
•
With this trigger installed, executing the statement:
UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';
•
causes the following to be automatically executed:
UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';
Beispiele entnommen aus: http://www.sqlite.org/lang_createtrigger.html
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
151
Trigger
Ein Beispiel zur Neuverheiratung
CREATE TRIGGER married_trigger
BEFORE UPDATE ON marital_table
REFERENCING NEW ROW AS n, OLD ROW AS o
FOR EACH ROW
IF n.marriedTo <> o.marriedTo THEN
if (n.marriedTo is not null) and ((select m.gender from marriedTo as m where m.persId = n.marriedTo) =
n.gender) THEN
persID
First name Gender marriedTo
ABORT;
END IF;
10
Guido
M
NULL
END IF;
11
Hans
M
13
;
WI
12
Grete
F
43
13
Maggie
F
11
14
Michael
M
NULL
…
…
…
…
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
152
Trigger
SQLite Syntax
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
153
Kapitel 1.5
INTERNA DES BETRIEBS VON
DATENBANKEN
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
154
Interna des Betriebs
Gliederung
• Indizes
• Query Planner
• Transaktionen
– ACID  commit, rollback und die Unterstützung durch logs
– Isolation als Problem des multitasking und ihre Sicherstellung durch
Sperrprotokolle oder andere Mechanismen
–  2PL
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
155
Indizes
•
Fortsetzung des „Ausleihe“-Beispiels ….
•
Oder parametrisiert
SELECT BuchTitel, PLZ, … FROM Bücher INNER JOIN Personen ON Bücher.AusleiherID
= Personen.PersID WHERE Personen.Nachname =[Nachname des Ausleihers:]
•
Sehr häufig wird auf die Bücher Tabelle mit Fremd-Schlüssel AusleiherID zugegriffen
…
Und sehr häufig auf die Personen Tabelle via Nachname, was gar kein Schlüssel ist
•
•
WI
!! Wirklich schnelle Zugriffe in eine Tabelle erfolgen nur sequentiell, oder wenn man
– salopp formuliert – die Zeilennummer kennt !!
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
156
Indizes
• Organisation auf der Festplatte
• Visual Basic Syntaxbeispiel
– Get #FileNum,
Position, Employee
– Put #FileNum,
Position, Employee
– LastRecord =
LastRecord + 1 ; Put
#FileNum, LastRecord,
Employee
–
–
WI
#FileNum  Kennzeichen der Datei
Position  Nr des Eintrags
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
157
Indizes
•
Lösung:
Neben der Tabelle eine weitere Struktur pflegen, in der die Zuordnung „Suchbegriff“ →
Satznummer rasch nachgeschlagen werden kann: „Index“
•
Beispiel: Grundmuster der „Inverted file“
Index on
Personen.Nachname
Index on
Bücher.AusleiherID
Suchbegriff
Liste des Auftretens
(Satznummern!
Nicht Primärschlüssel)
Opusko
981
Ruhland
2,99
Müller
Suchbegriff
12,13,14,45,98
Liste des Auftretens
(Satznummern)
789
123,453,523
1901
901
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
persID
Opuszko
4
45
158
Indizes
• Beobachtungen
– Der Index ist erheblich kleiner als die Datenbanktabelle und daher kann oft im
Hauptspeicher gehalten werden
– Tradeoff
• Es ist bei Vorhandensein eines Index zunächst auf den Index und dann auf Teile der Tabelle
zuzugreifen
• Der Index muss gepflegt werden (löschen/einfügen von Einträgen)
– Nicht immer ist ein bestimmter Index vorteilhaft
•
•
•
•
Wieviele Bücher sind derzeit insgesamt ausgeliehen?
Wieviel Bücher von Männern? Von 4 Personen-Familien?
Wieviele Bücher von Personen, deren Namen mit „O“ beginnt?
Wieviele Bücher von Personen, deren Namen mit „o“ endet?
– Die Ergebnisse sind mit ohne Index identisch (Index ist also reine Performanz-Überlegung)
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
159
Indizes
• Konsequenzen
– Die Anlage eines Index auf Spalten/Spaltengruppen muss vom Benutzer
angefordert werden (Ausnahme: Primärschlüssel und UNIQUE constraint)
– Ob ein Index bei der Abfrage verwendet wird, kann und wird meist vom DBMS
allein entschieden (Der Benutzer kann sog. Query Hints setzen)
– Sobald ein Index angelegt ist, muss er bei insert/delete/updates neben den
Tabellen gepflegt werden. Das DBMS macht das automatisch.
– Die optimale Indexstruktur ist ein bedeutendes Problem der Praktischen
Informatik und ist von Eigenschaften der indexierten Spalten abhängig (Beispiel:
„Index auf Primärschlüssel“  maximal 1 Eintrag; „Index auf Geschlecht“ 
~50% aller Zeilen). Die leistungsfähigsten DBMS stellen unterschiedliche
Indexarten bereit
– en.wikipedia.org/wiki/Index_(database) ist ein guter Einstieg
– Falls ein Indexwert nur wenige Records tangiert, ist der B-Tree ein guter und
gängiger Startpunkt
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
160
Indizes
Syntax
• CREATE INDEX index_name [index_type] ON tbl_name (index_col_name,...)
– index_col_name: col_name [(length)] [ASC | DESC]
– index_type: USING {BTREE | HASH}
• SELECT * FROM table1 IGNORE INDEX (col3_index) WHERE col1=1 AND
col2=2 AND col3=3;
• SELECT * FROM table1 USE INDEX (col3_index) WHERE col1=1 AND
col2=2 AND col3=3;
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
161
Indizes
•
Von http://www.sqlite.org/queryplanner.html:
“Most of the time, the query planner in SQLite does a good job on its own and without
outside help. However, the query planner needs indices to work with and it usually falls to
the programmer to add indices to the schema that are sufficient for the query planner to
accomplish its task.”
•
Es gibt Systeme, bei denen der Query-Planner selbst noch einen Index “on the fly”
anlegen darf
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
162
Indizes: Btree ( hier aus http://20bits.com/articles/interview-questions-database-indexes/ )
http://slady.net/java/bt/view.php animated Btree
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
163
Dt. und US Wikipedia SEHR gut
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
164
Lehrvideos
• https://www.youtube.com/watch?v=DoUWf3ASwpQ
• https://infosys.uni-saarland.de/datenbankenlernen/ Ein
exzellentes Kompendium aller Aspekte der Datenbanktechnik auf
Folien
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
165
Theo Härder (* 28. August 1945 in Bad Neustadt an der Saale) ist Professor der
Informatik an der Universität Kaiserslautern.
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
166
Transaktionen / ACID / Multitasking
•
•
•
Viele Benutzer / Prozesse greifen auf eine DB zu. Nicht jede einzelne Operation ist geeignet, die
Datenbank in einem „für Dritte“ benutzbaren Zustand zu hinterlassen (Beispiel: Buchung von
mehreren Aktiv- und Passiv-Konten)
Konzept der Datenbanktransaktion = Bündelung von logisch zusammengehörigen Operationen
Atomicity = „ganz oder gar nicht“ Prinzip. Nachdem aber die Einzeloperationen einer Transaktion eben
doch sequentiell ausgeführt werden, muss es geben:
–
Rollback transaction: alle bisherigen Änderungen der Transaktion werden rückgängig gemacht. Der Zustand der
Datenbank ist, als wäre eine Transaktion nie gestartet
•
–
•
Ein Rollback kann in Nutzer-Anforderung oder DBMS-Fehler, Rechten des Nutzes und anderem begründet sein
Commit transaction: die Transaktion hat auch die Prüfung auf Consistency überstanden. Ihr Dauernder Effekt
auf die Datenbank ist nach erfolgreichem commit garantiert †
Consistency. Eine Transaction, die committen will, wird auf alle Konsistenzbedingungen, die in der
Datenbank abgelegt sind, geprüft. Weitere Konsistenzbedingungen müssen ggf. durch user geprüft
sein.
–
Folgerung: eine Commit führt eine Datenbank aus einem konsistenten in einen anderen konsistenten Zustand
† heißt:
auf Datenbank, Sicherungskopie und logFile, so dass man auf Permanenz
hoffen darf
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
167
Transaktionen / ACID / Multitasking
•
•
Isolation: Transaktionen wissen nichts von anderen Transaktionen; sie lassen sich serialisieren
– Es ist für das Ergebnis egal, ob parallel zu TA auch TB und TC laufen
– Wenn TA,TB und TC zeitweise parallel laufen, so ist das Ergebnis doch so als ob sie in einer (u.U.
vorher nicht festgelegten) Reihenfolge völlig separat hintereinander gelaufen wären (z.B. TA  TC 
TB)
Durability: nach einem Commit von TA muss die Datenbank auch „nach einem crash“ die Veränderungen,
die TA gebracht hat, wieder enthalten (ggf. nach einer „Wiederanlaufphase“). Ein erfolgreich ausgeführter
Commit Befehl beinhaltet diese Verpflichtung.
Hinweis:
• „Rollback“ alias „Abort“ ist eine vergleichsweise komplizierte Operation, da z.B. abgebrochen wird,
nachdem schon 2 Teil-Ergebnisse geschrieben sind und diese wieder auf den alten Wert gesetzt
werden müssen (dazu hat die Datenbank „logs“ mit „vorher und nachher“-Zustand der Zeilen
• In modernen DBMS („Verteilte DBMS“, „Web DBMS“, ….) wird ACID oft für gewisse Zeitspannen
gelockert, in seiner Grundorientierung fast nie aufgegeben
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
168
Transaktionen / ACID / Multitasking
•
No changes can be made to the database except within a transaction. Any command that changes
the database (basically, any SQL command other than SELECT) will automatically start a transaction
if one is not already in effect. Automatically started transactions are committed when the last query
finishes.
(Quelle: SQLite)
•
Kernbefehle
–
–
–
–
•
WI
Begin transaction
Commit
Rollback
Autocommit wie oben geschildert
Verfeinerte Möglichkeiten für fortgeschrittene Anwendungen
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
169
Transaktionen / ACID / Multitasking
Literatur
• Informatik Universität Tübingen
– http://www-db.informatik.unituebingen.de/files/teaching/ws0910/dbs2/10_transactions_2_3.pdf
– Sehr gut in Deutsch
• Gute DBMS Kurse
– http:/inst.eecs.berkeley.edu/~cs186/sp03/lecs/ ab 22 ff auch was zu
Sperrprotokollen
– http://www.voneicken.com/courses/ucsb-cs290ffa06/images/f/ff/Lecture11.pdf
– kaul.inf.h-brs.de/home/script/db/folien_2004/12_Transactions.pdf und
auch sonst guter, suggestiver Deutscher Kurs
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
170
Rollback und ACID
zugleich: Wiederanlauf nach Störung
Transaction log auf „ausfallischerem Speicher“
hält für jede Transaktion ein „before“ und „after“
fest, sowie eine „commit“ bzw „abort“. Auch nach
einem crash kann die Datenbank so durch
„Nachfahren“ des log (wg. des „before-Image“
ggf. sogar „rückwärts“vom crash, indem
uncommited Transactions zurückgefahren werden
http://dbs.uni-leipzig.de/file/LoggingRecovery.pdf
sehr kurze logfile Intro
http://courses.cs.washington.edu/courses/cse544/00
sp/lectures/ppt/l11.ppt Intermediate Level
http://pages.cs.wisc.edu/~dbbook/openAccess/third
Edition/slides/slides3ed-english/Ch18_Recovery95.pdf
Ramakrishnan / Gehrke Slides
http://www.csee.umbc.edu/portal/help/oracle8/serv
er.815/a67781/c28recov.htm advanced level
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
171
http://www.sqlbackuprestore.com/trxlog07_a.png
Logfile Structue (simplified)
•
WI
see also
http://pages.cs.wisc.edu/~dbbook/openAccess/thi
rdEdition/slides/slides3edenglish/Ch18_Recovery-95.pdf
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
172
Serialisierbarkeit
• Probleme des Multitasking: Änderungen durch schreibende Transaktionen
können mit anderen Transaktionen interferieren und Ergebnisse generieren, die
auf der Basis eines konsistenten Zustands der Datenbank nicht möglich wären
• Lost Updates: Zwei Transaktionen modifizieren parallel denselben Datensatz
und nach Ablauf dieser beiden Transaktionen wird nur die Änderung von einer
von ihnen übernommen. T1: aa+2; bb*a T2 a3*a
• Dirty Read: Daten einer noch nicht abgeschlossenen, anderen Transaktion
werden gelesen. T2 hat gerechnet T3 berechnet eine sum(a) T2 aborts
• Non-Repeatable Read: Wiederholte Lesevorgänge liefern unterschiedliche
Ergebnisse. T2 hat gerechnet T3 berechnet eine sum(a) T2 aborts T3 sum(a)
• Phantom Read: Suchkriterien treffen während einer Transaktion auf
unterschiedliche Datensätze zu, weil eine (während des Ablaufs dieser
Transaktion laufende) andere Transaktion Datensätze hinzugefügt, entfernt oder
verändert hat.
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
173
Serialisierbarkeit
•
•
•
•
Das gängigste Verfahren zur Sicherstellung (starker Formen der) Serialisierbarkeit:
Sperren („locks“) auf Objekte, die eine Transaktion zu lesen (RL = read lock) oder zu
schreiben wünscht (WL)
WL schließt RL mit ein, WL ist exclusive, RL ein shared lock
Eine Transaktion, die ein Lock nicht erhält, muss warten, bis es verfügbar ist. Erst dann
rechnet sie weiter.
Locks allein reichen zur Serialisierbarkeit nicht aus, Zeitplan bei Erwerb/ Rückgabe
entscheidend
– Alle Locks zu Anfang erwerben und ganz zum Ende zurückgeben  OK, aber
überhaupt keine Parallelität potentiell kritischer Transacts
– 2 Phase lock: eine Transaktion kann in Phase 1 immer nur neue Locks erwerben (oder
RLWL upgraden), aber keine zurückgeben. In Phase 2 werden nur locks abgebaut
– … und in der „strict 2PL“ (manchmal „rigorous 2PL genannt“ erfolgt der Abbau sogar
zum Ende „schlagartig“  sehr häufig gewählte Variante
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
174
Sperrverfahren
•
Strong strict two-phase locking
•
or Rigorousness, or Rigorous scheduling, or Rigorous two-phase
locking
•
To comply with strong strict two-phase locking (SS2PL) the locking
protocol releases both write (exclusive) and read (shared) locks
applied by a transaction only after the transaction has ended, i.e.,
only after both completing executing (being ready) and becoming
either committed or aborted. This protocol also complies with the
S2PL rules. A transaction obeying SS2PL can be viewed as having
phase-1 that lasts the transaction's entire execution duration, and
no phase-2 (or a degenerate phase-2). Thus, only one phase is
actually left, and "two-phase" in the name seems to be still utilized
due to the historical development of the concept from 2PL, and 2PL
being a super-class. The SS2PL property of a schedule is also called
Rigorousness. It is also the name of the class of schedules having
this property, and an SS2PL schedule is also called a "rigorous
schedule". The term "Rigorousness" is free of the unnecessary
legacy of "two-phase," as well as being independent of any (locking)
mechanism (in principle other blocking mechanisms can be
utilized). The property's respective locking mechanism is sometimes
referred to as Rigorous 2PL.
Quelle: http://de.wikipedia.org/wiki/Sperrverfahren#Zwei-Phasen-Sperrprotokoll
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
175
Sperrverfahren:Begiffskonfusion
•
WI
Especially before 1990, but also after, in many articles and books, e.g., (Bernstein et al. 1987, p. 59),
the term "Strict 2PL" (S2PL) has been frequently defined by the locking protocol "Release all locks
only after transaction end," which is the protocol of SS2PL. Thus, "Strict 2PL" could not be there the
name of the intersection of Strictness and 2PL, which is larger than the class generated by the SS2PL
protocol. This has caused confusion. With an explicit definition of S2PL as the intersection of
Strictness and 2PL, a new name for SS2PL, and an explicit distinction between the classes S2PL and
SS2PL, the articles (Breitbart et al. 1991) and (Raz 1992) have intended to clear the confusion: The
first using the name "Rigorousness," and the second "SS2PL."
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
176
Sperrverfahren
Detailfragen
• Welches Objekt wird gesperrt
– Tabelle ?
– Zeile ?
– Zelle ?
• Sinnvoll ist es, dass zum Ende eher eine kleine Granularität gesperrt wird, aber
wir für die größeren Ebenen „vor-Sperrungen“ (Intention locks) einfügen
• Weitere Lock-Formen und -Kompatibilitäten
– Z.B. „Write Intention lock auf Tabelle2“ Erst mit einem derartigen WIL(Tab2)
Writelock auf Zeile3.Tabelle2“
– Siehe z.B. http://msdn.microsoft.com/en-us/library/ms175519.aspx
– Ähnlich wie ein write lock eine andere Transaktion von einem read lock
ausschließt, gibt es es bei den Intention locks auch eine Hierarchie des
Zulässigen
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
177
Sperrverfahren
Detailfragen
• Die Matrix in Tabelle 1
beschreibt, wann eine
Transaktion Tj eine Sperre
• erhält (Eintrag in der Zelle 1)
oder nicht erhält (Eintrag in
der Zelle 0),
• wenn eine andere
Transaktion Ti bereits eine
bestimmte Sperre hält
Quelle: http://homepages.fh-giessen.de/~hg11260/mat/trans-l.pdf
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
178
Zeitstempel (Timestamping)
• „Optimistische“ Alternativen zum Locking
– Annahme: es geht in den allermeisten Fällen gut
– Wir müssen die Konfliktfälle erkennen und dann eine der
Transaktionen zurücksetzen
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
179
Zeitstempel (Timestamping)
•
Jedes Objekt (z.B. Tabellenzeile) trägt zwei time stamps
– Zeitpunkt letztes Write Tw(Obji) (genauer: timestamp τ der letzten ändernden Transaction)
– Zeitpunkt letztes Read Tr(Obji) (genauer: timestamp τ der letzten schreibenden Transaction)
•
Jedes Transaktion trägt als stamp den Zeitpunkt ihrer Erstellung τ
– Idee ist: die Transaktion verhält sich so, als wäre sie komplett zu diesem
Zeitpunkt abgelaufen. Wenn sie sich z.T. auf Daten stützt, die nach τ verändert
wurden, muss sie abbrechen
– Ebenso in gewissen Fällen des Schreibkonflikts
•
Jede Transaktion poolt alle ihre möglichen Änderungen und führt diese erst am
Ende aus
•
Typische Anwendung z.B. WEB-datenbanken, damit uns nicht ein „abgestürzter“
user den Rest ausbremst
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
180
Zeitstempel (Timestamping) - Regeln
•
•
Wenn ein Objekt i gelesen wird, das zuletzt vor τ geändert wurde  OK; anpasse dessen
letztes Read Tr
Wenn ein Objekt i gelesen wird, das zuletzt nach τ geändert wurde  Breche ab und
Starte die Transaktion neu
– Nachdem wir alle Ergebnisse erst ganz zum Ende schreiben  einfach abbrechen
•
•
•
WI
Wenn ein Objekt geschrieben werden soll, dessen letztes Tr nach τ liegt  eine andere
Transaktion hat sich schon auf diesen (bisherigen Wert verlassen)  Abbrechen
Wenn ein Objekt geschrieben werden soll, dessen letztes Tw nach τ liegt  eine
Transaktion, die jünger ist als unsere hat einen ganz neuen Wert reingeschrieben. Unser
Wert wäre überschrieben  ignoriere unseren Schreibbefehl
Else: schreiben und Tw(Obji) mit τ aufdatieren
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
181
Deadlocks bei Sperrprotokollen
Beispiel
t1
tc
T1 hat RL(A) und
will WL(B)
T2
hat WL(B) und
will WL(A)
In einem S2PL muss bei tc die T1 oder T2 abgebrochen werden („rollback“ and redo), sonst droht
unendlich lange Verklemmung
t
Handlungsbedarf
• Wer entscheidet, ob T1 oder T2 abgebrochen wird
–
•
WI
…nach welchen Kriterien
Oder vermeidet man den deadlock durch Rücksetzen von T1 oder T2 ehe er auftritt (deadlock droht
erst nach tc also könnte man vor tc eine der Transaktionen zurücksetzen), wofür t1 der natürlich
Zeitpunkt ist
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
182
Deadlocks
Beispiel
T1 hat RL(A) und
T2
hat WL(B) und
t1
tc
t
will WL(B)
will WL(A)
Typische Strategie
• Jede Transaction hat ihren Startzeitpunkt
• Will eine Transact eine Ressource, die von einer anderen gehalten wird (hier:t1), läuft die ältere
weiter; die jüngere transact bricht ab und wird mit Originalstartzeitpunkt neu gestartet („wait or
die“). Es wird oft nicht geprüft, ob es tatsächlich zu einem Deadlock gekommen wäre
• Alternativ könnte z.B. die rechenintensivere der beiden Transacts der Gewinner sein
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
183
locking vs. timestamping
Locks
•
•
•
•
•
WI
Timestamps
jede xact wird u.U. oft warten,
bis sie ihre locks bekommt
beim R2PL bricht keine xact
wegen einer anderen ab
der Zeitpunkt der xact ist der
Zeitpunkt ihres commit
sie friert sich progressiv die für
sie relevanten Datenbank ein
•
Risiko: „deadlock“
•
•
•
•
keine xact wird je warten, aber u.U. oft
abbrechen
xacts brechen ab, wenn sie einen Konflikt
feststellen
der Zeitpunkt eine xact ist ihre „Geburt“
sie hofft, dass sich die DB in ihrer
Lebensdauer nicht verändert hat
Risiko: oftmaliger „abort“
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
184
Transaktionen vs. Locks
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
185
Kapitel 1.6
ANBINDUNG VON DATENBANKEN IN
PROGRAMMEN
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
186
Problemübersicht
• Ein Programm muss Anfragen an die DB absetzen können und Ergebnisse in
einer Form erhalten, die innerhalb der Programmiersprache weiterverwendbar
sind
• Der genaue Typ der im Client/Servermodell kontaktierten Datenbank sollte dem
Programm weitestgehend
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
187
Lost in Translation
• SQL is a powerful language but specific to DBMS
• Result of a query can be a set of rows that come crashing down like a big wave
• Application languages very flexible but no data structure to handle rows and
rows of query results
• Mismatch resolved through additional SQL constructs
• Obtain a handle on a collection and iterate over it one record at a time
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
188
Operationalisierung der Grundideen
Verbindung zum DBMS
 „Embedded SQL“: spezielle Befehle innerhalb eines Programms werden
von einem „Precompiler“ übersetzt  nur noch selten

 „API“: es gibt direkte Aufrufe eines Application Programmers Interface, das
vom Hersteller der Datenbank mitgeliefert wird.

– y = Math.Exp (x) -- das von einem Programmierer zur Verfügung gestellte EXP Programm wird aufgerufen. Der Wert in
x wird verwendet, das Ergebnis in y gespeichert
-------------– $dbhandle = sqlite_open('sqlitedb');
– $query = sqlite_query($dbhandle, 'SELECT name, em FROM users LIMIT 25'); --führe den SelectBefehl aus, richte ihn gegen die Datenbank, die mit $dbhandle bezeichnet ist, und speichere das Ergebnis in $query (eine
Struktur, deren Spaltentyp und Zeilenanzahl unbekannt sind !!!
$result = sqlite_fetch_all($query, SQLITE_ASSOC); --extrahiere die 1. Zeile aus $query
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
189
Operationalisierung der Grundideen
Problem der Weiterverarbeitung der Ergebnisse:
• ein SELECT liefert eine Tabelle unbekannter Länge, mit unbekannten
Spaltennamen und Spaltentypen ab unterschiedliche Lösungen
• (a) Einfachlösung
– Cursor Prinzip: ein SELECT kreiert eine Ergebnistabelle mit einem
Cursor (= Zähler auf die Zeilennummer). Ein Cursor auf dieses Ergebnis
liefert die Resultate zeilenweise ab. Mit einem Befehl der Art fetch_next
bekommt man die folgende Zeile
– die Felder werden als Text angeliefert. Für die weitere Verarbeitung ist
das Programm verantwortlich.
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
190
Operationalisierung der Grundideen
• Problem der Weiterverarbeitung der Ergebnisse:
ein SELECT liefert eine Tabelle unbekannter Länge, mit unbekannten
Spaltennamen und Spaltentypen ab unterschiedliche Lösungen
• (b)OO Lösung
– OO Prinzip: das SELECT kreiert ein Objekt vom Typ Resultset. Objekte
dieses Typs verstehen Methoden der Art „gib mir deine nächste Zeile“,
„sag mir, wie deine 3. Spalte heißt“, „sag mir, wie viele Zeilen Du
enthältst“, gibMirVonZeile 30 die Spalte mit dem Namen „Umsatz“
– Das ist leistungsfähiger und intuitiver als (a) , setzt aber
Programmiersprachen voraus, die solche Konzepte unterstützen!
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
191
Lokaler Zugriff durch das Programm - Beispiel
$<?php
Beispiel: Programmiersprache PHP 5 und (nativer) Zugriff auf SQLite Datenbank
// create new database (OO interface)
$db = new SQLiteDatabase("db.sqlite");
// create table foo and insert sample data
$db->query("BEGIN;
CREATE TABLE foo(id INTEGER PRIMARY KEY, name CHAR(255));
INSERT INTO foo (name) VALUES('Ilia');
INSERT INTO foo (name) VALUES('Ilia2');
INSERT INTO foo (name) VALUES('Ilia3');
COMMIT;");
// execute a query
WI
……
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
192
Lokaler Zugriff durch das Programm - Beispiel
// …execute a query
$result = $db->query("SELECT * FROM foo");
// iterate through the retrieved rows
Echo “we have in Total”. $result->numRows()
while ($result->valid()) {
// fetch current row
$row = $result->current();
print_r($row);
// proceed to next row
$result->next();
}
// not generally needed as PHP will destroy the connection
unset($db);
?>
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
193
Lokaler Zugriff durch das Programm - Beispiel
•
WI
/* each result looks something like this
Array
(
[0] => 1
[id] => 1
[1] => Ilia
[name] => Ilia
)
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
194
Anbindung von DB in Programme: PHP  PDO
<?php
/*** mysql hostname ***/
$hostname = 'localhost';
/*** mysql username ***/
$username = 'username';
/*** mysql password ***/
$password = 'password';
try {
$dbh = new PDO("mysql:host=$hostname;dbname=animals", $username,
$password);
/*** echo a message saying we have connected ***/
echo 'Connected to database<br />';
/*** INSERT data ***/
$count = $dbh->exec("INSERT INTO animals(animal_type, animal_name)
VALUES ('kiwi', 'troy')");
/*** echo the number of affected rows ***/
echo $count;
/*** close the database connection ***/
$dbh = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>Beispiel OO-Zugriff in PHP
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
195
Datenbankabstraktion
• Jedes Datenbanksystem hat seine eigenen API-Befehle, die leicht
unterschiedlich sein können
– SELECT name, gebDat FROM users LIMIT 25;
– SELECT TOP 25 name, gebDat FROM users
• Programmierer haben Interesse, nicht unterschiedliche
Programmversionen pro Datenbank zu pflegen
• Datenbankhersteller wollen möglichst viele Programmierer /
Anwender erreichen
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
196
Datenbankabstraktion
• Lösung „Abstraktions-‹layer›“:
– Programm ruft die API einer „abstrakten“ Datenbank in einem Standard-SQLDialekt auf
– Abstraktionsschicht übersetzt in die Sprache der konkreten Datenbank und ruft
deren API auf
– Ergebnisse werden ebenfalls über die Abstraktionsschicht geleitet und ggf.
umformatiert
• Meyer,Sept 14 2010
• Meyer, 20100914
• Dieses Konzept wird von einer Vielzahl leicht unterschiedlicher Lösungen
umgesetzt
– ODBC JDBC
– PEAR PDO OLE DB
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
197
Datenbankabstraktion
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
198
Datenbankabstraktion
•
The barest ODBC system would
include an ODBC-conformant
driver accessing some data, and an
ODBC-conformant application,
linked to the driver library.
•
If commercial applications were
distributed in this way, users would
need to re-link their applications
to their chosen driver whenever
they wanted to access a different
data source.
•
Instead, the application program is
linked to a driver manager, which
loads and initializes the required
driver at runtime:
Quelle:
http://www.easysoft.com/products/data_access/xml_odbc_server/manu
al/introduction.html
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
199
Datenbankabstraktion - Bemerkungen
• Die Herstellerabhängigkeit wird nicht beseitigt, sondern an einer
einzigen Stelle konzentriert
• Diese Abhängigkeit wird durch den Driver Manager vom Benutzer
voll verborgen
• Die Grundidee lässt sich erweitern, indem z.B. anders formatierte
Datenbestände, die ebenfalls letztlich eine Tabelle liefern können,
am backend verwendet werden
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
200
Datenbankabstraktion
•
WI
OLE DB (Object Linking and Embedding, Database, sometimes written as OLEDB or OLEDB) is an API designed by Microsoft for accessing data from a variety of sources in a
uniform manner. It is a set of interfaces implemented using the Component Object
Model (COM); it is otherwise unrelated to OLE. It was designed as a higher-level
replacement for, and successor to, ODBC, extending its feature set to support a wider
variety of non-relational databases, such as object databases and spreadsheets that do
not necessarily implement SQL.
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
201
Literatur
http://php.net/manual/en/intro.pdo.php onPDO
http://php.net/manual/en/book.pdo.php
http://www.ch-werner.de/sqliteodbc/ SQLite ODBC
PEAR
JDBC
http://www.sqlsummit.com/ODBCVend.HTM listet –zig ODBC und OLE
DB und JDBC providers
• http://www.canaimasoft.com/f90sql/ListOfODBCDrivers.htm d/o
• http://www.greaterscope.net/documentation/php_database_abstractio
n_class_comparison.html (PEAR PDO,…)
•
•
•
•
•
•
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
202
Kapitel 1.7
VERTEILTE DATENBANKEN
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
203
Verteilte Datenbanken (gesamter Abschnitt aus http://www.sts.tuharburg.de/~r.f.moeller/lectures/db-ws-04-05/91-VerteilteDBs.pdf)
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
204
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
205
Verteilte Datenbanken
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
206
Horizontale Fragmentierung der ProfessorenTabelle
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
207
Verteilte Datenbanken
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
208
Schlechte Fragementierung der VorlesungTabelle
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
209
Gute horizontale Fragmentierung
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
210
Verteilte Datenbanken
Aufteilung in einzelne, meist nicht überlappungsfreie „Projektionen“
Siehe später in der Vorlesung bei
Coumnar storage
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
211
Verteilte Datenbanken 2PC (two phase commit)
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
212
Verteilte Datenbanken 2PC (two phase commit)
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
213
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
214
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J.
Ruhland | SS2015
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
215
Verteilte Datenbanken 2PC (two phase commit)
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
216
Verteilte Datenbanken 2PC (two phase commit)
behandeln wir zum
Ende des 2PC !
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
217
Verteilte Datenbanken 2PC (two phase commit)
wenn Agent lange inaktiv war, kann
ein „abort assumed“ von Anfang an
für das DBMS global vereinbart
werden
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
218
Verteilte Datenbanken 2PC (two phase commit)
wieder das KoordinatorAbsturz-Problem
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
219
Verteilte Datenbanken
• 3PC: mehrstufige Verfahren zur Sicherung von ACID
•
•
Siehe zB
www.cs.utexas.edu/users/lorenzo/corsi/
cs380d/past/03F/notes/9-11.pdf
•
Precommit sagt „als coordinator habe
ich gegen commit nichts einzuwenden“
EIN Client, der ein preCommit
erhalten hat, reicht aus um die
Meinung des Coordinators zu
sichern (oder „Quorum“-regelung) 
Nachfolger für Coordinator
wählbar
doCommit sagt: jetzt kannst du alles
zur Transact vergessen
•
•
WI
Eine Variante des 3 phase commit protocols
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
220
http://dbs.uni-leipzig.de/buecher/mrdbs/mrdbs-70.html
Mit dem Precommit sichert der Koordinator zu, daß er von sich aus die Transaktion nicht mehr zurücksetzt. Allerdings ist es im Gegensatz zum Commit
nach einem Precommit des Koordinators nach dessen Ausfall noch möglich, die Transaktion abzubrechen.
Wird während der Commit-Behandlung ein Koordinatorausfall erkannt (durch Timeout), so erfolgt die Wahl eines neuen Koordinators. Damit der neue
Koordinator die Commit-Behandlung fortführen kann, erfrägt er zunächst von den überlebenden Rechnern den Commit-Zustand bezüglich der
betroffenen Transaktion. Wenn einer der Agenten einen Commit-Satz oder Abort-Satz für die Transaktion protokolliert hat, wird das
entsprechende Ergebnis global gültig gemacht. Wenn keiner der Agenten einen Abort- oder Commit-Satz, jedoch wenigstens einer einen
Precommit-Zustand vorliegen hat, dann wird das 3PC-Protokoll mit dem Verschicken der PRECOMMIT-Nachrichten fortgesetzt. Anderenfalls wird
auf Abbruch der Transaktion entschieden.
Abb. 7-8: Nachrichtenfluß beim Drei-Phasen-Commit
Das im 2PC-Protokoll bestehende Blockierungsproblem im Prepared-Zustand eines Agenten ist mit diesem Ansatz gelöst. Denn wenn der Koordinator
auf Abort entschieden hat, dies jedoch nicht mehr propagiert wurde, dann kann keiner der Agenten im Precommit-Zustand sein. Der neue
Koordinator entscheidet daher richtigerweise auf Transaktionsabbruch. Hatte der ausgefallene Koordinator auf Commit entschieden, so findet
der neue Koordinator bei wenigstens einem der überlebenden Agenten einen Precommit-Zustand vor (da nach Voraussetzung neben dem
Koordinator höchstens K-1 weitere Rechner ausfallen dürfen und vor dem Commit K Agenten das Precommit quittierten). Allerdings entscheidet
der neue Koordinator in dieser Situation nicht unmittelbar auf Commit, da dann der Ausfall des neuen Koordinators auf dasselbe
Blockierungsproblem wie beim 2PC führen würde. Stattdessen wird das 3PC-Protokoll mit dem Verschicken der PRECOMMIT-Nachrichten
fortgesetzt. Ist der ursprüngliche Koordinator im Precommit-Zustand ausgefallen, so kann die globale Transaktion sowohl abgebrochen als auch
erfolgreich beendet werden. Welche Entscheidung vom neuen Koordinator gefällt wird, hängt davon ab, ob noch einer der Agenten den
Precommit-Zustand erreicht hat.
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
221
Kritik 2PC und insbes. 3PC
•
•
•
WI
Sehr großer Aufwand, der eigentlich
nur für einige wenige Situationen
benötigt wird (Coordinator fail in der
2.Phase), sonst aber zu nichts
genutzt wird. (vgl. Aiport Security;
Entrauchung BER)
ACID zu jeder Millisekunde als
„heilige Kuh“
?? ist fail-> stop der wahrscheinliche
Verlauf einer Störung = ??
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
222
http://the-paper-trail.org/blog/consensus-protocols-three-phase-commit/
see also http://the-paper-trail.org/blog/consensus-protocols-paxos/
Consensus Protocols: Three-phase Commit
Last time we looked extensively at two-phase commit, a consensus algorithm that has the benefit of low latency but which is offset by fragility in the face of participant machine crashes. In this short note, I’m going to explain how the addition of an extra phase to the protocol
can shore things up a bit, at the cost of a greater latency.
The fundamental difficulty with 2PC is that, once the decision to commit has been made by the co-ordinator and communicated to some replicas, the replicas go right ahead and act upon the commit statement without checking to see if every other replica got the message.
Then, if a replica that committed crashes along with the co-ordinator, the system has no way of telling what the result of the transaction was (since only the co-ordinator and the replica that got the message know for sure). Since the transaction might already have been
committed at the crashed replica, the protocol cannot pessimistically abort – as the transaction might have had side-effects that are impossible to undo. Similarly, the protocol cannot optimistically force the transaction to commit, as the original vote might have been to abort.
This problem is – mostly – circumvented by the addition of an extra phase to 2PC, unsurprisingly giving us a three-phase commit protocol. The idea is very simple. We break the second phase of 2PC – ‘commit’ – into two sub-phases. The first is the ‘prepare to commit’ phase.
The co-ordinator sends this message to all replicas when it has received unanimous ‘yes’ votes in the first phase. On receipt of this messages, replicas get into a state where they are able to commit the transaction – by taking necessary locks and so forth – but crucially do not
do any work that they cannot later undo. They then reply to the co-ordinator telling it that the ‘prepare to commit’ message was received.
The purpose of this phase is to communicate the result of the vote to every replica so that the state of the protocol can be recovered no matter which replica dies.
The last phase of the protocol does almost exactly the same thing as the original ‘commit or abort’ phase in 2PC. If the co-ordinator receives confirmation of the delivery of the ‘prepare to commit’ message from all replicas, it is then safe to go ahead with committing the
transaction. However, if delivery is not confirmed, the co-ordinator cannot guarantee that the protocol state will be recovered should it crash (if you are tolerating a fixed number of failures, the co-ordinator can go ahead once it has received confirmations). In this case, the
co-ordinator will abort the transaction.
If the co-ordinator should crash at any point, a recovery node can take over the transaction and query the state from any remaining replicas. If a replica that has committed the transaction has crashed, we know that every other replica has received a ‘prepare to commit’
message (otherwise the co-ordinator wouldn’t have moved to the commit phase), and therefore the recovery node will be able to determine that the transaction was able to be committed, and safely shepherd the protocol to its conclusion. If any replica reports to the
recovery node that it has not received ‘prepare to commit’, the recovery node will know that the transaction has not been committed at any replica, and will therefore be able either to pessimistically abort or re-run the protocol from the beginning.
So does 3PC fix all our problems? Not quite, but it comes close. In the case of a network partition, the wheels rather come off – imagine that all the replicas that received ‘prepare to commit’ are on one side of the partition, and those that did not are on the other. Then both
partitions will continue with recovery nodes that respectively commit or abort the transaction, and when the network merges the system will have an inconsistent state. So 3PC has potentially unsafe runs, as does 2PC, but will always make progress and therefore satisfies its
liveness properties. The fact that 3PC will not block on single node failures makes it much more appealing for services where high availability is more important than low latencies.
Next time I talk about consensus, it will be to try and describe Paxos, which is really a generalisation of 2PC and 3PC which has found massive popularity recently in building real-world distributed replicated state machines such as Chubby.
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
223
Verteilte Datenbanken 2PC (two phase commit)
•
•
Hundertprozent ACID und Konsistenz an jedem Ort verursachen einen ERHEBLICHEN
Aufwand und Latenzzeiten bis zum Commit
Ein „auseinandergebrochenes Netz“ ist gar nicht arbeitsfähig
gute Quellen zum 3PC
http://www.ipd.uka.de/~ipd/institut/tav/Material06/09b-verteilteTA.pdf
http://pages.cs.wisc.edu/~dbbook/openAccess/thirdEdition/ Ramakrishnan/Gehrke
http://books.google.de/books?id=_Z0DTXSJHfwC&pg=PA726&lpg=PA726&dq=3pc+alte
rnatives+2pc&source=bl&ots=7gZijRhdoy&sig=JeRJY0QpxAbROXtj5gpOzEPqyE&hl=de&sa=X&ei=ZOZpU5TtCMTkOsDsgMAE&ved=0CFIQ6
AEwAw#v=onepage&q=3pc%20alternatives%202pc&f=false ein Google Book
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
224
http://www.facweb.iitkgp.ernet.in/~pallab/dist_sys/Lec-11CommitProtocols.pdf
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
225
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
226
Ende
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
227
ACK
ACK
NAK
NAK
PRECOMMIT !
COMMIT !
PREPARE
PRECOMMIT !
COMMIT !
PREPARE
WI
Vorlesung "Data and Knowledge Management" (MW31.6) | Prof. Dr. J. Ruhland | SS2016
228