Post on 06-Apr-2015
WS 2011/12Datenbanksysteme
Fr 15:15 – 16:45R 0.006
Vorlesung #6
SQL (Teil 3)
WS 2011/12Datenbanksysteme
Fr 15:15 – 16:45R 0.006
© Bojan Milijaš, 11.11.2011 2
„Fahrplan“ Besprechung eines Beispiels, SQL-Übungen 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 #7, SQL Teil 4
Vorlesung #6 - SQL (Teil 3)
WS 2011/12Datenbanksysteme
Fr 15:15 – 16:45R 0.006
© Bojan Milijaš, 11.11.2011 3
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' ;
Vorlesung #6 - SQL (Teil 3)
WS 2011/12Datenbanksysteme
Fr 15:15 – 16:45R 0.006
© Bojan Milijaš, 11.11.2011 4
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');
Vorlesung #6 - SQL (Teil 3)
WS 2011/12Datenbanksysteme
Fr 15:15 – 16:45R 0.006
© Bojan Milijaš, 11.11.2011 5
Rekursion (3)SELECT Vogänger
FROM voraussetzen
WHERE Nachfolger IN (SELECT Vorgänger
FROM voraussetzen, Vorlesungen
WHERE Nachfolger = VorlNr
AND Titel = `Der Wiener Kreis´);
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´;
EntschachtelungEntschachtelung
Vorlesung #6 - SQL (Teil 3)
WS 2011/12Datenbanksysteme
Fr 15:15 – 16:45R 0.006
© Bojan Milijaš, 11.11.2011 6
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 vWHERE 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´;
Vorlesung #6 - SQL (Teil 3)
WS 2011/12Datenbanksysteme
Fr 15:15 – 16:45R 0.006
© Bojan Milijaš, 11.11.2011 7
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“
Vorlesung #6 - SQL (Teil 3)
WS 2011/12Datenbanksysteme
Fr 15:15 – 16:45R 0.006
© Bojan Milijaš, 11.11.2011 8
Rekursion (6) –Transitive Hülle voraussetzen
Der Wiener Kreis
Wissenschaftstheorie
Bioethik
Erkenntnistheorie Ethik Mäeutik
Grundzüge
Vorlesung #6 - SQL (Teil 3)
WS 2011/12Datenbanksysteme
Fr 15:15 – 16:45R 0.006
© Bojan Milijaš, 11.11.2011 9
Rekursion (7) – Transitive Hülle
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))}
Vorlesung #6 - SQL (Teil 3)
WS 2011/12Datenbanksysteme
Fr 15:15 – 16:45R 0.006
© Bojan Milijaš, 11.11.2011 10
Rekursion (8) – Oracle CONNECT BY Konstrukt
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'));
Vorlesung #6 - SQL (Teil 3)
WS 2011/12Datenbanksysteme
Fr 15:15 – 16:45R 0.006
© Bojan Milijaš, 11.11.2011 11
Rekursion (9) in IBM DB2
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') );Vorlesung #6 - SQL (Teil 3)
WS 2011/12Datenbanksysteme
Fr 15:15 – 16:45R 0.006
© Bojan Milijaš, 11.11.2011 12
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;
Vorlesung #6 - SQL (Teil 3)
WS 2011/12Datenbanksysteme
Fr 15:15 – 16:45R 0.006
© Bojan Milijaš, 11.11.2011 13
Sichten (2)
(+) 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
NAME TITEL
Kant Grundzuege
... ...
SELECT * FROM ProfVorlesung;
Vorlesung #6 - SQL (Teil 3)
WS 2011/12Datenbanksysteme
Fr 15:15 – 16:45R 0.006
© Bojan Milijaš, 11.11.2011 14
Sichten (3) - logische Datenunabhängigkeit
Relation 1 Relation 2 Relation 3
Benutzer 2Benutzer 1
Sicht 1 Sicht 2 Sicht 3
Physische
Datenunabhängigkeit
Logische
Datenunabhängigkeit
Vorlesung #6 - SQL (Teil 3)
WS 2011/12Datenbanksysteme
Fr 15:15 – 16:45R 0.006
© Bojan Milijaš, 11.11.2011 15
Sichten (4) – Beispiel logische Datenunabhängigkeit
Internet-BesucherStudenten
ProfVerlesung
Dozentenlesen Kurse
CREATE VIEW ProfVorlesungCREATE VIEW ProfVorlesung AS SELECT Name, TitelAS SELECT Name, Titel FROM DozentenFROM Dozenten NATURAL JOIN lesenNATURAL JOIN lesen NATURAL JOIN Kurse;NATURAL JOIN Kurse;
CREATE VIEW ProfVorlesungCREATE VIEW ProfVorlesung
ASAS
SELECT Name, TitelSELECT Name, Titel
FROM Professoren, VorlesungenFROM Professoren, Vorlesungen
WHERE PersNr = gelesenVon;WHERE PersNr = gelesenVon;
Professoren Vorlesungen
Vorlesung #6 - SQL (Teil 3)
WS 2011/12Datenbanksysteme
Fr 15:15 – 16:45R 0.006
© Bojan Milijaš, 11.11.2011 16
Sichten (5) – Modellierungder Generalisierungen 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.
Vorlesung #6 - SQL (Teil 3)
WS 2011/12Datenbanksysteme
Fr 15:15 – 16:45R 0.006
© Bojan Milijaš, 11.11.2011 17
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);
create table AssiDaten(PersNr integer not null, Fachgebiet varchar(30), Boss integer);
create view Professoren as
select *
from Angestellte a, ProfDaten d
where a.PersNr=d.PersNr;
create view Assistenten as
select *
from Angestellte a, AssiDaten d
where a.PersNr=d.PersNr;
Vorlesung #6 - SQL (Teil 3)
WS 2011/12Datenbanksysteme
Fr 15:15 – 16:45R 0.006
© Bojan Milijaš, 11.11.2011 18
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);
create view Angestellte as(select PersNr, Name from Professoren)union(select PersNr, Name from Assistenten);
Vorlesung #6 - SQL (Teil 3)
WS 2011/12Datenbanksysteme
Fr 15:15 – 16:45R 0.006
© Bojan Milijaš, 11.11.2011 19
Sichten (8) – Modellierungder Generalisierungen 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
Vorlesung #6 - SQL (Teil 3)
WS 2011/12Datenbanksysteme
Fr 15:15 – 16:45R 0.006
© Bojan Milijaš, 11.11.2011 20
Sichten (9) – UPDATE-Fähigkeit Sichten sind immer veränderbar im Bezug auf DDL-
Operation, 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
Vorlesung #6 - SQL (Teil 3)
WS 2011/12Datenbanksysteme
Fr 15:15 – 16:45R 0.006
© Bojan Milijaš, 11.11.2011 21
Sichten (10) – UPDATE-FähigkeitBeispiel einer nicht UPDATABLE View:
create view WieHartAlsPrüfer (PersNr, Durchschnittsnote) asselect PersNr, avg(Note)from prüfengroup by PersNr;
alle Sichten
theoretisch änderbare Sichten
in SQL änderbare Sichten
Vorlesung #6 - SQL (Teil 3)
WS 2011/12Datenbanksysteme
Fr 15:15 – 16:45R 0.006
© Bojan Milijaš, 11.11.2011 22
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
Vorlesung #6 - SQL (Teil 3)
WS 2011/12Datenbanksysteme
Fr 15:15 – 16:45R 0.006
© Bojan Milijaš, 11.11.2011 23
Statische CONSTRAINTs
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') ));
Vorlesung #6 - SQL (Teil 3)
WS 2011/12Datenbanksysteme
Fr 15:15 – 16:45R 0.006
© Bojan Milijaš, 11.11.2011 24
Statische CONSTRAINTs (2)
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)
Vorlesung #6 - SQL (Teil 3)
WS 2011/12Datenbanksysteme
Fr 15:15 – 16:45R 0.006
© Bojan Milijaš, 11.11.2011 25
Referentielle Integrität
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 ...)
Vorlesung #6 - SQL (Teil 3)
WS 2011/12Datenbanksysteme
Fr 15:15 – 16:45R 0.006
© Bojan Milijaš, 11.11.2011 26
Referentielle Integrität (2)
Schlüsselkandidat UNIQUE CONSTRAINT Primärschlüssel PRIMARY KEY Fremdschlüssel FOREIGN KEY (auch implizit
durch das Wort REFERENCES in Tabellen-Definition)
FOREIGN KEYs können auch NULL Werte enthalten
UNIQUE FOREIGN KEY modelliert 1:1 Beziehung
Vorlesung #6 - SQL (Teil 3)
WS 2011/12Datenbanksysteme
Fr 15:15 – 16:45R 0.006
© Bojan Milijaš, 11.11.2011 27
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 Tabellen-Definition 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.
Vorlesung #6 - SQL (Teil 3)
WS 2011/12Datenbanksysteme
Fr 15:15 – 16:45R 0.006
© Bojan Milijaš, 11.11.2011 28
UNI Schema mit Constraints
Kemper Seite 157 URL: siehe Übungsblätter #4 und #5
Vorlesung #6 - SQL (Teil 3)
WS 2011/12Datenbanksysteme
Fr 15:15 – 16:45R 0.006
© Bojan Milijaš, 11.11.2011 29
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
Vorlesung #6 - SQL (Teil 3)
WS 2011/12Datenbanksysteme
Fr 15:15 – 16:45R 0.006
Vorlesung #6
Ende