Datenmodellierung Datenmodellierung Acknowledgments Datenmodellierung VU 184.685, WS 2015 Datenintegrität Die Folien sind eine kleine Erweiterung der Folien von Katrin Seyr. Sebastian Skritek Institut für Informationssysteme Technische Universität Wien Sebastian Skritek Seite 1 Datenmodellierung 1. Überblick Sebastian Skritek Datenmodellierung Überblick Seite 2 2. Datenmanipulationssprache Datenmanipulationssprache – Tupel Einfügen Einfügen von Tupeln in eine angelegte Tabelle Professoren ( PersNr : integer , Name : varchar (30) , Rang : character (2) , Raum : integer ) 1 Nachtrag: SQL als Datenmanipulationssprache 2 Überblick 3 Integritätsbedingungen Beispiel 4 Statische Integritätsbedingungen Einfügen der Professorin Curie: 5 Referentielle Integrität insert into Professoren values (2136 , ’ Curie ’ , ’ C4 ’ , 36); • Gewährleistung • Referentielle Integrität in SQL • Zyklische Abhängigkeiten 6 Einfügen mehrerer Professoren: Trigger Sebastian Skritek insert into Professoren values (2125 , ’ Sokrates ’ , ’ C4 ’ , 226) , (2126 , ’ Russel ’ , ’ C4 ’ , 232); Seite 3 Sebastian Skritek Seite 4 Datenmodellierung 2. Datenmanipulationssprache Datenmodellierung Datenmanipulationssprache – Tupel Einfügen 2. Datenmanipulationssprache Datenmanipulationssprache – Tupel Löschen Auflisten der zu löschenden Tupeln: Das Ergebnis einer Anfrage in eine Tabelle eintragen: Beispiel Beispiel Löschen des Herrn Kant aus der Professorentabelle: Eintragen aller Studenten zur Vorlesung ‘Logik’: delete from Professoren values (2137 , ’ Kant ’ , ’ C4 ’ , 7); hören ( MatrNr , VorlNr ) Studenten ( MatrNr , Name , Sem ) Vorlesungen ( VorlNr , Titel , SWS , PersNr ) Lösche alle Tupel die eine Bedingung erfüllen: Beispiel insert into hören select MatrNr , VorlNr from Studenten , Vorlesungen where Titel = ’ Logik ’; Löschen des Herrn Kant aus der Professorentabelle: delete from Professoren where PersNr =2137; delete from Professoren where PersNr < 2137; Sebastian Skritek Datenmodellierung Seite 5 2. Datenmanipulationssprache Datenmodellierung Datenmanipulationssprache – Tupel Löschen Seite 6 2. Datenmanipulationssprache Datenmanipulationssprache – Tupel Verändern Beispiel Beispiel Erhöhen der Semesteranzahl aller Studierender um 1: Lösche alle Studenten, die die Vorlesung ‘Logik’ besuchen update Studenten set Semester = Semester + 1; hören ( MatrNr , VorlNr ) Studenten ( MatrNr , Name , Sem ) Vorlesungen ( VorlNr , Titel , SWS , PersNr ) Beispiel delete from Studenten where MatrNr in ( select MatrNr from hören , Vorlesungen where hören . VorlNr = Vorlesungen . VorlNr and Titel = ’ Logik ’ ); Sebastian Skritek Sebastian Skritek Alle C3 Professoren mit einer Personalnummer über 2500 erhalten den Rang C2 update Professoren set Rang = ’ C2 ’ where Rang = ’ C3 ’ and PersNr > 2500; Seite 7 Sebastian Skritek Seite 8 Datenmodellierung 2. Datenmanipulationssprache Datenmodellierung Datenmanipulationssprache – Tupel Verändern 3. Überblick Überblick Beispiel Professoren ( PersNr : integer , Name : varchar (30) , Rang : character (2) , Raum : integer , Lehrbelastung: integer ) Vorlesungen ( VorlNr , Titel , SWS , PersNr ) Trage für jeden Professor seine Lehrbelastung (=Summe SWS) ein 1 Nachtrag: SQL als Datenmanipulationssprache 2 Überblick 3 Integritätsbedingungen 4 Statische Integritätsbedingungen 5 Referentielle Integrität • Gewährleistung • Referentielle Integrität in SQL • Zyklische Abhängigkeiten update Professoren set Lehrbelastung = ( select sum ( SWS ) from Vorlesungen v where v . PersNr = Professoren . PersNr ) 6 Trigger (Anmerkung: Lehrbelastung sollte so nicht gespeichert werden) Sebastian Skritek Datenmodellierung Seite 9 4. Integritätsbedingungen Sebastian Skritek Datenmodellierung Integritätsbedingungen Seite 10 4. Integritätsbedingungen Integritätsbedingungen Integritätsbedingungen werden eingeteilt in: Datenbankmanagementsysteme sind für die Erhaltung der Datenkonsistenz zuständig. statische Integritätsbedingungen: Bedingungen (constraints) an den Zustand der Datenbasis, die in jedem Zustand der Datenbank erfüllt sein müssen. Konsistenzerhaltung nach Systemfehler (Recovery) bzw. bei Mehrbenutzerbetrieb (Concurrency Control) → LVA Datenbanksysteme. Beispiele Hier (Kapitel 5, 6): semantische Integritätsbedingungen, die aus den Eigenschaften der modellierten Miniwelt abgeleitet werden. Professoren haben Rang C2, C3, C4, Beispiele Anmeldung zur Übung nur bei aufrechter Inskription Matrikelnummer der Studierenden ist eindeutig MatrNr muss eindeutig sein; - in AT: 7 stellig, die ersten zwei Ziffern leiten sich aus dem Immatrikulationsjahr ab, die nächsten zwei Ziffern codieren die Immatrikulationsuniversität. dynamische Integritätsbedingungen: Bedingungen an Zustandsänderungen in der Datenbank. Beispiel PersNr ist eine 4-stellige Zahl Professoren dürfen nur befördert werden Sebastian Skritek Seite 11 Sebastian Skritek Seite 12 Datenmodellierung 4. Integritätsbedingungen 4.1. Statische Integritätsbedingungen Datenmodellierung Integritätsbedingungen 4. Integritätsbedingungen 4.2. Statische Integritätsbedingungen in SQL Statische Integritätsbedingungen in SQL Schlüsselkandidaten: unique(Attributliste) Bekannte implizite Anforderungen an die Datenintegrität Primärschlüssel: primary key(Attributliste) Fremdschlüssel: foreign key(Attributliste) Schlüssel: müssen innerhalb einer Relation eindeutig sein. Keine Nullwerte erlaubt: not null Defaultwert angeben: default Beziehungskardinalitäten: die im ER Modell definierten Kardinalitäten werden im Relationenmodell so umgesetzt, dass davon abweichende Daten nicht eingegeben werden können. Attributdomänen: durch die Festlegung von Wertebereichen werden Bedingungen an die Daten gestellt. Beispiel Inklusion bei Generalisierung: Entities der Untertypen müssen auch in Obertypen enthalten sein create table Studenten ( MatrNr integer primary key , Name varchar (30) not null , Semester integer default 1); Sebastian Skritek Datenmodellierung Seite 13 4. Integritätsbedingungen 4.2. Statische Integritätsbedingungen in SQL Statische Integritätsbedingungen in SQL Datenmodellierung Seite 14 4. Integritätsbedingungen 4.2. Statische Integritätsbedingungen in SQL Statische Integritätsbedingungen in SQL Überprüfung allgemeiner statischer Integritätsbedingungen mittels check-Klausel, gefolgt von einer Bedingung Beispiel Studenten können sich nur über Vorlesungen prüfen lassen, die sie vorher gehört haben. Auswertung der check-Klausel bei Änderung/Einfügung in die Tabelle Änderungen auf einer Tabelle werden nur zugelassen, wenn check zu true auswertet. create table prüfen ( MatrNr integer , VorlNr integer , PersNr integer , Note numeric (2 ,1) check ( Note between 0.7 and 5.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 ))); Bedingungen können beliebig komplex sein, auch eine Anfrage enthalten - Achtung: nur sehr rudimentär implementiert! Beispiel create table Studenten ( MatrNr integer primary key , Name varchar (30) not null , Semester integer default 1 check ( Semester between 1 and 13)); Sebastian Skritek Sebastian Skritek Seite 15 Sebastian Skritek Seite 16 Datenmodellierung 5. Referentielle Integrität Datenmodellierung Referentielle Integrität 5. Referentielle Integrität Referentielle Integrität Definition (Dangling References) Schlüssel identifizieren ein Tupel einer Relation. Fremdschlüssel verweisen auf Tupel einer in Beziehung stehenden Relation. Dangling References sind Verweise von Fremdschlüsseln auf nicht existierende Datensätze Beispiel Beispiele (Dangling References) PersNr ist Schlüssel von Professoren, gelesenVon in Vorlesungen verweist auf Tupel in Professoren und ist somit Fremdschlüssel. Es muss sichergestellt werden, dass in gelesenVon keine Werte stehen, die in PersNr nicht vorkommen. Einfügen des folgenden Datensatzes in der Tabelle Vorlesungen: insert into Vorlesungen values (5100 , ’ Nihilismus ’ , 40 , 0007); ⇒ worauf verweist ’0007’ ? Definition (referentielle Integrität) Löschen des folgenden Datensatzes in der Tabelle Professoren: Fremdschlüssel müssen entweder auf existierende Tupel einer anderen Relation verweisen oder einen Nullwert enthalten. delete from Professoren where PersNr =2125; ⇒ worauf verweist ’2125’ in der Vorlesungs-Tabelle? Sebastian Skritek Datenmodellierung Seite 17 5. Referentielle Integrität 5.1. Gewährleistung der referentiellen Integrität Gewährleistung der referentiellen Integrität Sebastian Skritek Datenmodellierung Seite 18 5. Referentielle Integrität 5.2. Referentielle Integrität in SQL Referentielle Integrität in SQL Beschreibungsmöglichkeit für jeden der drei Schlüsselbegriffe wie folgt: (Kandidaten)-Schlüssel: unique Theorem (referentielle Integrität) Primärschlüssel: primary key(Attributliste) Sei R eine Relation mit Primärschlüssel κ und S eine Relation mit Fremdschlüssel α auf R. Die referentielle Integrität ist gewährleistet, wenn Fremdschlüssel: foreign key(Attributliste) Kennzeichnung der Tabelle, auf die verwiesen wird: πα (S) ⊆ πκ (R) references (Tabellenname) Einhaltung der Referenziellen Integrität bei Updates, Löschen: Erlaubte Änderungen sind also: on update {no action | cascade | set null | set default} Einfügen von s in S, wenn s.α ∈ πκ (R) Verändern Tupels von s zu s 0 in S, wenn s 0 .α ∈ πκ (R) on delete {no action | cascade | set null | set default} Verändern von r .κ in R, wenn σα=r .κ (S) = ∅, d.h. es gibt keine Verweise von S auf r Achtung bei zyklischen Abhängigkeiten (Chicken-Egg Problem - siehe unten): Löschen von r in R, wenn σα=r .κ (S) = ∅ alter table bzw. deferred deferrable Sebastian Skritek Seite 19 Sebastian Skritek Seite 20 Datenmodellierung 5. Referentielle Integrität 5.2. Referentielle Integrität in SQL Datenmodellierung Referentielle Integrität in SQL 5. Referentielle Integrität 5.2. Referentielle Integrität in SQL Referentielle Integrität in SQL Beispiel (Fortsetzung) Beispiel create table Professoren ( PersNr integer primary key , Name varchar (30) not null , ... ); Vorlesung create table Vorlesungen ( VorlNr integer primary key , Titel varchar (30) , SWS integer , gelesenVon integer [ foreign key ] references Professoren on update cascade on delete set null ); ... Professoren PersNr ... 2137 2137 ... ... 2125 2125 ... ... .. . 2126 .. . 2136 .. . ... .. . update Professoren set PersNr =1111 where PersNr =2137; delete from Professoren where PersNr =2125; Seite 21 5. Referentielle Integrität gelesenVon Gewünschte Änderungen: Sebastian Skritek Datenmodellierung ... 5.2. Referentielle Integrität in SQL Sebastian Skritek Seite 22 Datenmodellierung Referentielle Integrität in SQL 5. Referentielle Integrität 5.2. Referentielle Integrität in SQL Referentielle Integrität in SQL Beispiel (Fortsetzung) Beispiel (Fortsetzung) update Professoren set PersNr =1111 where PersNr =2137; delete from Professoren where PersNr =2125; Einhaltung der referentiellen Integrität durch “auf NULL setzen” (on delete set NULL) Einhaltung der referentiellen Integrität durch Kaskadieren (on update cascade) Vorlesung Vorlesung ... ... Sebastian Skritek gelesenVon 1111 Professoren PersNr 1111 ... ... ... 2125 2125 ... ... .. . 2126 .. . 2136 .. . ... .. . Seite 23 Sebastian Skritek ... gelesenVon ... 1111 ... NULL ... .. . 2126 .. . Professoren PersNr ... 1111 ... 2136 .. . ... .. . Seite 24 Datenmodellierung 5. Referentielle Integrität 5.2. Referentielle Integrität in SQL Datenmodellierung Kaskadierendes Löschen create table Studenten ( MatrNr integer primary key , Name varchar (30) not null , Semester integer check ( Semester between 1 and 13)); create table Vorlesungen (... gelesenVon integer references Professoren on delete cascade ); create table Professoren ( PersNr integer primary key , Name varchar (30) not null , Rang char (2) check ( Rang in ( ’ C2 ’ , ’ C3 ’ , ’ C4 ’)) , Raum integer unique ); create table hören (... VorlNr integer references Vorlesungen on delete cascade ); Logik create table Assistenten ( PersNr integer primary key , Name varchar (30) not null , Fachgebiet varchar (30) , Boss integer references Professoren on delete set null ); Schopenhauer Mäeutik Ethik Theophrastos Sebastian Skritek Datenmodellierung Seite 25 5. Referentielle Integrität 5.2. Referentielle Integrität in SQL Sebastian Skritek Datenmodellierung Universitätsschema mit Integritätsbedingungen Seite 26 5. Referentielle Integrität 5.2. Referentielle Integrität in SQL Universitätsschema mit Integritätsbedingungen create table voraussetzen ( Vorgänger integer references Vorlesungen on delete cascade , Nachfolger integer references Vorlesungen on delete cascade , primary key ( Vorgänger , Nachfolger )); create table Vorlesungen ( VorlNr integer primary key , Titel varchar (30) , SWS integer , gelesenVon integer references Professoren on delete set null ); 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 )); create table hören ( MatrNr integer references Studenten on delete cascade , VorlNr integer references Vorlesungen on delete cascade , primary key ( MatrNr , VorlNr )); Sebastian Skritek 5.2. Referentielle Integrität in SQL Universitätsschema mit Integritätsbedingungen Vorsicht bei der Verwendung von on delete cascade. Es kann zu kaskadierdendem Löschen kommen. Sokrates 5. Referentielle Integrität Seite 27 Sebastian Skritek Seite 28 Datenmodellierung 5. Referentielle Integrität 5.3. Zyklische Abhängigkeiten Datenmodellierung Zyklische Abhängigkeiten 5. Referentielle Integrität 5.3. Zyklische Abhängigkeiten Zyklische Abhängigkeiten Lösung: verwenden Sie den alter table Befehl: Beispiel (chicken/egg) Legen Sie die Tabellen chicken und egg an, wobei für jedes Huhn vermerkt wird, aus welchem Ei es geschlüpft ist, und umgekehrt: Beispiel (chicken/egg) create table chicken ( cID integer primary key , eID integer references egg ); create table chicken ( cID integer primary key , eID integer ); create table egg ( eID integer primary key , cID integer references chicken ); ERROR: relation ‘‘egg’’ does not exist create table egg ( eID integer primary key , cID integer references chicken ); alter table chicken add constraint chickenrefegg foreign key ( eID ) references egg ; Sebastian Skritek Datenmodellierung Seite 29 5. Referentielle Integrität 5.3. Zyklische Abhängigkeiten Sebastian Skritek Datenmodellierung Zyklische Abhängigkeiten Seite 30 5. Referentielle Integrität 5.3. Zyklische Abhängigkeiten Zyklische Abhängigkeiten Lösung: verzögern Sie die Überprüfung der Bedingung bis zum Ende der Transaktion (Definition Transaktion in LVA Datenbanksysteme). Beispiel (chicken/egg) Beispiel (chicken/egg) Das Anlegen der Tabellen funktioniert, aber: alter table chicken add constraint chickenrefegg foreign key ( eID ) references egg initially deferred deferrable ; insert into chicken values (1 ,11); ERROR: insert or update on table ‘‘chicken’’ violates foreign key constraint ‘‘chickenrefegg’’ DETAIL: Key (eid)=(11) is not present in table ‘‘egg’’. Sebastian Skritek begin ; insert into chicken values (1 ,11); insert into egg values (11 ,1); commit ; Seite 31 Sebastian Skritek Seite 32 Datenmodellierung 6. Trigger Datenmodellierung 6. Trigger 6.1. Anwendungsgebiete Anwendungsgebiete Trigger Trigger finden Verwendung bei: Standardisierung erst im SQL-99 Standard, waren allerdings schon vorher in kommerziellen DBMS enthalten. Daher ist die Implementierung in den meisten DBMS verschieden. Berechnung gespeicherter abgeleiteter Attribute Beispiel Verwendung zur Sicherstellung Datenintegrität Berechnung des Bruttopreises bei gespeichertem Nettopreis und Umsatzsteuersatz Arbeiten nach dem Event - Condition - Action Modell: beim Eintreffen eines bestimmten Ereignisses (event) unter bestimmten Bedingungen (condition) werden vom DBMS Aktionen automatisch ausgeführt (action). allgemeinen Bedingungen Beispiele Alle Mitarbeiter, die mehr als 5 SWS Vorlesungen abhalten, bekommen einen Gehaltsbonus (+5%). Professoren können nicht degradiert werden Achtung: zyklische Trigger oder Endlosschleifen! Sebastian Skritek Datenmodellierung Seite 33 6. Trigger 6.2. Syntax Sebastian Skritek Datenmodellierung Syntax Seite 34 6. Trigger 6.3. Beispiele Beispiele Beispiel Alle Mitarbeiter, die mehr als 5 SWS Vorlesungen abhalten, bekommen einen Gehaltsbonus (+5%). Definition des Triggers: create trigger Auslösende Ereignisse: insert, update, delete create trigger viellehre after insert on Vorlesungen referencing new as vo_neu for each row when group by vo_neu . gelesenvon having sum ( Vorlesungen . SWS ) > 5 and sum ( Vorlesungen . SWS ) - vo_neu . SWS <=5 update Professoren set Professoren . gehalt = Professoren . gehalt *1.05 where Professoren . PersNr = vo_neu . gelesenvon ; “feuert” vor oder nach Ausführung: before/after insert auf Tupel- bzw. Queryebene: for each row/statement einschränkende Bedingungen mittels when kann auf die Werte vor und nach dem Ereignis zugreifen: referencing old/new (nur bei for each row) Verwendung von Prozeduren in jeweiligen DBMS Syntax Sebastian Skritek Seite 35 Sebastian Skritek Seite 36 Datenmodellierung 6. Trigger 6.3. Beispiele Datenmodellierung Beispiele 6. Trigger 6.3. Beispiele Beispiele Beispiel Beispiel Professoren können nicht degradiert werden (ORACLE Syntax in Prozedur) Professoren können nicht degradiert werden (PostgreSQL Syntax) create trigger ke ineDeg radier ung before update on Professoren for each row when ( old . Rang is not null ) begin if : old . Rang = ’ C3 ’ and : new . Rang = ’ C2 ’ then : new . Rang := ’ C3 ’; end if ; if : old . Rang = ’ C4 ’ then : new . Rang := ’ C4 ’; end if ; if : new . Rang is null then : new . Rang := : old . Rang ; end if ; end Sebastian Skritek Seite 37 Datenmodellierung 7. Lernziele CREATE OR REPLACE FUNCTION ouf () RETURNS TRIGGER AS $$ BEGIN IF ( OLD . Rang = ’ C3 ’ AND NEW . Rang = ’ C2 ’) THEN RETURN OLD ; END IF ; RETURN NEW ; END ; $$ LANGUAGE plpgsql ; CREATE TRIGGER keineDeg radierung BEFORE UPDATE ON Profesoren FOR EACH ROW when ( OLD . Rang is not NULL ) EXECUTE PROCEDURE ouf (); Sebastian Skritek Datenmodellierung Lernziele Seite 38 8. Ausblick Ausblick Bachelor-LVAs: 184.686 – VU Datenbanksysteme (WS) SQL als Datenmanipulationssprache: Einfügen, Bearbeiten, und Löschen von Daten 184.705 – VU Semistrukturierte Daten (SS) 184.737 – VU Einführung in wissensbasierte Systeme Welche Arten von Integrtätsbedingungen gibt es ... Statische Integritätsbedingungen in SQL Insbesonders: Fremdschlüssel: Master-LVAs: • Referentielle Integrität in SQL 181.201 – SE Seminar aus Datenbanken Umgang mit zyklischen Abhängigkeiten 181.140 – VU Datenbanktheorie Trigger 184.734 – VU Foundations of Information Integration 184.247 – VU Deduktive Datenbanken ... Sebastian Skritek Seite 39 Sebastian Skritek Seite 40