Datenbanksysteme (VL 4.0, 181.186) Laborübungsteil, WS 2010/2011
Beispiel 2
Sie sollen für die öffentlichen Stadtbüchereien in Wien eine Datenbank erstellen, die folgende Informationen beinhaltet:
- Jede Person - egal ob Mitarbeiter oder Kunde der Büchereien - wird durch eine eindeutige Personen-ID („pid“) identifiziert. Weiters werden der Vorname („vname“), der Nachname („nname“), das Geburtsdatum („gebdat“) und die Wohnadresse („adresse“) vermerkt. Bei Mitarbeitern wird außerdem die Sozialversicherungsnummer („svnr“), das Datum ihrer Einstellung („eindat“), das aktuelle Monatsgehalt („gehalt“), die Kontonummer („kontonr“) und die Bankleitzahl („blz“) gespeichert.
- Jeder Mitarbeiter arbeitet in genau einer Zweigstelle der Büchereien. Zweigstellen haben einen eindeutigen Namen („name“), eine Anschrift („anschrift“), eine Telefonnummer („telefon“), eine email-Adresse („email“) und werden von genau einem Mitarbeiter geleitet.
- Die Büchereien verfügen über verschiedene Medien, die sich alle durch eine eindeutige Nummer („mediennr“) identifizieren lassen. Zudem wird ihr Erscheinungsjahr („jahr“) und Titel („titel“) gespeichert, sowie das Alter, ab dem sie freigegeben sind („freigabe“). Für Medien ohne Altersbeschränkung soll der Wert „0“ eingetragen werden. Medien sind mindestens einem Themengebiet zugeordnet. Jedes Themengebiet hat eine eindeutige ID („themenid“) und einen Namen („name“). Weiters sind Themengebiete hierarchisch organisiert, d.h. zu jedem Themengebiet kann es untergeordnete Themengebiete geben, wobei jedes Themengebiet aber immer nur maximal ein übergeordnetes Themengebiet haben kann (zum Beispiel ist das Themengebiet „Außereuropäische Kunst“ in „Amerikanische Kunst“, „Asiatische Kunst“ und „Afrikanische Kunst“ unterteilt, wobei „Amerikanische Kunst“ wiederum in „Nordamerikanische Kunst“ und „Lateinamerikanische Kunst“ unterteilt ist, „Asiatische Kunst“ hingegen in „Japan“, „China“ und „Indien“.)
- Medien unterteilen sich in Bücher und Filme. Bücher haben eine gewisse Seitenanzahl („umfang“) und werden von ein oder mehreren Künstlern als Autoren verfasst, wohingegen Filme ein Format („format“) und einen Künstler als Regisseur besitzen. Beachten Sie, dass bei den Formatangaben nur die Werte „8mm“, „DVD“ und „VHS“ zulässig sind. Künstler werden durch eine eindeutige ID („kid“) identifiziert und besitzen einen Vornamen („vname“) und einen Nachnamen („nname“).
- Die Bücherei besitzt von jedem Medium ein oder mehrere Exemplare. Exemplare werden über eine pro Medium eindeutige Exemplar-Nummer („enr“) identifiziert. Zusätzlich wird für jedes Exemplar das Datum ihres Ankaufs („kaufdat“) sowie die Zweigstelle vermerkt, in deren Besitz es sich befindet. Exemplare können selbstverständlich entlehnt werden. Dazu ist allerdings eine Entlehnkarte vonnöten: Jede Entlehnkarte hat eine eindeutige Nummer („nr“), ist bis zu einem bestimmten Datum gültig („gueltig“) und gehört einem bestimmten Kunden (wobei natürlich auch Mitarbeiter eine Entlehnkarte besitzen können). Weil bei Entlehnungen manchmal Gebühren anfallen, werden bei jeder Entlehnkarte die aktuellen offenen Gebühren vermerkt („gebuehr“).
- Mit der Entlehnkarte ist es nun möglich, Exemplare auszuleihen. Vermerken Sie für jede Entlehnung, das Datum der Entlehnung („adat“), die Entlehnkarte, mit der die Entlehnung durchgeführt wird, die ausgeliehenen Exemplare sowie das Fälligkeitsdatum („fdat“), bis zu dem die entliehenen Exemplare spätestens zurückgegeben werden müssen. Vergeben Sie für jede Entlehnung eine eindeutige ID („eid“).
- Erfassen Sie nun auch noch die Rückgaben: Jede Rückgabe findet an einem bestimmten Datum („rdat“), in einer bestimmen Zweigstelle statt, gehört zu einer bestimmten Entlehnung und enthält genau ein Exemplar, das zurückgegeben wird.
Aufgabenstellung
Erstellen Sie für die oben beschriebene Datenbank ein Entity-Relationship-Diagramm. Benützen Sie dazu die (min, max)-Notation wie im Lehrbuch von Kemper/Eickler bzw. in der VU Datenmodellierung beschrieben. Andere Notationen werden nicht akzeptiert. Erstellen Sie das ER-Diagramm mit einem Grafikprogramm (z. B. Dia, Visio).
Bedenken Sie dabei, dass an zwei Stellen (für Zweigstellen, sowie bei Personen) Adressen gespeichert werden müssen. Welche Vor- und Nachteile entstehen, wenn Sie einen eigenen Entitytypen für Adressen erstellen würden, wobei jede Adresse von einem künstlichen Schlüssel („aid“) identifiziert wird?
Leiten Sie aus dem ER-Diagramm die Relationen der Datenbank in 3. Normalform so ab, dass sie verbundtreu und abhängigkeitstreu sind. Halten Sie diese Relationen schriftlich fest und machen Sie dabei PRIMARY und FOREIGN KEYS eindeutig kenntlich. Folgende Notation wird empfohlen:
RelX (attr1, attr2, attr3, attr4: RelY.attrZ)
bedeutet, dass die Datenbank eine Relation RelX enthält. Diese Relation hat 4 Attribute attr1, attr2, attr3 und attr4. Der Primary Key dieser Relation besteht aus den Attributen attr1 und attr2. Das Attribut attr4 ist ein Foreign Key auf das Attribut attrZ in der Relation RelY.
Das Entity-Relationship-Diagramm sowie das Relationenmodell sollen gemeinsam in der Datei
entwurf.pdfgespeichert werden. Für die Erstellung des PDFs kann beispielsweise das Tool doc2pdf verwendet werden.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:- Die Datenbank soll keine NULL-Werte enthalten.
- Stellen Sie sicher, dass trotz des künstlichen Schlüssel in der Relation „Person“ niemals dieselbe Person mehrfach vorkommen kann. Stellen Sie dazu sicher, dass die Kombination aus Vorname, Nachname, Geburtsdatum und Adresse eindeutig ist.
- Realisieren Sie folgende Attribute mit fortlaufenden Nummern mit Hilfe von Sequences: „nr“ von Entlehnkarte, „mediennr“ von Medien und „kid“ von Künstler. Das „nr“-Attribut soll bei 1.000.000.000 beginnen und anschließend in 15er-Schritten fortgesetzt werden (d.h. 1.000.000.015, 1.000.000.030, 1.000.000.045, ...); „mediennr“ und „kid“ sollen positive ganze Zahlen sein.
- Sollten zwischen 2 Tabellen zyklische FOREIGN KEY Beziehungen existieren, so achten Sie darauf, dass eine Überprüfung dieser FOREIGN KEYs erst zum Zeitpunkt eines COMMIT stattfindet.
Erstellen Sie eine weitere Datei
insert.sql, welche die INSERT-Befehle für die Testdaten der in Punkt 3 erstellten Tabellen enthält. Jede Tabelle soll zumindest 6 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“ Künstler, Bücher, Filme, etc. wählen, Stattdessen können sie ruhig „Buch 1“, „Buch 2“, „Film 1“, „Film 2“ etc. verwenden.Erstellen Sie eine Datei
drop.sql, welche die nötigen DROP-Befehle enthält, um alle in Punkt 3 erzeugten Datenbankobjekte wieder zu löschen. Das SchlüsselwortCASCADEdarf dabei nicht verwendet werden.
Für die Abgabe bereitzustellen
- Bringen Sie bitte Ihren Studentenausweis zur Abgabe mit. Eine Abgabe ohne Ausweis ist nicht möglich.
- Stellen Sie sicher, dass die SQL-Befehle (laut Punkten 3-5) unter PostgreSQL 8.4 auf dem Server bordo.dbai.tuwien.ac.at fehlerlos laufen. Sie müssen in der Lage sein, die von Ihnen bereitgestellten SQL-Dateien im Rahmen des Abgabegesprächs ablaufen lassen.
- Stellen Sie in Ihrem Abgabeverzeichnis eine Listing-Datei mit dem Namen
listing.txtbereit, die Sie beim Test der SQL-Daten erzeugt haben. Diese Datei soll alle Informationen beinhalten, die beim Ablauf der Dateiencreate.sql,insert.sqlunddrop.sqlerzeugt werden. Beachten Sie dazu bitte die Hinweise zur Benützung von psql am Ende dieses Dokuments. - Zusammenfassung: In Summe sind also folgende 5 Dateien zu erstellen:
entwurf.pdfcreate.sqlinsert.sqldrop.sqllisting.txt
- Die Beispiele müssen bis zum Abgabetermin am Sonntag, den 7. November 2010 um Mitternacht auf unserem Server (bordo.dbai.tuwien.ac.at)
im Unterverzeichnis
beispiel2verfügbar sein (die Dateien werden automatisch abgesammelt und den Tutorinnen und Tutoren zur Verfügung gestellt). Es sind keine Ausdrucke erforderlich. - Wir erwarten von Ihnen eigenständige Lösungen. Plagiate werden nicht akzeptiert.
Bewertung
Für das Übungsbeispiel 2 werden maximal 10 Punkte vergeben. Im Rahmen des Abgabegesprä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 auch entsprechend zu erklären.
Die Verteilung der Punkte erfolgt nach folgendem Schlüssel:
- Datei
entwurf.pdf: max. 5 Punkte (max. 3 Punkte für das ER-Diagramm; max. 2 Punkte für das Relationenmodell) - Datei
create.sql: max. 2 Punkte - Datei
insert.sql: max. 2 Punkte - Datei
drop.sql: max. 1 Punkt - Datei
listing.txt: Vorhandensein für das Erreichen der vollen Punktezahl notwendig.
Hinweise zur Verwendung von psql
Folgende Befehle können für Ihre Arbeit mit der interaktiven SQL-Shell psql von PostgreSQL 8.4 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