Prof. Dr. Stephan Kleuker Hochschule Osnabrück Fakultät Ing.-Wissenschaften und Informatik - Software-Entwicklung - Datenbanksysteme Wintersemester 2012/13 10. Aufgabenblatt Benutzt werden die Tabellen aus dem letzten Praktikum. Prüfen Sie Ihre Ergebnisse. Aufgabe 26 (2 Punkte) a) Schreiben Sie zwei PL/SQL-Prozeduren, die mit Hilfe eines Cursors einmal mit einer FOR-Schleife und einmal mit einer WHILE-Schleife alle Module zusammen mit der zugehörigen Durchschnittsnote ausgeben. Vergessen Sie nicht, die Ausgabe zu aktivieren. b) 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. Aufgabe 27 (6 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. 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 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. INSERT INSERT INSERT INSERT INSERT INSERT INTO INTO INTO INTO INTO INTO Pruefung Pruefung Pruefung Pruefung Pruefung Pruefung VALUES VALUES VALUES VALUES VALUES VALUES (450,21,to_date('11.10.13'),1,500) (449,31,to_date('11.10.13'),1,500) (449,21,to_date('11.10.13'),2,500) (446,22,to_date('11.10.13'),3,500) (446,22,to_date('11.10.13'),2,500) (446,23,to_date('11.10.15'),2,500) Seite 1 von 2 Prof. Dr. Stephan Kleuker Hochschule Osnabrück Fakultät Ing.-Wissenschaften und Informatik - Software-Entwicklung - Datenbanksysteme Wintersemester 2012/13 10. Aufgabenblatt Aufgabe 28 (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