Grundlagen

Grundlagen (3)
„
Sprachentwicklung von SQL
„
5. Die Standardsprache SQL
„
„
SQL wurde „de facto“-Standard in der relationalen Welt
(1986 von ANSI, 1987 von ISO akzeptiert)
Weiterentwicklung des Standards in drei Stufen
Inhalt
„
SQL2 (1992)
Grundlagen
„
(SQL3) SQL:1999
Mengenorientierte Anfragen (Retrieval)
„
SQL:2003
Möglichkeiten der Datenmanipulation
„
Möglichkeiten der Datendefinition
Beziehungen und referentielle Integrität
Mächtigkeit von SQL
„
Schemaevolution
Indexierung
„
Sichten
N. Ritter, DIS, SS 2005, Kapitel 5
1
Auswahlvermögen äquivalent dem Relationenkalkül und der
Relationenalgebra
Trotz Vermeidung von mathematischen Konzepten wie Quantoren
relational vollständig
N. Ritter, DIS, SS 2005, Kapitel 5
Grundlagen (1)
„
Spezielle Sprachkonstrukte für den DBA
4
Grundlagen (4)
Seit 1974 viele Sprachentwürfe
„
SQL: abbildungsorientierte Sprache
„
SQUARE: Specifying Queries As Relational Expressions
„
SEQUEL: Structured English Query Language, Weiterentwicklung zu
FROM
„
SQL: Structured Query Language
WHERE ...
„
„
„
strukturierte Sprache, die auf englischen Schlüsselwörtern
basiert
„
Grundbaustein:
SELECT ...
...
Abbildung
Ein bekanntes Attribut oder eine Menge von Attributen wird mit Hilfe einer
Relation in ein gewünschtes Attribut oder einer Menge von Attributen
abgebildet.
Weitere ähnliche Sprachen: QUEL, . . .
http://www.cse.iitb.ernet.in:8000/proxy/db/~dbms/Data/Papers-Other/SQL1999/
„
Allgemeines Format
(SQL-Standard Dokumente + einige Artikel)
<Spezifikation der Operation>
http://www.wiscorp.com/sql99.html
<Liste der referenzierten Tabellen>
(Artikel + Präsentationen zu SQL:1999)
[WHERE Boolescher Prädikatsausdruck]
N. Ritter, DIS, SS 2005, Kapitel 5
2
N. Ritter, DIS, SS 2005, Kapitel 5
Grundlagen (2)
„
Grundlagen (5)
Sprachentwicklung von SQL
„
„
„
„
„
Entwicklung einer vereinheitlichten DB-Sprache für alle Aufgaben der
DB-Verwaltung
SQL92-Syntax (Auszug, Table=Relation, Column=Attribut, Listenelemente durch Komma getrennt)
SQL-statement: query
INSERT INTO
Erweiterung der Anfragesprache zur „natürlichen“ Formulierung
bestimmter Fragen
DELETE FROM
gezielte Verbesserungen verschiedener Sprachkonstrukte zur
UPDATE
Komplexität:
„
einfache Anfragemöglichkeiten für den gelegentlichen Benutzer
„
mächtige Sprachkonstrukte für den besser ausgebildeten Benutzer
N. Ritter, DIS, SS 2005, Kapitel 5
view-name
3
table-name
view-name
DROP TABLE
table-name
set-list
(
(
)
attr-name-list
WHERE
SET
CREATE VIEW
DROP VIEW
(
table-name
view-name
table-name
view-name
CREATE TABLE
leichter Zugang durch verschiedene „Sprachebenen“ anwachsender
query
table-name
Lehrexperimente mit Studenten mit und ohne Programmiererfahrung
Erleichterung des Verständnisses und zur Reduktion von Fehlern
„
5
VALUES
constant-list
condition
WHERE
attr-defn-list
attr-name-list
condition
, PRIMARY KEY (
)
AS
attr-name-list
)
)
query
view-name
N. Ritter, DIS, SS 2005, Kapitel 5
6
1
Grundlagen (6)
Anfragen (1)
SQL92-Syntax (Forts.)
„
„
SELECT-Anweisung
select-exp
::=
bitte keine
Aggregatfunktionen
verwenden
query: SELECT
expr-list
DISTINCT
GROUP BY
FROM
from-list
attr-spec-list
HAVING
condition
WHERE
*
ORDER BY
condition
ASC
attr-spec-list
„
DESC
N. Ritter, DIS, SS 2005, Kapitel 5
7
expr:
„
SELECT
„
FROM-Klausel:
*:
spezifiziert zu verarbeitende Relation bzw.
Ausgabe ‚ganzer‘ Tupel
„
WHERE-Klausel:
Sammlung (elementarer) Prädikate der Form
Ai Θ ai oder Ai Θ Aj (Θ ∈ { =, <>, <, <, >, > }),
die mit AND und OR verknüpft sein können
10
Unser Beispiel-Schema:
predicate: expr
condition
condition
AND
condition
condition
OR
condition
comparison
expr
comparison
expr
attr-spec
expr
arithm.op
expr
TITEL
ANY
SCHAUSPIELER (SP)
PNR
query
W-ORT NAME
IS
ROLLE (RO)
NOT
attr-spec
LIKE
NOT
FIGUR
NULL
N. Ritter, DIS, SS 2005, Kapitel 5
8
A-JAHR A-ORT THEATER
N. Ritter, DIS, SS 2005, Kapitel 5
Grundlagen (8)
11
Anfragen (3)
SQL92-Syntax (Forts.)
„
Untermengenbildung
Welche Dramen von Goethe wurden nach 1800 uraufgeführt?
table-name
from
R-TYP
DARSTELLER (DA)
PNR FIGUR
view-name
TITEL
constant
arithm.op: + - * /
„
AUTOR KRITIKER U-ORT U-JAHR
query
query
attr-spec
comparison: = <> < > <= >=
DRAMA (DR)
expr-list
AL L
expr-list
IN
NOT
EXISTS
function
query
comparison
expr-list
constant
from:
DICHTER (DI)
AUTOR G-ORT G-JAHR
expr
predicate
NOT
„
Anfragen (2)
SQL92-Syntax (Forts.)
condition:
Grob:
N. Ritter, DIS, SS 2005, Kapitel 5
Grundlagen (7)
„
SELECT [ALL | DISTINCT] select-item-commalist
FROM table-ref-commalist
[WHERE cond-exp]
[GROUP BY column-ref-commalist]
[HAVING cond-exp]
SELECT
FROM
WHERE
var-name
LEFT JOIN
from
ON
condition
set:
attr-spec
=
expr
function: AVG(
expr
MAX(
DISTINCT
attr-spec
MIN(
SUM(
COUNT(*)
)
COUNT(DISTINCT attr-spec
attr-defn:
attr-name
„
)
attr-name
„
Ausgabe von Attributen, Text oder Ausdrücken
Spalten der Ergebnisrelation können (um)benannt werden (AS)
table-name
.
var-name
attr-name
„
Beispiel:
SELECT
INTEGER
NUMERIC(
DECIMAL(
CHAR(
VARCHAR(
N. Ritter, DIS, SS 2005, Kapitel 5
Benennung von Ergebnis-Spalten
„
attr-spec:
length
)
*
DRAMA
AUTOR = ‘Goethe’ AND U-JAHR > 1800;
FROM
NAME,
‘Berechnetes Alter: ‘ AS TEXT,
CURRENT_DATE – GEBDAT AS ALTER
SCHAUSPIELER;
NOT NULL
9
N. Ritter, DIS, SS 2005, Kapitel 5
12
2
Anfragen (4)
„
Anfragen (7)
Test auf Mengenzugehörigkeit
„
„
„
Symmetrische Abbildung (Forts.)
Finde die Dichter (AUTOR, G-ORT), deren Dramen von Dichtern
mit demselben Geburtsort (G-ORT) kritisiert wurden.
Ai IN (a1, aj, ak)
explizite Mengendefinition
Ai IN (SELECT . . .) implizite Mengendefinition
SELECT A.AUTOR, A.G-ORT
Beispiel:
FROM
Finde die Schauspieler (PNR), die Faust, Hamlet oder Wallenstein
SELECT DISTINCT PNR
FROM
DARSTELLER
„
Default: keine Duplikateliminierung
„
DISTINCT erzwingt Duplikateliminierung
13
N. Ritter, DIS, SS 2005, Kapitel 5
„
Welche Figuren kommen in Dramen von Schiller oder Goethe vor?
SELECT
FROM
äußere
Abbildung WHERE
16
Anfragen (8)
Geschachtelte Abbildung
„
A.G-ORT = B.G-ORT;
D.KRITIKER = B.AUTOR in der erhaltenen Lösung?
Anfragen (5)
„
D.KRITIKER = B.AUTOR
AND
Duplikateliminierung
N. Ritter, DIS, SS 2005, Kapitel 5
„
AND
Diskussion: Welche Rolle spielen die Bedingungen A.AUTOR = D.AUTOR und
WHERE FIGUR IN („Faust“, „Hamlet“, „Wallenstein“);
„
DICHTER A, DRAMA D, DICHTER B
WHERE A.AUTOR = D.AUTOR
gespielt haben.
Symmetrische Abbildung (Forts.)
Finde die Schauspieler (NAME, W-ORT), die bei in Weimar uraufgeführten Dramen an ihrem Wohnort als ’Held’ mitgespielt haben.
DISTINCT FIGUR
ROLLE
TITEL IN (
SELECT TITEL
FROM
DRAMA
innere
WHERE AUTOR IN („Schiller“, „Goethe“));
Abbildung
A:
SELECT S.NAME, S.W-ORT
FROM
SCHAUSPIELER S, DARSTELLER D, ROLLE R, DRAMA A
WHERE
Innere und äußere Relationen können identisch sein
Eine geschachtelte Abbildung kann beliebig tief sein
F1
F2
F3
F4
F5
F6
S.PNR = D.PNR
AND
D.FIGUR = R.FIGUR
AND
R.TITEL = A.TITEL
AND
A.U-ORT = ’Weimar’
AND
R.R-TYP = ’Held’
AND
D.A-ORT = S.W-ORT;
Diskussion: Wie sieht das Auswertungsmodell (Erklärungsmodell) bei
symmetrischer Notation aus?
N. Ritter, DIS, SS 2005, Kapitel 5
14
N. Ritter, DIS, SS 2005, Kapitel 5
Anfragen (6)
„
Anfragen (9)
Symmetrische Abbildung
„
Finde die Figuren und ihre Autoren, die in Dramen von Schiller oder Goethe
vorkommen.
SELECT
FROM
WHERE
Einführung von Tupelvariablen (correlation names) erforderlich
„
Vorteile der symmetrischen Notation
„
Ausgabe von Größen aus inneren Blöcken
„
keine Vorgabe der Auswertungsrichtung (DBS optimiert !)
„
Auswertungs-/Erklärungsmodell
„
Einfacher Operatorbaum für Anfrage A (siehe Folie 17)
FIGUR, AUTOR
ROLLE RO, DRAMA DR
(RO.TITEL=DR.TITEL) AND
(DR.AUTOR=„Schiller“ OR DR.AUTOR=„Goethe“);
„
„
17
πNAME, W-ORT
σF1 ∧ F2 ∧ F3 ∧ F4 ∧ F5 ∧ F6
Optimierung?
×
×
×
SP
direkte Formulierung von Vergleichsbedingungen über Relationengrenzen hinweg möglich
DA
RO
DR
einfache Formulierung des Verbundes
N. Ritter, DIS, SS 2005, Kapitel 5
15
N. Ritter, DIS, SS 2005, Kapitel 5
18
3
Anfragen (10)
„
Anfragen (13)
Auswertungs-/Erklärungsmodell (Forts.)
„
„
Optimierter Operatorbaum für Anfrage A (siehe Folie 17)
Aggregatfunktionen (Forts.)
„
πNAME, W-ORT
„
Heuristische Optimierungsregeln:
σF6
1. Führe Selektionen so früh wie möglich aus!
2. Bestimme die Verbundreihenfolge so, dass
die Anzahl und Größe der Zwischenobjekte
minimiert wird!
F1
„
„
„
F2
F3
SP
DA
σF5
σF4
RO
DR
N. Ritter, DIS, SS 2005, Kapitel 5
19
Aggregat-Funktion (AVG) wird angewendet auf einstellige Ergebnisliste
(GEHALT)
keine Eliminierung von Duplikaten
Verwendung von arithmetischen Ausdrücken ist möglich:
AVG (GEHALT/12)
An wievielen Orten wurden Dramen uraufgeführt (U-Ort)?
SELECT
COUNT (DISTINCT U-ORT)
FROM
DRAMA;
N. Ritter, DIS, SS 2005, Kapitel 5
Anfragen (11)
„
Auswertung
Anfragen (14)
Benutzer-spezifizierte Reihenfolge der Ausgabe
„
Aggregatfunktionen (Forts.)
„
ORDER BY order-item-commalist
An welchen Orten wurden mehr als zwei Dramen uraufgeführt ?
SELECT
Finde die Schauspieler, die an einem Ort wohnen, an dem sie gespielt haben,
sortiert nach Name (aufsteigend), W-Ort (absteigend).
SELECT
FROM
WHERE
ORDER BY
„
22
S.NAME, S.W-ORT
SCHAUSPIELER S, DARSTELLER D
S.PNR = D.PNR AND S.W-ORT = D.A-ORT
S.NAME ASC, S.W-ORT DESC;
Ohne Angabe der ORDER-BY-Klausel wird die Reihenfolge der Ausgabe
DISTINCT U-ORT
FROM
DRAMA
WHERE
COUNT(U-ORT)>2;
„
keine geschachtelte Nutzung von Funktionsreferenzen !
„
Aggregat-Funktionen in WHERE-Klausel unzulässig !
SELECT
DISTINCT U-ORT
FROM
DRAMA D
WHERE
2<
(SELECT COUNT(*)
FROM
durch das System bestimmt (Optimierung der Auswertung).
DRAMA X
WHERE X.U-ORT = D.U-ORT);
N. Ritter, DIS, SS 2005, Kapitel 5
20
N. Ritter, DIS, SS 2005, Kapitel 5
Anfragen (12)
„
Anfragen (15)
Aggregatfunktionen
„
aggregate-function-ref
::= COUNT(*)
| {AVG | MAX | MIN | SUM | COUNT}
([ALL | DISTINCT] scalar-exp)
„
„
23
Aggregatfunktionen (Forts.)
„
Standard-Funktionen: AVG, SUM, COUNT, MIN, MAX
„
Elimination von Duplikaten : DISTINCT
Welches Drama wurde zuerst aufgeführt ?
SELECT
TITEL, MIN(U-JAHR)
FROM
DRAMA;
SELECT
TITEL, U-JAHR
„
keine Elimination : ALL (Defaultwert)
FROM
DRAMA
„
Typverträglichkeit erforderlich
WHERE
U-JAHR = (SELECT MIN(U-JAHR)
FROM DRAMA X);
Bestimme das Durchschnittsgehalt der Schauspieler, die älter als 50 Jahre sind
(GEHALT und ALTER seien Attribute von SP).
SELECT AVG(GEHALT) AS Durchschnittsgehalt
FROM
SCHAUSPIELER
WHERE ALTER > 50;
N. Ritter, DIS, SS 2005, Kapitel 5
21
N. Ritter, DIS, SS 2005, Kapitel 5
24
4
Anfragen (16)
„
Anfragen (19)
Partitionierung
„
Hierarchische Beziehungen auf einer Relation (Forts.)
„
GROUP BY column-ref-commalist
Beispielschema: PERS (PNR, NAME, GEHALT, ALTER, ANR)
PRIMARY KEY (PNR)
PERS
Liste alle Abteilungen und das Durchschnittsgehalt ihrer Angestellten auf
(Monatsgehalt).
SELECT
FROM
GROUP BY
Erklärung der Auswertung der Formel
X.MNR = Y.PNR AND X.GEHALT > Y.GEHALT
in Anfrage B (siehe vorhergehende Folie) am Beispiel
ANR, AVG(GEHALT)
PERS
ANR;
PNR
25
406
Abel
50 K
829
123
Maier
60 K
829
123
Maier
60 K
829
829
Müller
55 K
574
829
Müller
55 K
574
574
May
50 K
999
574
May
50 K
999
„
Liste die Abteilungen zwischen K50 und K60 auf, bei denen das Durchschnittsalter
ihrer Angestellten kleiner als 30 ist.
PERS
„
WHERE ANR > K50 AND ANR < K60
GROUP BY ANR
„
HAVING AVG(ALTER) < 30;
Diskussion: Allgemeines Erklärungsmodell?
„
26
„
60 K
Müller
Müller
55 K
May
28
Qualifizierte Tupeln werden gemäß der GROUP-BY-Klausel in Gruppen
eingeteilt.
Gruppen werden ausgewählt, wenn sie die HAVING-Klausel erfüllen. Prädikat in
der HAVING-Klausel darf sich nur auf Gruppeneigenschaften beziehen
(Attribute der GROUP-BY-Klausel oder Anwendung von Aggregat-Funktionen).
Die Ausgabe wird durch die Auswertung der SELECT-Klausel abgeleitet. Wurde
eine GROUP-BY-Klausel spezifiziert, dürfen als SELECT-Elemente nur
Ausdrücke aufgeführt werden, die für die gesamte Gruppe genau einen Wert
ergeben (Attribute der GROUP-BY-Klausel oder Anwendung von AggregatFunktionen).
Die Ausgabereihenfolge wird gemäß der ORDER-BY-Klausel hergestellt. Wurde
keine ORDER-BY-Klausel angegeben, ist die Ausgabereihenfolge
systembestimmt (indeterministisch).
29
Erklärungsmodell – Beispiel
PERS (PNR, NAME, GEHALT, MNR)
PRIMARY KEY (PNR)
FOREIGN KEY MNR REFERENCES PERS
R
FROM R
Finde die Angestellten, die mehr als ihre (direkten) Manager
verdienen (Ausgabe: NAME, GEHALT, NAME des Managers).
SELECT X.NAME, X.GEHALT, Y.NAME
FROM
PERS X, PERS Y
WHERE X.MNR = Y.PNR AND
X.GEHALT > Y.GEHALT;
N. Ritter, DIS, SS 2005, Kapitel 5
Maier
Anfragen (21)
Hierarchische Beziehungen auf einer Relation
B:
Y.NAME
N. Ritter, DIS, SS 2005, Kapitel 5
Anfragen (18)
Beispielschema:
X.GEHALT
Auswertung von SELECT-Anweisungen – Erklärungsmodell
„
Die auszuwertenden Relationen werden durch die FROM-Klausel bestimmt.
Alias-Namen erlauben die mehrfache Verwendung derselben Relation.
„
Das Kartesische Produkt aller Relationen der FROM-Klausel wird gebildet.
„
Tupeln werden ausgewählt durch die WHERE-Klausel.
„
SELECT ANR
„
X.NAME
N. Ritter, DIS, SS 2005, Kapitel 5
HAVING cond-exp
N. Ritter, DIS, SS 2005, Kapitel 5
NAME GEH. MNR
829
Anfragen (20)
Partitionierung (Forts.)
FROM
PNR
50 K
AUSGABE
Anfragen (17)
„
PERS
Abel
Die GROUP-BY-Klausel wird immer zusammen mit einer Aggregat-Funktion benutzt.
Die Aggregat-Funktion wird jeweils auf die Tupeln einer Gruppe angewendet. Die
Ausgabe-Attribute müssen verträglich miteinander sein!
N. Ritter, DIS, SS 2005, Kapitel 5
NAME GEH. MNR
406
R‘
WHERE B <= 50
27
N. Ritter, DIS, SS 2005, Kapitel 5
A
B
C
Rot
Rot
Gelb
Rot
Gelb
Blau
Blau
Blau
10
20
10
10
80
10
80
20
10
10
50
20
180
10
10
200
A
B
C
Rot
Rot
Gelb
Rot
Gelb
Blau
Blau
Blau
10
20
10
10
80
10
80
20
10
10
50
20
180
10
10
200
30
5
Anfragen (22)
„
Anfragen (25)
Erklärungsmodell – Beispiel (Forts.)
R‘‘
GROUP BY A
R‘‘
HAVING MAX(C)>100
R‘‘‘‘
SELECT A, SUM(B), 12
R‘‘‘‘‘
ORDER BY A
A
B
C
Rot
Rot
Rot
Gelb
Blau
Blau
10
20
10
10
10
20
10
10
20
50
10
200
A
B
„
PNR
ANR
GEH
BONUS
ALTER
0815
4711
K45
K45
80K
30K
0
1
52
42
1111
K45
50K
2
43
C
1234
K56
40K
3
31
7777
0007
K56
K56
80K
20K
3
3
45
41
Rot
Rot
Rot
Gelb
Blau
Blau
10
20
10
10
10
20
10
10
20
50
10
200
A
SUM(B)
12
Blau
30
12
A
SUM(B)
12
Blau
30
12
N. Ritter, DIS, SS 2005, Kapitel 5
PERS
Die Summe der Gehälter pro Abteilung, in der mindestens ein Mitarbeiter 40 Jahre
oder älter ist, soll berechnet werden:
SELECT
FROM
WHERE
GROUP BY
HAVING
31
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
„
BONUS
ALTER
PNR
ANR
GEH
BONUS
ALTER
0815
4711
K45
K45
80K
30K
0
1
52
42
0815
4711
K45
K45
80K
30K
0
1
52
42
1111
K45
50K
2
43
1111
K45
50K
2
43
1234
K56
40K
3
31
1234
K56
40K
3
31
7777
0007
K56
K56
80K
20K
3
3
45
41
7777
0007
K56
K56
80K
20K
3
3
45
41
PERS
Die Summe der Gehälter pro Abteilung, in der mindestens ein Mitarbeiter 40 Jahre
oder älter ist, soll berechnet werden:
ANR, SUM(GEH)
PERS
BONUS <> 0
ANR
(COUNT(*) > 1)
ANR DESC
ANR
SUM(GEH)
K56
K45
SELECT
FROM
GROUP BY
HAVING
140K
80K
32
ANR, SUM(GEH)
PERS
ANR
(MAX(ALTER) >= 40)
ANR
SUM(GEH)
K45
K56
160K
140K
N. Ritter, DIS, SS 2005, Kapitel 5
35
Anfragen (27)
„
Suchbedingungen
„
PNR
ANR
GEH
BONUS
ALTER
0815
4711
K45
K45
80K
30K
0
1
52
42
1111
K45
50K
2
43
1234
K56
40K
3
31
„
7777
0007
K56
K56
80K
20K
3
3
45
41
„
„
„
„
„
„
ANR, SUM(GEH)
PERS
BONUS <> 0
ANR
(COUNT(DISTINCT BONUS) > 1)
ANR DESC
ANR
K45
„
SUM(GEH)
„
80K
Vergleichsprädikate
BETWEEN-Prädikate
IN-Prädikate
Ähnlichkeitssuche
Prädikate über Nullwerten
comparison-cond
::=
row-constructor
::=
row-constructor
Θ row-constructor
scalar-exp |
(scalar-exp-commalist) |
(table-exp)
Quantifizierte Prädikate mit Hilfe von ALL, ANY, EXISTS
Weitere Prädikate
„
„
33
Sammlung (elementarer) Prädikate
Verknüpfung mit AND, OR, NOT
Ggf. Bestimmung der Auswertungsreihenfolge durch Klammerung
Nicht-quantifizierte Prädikate
„
N. Ritter, DIS, SS 2005, Kapitel 5
160K
100K
Erklärungsmodell – weitere Beispiele
GEH
Erklärungsmodell – weitere Beispiele
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
SUM(GEH)
34
ANR
Anfragen (24)
PERS
K45
K56
PNR
N. Ritter, DIS, SS 2005, Kapitel 5
„
ANR
Anfragen (26)
Erklärungsmodell – weitere Beispiele
PERS
ANR, SUM(GEHALT)
PERS
ALTER >= 40
ANR
(COUNT(*) >= 1)
N. Ritter, DIS, SS 2005, Kapitel 5
Anfragen (23)
„
Erklärungsmodell – weitere Beispiele
MATCH-Prädikat
UNIQUE-Prädikat
N. Ritter, DIS, SS 2005, Kapitel 5
Beispiel: GEHALT BETWEEN 80K AND 100K
36
6
Anfragen (28)
„
IN-Prädikate
Anfragen (31)
„
row-constr [NOT] IN (table-exp)
Prädikate über Nullwerten
„
„
x IN (a, b, . . ., z)
⇔ x = a OR x = b . . . OR x = z
„
row-constr IN (table-exp)
⇔ row-constr = ANY (table-exp)
„
x NOT IN erg
⇔ NOT (x IN erg)
„
Beispiel:
Attributspezifikation: Es kann für jedes Attribut festgelegt werden,
ob NULL-Werte zugelassen sind oder nicht
„
Verschiedene Bedeutungen von Nullwerten:
„
Auswertung von boolschen Ausdrücken anhand 3-wertiger Logik
„
„
Datenwert ist momentan nicht bekannt
Attributwert existiert nicht für ein Tupel
Finde die Namen der Schauspieler, die den Faust gespielt haben.
SELECT S.NAME
SCHAUSPIELER S
FROM
WHERE ’Faust’ IN
(SELECT D.FIGUR
DARSTELLER D
FROM
WHERE D.PNR = S.PNR)
SELECT S.NAME
FROM SCHAUSPIELER S
WHERE S.PNR IN
(SELECT D.PNR
DARSTELLER D
FROM
WHERE D.FIGUR = ’Faust’)
NOT
T
F
?
SELECT S.NAME
SCHAUSPIELER S,
FROM
DARSTELLER D
WHERE S.PNR = D.PNR AND
D.FIGUR = ‘Faust”
N. Ritter, DIS, SS 2005, Kapitel 5
„
„
37
„
„
„
„
Syntaktische Ähnlichkeitssuche (siehe LIKE-Prädikat)
„
Phonetische Ähnlichkeit (spezielle DBS)
„
Semantische Ähnlichkeit (benutzerdefinierte Funktionen)
„
„
„
„
Das LIKE-Prädikat ist TRUE, wenn der entsprechende Datenwert der Maske
mit zulässigen Substitutionen von Zeichen für % und _ entspricht
38
„
NAME NOT LIKE ’%-%’ wird erfüllt von allen Namen ohne Bindestrich
„
Suche nach ‘%’ und ‘_’ durch Voranstellen eines Escape-Zeichens möglich:
wird erfüllt von STRING-Werten mit Unterstrich
erlaubt die Nutzung von regulären Ausdrücken zum Maskenaufbau
„
Beispiel: NAME SIMILAR TO ‘(SQL-(86 | 89 | 92 | 99)) | (SQL(1 | 2 | 3))’
N. Ritter, DIS, SS 2005, Kapitel 5
Verbund
„
Achtung
„
39
SELECT PNR, GEH + PROV
FROM PERS:
0815: ?,
4711: 80K,
...
„
„
SIMILAR-Prädikat in SQL:1999
„
row-constr IS [NOT] NULL
SELECT PNR, PNAME
FROM
PERS
WHERE GEHALT IS NULL;
41
PERS
STRING LIKE ’%\_%’ ESCAPE ’\’
„
Beispiel:
Eine arithmetische Operation (+, -, *, /) mit einem NULL-Wert
führt auf einen NULL-Wert
„
„
PROV
50K
100K
Weiteres zu Nullwerten
NAME LIKE ’%SCHMI%’ wird z. B. erfüllt von
‘H.-W. SCHMITT’, ‘SCHMITT, H.-W.’, ‘BAUSCHMIED’, ‘SCHMITZ’
ANR LIKE ’_7%’ wird erfüllt von Abteilungen mit einer 7 als zweitem Zeichen
GEH
80K
30K
20K
80K
N. Ritter, DIS, SS 2005, Kapitel 5
„
LIKE-Prädikat (Forts.) – Beispiele
„
ANR
K45
K45
K45
K56
K56
Anfragen (33)
Ähnlichkeitssuche (Forts.)
„
PNR
0815
4711
1111
1234
7777
GEH > PROV:
0815: ?, 1111: ?, 1234: ?
GEH > 70K AND PROV > 50K: 0815: ?, 1111: F, 1234: ?
GEH > 70K OR PROV > 50K: 0815: T, 1111: ?, 1234: ?
Test auf Nullwert
Unscharfe Suche: LIKE-Prädikat vergleicht einen Datenwert mit einem
„Muster“ bzw. einer „Maske“
Anfragen (30)
„
40
Beispiele
„
[ NOT ] LIKE char-string-exp
[ ESCAPE char-string-exp ]
N. Ritter, DIS, SS 2005, Kapitel 5
„
nach vollständiger Auswertung einer WHERE-Klausel wird das Ergebnis ?
wie FALSE behandelt
PERS
„
„
T T T
T F ?
T ? ?
Elementares Prädikat wird zu UNKNOWN (?) ausgewertet, falls Nullwert
vorliegt
Klassen
char-string-exp
T F ?
T
F
?
Prädikate über Nullwerten (Forts.)
„
Unterstützung der Suche nach Objekten, von denen nur Teile des Inhalts
bekannt sind oder die einem vorgegebenen Suchkriterium möglichst nahe
kommen.
LIKE-Prädikat
OR
T F ?
F F F
? F ?
Anfragen (32)
Ähnlichkeitssuche
„
T
F
?
N. Ritter, DIS, SS 2005, Kapitel 5
Anfragen (29)
„
AND T F ?
F
T
?
PNR
0815
4711
1111
1234
7777
ANR
K45
K45
K45
K56
K56
GEH
80K
30K
20K
80K
PROV
50K
100K
Tupel mit NULL-Werten im Verbundattribut nehmen nicht am Verbund teil
Im allgemeinen ist AVG (GEH) <> SUM (GEH) / COUNT (PNR)
N. Ritter, DIS, SS 2005, Kapitel 5
42
7
Anfragen (34)
„
Anfragen (37)
Quantifizierung
„
„
„
„
„
ALL-or-ANY-Prädikate
„
Quantifizierung (Forts.)
„
Θ ALL: Prädikat wird zu „true“ ausgewertet, wenn der Θ-Vergleich für alle
Ergebniswerte von table-exp „true“ ist
Beispiele (Forts.)
„
Finde die Namen aller Schauspieler, die alle Rollen gespielt haben.
SELECT S.NAME
FROM
SCHAUSPIELER S
WHERE NOT EXISTS
(SELECT *
FROM
ROLLE R
WHERE NOT EXISTS
(SELECT *
FROM
DARSTELLER D
WHERE D.PNR = S.PNR
AND D.FIGUR = R.FIGUR))
Θ ANY / Θ SOME: analog, wenn der Θ-Vergleich für einen Ergebniswert
„true“ ist
Existenztests
„
row-constr Θ { ALL | ANY | SOME} (table-exp)
[NOT] EXISTS (table-exp)
Das Prädikat wird zu „false“ ausgewertet, wenn table-exp auf die leere
Menge führt, sonst zu „true“
Im EXISTS-Kontext darf table-exp mit (SELECT * ...) spezifiziert werden
(Normalfall)
Andere Formulierung: Finde die Namen der Schauspieler, so dass keine Rolle
„existiert“, die sie nicht gespielt haben.
N. Ritter, DIS, SS 2005, Kapitel 5
43
N. Ritter, DIS, SS 2005, Kapitel 5
Anfragen (35)
„
Anfragen (38)
„
Quantifizierung (Forts.)
„
„
„
„
x Θ ANY (SELECT y FROM T WHERE p) ⇔
EXISTS (SELECT * FROM T WHERE (p) AND x Θ T.y)
„
Schema: station (snr, name, ...); wettert (datum, snr, mintemp, ...)
„
Anfrage: Finde die Messstation mit der niedrigsten gemessenen Temperatur
Gute Lösung: (Aggregat-Funktion in Subquery)
SELECT s.name FROM station s, wettert w
WHERE s.snr=w.snr AND w.mintemp=
(SELECT MIN(ww.mintemp) FROM wettert ww);
x Θ ALL (SELECT y FROM T WHERE p) ⇔
NOT EXISTS (SELECT * FROM T WHERE (p) AND NOT (x Θ T.y))
Schlechte Lösung: Keine Joins
SELECT name FROM station WHERE snr=(
SELECT DISTINCT snr FROM wettert WHERE mintemp=(
SELECT MIN(mintemp) FROM wettert));
Finde die Manager, die mehr verdienen als alle ihre direkten
Untergebenen
SELECT
FROM
WHERE
M.PNR
PERS M
M.GEHALT > ALL
N. Ritter, DIS, SS 2005, Kapitel 5
(SELECT P.GEHALT
FROM
PERS P
WHERE P.MNR = M.PNR)
Naja, worst case?!: Keine Aggregat-Funktion
SELECT DISTINCT name FROM station
WHERE snr IN ( SELECT W1.snr FROM wettert W1
WHERE NOT EXISTS (SELECT * FROM wettert W2
WHERE W2.mintemp < W1.mintemp));
44
N. Ritter, DIS, SS 2005, Kapitel 5
Anfragen (36)
„
Einfügen von Tupeln
Beispiele (Forts.)
„
Finde die Namen der Schauspieler, die mindestens einmal gespielt
haben (... nie gespielt haben)
SELECT
FROM
WHERE
„
Beispiel:
INSERT INTO table [ (column-commalist) ]
{ VALUES row-constr.-commalist |
table-exp |
DEFAULT VALUES }
Füge den Schauspieler Garfield mit der PNR 4711 ein.
SP.NAME
SCHAUSPIELER SP
(NOT) EXISTS
(SELECT *
FROM
DARSTELLER DA
WHERE DA.PNR = SP.PNR)
INSERT INTO SP
(PNR, NAME, W-ORT)
VALUES (4711, „Garfield“, DEFAULT);
„
Anmerkungen (zu satzweises Einfügen)
„
„
„
N. Ritter, DIS, SS 2005, Kapitel 5
47
Datenmanipulation (1)
Quantifizierung (Forts.)
„
Beispiel
Beispiele
„
„
Es gibt meist viele Möglichkeiten
Semantik
„
46
45
Alle nicht angesprochenen Attribute erhalten Nullwerte.
Falls alle Werte in der richtigen Reihenfolge versorgt werden, kann
die Attributliste weggelassen werden.
Mengenorientiertes Einfügen ist möglich, wenn die einzufügenden Tupel
aus einer anderen Relation mit Hilfe einer SELECT-Anweisung ausgewählt
werden können.
N. Ritter, DIS, SS 2005, Kapitel 5
48
8
Datenmanipulation (2)
„
Datendefinition (1)
Einfügen von Tupeln (Forts.)
„
„
„
möglichst große Unabhängigkeit der DB-Anwendungen von speziellen DBS
Füge die Schauspieler aus KL in die Relation TEMP ein.
„
einheitliche Sprachschnittstelle genügt nicht!
INSERT INTO TEMP
„
Anmerkungen (zu mengenorientiertes Einfügen)
„
„
„
Im Beispiel sei eine (leere) Relation TEMP sei vorhanden. Die Datentypen
ihrer Attribute müssen kompatibel zu den Datentypen der ausgewählten
Attribute sein.
49
Informationsschema
„
Definitionsschema
N. Ritter, DIS, SS 2005, Kapitel 5
searched-delete
::=
„
Definitionsschema
REFERENTIAL_
CONSTRAINTS
DELETE FROM table [WHERE cond-exp]
Aufbau der WHERE-Klausel entsprecht dem der SELECT-Anweisung
Beispiele
Refs PK /
unique
Lösche den Schauspieler mit der PNR 4711.
DELETE
FROM
SCHAUSPIELER
WHERE PNR = 4711;
„
50
OR
DOMAIN_
CONSTRAINTS
DOMAINS
KEY_COLUMN_
USAGE
CHECK_TABLE_
USAGE
CHECK_COLUMN_
USAGE
DATA_TYPE_
DESCRIPTOR
>0
COLUMNS
OR
COLLATIONS
N. Ritter, DIS, SS 2005, Kapitel 5
53
Datendefinition (3)
„
Definitionsschema (Forts.)
UPDATE table SET update-assignment-commalist
[WHERE cond-exp]
>0
TABLES
COLUMNS
COLLATIONS
CHARACTER_
USAGE
Beispiel
Gib den Schauspielern, die am Pfalztheater spielen, eine Gehaltserhöhung von
5% (Annahme: GEHALT in Schauspieler).
Default
collation
VIEW_TABLE_
USAGE
VIEW
VIEW_COLUMN_
USAGE
TABLE_
PRIVILEGES
COLUMN_
PRIVILEGES
USAGE_
PRIVILEGES
UPDATE SCHAUSPIELER S
SET
S.GEHALT = S.GEHALT * 1.05
WHERE EXISTS (SELECT *
FROM DARSTELLER D
WHERE D.PNR = S.PNR AND D.THEATER = ‘Pfalz’);
target
grantor
„
SCHEMATA
Default
char set
TABLES
Ändern von Tupeln mit Hilfe von Suchklauseln
searched-update
::=
owner
Char set
Datenmanipulation (4)
„
ASSERTIONS
check
CHECK_
CONSTRAINTS
FROM
SCHAUSPIELER S
WHERE NOT EXISTS
(SELECT *
FROM DARSTELLER D
WHERE D.PNR = S.PNR);
N. Ritter, DIS, SS 2005, Kapitel 5
FK
Is FK
PK /
unique
XOR
TABLE_
CONSTRAINTS
Lösche alle Schauspieler, die nie gespielt haben.
DELETE
52
Datendefinition (2)
Löschen von Tupeln mit Hilfe von Suchklauseln
„
„
Die kopierten Tupel sind unabhängig von ihren Ursprungstupeln.
Datenmanipulation (3)
„
Zweischichtiges Definitionsmodell für die Beschreibung der Daten
Ein mengenorientiertes Einfügen wählt die spezifizierte Tupelmenge aus
und kopiert sie in die Zielrelation.
N. Ritter, DIS, SS 2005, Kapitel 5
„
Beschreibung der gespeicherten Daten und ihrer Eigenschaften nach
einheitlichen und verbindlichen Richtlinien ist genauso wichtig
„
(SELECT *
FROM
SP
WHERE W-ORT=„KL“);
„
Ziel der SQL-Normierung
Beispiel:
grantee
grantee
Einschränkung:
Innerhalb der WHERE-Klausel in einer Lösch- oder Änderungsanweisung
darf die Zielrelation in einer FROM-Klausel nicht referenziert werden.
N. Ritter, DIS, SS 2005, Kapitel 5
grantor
USERS
51
N. Ritter, DIS, SS 2005, Kapitel 5
OR
source
TRANSLATIONS
grantor
grantee
SQL_
LANGUAGES
54
9
Datendefinition (4)
„
Datendefinition (7)
Definition von Schemata
„
„
Jedes Schema ist einem Benutzer (user) zugeordnet, z.B. DBA
„
Schema erhält Benutzernamen, falls keine explizite Namensangabe erfolgt
„
„
Definition von Attributen
column-def
: : = column { data-type | domain }
[ DEFAULT { literal | niladic-function-ref | NULL} ]
[ column-constraint-def-list ]
CREATE SCHEMA [schema] [AUTHORIZATION user]
[DEFAULT CHARACTER SET char-set]
[schema-element-list]
„
Definition aller Definitionsbereiche, Basisrelationen, Sichten (Views),
Integritätsbedingungen und Zugriffsrechte
Beispiel
„
Spezifikation von
„
Attributname
„
Datentyp bzw. Domain
„
Defaultwert sowie Constraints
Beispiele:
PNAME CHAR (30)
CREATE SCHEMA Beispiel-DB AUTHORIZATION DB-Admin
PALTER ALTER (siehe Definition von Domain ALTER)
N. Ritter, DIS, SS 2005, Kapitel 5
55
N. Ritter, DIS, SS 2005, Kapitel 5
Datendefinition (5)
„
Datendefinition (8)
Datentypen
„
„
CHARACTER [ ( length ) ]
(Abkürzung: CHAR)
„
CHARACTER VARYING [ ( length ) ]
(Abkürzung: VARCHAR)
„
„
„
„
NUMERIC [ ( precision [ , scale] ) ]
„
INTEGER
„
REAL
„
...
„
DATE
„
TIME
„
...
(Abkürzung: DEC)
„
(Abkürzung: INT)
56
Eindeutigkeit (UNIQUE bzw. PRIMARY KEY)
„
FOREIGN-KEY-Klausel
„
CHECK-Bedingungen
Vorteile der Vergabe von Constraint-Namen
„
Diagnosehilfe bei Fehlern
„
gezieltes Ansprechen bei SET oder DROP des Constraints
N. Ritter, DIS, SS 2005, Kapitel 5
„
Definition von Attributen (Forts.)
„
[ [CONSTRAINT constraint] CHECK (cond-exp) [deferrability]]
Spezifikationsmöglichkeiten
Optionale Angabe von Default-Werten
„
Wertebereichseingrenzung durch benamte CHECK-Bedingung möglich
Beispiel:
Verkaufs_Preis DECIMAL (9, 2),
CONSTRAINT Ausverkauf
CHECK ( Verkaufs_Preis
<= (SELECT MIN (Preis) FROM Konkurrenz_Preise))
[DEFAULT { literal | niladic-function-ref | NULL} ]
„
59
Datendefinition (9)
CREATE DOMAIN domain [AS] data type
„
Verbot von Nullwerten (NOT NULL)
„
| references-def | CHECK (cond-exp) } [deferrability]
Definition von Domains
„
„
{ NOT NULL | { PRIMARY KEY | UNIQUE }
Datendefinition (6)
„
Als Constraints können definiert werden
column-constraint-def : : = [CONSTRAINT constraint]
N. Ritter, DIS, SS 2005, Kapitel 5
„
Definition von Attributen (Forts.)
...
DECIMAL [ ( precision [ , scale ] ) ]
58
„
Überprüfungszeitpunkt
„
CHECK-Bedingungen können Relationen der DB referenzieren;
SQL-Domänen sind also dynamisch!
„
Jeder Constraint bzgl. einer SQL2-Transaktion ist zu jedem Zeitpunkt
in einem von zwei Modi: „immediate“ oder „deferred“
Der Default-Modus ist „immediate“
Beispiele:
deferrability
CREATE DOMAIN ABTNR AS CHAR (6)
: : = INITIALLY { DEFERRED | IMMEDIATE }
CREATE DOMAIN ALTER AS INT DEFAULT NULL
CONSTRAINT ALTERSBEGRENZUNG
CHECK (VALUE=NULL OR (VALUE > 18 AND VALUE < 70))
N. Ritter, DIS, SS 2005, Kapitel 5
[ NOT ] DEFERRABLE
57
N. Ritter, DIS, SS 2005, Kapitel 5
60
10
Datendefinition (10)
„
Beziehungen (1)
Definition von Attributen (Forts.)
„
„
Aufbau der FOREIGN-KEY-Klausel
(1:n)-Beziehung
„
Beispiel (ERM):
[0,n]
ABT
references-def : : =
ist_
beschäftigt_
in
[0,1]
PERS
REFERENCES base-table [ (column-commalist)]
[ON DELETE
referential-action]
[ON UPDATE
referential-action]
„
Abbildung
ABT ( ABTNR ...,
referential-action
PERS (
ANR ...,
PRIMARY KEY (ABTNR))
PRIMARY KEY (PNR),
: : = NO ACTION | CASCADE | RESTRICT | SET DEFAULT | SET NULL
FOREIGN KEY (ANR) REFERENCES ABT)
„
„
Fremdschlüssel kann auch auf Schlüsselkandidat definiert sein
„
Referentielle Aktionen werden später behandelt
PNR ...,
...
Referenzgraph:
ABT
ANR
referenzierte
PERS
referenzierende
Relation
N. Ritter, DIS, SS 2005, Kapitel 5
61
N. Ritter, DIS, SS 2005, Kapitel 5
Datendefinition (11)
„
Beziehungen (2)
Erzeugung von Basisrelationen
„
CREATE TABLE base-table (base-table-element-commalist)
(1:n)-Beziehung (Forts.)
„
base-table-element
Mögliche zusätzliche Regeln:
„
„
Definition aller zugehörigen Attribute mit Typfestlegung
„
Spezifikation aller Integritätsbedingungen (Constraints)
„
PRIMARY KEY,
ANAME
CHAR (30)
NOT NULL,
ANZAHL-ANGEST
INT
NOT NULL,
N. Ritter, DIS, SS 2005, Kapitel 5
nicht einschränken (außer [0,1]).
62
„
Beispiel (Forts.):
TABLE PERS
INT
CHAR (30),
CHAR (30)
ALTER,
INT
ABTNR
CHAR (25)
DEC (9,2)
Vorschlag: PENDANT-Klausel, mit der der Fall [1,n] abgedeckt werden kann.
„
Bei der Erstellung müssen solche Beziehungen verzögert überprüft werden.
65
Beziehungen (3)
Erzeugung von Basisrelationen (Forts.)
CREATE
( PNR
BERUF
PNAME
PALTER
MGR
ANR
W-ORT
GEHALT
„
N. Ritter, DIS, SS 2005, Kapitel 5
Datendefinition (12)
„
In SQL2 kann (im Rahmen der Erzeugung von Relationen) nicht spezifiziert werden,
dass ein Vater einen Sohn haben muss, z. B. [1,n]; die Anzahl der Söhne lässt sich
. . .)
„
Bemerkung:
„
CREATE TABLE ABT
ABTNR
Jede Abteilung (ABT: [0,1]) darf höchstens einen Angestellten beschäftigen:
PERS.ANR ... UNIQUE
„
Beispiel: Definition der Relationen ABT und PERS
(ANR
Jeder Angestellte (PERS) muss in einer Abteilung beschäftigt sein ([1,1]):
PERS.ANR ... NOT NULL
::= column-def | base-table-constraint-def
„
64
(1:n)-Beziehung (Forts.)
„
Beispiel (ERM):
[0,n]
PRIMARY KEY,
hat_
Büro_
von
[1,1]
ABT
NOT NULL,
(* siehe Domaindefinition *)
REFERENCES PERS,
NOT NULL, (* Domaindef. *)
DEFAULT ’ ’,
DEFAULT 0,00
CHECK (GEHALT < 120.000,00)
PERS
[0,n]
„
[0,1]
Abbildung
ABT (ABTNR ...,
...
FOREIGN KEY (ANR) REFERENCES ABT )
arbeitet_
für
PRIMARY KEY (ABTNR))
PERS (PNR ...,
ANRA ...,
ANRB... NOT NULL,
PRIMARY KEY (PNR),
FOREIGN KEY (ANRA) REFERENCES ABT,
N. Ritter, DIS, SS 2005, Kapitel 5
63
N. Ritter, DIS, SS 2005, Kapitel 5
FOREIGN KEY (ANRB) REFERENCES ABT)
66
11
Beziehungen (4)
„
Beziehungen (7)
(1:n)-Beziehung (Forts.)
„
„
Referenzgraph (zu obigem Beispiel)
Symmetrische (1:1)-Beziehung
„
Beispiel (ERM)
„
Abbildung
ANRB
ABT
ABT
PERS
ANRA
„
ABT (ANR ...,
MNR ... UNIQUE NOT NULL,
...
PRIMARY KEY (ANR),
FOREIGN KEY (MNR)
REFERENCES MGR)
Bemerkung:
„
Für jede FS-Beziehung benötigt man einen separaten FS.
„
Mehrere FS können auf denselben PS/SK verweisen.
„
„
N. Ritter, DIS, SS 2005, Kapitel 5
67
„
hat_
Mgr
[0,1]
leitet_
Abt
[0,1]
Variation über Schlüsselkandidaten
MGR (SVNR ...,
MNR ... UNIQUE,
...
...
PRIMARY KEY (ANR),
PRIMARY KEY (SVNR)
FOREIGN KEY (MNR)
FOREIGN KEY (MNR)
„
„
REFERENCES ABT(MNR))
Es sind alternative Lösungen möglich
Die Nutzung von Schlüsselkandidaten mit der Option NOT NULL
erlaubt die Darstellung des Falles ([1,1] , [1,1])
Alle Kombinationen mit [0,1] und [1,1] sind möglich
Alternative Lösungen möglich!
N. Ritter, DIS, SS 2005, Kapitel 5
68
N. Ritter, DIS, SS 2005, Kapitel 5
Beziehungen (6)
71
Beziehungen (9)
(1:1)-Beziehung (Forts.)
„
70
REFERENCES MGR(MNR)
MGR (MNR ...,
ANR ... UNIQUE,
...
PRIMARY KEY (MNR),
FOREIGN KEY (ANR)
REFERENCES ABT)
„
„
MGR
MNR
MNR ... UNIQUE,
Abbildung
ABT (ANR ...,
MNR ... UNIQUE,
...
PRIMARY KEY (ANR),
FOREIGN KEY (MNR)
REFERENCES MGR)
„
ABT
Fall ([0,1] , [0,1]) so nicht darstellbar
ABT (ANR ...,
MGR
[0,1]
„
MNR
Nutzung des MNR-Attributes für
beide FS-Beziehungen gewährleistet
Einhaltung der (1:1)-Beziehung
Symmetrische (1:1)-Beziehung (Forts.)
„
[0,1]
ABT
„
MGR (MNR ...,
...
PRIMARY KEY (MNR),
FOREIGN KEY (MNR)
REFERENCES ABT(MNR))
Beziehungen (8)
(1:1)-Beziehung
Beispiel (ERM):
MGR
N. Ritter, DIS, SS 2005, Kapitel 5
Beziehungen (5)
„
[1,1]
leitet
Referenzgraph
„
„
[1,1]
„
Mögliche zusätzliche Regeln zu obigem Beispiel:
(Symmetrische) (1:1)-Beziehung (Forts.)
„
„
Jede Abteilung hat einen Manager → ABT.MNR ... UNIQUE NOT NULL
„
Jeder Manager leitet eine Abteilung → MGR.ANR ... UNIQUE NOT NULL
Diskussion der verschiedenen Ansätze (siehe Folien oben) am Beispiel
Referenzgraph
MNR
ABT
MGR
ABT
MGR
a1
1
a2
2
a3
3
a4
4
ANR
„
Frage:
„
Kann durch die beiden (n:1)-Beziehungen eine symmetrische (1:1)-Beziehung
ausgedrückt werden?
N. Ritter, DIS, SS 2005, Kapitel 5
69
1. Ansatz:
ABT (ANR, MNR, ...)
PERS (MNR, ANR, ...)
a1
1
1
a2
a2
2
2
a3
a3
3
3
a1
a4
-
4
-
N. Ritter, DIS, SS 2005, Kapitel 5
72
12
Beziehungen (10)
„
Beziehungen (13)
(Symmetrische) (1:1)-Beziehung (Forts.)
„
„
Diskussion der verschiedenen Ansätze (siehe Folien oben) am Beispiel
„
ABT
MGR
a1
1
a2
2
a3
3
a4
4
„
„
2. Ansatz:
„
ABT (ANR, MNR, ...)
a1
1
1
a2
2
2
a3
3
3
?
?
N. Ritter, DIS, SS 2005, Kapitel 5
„
ABT
MGR
a1
1
a2
2
a3
3
a4
4
Reflexive (1:n)-Beziehung
„
„
hat_
Mgr
Abbildung
MNR ...,
...
PRIMARY KEY (PNR),
PERS (SVNR, MNR, ...)
1
x
1
a2
2
y
2
a3
3
z
3
a4
-
w
-
FOREIGN KEY (MNR) REFERENCES PERS (PNR))
„
74
„
bearbeitet
[0,m]
Reflexive (1:n)-Beziehung (Forts.)
PROJ
„
PERS (PNR ...,
...
PRIMARY KEY (PNR))
77
Beziehungen (15)
(n:m)-Beziehung
[0,n]
MNR
N. Ritter, DIS, SS 2005, Kapitel 5
Beziehungen (12)
PERS
Referenzgraph
PERS
N. Ritter, DIS, SS 2005, Kapitel 5
Abbildung
[0,1]
PERS (PNR ...,
a1
„
[0,n]
Beispiel (ERM)
PERS
3. Ansatz:
Beispiel (ERM)
76
Beziehungen (14)
Diskussion der verschiedenen Ansätze (siehe Folien oben) am Beispiel
„
PROJ
JNR
MITARBEIT
73
ABT (ANR, MNR, ...)
„
Zugehöriger Referenzgraph
PNR
(Symmetrische) (1:1)-Beziehung (Forts.)
„
Ist die Realisierung von [1,n] oder [1,m] bei der Abbildung der (n:m)Beziehung möglich?
PERS
Beziehungen (11)
„
Diese Standardlösung (siehe vorangegangene Folie) erzwingt eine
„Existenzabhängigkeit“ von MITARBEIT; soll dies vermieden werden, dürfen
die Fremdschlüssel von MITARBEIT nicht als Teil des Primärschlüssels
spezifiziert werden.
PERS (MNR, ...)
N. Ritter, DIS, SS 2005, Kapitel 5
„
(n:m)-Beziehung (Forts.)
PROJ (JNR ...,
...
PRIMARY KEY (JNR))
„
MITARBEIT (PNR ...,
JNR ...,
„
PRIMARY KEY (PNR, JNR),
Mit Hilfe dieser Lösung (siehe vorangegangene Folie) kann die PersonalHierarchie eines Unternehmens dargestellt werden;
die referentielle Beziehung stellt hier eine partielle Funktion dar, da die
„obersten“ Manager einer Hierarchie keinen Manager haben
MNR ... NOT NULL lässt sich nur realisieren, wenn die „obersten“ Manager als
ihre eigenen Manager interpretiert werden;
dadurch treten jedoch Referenzzyklen auf, was die Frageauswertung und die
Konsistenzprüfung erschwert
Welche Beziehungsstruktur erzeugt MNR ... UNIQUE NOT NULL?
FOREIGN KEY (PNR) REFERENCES PERS,
FOREIGN KEY (JNR) REFERENCES PROJ)
N. Ritter, DIS, SS 2005, Kapitel 5
75
N. Ritter, DIS, SS 2005, Kapitel 5
78
13
Beziehungen (16)
„
Datendefinition (14)
Abbildung von Beziehungen - Zusammenfassung
„
„
„
Relationenmodell ‚hat‘ wertbasierte Beziehungen
„
„
Alle Beziehungen (FS ↔ PS/SK) sind binär und symmetrisch
„
Auflösung einer Beziehung geschieht durch Suche
„
Es sind i. allg. k (1:n)-Beziehungen zwischen zwei Relationen möglich
„
Objektorientierte Datenmodelle haben referenzbasierte Beziehungen!
„
Spezifikationsmöglichkeiten in SQL
SK
FS
FBNR
PNR PNAME FBNR FACHGEBIET
PRUEFUNG
79
Abbildung von Beziehungen – Zusammenfassung (Forts.)
„
82
Beispiel (Forts.)
„
Spezifikationsmöglichkeiten in SQL (Forts.)
Fremdschlüsseldeklaration in S:
S
0,n
0,1
FS ...
0,n
1,1
FS ...
NOT NULL
0,1
0,1
FS ...
UNIQUE
0,1
1,1
FS ...
UNIQUE NOT NULL
80
Datendefinition - Wertebereiche:
CREATE DOMAIN
FACHBEREICHSNUMMER
AS
CHAR
CREATE DOMAIN
FACHBEREICHSNAME
AS
VARCHAR
(20)
CREATE DOMAIN
FACHBEZEICHNUNG
AS
VARCHAR
(20)
CREATE DOMAIN
NAMEN
AS
VARCHAR
(30)
CREATE DOMAIN
PERSONALNUMMER
AS
CHAR
CREATE DOMAIN
MATRIKELNUMMER
AS
INT
CREATE DOMAIN
NOTEN
AS
SMALLINT
CREATE DOMAIN
DATUM
AS
DATE
N. Ritter, DIS, SS 2005, Kapitel 5
Datendefinition (13)
(4)
(4)
83
Datendefinition (16)
Beispiel
„
PNR MATNR FACH PDATUM NOTE
N. Ritter, DIS, SS 2005, Kapitel 5
Datendefinition (15)
N. Ritter, DIS, SS 2005, Kapitel 5
„
DEKAN
PROF
PRIMARY KEY
(implizit: UNIQUE NOT NULL)
UNIQUE [NOT NULL]
[UNIQUE] [NOT NULL]
V
FBNAME
MATNR SNAME FBNR STUDBEG
Beziehungen (17)
„
FB
STUDENT
N. Ritter, DIS, SS 2005, Kapitel 5
„
Illustration des DB-Schemas
Fremdschlüssel (FS) und zugehöriger Primärschlüssel/Schlüsselkandidat
(PS/SK) repräsentieren eine Beziehung (gleiche Wertebereiche!)
PS
„
Beispiel (Forts.)
„
Miniwelt (ER-Diagramm)
Beispiel (Forts.)
„
Datendefinition - Relationen:
CREATE TABLE FB (
Fachbereich
0,N
0,N
1,1
ist-Dekanvon
0,1
gehört-zu
isteingeschr.in
FBNR
FACHBEREICHSNUMMER
PRIMARY KEY,
FBNAME
FACHBEREICHSNAME
UNIQUE,
DEKAN
PERSONALNUMMER
UNIQUE NOT NULL,
CONSTRAINT FFK FOREIGN KEY (DEKAN)
REFERENCES PROF (PNR)
1,1
1,1
ON UPDATE CASCADE
ON DELETE RESTRICT)
Prof
N. Ritter, DIS, SS 2005, Kapitel 5
0,N
Prüfung
0,M
Student
81
N. Ritter, DIS, SS 2005, Kapitel 5
84
14
Datendefinition (17)
„
Datendefinition (20)
Beispiel (Forts.)
„
Beispiel (Forts.)
„
Datendefinition – Relationen (Forts.):
„
Ausprägungen
CREATE TABLE PROF (
FB
PNR
PERSONALNUMMER
PRIMARY KEY,
PNAME
NAMEN
NOT NULL,
FBNR
FACHBEREICHSNUMMER
NOT NULL,
FACHGEBIET
FACHBEZEICHNUNG,
PROF PNR
1234
5678
4711
6780
2223
PNAME
FBNR
FACHGEBIET
HÄRDER
WEDEKIND
MÜLLER
NEHMER
RICHTER
FB 5
FB 9
FB 9
FB 5
FB 5
DATENBANKSYSTEME
INFORMATIONSSYSTEME
OPERATIONS RESEARCH
BETRIEBSSYSTEME
EXPERTENSYSTEME
CONSTRAINT PFK1 FOREIGN KEY (FBNR)
REFERENCES FB (FBNR)
„
STUDENT
Es wird darauf verzichtet, die Rückwärtsrichtung der „ist-Dekan-von“-Beziehung
explizit als Fremdschlüsselbeziehung zu spezifizieren. Damit fällt auch die mögliche
Spezifikation von referentiellen Aktionen weg.
N. Ritter, DIS, SS 2005, Kapitel 5
85
4711
2223
MATNR
FACH
PDATUM NOTE
5678
4711
1234
1234
6780
1234
6780
123 766
123 766
654 711
123 766
654 711
196 481
196 481
BWL
OR
DV
DV
SP
DV
BS
22.10.97
16. 1.98
17. 4.97
17. 4.97
19. 9.97
15.10.97
23.12.97
MATNR
SNAME
FBNR
STUDBEG
123 766
225 332
654 711
226 302
196 481
130 680
COY
MÜLLER
ABEL
SCHULZE
MAIER
SCHMID
FB 9
FB 5
FB 5
FB 9
FB 5
FB 9
1.10.95
15. 4.87
15.10.94
1.10.95
23.10.95
1. 4.97
„
„
Primärschlüsselbedingung: Eindeutigkeit, keine Nullwerte!
CREATE TABLE STUDENT (
„
Fremdschlüsselbedingung: Zugehöriger PS (SK) muss existieren
MATRIKELNUMMER
88
Relationale Invarianten / referentielle Integrität:
Datendefinition – Relationen (Forts.):
MATNR
4
3
2
4
2
1
3
Wartung von Beziehungen (1)
Beispiel (Forts.)
„
DEKAN
WIRTSCHAFTSWISS
INFORMATIK
N. Ritter, DIS, SS 2005, Kapitel 5
Datendefinition (18)
„
FBNAME
PRÜFUNG PNR
ON UPDATE CASCADE
ON DELETE SET DEFAULT)
FBNR
FB 9
FB 5
PRIMARY KEY,
SNAME
NAMEN
NOT NULL,
FBNR
FACHBEREICHSNUMMER
NOT NULL,
„
Potentielle Gefährdung
„
STUDBEG DATUM,
Operationen in der Sohn-Relation
„
Einfügen eines Sohn-Tupels
„
Ändern des FS in einem Sohn-Tupel
„
Löschen eines Sohn-Tupels
„
Welche Maßnahmen sind erforderlich?
CONSTRAINT SFK FOREIGN KEY (FBNR)
REFERENCES FB (FBNR)
ON UPDATE CASCADE
ON DELETE RESTRICT)
„
Beim Einfügen erfolgt eine Prüfung, ob in einem Vater-Tupel ein
PS/SK-Wert gleich dem FS-Wert des einzufügenden Tupels existiert
„
N. Ritter, DIS, SS 2005, Kapitel 5
86
N. Ritter, DIS, SS 2005, Kapitel 5
Datendefinition (19)
„
„
Datendefinition – Relationen (Forts.):
Potentielle Gefährdung (Forts.)
„
CREATE TABLE PRUEFUNG (
Operationen in der Vater-Relation
PNR
PERSONALNUMMER,
„
Löschen eines Vater-Tupels
MATNR
MATRIKELNUMMER,
„
Ändern des PS/SK in einem Vater-Tupel
FACH
FACHBEZEICHNUNG,
„
Einfügen eines Vater-Tupels
PDATUM
DATUM
NOT NULL,
„
Welche Reaktion ist wann möglich/sinnvoll?
NOTE
NOTEN
NOT NULL,
PRIMARY KEY (PNR, MATNR),
„
Verbiete Operation
„
Lösche/ändere rekursiv Tupel mit zugehörigen FS-Werten
„
CONSTRAINT PR1FK FOREIGN KEY (PNR)
REFERENCES PROF (PNR)
„
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT PR2FK FOREIGN KEY (MATNR)
REFERENCES STUDENT (MATNR)
ON UPDATE CASCADE
N. Ritter, DIS, SS 2005, Kapitel 5
ON DELETE CASCADE)
89
Wartung von Beziehungen (2)
Beispiel (Forts.)
„
Beim Ändern eines FS-Wertes erfolgt eine analoge Prüfung
87
Falls Sohn-Tupel erhalten bleiben soll (nicht immer möglich, z.B.
bei Existenzabhängigkeit), setze FS-Wert zu NULL oder Default
Wie geht man mit NULL-Werten um?
„
Spezielle Semantiken von NULL-Werten
„
Dreiwertige Logik verwirrend: T, F, ?
„
Setzung: NULL ≠ NULL (z. B. beim Verbund)
„
bei Operationen: Ignorieren von NULL-Werten
N. Ritter, DIS, SS 2005, Kapitel 5
90
15
Wartung von Beziehungen (3)
„
„
Wartung von Beziehungen (6)
SQL2-Standard führt „referential actions“ ein
„
Sind „Nullen“ verboten ?
„
Löschregel für Zielrelation (referenzierte Relation)
„
„
NOT NULL
„
ON DELETE
{CASCADE | RESTRICT | SET NULL | SET DEFAULT | NO ACTION}
„
ON UPDATE
{CASCADE | RESTRICT | SET NULL | SET DEFAULT | NO ACTION}
„
Die Option RESTRICT wird hier explizit aufgeführt; sie entspricht dem Fall,
dass die gesamte Klausel weggelassen wird.
N. Ritter, DIS, SS 2005, Kapitel 5
„
91
FBNR
PNR
PNAME
123766
COY
FB9
1234
HAERDER
654711
ABEL
FB5
4711
„
USN, DSN →
Schlüsselverletzung
USD, DSD →
ggf. Mehrdeutigkeit
UNA, DNA →
Wirkung identisch mit UR, DR
CASCADE: Operation „kaskadiert“ zu allen zugehörigen Sätzen
„
SET NULL: FS wird in zugehörigen Sätzen zu „Null“ gesetzt
„
SET DEFAULT: FS wird in den zugehörigen Sätzen auf einen benutzer-
OR
654711
DV
1234
123766
DV
4711
654711
OR
DC
DC
FBNR
FBNR
PNR
MATNR
STUDENT
DC
DC
PRUEFUNG
„
unterschiedlichen Optionen) betroffen sein; am Ende aller zugehörigen
referentiellen Aktionen wird die Einhaltung der referentiellen Integrität geprüft
„
N. Ritter, DIS, SS 2005, Kapitel 5
92
STUDENT
FBNR
Eindeutigkeit: Ergebnis der Operation ist reihenfolge-unabhängig
→ sicheres Schema!
95
Wartung von Beziehungen (8)
Diskussion der Auswirkungen referentieller Aktionen am Beispiel
1. Isolierte Betrachtung von STUDENT-FB
Lösche FB (mit FBNR „FB9“)
‘ erst links’:
‘erst rechts’:
- Löschen in FB
- Löschen in FB
- Löschen in PROF
- Löschen in STUDENT
- Löschen in PRUEFUNG
- Löschen in PRUEFUNG
- Löschen in STUDENT
- Löschen in PROF
- Löschen in PRUEFUNG
- Löschen in PRUEFUNG
N. Ritter, DIS, SS 2005, Kapitel 5
Wartung von Beziehungen (5)
„
Diskussion der Auswirkungen referentieller Aktionen am Beispiel (Forts.)
3. Vollständiges Beispiel –
Modifiziertes Schema
FB
FB
DC
DC
Beispiel-DB
MATRNR
SNAME
FBNR
FBNR
FBNAME
123766
COY
FB9
FB9
WIRTSCHAFTSWISS
225332
MÜLLER
FB5
FB5
INFORMATIK
654711
ABEL
FB5
226302
SCHULZE
FB9
„
Operationen
„
Referentielle Aktionen
FB
„
Lösche FB (mit FBNR „FB5“)
„
Ändere FB (FBNR=„FB9“ → FBNR=„FB10“)
„
DC, DSN, DSD, DR, DNA
„
UC, USN, USD, UR, UNA
N. Ritter, DIS, SS 2005, Kapitel 5
123766
1234
94
PROF
ausgeführt. Durch eine DB-Operation können jedoch mehrere Referenzen (mit
STUDENT
4711
FB
NO ACTION: Für die spezifizierte Referenz wird keine referentielle Aktion
„
MUELLER
MATRNR FACH
Diskussion der Auswirkungen referentieller Aktionen am Beispiel (Forts.)
definierten Default-Wert gesetzt
„
PRUEFUNG PNR
„
Unabhängigkeit von Beziehungen hinsichtlich referentieller Aktionen?
3. Vollständiges Beispiel
RESTRICT: Operation wird nur ausgeführt, wenn keine zugehörigen
Sätze (FS-Werte) vorhanden sind
„
„
PROF
Wartung von Beziehungen (7)
Genauere Spezifikation der referentiellen Aktionen (Forts.)
„
SNAME
N. Ritter, DIS, SS 2005, Kapitel 5
Wartung von Beziehungen (4)
„
PRUEFUNG
MATRNR
Einsatz von
„
„
MATNR
Beispiel-DB
STUDENT
Änderungsregel für Ziel-Primärschlüssel (PS oder SK)
„
STUDENT
PROF
PNR
„
„
Diskussion der Auswirkungen referentieller Aktionen am Beispiel (Forts.)
2. Isolierte Betrachtung von STUDENT-PRUEFUNG-PROF
Genauere Spezifikation der referentiellen Aktionen für jeden
Fremdschlüssel (FS)
STUDENT
PROF
DC
DR
PRUEFUNG
„
93
Lösche FB (mit FBNR „FB9“)
‘ erst links’:
- Löschen in FB
- Löschen in PROF
- Löschen in PRUEFUNG
- Löschen in STUDENT
- Zugriff auf PRUEFUNG
Wenn ein Student bei einem
FB-fremden Professor geprüft wurde
→ Rücksetzen
N. Ritter, DIS, SS 2005, Kapitel 5
‘erst rechts’:
- Löschen in FB
- Löschen in STUDENT
- Zugriff auf PRUEFUNG
Wenn ein gerade gelöschter Student
eine Prüfung abgelegt hatte
→ Rücksetzen
sonst:
- Löschen in PROF
- Löschen in PRUEFUNG
96
16
Wartung von Beziehungen (9)
„
Wartung von Beziehungen (12)
Diskussion der Auswirkungen referentieller Aktionen am Beispiel (Forts.)
3. Vollständiges Beispiel –
Modifiziertes Schema (Forts.)
„
DC
DC
STUDENT
PROF
DC
DR
„
PRUEFUNG
„
Es können reihenfolgenabhängige Ergebnisse auftreten!
„
Die Reihenfolgenabhängigkeit ist hier wertabhängig
97
„
DC
bei wertabhängigen Konflikten zu restriktiv (konfliktträchtige Schemata)
Dynamische Überwachung der Modifikationsoperationen
hoher Laufzeitaufwand
Maßnahmen zur Verhinderung von Mehrdeutigkeiten - Vorgehensweisen
DNA
PRUEFUNG
„
sie werden verboten, oder
„
sie werden erlaubt und
„
‘erst rechts’:
- Löschen FB
- Löschen STUDENT
- Löschen PROF
- Löschen PRUEFUNG
Test, ob es noch offene
Referenzen in PRUEFUNG
auf gelöschte Studenten gibt;
wenn ja → Rücksetzen
„
„
98
falls ein Konflikt erkannt wird, wird die Operation zurückgesetzt
„
Durchführung der referentiellen Aktionen (RA)
„
FB
Zyklische Referenzpfade
FS1
DC
DC
STUDENT
PROF
DC
ABT
FS2
„
Bei der NA-Option wird der explizite Test der referenzierenden Relation
ans Ende der Operation verschoben. Eine Verletzung der referentiellen
Beziehung führt zum Rücksetzen → Schema ist immer sicher
N. Ritter, DIS, SS 2005, Kapitel 5
MGR
DNA
PRUEFUNG
„
101
Wartung von Beziehungen (14)
Diskussion der Auswirkungen referentieller Aktionen am Beispiel (Forts.)
3. Vollständiges Beispiel –
Nochmalig modifiziertes
Schema (Forts.)
die referentiellen Aktionen werden bei jeder Operation dynamisch
überwacht
N. Ritter, DIS, SS 2005, Kapitel 5
Wartung von Beziehungen (11)
„
100
2. Alternative Möglichkeiten zur Behandlung konfliktträchtiger Schemata, nach
dem die statische Schemaanalyse die Sicherheit des Schemas nicht feststellen
konnte
STUDENT
PROF
Lösche FB (mit FBNR „FB9“)
‘ erst links’:
- Löschen FB
- Löschen PROF
- Löschen PRUEFUNG
- Löschen STUDENT
Test, ob es noch offene
Referenzen in PRUEFUNG
auf gelöschte Studenten gibt;
wenn ja → Rücksetzen
N. Ritter, DIS, SS 2005, Kapitel 5
hohe Komplexität der Analysealgorithmen
„
1. Falls Sicherheit eines Schemas festgestellt werden kann, ist keine Laufzeitüberwachung erforderlich
FB
DC
nur bei einfach strukturierten Schemata effektiv
„
Wartung von Beziehungen (13)
Diskussion der Auswirkungen referentieller Aktionen am Beispiel (Forts.)
DC
„
N. Ritter, DIS, SS 2005, Kapitel 5
Wartung von Beziehungen (10)
3. Vollständiges Beispiel –
Nochmalig modifiziertes
Schema
Statische Schemaanalyse zur Feststellung sicherer DB-Schemata
„
N. Ritter, DIS, SS 2005, Kapitel 5
„
Maßnahmen zur Verhinderung von Mehrdeutigkeiten
„
FB
„
99
wenigstens ein Fremdschlüssel im Zyklus muss „NULL“ erlauben
oder
Prüfung der referentiellen Integrität muss verzögert (DEFERRED)
werden (z. B. bei COMMIT)
N. Ritter, DIS, SS 2005, Kapitel 5
102
17
Wartung von Beziehungen (15)
„
Schemaevolution (3)
Durchführung der referentiellen Aktionen (Forts.)
„
„
Verarbeitungsmodell
Dynamische Änderung von Tabellen - Beispiele:
„
„
Benutzeroperationen (Op) sind in SQL immer atomar
„
mengenorientiertes oder tupelorientiertes Verarbeitungsmodell
ALTER TABLE Pers ADD Svnr INT UNIQUE
ALTER TABLE Abt ADD Geh-Summe INT
Op
Op
„
t1
t1
Erweiterung der Tabellen Abt und Pers um neue Spalten
t2
tn
RA
RA
Verkürzung der Tabelle Pers um eine Spalte
ALTER TABLE Pers DROP COLUMN Alter RESTRICT
...
t2 ... tn
RA
RA‘s
„
„
„
IMMEDIATE-Bedingungen müssen erfüllt sein an Anweisungsgrenzen
(→ mengenorientierte Änderung)
N. Ritter, DIS, SS 2005, Kapitel 5
„
103
„
„
Erzeugen/Löschen von Tabellen (und Sichten)
„
Hinzufügen, Ändern und Löschen von Spalten
„
Anlegen/Ändern von referentiellen Beziehungen
„
Hinzufügen, Modifikation, Wegfall von Integritätsbedingungen
„
„
Zusätzliche Änderungen im DB-Schema durch veränderte Anforderungen
bei der DB-Nutzung
Dynamisches Anlegen von Zugriffspfaden
„
Aktualisierung der Zugriffskontrollbedingungen
N. Ritter, DIS, SS 2005, Kapitel 5
„
„
104
{TABLE base-table ⎢ VIEW view |
DOMAIN domain | SCHEMA schema }
{RESTRICT | CASCADE}
Falls Objekte (Tabellen, Sichten, ...) nicht mehr benötigt werden, können sie
durch die DROP-Anweisung aus dem System entfernt werden.
Mit der CASCADE-Option können ’abhängige’ Objekte (z.B. Sichten auf
Tabellen oder anderen Sichten) mitentfernt werden
RESTRICT verhindert Löschen, wenn die zu löschende Tabelle noch durch
Sichten oder Integritätsbedingungen referenziert wird
N. Ritter, DIS, SS 2005, Kapitel 5
Schemaevolution (2)
„
106
Löschen von Schemaelementen
DROP
Hoher Grad an logischer Datenunabhängigkeit ist sehr wichtig!
„
CASCADE dagegen erzwingt die Folgelöschung aller Sichten und Check-Klauseln, die
von der Spalte abhängen.
Schemaevolution (4)
Wachsender oder sich ändernder Informationsbedarf
„
Da RESTRICT spezifiziert ist, wird die Operation zurückgewiesen, wenn die Spalte in
einer Sicht oder einer Integritätsbedingung (Check) referenziert wird.
N. Ritter, DIS, SS 2005, Kapitel 5
Schemaevolution (1)
„
Wenn die Spalte die einzige der Tabelle ist, wird die Operation zurückgewiesen.
107
Schemaevolution (5)
Dynamische Änderung von Tabellen
„
Löschen von Schemaelementen - Beipiele
„
ALTER TABLE base-table
{ ADD [COLUMN] column-def
| ALTER [COLUMN] column
{SET default-def | DROP DEFAULT}
| DROP [COLUMN] column {RESTRICT | CASCADE}
| ADD base-table-constraint-def
| DROP CONSTRAINT constraint {RESTRICT | CASCADE}}
Löschen von Tabelle Pers
DROP TABLE Pers RESTRICT
„
PersConstraint sei definiert auf Pers
ALTER TABLE Pers
DROP CONSTRAINT PersConstraint CASCADE
DROP TABLE Pers RESTRICT
„
Durchführung der Schemaevolution
„
„
N. Ritter, DIS, SS 2005, Kapitel 5
105
Aktualisierung von Tabellenzeilen des SQL-Definitionsschemas
“tabellengetriebene” Verarbeitung der Metadaten durch das DBS
N. Ritter, DIS, SS 2005, Kapitel 5
108
18
Indexierung (1)
„
„
Indexierung (4)
Einsatz von Indexstrukturen
„
„
Beschleunigung der Suche: Zugriff über Spalten (Schlüsselattribute)
„
Kontrolle von Integritätsbedingungen (relationale Invarianten)
„
Zeilenzugriff in der logischen Ordnung der Schlüsselwerte
„
Gewährleistung der Clustereigenschaft für Tabellen
„
Aber auch: erhöhter Aktualisierungsaufwand und Speicherplatzbedarf
IPers(Anr)
8
„
„
61
33
45
Wurzelseite
77
85
Zwischenseiten
Datenunabhängigkeit des Relationenmodells erlaubt ein Hinzufügen und Löschen
Blattseiten
jederzeit möglich, um z. B. bei veränderten Benutzerprofilen das Leistungsverhalten zu
optimieren
“beliebig” viele Indexstrukturen pro Tabelle und mit unterschiedlichen
Spaltenkombinationen als Schlüssel möglich
„
Steuerung der Eindeutigkeit der Schlüsselwerte, der Clusterbildung
„
Freiplatzanteil (PCTFREE) in jeder Indexseite beim Anlegen erleichtert das Wachstum
„
Spezifikation: DBA oder Benutzer
N. Ritter, DIS, SS 2005, Kapitel 5
Datenseiten
109
N. Ritter, DIS, SS 2005, Kapitel 5
Indexierung (2)
Im SQL-Standard keine Anweisung vorgesehen, jedoch in realen
Systemen (z. B. IBM DB2):
„
Beispiele
„
Erzeugung einer Indexstruktur mit Clusterbildung auf der Spalte Anr
von Abt
CREATE UNIQUE INDEX Persind1 ON Abt (Anr) CLUSTER
„
Nutzung eines vorhandenen Index
„
112
Indexierung (5)
CREATE [UNIQUE] INDEX index
ON base-table (column [ORDER] [,column[ORDER]] ...)
[CLUSTER] [PCTFREE]
„
13
25
Einrichtung von Indexstrukturen
„
„
Index ohne Clusterbildung
Entscheidung durch DBS-Optimizer
„
Realisierung z. B. durch B*-Baum
(oder Hashing, mit verminderter Funktionalität)
„
UNIQUE: keine Schlüsselduplikate im Index
„
CLUSTER: zeitoptimale sortiert-sequentielle Verarbeitung (Scan-Operation)
Erzeugung einer Indexstruktur auf den Spalten Anr
(absteigend) und Gehalt (aufsteigend) von Pers.
CREATE INDEX Persind2 ON Pers (Anr DESC, Gehalt ASC)
N. Ritter, DIS, SS 2005, Kapitel 5
110
N. Ritter, DIS, SS 2005, Kapitel 5
Indexierung (3)
„
Indexierung (6)
Index mit Clusterbildung
IAbt(Anr)
8
13
„
25
33
61
45
Wurzelseite
77
85
Typische Implementierung eines Index: B*-Baum
(wird von allen DBS angeboten!)
„
dynamische Reorganisation durch Aufteilen (Split) und Mischen von Seiten
„
Wesentliche Funktionen
„
Zwischenseiten
„
Blattseiten
„
Datenseiten
N. Ritter, DIS, SS 2005, Kapitel 5
113
111
direkter Schlüsselzugriff auf einen indexierten Satz
sortiert sequentieller Zugriff auf alle Sätze
(unterstützt Bereichsanfragen, Verbundoperation usw.)
Balancierte Struktur
„
unabhängig von Schlüsselmenge
„
unabhängig von Einfügereihenfolge
N. Ritter, DIS, SS 2005, Kapitel 5
114
19
Indexierung (6)
„
Sichten (3)
Typische Implementierung eines Index: B*-Baum (Forts.)
„
ES = SL/EL = max. # Einträge/Seite
„
hB = Baumhöhe
„
NT = #Zeilenverweise im B*-Baum
„
NB = #Blattseiten im B*-Baum
„
NTmin = 2 (ES/2)
„
Realistische Größenverhältnisse: hB = 3 und EL= 20 B
„
Sichten zur Gewährleistung von Datenunabhängigkeit
Benutzer
hb-1
hb
≤ NT ≤ ES
Sicht 1
= NTmax
Tabelle 1
Seitenformat
im B*-Baum
Sicht 2
Sicht 3
Tabelle 2
Tabelle 3
S L = 8 KB
Key | Ptr
.. .
EL ≈ 20 B
N. Ritter, DIS, SS 2005, Kapitel 5
115
N. Ritter, DIS, SS 2005, Kapitel 5
Sichten (1)
„
„
„
Sichten (4)
Ziel: Festlegung
„
Eigenschaften von Sichten
„
welche Daten Benutzer sehen wollen (Vereinfachung, leichtere Benutzung)
„
Sicht kann wie eine Tabelle behandelt werden
„
welche Daten sie nicht sehen dürfen (Datenschutz)
„
Sichtsemantik: „dynamisches Fenster“ auf zugrundeliegende Tabellen
„
einer zusätzlichen Abbildung (erhöhte Datenunabhängigkeit)
„
Sichten auf Sichten sind möglich
„
eingeschränkte Änderungen: aktualisierbare und nicht-aktualisierbare Sichten
Sicht (View)
„
„
118
mit Namen bezeichnete, aus Tabellen abgeleitete, virtuelle Tabelle (Anfrage)
Korrespondenz zum externen Schema bei ANSI/SPARC
(Benutzer sieht jedoch i. allg. mehrere Sichten (Views) und Tabellen)
Syntax
CREATE VIEW view [ (column-commalist ) ]
AS table-exp
[WITH [ CASCADED | LOCAL] CHECK OPTION]
N. Ritter, DIS, SS 2005, Kapitel 5
116
N. Ritter, DIS, SS 2005, Kapitel 5
Sichten (2)
„
Sichten (5)
Beispiele
„
„
Semantik von Sichten – ‚dynamisches Fenster‘
Sicht, die alle Programmierer mit einem Gehalt < 30.000 umfasst.
CREATE VIEW
Arme_Programmierer (Pnr, Name, Beruf, Gehalt, Anr)
AS
SELECT Pnr, Name, Beruf, Gehalt, Anr
FROM Pers
WHERE Beruf = ’Programmierer’ AND Gehalt < 30 000
„
119
Sicht V als dynamisches Fenster
Tabelle R ( A1,
A2,
A3,
A4,
A5 )
t1:
a11
a12
a13
a14
a15
t4:
a41
a42
a43
a44
a45
Sicht für den Datenschutz
CREATE VIEW Statistik (Beruf, Gehalt)
AS
SELECT Beruf, Gehalt
FROM Pers
N. Ritter, DIS, SS 2005, Kapitel 5
117
N. Ritter, DIS, SS 2005, Kapitel 5
120
20
Sichten (6)
„
Sichten (9)
Sichtbarkeit von Änderungen
„
„
Wann werden welche Datenänderungen in der Tabelle/Sicht für die anderen
Benutzer sichtbar? (Beachte Beispiel auf vorangegangener Folie)
Abbildung von Sicht-Operationen auf Tabellen
„
Abbildungsprozess auch über mehrere Stufen durchführbar
„
Vor BOT
von T1, T2
Insert t2
B1 sieht R = {t1, t4}
T1
B2 sieht V = {t1‘}
T2
Select t4
R=
Sichtendefinitionen
CREATE VIEW V AS SELECT ... FROM R WHERE P
Nach EOT
von T1, T2
CREATE VIEW W AS SELECT ... FROM V WHERE Q
?
„
Anfrage
SELECT ... FROM W WHERE C
Insert t3‘
Select t2‘
V=
?
„
Ersetzung durch
SELECT ... FROM V WHERE Q AND C
SELECT ... FROM R WHERE Q AND P AND C
N. Ritter, DIS, SS 2005, Kapitel 5
121
N. Ritter, DIS, SS 2005, Kapitel 5
Sichten (7)
„
Sichten (10)
Sichtbarkeit von Änderungen
„
„
Wann werden welche Datenänderungen in der Tabelle/Sicht für die anderen
Benutzer sichtbar? (Forts.)
„
„
A2,
A3,
A4,
A5 )
a12
a13
a14
a15
a24
a25
t1:
a11
t2:
a21
a22
a23
t3:
≡
a32
a33
a34
≡
t4:
a41
a42
a43
a44
a45
Einschränkungen der Abbildungsmächtigkeit
„
Sicht V als dynamisches Fenster
Tabelle R ( A1,
N. Ritter, DIS, SS 2005, Kapitel 5
„
„
Anfrage
SELECT AVG (Gsumme) FROM Abtinfo
Ersetzung durch (bei naiver Vorgehensweise)
SELECT ?
FROM Pers
GROUP BY Anr
122
N. Ritter, DIS, SS 2005, Kapitel 5
125
Sichten (11)
Abbildung von Sicht-Operationen auf Tabellen
„
Sichtendefinition
CREATE VIEW Abtinfo (Anr, Gsumme) AS
SELECT Anr, SUM (Gehalt)
FROM Pers
GROUP BY Anr
Sichten (8)
„
keine Schachtelung von Aggregat-Funktionen und Gruppenbildung (GROUP-BY)
keine Aggregat-Funktionen in WHERE-Klausel möglich
Beispiel
„
„
124
„
Sichten werden i. allg. nicht explizit und permanent gespeichert, sondern SichtOperationen werden in äquivalente Operationen auf Tabellen umgesetzt
Umsetzung ist für Leseoperationen meist unproblematisch
Löschen von Sichten:
„
Beispiel
DROP VIEW Arme_Programmierer CASCADE
„
Anfrage (Sichtreferenz):
SELECT Name, Gehalt
FROM
Arme_Programmierer
WHERE Anr = ‘K55’
„
Alle referenzierenden Sichtdefinitionen und Integritätsbedingungen
werden mitgelöscht
RESTRICT würde eine Löschung zurückweisen, wenn die Sicht in
weiteren Sichtdefinitionen oder CHECK-Constraints referenziert werden
würde.
Ersetzung durch:
SELECT Name, Gehalt
FROM
PERS
WHERE Anr = ‘K55’
AND Beruf = ’Programmierer’ AND Gehalt < 30 000
N. Ritter, DIS, SS 2005, Kapitel 5
123
N. Ritter, DIS, SS 2005, Kapitel 5
126
21
Sichten (12)
„
Sichten (15)
Änderbarkeit von Sichten
„
WITH CHECK OPTION (Forts.)
alle Sichten
Sichtdef.
Prädikat
SA
theoretisch änderbare Sichten
CHECKOption
VA
___
VN
___
VI
CASCADED
SN
in SQL änderbare Sichten
•••
SI
„
Änderbarkeit in SQL
S I-1
„
nur eine Tabelle (Basisrelation oder Sicht)
„
Schlüssel muss vorhanden sein
„
keine Aggregatfunktionen, Gruppierung und Duplikateliminierung
V I-1
R
N. Ritter, DIS, SS 2005, Kapitel 5
127
N. Ritter, DIS, SS 2005, Kapitel 5
Sichten (13)
„
„
Sichten über mehrere Tabellen sind im Allg. nicht änderbar
WITH CHECK OPTION (Forts.)
„
Annahmen
„
W = ΠA2,A3,B1,B2 (R
S)
A3 = B1
„
Not Null ?
W
„
R(
A1,
A2,
A3)
B1,
B2,
B3)
a11
a21
a31
S(
a31
b21
b31
a12
a22
a31
a32
b22
b32
a13
a23
a32
„
„
„
„
Ändern?
N. Ritter, DIS, SS 2005, Kapitel 5
128
„
Einfügungen und Änderungen müssen das die Sicht definierende Prädikat
erfüllen, sonst Zurückweisung
„
Spezifikationsmöglichkeiten
Weglassen der CHECK-Option
„
WITH CASCADED CHECK OPTION oder äquivalent WITH CHECK OPTION
„
WITH LOCAL CHECK OPTION
N. Ritter, DIS, SS 2005, Kapitel 5
erscheint irgendeine aktualisierte Zeile von SA nicht in SI, so wird die
Operation zurückgesetzt
Es ist möglich, dass Zeilen aufgrund von gültigen Einfüge- oder
Änderungsoperationen aus SA verschwinden
131
Aktualisierte Sicht besitzt WITH CHECK OPTION
„
nur auf aktualisierbaren Sichten definierbar
„
als Prüfbedingung wird von SI aus an SA “vererbt”:
V = VI ∧ VI-1 ∧ . . . ∧ V1
WITH CHECK OPTION (Forts.)
„
„
Oberhalb von SI tritt keine LOCAL-Bedingung auf
Sichten (17)
WITH CHECK OPTION
„
SI ist die höchste Sicht im Abstammungspfad von SA, die die Option
CASCADED besitzt
N. Ritter, DIS, SS 2005, Kapitel 5
Sichten (14)
„
Sicht SA mit dem die Sicht definierenden Prädikat VA wird aktualisiert
Aktualisierung von SA
Einfügen ?
„
130
Sichten (16)
Änderbarkeit von Sichten (Forts.)
„
LOCAL/CASCADED/___
•••
„
„
129
Default ist CASCADED
Als Prüfbedingung bei Aktualisierungen ergibt sich
V = VA ∧ V N ∧ . . . ∧ V I ∧ . . . ∧ V 1
Zeilen können jetzt aufgrund von gültigen Einfüge- oder
Änderungsoperationen nicht aus SA verschwinden
LOCAL hat eine undurchsichtige Semantik
„
wird hier nicht diskutiert
„
Empfehlung: nur Verwendung von CASCADED
N. Ritter, DIS, SS 2005, Kapitel 5
132
22
Sichten (18)
„
Zusammenfassung (1)
WITH CHECK OPTION (Forts.)
„
R
SQL-Anfragen
„
Mengenorientierte Spezifikation, verschiedene Typen von Anfragen
„
Vielfalt an Suchprädikaten
„
Auswahlmächtigkeit von SQL ist höher als die der Relationenalgebra.
Sichtenhierarchie:
S1
S2 mit V1 Λ V2
S1 mit V1 und CASCADED
S2
U1
R
„
I1
„
„
I1 und U1 erfüllen das S2-definierende Prädikat V1 ∧ V2
„
I2 und U2 erfüllen das S1-definierende Prädikat V1
„
I3 und U3 erfüllen das S1-definierende Prädikat V1 nicht
N. Ritter, DIS, SS 2005, Kapitel 5
133
Mengenorientierte Datenmanipulation
„
Datendefinition
„
SQL erlaubt nur die Spezifikation von binären Beziehungen.
Referentielle Integrität von FS --> PS/SK wird stets gewährleistet.
Sicht1 auf Pers: AP1, mit Beruf = ‘Progr’ AND Gehalt < ‘30K’
„
Rolle von PRIMARY KEY, UNIQUE, NOT NULL
Sicht2 auf AP1: AP2, mit Gehalt > ‘20K’
„
Tabelle Pers
„
„
Sichtdef.
Prädikat
1
___
CHECK-Optionen
2
3
4
___
CASC CASC
___
___
„
AP1
< ‘30K’
CASC
CASC
N. Ritter, DIS, SS 2005, Kapitel 5
134
Es sind stets sichere Schemata anzustreben
Schemaevolution
„
Indexstrukturen als B*-Bäume
„
Operationen
1
2
3
4
„
INSERT INTO AP2 (PNR, BERUF, GEHALT, ANR)
„
VALUES ( 1234, ‘Progr’ , ‘25K’, ‘K55’)
INSERT INTO AP2 (PNR, BERUF, GEHALT, ANR)
VALUES ( 4711, ‘Progr’ , ‘15K’, ‘K55’)
UPDATE AP2
137
WHERE ANR = ‘K55’
AP2: > 20K
-
AP1: < 30K
-
CASC
CASC
CASC
-
CASC
135
Änderung/Erweiterung von Spalten, Tabellen, Integritätsbedingungen, ...
mit und ohne Clusterbildung spezifizierbar
Balancierte Struktur unabhängig von Schlüsselmenge und
Einfügereihenfolge
„
dynamische Reorganisation durch Aufteilen (Split) und Mischen von Seiten
„
direkter Schlüsselzugriff auf einen indexierten Satz
„
SET Gehalt = Gehalt + ‘10K’
N. Ritter, DIS, SS 2005, Kapitel 5
Falls eine statische Schemaanalyse zu restriktiv für die Zulässigkeit
eines Schemas ist, muss für das gewünschte Schema eine
Laufzeitüberwachung der referentiellen Aktionen erfolgen.
„
Beispiel (Forts.)
3.
SQL2/3 bietet reichhaltige Optionen für referentielle Aktionen
„
Zusammenfassung (3)
WITH CHECK OPTION (Forts.)
2.
„
N. Ritter, DIS, SS 2005, Kapitel 5
Sichten (20)
1.
Es ist nur eine eingeschränkte Nachbildung von Kardinalitätsrestriktionen
möglich; insbesondere kann nicht spezifiziert werden, dass „ein Vater Söhne
haben muss“.
Wartung der referentiellen Integrität
„
PERS
„
136
Kontrolle von Beziehungen
„
„
> ‘20K’
„
Spezifikation des Überprüfungszeitpunktes
„
Beispiel
AP2
„
CHECK-Bedingungen für Wertebereiche, Attribute und Relationen
„
Zusammenfassung (2)
WITH CHECK OPTION (Forts.)
„
„
N. Ritter, DIS, SS 2005, Kapitel 5
Sichten (19)
„
Optimierung der Anfrageauswertung durch das DBS
„
Aktualisierungsoperationen in S2 (welche sind erlaubt?)
„
Erklärungsmodell für die Anfrageauswertung: Festlegung der Semantik
von Anfragen mit Hilfe von Grundoperationen
sortiert sequentieller Zugriff auf alle Sätze
(unterstützt Bereichsanfragen, Verbundoperation usw.
N. Ritter, DIS, SS 2005, Kapitel 5
138
23
Zusammenfassung (4)
„
Sichtenkonzept
„
Erhöhung der Benutzerfreundlichkeit
„
Flexibler Datenschutz
„
Erhöhte Datenunabhängigkeit
„
Rekursive Anwendbarkeit
„
Eingeschränkte Aktualisierungsmöglichkeiten
N. Ritter, DIS, SS 2005, Kapitel 5
139
24