Datenbanksysteme (VL 4.0, 181.186)
Laborübungsteil, WS 2010/2011

Beispiel 3

Die folgenden Aufgabenstellungen basieren auf der Datenbank, welche Sie für Beispiel 2 des Laborübungsteils zu erstellen hatten. Sie können entweder auf Ihrer Datenbank weiterarbeiten, oder die Musterlösung des 2. Beispiels verwenden, welche auf der LVA-Homepage zum Download bereitsteht. Stellen Sie allerdings in jedem Fall in Ihrem Abgabe-Verzeichnis auf bordo.dbai.tuwien.ac.at die Create-, Insert- und Drop-Befehle jener Datenbank bereit, auf der Sie arbeiten.

Lösen Sie die folgenden Probleme mittels SQL:

1. (Zweigstellen) Geben Sie alle Zweigstellen ("name" und "anschrift") zusammen mit ihren Angestellten ("pid", "vname" und "nname") aus. Vermerken Sie in einer zusätzlichen Spalte, ob es sich bei dem Angestellten um den Leiter der Zweigstelle handelt. Benennen Sie diese neue Spalte mit "position". Sortieren Sie die Ausgabe nach der Zweigstelle und - falls dieser gleich ist - nach dem Namen des Angestellten.
2. (Belesenster Mitarbeiter) Geben Sie den (oder die) Mitarbeiter ("vname", "nname") mit den meisten Entlehnungen zusammen mit der Anzahl der Entlehnungen aus.
3. (Lese-Statistik) Geben Sie eine Liste ALLER Entlehnkarten-Besitzer (Nummer der Entlehnkarte, Vor- und Nachname des Besitzers) zusammen mit der Summe der Seiten aus, die jeder Besitzer bereits gelesen hat. Gehen Sie davon aus, dass jedes ausgeliehene Buch tatsächlich in seinem vollem Umfang gelesen wird. Für Entlehnkarten-Besitzer, die bisher noch keine Bücher bzw. überhaupt noch nichts ausgeliehen haben, soll der Wert '0' ausgegeben werden.
Wählen Sie per Hand ein Themengebiet aus, das Teil einer hierarchischen Ordnung ist. Schreiben Sie eine Query, die dieses Themengebiet ("themenid" und "name") ausgibt sowie rekursiv alle Oberthemen in dieser Hierarchie. Passen Sie die Tupel in Ihrer Datenbank so an, dass es zu dem von Ihnen ausgewählten Themengebiet mindestens zwei Ebenen übergeordneter Themengebieten gibt. Achten Sie darauf, dass Ihre Daten keine Schleife enthalten, da ansonsten die Abfrage fehlschlägt.
1. Wählen Sie per Hand eine Zweigstelle aus und geben Sie alle Themengebiete ("themenid", "name") aus, zu denen es in dieser Zweigstelle kein einziges Exemplar gibt.
2. Geben Sie zu jeder Zweigstelle ("name", "anschrift") jene Mitarbeiter aus ("pid", "vname", "nname"), die in dieser Zweigstelle das höchste Gehalt beziehen. Ordnen Sie das Ergebnis nach der Zweigstelle.
Schreiben Sie Befehle zum Erzeugen und Löschen einer View ("kuenstler_statistik_view"), welche für ALLE Künstler ("kid", "vname", "nname") ...
- die Anzahl der Bücher ausgibt, die er alleine verfasst hat,
- die Anzahl der Bücher ausgibt, die er mit anderen Autoren gemeinsam geschrieben hat,
- UND die Anzahl der Filme ausgibt, bei denen er Regie geführt hat.
Hat ein Künstler kein Buch alleine verfasst bzw. kein Buch gemeinsam geschrieben bzw. in keinem Film Regie geführt, so soll der Wert "0" eingetragen werden. Ordnen Sie das Ergebnis nach der Künstler-ID ("kid").

Lösen Sie die folgenden Probleme mittels PL/pgSQL:

Trigger
Schreiben Sie einen BEFORE INSERT ON - Trigger ("t_before_entlehnt"), welcher vor einem Insert in die Tabelle "entlehnt" überprüft, ob ein Exemplar überhaupt entlehnt werden darf. Ein Datenbankeintrag soll nur dann stattfinden, wenn...
- der Besitzer der Entlehnkarte die Altersfreigabe nicht verletzt (d.h. wenn ein Medium ab 16 Jahre freigegeben ist, dann darf es auch nur von mindestens 16 Jahre alten Personen ausgeliehen werden, nicht aber von jüngeren.) Hinweis: zur Berechnung des Alters einer Person können Sie die Funktionen age() und date_part() verwenden (siehe Postgres Dokumentation.)
- der Besitzer der Entlehnkarte die maximal entlehnbare Medienmenge von 10 Exemplaren nicht überschreitet (d.h. pro Entlehnkarte dürfen maximal 10 Exemplare entliehen werden; bereits zurückgegebene Exemplare zählen selbstverständlich nicht dazu!)
Bei Verletzung von (a), werfen Sie die Exception "zu_jung" und bei (b) die Exception "zu_viele_exemplare".
Funktionen
1. Schreiben Sie eine Funktion ("f_bestseller"), die für ein bestimmtes Medium den prozentuellen Anteil der Entlehnungen im Vergleich zur Gesamtanzahl aller Entlehnungen berechnet:
  - Prozentueller Anteil = (Anzahl der Entlehnungen pro Medium * 100) / (Anzahl der Entlehnungen insgesamt).
  - Die Funktion hat als Eingabeparameter "mediennummer" und liefert den prozentuellen Anteil für dieses Medium zurück.
  - Um bei der Berechnung des prozentuellen Anteils eine Division durch "0" zu vermeiden, stellen Sie bitte sicher, dass überhaupt Entlehnungen gespeichert sind. Werfen Sie die Exception "keine_entlehnungen", falls noch überhaupt keine Entlehnungen existieren.
  - Beachten Sie außerdem, dass Medien, die noch überhaupt nie entlehnt wurden einen prozentuellen Anteil von "0" haben.
  - Stellen Sie für etwaige andere Ausnahmefälle im Exception-Teil der Funktion "f_bestseller" einen WHEN OTHERS Zweig bereit, der den PostgreSQL-Status (Fehlernummer) und die Fehlermeldung ausgibt.
  - Liefern Sie bei auftretenden Fehlern den Wert "-1" zurück.
2. Schreiben Sie eine Funktion ("f_gebuehr"), die die anfallenden Gebühren für eine Entlehnung berechnet. Verrechnen Sie für jedes Exemplar dieser Entlehnung:
  - ...1.00 Euro, falls es eine Film-DVD ist;
  - ...0.80 Euro, falls es ein Neuerwerb ist - d.h. das Kaufdatum des Exemplars muss weniger als 1 Jahr vor dem aktuellen Datum liegen;
  - ...0.60 Euro, falls der Bestseller-Wert zwischen 20 und 50 Prozent liegt bzw. 1.20 Euro, falls der Bestseller-Wert über 50 Prozent liegt. Verwenden Sie die im vorigen Punkt erstellte Funktion "f_bestseller", um den Bestseller-Wert zu berechnen.
  Die Funktion hat als Eingabeparamter "entlehnid" ("eid" aus Entlehnung) und liefert die Summe der für diese Entlehnung anfallenden Gebühren zurück. Stellen Sie im Exception-Teil der Funktion "f_gebuehr" einen WHEN OTHERS Zweig bereit, der bei einem unvorhergesehenen Fehler den PostgreSQL-Status (Fehlernummer) und die Fehlermeldung ausgibt. Liefern Sie bei auftretenden Fehlern den Wert "-1" zurück.
3. Schreiben Sie eine Prozedur ("f_gebuehren_eintragen"), die als Eingabeparameter ein Datum hat ("adat" von Entlehnung) und nichts zurückgibt (Rückgabetyp void). Die Prozedur soll für alle an diesem Datum stattfindenden Entlehnungen die Gebühren berechenen und auf den Entlehnkarten der jeweiligen Kunden verbuchen ("gebuehr" bei Entlehnkarte). Verwenden Sie zur Berechnung der anfallenden Gebühren die im vorigen Punkt erstellte Funktion ("f_gebuehr"). Beachten Sie dabei, dass sie die "neu" berechneten Gebühren zu bereits gespeicherten Gebühren auf der Entlehnkarte addieren. Stellen Sie im Exception-Teil der Funktion "f_gebuehren_eintragen" einen WHEN OTHERS Zweig bereit, der bei etwaig auftretenden Fehlern den PostgreSQL-Status (Fehlernummer) und die Fehlermeldung ausgibt.
4. Schreiben Sie eine Prozedur ("f_alte_entlehnungen_loeschen()"), die alle Entlehnungen aus der Datenbank entfernt, die länger als 2 Jahre zurückliegen, d.h. die Zeitspanne vom Datum der Ausleihe ("adat") und der aktuellen Systemzeit ist gleich bzw. länger als 2 Jahre. Bedenken Sie, dass Sie auch in der Tabelle "entlehnt" und "retourniert" alle Datensätze löschen müssen, die zu den betroffenen Entlehnungen gehören. Löschen Sie allerdings nur jene Entlehnungen, die bereits vollständig zurückgegeben wurden. Die Prozedur soll am Ende einen Hinweis ausgeben, wie viele Entlehnungen dabei entfernt wurden.
5. Überlegen Sie sich eine sinnvolle Testabdeckung für die PL/pgSQL-Programmteile laut Punkt 5 - 6, z.B.: Erweiterung der Testdaten vom 2. Übungsbeispiel, Aufruf der zu testenden PL/pgSQL-Programmteile mit entsprechenden Ausgaben, so dass sich die erfolgreiche Durchführung der Tests überprüfen lässt. Stellen Sie in Ihrem Abgabe-Verzeichnis die SQL-Dateien mit den zusätzlichen INSERT-Befehlen und den "Testtreibern" in der Datei test.sql bereit. Sie müssen in der Lage sein, diese SQL-Dateien und PL/pgSQL-Dateien im Rahmen des Abgabegesprächs ablaufen zu lassen.

Lösen Sie folgende Probleme mittels Java und JDBC:

Sie können folgende Vorlage verwenden: Entlehnungsverwaltung.java

Schreiben Sie eine Java Klasse EntlehnungsVerwaltung mit folgenden Methoden:
1. Eine Methode "dbConnect()", die eine JDBC-Verbindung zur Datenbank herstellt und AUTOCOMMIT ausschaltet.
2. Eine Methode "dbOutputKunden()", die alle Entlehnkartenbesitzer (Entlehnkartennummer, Vor- und Nachname, offene Gebühren auf der Entlehnkarte) ausgibt, die Anzahl aller bisherigen Entlehnungen sowie die Gesamtsumme der Gebühren, die durch alle Entlehnungen dieses Kunden bisher entstanden sind. Vergessen Sie nicht auf jene Kunden, für die noch keine Entlehnungen erfasst sind! Verwenden Sie dabei die Funktion "f_gebuehr", die Sie in Punkt 6.2 erstellt haben. Die Ausgabe soll als Comma-Separated List erfolgen, d.h.: eine Bildschirm-Zeile pro Zeile der Tabelle; die Spaltenwerte werden einfach nacheinander ausgegeben und mittels "," getrennt.
3. Eine Methode "editGebuehren()", die exakt die Funktionalität der PL/pgSQL-Prozedur von Punkt 6.3 bereitstellt. Beachten Sie bei der Erstellung dieser Methode folgende Punkte:
  - "editGebuehren()" soll die Funktion "f_gebuehr" aus Punkt 6.2 verwenden. Ansonsten dürfen keine weiteren Benutzer-definierten Funktionen oder Prozeduren verwendet werden. Insbesonders darf nicht einfach die Prozedur aus Punkt 6.3 aufgerufen werden.
  - Stellen Sie durch entsprechende Transaction Control Kommandos sicher, dass die Methode "editGebuehren()" entweder die vorgesehene änderung erfolgreich durchführt (und festschreibt) oder die Datenbank unverändert lässt.
  - Vermeiden Sie in der Java-Methode die Verwendung von "*" in SELECT-Anweisungen. Listen Sie statt dessen in jeder SELECT Anweisung explizit die Spalten auf, die Sie auslesen (oder verändern) möchten, z.B.: In der Uni-DB würden Sie "SELECT MatrNr, Name, Semester FROM Studenten" statt "SELECT * FROM Studenten" schreiben. Und wenn das Semester in der Programmlogik keine Rolle spielt, würden Sie "SELECT MatrNr, Name FROM Studenten" schreiben.
4. Eine Methode "resetGebuehren()", die alle offenen Gebühren aus der Tabelle "Entlehnkarte" auf "0.00" setzt. Die Methode soll auf der Konsole ausgeben, wieviele Datensätze geändert wurden.
5. Eine Methode "dbDisconnect()", die die bestehende JDBC-Verbindung wieder schließt.
6. Überlegen Sie sich eine sinnvolle Testabdeckung der Java-Klasse "Bestellungsverwaltung". Legen Sie dazu eine weitere Methode "testEntlehnungsverwaltung()" an, die die anderen Methoden aufruft und entsprechende Ausgaben erzeugt, so dass sich die erfolgreiche Durchführung der Tests überprüfen lässt. Sie müssen in der Lage sein, diese Tests im Rahmen des Abgabegesprächs ablaufen zu lassen.

Für die Abgabe beim Tutor bereitzustellen

Bringen Sie bitte Ihren Studentenausweis zur Abgabe mit. Eine Abgabe ohne Ausweis ist nicht möglich.
Stellen Sie in Ihrem Abgabeverzeichnis auf bordo eine Listing-Datei mit dem Namen listing.txt bereit, die Sie beim Testen der SQL- und PL/pgSQL-Dateien (wie in Beispiel 2 des Laborübungsteils) erzeugt haben.
In Summe sind also folgende 5 Dateien zu erstellen:
- sql-teil.sql (Lösung zu Punkt 1 - 4)
- plpgsql-teil.sql (Lösung zu Punkt 5 - 6)
- test.sql (Testabdeckung zu Punkt 5 - 6)
- Bestellungsverwaltung.java (Lösung zu Punkt 7)
- listing.txt (mittels \o Befehl erzeugt)
Achten Sie weiters darauf, dass sich in Ihrem Abgabe-Verzeichnis auch die Create- Insert- und Drop-Befehle jener Datenbank befinden, welche Sie für die Lösung der Beispiele verwendet haben. Vergessen Sie nicht, die DROP-Datei dahingehend anzupassen, dass sie nun zusätzlich auch Befehle enthält, mit der sich alle in Punkt 5 - 6 erzeugten Datenbankobjekte löschen lassen.
Die Beispiele müssen bis zum Abgabetermin am Sonntag, den 5. Dezember 2010 um Mitternacht auf unserem Server (bordo.dbai.tuwien.ac.at) im Unterverzeichnis beispiel3 verfügbar sein (die Dateien werden automatisch abgesammelt und den Tutoren zur Verfügung gestellt). Es sind keine Ausdrucke erforderlich.
Wir erwarten von Ihnen eigenständige Lösungen. Plagiate werden nicht akzeptiert.
Stellen Sie sicher, dass alle geforderten Programmteile (d.h.: sql-teil.sql für Punkt 1 - 4, plpgsql-teil.sql und test.sql für Punkt 5 und 6 sowie Bestellungsverwaltung.java für Punkt 7) unter PostgreSQL 8.4 auf dem Server bordo.dbai.tuwien.ac.at fehlerlos laufen. Sie müssen in der Lage sein, diese Dateien im Rahmen des Abgabegesprächs ablaufen zu lassen.

Anmerkungen

plpgsql does not exist

Sollten Sie auf unserem Server folgenden Fehler bekommen, melden Sie sich unter dbs@dbai tuwien ac at:

psql:a_create.sql:21: ERROR: language "plpgsql" does not exist HINT: Use CREATE LANGUAGE to load the language into the database.

Falls Sie auf Ihrer eigenen DB arbeiten müssen Sie die plpgsql Sprache "erlauben". Dies geschieht mit createlang plpgsql auf der Kommandozeile (oder mit CREATE LANGUAGE siehe Postgres Dokumentation).

Classpath

Der Classpath ist standardmäßig auf den JDBC Treiber und das aktuelle Verzeichnis gesetzt. Dh

CLASSPATH=/usr/share/pgsql/postgresql-<VERSION>.jar:.

Sollten Sie eine Fehlermeldung wie Exception in thread "main" java.lang.NoClassDefFoundError: bekommen, müssen Sie den Classpath an Ihre Verzeichnisstruktur anpassen.

Bewertung

Für das Übungsbeispiel 3 werden maximal 15 Punkte vergeben. Im Rahmen des Abgabesprächs wird nicht nur die Korrektheit der Lösungen sondern auch (und vor allem) das Verständnis überprüft. Für die einzelnen Aufgaben erhalten Sie die maximal möglichen Punkte nur dann, wenn die Lösung richtig ist und wenn Sie in der Lage sind, diese Lösung zu erklären.

Die Verteilung der Punkte erfolgt nach folgendem Schlüssel:

Datei sql-teil.sql: max. 5 Punkte
Datei plpgsql-teil.sql und test.sql: max. 5 Punkte
Datei Entlehnungsverwaltung.java: max. 5 Punkte
Datei listing.txt: Vorhandensein für das Erreichen der vollen Punktezahl notwendig

Home / Kontakt / Webmaster / Offenlegung gemäß § 25 Mediengesetz: Inhaber der Website ist das Institut für Logic and Computation an der Technischen Universität Wien, 1040 Wien. Die TU Wien distanziert sich von den Inhalten aller extern gelinkten Seiten und übernimmt diesbezüglich keine Haftung. Disclaimer / Datenschutzerklärung