Datenbanksysteme (VU 4.0, 184.686)
Übungsteil, WS 2012/2013
Beispiel 3
Basierend auf der im vorigen Übungsbeispiel beschriebenen Datenbank sollen im ersten Teil komplexere Anfragen formuliert werden. Im zweiten und dritten Teil werden mit Hilfe von Java zuerst Concurrency und danach Prozeduren behandelt.
Sie können für dieses Übungsbeispiel entweder ihre eigene Lösung von Beispiel 2 weiterverwenden oder die Musterlösung als Basis für diese Aufgabe verwenden. Die Musterlösung wird allerdings erst nach Ablauf der Abgabegespräche zu Beispiel 2 auf der Übungsseite der Lehrveranstaltung veröffentlicht.
Teil 1: Komplexe Abfragen
Lösen Sie die folgenden Probleme mittels SQL:
- Geben Sie ID, Rufname sowie die ID und den Nachnamen des Leiters jener Mannschaften aus, die bereits mit allen Bergefahrzeugen bei Einsätzen waren.
- Wählen Sie per Hand einen Dienstgrad aus, der anderen Dienstgraden untergeordnet ist. Schreiben Sie eine Anfrage, die diesen Dienstgrad ausgibt, sowie rekursiv alle übergeordneten Dienstgrade. Geben Sie für jeden Dienstgrad die ID, Beschreibung sowie das zugehörige Gehalt und den Gehaltsunterschied zum nächstniedrigen Dienstgrad aus. Sollte es keinen niedrigeren Dienstgrad geben, soll der Inhalt der Spalte 0 sein. Passen Sie die Tupel in Ihrer Datenbank so an, dass es zu der von Ihnen ausgewählten Kategorie mindestens zwei Ebenen übergeordneter Kategorien gibt.
- Geben Sie die ID, Vor- und Nachname sowie Anzahl der Berichte der Personen aus, die an nicht mehr als drei Einsätzen teilgenommen haben und gleichzeitig die wenigsten Berichte verfasst haben. Sortieren Sie das Ergebnis nach der Anzahl der erstellten Berichte absteigend. [Hinweis nach Rückfragen: Da maximal ein Ergebnis zurückgeliefert wird, ist das Sortieren nicht nötig!] Vergessen Sie nicht darauf, dass im Falle, dass noch keine Berichte erstellt wurden auch die Personen ausgegeben werden, die keine Berichte verfasst haben.
Passen Sie gegebenenfalls Ihre Testdaten (insert.sql) an, damit die Abfragen Ergebnisse liefern. Stellen Sie insbesondere für die zweite Abfrage sicher, dass Ihre Daten keine Zyklen bei den Dienstgraden enthalten.
Hinweise zu Teil 2 und 3
Sie finden hier mehrere ZIP-Archive mit Java-Dateien (wahlweise als NetBeans- oder Eclipse-Projekt oder auch als Version für die Bearbeitung ohne IDE), die wir Ihnen als Vorlage zur Verfügung stellen. Sie können diese Dateien in jedem beliebigen Editor bearbeiten und die von Ihnen erstellten Lösungen sowohl zu Hause als auch auf dem Übungsserver (bordo.dbai.tuwien.ac.at) testen. Stellen Sie aber jedenfalls sicher, dass Ihre endgültige Lösung auf dem Übungsserver korrekt ausgeführt wird, um mögliche Punkteabzüge zu vermeiden!
Beachten Sie bitte, dass die vorgegebenen Codezeilen, die markiert sind durch einen Kommentarblock (“Vorgegebener Codeteil”) und geschlossen werden durch eine Kommentarzeile aus Hashtags (#), nicht verändert werden sollten. Wenn Sie ihre eigene Lösung von Beispiel 2 weiterverwenden, kann es passieren, dass aufgrund anderer Namensgebung (für Tabellen und/oder Attribute) kleine Adaptierungen vorgenommen werden müssen.
Teil 2: Java und Concurrency
In diesem Teil zum Thema Concurrency werden zwei Übungsszenarien betrachtet (Szenario1.java, Szenario2.java). Die Szenarien sind so angelegt, dass es jeweils eine bereits existierende Transaktion gibt, die Ihnen von uns zur Verfügung gestellt wird, und eine die von Ihnen zu ergänzen ist.
Der Großteil des Codes ist bereits vorgegeben. Sie müssen nur die Methode runTransactionA() vervollständigen, indem sie den in der Vorlage bereitgestellten Code ergänzen. Die bereitgestellte Methode runTransactionB() dient zur Überprüfung der Funktionalität und erzeugt bei Wahl des falschen Isolation-Levels ein Fehlverhalten.
Die Parameter der Szenarien 1 und 2 sind jeweils die folgenden:
- "a" oder "b" für die Wahl der jeweiligen Transaktion
- Servername
- Port-Nummer
- Datenbank-Name
- Username (optional)
- Passwort (optional)
Helfende Hinweise
Zum vereinfachten Testen haben wir Ihnen ein ant-Skript zur Verfügung gestellt (build.xml). Wenn Sie Ihre Verbindungs- und Benutzerdaten in diesem Skript hinterlegen, müssen Sie nur mehr die folgenden Aufrufe benutzen, um die Programme zu starten:
ant run-szenario1-a(für Ihre eigene Implementierung von Szenario 1)ant run-szenario1-b(für die bereitgestellte Transaktion zu Szenario 1)ant run-szenario2-a(für Ihre eigene Implementierung von Szenario 2)ant run-szenario2-b(für die bereitgestellte Transaktion zu Szenario 2)
Für einen Test öffnen Sie am besten zwei Konsolenfenster. Im ersten Fenster rufen Sie zuerst Ihre eigene Implementierung auf (also z.B. mittels des Befehls “ant run-szenario1-a”) und im zweiten Fenster die von uns bereitgestellte zugehörige Transaktion (z.B. “ant run-szenario1-b”).
Sie sollten nun zwei Konsolenfenster sehen, die beide auf einen Befehl warten. Wenn Sie im Fenster mit der Transaktion A die Eingabetaste drücken, startet der von Ihnen programmierte Ablauf und sollte die erste Ausgabe anzeigen. Danach starten Sie Transaktion B ebenfalls mit der Eingabetaste. Die Transaktion B verändert nun den Datenbestand in der Datenbank. Wenn Sie nun wieder im anderen Konsolenfenster mit der Transaktion A die Enter-Taste betätigen, sollte im Erfolgsfall die korrekte zweite Ausgabe Ihres Programms erscheinen.
Überprüfen Sie nun sorgfältig, ob sich Ihr Programm auch wirklich der Spezifikation entsprechend verhält und keine unerwünschten Ergebnisse durch die Transaktion B entstehen. Durch eine andere Reihenfolge beim Drücken der Enter-Taste in den verschiedenen Programmen können Sie natürlich auch andere Ablaufmuster und Verschachtelungen der Transaktionen simulieren und testen.
Für eine schnelle Übersicht über das Setzen von Isolation Levels via JDBC bietet sich das entsprechende Java Tutorial an. In der entsprechenden PostgreSQL Dokumentation findet man eine Übersicht über die in PostgreSQL tatsächlich unterstützen Levels.
Szenario 1
Für die Erstellung des jährlichen Jahresrückblicks durch den Landesfeuerwehrverband, müssen die einzelnen Feuerwehren Zahlen und Fakten liefern. Für die Erfüllung dieser Aufgabe benötigt der Feuerwehrkommandant folgende Informationen:
- Das Durchschnittsalter von Personen, welche an den letzten Wettkämpfen teilgenommen haben,
- sowie das allgemeine Durchschnittsalter der Personen und
- das Verhältnis dieser beiden Werte
Diese Zahlenwerte sollen in einer Transaktion ermittelt werden. Bezüglich der Wettkämpfe hält der Feuerwehrkommandant die letzten 3 Wettkämpfe für ausreichend, also jene die zuletzt stattgefunden haben.
Da diese Statistiken aus der Sicht der Feuerwehrkommandanten nur eine untergeordnete Rolle spielen, ist es ihm ein dringendes Anliegen, dass der Echtzeitbetrieb so wenig wie möglich belastet wird. Daher sind etwaige minimale Abweichungen bei diesen Statistiken irrelevant.
Beachten Sie diese Anforderungen und den möglichen Zusammenhang der beiden Abfragen. Überlegen Sie sich, welches Isolation-Level hierfür ausreicht und begründen Sie ihre Entscheidung beim Abgabegespräch.
Ermitteln Sie die geforderten Informationen in den bereitgestellten Codeblöcken der Transaktion A in Szenario 1. Mit der zur Verfügung gestellten Transaktion B können Sie verschiedenste Verschachtelungen der einzelnen Befehle durchspielen und so gewollte, oder aber auch ungewollte Effekte des gewählten Isolation-Levels sehen.
Szenario 2
Für Belobigungen will der Feuerwehrkommandant regelmäßig abfragen, an welchen Einsätzen eine bestimmte Mannschaft teilgenommen hat (Typ, Ort, Betroffene Personen) und das Ergebnis soll nach Anzahl der betroffenen Personen absteigend sortiert werden. Für unsere Tests verwenden Sie bitte die Mannschaft mit der ID 10 in ihrer Transaktion. Bitte stellen Sie sicher, dass es auch in Ihren Testdaten eine Mannschaft mit der ID 10 gibt, da ansonsten das Ergebnis beim Abgabegespräch nicht vernünftig überprüft werden kann.
In der gleichen Transaktion soll auch die Einsatzstatistik ausgelesen werden. Die Statistik soll beinhalten, wie viele Ereignisse es von jedem Typ (Brand, Verkehrsunfall, Hochwasser, Sonstiges) ingesamt bisher gab und wie viele Personen von dem jeweiligen Ereignistyp in Summe betroffen waren. Diese Ausgabe soll ebenfalls nach Typ aufsteigend sortiert werden. Sie müssen Ereignistypen, die nicht verwendet wurden, nicht berücksichtigen.
Da der Kommandant die Daten gerne mobil abrufen möchte, soll die zu übertragende Datenmenge möglichst gering sein, d.h. es ist notwendig, dass von der Datenbank immer nur die jeweils notwendigen Tupel ausgelesen werden und auch alle Berechnungen und Sortierungen sollen von der Datenbank übernommen werden. Ihre Java-Anwendung soll die abgerufenen Daten nur mehr anzeigen.
Der Kommandant wünscht auch ausdrücklich, dass es nicht möglich sein soll, dass Veränderungen in anderen Tabellen während der Laufzeit der Transaktion die Daten verfälschen können. Das heißt, dass die Daten aus der ersten Abfrage unbedingt konsistent mit denen aus der zweiten Abfrage sein müssen.
Als Ausgabe soll Ihr Programm eine Liste der Einsätze ausgeben, die der Anforderung des Kommandanten entspricht. In einer weiteren Liste sollen die Einsatzsstatistik ausgegeben werden. Achten Sie darauf, dass sie unerwünschtes Verhalten vermeiden, aber die Datenbank soll nicht durch ein zu restriktives Isolation-Level unnötig blockiert werden.
Setzen Sie nun wieder die geforderten Kommandos in die Codeblöcke von Transaktion A in Szenario 2. Auch in diesem Szenario gibt es wieder eine vorgegebene Transaktion B, welche gewisse Effekte bei der parallelen Ausführung hervorbringt.
Hinweise zur Abgabe
Sie müssen beim Kontrollgespräch Ihr Vorgehen bei der Erstellung und auch die Funktionsweise des Programms erklären können. Sie sollten in der Lage sein, zu erklären wie sich die Wahl des Isolation-Levels auf das Programmverhalten auswirkt. Dazu ist ein gewisses Basiswissen im Bereich Concurrency erforderlich, zum Beispiel das Verständnis von Transaktionen (sprich z.B. ACID), die Charakteristika von Isolation-Levels, oder welche Probleme die einzelnen Isolation-Levels beheben (z.B. Dirty Read).
Teil 3: Java und Prozeduren
In der dritten Datei (Szenario3.java) sollen Sie die Methode inflationsAusgleich(double inflation) entsprechend der folgenden Angabe implementieren. Sie können dieses Programm entweder mittels der Kommandozeilen-Argumente oder wiederum direkt mittels des ant-Skripts (ant run-szenario3) starten.
Die Argumente für den Aufruf dieses Szenarios sind die folgenden:
- Servername
- Port-Nummer
- Datenbank-Name
- Username (optional)
- Passwort (optional)
Szenario 3
Für die bessere Verwaltung der Gehälter der einzelnen Mitarbeiter soll eine Java-Methode inflationsAusgleich(double inflation) erstellt werden. Der Parameter inflation gibt dabei die aktuelle Inflationsrate an. Eine Inflationsrate von 0.05 würde bedeuten, dass die Inflation 5% beträgt.
Die folgenden Aktionen sollen von der Prozedur ausgeführt werden:
- Benutzen Sie die Stored Procedure
p_erhoehe_dienstgrad(integer)aus Beispiel 2, um alle Personen zu befördern, die bereits zwei oder mehr Jahre im aktuellen Dienstgrad sind. - Erstellen Sie ein Prepared Statement zum Setzen des Gehalts eines bestimmten, existierenden Dienstgrades.
- Benutzen sie das oben beschriebene Prepared Statement um für jeden Dienstgrad
die folgenden Änderungen durchzuführen:
- Gehälter unter 750 Euro sollen um die 2.5-fache Inflation erhöht werden.
- Gehälter unter 1250 Euro sollen um die 2.0-fache Inflation erhöht werden.
- Gehälter unter 1750 Euro sollen um die 1.5-fache Inflation erhöht werden.
- Gehälter über 5000 Euro sollen um die 0.5-fache Inflation erhöht werden.
- Alle andere Gehälter sollen um die einfache Inflationsrate erhöht werden.
- Geben Sie zum Abschluss alle Personen mit ihrer ID, dem Vor- und Nachnamen sowie dem aktuellen Gehaltsanspruch aus. Der Anspruch berechnet sich aus der Summe des dem Dienstgrad zugewiesenen Gehalts und den Bonuszahlungen für die jeweilige Person. Benutzen Sie für die Berechnung der Bonuszahlungen die von Ihnen erstellte Funktion
f_bonus(int). Natürlich soll für die Berechnung bereits das um den Inflationsausgleich erhöhte Gehalt herangezogen werden.
Abgabe
Für das Beispiel 3 sind folgende Dateien abzugeben:
create.sql(aus Beispiel 2 oder Musterlösung)drop.sql(aus Beispiel 2 oder Musterlösung)insert.sql(aus Beispiel 2 oder Musterlösung)create-plpgsql.sql(aus Beispiel 2 oder Musterlösung)drop-plpgsql.sql(aus Beispiel 2 oder Musterlösung)queries.sqlSzenario1.javaSzenario2.javaSzenario3.java
Alle sonstigen Dateien (z.B. DBConnector.java und das Build-Script) sollen nicht Teil der Abgabe sein.
Führen Sie keine Änderung an der "Testumgebung" aus Teil 2 und 3 (DBConnector.java, Build-Script, Main-Methoden, usw.) durch, da ansonsten ihre Abgabe im Rahmen des Abgabegesprächs möglicherweise nicht ausgeführt werden kann.
Die aufgezählten Dateien sind in einer ZIP-Datei beispiel3.zip bis spätesten 02.12.2012 um 23:59 im CourseManager abzugeben. Es wird stets die zuletzt hochgeladene Version Ihrer Lösung gewertet.
Achten Sie darauf, dass der Name jeder dieser abzugebenen Dateien so lautet wie oben beschrieben. Erstellen Sie keine Ordner innerhalb der ZIP-Datei.
Abgabegespräche
Die Verteilung der Punkte erfolgt nach folgendem Schlüssel:
- Teil 1 (
queries.sql): 4 Punkte - Teil 2 (
Szenario1.java,Szenario2.java): 7 Punkte - Teil 3 (
Szenario3.java): 4 Punkte
Im Rahmen des Kontrollgespräches wird nicht nur die Korrektheit, sondern vor allem das Verständnis der Konzepte überprüft. Durch die Übung sollen sowohl Ihre praktische Problemlösungskompetenz als auch das theoretische Wissen über Datenbanksysteme gefördert werden. Sie müssen daher bei den Abgabegesprächen in der Lage sein, nicht nur Ihre Beispiele zu erklären, sondern ebenfalls zeigen, dass Sie die bisher in der Vorlesung behandelte Theorie zu diesen Beispielen ausreichend verstanden haben. Dies soll Ihnen die Vorbereitung für die Prüfung erleichtern und so können Sie Ihr Wissen während der Abgabegespräche selbst testen und gegebenenfalls vertiefen.
Die volle Punktezahl gibt es nur, wenn die Beispiele korrekt gelöst wurden und die Lösung einwandfrei erklärt werden kann. Nicht selbstständig gelöste Abgaben werden mit 0 Punkten bewertet!
Erscheinen Sie in Ihrem eigenen Interesse pünktlich zum Abgabegespräch, da andernfalls nicht garantiert werden kann, dass Ihre gesamte Lösung in der verbleibenden Zeit beurteilt werden kann.
Bringen Sie bitte Ihren Studentenausweis zur Abgabe mit. Eine Abgabe ohne Ausweis ist nicht möglich.
Hinweise zur Verwendung von psql
Folgende Befehle können für Ihre Arbeit mit der interaktiven SQL-Shell psql von PostgreSQL 9.1 hilfreich sein:
- \?: Listet alle psql-internen Befehle samt Erklärung auf.
- \i <dateiname>: Führt das Skript <dateiname> aus. Beispiel: \i create.sql
- \o <dateiname>: Lenkt die Ausgabe in eine Datei mit dem Namen <dateiname>um. Lässt man den Parameter <dateiname> weg, so wird dieses Verhalten wieder abgestellt. Beispiel: \o index.txt
Kleine Klarstellung (durch "[Hinweis" markiert)
Version 1.2 (2012-11-16):
Änderung an den Codevorlage-ZIPs (für Windows-Linebreaks)