Datenbanksysteme (VU 4.0, 184.686)
Übungsteil, WS 2015/16
Beispiel 1
Eine Eventtechnikfirma möchte seine Auftragsverwaltung in einer Datenbank erfassen. Dazu sollen folgende Informationen gespeichert werden:
- Jedem/r Kunden/in wird eine Nummer (KNr) zugeordnet über die diese/r eindeutig identifiziert werden kann. Der Name jeder Person wird ebenso gespeichert. Die restlichen Daten werden historisch in der Tabelle Kundendaten gespeichert, dh. es ist zu jedem Zeitpunkt möglich, die aktuelle Anschrift und den aktuellen Sonderrabatt zu ermitteln.
- Ein Auftrag wird genau von einer Kundin oder einem Kunden erstellt. Jeder Auftrag
wird eindeutig durch eine Auftragsnummer (ANr) identifiziert, weiters wird das
Auftragsdatum gespeichert.
Aufträge enthalten mindestens eine Auftragsposition. Diese wird durch den Auftrag und einer Nr identifiziert. Zusätzlich werden ein Liefertermin und ein Rabatt gespeichert.
Eine Auftragsposition ist entweder eine Leistungsposition, dh. es wurde eine bestimmte Leistung angefordert, oder eine sonstige Position. Für die sonstige Position wird ein Wert und eine Beschreibung gespeichert. Für die Leistungsposition die Menge und die zu erbringende Leistung. - Eine Leistung wird durch eine eindeutige Nummer (LNr) identifiziert und hat eine Bezeichnung und einen Preis. Leistungen können eine bestimmte Menge anderer Leistungen benötigen. Jede Leistung ist einer Kategorie zugeordnet.
- Jede Kategorie besitzt neben einer eindeutigen Nummer (KNr), einen Namen, und optional eine "Topleistung". Diese Leistung soll diese Kategorie repräsentieren, z.B. auf der Kategorieseite in einem zukünftigen Online-Shop.
- Nachdem ein Auftrag zur Gänze oder teilweise erfüllt worden ist, wird für die erbrachte Leistung eine Rechnung mit einer eindeutigen Nummer (RNr) und einem Rechnungsdatum angelegt. Jede Rechnung hat wiederum mindestens eine oder mehrere Rechnungspositionen. Diese werden durch eine Nummer (RPNr) identifiziert, haben einen Wert, einen Text, und gehören zu genau einer Auftragsposition.
Sie finden nun im folgenden Bild ein ER-Diagramm, das die grobe Struktur der Datenbank abbildet, wobei Sie davon ausgehen können, dass die Abbildung sowohl den Sachverhalt als auch alle Kardinalitäten bereits korrekt abdeckt.
- Die korrekten CREATE-, INSERT- und DROP-Befehle erstellen, um alle vorhin genannten Informationen verwalten zu können.
- Vergessen Sie nicht, die Schlüssel und Fremdschlüssel der einzelnen Tabellen korrekt abzubilden!
Bei der Überführung in die CREATE-Anweisungen werden Sie feststellen, dass NULL-Werte durch das ERD erlaubt sind. Beim Abgabegespräch sollten Sie in der Lage sein, die betroffenen Tabellen zu nennen und Vorschläge zu machen, wie man diese NULL-Werte vermeiden kann. Es ist aber nicht notwendig, NULL-Werte bei den CREATE-Befehlen zu vermeiden und die mündliche Erklärung während des Abgabegesprächs ist ausreichend.
SQL
- Erstellen Sie eine Query, die alle Auftragsnummern ausgibt, welche noch unerledigte Auftragspositionen haben (Unerledigte Auftragspositionen haben keine zugehörige Rechnungsposition).
- Erstellen Sie eine View, die den Preis jeder Leistung inklusive der benötigten Leistungen ausgibt. Beachten Sie das Attribut Menge in der "benötigt" Relation. Hinweis: Die gesuchte Query verwendet WITH RECURSIVE.
- Erstellen Sie eine View, die alle Kunden mit ihren aktuellen (neuestes Erstelldatum) Kundendaten ausgibt.
PL/pgSQL
Erstellen Sie folgende Trigger:
- Rechnungspositionen einer Rechnung gehören immer zum selben Auftrag.
- Eine Auftragsposition kann entweder eine Sonstige Position oder eine Leistungsposition sein (nicht beides).
- Leistungen dürfen nicht zirkulär voneinander abhängig sein. Zum Beispiel kann die Leistung mit der Nummer 1 nicht die Nummer 2 benötigen welche wiederum Leistung Nummer 1 benötigt (mit beliebig vielen Zwischenstationen). Hinweis: Der Trigger verwendet eine Query mit WITH RECURSIVE.
Function:
- Schreiben Sie eine Funktion
f_calc_apos, welche als Parameter die Auftragsnummer und die APNr einer Auftragsposition erhält.
Die Funktionf_calc_apossoll nun für die übergebene Auftragsposition den Wert berechnen. Wobei folgendes zu beachten gilt:- Für eine Sonstige Position ist der zurückgegebene Wert, der Wert der Sonstigen Position minus dem Positionsrabatt minus dem aktuellen Sonderrabatt des Kunden.
- Für eine Leistungsposition muss der Wert der Leistung inklusiver aller benötigten Leistungen ermittelt und mit der bestellten Menge multipliziert werden (sie können die angelegte View verwenden). Von diesem Betrag werden der Positionsrabatt minus der Sonderrabatt des Kunden abgezogen.
Aufgabenstellung
-
Leiten Sie aus dem ER-Diagramm die Relationen der Datenbank in 3. Normalform so ab, dass sie verbundtreu und abhängigkeitstreu sind. Sie können die Ableitung der Relationen gleich unmittelbar in der geforderten Datei
create.sqlvornehmen und müssen KEIN explizites Dokument für das Relationenmodell (wie in den vorangegangenen Semestern) erstellen. -
Erstellen Sie eine Datei
create.sql, in welcher die nötigen CREATE-Befehle gespeichert werden, um die Relationen mittels SQL zu realisieren. Dabei sind folgende Punkte zu beachten:- Wählen Sie für Geldbeträge keine Gleitkommatypen sondern z. B. NUMERIC mit zwei Nachkommastellen.
- Realisieren Sie die fortlaufende Nummerierung der künstlichen Primärschlüssel-Attribute mit Hilfe von Sequences. Die Sequence für den Schlüssel der Tabelle Auftrag soll bei 10 beginnen und in Zehnerschritten erhöht werden (d.h. 10, 20, 30, ...).
- Sollten zwischen zwei Tabellen zyklische FOREIGN KEY Beziehungen existieren, so achten Sie darauf, dass eine Überprüfung dieser FOREIGN KEYs erst zum Zeitpunkt eines COMMITs stattfindet.
- Verwenden Sie keine Umlaute für Bezeichnungen von Relationen, Attributen, etc.
- Stellen Sie die folgenden Sachverhalte durch geeignete CHECK-Bedingungen sicher:
- Alle Werte bzw. Preise müssen größer gleich 1 sein.
-
Erstellen Sie eine weitere Datei
insert.sql, welche die INSERT-Befehle für die Testdaten der in Punkt 2 erstellten Tabellen enthält. Jede Tabelle soll zumindest drei Zeilen enthalten. Sie dürfen die Wahl der Namen, Bezeichnungen etc. so einfach wie möglich gestalten, d. h. Sie müssen nicht "real existierende" Aufträge, Personen, Leistungen, etc. wählen. Stattdessen können Sie auch einfach "Auftrag 1", "Auftrag 2", "Leistung 1", "Leistung 2" etc. verwenden. -
Erstellen Sie eine Datei
queries.sql, welche den Code für die SQL Query und die beiden Views enthält. -
Erstellen Sie eine Datei
plpgsql-teil.sql, welche den Code für die Trigger und die Funktionf_calc_aposenthält. -
Erstellen Sie eine Datei
drop.sql, welche die nötigen DROP-Befehle enthält, um alle in den Punkten 2 und 4 erzeugten Datenbankobjekte wieder zu löschen. Das Schlüsselwort CASCADE darf dabei NICHT verwendet werden. -
Überlegen Sie sich eine sinnvolle Testabdeckung für die PL/pgSQL-Programmteile laut Punkt 5, z.B.: Erweiterung der Testdaten, Aufruf der zu testenden PL/pgSQL-Programmteile mit entsprechenden Ausgaben, so dass sich die erfolgreiche Durchführung der Tests überprüfen lässt. Denken Sie auch an negative Tests, welche mit einer Exception enden sollen, z.B. Aufruf der Funktion
f_calc_aposmit einer falschen Auftragsnummer.
Stellen Sie in Ihrem ZIP-Archiv die SQL-Dateien mit den zusätzlichen INSERT-Befehlen und den "Testtreibern" in der Dateitest.sqlbereit. Sie müssen in der Lage sein, diese SQL-Dateien und PL/pgSQL-Dateien im Rahmen des Abgabegesprächs ablaufen zu lassen. -
Stellen Sie in Ihrem Abgabearchiv eine Listing-Datei mit dem Namen
listing.txtbereit, die Sie bei der Ausführung der SQL-Dateien erzeugt haben. Diese Datei soll alle Informationen beinhalten, die beim Ablauf der Dateiencreate.sql, insert.sql, queries.sql plpgsql-teil.sql, test.sqlunddrop.sqlerzeugt werden. Beachten Sie dazu bitte die Hinweise zur Benützung von psql am Ende dieses Dokuments. -
Im Rahmen des Abgabegesprächs müssen Sie in der Lage sein, alle von Ihnen in der Abgabe bereitgestellten Dateien erklären zu können. Weiters wird erwartet, dass Sie auch das ER-Diagramm einwandfrei analysieren können, auch wenn es in diesem Semester bereits vorgegeben wurde. Bitte achten Sie auch darauf, dass sich alle von Ihnen abgegebenen Dateien auf dem bordo-Server ausführen lassen, um Probleme (und möglicherweise daraus resultierenden Punktabzug) zu vermeiden
Abgabe
Zusammenfassend sind nun folgende Dateien abzugeben:
create.sqlinsert.sqldrop.sqlqueries.sqlplpgsql-teil.sqltest.sqllisting.txt
Die aufgezählten Dateien sind in einer ZIP-Datei beispiel1.zip bis
spätestens 02.11.2015 um 23:59 im TUWEL
abzugeben. Es wird stets die zuletzt hochgeladene Version Ihrer Lösung gewertet.
Achten Sie darauf, dass der Name jeder dieser abzugebenden 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:
- SQL Teil (
create.sql,insert.sql,drop.sql): max. 4 Punkte - Queries Teil (
queries.sql): max. 2 Punkte - PL/pgSQL Teil (
plpgsql-teil.sql,test.sql): max. 4 Punkte listing.txt: Vorhandensein für das Erreichen der vollen Punkteanzahl notwendig
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 listing.txt