1 P.

Datenbanksysteme WS 2015/16
Prof. Dr.-Ing. Sebastian Michel
MSc. Manuel Hoffmann
TU Kaiserslautern, FB Informatik – Lehrgebiet Informationssysteme
Übungsblatt 10: Ausgabe 12.01.2016, Präsentation 20.01.2016
Aufgabe 1: User-Defined Functions
http://dbis.informatik.uni-kl.de
(1 P.)
Verwenden Sie die Tabelle fernbus von Übungsblatt 9 und erstellen Sie User-Defined Functions:
1. Eine Prozedur preiserhoehung(int zuschlag), die den Preis jeder Busreise pro voller Stunde
Reisedauer um zuschlag EUR erhöht. Beispiel: preiserhoehung(1) erhöht die Preise von Fernbusreisen, die weniger als 60 Minuten dauern, nicht, aber von Reisen, die z.B. 65 Minuten dauern,
um einen Euro, die 150 Minuten dauern, um zwei Euro, etc.
2. Eine Funktion teuerer(int, int), die zwei Busnummern als Parameter erhält und −1 ausgibt,
wenn die erste Fernbusreise günstiger als die zweite ist, 0, wenn die beiden Reisen gleich teuer sind
und 1, wenn die erste Reise teurer als die zweite ist.
3. Eine Tabellenfunktion anschluss(int), die als Parameter eine Busnummer entgegennimmt und
die Fernbusreisen anzeigt, die am Zielort der gegebenen Reise starten.
Aufgabe 2: Trigger
(1 P.)
(a) Verwenden Sie auch für diese Aufgabe die Tabelle fernbus und erstellen Sie folgende Trigger:
1. Wenn eine neue Busreise eingetragen wird, soll ebenfalls die Rückfahrt eingetragen werden (sofern
diese noch nicht existiert).
2. Wenn der Preis oder die Dauer einer Busreise geändert wird, soll dies auch für die Rückfahrt gelten.
3. Erstellen sie eine Tabelle fernbus archiv. Beim Löschen einer Busreise wird die Rückfahrt ebenfalls
gelöscht und beide Fahrten kommen in diese Archiv-Tabelle.
Verwenden Sie innerhalb der Trigger-Prozeduren den Befehl RAISE NOTICE, um Meldungen wie ”Der
Preis und die Dauer der Rückfahrt wurde ebenfalls angepasst” auszugeben.
(b) Gegeben eine abstrakte Relation Bus(busnr), die als Spezialisierungen Fernbus wie gehabt und
Stadtbus(busnr, tarifzonen, wlan) hat. Erstellen Sie die passenden SQL-Tabellen für eine Ralisierung im Hausklassenmodell (vgl. Folien InSy SS2015, Kapitel 3). Wir wollen die Nachteile des Hausklassenmodells mit Triggern ausbügeln. Erstellen Sie einen Trigger, der bei einem INSERT-Befehl dafür sorgt,
dass die eingefügte busnr über alle erstellten Tabellen eindeutig ist; auch wenn dazu eine andere als die
vom Benutzer gewünschte Nummer benutzt werden muss.
1
Datenbanksysteme WS 2015/16
Prof. Dr.-Ing. Sebastian Michel
MSc. Manuel Hoffmann
TU Kaiserslautern, FB Informatik – Lehrgebiet Informationssysteme
Übungsblatt 10: Ausgabe 12.01.2016, Präsentation 20.01.2016
http://dbis.informatik.uni-kl.de
Aufgabe 3: Aktualisierbare Sichten
(1 P.)
In dieser Aufgabe betrachten wir nicht-verschachtelte Anfragen und Sichten. Das Regelsystem, nachdem
PostgreSQL festlegt, welche Sichten automatisch durch INSERT, UPDATE oder DELETE Anfragen aktualisierbar sind, lautet wie folgt:
• Die Sichtdefinition hat genau einen Eintrag in der FROM-Klausel. Dieser Eintrag referenziert eine
Tabelle oder eine andere aktualisierbare Sicht.
• Die Sichtdefinition beinhaltet keine WITH, DISTINCT, GROUP BY, HAVING, LIMIT oder OFFSET Klausel.
• Die Sichtdefinition beinhaltet keine Mengenoperationen (UNION, INTERSECT oder EXCEPT).
• Die Selektionsliste der Sicht darf keine Aggregatsfunktionen, Fensterfunktionen oder Funktionen,
die Mengen zurückgeben, beinhalten.
Erfüllt eine Sicht diese Anforderungen nicht, können Trigger definiert werden, die beim Aufruf einer dieser
drei Anfragen ausgeführt werden, etwa:
CREATE TRIGGER update_my_view INSTEAD OF UPDATE ON my_view
FOR EACH ROW EXECUTE PROCEDURE update_my_view () ;
1. Kann eine Sichtdefinition der Form SELECT DISTINCT ... FROM view aktualisierbar gemacht werden? Können alle drei Operationen (INSERT, UPDATE, DELETE) oder nur ein Teil davon ausgeführt
werden und gibt es vielleicht weitere Restriktionen? Wie muss ein Trigger und die getriggerte Funktion aussehen, um diese Funktionen auszuführen?
2. Wie lauten die Antworten für Definitionen der Form SELECT a, AGG(b) FROM view GROUP BY a,
wobei AGG eine der Aggregatsfunktionen SUM, COUNT, MAX ist.
2