Vorlesung8 - Wiederholung: Referentielle Integrität

WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
Vorlesung #8
Wiederholung: Referentielle Integrität/
Embedded SQL
WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
„Fahrplan“
 Wiederholung
 Beziehungstypen 1:1, 1:n, n:m
 Primärschlüssel, Fremdschlüssel, referentielle Integrität
 Dynamische Intergritätsbedingungen - „Trigger“Konzept von Oracle
 Prozedurale Erweiterungen (Embedded SQL)





Beispiel Oracle PL/SQL
Einbettung von SQL in Wirtssprachen, C, C++
ODBC – Open Database Connectivity
JDBC – Java Database Connectivity
SQLJ – Einbettung von SQL in Java
 QBE – Query by Example
 Fazit und Ausblick Vorlesung #9
© Bojan Milijaš, 28.11.2007
Vorlesung #8 - Wiederholung Referentielle
Integrität / Embedded SQL
2
Relationales Modell
WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
 ... besteht aus Relationen bzw. Tabellen, die
zueinander in Relation stehen
 Relation = Beziehung
 Relation:
 Synonym für Tabelle und
 Synonym für Beziehung
 umgangssprachlich: Tabellen stehen in einer
Beziehung zu einander (Relationen stehein in
Relation zu einander )
 Das relationale Modell beschreibt die Beziehung
zwischen zwei Tabellen (Relationen) mittels
referentieller Integrität
© Bojan Milijaš, 28.11.2007
Vorlesung #8 - Wiederholung Referentielle
Integrität / Embedded SQL
3
WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
Beziehungstypen
 Zwei Tabellen T1, T2. Beziehungstypen





1:1, 1:n, n:m
m:1 entfällt, da analog zu 1:n
1:1 Ehemann <-> Ehefrau,
1:n Mannschaft <-> Spieler
n:m Studenten <-> Vorlesungen
 (* Notation Prof. Kemper, die meiste Literatur)
© Bojan Milijaš, 28.11.2007
T1/T2
1
n
1
1:1
1:n
m
m:1
n:m
Vorlesung #8 - Wiederholung Referentielle
Integrität / Embedded SQL
4
Beziehungstypen (fortgesetzt)
WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
 Notation Prof. Schwenkert
T1/T2 1
c
m
mc
1
1:1
1:c
1:m
1:mc
c
c:1
c:c
c:m
c:mc
n
n:1
n:c
n:m
n:mc
nc
nc:1
nc:m
nc:m
nc:mc
 c steht für „conditional“, d.h „ein“ oder „kein“
 Eigentlich 10 statt 16 Funktionalitäten, da Einträge
oberhalb der Diagonale analog zu Einträgen
unterhalb der Diagonale ist
© Bojan Milijaš, 28.11.2007
Vorlesung #8 - Wiederholung Referentielle
Integrität / Embedded SQL
5
Schlüsselbegriff
WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
 Primary Key – PK – Primärschlüssel
 identifiziert eindeutig eine Relation
 kann aus mehreren Attributen (Spalten) bestehen
 zusammengesetzter Primärschlüssen
 Beispiel: Relation Personen
 Personal_ID - einfacher PK
 (Vorname, Nachname, Geburtsort und Geburtsuhrzeit) –
zusammengesetzter PK
 Foreign Key – FK - Fremdschlüssel
 zeigt auf die Primärschlüsselspalte in der
Referenztabelle
 sorgt für referentielle Integrität
© Bojan Milijaš, 28.11.2007
Vorlesung #8 - Wiederholung Referentielle
Integrität / Embedded SQL
6
1:1 Beziehungen
WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
 Beispiele:
 Ehemann <-> Ehefrau
 Fußballverein <-> Trainer (*an einem Spieltag)
 können in eine einzige Relation bzw. Tabelle
zusammengefasst werden
 Stufe 1 (3 Tabellen)
 Männer <-> Ehen <-> Frauen
 FK  zusammengesetzter PK  FK
 Stufe 2 (2 Tabellen):
 Männer <-> Ehefrauen
 FK  PK
 Stufe 3 (1 Tabelle): Eheleute
 Zusammengesetzter PK
© Bojan Milijaš, 28.11.2007
Vorlesung #8 - Wiederholung Referentielle
Integrität / Embedded SQL
7
1:n Beziehungen
WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
 Beispiele:
 Mutter <-> Kinder
 Fußballverein <-> Spieler
 können in zwei sinnvolle Relationen (sinnvoll = ohne
Redundanz) bzw. Tabellen zusammengefasst werden
 Stufe 1 (3 Tabellen):
 Mütter <-> Mutter_Kind <-> Kinder
 FK  zusammengesetzter PK  FK
 Stufe 2 (2 Tabellen): Mütter <-> Kinder
 PK  FK
 Stufe 3 (1 Tabelle): Kinder (Redundanz – sämtliche
Informationen über Mutter wiederholt sich pro Kind!!!)
© Bojan Milijaš, 28.11.2007
Vorlesung #8 - Wiederholung Referentielle
Integrität / Embedded SQL
8
n:m Beziehungen
WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
 Beispiele:
 Menschen <-> befreundet_mit <-> Menschen
(rekursiv, Self-Join)
 Fußballvereine <-> spielen_in <-> Gaststadien
 können nicht sinnvoll zusammengefasst
werden, man braucht immer eine
Zuordnungsrelation
 Fußballvereine <-> spielen_in <->
Gaststadien
 FK  zusammengesetzter PK  FK
© Bojan Milijaš, 28.11.2007
Vorlesung #8 - Wiederholung Referentielle
Integrität / Embedded SQL
9
Dynamische
Integritätsbedingungen
WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
 Statische Integritätsbedingungen: PRIMARY KEY,
FOREIGN KEY, CHECK, UNIQUE etc.
 Dynamische Integritätsbedingungen: Bedingungen
an Zustandsänderungen bzw. Datenänderungen
werden mittels Trigger überprüft. Man kann mit
Triggern auch nachträgliche bzw. zusätzliche
Berechnungen durchführen.
 CREATE TRIGGER – eigenständiger Datenbankobjekt der
einer Tabelle zugeordnet wird, d.h. auf eine Tabelle
„aufpasst“
 Auslöser wird definiert (before update, for each row)
 Bedingungen gefolgt von Anweisungen
© Bojan Milijaš, 28.11.2007
Vorlesung #8 - Wiederholung Referentielle
Integrität / Embedded SQL
10
Trigger in Oracle Syntax
WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
create or replace trigger keineDegradierung
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;
© Bojan Milijaš, 28.11.2007
Vorlesung #8 - Wiederholung Referentielle
Integrität / Embedded SQL
11
Trigger in Oracle Syntax (2)
WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
Vereinfacht – nur DML Events, man kann auch DDL abfangen
CREATE [OR REPLACE] TRIGGER [schema .] trigger
{ BEFORE | AFTER | INSTEAD OF }
{ DELETE |
INSERT |
UPDATE [OF column [, column]...] }
[FOR EACH ROW]
[WHEN ( condition ) ]
{ pl/sql_block | call_procedure_statement }
© Bojan Milijaš, 28.11.2007
Vorlesung #8 - Wiederholung Referentielle
Integrität / Embedded SQL
12
Trigger in DB2 Syntax
© Bojan Milijaš, 28.11.2007
WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
Vorlesung #8 - Wiederholung Referentielle
Integrität / Embedded SQL
13
WS 2007/08
Prozedurale Erweiterung von
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
SQL – Oracle PL/SQL
 Trigger waren bzw. sind bereits prozedural!
 Prozedurale Erweiterung ist notwendig
 Aus theoretischer Sicht – um TouringVollständigkeit zu erreichen
 Aus praktischer Sicht - manche Probleme des
Alltags, lassen sich einfacher prozedural statt
deklarativ lösen
 Hauptbegriff: Datenbank Cursor
 Dynamisches SQL in PL/SQL – würde hier
den Rahmen sprengen
© Bojan Milijaš, 28.11.2007
Vorlesung #8 - Wiederholung Referentielle
Integrität / Embedded SQL
14
Oracle PL/SQL
WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
 Prozedurale Erweiterung von SQL
 Beispiel: Funktion Summe - rekursiv
CREATE FUNCTION Summe1 (n INTEGER)
RETURN INTEGER
IS
BEGIN
IF n = 0
THEN return 0;
ELSIF n = 1 THEN return 1;
ELSIF n > 1 THEN return n + Summe1(n - 1);
END IF;
END;
© Bojan Milijaš, 28.11.2007
Vorlesung #8 - Wiederholung Referentielle
Integrität / Embedded SQL
15
Oracle PL/SQL (2)
WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
 nicht rekursiv
 nicht rekursiv, schlau
FUNCTION Summe2(n INTEGER)
RETURN INTEGER
IS
result INTEGER DEFAULT 0;
v_n INTEGER DEFAULT n;
BEGIN
WHILE v_n >= 0
LOOP
result := result + v_n;
v_n := v_n - 1;
END LOOP;
return result;
END;
FUNCTION Summe3(n INTEGER)
RETURN INTEGER
IS
BEGIN
RETURN (n*(n+1))/2;
END;
© Bojan Milijaš, 28.11.2007
Vorlesung #8 - Wiederholung Referentielle
Integrität / Embedded SQL
16
WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
© Bojan Milijaš, 28.11.2007
Vorlesung #8 - Wiederholung Referentielle
Integrität / Embedded SQL
17
Oracle PL/SQL Beispiel
WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
 man verfügt über eine Tabelle (oder eine
View), die zu jeder Spiel-Saison einen oder
mehreren Trainer eines Vereins beinhaltet
 Man möchte für jeden Trainer SaisonIntervale bestimmen
 In SQL sehr umständlich, mit vielen „unsauberen“
Hilfskonstrukten
 Besser mit einer einfachen Schleife, die sich
Zustandsübergänge merkt
 Wie bringt aber man aber Daten aus einer
Tabelle in eine prozedurale Sprache hinein?
© Bojan Milijaš, 28.11.2007
Vorlesung #8 - Wiederholung Referentielle
Integrität / Embedded SQL
18
Deklarativ vs. Prozedural (2)
© Bojan Milijaš, 28.11.2007
WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
Vorlesung #8 - Wiederholung Referentielle
Integrität / Embedded SQL
19
Deklarativ vs. Prozedural (3)
WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
 Zeitintervall jedes Trainers
 Idee
 Sortiere nach der Saison
 Merke den Trainer
 Bei jeder weiteren Saison überprüfe, ob sich der
Trainer geändert hat, d.h. vergleich den aktuellen
Trainer mit dem Trainer der Vorsaison
 Wenn ja, dokumentiere die Änderung
 Wiederhole bis zur aktuellen Saison
 Cursor-FOR oder Cursor-WHILE Schleife 
vorgestellt in der Vorlesung
© Bojan Milijaš, 28.11.2007
Vorlesung #8 - Wiederholung Referentielle
Integrität / Embedded SQL
20
Cursor Beispiel (3)
WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
...
CURSOR c_club_trainer_saison
IS
SELECT saison, trainer, verein
FROM club_trainer_saison
ORDER BY saison;
...
BEGIN
FOR curvar IN c_club_trainer_saison
LOOP
... v_trainer := curvar.trainer; ...
END LOOP;
...
* Vollständiger Code wird Online gestellt 
© Bojan Milijaš, 28.11.2007
Vorlesung #8 - Wiederholung Referentielle
Integrität / Embedded SQL
21
WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
© Bojan Milijaš, 28.11.2007
Vorlesung #8 - Wiederholung Referentielle
Integrität / Embedded SQL
22
Einbettung in Wirtssprachen
„Embedded SQL“
WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
Mit Hilfe eines Präcompilers!!!
#include <stdio.h>
exec sql begin declare section;
varchar user_passwd[30];
int exMatrNr;
exec sql end declare section;
exec sql include SQLCA;
main()
{
printf("Name/Password:");
scanf("%", user_passwd.arr);
© Bojan Milijaš, 28.11.2007
Vorlesung #8 - Wiederholung Referentielle
Integrität / Embedded SQL
23
Einbettung in Wirtssprachen
„Embedded SQL“ (2)
WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
user_passwd.len=strlen(user_passwd.arr);
exec sql wheneversqlerror goto error;
exec sql connect :user_passwd;
while (1) {
printf("Matrikelnummer (0 zum beenden):");
scanf("%d", &ecMatrNr);
if (!exMatrNr) break;
exec sql delete from Studenten
where MatrNr= :exMatrNr;
}
exec sql commit work release;
exit(0);
© Bojan Milijaš, 28.11.2007
Vorlesung #8 - Wiederholung Referentielle
Integrität / Embedded SQL
24
Einbettung in Wirtssprachen
„Embedded SQL“ (3)
WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
error:
exec sql whenever sqlerror continue;
exec sql rollback work release;
printf("fehler aufgetreten!\n");
exit(-1);
}
© Bojan Milijaš, 28.11.2007
Vorlesung #8 - Wiederholung Referentielle
Integrität / Embedded SQL
25
Datenbankanbindungen
WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
 ODBC (Open Database Connecticity)
 Anbindung von Windows-Programmen
 Beispiel –Excel Zugriff auf Oracle
 JDBC (Java Database Connectivity)
 Eigentlich ein CLI Call-Level Interface
 SQLJ
 Echte Einbettung von SQL in Java, wie C, C++
 Wichtig bei allen Schnittstellen: immer an die
Voreinstellungen denken
 was passiert bei einem Fehler
 gibt es automatisch einen COMMIT oder ROLLBACK
© Bojan Milijaš, 28.11.2007
Vorlesung #8 - Wiederholung Referentielle
Integrität / Embedded SQL
26
WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
© Bojan Milijaš, 28.11.2007
Vorlesung #8 - Wiederholung Referentielle
Integrität / Embedded SQL
27
WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
© Bojan Milijaš, 28.11.2007
Vorlesung #8 - Wiederholung Referentielle
Integrität / Embedded SQL
28
WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
© Bojan Milijaš, 28.11.2007
Vorlesung #8 - Wiederholung Referentielle
Integrität / Embedded SQL
29
WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
© Bojan Milijaš, 28.11.2007
Vorlesung #8 - Wiederholung Referentielle
Integrität / Embedded SQL
30
WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
© Bojan Milijaš, 28.11.2007
Vorlesung #8 - Wiederholung Referentielle
Integrität / Embedded SQL
31
WS 2007/08
Datenbanksysteme
Mi 17:00 – 18:30
R 1.007
Vorlesung #8
Ende