# Datenbanken Theorie

Werbung
```Udo Matthias Munz
Relationale
Datenbanken
und
SQL
Informationstechnik
Einf&uuml;hrung
Unformatierte Daten
EINF&Uuml;HRUNG ................................................................................................................................................4
Daten und Information...............................................................................................................................4
MODELLE VON DATENBANKEN .............................................................................................................4
UNFORMATIERTE DATEN ...............................................................................................................................4
HIERARCHISCHES MODELL ............................................................................................................................4
TABELLE.........................................................................................................................................................5
ARBEITEN MIT EINER DATENBANK......................................................................................................5
RELATIONALE DATENBANKEN ..............................................................................................................6
NORMALISIEREN EINER RELATION / TABELLE AM BEISPIEL .........................................................................6
Die erste Normalform (1NF)......................................................................................................................6
Die zweite Normalform (2NF) ...................................................................................................................7
Die dritte Normalform (3NF).....................................................................................................................7
Verkn&uuml;pfung...............................................................................................................................................8
Nachteile einer extremen Normalisierung .................................................................................................8
EINIGE BEGRIFFE............................................................................................................................................8
Entit&auml;t, Entit&auml;tstyp .....................................................................................................................................8
Entit&auml;tsbeziehungen ...................................................................................................................................8
Identifikationsschl&uuml;ssel ..............................................................................................................................8
Redundanz, Mutation und Anomalie ..........................................................................................................9
REFERENTIELLE INTEGRIT&Auml;T .......................................................................................................................10
ENTITY-RELATIONSHIP-DIAGRAMM (ER-DIAGRAMM) ...............................................................10
Entitytyp (Objekt).....................................................................................................................................10
Attribute ...................................................................................................................................................11
Abh&auml;ngige (weak) Entities .......................................................................................................................12
Relation ....................................................................................................................................................13
Kardinalit&auml;t..............................................................................................................................................14
Die Darstellung in einem vereinfachten ER-Diagramm ..........................................................................14
F&Uuml;NF SCHRITTE ZUM ER-MODELL ..............................................................................................................15
&Uuml;BERLEGUNGEN BEIM ENTWURF EINES DATENMODELLS.......................................................16
ER-DIAGRAMM ............................................................................................................................................16
TABELLEN ERSTELLEN .................................................................................................................................17
RELATIONEN MIT ATTRIBUTEN ....................................................................................................................17
DATENTYPEN ...............................................................................................................................................17
AUF DEM INDEX: DIE SCHL&Uuml;SSELFELDER ....................................................................................................17
DATENBANKANWENDUNG FERTIGSTELLEN .................................................................................................17
UMSETZUNG ER-DIAGRAMM IN TABELLEN ....................................................................................18
Die erste Regel:........................................................................................................................................18
Die zweite Regel: .....................................................................................................................................18
Die dritte Regel:.......................................................................................................................................19
Die vierte Regel: ......................................................................................................................................19
ZUSAMMENFASSUNG ...............................................................................................................................20
REKURSIVE BEZIEHUNGEN ...................................................................................................................23
Beispiel: ...................................................................................................................................................23
TERN&Auml;RE BEZIEHUNGEN .......................................................................................................................23
Beispiel1: .................................................................................................................................................23
Beispiel2: .................................................................................................................................................23
UMSETZUNG IN RELATIONEN (TABELLEN) ..................................................................................................23
GENERALISIERUNG ..................................................................................................................................26
2
Einf&uuml;hrung
Unformatierte Daten
DATENBANKZUGRIFFE MIT SQL..........................................................................................................27
Kleine SQL-Geschichte ............................................................................................................................27
Ein SQL-Beispiel: ....................................................................................................................................28
SQL - BEFEHLS&Uuml;BERSICHT ..........................................................................................................................28
ABFRAGEFORMULIERUNG MIT SQL ..................................................................................................29
BEISPIEL-DATENBANK .................................................................................................................................29
1 PROJEKTION UND FORMATIERUNG ...........................................................................................................30
2 SELEKTION ................................................................................................................................................31
3 VERBUND VON TABELLEN ........................................................................................................................32
4 AGGREGATFUNKTIONEN UND GRUPPEN ...................................................................................................33
5 UNTERABFRAGEN (SUBQUERIES)..............................................................................................................34
6 STRUKTUR VON TABELLEN ERZEUGEN, &Auml;NDERN UND L&Ouml;SCHEN ..............................................................35
WEITERE HINWEISE ZUM VERBUND VON TABELLEN ..................................................................................36
Inner Join: Verkn&uuml;pfung von Tabellen ....................................................................................................36
Auto Join/Self Join: Verkn&uuml;pfung einer Tabelle mit sich selbst...............................................................36
Outer Join: Verkn&uuml;pfung von Tabellen....................................................................................................36
Unterabfragen: Einzeilige (= Single Row Subquery) ..............................................................................36
Unterabfragen: Mehrzeilige (= Multiple Row Subquery)........................................................................37
WEITERE BEISPIELE ZUM VERBUND VON TABELLEN ..................................................................................37
3
Einf&uuml;hrung
Unformatierte Daten
Einf&uuml;hrung
Daten und Information
Grunds&auml;tzlich ist eine Datenbank eine Sammlung von Daten. Diese k&ouml;nnen in beliebiger Form gespeichert
sein, auf fliegenden Bl&auml;ttern genauso wie in einem Karteikasten oder auf einem Computersystem. Datenbanken haben aber erst mit der Verbreitung des Computers an Brisanz gewonnen, weil nur mit ihrer Hilfe aus
den Daten Informationen gewonnen werden k&ouml;nnen, die so nicht gespeichert sind. Deshalb sind neben der
Entwicklung von Datenbanksystemen die Fragen des Datenschutzes bedeutsam geworden. Datenschutz bedeutet, dass nicht die Daten selbst gesch&uuml;tzt werden sollen (das w&auml;re die Frage der Datensicherheit, z.B.
regelm&auml;&szlig;ige Sicherungskopien anfertigen) sondern es sollen die Personen gesch&uuml;tzt werden, deren Daten
gespeichert werden.
Datenbanken, Informationssysteme sowie Datenschutz und Datensicherheit als Expertengruppenarbeit
Siehe http://www.educeth.ch/informatik/puzzles/rundum/
Modelle von Datenbanken
Unformatierte Daten
Keine feste Struktur.
Hierarchisches Modell
In Computer-Datenbanken werden die Daten meist in einer bestimmten, klar beschriebenen Struktur verwaltet:
Auf dem Datentr&auml;ger sind die Daten meist
linear angeordnet. Als Modell f&uuml;r die Datenbank kann man sich daher vorstellen,
dass die einzelnen Zeichen aneinander gereiht sind. Daraus bilden sich die Datenfelder, welche Datens&auml;tze ergeben. Insgesamt
ergibt sich eine Datei. Mehrere Dateien, die
zu einem gemeinsamen Projekt geh&ouml;ren
bilden die Datenbank.
4
Arbeiten mit einer Datenbank
Tabelle
BOF
Zeiger
Datenfeld1
Datenfeld2
EOF
Datenfeld3
Datenfeld4
Datensatz1
Datensatz2
Datei1
Der Zugriff auf die Datei geschieht &uuml;ber Zeiger, die auf das jeweils gelesene Zeichen zeigen. Ein Zeiger
bezeichnet den Anfang der Datei (begin of file, BOF) einer deren Ende (end of file, EOF).
1:
Datenbank1
Zeichnen Sie die Struktur einer hierarchischen Datenbank mit folgenden Feldern: Name(3),
PLZ(5) und Ort(4) f&uuml;r die Personen: Tom, 77815, B&uuml;hl; Ben, 77654, OG, Ali, 77815, B&uuml;hl
Nennen Sie einige Probleme bei dieser Datenbank.
Tabelle
Man kann sich die Organisation einer Datei auch wie eine Tabelle vorstellen. Dieses Modell ist weiter weg
von der technischen Wirklichkeit, ist aber f&uuml;r die Anwendung praktischer. Jeder Datensatz bildet eine Zeile
der Tabelle. Die einzelnen gleichen Datenfelder jeden Datensatzes kommen dadurch direkt untereinander zu
stehen, wodurch die &Uuml;bersichtlichkeit erheblich gesteigert wird.
Datenfelder
Datensatz
Beachten Sie, dass dieser Aufbau nicht den realen, physischen Aufbau der Daten auf dem Datentr&auml;ger widerspiegelt.
Arbeiten mit einer Datenbank
Auch bei einer Datenbank gilt das EVA-Prinzip:
Die Daten werden
•
in Formularen, Datenzugriffseiten eingelesen, die Speicherung der Datenfelder und Datens&auml;tze stellen wir uns in Form von Tabellen vor.
•
vom Database Managementsystem (DBMS) verarbeitet,
•
mit Abfragen und Berichten ausgegeben.
5
Relationale Datenbanken
Normalisieren einer Relation / Tabelle am Beispiel
Relationale Datenbanken
Eine komplette Datenbank besteht zur Zeit des Datenbankaufbaus nur aus der Software, mit der man die
Datenbank bearbeiten kann und der Hardware, auf der das Programm l&auml;uft. Beides hat seinen Preis, der gut
kalkulierbar ist. Die Kosten der Datenbasis selbst spielen zu diesem Zeitpunkt eine untergeordnete Rolle. Im
Laufe der Zeit werden aber die Daten einen Wert darstellen, der die Hard- und Softwarekosten um ein Vielfaches &uuml;bersteigen kann. Die Stammdaten m&uuml;ssen &uuml;ber Jahre hinweg gepflegt und erweitert werden. F&uuml;r
Versicherungsdaten beispielsweise ist eine Lebensdauer der Stammdaten von 50 und mehr Jahren angesagt.
Vor diesem Hintergrund wird klar, dass man dem Aufbau der Datenbasis erheblich mehr Aufmerksamkeit
widmen sollte als der Hardware, die vielleicht nach 3-5 Jahren bereits ersetzt werden muss.
Anfang der 70er Jahre entwickelten Codd, Chen und andere Mathematiker das relationale Datenbankmodell,
welches bereits Anfang der 80er Jahre als „dBase“ auf Personalcomputern implementiert wurde und sich bis
heute allgemein f&uuml;r PC-Datenbanken durchgesetzt hat.
Im relationalen Datenbankmodell werden die Daten in (meist mehreren) Tabellen unabh&auml;ngig voneinander
gespeichert. Die Beziehungen zwischen den Daten werden erst durch die Anwendung realisiert.
Normalisieren einer Relation / Tabelle am Beispiel
Redundanzen sind in relationalen Datenbanken zu vermeiden. Im Verlaufe der Normalisierung sollen einerseits die Relationen aufgebaut und andererseits die Redundanzen verringert werden.
Ich verwende zur Erl&auml;uterung der Normalisierung als Beispiel eine Freunde- und CD-Datenbank. Meine
Ausleihtabelle FREUNDE.DB besteht zun&auml;chst nur aus einer einzigen Tabelle:
Name
Unsinn
Maus
D&uuml;sentrieb
Maus
Vorname
Reiner
Mickey
Daniel
Mickey
Telefon
564321
1234
9999
1234
Datum
01.01.97
12.01.97
14.11.97
14.11.97
Titel
Tina Dreams
Tina Dreams, Stones Best
Harry Lemon Tree, Tina Dreams, Beatles Rotes
Stones Best
Die gesamte Datenbank besteht aus einer einzigen Tabelle. Wir brauchen keine Schl&uuml;ssel zu definieren, weil
wir keine Verkn&uuml;pfungen mit anderen Tabellen bilden m&uuml;ssen und somit Eindeutigkeit nicht unbedingt erforderlich ist.
Diese Tabelle ist aber nicht sehr sinnvoll: die Anzahl der ausgeliehenen Titel ist begrenzt auf die Zahl, die in
das Feld „Titel“ hineinpasst. Au&szlig;erdem ist eine Suche r&uuml;ckw&auml;rts („Wer hat meine Beatles-CD?“) sehr erschwert. Jede neue Ausleihe erzeugt eine (unkontrollierte) Redundanz!
Die erste Normalform (1NF)
Der erste Schritt der Normalisierung vermeidet mehrere Merkmale in einem Feld.
Name
Unsinn
Maus
Maus
D&uuml;sentrieb
D&uuml;sentrieb
D&uuml;sentrieb
Maus
Vorname
Reiner
Mickey
Mickey
Daniel
Daniel
Daniel
Mickey
Telefon
564321
1234
1234
9999
9999
9999
1234
Datum
01.01.97
12.01.97
12.01.97
14.11.97
14.11.97
14.11.97
14.11.97
Titel
Tina Dreams
Tina Dreams
Stones Best
Harry Lemon Tree
Tina Dreams
Beatles Rotes
Stones Best
Fertig sind wir mit dieser Form noch nicht, denn jetzt haben wir neben dem Problem der vielen redundanten
Daten noch weitere Problembereiche:
Die &Auml;nderungsabh&auml;ngigkeit (Update-Dependency): eine &Auml;nderung der Telefonnummer von Mickey
muss in allen Zeilen durchgef&uuml;hrt werden um eine „&Auml;nderungsanomalie“ zu verhindern.
Die Einf&uuml;gungsabh&auml;ngigkeit (Insertion-Dependency): zum Identifizierungsschl&uuml;ssel muss das Ausleihdatum und der Titel hinzugef&uuml;gt werden (Eindeutigkeit der Tupel!, siehe „Identifikationsschl&uuml;ssel“ Seite
6
Relationale Datenbanken
Normalisieren einer Relation / Tabelle am Beispiel
8). Will man einen neuen Freund in die Liste aufnehmen, funktioniert das nur, wenn er gleichzeitig eine
Ausleihe vornimmt, weil die Felder Datum und Titel ja zum Schl&uuml;ssel geh&ouml;ren m&uuml;ssen und daher nicht
leer bleiben d&uuml;rfen.
Die L&ouml;schabh&auml;ngigkeit (Deletion-Dependency): ist das Gegenst&uuml;ck zur Einf&uuml;gungsabh&auml;ngigkeit. Wenn
Daniel seine beiden ausgeliehen CDs zur&uuml;ckgibt, dann werden mit dem L&ouml;schen auch alle seine Daten
gel&ouml;scht.
Die zweite Normalform (2NF)
Wir teilen die Datenbank in mehrere Tabellen auf, die jede nach den Entit&auml;ten: Freundedaten und Verleihdaten unterschieden sind. In beiden Tabellen wird das Feld FID zus&auml;tzlich eingef&uuml;gt. Mit Hilfe dieser Identifizierungsnummer (Identifizierungsschl&uuml;ssel) k&ouml;nnen die Beziehungen zwischen den beiden Tabellen wieder
hergestellt werden. Die Freundetabelle ist nun unabh&auml;ngig von der Verleihtabelle erweiterbar, und diese
wieder unabh&auml;ngig von der Freundetabelle.
Freundetabelle
FID Name
Maus
1
D&uuml;sentrieb
2
Unsinn
3
Vorname
Mickey
Daniel
Reiner
Telefon
1234
9999
564321
Verleihtabelle
FID
Datum
01.01.97
3
12.01.97
1
12.01.97
1
14.11.97
2
14.11.97
2
14.11.97
2
14.11.97
1
Titel
Tina Dreams
Tina Dreams
Stones Best
Harry Lemon Tree
Tina Dreams
Beatles Rotes
Stones Best
Das DBMS muss die Verbindung zwischen den Tabellen herstellen k&ouml;nnen. Daher ist in der Verleihtabelle
der Schl&uuml;ssel FID mit aufzunehmen. Man nennt diesen Schl&uuml;ssel in der Verleihtabelle einen „Fremdschl&uuml;ssel“, das entsprechende Attribut in der Freundetabelle ist dort der Identifikations- oder Prim&auml;rschl&uuml;ssel.
Die dritte Normalform (3NF)
Wir haben in der Verleihtabelle aber immer noch Redundanzen, n&auml;mlich beim Datum und beim Titel. Daher
m&uuml;ssen wir die zweite Tabelle noch weiter aufteilen und gelangen zur 3. Normalform.
tbFreunde
FID Name
Maus
1
D&uuml;sentrieb
2
Unsinn
3
Vorname
Mickey
Daniel
Reiner
Telefon
1234
9999
564321
tbVerleih
FID Datum
01.01.97
3
12.01.97
1
12.01.97
1
14.11.97
2
14.11.97
2
14.11.97
2
14.11.97
1
TID
1
1
2
4
1
5
2
tbCDs
TID
1
2
3
4
5
Titel
Tina Dreams
Stones Best
Elvis Jailhouse
Harry Lemon Tree
Beatles Rotes
Betrachten Sie nun einmal die Beziehungen zwischen diesen Tabellen. Ein Tupel in der Freundetabelle
tbFreunde kann auf mehrere Tupel in der Verleihtabelle tbVerleih verweisen (1:n), d.h. ein Freund kann
mehrmals CDs ausleihen. Anders sind die Verh&auml;ltnisse jedoch zwischen der Verleihtabelle tbVerleih und der
CDs-Tabelle tbCDs. Hier haben wir eine n:1-Beziehung, weil ja dieselbe CD auch mehrmals ausgeliehen
werden kann. (Zwischenzeitliche R&uuml;ckgabe nat&uuml;rlich vorausgesetzt. Dies ist aber in diesem DatenbankModell nicht erkennbar.)
In der Tabelle tbVerleih erscheinen zwei Fremdschl&uuml;ssel: FID und TID. Diese Tabelle hat eine zentrale Bedeutung f&uuml;r die Verkn&uuml;pfungen (Relationen) der Datenbank.
Damit h&auml;tten wir f&uuml;r die meisten F&auml;lle die optimale Datenbankrelation erreicht. Die Redundanz beim Datum
werde ich bestehen lassen und die Verleihtabelle nicht noch mal unterteilen. Das w&auml;re dann die 4NF.
F&uuml;r die f&uuml;nfte Normalform betrachten wir kein Beispiel mehr. Nur ein Hinweis: wenn sich Datenmengen
&uuml;berschneiden, kann wieder Redundanz auftreten. Dann n&auml;mlich, wenn wir beispielsweise noch eine Komponistentabelle anlegen und ein Freund auch Komponist ist; sein Name m&uuml;sste dann mehrfach gespeichert
werden, n&auml;mlich sowohl in der Freunde als auch in der Komponisten-Tabelle.
7
Relationale Datenbanken
Einige Begriffe
Verkn&uuml;pfung
Die einzelnen Tabellen enthalten jetzt neben den Schl&uuml;sselfeldern (Identifikations- bzw. Fremdschl&uuml;ssel) nur
noch Daten. Die Beziehung der Daten zueinander ist aber verloren gegangen! Die Beziehungen werden aus
den Tabellen allein nicht unmittelbar ersichtlich. Eine Beschreibung einer Datenbank, die die Relationen
besser darstellt ist das ER-Diagramm.
Nachteile einer extremen Normalisierung
Bei extremer Normalisierung k&ouml;nnen viele kleine Tabellen entstehen, die die Leistung (z.B. Antwortverhalten) der Datenbank negativ beeinflussen. Durch die vielen k&uuml;nstlichen Schl&uuml;ssel und die erforderlichen zus&auml;tzlichen Verkn&uuml;pfungen wird das System komplexer, was zu gr&ouml;&szlig;erer Fehleranf&auml;lligkeit f&uuml;hren kann. Die
Schl&uuml;ssel erfordern Speicherplatz, stellen also auch wieder eine Art von Redundanz dar. Es ist daher ein
Kompromiss zwischen Redundanzfreiheit der Daten und Performance des System anzustreben. Es ist nicht
erforderlich jede Redundanz zu entfernen, sondern nur die „unkontrollierte“ Redundanz.
Einige Begriffe
Entit&auml;t, Entit&auml;tstyp
Die einzelnen Objekte der Datenbasis k&ouml;nnen Personen sein, Vorg&auml;nge oder Handlungsmuster. Sie werden
als „Entit&auml;ten“ bezeichnet. Jede Entit&auml;t weist „Merkmale“ auf. Eine Entit&auml;tsmenge (z.B. die Daten aller
Freunde) wird in einer Tabelle abgebildet. Die Tabelle baut sich aus Datens&auml;tzen („Tupeln“) auf, die die
Merkmale (Attribute, Felder) enthalten; jeder Datensatz ist eine Entit&auml;t. Die (allgemeine) Struktur eines Datensatzes ist der Entit&auml;tstyp.
Entit&auml;tsbeziehungen
In unserem Beispiel der CD-Verleihdatenbank haben wir die Tabelle mit den Freunden. In der zweiten Tabelle sind den Freunden die ausgeliehenen CDs zugeordnet. Dabei kann ein Freund keine, eine oder mehrere
CDs ausgeliehen haben. Entsprechend sind einem Tupel in der Freunde-Tabelle kein, ein oder mehrere Tupel
in der Ausleihtabelle zugeordnet. Es handelt sich hier um eine 1:n-Beziehung. In einer dritten Tabelle sind
die Titel und Interpreten der CDs verzeichnet. In Bezug auf die Freundetabelle haben wir es mit einer m:nBeziehung zu tun: jeder Freund kann mehrere CDs ausleihen und jede CD kann mehrmals ausgeliehen werden (vorausgesetzt sie wurde in der Zwischenzeit wieder zur&uuml;ckgegeben).
Identifikationsschl&uuml;ssel
Im Relationenmodell werden in den Tabellen nur die reinen Daten gespeichert. Wie diese miteinander in
Beziehung stehen, muss durch (aufwendige) Methoden in Form von Verkn&uuml;pfungen realisiert werden. Damit
eine Verkn&uuml;pfung von zwei Tabellen &uuml;berhaupt funktionieren kann, muss jedes einzelne Tupel eindeutig
identifiziert werden. Dieses Merkmal nennt man den „Schl&uuml;ssel“. Im Prinzip kann jedes (nat&uuml;rliche) Schl&uuml;sselattribut verwendet werden. Das gibt aber oft Probleme mit der Eindeutigkeit: gleiche Namen kommen oft
mehrfach f&uuml;r verschiedene Personen vor. Deshalb verwendet man meist eine Nummer. Dieser k&uuml;nstliche
Schl&uuml;ssel kann stets so gew&auml;hlt werden, dass er eindeutig ist.
Der (willk&uuml;rlich gew&auml;hlte) k&uuml;nstliche Schl&uuml;ssel hat noch den weiteren Vorteil, ausschlie&szlig;lich der Identifikation des Datensatzes zu dienen und keinerlei weitere Informationen zu enthalten. Warum ist das n&ouml;tig? Beispielsweise k&ouml;nnte die Postleitzahl Bestandteil des Schl&uuml;ssels sein. Dieser „sprechende Schl&uuml;ssel“ enth&auml;lt
also eine zus&auml;tzliche Information. Wenn sich aber das Merkmal &auml;ndert, weil der Kunde umzieht, wird der
Schl&uuml;ssel fortan mit „gespaltener Zunge“ sprechen, weil ja der Schl&uuml;ssel nicht ge&auml;ndert werden darf. W&uuml;rde
der Schl&uuml;ssel nachtr&auml;glich bei einem Kunden ge&auml;ndert, w&uuml;rden alle bisherigen Verkn&uuml;pfungen verloren gehen oder man m&uuml;&szlig;te alle Verkn&uuml;pfungen mitanpassen!
8
Relationale Datenbanken
Einige Begriffe
Redundanz, Mutation und Anomalie
Redundanz bedeutet, dass Daten mehrfach gespeichert werden. Bei der Redundanz ist die Speicherplatzverschwendung das kleinere Problem; viel schlimmer ist die Gefahr, dass sich „Mutationsanomalien“ einschleichen. Ganz allgemein ist die &Auml;nderung auf einem Datensatz eine Mutation. Solche &Auml;nderungen sind nie zu
vermeiden: z.B. Namens&auml;nderung bei Heirat. Wird bei einer solchen Mutation vergessen s&auml;mtliche Redundanzen mit zu ver&auml;ndern, dann entstehen widerspr&uuml;chliche, inkonsistente Datens&auml;tze, die man als Anomalien
bezeichnet.
2:
Normalisieren
Das Normalisieren einer Relation hat die Ziele:
•
•
•
Redundanzfreiheit
keine zusammengesetzten Attribute
keine transitiven Abh&auml;ngigkeiten
Geben Sie an, an wo die Ziele in der folgenden Tabelle verletzt sind und f&uuml;hren Sie die
Normalisierung bis zur dritten Normalform durch.
L&ouml;sungsvorschlag:
Nach Beseitigung mehrfach besetzter Felder und zusammengesetzter Attribute:
Aufteilung in mehrere Tabellen:
Die Zuordnung des Attributs ‚Gruppe’ ist nicht klar. Hat jeder Artikel eine besondere Rabattgruppe, jeder Kunde oder jede Position eines Auftrags? Hier wird angenommen, dass die Rabattgruppe an den Artikel gebunden ist. In der Tabelle fehlen noch Angaben &uuml;ber die Artikel: Bezeichnung Preis usw. Die lassen sich aber ohne Weiteres in der Tabelle Artikel unterbringen.
9
Entity-Relationship-Diagramm (ER-Diagramm)
3:
Referentielle Integrit&auml;t
B&uuml;cherei
ISBN-Nr
0-201-14192-2
Autoren
Date, Ch.
3-89319-117-8
Finkenzeller, H.
Kracke, U.
Unterstein, M.
Melton, J.
Simon, A.
1-55860-245-3
Titel
The Relational Model for Database
Management: Version 2
Systematischer Einsatz von SQL-Oracle
Jahr
1990
Seiten
538
1989
494
Understanding the new SQL
1993
536
a) Welche Normalformen sind verletzt?
b) Erzeugen Sie ein &auml;quivalentes System in normalisierter Form.
4:
Kurs-Noten
Matrikel
30321
30321
30346
30346
30346
30378
Student
Meyer, J.
Meyer, J.
Ahrens, H.
Ahrens, H.
Ahrens, H.
Knudsen, K.
Kurs-Nr
706S6
715S4
715S4
706S6
713S5
706S6
Kurs-Titel
Datenbanksysteme
Software-Engineering
Software-Engineering
Datenbanksysteme
relationale u. funktionale Programmierung
Datenbanksysteme
Note
1,0
1,7
3,0
2,0
1,7
2,0
a) Welche Normalformen sind verletzt?
b) Erzeugen Sie ein &auml;quivalentes System in normalisierter Form.
Referentielle Integrit&auml;t
Durch die Normalisierung und die anschlie&szlig;ende Verkn&uuml;pfung haben wir uns das Problem der Redundanz
und der damit verbundenen Mutationsanomalien vom Halse geschafft, aber daf&uuml;r ein neues Problem erzeugt.
Wird beispielsweise ein Freund aus der Freundetabelle gel&ouml;scht und nicht alle Datens&auml;tze aus den Kindtabellen, die in irgendeiner Beziehung zu diesem Datensatz stehen, dann entstehen Waisen also „elternlose“ Datens&auml;tze. Die referentielle Integrit&auml;t ist damit nicht mehr gegeben. Ganz schlimm kann so etwas werden,
wenn irgendwann sp&auml;ter einmal der gel&ouml;schte Schl&uuml;ssel f&uuml;r einen anderen Freund neu vergeben wurde. Das
Wiederaufleben des Schl&uuml;ssels stellt auch die Referenzen wieder her. Die m&ouml;glichen Folgen k&ouml;nnen Sie sich
leicht selbst ausmalen! Viele Datenbanksysteme haben Mechanismen eingebaut, die die referentielle Integrit&auml;t gew&auml;hrleisten k&ouml;nnen.
Entity-Relationship-Diagramm (ER-Diagramm)
Der Entwurf einer Datenbank kann wie eben gesehen von den Daten her ausgehen, wobei oft eine vorhandene, gewachsene Tabelle als Ausgangsbasis dient. Wenn man aber eine Datenbank komplett neu konzipiert,
empfiehlt es sich, davon auszugehen in welchen Beziehungen die Daten untereinander stehen. Aus diesem
Entwurf werden direkt die Tabellen abgeleitet, die dann h&auml;ufig bereits normiert sind. Es ist aber unerl&auml;sslich,
die entstandenen Tabellen auf die Einhaltung der Normalit&auml;tsregeln zu &uuml;berpr&uuml;fen.
Entitytyp (Objekt)
Eine Entity ist vergleichbar mit einer Tabelle. Die Daten eines bestimmten Kunden sind eine Entity. Eine
Datenbank besteht demnach aus mehreren Entities, die innerhalb einer Tabelle jeweils die gleiche Struktur
aufweisen.
10
Entity-Relationship-Diagramm (ER-Diagramm)
Referentielle Integrit&auml;t
Attribute
Attribute sind die „Daten“ der Entity. Im Datenmodell einer Tabelle sind dies die Datenfelder in einer Spalte.
Wenn die Entity ein eindeutig identifizierbares Attribut besitzt (z.B. Kundennummer), dann wird es unterstrichen.
5:
Versandhandel 1
Zeichnen Sie das ER-Diagramm f&uuml;r folgende Entities:
ENTITY TYPE Artikel
DESCRIPTION Beschreibung f&uuml;r die verschiedenen Artikel. Artikel ist hier als
Kategorie einer Ware gemeint, die in einer gr&ouml;&szlig;eren Menge auf Lager vorhanden sein kann.
ATTRIBUTES
Artikel-Nr
CHAR(4);
Bezeichnung
STRING;
Listenpreis
Geldbetrag;
Bestand
CARDINAL;
Mindestbestand
CARDINAL;
Verpackung
STRING;
Lagerplatz
Lager;
Kann-wegfallen
BOOLEAN;
keine Nachbestellung, wenn Lager ger&auml;umt
Bestellvorschlag
Zeitpunkt;
vom System automatisch erzeugt
Nachbestellung
Zeitpunkt;
tats&auml;chliche Bestellung
Nachbestellmenge
CARDINAL;
KEY (Artikel Nr)
END
Artikel;
ENTITY TYPE MWSt-Satz
DESCRIPTION Mehrwertsteuersatz. Da der Mehrwertsteuersatz gesetzlichen &Auml;nderungen unterliegt und da es mehrere M&ouml;glichkeiten der Versteuerung gibt
(keine MWSt, reduzierte MWSt, volle MWSt), werden die verschiedenen S&auml;tze in dieser Tabelle beschrieben.
ATTRIBUTES
MwSt
CARDINAL;
Prozent
NUMERIC(3,1);
Beschreibung
STRING;
KEY (MWSt)
END MWSt-Satz;
ENTITY TYPE Kunde
DESCRIPTION Beschreibung der relevanten Kundendaten.
ATTRIBUTES
Kunden-Nr
CARDINAL;
11
Entity-Relationship-Diagramm (ER-Diagramm)
Referentielle Integrit&auml;t
Status
(Stammkunde, Werbung, Gelegenheitskunde)
Name
STRING;
Stra&szlig;e
Stra&szlig;en;
PLZ
Postleitzahlen;
Ort
Orte;
letzte-Bestellung
DATE;
letzte-Werbeaktion DATE;
KEY (Kunden-Nr)
END Kunde;
ENTITY TYPE Bestellung
DESCRIPTION Dieser Entit&auml;tstyp beschreibt einen Bestellvorgang von der Bestell-Aufnahme bis zur Lieferung. Allerdings wird eine stornierte Bestellung aus der Datenbank gel&ouml;scht, ohne dass dieser Vorgang in der Datenbank vermerkt wird.
ATTRIBUTES
Bestell-Nr
CARDINAL;
Bestell-Datum
DATE;
Liefer-Datum
DATE;
Rechnungsbetrag
Geldbetrag;
KEY (Bestell-Nr)
END Bestellung;
Abh&auml;ngige (weak) Entities
Um Speicherplatz zu sparen, kann man Entities als von einer anderen Entity abh&auml;ngig deklarieren. Die weak
Entity „erbt“ dann gewisserma&szlig;en das Schl&uuml;sselattribut von der strong Entity. Man kann das so sehen, als ob
an eine Karteikarte (manchmal) ein weiteres Blatt angeheftet wird. In der graphischen Darstellung wird diese
Entity mit einem doppelten Rahmen gezeichnet. Wichtig ist, dass diese Entit&auml;t mit verschwinden muss, wenn
die zugeh&ouml;rige „Hauptindentit&auml;t“ gel&ouml;scht wird.
6:
Versandhandel 2
Zeichnen Sie die vom Kunden abh&auml;ngige Entity „Girokonto“ in das Diagramm der vorigen Aufgabe ein.
ENTITY TYPE Girokonto
DEPENDENT ON Kunde
DESCRIPTION Bankverbindungen der Kunden, die eine Einzugserm&auml;chtigung erteilt
haben. Falls (ausnahmsweise) mehrere Kunden dieselbe Bankverbindung belasten, ist das Konto mehrfach aufzunehmen, da andernfalls die Konto-&Auml;nderung eines Kunden problematische Konsequenzen h&auml;tte.
ATTRIBUTES
Konto-Inhaber String;
BLZ
Bankleitzahlen;
Konto-Nr
String(10);
END Girokonto;
L&ouml;sung:
12
Entity-Relationship-Diagramm (ER-Diagramm)
Referentielle Integrit&auml;t
Relation
Das ER-Modell kennzeichnet die Beziehungen der Entities (die Relationen) durch eine Raute. Die beteiligten
Entit&auml;ten haben eine „Rolle“.
7:
Schule
Erstellen Sie ein Entity-Relationship-Diagramm mit folgenden Begriffen:
Entities:
Sch&uuml;ler, Schule, Ort
Attribute:
PLZ, Einwohnerzahl, Ortsname, Sch&uuml;lername, Schulname, Sch&uuml;lernummer,
Schulart, Sch&uuml;leranzahl, Vorname, Klasse, Schulnummer (unterstreichen des
eindeutigen Attributs nicht vergessen!)
Relationen:
wohnt, besucht
L&ouml;sung:
13
Entity-Relationship-Diagramm (ER-Diagramm)
Referentielle Integrit&auml;t
Kardinalit&auml;t
Schauen Sie sich wieder die Aufgabe „Versandhandel 1“ an. Beziehungen bestehen hier zwischen jeweils einer Bestellung und genau einem
Kunden und zwischen jeweils einer Bestellung
und mehreren Artikeln. Man kann bei den Beziehungen vier F&auml;lle unterscheiden:
0..1
null-one
0..*
null-many
1..1
one-one
1..*
one-many
Der f&uuml;nfte Fall: n..m many-many kann stets
aufgel&ouml;st werden indem man die eine Relation
in zwei Relationen auftrennt.
Die Darstellung in einem vereinfachten ER-Diagramm
In einem vereinfachten ERDiagramm werden die entities als
K&auml;sten dargestellt und die Beziehungen als Striche mit Beschriftung und nicht als Raute. Die
weak (abh&auml;ngigen) entities werden normalerweise unter oder
neben den strong (unabh&auml;ngigen)
entities aufgelistet. Die Beziehungen werden ebenfalls n&auml;her beschrieben, wenn es dazu Daten
gibt. In diesem Fall wird hier der
Vorgang des Ausleihens n&auml;her
durch Ausleihdatum und R&uuml;ckgabedatum beschrieben. Die Beziehungen werden an dieser Stelle
bereits in 1:N-, M:N- und 1:1Beziehungen festgelegt. Beim ER-Modell ist der Vorgang, das Diagramm zu erstellen, wohl der aufwendigste und manchmal auch komplizierteste Schritt. Ein Fehler an dieser Stelle bringt das ganze Modell zum
Scheitern. Daher ist dieser Schritt sehr gr&uuml;ndlich durchzuf&uuml;hren.
Bitte beachten Sie, da&szlig; es sich nach wie vor um keine Tabellen und deren Beziehnungen handelt, sondern
um die Darstellung eines ER-Modells.
Die obige M:N-Beziehung zwischen Kunde und Kassette ergibt sich daraus, da&szlig; ein Kunde mehrere Kassetten leihen kann, eine Kassette aber an mehrere Kunden verliehen werden kann.
8:
Versandhandel 3
F&uuml;gen Sie die Relationen zur Aufgabe Versandhandel1 hinzu.
RELATIONSHIP TYPE Bestellung-Kunde
ROLES
Bestellung Bestellung OPTIONAL MULTIPLE;
kunde
Kunden
MANDATORY UNIQUE;
END Bestellung-Kunden;
RELATIONSHIP TYPE Position
ROLES
14
Entity-Relationship-Diagramm (ER-Diagramm)
Bestellung
artikel
ATTRIBUTES
menge
preis
END Position;
Bestellung
Artikel
F&uuml;nf Schritte zum ER-Modell
OPTIONAL MULTIPLE;
MANDATORY MULTIPLE;
CARDINAL;
Geldbetrag
RELATIONSHIP TYPE Kunde-Girokonto
ROLES
kunde
Kunde
MANDATORY UNIOUE;
konto
Girokonto
OPTIONAL UNIQUE;
END
Kunde-Girokonto,
RELATIONSHIP TYPE Artikel-Mwstsatz
ROLES
artikel
Artikel
OPTIONAL MULTIPLE;
mwstsatz
Mwstsatz
MANDATORY UNIQUE;
END Artikel-Mwstsatz;
L&ouml;sung
Der Beziehungstyp der zweiten Relation enth&auml;lt noch ein Attribut &uuml;ber die Anzahl der georderten Artikel und
den Gesamtpreis f&uuml;r diese Artikel (dieser kann sp&auml;ter nicht mehr aus den ArtikelDaten rekonstruiert werden,
da der Preis sich ge&auml;ndert haben kann; au&szlig;erdem sind gegebenenfalls auch Sonderkonditionen m&ouml;glich).
F&uuml;nf Schritte zum ER-Modell
Ein ER-Modell wird in folgenden Schritten erstellt:
1. Datenobjekte werden aus der Beschreibung des Systems identifiziert. Dazu m&uuml;ssen f&uuml;r jedes Datenobjekt die relevanten Attribute bestimmt werden. Die Attribute sollten einfache, nichtstrukturierte
Eigenschaften darstellen, die sich mit nichtstrukturierten Werten belegen lassen.
2. Entit&auml;ten werden teilweise direkt aus der Beschreibung des Systems identifiziert, teilweise durch
Verallgemeinerung der gruppierten Datenobjekte. Jede Entit&auml;t modelliert genau ein Konzept. Es
handelt sich um alle im System vorhandenen physischen Elemente (z.B. Lehrer, Klasse).
3. Die Menge der Entit&auml;ten wird dadurch erweitert, da&szlig; auch abstrakte (nicht sichtbare) Entit&auml;ten (z.B.
Projekt, Budget) identifiziert werden.
4. Die Beziehungen werden identifiziert. Es wird ermittelt, in welche Wechselwirkungen die Datenobjekte einzelner Entit&auml;ten treten. Die gefundenen Wechselwirkungen werden durch Tupel der Beziehungen modelliert.
5. Die Kardinalit&auml;t der Beziehungen wird untersucht und bestimmt.
15
&Uuml;berlegungen beim Entwurf eines Datenmodells*)
9:
ER-Diagramm
CD-Verleih
Gehen Sie zur&uuml;ck zum Beispiel der normalisierten Tabellen des CD-Verleihs auf der Seite 7.
Zeichnen Sie das ER-Diagramm f&uuml;r diese Tabellen. Beachten Sie, dass die Tabellenfelder mit
den Fremdschl&uuml;sseln nicht als Attribute im ER-Diagramm erscheinen! Setzen Sie die Tabelle
tbVerleih als Relation und nicht als Entity um.
&Uuml;berlegungen beim Entwurf eines Datenmodells*)
Ergebnis der folgenden &Uuml;berlegungen soll
eine Verwaltung f&uuml;r eine private B&uuml;chersammlung sein. Bei B&uuml;chern ist es sicher
sinnvoll, Daten wie Autor, Titel, Untertitel,
Verlag und Sprache zu erfassen und vielleicht noch Felder f&uuml;r Kaufdatum, Erscheinungsjahr und Bemerkungen vorzusehen.
Enth&auml;lt die Sammlung vorwiegend Sachb&uuml;cher. hilft eine Klassifizierung nach Sachgebieten, Belletristik k&ouml;nnte man nach Genre
sortieren. Wer es bunt mag, spendiert noch
ein Feld, das einen Scan des Einbands aufnimmt. Bei verliehenen B&uuml;chern sind der
Entleiher, Verleih- und versprochenes R&uuml;ck,gabedatum zu erfassen.
Bei der Festlegung, wie die Tabellen der zu
erstellenden Datenbank aufgebaut sein sollen, ist es oft hilfreich, einfach erst einmal
die Hauptw&ouml;rter aus einer solchen groben
Beschreibung herauszuschreiben, zu gruppieren und sich zu &uuml;berlegen. in welcher
Beziehung sie stehen. Zwei Tabellen kristallisieren sich sofort heraus: 'Buch' und 'Entleiher'. Bei der Entscheidung, welche Attribute als Felder beispielsweise der Tabelle
'Buch' zu realisieren sind und f&uuml;r welche
eine verkn&uuml;pfte Tabelle zu spendieren ist,
helfen Fragen wie: 'Hat dieses Attribut jedes Buch, die meisten oder nur wenige?', 'Kann es mehrfach bei
einem Buch vorhanden sein?' und 'Ist es atomar, also durch eine Zahl, einen kurzen Text oder &Auml;hnliches
darstellbar, oder besteht es aus weiteren Unterattributen?' Jedes Buch hat einen Titel und eine Sprache, ist in
einem bestimmten Jahr in einem Verlag erschienen und zu einem Zeitpunkt gekauft worden. Einige B&uuml;cher
haben einen Untertitel, f&uuml;r einige liegt ein Umschlag-Scan vor, und zu einigen f&auml;llt mir sofort eine Bemerkung ein. B&uuml;cher haben einen oder mehrere Autoren und geh&ouml;ren zu einer oder mehreren Kategorien. Ein
Verlag ist gekennzeichnet durch Name, Land und Ort, ein Autor durch Vor- und Nachname. Letzteres gilt
auch f&uuml;r einen Entleiher, der au&szlig;erdem eine Telefonnummer und eine E-Mail-Adresse hat. Eine Person kann
sich zu unterschiedlichen Zeitpunkten mehrere B&uuml;cher leihen und f&uuml;r jedes ein anderes R&uuml;ckgabedatum vereinbaren; ein Buch kann nur an eine Person verliehen sein.
ER-Diagramm
Die geschilderten Zusammenh&auml;nge kann man grafisch wie in der Abbildung auf dieser Seite darstellen. Die
Rechtecke kennzeichnen dabei Objekte aus der realen Welt, die abgerundeten K&auml;sten ihre Attribute. Rauten
stehen f&uuml;r Beziehungen zwischen Objekten; an den Enden ihrer Verbindungslinien steht ihre sogenannte
Kardinalit&auml;t. die 1:n-, m:n- und 1:1-Beziehungen auseinander h&auml;lt.
*)
(nach c’t Heft 18/2001)
16
&Uuml;berlegungen beim Entwurf eines Datenmodells*)
Tabellen erstellen
Tabellen erstellen
Aus einer solchen Grafik l&auml;sst sich unmittelbar die Tabellenstruktur der Datenbank ableiten: Rechtecke werden zu Tabellen, die dazugeh&ouml;rigen Attribute zu deren Feldern. Die Tabelle am Ende jeder 1:n-Beziehung
bekommt ein weiteres Feld mit einem entsprechenden Fremdschl&uuml;ssel. Zus&auml;tzliche Tabellen sind f&uuml;r
m:n-Beziehungen vorzusehen, neben eventuellen Attributen besitzen sie ein Feld f&uuml;r jede an der Relation
beteiligte Tabelle.
Relationen mit Attributen
Einen Sonderfall stellt in diesem Beispiel die 1:n-Relation 'hat geliehen’ dar, die man eigentlich ohne zus&auml;tzliche Tabelle realisieren k&ouml;nnte. Aber wo bringt man Entleih- und geplantes R&uuml;ckgabedatum unter? Speichert man sie in der Buch-Tabelle, verschwendet man Speicherplatz. denn die m&uuml;sste diese Felder f&uuml;r jedes
Buch bereithalten, obwohl in der Regel nur wenige B&uuml;cher verliehen sind. Schl&auml;gt man sie andererseits der
Entleiher-Tabelle zu, m&uuml;sste man Name, Telefonnummer und E-Mail-Adresse des Empf&auml;ngers jedes Mal
neu ausf&uuml;llen, auch wenn sich ein und dieselbe Person mehrere B&uuml;cher leiht; au&szlig;erdem gehen diese Informationen verloren, wenn man den entsprechenden Eintrag l&ouml;scht, weil jemand ein Buch zur&uuml;ckgibt. Die beste
L&ouml;sung ist hier wohl, eine Extra-Tabelle 'Leihe' zu spendieren, die die Felder 'Buch' und 'Entleiher’ als
Fremdschl&uuml;ssel sowie die beiden Datumsfelder enth&auml;lt.
Datentypen
Die Datentypen der einzelnen Tabellenfelder ergeben sich meist intuitiv. Zu beachten ist, dass Frerndschl&uuml;sselfelder denselben Typ besitzen m&uuml;ssen wie das Prim&auml;rschl&uuml;sselfeld, auf das sie verweisen. Ansonsten sollte man Zahlentypen nur dann verwenden, wenn man die Werte f&uuml;r Berechnungen verwenden oder Datens&auml;tze numerisch sortieren will - f&uuml;r deutsche Postleitzahlen ist beispielsweise ein f&uuml;nf Zeichen fassendes Textfeld angebrachter, denn sonst erscheint Leipzig (04105) nur vierstellig. Bei der Dimensionierung von Textfeldem sollte man nicht zu sparsam sein, denn bei den meisten modernen Datenbankprogrammen belegen
Datens&auml;tze auf der Festplatte nur die Anzahl von Zeichen, die tats&auml;chlich benutzt sind. Lediglich &auml;ltere Datenbankformate wie dBase arbeiten mit festen Feldl&auml;ngen und verbraten f&uuml;r ein Textfeld, das 100 Zeichen
fassen darf, in jedem Datensatz 100 Bytes, auch wenn dort nur 'Der Herr der Ringe' steht.
Auf dem Index: die Schl&uuml;sselfelder
F&uuml;r die Prim&auml;rschl&uuml;ssel der Tabellen kommen in diesem Beispiel eigentlich nur automatisch hochgez&auml;hlte
laufende Nummern in Frage, es gibt bei keinem der Objekte Attribute, die es eindeutig kennzeichnen. Man
k&ouml;nnte auf die Idee kommen, zu jedem Buch seine ISBN zu speichern und als Prim&auml;rschl&uuml;ssel zu verwenden, das kann aber zu Problemen f&uuml;hren, wenn man mehrere B&auml;nde eines Werkes getrennt verwalten will
oder wenn etwa ein Versandhauskatalog seinen Platz in der Bibliothek finden soll.
F&uuml;r Sekund&auml;rindizes sind selbstverst&auml;ndlich erst einmal alle Fremdschl&uuml;sselfelder vorzusehen, damit man
beispielsweise schnell von einem Verlag zu den B&uuml;chern gelangt, die er herausgegeben hat. Welche weiteren
Felder man indiziert, ist weitgehend Geschmackssache und h&auml;ngt davon ab, wonach man h&auml;ufig suchen und
sortieren will. Folgendes ist dabei allerdings zu beachten: Man wird die Datenbank wohl selten benutzen, um
beispielsweise herauszufinden, wie ein Autor namens Brecht mit Vornamen hei&szlig;t, also f&uuml;r eine Suche nur in
der Autoren-Tabelle. Allerdings ist eine Anfrage wie 'alle B&uuml;cher eines Autors, der mit Nachnamen Brecht
hei&szlig;t' durchaus sinnvoll. Daher sollte die Datenbank einen Sekund&auml;rindex &uuml;ber die Nachnamen der Autoren
enthalten. Dasselbe gilt f&uuml;r die Namen von Verlagen und Entleihern.
Datenbankanwendung fertigstellen
Mit dem Entwurf des Datenmodells und dem Anlegen der Tabellen ist ein Gro&szlig;teil der Handarbeit auf dem
Weg zu einer Datenbankanwendung erledigt. Was fehlt, sind Eingabeund Suchmasken, mit denen man den
Datenbestand bequem pflegen und durchforschen kann, sowie Formulardefinitionen zum Drucken von Berichten. Die meisten modernen Datenbankprogramme bieten daf&uuml;r aber recht brauchbare Vorlagen oder Assistenten an, die zumindest ein funktionierendes Grundger&uuml;st erstellen.
17
Umsetzung ER-Diagramm in Tabellen
Datenbankanwendung fertigstellen
Der Rest ist Kosmetik. So kann man sich die Dateneingabe dadurch erleichtern, dass die Datenbank beim
Anlegen eines neues Datensatzes bestimmte Felder mit sinnvollen Vorgaben belegt, etwa 'Heute' als Kaufdatum und 'Deutsch' als Sprache beim Eintragen eines neuen Buches. Wer zus&auml;tzlich Lust versp&uuml;rt, sich in die
Skript- oder Programmiersprache eines Datenbanksystems einzuarbeiten, er&ouml;ffnet sich die M&ouml;glichkeit, weitere Funktionen um die eigentliche Datenbank herum zu programmieren, etwa das automatische Versenden
von ErinnerungsMails an s&auml;umige Entleiher.
Umsetzung ER-Diagramm in Tabellen
Die nachfolgenden Regeln sind f&uuml;r eine schrittweise Erstellung eines normalisierten Datenmodells gedacht.
Die Verfahrensweise soll an folgendem Beispiel erl&auml;utert werden. Ein Kunde leiht Video-Kassetten. Jede
Kassette hat einen Titel und ist in einer Kategorie eingeteilt. Die Kategorien haben unterschiedliche Leihpreise.
Die erste Regel:
Wertetypen werden 1:1 auf Datentypen abgebildet. Hier wird also festgelegt, dass z. B. das Feld &raquo;Name&laquo; ein
Textfeld ist und das Geburtsdatum in einem Datumsfeld gespeichert wird. Es werden die Datentypen festgelegt.
Durch diesen Schritt entstehen die Entit&auml;ten und deren Attribute
Die zweite Regel:
Ein Gegenstand (Entity) wird mit all seinen Attributen zu jeweils einer Tabelle zusammengefasst.
Durch die zweite Regel erfolgt also die Umsetzung in Tabellen. Die strong entities werden mit den dazugeh&ouml;rigen weak entites in einer Tabelle abgelegt. Ab diesem Punkt existieren die ersten Tabellen in unserem
Modell und wir haben eine gute Basis f&uuml;r die weitere Regel geschaffen. Die Kassettentabelle hat im Moment
noch gar kein Feld, aber wir sind noch nicht fertig.
18
Umsetzung ER-Diagramm in Tabellen
Datenbankanwendung fertigstellen
Die dritte Regel:
Jeder Gegenstand erh&auml;lt ein 1:1-Attribut als Identifikator (Prim&auml;rschl&uuml;ssel), soweit noch nicht vorhanden.
Der notwendige Prim&auml;rschl&uuml;ssel in einem relationalen Datenbankmodell wurde bereits besprochen. Als Prim&auml;rschl&uuml;ssel kann hier ein vorhandenes Datenfeld benutzt werden, oder es wird ein Feld hinzugef&uuml;gt (z.B.
eine &raquo;Kundennummer&laquo;). Nach diesem Schritt haben wir also alle Objekte untergebracht, und es sind nur
noch die Beziehungen zu definieren.
Die vierte Regel:
Jede M:N-Beziehung wird in einer eigenen Tabelle abgebildet. Diese Tabelle enth&auml;lt die Prim&auml;rschl&uuml;ssel der
beteiligten Entit&auml;ten und die Datenfelder, die die Beziehung selbst beschreiben.
1:N-Beziehungen werden durch Einf&uuml;gen des Prim&auml;rschl&uuml;ssels der 1-Tabelle in der N-Tabelle als Fremdschl&uuml;ssel realisiert.
Dieser Schritt l&ouml;st unsere verbleibenden Beziehungen auf, so dass diese in Tabellen realisiert werden k&ouml;nnen.
L&ouml;sung:
19
Zusammenfassung
Datenbankanwendung fertigstellen
Zusammenfassung
10:
1.
Das Datenmodell wird mit keinerlei Blickpunkt auf die Programmierung erstellt. Es ist einzig
und allein die Umsetzung der realen Welt auf die Ebene der Datenverarbeitung. Programmiertechnische &Uuml;berlegungen haben an dieser Stelle nichts zu suchen. Diese Regel gilt jedoch nur,
wenn Aspekte der Geschwindigkeit unber&uuml;cksichtigt bleiben k&ouml;nnen. Bei DataWarehouseAnwendungen ist es oft erforderlich, da&szlig; redundante Daten angelegt werden, um die Zugriffszeiten auf Tabellen und Datenbanken zu reduzieren. In diesem Fall wird nach der dritten Normalform oder beim ER-Modell nach der vierten Regel das Modell mit dem Gesichtspunkt der optimalen Geschwindigkeit modifiziert.
2.
Das Normalisieren nach Codd und das ER-Modell sind zwei getrennte, unabh&auml;ngig voneinander
existierende Methoden. Nat&uuml;rlich kann ein Datenmodell, das nach Codd erstellt wurde, immer
mit einem ER-Diagramm dargestellt werden, da beide Verfahren das relationale Datenbanksystem zum Ziel haben.
3.
Bevorzugen Sie das Datenbank-Design mit dem ER-Modell, pr&uuml;fen Sie wenigstens die Werte
auf Abh&auml;ngigkeit, wie es bei der Methode von Codd erfolgt. Dies erspart Ihnen oft nachtr&auml;gliches &Auml;ndern im Datenmodell.
4.
Das Modellieren der Daten in Tabellen ist ein nicht zu untersch&auml;tzender Punkt. Fehler beim Modellieren kosten zu einem sp&auml;teren Zeitpunkt enorm viel Zeit. Sollen &Auml;nderungen an dem Modell
durchgef&uuml;hrt werden, m&uuml;ssen diese &Auml;nderungen durch alle Stufen der benutzten Methode durchgezogen werden. Es f&uuml;hrt zu Fehlern, wenn versucht wird, die dritte Normalform, die ja das Ergebnis beider besprochener Verfahren ist, abzu&auml;ndern und anzupassen.
5.
&Auml;nderungen an einem fertigen Datenmodell sind immer ein Hinweis darauf, da&szlig; die Tabellen
nicht korrekt normalisiert worden sind. Die Ursache ist meist, da&szlig; in der Anwendungsanalyse
nicht gr&uuml;ndlich genug gearbeitet wurde. Diese Phase kann bis zu 30% des Aufwandes f&uuml;r die Erstellung der gesamten Anwendung einnehmen, und man sollte sich diese Zeit unbedingt nehmen.
6.
Tabellen m&uuml;ssen in einer bestimmten Reihenfolge gef&uuml;llt werden. Die Tabellen ohne Fremdschl&uuml;ssel m&uuml;ssen zuerst mit Daten gef&uuml;llt werden. In unserem Beispiel kann keine Kassette erfa&szlig;t werden, wenn keine Titel und keine Kategorien mit ihren Preisen eingegeben wurden. Es ist
die Aufgabe der Programmierung, dies abzufangen und den Benutzer hier richtig zu leiten.
ER-Diagramm1
Erstellen Sie f&uuml;r das Beispiel in der dritten Normalform (Seite 7) das ER-Diagramm.
11:
Kardinalit&auml;ten
Untersuchen Sie, welche Kardinalit&auml;ten in folgenden Beziehungen m&ouml;glich sind - geben Sie daf&uuml;r gegebenenfalls Randbedingungen vor:
12:
a)
Postleitzahlen Orte (in Deutschland)
b)
Steuernummer Steuerpflichtiger
c)
Kind
↔
Vater
d)
Kind
↔
Mutter
e)
Kind
↔
Elternteil
ER-Diagramm2
Erstellen Sie f&uuml;r die Beziehungen der vorigen Aufgabe ER-Diagramme. Beachten Sie dabei,
dass wir in c), d), und e) in der Aufgabenstellung nicht die Entit&auml;tenmengen angegeben haben
(die auf beiden Seiten jeweils &raquo;Person&laquo; ist), sondern Rollen.
20
Zusammenfassung
13:
Datenbankanwendung fertigstellen
Schl&uuml;sselattribute
Erstellen Sie f&uuml;r die folgenden Angaben jeweils ein Entity-Relationship-Diagramm und bestimmen Sie die Schl&uuml;sselattribute.
14:
a)
Es gibt Personen, B&uuml;cher und Verlage. Jedes Buch hat einen oder mehrere Autoren.
Jedes Buch kann zu gegebener Zeit von h&ouml;chstens einem Ausleiher ausgeliehen werden. Ein Ausleiher kann mehrere B&uuml;cher leihen, Ein Buch ist von einem Verlag.
b)
Ein Auto ist von einem Hersteller. Es hat zum Zeitpunkt der Herstellung keinen Halter,
sonst h&ouml;chstens einen Halter. Auf einen Halter k&ouml;nnen mehrere Autos eingetragen
sein.
c)
In einer Bibliothek gibt es &raquo;Buchtitel&laquo; und &raquo;Buchexemplare&laquo;. F&uuml;r einen Buchtitel k&ouml;nnen mehrere Exemplare vorhanden sein, jedoch immer mindestens eins. Ausleiher leihen Buchexemplare. Ausleiher k&ouml;nnen Buchtitel vormerken lassen. Ausleiher und Autoren sind Personen.
d)
Personen sind Studenten oder Professoren. Jede Vorlesung wird von einem Professor
gehalten. Ein Professor h&auml;lt mehrere Vorlesungen. Ein Student besucht mehrere Vorlesungen. Eine Vorlesung wird von mehreren Studenten besucht, aber erst nach Semesterbeginn steht fest, von wem. Ein Professor empfiehlt f&uuml;r eine bestimmte Vorlesung ein
Buch.
Segelt&ouml;rn
Eine Yachtagentur will die T&ouml;rns (Touren) ihrer Segelyachten mit einer Datenbank verwalten.
Dabei geht es darum, die Mitfahrer ebenso zu erfassen wie die im Lauf der Tour angelaufenen
H&auml;fen. Es gelten folgende Regeln:
1.
Eine Crew setzt sich aus mehreren Mitfahrern zusammen. Mitfahrer m&uuml;ssen an keiner
Crew teilnehmen, k&ouml;nnen aber auch an mehreren Crews beteiligt sein.
2.
Eine Crew bezieht sich immer auf eine Tour. W&auml;hrend einer Tour kann aber die Crew
wechseln.
3.
F&uuml;r jede Tour gibt es einen Kapit&auml;n. Ein Kapit&auml;n kann nat&uuml;rlich an mehreren Touren
teilnehmen,
4.
Kapit&auml;ne und Mitfahrer sind Personen.
5.
Eine Tour wird immer von einer Yacht gefahren. Meistens &uuml;berlebt eine Yacht die erste
Fahrt. Dann kann sie an weiteren Touren teilnehmen.
6.
W&auml;hrend einer Tour l&auml;uft eine Yacht mehrere H&auml;fen an.
Modellieren Sie grafisch die Entit&auml;tenmengen- und Beziehungsmengen. Legen Sie Schl&uuml;sselattribute und die wichtigsten anderen Attribute fest.
15:
Arztpraxis1
Nichts ist ern&uuml;chternder als die Wirklichkeit! Nach mehrmaligem Umdrehen Ihrer leeren Taschen
beschlie&szlig;en Sie, den am Uni-Aushang angek&uuml;ndigten Hilfsjob bei den Medizinern (Seufz!) als
Systemdesigner im Projekt &quot;Diabetes&quot; bei Chefarzt Prof. Dr. Dr. hc. mult. Gerngro&szlig; anzunehmen. Die erste Begegnung verl&auml;uft wie folgt: Sie betreten nach langem Warten das Vorzimmer
des Chefs. Sie betreten nach langem Warten das Arbeitszimmer des Chefs. Nach langem Warten kommt der Chef herein, im gr&uuml;nen Kittel (Vorsicht: Klischee!), und reicht Ihnen die plastikbehandschuhte Hand. Nun geht alles ziemlich rasch:
&quot;Sch&ouml;n, dass Sie den Job bei uns angenommen haben. Unsere Sekret&auml;rin wird nachher mit Ihnen den Papierkram erledigen. Projektgelder sind genug da. Also, n&auml;chste Woche brauche ich
einen Datenbankentwurf f&uuml;r unsere Diabetesambulanz. Nun ... Die Diabetikerbetreuung l&auml;uft bei
1
Universit&auml;t Ulm -Sektion Angewandte Informationsverarbeitung 3.&Uuml;bungsblatt (09.11.98 bis
16.11.98) zur Vorlesung Software Engineering Praxis (WS 98/99)
21
Zusammenfassung
Datenbankanwendung fertigstellen
uns in etwa wie folgt ab: Also, &auml;hh, die Patienten kommen halt regelm&auml;&szlig;ig zur Untersuchung. Ja
- station&auml;r zur Stoffwechseleinstellung, so alle zwei Jahre. Dann aber regelm&auml;&szlig;ig alle Vierteljahr
zur ambulanten Untersuchung. Die station&auml;re lassen wir mal weg! Ambulant - ja, das ist wegen
des neuen EBM-Katalogs und des GSG III prim&auml;r dringend. Bis n&auml;chste Woche, das sagte ich ja
schon. Die Patienten werden also ambulant untersucht ... Auf der Akte sehe ich immer, wen ich
vor mir habe. Alter, Geschlecht und so. Wohnort... Na ja, alle unver&auml;nderlichen Merkmale eines
Menschen halt, Stammdaten sagt man glaub'. Auch den betreuenden Hausarzt vor Ort. Da
kommen viele vom gleichen Hausarzt. Da k&ouml;nnte ich Geschichten erz&auml;hlen... Jedenfalls werden
die Patienten dann untersucht. Pro Untersuchung: Gewicht, Gr&ouml;&szlig;e, Blutdruck etc. eben die ganzen antropometrischen Daten, Sie wissen! Dann halt das Labor. Ja mei, HbA1c-Wert, Kalium,
Natrium. Betreut werden die bei uns von 6 Klinik&auml;rzten. Jeder hat seine festen Patienten. Wir
sind ein eingespieltes Team. Nicht so wie an der Uni XYZ! Ach ja ... Nat&uuml;rlich wird die Insulintherapie des Patienten eingestellt. Bei uns ist die Intensivierte Therapie als Ergebnis der DCCTStudie eingef&uuml;hrt worden! Die Patienten spritzen bis zu 7 mal pro Tag. Keine festen Dosen ... Sie
wissen das sicher! Wir sagen ihnen nur wie viel Insulin pro gegessener Broteinheit sie spritzen
sollen. Und geben Ihnen mit, wie viel Broteinheiten wir empfehlen. Noch Fragen? Nein? Gut! Ich
fasse zusammen: Also bis n&auml;chste Woche der Entwurf. Patientenstammdaten, ambulante Untersuchungen. Sie machen das schon! Hals- und Beinbruch! Auf Wiedersehen! Der n&auml;chste bitte!&quot;
Sie machen das schon: Bis n&auml;chste Woche ein ER-Diagramm, bei dem Sie versuchen, das
Durcheinander des Chefarztes in sinnvolle Relationen und auf Papier zu bannen. Zu einem guten Entwurf geh&ouml;rt nat&uuml;rlich auch eine Umsetzung in Tabellen. Der Chefarzt w&uuml;rde zwar nichts
merken, da Sie aber auch Ihrem Gewissen verpflichtet sind, beschlie&szlig;en Sie, dass die Tabellen
alle der 3.Normalform gen&uuml;gen sollen. Und einem Chefarzt (hier: Tutor) legt man seinen Entwurf
nat&uuml;rlich nicht handschriftlich vor! Sondern eben mit einem netten Malprogramm gezeichnet
Der n&auml;chste bitte!
16:
Uni1
Erstellen Sie die Tabellen f&uuml;r die Universit&auml;tsverwaltung
22
Rekursive Beziehungen
Umsetzung in Relationen (Tabellen)
Rekursive Beziehungen
Beispiel:
&quot;Ein Angestellter kann einem oder mehreren anderen Angestellten vorgesetzt
sein. Wir halten das Datum der &Uuml;bernahme dieser F&uuml;hrungsfunktion fest.&quot;
N am e
A d re s s e
D a tu m
(0 ,n )
A n g e s te llte r
le ite n
(0 ,1 )
Dies ist eine bin&auml;re Beziehung und damit problemlos in eine Tabelle abbildbar:
Projektleiter
Mitarbeiter
Datum
Tern&auml;re Beziehungen
Beispiel1:2
&quot;Wir beziehen die Bestandteile &uuml;ber Artikelnummer - von einem Lieferanten f&uuml;r ein bestimmtes Projekt. Den Lieferanten identifizieren wir mit einer
Lieferantennummer, jedes Projekt verf&uuml;gt &uuml;ber einen einmaligen Codenamen. Die bestellte
Menge und den Preis halten wir
nat&uuml;rlich fest.&quot;
Lief.-N r.
M en g e
Lie fe ra n t
(0,m )
C o d en am e
P reis
beziehe n
(0 ,m )
P ro je kt
(0,m )
A rtike l-N r.
B e stan d te il
Beispiel2:3
&quot;Unser Schulungsinformationssystem soll folgende Tatbest&auml;nde abbilden: Ein Kurs kann wegen des gro&szlig;en
Andranges in mehreren Kursr&auml;umen gleichzeitig gehalten werden. Um die einzelnen Schulungen identifizieren zu k&ouml;nnen, verwenden wir daher neben den Kursnamen eindeutige KursIDs. An einem Kurs k&ouml;nnen
nat&uuml;rlich mehrere Personen teilnehmen, von denen wir Name und Addresse erfassen. Die R&auml;umlichkeiten
identifizieren wir anhand von Raumnummern; wir wollen vor allem die Rechnerausstattung festhalten.&quot;
Umsetzung in Relationen (Tabellen)
Beziehungstypen, die mehr als zwei Entit&auml;tstypen miteinander in Beziehung setzen, werden in einer eigenen
Relation abgebildet. Die Relation erh&auml;lt als Fremdschl&uuml;sselattribut die Prim&auml;rschl&uuml;ssel der Entit&auml;tstypen, die
2
Beispiel von WU Wien
3
Beispiel von WU Wien
23
Tern&auml;re Beziehungen
Umsetzung in Relationen (Tabellen)
Existiert eine strukturelle Bedingung, deren Maximalwert 1 ist, reicht es, dieses Fremdschl&uuml;sselattribut in der
abgeleiteten Relation als Prim&auml;rschl&uuml;ssel zu definieren. Ansonsten bilden alle Fremdschl&uuml;sselattribute den
Prim&auml;rschl&uuml;ssel.
Wunsch
W-Nr.
Wunsch
(0,1)
beauftragen
(0,m)
(0,m)
stationiert
Hilfsosterhase
Name
SV-Nr.
Osterhase
Name
SV-Nr.
Wunsch
W-Nr.
...
Wunsch
K-SVNr
O-SVNr
Auftrag
W-Nr.
...
H-SVNr.
O-SVNr.
Osterhase
SV.-Nr.
Name
Hilfsosterhase
SV.-Nr.
...
17:
Name
stationiert
...
Transportkompanie
Eine Transportkompanie der Schweizer Armee ben&ouml;tigt zur Abwicklung der Transportzentrale eine Datenbank. Fr&uuml;her wurde die Abwicklung in MS Excel gemacht.
Ein Unteroffizier verwaltet die Standalone Datenbank. Die Fahrauftr&auml;ge kommen per Fax oder
telefonisch. Angegeben wird bei der Bestellung die Route das Transportgut und die am Auftragsort zust&auml;ndige Person.
Der Unteroffizier w&auml;hlt anhand des Transportguts und der Strecke das passende Fahrzeug aus.
Er w&auml;hlt einen Motorfahrer, der dieses Fahrzeug f&uuml;hren kann. Der Unteroffizier nimmt die Datenbank zu Hilfe.
In der Datenbank sind alle Fahrer erfasst. Unterschieden werden sie anhand der eindeutigen
Matrikel – Nummer. Des weiteren hat ein Fahrer noch einen Dienstgrad, Namen und Vornamen. Er hat die Fahrerlaubnis einer bestimmten Kategorie.
In einer Tabelle Kategorie sind der Kategoriename und die Bezeichnung der dazugeh&ouml;rigen
erlaubten Fahrzeuge gespeichert. Von einer Kategorie k&ouml;nnen mehrere Fahrer die Erlaubnis
haben.
Die Fahrzeuge m&uuml;ssen auch erfasst werden. Jedes Fahrzeug hat eine eindeutige Nummer
24
Tern&auml;re Beziehungen
Umsetzung in Relationen (Tabellen)
Ein bestimmtes Fahrzeug geh&ouml;rt zu einem Typ. Ein Typ kann mehrere Fahrzeuge beinhalten.
Der Typ beinhaltet die Bezeichnung, H&ouml;he, Breite, das Leergewicht in Kilo, das H&ouml;chstgewicht in
Zusatzaufgaben:
Erweitern Sie den Entwurf so, dass je Fahrauftrag mehrere Artikel transportiert werden k&ouml;nnen.
Kann das System so erweitert werden, dass jeder Artikel einen anderen Zielort erh&auml;lt? Mit anderen Worten: Je Fahrauftrag m&uuml;ssen mehrere Ziele gespeichert werden.
Wenn die Fahrer alle KfZ fahren d&uuml;rften, kann die Relation „darffahren“ entfallen. Dann kann der
Entit&auml;tentyp „Klasse“ ebenfalls entfallen und das Attribut dieses Typs direkt im Entit&auml;tentyp
„Fahrzeug“ erscheinen. F&uuml;hren Sie die entsprechenden &Auml;nderungen durch.
25
Generalisierung
Umsetzung in Relationen (Tabellen)
Generalisierung
Zur weiteren Strukturierung der
Entity-Typen wird die Generalisierung eingesetzt. Hierbei werden
Eigenschaften von &auml;hnlichen Entity-Typen einem gemeinsamen
Obertyp zugeordnet. Bei dem jeweiligen Untertyp verbleiben nur
die nicht faktorisierbaren Attribute. Somit stellt der Untertyp eine
Spezialisierung des Obertyps dar.
Diese Tatsache wird durch eine
Beziehung mit dem Namen is-a
(ist ein) ausgedr&uuml;ckt, welche durch
ein Sechseck, verbunden mit gerichteten Pfeilen symbolisiert
wird. In Abbildung 2.5 sind Assistenten und Professoren jeweils
Spezialisierungen von Angestellte
und stehen daher zu diesem EntityTyp in einer is-a Beziehung.
Beispiel:
&quot;Bei unseren Angestellten unterscheiden wir zwischen den Sekret&auml;ren und
halten wir von allen fest; von den Sekret&auml;ren interessieren uns die Anschl&auml;ge
pro Minute und von den Managern
wollen wir wissen, welche Projekte sie
leiten.&quot;
26
Datenbankzugriffe mit SQL
18:
Umsetzung in Relationen (Tabellen)
Uni2
Erstellen Sie die Tabellen f&uuml;r die Universit&auml;tsverwaltung
Datenbankzugriffe mit SQL
SQL (Structured Query Language) ist eine &raquo;Programmiersprache&laquo;, die nur noch ausdr&uuml;ckt, welches Ergebnis
gew&uuml;nscht wird, und nicht wie der Rechner zu diesem Ergebnis kommt. Der Programmierer braucht sich
daher nicht um den Algorithmus der Datengewinnung zu k&uuml;mmern. Diese „nicht-prozeduralen“ Sprachen
werden zu den Programmiersprachen der vierten Generation (4GL) gez&auml;hlt (im Gegensatz zu prozeduralen
3GL-Sprachen wie Modula-2, Pascal, ADA, COBOL, BASIC, Fortran usw).
Die derzeit verbreitetste Sprache f&uuml;r relationale Datenbanken ist unbestritten SQL. Auch in Zukunft wird
diese Sprache keine Konkurrenz bekommen, was sich aus der Tatsache ableiten l&auml;&szlig;t, da&szlig; mittlerweile alle
Hersteller relationaler Datenbanken ihr System auf diese Sprache umgestellt haben.
Kleine SQL-Geschichte
Anfang der siebziger Jahre wurde in den IBM Forschungslaboratorien in San Jose, Kalifornien, ein Forschungsprojekt begonnen, das sich &raquo;System R&laquo; nannte. Es sollte die Praktizierbarkeit der relationalen Theorien untersuchen. Von den IBM-Mitarbeitern R.F. Boyce und D.D. Chamberlain wurde die Sprache
SEQUEL (sprich: siequel) entwickelt, die sp&auml;ter in SQL umbenannt wurde. Man lehnte hierbei die Syntax an
Begriffe der englischen Umgangssprache wie z.B. SELECT, FROM, WHERE an.
Seit dieser Zeit wurden von fast allen DB-Herstellern SQL-Schnittstellen zu ihren relationalen und nichtrelationalen Datenbanksystemen entwickelt. Da nun auch eine ANSI- und eine IS0-Definition der Sprache SQL
27
Datenbankzugriffe mit SQL
SQL - Befehls&uuml;bersicht
vorliegt, ist zu erwarten, da&szlig; SQL die Sprache f&uuml;r alle zuk&uuml;nftigen relationalen Datenbanksysteme werden
wird.
Ein SQL-Beispiel:
Mit der Anweisung
CREATE TABLE ARTIKEL (ART_NR SMALLINT, ART_BEZ CHAR(13),
ART_ART CHAR(11), LIEF_NR SMALLINT)
wird eine leere Tabelle mit dem Namen ARTIKEL und dem folgendem Aufbau erzeugt:
Mit dem INSERT-Befehl ist es m&ouml;glich, Daten in eine Tabelle einzuf&uuml;gen:
INSERT INTO ARTIKEL VALUES (1, 'Multisync II', 'Monitor', 1)
INSERT INTO ARTIKEL VALUES (2, 'Multisync I', 'Monitor', 2)
Mit dem SELECT-Befehl werden Daten aus der Tabelle selektiert:
SELECT ART_BEZ, ART_ART FROM ARTIKEL WHERE LIEF_NR = 2
Diese Abfrage liefert das Ergebnis:
SQL - Befehls&uuml;bersicht
Man unterscheidet f&uuml;nf SQL-Kommandoklassen:
DATA Definition Language (DDL) (Daten-Definitions-Sprache)
Dazu z&auml;hlen alle Datenbankanweisungen, mit denen die logische Struktur der Datenbank bzw. der Tabellen
der Datenbank beschrieben bzw. ver&auml;ndert wird, um Tabellen zu erzeugen, zu &auml;ndern und zu l&ouml;schen und um
Indizes zu erzeugen und zu l&ouml;schen.
DML Data Manipulation Language (Daten-Manipulations-Sprache)
Dazu z&auml;hlen alle Anweisungen an das Datenbanksystem, die dazu dienen, die Daten zu verarbeiten, um Tabelleneintr&auml;ge auszuw&auml;hlen, einzuf&uuml;gen, zu aktualisieren und zu l&ouml;schen. Hierzu geh&ouml;ren folgende Befehle:
Die SET-Klausel ist eine durch Kommas getrennte Liste von Aktualisierungsausdr&uuml;cken. Jeder Ausdruck
setzt sich aus dem Namen einer Spalte, dem Zuweisungsoperator (=) und dem Aktualisierungswert (Aktualisierungsatom) f&uuml;r diese Spalte zusammen.
Die optionale WHERE-Klausel beschr&auml;nkt Aktualisierungen auf einen Teil der Zeilen in der Tabelle. Ist
keine WHERE-Klausel angegeben, so werden alle Zeilen in der Tabelle unter Verwendung der Aktualisierungsausdr&uuml;cke der SET-Klausel aktualisiert.
Transaction Control Language (TCL) (Daten-Steuerungs-Sprache
Die Transactionskommandos stellen die Datenintegrit&auml;t sicher, indem logisch zusammenh&auml;ngende Anweisungen entweder komplett oder gar nicht ausgef&uuml;hrt werden.
28
Abfrageformulierung mit SQL
Beispiel-Datenbank
Die Befehle aus dieser Kategorie dienen dem Anwendungsentwickler bzw. dem Datenbankadministrator.
&Uuml;ber SQL werden auch Datenbanken angelegt und geartet. Au&szlig;erdem m&uuml;ssen Benutzer f&uuml;r die Datenbank
eingerichtet werden, wobei jedem Benutzer bestimmte Rechte f&uuml;r die Tabellen in der Datenbank einzur&auml;umen sind.
DATA Query Language (DQL) (Daten-Abfrage-Sprache)
Dies wird wohl die meistgebrauchte Gruppe sein, da hier die SELECT-Anweisung auftaucht.
Abfrageformulierung mit SQL
Beispiel-Datenbank
Die Beispiele in der folgenden Tabelle beziehen sich auf diese Datenbank mit den Tabellen Teilnehmern,
Kurs und Ort.
Tabelle: Teilnehmer
Teiln
Nr
Name
Vorname
Strasse
Wohnort
100
101
102
103
104
105
106
107
108
Hirsch
Nicks
Unterl&auml;nder
Peters
Wallung
Pfeiffer
Hauer
Hofmann
Sorglos
Harry
Steffi
Elke
Paul
Walther
Claudia
Hans
Helma
Susi
Baumgartenstr. 2
Holzweg 8
Max-Weber-Str. 12
Am Markt 1
Mozartweg 6
Im Winkel 16a
Am B&auml;chle 3
Hauptstr. 143
75175 Pforzheim
73124 Oberndorf
81023 M&uuml;nchen
53522 K&ouml;ln
74121 Ludwigshafen
12329 Talhausen
66822 Heidelberg
40210 Sonnstetten
Tabelle: Kurs
Kurs
Nr
Kurs
Dauer
Voraussetzungen
Ort_Nr
2.0
3.1
3.2
Projektmanagement
Kostenrechnung
IT-Marketing
5
2
1
010
011
011
1.0
3
4.1
5.1c
5.1b
5.1d
5.6
5.3
Gesch&auml;ftsprozess,
betriebl. Organisation,
Grundlagen PC-Technik
C++ Anf&auml;ngerkurs
Java Fortgeschrittenen-kurs
C++ Fortgeschrittenen-kurs
Grundlagen Datenbanken
Vertiefung Java
keine
keine
Teilnahme am Modul Kostenrechnung
keine
2
14
10
10
5
1
013
014
014
014
013
015
5.4
Dynamische Web-Seiten
2
4.3
Vertiefung PC-Technik
2
keine
keine
Programmiererfahrung
Programmiererfahrung
keine
Teilnahme am Fortgeschrittenenkurs oder vergleichbare
Kenntnisse
Kenntnisse einer h&ouml;heren
Programmiersprache
Modul 4.1
012
010
013
29
Abfrageformulierung mit SQL
1 Projektion und Formatierung
Tabelle: Ort
Ort Nr
Ort
Schule
010
011
012
013
014
015
Ulm
Binzen
Ehingen
Pforzheim
Freiburg
L&ouml;rrach
Robert-Bosch-Schule
Fritz-Erler-Schule
Kfm. Schule
Heinrich-Wieland-Schule
Walther-Rathenau-Schule
Gewerbeschule
1 Projektion und Formatierung
Auswahl aller Spalten einer Tabelle
SELECT
*
FROM
&lt;Tabelle&gt;
Gesucht wird die Tabelle &quot;Kurs&quot;.
Beispiel :
SELECT
*
FROM
Kurs
Auswahl einer Spalte einer Tabelle
Syntax :
SELECT
&lt;Spalte&gt;
FROM
&lt;Tabelle&gt;
Gesucht werden die Nachnamen aller Teilnehmer der Kurse.
Beispiel :
SELECT
Name
FROM
Teilnehmer
Auswahl mehrerer Spalten einer Tabelle
Syntax :
SELECT
&lt;Spalte1&gt; , &lt;Spalte2&gt; , ...
FROM
&lt;Tabelle&gt;
Gesucht werden die Vor- und Nachnamen aller Teilnehmer der Kurse.
Beispiel :
SELECT
Vorname, Name
FROM
Teilnehmer
Hinweis :
In SQL kann ein Anfrageergebnis mehrere identische Tupel enthalten.
Auswahl ohne mehrfaches Auftreten desselben Tupels
Syntax :
SELECT
DISTINCT
&lt;Spalte&gt;
FROM
&lt;Tabelle&gt;
Gesucht werden die verschiedenen Nachnamen der Teilnehmer der Kurse. Tragen zwei
Beispiel :
Teilnehmer den gleichen Nachnamen, so wird dieser nur einmal aufgef&uuml;hrt.
SELECT
DISTINCT
Name
FROM
Teilnehmer
Formatierte Ausgabe und Berechnungen in einer Selektion
Syntax :
Beispiel :
Es soll f&uuml;r jeden Kurs berechnet werden, wieviel Stunden dieser dauert.
SELECT
&quot;Die Fortbildung &quot;, Kurs, &quot;dauert &quot;, Dauer*8, &quot; Stunden&quot;
FROM
Kurs
Umbenennen von Spalten
Syntax :
Beispiel :
SELECT
&lt;Spalte&gt; AS &lt;neuer Spaltenname&gt;
FROM
&lt;Tabelle&gt;
Unter der Bezeichnung “Schulorte“ sind alle Schulen gesucht, die in der Tabelle &quot;Ort&quot;
gespeichert sind.
SELECT
DISTINCT
Ort AS Schulorte
FROM
Ort
30
Abfrageformulierung mit SQL
2 Selektion
Sortierung
Syntax :
Beispiel :
SELECT
&lt;Spalte&gt;
FROM
&lt;Tabelle&gt;
ORDER BY
&lt;Spalte&gt; {DESC | ASC}
Gesucht wird f&uuml;r jeden Teilnehmer der Name und sein Wohnort. Dabei ist die Ergebnistabelle absteigend nach dem Namen sortiert auszugeben.
SELECT
Name, Wohnort
FROM
Teilnehmer
ORDER BY
Name DESC
2 Selektion
SELECT
&lt;Spalte&gt;
FROM
&lt;Tabelle&gt;
WHERE
&lt;Bedingung&gt;
Selektion mit einfachem Vergleich
Syntax :
Beispiel :
Gesucht werden die Kurse, bei denen keine Voraussetzungen notwendig sind.
SELECT
Kurs
FROM
Kurs
WHERE
Voraussetzungen = 'keine'
Gesucht werden die Kurse, bei denen Voraussetzungen gefordert werden.
Beispiel :
SELECT
Kurs, Voraussetzungen
FROM
Kurs
WHERE
NOT (Voraussetzungen = 'keine')
Gesucht werden die Kurse, die mindestens 3 Tage dauern.
Beispiel :
SELECT
Kurs, Dauer
FROM
Kurs
WHERE
Dauer &gt;= 3
Selektion mit mehreren Bedingungen
Beispiel :
Gesucht werden alle Kurse, die Programmiererfahrung erfordern und weniger als 10
Tage dauern.
SELECT
Kurs
FROM
Kurs
WHERE
Voraussetzungen = 'Programmiererfahrung'
AND
Dauer &lt; 10
Gesucht werden die Namen und Wohnorte aller Teilnehmer, deren Vorname Harry oder
Beispiel :
Susi ist.
SELECT
Name, Wohnort
FROM
Teilnehmer
WHERE
Vorname = 'Harry'
OR
Vorname = 'Susi'
Selektion mit dem Operator IN
Beispiel :
Gesucht werden die Namen aller Schulen, die in Binzen oder Freiburg sind.
SELECT
Schule
FROM
Ort
WHERE
Ort IN (''Binzen, 'Freiburg')
Gesucht werden die Namen aller Schulen, die nicht inL&ouml;rrach, Binzen und Freiburg
Beispiel :
sind.
SELECT
Schule
FROM
Ort
WHERE
Ort NOT IN ('L&ouml;rrach', 'Binzen, 'Freiburg')
Selektion mit dem Operator LIKE
Der LIKE-Operator erm&ouml;glicht den Vergleich eines Strings mit einem Muster. Muster werden aus beliebigen
Zeichen eines Strings und den beiden Sonderzeichen '?' und '*' gebildet. '?' steht f&uuml;r genau ein beliebiges
31
Abfrageformulierung mit SQL
3 Verbund von Tabellen
Zeichen, w&auml;hrend '*' f&uuml;r eine beliebig gro&szlig;e (evtl. leere) Kette von beliebigen Zeichen steht. Achtung: In
ANSI-SQL: '?' = '_' und '*' = '%'.
Beispiel :
Gesucht werden die Vornamen aller Teilnehmer, deren Postleitzahl mit &quot;3&quot; endet.
SELECT
Vorname
FROM
Teilnehmer
WHERE
Wohnort LIKE '????3*'
Gesucht werden die Vornamen aller Teilnehmer, deren Name mit &quot;H&quot;- und deren
Beispiel :
Wohnort nicht mit &quot;H&quot; beginnt.
SELECT
Vorname
FROM
Teilnehmer
WHERE
Name LIKE 'H*' AND Wohnort NOT LIKE '??????H*'
Selektion und NULL-Werte
NULL wird i.a. interpretiert als ein Platzhalter f&uuml;r die Aussage &quot;Information/Attribut ist nicht vorhanden
oder nicht bekannt oder nicht anwendbar&quot;. SQL betrachtet daher im Vergleich zwei NULL-Werte immer als
unterschiedlich. M&ouml;glich ist es allerdings abzufragen, ob die Aussage &quot;Spalten-Wert ist NULL&quot; f&uuml;r ein Tupel
gilt.
Beispiel :
Gesucht werden die Kurse, die keine Dauer angegeben haben (oder deren Dauer nicht
bekannt ist)
SELECT
Kurs
FROM
Kurs
WHERE
Dauer IS NULL
3 Verbund von Tabellen
Einfacher Equijoin mit zwei Tabellen
SELECT
&lt;Spalte1&gt; , &lt;Spalte2&gt;, ...
FROM
&lt;Tabelle1&gt; , &lt;Tabelle2&gt;
WHERE
&lt;Join-Bedingung&gt;
Hinweis :
Wenn die Tabellen, die miteinander zu verbinden sind, Spalten mit gleichem
Spaltennamen aufweisen, dann mu&szlig; jeweils spezifiziert werden, welche Spalte
welcher Tabelle gemeint ist.
Zur Verk&uuml;rzung des Anfragetextes k&ouml;nnen f&uuml;r die Tabellen in der FROM-Komponente
Beispiel :
auch Alias-Namen vergeben werden.
SELECT
Schule, Kurs
FROM
Ort O, Kurs K
WHERE
O.Ort_Nr = K.Ort_Nr ORDER BY Schule ASC, Kurs ASC
Hinweis :
Die Alias-Namen k&ouml;nnen bereits in der SELECT-Komponente verwendet werden, auch wenn sie erst in der FROM-Komponente definiert werden.
Einfacher Equijoin &uuml;ber n&gt;2 Tabellen
Syntax :
Beispiel :
Welche Kurse belegt Frau Hofmann in Binzen?
SELECT
Kurs
FROM
Teilnehmer T, TeilnehmerKursZuordnung TK, Kurs K, Ort O
WHERE
(T.Vorname = ‘Hofmann‘) AND (O.Ort_Nr = ‘Binzen‘) AND
(T.Teiln_Nr = TK.Teiln_Nr) AND (K.Kurs_N r = TK.Kurs_Nr)
AND (O.Ort_Nr = K.Ort_Nr) ORDER BY Kurs ASC
Equijoin mit Umbenennen der Spalte
Hinweis :
Beispiel :
Es ist oft sinnvoll, Spalten umzubenennen, um Mi&szlig;verst&auml;ndnisse auszuschlie&szlig;en.
F&uuml;r jeden Teilnehmer wird die Schulungsdauer in Stunden gesucht.
SELECT
Name, Vorname, Kurs, Dauer*8 AS Stunden
FROM
Teilnehmer T, TeilnehmerKursZuordnung TK, Kurs K
WHERE
(T.Teiln_Nr = TK.Teiln_Nr) AND
(K.Kurs_Nr = TK.Kurs_Nr ORDER BY Name
32
Abfrageformulierung mit SQL
4 Aggregatfunktionen und Gruppen
Vereinigung und Durchschnitt mit UNION
Die Datens&auml;tze von Tabellen, die identische Spalten enthalten, k&ouml;nnen durch UNION zusammengefa&szlig;t werden.
Beispiel :
Gew&uuml;nscht wird eine Tabelle mit den Ortsnummern Ort_Nr, der Orte, an denen das Modul 4.1 Voraussetzung ist oder deren OrtL&ouml;rrach oder Binzen ist.
SELECT
Ort_Nr
FROM
Kurs K
WHERE
K.Voraussetzungen = 'Modul 4.1'
UNION
SELECT
FROM
WHERE
Ort_NR
Ort O
(O.Ort = 'L&ouml;rrach') OR (O.Ort = 'Binzen')
4 Aggregatfunktionen und Gruppen
Hinweis: NULL-Werte werden vor der Auswertung einer Aggregatfunktion eliminiert.
Z&auml;hlfunktion
SELECT
COUNT ([DISTINCT] &lt;Spaltenliste|*&gt;)
FROM
&lt;Tabelle&gt;
Gesucht wird die Anzahl aller Fortbildungsteilnehmer.
Beispiel :
SELECT
COUNT (*)
FROM
Teilnehmer
Wieviel verschiedene Schulorte existieren?
Beispiel :
SELECT
COUNT (DISTINCT Ort) AS Schulorte
FROM
Ort
Arithmetische Funktionen
Syntax :
Syntax :
SELECT
SUM ({numerische Spalte |
Arithmetischer Ausdruck mit numerischen Spalten})
FROM
&lt;Tabelle&gt;
Gesucht wird die Gesamtdauer aller Fortbildungen, die in Binzen stattfinden.
Beispiel :
SELECT
SUM (Dauer) AS Gesamtdauer_in_Binzen
FROM
Kurs K, Ort O
WHERE
(K.Ort_Nr = O.Ort_Nr) AND (O.Ort = ‘Binzen‘)
SELECT
AVG ({numerische Spalte |
Syntax :
Arithmetischer Ausdruck mit numerischen Spalten }
FROM
&lt;Tabelle&gt;
Gesucht wird die Durchschnittsdauer aller Fortbildungsveranstaltungen.
Beispiel :
SELECT
AVG (Dauer) AS Durchschnittsdauer
FROM
Kurs
Min-/Max-Funktionen
Syntax :
SELECT
Syntax :
Beispiel :
MAX ({numerische Spalte |
Arithmetischer Ausdruck mit numerischen Spalten})
FROM
&lt;Tabelle&gt;
SELECT
MIN ({numerische Spalte |
Arithmetischer Ausdruck mit numerischen Spalten})
FROM
&lt;Tabelle&gt;
Gesucht wird die k&uuml;rzeste Kursdauer.
SELECT
MIN (Dauer) AS Minimale_Kursdauer
FROM
Kurs
33
Abfrageformulierung mit SQL
5 Unterabfragen (Subqueries)
Gruppenbildung in SQL-Anfragen
In den vorangegangenen Beispielen wurden die Aggregatfunktionen immer auf eine ganze Tabelle angewandt. Daher bestand das Abfrageergebnis immer nur aus einem Tupel. In SQL ist es aber auch m&ouml;glich,
eine Tabelle in Gruppen &quot;aufzuteilen&quot;, d.h. die Tupel einer Tabelle in Gruppen einzuteilen, und dann die
Aggregatfunktionen jeweils auf die Gruppen anzuwenden.
SELECT
&lt;Spalte&gt; , &lt;Aggregatfunktion ...&gt;
FROM
&lt;Tabelle&gt;
GROUP BY
&lt;Spalte&gt;
Hinweis :
Die in der group by-Komponente spezifizierten Spalten m&uuml;ssen auch in der
SELECT-Komponente spezifiziert sein, da Basis f&uuml;r die Gruppierung die &quot;Zwischen-Ergebnis&quot;-Tabelle ist, die durch Select ... From ... Where ... spezifiziert
wurde. Andererseits m&uuml;ssen alle Spalten der Selektionsliste, die nicht durch
eine Aggregatfunktion gebunden sind, in der group by-Komponente aufgef&uuml;hrt
werden. Die Reihenfolge der Spaltenspezifikation in der GROUP BYKomponente hat keinen Einflu&szlig; auf das Resultat der Abfrage.
Es sind 2 Gruppen auszugeben: Alle verschiedenen Voraussetzungen und deren GesamtBeispiel :
Kursdauer nach Gesamt-Kursdauer absteigend sortiert.
SELECT
Voraussetzungen, SUM (Dauer) AS Gesamtdauer
FROM
Kurs
GROUP BY
Voraussetzungen ORDER BY Sum (Dauer) DESC
F&uuml;r jeden Kursort soll die Anzahl der Veranstaltungen ermittelt werden.
Beispiel :
SELECT
Ort, COUNT (Ort) AS Veranstaltungen
FROM
Ort
GROUP BY
Ort ORDER BY COUNT(Ort) DESC, Ort ASC
Auswahl von Gruppen
Syntax :
Syntax :
Beispiel :
SELECT
&lt;Spalte&gt; , &lt;Aggregatfunktion ...&gt;
FROM
&lt;Tabelle&gt;
GROUP BY
&lt;Spalte&gt;
HAVING
&lt;Bedingung&gt;
Es sind alle Orte zusammen mit der Gesamtdauer auszugeben, wenn die Gesamtdauer
mindestens 10 Tage lang ist.
SELECT
Ort, SUM (Dauer) AS Gesamtdauer
FROM
Kurs, Ort
WHERE
Kurs.Kurs_Nr = Ort.Ort_Nr
GROUP BY
Ort
HAVING
SUM (Dauer) &gt;= 10
5 Unterabfragen (Subqueries)
Eine Unterabfrage ist eine Abfrage, die in einer Abfrage eingebettet ist.
Unteranfragen mit dem IN-Operator
Syntax :
Hinweis :
Beispiel :
Hinweis :
SELECT
FROM
WHERE
&lt;Spalte1&gt;
&lt;Tabelle&gt;
&lt;Spalte2&gt;
IN (SELECT &lt;Spalte3&gt;)
FROM &lt;Tabelle&gt;
[WHERE &lt;Bedingung&gt;])
Spalte 1, Spalte 2 und Spalte 3 m&uuml;ssen nicht unterschiedlich sein.
Gesucht wird jede Schule an der ein Kurs stattfindet, der Programmiererfahrung verlangt.
SELECT
Ort, Schule
FROM
Ort
WHERE
Ort_Nr IN (SELECT Ort_Nr
FROM Kurs
WHERE Voraussetzungen = ‘Programmiererfahrung‘)
Jede Anfrage, die eine Unteranfrage mit dem IN-Operator enth&auml;lt, ist als Equi-
34
Abfrageformulierung mit SQL
Hinweis :
Beispiel :
6 Struktur von Tabellen erzeugen, &auml;ndern und l&ouml;schen
join formulierbar (gilt nicht f&uuml;r NOT IN). So ist die folgende Abfrage &auml;quivalent
zur vorhergehenden.
SELECT
Ort, Schule
DISTINCT
FROM
Kurs K, Ort O
WHERE
(K.Ort_Nr = O.Ort_Nr) AND
(Voraussetzungen = ‘Programmiererfahrung‘)
Um das Ergebnis einer Unterabfrage zu negieren, kann der NOT-Operator benutzt werden.
Gesucht werden alle Kurse, die nicht in Binzen stattfinden.
Kurs
SELECT
FROM
Kurs
WHERE
Ort_Nr NOT IN (SELECT Ort_Nr
FROM
Ort
WHERE Ort = ‘Binzen‘)
Identisch w&auml;re: SELECT
FROM
WHERE
Kurs
Kurs
Ort_Nr IN (SELECT Ort_Nr
FROM
Ort
WHERE Ort &lt;&gt; ‘Binzen‘)
6 Struktur von Tabellen erzeugen, &auml;ndern und l&ouml;schen
Neue Tabelle zu einer bestehenden Datenbank hinzuf&uuml;gen
&lt;Tabellenname&gt;
(&lt;Spalte1&gt; Datentyp, &lt;Spalte2&gt; Datentyp, ...)
Es soll eine Tabelle “Referent“ erstellt werden.
Beispiel :
CREATE TABLE
Referent
(Ref_Nr Text(4), Name Text(20), Vorname Text(18))
Eine bestehende Tabelle aus einer Datenbank l&ouml;schen
Syntax :
CREATE TABLE
DROP TABLE
&lt;Tabellenname&gt;
Es soll eine Tabelle “Referent“ endg&uuml;ltig entfernt werden.
CREATE TABLE
Referent
Eine neue Spalte zu einer bestehenden Tabelle hinzuf&uuml;gen
Syntax :
Beispiel :
&lt;Tabellenname&gt;
Zur Tabelle “Ort“ soll die Spalte “OSA“ hinzugef&uuml;gt werden.
Beispiel :
ALTER TABLE
Ort
Eine Spalte einer Tabelle l&ouml;schen
Syntax :
ALTER TABLE
Syntax :
ALTER TABLE
&lt;Tabellenname&gt;
DROP COLUMN
Aus der Tabelle “Ort“ soll die Spalte “OSA“ dauerhaft entfernt werden.
Beispiel :
ALTER TABLE
Ort
DROP COLUMN OSA
An eine Tabelle ein Datensatz anf&uuml;gen
Syntax :
INSERT INTO
Beispiel :
&lt;Tabellenname&gt;
(&lt;Spalte1&gt; Datentyp, &lt;Spalte2&gt; Datentyp, ...)
VALUES
(&lt;Wert1&gt;, &lt;Wert2&gt;, ...)
Zur Tabelle “Ort“ soll der Datensatz “016“,“L&ouml;rrach“, “Gewerbeschule“ hinzugef&uuml;gt werden.
INSERT INTO Ort (Ort_Nr, Ort, Schule)
VALUES
(016, ‘L&ouml;rrach‘, ‘Gewerbeschule‘)
35
Abfrageformulierung mit SQL
Weitere Hinweise zum Verbund von Tabellen
Datensatz aus einer Tabelle l&ouml;schen
Syntax :
DELETE FROM
&lt;Tabellenname&gt;
WHERE
&lt;BEDINGUNG&gt;
Von der Tabelle “Ort“ sollen die Datens&auml;tze gel&ouml;scht werden, deren Schulbezeichnung
“Heinrich-Hertz-Schule“ lautet.
DELETE FROM
Ort
WHERE
Schule = ‘Gewerbeschule-Loerrach‘
Beispiel :
Weitere Hinweise zum Verbund von Tabellen
Inner Join: Verkn&uuml;pfung von Tabellen
Beispiel: SELECT
b.buch_nr, autor, titel, leser_nr
FROM buecher b, verleih v WHERE b.buch_nr = v.buch_nr; Beschreibung:
Hier werden die beiden Spalten buecher.buch_nr und verleih.buch_nr verglichen, und bei gleichem Inhalt in
der Tabelle ausgegeben. Somit erh&auml;lt man eine Tabelle mit dem Inhalt von mehreren Tabellen. Alias-Namen
sind nicht zwingend, aber ratsam.
Auto Join/Self Join: Verkn&uuml;pfung einer Tabelle mit sich selbst
Beispiel: SELECT
emp1.name, emp1.sal, emp2.name, emp2.sal
FROM emp emp1, emp emp2 WHERE (
(emp2.sal &gt; emp1.sal) AND (emp1.ename = 'BLAKE') );
Beschreibung:
Hier k&ouml;nnen Angaben einer Tabelle miteinander verkn&uuml;pft und verglichen werden. Alias-Namen sind wegen
der garantiert doppelt vorkommenden Namen Pflicht.
Outer Join: Verkn&uuml;pfung von Tabellen
Beispiel: SELECT * FROM u, v WHERE u.s1(+) = v.s1(+);
SELECT * FROM u, v WHERE u.s1(+) = v.s1;
--&gt; vollst&auml;ndig
--&gt; einseitig
Beschreibung:
Bei dieser Tabellenverkn&uuml;pfung werden auch Zeilen dargestellt, die nur in einer Tabelle vorhanden sind. Die
Spalten der anderen Tabelle bleiben an dieser Stelle leer. Beim vollst&auml;ndigen Outer Join geschieht dies bidirektional, beim einseitigen Outer Join nur in eine Richtung.
Unterabfragen: Einzeilige (= Single Row Subquery)
- Beispiel: SELECT
ename FROM emp WHERE hiredate &gt;
( SELECT hiredate FROM emp
WHERE
ename = 'FORD' );
36
Abfrageformulierung mit SQL
Weitere Beispiele zum Verbund von Tabellen
Beschreibung:
Eine SELECT-Unterabfrage ist dann einzeilig (=Single Row Subquery), wenn sie genau eine Zeile als Ergebnis liefert. Das Ergebnis der einzeiligen SELECT-Abfrage kann mit &lt;, =, &gt;, &lt;= und &gt;= verglichen werden.
Unterabfragen: Mehrzeilige (= Multiple Row Subquery)
Beispiel: SELECT
* FROM buecher WHERE buch_nr IN ( SELECT buch_nr FROM verleih );
Beschreibung:
Eine SELECT-Unterabfrage ist dann mehrzeilig (=Multiple Row Subquery), wenn sie als Ergebnis eine
mehrzeilige Tabelle liefert, die nur mit Mengenoperatoren verglichen werden kann.
Mengenoperatoren sind:
ANY, ALL, [NOT] IN, EXISTS, UNION, INTERSECT, MINUS. Auf den Seiten DQL 40 und DQL 42
werden diese beschrieben.
Weitere Beispiele zum Verbund von Tabellen
SELECT * FROM testCompanies JOIN testEmployees&quot;;
SELECT * FROM testCompanies AS a
LEFT JOIN testEmployees AS b ON (a.ID = b.ID)&quot;;
SELECT * FROM testCompanies AS a
RIGHT JOIN testEmployees AS b ON (a.ID = b.ID)&quot;;
SELECT * FROM testCompanies
LEFT JOIN testEmployees USING (ID)&quot;;
SELECT * FROM testCompanies as A , testEmployees AS B
WHERE A.ID = B.ID
SELECT *
FROM testCompanies
JOIN testEmployees
ID name ID nachname position companyID 1 Firma 1 3 Schmidt
Hausmeister
1
2 Firma 2 3 Schmidt
Hausmeister
1
3 Firma 3 3 Schmidt
Hausmeister
1
1 Firma 1 1 Musterfrau
Gesch&auml;ftsf&uuml;hrerin 1
2 Firma 2 1 Musterfrau
Gesch&auml;ftsf&uuml;hrerin 1
3 Firma 3 1 Musterfrau
Gesch&auml;ftsf&uuml;hrerin 1
1 Firma 1 2 Mustermann Gesch&auml;ftsf&uuml;hrer
2
2 Firma 2 2 Mustermann Gesch&auml;ftsf&uuml;hrer
2
3 Firma 3 2 Mustermann Gesch&auml;ftsf&uuml;hrer
2
37
Abfrageformulierung mit SQL
SELECT *
Weitere Beispiele zum Verbund von Tabellen
FROM testCompanies AS a
LEFT JOIN testEmployees AS b
ON (a.ID = b.companyID)
ID name ID nachname position companyID 1 Firma 1 3 Schmidt
Hausmeister
1
1 Firma 1 1 Musterfrau
Gesch&auml;ftsf&uuml;hrerin 1
2 Firma 2 2 Mustermann Gesch&auml;ftsf&uuml;hrer
3
2
Firma 3
SELECT *
FROM testCompanies as A ,
testEmployees AS B
WHERE A.ID = B.companyID
ID name ID nachname position companyID 1 Firma 1 3 Schmidt
Hausmeister
1
1 Firma 1 1 Musterfrau
Gesch&auml;ftsf&uuml;hrerin 1
2 Firma 2 2 Mustermann Gesch&auml;ftsf&uuml;hrer
2
38
```