Aufgabenblatt 09 - Hochschule Osnabrück
Werbung
Prof. Dr. Stephan Kleuker
Hochschule Osnabrück
Fakultät Ing.-Wissenschaften und Informatik
- Software-Entwicklung -
Datenbanksysteme
Wintersemester 2014/15
9. Aufgabenblatt
Benutzt werden die Tabellen aus dem letzten Praktikum. Prüfen Sie Ihre Ergebnisse.
Aufgabe 25 (1 Punkt)
Schreiben
Sie
eine
Prozedur
datenZeigen(Matrikelnummer),
die
den
Namen des Studierenden sowie zu allen in
der Datenbank eingetragenen Modulen, den
Titel und die bisher erreichte Note ausgibt.
Sollte es noch keine Note geben, wird nur der
Modultitel ausgegeben. Sollte es keinen Studierenden zur Matrikelnummer geben, ist dies
auch auszugeben. Vergessen Sie nicht, die Ausgabe zu aktivieren.
Aufgabe 26 (7 Punkte)
Hinweis: Bei den folgenden Triggern soll folgender Aufbau genutzt werden, damit mit :NEW
auf neue Werte zugegriffen werden kann. Auf ein WHEN kann dann verzichtet werden.
Alternativ dürfen Sie natürlich auch einen AFTER-Trigger nutzen, weiterhin muss „INSERT“
eventuell passend ersetzt werden. Das Pragma ermöglicht es, auf die Tabellen zuzugreifen,
für die gerade ein Trigger bearbeitet wird. Dies ist möglich, solange nur auf die
festgeschriebenen Daten (commit) vor der Aktion zugegriffen werden soll. Sie dürfen für die
Aufgabe annehmen, dass nach jedem SQL-Befehl ein COMMIT durchgeführt wird.
CREATE OR REPLACE TRIGGER <Triggername>
BEFORE INSERT ON <Tabellenname>
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
/* lokale Variablen und Cursor */
BEGIN
/* ... */
a)
b)
c)
Schreiben Sie einen Trigger, der garantiert, dass nur die jeweils letzte Prüfung zu einem
Modul gelöscht werden kann.
Schreiben Sie einen Trigger, der garantiert, dass nur die Note des bisher letzten Versuchs
zu einem Modul geändert werden kann, die anderen Daten vorheriger Prüfungen sollen
dabei unveränderbar sein. Weiterhin kann bei einer eingetragenen Prüfung nicht die
Matrikelnummer oder die Modul-ID oder das Datum oder die Versuchsnummer geändert
werden.
Schreiben Sie einen Trigger, der garantiert, dass nur gültige Prüfungen eingetragen
werden, d .h. die Versuchsanzahl muss zu den vorherigen Versuchen passen, das Datum
der Prüfung muss nach dem Datum der vorherigen Versuche in diesem Modul liegen, es
kann nur ein Modul bestanden werden, wenn es vorher nicht bestanden wurde, es kann
nur eine Prüfung für ein Fach des dritten Semesters eingetragen werden, wenn der
Student bisher mindesten 40 CP aus den ersten beiden Semestern hat und es kann nur
eine Prüfung eingetragen werden, wenn der Student nicht endgültig durchgefallen ist,
also drei Versuche nicht bestanden hat.
Folgende Einfüge-Befehle müssen scheitern, wenn die Ausgangssituation hergestellt ist.
/* geht nicht, da endgueltig durchgefallen */
INSERT INTO Pruefung VALUES (450,21,to_date('11.10.13'),1,500)
/* geht nicht, da nicht genuegend Leistungspunkte für Drittsemesterpruefung */
INSERT INTO Pruefung VALUES (449,31,to_date('11.10.13'),1,500)
/* geht nicht, da noch kein erster Versuch fuer das Modul eingetragen */
INSERT INTO Pruefung VALUES (449,21,to_date('11.10.13'),2,500)
/* geht nicht, da noch kein zweiter Versuch fuer das Modul eingetragen */
INSERT INTO Pruefung VALUES (446,22,to_date('11.10.15'),3,500)
Seite 1 von 2
Prof. Dr. Stephan Kleuker
Hochschule Osnabrück
Fakultät Ing.-Wissenschaften und Informatik
- Software-Entwicklung /* geht nicht, da Datum vor
INSERT INTO Pruefung VALUES
/* geht nicht, da das Modul
INSERT INTO Pruefung VALUES
Datenbanksysteme
Wintersemester 2014/15
9. Aufgabenblatt
dem Datum des ersten Versuchs liegt */
(446,22,to_date('11.10.13'),2,500)
bereits im vorherigen Versuch bestanden wurde */
(446,23,to_date('11.10.15'),2,500)
Hinweis: Durch das Löschen von Tabellen können auch Trigger verschwinden. Deshalb ist es
sinnvoll, funktionierende Prozeduren und Trigger in einer SQL-Datei zu speichern. Beachten,
dass dann am Ende in einer neuen Zeile ein / stehen muss.
CREATE OR REPLACE PROCEDURE EINFUEGENSTUDENT (matnr INTEGER, name VARCHAR) AS
BEGIN
INSERT INTO Student VALUES(matnr,name);
END EINFUEGENSTUDENT;
/
Nutzen Sie zur Überprüfung Ihrer Ergebnisse, das von der Web-Seite erhältliche EclipseProjekt StudiTriggerTest, das mit Hilfe von JUnit und DBUnit die Trigger dieser Aufgabe
überprüft. Um das Projekt lauffähig zu machen, müssen sie zunächst die Oracle-JDBCUmsetzung ojdbc6.jar in das Unterverzeichnis lib kopieren. Die Datei befindet sich z. B. beim
Sqldeveloper im Unterverzeichnis jdbc/lib. Die Libraries im Unterverzeichnis lib müssen dann
in das Projekt eingebunden werden. Dazu wird ein Rechtsklick auf dem Projekt gemacht, unten
„Properties“ gewählt, links der „Java Build Path“ und dort der Reiter „Libraries“ angeklickt, dann
rechts „Add External JARs…“ geklickt, in das lib-Verzeichnis manövriert, alle jar-Dateien
ausgewählt, mit „Öffnen“ hinzugefügt und mit „OK“ bestätigt. Details zur Nutzung von Eclipse
können
auch
http://home.edvsz.hs-osnabrueck.de/skleuker/querschnittlich/SEU.pdf
entnommen werden.
Die Tests sind z. B. mit einem Rechtsklick und der Auswahl „Run As“ und „JUnit Test“
ausführbar.
Passen Sie zunächst die Verbindungsdaten in der Klasse Verbindung.java an.
Aufgabe 27 (0 Punkte, freiwillig)
Durch das SQL-Skript graph.SQL von der Veranstaltungswebseite wird eine einfache Tabelle
Graph mit den Verbindungen der Knoten eines gerichteten Graphen erzeugt. Weiterhin wird
eine leere Tabelle TransitiveHuelle angelegt. Ihre Aufgabe ist es, mit Hilfe einer Prozedur und
den ihnen bekannten Sprachmöglichkeiten die transitive Hülle zu berechnen (der Graph hat
einen Zyklus).
Hinweise: Strings werden in Oracle mit || verbunden. In PL/SQL gibt es den Datentyp
BOOLEAN. Sie dürfen selbstgeschriebene Prozeduren und Funktionen in anderen
Aufgabenteilen nutzen. Denken Sie daran, dass sie in der Programmierung die inkrementelle
Entwicklung gelernt haben und komplexe Abläufe aus einfacheren zusammensetzen.
Seite 2 von 2
