Vorlesung 7 - SQL (Teil 4)

WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
Vorlesung #7
SQL (Teil 4)
„Fahrplan“
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
 Wiederholung
 Relationale Division (doppelter NOT EXISTS, HAVING
(count) = ...)
 Rekursion / hierarchische Abfragen
 Views (Sichten) - gespeicherte Abfragen
 Gewährleistung der logischen Datenunabhängigkeit
 Modellierung von Generalisierung
 UPDATE-fähige Sichten
 Constraints
 NOT NULL, CHECK, UNIQUE, PRIMARY KEY
 Referentielle Integriät (FOREIGN KEY)
 Ausblick Vorlesung #8, SQL Teil 5
© Bojan Milijaš, 14.11.2013
Sichten (VIEWs)
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
 „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š, 14.11.2013
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
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š, 14.11.2013
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
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š, 14.11.2013
Sichten (4) – Beispiel
logische Datenunabhängigkeit
Studenten
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
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š, 14.11.2013
Sichten (5) –
UPDATE-Fähigkeit
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
 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š, 14.11.2013
Sichten (6) –
UPDATE-Fähigkeit
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
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š, 14.11.2013
Datenintegrität
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
 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š, 14.11.2013
Statische CONSTRAINTs
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
 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š, 14.11.2013
Statische CONSTRAINTs (2)
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
 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š, 14.11.2013
Referentielle Integrität
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
 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š, 14.11.2013
Referentielle Integrität (2)
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
 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š, 14.11.2013
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 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
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š, 14.11.2013
UNI Schema mit Constraints


WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
Kemper Seite 157
URL: siehe Übungsblätter #4 und #5
© Bojan Milijaš, 14.11.2013
Fazit und Ausblick

Fazit SQL Teil 1 bis 4





WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
SQL Teil 1 – Datentypen, einfache Abfragen
SQL Teil 2 – komplexe Abfragen, Unterabfragen
SQL Teil 3 – quantifizierte Abfragen, Rekursion
SQL Teil 4 – Views, Constraints
Ausblick SQL Teil 5




Trigger, prozedurale Erweiterungen (PL/SQL)
Einbettung in C,C++,Java
SQL Schnittstellen JDBC,ODBC
Query By Example QBE
© Bojan Milijaš, 14.11.2013
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
Vorlesung #7
Ende