Datenbanksysteme (VU 4.0, 184.686)
Übungsteil, WS 2014/2015
Beispiel 3
Große Teile von Beispiel 3 basieren auf der im Zuge von Beispiel 2 erstellten Datenbank. Sie können entweder die Musterlösung als Basis für diese Aufgabe verwenden oder ihre eigene Lösung weiterverwenden. Die Musterlösung wird mit Ablauf der Abgabegespräche zu Beispiel 2 auf der Übungsseite der Lehrveranstaltung veröffentlicht.
Wenn Sie ihre eigene Lösung weiterverwenden wollen, kann es passieren, dass aufgrund anderer Namensgebung (für Tabellen und/oder Attribute) kleine Adaptierungen vorgenommen werden müssen. Konkret sind einige Codeblöcke im Java-Teil vorgegeben, welche auf die Bezeichnungen der Musterlösung abgestimmt sind. Diese müssten Sie unter Umständen anpassen, oder Sie passen ihre Lösung aus Beispiel 2 daran an.
Grundgerüst
Als Grundgerüst finden Sie auf der Übungsseite ein ZIP-Archiv mit einer grundlegenden Projektstruktur die Sie als Vorlage für diese Übung nutzen müssen. Weiters wird Ihnen in diesem Gerüst ein ant-Skript zur Verfügung gestellt (build.xml) um das Testen und die Abgabe der Übung zu vereinfachen: dbs-template-r2.zip
Beachten Sie:- Alle Dateien und Pfade beziehen sich auf diese Vorlage
- Genauere Instruktionen zur Verwendung der ant Targets finden Sie in den einzelnen Übungsbeschreibungen
- Die Musterlösung wurde in dieser Version (r2) bereits eingefügt. Sie können diese aber auch durch ihre eigene Lösung ersetzen.
- Um ihre Datenbank vorzubereiten, müssen Sie folgende Dateien aus dem Ordner
resourcesmittelspsqlladen:create.sqlplpgsql-teil.sqlinsert.sqltest.sql
Teil 1 - Abfragen
Die folgenden Aufgabenstellungen basieren auf der Datenbank, welche Sie für
Beispiel 2 des Übungsteils zu erstellen hatten. Sie können entweder auf
Ihrer Datenbank weiterarbeiten, oder die Musterlösung des zweiten Beispiels verwenden,
welche auf der LVA-Homepage zum Download bereitsteht (allerdings erst nach den Abgabegesprächen
zu Beispiel 2). Stellen Sie allerdings in jedem Fall in Ihrer Abgabe die Create-,
Insert- und Drop-Befehle jener Datenbank bereit, auf der Sie arbeiten. Ersetzen Sie dazu im Verzeichnis resources in der Vorlage die Dateien.
Stellen Sie sicher, dass die Daten, mit denen Sie testen, der Spezifikation entsprechen (d.h. Kardinalitäten berücksichtigen usw.). Sie müssen keine Fälle berücksichtigen, die der Spezifikation widersprechen.
Erstellen Sie für folgende Abfrage einen View. Views können wie Tabellen verwendet werden,
werden jedoch nicht materialisiert. In PostgreSQL können Views folgendermaßen erstellt werden:
CREATE VIEW viewname AS query.
Lösen Sie die folgenden Probleme mittels SQL:
- Wählen Sie per Hand eine Krankheit aus. Schreiben Sie eine Anfrage, die für diese Krankheit, ihre Klasse ausgibt, sowie rekursiv alle übergeordneten Klassen. Geben Sie für jede Klasse den Namen, und den Namen der übergeordneten Klasse sowie die Stufe der Klasse in der Hierarchie aus. Die Stufe der jeweiligen Klasse ergibt sich dabei aus der Stufe der untergeordneten Klasse plus 1, wobei die Klasse, der von Ihnen per Hand ausgewählten Krankheit, logischerweise mit Stufe 0 beginnt. Zu der Klasse, der von Ihnen ausgewählten Krankheit, soll es in der Datenbank mindestens zwei Ebenen übergeordneter Klassen geben.
- Geben Sie den Namen und den Gesamtlohn (Summe aller Lohnzettel) des Arztes mit dem niedrigsten und mit dem höchsten Gesamtlohn aus.
Teil 2a - Java (Concurrency)
Beachten Sie bitte, dass die vorgegebenen Codezeilen, die durch einen Kommentarblock (“Vorgegebener Codeteil”) markiert sind und die durch eine Kommentarzeile aus Hashtags (#) geschlossen werden, nicht verändert werden dürfen.
Die ersten beiden Szenarien (Szenario1.java, Szenario2.java)
sind so angelegt, dass es eine von Ihnen zu ergänzende Transaktion gibt und
eine bereits existierende Transaktion, die Ihnen von uns zur Verfügung gestellt
wird.
Sie müssen hierbei 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 Folgende:
- “a” oder “b” für die Wahl der jeweiligen Transaktion
- Servername
- Port-Nummer
- Datenbank-Name
- Username (optional)
- Passwort (optional)
Zum vereinfachten Testen haben wir Ihnen in diesem Semester auch ein ant-Skript zur Verfügung gestellt. 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 von uns bereitgestellte Transaktion zu Szenario 1)ant run-szenario2-a(Für Ihre eigene Implementierung von Szenario 2)ant run-szenario2-b(Für die von uns 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 eine Eingabe 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 nach der Spezifikation 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.
Sie müssen anschließend beim Abgabegespräch Ihr Vorgehen bei der Erstellung und auch die Funktionsweise des Programms erklären können, insbesondere auch die Frage, warum Sie der Meinung sind, dass Ihr Programm die Aufgabenstellung optimal erfüllt.
Szenario 1:
Für den Jahresrückblick werden vom Leiter des Krankenhauses Statistiken verlangt. Für die Erfüllung dieser Aufgabe benötigt der Leiter folgende Informationen:
- Die Anzahl der Patienten im Krankenhaus
- sowie die Anzahl der Patienten pro Abteilung (verwenden Sie den erstellten View) und
- das Verhältnis dieser beiden Werte
Diese Zahlenwerte sollen in einer Transaktion ermittelt werden.
Da diese Statistiken aus der Sicht des Leiters 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 durch geeignete SELECT-Befehle 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:
Neben der oberen Statistik wurden von den Krankenhäusern auch noch zusätzliche Statistiken angefordert. Es sollen für das Krankenhaus mit der Nummer '10' für jede Abteilung folgende Informationen ausgegeben werden: Der Name der Abteilung, die Krankheiten, auf die diese Abteilung spezialisiert ist, und die Anzahl der Patienten, die zu dieser Krankheit in dieser Abteilung in Behandlung sind. Das Ergebnis soll nach dem Abteilungsnamen aufsteigend und dann nach der Anzahl der Patienten absteigend sortiert werden.
In der gleichen Transaktion sollen auch der Name und die Anzahl der Patienten pro Abteilung des Krankenhauses mit der Nummer '10' ausgegeben werden (verwenden Sie die erstellte View). Diese Daten sollen aufsteigend nach dem Namen der Abteilung sortiert werden.
Der Krankenhausleiter möchte die Daten gerne mobil abrufen, deshalb 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. Ebenso sollen alle Berechnungen und Sortierungen von der Datenbank übernommen werden. Ihre Java-Anwendung soll die abgerufenen Daten nur mehr anzeigen.
Der Leiter wünscht auch ausdrücklich, dass es nicht möglich sein soll, das 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 jeweils Listen der Ergebnisse der beiden Abfragen ausgeben. Achten Sie darauf, dass Sie unerwünschtes Verhalten vermeiden, aber die Datenbank nicht durch ein zu restriktives Isolation-Level unnötig blockieren.
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.
Hinweis:
Im Rahmen des Abgabegesprächs wird nicht nur die Korrektheit der Lösungen, sondern vor allem auch das Verständnis überprüft. Sie sollten beim Abgabegespräch in der Lage sein, zu erklären wie sich die Wahl des Isolation-Levels auf das Programmverhalten auswirkt. Zudem ist ein gewisses Basiswissen im Bereich Concurrency gefordert, dazu zählt zum Beispiel das Verständnis von Transaktionen (sprich z.B. ACID), die Charakteristika von Isolation-Levels, oder welche Anomalien die einzelnen Isolation-Levels beheben (z.B. Dirty Read).
Teil 2b: Java (PL/pgSQL)
In der dritten Datei (Szenario3.java) sollen Sie die Methode PrintAkteneintraege()
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 Folgende:
- Servername
- Port-Nummer
- Datenbank-Name
- Username (optional)
- Passwort (optional)
Auch hier müssen Sie beim Abgabegespräch in der Lage sein, detaillierte Fragen zum Verständnis der Anwendung und Ihres Vorgehens bei der Implementierung zu beantworten.
Szenario 3: (Akteneinträge)
Es sollen für jede Person die Akteneinträge und eine kurze Statistik dazu ausgegeben werden.
Die folgenden Aktionen sollen von der Prozedur ausgeführt werden:
- Erstellen Sie ein Prepared Statement, um für eine übergebene Person alle Akteneinträge aufzulisten.
- Benutzen sie das oben beschriebene Prepared Statement um für jede Person
alle Akteneinträge auszugeben und erstellen Sie eine kurze Statistik. Folgende Daten soll die Statistik pro Person enthalten:
- Anzahl der Akteneinträge
- Maximale Krankenhausaufenthaltsdauer
- Minimale Krankenhausaufenthaltsdauer
- Durchschnittliche Krankenhausaufenthaltsdauer
Abgabe
Um ein abgabefertiges Archiv dbs-exercise3-ws14.zip zu erzeugen, führen Sie folgenden Befehl aus:
ant zip
Diese ZIP-Datei ist bis spätestens 30.11.2014 um 23:59 im CourseManager abzugeben. Es wird stets die zuletzt hochgeladene Version Ihrer Lösung gewertet.
Führen Sie keine Änderung an der “Testumgebung” aus Teil
2 (DBConnector.java, Main-Methoden, usw.) durch, da ansonsten ihre
Abgabe im Rahmen des Abgabegesprächs möglicherweise nicht ausgeführt
werden kann.
Abgabegespräche
Die Verteilung der Punkte erfolgt nach folgendem Schlüssel:
- Datei
view.sql: 1 - Datei
queries.sql: 2 - Datei
Szenario1.java: 4 - Datei
Szenario2.java: 4 - Datei
Szenario3.java: 4
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 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