WS 2013/14 Datenbanksysteme Do 17:00 – 18:30 R 1.007 Vorlesung #7 SQL (Teil 4)

WS 2013/14Datenbanksysteme

Do 17:00 – 18:30R 1.007

Vorlesung #7

SQL (Teil 4)


Do 17:00 – 18:30R 1.007

„Fahrplan“ 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


Do 17:00 – 18:30R 1.007

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;



Do 17:00 – 18:30R 1.007

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;



Do 17:00 – 18:30R 1.007

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



Do 17:00 – 18:30R 1.007

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



Do 17:00 – 18:30R 1.007

Sichten (5) – 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



Do 17:00 – 18:30R 1.007

Sichten (6) – 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



Do 17:00 – 18:30R 1.007

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



Do 17:00 – 18:30R 1.007

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') ));© Bojan Milijaš, 14.11.2013


Do 17:00 – 18:30R 1.007

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)



Do 17:00 – 18:30R 1.007

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 ...)



Do 17:00 – 18:30R 1.007

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



Do 17:00 – 18:30R 1.007

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.



Do 17:00 – 18:30R 1.007

UNI Schema mit Constraints

Kemper Seite 157 URL: siehe Übungsblätter #4 und #5



Do 17:00 – 18:30R 1.007

Fazit und Ausblick

Fazit SQL Teil 1 bis 4 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



Do 17:00 – 18:30R 1.007

Vorlesung #7

Ende

WS 2013/14 Datenbanksysteme Do 17:00 – 18:30 R 1.007 Vorlesung #7 SQL (Teil 4)

Documents

Transcript of WS 2013/14 Datenbanksysteme Do 17:00 – 18:30 R 1.007 Vorlesung #7 SQL (Teil 4)