2x2 - DBAI

Datenmodellierung
Datenmodellierung
Acknowledgments
Datenmodellierung
VU 184.685, WS 2015
Datenintegrität
Die Folien sind eine kleine Erweiterung der Folien von Katrin Seyr.
Sebastian Skritek
Institut für Informationssysteme
Technische Universität Wien
Sebastian Skritek
Seite 1
Datenmodellierung
1. Überblick
Sebastian Skritek
Datenmodellierung
Seite 2
2. Datenmanipulationssprache
Datenmanipulationssprache – Tupel Einfügen
Überblick
Einfügen von Tupeln in eine angelegte Tabelle
1
Nachtrag: SQL als Datenmanipulationssprache
2
Überblick
3
Integritätsbedingungen
4
Statische Integritätsbedingungen
5
Referentielle Integrität
Professoren ( PersNr : integer , Name : varchar (30) ,
Rang : character (2) , Raum : integer )
• Gewährleistung
• Referentielle Integrität in SQL
• Zyklische Abhängigkeiten
6
Trigger
Sebastian Skritek
Seite 3
Sebastian Skritek
Seite 4
Datenmodellierung
2. Datenmanipulationssprache
Datenmodellierung
Datenmanipulationssprache – Tupel Einfügen
2. Datenmanipulationssprache
Datenmanipulationssprache – Tupel Einfügen
Einfügen von Tupeln in eine angelegte Tabelle
Einfügen von Tupeln in eine angelegte Tabelle
Professoren ( PersNr : integer , Name : varchar (30) ,
Rang : character (2) , Raum : integer )
Professoren ( PersNr : integer , Name : varchar (30) ,
Rang : character (2) , Raum : integer )
Beispiel
Beispiel
Einfügen der Professorin Curie:
Einfügen der Professorin Curie:
insert into Professoren
values (2136 , ’ Curie ’ , ’ C4 ’ , 36);
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
Datenmodellierung
Seite 4
2. Datenmanipulationssprache
Sebastian Skritek
Datenmodellierung
Seite 4
2. Datenmanipulationssprache
Datenmanipulationssprache – Tupel Einfügen
Datenmanipulationssprache – Tupel Einfügen
Das Ergebnis einer Anfrage in eine Tabelle eintragen:
Das Ergebnis einer Anfrage in eine Tabelle eintragen:
Beispiel
Beispiel
Eintragen aller Studenten zur Vorlesung ‘Logik’:
Eintragen aller Studenten zur Vorlesung ‘Logik’:
hören ( MatrNr , VorlNr )
Studenten ( MatrNr , Name , Sem )
Vorlesungen ( VorlNr , Titel , SWS , PersNr )
hören ( MatrNr , VorlNr )
Studenten ( MatrNr , Name , Sem )
Vorlesungen ( VorlNr , Titel , SWS , PersNr )
insert into hören
Sebastian Skritek
insert into hören
select MatrNr , VorlNr
from Studenten , Vorlesungen
where Titel = ’ Logik ’;
Seite 5
Sebastian Skritek
Seite 5
Datenmodellierung
2. Datenmanipulationssprache
Datenmodellierung
Datenmanipulationssprache – Tupel Löschen
2. Datenmanipulationssprache
Datenmanipulationssprache – Tupel Löschen
Auflisten der zu löschenden Tupeln:
Auflisten der zu löschenden Tupeln:
Beispiel
Beispiel
Löschen des Herrn Kant aus der Professorentabelle:
Löschen des Herrn Kant aus der Professorentabelle:
delete from Professoren
values (2137 , ’ Kant ’ , ’ C4 ’ , 7);
delete from Professoren
values (2137 , ’ Kant ’ , ’ C4 ’ , 7);
Lösche alle Tupel die eine Bedingung erfüllen:
Lösche alle Tupel die eine Bedingung erfüllen:
Beispiel
Beispiel
Löschen des Herrn Kant aus der Professorentabelle:
Löschen des Herrn Kant aus der Professorentabelle:
delete from Professoren where PersNr =2137;
delete from Professoren where PersNr =2137;
delete from Professoren where PersNr < 2137;
Sebastian Skritek
Datenmodellierung
Seite 6
2. Datenmanipulationssprache
Sebastian Skritek
Datenmodellierung
Datenmanipulationssprache – Tupel Löschen
Seite 6
2. Datenmanipulationssprache
Datenmanipulationssprache – Tupel Löschen
Beispiel
Beispiel
Lösche alle Studenten, die die Vorlesung ‘Logik’ besuchen
Lösche alle Studenten, die die Vorlesung ‘Logik’ besuchen
hören ( MatrNr , VorlNr )
Studenten ( MatrNr , Name , Sem )
Vorlesungen ( VorlNr , Titel , SWS , PersNr )
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
Sebastian Skritek
Seite 7
Datenmodellierung
2. Datenmanipulationssprache
Datenmodellierung
Datenmanipulationssprache – Tupel Verändern
2. Datenmanipulationssprache
Datenmanipulationssprache – Tupel Verändern
Beispiel
Erhöhen der Semesteranzahl aller Studierender um 1:
update Studenten
set Semester = Semester + 1;
Sebastian Skritek
Datenmodellierung
Seite 8
2. Datenmanipulationssprache
Sebastian Skritek
Datenmodellierung
Datenmanipulationssprache – Tupel Verändern
Seite 8
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
Sebastian Skritek
Seite 9
Datenmodellierung
2. Datenmanipulationssprache
Datenmodellierung
Datenmanipulationssprache – Tupel Verändern
2. Datenmanipulationssprache
Datenmanipulationssprache – Tupel Verändern
Beispiel
Beispiel
Professoren ( PersNr : integer , Name : varchar (30) ,
Rang : character (2) , Raum : integer ,
Lehrbelastung: integer )
Vorlesungen ( VorlNr , Titel , SWS , PersNr )
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
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
Sebastian Skritek
Datenmodellierung
Seite 9
4. Integritätsbedingungen
Integritätsbedingungen
Überblick
Datenbankmanagementsysteme sind für die Erhaltung der
Datenkonsistenz zuständig.
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
Sebastian Skritek
Seite 11
Datenmodellierung
4. Integritätsbedingungen
Datenmodellierung
Integritätsbedingungen
4. Integritätsbedingungen
Integritätsbedingungen
Datenbankmanagementsysteme sind für die Erhaltung der
Datenkonsistenz zuständig.
Datenbankmanagementsysteme sind für die Erhaltung der
Datenkonsistenz zuständig.
Konsistenzerhaltung nach Systemfehler (Recovery) bzw. bei
Mehrbenutzerbetrieb (Concurrency Control) → LVA
Datenbanksysteme.
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
Datenmodellierung
Seite 11
4. Integritätsbedingungen
Sebastian Skritek
Datenmodellierung
Integritätsbedingungen
Seite 11
4. Integritätsbedingungen
Integritätsbedingungen
Integritätsbedingungen werden eingeteilt in:
Datenbankmanagementsysteme sind für die Erhaltung der
Datenkonsistenz zuständig.
statische Integritätsbedingungen: Bedingungen (constraints) an den
Zustand der Datenbasis, die in jedem Zustand der
Datenbank erfüllt sein müssen.
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
Sebastian Skritek
Seite 12
Datenmodellierung
4. Integritätsbedingungen
Datenmodellierung
Integritätsbedingungen
4. Integritätsbedingungen
Integritätsbedingungen
Integritätsbedingungen werden eingeteilt in:
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.
statische Integritätsbedingungen: Bedingungen (constraints) an den
Zustand der Datenbasis, die in jedem Zustand der
Datenbank erfüllt sein müssen.
Beispiele
Beispiele
Professoren haben Rang C2, C3, C4,
Professoren haben Rang C2, C3, C4,
Matrikelnummer der Studierenden ist eindeutig
Matrikelnummer der Studierenden ist eindeutig
Anmeldung zur Übung nur bei aufrechter Inskription
Anmeldung zur Übung nur bei aufrechter Inskription
dynamische Integritätsbedingungen: Bedingungen an
Zustandsänderungen in der Datenbank.
Sebastian Skritek
Datenmodellierung
Seite 12
4. Integritätsbedingungen
Sebastian Skritek
Datenmodellierung
Seite 12
4. Integritätsbedingungen
4.1. Statische Integritätsbedingungen
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.
Bekannte implizite Anforderungen an die Datenintegrität
Schlüssel: müssen innerhalb einer Relation eindeutig sein.
Beispiele
Beziehungskardinalitäten: die im ER Modell definierten Kardinalitäten
werden im Relationenmodell so umgesetzt, dass davon
abweichende Daten nicht eingegeben werden können.
Professoren haben Rang C2, C3, C4,
Matrikelnummer der Studierenden ist eindeutig
Attributdomänen: durch die Festlegung von Wertebereichen werden
Bedingungen an die Daten gestellt.
Anmeldung zur Übung nur bei aufrechter Inskription
Inklusion bei Generalisierung: Entities der Untertypen müssen auch in
Obertypen enthalten sein
dynamische Integritätsbedingungen: Bedingungen an
Zustandsänderungen in der Datenbank.
Beispiel
Professoren dürfen nur befördert werden
Sebastian Skritek
Seite 12
Sebastian Skritek
Seite 13
Datenmodellierung
4. Integritätsbedingungen
4.2. Statische Integritätsbedingungen in SQL
Datenmodellierung
4. Integritätsbedingungen
4.2. Statische Integritätsbedingungen in SQL
Statische Integritätsbedingungen in SQL
Statische Integritätsbedingungen in SQL
Schlüsselkandidaten: unique(Attributliste)
Schlüsselkandidaten: unique(Attributliste)
Primärschlüssel: primary key(Attributliste)
Primärschlüssel: primary key(Attributliste)
Fremdschlüssel: foreign key(Attributliste)
Fremdschlüssel: foreign key(Attributliste)
Keine Nullwerte erlaubt: not null
Defaultwert angeben: default
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
Datenmodellierung
Seite 14
4. Integritätsbedingungen
4.2. Statische Integritätsbedingungen in SQL
Statische Integritätsbedingungen in SQL
Sebastian Skritek
Datenmodellierung
Seite 14
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
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
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.
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!
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
Sebastian Skritek
Seite 15
Datenmodellierung
4. Integritätsbedingungen
4.2. Statische Integritätsbedingungen in SQL
Statische Integritätsbedingungen in SQL
Datenmodellierung
5. Referentielle Integrität
Referentielle Integrität
Beispiel
Schlüssel identifizieren ein Tupel einer Relation.
Studenten können sich nur über Vorlesungen prüfen lassen, die sie vorher
gehört haben.
Fremdschlüssel verweisen auf Tupel einer in Beziehung stehenden
Relation.
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
Datenmodellierung
Seite 16
5. Referentielle Integrität
Sebastian Skritek
Datenmodellierung
Referentielle Integrität
Seite 17
5. Referentielle Integrität
Referentielle Integrität
Schlüssel identifizieren ein Tupel einer Relation.
Schlüssel identifizieren ein Tupel einer Relation.
Fremdschlüssel verweisen auf Tupel einer in Beziehung stehenden
Relation.
Fremdschlüssel verweisen auf Tupel einer in Beziehung stehenden
Relation.
Beispiel
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.
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
Sebastian Skritek
Seite 17
Datenmodellierung
5. Referentielle Integrität
Datenmodellierung
Referentielle Integrität
5. Referentielle Integrität
Referentielle Integrität
Definition (Dangling References)
Definition (Dangling References)
Dangling References sind Verweise von Fremdschlüsseln auf nicht
existierende Datensätze
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
Datenmodellierung
Seite 18
5. Referentielle Integrität
5.1. Gewährleistung der referentiellen Integrität
Gewährleistung der referentiellen Integrität
Sebastian Skritek
Datenmodellierung
Seite 18
5. Referentielle Integrität
5.1. Gewährleistung der referentiellen Integrität
Gewährleistung der referentiellen Integrität
Theorem (referentielle 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
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)
πα (S) ⊆ πκ (R)
Erlaubte Änderungen sind also:
Einfügen von s in S, wenn s.α ∈ πκ (R)
Sebastian Skritek
Seite 19
Sebastian Skritek
Seite 19
Datenmodellierung
5. Referentielle Integrität
5.1. Gewährleistung der referentiellen Integrität
Gewährleistung der referentiellen Integrität
Datenmodellierung
5. Referentielle Integrität
5.1. Gewährleistung der referentiellen Integrität
Gewährleistung der referentiellen Integrität
Theorem (referentielle 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
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)
πα (S) ⊆ πκ (R)
Erlaubte Änderungen sind also:
Erlaubte Änderungen sind also:
Einfügen von s in S, wenn s.α ∈ πκ (R)
0
Einfügen von s in S, wenn s.α ∈ πκ (R)
0
Verändern Tupels von s zu s 0 in S, wenn s 0 .α ∈ πκ (R)
Verändern Tupels von s zu s in S, wenn s .α ∈ πκ (R)
Sebastian Skritek
Datenmodellierung
Verändern von r .κ in R, wenn σα=r .κ (S) = ∅, d.h. es gibt keine
Verweise von S auf r
Seite 19
5. Referentielle Integrität
5.1. Gewährleistung der referentiellen Integrität
Gewährleistung der referentiellen Integrität
Sebastian Skritek
Datenmodellierung
Seite 19
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:
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
Sebastian Skritek
Seite 20
Datenmodellierung
5. Referentielle Integrität
5.2. Referentielle Integrität in SQL
Datenmodellierung
Referentielle Integrität in SQL
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:
Beschreibungsmöglichkeit für jeden der drei Schlüsselbegriffe wie folgt:
(Kandidaten)-Schlüssel: unique
(Kandidaten)-Schlüssel: unique
Primärschlüssel: primary key(Attributliste)
Primärschlüssel: primary key(Attributliste)
Fremdschlüssel: foreign key(Attributliste)
Fremdschlüssel: foreign key(Attributliste)
Kennzeichnung der Tabelle, auf die verwiesen wird:
references (Tabellenname)
Sebastian Skritek
Datenmodellierung
Seite 20
5. Referentielle Integrität
5.2. Referentielle Integrität in SQL
Sebastian Skritek
Datenmodellierung
Referentielle Integrität in SQL
Seite 20
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:
Beschreibungsmöglichkeit für jeden der drei Schlüsselbegriffe wie folgt:
(Kandidaten)-Schlüssel: unique
(Kandidaten)-Schlüssel: unique
Primärschlüssel: primary key(Attributliste)
Primärschlüssel: primary key(Attributliste)
Fremdschlüssel: foreign key(Attributliste)
Fremdschlüssel: foreign key(Attributliste)
Kennzeichnung der Tabelle, auf die verwiesen wird:
Kennzeichnung der Tabelle, auf die verwiesen wird:
references (Tabellenname)
references (Tabellenname)
Einhaltung der Referenziellen Integrität bei Updates, Löschen:
Einhaltung der Referenziellen Integrität bei Updates, Löschen:
on update
{no action | cascade | set null | set default}
on update
{no action | cascade | set null | set default}
on delete
{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
Sebastian Skritek
Seite 20
Datenmodellierung
5. Referentielle Integrität
5.2. Referentielle Integrität in SQL
Datenmodellierung
Referentielle Integrität in SQL
5. Referentielle Integrität
5.2. Referentielle Integrität in SQL
Referentielle Integrität in SQL
Beispiel
Beispiel
create table Professoren
( PersNr
integer primary key ,
Name
varchar (30) not null ,
...
);
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
Sebastian Skritek
Seite 21
Datenmodellierung
Referentielle Integrität in SQL
5. Referentielle Integrität
5.2. Referentielle Integrität in SQL
Referentielle Integrität in SQL
Beispiel (Fortsetzung)
Beispiel (Fortsetzung)
Vorlesung
...
gelesenVon
...
...
...
..
.
Professoren
Vorlesung
PersNr
...
...
gelesenVon
2137
2137
...
...
2125
2125
...
...
2126
..
.
2136
..
.
...
..
.
...
..
.
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
Sebastian Skritek
Seite 22
Datenmodellierung
5. Referentielle Integrität
5.2. Referentielle Integrität in SQL
Datenmodellierung
Referentielle Integrität in SQL
5. Referentielle Integrität
5.2. Referentielle Integrität in SQL
Referentielle Integrität in SQL
Beispiel (Fortsetzung)
Beispiel (Fortsetzung)
update Professoren set
PersNr =1111
where PersNr =2137;
update Professoren set
PersNr =1111
where PersNr =2137;
Einhaltung der referentiellen Integrität durch Kaskadieren
(on update cascade)
Einhaltung der referentiellen Integrität durch Kaskadieren
(on update cascade)
Vorlesung
Sebastian Skritek
Datenmodellierung
Seite 23
5. Referentielle Integrität
5.2. Referentielle Integrität in SQL
...
gelesenVon
...
...
...
..
.
...
1111
1111
...
2125
2125
...
2126
..
.
2136
..
.
...
..
.
Sebastian Skritek
Seite 23
Datenmodellierung
Referentielle Integrität in SQL
Professoren
PersNr
5. Referentielle Integrität
5.2. Referentielle Integrität in SQL
Referentielle Integrität in SQL
Beispiel (Fortsetzung)
Beispiel (Fortsetzung)
delete from Professoren where PersNr =2125;
delete from Professoren where PersNr =2125;
Einhaltung der referentiellen Integrität durch “auf NULL setzen”
(on delete set NULL)
Vorlesung
Sebastian Skritek
Seite 24
Sebastian Skritek
...
gelesenVon
...
1111
...
NULL
...
..
.
2126
..
.
Professoren
PersNr
...
1111
...
2136
..
.
...
..
.
Seite 24
Datenmodellierung
5. Referentielle Integrität
5.2. Referentielle Integrität in SQL
Datenmodellierung
Kaskadierendes Löschen
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.
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
Sebastian Skritek
Datenmodellierung
Kaskadierendes Löschen
Seite 25
5. Referentielle Integrität
5.2. Referentielle Integrität in SQL
Universitätsschema mit Integritätsbedingungen
Vorsicht bei der Verwendung von on delete cascade. Es kann zu
kaskadierdendem Löschen kommen.
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 hören
(...
VorlNr integer references Vorlesungen
on delete cascade );
Logik
Sokrates
Mäeutik
Ethik
Sebastian Skritek
Schopenhauer
Theophrastos
Seite 25
Sebastian Skritek
Seite 26
Datenmodellierung
5. Referentielle Integrität
5.2. Referentielle Integrität in SQL
Datenmodellierung
Universitätsschema mit Integritätsbedingungen
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 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 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
Datenmodellierung
Seite 26
5. Referentielle Integrität
5.2. Referentielle Integrität in SQL
Sebastian Skritek
Datenmodellierung
Universitätsschema mit Integritätsbedingungen
Seite 26
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 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
Sebastian Skritek
Seite 27
Datenmodellierung
5. Referentielle Integrität
5.2. Referentielle Integrität in SQL
Datenmodellierung
Universitätsschema mit Integritätsbedingungen
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 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
Datenmodellierung
Seite 28
5. Referentielle Integrität
5.3. Zyklische Abhängigkeiten
Sebastian Skritek
Datenmodellierung
Seite 28
5. Referentielle Integrität
5.3. Zyklische Abhängigkeiten
Zyklische Abhängigkeiten
Zyklische Abhängigkeiten
Beispiel (chicken/egg)
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:
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
Sebastian Skritek
Seite 29
Datenmodellierung
5. Referentielle Integrität
5.3. Zyklische Abhängigkeiten
Datenmodellierung
5. Referentielle Integrität
5.3. Zyklische Abhängigkeiten
Zyklische Abhängigkeiten
Zyklische Abhängigkeiten
Beispiel (chicken/egg)
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:
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 );
create table chicken
( cID integer primary key ,
eID integer references egg );
ERROR: relation ‘‘egg’’ does not exist
ERROR: relation ‘‘egg’’ does not exist
create table egg
( eID integer primary key ,
cID integer references chicken );
Sebastian Skritek
Datenmodellierung
Seite 29
5. Referentielle Integrität
5.3. Zyklische Abhängigkeiten
Sebastian Skritek
Datenmodellierung
Zyklische Abhängigkeiten
Seite 29
5. Referentielle Integrität
5.3. Zyklische Abhängigkeiten
Zyklische Abhängigkeiten
Lösung: verwenden Sie den alter table Befehl:
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
Sebastian Skritek
Seite 30
Datenmodellierung
5. Referentielle Integrität
5.3. Zyklische Abhängigkeiten
Datenmodellierung
Zyklische Abhängigkeiten
5. Referentielle Integrität
5.3. Zyklische Abhängigkeiten
Zyklische Abhängigkeiten
Lösung: verwenden Sie den alter table Befehl:
Beispiel (chicken/egg)
Beispiel (chicken/egg)
Das Anlegen der Tabellen funktioniert, aber:
create table chicken ( cID integer primary key ,
eID integer );
insert into chicken values (1 ,11);
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 30
5. Referentielle Integrität
5.3. Zyklische Abhängigkeiten
Sebastian Skritek
Datenmodellierung
Zyklische Abhängigkeiten
Seite 31
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)
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
Sebastian Skritek
Seite 32
Datenmodellierung
5. Referentielle Integrität
5.3. Zyklische Abhängigkeiten
Datenmodellierung
Zyklische Abhängigkeiten
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).
Lösung: verzögern Sie die Überprüfung der Bedingung bis zum Ende der
Transaktion (Definition Transaktion in LVA Datenbanksysteme).
Beispiel (chicken/egg)
Beispiel (chicken/egg)
alter table chicken add constraint chickenrefegg
foreign key ( eID ) references egg
initially deferred deferrable ;
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
Datenmodellierung
Seite 32
6. Trigger
Sebastian Skritek
Datenmodellierung
Seite 32
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 Datenintegritä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 ausgeführt
(action).
Achtung: zyklische Trigger oder Endlosschleifen!
Sebastian Skritek
Seite 33
Sebastian Skritek
Seite 34
Datenmodellierung
6. Trigger
6.1. Anwendungsgebiete
Datenmodellierung
Anwendungsgebiete
6. Trigger
6.2. Syntax
Syntax
Trigger finden Verwendung bei:
Berechnung gespeicherter abgeleiteter Attribute
Definition des Triggers: create trigger
Beispiel
Auslösende Ereignisse: insert, update, delete
Berechnung des Bruttopreises bei gespeichertem Nettopreis und
Umsatzsteuersatz
“feuert” vor oder nach Ausführung: before/after insert
auf Tupel- bzw. Queryebene: for each row/statement
einschränkende Bedingungen mittels when
allgemeinen Bedingungen
kann auf die Werte vor und nach dem Ereignis zugreifen:
referencing old/new (nur bei for each row)
Beispiele
Alle Mitarbeiter, die mehr als 5 SWS Vorlesungen abhalten, bekommen
einen Gehaltsbonus (+5%).
Professoren können nicht degradiert werden
Sebastian Skritek
Datenmodellierung
Verwendung von Prozeduren in jeweiligen DBMS Syntax
Seite 34
6. Trigger
6.3. Beispiele
Sebastian Skritek
Datenmodellierung
Beispiele
Seite 35
6. Trigger
6.3. Beispiele
Beispiele
Beispiel
Beispiel
Professoren können nicht degradiert werden (ORACLE Syntax in
Prozedur)
Alle Mitarbeiter, die mehr als 5 SWS Vorlesungen abhalten, bekommen
einen Gehaltsbonus (+5%).
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
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
Sebastian Skritek
Seite 37
Datenmodellierung
6. Trigger
6.3. Beispiele
Datenmodellierung
Beispiele
7. Lernziele
Lernziele
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 ;
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
CREATE TRIGGER ke ineDeg radier ung
BEFORE UPDATE ON Profesoren
FOR EACH ROW
when ( OLD . Rang is not NULL )
EXECUTE PROCEDURE ouf ();
Sebastian Skritek
Datenmodellierung
Seite 38
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
Sebastian Skritek
Seite 39