Datenmodellierung

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