SQL HOTEL - Interbase

AKAD HOCHSCHULEN FÜR BERUFSTÄTIGE
SQL – Structured Query Language
Beispiel Hotel
Prof. Thomas Müller
Seminartext für das
Diplomandenseminar Wirtschaftsinformatik
Alle SQL-Beispiele dieses Seminartextes wurden unter dem
Datenbanksystem INTERBASE® von Borland entwickelt und
getestet. Die SQL-Beispiele lassen sich jedoch auch mit dem
Datenbanksystem MS ACCESS® von Microsoft ausführen.
Sie finden die Hoteldatenbank HOTEL.MDB zum Download
unter der Adresse www.profmueller.de/akad
ST/HfBR/DS-EDV K9039
2
AKAD Diplomandenseminar Wirtschaftsinformatik
Übersicht über die Seminarinhalte
1.
Systemanalyse
2.
Datenbanksysteme
1.1
Systembegriff
2.1
1.2
Ansätze zur Systemanalyse
Ziele und Strategien der Datenorganisation, Datenbankbegriffe
2.2
Das relationale Datenbankmodell (RDBMS)
2.3
Einsatzbeispiel RDBMS (Hoteldatenbank)
1.2.1 Konventioneller Ansatz und Kritik am Lifecycle-Konzept
1.2.2 Prototyping
2.3.1 Relationenmodell und Normalisierung
1.3
2.3.2 Selektion, Projektion und Join
Erhebungs- und Darstellungstechniken
1.3.1 Beobachtung, Interview
2.3.3 Weitere Anfragetypen
1.3.2 Structured (System) Analysis
2.4
1.3.3 Entscheidungstabellentechnik (mit Übungsaufgabe)
Semantische Datenmodelle (EntityRelationship-Diagramm)
2.5
Übungsaufgabe Datenbanken
1.4
3.
Individuelle Datenverarbeitung
1.4.1 Hilfsmittel des Algorithmendesigns
3.1
Begriff und Abgrenzung
1.4.2 Graphische Darstellung von Algorithmen
(Struktogramme)
3.2
Ausgewählte IDV-Anwendungen
Systemimplementierung
1.4.3 Stepwise Refinement
1.5
Projektplanung mit Netzplantechnik
1.5.1 Vorgang, Ereignis, Knoten, Kanten
1.5.2 Beispiel eines Netzplanes
3.2.1 Textverarbeitung
3.2.2 Hypertext
3.2.3 Groupware
3.2.4 Tabellenkalkulation
1.5.3 Übungsaufgabe Netzplantechnik
Die in der obigen Übersicht hervorgehobenen Abschnitte werden in diesem Seminartext ausführlich behandelt und vertieft.
© Copyright
Prof. Thomas Müller, Grundhof / AKAD, 1995, 1999, 2001, 2002, 2003
Alle Rechte vorbehalten.
Vervielfältigung sowie sonstige Verbreitung und Verwertung – auch auszugsweise –
nur mit schriftlicher Genehmigung des Autors.
ST/HfBR/DS-EDV K9039
3
Inhaltsverzeichnis
1.
Strukturierung der Daten (Normalisierung)
4
1.1. Relationenmodell
4
1.2. Daten in unnormalisierter Form
5
1.3. Speichereffizienz der unnormalisierten Form durch Mengen
5
1.4. Redundanzprobleme
6
1.5. Anomalieprobleme
6
1.5.1.
Einfügeanomalien
6
1.5.2.
Änderungsanomalien
7
1.5.3.
Löschanomalien
7
1.6. Problem der Nullwerte
8
1.7. Normalformen von Relationen
8
1.7.1.
Arten der Abhängigkeit
8
1.7.2.
Erste Normalform (1NF)
9
1.7.3.
Zweite Normalform (2NF)
10
1.7.4.
Dritte Normalform (3NF)
12
1.8. Integritätsbedingungen
13
1.8.1.
Referentielle Integrität durch Fremdschlüsselbeziehungen
13
1.8.2.
Weitere Integritätsbedingungen
15
2.
Einrichtung der Datenbank (DDL)
18
3.
Erfassung von Daten (DML)
22
4.
Anfragen an die Datenbasis (Retrieval)
24
4.1. DML Statement SELECT
24
4.2. Anfragetypen Selektion, Projektion und Join
25
4.3. Operatoren in SELECT-Statements
28
4.4. SQL-Funktionen
29
4.5. Subqueries
30
4.6. SQL-Klauseln
32
4.6.1.
ORDER BY-Klausel
32
4.6.2.
GROUP BY-Klausel
33
4.6.3.
HAVING-Klausel
36
4.6.4.
UNION-Klausel
36
4.7. Verwendung von Literalen
38
5.
Änderung und Löschung von Daten
39
6.
Entwicklung von Views für die Datenbank
41
7.
Hinweise zu den normalisierten Relationen
45
8.
Literaturhinweise
46
9.
Stichwortverzeichnis
47
10. Daten des Beispiels
49
11. ER-Diagramm zum Hotelbeispiel
50
12. Abbildungsverzeichnis
51
ST/HfBR/DS-EDV K9039
4
Strukturierung der Daten (Normalisierung)
1.
Strukturierung der Daten (Normalisierung)
1.1. Relationenmodell
Das hier darzustellende Relationenmodell stellt reale Sachverhalte (Daten) in tabellarischer Form dar.
Zu den zu betrachtenden Sachverhalten gehören
die Objekte (Entitäten, entities) mit ihren Eigenschaften (Attributen),
die Beziehungen (relationship) zwischen diesen Objekten, ebenfalls mit ihren Eigenschaften (Beziehungsattribute).
Attribute
Entitytyp
Attributwerte
KdNr
Name
Wohnort
100
Müller
Hagen
101
AKAD
102
Meier
103
Schmidt
Pinneberg
Flensburg
Entities
Kiel
Domänen
z.B.:
KdNr:
Name: Zeichenkette mit 40 Zeichen
Abb. 1: Begriffe des Relationenmodells
Jeder Sachverhalt entspricht einer Zeile (Tupel oder Entities) in einer solchen Tabelle. Eine Relation
ist also eine Menge solcher Tupel. Jedes Tupel wird dabei durch eine endliche Menge gleicher Eigenschaften (Attribute) beschrieben und durch unterschiedliche Eigenschaftswerte (Attributwerte) spezifiziert. Die Attribute entsprechen dabei den Spaltenüberschriften der Tabelle, die Attributwerte den
jeweiligen Eintragungen in einer Zeile.
1
Ausgehend von einer unnormalisierten Form werden die Daten durch schrittweise Zerlegung (Aufspaltung), den Normalisierungsprozess, in mehrere Relationen aufgeteilt, die den spezifischen Notwendigkeiten der betrachteten Objekte und insbesondere ihrer Beziehungen untereinander gerecht
werden.
Die Normalisierung der Relationen dient folgenden Zielen:
1. Vermeidung von Redundanz (mehrmaliges Festhalten ein- und desselben Sachverhaltes).
2. Vermeidung der infolge von Redundanz entstehenden Anomalien und NULL-Werte (Probleme
bei der Datenmanipulation wie Einfügungen, Löschungen in bzw. Aktualisierung der Datenbasis).
3. Sicherung der Daten-Integrität (Speichern realitätskonformer Sachverhalte).
4. Einsparung von Speicherplatz.
Wichtig dabei ist jedoch, dass sich aus jeder im Rahmen des Prozesses gebildeten Form der Daten
(Relationen) die ursprünglich in der unnormalisierten Form vorhandene Information wieder rekon-
1
Von unnormalisierter Form spricht man, wenn alle benötigten Daten unabhängig von ihren jeweiligen Beziehungen sich in einer Tabelle befinden.
ST/HfBR/DS-EDV K9039
Strukturierung der Daten (Normalisierung)
5
struieren lässt2: Die Normalisierung darf also nicht zu Informationsverlusten führen. Unter Normalisierung versteht man also die verlustfreie Zerlegung einer Relation in mehrere Relationen. Dies wird
dadurch erreicht, dass alle durch Teilung verlorengehenden Beziehungen durch Aufnahme der identifizierenden Eigenschaften (Primärschlüssel) in beide neuen Relationen und Aufbau von Fremd3- und
Primärschlüsselbeziehungen bzw. -verweisen festgehalten werden.
Zusätzlich erhält jede Relation im Rahmen der Normalisierung ein Schlüsselattribut, dessen Attributwert einen Tupel eindeutig identifiziert (Bedingung der Entitätsintegrität). Als Schlüssel kommen
dabei entweder ein einzelnes Attribut einer Relation oder auch eine Kombination mehrerer Attribute in
Betracht (zusammengesetzter Schlüssel). Einen solchen Schlüssel nennt man Primärschlüssel.
1.2. Daten in unnormalisierter Form
Daten in unnormalisierter Form sind daran erkennbar, dass sie Attribute besitzen, die sich aus mehreren Elementen zusammensetzen (z.B. Gast mietet Zimmer mehrmals hintereinander). Daher können
in der unnormalisierten Relation am Kreuzungspunkt von Spalten und Zeilen mehrere Werte (sog.
Mengen) auftreten.
Die Ausgangsrelation für die Datenbank Hotel, die jeweils ein Vermietverhältnis beschreibt, kann in
unnormalisierter Form wie folgt abgebildet werden:
R.Vermietung
Name
Wohnort
Strasse
ZiNr
Art
Ausst.
Dauer
Miete
Abb. 2: Unnormalisierte Relation Vermietung
Beispieldaten (Tupel) der Relation Vermietung zeigen dabei die Möglichkeit des Auftretens von Mengen in der Relation:
R.Vermietung
Name
Wohnort
Strasse
ZiNr
Art
Ausst.
Dauer
Miete
Müller
Hagen
Weg 1
100
EZ
DBF
{1,4}
60.00
Abb. 3: Unnormalisierte Relation Vermietung mit Mengen
Die in dieser Tabelle beschriebenen Sachverhalte besagen, dass der Gast Müller aus Hagen zweimal
das Zimmer 100 gemietet hat. Einmal betrug die Mietdauer einen, das zweite Mal vier Tage.
In dieser unnormalisierten Form die nachfolgend beschriebenen Probleme auf.
1.3. Speichereffizienz der unnormalisierten Form durch Mengen
In der Relation R.Vermietung treten Mengen auf: Im Beispiel können in der Spalte Dauer mehrere
Einträge erfolgen, da ja ein Gast mehrfach hintereinander ein Zimmer mieten kann. Dies widerspricht
rein formal der Forderung, dass Werte im Kreuzungspunkt von Spalten (Attributen) und Zeilen (Enti4
ties) atomar (nicht weiter zerlegbar) sein müssen . Darüber hinaus ist damit das Problem weiterer
Kombinationen (selber Gast, selbes Zimmer, selbe Dauer) noch gar nicht angesprochen; Dies würde
dazu führen, dass alle Attribute zweier Tupel identisch wären. Da unbekannt ist, wieviele Attributwerte
entstehen können, müsste ein entsprechend ausreichender Speicherplatz für diese Mengen bevorratet werden. Dieser müsste bei der Speicherung eines jeden Satzes vorgesehen werden, da der Direktzugriff auf Daten jeweils zur Berechnung der Speicheradresse eine feste Satzlänge voraussetzt.
2
Man nennt diese Bedingung die Referentielle Integritätsbedingung.
3
Vgl. dazu Seite 13.
4
Technisch gesehen müßte hier für alle denkbaren weiteren Vermietungen an denselben Gast entsprechend Speicherplatz freigehalten bzw. bevorratet werden (vgl. AKAD-Heft Datenbanken).
ST/HfBR/DS-EDV K9039
6
Strukturierung der Daten (Normalisierung)
Die gezeigten Mengen ließen sich jedoch einfach wie folgt auflösen:
R.Vermietung
Name
Wohnort
Strasse
ZiNr
Art
Ausst.
Dauer
Miete
Müller
Hagen
Weg 1
100
EZ
DBF
1
60.00
Müller
Hagen
Weg 1
100
EZ
DBF
4
60.00
Abb. 4: Auflösung von Mengen
Im engeren Sinne enthält die obige Relation dennoch eine Menge: Die Ausstattungsmerkmale kennzeichnen ja eigentlich Mengen von Ausstattungsdetails wie Dusche, Bad ... u.s.w.. Genau genommen
müssten auch diese Werte zerlegt werden und für jedes Ausstattungsmerkmal ein neues Attribut in
die Relation aufgenommen werden. Erkennbar ist diese Eigenschaft auch an der entsprechend notwendigen Formulierung zum Auffinden eines Tupels mit dem Merkmal D wie Dusche: Hier ist ein Auffinden nur unter Formulierung einer SELECT...WHERE Ausstattung like '%D%' möglich, einer Selektion, die unterhalb der eigentlichen relationalen Abfrageebene liegt. Damit wären die Ausstattungswerte nicht mehr atomar und würden so den Grundsätzen der Normalformen nicht entsprechen.
In unserem Beispiel betrachten wir jedoch alle Einträge der Art DBFT, DFT,... als unteilbare Menge,
die im Bereich der Domäne ( = Wertebereich) von Ausstattung liegen.
1.4. Redundanzprobleme
Redundanzprobleme ergeben sich durch mehrfache Speicherung ein- und derselben Sachverhaltes
an unterschiedlichen Stellen (Spalten) der unnormalisierten Relation. Sofern ein Gast mehrmals die
Dienste unseres Hotels in Anspruch nimmt (s. Abb. 4), sind alle Gastattribute zu wiederholen. Bei
jeder erneuten Vermietung eines Zimmers sind alle Attribute des Zimmers zu wiederholen. In der
Abb. 4 wären also sowohl die Gastattribute Wohnort und Strasse als auch die Zimmerattribute Art,
Ausstattung und Miete redundant.
Redundanz zieht nicht nur einen erhöhten Speicherbedarf nach sich, sondern führt auch zu weiteren
Probleme im Zusammenhang mit den grundlegenden Speicheroperationen des Einfügens, Löschens
und Änderns. Bei vorliegender Datenstruktur können durch diese Operationen Anomalien entstehen,
die dazu führen, dass der reale Sachverhalt nicht mehr korrekt abgebildet wird. Schließlich treten bei
Redundanz auch oftmals unbestimmte (Null-) Werte auf.
1.5. Anomalieprobleme
1.5.1. Einfügeanomalien
Auch das Einfügen nicht realitätskonformer Sachverhalte führt zur Inkonsistenz der Datenbasis. So
stellt in vorstehender Relation der folgende Einschub keine Verletzung des Relationenprinzips dar:
R.Vermietung
Name
Wohnort
Strasse
ZiNr
Art
Ausst.
Dauer
Miete
Müller
Hagen
Weg 1
110
DZ
DBFR
10
75.00
Meier
Grundhof
Turmweg 7
110
EZ
DBFT
3
75.00
Abb. 5: Inkonsistenzen durch Einfügeanomalien
Die Einfügung des Tupels (Meier, Grundhof, Turmweg 7, 110, DZ, DBFT) führt zur Abbildung des
realitätsfremden Sachverhalts, dass das Zimmer 110 sowohl ein Einzel- als auch ein Doppelzimmer
sein kann.
ST/HfBR/DS-EDV K9039
Strukturierung der Daten (Normalisierung)
7
1.5.2. Änderungsanomalien
Ändern sich die Ausstattungsmerkmale eines Zimmers (z.B. Zimmer 100 hat nun die Ausstattung Dusche/Bad/Fernsehen/Telefon = DBFT), so wäre sicherzustellen, dass in allen Tupeln (Zeilen) der Relation die Änderungen vorgenommen werden. Werden die Preise für Einzelzimmer (EZ) bspw. generell um 10 % erhöht, so sind diese Änderungen an allen betroffenen Tupeln vorzunehmen. Andernfalls wird die Datenbasis inkonsistent, da sie Widersprüche beinhaltet.
Z.B. kostet das Zimmer 100 für Herrn Müller aus Hagen dann 60.00 €, für Herrn Müller aus Pinneberg
jedoch 66.00 €. Zieht bspw. ein Gast um (von Hagen nach Flensburg), so wäre auch hier eine mehrfache Änderung der Daten (alle Tupel von Müller aus Hagen) erforderlich. Ein Unterlassen der Änderungen in einigen Tupeln würde zur Inkonsistenz der Datenbasis führen.
R.Vermietung
Name
Wohnort
Strasse
ZiNr
Art
Ausst.
Dauer
Miete
Müller
Flensburg
Aue 14
100
EZ
DBF
1
75.00
Müller
Hagen
Weg 1
100
EZ
DBFT
4
60.00
Abb. 6: Inkonsistenzen durch Änderungsanomalien
1.5.3. Löschanomalien
Diese entstehen beim Entfernen von Informationen aus der Datenbasis. Storniert z.B. Herr Meier aus
Grundhof eine Zimmerreservierung für ein neues Zimmer, so sind mit der Stornierung gleichfalls alle
Zimmerattribute verloren, sofern sie nicht "zufällig" in einem anderen Tupel auftreten. Zugleich ist
auch die Adresse des Gastes (sofern er zum ersten Mal bei uns als Gast auftritt) nicht mehr bspw. für
Werbeaktionen verfügbar.
R.Vermietung
Name
Wohnort
Strasse
ZiNr
Art
Ausst.
Dauer
Miete
Meier
Grundhof
Hof 25
109
EZ
DBFR
5
75.00
Müller
Hagen
Weg 1
100
EZ
DBFT
4
60.00
Abb. 7: Inkonsistenzen durch Löschanomalien
Letztendlich dient die Vermeidung von Anomalien der Sicherstellung der Integrität der Datenbasis. So
muss z.B. sichergestellt sein, dass bei Löschungen z.B. aller Vermietverhältnisse mit dem Gast Müller nicht auch zugleich ein Zimmer vollständig gelöscht wird oder dass z.B. bei Löschung eines Gastes alle noch nicht abgerechneten Leistungen erhalten bleiben. Die Sicherung der (referentiellen)
Integrität wird von unterschiedlichen DBMS mehr oder weniger unterstützt.
So kann es sinnvoll sein, im Rahmen der Implementierung einer Anwendung zu definieren, was bei
der Löschung von Gästen mit noch offenen Rechnungen (z.B. definiert durch bestehende Fremd5
schlüsselbeziehung zur Tabelle Rechnung) passieren soll. Im wesentlichen lassen sich hier drei
Optionen anführen:
Abweisung des Löschauftrages
RESTRICT
Löschung aller Vermietverhältnisse
CASCADE
Löschung der Beziehung
NULLIFY
Abb. 8: Optionen des DELETE-Kommando
Zur Vermeidung von Redundanz und in deren Folge entstehenden Anomalieproblemen ist die bisherige Relation R.Vermietung weiter zu zerlegen. Prinzipiell werden dabei die durch die Zerlegung einer
5
Vgl. dazu Seite 13.
ST/HfBR/DS-EDV K9039
8
Strukturierung der Daten (Normalisierung)
Relation verlorengehenden Beziehungen durch verknüpfte Primärschlüssel (gleiche Spalten in unterschiedlichen Relationen) festgehalten (Sicherstellung der Integrität).
1.6. Problem der Nullwerte
Bei näherer Betrachtung der obigen Relation sind folgende Probleme ersichtlich: Soll in die obige
Relation R.Vermietung ein neues Zimmer eingefügt werden, das gerade fertiggestellt wurde, so ergibt
sich das Problem der Null-Werte. Null-Werte können dabei als unbekannte oder auch als nicht zutreffende Werte aufgefasst werden. Da evtl. noch kein Gast für das Zimmer vorhanden ist, müssten in
die Tabelle für alle Gastattribute NULL-Werte eingetragen werden. Desgleichen müssten z.B. bei
telefonischen Bestellungen, denen nicht sofort ein Zimmer zugeordnet werden kann (oder denen
mehrere mögliche Zimmer zugeordnet werden könnten) ebenfalls NULL-Werte für die Zimmerattribute Verwendung finden. Dabei entsteht das Problem der Selektion nach diesen Werten: Beim
Suchen, Ändern oder Löschen kann nur noch nach dem Kriterium "Ist Null" oder "Ist nicht Null" selektiert werden.
R.Vermietung
Name
Wohnort
Strasse
ZiNr
Art
Ausst.
Dauer
Miete
Müller
Hagen
Weg 1
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
110
EZ
DBFT
NULL
75.00
Abb. 9: Beispieldatensätze mit NULL-Werten
Im Rahmen des Normalisierungsprozesses, der zur Vermeidung von Anomalie- und Nullwertproblemen durchgeführt wird, werden in der Praxis drei Normalformen unterschieden, die nachfolgend
anhand unseres Beispiels erläutert werden.
1.7. Normalformen von Relationen
1.7.1. Arten der Abhängigkeit
Zum besseren Verständnis der Definitionen sei hier zunächst der Begriff der Abhängigkeit definiert.
Dabei geht es immer um die Abhängigkeiten zwischen solchen Attributen, die ein Entity eindeutig und
vollständig beschreiben (sog. Schlüsselkandidaten bzw. Schlüsselattribute) und solchen, die diese
Eigenschaft nicht aufweisen (sog. Nichtschlüsselattribute).
Funktionale Abhängigkeit: Funktional abhängig sind Attribute einer Relation, die sich nicht unabhängig voneinander ändern können. Funktionale Abhängigkeit impliziert also, dass zu jedem unabhängigen Attribut 1 genau ein Wert des abhängigen Attributes 2 gehört. Aus der Kenntnis des Wertes des
Attributes 1 kann also der Wert des Attributs 2 abgeleitet werden.
Sei S ein Schlüsselattribut der Relation R(S,A) und A ein weiteres (Nichtschlüssel-) Attribut der Relation: Dann gilt A als funktional abhängig von S, wenn genau zu jedem Wert von S exakt ein Wert von
A gehört:
R.Funktional
S (Kfz-Fahrgestellnummer)
A (Halter)
23479-WX-4576
Müller, Thomas
4456-RDT-3456-W
Müller, Sylvia
Abb. 10: Beispiel Funktionale Abhängigkeiten
Volle funktionale Abhängigkeit: Voll funktional abhängig sind Attribute einer Relation immer dann,
wenn mit einer bestimmten Wertekombination zweier oder mehrerer unabhängiger Attribute genau 1
Wert des abhängigen Attributes einhergeht. Volle funktionale Abhängigkeit impliziert also die Abhängigkeit eines Attributes von einer Attributskombination.
ST/HfBR/DS-EDV K9039
Strukturierung der Daten (Normalisierung)
9
Seien S1 und S2 ein zusammengesetzter Schlüssel der Relation R(S1, S2, A), dann gilt A als voll
funktional abhängig, wenn zu jeder zulässigen Wertekombination von S1 und S2 genau 1 Wert des
Attributes A gehört.
R.Voll Funktional
S1 (Model)
S2 (Typ)
A (Preis)
Mazda 626
Fließheck
27.856,00
Mazda 626
Kombi
30.800,00
Abb. 11: Beispiel Voll funktionale Abhängigkeiten
Transitive Abhängigkeit: Transitive Abhängigkeiten beschreiben indirekte Abhängigkeiten zwischen
Nichtschlüsselattributen.
Sei S1 ein Schlüssel der Relation R(S1, A, B), dann gilt B als transitiv abhängig, wenn A von S1, S1
aber nicht von A funktional abhängt, B jedoch von A funktional abhängig ist. Im Beispiel ist Halter und
Wohnort von der Fahrgestellnummer funktional abhängig (nicht aber umgekehrt, da eine Person
mehrere Kfz halten kann). Dagegen ist das Bundesland nicht von der Fahrgestellnummer, sondern
vom Wohnort funktional abhängig.
R.Transitiv
S (Kfz-Fahrgestellnummer)
Halter
B (Bundesland)
A (Wohnort)
23479-WX-4576
Müller, Thomas
Schleswig-Holstein
Grundhof
4456-RDT-3456-W
Müller, Sylvia
Hessen
Frankfurt
Abb. 12: Beispiel Transitive Abhängigkeiten
1.7.2. Erste Normalform (1NF)
An die erste Normalform sind die folgenden Bedingungen geknüpft:
Die Attribute dürfen nur einfache Werte aufweisen, d.h., sie müssen atomar (nicht weiter zerlegbar) sein. Am Kreuzungspunkt von Spalten und Zeilen darf höchstens ein skalarer Wert stehen.
Jede Relation erhält einen eindeutigen Schlüssel oder eine eindeutige Schlüsselkombination (Primärschlüssel), der das Tupel eindeutig identifiziert. D.h., es gibt keinen zweiten Tupel mit dem
selbem Primärschlüssel, der eine andere Attributskombination beschreibt.
Die Forderungen der 1NF können durch Aufspaltung der Relation R.Vermietung in 2 neue Relationen
erfüllt werden (Primärschlüssel sind doppelt unterstrichen):
R.Gast
Name
Wohnort
Strasse
R.Vermietung
Name
ZiNr
Art
Ausst.
Dauer
Miete
Abb. 13: Neue Relationen Gast und Vermietung zur Erreichung der 1 NF
Als Primärschlüssel wurde hier der Name des Gastes und - in der Relation R.Vermietung - zusätzlich
die Zimmernummer gewählt. Verlorengehende Beziehungen zu übergeordneten Relationen werden
durch Übernahme des/der Primärschlüssel/-kombination (hier Name) erhalten.
Dies führt jedoch in unserem Beispiel zu folgenden Problemen:
1.
Da ein Primärschlüsselattribut eine Relation eindeutig und vollständig beschreiben soll und er
damit ein eindeutiges Identifizierungsmerkmal darstellt, scheint der Name allein dafür nicht geeignet. Zwei Gäste mit dem Namen Müller wären dadurch nicht voneinander zu unterscheiden.
Denkbar wäre eine Kombination aus Name und Wohnort (zusammengesetzter Schlüssel); je-
ST/HfBR/DS-EDV K9039
10
Strukturierung der Daten (Normalisierung)
doch könnten auch hier offensichtlich Probleme entstehen (2 Gäste Müller aus Frankfurt sind
leicht vorstellbar). Dies könnte Veranlassung geben, auch den Straßennamen in den Schlüssel
aufzunehmen. Es entstünde dadurch jedoch die ursprünglich zerlegte Relation.
2.
Wesentlich vereinfachen lässt sich die Aufspaltung von Relationen jedoch durch die Einführung
eines "künstlichen" Schlüssels, der die Unterscheidung einzelner Entities durch ein Attribut ermöglicht und damit erheblich verkürzt und vereinfacht. Eine solche Möglichkeit bietet z.B. die
Einführung einer eindeutigen Kundennummer (KdNr). Diese wird nur je einmal für einen ganz
bestimmten Gast vergeben und unterscheidet Gäste voneinander. Erst dann ist es möglich,
Gäste mit gleichem Namen, Wohnort und Strasse (Zweifamilienhaus mit Vater und Sohn) zu unterscheiden.
3.
Die Verbindung zwischen den aufgeteilten Relationen lässt sich dann ebenfalls mit nur einer
Spalte in der zweiten Relation abbilden.
4.
Die Definition von Primärschlüsseln in der Datenbank setzt zugleich rein technisch gesehen voraus, dass diese Werte nicht wiederholt auftreten können (UNIQUE-Klausel). Bei der Wahl der
Schlüsselkombination Name, Wohnort, Strasse ist dies jedoch wie gezeigt nicht immer sicherzustellen.
Daraus ergibt sich eine verbesserte Aufteilung der Ursprungsrelation in die neuen Relationen
R.Gast
KdNr
Name
Wohnort
Strasse
ZiNr
Art
Ausst.
R.Vermietung
KdNr
Dauer
Miete
Abb. 14: Verbesserung der 1NF durch künstlichen Schlüssel Kundennummer (KdNr)
Die Redundanz- und Anomalieprobleme für Gastdaten wurden damit entfernt: Jede erneute Vermietung an einen Gast, der bereits einmal da war, würde nur zu einem neuen Datensatz in der Vermietung mit der entsprechenden (vorhandenen) Kundennummer führen. Die Gastattribute treten nun nur
noch einmalig in der Relation Gast auf. Die durch die Aufteilung verlorene Beziehung (Gast Müller
hat Einzelzimmer 100 mit der Ausstattung DBF zu einem Preis von € 60.00 gemietet) wird über die
Aufnahme des Primärschlüssels Kundennummer in die Relation Vermietung erhalten.
Betrachtet man die Relation Vermietung genauer, so bleibt festzustellen, dass auch hier ein Teil der
geschilderten Anomalie- und Redundanzprobleme fortbestehen. Wird ein Zimmer gelöscht, so lassen
sich die Vermietverhältnisse nicht rekonstruieren (Löschanomalie). Art und Ausstattung der Zimmer
können durch Änderungen bei gleicher Zimmernummer verschieden sein (Änderungsanomalie). Gibt
es für ein Zimmer keine offene Rechnung, d.h., keinen Eintrag in der Relation Vermietung mehr, sind
die Zimmerinformationen endgültig verloren (Löschanomalie). Auch tritt weiterhin das Problem der
NULL-Werte auf (Neues Zimmer ohne Vermietung).
Diese Überlegungen führen zur Bildung der 2. Normalform (2NF) einer Relation.
1.7.3. Zweite Normalform (2NF)
Allgemein lässt sich formulieren, dass sich eine Relation in der 2. Normalform (2NF) befindet,
wenn sie sich in der ersten Normalform befindet und zur Beschreibung der Abhängigkeit vom
Primärschlüssel für jedes Nicht-Primärschlüsselattribut alle Attribute des Primärschlüssels und
nicht nur Teile davon gehören (volle funktionale Abhängigkeit der Nichtschlüssel von den Schlüsselattributen). Daraus ergibt sich, dass die 2NF nur bei Vorliegen zusammengesetzter Primärschlüssel (wie im Falle der Relation Zimmer) relevant wird. Relationen mit nur einem Primärschlüsselattribut sind automatisch bei Vorliegen der 1NF auch und der Form der 2NF.
Die Relation Vermietung widerspricht dieser Forderung, da sich hier die Attribute Art, Ausstattung und
Miete bereits aus dem Primärschlüssel Zimmernummer ergeben; Die Kundennummer ist dafür nicht
erforderlich. Dies trifft jedoch nicht für die Mietdauer zu: Diese ist vom Gesamtschlüssel (Kundenund Zimmernummer) abhängig.
ST/HfBR/DS-EDV K9039
Strukturierung der Daten (Normalisierung)
11
Daher ist die Relation erneut aufzuteilen. Aus der Relation Vermietung ergeben sich nun die beiden
neuen Relationen
R.Zimmer
ZiNr
Art
Ausst.
Miete
Abb. 15: Relation Zimmer in 2NF
R.Vermietung
ZiNr
KdNr
Dauer
Abb. 16: Neue Relation Vermietung
Die Relation Vermietung birgt jedoch einen Nachteil in sich: Da die Attribute Kundennummer und
Zimmernummer als Primärschlüsselkombination jeweils nur einmal eine ganz bestimmte Attributkombination erlauben, wird es hier nicht möglich sein, die zweimalige Vermietung ein- und demselben Zimmers an ein- und denselben Gast historisch abzubilden. Darin zeigt sich eine gewisse
Schwäche des Relationenmodells schlechthin: sollen Historien abgebildet werden, müsste für diese
Relation ein neuer Schlüssel gefunden werden. Vorstellbar wäre hier die Aufnahme des Vermietdatums in den Primärschlüssel, da ja ein Zimmer nicht am gleichen Tag doppelt vermietet sein kann
oder auch die Vergabe einer Ereignisnummer wie Buchungsnummer.
Soll jedoch nicht eine Historie, sondern der jeweilige Zustand (im Sinne eines Platzbuchungssystems)
abgebildet werden, so ergeben sich hier keine Nachteile: Ein- und dasselbe Zimmer kann ja nicht zur
gleichen Zeit zweimal vermietet sein. Die Belegung des Zimmers wird erst wieder möglich, wenn es
frei gegeben wurde (durch Löschung der Vermietung).
In unserem Beispiel soll jedoch die Historie (zur Speicherung aller offenen Posten) abbildbar sein.
Ohne Einführung eines "künstlichen" Schlüssels und unter Verzicht auf einen Primärschlüssel
entstünden die folgenden Relationen, die jedoch eine Verletzung der Entitätsintegrität darstellen.
R.Vermietung
ZiNr
KdNr
Dauer
Abb. 17: Relation Vermietung ohne Primärschlüssel (Struktur)
Diese Vorgehensweise erlaubt nun auch die Abbildung gleicher Attributskombinationen:
R.Vermietung
KdNr
ZiNr
Dauer
100
100
1
100
100
4
100
100
1
Abb. 18: Relation Vermietung ohne Primärschlüssel (Daten)
Aus obiger Tabelle wird zugleich deutlich, dass diese Vorgehensweise erhebliche Nachteile mit sich
führt: Bei allen Datenbankoperationen auf diese Tabelle wäre Zeile 1 nicht mehr von Zeile 3 zu unterscheiden. D.h., Löschungen, Änderungen oder Selektionen würden immer beide Zeilen zugleich
betreffen, da hier kein eindeutiges Unterscheidungsmerkmal vorhanden ist.
Daher fügen wir auch in diese Relation einen künstlichen Schlüssel ein, dessen einzige Aufgabe es
ist, die Tupel eindeutig voneinander zu unterscheiden. Es wird hier die Einführung einer Buchungsnummer (BuNr) als Primärschlüssel vorgeschlagen. Damit ist zugleich einer grundsätzlichen Forderung des Datenbankentwurfs Rechnung getragen: Jede Relation verfügt über einen Primärschlüssel
(Bedingung der Entitätsintegrität).
ST/HfBR/DS-EDV K9039
12
Strukturierung der Daten (Normalisierung)
Aus den genannten Gründen ergibt sich folgende neue Relation Vermietung:
R.Vermietung
ZiNr
BuNr
KdNr
Dauer
Abb. 19: Primärschlüssel Buchungsnummer in der Relation Vermietung
1.7.4. Dritte Normalform (3NF)
Die Forderung der 3. Normalform (3NF) betrifft die direkte Abhängigkeit aller Nichtschlüssel- vom
Schlüsselattribut. Eine Relation befindet sich in der 3NF, wenn sie sich in der 2NF befindet und
alle Nichtschlüsselattribute direkt vom Primärschlüssel abhängen. Das heißt, dass alle Nichtschlüsselattribute wechselseitig voneinander unabhängig sind und damit auch keine indirekten Abhängigkeiten (Transitivität) der Nichtschlüsselattribute untereinander bestehen.
Die Relation Zimmer des Beispiels weist eine solche indirekte Abhängigkeit auf: Die Zimmermiete
wird in unserem Beispiel zunächst direkt von der Ausstattung bestimmt, nicht jedoch vom Primärschlüssel Zimmernummer. Daher ist hier die Miete in eine neue Relation Preis auszugliedern,
was zu folgenden neuen Relationen führen würde:
R.Preis
Ausst.
Miete
Abb. 20: Neue Relation Preis
R.Zimmer
ZiNr
Art
Ausst.
Abb. 21: Relation Zimmer in 3NF
Andererseits ist die Abhängigkeit des Preises nur von der Ausstattung in unserem Beispiel nicht realitätsnah: Zusätzlich müsste noch die Art des Zimmers in der Miete berücksichtigt werden (Doppelzimmer mit DBF teurer als Einzelzimmer mit DBF). Daher ist in die Preisrelation die Art als Schlüssel
mit aufzunehmen, was zu einem zusammengesetzten Schlüssel führt:
R.Preis
Art
Ausst.
Miete
Art
Ausst.
R.Zimmer
ZiNr
Abb. 22: Verbesserte Relationen Preis und Zimmer in 3NF
In diesem Beispiel ist zugleich ersichtlich, dass die Redundanz der Primärschlüssel sehr stark ansteigt. Damit steigt z.B. zugleich der Aufwand bei einer entsprechenden Abfrage der Tabellen (z.B.
SELECT ZiNr, Zimmer.Ausstattung, Zimmer.Art, Miete FROM Zimmer, Preis WHERE Zimmer.Ausstattung = Preis.Ausstattung AND Zimmer.Art = Preis.Art)6.
Bei weiterer Prüfung der Relationen zeigt sich, dass nun durch Einfügen, Ändern und Löschen keine
Anomalien mehr erzeugt werden können. Daher wurde die 3. Normalform erreicht.
Ein letztes Problem besteht allerdings noch bezüglich solcher Attributwerte, die sich zum Zwecke der
Verbindung der Relationen untereinander in einer weiteren Relationen befinden. Für alle diese Werte
muss gelten: Es muss ein entsprechender Wert in der anderen Relation existieren. Folgende zwei
Beispiele mögen den Zusammenhang erhellen:
6
Vgl. dazu Kapitel Anfragen an die Datenbasis (Retrieval) auf Seite 24.
ST/HfBR/DS-EDV K9039
Strukturierung der Daten (Normalisierung)
13
1. Niemals darf in der Relation Vermietung eine Kundennummer existieren, für die sich in der Relation Gast nicht ein entsprechender Wert finden lässt. Der Adressat einer Rechnung ließe sich nicht
ermitteln.
2. Niemals darf sich in der Relation Vermietung ein Zimmer befinden, das nicht in der Relation Zimmer existiert. Art und Ausstattung würden damit nicht feststellbar sein, womit auch eine Preisermittlung unmöglich wäre.
Man nennt diese Bedingungen auch die Bedingungen zur Sicherung der referentiellen Integrität. Ein
mögliches Instrument zur Sicherung der referentiellen Integrität, das von den meisten Datenbanksystemen unterstützt wird, ist die Möglichkeit zur Formulierung von Fremdschlüsselbeziehungen7.
1.8. Integritätsbedingungen
1.8.1. Referentielle Integrität durch Fremdschlüsselbeziehungen
Fremdschlüsselbeziehungen erleichtern die Erhaltung der Integrität, insbesondere der referentiellen
Integrität, einer Datenbank.
Ein Fremdschlüssel ist ein Attribut, dass in einer anderen Relation ein Primärschlüsselattribut ist.
Zwischen beiden baut das DBMS eine besondere Beziehung auf, die sicherstellt, dass zu jeder Zeit
für alle Fremdschlüsselattribute ein entsprechendes Primärschlüsselattribut gefunden wird. Dies ist
bei allen Verbunden8 von Tabellen relevant.
Beispiel: Gegeben sei folgendes, leicht verkürzte Relationenschema:
R.Vermietung (BuNr, KdNr, ZiNr, Dauer)
R.Gast (KdNr, Name, Wohnort, Strasse);
R.Zimmer (ZiNr, Ausstattung, Ausstattung);
Ohne Formulierung von Fremdschlüsselbeziehungen wären folgende Einschübe in die Relationen
möglich, die eine Rekonstruktion der Gesamtinformation nicht mehr ermöglichen würden:
•
Einfügen/Ändern einer KdNr in Vermietung, für die in R.Gast kein Datensatz/kein Entity existiert.
•
Einfügen/Ändern einer ZiNr in Vermietung, für die in R.Zimmer kein Datensatz existieren.
•
Löschen eines Zimmers/Gastes, für das in Relation Vermietung eine (vielleicht noch nicht abgerechnete) Vermietung verzeichnet ist; Es wäre bei Rechnungsstellung kein Zimmer und damit
auch kein Preis bestimmbar bzw. kein Rechnungsadressat mehr vorhanden.
Im obigen Beispiel wäre es also sinnvoll, zwischen der Relation Gast bzw. der Relation Zimmer und
der Relation Vermietung eine Fremdschlüsselbeziehung aufzubauen. Die Fremdschlüsselattribute
sind die, die in beiden Relationen enthalten sind und in der referenzierten Relation Primärschlüsseleigenschaft haben. In den referenzierenden Relationen sind diese Attribute die Fremdschlüsselattribute:
Relation
Primärschlüssel
Fremdschlüssel
referenzierte Relationen
Gast
KdNr
-
-
Zimmer
Vermietung
Preis
ZiNr
BuNr
Art, Ausstattung
Art, Ausstattung
KdNr, ZiNr
-
Preis
Gast, Zimmer
-
Abb. 23: Primär- und Fremdschlüssel des Relationenschemas Hotel
Die Formulierung von Fremdschlüsselbeziehungen sorgt dafür, dass zu einer Vermietung immer und
zu jeder Zeit ein Gast in der Relation Gast und ein Zimmer in der Relation Zimmer gefunden wird
(referentielle Integritätsbedingung), da sie zur Folge hat, dass Änderungen/Einfügungen/Löschungen,
die die Integrität zerstören würden, i.d.R. zurückgewiesen würden oder zumindest die korrekte Rei-
7
Eine Alternative dazu stellt die Verwendung von Datenbank-Triggern da, die die Einhaltung der
beschriebenen Regeln überwachen.
8
Ein Verbund selektiert Daten aus mehreren Tabellen und wird auch als Join bezeichnet.
ST/HfBR/DS-EDV K9039
14
Strukturierung der Daten (Normalisierung)
henfolge der Operationen einzuhalten wäre. Gleiches gilt für die Beziehungen zwischen Zimmer und
Preis.
Folgende Tabelle zeigt, welche Operationen auf mit Fremdschlüsselbeziehungen versehene Tabellen
erlaubt bzw. abgewiesen werden. Dabei ist beispielsweise Vermietung die referenzierende, Gast bzw.
Zimmer die referenzierte Relation, d.h., Vermietung.KdNr referenziert Gast.KdNr und Vermietung.ZiNr referenziert Zimmer.ZiNr. Gleiches gilt für Zimmer (referenzierende) und Preis (referenzierte Relation). Dabei wird die Option RESTRICT unterstellt.
Operation Granularität9
Vermietung
Gast
Zimmer
Preis
INSERT
Tupel
erlaubt, wenn in erlaubt, z.B.
neuer Gast
Gast ein Entity
mit KdNr und in
Zimmer ein
Entity mit ZiNr
vorhanden ist,
sonst wird abgewiesen
erlaubt, wenn
die beschriebene
Art/ Ausstattung
in der Relation
Preis enthalten
ist, sonst wird
abgewiesen
erlaubt, z.B.
neue Art/Ausstattungskombination mit
einem Preis
DELETE
Tupel
erlaubt
erlaubt, wenn
in Vermietung
kein Gast mit
dieser KdNr
mehr existiert,
sonst wird abgewiesen
erlaubt, wenn in
Vermietung kein
Zimmer mit dieser ZiNr mehr
existiert, sonst
wird abgewiesen
erlaubt, wenn in
Zimmer kein Tupel mehr mit der
entsprechenden
Art/Ausstattung
existiert, sonst
wird abgewiesen
UPDATE
Nichtschlüsselattribut
erlaubt, z.B.
Dauer
erlaubt, z.B.
Wohnort
nicht vorhanden
erlaubt, z.B.
Miete
Primärschlüsselattribut
wird abgewiesen
wird abgewiesen
wird abgewiesen
wird abgewiesen
Fremdschlüsselattribut
erlaubt, wenn in nicht vorhanden
Gast ein Entity
mit KdNr und in
Zimmer ein
Entity mit ZiNr
vorhanden ist,
sonst wird abgewiesen
erlaubt, wenn
die geänderte
Art/ Ausstattung
in der Relation
Preis enthalten
ist, sonst wird
abgewiesen
nicht vorhanden
Abb. 24: Operationentableau bei Fremdschlüsselbeziehungen
Daraus, dass eine Operation abgewiesen wird, ist jedoch nicht endgültig zu schließen, dass diese
Operation überhaupt nicht möglich wäre: Entscheidend ist - wie beim Einfügen auch (s. Kapitel 3:
Erfassung der Daten) bereits verdeutlicht - die Reihenfolge der Operationen und die bei der Einrichtung der Relationen vereinbarten Optionen von Update/Delete, die RESTRICT, CASCADE oder
NULLIFY sein können.
INSERT in eine referenzierende Relation (z.B. Vermietung) erfordert erst das Einfügen der/des entsprechenden Tupels in der/die referenzierten Tabelle/n (Gast und Zimmer); m.a.W. ist zuerst ein
Gast mit der entsprechenden KdNr und (es handelt sich ja hier um eine zusammengesetzte Fremdschlüsselbeziehungen) ein Zimmer mit der entsprechenden ZiNr einzufügen, dann kann in Vermietung das Tupel mit der entsprechenden KdNr und ZiNr (für die dann ja Werte in Gast.KdNr und Zimmer.ZiNr gefunden werden) eingefügt werden.
9
Granularität = kleinste, mit einer Datenbankoperation erreichbare Attributmenge. Man unterscheidet dabei einmal die Zeile (z.B. Löschen mindestens einer Zeile, nicht aber eines Attributes) und
den Attributwert (z.B. Ändern nur eines Attributwertes, die übrigen Attributwerte bleiben unverändert).
ST/HfBR/DS-EDV K9039
Strukturierung der Daten (Normalisierung)
15
DELETE in einer referenzierten Relation (Gast und Zimmer) erfordert erst das Löschen der/des
entsprechenden Tupels in der/den referenzierenden Relation/en (Vermietung); m.a.W. ist bspw.
zuerst die Vermietung mit der entsprechenden KdNr/ZiNr-Kombination zu löschen; erst dann kann der
Gast mit der entsprechenden KdNr und (es handelt sich ja hier um eine zusammengesetzte
Fremdschlüsselbeziehungen) das Zimmer mit der entsprechenden ZiNr gelöscht werden. Andernfalls
würde ja auch bspw. eine Rechnung für eine Vermietung keine Gastadresse mehr finden.
UPDATE erfordert dagegen eine differenzierte Betrachtung: Zum einen könnte man die Änderung
des Wertes eines Primärschlüsselattributes, zum anderen auch die eines Nichtschlüsselattributs und
drittens auch die eines Fremdschlüsselattributs vornehmen wollen.
Die Änderung des Wertes eines Nichtschlüsselattributes in referenzierender und referenzierter
Relation (z.B. Vermietung.Dauer und Zimmer.Art oder Gast.Name) stellt uns vor keine Probleme, da
die Fremdschlüsselbeziehungen dadurch nicht berührt werden.
Die Änderung eines Primärschlüsselattributwertes wird in der/den referenzierten Relation/en
(Gast/Zimmer) auf jeden Fall abgewiesen, da sich - wie beim Ändern - ein Informationsverlust einstellen würde (nicht für alle Tupel in der referenzierenden Relation (Vermietungen) würde dann ein
Gast/Zimmer in der/den referenzierten Relation/en (Gast/Zimmer) gefunden. Gleiches gilt für die
Primärschlüsselattribute der referenzierenden Relation/en (Vermietung). In diesem Falle wäre nur ein
Löschen und ein anschließendes Einfügen (in der jeweils oben beschriebenen Reihenfolge) möglich.
Für den Wert eines Fremdschlüsselattributs in der (ja nur dort vorkommenden) referenzierenden
Relation (Vermietung) ist in den oben genannten Grenzen (KdNr und ZiNr müssen in Gast/Zimmer
vorhanden sein) eine Änderung möglich: eine in Gast vorhandener KdNr kann ja jederzeit ein in
Zimmer vorhandene ZiNr buchen; da jedoch hier die Fremd- zugleich auch die Primärschlüsselkombination der Relation Vermietung ist, scheitert dies an der Nichtänderbarkeit der Primärschlüssel
(s.o.)! Zu lösen wäre dieses Problem, wenn in Vermietung eine fortlaufende Buchungsnummer (BuNr)
als Primärschlüsselattribut eingefügt würde (wie dies ja bereits geschehen ist) und die Primärschlüsseleigenschaft der Attribute KdNr sowie ZiNr in Vermietung aufgehoben würden. Dann wäre hier eine
Änderung von Vermietung.KdNr und Vermietung.ZiNr möglich.
1.8.2. Weitere Integritätsbedingungen
Im Rahmen der Entwicklung des obigen Datenbankschemas wurde bisher auf die Bedeutung der
referentiellen Integrität sowie die der Entitätsintegrität hingewiesen. Neben diesen ist darüber hinaus
auch die benutzerdefinierte Integrität von Bedeutung.
Im Rahmen der Sicherstellung der benutzerdefinierten Integrität ist es oft wünschenswert, die Einhaltung von Werten einer Domäne (Wertebereich) sicherzustellen, um "unsinnige" Eingaben zu verhindern. Eine solche Sicherung könnte auf unterschiedliche Art und Weise bewirkt werden: Zum
einen sind zu verschiedenen Datenbanken Werkzeugprogramme (TOOLS) erhältlich, die über solche
Sicherungsmechanismen verfügen (z.B. SQL-Forms für das DB-System ORACLE). Weiterhin könnten über die Verwendung von Views (s. unten) unter Einsatz der CHECK OPTION Wertebereiche
eingegrenzt werden (sog. Constraints). Sofern solche Mechanismen jedoch in dem verwendeten
Datenbanksystem nicht implementiert sind, hilft oft der "Umweg" über die Nutzung der Instrumente
zur Sicherstellung der referentiellen Integrität. Ein Beispiel mag den Zusammenhang erhellen:
Nehmen wir an, unsere Relation Gast sollte für die Zwecke der Serienbrieferstellung ein zusätzliches
Attribut Anrede erhalten: m für männlich (Anrede: Herrn), w für weiblich (Anrede: Frau) und f für Firma (Anrede: keine spezielle). Bei der Erfassung neuer Gäste kann es nun vorkommen, dass dem
Erfasser der Daten die Kürzel nicht präsent sind. Er könnte bspw. für männlich H (Anrede: Herrn) und
für weiblich F (Anrede: Frau) eingeben. Bei entsprechender Selektion durch ein Textverarbeitungssystem, das Serienbriefanschriften aus unserer Datenbank entnimmt, könnten die Tupel, die mit H oder
F gekennzeichnet sind, nicht korrekt verarbeitet werden, da die Tupel ja nach Anrede=m oder Anrede=w oder Anrede=f selektiert würden.
Zur Lösung eines solchen (oder ähnlichen Problems) könnte man eine zusätzliche Relation Floskel in
die Datenbank aufnehmen. Diese weist einen Grad von 1 auf (d.h., sie hat genau 1 Attribut, das wir
Bezeichnung nennen wollen). Wir nehmen dazu genau 3 Tupel (Kardinalität der Relation ist dann 3)
in die Relation auf: Nämlich die Werte "m" , "w" und "f" (für Herrn/ Frau/Firma). Was haben wir nun
erreicht? Wenn wir zwischen dem Attribut Gast.Anrede und dem Attribut Floskel.Bezeichnung eine
Fremdschlüsselbeziehung aufbauen (Gast.Anrede referenziert Floskel.Bezeichnung), so wird die
Aufnahme einer Anrede bei der Erfassung des neuen Gastes, deren Wert nicht in Flos-
ST/HfBR/DS-EDV K9039
16
Strukturierung der Daten (Normalisierung)
kel.Bezeichnung enthalten ist, abgewiesen. Es kann somit nicht zu einer falschen Erfassung von
Werten für die Anrede kommen.
Das geschilderte Problem erscheint relativ trivial. Ein ähnliches Problem jedoch könnte die Erfassung
von Rechtsformen gespeicherter Unternehmen sein. Wer will dann aber noch zwischen den (identischen) Rechtsformen "GmbH + .CO" bzw. "GmbH & CO" oder auch "GmbH & CO.KG" bei einer
Selektion nach Rechtsformen unterscheiden?
Insgesamt ergeben sich folgende Relationen, die nun unmittelbar in die Datenbanksprache SQL umgesetzt werden können. Die Relationennamen entsprechen den Tabellennamen, die Attributbezeichnungen den der Spaltennamen der Datenbank. Primärschlüssel sind doppelt, Fremdschlüsselbeziehungen einfach unterstrichen:
R.Gast
(KdNr, Name, Wohnort, Strasse)
R.Zimmer
(ZiNr, Ausstattung, Art )
R.Preis
(Ausstattung, Art, Miete)
R.Vermietung
(BuNr, KdNr, ZiNr, Dauer)
Abb. 25: Relationenschema zur Datenbank Hotel mit Primär- und Fremdschlüsseln
Ungelöst ist allerdings noch das Problem der Postleitzahlen. Soll die Adresse um eine Postleitzahl
ergänzt werden, müsste genau genommen zusätzlich die Relation PLZ
R.PLZ
(Ort, PLZ)
Abb. 26: Ergänzungen zum Relationenschema Hotel
als eigenständige Relation errichtet werden. Andernfalls würde - bei Aufnahme der PLZ in die Relation Gast - die Forderung der 3NF (Nicht-Transitivität, d.h., keine wechselseitige Abhängigkeit der
Nichtschlüsselattribute) nicht erfüllt: Die PLZ ist funktional abhängig vom Ort, nicht aber vom Primärschlüssel der Relation, der Kundennummer des Gastes. Sichtbar würde dies nicht nur am Datenbankschema, sondern auch an einem möglichen Inhalt der Datenbasis. Hier wären dann nämlich folgende
Entities vorstellbar:
R.Gast_Mit_PLZ
KdNr
PLZ
Wohnort
...
1
24977
Grundhof
...
2
24997
Grundhof
...
...
...
...
...
Abb. 27: Relation Gast mit Verstoß gegen die 3NF
Es ist unmittelbar einsichtig, dass bei Änderungen des Wohnortes des Gastes mit der KdNr 1 (z.B. in
den Ort Hagen) und "vergessener" Änderung der PLZ (in 58093) Mutationsanomalien entstehen. Dies
kann durch Auslagerung in die Relation PLZ einfach verhindert werden, da sich dort dann die korrekte
Postleitzahl für Hagen finden würde. Dadurch würde zugleich das Auftreten einer transitiven Abhängigkeit zwischen PLZ und Wohnort (wechselseitige Abhängigkeit, keine direkte Abhängigkeit vom Primärschlüssel) verhindert.
Die Beziehung zwischen Postleitzahl und Ort ist in der Bundesrepublik wie folgt zu beschreiben:
Jeder Ort hat genau eine Postleitzahl; eine Postleitzahl kann jedoch zu mehreren Orten gehören.
Daher wurde der Ort(sname) als Primärschlüssel für die Relation PLZ verwendet. Die wenigen Ausnahmen, dass ein Ort auch mehrere Postleitzahlen haben kann, vernachlässigen wir hier. Dieser
Umstand könnte aber durch Bildung der Primärschlüsselkombination PLZ, ORT berücksichtigt werden.
Nach der Einführung des neuen Postleitzahlsystems ändert sich die Situation allerdings: Größere
Orte können nun über mehrere Postleitzahlen verfügen. Außerdem gibt es die an ein Postfach gekoppelte Postleitzahl und zusätzlich die Postleitzahl von Großkunden. Dies würde letztlich bedeuten,
dass das Gesamtverzeichnis der Postleitzahlen jeweils als eigenständige Relation vorzuhalten wäre.
ST/HfBR/DS-EDV K9039
Strukturierung der Daten (Normalisierung)
17
Es müsste eine Fremdschlüsselbeziehung zwischen Ort und Straße bzw. Postfach hergestellt werden.
Dies bedeutet jedoch, dass auch bei einem geringem Adressenbestand eine große Datenmengen (im
Prinzip das gesamte Verzeichnis der Postleitzahlen in Deutschland) in einer Relation vorzuhalten
wären. Daher erscheint es in diesen Fällen sinnvoll, die Postleitzahl als Attribut zur (Gast-) Adressen
aufzunehmen und die Redundanz in Kauf zu nehmen.
ST/HfBR/DS-EDV K9039
18
Einrichtung der Datenbank (DDL)
2.
Einrichtung der Datenbank (DDL)
Vor der konkreten Einrichtung der Relationen in der Datenbank muss die Wahl geeigneter Datentypen erfolgen. Die Wahl der Datentypen für die Attribute hängt in hohem Maße von dem verwendeten DBMS ab. Die hier gezeigten Typen werden u.a. vom Datenbanksystem INTERBASE10
unterstützt. Vor der Errichtung der Tabellen lassen sich entsprechende Domänen im DBMS einrichten, die eine exakte Abgrenzung von möglichen Wertemengen erlauben.
Domäne
Datentyp
Check
DomKdNr
Integer
Values Between 100 and 999
DomZiNr
Integer
Values Between 100 and 300
DomArt
VarChar(2)
Values in (‘EZ’, ‘DZ’)
Abb. 28: Domänen der Datenbank HOTEL
Spalte
Datentyp
Inhalt
Primärschlüssel
Fremdschlüssel
KdNr
DomKdNr
Kundennummer
Gast
Vermietung
Name
VarChar(50)
Name des Gastes
-
-
Wohnort
VarChar(50)
Wohnort des Gastes
-
PLZ
Strasse
VarChar(50)
Strasse des Gastes
ZiNr
DomZiNr
Zimmernummer
Zimmer
Vermietung
Art
DomArt
Einzel-/Doppelzimmer
Preis
Zimmer
Ausstattung
Varchar(10)
Ausstattungskürzel
Preis
Zimmer
Miete
Double Precision
€-Betrag je Nacht und Person -
-
BuNr
Integer
Buchungsnummer
Vermietung
-
Dauer
Integer
Aufenthalt in Tagen
-
-
-
Abb. 29: Datentypen der Attributswerte der Relationen
Die Einrichtung der Datenbank erfolgt mit den DDL11 Statements von SQL; diese lassen sich wie folgt
strukturieren:
(a) Bildung physikalischer Strukturen
CREATE|OPEN|CLOSE|DROP
DATABASE
CREATE|ALTER|DROP
TABLESPACE|INDEXSPACE
CREATE
BUFFERSPACE
(b) Bildung logischer Strukturen
CREATE|ALTER|DROP
TABLE|VIEW
CREATE|DROP
INDEX|SCHEMA
10
Das Datenbanksystem Interbase wird als lokale Datenbank von Inprise (Borland) mit der Entwicklungsumgebung DELPHI ausgeliefert und ist mittlerweile auch unter Open Source verfügbar. Für
die Beispiele wurde die Version: WI-V5.1.1.680 verwendet.
11
DDL = Data Definition Language (CREATE, DROP, ...)
DCL = Data Control Language (GRANT, REVOKE: Verwaltung von Rechten der Benutzer).
ST/HfBR/DS-EDV K9039
Einrichtung der Datenbank (DDL)
19
(c) Datenbankerweiterung/Rekonstruktion
CREATE|DROP
SYNONYM
Abb. 30: DDL Befehle von SQL
Die Datenbank wird nun mit der folgenden Anweisung eingerichtet:
CREATE DATABASE HOTEL USER „xyz“ PASSWORD „mypassword“
Damit wird die Datenbank auch zugleich eröffnet. Nach Verlassen mit EXIT und neuer Nutzung kann
diese dann mit CONNECT DATABASE Hotel adressiert werden. Anschließend wird ist der physikalische Speicherplatz zu reservieren:
CREATE TABLESPACE Dateiname;
CREATE INDEXSPACE Dateiname;
Die Einrichtung und Strukturierung der Tabellen erfolgt mit der CREATE TABLE-Anweisung. Das
DDL Statement CREATE TABLE ist ein extrem komplexer Befehl, der je nach SQL-Implementierung
über eine Vielzahl von Optionen z.B. zur Sicherung der referentiellen Integrität der Datenbasis verfügt.
CREATE TABLE [creator].Tabellenname
(Spaltenname Spaltentyp,
[NOT NULL [UNIQUE]
[DEFAULT literal | NULL | USER]
[PRIMARY KEY]
[REFERENCES [creator.]Tabellenname
[ON UPDATE RESTRICT
[ON DELETE | RESTRICT | CASCADE | SET NULL]]
{,repeat}
[,UNIQUE (Liste von Spaltennamen)]
[,PRIMARY KEY(Liste von Spaltennamen)]
[,FOREIGN KEY (Liste von Spaltennamen)]
REFERENCES [creator.]Tabellenname
[ON UPDATE RESTRICT
[ON DELETE | RESTRICT | CASCADE | SET NULL]]
{,repeat}
[IN | TABLESPACE | INDEXSPACE Dateiname]
[CHECK (Spaltenname Restriktion)
{,repeat}
Abb. 31: DDL Befehl CREATE TABLE von SQL
Dabei gelten die folgenden Konventionen:
[]
Optionale Angaben
{}
wiederholbare Angaben
|
alternative Angaben/Optionen
Die Bedeutung der wichtigsten Schlüsselwörter dabei sind:
NOT NULL
keine Null-Werte erlaubt, kein Leerfeld
UNIQUE
identische Werte in unterschiedlichen Tupeln nicht erlaubt
PRIMARY KEY
definiert das/die Schlüsselfelder
FOREIGN KEY/
REFERENCES
Festlegung einer Fremdschlüsselbeziehung durch Angabe der referenzierten Tabelle; Spaltenname muss identisch sein.
ON DELETE/ON UPDATE
Statements zur Sicherung der referentiellen Integrität der Datenbank
Abb. 32: Schlüsselwörter des CREATE TABLE Befehls von SQL
ST/HfBR/DS-EDV K9039
20
Einrichtung der Datenbank (DDL)
Die entworfenen Relationen können damit wie folgt eingerichtet werden:
CREATE TABLE Gast (KdNr
Name
Wohnort
Strasse
DomKdNr NOT NULL PRIMARY KEY,
Varchar(50) NOT NULL,
Varchar(50),
Varchar(50));
Abb. 33: Einrichtung der Relation Gast
Zusätzlich könnte die Spalte Gast.Wohnort auch die Spalte PLZ.Ort als Fremdschlüssel referenzieren. Dies hätte den Vorteil, dass ein neuer Gast nur dann eingefügt werden könnte, wenn sich in der
Tabelle PLZ auch der entsprechende Ortsname finden würde. Das Weglassen dieser Option führt
dann dazu, dass bei einer Anfrage nach der vollständigen Adresse eines Gastes (inkl. Postleitzahl)
ein solcher Gast nicht selektiert würde. Vermeiden ließe sich dies durch die Formulierung der Anfrage
als Outerjoins, einer speziellen Form des Joins (zum Join siehe unten). Andererseits lässt sich durch
das hier gewählte Relationenschema auch ein Gast ohne PLZ erfassen.
Dann kann die Relation Preis erfasst werden, da sie ebenfalls keine Fremdschlüsselbeziehung aufweist.
CREATE TABLE Preis (Ausstattung Varchar(10) NOT NULL,
Art
DomArt
NOT NULL,
Miete
Double Precision NOT NULL,
PRIMARY KEY (Ausstattung, Art));
Abb. 34: Einrichtung der Relation Preis
Nun kann die Relation Zimmer formuliert werden, da sie die Relation Preis referenziert und diese bereits implementiert ist.
CREATE TABLE Zimmer(ZiNr
Dom ZiNr
NOT NULL PRIMARY KEY,
Ausstattung Varchar(10) NOT NULL,
Art
DomArt
NOT NULL,
FOREIGN KEY (Ausstattung, Art)
REFERENCES Preis);
Abb. 35: Einrichtung der Relation Zimmer
Die Angabe REFERENCES Preis baut eine Fremdschlüsselbeziehung zu dem gleichnamigen Attribut
Ausstattung der Relation Preis auf. Dabei könnten entsprechende Optionen für das Löschen (z.B.
Löschen einer Ausstattungs-/Preisrelation führt zur Ablehnung [Option ON DELETE | RESTRICT), so
dass die referentielle Integrität sichergestellt bleibt) formuliert werden. Das Weglassen einer solchen
Option impliziert hier jedoch automatisch die RESTRICT Option.
Es fehlen noch die Tabellen Vermietung und PLZ für die Datenbank HOTEL:
CREATE TABLE Vermietung(BuNr Integer
NOT NULL PRIMARY KEY,
KdNr DomKdNr
NOT NULL
REFERENCES Gast,
ZiNr DomZiNr
NOT NULL
REFERENCES Zimmer,
Dauer Integer
NOT NULL);
Abb. 36: Einrichtung der Relation Vermietung
CREATE TABLE PLZ
(PLZ
Ort
Numeric(4),
VarChar(50) NOT NULL PRIMARY KEY);
Abb. 37: Einrichtung der Relation Postleitzahl
ST/HfBR/DS-EDV K9039
Einrichtung der Datenbank (DDL)
21
Für jeden Schlüssel einer Relation kann nun Indexfile angelegt werden. Vorteil: Schnellerer Zugriff
auf die Schlüssel der Relationen über einen Index (z.B. bei einem Equi-Join).
CREATE [UNIQUE] INDEX
[creator.] Indexname
ON
Tabellenname [ASC|DESC] {repeat,][Cluster]}
[INDEXSPACE
Indexspace-Name];
Abb. 38: DDL Befehl CREATE INDEX von SQL
ST/HfBR/DS-EDV K9039
22
Erfassung von Daten (DML)
3.
Erfassung von Daten (DML)
Die Erfassung der Daten erfolgt über die DML-Statements12 von SQL (Data Manipulation Language).
Diese sind:
INSERT
Einfügen von Daten in eine Tabelle/ein View
UPDATE
Ändern von Daten einer Tabelle/eines Views
DELETE
Löschen von Daten aus einer Tabelle/einem View
Abb. 39: DML Befehle von SQL
Als Quelle zur Neuerfassung von Daten kommen die Tastatur, aber auch der Import von Daten in Betracht. Zusätzlich ist es jedoch auch möglich, in Tabellen Werte aus anderen Tabellen einzufügen.
Zum Einfügen von Daten in die Tabellen steht das DML Sprachelement INSERT zur Verfügung. Die
Übernahme von Daten aus anderen Dateien (Import) setzt die entsprechende Formatierung der Eingangsdaten voraus. Diese Formatierung hängt von der verwendeten SQL-Implementierung ab.
Prinzipiell hängt die Syntax des INSERT-Kommando von der Quelle der Daten ab:
Tastatur
INSERT INTO Tabellenname VALUES (Wert1[,Wert2..,WertN]);
Datei
INSERT INTO Tabellenname FILE Dateiname;
andere DB-Tabelle
INSERT INTO Zieltabelle
SELECT Spalten
FROM Quelltabelle
WHERE Bedingung
ORDER BY | GROUP BY | ...
Abb. 40: Optionen des INSERT-Kommandos
Bei der Erfassung der Daten spielt - zumindest in der hier verwendeten SQL-Implementierung - bereits der Datenbankentwurf eine wesentliche Rolle: Die Daten können nicht in beliebiger Folge in die
Tabellen eingefügt werden (s. 1.9.1). Die referentielle Integrität wird durch das System INTERBASE
auf Grund der Angaben der Fremdschlüsselbeziehung in den DDL Statements automatisch überwacht. In anderen DBMS wie bspw. ORACLE werden die Integritätsbedingungen erst nach Abschluss
einer Transaktion (vollständige Eingabe der Daten) geprüft.
Hier können jedoch aufgrund der Verweise in der Relation Vermietung auf andere Relationen Einträge in die Relation Vermietung erst erfolgen, wenn die entsprechenden referenzierten Spalten der
Tabellen Gast und Zimmer die einzufügenden Werte aufweisen. Beispiel: Einfügen der Vermietung
(1,102,3) erfordert einen vorherigen entsprechenden Eintrag in den Tabellen Gast (1,'...') und Zimmer
(102,...)!
Ebenso ist es nicht möglich, ein neues Zimmer einzufügen, wenn in der Preisrelation nicht die entsprechende Ausstattungs-/Artkombination mit seinem Preis beschrieben ist. Ist in der Relation Preis
beispielsweise derzeit nur der Tupel ('DBT','EZ',55) enthalten, so können in Zimmer die Werte
(100,'DBT','EZ'), nicht aber Werte wie (100,'DBT','DZ') oder (100, 'DBFT','EZ') eingefügt werden (Foreign Key, No matching value in referenced table).
Die Daten könnten bspw. wie folgt in die Tabellen eingefügt werden:
INSERT INTO Plz
VALUES ('Husby',24989);
INSERT INTO Plz
VALUES ('Grundhof',24977);
...
INSERT INTO Gast
VALUES (100,'Müller','Grundhof','Aukjer Strasse 17');
INSERT INTO Gast
VALUES (102,'Paessens','Husby','Am Polldamm 1A');
...
INSERT INTO Zimmer VALUES (100,'DBF','EZ');
12
DML = Data Manipulation Language (SELECT, INSERT, DELETE, UPDATE)
ST/HfBR/DS-EDV K9039
Erfassung von Daten (DML)
INSERT
...
INSERT
INSERT
...
INSERT
INSERT
...
23
INTO Zimmer
VALUES (101,'DBT','EZ');
INTO Preis
INTO Preis
VALUES ('DBT','EZ',52.00);
VALUES ('DBF','EZ',60.00);
INTO Vermietung VALUES (1,1,102,12);
INTO Vermietung VALUES (2,1,101,10);
Gleiches gilt für alle späteren Änderungen in der Datenbasis: Auch hier werden Änderungen in Tabellen, die Fremdschlüsselbeziehungen aufweisen, zurückgewiesen, wenn sich in der referenzierten
Tabelle kein entsprechender Primärschlüsselwert befindet13.
13
Vgl. dazu Referentielle Integrität durch Fremdschlüsselbeziehungen auf der Seite 13.
ST/HfBR/DS-EDV K9039
24
Anfragen an die Datenbasis (Retrieval)
4.
Anfragen an die Datenbasis (Retrieval)
4.1. DML Statement SELECT
Nach Erfassung der Daten sind erste Anfragen an die Datenbasis mit dem DML Statement SELECT
möglich. SELECT ist je nach Implementierung ein sehr komplexer Befehl, der über eine Reihe von
Optionen verfügt. Dem Retrieval von SQL-Daten dient der DML Befehl
SELECT
Auswahl aus einer Tabelle/einem View
Abb. 41: DML Befehl SELECT von SQL
Aus syntaktischer Sicht ist der SELECT Befehl von INTERBASE14 wie folgt aufgebaut:
SELECT
[DISTINCT | ALL] [Funktion,][POINTER,]Kommaliste von Spaltennamen|*
FROM
Kommaliste von [creator.]Tabellennamen | ViewNamen
[WHERE
Bedingungen, die Tupel (Zeilen) spezifizieren]
[GROUP BY
Kommaliste von Spaltennamen, nach denen gruppiert wird
[HAVING
Bedingungen]]
[UNION [ALL]
SELECT Anfrage {,repeat}]
[ORDER BY
Spaltenname | Spaltennummer[order format] {,repeat}];
Abb. 42: SELECT Befehlsoptionen von SQL
Dabei gelten die folgenden Konventionen:
[]
Optionale Angaben
{}
zu wiederholende Angaben
|
alternative Angaben/Optionen
Die Bedeutung der wichtigsten Schlüsselwörter dabei sind:
DISTINCT
ignoriert doppelt vorhandene Datensätze, die die Retrieval-Bedingungen erfüllen
ALL
auch doppelte Datensätze werden gefunden (Default-Option)
WHERE
Selektion spezifischer Tupel (Zeilen) durch Formulierung von Bedingungen, die
wahr oder falsch werden, wie Bool'sche Operatoren (AND, OR, NOT), Relationaloperatoren (>,<,=...) sowie spezielle Operatoren (BETWEEN,IN,IS
NULL,LIKE,EXISTS) oder auch Unteranfragen (SELECT...)
GROUP BY
Gruppenbildung bei gleichen Werten in der angegebenen Spalte
HAVING
Selektion spezifischer Gruppen, vergleichbar mit WHERE-Bedingungen
ORDER BY
sortiert die gefundenen Sätze nach den angegebenen Spalte[n], order format:
ASC|DESC (auf-/absteigend)
Abb. 43: Bedeutung der Schlüsselwörter des DML Befehls SELECT
14
Alle nachfolgend verwendeten SQL-Beispiele lassen sich ebenfalls unter MS ACCESS ausprobieren. Die Hoteldatenbank im Access-Format finden Sie unter www.wi.fh-flensburg.de/wi/mueller/a.
ST/HfBR/DS-EDV K9039
Anfragen an die Datenbasis (Retrieval)
25
4.2. Anfragetypen Selektion, Projektion und Join
Bei Anfragen werden prinzipiell die Selektion, Projektion und der Join unterschieden.
Eine Projektion wählt aus einer Tabelle eine Anzahl von Spalten (Attributwerten) zur Anzeige aus. Für
die ausgewählten Spalten enthält die Ergebnisrelation die Werte aller Zeilen (Entities, Tupel,
Datensätze).
A1
A2
A3
A4
E1
E2
E3
E4
E5
E5
E7
Abb. 44: Schematische Darstellung einer Projektion
Die Projektion erfolgt durch Angabe der ausgewählten Spaltennamen in einer Kommaliste. Somit
entsteht als Ergebnisrelation eine gleich lange, jedoch schmalere Relation als die Ausgangsrelation:
SELECT
ZiNr, Art
{PS_1}
FROM
Zimmer;
ZiNr
Art
100
EZ
101
EZ
102
EZ
205
DZ
210
DZ
215
DZ
Es werden für alle Datensätze (d.h., alle Zimmer) die Zimmernummer und die Art (DZ|EZ) angezeigt.
Bei der Selektion werden prinzipiell bestimmte Entities/Datensätze zunächst mit allen Spalten (Attributen), die einer vorgegebenen Bedingung genügen, ausgewählt. Die Selektion erfolgt anhand einer
mit dem Bezeichner WHERE bzw. nach einer Gruppierung mittels HAVING formulierten Bedingung.
Somit entsteht gegenüber der Grundrelation eine um Zeilen verkürzte Relation, die alle Spalten enthält:
A1
A2
A3
A4
E1
E2
E3
E4
E5
E5
E7
Abb. 45: Schematische Darstellung einer Selektion
SELECT
FROM
WHERE
*
Zimmer
ZiNr >= 200;
{PS_2}
ST/HfBR/DS-EDV K9039
26
Anfragen an die Datenbasis (Retrieval)
ZiNr
Ausstattung
Art
205
DBFT
DZ
210
DBT
DZ
215
DT
DZ
Es werden alle Datensätze mit allen Attributen (*) aus der Relation Zimmer selektiert, deren Zimmernummer größer/gleich 200 ist.
Selbstverständlich lassen sich Projektion und Selektion miteinander in einer Anfrage kombinieren:
SELECT
FROM
WHERE
ZiNr, Art
Zimmer
ZiNr >= 200;
ZiNr
Art
205
DZ
210
DZ
215
DZ
{PS_3}
Es werden nun für alle Zimmer, deren Nummer größer/gleich 200 ist, die Zimmernummer und die Art
angezeigt.
Weitere Beispiele für Projektionen/Selektionen sind:
SELECT
FROM
Name
Gast;
{PS_4}
Name
Gast
Wohnort > 'Flensburg';
{PS_5}
Wohnort
Gast
Wohnort >='Pinneberg';
{PS_6}
Name
Müller
Paessens
Dr. Schmidt
Müller-Lüdenscheid
AKAD
IBM
FH Flensburg
Meier
Meier
Timm
SELECT
FROM
WHERE
Name
Müller
Paessens
Dr. Schmidt
Müller-Lüdenscheid
AKAD
IBM
Meier
Meier
Timm
SELECT
FROM
WHERE
ST/HfBR/DS-EDV K9039
Anfragen an die Datenbasis (Retrieval)
27
Wohnort
Pinneberg
Schleswig
Schleswig
Stuttgart
SELECT
FROM
WHERE
ZiNr, Art
Zimmer
Ausstattung LIKE '%T%';
ZiNr
Art
101
EZ
205
DZ
210
DZ
215
DZ
{PS_7}
Bei einem Join (Zusammensetzen von Tabellen) werden im Gegensatz zu den obigen Beispielen der
Selektion und Projektion nicht nur einer Tabelle, sondern gleichzeitig mehreren Tabellen Daten entnommen. Dabei ist jedoch zu beachten, dass für die Auswahl der Daten jeweils eine Spalte beider Tabellen identische Einträge aufweisen muss; andernfalls ist das Ergebnis der Anfrage das kartesische
Produkt beider Tabellen (Kombination eines jeden Entities aus Relation R1 mit allen Entities aus
Relation R2).
Beispiel für eine zusammengesetzte Anfrage (Equi-Join):
SELECT
FROM
WHERE
Name, Dauer
{JOIN_1}
Gast, Vermietung
Gast.KdNr = Vermietung.KdNr;
Beachten Sie, dass bei dieser Anfrage nur Gäste in der Ergebnisrelation enthalten sind, die auch
wirklich einmal als Gast ein Zimmer gemietet hatten, da die Relation Vermietung einbezogen wird;
Daher ist der Gast IBM nicht in der Ergebnisrelation enthalten.
Es ist also jeweils zu überdenken, ob man alle Gäste oder nur nutzende Gäste in die Ergebnisrelation
einbeziehen will!
Name
Dauer
Müller
12
Müller
10
Paessens
3
Dr. Schmidt
5
Müller-Lüdenscheid
5
AKAD
10
FH Flensburg
2
Meier
5
Meier
1
Timm
3
Die Spalten Gast.KdNr und Vermietung.KdNr heißen Joinspalten. Diese in der Abfrage ja mit unterschiedlichem Bezug zu Relationen genannten Attribute müssen zur Unterscheidung dann durch die
Angabe Relation.Attribut ergänzt werden. Der Join, der letztlich alle in der Ausgangsrelation enthaltenen Daten wieder zu einer Ergebnisrelation zusammenfügt, würde demnach wie folgt zu formulieren
sein:
SELECT
FROM
WHERE
AND
AND
AND
Name, Wohnort, Strasse,
Zimmer.ZiNr, Zimmer.Art, Zimmer.Ausstattung,
Miete, Dauer
Gast, Vermietung, Zimmer, Preis
Gast.KdNr = Vermietung.KdNr
Zimmer.ZiNr = Vermietung.ZiNr
Zimmer.Art = Preis.Art
Zimmer.Ausstattung = Preis.Ausstattung;
ST/HfBR/DS-EDV K9039
28
Anfragen an die Datenbasis (Retrieval)
Die Bedingung der Inhaltsgleichheit von Spalten unterschiedlicher Relationen wird Equi-Join genannt.
Der Theta-Join ist ein Verbund, der eine andere Selektionsbedingung als die Gleichsetzung von Spalten vornimmt. Hier können Vergleichsoperatoren (<,>,<>,>=,<=) oder auch Operatoren wie BETWEEN eingesetzt werden.
4.3. Operatoren in SELECT-Statements
Im Rahmen von SELECT-Anfragen an eine Datenbasis lassen sich eine Vielzahl von Operatoren zur
Beschreibung der auszuwählenden Tupel verwenden. Die Anwendung ausgewählter Operatoren
zeigen folgende beispielhafte Anfragen:
Zimmervermietungen mit einer Dauer zwischen einem und drei Tagen:
SELECT
FROM
WHERE
ZiNr
Vermietung
Dauer BETWEEN 1 AND 3;
{OPER_1}
ZiNr
100
102
215
210
Alle Gastadressen aus Orten mit Postleitzahl kleiner 4000:
SELECT
FROM
WHERE
AND
Name, Wohnort, Strasse
Gast, Plz
Gast.Wohnort = Plz.Ort
Plz.Plz < 40000;
Name
Wohnort
FH Flensburg
Flensburg
Kanzleistraße 91-92
Müller
Grundhof
Aukjer Straße 17
Paessens
Husby
Am Polldamm 1A
Timm
Kiel
Ostufer 93
AKAD
Pinneberg
Am Bahnhof 10
{OPER_2}
Straße
Dr. Schmidt
Schleswig
Landberg 18
Meier
Schleswig
Wikingturm 18
Alle Zimmer, deren Preis nicht zwischen 50 und 60 € liegen:
SELECT
ZiNr, Miete
{OPER_3}
FROM
Zimmer, Preis
WHERE
Miete NOT BETWEEN 50 AND 60
AND
Zimmer.Art = Preis.Art
AND
Zimmer.Ausstattung = Preis.Ausstattung;
ZiNr
Miete
205
100
210
80
215
75
Alle Gäste, deren Namen vorne mit "Müll" anfangen:
SELECT
FROM
WHERE
Name
Gast
Name LIKE 'Müll%';
Name
Müller
Müller-Lüdenscheid
ST/HfBR/DS-EDV K9039
{OPER_4}
Anfragen an die Datenbasis (Retrieval)
29
Alle Gäste, deren Namen mit Buchstaben zwischen A und B anfangen:
SELECT
FROM
WHERE
Name
Gast
Name BETWEEN 'A%' AND 'B%';
{OPER_5}
Name
AKAD
4.4. SQL-Funktionen
Die Ergebnisse (=selektierte Tupel) von Selektionen lassen sich weiter aufbereiten; dazu dienen
Funktionen. SQL verfügt über eine Vielzahl solcher Funktionen, deren Erläuterung diesen Rahmen
sprengen würde. Einen kurzen Überblick gibt nachfolgende Aufstellung:
FUNKTION
Beispiel
arithmetische
ROUND, TRUNC
arithm. Gruppen-
AVG, COUNT, MAX, MIN, SUM
Text-
LOWER, UPPER, SUBSTR
Datum/Zeit
ADD_MONTHS, SYSDATE
Umwandlung
TO_CHAR, TO_DATE
Abb. 46: Wichtige Standardfunktionen von SQL
Die genannten SQL-Funktionen selektieren dabei jeweils einen Wert eines Attributes (z.B. Summe,
Maximum, Minimum). Das Selektionsergebnis besteht also aus einer einspaltigen und einzeiligen Relation.
Beispiele:
Die größte Zimmermiete:
SELECT
FROM
MAX(Miete)
Preis;
{FKT_1}
Max
100.00
Die kleinste Zimmermiete:
SELECT
FROM
MIN(Miete)
Preis;
{FKT_2}
MAX(Dauer)
Vermietung;
{FKT_3}
Min
48.00
Die längste Mietdauer:
SELECT
FROM
Max
12
Umsatzsumme aller Vermietungen:
SELECT
SUM(Miete * Dauer)
FROM
Umsatzliste;
Häufig sollen jedoch neben diesen Werten zusätzliche Informationen selektiert werden. So wäre es
sinnvoll, nicht nur die längste Mietdauer, sondern den dazugehörenden Gast mit seinem Namen zu
selektieren. Oder es sind alle Gäste, deren Mietdauer unterhalb der durchschnittlichen Mietdauer
liegen, zu suchen.
ST/HfBR/DS-EDV K9039
30
Anfragen an die Datenbasis (Retrieval)
Das erste Beispiel betrifft die Relationen Gast und Vermietung. Ein erster Versuch zur Selektion des
Gastes mit der längsten Mietdauer könnte wie folgt aussehen:
Gast mit der längsten Mietdauer: (fehlerhaft)
SELECT
FROM
WHERE
Name, MAX(Dauer)
{FKT_4}
Gast, Vermietung
Gast.KdNr = Vermietung.KdNr;
Diese Query liefert in einigen Datenbanksystemen folgendes Ergebnis:
Name
Dauer
Timm
12
Diese Query erbringt zwar die korrekte maximale Mietdauer, ordnet jedoch diese nicht dem richtigen
Gast zu; damit ist diese Query fehlerhaft. Das liegt daran, dass bei der Selektion des Namens nicht
die richtige Bedingung (Namen mit der längsten Vermietdauer) formuliert wurde. Vielmehr erscheint
hier der letzte selektierte Name der Relation Gast. Die Lösung des Problems liegt in der Formulierung
einer Unterabfrage (Subquery).
4.5. Subqueries
Queries (Anfragen) können auch selbst wieder Queries enthalten. Die enthaltenen Queries werden
dann als Subqueries (Unterabfrage) bezeichnet. Dabei wird der Vergleichswert für die Selektion einer
anderen Tabelle entnommen. Die Subquery wird dabei zuerst ausgeführt, das Ergebnis geht dann
(z.B. als Bedingung) in die übergeordnete Query ein.
Kehren wir zu obigem Beispiel zurück: Wir müssen den Namen des Gastes korrekt selektieren. Dies
gelingt in diesem Beispiel durch Bildung einer Unterabfrage (Subquery), in der zunächst das Maximum der Dauer und mit diesem Wert dann der Name des Gastes selektiert wird:
Gast mit der längsten Mietdauer:
SELECT
FROM
WHERE
AND
Name, Dauer
{SUB_1}
Gast, Vermietung
Dauer = (SELECT MAX(Dauer) FROM Vermietung)
Gast.KdNr = Vermietung.KdNr;
Name
Dauer
Müller
12
Die Anfrage löst sich wie folgt auf: zuerst wird in der Unterabfrage das Maximum der Mietdauer bestimmt; An die Stelle der Unterabfrage tritt in unserem Falle der Wert 12 (= Maximum der Mietdauer),
so dass sich die bis dahin aufgelöste Anfrage wie folgt darstellt:
SELECT
FROM
WHERE
AND
Name, Dauer
{SUB_2}
Gast, Vermietung
Dauer = 12
Gast.KdNr = Vermietung.KdNr;
Somit werden in der äußeren Query alle Gäste selektiert, deren Mietdauer 12 Tage beträgt. Weitere
Beispiele zur Anwendung von Funktionen mit Subqueries sind:
Alle Gäste, deren Mietdauer unterhalb der durchschnittlichen Mietdauer liegen:
SELECT
FROM
WHERE
AND
Name, Dauer
{SUB_3}
Gast, Vermietung
Dauer < (SELECT AVG(Dauer) FROM Vermietung)
Gast.KdNr = Vermietung.KdNr;
ST/HfBR/DS-EDV K9039
Anfragen an die Datenbasis (Retrieval)
31
Name
Dauer
Paessens
3
Dr. Schmidt
5
Müller-Lüdenscheid
5
FH Flensburg
2
Meier
5
Meier
1
Timm
3
Alle Gäste, deren Mietdauer oberhalb der durchschnittlichen Mietdauer liegen:
SELECT
FROM
WHERE
AND
Name, Dauer
{SUB_4}
Gast, Vermietung
Dauer > (SELECT AVG(Dauer) FROM Vermietung)
Gast.KdNr = Vermietung.KdNr;
Name
Dauer
Müller
12
Müller
10
AKAD
10
Alle Gäste, deren Wohnorte noch nicht in der Tabelle PLZ eingetragen sind15:
SELECT
Name
{SUB_5}
FROM
Gast
WHERE
NOT EXISTS (SELECT * FROM PLZ WHERE
Gast.Wohnort=Ort);
Name
Dr. Schmidt
Meier
Alle Gäste, deren Wohnorte in der Tabelle PLZ eingetragen sind:
SELECT
FROM
WHERE
Name
{SUB_6}
Gast
EXISTS (SELECT * FROM PLZ WHERE
Gast.Wohnort=Ort);
Name
Müller
Paessens
Müller-Lüdenscheid
AKAD
IBM
FH Flensburg
Meier
Timm
Alle Gäste, die den Wohnort mit der PLZ 24944 haben:
SELECT
Name, Wohnort
{SUB_7}
FROM
Gast
WHERE
Wohnort IN (SELECT Ort FROM Plz WHERE
Plz=24944);
Name
Wohnort
FH Flensburg
Flensburg
Alle Gäste mit den gleichen Wohnorten wie die Gäste Meier:
15
Liefert im Beispiel leere Relation, da im DB-System INTERBASE und ACCESS jeweils Fremdschlüssel in Gast auf PLZ zeigen und somit keine Wohnorte ohne PLZ existieren können.
ST/HfBR/DS-EDV K9039
32
Anfragen an die Datenbasis (Retrieval)
SELECT
FROM
WHERE
Name, Wohnort
{SUB_8}
Gast
Wohnort IN (SELECT Wohnort FROM Gast WHERE Name='Meier');
Name
Wohnort
Dr. Schmidt
Schleswig
Müller-Lüdenscheid
Hagen
Meier
Schleswig
Meier
Hagen
Unteranfragen sind im Zusammenhang mit Einfügungen, Änderungen und Löschungen im Datenbestand ebenfalls hilfreich16.
4.6. SQL-Klauseln
Schließlich können auf die Ergebnisse Klauseln angewendet werden. Dies sind die Klauseln ORDER
BY, GROUP BY und HAVING.
4.6.1. ORDER BY-Klausel
ORDER BY sortiert die Tupeln nach dem oder den hinter BY angegebenen Attributen (Name oder
Nummer einer Spalte). Standardmäßig wird aufsteigend (ASC) sortiert, durch Angabe von DESC
kann die Sortierreihenfolge umgekehrt werden.
Beispiele für sortierte Ergebnisrelationen sind:
Alle Gäste mit allen Gastattribute, sortiert nach Name:
SELECT
FROM
ORDER BY
KdNr
*
Gast
Name;
Name
{ORDER_1}
Wohnort
Strasse
104 AKAD
Pinneberg
Am Bahnhof 10
102 Dr. Schmidt
Schleswig
Landberg 18
106 FH Flensburg
Flensburg
Kanzleistraße 91-92
105 IBM
Stuttgart
Watsonstraße 23
107 Meier
Schleswig
Wikingturm 243
108 Meier
Hagen
Volmestraße 12
100 Müller
Grundhof
Aukjer Straße 17
103 Müller-Lüdenscheid
Hagen
Am Waldesrand 3A
101 Paessens
Husby
Am Polldamm 1A
109 Timm
Kiel
Ostufer 93
Alle Preise, sortiert nach der Zimmerart und Ausstattung:
SELECT
FROM
ORDER BY
16
Miete
{ORDER_2}
Preis
Art ASC, Ausstattung DESC;
Vgl. dazu Kapitel 5 auf Seite 39.
ST/HfBR/DS-EDV K9039
Anfragen an die Datenbasis (Retrieval)
33
Miete
90.00
100.00
75.00
80.00
75.00
60.00
75.00
55.00
52.00
48.00
4.6.2. GROUP BY-Klausel
GROUP BY fasst eine Teilmenge von Tupeln zu einem neuen Tupel zusammen (Gruppenbildung):
Damit lassen sich Ergebnisse nach beliebigen Kriterien gruppieren. Dazu werden bezüglich des
Gruppierungsmerkmals identische Entities zusammengefasst. Voraussetzung ist die Verwendung
einer Gruppenfunktion wie Summe oder Anzahl.
Wir wollen eine Liste unserer Gastnamen und die Häufigkeit des Vorkommens gleicher Namen
erzeugen, wobei gleiche Namen zu einer Gruppe zusammengefasst werden. Unerheblich soll zunächst dabei sein, ob die verzeichneten Gäste wirklich schon einmal bei uns ein Zimmer gemietet
haben (d.h., ob sie in der Tabelle Vermietung auch enthalten sind):
SELECT
FROM
GROUP BY
Name, COUNT(*)
Gast
Name;
Name
*
AKAD
1
Dr. Schmidt
1
FH Flensburg
1
IBM
1
Meier
2
Müller
1
Müller-Lüdenscheid
1
Paessens
1
Timm
1
{GROUP_1}
Das Ergebnis zeigt, dass der Name Meier zweimal in Gast vorkommt. Nach der Bedingung, dass
jeder Gast in der Relation Gast nur einmal verzeichnet sein darf, müsste es sich hier also um zwei
unterschiedliche Gäste mit dem Namen Meier handeln. Wir können dies prüfen, indem wir nicht den
Gastnamen, sondern das unterscheidende Kriterium Gast.KdNr zur Gruppierung verwenden. Zu17
sätzlich müssen alle weiteren, nicht gruppierten Attribute in der Gruppierung genannt werden . Dabei
dürften dann keine Häufigkeiten über 1 aufzufinden sein:
SELECT
FROM
GROUP BY
17
Name, COUNT(*)
Gast
KdNr, Name;
{GROUP_2}
Rein theoretisch könnten ja zwei Gäste unter der selben Kundennummer existieren. Daher müßte
dann hier nach dem Namen weiter gruppiert werden. Das Datenbanksystem kann die semantische
Bedeutung von KdNr (eindeutiger Primärschlüssel) hier ja nicht überprüfen.
ST/HfBR/DS-EDV K9039
34
Anfragen an die Datenbasis (Retrieval)
Name
*
AKAD
1
Dr. Schmidt
1
FH Flensburg
1
IBM
1
Meier
1
Meier
1
Müller
1
Müller-Lüdenscheid
1
Paessens
1
Timm
1
Eine Gruppierung nach einem anderen als dem Namensattribut zeigt folgende Anfrage:
SELECT
FROM
GROUP BY
Wohnort, COUNT(*)
Gast
Wohnort;
Wohnort
*
Flensburg
1
Grundhof
1
Hagen
2
Husby
1
Kiel
1
Pinneberg
1
Schleswig
2
Stuttgart
1
{GROUP_3}
Uns interessiert aber vermutlich weniger eine Aufstellung aller Gastnamen als vielmehr eine Liste, in
der nur die Gäste, die bei uns schon einmal ein Zimmer gemietet haben, verzeichnet sind. Diese
Information ist in der Tabelle Vermietung enthalten, die wir deshalb als weiteres Selektionskriterium heranziehen müssen. Gleichzeitig wollen wir dabei die Ergebnisrelation nach Häufigkeit der Vermietung (höchste Vermietung zuerst, d.h., absteigend) und, bei gleicher Häufigkeit, aufsteigend nach
Namen sortieren. Auch hier muss das Gruppierungskriterium wieder die KdNr sein, da andernfalls
der Gast Meier mit je 2 Vermietungen doppelt erscheinen würde:
SELECT
FROM
WHERE
GROUP BY
ORDER BY
Name, COUNT(*)
{GROUP_4}
Gast, Vermietung
Gast.KdNr = Vermietung.KdNr
Vermietung.KdNr, Name
COUNT(*) DESC, Name ASC;
Name
*
Müller
2
AKAD
1
Dr. Schmidt
1
FH Flensburg
1
Meier
1
Meier
1
Müller-Lüdenscheid
1
Paessens
1
Timm
1
Es erfolgt hier eine Sortierung zunächst nach der Häufigkeit, dann nach dem Namen. Die Tabelle
zeigt auch nun, dass es sich bei den Gästen Meier um unterschiedliche Entities handelt und dass der
Gast IBM nun nicht mehr in der Ergebnisrelation enthalten ist, da er bei uns zwar in der Relation Gast
enthalten ist, aber noch keine Vermietung zu verzeichnen hat.
ST/HfBR/DS-EDV K9039
Anfragen an die Datenbasis (Retrieval)
35
Ähnliche Auswertungen lassen sich nun mit der Group-Klausel für andere Gruppierungsmerkmale
erstellen: So könnte es von Interesse sein, aus welchem Einzugsbereich unsere Kunden kommen.
Wir gruppieren die Vermietungen an unsere Gäste also nach dem Wohnort:
SELECT
FROM
WHERE
GROUP BY
ORDER BY
Wohnort
Wohnort, COUNT(*)
{GROUP_5}
Gast, Vermietung
Gast.KdNr=Vermietung.KdNr
Wohnort
COUNT(*), Wohnort;
*
Grundhof
2
Hagen
2
Schleswig
2
Flensburg
1
Husby
1
Kiel
1
Pinneberg
1
Beachten Sie, dass wir hier nach dem Wohnort gruppiert haben, d.h., dass der Wohnort von identischen Gästen, die mehrfach ein Zimmer bei uns gemietet haben, auch mit der entsprechenden Häufigkeit in der Ergebnisrelation auftreten. Dies gilt hier für die Vermietungen an Gäste aus Grundhof:
Es handelt sich hier um ein- und denselben Gast Müller, der zweimal ein Zimmer gemietet hatte.
Dagegen stehen hinter den Vermietungen an Hagener und Schleswiger jeweils zwei unterschiedliche
Gäste.
Wollen wir dagegen diese Mehrfachvermietungen ausschließen und nur die Zahl der unterschiedlichen nutzenden Gäste aus den unterschiedlichen Orten selektieren, ist die Anfrage wie folgt zu formulieren:
SELECT
FROM
WHERE
GROUP BY
ORDER BY
DISTINCT(Wohnort), COUNT(*)
Gast,Vermietung
Gast.KdNr = Vermietung.KdNr
Wohnort
COUNT(*), Wohnort;
Wohnort
*
Hagen
2
Schleswig
2
Flensburg
1
Grundhof
1
Husby
1
Kiel
1
Pinneberg
1
{GROUP_6}
Die Ergebnisrelation zeigt, dass nun der Ort Grundhof nur mit einer Häufigkeit 1 erscheint, da in wir
nur einen nutzenden Gast aus Grundhof hatten.
Folgende Anfragen zeigen weitere Beispiele für Gruppenfunktionen.
Gesamtmietdauer, Miethäufigkeit und durchschnittliche Mietdauer aller Vermietungen aus den
jeweiligen Gastwohnorten:
SELECT
FROM
WHERE
GROUP BY
ORDER BY
Wohnort, SUM(Dauer), COUNT(*), AVG(Dauer)
Gast, Vermietung
Gast.KdNr=Vermietung.KdNr
Wohnort
AVG(Dauer) DESC;
{GROUP_7}
ST/HfBR/DS-EDV K9039
36
Anfragen an die Datenbasis (Retrieval)
Wohnort
Dauer
*
Dauer
Grundhof
22
2
11.0
Pinneberg
10
1
10.0
Schleswig
10
2
5.0
Hagen
6
2
3.0
Husby
3
1
3.0
Kiel
3
1
3.0
Flensburg
2
1
2.0
Namen aller Gäste, ihre Gesamtmietdauer und Miethäufigkeit:
SELECT
FROM
WHERE
GROUP BY
ORDER BY
Name, SUM(Dauer), COUNT(*)
Gast, Vermietung
Gast.KdNr=Vermietung.KdNr
Gast.KdNr, Name
Name, SUM(Dauer) DESC;
Name
*
AKAD
{GROUP_8}
*
10 1
Dr. Schmidt
5 1
FH Flensburg
2 1
Meier
5 1
Meier
1 1
Müller
22 2
Müller-Lüdenscheid
5 1
Paessens
3 1
Timm
3 1
4.6.3. HAVING-Klausel
Die HAVING-Klausel, die nur in Verbindung mit der GROUP BY-Klausel Einsatz findet, nimmt aus
den gebildeten Gruppen weitere Selektionen vor. Dadurch können die Gruppierungsmerkmale als
Selektionsbedingung der Ergebnisrelation Anwendung finden.
Wie oft und wie lange
insgesamt da:
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
waren "gute" Kunden (Kunden mit Gesamtmietdauer von mehr als 4 Tagen)
Name, SUM(Dauer), COUNT(*)
Gast, Vermietung
Gast.KdNr=Vermietung.KdNr
Gast.KdNr, Name
SUM(Dauer) > 4
Name, SUM(Dauer) DESC;
Name
1
2
AKAD
10
1
Dr. Schmidt
5
1
Meier
5
1
Müller
22
2
5
1
Müller-Lüdenscheid
{HAVE_1}
4.6.4. UNION-Klausel
Zuletzt ist es auch möglich, das Ergebnis mehrerer Selektionen zu einem gemeinsamen Ergebnis
zusammenzufassen. Voraussetzung dazu ist, dass alle Selektionen einen typgleichen Aufbau der
selektierten Relation aufweisen. Die Vereinigung solcher typgleicher Selektionsergebnis werden mit
der UNION-Klausel erreicht.
Beispiel: Wir wollen zunächst eine einfache ABC-Analyse der Mietverhältnisse erstellen. Dazu selektieren wir alle Vermietverhältnisse, deren Dauer über 10 Tagen (A-Vermietungen), dann solche, deren
Dauer zwischen 3 und 10 Tagen (B-Vermietungen) und schließlich solche, deren Dauer unter 3 Tagen liegen. Alle drei Ergebnisse sollen gemeinsam in einer Ergebnisrelation erscheinen:
ST/HfBR/DS-EDV K9039
Anfragen an die Datenbasis (Retrieval)
Klasse
37
Anzahl
Vermietungen über 10 Tage
1
Vermietungen zwischen 3 und 10 Tagen
7
Vermietungen unter 3 Tagen
2
Eine solche Selektion wäre durch die UNION-Klausel möglich:
SELECT
'Vermietungen
FROM Vermietung
WHERE
Dauer > 10
GROUP BY
KdNr
UNION
SELECT
'Vermietungen
FROM Vermietung
WHERE
Dauer BETWEEN
GROUP BY
KdNr
UNION
SELECT
'Vermietungen
FROM Vermietung
WHERE
Dauer < 3
GROUP BY
KDNR;
über 10 Tagen',COUNT(*)
von 3 bis 10 Tage',COUNT(*)
3 AND 10
unter 3 Tagen',COUNT(*)
{UNION_1}
Selbstverständlich sind auch komplexere ABC-Anaylsen denkbar, die auf der Basis der kundenbezogenen Vermiettage einen Kunden jeweils zu einer Kundenklasse zuordnen. Gehen wir dazu von
folgenden Prämissen aus: Ein Kunde sei ein A-Kunde, wenn die Summe seiner Miettage 30% aller
Miettage überschreitet. Liegt die Summe zwischen 10% und 30%, soll es sich um einen B-Kunden
handeln. Schließlich sind alle Kunden C-Kunden, deren Gesamtmiettage weniger als 10% betragen.
Zur Berechnung lassen sich Subqueries und Ausdrücke in SQL verwenden.
Die folgende komplexe SQL-Abfrage erzeugt eine ABC-Kundenanalyse auf der Basis der gesamten
Miettage:
/* A-Kunden bestimmen, mehr als 30% aller Miettage */
SELECT
NAME, 'A-KUNDE' AS Klasse,
SUM(DAUER) AS Miettage,
SUM(DAUER)/(SELECT SUM(DAUER) FROM VERMIETUNG) * 100 AS Anteil
FROM
VERMIETUNG, GAST
WHERE
VERMIETUNG.KDNR=GAST.KDNR
GROUP BY KDNR, NAME
HAVING
SUM(DAUER) > ((SELECT SUM(DAUER) FROM VERMIETUNG) * 0.3)
UNION
/* B-Kunden bestimmen, zwischen 10 und 30% aller Miettage */
SELECT
NAME, 'B-KUNDE' AS Klasse,
SUM(DAUER),
SUM(DAUER)/(SELECT SUM(DAUER) FROM VERMIETUNG) * 100 AS Anteil
FROM
VERMIETUNG, GAST
WHERE
VERMIETUNG.KDNR=GAST.KDNR
GROUP BY KDNR, NAME
HAVING
SUM(DAUER) between
((SELECT SUM(DAUER) FROM VERMIETUNG) * 0.1)
AND
((SELECT SUM(DAUER) FROM VERMIETUNG) * 0.3)
UNION
/* C-Kunden bestimmen, weniger als 10 % aller Miettage */
SELECT
NAME, 'C-KUNDE' AS Klasse,
SUM(DAUER),
SUM(DAUER)/(SELECT SUM(DAUER) FROM VERMIETUNG) * 100 AS Anteil
FROM
VERMIETUNG, GAST
WHERE
VERMIETUNG.KDNR=GAST.KDNR
GROUP BY KDNR, NAME
HAVING
SUM(DAUER) < ((SELECT SUM(DAUER) FROM VERMIETUNG) * 0.1)
ST/HfBR/DS-EDV K9039
38
Anfragen an die Datenbasis (Retrieval)
ORDER BY 3 DESC;
{ABC_0.SQL}
Die Anfrage liefert folgende Ergebnisrelation:
NAME
KLASSE
MIETTAGE
ANTEIL
Müller
A-KUNDE
22
39,2857142857143
AKAD
B-KUNDE
10
17,8571428571429
Meier
C-KUNDE
5
8,92857142857143
Dr. Schmidt
C-KUNDE
5
8,92857142857143
Müller-Lüdenscheid
C-KUNDE
5
8,92857142857143
Timm
C-KUNDE
3
5,35714285714286
Paessens
C-KUNDE
3
5,35714285714286
IBM
C-KUNDE
2
3,57142857142857
Meier
C-KUNDE
1
1,78571428571429
4.7. Verwendung von Literalen
Zur Verbesserung der Verständlichkeit der Ergebnisrelation ist es möglich, zusätzlich erklärenden
Text (Literale) in eine zusätzliche Spalte der Tabelle einzufügen. Folgende SQL-Anfrage verdeutlicht
Ziel und Vorgehen dabei:
SELECT
'Gast', NAME, 'war insgesamt', SUM(DAUER),
'Tage da und zwar', COUNT(DAUER), 'mal'
FROM
VERMIETUNG,GAST
WHERE
GAST.KDNR=VERMIETUNG.KDNR
GROUP BY
VERMIETUNG.KDNR, Name
ORDER BY
NAME ASC;
{LITER_1}
1
Name
2
5
6
Gast
AKAD
war insgesamt
10 Tage da und zwar
1
mal
Gast
Dr. Schmidt
war insgesamt
5 Tage da und zwar
1
mal
Gast
FH Flensburg
war insgesamt
2 Tage da und zwar
1
mal
Gast
Meier
war insgesamt
5 Tage da und zwar
1
mal
Gast
Meier
war insgesamt
1 Tage da und zwar
1
mal
Gast
Müller
war insgesamt
22 Tage da und zwar
2
mal
Gast
Müller-Lüdenscheid
war insgesamt
5 Tage da und zwar
1
mal
Gast
Paessens
war insgesamt
3 Tage da und zwar
1
mal
Gast
Timm
war insgesamt
3 Tage da und zwar
1
mal
ST/HfBR/DS-EDV K9039
3
4
Änderung und Löschung von Daten
5.
39
Änderung und Löschung von Daten
In der Datenbank befindliche Daten können über die DML Statements UPDATE bzw. DELETE geändert bzw. gelöscht werden. Änderungen und Löschungen können durch entsprechende Optionen bei
der Einrichtung der Tabellen/Views an Passwörter gebunden werden.
Die Änderung der Inhalte von Datensätzen erfolgt über das DML Kommando:
UPDATE
[creator.]Tabellen-Name|View-Name
SET
Spaltenname = neuer Wert {repeat,}
[WHERE
Bedingungen|SELECT Unteranfrage]
Abb. 47: DML Befehl UPDATE von SQL
Beispiel: Alle Preise der Doppelzimmer erhöhen sich um 5 %
UPDATE
SET
Preis
Miete=Miete*1.05;
{UPDATE_1}
Die Löschung der Inhalte von Datensätzen erfolgt über das DML Kommando:
DELETE FROM
[creator.]Tabellen-Name|View-Name
WHERE
Bedingungen|SELECT Unteranfrage
Abb. 48: DML Befehl DELETE von SQL
Beispiel: Alle Zimmer, deren Zimmernummer > 215 ist, sollen gelöscht werden
DELETE
FROM Zimmer
WHERE ZiNr > 215;
{DELETE_1}
Auch hier wirken die bei der Einrichtung der Tabellen genutzten Optionen zur Sicherung der Integrität
der Datenbank: Würde bspw. ein Entity in Preis gelöscht, auf das noch eine Fremdschlüsselbeziehung verweist (z.B. Preis ('DBT','EZ',55)), so würde die Löschung auf Grund der Option RESTRICT
abgewiesen (No update, foreign key exists with restrict).
Ähnliches gilt bei der Änderung von Tupeln. Angenommen, die Preisrelation enthalte folgende Entites:
R.Preis
Ausstattung
Art
Miete
DBT
EZ
55.00
DBT
DZ
75.00
Abb. 49: Tupel in Preis
Nun kann ein "DBT"-Zimmer nur auf ein Doppelzimmer, im Moment aber nicht auf ein Einzelzimmer
mit der Ausstattung 'DBFT' geändert werden, da eine solche Kombination in Preis derzeit nicht enthalten ist und damit die Fremdschlüsselbeziehung nicht aufgebaut werden könnte (s. 1.9.1, Reihenfolge des Einfügens).
Sollen ein oder mehrere Entities gelöscht oder geändert werden und ist deren Primärschlüsselwert
nicht bekannt, so hilft hier wiederum eine Unteranfrage zur Bestimmung des Primärschlüsselwertes.
Beispiel: Lösche die Vermietung des Gastes AKAD im Zimmer 205, die Buchungsnummer der Vermietung ist im Moment unbekannt:
ST/HfBR/DS-EDV K9039
40
Änderung und Löschung von Daten
DELETE
FROM
WHERE
{DELETE_2}
Vermietung
KdNr = (SELECT
FROM
WHERE
AND
KdNr
Gast
Name = 'AKAD'
ZiNr = 205);
Beispiel: Ändere die Mietdauer des Gastes Müller im Zimmer 101 von 10 auf 20 Tage, die Buchungsnummer der Vermietung und Kundennummer Müller sind im Moment unbekannt:
UPDATE
Vermietung
{UPDATE_2}
SET
Dauer = 20
WHERE
Dauer = 10
AND
KdNr = (SELECT
KdNr
FROM
Gast
WHERE
Name = 'Müller');
Beispiel: Erfasse eine neue Vermietung des Gastes AKAD (Kundennummer im Moment unbekannt)
im Zimmer 205 mit der Dauer 30 unter der nächstfolgenden Buchungsnummer, wobei die aktuell
nächste Buchungsnummer ebenfalls unbekannt ist.
INSERT INTO Vermietung
{INSERT_1}
SELECT
MAX(BuNr)+1,
Gast.KdNr,
205, 30
FROM
Vermietung, Gast
WHERE
Name = 'AKAD';
ST/HfBR/DS-EDV K9039
Entwicklung von Views für die Datenbank
6.
41
Entwicklung von Views für die Datenbank
Views sind gespeicherte und damit wieder abrufbare Anfragen an die Datenbasis. Sie bilden zugleich
den normalen Weg eines Benutzers zu seinen Daten. Vorteil: Wiederkehrende und evtl. komplizierte
Anfragen werden gespeichert und "auf Knopfdruck" (d.h., Aufruf des Views) durchführbar (Konzept
der virtuellen Tabellen). Mit Views sind alle Data Manipulation Language-Operationen erlaubt (z.B.
SELECT, INSERT, DELETE), sofern bestimmte Regeln eingehalten werden.
Neben dem genannten Ziel der Verfügbarkeit komplizierter Anfragen auf "Knopfdruck" (Vereinfachung und Fehlerreduktion) lassen sich mit Views weitere Ziele verfolgen: Zum einen sind sie ein
Instrument des Datenschutzes, indem sie dem jeweiligen Benutzer Zugriff nur auf solche Daten, für
die er eine Zugriffsberechtigung hat, gewähren. Außerdem lassen sich damit Teilmengen aus Relationen abbilden; für die Aufgabenstellung unnötige Daten werden unterdrückt (information hiding,
Datenschutz). Schließlich kann in Views bewusst (virtuelle) Redundanz, die beim Datenbankentwurf
verhindert wird, erzeugt werden, um damit dem Benutzer weitere Anfragen zu ersparen. So ist denkbar, dass eine Relation die Attribute Menge und Preis, nicht aber das Attribut Umsatz erhält, da dieses ja jederzeit aus Menge X Preis erzeugt werden kann (also redundant wäre). Für den Benutzer
kann jedoch in seinem View die virtuelle Spalte Umsatz durch Aufnahme des Umsatzattributes und
Bildung der Werte Menge X Preis mit ausgegeben werden. Schließlich erhöhen Views die physikalische Datenunabhängigkeit, da nach Änderung einer Datenbankstruktur nur die Definition bestehender Views in der Datenbank adaptiert werden müssen. Der Datenstrom, der bspw. über diesen View
an ein Anwendungsprogramm fließt (z.B. Daten zur monatlichen Lohnabrechnung), kann damit ohne
Änderung des Anwendungsprogramms selbst angepasst werden.
Views lassen sich mit folgenden DDL-Befehl einrichten:
CREATE VIEW
[creator.]View-Name [(Spaltenname {repeat,})]
AS
Subquery [WITH CHECK OPTION];
Abb. 50: CREATE VIEW Befehl von SQL
Die Subquery kann eine wie unter SELECT beschriebene Anfrage enthalten.
Beispiel: Erstellen des Views Vermietungen, das eine komplette Liste aller Mietverhältnisse (auch
Mehrfachbesuche derselben Gäste) mit Gastdaten, Zimmernummer und Mietdauer ausgibt.
Prinzip : Die Anfrage wird "normal" formuliert, allerdings wird sie mit einem Namen versehen, unter
dem sie künftig abrufbar ist:
CREATE VIEW Vermietungen
AS SELECT
Name, Wohnort, ZiNr, Dauer
FROM
Gast, Vermietung
WHERE
Gast.KdNr=Vermietung.KdNr
ORDER BY
Name;
Damit wird die virtuelle Tabelle Vermietungen erzeugt. Auf sie kann mit normalen DML-Befehlen
zugegriffen werden:
SELECT
FROM
ORDER BY
*
Vermietungen
Name;
Name
Wohnort
ZiNr
Dauer
AKAD
Pinneberg
205
10
Dr. Schmidt
Schleswig
102
5
FH Flensburg
Flensburg
102
2
Meier
Schleswig
101
5
Meier
Hagen
215
1
Müller
Grundhof
102
12
Müller
Grundhof
101
10
Müller-Lüdenscheid
Hagen
210
5
Paessens
Husby
100
3
Timm
Kiel
210
3
ST/HfBR/DS-EDV K9039
42
Entwicklung von Views für die Datenbank
SELECT
FROM
ORDER BY
Name, ZiNr
Vermietungen
ZiNr;
Name
ZiNr
Paessens
100
Meier
101
Müller
101
Dr. Schmidt
102
FH Flensburg
102
Müller
102
AKAD
205
Müller-Lüdenscheid
210
Timm
210
Meier
215
Jedoch kann dieser View auch in weiteren Anfragen als "Grunddatenmenge" zum Zwecke weiterer
Selektionen verwendet werden. Selbst Views können dabei auf Views aufbauen.
Anfrage aller Mieteradressen (ohne doppelte Gäste)
SELECT
FROM
DISTINCT Name, Wohnort
Vermietungen;
Name
Wohnort
AKAD
Pinneberg
Dr. Schmidt
Schleswig
FH Flensburg
Flensburg
Meier
Schleswig
Meier
Hagen
Müller
Grundhof
Müller-Lüdenscheid
Hagen
Paessens
Husby
Timm
Kiel
Anfrage aller "Müller"-Besuche:
SELECT
FROM
WHERE
*
Vermietungen
Name = 'Müller';
Name
Wohnort
ZiNr
Dauer
Müller
Grundhof
102
12
Müller
Grundhof
101
10
Aufgabe: Erstellen eines Views für eine Preisliste mit Ausstattungsmerkmalen:
CREATE VIEW
AS SELECT
FROM
WHERE
AND
Preisliste
ZiNr,Zimmer.Art,Zimmer.Ausstattung,Miete
Preis, Zimmer
Zimmer.Ausstattung=Preis.Ausstattung;
Zimmer.Art=Preis.Art;
Anfrage:
SELECT
*
FROM Preisliste;
ST/HfBR/DS-EDV K9039
Entwicklung von Views für die Datenbank
43
ZiNr
Art
Ausstattung
Miete
205
DZ
DBFT
100
210
DZ
DBT
80
215
DZ
DT
75
100
EZ
DBF
60
101
EZ
DBT
52
102
EZ
DBR
55
Aufgabe: Erstellen eines Views für eine Umsatzliste aller Zimmer:
CREATE VIEW
AS SELECT
FROM
WHERE
AND
AND
AND
Umsatzliste (Name, ZiNr, Miete, Dauer, Umsatz)
Name, Zimmer.ZiNr, Miete, Dauer, Miete*Dauer
Gast, Vermietung, Zimmer, Preis
Gast.KdNr=Vermietung.KdNr
Vermietung.ZiNr=Zimmer.ZiNr
Zimmer.Ausstattung=Preis.Ausstattung
Zimmer.Art=Preis.Art;
Anfrage:
SELECT
*
FROM Umsatzliste;
Name
ZiNr
Miete
Dauer
Umsatz
AKAD
205
100
10
1000
Dr. Schmidt
100
55
5
275
FH Flensburg
102
55
2
110
Meier
101
52
5
260
Meier
210
75
1
75
Müller
102
55
12
660
Müller
101
52
10
520
Müller-Lüdenscheid
210
80
5
400
Paessens
100
60
3
180
Timm
210
80
3
240
Zusätzlich lassen sich die ausgegebenen Spalten eines Views mit einer Überschrift versehen. Dies ist
insbesondere dann empfehlenswert, wenn es sich um im Rahmen des Views gebildete virtuelle Spalten handelt. Die Spaltenüberschriften werden bei INTERBASE hinter dem Viewnamen in Klammern
angegeben. Bei ORACLE werden diese jeweils hinter der selektierten Spalte direkt angegeben.
Aufgabe: Erstellen eines Views mit Gastnamen, deren gesamte Mietdauer und Miethäufigkeit mit
entsprechenden Überschriften:
CREATE VIEW
AS SELECT
FROM
WHERE
GROUP BY
ORDER BY
Statistik_1 (Gast, Mietdauer, Haeufigkeit)
Name, SUM(Dauer), COUNT(*)
Gast, Vermietung
Gast.KdNr=Vermietung.KdNr
KdNr, Name
Name;
Anfrage:
SELECT
*
FROM Statistik_1:
ST/HfBR/DS-EDV K9039
44
Entwicklung von Views für die Datenbank
Gast
Mietdauer
Haeufikgeit
AKAD
10
1
Dr. Schmidt
5
1
FH Flensburg
2
1
Meier
5
1
Meier
1
1
Müller
22
2
Müller-Lüdenscheid
5
1
Paessens
3
1
Timm
3
1
Darüber hinaus lassen sich in Anfragen auch mathematische Operatoren und Literale verwenden:
CREATE VIEW Auslastung (Auslastung, Tage)
AS SELECT
'Bisherige Auslastung : ',SUM(Dauer)
FROM
Vermietung;
Anfrage:
SELECT * FROM Auslastung;
Auslastung
Tage
Bisherige Auslastung
56
CREATE VIEW D_Dauer (mittl_Dauer)
AS SELECT
AVG(Dauer)
FROM
Vermietung;
Anfrage:
SELECT * FROM D_Dauer;
Mitll_Dauer
5.600
Views lassen sich - wie Tabellen - ebenfalls löschen. Dazu dient der SQL-Befehl DROP VIEW:
DROP VIEW
ViewName;
Beispiel: DROP VIEW Zimmerliste;
Views können auch als Basis für die Datenmanipulation via Einfügen, Ändern und Löschen eingesetzt
werden. Dafür gelten jedoch spezielle Regeln, die den Rahmen dieses Beitrags übersteigen würden.
Oftmals lassen sich jedoch DML-Befehle nur dann über Views realisieren, wenn diese Views jeweils
nur 1 Relation adressieren (d.h., keine Views, die einen JOIN enthalten).
ST/HfBR/DS-EDV K9039
Hinweise zu den normalisierten Relationen
7.
45
Hinweise zu den normalisierten Relationen
Scheinbar muss bei einer Änderungen in der Ausstattung eines Zimmers eine doppelte Änderung der
Datenbasis vorgenommen werden: Nämlich in der Relation Zimmer und in der Relation Preis. Dies ist
aber nicht korrekt! Folgende Fälle sind vorstellbar:
1. Das veränderte Zimmer erhält eine "Standardausstattung", die bereits in Preis beschrieben ist.
Dann wird ab sofort der richtige Preis angezeigt.
2. Das veränderte Zimmer erhält eine neue Ausstattung, die bisher noch nicht in Preis beschrieben
war. Daraus können sich zwei mögliche Folgen, die durchaus erwünscht sein können, ergeben:
a) Besteht zwischen der Relation Zimmer und der Relation Preis über Ausstattung und Art
eine Fremdschlüsselbeziehung, so wird das Einfügen eines Tupels in Zimmer mit einer
Ausstattungs-/Art-Kombination, die derzeit nicht in Preis beschrieben ist, abgewiesen (Foreign Key, no matching value in referenced table Preis). Zuerst wäre hier ein entsprechender Eintrag in der Relation Preis erforderlich.
b) Besteht jedoch eine solche Beziehung nicht, kann ein Eintrag in die Relation Zimmer auch
unabhängig von einer entsprechenden Preisrelation vorgenommen werden. Allerdings wird
dann das Zimmer bei einem Join mit der Relation Preis nicht selektiert:
SELECT
Zimmer.Art, Zimmer.Ausstattung, Miete
FROM
Zimmer, Preis
WHERE
Preis.Ausstattung = Zimmer.Ausstattung
AND
Preis.Art=Zimmer.Art;
Das neue Zimmer wird nicht aufgeführt, da die neuen Ausstattungsmerkmale in der Relation
Preis noch nicht implementiert sind; somit wird die Bedingung Preis.Ausstattung = Zimmer.Ausstattung nicht TRUE. Erst nach Aufnahme in die Preisrelation erfolgt eine vollständige
Ausgabe. Dazu ein Beispiel:
INSERT INTO ZIMMER VALUES (160,'DTFV','DZ');
V = Videorekorder
SELECT
FROM
WHERE
Zimmer.Art, Zimmer.Ausstattung, Miete
Zimmer, Preis
Preis.Ausstattung = Zimmer.Ausstattung:
oder Nutzung des Views ZIMMERLISTE
SELECT
*
FROM Zimmerliste;
•
Das neue Zimmer wird nicht mit angezeigt, da für diese Ausstattung noch kein Preis vorgesehen ist.
INSERT INTO Preis
VALUES
('DTFV','62.00');
SELECT
FROM
•
*
Zimmerliste;
Das neue Zimmer wird mit angezeigt, da für diese Ausstattung jetzt ein Preis vorgesehen ist.
ST/HfBR/DS-EDV K9039
46
8.
Literaturhinweise
Literaturhinweise
AKAD-Lektionen
Großer, Rainer, Renkl, Cornelius
Wirtschaftsinformatik, Lektion 1, Modelle betrieblicher Informationssysteme, hrsg. V. AKAD,
Seite 63 - 65
Fuchs, Gerhard, Bauer, Jürgen, Jaquart, Sabine
Wirtschaftsinformatik, Lektion 5, Individuelle Datenverarbeitung, hrsg. V. AKAD, Seite 48 - 51
Stelck, Klaus
Wirtschaftsinformatik, Lektion 3, Datenbanken, hrsg. V. AKAD, Seite 37 - 85
Stickel, Eberhard
Wirtschaftsinformatik, Lektion 6, Software-Entwicklung, hrsg. V. AKAD, Seite 40 - 42
Weiterführende Literatur
Dürr/Rademacher
Einsatz von Datenbanksystemen, Springer 1990, ISBN 3-540-52080-5
Diverse Autoren
Handbuch der modernen Datenverarbeitung (HMD), Schwerpunktheft Datenmodellierung,
Heft 152 vom März 1990 (27. Jg), Forkel 1990, ISSN 0723-5208
Eilers/Jansen/de Volder
SQL in der Praxis, Addison-Wesley 1990, ISBN 3-925118-59-4
Finkenzeller/Kracke/Unterstein
Systematischer Einsatz von SQL ORACLE, Addison-Wesley 1989, ISBN 3-89319-117-8
Hansen, H.R.,
Wirtschaftsinformatik I, 7. Auflage, UTB 1992, ISBN 3-8252-0802-8
Korth/Silberschatz
Database System Concepts, McGraw-Hill 1986, ISBN 0-07-100529-3
Moos/Daues
SQL-Datenbanken - Der Weg vom Konzept zur Realisierung, Vieweg 1991,
ISBN 3-528-05183-3
Reese, Joachim
Wirtschaftsinformatik, eine Einführung, Gabler 1990, ISBN 3-409-13380-1
Sauer
Relationale Datenbanken, Theorie und Praxis, Addison-Wesley 1991, ISBN 3-89319-167-4
Schäfer
Datenstrukturen und Datenbanken, Vieweg 1989, ISBN 3-528-04612-0
Stahlknecht, Peter
Einführung in die Wirtschaftsinformatik, 6. Auflage, Springer 1993, ISBN 3-540-56370-9
Van der Lans
Das SQL-Lehrbuch, Addison-Wesley 1990, ISBN 3-925118-77-2
Vetter
Der Aufbau betrieblicher Informationssysteme mittels konzeptioneller Datenmodellierung,
5. Aufl., Teubner 1989, ISBN 3-519-42464-9
Vetter
Konzeptionelle Datenmodellierung, in: Handbuch Wirtschaftsinformatik, hrsg. v. Kurbel/Strunz,
Seite 383 - 401, Poeschel 1990, ISBN 3-7910-0499-9
ST/HfBR/DS-EDV K9039
Stichwortverzeichnis
9.
47
Stichwortverzeichnis
-11. Normalform ..................................................... 9
1NF ..................................................................... 9
-22. Normalform ................................................... 10
2NF ................................................................... 10
-33. Normalform ................................................... 12
3NF ................................................................... 12
Fremdschlüsselbeziehung .................................. 22
Fremdschlüsselbeziehung .................................. 39
Fremdschlüsselbeziehung .................................. 39
Fremdschlüsselbeziehung .................................. 45
Funktion
arithmetische................................................. 29
AVG .............................................30, 31, 35, 44
COUNT ...................... 33, 34, 35, 36, 37, 38, 43
Datum/Zeit .................................................... 29
Gruppen- ....................................................... 29
MAXimum ............................................... 29, 30
MINimum ...................................................... 29
SUMme ............................ 29, 35, 36, 38, 43, 44
Text ............................................................... 29
Umwandlung ................................................. 29
-G-AAbhängigkeit........................................................ 8
funktionale ...................................................... 8
transitive ..............................................9, 12, 16
voll funktionale ............................................ 8, 9
Anomalie ......................................................... 4, 6
Änderungs- ..................................................... 7
Einfüge- .......................................................... 6
Lösch-........................................................7, 10
Anomalieprobleme..........................................7, 10
Attribut
Fremdschlüssel-............................................ 15
Nichtschlüssel- .............................................. 15
Primärschlüssel- ........................................... 15
Attribute............................................................... 4
-BBeziehungen ........................................................ 4
Beziehungsattribute ............................................. 4
-DDaten
Ändern von.................................................... 22
Datentypen.................................................... 18
Einfügen von ................................................. 22
Löschen von.................................................. 22
DDL.................................................. 18, 19, 21, 22
DML.................................................. 22, 24, 39, 41
-EEinrichtung der Datenbank ................................. 18
Entity ................................................................... 4
Entity-Relationship-Diagramm ........................... 50
-FFremdschlüssel.........................................5, 13, 20
Fremdschlüsselbeziehung .................................. 20
Fremdschlüsselbeziehung .............................13, 16
Fremdschlüsselbeziehung .................................. 20
Granularität........................................................ 14
-HHistorien
Abbbildung von.............................................. 11
-IIndex.................................................................. 21
Integrität .......................................................... 4, 7
benutzerdefinierte .......................................... 15
Entitäts-............................................... 5, 11, 15
referentielle........... 4, 7, 8, 13, 15, 19, 20, 22, 23
-JJoin.........................................................20, 25, 27
Equi- ....................................................... 21, 28
Equi- ............................................................. 27
Joinspalten .................................................... 27
Theta- ........................................................... 28
-KKartesisches Produkt ......................................... 27
Klausel............................................................... 32
DISTINCT...........................................24, 35, 42
GROUP BY ...................... 24, 32, 33, 34, 35, 36
HAVING .......................................24, 25, 32, 36
ORDER BY...................................24, 32, 34, 35
ORDER BY.................................................... 32
UNION ...............................................24, 36, 37
UNIQUE .............................................10, 19, 21
-LLiterale............................................................... 38
-MMengen............................................................ 5, 6
ST/HfBR/DS-EDV K9039
48
Stichwortverzeichnis
Auflösung von..................................................6
-NNichtschlüsselattribute..........................................8
Normalisierung .....................................................4
Null-Werte........................................................ 4, 8
-OOperator............................................................. 28
< 28
= 32
AND............................................................... 24
BETWEEN......................................... 24, 28, 29
EXISTS.................................................... 24, 31
IN 24, 31
IS NULL......................................................... 24
LIKE .................................................. 24, 27, 28
mathematisch ................................................ 44
NOT ........................................................ 24, 28
NOT NULL............................................... 19, 20
OR................................................................. 24
Relational- ..................................................... 24
Option
ASC............................................. 21, 24, 34, 38
CASCADE ........................................... 7, 14, 19
DESC .....................................21, 24, 34, 35, 36
NULLIFY.................................................... 7, 14
ON DELETE .................................................. 19
ON UPDATE.................................................. 19
RESTRICT................................7, 14, 19, 20, 39
-PPrimärschlüssel .........................8, 9, 10, 11, 12, 16
verknüpfter..................................................... 10
Primärschlüsselkombination ............................... 11
Problem der Postleitzahl ..................................... 16
Projektion ............................................... 25, 26, 27
-RRedundanz................................................. 4, 7, 12
Redundanzprobleme....................................... 6, 10
Relation.................................................. 4, 5, 9, 10
Relationenmodell..................................................4
Relationship .........................................................4
ST/HfBR/DS-EDV K9039
-SSchlüssel ...................................... 5, 10, 11, 12, 21
künstlicher ...............................................10, 11
zusammengesetzter ...............................5, 9, 10
Schlüsselkandidaten .............................................8
Selektion ............................................................25
Selektion ............................................................25
Selektion ............................................................26
Selektion ............................................................27
SQL
ALL................................................................24
CREATE
DATABASE ...............................................19
INDEX .......................................................21
INDEXSPACE ...........................................19
TABLE.................................................19, 20
TABLESPACE ...........................................19
DELETE........................7, 13, 15, 19, 20, 22, 39
FOREIGN KEY ........................................19, 20
Funktion.........................................................29
INDEX............................................................21
INSERT ................................. 13, 14, 22, 23, 45
POINTER.......................................................24
PRIMARY KEY.........................................19, 20
REFERENCES.........................................19, 20
SELECT................ 12, 24, 25, 26, 27, 29, 39, 45
Subquery ........................................... 30, 37, 39
UPDATE ................................ 13, 15, 19, 22, 39
WHERE ......................12, 24, 25, 26, 27, 39, 45
-TTupel ....................................................................4
-VView ................................................. 41, 42, 43, 44
CHECK OPTION............................................15
DELETE.........................................................41
INSERT .........................................................41
Löschen .........................................................44
ORDER BY-Klausel..................................41, 42
SELECT....................................... 41, 42, 43, 44
Selektion ........................................................42
Spaltenüberschriften ......................................43
WHERE ............................................. 41, 42, 43
Ziele...............................................................41
VIEW
DROP ............................................................44
Daten des Beispiels
10.
49
Daten des Beispiels
KdNr
1
2
3
4
5
6
7
8
9
10
ZiNr
100
101
102
205
210
215
Name
Müller
Paessens
Dr. Schmidt
Müller-Lüdenscheid
AKAD
IBM
FH Flensburg
Meier
Meier
Timm
Ausstattung
DBF
DBT
DBR
DBFT
DBT
DT
BuNr
1
2
3
4
5
6
7
8
9
10
KdNr
100
100
101
102
103
104
105
107
108
109
Ausstattung
DBF
DBF
DBFT
DBFT
DBR
DBR
DBT
DBT
DT
DT
PLZ
24944
24977
58093
24957
24113
25421
24921
70469
ZiNr
102
101
100
102
210
205
102
101
215
210
Art
EZ
EZ
EZ
DZ
DZ
DZ
Wohnort
Grundhof
Husby
Schleswig
Hagen
Pinneberg
Stuttgart
Flensburg
Schleswig
Hagen
Kiel
Strasse
Aukjer Straße 17
Am Polldamm 1A
Landberg 18
Am Waldesrand 3A
Am Bahnhof 10
Watsonstraße 23
Kanzleistraße 91-92
Wikingturm 243
Volmestraße 12
Ostufer 93
Relation Gast
Relation Zimmer
Dauer Relation Vermietung
12
10
3
5
5
10
2
5
1
3
Art
EZ
DZ
EZ
DZ
EZ
DZ
EZ
DZ
EZ
DZ
Ort
Flensburg
Grundhof
Hagen
Husby
Kiel
Pinneberg
Schleswig
Stuttgart
Miete Relation Preis
60
90
75
100
55
75
52
80
48
75
Relation Postleitzahl
sofern nicht in Gast enthalten
ST/HfBR/DS-EDV K9039
50
ER-Diagramm zum Hotelbeispiel
11.
ER-Diagramm zum Hotelbeispiel
Nachfolgend sehen Sie das Entity-Relationship-Diagramm für das Datenbankschema Hotel. Entitytypen werden als Rechtecke, Beziehungstypen als Rauten, Attribute werden als Ellipsen dargestellt.
Entities haben immer Attribute, Beziehungen können Attribute aufweisen. Die Beziehungen werden
entsprechend ihres Komplexitätsgrades klassifiziert, wobei man die Komplexitätsgrade 1:1, 1:N, N:1
und M:N unterscheidet.
KdNr
PLZ,
Wohnort
Name
Strasse
Gast
M
BuNr
nutzt
Dauer
N
ZiNr
Art
Zimmer
N
hat
Ausst.
1
Art
Preis
Ausstat.
Miete
Abb. 51: ER-Diagramm Hotelbeispiel
Erläuterung zu den Komplexitätsgraden:
•
Ein Gast kann viele (N) Zimmer genutzt haben.
•
Ein Zimmer kann von vielen (M) Gästen genutzt worden sein.
•
Ein Zimmer hat genau einen (1) Preis.
•
Ein Preis, z.B. 52,-- €, kann zu mehreren (M) Zimmern gehören.
ST/HfBR/DS-EDV K9039
Abbildungsverzeichnis
12.
51
Abbildungsverzeichnis
Abb. 1: Begriffe des Relationenmodells
Abb. 2: Unnormalisierte Relation Vermietung
Abb. 3: Unnormalisierte Relation Vermietung mit Mengen
Abb. 4: Auflösung von Mengen
Abb. 5: Inkonsistenzen durch Einfügeanomalien
Abb. 6: Inkonsistenzen durch Änderungsanomalien
Abb. 7: Inkonsistenzen durch Löschanomalien
Abb. 8: Optionen des DELETE-Kommando
Abb. 9: Beispieldatensätze mit NULL-Werten
Abb. 10: Beispiel Funktionale Abhängigkeiten
Abb. 11: Beispiel Voll funktionale Abhängigkeiten
Abb. 12: Beispiel Transitive Abhängigkeiten
Abb. 13: Neue Relationen Gast und Vermietung zur Erreichung der 1 NF
Abb. 14: Verbesserung der 1NF durch künstlichen Schlüssel Kundennummer (KdNr)
Abb. 15: Relation Zimmer in 2NF
Abb. 16: Neue Relation Vermietung
Abb. 17: Relation Vermietung ohne Primärschlüssel (Struktur)
Abb. 18: Relation Vermietung ohne Primärschlüssel (Daten)
Abb. 19: Primärschlüssel Buchungsnummer in der Relation Vermietung
Abb. 20: Neue Relation Preis
Abb. 21: Relation Zimmer in 3NF
Abb. 22: Verbesserte Relationen Preis und Zimmer in 3NF
Abb. 23: Primär- und Fremdschlüssel des Relationenschemas Hotel
Abb. 24: Operationentableau bei Fremdschlüsselbeziehungen
Abb. 25: Relationenschema zur Datenbank Hotel mit Primär- und Fremdschlüsseln
Abb. 26: Ergänzungen zum Relationenschema Hotel
Abb. 27: Relation Gast mit Verstoß gegen die 3NF
Abb. 28: Domänen der Datenbank HOTEL
Abb. 29: Datentypen der Attributswerte der Relationen
Abb. 30: DDL Befehle von SQL
Abb. 31: DDL Befehl CREATE TABLE von SQL
Abb. 32: Schlüsselwörter des CREATE TABLE Befehls von SQL
Abb. 33: Einrichtung der Relation Gast
Abb. 34: Einrichtung der Relation Preis
Abb. 35: Einrichtung der Relation Zimmer
Abb. 36: Einrichtung der Relation Vermietung
Abb. 37: Einrichtung der Relation Postleitzahl
Abb. 38: DDL Befehl CREATE INDEX von SQL
Abb. 39: DML Befehle von SQL
Abb. 40: Optionen des INSERT-Kommandos
Abb. 41: DML Befehl SELECT von SQL
Abb. 42: SELECT Befehlsoptionen von SQL
Abb. 43: Bedeutung der Schlüsselwörter des DML Befehls SELECT
Abb. 44: Schematische Darstellung einer Projektion
Abb. 45: Schematische Darstellung einer Selektion
Abb. 46: Wichtige Standardfunktionen von SQL
Abb. 47: DML Befehl UPDATE von SQL
Abb. 48: DML Befehl DELETE von SQL
Abb. 49: Tupel in Preis
Abb. 50: CREATE VIEW Befehl von SQL
Abb. 51: ER-Diagramm Hotelbeispiel
ST/HfBR/DS-EDV K9039
4
5
5
6
6
7
7
7
8
8
9
9
9
10
11
11
11
11
12
12
12
12
13
14
16
16
16
18
18
19
19
19
20
20
20
20
20
21
22
22
24
24
24
25
25
29
39
39
39
41
50