Informatik für Ökonomen II: Datenintegrität Prof. Dr. Carl-Christian Kanne Wednesday, November 19, 2008 1 Konsistenzbedingungen • DBMS soll logische Datenintegrität gewährleisten • Beispiele für Integritätsbedingungen • • • Schlüssel Beziehungskardinalitäten Attributdomänen • Klassifikation • • statisch: Bedingungen an DB-Zustand dynamisch: Bedingungen an DB-Zustandsübergänge Wednesday, November 19, 2008 2 Referentielle Integrität • Fremdschlüssel müssen auf existierende Tupel verweisen oder einen Nullwert enthalten • Beispiel: Vorlesungen.gelesenVon → Professoren.PersNr Wednesday, November 19, 2008 3 Referentielle Integrität in SQL • • • Kandidatenschlüssel: unique Primärschlüssel: primary key Fremdschlüssel: foreign key • Beispiel: create table R ( α integer primary key, ... ); create table S ( ..., κ integer references R ); Wednesday, November 19, 2008 4 Einhaltung referentieller Integrität Änderung von referenzierten Daten 1. Default: Zurückweisen der Änderungsoperation 2. Propagieren der Änderungen: cascade 3. Verweise auf Nullwert setzen: set null Wednesday, November 19, 2008 5 Einhaltung referentieller Integrität Originalzustand S R κ α α1 α1 α2 α2 Änderungsoperationen update R delete from R set α = α'1 where α = α1; where α = α1; Wednesday, November 19, 2008 6 Kaskadieren (Update) S R κ α α'1 α'1 α2 α2 create table S ( ..., κ integer references R on update cascade ); Wednesday, November 19, 2008 7 Einhaltung referentieller Integrität Originalzustand S R κ α α1 α1 α2 α2 Änderungsoperationen update R delete from R set α = α'1 where α = α1; where α = α1; Wednesday, November 19, 2008 8 Kaskadieren (DELETE) S R κ α α2 α2 create table S ( ..., κ integer references R on delete cascade ); Wednesday, November 19, 2008 9 set null S R κ α2 S R α κ α1 - α2 α2 α α2 create table S create table S ( ..., ( ..., κ integer references R κ integer references R on update set null ); on delete set null ); Wednesday, November 19, 2008 10 Kaskadierendes Löschen Logik Schopenhauer Sokrates Ethik Theoprastos Mäeutik Erkenntnistheorie ... Boethik Carnap ... ... Russel Wissenschaftstheorie Wednesday, November 19, 2008 11 create table Vorlesungen ( ..., gelesenVon integer references Professoren on delete cascade); create table hören ( ..., VorlNr integer references Vorlesungen on delete cascade); Wednesday, November 19, 2008 12 Einfache statische Integritätsbedingungen • Wertebereichseinschränkungen ... check Semester between 1 and 13 • Aufzählungstypen ... check Geschlecht=’M’ or Geschlecht=’W’ ... Wednesday, November 19, 2008 13 Das Universitätsschema mit Integritätsbedingungen create table Studenten ( MatrNr integer primary key, Name varchar(30) not null, Semester integer check Semester between 1 and 13), create table Professoren ( PersNr integer primary key, Name varchar(30) not null, Rang character(2) check (Rang=`C2´ or Rang=`C3´ ...), Raum integer unique ); Wednesday, November 19, 2008 14 create table Assistenten ( PersNr integer primary key, Name varchar(30) not null, Fachgebiet varchar(30), Boss integer, foreign key (Boss) references Professoren on delete set null); create table Vorlesungen ( VorlNr integer primary key, Titel varchar(30), SWS integer, gelesen Von integer references Professoren on delete set null); Wednesday, November 19, 2008 15 create table hören ( MatrNr integer references Studenten on delete cascade, VorlNr integer references Vorlesungen on delete cascade, primary key (MatrNr, VorlNr)); create table voraussetzen ( Vorgänger integer references Vorlesungen on delete cascade, Nachfolger integer references Vorlesungen on delete cascade, primary key (Vorgänger, Nachfolger)); Wednesday, November 19, 2008 16 create table prüfen ( MatrNr integer references Studenten on delete cascade, VorlNr integer references Vorlesungen, PersNr integer references Professoren on delete set null, Note numeric (2,1) check (Note between 0.7 and 5.0), primary key (MatrNr, VorlNr)); Wednesday, November 19, 2008 17 Weitere Constraints create table prüfen ( MatrNr integer references Studenten on delete cascade, VorlNr integer references Vorlesu... Note numeric (2,1) check (Note between 1.0 and 6.0), primary key (MatrNr,VorlNr), constraint VorherHören check ( exists (select * from hören h where h.VorlNr =prüfen.VorlNr and h.MatrNr = prüfen.MatrNr)) ); Wednesday, November 19, 2008 18