6. Datenintegrität
Werbung
6. Datenintegrität Motivation § Semantische Integrität (auch: Konsistenz) der in einer Datenbank gespeicherten Daten als wichtige Anforderung § nur sinnvolle Attributwerte (z.B. keine negativen Semester) § Abhängigkeiten zwischen Attributwerten (z.B. Telefon oder E-Mail muss bekannt sein) § Referentielle Integrität d.h. Integrität von Verweisen (z.B. Professor zu einer Vorlesung muss bekannt sein) § Eindeutigkeit von Attributwerten (z.B. jedes Büro darf nur einmal in Professoren auftauchen) § nur sinnvolle Änderungen der Attributwerte (z.B. Semester darf nicht verringert werden) Datenbanken / Kapitel 6: Datenintegrität 2 Inhalt § 6.1 Semantische Integrität § 6.2 Primärschlüssel und Eindeutigkeit § 6.3 Referentielle Integrität § 6.4 Constraints § 6.5 Trigger Datenbanken / Kapitel 6: Datenintegrität 3 6.1 Semantische Integrität § Semantische Integrität der Daten umfasst Anforderungen an den Zustand und Zustandsänderungen der Daten § Integritätsbedingungen müssen während des Datenbankentwurfs für den relevanten Ausschnitt der realen Welt erfasst werden § Integritätsbedingungen müssen allgemein und nicht nur auf den aktuellen Daten gelten Datenbanken / Kapitel 6: Datenintegrität 4 Statische Integritätsbedingungen § Statische Integritätsbedingungen sind Anforderungen an den Zustand der Daten § Beispiele: § hören verweist nur auf bekannte Studenten und Vorlesungen § Semester eines Studenten muss eine positive Zahl sein § Büro eines Professors darf nicht NULL sein § Professoren haben Einzelbüros Datenbanken / Kapitel 6: Datenintegrität 5 Dynamische Integritätsbedingungen § Dynamische Integritätsbedingungen sind Anforderungen an die Zustandsveränderung der Daten § Beispiele: § Semester eines Studenten darf nur erhöht werden § MatrNr eines Studenten darf nie verändert werden § Fach eines Professors darf nur vom initialen Wert NULL zu einem anderen von NULL verschiedenen Wert werden Datenbanken / Kapitel 6: Datenintegrität 6 Semantische Integrität in RDBMSs § RDBMSs sichern die semantische Integrität der Daten, sofern sinnvolle Integritätsbedingungen definiert sind § Sicherstellung der semantischen Integrität im Code der Anwendung hat im Vergleich schlechtere Wartbarkeit und höhere Fehleranfälligkeit § SQL unterstützt Definition von Integritätsbedingungen seit SQL-89 (z.B. Primär- und Fremdschlüssel) bzw. SQL:1999 (z.B. Trigger) Datenbanken / Kapitel 6: Datenintegrität 7 6.2 Primärschlüssel und Eindeutigkeit § Zur Erinnerung: Eine Menge von Attributen ist ein Schlüsselkandidat, wenn Sie die Tupel (Zeilen) einer Relation (Tabelle) eindeutig identifiziert § Beispiel: Tabelle Professoren in Hochschulanwendung § PersNr als künstliches Attribut ist Schlüsselkandidat § Büro ist Schlüsselkandidat (sofern Einzelbüros) § Wie können wir sicherstellen, dass es je Wert der beiden Attribute nur einen Professor gibt? Datenbanken / Kapitel 6: Datenintegrität 8 Primärschlüssel § Einer der Schlüsselkandidaten wird als Primärschlüssel ausgewählt und kann als solcher markiert werden § Beispiel: Tabelle Professoren 1 2 3 4 5 6 7 8 CREATE TABLE Professoren ( PersNr int NOT NULL , Vorname varchar (30) NOT NULL , Name varchar (30) NOT NULL , B ü ro int NOT NULL , Fach varchar (60) NULL , PRIMARY KEY ( PersNr ) ) § Wie können wir sicherstellen, dass es nur einen Professor pro Büro gibt? Datenbanken / Kapitel 6: Datenintegrität 9 Eindeutigkeit § Eindeutige Attribute können durch Angabe von UNIQUE im CREATE TABLE Kommando angegeben werden § Beispiel: Tabelle Professoren 1 2 3 4 5 6 7 8 CREATE TABLE Professoren ( PersNr int NOT NULL , Vorname varchar (30) NOT NULL , Name varchar (30) NOT NULL , B ü ro int NOT NULL UNIQUE , Fach varchar (60) NULL , PRIMARY KEY ( PersNr ) ) Datenbanken / Kapitel 6: Datenintegrität 10 6.3 Referentielle Integrität § Fremdschlüssel stellen eine Integritätsbedingung dar (d.h. referenzierte Tupel müssen vorhanden sein) § RDBMS stellt referentielle Integrität der Daten sicher, d.h. wenn die Attribute A1 ,…, An als Fremdschlüssel auf eine andere Tabelle T markiert sind, dann § müssen alle Ai den Wert NULL haben (sofern erlaubt), oder § es muss ein entsprechendes Tupel mit den Werten der Attribute Ai als Primärschlüssel in der Tabelle T existieren Datenbanken / Kapitel 6: Datenintegrität 11 Fremdschlüssel § Fremdschlüssel werden mittels FOREIGN KEY ... REFERENCES angegeben und können jeweils mehrere Attribute enthalten § Beispiel: Tabelle Vorlesungen 1 2 3 4 5 6 7 8 9 CREATE TABLE Vorlesungen ( VorlNr int NOT NULL , Bezeichnung varchar (60) NOT NULL , SWS varchar (30) NOT NULL , PersNr int NULL , FOREIGN KEY ( PersNr ) REFERENCES Professoren ( PersNr ) , PRIMARY KEY ( VorlNr ) ) § Wie soll sich RDBMS bei Änderung der Daten verhalten? Datenbanken / Kapitel 6: Datenintegrität 12 Fremdschlüssel bei Änderung der Daten § Ohne weitere Angabe, verweigert RDBMS Löschen eines Tupels, sofern noch ein anderes Tupel per Fremdschlüssel darauf verweist § Bei Definition eines Fremdschlüssels können wir angeben, was bei Änderung referenzierter Daten geschehen soll § SQL erweitert FOREIGN KEY Kommando wie folgt § Angabe eines Ereignis: ON DELETE oder ON UPDATE § Angabe des Verhaltens: CASCADE oder SET NULL Datenbanken / Kapitel 6: Datenintegrität 13 Fremdschlüssel bei Änderung der Daten § Beispiel: Vorlesungen mit Fremdschlüssel auf Professoren 1 2 3 4 5 6 7 8 9 10 CREATE TABLE Vorlesungen ( VorlNr int NOT NULL , Bezeichnung varchar (60) NOT NULL , SWS varchar (30) NOT NULL , PersNr int NULL , FOREIGN KEY ( PersNr ) REFERENCES Professoren ( PersNr ) ON UPDATE CASCADE , PRIMARY KEY ( VorlNr ) ) § Ändert sich die PersNr eines Professors, wird die geänderte PersNr für seine Vorlesungen übernommen Datenbanken / Kapitel 6: Datenintegrität 14 Fremdschlüssel bei Änderung der Daten § Beispiel: Vorlesungen mit Fremdschlüssel auf Professoren 1 2 3 4 5 6 7 8 9 10 CREATE TABLE Vorlesungen ( VorlNr int NOT NULL , Bezeichnung varchar (60) NOT NULL , SWS varchar (30) NOT NULL , PersNr int NULL , FOREIGN KEY ( PersNr ) REFERENCES Professoren ( PersNr ) ON DELETE CASCADE , PRIMARY KEY ( VorlNr ) ) § Wird ein Professor gelöscht, so werden alle seine Vorlesungen gelöscht Datenbanken / Kapitel 6: Datenintegrität 15 Fremdschlüssel bei Änderung der Daten § Beispiel: Vorlesungen mit Fremdschlüssel auf Professoren 1 2 3 4 5 6 7 8 9 10 CREATE TABLE Vorlesungen ( VorlNr int NOT NULL , Bezeichnung varchar (60) NOT NULL , SWS varchar (30) NOT NULL , PersNr int NULL , FOREIGN KEY ( PersNr ) REFERENCES Professoren ( PersNr ) ON UPDATE SET NULL , PRIMARY KEY ( VorlNr ) ) § Ändert sich die PersNr eines Professors, wird die PersNr seiner Vorlesungen auf NULL gesetzt Datenbanken / Kapitel 6: Datenintegrität 16 Fremdschlüssel bei Änderung der Daten § Beispiel: Vorlesungen mit Fremdschlüssel auf Professoren 1 2 3 4 5 6 7 8 9 10 CREATE TABLE Vorlesungen ( VorlNr int NOT NULL , Bezeichnung varchar (60) NOT NULL , SWS varchar (30) NOT NULL , PersNr int NULL , FOREIGN KEY ( PersNr ) REFERENCES Professoren ( PersNr ) ON DELETE SET NULL , PRIMARY KEY ( VorlNr ) ) § Wird ein Professor gelöscht, so wird die PersNr seiner Vorlesungen auf NULL gesetzt Datenbanken / Kapitel 6: Datenintegrität 17 6.4 Constraints § Integritätsbedingungen (constraints) lassen sich mittels des CONSTRAINT Kommandos in SQL definieren § Integritätsbedingungen haben einen Namen und SQL erlaubt deren Definition in den Kommandos CREATE TABLE und ALTER TABLE § Integritätsbedingungen beziehen sich immer auf eine einzelne Tabelle Datenbanken / Kapitel 6: Datenintegrität 18 UNIQUE Constraints § Eindeutigkeit einzelner Attribute lässt sich bei der Definition einer Tabelle mittels UNIQUE erreichen § UNIQUE Constraints erlauben darüber hinaus, die Eindeutigkeit einer Mengen von Attributen festzulegen § NULL-Werte sind, anders als bei Primärschlüsseln, erlaubt Datenbanken / Kapitel 6: Datenintegrität 19 UNIQUE Constraints § Beispiel: Tabelle Vorlesungen erweitert um ein Attribut Studiengang; im gleichen Studiengang darf es nicht mehrere Vorlesungen mit gleichen Namen geben 1 2 3 4 5 6 7 8 9 10 CREATE TABLE Vorlesungen ( VorlNr int NOT NULL , Bezeichnung varchar (60) NOT NULL , Studiengang varchar (30) NULL , SWS varchar (30) NOT NULL , PersNr int NULL , CONSTRAINT B e z e i c h n u n g S t u d i e n g a n g U n i q u e UNIQUE ( Bezeichnung , Studiengang ) , PRIMARY KEY ( VorlNr ) ) Eine Vorlesung mit Titel „Wissenschaftliches Arbeiten“, die keinem Studiengang zugeordnet ist, ist möglich Datenbanken / Kapitel 6: Datenintegrität 20 CHECK Constraints § Integritätsbedingungen, die sich auf die Werte der Attribute eines Tupels (Zeile) beziehen, lassen sich mittels des CHECK Kommandos definieren § CHECK Constraints können z.B. verwendet werden, um § Wertebereich eines Attributs einzugrenzen § Zusammenhänge zwischen Attributwerten zu erfassen (z.B. nur bestimmte Kombinationen zu erlauben) § … Datenbanken / Kapitel 6: Datenintegrität 21 CHECK Constraints § Beispiel: Einschränkung des Wertebereichs von Semester auf einen Wert im Bereich [1,50] 1 2 3 4 5 6 7 8 9 CREATE TABLE Studenten ( MatrNr int NOT NULL , Vorname varchar (30) NOT NULL , Name varchar (30) NOT NULL , Semester int NOT NULL , CONSTRAINT Semester Cons traint CHECK ( Semester >= 1 AND Semester <= 50) , PRIMARY KEY ( MatrNr ) ) § RDBMSs erlauben teilweise auch die Angabe ohne Benennung bei der Deklaration des Attributes 1 Semester int NOT NULL CHECK ( Semester >= 1 AND Semester <= 50) , Datenbanken / Kapitel 6: Datenintegrität 22 CHECK Constraints § Beispiel: Tabelle Professoren erweitert um private Kontaktdaten E-Mail und Mobil, wovon mindestens eines bekannt sein müssen 1 2 3 4 5 6 7 8 9 10 11 12 CREATE TABLE Professoren ( PersNr int NOT NULL , Vorname varchar (30) NOT NULL , Name varchar (30) NOT NULL , B ü ro int NOT NULL UNIQUE , Fach varchar (60) NULL , EMail varchar (60) NULL , Mobil varchar (60) NULL , CONSTRAINT Erreichbarkeit CHECK ( EMail IS NOT NULL OR Mobil IS NOT NULL ) , PRIMARY KEY ( PersNr ) ) Datenbanken / Kapitel 6: Datenintegrität 23 CHECK Constraints § Beispiel: Attribut Mobil in Tabelle Professoren soll nur Werte der Form +49 (177) 7343809 erlauben 1 2 3 4 5 6 7 8 9 10 11 12 Datenbanken / Kapitel 6: Datenintegrität CREATE TABLE Professoren ( PersNr int NOT NULL , Vorname varchar (30) NOT NULL , Name varchar (30) NOT NULL , B ü ro int NOT NULL UNIQUE , Fach varchar (60) NULL , EMail varchar (60) NULL , Mobil varchar (60) NULL , CONSTRAINT MobilFormat CHECK ( Mobil LIKE ’ +% (%) % ’) , PRIMARY KEY ( PersNr ) ) 24 CHECK Constraints § Beispiel: Attribut Fach in Tabelle Professoren darf nur die Werte „Informatik“, „Mathematik“ und „Physik“ annehmen 1 2 3 4 5 6 7 8 9 10 11 12 CREATE TABLE Professoren ( PersNr int NOT NULL , Vorname varchar (30) NOT NULL , Name varchar (30) NOT NULL , B ü ro int NOT NULL UNIQUE , Fach varchar (60) NULL , EMail varchar (60) NULL , Mobil varchar (60) NULL , CONSTRAINT F ä cher CHECK ( Fach IN ( ’ Informatik ’ , ’ Mathematik ’ , ’ Physik ’)) , PRIMARY KEY ( PersNr ) ) Datenbanken / Kapitel 6: Datenintegrität 25 CHECK Constraints § CHECK Constraints können Unteranfragen enthalten und so komplexe Integritätsbedingungen abbilden § RDBMS muss komplexe Integritätsbedingungen bei jeder Änderung der Daten überprüfen und enthaltene Unteranfragen auswerten (Kosten!) Datenbanken / Kapitel 6: Datenintegrität 26 CHECK Constraints § Beispiel: Studenten müssen Vorlesungen gehört haben, in denen sie eine Prüfung ablegen 1 2 3 4 5 6 7 8 9 10 11 CREATE TABLE pr ü fen ( MatrNr int NOT NULL , VorlNr int NOT NULL , Note float NOT NULL , CONSTRAINT VorlesungGeh ö rt CHECK ( EXISTS ( SELECT * FROM h ö ren WHERE h ö ren . MatrNr = pr ü fen . MatrNr AND h ö ren . VorlNr = pr ü fen . VorlNr )) , PRIMARY KEY ( MatrNr , VorlNr ) ) § Bemerkung: Dies ließe sich auch durch einen Fremdschlüssel in prüfen umsetzen Datenbanken / Kapitel 6: Datenintegrität 27 CHECK Constraints § Beispiel: Studenten dürfen nicht mehrere Vorlesungen mit identischem Titel hören 1 2 3 4 5 6 7 8 9 10 11 12 13 14 CREATE TABLE h ö ren ( MatrNr int NOT NULL , VorlNr int NOT NULL , CONSTRAINT T i t e l U n t e r s c h i e d l i c h CHECK ( NOT EXISTS ( SELECT * FROM h ö ren h , Vorlesung v1 , Vorlesung v2 WHERE h . MatrNr = h ö ren . MatrNr AND h . VorlNr = v1 . VorlNr AND h ö ren . VorlNr = v2 . VorlNr AND v1 . Titel = v2 . Titel ) PRIMARY KEY ( MatrNr , VorlNr ) ) Datenbanken / Kapitel 6: Datenintegrität 28 6.5 Trigger § Trigger wurden in SQL:1999 eingeführt und sind der allgemeinste Mechanismus, um Integritätsbedingungen zu formulieren § Trigger beschreiben eine Aktion, die beim Eintreten eines bestimmten Ereignis durchgeführt werden soll § Aktion wird in prozeduraler Programmiersprache des RDBMS geschrieben (z.B. Transact-SQL oder PL/SQL) § Syntax ist (noch) wenig einheitlich zwischen RDBMSs Datenbanken / Kapitel 6: Datenintegrität 29 Trigger § Trigger können verwendet werden, um dynamische Integritätsbedingungen zu formulieren § Beispiel: Semester von Studenten darf nur erhöht werden 1 2 3 4 5 6 CREATE TRIGGER SemesterErh ö hung BEFORE UPDATE OF Semester ON Studenten FOR EACH ROW WHEN ( old . Semester > new . Semester ) SET ( new . Semester = old . Semester ) END Betrifft eine Änderung das Semester Attribut (Ereignis), wird überprüft, ob der neue Wert kleiner ist und ggf. der alte Wert beibehalten (Aktion) Datenbanken / Kapitel 6: Datenintegrität 30 Übung Integritätsbedingungen Versandhandel § Beispiel: Artikel und Bestellungen im Versandhandel 1 2 3 4 5 6 7 8 9 CREATE TABLE Bestellungen ( BestellNr int NOT NULL , KundenNr int NOT NULL , Bestelldatum date NOT NULL , Lieferdatum date NULL , Betrag money NOT NULL , Bemerkung varchar (100) NULL , PRIMARY KEY ( BestellNr ) ) 1 2 3 4 5 6 7 8 9 10 11 12 CREATE TABLE Kunden ( KundenNr int NOT NULL , Name varchar (40) NOT NULL , Vorname varchar (30) NOT NULL , Strasse varchar (40) NOT NULL , PLZ char (5) NOT NULL , Wohnort varchar (40) NOT NULL , Telefon varchar (20) NULL , Telefax varchar (20) NULL , Email varchar (30) NULL , PRIMARY KEY ( KundenNr ) ) § Was sind sinnvolle Fremdschlüssel? § PLZ von Kunden soll zwischen „01067“ und „99189“ liegen § Kunden sollen auf mindestens zwei der Kommunikationswege Telefon, Telefax und E-Mail erreichbar sein § Bestellungen werden frühestens am Bestelldatum ausgeliefert Datenbanken / Kapitel 6: Datenintegrität 31 Übung Integritätsbedingungen Versandhandel 1 2 3 4 5 6 7 8 9 10 11 12 13 CREATE TABLE Bestellungen ( BestellNr int NOT NULL , KundenNr int NOT NULL , Bestelldatum date NOT NULL , Lieferdatum date NULL , Betrag money NOT NULL , Bemerkung varchar (100) NULL , CONSTRAINT Daten CHECK ( Bestelldatum <= Lieferdatum ) , FOREIGN KEY KundenNr REFERENCES Kunden ( KundenNr ) , PRIMARY KEY ( BestellNr ) ) Datenbanken / Kapitel 6: Datenintegrität 32 Übung Integritätsbedingungen Versandhandel 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 CREATE TABLE Kunden ( KundenNr int NOT NULL , Name varchar (40) NOT NULL , Vorname varchar (30) NOT NULL , Strasse varchar (40) NOT NULL , PLZ char (5) NOT NULL , Wohnort varchar (40) NOT NULL , Telefon varchar (20) NULL , Telefax varchar (20) NULL , Email varchar (30) NULL , CONSTRAINT Postleitzahlen CHECK ( PLZ >= ’ 01067 ’ AND PLZ <= ’ 99189 ’) , CONSTRAINT Erreichbarkeit CHECK (( Telefon IS NOT NULL AND Telefax IS NOT NULL ) OR ( Telefon IS NOT NULL AND Email IS NOT NULL ) OR ( Telefax IS NOT NULL AND Email IS NOT NULL )) , PRIMARY KEY ( KundenNr ) ) Datenbanken / Kapitel 6: Datenintegrität 33 Zusammenfassung § Semantische Integrität umfasst Anforderungen an Zustand und Zustandsänderungen der Daten § Statische Integritätsbedingungen umsetzbar mittels § Primärschlüsseln und Fremdschlüsseln § Constraints z.B. zur Wahrung der Eindeutigkeit oder Einschränkung des Wertebereichs § Dynamische Integritätsbedingungen umsetzbar mittels § Trigger als allgemeiner Mechanismus mit systemabhängiger Syntax Datenbanken / Kapitel 6: Datenintegrität 34 Literatur [1] A. Kemper und A. Eickler: Datenbanksysteme – Eine Einführung, De Gruyter Oldenbourg, 2015 (Kapitel 5) [2] G. Saake, K.-U. Sattler und A. Heuer: Datenbanken - Konzepte und Sprachen, mitp Professional, 2013 (Kapitel 12) Datenbanken / Kapitel 6: Datenintegrität 35
