Upload
hannelore-bauer
View
219
Download
0
Embed Size (px)
Citation preview
WS 2014/15Datenbanksysteme
Do 17:00 – 18:30R 0.005
Vorlesung #9
SQL Zusammenfassung
WS 2014/15Datenbanksysteme
Do 17:00 – 18:30R 0.005
„Fahrplan“ Nachtrag
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)
Zusammenfassung Ausblick Vorlesung #10
Vorlesung #9 - SQL Zusammenfassung© Bojan Milijaš, 27.11.2014 2
WS 2014/15Datenbanksysteme
Do 17:00 – 18:30R 0.005
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 ProfVorlesungASSELECT Name, TitelFROM Professoren, VorlesungenWHERE PersNr = gelesenVon;
Vorlesung #9 - SQL Zusammenfassung© Bojan Milijaš, 27.11.2014 3
WS 2014/15Datenbanksysteme
Do 17:00 – 18:30R 0.005
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 TITELKant Grundzuege
... ...
SELECT * FROM ProfVorlesung;
Vorlesung #9 - SQL Zusammenfassung© Bojan Milijaš, 27.11.2014 4
WS 2014/15Datenbanksysteme
Do 17:00 – 18:30R 0.005
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 #9 - SQL Zusammenfassung© Bojan Milijaš, 27.11.2014 5
WS 2014/15Datenbanksysteme
Do 17:00 – 18:30R 0.005
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 ProfVorlesungASASSELECT Name, TitelSELECT Name, TitelFROM Professoren, VorlesungenFROM Professoren, VorlesungenWHERE PersNr = gelesenVon;WHERE PersNr = gelesenVon;
Professoren Vorlesungen
Vorlesung #9 - SQL Zusammenfassung© Bojan Milijaš, 27.11.2014 6
WS 2014/15Datenbanksysteme
Do 17:00 – 18:30R 0.005
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
Vorlesung #9 - SQL Zusammenfassung© Bojan Milijaš, 27.11.2014 7
WS 2014/15Datenbanksysteme
Do 17:00 – 18:30R 0.005
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
Vorlesung #9 - SQL Zusammenfassung© Bojan Milijaš, 27.11.2014 8
WS 2014/15Datenbanksysteme
Do 17:00 – 18:30R 0.005
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 #9 - SQL Zusammenfassung© Bojan Milijaš, 27.11.2014 9
WS 2014/15Datenbanksysteme
Do 17:00 – 18:30R 0.005
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 #9 - SQL Zusammenfassung© Bojan Milijaš, 27.11.2014 10
WS 2014/15Datenbanksysteme
Do 17:00 – 18:30R 0.005
Statische CONSTRAINTs (2) Man kann CONSTRAINTs nachträglich definierenALTER TABLE myprofessorenADD 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 myprofessorenDROP CONSTRAINT sys_c003798; Dynamische Constraints mit Triggern nächstes Mal
(Vorlesung #9)
Vorlesung #9 - SQL Zusammenfassung© Bojan Milijaš, 27.11.2014 11
WS 2014/15Datenbanksysteme
Do 17:00 – 18:30R 0.005
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 PersNrCREATE TABLE Professoren(PersNr INTEGER PRIMARY KEY ...) (CREATE TABLE Vorlesungen gelesenVon INTEGER REFERENCES Professoren ...)
Vorlesung #9 - SQL Zusammenfassung© Bojan Milijaš, 27.11.2014 12
WS 2014/15Datenbanksysteme
Do 17:00 – 18:30R 0.005
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 #9 - SQL Zusammenfassung© Bojan Milijaš, 27.11.2014 13
WS 2014/15Datenbanksysteme
Do 17:00 – 18:30R 0.005
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 #9 - SQL Zusammenfassung© Bojan Milijaš, 27.11.2014 14
WS 2014/15Datenbanksysteme
Do 17:00 – 18:30R 0.005
UNI Schema mit Constraints
Kemper Seite 157
In der Vorlesung hat es mit dem „ON DELETE CASCADE“ Beispiel nicht geklappt, weil weitere Tabellen (hören und prüfen) auf Professoren referenzieren und nicht nur Vorlesungen
Vorlesung #9 - SQL Zusammenfassung© Bojan Milijaš, 27.11.2014 15
WS 2014/15Datenbanksysteme
Do 17:00 – 18:30R 0.005
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 SQL Teil 4 – 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 #9 - SQL Zusammenfassung© Bojan Milijaš, 27.11.2014 16
WS 2014/15Datenbanksysteme
Do 17:00 – 18:30R 0.005
Vorlesung #7
Ende