Einführung in relationale Datenbanken und SQL

Einführung in relationale
Datenbanken und
SQL-Programmierung
Ralf Adams, TBS1 Bochum
12. Oktober 2011
Inhaltsverzeichnis
1 Einführung
2 Tabellen
2.1 Tabellen . . . . . . . . . . . . . . . . . . .
2.1.1 Tabelle, Zeile, Spalte und Schlüssel
2.2 Exkurs: Hierarchische Datenbanken . . . .
2.3 Schlüssel und Verknüpfungen . . . . . . .
2.3.1 Schlüssel und Primärschlüssel . . .
2.3.2 Darstellung als ER-Modell . . . . .
2.3.3 Was ist eine Verknüpfung? . . . . .
2.3.4 Kardinalitäten . . . . . . . . . . . .
2.3.4.1 1:1-Verknüpfungg . . . . .
2.3.4.2 1:n-Verknüpfung . . . . .
2.3.4.3 n:m-Verknüpfung . . . . .
2.3.5 Aufgaben zum ER-Modell . . . . .
2.3.6 Referentielle Integrität . . . . . . .
2.3.7 Normalformen . . . . . . . . . . . .
2.3.7.1 Normalform 1 . . . . . . .
2.3.7.2 Normalform 2 . . . . . . .
2.3.7.3 Normalform 3 . . . . . . .
7
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
3 Implementieren mit SQL
3.1 Was ist SQL? . . . . . . . . . . . . . . . . . . . . . .
3.2 MySQL . . . . . . . . . . . . . . . . . . . . . . . . .
3.3 SQL-DDL . . . . . . . . . . . . . . . . . . . . . . . .
3.4 SQL-DML . . . . . . . . . . . . . . . . . . . . . . . .
3.4.1 Daten einfügen . . . . . . . . . . . . . . . . .
3.4.1.1 Daten aus CSV-Dateien einfügen . .
3.4.1.2 Daten mit INSERT einfügen . . . . .
3.4.2 Daten ändern . . . . . . . . . . . . . . . . . .
3.4.3 Exkurs: WHERE-Klausel . . . . . . . . . . . . .
3.4.4 Daten löschen . . . . . . . . . . . . . . . . . .
3.4.5 Daten auswerten mit SELECT . . . . . . . . . .
3.4.5.1 Tabellenunabhängige Auswertungen
3.4.5.2 Einfache Zeilen und Spaltenauswahl
3.4.5.3 Auswahl sortieren . . . . . . . . . .
Einführung in relationalen Datenbanken und SQL
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
8
8
8
11
12
12
15
15
18
18
20
22
23
25
27
27
28
30
.
.
.
.
.
.
.
.
.
.
.
.
.
.
31
31
31
32
40
41
41
42
43
43
46
46
46
47
49
2
Inhaltsverzeichnis
3.4.5.4
3.4.5.5
Inhaltsverzeichnis
Mehrfachausgaben unterdrücken . . . . . . . . . . . . .
Verknüpfen mit INNER JOIN . . . . . . . . . . . . . . .
Einführung in relationalen Datenbanken und SQL
50
51
3
Tabellenverzeichnis
2.3
2.5
Namensübersicht . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Tabelle: buch (3) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
11
14
3.1
3.2
3.3
3.4
3.5
3.5
Tabelle:
Tabelle:
Tabelle:
Tabelle:
Tabelle:
Tabelle:
34
35
36
37
44
45
Numerische Datentypen (Auszug) . . . . .
Datum und Zeit Datentypen (Auszug) . . .
Text und Blob Datentypen (Auszug) . . .
Zusätze (modifier) zu Datentypen (Auszug)
Elemente der WHERE-Klausel (Auszug) . . .
Elemente der WHERE-Klausel (Auszug) . . .
Einführung in relationalen Datenbanken und SQL
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
4
Abbildungsverzeichnis
2.1
2.2
2.3
2.4
2.5
2.6
2.7
2.8
2.9
2.10
2.11
2.12
2.13
Hierarchische Darstellung . . . . . . . . . . . . . . . . . . . . . .
ER-Modell buch(1) . . . . . . . . . . . . . . . . . . . . . . . . . .
Primärschlüssel - Fremdschlüssel zwischen buch und autor . . . .
Primärschlüssel - Fremdschlüssel zwischen buch anderen Tabellen
ER-Modell Buch(2) . . . . . . . . . . . . . . . . . . . . . . . . . .
Primärschlüssel - Fremdschlüssel zwischen person und ausweis .
ER-Modell person - ausweis . . . . . . . . . . . . . . . . . . . . .
Primärschlüssel - Fremdschlüssel zwischen bankkunde und konto .
ER-Modell bankkunde - bankkonto . . . . . . . . . . . . . . . . .
ER-Modell kuchen . . . . . . . . . . . . . . . . . . . . . . . . . .
Primärschlüssel - Fremdschlüssel zwischen kuchen und zutat . . .
Datenauszug von kunde und adresse . . . . . . . . . . . . . . . .
Datenauszug von kunde und adresse . . . . . . . . . . . . . . . .
Einführung in relationalen Datenbanken und SQL
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
12
15
16
18
19
19
20
20
21
22
23
25
25
5
Definitionen
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Spalte . . . . . . . . . . . .
Zeile . . . . . . . . . . . . .
Tabelle . . . . . . . . . . . .
Domäne . . . . . . . . . . .
Schlüssel . . . . . . . . . . .
Primärschlüssel . . . . . . .
Entity Relationship Model .
Fremdschlüssel . . . . . . .
Verknüpfung . . . . . . . . .
1:1-Verknüpfung . . . . . . .
1:n-Verknüpfung . . . . . .
n:m-Verknüpfung . . . . . .
Referentielle Integrität . . .
Atomar . . . . . . . . . . .
Wiederholungsgruppefreiheit
Normalform 1 . . . . . . . .
Voll- und teilfunktional . . .
Normalform 2 . . . . . . . .
Transitiv . . . . . . . . . . .
Normalform 3 . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
10
10
10
11
12
13
15
16
17
19
21
22
26
28
28
28
29
30
30
30
21
22
Kartesisches Produkt . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
INNER JOIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
52
53
Einführung in relationalen Datenbanken und SQL
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
6
1 Einführung
Die Ablage, Edition und Auswertung von Informationen ist eines der wesentlichen Aufgaben der EDV. Schnell stößt man dabei auf das Problem, die Daten in möglichst optimaler Form zu verwalten. Die Daten sollen derart abgelegt sein, dass sie schnell verfügbar,
leicht auszuwerten und mit möglichst geringem Aufwand zu editieren sind. Mit wachsender Rechnerleistung werden die dazu verwendeten Konzepte immer mächtiger. Das
derzeit bedeutendste ist das Konzept der relationalen Datenbanken mit der Abfragesprache SQL1 . Neben diesem Konzept gibt es aber auch noch andere, die hier nur kurz
erwähnt werden
• Hierarchische Datenbanken
• Objektorientierte Datenbanken
Alle diese Konzepte finden heute in der Praxis Verwendung.
Zum Skript
Ziel des Skripts ist die Einführung aller wichtigen Begriffe und Techniken einer relationalen Datenbank, sofern diese sinnvollerweise Unterrichtsgegenstand sein können. Weiterführende Techniken oder Vertiefung in einzelnen Datenbankserversysteme, wie Oracle
würden den Rahmen sprengen.
Die Zielgruppen dieses Skripts sind Informationstechnische Assistenten/innen und Fachinformatiker/innen in der Ausbildung. Das Skript kann als Unterrichtsgrundlage oder
häusliches Lernmittel verwendet werden.
1
structered query language; engl. strukturierte Abfragesprache
Einführung in relationalen Datenbanken und SQL
7
2 Tabellen
2.1 Tabellen
2.1.1 Tabelle, Zeile, Spalte und Schlüssel
In relationalen Datenbanken werden alle Informationen in Tabellen abgespeichert. Dies
scheint trivial zu sein, aber man mache sich klar, dass es auch andere Formate zur Ablage
von Informationen gibt.
Als Beispiel für eine sequentielle Buchdatenablage nehmen wir ein spezielles Ablageformat: Das BibTEX-Format.
BibTeX
@Book{boehm1,
author = {Oliver Böhm},
title = {Fehlerfrei programmieren in C und C++},
publisher = {dpunkt.verlag},
year = {2000},
key = {Softwareentwicklung, C, C++},
address = {Heidelberg},
edition = {2},
note = {Tipps zur Fehlervermeidung},
annote = {ISBN: 3-932588-76-3}
}
@Book{apetri1,
author = {Marius Apetri},
title = {3D-Grafik Progammierung},
publisher = {mitp},
year = {2003},
key = {Grafikprogrammierung, C++},
address = {Bonn},
edition = {1},
note = {Theorie + Praxis der Grafikprogrammierung},
annote = {ISBN: 3-8266-0891-7}
}
@Book{loudon1,
author = {Kyle Loudon},
title = {Algorithmen in C},
publisher = {O’Reilly},
year = {2000},
key = {Algorithmen, Programmierung, C},
address = {Köln},
edition = {1},
note = {Theorie + Praxis von Standardalgorithmen},
annote = {ISBN: 3-89721-165-3}
}
@Book{yarger1,
author = {Randay J. Yarger, George Reese, Tim King},
title = {MySQL},
publisher = {O’Reilly},
year = {2000},
key = {Datenbankprogrammierung, SQL},
address = {Köln},
edition = {1},
note = {MySQL als DB-Engine und die Sprache SQL},
annote = {ISBN: 3-89721-163-7}
}
Einführung in relationalen Datenbanken und SQL
8
2 Tabellen
2.1 Tabellen
@Book{stoer1,
author = {Josef Stoer},
title = {Numerische Mathematik 1},
publisher = {Springer},
year = {1989},
key = {Mathematik},
address = {Berlin},
edition = {5},
note = {},
annote = {ISBN: 3-540-51481-3}
}
@Book{stoer2,
author = {Josef Stoer, Roland Burlisch},
title = {Numerische Mathematik 2},
publisher = {Springer},
year = {1990},
key = {Mathematik},
address = {Berlin},
edition = {3},
note = {},
annote = {ISBN: 3-540-51482-1}
}
Offensichtlich liegt ein Ordnung der Daten vor und man kann sich leicht vorstellen, dass
diese Daten von einem Programm (hier: BibTEX) ausgewertet werden können.
Aufgabe 2.1 Sammeln Sie weitere Ablageformate von Informationen!
Der erste Schritt in Richtung relationale Datenbank wäre nun die Daten in Tabellen
anzuordnen. In diesem Fall wäre der Umbau schnell passiert.
address
edition
dpunkt.verlag
2000
Softwareentwicklung, C, C++
Heidelberg
2
Tipps zur Fehlervermeidung
ISBN:
3-93258876-3
petri1
Marius Apetri
3D-Grafik
Programmierung
mitp
2003
Grafikprogrammierung, C++
Bonn
1
Theorie
+
Praxis
der
Grafikprogrammierung
ISBN:
3-82660891-7
loudon1
Kyle Loudon
Algorithmen in C
O’Reilly
2000
Algorithmen,
Programmierung,
C
Köln
1
Theorie
+
Praxis
von
Standardalgorithmen
ISBN:
3-89721165-3
yarger1
Randay
J.
Yarger,
George Reese,
Tim King
MySQL
O’Reilly
2000
Datenbankprogrammierung,
SQL
Köln
1
MySQL als DBEngine und die
Sprache SQL
ISBN:
3-89721163-7
stoer1
Josef Stoer
Numerische
thematik 1
Me-
Springer
1989
Mathematik
Berlin
5
ISBN:
3-54051481-3
stoer2
Josef Stoer,
Roland Burlisch
Numerische
thematik 2
Me-
Springer
1990
Mathematik
Berlin
3
ISBN:
3-54051482-1
annote
year
Fehlerfrei
programmieren in C
und C++
note
publisher
Oliver Böhm
key
author
boehm1
title
id
Tabelle: buch (1)
Der Aufbau in der Tabellenform ermöglicht nun die direkte Vergleichbarkeit aller gleichartigen Informationen. Alle Verlage oder Erscheinungsdatümer stehen nun untereinan-
Einführung in relationalen Datenbanken und SQL
9
2 Tabellen
2.1 Tabellen
der. Für die Datenverarbeitung kann nun sehr schnell auf eine bestimmte Information
zugegriffen werden. Möchte man auf das Erscheinungsjahr des zweiten Buches zugreifen,
kann der Computer die Stelle sehr schnell errechnen:
Position = Spaltenanzahl × (Zeilennummer − 1) + Spaltennummer
Tragen wir die Daten ein, so ergibt sich für das Erscheinungsjahr des zweiten Datensatzes: Spaltenanzahl = 10, Zeilennummer = 2 und Spaltennummer = 5
Position = 10 × (2 − 1) + 5 = 15
Im 15ten Feld steht somit die gesuchte Information. Die Tabellendarstellung ist unter
anderem wegen dieser Eigenschaft gut für die Verarbeitung mit dem Computer geeignet.
Tabellenspalte
Definition 1 Spalte
Eine Spalte einer Tabelle enthält immer Informationen des selben Typs. Damit meint man Daten
1. des selben technischen Datentyps und
2. zur gleichen inhaltlichen Kategorie.
Jede Spalte hat eine Überschrift, die die Kategorie inhaltlich festlegt und innerhalb der Tabelle eindeutig ist.
Tabellenzeile
Definition 2 Zeile
Eine Zeile einer Tabelle enthält in jeder Spalte inhaltlich zusammenhängende
Informationen. Die Reihenfolge der Spalten ist in jeder Zeile gleich.
Tabelle
Definition 3 Tabelle
Eine Tabelle besteht aus mindestens einer Spalte und einer endlichen Anzahl
von Zeilen. Die Anzahl der Spalten ist ebenfalls endlich.
In diesem Skript werden diese drei Begriffe verwendet: Tabelle, Zeile und Spalte. In der
Literatur findet man aber noch andere, die ebenfalls verwendet werden können (siehe
Tabelle 2.3 auf der nächsten Seite).
Überprüfen wir, ob unsere Tabelle 2.2 auf der vorherigen Seite auch den Definitionen 1, 2
und 3 genügt.
Einführung in relationalen Datenbanken und SQL
10
2 Tabellen
2.2 Exkurs: Hierarchische Datenbanken
Tabelle 2.3: Namensübersicht
Skript
Alternative Namen
Zeile
Tupel, Entity (Entität), Objekt, Datensatz, Record
Spalte
Attribut, Feld, Datenfeld, Item, Property
Tabelle
Matrix, Entitytyp (Entititätentyp), Schema, Relation, Klasse, Recordset
In jeder Spalte müssen die Informationen des gleichen Datentyps und der gleichen inhaltlichen Kategorie vorhanden sein. Dies ist offensichtlich der Fall. Jede Zeile besteht
aus gültigen Attributwerten in den Spalten. Auch dies ist der Fall. Wir haben nirgendwo
Werte, die nicht zu einem Buch passen würden. Die Tabelle selbst besteht tatsächlich
aus Zeilen und Spalten. Das bedeutet, sie hat keine Untertabellen oder ähnlich windige
Dinger zu bieten.
Der Begriff relationale Datenbank kommt somit nicht - wie oft irrtümlich angenommen
wird - von den Beziehungen der Tabellen untereinander, sondern vom Fachbegriff Relation für eine Tabelle. Bei relationalen Datenbanken werden die Daten in Tabellen abgelegt.
Die Bedeutung einer Information ist davon abhängig, in welcher Tabelle diese steht.
Definition 4 Domäne
Als Domäne einer Spalte bezeichnet man die fachlich und formal gültigen
Spaltenwerte (Attributwerte).
2.2 Exkurs: Hierarchische Datenbanken
Betrachten Sie folgenden Dateiauszug:
05
10
15
15
15
10
15
05
10
15
15
12345 Marx
Karl
8594882 37010011 01.12.1990
01.12.1990 +100,00
02.12.1990 +50,00
05.12.1990 +50,00
8594823 37010011 01.05.2000
01.15.2000 +5,00
12346 Engels
Friederich
8236734 37010011 01.05.2001
01.05.2001 +0,00
01.05.2001 +1432,45
Hauptstraße 41 Trier
Sparbuch
Giro
Nebensstraße 3 Bremen
Giro
Aufgabe 2.2 Versuchen Sie die Informationen des Auszugs zu bestimmen.
Einführung in relationalen Datenbanken und SQL
11
Relationale
Datenbank
2 Tabellen
2.3 Schlüssel und Verknüpfungen
Man erkennt eine inhaltliche Struktur, die offensichtlich keine Tabelle ist. Die Daten
haben eine hierarchische Struktur (siehe Abb. 2.1), da die Daten von oben nach unten
organisiert sind. Die Bedeutung einer Information ist davon abhängig, unter welcher
anderen Information sie steht.
00
05
10
15
15
05
10
15
15
10
15
15
Abbildung 2.1: Hierarchische Darstellung
2.3 Schlüssel und Verknüpfungen
2.3.1 Schlüssel und Primärschlüssel
In Tabelle 2.2 auf Seite 9 heißt eine Spalte id und soll offensichtlich eine eindeutige
Benennung (Identifikation) eines Buches darstellen. Eine solche eindeutige Identifikation
braucht man, wenn man auf eine bestimmte Zeile zugreifen möchte. Will man diese Zeile
lesen, löschen oder verändern, so ist es sehr umständlich immer angeben zu müssen:
Lösche die Zeile mit dem Autor ..., dem Titel ... usw.. Man möchte sagen können:
Lösche boehm1. Durch die Eindeutigkeit von boehm1 kann kein Missverständnis darüber
entstehen, welche Zeile gelöscht werden soll.
Definition 5 Schlüssel
Ein Schlüssel ist mindestens eine Spalte einer Tabelle, welche jede Zeile konzeptionell eindeutig macht, d.h. der Attributwert kann per Definition in der
Tabelle nur einmal in der Spalte vorkommen.
Ein Schlüssel kann aus mehreren Spalten zusammengesetzt werden, muss aber
minimal sein. Ein Schlüssel ist dann minimal, wenn er nicht mehr reduziert
werden kann, ohne seine Schlüsseleigenschaft zu verlieren.
Einführung in relationalen Datenbanken und SQL
12
2 Tabellen
2.3 Schlüssel und Verknüpfungen
Definition 6 Primärschlüssel
In einer Tabelle können mehrere Schlüssel vorkommen. Ein Schlüssel wird
herausgehoben und als Primärschlüssel gekennzeichnet. Die anderen Schlüssel nennt man Sekundärschlüssel oder Schlüsselkandidaten.
Was meint konzeptionell eindeutig? Stellen Sie sich vor, in der Buchtabelle sind nur
2 Bücher mit unterschiedlichen Buchtitel erfasst. Dann könnte man auf die Idee kommen,
den Titel als Schlüssel anzusehen. Konzeptionell eindeutig bedeutet, dass es kein Zufall
sein darf, dass die Attributwerte eindeutig sind.
konzeptionell
eindeutig
Aufgabe 2.3 Nennen Sie Schlüssel der Bücherliste in Tabelle 2.4. Wählen Sie einen
Primärschlüssel aus und begründen Sie ihre Wahl!
year
address
edition
Fehlerfrei
programmieren in C
und C++
dpunkt.verlag
2000
Softwareentwicklung, C, C++
Heidelberg
2
Tipps zur Fehlervermeidung
ISBN:
3-93258876-3
petri1
Marius Apetri
3D-Grafik
Programmierung
mitp
2003
Grafikprogrammierung, C++
Bonn
1
Theorie
+
Praxis
der
Grafikprogrammierung
ISBN:
3-82660891-7
loudon1
Kyle Loudon
Algorithmen in C
O’Reilly
2000
Algorithmen,
Programmierung,
C
Köln
1
Theorie
+
Praxis
von
Standardalgorithmen
ISBN:
3-89721165-3
yarger1
Randay
J.
Yarger,
George Reese,
Tim King
MySQL
O’Reilly
2000
Datenbankprogrammierung,
SQL
Köln
1
MySQL als DBEngine und die
Sprache SQL
ISBN:
3-89721163-7
kopka1
Helmut Kopka
Latex
Addison-Wesley
1996
Latex
Bonn
2
Einführung
Latex
ISBN:
3-89319199-2
kopka2
Helmut Kopka
Latex, Band 1
Addison-Wesley
1998
Latex
Bonn
2
Einführung in
Latex, Überarbeitete Auflage
stoer1
Josef Stoer
Numerische
thematik 1
Me-
Springer
1989
Mathematik
Berlin
5
ISBN:
3-54051481-3
stoer2
Josef
Stoer,
Roland
Burlisch
Numerische
thematik 2
Me-
Springer
1990
Mathematik
Berlin
3
ISBN:
3-54051482-1
note
annote
publisher
Oliver Böhm
key
author
boehm1
title
id
Tabelle: buch (2)
in
Hinweis: Wenn man selbst Tabellen erstellt, sollte man zusammengesetzte Primärschlüssel vermeiden. Die neigen dazu Schwierigkeiten zu machen. Eine einfach aber sehr sichere
Einführung in relationalen Datenbanken und SQL
13
ISBN:
3-82731025-3
2 Tabellen
2.3 Schlüssel und Verknüpfungen
Wahl ist die Verwendung einer laufenden Nummer, wie Artikelnummer, Kundennummer
usw.. Sie nehmen wenig Platz ein und müssen fast nie verändert werden.
Gerade bei zusammengesetzten Primärschlüsseln kann das Verändern aber ein Problem
werden. Nehmen wir an, dass der Primärschlüssel einer Adressentabelle Nachname +
Vorname ist. Man hat zwar immer noch das Problem, dass es durchaus mehrere Leute
mit dem gleichen Namen geben kann, aber lassen wir dieses Problem mal beiseite. Nun
heiratet die Person und nimmt einen neuen Nachnamen an. Dadurch ändert sich der
Primärschlüssel dieser Zeile und alle, die auf die Tabelle zugreifen, müssen von dem
neuen Primärschlüssel unterrichtet werden; ein großer Aufwand und eine potentielle
Fehlerquelle.
Bauen wir die Tabelle nun bezüglich der obigen Bemerkungen um:
1. Eindeutschen der Namen
2. isbn ist Primärschlüssel
3. id-Spalte entfällt
In Tabelle 2.5 können Sie das Ergebnis des Umbaus betrachten.
verlag
jahr
ort
auflage
bemerkung
autor
3-932588-76-3
Oliver Böhm
Fehlerfrei programmieren in C und
C++
dpunkt.verlag
2000
Softwareentwicklung, C, C++
Heidelberg
2
Tipps zur Fehlervermeidung
3-8266-0891-7
Marius Apetri
3D-Grafik Programmierung
mitp
2003
Grafikprogrammierung, C++
Bonn
1
Theorie + Praxis der Grafikprogrammierung
3-89721-165-3
Kyle Loudon
Algorithmen in C
O’Reilly
2000
Algorithmen,
Programmierung,
C
Köln
1
Theorie + Praxis von Standardalgorithmen
3-89721-163-7
Randay
J.
Yarger,
George Reese,
Tim King
MySQL
O’Reilly
2000
Datenbankprogrammierung,
SQL
Köln
1
MySQL als DBEngine und die
Sprache SQL
3-89319-199-2
Helmut Kopka
Latex
Addison-Wesley
1996
Latex
Bonn
2
Einführung in Latex
3-8273-1025-3
Helmut Kopka
Latex, Band 1
Addison-Wesley
1998
Latex
Bonn
2
Einführung in Latex, Überarbeitete
3-540-51481-3
Josef Stoer
Numerische Methematik 1
Springer
1989
Mathematik
Berlin
5
-
3-540-51482-1
Josef Stoer,
Roland Burlisch
Numerische Mathematik 2
Springer
1990
Mathematik
Berlin
3
-
titel
isbn
ttichwort
Tabelle 2.5: Tabelle: buch (3)
Einführung in relationalen Datenbanken und SQL
14
2 Tabellen
2.3 Schlüssel und Verknüpfungen
2.3.2 Darstellung als ER-Modell
In der Informatik ist es üblich, dass man ein Modell eines EDV-Systems erstellt. Sie
kennen solche Modelle aus der Programmierung: Programmablaufplan, Struktogramm,
Datenflussdiagramm, UML-Diagramme usw. Die Modellierungstechnik für relationale
Datenbanken ist das Entity Relationship Model oder auch ER-Modell.
ER-Modell
Definition 7 Entity Relationship Model
Das Entity Relationship Model (ER-Modell oder ERM) ist eine grafische Darstellung von Tabellen und ihren Beziehungen untereinander.
Die Symbole1 und die Verwendung des ER-Modells werden in den nachfolgenden Kapiteln mit eingeführt.
Betrachten wir dazu das Beispiel in Abbildung 2.2:
verlag
titel
jahr
autor
isbn
stichwort
ort
buch
auflage
bemerkung
Abbildung 2.2: ER-Modell buch(1)
Man erkennt, dass die Tabelle selbst als ein Rechteck mit dem Tabellennamen buch
dargestellt wird. Die Attribute einer Tabelle werden als Ellipsen mit dem Spaltennamen
titel durch eine einfache Linie mit der Tabelle verbunden. Der Primärschlüssel wird
durch Unterstrich beim Attributnamen isbn hervorgehoben. Spalten, die mehr als einen
Wert enthalten können, werden mehrwertige Spalten genannt; stichwort ist so eine
Spalte. Diese Spalten werden durch eine doppelte Ellipse stichwort dargestellt.
2.3.3 Was ist eine Verknüpfung?
In der Tabelle 2.5 auf der vorherigen Seite findet man unter autor und stichwort Informationen, die mehrfach vorkommen. Für das Lesen der Tabelle durch einen Menschen
ist dies eine gute Sache. Er erkennt sofort, worum es in dem Buch geht und wer es
geschrieben hat. Aus Sicht der EDV ist das nicht so prickelnd.
1
In diesem Skript wird die Notation nach Chen verwendet. Andere Notationen, wie beispielsweise die
Krähenfußnotation, sind auch möglich.
Einführung in relationalen Datenbanken und SQL
15
ChenNotation
2 Tabellen
2.3 Schlüssel und Verknüpfungen
Aufgabe 2.4 Überlegen Sie sich mindestens drei Gründe, die gegen eine Wiederholung
der Stichwörter in Klartext sprechen!
Da wir bei relationalen Datenbanken Informationen in Tabellen organisieren, wird nun
eine neue Tabelle angelegt, die die Autoren enthält. Als Primärschlüssel wählen wir eine
laufende Nummer. Die Tabelle nennen wir autor.
Hinweis: Wenn man den Namen des Primärschlüssels neu festlegt und dieser eine laufende
Nummer ist, ist es eine gute Konvention den Primärschlüsselnamen wie folgt zusammenzusetzten: tabellenname_id; für uns heißt der Primärschlüssel somit autor_id.
Primärschlüsselnamen
Die Spalte darf den gleichen Namen haben wie die ganze Tabelle. Nun kann man in die
Tabelle Buch anstelle der Autoren die Ziffer aus der Spalte autor_id eintragen. Man
erhält folgende Verknüpfung (einige Spalten wurden hier wegen der Übersichtlichkeit
weggelassen):
Tabelle: buch (4)
Tabelle: autor
autor
autor_id
Oliver Böhm
1
Apetri Marius
2
Kyle Loudon
3
Randay J. Yarger
4
George Reese
5
Tim King
6
Helmuth Kopka
7
Josef Stoer
8
Roland Burlisch
9
isbn
autor_id
titel
verlag
jahr
stichwort
3-932588-76-3
1
Fehlerfrei programmieren in C und C++
dpunkt.verlag
2000
Softwareentwicklung,
C++
3-8266-0891-7
2
3D-Grafik Programmierung
mitp
2003
Grafikprogrammierung,
C++
3-89721-165-3
3
Algorithmen in C
O’Reilly
2000
Algorithmen, Programmierung, C
3-89721-163-7
6 , 5 , 4
MySQL
O’Reilly
2000
Datenbankprogrammierung,
SQL
3-89319-199-2
7
Latex
Addison-Wesley
1996
Latex
3-8273-1025-3
7
Latex, Band 1
Addison-Wesley
1998
Latex
3-540-51481-3
8
Numerische Methematik 1
Springer
1989
Mathematik
3-540-51482-1
8 , 9
Numerische Mathematik 2
Springer
1990
Mathematik
Abbildung 2.3: Primärschlüssel - Fremdschlüssel zwischen buch und autor
Es ist klar, warum das Ding nun Verknüpfung heißt. Jede Zeile in der Tabelle autor
ist eindeutig durch den Primärschlüssel autor_id gekennzeichnet. Wird dieser Primärschlüsselwert nun in die Tabelle buch eingetragen, wird auf die entsprechende Zeile
verwiesen; die Einträge werden verknüpft.
Definition 8 Fremdschlüssel
Ein Fremdschlüssel ist mindestens eine Spalte der Tabelle A, welche Attributwerte des Primärschlüssels der Tabelle B enthält.
In unserem Fall enthält die Spalte autor_id Fremdschlüssel auf die Tabelle autor.
Aufgabe 2.5 Man mache sich klar, dass diese Definition auch für A = B gilt!
Einführung in relationalen Datenbanken und SQL
16
C,
2 Tabellen
2.3 Schlüssel und Verknüpfungen
Hinweis: Der Name des Fremdschlüssels sollte der gleiche sein, wie der Name des entsprechenden Primärschlüssels. In unserem Fall wäre ein Änderung des Namen Autor
in autor_id sinnvoll. Man erkennt so unmittelbar, auf welche Tabelle sich der Fremdschlüssel bezieht, wenn die Namenskonvention für die Primärschlüssel eingehalten wurde.
Referenz
Definition 9 Verknüpfung
Eine Verknüpfung oder Referenz entsteht durch die Verwendung von Fremdschlüsseln.
Durch die Verwendung des Fremdschlüssels autor_id in der Tabelle buch entsteht die
Verknüpfung zur Tabelle autor.
Man erkennt die Vorteile der Organisation durch Verknüpfungen unmittelbar:
• Es wird im System viel weniger Speicherplatz verbraucht.
• Eine Änderung (Korrektur) muss nur an einer Stelle vorgenommen werden.
• Informationen können nicht in unterschiedlichen Schreibweisen vorkommen.
Das gleiche Verfahren kann auch für Stichwörter und Verlage angewendet werden. Wir
erhalten:
Aufgabe 2.6 Tragen Sie die passenden Fremdschlüsselattributwerte ein und zeichnen
Sie die Verknüpfungen!
Verknüpfungen werden im ER-Modell durch eine Raute dargestellt. Welche Art von
Beziehung zwischen den Tabellen besteht, muss noch genauer untersucht werden. Das
vorläufige ER-Modell für die vier Tabellen buch, stichwort, autor und verlag kann
man in Abbildung 2.5 auf Seite 19 sehen.
In die Rauten der Verbinder schreibt man einen Text, der die Qualität der Verknüpfung
beschreibt. Das Stichwort passt zum Buch, der Autor schreibt das Buch und der Verlag
gibt das Buch heraus.
Weiter ist wichtig, das die Fremdschlüssel nicht mehr in den Tabellen angezeigt werden.
Dies erklärt sich aus der anderen Sichtweise, die ein ER-Modell hat. Das ER-Modell ist
eine logische Sicht auf die Datenbank und keine physische. Es wird ausgedrückt, dass
es eine Verknüpfung zwischen Verlag und Buch gibt. Wie diese realisiert wird - durch
Fremdschlüssel oder Wunder - lässt das ER-Modell offen. Dem Programmierer ist klar,
dass er diese Verknüpfung durch Fremdschlüssel realisieren muss. Es kann daher darauf
verzichtet werden, das ER-Modell durch die Fremdschlüssel aufzublähen2 .
2
In bestimmten Situationen - wie Klausuren, Prüfungen etc. - kann es notwendig sein, die Fremdschlüssel anzugeben!
Einführung in relationalen Datenbanken und SQL
17
2 Tabellen
2.3 Schlüssel und Verknüpfungen
Tabelle: stichwort
Tabelle: buch (5)
isbn
autor_id
titel
verlag_id
jahr
stichwort_id
stichwort_id
stichwort
1
Softwareentwicklung
3-932588-76-3
Fehlerfrei programmieren in C und C++
2000
2
Programmierung
3-8266-0891-7
3D-Grafik Programmierung
2003
3
C
3-89721-165-3
Algorithmen in C
2000
4
C++
3-89721-163-7
MySQL
2000
5
Grafikprogrammierung
3-89319-199-2
Latex
1996
6
Algorithmen
3-8273-1025-3
Latex, Band 1
1998
7
Datenbankprogrammierung
3-540-51481-3
Numerische Mathematik 1
1989
8
SQL
3-540-51482-1
Numerische Mathematik 2
1990
9
Latex
10
Mathematik
Tabelle: autor
Tabelle: verlag
autor_id
autor
verlag_id
verlag
erscheinungsort
1
Oliver Böhm
1
dpunkt.verlag
Heidelberg
2
Apetri Marius
2
mitp
Bonn
3
Kyle Loudon
3
O’Reilly
Köln
4
Randay J. Yarger
4
Addison-Wesley
Bonn
5
George Reese
5
Springer
Berlin
6
Tim King
7
Helmuth Kopka
8
Josef Stoer
9
Roland Burlisch
Abbildung 2.4: Primärschlüssel - Fremdschlüssel zwischen buch anderen Tabellen
2.3.4 Kardinalitäten
Es gibt verschiedene Arten von Verknüpfungen. Sie ergeben sich aus dem Mengenverhältnis zwischen den Zeilen verknüpfter Tabellen.
2.3.4.1 1:1-Verknüpfungg
Betrachten Sie bitte die Tabellen in Abbildung 2.6 auf der nächsten Seite:
Aufgabe 2.7 Zeichnen Sie die Referenzen zwischen den Tabellen ein!
Man erkennt, dass jede Zeile der Tabelle person mit genau einer Zeile aus der Tabelle
ausweis verknüpft ist. Man kann auch sagen, dass jede Zeile der Tabelle ausweis mit
genau einer Zeile aus der Tabelle person verknüpft ist. Eine solche Verknüpfung von
Tabellen nennt man 1:1 -Verknüpfung.
Einführung in relationalen Datenbanken und SQL
18
1:1
2 Tabellen
2.3 Schlüssel und Verknüpfungen
isbn
titel
jahr
stichwort_id
stichwort
auflage
buch
passt zu
schreibt
gibt heraus
stichwort
bemerkung
autor
autor
autor_id
verlag
ort
verlag_id
verlag
Abbildung 2.5: ER-Modell Buch(2)
Tabelle: person
Tabelle: ausweisdaten
nachname
vorname
verheiratet
kinder
personalausweisnummer
personalausweisnummer
austellungsort
Müller
Hans
Nein
0
1212121212
12121212
Bochum
gültig bis
01.01.2005
Schmidt
Peter
Ja
2
1313131313
13131313
Dortmund
17.09.2020
Meier
Gundula
Nein
1
1414141414
14141414
Gelsenkirchen
29.02.2010
Abbildung 2.6: Primärschlüssel - Fremdschlüssel zwischen person und ausweis
Definition 10 1:1-Verknüpfung
Zwei Tabellen A und B stehen in einer 1:1-Verknüpfung, wenn es zu jeder
Zeile aus der Tabelle A höchstens eine Zeile in der Tabelle B gibt und wenn
es zu jeder Zeile aus der Tabelle B höchstens eine Zeile in der Tabelle A gibt.
Das entsprechende ER-Modell ist in Abbildung 2.7 auf der nächsten Seite zu sehen.
Wie realisiert man 1:1-Verknüpfung? Eine 1:1-Verknüpfung wird in der Regel durch
einen gemeinsamen Primärschlüssel realisiert. Bei den Ausweisdaten ist die Personalausweisnummer der gemeinsame Primärschlüssel.
Aufgabe 2.8 Beschreiben Sie weitere Möglichkeiten eine 1:1-Verknüpfung zu realisieren.
Einführung in relationalen Datenbanken und SQL
19
2 Tabellen
2.3 Schlüssel und Verknüpfungen
ausstellungsort
nachname
vorname
gültig bis
verheiratet
person
1
gehört
personalausweis
1
kinder
personalausweisnummer
personalausweisnummer
Abbildung 2.7: ER-Modell person - ausweis
Aufgabe 2.9 Man mache sich klar, dass, wenn zwei Tabellen den gleichen Primärschlüssel haben, eine 1:1-Verknüpfung vorliegen muss!
Hinweis: Diese Formulierung lässt zu, dass es zu einem Datensatz in der Tabelle A keinen
in der Tabelle B gibt und umgekehrt.
Aufgabe 2.10 Diskutieren Sie die Möglichkeit 1:1-Verknüpfungen zu einer Tabellen zusammenzufassen!
2.3.4.2 1:n-Verknüpfung
Tabelle: bankkunde
Tabelle: bankkonto
nachname
vorname
geburtsjahr
bankkunde_knr
bankkunde_knr
kontonummer
stand
gesperrt
Müller
Hans
1950
965489
965489
1000001
-60,23e
Nein
Schmidt
Peter
1963
732984
965489
1000002
+12099,20e
Nein
Meier
Gundula
1974
665238
965489
1000003
+10,12e
Nein
732984
2000001
-234,98e
Ja
732984
2000002
+123,90e
Ja
Abbildung 2.8: Primärschlüssel - Fremdschlüssel zwischen bankkunde und konto
Aufgabe 2.11 Zeichnen Sie die Referenzen der Tabellen in Abbildung 2.8 ein!
Sie können sehen, dass ein Kunde 3 Bankkonten, ein Kunde 2 Bankkonten und ein Kunde
kein Bankkonto hat. Die Verknüpfung zwischen diesen Tabellen ist somit derart, dass
es zu jeder Zeile in der Tabelle bankkunde beliebig viele Zeile in der Tabelle bankkonto
gibt und es zu jeder Zeile in der Tabelle bankkonto genau eine Zeile in der Tabelle
bankkunde gibt3 .
Das entsprechende ER-Modell ist in Abbildung 2.9 auf der nächsten Seite zu sehen. Auch
hier kann man sehen, dass der Fremdschlüssel nicht im ER-Modell angegeben wird.
Einführung in relationalen Datenbanken und SQL
20
2 Tabellen
2.3 Schlüssel und Verknüpfungen
vorname
stand
nachname
gesperrt
geburtsjahr
bankkunde 1
unterhält
kundennummer
n bankkonto
kontonummer
Abbildung 2.9: ER-Modell bankkunde - bankkonto
1:n
Definition 11 1:n-Verknüpfung
Zwei Tabellen A und B stehen in einer 1:n-Verknüpfung, wenn es zur jeder
Zeile aus der Tabelle A beliebig Zeilen in der Tabelle B gibt und wenn es zu
jeder Zeile aus der Tabelle B genau eine Zeile in der Tabelle A gibt.
Hinweis: Diese Formulierung lässt zu, dass es zu einem Datensatz in der Tabelle A keinen
in der Tabelle B gibt, aber nicht umgekehrt.
Aufgabe 2.12 Tragen Sie im ER-Modell 2.5 auf Seite 19 die 1:n-Verknüpfungen ein.
Wie realisiert man 1:n-Verknüpfung? Wenn man das ER-Modell vor sich hat, gibt
es bei der 1:n-Verknüpfung immer eine Tabelle, wo die 1 steht und eine Tabelle, wo
das n steht. Dort wo das n steht, wird der Primärschlüssel der anderen Tabelle als
Fremdschlüssel aufgenommen.
Das bedeutet in unserem Beispiel: In der Tabelle bankkonto gibt es den Fremdschlüssel
bankkunde_knr.
Aufgabe 2.13 Natürlich könnte man sich auch überlegen, in der Tabelle bankkunde
eine Spalte kontonummer einzufügen und dort per Liste alle Konten einzutragen. Nennen
Sie die Nachteile dieses Verfahrens!
3
Dies setzt inhaltlich voraus, dass ein Konto nur von einem Kunden verwendet werden kann; tun wir
einfach mal so.
Einführung in relationalen Datenbanken und SQL
21
2 Tabellen
2.3 Schlüssel und Verknüpfungen
2.3.4.3 n:m-Verknüpfung
Aufgabe 2.14 Finden Sie anhand der Definitionen 10 auf Seite 19 und 11 auf der
vorherigen Seite heraus, ob es sich bei der Verknüpfung von Buch und Stichwort um
eine der beiden Verknüpfungsarten handelt. Begründen Sie!
Betrachten wir die Zutaten eines Kuchens4 . Aus dem ER-Modell 2.10 kann man entnehmen, dass es sich um eine n:m-Verknüpfung handelt.
zutat_id
kuchen_id
kuchen
n
beinhaltet
m
name
zutat
name
Abbildung 2.10: ER-Modell kuchen
n:m
Definition 12 n:m-Verknüpfung
Zwei Tabellen A und B stehen in einer n:m-Verknüpfung, wenn es zur jeder
Zeile aus der Tabelle A beliebig viele Zeilen in der Tabelle B gibt und wenn
es zu jeder Zeile aus der Tabelle B beliebig viele Zeilen in der Tabelle A gibt.
Aufgabe 2.15 Tragen Sie im ER-Modell 2.5 auf Seite 19 die n:m-Verknüpfungen ein.
Wie realisiert man n:m-Verknüpfung? Eine n:m-Verknüpfung wird über eine Hilfstabelle realisiert. In dieser Hilfstabelle werden die Primärschlüssel der beiden zu verknüpfenden Tabellen als Fremdschlüssel eingetragen.
Aufgabe 2.16 Betrachten Sie dazu das Beispiel Backrezept. Was für eine Kardinalität
besteht zwischen den zu verknüpfenden Tabellen und der Hilfstabelle?
Aufgabe 2.17 Erweitern Sie dieses Beispiel um alle Zutaten für einen Käse-, Marmorund Pflaumenkuchen. Erweitern Sie die Verknüpfungstabelle (Hilfstabelle) entsprechend
und verbinden Sie die Fremdschlüsselwerte mit den passenden Primärschlüsselwerten.
4
Lassen wir dabei der Einfachheit halber Menge und Zubereitung weg :-)
Einführung in relationalen Datenbanken und SQL
22
2 Tabellen
2.3 Schlüssel und Verknüpfungen
Tabelle: zutat_kuchen
zutat_id
kuchen_id
name
1
1
Mehl
1
2
1
Ei
2
3
1
Zucker
3
4
1
Butter
4
6
1
Milch
5
1
2
Tabelle: zutat
zutat_id
Kakao
Hefe
6
7
2
2
3
2
4
2
Tabelle: kuchen
kuchen_id
name
1
Marmorkuchen
2
Käsekuchen
3
Pflaumenkuchen
Abbildung 2.11: Primärschlüssel - Fremdschlüssel zwischen kuchen und zutat
2.3.5 Aufgaben zum ER-Modell
Aufgabe 2.18 Erstellen Sie ein ER-Modell für folgenden Anforderungen: Für eine Bücherei sollen Kunden, Bücher und ein Verleihvorgang in eine Datenbank abgelegt werden.
Die Darstellung der Bücher soll sich an obigen Daten orientieren. Zum Kunden sollen
die üblichen Stammdaten, wie Name etc. erfasst werden. Der Verleihvorgang besteht aus
den Informationen: Wer hat was von wann bis wann ausgeliehen.
Aufgabe 2.19 Eine Clown-Agentur beauftragt Sie mit der Erstellung einer Clown Datenbank. Diese soll Informationen über Clowns, ihre Verträge etc. enthalten. Erstellen
Sie ein ER-Modell für die Tabellen clown, vertrag, veranstalter und programm. In clown
sind die Basisdaten des Künstlers erfasst, veranstalter analog. In vertrag werden Ort
und Termin eines Auftritts festgelegt. Jeder Clown bietet mehrere Programme an. Bei
einigen Programmen arbeiten mehrere Clowns zusammen. Es kann im Vertrag festgelegt
werden, welche der angebotenen Programme bei der Veranstaltung vorgeführt werden
sollen!
Aufgabe 2.20 Für den deutschen Brauereiverband soll eine Datenbank, die über alle
Biere Auskunft geben kann, entwickelt werden. Erstellen Sie ein ER-Modell für die Tabellen bier, brauerei, grosshaendler und brauart. Großhändler führen mehrere Biere im
Sortiment!
Einführung in relationalen Datenbanken und SQL
23
2 Tabellen
2.3 Schlüssel und Verknüpfungen
Aufgabe 2.21 Ein mittelständiges Unternehmen möchte seine Kunden über ihre Zufriedenheit befragen. Erstellen Sie ein ER-Modell für eine Fragebogenaktion. Verwenden
Sie folgende Tabellen: frage, antwort und ueberschrift (mehrere Fragen werden unter
einer Überschrift zusammengefasst). Jeder Fragebogen soll dem entsprechenden Kunden
zugeordnet werden können.
Aufgabe 2.22 Im Rahmen eines Hotel-Software Projektes werden Sie gebeten, die Datenhaltungsschicht zu programmieren. Erstellen Sie ein ER-Modell nach Auszug aus dem
Lastenheft: Die Anwendung soll folgende Bereiche abdecken:
1. Kundenverwaltung
Zu jedem Kunden werden seine persönlichen Daten, sowie Rechnungsanschriften
erfasst. Falls Kunden einer bestimmten Firma angehören, sollen diese einer Firma
zugeordnet werden können, um spätere Rabatte berechnen zu können.
2. Raumreservierung
Für jeden Raum wird seine Bezeichnung, Ausstattung und Tarifgruppe erfasst.
Ebenso sollen pro Raum mehrere Reservierungszeiträume erfasst werden können.
Diese Zeiträume sollen einem Kunden zugeordnet werden.
3. Rechnungsstellung.
Für jeden Kunden soll anhand der Belegzeiten und sonstiger Leistungen (Mini-Bar,
Sauna etc.) eine Rechnung zusammengestellt werden können.
Aufgabe 2.23 Sie sollen Auswertungen in einer CD-Sammlung programmieren. Gehen
Sie von folgendem Sachverhalt aus: Es gibt die Tabellen: cd, titel und interpret. Erstellen
ein sinnvolles ER-Modell.
Aufgabe 2.24 Erstellen Sie ein ER-Modell passend für folgenden Lastenheftauszug:
Für ein Bestellwesen sollen die Bestell- und Kundendaten in einer Datenbank abgelegt
werden. Zu einem Kunden werden Name und Ort erfasst. Jeder Kunde soll anhand einer eindeutigen Identifikation ermittelt werden können. Jeder Kunde kann Bestellungen
aufgeben. Zu jeder Bestellung werden das Bestelldatum und eine Bestellnummer erfasst.
Der Kunde gibt an, welche Artikel er bestellt hat. Die Artikel werden als Positionen der
Bestellung zugeordnet. Zu jeder Position wird die Bestellmenge mit erfasst. Zu jedem
Artikel wird der Stückpreis und die Bezeichung gespeichert. Jeder Artikel soll eindeutig
identifiziert werden können.
Aufgabe 2.25 Erstellen Sie zu dem Tabellenauszug in Abbildung 2.12 auf der nächsten
Seite ein passendes ER-Modell.
Einführung in relationalen Datenbanken und SQL
24
2 Tabellen
2.3 Schlüssel und Verknüpfungen
Tabelle: kunde
Tabelle: adresse
name
kunde_knr
id
kunde_knr
strasse
plz
ort
Hans Herbet Schmidt
1
1
1
Feldweg 7
90909
Kaff
Jochen Schibulsky
2
2
1
Hochstrasse 12,
90909
Kaff
Manfred Gianonatti
3
3
5
Ruhrschnellweg 1,
44879
Bochum
Gundula Schauinsfeld
4
4
2
Kleine Gasse 3,
22122
Hallig
Angelique Börger
5
4
3
Kleine Gasse 3,
22122
Hallig
Abbildung 2.12: Datenauszug von kunde und adresse
Aufgabe 2.26 Erstellen Sie ein ER-Modell für die Tabellen klasse, schueler, lehrer,
raum, fach und stundenplan. In klasse sind die Daten einer Schulklasse erfasst. In lehrer und schueler die entsprechenden Stammdaten. In der Tabelle fach werden die Fächer
erfasst. Die Tabelle stundenplan enthält alle Stundenplandaten für alle Schuljahre. Jeder
Stundenplaneintrag enthält neben anderen Informationen die Angaben: Uhrzeit, Schuljahr und Wochentag.
Wie muss dass ER-Modell abgeändert werden, damit ein Schüler mehrere Klassen besuchen kann?
2.3.6 Referentielle Integrität
Tabellen werden über Primär-/Fremdschlüsselpaare mit einander verknüpft. Mit anderen
Worten, wenn man eine Datenbank als ein fein verknüpftes Netzwerk von Tabellenzeilen
versteht, sind diese Primär-/Fremdschlüsselpaare die Knoten. Und was passiert, wenn
diese Knoten sich lösen oder die Enden falsch verknüpft werden?
Betrachten Sie das Beispiel in Abbildung 2.13.
Tabelle: kunde
Tabelle: adresse
name
kunde_knr
id
kunde_knr
strasse
plz
ort
Hans Herbet Schmidt
1
1
1
Feldweg 7
90909
Kaff
Jochen Schibulsky
2
2
1
Hochstrasse 12,
90909
Kaff
Manfred Gianonatti
3
3
6
Ruhrschnellweg 1
44879
Bochum
Gundula Schauinsfeld
4
4
2
Kleine Gasse 3
22122
Hallig
Angelique Börger
5
5
3
Kleine Gasse 3
22122
Hallig
Abbildung 2.13: Datenauszug von kunde und adresse
Aufgabe 2.27 Verbinden Sie alle Primär-/Fremdschlüsselpaare. Was fällt auf ?
Einführung in relationalen Datenbanken und SQL
25
2 Tabellen
2.3 Schlüssel und Verknüpfungen
Definition 13 Referentielle Integrität
Wenn es zu jedem Attributwert eines Fremdschlüssels einen passenden Attributwert des entsprechenden Primärschlüssels gibt, ist die Verknüpfung (Referenz) integer.
Ist diese Bedingung nicht erfüllt, spricht man von einer verletzten referentiellen Integrität.
Eine Verletzung der referentiellen Integrität kann aus vielen Gründen passieren. Ich
werde hier einige anhand des Bankkontobeispiels erläutern:
• Der Kunde mit der Kundennummer 963680 ist in der Tabelle bankkunde gelöscht
worden. In vielen Systemen kann man das DBMS anweisen, alle Zeilen, die mit
einer zu löschenden Zeile verknüpft sind, ebenfalls zu löschen. Man spricht hier
von kaskadierendem Löschen oder Löschweitergabe. Dieses Feature ist mit großer
Vorsicht zu genießen. Beispiel: Sie löschen einen Bankkunden. Dadurch werden
alle Konten des Kunden gelöscht. Dadurch werden alle Buchungen des Kunden
gelöscht. Dadurch werden alle Buchungen bei anderen Kunden gelöscht, die mit
den gelöscht verknüpft waren. Dadurch werden alle Einträge in anderen Tabellen, wie z.B. Kreditrückzahlungen gelöscht usw. Das Löschen von Datensätzen
ist bei nicht trivialen Systemen das Schwierigste, was man sich vorstellen kann.
Aus diesen Gründen wird in vielen Programmierrichtlinien das Verwenden der
Löschweitergabe untersagt. Oftmals wird überhaupt nicht gelöscht! Man fügt den
Tabellen standardmäßig eine Spalte mit dem Namen aktiv oder deleted hinzu.
Diese Spalte dient als Löschkennzeichen.
• Der Kunde mit der Kundennummer 665238 hatte vorher die Nummer 963680.
Der Primärschlüsselattributwert hat sich also geändert. Wie bei der Löschweitergabe, kann man man bei den meisten DBMSen eine Änderungsweitergabe oder
kaskadierende Änderung verwenden. Die Folgen sind zwar in der Regel nicht ganz
so katastrophal, können aber auch schon gehörigen Schaden anrichten. Beispiel: Die
Kundennummer hat sich geändert. In allen archivierten Schriftverkehren steht aber
die alte Kundennummer. Das gleiche gilt für elektronisch signierte Archive (z.B. für
die Steuer). Man vermeidet das Problem, indem man nichtinformationstragende
Primärschlüssel verwendet. Laufende Nummern sind deshalb sehr gute Primärschlüssel. Schlüssel, die sich aus informationstragenden Spalten zusammensetzen,
laufen immer Gefahr, dass sie sich ändern müssen (z.B. geändertes Kennzeichen
bei einem PKW).
• Es sollten zwei Datensätze eingefügt werden: Einen neuen Kunden und das neue
Konto. Diese Operation ist aber unvollständig durchgeführt worden (z.B. durch
einen Absturz oder Timeout). Dieses Problem vermeidet man durch die Verwendung von Transaktionen. Eine Folge von SQL-Befehlen werden als Teil einer Transaktion gekennzeichnet. Diese SQL-Operationen werden dann simuliert ausgeführt.
Einführung in relationalen Datenbanken und SQL
26
kaskadierendes
Löschen
Löschweitergabe
Löschkennzeichen
Änderungsweiter
kaskadierende
Änderung
Transaktion
2 Tabellen
2.3 Schlüssel und Verknüpfungen
Am Ende überprüft das System, ob alle Befehle der Transaktion vollständig und
korrekt ausgeführt wurden. Erst dann werden die Daten in die Tabellen übernommen (commit). War die Transaktion nicht erfolgreich, wird der Zustand vor der
Transaktion wieder hergestellt (rollback).
Natürlich können auch andere Gründe eine verletzte referentielle Integrität verursachen;
die hier vorgestellten Gründe sind aber die häufigsten.
Ist die Integrität einmal kaputt, hat man ein echtes Problem :-( In der Regel ist es
nur mit sehr kostenintensiven manuellen Analysen möglich, die verbogenen Referenzen
wieder gerade zu biegen. Also: Vorsicht bei Operationen, die direkt oder indirekt den
Primärschlüssel betreffen.
2.3.7 Normalformen
Der inhaltliche Aufbau von Tabellen ist in den Anfängen der Datenbankentwicklung
sehr intuitiv erfolgt. Dies hatte zur Folge, dass viele Datenbanken im Laufe der Zeit
sehr langsam im Zugriff wurden und die Konsistenz der Daten abnahm. Ebenso kam
es oft vor, dass Daten redundant5 vorkamen und somit an allen Stellen gepflegt werden mussten. Man suchte Wege, Datenbanken grundsätzlich so zu gestalten, dass diese
Probleme erst gar nicht entstehen. Das Ergebnis ist die Formulierung sogenannter Normalformen. Die Anpassung einer bestehenden Datenbank an die Normalformen nennt
man Normalisierung.
2.3.7.1 Normalform 1
Aufgabe 2.28 Betrachten Sie die Inhalte der Bestelltabelle 2.6 und diskutieren Sie die
möglichen Nachteile. Gibt es auch Vorteile?
Tabelle: bestellung (1)
5
id
knr
positionen
1
4712
5 Towergehäuse, 3 CD-ROM Laufwerke, 2 DVD Laufwerke
2
4712
10m Netzwerkkabel, 25 Anschlussdosen
3
10001
5 DVD Laufwerke, 5m Netzwerkkabel
mehrfach, überflüssig
Einführung in relationalen Datenbanken und SQL
27
COMMIT
ROLLBACK
2 Tabellen
2.3 Schlüssel und Verknüpfungen
Die Attributwerte der Spalte positionen sind im Grunde Listen und sie wiederholen
sich inhaltlich. Dies will man verhindern, indem man fordert, dass die Attributwerte
atomar sind.
Definition 14 Atomar
Ein Attributwert ist atomar, wenn er nicht mehr in Teilinformationen zerlegt
werden kann, ohne seinen Sinn zu verlieren. Eine Tabelle heißt atomar, wenn
alle ihre Attributwerte atomar sind.
Ein weiterer Nachteil ist die Wiederholung von Artikeltexten, man spricht hier von
Wiederholungsgruppen.
Definition 15 Wiederholungsgruppefreiheit
Eine Wiederholungsgruppe ist eine Liste von Informationen des selben inhaltlichen Typs. Eine Tabelle ist dann wiederholungsgruppenfrei, wenn alle
ihre Attributwerte wiederholungsgruppenfrei sind.
Definition 16 Normalform 1
Eine Tabelle ist dann in der 1. Normalform, wenn sie atomar und wiederholungsgruppenfrei ist.
Die Tabelle müsste somit in zwei Schritten normalisiert werden:
1. Auflösen der Wiederholungsgruppe (menge, artikelname)
2. Herstellen der Atomarität durch Aufteilung in menge und artikelname
Hinweis: Die Werte dürfen nicht zufällig atomar oder wiederholungsgruppenfrei sein.
Aufgabe 2.29 Erstellen Sie ein ER-Modell für das Beispiel in der Tabelle 2.6 auf der
vorherigen Seite, welches der 1. Normalform entspricht.
2.3.7.2 Normalform 2
Aufgabe 2.30 Betrachten Sie die Inhalte der Buchtabelle 2.7 auf der nächsten Seite. Entspricht die Tabelle der 1. Normalform? Hinweis: Die ISBN setzt sich wie folgt
zusammen.
1. Gruppe Sprachkennung: 0, 1 = Englisch; 2 = Französich; 3 = Deutsch; 4 = Japanisch; usw.
Einführung in relationalen Datenbanken und SQL
28
2 Tabellen
2.3 Schlüssel und Verknüpfungen
2. Gruppe Verlagsnummer: 8237 = Stam; 89721 = O’Reilly; 446 = Hanser; usw.
3. Gruppe Laufende Nummer: Diese Nummer wird vom Verlag intern vergeben.
P9
4. Gruppe Prüfziffer (ISNB-10): (
Position ist.
i=1
i · zi )mod 11, wobei zi die Ziffer an der i -ten
Gegen welche weitere Definition verstößt diese Tabelle?
Tabelle: buch (6)
isbn PK
titel
verlag
jahr
stichwort
3-932588-76-3
Fehlerfrei programmieren in C und C++
dpunkt
2000
Softwareentwicklung, C, C++
3-8266-0891-7
3D-Grafik
mierung
mitp
2003
Grafikprogrammierung, C++
3-89721-165-3
Algorithmen in C
O’Reilly
2000
Algorithmen, Programmierung, C
3-89721-163-7
MySQL
O’Reilly
2000
Datenbankprogrammierung, SQL
3-89319-199-2
Latex
AddisonWesley
1996
Latex
3-8273-1025-3
Latex, Band 1
AddisonWesley
1998
Latex
3-540-51481-3
Numerische Methematik 1
Springer
1989
Mathematik
3-540-51482-1
Numerische Mathematik 2
Springer
1990
Mathematik
Program-
Es fällt auf, dass die Attributwerte der Spalte verlag sich wiederholen. Grund ist, dass
der Verlagsname nicht etwa vom gesamten Primärschlüssel abhängt - und damit potentiell einmalig ist -, sondern nur von einem Teil - und deshalb mehrfach vorkommt.
Definition 17 Voll- und teilfunktional
Wenn die Attributwerte einer Spalte nur von einem Teil des Primärschlüssels abhängen, ist diese Spalte teilfunktional. Ist dies nicht der Fall, ist sie
vollfunktional.
Teilfunktionalität tritt nur bei zusammengesetzten6 Primärschlüsseln auf. Laufende Zähler sind - ausser bei perversen Gegenbeispielen - davon nicht betroffen.
6
siehe Definition 5 auf Seite 12: Ein Schlüssel kann aus mehreren Spalten bestehen.
Einführung in relationalen Datenbanken und SQL
29
2 Tabellen
2.3 Schlüssel und Verknüpfungen
Definition 18 Normalform 2
Eine Tabelle ist dann in der 2. Normalform, wenn sie den Bedingungen der
1. Normalform entspricht und alle Nichtschlüsselspalten vollfunktional sind.
Aufgabe 2.31 Erstellen Sie ein ER-Modell für das Beispiel aus Tabelle 2.7 auf der
vorherigen Seite, welches der 2. Normalform entspricht.
2.3.7.3 Normalform 3
Aufgabe 2.32 Lassen sich in Tabelle 2.8 Abhängigkeiten von Nichtschlüsselspalten festellen?
Tabelle: buchung (1)
id
quelle_ktnr
quelle_blz
12
54842
46062817
13
58627
14
25823
15
quelle_bank
ziel_ktnr
ziel_blz
ziel_bank
betrag
VB Schmallenberg
54568
40040028
Commerzbank Münster
-60,23e
43050001
Sparkasse Bochum
85674
40040028
Commerzbank Münster
+12099,20e
43050001
Sparkasse Bochum
67831
46062817
VB Schmallenberg
+10,12e
54842
10010010
Postbank Berlin
67831
46062817
VB Schmallenberg
+54,02e
16
54842
10010010
Postbank Berlin
67831
46062817
VB Schmallenberg
-234,98e
17
25823
43050001
Sparkasse Bochum
67831
46062817
VB Schmallenberg
+123,90e
Wenn nicht Nichtschlüsselspalten aus anderen Nichtschlüsselspalten herleitbar sind, bedeutet dies in der Regel, dass Informationen redundant in der Tabelle gehalten werden.
Wie in Tabelle 2.8 zu sehen, werden die Banknamen mehrfach genannt. Dies verbraucht
nicht nur Speicherplatz, sondern macht eine Änderung der Banknamen teuer.
Definition 19 Transitiv
Eine Nichtschlüsselspalte ist transistiv, wenn sie sich aus anderen Nichtschlüsselspalten herleiten lässt.
Definition 20 Normalform 3
Eine Tabelle entspricht der 3. Normalform, wenn sie den Bedingungen der
2. Normalform entspricht und keine transitiven Spalten hat.
Aufgabe 2.33 Erstellen Sie ein ER-Modell für Tabelle 2.8, so dass diese der 3. Normalform entspricht.
Einführung in relationalen Datenbanken und SQL
30
3 Implementieren mit SQL
3.1 Was ist SQL?
Die Abkürzung SQL steht für Structured Query Language 1 . SQL ist eine Sprache zu
Bearbeitung und Auswertung von relationalen Datenbanken. Sie umfasst drei Bereiche:
1. Data Definition Language (DDL): Befehlssatz zum Anlegen, Ändern und Löschen
von Datenbanken, Tabellen usw. und ihren Strukturen.
2. Data Manipulation Language (DML): Befehlssatz zum Einfügen, Ändern, Löschen
und Auslesen von Daten aus den Tabellen.
3. Data Control Language (DCL): Befehlssatz zur Administration von Datenbanken2 .
Anders als bei imperativen Programmiersprachen wie C#, C++, Java oder Pascal wird
durch die Befehle nicht die Art und Weise bestimmt, wie man ein Ergebnis erhält; es
wird kein Algorithmus implementiert. Vielmehr sagt man, was man haben möchte und
der Datenbankserver ermittelt das Ergebnis. Solche Arten von Programmiersprachen
nennt man deklarativ.
Obwohl es viele SQL-Dialekte gibt, ist der offizielle SQL-Standard in vielen Systemen
implementiert und garantiert eine leichte Wiederverwendbarkeit oder Übertragbarkeit
der Befehle. 1986 wurde der erste SQL-Standard vom ANSI verabschiedet (der 1987
von der ISO ratifiziert wurde). 1992 wurde der Standard überarbeitet und als SQL92 (oder auch SQL2) veröffentlicht. Alle aktuellen Datenbanksysteme halten sich im
wesentlichen an diese Standardversion. Die Version SQL:1999 (ISO/IEC 9075:1999, auch
SQL3 genannt) ist noch nicht in allen Datenbanksystemen implementiert. Das gilt auch
für die nächste Version SQL:2003. Der aktuelle Standard wurde 2008 unter SQL:2008
verabschiedet.3 .
3.2 MySQL
MySQL ist ein frei verfügbarer Datenbankserver; er kann unter http://www.mysql.com
heruntergeladen werden. Eine weitere Quelle ist http://www.apachefriends.org. Dort
1
Aussprache: EsKjuEl; die Aussprache SiQwL ist nicht korrekt, da damit der Vorläufer von SQL
SEQUEL gemeint ist.
2
Nicht zur Administration des Servers!
3
Quelle: http://de.wikipedia.org/wiki/SQL, Stand 10.2011
Einführung in relationalen Datenbanken und SQL
31
imperativ
deklarativ
3 Implementieren mit SQL
3.3 SQL-DDL
kann man ein Paket herunterladen, welches nach der Installation einen vorkonfigurierten
Web-Server (Apache), einen Datenbankserver (MySQL) und die Programmiersprachen
PHP und Perl zur Verfügung stellt4 . Linux-Distributionen liefern diese Pakete in der
Regel mit und daher sollten diese über die Installationsskripte der Distribution installiert
werden.
Die hier gezeigten Beispiele sind alle unter MySQL Server 5.1.53 unter OpenSuse 11.3
getestet worden. Als Beispiel wird die Sakila-Datenbank von MySQL (http://dev.
mysql.com/doc) verwendet5
Sakila
Aufgabe 3.1 Ordnen Sie den folgenden Begriffen Datenbankprodukten zu:
• Office Solution
• Midrange Solution
• Enterprise Solution
Aufgabe 3.2 Bereiten Sie sich vor:
1. Installieren Sie XAMPP oder einen MySQL-Server ihrer Wahl (Version ≥ 5.1)
auf Ihren Rechner.
2. Installieren Sie die Sakila-Datenbank.
3. erstellen Sie ein ER-Modell zur Sakila-Datenbank.
4. Legen Sie sich Links auf den Desktop zu der MySQL- und Sakila-Dokumentation.
3.3 SQL-DDL
In diesem Kapitel werden ein Teil SQL-DDL6 -Befehle beschrieben. Die Auswahl ist so
getroffen, dass viele Standardaufgaben gelöst werden können. Weitere Befehle oder Befehlsvarianten sollten dem SQL-Handbuch Ihres Servers entnommen werden.
Nach der Installation gibt es einen vordefinierten SQL-User, den root7 . Starten Sie den
MySQL-Client unter Angabe des anzumeldenden Benutzers.
1
ralf@linux - edzz :~ > mysql - uroot -p
MySQL Client
Nach dem -u folgt der Name des SQL-Benutzers (nach einer Installation meist root).
Der Parameter -p erzwingt die Eingabe eines Passworts. Falls Sie bei der Installation
keines vergeben haben (BÖSE!), geben Sie auch keines ein.
Zuerst interssiert, welche Datenbanken schon vorhanden sind:
SHOW DATABASES
4
Da wir als Projekt eine PHP-Anwendung schreiben werden, empfehle ich die XAMPP-Installation.
Eine Dokumentation dieses Beispiels finden Sie unter http://downloads.mysql.com/docs/
sakila-db.zip.
6
DDL: data definition language; engl. Datendefinitionssprache
7
Nicht zu verwechseln mit den Benutzern des Betriebssystems. Selbst gleichnamige Benutzer in SQL
und dem Betriebssystem haben nichts miteinander zu tun!
5
Einführung in relationalen Datenbanken und SQL
32
3 Implementieren mit SQL
1
2
3
4
5
6
7
8
9
10
11
12
3.3 SQL-DDL
mysql > SHOW DATABASES ;
+ -- - - - - - - - - - - - - - - - - - -+
| Database
|
+ -- - - - - - - - - - - - - - - - - - -+
| in fo rma ti on _sc he ma |
| mysql
|
| sakila
|
| test
|
+ -- - - - - - - - - - - - - - - - - - -+
4 rows in set (0.00 sec )
mysql >
Falls Sie sakila noch nicht installiert haben, fehlt dieser Eintrag natürlich.
Aufgabe 3.3 Finden Sie heraus, welche Aufgaben die Datenbanken
• information_schema,
• mysql und
• test
haben.
Bevor wir die Sakila-Datenbank verwenden, legen wir eine eigene Spieldatenbank an.
Basis soll die Abbildung 2.5 auf Seite 19 sein.
Dazu werden folgende Schritte unternommen:
1. Anlegen der Datenbank
2. Wechseln in die Datenbank
3. Anlegen der Tabellen
4. Anzeige aller Tabellen
5. Tabellenbeschreibungen anzeigen
Das Anlegen einer Datenbank erfolgt durch CREATE DATABASE datenbankname. Der Datenbankname muss auf dem Server einmalig sein. Unsere Datenbank soll buch heißen.
1
2
3
mysql > CREATE DATABASE buch ;
Query OK , 1 row affected (0.00 sec )
mysql >
Es fällt auf, dass wie bei SHOW DATABASES der Befehl mit einem Semikolon endet. Dies
ist bei SQL-Befehlen grundsätzlich so. Dabei spielt der Zeilenumbruch keine Rolle! Der
Parser eines SQL-Befehls sucht nach einem Semikolon und erst dann wird der Befehl
analysiert und interpretiert. Folgendes hätte genau die gleiche Wirkung gehabt:
Einführung in relationalen Datenbanken und SQL
33
CREATE
DATABASE
3 Implementieren mit SQL
1
2
3
4
5
3.3 SQL-DDL
mysql > CREATE
-> DATABASE
-> buch ;
Query OK , 1 row affected (0.00 sec )
mysql >
Als zweites fällt auf, dass die SQL-Befehle alle in Großbuchstaben geschrieben werden.
Dies ist nicht von der Sprachdefinition gefordert, aber eine weit verbreitete Sprachkonvention, der wir folgen.
Aufgabe 3.4 Ermitteln Sie, ob die Datenbank erfolgreich angelegt wurde.
Da ein Datenbankserver viele Datenbanken beinhalten kann, muss man ihm sagen, auf
welche Datenbank alle nachfolgenden Befehle angewendet werden sollen. Dazu verwendet
man den Befehl USE:
1
2
3
USE
mysql > USE buch ;
Database changed
mysql >
Aufgabe 3.5 Wenn der Befehl zur Auflistung der Datenbanken SHOW DATABASES heißt,
wie mag der Befehl zur Auflistung der Tabellen heißen?
Nun sollen die Tabellen angelegt werden. Der entsprechende Befehl heißt CREATE TABLE
tabellenname. Bevor wir den Befehl näher betrachten, wollen wir ermitteln, welche
Datentypen und Zusätze einem bei einer Spaltenspezifikation zur Verfügung stehen.
CREATE
TABLE
Datentypen
Zusätze
Datentypen:
Zahlen
Tabelle 3.1: Tabelle: Numerische Datentypen (Auszug)
Typ
#Bytes
Kommentar
TINYINT(länge)
1
Integer im Bereich 0 bis 255 (ohne Vorzeichen) oder -128 bis 127 (mit Vorzeichen).
SMALLINT(länge)
2
Integer im Bereich 0 bis 65.353 (ohne Vorzeichen) oder -32.768 bis 32.767
(mit Vorzeichen).
MEDIUMINT(länge)
3
Integer im Bereich 0 bis 16.777.215
(ohne Vorzeichen) oder -8.388.608 bis
8.388.607 (mit Vorzeichen).
INT(länge)
4
Integer im Bereich 0 bis 4.294.967.295
(ohne Vorzeichen) oder -2.174.483.648
bis 2.174.483.647 (mit Vorzeichen).
Einführung in relationalen Datenbanken und SQL
34
3 Implementieren mit SQL
3.3 SQL-DDL
Typ
#Bytes
Kommentar
BIGINT(länge)
8
Integer
im
Bereich
0
bis
18.446.744.073.709.551.615
(ohne
Vorzeichen)
oder
9.223.372.036.854.775.808
bis
9.223.372.036.854.775.807 (mit Vorzeichen).
FLOAT(länge,dezimalstellen)
4
Fließkommazahlen mit einem Maximalwert von ±3, 40282346638 und einem Minimalwert ±1, 175494351−38 .
DOUBLE(länge,dezimalstellen)
8
Fließkommazahlen mit einem Maximalwert von ±1, 7976931348623157308
und
einem
Minimalwert
−308
±2, 250738585072014
.
länge
Fließkommazahlen mit einem Wertebereich von double, die in einem CHARFeld abgelegt werden.
DECIMAL(länge,dezimalstellen)
Die Angaben länge und dezimalstellen in Tabelle 3.1 auf der vorherigen Seite sind optional und bestimmen die Konsolenausgabe.
Tabelle 3.2: Tabelle: Datum und Zeit Datentypen (Auszug)
Typ
#Bytes
Kommentar
YEAR
1
Dieser Typ speichert eine Jahreszahl. Die Werte
können entweder im Format YY als auch YYYY
eingegeben werden und werden dann als 2 oder
4 stellige Jahreszahl ausgegeben. Bei zweistelligen
liegt der Wertebereich zwischen (19)70 und (20)69.
Bei vierstelligen von 1901 bis 2155.
DATE
3
Ein Datumstyp, der Jahr, Monat und Tag speichert. Die Werte werden im Format YYYY-MMDD ausgegeben.
TIME
3
Ein Zeittyp, der Stunde, Minute und Sekunde speichert. Die Werte werden im Format HH:MM:SS
ausgegeben.
DATETIME
8
Ein Typ, der sowohl Datum als auch Zeit speichert.
Die Werte werden YYYY-MM-DD HH:MM:SS
ausgegeben.
Einführung in relationalen Datenbanken und SQL
35
Datentypen:
Datum und
Zeit
3 Implementieren mit SQL
Typ
3.3 SQL-DDL
#Bytes
8
TIMESTAMP(länge)
Kommentar
Eine Zeitangabe, die bei jeder Modifikation der
Zeile aktualisiert wird. Man kann dieser Spalte
auch manuell einen Wert zuweisen. Die Zuweisung eines NULL-Werts setzt das Feld auf die
aktuelle Zeit. Die optionale Längenangabe bestimmt das Ausgabeformat: 14 (Standardwert):
’YYYYMMDDHHMMSS’, 12: ’YYYYMMDDHHMM’, 8: ’YYYYMMDD’, 6: ’YYMMDD’
Das Ausgabeformat von Datum und Uhrzeit kann durch viele Formatierungszeichen und
Hilfsfunktionen angepasst werden. Näheres entnehmen Sie bitte der Dokumentation.
Tabelle 3.3: Tabelle: Text und Blob Datentypen (Auszug)
Typ
#Bytes
Kommentar
länge
Eine Zeichenkette fester Länge. Eingabe, die kürzer sind
als die länge, werden mit Leerzeichen aufgefüllt. Diese
Leerzeichen werden bei Ausgabe wieder entfernt. Standardmäßig wird nicht zwischen Groß- und Kleinschreibung bei der Verarbeitung dieses Feldtyps unterschieden
(siehe Tabelle 3.4 auf der nächsten Seite).
VARCHAR(länge)
länge+2
Ein Textstring variabler Länge. Eingabe, die länger sind
als die länge, werden gekürzt. Die Länge muss mit einem
Wert von 1 bis 656535 angegeben sein. Standardmäßig
wird nicht zwischen Groß- und Kleinschreibung bei der
Verarbeitung dieses Feldtyps unterschieden (siehe Tabelle 3.4 auf der nächsten Seite).
TINYTEXT
länge+1
Ein Textfeld mit einer maximalen Länge von 255 B.
TINYBLOB
länge+1
Ein Binärfeld mit einer maximalen Länge von 255 B.
TEXT
länge+2
Ein Textfeld mit einer maximalen Länge von 64 KB.
BLOB
länge+2
Ein Binärfeld mit einer maximalen Länge von 64 KB.
MEDIUMTEXT
länge+3
Ein Textfeld mit einer maximalen Länge von 16 MB.
MEDIUMBLOB
länge+3
Ein Binärfeld mit einer maximalen Länge von 16 MB.
LONGTEXT
länge+4
Ein Textfeld mit einer maximalen Länge von 4 GB.
LONGBLOB
länge+4
Ein Binärfeld mit einer maximalen Länge von 4 GB.
CHAR(länge)
Einführung in relationalen Datenbanken und SQL
36
Datentypen:
Text
3 Implementieren mit SQL
3.3 SQL-DDL
Diese Datentypen werden noch durch viele aus dem SQL-Standard ergänzt. Weitere
werden von den einzelnen Serverherstellern properitär angeboten und sollten nur nach
genauer Abwägung eingesetzt werden.
Die Datentypen werden noch durch einige Zusätze (modifier) genauer bestimmt (siehe
Tabelle 3.4).
Modifier
Tabelle 3.4: Tabelle: Zusätze (modifier) zu Datentypen (Auszug)
Zusatz
Kommentar
AUTO_INCREMENT
Dieser Zusatz ermöglicht einem numerischen Feld die automatische Aktualisierung. Dies eignet sich zur Generierung von
Schlüssel. Die Daten können in dieses Feld gelesen und auch
beschrieben werden. Wenn aber ein Wert 0 oder NULL zugewiesen wird, wird der nächste Zahlenwert zugewiesen. Dieser
Zusatz kann nur einmal pro Tabelle und nur dem Primärschlüssel zugewiesen werden.
BINARY
Dieser Zusatz kann bei CHAR und VARCHAR Typen verwendet werden, um die Beachtung der Groß- und Kleinschreibung
zu erzwingen.
DEFAULT wert
Mit diesem Zusatz können Spalten Vorbelegungen zugewiesen
werden. Falls einer Spalte kein Wert zugewiesen wird, wird
automatisch der Wert wert zugewiesen.
NULL
Die Spalte darf auch keinen Attributwert haben. Dies ist nicht
gleichbedeutend der Zahl 0 oder dem Leerstring!
NOT NULL
Spalten mit diesem Zusatz dürfen nicht leer sein.
UNSIGNED
Bei ganzzahligen Zahlentypen steuert dieser Zusatz, ob die
Darstellung das Vorzeichen verwendet oder ob dieses Bit zur
Zahlendarstellung mitgenutzt wird.
Aufgabe 3.6 Weisen Sie mit Hilfe der Tabellen zu den Datentypen und den Zusätzen
den Spalten des ER-Modells aus Abb. 2.5 auf Seite 19 Datentypen und Zusätze zu.
Nun kann man die Tabellen anlegen. Der entsprechende Befehl lautet: CREATE TABLE.
Zu diesem Befehl gibt es viele Varianten, aber für das normale Anlegen einer Tabelle
hat er folgenden Aufbau:
1
2
3
4
5
6
CREATE TABLE tabellenname
(
spaltenname datentyp zusätze ,
spaltenname datentyp zusätze ,
spaltenname datentyp zusätze ,
PRIMARY KEY (spaltenname, spaltenname )
);
Einführung in relationalen Datenbanken und SQL
37
CREATE
TABLE
3 Implementieren mit SQL
3.3 SQL-DDL
Als Beispiel seien hier die CREATE TABLES für zwei Tabellen angegeben:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql > CREATE TABLE buch
-> (
-> id
INT
-> isbn_lkz
INT
-> isbn_verlag INT
-> isbn_nr
INT
-> titel
VARCHAR (255)
-> jahr
YEAR ,
-> auflage
INT
-> bemerkung
TEXT ,
-> verlag_id
INT
-> PRIMARY KEY ( id )
-> );
mysql > CREATE TABLE verlag
-> (
-> id
INT
-> verlag
VARCHAR (255)
-> ort
VARCHAR (255) ,
-> PRIMARY KEY ( id )
-> );
UNSIGNED AUTO_INCREMENT ,
UNSIGNED ,
UNSIGNED ,
UNSIGNED ,
NOT NULL ,
UNSIGNED ,
UNSIGNED ,
UNSIGNED AUTO_INCREMENT ,
NOT NULL ,
Um der ersten Normalform zu entsprechen, wurde die ISBN in ihre Teile zerlegt. Ein
weiterer Grund ist die Erfüllung der 2. Normalform durch die Verknüpfung über die
Verlagsnummer.
Aufgabe 3.7 Welche Normalform wird verletzt und warum?
Aufgabe 3.8 Erstellen Sie für alle ausstehenden Tabellen (inklusive der Hilfstabellen)
die CREATE TABLE Befehle.
Dem kritischen Leser wird auffallen, das wir das Primär-/Fremdschlüsselverhältnis zwischen verlag.id und buch.isbn_verlag nicht mit einem SQL-Befehl hergestellt haben.
Vom Modell her ist das auch nicht nötig. Das es sich hier um ein Primär-/Fremdschlüsselpaar handelt, wird durch entsprechende Auswertungen (s. INNER JOIN etc) sichergestellt. Trotzdem kann es sinnvoll sein (siehe Referentielle Integrität 13 auf Seite 26),
durch entsprechende Erweiterung der Tabellenspezifikation dem Server schon mitzuteilen, dass diese Tabellen über diese beiden Spalten verbunden sind.
Dazu legen wir die Tabelle buch neu an. Vorher muss man dieser aber löschen 8 .
1
DROP
TABLE
mysql > DROP TABLE IF EXISTS buch ;
Der Zusatz IF EXISTS ist nicht zwingend nötig, verhindert aber eine Fehlermeldung,
falls die Tabelle - warum auch immer - schon nicht mehr da ist. Es wird dann eine
entsprechende Warnung erzeugt, die man mit SHOW WARNINGS einsehen kann.
8
ALTER TABLE kommt noch ;-)
Einführung in relationalen Datenbanken und SQL
38
SHOW
WARNINGS
3 Implementieren mit SQL
1
2
3
4
5
6
7
8
3.3 SQL-DDL
mysql > DROP TABLE IF EXISTS buch ;
mysql > SHOW WARNINGS ;
+ -- - - - - -+ - - - - - -+ - - - - - - - - - - - - - - - - - - - - - -+
| Level | Code | Message
|
+ -- - - - - -+ - - - - - -+ - - - - - - - - - - - - - - - - - - - - - -+
| Note | 1051 | Unknown table ’ buch ’ |
+ -- - - - - -+ - - - - - -+ - - - - - - - - - - - - - - - - - - - - - -+
1 row in set (0.00 sec )
Nun kann man ein verändertes CREATE TABLE absetzten, welches eine Verknüpfung
festlegt.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql > CREATE TABLE buch
-> (
-> id
INT
UNSIGNED AUTO_INCREMENT ,
-> isbn_lkz
INT
UNSIGNED ,
-> isbn_verlag INT
UNSIGNED ,
-> isbn_nr
INT
UNSIGNED ,
-> titel
VARCHAR (255)
NOT NULL ,
-> jahr
YEAR ,
-> auflage
INT
UNSIGNED ,
-> bemerkung
TEXT ,
-> verlag_id
INT
UNSIGNED ,
-> PRIMARY KEY ( id ) ,
->
CONSTRAINT fk_ve rlag_buch _id
->
FOREIGN KEY ( isbn_verlag )
->
REFERENCES verlag ( id )
->
ON DELETE NO ACTION
->
ON UPDATE NO ACTION
-> );
In Zeile 13 wird ein Constraint9 definiert. Jeder Constraint hat einen datenbankweiten
eindeutigen Namen. Bei Fremdschlüsseln empfiehlt sich folgende Namenskonvention:
fk_Quelltabelle_Zieltabelle_Primärschlüsselname
Der Constraint kann für vieles verwendet werden, hier beschreibt er eine Fremdschlüsselbeziehung. In Zeile 14 wird über das Schlüsselwort FOREIGN KEY die Spalten benannt,
die ein Fremdschlüssel in der Tabelle ist. In der Zeile danach wird mit dem Schlüsselwort
REFERENCES die Tabelle und in Klammern dahinter der Schlüssel festgelegt10 .
Aufgabe 3.9 Was bedeuten ON DELETE NO ACTION und ON UPDATE NO ACTION?
In der Tabelle verlag muss gar nichts passieren, da es Tabelle egal sein muss, wer wie
auf sie verweist.
Nun wäre es schön bescheuert, wenn man bei jeder Änderung der Tabellenspezifikation
die Tabelle löschen und neu anlegen müsste. Deshalb gibt es einen Befehl, mit dessen
9
10
engl. Einschränkung
Um den Zugriff über den Fremdschlüssel zu beschleunigen, sollte man einen Index vor der Zeile 13
für diese Spalte definieren: INDEX buch_verlag_id (verlag_id ASC).
Einführung in relationalen Datenbanken und SQL
39
CONSTRAINT
FOREIGN
KEY
3 Implementieren mit SQL
3.4 SQL-DML
Hilfe man nachträglich Spezifikationseigenschaften einfügen, ändern oder löschen kann.
Wollte man beispielsweise für den Verlag eine neue Spalte geloescht hinzufügen, ginge
dies so:
1
2
ALTER TABLE
mysql > ALTER TABLE verlag
-> ADD geloescht BOOL NOT NULL DEFAULT false ;
Möchte man den Datentyp von BOOL auf INT umstellen ginge dies so:
1
2
mysql > ALTER TABLE verlag
-> MODIFY geloescht INT NOT NULL DEFAULT 0;
Wollte man diese Spalte wieder entfernen, ginge die so:
1
mysql > ALTER TABLE verlag DROP geloescht ;
Man kann in einem Befehl mehrere Änderungen vornehmen:
1
2
3
4
mysql > ALTER TABLE verlag
-> ADD wurst char (3) ,
-> ADD kaese INT ,
-> MODIFY ort VARCHAR (100);
Aufgabe 3.10 Ändern Sie die vorhandenen Tabellen so, dass alle Constraints bzgl.
der Fremdschlüssel enthalten. Ändern Sie alle VARCHAR / CHAR Spalten so um, dass sie
zwischen Groß- und Kleinschreibung unterscheiden.
Die vorhandene Tabellenstruktur kann man mit dem Befehl DESCRIBE ermitteln:
1
2
3
4
5
6
7
8
9
10
11
DESCRIBE
mysql > DESCRIBE verlag ;
+ -- - - - - - -+ - - - - - - - - - - - - - - - - - -+ - - - - - -+ - - - - -+ - - - - - - - - -+ - - - - - - - - - - - - - - - -+
| Field | Type
| Null | Key | Default | Extra
|
+ -- - - - - - -+ - - - - - - - - - - - - - - - - - -+ - - - - - -+ - - - - -+ - - - - - - - - -+ - - - - - - - - - - - - - - - -+
| id
| int (10) unsigned | NO
| PRI | NULL
| auto_increment |
| verlag | varchar (255)
| NO
|
| NULL
|
|
| ort
| varchar (100)
| YES |
| NULL
|
|
| wurst | char (3)
| YES |
| NULL
|
|
| kaese | int (11)
| YES |
| NULL
|
|
+ -- - - - - - -+ - - - - - - - - - - - - - - - - - -+ - - - - - -+ - - - - -+ - - - - - - - - -+ - - - - - - - - - - - - - - - -+
5 rows in set (0.00 sec )
3.4 SQL-DML
Die Befehle der SQL-DML11 werden verwendet um
1. Daten hinzuzufügen,
11
DML: data manipulation language; engl. Datenveränderungssprache
Einführung in relationalen Datenbanken und SQL
40
3 Implementieren mit SQL
3.4 SQL-DML
2. Daten zu ändern,
3. Daten zu löschen oder
4. Daten auszuwerten.
Wie bei der SQL-DDL werden hier nur solche Befehle und Befehlsvarianten vorgestellt,
die man für die wichtigsten Standardaufgaben benötigt.
3.4.1 Daten einfügen
3.4.1.1 Daten aus CSV-Dateien einfügen
Daten werden oft aus anderen Systemen übernommen. Typische Beispiele sind: Messwertdaten, Vorversionen, Tabellenkalkulationen, Tabellenkalkulationen, proprietäre Programme etc. Diese Daten werden oft über das CSV-Format in Datenbanken importiert.
Aufgabe 3.11 Finden Sie heraus, was das CSV-Format ist und erstellen Sie eine Datei
namens verlag.csv mit folgenden Inhalt:
verlagnr;verlagsname;ort
8266;mitp;Bonn
89721;O’Reilly;Köln
8362;Galileo Press;Bonn
Stellen Sie sicher, dass in der Datenbank eine Tabelle verlag wie oben beschrieben
existiert. Dann können die Daten der CSV-Datei verlag.csv mit dem Befehl LOAD
DATA INFILE importiert werden.
1
2
3
4
5
6
7
8
9
LOAD DATA LOCAL INFILE ’/ home / user / verlag . csv ’
INTO TABLE verlag
FIELDS
TERMINATED BY ’; ’
LINES
TERMINATED BY ’\ n ’
IGNORE 1 LINES
( id , verlag , ort )
;
In Zeile 1 wird dem Server mitgeteilt, wo die Datei liegt. Der Zusatz LOCAL legt fest,
dass die Datei nicht auf dem MySQL-Server liegt, sondern beim Client. Sie wird dann
zuerst vom Client zum Server kopiert und anschließend eingelesen. In Zeile 2 wird die
Zieltabelle angegeben: Hier verlag.
Nun folgt die Spezifikation der CSV-Parameter. Ab Zeile 3 wird festgelegt, wie der
Befehl einzelne Feldinhalte von einander unterscheiden kann. In unserem Fall ist das sehr
Einführung in relationalen Datenbanken und SQL
41
LOAD
DATA
INFILE
3 Implementieren mit SQL
3.4 SQL-DML
einfach, die Felder werden durch ein Semikolon getrennt. Ab Zeile 3 auf der vorherigen
Seite wird festgelegt, wie einzelne Datensätze unterschieden werden können. Auch hier
ist das relativ einfach, jeder Datensatz ist eine Zeile der CSV-Datei, daher das Zeichen
für einen Zeilenumbruch \n12 . Da die erste Zeile keine Daten enthält, wird in Zeile 7 auf
der vorherigen Seite die Anzahl der zu ignorierenden Zeilen mit 1 angegeben. In Zeile 8
auf der vorherigen Seite wird die Entsprechung der Felder in der Datei zu den Spalten
der Tabelle bestimmt. Das erste Feld wird der Spalte id, die zweite der Spalte verlag
und die letzte der Spalte ort zugeordnet.
Erfolgt keine Fehlermeldung sollte folgendes Ergebnis vorhanden sein:
1
2
3
4
5
6
7
mysql > select * from verlag ;
+ -- - - - - -+ - - - - - - - - - - - - - - -+ - - - - - - -+
| id
| verlag
| ort
|
+ -- - - - - -+ - - - - - - - - - - - - - - -+ - - - - - - -+
| 8266 | mitp
| Bonn |
| 8362 | Galileo Press | Bonn |
+ -- - - - - -+ - - - - - - - - - - - - - - -+ - - - - - - -+
3.4.1.2 Daten mit INSERT einfügen
Ab jetzt werde ich die sakila-Datenbank (siehe 3.2 auf Seite 32) verwenden. Bitte
stellen Sie die Installation sicher. Ich möchte in die Tabelle languageweitere Sprachen
hinzufügen.
Aufgabe 3.12 Ermitteln Sie die Struktur und den Inhalt der Tabelle language.
Eine Sprache, die derzeit nicht in dieser Tabelle erfasst ist, ist: Sindarin13 . Diesen Datensatz kann mit dem INSERT INTO Befehl einfügen.
1
INSERT INTO language ( name ) VALUES ( ’ Sindarin ’ );
Nach dem INSERT INTO steht immer der Name der Tabelle, in dem die Daten eingefügt werden sollen. Hinter dem Tabellennamen kommt eine Liste von Spaltennamen
(in runden Klammern, durch Komma getrennt), für welche man Werte zur Verfügung
stellt. Anschließend stehen hinter dem Schlüsselwort VALUES in runden Klammern, durch
Komma getrennt die Datensätze.
1
2
3
4
5
6
INSERT INTO language
( language_id , name )
VALUES
(10 , ’ Quenya ’) ,
(15 , ’ Westron ’) ,
(16 , ’ Entisch ’ );
12
13
Bei unter Windows erzeugten CSV-Dateien muss oft \r\n als Zeilenende angegeben werden!
Na, wer weiss es?
Einführung in relationalen Datenbanken und SQL
42
INSERT INTO
3 Implementieren mit SQL
3.4 SQL-DML
Aufgabe 3.13 Überprüfen Sie, ob die Datensätze in der Tabelle angekommen sind.
Die Anzahl der Angaben in der Spaltenliste (hinter dem Tabellennamen) muss mit die
Anzahl der Werte pro Datensatz übereinstimmen! Werden keine Spaltennamen hinter
dem Tabellennamen angegeben, müssen zu allen Spalten der Tabelle Werte angegeben
werden. Die Reihenfolge muss der des CREATE TABLE entsprechen.
Aufgabe 3.14 Versuchen Sie zu beiden Situationen fehlerhaft Befehle auszuführen. Notieren Sie sich die Fehlermeldung und ihre Bedeutung.
3.4.2 Daten ändern
Sind die Datensätze vorhanden und soll der Inhalt der Datensätze geändert werden,
verwendet man den Befehl: UPDATE.
UPDATE
In der Tabelle film gibt die Spalte length, die die Länge des Films in Minuten angibt.
Ich möchte diese Angabe auf Sekunden ändern.
1
2
mysql > UPDATE film
SET length = length * 60;
In der Tabelle film gibt die Spalte rental_rate den Preis pro Ausleihperiode an. Diesen
Preis möchte ich um 15% erhöhen.
1
2
mysql > UPDATE film
SET rental_rate = rental_rate + ( rental_rate * 15) / 100;
In der Tabelle category befinden sich Filmkategorien. Die Kategorie new möchte ich
nun in brand new ändern.
1
2
3
UPDATE category
SET
name = ’ brand ␣ new ’;
Aufgabe 3.15 Führen Sie den Befehl aus und überprüfen Sie das Ergebnis. Diskutierten Sie mögliche Ursachen. Stellen Sie anschließend den originalen Zustand der Tabelle
wieder her, indem Sie das Installationsskript wieder starten.
Was dem Befehl fehlt, ist die Einschränkung, auf welche Datensätze die UPDATE Operation ausgeführt werden soll.
3.4.3 Exkurs: WHERE-Klausel
Die WHERE-Klausel ist so was wie das if für SQL. Es werden Bedingungen mit Teilbedingungen formuliert, die datensatzweise auf TRUE oder FALSE überprüft werden. Ist der
Datensatz mit TRUE bewertet, wird die Operation ausgeführt, ansonsten nicht.
Einführung in relationalen Datenbanken und SQL
43
WHERE
3 Implementieren mit SQL
3.4 SQL-DML
Tabelle 3.5: Tabelle: Elemente der WHERE-Klausel (Auszug)
Element
Bedeutung
(bedingung)
Mit runden Klammern kann man die Auswertungsreihenfolge von Teilbedingungen festlegen.
Oft dienen sie nur der optischen Gruppierung
von Teilbedingungen, damit man sie besser lesen und warten kann.
bedingung AND bedingung
bedingung && bedingung
Die beiden Teilbedingungen werden mit der logischen UND-Funktion verknüpft. Die UNDFunktion bindet stärker als die ODERFunktion. Der Lesbarkeit wegen sollte der Text
AND verwendet werden.
bedingung OR bedingung
bedingung || bedingung
Die beiden Teilbedingungen werden mit der logischen ODER-Funktion verknüpft. Der Lesbarkeit wegen sollte der Text OR verwendet werden.
NOT bedingung
!bedingung
Kehrt den Wahrheitswert einer Bedingung oder
Funktion um, d.h. TRUE → FALSE und
FALSE → TRUE .
wert1 = wert2
Überprüft, ob die rechts und links vom Gleichheitszeichen stehenden Werte(!) gleich sind.
Vorsicht: Anders als bei C/C++ steht hier nur
ein Gleichheitszeichen.
wert1 <> wert2
wert1 != wert2
Überprüft, ob die rechts und links vom Gleichheitszeichen stehenden Werte(!) ungleich sind.
Vorsicht: Bei Kommazahlen sind Rundungsfehler zu beachten.
wert1 < wert2
Überprüft, ob der linke Wert kleiner dem rechten Wert ist. Bei numerischen Datentypen wird
der Zahlenwert als Ordnungskriterium verwendet, bei Texten die festgelegte lexikalische Reihenfolge (nicht die Länge).
wert1 <= wert2
Kurzschreibweise von:
((wert1 < wert2 ) OR (wert1 = wert2 )).
wert1 > wert2
Überprüft, ob der linke Wert größer dem rechten Wert ist. Bei numerischen Datentypen wird
der Zahlenwert als Ordnungskriterium verwendet, bei Texten die festgelegte lexikalische Reihenfolge (nicht die Länge).
Einführung in relationalen Datenbanken und SQL
44
3 Implementieren mit SQL
3.4 SQL-DML
Tabelle 3.5: Tabelle: Elemente der WHERE-Klausel (Auszug)
Element
Bedeutung
wert1 >= wert2
Kurzschreibweise von:
((wert1 > wert2 ) OR (wert1 = wert2 )).
wert BETWEEN wert1 AND wert2
Es wird überprüft, ob wert in dem Intervall von
(wert1 , wert2 ) liegt. Kurzschreibweise von:
((wert >= wert1 ) AND (wert <= wert2 )).
wert NOT BETWEEN wert1 AND wert2
Es wird überprüft, ob wert nicht in dem Intervall von (wert1 , wert2 ) liegt.
wert IN (wert1 , wert2 , ...)
Es wird überprüft, ob wert in der Werteliste
(wert1 , wert2 , ...) liegt. Kurzschreibweise von:
((wert = wert1 ) OR (wert = wert2 ) usw.).
wert NOT IN (wert1 , wert2 , ...)
Es wird überprüft, ob wert nicht in der Werteliste (wert1 , wert2 , ...) liegt.
’wert1 ’ LIKE ’wert2 ’
Es wird überprüft, ob der Text wert1 dem Text
von wert2 ähnlich ist. Die Ähnlichlichkeit wird
mit Wildcards bestimmt. Das Wildcard % steht
für jedes Zeichen beliebig (auch 0 mal) oft. Das
Wildcard _ steht für jedes beliebige Zeichen genau einmal.
wert IS NULL
Wenn der Inhalt einer Spalte auf NULL überprüft werden soll, kann man nicht wert = NULL
schreiben, sondern muss diese Notation verwenden.
Ein typisches Beispiel für eine WHERE-Klausel ist:
WHERE (umsatz > 50000 OR typ = ’G’) AND (name1 LIKE ’A%’)
Diese Klausel trifft auf alle Zeilen zu, die einen Umsatz größer 50000 oder vom Typ G
sind und deren Name mit einem A beginnt. Die Klammerung ist notwendig, da sonst
das AND stärker bindet als das OR.
Hinweis: Datumswerte werden unabhängig von der Ländereinstellung der Ausgabe im
(YYYY-MM-DD) Format angegeben.
Zurück zu unserem obigen Beispiel. Ich möchte immer noch new in brand new ändern14 .
Mit einer passenden WHERE-Klausel sähe es so aus:
14
Haben Sie den ursprünglichen Inhalt der Tabelle wieder hergestellt?
Einführung in relationalen Datenbanken und SQL
45
3 Implementieren mit SQL
1
2
3
4
3.4 SQL-DML
mysql > UPDATE category
-> SET name = ’ brand ␣ new ’ WHERE name = ’ new ’;
Query OK , 1 row affected (0.03 sec )
Rows matched : 1 Changed : 1 Warnings : 0
Aufgabe 3.16 Wie könnte die WHERE-Klausel auch aussehen?
3.4.4 Daten löschen
Bevor wir das Löschen besprechen, soll hier etwas sehr deutlich betont werden:
Es gibt kein Undo!. Was immer sie löschen, ist weg.
1
2
undo
Nach dem MVC-Konzept ist die Controler-Schicht für die Plausibilisierung der Löschoperation verantwortlich, nicht der SQL-Server.
MVC
Der SQL-Befehl zum Löschen ist DELETE. Ohne WHERE-Klausel löscht er die Inhalte einer
ganzen Tabelle. Deshalb sollte er auch nur vorsichtig verwendet werden.
DELETE
mysql > DELETE FROM film_category WHERE category_id = 6;
Query OK , 68 rows affected (0.11 sec )
Dieser Befehl löscht aus der Verknüpfungstabelle zwischen film und category alle Zeilen, deren category_id des Wert 6 (Dokumentation) haben.
Aufgabe 3.17 Welche Kardinalität besteht zwischen diesen beiden Tabellen?
3.4.5 Daten auswerten mit SELECT
Der SELECT-Befehl ermittelt aus einer oder mehreren Tabellen die Inhalte, bereitet sie
auf und stellt sie wieder in tabellarischer Form zur Verfügung.
SELECT
3.4.5.1 Tabellenunabhängige Auswertungen
Bevor wir die Auswertung von Tabellendaten bearbeiten, soll hier kurz eingeführt werden, dass man mit SELECT auch andere Sachen machen kann.
Arithmetische Berechnungen werden in einem SELECT ausgerechnet und angezeigt. Welche mathematischen Operationen zur Verfügung stehen, entnehmen Sie bitte dem Handbuch.
1
2
3
4
5
6
mysql > SELECT 5+7;
+ -- - - -+
| 5+7 |
+ -- - - -+
| 12 |
+ -- - - -+
Einführung in relationalen Datenbanken und SQL
46
Berechnungen
3 Implementieren mit SQL
3.4 SQL-DML
Eine weitere Anwendung ist die Ausgabe von Texten auf der Konsole.
1
2
3
4
5
6
mysql > SELECT ’ Berechnung ␣ des ␣ J a hr e sd u rc h s ch n it t s ’;
+ -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -+
| Berechnung des J a hr e sd u r ch s ch n i tt s |
+ -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -+
| Berechnung des J a hr e sd u r ch s ch n i tt s |
+ -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -+
Das erscheint im Moment nicht besonders sinnvoll. hat man aber SQL-Skripte geschrieben, die umfangreiche Konvertierungen oder Auswertungen durchführen, so kann die
Ausgabe auf der Konsole sehr hilfreich sein, wenn dabei Problemen auftreten.
3.4.5.2 Einfache Zeilen und Spaltenauswahl
Die einfachste Art den kompletten Inhalt einer Tabelle zu erfahren ist:
1
2
3
4
5
6
7
8
9
10
11
mysql > SELECT * FROM language ;
+ -- - - - - - - - - - - -+ - - - - - - - - - -+ - - - - - - - - - - - - - - - - - - - - -+
| language_id | name
| last_update
|
+ -- - - - - - - - - - - -+ - - - - - - - - - -+ - - - - - - - - - - - - - - - - - - - - -+
|
1 | English | 2006 -02 -15 05:02:19 |
|
2 | Italian | 2006 -02 -15 05:02:19 |
|
3 | Japanese | 2006 -02 -15 05:02:19 |
|
4 | Mandarin | 2006 -02 -15 05:02:19 |
|
5 | French
| 2006 -02 -15 05:02:19 |
|
6 | German
| 2006 -02 -15 05:02:19 |
+ -- - - - - - - - - - - -+ - - - - - - - - - -+ - - - - - - - - - - - - - - - - - - - - -+
Nach dem Schlüsselwort SELECT steht ein *. Dieser ist ein Platzhalter für die Spalten,
die man sich anzeigen lassen möchte. Der Stern steht für alle Spalten. Anstelle des Sterns
können auch einzelne Spalten in beliebiger Reihenfolge durch Komma getrennt stehen.
1
2
3
4
5
6
7
8
9
10
11
mysql > SELECT last_update , name FROM language ;
+ -- - - - - - - - - - - - - - - - - - - -+ - - - - - - - - - -+
| last_update
| name
|
+ -- - - - - - - - - - - - - - - - - - - -+ - - - - - - - - - -+
| 2006 -02 -15 05:02:19 | English |
| 2006 -02 -15 05:02:19 | Italian |
| 2006 -02 -15 05:02:19 | Japanese |
| 2006 -02 -15 05:02:19 | Mandarin |
| 2006 -02 -15 05:02:19 | French
|
| 2006 -02 -15 05:02:19 | German
|
+ -- - - - - - - - - - - - - - - - - - - -+ - - - - - - - - - -+
Die Spaltennamen können mit einem Alias versehen werden:
1
2
3
4
Alias
mysql > SELECT last_update ’ aktualisiert ␣ am ’ , name ’ Sprache ’
->
FROM language ;
+ -- - - - - - - - - - - - - - - - - - - -+ - - - - - - - - - -+
| aktualisiert am
| Sprache |
Einführung in relationalen Datenbanken und SQL
47
3 Implementieren mit SQL
5
6
7
8
9
10
11
12
3.4 SQL-DML
+ -- - - - - - - - - - - - - - - - - - - -+ - - - - - - - - - -+
| 2006 -02 -15 05:02:19 | English |
| 2006 -02 -15 05:02:19 | Italian |
| 2006 -02 -15 05:02:19 | Japanese |
| 2006 -02 -15 05:02:19 | Mandarin |
| 2006 -02 -15 05:02:19 | French
|
| 2006 -02 -15 05:02:19 | German
|
+ -- - - - - - - - - - - - - - - - - - - -+ - - - - - - - - - -+
Mit Hilfe der WHERE-Klausel können Zeilen bestimmt werden, die angezeigt werden sollen:
1
2
3
4
5
6
7
8
9
mysql > SELECT name ’ Sprache ’ FROM language WHERE name LIKE ’% a % ’;
+ -- - - - - - - - -+
| Sprache |
+ -- - - - - - - - -+
| Italian |
| Japanese |
| Mandarin |
| German
|
+ -- - - - - - - - -+
Eine weitere Methode die Zeilen der Auswahl einzuschränken ist die Ergänzung LIMIT
des SELECT.
1
2
3
4
5
6
7
8
9
10
mysql > SELECT actor_id , first_name , last_name FROM actor LIMIT 5;
+ -- - - - - - - - -+ - - - - - - - - - - - -+ - - - - - - - - - - - - - -+
| actor_id | first_name | last_name
|
+ -- - - - - - - - -+ - - - - - - - - - - - -+ - - - - - - - - - - - - - -+
|
1 | PENELOPE
| GUINESS
|
|
2 | NICK
| WAHLBERG
|
|
3 | ED
| CHASE
|
|
4 | JENNIFER
| DAVIS
|
|
5 | JOHNNY
| LOLLOBRIGIDA |
+ -- - - - - - - - -+ - - - - - - - - - - - -+ - - - - - - - - - - - - - -+
Durch das LIMIT 5 werden nur die ersten 5 Zeilen angezeigt. Man kann sich auch die
Ergebnisse von Zeile 11 bis 16 anzeigen lassen:
1
2
3
4
5
6
7
8
9
10
11
mysql > SELECT actor_id , first_name , last_name FROM actor LIMIT 10 , 6;
+ -- - - - - - - - -+ - - - - - - - - - - - -+ - - - - - - - - - - -+
| actor_id | first_name | last_name |
+ -- - - - - - - - -+ - - - - - - - - - - - -+ - - - - - - - - - - -+
|
11 | ZERO
| CAGE
|
|
12 | KARL
| BERRY
|
|
13 | UMA
| WOOD
|
|
14 | VIVIEN
| BERGEN
|
|
15 | CUBA
| OLIVIER
|
|
16 | FRED
| COSTNER
|
+ -- - - - - - - - -+ - - - - - - - - - - - -+ - - - - - - - - - - -+
Wird das LIMIT in dieser Form angegeben, ist die Bedeutung etwas komplizierter als
mit nur einer Zahl. Bei einer Zahl (wie oben die 5) werden die ersten 5 Zeilen ausgegeben. Bei LIMIT 10, 6 ist die erste Zahl der Offset und die zweite die Anzahl. Da die
Nummerierung immer mit 0 beginnt, zeigt der Offset 10 auf die 11 Zeile.
Einführung in relationalen Datenbanken und SQL
48
LIMIT
3 Implementieren mit SQL
3.4 SQL-DML
3.4.5.3 Auswahl sortieren
Üblicherweise werden Daten in irgendeiner Art und Weise sortiert: Nach Name, Umsatz,
Ort, Betrag, Dauer etc. Eine Sortierung erfolgt mit der Ergänzung ORDER BY des SELECT.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
ORDER BY
mysql >
SELECT actor_id , last_name , first_name
FROM actor
ORDER BY last_name
LIMIT 5 ;
+ -- - - - - - - - -+ - - - - - - - - - - -+ - - - - - - - - - - - -+
| actor_id | last_name | first_name |
+ -- - - - - - - - -+ - - - - - - - - - - -+ - - - - - - - - - - - -+
|
58 | AKROYD
| CHRISTIAN |
|
92 | AKROYD
| KIRSTEN
|
|
182 | AKROYD
| DEBBIE
|
|
118 | ALLEN
| CUBA
|
|
145 | ALLEN
| KIM
|
+ -- - - - - - - - -+ - - - - - - - - - - -+ - - - - - - - - - - - -+
Man kann deutlich erkennen, dass die Daten aufsteigend nach dem Nachnamen sortiert
werden. Beim Nachnamen AKROYD fällt auf, dass die Vornamen immer noch unsortiert
sind. Will man nach mehr als einer Spalte sortieren, gibt man die Spaltennamen hinter
dem ORDER BY durch Komma getrennt an. Nun werden die Schauspieler bei Gleichheit
des Nachnamens nach dem Vornamen sortiert.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql >
SELECT actor_id , last_name , first_name
FROM actor
ORDER BY last_name , first_name
LIMIT 5 ;
+ -- - - - - - - - -+ - - - - - - - - - - -+ - - - - - - - - - - - -+
| actor_id | last_name | first_name |
+ -- - - - - - - - -+ - - - - - - - - - - -+ - - - - - - - - - - - -+
|
58 | AKROYD
| CHRISTIAN |
|
182 | AKROYD
| DEBBIE
|
|
92 | AKROYD
| KIRSTEN
|
|
118 | ALLEN
| CUBA
|
|
145 | ALLEN
| KIM
|
+ -- - - - - - - - -+ - - - - - - - - - - -+ - - - - - - - - - - - -+
Die Sortierung erfolgt aufsteigend (ASC), wenn man nichts anderes festlegt. So ist folgender SQL gleichbedeutend mit dem vorherigen:
1
2
3
4
5
ASC
mysql >
SELECT actor_id , last_name , first_name
FROM actor
ORDER BY last_name ASC , first_name ASC
LIMIT 5 ;
Man kann für jede Spalte einzeln festlegen, ob die Daten absteigend (DESC) sortiert
werden sollen. Dazu wird an den Stellen, wo jetzt das ASC steht, das DESC gesetzt.
Einführung in relationalen Datenbanken und SQL
49
DESC
3 Implementieren mit SQL
3.4 SQL-DML
Bei der Sortierung ist zu beachten, dass nicht zwischen Groß- und Kleinschreibung bei
zeichenbasierenden Datentypen unterschieden wird, wenn die Spalte nicht mit dem Modifier BINARY spezifiziert wurde.
BINARY
Wie man die passende Zeichenkodierung und die passende länderspezifische Sortierreihenfolge einstellt, muss man der Dokumentation des jeweiligen Servers entnehmen. Bei
MySQL geschieht dies durch die Collation.
3.4.5.4 Mehrfachausgaben unterdrücken
Die Ausgabe aller Nachnamen der actor-Tabelle enthält viele Mehrfachnennungen:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT last_name FROM actor ORDER BY last_name LIMIT 10;
+ -- - - - - - - - - -+
| last_name |
+ -- - - - - - - - - -+
| AKROYD
|
| AKROYD
|
| AKROYD
|
| ALLEN
|
| ALLEN
|
| ALLEN
|
| ASTAIRE
|
| BACALL
|
| BAILEY
|
| BAILEY
|
+ -- - - - - - - - - -+
Insgesamt werden 200 Nachnamen ausgegeben. Möchte man aber nur die unterschiedlichen Nachnamen weiterverarbeiten, also Mehrfachnennungen unterdrücken, so fügt man
das Schlüsselwort DISTINCT unmittelbar hinter das SELECT ein.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT DISTINCT last_name FROM actor ORDER BY last_name LIMIT 10;
+ -- - - - - - - - - -+
| last_name |
+ -- - - - - - - - - -+
| AKROYD
|
| ALLEN
|
| ASTAIRE
|
| BACALL
|
| BAILEY
|
| BALE
|
| BALL
|
| BARRYMORE |
| BASINGER |
| BENING
|
+ -- - - - - - - - - -+
Ohne LIMIT würden nun nur noch 121 Nachnamen ausgegeben werden15 .
15
Die Verwendung von DISTINCT ist sehr rechenintensiv, wenn nicht indizierte Spalten verwendet werden.
Einführung in relationalen Datenbanken und SQL
50
DISTINCT
3 Implementieren mit SQL
3.4 SQL-DML
3.4.5.5 Verknüpfen mit INNER JOIN
In der Regel sind die Informationen innerhalb einer Datenbank auf verschiedene Tabellen verteilt (siehe Abbildung 2.5 auf Seite 19). So entstehen beispielsweise bei der
Normalisierung (siehe Kapitel 2.3.7 auf Seite 27) Tabellen, um Mehrfachangaben zu
vermeiden.
Für die Anzeige oder Weiterverarbeitung müssen diese Informationen wieder zusammengeführt werden. Betrachten Sie dazu das ER-Modell der sakila-Datenbank. Dort finden
Sie die beiden Tabellen staff und store. Die Verknüpfung erfolgt über die Spalte
store_id. Hier wird festgelegt, welcher Mitarbeiter in welchem Geschäft arbeitet ist.
Hier erst der Inhalt der Tabelle staff. Die Einschränkung auf wenige Spalten erfolgt
nicht nur wegen der Übersichtlichkeit, sondern auch, weil die Tabelle eine Spalte vom
Typ BLOB enthält. Den Inhalt solcher Spalten, kann man in der Regel nicht auf der
Konsole ausgeben.
1
2
3
4
5
6
7
8
mysql > SELECT staff_id , last_name , store_id FROM staff ;
+ -- - - - - - - - -+ - - - - - - - - - - -+ - - - - - - - - - -+
| staff_id | last_name | store_id |
+ -- - - - - - - - -+ - - - - - - - - - - -+ - - - - - - - - - -+
|
1 | Hillyer
|
1 |
|
2 | Stephens |
2 |
+ -- - - - - - - - -+ - - - - - - - - - - -+ - - - - - - - - - -+
2 rows in set (0.00 sec )
Nun der Inhalt von store:
1
2
3
4
5
6
7
8
mysql > SELECT * FROM store ;
+ -- - - - - - - - -+ - - - - - - - - - - - - - - - - - -+ - - - - - - - - - - - -+ - - - - - - - - - - - - - - - - - - - - -+
| store_id | manager_staff_id | address_id | last_update
|
+ -- - - - - - - - -+ - - - - - - - - - - - - - - - - - -+ - - - - - - - - - - - -+ - - - - - - - - - - - - - - - - - - - - -+
|
1 |
1 |
1 | 2006 -02 -15 04:57:12 |
|
2 |
2 |
2 | 2006 -02 -15 04:57:12 |
+ -- - - - - - - - -+ - - - - - - - - - - - - - - - - - -+ - - - - - - - - - - - -+ - - - - - - - - - - - - - - - - - - - - -+
2 rows in set (0.00 sec )
Aufgabe 3.18 In welcher Tabelle ist store_id ein Primärschlüssel und in welcher ein
Fremdschlüssel?
Aufgabe 3.19 Zeichnen Sie einen Pfeil vom Fremdschlüsselwert zum passenden Primärschlüsselwert.
Um zu verstehen, wie ein INNER JOIN funktioniert, wollen wir zunächst das Kartesische16
Produkt bilden.
16
Benannt nach René Descartes (1596 - 1650); er war ein französischer Philosoph, Mathematiker und
Naturwissenschaftler.
Einführung in relationalen Datenbanken und SQL
51
Kartesisches
Produkt
3 Implementieren mit SQL
3.4 SQL-DML
Definition 21 Kartesisches Produkt
Bei einem kartesischem Produkt wird jedes Elemente der Menge A mit allen
Elementen der Menge B verknüpft. Sind die beiden Menge endlich ist die
Anzahl der verknüpften Elemente: Anzahl = Anzahl(A) * Anzahl(B). Ein
anderer Name für kartesisches Produkt ist Kreuzprodukt.
Nun das Kartesische Produkt:
1
2
3
4
5
6
7
8
9
10
SELECT staff . staff_id , staff . last_name , staff . store_id , store . store_id , store . addre
FROM staff , store ;
+ -- - - - - - - - -+ - - - - - - - - - - -+ - - - - - - - - - -+ - - - - - - - - - -+ - - - - - - - - - - - -+
| staff_id | last_name | store_id | store_id | address_id |
+ -- - - - - - - - -+ - - - - - - - - - - -+ - - - - - - - - - -+ - - - - - - - - - -+ - - - - - - - - - - - -+
|
1 | Hillyer
|
1 |
1 |
1 |
|
2 | Stephens |
2 |
1 |
1 |
|
1 | Hillyer
|
1 |
2 |
2 |
|
2 | Stephens |
2 |
2 |
2 |
+ -- - - - - - - - -+ - - - - - - - - - - -+ - - - - - - - - - -+ - - - - - - - - - -+ - - - - - - - - - - - -+
Was will uns dieser Befehl sagen: In den ersten beiden Zeilen werden die Spalten ausgewählt, die man sich anschauen möchte. Da man hier eine Auswahl über mehrere Tabellen vornimmt, ist es Konvention vor dem Spaltennamen immer den Tabellennamen oder
einen passenden Alias durch einen Punkt getrennt zu schreiben. Hier ist dies aber nicht
nur eine Konvention, sondern auch notwendig!
Aufgabe 3.20 Lassen Sie die Tabellennamen vor den Spaltennamen weg und interpretieren Sie die Fehlermeldung.
In der Bildschirmausgabe kann man nun gut erkennen, dass jeder Mitarbeiter mit allen
Geschäften verknüpft wurde. Deshalb werden auch 4 Datenzeilen ausgegeben. Aber wir
bemerken auch, dass dieses Ergebnis sinnlos ist. Wir wissen doch durch die Bearbeitung
der Aufgaben 3.19 auf der vorherigen Seite, dass z.B. Hillyer im Geschäft 1 und nicht 2
arbeitet.
Durch scharfes Anschauen der beiden store_id Spalten, zeichnet sich aber auch schon
eine Lösung ab:
1
2
3
4
5
6
7
8
9
SELECT a . staff_id , a . last_name , a . store_id , o . store_id , o . address_id
FROM staff a , store o
WHERE a . store_id = o . store_id ;
+ -- - - - - - - - -+ - - - - - - - - - - -+ - - - - - - - - - -+ - - - - - - - - - -+ - - - - - - - - - - - -+
| staff_id | last_name | store_id | store_id | address_id |
+ -- - - - - - - - -+ - - - - - - - - - - -+ - - - - - - - - - -+ - - - - - - - - - -+ - - - - - - - - - - - -+
|
1 | Hillyer
|
1 |
1 |
1 |
|
2 | Stephens |
2 |
2 |
2 |
+ -- - - - - - - - -+ - - - - - - - - - - -+ - - - - - - - - - -+ - - - - - - - - - -+ - - - - - - - - - - - -+
Einführung in relationalen Datenbanken und SQL
52
3 Implementieren mit SQL
3.4 SQL-DML
Diese Art einen INNER JOIN zu bilden, hat einen entscheidenden Nachteil: Kommen
weitere Bedingungen in der WHERE Klausel vor, wird der Befehl schnell unübersichtlich
und damit fehleranfällig. Aus diesem Grund hat man für den INNER JOIN eine eigene
Syntax eingeführt:
1
2
3
SELECT a . staff_id , a . last_name , a . store_id , o . store_id , o . address_id
FROM
staff a INNER JOIN store o ON a . store_id = o . store_id ;
Links und rechts vom Schlüsselwort INNER JOIN stehen die beiden zu verknüpfenden
Tabellen. Anschließend folgt das Schlüsselwort ON und die Gleichheitsbedingung des
Primär-/Fremdschlüsselpaars.
Aufgabe 3.21 Vertauschen Sie im Befehl die Tabellennamen und vergleichen Sie das
Ergebnis.
Aufgabe 3.22 Vertauschen Sie im Befehl die Primär- und Fremdschlüsselspalte und
vergleichen Sie das Ergebnis.
Falls die Namen des Primärschlüssels und des Fremdschlüssels gleich sind, kann man das
auch deutlich kürzer schreiben:
1
2
3
SELECT a . staff_id , a . last_name , a . store_id , o . store_id , o . address_id
FROM
staff a INNER JOIN store o USING ( store_id );
Definition 22 INNER JOIN
Zwei Tabellen werden per INNER JOIN verknüpft, in dem man nur die
Zeilen des Kartesischen Produkts ausgibt, für welche ein passendes Primär/Fremdschlüsselpaar gefunden wird.
Was diese Definition genau bedeutet, wird erst klar, wenn wir die Daten ein wenig
verändern.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
INSERT INTO staff ( staff_id , last_name , address_id , store_id )
VALUES (3 , ’ Adams ’ , 1 , 1);
INSERT INTO store ( store_id , manager_staff_id , address_id )
VALUES (3 , 3 , 1);
SELECT a . staff_id , a . last_name , a . store_id , o . store_id , o . address_id
FROM
staff a INNER JOIN store o USING ( store_id );
+ -- - - - - - - - -+ - - - - - - - - - - -+ - - - - - - - - - -+ - - - - - - - - - -+ - - - - - - - - - - - -+
| staff_id | last_name | store_id | store_id | address_id |
+ -- - - - - - - - -+ - - - - - - - - - - -+ - - - - - - - - - -+ - - - - - - - - - -+ - - - - - - - - - - - -+
|
1 | Hillyer
|
1 |
1 |
1 |
|
3 | Adams
|
1 |
1 |
1 |
|
2 | Stephens |
2 |
2 |
2 |
+ -- - - - - - - - -+ - - - - - - - - - - -+ - - - - - - - - - -+ - - - - - - - - - -+ - - - - - - - - - - - -+
Einführung in relationalen Datenbanken und SQL
53
INNER
JOIN
3 Implementieren mit SQL
3.4 SQL-DML
Es fällt auf, dass das Geschäft mit der store_id 3 nicht erscheint.
Aufgabe 3.23 Begründen Sie das Ergebnis mit der Definition 22 auf der vorherigen
Seite vom INNER JOIN.
Spätestens bei einer n:m-Verknüpfung muss man mehr als zwei Tabellen mit einander
verbinden. In der sakila Datenbank finden sich solche n:m-Verknüpfungen zwischen den
Tabellen actor und film und den Tabellen category und film.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT f . title , a . last_name
FROM
film f INNER JOIN film_actor USING ( film_id )
INNER JOIN actor a
USING ( actor_id )
LIMIT 10;
+ -- - - - - - - - - - - - - - - - -+ - - - - - - - - - - -+
| title
| last_name |
+ -- - - - - - - - - - - - - - - - -+ - - - - - - - - - - -+
| ACADEMY DINOSAUR | GUINESS
|
| ACADEMY DINOSAUR | GABLE
|
| ACADEMY DINOSAUR | TRACY
|
| ACADEMY DINOSAUR | PECK
|
| ACADEMY DINOSAUR | CAGE
|
| ACADEMY DINOSAUR | TEMPLE
|
| ACADEMY DINOSAUR | NOLTE
|
| ACADEMY DINOSAUR | KILMER
|
| ACADEMY DINOSAUR | DUKAKIS
|
| ACADEMY DINOSAUR | KEITEL
|
+ -- - - - - - - - - - - - - - - - -+ - - - - - - - - - - -+
10 rows in set (0.00 sec )
Das LIMIT 10 ist der Größe der beiden Tabellen geschuldet.
Aufgabe 3.24 Erstellen Sie einen passenden INNER JOIN um die n:m-Verknüpfung
zwischen film und category aufzulösen. Begrenzen Sie die Ausgabe auf Filme mit den
Kategorien Music oder Horror.
Auch aus fachlicher Sicht kann eine Verknüpfung über mehrere Tabellen notwendig sein:
Möchte man beispielsweise wissen, in welchen Kategorien ein bestimmter Schauspieler
(last_name = ’zellweger’) schon mal einen gespielt hat, sind die Tabellen actor,
film_actor, film, film_category und category zu verknüpfen.
1
2
3
4
5
6
SELECT actor . last_name , category . name FROM
actor INNER JOIN film_actor
USING ( actor_id )
INNER JOIN film
USING ( film_id )
INNER JOIN film_category USING ( film_id )
INNER JOIN category
USING ( category_id )
WHERE actor . last_name = ’ zellweger ’
Aufgabe 3.25 Versuchen Sie diesen Befehl anhand des ER-Modells nachzuvollziehen.
Aufgabe 3.26 Wie verhindern Sie die Mehrfachausgabe von gleichen Ergebnissen?
Einführung in relationalen Datenbanken und SQL
54
Index
*, 47
1:1, 18
1:1-Verknüpfung, 19
1:n, 21
1:n-Verknüpfung, 21
3-Schichtenmodell, 46
A
Änderungsweitergabe, 26
Alias, 47
ALTER TABLE, 40
ASC, 49
Atomar, 28
Attribut, 11
B
Berechnungen, 46
BibTeX, 8
BINARY, 50
C
Chen-Notation, 15
COMMIT, 27
CONSTRAINT, 39
CREATE DATABASE, 33
CREATE TABLE, 34, 37
D
Datenfeld, 11
Datensatz, 11
Datentypen, 34
Datentypen: Datum und Zeit, 35
Datentypen: Text, 36
Datentypen: Zahlen, 34
deklarativ, 31
DELETE, 46
DESC, 49
DESCRIBE, 40
DISTINCT, 50
Domäne, 11
DROP TABLE, 38
E
Entität, 11
Entititätentyp, 11
Entity Relationship Model, 15
ER-Modell, 15
F
Feld, 11
FOREIGN KEY, 39
Fremdschlüssel, 16
I
imperativ, 31
INNER JOIN, 53
INSERT INTO, 42
Item, 11
K
Kartesisches Produkt, 51, 52
kaskadierende Änderung, 26
kaskadierendes Löschen, 26
Klasse, 11
konzeptionell eindeutig, 13
Kreuzprodukt, 52
L
LIMIT, 48
LOAD DATA INFILE, 41
Löschkennzeichen, 26
Löschweitergabe, 26
M
Matrix, 11
Einführung in relationalen Datenbanken und SQL
55
Index
Modifier, 37
MVC, 46
MySQL Client, 32
mysql.com, 31
N
n:m, 22
n:m-Verknüpfung, 22
Normalform 1, 28, 30
O
Objekt, 11
ORDER BY, 49
P
Primärschlüsselnamen, 16
Primärschlüssel, 13
Property, 11
R
Record, 11
Recordset, 11
REFERENCES, 39
Referentielle Integrität, 26
Referenz, 17
Relation, 11
Relationale Datenbank, 11
ROLLBACK, 27
Index
Tupel, 11
U
undo, 46
UPDATE, 43
USE, 34
V
Verknüpfung, 17
verletzten referentielle Integrität, 26
Voll- und teilfunktional, 29
vollfunktional, 29
W
WHERE, 43
Wiederholungsgruppefreiheit, 28
Z
Zeile, 10
Zusätze, 34
S
Sakila, 32
Schema, 11
Schlüssel, 12
SELECT, 46
SHOW DATABASES, 32
SHOW TABLES, 34
SHOW WARNINGS, 38
Spalte, 10
T
Tabelle, 10
Tabellenspalte, 10
Tabellenzeile, 10
teilfunktional, 29
Transaktion, 26
Transitiv, 30
Einführung in relationalen Datenbanken und SQL
56