2. Datenbanken

2. Datenbanken
Inhalt
§ 2.1 Datenbankentwurf
§ 2.2 Relationales Modell
§ 2.3 Relationale Entwurfstheorie
§ 2.4 Relationale Algebra
§ 2.5 Structured Query Language (SQL)
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
2
2.1 Datenbankentwurf
§ Datenbankanwendungen werden oft über einen sehr
langen Zeitraum (z. B. Jahrzehnte) eingesetzt
§ Fehler sind umso teurer zu beheben, je weiter die
Entwicklung bzw. der Einsatz der Datenbankanwendung
fortgeschritten sind
§ Datenbankentwurf (d.h. die Planung des Einsatzes der
Datenbank) verdient besondere Aufmerksamkeit
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
3
2.1.1 Phasen des Datenbankentwurf
§ Anforderungsanalyse
§ Welche Aufgaben sollen unterstützt werden?
§ Welche Arten von Objekten gibt es?
§ Ergebnis: Informale Beschreibungen (z.B. Interviewprotokolle)
§ Konzeptueller Entwurf
§ strukturiert Anwendungsbereich (Ausschnitt der realen Welt)
§ unabhängig vom (Typ des) verwendeten DBMS
§ Ergebnis: Konzeptuelles Schema (ERM oder UML)
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
4
Phasen des Datenbankentwurfs
§ Implementationsentwurf
§ setzt Anwendung in Konzepte des verwendeten DBMS um
§ Ergebnis: Relationales Schema (z.B. bei RDBMS)
§ Physischer Entwurf
§ legt Details zur Speicherung und Aufbereitung der Daten fest,
um Leistungsfähigkeit zu optimieren
§ Ergebnis: Indexstrukturen sowie Parameter beispielsweise
bezüglich Komprimierung und Blockgröße
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
5
2.1.2 Entity-Relationship-Modell
§ Entity-Relationship-Modell (ERM)
(deutsch: Gegenstands-Beziehungs-Modell)
§ bereits 1976 von Peter Chen [2] vorgeschlagen
§ verbreitetes Instrument für den konzeptuellen Entwurf
§ wichtiges Mittel zur Kommunikation mit Nicht-Informatikern
452
Beschaffungs- und Vertriebslogistik
Logistikprozesse
453
ARBEilSPlAN
ORT
<
Abb. 8.11.25: Detaillierte Datenstruktur der Vertriebsabwicklung
Quelle: http://www.chen.ethz.ch
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
Nehmen Kunden auf vorangegangene
in Form von Nachbesteliiber einen
lungen Bezug, so ist es sinnvoll, abgeschlossene
Zeitraum hinweg verfiigbar zu halten. Dariiber hinaus konnen iiber
eine Zeitraster-Zuordnung auch statistische Zeitvergleiche durchgefiihrt werden.
Quelle: Scheer [4]
Fiir die
der Vergangenheit werden eigene Beziehungstypen
eingefiihrt, die parallel zu den bereits eingefiihrten Auftragsstrukturen verlauist
fen . Die Trennung zwischen aktiven und vergangenen
sinnvoll, da die Attribute der bereits abgeschlossenen
unterschied-
6
Entity-Relationship-Modell
452
Beschaffungs- und Vertriebslogistik
Logistikprozesse
ARBEilSPlAN
ORT
<
Abb. 8.11.25: Detaillierte Datenstruktur der Vertriebsabwicklung
Quelle: Scheer [4]
Nehmen Kunden auf vorangegangene
in Form von Nachbesteliiber einen
lungen
Bezug,
so
ist
es
sinnvoll,
abgeschlossene
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
Zeitraum hinweg verfiigbar zu halten. Dariiber hinaus konnen iiber
Fiir die
der Vergangenheit werden eigene Beziehungstypen7
45
Bestandteile des Entity-Relationship-Modells
§ Entity-Relationship-Modell (ERM) strukturiert
relevanten Ausschnitt der realen Welt in
§ Entitytypen (entity types)
§ Beziehungstypen (relationships)
§ Achtung:
§ Begrifflichkeiten (z.B. Entity und Entitytyp)
werden in der Literatur teilweise austauschbar verwendet
§ In der Literatur finden sich leicht unterschiedliche
graphische Darstellungen des ERMs
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
8
Entitäten und Entitytypen
§ Entitäten (entities) (auch: Gegenstände) als wohl
unterscheidbare physisch oder gedanklich existierende
Konzepte des zu modellierenden Anwendungsbereichs
§ Beispiel:
§ Max Müller, Prof. Dr. Snuggles, Informatikgrundlagen
§ Ähnliche Entitäten werden zu Entitytypen (auch:
Gegenstandstypen) (entity types) zusammengefasst
§ Beispiel:
§ Studenten, Professoren, Vorlesungen
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
9
Entitytypen im ERM
§ Darstellung: Rechteck um Namen des Entitytyps
Studenten
Professoren
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
Vorlesungen
10
Beziehungen und Beziehungstypen
§ Beziehungen zwischen mehreren konkreten Entitäten
(z.B. Prof. Dr. Snuggles liest Datenbanken)
werden zu Beziehungstypen (relationships) abstrahiert
§ Beispiel: Beziehungstypen in einer Hochschule
§ Studenten hören Vorlesungen
§ Professoren lesen Vorlesungen
§ Professoren prüfen Studenten in Vorlesungen
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
11
Beziehungstypen im ERM
§ Darstellung: Raute um Namen des Beziehungstyps
hören
lesen
prüfen
§ Raute des Beziehungstyps durch ungerichtete Kanten mit
Rechtecken der Entitytypen verbunden
Studenten
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
hören
Vorlesungen
12
Beziehungstypen im ERM
Studenten
hören
Professoren
lesen
Vorlesungen
Professoren
prüfen
Vorlesungen
Vorlesungen
Studenten
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
13
Grade von Beziehungstypen
Studenten
hören
Professoren
prüfen
Vorlesungen
Vorlesungen
n=2
binär
n=3
ternär
Studenten
§ Allgemein: Ein Beziehungstyp mit Grad n heißt n-stellig
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
14
Attribute
§ Attribute (attributes) charakterisieren Entitäten oder
Beziehungen (z.B. Vorname des Professors, Titel der
Vorlesung, Note der Prüfung)
§ Während des konzeptuellen Entwurfs werden die Typen
der Attribute (z.B. Zeichenkette oder Zahl) nicht betrachtet
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
15
Attribute im ERM
§ Darstellung: Oval um Name des Attributs
Name
Titel
Note
§ Oval des Attributs durch ungerichtete Kante mit Rechteck
des Entitytyps oder Raute des Beziehungstyps verbunden
Note
Name
Professoren
prüfen
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
16
Schlüssel
§ Entitytypen wird ein eindeutig identifizierendes Attribut
(oder eine Menge von Attributen) als Schlüssel
zugeordnet
§ Schlüssel müssen so gewählt werden, dass sie Entitäten
nicht nur momentan, sondern immer eindeutig
identifizieren
§ Beispiel: Name kein Schlüssel für Professoren
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
17
Schlüssel
§ Künstliche Attribute dienen häufig als Schlüssel
(z.B. PersNr, MatrNr, ArtNr)
§ Schlüssel müssen minimal sein, d.h. die gewählte Menge
von Attributen soll keine überflüssigen Attribute enthalten
§ Beispiel: PersNr und Name kein Schlüssel für Professoren
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
18
Schlüssel im ERM
§ Darstellung: Name von Schlüsselattributen unterstrichen
PersNr
Professoren
Name
§ Kompaktere Darstellung für Mengen von Attributen
PersNr, Name
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
Professoren
19
ER-Diagramm für Hochschule
MatrNr
VorlNr
Vorname
Studenten
hören
Vorlesungen
SWS
Name
Titel
prüfen
Semester
lesen
Note
PersNr
Vorname
Assistenten
arbeiten für
Professoren
Name
Tätigkeit
Fach
PersNr
Vorname
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
Name
20
Kardinalitäten von Beziehungstypen
§ Beziehungstypen werden bzgl. ihrer Kardinalität (auch:
Funktionalität) charakterisiert, d.h. wie viele Entitäten der
Entitytypen sie in Beziehung setzen
§ Wir betrachten Kardinalitäten binärer Beziehungstypen
R ™ E1 ◊ E2
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
21
1:1
§ Für einen 1:1-Beziehungstyp R gilt, dass
§ jeder Entität aus E1 höchstens ein E2 zugeordnet ist
§ jeder Entität aus E2 höchstens ein E1 zugeordnet ist
§ Beispiele:
§ Person ist verheiratet mit Person (Polygamie außen vor)
§ Kennzeichen gehört zu Fahrzeug (keine Saisonkennzeichen)
§ Darstellung:
E1
1
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
R
1
E2
22
1:N
§ Für einen 1:N-Beziehungstyp R gilt, dass
§ jeder Entität aus E1 beliebig viele E2 zugeordnet ist
§ jeder Entität aus E2 höchstens ein E1 zugeordnet ist
§ Beispiele:
§ Professoren lesen Vorlesungen
§ Bücher beinhalten Kapitel
§ Darstellung:
E1
1
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
R
N
E2
23
N:1
§ Für einen N:1-Beziehungstyp R gilt, dass
§ jeder Entität aus E1 höchstens ein E2 zugeordnet ist
§ jeder Entität aus E2 beliebig viele E1 zugeordnet ist
§ Beispiele:
§ Assistenten arbeiten für Professoren
§ Sportler spielen für Mannschaften
§ Darstellung:
E1
N
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
R
1
E2
24
N:M
§ Für einen N:M-Beziehungstyp R gilt, dass
§ jeder Entität aus E1 beliebig viele E2 zugeordnet ist
§ jeder Entität aus E2 beliebig viele E1 zugeordnet ist
§ Beispiele:
§ Studenten hören Vorlesungen
§ Schauspieler spielen in Filmen
§ Darstellung:
E1
N
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
R
M
E2
25
ER-Diagramm für Hochschule
MatrNr
VorlNr
Vorname
N
Studenten
Name
M
hören
M
Vorlesungen
N
SWS
N
Titel
prüfen
Semester
lesen
Note
PersNr
1
Vorname
Assistenten
N
arbeiten für
1
1
Professoren
Name
Tätigkeit
Fach
PersNr
Vorname
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
Name
26
(min, max)-Notation
§ Genauere Charakterisierung der Kardinalität von
Beziehungstypen mittels (min, max)-Notation
§ Beispiel:
§ jeder Student hört beliebig viele Vorlesungen
§ jede Vorlesung hören mindestens drei Studenten
Studenten
(0,*)
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
hören
(3,*)
Vorlesungen
27
(min, max)-Notation
§ Beispiel:
§ jeder Professor liest beliebig viele Vorlesungen
§ jede Vorlesung wird von genau einem Professor gelesen
Professoren
(0,*)
1
lesen
(1,1)
N
Vorlesungen
§ Achtung: Die Kardinalitäten werden im Vergleich zur
einfachen Notation in umgekehrter Orientierung notiert
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
28
Kardinalitäten als Integritätsbedingungen
§ Kardinalitäten von Beziehungstypen sind
Integritätsbedingungen, d.h. sie sollen nicht nur
momentan, sondern immer zutreffen
§ Beispiel: Auch wenn momentan jede Vorlesung von nur
einem Studenten gehört wird, handelt es sich bei hören
trotzdem um einen N:M-Beziehungstyp
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
29
Schwache Entitytypen
§ Schwache Entitäten existieren nur in Abhängigkeit von
einer anderen starken Entität
§ Beispiel:
§ Räume liegen in Gebäude
(aber existieren nicht ohne dieses Gebäude)
§ Schwache Entitytypen besitzen keinen eigenen
Schlüssel, sondern „erben“ den Schlüssel des
übergeordneten Entitytyps und ergänzen ihn um ein oder
mehrere unterscheidende Attribute
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
30
Schwache Entitäten im ERM
§ Darstellung: Doppeltes Rechteck um Namen, doppelte
Raute um Namen der Abhängigkeitsbeziehungtyp,
unterscheidende Attribute gestrichelt unterstrichen
RaumNr, Kapazität
Räume
N
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
GebNr, Höhe
liegen in
1
Gebäude
31
Generalisierung von Entitytypen
§ Erinnerung: Entitytypen fassen ähnliche Entitäten
zusammen, d.h. sie abstrahieren von der einzelnen
konkreten Entität
§ Generalisierung identifiziert gemeinsame Attribute
mehrerer Entitytypen und lagert diese in gemeinsamen
Obertyp aus
§ Beispiel: Sowohl Assistenten als auch Professoren
besitzen Attribute wie Name, Vorname
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
32
Generalisierung von Entitytypen
§ Beispiel (fortgeführt): Gemeinsame Attribute werden in
gemeinsamen Obertyp Personen ausgelagert
§ Darstellung: Beziehungstyp ist-ein (is-a) als Sechseck
dargestellt verbindet Entitytypen mit ihrem Obertyp
Mitarbeiter
PersNr, Vorname, Name
ist-ein
Tätigkeit
Assistenten
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
ist-ein
Professoren
Fach
33
Aggregation
§ Aggregation ordnet Entitytypen einander zu, die
zusammen einen anderen Entitytypen bilden
§ Beispiel: Fahrräder bestehen aus Rahmen und Rädern;
Rahmen bestehen aus Rohren und Lenker; Räder
bestehen aus Felgen und Speichen
§ Darstellung: Beziehungstyp Teil-von (part-of) als Raute
verbindet den übergeordneten mit dem untergeordneten
Entitytyp
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
34
Aggregation
Fahrräder
Teil-von
Teil-von
Rahmen
Teil-von
Rohre
Teil-von
Lenker
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
Räder
Teil-von
Felgen
Teil-von
Speichen
35
Zusammenfassung
§ Entity-Relationship-Modell für konzeptuellen Entwurf
§ Entitytypen (entity types) (z.B. Studenten)
§ Beziehungstypen (relationships) (z.B. hören)
§ Attribute beschreiben Gegenstände oder Beziehungen
§ Schlüssel identifizieren Gegenstände eindeutig
§ Kardinalitäten charakterisieren Beziehungstypen
§ 1:1-, 1:N- (N:1-), N:M-Beziehungstypen
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
36
Literatur
[1]
A. Kemper und A. Eickler: Datenbanksysteme – Eine
Einführung, De Gruyter Oldenbourg, 2013 (Kapitel 2)
[2]
P. Chen: The Entity-Relationship-Model – Towards a
Unified View of Data, ACM TODS 1(1), 1976
[3]
G. Saake, K.-U. Sattler und A. Heuer:
Datenbanken - Konzepte und Sprachen,
mitp Professional, 2013 (Kapitel 5)
[4]
A.-W. Scheer: Wirtschaftsinformatik – Referenzmodelle
für industrielle Geschäftsprozesse, Springer, 1995
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
37
Rückblick: Datenbankentwurf
§ Entity-Relationship-Modell für konzeptuellen Entwurf
§ Entitytypen (entity types) (z.B. Studenten)
§ Beziehungstypen (relationships) (z.B. hören)
§ Attribute beschreiben Gegenstände oder Beziehungen
§ Schlüssel identifizieren Gegenstände eindeutig
§ Kardinalitäten charakterisieren Beziehungstypen
§ 1:1-, 1:N- (N:1-), N:M-Beziehungstypen
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
38
2.2 Relationales Modell
§ Relationales Modell (auch: Relationenmodell)
§ als das Datenmodell hinter den heute vorherrschenden
relationalen Datenbanksystemen (RDBMS)
§ vorgeschlagen im Jahr 1970 durch Edgar F. Codd [2],
der u.a. dafür den A. M. Turing Award gewonnen hat
§ mengenorientierte Verarbeitung von Daten, die in
sogenannten Relationen (Tabellen) gespeichert werden
Studenten
MatrNr
Vorname
Name
Semester
13765
18877
18879
Moritz
Peter
Marty
Müller
Parker
McFly
1
7
3
Quelle: http://amturing.acm.org
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
39
Wertebereiche
§ Wertebereiche (auch: Domäne) (domain) können alle
Mengen sein, die nur atomare Werte enthalten, z.B.
§ ganze Zahlen (integer)
§ Kommazahlen (float oder real)
§ Zeichenketten (string)
§ Datentypen wie z.B. Datumsangaben, die eine eigene
Struktur haben (Tag, Monat, Jahr) sind also nicht erlaubt
(später: RDBMSs erlauben jedoch solche Datentypen)
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
40
Relationen
§ Relation R ist Untermenge des kartesischen Produkts
(Kreuzprodukts) von n Wertebereichen D1, …, Dn
R ™ D1 ◊ . . . ◊ Dn
§ Beispiel: Relation Studenten mit vier Komponenten
(Matrikelnummer, Vorname, Name und Semester)
Studenten ™ integer ◊ string ◊ string ◊ integer
§ Ein Element der Relation R heißt (n-stelliges) Tupel
§ Beispiel: (13765, Moritz, Müller, 1) als Tupel in Studenten
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
41
Attribute und Schlüssel
§ Komponenten werden zur besseren Identifikation benannt
§ Attribute einer Relationen setzen sich zusammen aus je
§ einem Attributnamen Ai (z.B. MatrNr)
§ einem Wertebereich dom(Ai) (z.B. integer)
§ Attributnamen einer Relationen müssen eindeutig sein
§ Jede Relation verfügt über einen Schlüssel, d.h. eine
Menge von Attributen, die Tupel eindeutig identifizieren
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
42
Relationen
§ Relationen werden nach folgendem Muster spezifiziert
R : {[ A1 : D1 , . . . , An : Dn ]}
dabei werden alle Schlüsselattribute unterstrichen
§ Beispiel: Relation Studenten
Ó
Ô
Studenten : [ MatrNr : integer, Vorname : string, Name : string, Semester : integer ]
mit eindeutig identifizierendem Schlüsselattribut MatrNr
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
43
Relationen als Tabellen
§ Relationen meist als Tabellen in RDBMSs bezeichnet
§ mit Attributen als Spalten der Tabelle
§ mit Tupeln als Zeilen der Tabelle
§ Beispiel: Relation Studenten
Studenten
MatrNr
Vorname
Name
Semester
13765
18877
18879
Moritz
Peter
Marty
Müller
Parker
McFly
1
7
3
mit vier Attributen und drei Tupeln
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
44
Ausprägung vs. Schema
§ Unterscheidung zwischen Ausprägung (konkreten Daten)
und Schema (Attributnamen Ai mit Domänen Di)
§ Schema der Relation sch(R) ist die Menge aller Attribute
sch(R) = {A1 , . . . , An }
§ dom(Ai) bezeichnet die Domäne des Attributs Ai
dom(Ai ) = Di
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
45
2.3 Relationale Entwurfstheorie
§ Wie können wir ein konzeptuelles Modell in Form eines
Entity-Relationship-Diagramms in ein
Relationenschema übersetzen?
§ Welche Eigenschaften hat ein „gutes“ Relationenschema
und wie können wir diese erreichen?
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
46
2.3.1 Übersetzung eines konzeptuellen Modells
§ Wie können wir ein konzeptuelles Modell in Form eines
Entity-Relationship-Diagramms in Relationen übersetzen?
§ Jetzt: Initiale Übersetzung von Entitytypen und
Beziehungstypen in möglichst wenige Relationen
§ Danach: Weitere Optimierung des Relationenschemas
anhand von Normalformen zur Vermeidung von
Redundanzen und Anomalien
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
47
ER-Diagramm für Hochschule
MatrNr
VorlNr
Vorname
N
Studenten
Name
M
hören
M
Vorlesungen
N
SWS
N
Titel
prüfen
Semester
lesen
Note
PersNr
1
Vorname
Assistenten
N
arbeiten für
1
1
Professoren
Name
Tätigkeit
Fach
PersNr
Vorname
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
Name
48
Entitytypen als Relationen
§ Jeder Entitytyp wird zu eigener Relation
§ mit entsprechenden Attributen und Schlüssel
§ Beispiel: Für unser ER-Diagramm Hochschule erhalten wir
Ó
Ô
Studenten : [ MatrNr : integer, Vorname : string, Name : string, Semester : integer ]
Ó
Ô
Vorlesungen : [ VorlNr : integer, SWS : integer, Titel : string ]
Ó
Ô
Professoren : [ PersNr : integer, Vorname : string, Name : string, Fach : string ]
Ó
Ô
Assistenten : [ PersNr : integer, Vorname : string, Name : string, Tätigkeit : string ]
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
49
Beziehungstypen als Relationen
§ Jeder Beziehungstyp wird zu eigener Relation
§ übernimmt Schlüsselattribute verbundener Entitytypen,
diese werden dann als Fremdschlüssel bezeichnet
§ eigene Attribute des Beziehungstyps bleiben erhalten
§ evtl. ist eine Umbenennung von Attributen notwendig
§ Beispiel: Beziehungstyp prüfen wird zur Relation
Ó
Ô
prüfen : [ MatrNr : integer, VorlNr : integer, PersNr : integer, Note : integer ]
§ Schlüssel der Relation ist von der Kardinalität des
Beziehungstyps abhängig
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
50
N:M-Beziehungstypen als Relationen
§ Schlüssel der Relation zu einem N:M-Beziehungstyp
beinhaltet übernommene Attribute
aus allen Fremdschlüsseln
§ Beispiel: Studenten hören Vorlesungen
Ó
Ô
hören : [ MatrNr : integer, VorlNr : integer ]
Wäre nämlich nur eines der übernommenen Attribute im
Schlüssel, könnte jeder Student nur eine Vorlesung hören
(bei MatrNr) bzw. jede Vorlesung könnte nur von einem
Studenten besucht werden (bei VorlNr)
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
51
1:N-Beziehungstypen als Relation
§ Schlüssel der Relation zu einem 1:N-Beziehungstyp
beinhaltet übernommene Attribute
aus dem Fremdschlüssel des rechten Entitytyps E2
§ Beispiel: Professoren lesen Vorlesungen
Ó
Ô
lesen : [ PersNr : integer, VorlNr : integer ]
Wären nämlich beide der übernommenen Attribute
(PersNr und VorlNr) im Schlüssel, könnte eine Vorlesung
von mehreren Professoren gelesen werden
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
52
N:1-Beziehungstypen als Relationen
§ Schlüssel der Relation zu einem N:1 Beziehungstyp
beinhaltet übernommene Attribute
aus dem Fremdschlüssel des linken Entitytyps E1
§ Beispiel: Assistenten arbeiten für Professor
Ó
Ô
arbeitenFür : [ AssPersNr : integer, ProfPersNr : integer ]
In diesem Fall ist eine Umbenennung der übernommenen
Attribute notwendig, da beide ursprünglich gleich heißen
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
53
1:1-Beziehungstypen als Relationen
§ Schlüssel der Relation zu einem 1:1-Beziehungstyp
beinhaltet übernommene Attribute
aus dem Fremdschlüssel nur eines Entitytyps (E1 oder E2)
§ Beispiel: Kennzeichen gehören zu Fahrzeug
Ó
Ô
gehörtZu : [ Kennzeichen : integer, FahrzeugNr : integer ]
oder
Ó
Ô
gehörtZu : [ Kennzeichen : integer, FahrzeugNr : integer ]
Dies garantiert jedoch nicht die Integrität unserer Daten;
mehr zum Thema Datenintegrität später (Kapitel 6)
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
54
Umsetzung der Generalisierung
Mitarbeiter
PersNr, Vorname, Name
ist-ein
Tätigkeit
Assistenten
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
ist-ein
Professoren
Fach
55
Umsetzung der Generalisierung
§ Jeder Entitytyp wird zu eigener Relation
§ Relation des Obertyps enthält gemeinsame Attribute
§ Relationen der Untertypen enthalten eigene Attribute
§ alle Relationen haben gemeinsamen Schlüssel
§ Beispiel: Assistenten und Professoren als Mitarbeiter
Ó
Ô
Mitarbeiter : [ PersNr : integer, Vorname : string, Name : string ]
Ó
Ô
Professoren : [ PersNr : integer, Fach : string ]
Ó
Ô
Assistenten : [ PersNr : integer, Tätigkeit : string ]
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
56
Eliminierung von Relationen
§ Beschriebene Vorgehensweise führt zu vielen Relationen
§ Relationen aus 1:N-, N:1- und 1:1-Beziehungstypen
(nicht jedoch N:M) können eliminiert werden, hierbei gilt:
Nur Relationen mit gleichem Schlüssel zusammenfassen!
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
57
Eliminierung von Relationen (1:N)
§ Bei 1:N-Beziehungstypen werden
der Schlüssel des linken Entitytyps
sowie eventuelle Attribute des Beziehungstyps
in Relation des rechten Entitytyps aufgenommen
§ Beispiel: Professoren lesen Vorlesungen
Ó
Ô
lesen : [ PersNr : integer, VorlNr : integer ]
Ó
Ô
Vorlesungen : [ VorlNr : integer, SWS : integer, Titel : string ]
wird zu
Ó
Ô
Vorlesungen : [ VorlNr : integer, SWS : integer, Titel : string, PersNr : integer ]
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
58
Eliminierung von Relationen (N:1)
§ Bei N:1-Beziehungstypen werden
der Schlüssel des rechten Entitytyps
sowie eventuelle Attribute des Beziehungstyps
in Relation des linken Entitytyps aufgenommen
§ Beispiel: Assistenten arbeiten für Professoren
Ó
Ô
Assistenten : [ PersNr : integer, Vorname : string, Name : string, Tätigkeit : string ]
wird zu
Ó
Ô
arbeitenFür : [ AssPersNr : integer, ProfPersNr : integer ]
Ó
Ô
Assistenten : [ PersNr : integer, . . . , ProfPersNr : integer, ]
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
59
Eliminierung von Relationen (1:1)
§ Bei 1:1-Beziehungstypen werden
der Schlüssel des linken (rechten) Entitytyps
sowie eventuelle Attribute des Beziehungstyps
in Relation des rechten (linken) Entitytyps aufgenommen
§ Beispiel: Kennzeichen gehören zu Fahrzeugen
Ó
Ô
Kennzeichen : [ Kennzeichen : integer, . . . ]
wird zu
Ó
Ô
gehörtZu : [ Kennzeichen : integer, FahrzeugNr : integer ]
Ó
Ô
Kennzeichen : [ Kennzeichen : integer, . . . , FahrzeugNr : integer, ]
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
60
Eliminierung von Relationen (1:1)
§ Beispiel: Kennzeichen gehören zu Fahrzeugen
Ó
Ô
gehörtZu : [ Kennzeichen : integer, FahrzeugNr : integer ]
wird zu
Ó
Ô
Fahrzeuge : [ FahrzeugNr : integer, . . . ]
Ó
Ô
Fahrzeuge : [ FahrzeugNr : integer, . . . , Kennzeichen : integer, ]
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
61
Literatur
[1]
A. Kemper und A. Eickler: Datenbanksysteme – Eine
Einführung, De Gruyter Oldenbourg, 2013
(Kapitel 3)
[2]
E. F. Codd: A Relational Model of Data for Large
Shared Data Banks, CACM 13(6), 1970
[3]
G. Saake, K.-U. Sattler und A. Heuer:
Datenbanken - Konzepte und Sprachen,
mitp Professional, 2013 (Kapitel 4 & 5)
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
62
Rückblick: Relationales Modell
§ Relationales Modell als vorherrschendes Datenmodell
§ Relationen (Tabellen) besitzen Attribute (Spalten) mit
Wertebereichen und beinhalten Tupel (Zeilen)
§ Umsetzung eines konzeptuellen Modells in Relationen
§ Entitytypen werden zu eigenen Relationen
§ Beziehungstypen werden zu eigenen Relationen
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
63
2.3.2 Relationale Normalisierung
§ Bisher: Initiale Übersetzung von Entitytypen und
Beziehungstypen in möglichst wenige Relationen
§ Welche Eigenschaften hat ein „gutes“ Relationenschema
und wie können wir diese erreichen?
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
64
Ein „schlechtes“ Relationenschema
§ Beispiel: Welcher Professor welche Vorlesung liest,
könnten wir mit folgendem Relationenschema festhalten
Vorlesungsplan
PersNr
Vorname
Name
Büro
Fach
VorlNr
Titel
SWS
101101
101101
231011
231011
..
.
Donald
Donald
Albert
Albert
..
.
Knuth
Knuth
Einstein
Einstein
..
.
2781
2781
3141
3141
..
.
Informatik
Informatik
Physik
Physik
..
.
101
110
201
202
..
.
Informatikgrundlagen
Datenbanken
Physik 1
Physik 2
..
.
4
4
4
4
..
.
§ Warum ist das keine „gute“ Idee?
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
65
Redundanz
§ Ein gutes Relationenschema vermeidet Redundanz,
d.h. dass die gleiche Information (oder: der gleiche Fakt)
mehrfach gespeichert wird
§ Beispiel: Wir speichern zweimal, dass
§ Donald Knuth Informatiker ist und im Büro 2781 sitzt
§ Albert Einstein Physiker ist und im Büro 3141 sitzt
Vorlesungsplan
PersNr
Vorname
Name
Büro
Fach
VorlNr
Titel
SWS
101101
101101
231011
231011
..
.
Donald
Donald
Albert
Albert
..
.
Knuth
Knuth
Einstein
Einstein
..
.
2781
2781
3141
3141
..
.
Informatik
Informatik
Physik
Physik
..
.
101
110
201
202
..
.
Informatikgrundlagen
Datenbanken
Physik 1
Physik 2
..
.
4
4
4
4
..
.
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
66
Anomalien
§ Ein gutes Relationenschema vermeidet Anomalien, die
beim Einfügen, Ändern oder Löschen entstehen
§ Beispiel: Wir möchten…
§ Claude Shannon als neuberufenen Professor erfassen,
§ Einfügeanomalie: NULL-Werte für Vorlesung notwendig
Vorlesungsplan
PersNr
Vorname
Name
Büro
Fach
VorlNr
Titel
SWS
101101
101101
231011
231011
..
.
Donald
Donald
Albert
Albert
..
.
Knuth
Knuth
Einstein
Einstein
..
.
2781
2781
3141
3141
..
.
Informatik
Informatik
Physik
Physik
..
.
101
110
201
202
..
.
Informatikgrundlagen
Datenbanken
Physik 1
Physik 2
..
.
4
4
4
4
..
.
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
67
Anomalien
§ Ein gutes Relationenschema vermeidet Anomalien, die
beim Einfügen, Ändern oder Löschen entstehen
§ Beispiel: Wir möchten…
§ Donald Knuth ins Büro 1011 umziehen lassen
§ Änderungsanomalie: Mehrere Zeilen werden angefasst
Vorlesungsplan
PersNr
Vorname
Name
Büro
Fach
VorlNr
Titel
SWS
101101
101101
231011
231011
..
.
Donald
Donald
Albert
Albert
..
.
Knuth
Knuth
Einstein
Einstein
..
.
2781
2781
3141
3141
..
.
Informatik
Informatik
Physik
Physik
..
.
101
110
201
202
..
.
Informatikgrundlagen
Datenbanken
Physik 1
Physik 2
..
.
4
4
4
4
..
.
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
68
Anomalien
§ Ein gutes Relationenschema vermeidet Anomalien, die
beim Einfügen, Ändern oder Löschen entstehen
§ Beispiel: Wir möchten…
§ Albert Einstein in Ruhestand schicken (d.h. löschen)
§ Löschanomalie: NULL-Werte für Professor oder
Verlust der Vorlesungen Physik I und II
Vorlesungsplan
PersNr
Vorname
Name
Büro
Fach
VorlNr
Titel
SWS
101101
101101
231011
231011
..
.
Donald
Donald
Albert
Albert
..
.
Knuth
Knuth
Einstein
Einstein
..
.
2781
2781
3141
3141
..
.
Informatik
Informatik
Physik
Physik
..
.
101
110
201
202
..
.
Informatikgrundlagen
Datenbanken
Physik 1
Physik 2
..
.
4
4
4
4
..
.
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
69
Zerlegung von Relationen
§ Redundanz und Anomalien lassen sich durch eine
Zerlegung von Relationen eliminieren
§ Beispiel: Relation Vorlesungsplan lässt sich zerlegen in
lesen
Professoren
PersNr
Vorname
Name
Büro
Fach
PersNr
101101
231011
..
.
Donald
Albert
..
.
Knuth
Einstein
..
.
2781
3141
..
.
Informatik
Physik
..
.
101101
101101
23101
23101
..
.
Vorlesungen
VorlNr
VorlNr
Titel
SWS
101
110
201
202
..
.
101
110
201
202
..
.
Informatikgrundlagen
Datenbanken
Physik 1
Physik 2
..
.
4
4
4
4
..
.
§ Wie geht man bei der Zerlegung vor?
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
70
Funktionale Abhängigkeiten
§ Betrachten wir eine Relation R mit Schema sch(R)
und seien α ⊆ sch(R) und β ⊆ sch(R) Attributmengen
§ Eine funktionale Abhängigkeit (FA) α ⟶ β besteht, wenn
die Werte der Attribute in α die Werte der Attribute in β
eindeutig bestimmen
§ Anders ausgedrückt: Alle Tupel (Zeilen) mit den gleichen
Werten für Attribute in α müssen auch in den Werten für
Attribute in β übereinstimmen
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
71
Funktionale Abhängigkeiten
§ Beispiel: Funktionale Abhängigkeiten im Vorlesungsplan
Vorlesungsplan
PersNr
Vorname
Name
Büro
Fach
VorlNr
Titel
SWS
101101
101101
231011
231011
..
.
Donald
Donald
Albert
Albert
..
.
Knuth
Knuth
Einstein
Einstein
..
.
2781
2781
3141
3141
..
.
Informatik
Informatik
Physik
Physik
..
.
101
110
201
202
..
.
Informatikgrundlagen
Datenbanken
Physik 1
Physik 2
..
.
4
4
4
4
..
.
§ {PersNr} ⟶ {Vorname, Name, Büro, Fach}
§ {Büro} ⟶ {PersNr, Vorname, Name, Büro, Fach} (Einzelbüros)
§ {VorlNr} ⟶ {PersNr, Vorname, Name, Büro, Fach, Titel, SWS}
§ {VorlNr} ⟶ {Titel, SWS}
§ {PersNr, VorlNr} ⟶ {Vorname, Name, Büro, Fach}
§ {PersNr, VorlNr} ⟶ {Vorname, Name, Büro, Fach, Titel, SWS}
§ …
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
72
Funktionale Abhängigkeiten
§ Beispiel: Funktionale Abhängigkeiten im Vorlesungsplan
Vorlesungsplan
PersNr
Vorname
Name
Büro
Fach
VorlNr
Titel
SWS
101101
101101
231011
231011
..
.
Donald
Donald
Albert
Albert
..
.
Knuth
Knuth
Einstein
Einstein
..
.
2781
2781
3141
3141
..
.
Informatik
Informatik
Physik
Physik
..
.
101
110
201
202
..
.
Informatikgrundlagen
Datenbanken
Physik 1
Physik 2
..
.
4
4
4
4
..
.
§ Funktionale Abhängigkeiten müssen auf allen denkbaren
Ausprägungen gelten, nicht nur auf der aktuellen
§ {Vorname, Name} ⟶ {Büro} z.B. gilt nicht,
da es mehrere Professoren gleichen
Namens geben könnte
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
73
Volle funktionale Abhängigkeit
§ Betrachten wir eine Relation R mit Schema sch(R)
und seien α ⊆ sch(R) und β ⊆ sch(R) Attributmengen
§ Eine volle funktionale Abhängigkeit besteht, wenn
§ die funktionale Abhängigkeit α ⟶ β gilt
§ die Attributmenge α nicht verkleinert werden kann
(d.h. es gibt kein Attribut A, so dass α \ {A} ⟶ β gilt)
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
74
Volle funktionale Abhängigkeit
§ Beispiel: Volle FA‘en im Vorlesungsplan
Vorlesungsplan
PersNr
Vorname
Name
Büro
Fach
VorlNr
Titel
SWS
101101
101101
231011
231011
..
.
Donald
Donald
Albert
Albert
..
.
Knuth
Knuth
Einstein
Einstein
..
.
2781
2781
3141
3141
..
.
Informatik
Informatik
Physik
Physik
..
.
101
110
201
202
..
.
Informatikgrundlagen
Datenbanken
Physik 1
Physik 2
..
.
4
4
4
4
..
.
§ {PersNr} ⟶ {Vorname, Name, Büro, Fach}
§ {VorlNr} ⟶ {Titel, SWS}
§ {Büro} ⟶ {PersNr, Vorname, Name, Fach}
§ {PersNr, VorlNr} ⟶ {Vorname, Name, Büro, Fach, Titel, SWS}
§ keine volle FA z.B.: {PersNr, VorlNr} ⟶ {Titel, SWS}
da PersNr entfernt werden kann
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
75
Kandidatenschlüssel
§ Attributmenge α ⊆ sch(R) ist Kandidatenschlüssel einer
Relation R, wenn folgende Bedingungen erfüllt sind
§ es gilt die funktionale Abhängigkeit α ⟶ sch(R)
(d.h. die Werte der Attribute in α bestimmen gesamtes Tupel)
§ α kann nicht weiter verkleinert werden
(d.h. es gibt kein Attribut A, so dass α \ {A} ⟶ sch(R) gilt)
§ Zu einer Relation R kann es mehrere Kandidatenschlüssel
geben; einer davon wird als Primärschlüssel ausgewählt
§ Nichtschlüssel-Attribute sind Attribute,
die nicht Teil eines Kandidatenschlüssel sind
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
76
Kandidatenschlüssel
§ Beispiel: Kandidatenschlüssel von Professoren
Professoren
PersNr
Vorname
Name
Büro
Fach
101101
231011
..
.
Donald
Albert
..
.
Knuth
Einstein
..
.
2781
3141
..
.
Informatik
Physik
..
.
§ {PersNr} und {Büro} sind Kandidatenschlüssel
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
77
Relationale Normalformen
§ Man unterscheidet fünf relationale Normalformen
§ Erste Normalform (1NF)
§ Zweite Normalform (2NF)
§ Dritte Normalform (3NF)
§ Boyce-Codd Normalform (BCNF)
4NF
BCNF
3NF
§ Vierte Normalform (4NF)
2NF
1NF
§ Die in der Praxis wichtigste ist die dritte Normalform
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
78
Erste Normalform (1NF)
§ Eine Relation ist in erster Normalform (1NF), wenn
§ alle Attribute atomare Wertebereiche haben
(d.h. keine zusammengesetzten Wertebereichen)
§ Beispiel: Relation Hausmeister ist nicht in 1NF
Hausmeister
PersNr
Vorname
Name
Zuständigkeiten
5011
8898
..
.
Karl
Argus
..
.
Eder
Filch
..
.
{G1, G3, G5}
{G2, G4}
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
79
Erste Normalform (1NF)
§ Die Relation Hausmeister lässt sich durch Zerlegung
in zwei Relationen in die erste Normalform bringen
Hausmeister
PersNr
Vorname
Name
Zuständigkeiten
5011
8898
..
.
Karl
Argus
..
.
Eder
Filch
..
.
{G1, G3, G5}
{G2, G4}
Hausmeister
Zuständigkeiten
PersNr
Vorname
Name
PersNr
Gebäude
5011
8898
..
.
Karl
Argus
..
.
Eder
Filch
..
.
5011
5011
5011
8898
8898
..
.
G1
G3
G5
G2
G4
..
.
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
80
Zweite Normalform (2NF)
§ Eine Relation ist in zweiter Normalform (2NF), wenn
§ sie in 1NF ist
§ jedes Nichtschlüssel-Attribut A voll funktional abhängig
von jedem Kandidatenschlüssel der Relation ist
§ Beispiel: Relation Prüfungsergebnisse nicht in 2NF
Prüfungsergebnisse
Matr
Vorname
Name
VorlNr
Titel
Note
13765
13765
18877
18877
..
.
Moritz
Moritz
Peter
Peter
..
.
Müller
Müller
Parker
Parker
..
.
101
110
101
110
..
.
Informatikgrundlagen
Datenbanken
Informatikgrundlagen
Datenbanken
..
.
2.3
1.3
1.3
1.0
..
.
z.B. Vorname ist nicht voll funktional abhängig vom
einzigen Kandidatenschlüssel {MatrNr, VorlNr}
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
81
Zweite Normalform (2NF)
§ Relation Prüfungsergebnisse lässt sich durch Zerlegung
in drei Relationen in die zweite Normalform bringen
Prüfungsergebnisse
Matr
Vorname
Name
VorlNr
Titel
Note
13765
13765
18877
18877
..
.
Moritz
Moritz
Peter
Peter
..
.
Müller
Müller
Parker
Parker
..
.
101
110
101
110
..
.
Informatikgrundlagen
Datenbanken
Informatikgrundlagen
Datenbanken
..
.
2.3
1.3
1.3
1.0
..
.
Studenten
Vorlesungen
Prüfungen
Matr
Vorname
Name
Matr
VorlNr
Note
VorlNr
Titel
SWS
13765
18877
..
.
Moritz
Peter
..
.
Müller
Parker
..
.
13765
13765
18877
18877
..
.
101
110
101
110
..
.
2.3
1.3
1.3
1.0
..
.
101
110
..
.
Informatikgrundlagen
Datenbanken
..
.
4
4
..
.
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
82
Dritte Normalform (3NF)
§ Eine Relation ist in dritter Normalform (3NF), wenn
§ sie in 2NF ist
§ kein Nichtschlüsselattribut A transitiv
von einem Kandidatenschlüssel abhängt
§ Beispiel: Relation Professoren nicht in 3NF
Professoren
PersNr
Vorname
Name
Büro
Fach
Fakultät
101101
330123
887234
..
.
Donald
Adam
Norman
..
.
Knuth
Smith
Foster
..
.
2781
7762
9966
..
.
Informatik
Volkswirtschaftslehre
Architektur
..
.
IngWi
WiWi
AuB
..
.
da Fakultät von Fach und damit transitiv von den
Kandidatenschlüsseln {PersNr} und {Büro} abhängt
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
83
Dritte Normalform (3NF)
§ Relation Professoren lässt sich durch Zerlegung
in zwei Relationen in die dritte Normalform bringen
Professoren
PersNr
Vorname
Name
Büro
Fach
Fakultät
101101
330123
887234
..
.
Donald
Adam
Norman
..
.
Knuth
Smith
Foster
..
.
2781
7762
9966
..
.
Informatik
Volkswirtschaftslehre
Architektur
..
.
IngWi
WiWi
AuB
..
.
Professoren
Fächer
PersNr
Vorname
Name
Büro
Fach
Fach
Fakultät
101101
330123
887234
..
.
Donald
Adam
Norman
..
.
Knuth
Smith
Foster
..
.
2781
7762
9966
..
.
Informatik
Volkswirtschaftslehre
Architektur
..
.
Informatik
Volkswirtschaftslehre
Architektur
..
.
IngWi
WiWi
AuB
..
.
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
84
Zusammenfassung
§ „Gute“ Relationenschema vermeiden Redundanz
und führen nicht zu Anomalien beim
Einfügen, Löschen oder Ändern
§ Relationale Normalformen (1NF, 2NF, 3NF, BCNF, 4NF)
charakterisieren die Güte von Relationenschema
§ Dritte Normalform (3NF) ist in der Praxis am wichtigsten
§ Relationenschema lassen sich durch Zerlegung
in die dritte Normalform bringen
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
85
Literatur
[1]
A. Kemper und A. Eickler: Datenbanksysteme – Eine
Einführung, De Gruyter Oldenbourg, 2013 (Kapitel 6)
[2]
G. Saake, K.-U. Sattler und A. Heuer:
Datenbanken - Konzepte und Sprachen,
mitp Professional, 2013 (Kapitel 6)
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
86
Rückblick: Relationale Normalisierung
§ „Gute“ Relationenschema vermeiden Redundanz
und führen nicht zu Anomalien beim
Einfügen, Löschen oder Ändern
§ Relationale Normalformen (1NF, 2NF, 3NF, BCNF, 4NF)
charakterisieren die Güte von Relationenschema
§ Dritte Normalform (3NF) ist in der Praxis am wichtigsten
§ Relationenschema lassen sich durch Zerlegung
in die dritte Normalform bringen
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
87
2.4 Relationale Algebra
§ Relationales Modell bietet Formalismus zur Beschreibung
zu speichernder Daten und deren Struktur
§ Wie können wir Daten extrahieren, d.h. Anfragen auf den
gespeicherten Daten formulieren?
§ Beispiele:
§ Welche Vorlesungen hört der Student Marty McFly?
§ Welche Professoren halten Vorlesungen,
die nur von Studenten im ersten Semester besucht werden?
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
88
Relationale Algebra
§ Relationale Algebra (auch: Relationenalgebra) bietet
eine Anfragesprache auf Relationen
§ Operatoren bilden Ausdrücke der Relationenalgebra
§ Ausdrücke beinhalten implizit einen Auswertungsplan
§ spielt wichtige Rolle bei Implementierung von RDBMSs
(zur internen Darstellung und Optimierung von Anfragen)
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
89
Selektion
§ Selektion σ (sigma) wählt Tupel (Zeilen) aus einer
Relation R anhand einer Selektionsbedingung P aus
‡[P](R)
§ Selektionsbedingung kann folgende Bestandteile haben
§ Attributnamen der Relation R oder Konstanten
§ Arithmetische Vergleichsoperatoren =, <, ≤, ≥, ≠
§ Logische Operatoren ¬, ⋀, ⋁
§ Ergebnis der Selektion ist wiederum eine Relation
bestehend aus allen Tupeln aus R, die P erfüllen
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
90
Selektion
§ Beispiel: Erstsemester aus Relation Studenten
Studenten
MatrNr
Vorname
Name
Semester
13765
18877
18898
18979
18979
Moritz
Peter
Gordon
Marty
Benjamin
Müller
Parker
Shumway
McFly
Berger
1
7
1
3
1
‡ [ Semester = 1 ] ( Studenten )
‡ [ Semester = 1 ] ( Studenten )
MatrNr
Vorname
Name
Semester
13765
18898
18979
Moritz
Gordon
Benjamin
Müller
Shumway
Berger
1
1
1
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
91
Projektion
§ Projektion π (pi) wählt Attribute (Spalten) einer Relation R
anhand einer gegebenen Attributmenge A aus
fi[A](R)
§ Attributmenge A als Liste von Attributnamen angegeben
§ Ergebnis der Projektion ist eine Relation, die Tupel mit den
in A angegebenen Attributen enthält und frei von
Duplikaten ist (Relationen sind Mengen)
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
92
Projektion
§ Beispiel: Vorname und Fach von Professoren
Professoren
PersNr
Vorname
Name
Fach
101101
231011
300128
478122
600321
Donald
Albert
Alfred
Donald
Carl
Knuth
Einstein
Nobel
Kossmann
Gauss
Informatik
Physik
Chemie
Informatik
Mathematik
fi [ Vorname, Fach ] ( Professoren )
fi [ Vorname, Fach ] ( Professoren )
Vorname
Fach
Donald
Albert
Alfred
Carl
Informatik
Physik
Chemie
Mathematik
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
Duplikate
eliminiert
93
Kartesisches Produkt (Kreuzprodukt)
§ Das Kreuzprodukt zweier Relationen R und S enthält alle
|R| * |S| möglichen Paare von Tupeln aus R und S
R◊S
§ Ergebnis des Kreuzprodukts ist eine Relation mit Schema
sch(R ◊ S) = sch(R) fi sch(S)
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
94
Kartesisches Produkt (Kreuzprodukt)
§ Beispiel: Kombinationen von Professoren und Vorlesungen
Professoren
Vorlesungen
PersNr
Vorname
Name
Fach
101101
231011
300128
478122
600321
Donald
Albert
Alfred
Donald
Carl
Knuth
Einstein
Nobel
Kossmann
Gauss
Informatik
Physik
Chemie
Informatik
Mathematik
◊
VorlNr
Titel
SWS
101
110
Informatikgrundlagen
Datenbanken
4
4
Professoren ◊ Vorlesungen
PersNr
Vorname
Name
101101
101101
231011
231011
..
.
Donald
Donald
Albert
Albert
..
.
Knuth
Knuth
Einstein
Einstein
..
.
Fach
VorlNr
Titel
SWS
Informatik
Informatik
Physik
Physik
..
.
101
110
101
110
..
.
Informatikgrundlagen
Datenbanken
Informatikgrundlagen
Datenbanken
..
.
4
4
4
4
..
.
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
95
Qualifizierte Attributnamen
§ Verbundene Relationen R und S können identisch
benannte Attribute besitzen (z.B. Vorname und Name in
Professoren und Studenten)
§ Qualifizierte Attributnamen stellen dem Attributnamen
den Namen der Ursprungsrelation voran und erlauben so
eine Unterscheidung von identisch benannten Attributen
§ Beispiel:
Professoren ◊ Studenten
...
Professoren.Vorname
...
Studenten.Vorname
...
..
.
..
.
..
.
..
.
..
.
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
96
Umbenennung von Relationen und Attributen
§ Qualifizierte Attributnamen reichen zur Unterscheidung
nicht aus, wenn eine Ursprungsrelation mehrfach auftaucht
§ Beispiel: Paare von Artikeln mit gleichen Mengeneinheiten
‡ [ ??? ] ( Artikel ◊ Artikel )
§ Umbennungsoperator ρ (rho) erlaubt Umbenennung von
§ Relationen (z.B. Artikel in ArtikelLinks)
fl[ Artikel ](ArtikelLinks)
§ Attributen (z.B. Fach in Fachgebiet von Professoren)
fl[ Fachgebiet Ω Fach ](Professoren)
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
97
Mengenoperationen auf Relationen
§ Zur Erinnerung: Relationen sind Mengen von Tupeln
§ Für zwei Relationen R und S mit identischem Schema
sch(R) = sch(S)
sind die bekannten Mengenoperationen (Vereinigung,
Schnitt und Differenz) wie bekannt definiert
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
98
Vereinigung
§ Beispiel: Namen von Studenten oder Professoren
fi [ Name ] ( Studenten ) fi fi [ Name ] ( Professoren )
fi [ Name ] ( Studenten )
fi [ Name ] ( Professoren )
Name
Name
Müller
Meier
McFly
fi
Knuth
Meier
Einstein
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
Name
=
Name
Müller
Meier
McFly
Knuth
Einstein
99
Schnitt
§ Beispiel: Namen von Studenten und Professoren
fi [ Name ] ( Studenten ) fl fi [ Name ] ( Professoren )
fi [ Name ] ( Studenten )
fi [ Name ] ( Professoren )
Name
Name
Name
Name
Müller
Meier
McFly
fl
Knuth
Meier
Einstein
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
=
Meier
100
Differenz
§ Beispiel: Namen von Studenten nicht aber Professoren
fi [ Name ] ( Studenten ) ≠ fi [ Name ] ( Professoren )
fi [ Name ] ( Studenten )
fi [ Name ] ( Professoren )
Name
Name
Name
Name
Müller
Meier
McFly
≠
Knuth
Meier
Einstein
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
=
Müller
McFly
101
Natürlicher Join
§ Natürlicher Join (Verbund) verbindet zwei Relationen
R und S anhand ihrer gemeinsamen Attribute
R ÛÙ S
§ Ergebnis des natürlichen Joins ist Relation mit Schema
sch(R) fi sch(S)
§ Hierbei werden Tupel aus R und S verbunden, deren Werte
für alle gemeinsamen Attribut übereinstimmen
§ Attribute gleichen Namens werden zusammengefasst
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
102
Natürlicher Join
§ Beispiel: Studenten mit ihren gehörten Vorlesungen
(Studenten ÛÙ hören) ÛÙ Vorlesungen
hören
Studenten
MatrNr
Vorname
Name
Semester
13765
18877
18898
18979
18979
Moritz
Peter
Gordon
Marty
Benjamin
Müller
Parker
Shumway
McFly
Berger
1
7
1
3
1
ÛÙ
MatrNr
VorlNr
13765
18979
18979
101
101
110
Vorlesungen
ÛÙ
VorlNr
Titel
SWS
101
110
Informatikgrundlagen
Datenbanken
4
4
(Studenten ÛÙ hören) ÛÙ Vorlesungen
MatrNr
Vorname
Name
Semester
VorlNr
Titel
SWS
13765
18979
18979
Moritz
Benjamin
Benjamin
Müller
Berger
Berger
1
1
1
101
101
110
Informatikgrundlagen
Informatikgrundlagen
Datenbanken
4
4
4
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
103
Allgemeiner Join
§ Allgemeiner Join verbindet zwei Relationen R und S
anhand eines beliebigen Joinprädikates θ (theta)
R ÛÙ [ ◊ ] S
§ Ergebnis des allgemeinen Joins ist Relation mit Schema
{ fl [ R.A Ω A ] ( R ) | A œ sch(R) }
fi
{ fl [ S.A Ω A ] ( S ) | A œ sch(S) }
§ Attribute gleichen Namens werden umbenannt, d.h. mit
dem Namen der Ursprungsrelation qualifiziert
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
104
Allgemeiner Join
§ Beispiel: Professoren und Studenten mit gleichem Namen
Professoren ÛÙ [ Professoren.Name = Studenten.Name ] Studenten
(S)tudenten
(P)rofessoren
PersNr
Vorname
Name
Fach
101101
231011
300128
478122
600321
Donald
Albert
Alfred
Donald
Carl
Knuth
Einstein
Nobel
Kossmann
Gauss
Informatik
Physik
Chemie
Informatik
Mathematik
ÛÙ [ P.Name = S.Name ]
MatrNr
Vorname
Name
Semester
18979
19880
19882
20020
Gundula
Petra
Nora
Emma
Gauss
Parker
Nobel
Ernst
1
1
3
3
P ÛÙ [ P.Name = S.Name ] S
P.PersNr
P.Vorname
P.Name
P.Fach
S.MatrNr
S.Vorname
S.Name
S.Semester
300128
600321
Alfred
Carl
Nobel
Gauss
Chemie
Mathematik
19882
18979
Nora
Gundula
Nobel
Gauss
3
1
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
105
Allgemeiner Join
§ Allgemeiner Join ist letztlich eine Kurzschreibweise für
R ÛÙ [ ◊ ] S = ‡ [ ◊ ] ( R ◊ S )
§ Verwendet das Joinprädikat θ nur Vergleiche mittels „=“
spricht man auch von einem Equi-Join
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
106
Äußere Joins
§ Natürlicher und allgemeiner Join erhalten nur solche
Tupel aus R und S, die einen „Joinpartner“ finden
§ Äußere Joins erhalten auch solche Tupeln aus einer oder
beiden Relationen, die keinen „Joinpartner“ finden
§ linker äußerer Join (left outer join) erhält alle Tupel aus R
R ÛÙ [ ◊ ] S
§ rechter äußerer Join (right outer join) erhält alle Tupel aus S
R ÛÙ [ ◊ ] S
§ äußerer Join (full outer join) erhält alle Tupel aus R und S
R ÛÙ [ ◊ ] S
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
107
Linker äußerer Join
§ Beispiel: Professoren und Studenten mit gleichem Namen
(S)tudenten
(P)rofessoren
PersNr
Vorname
Name
Fach
MatrNr
Vorname
Name
Semester
101101
231011
300128
478122
600321
Donald
Albert
Alfred
Donald
Carl
Knuth
Einstein
Nobel
Kossmann
Gauss
Informatik
Physik
Chemie
Informatik
Mathematik
18979
19880
19882
20020
Gundula
Petra
Nora
Emma
Gauss
Parker
Nobel
Ernst
1
1
3
3
§ linker äußerer Join
P ÛÙ [ P.Name = S.Name ] S
P.PersNr
P.Vorname
P.Name
P.Fach
S.MatrNr
S.Vorname
S.Name
S.Semester
101101
231011
300128
478122
600321
Donald
Albert
Alfred
Donald
Carl
Knuth
Einstein
Nobel
Kossmann
Gauss
Informatik
Informatik
Chemie
Informatik
Mathematik
19882
Nora
Nobel
3
18979
Gundula
Gauss
1
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
108
Rechter äußerer Join
§ Beispiel: Professoren und Studenten mit gleichem Namen
(S)tudenten
(P)rofessoren
PersNr
Vorname
Name
Fach
MatrNr
Vorname
Name
Semester
101101
231011
300128
478122
600321
Donald
Albert
Alfred
Donald
Carl
Knuth
Einstein
Nobel
Kossmann
Gauss
Informatik
Physik
Chemie
Informatik
Mathematik
18979
19880
19882
20020
Gundula
Petra
Nora
Emma
Gauss
Parker
Nobel
Ernst
1
1
3
3
§ rechter äußerer Join
P ÛÙ [ P.Name = S.Name ] S
P.PersNr
P.Vorname
P.Name
P.Fach
S.MatrNr
S.Vorname
S.Name
S.Semester
600321
Carl
Gauss
Mathematik
300128
Alfred
Nobel
Chemie
18979
19880
19882
20020
Gundula
Petra
Nora
Emma
Gauss
Parker
Nobel
Ernst
1
1
3
3
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
109
Äußerer Join
§ Beispiel: Professoren und Studenten mit gleichem Namen
(S)tudenten
(P)rofessoren
PersNr
Vorname
Name
Fach
MatrNr
Vorname
Name
Semester
101101
231011
300128
478122
600321
Donald
Albert
Alfred
Donald
Carl
Knuth
Einstein
Nobel
Kossmann
Gauss
Informatik
Physik
Chemie
Informatik
Mathematik
18979
19880
19882
20020
Gundula
Petra
Nora
Emma
Gauss
Parker
Nobel
Ernst
1
1
3
3
§ äußerer Join
P ÛÙ [ P.Name = S.Name ] S
P.PersNr
101101
231011
300128
478122
600321
P.Vorname
Donald
Albert
Alfred
Donald
Carl
P.Name
Knuth
Einstein
Nobel
Kossmann
Gauss
P.Fach
Informatik
Informatik
Chemie
Informatik
Mathematik
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
S.MatrNr
S.Vorname
S.Name
S.Semester
19880
Petra
Parker
1
19882
Nora
Nobel
3
18979
20020
Gundula
Emma
Gauss
Ernst
1
3
110
Operatorbaumdarstellung
§ Ausdrücke der relationalen Algebra lassen sich alternativ
auch als sogenannte Operatorbäume darstellen
§ Beispiel:
fi [ S.Vorname, S.Name, V.Titel ] ( (‡ [ Semester > 3 ] ( S ) ÛÙ h) ÛÙ V )
fi[S.Vorname, S.Name, V.Titel]
ÛÙ
V
ÛÙ
‡[ Semester > 3 ]
h
S
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
111
Grenzen der relationalen Algebra
§ Können wir mit den Operatoren der relationalen Algebra
alle „interessanten“ Anfragen formulieren?
§ Beispiel: Anzahl von Studenten pro Vorlesung
(Studenten ÛÙ hören) ÛÙ Vorlesungen
MatrNr
Vorname
Name
Semester
VorlNr
Titel
SWS
13765
18979
18979
Moritz
Benjamin
Benjamin
Müller
Berger
Berger
1
1
1
101
101
110
Informatikgrundlagen
Informatikgrundlagen
Datenbanken
4
4
4
Gruppierung nach Vorlesung und „Zählen“ notwendig!
§ Erweiterungen zum Gruppieren & Aggregieren existieren
(vgl. z.B. Kapitel 9 in Saake et al. [3])
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
112
Anfrageübung Versandhandel
Ó
Ô
Bestellungen : [ BestellNr : integer, Bestelldatum : string, . . . , KundenNr : integer, ]
Ó
Ô
Kunden : [ KundenNr : integer, Vorname : string, Name : string, . . . ]
Ó
Ô
Artikel : [ ArtikelNr : integer, Beschreibung : string, . . . , ME : string ]
Ó
Ô
Mengeneinheiten : [ ME : string, Beschreibung : string, . . . ]
Ó
Ô
Bestellpositionen : [ BestellNr : integer, ArtikelNr : integer , Anzahl : integer ]
§ Welche Kunden heißen Müller oder Meier?
§ Welche Vornamen haben Kunden aus Mannheim?
§ Welche Artikel (ArtikelNr) werden im 5er-Pack verkauft?
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
113
Anfrageübung Versandhandel
§ Welche Kunden heißen Müller oder Meier?
‡ [ Name = “Meier” ‚ Name = “Müller” ] ( Kunden )
§ Welche Vornamen haben Kunden aus Mannheim?
fi [ Vorname ] ( ‡ [ Wohnort = “Mannheim” ] ( Kunden ) )
§ Welche Artikel (ArtikelNr) werden im 5er-Pack verkauft?
fi[ Artikel.ArtikelNr ](
‡[ Artikel.ME = Mengeneinheiten.ME ](
Artikel ◊ ‡[ Beschreibung = “5er-Pack” ](Mengeneinheiten)))
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
114
Anfrageübung Versandhandel
Ó
Ô
Bestellungen : [ BestellNr : integer, Bestelldatum : string, . . . , KundenNr : integer, ]
Ó
Ô
Kunden : [ KundenNr : integer, Vorname : string, Name : string, . . . ]
Ó
Ô
Artikel : [ ArtikelNr : integer, Beschreibung : string, . . . , ME : string ]
Ó
Ô
Mengeneinheiten : [ ME : string, Beschreibung : string, . . . ]
Ó
Ô
Bestellpositionen : [ BestellNr : integer, ArtikelNr : integer , Anzahl : integer ]
§ Welche Artikel (ArtikelNr) werden im 5er-Pack verkauft?
§ Welche Artikel (ArtikelNr) wurden am gleichen Tag von
Kunden aus Worms und Kunden aus Trier bestellt?
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
115
Anfrageübung Versandhandel
§ Welche Artikel (ArtikelNr) werden im 5er-Pack verkauft?
fi [ Artikel.ArtikelNr ] ( Artikel ÛÙ ‡ [ Beschreibung = “5er-Pack” ] ( ME ) )
§ Welche Artikel (ArtikelNr) wurden am gleichen Tag von
Kunden aus Worms und Kunden aus Trier bestellt?
fi[W.ArtikelNr](
fl [ W ] ( ‡ [ Wohnort = “Worms” ] ( (Kunden ÛÙ Bestellungen) ÛÙ Bestellpositionen ) )
ÛÙ [ W.ArtikelNr = T.ArtikelNr · W.Bestelldatum = T.Bestelldatum ]
fl [ T ] ( ‡ [ Wohnort = “Trier” ] ( (Kunden ÛÙ Bestellungen) ÛÙ Bestellpositionen ) ) )
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
116
Zusammenfassung
§ Relationale Algebra als Anfragesprache auf Relationen
§ Ausdrücke zusammengesetzt aus Operatoren, z.B.
§ Selektion (σ) zur Auswahl von Tupeln
§ Projektion (π) zur Auswahl von Attributen
§ Kreuzprodukt (×) und Joins (⨝, ⟕, ⟖, ⟗)
zur Verknüpfung von Relationen
§ Mengenoperationen (∩,∪, −)
zur Verknüpfung von Relationen mit gleichem Schema
§ Mehrdeutige Namen von Attributen und Relationen
können durch Umbenennung eliminiert werden
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
117
Literatur
[1]
A. Kemper und A. Eickler: Datenbanksysteme – Eine
Einführung, De Gruyter Oldenbourg, 2013 (Kapitel 3)
[3]
G. Saake, K.-U. Sattler und A. Heuer:
Datenbanken - Konzepte und Sprachen,
mitp Professional, 2013 (Kapitel 4)
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
118
Rückblick: Relationale Algebra
§ Relationale Algebra als Anfragesprache auf Relationen
§ Ausdrücke zusammengesetzt aus Operatoren, z.B.
§ Selektion (σ) zur Auswahl von Tupeln
§ Projektion (π) zur Auswahl von Attributen
§ Kreuzprodukt (×) und Joins (⨝, ⟕, ⟖, ⟗)
zur Verknüpfung von Relationen
§ Mengenoperationen (∩,∪, −)
zur Verknüpfung von Relationen mit gleichem Schema
§ Mehrdeutige Namen von Attributen und Relationen
können durch Umbenennung eliminiert werden
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
119
2.5 Structured Query Language (SQL)
§ Structured Query Language (SQL)
§ geht zurück auf den in IBM Almaden (San Jose)
entwickelten Prototypen System R
§ ursprünglich: Structured English Query Language (SEQUEL)
§ auf Englisch wird SQL noch immer sequel gesprochen
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
120
SQL als Standard
§ SQL standardisiert durch American National Standards
Institute (ANSI) und International Organization for
Standardization (ISO)
§ SQL-86 / SQL-89
§ SQL-92 (z.B. Datentypen für Daten, Mengenoperationen)
§ SQL-99 (z.B. rekursive Anfragen)
§ SQL-2003 (z.B. Unterstützung von XML)
§ SQL-2008 / SQL-2011
§ Oracle, IBM DB2 und Microsoft SQL Server unterstützen
SQL-92 weitgehend und bieten darüber hinaus
proprietäre Funktionalität (z.B. XML-Unterstützung)
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
121
SQL Sprachbestandteile
§ Data Definition Language (DDL) zur
Schemadefinition (z.B. Anlegen von Tabellen)
§ Data Query Language (DQL) zum
Anfragen (z.B. Auswahl bestimmer Zeilen)
§ Data Manipulation Language (DML) zur
Datenmanipulation (z.B. Einfügen von Daten in Tabellen)
§ Data Control Language (DCL) zur
Rechteverwaltung (z.B. Sperren des Zugriffs auf Tabelle)
§ Transaction Control Language (TCL) zur
Transaktionsverwaltung (z.B. rückgängig machen)
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
122
2.5.1 Anfragen auf einer Tabelle
§ Anfragen lassen sich mittels des SELECT Kommandos
formulieren; dieses hat folgende Form
1
2
3
4
SELECT
FROM
WHERE
ORDER BY
< Attribute >
< Tabellen >
< Bedingungen >
< Attribute >
§ Beispiel: Vorname und Name von Professoren in Informatik
1
2
3
SELECT Vorname , Name
FROM Professoren
WHERE Fach = ’ Informatik ’
dies entspricht folgendem Ausdruck der Relationenalgebra
fi [ Vorname, Name ] ( ‡ [ Fach = “Informatik” ] ( Professoren ) )
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
123
Duplikateneliminierung
§ Anfrageergebnisse können, im Gegensatz zu
Ergebnisrelationen der Relationanalgebra,
Duplikate enthalten
Professoren
PersNr
Vorname
Name
Fach
101101
231011
300128
478122
600321
Donald
Albert
Alfred
Donald
Carl
Knuth
Einstein
Nobel
Kossmann
Gauss
Informatik
Physik
Chemie
Informatik
Mathematik
Vorname
1
2
SELECT Vorname
FROM Professoren
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
Donald
Albert
Alfred
Donald
Carl
124
Duplikateneliminierung
§ Duplikate im Anfrageergebnis können durch Angabe von
DISTINCT unterdrückt werden
Professoren
PersNr
Vorname
Name
Fach
101101
231011
300128
478122
600321
Donald
Albert
Alfred
Donald
Carl
Knuth
Einstein
Nobel
Kossmann
Gauss
Informatik
Physik
Chemie
Informatik
Mathematik
Vorname
1
2
SELECT DISTINCT Vorname
FROM Professoren
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
Donald
Albert
Alfred
Carl
125
Sortierung
§ Anfrageergebnisse können, im Gegensatz zu
Ergebnisrelationen der Relationanalgebra,
eine Ordnung haben
§ Sortierung des Anfrageergebnis nach ein oder mehreren
Attributen durch Angabe durch ORDER BY
§ Natürliche Ordnung der Attribute wird gemäß ihres
Datentyps verwendet, d.h. numerische Attribute (z.B. int
und float) werden nach numerischem Wert, textuelle
Attribute (z.B. char und varchar) lexikografisch sortiert
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
126
Sortierung
§ Angabe von ASC bzw. DESC bestimmt, ob nach einem
Attribut aufsteigend oder absteigend sortiert wird
§ ASC ist hier Vorgabewert (default) und kann entfallen
§ Beispiel: Vorname, Name und Fach von Professoren,
aufsteigend sortiert nach Nachname und Vorname
1
2
3
4
SELECT
FROM
WHERE
ORDER BY
Vorname , Name , Fach
Professoren
Fach = ’ Mathematik ’
Name , Vorname
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
127
Sortierung
§ Beispiel: Vorname und Name von Studenten, absteigend
sortiert nach ihrer Anzahl von Semestern
1
2
3
SELECT Vorname , Name
FROM Studenten
ORDER BY Semester DESC
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
128
Formatierung und Namenskonventionen
§ Ähnlich zu Programmiersprachen, gibt es auch für SQL
verschiedene Konvetionen zur Bennenung von
Bezeichnern und Formatierung von Kommandos
§ Schlüsselwörter (CREATE vs. create)
§ Attributnamen (Bestell_Nr vs. BestellNr)
§ Tabellennamen (Kunden vs. Kunde)
§ Formatierung von Kommandos (Klammern und Umbrüche)
§ Letztlich Geschmacksache,
Konsistenz innerhalb eines Projekts ist jedoch wichtig
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
129
Mathematische Funktionen
§ SQL unterstützt die mathematischen Operatoren (+, *, -, /)
und eine Vielzahl mathematischer Funktionen, z.B.
§ ABS(A): Betrag
§ SIGN(A): Vorzeichen
§ SQRT(A): Quadratwurzel
§ FLOOR(A): Abrunden auf ganze Zahl
§ CEIL(A): Aufrunden auf ganze Zahl
§ MAX(A,B): Maximum der Attribute A und B
§ MIN(A,B): Minimum der Attribute A und B
§ …
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
130
Sonstige Funktionen
§ SQL unterstützt zahlreiche Funktionen für
nichtnumerische Datentypen, etwa für Zeichenketten
§ LOWER(A): Zeichenkette in Kleinbuchstaben
§ UPPER(A): Zeichenkette in Großbuchstaben
§ LENGTH(A): Länge der Zeichenkette
§ SUBSTRING(A, start, end): Ausschnitt der Zeichenkette
§ TRIM(A): Zeichenkette ohne umgebende Leerzeichen
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
131
Aggregatfunktionen
§ SQL unterstützt eine Vielzahl von Aggregatfunktionen,
die auf die Werte eines Attributs in mehreren Tupeln
angewendet werden können, z.B.:
§ MIN(A): minimaler Wert für Attribut A
§ MAX(A): maximaler Wert für A
§ AVG(A): durchschnittlicher Wert für A
§ SUM(A): Summe der Werte für A
§ COUNT(*): Anzahl Tupel
§ COUNT(A): Anzahl Tupel mit Wert ungleich NULL für A
§ COUNT(DISTINCT A): Anzahl Werte ungleich NULL für A
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
132
Aggregatfunktionen
§ Beispiel:
§ MIN(A) = 1
§ MAX(A) = 3
§ AVG(A) = 8 / 4 = 2
§ COUNT(*) = 5
§ COUNT(A) = 4
A
1
2
NULL
2
3
§ COUNT(DISTINCT A) = 3
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
133
Aggregatfunktionen
§ Beispiel: Maximale, minimale und durchschnittliche
Semesteranzahl innerhalb Studenten der Physik
1
2
3
4
5
SELECT MAX ( Semester ) ,
MIN ( Semester ) ,
AVG ( Semester )
FROM Studenten
WHERE Fach = ’ Physik ’
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
134
Selektionsbedingungen
§ Selektionsbedingungen, in WHERE-Klausel, erlauben
mehr als Vergleich zwischen Attribut und Konstante
§ Vergleichsoperatoren (=, <>, >, <, >=, <=)
1
2
3
SELECT Vorname , Name
FROM Studenten
WHERE Semester > 10
§ Vergleiche zwischen Attributen
1
2
3
SELECT *
FROM Bestellungen
WHERE Bestelldatum = Lieferdatum
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
135
Selektionsbedingungen
§ Boole‘sche Operatoren (NOT, OR, AND) mit der Rangfolge
NOT vor AND vor OR und ggf. Klammerung
§ Beispiel: Studenten weder von Informatik noch Physik
SELECT *
FROM Studenten
WHERE Fach != ’ Informatik ’ AND Fach != ’ Physik ’
1
2
3
oder
1
2
3
SELECT *
FROM Studenten
WHERE NOT ( Fach = ’ Informatik ’ OR Fach = ’ Physik ’)
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
136
Mustervergleiche in Zeichenketten
§ Textuelle Attribute kann man mittels LIKE gegen ein
Muster vergleichen; hierbei dient % als Platzhalter
(wildcard) und repräsentiert kein oder mehrere Zeichen
§ Beispiel: Vorlesungen mit „Grundlagen“ im Titel
1
2
3
SELECT *
FROM Vorlesungen
WHERE Titel LIKE ’% Grundlagen % ’
wenn auch „Informatikgrundlagen“ gefunden werden soll
1
2
3
SELECT *
FROM Vorlesungen
WHERE LOWER ( Titel ) LIKE ’% grundlagen % ’
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
137
Wertemengen
§ Mittels des Kommandos IN kann überprüft werden, ob der
Wert eines Attributs in einer gegebenen Wertemenge liegt
§ Beispiel: Studenten mit Vornamen Max oder Moritz
1
2
3
SELECT *
FROM Studenten
WHERE Vorname IN ( ’ Max ’ , ’ Moritz ’)
§ Die Wertemenge darf auch mittels einer
SQL-Unteranfrage bestimmt werden,
dazu später mehr
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
138
2.5.2 Anfragen über mehrere Tabellen
§ SQL unterstützt verschiedene Kommandos, um zwei oder
mehr Tabellen miteinander zu verknüpfen; hierzu
werden mehrere Tabellen in der FROM-Klausel erwähnt und
Joinprädikate in der WHERE-Klausel angegeben
§ SQL kennt zudem eine alternative Syntax, die dazu dient,
Joinprädikate von Selektionsprädikaten zu trennen
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
139
Kartesisches Produkt
§ Kartesisches Produkt zweier Tabellen lässt sich durch
deren Nennung in der FROM-Klausel oder durch
Verwendung des CROSS JOIN Kommandos ermitteln
§ Beispiel: Kombinationen von Studenten und Professoren
1
2
SELECT *
FROM Studenten , Professoren
oder
1
2
SELECT *
FROM Studenten CROSS JOIN Professoren
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
140
Natürlicher Join
§ Natürlicher Join zweier Tabellen lässt sich durch deren
Nennung in der FROM-Klausel und Angabe der
gemeinsamen Attribute in der WHERE-Klausel oder durch
Verwendung des NATURAL JOIN Kommandos ermitteln
§ Beispiel: Vorlesungen mit zugehörigen Professoren
SELECT *
FROM Vorlesungen , Professoren
WHERE Vorlesungen . PersNr = Professoren . PersNr
1
2
3
oder
1
2
SELECT *
FROM Vorlesungen NATURAL JOIN Professoren
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
141
Allgemeiner Join
§ Allgemeiner Join zweier Tabellen lässt sich durch deren
Nennung in der FROM-Klausel und Angabe des
Joinprädikats in der WHERE-Klausel oder durch
Verwendung des JOIN ON Kommandos ermitteln
§ Beispiel: Professoren und Studenten gleichen Namens
1
2
3
SELECT *
FROM Professoren , Studenten
WHERE Professoren . Name = Studenten . Name
oder
1
2
3
4
SELECT
FROM
JOIN
ON
*
Professoren
Studenten
Professoren . Name = Studenten . Name
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
142
Mengenoperationen
§ Die Kommandos UNION, INTERSECT und EXCEPT
bilden die Mengenoperationen ∪, ∩ und ∖ ab und
dürfen nur auf Tabellen mit kompatiblen Schemata
angewendet werden
§ Beispiel: Namen, die nur bei Studenten, aber nicht bei
Professoren vorkommen
1
2
3
4
5
SELECT
FROM
EXCEPT
SELECT
FROM
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
Name
Studenten
Name
Professoren
143
Umbenennung
§ Taucht die gleiche Tabelle mehrfach in einem SQL
Kommando auf, können die einzelnen Auftreten
benannt werden
§ Beispiel: Paare von Studenten mit gleichem Fach
1
2
3
SELECT *
FROM Studenten s1 , Studenten s2
WHERE s1 . Fach = s2 . Fach
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
144
Unteranfragen
§ SQL unterstützt Unteranfragen, d.h. eine SELECT
Kommando kann häufig anstelle einer Tabelle oder
einer Wertemenge verwendet werden
§ Beispiel: Studenten mit einem Vornamen, den es auch
unter den Professoren gibt
1
2
3
SELECT *
FROM Studenten
WHERE Vorname IN ( SELECT Vorname FROM Professoren )
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
145
Umbenennung
§ SQL erlaubt die Umbenennung von Tabellen und
Attributen; dies ist insbesondere dann nützlich, wenn die
gleiche Tabelle mehrfach verwendet werden soll
§ Beispiel: Studenten, die eine gemeinsame Vorlesung hören
1
2
3
4
5
SELECT
FROM
WHERE
AND
AND
DISTINCT s1 . Name AS StudentEins , s2 . Name AS StudentZwei
Studenten s1 , h ö ren h1 , h ö ren h2 , Studenten s2
s1 . MatrNr = h1 . MatrNr
h1 . VorlNr = h2 . VorlNr
h2 . MatrNr = s2 . MatrNr
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
146
Äußere Joins
§ SQL unterstützt äußere Joins mittels der
(LEFT|FULL|RIGHT) OUTER JOIN Kommandos
§ Beispiel: Professoren und Studenten mit gleichem Namen
(bei Erhaltung aller Professoren)
1
2
3
SELECT *
FROM Professoren LEFT OUTER JOIN Studenten
ON Professoren . Name = Studenten . Name
P.PersNr
P.Vorname
P.Name
P.Fach
S.MatrNr
S.Vorname
S.Name
S.Semester
101101
231011
300128
478122
600321
Donald
Albert
Alfred
Donald
Carl
Knuth
Einstein
Nobel
Kossmann
Gauss
Informatik
Informatik
Chemie
Informatik
Mathematik
19882
Nora
Nobel
3
18979
Gundula
Gauss
1
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
147
2.5.3 Gruppierung & Aggregation
§ Aggregatfunktionen wurde bisher nur auf gesamte
Tabellen angewendet; oft möchte man diese
jedoch auf Gruppen von Tupeln anwenden
§ Beispiele:
§ Durchschnittliche Note je Vorlesung
§ Semesterwochenstunden je Professor
§ Anzahl der Studenten pro Fach
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
148
Gruppierung
§ Aggregatfunktionen lassen sich mittels des GROUP BY
Kommandos auf Gruppen von Tupeln anwenden; dieses
hat folgende allgemeine Form
1
2
3
4
5
6
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
< Projektionsattribute > , < Aggregate >
< Tabellen >
< Bedingungen auf Attributen >
< Gruppierattribute >
< Bedingungen auf Aggregaten >
< Sortierattribute > , < Aggregate >
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
149
Gruppierung
§ Tupel mit den gleichen Werten für die angegebenen
Gruppierattribute bilden eine Gruppe, für die ein
Wert der Aggregate berechnet wird
§ Beispiel: Fächer absteigend sortiert nach Anzahl Studenten
1
2
3
4
SELECT
FROM
GROUP BY
ORDER BY
Fach , Count (*) AS Anzahl
Studenten
Fach
Anzahl DESC
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
150
Gruppierung
§ Beispiel: Anzahl Professoren eines Namens je Fach
1
2
3
SELECT Fach , Name , Count (*)
FROM Professoren
GROUP BY Fach , Name
§ Projektionsattribute sind in der Regel identisch mit den
Gruppierattributen; sie müssen eine Teilmenge sein
§ Sortierattribute müssen eine Teilmenge von
Gruppierattributen und Aggregaten sein
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
151
Gruppierung mit Selektion auf Attributen
§ Soll die Gruppierung nur solche Tupel berücksichtigen,
deren Attribute eine bestimmte Bedingung erfüllen,
so kann dies mittels einer WHERE-Klausel
erreicht werden
§ Beispiel: Anzahl Professoren namens Meier je Fach
1
2
3
4
SELECT
FROM
WHERE
GROUP BY
Fach , Count (*) AS Anzahl
Professoren
Name = ’ Meier ’
Fach
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
152
Gruppierung mit Selektion auf Aggregaten
§ Soll die Gruppierung nur solche Tupel zurückliefern,
deren Aggregate eine bestimmte Bedingung erfüllen,
so kann dies mittels einer HAVING-Klausel
erreicht werden
§ Beispiel: Fächer mit mehr als 10 Professoren
1
2
3
4
SELECT
FROM
GROUP BY
HAVING
Fach , Count (*) AS Anzahl
Professoren
Fach
Anzahl > 10
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
153
Gruppierung mit Selektion
§ SQL-Anfrage kann sowohl eine WHERE-Klausel als auch
eine HAVING-Klausel besitzen und es kann
mehrere Aggregate geben
§ Beispiel: Anzahl Professoren in Fächern, die auf
„ik“ enden und mehr als 5 Professoren haben,
alphabetisch sortiert
1
2
3
4
5
6
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
Fach , COUNT (*)
Professoren
Fach LIKE ’% ik ’
Fach
COUNT (*) > 5
Fach
154
Verarbeitung der Gruppierung
§ WHERE
(d.h. betrachte nur Professoren, deren Fach auf „ik“ endet)
§ GROUP BY
(d.h. gruppiere verbleibende Professoren nach Fach)
§ HAVING
(d.h. eliminiere Fächer mit weniger als 5 Professoren)
1
§ ORDER BY
(d.h. sortiere verbleibende Fächer)
2
3
4
5
6
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
Fach , COUNT (*)
Professoren
Fach LIKE ’% ik ’
Fach
COUNT (*) > 5
Fach
155
Top-k Anfragen
§ Bei Bildung von Aggregaten sind wir häufig nur an einer
bestimmten Anzahl von Tupeln mit höchstem oder
niedrigstem Aggregatwert interessiert; dies lässt sich
durch Verwendung des TOP (MS SQL Server) oder
LIMIT Kommandos (SQLite) erzielen
§ Beispiel: 5 Fächer mit den meisten Professoren
MS SQL Server:
1
2
3
4
SELECT
FROM
GROUP BY
ORDER BY
TOP 5 Fach , COUNT (*) AS Anzahl
Professoren
Fach
Anzahl DESC
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
156
Top-k Anfragen
§ Beispiel: 5 Fächer mit den meisten Professoren
SQLite:
1
2
3
4
5
SELECT
FROM
GROUP BY
ORDER BY
LIMIT
Fach , COUNT (*) AS Anzahl
Professoren
Fach
Anzahl DESC
5
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
157
2.5.4 Quantifizierung
§ SQL unterstützt mittels der Kommandos EXISTS und
NOT EXISTS den Existenzquantor ∃ und
den Nichtexistenzquantor ∄
§ Beispiel: Professoren zu denen es Studenten
mit gleichem Vornamen gibt
1
2
3
4
SELECT *
FROM Professoren p
WHERE EXISTS
( SELECT * FROM Studenten s WHERE p . Vorname = s . Vorname )
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
158
Quantifizierung
§ Allquantor ∀ wird nicht explizit unterstützt; kann aber
mittels des Nichtexistenzquantors ∄ ausgedrückt werden
§ Beispiel: Studenten, die alle Vorlesungen hören
(d.h. es gibt kein Vorlesung, die der Student nicht hört)
1
2
3
4
5
6
7
8
9
10
SELECT *
FROM Studenten s
WHERE NOT EXISTS
( SELECT *
FROM Vorlesungen v
WHERE NOT EXISTS
( SELECT *
FROM h ö ren
WHERE s . MatrNr = h . MatrNr
AND h . VorlNr = v . VorlNr ))
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
159
Zusammenfassung
§ Structured Query Language (SQL) als standardisierte
Anfragesprache für relationale Datenbanken
§ Data Query Language zum Formulieren von Anfragen
(SELECT ... FROM ... WHERE ... ORDER BY ...)
§ mathematische Funktionen (z.B. ABS(A) und SIGN(A))
§ Aggregatfunktionen (z.B. MIN(A) und SUM(A))
§ Boole‘sche Operatoren (AND, OR, EXCEPT)
§ Verknüpfungen von mehreren Tabellen (JOINs)
§ Gruppierung (GROUP BY)
§ Quantifizierung (EXISTS)
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
160
Literatur
[1]
A. Kemper und A. Eickler: Datenbanksysteme – Eine
Einführung, De Gruyter Oldenbourg, 2013 (Kapitel 4)
[2]
G. Saake, K.-U. Sattler und A. Heuer:
Datenbanken - Konzepte und Sprachen,
mitp Professional, 2013 (Kapitel 7 & 10)
Datenbanken & Informationssysteme / Kapitel 2: Datenbanken
161