Integritätsbedingungen- · 2015-06-10 · 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.


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)


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)


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


FK

FK ... NOT NULL

FK ... UNIQUE

FK ... UNIQUE NOT NULL

(0,n)

(0,n)

(0,1)

(0,1)

(0,1)

(1,1)

(0,1)

(1,1)

1 1 bzw. N

Abbildung von N:1-‐Beziehungen in SQL §  ER-‐Diagramm:

(1:n)-‐Beziehung

§  Umsetzung ins Rela7onenmodell ABT (ABTNR ..., PERS (PNR ..., ... ANR ...,

PRIMARY KEY (ABTNR)) PRIMARY KEY (PNR), FOREIGN KEY (ANR) REFERENCES ABT)

§  Referenzgraph

§  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)!


ist_ beschäftigt_

in ABT PERS

[0,n] [0,1]

ABT PERS

referenzierte referenzierende Relation

ANR

Abbildung von symmetrischen 1:1-‐Beziehungen §  ER-‐Diagramm: Symmetrische (1:1)-‐Beziehung

§  Umsetzung in SQL 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


leitet ABT MGR [1,1] [1,1]

ABT MGR

MNR

MNR

Abbildung von N:M-‐Beziehungen §  ER-‐Diagramm: (n:m)-‐Beziehung

§  Umsetzung ins Rela7onenmodell PERS (PNR ..., PROJ (JNR ...,

… … PRIMARY KEY (PNR)) PRIMARY KEY (JNR)

MITARBEIT (PNR …,

JNR …, 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?


bearbeitet PERS PROJ [0,n] [0,m]

PERS PROJ

JNR

MITARBEIT PNR

Abbildung von rekursiven 1:N-‐Beziehungen §  ER-‐Diagramm: (1:n)-‐Beziehung als Selbstreferenz

§  Umsetzung ins Rela7onenmodell PERS (PNR ...,

MNR ..., ... 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.


hat_Mgr PERS

[0,n]

[0,1]

PERS MNR

Beispiel-‐Schema §  ER-‐Diagramm

§  Graphische Darstellung des Rela7onenschemas


Fachbereich

1,1

Prof Student Prüfung

ist-eingeschr.-

in gehört-zu ist-Dekan-von

0,N 0,N

0,1 1,1 1,1

0,N 0,M

FBNR FBNAME DEKAN FB

MATNR SNAME FBNR STUDBEG

STUDENT

PNR MATNR FACH PDATUM NOTE PRUEFUNG

PNR PNAME FBNR FACHGEBIET PROF

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.

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 Op1 Op2 Op3 COMMIT

IMM IMM IMM DEF

IMMEDIATE und DEFERRED §  Beispiel: neuer Fachbereich entsteht

•  INSERT INTO FB (FB13, ..., 1234, ...) •  INSERT INTO PROF(1234, ..., FB13, ...)

§  Bei zyklischen Referenzpfaden •  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 jede IB definiert werden

§  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)


FB PROF

PFK1 (FBNR)

FFK (DEKAN)

deferrability ::= [INITIALLY {DEFERRED | IMMEDIATE}] [ [NOT] DEFERRABLE ]

Ä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, ...) //FFK ist INITIALLY DEFERRED! INSERT INTO PROF(1234, ..., FB13, ...) //PFK1 ist wird geprüd! SET CONSTRAINTS FFK IMMEDIATE //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!


Referen7elle Integrität -‐ Mo7va7on §  Was passiert bei

DELETE FB9 ?


FB FBNR FBNAME DEKAN

FB9 FB5

WIRTSCHAFTSWISS INFORMATIK

4711 2223

PROF 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

STUDENT 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.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

FBNR FBNAME DEKAN FB

MATNR SNAME FBNR STUDBEG STUDENT

PNR MATNR FACH PDATUM NOTE

PRUEFUNG

PNR PNAME FBNR FACHGEBIET

PROF

SET DEFAULT RESTRICT

zuerst exmatrikulieren! (DELETE)

CASCADE

Referen7elle Integrität (RI) §  Fremdschlüsselbedingung: Zugehöriger PS (SK) muss exis7eren*

§  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.


FB STUDENT

referenzierte referenzierende Relation

FBNR

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)


Referen7elle Ak7onen

§  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


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 (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


Durchführung der Änderungsopera7onen §  Durchführung der referen7ellen Ak7onen (RA)

•  Benutzeropera7onen (Op) sind in SQL immer atomar •  mengenorien7ertes oder satzorien7ertes (in-‐flight) Verarbeitungsmodell

•  IMMEDIATE-‐Bedingungen müssen erfüllt sein an Anweisungsgrenzen (➥ mengenorien7erte Änderung)

•  Satzorien7ertes Modell darf nur genutzt werden, wenn Äquivalenz zum mengenorien7erten Modell garan7ert ist -  Beipiel: PERS.MGR à PERS.PNR (RESTRICT) Lösche alle Angestellten aus Abteilung K55, inklusive Manager


Op

t1 t2 tn …

RA‘s RA

… t1 t2

Op

tn

RA RA

Auswirkungen referen7eller Ak7onen §  Isolierte Betrachtung von

STUDENT – FB §  Beispiel-‐DB

§  Opera7onen

•  Lösche FB (FBNR=FB5) •  Ändere FB ((FBNR=FB9) → (FBNR=FB10))

§  Referen7elle Ak7onen •  DC, DSN, DSD, DR, DNA •  UC, USN, USD, UR, UNA

§  Eindeu7gkeit der Opera7onen •  Auswirkungen von ref. Ak7onen sind eindeu7g!


STUDENT FB FBNR

STUDENT MATNR SNAME FBNR

123 766 225 332 654 711 226 302

COY MÜLLER ABEL SCHULZE

FB 9 FB 5 FB 5 FB 9

FB FBNR FBNAME

FB9 FB5

WIRTSCHAFTSWISS INFORMATIK

DC DR DSN DSD DNA

del x -‐ FBX x

del x -‐ FBX x

UC UR USN USD UNA

FB10 x -‐ FBX x

FB10 x -‐ FBX x

Auswirkungen referen7eller Ak7onen (2) §  Betrachtung von

STUDENT – PRUEFUNG – PROF §  Beispiel-‐DB

§  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


PROF STUDENT

PNR PRUEFUNG MATNR

PROF PNR PNAME

1234 4711

HÄRDER MÜLLER

STUDENT MATNR SNAME

123 766 654 711

COY ABEL

PRÜFUNG PNR MATNR FACH

4711 1234 12344711

123 766 654 711 123 766 654 711

OR DV DV OR

Auswirkungen referen7eller Ak7onen (3) §  Vollständiges Beispiel: Lösche FB (FBNR=FB9)

•  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


PROF STUDENT

PNR

PRUEFUNG

FB

MATNR DC DC

DC DC FBNR FBNR

erst links erst rechts

-‐  Löschen in FB -‐  Löschen in PROF -‐  Löschen in PRUEFUNG -‐  Löschen in STUDENT -‐  Löschen in PRUEFUNG

-‐  Löschen in FB -‐  Löschen in STUDENT -‐  Löschen in PRUEFUNG -‐  Löschen in PROF -‐  Löschen in PRUEFUNG

Auswirkungen referen7eller Ak7onen (4) §  Modifika7on des Schemas §  Lösche FB (FBNR=FB9)

•  Es können reihenfolgenabhängige Ergebnisse audreten! •  Die Reihenfolgenabhängigkeit ist hier wertabhängig •  Schema ist nicht sicher


PROF STUDENT

PRUEFUNG

FB

DC DR

DC DC

erst rechts

-‐  Löschen in FB -‐  Löschen in STUDENT -‐  Löschen in PRUEFUNG Wenn ein gerade gelöschter Student eine Prüfung abgelegt haee → Rücksetzen sonst: -‐  Löschen in PROF -‐  Löschen in PRUEFUNG

erst links

-‐  Löschen in FB -‐  Löschen in PROF -‐  Löschen in PRUEFUNG -‐  Löschen in STUDENT -‐  Löschen in PRUEFUNG Wenn ein Student bei einem FB-‐fremden Professor geprüd wurde → Rücksetzen

Auswirkungen referen7eller Ak7onen (5) §  Weitere Modifika7on des Schemas §  Lösche FB (FBNR=FB9)

•  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


PROF STUDENT

PRUEFUNG

FB

DC DNA

DC DC

erst links

-‐  Löschen in FB -‐  Löschen in PROF -‐  Löschen in PRUEFUNG -‐  Löschen in STUDENT Test, ob es noch offene Referenzen in PRUEFUNG auf gelöschte Studenten gibt; wenn ja → Rücksetzen

erst rechts

-‐  Löschen in FB -‐  Löschen in STUDENT -‐  Löschen in PROF -‐  Löschen in PRUEFUNG Test, ob es noch offene Referenzen in PRUEFUNG auf gelöschte Studenten gibt; wenn ja → Rücksetzen

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)  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.

b)  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.


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.

Integritätsbedingungen- · 2015-06-10 · Integritätsbedingungen-!...

Documents

Transcript of Integritätsbedingungen- · 2015-06-10 · Integritätsbedingungen-!...