SQL (Teil 3)

WS 2010/11
Datenbanksysteme
Fr 15:15 – 16:45
R 1.008
Vorlesung #5
SQL (Teil 3)
WS 2010/11
Datenbanksysteme
Fr 15:15 – 16:45
R 1.008
„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 #6, SQL Teil 4
© Bojan Milijaš, 12.11.2010
Vorlesung #5 - SQL (Teil 3)
2
WS 2010/11
Datenbanksysteme
Fr 15:15 – 16:45
R 1.008
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š, 12.11.2010
Vorlesung #5 - SQL (Teil 3)
3
WS 2010/11
Datenbanksysteme
Fr 15:15 – 16:45
R 1.008
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š, 12.11.2010
Vorlesung #5 - SQL (Teil 3)
4
WS 2010/11
Datenbanksysteme
Fr 15:15 – 16:45
R 1.008
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š, 12.11.2010
Vorlesung #5 - SQL (Teil 3)
5
WS 2010/11
Datenbanksysteme
Fr 15:15 – 16:45
R 1.008
Rekursion (4)
Man kann die gewonnene 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š, 12.11.2010
Vorlesung #5 - SQL (Teil 3)
6
WS 2010/11
Datenbanksysteme
Fr 15:15 – 16:45
R 1.008
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š, 12.11.2010
Vorlesung #5 - SQL (Teil 3)
7
Rekursion (6) –
Transitive Hülle voraussetzen
WS 2010/11
Datenbanksysteme
Fr 15:15 – 16:45
R 1.008
Der Wiener Kreis
Wissenschaftstheorie
Erkenntnistheorie
Bioethik
Ethik
Mäeutik
Grundzüge
© Bojan Milijaš, 12.11.2010
Vorlesung #5 - SQL (Teil 3)
8
Rekursion (7) – Transitive Hülle
WS 2010/11
Datenbanksysteme
Fr 15:15 – 16:45
R 1.008
transA,B(R)= {(a,b) | ∃k ∈ IN (∃Γ1, ..., Γk ∈ R (
Γ1.A= Γ2.B Λ
M
Γk-1.A= Γk.B Λ
Γ1.A= a Λ
Γk.B= b))}
© Bojan Milijaš, 12.11.2010
Vorlesung #5 - SQL (Teil 3)
9
Rekursion (8) –
Oracle CONNECT BY Konstrukt
WS 2010/11
Datenbanksysteme
Fr 15:15 – 16:45
R 1.008
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š, 12.11.2010
Vorlesung #5 - SQL (Teil 3)
10
Rekursion (9) in IBM DB2
WS 2010/11
Datenbanksysteme
Fr 15:15 – 16:45
R 1.008
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š, 12.11.2010
Vorlesung #5 - SQL (Teil 3)
11
WS 2010/11
Datenbanksysteme
Fr 15:15 – 16:45
R 1.008
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š, 12.11.2010
Vorlesung #5 - SQL (Teil 3)
12
WS 2010/11
Datenbanksysteme
Fr 15:15 – 16:45
R 1.008
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š, 12.11.2010
Vorlesung #5 - SQL (Teil 3)
13
WS 2010/11
Datenbanksysteme
Fr 15:15 – 16:45
R 1.008
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š, 12.11.2010
Vorlesung #5 - SQL (Teil 3)
14
WS 2010/11
Datenbanksysteme
Fr 15:15 – 16:45
R 1.008
Sichten (4) – Beispiel
logische Datenunabhängigkeit
Studenten
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š, 12.11.2010
Vorlesung #5 - SQL (Teil 3)
15
Sichten (5) – Modellierung
der Generalisierungen
WS 2010/11
Datenbanksysteme
Fr 15:15 – 16:45
R 1.008
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š, 12.11.2010
Vorlesung #5 - SQL (Teil 3)
16
Sichten (6) – Untertyp als Sicht
create table Angestellte
(PersNr integer not null,
Name varchar (30) not null);
create table ProfDaten
(PersNr integer not null,
Rang character(2),
Raum integer);
WS 2010/11
Datenbanksysteme
Fr 15:15 – 16:45
R 1.008
create view Professoren as
select *
from Angestellte a, ProfDaten d
where a.PersNr=d.PersNr;
create view Assistenten as
create table AssiDaten
(PersNr
integer not null,
Fachgebiet varchar(30),
Boss
integer);
© Bojan Milijaš, 12.11.2010
select *
from Angestellte a, AssiDaten d
where a.PersNr=d.PersNr;
Vorlesung #5 - 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š, 12.11.2010
WS 2010/11
Datenbanksysteme
Fr 15:15 – 16:45
R 1.008
create view Angestellte as
(select PersNr, Name
from Professoren)
union
(select PersNr, Name
from Assistenten);
Vorlesung #5 - SQL (Teil 3)
18
Sichten (8) – Modellierung
der Generalisierungen
WS 2010/11
Datenbanksysteme
Fr 15:15 – 16:45
R 1.008
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š, 12.11.2010
Vorlesung #5 - SQL (Teil 3)
19
WS 2010/11
Datenbanksysteme
Fr 15:15 – 16:45
R 1.008
Sichten (9) –
UPDATE-Fähigkeit
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š, 12.11.2010
Vorlesung #5 - SQL (Teil 3)
20
WS 2010/11
Datenbanksysteme
Fr 15:15 – 16:45
R 1.008
Sichten (10) –
UPDATE-Fähigkeit
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š, 12.11.2010
Vorlesung #5 - SQL (Teil 3)
21
WS 2010/11
Datenbanksysteme
Fr 15:15 – 16:45
R 1.008
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š, 12.11.2010
Vorlesung #5 - SQL (Teil 3)
22
Statische CONSTRAINTs
WS 2010/11
Datenbanksysteme
Fr 15:15 – 16:45
R 1.008
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š, 12.11.2010
Vorlesung #5 - SQL (Teil 3)
23
Statische CONSTRAINTs (2)
WS 2010/11
Datenbanksysteme
Fr 15:15 – 16:45
R 1.008
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š, 12.11.2010
Vorlesung #5 - SQL (Teil 3)
24
Referentielle Integrität
WS 2010/11
Datenbanksysteme
Fr 15:15 – 16:45
R 1.008
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š, 12.11.2010
Vorlesung #5 - SQL (Teil 3)
25
Referentielle Integrität (2)
WS 2010/11
Datenbanksysteme
Fr 15:15 – 16:45
R 1.008
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š, 12.11.2010
Vorlesung #5 - 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
WS 2010/11
Datenbanksysteme
Fr 15:15 – 16:45
R 1.008
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š, 12.11.2010
Vorlesung #5 - SQL (Teil 3)
27
UNI Schema mit Constraints
WS 2010/11
Datenbanksysteme
Fr 15:15 – 16:45
R 1.008
Kemper Seite 157
URL: siehe Übungsblätter #4 und #5
© Bojan Milijaš, 12.11.2010
Vorlesung #5 - SQL (Teil 3)
28
WS 2010/11
Datenbanksysteme
Fr 15:15 – 16:45
R 1.008
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š, 12.11.2010
Vorlesung #5 - SQL (Teil 3)
29
WS 2010/11
Datenbanksysteme
Fr 15:15 – 16:45
R 1.008
Vorlesung #5
Ende