Informationssysteme IS-K WS 2003/2004 ¨Ubung 7 Beispiellösung

¥¥¥¥¥¥
¥¥¥¥¥¥
Institut für Informationssysteme
¥¥¥¥¥¥ ¥¥¥¥¥¥ ¥¥¥¥¥¥
¥¥¥¥¥¥ ¥¥¥¥¥¥ ¥¥¥¥¥¥
Prof. Dr. H.-J. Schek
F. Akal, K. Jauslin
K. Haller, M. Mlivoncic
Eidgenössische Technische Hochschule Zürich
Swiss Federal Institute of Technology Zurich
Informationssysteme IS-K WS 2003/2004
Übung 7
Beispiellösung
Aufgabe 1: VIEWs in Oracle–SQL
Wir wollen zunächst sämtliche Informationen zusammenstellen, die wir über einen bestimmten
Geschäftsreisenden zur Verfügung haben, d.h. neben seinem Beruf auch seine Adresse und seine
Geschäftsadresse.
a) Geben Sie ein SQL-Statement an, die sämtliche vorhandenen Daten über “Fred Feuerstein”
liefern.
SELECT p.*, gr.Beruf, g.Name Gesch_Name, g.Strasse Gesch_Strasse,
g.Stadt Gesch_Stadt, g.PLZ Gesch_PLZ
FROM Person p, Geschaeftsreisender gr, Geschaeft g
WHERE p.Name = gr.Name AND gr.GName = g.Name AND
p.Name = ’Fred Feuerstein’;
b) Wir betrachten nun die folgende SQL–VIEW:
CREATE OR REPLACE VIEW Geschaeftsreisender_VW AS
SELECT p.*, gr.Beruf, g.Name Gesch_Name, g.Strasse Gesch_Strasse,
g.Stadt Gesch_Stadt, g.PLZ Gesch_PLZ
FROM Person p, Geschaeftsreisender gr, Geschaeft g
WHERE p.Name = gr.Name AND gr.GName = g.Name;
Führen Sie den Befehl in Ihrer Datenbank aus, um die View zu erzeugen. Wie lässt sich die
Anfrage aus der vorherigen Teilaufgabe nun kürzer Formulieren?
SELECT * FROM Geschaeftsreisender_vw WHERE Name=’Fred Feuerstein’;
c) Definieren Sie eine View, Reise Statistik, die für jede Firma (Name) die Anzahl der Dienstreisen (Reisen Total ) auflistet. Testen Sie Ihre View anschliessend mit einem entsprechendem
SELECT –Befehl.
Hinweis: Verwenden Sie GROUP BY.
CREATE OR REPLACE VIEW Reise_Statistik AS
SELECT g.Name, COUNT(r.Name) Reisen_Total
FROM Reise r, Geschaeftsreisender gr, Geschaeft g
WHERE r.Name = gr.Name AND gr.GName = g.Name
GROUP BY g.Name ;
d) Definieren Sie eine weitere View, ReiseNachZuerich Statistik, die für jede Firma (Name) die Anzahl der Dienstreisen nach Zürich (Reisen Zuerich) auflistet. Testen Sie Ihre View anschliessend
mit einem entsprechendem SELECT –Befehl.
CREATE OR REPLACE VIEW ReiseNachZuerich_Statistik AS
SELECT g.Name, COUNT(r.Name) Reisen_Zuerich
FROM Reise r, Geschaeftsreisender gr, Geschaeft g
WHERE r.Name = gr.Name AND gr.GName = g.Name AND
r.Destination = ’Zuerich’
GROUP BY g.Name ;
1
Aufgabe 2: TRIGGER in Oracle–SQL
a) Versuchen Sie einen Mitarbeiter über die View einzufügen:
INSERT INTO Geschaeftsreisender_vw
VALUES (’Hermann Meier’, ’Seestr. 10’, ’Küsnacht’, 8700 , ’Aussendienst-Support’,
’Easy-Informatik’, ’Computerweg 1’, ’Zürich’, 8040);
Was bedeutet die Fehlermeldung, die Sie erhalten?
ORA-01732: data manipulation operation not legal on this view
Diese Fehlermeldung besagt, dass Oracle zwar in der Lage ist, Änderungen an einer Basis–
Tabelle, die der View zugrunde liegt, vorzunehmen, aber nich an mehreren gleichzeitig.
b) Definieren Sie nun den folgenden Trigger in Ihrer Datenbank. Versuchen Sie danach, den Mitarbeiter nochmals über die View einzufügen.
CREATE OR REPLACE TRIGGER Geschaeftsreisender_vw_trigger
INSTEAD OF INSERT ON Geschaeftsreisender_vw FOR EACH ROW
DECLARE
rowcount INTEGER;
BEGIN
SELECT count(*) INTO rowcount FROM Geschaeft WHERE Name = :new.Gesch_Name;
IF (rowcount = 0) THEN
INSERT INTO Geschaeft
VALUES (:new.Gesch_Name, :new.Gesch_Strasse, :new.Gesch_Stadt, :new.Gesch_PLZ);
END IF;
INSERT INTO Person VALUES (:new.Name, :new.Strasse, :new.Stadt, :new.PLZ);
INSERT INTO Geschaeftsreisender VALUES (:new.Name, :new.Gesch_Name, :new.Beruf);
END;
/
Wurden die Daten nun korrekt in die entsprechenden Tabellen geschrieben?
Was passiert, wenn wir weitere Mitarbeiter der Firma “Easy-Informatik” über die View
einfügen? Ist dies sinnvoll?
Beispiel:
INSERT INTO Geschaeftsreisender_vw VALUES (’Ursi Meier’, ’Seestr.
10’, ’Küsnacht’, 8700 , ’Programmiererin’, ’Easy-Informatik’,
’Computergasse 41’, ’Baerlin’, 1234);
Dies ist nur in Fällen sinnvoll, sofern nicht noch gleichzeitig versucht wird, die Anschrift der
Firma zu ändern. Wenn dies gewünscht wird, könnte man den Trigger aber entprechend erweitern. Ein ELSE –Zweig bei der bedingten Anfrage könnte mittels UPDATE –Statement die
entsprechende Änderung vornehmen.
Allgemein wären weitere Trigger denkbar, die auch ein UPDATE der gesamten View vornehmen.
Beachten Sie die Reihenfolge der INSERT–Befehle in der Trigger-Definition. Was passiert, wenn
man die Reihenfolge der INSERT–Befehle des Triggers vertauscht bzw. den IF–Block ans Ende
der Definition stellt? Probieren Sie es ruhig einmal aus und versuchen Sie dann erneut Daten
über die View einzufügen.
Wegen den Fremdschlüsselbeziehungen müssen die INSERT –Befehle in exakt dieser Reihenfolge
vorgenommen werden.
Wir wollen versuchen, das Problem aus Teilaufgabe 1c) nun mit Hilfe von Triggern zu lösen. Hierzu
betrachten wir folgende Table–Definition:
CREATE TABLE Reise_Statistik_Table (
Name
VARCHAR2(20) NOT NULL,
Reisen_Total INTEGER,
PRIMARY KEY(Name),
FOREIGN KEY(Name) REFERENCES Geschaeft ON DELETE CASCADE
);
2
c) Geben Sie an, wie Sie diese Tabelle mit den korrekten Werten füllen würden.
Hinweis: Verwenden Sie z.B. den INSERT INTO–Befehl.
INSERT INTO Reise_Statistik_Table
SELECT Name, Reisen_Total FROM Reise_Statistik;
d) Schreiben Sie einen Trigger Statistik trigger1, der jeweils beim Einfügen einer neuen Dienstreise
in Reise die Statistik aktualisiert.
Hinweis: Sofern Sie es nicht bereits wissen, überlegen Sie sich, wie Sie mittels UPDATE–
Kommando einen Wert inkrementieren können.
CREATE OR REPLACE TRIGGER Statistik_trigger1 AFTER INSERT ON Reise
FOR EACH ROW DECLARE
myGName VARCHAR2(30);
rowcount INTEGER;
BEGIN
SELECT GName INTO myGName FROM Geschaeftsreisender WHERE Name=:new.Name ;
SELECT count(*) INTO rowcount FROM Reise_Statistik_Table WHERE Name = myGName ;
IF (rowcount = 0) THEN
INSERT INTO Reise_Statistik_Table Values(myGName, 0) ;
END IF;
UPDATE Reise_Statistik_Table SET Reisen_Total=Reisen_Total+1 WHERE Name = myGName ;
END;
/ show errors;
e) Schreiben Sie einen Trigger Statistik trigger2, der jeweils beim Entfernen einer Dienstreise aus
der Tabelle Reise die Statistik aktualisiert.
CREATE OR REPLACE TRIGGER Statistik_trigger2 AFTER DELETE ON Reise
FOR EACH ROW
BEGIN
-- hier brauchen wir nichts ueberpruefen, da alles passen muesste:
-- fuer Destination existiert Reisender, dafuer wiederum Geschaeft.
-- beim Einfuegen von Reise wurde diese Row auf jeden Fall angelegt!
UPDATE Reise_Statistik_Table SET Reisen_Total=Reisen_Total-1 WHERE Name =
(SELECT GName FROM Geschaeftsreisender WHERE Name=:old.Name);
END;
/ show errors;
Aufgabe 3: Zusätzliche Überlegungen
Erläutern Sie die Stärken und Schwächen der vorgestellten Lösungen mittels Trigger bzw. Views.
Was passiert in der Datenbank wenn ein Mitarbeiter die Firma wechselt; was, wenn eine Firma in
Konkurs geht (d.h. aus der Tabelle gelöscht wird)?
Sind die Daten danach immer konsistent? Welche Seiteneffekte treten auf? Welche SQL–
Anweisungen unserer Definitionen sind dafür verantwortlich? Welche weitere Funktionalität wäre
wünschenswert?
3
Die sog. “referentielle Integrität” der Daten wird durch die Verwendung der ON–DELETE–
CASCADE –Anweisungen in der Datenbank garantiert. Dies bedeutet in unserem Fall, dass das
Löschen eines Geschäftes auch das automatische Löschen aller Geschäftsreisenden zur Folge hat.
Die Personen an sich bleiben in der Datenbank; die Dienstreisen hingegen werden gelöscht.
Im Modell ist nicht vorgesehen, dass es Personen gibt, die einen Beruf haben und nicht als Geschäftsreisende geführt werden. Ebenso wird beim Wegfall der Anstellung (d.h. der Eintrag in der Tabelle
Geschaeftsreisender wird gelöscht) die Information “Beruf” ebenfalls verloren gehen. Wenn dies
nicht gewünscht ist, muss es beim Design bereits berücksichtigt werden und das Beschäftigungsverhältnis von der Berufsangabe entkoppelt werden.
Eindeutige Personen- und Firmennamen sind ebenfalls in der Realität eher selten. Man könnte hier
natürlich auf Personen–IDs bzw. Firmen–IDs zurückgreifen. Hier wurde darauf verzichtet, um die
SQL–Definitionen übersichtlicher zu halten.
4