2x2 - DBAI

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