SQL/Datendefinition
Datendefinition
Datenbanktabelle (CREATE TABLE
)
Die Datenbanktabelle Vorlesung
kann mit der folgenden Anweisung erzeugt werden:
CREATE TABLE Vorlesung (VorlNr INT NOT NULL PRIMARY KEY, Titel VARCHAR NOT NULL, PersNr NOT NULL, FOREIGN KEY (PersNr) REFERENCES Professor (PersNr));
In keinem der Felder VorlNr
, Titel
, PersNr
ist der Wert NULL
erlaubt. Der Fremdschlüssel PersNr
referenziert den Primärschlüssel PersNr
der Tabelle Professor
. Damit wird sichergestellt, dass in die Tabelle Vorlesung
nur Datensätze eingefügt werden können, für die der Wert von PersNr
in der Tabelle Professor
als Primärschlüssel vorkommt (siehe referenzielle Integrität).
Datenbankindex (CREATE INDEX
)
Mit der Anweisung
CREATE INDEX VorlesungIndex
ON Vorlesung (PersNr, Titel);
kann ein Datenbankindex für die Tabelle Vorlesung
definiert werden, der vom Datenbanksystem bei der Ausführung von Abfragen zur Beschleunigung verwendet werden kann. Ob das sinnvoll ist, entscheidet das Datenbanksystem eigenständig durch komplexe Auswertungen und Analysen für jede Abfrage erneut.
Sicht (CREATE VIEW
)
Eine Sicht ist im Wesentlichen ein Alias für eine Datenbankabfrage. Sie kann wie eine Datenbanktabelle verwendet werden. Die Anweisung
CREATE VIEW Vorlesungssicht AS
SELECT Vorlesung.VorlNr,
Vorlesung.Titel,
Professor.PersNr,
Professor.Name
FROM Professor
INNER JOIN Vorlesung
ON Professor.PersNr = Vorlesung.PersNr;
speichert die definierte Abfrage als Sicht. Die Abfrage
SELECT Titel,
Name
FROM Vorlesungssicht
WHERE VorlNr < 5000;
verwendet diese Sicht und könnte zum Beispiel folgendes Ergebnis liefern:
Titel
|
Name
|
---|---|
Softwareentwicklung 1
|
Wirth
|
Netzwerke 1
|
Urlauber
|
Algorithmen
|
Wirth
|
Datenbanken
|
Urlauber
|
Zusammenfassung von CREATE TABLE
, CREATE INDEX
und CREATE VIEW
Zusammengefasst sind die wichtigsten Elemente der Definition einer Datenbanktabelle, eines Datenbankindex oder einer Sicht wie folgt anzugeben:
CREATE TABLE Tabellenname (Attributdefinition [PRIMARY KEY]) [, FOREIGN KEY (Attributliste) REFERENCES Tabellenname (Attributliste)]);
DROP TABLE Tabellenname;
ALTER TABLE Tabellenname (Attributdefinition [PRIMARY KEY]) [, FOREIGN KEY (Attributliste) REFERENCES Tabellenname (Attributliste)]);
CREATE INDEX Indexname ON Tabellenname (Attributliste);
DROP INDEX Indexname;
CREATE VIEW Sichtname [(Attributliste)] AS SELECT <Auswahlkriterien>;
DROP VIEW Sichtname;
Redundanz
Ein Grundsatz des Datenbankdesigns ist, dass in einer Datenbank keine Redundanzen auftreten sollen. Dies bedeutet, dass jede Information, also zum Beispiel eine Adresse, nur genau einmal gespeichert wird.
- Beispiel: in der Teilnehmerliste einer Vorlesung werden die Adressen nicht erneut erfasst, sondern nur indirekt über die Matrikelnummer. Um dennoch eine Teilnehmerliste mit Adressen zu erstellen, erfolgt eine
SELECT
-Abfrage, in der die Teilnehmertabelle mit der Studententabelle verknüpft wird (siehe oben:JOIN
).
In manchen Fällen ist die Performance einer Datenbank besser, wenn sie nicht (vollständig) normalisiert wird. In diesem Falle werden in der Praxis oft Redundanzen bewusst in Kauf genommen, um zeitaufwändige und komplexe Joins zu verkürzen und so die Geschwindigkeit der Abfragen zu erhöhen. Man spricht auch von einer Denormalisierung einer Datenbank. Wann (und ob überhaupt) eine Denormalisierung sinnvoll ist, ist umstritten und hängt von den Umständen ab.
Schlüssel
Während die Informationen auf viele Tabellen verteilt werden müssen, um Redundanzen zu vermeiden, sind Schlüssel das Mittel, um diese verstreuten Informationen miteinander zu verknüpfen.
So hat in der Regel jeder Datensatz eine eindeutige Nummer oder ein anderes eindeutiges Feld, um ihn zu identifizieren. Diese Identifikationen werden als Schlüssel bezeichnet.
Wenn dieser Datensatz in anderen Zusammenhängen benötigt wird, wird lediglich sein Schlüssel angegeben. So werden bei der Erfassung von Vorlesungsteilnehmern nicht deren Namen und Adressen, sondern nur deren jeweilige Matrikelnummer erfasst, aus der sich alle weiteren Personalien ergeben.
So kann es sein, dass manche Datensätze nur aus Schlüsseln (meist Zahlen) bestehen, die erst in Verbindung mit Verknüpfungen verständlich werden. Der eigene Schlüssel des Datensatzes wird dabei als Primärschlüssel bezeichnet. Andere Schlüssel im Datensatz, die auf die Primärschlüssel anderer Tabellen verweisen, werden als Fremdschlüssel bezeichnet.
Schlüssel können auch aus einer Kombination mehrerer Angaben bestehen. Zum Beispiel können die Teilnehmer einer Vorlesung durch die eindeutige Kombination von Vorlesungsnummer und Studentennummer identifiziert werden, so dass die doppelte Anmeldung eines Studenten zu einer Vorlesung ausgeschlossen ist.
Referenzielle Integrität
Referenzielle Integrität bedeutet, dass Datensätze, die von anderen Datensätzen verwendet werden, in der Datenbank auch vollständig vorhanden sind.
- Im obigen Beispiel bedeutet dies, dass in der Teilnehmertabelle nur Matrikel-Nummern gespeichert sind, die es in der Studenten-Tabelle auch tatsächlich gibt.
Diese wichtige Funktionalität kann (und sollte) bereits von der Datenbank überwacht werden, so dass zum Beispiel
- nur vorhandene Matrikelnummern in die Teilnehmertabelle eingetragen werden können,
- der Versuch, den Datensatz eines Studenten, der schon eine Vorlesung belegt hat, zu löschen, entweder verhindert wird (Fehlermeldung) oder der Datensatz auch gleich aus der Teilnehmertabelle entfernt wird (Löschweitergabe) und
- der Versuch, die Matrikelnummer eines Studenten, der schon eine Vorlesung belegt hat, zu ändern, entweder verhindert wird (Fehlermeldung) oder der Eintrag in der Teilnehmertabelle gleich mitgeändert wird (Aktualisierungsweitergabe).
Widersprüchlichkeit von Daten wird allgemein als Dateninkonsistenz bezeichnet. Diese besteht, wenn Daten bspw. die Integritätsbedingungen (z. B. Constraints oder Fremdschlüsselbeziehungen) nicht erfüllen.
Ursachen für Dateninkonsistenzen können Fehler bei der Analyse des Datenmodells, fehlende Normalisierung des ERM oder Fehler in der Programmierung sein.
Zum letzteren gehören die Lost-Update-Phänomene sowie die Verarbeitung von zwischenzeitlich veralteten Zwischenergebnissen. Dies tritt vor allem bei der Online-Verarbeitung auf, da dem Nutzer angezeigte Werte nicht in einer Transaktion gekapselt werden können.
Beispiel: Transaktion A liest Wert x Transaktion B verringert Wert x um 10 Transaktion A erhöht den gespeicherten Wert von x um eins und schreibt zurück Ergebnis x' = x+1 Die Änderung von B ist verloren gegangen
SQL-Datentypen
In den oben vorgestellten Befehlen
create table
und alter
table
wird bei der Definition jeder Spalte angegeben,
welchen Datentyp die Werte dieser Spalte annehmen können. Dazu
liefert SQL eine ganze Reihe standardisierter Datentypen mit. Die
einzelnen DBMS-Hersteller haben diese Liste jedoch um eine Unzahl
weiterer Datentypen erweitert. Die wichtigsten Standarddatentypen sind:
integer
- Ganze Zahl (positiv oder negativ), wobei je nach Zahl der verwendeten Bit Bezeichnungen wie smallint, tinyint oder bigint verwendet werden. Die jeweiligen Grenzen und die verwendete Terminologie sind vom Datenbanksystem definiert.
numeric (n, m)
oderdecimal (n, m)
- Festkommazahl (positiv oder negativ) mit insgesamt maximal
n
Stellen, davonm
Nachkommastellen. Wegen der hier erfolgenden Speicherung als Dezimalzahl ist eine besonders für Geldbeträge notwendige Genauigkeit gegeben. float (m)
- Gleitkommazahl (positiv oder negativ) mit maximal
m
Nachkommastellen. real
- Gleitkommazahl (positiv oder negativ). Die Genauigkeit für diesen Datentyp ist jeweils vom Datenbanksystem definiert.
double
oderdouble precision
- Gleitkommazahl (positiv oder negativ). Die Genauigkeit für diesen Datentyp ist jeweils vom Datenbanksystem definiert.
float
unddouble
- sind für technisch-wissenschaftliche Werte geeignet und umfassen auch die Exponentialdarstellung. Wegen der Speicherung im Binärformat sind sie aber für Geldbeträge nicht geeignet, weil sich beispielsweise der Wert 0,10 € (entspricht 10 Cent) nicht exakt abbilden lässt.
character (n)
oderchar (n)
- Zeichenkette Text mit
n
Zeichen. varchar (n)
odercharacter varying (n)
- Zeichenkette (also Text) von variabler Länge, aber maximal
n
druckbaren und/oder nicht druckbaren Zeichen. Die Variantevarchar2
ist für Oracle spezifisch, ohne dass sie sich tatsächlich unterscheidet. text
- Zeichenkette (zumindest theoretisch) beliebiger Länge. In manchen Systemen synonym zu
clob
. date
- Datum (ohne Zeitangabe)
time
- Zeitangabe (evtl. inklusive Zeitzone)
timestamp
- Zeitstempel (umfasst Datum und Uhrzeit; evtl. inklusive Zeitzone), meistens mit Millisekundenauflösung, teilweise auch mikrosekundengenau
boolean
- Boolesche Variable (kann die Werte
true
(wahr) oderfalse
(falsch) oderNULL
(unbekannt) annehmen). Dieser Datentyp ist laut SQL:2003 optional und nicht alle DBMS stellen diesen Datentyp bereit. blob (n)
oderbinary large object (n)
- Binärdaten von maximal
n
Bytes Länge. clob (n)
odercharacter large object (n)
- Zeichenketten mit maximal
n
Zeichen Länge.
Wenn es die Tabellendefinition erlaubt, können Attribute auch den Wert
NULL
annehmen, wenn kein Wert bekannt ist oder aus anderen Gründen kein Wert gespeichert werden soll. Der
NULL
-Wert ist von allen anderen möglichen Werten des Datentyps verschieden.
Transaktion, Commit und Rollback
Eine Transaktion bezeichnet eine Menge von Datenbankänderungen, die zusammen ausgeführt werden (müssen). So ist beispielsweise die Buchung (als Transaktion) eines Geldbetrags durch zwei atomare Datenbankoperationen „Abbuchen des Geldbetrages von Konto A“ und „Buchung des Geldbetrages auf Konto B“ gekennzeichnet. Kann die vollständige Abarbeitung der elementaren Datenbankoperationen der Transaktion nicht durchgeführt werden (z. B. aufgrund eines Fehlers), müssen alle durchgeführten Änderungen an dem Datenbestand auf den Ausgangszustand zurückgesetzt werden.
Der Vorgang, der alle Änderungen einer Transaktion zurücksetzt, wird als Rollback bezeichnet. Der Begriff Commit bezeichnet das Ausführen einer Transaktion. Transaktionen sind eine Möglichkeit, die Konsistenz des Datenbestandes zu sichern. Im Beispiel der doppelten Kontenführung wird durch das Verhindern von ungültigen Teilbuchungen eine ausgeglichene Kontobilanz gewährleistet.
Datenbanken erlauben es zum Teil, bestimmte Befehle außerhalb einer Transaktion auszuführen. Darunter fällt insbesondere das Laden von Daten in Tabellen oder das Exportieren von Daten mittels Utilities. Manche DBMS erlauben das temporäre Abschalten der Transaktionslogik sowie einiger Kontrollen zur Erhöhung der Verarbeitungsgeschwindigkeit. Dies muss allerdings meist durch einen expliziten Befehl erzwungen werden, um ein versehentliches Ändern von Daten außerhalb einer Transaktion zu vermeiden. Solche Änderungen können, falls eine Datenbankwiederherstellung erforderlich ist, zu schweren Problemen oder gar Datenverlusten führen.
Eine Transaktion wird mit der SQL-Anweisung Commit
beendet. Alle Änderungen der Transaktion werden persistent gemacht, und das DBMS stellt durch geeignete (interne) Mittel (z. B. Logging) sicher, dass diese Änderungen nicht verloren gehen.
Mit dem Befehl Rollback
wird eine Transaktion ebenfalls beendet, es werden jedoch alle Änderungen seit Beginn der Transaktion rückgängig gemacht. Das heißt, der Zustand des Systems (in Bezug auf die Änderungen der Transaktion) ist der gleiche wie vor der Transaktion.