Skript Folge 4

Rückblick
• Abbildung E/R-Modell auf Relationales Modell
(Tabellenmodell)
– ENTITY-TYPES
– RELATIONSHIP-TYPES (1:N / N:M / 1:1)
– Generalisierungshierarchie
• SQL-Anweisungen Überblick
– CREATE TABLE
– CREATE DOMAIN [AS] Datentyp [CHECK]
– SELECT ... FROM ... WHERE ...
– UPDATE, INSERT INTO, DELETE FROM,
– Datentypen, Vergleichsoperatoren, Aggregationsfunktionen
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
1
Offene Fragen
schreibweise Datentyp Date:
• Date'1995-06-22'
• Hochkomma: '
• Bei Strings: ' ' wird Gross-Kleinschreibung
unterschieden,
• ansonsten (Schlüsselwörter, Tabellennamen,
Attributnamen) wird Gross-Kleinschreibung nicht
unterschieden .
• INSERT INTO : Zuweisung erfolgt über Position;
bei Textfeldern müssen die Werte in Hochkomma
eingefasst werden.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
2
Ausblick heute
• Relationale Algebra (theoretische Basis)
• SQL detaillierter / systematischer /
vollständiger
– CREATE TABLE
– SELECT ... FROM ... WHERE ...
• JOIN (Verbund)
• NULL-Werte 3wertige Logik / Semantik
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
3
Relationale
Algebra
(Relationenalgebra)
Relationale Algebra
Die
relationale Algebra
(Relationenalgebra)
bildet den formalen Rahmen für
die
relationalen Datenbanksprachen.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
5
Relationale Algebra
Relationale Datenbanksprachen,
die die Operationen der
Relationenalgebra sinngemäss
umsetzen, heissen
relational vollständige
Sprachen.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
6
Relationale Algebra
Die relationale Algebra
(Relationenalgebra) setzt
Relationen (Tabellen) in der
ersten Normalform
voraus.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
7
Relationale Algebra
Man unterscheidet:
• Mengenorientierte Operatoren
• Relationenorientierte Operatoren
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
8
Relationale Algebra
Alle Operatoren der Relationenalgebra
bilden eine oder zwei Tabellen auf eine
neue Tabelle ab.
Tabelle1  Tabelle2
Tabelle1, Tabelle2  Tabelle3
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
9
Relationale Algebra
Mengenorientierte Operatoren:
• Vereinigung
RS
• Durchschnitt
RS
• Differenz
R\S
• Kartesisches Produkt
RxS
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
10
Relationale Algebra
Vorraussetzung
für die Anwendung der
mengenorientierten Operatoren
R  S, R  S, R \ S
ist das
Verträglichkeitskriterium
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
11
Verträglichkeitskriterium
Die in die Operationen
(R  S, R  S, R \ S)
involvierten Tabellen R und S weisen
die gleiche Anzahl Attribute auf und die
Domänen / Datenformate der korrespondierenden Attribute sind identisch.
Die Ergebnistabelle hat dann auch die
gleiche Anzahl Attribute mit den
gleichen Domänen.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
12
Relationale Algebra
Ansonsten können die
Operatoren der
Relationenalgebra
weitgehend
ohne Einschränkung
miteinander kombiniert
werden.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
13
Mengenorientierte Operatoren
Vereinigung
Duchschnitt
Differenz
R
R
R
RS
R\S
RS
S
{t| t  R or t  S}
S
{t| t  R and t  S}
S
{t| t  R and t  S}
R
RxS
Kartesisches Produkt:=
{(x,y): R(x)  S(y)}
S
14
Relationale Algebra
Vereinigung
•
•
•
(R  S):
Die Vereinigungstabelle enthält alle
Datensätze, die in R oder in S enthalten
sind.
Da es sich um die mengentheoretische
Vereinigung handelt, werden Duplikate
dabei eliminiert.
Zur Anwendung der Vereinigung müssen R
und S gleiche Stelligkeit und verträgliche
Domänen haben.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
15
Relationale Algebra
Durchschnitt (R  S):
•
•
•
Die Durchschnittstabelle von R und S
enthält alle Datensätze (Tupel), die in R und
in S enthalten sind.
Alle Datensätze (Tupel) sind nur einmal
enthalten.
Zur Anwendung des Durchschnitts müssen
R und S gleiche Stelligkeit und verträgliche
Domänen haben.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
16
Relationale Algebra
Differenz (R \ S):
•
•
Die Ergebnistabelle der Differenzoperation
zwischen R und S enthält alle Datensätze
(Tupel) , die in R und nicht in S enthalten
sind.
Zur Anwendung des Durchschnitts müssen
R und S gleiche Stelligkeit und verträgliche
Domänen haben.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
17
Relationale Algebra
Kartesisches Produkt (R  S):
•
•
Das Kartesische Produktes R  S zwischen
R und S ist die Menge aller möglichen
Kombinationen (x,y), die sich aus den
Tupeln x aus R und y aus S bilden lassen.
Zur Anwendung des Kartesischen Produktes
muss das Verträglichkeitskriterium nicht
gelten, d.h. die Attribute der beteiligten
Relationen müssen nicht gleiche Stelligkeit
und verträgliche Domänen haben.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
18
Relationale Algebra
Kartesisches Produkt: R x S
Beispiel
Gegeben seien die Relationen
R = {(1,Julia), (2,Michael)}
und
S = {(1,Julia), (3,Toni)}
R x S = {(1,Julia,1,Julia), (1,Julia,3,Toni),
(2, Michael, 1,Julia), (2,Michael, 3,Toni)}
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
19
Beispieltabelle
Mitarbeiter
M# Name
Strasse
Ort
A#-Unt
M1 Meier
Lindstrasse
Liestal
A3
M7 Huber
Mattenweg
Basel
A5
Hauptstrasse
Zürich
A6
Wasserweg
Liestal
A6
M19 Schweizer
M4 Becker
20
Beispieltabelle
Sportclub
M#
Name
Strasse
Ort
M1
Meier
Lindstrasse
Liestal
M7
Huber
Mattenweg
Basel
M19
Schweizer
Hauptstrasse Zürich
21
Beispieltabelle
Fotoclub
M#
Mitglied
Strasse
Ort
M4
Becker
Wasserweg
Liestal
M7
Huber
Mattenweg
Basel
22
Beispieltabelle
Clubmitglieder = Sportclub  Fotoclub
M#
Name
Strasse
Ort
M1
Meier
Lindstrasse
Liestal
M7
Huber
Mattenweg
Basel
M19
Schweizer
Hauptstrasse Zürich
M4
Becker
Wasserweg
Liestal
23
Beispieltabelle
Clubmitglieder = Sportclub  Fotoclub
M#
Name
Strasse
Ort
M7
Huber
Mattenweg
Basel
24
Beispieltabelle
Clubmitglieder = Sportclub \ Fotoclub
M#
Name
Strasse
Ort
M1
Meier
Lindstrasse
Liestal
M19
Schweizer
Hauptstrasse Zürich
25
Relationale Algebra
Relationenorientierte Operatoren:
•
•
•
•
Projektion der Tabelle R auf eine Menge von
Merkmalen M: M (R)
Selektion von Zeilen aus einer Tabelle R
mittels einer Formel F: F (R)
Verbund zweier Tabellen R und S durch
Prädikat P: R |X|P S
Division der Tabelle R durch die Teiltabelle
S:
RS
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
26
Relationenorientierte Operatoren
R
R
M (R)
Projektion
von R auf M
F (R)
Selektion
von R
mittels F
R
R |X|P S
Verbund von R und S
durch Prädikat P
S
R
27
Relationenorientierte Operatoren
R
S
Divison
der Tabelle R
durch
die Teiltabelle S
RS
28
Relationale Algebra
Projektion:
Wir setzen hier voraus, daß M eine
Teilmenge aller Attribute von R (A1,..., An)
ist.
Die Projektion entsteht aus R durch
Weglassen aller Attribute von R, die nicht
in M aufgeführt sind. Dabei auftretende
Duplikate (Datensätze) müssen entfernt
werden.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
29
Relationale Algebra
Projektion:
Beispiel
R = {(42,18,Maria), (99,42,Max), (42,30,Maria)}
Projektion(R,1.Stelle, 3.Stelle) = {(42,Maria), (99, Max)}
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
30
Beispiel - Projektion
Ort (Mitarbeiter)
Ort
Liestal
Basel
Zürich
31
Beispiel 2 Projektion
A#-Unt,Name (Mitarbeiter)
A#-Unt Name
A3 Meier
A5 Huber
A6 Schweizer
A6 Becker
32
Beispieltabelle
Kartesisches Produkt:
Wettkampfpaarungen: (Sportclub \ Fotoclub) x Fotoclub
M#
Name
M1
M1
M19
M19
Meier
Meier
Schweizer
Schweizer
Strasse
Lindstrasse
Lindstrasse
Hauptstrasse
Hauptstrasse
Ort
Liestal
Liestal
Zürich
Zürich
M#
Mitglied
Strasse
M4
M7
M4
M7
Becker
Huber
Becker
Huber
Wasserweg
Mattenweg
Wasserweg
Mattenweg
Ort
Liestal
Basel
Liestal
Basel
33
Relationale Algebra
RS
Setzt voraus, dass S in R enthalten ist!!!
R  S berechnet eine Teiltabelle R´von R mit der
Eigenschaft, dass R´ x S  R.
Division:
R´muss dabei maximal sein.
R´ enthält genau die Attribute von R, die nicht in
S vorkommen.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
34
Beispiel Division
M#
M1
M1
M1
M2
M2
M4
M4
PROJ#
P1
P2
P4
P1
P2
P2
P4
R
Tabelle der den Projekten
zugeordneten Mitarbeiter
R´´= R´ x S
R´
S
M#
M1
M4
PROJ#
P2
P4
Mitarbeiter,
die gleichzeitig an Projekten
P2 und P4 arbeiten
35
Beispiel Verbund: Ausgangstabellen
Mitarbeiter
M#
M19
M1
M7
M4
Name
Schweizer
Meier
Huber
Becker
A#
A3
A5
A6
Strasse
Hauptstrasse
Lindstrasse
Mattenweg
Wasserweg
Bezeichnung
Informatik
Personal
Finanz
Ort
Zürich
Liestal
Basel
Liestal
Unt
A6
A3
A5
A6
Abteilung
36
Beispiel Verbund
Mitarbeiter ||Unt=A# Abteilung
M#
M19
M1
M7
M4
Name
Schweizer
Meier
Huber
Becker
Strasse
Hauptstrasse
Lindstrasse
Mattenweg
Wasserweg
Ort
Zürich
Liestal
Basel
Liestal
Unt
A6
A3
A5
A6
A#
A6
A3
A5
A6
Bezeichnung
Finanz
Informatik
Personal
Finanz
37
Verbund
Es gilt folgende Formel:
R ||P S = P R  S
Dies bedeutet, dass der Verbund
von R und S mittels P durch
das Kartesische Produkt von R und S
plus anschliessender Selektion mittels P
definiert werden kann.
38
Relationale Algebra
•
•
•
•
•
Vereinigung
Differenz
Kartesisches Produkt
Projektion
Selektion
sind die kleinst mögliche Menge von
Operatoren der Relationenalgebra. Alle
anderen können dadurch definiert werden.
Beispiel:
R  S := R \ (R \ S)
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
39
SQL DDL
SQL DDL
CREATE TABLE
ALTER TABLE
DROP TABLE
Konzeptuelle Ebene
CREATE DOMAIN
ALTER DOMAIN
DROP DOMAIN
Konzeptuelle Ebene / SQL 92
CREATE VIEW
DROP VIEW
Externe Ebene
CREATE INDEX
ALTER INDEX
DROP INDEX
Interne Ebene
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
41
CREATE TABLE
Allgemein:
CREATE TABLE table_name
(spaltendefinitionsliste
[,tabellenintegritätsregelliste]);
spaltendefinition ::=
spaltenname typangabe [default-Klausel]
[spaltenintegritätsregelliste]
tabellenintegritätsregel ::=
check-klausel | primary_key-klausel |
unique-klausel | foreign-key-klausel
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
42
CREATE TABLE
Der Tabellenname muss innerhalb der
Datenbank eindeutig sein!
Innerhalb einer Tabellendefinition müssen
die Attributnamen ebenfalls eindeutig
sein!
Integritätsregeln können als Spalten- oder
als Tabellenintegritätsregeln formuliert
werden.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
43
DDL CREATE TABLE
Mögliche Integritätsregeln:
• Primärschlüssel
• weitere Schlüssel (Schlüsselkandidaten)
• Fremdschlüssel mit Bezugstabelle
• Einschränkungen für die Wertebereiche der Spalten
• Verbot von Nullmarken in Spalten
• Spaltenübergreifende Integritätsbedingungen
• tabellenübergreifende Integritätsbedingungen
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
44
Spaltendefinition
Allgemein:
spaltendefinition ::=
spaltenname typangabe [default-Klausel]
[spaltenintegritätsregelliste]
„typangabe“ ist ein Datentyp oder eine in der
Datenbank definierte Domäne.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
45
Spaltendefinition
DEFAULT-Klausel:
default-klausel ::=
DEFAULT NULL | DEFAULT systemvariable | DEFAULT literal
NULL: Standardmässig wird eine Nullmarke eingesetzt.
Mögliche Systemvariable:
CURRENT_USER, CURRENT_DATE, CURRENT_TIME,
CURRENT_TIMESTAMP
literal ist eine explizite Angabe des DEFAULT-Werts. Sie muss zu
der Datentypdefinition bzw. der Domäne der Spalte passen.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
46
Spaltendefinition
DEFAULT-Klausel (Beispiel):
CREATE TABLE vorschlag (
vorschlagsnr
INTEGER NOT NULL,
mitarbeitername
CHAR(30) DEFAULT CURRENT_USER,
datum
DATE DEFAULT CURRENT_DATE,
art
vorschlagsart DEFAULT ‘informatik‘,
vorschlag
VARCHAR(200)
);
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
47
Spaltendefinition
Domain-Definition
CREATE DOMAIN vorschlagsart AS char(30)
CHECK (VALUE IN ('informatik'‚ 'mathematik'));
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
48
Spaltendefinition
Spaltenintegritätsregel (Beispiel):
CREATE TABLE vorschlag (
vorschlagsnr
INTEGER NOT NULL,
mitarbeitername
CHAR(30) DEFAULT CURRENT_USER,
datum
DATE DEFAULT CURRENT_DATE,
art
vorschlagsart DEFAULT ‘informatik‘,
vorschlag
VARCHAR(200)
);
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
49
Spaltendefinition
Spaltenintegritätsregel (Beispiel):
CREATE TABLE Kunde (
kundennr
INTEGER NOT NULL,
status
CHAR(1) NOT NULL
CHECK (status IN (‘W‘, ‘G‘, ‘S‘)),
Name
CHAR(30) NOT NULL,
zahlungsart
CHAR(1) NOT NULL DEFAULT ‘N‘
CHECK (zahlungsart IN (‘R‘, ‘B‘, ‘N‘,‘V‘,‘K‘)),
Ort
CHAR (39) NOT NULL,
PRIMARY KEY (kundennr)
);
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
50
Spalten- Tabellenintegrität
Falls sich eine Tabellenintegritätsregel nur auf eine
Spalte (ein Attribut) bezieht unterscheidet sie sich
inhaltlich (semantisch) und von der Form her nicht
von der sinngleichen Spaltenintegritätsregel.
Der Unterschied ergibt sich nur aus der Position in der
DELETE TABLE Anweisung.
Um spaltenübergreifende Integritätsregeln zu
formulieren muss eine Tabellenintegritätsregel
verwendet werden.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
51
Spalten- Tabellenintegrität
Bei jeder Datenänderung wird sichergestellt,
dass die in den Integritätsregeln formulierten
Bedingungen nicht den Wert false annehmen
können.
Der Wert unknown der dreiwertigen Logik, der
durch NULL-Marken repräsentiert wird, ist
allerdings zulässig
CHECK (liefermenge <= bestellmenge)
wird akzeptiert für liefermenge IS NULL
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
52
Tabellenintegritätsregel
CHECK-Klausel (allgemeine Form):
CHECK (bedingung)
• „bedingung“ ist analog der WHERE-Klausel in der
SELECT-Anweisung definiert.
• Dabei sind auch Unterabfragen zulässig, die sich auf
andere Tabellen in der gleichen DB beziehen.
• Bei jeder Datenänderung wird sichergestellt, dass
„bedingung“ nicht den Wert „false“ annimmt,
„unknown“ ist hingegen erlaubt.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
53
Tabellenintegritätsregel
CHECK-Klausel (Beispiel):
CREATE TABLE Kunde (
kundennr
INTEGER NOT NULL,
status
CHAR(1) NOT NULL,
Name
CHAR(30) NOT NULL,
zahlungsart
CHAR(1) NOT NULL DEFAULT ‘N',
Ort
CHAR (39) NOT NULL,
CHECK (status IN (‘W‘, ‘G‘, ‘S‘)),
CHECK (zahlungsart IN (‘R‘, ‘B‘, ‘N‘,‘V‘,‘K‘))
);
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
54
Tabellenintegritätsregel
PRIMARY KEY-Klausel (allgemeine Form):
PRIMARY KEY (spaltenliste)
Die PRIMARY KEY-Klausel ist nicht verbindlicher
Bestandteil der CREATE TABLE-Anweisung.
Jede Relation muss allerdings einen Primary Key
haben.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
55
Tabellenintegritätsregel
CHECK-Klausel ermöglicht:
• weitere Festlegungen bezüglich der erlaubten
Spaltenwerte
• die Formulierung spaltenübergreifender
Integritätsbedingungen
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
56
Tabellenintegritätsregel
CHECK-Klausel (Beispiel):
CREATE TABLE bestellung (
kundennr
INTEGER NOT NULL,
liefermenge
INTEGER,
bestellmenge
INTEGER,
CHECK (liefermenge <= bestellmenge)
);
Es ist zulässig, dass eines der beiden Attribute einen
Nullwert hat.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
57
Tabellenintegritätsregel
PRIMARY KEY-Klausel (Beispiel 1):
CREATE TABLE bestellung (
kundennr
INTEGER NOT NULL,
liefermenge
INTEGER,
bestellmenge
INTEGER,
CHECK (liefermenge <= bestellmenge),
PRIMARY KEY (kundennr)
);
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
58
Tabellenintegritätsregel
PRIMARY KEY-Klausel (Beispiel 1):
CREATE TABLE bestellung (
kundennr
INTEGER,
liefermenge
INTEGER,
bestellmenge
INTEGER,
CHECK (liefermenge <= bestellmenge),
PRIMARY KEY (kundennr)
);
Auf die Angabe „NOT NULL“ kann verzichtet werden:
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
59
Tabellenintegritätsregel
PRIMARY KEY-Klausel (Beispiel 2):
Bei einspaltigem Primärschlüssel ist auch folgende
Festlegung möglich:
CREATE TABLE bestellung (
kundennr
INTEGER PRIMARY KEY,
liefermenge
INTEGER,
bestellmenge
INTEGER,
CHECK (liefermenge <= bestellmenge)
);
Der Schlüssel bezieht sich dennoch auf die ganze Tabelle!
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
60
Tabellenintegritätsregel
Beispiel:
CREATE TABLE Kunde (
kundennr
INTEGER NOT NULL,
status
CHAR(1) NOT NULL,
Name
CHAR(30) NOT NULL,
zahlungsart
CHAR(1) NOT NULL DEFAULT ‘N',
Ort
CHAR (39) NOT NULL,
CHECK (status IN (‘W‘, ‘G‘, ‘S‘)),
CHECK (zahlungsart IN (‘R‘, ‘B‘, ‘N‘,‘V‘,‘K‘)),
PRIMARY KEY (kundennr)
);
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
61
Tabellenintegritätsregel
FOREIGN KEY-Klausel (allgemeine Form):
foreign_key-klausel ::=
FOREIGN KEY (spaltenliste)
REFERENCES tabellenname [(spaltenliste)]
[ON DELETE änderungsaktion]
ON UPDATE änderungsaktion]
Unterstützung der referentiellen Integrität!!!
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
62
Tabellenintegritätsregel
FOREIGN KEY-Klausel (Beispiel):
CREATE TABLE bestellung (
bestellnr
INTEGER NOT NULL,
kundennr
INTEGER NOT NULL,
bestelldatum
DATE NOT NULL,
lieferdatum
DATE,
rechnungsbetrag
MONEY,
PRIMARY KEY (bestellnr),
FOREIGN KEY (kundennr) REFERENCES Kunde
);
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
63
Tabellenintegritätsregel
FOREIGN KEY-Klausel (Beispiel-Kurzform):
CREATE TABLE bestellung (
bestellnr
INTEGER NOT NULL,
kundennr
INTEGER NOT NULL
FOREIGN KEY REFERENCES Kunde,
bestelldatum
DATE NOT NULL,
lieferdatum
DATE,
rechnungsbetrag
MONEY,
PRIMARY KEY (bestellnr)
);
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
64
FOREIGN KEY-Klausel
Aktionsregeln für referentielle Integrität:
änderungsaktion ::=
NO ACTION | CASCADE |
SET NULL | SET DEFAULT
Änderung am Primärschlüssel können
–
–
–
–
verboten werden (NO ACTION)
an Fremdschlüssel weitergeben werden (CASCADE)
Fremdschlüsselwert auf Null setzen (SET NULL)
Fremdschlüsselwert auf Defaultwert setzen (SET DEFAULT)
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
65
FOREIGN KEY-Klausel
NO ACTION
Jegliche Änderung an den referenzierten
Schlüsselwerten ist untersagt, wenn es
korrespondierende Fremdschlüsselwerte gibt.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
66
FOREIGN KEY-Klausel
CASCADE
Die per Fremdschlüsselwert
korrespondierenden Zeilen in der abhängigen
Tabelle werden entsprechend manipuliert,
wenn der Schlüsselwert gelöscht oder
geändert wird.
Wird z.B. ein Kunde gelöscht, werden auch
die zugehörigen Bestellungen gelöscht.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
67
FOREIGN KEY-Klausel
SET NULL
Korrespondierende Fremdschlüsselwerte
werden auf NULL gesetzt, wenn der
referentielle Schlüssel manipuliert wird.
Dies setzt voraus, dass für die
Fremdschlüsselwerte Nullmarken zugelassen
sind.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
68
FOREIGN KEY-Klausel
SET DEFAULT
Alle entsprechenden Fremdschlüsselwerte
werden auf den bei der Tabellendefinition mit
der DEFAULT-Klausel definierten Default-Wert
gesetzt, wenn der Primärschlüssel verändert
wird.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
69
Tabellenintegritätsregel
FOREIGN KEY-Klausel (Beispiel 2):
CREATE TABLE position (
bestellnr
INTEGER NOT NULL,
artikelnr
INTEGER NOT NULL,
bestellmenge
INTEGER NOT NULL,
liefermenge
INTEGER,
gesamtpreis
MONEY,
PRIMARY KEY (bestellnr, artikelnr),
FOREIGN KEY (bestellnr) REFERENCES bestellung
ON UPDATE CASCADE
ON DELETE CASCADE);
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
70
Tabellenintegritätsregel
FOREIGN KEY-Klausel (Beispiel 3):
Bei vorliegen der Regel (in der Tabelle „bestellung“)
FOREIGN KEY (kundennr) REFERENCES kunde
wird
DROP TABLE kunde
nicht ausgeführt.
DROP TABLE kunde CASCADE
löscht die ganze Integritätsdefinition aus der Tabelle
„bestellung“.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
71
Tabellenintegritätsregel
UNIQUE-Klausel (allgemeine Form):
unique-klausel ::=
UNIQUE(spaltenliste)
dient dazu, Schlüsselkandidaten zu verwalten
Schlüsselkandidaten dürfen wie Primärschlüssel
nicht mehrfach vorkommen.
NULL-Werte sind allerdings erlaubt, d.h. sie
werden nicht automatisch ausgeschlossen.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
72
Tabellenintegritätsregel
UNIQUE-Klausel (Beispiel):
CREATE TABLE Kunde (
kundennr
INTEGER NOT NULL,
status
CHAR(1) NOT NULL,
Name
CHAR(30) NOT NULL,
zahlungsart
CHAR(1) NOT NULL DEFAULT ‘N',
Ort
CHAR (39) NOT NULL,
debitoren_kontonr INTEGER NOT NULL,
UNIQUE (debitoren_kontonr),
PRIMARY KEY (kundennr)
);
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
73
Tabellenintegritätsregel
UNIQUE-Klausel (Beispiel 2):
CREATE TABLE Kunde (
kundennr
INTEGER NOT NULL,
...
debitoren_kontonr INTEGER NOT NULL UNIQUE,
PRIMARY KEY (kundennr)
);
UNIQUE kann bei einspaltigem Kandidatenschlüssel
auch in Kurzform als Bestandteil der Spaltendefinition
verwendet werden
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
74
tabellenübergreifende Integritätsregeln
ASSERTION-Statement
CREATE ASSERTION assertion-name
CHECK (bedingung) [attributliste]
„bedingung“ formuliert die Bedingung, die nicht verletzt
werden darf
Ist in vielen DBMS noch nicht realisiert.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
75
tabellenübergreifende Integritätsregeln
ASSERTION-Statement (Beispiel):
CREATE ASSERTION kunde_zahlart
CHECK (NOT EXISTS (
SELECT * FROM kunde k
WHERE zahlungsart =‘B‘
AND NOT EXISTS(
SELECT * FROM girokonto g
WHERE g.kundennr = k.kundennr
)))
;
Zahlungsart =„B“ nur dann, wenn Bankkonto bekannt!
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
76
ALTER TABLE
Schema-Evolution in SQL2:
ALTER TABLE <relation>
DROP COLUMN <attribute>;
ALTER TABLE <relation>
ADD COLUMN <attribute> <domain>;
Keines der vorhandenen Applikationsprogramme
sollte dadurch beeinträchtigt werden, es sei denn, es
verwendet das zu löschende Attribut.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
77
CAST-Funktion zur Typkonversion
Allgemein:
CAST (Skalarer Ausdruck AS [datentyp | domäne])
Beispiel:
CAST(‘344‘ AS INTEGER)
Die Zeichenfolge ‘344‘ wird in die Zahl 344
umgeformt. (erfolgt i.a. Fall nur, wenn es möglich ist.)
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
78
Übungsaufgabe
Schemadefinition
Vertriebsdatenbank
(Web-Shop)
in SQL
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
79
Preis
Anzahl
PNr
BNr
Produkt
n
n
n
m
1
m
Bestellung
n
n
Posten
LDATUM
LPreis
liefert
BDatum
liefert
aus
beschreibt
1
Spediteur
1
SNr
Lieferant
Kategorie
LNr
KaNr
n
bestellt
KuNr
1
Preis Anzahl
Kunde
1
offeriert
bearbeitet
1
Mitarbeiter
1
MNr
OPosten
n
n
n
Offerte
ONr
bearbeitet
Offerte
80
Tabellen Vertriebsdatenbank
Kunde(KuNr, Name, Adresse, Rabatt)
Produkt(PNr, P-Name, KaNr, LNr, Preis, LPreis)
Bestellung(BNr,KuNr,MNr,SNr,Bestelldatum,
Lieferdatum)
Lieferant(LNr,Name, Adresse)
Kategorie(KaNr,Name)
Spediteur(SNr, Name, Adresse)
Mitarbeiter(MNr,Name, Adresse)
Offerte(Onr, KuNr, MNr)
Posten(PNr,BNr,Anzahl)
Oposten(PNr,ONr,Anzahl,Preis)
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
81
Lösungsbeispiel 1
CREATE TABLE Kunde (
KuNr
integer PRIMARY KEY,
Name
char(20) NOT NULL,
Adresse char(50),
Rabatt
Decimal(3,1)
);
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
82
Lösungsbeispiel 2
CREATE TABLE Bestellung (
BNr
integer PRIMARY KEY,
KuNr
integer NOT NULL,
MNr
integer,
SNr
integer,
Bestelldatum
Date DEFAULT CURRENT_DATE,
Lieferdatum
Date,
FOREIGN KEY (KuNr) REFERENCES Kunde ON DELETE NO
ACTION ON UPDATE CASCADE,
FOREIGN KEY (MNr) REFERENCES Mitarbeiter ON DELETE
SET NULL ON UPDATE CASCADE,
FOREIGN KEY (SNr) REFERENCES Spediteur ON DELETE
SET NULL ON UPDATE CASCADE
);
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
83
Lösungsbeispiel 3
CREATE TABLE Posten (
BNr
integer,
PNr
integer,
Anzahl
integer NOT NULL,
PRIMARY KEY
(BNr, PNr),
FOREIGN KEY (BNr) REFERENCES Bestellung ON
DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (PNr) REFERENCES Produkt ON
DELETE NO ACTION ON UPDATE CASCADE
);
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
84
Aufgabe Bibliothek
• Modellieren Sie eine Bibliothek (Bücher/ Ausleihe / Autoren /
Benutzer (Ausleihende) / Vormerkungen) im E/R-Modell
(möglichst redundanzfrei).
Folgendes sollte dabei berücksichtigt werden:
• Ein Buch kann mehrere Autoren haben.
• Ein Buch kann in verschiedenen Auflagen vorliegen.
• Jede Auflage eines Buches kann in mehreren Exemplaren in
der Bibliothek vorhanden sein.
• Bücher sollten nach explizit zugeordneten Schlagworten
gesucht werden können.
• Übersetzen Sie das E/R-Modell möglichst redundanzfrei ins
relationale Modell (Datenbankschema in Tabellenform)
• Geben Sie alle SQL-Kommandos an, die zur Anlage der
Tabellen notwendig sind (inklusive aller sinnvollen
Integritätsregeln).
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
85