Entity-Relationship Modell - IT
Werbung
1
Datenbank-Seminar
V 1.7 20.05.1997
Autoren:
Reiner Götz
Karl Laderer
Cristobal Pino
Andreas Rau
H.J. Schmidt
Jörg Schliepkorte
Rainer Brosi
Norbert Rösch
Matin Walser
Gunnar Krauß
Martin Buchthal
Dirk Kaller
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
2
INHALTSVERZEICHNIS
1 ENTITY-RELATIONSHIP MODELL.............................................................................................................5
1.1 BEZIEHUNGEN ZWISCHEN ENTITÄTEN ............................................................................................................5
1.2 MODELLERSTELLUNG ....................................................................................................................................9
1.3 BESONDERHEITEN BEI ERM ..........................................................................................................................9
1.3.1 Reflexive Beziehungen/Vergabe von Rollen ..........................................................................................9
1.3.2 Vererbungsbeziehung ..........................................................................................................................10
1.4 UMWANDLUNG IN EIN RELATIONALES MODELL ...........................................................................................11
1.5 ZUSAMMENFASSUNG ...................................................................................................................................12
2 METHODEN FÜR DEN SYSTEMENTWURF - DATENBANKENTWURF............................................13
2.1 NACHTEILE VON DATEIEN UND VORTEILE VON DATENBANKEN ..................................................................13
2.2 ZUGRIFFSSCHNITTSTELLEN ZU DATENBANKEN ............................................................................................14
2.3 RELATIONALES DATEN-MODELL .................................................................................................................15
2.3.1 Der Begriff der Relation......................................................................................................................16
2.3.2 Ein einfaches Beispiel .........................................................................................................................17
2.3.3 Schlüssel einer Relation ......................................................................................................................18
2.3.4 Relationale Integritätsregeln...............................................................................................................19
2.4 ABBILDUNG VON ENTITÄTSTYPEN UND BEZIEHUNGEN ZWISCHEN ENTITÄTSTYPEN AUF RELATIONEN ........20
2.4.1 Datenbankentwurf für 1 zu 1 -Beziehungen des ERM.........................................................................20
2.4.2 Datenbankentwurf für 1 zu n -Beziehungen des ERM.........................................................................21
2.4.3 Datenbankentwurf für m zu n -Beziehungen des ERM ........................................................................24
2.5 NORMALISIERUNG........................................................................................................................................26
2.5.1 Beispiel 1.............................................................................................................................................26
2.5.1.1 Unnormalisierte Relationen .......................................................................................................................... 27
2.5.1.2 Erste Normalform ......................................................................................................................................... 27
2.5.1.3 Zweite Normalform ...................................................................................................................................... 28
2.5.1.4 Dritte Normalform ........................................................................................................................................ 29
2.5.2 Beispiel 2.............................................................................................................................................31
2.5.2.1 Erste Normalform ......................................................................................................................................... 31
2.5.2.2 Zweite Normalform ...................................................................................................................................... 32
2.5.2.3 Dritte Normalform: ....................................................................................................................................... 32
2.5.2.4 Redunzanzfreiheit......................................................................................................................................... 33
2.5.3 Modellierung .......................................................................................................................................34
2.5.3.1 Vergleich der Methoden ............................................................................................................................... 36
2.5.3.2 Modellierung impliziert Normalisierung ...................................................................................................... 36
2.5.4 Zusammenfassung ...............................................................................................................................37
3 SQL ....................................................................................................................................................................39
3.1 ALLGEMEINES ZUR SPRACHE SQL................................................................................................................39
3.2 OBJEKTE EINER RELATIONALEN DATENBANK ..............................................................................................40
3.3 ETWAS RELATIONALE ALGEBRA ..................................................................................................................40
3.3.1 Restriction ...........................................................................................................................................41
3.3.2 Projection............................................................................................................................................41
3.3.3 Product................................................................................................................................................41
3.3.4 Union...................................................................................................................................................42
3.3.5 Intersection..........................................................................................................................................42
3.3.6 Difference............................................................................................................................................42
3.3.7 Join......................................................................................................................................................43
3.4 SPRACHBEREICHE ........................................................................................................................................45
3.5 DML............................................................................................................................................................45
3.5.1 Abfragen (Queries) mit der SELECT Anweisung ................................................................................45
3.5.1.1 SELECT Klausel .......................................................................................................................................... 46
3.5.1.2 Funktionen.................................................................................................................................................... 46
3.5.1.3 FROM Klausel.............................................................................................................................................. 47
3.5.1.4 WHERE Klausel - Zeilenfilter...................................................................................................................... 47
3.5.1.5 Gruppenfunktionen, GROUP BY Klausel .................................................................................................... 49
3.5.1.6 HAVING Klausel - Gruppenfilter................................................................................................................. 50
3.5.1.7 ORDER BY Klausel ..................................................................................................................................... 50
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
3
3.5.1.8 Subselects ..................................................................................................................................................... 51
3.5.1.9 Mengenoperatoren ........................................................................................................................................ 52
3.5.1.10 JOIN - Verknüpfung von Tabellen ............................................................................................................. 52
3.5.2 Umsetzung der SELECT Anweisung in relationale Operatoren .........................................................54
3.5.3 INSERT................................................................................................................................................55
3.5.4 UPDATE .............................................................................................................................................55
3.5.5 DELETE ..............................................................................................................................................56
3.6 DDL ............................................................................................................................................................56
3.6.1 CREATE ..............................................................................................................................................56
3.6.1.1 CREATE TABLE ......................................................................................................................................... 56
3.6.1.2 CREATE VIEW ........................................................................................................................................... 56
3.6.1.3 CREATE INDEX ......................................................................................................................................... 57
3.6.2 ALTER .................................................................................................................................................57
3.6.3 DROP ..................................................................................................................................................57
3.7 DCL.............................................................................................................................................................57
3.7.1 Transaktionsverarbeitung ...................................................................................................................57
3.7.2 Objektprivilegien.................................................................................................................................58
3.7.2.1 GRANT ........................................................................................................................................................ 59
3.7.2.2 REVOKE...................................................................................................................................................... 59
3.8 ERWEITERUNGEN DURCH WEITERFÜHRENDE SQL STANDARDS ...................................................................59
3.8.1 Check-Option ......................................................................................................................................59
3.8.2 Default-Option ....................................................................................................................................59
3.8.3 Unique-Option.....................................................................................................................................59
3.8.4 Foreign Key.........................................................................................................................................59
3.8.5 Definition von Domänen .....................................................................................................................60
3.8.6 Zustände von Tabellen ........................................................................................................................60
3.8.7 Transaktionen......................................................................................................................................60
3.8.8 dynamisches SQL ................................................................................................................................60
3.8.9 Funktionen ..........................................................................................................................................60
4 EINFÜHRUNG IN SQL*PLUS.......................................................................................................................61
4.1 BEGRIFFE ZU SQL*PLUS ..............................................................................................................................61
4.2 SQL*PLUS BEFEHLE ....................................................................................................................................61
4.2.1 Eingabe von SQL*Plus Befehlen.........................................................................................................61
4.2.2 START .................................................................................................................................................61
4.2.3 DESCRIBE ..........................................................................................................................................62
4.2.4 Tabelle TABS.......................................................................................................................................62
5 PL/SQL - EINE PROZEDURALE ERWEITERUNG VON SQL FÜR ORACLE.....................................63
5.1 ALLGEMEINES ..............................................................................................................................................63
5.2 AUFBAU EINES PL/SQL BLOCKES ...............................................................................................................63
5.3 ABLAUFSTEUERUNG.....................................................................................................................................63
5.4 DAS SELECT-STATEMENT ..........................................................................................................................65
5.5 INSERT-, UPDATE- UND DELETE-STATEMENT ......................................................................................65
5.6 ELEMENTARE DATENTYPEN.........................................................................................................................66
5.7 STRUKTURIERTE DATENTYPEN ....................................................................................................................67
5.8 CURSOR-MANAGEMENT ..............................................................................................................................68
5.9 PROZEDUREN UND FUNKTIONEN ..................................................................................................................71
5.10 FEHLERBEHANDLUNG ................................................................................................................................73
6 EMBEDDED SQL MIT PRO*C .....................................................................................................................74
6.1 ALLGEMEINES ..............................................................................................................................................74
6.2 TEILE EINES PRO*C-PROGRAMMS ...............................................................................................................74
6.3 VORGEHENSWEISE BEI DER ERSTELLUNG EINES PRO*C-PROGRAMMS ........................................................75
6.3.1 Grundstruktur eines ESQL-Programms ..............................................................................................75
6.4 DER APPLICATION PROLOGUE......................................................................................................................76
6.4.1 DECLARE - SECTION........................................................................................................................76
6.4.2 Zulässige Datentypen:.........................................................................................................................77
6.4.3 Strings variabler Länge.......................................................................................................................77
6.5 DIE VEREINBARUNG DER SQLCA ZUR FEHLERBEHANDLUNG .....................................................................78
6.6 DIE CONNECT - ANWEISUNG ....................................................................................................................79
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
4
6.7 DER APPLICATION BODY .............................................................................................................................80
6.7.1 Kennzeichnung von Kommentaren......................................................................................................80
6.8 EINBETTEN VON QUERIES ............................................................................................................................80
6.8.1 Single Row Select ................................................................................................................................81
6.8.2 Multiple Row Select.............................................................................................................................81
6.8.3 Die INSERT-Anweisung ......................................................................................................................83
6.8.4 Die DELETE-Anweisung.....................................................................................................................83
6.8.5 Die UPDATE-Anweisung ....................................................................................................................83
6.9 ARBEITEN MIT VARCHAR..........................................................................................................................83
6.10 FEHLERANALYSE UND -BEHANDLUNG ........................................................................................................86
6.11 ZUSAMMENFASSUNG .................................................................................................................................87
7 AUFGABEN ZUM DATENBANK-SEMINAR .............................................................................................88
7.1 AUFGABEN ZUM ERM .................................................................................................................................88
7.2 AUFGABEN ZUM RELATIONALEN MODELL ..................................................................................................88
7.3 AUFGABE ZUR NORMALISIERUNG ................................................................................................................89
7.4 AUFGABEN ZU SQL*PLUS..........................................................................................................................89
7.5 AUFGABEN ZU EMBEDDED SQL ..................................................................................................................90
8 LÖSUNGEN ......................................................................................................................................................91
8.1 LÖSUNG ZUM ERM......................................................................................................................................91
8.2 LÖSUNG ZUM RELATIONALEN MODELL .......................................................................................................91
8.3 LÖSUNG ZUR NORMALISIERUNG ..................................................................................................................92
8.4 LÖSUNGEN ZU SQL*PLUS ..........................................................................................................................94
8.5 LÖSUNGEN EMBEDDED SQL........................................................................................................................95
9 LITERATURHINWEISE ..............................................................................................................................101
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
5
1 Entity-Relationship Modell
Das Entity-Relationship Modell (ERM) stellt das älteste und allgemeinste Modell zur
strukturierten Modellierung von Daten dar. Es wurde 1970-76 von Chen definiert und
später vielfach weiterentwickelt. Viele neue Ansätze, wie das relationale Datenmodell
oder die Objektorientierte Datenmodellierung haben immer noch viel mit dem
ursprünglichen ERM gemeinsam. Wegen ihrer Bedeutung für die Modellierung von
Daten wurde die Entity-Relationship Modellierung nachträglich in die Strukturierte
Analyse aufgenommen.
Der zentrale Begriff in diesem Modell ist die Entität (entity). Damit sind eindeutig
identifizierbare1 Objekte der realen Welt gemeint. Dies können sowohl konkrete
Objekte wie Gegenstände, Gebäude oder Personen als auch abstrakte Konzepte
wie Verträge, Gesetze oder Ideen sein.
Alle diese Entitäten haben Eigenschaften. Diese werden als Attribute (attribute)
bezeichnet. Jedem der Attribute ist eine Domäne (domain) zugeordnet. Diese enthält
alle Werte, die das Attribut annehmen kann. Ein solcher Wert heißt dann
Ausprägung des Attributs. Damit repräsentiert die Domäne den Wertebereich des
Attributs.
1.1 Beziehungen zwischen Entitäten
Zwischen Entitäten bestehen Beziehungen (relations). Sie werden nach ihrer
Komplexität unterschieden. Je nachdem, wieviele Entitäten an der Beziehung
beteiligt sind, unterscheidet man 3 Fälle:
• 1 zu 1, einfache Beziehung
Ein Ehemann hat - in unserer Kultur - genau eine Ehefrau und umgekehrt.
Bedeutung des Pfeils:
genau 1 Frau
Mann
Frau
hat
Bedeutung des
Pfeils:
genau 1 Mann
Alternative Notation:
1,1
Mann
Bedeutung von
1,1:
genau 1 Mann
1,1
hat
Frau
Bedeutung von
1,1:
genau 1 Frau
1
Sie müssen eindeutig identifizierbar sein, damit man überhaupt mit ihnen arbeiten bzw auf sie
zugreifen kann. Mit dieser Voraussetzung werden wir uns in Kapitel 11 im Zusammenhang mit
Schlüsseln (keys) noch näher beschäftigen.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
6
• 1 zu n, konditionelle Beziehung
Ein Paar kann mehrere Kinder haben, ein Kind jedoch nur einen Vater und eine
Mutter.
Bedeutung des Pfeils:
genau 1 Paar
Kind
Paar
hat
Bedeutung des
Pfeils:
1-n Kinder
Alternative Notation:
1,n
Kind
1,1
Paar
hat
Bedeutung von
1,1:
genau 1 Paar
Bedeutung von
1,n:
1-n Kinder
• n zu m, komplexe Beziehung
Eine Person kann mehrere Telefonnummern haben, unter einer Telefonnummer
können mehrere Personen erreichbar sein.
Bedeutung des Pfeils:
genau 1-m Personen
Telefon-Nr.
Person
hat
Bedeutung des
Pfeils:
1-n Telefon_Nr.
Alternative Notation:
Telefon-Nr.
1,n
1,m
hat
Person
Bedeutung von
1,m:
1-m Personen
Bedeutung von
1,n:
1-n Telefon-Nr.
Beachten Sie, daß in der Notation mit Pfeilen ein Doppelpfeil von Paar auf Kind
zeigt, wenn ein Paar 1-n Kinder hat.
Streng genommen müßte man noch solche Beziehungen unterscheiden, bei denen
ein Partner nicht zwingend vorhanden ist (Ein Mann kann verheiratet sein, er muß es
nicht sein, dasselbe gilt natürlich auch für eine Frau). Diese "kann"-Beziehungen
werden jedoch im allgemeinen als Sonderfälle der obigen Fälle ("muß"-Beziehungen)
betrachtet; aus „1 zu“ wird dann „0 oder 1 zu“ aus „genau 1“ wird „höchstens 1“.
Bild 1-1
Frau
hat
Mann
Kind
hat
Paar
Spezialfälle:
1 Mann hat 0 oder 1 Frau, 1 Frau hat 0 oder 1 Mann
Zu einem Kind gehört 1 Paar, zu einem Paar gehören 0 bis n Kinder.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
7
Auch Beziehungen können Eigenschaften besitzen. Diese werden dann LinkAttribute genannt. Ein Beispiel hierfür wäre das Datum und der Gegenstand eines
Kaufs, der über die Beziehung „kauft“ zwischen Kunde und Verkäufer modelliert
wird. Auch wenn es bei einfacher (1-zu-1) und konditioneller Beziehung (1-zu-n)
noch möglich wäre, die Link-Attribute einer der einfach an der Beziehung beteiligten
Entitäten zuzuordnen (z.B. dem Kunden, wenn der Verkäufer mehrere Kunden hat,
der Kunde aber nur einmal und bei einem Verkäufer einkauft), macht doch meist die
separate Modellierung über die Beziehung im Hinblick auf Verständlichkeit des
Modells und zukünftige Erweiterungen (1-zu-n auf m-zu-n, ein Kunde darf
verschiedene Dinge bei mehreren Verkäufern einkaufen) mehr Sinn.
Gleichartige Entitäten und Beziehungen kann man unter übergeordneten
Entitätstypen bzw. Beziehungstypen zusammenfassen. Beispiel: Der Entitätstyp
„Kunde“ statt „Herr Müller“, „Frau Meier“, etc.
Begriff
Entität
Bedeutung
Ein eindeutig identifizierbares Objekt der realen Welt
(Datensatz, Objekt, Zeile einer Tabelle)
Entitätstyp
Bezeichnung und Charakterisierung einer Gruppe
gleichartiger Entitäten (Klasse)
Beziehung
Zusammenhang zwischen Entitäten
Beziehungstyp Bezeichnung
und
Charakterisierung
gleichartiger
Beziehungen
Attribut
charakterisierende Eigenschaft einer Entität (Datenfeld)
Domäne
Wertebereich für Attribute
Link-Attribut
Attribut, das nicht zu einer Entität, sondern zu einer
Beziehung gehört
Tabelle 1-1 Übersicht
der wichtigsten Begriffe im ERM
Bei der praktischen Modellierung arbeitet man natürlich nicht direkt textuell mit
diesen Begriffen, sondern - wie bei vielen anderen Verfahren auch - grafisch. Dies
kommt der Übersicht und dem schnellen Verständnis sehr entgegen. Die dabei
verwendeten Symbole finden Sie im folgenden dargestellt. Wie Sie sehen, werden
für Entitäten und Beziehungen dieselben Symbole wie für die zugehörigen Typen
verwendet. Dies rührt daher, daß man im Modell weniger mit konkreten Entitäten als
vielmehr stellvertretend für ihre Gesamtheit mit den Typen modelliert. Das Modell
gestattet jedoch auch die Darstellung von Einzelfällen.
Entität(styp)
Attribut
Beziehung(styp)
Bild 1-2 Symbole in ER-Diagrammen
Ein jedes dieser Rechtecke der Grafik, d.h. ein jeder Entitätstyp (ein jeder Objekttyp),
entspricht einem Datenspeicher in einem DFD. Wenn es im Entity-Relationship
Diagramm den Objekttyp „Kunde“ gibt, so ist im Datenflußdiagramm der
Datenspeicher „Kunde“ enthalten.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
8
Nun zu den Regeln zum Aufbau eines ER-Diagramms:
• Jede Entität wird durch ein oder mehrere Datenelemente - Attribute genannt beschrieben. Entitäten werden mit ihren Attributen durch ungerichtete Kanten
verbunden.
• Die Symbole für Beziehungen stehen zwischen den Entitäten, die sie verbinden,
und sind mit beiden Entitäten durch gerichtete Kanten verbunden.
• Die Komplexität von Beziehungen kann durch die Anzahl der Pfeilspitzen, die
stets an der Beziehung ansetzen, ausgedrückt werden. Eine Pfeilspitze bedeutet
„1“, zwei Pfeilspitzen bedeuten „n“ bzw. „m“ bezüglich derjenigen Entität am
anderen Ende der Beziehung. Um auszudrücken, daß auch die Null mit
eingeschlossen ist, können die Pfeilspitzen um eine „0“ ergänzt werden. Um
beispielsweise zu modellieren, daß ein Paar mehrere Kinder haben oder auch
kinderlos sein kann, verbindet man die Entität „Paar“ über eine Linie mit 2
Pfeilspitzen mit der Beziehung „hat“. Hinter der zweiten Pfeilspitze wird eine „0“
eingefügt. Von der anderen Seite verbindet man dann die Entität „Kind“ über
einen einfachen Pfeil mit der Beziehung. Will man außerdem noch die Möglichkeit
von Waisenkindern berücksichtigen, kann man auch hier die Pfeilspitze durch
eine „0“ ergänzen (vgl. Beispiele oben). Eine alternative Notation mit
Komplexitätsangaben „1,n“ wurde bereits beschrieben. In der Literatur existieren
weitere alternative Notationen.
0,1
1,1
1,n
0,n
Bild 1-3 Vergleich
der Pfeil-und der „1,n“-Notation
• Alle Elemente werden entweder direkt im Diagramm beschriftet oder mit einer
Nummer versehen und extern erklärt (letzteres Verfahren wird häufig für
Beziehungen angewandt). Dabei gilt die Empfehlung, Beziehungen so zu
beschriften, daß sie von oben nach unten oder von links nach rechts gelesen
werden können. Ist dies nicht möglich und ist die Leserichtung nicht aufgrund der
Namensgebung offensichtlich, so ist die Leserichtung durch kleine Richtungspfeile
neben dem Beziehungssymbol zu kennzeichnen. Alle Namen sollen kurz,
prägnant und vielsagend sein. Für Entitäten und Attribute sind Substantive
(Person, Adresse, Alter, Name), für Beziehungen Verben (hat, ist, wohnt) zu
bevorzugen.
Name
Vorname
Person
Stand
Typ
1
Konto
1: "hat"
Bild 1-4 Einfaches
Beispiel
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
9
Vorname
Vorwahl
Name
Person
hat
Nummer
Tel.Nummer
Straße
Miete
mietet
Wohnung
Ort
Bild 1-5
ER-Diagramm mit Link-Attribut. Mehrere Personen (Familie oder
Wohngemeinschaft) mieten eine Wohnung. Zum Mietvorgang gehört
die Miete. Miete ist also ein Linkattribut.
1.2 Modellerstellung
Wie geht man nun bei der Modellerstellung vor? Oftmals wird man als Vorlage eine
textuelle Beschreibung einer Aufgabe oder eines Vorgangs haben. Um aus ihr ein
erstes Modell zu extrahieren, müssen zunächst Entitäten, Attribute und Beziehungen
identifiziert werden. Dabei sind Substantive Kandidaten für Entitäten, Adjektive
Kandidaten für Attribute und Verben Kandidaten für Beziehungen. Es ist jedoch
Sorgfalt angebracht, denn auch Attribute können durch Substantive bezeichnet sein.
Entitäten, die aus einer solchen Betrachtung entstehen, müssen verifiziert werden.
Besitzen sie keine Eigenschaften oder gehen sie keine Beziehungen ein, sind sie
vermutlich überflüssig. Umgekehrt ist es oft sinnvoll, eine Gruppe
zusammengehöriger Attribute als eigenen Entitätstyp zu realisieren, wenn dieser für
sich einen Sinn macht, Beziehungen eingehen kann und somit an mehreren Stellen
benutzt werden kann (Zum Beispiel haben Personen und Firmen eine Adresse mit
PLZ, Ort, Straße und Hausnummer. Diese wird als eigener Entitätstyp realisiert). Für
die Attribute müssen die Domänen definiert werden (z.B. Entität Datum, Attribut
Monat: 1..12). An dieser Stelle werden auch Überlegungen zu Integritätskriterien
angestellt. Ein Wert kleiner 1 oder größer 12 beim Monat ist offensichtlich ungültig.
Schwieriger ist da schon die Entdeckung von Fehlern wie "31.2." . Hier muß der
Wertebereich des Tages (1..31) abhängig vom Monat eingeschränkt werden.
Schließlich ist noch zu prüfen, ob das Modell alle Anfragen, die sich aus der
Aufgabenstellung ableiten lassen, befriedigen kann (einfache Beziehungen können
in beiden Richtungen eindeutig durchlaufen werden, konditionale nur in einer und
komplexe gar nicht). Vor der Implementierung des Modells kann es dann noch nötig
sein, das Datenmodell zu normalisieren. Damit wollen wir uns in Kapitel 2
beschäftigen.
1.3 Besonderheiten bei ERM
1.3.1 Reflexive Beziehungen/Vergabe von Rollen
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
10
Reflexive Beziehungen bestehen nicht zwischen Entitäten verschiedener
Entitätstypen, sondern zwischen Entitäten desselben Typs. Ein Beispiel hierfür ist die
Beziehung „ist Vater von“ zwischen Entitäten des Typs Person. Im Diagramm sind
demzufolge beide Seiten der Beziehung mit dem selben Entitätstyp verbunden. Die
Entitäten treten jedoch in verschiedenen Rollen auf. In unserem Beispiel als Vater
und Kind. Diesem Umstand trägt man durch Beschriftung der beiden Seiten der
Beziehung mit dem Rollennamen Rechnung. Man beachte, wie mit Hilfe der
Beziehungen ausgedrückt werden kann, daß zwar ein Kind nur einen Vater, ein
Vater jedoch durchaus mehrere Kinder haben kann.
Vater
ist
Person
Kind
Bild 1-6
Reflexive Beziehungen mit Rollen
1.3.2 Vererbungsbeziehung
Vererbung wird verwendet, um auszudrücken, daß Gemeinsamkeiten (d.h.
gemeinsame Attribute) von Entitätstypen in einem separaten, übergeordneten
Entitätstyp untergebracht sind, mit dem die ursprünglichen Entitätstypen eine
besondere Beziehung eingehen. Dadurch wird die redundante Modellierung in
beiden Entitätstypen vermieden und gleichzeitig ihre Gemeinsamkeit explizit im
Modell deutlich gemacht.
Name
Vorname Geb.Dat
Person
Gehalt
Angestellter
hat
Straße
Adresse
Std.
Lohn
Arbeiter
arbeitet für
Ort
arbeitet für
Firma
Bild 1-7 Beispiel
mit Vererbungsbeziehung
Das obige Beispiel drückt aus, daß sowohl Angestellte als auch Arbeiter Personen
mit Name, Vorname und Geburtsdatum sind. Jeder Arbeiter/Angestellte ist mit einer
Entität des Typs Person verbunden, welche die gemeinsamen Attribute aufnimmt.
Zusätzlich ist es mit diesem Mechanismus möglich, daß eine Person ein
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
11
Arbeitsverhältnis als Angestellter und gleichzeitig eines als Arbeiter hat (auch bei
verschiedenen Firmen), ohne daß seine Stammdaten doppelt gespeichert werden:
Der Arbeiter und der Angestellte referenzieren einfach dieselbe Person. Eine
Vererbungsbeziehung ist also (von unten nach oben) entweder 1-zu-1 oder n-zu-1.
Mehrfachvererbung kann durch mehrere Vererbungsbeziehungen ausgedrückt
werden.
Die Vererbungsbeziehung drückt die is-a (ist) Beziehung aus (z.B. Ein Angestellter
ist eine Person). Sie ist zu unterscheiden von der has-a (hat) Beziehung (z.B. eine
Person hat eine Adresse). Die has-a Beziehung ist eine Beziehung zu einem
anderen Entitätstyp, der quasi ein indirektes Attribut des Ursprungstyps darstellt. Die
besondere Darstellungsform der is-a Beziehung ist eine Erweiterung des ERM nach
Chen. Die ursprüngliche Darstellung macht nochmals deutlich, daß es sich bei is-a
lediglich um eine spezielle Beziehung handelt.
Name
Person
Vorname
is-a
Arbeiter
Bild 1-8
Geb.Dat
Std.
Lohn
Alte Darstellung der is-a Beziehung
Mit Hilfe der Vererbungsbeziehung können auch mehrstufige Hierarchien aufgebaut
werden. Es ist jedoch zu beachten, daß eine Entität der untersten Hierarchiestufe
zum Zugriff auf Attribute die es von ganz oben „geerbt“ hat sämtliche Beziehungen
durchlaufen muß. Dies kann bei der Implementierung u.U. lange dauern. Demnach
sollte man den Gebrauch der Vererbung auf ein vernünftiges Maß beschränken.
1.4 Umwandlung in ein relationales Modell
Das ERM ist ein allgemeines Modell zur Modellierung von Daten, mit der
Implementierung (z.B. von Beziehungen) und den Beschränkungen, die dabei
möglicherweise beachtet werden müssen, befaßt es sich nicht. Dieser Unterschied
besteht generell zwischen Modellen und Implementierungen. Modelle sind allgemein
und gehen von einer „idealen Welt“ aus, die Implementierung ist jedoch immer
gewissen Beschränkungen unterworfen.
Standard zur Implementierung von Datenbanken sind heute Relationale DBMS. Sie
beruhen auf dem relationalen Datenmodell, welches uns in Kapitel 2 beschäftigen
wird. Zur Implementierung eines ERM mit Hilfe einer solchen Datenbank muß dieses
zunächst in ein Relationales Modell konvertiert werden. Obwohl diese Konvertierung
über weite Strecken keine Änderung des eigentlichen Modells notwendig macht,
sondern eher einem simplen Wechsel der Notation gleichkommt gibt es hier und da
Dinge, die man beachten muß. So kennt zum Beispiel das relationale Modell keine
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
12
m-zu-n Beziehungen. Wir werden
unüberwindliches Hindernis darstellt.
jedoch
sehen,
daß
auch
dies
kein
1.5 Zusammenfassung
Das Entity-Relationship Model zur Modellierung von Daten und ihrer Beziehungen
wurde nachträglich in die Strukturierte Analyse aufgenommen. Beziehungen
zwischen Entitäten (Objekten der realen Welt, abstrakt oder konkret) bzw. zwischen
Entitätstypen können einfach oder komplex sein. Komplexe Beziehungen können als
1 zu n oder n zu m-Beziehungen auftreten (Kap. 1.1). Zur Darstellung dieser
Beziehungen gibt es verschiedene grafische Notationen. Liegt eine verbale
Aufgabenstellung vor, so muß man, wenn man die Daten modellieren möchte (Kap.
1.2), Kandidaten für Entitäten, Attribute und Beziehungen finden und hierfür das
ERM-Diagramm in graphischer Form aufstellen. Durch Vergleich mit der verbalen
Aufgabenstellung muß anschließend überprüft werden, ob die Entitäten und ihre
Beziehungen die Anforderungen der Aufgabenstellung erfüllen können. Kap. 1.3
behandelt spezielle Beziehungen wie reflexive Beziehungen, bei denen Entitäten
desselben Typs in verschiedenen Rollen auftreten, und Vererbungsbeziehungen.
Kap. 1.4 gibt einen Ausblick auf Kap. 2, in welchem die Abbildung der logischen
Datenmodellierung des Entity-Relationship Modells auf das Relationale Modell,
welches bei Relationalen Datenbanken implementiert ist, durchgeführt wird.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
13
2 Methoden für den Systementwurf - Datenbankentwurf
Dieses Kapitel befaßt sich mit dem Datenbankentwurf für Relationale Datenbanken.
Ausgangspunkt des Datenbank-Entwurfs ist das Logische Datenmodell, welches mit
Hilfe der Entity-Relationship-Modellierung (siehe Kap. 1) erstellt wurde. Dieses
Logische Datenmodell ist nun umzusetzen auf Relationen, die in einem Relationalen
Datenbankmanagementsystem (RDBMS) gespeichert werden. Bei der Abbildung auf
Relationen sind Regeln zu berücksichtigen (Normalisierungsregeln), die unter
anderem dafür sorgen sollen, daß eine redundante Datenspeicherung vermieden
wird.
Ehe wir auf den Datenbankentwurf für relationale Datenbanken eingehen, betrachten
wir in Kap. 2.1 die Vorteile von Datenbanken und in Kap. 2.2 Zugriffsmöglichkeiten
auf die Daten bei verschiedenen Datenbank-Architekturen.
2.1 Nachteile von Dateien und Vorteile von Datenbanken
Hält ein Programm sich diejenigen Daten in Dateien, die es braucht, so führt dies in
der Praxis oftmals dazu, daß
• Daten redundant gespeichert werden.
Dadurch wird eine Aktualisierung der Daten sehr aufwendig. Außerdem muß jedes
Programm gleiche Funktionen für das Lesen, Schreiben, Aktualisieren, Suchen und
vor allem für die Behandlung von Fehlerfällen beinhalten. Jeder einzelne
Programmierer bestimmt hierbei das Ausmaß der Integritätskontrollen und der
Vorsorgemaßnahmen im Fehlerfall.
Integrität bedeutet z.B., daß wenn ein Mitarbeiter aus dem Datenbestand
"Mitarbeiter" verschwindet, daß er dann auch aus dem Datenbestand
"Firmenkreditkarte" verschwinden muß.
Ein weiterer Nachteil ist, daß ein jedes Programm genau wissen muß, wie die Daten
physikalisch gespeichert sind. Ändert man die Datenstrukturen der Dateien, so muß
man auch die Programme abändern. Isolierte Anwendungen mit Hilfe des
Dateikonzeptes beruhen auf der Annahme, daß stets alles gut geht und alles stabil
bleibt.
Was man aber haben will, ist daß die Programme unabhängig von der
physikalischen Speicherung der Daten sind, ja - wenn es geht - sogar unabhängig
von der logischen Speicherung der Daten.
Das Datenbankkonzept beinhaltet die Datenbank als die Menge der zentral
gespeicherten Daten und das Datenbankverwaltungssystem (DBMS = Data Base
Management System) als Zusammenfassung aller Funktionen zu ihrer Handhabung.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
14
Das Datenbankkonzept zielt darauf ab:
• alle Daten nur einmal zu halten und zwar zentral
• alle Funktionen zum Lesen, Schreiben, Aktualisieren, Suchen nur einmal zentral
zu haben
• alle erforderlichen Funktionen zur Integritätskontrolle und Fehlerbehandlung in der
erforderlichen Qualität zentral zu halten
• die Programme von den Daten zu entkoppeln (Erhöhung der Datenunabhängigkeit der Programme)
• Sprachen zur leichteren und flexiblen Handhabung der Daten bereitzustellen
Es gibt verschiedene Grade der Datenunabhängigkeit. Hier sei die Einteilung sehr
grob gemacht: Relationale Datenbanken weisen keine physische Datenabhängigkeit
mehr auf. Hier ist die physikalische Speicherung dem Anwendungsprogramm
verborgen. Der Benutzer kennt nur noch die logische Beschreibung der Daten.
2.2 Zugriffsschnittstellen zu Datenbanken
Generell gibt es beim Zugriff auf Datenbanken verschiedene Möglichkeiten der
Zugriffsschnittstelle:
• interne Satzschnittstelle
• logische Satzschnittstelle
• mengenorientierte Schnittstelle
Das relationale Modell arbeitet mit einer mengenorientierten Schnittstelle. In
diesem Modell werden die Daten ausschließlich über inhaltliche Angaben aufeinander bezogen. Es muß nur spezifiziert werden, was gefunden werden soll, nicht
aber, wie der Suchprozeß ablaufen soll. Es erfolgt eine Anforderung an die
Datenbank (1 Datenbank-Anweisung). Gefunden wird eine Menge von Sätzen, die
den Suchkriterien entsprechen.
Bei der satzorientierten Schnittstelle muß man eine oder mehrere DatenbankAnweisungen pro gesuchtem Satz angeben. Geliefert wird immer nur ein einzelner
Satz. Die satzorientierte Schnittstelle verbirgt die physikalische Speicherung. Es gibt
aber einen logischen Zugriffspfad zu den Daten. Es muß angegeben werden, in
welcher Reihenfolge die Daten durchsucht werden (Navigation).
Bei der internen Satzschnittstelle sind physikalische Speicherungsstrukturen wie
interne Sätze und Zugriffsindizes wie B*-Bäume oder Hash-Strukturen sichtbar,
weitere Implementierungsdetails sind aber verborgen. Beim Zugriff ist die
(physikalische) Zugriffsmethode sichtbar. Pro gefundenem Satz müssen ein oder
mehrere Datenbank-Anweisungen angegeben werden.
Es ist vollkommen klar, daß die mengenorientierte Schnittstelle, die beim relationalen
Datenmodell implementiert ist, die komfortabelste Schnittstelle darstellt, da sie
Details vor dem Nutzer verbirgt, und der Nutzer daher weniger wissen muß, um auf
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
15
Daten zugreifen zu können. Natürlich ist mit diesem Komfort eine PerformanceVerschlechterung verbunden.
Das folgende Schichtenmodell zeigt den Unterschied im Abstraktionsgrad und der
Architektur
zwischen
relationalen
Datenbanksystemen
(besitzen
eine
mengenorientierte
Schnittstelle),
hierarchischen
und
netzwerkartigen
Datenbanksystemen (haben eine satzorientierte Schnittstelle) und Datenbanksystemen mit zugriffsmethodenorientierter Programmierschnittstelle
mengenorientierte
DB-Schnittstelle
satzorientierte
DB-Schnittstelle
Interne
Satzschnittstelle
Systempuffer-Schnittstelle
Zugriffspfadunabhängiges
Datenmodell
Zugriffspfadbezogenes
Datenmodell
Zugriffspfadbezogenes
Datenmodell
Satz-/Zugriffspfadverwaltung
Satz/Zugriffspfadverwaltung
Satz-/
Zugriffspfadverwaltung
Systempufferverwaltung
Systempufferverwaltung
Systempufferverwaltung
Externspeicherverwaltung
Externspeicherverwaltung
Externspeicherverwaltung
Datei-Schnittstelle
Geräteschnittstelle
Architektur relationaler
Datenbanksysteme
Bild 2-1
Architektur hierachischer und
netzwerkartiger Datenbanksysteme
Architektur von Datenbanksystemen mit zugriffsmethodenorientierter Schnittstelle
Schichtenmodelle für Schnittstellen zu Datenbanken [9]
2.3 Relationales Daten-Modell
Das Relationale Daten-Modell, kurz Relationen-Modell genannt, wurde 1970 von
E.F. Codd entwickelt. Das Relationen-Modell besteht aus 3 Teilen:
• Objekten:
Die folgenden Objekte müssen definiert werden:
− Relationen (oder Tabellen). Sie entsprechen den Entitätstypen des EntityRelationship Modells
− Tuple (oder Datensatz oder Record). Ein Tuple entspricht einer konkreten
Entität des Entity-Relationship Modells
− Attribut (Spalte). Ein Attribut eines Entitätstyps entspricht einem Attribut einer
Relation und stellt eine Spalte einer Tabelle dar
− Domain (Wertebereich). Der Wertebereich eines Atttributs muß festgelegt
werden, damit bei Änderungen der Attributwerte Syntaxprüfungen durchgeführt
werden können
− Schlüssel. Schlüssel erlauben den Zugriff auf Tuples. Von Bedeutung sind
Candidate-Keys (eindeutige Schlüssel), Primary Keys (Primärschlüssel,
Hauptschlüssel), Alternate Keys (Sekundärschlüssel, Zweit-Schlüssel),
Foreign-Keys (Fremdschlüssel). Die Bedeutung der einzelnen Schlüssel wird im
folgenden noch erläutert werden
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
16
• Operationen:
Abfrage- und Update-Operationen auf den Relationen, sowie zum Anlegen und
Löschen von Relationen
• Regeln:
Diese dienen zur Gewährleistung der Konsistenz der Daten. Hierbei geht es um
die Entity-Integrität und Referentielle Integrität. Beide Integritätsregeln müssen
im folgenden noch erklärt werden
2.3.1 Der Begriff der Relation
In diesem Kapitel sollen die grundlegenden Begriffe, die im Zusammenhang mit
Relationalen Daten-Modellen gebraucht werden, erläutert werden. Da die Basis des
Relationalen Daten-Modells das 1970 von Codd entwickelte Relationen-Modell
(Codd 70) darstellt, soll dieses nun kurz charakterisiert werden.
Als Relation (dt. Beziehung) bezeichnet man eine logisch zusammenhängende
Einheit von Informationen. Eine Relation entspricht einem Entitätstyp. Eine Relation
stellt eine zweidimensionale Tabelle mit einer festen Anzahl von Spalten (Attributen)
und einer variablen Anzahl von Tupeln (Zeilen) dar. Eine Relation ist durch ihren
Namen und die Namen der Attribute eindeutig beschrieben.
Die Anzahl der Attribute einer Relation heißt Degree (Ausdehnungsgrad). Die
Anzahl der Zeilen einer Relation - diese Anzahl kann sich dynamisch ändern und
kann auch Null werden - heißt Kardinalität der Relation.
Eine Relation hat die folgenden Eigenschaften:
Es gibt keine zwei Tupel, die in ihren Attributwerten übereinstimmen. Die Reihenfolge, in der die Tupel einer Relation gespeichert werden, ist nicht definiert, d.h. es
gibt keine Reihenfolge der Tupel. Genausowenig ist die Reihenfolge, in der die
Attribute einer Relation gespeichert werden, definiert.
Macht man den Übergang von dem Entity-Relationship Modell zum Relationalen
Datenbank-Entwurf, so werden - wie wir in Kap. 2.4 noch sehen - Beziehungs-Typen
zwischen zwischen Entitätstypen (Relationen) im Relationalen Modell abgebildet auf
Relationen oder auf Attribute einer Relation.
Die Entitäten als konkrete Objekte werden als Tupel (Datensatz) bezeichnet. Sie
bilden die Zeilen einer Tabelle.
Die Struktur einer Relation wird auch als Relationen-Schema bezeichnet. Das
Relationen-Schema beschreibt den Aufbau der Tupel einer Relation, nicht aber die
einzelnen Tupel-Ausprägungen (Werte). Jedes Tupel einer Relation setzt sich aus
den gleichen Relations-Attributen zusammen. Die Attribute der Relation bilden die
Spaltenüberschriften einer Tabelle. In einer Spalte stehen verschiedene oder gleiche
Attributwerte, die alle einer Domäne angehören.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
17
Atomare Werte der Attribute
Die Attribute bestehen nur aus einem Wert pro Tupel, nicht aus mehreren Werten.
Sie sind „atomar“.
Personal-Nr. Name
Bild 2-2
Vorname Projekt
Datenstruktur mit einer Periodengruppe (Projekt)
Im Beispiel von Bild 2-2 kann ein Firmen-Mitarbeiter in mehreren Projekten
mitarbeiten. Dies kann durch eine Periodengruppe ausgedrückt werden. Das Feld
Projekt kann dabei mehrere Werte annehmen. Solche Periodengruppen sind aber
nicht „atomar“, da mehrere Werte für ein Attribut existieren. Sie sind also im Rahmen
der Relationen nicht zulässig.
2.3.2 Ein einfaches Beispiel
Ein Relationen-Modell beschreibt Objekte der realen Welt durch eine Menge von
Attributen, die die gemeinsamen Eigenschaften der Objekte repräsentieren. Bild 2-3
zeigt ein Beispiel für eine Relation.
Relation Angestellter
• Relation:
ANGESTELLTER (persNr, name, vorname,
geburtsdatum, abtlgNr)
• Primärschlüssel: personalnummer
persNr
name
vorname geburtsdatum abtlgNr
3213654616
9684646366
6546321856
1354585544
...
Maier
Müller
Kunz
Hinz
...
Hans
Heinz
Thomas
Herman
...
Bild 2-3 Relation
15.03.1956
24.09.1945
29.07.1967
10.10.1937
...
13
24
13
12
...
Angestellter
Das folgende Beispiel (siehe Bild 2-4 Domänen ) verdeutlicht noch einmal den
Begriff der Domäne. In dem gezeigten Beispiel gibt es 3 Domänen:
• Domäne 1 ist eine Folge von ganzen Zahlen (Datentyp INTEGER).
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
18
• Domäne 2 ist eine Folge von Buchstaben (Datentyp CHAR), die Namen von
Teilen darstellen.
• Domäne 3 ist ebenfalls eine Folge von Buchstaben (vom selben Typ wie Domäne
2), die eine Farbe darstellen.
Das Beispiel hat 5 Tupel. Jedes Tupel setzt sich aus 3 Attributen zusammen, von
denen jeder Attributwert einer anderen Domäne entstammt. Die Reihenfolge der
Attribute in den Tupeln muß eingehalten werden. Mit anderen Worten, Attribute
müssen immer unter den dazugehörigen Attributnamen stehen. Eine andere
Geschichte ist, daß die Reihenfolge, in der die Attribute einer Relation gespeichert
werden, nicht definiert ist.
Domäne 1
10
10
10
10
10
Domäne 3
Domäne 2
Bolzen
schwarz
Rohr
Mutter
grau
rot
Stift
grün
Schraub
101
Bolzen
schwarz
102
Mutter
rot
103
Schraube
grün
104
Stift
grau
105
Rohr
weiß
weiß
Tupel
Bild 2-4
Domänen
2.3.3 Schlüssel einer Relation
Eine Zusammenstellung von Attributwerten heißt Candidate-Key (SchlüsselKandidat), wenn die Werte, die diese Attribute annehmen, stets ein Tupel eindeutig
identifizieren. Ein Candidate-Key kann aus einem oder mehreren Atttributnamen
bestehen. Eine Relation kann einen oder mehrere Candidate-Keys besitzen. Da in
einer Relation keine zwei identischen Tupel existieren, muß es immer mindestens
einen Candidate Key - nämlich die Zusammenstellung aller Attributnamen - geben.
Ein Candidate-Key darf keine überflüssigen Attributnamen enthalten. Diese müssen
entfernt werden, jedoch darf keines der Schlüssel-Attribute vernachlässigt werden,
damit die eindeutige Identifizierbarkeit nicht verloren geht. Mit anderen Worten, ein
Candidate-Key umfaßt soviel Attributnamen wie nötig und sowenig wie möglich.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
19
Ein Primary-Key ist ein Candidate-Key, der zum Primary-Key erklärt wird oder der
als neues Attribut (z.B. Personal-Nummer) speziell zu diesem Zweck eingeführt wird.
Eine Relation besitzt genau einen Primärschlüssel. Um auf ein Tupel zugreifen zu
können oder ein neues Tupel einzufügen zu können, ist die Angabe des jeweiligen
Primärschlüssels erforderlich.
Fremdschlüssel dienen dazu, um Relationen zu verknüpfen und um die referentielle
Integrität der Datenbank (siehe später) zu gewährleisten. Ein Fremdschlüssel einer
Relation muß dabei immer Primärschlüssel einer anderen Relation sein. Der
Fremdschlüssel referenziert auf ein Tupel der Relation, in der er Primärschlüssel ist.
Wie wir in Kapitel 2.4 noch sehen werden, sind das Tupel der Relation mit dem
Fremdschlüssel und das Tupel der Relation mit dem entsprechenden
Primärschlüssel miteinander durch eine 1 zu n Beziehung verknüpft. Dies wird als
Primärschlüssel-Fremdschlüssel-Prinzip bezeichnet. Um Fremdschlüssel zu
kennzeichnen, wird ihnen ein # Zeichen nachgestellt.
2.3.4 Relationale Integritätsregeln
Mit der Entity-Integrität wird sichergestellt, daß jedes Tupel (Entity) in einer Relation
einen eindeutigen Schlüssel besitzt. Da NULL-Werte nicht eindeutig sind, darf der
Primary-Key keinen NULL-Wert enthalten.
Durch die referentielle Integrität wird sichergestellt, daß jedem Wert eines ForeignKeys in einer Relation R2 gleich ist einem Wert des Primary-Keys in einer Relation
R1 oder daß der Wert des Foreign-Keys ein NULL-Wert ist. Referentielle Integrität
bedeutet z.B. daß wenn ein Mitarbeiter aus dem Datenbestand "Mitarbeiter"
verschwindet, daß er dann auch aus dem Datenbestand "Firmenkreditkarte"
verschwinden muß.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
20
2.4 Abbildung von Entitätstypen und Beziehungen zwischen Entitätstypen auf
Relationen
Nachdem in Kapitel 2.3 die Grundlagen des Relationalen Daten-Modells erörtert
wurden, soll nun in diesem Kapitel der Datenbankentwurf im Vordergrund stehen.
Auszugehen ist vom logischen Datenmodell, welches mit Hilfe der EntityRelationship-Modellierung erstellt wurde. Die Fragestellung ist nun, wie die
Entitätstypen und ihre Beziehungen umzusetzen sind in Datenbank-Tabellen.
Beginnen wir zunächst mit dem einfachsten, der Abbildung von 1 zu 1-Beziehungen
(Kap. 2.4.1). Es folgt dann die Umsetzung von 1 zu n-Beziehungen (in Kap. 2.4.2)
und last not least die Abbildung von n zu m-Beziehungen auf Tabellen (Kap. 2.4.3).
2.4.1 Datenbankentwurf für 1 zu 1 -Beziehungen des ERM
Anhand eines Beispiels wird die Modellierung einer 1 zu 1-Beziehung gezeigt.
Es werden zwei Möglichkeiten des Entwurfs vorgestellt. Bei der ersten Möglichkeit
werden 2 Relationen entworfen, bei der anderen nur eine einzige Relation.
Den Ausgangspunkt für den Entwurf stellt das folgende Entity-RelationshipDiagramm dar.
ERM:
leitet
1, 1
Mitarbeiter
0,1
Abteilung
Bild 2-5 ERM
Es gibt die Entitätstypen "Mitarbeiter" und "Abteilung", sowie die Beziehung "leitet".
Jede Abteilung wird von einem Mitarbeiter geleitet. Jeder Mitarbeiter kann keine oder
maximal 1 Abteilung leiten.
Möglichkeit 1: Entwurf von 2 Relationen
Jeder Entitätstyp wird durch eine eigene Relation dargestellt. Die Beziehung
zwischen beiden Relationen wird hergestellt durch einen Fremdschlüssel in einer der
beiden oder in beiden Relationen. Der Fremdschlüssel entspricht dann dem
Primärschlüssel der verbundenen Relation.
Eventuell vorhandene Link-Attribute (hier keine vorhanden) werden als Attribute in
einer der beiden Relationen abgebildet.
In unserem Beispiel leitet ein Mitarbeiter entweder eine oder keine Abteilung (0,1).
Würde man den Fremdschlüssel bei der Relation Mitarbeiter modellieren, hätte man
folgendes Problem: Alle Mitarbeiter, die keine Abteilung leiten, müßten NULL als
Attributwert bei dem Fremdschlüssel (abtlgNr#) haben. Deshalb wird hier
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
21
entschieden in der Relation ABTEILUNG mit dem Fremdschlüssel PersNr# den
Bezug zwischen beiden Tabellen herzustellen.
Der Datenbankentwurf sieht dann folgendermaßen aus:
Relation (Entitätstyp)
Relation (Entitätstyp)
(persNr, name, vorname)
(abtlgNr, name, persNr# ... )
MITARBEITER
ABTEILUNG
Relationales Modell:
1, 1
leitet
Mitarbeiter
Bild 2-6
0, 1
Abteilung
Relationales Modell
Möglichkeit 2: Entwurf von 1 Relation. Abbildung der Entitätstypen auf
Attribute dieser Relation
Bei jeder 1 zu 1 Beziehung können die Attribute beider Entitätstypen in einer
Relation zusammengefaßt werden, hier in der Relation ABTEILUNG. Dies ist
allerdings nur sinnvoll, wenn der Entitätstyp ANGESTELLTER keine weiteren
Beziehungen eingeht und deshalb nicht eigenständig modelliert wird. Ein
Fremdschlüssel ist hier natürlich nicht notwendig.
Der Datenbankentwurf sieht dann folgendermaßen aus:
Relation (Entitätstyp)
ABTEILUNG
Relationales Modell:
Bild 2-7
(persNr, name, vorname,
abtlgNr, name, leiternr... )
Abteilung
Relationales Modell
2.4.2 Datenbankentwurf für 1 zu n -Beziehungen des ERM
Anhand eines Beispiels soll die Abbildung auf Relationen für 1 zu n-Beziehungen
erläutert werden.
In diesem Beispiel arbeiten in einer Abteilung mehrere Mitarbeiter, mindestens
jedoch 1 Mitarbeiter. Jeder Mitarbeiter arbeitet in genau einer Abteilung. Zur
Beziehung gehören 2 Attribute: Eintrittsdatum in die Abteilung und Aufgabe in der
Abteilung.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
22
ERM:
Eintrittsdatum
Aufgabe
gehört zu
1, 1
1, n
Mitarbeiter
Bild 2-8
Abteilung
ERM
Zunächst wäre man versucht, eine Periodengruppe zu entwerfen:
Abteilungs-Nr. Mitarbeiter
Bild 2-9
Eintrittsdatum Aufgabe
Entwurf mit Periodengruppen
Periodengruppen sind jedoch - wie Sie bereits wissen - nicht zulässig. Also muß man
anders vorgehen: Ein Entwurf mit nur 1 Relation geht also nicht, man muß 2
Relationen entwerfen. Jede 1 zu n Beziehung kann durch 2 Relationen definiert
werden. Die Beziehung wird als Fremdschlüssel in der Relation modelliert, deren
Tupel n-fach in der Beziehung auftreten (siehe Modellierungsvergleich auf der
nächsten Seite). Der Fremdschlüssel entspricht dem Primärschlüssel in der 1-fach
auftretenden Relation. Würde man den Fremdschlüssel in der Relation angeben
wollen, deren Tupel einfach in der Beziehung auftreten, so hätte man wieder das
Problem der Periodengruppe.
Die Link-Attribute werden in der n-fachen Relation modelliert (im folgenden fett
dargestellt). Der Datenbankentwurf sieht dann folgendermaßen aus:
Relation (Entitätstyp)
MITARBEITER
Relation (Entitätstyp)
ABTEILUNG
(persNr, name, vorname, abtlgNr #,
eintritt, aufgabe)
(abtlgNr, name, leiter, ...)
Relationales Modell:
Mitarbeiter
1, n gehört zu 1, 1
Bild 2-10 Relationales
Abteilung
Modell
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
23
Modellierungsvergleich:
Beispiel: 8 Mitarbeiter arbeiten in jeweils einer, von insgesamt 3 Abteilungen.
Möglichkeit 1:
Mitarbeiter
PersNr
1
2
3
4
5
6
7
8
Name
.....
Vorname
.....
.....
.....
A-Nr#
1
3
2
1
2
3
2
3
Abteilung
A_Nr
1
2
3
Name
An1
An2
An3
Budget
10000
20000
30000
Möglichkeit 2
Mitarbeiter
PersNr
1
2
3
4
5
6
7
8
Name
.....
Vorname
.....
Abteilung
A-Nr
1
2
3
1
2
2
3
3
Name
An1
An2
An3
An1
An2
An2
An3
An3
Budget
10000
20000
30000
10000
20000
20000
30000
30000
PersNr#
1
3
2
4
5
7
6
8
.....
.....
Bei der Möglichkeit 2 werden Name und Budget der Abteilung redundant
gespeichert, zudem wird dadurch mehr Speicherplatz benötigt. Deshalb ist
Möglichkeit 1 vorzuziehen. Bei einer 1 zu n Beziehung wird also die Beziehung als
Fremdschlüssel in der Relation modelliert (hier Relation Mitarbeiter), deren Tupel nfach in der Beziehung auftreten.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
24
2.4.3 Datenbankentwurf für m zu n -Beziehungen des ERM
• Zwischen den Entitäten1 und 2 existiert ein Beziehungs-Typ.
• Jeweils m Tupel der Entität 1 können
mit n Tupeln der Entität 2 in Beziehung stehen.
• Jede Beziehung hat i Attribute.
Beziehungs-Typ
0, n
0, m
Entitäts 1
Entität 2
attribut 1
•••••
attribut i
Bild 2-11 Allgemeine
m zu n Beziehung
Entwurf für allgemeine m zu n-Beziehungen (n > 1, m > 1):
Die beiden Entitäten werden direkt in zwei Relationen 1 und 2 überführt. Die
Beziehung zwischen der Relation 1 und der Relation 2 wird durch eine neue dritte
Relation ausgedrückt. Der Primärschlüssel der dritten Relation setzt sich aus den
Primärschlüssel-Attributen der Relation 1 und aus den Primärschlüssel-Attributen der
Relation 2 zusammen.
Um die obigen Sachverhalte noch etwas zu verdeutlichen, sollen diese anhand eines
Beispiels erläutert werden. Im Beispiel gibt es Projekte, an denen mehrere
Mitarbeiter arbeiten. Ein Mitarbeiter kann in keinem, einem oder mehreren Projekten
mitarbeiten.
In den Bildern unten ist der Übergang vom Entity-Relationship-Modell zum
Relationalen Modell zu sehen. Der Entitätstyp Mitarbeiter des ERM wird zur Relation
MITARBEITER im Relationalen Modell, aus dem Entitätstyp Projekt wird die Relation
PROJEKT und aus der Beziehung "arbeitet mit an" wird die Relation
MA_PROJEKT. Zur Beziehung gehören zwei Attribute:
ERM :
a r b e ite t m it a n
0, m
1, n
M it a r b e it e r
A u fg a b e
P r o je k t
Dauer
Bild 2-12
ERM
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
25
Der Datenbankentwurf sieht dann folgendermaßen aus:
•
Relation
• Relation
• Relation
MITARBEITER
PROJEKT
MA_PROJEKT
(persNr, name, vorname, abtlgNr, ... )
(projektNr, name, leiter, ... )
(persNr#, projektNr#, aufgabe, dauer )
Relationales Modell:
1
1, n
0, m
Mitarbeiter
Bild 2-13
1
Ma_Projekt
Projekt
Relationales Modell
Im folgenden werden die Relationen-Schemata dargestellt:
MITARBEITER
PROJEKT
persNr
name
vorname
abtlgNr ...
projektNr
name
leiter...
131
123
134
112
125
...
Maier
Müller
Kaiser
Berger
Fischle
...
Herbert
Hugo
Fridolin
Karl
Amadeus
...
12
19
12
03
19
...
1
2
3
...
Neuentwicklung
Prototyp
Änderung
...
112
131
112
...
Tabelle 2-2
Tabelle 2-1
MA_PROJEKT
persNr# projektNr#
aufgabe
dauer ...
125
123
134
123
112
...
Test
Arbeiten
Layout
Arbeiten
Planung
...
10
07
15
03
02
...
2
1
3
2
2
...
Tabelle 2-3
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
26
2.5 Normalisierung
Wozu Normalisierung ?
Die Normalisierung hilft uns beim fachgerechten Entwurf von relationalen
Datenmodellen. Bei Beachtung der Normalisierungsregeln werden Redundanzen
beseitigt und Updateanomalien vermieden.
Eine Normalisierung findet stets aufgrund bereits bestehender Tabellen statt.
Ist die Aufgabe in Textform gegeben, müssen die Tabellen zuerst erzeugt werden.
Dafür werden
geeignete Testdaten benötigt, die den Wertebereich der
Aufgabenstellung abdecken. Die Normalisierung kann nur bezogen auf diese Daten
stattfinden. Mit anderen Daten ergibt sich eine andere Normalisierung.
Anhand eines hier durchgeführten Beispiels sollen nun die Grundlagen und Regeln
der Normalisierung bekanntgemacht werden.
2.5.1 Beispiel 1
An einer Fachhochschule für Technik werden Vorlesungen von Dozenten gehalten.
Die Dozenten gehören zu einem Fachbereich, welcher von einem Dekan geleitet
wird. Die Verwaltung erfolgt in einer relationalen Datenbank.
Dann könnte ein Eintrag in der Datenbank wie folgt aussehen:
Dozent_Nr
4711
4713
4712
Fachbereich
TI
NT
NT
Dekan_Nr
007
003
003
Semester
TI 1
NT 2
TI 1
Fach
DV
Physik
Mathe
Anz_Stud
48
43
48
Tabelle 2-4
Als ein Pflegeproblem dieses Beispiels (weitere folgen später), sei hier das folgende
erwähnt:
• Ändert sich die maximale Anzahl der Studenten in einem Semester, so muß in
jedem Eintrag in der Datenbank, in dem die Anzahl Studenten vorkommt, die
maximale Anzahl der Studenten geändert werden
• Wird ein Eintrag vergessen, kommt es zu Inkonsistenzen
Abhilfe schafft die Normalisierung. Dann genügt ein Zugriff auf ein Tupel. Inhaltliche
widersprüchliche Datensätze kommen somit nicht vor. Zudem werden die Updates
sehr änderungsfreundlich (Minimalisierung der Anzahl der Updates).
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
27
Wie die Übersicht zeigt, gibt es viele verschiedene Normalformen. In der Praxis
beschränkt man sich jedoch auf die dritte Normalform.
< weitere Normalformen >
Relation in 3. NF
Relation in 2. NF
Relation in 1. NF
unnormalisierte Relationen
Übersicht Normalformen
Jede Normalform enthält implizit die vorgehende Normalform (die 3. Normalform
enthält die zweite und damit die erste Normalform).
Um auf eine Normalform zu kommen, müssen nicht zwangsweise die vorgehenden
Normalformen durchlaufen werden ( d.h. man kann z.B. mit etwas Übung direkt auf
die 3. NF kommen).
2.5.1.1 Unnormalisierte Relationen
Eine Relation heißt unnormalisiert, wenn die Attribute nicht atomare Werte enthalten,
gegenseitig Beziehungen eingehen oder nicht funktionell abhängig vom eindeutigen
Primärschlüssel sind.
Hochschulbetrieb 1
Dozent_Nr
4711
4713
4712
Fachber.
TI
NT
NT
Dekan_Nr
007
003
003
Semester
TI 1, TI 1
NT 2
TI 1
Fach
DV, ET
Physik
Mathe
Anz_Stud
48
43
48
Tabelle 2-5
Wie in diesem Beispiel (Hochschulbetrieb 1) zu sehen ist, werden alle 3 Normalformen verletzt.
2.5.1.2 Erste Normalform
Eine Relation befindet sich in der 1. Normalform, wenn alle zugrundeliegenden
Wertebereiche nur atomare Werte enthalten. Vor der Überführung in die zweite
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
28
Normalform ist die Einführung des Primärschlüssels von Bedeutung. Der
Primärschlüssel muß so gewählt werden, daß jede Zeile eindeutig ist. In diesem
Beispiel wählen wir Dozent_Nr, Semester und Fach als Primärschlüssel aus.
Hochschulbetrieb 2
Wir sehen hier die Relation, in der die 1. NF erfüllt ist, aber die 2. und die 3. NF
verletzt sind.
Dozent_Nr
Fachber.
Dekan_Nr Semester
Fach
Anz_Stud
11
TI
5
TI 1
Physik
55
11
TI
5
TI 2
DV
51
11
TI
5
TI 1
Mathe
55
21
NT
8
NT 1
DV
58
21
11
NT
TI
8
5
TI 1
TI 2
Mathe
Mathe
55
51
Tabelle 2-6
2.5.1.3 Zweite Normalform
Eine Relation ist in 2. Normalform, wenn sie in 1. Normalform ist und wenn jedes
Nichtschlüsselattribut voll funktional abhängig ist vom Primärschlüssel. Felder, die
nur von einem Schlüsselteil abhängen, werden separat modelliert.Die zweite
Normalform kann somit nur verletzt werden, wenn der Primärschlüssel aus mehr als
einem Attribut zusammengesetzt ist.
Hochschulbetrieb 3
In diesem Beispiel ist die 1. und 2. Normalform erfüllt. Aus einer Tabelle entstehen 3
Tabellen (separate Modellierung). Das ist notwendig, da
• die Anzahl der Stunden nur vom jeweiligen Semester abhängt
• und da Anz_Stud/Fachbereich/Dekan_Nr nicht voll funktional abhängig sind von
Semester/Dozent-Nr/Fach (Primärschlüssel)
Dozent_Nr
11
21
Dozent
Fachbereich
TI
NT
Dekan_Nr
5
8
Tabelle 2-7
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
29
Dozent/Vorlesung
Semester Dozent_Nr
Fach
TI 1
11
Physik
TI 2
11
DV
TI 1
11
Mathe
NT 1
21
DV
TI 1
21
Mathe
TI 2
11
Mathe
Tabelle 2-8
Beachten Sie, daß die Relation Dozent/Vorlesung den zusammengesetzten
Primärschlüssel Semester/Dozent-Nr/Fach besitzt.
Anz_Sem
Semester
TI 1
TI 2
NT 1
Anz_Stud
55
51
58
Tabelle 2-9
Vorteil:
Ändert sich die max. Anzahl der Studenten eines Semesters, so
genügt jetzt das Updaten eines Tupels.
2.5.1.4 Dritte Normalform
Eine Relation ist genau dann in der 3. Normalform, wenn sie in 1. und 2. NF ist und
wenn alle Nichtschlüsselattribute gegenseitig unabhängig, aber voll funktional
abhängig vom gesamten Primärschlüssel sind.
Hochschulbetrieb 4:
Im Beispiel Hochschulbetrieb 3 ist Dozent_Nr der Primärschlüssel der Relation
Dozent. Die Attribute Fachbereich/Dekan_Nr sind Nichtschlüsselattribute. Da sie
gegenseitig abhängig sind (zu einem Fachbereich gehört der entsprechende Dekan)
ist die dritte Normalform verletzt. Dies ist im Beispiel Hochschulbetrieb 4 korrigiert: es
entsteht eine weitere Tabelle (Dekan).
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
30
Dozent
Dozent_Nr Fachber
11
TI
21
NT
Tabelle 2-10
Dekan
Dekan_Nr Fachber
5
TI
8
NT
Tabelle 2-11
Dozent/Vorlesung
Semester Dozent_Nr
Fach
TI 1
11
Physik
TI 2
11
DV
TI 1
11
Mathe
NT 1
21
DV
TI 1
21
Mathe
11
Mathe
TI 2
Tabelle 2-12
Anz_Sem
Semester
TI 1
TI 2
NT 1
Tabelle 2-13
Vorteil:
Anz_Stud
55
51
58
Wechselt jetzt ein Dozent den Fachbereich oder ändert sich der
Dekan in einem Fachbereich, so genügt jetzt das Updaten eines
einzigen Attributes.
Mit der Modellierung in 4 Tabellen erreichen wir in diesem Beispiel die Einhaltung
aller 3 Normalformen.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
31
2.5.2 Beispiel 2
Nicht normalisierte Tabelle:
Matrikelnr.
678430
562721
Student-Name
Gerhard Gscheidle
Walter Weisheit
628389
Schorsch Schlaule
621285
Bodo Bildung
Vorlesungstitel
Mathematik
Vorlesungsnr.
2001
Chemie
3010
Compilerbau
7203
Mathematik
2001
Physik
2002
Chemie
3010
Compilerbau
7203
Mathematik
2001
Physik
2002
Professor-Name
Albert Algebra
Ingo Integral
Fridolin
Fermenter
Richard Reagenz
Patrick Parser
Albert Algebra
Ingo Integral
Freddy Force
Ingo Integral
Fridolin
Fermenter
Richard Reagenz
Patrick Parser
Albert Algebra
Ingo Integral
Freddy Force
Ingo Integral
Tabelle 2-12
2.5.2.1 Erste Normalform
Die erste Normalform beinhaltet nur atomare Feldinhalte. In jedem Feld darf nur ein
Wert stehen. Überzählige Werte führen zu neuen Zeilen.
Tabelle in 1. Normalform:
Matrikelnr.
678430
678430
678430
678430
678430
562721
562721
562721
562721
628389
628389
628389
621285
621285
621285
621285
Student-Name
Gerhard Gscheidle
Gerhard Gscheidle
Gerhard Gscheidle
Gerhard Gscheidle
Gerhard Gscheidle
Walter Weisheit
Walter Weisheit
Walter Weisheit
Walter Weisheit
Schorsch Schlaule
Schorsch Schlaule
Schorsch Schlaule
Bodo Bildung
Bodo Bildung
Bodo Bildung
Bodo Bildung
Vorlesungstitel
Mathematik
Mathematik
Chemie
Chemie
Compilerbau
Mathematik
Mathematik
Physik
Physik
Chemie
Chemie
Compilerbau
Mathematik
Mathematik
Physik
Physik
Vorlesungsnr.
2001
2001
3010
3010
7203
2001
2001
2002
2002
3010
3010
7203
2001
2001
2002
2002
Professor-Name
Albert Algebra
Ingo Integral
Fridolin Fermenter
Richard Reagenz
Patrick Parser
Albert Algebra
Ingo Integral
Freddy Force
Ingo Integral
Fridolin Fermenter
Richard Reagenz
Patrick Parser
Albert Algebra
Ingo Integral
Freddy Force
Ingo Integral
Tabelle 2-13
Vor der zweiten Normalform führen wir den Primärschlüssel ein. In diesem Beispiel
handelt es sich um einen aus drei Attributen zusammengesetzten Primärschlüssel.
Primärschlüsselattribute werden unterstrichen dargestellt. Es wäre hier auch möglich
gewesen, den Primärschlüssel aus anderen Schlüsseln zusammenzusetzen. Es
wäre möglich statt der Matrikelnummer den Studentennamen und statt der
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
32
Vorlesungsnummer den Vorlesungstitel zu verwenden. Die Möglichkeit wie der
Schlüssel gesetzt werden kann, wird durch die verwendeten Testdaten bestimmt
2.5.2.2 Zweite Normalform
Bei der Überführung in die zweite Normalform werden die Nichtschlüsselattribute
betrachtet. Sie dürfen nur vom Gesamtschlüssel abhängen, nicht von Teilschlüsseln.
Dies wird als funktionale Abhängigkeit bezeichnet. In unserem Beispiel ist der
Student von der Matrikelnummer und die Vorlesung von der Vorlesungsnummer
abhängig. Beide sind jedoch nur vom Teilschlüssel abhängig. Deshalb werden zwei
neue Tabellen gebildet.
Matrikelnr.
678430
678430
678430
678430
678430
562721
562721
562721
562721
628389
628389
628389
621285
621285
621285
621285
Vorlesungsnr.
2001
2001
3010
3010
7203
2001
2001
2002
2002
3010
3010
7203
2001
2001
2002
2002
Professor-Name
Albert Algebra
Ingo Integral
Fridolin Fermenter
Richard Reagenz
Patrick Parser
Albert Algebra
Ingo Integral
Freddy Force
Ingo Integral
Fridolin Fermenter
Richard Reagenz
Patrick Parser
Albert Algebra
Ingo Integral
Freddy Force
Ingo Integral
Tabelle 2-14
Matrikelnr.
678430
562721
628389
621285
Student-Name
Gerhard Gscheidle
Walter Weisheit
Schorsch Schlaule
Bodo Bildung
Tabelle 2-15
Vorlesungsnr.
2001
3010
7203
2002
Vorlesungstitel
Mathematik
Chemie
Compilerbau
Physik
Tabelle 2-16
2.5.2.3 Dritte Normalform:
In der dritten Normalform darf es keine funktionalen Abhängigkeiten geben zwischen
Attributen, die nicht zum Schlüssel gehören. Durch geeignete Wahl der Testdaten
und des Primärschlüssels bei der 1. Normalform befindet sich dieses Beispiel bereits
in der dritten Normalform.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
33
2.5.2.4 Redunzanzfreiheit
Die Tabelle 2-14 weist jedoch immer noch redundante Information auf. Es handelt
sich um die Verschmelzung zweier einfacherer Relationen. Durch eine
Normalisierung bis zur 5. Normalform zerfällt diese Tabelle in zwei Tabellen:
Matrikelnr.
678430
678430
678430
562721
562721
628389
628389
621285
621285
Vorlesungsnr.
2001
3010
7203
2001
2002
3010
7203
2001
2002
Tabelle 2-17
Vorlesungsnr.
2001
2001
3010
3010
7203
2002
2002
Professor-Name
Albert Algebra
Ingo Integral
Fridolin Fermenter
Richard Reagenz
Patrick Parser
Freddy Force
Ingo Integral
Tabelle 2-18
Es ist nicht erforderlich, daß nur zwei Spalten pro Tabelle übrigbleiben! Dies ergibt
sich lediglich durch unser einfach gewähltes Beispiel. Bei den Änderungen an den
Matrikelnummern ergibt sich jedoch ein Problem, auf das wir später bei der
Modellierung noch eingehen werden.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
34
2.5.3 Modellierung
Aufgabenstellung
Für eine Modellierung wird meist eine Beschreibung der Aufgabe als Ausgangspunkt
verwendet:
Für eine Fachhochschule soll ein Verwaltungsprogramm für Professoren,
Studenten und Vorlesungen erstellt werden. Professoren besitzen einen Namen.
Studenten besitzen einen Namen und eine Matrikelnummer. Jede Vorlesung hat
ihre eigene Vorlesungsnummer und einen Vorlesungstitel.
Jeder Student kann mehrere Vorlesungen besuchen. Ein Professor kann mehrere
Vorlesungen halten. In einer Vorlesung können mehrere Studenten sein. Eine
Vorlesung kann von mehreren Professoren gehalten werden.
Je nach Modellierungsart entstehen aus dem Text Entitäten, Relationen oder
Objekte. Diese enthalten verschiedene Attribute und sind untereinander mit
Beziehungen verknüpft.
Objekt-Darstellungsform:
Ein solches Modell läßt sich einfach in relationale Tabellen umwandeln. Die hier
dargestellten m:n Verknüpfungen müssen aufgelöst werden, indem
Zwischentabellen (Zwischenrelationen) erzeugt werden.
Relationale Darstellungsform:
Jeder Relation wird ein eindeutiger Primärschlüssel hinzugefügt. Die Beziehungen
werden über Fremdschlüssel aufgelöst. Die Trennung zwischen fachlichen Attributen
und Beziehungsattributen erleichtert die Arbeit mit den Tabellen. Die
Beziehungsattribute sind in der unteren Grafik mit Großbuchstaben abgekürzt.
Primärschlüssel sind wieder unterstrichen, Fremdschlüsseln folgt das Zeichen ‘#’.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
35
Tabellen:
Mit den Beispieldaten der Normalisierung ergeben sich folgende Tabellen. Es sind in
diesem Beispiel relationale Tabellen entstanden, die der dritten Normalform
genügen. Es ist außerdem leicht ersichtlich, daß diese Tabellen redundanzfrei sind.
Student
SNR
1
2
3
4
Matrikelnr.
678430
562721
628389
621285
Name
Gerhard Gscheidle
Walter Weisheit
Schorsch Schlaule
Bodo Bildung
Tabelle 2-19
Vorlesung
VNR Vorlesungsnr.
1
2001
2
3010
3
7203
4
2002
Vorlesungstitel
Mathematik
Chemie
Compilerbau
Physik
Tabelle 2-20
Professor
PNR Name
1
Albert Algebra
2
Ingo Integral
3
Fridolin Fermenter
4
Richard Reagenz
5
Patrick Parser
6
Freddy Force
Hört
SNR
1
1
1
2
2
3
3
4
4
VNR
1
2
3
1
4
2
3
1
4
Tabelle 2-21
Liest
VNR
1
1
2
2
3
4
4
PNR
1
2
3
4
5
6
2
Tabelle 2-22
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
36
2.5.3.1 Vergleich der Methoden
Nachfolgendes Schema zeigt den Zusammenhang zwischen Modellierung und
Normalisierung. Bei einer Aufgabenstellung in Form einer nicht normalisierten
Tabelle ist nur der Schritt der Normalisierung notwendig. Dieser ist jedoch äußerst
schwierig. Diese Aufgabenstellung kommt in der Praxis auch kaum vor. Weit
häufiger hat man zum Beispiel nach einer Besprechung eine Aufgabenstellung in
textueller Form vorliegen. Bei dieser sind bei beiden Wegen zwei Schritte notwendig.
Bei der Erstellung einer Tabelle aus einer Textaufgabe ergibt sich das Problem die
richtigen Testdaten zu finden. Diesesollen möglichst alle auftretenden Möglichkeiten
abdecken.
2.5.3.2 Modellierung impliziert Normalisierung
Mit einer sachgerechten Modellierung kann man sich die schwierige Normalisierung
ersparen. Die einzelnen Schritte der Normalisierung werden implizit bei der
Modellierung durchgeführt:
1. Normalform Atomare Werte
Die Attribute der verschiedenen Modellierungsverfahren sind per Definition bereits
atomar. Es können nicht zwei Werte in einem Attribut gespeichert werden. Werte
von Attributen sind nicht teilbar.
2. Normalform funktionale Abhängigkeit
3. Normalform transitive Abhängigkeit
Durch die fachliche Modellierung werden die Attribute den Objekten zugeordnet. Sie
sind somit stets vom Objekt funktional abhängig. Für alle nicht fachlichen
Abhängigkeiten zwischen Relationen, die bei der Auflösung von Vererbung, usw.
entstehen, gibt es feste Umwandlungsregeln.
Über die oben beschriebene dritte Normalform hinaus wird mit einer Modellierung
meist eine Redundanzfreiheit erreicht, die beim Normalisierungsverfahren erst in der
5. Normalform erreicht wird. Die Modellierung mit modernen Verfahren bietet darüber
hinaus die Möglichkeit komplexere Konstrukte wie Vererbung (Generalisierung ──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
37
Spezialisierung) und Aggregation zu verwenden. Für die Umwandlung eines Modells
in konkrete Datenbanktabellen gibt es bereits Softwarewerkzeuge.
2.5.4 Zusammenfassung
Im folgenden Bilde werden die ersten drei Normalformen noch einmal zusammengestellt:
1. NF: funktionale Abhängigkeit
vom Primärschlüssel
S1
S2
A
B
C
D
...
3. NF: keine funktionalen Abhängigkeit
zwischen Nichtschlüsselattribute
2. NF: keine funktionale Abhängigkeit
von Schlüsselteilen
Werden die Daten von Programmsystemen in Dateien gespeichert, so führt dies in
der Praxis oft zur redundanten Speicherung der Daten und damit auch zu
inkonsistenten Datenbeständen. Ein Programm muß die physikalische Speicherung
der Daten kennen, sowie Routinen für das Lesen, Schreiben, Aktualisieren, Suchen
vor allem für das Fehlerhandling beinhalten. Was man aber haben will, ist daß
Programme
unabhängig
von
der
physikalischen
Speicherung
sind
(Datenunabhängigkeit der Programme), und daß die Routinen für das Lesen,
Schreiben, Aktualisieren, Suchen und Fehlerhandling nur einmal zentral vorhanden
sein müssen. Solche Vorteile werden von Datenbankmanagementsystemen geboten
(Kap. 2.1). Beim Zugriff auf Datenbanken gibt es interne Satzschnittstellen, logische
Satzschnittstellen und mengenorientierte Schnittstellen (Kap. 2.2). Relationale
Datenbanken besitzen eine mengenorientierte Schnittstelle. Relationale
Datenbanken enthalten Relationen, die in Form von Tabellen dargestellt werden
(Kap. 2.3). Die Relationen entsprechen den Entitätstypen des Entity-Relationship
Modells. Ein Attribut eines Entitätstyps entspricht einem Attribut einer Relation und
stellt eine Spalte einer Tabelle dar. Eine Zeile (Tupel) entspricht einer konkreten
Entität des Entitättyps. Die Attribute bestehen nur aus einem Wert pro Tupel, nicht
aus mehreren Werten. Sie sind „atomar“. Eine Zusammenstellung von Attributwerten
heißt Candidate-Key, wenn die Werte, die diese Attribute annehmen, stets ein Tupel
eindeutig identifizieren. Eine Relation kann einen oder mehrere Candidate-Keys
besitzen. Ein Primary-Key ist ein Candidate-Key, der zum Primary-Key erklärt wird
oder der als neues Attribut speziell zu diesem Zweck eingeführt wird.
Fremdschlüssel dienen dazu, um Relationen zu verknüpfen und um die referentielle
Integrität zu gewährleisten. Durch die referentielle Integrität wird sichergestellt, daß
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
38
jedem Wert eines Foreign-Keys in einer Relation R2 gleich ist einem Wert des
Primary-Keys in einer Relation R1 oder daß der Wert des Foreign-Keys ein NULLWert ist. Referentielle Integrität bedeutet z.B., daß wenn ein Mitarbeiter aus dem
Datenbestand "Mitarbeiter" verschwindet, er dann auch aus dem Datenbestand
"Firmenkreditkarte" verschwinden muß.
Für den Datenbankentwurf müssen nun die Entitätstypen und die Beziehungen
zwischen Entitätstypen abgebildet werden auf Relationen. Eine 1 zu 1 -Beziehung
kann dabei auf 2 Relationen oder auf 1 Relation abgebildet werden. Im zweiten Fall
werden die Entitätstypen auf Attribute dieser Relation abgebildet. Eine 1 zu n
Beziehung wird auf 2 Relationen abgebildet, eine n zu m Beziehung auf 3 Relationen
(Kap. 2.4).
Um Redundanzen zu beseitigen und Update-Anomalien zu vermeiden, werden die
Relationen normalisiert (Kap. 2.4). In der Praxis geht man bis zur 3. Normalform. Da
die Normalisierung aber die Zahl der Tabellen erhöht, gibt es in der Praxis auch
Fälle, wo man aus Performance-Gründen auch nicht normalisierte Relationen
verwendet. Jede Normalform enthält implizit die vorhergehende Normalform (die 3.
Normalform enthält die 2. und damit die 1. Normalform). Eine Relation befindet sich
in der ersten Normalform, wenn alle Attribute nur atomare Werte enthalten. Eine
Relation ist in der 2. Normalform, wenn sie in 1. Normalform ist und wenn jedes
Nichtschlüsselattribut voll funktional abhängig ist vom Primärschlüssel. Felder, die
nur von einem Schlüsselteil abhängen, werden separat modelliert. Die zweite
Normalform kann somit nur verletzt werden, wenn der Primärschlüssel aus mehr als
einem Attribut zusammengesetzt ist. Eine Relation ist genau dann in der 3.
Normalform, wenn sie in 1. und 2. Normalform ist und wenn alle
Nichtschlüsselattribute gegenseitig unabhängig sind, aber voll funktional abhängig
vom gesamten Primärschlüssel sind. Wenn Attribute gegenseitig abhängig sind,
entsteht eine weitere Tabelle.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
39
3 SQL
3.1 Allgemeines zur Sprache SQL
SQL, ausführlich 'Structured Query Language', ist die Standardsprache für
relationale Datenbanken. Sie wurde als Zugriffssprache für das von dem
Mathematiker E.F. Codd in den siebziger Jahren aufgestellte Relationenmodell
entwickelt. SQL stellt die praktische Umsetzung der relationalen Algebra dar.
SQL ist eine Sprache zur
•
•
•
•
•
•
•
strukturierten Abfrage
Aktualisierung
Datendefinition
Datenprüfung
Datenüberprüfung
Sicherung der Konsistenz
Pflege des Datenkatalogs
SQL ist eine nichtprozedurale und mengenorientierte Sprache.
SQL gehört zu den Programmiersprachen der 4. Generation. In diesen Sprachen
wird nur noch ausgedrückt, wie ein Ergebnis aussehen soll, und nicht, wie bei
Programmiersprachen der 3. Generation, wie der Rechner zu diesem Ergebnis
kommt.
SQL ist die am meisten verbreitete Sprache für relationale Datenbanken. Dies liegt
unter anderem auch daran, daß die ursprünglich von IBM entwickelte Sprache 1987
durch ANSI standardisiert wurde.
Die Meilensteine der Standardisierung sind:
1987
1989
1992
geplant
SQL wird erstmals durch ANSI standardisiert
erweiterter SQL Standard (Embedded SQL, Integrität)
SQL2
SQL3 Standard (Objektorientierung)
Die meisten Hersteller relationaler Datenbanken haben die Sprache SQL in ihrem
Produkt um einige Anweisungen erweitert, da Standards üblicherweise der
Entwicklung etwas hinterherlaufen. Deshalb sind in den marktüblichen
Datenbanksystemen weitere, hier nicht aufgeführte, Anweisungen enthalten.
Wesentliche Eigenschaften von SQL sind
• SQL entspricht in der Grundstruktur und den verwendeten Schlüsselwörtern der
englischen Sprache und ist auf sehr wenige Befehle beschränkt und somit leicht
zu erlernen.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
40
• SQL ist Teil des Datenbank Management Systems (DBMS) und die einzige
Schnittstelle um, mit der Datenbank auf konzeptioneller oder externer Ebene zu
kommunizieren.
• SQL ist sowohl eine einfache Abfragesprache als auch ein Entwicklungswerkzeug
für Anwendungsprogrammierer. Sämtliche Anwender, die mit der Datenbank
arbeiten,
sei
es
der
Datenbankadministrator,
oben
genannter
Anwendungsentwickler oder der einfache Benutzer, der lediglich Abfragen startet,
arbeiten mit demselben Werkzeug.
• Sämtliche relationale Datenbanken, die SQL unterstützen, benutzen dieselbe
standardisierte Sprache. Die Sprache muß somit vom Benutzer nur einmal erlernt
werden. Er kann sie auf jedem beliebigen System, vom PC bis hin zum
Großrechner, gleich einsetzen.
• SQL ist damit in der Lage, heterogene Rechnersysteme zu verbinden und schafft
somit die Möglichkeit zur Realisierung verteilter Datenbanken.
3.2 Objekte einer Relationalen Datenbank
Innerhalb einer relationalen Datenbank sind Objekte definiert, auf denen
Operationen ausgeführt werden können.
Einige wichtige Objekte werden im folgenden kurz erläutert.
• Tabellen
Tabellen (Relationen) sind zweidimensionale Basisstrukturen, um Daten von
Benutzern zu speichern. Tabellen sind definiert durch Spalten (Attribute,
Eigenschaften) und Zeilen (Datensätze, Tupel). Kreuzungspunkte von Spalten
und Zeilen nennt man Felder.
Um Zeilen eindeutig identifizieren zu können, werden Spalten angelegt, die die
Eindeutigkeit gewährleisten. Sie werden Primärschlüssel genannt.
• Views
Views sind logische Sichten auf physische Basistabellen. Ein View ist im
Gegensatz zu einer Basistabelle eine virtuelle Tabelle, durch die es möglich wird,
Daten nach bestimmten Kriterien sichtbar zu machen bzw. unsichtbar zu halten.
• Indizes
Ein Index ist eine Struktur, die optional für eine Tabelle angelegt werden kann.
Durch einen Index kann die Suche nach Datensätzen beschleunigt werden.
3.3 Etwas relationale Algebra
Die relationale Algebra beschäftigt sich mit dem datenmanipulierenden Teil des
relationalen Modells. Hier werden Operatoren definiert, die auf Relationen (Tabellen)
angewandt werden und als Ergebnis neue Relationen schaffen. SQL orientiert sich
sehr stark an der relationalen Algebra.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
41
Nachfolgend werden die wichtigsten Begriffe der relationalen Algebra kurz
vorgestellt. Auf die Angabe der exakten mathematischen Definitionen wird dabei
verzichtet, da sie vornehmlich Datenbanktheoretiker zufriedenstellen.
3.3.1 Restriction
REST (Relation, Bedingung)
Sie extrahiert aufgrund der Bedingung ein oder mehrere Tupel aus der Relation
(Zeilenauswahl).
r1
Rest (r1, attr2 = ‘A’)
attr1
1
2
3
attr2
A
B
C
attr1
1
attr2
A
3.3.2 Projection
PROJ (Relation, Attr1, Attr2,....)
Sie extrahiert Attribute (Spalten, Eigenschaften) aus der Relation.
r1
attr1
1
2
3
Proj (r1,<attr2>)
attr2
A
B
C
attr2
A
B
C
3.3.3 Product
PRODUCT (Relation1, Relation2)
bildet das kartesische Produkt aus den beiden Relationen. (Jedes Tupel in Relation1
wird mit jedem Tupel in Relation2 kombiniert.)
r1
r2
A
B
C
x
y
Product (r1,r2)
A
A
B
B
C
C
x
y
x
y
x
y
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
42
3.3.4 Union
UNION (Relation1, Relation2)
bildet die Vereinigungsmenge zweier Relationen. Dabei müssen die Attribute
selbstverständlich kompatibel sein.
r1
r2
A
B
C
D
E
Union (r1,r2)
A
B
C
D
E
3.3.5 Intersection
INTERSECTION (Relation1, Relation2)
bildet die Schnittmenge, also die gemeinsamen Tupel zweier Relationen.
r1
r2
A
B
C
A
D
Intersection
(r1,r2)
A
3.3.6 Difference
DIFFERENCE (Relation, Relation2)
bildet die Differenzmenge zweier Relationen, also Vereinigungsmenge abzüglich
Schnittmenge.
r1
r2
A
B
C
A
D
Difference (r1,r2)
B
C
D
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
43
3.3.7 Join
JOIN (Rel1, Rel2, Attr1 = Attr2)
verbindet zwei Relationen ähnlich dem Operator PRODUCT. Hier werden jedoch nur
die Tupel abgebildet, in der zwei bestimmte Attributwerte in einer gewissen
Beziehung zueinander stehen.
Es gibt mehrere Arten des Joins.
• Inner-Join
• Theta-Join (Θ-Join):
zusammen-
Beim Theta-Join erfolgt eine Zuordnung
hängender Tupel aus verschiedenen Relationen
durch einen Vergleich je eines Attributs pro
beteiligter Relation. Das Θ steht dabei als Platzhalter für den möglichen Operator, der aus der
Menge der Vergleichsoperatoren (=,<,>,>=,<=, <>)
stammen muß.
r1
a4)
a1
A
B
C
r2
a2
w
x
y
a3
C
D
a4
y
z
a1
A
A
B
B
C
C
a2
w
w
x
x
y
y
a3
C
D
C
D
C
D
Theta-Join (r1,r2,a2 =
a4
y
z
y
z
y
z
a1
C
a2
y
a3
y
a4
C
• Equi-Join:
Ein Equi-Join ist eine Untermenge des Theta-Joins
und läßt nur das Gleichheitszeichen als Operator
zu.
• Natural-Join:
Ein Natural-Join ist ein Equi-Join, der die
gleichen Attributwerte (der Vergleichsoperation) nur
einmal beinhaltet.
r1
a1
A
B
C
Product(r1,r2)
r2
a2
w
x
y
a3
C
D
a4
y
z
a1
A
A
B
B
C
C
Product(r1,r2) Natural-Join (r1,r2,a2 = a4)
a2
a3
a4
a1
a2
a3
w
C
y
C
y
C
w
D
z
x
C
y
x
D
z
y
C
y
y
D
z
• Semi-Join:
Bei einem Semi-Join werden nur die Tupel
aus der
ersten Relation des Joins
ausgegeben.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
44
r1
r2
a1
A
B
C
a2
w
x
y
a3
C
D
a4
y
z
a1
A
A
B
B
C
C
• Multiple-Join:
r1
a2
w
x
y
a3
C
D
r3
a4
y
z
a5
A
B
C
• Restricted-Join:
r1
a1
A
B
C
D
E
a3
C
D
a4
y
z
• Non-Equi-Join:
r1
a1
A
B
C
D
E
a3
C
D
Restricted-Join (r1,r2,a2 = a4 AND a2 = ’z’)
a1
a2
a3
E
z
D
Ein Non-Equi-Join bedeutet, daß in der JoinBedingung bzw. in der Restriktion ein beliebiger
Operator vorkommen darf.
r2
a2
v
w
x
y
z
Multiple-Join (r1,r2, r3, a2 = a4 = a6)
a6
a1
a2
a3
a5
x
C
y
C
B
y
z
Bei einem Restricted-Join werden zusätzlich zur
Join-Bedingung noch weitere Bedingungen
(Restriktionen) über eine AND-Verknüpfung
angegeben.
r2
a2
v
w
x
y
z
Semi-Join (r1,r2,a2 = a4)
a4
a1
a2
y
C
y
z
y
z
y
z
Ein Join, an dem mehr als zwei Relationen beteiligt
sind, nennt man Multiple-Join.
r2
a1
A
B
C
Product(r1,r2)
a2
a3
w
C
w
D
x
C
x
D
y
C
y
D
a4
y
z
• Auto-Join (Self-Join):
Non-Equi-Join (r1,r2,a2 = a4 AND a2 <= ’z’)
a1
a2
a3
D
y
C
E
z
D
verknüpft eine Relation mit sich selbst.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
45
r1
a1
A
B
C
D
E
a2
w
x
y
z
y
Auto-Join (r1, row1.a2 = row2.a2)
a1
a2
a1
C
y
E
• Outer-Join
• zeigt neben den Tupeln, bei denen der Attributwert der ersten Relation in
der zweiten Relation vorkommt, auch die Tupel einer Relation an, bei
denen dies nicht der Fall ist. D.h. eine Relation kann vollständig angezeigt
werden.
r1
a1
A
B
C
r2
a2
w
x
y
a3
C
D
a4
y
z
Outer-Join (r1,a2 = a4,r2)
a1
a2
a3
A
w
B
x
C
y
C
3.4 Sprachbereiche
SQL arbeitet mit Datenbankobjekten und Operatoren auf diesen Objekten. Weiterhin
verwaltet SQL Privilegien, die festlegen wer welche Operation auf welche Objekte
ausführen darf.
Demzufolge kann man die Sprache SQL in 3 Kategorien einteilen.
DDL =
DML =
DCL =
Data Definition Language
Data Manipulation Language
Data Control Language
zur Definition von Objekten
zur Manipulation von Objekten
zur Transaktionsverwaltung und zur
Kontrolle von Privilegien
3.5 DML
Zu den Data Manipulation Anweisungen gehören die Befehle
•
•
•
•
SELECT
INSERT
UPDATE
DELETE
Abfragen von Daten
Einfügen von Datensätzen
Ändern von Daten
Löschen von Datensätzen
3.5.1 Abfragen (Queries) mit der SELECT Anweisung
Der SELECT Befehl ist der mächtigste Befehl der Sprache SQL. Er dient dazu
bestimmte Datensätze und Spalten aus einer Tabelle oder View zu lesen. Das
Ergebnis einer Abfrage ist wiederum eine Tabelle.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
46
Die SELECT Anweisung besteht im Minimalfall aus der SELECT und FROM Klausel.
SQL>
SELECT <spalte1>, <spalte2>,... FROM <tabelle>
3.5.1.1 SELECT Klausel
In der SELECT Klausel werden die Spalten definiert, die in der Ergebnisrelation
erscheinen sollen.
Wird direkt hinter SELECT das Wort 'DISTINCT' angegeben, so wir die Ausgabe von
gleichen Tupeln unterdrückt. Die Gleichheit bezieht sich dabei auf die Spalten, die in
der SELECT Klausel angegeben sind.
Das Wort 'ALL' (Default) wertet alle Zeilen aus, also auch Duplikate.
Die Selektionsliste kann folgende Elemente enthalten:
*
selektiert alle Spalten der Tabelle
Spaltennamen
Namen von Spalten einer Tabelle. Die Eindeutigkeit muß
gewährleistet sein.
Funktionen
Ergebnisse von arithmetischen Operationen über mehrere
Spalten eines Tupels, wie z.B. Berechnung von Jahresgehältern
aus Monatsgehältern sowie arithmetische Funktionen oder
Stringfunktionen.
Gruppenfunktionen
führen Berechnungen über eine Spalte (nicht wie oben über eine
Zeile) aus.
Konstanten
Konstante numerische oder alphanumerische Werte.
Die Elemente können miteinander kombiniert werden, wenn sie durch Kommata
getrennt werden.
3.5.1.2 Funktionen
Funktionen werden in Ausdrücken verwendet, die Spaltenwerte enthalten. Also in
der
SELECT Klausel
WHERE Klausel
ORDER BY Klausel
Funktionen bestehen immer aus dem Funktionsnamen und einem oder mehreren
Argumenten.
Man unterteilt die Funktionen in folgende Bereiche
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
47
•
•
•
•
•
Arithmetische Funktionen
Zeichenkettenfunktionen
Datumsfunktionen
Konvertierungsfunktionen
Sonstiges
(SIN(),LOG(),MOD(),SIGN(),ABS(),...)
(SUBSTR(), RTRIM(), TRANSLATE(),...)
(LAST_DAY(),NEXT_DAY(),ADD_MONTH(),...)
(TO_CHAR(), TO_DATE(),REPLACE(),...)
(NVL(), GREATEST(), LEAST(),...)
Die Bedeutung und genaue Syntax der Funktionen kann aus Handbüchern oder
Syntaxdiagrammen entnommen werden. Erklärungen an dieser Stelle würden den
Rahmen der Einführung sprengen.
3.5.1.3 FROM Klausel
Die FROM Klausel enthält die Bezeichnung der Tabelle oder View aus der Daten
selektiert werden sollen.
Eine Tabelle ist immer einem Besitzer zugeordnet. In SQL werden Tabellen mit
gleichem Namen aber unterschiedlichen Besitzern durch voranstellen des
Besitzernamens vor den Tabellennamen unterschieden.
In den Anweisungen können Namen von Tabellen oder Views, um die Handhabung
zu erleichtern durch meist kürzere Aliasnamen ersetzt werden. Dies geschieht
einfach durch anhängen des Aliases an den Namen in der FROM Klausel.
Beispiel einer minimalen SELECT Anweisung.
SQL>
SELECT Name, Abteilung, Monatsgehalt, 12*Monatsgehalt
FROM USER1.Mitarbeiter
Diese Anweisung extrahiert aus der Tabelle 'Mitarbeiter', deren Eigentümer USER1
ist, die Spalten 'Name', 'Abteilung', 'Monatsgehalt' und fügt der Ergebnistabelle eine
Spalte mit dem Jahresgehalt hinzu.
3.5.1.4 WHERE Klausel - Zeilenfilter
Nachdem durch die SELECT Klausel eine Selektion bezüglich der Spalten
ermöglicht wurde, werden mittels der WHERE Klausel bestimmte Zeilen extrahiert.
Die WHERE Klausel enthält eine Bedingung, der die Zeilen entsprechen müssen,
wenn sie mit in die Ergebnistabelle eingehen sollen.
Eine Bedingung ist folgendermaßen aufgebaut
Ausdruck
Vergleichsoperator Ausdruck
Ein Ausdruck ist ein Spaltenname, eine Konstante oder das Ergebnis einer
untergeordneten, verschachtelten Abfrage.
• Eine untergeordnete Abfrage kann in der WHERE Klausel folgendermaßen
formuliert sein.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
48
...WHERE Name = (SELECT Name FROM mitarbeiter WHERE
Personalnummer=123)
• Arithmetische Vergleichsoperatoren
Arithmetische
Vergleichsoperatoren
sind
die
üblichen
Programmiersprachen bekannten Operatoren (=, <>, <=, >=, <, >).
aus
anderen
• Alphanumerische Vergleichsoperatoren
Der Zeichenkettenoperator LIKE ermöglicht das Erstellen von Suchschablonen.
Der Vergleichswert muß dabei nicht vollständig angegeben werden. So können
beispielsweise Zeilen selektiert werden, bei denen ein Attribut nicht vollständig
bekannt ist.
• Das Platzhaltersymbol '%' repräsentiert kein, ein oder beliebig viele Zeichen.
• '_' repräsentiert genau ein Zeichen.
Beispiele:
SQL>
SELECT Name, Abteilung FROM Mitarbeiter
WHERE Name= 'Maier'
selektiert alle Zeilen, in denen die Spalte Name der Wert 'Maier' beinhaltet.
...WHERE Name LIKE 'Ma%' selektiert alle Zeilen mit Namen, die mit 'Ma'
anfangen
...WHERE Name LIKE '%ai%' selektiert alle Zeilen mit Namen, die 'ai' beinhalten,
egal an welcher Stelle.
...WHERE Name LIKE '_aier' selektiert alle Zeilen mit Namen, die 5 Zeichen lang
sind und mit 'aier' enden.
• Logische Vergleichsoperatoren
• Durch BETWEEN <Wert1> AND <WERT2> werden alle Zeilen erfaßt, die
zwischen zwei Grenzwerten liegen.
...WHERE Name BETWEEN 'Holzmann' AND 'Maier'
selektiert alle Mitarbeiter deren Namen im Alphabet zwischen den angegebenen
liegt.
• Der Operator IN erlaubt es ein Attribut mit einer Menge von Werten zu
vergleichen.
...WHERE Name IN ('Maier', 'Müller,...)
• Zeilen, die NULL Felder enthalten können mit der Bedingung
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
49
...WHERE Name IS NULL
selektiert werden.
• Mit dem NOT Operator können Bedingungsergebnisse invertiert werden.
...WHERE Name NOT IN ('Maier', 'Müller')
• Mehrere Bedingungen können formuliert und mit logischen Operatoren AND und
OR verknüpft werden.
...WHERE Name = ' Maier' AND Abteilung = 'Vertrieb'
Dadurch werden alle Mitarbeiter namens Maier in der Abteilung 'Vertrieb'
gefunden.
3.5.1.5 Gruppenfunktionen, GROUP BY Klausel
In der SELECT Klausel können, wie oben erwähnt, Guppenfunktion auftauchen, die
ihr Ergebnis aus mehreren Zeilen berechnen. Diese Gruppenfunktionen liefern als
Ergebnis einen Wert zurück.
Gruppenfunktionen beziehen sich stets auf die Ergebnistabelle und nicht auf die
Haupttabelle und immer auf die angegebene Spalte. Enthält diese Spalte NULL
Werte, können unter Umständen unerwartete Ergebnisse entstehen, da NULL Werte
von den Gruppenfunktionen unterschiedlich gehandhabt werden.
Im Standard SQL sind folgende 5 Gruppenfunktionen definiert.
•
•
•
•
•
COUNT
AVG
SUM
MIN
MAX
Anzahl ausgewählter Zeilen
Durchschnitt der ausgewählten Werte
Summe der ausgewählten Werte
kleinster Wert
größter Wert
Die Verwendung von Gruppenfunktionen ist solange unkritisch, solange
ausschließlich Gruppenfunktionen in der SELECT Klausel verwendet werden.
Kommen in der SELECT Klausel normale Spaltennamen vor, muß die GROUP BY
Klausel verwendet werden.
GROUP BY führt eine Gruppenbildung durch. Eine Teilmenge von Zeilen mit
gleichen Attributwerten in einer Spalte wird dabei zusammengefaßt.
Gruppenfunktionen beziehen sich somit auf die durch GROUP BY gebildeten
Gruppen.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
50
In der GROUP BY Klausel müssen alle Spaltennamen der SELECT Klausel
aufgeführt sein.
Beispiele:
SQL>
SELECT SUM (Gehalt) FROM Mitarbeiter;
gibt als Ergebnis die Gesamtsumme aller Gehälter aus.
SQL>
SELECT Abteilung, SUM(Gehalt)
FROM Mitarbeiter
GROUP BY Abteilung;
gibt als Ergebnis für jede Abteilung die Summe der Gehälter aus.
SQL>
SELECT Abteilung, Funktion, SUM (Gehalt)
FROM Mitarbeiter
GROUP BY Abteilung, Funktion;
gibt als Ergebnis die Summe der Gehälter für jede Berufsgruppe (Funktion) in jeder
Abteilung aus.
3.5.1.6 HAVING Klausel - Gruppenfilter
Die HAVING Klausel dient, wie die WHERE Klausel, der Auswahl bestimmter Zeilen,
deshalb entspricht die Syntax der HAVING Bedingung der, der WHERE Bedingung.
Im Gegensatz zur WHERE Klausel bezieht sich die HAVING Klausel jedoch nicht nur
auf einzelne Zeilen , sondern auf die Gruppen, die durch die GROUP BY Klausel
erzeugt worden sind.
SQL>
SELECT Abteilung, SUM (Gehalt)
FROM Mitarbeiter
WHERE Abteilung <> 'Vertrieb'
GROUP BY Abteilung
HAVING SUM(Gehalt) > 1000000;
Diese Anweisung extrahiert alle Abteilungen außer der Abteilung 'Vertrieb', bei
denen das Gesamtgehalt der Mitarbeiter über 1 000 000 liegt.
Die HAVING Klausel und die WHERE Klausel können durchaus in derselben
Anweisung verwendet werden. Dabei filtert die WHERE Klausel alle Zeilen aus, die
aufgrund eines Attributes nicht betrachtet werden sollen, während die HAVING
Klausel die Zeilen ausfiltert, die aufgrund des Ergebnisses einer Gruppenfunktion
ausscheiden. Die Entscheidung, ob eine Zeile zum Ergebnis gehört oder nicht kann
nicht anhand einzelner Zeilen getroffen werden.
3.5.1.7 ORDER BY Klausel
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
51
Die ORDER BY Klausel sorgt für die Sortierung der Zeilen in der Ergebnistabelle.
Die Liste der Argumente ist eine Teilmenge der SELECT Argumente.
SQL>
SELECT Name, Abteilung FROM Mitarbeiter
ORDER BY Name ASC;
Die Sortierung kann durch den Zusatz DESC absteigend erfolgen. Kein Zusatz oder
der Zusatz ASC verursacht eine aufsteigende Sortierung.
3.5.1.8 Subselects
Innerhalb einer WHERE oder HAVING Klausel können SELECT Anweisungen
verwendet werden, was zu einer Verschachtelung der SELECT Anweisung führt.
SQL>
SELECT Name
FROM Mitarbeiter
WHERE Abteilung = (SELECT Abteilung FROM Mitarbeiter WHERE
Name='Maier');
Diese Anweisung führt alle Mitarbeiter auf, die in derselben Abteilung arbeiten, wie
der Mitarbeiter mit dem Namen 'Maier'.
Die Subselect Anweisung ermittelt hier die Abteilungsbezeichnung die im
übergeordneten SELECT als Ausdruck in der WHERE Bedingung dient.
Die Unterabfrage kann einen Wert oder eine Wertemenge zurückliefern. Deshalb ist
die Verwendung eines arithmetischen Operators in der WHERE Klausel der
übergeordneten Abfrage nicht zu empfehlen. Besser ist hier der logische Operator
IN.
Eine Unterabfrage muß stets rechts vom Vergleichsoperator der übergeordneten
WHERE Klausel stehen.
• Der EXISTS Operator
wird in Verbindung in Verbindung mit SUBSELECTS verwendet, um auf das
Vorhandensein von Zeilen zu prüfen, die ein Vergleichskriterium erfüllen.
SQL>
SELECT Abteilung
FROM Mitarbeiter
WHERE EXISTS (SELECT * FROM Mitarbeiter WHERE
Name='Maier');
Hier werden alle Abteilungen extrahiert, die einen Mitarbeiter Namens 'Maier'
beschäftigen.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
52
3.5.1.9 Mengenoperatoren
Eine relationale Menge sind die Zeilen , die das Ergebnis einer SELECT Anweisung
darstellen.
Die leere Menge entspricht dabei 0 selektierten Zeilen.
Relationale Mengen können mittels der Mengenoperatoren
• UNION (ALL)
• INTERSECT
• MINUS
Vereinigungsmenge, mit der Option ALL werden auch Duplikate
ausgegeben.
Schnittmenge
Zeilen, die in der ersten, nicht aber in der zweiten Menge
vorkommen.
verknüpft werden.
SQL>
SELECT Name FROM Mitarbeiter WHERE Abteilung='Vertrieb'
UNION
SELECT Name FROM Mitarbeiter WHERE Abteilung='Einkauf';
Hier werden Namen der Mitarbeiter aus den Abteilungen 'Vertrieb' und 'Einkauf'
ausgegeben.
Die Abfragen müssen sich nicht auf dieselbe Tabelle beziehen. Die Attributwerte der
Ergebnistabellen müssen jedoch derselben Domäne entstammen.
3.5.1.10 JOIN - Verknüpfung von Tabellen
Die bisher verwendeten SELECT Anweisungen verwendeten immer nur eine
Tabelle. Da i.a. Daten in verschieden Tabellen gehalten werden, muß es eine
Möglichkeit geben, Abfragen über mehrere Tabellen zu formulieren.
Das Konstrukt, das dies ermöglicht, heißt JOIN. Er wurde in der relationalen Algebra
bereits vorgestellt.
In der Sprache SQL gibt es keine explizite Anweisung zur Bildung dieses Produktes.
Der JOIN wird dadurch realisiert, daß in der FROM Klausel einer SELECT
Anweisung mehrere Tabellen angegeben werden.
Die Art des Operators JOIN der relationalen Algebra wird durch die Angabe einer
Join Bedingung in der WHERE Klausel bestimmt.
Als Beispiel sollen zwei Tabellen dienen. Die Tabelle Mitarbeiter enthält die
Personalnummer, Namen und Abteilung der Angestellten, die Tabelle Projekt enthält
die Personalnummer und das dem Mitarbeiter zugehörige Projekt.
Mit einer SQL Abfrage soll nun ermittelt werden, wer in welchem Projekt arbeitet.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
53
SQL>
SELECT Mitarbeiter.Name, Mitarbeiter.Abteilung,
Projekt.ProjBezeichnung
FROM Mitarbeiter, Projekt
Diese Anweisung bildet das kartesische Produkt der beiden Tabellen. Dabei werden
zwangsläufig Zeilen entstehen, die die Realität falsch wiedergeben, weil jeder
Mitarbeiter plötzlich bei jedem Projekt mitarbeitet.
Durch die Join Bedingung muß die Teilmenge des kartesischen Produktes ermittelt
werden, die die Realität widerspiegelt.
In unserem Fall benötigen wir einen EQUI JOIN, da nur die Datensätze der
Produkttabelle von Interesse sind in denen die Personalnummer aus Mitarbeiter und
aus Projekt übereinstimmen. Die gesuchte Abfrage lautet somit.
SQL>
SELECT Mitarbeiter.Name, Mitarbeiter.Abteilung,
Projekt.ProjBezeichnung
FROM Mitarbeiter, Projekt
WHERE Mitarbeiter.Personalnummer = Projekt.Personalnummer
Da die Spalte Personalnummer in beiden Tabellen vorkommt, muß der Eindeutigkeit
wegen der Tabellenname mit angegeben werden.
Es ist möglich, mehr als zwei Tabellen mit einem Join zu verknüpfen. Werden n
Tabellen miteinander verknüpft, müssen n-1 Join Bedingungen formuliert werden.
Die Join Bedingung kann selbverständlich mit weiteren Bedingungen kombiniert
werden.
In der SELECT Anweisung kann, um die oft langen Tabellennamen abzukürzen und
um beim SELF JOIN die Tabellen zu unterscheiden, für die Tabellennamen ein
Aliasname vergeben werden.
Beispiel:
SQL>
SELECT M.Name, M.Abteilung, P.ProjBezeichnung
FROM Mitarbeiter M, Projekt P
WHERE M.Personalnummer = P.Personalnummer
Der OUTER JOIN wird realisiert, indem in der Join Bedingung die Tabelle, die
vollständig in das Ergebnis eingehen soll, mit einem '(+)' ergänzt wird.
SQL>
SELECT M.Name, P.Projekt
FROM Mitarbeiter M, Projekt P
WHERE M.Personalnummer (+) = P.Personalnummer
Mit dieser Anweisung erhalten wir alle Mitarbeiter, also auch solche, die in keinem
Projekt tätig sind. Wenn in der Tabelle P unter M.Personalnummer kein Datensatz
gefunden wird, so wird ein zusätzlicher Datensatz erstellt, der im Schlüsselfeld den
Wert des geforderten Schlüssels enthält. Alle anderen Felder sind NULL.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
54
3.5.2 Umsetzung der SELECT Anweisung in relationale Operatoren
Die Anweisungen der Sprache SQL werden innerhalb des Datenbanksystems auf
Operationen der relationalen Algebra umgesetzt.
Zunächst werden die Anweisungen semantisch und syntaktisch geprüft. Läßt man
Optimierungsprozesse außer acht, läuft die Umsetzung einer SELECT Anweisung
folgendermaßen ab.
1. Bei der Verwendung von JOINS wird zuerst das kartesische Produkt über die
Tabellen gebildet.
2. Aufgrund der WHERE Klausel werden Zeilen selektiert.
3. Das Ergebnis wird entsprechend der SELECT Klausel auf die gewünschten
Spalten projiziert.
Nach jedem Schritt entsteht eine temporäre Ergebnistabelle.
Beispiel: In der Tabelle Mitarbeiter seien 500 Mitarbeiter, davon 50 im 'Vertrieb'. In
der Tabelle seien 30 Projekte. Jeder Mitarbeiter ist in genau einem Projekt
tätig.
SQL>
SELECT Name, Projekt
FROM Mitarbeiter M, Projekt P
WHERE M.Abteilung = 'Vertrieb'
AND M.Personalnummer = P.Personalnummer
Mitarbeiter (500)
Projekt (30)
Product (15.000)
Rest (
Mitarbeiter.Abteilung = 'Vertrieb'
M.Personalnummer = P.Personalnummer) (50)
Proj (Name,Projekt) (50)
Durch die sequentielle Abarbeitung der Operationen entstehen mitunter enorm große
temporäre Tabellen.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
55
Aufgabe sog. Optimierer ist, die Reihenfolge und Art der Umsetzung so anzupassen,
daß die Anzahl der Zeilen in den temporären Tabellen möglichst klein gehalten wird,
wobei die Geschwindigkeit der Abarbeitung mit berücksichtigt werden muß.
Bei kleinen Tabellen wird anstatt des Operators Product auch der Join Operator
verwendet. Bei diesem Operator wird bereits die Join Bedingung berücksichtigt. Es
fallen so sehr viel weniger Zeilen an. Da der Join Operator allerdings durch
Programmschleifen realisiert ist, wird er bei steigender Zeilenanzahl sehr langsam.
3.5.3 INSERT
Die INSERT Anweisung fügt in eine bestehende Tabelle Datensätze ein. Sie benötigt
dazu die Angabe des Tabellennamens, der Spaltenname und der Werte, die
eingetragen werden sollen.
Je nachdem ob eine Zeile oder mehrere Zeilen angefügt werden sollen, wird die
VALUES Klausel verwendet.
SQL>
INSERT INTO <tabelle> (<spalte>,...) VALUES (<Wert1>,<Wert2>,...);
schreibt eine neue Zeile in die <tabelle> mit den Werten, die nach VALUES
angegeben sind.
SQL>
INSERT INTO <tabelle> (<spalte>,....) SELECT...;
schreibt alle die Zeilen in <tabelle>, die durch die SELECT Anweisung erzeugt
werden.
Die Anzahl und die Datentypen in der Spaltenauswahl der SELECT Anweisung muß
denen der INSERT Anweisung entsprechen.
3.5.4 UPDATE
UPDATE ändert ein oder mehrere bereits bestehende Felder einer Tabelle.
SQL>
UPDATE <tabelle> SET <spalte> = <Wert> WHERE <Bedingung>
Es können mehrere Spalten mit Werten, durch Komma getrennt, angegeben
werden.
Die WHERE Klausel selektiert die Datensätze, die aktualisiert werden. Dort kann
auch eine Unterabfrage (Subquery) eingebaut werden.
Durch die Verwendung einer Subquery in der SET Klausel
SQL>
UPDATE<tabelle> SET (<spalte>,...) = (SELECT...) WHERE
<Bedingung>
sind komplexe UPDATE Anweisungen möglich.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
56
3.5.5 DELETE
DELETE löscht eine oder mehrere Zeilen aus einer Tabelle.
SQL>
DELETE FROM <tabelle> WHERE <bedingung>;
Mit der WHERE Klausel werden die zu löschenden Zeilen spezifiziert. Fehlt die
WHERE Klausel oder ist sie nicht eindeutig, werden mehrere Zeilen oder der ganze
Inhalt der Tabelle gelöscht.
3.6 DDL
Die Data Definition Language (DCL) dient auf konzeptioneller Datenbankebene der
Erstellung , Änderung und dem Löschen von Datenbankobjekten. Demzufolge
gehören dazu die Anweisungen
• CREATE
• ALTER
• DROP
3.6.1 CREATE
3.6.1.1 CREATE TABLE
CREATE TABLE definiert eine neue Tabelle. Die Parameter enthalten den
Tabellennamen und die Spalten.
Für jede Spalte muß der Datentyp und das Attribut NULL/NOT NULL angegeben
werden (Default ist NULL).
SQL>
CREATE TABLE <tabelle>
(<spalte1> <Datentyp> [NULL/NOT NULL],
<spalte2> <Datentyp> [NULL/NOT NULL],.....)
Eine weitere Variante des CREATE Befehls ist die Verwendung der AS SELECT
Klausel, die es ermöglicht, die Tabellenstruktur und die Daten einer bestehenden
Tabelle zu übernehmen.
SQL>
CREATE TABLE <tabelle1> (<spalte1>, <spalte2>,...)
AS SELECT <spalte1>,<spalte2>,... FROM <tabelle2>
Die Anzahl der Spalten in der SELECT Anweisung muß gleich sein wie die Anzahl in
der CREATE TABLE Anweisung.
3.6.1.2 CREATE VIEW
Die Anweisung
SQL>
CREATE VIEW <view_name> AS SELECT ....
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
57
generiert eine virtuelle Tabelle , die genau die Spalten und Zeilen enthält, die durch
die SELECT Anweisung erzeugt wurden.
3.6.1.3 CREATE INDEX
CREATE INDEX erzeugt einen Index auf eine bestimmte Spalte einer Tabelle (wird
beim Anlegen eines Primary Key automatisch erzeugt).
3.6.2 ALTER
Die ALTER Anweisung ändert bestehende Datenbankobjekte.
Angewandt auf Tabellen können Spalten angefügt oder modifiziert werden, d.h. die
Eigenschaften wie Datentyp oder die Zulassung von Nullwerten.
• Eine NOT NULL Spalte kann jedoch nur bei leeren Tabellen angefügt werden.
• Verkleinern der Spaltenbreite ist nur bei leeren Spalten möglich.
SQL>
ALTER TABLE <tabelle>
ADD (<Spalte1 <Datentyp> NULL/NOT NULL, <Spalte2> ...);
SQL>
ALTER TABLE <tabelle>
MODIFY (<Spalte1 <Datentyp> NULL/NOT NULL, <Spalte2> ...);
ALTER VIEW und ALTER INDEX spielen eine eher unwichtige Rolle, so daß auf ihre
Behandlung hier verzichtet werden kann. Nähere Informationen sind aus den
Syntaxdiagrammen ersichtlich.
3.6.3 DROP
DROP löscht ein Datenbankobjekt. Dazu muß der Objekttyp und der Objektname
angegeben werden.
SQL>
DROP TABLE <tabelle>
DROP INDEX <index>
DROP VIEW <view>
3.7 DCL
3.7.1 Transaktionsverarbeitung
Eine Transaktion ist eine logisch zusammengehörende Arbeitseinheit, die eine oder
mehrere SQL Anweisungen enthält.
Die Auswirkungen aller der Transaktion angehörigen Anweisungen müssen aus
Sicherheitsgründen gemeinsam festgeschrieben oder zurückgenommen werden.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
58
Ohne Mechanismen der Transaktionsverarbeitung können sich die Daten während
einer Transaktion in einem inkonsistenten Zustand befinden. Ein Beispiel dafür ist
die Buchung von Geldbeträgen von einem Konto zu einem anderen. Je nachdem,
wie lange die Transaktion dauert (evtl. Warten auf Eingaben inbegriffen), steigt die
Wahrscheinlichkeit, daß Probleme auftreten, die das Weiterarbeiten verhindern.
Somit wäre beispielsweise ein Betrag vom Konto abgebucht, nicht jedoch auf das
andere Konto übertragen.
Die Änderungen während einer Transaktion werden solange, bis die Eingabe
bestätigt wird, in sog. Rollbacksegmenten gespeichert und erst bei Bestätigung in die
Tabellen übertragen.
Ändern des Kontostandes auf
Konto 1
Zeitachse
Ändern des Kontostandes auf
Konto 2
Festschreiben oder
Zurücknehmen der Transaktion
SQL bietet für die Transaktionsverarbeitung die Anweisungen COMMIT und
ROLLBACK.
COMMIT
dient dabei der Bestätigung und des Festschreibens der Änderungen
ROLLBACK nimmt alle noch nicht bestätigten Anweisungen zurück.
Um die Integrität der Daten bei einem Multi-User System zu gewährleisten, müssen
Tabellenzeilen oder vollständige Tabellen für die Dauer einer Transaktion für andere
Anwender gesperrt werden (bezüglich Schreibzugriffen, Lesezugriffe sind immer
möglich).
SQL hält dazu den LOCK Befehl bereit. Da allerdings alle gängigen
Datenbanksysteme die Sperrung automatisch bei Datenmanipulierenden
Anweisungen wie UPDATE, INSERT, DELETE veranlassen, wird der LOCK Befehl
nur selten verwendet.
3.7.2 Objektprivilegien
SQL stellt Anweisungen bereit, den Zugriff auf Daten und Strukturen zu verhindern,
um die Datensicherheit zu gewährleisten.
Datenbankanwendern werden Privilegien auf Datenbankobjekte verliehen oder
entzogen.
Man unterscheidet folgende Privilegien
• CREATE Erstellen eines Objekts
• ALTER Ändern eines Objekts
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
59
•
•
•
•
DELETE
INSERT
SELECT
UPDATE
Löschen eines Objekts
Einfügen neuer Zeilen in Tabellen oder Views
Selektion aus einer Tabelle oder View
Ändern von Daten in einer Tabelle oder View
3.7.2.1 GRANT
Mit der GRANT Anweisung werden Privilegien weitergereicht.
Beispiel:
SQL>
GRANT create Table TO <user>
3.7.2.2 REVOKE
Mit der REVOKE Anweisungen werden Privilegien entzogen
Beispiel:
SQL>
REVOKE create table FROM <user>
3.8 Erweiterungen durch weiterführende SQL Standards
3.8.1 Check-Option
Die CHECK-Option ermöglicht es einen Wertebereich für Tabellenspalten
anzugeben. Bei UPDATE oder INSERT Anweisungen wird der Eingabewert auf
Gültigkeit überprüft.
3.8.2 Default-Option
Für Attribute einer Tabelle können beim Anlegen Default Werte festgelegt werden.
Dies vermeidet NULL Werte.
3.8.3 Unique-Option
Die Eindeutigkeit bestimmter Attributswerte wird durch die UNIQUE-Option beim
anlegen einer Tabelle gewährleistet.
3.8.4 Foreign Key
Ein Attribut kann als Foreign Key deklariert werden. Dieser Foreign Key verweist auf
einen Primärschlüssel einer weiteren Relation.
Grundsätzlich gilt: Jeder Wert des Foreign Keys muß einem Wert des Primary Keys
entsprechen.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
60
Die Verletzung dieser Integritätsregel durch UPDATE, INSERT oder DELETE
Anweisungen kann mittels der Foreign Key Option verhindert werden.
Es besteht dadurch auch die Möglichkeit des kaskadierbaren Löschens.
Beispiel:
Tabelle Auftrag enthält als Foreign Key die Kundennummer.
Wird nun ein Kunde gelöscht, werden automatisch auch die Aufträge
dieses Kunden gelöscht. Ohne Verwendung von Foreign Key bliebe ein
Auftrag übrig, der keinem Kunden zugeordnet werden könnte oder aber
bei Wiederverwendung der Kundennummer einem falschen Kunden
zugeordnet wird.
3.8.5 Definition von Domänen
Seit dem SQL2 Standard ist die Definition eines Datenbankobjektes Domäne
möglich. Damit können Datentypen erzeugt werden, die eine stärkere Einschränkung
des Wertebereichs von Attributen ermöglichen.
3.8.6 Zustände von Tabellen
Tabellen können verschiedene Zustände annehmen. So ist es zum Beispiel möglich
Tabellen nur für die Dauer einer Session anzulegen.
3.8.7 Transaktionen (siehe auch Kapitel 3.7.1)
Die Zugriffsart bei Transaktionen kann manuell festgelegt werden. Read-only und
Read-write Transaktionen sind möglich. Dadurch wird bestimmt inwieweit
Datenänderungen für eine konkurrierende Transaktion sichtbar sind.
3.8.8 dynamisches SQL
Die Form von SQL Anweisungen kann zur Laufzeit der Anwendung geändert
werden. Der Anwender kann so interaktiv eingreifen.
3.8.9 Funktionen
Die Standardfunktionen COUNT, AVG, MAX, MIN, SUM können an jeder beliebigen
Stelle verwendet werden. Weitere Funktionen sind implementiert, die hauptsächlich
auf Stringvariablen bezogen sind. (Bsp.: Substring, Char_length,..)
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
61
4 Einführung in SQL*Plus
4.1 Begriffe zu SQL*Plus
SQL*Plus stellt eine einfache Möglichkeit dar um mit der Oracle Datenbank interaktiv
in Kontakt zu treten, Änderungen vorzunehmen, Eingaben und Abfragen zu tätigen.
Es handelt sich hierbei um einen Interpreter, d.h. die Übersetzung der Eingaben in
Maschinencode erfolgt anweisungs-orientiert. SQL*Plus stellt ein typisches
Prototyping-Werkzeug dar, mit dessen Hilfe interaktiv Eingaben getestet werden
können, die später in Programme eingebunden werden.
Möglichkeiten von SQL*Plus:
• Unterstützung von SQL Kommandos, wie z.B. create, select, insert
• Verarbeitung PL/SQL-Blöcken
• Informationen über den Aufbau von Tabellen
• Einlesen und Schreiben von SQL-Befehlen von bzw. in Dateien
4.2 SQL*Plus Befehle
Dieses Kapitel stellt lediglich die Befehle vor, die zur Durchführung der Übungen
gebraucht werden. Zur Vertiefung von SQL*PLUS wird auf das Oracle Handbuch
„SQL*PLUS User Guide and Reference“ verwiesen.
4.2.1 Eingabe von SQL*Plus Befehlen
Wie bereits schon erwähnt, handelt es sich bei SQL*Plus um einen Interpreter. Der
SQL*Plus Interpreter arbeitet anweisungsorientiert. Eine Anweisung wird durch ein
Semikolon abgeschlossen. Es ist möglich, eine Anweisung länger als 1 Zeile zu
machen, d.h. Folgezeichen einzugeben, bis das Statement in einer Folgezeile durch
einen Strichpunkt abgeschlossen wird.
Beispiel:
Eine Eingabe mit Folgezeilen kann folgendermaßen aussehen:
SQL>
SELECT name, vorname
1
from person
2
where name=‘Maier’ and
3
vorname like ‘A%’;
4.2.2 START
SQL*Plus Befehle können im Dialog eingegeben werden. Es ist aber auch möglich,
eine Stapeldatei von SQL*Plus Befehlen zu schreiben. Mit START können einzelne
oder auch mehrere SQL-Befehle aus einem File gelesen und gleich ausgeführt
werden. Dabei ist zu beachten, daß dieses File die Extension „.sql“ besitzen muß
und nur durch den Filenamen ohne explizite Angabe der Extension aufgerufen wird.
SQL>
START <Filename>
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
62
Falls mehrere SQL-Befehle in diesem File vorhanden sind und diese nacheinander
abgearbeitet werden sollen, so müssen sie durch einen „/“ voneinander getrennt
werden.
Beispiel:
Das File „test.sql“ mit folgendem Inhalt soll aufgeführt werden:
CREATE TABLE person(id int, nachname varchar(30), varchar(30))
/
CREATE TABLE Student(MatrNr int, id int, semester int)
/
Durch Eingabe des folgenden SQL-Befehls wird das File „test.sql“ ausgeführt:
SQL>
START test
4.2.3 DESCRIBE
Der Befehl DESCRIBE ermittelt den Aufbau einer angelegten Tabelle.
SQL>
DESCRIBE <Tablename>
Beispiel:
Das folgende Kommando gibt die Struktur der Tabelle aus.
SQL>
DESCRIBE person
Name
---------------------------------------------------------ID
NAME
VORNAME
Null?
-------------
Type
---------------------------INT
VARCHAR(30)
VARCHAR(30)
4.2.4 Tabelle TABS
In der Tabelle TABS werden von Oracle allgemeine Informationen zu den
angelegten Tabellen abgelegt, wie z.B. der Name aller angelegten Tabellen eines
Benutzers. Auf diese Tabelle kann wie auch auf andere Tabellen über den SELECTBefehl zugegriffen werden. Die Tabelle TABS ist nicht SQL*PLUS spezifisch,
sondern ist ebenso in anderen Oracle-Werkzeugen verfügbar.
Beispiel:
Mit folgendem Befehl werden alle angelegten Tabellen des Benutzers aufgeführt:
SQL>
SELECT table_name
1
FROM tabs;
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
63
5 PL/SQL - eine prozedurale Erweiterung von SQL für
Oracle
5.1 Allgemeines
PL/SQL (Procedural Language Structured Query Language) ist eine prozedurale
Erweiterung von SQL und basiert auf der Programmiersprache Ada. PL/SQL bietet
einerseits die prozeduralen Elemente von 3.Generation-Sprachen wie C oder
PASCAL
und
andererseits
die
nicht-prozeduralen
Möglichkeiten
der
Datenmanipulation von SQL. Dabei unterstützt PL/SQL alle Data Manipulation
Statements von SQL (DELETE, INSERT, SELECT, UPDATE), Cursor-Operationen
sowie Transaction Processing (COMMIT, ROLLBACK und SAVEPOINT).
Ferner kann eine beliebige Anzahl von SQL-Statements mit Statements für die
Programmierung von Schleifen (FOR ... LOOP , WHILE ... LOOP), mit Statements
zur Ablaufsteuerung (IF ... ELSIF ... ELSE ... END IF, EXIT und GOTO) und mit
Zuweisungs-Statements verknüpft werden. PL/SQL faßt mehrere SQL-Statements
zusammen und schickt diese als Einheit an die Datenbank. Diese Funktionalität
steigert die Performance und reduziert den Netzwerk-Verkehr in verteilen Systemen
drastisch.
PL/SQL ist kein eigenständiges Tool . Es ist einerseits direkt im Oracle Management
System (RDBMS) eingebaut und kann andererseits in Oracle-Precompilern,
SQL*Plus, SQL*Forms und anderen Oracle-Tools verwendet werden.
5.2 Aufbau eines PL/SQL Blockes
Ein PL/SQL Block besteht aus einem Deklarationsteil und einem Anweisungsteil.
Folgende Schlüsselwörter definieren den Aufbau eines Blockes:
• DECLARE
bezeichnet den Beginn des Deklarationsteiles und eines PL/SQL Blockes
• BEGIN
bezeichnet den Beginn des Anweisungsteils und den Beginn des PL/SQL
Blockes, wenn keine Deklarationen vorzunehmen sind.
• EXCEPTION
bezeichnet den Beginn des Fehlerbehandlungsteiles.
• END
bezeichnet das Ende des PL/SQL Blockes.
5.3 Ablaufsteuerung
Bedingte Verzweigung:
• IF-Statement:
Wenn die Bedingung erfüllt ist, werden die nachfolgenden Statements
ausgeführt.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
64
• ELSIF-Statement:
Wenn die neue Bedingung erfüllt ist werden die nachfolgenden Statements
ausgeführt.
• ELSE-Statement:
Sind die Bedingungen obiger IF- oder ELSIF-Statements nicht erfüllt, werden
die nachfolgenden Statements ausgeführt.
Unbedingte Verzweigung:
Mit Hilfe von GOTO kann der Kontrollfluß des PL/SQL-Programms gesteuert
werden.
Schleifen:
• FOR-Schleife
Beispiel:
FOR i IN 1..100 LOOP
Statement;
Statement;
...
END LOOP;
Die Schleife wird für jeden ganzzahligen Wert der Indexvariablen durchlaufen.
Eine Umkehr der Zählrichtung erfolgt ausschließlich mit dem Schlüsselwort
REVERSE. Der Werte-bereich des Zählers kann auch in Form von Variablen und
Ausdrücken angegeben werden.
Beispiel:
...
FOR i IN REVERSE 1..max_zeilen LOOP
Statement;
Statement;
...
END LOOP;
• WHILE Schleifen
Beispiel:
WHILE BEDINGUNG LOOP
Statement;
Statement;
...
END LOOP;
• EXIT-Statement
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
65
Mit dem EXIT-Statement kann eine Schleife verlassen werden. Sind die Schleifen
mit Namen versehen, so kann bei verschachtelten Schleifen nicht nur die aktuelle
Schleife, sondern auch die übergeordnete Schleife verlassen werden.
Beispiel:
...
<<Schleife_aussen>>
FOR l_v1 IN 1..10 LOOP
...
<<Schleife_innen>>
FOR l_v2 IN 1..100 LOOP
Statement;
...
IF ... THEN EXIT Schleife_aussen;
END IF;
END LOOP Schleife_innen;
END LOOP Schleife_aussen;
...
5.4 Das SELECT-Statement
Das SELECT-Statement hat eine zusätzliche INTO-Klausel, in der die PL/SQLVariablen mit den Daten aus der Datenbank gefüllt werden. In der WHERE-Klausel
kann auf PL/SQL-Variable Bezug genommen werden. Ansonsten gibt es keine
Unterschiede zum SELECT-Statement von SQL.
Beispiel:
DECLARE
v_empno NUMBER(4) := 7700;
v_sal
emp.sal%TYPE;
/*Mit dem Schluesselwort %TYPE wird v_sal mit dem Typ der Spalte sal deklariert.*/
BEGIN
SELECT sal
INTO v_sal
FROM emp
WHERE empno = v_empno;
.....
END;
5.5 INSERT-, UPDATE- und DELETE-Statement
Diese Statements manipulieren die Datenbank. Mit dem INSERT-Statement kann
man einen Datensatz einfügen. Das UPDATE-Statement verändert und das
DELETE-Statement löscht Datensätze, die mit der WHERE-Klausel beschrieben
werden.
Beispiel:
DECLARE
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
66
v_empno NUMBER(4) := 7700;
v_sal
emp.sal%TYPE;
v_deptno dept.deptno%TYPE;
BEGIN
SELECT sal
INTO v_sal
FROM emp
WHERE empno = v_empno;v_sal := v_sal * 1.1;
DELETE FROM emp
WHERE mgr = v_empno;
v_deptno :=0;
UPDATE emp SET deptno = v_deptno
WHERE empno = v_empno;
INSERT INTO dept
VALUES(v_deptno,’Temp’,’Temp;);
....
END;
5.6 Elementare Datentypen
PL/SQL der Version 2 hat folgende Datentypen:
• BINARY_INTEGER
Wertebereich : -2-31-1 ... 231 -1
Mit folgenden Subtypen:
- NATURAL 0.. 231 -1
- POSITIVE 1.. 231 -1
• NUMBER[(x[,y])]
x : Gesamtziffernzahl im Bereich 1.0E-129..9.99E125.
y : Rundung auf Stellen hinter dem Komma.
Die folgenden Subtypen haben denselben Wertebereich wie der Basistyp
NUMBER:
- DECIMAL / DEC
- FLOAT
- INTEGER / INT
- REAL
- SMALLINT
- DOUBLE PRECISION
- NUMERIC
• CHAR[(n)]
Zur Speicherung von CHAR-Daten fester Länge. n : Länge bis maximal 32767
Bytes, ohne Angaben nimmt n den Wert 1 an. Die maximale Länge dieses
Datentyps weicht von der maximalen Länge des gleichnamigen Datentyps für
Tabellenspalten ab, dort beträgt er 255 Bytes.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
67
Subtypen sind:
- CHARACTER
- STRING
• ROWID
Interne Spalte einer Datenbanktabelle, die angibt, in welchem Block(B), in welcher
Zeile(R) und in welchem Datenbank-File(F) ein Datensatz gespeichert ist. Diese
ROWID kann selektiert und in einer Variablen gleichen Typs gespeichert werden.
• VARCHAR(n)
VARCHAR2(n)
Zur Speicherung von CHAR-Daten variabler Länge. Die Längenangabe n ist
verbindlich.
(n = max 32767).
• LONG
Entspricht dem Datentyp VARCHAR2. Max Länge 32767 Bytes.
Die maximale Länge dieses Datentyps weicht von der maximalen Länge des
gleichnamigen Datentyps für Tabellenspalten ab.
• RAW / LONG RAW
Dient zur Speicherung binärer Daten. Maximale Länge: 32767 Bytes bzw. 32760
Bytes (LONG RAW).
5.7 Strukturierte Datentypen
1. PL/SQL-TABLE
PL/SQL-TABLES sind ähnlich aufgebaut wie Datenbanktabellen. Die Anzahl der
Zeilen ist nicht beschränkt. Eine PL/SQL-TABLE besteht aus zwei Spalten, einem
Primärschlüssel vom Datentyp BINARY_INTEGER und einer zweiten Spalte mit
einem beliebigen Datentyp.
Die Deklaration erfolgt in zwei Schritten:
1. Schritt: Deklaration des Tabellentyps.
Beispiel:
DECLARE
TYPE s_name_tabelletyp IS TABLE OF CHAR(10) INDEX BY
BINARY_INTEGER;
Mit tabellenname.spaltenname%TYPE kann der Datentyp einer Tabellenspalte
direkt vergeben werden:
DECLARE
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
68
TYPE s_name_tabelletyp IS TABLE OF emp.ename%TYPE INDEX BY
BINARY_INTEGER;
2. Schritt: Deklaration einer Variablen dieses Typs.
Beispiel:
s_name_tab
s_name_tabelletyp
Wertzuweisung:
Beispiel einer Zuweisung mittels Schleife:
...
FOR i IN 1.. 14 LOOP
SELECT ename INTO s_name_tab(i)
FROM emp;
END LOOP;
...
2. Records
Wie die Tables müssen auch die Records in zwei Schritten deklariert werden.
1. Schritt: Deklaration des Records.
2. Schritt: Definition von Records dieses Typs.
Beispiel:
DECLARE --Deklaration
TYPE emprectyp IS RECORD
(empno NUMBER(4),
ename emp.ename%TYPE,
job emp.job%TYPE);
.... --Definition
emp_rec emprectyp;
5.8 Cursor-Management
Cursor sind Datenstrukturen im Arbeitsspeicher, sie verwalten den Zustand der
Abarbeitung einer SQL-Anweisung. Bei INSERT-, UPDATE- und DELETEStatements werden im Cursor lediglich Statusinformationen über den Erfolg einer
Operation abgelegt.
Bei SELECT-Statements kann ohne einen Cursor nur genau ein Datensatz in die
Ergebnisvariablen eingelesen werden. Ein SELECT-Statement, welches keinen oder
mehrere Datensätze findet, führt ohne Verwendung eines Cursors zu einem Fehler.
Der Cursor beinhaltet bei SELECT-Statements außer den Statusinformationen über
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
69
Erfolg oder Mißerfolg auch Information über den gerade aktuellen Datensatz. Es wird
zwischen zwei verschiedenen Cursorn unterschieden. Dem expliziten und dem
impliziten Cursor. Der explizite Cursor muß selbst verwaltet werden der implizite
Cursor wird ohne Zutun des Anwendungsentwicklers verwaltet.
1. Expliziter Cursor
Für die Handhabung eines expliziten Cursors werden 4 Schritte benötigt:
• DECLARE
Im DECLARE-Statement wird dem Cursor ein Name zugewiesen und das
SELECT-Statement angegeben. Input Parameter können deklariert werden, die
für das SELECT-Statement dienen.
Beispiel:
DECLARE
v_empno emp.empno%TYPE := 2312;
CURSOR curs1(p_emp_no NUMBER) IS
SELECT empno, ename,sal
FROM emp;
WHERE empno = p_empno;
satz curs1%ROWTYPE; --Deklaration der Strukturvariable.
• OPEN
Es wird das SELECT-Statement ausgeführt und der Cursor auf den ersten
Datensatz positioniert.
Beispiel:
OPEN curs1(v_empno);
• FETCH
Mit dem FETCH-Statement werden die einzelnen Datensätze sequentiell
eingelesen.
Beispiel:
LOOP
FETCH curs1 INTO satz;
EXIT WHEN curs1%NOTFOUND OR curs1%NOTFOUND IS NULL;
END LOOP;
• CLOSE
Das CLOSE-Statement schließt den Cursor, es können keine weiteren
Datensätze gelesen werden, ohne den Cursor erneut zu öffnen.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
70
Beispiel:
CLOSE curs1;
2. Impliziter Cursor
Bei impliziten Cursorn übernimmt die Datenbank die Verwaltung des Cursors. Bei
der Verwendung von impliziten Cursorn muß mittels FOR-Schleife der komplette
Cursor abgearbeitet werden.
Die Cursor-FOR Schleife:
Da der Programmierer nicht auf den impliziten Cursor mit DECLARE, OPEN, FETCH
und CLOSE einwirken kann, wird mit einer FOR-Schleife gearbeitet, bei welcher der
Cursor automatisch geöffnet, alle Datensätze eingelesen und der Cursor wieder
geschlossen wird. Die Daten der Sätze werden als „Felder“ einer Struktur
ausgegeben.
Beispiel:
DECLARE
v_empno emp.empno%TYPE := 2312;
CURSOR curs1(p_emp_no NUMBER) IS
SELECT empno, ename,sal
FROM emp;
WHERE empno = p_empno;
BEGIN
FOR emp_rec IN curs1(v_deptno)LOOP
...
END LOOP;
Die For <record> IN Schleife:
Die For <record> IN Schleife arbeitet wie die Cursor_FOR Schleife, ist aber einfacher
zu codieren.
Beispiel:
DECLARE
... v_empno
emp.empno%TYPE := 2312;
BEGIN
FOR emp_rec IN
(SELECT empno, ename,sal
FROM emp;
WHERE empno = n_empno)
LOOP
...
END LOOP;
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
71
Attribute:
Sie erlauben es, die Statusinformationen eines Cursors abzufragen.
Es gibt folgende Attribute:
• %NOTFOUND
%NOTFOUND wird TRUE gesetzt, wenn ein INSERT-, UPDATE- und DELETEStatement auf keinen Datensatz wirkt oder ein SELECT-Statement keinen Satz in
der Datenbank findet. Bei SELECT-Statements wird der Exception Handler
NO_DATA_FOUND angestoßen, %NOTFOUND kann also nur im Exception
Handler geprüft werden. Anstatt %NOTFOUND zu prüfen kann, auch der
Exception Handler NO_DATA_FOUND direkt verwendet werden.
• %FOUND
Ist das Gegenteil von %NOTFOUND und wird TRUE gesetzt, wenn ein DMLStatement auf einen oder mehrere Datensätze wirkt oder ein SELECT-Statement
einen Satz in der Datenbank findet.
• %ROWCOUNT
Liefert die Anzahl betroffener oder gefundener Zeilen eines Statements. Wird bei
SELECT INTO mehr als eine Zeile geliefert. Wird die Predefined Exception
TOO_MANY_ROWS angestoßen und %ROWCOUNT auf 1 gesetzt.
• %ISOPEN
Ist TRUE, solange der Cursor geöffnet ist. Bei impliziten Cursorn ist %ISOPEN
immer FALSE, da diese automatisch geschlossen werden.
Aufruf:
Der Aufruf der Attribute erfolgt mit folgenden Schlüsselwörtern:
Beim expliziten Cursor mit dem Cursornamen und dem Attribut:
cursor_name%FOUND;
Beim impliziten Cursor mit SQL und dem gewünschten Attribut:
SQL%FOUND;
5.9 Prozeduren und Funktionen
Unterprogramme wie Prozeduren und Funktionen können einerseits in PL/SQLBlöcken und andererseits als eigenständige Datenbankobjekte (stored procedures)
deklariert werden. Wie bei anderen Programmiersprachen muß die Prozedur oder
Funktion zuerst deklariert werden und kann danach aufgerufen werden.
Deklaration einer Funktion:
Beispiel:
FUNCTION gehalt_lesen(l_empno IN BINARY_INTEGER ,
sal OUT BINARY_INTEGER) RETURN BINARY_INTEGER
IS
BEGIN
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
72
....
END gehalt_lesen;
Syntax:
FUNCTION func_name[(argument) [, argument, ...])]
RETURN datentyp
[Lokale Deklarationen]
IS|AS
BEGIN
Anweisungen
[EXCEPTION]
Fehlerbehandlung
END [func_name];
Deklaration einer Prozedur:
PROCEDURE proc_name[(argument) [, argument, ...])]
IS|AS
[Lokale Deklarationen]
BEGIN
Anweisungen
[EXCEPTION]
Fehlerbehandlung
END [proc_name];
Aufruf:
Beispiel für Funktion: l_retval :=func_name(empno,sal);
Beispiel für Prozedur: proc_name(empno,sal);
Parametermodi:
Die Parameter können als IN , OUT oder IN OUT übergeben werden.
IN-Parameter, zur Übergabe von initialisierten Werten an ein Unterprogramm.
Innerhalb der Subroutine verhaltet sich ein IN-Parameter wie eine Konstante,der kein
Wert zugewiesen werden kann. IN-Parameter können bei der Deklaration mit
Defaultwerten vorbesetzt werden.
Beispiel: ... , v_empno IN BINARY_INTEGER DEFAULT 22, ...
OUT-Parameter liefern die Rückgabewerte an das aufrufende Objekt. Innerhalb der
Subroutine wird ein OUT-Parameter wie eine nicht initialisierte Variable behandelt.
Vor dem Verlassen sollte dem Parameter ein Wert zugewiesen werden.
IN OUT-Parameter übergeben initialisierte Werte an die Subroutine, verhalten sich
innerhalb wie initialisierte Variable. Einem IN OUT-Parameter kann zum einen ein
Wert zugewiesen werden und zum anderen kann mit dem Inhalt eines IN OUTParameters eine andere Variable initialisiert werden.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
73
5.10 Fehlerbehandlung
Die Fehlerbehandlung (Exception Handling) zur Laufzeit in PL/SQL erfolgt mit Hilfe
von Exceptions.
Beispiel:
DECLARE
status CHAR(240);
...
BEGIN
...
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
WHEN OTHERS
status := SQLERRM(SQLCODE);
RETURN SQLCODE;
END;
Wichtigen Fehlern ist standardmäßig ein vordefinierter Name (Predefined Exception)
zugeordnet. Den übrigen Fehlern kann mit Hilfe der EXCEPTION_INIT ein Name
zugewiesen werden. Ansonsten kann der Fehler mit Fehlernummer SQLCODE und
Fehlertext SQLERRM ausgegeben werden.
Beispiele für Predefined Exceptions sind:
NO_DATA_FOUND, NOT_LOGGED_ON, TOO_MANY_ROWS, OTHERS,.....
In der Predefined Exception OTHERS werden alle Fehlernummern behandelt, die
nicht in einer anderen Predefined Exception behandelt werden.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
74
6 Embedded SQL mit PRO*C
6.1 Allgemeines
Die Grundidee von „Embedded SQL“ besagt, daß es möglich sein muß, alle SQLAnweisungen, die interaktiv gültig sind, unverändert in ein Programm zu integrieren.
Zu beachten ist aber, daß der Umkehrschluß nicht möglich ist, da Embedded SQL
gegenüber dem interaktiven SQL einige Spracherweiterungen aufweist, die zwar
innerhalb eines Programms, aber nicht interaktiv zulässig sind. Dazu wird ein
Programm geschrieben, das bei PRO*C die Endung .pc erhält. Dieses Programm
wird dann von einem PRE-Compiler so übersetzt, daß daraus gültiger C-Code
entsteht, der dann ganz normal vom Compiler übersetzt werden kann.
Deklarative und ausführbare Anweisungen
Man unterscheidet zwei Arten von Anweisungen, ausführbare und deklarative:
• Ausführbare Anweisungen werden vom Precompiler in Funktionen und
Unterprogramme umgewandelt, die dann verschiedene Werte zurückliefern
können, z.B. über die Variable SQLCA. Zu dieser Gruppe zählen alle vom
interaktiven SQL her bekannten Anweisungen (Query, DML, DDL, DCL)
• Deklarative Anweisungen werden nicht durch den Precompiler in Funktionen
umgewandelt und liefern folglich auch keinen Return-Code. Diese Anweisungen
dienen u. a. dazu, dem System die benutzten Variablen bekannt zu machen.
Beispiele für deklarative Anweisungen:
BEGIN DECLARE SECTION
END DECLARE SECTION
INCLUDE ...
WHENEVER ...
DECLARE CURSOR ...
DECLARE DATABASE ...
DECLARE STATEMENT ...
DECLARE TABLE ...
Alle Anweisungen, seien sie ausführbar oder deklarativ, müssen, um für den
Precompiler erkennbar zu sein, mit dem Präfix EXEC SQL begonnen werden.
Abschließend muß ein Semikolon folgen.
6.2 Teile eines Pro*C-Programms
• Der „Application Prologue“ besteht fast ausschließlich aus deklarativen
Anweisungen. Er dient der Vorbereitung und Bereitstellung all dessen, was die im
zweiten Teil, dem sogenannten „Application Body“ folgenden Anweisungen
benötigen. Es müssen bestimmte Elemente enthalten sein, wie weiter unten noch
beschrieben wird.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
75
• Der „Application body“ enthält ausführbare, in der Regel auch zusätzliche
deklarative Embedded SQL-Anweisungen. Die ausführbaren Anweisungen
beschränken sich nicht auf Abfragen und Anweisungen zur Datenmanipulation.
Diese treten in der Praxis zwar am häufigsten auf, aber Anweisungen zur
Datendefinition und Datenintegritätskontrolle sind ebenso zulässig und in vielen
Fällen sinnvoll.
Zwischen diesen Teilen können jederzeit ‘normale’ C-Anweisungen und Funktionen
stehen.
6.3 Vorgehensweise bei der Erstellung eines ESQL-Programms
Der Ablauf beim Erstellen eines Programms, das in einer Sprache der 3.Generation
geschrieben wird, mag sich hinsichtlich der notwendigen Kommandos von Compiler
zu Compiler unterscheiden, schließt jedoch immer die folgenden vier Schritte ein:
1.
2.
3.
4.
Erstellen der Quelldatei (Editor).
Übersetzen des Programms (Compiler).
Linken des Programms (Linker).
Starten des Programms.
Diese vier Schritte bleiben auch notwendig, wenn SQL-Anweisungen in das
Programm eingebettet werden. Um die Embedded SQL-Anweisungen in C-Code
umzuwandeln, muß vor dem Compilerlauf noch der PRE-Compiler-Lauf
eingeschoben werden (Pro*C). Der PRE-Compiler darf nicht verwechselt werden mit
dem Präprozessor des C-Compilers!
6.3.1 Grundstruktur eines ESQL-Programms
/* Deklaration der in ESQL-verwendeten Variablen */
/* (Declare Section) */
function( )
/* function() = Funktion, die Datenbankzugriff benötigt */
/* (kann z.B.: void main() sein) */
{
/* Herstellen einer Datenbankverbindung */
/* Anweisung für Fehlerbehandlung */
/* SQL - Anweisung */
.
.
.
/* Beenden der Datenbankverbindung */
}
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
76
6.4 Der Application Prologue
Dem Application Prologue kommt etwa die gleiche Funktion zu, die in einem
Programm ohne Embedded-SQL durch den Deklarationsteil übernommen wird: die
Vorbereitung und Bereitstellung all dessen was im Anweisungsteil benötigt wird.
Im allgemeinen sind dies die folgenden drei Teile:
1. DECLARE-SECTION: Hier werden die Programmvariablen vereinbart.
2. Bereitstellen eines Datenbereichs für die Kommunikation des Datenbankkernels
mit dem Programm (SQLCA).
3. Das Anmelden bei der Datenbank.
Hier wird lediglich die Syntax dargestellt. Praktische Implementationsbeispiele sehen
Sie dann im Übungsteil.
6.4.1 DECLARE - SECTION
In der DECLARE-SECTION werden alle Programmvariablen bekanntgemacht, die
später in SQL-Anweisungen verwendet werden. Sie beginnt immer mit der
Anweisung:
EXEC SQL BEGIN DECLARE SECTION;
und wird abgeschlossen durch
EXEC SQL END DECLARE SECTION;
Zwischen diesen beiden Anweisungen dürfen nur Vereinbarungen auftreten.
Bei der Wahl der Variablennamen sind einerseits die Einschränkungen durch
ORACLE (reservierte Worte), andererseits diejenigen durch den Compiler zu
berücksichtigen. Namen von Variablen und Spalten aus Datenbanktabellen dürfen
gleich sein, da durch die Syntax eindeutig bezeichnet wird, ob die Spalte oder
Variable gemeint ist.
Alle Variablen, die in einer SQL-Anweisung benutzt werden sollen, müssen hier
vereinbart werden. Trifft der PRE-Compiler auf eine nicht vereinbarte Variable, wird
die Fehlermeldung
Undeclared host variable a at line b in file c
ausgegeben. Ob Variablen, die nicht in SQL- ,sondern ausschließlich in
Programmiersprachenanweisungen verwendet werden, innerhalb oder außerhalb der
DECLARE SECTION vereinbart werden, bleibt dem Programmierer überlassen.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
77
Die innerhalb der DECLARE SECTION vereinbarten Variablen bezeichnet man auch
als HOSTVARIABLEN. Sie dürfen sowohl in SQL-Anweisungen, als auch in der CApplication verwendet werden.
Pro Programmeinheit darf es nur eine DECLARE SECTION geben.
6.4.2 Zulässige Datentypen:
Es sind alle vordefinierten C-Datentypen erlaubt und werden vom PREcompiler
entsprechend unterstützt (selbst Strukturen sind zulässig, sofern die Strukturen
innerhalb der Declare Section instanziiert werden). Desweiteren sind einfache
Pointer, also solche, bei denen nur eine einmalige Dereferenzierung notwendig ist,
erlaubt. Es ist hingegen verboten Namen zu verwenden, die durch typdef’s bekannt
gemacht werden, da der PREcompiler diese nicht kennen kann, da sie erst vom
Compiler ausgewertet werden.
EXEC SQL BEGIN DECLARE SECTION;
.
.
.
.
.
EXEC SQL END DECLARE SECTION;
Datentyp
char
char[n]
int
float
double
VARCHAR[n]
char[9]
Beschreibung
einzelnes Zeichen
Zeichenkette n Zeichen
integer (number)
Gleitkomma-Zahl, einfach (number)
Gleitkomma-Zahl, doppelt (number)
Zeichenkette mit variabler Länge
(Oracle-Datentyp)
date
6.4.3 Strings variabler Länge
Strings variabler Länge spielen bei der Kommunikation mit ORACLE, sowie bei der
Programmierung von User Exits eine große Rolle. Da C standardmäßig keinen
geeigneten Datentyp für Strings mit variabler Länge hat, macht dies ORACLE,
welches durch den PREcompiler den zusätzlichen Datentyp VARCHAR zur
Verfügung stellt.
Dabei handelt es sich um eine Verbindung eines Strings (fester Länge) und einem
ganzzahligen Wert, der die Länge des Strings angibt. Dies wird abgebildet durch
einen struct, mit den Komponenten char arr[zahl] und short len. Die Angabe zahl
gibt an, wieviele Zeichen der String maximal haben darf. In der Komponente len wird
angegeben, wieviele Zeichen der String tatsächlich enthält.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
78
Wie VARCHAR-Variablen bearbeitet werden, ist abhängig vom Ort ihres Auftretens:
in SQL-Anweisungen werden sie als Einheit angesprochen, während in
Programmiersprachen-Anweisungen nur auf die Komponenten Bezug genommen
werden kann. Wird die Variable von ORACLE beschrieben und vom Programm
gelesen, so trägt ORACLE den String und die Länge ein. Wird sie dagegen vom
Programm beschrieben , so ist der Programmierer auch dafür verantwortlich, die
Längenangabe einzutragen.
• Vereinbarung von VARCHAR innerhalb des PRO*C-Programms:
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR username[20];
VARCHAR password[20];
EXEC SQL END DECLARE SECTION;
• Der Precompiler nimmt folgende Umsetzung vor:
struct {
unsigned short len;
unsigned char arr[20];
} username;
struct {
unsigned short len;
unsigned char arr[20];
} password;
Der Precompiler akzeptiert auch die Deklaration der Form:
VARCHAR username[20],password[20];
6.5 Die Vereinbarung der SQLCA zur Fehlerbehandlung
Für die Kommunikation zwischen dem Datenbanksystem und der Applikation, die
nicht den Datentransfer, sondern die Informationen über Erfolg und Mißerfolg einer
Aktion enthalten, ist ein spezieller Datenbereich, die sogenannte SQL
Communications Area (SQLCA), vorgesehen. Diese muß durch ein geeignetes
Statement eingebunden werden.
Dies geschieht durch das Statement:
EXEC SQL INCLUDE sqlca;
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
79
6.6 Die CONNECT - Anweisung
Bevor auf Daten zugegriffen werden kann, muß das Programm eine Verbindung zur
Datenbank aufbauen. Dies geschieht durch die CONNECT - Anweisung in der Form
EXEC SQL CONNECT :username IDENTIFIED BY :password
oder
EXEC SQL CONNECT :userid
wobei im zweiten Fall die userid als username/password angegeben werden muß.
Username, password und userid müssen Variablen sein (keine Konstanten).
Das Einlesen der Variablen geschieht erst im Programmteil (Application Body),
wodurch sich der Application Prologue in diesen hinein erstreckt. Zudem ist die
CONNECT-Anweisung selbst keine deklarative sondern eine ausführbare
Anweisung. Allerdings beginnt mit Ihr keine Transaktion.
Eine Disconnect - Anweisung existiert nicht. Die Auflösung der Verbindung erfolgt
durch COMMIT/ROLLBACK WORK RELEASE.
Dazu wird nach dem Ausführen einer Transaktion der Commit-Befehl ausgeführt
oder beim Fehlerfall ein Rollback durchgeführt:
EXEC SQL ROLLBACK WORK RELEASE
EXEC SQL COMMIT WORK RELEASE
abschließen
-- Transaction zurückrollen
-- Transaction
Beispiel für CONNECT:
/* HOSTVARIABLEN
*/
.
.
.
VARCHAR password[20];
VARCHAR username[20];
.
.
.
strcpy(username.arr,“SCOTT“);
username.len=strlen(username.arr);
strcpy(password.arr,“TIGER“);
password.len=strlen(password.arr);
EXEC SQL CONNECT :username IDINTIFIED BY :password;
.
.
.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
80
6.7 Der Application Body
Der Application Body enthält die Anweisungen zur Abfrage, Manipulation, Definition
und Kontrolle von Daten der Datenbank. Sämtliche SQL-Anweisungen die interaktiv
möglich sind, sind auch hier erlaubt. Allerdings sind zur Bearbeitung von Queries
einige Syntax-Erweiterungen gegenüber dem interaktiven SQL notwendig.
In der DECLARE SECTION vereinbarte Variablen können in den SQL-Anweisungen
und in der C-Application verwendet werden. In SQL-Anweisungen müssen sie, um
sie von Spaltennamen zu unterscheiden, durch einen vorangestellten Doppelpunkt
gekennzeichnet werden.
EXEC SQL BEGINN DECLARE SECTION
REAL INCR
EXEC SQL END DECLARE SECTION
...
/********************************************************/
/*
Die folgende SQL-Anweisung addiert bei allen
*/
/*
Angestellten, deren Gehalt (SAL) kleiner als
*/
/*
2000 ist, den Wert INCR hinzu.
*/
/********************************************************/
EXEC SQL UPDATE EMP
SET SAL = SAL + :INCR -- SAL
WHERE SAL < 2000
-- :INCR
kennzeichnet eine Spalte
kennzeichnet eine Variable
6.7.1 Kennzeichnung von Kommentaren
Seit der Version 1.3 des PRO*C PRE-Compilers ist es möglich, SQL-Anweisungen
durch Kommentare zu unterbrechen. Ein solcher dem ANSI-Standard
entsprechender Kommentar wird mit einem doppelten Bindestrich eingeleitet und
erstreckt sich bis zum Zeilenende. Sie können an der oben stehenden SQLAnweisung sehen, wie dies aussieht. Es ist desweiteren auch möglich, die in C
übliche Schreibweise für Kommentare zu verwenden. C++ Kommentare („//“) dürfen
jedoch nicht verwendet werden.
6.8 Einbetten von Queries
Die Ergebnisse einer interaktiv eingegebenen SELECT-Anweisung werden direkt auf
den Bildschirm ausgegeben. Ebenso werden die Ergebnisse einer SELECTAnweisung, die in ein Programm eingebettet wurde, an das Programm übergeben
und müssen dort übernommen werden. Welche Schritte der Programmierer
unternehmen muß, um dieser Anforderung zu genügen, hängt davon ab, ob das
Ergebnis lediglich aus einem Datensatz besteht (Single Row Select) oder aus
mehreren (Multiple Row Select).
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
81
6.8.1 Single Row Select
Ist es sichergestellt, daß die Abfrage nur einen Datensatz als Ergebnis liefern kann,
so genügt es, die SELECT-Anweisung durch die gegenüber dem interaktiven SQL
zusätzliche INTO-Klausel zu erweitern. Diese enthält die Variablen, die die
Ergebniswerte aufnehmen sollen:
EXEC SQL SELECT NAME
INTO :nachname
FROM Student
WHERE MATR_NR = :mat_nr;
Die Spalten der SELECT-Klausel und die Variablen in der INTO-Klausel müssen
hinsichtlich der Anzahl und der Reihenfolge übereinstimmen. Es ist nicht möglich,
mehr oder weniger Variablen anzugeben. Alle Variablen müssen in der DECLARE
SECTION bekannt gemacht worden sein.
6.8.2 Multiple Row Select
Besteht das Ergebnis einer Query aus mehreren Datensätzen, so genügt der im
vorigen Abschnitt dargestellte Mechanismus nicht mehr, da es mehr Ergebniswerte
gibt, als Variablen, die sie aufnehmen. Um dieses Problem zu umgehen muß mit
einem sogenannten CURSOR gearbeitet werden. Das heißt, daß die Ergebnisdaten
quasi in einer temporären Tabelle abgelegt werden, wo sie dann sequentiell
ausgelesen werden können. Der CURSOR ist sozusagen ein Zeiger, der jeweils auf
die nächste Reihe der Ergebnisse zeigt. Mit einer FETCH-Anweisung wird der Cursor
auf die nächsten Datensatz bewegt.
Die Bearbeitung einer solchen SELECT-Anweisung geschieht in folgenden vier
Schritten:
Schritt 1:
CURSOR DEKLARIEREN
EXEC SQL DECLARE abfrage CURSOR FOR
SELECT NAME,VORNAME FROM Student
WHERE GESCHLECHT = :sex
Der Cursor muß auf diese Weise deklariert worden sein, bevor er in irgendeiner
anderen Anweisung auftreten darf. Ist er einmal deklariert, so ist er von da an im
gesamten Programm bekannt. Jedoch müssen alle Anweisungen, die sich auf ihn
beziehen, sich in der gleichen Precompiler-Einheit befinden wie die DECLAREAnweisung, da der Cursor sonst dem Pre-Compiler nicht bekannt ist.
Schritt 2:
ÖFFNEN DES CURSORS
EXEC SQL OPEN abfrage
Bei dieser Anweisung wird das SELECT-Statement ausgeführt und der Cursor auf
den ersten Datensatz positioniert. Allerdings wird hier noch kein Ergebnis an das
Programm übergeben. Nachdem der Cursor einmal geöffnet wurde, bewirkt eine
Veränderung im Datenbestand der Datenbank keine Veränderung der
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
82
Ergebnismenge des Cursors mehr. Um dies zu erreichen, müßte der Cursor wieder
neu geöffnet werden.
Schritt 3:
FETCH (einlesen) der Datensätze
EXEC SQL FETCH abfrage INTO :nachname,:vorname;
Die erste FETCH-Anweisung liefert den ersten Datensatz, jede weitere den jeweils
nächsten. Explizites Positionieren des Cursors innerhalb der Ergebnismenge ist nicht
nötig - aber auch nicht möglich. Das bedeutet, daß man die einzelnen Datensätze
nur sequentiell vorwärts lesen kann, aber nicht rückwärts. Um einen bereits
gelesenen Datensatz noch einmal zu lesen, muß der Cursor neu geöffnet und von
Anfang an gelesen werden. Beachten Sie aber bitte, daß beim erneuten öffnen eines
Cursors sich in der Ergebnismenge eine zwischenzeitliche Veränderung des
Datenbestandes der Datenbank niederschlägt.
Da die Anzahl der ermittelten Datensätze in der Regel nicht bekannt ist, muß bei der
Bearbeitung eine Schleife mit Abbruchbedingung verwendet werden. Diese
Bedingung ist dadurch gegeben, daß ORACLE, wenn kein oder kein weiterer
Datensatz vorhanden ist, in eine Komponente der SQLCA (SQLCA.SQLCODE) den
WERT +1403 einträgt. Dies kann in zweifacher Weise ausgenutzt werden:
1. SQLCODE wird in der Schleife direkt abgefragt
do {
EXEC SQL FETCH abfrage INTO :nachname,:vorname;
if (sqlca.sqlcode == 1403) break;
} while (TRUE);
2. Verwenden der WHENEVER-Anweisung
exec sql whenever not found goto weiter;
do {
EXEC SQL FETCH abfrage INTO :nachname,:vorname;
} while (TRUE);
weiter: /* weiter Anweisungen */
Schritt 4:
Schließen des Cursors
EXEC SQL CLOSE abfrage
Nach dem Schließen des Cursors können keine weiteren Datensätze mehr
übertragen werden.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
83
6.8.3 Die INSERT-Anweisung
Die INSERT-Anweisung hat im embedded SQL folgende Syntax
EXEC SQL INSERT INTO (Fach_Nr,Fach_Bez)
VALUES (:f_nr,:f_name);
wobei Fach_nr und Fach_Bez die Tabellenspalten bezeichnen, und f_nr und f_name
die Variablen, die den einzufügenden Datensatz beinhalten. Ansonsten kennen Sie
den Befehl ja vom interaktiven SQL.
6.8.4 Die DELETE-Anweisung
Äquivalent sieht auch die DELETE-Anweisung aus
EXEC SQL DELETE FROM Student
WHERE MATR_NR = :mat_nr;
wobei Student die Tabelle bezeichnet und mat_nr eine Variable für die WHEREKLAUSEL. Wie im interaktiven Betrieb auch, können auch hier über die WHEREKlausel mehrere Datensätze selektiert und gelöscht werden.
6.8.5 Die UPDATE-Anweisung
Auch hier ist die Anweisung im Prinzip gleich aufgebaut
EXEC SQL UPDATE Student
SET Fachbereich = :fb, Sem = :sem
WHERE MATR_NR = :mat_nr
Selbstverständlich ist es auch hier möglich, dieselben Aktionen durchzuführen wie im
interaktiven Betrieb.
6.9 Arbeiten mit VARCHAR
Wie VARCHAR-Variablen bearbeitet werden, ist abhängig vom Ort ihres Auftretens:
in SQL-Anweisungen werden sie als Einheit angesprochen während in
Programmiersprachen-Anweisungen nur auf die Komponenten Bezug genommen
werden kann. Ein kleines Beispiel dazu ist der gesamte Kontext zum CONNECT mit
der Datenbank.
exec sql begin declare section;
VARCHAR username[20],password[20];
exec sql end declare section;
printf („\n\nORACLE-Username: „);
gets (username.arr);
username.len = strlen (username.arr);
printf („\n\nOracle-Password: „);
gets (password.arr);
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
84
password.len = strlen (password.arr);
exec sql connect :username identified by :password;
Wird die Variable von ORACLE beschrieben und vom Programm gelesen, so trägt
ORACLE den String und die Länge ein. Vor einer Weiterverarbeitung mit CStringverarbeitungsfunktionen muß das Stringende durch Anhängen des
Stringterminators (ASCII 0) gekennzeichnet werden. Achtung: Länge der VARCHARVariable um 1 größer dimensionieren als die maximale Stringlänge! Wird die
Variable vom Programm beschrieben , so ist der Programmierer auch dafür
verantwortlich, die Längenangabe einzutragen.
6.10 Strukturen
Seit der Version Pro*C 2.0 ist es möglich, Strukturen innerhalb einer Abfrage direkt
mit ihrem Namen anzusprechen.
Beispiel 1:
struct
{
int
emp_no;
float salary;
char
emp_name [10];
char emp_job [10];
} emp_record;
...
...
EXEC
SQL SELECT empno, sal, ename, job
FROM
emp
INTO
:emp_record
WHERE mgr IS NULL;
Beispiel 2:
Es ist auch möglich, auf einzelne Elemente einer Struktur zuzugreifen.
struct
{
int
emp_no;
float salary;
char
emp_name [10];
char emp_job [10];
} emp_record;
...
...
EXEC
SQL SELECT ename, sal
FROM
emp
INTO
:emp_record.emp_name, :emp_record.salary
WHERE mgr IS NULL;
Nach wie vor ist es aber nicht möglich, auf ein array of structures oder nested host
structures zuzugreifen
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
85
6.11 Indikatorvariablen
Indikatorvariablen sind eine Neuerung, die seit der Oracle Version 7.0 gibt. Diese
Variablen ermöglichen es, bei einer Abfrage NULL-Values und gekürzte
Zeichenketten zu erkennen. Des weiteren können sie zum expliziten einfügen von
NULL-Values bei INSERT- oder UPDATE-Anweisungen genutzt werden.
Indikatorvariablen sind vom Typ „short“.
Die Syntax lautet:
<programmvariable>:<indikatorvariable>
oder
<programmvariable> INDICATOR :<indikatorvariable>
Auch hier kann mit Strukturen gearbeitet werden.
Beispiel:
Abfrage mit Indikatorvariablen
struct
{
int
emp_no;
float salary;
char
emp_name [10];
char emp_job [10];
} emp_record;
struct
{
short i_emp_no;
short i_salary;
short i_emp_name;
short i_emp_job;
} i_emp_record;
...
...
EXEC
FROM
INTO
WHERE
SQL SELECT ename, sal
emp
:emp_record.emp_name:i_emp_record.i_emp_name,
:emp_record.salary:i_emp_record.i_salary
mgr IS NULL;
Nach der Ausführung der Anweisung kann der Inhalt der Indikatorvariablen abgefragt
werden:
=0:
Fehlerfreie Übertragung.
-1:
Inhalt der Programmvariablen undefiniert -> Übertragung brachte einen NULLValue.
>0:
Ein String wurde bei der Übertragung gekürzt. Der Wert der Indikatorvariablen
gibt die ursprüngliche Länge an.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
86
Anmerkung: Die Übertragung von NULL-Values in Programmvariable, denen keine
Indikatorvariable zugeordnet sind, erzeugt einen Laufzeitfehler (ORA-1405).
Beispiel:
Explizites Schreiben eines NULL-Values
float salary;
short i_salary = -1;
...
...
i_salary = -1;
EXEC
SET
SQL UPDATE emp
sal = :salary:i_salary;
6.12 Der Outer Join
Der Outer Join dient zur zusätzlichen Abfrage all jener Zeilen, die keine direkte
Entsprechung in der anderen Tabelle haben. Zu der Tabelle, die für die Abfrage
spezifiziert wurde, wird ein zusätzlicher NULL-Datensatz hinzugefügt, bei welchem
das Schlüsselfeld mit dem in der Abfrage spezifiziertem Wert gefüllt wird.
Die Tabelle wird spezifiziert, indem das Zeichen (+) zu dem bzw. den Spaltennamen
hinzugefügt wird. Die mit (+) bezeichnete Tabelle wird um eine zusätzliche NULLZeile (all null row) ergänzt. Diese Nullzeile wird dann in der Ergebnistabelle zu allen
bisherigen ungültigen (irrelevanten) Zeilen der anderen Tabelle des Joins
hinzugefügt.
1. Beispiel:
Der Outer Join sorgt dafür, daß alle Abteilungen ausgegeben werden,
auch die, in welchen keine Angestellten beschäftigt sind.
SELECT
FROM
INTO
WHERE
name, abteilungsname, abteilungsnr, abteilungsort
angestellte, abteilung
:emp_name, :dept, :dept_no, :loc
abteilung.abteilungsnr = angestellte.abteilungsnr(+);
2. Beispiel:
Der Outer Join kann auch auf mehrere Bedingungen in einer Abfrage
angewandt werden, auch die, in welchen keine Angestellten beschäftigt
sind.
SELECT
name, abteilungsname, abteilungsnr, abteilungsort,
firmenname
angestellte, abteilung, firma
:emp_name, :dept, :dept_no, :loc, :firm
abteilung.abteilungsnr = angestellte.abteilungsnr(+)
firma.abteilungsnr = angestellte.abteilungsnr(+);
FROM
INTO
WHERE
AND
6.13 Fehleranalyse und -behandlung
An dieser Stelle sollen nur die wichtigsten Anweisungen der Fehlerbehandlung
aufgeführt werden, wie sie für den Einstieg in die embedded SQL Programmierung
notwendig sind.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
87
EXEC SQL WHENEVER <BEDINGUNG> <REAKTION>;
Bedingung:
SQLWARNING
SQLERROR
NOT FOUND
Reaktionen:
CONTINUE
STOP
GOTO marke
DO
function
z.B.
EXEC SQL WHENEVER NOT FOUND GOTO ende;
EXEC SQL WHENEVER SQLERROR GOTO FEHLER;
6.14 Zusammenfassung
Anhand der Übungsaufgaben und Musterlösungen können Sie einige praktische
Vorgehensweisen und Ansätze selbst erforschen. Nach dem Sie sich die ersten
Beispiele angeschaut und verinnerlicht haben, sollten Sie versuchen, die Aufgaben
auch selbständig ohne die Zuhilfenahme der Musterlösung zu bearbeiten. Mit ein
wenig Praxis lassen sich hier schnell die größten Probleme angehen und lösen.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
88
7 Aufgaben zum Datenbank-Seminar
7.1 Aufgaben zum ERM
− Ausgangspunkt sei die folgende Problembeschreibung:
An einer Fachhochschule gibt es viele verschiedene Fachbereiche. Jeder
Fachbereich bietet einen Studiengang an. In diesen sind Studenten
eingeschrieben. Sie besuchen Vorlesungen ihres jeweiligen Semesters, die von
den Professoren des Fachbereichs gehalten werden. Die Professoren können
außerdem noch Funktionen wie Dekan oder Praktikantenamtsleiter im
Fachbereich übernehmen. Dieselbe Vorlesungen kann von Studenten mehrerer
Fachbereiche besucht werden. Sie hat jedoch nur eine eindeutige Fachnummer.
Außerdem haben sie eine definierte Wochenstundenzahl und natürlich einen
Namen. Die Fachbereiche werden durch einen eindeutigen Namen identifiziert, zu
dem es ein ebenso eindeutiges Kürzel gibt. Studenten wie Professoren haben
Name und Vorname. Professoren haben ein Gehalt, Studenten dafür ihre
Matrikelnummer. Jeder Student ist in einem bestimmten Semester.
Erstellen Sie ein ERM.
7.2 Aufgaben zum Relationalen Modell
− Konvertierung in ein relationales Modell
Wandeln Sie das obige ERM in ein relationales Modell um. Achten sie dabei
auf Link-Attribute und komplexe Beziehungen.
• Identifizieren sie für jede Relation die möglichen Candidate-Keys und wählen
sie daraus einen Primärschlüssel. Kennzeichnen Sie Primär- und Fremdschlüssel in ihrem Modell.
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
89
7.3 Aufgabe zur Normalisierung
a) Überprüfen Sie, ob ihr Relationales Modell die ersten drei Normalformen erfüllt,
und arbeiten Sie ggf. nach.
b) Normalisieren sie untenstehende Vorgabe
Softwarehaus:
Programmierer
Maier
Buchberg
Maier
Schäfer
Buchberg
Derderlad
Preis
500.10.500.1000.0.10599.-
Projekt
FiBu
AAV
AAV
FiBu
AAV
FiBu
Hersteller
Kleinweich
GNU
Kleinweich
Bohrhand
UniMex
Delphi-Soft
Tool
WirfWeg 6.0
Reh++
WirfWeg 6.0
Zeh++
Kharaz
Orakel for NULL
Deadline
31.12.95
31.07.95
31.07.96
31.12.95
31.07.96
31.12.95
Kunde
Stadt ES
Immens
Immens
Stadt ES
Immens
Stadt ES
7.4 Aufgaben zu SQL*PLUS
Das Datenmodell wird folgendermaßen erzeugt:
CREATE TABLE fachbereich (kuerzel VARCHAR (3), name VARCHAR (30),
budget INT)
CREATE TABLE Person (id int, name VARCHAR (30), vorname VARCHAR (30) )
CREATE TABLE Professor (persnr INT, id INT, gehalt INT, kuerzel VARCHAR (3),
d_bz varchar(1), pal_bz varchar(1))
CREATE TABLE Student (matr_nr INT, semester INT, kuerzel varchar(3), id int)
CREATE TABLE Vorlesung ( fach_nr INT, persnr INT, name VARCHAR(40),
sws INT)
CREATE TABLE stud_vorl (fach_nr INT, matr_nr INT)
CREATE TABLE Fb_Vorl (kuerzel VARCHAR (3), fach_nr INT, semester INT)
CREATE TABLE Fachbereich (kuerzel VARCHAR(3), name VARCHAR(30), budget
INT)
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
90
1.
Machen Sie sich einen Überblick über die vorhandenen Tabellen.
2.
Korrigieren Sie Ihre persönlichen Angaben in den Tabellen Person und
Student.
3.
Tragen Sie sich in Tabelle Stud_Vorl mindestens in 2 Vorlesungen ein.
4.
Führen Sie Abfragen durch um die folgenden Informationen zu erhalten:
4.1
Liste der Studenten mit Vor- und Nachnamen sowie Matrikelnummer. Die
Liste soll alphabetisch sortiert sein.
4.2
Liste der Namen von Studenten mit dazugehörigem Fachbereich alphabetisch
nach Namen sortiert.
4.3
Liste aller Studenten mit dazugehörigen Vorlesungen.
4.4
Liste aller Studenten, die Vorlesungen eines Professors mit Personalnummer
1000 besuchen.
4.5
Liste der Vor- und Nachnamen von Studenten, deren Matrikelnummer
zwischen 400002 und 400006 liegt.
4.6
Liste aller Personen dessen Nachnamen mit „W“ beginnt.
5
Löschen Sie den Studenten mit der Matrikelnummer 666666 aus der
Datenbank. Auf welche Tabellen müssen Sie zugreifen?
7.5 Aufgaben zu Embedded SQL
Lösen Sie die in „Aufgaben in SQL*PLUS“ gestellten Fragen 4.1 bis 4.6 mi t Hilfe von
Embedded SQL.
Hinweise zum Einloggen (Aufgabe 6.4 und 6.5)
1.
2.
3.
4.
5.
6.
Lokales login ( z.B. rhds01,nthx01,nthx02,...)
rlogin rshx01.rz -l xxxxxx
login bitte beim Kursdozenten nachfragen
Password: yyyyyy
Passwort bitte beim Kursdozenten nachfragen
Oracle 7 - User:
stud[1..10]
Oracle 7 - Password: st[1..10]
Ausführen: start [Name]
Verzeichnisse unter:
~/DB_Kurs/stud[1..10]
~/DB_Kurs/create ( *.sql - Dateien)
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
91
8 Lösungen
8.1 Lösung zum ERM
Name
Person
Gehalt
Semester
Professor
ist eingeschrieben
bei
1
Vorname
2
Matrikel
Nummer
Student
hält
3
besucht
bietet an
Fachbereich
Vorlesung
1: ist Dekan
2: ist Prakt.Amt.L
Name
3: gehört zu
Kürzel
Studiengang
Name
Semester
FachNr
Wochenstunden
Fachhochschule ist überflüssig, da nur einmal vorhanden. Studiengang und
Fachbereich bezeichnen praktisch dasselbe.
Erweiterungen:
Es gibt auch Dozenten und Lehrbeauftragte. Neben Vorlesungen existieren
darüberhinaus noch Labors. Sie finden, ebenso wie Vorlesungen in Räumen statt.
Die Laborräumen werden von Meistern betreut. Wie könnte man StuPo´s,
Prüfungsanmeldung und Notenspiegel im Modell unterbringen?
Lösungshinweise:
Lehrbeauftragter und Professor werden vom Dozent abgeleitet. Der Dozent ist eine
Person, die Veranstaltungen hält. Eine Vorlesung und ein Labor sind
Veranstaltungen. Eine Veranstaltung findet in einem Raum statt. Das kann eine Aula
oder ein Laborraum sein. Der Laborraum wird von einem Meister betreut. Er ist eine
Person die zum Fachbereich gehört. Ein Fachbereich hat mehrere StuPos. In ihnen
tauchen die Veranstaltungen mit einer Gewichtung für das Diplomzeugnis auf. Ein
Student studiert nach einer dieser StuPos und meldet sich für Veranstaltungen zur
Prüfung an. Die entsprechende Beziehung enthält einen Zähler für die Versuche, die
Note und ggf. einen Status (angemeldet, bestanden, durchgefallen).
8.2 Lösung zum Relationalen Modell
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
92
<kuerzel,name,budget>
<persnr,gehalt,id#,kuerzel#,d_bz,p_bz>
1,n
gehört zu
1,1
ist Dekan
Professor
1,1
ist Prakt.
0,1
1,1
1,1
0,1
0,1
1,1
Fachbereich
1,1
<id,name,vorname> 1,1
hat
hält
Person
1,1
0,1
Student
1, n
1, n
1,1
Stud_Vorl
1, n
1, n
<Matrnr,kuerzel#,id#,semester> <matrnr#,fachnr#>
1,1
1, n
Vorlesung
<fachnr,persnr#,kuerzel#,
name,sws,semester>
ist eingeschrieben
8.3 Lösung zur Normalisierung
Programmierer
Name
bearbeitet
Name
Name
Tool
Projekt
wird verwendet
Hersteller
Preis
Deadline
Kunde
Programmierer
Persnr
Name
...
Maier
...
Buchberg
...
Schäfer
...
Derderlad
...
Projekt
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
93
Name
Deadline
Kunde
AAV
31.07.96
Stadt ES
FiBu
31.12.95
Immens
Prog Proj
Prog persnr#
Proj Name#
...
FiBu
...
AAV
...
AAV
...
FiBu
...
Derderlad
Tool
Name
Hersteller
Preis
Wirfweg 6.0
Kleinweich
500
Reh++
GNU
10
Zeh++
Bohrhand
1000
Kharaz
UniMex
0
Orakel for NULL
Delphi-Soft
10599
Tool Projekt
Tool name#
Projekt name#
Wirfweg 6.0
FiBu
Reh++
AAV
Wirfweg 6.0
AAV
Zeh++
FiBu
Kharaz
AAV
Orakel for NULL
FiBu
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
94
8.4 Lösungen zu SQL*PLUS
1
DESCRIBE <Tabelle>
2
UPDATE <Tabelle> SET <Spalte> = <Wert> WHERE <Bedingung>;
3
INSERT INTO stud_vorl VALUES (<Wert1> , <Wert2>..);
4.1
SELECT Name, Vorname, Matr_nr
FROM Person,Student
WHERE Person.id = Student.id
ORDER BY Name;
4.2
SELECT Person.Name, fachbereich.name
FROM Person,Fachbereich,Student
WHERE Student.id = Person.id
AND Fachbereich.kuerzel = Student.kuerzel
ORDER BY Person.name;
4.3
SELECT vorlesung.Name, student.matr_nr
FROM Vorlesung,Student,Stud_vorl
WHERE Student.Matr_nr = Stud_vorl.Matr_nr
AND Stud_Vorl.fach_nr = Vorlesung.fach_nr
ORDER BY Vorlesung.name;
4.4
SELECT Person.Name, Vorlesung.Name, Vorlesung.Persnr
FROM Person, Student, Stud_Vorl, Vorlesung
WHERE Person.id = Student.id
AND Student.Matr_nr = Stud_Vorl.Matr_Nr
AND Stud_Vorl.Fach_Nr = Vorlesung.Fach_Nr
AND Vorlesung.Persnr = 1000;
4.5
SELECt Person.Name, Person.Vorname, Student.Matr_nr
FROM Person,Student
WHERE Student.Matr_nr BETWEEN 400002 and 400006
AND Student.id = Person.id;
4.6
SELECT Name,Vorname
FROM Person
WHERE Name LIKE 'W%';
5.
DELETE from Person,Student
WHERE Student.matr_nr = 666666
AND Student.id = Person.id
AND Student.matr = Stud_vorl.matr_nr;
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
95
8.5 Lösungen Embedded SQL
Aufgabe 1 / Lösung SQL*PLUS 4.1 in Embedded SQL
#include <stdio.h>
#include <string.h>
EXEC SQL BEGIN DECLARE SECTION;
int
Matr_nr;
VARCHAR
Name[25];
VARCHAR
Vorname[25];
VARCHAR
username[20];
VARCHAR
password[20];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE sqlca;
main()
{
strcpy(username.arr,"stud1");
username.len=strlen(username.arr);
strcpy(password.arr,"st1");
password.len=strlen(password.arr);
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("\n in SQL eingeloggt");
/* CURSOR DECLARIEREN */
EXEC SQL DECLARE abfrage CURSOR FOR
SELECT Name,Vorname,Matr_nr from Person,Student
WHERE Person.id = Student.id
order by Name;
EXEC SQL OPEN abfrage;
EXEC SQL WHENEVER NOT FOUND GOTO raus;
while (1)
/* (sqlca.sqlcode == 0) */
{
EXEC SQL FETCH abfrage INTO :Name,:Vorname,:Matr_nr;
{
Name.arr[Name.len]='\0';
Vorname.arr[Vorname.len]=’\0’;
printf ("\nNAME: %30s | Vorname: %30s | Matr._Nr.
%d",Name.arr,Vorname.arr,Matr_nr);
}
}
raus: EXEC SQL CLOSE abfrage;
printf("\n\n");
EXEC SQL COMMIT WORK RELEASE;
}
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
96
Aufgabe 2 / Lösung SQL*PLUS 4.2 in Embedded SQL
#include <stdio.h>
#include <string.h>
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR
Person_name[25];
VARCHAR
Fachbereich_name[25];
VARCHAR
username[20];
VARCHAR
password[20];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE sqlca;
main()
{
strcpy(username.arr,"stud1");
username.len=strlen(username.arr);
strcpy(password.arr,"st1");
password.len=strlen(password.arr);
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("\n in SQL eingeloggt");
/* CURSOR DECLARIEREN */
EXEC SQL DECLARE abfrage CURSOR FOR
select Person.Name, fachbereich.name
from Person,Fachbereich,Student
where Student.id = Person.id
and Fachbereich.kuerzel = Student.kuerzel
order by Person.name;
EXEC SQL OPEN abfrage;
EXEC SQL WHENEVER NOT FOUND GOTO raus;
while (1)
/* (sqlca.sqlcode == 0) */
{
EXEC SQL FETCH abfrage INTO :Person_name,:Fachbereich_name;
{
Fachbereich_name.arr[Fachbereich_name.len]='\0';
Person_name.arr[Person_name.len]=’\0’;
printf ("\nPerson: %30s | Fachbereich: %30s
",Person_name.arr,Fachbereich_name.arr);
}
}
raus: EXEC SQL CLOSE abfrage;
printf("\n\n");
EXEC SQL COMMIT WORK RELEASE;
}
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
97
Aufgabe 3 / Lösung SQL*PLUS 4.3 in Embedded SQL
#include <stdio.h>
#include <string.h>
EXEC SQL BEGIN DECLARE SECTION;
int
Matr_nr;
VARCHAR
Vorlesung_name[25];
VARCHAR
username[20];
VARCHAR
password[20];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE sqlca;
main()
{
strcpy(username.arr,"stud1");
username.len=strlen(username.arr);
strcpy(password.arr,"st1");
password.len=strlen(password.arr);
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("\n in SQL eingeloggt");
/* CURSOR DECLARIEREN */
EXEC SQL DECLARE abfrage CURSOR FOR
select vorlesung.Name, student.matr_nr
from Vorlesung,Student,Stud_vorl
where Student.Matr_nr = Stud_vorl.Matr_nr
and Stud_Vorl.fach_nr = Vorlesung.fach_nr
order by Vorlesung.name;
EXEC SQL OPEN abfrage;
EXEC SQL WHENEVER NOT FOUND GOTO raus;
while (1)
/* (sqlca.sqlcode == 0) */
{
EXEC SQL FETCH abfrage INTO :Vorlesung_name,:Matr_nr;
{
Vorlesung_name.arr[Vorlesung_name.len]='\0';
printf ("\nVorlesung: %30s | Matrikelnummer: %d
,Vorlesung_name.arr,Matr_nr);
}
}
raus: EXEC SQL CLOSE abfrage;
printf("\n\n");
EXEC SQL COMMIT WORK RELEASE;
}
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
98
Aufgabe 4 / Lösung SQL*PLUS 4.4 in Embedded SQL
#include <stdio.h>
#include <string.h>
EXEC SQL BEGIN DECLARE SECTION;
int
Persnr;
VARCHAR
Person_name[25];
VARCHAR
Vorlesung_name[25];
VARCHAR
username[20];
VARCHAR
password[20];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE sqlca;
main()
{
strcpy(username.arr,"stud1");
username.len=strlen(username.arr);
strcpy(password.arr,"st1");
password.len=strlen(password.arr);
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("\n in SQL eingeloggt");
/* CURSOR DECLARIEREN */
EXEC SQL DECLARE abfrage CURSOR FOR
Select Person.Name, Vorlesung.Name, Vorlesung.Persnr
from Person, Student, Stud_Vorl, Vorlesung
where Person.id = Student.id
and Student.Matr_nr = Stud_Vorl.Matr_Nr
and Stud_Vorl.Fach_Nr = Vorlesung.Fach_Nr
and Vorlesung.Persnr = 1000;
EXEC SQL OPEN abfrage;
EXEC SQL WHENEVER NOT FOUND GOTO raus;
while (1)
/* (sqlca.sqlcode == 0) */
{
EXEC SQL FETCH abfrage INTO :Person_name,:Vorlesung_name,:Persnr;
{
Vorlesung_name.arr[Vorlesung_name.len]='\0';
Person_name.arr[Person_name.len]='\0';
printf ("\nName: %30s| Vorlesung: %s | Persnr: %d
",Person_name.arr,Vorlesung_name.arr,Persnr);
}
}
raus: EXEC SQL CLOSE abfrage;
printf("\n\n");
EXEC SQL COMMIT WORK RELEASE;
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
99
}
Aufgabe 5 / Lösung SQL*PLUS 4.5 in Embedded SQL
#include <stdio.h>
#include <string.h>
EXEC SQL BEGIN DECLARE SECTION;
int
Matr_nr;
VARCHAR
Person_name[25];
VARCHAR
Person_Vorname[25];
VARCHAR
username[20];
VARCHAR
password[20];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE sqlca;
main()
{
strcpy(username.arr,"stud1");
username.len=strlen(username.arr);
strcpy(password.arr,"st1");
password.len=strlen(password.arr);
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("\n in SQL eingeloggt");
/* CURSOR DECLARIEREN */
EXEC SQL DECLARE abfrage CURSOR FOR
select Person.Name, Person.Vorname,student.matr_nr
from Person,Student
where Student.Matr_nr between 400002 and 400006
and Student.id = Person.id;
EXEC SQL OPEN abfrage;
EXEC SQL WHENEVER NOT FOUND GOTO raus;
while (1)
/* (sqlca.sqlcode == 0) */
{
EXEC SQL FETCH abfrage INTO :Person_name,:Person_Vorname,:Matr_nr;
{
Person_name.arr[Person_name.len]='\0';
Person_Vorname.arr[Person_Vorname.len]='\0';
printf ("\nName: %30s | Vorname: %30s | Matrikelnummer: %d
",Person_name.arr,Person_Vorname.arr,Matr_nr);
}
}
raus: EXEC SQL CLOSE abfrage;
printf("\n\n");
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
100
EXEC SQL COMMIT WORK RELEASE;
}
Aufgabe 6 / Lösung SQL*PLUS 4.6 in Embedded SQL
#include <stdio.h>
#include <string.h>
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR
Name[25];
VARCHAR
Vorname[25];
VARCHAR
username[20];
VARCHAR
password[20];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE sqlca;
main()
{
strcpy(username.arr,"stud1");
username.len=strlen(username.arr);
strcpy(password.arr,"st1");
password.len=strlen(password.arr);
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("\n in SQL eingeloggt");
/* CURSOR DECLARIEREN */
EXEC SQL DECLARE abfrage CURSOR FOR
select Name,Vorname
from Person
where Name like 'W%';
EXEC SQL OPEN abfrage;
EXEC SQL WHENEVER NOT FOUND GOTO raus;
while (1)
/* (sqlca.sqlcode == 0) */
{
EXEC SQL FETCH abfrage INTO :Name,:Vorname;
{
Name.arr[Name.len]='\0';
Vorname.arr[Vorname.len]='\0';
printf ("\nName: %30s | Vorname: %30s
",Name.arr,Vorname.arr);
}
}
raus: EXEC SQL CLOSE abfrage;
printf("\n\n");
EXEC SQL COMMIT WORK RELEASE;
}
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
101
9 Literaturhinweise
zum Entity Relationship und Relationalen Datenmodell:
[Alber] Jörg Alber, Datenbankkomponente im Projekt VVA
Diplomarbeit im Fachbereich NT, WS94
[Sauer] Sauer, Relationale Datenbanken
[Letters] Letters, Vorlesungsmanuskript
Datenmodellierung FB WI im WS94
zum Kapitel SQL:
[1]
[2]
[3]
[4]
[5]
[6]
Finkenzeller/Kracke/Unterstein, 'Systematischer Einsatz von SQL-Oracle',
ddsion-Wesley 1989.
Sauer, 'Relationale Datenbanken', Addsion-Wesley 1991.
Wilke, 'Oracle Datenbankmanagement professionell' 2.Aufl., Addison-Wesley
1991.
Oracle Deutschland GmbH, 'Professioneller Einstieg in SQL und Oracle',
Müchen 1994.
Misgeld, 'Oracle für Profis', Hanser, München, Wien 1991.
Letters, 'Daten-Modellierung', IBL, Echterdingen 1995
──────────────────────────────────────────────────────────────
STEINBEIS-TRANSFERZENTRUM
SOFTWARETECHNIK ESSLINGEN
Datenbank-Seminar für Diplomanden
V 1.7 20.05.1997
Goll
