primary key

SQL
Übersicht
•
•
•
•
•
•
•
•
Grundlagen
g
Mengenorientierte Anfragen (Retrieval)
Möglichkeiten der Datenmanipulation
Möglichkeiten der Datendefinition
Beziehungen und referentielle Integrität
Schemaevolution
Indexierung
Sichten
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
51
SQL
Einfügen von Tupeln
INSERT INTO table [ (column
(column-commalist)
commalist) ]
{ VALUES row-constr.-commalist | table-exp | DEFAULT VALUES }
•
Beispiel:
Füge den Schauspieler Garfield mit der PNR 4711 ein.
INSERT INTO SP (PNR
(PNR, NAME
NAME, W
W-ORT)
ORT)
VALUES (4711, „Garfield“, DEFAULT);
•
Anmerkungen (zum satzweisen Einfügen)
ƒ 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.
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
52
SQL
Einfügen von Tupeln
•
Beispiel:
Füge die Schauspieler
S h piele aus KL in die Relation
Rel tion TEMP ein.
ein
INSERT INTO TEMP
(SELECT *
FROM SP
WHERE W-ORT=„KL“);
•
Anmerkungen (zum mengenorientierten Einfügen)
ƒ Im Beispiel sei eine (leere) Relation TEMP vorhanden.
ƒ Die Datentypen der Attribute in TEMP müssen kompatibel zu den
Datentypen der ausgewählten Attribute sein.
ƒ Ein mengenorientiertes
g
Einfügen
g wählt die spezifizierte
p
Tupelmenge
p
g aus und
kopiert sie in die Zielrelation.
ƒ Die kopierten Tupel sind unabhängig von ihren Ursprungstupeln.
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
53
SQL
Löschen von Tupeln
searched-delete ::= DELETE FROM table [WHERE cond-exp]
•
•
Aufbau der WHERE-Klausel entspricht dem der SELECT-Anweisung
Beispiele:
Lösche den Schauspieler mit der PNR 4711.
DELETE FROM SCHAUSPIELER
WHERE PNR = 4711;
Lösche alle Schauspieler, die nie gespielt haben.
DELETE FROM SCHAUSPIELER S
WHERE NOT EXISTS (SELECT *
FROM DARSTELLER D
WHERE D.PNR = S.PNR);
);
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
54
SQL
Ändern von Tupeln
searched-update ::= UPDATE table
SET
update-assignment-commalist
update
assignment commalist
[WHERE cond-exp]
•
Beispiel:
Gib den Schauspielern, die am Pfalztheater spielen, eine Gehaltserhöhung
von 5% (Annahme: GEHALT in Schauspieler).
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’);
•
Einschränkung:
Innerhalb der WHERE-Klausel in einer Lösch- oder Änderungsanweisung
da f die Ziel
darf
Zielrelation
elation in einer
eine FROM-Klausel
FROM Kla sel nicht referenziert
efe en ie t werden.
e den
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
55
SQL
Übersicht
•
•
•
•
•
•
•
•
Grundlagen
g
Mengenorientierte Anfragen (Retrieval)
Möglichkeiten der Datenmanipulation
Möglichkeiten der Datendefinition
Beziehungen und referentielle Integrität
Schemaevolution
Indexierung
Sichten
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
56
SQL
Datendefinition
•
•
Ziel der SQL-Normierung
ƒ möglichst
ö li h t große
ß Unabhängigkeit
U bhä i k it der
d DB-Anwendungen
DB A
d
von speziellen
i ll DBS
ƒ einheitliche Sprachschnittstelle genügt nicht!
ƒ Beschreibung
g der gespeicherten
g p
Daten und ihrer Eigenschaften
g
nach
einheitlichen und verbindlichen Richtlinien ist genauso wichtig
Zweischichtiges Definitionsmodell für die Beschreibung der Daten
ƒ Informationsschema
- einheitliche Sichten auf das Definitionsschema
- für den Benutzer zugänglich
ƒ Definitionsschema
- beschreibt hypothetische Katalogstruktur
- erlaubt "Altsystemen" mit abweichenden Implementierungen normkonform zu
werden
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
57
SQL
Definitionsschema
REFERENTIAL_
CONSTRAINTS
Refs PK /
unique
FK
Is FK
PK /
unique
XOR
ASSERTIONS
owner
check
SCHEMATA
Default
char set
OR
TABLE_
CONSTRAINTS
CHECK_
CONSTRAINTS
DOMAIN_
CONSTRAINTS
DOMAINS
KEY_COLUMN_
USAGE
CHECK_TABLE_
USAGE
CHECK_COLUMN_
USAGE
DATA_TYPE_
_
_
DESCRIPTOR
Char set
TABLES
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
>0
COLUMNS
OR
COLLATIONS
58
SQL
Definitionsschema
>0
TABLES
COLUMNS
COLLATIONS
Default
collation
VIEW_TABLE_
USAGE
CHARACTER_
USAGE
VIEW_COLUMN_
USAGE
VIEW
target
TABLE_
TABLE
PRIVILEGES
grantor
grantee
g
COLUMN_
COLUMN
PRIVILEGES
grantor
grantee
USERS
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
USAGE_
USAGE
PRIVILEGES
grantor
OR
source
TRANSLATIONS
grantee
g
SQL_
LANGUAGES
59
SQL
Definition von Schemata
CREATE SCHEMA [schema] [AUTHORIZATION user]
[DEFAULT CHARACTER SET char-set]
h
et]
[schema-element-list]
•
•
•
•
Jedes Schema ist einem Benutzer (user) zugeordnet, z.B. DBA
Schema erhält Benutzernamen, falls keine explizite Namensangabe erfolgt
Definition aller Definitionsbereiche
Definitionsbereiche, Basisrelationen
Basisrelationen, Sichten (Views),
(Views)
Integritätsbedingungen und Zugriffsrechte
Beispiel:
CREATE SCHEMA Beispiel-DB AUTHORIZATION DB-Admin
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
60
SQL
Datentypen
•
CHARACTER [ ( length
g )]
((Abkürzung:
g CHAR))
•
CHARACTER VARYING [ ( length ) ]
(Abkürzung: VARCHAR)
•
...
•
NUMERIC [ ( precision [ , scale] ) ]
•
DECIMAL [ ( precision [ , scale ] ) ]
(Abkürzung: DEC)
•
INTEGER
(Abkürzung: INT)
•
REAL
•
...
•
DATE
•
TIME
•
...
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
61
SQL
Definition von Domains
CREATE DOMAIN domain [AS] data type
[DEFAULT { literal
lite l | niladic-function-ref
nil di f n tion ef | NULL} ]
[ [CONSTRAINT constraint] CHECK (cond-exp) [deferrability]]
•
•
Spezifikationsmöglichkeiten
ƒ Optionale Angabe von Default-Werten
ƒ Wertebereichseingrenzung durch benannte CHECK-Bedingung
CHECK Bedingung möglich
ƒ CHECK-Bedingungen können Relationen der DB referenzieren;
SQL-Domänen sind also dynamisch!
Beispiele:
CREATE DOMAIN ABTNR AS CHAR (6)
CREATE DOMAIN ALTER AS INT DEFAULT NULL
CONSTRAINT ALTERSBEGRENZUNG
CHECK (VALUE = NULL OR (VALUE > 18 AND VALUE < 70))
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
62
SQL
Definition von Attributen
column-def ::=
•
•
column { data-type | domain }
[ DEFAULT { literal | niladic-function-ref | NULL} ]
[ column-constraint-def-list ]
Spezifikation von
ƒ Attributname
ƒ Datentyp bzw.
bzw Domain
ƒ Defaultwert
ƒ Constraints
Beispiele:
PNAME CHAR (30)
( )
PALTER ALTER (siehe Definition von Domain ALTER)
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
63
SQL
Definition von Attributen
•
Constraints
column-constraint-def
ol mn on t int def ::= [CONSTRAINT constraint]
on t int]
{ NOT NULL | { PRIMARY KEY | UNIQUE }
| references-def | CHECK (cond-exp) } [deferrability]
•
Als Constraints können definiert werden
ƒ Verbot von Nullwerten (NOT NULL)
ƒ Eindeutigkeit (UNIQUE bzw.
bzw PRIMARY KEY)
ƒ FOREIGN-KEY-Klausel
ƒ CHECK-Bedingungen
Vorteile der Vergabe von Constraint
Constraint-Namen
Namen
ƒ Diagnosehilfe bei Fehlern
ƒ gezieltes Ansprechen bei SET oder DROP des Constraints
Beispiel:
Verkaufs_Preis DECIMAL (9, 2),
CONSTRAINT Ausverkauf
CHECK ( Verkaufs_Preis <= (SELECT MIN (Preis) FROM Konkurrenz_Preise))
•
•
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
64
SQL
Definition von Attributen
•
FOREIGN-KEY-Klausel
references-def ::= REFERENCES base-table [ (column-commalist)]
[ON DELETE referential-action]
[ON UPDATE referential-action]
referential-action ::=
NO ACTION | CASCADE | RESTRICT | SET DEFAULT | SET NULL
•
•
•
Bezieht sich der Fremdschlüssel auf den PRIMARY KEY der base-table, so
k
kann
d
die column-commalist
l
l entfallen
f ll
Fremdschlüssel kann auch auf Schlüsselkandidat definiert sein
Referentielle Aktionen werden später
p
behandelt
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
65
SQL
Definition von Attributen
•
Überprüfungszeitpunkt
deferrability ::= [ NOT ] DEFERRABLE
[ INITIALLY { DEFERRED | IMMEDIATE } ]
ƒ JJeder
d Constraint
C
t i t bzgl.
b l einer
i
SQL2-Transaktion
SQL2 T
kti iistt zu jedem
j d
Zeitpunkt
Z it
kt
in einem von zwei Modi: IMMEDIATE oder DEFERRED
IMMEDIATE: Constraint wird am Ende einer SQL-Anweisung überprüft
DEFERRED: Constraint wird erst am Ende der Transaktion überprüft
ƒ Der Default-Modus für Constraints ist NOT DEFERRABLE
ƒ Der Default für Constraints, die als DEFERRABLE angegeben sind, ist
IMMEDIATE
ƒ Anweisung SET CONSTRAINTS erlaubt den Wechsel des Modus
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
66
SQL
Erzeugen von Basisrelationen
CREATE TABLE base-table (base-table-element-commalist)
base-table-element ::= column-def | base-table-constraint-def
base-table-constraint-def
b
t bl
t i t d f ::= [CONSTRAINT constraint]
t i t] {
{ PRIMARY KEY | UNIQUE } (column-commalist) |
FOREIGN KEY (column-commalist) references-def |
CHECK (cond-exp)
(cond exp)
} [deferrability]
•
•
Definition
D
fi iti aller
ll zugehörigen
hö i
Attribute
Att ib t mit
it Typfestlegung
T f tl
Spezifikation aller Integritätsbedingungen (Constraints)
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
67
SQL
Erzeugen von Basisrelationen
•
Beispiel:
Definition der Relationen ABT und PERS
CREATE TABLE ABT
(ANR
ABTNR
ANAME
CHAR (30)
ANZAHL-ANGEST
INT
PRIMARY KEY (ANR))
(
))
NOT NULL
NULL,
NOT NULL,
NOT NULL,
CREATE TABLE PERS
((PNR
INT
PRIMARY KEY,,
BERUF
CHAR (30),
PNAME
CHAR (30)
NOT NULL,
PALTER
ALTER,
(* siehe Domaindefinition *)
MGR
INT
REFERENCES PERS,
PERS
ANR
ABTNR
NOT NULL, (* Domaindef. *)
W-ORT
CHAR (25)
DEFAULT ’ ’,
GEHALT
DEC (9,2)
DEFAULT 0,00 CHECK (GEHALT < 120.000,00)
FOREIGN KEY (ANR) REFERENCES ABT )
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
68
SQL
Übersicht
•
•
•
•
•
•
•
•
Grundlagen
g
Mengenorientierte Anfragen (Retrieval)
Möglichkeiten der Datenmanipulation
Möglichkeiten der Datendefinition
Beziehungen und referentielle Integrität
Schemaevolution
Indexierung
Sichten
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
69
SQL
(1:n) - Beziehungen
•
Beispiel (ERM):
[0,n]
ABT
•
ist_
beschäftigt_
in
[0,1]
PERS
Abbildung:
ABT ( ABTNR ...,
...
PERS (
PRIMARY KEY (ABTNR))
PNR ...,
ANR ...,
PRIMARY KEY (PNR),
FOREIGN KEY (ANR) REFERENCES ABT)
•
Referenzgraph:
ABT
ANR
referenzierte
PERS
referenzierende
Relation
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
70
SQL
(1:n) - Beziehungen
•
•
•
Bemerkung:
ƒ Für jede FS-Beziehung benötigt man einen separaten FS.
ƒ Mehrere FS können auf denselben PS/SK verweisen.
g
zusätzliche Einschränkungen:
g
Mögliche
ƒ Jeder Angestellte muss in einer Abteilung beschäftigt sein (PERS: [1,1]):
PERS.ANR ... NOT NULL
ƒ Jede Abteilung darf höchstens einen Angestellten beschäftigen (ABT: [0,1]):
[0 1]):
PERS.ANR ... UNIQUE
Bemerkung:
ƒ In
I SQL2 kann
k
(im
(i Rahmen
R h
der
d Erzeugung
E
von Relationen)
R l ti
) nicht
i ht spezifiziert
ifi i t
werden, dass
- eine Abteilung einen Mitarbeiter haben muss (z. B. ABT:[1,n])
- die
di A
Anzahl
hl der
d Mitarbeiter
Mit b it pro Abteilung
Abt il
einschränkt
i h ä kt sein
i sollll (außer
( ß [0,1])
[0 1])
ƒ Bei der Erstellung müssen solche Beziehungen verzögert überprüft werden.
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
71
SQL
(1:n) - Beziehungen
•
Beispiel (ERM):
[0,n]
hat_
Bü
Büro_
von
[1,1]
ABT
PERS
[0,n]
•
[0,1]
arbeitet_
für
Abbild
Abbildung:
ABT (ABTNR ...,
...
PERS (
PRIMARY KEY (ABTNR))
PNR ...,
ANRA ...,
ANRB... NOT NULL,
PRIMARY KEY (PNR),
FOREIGN KEY (ANRA) REFERENCES ABT,
FOREIGN KEY (ANRB) REFERENCES ABT)
•
Referenzgraph:
ANRB
ABT
PERS
ANRA
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
72
SQL
(1:1)-Beziehungen
(1:1)
Beziehungen
•
Beispiel (ERM):
[0 1]
[0,1]
hat_
Mgr
[0 1]
[0,1]
ABT
MGR
[0,1]
•
[0,1]
Abbildung (1.Ansatz):
ABT ( ANR ...,
MNR ... UNIQUE,
...
PRIMARY KEY (ANR),
FOREIGN KEY (MNR)
REFERENCES MGR)
•
leitet_
Abt
Referenzgraph:
MGR ( MNR ...,
ANR ... UNIQUE,
...
PRIMARY KEY (MNR),
FOREIGN KEY (ANR)
REFERENCES ABT)
MNR
ABT
MGR
ANR
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
73
SQL
(1:1) Beziehungen
(1:1)-Beziehungen
•
Alternative Lösungen möglich!
•
Mögliche zusätzliche Regeln zu obigem Beispiel:
ƒ Jede Abteilung hat einen Manager → ABT.MNR ... UNIQUE NOT NULL
ƒ Jeder Manager leitet eine Abteilung → MGR.ANR ... UNIQUE NOT NULL
•
Frage:
Kann durch die beiden Beziehungen eine symmetrische (1:1)-Beziehung
ausgedrückt werden?
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
74
SQL
Beispiel
•
•
Diskussion der verschiedenen Ansätze am Beispiel
ABT
MGR
a1
1
a2
2
a3
3
a4
4
1. Ansatz:
ABT (ANR, MNR, ...)
PERS (MNR, ANR, ...)
a1
1
1
a2
a2
2
2
a3
a3
3
3
a1
a4
-
4
-
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
75
SQL
Symmetrische (1:1)
(1:1)-Beziehung
Beziehung
•
Beispiel (ERM):
[1 1]
[1,1]
ABT
•
Abbildung (2. Ansatz):
•
Referenzgraph:
ƒ Nutzung des MNR-Attributes für
beide FS-Beziehungen
g gewährleistet
g
Einhaltung der (1:1)-Beziehung
ƒ Fall ([0,1] , [0,1]) so nicht darstellbar
ABT ( ANR ...,
MNR ... UNIQUE NOT NULL,
...
PRIMARY KEY ((ANR),
),
FOREIGN KEY (MNR)
REFERENCES MGR)
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
l it t
leitet
[1 1]
[1,1]
MGR
MGR ( MNR ...,
...
PRIMARY KEY (MNR),
FOREIGN
O
G KEY ((MNR))
REFERENCES ABT(MNR))
MNR
ABT
MGR
MNR
76
SQL
Beispiel
•
•
Diskussion der verschiedenen Ansätze am Beispiel
ABT
MGR
a1
1
a2
2
a3
3
a4
4
2. Ansatz:
ABT (ANR,
(ANR MNR,
MNR ...))
PERS (MNR,
(MNR ...))
a1
1
1
a2
2
2
a3
3
3
?
?
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
77
SQL
Symmetrische (1:1)
(1:1)-Beziehung
Beziehung
•
•
Variation über Schlüsselkandidaten
Abbildung (3
(3. Ansatz):
ABT (ANR ...,
MNR ... UNIQUE,
...
PRIMARY KEY (ANR),
FOREIGN KEY (MNR)
REFERENCES MGR(MNR)
•
•
•
MGR ( SVNR ...,
MNR ... UNIQUE,
...
PRIMARY KEY (SVNR)
FOREIGN KEY (MNR)
REFERENCES ABT(MNR))
Die Nutzung von Schlüsselkandidaten mit der Option NOT NULL
erlaubt
l bt die
di Darstellung
D t ll
des
d Falles
F ll ([1,1]
([1 1] , [1,1])
[1 1])
Alle Kombinationen mit [0,1] und [1,1] sind möglich
Es sind alternative Lösungen möglich
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
78
SQL
Beispiel
•
•
Diskussion der verschiedenen Ansätze am Beispiel
ABT
MGR
a1
1
a2
2
a3
3
a4
4
3. Ansatz:
ABT (ANR,
(ANR MNR,
MNR ...))
PERS (SVNR,
(SVNR MNR,
MNR ...))
a1
1
x
1
a2
2
y
2
a3
3
z
3
a4
-
w
-
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
79
SQL
(n:m)-Beziehungen
(n:m)
Beziehungen
•
Beispiel (ERM):
PERS
•
[0,n]
bearbeitet
[0,m]
PROJ
Abbildung:
PERS (PNR ...,
...
PRIMARY KEY (PNR))
PROJ (JNR ...,
...
PRIMARY KEY (JNR))
MITARBEIT (PNR ...,
JNR ...,
PRIMARY KEY (PNR, JNR),
FOREIGN KEY ((PNR)) REFERENCES PERS,,
FOREIGN KEY (JNR) REFERENCES PROJ)
•
•
•
Diese Standardlösung 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
PROJ
Ist die Realisierung von [1,n] oder [1,m] bei der
Abbild ng der
Abbildung
de (n
(n:m)-Beziehung
m) Be ieh ng möglich?
Referenzgraph:
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
PNR
JNR
MITARBEIT
80
SQL
Reflexive (1:n)
(1:n)-Beziehung
Beziehung
•
Beispiel (ERM):
[0,n]
PERS
•
hat_
Mgr
[0,1]
Abbildung:
PERS (PNR ...,
MNR ...,
...
PRIMARY KEY (PNR),
FOREIGN KEY (MNR) REFERENCES PERS (PNR))
•
Referenzgraph:
PERS
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
MNR
81
SQL
Reflexive (1:n)
(1:n)-Beziehung
Beziehung
•
•
•
Mit Hilfe der gezeigten Lösung kann die Personal-Hierarchie eines
Unte nehmen dargestellt
Unternehmens
d ge tellt werden
e den
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?
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
82
SQL
Zusammenfassung Beziehungen
•
•
•
•
•
•
•
Relationenmodell ‚‚hat‘ wertbasierte Beziehungen
g
Fremdschlüssel (FS) und zugehöriger Primärschlüssel/Schlüsselkandidat
(PS/SK) repräsentieren eine Beziehung (gleiche Wertebereiche!)
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:
PS
PRIMARY KEY
(implizit: UNIQUE NOT NULL)
SK
UNIQUE [NOT NULL]
FS
[UNIQUE] [NOT NULL]
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
83
SQL
Zusammenfassung Beziehungen
•
Fremdschlüsseldeklaration in S
V
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
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
84
SQL
Beispiel Datendefinition
•
Miniwelt (ER-Diagramm)
Fachbereich
0N
0,N
0,N
1,1
ist-Dekanist
Dekan
von
0,1
Prof
gehört-zu
gehört
zu
isteingeschr.in
1,1
1,1
0,N
Prüfung
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
0,M
Student
85
SQL
Beispiel Datendefinition
•
Illustration des DB-Schemas
FB
FBNR
FBNAME
DEKAN
STUDENT
MATNR SNAME FBNR STUDBEG
PROF
PNR PNAME FBNR FACHGEBIET
PRUEFUNG
PNR MATNR FACH PDATUM NOTE
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
86
SQL
Beispiel Datendefinition
•
Wertebereiche:
CREATE DOMAIN
FACHBEREICHSNUMMER
AS
CHAR
(4)
CREATE DOMAIN
FACHBEREICHSNAME
AS
VARCHAR
(20)
CREATE DOMAIN
FACHBEZEICHNUNG
AS
VARCHAR
(20)
CREATE DOMAIN
NAMEN
AS
VARCHAR
(30)
CREATE DOMAIN
PERSONALNUMMER
AS
CHAR
(4)
CREATE DOMAIN
MATRIKELNUMMER
AS
INT
CREATE DOMAIN
NOTEN
AS
SMALLINT
CREATE DOMAIN
DATUM
AS
DATE
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
87
SQL
Beispiel Datendefinition
•
Relationen:
CREATE TABLE FB (
FBNR
FACHBEREICHSNUMMER
PRIMARY KEY,
FBNAME FACHBEREICHSNAME
UNIQUE
UNIQUE,
DEKAN PERSONALNUMMER
UNIQUE NOT NULL,
CONSTRAINT FFK FOREIGN KEY (DEKAN)
REFERENCES PROF (PNR)
ON UPDATE CASCADE
ON DELETE RESTRICT)
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
88
SQL
Beispiel Datendefinition
•
Relationen:
CREATE TABLE PROF (
PNR
PERSONALNUMMER
PRIMARY KEY,
PNAME
NAMEN
NOT NULL,
FBNR
FACHBEREICHSNUMMER
NOT NULL,
FACHGEBIET
FACHBEZEICHNUNG,
CONSTRAINT PFK1 FOREIGN KEY (FBNR)
REFERENCES FB (FBNR)
ON UPDATE CASCADE
ON DELETE SET DEFAULT)
ƒ 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.
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
89
SQL
Beispiel Datendefinition
•
Relationen:
CREATE TABLE STUDENT (
MATNR
MATRIKELNUMMER
PRIMARY KEY,
S
SNAME
NAMEN
NOT
O NULL,
FBNR
FACHBEREICHSNUMMER
NOT NULL,
STUDBEG DATUM,
CONSTRAINT SFK FOREIGN KEY (FBNR)
REFERENCES FB (FBNR)
ON UPDATE CASCADE
ON DELETE RESTRICT)
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
90
SQL
Beispiel Datendefinition
•
Relationen:
CREATE TABLE PRUEFUNG (
PNR
PERSONALNUMMER
PERSONALNUMMER,
MATNR
MATRIKELNUMMER,
FACH
FACHBEZEICHNUNG,
PDATUM DATUM
NOT NULL,
NOTE
NOT NULL,
NOTEN
PRIMARY KEY (PNR,
(
MATNR),
)
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
ON DELETE CASCADE)
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
91
SQL
Beispiel Datendefinition
•
Ausprägungen
PROF PNR
1234
5678
4711
6780
2223
STUDENT
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
PRÜFUNG PNR
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
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
23.10.95
1. 4.97
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
FB
FBNR
FBNAME
DEKAN
FB 9
FB 5
WIRTSCHAFTSWISS
INFORMATIK
4711
2223
4
3
2
4
2
1
3
92
SQL
Wartung von Beziehungen
•
•
Relationale Invarianten:
ƒ Primärschlüsselbedingung:
- Eindeutigkeit, keine Nullwerte!
ƒ Fremdschlüsselbedingung:
- Zugehöriger PS (SK) muss existieren
Potentielle Gefährdung der Fremdschlüsselbedingung/referentiellen Integrität
ƒ Operationen in der Sohn-Relation
ƒ Operationen in der Vater-Relation
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
93
SQL
Potentielle Gefährung der ref. Integrität
FS
Sohn-Relation
PS/SK
Vater-Relation
Operationen in der Sohn-Relation:
Operationen in der Vater-Relation:
• Einfügen eines Sohn
Sohn-Tupels
Tupels
• Ändern des FS in einem Sohn-Tupel
• Löschen eines Sohn-Tupels
• Einfügen eines Vater
Vater-Tupels
Tupels
• Ändern des PS/SK in einem Vater-Tupel
• Löschen eines Vater-Tupels
Welche Maßnahmen sind erforderlich?
• Beim Einfügen
g erfolgt
g eine Prüfung,
g, ob
in einem Vater-Tupel ein PS/SK-Wert
gleich dem FS-Wert des einzufügenden
Tupels existiert
• Beim Ändern eines FS-Wertes erfolgt
eine analoge Prüfung
Welche Reaktion ist wann möglich?
• Verbiete Operation
p
• Lösche/ändere rekursiv Tupel mit
zugehörigen FS-Werten
• Falls Sohn
Sohn-Tupel
Tupel erhalten bleiben soll
(nicht immer möglich, z.B. bei
Existenzabhängigkeit), setze FS-Wert zu
NULL oder Default
¾Referential Actions
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
94
SQL
Referentielle Aktionen
•
•
SQL2-Standard führt „referential actions“ ein
S
Spezifikation
ifik i der
d referentiellen
f
i ll Aktionen:
Ak i
Legt für einen Fremdschlüssel (FS) in der Sohn-Relation fest, welche
Auswirkungen Operationen in der Vater-Relation haben:
•
Löschregel (Löschen in der Vater-Relation)
ON DELETE
{CASCADE | RESTRICT | SET NULL | SET DEFAULT | NO ACTION}
•
Änderungsregel (Ändern des PS oder SK in der Vaterrelation)
ON UPDATE
{CASCADE | RESTRICT | SET NULL | SET DEFAULT | NO ACTION}
•
Die Option NO ACTION wird hier explizit aufgeführt; sie entspricht dem Fall,
dass die gesamte Klausel weggelassen wird.
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
95
SQL
Referentielle Aktionen
•
RESTRICT (DR, UR):
Ope tion wird
Operation
i d nur
n ausgeführt,
gefüh t wenn
enn keine zugehörigen
gehö igen Sät
Sätze
e (FS-Werte)
(FS We te)
vorhanden sind
•
CASCADE (DC, UC):
Operation „kaskadiert“ zu allen zugehörigen Sätzen
•
SET NULL (DSN, USN):
FS wird in zugehörigen Sätzen zu „Null
„Null“ gesetzt
•
SET DEFAULT (DSD, USD):
FS wird in den zugehörigen Sätzen auf einen benutzer-definierten DefaultWert gesetzt
•
NO ACTION (DNA, UNA):
Für die spezifizierte Referenz wird keine referentielle Aktion ausgeführt.
D h eine
Durch
i DB-Operation
DB O
ti können
kö
jedoch
j d h mehrere
h
Referenzen
R f
(mit
( it
unterschiedlichen Optionen) betroffen sein; am Ende aller zugehörigen
referentiellen Aktionen wird die Einhaltung der referentiellen Integrität
geprüft
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
96
SQL
Auswirkungen referentieller Aktionen
1. Isolierte Betrachtung von STUDENT-FB
STUDENT
FBNR
FB
ƒ Operationen
-
Lösche FB ((mit FBNR „FB5“))
-
Ändere FB (FBNR=„FB9“ → FBNR=„FB10“)
STUDENT
MATNR
SNAME
FBNR
123 766
COY
FB 9
225 332
MÜLLER
FB 5
654 711
ABEL
FB 5
226 302
SCHULZE
FB 9
196 481
MAIER
FB 5
130 680
SCHMID
FB 9
ƒ Referentielle Aktionen
-
DC DSN,
DC,
DSN DSD
DSD, DR
DR, DNA
FB
-
UC, USN, USD, UR, UNA
FBNR
FBNAME
FB 9
WIRTSCHAFTSWISS
FB 5
INFORMATIK
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
97
SQL
Auswirkungen referentieller Aktionen
2. Isolierte Betrachtung von STUDENT-PRUEFUNG-PROF
PROF
STUDENT
PNR
MATNR
PRUEFUNG
PROF
PNR
PNAME
FBNR
1234
HÄRDER
FB 5
4711
MÜLLER
FB 9
PRUEFUNG
ƒ Einsatz von
- USN, DSN → Schlüsselverletzung
- USD, DSD → ggf. Mehrdeutigkeit
- UNA,
UNA DNA → Wirkung identisch mit UR
UR, DR
PNR
MATNR
FACH
4711
123 766
OR
1234
654 711
DV
1234
123 766
DV
4711
654 711
OR
STUDENT
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
MATNR
SNAME
FBNR
123 766
COY
FB 9
654 711
ABEL
FB 5
98
SQL
Auswirkungen referentieller Aktionen
3. Vollständiges Beispiel – Variante 1
FB
DC
FBNR
FBNR
PROF
DC
DC
STUDENT
PNR
MATNR
DC
Unabhängigkeit von
g hinsichtlich
Beziehungen
referentieller Aktionen?
PRUEFUNG
ƒ Operation: Lösche FB (mit FBNR „FB9“)
‘ erstt links’:
li k ’
- Löschen in FB
- Löschen in PROF
- Löschen in PRUEFUNG
- Löschen in STUDENT
- Löschen in PRUEFUNG
‘erstt rechts’:
ht ’
- Löschen in FB
- Löschen in STUDENT
- Löschen in PRUEFUNG
- Löschen in PROF
- Löschen in PRUEFUNG
ƒ Eindeutigkeit: Ergebnis der Operation ist reihenfolge-unabhängig
→ sicheres Schema!
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
99
SQL
Auswirkungen referentieller Aktionen
3. Vollständiges Beispiel – Variante 2
FB
DC
FBNR
FBNR
PROF
DC
DC
STUDENT
PNR
MATNR
DR
PRUEFUNG
ƒ Operation: Lösche FB (mit FBNR „FB9“)
‘ erst links’:
- Löschen
Lö h in
i FB
- Löschen in PROF
- Löschen in PRUEFUNG
- Löschen in STUDENT
- Zugriff
g
auf PRUEFUNG
Wenn ein Student bei einem FB-fremden
Professor geprüft wurde
→ Rücksetzen
‘erst rechts’:
- Löschen
Lö h in
i FB
- Löschen in STUDENT
- Zugriff auf PRUEFUNG
Wenn ein gerade gelöschter Student eine
Prüfung
g abgelegt
g g hatte
→ Rücksetzen
sonst:
- Löschen in PROF
- Löschen in PRUEFUNG
ƒ E
Es können
kö
reihenfolgenabhängige
ih f l
bhä i
E
Ergebnisse
b i
auftreten!
ft t !
ƒ Die Reihenfolgenabhängigkeit ist hier wertabhängig
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
100
SQL
Auswirkungen referentieller Aktionen
3. Vollständiges Beispiel – Variante 2
ƒ Operation: Lösche FB (mit FBNR „FB9“)
„FB9 )
‘ erst links’:
1.
2.
3.
4.
5.
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
FB
c
FBNR
FBNAME
FB 9
WIRTSCHAFTSWISS
FB 5
INFORMATIK
PROF
d
PNR
PNAME
FBNR
1234
HÄRDER
FB 5
5678
WEDEKIND
FB 9
4711
MÜLLER
FB 9
6780
NEHMER
FB 5
2223
RICHTER
FB 5
e
g
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
STUDENT
MATNR
SNAME
FBNR
123 766
COY
FB 9
225 332
MÜLLER
FB 5
PRUEFUNG
654 711
ABEL
FB 5
PNR
MATNR
226 302
SCHULZE
FB 9
5678
123 766
196 481
MAIER
FB 5
4711
123 766
130 680
SCHMID
FB 9
1234
654 711
1234
123 766
6780
654 711
1234
196 481
6780
196 481
f
101
SQL
Auswirkungen referentieller Aktionen
3. Vollständiges Beispiel – Variante 2
ƒ Operation: Lösche FB (mit FBNR „FB9“)
„FB9 )
‘ erst rechts’:
1.
2.
3.
4.
5.
Löschen in FB
Löschen in STUDENT
Zugriff
g
auf PRUEFUNG
Wenn ein gerade gelöschter Student
eine Prüfung abgelegt hatte
→ Rücksetzen
Löschen in PROF
Löschen in PRUEFUNG
FB
c
FBNR
FBNAME
FB 9
WIRTSCHAFTSWISS
FB 5
INFORMATIK
PROF
f
PNR
PNAME
FBNR
1234
HÄRDER
FB 5
5678
WEDEKIND
FB 9
4711
MÜLLER
FB 9
6780
NEHMER
FB 5
2223
RICHTER
FB 5
eg
e
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
STUDENT
MATNR
SNAME
FBNR
123 766
COY
FB 9
225 332
MÜLLER
FB 5
PRUEFUNG
654 711
ABEL
FB 5
PNR
MATNR
226 302
SCHULZE
FB 9
5678
123 766
196 481
MAIER
FB 5
4711
123 766
130 680
SCHMID
FB 9
1234
654 711
1234
123 766
6780
654 711
1234
196 481
6780
196 481
d
102
SQL
Auswirkungen referentieller Aktionen
3. Vollständiges Beispiel – Variante 3
FB
DC
FBNR
FBNR
PROF
DC
DC
STUDENT
PNR
MATNR
DNA
PRUEFUNG
ƒ
Operation: 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
ƒ
‘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
Bei der NA-Option wird der explizite Test der referenzierenden Relation ans
Ende der Operation
p
verschoben. Eine Verletzung
g der referentiellen Beziehung
g
führt zum Rücksetzen
→ Schema ist immer sicher
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
103
SQL
Verhinderung von Mehrdeutigkeiten
Maßnahmen:
• Statische Schemaanalyse zur
Feststellung sicherer DB-Schemata
ƒ nur bei einfach strukturierten
Schemata effektiv
ƒ hohe Komplexität der
Analysealgorithmen
ƒ bei wertabhängigen Konflikten zu
restriktiv (konfliktträchtige
Schemata)
• Dynamische Überwachung der
Modifikationsoperationen
ƒ hoher Laufzeitaufwand
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
Vorgehensweisen:
1. Falls Sicherheit eines Schemas
festgestellt werden kann, ist
keine Laufzeitüberwachung
erforderlich
2. Alternative Möglichkeiten zur
Behandlung
g konfliktträchtiger
g
Schemata, nachdem die statische
Schemaanalyse die Sicherheit des
Schemas nicht feststellen konnte
- sie werden verboten, oder
- sie werden erlaubt und
• die referentiellen Aktionen
werden bei jeder Operation
dynamisch überwacht
• falls ein Konflikt erkannt
wird, wird die Operation
zurückgesetzt
104
SQL
Durchführung der Änderungsoperationen
•
Prüfung der referentiellen Integrität (IMMEDIATE/DEFERRED)
BEGIN
•
OP1
OP2
OP3
COMMIT
Zyklische Referenzpfade
FS1
ABT
MGR
FS2
ƒ 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)
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
105
SQL
Durchführung der Änderungsoperationen
•
Verarbeitungsmodell
ƒ Benutzeroperationen (Op) sind in SQL immer atomar
ƒ mengenorientiertes oder tupelorientiertes Verarbeitungsmodell
Op
Op
t1
t1
t2
tn
...
t2 ... tn
RA
RA
RA
RA‘s
ƒ IMMEDIATE-Bedingungen müssen an Anweisungsgrenzen erfüllt sein
(→ mengenorientierte Änderung)
Ä
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
106
SQL
Übersicht
•
•
•
•
•
•
•
•
Grundlagen
g
Mengenorientierte Anfragen (Retrieval)
Möglichkeiten der Datenmanipulation
Möglichkeiten der Datendefinition
Beziehungen und referentielle Integrität
Schemaevolution
Indexierung
Sichten
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
107
SQL
Schemaevolution
•
Wachsender oder
sich
i h ändernder
ä d d
Informationsbedarf
ƒ 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
Veränderte
Anforderungen bei
der DB-Nutzung
ƒ D
Dynamisches
i h Anlegen
A l
von
Zugriffspfaden
ƒ Aktualisierung der
Z iff k t llb di
Zugriffskontrollbedingungen
Hoher Grad an logischer
Datenunabhängigkeit ist sehr wichtig!
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
108
SQL
Dynamische Änderung von Tabellen
•
ALTER TABLE-Anweisung
ALTER TABLE base-table
{ADD [COLUMN] column-def
| ALTER [COLUMN] column {SET default-def | DROP DEFAULT}
| DROP [COLUMN] column {RESTRICT | CASCADE}
| ADD base
base-table-constraint-def
table constraint def
| DROP CONSTRAINT constraint {RESTRICT | CASCADE}}
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
109
SQL
Dynamische Änderung von Tabellen
•
Beispiele:
E
Erweiterung
it
der
d Tabellen
T b ll Abt und
d Pers
P
um neue Spalten
S lt
ALTER TABLE Pers ADD Svnr INT UNIQUE
ALTER TABLE Abt ADD Geh-Summe INT
Verkürzung
g der Tabelle Pers um eine Spalte
p
ALTER TABLE Pers DROP COLUMN Alter RESTRICT
ƒ Wenn die Spalte die einzige der Tabelle ist, wird die Operation
zurückgewiesen.
ƒ Da RESTRICT spezifiziert ist
ist, wird die Operation zurückgewiesen
zurückgewiesen, wenn
die Spalte in einer Sicht oder einer Integritätsbedingung (CHECK)
referenziert wird.
ƒ CASCADE dagegen erzwingt die Folgelöschung aller Sichten und CHECK,
CHECK
die von der Spalte abhängen.
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
110
SQL
Löschen von Schemaelementen
•
DROP-Anweisung
DROP {
•
•
•
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
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
111
SQL
Löschen von Schemaelementen
•
Beipiele:
Lö h von Tabelle
Löschen
T b ll Pers
P
DROP TABLE Pers RESTRICT
PersConstraint sei definiert auf Pers
ALTER TABLE Pers
DROP CONSTRAINT PersConstraint CASCADE
DROP TABLE Pers RESTRICT
•
Durchführung der Schemaevolution
ƒ Aktualisierung von Tabellenzeilen des SQL-Definitionsschemas
ƒ “tabellengetriebene” Verarbeitung der Metadaten durch das DBS
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
112
SQL
Übersicht
•
•
•
•
•
•
•
•
Grundlagen
g
Mengenorientierte Anfragen (Retrieval)
Möglichkeiten der Datenmanipulation
Möglichkeiten der Datendefinition
Beziehungen und referentielle Integrität
Schemaevolution
Indexierung
Sichten
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
113
SQL
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:
ƒ e
erhöhter
ö te Aktualisierungsaufwand
tua s e u gsau a d und
u d Speicherplatzbedarf
Spe c e p at beda
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
114
SQL
Einrichtung von Indexstrukturen
•
•
•
•
•
•
Datenunabhängigkeit des Relationenmodells erlaubt ein Hinzufügen und
Löschen
jederzeit möglich, um z. B. bei veränderten Benutzerprofilen das
Leistungsverhalten zu optimieren
“beliebig”
beliebig viele Indexstrukturen pro Tabelle und mit unterschiedlichen
Spaltenkombinationen als Schlüssel möglich
Steuerung der Eindeutigkeit der Schlüsselwerte, der Clusterbildung
F i l
Freiplatzanteil
il (PCTFREE) in
i jeder
j d Indexseite
I d
i beim
b i Anlegen
A l
erleichtert
l i h
das
d
Wachstum
Spezifikation:
ƒ DBA oder Benutzer
ƒ DBMS auf Grundlage des Workloads
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
115
SQL
Indexierung
•
Im SQL-Standard keine Anweisung
g vorgesehen,
g
jedoch
j
in realen Systemen
(z. B. IBM DB2):
CREATE [UNIQUE] INDEX index
ON base-table (column [ORDER] [,column[ORDER]] ...)
[CLUSTER] [PCTFREE]
•
Nutzung eines vorhandenen Index
ƒ Entscheidung durch DBS-Optimizer
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
116
SQL
Indexierung
•
Beispiele:
Erzeugung einer Indexstruktur mit Clusterbildung auf der Spalte Anr von Abt
CREATE UNIQUE INDEX Persind1 ON Abt (Anr) CLUSTER
ƒ 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)
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
117
SQL
B Baum
B*-Baum
•
•
•
Typische Implementierung eines Index
wird von allen DBS angeboten!
Knotenformat
ƒ Zi = Zeiger auf Sohnseite bzw. Satz
ƒ Si = Schlüssel
•
•
•
•
•
•
SL
Z0 S1 Z1 S2 Z2 …
ES = SL/EL = max. # Einträge/Seite
hB = Baumhöhe
NT = #Zeilenverweise im B*-Baum
NB = #Blattseiten im B*-Baum
h -1
1
h
NTmin = 2 (ES/2) b ≤ NT ≤ ES b = NTmax
Realistische Größenverhältnisse:
hB = 3 und EL= 20 B
Sm Zm frei
f i
EL
2
4
6
8
hB
2
3
4
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
5
6
7
8
9
NB
118
SQL
B*-Baum
B
Baum
•
•
•
dynamische Reorganisation durch Aufteilen (Split) und Mischen von Seiten
Wesentliche Funktionen
ƒ direkter Schlüsselzugriff auf einen indexierten Satz
ƒ sortiert sequentieller Zugriff auf alle Sätze
(unterstützt Bereichsanfragen, Verbundoperation usw.)
Balancierte Struktur
ƒ unabhängig
bhä i von Schlüsselmenge
S hlü l
ƒ unabhängig von Einfügereihenfolge
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
119
SQL
Indexierung
• Index mit Clusterbildung
g
• Index ohne Clusterbildung
g
c
c
IAbt(Anr)
d
8
13
25
61
33
45
77
85
d
e
e
f
f
Tupel
c
d
e
f
IPers(Anr)
8
13
25
61
33
45
77
85
Seite
Wurzelseite
Zwischenseiten
Blattseiten
Datenseiten
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
120
SQL
Übersicht
•
•
•
•
•
•
•
•
Grundlagen
g
Mengenorientierte Anfragen (Retrieval)
Möglichkeiten der Datenmanipulation
Möglichkeiten der Datendefinition
Beziehungen und referentielle Integrität
Schemaevolution
Indexierung
Sichten
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
121
SQL
Sichten
•
Ziel: Festlegung
ƒ welche Daten Benutzer sehen wollen (Vereinfachung, leichtere Benutzung)
ƒ welche Daten sie nicht sehen dürfen (Datenschutz)
g (erhöhte
(
Datenunabhängigkeit)
gg
)
ƒ einer zusätzlichen Abbildung
•
Sicht (View)
ƒ mit Namen bezeichnete,
bezeichnete aus Tabellen abgeleitete,
abgeleitete virtuelle Tabelle (Anfrage)
•
Anlegen von Sichten:
CREATE VIEW view [ (column-commalist ) ] AS table-exp
[WITH [ CASCADED | LOCAL] CHECK OPTION]
•
Korrespondenz zum externen Schema bei ANSI/SPARC;
Benutzer sieht jedoch i. allg. mehrere Sichten (Views) und Tabellen
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
122
SQL
Sichten
•
Beispiele:
Si ht die
Sicht,
di alle
ll Programmierer
P
i
mit
it einem
i
Gehalt
G h lt < 30.000
30 000 umfasst.
f t
CREATE VIEW Arme_Programmierer (Pnr, Name, Beruf, Gehalt, Anr) AS
SELECT Pnr, Name, Beruf, Gehalt, Anr
FROM Pers
WHERE Beruf = ’Programmierer’
Programmierer AND Gehalt < 30 000
Sicht für den Datenschutz
CREATE VIEW Statistik (Beruf, Gehalt) AS
SELECT Beruf, Gehalt
FROM Pers
P
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
123
SQL
Sichten
• Sichten zur Gewährleistung
g von
Datenunabhängigkeit
Benutzer
Sicht 4
Sicht 1
Sicht 2
Sicht 3
• Eigenschaften:
g
ƒ Sicht kann wie eine Tabelle
behandelt werden
ƒ Sichtsemantik: „dynamisches
dynamisches
Fenster“ auf zugrundeliegende
Tabellen
ƒ Sichten auf Sichten sind möglich
ƒ eingeschränkte
Änderungsmöglichkeiten:
- aktualisierbare Sichten
- nicht-aktualisierbare Sichten
Tabelle 1
Tabelle 2
Tabelle 3
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
Tabelle 4
124
SQL
Semantik von Sichten
•
Semantik: ‚dynamisches Fenster‘
Sicht V als dynamisches Fenster
Tabelle R ( A1,
A2,
A3,
A4,
A5 )
t1:
a 11
a 12
a 13
a 14
a 15
t4:
a 41
a 42
a 43
a 44
a 45
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
125
SQL
Sichtbarkeit von Änderungen
•
Wann werden welche Datenänderungen in der Tabelle/Sicht für die anderen
Benutzer sichtbar?
(Beachte Beispiel auf vorangegangener Folie)
Vor BOT
von T1, T2
Nach EOT
von T1, T2
Insert t2
B1 sieht R = {t1, t4}
Select t4
T1
Insert t3‘
B2 sieht V = {t1‘}
T2
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
R=
?
V=
?
Select t2‘
126
SQL
Sichtbarkeit von Änderungen
•
Wann werden welche Datenänderungen in der Tabelle/Sicht für die anderen
Benutzer sichtbar?
Sicht V als dynamisches
y
Fenster
Tabelle R ( A1,
A2,
A3,
A4,
A5 )
t1:
a 11
a 12
a 13
a 14
a 15
t2:
a21
a22
a23
a24
a25
t3:
≡
a32
a33
a34
≡
t4:
a 41
a 42
a 43
a 44
a 45
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
127
SQL
Abbildung
g von Sicht-Operationen
p
auf Tabellen
•
•
•
Sichten werden i. allg. nicht explizit und permanent gespeichert, sondern
Si ht Ope tionen werden
Sicht-Operationen
e den in äquivalente
äq i lente Operationen
Ope tionen auff Tabellen
T bellen
umgesetzt
Umsetzung ist für Leseoperationen meist unproblematisch
Beispiel:
Anfrage (Sichtreferenz):
SELECT
FROM
WHERE
Name, Gehalt
Arme_Programmierer
Anr = ‘K55’
Ersetzung durch:
SELECT
FROM
WHERE
Name, Gehalt
PERS
Anr = ‘K55’
AND Beruf = ’Programmierer’ AND Gehalt < 30 000
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
128
SQL
Abbildung
g von Sicht-Operationen
p
auf Tabellen
•
Abbildungsprozess auch über mehrere Stufen durchführbar
Sichtendefinitionen:
CREATE VIEW V AS SELECT ... FROM R WHERE P
CREATE VIEW W AS SELECT ... FROM V WHERE Q
Anfrage:
SELECT ... FROM W WHERE C
Ersetzung durch:
SELECT
S
C ... FROM
O V WHERE Q AND C
SELECT ... FROM R WHERE Q AND P AND C
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
129
SQL
Abbildung
g von Sicht-Operationen
p
auf Tabellen
•
•
Einschränkungen der Abbildungsmächtigkeit:
ƒ keine Schachtelung von Aggregat
Aggregat-Funktionen
Funktionen und Gruppenbildung
(GROUP-BY)
ƒ keine Aggregat-Funktionen in WHERE-Klausel möglich
Beispiel:
Sichtendefinition
CREATE VIEW Abtinfo (Anr, Gsumme) AS
SELECT Anr, SUM (Gehalt)
FROM Pers
GROUP BY Anr
Anfrage
SELECT AVG (Gsumme) FROM Abtinfo
E t
Ersetzung
d
durch
h (bei
(b i naiver
i
Vorgehensweise)
V
h
i )
?
SELECT
…
FROM Pers
GROUP BY Anr
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
130
SQL
Löschen von Sichten
•
Beispiel:
DROP VIEW Arme_Programmierer CASCADE
•
•
Alle referenzierenden Sichtdefinitionen und Integritätsbedingungen werden
mitgelöscht
RESTRICT würde eine Löschung zurückweisen,
zurückweisen wenn die Sicht in weiteren
Sichtdefinitionen oder CHECK-Constraints referenziert werden würde.
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
131
SQL
Änderbarkeit von Sichten
ll Si
Sichten
ht
alle
ttheoretisch
eo et sc ä
änderbare
de ba e S
Sichten
c te
in SQL änderbare Sichten
•
Änderbarkeit in SQL
ƒ nur eine Tabelle (Basisrelation oder Sicht)
ƒ Schlüssel muss vorhanden sein
ƒ keine Aggregatfunktionen
ƒ keine Gruppierung
ƒ keine
k i Duplikateliminierung
D lik t li i i
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
132
SQL
Änderbarkeit von Sichten
•
Sichten über mehrere Tabellen sind im Allg. nicht änderbar
W = ΠA2,A3,B1,B2 (R
S)
A3 = B1
Not Null ?
W
R(
A1 ,
A2,
A3)
a 11
a 21
a 12
a 13
S(
B1,
B2,
B3)
a 31
a 31
b21
b31
a 22
a 31
a 32
b22
b32
a 23
a 32
Einfügen ?
Ändern?
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
133
SQL
Änderbarkeit von Sichten
•
•
WITH CHECK OPTION
ƒ Einfügungen
Ei fü
und
d Änderungen
Ä d
müssen
ü
das
d die
di Sicht
Si ht definierende
d fi i
d Prädikat
P ädik t
erfüllen, sonst Zurückweisung
Sichtdef.
CHECKƒ nur auf aktualisierbaren
Prädikat
SA
Option
Sichten definierbar
VA
___
Spezifikationsmöglichkeiten:
ƒ Weglassen der CHECK-Option
ƒ WITH CASCADED CHECK OPTION
oder äquivalent
WITH CHECK OPTION
ƒ WITH LOCAL CHECK OPTION
SN
VN
___
VI
CASCADED
•••
SI
S I-1
V I-1
R
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
LOCAL/CASCADED/___
•••
134
SQL
Änderbarkeit von Sichten
•
•
Annahmen:
ƒ Sicht SA mit dem die Sicht definierenden Prädikat VA wird aktualisiert
ƒ SI ist die höchste Sicht im Abstammungspfad von SA, die die Option
CASCADED besitzt
ƒ Oberhalb von SI tritt keine LOCAL
LOCAL-Bedingung
Bedingung auf
Aktualisierung von SA
ƒ als Prüfbedingung wird von SI aus an SA “vererbt”:
V = VI ∧ VI-1 ∧ . . . ∧ V1
ƒ 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
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
135
SQL
Änderbarkeit von Sichten
•
•
•
zusätzliche Annahmen:
ƒ Aktualisierte Sicht besitzt WITH CHECK OPTION
ƒ Default ist CASCADED
Aktualisierung von SA:
ƒ als
l Prüfbedingung
P üfb di
bei
b i Aktualisierungen
Akt li i
ergibt
ibt sich:
i h
V = VA ∧ VN ∧ . . . ∧ VI ∧ . . . ∧ V1
ƒ Zeilen können jetzt aufgrund von gültigen Einfüge- oder
Ä d
Änderungsoperationen
i
nicht
i h aus SA verschwinden
h i d
LOCAL hat eine undurchsichtige Semantik
ƒ wird hier nicht diskutiert
ƒ Empfehlung: nur Verwendung von CASCADED
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
136
SQL
Änderbarkeit von Sichten
•
S2 mit V1 ∧ V2
S1 mit V1 und CASCADED
R
Sichtenhierarchie:
R
R
R
S1
U1
S2
I1
I2
U2
S1
S1
S2
S2
U3
I3
•
Aktualisierungsoperationen in S2 (welche sind erlaubt?)
ƒ 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
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
137
SQL
Änderbarkeit von Sichten
•
Beispiel:
ƒ Tabelle Pers
ƒ Sicht1 auf Pers: AP1, mit Beruf = ‘Progr’ AND Gehalt < ’30K’
ƒ Sicht2 auf AP1: AP2, mit Gehalt > ‘20K’
AP2
Sichtdef.
Prädikat
> ‘20K’
1
___
CHECK-Optionen
2
3
4
___
CASC CASC
AP1
< ‘30K’
___
CASC
___
CASC
PERS
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
138
SQL
Änderbarkeit von Sichten
•
Beispiel:
Operationen
1
2
3
4
AP2: > 20K
-
-
CASC
CASC
AP1: < 30K
-
-
CASC
1. INSERT INTO
O AP2 ((PNR,, BERUF,
U , GEHALT,
G
, ANR))
VALUES ( 1234, ‘Progr’ , ‘25K’, ‘K55’)
2. INSERT INTO AP2 (PNR, BERUF, GEHALT, ANR)
VALUES ( 4711, ‘Progr’ , ‘15K’, ‘K55’)
3. UPDATE AP2
SET Gehalt = Gehalt + ‘10K’
WHERE ANR = ‘K55’
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
CASC
139
SQL
Zusammenfassung SQL
•
•
•
SQL-Anfragen
ƒ Mengenorientierte
Mengeno ientie te Spezifikation,
Spe ifikation verschiedene
e schiedene Typen
T pen von
on Anfragen
Anf agen
ƒ Vielfalt an Suchprädikaten
ƒ Auswahlmächtigkeit von SQL ist höher als die der Relationenalgebra.
ƒ Erklärungsmodell für die Anfrageauswertung: Festlegung der Semantik von
Anfragen mit Hilfe von Grundoperationen
ƒ Optimierung der Anfrageauswertung durch das DBS
Mengenorientierte Datenmanipulation
Datendefinition
ƒ CHECK-Bedingungen
CHECK B di
fü Wertebereiche,
für
W t b i h Attribute
Att ib t und
d Relationen
R l ti
ƒ Spezifikation des Überprüfungszeitpunktes
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
140
SQL
Zusammenfassung SQL
•
•
Kontrolle von Beziehungen
ƒ SQL erlaubt nur die Spezifikation von binären Beziehungen.
ƒ Referentielle Integrität von FS --> PS/SK wird stets gewährleistet.
ƒ Rolle von PRIMARY KEY
KEY, UNIQUE,
UNIQUE NOT NULL
ƒ Es ist nur eine eingeschränkte Nachbildung von Kardinalitätsrestriktionen
möglich; insbesondere kann nicht spezifiziert werden, dass „ein Vater Söhne
haben muss
muss“.
Wartung der referentiellen Integrität
ƒ SQL2/3 bietet reichhaltige Optionen für referentielle Aktionen
ƒ Es sind stets sichere Schemata anzustreben
ƒ Falls eine statische Schemaanalyse zu restriktiv für die Zulässigkeit
eines Schemas ist, muss für das g
gewünschte Schema eine
Laufzeitüberwachung der referentiellen Aktionen erfolgen.
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
141
SQL
Zusammenfassung SQL
•
•
•
Schemaevolution
ƒ Änderung/Erweiterung von Spalten, Tabellen, Integritätsbedingungen, ...
Indexstrukturen als B*-Bäume
ƒ mit und ohne Clusterbildung spezifizierbar
ƒ Balancierte Struktur unabhängig von Schlüsselmenge und
Einfügereihenfolge
ƒ dynamische
d
i h Reorganisation
R
i ti durch
d h Aufteilen
A ft il (Split)
(S lit) und
d Mischen
Mi h von Seiten
S it
ƒ direkter Schlüsselzugriff auf einen indexierten Satz
ƒ sortiert sequentieller Zugriff auf alle Sätze
(unterstützt Bereichsanfragen, Verbundoperation usw.
Sichtenkonzept
ƒ Erhöhung der Benutzerfreundlichkeit
ƒ Flexibler Datenschutz
ƒ Erhöhte Datenunabhängigkeit
ƒ Rekursive
R k i Anwendbarkeit
A
db k it
ƒ Eingeschränkte Aktualisierungsmöglichkeiten
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
142
SQL
Ergänzende Literatur zu diesem Kapitel
[[MSG99]]
[Me02]
Melton,, J.,, Simon,, A.R.,, Gray,
y, J.: SQL:
Q 1999 - Understanding
g
Relational Language Components, Morgan Kaufmann Series in
Data Management Systems, 1999.
Melton J.:
Melton,
J : Advanced SQL: 1999 - Understanding ObjectRelational and Other Advanced Features, Morgan Kaufmann
Series in Data Management Systems, 2002.
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
143