Folien



(letzte Synchronisation der PDF-Präsentation: 02.03.2017)

Falls keine PDF-Präsentation zu sehen ist, klicken Sie zum Download hier: Direktdownload PDF-Präsentation

Datenkonsistenz, Datenintegrität

Daten müssen sich in einem logisch korrekten, widerspruchsfreien Zustand befinden. Dann sind die Daten "integer" oder "konsistent".

Beispiel 1: In der folgenden Tabelle sind zwei verschiedene Schreibweisen eines Ortes zu ein und derselben Postleitzahl angegeben; der Arbeitgeber "Bäckerei Zimmermann" ist unterschiedlich erfasst; es fehlt ein Primärschlüssel, d.h. der dritte Datensatz kann nicht eindeutig identiiziert werden.

Beispiel für eine inkonsistente Datentabelle

Beispiel 2: Welche Inkonsistenzen sind in dieser Datenbank zu finden? Beispiel für inkonsistente Tabellen (mit Fremdschlüssel)

Einige Fremdschlüssel weisen ins Nichts: postleitzahl 79312 hat keine Entsprechung in orte, es fehlt eine Tabelle arbeitgeber, auf die die Fremdschlüssel arbeitgeberFK verweisen können.

Integritätsregeln

Damit ein Datenbestand konsistent ist, müssen vier Integritätsbedingungen erfüllt sein:

  • Bereichsintegrität: Attribute sind nur gültig, wenn sie einen bestimmten Wertebereich haben.
  • Entitätsintegrität: Jeder Datensatz ist eindeutig definiert.
  • Referentielle Integrität: Beziehungen zwischen Tabellen müssen synchronisiert bleiben.
  • Benutzerdefinierte Integrität: Sonstige vom Benutzer festgelegte Regeln (z.B.: Datum darf nicht vor 01.01.2000 liegen)

Interessant für uns ist v.a. die referentielle Integrität, siehe unten. Bereichsintegrität und Entitätsintegrität ist für uns nur theoretisch interessant, da das RDBMS i.d.R. dafür sorgt, dass diese Regeln eingehalten werden (versuchen Sie mal, als Geburtsdatum "hihihi" einzugeben).

COMMIT / ROLLBACK

Eine Datenbank muss sich VOR einer Transaktion in einem konsistenten Zustand befinden, danach aber ebenfalls. Nun kann es gerade bei Änderungen an zahlreichen Datensätzen vorkommen, dass ein Fehler auftritt - dann sind einige Datensätze von der Änderung betroffen, andere nicht. Die Datenbank ist dann möglicherweise in einem inkonsistenten Zustand. Mit ROLLBACK kann die gesamte Transaktion dann wieder rückgängig gemacht werden (mit COMMIT wird sie bestätigt; das setzt in MySQL/InnoDB voraus, dass AUTOCOMMIT auf 0 steht (MySQL-Doku: AUTOCOMMIT)).

Klassisches Beispiel ist der Bankensektor: Zu einem bestimmten Zeitpunkt werden bspw. die Dispozinsen berechnet und dem Konto zugerechnet. Das betrifft sehr viele Kunden. Wenn nun ein Systemabsturz während der Änderung erfolgt, haben einige Kunden die Dispozinsen bereits zugerechnet, andere nicht. Hier hilft nur ein ROLLBACK.

COMMIT und ROLLBACK - Beispiel

Referentielle Integrität

Die referentielle Integrität besagt, dass Primärschlüssel-Fremdschlüssel-Beziehungen intakt sein müssen. Beispiel:

Beispiel für fehlende referentielle Integrität

Die Definition von Wikipedia ist ganz gut, man könnte sie direkt für Klausuren oder das Abitur auswendiglernen:

Definition referentielle Integrität
Die referentielle Integrität (auch Beziehungsintegrität) besagt, dass Attributwerte eines Fremdschlüssels auch als Attributwert des Primärschlüssels vorhanden sein müssen.

Als Grundregel der referentiellen Integrität gilt:

Fremdschlüssel müssen IMMER auf existierende Datensätze verweisen!

Das muss beachtet werden beim
1. Löschen von Datensätzen - wenn dadurch bspw. in der Kindtabelle ein Fremdschlüssel verwaist:

Beispiel: Durch das Löschen eines Datensatzes geht die referentielle Integrität verloren.

2. Einfügen von Datensätzen - wenn dadurch bspw. in der Kindtabelle ein verwaister Fremdschlüssel entsteht:

Beispiel: Durch das Einfügen eines Datensatzes geht die referentielle Integrität verloren.

Das gilt natürlich auch für das Ändern von Fremdschlüsseln.

Änderungsweitergabe, Löschweitergabe

Viele RDBMS haben Hilfsmittel, um die referentielle Integrität zu wahren. In MySQL kann InnoDB durch Foreign-Key-Constraints helfen.

Löschweitergabe

Wird ein Datensatz gelöscht, werden die abhängigen Datensätze auch in der Kindtabelle gelöscht. Beispiel: Wird vom Buchhändler ein Autor gelöscht, könnten sämtliche Bücher, die dieser Autor geschrieben hat, ebenfalls gelöscht werden.

Änderungsweitergabe

Wird ein Datensatz geändert, wird diese Änderung auch in der Kindtabelle ausgeführt. Beispiel:

Beispiel: Änderungsweitergabe zur Wahrung der referentiellen Integrität

Da wird über ON DELETE ... oder ON UPDATE ... erledigt. Entweder macht man sich die Hände direkt im MySQL-Code schmutzig und schreibt etwas wie

MySQL-Code
  CREATE TABLE kunden ( id INT, name VARCHAR(45),postleitzahl_FK VARCHAR(5), PRIMARY KEY (idkunden), -- .... CONSTRAINT FK_postleitzahl FOREIGN KEY (postleitzahl_FK) REFERENCES orte (postleitzahl) ON DELETE NO ACTION ON UPDATE CASCADE) ENGINE = InnoDB;    

... oder man lässt MySQL-Workbench für sich arbeiten. Beim Anlegen oder Ändern einer Tabelle (rechte Maustaste auf die Tabelle, "Alter Table ...") wählt man dann den Tab "Foreign Keys" und stellt die Weitergabeform ein:

In MySQL-Workbench Änderungsweitergaben einstellen

Dabei gibt es für ON UPDATE ... oder ON DELETE ... folgende möglichen Werte:

CASCADE - Änderungen/Löschung auch in Kindtabelle vornehmen

SET NULL - Fremdschlüssel in Kindtabelle auf NULL setzen

NO ACTION - keine weitere Aktion

RESTRICT - Aktion verweigern

Links

zdnet: Zuverlässigere Datenbanken durch Fremdschlüssel in MySQL - ausführliche Beschreibung, wie bei MySQL durch Inno-DB die referentielle Integrität aufrechterhalten werden kann.

Übungen

Alle Übungen finden Sie in der Materialsammlung (dort auch alle zusätzlichen Dateien wie Bilder, Klassendiagramme oder HTML-Vorlagen!).

Die aktuelle Übung können Sie hier als txt-File herunterladen.


***** Übungen: Datenintegrität / Referentielle Integrität *****

DB_05-1: Definition

Definieren Sie den Begriff "referentielle Integrität" und erläutern Sie ihn anhand eines Beispiels.




DB_05-2: Verständnisfragen zur Referentiellen Integrität

Betrachten Sie die Datenbank, die in 06datenbanken/datenmodellierung05_datenintegritaet_aufgabe2.png abgebildet ist.

Welche Operationen sind zulässig?
a) Einfügen Datensatz
b) Einfügen Datensatz
c) Löschen Datensatz
d) Löschen Datensatz




DB_05-3: Anwendung von FOREIGN-KEY-CONSTRAINTS

Benutzen Sie für diese Aufgabe diesen Dump: 06datenbanken/datenmodellierung05_datenintegritaet_kundendump.sql

Achtung: Einige Aufgaben funktionieren vielleicht nicht. Versuchen Sie genau (!) zu verstehen, warum nicht (die Fehlermeldungen sind i.d.R. zu allgemein gehalten).

1. Löschen Sie alle Orte (DELETE FROM orte;)
2. Wenden Sie RESTRICT auf ON UPDATE und ON DELETE an.
3. Löschen Sie via SQL-Befehl den Ort Emmendingen (DELETE FROM orte WHERE ...).
4. Ändern Sie die Postleitzahl von Musterhausen (UPDATE orte SET postleitzahl = 99999 WHERE ...).
5. Sie haben festgestellt, dass sich Änderungen nicht durchführen lassen. Ändern Sie die FOREIGN-KEY-CONSTRAINTS und probieren Sie sie bspw. mit Nr. 3 und 4 oben aus.