5. SQL Einleitung / Historie / Normierung Datendefinition mit SQL

5. SQL
Einleitung / Historie / Normierung
Datendefinition mit SQL (DDL)
Datenänderung/-zugriff mit SQL (DML)
Datenkontrolle mit SQL (DCL)
Ausblick: Transaktionsverwaltung/Fehlerbehandlung
Praktischer Teil
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 143
5.1 Einleitung / Historie / Normierung
SQL = Structured Query Language
- Die Norm-Datenbanksprache (ISO, DIN) für relationale DBMS
- Hoher Anwendungs- und Verbreitungsgrad
- Abdeckung aller 3 Ebenen der ANSI/SPARC...
Architektur (siehe Folie 33):
• Externes Schema (Sichten, Views)
• Konzeptuelles Schema
• Internes Schema (teilweise)
SQL als Sprache für alle Bereiche eines DBMS ("aus einem Guß"), ist in
vorrelationalen DMBS getrennt
- DDL: Data Definition Language (CREATE TABLE, CREATE INDEX, ...)
- DML: Data Manipulation Language (SELECT, UPDATE, INSERT, DELETE)
- DCL: Data Control Language (GRANT)
- TCL: Transaction Control Language (COMMIT, ROLLBACK)
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 144
5.1 Einleitung / Historie / Normierung
Historie und Entstehungsgeschichte
- Ursprung im Rahmen des System R Projektes
• IBM Research Lab, San Jose, Anfang 1970er
• Sprache SEQUEL (Structured English QUEry Language) publiziert
durch Don Chamberlin und Raymond F. Boyce
- Ziel war die Entwicklung einer
• Benutzerfreundlichen Datenbanksprache mit
• Großer Mächtigkeit basierend auf den
• Erfahrungen mit Relationenalgebra und Relationenkalkül
- Weiterentwicklung 1975/76 bei IBM zu SEQUEL2
- Umbenennung zu SQL, erste Produkte Ende der 70er:
• Oracle
• System R SQL/DS (SQL Data System) DB2
- In Folge weitere Produkte mit SQL-Schnittstelle oder SQL-Aufsätzen für
vorrelationale DBMS (UDS, ADABAS, Informix, MS Access, ...)
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 145
5.1 Einleitung / Historie / Normierung
SQL-Normierungsaktivitäten, seit 1982 etabliert
- ANSI (American National Standards Institute)
- ISO (International Organization for Standardisation)
- DIN (Deutsches Institut für Normung)
- Syntax in BNF-Grammatik: http://savage.net.au/SQL/
Entwicklungsschritte
- SQL0: 1. Norm 1987 (ISO und DIN 9075)
- SQL1: 2. Norm 1989 ("SQL-89")
• IEF (Integrity Enhancement Feature)
- SQL2: 3. Norm 1992 ("SQL-92")
- SQL3: SQL:1999
- SQL4: SQL:2003
• Behandlung von XML in relationen DBMS
- (SQL5: SQL:2008)
- ...
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 146
5.1 Einleitung / Historie / Normierung
Genormte SQL-Sprachumfänge werden immer umfangreicher, SQLn+1
schließt jeweils SQLn ein (Abwärtskompatibilität)
Produkte vs. SQL-Norm
- Weitgehend SQL-89 konform, teils SQL-92 konform, nicht SQL:1999
- Produkte bieten zahlreiche "Spezialitäten" und Eigenheiten, die nicht
normkonform sind bzw. vielleicht später von der Norm erfasst werden
typischer heutiger SQL-Dialekt in Produkten
SQL3 / SQL-99
SQL2 / SQL-92
SQL1 / SQL-89
SQL0 / SQL-87
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 147
5.2 Datendefinition mit SQL (DDL)
Grober Überblick zu Datendefinitionsanweisungen in SQL
Konzeptuelle Ebene
- CREATE TABLE, ALTER TABLE, DROP TABLE
- CREATE DOMAIN, ALTER DOMAIN, DROP DOMAIN
- ...
Interne Ebene
- CREATE INDEX, ALTER INDEX, DROP INDEX
- Nicht Bestandteil der aktuellen SQL-Norm (SQL:2003)
Externe Ebene
- CREATE VIEW, DROP VIEW
- Nicht notwendig: ALTER VIEW ( = DROP + CREATE)
- Erläuterungen im Abschnitt 5.3 Datenänderung/-zugriff mit SQL (DML)
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 148
5.2.1 CREATE TABLE
Syntax (in einfachster Form):
CREATE TABLE Basisrelationenname (
Spaltenname_1 Wertebereich_1, [NOT NULL]
...
Spaltenname_k Wertebereich_k [NOT NULL])
Beispiel (siehe Folie 128):
- Definition für Primärschlüssel und Fremdschlüssel fehlen
- Testausführung in MS Access
- Anweisungen:
CREATE TABLE Buch (
InvNr INTEGER NOT NULL,
Titel VARCHAR(30),
ISBN CHAR(5),
Autor VARCHAR(40))
Datenbanksysteme
CREATE TABLE Ausleihe (
InvNr INTEGER NOT NULL,
Name VARCHAR(20))
Friedrich-Schiller-Universität Jena
Seite 149
5.2.1 CREATE TABLE
Bedeutung der Anweisung:
- Erzeugen einer k-spaltigen Tabelle (Relation mit k Attributen) mit über
Spaltenname_i/Wertebereich_i definiertem Relationenschema
- Tabelle ist nach dem CREATE zunächst leer, d.h. enthält keine Tupel
- NOT NULL Klausel verbietet das Auftreten von Nullwerten (NULL) in den
spezifizierten Spalten, ohne Angabe sind Nullwerte erlaubt
- Schemainformation wird im Datenbankkatalog abgelegt (teils auch als
Data Dictionary bezeichnet)
• Datenbankkatalog besteht ebenfalls aus Tabellen; Verzeichnis aller
erzeugten Tabellen, Attribute, benutzerdefinierten Wertebereiche etc.
• Katalogtabellen können mit SQL vom Benutzer gelesen werden wie
"normale" benutzereigene Tabellen
• Datenbankkataloge sind prinzipiell normiert
Wesentlicher Vorteil von relationalen Datenbanksystemen im
Vergleich zu vorrelationalen DBMS mit "kryptischer" interner Ablage
der Kataloginformationen!
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 150
5.2.1 CREATE TABLE
Wertebereiche / Datentypen
- Numerische Typen:
• INTEGER, SMALLINT, BIGINT
• FLOAT(precision), REAL, DECIMAL(precision [, scale])
- Zeichenkettentypen:
• CHARACTER(n), CHAR(n), VARCHAR(n)
- Logische Datentypen:
• BIT(n), BOOLEAN
- Datentypen für Zeit/Datum:
• DATE, TIME[(precision)], TIMESTAMP[(precision)]
- Datentypen für große Objekte
• CHARACTER LARGE OBJECT[(length)], CLOB[(length)]
Datentypen für konkrete relationale Datenbanksystemen (Produkte)
- Grundlage ist immer die Produktliteratur
- Oft existieren zusätzliche Datentypen (nicht in der SQL-Norm)
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 151
5.2.1 CREATE TABLE
Bemerkungen zu Schlüsseln
- SQL-Norm erzwingt keine Primärschlüssel-Definition!
- Tabellen dürfen also Duplikate aufweisen
• Mehrfache Tupel, die in allen ihren Attributwerten übereinstimmen
• Multimengeneigenschaft ("bags" statt "sets")
- Duplikatfreiheit muss bei Bedarf explizit festgelegt werden
• PRIMARY KEY Klausel als eine Möglichkeit (implizit NOT NULL)
• Überwachung durch das DBMS
Abweichen von der "reinen (relationalen) Lehre"!
Beispiel (Erweiterung von Folie 149):
CREATE TABLE Buch (
InvNr INTEGER NOT NULL,
Titel VARCHAR(30),
ISBN CHAR(5),
Autor VARCHAR(40),
PRIMARY KEY (InvNr))
Datenbanksysteme
CREATE TABLE Ausleihe (
InvNr INTEGER NOT NULL,
Name VARCHAR(20),
PRIMARY KEY (InvNr),
FOREIGN KEY (InvNr)
REFERENCES Buch(InvNr))
Friedrich-Schiller-Universität Jena
Seite 152
5.2.1 CREATE TABLE
Zusätzliche Anweisungen für CREATE TABLE mit Fokus auf der
Integritätssicherung, war großes Thema der letzten Jahre (ab SQL-92)
- Primär-/Fremdschlüsseldefinition ist im relationalen Fall modellinhärent,
"ohne" sollte eigentlich nicht zulässig sein
- Falls der Primärschlüssel nur aus einem Attribut besteht, kann die
PRIMARY KEY Klausel "in-line" angegeben werden
DEFAULT-Klausel zur expliziten, benutzerseitigen Festlegung eines DefaultWerts für eine Tabellenspalte
- Bsp: CREATE TABLE Buch (
InvNr INTEGER PRIMARY KEY,
Titel VARCHAR(30),
ISBN CHAR(5),
Autor VARCHAR(40) DEFAULT 'Küspert')
- Beim Einfügen eines Datensatzes ohne Autor-Angabe wird vom DBVS der
Werte 'Küspert' eingetragen
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 153
5.2.1 CREATE TABLE
CHECK-Klausel zur Spezifikation von Integritätsbedingungen (nicht nur) für
einzelne Attributwerte
- Ermöglicht die Angabe von Prädikaten, welche den zulässigen
Wertebereich für Tabellenspalten weiter einschränken
- Prüfung erfolgt durch das DBVS
• Bei Tupeleinfügung (INSERT) und Wertänderung (UPDATE)
• Im Fall einer drohenden Konsistenzverletzung wird die verursachende
Anweisung nicht ausgeführt, also zurückgewiesen
- CHECK-Klausel ist auch an einer Tabelle gesamt definierbar
- Bsp:
CREATE TABLE Buch (
InvNr INTEGER PRIMARY KEY CHECK (InvNr BETWEEN 5 AND 9999),
Titel VARCHAR(30),
ISBN CHAR(5),
Autor VARCHAR(40) DEFAULT 'Küspert' CHECK (Autor <> 'Kujau'))
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 154
5.2.2 ALTER TABLE
Syntax:
ALTER TABLE Basisrelationenname <alter_action>
Bedeutung der Anweisung:
- Änderung eines mit CREATE TABLE angelegten Relationsschemata
(Schemaevolution)
- Möglichkeiten der Änderung über <alter_action>
• Hinzufügen neuer Attribute (Spalten) zu einer Tabelle, seit SQL-89
normiert (seit SQL-92 mit DEFAULT- und CHECK-Klausel)
• Löschen von Tabellenspalten, erst seit SQL-92 normiert
• Ändern von Eigenschaften vorhandener Attribute (sehr eingeschänkt!)
• Hinzufügen von Integritätsbedingungen an einer Tabelle
• Löschen von Integritätsbedingungen einer Tabelle
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 155
5.2.2 ALTER TABLE
Hinzufügen von Tabellenspalten
- Neue Spalte wird bei allen in der Tabelle aktuell vorhandenen Tupeln mit
NULL belegt (zumindest "logisch")
- Neue Spalte (Name, Wertebereich, Tabellenzuordnung) wird im Katalog
eingetragen
- Bsp:
• Tabelle "Buch" erhält neue Spalte "Einkaufspreis"
• Testausführung in MS Access
• Syntax: ALTER TABLE Buch
ADD Seitenzahl INTEGER CHECK (Seitenzahl > 0)
Löschen von Tabellenspalten
- Umkehrung der Wirkung gegenüber Hinzufügen
- Zusätzlich Angabe CASCADE bzw. RESTRICT möglich, Erklärung später
- Bsp:
• Löschen der Spalte "Einkaufspreis" in Tabelle "Buch"
• Syntax: ALTER TABLE Buch DROP Seitenzahl
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 156
5.2.2 ALTER TABLE
Ändern von Tabellenspalten
- Ändern/Löschen einer Default-Angabe
- Aktualisierung des Katalogs
- Bsp 1:
• Default-Wert an der Spalte "ISBN" in Tabelle "Buch" setzen
• Syntax: ALTER TABLE Buch ALTER ISBN SET DEFAULT '0-000'
- Bsp 2:
• Default-Wert für Spalte "ISBN" in Tabelle "Buch" löschen
• Syntax: ALTER TABLE Buch ALTER ISBN DROP DEFAULT
Hinzufügen einer Tabellen-Integritätsbedingung
- Aktuelle Daten der Tabelle müssen die Bedingung erfüllen!
- Aktualisierung des Katalogs
- Bsp:
• Spalte "Autor" und "Titel" in Tabelle "Buch" sollen eindeutig sein
• Syntax: ALTER TABLE Buch ADD UNIQUE(Autor, Titel)
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 157
5.2.2 ALTER TABLE
Bemerkungen
- Wunsch nach viel mehr Möglichkeiten vorallem beim Verändern einer
bestehenden Tabellenspalte
• Bsp: Ändern des Datentyps
• Keine Unterstützung durch SQL-Norm (warum wohl?!)
- Relationale DBMS-Produkte unterstützen diesbezüglich meist nur kleinen
Teil der SQL-92-Norm: ALTER TABLE ... ADD ...
Schemaänderung generell heikles Thema bei DBMS
- Was geschieht mit den vorhandenen Daten (Tupeln in Tabellen)?
• Sofortige Transformation in neues Format
• Verzögerte Transformation in neues Format
• Was, wenn Transformation scheitert / nicht automatisch durchführbar?
- Auswirkungen auf vorhandene Anwendungen
• Programme, gespeicherte SQL-Anfragen
• Teilweise Abhilfe durch Sichten (Views)
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 158
5.2.3 DROP TABLE
Syntax:
DROP TABLE Basisrelationenname <drop_behavior>
Bedeutung der Anweisung:
- Tabelle "Basisrelationenname" wird gelöscht (Schema und Daten!)
• Einträge zur Schemabeschreibung (Relationsname, Attributnamen,
Wertebereichsangaben, ...) werden aus dem Katalog entfernt
• Üblicherweise nicht zwischen normalen DB-Veränderungen in einer
Transaktion erlaubt
- Definition der Reaktion des DBVS über <drop_behavior>, wenn die
Tabelle noch über Integritätsbedingungen/Sichten referenziert wird:
• CASCADE: bezugnehmende Objekte werden mit gelöscht
• RESTRICT: Löschung der Tabelle wird zurückgewiesen
Beispiel:
DROP TABLE Buch RESTRICT
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 159
5.2.4 CREATE DOMAIN
Syntax:
CREATE DOMAIN Domainname Wertebereich
Bedeutung der Anweisung:
- Möglichkeit zur Vereinbarung benutzerdefinierter Wertebereiche
• Eigene Datentypen (Domänen) im Gegensatz zur Einschränkung des
Wertebereichs von Standarddatentypen mit CHECK-Klausel
• Analog zu Programmiersprachen
- Definition darf mit DEFAULT- und CHECK-Klausel verwendet werden
Beispiel:
- Neues Attribut "Gebiet" in Tabelle
"Buch" mit nutzerdefinierten Typ
CREATE DOMAIN Fachgebiet VARCHAR(20)
DEFAULT 'Informatik'
CHECK (VALUE IN ('Informatik', 'Physik',
'BWL', 'Mathematik'))
Datenbanksysteme
CREATE TABLE Buch (
InvNr INTEGER PRIMARY KEY,
Titel VARCHAR(30),
Gebiet Fachgebiet,
ISBN CHAR(5),
Autor VARCHAR(40))
Friedrich-Schiller-Universität Jena
Seite 160
5.2.4 CREATE DOMAIN
Erläuterungen zum Beispiel:
- Neuer benutzerdefinierter Typ "Fachgebiet", welcher Zeichenketten der
Länge 20 aufnehmen kann
• Standardwert ist 'Informatik'
• Erlaubte Werte sind 'Informatik', 'Physik', 'Mathematik' und 'BWL'
Was wäre, wenn in der Liste der erlaubten Werte 'Informatik' fehlt?
- Einfügen eines Buch-Tupels ohne Gebiet-Angabe scheitert, weil
• DBVS den Default-Wert 'Informatik' generiert,
• CHECK-Klausel aber nicht erfüllt ist
Hinweise:
- Anweisung CREATE DOMAIN sowie DEFAULT- und CHECK-Klausel sind
erst ab SQL-92 normiert
- Realisierung in der gezeigten Form nicht in allen Produkten vorhanden
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 161
5.2.5 ALTER DOMAIN
Syntax:
ALTER DOMAIN Domainname <alter_action>
Bedeutung der Anweisung
- Verändert einen existierenden nutzerdefinierten Datentyp
- Möglichkeiten der Änderung über <alter_action>
• SET DEFAULT: Setzen eines (neuen) Defaultwertes
• DROP DEFAULT: Defaultwert löschen
• ADD CHECK: Check-Klausel hinzufügen/überschreiben
• DROP CHECK: Check-Klausel löschen
Beispiel:
- Setzen des neuen Default-Wertes 'BWL' am Datentyp "Fachgebiet"
ALTER DOMAIN Fachgebiet SET DEFAULT 'BWL'
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 162
5.2.6 DROP DOMAIN
Syntax:
DROP DOMAIN Domainname <drop_behavior>
Bedeutung der Anweisung
- Löscht einen existierenden nutzerdefinierten Datentyp
- Definition der Reaktion des DBVS über <drop_behavior>, wenn der
Datentyp noch verwendet wird:
• CASCADE: Gleichzeitige Löschung der abhängigen Objekte, z.B.
Tabellenspalten
• RESTRICT: Zurückweisen der Löschung
Beispiel:
- Löschen des Datentyps "Fachgebiet"
- Abhängige Objekte (z.B. Tabellenspalte "Buch.Gebiet") sollen auch
gelöscht werden
DROP DOMAIN Fachgebiet CASCADE
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 163
5.2.7 CREATE / DROP INDEX
Syntax:
CREATE [UNIQUE] INDEX Indexname
ON Basisrelationenname (
Spaltenname_1 Ordnung_1,
...
Spaltenname_k Ordnung_k)
Bedeutung der Anweisung
- Erzeugung eines Zugriffspfads (Index), der zum schnellen Zugriff auf
bestimmte Tupel bei gegebenem Attributwert dient
- UNIQUE-Angabe spezifiziert, dass die Werte im Index eindeutig sein
müssen (keine Duplikate)
- Ordnung_i: mögliche Werte ASC (ascending) und DESC (descending)
PRIMARY KEY-Definition bei CREATE TABLE erzeugt implizit einen Unique
Index auf dem Primärschlüssel
- Keine Forderung der (SQL-)Norm, aber Realität in Produkten
- Grund: Effizienz beim Prüfen der Werteeindeutigkeit!
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 164
5.2.7 CREATE / DROP INDEX
Bemerkung zur Indexrealisierung
- Art der Realisierung auf konzeptueller und externe Ebene nicht sichtbar
- Sogar die (Nicht-)Existenz eines Index bleibt verborgen
- Grund Datenunabhängigkeit: Indexe können erzeugt/gelöscht werden,
ohne dass der Anwender etwas davon merkt (außer Performance)!
Beispiel (siehe Folie 128):
- Index-Ausprägung zum Primärschlüssel "InvNr" in Tabelle "Buch"
z.B.
B*-Baum
0007
...Dr. No...
1201
4711
4712
4717
...Datenbanken...
...Datenbanken...
...Objektbanken...
...Pascal...
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 165
5.2.7 CREATE / DROP INDEX
Beispiele:
- CREATE UNIQUE INDEX Titel_Index ON Buch (Titel ASC)
• Ergebnis: kann nicht erfolgreich durchgeführt werden, da keine
Werteeindeutigkeit in Spalte "Titel"
• Lösung: Weglassen der UNIQUE-Angabe
- CREATE INDEX Autoren_Index ON Buch (Autor ASC)
• Ermöglicht schnellen Zugriff auf die zu einem Autor gehörigen Bücher
- CREATE INDEX Autoren_Titel_Index ON Buch (Autor ASC, Titel ASC)
• Index enthält Werte-Paare (Autor, Titel)
• Gut geeignet, wenn Anfragen häufig mit Autoren- und Titelangabe
erfolgen ("Ist das Pascal-Buch von Wirth vorhanden?")
- CREATE INDEX InvNr_Index ON Ausleihe (InvNr ASC)
• Unnötig, da "InvNr" Primärschlüssel in Tabelle "Ausleihe" ist und Index
somit vorhanden (sogar UNIQUE)
- DROP INDEX Autoren_Index
• Index mit global eindeutigem Namen "Autoren_Index" wird gelöscht
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 166
5.2.7 CREATE / DROP INDEX
Gedankenspiel: angenommen, "InvNr" wäre nicht als Primärschlüssel in
"Ausleihe" vereinbart, warum erscheint Index auf "InvNr" trotzdem sinnvoll?
- "InvNr" ist Fremdschlüssel in "Ausleihe" mit Bezug auf den
Primärschlüssel (InvNr) von "Buch"
- DBVS muss deshalb Integritätsüberwachung vornehmen, z.B. dass ein
Buch nicht gelöscht werden darf, wenn noch ausgeliehen
- Erfordert aus Effizienzgründen einen Index (welcher von realen Produkten
nicht erzwungen wird!)
Abschließende Bemerkungen zur Index-Thematik
- Seit SQL-92 gibt es eine UNIQUE-Spezifikation, die genau so eingesetzt
werden darf, wie die PRIMARY KEY-Spezifikation
- Damit adäquates Mittel zur Definition von Schlüsselkandidaten
• Unterstützung nicht in allen Produkten
• Unschöne Alternative: Vereinbarung von Schlüsselkandidaten mit
CREATE UNIQUE INDEX
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 167