DBIS-2016-SQL-DDL - Fakultät für Mathematik und Informatik

SQL
Wintersemester 16/17
DBIS
1
Typischer Datenbankentwurf
Anforderungsanalyse
und -spezifikation
Miniwelt
Konzeptioneller
Entwurf
E/R-Diagramm
Logischer
Entwurf
Relationales DBSchema
Datendefinition
Durch Wahl eines
Produkts
SQL-Anweisungen gemäß SQLDialekt des gewählten DBMS
(Übung: DB2)
Wintersemester 16/17
DBIS
Alternative: UML-Diagramm
Alternativen:
Bachman-Diagramm (NetzwerkDB-Modell)
Hierarchisches DB-Schema
(Hierarchisches DB-Modell)
Alternativen:
Wahl eines anderen RDBMS
2
Einführung in SQL
Wintersemester 15/16
DBIS
3
Einleitung
• 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 (externes,
konzeptuelles und internes Schema)
• SQL als Sprache für alle Bereiche eines DBMS
• 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)
Wintersemester 16/17
DBIS
4
Historie I
• 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, DB2)
• In Folge weitere Produkte mit SQL-Schnittstelle oder SQL-Aufsätzen für
vorrelationale DBMS (UDS, ADABAS, Informix, MS Access, ...)
Wintersemester 16/17
DBIS
5
Historie II
• SQL-Normierungsaktivitäten, seit 1982 etabliert (ANSI, ISO, DIN)
• Entwicklungsschritte
• SQL0: 1. Norm 1987 (ISO und DIN 9075)
• SQL1: 2. Norm 1989 ("SQL-89")
• SQL2: 3. Norm 1992 ("SQL-92")
• SQL:1999
• …
• Aktuelle Revision: SQL:2011
• Der aktuelle ISO-Standard besteht aus 14 Publikationen
Wintersemester 16/17
DBIS
6
Normierung vs. Praxis
• Genormte SQL-Sprachumfänge werden immer umfangreicher, SQLn+1
schließt jeweils SQLn ein (Abwärtskompatibilität)
• 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
SQL-2011
SQL-2008
SQL/XML / SQL-2006
SQL:2003
SQL3 / SQL-99
SQL2 / SQL-92
SQL1 / SQL-89
SQL0 / SQL-87
Wintersemester 16/17
DBIS
8
Datendefinition mit SQL
DDL
Wintersemester 16/17
DBIS
8
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:2011)
• Externe Ebene
• CREATE VIEW, DROP VIEW
• Nicht notwendig (aber in manchen Produkten vorhanden):
ALTER VIEW (= DROP + CREATE)
Wintersemester 16/17
DBIS
9
Der „CREATE TABLE“-Befehl
• Syntax (in einfachster Form):
CREATE TABLE Basisrelationenname (
Spaltenname_1 Wertebereich_1 [NOT NULL],
...
Spaltenname_k Wertebereich_k [NOT NULL])
• Beispiel (ohne Schlüsseldefinitionen)
CREATE TABLE Buch(
CREATE TABLE Ausleihe(
InvNr INTEGER NOT NULL,
InvNr INTEGER NOT NULL,
Titel VARCHAR(30),
Name VARCHAR(20))
ISBN CHAR(5),
Autor VARCHAR(40))
Wintersemester 16/17
DBIS
10
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 (Data Dictionary) abgelegt
• Datenbankkatalog prinzipiell normiert und 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
• Wesentlicher Vorteil von relationalen Datenbanksystemen
Wintersemester 16/17
DBIS
11
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 in konkreten relationalen Datenbanksystemen (Produkten)
• Grundlage ist immer die Produktliteratur
• Oft existieren zusätzliche Datentypen (nicht in der SQL-Norm)
Wintersemester 16/17
DBIS
12
Bemerkung zu Schlüsseln
• SQL-Norm erzwingt keine Primärschlüssel-Definition (Abweichung von
der reinen relationalen Lehre)!
• Tabellen dürfen also Duplikate aufweisen (Mehrfache Tupel, die in allen
ihren Attributwerten übereinstimmen)
• Duplikatfreiheit muss explizit festgelegt werden
• PRIMARY KEY Klausel als eine Möglichkeit (implizit NOT NULL)
• Überwachung durch das DBMS
CREATE TABLE Buch (
InvNr INTEGER NOT NULL,
Titel VARCHAR(30),
ISBN CHAR(5),
Autor VARCHAR(40),
PRIMARY KEY (InvNr))
Wintersemester 16/17
CREATE TABLE Ausleihe (
InvNr INTEGER NOT NULL,
Name VARCHAR(20),
PRIMARY KEY (InvNr),
FOREIGN KEY (InvNr)
REFERENCES Buch(InvNr))
DBIS
13
Zusätzliche Anweisungen I
• Falls der Primärschlüssel nur aus einem Attribut besteht, kann die
PRIMARY KEY Klausel „inline“ angegeben werden
• DEFAULT-Klausel zur expliziten, benutzerseitigen Festlegung
eines Default-Werts für eine Tabellenspalte
• Beispiel:
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 DBMS der
Wert 'Küspert' eingetragen
Wintersemester 16/17
DBIS
14
Zusätzliche Anweisungen II
• CHECK-Klausel zur Spezifikation von Integritätsbedingungen
• Ermöglicht die Angabe von Prädikaten, welche den zulässigen Wertebereich für
Tabellenspalten weiter einschränken
• Prüfung erfolgt durch das DBMS 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
• Beispiel:
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'))
Wintersemester 16/17
DBIS
15
Der „ALTER TABLE“-Befehl
• Syntax:
ALTER TABLE Basisrelationenname <alter_action>
• Bedeutung der Anweisung:
• Änderung eines mit CREATE TABLE angelegten Relationsschemas
(Schemaevolution)
• Möglichkeiten der Änderung über <alter_action>
• Hinzufügen neuer Attribute (Spalten) zu einer Tabelle
• Löschen von Tabellenspalten
• Ändern von Eigenschaften vorhandener Attribute (sehr eingeschänkt!)
• Hinzufügen von Integritätsbedingungen an einer Tabelle
• Löschen von Integritätsbedingungen einer Tabelle
Wintersemester 16/17
DBIS
16
ALTER TABLE – Hinzufügen und Löschen von Spalten
• 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
• Beispiel: 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
• Beispiel: ALTER TABLE Buch DROP Seitenzahl
Wintersemester 16/17
DBIS
17
ALTER TABLE – Ändern von Integritätsbedingungen
• Ändern von Tabellenspalten
• Ändern/Löschen einer Default-Angabe
• Aktualisierung des Katalogs
• Beispiel 1: ALTER TABLE Buch ALTER ISBN SET DEFAULT '0-000'
• Beispiel 2: 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
• Beispiel:
Wintersemester 16/17
ALTER TABLE Buch ADD UNIQUE(Autor, Titel)
DBIS
18
ALTER TABLE - Bemerkungen
• Wunsch nach viel mehr Möglichkeiten vor allem beim Verändern einer
bestehenden Tabellenspalte (z. B. Ändern des Datentyps - Keine
Unterstützung durch SQL-Norm)
• 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)
Wintersemester 16/17
DBIS
19
Der „DROP TABLE“-Befehl
• 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 DBMS über <drop_behavior>, wenn die
Tabelle noch über Integritätsbedingungen/Sichten referenziert wird
• Beispiel:
DROP TABLE Buch RESTRICT
Wintersemester 16/17
DBIS
20
Der „CREATE DOMAIN“-Befehl
• 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
•
CREATE TABLE Buch (
Beispiel:
InvNr INTEGER PRIMARY KEY,
Titel VARCHAR(30),
CREATE DOMAIN Fachgebiet VARCHAR(20)
Gebiet Fachgebiet,
DEFAULT 'Informatik'
ISBN CHAR(5),
CHECK (VALUE IN ('Informatik',
'Physik', 'BWL', 'Mathematik'))
Autor VARCHAR(40))
Wintersemester 16/17
DBIS
21
CREATE DOMAIN - Erläuterungen
• 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
• DBMS den Default-Wert 'Informatik' generiert,
• CHECK-Klausel aber nicht erfüllt ist
• Realisierung in der gezeigten Form nicht in allen Produkten vorhanden
Wintersemester 16/17
DBIS
22
Der „ALTER DOMAIN“-Befehl
• 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:
Wintersemester 16/17
ALTER DOMAIN Fachgebiet SET DEFAULT 'BWL'
DBIS
23
Der „DROP DOMAIN“-Befehl
• Syntax: DROP DOMAIN Domainname <drop_behavior>
• Bedeutung der Anweisung
• Löscht einen existierenden nutzerdefinierten Datentyp
• Definition der Reaktion des DBMS über <drop_behavior>, wenn der
Datentyp noch verwendet wird:
• Beispiel:
DROP DOMAIN Fachgebiet CASCADE
Wintersemester 16/17
DBIS
24
Die Befehle „CREATE INDEX“ und „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)
Wintersemester 16/17
DBIS
25
Bemerkungen zu CREATE / DROP INDEX I
• 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!
• 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)!
Wintersemester 16/17
DBIS
26
Beispiele CREATE / DROP INDEX
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 Autorenund 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
DROP INDEX Autoren_Index
Index mit global eindeutigem Namen "Autoren_Index" wird gelöscht
Wintersemester 16/17
DBIS
27
Bemerkungen zu CREATE / DROP INDEX II
• 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“
• DBMS 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!)
• CREATE UNIQUE INDEX ist damit eine unschöne Alternative zu
PRIMARY KEY
Wintersemester 16/17
DBIS
28