Skript zu Datenbanken

Skript zu Datenbanken
Prof. Dr. Karim Roger Kremer
8. September 2002
Inhaltsverzeichnis
1
Einführung
2
2
Einführung in relationale Datenbanken
2.1 Drei-Ebenen-Konzept . . . . . . . . . . . . . . . . . . . . . . . . . .
2.2 Traditionelle Datenbankmodelle . . . . . . . . . . . . . . . . . . . .
2.3 Sprachklassen des relationalen Datenmodells . . . . . . . . . . . . .
7
7
8
9
3
Structured Query Language (SQL)
3.1 Kurzeinführung . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.1.1 CREATE TABLE und DROP TABLE: Anlegen und Löschen
von Tabellenstrukturen . . . . . . . . . . . . . . . . . . . . .
3.1.2 INSERT, DELETE, UPDATE: Änderungen von Tabelleninhalten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.1.3 SELECT: Retrieval aus Tabelleninalten . . . . . . . . . . . .
3.2 Rechteverwaltung . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.2.1 Privilegienstufen und Eigentumsrechte . . . . . . . . . . . .
3.2.2 CREATE USER-, DROP USER-, GRANT-, REVOKE-Befehl
3.3 VIEW’s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.4 Datentypen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.5 SELECT-Vertiefung . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.5.1 ORDER BY-Klausel . . . . . . . . . . . . . . . . . . . . . .
3.5.2 AND, OR, NOT in der WHERE-Klausel . . . . . . . . . . .
3.5.3 Bereiche, Listen und Subqueries . . . . . . . . . . . . . . . .
3.5.4 Gruppenverarbeitung . . . . . . . . . . . . . . . . . . . . . .
3.5.5 Operationen und Funktionen . . . . . . . . . . . . . . . . . .
3.5.6 Mengenoperationen: UNION, INTERSECT, EXCEPT . . . .
3.5.7 Verbunde . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.6 Zugriffsoptimierung mit INDEX und CLUSTER . . . . . . . . . . .
3.7 INSERT, DELETE, UPDATE-Vertiefung . . . . . . . . . . . . . . .
3.8 ALTER: Datenbankobjekte ändern . . . . . . . . . . . . . . . . . . .
3.9 Multimedia-Unterstützung . . . . . . . . . . . . . . . . . . . . . . .
3.9.1 LIKE-Prädikat . . . . . . . . . . . . . . . . . . . . . . . . .
3.9.2 Volltext Datenbanken . . . . . . . . . . . . . . . . . . . . . .
3.9.3 Datentyp BLOB . . . . . . . . . . . . . . . . . . . . . . . .
12
12
1
12
15
16
17
17
18
19
20
21
21
21
22
25
26
29
31
32
34
35
36
36
36
37
INHALTSVERZEICHNIS
4
2
Normalformen und Designfragen relationaler Datenbanken
4.1 Funktionale Datenabhängigkeiten . . . . . . . . . . . .
4.2 Normalformen . . . . . . . . . . . . . . . . . . . . . . .
4.3 Regelkalkül funktionaler Datenabhängigkeiten . . . . .
4.4 Verlustfreie und abhängigkeitsbewahrende Zerlegungen .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
38
39
40
43
45
5
Internet-Datenbanken
5.1 HTML-Grundlagen und Formulare . . . . . . . . . . . . . . . . . . .
5.2 Common Gateway Interface (CGI) . . . . . . . . . . . . . . . . . . .
5.3 Beispiel 1: MySQL, Apache-Webserver und Personal Home Page (PHP)
5.4 Beispiel 2: Adabas-WebDB . . . . . . . . . . . . . . . . . . . . . . .
49
49
53
55
57
6
Datenbankprogrammierung am Beispiel Adabas
6.1 Embedded SQL . . . . . . . . . . . . . . . .
6.2 C/C++ Schnittstelle . . . . . . . . . . . . . .
6.3 Datenbankprozeduren und Trigger in SQL-PL
6.4 Parallele Transaktionen . . . . . . . . . . . .
61
61
61
64
66
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
Kapitel 1
Einführung
Eine Datenbank enthält eine Menge von Informationsdarstellungen (Daten) aus einem
abgegrenzten Themenbereich. Dabei werden die Daten vom Datenbankverwaltungssystem (DBMS -Database Management System) so verwaltet, dass sie i.w. unabhängig
von den Anwendungsprogrammen sind, die sie benutzen. Der Vorteil ist hierbei, dass
die Anwendungsprogramme i.d.R. nicht geändert werden müssen, wenn sich die Struktur der Daten ändert.
Weiterhin ermöglicht das DBMS mehreren Benutzern, simultan Daten zu suchen,
hinzuzufügen, zu löschen oder zu ändern. Die simultane Nutzung von Daten kann bei
Änderungen (Updates) in der Datenbank zu Problemen führen, die das DBMS behandeln muss.
Beispiel: In einer Datenbank sollen Sitzplatzreservierungen für Flüge verwaltet
werden, wobei das Feld F aus der Datenbank, die Anzahl gebuchter Plätze eines bestimmten Fluges enthalten soll. z1 und z2 sind lokale Variablen in den Programmen
der Benutzer 1 und 2. Der zeitliche Ablauf (ohne geeignete Maßnahmen des DBMS)
kann dann z.B. folgendermaßen aussehen, wobei die Zeit in der Tabelle von oben nach
unten läuft:
Benutzer 1/Programm 1
z1 = read(F) = 42
Interaktive Anzeige von F = 42
Benutzer 2/Programm 2
z2 = read(F) = 42
Interaktive Anzeige von F = 42
z2 = z2 + 1 = 43
write(z2,F) = 43
z1 = z1 + 1 = 43
write(z1, F) = 43
Obwohl beide Benutzer den Eindruck haben, dass ihre Buchung erfolgreich war,
hat Benutzer 1 aufgrund des falschen Wertes der lokalen Variablen z1 eigentlich keine Buchung durchgeführt. Sein Update ist verloren gegangen. Man spricht von einem
Lost-Update-Problem. Neben dem Lost-Update-Problem gibt es eine ganze Reihe weiterer Probleme, die durch Inkonsistenzen in Datenbanken hervorgerufen werden. Eine
Lösung solcher Probleme wird z.B. durch Sperren (Locks) erreicht, die den Zugriff auf
Daten für andere Programme sperren, bis der Update durchgeführt ist.
Die Grundlage aller Arbeiten mit Datenbanken bildet das Datenbankdesign. Hier3
KAPITEL 1. EINFÜHRUNG
4
bei werden die Objekte und Aktionen der Realität in ein Modell umgewandelt, das
relativ einfach in eine Datenbank abgebildet werden kann. Es folgt ein Beispiel für
schlechtes Datenbankdesign einer relationalen Datenbank. Relationale Datenbanken
bestehen aus einer oder mehreren Tabellen, die die Eigenschaften realer Objekte widerspiegeln. In einer Tabelle werden Daten über Angestellter einer Firma mit den
Projekten in denen sie arbeiten gespeichert:
Ang.Nr
4711
4711
4712
4712
Name
Meier
Meier
Schmidt
Schmidt
Ort
Friedberg
Friedberg
Gießen
Gießen
Gehalt
6100
6100
5200
5200
P.Nr
1
2
2
3
Projektname
DB-Design
DB-Realisierung
DB-Realisierung
DB-Test
Budget
15000
10000
10000
5000
Wie aus der Tabelle ersichtlich ist, kann ein Angestellter in mehreren Projekten
arbeiten, d.h. Name, Ort und Gehalt werden zu jedem Projekt gespeichert, in dem der
Angestellte arbeitet, obwohl sie eigentlich nur von der AngestelltenNr abhängen. Dies
ist oder Wiederholung überflüssiger Informationen. Man nennt dies eine Redundanz.
Sie bewirkt zumindesten eine Vergeudung von Speicherplatz.
Noch deutlicher wird das Problem, das durch die Redundanz entsteht, wenn man
annimmt, dass ein Angestellter z.B. für längere Zeit krank ist. Er ist dann u.U. an
keinem Projekt mehr beteiligt. Die Informationen über seine Projekte zu löschen heißt
aber, die übrigen Informationen AngestelltenNr, Name, Ort und Gehalt über den Angestellten ebenfalls aus der Datenbank zu entfernen. Dies ist sicher ungewollt, wenn der
Angestellte in die Firma zurückkehrt. Dieses Problem nennt sich Lösch- oder DeleteAnomalie.
Anomalien treten aber auch beim Einfügen (Insert) oder Ändern (Update) auf. Sie
werden dann als Insert- oder Update-Anomalien bezeichnet. Will man einen neuen
Angestellten einfügen, so muss man ihm ein Projekt zuweisen. Will man das Gehalt
von Angestellten Meier ändern, so muss man das für alle seine Projekte tun, obwohl
vom Projekt unabhängig ist.
Um Redundanzen und Anomalien zu vermeiden, geht man konstruktiv und analytisch vor. Ein konstruktives Verfahren besteht darin, sich über die Einheiten der realen Welt (Entity) und deren Beziehungen (Relationship) untereinander klar zu werden.
Im obigen Beispiel haben wir es mit Angestellten zu tun, die an Projekten beteiligt
sind. Solche Zusammenhänge hält man grafisch in Entity-Relationship-Diagrammen
(ERD’s) fest. Entitäten werden durch Rechtecke, Attribute durch Ovale und Beziehungen durch Rauten dargestellt. Um anzuzeigen, dass es zu einem Projekt mehrere
Angestellte gibt, wird an die Beziehung ein m bei den Angestellten eingezeichnet. Genauso wird an die Beziehung zu den Projekten ein n eingezeichnet, um anzuzeigen,
dass ein Angestellter in mehreren Projekten arbeiten kann. Man spricht auch von einer
Beziehung mit many-to-many-Stelligkeit. Dürfte ein Angestellter höchstens in einem
Projekt arbeiten, so müsste zum Projekt hin eine 1 stehen. Das ERD für das obige
Beispiel sieht also folgendermaßen aus:
KAPITEL 1. EINFÜHRUNG
Ang.Nr
5
Name
Ort
Gehalt
Angestellter
m
beteiligt
n
Projekt
P.Nr
Projektname
Budget
Der Erfolg dieser ERD-Methode besteht darin, dass man auf natürliche Weise zu
einer Zerlegung der obigen Tabelle in drei Tabellen kommt, die nun keine Redundanz
mehr aufweisen. Die Umsetzung eines ERD in Tabellen geschieht wie folgt: Jede
Entität wird in eine Tabelle umgewandelt, wobei die Attribute der Entität die Spaltenüberschriften der Tabelle darstellen. Eine many-to-many-Beziehung wird in ebenfalls
in eine Tabelle umgewandelt, wobei diese Tabelle aus eindeutigen Kennzeichen für
die im Zusammenhang stehenden Einträge der anderen Tabellen besteht. Solche eindeutigen Kennzeichen nennt man auch Schlüssel. Eine exakte Definition des Begriffs
Schlüssel folgt noch. Die Tabellen für das obige Beispiel sehen wie folgt aus:
Angestellter:
Ang.Nr
4711
4712
Name
Meier
Schmidt
Ort
Friedberg
Gießen
Gehalt
6100
5200
Projekt:
P.Nr
1
2
3
Beteiligung:
Projektname
DB-Design
DB-Realisierung
DB-Test
Budget
15000
10000
5000
KAPITEL 1. EINFÜHRUNG
6
Ang.Nr
4711
4711
4712
4712
P.Nr
1
2
2
3
Beziehungen werden also im relationalen Datenmodell wie Entitäten in Tabellen
abgebildet. Dies ist eine Besonderheit des relationalen Modells. Das Datenbanksystem
kann also zwischen einer Entität und einer Beziehung nicht unterscheiden, sondern nur
der Anwender. Jedoch muss nicht jede Beziehung in eine separate Tabelle abgebildet
werden. Betrachten wir dazu das Beispiel einer Bibliothek mit den Entitäten Leser und
Buch und der Beziehung Ausleihen:
InventarNr
Autor
Titel
Verlag
Jahr
Buch
m
Ausleihen
1
Leser
LeserNr
Name
Vorname
Strasse
Hausnr
PLZ
Ort
Hier ist jedes Buch höchstens von einem Leser ausgeliehen. Also kann wahlweise
die LeserNr zur Tabelle Buch hinzugefügt werden oder eine dritte Tabelle aus LeserNr
und BuchNr gebildet werden.
Weiterhin ist klar, dass man den Zusammenhang zwischen Angestellten und Projekten aus den drei Spalttabellen wieder herstellen kann. Wenn man die Schlüssel der
Tabelle Beteiligung benutzt, um korrespondierende Zeilen aus den Tabellen Angestellter und Projekt zusammenzufügen. Eine solche Operationen nennt man Verbund oder
Join. Ein Verbund ist eine relativ zeitaufwendige Operation, da jede Zeile der beteiligten Relationen untersucht werden muss.
Die analytische Methode der Normalisierung von Relationen geht von durch den
Anwender erkannten funktionalen Abhängigkeiten der Attribute untereinander aus. So
bestimmt in der Ausgangstabelle die Projektnummer P.Nr eindeutig den Projektnamen
KAPITEL 1. EINFÜHRUNG
7
und das Budget und die Angestelltennummer Ang.Nr eindeutig den Namen, den Ort
und das Gehalt. Schlüssel der Gesamtrelation sind die beiden Attribute Ang.Nr und
P.Nr. Jedoch ist Name, Ort und Gehalt nicht funktional abhängig von P.Nr. Genauso ist Projektname und Budget nicht funktional abhängig von Ang.Nr. Funktionale Abhängigkeiten von Teilschlüsseln z.B. von Ang.Nr oder von P.Nr führen also zu
Redundanzen. Deshalb fordert man in der 2. Normalform, das im Relationenschema
keine funktionalen Abhängigkeiten von Teilschlüsseln vorkommen dürfen. Neben dieser Forderung gibt es natürlich auch Konstruktionsverfahren, die Tabellen so zerlegen,
dass die Spaltprodukte in der jeweiligen Normalform sind. Wir kommen auf die unterschiedlichen Normalformen und die Konstruktionsverfahren später noch genauer zu
sprechen.
In der Praxis werden manchmal Redundanzen in Datenbanken in Kauf genommen,
z.B. wenn häufig Verbundoperationen auf großen Relationen notwendig sind, die dann
sehr zeitaufwendig werden. Dann ist es ggfs. besser, die häufig angefragte Sicht der
Daten mit ihren Redundanzen zu speichern. In jedem Fall muss der Anwender sich
aber über die Redundanzen im Klaren sein, um korrekte Anfragen und Programme
verfassen zu können.
Kapitel 2
Einführung in relationale
Datenbanken
2.1
Drei-Ebenen-Konzept
Ein grundlegendes Architekturkonzept von Datenbanksystemen ist das auf drei verschiedenen Abstraktionsebenen angesiedelte Ebenen-Konzept. Dieses Konzept betrifft
die Datenunabhängigkeit in physischer und logischer Hinsicht. Folgende Ebenen werden unterschieden:
Interne Ebene: Physische Datenorganisation und Datenverwaltung z.B. Speicherform: sequentiell, indexsequentiell, wahlfrei, Hash und Suchmethoden. Datenbankadministrator definiert interne Ebene.
Konzeptionelle Ebene: Logische Gesamtsicht der Daten, Abbildung der realen
Welt auf ein Datenmodell mit Abstraktion von den Dateninhalten z.B. Nr und
nicht 4711 betrachtet. Unternehmensadministrator definiert konzeptionelle Ebene.
Externe Ebene: Logische Einzelsicht der Daten eines Anwenders mit Abfragen
und Verwaltung von Zugriffsrechten. Anwendungsadministrator erstellt externe
Ebene.
Die Hauptaufgaben des Datenbankverwaltungssystems bestehen in der Realisierung
der Unabhängigkeit zwischen physischen und logischen Daten und der Sicherung der
Integrität der Daten. Zur Sicherung der Integrität stellt der Unternehmensadministrator semantische Bedingungen (Integritätsbedingungen) auf, die von den Daten eingehalten werden müssen. Beispiele für semantische Bedingungen sind, daß das Gehalt
eines Mitarbeiters eine positive Zahl ist und i.d.R. nicht schrumpfen darf. Diese semantischen Bedingungen überwacht das Datenbanksystem automatisch, d.h. daß nur
Änderungen von Daten zugelassen werden, die die semantischen Bedingungen erfüllen. Eine Datenbank, die alle Integritätsbedingungen erfüllt, nennt man konsistent. Das
Datenbankverwaltungssystem sichert also die Konsistenz der Datenbank.
Weitere Aufgaben des Datenbankverwaltungssystems sind die Realisierung des
Datenschutz im Mehrbenutzerbetrieb und der Datensicherung, z.B. werden im Falle von Soft- oder Hardware-Problemen nicht abgeschlossene Transaktionen in einem
Logbuch protokolliert, um sie nach dem Neustart der Datenbank zu vervollständigen.
8
KAPITEL 2. EINFÜHRUNG IN RELATIONALE DATENBANKEN
2.2
9
Traditionelle Datenbankmodelle
Wie wir bereits in der Einführung gesehen haben, werden im relationalen Datenmodell
Beziehungen zwischen Entities genauso wie die Entities selber in Relationen festgehalten. Bei relationalen Datenbanken weiß also nur der Datenbankanwender, was eine
Beziehung und was eine Entität ist.
Hierin unterscheiden sich die traditionellen Datenbankmodelle das hierarchische
Modell und das Netzwerkmodell vom relationalen Modell. In diesen traditionellen
Modellen gibt es explizite Konstrukte für Beziehungen. Während die Suche im relationalen Modell ohne Kenntnis der Ordnung der Daten untereinander erfolgen kann, muss
der Anwender bei der Suche im hierarchischen Modell und im Netzwerkmodell über
die verschiedenen Entitäten hinweg navigieren. Dazu sind die Beziehungen als Zeigerstrukturen implementiert, über die man von einer Entität zu einer anderen gelangen
kann.
Man kann grundsätzlich folgende Unterschiede der traditionellen Modelle gegenüber dem relationalen Ansatz feststellen:
Bei traditionellen Modellen ist die Suche von Daten mit Navigation verbunden.
Die Suche von Daten in traditionellen Modellen erfolgt gezielt durch den Anwender, d.h. es müssen nicht wie im relationalen Modell bei einer Anfrage
grundsätzlich alle Daten der beteiligten Relationen durchsucht werden.
Gerade der zweite Unterschied führt bei den traditionellen Modellen u.U. zu einem
Geschwindigkeitsvorteil. Dies war gleichzeitig lange Zeit das Hauptproblem der relationalen Datenbanken. Heute fällt jedoch dieser Nachteil aufgrund der besseren
Hardware-Voraussetzungen nicht mehr so stark ins Gewicht und wird durch den Vorteil
der einfacheren Nutzbarkeit der relationalen Datenbanken mehr als ausgeglichen. Die
einfachere Nutzung hat auch zur Durchsetzung des relationalen Modells geführt.
Allerdings gibt es auch heute noch Datenbanken, die mit traditionellen Modellen
arbeiten. Deswegen soll zumindest der Unterschied zwischen dem hierarchischen und
dem Netzwerkmodell erklärt werden. Im hierarchischen Modell sind die Entitäten im
ERD in einem Hierarchiebaum angeordnet. Z.B. kann das Angebot von Kursen einer
Schule wie folgt strukturiert werden:
Kursus
1
1
n1
n2
Voraussetzung
Angebot
1
n3
Lehrer
1
n4
Schüler
Wie man sieht, handelt es sich bei den Beziehungen immer um 1 zu n Beziehungen,
d.h. Eltern-Kind-Beziehungen. Beim hierarchischen Modell kann eine Entität also
nicht Kind von zwei oder mehr Eltern-Entitäten sein. Solche Situationen kommen aber
in der Realität vor, z.B. bei der Zuordnung von Mitarbeitern und Projekten. Hierbei soll
ein Mitarbeiter wieder in mehreren Projekten arbeiten können und ein Projekt wieder
mehrere Mitarbeiter haben können.
KAPITEL 2. EINFÜHRUNG IN RELATIONALE DATENBANKEN
Mitarbeiter
10
Projekte
1
1
n1
n2
Projektmitarbeit
Um eine solche Situation im hierarchischen Modell abzubilden, müssen zwei verschiedene Entitäts-Bäume eingeführt werden. Dabei wird der Entitäts-Typ Projektmitarbeit zweimal geführt, d.h. es muss eine gezielte Redundanz eingeführt werden. Dies
führt wieder zu unerwünschten Anomalien.
Mitarbeiter
Projekte
1
1
n1
n2
Projektmitarbeit
Projektmitarbeit
Das Netzwerkmodell kennt diese Beschränkungen nicht, d.h. es können 1 zu n
Beziehungen beliebig miteinander verknüpft werden. Das Netzwerkmodell wurde
bei einer Datenbank-Konferenz veröffentlich, weshalb es auch häufig als CODASYLModell bezeichnet wird (Conference on Data System Languages).
2.3
Sprachklassen des relationalen Datenmodells
Im relationalen Modell gibt es drei verschiedene Sprachklassen:
1. Im Relationenkalkül wird die bei einer Anfrage neu zu bildende Relation durch
Prädikatenlogik beschrieben. Es handelt sich also um einen deskriptiven Sprachansatz. Sprachen des Relationenkalküls sind mächtig aber in der Anwendung gewöhnungsbedürftig und erfordern i.a. langwierige Übersetzungsvorgänge.
2. Bei abbildungsorientierten Sprachen werden Anfragen graphisch durch Tabelleneditoren unterstützt. Dieser Sprachansatz ist sowohl deskriptiv als auch prozedural. Der Vorteil dieses Sprachansatzes liegt in der einfachen Anwendung.
Allerdings sind die Anfrageformulare i.d.R. wenig flexibel.
3. Bei der Relationenalgebra werden Relationen bei Anfragen mit Hilfe von Mengenoperationen in andere Relationen umgewandelt. Dieser Sprachansatz ist also
prozedural. Die Sprache SQL gehört zu dieser Sprachklasse. Die Sprachen der
Relationenalgebra zeichnen sich durch Flexibilität, Effizienz und einfache Anwendung aus. Deswegen sind sie wohl bei relationen Datenbanken am weitesten
verbreitet.
Eine Sprache des Relationenkalküls ist RTK (Relationen-Tupel-Kalkül). Sie zeichnet
sich durch ein hohes Maß an mathematischer Abstraktion aus. Zunächst sollen folgende Schreibkonventionen gelten:
KAPITEL 2. EINFÜHRUNG IN RELATIONALE DATENBANKEN
11
R, S seien Relationsnamen
t, u seien Tupelvariablen
A, B seien Attributnamen
’a’, ’b’ seien Attributwerte (Konstanten)
Dann wird eine Anfrage in RTK durch eine mathematische Formel beschrieben. Eine
Formel wird durch folgende drei Definitionen beschrieben:
Atomare Formeln: R(t) ist die Protokollformel. Sie besagt, daß die freie Tupelva
riable t eine Zeile der Relation R annehmen kann: t R. t[A] u[A] ist die Vergleichsformel. Sie besagt, daß die freie Tupelvariable t bzgl. des Attributes A
kleiner oder gleich der freien Tupelvariable u ist. Anstatt kann natürlich jeder
andere Vergleichsoperator verwendet werden.
Logische Verknüpfung von Formeln: Sind Formeln so sind folgende Konstrukte ebenfalls Formeln: und und Quantifikation:
Ist
eine Formel, so sind ebenfalls folgende Konstrukte Formeln:
und . Man nennt s in diesen Fällen eine gebundene Tupelvariable,
d.h. im ersten Fall muss ein s existieren das erfüllt und im zweiten Fall müssen
alle s die Formel erfüllen.
Über Ausdrücken werden neue Relationen mit Hilfe von Formeln erzeugt. Die Projektion auf die Untermenge A,B der Attribute einer Relation R mit den Attributen A,B,C
und Dkann
"!$#&%(folgenden
' )*+,Ausdruck
*+ %- .0/erzeugt
!1*2- .0werden:
/ %- 3 /+!4*2- 3 /65
durch
Die neuen Tupel (Tabellenzeilen) t werden aus den Attributwerten der freien Tupelvariablen u gebildet, die mit der Bedingung über t gebunden wird.
Die Selektion
Tupeln
R kann z.B. wie folgt aussehen:
"!$von
#&%(' ,
% %aus
- @A/der
!CBRelation
D6B&5
798;:(<>=?<
Ein Problem von RTK ist die Tatsache, daß sehr große unerwünschte Relationen
als Abfrageergebnis entstehen können, z.B. die Allrelation aus allen möglichen Werten
der Datentypen:
#&%(' E%
G%5
F
Das Sichere RTK (SRTK) verhindert solche Anfragen; es braucht jedoch noch längere Übersetzungszeiten als RTK für diese Prüfung.
Die Elemente der Relationenalgebra basieren auf Mengenoperationen wie Vereinigung, Durchschnitt, Selektion, Projektion und Verbund.
5
#JI &K & ML ! O!P
N wird die Selektion
Wenn H ein Vergleichsoperator ist, also H
folgendermaßen
(Q "!Rdefiniert:
#M% S' %- .0/ B>D6B&5
7
<= <
H
Es werden also genau die Zeilen der Relation selektiert, bei denen das Attribut A
den Wert ’a’ hat.
Die
auf5 das Attribut mit dem Namen A wird wie folgt definiert:
Projektion
"!$#&%- .Uvon
/6' % R T
Der Verbund ist eine Operation über zwei Relationen R und S, wobei die Werte
gemeinsamer
4VXWZY[!$Attribute
#&% .C\ der beiden
.`_a 3 Relationen
@0\
@b)(übereinstimmen
' %+- .C\
.`_ 3Zmüssen:
/ %- 3 @0\
@bA/
&]^]^]>
&]^]>]^
&]^]^]>
M]>]^]^
Yc5
KAPITEL 2. EINFÜHRUNG IN RELATIONALE DATENBANKEN
12
Für die Optimierung von Anfragen sind u.a. folgende Rechenregeln in Form von
Sätzen nützlich. Sie müssen bei der Implementation von relationalen Datenbanksystemen berücksichtigt werden:
1.
2.
7 : < = < GdY""! 7 : < = < (d 7 : < = < XY"
7 : < = < 7 : <^eX< )f"! 7 : <^eX< 7 : < = < f
3. Wenn XY,
Z Untermengen der Attributmenge X einer Relation R sind, gilt:
T g+jki . Gilt zusätzlich lRmGn so ist T g T i X "! T g und T i 7
7 g T i o .
4.
5.
6.
Thg +T i f"!
g f "!
4VW p!4
Reflexivität
4VWZYq!rYsVXW)
Kommutativität
tVXW Y"VXWAu4!ttVXWvXY[VWCuA
Assoziativität
7. Wenn R über der Attributmenge
der
!RzRX!(und
{|RSVXüber
WZYE!r
~Attributmenge
}Y
! Y definierte
!({|pVXW
y
w
x
n
w
n
Relationen
sind,
gilt:
und
Y[!t Y
x
8. Wenn R über der Attributmenge X, S über der Attributmenge
T über der
@
!({ Y 7und
8 rVW Y"c!
€
m
w
Attributmenge
Z
definierte
Relationen
sind
gilt:
798 FVXWGY und @ m‚w‚xƒn !({ 7„ rVW Y"…! 7„ X FVW 7„ XY" und
d
!
† d !
† d 2!({
rVW Y"c!
†
TŒ
myw
n‡‰ˆ
wŠx
n ‰ ‹
n~x
w
T+Œ T+ X)VXW T+Ž XY" und n ! l ! {tVXWcY…d
uA"!$X4VXWZY"d[tVXWCuA
Kapitel 3
Structured Query Language
(SQL)
In diesem Kapitel wird ein Überblick über den Sprachstandard SQL2 gegeben. Leider weichen die einzelnen Implementationen von Datenbanksystemen, wie MySQL,
Adabas, Informix und Oracle teilweise erheblich von diesem SQL2-Standard ab. Dies
kann bedeuten, daß bestimmte Sprachelemente zusätzlich zum SQL2-Standard verfügbar sind, aber auch, daß bestimmte SQL2-Sprachelemente ganz fehlen. Die vollständige Syntax für SQL 2 (offiziell SQL-92) umfasst etwa 700 Seiten! Daher möchte ich
zunächst eine Kurzeinführung mit den wichtigsten Sprachelementen geben.
3.1
Kurzeinführung
Die Sprache SQL besteht aus zwei Teilen: Der Data Definition Language (DDL) mit
der Datenbankobjekte, wie Tabellen, Views und Benutzer verwaltet werden können und
der Data Manipulation Language (DML) zur Abfrage sowie zum Einfügen, Löschen
und Ändern von Daten.
3.1.1
CREATE TABLE und DROP TABLE: Anlegen und Löschen
von Tabellenstrukturen
Die grundlegende Syntax zum Anlegen einer Tabelle ist:
CREATE TABLE Tabellenname (Liste).
In Liste stehen die Attributnamen mit ihren Wertebereichen. Wertebereiche sind u.a.
CHAR, INT, SMALLINT, NUMBER, DEC, FLOAT und DATE. Ein Beispiel zum
Anlegen einer Tabelle ist:
CREATE TABLE Test(A INT, B DEC(5,2), C CHAR(80))
Alternativ können ähnlich zur Definition von Datentypen in höheren Programmiersprachen sog. Domains angelegt werden. Hierdurch ist gewährleistet, dass eine Änderung
z.B. in der Genauigkeit eines DEC-Datentyps auf alle Tabellen durchgreift, die die
Domain einbinden:
13
KAPITEL 3. STRUCTURED QUERY LANGUAGE (SQL)
14
CREATE DOMAIN BType AS DEC(5,2)
CREATE DOMAIN CType AS CHAR(80)
CREATE TABLE Test(A INT, B BType, C CType)
Weiterhin werden bei der Definition von Tabellen Integritätsbedingungen definiert, die
das DBMS zu überwachen hat. Dazu gehören Schlüsseldefinitionen in der Form:
Primärschlüssel:
PRIMARY KEY(Attributliste)
Sekundärschlüssel:
UNIQUE(Attributliste)
Fremdschlüssel:
FOREIGN KEY(Attributliste)
[REFERENCES Tabellenname[(Attributliste)]]
Die eckigen Klammern [] sind nicht Teil der Syntax, sondern bedeuten Optionalität.
Ein Fremdschlüssel ist hierbei ein Primärschlüssel aus einer anderen Tabelle. Beim
Anlegen oder Ändern einer Zeile der Tabelle überprüft das DBMS automatisch, ob eine
Zeile in der korrespondierenden Tabelle enthalten ist. Wenn das nicht der Fall ist, wird
die Aktion mit einer Fehlermeldung abgelehnt. Hierzu bemühen wir noch einmal das
etwas veränderte Beispiel einer Bibliothek mit folgendem ERD:
InventarNr
Autor
Titel
Erscheinungsdatum
Verlag
Buch
m
Ausleihen
Rückgabedatum
1
Leser
LeserNr
Name
Vorname
Strasse
Hausnr
PLZ
Ort
KAPITEL 3. STRUCTURED QUERY LANGUAGE (SQL)
15
Wir haben es mit einer attributierten Beziehung Ausleihen mit einem Rückgabedatum zu tun. Wir entscheiden uns auch deshalb für die Realisation in drei Tabellen:
Leser, Buch und Ausleihe. Die Relation Ausleihe besteht aus drei Attributen: LeserNr,
BuchNr und Rückgabedatum. Der Primärschlüssel von Ausleihe ist BuchNr. Weiterhin ist LeserNr Fremdschlüssel aus der Relation Leser und BuchNr Fremdschlüssel aus
der Relation Buch.
Damit kein nicht inventarisiertes Buch ohne BuchNr in der Relation Buch ausgeliehen wird und damit an keinen nicht registrierten Leser ohne LeserNr in der Relation
Leser Bücher ausgeliehen werden, werden vom DBMS die Fremdschlüssel bei folgender Definition von Ausleihe überwacht:
CREATE TABLE Ausleihe (
BuchNr
INT NOT NULL,
LeserNr
INT NOT NULL,
Rückgabedatum DATE NOT NULL,
PRIMARY KEY (BuchNr),
FOREIGN KEY (BuchNr) REFERENCES Buch(InventarNr),
FOREIGN KEY (LeserNr) REFERENCES Leser
)
Der Zusatz NOT NULL zwingt den Anwender auf jeden Fall in diese Felder Werte
einzugeben, ansonsten wird die Einfüge- oder Änder-Aktion vom DBMS abgelehnt.
Dieser Zusatz ist natürlich bei Schlüsselattributen immer sinnvoll.
Die Definition der Tabellen Buch und Leser enthält natürlich keine FremdschlüsselDefinitionen. Eine gegenseitige Fremdschlüssel-Definition in zwei Tabellen führt dazu,
dass in keiner der Tabellen mehr eine Zeile eingefügt werden kann.
CREATE TABLE Buch (
InventarNr
INT NOT NULL,
Autor
CHAR(80) NOT NULL,
Titel
CHAR(80) NOT NULL,
Verlag
CHAR(80) NOT NULL,
Erscheinungsdatum DATE NOT NULL,
PRIMARY KEY (InventarNr)
)
Die Definition von Leser lautet:
CREATE TABLE Leser (
LeserNr INT NOT NULL,
Name
CHAR(80) NOT NULL,
Vorname CHAR(80) NOT NULL,
Strasse CHAR(80) NOT NULL,
HausNr DEC(4)
NOT NULL,
PLZ
DEC(5)
DEFAULT 61169,
Ort
CHAR(80) DEFAULT ’Friedberg’,
PRIMARY KEY (LeserNr)
)
DEC(4) definiert hierbei eine höchstens vierstellige ganze Zahl ggfs. mit negativem
Vorzeichen. Die DEFAULT Anweisungen besetzen die Werte von PLZ mit einer Zahl
KAPITEL 3. STRUCTURED QUERY LANGUAGE (SQL)
16
und Ort mit einer Zeichenkette vor, falls kein Wert explizit eingetragen wird. Diese
Anweisungen implizieren die Wirkung der NOT NULL-Anweisung.
Der Befehl zum Löschen einer Tabelle lautet:
DROP TABLE Tabellenname
Hierbei wird nicht nur der Tabelleninhalt sondern auch die Tabellenstruktur gelöscht.
3.1.2
INSERT, DELETE, UPDATE: Änderungen von Tabelleninhalten
Das Einfügen von Tabellenzeilen geschieht mit folgender Syntax:
INSERT INTO Tabellenname [(Attributliste)]
VALUES(Datenliste)
Die Attributliste hinter dem Tabellennamen ist optional. Wenn sie fehlt, müssen alle
Daten in der Reihenfolge der Definition der Tabelle mit CREATE TABLE angegeben
werden. Gegeben sei folgende Tabellendefinition:
CREATE TABLE Mitarbeiter (
Nr
SMALLINT NOT NULL,
Nachname
CHAR(25) NOT NULL,
Vorname
CHAR(25) NOT NULL,
Vertragsart CHAR(25) DEFAULT ’Angestellter’,
Geburtstag DATE,
Anstellung DATE NOT NULL,
Gehalt
DEC(7,2),
PRIMARY KEY (Nr))
Dann sind folgende Einfüge-Operationen möglich:
INSERT INTO Mitarbeiter VALUES
(1, ’Nolte’, ’Fritz’,
’Arbeiter’, NULL, ’19991001’,5000.10)
INSERT INTO Mitarbeiter VALUES
(2, ’Mund’, ’Martin’,
’freier Mitarbeiter’, ’19580417’,
’19991001’, 12345.67)
INSERT INTO Mitarbeiter
(Nr, Vorname, Nachname, Anstellung)
VALUES (3, ’Ralf’, ’Schiemann’, ’19880301’, 6000)
Die dritte Einfüge-Operation zeigt, dass mit der Angabe einer Attributliste die Attributwerte fehlen dürfen, die nicht mit der Klausel NOT NULL in der Definition belegt
sind. Außerdem kann die Reihenfolge der Attributwerte anders als in der Tabellendefinition gewählt sein (hier Vorname vor Nachname). Nach diesen Einfüge-Operationen
ergibt sich folgender Tabelleninhalt:
Nr
1
2
3
Nachname
Nolte
Mund
Schiemann
Vorname
Fritz
Martin
Ralf
Vertragsart
Arbeiter
freier Mitarbeiter
Angestellter
Geburtstag
19580417
Anstellung
19991001
19991001
19880301
Gehalt
5000.10
12345.67
6000
KAPITEL 3. STRUCTURED QUERY LANGUAGE (SQL)
17
Das Löschen von Tabellenzeilen (nicht Löschen der Tabellenstruktur) geschieht mit
folgender Syntax:
DELETE FROM Tabellenname [WHERE Bedingungen]
Die WHERE-Klausel ist hier optional. Mit ihr werden bestimmte Tabellenzeilen, die
gelöscht werden sollen, aus der Tabelle selektiert. Deswegen löscht folgende Anweisung alle Tabellenzeilen, jedoch nicht die Tabellenstruktur:
DELETE FROM Mitarbeiter
Will man nur den Mitarbeiter Nolte löschen, so ist dies z.B. mit folgender Anweisung
möglich:
DELETE FROM Mitarbeiter WHERE Nr = 1
Die möglichen Bedingungen in der WHERE-Klausel werden noch genauer in Zusammenhang mit dem Retrieval mit Hilfe von SELECT betrachtet.
Das Ändern von Tabellenzeilen (nicht Ändern der Tabellenstruktur) wird mit folgender Syntax möglich:
UPDATE Tabellenname
SET Attributname = Ausdruck
[, Attributname = Ausdruck...]
[WHERE Bedingungen]
Hierbei können beliebig viele Attributwerte geändert werden, es muss jedoch mindestens einer geändert werden. Die WHERE-Klausel ist wieder optional. Fehlt Sie, so
werden alle Zeilen mit dem angegebenen Attributwert in der jeweiligen Spalte des Attributnamens versehen. Folgende Anweisung bewirkt eine Gehaltserhöhung für alle
Mitarbeiter um 5 Prozentpunkte:
UPDATE Mitarbeiter
SET Gehalt=Gehalt*1.05
Soll nur der Mitarbeiter 1 eine Gehaltserhöhung bekommen, so wählt man folgende
Anweisung:
UPDATE Mitarbeiter
SET Gehalt=Gehalt*1.05
WHERE Nr = 1
3.1.3
SELECT: Retrieval aus Tabelleninalten
Die Suche (Retrieval) von Daten aus Tabellen wird mit der SELECT-Anweisung durchgeführt. Der grundlegende Syntax-Aufbau ist dabei:
SELECT Attributliste
FROM Tabellenliste
[WHERE Bedingungen]
Die einfachste Anweisung ohne die optionale WHERE-Klausel zeigt den gesamten
Tabelleninhalt:
KAPITEL 3. STRUCTURED QUERY LANGUAGE (SQL)
18
SELECT *
FROM Mitarbeiter
Hierbei steht * als Wildcard für alle Attribute. Will man nur bestimmte Attribute oder
Spalten der Tabelle anzeigen, so entspricht dies mathematisch einer Projektion. Das
folgende Beispiel zeigt dies:
SELECT Name, Gehalt
FROM Mitarbeiter
Durch diese Anweisung erscheinen nur die Spalten Name und Gehalt der Tabelle Mitarbeiter. Die WHERE-Klausel schränkt die auszugebenden Zeilen ein. Will man z.B.
nur Mitarbeiter mit einem Gehalt von mehr als 5500 ansehen, so wird dies durch folgende Abfrage erreicht:
SELECT * FROM Mitarbeiter
WHERE Gehalt > 5500
Interessieren einen hierbei nicht alle Spalten sondern nur Name, Vertragsart und Gehalt, so setzt man folgende Abfrage ein:
SELECT Name,Vertragsart, Gehalt
FROM Mitarbeiter
WHERE Gehalt > 5500
Man kann also festhalten: Mit der Attributliste in der SELECT-Klausel wird projeziert,
d.h. Spalten ausgewählt. Mit den Bedingungen in der WHERE-Klausel wird selektiert,
d.h. Zeilen ausgewählt.
3.2
3.2.1
Rechteverwaltung
Privilegienstufen und Eigentumsrechte
In Standard-SQL-Datenbanksystemen gibt es drei verschiedene Privilegienstufen für
Nutzer:
CONNECT: Dies ist die Stufe für einen Benutzer, der überwiegend Anfragen an
das Datenbanksystem macht. Er hat die Erlaubnis sich beim Datenbanksystem
anzumelden und Views zu erzeugen. Die Zugriffsmöglichkeiten auf die Datenbankobjekte anderer Benutzer wird von diesen definiert. Eigene Tabellen dürfen
nicht angelegt werden.
RESOURCE: Dies ist die normale Privilegienstufe für einen Benutzer, der eigene Datenbankobjekte, wie Tabellen, Prozeduren und Indizes erzeugen darf.
DBA: Dies ist die höchste Privilegienstufe für den Datenbankadministrator, der
alle Aufgaben der Systemverwaltung wahrnimmt. Er kann z.B. Benutzer zum
Datenbanksystem zulassen, Rechte an Objekten geben oder verwehren und das
Datenbanksystem z.B. zur Datensicherung offline schalten.
Der Erzeuger eines Datenbankobjekts (z.B. einer Tabelle) besitzt automatisch alle Rechte an dem Objekt und kann die Rechte an andere Benutzer weitergeben. Es kann anderen Benutzern sogar erlaubt werden, zugeteilte Rechte ihrerseits wieder an weitere
Benutzer weiterzugeben.
KAPITEL 3. STRUCTURED QUERY LANGUAGE (SQL)
3.2.2
19
CREATE USER-, DROP USER-, GRANT-, REVOKE-Befehl
Zum Anlegen eines Benutzers dient der Befehl CREATE USER. Natürlich darf nur ein
Benutzer mit DBA-Privileg einen anderen Benutzer anlegen.
CREATE USER Schulz IDENTIFIED BY k2rtaedjods11
Der Benutzer Schulz erhält die Benutzerklasse RESOURCE und das Passwort k2rtaedjods11.
Damit erhält er das Recht, private Objekte zu definieren und anderen Benutzern Privilegien für diese Objekte zu erteilen.
Ein DBA-Benutzer kann dem Benutzer Schulz nach dem Anlegen eine andere
Privilegienstufe zuweisen. Mit REVOKE wird eine Privilegienstufe entzogen; mit
GRANT eine Stufe vergeben:
REVOKE RESOURCE FROM Schulz
GRANT DBA TO Schulz
Zum Ändern eines Benutzerprofils z.B. des Passworts steht der Befehl ALTER USER
zur Verfügung:
ALTER USER Schulz IDENTIFIED BY r2d2k2rxyungelöst
Der Befehl DROP USER löscht einen Benutzer, wenn er keine Datenbankobjekte mehr
besitzt. Will man einen Benutzer mit seinen Datenbankobjekten zusammen löschen, so
wählt man die Zusatzoption CASCADE:
DROP USER Schulz CASCADE
Neben Vergabe und Entzug von Benutzerprivilegien, können mit GRANT und REVOKE Rechte an Datenbankobjekten verwaltet werden. Dabei sind die am häufigsten
vergebenen Rechte SELECT, INSERT, DELETE und UPDATE. Es folgen einige Beispiele zur Benutzung von GRANT und REVOKE mit der Tabelle Mitarbeiter, hierbei
wird jeder Befehl für sich alleine betrachtet. Es handelt sich also nicht um eine Sequenz
von Befehlen:
GRANT SELECT
ON Mitarbeiter
TO Schulz
Schulz darf den SELECT-Befehl auf alle Attrbute von Mitarbeiter ausführen.
GRANT SELECT(Nachname, Vorname)
ON Mitarbeiter
TO Schulz
Schulz darf nur die in der Liste angegebenen Attribute mit SELECT auswerten.
GRANT SELECT, INSERT, DELETE
ON Mitarbeiter
TO Schulz
Schulz darf die Befehle SELECT, INSERT und DELETE uneingeschränkt auf Mitarbeiter ausführen.
KAPITEL 3. STRUCTURED QUERY LANGUAGE (SQL)
20
GRANT UPDATE
ON Mitarbeiter
TO Schulz
WITH GRANT OPTION
Schulz darf UPDATE auf Mitarbeiter uneingeschränkt ausführen und UPDATE-Rechte
an andere Benutzer weitergeben.
GRANT UPDATE(Nachname, Vorname)
ON Mitarbeiter
TO Schulz
Schulz darf nur Nachname und Vorname mit UPDATE ändern.
REVOKE UPDATE(Nachname)
ON Mitarbeiter
FROM Schulz
Das UPDATE-Recht für das Attribut Nachname wird Schulz entzogen.
REVOKE DELETE
ON Mitarbeiter
FROM Schulz
Das DELETE-Recht wird Schulz entzogen.
REVOKE ALL
ON Mitarbeiter
FROM Schulz
Alle Rechte an der Tabelle Mitarbeiter werden Schulz entzogen.
3.3
VIEW’s
Ein VIEW (Sicht) ist ein Datenbankobjekt, das über eine SELECT-Abfrage definiert
wird. VIEW´s werden wie SELECT-Abfragen zur Laufzeit ausgewertet. Beachten Sie
den Unterschied zwischen den folgenden beiden Anweisungen. In der ersten Anweisung wird eine neue Tabelle angelegt, die nur Mitarbeiter enthält, die in diesem Jahr
eingestellt wurden. YEAR() ist eine Funktion, die das Jahr aus einem Datum ausblendet. NOW() liefert das aktuelle Datum.
CREATE TABLE Neue_Mitarbeiter AS
SELECT * FROM Mitarbeiter
WHERE YEAR(Anstellung) = YEAR(NOW())
Die zweite Anweisung erstellt keine neue Tabelle sondern einen VIEW, d.h. eine Art
virtuelle Tabelle. Der Unterschied zeigt sich z.B. darin, dass die Sicht auch aktuell
bleibt, wenn sich in der Basistabelle Mitarbeiter etwas ändert. Dies ist bei der neuen
Tabelle nicht der Fall. Die Anfrage wird im ersten Fall nur einmal bei Erzeugung der
Tabelle ausgeführt, im zweiten Fall wird die Anfrage jedoch bei jedem Zugriff auf die
Sicht ausgeführt.
KAPITEL 3. STRUCTURED QUERY LANGUAGE (SQL)
21
CREATE VIEW Neue_Mitarbeiter AS
SELECT * FROM Mitarbeiter
WHERE YEAR(Anstellung) = YEAR(NOW())
Eine weitere Aufgabe einer Sicht ist es, den Zugriff für bestimmte Benutzer auf spezielle Attribute einzuschränken. So sollen z.B. nur Mitarbeiter der Personalabteilung
Zugriff auf das Attribut Gehalt haben, d.h. für alle anderen Benutzer wird folgende
Sicht definiert:
CREATE VIEW Mitarbeiter_Sicht AS
SELECT Vorname, Nachname,
Vertragsart, Anstellung
FROM Mitarbeiter
Nun kann ein direkter Zugriff auf die Tabelle Mitarbeiter verboten werden, so dass nur
über den VIEW zugegriffen werden kann. Hierdurch bleibt das Gehalt für diejenigen,
die nicht auf die Tabelle direkt zugreifen können, unsichtbar.
GRANT SELECT ON Mitarbeiter_Sicht TO PUBLIC
3.4
Datentypen
Die wichtigsten Datentypen in SQL2 werden in der folgenden Tabelle zusammen mit
Beispielen aufgelistet:
Datentyp
CHAR
CHAR VARYING
INT
SMALLINT
NUMERIC
DECIMAL
FLOAT
DOUBLE PRECISION
BIT
BIT VARYING
DATE
TIME
Beispiele
’ABC’
’ABC’
258734
42
2.99 oder 3
2.99 oder 3
2.5E-4 oder 10E9
3.1415929E00
B’01111101’ oder X’74E’
B’01111101’ oder X’74E’
’2000-10-10’
’09:00:05.01’
Die Datentypen CHAR und CHAR VARYING unterscheiden sich lediglich in der
Art der Speicherung der Zeichenketten. Während CHAR statisch den angegebenen
Speicherplatz reserviert, wird bei CHAR VARYING nur soviel Speicherplatz verwendet wie die Länge der Zeichenkette erfordert und zusätzlich meist 1 Byte zur Speicherung dieser Länge.
INT und SMALLINT können verschieden große ganzzahlige Werte aufnehmen.
Während INT meist wie der C-Datentyp LONG realisiert ist, ist SMALLINT platzsparender als SHORT realisiert. Der Wertebereich von INT geht von -2.147.483.648 bis
2.147.483.647; der Wertebereich von SMALLINT geht von -32.768 bis 32.767.
FLOAT entspricht i.d.R. dem C-Datentyp REAL, DOUBLE PRECISION dem CDatentyp DOUBLE.
KAPITEL 3. STRUCTURED QUERY LANGUAGE (SQL)
22
Weil SQL2 streng typisiert ist, müssen die Operanden in arithmetischen Ausdrücken
von einem einheitlichen Typ sein. Falls x den Typ DECIMAL(6, 2) und y den Typ REAL hat, ist x+y < 5 eine illegale Anweisung. Mit einem CAST-Operator (ähnlich zur
Programmiersprache C) kann der Typ explizit umgewandelt werden:
CAST(x AS REAL) + y < CAST(5 AS REAL)
3.5
SELECT-Vertiefung
Das bei weitem schwierigste Sprachkonstrukt in SQL ist die SELECT-Abfrage. Es gibt
eine Vielzahl von zusätzlichen Klauseln zu SELECT, die es ermöglichen sehr gezielte
und damit auch schnelle Datenbankabfragen zu formulieren.
3.5.1
ORDER BY-Klausel
Mit der ORDER BY-Klausel kann das Ergebnis einer SELECT-Abfrage sortiert werden. Sollen die Mitarbeiter aufsteigend nach Gehältern ausgegeben werden, so kann
das wie folgt erreicht werden:
SELECT *
FROM Mitarbeiter
ORDER BY Gehalt
Die absteigende Ordnung wird mit DESC (descending) erzielt:
SELECT *
FROM Mitarbeiter
ORDER BY Gehalt DESC
Ordnungen können auch nach verschiedenen Kriterien gestaffelt durchgeführt werden,
z.B.:
SELECT *
FROM Mitarbeiter
ORDER BY Gehalt DESC, Nachname
Hier wird zunächst nach dem Gehalt numerisch absteigend sortiert; innerhalb der Zeilen mit gleichen Gehältern wird nach dem Nachnamen alfabetisch aufsteigend sortiert.
3.5.2
AND, OR, NOT in der WHERE-Klausel
In einer WHERE-Klausel können auch mehrere Bedingungen durch die logischen Operatoren AND, OR und NOT miteinander verknüpft werden. Folgende Operation zeigt
alle Mitarbeiter an, deren Gehalt mehr als 6000 beträgt und deren Nachname mit S
beginnt:
SELECT *
FROM Mitarbeiter
WHERE Gehalt > 6000 AND Nachname LIKE ’S%’
KAPITEL 3. STRUCTURED QUERY LANGUAGE (SQL)
23
Der Vergleichsoperator LIKE ist hierbei speziell für Zeichenketten geeignet, um sie mit
Wildcards zu vergleichen. Das %-Zeichen steht für beliebig viele oder kein Zeichen.
Der Unterstrich _ steht für genau ein Zeichen.
Werden mehrere AND- und OR-Operatoren verwendet, so gilt implizit die Ordnung, dass AND vor OR ausgewertet wird. AND bindet also als Operator stärker als
OR. NOT bindet stärker als AND und OR.
Folgende Anfrage selektiert alle Mitarbeiter, deren Gehalt 6000 beträgt und die
nach dem 1.1.1998 angestellt wurden und weiterhin alle Mitarbeiter deren Nachname
mit S beginnt.
SELECT *
FROM Mitarbeiter
WHERE Gehalt = 6000
AND Anstellung > ’19980101’
OR Name LIKE ’S%’
Durch die Klammerung des OR-Ausdrucks in der vorigen Anfrage entsteht eine andere
Anfrage:
SELECT *
FROM Mitarbeiter
WHERE Gehalt = 6000
AND (Anstellung > ’19980101’
OR Name LIKE ’S%’)
Hier werden alle Mitarbeiter selektiert, die ein Gehalt von 6000 haben und später als
zum 1.1.1998 eingestellt wurden oder deren Nachname mit S beginnt. Die folgende
Zeile wird daher durch die erste Anfrage ausgegeben, jedoch nicht durch die zweite:
Nr
6
3.5.3
Nachname
Schulz
Vorname
Franz
Vertragsart
Angestellter
Geburtstag
19651211
Anstellung
19970101
Gehalt
5000
Bereiche, Listen und Subqueries
Eine abkürzende Schreibweise für Bereiche, die natürlich auch mit den Operatoren
<,>,<= und >= formuliert werden können, ist der Operator BETWEEN. Mit folgender
Anfrage werden alle Mitarbeiter mit Gehältern zwischen 5000 und 10000 ausgeben:
SELECT *
FROM Mitarbeiter
WHERE Gehalt BETWEEN 5000 AND 10000
Natürlich kann der BETWEEN-Operator auch negiert werden:
SELECT *
FROM Mitarbeiter
WHERE Gehalt NOT BETWEEN 5000 AND 10000
Es werden dann alle Mitarbeiter mit einem Gehalt von mehr als 10000 oder weniger
als 5000 ausgegeben.
In Bedingungen von WHERE-Klauseln können auch Listen verwendet werden.
Die einfachste WHERE-Bedingung sieht z.B. wie folgt aus:
KAPITEL 3. STRUCTURED QUERY LANGUAGE (SQL)
24
SELECT *
FROM Mitarbeiter
WHERE Nachname IN (’Meier’, ’Schmidt’, ’Nolte’)
Hierbei handelt es sich um eine konstante Liste. Der Operator IN macht die Anweisung
äquivalent zu folgender Anweisung:
SELECT *
FROM Mitarbeiter
WHERE Nachname=’Meier’
OR Nachname=’Schmidt’
OR Nachname=’Nolte’)
Natürlich läßt sich auch eine Anfrage auserhalb der Werte aus der Liste wie folgt formulieren:
SELECT *
FROM Mitarbeiter
WHERE Nachname NOT IN (’Meier’, ’Schmidt’)
Der interessantere Fall der Anwendung von Listen, besteht in der Erzeugung einer
Liste zur Laufzeit durch eine Unterabfrage (Subquery). Man spricht dann von dynamischen Listen. Nehmen wir an, dass es zu unserer Mitarbeiter-Tabelle noch eine
zusätzliche Tabelle Abteilung gibt, in der die Abteilungsnummer, die Bezeichnung der
Abteilung, der Standort und der Abteilungsleiter festgehalten wird. Wir werden dann
zur Mitarbeiter-Tabelle eine Spalte Abteilungsnummer hinzufügen, um anzuzeigen in
welcher Abteilung der jeweilige Mitarbeiter tätig ist.
Anstellung
Nr
Gehalt
Nachname
AbteilungsNr
Vorname
Geburtstag
Ort
Leiter
Vertragsart
Mitarbeiter
n
arbeitet
in
1
Abteilung
Nr
Bezeichnung
Folgende Anfrage ist dann mit Hilfe einer Unterabfrage möglich. Überlegen Sie,
welche Nummern und Bezeichnungen von Abteilungen hiermit ausgegeben werden.
KAPITEL 3. STRUCTURED QUERY LANGUAGE (SQL)
25
SELECT Nr, Bezeichnung
FROM Abteilung
WHERE Nr NOT IN
(SELECT AbteilungsNr
FROM Mitarbeiter)
Die Operatoren ANY, ALL und EXISTS werden seltener verwendet, sind aber in Zusammenhang mit dynamischen Listen in ihrem Ausdruck sehr mächtig. Bei ANY ist
die Gesamtbedingung erfüllt, wenn sie für irgendein Listenelement erfüllt ist. Dies
entspricht der Oder-Logik. Bei ALL ist die Gesamtbedingung erfüllt, wenn sie für alle
Listenelemente erfüllt ist. Dies entspricht der Und-Logik.
ANY-Bedingung
x = ANY(Liste)
x < ANY(Liste)
x > ANY(Liste)
x <= ANY(Liste)
x >= ANY(Liste)
x != ANY(Liste)
Erfüllt (TRUE) wenn,
es zu x ein gleiches Listenelement gibt
es ein größeres Listenelement als x gibt
es ein kleineres Listenelement als x gibt
es ein mindestens so großes Listenelement wie x gibt
es ein mindestens so kleines Listenelement wie x gibt
es ein zu x ungleiches Listenelement gibt
Man sieht, dass =ANY(Liste) äquivalent zu IN(Liste) ist.
ALL-Bedingung
x = ALL(Liste)
x < ALL(Liste)
x > ALL(Liste)
x <= ALL(Liste)
x >= ALL(Liste)
x != ALL(Liste)
Erfüllt (TRUE) wenn,
alle Listenelemente gleich x sind
alle Listenelemente größer x sind
alle Listenelemente kleiner x sind
alle Listenelemente kleiner oder gleich x sind
alle Listenelemente größer oder gleich x sind
alle Listenelemente ungleich x sind
Mit dem EXIST-Operator kann man die Existenz bestimmter Zeilen in einer Tabelle überprüfen und abhängig davon andere Zeilen ausgeben. Durch folgende Abfrage
werden alle Abteilungen, in denen Mitarbeiter tätig sind ausgegeben:
SELECT *
FROM Abteilung
WHERE EXISTS
(SELECT *
FROM Mitarbeiter
WHERE Mitarbeiter.AbteilungsNr = Abteilung.Nr)
Was würde passieren, wenn wir die WHERE-Bedingung im Subquery vergessen hätten?
SELECT *
FROM Abteilung
WHERE EXISTS
(SELECT *
FROM Mitarbeiter)
Da das Ergebnis der EXISTS-Klausel auf den Subquery TRUE zurückliefert, wenn die
Mitarbeiter-Tabelle nicht leer ist, würden in diesem Fall alle Abteilungen ausgegeben,
KAPITEL 3. STRUCTURED QUERY LANGUAGE (SQL)
26
d.h. auch die ohne Mitarbeiter. Wäre die Mitarbeiter-Tabelle leer, so würden keine Abteilungen ausgegeben, da die EXISTS-Klausel den Wert FALSE zurückliefern würde.
Man sieht also, dass in diesem Fall die Anfrage und die Unteranfrage zueinander nicht
in der gewünschten Beziehung stehen.
3.5.4
Gruppenverarbeitung
Die GROUP BY-Klausel unterteilt eine Tabelle in Gruppen mit gleichen Werten bezüglich der angegebenen Attribute. Die folgende Abfrage unterteilt Abteilungen in
Gruppen gleicher Orte:
SELECT *
FROM Abteilung
GROUP BY Ort
Nr
3
5
2
4
1
Bezeichnung
Entwicklung
Produktion
Personal
Finanzen
Verkauf
Ort
Aachen
Aachen
Gießen
Gießen
Hamburg
Leiter
Hauser
Kienzle
Hartung
Opel
Stein
In der Ausgabe unterscheidet sich diese Anfrage nicht von folgender ORDER BYAnfrage:
SELECT *
FROM Abteilung
ORDER BY Ort
Der Unterschied zur ORDER BY-Anfrage zeigt sich bei der Verwendung von sogenannten Gruppenfunktionen. Eine Gruppenfunktion kann z.B. mit einer HAVINGKlausel angeschlossen werden:
SELECT *
FROM Abteilung
GROUP BY Ort
HAVING COUNT(Ort) > 1
Die Gruppenfunktion COUNT() zählt die Anzahl gleicher Vorkommen von Ort innerhalb einer Gruppe. Diese Anfrage bewirkt also, dass nach der Gruppenbildung Gruppen mit weniger als zwei Zeilen eliminiert werden:
Nr
3
5
2
4
Bezeichnung
Entwicklung
Produktion
Personal
Finanzen
Ort
Aachen
Aachen
Gießen
Gießen
Leiter
Hauser
Kienzle
Hartung
Opel
Weitere häufig verwendete Gruppenfunktionen sind:
KAPITEL 3. STRUCTURED QUERY LANGUAGE (SQL)
Funktion
AVG()
COUNT()
MAX()
MIN()
SUM()
27
Wirkung
arithmetisches Mittel pro Gruppe
Anzahl der Elemente pro Gruppe
Maximalwert pro Gruppe
Minimalwert pro Gruppe
Summe pro Gruppe
Folgende Anweisung zeigt alle Mitarbeiter aus den Abteilungen an, in denen das
Durchschnittsgehalt der Mittarbeiter 5000 übersteigt:
SELECT *
FROM Mitarbeiter
GROUP BY AbteilungsNr
HAVING AVG(Gehalt) > 5000
Eine Gruppenfunktion kann natürlich auch mit einer WHERE-Klausel kombiniert werden:
SELECT *
FROM Mitarbeiter
WHERE YEAR(Anstellung) = YEAR(NOW())
GROUP BY AbteilungsNr
HAVING SUM(Gehalt) > 100000
Hier werden die in diesem Jahr angestellten Mitarbeiter aus den Abteilungen selektiert,
bei denen die Summe der Gehälter dieser neu angestellten Mitarbeiter pro Abteilung
100000 übersteigt.
3.5.5
Operationen und Funktionen
Während Gruppenfunktionen für das gesamte Abfrageergebnis oder innerhalb von Gruppen der GROUP BY-Klausel angewendet werden, werden Einzeilenfunktionen für jede
Zeile des Abfrageergebnisses einzeln ausgeführt. Es folgen zwei Beispiele, um den
Unterschied noch einmal zu verdeutlichen. Die Gruppenfunktion AVG kann z.B. für
eine gesamte Relation ausgeführt werden:
SELECT AVG(Gehalt) AS Gehaltsmittel
FROM Mitarbeiter;
Die Einzeilenfunktion ROUND wird für jede einzelne Zeile, d.h. jedes Gehalt jedes
Mitarbeiters ausgeführt. Hierbei wird auf zwei Dezimalen hinter dem Komma gerundet.
SELECT ROUND(Gehalt, 2)
FROM Mitarbeiter;
Weitere arithmetische Operatoren für numerische Werte A, B sind:
TRUNC (A) für das Abschneiden der Dezimalstellen von A
TRUNC (A,n) schneidet die Zahl A nach n Stellen hinter dem Dezimalpunkt ab
TRUNC (A,-n) setzt n Stellen der Zahl A vor dem Dezimalpunkt auf 0
KAPITEL 3. STRUCTURED QUERY LANGUAGE (SQL)
28
ROUND (A) für das Auf- und Abrunden von Dezimalstellen
ROUND (A,n) für das Auf- und Abrunden auf die n-te Stelle rechts vom Dezimalpunkt
ROUND (A,-n) für das Auf- und Abrunden von n Stellen links vom Dezimalpunkt
NOROUND (A) verhindert das Auf- und Abrunden von Werten, um sie einem
Datentyp anzupassen
CEIL (A) bildet den kleinsten ganzzahligen Wert, der größer oder gleich A ist
FLOOR (A) bildet den größten ganzzahligen Wert, der kleiner oder gleich A ist.
SIGN (A) gibt Auskunft über das Vorzeichen von A
ABS (A) für den vorzeichenlosen (absoluten) Wert von A
FIXED (a,p,q) dient zur Angabe der Zahl a in einem Format vom Datentyp FIXED (p,q) mit Rundung
POWER (A,n) bildet die n-te Potenz von A SQRT (A) errechnet die Quadratwurzel von A
EXP (A) bildet die Potenz aus Basis e (2.71828183) und Exponent A ("e hoch
A")
LN (A) bildet den natürlichen Logarithmus von A
LOG (A,B) bildet den Logarithmus von B zur Basis A
PI gibt den Wert von [pi] aus
Als trigonometrische Funktionen, die als Ergebnis einen numerischen Wert liefern,
stehen bereit:
COS (A) Kosinus der Zahl A
SIN (A) Sinus der Zahl A
TAN (A) Tangens der Zahl A
COT (A) Kotangens der Zahl A
COSH (A) Kosinus hyperbolicus der Zahl A
SINH (A) Sinus hyperbolicus der Zahl A
TANH (A) Tangens hyperbolicus der Zahl A
ACOS (A) Arcus Kosinus der Zahl A
ASIN (A) Arcus Sinus der Zahl A
ATAN (A) Arcus Tangens der Zahl A
ATAN2 (A,B) bildet unter bestimmten Voraussetzungen den Arcus Tangens des
Wertes A/B
KAPITEL 3. STRUCTURED QUERY LANGUAGE (SQL)
29
RADIANS (A) Bogenmaß der Zahl A
DEGREES (A) Gradmaß der Zahl A
Neben den explizit aufgerufenen Operationen gibt es natürlich auch die gewohnten
arithmetischen Operationen in der Infix-Notation: Es können Ausdrücke mit den Symbolen für die Operationen + Addition - Subtraktion * Multiplikation / Division gebildet
werden. Es können Spaltennamen (geschwindigkeit * zeit), Konstanten (geschwindigkeit * 1.01) und Funktionen, die sich auf eine gesamte Spalte beziehen, wie beispielsweise (AVG (konto - 500)) verwendet werden.
Weiterhin stehen zur Verfügung:
A DIV B für die ganzzahlige Division von A durch B
A MOD B für den Rest nach einer ganzzahligen Division von A durch B
Um den Umgang mit Datums- und Zeitberechnungen zu erleichtern, sind eine Reihe
von Funktionen verfügbar, die mit Werten dieser Typen rechnen. Ein um zwei Tage
erhöhtes Buchungsdatum ergibt:
SELECT ADDDATE (ankunft,2) AS ankunft2
FROM buchung
WHERE bnr = 130
Die Anzahl der Buchungstage zwischen Anfangs- und Enddatum ergibt:
SELECT DATEDIFF (abreise, ankunft) AS differenz
FROM buchung
WHERE bnr = 130
Weitere Datumsfunktionen sind:
SUBDATE ermittelt ein zurückliegendes Datum.
DAYOFWEEK gibt den Wochentag an (1. Tag: Montag). DAYOFMONTH gibt
an, der wievielte Tag des Monats der angegebene Tag ist. DAYOFYEAR gibt
an, der wievielte Tag des Jahres der angegebene Tag ist. WEEKOFYEAR gibt
an, in welcher Woche des Jahres der angegebene Tag liegt.
YEAR, MONTH, DAY extrahieren aus einem Datums- oder Zeitstempelwert das
Jahr, den Monat bzw. den Tag.
MAKEDATE bildet aus einer Jahreszahl und einem Tag einen Datumswert. DAYNAME gibt den Wochentag als Zeichenkette aus. MONTHNAME gibt den Monatsnamen als Zeichenkette aus.
Wichtige Zeichenketten-Funktionen sind:
Konkatenation, || Wenn x eine Zeichenkette der Länge n und y eine Zeichenkette der Länge m ist, dann ist x||y die Konkatenation xy der Länge n+m. Wenn
eine Zeichenkette aus einer Spalte hervorgeht, dann erfolgt die Längenbestimmung dieser Zeichenkette ohne Berücksichtigung von nachfolgenden Leerzeichen (Code-Attribut ASCII oder EBCDIC) bzw. binären Nullen (Code-Attribut
BYTE). Wenn ein Operand der Konkatenation der NULL-Wert ist, dann ist das
KAPITEL 3. STRUCTURED QUERY LANGUAGE (SQL)
30
Ergebnis der NULL-Wert. Spalten mit gleichem Code-Attribut sind konkatenierbar. Spalten mit den unterschiedlichen Code-Attributen ASCII und EBCDIC
sind konkatenierbar. Spalten der Code-Attribute ASCII und EBCDIC sind mit
Datums-, Zeit- oder Zeitstempelwerten konkatenierbar. Die Konkatenation x&y
liefert das gleiche Ergebnis wie die Konkatenation x||y.
SUBSTR Wenn x eine Zeichenkette der Länge n ist, dann ist SUBSTR(x,a,b)
der Teil der Zeichenkette x, der an dem a-ten Zeichen beginnt und b Zeichen
lang ist. SUBSTR(x,a) entspricht SUBSTR(x,a,n-a+1) und liefert alle Zeichen
der Zeichenkette x vom a-ten Zeichen bis zum letzten (n-ten). Wird b als <unsigned integer> angegeben, so ist für b auch ein Wert zulässig, der größer als
(n-a+1) ist. In allen anderen Fällen darf der Wert von b den Wert (n-a+1) nicht
übersteigen. Wenn b > (n-a+1), dann wird intern SUBSTR(x,a) durchgeführt.
An dieses Ergebnis werden am Ende so viele Leerzeichen (Code-Attribut ASCII
oder EBCDIC) bzw. binäre Nullen (Code-Attribut BYTE) angefügt, bis es die
Länge b hat. Wenn x, a oder b der NULL-Wert ist, dann ist SUBSTR(x,a,b) der
NULL-Wert.
REPLACE ersetzt in der als ersten Parameter angegebenen Zeichenkette die als
zweiten Parameter angegebene Zeichenkette durch die als dritten Parameter angegebene Zeichenkette. Wenn kein dritter Parameter angegeben oder der dritte
Parameter der NULL-Wert ist, dann wird die als zweiter Parameter angegebene
Zeichenkette in der ersten Zeichenkette gelöscht. Wenn der erste Parameter der
NULL-Wert ist, dann liefert REPLACE den NULL-Wert als Ergebnis. Wenn der
zweite Parameter der NULL-Wert ist, wird der erste Parameter unverändert als
Ergebnis geliefert.
UPPER wandelt Kleinschrift in Großschrift um.
LOWER wandelt Großschrift in Kleinschrift um.
3.5.6
Mengenoperationen: UNION, INTERSECT, EXCEPT
Mit Mengenoperationen können Abfrageergebnisse verschiedener Tabellen verknüpft
werden. Im einfachsten Fall können auf diese Weise zwei Ergebnistabellen, die aus der
gleichen Basistabelle gebildet wurden, miteinander verknüpft werden. Sind beispielsweise alle Kunden, wohnhaft in Berlin oder München, gesucht, so läßt sich das unter
Benutzung von UNION folgendermaßen darstellen:
SELECT anrede, vorname, name, ort
FROM kunde
WHERE ort = ’Berlin’
UNION SELECT anrede, vorname, name, ort
FROM kunde
WHERE ort = ’München’
Dieses Ergebnis wäre jedoch auch mit einem einfachen SELECT unter Zuhilfenahme
von OR zu erhalten gewesen:
SELECT anrede, vorname, name, ort
FROM kunde
WHERE ort = ’Berlin’ OR ort = ’München’
KAPITEL 3. STRUCTURED QUERY LANGUAGE (SQL)
31
Die UNION-Anweisung erlaubt es darüber hinaus, Ergebnistabellen zu kombinieren,
die aus unterschiedlichen Tabellen gebildet wurden. Es muß dabei jedoch gewährleistet
sein, daß die Datentypen der jeweiligen i-ten Ausgabespalten vergleichbar sind. Eine
Gleichheit ist nicht erforderlich, da gegebenenfalls die maximale Länge genutzt wird.
Eine Kombination aus CHAR (10) und CHAR (15) Spalten ist also möglich, da die
Länge automatisch auf CHAR (15) ausgeweitet wird.
Die Wirkungsweise von UNION, INTERSECT und EXCEPT jeweils mit und ohne
ALL-Klausel wird anhand der Ortsspalten der beiden Tabellen ’hotel’ und ’kunde’
dargestellt.
Um eine bessere Übersicht über die gefundenen Ergebnisse zu erzielen, sollen sich
die Beispiele nur auf den süddeutschen Raum beziehen, d. h. die Postleitzahl sei
größer/gleich 70000.
(Die Abfrage wurde in der folgenden Weise formuliert, da die Postleitzahl nicht als
Zahl, sondern als Zeichenfolge definiert ist.)
SELECT ort
FROM kunde
WHERE plz > ’7’
UNION
SELECT ort
FROM hotel
WHERE plz > ’7’
Mehrfach auftauchende Orte nur einmal angezeigt werden. Die Datenbank setzt bei
Verwendung von UNION implizit ein DISTINCT ab. Um alle Orte in der Häufigkeit
ihres Auftretens zu erhalten, wird die Anweisung UNION ALL verwendet.
SELECT ort
FROM kunde
WHERE plz > ’7’
UNION ALL
SELECT ort
FROM hotel
WHERE plz > ’7’
Eine Schnittmengenbeziehung läßt sich mit der Anweisung INTERSECT herstellen.
Es sollen nun alle Orte gefunden werden, die sowohl in ’kunde’ als auch in ’hotel’ vorkommen. Ohne zusätzliche Angabe von ALL wird auch hier ein implizites DISTINCT
abgesetzt.
SELECT ort
FROM kunde
WHERE plz > ’7’
INTERSECT
SELECT ort
FROM hotel
WHERE plz > ’7’
Mehrfach in der Schnittmenge gefundene Werte werden bei Verwendung der nachfolgenden Anweisung angezeigt. Die Ergebniswerte tauchen jedoch nur in der Häufigkeit
auf, wie die Werte aus den beiden Tabellen ’kunde’ und ’hotel’ jeweils ein Pendant
aufweisen.
KAPITEL 3. STRUCTURED QUERY LANGUAGE (SQL)
32
SELECT ort
FROM kunde
WHERE plz > ’7’
INTERSECT ALL
SELECT ort
FROM hotel
WHERE plz > ’7’
Die EXCEPT-Klausel stellt die Möglichkeit bereit, Ergebnisse aus einer Ergebnistabelle von einer anderen abzuziehen. Gewünscht seien zunächst alle Orte, die in der Tabelle
’hotel’ gefunden werden, aber nicht in der Ergebnistabelle von ’kunde’ enthalten sind.
SELECT ort
FROM hotel
WHERE plz > ’7’
EXCEPT
SELECT ort
FROM kunde
WHERE plz > ’7’
Die Reihenfolge der SELECT-Anweisungen ist hier nicht - wie bei UNION und INTERSECT - beliebig. Bevor das EXCEPT wirksam wird, wird implizit ein DISTINCT
auf die Tabellen abgesetzt.
Möchte man erreichen, daß auch die Anzahl der in den einzelnen Ergebnistabellen gefundenen Zeilen in Betracht gezogen wird, muß mit EXCEPT ALL gearbeitet
werden.
SELECT ort
FROM kunde
WHERE plz > ’7’
EXCEPT ALL
SELECT ort
FROM hotel
WHERE plz > ’7’
Anschaulich gesehen bedeutet das, daß man die jeweils übereinstimmenden Werte der
beiden Tabellen wegstreicht; die verbleibenden Werte der ersten Tabelle bleiben als
Ergebnis stehen.
3.5.7
Verbunde
Je nachdem, ob das Gleichheitszeichen oder ein Ungleichheitszeichen in der WHEREKlausel eines Verbundes verwendet wird, spricht man von einem Equi-Join oder einem
Non-Equi-Join. Das folgende Beispiel für einen Equi-Join gibt Mitarbeiter mit den
Abteilungen, in denen sie arbeiten wieder:
SELECT M.Nr, M.Nachname, A.Bezeichnung
FROM Mitarbeiter M, Abteilung A
WHERE M.AbteilungsNr = A.Nr;
KAPITEL 3. STRUCTURED QUERY LANGUAGE (SQL)
33
Der Auto-Join ist ein Sonderfall des Equi-Join. Hierbei wird eine Tabelle mit sich
selbst verknüpft. In der FROM-Klausel werden dazu unterschiedliche Alias-Namen
für ein und dieselbe Tabelle vergeben. Ein Auto-Join macht Sinn, wenn eine physikalische Tabelle aus verschiedenen logischen Blickwinkeln betrachtet wird. So kann
in der Tabelle der Mitarbeiter ein Untergebener mit der Nummer seines Vorgesetzten
verzeichnet sein. Will man nun alle Untergebene mit ihren Vorgesetzten ausgeben,
so muss man zweimal auf die Tabelle Mitarbeiter zugreifen. Die SELECT-Abfrage
arbeitet wieder mit zwei Alias-Namen für eine physikalische Tabelle.
SELECT V.Nachname ’Vorgesetzter’,
U.Nachname ’Untergebener’
FROM Mitarbeiter V, Mitarbeiter U
WHERE U.VorgesetzterNr = V.Nr;
Bei einem Non-Equi-Join wird meistens jede Zeile der ersten Tabelle mit mehreren
Zeilen der zweiten Tabelle verknüpft. Natürlich kann auch der Non-Equi-Join auf eine
Tabelle angewendet werden. Im Beispiel werden für alle Mitarbeiter deren Kollegen
ausgegeben, die mindestens genauso lange in der Firma angestellt sind:
SELECT M1.Nr, M1.Nachname, M1.Anstellung,
M2.Nr, M2.Nachname, M2.Anstellung
FROM Mitarbeiter M1, Mitarbeiter M2
WHERE M1.Anstellung >= M2.Anstellung
ORDER BY M1.Nr;
Durch die Zusatzbedingung M1.Nr <> M2.Nr werden Selbstbezüge im Abfrageergebnis ausgeschlossen.
Hat eine der Zeilen der Relationen eines Equi- oder Non-Equi-Join in gemeinsamen
Attributen einer anderen Relation keine Entsprechung, so wird diese Zeile im Verbund
nicht auftreten. Anders ist das beim Outer-Join: Hier kann die Zeile auftreten, allerdings mpssen dei Attributwerte der Verbundrelation undefiniert bleiben. Ein Beispiel
ist eine neue Firma, in der nicht in allen Abteilungen Mitarbeiter tätig sind. Will man,
dass auch Abteilungen angezeigt werden, für die keine AbteilungsNr in der Tabelle
Mitarbeiter existiert, so fügt man das Outer-Join-Zeichen “(+)” an das gemeinsame
Attribut (oder die gemeinsamen Attribute) in der WHERE-Klausel an.
SELECT A.Nr, A.Bezeichnung, M.Nachname
FROM Abteilung A, Mitarbeiter M
WHERE A.Nr = M.AbteilungsNr (+);
Innerhalb eines Verbundes kann allerdings nur eine Tabelle das Outer-Join-Zeichen
erhalten. Sollte es einen Mitarbeiter geben, der noch keiner Abteilung zugeordnet ist,
so kann dieser nicht gleichzeitig mit Abteilungen, in denen keine Mitarbeiter arbeiten
angezeigt werden.
3.6
Zugriffsoptimierung mit INDEX und CLUSTER
Die physikalische Speicherung einer Tabelle ist i.d.R. unsortiert bzw. nur nach dem Primärschlüssel sortiert. Das hat zur Folge, daß Suchoperationen nach Attributen die im
Primärschlüssel nicht vorkommen, sequentiell über den Speicherbereich durchgeführt
KAPITEL 3. STRUCTURED QUERY LANGUAGE (SQL)
34
werden müssen. Gegenüber einem wahlfreien Zugriff führt das natürlich vor allem bei
größeren Relationen zu Geschwindigkeitsproblemen.
Um auch bei der Suche nach anderen als den Primärschlüssel-Attributen wahlfrei
zugreifen zu können, können Indices definiert werden. Ein Index enthält eine nach den
Index-Attributen sortierte Liste, wobei mit jedem Index-Attributwert in der Liste die
tatsächlichen Position des Satzes im Speicher vermerkt ist. Es können zu einer Basistabelle auch mehrere Indices angelegt werden. CREATE [UNIQUE] INDEX erzeugt
einen Index einer Basistabelle. Ein Index kann nur bei einer leeren Tabelle erzeugt
werden. Bei der Definition von Indizes wird mit UNIQUE festgelegt, ob die Spaltenwerte verschiedener Zeilen in den indizierten Spalten eindeutig sein müssen oder nicht.
Soll in der Tabelle Mitarbeiter z.B. ein Index mit dem Namen Gehalts_Index definiert
werden, der nach dem Gehalt sortiert ist, so ist dies auf folgende Art möglich:
CREATE INDEX Gehalts_Index
ON Mitarbeiter
Gehalt;
Die Beschleunigung des Zugriffs wird jedoch nur erreicht, wenn in der WHEREKlausel der Abfrage auch Attribute des Index verwendet werden. Also z.B. bei folgender Abfrage:
SELECT Name, Gehalt
FROM Mitarbeiter
WHERE Gehalt > 5000;
Die Beschleunigung des Zugriffs durch die Definition eines Index wird allerdings durch
den zusätzlichen Verwaltungsaufwand für die Pflege der Zugriffsliste bei INSERT, DELETE und UPDATE erkauft. Diese Operationen laufen mit Index i.d.R. langsamer ab
als ohne Index.
Mit dem Befehl DROP INDEX kann ein existierender Index gelöscht werden. Die
Basistabelle bleibt natürlich dabei erhalten.
Die Aufgabe eines Cluster ist die Beschleunigung von Verbund-Operationen. Hierbei werden Zeilen verschiedener Relationen, die gleiche Werte gemeinsamer Attribute haben, in einem Cluster dicht beisammen gespeichert. Auch ein Cluster kann nur
bei leeren Tabellen, zu denen es korrespondiert, erzeugt werden. Zur Definition eines Cluster sind drei Schritte notwendig, die am Beispiel der Tabellen Mitarbeiter und
Abteilung gezeigt werden:
Das Datenobjekt Cluster wird mit einem Schlüssel des Clusters definiert:
CREATE CLUSTER Abteilungs_Cluster
(Nummer SMALLINT);
Ein Index wird für den Cluster-Schlüssel angelegt:
CREATE INDEX Abteilungs_Index
ON CLUSTER Abteilungs_Cluster Nummer;
Die Zuordnung der Basistabellen zum Cluster, die beim Verbund mit gleichen
Attributen zusammengefügt werden, wird definiert.
Das Anlegen der Tabelle Mitarbeiter geschieht wie folgt:
KAPITEL 3. STRUCTURED QUERY LANGUAGE (SQL)
35
CREATE TABLE Mitarbeiter (
Nr
SMALLINT NOT NULL,
Nachname
CHAR(25) NOT NULL,
Vorname
CHAR(25) NOT NULL,
...
AbtNr
SMALLINT,
PRIMARY KEY (Nr))
CLUSTER Abteilungs_Cluster(AbtNr);
Das Anlegen von Abteilung sieht folgendermaßen aus:
CREATE TABLE Abteilung (
Nr SMALLINT NOT NULL,
...
PRIMARY KEY (Nr))
CLUSTER Abteilungs_Cluster(Nr);
Verbundanfragen, die über Cluster-Attribute arbeiten werden nun beschleunigt, z.B.:
SELECT Nachname, Bezeichunung
FROM Mitarbeiter, Abteilung
WHERE AbtNr = Abteilung.Nr;
Wie bei der Definition von Indices gilt auch hier, daß die Beschleunigung des Zugriffs
mit einer Verlangsamung der Operationen INSERT, DELETE und UPDATE für die
Pflege des Clusters erkauft werden.
Mit dem Befehl DROP CLUSTER kann ein existierendes Cluster gelöscht werden.
Die zugehörigen Basistabellen bleiben erhalten.
3.7
INSERT, DELETE, UPDATE-Vertiefung
Im SQL2-Standard können INSERT-, DELETE- und UPDATE-Anweisungen auf vielfältige Weise mit SELECT-Anweisungen kombiniert werden.
Will man z.B. aus einer alten Tabelle Werte in eine neue Tabelle übernehmen, so
geht das z.B. auf folgende einfache Weise:
INSERT INTO Mitarbeiter_neu
SELECT * FROM Mitarbeiter;
Auch das Löschen von Zeilen kann mit SELECT kombiniert werden. Die folgende
DELETE-Anweisung löscht alle Mitarbeiter, die in einer Abteilung des Abteilungsleiters Kienzle arbeiten. (Zugegeben ist dies ein fragwürdiges Unterfangen.)
DELETE FROM Mitarbeiter
WHERE AbtNr IN
(SELECT Nr
FROM Abteilung
WHERE Leiter = ’Kienzle’);
Auch die UPDATE-Anweisung kann auf vielerlei Arten mit SELECT verknüpft werden. Sollen z.B. die in der Tabelle Versetzung aufgeführten Mitarbeiter in andere Abteilungen versetzt werden, so kann das wie folgt geschehen:
KAPITEL 3. STRUCTURED QUERY LANGUAGE (SQL)
36
UPDATE Mitarbeiter
SET AbtNR =
SELECT AbtNr
FROM Versetzung
WHERE Versetzung.Nr = Mitarbeiter.Nr;
Sollen alle Passagiere einer Tabelle zur Abrechnung von Flugpreisen, die die gleiche
Zahlungsweise und den gleichen Passagiertyp wie der Passagier mit der Nr 4711 haben,
um 5% höhere Ticketpreise bezahlen, so lautet die Anweisung dazu:
UPDATE Reservierung
SET Ticketpreis = Ticketpreis * 1.05
WHERE (Zahlungsweise, Passagiertyp) =
(SELECT Zahlungsweise, Passagiertyp
FROM Reservierung
WHERE PassagierNr = 4711);
Sollen alle Mitarbeiter aus gewinnbringenden Abteilungen, deren Abteilungsnummern
in der Tabelle Gute_Abteilung verzeichnet sind, 10% mehr Gehalt in der Tabelle Mitarbeiter bekommen, so lautet die Anweisung:
UPDATE Mitarbeiter
SET Gehalt = Gehalt * 1.1
WHERE AbtNr IN
(SELECT *
FROM Gute_Abteilung);
Während die SELECT-Anweisung in der vorigen Anweisung mehrere Zeilen zurückliefern kann, zeigt das vorletzte Beispiel, daß die SELECT-Anweisung auch eine oder
keine Zeile in der WHERE-Klausel des UPDATE zurückliefern kann.
3.8
ALTER: Datenbankobjekte ändern
ALTER-Befehle ermöglichen es, Datenbankobjekte zu ändern. Der Befehl ALTER
PASSWORD ändert z.B. das Passwort eines Benutzers:
ALTER PASSWORD altespasswort TO neuespasswort
Mit dem Befehl ALTER USER kann der Datenbankadministrator die Eigenschaften
eines Benutzers ändern, z.B. ob er sich nur einmal oder mehrmals gleichzeitig bei der
Datenbank anmelden darf.
Der wichtigste Befehl für den Anwender ist sicherlich ALTER TABLE, mit dem
die Struktur einer Tabelle geändert werden kann. So ist es möglich Spalten aus einer
Tabelle zu löschen oder neue Spalten hinzuzufügen. Hierzu folgende Beispiele:
ALTER TABLE kunde
DROP ort;
ALTER TABLE kunde
ADD umsatz FIXED(5,2);
Auch der Datentyp einer Spalte kann unter gewissen Bedingungen geändert werden.
Dabei muss jedoch der neue Datentyp zum alten Datentyp kompatibel sein, genauer:
KAPITEL 3. STRUCTURED QUERY LANGUAGE (SQL)
37
1. [VAR]CHAR(n) kann in [VAR]CHAR(m) mit m>=n geändert werden.
2. Das Code-Attribut ASCII kann in EBCDIC geändert werden und umgekehrt.
3. FIXED(p,s) kann in FIXED(m,n) mit m>=p und n>=s und m-n>=p-s geändert
werden.
4. FIXED(p,s) kann in FLOAT(m) mit m>=p geändert werden.
5. FLOAT(p) kann in FLOAT(m) mit m>=p geändert werden.
Wenn die Spalte name in der Tabelle Kunde z.B. den Typ CHAR(40) hat und diese Anzahl von Zeichen nicht mehr ausreicht, kann der Datentyp dieser Spalte mit folgender
Anweisung geändert werden.
ALTER TABLE kunde
COLUMN name CHAR(80);
3.9
Multimedia-Unterstützung
Im Standard SQL2 ist die Unterstützung für Multimedia-Anwendungen nur gering. Es
gibt aber weitergehende Erweiterungen einzelner Implementationen für Multimedia.
3.9.1
LIKE-Prädikat
Zur Suche in Zeichenkettendaten existiert das LIKE-Prädikat. Hierbei können einzelne
Zeichen durch die Wildcard _ und ganze Zeichenfolgen (auch aus keinem Zeichen) mit
% symbolisiert werden.
SELECT *
FROM Mitarbeiter
WHERE Name LIKE ’M%’;
Die folgende Anweisung zeigt, daß auch die Wildcardzeichen in Zeichenketten abgefragt werden können. Hierzu wird ein Escape-Zeichen verwendet, das dem WildcardZeichen vorangestellt wird. Das Escape-Zeichen wird mit einer ESCAPE-Klausel definiert. Es wird nicht selber gesucht, sondern zeigt nur an, daß das nachfolgende Zeichen
nicht als Wildcard-Zeichen zu interpretieren ist. Die nachfolgende Anweisung findet
z.B. den Mitarbeiter mit dem Namen Martin%Schmidt.
SELECT *
FROM Mitarbeiter
WHERE Name LIKE ’M%#%%’ESCAPE ’#’;
Mustervergleiche sind auch mit NOT LIKE möglich.
3.9.2
Volltext Datenbanken
Die Unterstützung von Textrecherchen fällt mit LIKE in SQL2 eher dürftig aus. Außerdem ist die Textl#nge des Datentyp CHAR meistens auf wenige Tausend Byte beschränkt. Eine Volltext-Datenbank kann sehr viel umfangreichere Texte bis in den
Bereich von Milliarden Bytes verwalten. Zudem sind bessere Suchstrategien möglich.
Solche Volltext-Datenbanken kommen z.B. bei folgenden Anwendungen zum Einsatz:
KAPITEL 3. STRUCTURED QUERY LANGUAGE (SQL)
38
Bibliothekssysteme
Dokumentverwaltungssysteme
Redaktionssysteme
Das Datenbanksystem TransBase bietet z.B. ein CONTAINS-Prädikat zur Voltextsuche
an. CONTAINS beinhaltet die Funktionalität von LIKE, kann aber auch komplizierter
Boole’sche Ausdrücke bearbeiten und Phrasenrecherchen durchführen. Es folgt ein
Beispiel für einen Boole’schen Ausdruck:
SELECT *
FROM Buch
WHERE Thema CONTAINS
((’database’ NOT ’object’) OR (’SQL’ NOT ’4GL’));
Bei einer Phrasenrecherche können zusammenhängende Wortpassagen gesucht werden. Dabei können zusätzlich auch die Distanzen zwischen den Worten angegeben
werden.
SELECT *
FROM Buch
WHERE Thema CONTAINS
(’database’ [1, 3] ’object’);
3.9.3
Datentyp BLOB
Das Akronym BLOB steht für Binary Large Object. Hiermit ist gemeint, daß die Datenbank unter diesem Typ ein beliebiges Binärobjekt speichern kann. Dies können z.B.
Dokumente, Bilder, Filme und Programme sein.
BLOB’s werden von den Datenbanksystemen nicht interpretiert, d.h. sie tragen
keine eigene Semantik. Deshalb werden sie mit anderen Attributen zusammen gespeichert. Als Beispiel folgt die Definition einer Tabelle in der Bilder gespeichert sind:
CREATE TABLE Bilder (
Nr INT NOT NULL,
Bezeichnung CHAR(80),
Bild BLOB,
PRIMARY KEY (Nr));
Die Interpretation eines BLOB geschieht also durch die Applikation, die mit der Datenbank zusammenarbeitet.Folgende Operationen sind auf BLOB’s jedoch meist möglich:
Einfügen, Löschen und Ändern
Test auf NULL
Bestimmung der Länge in Bytes
Kapitel 4
Normalformen und
Designfragen relationaler
Datenbanken
Die semantische Korrektheit oder Konsistenz einer Datenbank kann durch die Beachtung von Integritätsregeln beim Ändern der Relationen gesichert werden. Beispiele für
Integritätsbedingungen sind:
Gehälter sind immer positive Zahlen
Mitarbeiternummern sind eindeutige Kennzeichen
Solche Integritätsbedingungen werden vom Anwender aufgestellt und vom Datenbanksystem automatisch überwacht. Änderungen, die den aufgestellten Integritätsbedingungen widersprechen, werden abgelehnt.
In SQL können Integritätsbedingungen bei der Definition einer Tabelle oder eines
View angegeben werden. Beispiele für Integritätsbedingungen sind:
Primärschlüssel-Definition:
PRIMARY KEY(Attributliste)
Sekundärschlüssel-Definitionen:
UNIQUE(Attributliste1),
UNIQUE(Attributliste2),
...
Fremdschlüssel-Definitionen:
FOREIGNKEY(Attributliste1)
REFERENCES Tabellenname1(Fremdattributliste1),
FOREIGNKEY(Attributliste2)
REFERENCES Tabellenname2(Fremdattributliste2),
...
Attribut-Wertebereich oder Typ:
39
KAPITEL 4. NORMALFORMEN UND DESIGNFRAGEN RELATIONALER DATENBANKEN40
INTEGER, REAL, FIXED(5,2), ...
Attribut-Bedingungen:
Attributname Typ [NOT NULL | UNIQUE]
DEFAULT [Konstante, NULL]
CHECK-Klauseln:
CHECK(Gehalt > 0)
Man kann einer CHECK-Klausel zur besseren Fehlerdiagnose bei der Verletzung der
Regel optional einen CONSTRAINT-Namen geben:
CREATE TABLE Test (
Artikel CHAR(80)
...
CONSTRAINT check_artikel
CHECK (Artikel IN (’Mantel’, ’Hemd’))
);
Weiterhin macht es Sinn CHECK-Klauseln mit CONSTRAINT-Namen zu versehen,
wenn Sie z.B. nach der Definition der Tabelle mit ALTER noch abgeändert werden
sollen.
Es sind auch CHECK-Klauseln zwischen verschiedenen Relationen in Form von
Unterabfragen möglich. Nehmen wir an wir haben zwei Tabellen Alte_Termine und
Neue_Termine. Dann kann überwacht werden, dass in Neue_Termine kein Termin aus
Alte_Termine auftaucht:
CREATE TABLE Alte_Termine (
Termin date NOT NULL,
...
);
CREATE TABLE Neue_Termine (
Termin date NOT NULL,
...
CHECK(Termin <>
ALL(SELECT Termin FROM Alte_Termine))
);
4.1
Funktionale Datenabhängigkeiten
Ein Spezialfall von Abhängigkeiten zwischen Daten sind die funktionalen Datenabhängigkeiten (Functional Dependencies, FD). Sie sind intrarelationale Integritätsbedingungen, d.h. sie beziehen sich auf genau eine Relation oder Tabelle. Bei der Formulierung
von FD’s wird in Regeln festgehalten, welche Attributwerte welche anderen Attributwerte einer Relation eindeutig bestimmen.
Ein Spezialfall einer FD ist die Abhängigkeit der Relationsattribute von den Schlüsselattributen, denn die Werte des Schlüssels bestimmen die Werte aller anderen Attribute der Relation eindeutig. FD’s können aber nicht nur zwischen Schlüsseln und anderen
Attributen, sondern auch zwischen beliebigen Teilmengen der Attribute bestehen.
KAPITEL 4. NORMALFORMEN UND DESIGNFRAGEN RELATIONALER DATENBANKEN41
Wenn U die Menge aller Attribute der Relation R ist, und X, Y Teilmengen von U
sind, so schreibt man: X ‘ Y, wenn die Attributwerte von X die Attributwerte von Y
eindeutig bestimmen. Dies kann man auch noch anders formulieren: Eine Selektion
bezüglich bestimmter Werte von X liefert in jedem Fall höchstens eine Wertausprägung
von Attributen von Y. Dazu betrachten wir nun folgendes Beispiel:
LieferantName
Schmidt
Schmidt
Meier
Müller
LieferantOrt
Friedberg
Friedberg
Butzbach
Gießen
Posten
Ordner
Folien
Ordner
Folienstifte
Preis
2.50
10.00
2.30
3.00
Der Schlüssel dieser Relation besteht aus der Kombination von LieferantName und
Posten, denn alle anderen Attributwerte der Relation werden durch die Werte dieser
Schlüsselattribute bestimmt und es kann kein Attribut des Schlüssels weggelassen werden,#Mohne
Eigenschaft
gilt
’‡“•”—–+”™das
˜—DJšdiese
%o›DJœF
”
M%o”™šverloren
5 #M’"“Ÿ”—geht.
–+”™˜DJEs
š% C
˜% also˜—”™die
“oO5 FD:
.
f ž
‘
f
Wir
erkennen
aber
noch
eine
weitere
FD:
’"“Ÿ”—–+”™˜—D¡š%o›D¡œ” ’"“Ÿ”—–+”™˜—D¡š% C˜%
‘
.
Diese FD führt zu einer Redundanz, denn der Ort eines Lieferanten wird nicht nur
zum Namen des Lieferanten, sondern zu allen seinen Posten gespeichert. Entsprechend
entstehen Anomalien beim Update der Tabelle.
Wie wir sehen werden, bewirken bestimmte Arten von FD’s genau solche Probleme. In der Theorie der Normalformen versucht man nun, FD’s zu klassifizieren
die Redundanzen und Anomalien bewirken. Weiterhin wird dann angegeben, welche
Arten von FD’s in Relationen nicht enthalten sein dürfen, um eine bestimmte Redundanzfreiheit zu erreichen.
4.2
Normalformen
Die Abhängigkiet von Nichtschlüsselattributen wie LieferantOrt im vorigen Beispiel
von Teilen eines Schlüssels wie LieferantName führt wie wir gesehen haben zu Redundanzen und Anomalien. Ursache der Redundanzen und Anomalien ist, dass der Ort
eines Lieferanten zwar von dessen Namen aber nicht vom Posten abhängt. Die Probleme kommen also daher, dass man in einer Relation Zusammenhänge, die eigentlich
voneinander unabhängig sind untergebracht
’‡“Ÿ”–+”™hat.
˜—DJšWürde
%o›DJœF”man die
’"“Ÿ”—obige
–+”M˜—DJšRelation
% C˜% in zwei
in eine eiTeilrelationen aufteilen, wobei die FD
‘
genständige Relation untergebracht wird, so wären die Redundanzprobleme behoben:
LieferantName
Schmidt
Schmidt
Meier
Müller
Posten
Ordner
Folien
Ordner
Folienstift
Preis
2.50
10.00
2.30
3.00
KAPITEL 4. NORMALFORMEN UND DESIGNFRAGEN RELATIONALER DATENBANKEN42
LieferantName
Schmidt
Meier
Müller
LieferantOrt
Friedberg
Butzbach
Gießen
Funktionale Abhängigkeiten von Teilschlüsseln können also als eigenständige Relationen aufgefasst werden. Die Tabellen der obigen Zerlegung der Ursprungsrelation
sind in 2. Normalform, denn die Definition der 2. Normalform besagt:
2. Normalform: Nichtschlüsselattribute einer Relation dürfen nicht von Teilen eines Schlüssels abhängen.
Die 1. Normalform besagt, dass Attribute nur elementar also nicht zusammengesetzt sein dürfen. Der Wert eines Attributs darf also keine Menge, Vektor, Struktur oder
ähnliches sein. Die 1. Normalform hat also nichts mit Redundanzen in einer Relation
zu tun.
Auch wenn eine Relation oder ein Relationenschema in 2. Normalform ist, können
durch transitive FD’s Redundanzen und Anomalien entstehen. Dazu ein Beispiel: In
einer Relation über Abteilungen wird zu jeder Abteilung neben dem Abteilungsleiter
auch die Gebäude-Nummer, in der die Abteilung untergebracht ist, sowie der Hausmeister, der für das Gebäude zuständig ist, gespeichert:
Abt.Nr.
1
2
3
4
Abt.Name
Personal
Finanzen
Produktion
Verkauf
Abt.Leiter
Hauser
Meier
Kienzle
Glatt
Geb.Nr.
1
1
2
2
Hausmeister
Kaczmarek
Kaczmarek
Müller
Müller
Hier wird deutlich, dass mehrere Abteilungen in einem Gebäude untergebracht sein
können. Andererseits ist ein Hausmeister für ein oder sogar mehrere Gebäude zuständig. Der Hausmeister hängt direkt von der Nummer des Gebäudes ab, für das er zuständig ist. Jedoch hängt der Hausmeister nur indirekt, d.h. transitiv, von der Nummer
der Abteilung ab, die in einem Gebäude untergebracht ist. Hierdurch entsteht eine
Redundanz, da der Hausmeister mit jeder Abteilung aufgeführt wird, wohingegen die
Speicherung des Hausmeisters zur Gebäude-Nummer ausreichen würde. Durch die
Redundanz entstehen wieder Anomalien:
Insert-Anomalie: Ein Hausmeister ist nur mit einer Abteilung einfügbar.
Delete-Anomalie: Wird die letzte Abteilung in einem Gebäude gelöscht, so ist auch
die Information über Gebäude-Nummer und Hausmeister verloren.
Update-Anomalie: Der Hausmeistername muss zu allen Abteilungen eines Gebäudes
gelöscht werden.
Die funktionalen Abhängigkeiten
.A¢£zeigen
% ›…˜ die.ATransitivität,
¢£% ›DJœF” .A¢£die
% ›…die
˜ Redundanz
.C¢£% ’‰”™“X%ound
”™˜ .Adie
¢£% ›…˜
Anomalien
verursacht
deutlich:
]
J
]
‘
]
]
¡
]
‘
]
]
DJ*+Mœ”M“oM%o”™˜
¤ ”—¢ ›…˜ ¤ ”—¢ ›…˜
]
]
‘|¥
Daher wird bei der 3. Normalform gefordert, dass Nichtschlüsselattribute nur direkt
- also nicht transitiv - von Schlüsseln abhängen dürfen. Ein Relationenschema in 3.
Normalform entsteht, wenn transitive Abhängigkeiten von Nichtschlüsselattributen in
neue Relationen abgebildet werden, z.B in eine Relation Abteilung mit den Attributen
Abt.Nr, Abt.Name, Abt.Leiter und Geb.Nr. und eine Relation Hausmeister mit den
Attributen Geb.Nr. und Hausmeister:
]O‘
KAPITEL 4. NORMALFORMEN UND DESIGNFRAGEN RELATIONALER DATENBANKEN43
Abt.Nr.
1
2
3
4
Abt.Name
Personal
Finanzen
Produktion
Verkauf
Geb.Nr.
1
2
Abt.Leiter
Hauser
Meier
Kienzle
Glatt
Geb.Nr.
1
1
2
2
Hausmeister
Kaczmarek
Müller
Die sogenannte Boyce-Codd-Normalform (BCNF) ist eine Verschärfung der 3.
Normalform: Hier dürfen alle Attribute also auch Schlüsselattribute nur direkt - also nicht transitiv - von Schlüsseln abhängen.
Manchmal hat eine Relation eine Bedeutung, die zwei durch logisches UND verknüpfte Aussagen enthält. Ein Angestellter (der Familienvater ist) hat ein oder mehrere
Fachgebiete und ein oder mehrere Kinder. Jedoch gibt es keine Beziehung zwischen
Kindern und Fachgebieten. Es muss aber jedes Kind zu jedem Fachgebiet aufgeführt
werden. Hierdurch entsteht Redundanz:
Angestellter
Hilbert
Hilbert
Pythagoras
Pythagoras
Pythagoras
Pythagoras
Turing
Kind
Hilda
Hilda
Peter
Paul
Peter
Paul
Fritz
Fachgebiet
Mathematik
Physik
Mathematik
Mathematik
Philosophie
Philosophie
Informatik
Wir sehen, dass für jedes Kind alle Fachgebiete und für jedes Fachgebiet alle Kinder in der Tabelle verzeichnet sind. Dies bezeichnet man mathematisch als Austauscheigenschaft, d.h. man kann Kinder und Fachgebiete eines Angestellten beliebig
austauschen, wobei das Austauschergebnis wieder in der Tabelle liegt. Neben der Austauscheigenschaft erkennen wir, dass der Angestellte die Werte der Kinder und der
Fachgebiete bestimmt. Solche Abhängigkeiten nennt man mehrwertig, da sie keine
Funktionen darstellen.
Man
Abhängigkeit (Multi Valued Dependency,
.Uš+¦k”—definiert
&%o”§§%o”™˜ die mehrwertige
“•š(©
- .Uš+MVD)
¦k”—M%o”™§§%o”™˜™/h!
als:
wenn
für
alle
Zeilen
der
Relation
mit
,
‘
|
‘
¨
ª
¬
«
ª
- .Uš+¦k”—&%o”§§%o”™˜™/
- .Uš+¦k”—M%o”™§§%o”™˜™/! - .Uš+¦k”—&%o”§§%o”™˜™/
«
- “Xš(©O/heine
! - Zeile
“Xš(©O­ / existiert,
- ® D6mit
¯&°k¦k­”—¢£“Ÿ”™%•/9! - ® DJ¯M°k¦k”—¢£ª “Ÿ”M%•/
ª ¨
«
und ­ ¨
und ­
.
­ ist also neben dem Angestellten aus dem Kind-Teil von ª und dem FachgebietTeil von « aufgebaut, d.h. alle Vertauschungen bzgl. Kind und Fachgebiet kommen
wieder in der Relation vor. Dies nennt man auch Abgeschlossenheit.
Allgemein formuliert heißt dieses, wenn ‹ das Universum aller Attribute ist und w und n - Teilmengen
/C! - / der Attribute sind: w±
- ‘,/C‘²
! n - genau
/C! dann
- / wenn für
- / alle
! ª- £« /
mit
ª w
« w und ­ - n
- ª w /! - « w ein
/ ­ existiert mit ­ w
/(ª ! n - und
­ ‹Z³´/ n
« ‹1³µn ] Die letzte Forderung ist gleichbedeutent mit ­ ‹P³—n w
« ‹1³
nZw ]
Nebenbei bemerkt gilt wŠ‘G‘n genau dann wenn w¶‘,‘±‹S³n .
Die Forderung der 4. Normalform besagt nun, dass eine mehrwertige Abhängigkeit
KAPITEL 4. NORMALFORMEN UND DESIGNFRAGEN RELATIONALER DATENBANKEN44
w·‘,‘n
nur dann existieren darf, wenn w ein Schlüsselkandidat ist.
In unserem Beispiel sehen wir, !¸
dass.`š+
der
¦6”—MSchlüssel
%o”§§%o”™˜ der Relation aus allen drei Attributen besteht. Das Attribut w
!€.Uš+¦k”istM%o”also
§§%o”™˜µkein
d Schlüsselkandidat.
“•š(©
!
w
n
¨
Deswegen
werden
zwei
Spaltrelationen
und ‹p³Un
.Uš+¦k”—M%o”™§§%o”™˜Cd[® D6¯&°¦6”—¢£“Ÿ”™%
gebildet. Der Verbund dieser Relationen ergibt wieder
die Ausgangsrelation,
aber
die
nicht mehr die mehrwertige Ab!1.`š+¦6”—M%o”§§%o”™˜ Relationen
“Xš(©Penthalten
!
‘G‘¨
n .
hängigkeit w
Angestellter
Hilbert
Pythagoras
Pythagoras
Turing
Angestellter
Hilbert
Pythagoras
Pythagoras
Turing
4.3
Kind
Hilda
Peter
Paul
Peter
Fachgebiet
Mathematik
Mathematik
Philosophie
Informatik
Regelkalkül funktionaler Datenabhängigkeiten
Durch ein formales Regelkalkül lassen sich aus funktionalen Datenabhängigkeiten weitere ableiten. Dabei gelten folgende Regeln, wenn w¬n‡¹l Attributmengen einer Relation sind:
1.
2.
3.
4.
5.
6.
7.
wŠ‘ºw Reflexivität
!{
d
wŠ‘n
w l4‘|n
!{
d
wŠ‘n‰w·‘±l
wŠ‘|n l Akkumulation
d !{
wŠ‘n l
w·‘|n Projektion
!({
wŠ‘n‰fn~‘»l
w·‘±l
d
!({
d
wŠ‘n‰fn l1‘|ˆ
w l1‘|ˆ
d
!({
d
wŠ‘n lU£l1‘¼ˆ
w¶‘n ˆ
Eine funktionale Abhängigkeit ist nun ableitbar, wenn sie ® über die Gesetze 1 bis 7
gewonnen werden kann. Die Menge aller aus einer Menge von
® ½ erkannten funktionalen Abhängigkeiten ableitbaren Abhängigkeiten nennen wir
(mathematisch die
transitive Hülle).
Das Erkennen von funktionalen Abhängigkeiten ist nicht automatisierbar, da es aus
der Semantik der Relation folgt. Jedoch kann ein Algorithmus
angegeben werden, mit
® ½
dem alle ableitbaren funktionalen Abhängigkeiten
ermittelt werden können.
Weiterhin unterscheiden führen wir nun neu® den Begriff der Folgerung ein. Eine
funktionale
Abhängigkeit heißt Folgerung von , wenn sie für alle Relationen, die
®
erfüllen, richtig (gültig) ist. Umgekehrt ist eine funktionale Abhängigkeit w¼‘¾n
KAPITEL 4. NORMALFORMEN UND DESIGNFRAGEN RELATIONALER DATENBANKEN45
®
keine Folgerung von , wenn eine Relation gefunden werden kann, die F erfüllt, aber
nicht w·‘n .
Nun stellt sich die Frage, ob eine Ableitung
® nach den Regeln 1 bis 7 eine funktionale Abhängigkeit erzeugt, die Folgerung in ® ist. Eine weitere Frage ist die nach der
Vollständigkeit, d.h. kann jede Folgerung aus auch formal abgeleitet werden?
Eine Anwort auf diese® Fragen liefert der Satz von Armstrong; er besagt: Jede Folgerung aus einer Menge von funktionalen Abhängigkeiten kann mit den Regeln 1
bis 7 abgeleitet werden. Der Beweis zu diesem Satz kann der angegebenen Literatur
entnommen werden.
® ½
Ein Algorithmus Closure zur Erzeugung von
- der Bernstein-Algorithmus - arbeitet mit sogenannten RAP-Ableitungen und benutzt dazu nur die Regeln 1, 3 und 4
(Reflexivität, Akkumulation und Projektion). Zu einer Menge w von Attributen werden alle ableitbaren funktionalen Abhängigkeiten, wie folgt gesucht:
1.
!
¿ÁÀZ
w
Reflexivität
®
2. Durchlaufe alle funktionalen Abhängigkeiten von und füge alle Attribute rechter Seiten zu ¿ÁÀZ hinzu, deren Attribute in der linken Seite in ¿
ÀZ enthalten
sind. Streiche danach die verwendeten funktionalen Abhängigkeiten.
3. Gehe zu 2. wenn ¿ÁÀP
vergrößert wurde in 2., sonst Ende.
® ½
Die Hülle
aller ableitbaren Abhängigkeiten kann nun
½ erzeugt werden, indem für
‚
w
4
m
‹
w
jede Attributteilmenge
untersucht
wird,
wie
®$!R#™.`3
@ .
® @ aussieht. ®
5
.A32 ½
¤ 32@
‘ ¿Â‘ ‘ ‘¿f¿ ‘|À soll
Beispiel: Zu
ermittelt werden.
.`3
.A3
‘
Reflexivität
.`3
Anwendung von
.`3
.A32@
‘
.A32@)®
‘
32@
‘
.A32@
@
®
¿
.
Anwendung von
.`3
‘
.A32@
‘
®
Akkumulation
Anwendung von
.`3
@
Akkumulation
Anwendung von
.`3
‘
¿
® ¤
Anwendung von ¿
‘¿
Akkumulation
¿y‘
¤
®
‘|À
.A32@
® ¤
‘
¿ÁÀ
.A32 ½ !4.`3v@
® ¤
¿ÁÀ
Also ist
.`3
Durch
können
nun die Regeln
.A3 Projektionen
® .A3
¤
ÀÃ
‘ ‘
abgeleitet werden.
.`3
.A3
‘
@ `
. 3
‘
In diesem Fall ist
also zunächst ein .`
Schlüsselkandidat.
Da weder
3
sich alleine Schlüsselkandidaten sind, ist
sogar ein Schlüssel.
.
¿
.`3
noch
3
‘
für
KAPITEL 4. NORMALFORMEN UND DESIGNFRAGEN RELATIONALER DATENBANKEN46
Bemerkungen:
1. Der Aufwand von Closure pro abzuleitender Attributmenge w ist quadratisch in
®
~ÄJ' ®Á' ÅMÆ
der Größe des Inputs der funktionalen Abhängigkeiten , also
, denn
bei jedem Schleifendurchlauf wird' ®Á
mindestens
eine Regel aus F verbraucht und
'
für jeden Durchlauf sind maximal
-Vergleiche notwendig.
2. Neben einem Konstruktionsverfahren liefert Closure ein Entscheidungsverfah® ½
ren, ob eine funktionale
Abhängigkeit wǑÈn , die vorgegeben wird, in
½
liegt, indem w
konstruiert wird.
®
wer3. Weiterhin liefert Closure auch eine
® Antwort auf die Frage, ob verkleinert
®
den kann, d.h. ob Regeln aus redundant sind. Eine Menge von funktionalen
® # Abhängigkeiten
5™ ½ !1® heißt
½ redundant, wenn es Regeln wɑÊn gibt, so dass
³ w·‘n
ist.
Um eine
® redundanzfreie Menge von funktionalen Abhängigkeiten zu erhalten, kann
man solange verkürzen, bis keine Abhängigkeit mehr weggelassen werden kann.
4.4
Verlustfreie und abhängigkeitsbewahrende Zerlegungen
Das Ziel des weiteren Vorgehens ist, ein Verfahren anzugeben, mit dem eine Relation
so in Teilrelationen aufgespalten wird, dass die Teilrelationen in 3. Normalform sind,
wobei die ursprüngliche Relation durch Verbund der Teilrelationen wiedergewonnen
werden kann.
Bemerkung: Der Verbund einer Zerlegung umfasst immer die ursprüngliche Relation,
er kann jedoch mehr Zeilen enthalten als die ursprüngliche Relation.
Beispiel:
Relation Lieferanten:
LName
Schmidt
Meier
LOrt
Friedberg
Friedberg
Posten
Ordner
Folien
Preis
2.50
10.00
Die Relation Lieferanten wird wie folgt aufgespalten:
LName
Schmidt
Meier
LOrt
Friedberg
Friedberg
LOrt
Friedberg
Friedberg
Posten
Ordner
Folien
Preis
2.50
10.00
KAPITEL 4. NORMALFORMEN UND DESIGNFRAGEN RELATIONALER DATENBANKEN47
Der Verbund beider Relationen ergibt nun nicht mehr die Ausgangsrelation:
LName
Schmidt
Schmidt
Meier
Meier
LOrt
Friedberg
Friedberg
Friedberg
Friedberg
Posten
Ordner
Folien
Ordner
Folien
Preis
2.50
10.00
2.50
10.00
Die ursprüngliche Relation kann also nicht wiedergewonnen werden. Da die Verbundrelation mehr Zeilen enthält als die Ausgangsrelation werden die Informationen
ungenauer. Man spricht von Informationsverlust. Der Grund dafür ist, dass das Attribut
(allgemein die Attributmenge), das beide Teilrelationen gemeinsam haben, die restlichen Attribute mindestens einer Teilrelation nicht eindeutig (funktional) bestimmt.
Daher liegt es nahe zu fordern, dass (mindestens) eine Teilrelation einen Schlüsselkandidaten der anderen Teilrelation enthält.
\
Eine Zerlegung einer Relation in zwei Teilrelationen
r!t \ und
VW) Å Å heißt verlustfreie Verbund-Zerlegung
(Lossless Join, LLJ),p
wenn
\
Å
Ëc{gilt
\ Å
\ . Å
)\
und
sind
eine
LLJ-Zerlegung
von
x
‘
³
oder
x
Å Å )\
‘
³ .
Beispiel:
LName
Schmidt
Meier
LName
Schmidt
Meier
LOrt
Friedberg
Friedberg
Posten
Ordner
Folien
Preis
2.50
10.00
)\p!Ì#M’‡›D¡œ” ’ C˜™%£5
!
Dies
mit#M’‰›DJœF” ’‰ C˜%
M%o”™š ˜—und
#M’‡›DJistœ” eine LLJ-Zerlegung,
M%o”™š ˜—”™“ŸO5 denn
‚!‚
”™“o´5 Å
von
, denn
f

ž
f

f

ž
f

\ Å !Í#M’‡›D¡œ”¡5
#M’‰ C˜%£5P!R \ Å
x
‘
³ . Man sieht das der Verbund die
Ausgangsrelation wieder erzeugt. Es gehen also keine Informationen verloren.
Nun gilt folgender fundamentaler
Satz: Jede Relation kann durch LLJ-Zerlegungen in die Boyce-Codd-Normalform überführt werden.
Beweisprinzip (Algorithmus zur Bestimmung der Boyce-Codd-Normalform): In BCNF
hängt jedes Attribut direkt, d.h. ausschließlich
von Schlüsselkandidaten
ab. Für
.
eine funktionale Abhängigkeit w¼‘
der Relation
,
bei
der
kein
Schlüsw
)\0! d2#™.Z5
!t #&. 5
selkandidat ist,
und Å
w ³
\ werden zwei neue Relationen
Å
w
gebildet.
In
ist
nun
Schlüsselkandidat
und
in
fehlt
die
Abhängigkeit
.
w|‘ . Diese Art der Zerlegung wird ggfs. wiederholt. Spätestens, wenn die
Spaltrelationen nur noch aus zwei Attributen bestehen, liegt BCNF vor.
Beispiel:
KAPITEL 4. NORMALFORMEN UND DESIGNFRAGEN RELATIONALER DATENBANKEN48
Eine Firma speichert ihre Lieferanten mit Ort und Entfernung des Lieferanten, sowie
Î!²der
#’"“Ÿ”—Teile
–+”™˜DJund
š% u`Anzahl
”™“•§ .`š(Ï¡von
Dk°§ Teilen,
C˜% die 5 er liefert in folgender RelaÎ!
tion
¬¨sÐ . Wir schreiben kurz
#M’ u . 5
f¨ .
Hieraus
#™ 25 # 2sind
5 folgende
# 5 funktionale Abhängigkeiten ersichtlich:
#M’U5 #
‘ ¨ und die transitive Abhängigkeit
‘ ¨
Man sieht leicht, dass
#M’"u 5
ein Schlüssel von
#™’"uZ5 #. 5 #™’05
‘
‘
5
.
ist.
’‡“•”—–+”™˜—DJš%
Ð ˜% müssen zu
Weiterhin erkennt man folgende Redundanzen: ¨s C
gespeichert werden, obwohl die Speicherung zum
ausreichen
würde,
wenn
C˜%
u`”™“Ÿ§
mehrere Lieferanten aus dem gleichen Ort stammen.
muss zu jedem
gespeichert werden, obwohl der Ort nur vom Lieferanten abhängt.
Aus den Redundanzen folgen wie gehabt Anomalien für Insert, Delete und Update.
#’"u 5
#M’U5
#™ 25
#
5
‘
‘ ¨ des NichtschlüsselWegen der# transitiven
Abhängigkeit
5
#™’ u 5 ‘
ist nicht in 3. Normalform.
Attributs ¨
vom Schlüssel
Wegen
#M’U5
# 25
‘
, ist
sogar nicht in 2. Normalform.
ist also in 1. Normalform.
Zerlegt man nach dem LLJ-Verfahren, so ergeben sich drei verschiedene Zerlegungsvorgänge, die sich durch die Wahl der Anfangszerlegung unterscheiden
und zu zwei unterschiedlichen Zerlegungen in BCNF führen:
#™’05 # 25 \ !Ñ#M’ 25 Å !Ñ#™’ u .
5
‘ #M’ : 5
ŬŠ!$#M ’ u .Z5 ¬¨ ;
\Å !$
f¨ und
.
# 25 # 5 )\0!$#™ 5 Å !$#M’ u . 25
Beginn mit
¨ #’ u .Z5 ;
der Abhängigkeit \ ‘!$#M’ ¨ 2: 5
f!R
und ŬÅ
.
nun wird Å weiter zerlegt in Å
#M’05 # 5 )\`!R#’ 5 Å !~#M’ u . 25
Beginn mit
der Abhängigkeit \P‘ !Ò#™¨ ’ 2: 5
f¨ !Ò# ’ u .Z5 ;
nun wird Å weiter zerlegt in Å
und ŬÅ
. Dies führt
1. Beginn mit
der Abhängigkeit nun wird Å weiter zerlegt in
2.
3.
zur gleichen Zerlegung wie beim ersten Zerlegungsvorgang.
!R#’ 25 !$#™’
5 )Óµ!$#™’ u . 5
e
¬ Die Zerlegung =
jedoch
’"“Ÿ ”—–+ ”™˜DJšbeinhaltet
%o”™š
wieder Redundanzen,
denn
[
¨
Ð
werden
wieder
zu
gespeichert,
C˜%
obwohl sie von
direkt abhängen. Entsprechend ergeben
C˜™sich
% wieder Anomalien. Außerdem
muss
bei
einer
Anfrage
der
Attribute
und ¨[Ð der
= !¼#M’ 25sVXWS#M’ ¬¨ 5[!‚ e gebildet werden, da die AbhängigVerbund
# 25
# 5
keit
‘ ¨ durch die Zerlegung#—verloren
25
# geht.
5 Man sagt diese Zerlegung
ist nicht abhängigkeitsbewahrend, da
‘ ¨ nicht aus den funktionalen
Abhängigkeiten,
#™ 25 # die
5ÁÔ in#™den
’ Spaltrelationen
’
’‡u verzeichnet
.Z5 ½ sind, gewonnen werden
kann:
.
‘ ¨ ‘ ‘¨ ‘
!º#M’ 25 !º# 5 Ó !‚#™’ u . 5
Die Zerlegung =
e
f¨ Redundanzen nicht, denn sie ist abhängigkeitsbewahrend.
enthält solche
Wir streben also an, eine Zerlegung als Relationenschema zu erhalten, die abhängigkeitsbewahrend ist. Ein Verfahren, das eine Relation direkt verlustfrei und abhängigkeitsbewahrend in die 3. Normalform zerlegt, arbeitet mit einer sogenannten Basis von
funktionalen Abhängigkeiten. Dabei ist eine Basis wie folgt definiert:
KAPITEL 4. NORMALFORMEN UND DESIGNFRAGEN RELATIONALER DATENBANKEN49
1. eine Basis ist nicht redundant,
® ½ d.h. wird versucht eine Abhängigkeit aus der
Basis wegzulassen, so kann
nicht mehr abgeleitet werden;
.
' .2'µ!»Õ
ist® minimal,
d.h.
wʑ . 5 ½ !t
½
.
w ‘
N
2. jede
#M® Abhängigkeit
.,d B
³‰w¶‘
und für
w
BPÖ
w
gilt
Die Attribute der funktionalen Abhängigkeiten einer solchen Basis bilden nun die Zerlegungsrelationen. Ggfs. muss noch eine Relation mit dem ursprünglichen Schlüssel
der Ausgangsrelation hinzugefügt werden, falls er nicht in den Zerlegungsrelationen
der Basis vorkommt.
Aufgrund der Konstruktion der Basis von funktionalen Abhängigkeiten erhalten
wir eine verlustfreie,
® ½ abhängigkeitsbewahrende Zerlegung, denn aus den Abhängigkeiten der Basis ist
ableitbar und da transitive Abhängigkeiten von NichtschlüsselAttributen redundant sind, dürfen sie nicht vorkommen also sind die Spaltrelationen in
3. Normalform.
Das Verfahren zur Bestimmung einer Basis von funktionalen Abhängigkeiten arbeitet nun dreistufig:
1. Zunächst werden
alle
rechtsminimal durch Projektionen gemacht,
.
' .2
'¡!$Ableitungen
Õ
mit
.
d.h. wŠ‘
2. Anschließend versucht
d.h. man
B(Ö man bei jeder
#M® Regel sukzessive
.,d B w . 5 zu½ verkürzen,
!t® ½
w gilt ³‰wŠ‘
w ‘
untersucht, ob bei w
.
3. Schließlich#versucht
man
½ !4® ½ Ableitungen, die
®
.Z5 sukzessive
. redundant sind, wegzulas³‰w·‘
gilt, kann w¶‘
weggelassen werden.
sen. Wenn
!‚#’ 25 !º#™ 5 Ó !‚#’ u .Z5
Die Zerlegung =
e
¬#M¨ ’U5 # 25 #™ 2 5 # ist5 abhängigkeitsbe#M’ u 5 #M. 5
wahrend, da sie aus den Regeln der
Basis
®×!Ò#M’ u ‘ . ’ ’ ‘ ¨ 5 ‘
besteht. In der Ausgangsmenge
‘ ‘
‘¨ ‘¨ darf die
#M’U5
# 5
transitive Regel
weggelassen
werden,
da
sie
‘
¨
#M’U5 # 5 #™’ u
. ’
5 ½ abgeleitet werden kann aus
der Basis:
‘ ¨
‘ ‘ ‘¨
Kapitel 5
Internet-Datenbanken
5.1
HTML-Grundlagen und Formulare
Formulare erlauben es, Daten vom Browser an den Webserver zu versenden. Auf dem
Rechner des Webservers können dann die Daten verwendet werden, um Berechnungen
z.B. Anfragen in Datenbanken durchzuführen.
Zu einem Formular gehört auch ein Skript-Programm, das als Teil des Webservers
oder als exterer Prozess ausgeführt wird. Eine Standardschnittstelle des Webservers für
externe Skripten ist das Common Gateway Interface (CGI).
Zur Erzeugung eines Formulars dient das <form>-Tag. Man kann in einer htmlSeite mehrere Formulare unterbringen; sie dürfen allerdings nicht verschachtelt sein.
Das einleitende form-Tag enthält zwei Elemente: die Methode für die Übermittlung der
Daten (method) und den Namen des auszuführenden Programms (action). Als Beispiel
folgt eine html-Seite mit einem einfachen Formular:
<html><head>
<title>Sag mir Deinen Namen</title>
</head><body>
<h2>Wer bist Du ?</h2>
<form method = “post” action=”echo.php”>
<p>Eingabe des Namens:
<input type = ”text” name = “der_Name”
size = “40”>
</p>
<p>
<input type = “submit” name = “absenden”>
</p>
</form></body></html>
Erklärungen und Bemerkungen:
<input> kennzeichnet ein Formular-Eingabeelement. Es gibt unterschiedliche
Typen von Eingabeelementen z.B.:
– text (default),
– submit (submit-Button),
50
KAPITEL 5. INTERNET-DATENBANKEN
51
– reset (reset-Button),
– radio (radio-Button),
– check (check-Boxes),
– password (unsichtbarer Text)
– image (Bild)
name bezeichnet den Namen des Eingabeelements und wird mit dem eingegebenen Wert an das Skript-Programm vom Webserver aus übermittelt.
Der submit-Button kann bei einem einzigen Eingabefeld fehlen. Das Absenden
wird dann durch dei Return-Taste aktiviert. Außerdem ist der Name des submitButton optional. Auch ein Wert kann mit dem Namen übergeben werden. Dies
macht bei submit-Buttons mit verschiedenen Werten Sinn:
<input type = “submit” name = “links”
wert = ”l”>
<input type = “submit” name = “rechts”
wert = “r”>
radio-Buttons kennzeichnen eine Liste von Einträgen, von denen nur einer ausgewählt werden kann. Typischerweise benutzt man radio-Buttons in Listen, die
auch verschachtelt sein können:
<ol>
<li><input
value
</li>
<ol>
<li><input
value
</li>
<li><input
value
</li>
</ol>
<li><input
value
</li>
<ol>
<li><input
value
</li>
<li><input
value
</li>
</ol>
</ol>
type = “radio” name = “tier”
= “vogel” checked> Vogel
type = “radio” name = “vogel”
= “papagei”> Papagei
type = “radio” name = “vogel”
= “kolibri”> Kolibri
type = “radio” name = “tier”
= “beuteltier”> Beuteltier
type = “radio” name = “beuteltier”
= “känguruh”> Känguruh
type = “radio” name = “beuteltier”
= “koala”> Koala
check-Boxes können im Gegensatz zu radio-Buttons mehrere Einträge einer Liste als ausgewählt übermitteln:
KAPITEL 5. INTERNET-DATENBANKEN
52
<ul>
<li><input type = “checkbox” name = “rot”> Rot
</li>
<li><input type = “checkbox” name = “grün”> Grün
</li>
<li><input type = “checkbox” name = “blau”> Blau
</li>
</ul>
Per Default wird der Wert “on” bei einer angekreuzten check-Box übergeben.
Man kann den Wert jedoch umsetzen und zusätzlich mit “checked” vorauswählen:
<li><input type = “checkbox” name = “rot”
value = “chosen” checked> Rot
</li>
Abbildungen in Formularen verhalten sich ähnlich wie ein submit-Button, d.h.
die Formularinhalte werden übermittelt. Dabei wird der Wert name zweimal
übermittelt und zwar mit x- und y-Koordinaten innerhalb des ausgewählten Punktes innerhalb des Bildes. Diese Koordinaten können ggfs. die Aktionen des
Server-Programms steuern.
<input type = “image” src = “stern.gif”
name = “stern”>
Der reset-Button dient dazu, die Eingabeelemente eines Formulars auf Vorgabewerte zurückzusetzen. Die Vorgabewerte werden über das value-Attribut bei
Zeichenketten bzw. checked bei Auswahl oder Optionsfeldern angegeben.
<input type = “reset” value = “zurücksetzen”>
Mit dem hidden-Attribut des input-Tag kann ein Feld für die Ausgabe durch den Browser unterdrückt werden. Dies ist z.B. sinnvoll, wenn die Werte eines Formulars über
ein weiteres Formular an ein Skript-Programm weitergegeben werden sollen:
Formular 1
Name: Fritz
Alter: 16
Skript 1
ruft
Name=Fritz
Alter=16
erzeugt
Formular 2
<input name="Name" value="Fritz" hidden>
<input name="Alter" value="16" hidden>
<input name="Beruf">
Skript 2
ruft
Name=Fritz
Alter=16
Beruf=Schüler
Skript 1 erstellt mit den Eingaben aus Formular 1 das Formular 2 und legt diese
Eingaben dort in hidden-Feldern ab. Skript 2 kann dann auch auf die Eingaben von
Formular 1 zugreifen, obwohl sie in Formular 2 nicht angezeigt werden.
Neben den Möglichkeiten mit input Daten zu übermitteln gibt es noch zwei weitere
Tags, die Formularelemente erzeugen.
KAPITEL 5. INTERNET-DATENBANKEN
53
Das select-Tag gestattet es, Pull-Down-Menüs und Listen zu erzeugen. Bei Auswahlmenüs, die von den Browsern meist als Pull-Down-Menüs dargestellt werden kann
immer nur ein Eintrag ausgewählt werden:
<p> Wählen Sie eine Farbe:
<select name = “farbe” size = “2”>
<option> schwarz
<option value=”gelbundblau”> grün
<option selected> rot
</select></p>
Erklärungen und Bemerkungen:
Der Wert des Auswahlmenüs wird in der Variablen mit dem Namen farbe zurückgegeben.
Soll der zurückgegebene Wert von dem am Bildschirm dargestellten Wert abweichen, so kann er mit value angegeben werden.
Mit selected kann ein Wert als Default vorgeschlagen werden.
Mit size kann optional ein Bereich von angezeigten Zeilen im Pull-Down-Menü
ggfs. mit der Möglichkeit zum Blättern angegeben werden.
Mit einem multiple-Attribut des select können auch mehrere Einträge ausgewählt
und übertragen werden:
<p> Einkaufsliste:
<select name=”einkauf” multiple>
<option> Butter
<option> Milch
<option> Käse
<option> Nudeln
<option> Pilze
</select></p>
Mit dem textarea-Tag können grösere Textbereiche in einem Formular eingegeben werden:
<textarea name = “dertext” rows = “20”
cols = “50”>
Geben Sie Ihre Nachricht ein:
</textarea>
Erklärungen und Bemerkungen:
Bei den meisten Browsern wird der Text mit Zeilenvorschüben des Benutzers
zum Webserver weitergegeben. Der Browser Netscape erlaubt über das wrapAttribut unterschiedliche Möglichkeiten des Zeilenumbruchs:
– wrap = off (default): Text auf einer Zeile bis zur Return-Taste.
– wrap = soft: Text passt sich automatisch dem Fenster an, wird aber als eine
einzige Zeile übertragen.
– wrap = hard: Text passt sich automatisch dem Fenster an und Zeilenumbrüche werden mit übertragen.
KAPITEL 5. INTERNET-DATENBANKEN
5.2
54
Common Gateway Interface (CGI)
CGI ist eine Schnittstelle zwischen dem Webserver und anderen Prozessen auf dem
Rechner, auf dem der Webserver läuft. Die Kopplung einer Datenbank mit einem
Webserver ist ein Beispiel für eine CGI-Anwendung. Es können aber beliebige Prozesse über die CGI-Schnittstelle vom Webserver aus angesteuert werden. Die Datenbank
muss nicht notwendigerweise auf dem Rechner des Webserver arbeiten, wenn das aufgerufene CGI-Programm Kontakt zur Datenbank aufnehmen kann. Im unteren Bild
wird die grundsätzliche Funktionsweise von CGI dargestellt:
1. Der Browser fordert eine URL vom Webserver an.
2. Der Webserver erkennt an der URL, dass es sich um ein CGI-Programm handelt
und führt es aus.
3. Das CGI-Programm ruft andere Programme z.B. einen Datenbankserver auf.
4. Der Datenbankserver gibt seine Ausgaben an das CGI-Programm zurück.
5. Das CGI-Programm formatiert eine html-Seite, die es an den Webserver schickt.
6. Der Webserver übermittelt die html-Seite an den Browser.
Das CGI-Programm wird also über eine URL aus der html-Seite vom Webserver gestartet. Die Standardausgabe jedes CGI-Programms ist mit dem Webserver verbunden,
d.h. das CGI-Programm sollte dynmamisch html-Daten produzieren, die der Webserver an den Browser weiterleitet. Damit der Browser erkennen kann, um welche Art
von Daten es sich handelt, muss vor den eigentlichen Daten ein Kopf (header) übertragen werden, der den Typ der Daten anzeigt. Folgende Datentypen werden hierbei am
meisten verwendet:
text/html
text/plain
image/gif
image/jpeg
video/mpeg
application/postskript
Damit ein Programm oder Skript auf unterschiedliche Anforderungen reagieren kann,
können Parameter vom Webserver an das Skript übermittelt werden. Es gibt drei unterschiedliche Möglichkeiten, ein CGI-Programm aus einer html-Seite aufzurufen:
Die erste Möglichkeit arbeitet URL-direkt ohne Formular (d.h. kein form-Tag).
Dabei werden Parameter an den Skriptnamen direkt im a-Tag angehangen. Das ? trennt
den Skriptnamen vom ersten Parameter. Die einzelnen Parameter werden durch + voneinander getrennt. Der Webserver startet das untere Programm mit der Anweisung
arg.cgi arg1 arg2 arg3.
<a href = “arg.cgi?arg1+arg2+arg3”>
starte CGI-Programm arg.cgi </a>
KAPITEL 5. INTERNET-DATENBANKEN
55
Eine einfaches Programm arg.cgi, das die Aufrufparameter zurückspiegelt, kann z.B.
als Korn Shell Skript in folgender Weise formuliert werden:
#!/bin/ksh
echo “Content-type: text/plain”
echo “”
for i in $@
do
echo “$i”
done
Als C-Programm kann arg.cgi z.B. wie folgt aussehen:
#include <stdio.h>
int main (int argc, char **argv) {
int i;
printf(“Content-type: text/plain\n\n”);
for (i = 1; i < argc; i++)
printf(“%s <br>”, argv[i]);
}
Die zweite und dritte Möglichkeit arbeitet jeweils mit Formularen für den Aufruf des
CGI-Programms. Hierbei werden die <input>-Tag Felder in der Form Name=Wert in
der URL-Kodierung vom Browser an den Webserver übermittelt. Eine solche URLKodierung kann z.B. wie folgt aussehen:
Nachname=kremer?Adresse=Wilhelm-LeuschnerStrasse+13
Hierbei kennzeichnet das ? ein Trennsymbol (Delimiter) zwischen zwei Feldern und
das + ein Leerzeichen.
Die zweite und dritte Möglichkeit unterscheiden sich lediglich durch die Art der
Übermittlung der URL-kodierten Name-Wert-Paare vom Webserver an das CGI-Programm.
<form method=”get” action=”argget.cgi”>
Die Get-Methode übermittelt die Parameter in einer Umgebungsvariablen mit dem Namen QUERY_STRING an das CGI-Programm.
<form method=”post” action=”argpost.cgi”>
Die Post-Methode übermittelt die Parameter über die Standardeingabe-Schnittstelle
STDIN an das CGI-Programm.
Da Umgebungsvariablen in ihrer Länge beschränkt sind, sollte man für große Formulare die Post-Methode verwenden. Meist wird bei der Übergabe mit Post eine temporäre Datei benutzt, so dass hier i.d.R. keine Platzprobleme entstehen.
Der entscheidende Unterschied zwischen einem Programm, das mit direkter URLKodierung (<a>-Tag) und eimem Programm, das von einem Formular aus aufgerufen
wird, ist die Notwendigkeit der Dekodierung der Parameter in der URL-Kodierung im
zweiten Fall.
KAPITEL 5. INTERNET-DATENBANKEN
56
Hierfür stehen aber Standard-Dekodierprogramme wie uncgi zur Verfügung. Dann
wird als erstes das Dekodierprogramm gestartet, das das eigentliche CGI-Programm
anschließend aufruft. In diesem Fall wird der Name des CGI-Programms als sogenannte Pfadinformation übergeben, die zwischen dem zu startenden Programm und
dem ersten Parameter steht:
<form method=”post” action=”http://berlin/cgibin/arg.cgi”>
wird zu
<form method=”post” action=”http://berlin/cgibin/uncgi/arg.cgi”>
Hierbei enthält die Umgebungsvariable $PATH_INFO die Zeichenkette arg.cgi. uncgi
startet dann das Programm arg.cgi und übergibt die Formularinhalte in Umgebungsvariablen, die mit www_ beginnen gefolgt von dem Namen des <input>-Tag Feldes im
Formular.
Es gibt also zwei Stellen an denen die CGI-Programmierung routinemäßige Arbeit
erzeugt:
Die URL-Kodierung bei Formularen muss dekodiert werden.
Die html-Seiten für den Browser müssen erzeugt werden.
Dazu kommt bei der Datenbankanbindung noch die Schnittstelle zum Datenbankserver.
Für diese Aufgaben gibt es nicht proprietäre und proprietäre Lösungen für die nun
Beispiele angeführt werden.
5.3
Beispiel 1: MySQL, Apache-Webserver und Personal Home Page (PHP)
MySQL, Adabas, Oracle, Informix und viele andere Datenbankserver können mit Hilfe
der Skript-Sprache PHP mit unterschiedlichen Webservern wie Apache und Internet Information Server verbunden werden. Die Syntax von PHP ist der Programmiersprache
C ähnlich. PHP-Skripten werden in html-Seiten integriert und füllen dort die variablen
Anteile der Seiten.
Ein PHP-Skript kann auf einfache Weise auf Formularvariablen in <input>-Tag
Feldern, auf Umgebungsvariablen, z.B. $PATH_INFO, und auf Aufrufparameter mit
$argv[i] in C-Manier zugreifen.
Für den Zugriff von PHP auf MySQL stehen spezielle Bibliotheksfunktionen zur
Verfügung. Die wichtigsten sind:
int mysql_pconnect(string hostname , string username , string password);
öffnet eine dauerhafte (persistente) Verbindung zum Datenbankserver, die auch erhalten bleibt wenn das PHP-Skript endet. Dies bietet Performance-Vorteile gegenüber
dem Aufruf mysql_connect, wenn mehrfach auf die Datenbank zugegriffen wird. Jedoch muss am Anfang jedes Skriptes erneut mysql_pconnect aufgerufen werden.
KAPITEL 5. INTERNET-DATENBANKEN
57
int mysql_select_db(string database_name, int link_identifier);
wählt zu der Verbindung mit der Kennzahl link_identifier, die z.B. mit mysql_pconnect
erzeugt wurde, eine Datenbank aus. Wenn die Auswahl erfolgreich war, wird TRUE
sonst FALSE zurückgegeben.
int mysql_query(string query, int link_identifier);
sendet die Zeichenkette query als Anfrage zu der Datenbankverbindung link_identifier.
Die Zeichenkette query darf nicht mit ; enden. Der Rückgabewert ist eine Ergebniskennzahl, wenn die Anfrage erfolgreich war bzw. FALSE, wenn die Anfrage syntaktisch oder semantisch falsch ist oder eine Rechteverletzung vorliegt.
Syntaktisch falsch ist z.B.:
select * where x=5
Semantisch falsch ist z.B.:
select col1 from tb
wobei col1 nicht Spalte von tb ist.
array mysql_fetch_row(int result);
speichert eine Zeile des Anfrage-Ergebnisses der Ergebniskennzahl result in einem
Feld, wobei die Spalten der Zeile in unterschiedlichen Feldelementen untergebracht
werden. Aufeinanderfolgende Aufrufe von mysql_fetch_row liefern jeweils die nächste Zeile des Anfrageergebnisses oder FALSE, falls es keine weiteren Zeilen mehr gibt.
Ein Programm-Konstrukt, das jeweils die ersten drei Spalten für alle Zeilen eines Anfrageergebnisses ausgibt, sieht z.B. wie folgt aus:
while (($a=mysql_fetch_row($result))!=FALSE) {
echo “$a[0], $a[1], $a[2]”
}
Ein ähnlicher Aufruf wie mysql_fetch_row ist:
array mysql_fetch_array(int result);
hierbei wird eine Zeile des Abfrageergebnisses in einem Feld mit den Spaltennamen
als assoziative Indices gespeichert. Ein Programm-Konstrukt sieht z.B. wie folgt aus:
while (($a=mysql_fetch_row($result))!=FALSE) {
echo “$a[tiernr], $a[tierart], $a[name]”
}
Um die Anzahl von Zeilen des Anfrageergebnisses zu zählen gibt es den Funktionsaufruf:
int mysql_num_rows(result);
KAPITEL 5. INTERNET-DATENBANKEN
58
Bei Anfragen mit den SQL-Update-Befehlen insert, delete oder update liefert folgende
Funktion die Anzahl betroffener Zeilen zurück:
int mysql_affected_rows(result);
Als Beispiel eines PHP-Skripts in einer html-Seite folgt nun eine einfache selectAnfrage an eine Datenbank:
<html>
<head>
<title>Adressbuch-Anzeige</title>
</head>
<body>
<hr>
<table border>
<th>Firma</th><th>Name</th><th>Vorname</th>
<th>Anschrift</th> <th>Telefon</th>
<th>Fax</th>
<?
$link=mysql_pconnect("localhost","kremer","kremer");
mysql_select_db("kremer",$link);
$query="select * from adressen";
$result=mysql_query($query,$link);
$num = mysql_numrows($result);
for ($i=0; $i < $num; $i++) {
$row=mysql_fetch_array($result);
echo "<tr><td>$row[firma]</td><td>$row[name]</td>
<td>$row[vorname]</td><td>$row[anschrift]</td>
<td>$row[telefon]</td><td>$row[fax]</td></tr>";
}
?>
</table>
</body>
</html>
Man erkennt hier das der variable PHP-Anteil von den Begrenzern <? und ?> umschlossen ist. Außerdem sieht man, dass die html-Kodierung mit echo-Anweisungen
erzeugt wird.
5.4
Beispiel 2: Adabas-WebDB
Bei Adabas steht das proprietäre CGI-Programm genpg.exe zur Verfügung, dem Parameter aus einer vorer aufgerufenen html-Seite übergeben werden können. (Die vorherige html-Seite ist meistens eine andere Seite kann aber auch dieselbe Seite sein, d.h.
die Seite ruft sich selbst.)
<form method=”post”
action=”../cgibin/genpg.exe?../html/query.html”>
KAPITEL 5. INTERNET-DATENBANKEN
59
Die Seite query.html enthält nun in html-Kommentaren Anweisungen an das CGIProgramm genpg.exe.
html-Kommentare sind wie folgt begrenzt:
<!- html-Kommentar ->
genpg.exe-Anweisungen sind wie folgt begrenzt:
<!-- genpg.exe-Anweisung -->
Parameter aus der vorangegangenen Seite, z.B. <input>-Tag Felder eines Formulars
bei der Post-Methode können in SQL-Anweisungen mit Hilfe des Makros $SQL_FV
angesprochen werden.
Natürlich können select-Anfragen an genpg.exe übergeben werden:
<!-- SQL_TXT select count(*) from tables -->
SQL_TXT liefert hierbei nicht html-formatierten Text. Um eine html-Tabelle zu erhalten, genügt z.B. folgender Aufruf von SQL_TAB:
<!-- SQL_TAB select * from tables -->
In dieser Form arbeiten die Anfragen mit einem Default-Benutzer und einer DefaultDatenbank. Will man dieselben Ergebnisse mit einem speziellen Benutzer oder einer
speziellen Datenbank erzielen, so benutzt man:
<!-- SQL_TXT_ADB Rechner:ServerDB:User:Passwort:
select count(*) from tables -->
bzw.
<!-- SQL_TAB_ADB Rechner:ServerDB:User:Passwort:
select * from tables -->
Für Rechner:ServerDB:User:Passwort: müssen natürlich gültige Werte eingetragen
werden, z.B.: localhost:MYDB:prakt1:prakt1:.
Ob eine Suche nicht erfolgreich war, kann durch SQL_ONNOTFOUND erkannt
werden:
<!-- SQL_ONNOTFOUND keine Daten gefunden
$SQL_MSG -->
$SQL_MSG liefert hierbei die Originalmeldung des Datenbankservers.
Will man Update-Operationen wie insert einsetzen, so kann man ebenfalls SQL_TXT
benutzen. Natürlich müssen dann i.d.R. Formularvariablen eingesetzt werden, die man
mit $SQL_FV ansprechen kann. Der erste Parameter von $SQL_FV ist das <input>Tag Feld der vorangehenden html-Seite. Der zweite Parameter bestimmt einen DefaultWert, wenn das <input>-Tag Feld auf der vorangehenden Seite nicht ausgefüllt wurde.
Das folgende Beispiel zeigt eine insert-Operation mit genpg.exe.
<!-- SQL_TXT insert into tier_r values(
$SQL_FV(Tiernr, “0”),
’$SQL_FV(Tierart, “”)’,
...) -->
KAPITEL 5. INTERNET-DATENBANKEN
60
In oberen Beispiel wird Tierart von einfachen Hochkommata umgeben, da es sich um
eine Zeichenkette handelt; Tiernr ist hingegen eine Zahl.
Um zu erkennen, ob die Anweisung erfolgreich war, gibt es folgende Anweisungen:
<!-- SQL_ONERROR Operation nicht erfolgreich
$SQL_MSG -->
<!-- SQL_ONSUCCESS Operation erfolgreich
$SQL_MSG -->
Die flexibelste Möglichkeit, mit genpg.exe Ausgaben zu erzeugen, arbeitet mit der
Funktion SQL_LIST. SQL_LIST führt eine select-Anfrage durch und zeigt das Ergebnis in einem mit SQL_LIST_FORMAT angegebenen Format an.
Um z.B. eine Tabelle auszugeben sind folgende Operationen notwendig:
<!-- SQL_LIST_LEAD <table border>
<tr><th>Tierart</th><th>Name</th></tr> -->
<!-- SQL_LIST_FORMAT
<tr><td>$tierart</td><td>$name</td></tr> -->
<!-- SQL_LIST_TRAIL </table> -->
<!-- SQL_LIST_ADB localhost:MYDB:prakt1:prakt1:
select tierart, name from tier_r -->
Im oberen Beispiel wurde SQL_LIST_ADB anstatt SQL_LIST verwendet, wobei SQL_LIST
wieder Default-Datenbank und Default-Benutzer anspricht.
Bei den bisherigen Kommandos wurde pro Kommando eine neue Datenbanksitzung veranlasst. Aus Geschwindigkeitsgründen ist es oft ratsam, eine permanente Sitzung zu öffnen. Dies geht mit folgender SQL_CONNECT-Anweisung:
<!-- SQL_CONNECT
servernode=”localhost”
serverdb=”MYDB”
user=”prakt1”
password=”prakt1”
session=c1
-->
Es wird hier eine Sitzung mit dem Namen c1 geöffnet. Nun können Anweisungen an
den Datenbankserver in der Sitzung c1 wie folgt abgesetzt werden:
<!-- SQL_EXEC
session=c1
statement=”select tierart from tier_r”
startrow=1
maxrows=20
result=r1
-->
Die Parameter startrow und maxrows sind hierbei optional. Diese Anweisung bewirkt,
dass eine Menge von höchstens 20 Zeilen (maxrows=20) beginnend ab der ersten Zeile
(startrow=1) in einem Pufferspeicher mit dem Namen r1 abgelegt wird.
Dieser Puffer kann nun mit folgendem Kommando ausgelesen und angezeigt werden:
KAPITEL 5. INTERNET-DATENBANKEN
61
<!-- SQL_RESULT
result=r1
startrow=1
lead=”Tierarten<p>”
format=”$tierart<br>”
trail=”<p>”
-->
Ein Ergebnispuffer kann mit SQL_CLOSE wieder zurückgegeben und damit der zugehörige Speicherbereich freigegeben werden:
<!-- SQL_CLOSE result=r1 -->
Eine Datenbanksitzung kann mit SQL_CLOSE geschlossen werden:
<!-- SQL_CLOSE session=c1 -->
Natürlich können bei der Arbeit mit SQL_CONNECT mehrere Datenbanksitzungen zu
unterschiedlichen Datenbankservern und Datenbanken in einer html-Seite gleichzeitig
geöffnet werden.
Kapitel 6
Datenbankprogrammierung am
Beispiel Adabas
6.1
Embedded SQL
Der Begriff Embedded SQL ist mehrdeutig:
Einerseits ist gemeint, dass der Anwender sogenannte Stored Procedures entwickelt,
die im Datenbanksystem gespeichert werden und bei Bedarf vom Anwender aufgerufen werden können. Stored Procedures werden in einer proprietären prozeduralen
Programmiersprache verfasst wie SQL-PL in Adabas oder PL/SQL in Oracle.
Andererseits bedeutet Embedded SQL, dass SQL-Anweisungen in eine Sprache
wie C oder Cobol integriert sind. Solche Programme werden dann i.a. von einem
Precompiler bearbeitet, der die SQL-Anweisungen in Aufrufe einer C- oder CobolBibliothek umwandelt, bevor das Programm mit dem eigentlichen C- oder CobolCompiler endgültig übersetzt wird. Diese Vorgehensweise erzeugt statisches SQL. Im
Gegensatz dazu spricht man von dynamischem SQL, wenn die SQL-Anweisungen erst
zur Laufzeit des Programms generiert werden.
Der Hauptunterschied zwischen Stored Procedures und C- oder Cobol-Programmen
mit Embedded SQL ist die Ausführung in Prozessen des Datenbankservers einerseits
bzw. in externen Prozessen auf u.U. vom Datenbankserver entfernten Rechnern andererseits.
6.2
C/C++ Schnittstelle
Schnittstelle zwischen C/C++-Anwendungsprogrammen und der Adabas-Datenbank
ist SQL. Werte werden hierbei zwischen dem Anwendungsprogramm über spezielle Programmvariablen - den Host-Variablen - ausgetauscht. Ein Precompiler überprüft
die in C/C++ eingebetteten SQL-Anweisungen auf syntaktische und semantische Richtigkeit und setzt sie in Aufrufe von Prozeduren des Adabas Laufzeitsystems um. Als
Eingabe erwartet der Precompiler Dateien mit der Endung .cpc. Allerdings darf diese
Endung beim Aufruf des Precompilers nicht mit angegeben werden.
Alle Anweisungen, die mit der Adabas-Datenbank etwas zu tun haben, fangen im
C/C++-Programm mit den Schlüsselworten exec sql an.
Um mit einer Datenbank verbindung aufzunehmen muss zuerst eine connect-Anweisung
62
KAPITEL 6. DATENBANKPROGRAMMIERUNG AM BEISPIEL ADABAS
63
ausgeführt werden. Dabei kann die Serverdb aus den Umgebungsvariablen bezogen
werden oder explizit mit dem set-Kommando gesetzt werden:
exec sql set serverdb “MYDB”
exec sql connect <Benutzer> identified by <Passwort>;
In einem C-Programm sind mehrere Datenbankverbindungen gleichzeitig möglich, die
SQL-Befehle auf die zweite Datenbank beginnen dann mit exec sql 2, auf die dritte mit
exec sql 3 usw.
Host-Variablen werden in einer Declare-Section analog zu C-Variablen deklariert:
exec
char
int
exec
sql begin declare section;
name[8];
gehalt;
sql end declare section;
Damit Host-Variablen von SQL-Bezeichnern unterschieden werden können, werden
sie in SQL-Anweisungen mit einem vorangestellten Doppelpunkt referenziert.
exec sql insert into mitarbeiter (name, gehalt)
values (:name, :gehalt);
Rückmeldungen des Datenbanksystems z.B. über aufgetretene Fehler erfolgen über
eine globale Struktur, die SQL Communication Area (SQLCA). Dabei enthält die
Struktur sqlca z.B. den Return Code des zuletzt ausgeführten SQL-Kommandos in sqlca.sqlcode (0 kein Fehler, sonst Fehler).
Wenn NULL-Werte aus der Datenbank in den Host-Variablen erkannt werden sollen, muss zusätzlich zur Host-Variablen eine sogenannte Indikatorvariable angegeben
werden. Indikatorvariablen müssen daher immer angegeben werden, wenn in der Tabelle auch NULL-Werte zugelassen sind, da sonst ein SQL-Fehler passiert. Ein Programmausschnitt zum Umgang mit Indikatorvariablen folgt:
exec sql begin declare section;
char vorn[8], nachn[8];
int vorind, nachind;
exec sql end declare section;
/* Voreinstellung für NULL selektiert */
vorind = -1;
strcpy(nachn, “kremer”);
/* Voreinstellung für Übertragung mit Wert */
nachind = 0;
exec sql select * from personen;
exec sql fetch into :nachn :nachind, :vorn :vorind;
while (sqlca.sqlcode == 0) {
if (vorind == 0)
printf(“Vorname: %s\n”,vorn);
if (nachind == 0)
printf(“Nachname: %s\n”, nachn);
exec sql fetch into :nachn :nachind, :vorn :vorind;
KAPITEL 6. DATENBANKPROGRAMMIERUNG AM BEISPIEL ADABAS
64
}
fprintf(stderr, “SQL-Meldung: %d %s\n”,
sqlca.sqlcode, sqlca.sqlerrmc);
Das vorstehende Programm erzeugt mit der Select-Anweisung i.d.R. mehr als eine Zeile. Diese Menge von Zeilen, die natürlich auch leer sein kann, wird in einem Pufferbereich für das C-Programm bereitgestellt. Diesen Pufferbereich nennt man einen Cursor.
Da der Cursor keinen Namen trägt, ist es genauer gesagt ein impliziter Cursor. Um nun
die Daten aus dem Cursor zeilenweise in die Host-Variablen zu übertragen, wird die
Fetch-Anweisung benutzt. Im oberen Beispiel werden die Zeilen solange aus dem Cursor geholt, bis keine mehr im Cursor sind. Dann ist die Bedingung sqlca.sqlcode == 0
nicht mehr erfüllt.
Wenn man mehrere Anfragen gleichzeitig im C-Programm auswerten will, braucht
man mehrere Cursor. Hierfür gibt es die benannten Cursor, für die es folgende Anweisungen gibt:
Cursor deklarieren:
exec sql declare <Cursorname> for <selectAnweisung>;
Cursor öffnen, d.h. Anfrage durchführen:
exec sql open <Cursorname>;
Zeile in Host-Variablen übertragen:
exec sql fetch <Cursorname> into :var1 :var2 ...;
Cursor schließen, d.h. Pufferspeicher freigeben:
exec sql close <Cursorname>
Um Tabellen zu bearbeiten können Befehle, wie create, drop, insert, delete und update angewendet werden. Allerdings werden Sie erst dann endgültig auf der Datenbank
ausgeführt, wenn anschließend eine commit work-Anweisung ausgeführt wird. Mit
der rollback work-Anweisung können alle SQL-Anweisungen, ab dem letzten commit work alternativ auch verworfen werden. Die commit work- und rollback workAnweisungen werden auch noch im Zusammenhang mit Transaktionen besprochen.
exec sql [create|drop|insert|delete|update ] ... ;
exec sql [commit work|rollback work];
Bisher müssen die SQL-Befehle zum Zeitpunkt der Übersetzung feststehen. Dies wird
auch als statisches SQL bezeichnet. Werden die SQL-Befehle erst zur Laufzeit des Programms bestimmt, so spricht man von dynamischem SQL. Hierbei werden die SQLBefehle zur Laufzeit als Zeichenketten (z.B. nach Einlesen) zusammengesetzt. Zur
Verarbeitung von SQL-Befehlen mit Parametern wird dreistufig vorgegangen:
exec sql prepare
exec sql describe
exec sql execute
Für SQL-Befehle ohne Parameter ist auch eine einstufige Vorgehensweise erlaubt:
exec sql immediate
KAPITEL 6. DATENBANKPROGRAMMIERUNG AM BEISPIEL ADABAS
6.3
65
Datenbankprozeduren und Trigger in SQL-PL
Für prozeduralen Erweiterungen von SQL, die im Datenbankserver gespeichert werden, ist in Adabas die Sprache SQL-PL (SQL-Procedural Language) vorgesehen. Die
Syntax von SQL-PL ist der von PASCAL ähnlich. Es gibt Schleifen (for, while, repeat), Verzweigungen (if, case), Blockanweisungen (begin, end), Wertzuweisungen (:=)
usw. Die Entwicklungsumgebung für SQL-PL besteht aus mehreren Bestandteilen:
Programm-Editor
Syntaxchecker
Interpreter
Compiler
Debugger
Versionsverwaltung
Crossreference-Verwaltung
SQL-PL-Programme können in der Entwicklungsumgebung entwickelt und getestet
werden, bevor sie in compilierter Form als Stored Procedures oder Trigger in die Datenbank eingestellt werden. Trigger sind spezielle Stored-Procedures, die bei Änderungen von Tabelleninhalten (Update, Delete, Insert) automatisch aufgerufen werden.
Die Entwicklungsumgebung wird mit xpl gestartet. SQL-PL-Programme können
z.B. mit einem Standard-Editor als Betriebssystemdateien gespeichert werden, bevor
sie in xpl geladen werden. Anschließend werden die SQL-PL-Programme übersetzt
und in der Datenbank abgespeichert.
Hierzu ein Beispiel. Nehmen wir an, es gibt zwei Tabellen zahlungen und buchungen, die durch folgende Anweisungen definiert worden sind:
create table zahlungen (
kundennr int not null,
zahlung int not null,
schuld int not null,
primary key(kundennr));
create table kundenkonto (
kundennr int not null,
status char(8) not null,
betrag int not null,
primary key(kundennr));
Nun soll die Tabelle zahlungen initial mit folgendem Befehl im Werkzeug xload geladen worden sein:
DATALOAD TABLE zahlungen
kundennr 1-1
zahlung 3-9
schuld 11-17
INFILE zahlungen.data
KAPITEL 6. DATENBANKPROGRAMMIERUNG AM BEISPIEL ADABAS
66
Die Datei zahlungen.data sieht z.B. wie folgt aus:
Position 1 2 3 4 5 6 7 8 9 10 11 12
1
3
0
2
3
5
Nun soll eine DB-Prozedur geschrieben werden, die aus der Tabelle zahlungen die
Tabelle
ÏJD6°§kundenkonto
*hš+¦ ™¯&°9*h§in©vfolgender
ØJM%oDJ%Ÿ*+`! Weise
Dk¢¹”™füllt:
š ¢¹”™%Ÿ˜—D¡¦v!1ÏJDk°9§*š+¦ ÙÚM¯M°*+§©
ÏJD6°§*hš+¦cKIS™¯&°9*h§©vØJM%oDJ%Ÿ*+`!t¥Y §§ ¢¹”™ %Ÿ˜—D¡¦v!4™¯&°9*h§© ÙqÏ¡Dk°§*hš+¦
ÏJD6°§*hš+¦P!t™¯&°9*h§©vØJM%oDJ%Ÿ*+`!43Pž ”ÏJD6 °§% ¢¹”™%Ÿ˜—DO¦2!tÛ
Die DB-Prozedur zahlungen.apl, die das Vorgegebene leistet, folgt nun in SQL-PL:
PROC zahlungen.start
sql (delete from kundenkonto);
sql (select * from zahlungen);
sql (fetch into :kundennr, :zahlung, :schuld);
write clear;
while $rc = 0 do begin
write nl, kundennr, zahlung, schuld;
if (zahlung > schuld) then begin
betrag := zahlung - schuld;
status:=’Haben’;
end
else if (zahlung < schuld) then begin
betrag := schuld - zahlung;
status:=’Soll’;
end
else begin
betrag := 0;
status:=’Bezahlt’;
end;
sql (insert into kundenkonto values(:kundennr,
:status, :betrag));
write nl, ’nach insert into kundenkonto: rc=’,$rc;
sql (fetch into :kundennr, :zahlung, :schuld);
end;
ENDMODULE
Um ein solches Programm auszuführen, laden wir es z.B. mit Hilfe des Pop-Up-Menüs
Selection -> Import in xpl hinein. Nun können wir das Programm mit der Funktionstaste F5 testen, d.h. es wird ein Syntaxcheck durchgeführt und das Programm danach interpretiert. Mit der Funktionstaste F9 können wir ein erfolgreich getestetes Programm
in der Datenbank abspeichern, um bei Bedarf die gespeicherte Version aufzurufen.
Zur Erklärung eines Triggers verwenden wir das Beispiel einer Bank, bei der für
Kunden Buchungen in der Tabelle buchungen vorgenommen werden:
create table buchungen (
kontonr int not null,
betrag fixed(10,2) not null,
KAPITEL 6. DATENBANKPROGRAMMIERUNG AM BEISPIEL ADABAS
67
bemerkung char(80) not null,
foreign key (kontonr) references konten);
Zu jeder Buchung gehört ein eindeutiges Konto, das über kontonr bestimmt ist:
create table konten (
kontonr int not null,
vorname char(8) not null,
nachname char(8) not null,
kontostand fixed(10,2) not null,
primary key (kontonr));
Nun soll nach jedem Eintrag in buchungen (Insert) kontostand automatisch aktualisiert
werden. Dies ist eine typisch Aufgabe für folgenden Trigger, der z.B. in einer Datei
steht und in xpl importiert wird:
TRIGGER buchungen.start (in betrag fixed(10,2));
sql (update kremer.konten
set kontostand=kontostand + :betrag);
ENDMODULE
PCREATE buchungen.start
Nach dem Import wird der Trigger z.B. mit dem Menüpunkt Create in DB oder mit
dem Kommando tcreate in der Datenbank aktiviert. Dabei muss die Tabelle (hier buchungen) und die Operation (Insert, Delete, Update) (hier Insert)angegeben werden,
für die der Trigger gilt. Der Trigger wird dann in einer Systemtabelle mit dem Namen
TRIGGERS verzeichnet.
Um einen Trigger zu löschen, reicht es nicht, das Programm aus xpl zu entfernen,
sondern er muss mit dem Befehl tdrop oder über den Menüpunkt Entfernen aus der
Datenbank im Objektmenü gelöscht werden.
6.4
Parallele Transaktionen
Wie in allen parallelen Systemen, z.B. bei parallelen Prozessen in Betriebssystemen,
kann eine unkontrollierte Parallelität bei Datenbanksystemen zu Problemen führen.
Das Verarbeitungsmodell der Datenbanken basiert auf parallelen Transaktionen. Eine Transaktion hat dabei folgende Eigenschaften:
Transaktionen haben einen Anfang und ein Ende.
Transaktionen bestehen aus einer oder mehreren sequentiellen Operationen, die
als atomar angesehen werden, d.h. sie werden ganz oder gar nicht ausgeführt
(ohne inkonsistente Zwischenzustände).
SQL-Anweisungen sind atomare Operationen
Änderungen durch eine Transaktion werden in einem privaten Arbeitsbereich
der Transaktion vorgenommen (Datenbank-Cache) und am Ende der Transaktion verworfen (ROLLBACK) oder in die physische Datenbank übernommen
(COMMIT).
KAPITEL 6. DATENBANKPROGRAMMIERUNG AM BEISPIEL ADABAS
68
Eine Ablaufsteuerung für Transaktionen
#Mu\ u Å
u+nennt
_h5 man konsistent, wenn die parallele Ausführung der Transaktionen
in ihrer Wirkung äquivalent
&
^
]
^
]
>
]
uÝ—Þ \oß uÝÞ Å ß uÝÞ _´ß?à
#JÕ
š5 zur seriellen
Ausführung Ü
für eine Permutation á auf &]^]^]>
ist. Konsi
M]>]^]>
stenzprobleme paralleler Transaktionen werden in verschiedene Gruppen unterteilt:
Verlorengegangen Änderungen:
In einer Datenbank sollen Sitzplatzreservierungen für Flüge verwaltet werden. F beinhaltet die Anzahl gebuchter Plätze eines bestimmten Fluges. z1 und z2 sind lokale
Variablen der Transaktionen T1 und T2.
T1
start T1
z1 = read(F)
T2
start T2
z2 = read(F)
z2 = z2 + 1
write(z2, F)
commit
z1 = z1 + 1
write(z1,F)
commit
In diesem Beispiel geht die Änderung von Transaktion T2 verloren. Man spricht
von einem Lost-Update-Problem.
Inkonsistente Datenbank:
Es soll die Integritätsbedingung A = B gelten. T1 soll A und B um 1 erhöhen; T2 soll
A und B verdoppeln.
T1
start T1
a1 = read(A)
a1 = a1 + 1
write(a1, A)
commit
T2
start T2
a2 = read(A)
a2 = 2 * a2
write(a2, A)
b2 = read(B)
b2 = 2 * b2
write(b2,B)
commit
b1 = read(B)
b1 = b1 + 1
write(b1, B)
commit
Nach diesen Transaktionen ist bei einem anfänglichen Wert von A = B = 5 zum
Schluss A = 6 und B = 11.
KAPITEL 6. DATENBANKPROGRAMMIERUNG AM BEISPIEL ADABAS
69
Nicht reproduzierbares Lesen:
T1
start T1
a1 = read(A)
print(a1)
T2
start T2
a2 = read(A)
a2 = a2 + 1
write(a2, A)
commit
a1 = read(A)
print(a1)
commit
Die Transaktion T1 erhält unterschiedliche Werte von A zu verschiedenen Zeitpunkten, da die Transaktion T2 inzwischen A verändert hat.
Alle gezeigten Probleme haben gemeinsam, dass ihre Wirkungen keiner sequentiellen Ausführungsreihenfolge der Transaktionen entsprechen. Ein allgemeiner Mechanismus zur Synchronisation, um konsistente parallele Systeme herzustellen, besteht
in der Anwendung von Sperren. Sperren können auf gesamte Tabellen oder einzelne Tabellenzeilen gesetzt werden. Grundsätzlich werden folgende Arten von Sperren
unterschieden:
Lesesperre (Share-Sperre in Adabas):
Jeder andere Benutzer darf das Objekt zwar lesen aber nicht verändern.
Es kann mehrere Share-Sperren von unterschiedlichen Transaktionen auf eine Tabellenzeile geben. Dann sind mehrere Select-Aufrufe von den unterschiedlichen Transaktionen aus möglich. Eine Update-Operation kann jedoch nur bei einer einzigen gesetzten Share-Sperre durchgeführt werden, d.h. alle übrigen Share-Sperren sollten vorher durch commit, rollback oder unlock zurückgegeben worden sein. Falls zwei Transaktionen mit zugeteilten Share-Sperren beide Update-Operationen durchführen wollen,
so kommt es zu einer Verklemmung (Deadlock):
Transaktion 1
Setzen der Share-Sperre auf Z
Update auf Z wartet bis
Share-Sperre von 1 zurückgesetzt
Transaktion 2
Setzen der Share-Sperre auf Z
Update auf Z wartet bis
Share-Sperre von 2 zurückgesetzt
Adabas erkennt die Verklemmung und macht die Transaktion des späteren UpdateVersuchs rückgängig.
Schreibsperre (Exclusive-Sperre in Adabas):
Kein anderer Benuter darf das Objekt lesen oder ändern.
Eine Exklusive-Sperre kann nur einer Transaktion für eine Tabellenzeile zugeteilt
werden. Diese Transaktion hat exklusives Lese- und Schreibrecht auf die Tabellenzeile.
Eine andere Transaktion, die eine Exklusive-Sperre oder eine Share-Sperre anfordert
blockiert, bis die Exklusive-Sperre freigegeben wird. Transaktionen, die ohne Sperre
KAPITEL 6. DATENBANKPROGRAMMIERUNG AM BEISPIEL ADABAS
70
lesend auf die exklusiv gesperrte Zeile zugreifen wollen, dürfen dies tun. UpdateOperationen auf die exklusiv gesperrte Zeile werden bis zur Rückgabe der Sperre hinausgezögert.
Optimistische Sperre (optimistic-Sperre in Adabas):
Optimistische Sperren können nur für Zeilen gesetzt werden.
Wird eine Zeile optimistisch gesperrt, so kann jeder Benutzer die Zeile lesen oder
ändern. Versucht der Erzeuger (Besitzer) der Sperre, die Zeile zu ändern, so wird zunächst überprüft, ob die Zeile durch einen anderen Benutzer in der Zwischenzeit geändert wurde. Falls ja, wird die Änderung abgelehnt und kann erst nach erneutem Lesen
der veränderten Zeile und Setzen der optimistischen Sperre durchgeführt werden. Fand
zwischenzeitlich keine Änderung statt, so wird die Änderung sofort durchgeführt.
Exklusive- oder Share-Sperren blockieren also eine Update-Operation, bis die Sperren der anderen Transaktionen freigegeben sind. Der Hauptvorteil optimistischer Sperren für die Transaktionsverarbeitung ist, das hierbei die Update-Operation nicht blockiert,
wenn die optimistische Sperre gesetzt ist.
Die Syntax für das explizite Setzen einer Sperre lautet für die verschiedenen Sperrenarten:
exec sql lock row
key <Schlüssel> =
exec sql lock row
key <Schlüssel> =
exec sql lock row
key <Schlüssel> =
<Tabellenname>
<Wert> in exclusive mode;
<Tabellenname>
<Wert> in share mode;
<Tabellenname>
<Wert> in optimistic mode;
Die Syntax für das Bestätigen oder Verwerfen einer Transaktion lautet:
exec sql commit work;
exec sql rollback work;
Die Syntax für das explizite Freigeben einer Sperre lautet:
exec sql unlock
key <Schlüssel>
exec sql unlock
key <Schlüssel>
exec sql unlock
key <Schlüssel>
row <Tabellenname>
= <Wert> in exclusive mode;
row <Tabellenname>
= <Wert> in share mode;
row <Tabellenname>
= <Wert> in optimistic mode;
Sperren können explizit durch Benutzer oder implizit durch das Datenbanksystem gesetzt werden. Sie gelten bis zum Ende der Transaktion durch ein commit work oder
rollback work oder bis zu einem expliziten unlock-Befehl. Den Anfang einer Transaktion bildet der connect-Befehl oder das Ende der vorigen Transaktion mit commit work
oder rollback work.
Ein Beispiel zum Einsatz einer exklusiven Sperre folgt für das Problem der Kontobuchung. Hierbei darf beim Ändern des Kontostands um einen bestimmten Betrag
kein anderer Benutzer den Kontostand ansehen oder ändern:
KAPITEL 6. DATENBANKPROGRAMMIERUNG AM BEISPIEL ADABAS
71
#include <stdio.h>
exec sql begin declare section;
int kontonr;
char vorname[9];
char nachname[9];
float kontostand;
float betrag;
exec sql end declare section;
void main(void) {
char answer;
printf("\nBuchungen:\n");
while (1) {
printf("\nKontonr (-1 für Ende):\t");
scanf("%d", &kontonr);
if (kontonr == -1) break;;
exec sql lock row konten key kontonr=:kontonr
in exclusive mode;
printf("\nSchreibsperre gesetzt.");
exec sql select * from konten
where kontonr = :kontonr;
exec sql fetch into :kontonr, :vorname,
:nachname,:kontostand;
printf("\n\nKontonr: %d\tVorname: %s\t
Nachname: %s\tStand: %f", kontonr,
vorname, nachname,kontostand);
printf("\nBuchungsbetrag:\t");
scanf("%f", &betrag);
exec sql update konten
set kontostand=kontostand+:betrag
where kontonr = :kontonr;
exec sql commit work;
} }
Das Verhalten impliziter Sperren kann in Adabas mit dem Isolation-Level beim connectBefehl festgelegt werden. Die Isolation-Level unterscheiden sich in der Ausführung
von Lesesperren bei einer Select-Anweisung:
Isolation-Level 0:
Es sind nur explizite Sperren erlaubt, d.h. es werden keine impliziten Sperren vom
Datenbanksystem gesetzt.
Isolation-Level 1:
Beim Select wird nur eine Zeile lesegesperrt, bei einem weiteren Select wird die vorher
gesperrte Zeile automatisch wieder freigegeben.
Isolation-Level 2:
KAPITEL 6. DATENBANKPROGRAMMIERUNG AM BEISPIEL ADABAS
72
Hier können mehrere Zeilen lesegesperrt sein. Eine Tabelle, die keinen Schlüssel enthält, wird insgesamt lesegesperrt und erst am Ende der Select-Anweisung wieder freigegeben.
Isolation-Level 3:
Eine Tabelle ohne Schlüssel wird bis zum Transaktionsende lesegesperrt.
Bei allen Isolation-Leveln werden bei Updates automatisch Schreibsperren gesetzt.
Für OLTP-Programme (Online Transaction Processing) wird bei Adabas emmpfohlen,
den Isolation-Level 0 zu wählen und optimistische Sperren explizit zu setzen.