Vorlesung8

SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
Vorlesung #8
SQL (Teil 3)
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
„Fahrplan“
 Besprechung der Übungsaufgaben
 Rekursion
 Rekursion in SQL-92
 Rekursion in DBMS-“Dialekten“ (Oracle und DB2)
 Views (Sichten) - gespeicherte Abfragen
 Gewährleistung der logischen Datenunabhängigkeit
 Modellierung von Generalisierung
 UPDATE-fähige Sichten
 Datenintegrität
 Statische und dynamische Bedingungen
 Referentielle Integrität (primary key, foreign key)
 Propagieren der Primärschlüsselveränderungen (cascade)
 Ausblick Vorlesung #9, SQL Teil 4
© Bojan Milijaš, 05.05.2004
Vorlesung #8 - SQL (Teil 3)
2
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
Rekursion
 Rekursive Relation voraussetzen in UNI Schema
voraussetzen : {[Vorgänger, Nachfolger]}
 „Welche Vorlesungen muss man hören, um die
Vorlesung „Der Wiener Kreis“ zu verstehen?
SELECT Vorgaenger
FROM voraussetzen vs, Vorlesungen vo
WHERE vs.Nachfolger = vo.VorlNr
AND vo.Titel = 'Der Wiener Kreis' ;
© Bojan Milijaš, 05.05.2004
Vorlesung #8 - SQL (Teil 3)
3
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
Rekursion (2)
 Das sind aber nur die direkten Vorgänger, bzw.
Vorgänger erster Stufe!
 Wie bekommt man alle?
 Zunächst Vorgänger zweiter Stufe:
SELECT Vorgaenger
FROM voraussetzen
WHERE Nachfolger IN (SELECT Vorgaenger
FROM voraussetzen, Vorlesungen
WHERE Nachfolger = VorlNr
AND Titel = 'Der Wiener Kreis');
© Bojan Milijaš, 05.05.2004
Vorlesung #8 - SQL (Teil 3)
4
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
Rekursion (3)
SELECT Vogänger
FROM voraussetzen
WHERE Nachfolger IN (SELECT Vorgänger
FROM voraussetzen, Vorlesungen
WHERE Nachfolger = VorlNr
AND Titel = `Der Wiener Kreis´);
Entschachtelung
SELECT v1.Vogänger
FROM voraussetzen v1, voraussetzen v2, Vorlesungen v
WHERE v1.Nachfolger = v2.Vorgänger
AND v2.Nachfolger = v.VorlNr
AND v.Titel = `Der Wiener Kreis´;
© Bojan Milijaš, 05.05.2004
Vorlesung #8 - SQL (Teil 3)
5
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
Rekursion (4)
 Man kann die gewonenne entschachtelte
Abfrage verallgemeinern für die Vorgänger
n-ten Stufe
SELECT v1.Vogänger
FROM voraussetzen v1, ..., voraussetzen v_n-1, voraussetzen vn,
Vorlesungen v
WHERE v1.Nachfolger = v2.Vorgänger
AND ...
AND v_n-1.Nachfolger, vn.Vorgänger
AND vn.Nachfolger = v.Vorgänger
AND v.Titel = `Der Wiener Kreis´;
© Bojan Milijaš, 05.05.2004
Vorlesung #8 - SQL (Teil 3)
6
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
Rekursion (5)
 (-) sehr umständlich zu formulieren
 (-) ineffizient bei der Durchführung
 (-) leider in SQL-Standard nicht anders
möglich  SQL ist nicht Turing-vollständig,
SQL ist deklarativ, keine Schleifen, keine
Kontrollverzweigungen, keine GO TO, JUMP
Befehle oder ähnliches
 Das Finden aller Vorgänger nennt man
allgemein „Berechnen der transitiver
Hülle“
© Bojan Milijaš, 05.05.2004
Vorlesung #8 - SQL (Teil 3)
7
Rekursion (6) –
Transitive Hülle voraussetzen
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
Der Wiener Kreis
Wissenschaftstheorie
Erkenntnistheorie
Bioethik
Ethik
Mäeutik
Grundzüge
© Bojan Milijaš, 05.05.2004
Vorlesung #8 - SQL (Teil 3)
8
Rekursion (7) – Transitive Hülle
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
transA,B(R)= {(a,b)  k  IN (1, ..., k  R (
1.A= 2.B 

k-1.A= k.B 
1.A= a 
k.B= b))}
© Bojan Milijaš, 05.05.2004
Vorlesung #8 - SQL (Teil 3)
9
Rekursion (8) –
Oracle CONNECT BY Konstrukt
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
select Titel
from Vorlesungen
where VorlNr in
(select Vorgaenger
from voraussetzen
CONNECT BY Nachfolger = PRIOR Vorgaenger
START WITH Nachfolger =
(select VorlNr
from Vorlesungen
where Titel= 'Der Wiener Kreis'));
© Bojan Milijaš, 05.05.2004
Vorlesung #8 - SQL (Teil 3)
10
Rekursion (9) in IBM DB2
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
with TransVorl (Vorg, Nachf)
as (select Vorgaenger, Nachfolger from voraussetzen
union all
select t.Vorg, v.Nachfolger
from TransVorl t, voraussetzen v
where t.Nachf= v.Vorgaenger)
select Titel from Vorlesungen where VorlNr in
(select Vorg from TransVorl where Nachf in
(select VorlNr from Vorlesungen
where Titel= 'Der Wiener Kreis') );
© Bojan Milijaš, 05.05.2004
Vorlesung #8 - SQL (Teil 3)
11
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
Sichten (VIEWs)
 „Aussenstehende“ – d.h. Datenbank-Benutzer wollen
wissen, welcher Professor welche Vorlesungen liest?
 Benutzer wissen nichts von Schlüsseln (künstliche
IDs), JOINs, verschiedenen Tabellen usw.
CREATE VIEW ProfVorlesung
AS
SELECT Name, Titel
FROM Professoren, Vorlesungen
WHERE PersNr = gelesenVon;
© Bojan Milijaš, 05.05.2004
Vorlesung #8 - SQL (Teil 3)
12
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
Sichten (2)
SELECT *
FROM ProfVorlesung;
NAME
TITEL
Kant
Grundzuege
...
...
(+) Wir zeigen den Benutzern genau das, was Sie
sehen wollen  Benutzerfreundlichkeit
(+) Wir können die Informationen verbergen, die
Benutzer nicht sehen wollen oder nicht sehen dürfen
 Datenschutz und Sicherheit
(+) Wir können darunterliegende Basis-Tabellen
verändern. Solange die Sichten angepasst werden,
merken die Benutzer nichts  logische
Datenunabhängigkeit
© Bojan Milijaš, 05.05.2004
Vorlesung #8 - SQL (Teil 3)
13
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
Sichten (3) - logische
Datenunabhängigkeit
Benutzer 1
Benutzer 2
Sicht 1
Sicht 2
Sicht 3
Relation 1
Relation 2
Relation 3
Logische
Datenunabhängigkeit
Physische
Datenunabhängigkeit
© Bojan Milijaš, 05.05.2004
Vorlesung #8 - SQL (Teil 3)
14
Sichten (4) – Beispiel
logische Datenunabhängigkeit
Studenten
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
Internet-Besucher
CREATE VIEW ProfVorlesung
CREATE VIEW ProfVorlesung
AS
AS SELECT Name, Titel
ProfVerlesung
FROM Dozenten
SELECT Name, Titel
NATURAL JOIN lesen
FROM Professoren, Vorlesungen
NATURAL JOIN Kurse;
WHERE PersNr = gelesenVon;
Professoren Vorlesungen
lesen
Kurse
Dozenten
© Bojan Milijaš, 05.05.2004
Vorlesung #8 - SQL (Teil 3)
15
Sichten (5) – Modellierung
der Generalisierungen
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
 Im relationalen Modell lassen sich Inklusion und
Vererbung nicht elegant modellieren
Angestellte : {[PersNr,Name]}
Professoren: {[PersNr,Rang,Raum]}
Assistenten: {[PersNr,Fachgebiet,Boss]}
 Wenn man die Relationen eins zu eins in eine SQL
Datenbank umsetzt, muss man sehr oft explizit
„joinen“, z.B. jedes Mal, wenn man den Namen (oder
nach dem Namen) eines Professors oder eines
Assistenten sucht.
© Bojan Milijaš, 05.05.2004
Vorlesung #8 - SQL (Teil 3)
16
Sichten (6) – Untertyp als Sicht
create table Angestellte
(PersNr integer not null,
Name varchar (30) not null);
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
create view Professoren as
select *
from Angestellte a, ProfDaten d
create table ProfDaten
(PersNr integer not null,
Rang character(2),
Raum integer);
where a.PersNr=d.PersNr;
create view Assistenten as
create table AssiDaten
(PersNr
integer not null,
Fachgebiet varchar(30),
Boss
integer);
© Bojan Milijaš, 05.05.2004
select *
from Angestellte a, AssiDaten d
where a.PersNr=d.PersNr;
Vorlesung #8 - SQL (Teil 3)
17
Sichten (7) – Obertyp als Sicht
create table Professoren
(PersNr integer,
Name varchar (30),
Rang character (2),
Raum integer);
create table Assistenten
(PersNr integer,
Name varchar (30),
Fachgebiet varchar (30),
Boss integer);
© Bojan Milijaš, 05.05.2004
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
create view Angestellte as
(select PersNr, Name
from Professoren)
union
(select PersNr, Name
from Assistenten);
Vorlesung #8 - SQL (Teil 3)
18
Sichten (8) – Modellierung
der Generalisierungen
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
 Sowohl Untertypen als auch Obertypen kann man als
Sichten modellieren
 Erste Alternative: Obertyp(en) als Sicht(en), Untertyp(en) als
Tabelle(n)
 Zweite Alternative: Obertyp(en) als Tabelle(n), Untertyp(en)
als Sicht(en)
 Man spart sich bei den Zugriffen zusätzliche Joins,
aber es entstehen Probleme beim Ändern oder
Hinzufügen der Daten, da Sichten i.a. nicht
veränderbar sind
 Die Zugriffe auf Tabellen sind i.a. Schneller und
hinsichtlich der Datenänderungen unproblematisch,
so dass man die Sichten für „unwichtigere“ Entities
nimmt
© Bojan Milijaš, 05.05.2004
Vorlesung #8 - SQL (Teil 3)
19
Sichten (9) –
UPDATE-Fähigkeit
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
 Sichten sind immer veränderbar im Bezug auf DDLOperation, hier ist aber DML gemeint!
 Sichten sind i.a. nicht UPDATE fähig, da das DBMS
bei einer UPDATE, DELETE oder INSERT Operation
auf einer Sicht nicht weiß, welche Basis-Tabelle wie
zu verändern ist:
 wenn Sichten Duplikatelimierung und Aggregatfunktionen
(DISTINCT, GROUP BY usw.) beinhalten
 wenn der Schlüssel der zugrundeliegenden Tabelle(n) nicht
enthalten ist
 Wenn durch das INSERT, UPDATE oder DELETE Statement
mehr als eine Tabelle referenziert wird
© Bojan Milijaš, 05.05.2004
Vorlesung #8 - SQL (Teil 3)
20
Sichten (10) –
UPDATE-Fähigkeit
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
Beispiel einer nicht UPDATABLE View:
create view WieHartAlsPrüfer (PersNr, Durchschnittsnote) as
select PersNr, avg(Note)
from prüfen
group by PersNr;
alle Sichten
theoretisch änderbare Sichten
in SQL änderbare Sichten
© Bojan Milijaš, 05.05.2004
Vorlesung #8 - SQL (Teil 3)
21
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
Datenintegrität
 Integitätsbedingungen bis jetzt




Schlüssel  Eindeutigkeit
Beziehungskardinalitäten (min,max Notation)
Attributdomänen (NUMBER, CHAR, DATE)
Inklusion bei Generalisierung (Untertyp immer im Obertyp
enthalten)
 statische Integritätsbedingungen
 Bedingungen an den Zustand der Datenbasis
 Mit Datenbank-CONSTRAINTs realisiert
 dynamische Integritätsbedingungen
 Bedingungen an Zustandsübergänge
 Mit Datenbank-TRIGGERn realisiert
* engl. CONSTRAINT = Bedingung, TRIGGER = Auslöser
© Bojan Milijaš, 05.05.2004
Vorlesung #8 - SQL (Teil 3)
22
Statische CONSTRAINTs
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
 NOT NULL
 UNIQUE
 CHECK (Regel)
 Vorisicht: CHECK ist auch dann erfüllt, wenn der logische
Vergleich einen NULL-Wert zurückliefert
CREATE TABLE MyProfessoren
(
PersNr NUMBER(5,0) UNIQUE,
Name VARCHAR2(30) NOT NULL,
Rang CHAR(2) CHECK (Rang IN ('C1', 'C2', 'C3','C4') )
);
© Bojan Milijaš, 05.05.2004
Vorlesung #8 - SQL (Teil 3)
23
Statische CONSTRAINTs (2)
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
 Man kann CONSTRAINTs nachträglich definieren
ALTER TABLE myprofessoren
ADD CHECK (Rang IN ('C1', 'C2', 'C3','C4') );
 löschen, verändern, suchen, auflisten, ein- und
ausschalten, validieren (siehe SQL-Manual des
jeweiligen DBMS, hier Oracle Syntax für das
Löschen)
ALTER TABLE myprofessoren
DROP CONSTRAINT sys_c003798;
 Dynamische Constraints  mit Triggern nächstes
Mal (Vorlesung #9)
© Bojan Milijaš, 05.05.2004
Vorlesung #8 - SQL (Teil 3)
24
Referentielle Integrität
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
 Sorgt dafür, dass die Beziehung zwischen dem
Primärschlüssel und dem Fremdschlüssel bestehen
bleibt (dass die Referenz - der Verweis - erhalten
bleibt)
 Fremdschlüssel müssen auf existierende Tupel
verweisen oder einen Nullwert enthalten
 Beispiel gelesenVon  PersNr
CREATE TABLE Professoren
(PersNr INTEGER PRIMARY KEY ...)
(CREATE TABLE Vorlesungen
gelesenVon INTEGER REFERENCES Professoren ...)
© Bojan Milijaš, 05.05.2004
Vorlesung #8 - SQL (Teil 3)
25
Referentielle Integrität (2)
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
 Schlüsselkandidat UNIQUE CONSTRAINT
 Primärschlüssel PRIMARY KEY
 Fremdschlüssel FOREIGN KEY (auch implizit
durch das Wort REFERENCES in TabellenDefinition)
 FOREIGN KEYs können auch NULL Werte
enthalten
 UNIQUE FOREIGN KEY modelliert 1:1
Beziehung
© Bojan Milijaš, 05.05.2004
Vorlesung #8 - SQL (Teil 3)
26
Einhaltung referentieller
Integrität

Änderung von referenzierten Daten




Default: Zurückweisen der Änderungsoperation
Propagieren der Änderungen: cascade
Verweise auf Nullwert setzen: set null
Dies ergibt folgende Möglichkeiten bei der
Festlegung des CONSTRAINTs in der TabellenDefinition



SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
ON [ UPDATE | DELETE ] [ SET NULL | CASCADE ]
Kaskadierendes Löschen mit Vorsicht geniessen!
Beispiel: wenn in „Vorlesungen“ und „hören“
kaskadierend gelöscht wird, verliert man die beim
Löschen eines Professors die Information welcher
Student was gehört hat.
© Bojan Milijaš, 05.05.2004
Vorlesung #8 - SQL (Teil 3)
27
UNI Schema mit Constraints


SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
Kemper Seite 157
In der Vorlesung verteilt
© Bojan Milijaš, 05.05.2004
Vorlesung #8 - SQL (Teil 3)
28
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
Fazit und Ausblick

Fazit SQL Teil 1 bis 3




SQL Teil 1 – Datentypen, einfache Abfragen
SQL Teil 2 – komplexe Abfragen, Unterabfragen
SQL Teil 3 – Rekursion, Views, Constraints
Ausblick SQL Teil 4





Trigger
Prozedurale Erweiterungen (PL/SQL)
Einbettung in C,C++,Java
SQL Schnittstellen JDBC,ODBC
Query By Example QBE
© Bojan Milijaš, 05.05.2004
Vorlesung #8 - SQL (Teil 3)
29
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
Vorlesung #8
Ende