Skript als PDF

Datenbanken
Berufsmittelschule Liechtenstein
Schwerpunkt: Informations- und Kommunikationstechnologien
Inhalt:
Datenorganisation ......................................................................................................................... 1
Datenbanken................................................................................................................................. 4
Das relationale Datenbankmodell.................................................................................................... 6
Datenmodellierung ...................................................................................................................... 14
Normalisierung ............................................................................................................................ 23
SQL - Structured Query Language ................................................................................................ 25
SQL - DDL (Data Definition Language) .......................................................................................... 27
SQL - DML (Data Manipulation Language) ..................................................................................... 32
Views (Sichten) ........................................................................................................................... 44
Gespeicherte Prozduren und Funktionen ....................................................................................... 44
Trigger........................................................................................................................................ 46
Tansaktionen............................................................................................................................... 48
Cursorkonzept ............................................................................................................................. 51
Literatur- und Quellenverzeichnis.................................................................................................. 55
Die nachstehende Schreibweise schliesst sowohl die weibliche
als auch die männliche Darstellungsform mit ein.
Autor: Stephan Geberl
VIP – Beratung Anstalt
Finanzerweg 3
FL-9496 Balzers
Tel.:
Mobil:
Fax.:
Mail.:
Skype:
+423 384 35 92
+423 791 21 12
+423 384 35 93
[email protected]
sgeberl
Datenorganisation
Datenorganisation
Grundlagen
Als Datenorganisation werden alle Verfahren bezeichnet, die dazu dienen, Daten bzw. Datenbestände
•
zu strukturieren
•
auf peripheren Speichern (persistent) zu speichern und verfügbar zu halten.
Die Bandbreite an Technologien reicht dabei vom Karteikastensystem über die Dateisysteme der verschiedenen Betriebssysteme bis zu hoch spezialisierten Datenbanksystemen. Die Grundlagen der Datenorganisation bleiben dabei unabhängig von der verwendeten Technologie gleich. Die Ziele der Datenorganisation sind in jedem Fall (auch bei physischer „Speicherung“ – Lager, Bibliothek):
•
Schneller Zugriff
•
Leichte Aktualisierbarkeit
•
Beliebig auszuwerten und zu verknüpfen
•
Schutz vor Verlust, Zerstörung und unbefugtem Zugriff
•
Wirtschaftliche Nutzung der Speicherkapazität
•
Vermeidung von Redundanzen
Kategorisierung
Daten können dabei grundsätzlich in den beiden Erscheinungsformen
•
zeichenorientiert (strukturiert oder unstrukturiert) und
•
bitorientiert (statisch oder dynamisch)
auftreten. Bitorientiert (d.h. Bilder, Filme, etc) beziehungsweise zeichenorientiert (Bücher, etc.) muss
dabei nicht wie die erwähnten Beispiele zeigen nicht unbedingt heissen, dass die Daten als Computerdateien vorliegen.
bitorientiert
Bilder, Töne, etc.
unstrukturiert
Texte
Text Retrieval Systeme
Daten
ändern sich
seltenoder nie
häufig
Stammdaten
Bestandesdaten
zeichenorientiert
zeitliche
Gültigkeit
ändern
Mutationsdaten
strukturiert
ändern
Bewegungsdaten
numerisch
Zeichensatz
Operationen
alphanumerisch
Nach der zeitlichen Gültigkeit können Daten in folgende Kategorien gebracht werden:
Stammdaten
Daten, die sich selten oder nie ändern (Mitarbeiterstamm, Kundenstamm, …)
Bestandesdaten
Daten, die sich ständig ändern (Kontostände, Lagerbestände, …)
Mutationsdaten
Daten, die Stammdaten verändern (Austritt eines Vereinsmitgliedes, Namensänderung durch Heirat, …)
Bewegungsdaten
Daten, die Bestandesdaten verändern (Überweisung, Einkauf, Verkauf, …)
Im Folgenden befassen wir uns lediglich mit zeichenorientierten Daten und dort insbesondere mit
strukturierten (formatierten) Daten. Einzelne Zeichen können dabei in numerische Zeichen und alphanumerische Zeichen unterschieden werden.
Autor: Stephan Geberl
Stand: 09.06.2009
1/56
Datenorganisation
Zeichenorientierte – strukturierte (formatierte) Daten
Für zeichenorientierte – strukturierte Daten erfolgt die Speicherung in einer bestimmten logischen
Anordnung bzw. Struktur. Die Graphik zeigt diese (grob) Struktur mit einer Auswahl gängigen Bezeichnungen.
Kunden
Mayer
Müller
Schmidt
Name
Vorname
Relationales
Datenbank-Modell
(Codd)
Chen
Entity-RelationshipModell
Oestereich
www.oogpm.de
(UML)
Tabelle
(Excel, etc.)
Scheer
Wirtschaftsinfrmatik
(ARIS)
Stahlknecht,
Hasenkamp
Einführung in die
Wirtschaftsinformatik
Relation
(Relationstyp)
Entitytyp
(Gegenst
andstyp)
Objekttyp
/Klasse
Inhalt
Informationsobjekt
Datei
Tupel
Entities
Objekte
Zeile
...
Datensätze
Wohnort
Nachname
PLZ
Ort
Datensegmente
Strasse
Attribute
Attribute
Spaltenüberschrift
Attribute
Datenelemente
Datenelemente
Die Datenorganisation im Bereich strukturierte Daten befasst sich mit Datensätzen, die durch Merkmale (Datensegmente, Attribute) beschrieben werden. Alle logisch zusammengehörigen Datensätze werden zu einer Datei zusammengefasst. Dateien mit logischen Abhängigkeiten (Beziehungen) werden zu
Datenbanken zusammengefasst.
Zeichenorientierte – unstrukturierte (unformatierte) Daten
Bei unstrukturierten Daten (Textdokument) ist keine bestimmte Anordnung, bzw. Struktur vorgegeben. Hier wird nicht anhand eines Ordnungsbegriffes (Schlüssels), sondern anhand von Inhalt gesucht
(Text Retrieval-Systeme).
Schlüssel, Verschlüsselung1, Beziehungen
Datensätze werden in der Regel eineindeutig durch einen Schlüssel (Key, Ordnungsbegriff, Signatur)
gekennzeichnet. Dieser Schlüssel dient zur logischen Unterscheidung und zur Lokalisation am Speichermedium (dies vor allem bei der Speicherung in einem dem relationalem Modell folgendem Datenbanksystem). Ein Schlüsselwert identifiziert einen Datensatz einer Datei eineindeutig und kann innerhalb einer Datei nur einmal vorkommen.
Kunde
Kundennummer
Nachname
Vorname
Strasse
33
Geberl
Stephan
Finanzerw.
FL
58
Hallinger
Eduard
Rautenweg
A
Primärschlüssel
Land
Fremdschlüssel
oder
Land
Kunde
Nachname
Geberl
Vorname
Stephan
Hallinger
Eduard
Schlüssel 1
Schlüssel 2
Wohnort
Strasse
Land
Finanzerw.
Rautenweg
FL
A
Fremdschlüssel
Bezeichnung
FL
Liechtenstein
A
Österreich
D
Deutschland
I
Italien
Primärschlüssel
zusammengesetzter
Schlüssel
Primärschlüssel
Schlüssel, die alleine einen Datensatz eindeutig identifizieren nennt man Primärschlüssel. Der Primärschlüssel einer Datei kann einerseits aus einem einzigen Datenelement bestehen, dass für sich alleine
genommen den Datensatz eindeutig identifiziert, er kann aber auch aus mehreren Datenelementen
bestehen (zusammengesetzter Schlüssel), die für sich alleine genommen einen Datensatz nicht unbe-
1
Nach DIN 6763 eigentlich Nummer, Benummerung
Autor: Stephan Geberl
Stand: 09.06.2009
2/56
Datenorganisation
dingt eindeutig identifizieren, als Gesamtwert den Datensatz aber eindeutig identifizieren (Achtung:
Gesamtwert heisst nicht Summe!)
In vielen Fällen kommen Nummernsysteme als Schüssel zum Einsatz. Nummernsysteme können folgendermassen klassifiziert werden (unvollständiger Ausschnitt, Aspekte wie numerisch/ alphanumerisch werden hier nicht beachtet):
Klassifikationsnummern dienen zur Zuordnung (Klassifizierung) der benummerten Datensätze zu bestimmten Klassen (m, w, Länderkennzeichen (A, CH, D, etc.)) Klassifikationsnummern sind nur in seltenen Fällen alleine als Primärschlüssel geeignet (z.B. für die Nationalität), können aber Teil eines
zusammengesetzten Schlüssels sein.
Ident(ifizierungs)nummern dienen zur eineindeutigen Kennzeichnung der benummerten Datensätze.
Wenn Identnummer(n) und Klassifikationsnummer(n) gemeinsam ein Nummernsystem bilden, spricht
man von einer Verbundnummer.
Die häufigsten Varianten von Nummernsystemen sind systemlose Identnummern, die im Prinzip fachlich nichts mit dem Datensatz zu tun haben (Achtung: systemlos heisst in diesem Zusammenhang
nicht, dass die Nummern auch technisch beliebig vergeben werden -> Beispiel Replikatons - ID) und
Verbundnummern, die nach einem bestimmten fachlich motivierten System vergeben werden.
Identnummer
Klassifikationsnummern
Systemlose
Identnummer
= Zählnummer
Verbundnummer
identifizierender Teil hängt
vom klassifizierenden Teil ab
(Hierarchische Nummer)
- Postleitzahlen (FL-9496)
- ISBN
EAN - Präfix
Prüfziffer
Internationale Standard Buchnummer (ISBN)
1
2
3
4
5
6
7
8
9
10
11
12
13
9
7
8
3
5
4
0
4
1
9
8
6
0
Kennzeichen
Bücher
D
Springer
Verlag,
Heidelberg
identifizierender Teil und
klassifizierender Teil sind
voneinander unabhängig
- Zugnummer
- Flugnummer
Stahlknecht /
Hasenkamp,
Wirtschaftsinformatik,
10. Auflage
Nummernsysteme (vom Autor angepasst)
Quelle: Stahlknecht, Hasenkamp; Einführung in die Wirtschaftsinformatik; Springer, Berlin; 2002
Bei der hierarchischen Nummerierung wird zuerst ein (oder mehrere) klassifizierende Teil(e) der
Nummer vergeben (z.B. das Länderkürzel bei der Postleitzahl) und dann darauf aufbauend, ein oder
mehrere identifizierende Teile (meist Nummern aus einem zugewiesenen Nummernbereich). Im Falle
der unabhängigen Nummern hat der klassifizierende Teil nichts mit dem identifizierenden Teil zu tun
(z.B. Flugnummern), dieser wird meist einfach hochgezählt.
Autor: Stephan Geberl
Stand: 09.06.2009
3/56
Datenbanken
Datenbanken
Aufbau von Datenbanksystemen
Ziel eines Datenbanksystems ist es, Dateien und ihre Beziehungen zueinander möglichst redundanzfrei
und konsistent (auch über die Zeit gesehen) zu verwalten, und den Zugriff für mehrere Benutzer auf
geordnete Art und Weise zu ermöglichen. Jedes Datenbanksystem besteht dazu (Grundstruktur) aus
einem Datenbankverwaltungssystem und der Datenbank mit meist mehreren, logisch miteinander
verknüpften Dateien. Die Dateien können dabei zusätzlich noch in Bereiche (Datenbanken, Domänen,
etc.) strukturiert werden.
Datenbanksystem
Verwaltungssystem
-
Suchen
Einfügen
Ändern
Löschen
Datenbank
Domäne 1
Domäne 2
Dateien
Dateien
Das Datenbankverwaltungssystem bildet die
Schnittstelle um Benutzern (Benutzerverwaltung!),
die Bearbeitung der Dateien zu ermöglichen. Dabei
werden die folgenden grundlegenden Dateioperationen unterschieden: Suchen, Einfügen, Ändern,
Löschen.
Benutzer oder Applikationen kommunizieren mit dem Verwaltungssystem (meist) über die genormte
Sprache (Schnittstelle) SQL (Structured Query Language).
ANSI-Architekturmodell
Das ANSI-Architekturmodell (Drei - Ebenen – Modell) beschreibt die Trennung der verschiedenen Aspekte eines Datenbanksystems. Einerseits sollte die physische Implementierung, also Datenspeicherung und der physische Zugriff (Pfade, Zeiger, Bäume, etc.) von der logischen (konzeptionellen) Datenbanksicht (also dem durch das System vorgegebene Datenmodell) getrennt werden und
andererseits sollen je nach Anwenderbedürfnissen (Teilsyste) verschiedene (konsistente) Benutzersichten auf ein und dieselbe Datenbank ermöglicht werden. Ein Benutzer, der in einer bestimmten
Rolle (z.B. als Empfangsmitarbeiter Kunden identifiziert) arbeitet und dieihn unterstützende Anwendung soll nicht mit dem gesamten konzeptionellen Schema arbeiten, sondern nur mit dem ihn interessierenden Teil. Eine Dateioperation in dieser externen Sicht sollte aber den Gesamtzusammenhang
(Konsistenz) nicht zerstören.
Dialogbetrieb
Anwender
1
Stapelbetrieb
Anwender
n
Externe Sicht
(Externes Schema)
Anwendungsprogramm 1
Konzeptionelle Sicht
(Konzeptionelles Schema)
Anwendungsprogramm n
Anwendungsprogramm 1
Logische Datenorganisation
Anwendungsprogramm n
QL (Query Language)
DML
(Data Manipulation
Language)
DDL
(Data Description
Language)
Physische Datenorganisation
DSDL
(Data Storage
Description Language)
Interne Sicht
(Internes Schema)
Datenspeicher
Datenspeicher
ANSI-Architekturmodell
Quelle: Stahlknecht, Hasenkamp; Einführung in die Wirtschaftsinformatik; Springer, Berlin; 2002
Die Kommunikation zwischen den Ebenen (Externes Schema / Konzeptionelles Schema) erfolgt meist
mittels genormter Abfragesprachen (SQL). Für die Erstellung eines konzeptionellen Schemas wird
meist eine Teilmenge von SQL (DDL = Data Definition Language) verwendet. Für Abfragen und Manipulation von Daten wird üblicherweise DML = Data Manipulation Language (ebenfalls eine Teilmenge
von SQL) als Query Language (QL) verwendet. Die physische Datenorganisation arbeitet mittels einer
DSL (Data Storage Descripton Language), mit dieser Sprache hat der Benutzer im Normalfall allerdings nichts zu tun.
Autor: Stephan Geberl
Stand: 09.06.2009
4/56
Datenbanken
Konzeptionelles Schema (Konzeptionelle Sicht, logische Datenbankorganisation)
Das Konzeptionelle Schema stellt die Datensicht der (problemrelevanten) Realwelt dar. Die Beschreibung / Modellierung erfolgt praktisch immer in Form eines semantischen Datenmodells, dass mittels
der Entity – Relationship - Modellierung (ER - Modellierung) oder neuerdings mittel erweiterter UML –
Klassenmodellierung erstellt wird. Das konzeptionelle Schema enthält alle Dateien und die Beziehungen zwischen den Dateien (im Entity Relationship Modell die Entitäten und Relationen). Das Konzeptionelle Schema kann sehr umfangreich werden und sollte deshalb (wenn es eine bestimmte Grösse
übersteigt) in fachlich sinnvolle Teilschemata untergliedert werden.
Internes Schema (interne Sicht)
Das Interne Schema (1. Schicht) spiegelt die interne (physische) Dateiorganisation (Dateispeicherung,
Suche, etc). Mit diesem Schema hat der fachliche Entwickler einer Datenbank meist nichts zu tun. Als
Kommunikationsschnittstelle mit dem Internen Schema dient eine (meist genormte) Datenbanksprache. Bei relationalen Datenbanken ist dies SQL (Structured Query Language) bzw. als Teilmenge DDL
(Data Description Language).
Externes Schema (externe Sicht, View)
Die Externen Schemata (3. Schicht) enthalten den für eine Teilimplementierung / Anwendung relevanten Ausschnitt (Entitäten und Relationen) eines konzeptionellen Schemas. Als Kommunikationsschnittstelle dient wiederum ein Ausschnitt aus der SQL, mämlich die DML (Data Manipulation Language).
Bei der Erstellung eines Externen Schemas ist darauf zu achten, dass keine der erlaubten Operationen
auf dieses Schema den Datenintegritätsbedingungen der Datenbank widerspricht, also die Datenbank
als Ganzes in einem undefinierten Zustand zurücklässt. Bei der Einhaltung dieser Bedingung helfen
moderne Datenbanksysteme, indem sie derartige Operationen gar nicht erst zulassen..
Aufbau von Daten(bank-)gestützten Applikationen
In Bezug auf die Verarbeitung von Daten kann zwischen den beiden (für uns wichtigen) Formen der
programmintegrierter Verarbeitung und der dateiintegrierter Verarbeitung unterschieden werden.
Programmintegrierte Verarbeitung
DebitorenBuchhaltung
Fakturierung
Rechnungssummen
Dateiintegrierte Verarbeitung
DebitorenBuchhaltung
Fakturierung
Debitorenkonten
Debitorenkonten
Aufbau Datengestützter Applikationen
Quelle: Stahlknecht, Hasenkamp; Einführung in die Wirtschaftsinformatik; Springer, Berlin; 2002
Bei der programmintegrierten Verarbeitung
arbeitet jedes Programm mit seiner eigenen
Datenhaltung (früher Band, heute meist Dateien auf einer Festplatte). Die Übergabe von
Daten von einem Programm an ein anderes
erfolgt über Schnittstellen (Export und Import). Die dateiintegrierte Verarbeitung bedient sich (meist) einer Datenbank (eines
Datenbanksystems), also einer gemeinsamen
Datenbasis für alle Programme, auf die alle
Programme zugreifen.
Ein weiteres Unterscheidungsmerkmal ist die Verortung der Geschäftsogik einer Systems. Klassische
datenbankgestützte Applikationen beherbergen die (einen Grossteil der) Geschäftslogik in der Datenbankstruktur während eher programmgestützte Applikationen die Geschäftslogik im Programmcode
beherbergen und die Datenbank (meist über eine eigene Schicht) nur benutzen um Programmzustände (mit ihren Daten) persistent = dauerhaft in einer Datenbank abzulegen. Wir beschäftigen uns hier
hauptsächlich mit der datenbankgestützten Variante, obwohl dies im Moment eher die weniger häufig
verwendete ist.
Entwurfsstrategien
Beim Entwurf eines Datenmodells besteht grundsätzlich die Möglichkeit von den Externen Schematas
auszugehen (die unter Umständen in Form von Excel – Tabellen etc. schon existieren) und diese dann
zu einem konzeptionellen Schema zu verdichten (Bottom – Up - Entwurf), als auch die Möglichkeit von
Anfang an von einem (gesamtbetrieblichen) Konzeptionellen Schema auszugehen und von dort aus
die Externen Schematas zu generieren (Top – Down - Entwurf).
Erstere Variante hat den Nachteil, dass es zu (Konsistenz-, Integritäts-) Problemen bei der Verdichtung der Externen Schematas kommen kann, die dann mittels Normalisierung wieder aufgelöst werden müssen. Andererseits ist die Definition eines begrenzten Externen Schemas einfacher, und berücksichtigt meist mehr die Bedürfnisse der konkreten Teilprobleme.
Der Top-Down-Entwurf ist im Hinblick auf Vollständigkeit und vollständiger Abdeckung der Benutzerbedürfnisse schwieriger, dafür ist von Anfang an die Konsistenz und Datenintegrität gewährleistet. Als
Werkzeug für die Modellierung eignet sich sowohl das Entity – Relationship – Modell (ER – Modell)
und seine Erweiterungen als auch mit Einschränkungen Klassendiagramme nach UML – Standard.
Autor: Stephan Geberl
Stand: 09.06.2009
5/56
Relationales Datenbankmodell
Das relationale Datenbankmodell
Grundlegendes
Wir betrachten mit diesem Kapitel, wie Daten (theoretisch / logisch) in der Internen Sicht des ANSI –
Modells organisiert sein können.
Dialogbetrieb
Anwender
1
Die heute üblichen Modelle dazu sind:
Stapelbetrieb
Anwender
n
QL (Query Language)
DML
(Data Manipulation
Language)
Externe Sicht
(Externes Schema)
Anwendungsprogramm n
Anwendungsprogramm 1
Konzeptionelle Sicht
(Konzeptionelles Schema)
Anwendungsprogramm 1
Anwendungsprogramm n
DDL
(Data Description
Language)
Logische Datenorganisation
• Relationales Datenbankmodell
• Hierarchisches Datenbankmodell (im mobilen
Bereich verbreitet)
• Netzwerk-Datenbankmodell
• Objektorientiertes Datenbankmodell
• Objektrelationales Datenbankmodell
Physische Datenorganisation
DSDL
(Data Storage
Description Language)
Interne Sicht
(Internes Schema)
Datenspeicher
Datenspeicher
• XML - basiertes Datenbankmodell (Ausprägung des objektorientierten Modells ??)
Wir beschränken uns auf das (am weitesten verbreitete) relationale Datenbankmodell.
Das relationale Datenbankmodell wurde in den 60’er und 70’er Jahren des vorigen Jahrhunderts entwickelt (Edgar F. Codd). Die ersten praktisch verwendbaren Datenbanken nach dem relationalen Modell wurden gegen Ende der 70’er Jahre auf den Markt gebracht (Oracle). Im Prinzip beschreibt das
relationale Datenmodell (mathematisch) Tabellen, in denen Daten zweidimensional verwaltet werden
können sowie einen Mechanismus, wie über Schlüssel und Fremdschlüssel Verknüpfungen zwischen
Tabellen hergestellt werden können. In der Theorie basieren alle möglichen Operationen auf der relationalen Algebra. Die meisten praktisch verwendeten Datenbanken sind (wie schon gesagt) relationaler Natur, was dazu geführt hat, dass oft (fälschlicherweise) die relationale Datenbank als Synonym
für Datenbanken verwendet wird.
Relation (Tabelle)
Wir werden unser Modell aus der Datenorganisation um die relationale (tabellarische) Umsetzung wie
sie beim relationalen Modell üblich ist erweitern.
Kunden
Mayer
Müller
Schmidt
Name
Vorname
Relationales
Datenbank-Modell
(Codd)
Chen
Entity-RelationshipModell
Oestereich
www.oogpm.de
(UML)
Tabelle
(Excel, etc.)
Scheer
Wirtschaftsinfrmatik
(ARIS)
Stahlknecht,
Hasenkamp
Einführung in die
Wirtschaftsinformatik
Relation
(Relationstyp)
Entitytyp
(Gegenst
andstyp)
Objekttyp
/Klasse
Inhalt
Informationsobjekt
Datei
Tupel
Entities
Objekte
Zeile
...
Datensätze
Wohnort
Nachname
PLZ
Ort
Datensegmente
Strasse
Kunden
Primärschlüssel
Vorname
Nachname
PLZ
Ort
Strasse
Klaus
Mayer
9490
Vaduz
Burg
Stefan
Müller
9496
Balzers
Heuweg
Kurt
Schmidt
9495
Triesen
Eck
Spaltenüberschrift
Attribute
Attribute
Attribute
Relation
(Relationstyp)
Entitätstyp
(Gegenstandstyp)
Klasse
(Objekttyp)
Tabelle
Attribute
Attribute
Attribute
Spaltenüberschrift
Relationsschema (1)
Relation
(Rumpf)
Tupel
(Zeilen)
Entitätsmenge (-set)
Attributwert
Attributwert
Attributwert
Primärschlüssel
Primärschlüssel
?
Wertebereich (2)
Wertebereich (2)
(Entitätstyp)
Entität
(Klassendefinition)
Objektmenge
Instanzmenge
Objekt,
Instanz
Datenelemente
Datenelemente
Kopfzeile
Inhalt
Zeile
Zelle
Kardinalität
Grad
Wertebereich (2)
(1) ... Kopf, Relationsformat, Relationstyp
(2) ... Gebiet, Domäne, Domain
Domäne für PLZ {9490, 9469, 9495, 9494, ...}
Die Relation kann dabei alternativ auch folgendermassen notiert werden:
Kunden {Vorname, Nachname, PLZ, Ort, Strasse}
Dabei bedeuten durchgehend unterstrichene Attribute Schlüssel (Primärschlüssel) und strichliert unterstrichene Attribute Fremdschlüssel. In weiterer Folge können bei dieser Darstellung auch Datentyp
und Domäne angegeben werden. Der Unterschied zwischen dem Datentyp und der Domäne ist, der
Datentyp ist die technische Einheit während die Domäne die fachliche Einheit ist. Der Datentyp für
PLZ könnte beispielsweise Integer sein (Wertebereich wie im System definiert) während die Domäne
alle (fachlich) in Liechtenstein vorkommenden Postleitzahlen sein könnte.
Autor: Stephan Geberl
Stand: 09.06.2009
6/56
Relationales Datenbankmodell
Beispiel:
Kunden {Vorname, Nachname, PLZ:Integer [9400, 9401, 9402, ...],
Ort, Strasse}
Schlüsselattribute werden in Primärschlüssel, die zur eindeutigen Identifikation des Tupels dienen und
Fremdschlüssel, die zur Verknüpfung mit Tupeln anderer Relationen dienen, unterschieden.
Die Relationen müssen (vereinfachte Übersetzung nach Codd2) folgenden Eigenschaften genügen:
•
Jede Relation wird durch eine zweidimensionale Tabelle umgesetzt. Tabellen sind prinzipiell
voneinander unabhängig.
•
Jede Zeile dieser Relation (Tabelle) wird Tupel genannt und beschreibt eine konkrete Ausprägung der Relation, den die Tabelle darstellt
•
Die Existenz zweier identischer Zeilen (das fachlich redundante Speichern ein und derselben
Ausprägung einer Relation) ist ungültig. Jedes Tupel (Zeile) wird durch einen (innerhalb der
Relation) eindeutigen Schlüssel identifiziert.
•
Attribute, die NULL – Werte enthalten, dürfen nicht als Schlüssel verwendet werden. „NULL –
Wert“ bedeutet konkret, der entsprechende Attributwert des Tupels ist (logisch, fachlich) nicht
vorhanden.
•
Die Reihenfolge der Tupel innerhalb einer Relation (Tabelle) ist irrelevant.
•
Jede Spalte der Relation (Tabelle) entspricht einem Attribut. Jedes Attribut hat einen Bezeichner (Spaltenname), der innerhalb der Relation eindeutig sein muss. Die konkreten Tupel werden durch die jeweiligen Attributwerte beschrieben.
•
Existiert für ein Attribut eine begrenzte Anzahl von (fachlich möglichen) Attributwerten, so
wird die Menge dieser Attributwerte Domäne (Wertebereich) genannt
•
Attribute und Attributwerte sind atomar (fachlich nicht in weitere Bestandteile zerlegbar). Auf
Attributebene bedeutet dies, dass ein Attribut keine Datenstrukturen enthalten darf (flache
Struktur). Alle Attributwerte sind vom selben Datentyp und unterliegen (falls definiert) denselben Domäneneinschränkungen.
•
Auf jeden atomaren Wert (jedes Attribut) eines Tupels kann durch die Kombination von Relationsnamen, Attributnamen und Primärschlüssel der Relation zugegriffen werden.
•
Die Reihenfolge der Attribute (Spalten) innerhalb der Relation ist irrelevant.
•
Der Grad einer Relation bezeichnet die Anzahl der Attribute, die Kardinalität einer Relation
entspricht der Anzahl der Tupel
Rationale Datenbanksysteme müssen (grob) folgende Bedingungen erfüllen:
•
Die Daten werden dem Benutzer ausschliesslich in Form von Relationen (Tabellen) zur Verfügung gestellt
•
Alle Bedingungen, insbesondere die Primärschlüssel und referenzielle Integrität (Beziehungen
zwischen Tabellen) betreffenden, müssen vom Datenbanksystem automatisch überwacht
werden.
•
Alle Operationen (Suchen, Einfügen, Ändern, Löschen) müssen ohne direkten physischen
Zugriff auf die Daten bewältigbar sein und vom Datenbanksystem unterstützt werden (meist
über SQL realisiert).
Bei der letzten Bedingung ist allerdings zu beachten, dass keine transitiven Hüllen (rekursive Abfragen) berechnet werden können. Beispielsweise3 kann bei einer Relation Mitarbeiter, die als Schlüssel
die Personalnummer und einen Fremdschlüssel (Verweis) zur Personalnummer seines direkten Vorgesetzten (Der Vorgesetzte ist in derselben Relation als Mitarbeiter mit einer Mitarbeiternummer als
Schüssel) enthält zwar mittels einer Abfrage jeweils der Vorgesetzte des Mitarbeiters, aber nicht mit
derselben Abfrage rekursiv die Vorgesetzten des Vorgesetzten ermittelt werden.
Wenn ein Datenbanksystem nach dem folgenden Schema erstellt wird, dann lässt sich der Effekt anschaulich nachweisen (übrigens: bei Einführung von Löschweitergaben – siehe säter – lässt sich zusätzlich ein „lustiger“ Ketteneffekt beim Löschen beobachten.)
2
siehe auch http://www.wikipedia.org und andere
3
Beispiel aus http://www.wikipedia.org
Autor: Stephan Geberl
Stand: 09.06.2009
7/56
Relationales Datenbankmodell
Mitarbeiter
Primärschlüssel
Kurt Schmidt
Fremdschlüssel
ID
Vorname
Nachname
Vorgesetzter
1
Klaus
Mayer
3
2
Stefan
Müller
1
3
Kurt
Schmidt
NULL
Klaus Mayer
...
Stefan Müller
...
...
In der konkreten Beispielrelation bedeutet dies, dass zwar zu jedem Mitarbeiter der Vorgesetzte zu
ermitteln ist (über den Fremdschlüssel „Vorgesetzter“), aber nicht (wie links gezeigt) das gesamte
Organigramm.
Empfehlungen für Bezeichner (Praxis)
Als Empfehlungen für die Bildung von Bezeichnern (Relationen, Attributnamen, etc.) haben sich eingebürgert4:
•
Ein Bezeichner muss mit einem Buchstaben oder einem Unterstrich (_) beginnen und darf
keine Leerzeichen enthalten.
•
Auf das erste Zeichen können Buchstaben, Ziffern und Unterstriche folgen.
•
Reservierte Wörter (Primär SQL aber unter Umständen auch systemabhängige reservierte
Wörter. Für SQL z.B. FROM) dürfen nicht als Bezeichner verwendet werden.
•
Bezeichner sollten max. 64 Zeichen lang sein
•
Bezeichner für Schlüssel sollten in ihrem Namen auf die Relation schliessen lassen und möglichst im System eindeutig sein (wünschenswert, bessere Lesbarkeit)
•
Bezeichner für Fremdschlüssel sollten in ihrem Namen auf den referenzierten Schlüssel und
eventuell auf den Beziehungstyp (wenn mehrere vorhanden sind) schliessen lassen (wünschenswert, bessere Lesbarkeit)
Die meisten Datenbanksysteme erlauben eine wesentlich freizügigere Bezeichnung (besonders
„freundlich“ erweisen sich die Microsoft – Systeme SQL-Server und Jet - Engine). Aus Gründen der
Portierbarkeit und des leichteren Zugriffs über Programmierschnittstellen ist aber dennoch eine Einhaltung der Empfehlungen wünschenswert. Eine weitere Empfehlung bezieht sich auf die (firmenweite)
schriftliche Normierung von Bezeichnern. Ein derartiges System erleichtert das Arbeiten (vor allem für
Programmierer) mit den Datenbanken erheblich.
Beziehungen zwischen Relationen (Tabellen)
Das Relationale Modell beschreibt nicht nur Relationen (Tabellen), sondern auch Beziehungen zwischen den Tabellen. Diese Beziehungen zwischen Relationen (oder auch innerhalb ein und derselben
Relation) können über Schlüssel und Fremdschlüssel realisiert werden. Vergleichbar ist dies mit einem
System, bestehend aus zwei Zettelkästen (Relationen - beispielsweise „Kunden“ und „Orte“), bei dem
jede Karteikarte (Tupel) aus dem Zettelkasten „Kunden“ einen Verweis (den Wohnort des Kunden =
Fremdschlüssel) auf eine Karteikarte des Zettelkastens „Orte“ trägt. Dazu muss jede Karteikarte im
Zettelkasten „Orte“ einen eindeutigen Schlüssel besitzen (hier der Ortsname), der als Fremdschlüsselwert auf dem entsprechenden Zettel des Karteikastens „Kunden“ eingetragen werden kann (natürlich
sollte auch jede Karteikarte im Zettelkasten „Kunden“ einen eindeutigen Schlüssel besitzen).
Ort
Beziehungstyp
(Relationship type)
“wohnt”
Kunde
IDOrt
Ortsname
1
Balzers
Nachname
Vorname
Strasse
FKOrt
2
Dornbirn
Geberl
Stephan
Finanzerw.
1
3
Triesen
Hallinger
Eduard
Rautenweg
2
4
Bregenz
Beziehung
Relationship
Fremdschlüssel
Primärschlüssel
Es ist jetzt wichtig, zwischen den einzelnen Beziehungen (Schlüssel – Fremdschlüssel -> Hans Mayer
wohnt beispielsweise in Triesen = eine Beziehung) und andererseits dem abstrakten Beziehungstyp
4
siehe auch http://www.wikipedia.org
Autor: Stephan Geberl
Stand: 09.06.2009
8/56
Relationales Datenbankmodell
(hier mit dem Verb „wohnt“ umschrieben) zu unterscheiden. Der Beziehungstyp ist das abstrakte
Sammelgefäss für die einzelnen Beziehungen (wie eine Relation für einzelne Tupel). Die Umsetzung
von Beziehungen kann (wie weiter unten gezeigt) entweder über einen einfachen Schlüssel - Fremdschlüssel – Mechanismus erfolgen oder aber über eine eingefügte Relation, die in der einfachsten
Form eine Zusammenstellung von Fremdschlüsseln als Tupel enthält.
Nullwerte in Datenbanken5
Die Bedeutung einer (Datenbank-) NULL6 i(englisch auch als ['nʌl] bezeichnet) st der eines dritten
Wahrheitswertes und nicht der Zahl 0. In jeder Implementierung einer Datenbankabfragesprache
(SQL) muss es demgemäss Möglichkeiten geben, auf diesen dritten Wahrheitswert abzufragen (IS
NULL, IS NOT NULL, isNull(), etc.): Es könnte beispielsweise einen Kunden geben, dessen Wohnort
uns nicht bekannt ist. Der entsprechende Fremdschlüsselwert für FKOrt des Kunden müsste dann mit
NULL angegeben werden. Die NULL bedeutet also: Für diesen Kunden gibt es keine bekannte Wohnort -Beziehung zu einem Tupel der Relation Ort (don't know-Unbestimmtheit). Es ist Aufgabe des Datenbankdesigners (also eine Frage der zugrundeliegenden Fachlogik), zu überprüfen ob ein solcher
NULL – Wert zugelassen werden soll, oder nicht. Falls er sich entscheidet, die NULL nicht zuzulassen,
erzwingt er damit die Eingabe eines Wohnortes für jeden Kunden.
Im Unterschied davon kann eine NULL auch dann auftreten, wenn der Wert von der Datenbank (der
zugrundeliegenden Logik) nicht benötigt wird (don't care-Unbestimmtheit). Dieser Fall weist laut gängiger Lehrmeinung auf Fehler im Datenbankdesign hin und sollte vermieden werden bzw. durch Normalisierung eliminiert werden (führt bei Abfragen auch immer wieder zu Ärger, weil konsequent auf IS
NULL oder IS NOT NULL abgefragt werden muss). Leider lässt sich dieser Fall in der Praxis (möglichst
einfaches Datenbankschema) nicht immer verhindern oder wird aus Zeitdruck übersehen.
Eine NULL als Primärschlüsselwert ergibt hingegen auf keinen Fall Sinn (würde bedeuten, dass das
Tupel nicht existiert – also ein Widerspruch an sich). Die meisten Datenbanksysteme verbieten die
NULL übrigens auch als Teil eines zusammengesetzten Schlüssels.
Kardinalitäten
Die Kardinalität bezieht sich einerseits bei einer Relation darauf, wie viele Tupel eine Relation enthält
(siehe oben) und andererseits bei einem Beziehungstyp darauf, wie viele Tupel einer Relation B von
einem Tupel der Relation A referenziert werden können. Zu beachten gilt, dass bei Kardinalitäten von
Beziehungstypen zwei Varianten möglich sind. Im deutschen Gebrauch (Scheer, ARIS, etc.) ist eher
die Variante (Leserichtung) 1, während die Variante (Leserichtung) 2 eher im amerikanischen Raum
anzutreffen ist (ursprüngliche Chen – Notation). Nebenbemerkung des Autors: dieser Umstand treibt
den Leser unterschiedlicher Literatur regelmässig in den Wahnsinn.
Die Kardialitäten werden dabei (klassisch) jeweils in Zahlenwerten oder den Variablen n und m ausgedrückt (wobei m und n potentiell unendliche Werte darstellen). Es gibt zudem diverse Notationen, die
Kardinalitätswerte nicht in Zahlen sondern in Buchstaben oder Symbolen angeben aber im Grossen
und Ganzen dasselbe aussagen (siehe Teil Modellierung).
Variante 1
ein Kunde muss genau zu einem Ort eine
Beziehung haben
1
ein Ort kann zu keinem oder mehreren
Kunden eine Beziehung haben
5
Vorname
Strasse
n,0
Ort
IDOrt
Ortsname
1
Balzers
FKOrt
2
Dornbirn
Triesen
Bregenz
Kunde
Nachname
Leserichtung
Geberl
Stephan
Finanzerw.
1
3
Hallinger
Eduard
Rautenweg
2
4
siehe http://de.wikipedia.org/wiki/Nullwert
6
„Nach Edgar F. Codd unterscheidet man zwei Arten von NULL: die Abwesenheit eines Wertes, weil
keiner existiert, oder die Abwesenheit, da man den Wert (noch) nicht kennt. Ein Nullwert steht für die
Abwesenheit eines Wertes, ein Nullwert ist aber gleichzeitig ein Wert.“ (eine Information, die besagt,
dass es (noch) keinen Wert gibt; Bemerkung des Autors) siehe http://de.wikipedia.org/wiki/Nullwert
Autor: Stephan Geberl
Stand: 09.06.2009
9/56
Relationales Datenbankmodell
Variante 2
ein Kunde muss genau zu einem Ort eine
Beziehung haben
1
Leserichtung
ein Ort kann zu keinem oder mehreren
Kunden eine Beziehung haben
Ort
n,0
IDOrt
Ortsname
1
Balzers
FKOrt
2
Dornbirn
Triesen
Bregenz
Kunde
Nachname
Vorname
Strasse
Geberl
Stephan
Finanzerw.
1
3
Hallinger
Eduard
Rautenweg
2
4
Die Kardinalität bei einem Beziehungstyp kann immer von zwei Seiten gelesen werden. In unserem
Beispiel kann die Relation von der Seite Kunde zu Ort (ein Tupel der Relation Kunde kann nur zu einem Tupel (oder keinem (NULL) Tupel) der Relation Ort eine Beziehung haben) oder von der Seite Ort
zu Kunde (ein Tupel der Relation Ort kann zu einem, keinem oder mehreren Tupeln der Relation Kunde eine Beziehung haben) gelesen werden.
Praktisch können drei grundsätzliche Beziehungstypen unterschieden werden. Diese Beziehungstypen
können wie oben gezeigt durch die 0 und / oder konkrete Werte der Kardinalität erweitert werden.
1:1 Beziehungstyp
Einem Tupel der Relation A ist genau ein Tupel einer anderen Tabelle B zugeordnet.
1
1:1
Adresse
Kunde
ID_Kunde
1
2
1
Name
Müller
Mayer
FK_Adresse
4
5
ID_Adresse
1
2
4
5
6
7
Ort
Vaduz
Schaan
Balzers
Nendeln
Triesen
Triesenberg
A
B
Im Prinzip könnten in dieser einfachen Form die beiden Relationen zu einer Relation zusammengezogen werden. Diese Beziehung kann aber aus Gründen der Sicherheit, Logik etc. dennoch in manchen
Fällen von Vorteil sein.
1:n Beziehungstyp
Einem Tupel einer Relation A sind ein oder mehrere Tupel einer anderen Relation B zugeordnet. Ein
Tupel der Relation B ist aber immer genau ein (oder kein) Tupel der Relation A zugeordnet.
n
Kunde
ID_Kunde
1
2
Name
Müller
Mayer
1
Adresse
ID_Adresse
1
2
4
5
6
7
1:n
FK_Kunde
1
1
2
Ort
Vaduz
Schaan
Balzers
Nendeln
Triesen
Triesenberg
A
B
Dies ist die Grundform eines Beziehungstyps. Der nachfolgende m:n Beziehungstyp wird von diesem
Beziehungstyp abgeleitet.
m:n Beziehungstyp
Einem Tupel einer Relation A sind ein oder mehrere Tupel einer anderen Relation B zugeordnet. Ein
Tupel der Relation B ist einem oder mehreren Tupeln der Relation A zugeordnet.
Autor: Stephan Geberl
Stand: 09.06.2009
10/56
Relationales Datenbankmodell
n
m
ID_Kunde
1
2
Adresse
ID_Adresse
1
2
4
5
6
7
wohnt
Kunde
FK_Kunde
1
1
2
1
2
2
Name
Müller
Mayer
n
1
FK_Adresse
1
2
1
7
6
7
1
n:m
Ort
Vaduz
Schaan
Balzers
Nendeln
Triesen
Triesenberg
A
B
n
Diese Form der Beziehung kann im relationalen Datenbankmodell nur über die Einführung einer weiteren Relation realisiert werden. Die m:n Beziehung wird dabei wie in der Graphik gezeigt in zwei 1:n
Beziehungen aufgelöst. In der Praxis kommt es zusätzlich häufig vor, dass in dieser Relation weitere
Daten (die Relation betreffend) gespeichert werden.
Integritätsbedingungen
Integritätsbedingungen sind Bedingungen, die an Zustände (Zeitpunkte) eines Systems bezüglich Wertebereiche, Abhängigkeiten und / oder Verlässlichkeit von Daten gestellt werden.
Es ist bei datenbankzentrierten Systemen ein allgemein gewünschtes Ziel, die Einhaltung von Integritätsbedingungen nicht den Programmierern zu überlassen, sondern sie über die Definition eines geeigneten konzeptionellen Schemas direkt auf Datenbankebene zu erzwingen. Dies kann dadurch erfolgen, dass die, die Integritätsbedingung verletzende Änderung entweder ganz unterbunden wird, oder
weiter entsprechende, die Integritätsbedingung wiederherstellende, Änderungen nach sich zieht
(Löschweitergabe, Aktualisierungsweitergabe, Trigger, etc.). Letztendlich werden damit Rahmenbedingungen für (Geschäfts-) Prozesse in der Datenbank implementiert.
Für die Einhaltung der Integrität der Schlüssel – Fremdschlüssel – Beziehungen ist ein Mechanismus
der Datenbank verantwortlich, der als referentielle Integrität bezeichnet wird. Wenn der Datenbankentwickler keine Überlegungen bezüglich der referentiellen Integrität trifft (und diese im System verankert), dann kann ein Fremdschlüssel jeden beliebigen (innerhalb des Datentyps und allenfalls der
Domäne zugelassenen) Wert annehmen. Also auch Werte, die entweder nie eine Schlüsselentsprechung in der referenzierten Relation hatte oder diese z.B. durch Löschen oder Ändern verloren hat.
Die Datenbank verhält sich nicht viel anders wie eine Tabellenkalkulation. Weiters kann der Datenbankdesigner definieren, ob eine NULL als Fremdschlüssel (also ein Fehlen der Beziehung) zugelassen
ist oder nicht und (durch Indizierung und / oder geschickte Wahl eines weiteren Attributes und dessen
Domäne) wie viele Beziehungen zugelassen sind (Kardinalität).
Um die referentielle Integrität einer Beziehung vom Datenbanksystem aufrechterhalten zu lassen,
muss diese dem System mitgeteilt werden. Dies geschieht entweder über ein entsprechendes Designtool (hier als Beispiel MS - Access), dass visuelle Eingaben in SQL umsetzt oder direkt über die Eingabe von SQL - Befehlen. Es ist wichtig anzumerken, dass diese Definition nicht wiederum ein Objekt
darstellt (das relationale Modell kennt wirklich nur Relationen (Tabellen)) sondern eine Bedingung
(Constraint) die auf ein Attribut (hier den Fremdschlüssel) angewendet wird. Die visuelle Darstellung
(hier als Linie) ist daher eher irreführend wie hilfreich. Die Entscheidung, ob dien NULL zugelassen
wird oder nicht, ist damit ebenfalls eine Bedingung, die den Fremdschlüssel (das Attribut) betrifft
(NOT NULL). Die folgenden SQL – Anweisungen werden im Kapitel SQL erklärt, sie sollen hier nur zur
Veranschaulichung des Gesagten dienen.
Wenn beispielsweise für die Kunde – Adresse - Beziehung referentielle Integrität definiert wurde,
muss für jeden Kunden eine gültige (oder keine = NULL) Adresse definiert werden (0,n : 1 Beziehung). Der Benutzer kann jetzt ausser NULL keinen Wert in FK_Adresse, der nicht Schlüssel eines
Tupels in der Relation Adresse ist (Variante 1).
Sollte dem Benutzer Eingabepflicht (Variante 2) für eine gültige Adresse auferlegt werden (1,n : 1
Beziehung), so muss einerseits referentielle Integrität bestehen und andererseits der Wertebereich
des Fremdschlüsselattributs auf ungleich NULL eingeschränkt werden. Damit ist die Eingabe des
NULL-Wertes verboten und der Benutzer muss dem Attribut FK_Adresse einen gütigen Schlüsselwert
aus der Relation Kunde zuweisen.
Autor: Stephan Geberl
Stand: 09.06.2009
11/56
Relationales Datenbankmodell
SQL – Create - Statement
Visuelles Beispiel unter MS-Access
CREATE TABLE Adresse (
ID_Adresse INT NOT NULL AUTO_INCREMENT,
Ort CHAR (255),
PRIMARY KEY (ID_Adresse))
ENGINE=InnoDB;
Variante 1: 0,n zu 1 Beziehung
CREATE TABLE Kunde (
ID_Kunde INT NOT NULL AUTO_INCREMENT,
FK_Adresse INT,
Vorname CHAR (255),
Nachname CHAR(255),
CONSTRAINT wohnt
FOREIGN KEY(FK_Adresse)
REFERENCES Adresse(ID_Adresse),
PRIMARY KEY (ID_Kunde))
ENGINE=InnoDB;
Variante 2: n zu 1 Beziehung
CREATE TABLE Kunde (
ID_Kunde INT NOT NULL AUTO_INCREMENT,
FK_Adresse INT NOT NULL,
Vorname CHAR (255),
Nachname CHAR(255),
CONSTRAINT wohnt
FOREIGN KEY(FK_Adresse)
REFERENCES Adresse(ID_Adresse),
PRIMARY KEY (ID_Kunde))
ENGINE=InnoDB;
In wenigen Fällen kann es sogar erwünscht sein, eine 1 : 1 – Beziehung zu erzwingen. Dies erreicht
man dadurch, dass für das Fremdschlüsselfeld ein Index mit der Bedingung „Eindeutig“ = „UNIQUE“
angelegt wird (eine Adresse darf immer nur genau einem Kunden zugewiesen werden – fachlich meist
unsinnig, hier aber als Beispiel er technischen Möglichkeit). Der Benutzer kann damit einen bestimmten Attributwert des Schlüssels zu Adresse in die Relation Kunde nur einmal eintragen.
CREATE TABLE Kunde (
ID_Kunde INT NOT NULL AUTO_INCREMENT,
FK_Adresse INT NOT NULL,
Vorname CHAR (255),
Nachname CHAR(255),
CONSTRAINT wohnt
FOREIGN KEY(FK_Adresse)
REFERENCES Adresse(ID_Adresse),
PRIMARY KEY (ID_Kunde),
UNIQUE INDEX idx_FK_Adresse (FK_Adresse))
ENGINE=InnoDB;
Wenn für eine Beziehung referentielle Integrität definiert wurde, ist es nicht mehr möglich Schlüsselwerte zu ändern (falls diese nicht automatisch vergeben werden) oder Tupel zu löschen wenn diese
mittels Fremdschlüssel mit einem Tupel einer anderen Relation verknüpft sind. Wenn beispielsweise
das Adressen - Tupel „Vaduz“ mit dem Kunden – Tupel Meyer verknüpft ist, kann das Tupel „Vaduz“
nicht mehr gelöscht werden (sonst würde ja der Fremdschlüssel im Tupel „Meyer“ in der Luft hängen
– was die Regel der referentiellen Integrität verletzen würde).
Autor: Stephan Geberl
Stand: 09.06.2009
12/56
Relationales Datenbankmodell
Das Tupel „Triesen“ kann dagegen gelöscht werden, da in der Relation Kunde kein Tupel mit einem
entsprechenden Fremdschlüsselwert existiert.
Eine Änderung des Schlüsselwertes des Tupels „Vaduz“ ist ebenfalls nicht möglich, da der Fremdschlüsselwert im Tupel „Meyer“ immer noch auf dem alten Wert stehen würde und damit der Verweis
zerstört wird.
Um dieses Problem zu lösen, kann das Datenbankverwaltungssystem angewiesen werden bei Veränderung eines, einem Fremdschlüssel zugrunde liegenden Tupels bestimmte, die referentielle Integrität
erhaltende, Operationen auszuführen. Dabei kann grundsätzlich zwischen Verhinderung der Änderung
(=Defaultwert), Update (des Schlüsselattributes mit entweder dem geänderten Wert, einem Defaultwert oder NULL) und Delete (des Tupels mit dem betreffenden Schlüsselwert) unterschieden werden.
Achtung, die Aktionen beziehen sich immer auf das Tupel, in dem sich der betreffende Fremdschlüsselwert befindet. Die gebräuchlichsten Operationen bei Löschen oder Ändern des Tupels (eigentlich
des Primärschlüsselwertes) sind entweder ein Delete „Löschweitergabe“ (CASCADE – das Tupel mit
dem entsprechenden Wert als Fremdschlüssel wird gelöscht), das Belegen des entsprechenden
Fremdschlüsselattributes mit NULL (SET NULL) oder einem Defaultwert (SET DEFAULT) oder das generelle Verhindern von Änderungen (Löschen oder Ändern des Schlüsselattributes) durch (RESTRICT)
oder (NO ACTION) wenn der Schlüsselwert als Fremdschlüssel in einem Tupel der entsprechenden
Relation verwendet wird (Achtung: auf die genaue jeweilige Implementierung in dem entsprechenden
Datenbanksystem achten, im Detailverhalten liegen die meisten Unterschiede und auch Bugs der verschiedenen Systeme). Access bietet hier übrigens nur eine einfache Lösch- und / oder Aktualisierungsweitergabe, bzw. setzt gelöschte Werte automatisch auf NULL.
CREATE TABLE Kunde (
ID_Kunde INT NOT NULL AUTO_INCREMENT,
FK_Adresse INT NOT NULL,
Vorname CHAR (255),
Nachname CHAR(255),
CONSTRAINT fk_Kunde_Adresse
FOREIGN KEY(FK_Adresse)
REFERENCES Adresse(ID_Adresse)
ON DELETE option
ON UPDATE option,
PRIMARY KEY (ID_Kunde))
ENGINE=InnoDB;
option kann dabei folgende Werte annehmen
RESTRICT, CASCADE, SET NULL,
NO ACTION, SET DEFAULT
Achtung: Insbesondere die Löschweitergabe ist mit Augenmass und Vorsicht zu verwenden!
Mitarbeiter
Primärschlüssel
Kurt Schmidt
Fremdschlüssel
ID
Vorname
Nachname
Vorgesetzter
1
Klaus
Mayer
3
2
Stefan
Müller
1
3
Kurt
Schmidt
NULL
Klaus Mayer
...
Stefan Müller
...
...
Beispiel: Wenn die Löschweitergabe für den Fremdschlüssel „Vorgesetzter“ definiert wurde, führt ein
Löschen des Tupels „Kurt Schmidt“ zum Löschen aller Tupel der Relation.
Autor: Stephan Geberl
Stand: 09.06.2009
13/56
Datenmodellierung
Datenmodellierung
Grundlegendes
Um die konzeptionelle Sicht eines Datenbanksystems zu planen werden die unterschiedlichsten Methoden eingesetzt. Momentan wichtige Methoden (im relationalen Umfeld) sind:
•
UML – Klassendiagramme (mit Erweiterungen, ohne Methoden)
•
ER – (Entity – Relationship - ) Modellierung (nach Chen 1976)
•
SERM – (Strukturierte Entity Relationship) – Modellierung (Erweiterung der ER – Methode vor
allem um eine explizite Darstellung von Existenzabhängigkeiten -> SAP)
•
Diverse andere Ansätze
Zusätzlich ist es möglich, mittels Normalisierung (Bottom – Up – Ansatz) zu einem konzeptionellen
Schema zu gelangen.
Dialogbetrieb
Anwender
1
Stapelbetrieb
Anwender
n
Externe Sicht
(Externes Schema)
Anwendungsprogramm 1
Anwendungsprogramm n
Anwendungsprogramm n
Anwendungsprogramm 1
Kunde
wohnt
Wohnort
bestellt
Posten
von
Konzeptionelle Sicht
(Konzeptionelles Schema)
Produkt
QL (Query Language)
DML
(Data Manipulation
Language)
DDL
(Data Description
Language)
SQL
(DDL)
Physische Datenorganisation
DSDL
(Data Storage
Description Language)
Interne Sicht
(Internes Schema)
Datenspeicher
Datenspeicher
Wir befassen uns hier ausschliesslich mit der ER - Modellierung und deren ursprünglicher Notation
nach Chen (mit Hiweisen auf Erweiterungen und alternativer Notation). Der Vorteil dieser Methode
besteht darin, dass diese direkt (meist automatisiert) mittels SQL in das konzeptionelle Schema überführbar ist, und damit de facto (in unserer nicht Datenbank - technischen – Sicht) mit diesem gleichgesetzt werden kann.
Vorgehen
Das Wichtigste vorweg: Modellieren kann man nicht durch Lernen der Begrifflichkeit, sondern nur
durch Üben am konkreten Beispiel lernen. Die folgenden Seiten ersetzen nicht das Üben, sondern
geben lediglich Hinweise zu in der Praxis „bewährtem“ Vorgehen und allgemein gültiger Begrifflichkeit.
unabhängig vom
verwendeten
Datenbanksystem
Abbildung eines
relevanten
Realitätsausschnittes
ER - Modell
Entity - Typ
Relationen
Tabellen)
SQL
(DDL)
alternative
Modelle
relationales
Modell
Zugriffsrechte
Geschwindikkeitsoptimierung
Benutzer
konkretes
Datenbanksystem
Relationship - Typ
Der wichtigste Schritt um ein funktionierendes Datenmodell zu erstellen, besteht darin, sich von den
Vorstellungen des verwendeten Datenbankmodells zu lösen. Bei der Modellierung geht es zuallererst
nicht um Relationen, Tupel und Schlüssel, sondern darum, den für die Anwendung relevanten Realitätsausschnitt abzugrenzen, und die darin enthaltenen Objekte (Personen, Orte, Gegenstände, Begrif-
Autor: Stephan Geberl
Stand: 09.06.2009
14/56
Datenmodellierung
fe, etc.) und ihre Beziehungen zu identifizieren. Wir verfolgen hier einen dementsprechend einen Ansatz der strengen sprachlichen Modellierung um vor allem den Einsteiger nicht dazu zu verführen, zu
früh in „Tabellen“ zu denken. Bitte immer daran denken: Die Implementierung in „Tabellen“, also in
einer relationalen Datenbank steht in diesem Stadium (zumindest theoretisch) noch gar nicht fest.
Der zweite Schritt der Modellierung ist, die gefundenen Objekte so zu ordnen, dass Objekttypen (Entitätstypen) entstehen, die fachlich gleichartige (zusammengehörende) Objekte (Entitäten) zusammenfassen. Es ist zu beachten dass (zumindest bei der ER – Modellierung) jede Entität eines Entitätstyps
dieselben Attribute (Achtung: eigentlich Attributstypen, nicht Ausprägungen) besitzen muss. Der analoge Vorgang findet für die Beziehungen (Relationship) statt, die zu Beziehungstypen (Relationship –
Type) zusammengefasst werden.
Das weitere Vorgehen findet ab hier auf Ebene dieser „Typen“ statt. Dieses Modell stellt eine statische
Sicht der Daten dar, Veränderung findet lediglich innerhalb der einzelnen Entities (Anfügen, Löschen,
Ändern) statt. Das ER – Modell an sich ändert sich dabei aber nicht. Die ER – Methode nach Chen
kennt in der Grundausprägung nur drei Konstrukte.
Entitätstyp
Entitytype
Gegenstandstyp
Darstellung: Quadrat
Kontakt
Die Bezeichnung erfolgt meist in Form
eines Substantives.
Die Gesamtheit von gleichartigen Entitys
(Objekten) (z.B. Mietwagen, Mitarbeiter
ist der Entitätstyp.
Das zu beschreibende Objekt (Wagen
mit der Nummer 345, Mitarbeiter Meier,
…) wird Entity (Entität, Gegenstand)
genannt.
Darstellung: Ellipse, Kreis
Attribut
Eigenschaften, die jedes Entity eines
Entitytyps aufweisen kann und die somit
jedem Entity zugewiesen werden können (z.B. Vorname für das Entity „Mitarbeiter“). Sowohl Entitytypen als auch
Relationstypen können Attribute haben.
Geburtsdatum
ID
Kontakt
Vorname
Nachname
Schlüsselattribute können unterstrichen,
Fremdschlüsselattribute strichliert werden.
Relationshiptype
Relationstyp
Beziehungstyp
Die konkrete Ausprägung eines Attributes bei einem Entity („Karl“ für das Attribut Vorname des Entities „Mitarbeiter
Karl Meier“ ist der Attributwert.
Darstellung: Raute, Rhombus
Kontakt
arbeitet
für
Kontakt
arbeitet
für
Projekt
Die Bezeichnung erfolgt meist in Form
eines (mehrerer) Verben.
Entitätstypen können durch Relationshiptypes (Beziehungstypen) verbunden
sein. Ein Relationshiptype besagt, dass
Entitäten zwischen den verbundenen
Entitätstypen Beziehungen haben (Relationships) haben können (evtl. müssen)
(z.B. kann eine Person für ein Projekt xy
arbeiten). Relationshiptypes können
zwischen Entitäten unterschiedlicher
Entitätstypen, aber auch zwischen (verschiedenen) Entitäten des gleichen Entitätstyps auftreten
Die eigentliche Modellierungstechnik ist dagegen schwer zu beschreiben. Folgende Überlegungen können hilfreich sein:
•
Was als Entitätstyp und was als Attribut modelliert wird, ist im Einzelfall zu entscheiden. Von
einem Attribut können keine Relationshiptypes ausgehen. Attribute müssen immer atomar
sein, Entitätstypen können Attribute enthalten.
•
Ein Entitietype wird immer von einem Relationshiptype gefolgt und dieser wiederum von einem Entitietype.
•
Was als Entitietype und was als Relationshiptype modelliert wird, hängt ebenfalls vom Einzelfall ab. Relationshiptypes besitzen üblicherweise keine wichtigen Attribute (können aber Attribute besitzen). Falls es als sinnvoll erscheint, wichtige Attribute auf ein Relationshiptype zu
Autor: Stephan Geberl
Stand: 09.06.2009
15/56
Datenmodellierung
verschieben, dann sollte man entweder einen abgeleiteten Entitietype (Erweiterung - siehe
unten) oder einen Entitietype in Erwägung ziehen.
•
Eine hilfreiche Regel (erster Ansatz) ist einerseits, dass Entitietypes meist durch Substantive
und Relationshiptypes meist durch Verben bezeichnet werden und andererseits die Leserichtung von links nach rechts und von oben nach unten. Im folgenden Beispiel kann damit der
Tatbestand „Kunde wohnt (an einem oder keinem) Wohnort herausgelesen werden (Wohnort
wohnt (bei) Kunden macht offensichtlich keinen Sinn). Die Bildung von Sätzen (einschliesslich
Formulierung der Kardinalität siehe Klammer) hilft also bei der Modellierung.
Die Abbildung zeigt nochmals den Zusammenhang und die Begrifflichkeit von Relationalem Datenbankmodell und ER – Modellierung.
Konzeptionelles Schema
SQL (DDL)
CREATE TABLE Wohnort (
IDOrt INT NOT NULL AUTO_INCREMENT,
Ortsname CHAR(255),
PRIMARY KEY (IDOrt))
ENGINE=InnoDB;
Kunde
0,1
0,n
wohnt
Relationales
Datenbank-Modell
(Codd)
Chen
Entity-RelationshipModell
Relation
Entitietyp
Relationshiptype Relationshiptype
Beziehungstyp Beziehungstyp
Wohnort
Beziehung
Relationship
CREATE TABLE Kunde (
Vorname CHAR(255) NOT NULL,
Nachname CHAR(255) NOT NULL,
Strasse CHAR(255),
FKOrt INT,
CONSTRAINT fk_Kunde_Wohnort
FOREIGN KEY(FKOrt)
REFERENCES Wohnort(IDOrt)
ON DELETE SET NULL
ON UPDATE CASCADE,
PRIMARY KEY (Vorname, Nachname))
ENGINE=InnoDB;
Wohnort
Beziehungstyp
(Relationship type)
“wohnt”
Kunde
IDOrt
Ortsname
1
Balzers
Nachname
Vorname
Strasse
FKOrt
2
Dornbirn
Geberl
Stephan
Finanzerw.
1
3
Triesen
Hallinger
Eduard
Rautenweg
2
4
Bregenz
Fremdschlüssel
Internes Schema
Relationship
Beziehung
Relationship
Primärschlüssel
Nebenbemerkung: Die beiden Tabellen müssen in der Reihenfolge Wohnort – Kunde angelegt werden,
da sonst die Schlüssel – Fremdschlüsselbeziehung nicht gebildet werden kann.
Kardinalitäten, Komplexitätsgrad
Die Kardinalität (oder Komplexitätsgrad) eines Relationshiptypes beschreibt, wie viele Entities eines
Entitietypes einem Entity des anderen Entitietypes zugeordnet werden können. Bei der Modellierung
arbeiten wir konsequent auf der Ebene der Relationshiptypes.
Die Relationshiptypes können nicht nur wie hier nach Chen angegeben werde, sondern es haben sich
im Laufe der Zeit verschiedene andere Notationen eingebürgert.
Kardinalität
(num. u. mc)
(0,1)
c
a
(1,1)
1
a
(n,0)
mc
a
(n,1)
m
Krähenfuss
(Martin)
Chen
a
(0,1)
(1,1)
(n,0)
(n,1)
Pfeil
(Bachmann)
UML
<<Relationship>>
A
A
A
A
A
A
A
A
A
a
0,1
A
<<Relationship>>
a
1
A
<<Relationship>>
A
a
n,0
A
<<Relationship>>
A
A
a
n
A
(nicht berücksichtigt wurden die veralteten Standards ISO – Min-Max und IDEF-1X). Ich bitte zu beachten, dass die Bachmannotation aus dem Bereich der Netzwerkdatenbanken stammt und in ihrer
reinen Form im relationalem Umfeld eher geringe Bedeutung hat, sehr wohl aber in Form der daraus
abgeleiteten Pfeilnotation. Für das Modellieren mit „Papier und Bleistift“ hat sich übrigens die Chen –
Notation am Besten bewährt. Case - Tools im Bereich ER – Modellierung greifen gerne auf die Krähenfussnotation oder die Pfeilnotation zurück.
Autor: Stephan Geberl
Stand: 09.06.2009
16/56
Datenmodellierung
Starke und schwache Entitätstypen
Im Beispiel liegt die Kardinalität [0,1:0,n] vor. Gelesen von links nach rechts bedeutet dies, dass einem Kunden (Entity des Entitietypes „Kunde“) genau ein Wohnort (Entity des Entitietypes „Ort“) zugeordnet werden kann (aber nicht muss). In der entgegengesetzten Richtung gelesen können einem
Ort entweder kein oder beliebig vielen Kunden zugeordnet sein. Beide Entitietypes sind voneinander
unabhängig (starke Entitietypes) da jedes Entity ohne jeweils ein Entity des anderen Entitietypes existieren kann.
Wird das Beispiel so abgeändert, dass in der Leserichtung von links nach rechts einem Kunden ein
Wohnort zugeordnet werden muss (Kardinalität [1:0,n]), dann wird aus dem Entitietype „Kunde“ ein
schwacher Entitietype, da ein Entity nicht mehr ohne ein Entity des Entitietypes „Ort“ existieren kann
(für die Praxis ist diese Konstruktion natürlich Nonsens).
Erweiterungen
Abgeleitete Entitietypes (Uminterpretieren eines Relationshiptypes)
Relationshiptypes können den Charakter von Entitätstypen annehmen. Meist stellt sich im Modellierungsfortschritt heraus, dass Relationshiptypes eher Entitätstypen ähneln. Dies erkennt man meist
daran, dass sich zur Beschreibung eher ein Substantiv als ein Verb eignet (z.B. kann ein Relationshiptype „kauft“ als Kaufvertrag interpretiert werden etc.) oder ein Relationshiptype besitzt viele und /
oder wichtige Attribute.
Kontakt
Kontakt
arbeitet
für
Arbeits
vertrag
Projekt
Projekt
Diesem Umstand wird dadurch Rechnung getragen, dass ein
abgeleiteter Entitätstyp eingesetzt wird. Die Raute wir mit
einem (berührungsfreien) Rechteck umrandet.
Die ursprüngliche Regel, dass Entitietype und Relationshiptype
sich abwechseln sollen, wird hierbei allerdings teilweise missachtet. Die (beispielsweise in Stahlknecht) vorgeschlagene
Lösung, zusätzliche Relationshiptypes einzuführen, ist in der
Praxis nicht üblich und verkompliziert das Modell unnötig.
Auch hier ist es weitgehend dem Modellierer überlassen (und seiner Einschätzung des Gesamtsystems) ab wann er es für gerechtfertigt hält einen abgeleiteten Entitietype einzusetzen.
Kontakt
arbeitet
für
Projekt
Kontakt
Arbeitsvertrag
arbeitet
für
Projekt
Spesen
schreibt
Autor: Stephan Geberl
Die Konstruktion des abgeleiteten Entitietypes wird verschiedentlich alternativ auch als Assoziation bzw. Aggregation bezeichnet, und zur Bildung von logischen Strukturen
in einem grösseren Datenmodell verwendet. Entitietypes
und dazwischen befindliche Relationshiptypes werden zu
logisch zusammengehörenden „globalen“ Entitietypes zusammengefasst.
Diese Form der Darstellung eignet sich im Zusammenhang
mit Formulardefinitionsmethoden zur Modellierung bis auf
Formularebene.
Stand: 09.06.2009
17/56
Datenmodellierung
Kontakt
Arbeitsvertrag
arbeitet
für
Projekt
Maske
Arbeitsvertrag
Arbeitsvertrag
komplexer
Objekttyp
Maskenseite
Sektion
Kontakt
Layout
Arbeitsvertrag
Rahmen
Kontakt:
KontaktID
Attribut (komplex)
Spalte
Vorname
KontaktID
Nachname
Vorname
Projekte
ProjektFK KontaktFK Projektbez. Kunde Budget
Nachname
Sektion
Projekte
Maskentabelle
Rahmen
Attribut (komplex)
ProjektFK
KontaktFK
Projektbez.
Kunde
Budget
Einen weiteren Aspekt betrifft die Existenzabhängigkeit der Entitietypes. Abgeleitete Entitietypes sind
per Definition schwache (weak) Entitietypes. Sie können ohne die verbundenen Entities meist nicht
existieren. Meist wird der Primärschlüssel aus zwei oder mehreren Fremdschlüsseln gebildet. Der Primärschlüssel für „Arbeitsvertrag“ könnte beispielsweise die Kontaktnummer und die Projektnummer
sein. Sollte bei einem Entity des abgeleiteten Entitietypes „Arbeitsvertrag“ entweder der zugeordnete
Kontakt oder das zugeordnete „Projekt“ wegfallen, so existiert auch der entsprechende Arbeitsvertrag
nicht mehr.
Wichtig: Für die Modellierung gilt, dass die verschiedenen Stufen zu jeder Zeit nachvollziehbar sein
sollten. Um dies zu gewährleisten, sollten derartige Konstrukte gut dokumentiert werden.
Generalisierung, Spezialisierung
Die Generalisierung fasst (logisch) ähnliche Entitietypes zu übergeordnete Entitietypes zusammen. Der
IS-A – Operator kann grundsätzlich von oben nach unten als Spezialisierung und von unten nach oben
als Generalisierung gelesen werden.
KFZ
IS
-A
Generalisierung
PKW
Spezialisierung
LKW
Im Relationalen Modell kann diese Struktur nicht direkt nachgebildet werden. Eine Vererbung, wie
beim objektorientierten Ansatz fehlt. Generalisierungen bzw. Spezialisierungen können mittels Trennung der Relationstypen, Attributen, „Verbindungstabellen“ etc. realisiert werden. Bei der Auswahl der
Realisierung spielen meist Ressourcenüberlegungen und Überlegungen bezüglich der benötigten Anwendungen (Auswertungen) eine Rolle. Die obenstehende Struktur könnte beispielsweise entweder
Autor: Stephan Geberl
Stand: 09.06.2009
18/56
Datenmodellierung
durch ein Attribut „KFZyp“ mit der Domäne [„PKW“, „LKW“] im Entitietype „KFZ“, oder durch Relationstypen „PKW“ bzw „LKW“ mit Fremdschlüsseln auf die Relationen des Relationstyps „KFZ“ realisiert
werden.
Modellierung der Zeitkomponente
Bei den meisten kleineren Modellen ist eine explizite Berücksichtigung der Zeitkomponente nicht erforderlich. Hier werden Datum und / oder Zeit wenn überhaupt benötigt als Attribute den einzelnen
Entitietypes hinzugefügt. Wenn eine explizite Beschreibung von Zuständen und Ereignissen aus Datensicht nötig wird, dann kann die Zeit explizit modelliert werden. Ereignisse können dann mittels
eines Relationshiptypes zum Entitietyp Zeit modelliert werden. Alle mit Ereignissen zusammenhängende Datenstrukturen (Bewegungsdaten) werden so anhand dieses Relationshiptypes sichtbar.
Zustände hingegen (Bestandsdaten) werden durch das Fehlen des Relationshiptypes zu Zeit eindeutig kenntlich gemacht. Alternativ können zur Darstellung von Bewegungsund Bestandsdaten besondere Symbole verwendet werden
(siehe diverse Publikationen von Campbell, etc. was hier
durch den expliziten Zeitbezug nicht nötig ist).
Zeit
arbeitet
für
Kontakt
Projekt
Die benötigten Zeiteinheit (Jahr, Monat, Tag, Stunde, Minute, …) ist je nach benötigter Granularität
festzulegen, und als Attribut dem Entitietype „Zeit“ zuzuordnen.
Die Darstellung der Historie eines einzelnen Entities (Anlegen, Ändern, Löschen, …) ist hier allerdings
ausgeklammert. Dies wäre Aufgabe einer Versionsverwaltung
Explizite Modellierung von Abhängigkeiten SERM, SIMO (SAP)
Hauptansatzpunkt der Erweiterungen von SERM (Structured – Entity – Relationship - Modell) und SIMO (SAP – Entity – Relationship - Modell) sind einerseits die explizite Modellierung von Existenzabhängigkeiten und andererseits die fehlende Unterscheidung von Entitietype und Relationshiptype.
Kunde
1
0,n
Auftrag
Kunde
0,n
1
Auftrag
1
1
1,n
Artikel
1
0,n
1,n
Auftragsposition
Artikel
SERM
1
0,n
Auftragsposition
SIMO
„Kunde“ und „Artikel“ sind starke, „Auftrag“ und „Auftragsposition“ sind schwache Entitietypes. Die
SERM – Notation stellt dies explizit dar. Zwar kennt die SERM – Notation Relationshiptypes, allerdings
ist deren Verwendung auf unbedingt nötige Fälle (n:m) beschränkt. Die SIMO – Notation kennt den
Relationshiptype überhaupt nicht.
Beispiel
Die Musteraufgabe besteht aus der Verwaltungsdatenbank für eine Bildungseinrichtung (der BMS).
Ziel ist es, die Leistungen und absolvierten Programme der Schüler zu erfassen.
Schritt 1 besteht in einer verbalen Beschreibung des Systems. Dabei sollten möglichst kurze einfache
Sätze verwendet werden. Der Schritt kann auch als Brainstorming über das System verstanden werden. Die Ergebnisse fasst man am Besten in einer Tabelle zusammen wobei auftretende fragen, Vertiefungen etc. als Notizen gefasst werden können.
Nr.
1
Satz
Bemerkungen
Die BMS bietet mehrere Lehrprogramme
Begriff BMS -> Schule
Begriff Lehrprogramme -> Schwerpunkte (Quelle: Webauftritt)
2
Ein Schwerpunkt besteht aus Modulen
Begriff Modul -> Fach (Quelle: Webauftritt)
3
Ein Fach ist entweder ein Schwerpunktfach ein Grundlagenfach oder eine interdisziplinäre Projektarbeit
Begriff interdisziplinäre projektarbeit > Projektarbeit (Quelle: Lehrplan
Deutsch)
4
Ein Schüler besucht einen Schwerpunkt
Begriff Schüler -> Studierender, Student (Quelle: Lehrplan, Webauftritt)
Autor: Stephan Geberl
Stand: 09.06.2009
19/56
Datenmodellierung
5
Ein Student absolviert pro Semester Fächer des
Schwerpunktes
Frage der Zählung von negativen
Noten ist damit nicht geklärt, spielt
aber keine Rolle.
6
Ein Student kann zur Matura antreten wenn er
Dies sind „Geschäftsregeln“ und haben mit der Struktur nichts zu tun.
•
alle Module eines Schwerpunktes positiv abgelegt
hat
•
in allen Modulen eine Anwesenheit von grösser
gleich 75% hat
•
im Modul Facharbeit eine Note von grösser gleich
4.0 hat
Alternativ zur Tabelle besteht die Möglichkeit, Fachbegriffsmodelle (ARIS) aufzustellen, die graphisch
Begriffsstrukturen aufzeigen
KFZ
steht in
Beziehung
mit
FB
ist ein
ist Exemplar von
Besitzer
kann sein
FB
Synonym
PKW
FB
Audi V8
FL XXXX
Fahrer
Auto
FB
FB
FB
Schritt 2 besteht aus einer Überarbeitung der Sätze wobei das Hauptgewicht auf der Terminologie
(Einheitlich, Synonyme, Unternehmensterminologie, etc.) und einer konsequenten Vereinfachung liegt.
Nebensatzkonstruktionen sollten wenn möglich aufgespalten werden.
Nr.
1
Satz
Bemerkungen
Die Schule bietet mehrere Schwerpunkte
Begriff BMS -> Schule
Begriff Lehrprogramme -> Schwerpunkte (Quelle: Webauftritt)
2
Ein Schwerpunkt besteht aus Fächern
Begriff Modul -> Fach (Quelle: Webauftritt)
3
Ein Fach ist entweder ein Schwerpunktfach ein Grundlagenfach oder eine Projektarbeit
Begriff interdisziplinäre projektarbeit > Projektarbeit (Quelle: Lehrplan
Deutsch)
4
Ein Studierender besucht einen Schwerpunkt
Begriff Schüler -> Studierender, Student (Quelle: Lehrplan, Webauftritt)
5
Ein Studierender absolviert pro Semester Fächer des
Schwerpunktes
Frage der Zählung von negativen
Noten ist damit nicht geklärt, spielt
aber keine Rolle.
6
Ein Studierender kann zur Matura antreten wenn er
Dies sind „Geschäftsregeln“ und haben mit der Struktur nichts zu tun.
•
alle Fächer eines Schwerpunktes positiv abgelegt
hat
•
in allen Fächern eine Anwesenheit von grösser
gleich 75% hat
•
die Projektarbeit mit einer Note von grösser gleich
4.0 abgeschlossen hat
Schritt 3 besteht in der Übertragung der Sätze in graphische (Chen -) Notation. Für die Übersichtlichkeit kann man dies für jeden Satz einzeln vornehmen und das Diagramm am Schluss zusammenführen. Als ersten Arbeitsschritt unterstreicht man am Besten Objekt/Subjekt und Verb und überträgt
diese in einem zweiten Schritt in das Diagramm (Leserichtung immer von links nach rechts und von
oben nach unten). Es ist da.bei immer auch die Frage nach der (fachlichen) Relevanz der identifizierten Objekte und Beziehungen zu stellen. Es sollte nichts modelliert werden, was fachlich nicht benötigt
wird.
Autor: Stephan Geberl
Stand: 09.06.2009
20/56
Datenmodellierung
Nr.
1
2
3
Satz
ER - Diagramm
Die Schule bietet mehrere Schwerpunkte.
Schule
bietet
Schwerpunkt
Schwerpunkt
besteht
aus
Fach
Ein Schwerpunkt besteht aus Fächern.
Ein Fach ist entweder ein Schwerpunktfach ein Grundlagenfach oder eine Projektarbeit.
Grundlagenfach
Schwerpunktfach
Projektarbeit
Fach
4
5
6
Ein Studierender besucht einen Schwerpunkt.
Studierender
besucht
Schwerpunkt
Ein Studierender absolviert pro Semester
Fächer des Schwerpunktes
Studierender
absolviert
Fach
Ein Studierender kann zur Matura antreten wenn er
•
alle Fächer eines Schwerpunktes
positiv abgelegt hat
•
in allen Fächern eine Anwesenheit
von grösser gleich 75% hat
•
die Projektarbeit mit einer Note von
grösser gleich 4.0 abgeschlossen hat
Schritt 4: besteht in der Zusammenfassung, Vereinfachung und Überarbeitung der Diagramme. Bei
der Zusammenfassung der Einzeldiagramme können zusätzlich aus den Texten abgelesene Kardinalitäten (Mehrzahlen, Zahlwörter, etc.) eingetragen werden.
Studierender
n
1
absolviert
besucht
Grundlagenfach
Schwerpunktfach
Projektarbeit
n
Schule
bietet
n
1
Schwerpunkt
1
besteht
aus
m
Fach
m
Hier sind sehr schön die Einschränkungen zu sehen, die man in diesem Stadium macht. Beispielsweise
kann in der vorliegenden Version ein Studierender nur einen Schwerpunkt besuchen (was ja auch Sinn
macht).
Schritt 5: Eine weitere Überarbeitungsmöglichkeit bietet die Uminterpretation von Beziehungstypen zu
abgeleiteten Entitätstypen sowie de Einbezug der Zeitebene. Beispielsweise könnte es hier Sinn machen, als Zeiteinheit das Semester (als Entitätstyp) einzuführen und der Beziehungstyp „besucht“
könnte in den abgeleiteten Entitätstyp „Matura“ sowie der Beziehungstyp „absolviert“ in den abgeleiteten Entitätstyp „Leistung“ umgewandelt werden.
Autor: Stephan Geberl
Stand: 09.06.2009
21/56
Datenmodellierung
Semester
Studierender
n
1
Matura
Leistung
Grundlagenfach
Schwerpunktfach
Projektarbeit
n
Schule
bietet
n
1
Schwerpunkt
besteht
aus
1
m
Fach
m
Bitte beachten Sie auch hier, dass der Zeitbezug für die Studierenden eingeführt wurde, aber nicht
beispielsweise für das Angebot an Schwerpunkten bzw. Fächern. Also auch hier wurden bei der Modellierung Einschränkungen (implizit – bewusst?) vorgenommen, die das spätere System essentiell einschränken.
Die nun folgenden Schritte 6, 7 und 8 betreffen eigentlich nicht mehr die Erstellung des konzeptionellen Schemas, sondern beziehen sich vielmehr schon auf den Übergang zum relationalen (internen)
Schema. Schritt 4 und 5 können auch in dem Sinn vertauscht werden, als die Schlüsselattribute aus
den allgemeinen Attributen der Entitätstypen abgeleitet werden können. Da aber in der Praxis aus
technischen Gründen meist nicht sprechende, syntetische Schlüssel verwendet werden (z.B. Laufnummern und / oder Replikations ID’s) , ist es sinnvoller, diese zuerst zu definieren, da sie in der Regel stabiler sind als die anderen Attribute.
Schritt 6: Auswahl der Schlüssel bzw. Fremdschlüssel
Hier ist beispielhaft anhand der Beziehung Studierender zu Fach eine mögliche Schlüssel –
Fremsdschlüsselstruktur aufgezeichnet.
Der Schlüssel für den Entitätstyp „Studierender“ ….
Schritt 7: Für die gefundenen Objekte der Modellwelt werden dann die benötigten (beschreibenden)
Eigenschaften (Attribute) identifiziert.
Die Ergebnisse von Schritt 6 und 7 können entweder in das Gesamtdiagramm übertragen werden oder
(wenn die Übersichtlichkeit leidet) für jeden Entitätstyp gesondert gezeichnet werden.
Schlüssel
Attribut
Matrikelnummer
Vorname
Studierender
Strasse
Nachname
FKOrt
Fremdschlüssel
Alternativ können Schlüssel auch unterstrichen und Fremdschlüssel
strichliert unterstrichen werden
In der Praxis üblich sind ausserdem Darstellungen in Tabellenform. Auf diese Weise können zusätzliche Informationen (Datentyp, Domäne, etc.) hinterlegt werden. Es ist lediglich wichtig zu bemerken,
dass diese Informationen möglichst noch unabhängig von dem konkreten Datenbanksystem sein soll.
Schritt 8: Schreiben der SQL – Anweisungen zur Erstellung der Tabellen sowie der Berechtigungsstrukturen. Für die Übertragung ds Modells auf eine Relationale Datenbank müssen die erstellten Modelle in
SQL (Structured Query Language) – Anweisungen übertragen und diese mittels eines SQL – Interpreters auf einer entsprechenden Datenbank ausgeführt werden (siehe Teil SQL).
Autor: Stephan Geberl
Stand: 09.06.2009
22/56
Datenmodellierung
Normalisierung
Grundlegendes
Daten werden in der Praxis meist ungeordnet, oder über eine gewachsene Ordnung gespeichert. Speichermedien sin dabei Textdateien oder Spreadsheets. Als Beispiel dient hier ein spreadsheet, dass
Vorlesungen erfasst.
Titel
Recht
Dozent
Meier
Semester, Rau, Sitzzahl
SS93, 111, 50; SS94, 112, 120
Bei einer derartigen Ablage, kann es bei der Bearbeitung zu Anomalien kommen, deren wichtigste die
•
Änderungsanomalie: Bei Wertänderung eines Attributes muss dieser in mehreren andere
Tupel geändert werden wenn die Daten redundant gespeichert sind.
•
Einfügeanomalie: Ein Datensatz lässt sich erst erfassen (NULL sei ausgeschlossen), wenn
man andere (eigentlich nicht dazugehörende) Daten kennt. Beispielsweise kann man hier keine Veranstaltung erfassen, zu der es (noch) keine Durchführung gibt.
•
Löschanomalie: Bei Löschen eines Datensatzes werden Daten (nicht) mitgelöscht, die mit
dem Datensatz eigentlich nichts zu tun haben (hier z.B. ein Raum und / oder ein Semester)
sind. Um diese Anomalien zu verhindern, wir bei Portierung in eine (relationale) Datenbank das Instrumentarium der Normalisierung angewandt.
In der Theorie wird üblicherweise von fünf (teilweise auch von mehr) Normalformen gesprochen (plus
BCNF Boyce-Codd-Normalform als Verschärfung der dritten Normalform). In der Praxis sind fast immer nur die ersten drei Normalformen von Bedeutung. Diese werden im Anschluss substanziell beschrieben.
Die grundlegende Regel lautet: Weiterfolgende Normalformen können nur erreicht werden,
wenn die Bedingungen der jeweils vorhergehenden Normalform erfüllt sind. Die Normalformen bauen aufeinander auf.
1. Normalform:
Die erste Normalform bezieht sich auf die Attribute und besagt, dass Attribute immer elementar
(atomar) sein müssen. Wiederholungsgruppen und / oder zusammengesetzte Attribute wie im Beispiel für Raum, Sitzzahl und Semester durch „,“ und „;“ getrennt müssen in eigenen, getrennten Attributen und Datensätzen (Tupel) dargestellt werden.
Titel
Recht
Titel
Dozent
Meier
Dozent
Semester, Rau, Sitzzahl
SS93, 111, 50; SS94, 112, 120
Raum
Sitzzahl
Semester
Recht
Meier
111
50
SS93
Recht
Meier
112
120
SS94
Zusätzlich muss ein neuer (zusammengesetzter) Primärschlüssel (Titel, Semester) gefunden werden,
da der alte Schlüssel (Titel) nicht mehr für jeden Datensatz eindeutig ist.
Bei der Überführung in die 1. Normalform kommt es fast immer vor, dass mehr Redundanz erzeugt
wird. Diese Redundanz wird dann in weiterer Folge aufgelöst.
Regel: Bilde elementare, atomare Attribute und bestimme einen eindeutigen (zusammengesetzten)
Schlüssel.
2. Normalform:
Die zweite Normalform stellt darauf ab, dass jedes Nichtschlüsselattribut vom gesamten Primärschüssel abhängig sein muss (voll funktional abhängig). In unserem Beispiel sind die Attribute
Stunden und Dozent zwar vom Schlüsselteil Titel abhängig, aber nicht vom Schlüsselteil Semester. Nur
die Attribute Raum und Sitzzahl hängen von beiden Schlüsselteilen (Titel, Semester) ab. Die praktische
Folge davon wäre, dass z.B. bei Änderung der Stundenanzahl, diese verlässlich in beiden Datensätzen
geändert werden müsste.
Bei Überführung der Datenbank in die 2. Normalform würde man darum die Relation in zwei Relationen aufspalten (Veranstaltung und Durchführung). Dabei wird ein Fremdschlüssel gebildet, der bei der
Relation Durchführung auf den entsprechenden Tupel der Relation Veranstaltung verweist.
Autor: Stephan Geberl
Stand: 09.06.2009
23/56
Datenmodellierung
Titel
Dozent
Raum
Sitzzahl
Recht
Meier
111
50
Semester
SS93
Recht
Meier
112
120
SS94
Veranstaltung
Titel
Recht
Dozent
Meier
Durchführung
Titel
Raum
Sitzzahl
Recht
111
50
Semester
SS93
Recht
112
120
SS94
Praktisch bedeutet dies, dass sich in einer Relation nur logisch zusammengehörende Daten befinden
sollen. Die Normalform 2 erreicht man auch durch fachlich korrekte Datenmodellierung.
Regel: Bilde aus jenen Attributen, die nur von einem Teil des Primärschlüssels abhängen eine neue
Relation (Tabelle). Der identifizierende Schlüsselteil wird der neue Primärschlüssel. In der verbleibenden Relation (Tabelle) wird der Schlüsselteil Fremdschlüssel.
3. Normalform:
Die dritte Normalform wird erreicht, wenn alle Nichtschlüsselattribute direkt vom Primärschlüssel abhängen und damit kein Nichtschlüsselattribut von einem anderen Nichtschlüsselattribut. Im
Beispiel hängt zwar der Raum vom Semester ab (im fraglichen Semester wird die Veranstaltung im
angegebenen Raum durchgeführt) aber die Sitzzahl hängt eigentlich vom Raum (also einem anderen
Nichtschlüsselattribut) und damit nur indirekt vom Semester ab.
Durchführung
Titel
Raum
Sitzzahl
Semester
Recht
111
50
SS93
Recht
112
120
SS94
Veranstaltung
Titel
Recht
Durchführung
Titel
Raum
Semester
Recht
111
SS93
Recht
112
SS94
Dozent
Meier
Raum
Raum
Sitzzahl
111
50
112
120
Die praktische Bedeutung ist ähnlich der bei der zweiten Normalform. Bei einer Änderung der Sitzzahl
(Raumumbau) müsste diese in sämtlichen Datensätzen nachgeführt werden, wenn sie nicht in die
Tabelle Raum ausgelagert wäre. Bei Einfügeoperationen in die ursprüngliche Tabelle Durchführung
müsste man zusätzlich anstatt nur der Raumummer, die Raumnummer und die Sitzzahl wissen, was
nach Überführung in die dritte Normalform nicht mehr nötig ist.
Regel: Bilde aus jenen Attributen, die funktional von einem Nichtschlüssel abhängen, eine neue Relation (Tabelle). Dasjenige Attribut, von dem sie abhängen wird der neue Schlüssel und der Fremdschlüssel in der verbleibenden Tabelle.
Autor: Stephan Geberl
Stand: 09.06.2009
24/56
SQL (Structured Query Language)
SQL - Structured Query Language
Grundlegendes
SQL wird meist in dier Kategorie 4GL (Fourth-Generation Language) d.h. SQL ist eine problemorientierte Programmiersprache im Gegensatz zu prozedural / objektorientiert orientierten Programmiersprachen wie Java, VB oder C. Sie ist nicht prozedural, d.h. der Fragesteller stellt eine Frage, gibt aber
keinen Algorithmus zur Lösung vor. In der 3GL wie Java, VB, Cobol, Pascal oder C müsste der Lösungsweg (wie die gesuchten Informationen gefunden werden können, z.B. vom Öffnen der Datei bis
zum schrittweise Durchgehen der Datensätze) angegeben werden. Der Programmierer drückt im Sourcecode nicht aus wie er zu bestimmten Ergebnissen kommt (Algorithmus), sondern welches Ergebnis
er haben will. Aus Gründen der Handhabbarkeit besitzen viele SQL – Dialekte (herstellerabhängig, z.B.
PL – SQL von Oracle) prozedurale Elemente die es erlauben prozedurales und problemorientiertes
programmieren zu verbinden.
Mit Hilfe von Structured Query Language (SQL) können die meisten relationale Datenbanken, wie
z. B. Microsoft Access, MySQL, Oracle, SQL-Server usw. „manipuliert“ werden.
Struktur
Die Sprache Teilt sich in einen Datendefinitionsteil (DDL – Data Definition Language), mit der
Datenbankobjekte (Relationen, Views, Trigger, User, usw) erstellt, manipuliert und gelöscht werden
können und einen Datenabfrageteil (DML - Data Manipulation Language), mit dem Daten abgefragt, verändert, erstellt oder gelöscht werden können. In manchen Literaturstellen findet sich noch
ein dritter Teil, die Data Control Language (DCL), die hier, es handelt sich bei den Benutzern ja letzendlich auch nur um Datenbankobjekte, unter DDL abgehandelt wird.
DDL – Data Definition Language
DML - Data Manipulation Language
Manipulation von Datenbankobjekten
Manipulation von Daten
Befehlsstrukturen
Befehlsstrukturen
CREATE <Objekt> …
erzeugen
SELECT
abfragen
ALTER <Objekt> …
verändern
INSERT
erzeugen
DROP <Objekt>
löschen
UPDATE
verändern
DELETE
löschen
GRANT
Benutzerrechte
REVOKE
Benutzerrechte
Wieder der Behauptung, SQL entstamme der Relationenalgebra ist zu sagen, dass sich lediglich einige
Operationen der Relationenalgebra mit SQL abbilden lassen. Ansonsten ist SQL eine Programmiersprache, die nur am Rande mit der Relationenalgebra zu tun hat und weit darüberhinausgeht.
Standardisierung
SQL ist ein ISO - und ANSI - Standard, der mehrfach spezifiziert wurde bzw. noch wird:
SQL86
1986 definiert
SQL89
1989 definiert. Zwei möglichen Ebenen des Sprachumfangs: (Level 1 und Level 2)
SQL 92 (SQL 2)
1992 definiert. Vier mögliche Ebenen des Sprachgebrauchs (Entry Level, Transition,
Intermediate Level, Full Level)
SQL 3
SQL 4
neues, laufendes Normierungsprojekt (aufgeschobene und neue Ziele)
Neben einem bestimmten SQL- Standard unterstützt Datenbanksysteme meist Teile höherer Standards sowie eigene SQL- Erweiterungen. SQL 89 Level 2 ist auch heute noch Basis des von vielen
Datenbanksystemen unterstützen SQL 92 sind die meisten Systeme nur Entry Level - Compliant ( z.B.
Oracle8)
Autor: Stephan Geberl
Stand: 09.06.2009
25/56
SQL (Structured Query Language)
SQL (in Access) verwenden
Für Übungen im Bereich SQL kann auch die Desktopdatenbank MS – Access herangezogen werden.
Leider kann SQL aus Access heraus nur auf Umwegen erreicht werden (Hauptweg ist das graphische
Frontend).
Wechseln Sie bei einer neuen Abfrage in die SQL-Ansicht (Bei den meisten Installationen ganz links
oben in der Buttonleiste unter den Menüs oder via Manü „Ansicht“). Anstatt der graphischen Oberfläche bekommen Sie jetzt einen Editor, in den Sie eine SQL-Anweisung schreiben können.
Sie können die Abfrage mit dem Rufezeichen in der Buttonleiste starten.
Das Semikolon „;“ am Ende einer SQL-Anweisung ist übrigens meist nur in bestimmten SQL-Editoren
ein Muss. Kein Standard definiert ein Semikolon. Wenn Sie SQL-Anweisungen in Code (VB(A), Java, C,
…) einbinden kann es unter Umständen Fehler verursachen. Die verwendeten Spezifikationen entsprechen leider nur bedingt den ein ISO- und ANSI-Standards
ANSI-89 Level 1 beschreibt die herkömmliche Jet SQL-Syntax. Dieser Modus entspricht weitestgehend der ANSI-89 Level 1-Spezifikation, ist jedoch nicht kompatibel mit ANSI-89 Level 1. Bestimmte
ANSI-89 SQL-Funktionen sind nicht implementiert, und die Platzhalterzeichen entsprechen nicht SQL,
sondern der VBA-Spezifikation (Visual Basic für Applikationen).
ANSI-92 Level 1 stellt neue reservierte Wörter, Syntaxregeln und Platzhalterzeichen bereit, die für
Sie die Möglichkeiten erweitern, Abfragen, Filter und SQL-Anweisungen zu erstellen. Dieser Modus
entspricht weitestgehend der ANSI-92 Level 1-Spezifikation, ist jedoch nicht kompatibel mit ANSI-92
Level 1. Dieser Abfragemodus enthält mehr Teile der ANSI-Syntax, und die Platzhalterzeichen entsprechen der SQL-Spezifikation.
Autor: Stephan Geberl
Stand: 09.06.2009
26/56
SQL (Structured Query Language)
SQL - DDL (Data Definition Language)
Umfang und Grundschema
Die DDL (Data Definition Language, Datendefinitionssprache) umfasst alle Befehle, die dazu dienen
Datenbankobjekte (Datenbanken, Benutzer, Relationen, Views, Trigger, Indizes, etc.) anzulegen, zu
verändern und zu löschen. Bemerkung: Hier wird auch die Manipulation von Benutzern unter DDL
subsumiert. In manchen Literaturstellen findet sich dazu auch der Begriff der Data Control Language
(DCL).
Grundsätzlich genügen für die Manipulationen drei Befehle:
CREATE
Erzeugt ein Objekt
ALTER
Verändert (Elemente) ein Objekt (eines Objektes)
DROP
Zerstört ein Objekt
Dabei können beispielsweise unter MySQL 5.1 folgende Objekte manipuliert werden: TABLESPACE,
LOGFILE GROUP, DATABASE, PROCEDURE, FUNCTION, TRIGGER, TABLE, INDEX, VIEW, USER
Zusätzlich werden zwei Befehle für die Vergabe von Rechten benötigt:
GRANT
Einem Benutzer Rechte zuweisen
REVOKE
Einem Benutzer Rechte entziehen
Wenn beispielsweise in einem bestehenden Tablespace Datenbanken und Relationen angefügt werden
sollen, kann dies über folgende SQL – Anweisungen geschehen (bitte beachten Sie, dass Trennzeichen, etc. nicht in jedem Frontend gleich behandelt werden; Gross- und Kleinschreibung der Befehle
wird hingegen durchwegs ignoriert (Achtung nicht bei Namen, nur bei Befehlen!).
Beispiel: Anlegen und Löschen von Datenbank, Benutzer und Relationn (Relationen)
Für diese Operationen müssen wir natürlich root (Administrator) – Rechte auf der Datenbank haben
(ALL PRIVILEGES). Der Name der Datenbank soll einfach Dummy und der des Benutzers Kurt sein.
Zugang zur Datenbank bekommen wir entweder über ein GUI – Tool (MySQL Query Browser, phpMyAdmin) oder über die Shell
mysql -u user -ppasswort Datenbankname
mysql -h <hostname> -u <user> [<datenbanname> -p<passwort>]
Achtung: Zwischen –p und dem Passwort darf keine Leerzeile stehen.
Und die Anlage einer neuen Datenbank (SHOW zeigt jeweils Informationen über die Objekte)
Für diese Tätigkeiten arbeiten wir ohne Vorgabeschema, da wir die Datenbank anlegen wollen, also
entsprechende Meldungen einfach ignorieren. Die Befehle gelten für alle Oberflächen (also auch Shelladministration)
Schritt 1: Datenbank anlegen
CREATE DATABASE dummy;
Allgemein lautet der Befehl:
Autor: Stephan Geberl
Stand: 09.06.2009
27/56
SQL (Structured Query Language)
CREATE DATABASE [IF NOT EXISTS] <datenbankname> [Spezifikation1, Spezifikation2, …];
Als Spezzifikationen können verwendet werden (Falls nichts angegeben ist, wird der Standardwert der
Installation verwendet):
•
CHARACTER SET <name> oder DEFAULT
Standard Zeichensatz
•
COLLATE <name> oder DEFAULT
Standard Sortierreihenfolge
Alternativ kann anstatt DATABASE auch SCHEMA benutzt werden.
Datenbanken können (obwohl selten) Datenbanken auch geändert werden
ALTER DATABASE <datenbankname> [Spezifikation1, Spezifikation2, …];
Schritt 2: Benutzer anlegen (Name: kurt). Der Benutzer soll nur auf die Datenbank „dummy“ zugreifen können , dort alle Rechte bis auf die Benutzerverwaltung haben und das Passwort „franz“ bekommen
CREATE USER kurt@localhost IDENTIFIED BY "franz";
Der Benutzer kann nur vom localhost auf die Datenbank zugreifen. Falls Fernzugriff gewünscht ist,
muss statdessen kurt@’%’ verwendet werden (bitte die ‘ beachten)
Es wurde jetzt ein Benutzer ohne jede Berechtigung ausser Lesen / Benutzung (=USAGE) erzeugt.
Allgemein lautet die Syntax:
CREATE USER <testuser>@<host> [IDENTIFIED BY [PASSWORD] "password"];
Passworte können über SQL im Nachhinein geändert werden (was man beispielsweise bei der Grundinstallation für root unbedingt sofort tun sollte). Klartextpasswörter muss man mittels der Funktion
PASSWORD – und des Passwortes in Anführungszeichen umwandeln.
SET PASSWORD = PASSWORD(„newpassword“);
SET PASSWORD FOR <testuser>@<host> = PASSWORD(„newpassword“);
Meist wird zusätzlich mittels FLUSH PRIVILEGES die Benutzertabelle neu geladen. Es gibt für den Benutzer unter MySQL übrigens einen Befehl, um Benutzer umzubenennen:
RENAME USER <testuser>@<host> TO <testuserneu>@<host>;
Dieser Befehl durchbricht etwas das Schema (CREATE, ALTER, DROP), sei aber hier nicht verschwiegen.
Schritt 3: Zuweisen von Benutzerprivilegien. Benutzerprivilegien können global für alle Datenbanken
und Datenbankspezifisch bzw. Relationenspezifisch vergeben warden. Beispielsweise sollen dem gerade angelegten Benutzer kurt alle Rechte innerhalb der Datenbank dummy eingeräumt werden, ausser
dem Recht weitere Rechte zu vergeben (ALL oder ALL PRIVILEGES).
GRANT ALL ON dummy.* TO kurt@localhost
Die Syntax dazu versteckt sich etwas hinter der Angabe des Objektes hinter dem ON Statement (<Datenbankname>.<Tabelenname>). Dabei ist der * als Platzhalterzeichen erlaubt (also auch *.* für alle
Datenbanken).
Anstatt ALL zu verwenden, können die Rechte auch einzeln vergeben werden (durch , (Komma) getrennt).
Berechtigung
Bedeutung
ALL [PRIVILEGES]
Setzt alle einfachen Berechtigungen außer GRANT OPTION.
ALTER
Erlaubt die Verwendung von ALTER TABLE.
ALTER ROUTINE
Erlaubt die Änderung oder Löschung gespeicherter Routinen.
CREATE
Erlaubt die Verwendung von CREATE TABLE.
CREATE ROUTINE
Erlaubt die Erstellung gespeicherter Routinen.
CREATE TEMPORARY TABLES
Erlaubt die Verwendung von CREATE TEMPORARY TABLE.
CREATE USER
Erlaubt die Verwendung von CREATE USER, DROP USER, RENAME USER und REVOKE ALL
PRIVILEGES.
CREATE VIEW
Erlaubt die Verwendung von CREATE VIEW.
DELETE
Erlaubt die Verwendung von DELETE.
Autor: Stephan Geberl
Stand: 09.06.2009
28/56
SQL (Structured Query Language)
DROP
Erlaubt die Verwendung von DROP TABLE.
EVENT
Erlaubt die Erstellung von Ereignissen für den Ereignisplaner.
EXECUTE
Erlaubt dem Benutzer die Ausführung gespeicherter Routinen.
FILE
Erlaubt die Verwendung von SELECT ... INTO OUTFILE und LOAD DATA INFILE.
INDEX
Erlaubt die Verwendung von CREATE INDEX und DROP INDEX.
INSERT
Erlaubt die Verwendung von INSERT.
LOCK TABLES
Erlaubt die Verwendung von LOCK TABLES für Tabellen, für die Sie die Berechtigung SELECT
haben.
PROCESS
Erlaubt die Verwendung von SHOW FULL PROCESSLIST.
REFERENCES
Nicht implementiert.
RELOAD
Erlaubt die Verwendung von FLUSH.
REPLICATION CLIENT
Erlaubt dem Benutzer, die Positionen von Slave- oder Master-Servern zu erfragen.
REPLICATION SLAVE
Für Replikationsslaves erforderlich (zum Lesen von Binärlogeinträgen auf dem Master).
SELECT
Erlaubt die Verwendung von SELECT.
SHOW DATABASES
SHOW DATABASES zeigt alle Datenbanken.
SHOW VIEW
Erlaubt die Verwendung von SHOW CREATE VIEW.
SHUTDOWN
Erlaubt die Verwendung von mysqladmin shutdown.
SUPER
Erlaubt die Verwendung der Anweisungen CHANGE MASTER, KILL, PURGE MASTER LOGS
und SET GLOBAL und des Befehls mysqladmin debug. Erlaubt ferner die (einmalige) Verbindungsherstellung auch in dem Fall, dass der Wert für max_connections erreicht wurde.
TRIGGER
Erlaubt dem Benutzer das Erstellen und Löschen von Triggern.
UPDATE
Erlaubt die Verwendung von UPDATE.
USAGE
Synonym für „keine Berechtigungen“
GRANT OPTION
Erlaubt die Gewährung von Berechtigungen.
Das Recht, Benutzer zu erstellen und Rechte zuzuweisen (GRANT Option) wird durch den Zusatz
WITH GRANT OPTION zugeteilt, auf dieselbe Weise können auch Parameter für den Datenbankzugang gesetzt werden (maximale Anzahl Zugriffe etc.)
Mittels des Befehls SHOW GRANTS können die Berechtigungen eingesehen werden
SHOW GRANTS [FOR kurt@localhost];
Jetzt können wir uns ausloggen und als Benutzer Kurt wieder einloggen. Wir verwenden dabei den
Namen der erstellten Datenbank.
mysql -h localhost -u kurt dummy -pfranz
Die Datenbank kann übrigens auch mittels folgendem Befehl eingestellt werden:
USE dummy;
Schritt 4: Der Benutzer kurt kann jetzt Datenbankrelationen anlegen. Die Grundstruktur der Anweisung sieht folgendermassen aus:
CREATE TABLE <relationenname> (<attributdefinitione>) [<optionen> <select>];
Die attributdefinition enthält die Attributdefinitionen mit Name, Datentyp, Definition für Auto - Increment, NULL, Defaultwert, etc. Beispiele: (ID INT(10) NOT NULL AUTO_INCREMENT, …) oder (Nachname VARCHAR(255) NOT NULL DEFAULT "", …). Datentypen unter MySQL sind (die genormten ANSI
– Typen sind fett gedruckt – Namensgleichheit):
TINYINT[(length)] [UNSIGNED] [ZEROFILL]
SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
INT[(length)] [UNSIGNED] [ZEROFILL]
INTEGER[(length)] [UNSIGNED] [ZEROFILL]
BIGINT[(length)] [UNSIGNED] [ZEROFILL]
REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
Autor: Stephan Geberl
Stand: 09.06.2009
29/56
SQL (Structured Query Language)
FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
DATE
TIME
TIMESTAMP
DATETIME
YEAR
CHAR(length) [BINARY | ASCII | UNICODE]
VARCHAR(length) [BINARY]
BINARY(length)
VARBINARY(length)
TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB
TINYTEXT [BINARY]
TEXT [BINARY]
MEDIUMTEXT [BINARY]
LONGTEXT [BINARY]
ENUM(value1,value2,value3,...)
SET(value1,value2,value3,...)
Zusätzlich können Attributdefinitionen wie PRIMARY KEY, KEY, INDEX, etc. gesetzt werden, und es
können Bedingungen (CONSTRAINTS) gesetzt werden, die vor allem für die Definition der referentiellen Integrität verantwortlich sind (siehe Beispiel – das Attribut von ist der Fremdschlüssel, id in der
Relation gugus ist der Schlüssel).
CONSTRAINT fkvon FOREIGN KEY(von) REFERENCES gugus(id) ON DELETE CASCADE
Die CHECK Anweisung, verantwortlich für detailierte Bedingungen zur Datenintegrität wird unter
MySQL bislang ignoriert.
Unter <optionen> wird vor allem die zu verwendende Datenbankengine angegeben (für uns immer
ENGINE = INNODB).
Als Beispiel dienen zwei Relationen (Kunde und Ort) wobei die Relation Kunde einen Fremdschlüssel
zu Ort besitzt und bei Löschen eines Ortes der entsprechende Fremdschlüsselwert auf NULL zu setzen
ist. Beide Relationen haben eine automatisch vergebene id als Schlüssel.
CREATE TABLE Ort (
ID_Ort INT(10) NOT NULL AUTO_INCREMENT,
Ortsname VARCHAR(255) NOT NULL DEFAULT "",
PRIMARY KEY (ID_Ort)
) ENGINE = INNODB;
CREATE TABLE Kunde (
ID_Kunde INT(10) NOT NULL AUTO_INCREMENT,
Vorname VARCHAR(255) NOT NULL DEFAULT "",
Nachname VARCHAR(255) NOT NULL DEFAULT "",
FK_Ort INT(10),
CONSTRAINT Wohnort FOREIGN KEY(FK_Ort) REFERENCES Ort(ID_Ort)
ON DELETE SET NULL,
PRIMARY KEY (ID_Kunde)
) ENGINE = INNODB;
Bei der Erzeugung der Relationen ist es wichtig, keine Fremdschlüssel – Constraints zu verwenden,
deren Schlüssel noch nicht definiert ist. Abhilfe schafft der Befehl SET FOREIGN_KEY_CHECKS = 0;,
der die Validierung (vorübergehend) abschaltet. Besser ist es allerdings, sich die Reihenfolge zu überlegen, in der Relationen angelegt werden.
Autor: Stephan Geberl
Stand: 09.06.2009
30/56
SQL (Structured Query Language)
Schritt 4: Wir füllen die Datenbank mit ein paar Testdaten (gehört zwar nicht hierher (DML) aber
ohne Daten ist es langweilig):
INSERT INTO Ort (ID_Ort, Ortsname)
VALUES
(1, „Balzers“),
(2, „Vaduz“),
(3, „Bendern“);
INSERT INTO Kunde (ID_Kunde, Vorname, Nachname, FK_Ort)
VALUES
(1, „Georg“, „Huber“, 1),
(2, „Karl“, „Muster“, 1),
(3, „Stefan“, „Meier“, 3);
Bitte beachten Sie, dass trotz Autonummerierung der Schlüsselwert gesetzt werden kann (ist das Gegenteil gewünscht, dann muss z.B. das Feld ID_Kunde weggelassen werden). Auch hier wird allerdings die referentielle Integrität geprüft. Es ist also die Relation „Ort“ (der entsprechende Datensatz)
zuerst zu füllen, bevor der Fremdschlüssel in der Relation „Kunde“ verwendet werden kann. Im Notfall
hilft wieder SET FOREIGN_KEY_CHECKS = 0;, Sie sollten dabei aber genau wissen was Sie tun. Sie
können jetzt übrigens die Daten mit einfachen SELECT – Anweisungen (DML) ausgeben lassen (SELECT * FROM Kunde;).
Zur Übung führen wir nun die Aktionen in umgekehrter Reihenfolge zur Vernichtung der erzeugten
Objekte durch.
Schritt 5: Als ersten Schritt löschen wir die Relationen:
DROP TABLE Kunde;
DROP TABLE Ort;
Achtung: in umgekehrter Reihenfolge wegen des Fremdschlüssels
Schritt 6: Um die Datenbank zu löschen, müssen wir unter Umständen „root“ sein („kurt“ hat, wenn
wir seine Rechte nicht mit ALL angegeben haben unter Umständen nicht das Recht, Datenbanken zu
löschen):
DROP DATABASE dummy;
Achtung: in umgekehrter Reihenfolge wegen des Fremdschlüssels
Schritt 7: Der Ordnung halber entziehen wir dem Benutzer „kurt“ alle Rechte, bevor wir ihn löschen
(guter Stil und ein sinnvolles Beispiel für REVOKE). Spätestens jetzt müssen wir uns als „root“ anmelden.
REVOKE ALL PRIVILEGES ON dummy.* FROM kurt@localhost
REVOKE USAGE ON *.* FROM kurt@localhost
Schritt 8: Als letztes wird der Benutzer „kurt“ selbst gelöscht.
DROP USER kurt@localhost
Damit wäre der Urzustand wieder hergestellt.
Autor: Stephan Geberl
Stand: 09.06.2009
31/56
SQL (Structured Query Language)
SQL - DML (Data Manipulation Language)
Umfang und Grundschema
Die DML stellt Konstrukte zur Verfügung um einerseits Daten in die Relationen einzufügen, zu ändern
und zu löschen:
INSERT
Erzeugt ein Objekt
UPDATE
Verändert (Elemente) ein Objekt (eines Objektes)
DELETE
Zerstört ein Objekt
und andererseits um Daten aus den Relationen abzufragen:
SELECT
Abfrage einer Relation – gibt das Ergebnis zurück
Beispiel
Gegeben ist folgende Datenbank, deren Relationen zu füllen sind:
FK_Kunde
FK_Reise
ID_Kunde
FK_Zimmer
Vorname
Kunde
ID_Reise
Rabatt
Nachname
1
0/n
Buchung
Reiseziel
0/n
1
0/n
n
ID_Zimmer
FK_Hotel
Zimmernummer
Name
Preis
von
bis
0/1
Zimmer
0/n
verwaltet
ID_Hotel
Reise
verfuegt
FK_Reise
FK_Hotel
1
Hotel
0/m
Die dazu nötigen Create – Anweisungen können dem folgenden Skript entnommen werden:
-- Tabellen löschen wenn vorhanden
DROP TABLE IF EXISTS verfuegt;
DROP TABLE IF EXISTS Buchung;
DROP TABLE IF EXISTS Zimmer;
DROP TABLE IF EXISTS Hotel;
DROP TABLE IF EXISTS Reise;
DROP TABLE IF EXISTS Kunde;
-- Tabellen erzeugen
CREATE TABLE Kunde (
ID_Kunde INT(10) NOT NULL AUTO_INCREMENT,
Vorname VARCHAR(255) NOT NULL DEFAULT "",
Nachname VARCHAR(255) NOT NULL DEFAULT "",
PRIMARY KEY (ID_Kunde)) ENGINE = INNODB;
Autor: Stephan Geberl
Stand: 09.06.2009
32/56
SQL (Structured Query Language)
CREATE TABLE Reise (
ID_Reise INT(10) NOT NULL AUTO_INCREMENT,
Reiseziel VARCHAR(255) NOT NULL DEFAULT "",
Preis DECIMAL(18, 2) NOT NULL DEFAULT 0,
von DATE NOT NULL,
bis DATE NOT NULL,
PRIMARY KEY (ID_Reise)) ENGINE = INNODB;
CREATE TABLE Hotel (
ID_Hotel INT(10) NOT NULL AUTO_INCREMENT,
Name VARCHAR(255) NOT NULL DEFAULT "",
PRIMARY KEY (ID_Hotel)
) ENGINE = INNODB;
CREATE TABLE Zimmer (
ID_Zimmer INT(10) NOT NULL AUTO_INCREMENT,
FK_Hotel INT(10) NOT NULL,
Zimmernummer VARCHAR(255) NOT NULL DEFAULT "100",
CONSTRAINT verwaltet FOREIGN KEY(FK_Hotel)
REFERENCES Hotel(ID_Hotel) ON DELETE CASCADE,
PRIMARY KEY (ID_Zimmer)) ENGINE = INNODB;
CREATE TABLE Buchung (
FK_Kunde INT(10) NOT NULL,
FK_Reise INT(10) NOT NULL,
FK_Zimmer INT(10),
Rabatt DECIMAL(18, 2) NOT NULL DEFAULT 0,
CONSTRAINT sBKunde FOREIGN KEY(FK_Kunde)
REFERENCES Kunde(ID_Kunde) ON DELETE CASCADE,
CONSTRAINT sBReise FOREIGN KEY(FK_Reise)
REFERENCES Reise(ID_Reise) ON DELETE CASCADE,
CONSTRAINT sBZimmer FOREIGN KEY(FK_Zimmer)
REFERENCES Zimmer(ID_Zimmer) ON DELETE SET NULL,
PRIMARY KEY (FK_Kunde, FK_Reise)) ENGINE = INNODB;
CREATE TABLE verfuegt (
FK_Reise INT(10) NOT NULL,
FK_Hotel INT(10) NOT NULL,
CONSTRAINT sVReise FOREIGN KEY(FK_Reise)
REFERENCES Reise(ID_Reise) ON DELETE CASCADE,
CONSTRAINT sVHotel FOREIGN KEY(FK_Hotel)
REFERENCES Hotel(ID_Hotel) ON DELETE CASCADE,
PRIMARY KEY (FK_Reise, FK_Hotel)) ENGINE = INNODB;
Autor: Stephan Geberl
Stand: 09.06.2009
33/56
SQL (Structured Query Language)
Die Tabellen sollen mit folgenden Werten gefüllt werden:
Es stehen die folgenden drei Reisen [reise] zur Verfügung (Berlin, Wien und Paris). Für die einzelnen
Reisen bestehen die folgende Buchungsmöglichkeiten auf die vor Ort verfügbare Hotels und ihre Zimmer zur Verfügung
Folgende Kunden haben Reisen (inclusive Zimmer) gebucht:
Einfügen, Ändern und Löschen von Daten
INSERT – Anweisungen legen neue Datensätze (Tupel) in einer Relation an und können entweder
unter Angabe von Werten (Wertelisten) oder unter Angabe einer SELECT – Anweisung, deren Ergebnisse als Werte übernommen werden, ausgeführt werden.
INSERT INTO Kunde (ID_Kunde, Vorname, Nachname)
VALUES (1, „Hans“, „Muster“),
(2, „Tom“, „Mayer“),
(3, „Eva“, „Müller“);
INSERT INTO Kunde (ID_Kunde, Vorname, Nachname)
SELECT ID_Kunde, Vorname, Nachname FROM Altkunden;
Die explizite Angabe von Attributlisten kann entfallen, wenn die Reihenfolge der Werte in der Werteliste oder SQL – Anweisung mit der Reihenfolge der zu füllenden Attribute übereinstimmt.
Schlüsselfelder, die als autonummerierte Felder definiert sind, kann man unter expliziter Angabe der
Werte (und wenn alle anderen Restriktionen eingehalten sind) überschreiben (MySQL). Dieses Vorgehen ist vor allem bei der Übernahme von Datenbankbeständen (inklusive Beziehungen) nützlich.
UPDATE – Anweisungen Verändern bestehende Datensätze (Tupel) einer Relation.
UPDATE Kunde SET (Nachname = „Hans-Mayer“) WHERE ID_Kunde = 2;
DELETE – Anweisungen löschen bestehende Datensätze (Tupel) einer Relation
Autor: Stephan Geberl
Stand: 09.06.2009
34/56
SQL (Structured Query Language)
DELETE FROM Kunde WHERE ID_Kunde = 3;
Achtung: Wird keine WHERE – Klausel angegeben, bezieht sich die Operation auf alle Datensätze, d.h.
der Inhalt der gesamten Relation wird gelöscht.
Im Beispiel sollen alle Datenbestände mittels INSERT Anweisungen per Batch – Job eingefügt werden.
Bitte beachten Sie, dass Fremdschlüsselwerte erst dann gesetzt werden können, wenn der entsprechende Datensatz mit dem referenzierten Schlüsselwert existiert. Es ist zwar möglich, die Überprüfung
der referenziellen Integrität für die Dauer des Batchlaufes abszuschalten,
abschalten:
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
wieder einschalten:
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
dies ist aber nicht ratsam, da damit auch jede logische Überprüfung der Datensätze verhindert wird.
Die sinnvollere Variante ist es, sich eine funktionierende Reihenfolge zu überlegen.
INSERT INTO Reise(ID_Reise, Reiseziel, Preis, von, bis)
VALUES (1, "Berlin", 300.00, "2007-04-10 12:00:00", "2007-04-20 12:00:00"),
(2, "Wien", 200.00, "2007-08-15 12:00:00", "2007-08-18 12:00:00"),
(3, "Paris", 400.00, "2007-02-01 12:00:00", "2007-02-14 12:00:00");
INSERT INTO Kunde (ID_Kunde, Vorname, Nachname)
VALUES (1, "Georg", "Huber"),
(2, "Karl", "Muster"),
(3, "Stefan", "Meier"),
(4, "Karin", "Bauer"),
(5, "Jessica", "Haller"),
(6, "Tom", "Flugangst"),
(7, "Karl", "Herzkrank");
INSERT INTO Hotel (ID_Hotel, Name)
VALUES (1, "Bahnhof"),
(2, "Astoria"),
(3, "Krone"),
(4, "Muehldorf"),
(5, "Zur Weinlaube");
INSERT INTO Zimmer (ID_Zimmer, FK_Hotel, Zimmernummer)
VALUES (1, 1, 100),
(2, 1, 200),
(3, 2, 44),
(4, 3, 10),
(5, 3, 12),
(6, 4, 501),
(7, 4, 502),
(8, 4, 503),
(9, 5, 4),
(10, 5, 5);
Autor: Stephan Geberl
Stand: 09.06.2009
35/56
SQL (Structured Query Language)
INSERT INTO verfuegt (FK_Reise, FK_Hotel)
VALUES (1, 1),
(1, 3),
(2, 4),
(2, 5),
(3, 2);
INSERT INTO Buchung (FK_Kunde, FK_Reise, FK_Zimmer, Rabatt)
VALUES (1, 1, 1, 0.00),
(1, 3, 3, 0.10),
(2, 1, 2, 0.10),
(3, 2, 9, 0.00),
(3, 1, 4, 0.00),
(4, 2, 6, 0.20),
(5, 2, 7, 0.00);
Abfragen von Daten
Selektion und Projektion
Selektion und Projektion arbeiten auf Tabellen, Abfragen, Sichten etc.
und wählen entweder bestimmte Spalten (Projektion) oder Zeilen
(Selektion) aus. Diese beiden Operationen sind Grundlage aller SQLAbfragen und bestimmen welche Daten einem Benutzer wie präsentiert werden.
Projektion
Selektion
Selektion und Projektion treten in der Praxis meist in Verbindung auf,
sollen hier aber erst einmal getrennt gezeigt werden.
Die Selektion wählt bestimmte Zeilen einer Tabelle aus. Beispielsweise sollen nur die Personen aus
der Tabelle Kunde ausgegeben werden, die „Bauer“ heissen. Die Selektion wird mit der WHERE –
Klausel der SELECT Anweisung realisiert.
SELECT *
FROM kunde
WHERE Nachname=“Bauer“;
Die Projektion wählt bestimmte Spalten einer Tabelle aus. Beispielsweise soll nur Nachname und Firmenname angezeigt werden. Um eine Projektion zu realisieren, müssen die gewählten Felder nach der
SELECT Klausel eingegeben werden (ein * steht dabei für alle Felder)
SELECT Nachname
FROM kunde;
Gibt beispielsweise nur die „Spalte“ Nachname aus.
Nebenbemerkung: Es können auch Felder neu kreiert werden. Dazu können Felder der Tabelle verknüpft, und je nach System Funktionen eingesetzt werden.
SELECT CONCAT(Nachname,' ',Vorname) AS Name
FROM kunde;
Die hier verwendete Funktion CONCAT fügt den Inhalt der übergebenen Strings zusammen. Je nach
SQL – Dialekt und verwendetem Frontend kann diese Operation unterschiedlich aussehen (z.B. & oder
+ als Operatoren etc.)
Parameter für die SELECT - Anweisung
Eine Select Anweisung muss nicht zwingend alle Datensätze zurückgeben. Mit bestimmten Parametern
nach der Anweisung kann die Anzahl der zurückgegebenen Datensätze gesteuert werden. Nachfolgend die verfügbaren Parameter (Beispiele für Jet (Access) und Microsoft SQL-Server).
Autor: Stephan Geberl
Stand: 09.06.2009
36/56
SQL (Structured Query Language)
SELECT [ALL | DISTINCT | DISTINCTROW | [TOP n [PERCENT]]]
Microsoft
SELECT [ALL | DISTINCT | DISTINCTROW ]
MySQL
ALL
Wie gehabt, alle Datensätze werden zurückgegeben (wird meist weggelassen und
nur dann angegeben, wenn man dem Leser der Anweisung mitteilen will, dass alle Datensätze ausgegeben werden sollen.
DISTINCT
Unterdrückt im Sinne der einer SELECT – Anweisung folgenden Feldliste doppelte
Datensätze.
DISTINCTROW
Legt als Kriterium für Dubletten nicht nur die Feldliste sondern alle zurückgegebenen Felder der Datenbank fest (DISTINCTROW ist nur dann wirksam, wenn die
Abfrage mehrere Tabellen umfasst und nicht alle Felder in der SELECT – Anweisung ausgegeben werden).
TOP n [PERCENT]
Gibt nur die obersten n (z.B. 10) oder n Prozent (z.B. 10%) zurück. Achtung: hier
wird die ORDER Klausel wichtig.
Achtung: Die Ausgabe einer Sicht (siehe später), die DISTINCT verwendet, kann nicht aktualisiert
werden, und von anderen Benutzern nachträglich vorgenommene Änderungen werden in der Ausgabe
nicht berücksichtigt.
Im Zweifelsfall steht bei einer unbekannten Datenbank DISTINCT aber nicht DISTINCTROW zur Verfügung. Allgemein sollte mit diesen Klauseln vorsichtig umgegangen werden, da sie nicht von allen
Datenbanken unterstützt werden. Bei der Verwendung von MySQL verwendet man anstatt TOP beispielsweise LIMIT am Ende der Anweisung mit einer entsprechenden Sortierung.
Sortieren mit der ORDER Klausel
Mit ORDER kann eine Sortierung erzwungen werden. Der Order Klausel folgt eine durch Komma getrennte Feldliste. Jedem Feld kann als Parameter ASC (=aufsteigend) oder DESC (=absteigend) mitgegeben werden.
Syntax:
SELECT Feldliste
FROM Tabelle
WHERE Kriterien
ORDER BY Feld1 [ASC | DESC] [, Feldn [ASC | DESC]]
WITH OWNERACCESS OPTION-Deklaration (Microsoft spezifisch)
Mit dieser Option kann einem Benutzer der Abfrage für diese Abfrage dieselben Benutzerrechte eingeräumt werden, die der Ersteller der Abfrage hatte.
SELECT Feldliste
...
WITH OWNERACCESS
Vereinigung (Union) und Schnittmenge (Intersection)
Mit Hilfe der Vereinigung oder Schnittmenge können Teile von (unterschiedlichen) Tabellen (Selektionen, Projektionen, …) mengenmässig verknüpft werden. Die beiden Tabellen T1 und T2 haben dabei (meist) zwar unterschiedliche Inhalte aber ähnliche Strukturen und bilden grundsätzlich den gleichen Entitätstyp ab. Beispiel wären zwei Tabellen die einerseits die aktuellen Kunden und andererseits
den Kundenstamm aus einem Altsystem abbilden.
Vereinigung
UNION
T1
T2
Schnittmenge
IN
NOT IN
T1
T2
T1
T2
INNER JOIN
T1
T2
LEFT JOIN
T1
T2
Join
RIGHT JOIN
T1
T2
OUTER JOIN
T1
T2
Unterschied zum später zu besprechenden Join ist, dass es sich beim Join um eine Verknüpfung zweier (meist nach dem Datenbankschema logich zusammengehörender – dies muss aber nicht zwingend
der Fall sein) Tabellen über „Schlüssel“ und „Fremdschlüssel“ handelt, während bei Union und Intersektion zwei Tabellen (Abfragen, Sichten, …) bezüglich eines Merkmals verglichen werden und daraus
eine dritte Tabelle generiert wird.
Autor: Stephan Geberl
Stand: 09.06.2009
37/56
SQL (Structured Query Language)
Die UNION (Vereinigung) fügt die Zeilen zweier Tabellen mit gleichen Spalten zu einer Tabelle zusammen. Hier anhand des Beispiels zweier Tabellen mit gleichartiger Struktur (K1 und K2).
Kunde
Altkunde
ID
Kunde
ID
Kunde
1
Stephan Geberl
2
Karl Muster
2
Karl Muster
3
Erna Schuster
SELECT ID, Kunde FROM Kunde
UNION
SELECT ID, Kunde FROM Altkunde;
SELECT ID, Kunde FROM Kunde
UNION ALL
SELECT ID, Kunde FROM Altkunde;
Ergebnismenge
Ergebnismenge
ID
Kunde
ID
Kunde
1
Stephan Geberl
1
Stephan Geberl
2
Karl Muster
2
Karl Muster
3
Erna Schuster
2
Karl Muster
3
Erna Schuster
Es werden, wenn Sie eine UNION-Operation verwenden, keine mehrfach vorkommenden Datensätze
zurückgegeben. Das Prädikat ALL führt zur Ausgabe aller (auch der doppelten) Datensätze. Kriterium
für das Finden der doppelten Datensätze ist die im ersten SELECT angegebene Feldliste. Ausserdem
hat ALL zur Folge, dass die Abfrage schneller ausgeführt wird, da die Datensätze nicht gefiltert werden.
Alle Abfragen in einer UNION-Operation müssen gleich viele Ausgabeattribute aufweisen. Diese Felder
müssen aber weder gleich gross sein, noch denselben Datentyp haben. Eine ungleiche Feldanzahl
führt zu einem Fehler. Für die zweite Abfrage kann aber ein * verwendet werden. Alias - Bezeichnungen (AS) (und Feldnamen) werden nur aus der ersten SELECT Anweisung verwendet. Auf diese Namen muss im gesamten weiteren SQL – Statement verwiesen werden (z.B. ORDER BY, …).
Achtung: Wenn in einem UNION Statement kein SELECT – Unterstatement sondern direkt eine Tabelle
verwendet wird, dann muss vor dem Tabellennamen das Schlüsselwort „TABLE“ stehen.
Schnittmengen (Intersektionen) werden auch als verschachtelte SELECT – Anweisungen bezeichnet.
Es geht dabei (in der einfachen Form) immer darum, aus einer Tabelle diejenigen Datensätze herauszusuchen, die in einer zweiten Tabelle enthalten, oder nicht enthalten sind. Vor SQL 92 wurde dazu
explizit die Anweisung INTERSECT verwendet. In den neueren Systemen wird IN oder NOT IN (ab
SQL 92 Intermediate Level auch MINUS SELECT) verwendet (wir beschränken uns hier auf die neuere
und damit verbreitetere Version). Das folgende Beispiel zeigt die Ergebnisse wiederum anhand der
Mustertabellen:
Kunde
Altkunde
ID
Kunde
ID
Kunde
1
Stephan Geberl
2
Karl Muster
2
Karl Muster
3
Erna Schuster
SELECT ID, Kunde FROM Kunde
WHERE ID IN (SELECT ID
FROM Altkunde);
SELECT ID, Kunde FROM Kunde
SELECT ID NOT IN (SELECT ID
FROM Altkunde);
Ergebnismenge
Ergebnismenge
ID
Kunde
ID
Kunde
2
Karl Muster
1
Stephan Geberl
Je nach verwendetem System sind auch noch andere Operatoren möglich (z.B. ANY, ALL, SOME, EXISTS bzw NOT EXISTS).
Autor: Stephan Geberl
Stand: 09.06.2009
38/56
SQL (Structured Query Language)
Bitte beachten Sie, dass z.B. bei einem einfachen Vergleich auch mit einem berechneten Feld der Unterabfrage verglichen werden kann. Diese Variante ist alternativ mit einem Join nicht möglich.
Join
Beim Join geht es darum, durch Schlüssel und Fremdschlüssel verknüpfte Tabellen zu einer Ergebnistabelle zusammenzufassen. Als Beispiel dient uns eine einfache Konstellation, in der eine Kundentabelle und eine Ortstabelle mittels Schlüssel – Fremdschlüssel – Mechanismus verknüpft sind.
Vereinigung
UNION
T1
T2
Schnittmenge
IN
NOT IN
T1
T2
T1
INNER JOIN
T2
T1
T2
LEFT JOIN
T1
Join
RIGHT JOIN
T2
T1
T2
OUTER JOIN
T1
T2
Die einfache Struktur (es wurde zugunsten der Darstellung nicht auf praxistauglichkeit geachtet) soll
das Ergebnis der vier wichtigsten Formen des Join zeigen (Achtung: der OUTER JOIN ist nicht mit
allen Systemen möglich)
Kunde
Ort
wohnt
Kunde
Ort
ID
Kunde
wohnt
ID
Ort
1
Stephan Geberl
2
1
Vaduz
2
Karl Muster
1
2
Balzers
3
Erna Schuster
4
3
Schaan
4
Georg Mayer
null
4
Eschen
Es ist beabsichtigt, dass der Kunde 4 (Georg Mayer) keinen Wohnort zugewiesen bekommt (NULL)
und der Ort Schaan (3) keinem Kunden zugewiesen wurde.
Ein Join würde grundsätzlich bedeuten, dass in einer (virtuellen) Gesamttabelle (Ergebnismenge) zu
jedem Kunden der entsprechende Ort angezeigt wird. Die Angabedatensätze enthalten (absichtlich)
einen Kunden ohne Ort (Georg Mayer) und einen Ort, der keinem Kunden zugeordnet ist.
Für die Verknüpfung der Tabellen kann es so prinzipiell drei verschiedene Varianten geben.
1. Es werden alle Datensätze angezeigt, für die es sowohl einen Kundendatensatz als auch einen
zugeordneten Ortsdatensatz gibt (Inner Join)
2. Es werden alle Kundendatensätze angezeigt und (falls vorhanden) ein Ortsdatensatz (Right
Join)
3. Es werden alle Ortsdatensätze angezeigt und (falls vorhanden) ein Kundendatensatz (Left
Join)
4. Es werden alle Datensätze angezeigt, wobei (falls vorhanden) Attribute nicht zugeordneter
Datensätze auf der jeweils anderen Seite mit NULL gekennzeichnet werden (OUTER JOIN)
Die Variante 1 (Inner Join) kann sowohl mit einem einfachen WHERE, als auch mit einem JOIN - Operator ausgeführt werden.
SELECT Kunde.ID, Kunde.Kunde, Kunde.wohnt, Ort.ID, Ort.Ort
FROM Kunde, Ort WHERE Kunde.wohnt = Ort.ID;
oder
SELECT Kunde.ID, Kunde.Kunde, Kunde.wohnt, Ort.ID, Ort.Ort
FROM Kunde INNER JOIN Ort ON Kunde.wohnt = Ort.ID;
Ergebnismenge:
Autor: Stephan Geberl
Stand: 09.06.2009
39/56
SQL (Structured Query Language)
Ergebnismenge
ID
Kunde
wohnt ID
Ort
1
Stephan Geberl
2
2
Balzers
2
Karl Muster
1
1
Vaduz
3
Erna Schuster
4
4
Eschen
Für diese Form des Joins (Inner Join) ist es übrigens irrelevant, in welcher Reihenfolge Kunde und Ort
verwendet werden (auch wie die Abfrage nach ON aussieht). Um eine Überleitung von der „WHERE –
Form“ zur „JOIN – Form“ zu erleichtern, kann man sich das folgende Schema vor Augen halten.
FROM Kunde , Ort WHERE Kunde.wohnt = Ort.ID
FROM Kunde INNER JOIN Ort ON Kunde.wohnt = Ort.ID
Die Variante 2 und 3 (Right Join, Left Join) kann auf einfachem Weg nur mit einem Join ausgeführt
werden. Beispiel: Right Join (Merkhilfe: Zeige alle Datensätze der Tabelle auf der rechten Seite des
Wortes „Join“)
SELECT Kunde.ID, Kunde.Kunde, Kunde.wohnt, Ort.ID, Ort.Ort
FROM Ort RIGHT JOIN Kunde ON Ort.ID = Kunde.wohnt;
Ergebnismenge:
Ergebnismenge
ID
Kunde
wohnt ID
Ort
1
Stephan Geberl
2
2
Balzers
2
Karl Muster
1
1
Vaduz
3
Erna Schuster
4
4
Eschen
4
Georg Mayer
null
null null
Beispiel: Left Join (Merkhilfe: Zeige alle Datensätze der Tabelle auf der linken Seite des Wortes „Join“)
SELECT Kunde.ID, Kunde.Kunde, Kunde.wohnt, Ort.ID, Ort.Ort
FROM Ort LEFT JOIN Kunde ON Ort.ID = Kunde.wohnt;
Ergebnismenge:
Ergebnismenge
ID
Kunde
wohnt ID
Ort
1
Stephan Geberl
2
2
Balzers
2
Karl Muster
1
1
Vaduz
3
Erna Schuster
4
4
Eschen
null
3
Schaan
null null
Die Variante 4 (OUTER JOIN) gibt alle Datensätze beider Tabellen wieder.
SELECT Kunde.ID, Kunde.Kunde, Kunde.wohnt, Ort.ID, Ort.Ort
FROM Ort OUTER JOIN Kunde ON Ort.ID = Kunde.wohnt;
Ergebnismenge:
Ergebnismenge
ID
Kunde
wohnt ID
Ort
1
Stephan Geberl
2
2
Balzers
2
Karl Muster
1
1
Vaduz
3
Erna Schuster
4
4
Eschen
4
Georg Mayer
null
null null
null
3
null null
Schaan
Die Konstruktion von Joins über zwei Tabellen erscheint noch als relativ einfach, bei der Konstruktion
von Joins über mehrere Tabellen stellt sich das Problem der korrekten Klammersetzung. Aus diesem
Grund wird hier der Variante mit WHERE Abfragen meist der Vorzug gegeben (was allerdings nur für
Autor: Stephan Geberl
Stand: 09.06.2009
40/56
SQL (Structured Query Language)
den INNER JOIN möglich ist). Bei einer klaren, strukturierten Vorgehensweise muss dies aber nicht
sein
Die Konstruktion verknüpfter Joins kann schnell kompliziert werden. Als Beispiel dient uns hier eine
„Hilfstabelle“, die Informationen über die Postleitzahlen enthält. Die SQL – Anweisung mit JOIN sieht
folgendermassen aus (es werden mit * alle Felder ausgegeben).
Kunde
wohnt
hat
Ort
Kunde
Ort
PLZ
PLZ
ID
Kunde
wohnt
ID
Ort
ID
1
Stephan Geberl
2
1
Vaduz
9490 1
2
Karl Muster
1
2
Balzers
9492 4
3
Erna Schuster
4
3
Schaan
9494 3
4
Georg Mayer
null
4
Eschen
9596 2
2
FKOrt
1
SELECT *
FROM (Ort INNER JOIN PLZ ON Ort.ID = PLZ.FKOrt)
INNER JOIN Kunde ON Ort.ID = Kunde.wohnt;
Die erste Klammer nach der FROM – Anweisung verbindet Ort und PLZ, während der darauf folgende
INNER JOIN das Ergebnis mit Kunde verbindet. Die SQL – Anweisung mit WHERE würde in diesem
Fall so aussehen:
SELECT *
FROM PLZ, ORT, Kunde
WHERE Ort.ID = PLZ.FKOrt
AND
Ort.ID = Kunde.wohnt;
Dieses Vorgehen funktioniert nur bei INNER JOINs während die JOIN – Syntax mehr Freiraum zur
Kombination lässt. Achtung: Je nach Datenbank und Entwicklungsumgebung sind allerdings nicht alle
Kombinationen von Joins möglich.
Gruppierungen
Gruppierungen sind Zusammenfassungen von Ergebnismengen (zusammengehörig im Sinne definierter Attribute), die es ermöglichen z.B. Berechnungen für (innerhalb) eine (einer) Gruppe von Datensätzen durchzuführen. Beispiele sind Mittelwertberechnungen, Summen, Maximal- bzw. Minimalwertausgabe. Die verfügbaren (komplexeren) Funktionen hängen dabei vom einzelnen System ab.
Es gilt, dass für alle Ausgabeattribute, ausser denjenigen, nach denen gruppiert wird eine Aggregationsfunktion anzugeben ist. Beispiele für mögliche Funktionen:
Microsoft
MySQL
Avg
AVG
Mittelwert
Count
COUNT
Anzahl Datensätze
First, Last
-
Feldwert erster, letzter Datensatz
Min, Max
MIN / MAX
Maximum, Minimum
StDev
STD / STDDEV
Standardabweichung Stichprobe
Sum
SUM
Summe
Var
VAR_POP
Varianz Stichprobe
Das Kriterienattribut (die Kriterienattribute) werden mit der GROUP BY Klausel festgelegt. Die allgemeine Syntax lautet:
Autor: Stephan Geberl
Stand: 09.06.2009
41/56
SQL (Structured Query Language)
SELECT Feldliste (mit Aggregatfunktionen)
FROM Tabelle
WHERE Kriterien
GROUP BY Gruppenfeldliste
HAVING Kriterien
ORDER BY Feldliste
Für jeden Datensatz der Gruppierung muss ein Ergebniswert pro Feld mit Hilfe von SQL - Aggregatfunktionen berechnet werden (siehe Liste der Funktionen). Ergebniswerte werden nicht angegeben,
wenn die SELECT-Anweisung keine SQL-Aggregatfunktion enthält. Zusätzlich ist bei den Funktionen
jeweils auf die Behandlung von NULL zu achten. NULL - Werte in GROUP BY-Feldern werden grundsätzlich berücksichtigt und gruppiert aber die Auswertung in den Funktionen ist je Funktion unterschiedlich ().
Bezogen auf ein einfaches Beispiel kann die Wirkung der Gruppierung folgendermassen dargestellt
werden:
Umsatzstatistik
SELECT Ort, SUM(Umsatz)
FROM Umsatzstatistik
GROUP BY Ort;
Umsatzstatistik
ID
Ort
Produkt Umsatz
1
Balzers
2
10.000
2
Balzers
1
20.000
3
Balzers
4
10.000
Ort
Umsatz
4
Schaan
1
05.000
Balzers
40.000
5
Schaan
4
10.000
Schaan
15.000
6
Vaduz
4
30.000
Vaduz
40.000
7
Vaduz
2
10.000
Umsatzstatistik
SELECT Produkt, SUM(Umsatz)
FROM Umsatzstatistik
GROUP BY Produkt;
ID
Ort
Produkt Umsatz
1
Balzers
2
10.000
2
Balzers
1
20.000
3
Balzers
4
10.000
Produkt
Umsatz
4
Schaan
1
05.000
1
25.000
5
Schaan
4
10.000
2
20.000
6
Vaduz
4
30.000
4
50.000
7
Vaduz
2
10.000
Umsatzstatistik
Bedenkenswert ist die Reihenfolge von WHERE und HAVING. Die Gruppierung der Datensätze erfolgt
nachdem die Bedingung in der WHERE Klausel überprüft wurde. Erst danach wird die Gruppierung
mittels der HAVING – Klausel eingeschränkt. Eine Abfrage auf den aggregierten Wert (z.B. die Summe) ist also nur mit einem HAVING möglich. Es ist (auch aus Performancegründen) ratsam die nachfolgende HAVING Klausel nicht anstatt der WHERE Klausel einzusetzen, sondern zuerst mit WHERE die
Grundgesamtheit soweit als möglich einzuschränken und dann die HAVING – Klausel einzusetzen, um
eine Auswahl aufgrund von berechneten Werten der Gruppierung zu treffen.
Als Beispiel dient wieder die Umsatztabelle. In Variante 1 geht es darum, nur die Umsätze in die Berechnung miteinzubeziehen, die mehr als 5.000 betragen. Wir fragen also den Grunddatenbestand mit
WHERE ab. Der Datensatz 4 fällt also weg und der Ort Schaan hat nur mehr eine Umsatzsumme von
10.000 (anstatt 15.000). In Variante 2 sollen die Umsatzsummen so gefiltert werden, dass nur mehr
Orte mit einer Umsatzsumme von über 20.000 angezeigt werden. Dazu wird die HAVING – Klausel
verwendet um die Summe des Umsatzes zu filtern.
Autor: Stephan Geberl
Stand: 09.06.2009
42/56
SQL (Structured Query Language)
Variante 1
Umsatzstatistik
SELECT Ort, SUM(Umsatz)
FROM Umsatzstatistik
WHERE Umsatz > 5000
GROUP BY Ort;
ID
Ort
Produkt Umsatz
1
Balzers
2
10.000
2
Balzers
1
20.000
3
Balzers
4
10.000
Ort
4
Schaan
1
05.000
Balzers
40.000
5
Schaan
4
10.000
Schaan
10.000
6
Vaduz
4
30.000
Vaduz
40.000
7
Vaduz
2
10.000
Variante 2
Umsatzstatistik
Umsatzstatistik
Umsatz
SELECT Ort, SUM(Umsatz)
FROM Umsatzstatistik
GROUP BY Ort
HAVING SUM(Umsatz) > 30000;
ID
Ort
Produkt Umsatz
1
Balzers
2
10.000
2
Balzers
1
20.000
3
Balzers
4
10.000
Ort
05.000
Balzers
40.000
Vaduz
40.000
4
Schaan
1
5
Schaan
4
10.000
6
Vaduz
4
30.000
7
Vaduz
2
10.000
Autor: Stephan Geberl
Umsatzstatistik
Stand: 09.06.2009
Umsatz
43/56
Views, Prozeduren, Trigger
Views (Sichten)
Ein View stellt eine logische Sicht auf das Datenbankschema dar (und ist damit dem externen Schema
zuzurechnen), die z.B. für eine bestimmte Anwendung benötigt wird. Views werden bei jeder Abfrage
durch das Datenbankmanagementsystem berechnet und sind im Grunde nichts anderes wie eine im
Datenbankmanagementsystem gespeicherte Abfragen (Alias).
Dialogbetrieb
Anwender
1
Stapelbetrieb
Anwender
n
Externe Sicht
(Externes Schema)
Anwendungsprogramm 1
Konzeptionelle Sicht
(Konzeptionelles Schema)
SQL
(DML)
Anwendungsprogramm n
Anwendungsprogramm 1
Kunde
wohnt
Wohnort
bestellt
Posten
von
Anwendungsprogramm n
Produkt
QL (Query Language)
DML
(Data Manipulation
Language)
DDL
(Data Description
Language)
SQL
(DDL)
Physische Datenorganisation
DSDL
(Data Storage
Description Language)
Interne Sicht
(Internes Schema)
Datenspeicher
Datenspeicher
Die prinzipielle Syntax unter MySQL verhält sich nicht anders, als bei jedem anderen Datenbankobjekt
(CREATE, ALTER, DROP):
CREATE
ALTER
DROP
CREATE [OR REPLACE]
VIEW name [(spaltenliste)]
AS sql-anweisung
ALTER
VIEW name [(spaltenliste)]
AS sql-anweisung
DROP
VIEW [IF XISTS]
name
Views müssen sich nicht unbedingt an die Vorgaben der konzeptionellen Sicht halten (das Datenbankmanagementsystem überprüft das nicht), es ist deshalb darauf zu achten, dass durch Views die
Konsistenz der Datenbank gewahrt bleibt.
Da sich in den meisten Systemen alle Datenbankobjekte den Namensraum teilen, ist es sinnvoll bei
der Vergabe von Viewnamen ein einheitliches Schema einzuhalten. In diesem Beispiel errechnen wir
einen kumulierten Wert über alle Buchungen eines Kunden und speichern diese Abfrage als Sicht ab.
CREATE VIEW v_Kundenwert (Vorname, Nachname, Kundenwert) AS
SELECT Vorname, Nachname, Sum(Reise.Preis-Buchung.Rabatt) AS Kundenwert
FROM Kunde
INNER JOIN Buchung ON Buchung.FK_Kunde = Kunde.ID_Kunde
LEFT JOIN Reise ON Reise.ID_Reise = Buchung.FK_Reise
GROUP BY ID_Kunde;
Views können geschachtelt (also auf Grundlage anderer Views) erzeugt werden und die Daten, die ein
View zur Verfügung stellt, können wenn die zugrundeliegende SQL-Anweisung es erlaubt grundsätzlich verändert werden.
Achtung: Bei praktischer Anwendung sollte die Dokumentation der jeweiligen Datenbank zu Rate gezogen werden (vor allem die hier nicht behandelten Details zur Rechtesteuerung und Sperrverwaltung
bzw. Algorithmus).
Gespeicherte Prozduren und Funktionen
Gespeicherte Prozeduren (Funktionen) sind eine Menge von SQL – Anweisungen, die im Datenbankmanagementsystem gespeichert sind und dort („auf dem Server“) ausgeführt werden. Damit können
Routinen unabhängig von der Programmiersprache der Anwendung implementiert werden und der
Client bzw. die Netzwerkverbindung entlastet werden. Dafür steigt die Serverbelastung.
Prozeduren können Parameter übernehmen und Funktionen können sowohl Parameter übernehmen,
als auch Werte zurückgeben. Für Funktionen und Prozeduren gelten wieder die Befehle CREATE, ALTER und DROP. Die allgemeine Syntax des CREATE Befehls sieht folgendermassen aus (MySQL unter
Auslassung konsultativer Angaben (verschiedene Optionen -> characteristics):
Autor: Stephan Geberl
Stand: 09.06.2009
44/56
Views, Prozeduren, Trigger
CREATE PROCEDURE name ([parameter[,...]])
[verschiedene optionen]
body
CREATE FUNCTION name ([parameter[,...]])
[verschiedene optionen]
RETURNS type
body
parameter (PROCEDURE): [ IN | OUT | INOUT ] name type
parameter (FUNCTION): name type
type: gültiger Datentyp der jeweiligen Datenbank
body: gültiges Statement
Der Rumpf (body) kann aus ein oder mehreren gültigen Statements (SQL – Anweisungen, Aufrufe von
Prozeduren, Konstrukte der Ablaufsteuerung) bestehen. Wenn mehr als ein Statement verwendet
wird, muss der Rumpf mittels BEGIN … END abgegrenzt werden. Bei einer Funktionsdefinition muss
der Rumpf mindestens ein RETURN – Statement enthalten, dass den vereinbarten Datentyp zurückgibt.
Beispiel: Für das Flugbuchungssystem soll eine Prozedur programmiert werden, die es ermöglicht
unter Angabe des Vor- und Nachnamens und der Flugnummer eine komplette Flugbuchung anzulegen:
Schritt 1: Eine Funktion soll falls der Passagier existiert seinen Datenbankschlüssel zurückgeben, ansonsten NULL:
DELIMITER $$
DROP FUNCTION IF EXISTS reise.getKundenNummer $$
CREATE FUNCTION reise.getKundenNummer(pvorname varchar(255),
pnachname varchar(255))
RETURNS int(11)
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE pnum int(11);
SELECT kunde.ID_Kunde INTO pnum
FROM kunde
WHERE kunde.Vorname = pvorname
AND kunde.Nachname = pnachname;
RETURN pnum;
END $$
DELIMITER ;
Aufruf erfolgt z.B. mit
SELECT getKundenNummer("Karl", "Muster");
Zu Anfang der Definition muss der Standarddelimiter von „;“ auf $$ (oder einen anderen sinnvollen
Wert) umgestellt werden, da wir den „;“ im Code verwenden und dies bei Abarbeitung der SQL - Anweisung CREATE FUNCTION … ansonsten zu einem Abbruch führen würde.
Nebenbemerkung: Eine Prozedur oder Funktion gilt als „deterministisch“, wenn sie für gleiche Eingabeparameter immer gleiche Resultate erzeugt; ansonsten ist sie „nichtdeterministisch“. Wenn in der
Definition der Routine weder DETERMINISTIC noch NOT DETERMINISTIC steht, ist die Voreinstellung
NOT DETERMINISTIC.
Autor: Stephan Geberl
Stand: 09.06.2009
45/56
Views, Prozeduren, Trigger
Schritt 2: Je nachdem, ob der Passagier angelegt ist, wird nur die Buchung erzeugt, oder Buchung
und Passagier:
DELIMITER $$
DROP PROCEDURE IF EXISTS reise.doBooking $$
CREATE PROCEDURE doBooking(pvor varchar(255), pnach varchar(255),
preise int(11))
BEGIN
DECLARE pnum int(11);
SET AUTOCOMMIT = 0;
START TRANSACTION;
IF ISNULL(reise.getKundenNummer(pvor, pnach)) THEN
INSERT INTO reise.kunde (Vorname, Nachname)
VALUES (pvor, pnach);
END IF;
SELECT reise.getKundenNummer(pvor, pnach) INTO pnum;
INSERT INTO reise.buchung (FK_Kunde, FK_Reise, FK_Zimmer, Rabatt)
VALUES (pnum, preise, NULL, 0.00);
COMMIT;
SET AUTOCOMMIT = 1;
END $$
DELIMITER ;
Aufruf erfolgt mit
CALL doBooking("Stephan", "Geberl", 3);
Achtung: über SQL werden Prozeduren mittels CALL und Funktionen innerhalb eines SELECT aufgerufen. Die Eingabe von Prozeduren/Funktionen ist über das Webfrontend nicht Trivial, da der Delimiter
„;“ unterdrückt werden muss um eine korrekte Abarbeitung zu ermöglichen. In den vorgestellten Lösungsbeispielen werden auch keinerlei Vorkehrungen gegen Fehler bei der Abarbeitung getroffen
(Transaktionen – rot und fett markierte Befehle). Bei praktischer Anwendung gilt dasselbe wie bei
Views: Konsultation der jeweiligen Dokumentation ist obligatorisch
Trigger
Datenbanktrigger stellen eine Möglichkeit dar, Aktionen vor oder nach bestimmten Operationen auf
eine Datenbank aufzurufen. Trigger können über folgende (grobe) Syntax erzeugt oder gelöscht werden.
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER name time event
ON tabelle FOR EACH ROW aktion
DROP TRIGGER name
Sollte eine Anweisung als aktion nicht genügen, kann mittels BEGIN … END ein Programmblock gebildet werden.
Die (in MySQL) definierten Operationen (event) sind INSERT, UPDATE, DELETE wobei die Aktion entweder immer (time) davor (BEFORE) oder danach (AFTER) eintritt.
Beispielsweise könnte für jede Veränderung an den Inhalten der Tabelle Passagier ein Eintrag in die
Tabelle Passagierlog getätigt werden:
CREATE TRIGGER doLog
AFTER UPDATE ON Passagier
FOR EACH ROW
INSERT INTO Log (text)
VALUES ("Eine Aenderung");
Autor: Stephan Geberl
Stand: 09.06.2009
46/56
Views, Prozeduren, Trigger
Der Vollständigkeit halber: Create für die Logtabelle
CREATE TABLE Log (
ID_Log INT(10) NOT NULL AUTO_INCREMENT,
text VARCHAR(255) DEFAULT "",
PRIMARY KEY (ID_Log) ) ENGINE = INNODB;
Trigger sind auch eine Alternative durch die Kontrolle der referentiellen Integrität durch das Datenbankverwaltungssystem, wobei zu beachten ist, dass ein Trigger immer einen Schritt später einsetzt
als die interne (DBMS) Kontrolle. Dafür können komplexere Bearbeitungen vorgenommen werden, als
dies mit CONSTRAINTS (Schlüssel – Fremdschlüssel) möglich wäre.
Autor: Stephan Geberl
Stand: 09.06.2009
47/56
Transaktionen
Tansaktionen
Grundlagen
Als Transaktion wird eine Folge von Operationen (auf eine Datenbank) bezeichnet, die als logische
Einheit betrachtet wird, und folgende Bedingungen erfüllt (ACID – Prinzip7):
•
Atomarität (Atomicity): Eine Transaktion wird entweder ganz oder gar nicht ausgeführt.
Transaktionen sind also „unteilbar“.
•
Konsistenz (Consistency): Vor und nach Ausführung der Transaktion ist der Datenbestand
in einer widerspruchsfreien (konsistenten) Form. Widerspruchsfrei bedeutet, dass Daten, die
mehrfach an verschiedenen Stellen (redundant) gespeichert sind, den gleichen Inhalt haben.
•
Isolation (Isolation): Bei gleichzeitiger Ausführung mehrerer Transaktionen dürfen sich
diese nicht gegenseitig beeinflussen.
•
Dauerhaftigkeit (Durability): Die Auswirkungen einer Transaktion müssen im Datenbestand dauerhaft bestehen bleiben (gespeichert werden).
Eine grundlegende Transaktion (bei der die ACID – Bedingung zutreffen sollte) ist eine SQL - Anweisung. Bei vielen modernen Datenbanksytemen können aber auch mehrere Anweisungen explizit zu
einer Transaktion zusammengefasst werden.
Transaktionen werden von Transaktionssystemen verwaltet (die in den meisten Datenbanksystemen
implementiert sind). Das Transaktionssystem überprüft die Einhaltung der ACID – Bedingungen z.B.
bei paralleler Abarbeitung von Transaktionen (Isolationskriterium).
Beispiel
Zur Veranschaulichung dient uns eine konventionelle Bibliothek, die auf Karteikarten beruht8: Eine
Transaktion könnte hier lauten:
Leihe das Buch „Die Schatzinsel“ an den Benutzer Peter Müller aus.
Die Verfahrensanweisung für den Bibliothekar würde dabei folgendermassen aussehen:
Beginn der Transaktion
lies das Feld "Vorbestellung" der Karte
schreibe "Peter Müller" in das Feld "ausgeliehen an"
schreibe "29. Juli 2001" in das Feld "Rückgabe am"
Ende der Transaktion
Ist die Karte erfolgreich ausgefüllt, wird sie wieder in den Karteikasten zurückgesteckt (Dauerhaftigkeit). Wenn der Vorgang durch irgendeinen Grund unterbrochen wurde, müssen alle bisherigen Änderungen rückgängig gemacht werden bevor die Karte wieder zurückgesteckt werden kann (Atomarität, Konsistenz). Zudem muss sichergestellt sein (hier physisch, durch die Karteikarte), dass nicht
an einer anderen Stelle der Bibliothek das Buch an einen zweiten Benutzer ausgeliehen wird (Isolation).
Umsetzung unter MySQL
Anweisungen, die Tabellen, etc. erstellen, löschen oder abändern können nicht rückgängig gemacht
werden, sie sollten daher nicht in einer Transaktion verwendet werden. Um Transaktionen nutzen zu
können, muss zudem eine transaktionssichere Engine gewählt werden (z.B. InnoDB, BDB).
Diverse Anweisungen verursachen zudem einen impliziten Commit (siehe Dokumentation) dies gilt vor
allem, wenn versucht wird eine weitere (geschachtelte) Transaktion zu eröffnen (unter MySQL sind
keine geschachtelten Transaktionen möglich). MySQL arbeitet in der Standardeinstellung im sogenannten Autocommit – Modus. Das heisst, alle Aktionen auf die Datenbank werden sofort gespeichert.
Um Transaktionen verwenden zu können, muss mittels
SET AUTOCOMMIT=0;
dieser Modus zuerst abgeschaltet werden (bitte nicht vergessen ihn wieder einzuschalten - SET AUTOCOMMIT=1;) Für den Datenbank- (SQL-) Programmierer sind dann (meist) nur drei Befehle von
Interesse (für Optionen siehe Dokumentation MySQL):
7
http://de.wikipedia.org/wiki/Transaktion_(Informatik) (25.09.2006)
8
http://de.wikipedia.org/wiki/Transaktion_(Informatik) (25.09.2006)
Autor: Stephan Geberl
Stand: 09.06.2009
48/56
Transaktionen
Start der Transaktion:
START TRANSACTION
Erfolgreiches Beenden:
COMMIT
Zurücksetzen (Fehler):
ROLLBACK
Ein neueres Konzept stellen die XA – Transaktionen dar, die grob gesprochen globale Transaktionen
verwalten, die wiederum mehrere lokale Transaktionen beinhalten.
InnoDB – Besonderheit: InnoDB unterstützt die SQL-Anweisungen SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT und das optionale Schlüsselwort WORK für ROLLBACK. Die Anweisung
ROLLBACK TO SAVEPOINT macht eine Transaktion bis zum benannten Speicherpunkt rückgängig
(weitere Informationen siehe Dokumentation MySQL)
Alternativen zu Transaktionen: Transaktionen können mittels LOCK TABLES und UNLOCK TABLES
„von Hand“ zumindestens emuliert werden (falls die Engine nicht transaktionsfähig ist). Durch dieses
Vorgehen wird die Tabelle aber als Gesamtes gesperrt. Zusätzlich steigt mit dieser Methode die Wahrscheinlichkeit Fehler zu begehen (und sei es nur z.B. die Freigabe einer Tabelle zu vergessen).
Isolation - Isolation Level:
Mögliche Anomalien9
Lost Update: Überschneidung von Transaktionen: Lost Update entsteht beispielsweise bei Buchungsaktionen. Ausgangspunkt sind 80 freie Plätze. Mit Transaktion 1 wird beispielsweise die Buchung von fünf Plätzen vorgenommen, das Ergebnis (75 freie Plätze) jedoch erst beim Commit in die
Datenbank geschrieben. Tritt davor Transaktion 2 ein, beispielsweise das Stornieren von vier Plätzen,
so arbeitet Transaktion 2 mit den ursprünglich vorhandenen 80 Plätzen, und erhöht diese auf 84. In
diesem Moment speichert Transaktion 1 seine 75 Plätze und damit ist das Ergebnis der Transaktion 1
überschrieben. Anstatt korrekterweise 79 Restplätzen stehen nun im System 84 Restplätze.
Zeit
t1
t2
t3
t4
-5
t5
t6
Lost Update
Transaktion 1
commit
80 80 75 84 84
Transaktion 2
commit
+4
Anzahl freier Plätze
korrekter Wert: 79
Dirty Read: Lesen ungesicherter Werte - Es können von anderen Transaktion geschriebene Daten
gelesen werden, für die noch kein "Commit" erfolgt ist, und die eventuell per "Rollback" zurückgesetzt
werden können. Wenn beispielsweise Transaktion 1 über eine Buchung die Anzahl der Plätze um 5
vermindert und diesen Wert in die Datenbank schreibt, dann liest Transaktion 2 diesen Wert und arbeitet mit ihm weiter indem es ihn beispielsweise um 4 vermindert. Wenn die Transaktion 1 nach der
Leseaktion von Transaktion 2 einen Rollback durchführt (z.B. Benutzerabbruch), hat Transaktion 2
einen falschen Wert gelesen (Dirty Read) und schreibt bei seinem Commit anstatt der jetzt korrekten
76 Restplätze, 71 Restplätze in die Datenbank. Er Rollback von Transaktion 1 wurde nicht berücksichtigt.
Zeit
t1
t2
t3
t4
t5
t6
-5
Transaktion 1
rollback
write
80 75 80 71 71
Transaktion 2
Dirty Read
commit
-4
Anzahl freier Plätze
korrekter Wert: 76
Non-repeatable Read: Stale Data, nichtwiederholbares Lesen - Während einer laufenden Transaktion können Daten von anderen Transaktionen geändert und committed werden, so dass in der ersten
Transaktion ein zweites Auslesen zu anderen Daten führt. Liest Transaktion 1 beim ersten mal 80
Restplätze und Transaktion 2 ändert diesen Wert, dann werden bei einem abermaligen Lesen von
9
Beispiele aus: http://www.it-infothek.de/fhtw/semester_8/db_anwendungen_02.html und
http://www.torsten-horn.de/techdocs/jee-transaktionen.htm
Autor: Stephan Geberl
Stand: 09.06.2009
49/56
Transaktionen
Transaktion 1 nur mehr 76 Restplätze zurückgegeben. Problematisch kann dieser Fall dann werden,
wenn z.B. Transaktion 1 in weiterer Folge Werte schreibt, die einfluss auf die Integrität der Datenbank
haben (z.B. Fremdschlüssel).
Zeit
t1
t2
t3
80
t4
t5
76
t6
Non-repeatable Read
Transaktion 1
read
read
80 80 76 76 76
Transaktion 2
-4
Anzahl freier Plätze
commit
Phantom Read (Sonderfall des Non-repeatable Read): Werden anstatt Werten Datensätze gelesen,
kann es analog dem non-repeatable Read dazu kommen, dass bei unterschiedlichen Leseoperationen
eine unterschiedliche Anzahl von Datensätzen zurückgegeben werden. Der in diesem Beispiel von
Transaktion 2 erzeugte, und bei der zweiten Leseaktion von Transaktion 1 gelesene Datensatz wird
dann als Phantom bezeichnet.
Zeit
t1
t2
t3
3 Datensätze
t4
t5
t6
4 Datensätze
Transaktion 1
read
read
Phantom Read
Anzahl Datensätze
Transaktion 2
commit
+1 Datensatz
Transaction Isolation Level
In vielen Datenbanken können zur Verhinderung von Anomalien verschiedene „Transaction Isolation
Level“ eingestellt werden. Je restriktiver die Einstellung, desto mehr an Ressourcen verbraucht das
DBMS in der Regel. Folgende "Transaction Isolation Level" sind in ANSI-SQL2 definiert:
Dirty
Read
Non-Repeatable
Read
Phantom
Read
möglich
möglich
möglich
Read Committed
unmöglich
möglich
möglich
Repeatable Read
unmöglich
unmöglich
möglich
Serializable
unmöglich
unmöglich
unmöglich
Isolationsebene
Read Uncommitted
Der Fall des Lost Update ist nur zu verhindern, wenn alle Transaktionen streng nacheinander ausgeführt werden (SERIALIZABLE) oder der Datensatz während der gesamten Transaktion gesperrt bleibt
(REPEATABLE-READ plus LOCK). Alle anderen Anomalien können mit steigendem Ressourcenaufwand (Performanceeinbusse) unterbunden werden. Unter MySQL werden die Einstellungen wie folgt
vorgenommen:
transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED
| REPEATABLE-READ | SERIALIZABLE}
Die Defaulteinstellung ist üblicherweise REPEATABLE-READ.
Bei READ-UNCOMMITTED findet praktisch keine Isolation statt. Änderungen während anderer Transaktionen sind sofort sichtbar, auch wenn diese noch keinen Commit abgesetzt haben. READCOMMITTED implementiert den Commit, Daten, die nicht Committed wurden, werden für andere
Transaktionen nie sichtbar. Bei den beiden Transaktionsebenen REPEATABLE-READ und SERIALIZABLE bewirken, dass eine Transaktion immer (auch bei mehrmaligen Lesen) nur die Daten sieht, die
zu ihrem Start vorhanden waren. Die Isolationsebene SERIALIZABLE bewirkt, dass parallel ablaufende
Transaktionen genau so abgearbeitet werden, als würden sie seriell (nacheinander) abgearbeitet (dies
kann zu Fehlern führen, mit denen die Transaktion umgehen muss).
Autor: Stephan Geberl
Stand: 09.06.2009
50/56
Cursorkonzept
Cursorkonzept
Allgemeines
Die Bezeichnung Cursor (Current Set of Records) ist in einigen Datenbanken gebräuchlich und bezeichnet ein Datenbankobjekt, das die aktuell ausgewählten (z.B. SELECT – Statement) Datensätze
enthält, und auf das (z.B. mittels Programmiersprachen) Operationen ausgeführt werden können.
Übliche Operationen sind das Bewegen innerhalb der Datensätze eines Cursors, das Löschen, Hinzufügen und Verändern von Datensätzen.
Die Bschreibung des Umgangs mit einem Datenbankcursor erfolgt hier auszugsweise anhand der Programmiersprache JAVA (bzw. deren Klassenbibliothek). In jeder anderen Programmiersprache ist das
Vorgehen ähnlich.
Architektur der Datenbankanbindung (JDBC)
Der Zugriff auf Datenbanken erfolgt grundsätzlich über den JDBC – Driver Manager (java.sql.* der
Java SE – Klassenbibliothek), der hauptsächlich Schnittstellen zu einem vom Datenbankhersteller zur
Verfügung gestellten Treiber enthält.
VB/C ... Programm
ADO
Java Programm
OLE
ODBC
JDBC - Driver Manager
JDBC - ODBC
Bridge Driver
Native
Driver
(teilweise)
über API
Native
Driver
(vollständig)
über DBMSProtokoll
JDBC-Net
Driver
JDBC
Middleware
Protokoll
Analog der unter Windows üblichen Programmiersprachen (C, VB, etc) kann der Zugriff auf die Datenbankengine über das ODBC – System erfolgen oder aber es können diverse Treiberkonfigurationen
(siehe Bild oben) gewählt werden. Wir verwenden den Native – Treiber der MySQL – Datenbank (unter www.mysql.com) um auf eine Testdatenbank mit folgenden Eckwerten zugreifen zu können:
Es existiert eine Datenbank auf dem lokalen Rechner (localhost) mit dem Schema (der
Datenbank "test". Innerhalb des Schemas existiert eine Tabelle mit Namen „person“,
die folgende Felder besitzt:
Nachname
VARCHAR(100); Schlüssel
Vorname
VARCHAR(100);
Der Benutzer „test“ hat das Passwort „passtest“ und vollen Zugriff auf diese Tabelle.
CREATE TABLE person (
Nachname VARCHAR(100) DEFAULT '',
Vorname VARCHAR(100) DEFAULT '',
PRIMARY KEY (Nachname)
)ENGINE = INNODB;
INSERT INTO person (Nachname, Vorname)
VALUES ('Geberl', 'Stephan'),
('Muster', 'Hans'),
('Müller', 'Elisabeth'),
('Mayer', 'Georg');
Bei Veränderung der Parameter müssen die entsprechenden Werte im Code angepasst werden.
Autor: Stephan Geberl
Stand: 09.06.2009
51/56
Cursorkonzept
Nutzung der Treiber
Datenbankverbindung herstellen und Abfragen absetzen
Bedingungen: Treiber der jeweiligen Datenbank muss zur Verfügung stehen, was bedeutet, dass
die entsprechenden .jar Datei(en) in einem von der Runtime – Installation erreichbaren Pfad liegen
müssen (Projektverzeichnis oder ein Verzeichnis, dass im CLASSPATH angegeben ist). Zusätzlich muss
zumindest das Paket java.sql.* importiert werden, dass die Schnittstellendefinitionen enthält. Aufbau
und Arbeit mit einer Datenbankverbindung laufen dabei in 5 Schritten ab:
1. Treiber bekannt machen (Laden, Instanzieren)
2. Verbindung öffnen
3. Abfrage an die Datenbank absenden
4. Resultate (Fehler) verarbeiten (wenn nötig)
5. Verbindung schliessen
Der Schritt 4 kann zumindest auf Resultate nur dann zugreifen wenn die Abfrage Resultate zurückliefert. Resultate werden als CURSOR – Objekt (CURrent Set Of Records) zurückgeliefert. Im Einzelnen
werden die Schritte wie folgt programmiert.
Schritt 1:
Laden des Treibers (Objekt erzeugen):
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
System.out.println("Datenbanktreiber für MySQL geladen");
} catch (ClassNotFoundException a) {
System.out.println( "Die Klasse wurde nicht gefunden."); }
catch (InstantiationException b) {
System.out.println( "Die Klasse wurde nicht instanziert."); }
catch (IllegalAccessException c) {
System.out.println( "kein Zugriff."); };
Eine Instanzierung (getInstance()) ist nicht immer nötig, da die wichtigsten Methoden als static deklariert sind.
Schritt 2:
Verbindung öffnen (Verbindungszeichenfolge für MySQL:
"jdbc:mysql://geberl.dynalias.org/test?user=xxxx&password=????")
Portnummer im Normalfall 3306
Achtung: import java.sql.*; nicht vergessen
java.sql.Connection itsConnection;
String aConnectString = new
String("jdbc:mysql://localhost/test?user=test&password=passtest");
try {
itsConnection = DriverManager.getConnection(aConnectString);
System.out.println("Verbindung geöffnet");
} catch (SQLException a) {
System.out.println("Verbindung nicht geöffnet"); }
Damit steht eine Verbindung zur Datenbank. Achtung: Bitte nicht vergessen sie wieder abzubauen.
Schritt 3:
SQL – Kommandos absetzen. SQL – Statements werden über ein Statement – Objekt
abgebildet und abgesetzt. Der Befehl executeUpdate dient dazu, Statements ohne
Rückgabedaten abzusetzen während executeQuery Datensätze zurückgibt, die in
einem ResultSet gespeichert sind.
Die erste Abfrage schreibt einen Datensatz in die Tabelle „person“ während die zweite Abfrage alle
Datensätze aus der Tabelle als Cursor zurückgibt:
try {
itsConnection = DriverManager.getConnection(aConnectString);
System.out.println("Verbindung geöffnet");
Statement aStatement = itsConnection.createStatement();
aStatement.executeUpdate("INSERT INTO person VALUES (‘Geberl’, 'Stephan')");
ResultSet aResultSet = aStatement.executeQuery("SELECT * FROM person");
} catch (SQLException a) {
System.out.println("Execute fehlgeschlagen"); }
Autor: Stephan Geberl
Stand: 09.06.2009
52/56
Cursorkonzept
Die Methoden addBatch und executeBatch dienen dazu, mehrere Statements auf einmal zu übergeben und auszuführen. Das ResultSet – Objekt enthält in strukturierter Form alle Datensätze der
Tabelle. Eine rudimentäre Ausgabe auf die Konsole kann folgendermassen realisiert werden.
while (aResultSet.next()) {
String nachname = aResultSet.getString("Nachname");
String vorname = aResultSet.getString("Vorname");
System.out.println("Satz: " + nachname + " ... " + vorname );
}
Je nachdem, mit welchen Parametern das Statement – Objekt instanziert wird, können Veränderungen wieder in die Datenbank zurückgeschrieben werden.
Types – Möglichkeiten sich im ResultSet zu bewegen
TYPE_FORWARD_ONLY
nur Vorwärtsbewegung möglich, Änderungen in der
zugrunde liegenden Datenquelle werden ignoriert
TYPE_SCROLL_INSENSITIVE
alle Bewegungsrichtungen möglich, Änderungen in der
zugrunde liegenden Datenquelle werden ignoriert
TYPE_SCROLL_SENSITIVE
alle Bewegungsrichtungen möglich, Änderungen in der
zugrunde liegenden Datenquelle werden aktualisiert
Concurrency – Änderungen in der zugrunde liegenden Datenquelle
CONCUR_READ_ONLY
Änderungen können nicht an die zugrunde liegende Datenbank weitergegeben werden
CONCUR_UPDATABLE
Änderungen können an die zugrunde liegende Datenbank
weitergegeben werden
Holdability – Status des ResultSets nach einem Commit
HOLD_CURSORS_OVER_COMMIT
ResultSet wird bei einem Commit nicht geschlossen
CLOSE_CURSORS_AT_COMMIT
ResultSet wird bei einem Commit geschlossen
Einstellen des ResultSet
Die Parameter des ResultSet werden bei Instanzierung des Statement-Objektes angegeben. Beispiel:
Statement stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY,
ResultSet.CLOSE_CURSORS_AT_COMMIT);
Schritt 4:
Resultate verarbeiten (Fehler analysieren):
Um Fehler bei der Verarbeitung zu analysieren dient das SQLException – Objekt. Alle Operationen auf
die Datenbank sind daher unbedingt mit einer Fehlerbehandlungsroutine zu versehen.
Try
{
// Hier die Methoden
} catch (SQLException a) {}
Im Gegensatz zu analogen Modellen in anderen Programmiersprachen existieren auch Positionen vor
dem ersten Datensatz und nach dem ersten Datensatz sowie eine Einfügeposition. Wenn das ResultSet geöffnet ist, steht der Zeiger also vor dem ersten Datensatz. Will man den ersten Datensatz auslesen, muss man zuerst mit der Methode next() den Zeiger setzen. Next dient zum Bewegen des Zeigers nach abwärts. Um sich im ResultSet zu bewegen, stehen weiters folgende Methoden zur
Verfügung:
Beispiel: Anzahl der im ResultSet enthaltenen Daten:
first() previous()
last()
beforeFirst()
next()
afterLast()
itsResultSet.last();
// Nummer des letzten Datensatzes
int itsAktNumRows = itsResultSet.getRow();
itsResultSet.first();
// Nummer des ersten Datensatzes
int itsAktRow = itsResultSet.getRow();
absolut(int row)
relative(int rows)
Autor: Stephan Geberl
Stand: 09.06.2009
53/56
Cursorkonzept
Daten Aus- und Einlesen
Attributwerte können nach folgendem Schema gelesen und geschrieben werden.
Lesen:
itsResultSet.getXXX(Feldname oder Feldnummer von links);
z.B. itsResultSet.getInt(1);
Schreiben:
itsResultSet.updateXXX(Feldname oder Feldnummer von links, Feldwert);
Folgendes Codefragment gibt also nach Aufbau der Verbindung den ersten Datensatz aus:
aResultSet.next();
String nachname = aResultSet.getString("Nachname");
String vorname = aResultSet.getString("Vorname");
System.out.println("Satz: " + nachname + " ... " + vorname );
Daten schreiben
Über die entsprechenden updateRow – Methoden können die Attribute verändert werden und dann
mittels update wieder in die Datenbank geschrieben werden (Achtung: das ResultSet muss natürlich
unter Verwendung von Parametern geöffnet sein, die eine Veränderung der zugrundeliegenden Datenbank zulassen).
aResultSet.next();
String nachname = aResultSet.getString("Nachname");
String vorname = aResultSet.getString("Vorname");
System.out.println("Satz: " + nachname + " ... " + vorname );
aResultSet.updateString("Vorname", "GUGUS");
aResultSet.updateRow();
String xnachname = aResultSet.getString("Nachname");
String xvorname = aResultSet.getString("Vorname");
System.out.println("Neuer Satz: " + xnachname + " ... " + xvorname );
Daten löschen
Die Datenzeile kann auch gelöscht werden. Bitte beachten Sie, dass der Datensatz nach wie vor mittels der get – Methoden angezeigt werden kann. Erst eine Neupositionierung des Zeigers spiegelt den
neuen Zustand wieder.
aResultSet.deleteRow();
aResultSet.first();
Datenzeilen anlegen
Um Datenzeilen anzulegen, muss der Zeiger zuerst zr InsertRow bewegt und dann mittels insertRow
die Feldwerte geschrieben werden. Der Befehl moveToCurrentRow bewegt den Zeiger auf die Zeile,
auf dem er vor dem Einfügevorgang stand.
aResultSet.moveToInsertRow();
aResultSet.updateString("Nachname", "Test");
aResultSet.updateString("Vorname", "Georg");
aResultSet.insertRow();
aResultSet.moveToCurrentRow(); // je nach Anwendung
Schritt 5:
Verbindung schliessen:
Abschliessend sind die Objekte wieder zu entsorgen und die Datenbankverbindung abzubauen.
aResultSet.close();
aStatement.close();
itsConnection.close();
Das Beispiel zeigt natürlich nur einen kleinen Ausschnitt zu diesem Thema und soll lediglich exemplarisch den Umgang mit einem Datenbankcursor erklären.
Autor: Stephan Geberl
Stand: 09.06.2009
54/56
Literatur
Literatur- und Quellenverzeichnis
MARKUS LUSTI:
Dateien und Datenbanken, Springer Berlin 1997
ANDREAS MEIER:
Relationale Datenbanken, Springer Berlin 1995
HEINZ-GERD RAYMANS:
SQL, Addison-Wesley München 2002
A.-W. SCHEER:
ARIS, Springer Berlin 1998
STAHLKNECHT, HASENKAMP:
Einführung in die Wirtschaftsinformatik, Springer Berlin 2005
Internetverzeichnis
-
http://www.wikipedia.org
-
http://www.it-infothek.de/fhtw/semester_8/db_anwendungen_02.html
-
http://www.torsten-horn.de/techdocs/jee-transaktionen.htm
Autor: Stephan Geberl
Stand: 09.06.2009
55/56