5.2.1 create table - Fakultät für Mathematik und Informatik

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
5.3 Datenänderung/-zugriff mit SQL (DML)
ƒ Hinweis:
- DML-Anweisungen sind mengenorientiert
- Mit einer Anweisungen kann mehr als ein Tupel eingefügt, geändert,
gelöscht oder gelesen werden
Æ Benutzungs- und Effizienzvorteile (Client-Server-Szenario!)
ƒ Anweisungen für Datenänderung:
- INSERT
- UPDATE
- DELETE
ƒ Anweisungen für Datenzugriff:
- SELECT
- CREATE / DROP VIEW (Konzeptuelle Ebene)
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 168
5.3.1 INSERT
ƒ Befehl zum Einfügen von Datensätzen in eine Tabelle
- Erweiterung des Entityset eines Entitytyp (siehe Folie 57)
- Ausprägungsformen
• Einfügen von Tupeln als Konstanten
• Einfügen von Tupeln als Abfrageergebnis auf einer (anderen)
Relationen (SFW-Klausel, siehe Abschnitt 5.3.4 SELECT)
ƒ Voraussetzung für aufgeführte Beispiele (Test in MS Access):
- Tabelle Buch, Tabelle Ausleihe
- Anweisungen von Folie 152:
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 169
5.3.1 INSERT – Tupel als Konstanten
ƒ Syntax:
INSERT
INTO Basisrelationenname [(Spaltenname_1, ..., Spaltenname_k)]
VALUES (Wert_11, ..., Wert_k1) [, ..., (Wert_1n, ..., Wert_kn)]
ƒ Beispiel 1: Einfügen eines Tupels in Relation "Buch"
INSERT INTO Buch (InvNr, Titel, ISBN, Autor)
VALUES (1201,'Objektbanken','3-111','Heuer')
ƒ Beispiel 2: Einfügen eines unvollständigen Tupels in Relation "Buch"
INSERT INTO Buch (InvNr, ISBN)
VALUES (4867,'3-221')
Æ Werte für "Autor" und "Titel" werden auf NULL gesetzt!
ƒ Beispiel 3: Einfügen eines Tupels in Relation "Buch" ohne Inventarnummer
INSERT INTO Buch (Titel, ISBN, Autor)
VALUES ('Modula-4','3-222','Wirth')
Æ NULL-Wert für InvNr nicht erlaubt (Primärschlüssel!) Æ Fehlermeldung
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 170
5.3.1 INSERT – Tupel als Konstanten
ƒ Beispiel 4: Weglassen der Attributliste bei der INTO-Klausel
INSERT INTO Buch
VALUES (4710,'Modula-4','3-222','Wirth')
- Werte der VALUES-Klausel werden den Attributen in der Reihenfolge
zugeordnet, wie sie beim CREATE-Statement aufgeführt waren
• 4711 → InvNr, 'Modula-4' → Titel, '3-222' → ISBN, 'Wirth' → Autor
• Funktioniert nur, weil SQL Tabellenspalten nicht als (ungeordnete!)
Menge auffasst Æ Abweichung von der "reinen" Lehre!
ƒ Beispiel 5: Einfügen eines Tupels in Relation "Ausleihe"
INSERT INTO Ausleihe
VALUES (0815,'Liebisch')
Æ Anweisung scheitert, da keine zugehöriger Primärschlüsselwert (InvNr =
0815) in Relation "Buch" existiert (Fremdschlüssel-Beziehung verletzt!)
ƒ Beispiel 6: Einfügen von zwei Tupeln in Relation "Ausleihe"
INSERT INTO Ausleihe (InvNr, Name)
VALUES (1201,'Liebisch'), (4710,'Küspert')
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 171
5.3.1 INSERT – Tupel als SFW-Klausel
ƒ Syntax:
INSERT
INTO Basisrelationenname [(Spaltenname_1, ..., Spaltenname_k)]
<lesende SQL-Anfrage>
ƒ Wirkung:
- Ergebnis der lesenden SQL-Anfrage (SFW: Select-From-Where) wird in
die angegebene Relation eingefügt
• Abgeschlossenheit: SELECT-Anfrage liefert wieder eine Menge
• Mengenorientiertheit: INSERT-Operation kann Mengen verarbeiten
- Voraussetzung sind strukturell gleiche Relationen
• Identische Anzahl von Attributen
• Kompatibilität der Attribut-Wertebereiche
• Reihenfolge der Attribute wichtig oder explizite Benennung
- SQL-Anfrage kann beliebig komplex sein
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 172
5.3.1 INSERT – Tupel als SFW-Klausel
ƒ Beispiel 7: Verdopplung der Tupel in Relation "Ausleihe"
INSERT INTO Ausleihe
SELECT * FROM Ausleihe
Æ Selektion aller Tupel (= *) aus Relation "Ausleihe" als Menge
Æ Einfügen der Tupelmenge in Relation "Ausleihe"
Æ Anweisung scheitert wegen Primärschlüssel-Verletzung
ƒ Beispiel 8: Kopieren von Inhalten verschiedener Tabellen
- Angenommen, es gibt zusätzlich eine Relation Ausleihe_alt mit
denjenigen Büchern, die schon länger als 2 Jahre ausgeliehen sind
- Inhalt dieser Relation soll nun, damit er nicht in Vergessenheit gerät,
wieder in Relation Ausleihe übernommen werden
INSERT INTO Ausleihe (InvNr, Name)
SELECT InvNr_alt, Name_alt FROM Ausleihe_Alt
Æ Anweisung fügt alle Tupel aus Relation "Ausleihe_alt" in Relation
"Ausleihe" ein (Kopieren)
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 173
5.3.2 UPDATE
ƒ Syntax:
UPDATE Basisrelationenname
SET Spaltenname_1 = Ausdruck_1 [, Spaltenname_k = Ausdruck_k]
[WHERE Bedingung]
ƒ Wirkung:
- In allen Tupeln von Basisrelationenname,
- welche die (optionale) Bedingung erfüllen,
- werden die Attributwerte der Spaltenname_i wie angegeben ersetzt
- Ausdruck_i: Konstante, Attribut, SFW-Ausdruck (Subselect)
ƒ Beispiel 1: Der Titel des Buches mit der InvNr = 1201 ('Objektbanken') soll in
'Objektdatenbanken' geändert werden
UPDATE Buch
SET Titel = 'Objektdatenbanken'
WHERE InvNr = 1201
Æ Anweisung ändert immer nur ein einzelnes Tupel, weil InvNr als
Primärschlüssel definiert ist
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 174
5.3.2 UPDATE
ƒ Beispiel 2: Alternative Anweisung für Beispiel 1
UPDATE Buch
SET Titel = 'Objektdatenbanken'
WHERE Titel = 'Objektbanken'
Æ Anweisung ändert datenabhängig ein oder mehrere Tupel, je nach dem
wieviele Tupel den Titel 'Objektbanken' haben
ƒ Beispiel 3: Für alle Bücher mit InvNr > 1000 soll der Autor auf 'Dr. Seltsam'
gesetzt werden
UPDATE Buch
SET Autor = 'Dr. Seltsam'
WHERE InvNr > 1000
Æ Explizites Mengen-Update
ƒ Beispiel 4: Gib allen Angestellten der Relation Angest(PNr, Name, Gehalt, ...)
eine Gehaltserhöhung um 1000,- EUR
UPDATE Angest
SET Gehalt = Gehalt + 1000
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 175
5.3.2 UPDATE
ƒ Beispiel 5: Für alle Bücher soll die InvNr auf 9999 gesetzt werden
UPDATE Buch
SET InvNr = 9999
Æ WHERE-Klausel nicht benötigt, da alle Tupel der Relation zu ändern sind
Æ Operation verletzt Konsistenz ("Duplicate key")
• Wird vom DBVS nicht ausgeführt / ungeschehen gemacht
• Nicht trivial für DBVS (Fehlerbehandlungsmaßnahme)
• Diskussion des Ausführungsergebnis in MS Access (Atomarität!)
ƒ Beispiel 6: Das Buch mit InvNr = 1201 soll den gleichen Titel bekommen wie
das Buch mit InvNr = 4710
UPDATE Buch
SET Titel = (SELECT Titel FROM Buch WHERE InvNr = 4710)
WHERE InvNr = 1201
Æ Subselect holt den Titel zum Buch mit InvNr = 4710
Æ Ergebnis wird als Titel für das Buch mit InvNr = 1201 gesetzt
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 176
5.3.3 DELETE
ƒ Syntax:
DELETE
FROM Basisrelationenname
[WHERE Bedingung]
ƒ Wirkung:
- Alle Tupel in Basisrelationenname, welche die (optionale) Bedingung
erfüllen, werden gelöscht
- Relationsschema bleibt unverändert (im Gegensatz zu DROP TABLE)
ƒ Beispiel 1:
- Das von Hr. Meyer ausgeliehene Buch mit InvNr = 4711 wurde
zurückgegeben und soll aus der Relation "Ausleihe" gelöscht werden
DELETE
FROM Ausleihe
WHERE InvNr = 4711
- Alternative Bedingung (Name = 'Meyer') löscht eventuell mehr
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 177
5.3.3 DELETE
ƒ Beispiel 2:
- Die Bibliothek hat die Hoffnung aufgegeben, die ausgeliehenen Bücher
jemals zurückzuerhalten
- Löschungs des gesamten Inhalts der Relation "Ausleihe"
DELETE
FROM Ausleihe
- Eigentlich müssten auch noch die entsprechenden Bücher aus Relation
"Buch" gelöscht werden, da "unwiderruflich verschwunden"
ƒ Beispiel 3: Kann eine DELETE-Anweisung zur Integritätsverletzung führen
(analog zu "Duplikate key" beim UPDATE)?
DELETE
FROM Buch
WHERE Titel = 'Objektdatenbanken'
Æ Verletzung der Fremdschlüsselbeziehung zwischen Ausleihe und Buch
Æ Fremdschlüsselwert 4710 für Ausleihe.InvNr würde ins "Leere zeigen"
Æ Zurückweisung der Operation
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 178