Integritätsbedingungen

Integritätsbedingungen §  Ziel: Verankerung von seman7schen Integritätsbedingungen im DB-­‐
Schema •  Seman7k der Mini-­‐Welt möglichst vollständig erfassen •  Integritätsbedingungen beschreiben akzeptable DB-­‐Zustände -  Änderungen werden zurückgewiesen, wenn sie Integrität verletzen •  effiziente Integritätskontrolle durch das DBMS -  Konsistenzgaran7e, auch für interak7ve Änderungen -  vereinfachte Anwendungsentwicklung -  leichte Änderbarkeit von Integritätsbedingungen §  Überblick •  Startpunkt: Verfeinerte Abbildung von ER-­‐Schemata -  PRIMARY KEY, FOREIGN KEY ... REFERENCES, UNIQUE, NOT NULL •  Prüfzeitpunkt (IMMEDIATE, DEFERRED) •  Referen7elle Constraints und Ak7onen •  CHECK-­‐Constraints und Asser7ons Informa7onssysteme 2015 Kapitel 6. Sichten, Integrität und Zugriffskontrolle 20 Arten von Integritätsbedingungen §  Integritätsbedingungen abhängig vom Rela7onenmodell •  Primärschlüsseleigenschad •  Referen7elle Integrität für Fremdschlüssel •  Deni7onsbereiche (Domains) für Aeribute §  Reichweite der Bedingung •  Aeributwert-­‐Bedingungen (z.B. Geburtsjahr > 1900) •  Satzbedingungen (z.B. Geburtsdatum < Einstellungsdatum) •  Satztyp-­‐Bedingungen (z.B. Eindeu7gkeit von Aeributwerten) •  Satztypübergreifende Bedingungen (z.B. referen7elle Integrität zwischen verschiedenen Tabellen) §  Klar, je geringer die Reichweite, desto einfacher lassen sich Bedingungen überprüfen. Informa7onssysteme 2015 Kapitel 6. Sichten, Integrität und Zugriffskontrolle 21 Arten von Integritätsbedingungen (2) §  Sta7sche vs. dynamische Bedingungen •  Sta7sche Bedingungen (Zustandsbedingungen): beschränken zulässige DB-­‐Zustände (z.B. Gehalt < 500000) •  Dynamische Integritätsbedingungen (Übergangsbedingungen): zulässige Zustandsübergänge (z.B. Gehalt darf nicht kleiner werden) •  Variante dynamischer Integritätsbedingungen: temporale IBs für längerfris7g §  Zeitpunkt der Überprüjarkeit: unverzögert vs. verzögert •  Verzögerte Bedingungen lassen sich nur durch eine Folge von Änderungen erfüllen (typisch: mehrere Sätze, mehrere Tabellen) und •  Benö7gen Transak7onsschutz (als zusammengehörige Änderungssequenzen) Informa7onssysteme 2015 Kapitel 6. Sichten, Integrität und Zugriffskontrolle 22 Eindeu7gkeit, Fremdschlüssel und Verbot von Nullwerten §  Bekannt aus Kapitel 5: Spezifika7on grundlegender Integritätsbedingungen (Constraints) •  Verbot von Nullwerten (NOT NULL) •  Schlüsselkandidaten (UNIQUE bzw. PRIMARY KEY) •  Fremdschlüssel (FOREIGN-­‐KEY ... REFERENCES) §  Beispiel: CREATE TABLE PERS (PNR INT PRIMARY KEY, BERUF CHAR (30), PNAME CHAR (30) NOT NULL, PALTER ALTER, (* siehe Domaindefini7on *) MGR INT REFERENCES PERS, ANR ABTNR NOT NULL, (* Domaindef. *) W_ORT CHAR (25) DEFAULT ' ', GEHALT DEC (9,2) DEFAULT 0,00, FOREIGN KEY (ANR) REFERENCES ABT) Informa7onssysteme 2015 Kapitel 6. Sichten, Integrität und Zugriffskontrolle 23 Abbildung von ER-­‐Schemata in SQL §  Abbildung folgt dem in Kapitel 3 (und 4) vorgestellten Verfahren •  Erzeugen von Tabellen für En77es und (N:M)-­‐Rela7onships •  Defini7on von geeigneten Primärschlüsseln (PRIMARY KEY) •  Defini7on von Fremdschlüsseln (FOREIGN-­‐KEY) -  direkte Abbildung von 1:1, 1:N -­‐ Beziehungen -  FOREIGN KEY ... UNIQUE zur Abbildung von 1:1-­‐Beziehung 1
1 bzw. N
(0,n)
(0,1)
FK
(0,n)
(1,1)
FK ... NOT NULL
(0,1)
(0,1)
FK ... UNIQUE
(0,1)
(1,1)
FK ... UNIQUE NOT NULL
Informa7onssysteme 2015 Kapitel 6. Sichten, Integrität und Zugriffskontrolle 24 Abbildung von N:1-­‐Beziehungen in SQL §  ER-­‐Diagramm: ist_
[0,n]
[0,1]
beschäftigt_
ABT
PERS
(1:n)-­‐Beziehung in
§  Umsetzung ins Rela7onenmodell ABT (ABTNR ..., PERS (PNR ..., ... ANR ..., PRIMARY KEY (ABTNR)) PRIMARY KEY (PNR), FOREIGN KEY (ANR) REFERENCES ABT) §  Referenzgraph ANR
ABT
PERS
Relation
referenzierte
referenzierende
§  Zusätzliche Einschränkungen •  Jeder Angestellte (PERS) muss in einer Abteilung beschädigt sein ([1,1]). ➥ PERS.ANR ... NOT NULL •  Jeder Abteilung (ABT: [0,1]) darf höchstens einen Angestellten beschädigen. ➥ PERS.ANR ... UNIQUE §  Bemerkung: In SQL kann als Teil der FOREIGN KEY-­‐Klausel nicht spezifiziert werden, dass ein jedes Tupel referenziert werden muss (z. B. [1,n] für ABT)! Informa7onssysteme 2015 Kapitel 6. Sichten, Integrität und Zugriffskontrolle 25 Abbildung von symmetrischen 1:1-­‐Beziehungen §  ER-­‐Diagramm: Symmetrische (1:1)-­‐Beziehung ABT
[1,1]
leitet
[1,1]
MGR
MNR
§  Umsetzung in SQL ABT
MGR
MNR
ABT (ANR ..., MGR (MNR ..., MNR ... UNIQUE NOT NULL, ... ... PRIMARY KEY (ANR), PRIMARY KEY (MNR), FOREIGN KEY (MNR) FOREIGN KEY (MNR) REFERENCES MGR) REFERENCES ABT(MNR)) •  Die Nutzung des MNR-­‐Aeributes für zwei FS-­‐Beziehungen (+ UNIQUE, NOT NULL) gewährleistet hier die Einhaltung von beiden [1, 1]-­‐
Kardinalitäten •  Der Fall ([0,1], [0,1]) ist so nicht darstellbar -  Alterna7ve: weiteres Aeribut in MGR (z.B. SVNR) als Primärschlüssel Informa7onssysteme 2015 Kapitel 6. Sichten, Integrität und Zugriffskontrolle 26 Abbildung von N:M-­‐Beziehungen §  ER-­‐Diagramm: (n:m)-­‐Beziehung [0,n]
[0,m]
bearbeitet
PERS
PROJ
§  Umsetzung ins Rela7onenmodell PERS (PNR ..., PROJ (JNR ..., … … PRIMARY KEY (PNR)) PRIMARY KEY (JNR) PERS
PROJ
MITARBEIT (PNR …, PNR
JNR
JNR …, MITARBEIT
PRIMARY KEY (PNR, JNR), FOREIGN KEY (PNR) REFERENCES PERS, FOREIGN KEY (JNR) REFERENCES PROJ) •  Diese Standardlösung erzwingt „Existenzabhängigkeit“ von MITARBEIT. Soll dies vermieden werden, dürfen die Fremdschlüssel von MITARBEIT nicht als Teil des Primärschlüssels spezifiziert werden. •  Ist die Realisierung von [1,n] oder [1,m] bei der Abbildung der (n:m)-­‐
Beziehung möglich? Informa7onssysteme 2015 Kapitel 6. Sichten, Integrität und Zugriffskontrolle 27 Abbildung von rekursiven 1:N-­‐Beziehungen §  ER-­‐Diagramm: (1:n)-­‐Beziehung als Selbstreferenz [0,n]
hat_Mgr
PERS
[0,1]
§  Umsetzung ins Rela7onenmodell PERS (PNR ..., MNR
MNR ..., PERS
... PRIMARY KEY (PNR), FOREIGN KEY (MNR) REFERENCES PERS (PNR)) •  Lösung erlaubt Darstellung der Personal-­‐Hierarchie eines Unternehmens. Die referen7elle Beziehung stellt hier eine par7elle Funk7on 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 interpre7ert werden. Dadurch treten jedoch Referenzzyklen auf, was die Frageauswertung und die Konsistenzprüfung erschwert. Informa7onssysteme 2015 Kapitel 6. Sichten, Integrität und Zugriffskontrolle 28 Beispiel-­‐Schema §  ER-­‐Diagramm Fachbereich
0,N
1,1
ist-Dekanvon
0,1
Prof
§  Graphische Darstellung des Rela7onenschemas 0,N
isteingeschr.in
gehört-zu
1,1
1,1
Prüfung
0,N
0,M
Student
FB
FBNR FBNAME DEKAN
STUDENT
MATNR SNAME FBNR STUDBEG
PROF
PNR PNAME FBNR FACHGEBIET
PRUEFUNG
PNR MATNR FACH PDATUM NOTE
Informa7onssysteme 2015 Kapitel 6. Sichten, Integrität und Zugriffskontrolle 29 Spezifika7on des rela7onalen DB-­‐Schemas (nach SQL2) § 
§ 
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 Rela7onen CREATE TABLE FB ( FBNR FACHBEREICHSNUMMER PRIMARY KEY, FBNAME FACHBEREICHSNAME
UNIQUE, DEKAN PERSONALNUMMER UNIQUE NOT NULL, CONSTRAINT FFK FOREIGN KEY (DEKAN) REFERENCES PROF (PNR)) 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)) CREATE TABLE STUDENT ( MATNR
MATRIKELNUMMER PRIMARY KEY, SNAME
NAMEN
NOT NULL, FBNR FACHBEREICHSNUMMER NOT NULL, STUDBEG DATUM, CONSTRAINT SFK FOREIGN KEY (FBNR) REFERENCES FB (FBNR)) CREATE TABLE PRUEFUNG ( PNR PERSONALNUMMER, MATNR
MATRIKELNUMMER, FACH FACHBEZEICHNUNG, PDATUM DATUM
NOT NULL, NOTE NOTEN
NOT NULL, PRIMARY KEY (PNR, MATNR), CONSTRAINT PR1FK FOREIGN KEY (PNR) REFERENCES PROF (PNR), CONSTRAINT PR2FK FOREIGN KEY (MATNR) REFERENCES STUDENT (MATNR)) // Es wird hier 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.
Informa7onssysteme 2015 Kapitel 6. Sichten, Integrität und Zugriffskontrolle 30 Prüfung von Integritätsbedingungen §  Od sollen Integritätsbedingungen (IBen) schon direkt nach Abschluss einer Änderungsopera7onen erfüllt sein •  Prüfzeit IMMEDIATE in SQL (ist auch der Default) •  Falls IB nach Abschluß einer DML-­‐Opera7on verletzt, scheitert die DML-­‐Opera7on vollständig (d.h., hat keine Auswirkungen auf die DB) §  Manchmal (z.B. bei tupelübergreifenden IBen) kann ein konsistenter DB-­‐
Zustand erst nach mehreren DML-­‐Befehlen erreicht werden •  Prüfzeit DEFERRED in SQL §  Transak7onskonzept (à ACID) fordert Erhaltung der sem. Integrität (Konsistenz) durch jede Transak7on è spätester Prüfzeitpunkt: Ende der Transak7on (Commit) •  falls IBen nicht erfüllt, dann scheitert die ganze Transak7on! BOT
Informa7onssysteme 2015 Op1
Op2
Op3
COMMIT
IMM
IMM
IMM
DEF
Kapitel 6. Sichten, Integrität und Zugriffskontrolle 31 IMMEDIATE und DEFERRED §  Beispiel: neuer Fachbereich entsteht •  INSERT INTO FB (FB13, ..., 1234, ...) FFK (DEKAN)
•  INSERT INTO PROF(1234, ..., FB13, ...) PROF
FB
§  Bei zyklischen Referenzpfaden PFK1 (FBNR)
•  wenigstens ein Fremdschlüssel im Zyklus muss „NULL“ erlauben oder •  Prüfung der referen7ellen Integrität muss für mindestens einen FK verzögert (DEFERRED) werden (z. B. bei COMMIT) §  Prüfzeitpunkt (deferrability) kann für deferrability
::=
[INITIALLY {DEFERRED | IMMEDIATE}]
jede IB definiert werden [ [NOT] DEFERRABLE ]
§  Im Beispiel: CREATE TABLE FB ( FBNR FACHBEREICHSNUMMER PRIMARY KEY, FBNAME FACHBEREICHSNAME UNIQUE, DEKAN PERSONALNUMMER UNIQUE NOT NULL, CONSTRAINT FFK FOREIGN KEY (DEKAN) REFERENCES PROF (PNR) INITIALLY DEFERRED) Informa7onssysteme 2015 Kapitel 6. Sichten, Integrität und Zugriffskontrolle 32 Ändern/Setzen des Prüfzeitpunkts §  SET CONSTRAINTS { constr. ... | ALL } IMMEDIATE | DEFERRED •  Setzt in der aktuellen Transak7on Prüfzeitpunkt für benannte bzw. alle IBen -  SET CONSTRAINTS ALL DEFERRED hat nur Auswirkungen auf IBen, die DEFERRABLE sind •  SET CONSTRAINTS ... IMMEDIATE bewirkt die sofor7ge Überprüfung der genannten IBen -  Beispiel: INSERT INTO FB (FB13, ..., 1234, ...) INSERT INTO PROF(1234, ..., FB13, ...) SET CONSTRAINTS FFK IMMEDIATE //FFK ist INITIALLY DEFERRED! //PFK1 ist wird geprüd! //FFK wird geprüd! •  SET CONSTRAINTS schlägt fehl, falls IB verletzt! -  TA scheitert (noch) nicht, könnte DB-­‐Zustand noch konsistent machen! §  COMMIT •  impliziert SET CONSTRAINTS ALL IMMEDIATE •  TA scheitert (wird zurückgesetzt), falls IB verletzt! Informa7onssysteme 2015 Kapitel 6. Sichten, Integrität und Zugriffskontrolle 33 Referen7elle Integrität -­‐ Mo7va7on FB
FBNR FBNAME DEKAN
§  Was passiert bei DELETE FB9 ? SET DEFAULT
RESTRICT
STUDENT
MATNR SNAME FBNR STUDBEG
PROF
FB PROF FBNR FBNAME DEKAN FB9 FB5 WIRTSCHAFTSWISS INFORMATIK 4711 2223 PNR PNAME FBNR FACHGEBIET
PRUEFUNG
PNR MATNR FACH PDATUM NOTE
PNR PNAME FBNR FACHGEB 1234 5678 4711 6780 HÄRDER WEDEKIND MÜLLER NEHMER FB 5 FB 9 FB 9 FB 5 DATENBANKSYSTEME INFORMATIONSSYSTEME OPERATIONS RESEARCH BETRIEBSSYSTEME zuerst exmatrikulieren! (DELETE)
STUDENT MATNR SNAME FBNR 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 Informa7onssysteme 2015 CASCADE
STUDBEG 1.10.05 15.04.02 15.10.04 1.10.05 23.10.05 1.04.07 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.07 16.01.08 17.04.08 17.04.07 19.09.07 15.10.07 23.10.07 4 3 2 4 2 1 3 Kapitel 6. Sichten, Integrität und Zugriffskontrolle 34 Referen7elle Integrität (RI) §  Fremdschlüsselbedingung: Zugehöriger PS (SK) muss exis7eren* FB
referenzierte
FBNR
Relation
STUDENT
referenzierende
§  Welche Opera7onen führen poten7ell zu RI-­‐Verletzungen? •  Opera7onen in der referenzierenden Rela7on (enthält FS) -  Einfügen eines Tupels -  Ändern des FS-­‐Wertes in einem Tupel -  Löschen eines Tupels ist unkri7sch (warum?) •  Opera7onen in der referenzierten Rela7on (enthält PS/SK) -  Löschen eines Tupels -  Ändern des PS/SK-­‐Wertes -  Einfügen eine Tupels ist unkri7sch (warum?) (*) Achtung: falls FS zusammengesetzt ist, kann in SQL zusätzlich definiert werden, wie Nullwerte für Teile des Schlüssels interpreEert werden (MATCH-­‐
Klausel). Darauf wird hier nicht weiter eingegangen. Informa7onssysteme 2015 Kapitel 6. Sichten, Integrität und Zugriffskontrolle 35 Wartung der referen7ellen Integrität (RI) §  Welche Maßnahmen sind möglich/sinnvoll? •  Einfügen/Ändern in der referenzierenden Tabelle -  Prüfung, ob in der referenzierten Tabelle ein Tupel mit einem PS/SK-­‐
Wert gleich dem FS-­‐Wert des einzufügenden/zu ändernden Tupels exis7ert. -  Opera7on wird abgewiesen, falls ein solches Tupel nicht exis7ert •  Löschen/Ändern in der referenzierten Tabelle 1.  Opera7on verbieten, falls es noch referenzierende Tupel gibt 2.  Löschen bzw. Ändern der FS in allen referenzierenden Tupeln 3.  Erhalten der referenzierenden Tupel durch Setzen von Default-­‐ bzw. NULL-­‐Werten für FS (falls das erlaubt ist) §  SQL unterstützt referen7elle Ak7onen, um bei Löschen/Ändern in der referenzierten Tabelle die gewünschte Maßnahme festzulegen •  Erlaubt Standardmaßnahmen zum Vermeiden von RI-­‐
Verletzungen durch das DBMS! (Ak7ves Verhalten) Informa7onssysteme 2015 Kapitel 6. Sichten, Integrität und Zugriffskontrolle 36 Referen7elle Ak7onen references-def ::=
REFERENCES base-table [(column-commalist)]
[ON DELETE referential-action]
[ON UPDATE referential-action]
referential-action
::= NO ACTION | CASCADE | SET DEFAULT | SET NULL | RESTRICT
§  Referen7elle Ak7onen (referenEal acEons) •  für jeden Fremdschlüssel (FS) separat festzulegen •  Angabe der gewünschten Ak7onen bei Löschen/Ändern von Tupeln in der referenzierten Rela7on -  Löschregel: ON DELETE ... -  Änderungsregel: ON UPDATE ... •  unterschiedliche Maßnahmen für DELETE und UPDATE möglich §  Durchführung von referen7ellen Ak7onen •  immer sofort bei der Ausführung der Änderungsopera7on •  vor der Prüfung der RI-­‐Bedingung •  unabhängig vom Prüfzeitpunkt (IMMEDIATE/DEFERRED)! •  verursacht ggf. weitere referen7elle Ak7onen Informa7onssysteme 2015 Kapitel 6. Sichten, Integrität und Zugriffskontrolle 37 Referen7elle Ak7onen (2) §  Bedeutung der einzelnen Ak7onen •  NO ACTION (Defaulteinstellung) -­‐ Keine referen7elle Ak7on -  Prüfung der RI erfolgt zum definierten Zeitpunkt (evtl. DEFERRED), nachdem die referen7ellen Ak7onen aller IBen ausgeführt wurden •  CASCADE -­‐ Opera7on „kaskadiert“ zu allen zugehörigen Sätzen è Existenzabhängigkeit (z.B. für schwache En77es) -  DELETE CASCADE: referenzierende Tupel werden gelöscht -  UPDATE CASCADE: FS in referenzierenden Tupeln wird geändert •  SET NULL -­‐ FS wird in zugehörigen Sätzen auf „NULL“ gesetzt •  SET DEFAULT -­‐ FS wird in den zugehörigen Sätzen auf den (benutzerdefinierten) Default-­‐Wert gesetzt •  RESTRICT-­‐ Opera7on wird nur ausgeführt, wenn keine zugehörigen Sätze (FS-­‐Werte) vorhanden sind -  ist restrik7ver als NO ACTION, da Opera7on sofort zurückgewiesen wird §  Referen7elle Ak7on ersetzt nicht generell die Prüfung der RI! •  Prüfung bei SET DEFAULT und NO ACTION erforderlich Informa7onssysteme 2015 Kapitel 6. Sichten, Integrität und Zugriffskontrolle 38 Durchführung der Änderungsopera7onen §  Durchführung der referen7ellen Ak7onen (RA) •  Benutzeropera7onen (Op) sind in SQL immer atomar •  mengenorien7ertes oder satzorien7ertes (in-­‐flight) Verarbeitungsmodell Op
Op
t
t
t
t
t … t
…
RA
RA
RA
RA‘s
•  IMMEDIATE-­‐Bedingungen müssen erfüllt sein an Anweisungsgrenzen (➥ mengenorien7erte Änderung) •  Satzorien7ertes Modell darf nur genutzt werden, wenn Äquivalenz zum mengenorien7erten Modell garan7ert ist 1
1
2
2
n
n
-  Beipiel: PERS.MGR à PERS.PNR (RESTRICT) Lösche alle Angestellten aus Abteilung K55, inklusive Manager Informa7onssysteme 2015 Kapitel 6. Sichten, Integrität und Zugriffskontrolle 39 Auswirkungen referen7eller Ak7onen §  Isolierte Betrachtung von FBNR
STUDENT
FB
STUDENT – FB §  Beispiel-­‐DB FB FBNR FBNAME STUDENT MATNR SNAME 123 766 COY FB9 WIRTSCHAFTSWISS 225 332 MÜLLER FB5 INFORMATIK 654 711 ABEL §  Opera7onen 226 302 SCHULZE •  Lösche FB (FBNR=FB5) DC DR DSN DSD •  Ändere FB del x -­‐ FBX ((FBNR=FB9) → (FBNR=FB10)) del x -­‐ FBX §  Referen7elle Ak7onen UC UR USN USD •  DC, DSN, DSD, DR, DNA FB10 x -­‐ FBX •  UC, USN, USD, UR, UNA FB10 x -­‐ FBX §  Eindeu7gkeit der Opera7onen •  Auswirkungen von ref. Ak7onen sind eindeu7g! Informa7onssysteme 2015 Kapitel 6. Sichten, Integrität und Zugriffskontrolle FBNR FB 9 FB 5 FB 5 FB 9 DNA x x UNA x x 40 Auswirkungen referen7eller Ak7onen (2) §  Betrachtung von PROF
STUDENT
STUDENT – PRUEFUNG – PROF PNR
MATNR
PRUEFUNG
§  Beispiel-­‐DB PROF PNR PNAME STUDENT MATNR SNAME PRÜFUNG PNR MATNR FACH 1234 HÄRDER 4711 123 766 OR 123 766 COY 4711 MÜLLER 1234 654 7
11 DV 654 711 ABEL 12344 123 766 DV 711 654 711 OR §  Einsatz nicht betrachtet für •  USN, DSN → Schlüsselverletzung •  USD, DSD → ggf. Mehrdeu7gkeit •  UNA, DNA → Wirkung iden7sch mit UR, DR §  Auswirkungen von Aktualisierungsopera7onen •  Löschen/Ändern von PROF betri‡ PROF-­‐PRUEFUNG, aber nicht STUDENT-­‐PRÜFUNG; analog für Löschen/Ändern von STUDENT •  Verträglichkeit der verbleibenden referen7ellen Ak7onen (hier CASCADE, RESTRICT) Ø  Unabhängige referen7elle Beziehungen können unabhängig definiert und gewartet werden Informa7onssysteme 2015 Kapitel 6. Sichten, Integrität und Zugriffskontrolle 41 Auswirkungen referen7eller Ak7onen (3) §  Vollständiges Beispiel: Lösche FB (FBNR=FB9) FB
DC
DC
erst links erst rechts FBNR
FBNR
-­‐  Löschen in FB -­‐  Löschen in FB -­‐  Löschen in PROF -­‐  Löschen in STUDENT PROF
STUDENT
-­‐  Löschen in PRUEFUNG -­‐  Löschen in PRUEFUNG -­‐  Löschen in STUDENT -­‐  Löschen in PROF PNR
MATNR
-­‐ 
L
öschen i
n P
RUEFUNG -­‐  Löschen in PRUEFUNG DC
DC
PRUEFUNG
•  Ergebnis der Opera7on unabhängig von der Reihenfolge der referen7ellen Ak7onen •  Eindeu7gkeit des erreichten DB-­‐Zustandes §  Was heißt Unabhängigkeit der referen7ellen Beziehungen? •  Es sind mehrere Kombina7onen von referen7ellen Ak7onen möglich: z. B. DSD, UC oder DC, USN •  Eindeu7gkeit bei allen Aktualisierungsopera7onen ➥ sicheres Schema Informa7onssysteme 2015 Kapitel 6. Sichten, Integrität und Zugriffskontrolle 42 Auswirkungen referen7eller Ak7onen (4) §  Modifika7on des Schemas §  Lösche FB (FBNR=FB9) erst links erst rechts FB
DC
DC
-­‐  Löschen in FB -­‐  Löschen in FB -­‐  Löschen in PROF -­‐  Löschen in STUDENT -­‐  Löschen in PRUEFUNG -­‐  Löschen in PRUEFUNG PROF
STUDENT
-­‐  Löschen in STUDENT Wenn ein gerade gelöschter Student -­‐  Löschen in PRUEFUNG eine Prüfung abgelegt haee Wenn ein Student bei einem FB-­‐
→ Rücksetzen DR
DC
fremden Professor geprüd wurde sonst: PRUEFUNG
→ Rücksetzen -­‐  Löschen in PROF -­‐  Löschen in PRUEFUNG •  Es können reihenfolgenabhängige Ergebnisse audreten! •  Die Reihenfolgenabhängigkeit ist hier wertabhängig •  Schema ist nicht sicher Informa7onssysteme 2015 Kapitel 6. Sichten, Integrität und Zugriffskontrolle 43 Auswirkungen referen7eller Ak7onen (5) §  Weitere Modifika7on des Schemas §  Lösche FB (FBNR=FB9) erst links erst rechts FB
DC
-­‐  Löschen in FB -­‐  Löschen in FB DC
-­‐  Löschen in PROF -­‐  Löschen in STUDENT -­‐  Löschen in PRUEFUNG -­‐  Löschen in PROF PROF
STUDENT
-­‐  Löschen in STUDENT -­‐  Löschen in PRUEFUNG Test, ob es noch offene Referenzen Test, ob es noch offene Referenzen in PRUEFUNG auf gelöschte in PRUEFUNG auf gelöschte DNA
DC
Studenten gibt; wenn ja → Studenten gibt; wenn ja → PRUEFUNG
Rücksetzen Rücksetzen •  Bei der NA-­‐Op7on wird der explizite Test der referenzierenden Rela7on ans Ende der Opera7on verschoben. Eine Verletzung der referen7ellen Beziehung führt zum Rücksetzen. •  Schema ist immer sicher Informa7onssysteme 2015 Kapitel 6. Sichten, Integrität und Zugriffskontrolle 44 Eindeu7gkeit referen7eller Ak7onen §  Aufgabe: Verhinderung von mehrdeu7gen DB-­‐Opera7onen §  Maßnahmen •  Sta7sche Schemaanalyse zur Feststellung sicherer DB-­‐Schemata -  nur bei einfach strukturierten Schemata effek7v -  bei wertabhängigen Konflikten zu restrik7v (konflikeräch7ge Schemata) -  Hohe Komplexität der Analysealgorithmen •  Dynamische Überwachung der Modifika7onsopera7onen -  hoher Laufzeitaufwand §  Vorgehensweise 1.  Falls Sicherheit eines Schemas festgestellt werden kann, ist keine Laufzeitüberwachung erforderlich 2.  Alterna7ve Möglichkeiten zur Behandlung konflikeräch7ger Schemata a) 
b) 
Sie werden verboten: i. 
Sta7sche Schemaanalyse kann Sicherheit eines Schemas nicht feststellen ii. 
Dabei sind ggf. pessimis7sche Annahmen zu treffen, je nachdem, ob bei der Analyse nur Rela7onen oder auch ihre Aeribute (Aeributkonflikte) betrachtet werden. Sie werden erlaubt (in SQL:1999 so vorgeschrieben): i. 
Die referen7ellen Ak7onen werden bei jeder Opera7on dynamisch überwacht. ii. 
Falls ein Konflikt erkannt wird, wird die Opera7on zurückgesetzt. Informa7onssysteme 2015 Kapitel 6. Sichten, Integrität und Zugriffskontrolle 45 Spezifika7on des rela7onalen DB-­‐Schemas (erweitert) § 
§ 
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 Rela7onen CREATE TABLE FB ( FBNR FACHBEREICHSNUMMER PRIMARY KEY, FBNAME FACHBEREICHSNAME
UNIQUE, DEKAN PERSONALNUMMER UNIQUE NOT NULL, CONSTRAINT FFK FOREIGN KEY (DEKAN) REFERENCES PROF (PNR) ON UPDATE CASCADE ON DELETE NO ACTION INITIALLY DEFERRED) 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) CREATE TABLE STUDENT ( MATNR
MATRIKELNUMMER PRIMARY KEY, SNAME
NAMEN
NOT NULL, FBNR FACHBEREICHSNUMMER NOT NULL, STUDBEG DATUM, CONSTRAINT SFK FOREIGN KEY (FBNR) REFERENCES FB (FBNR)
ON UPDATE CASCADE
ON DELETE NO ACTION) CREATE TABLE PRUEFUNG ( PNR PERSONALNUMMER, MATNR
MATRIKELNUMMER, FACH FACHBEZEICHNUNG, PDATUM DATUM
NOT NULL, NOTE NOTEN
NOT NULL, 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) // Es wird hier 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.
Informa7onssysteme 2015 Kapitel 6. Sichten, Integrität und Zugriffskontrolle 46