Aufgabe 10 - Bibliotheksmodell und relationales Schema (PostgreSQL)

Aufgabe 10 - Bibliotheksmodell und relationales Schema (PostgreSQL)

Worum geht es?

In dieser Übung wird die Brücke vom ER-Modell zur fertigen Datenbank geschlagen: ein Bibliotheks-Szenario wird modelliert, in ein relationales Schema überführt und als echte CREATE TABLE-Anweisungen für PostgreSQL umgesetzt (siehe Kapitel 4 - Relationenmodell). Im Expertenteil wird eine anspruchsvolle Geschäftsregel direkt in der Datenbank erzwungen.

Was Sie dafür brauchen

• Kapitel 4 - Relationenmodell und Kapitel 5 - SQL-Grundlagen.
• draw.io (diagrams.net) und ein PostgreSQL-Server (aus Aufgabe 09).

Welche Kompetenzen Sie erwerben und zeigen

• Sie leiten aus einer Beschreibung ein ER-Modell und daraus ein relationales Schema ab.
• Sie lösen eine n:m-Beziehung über eine Zwischentabelle auf.
• Sie wählen passende Datentypen und Integritätsbedingungen (NOT NULL, UNIQUE, CHECK, FOREIGN KEY).
• Sie erzwingen eine zeitliche Geschäftsregel mit einem partiellen Unique-Index.

Pädagogische Einordnung

• Reproduktion: Faktenbasis, Entities und Attribute ablesen (Teil A).
• Reorganisation und Transfer: ER-Modell und relationales Schema ableiten und umsetzen (Teile B und C).
• Reflexion, Problemlösung und Urteilsbildung: eine Geschäftsregel in der Datenbank erzwingen und testen (Teil D).

Arbeitsaufträge

Die Übung ist auf etwa zwei Stunden ausgelegt. Teil D ist der Expertenteil.

Szenario

Eine Schulbibliothek verwaltet Bücher (Titel, Erscheinungsjahr, ISBN). Von einem Buch gibt es mehrere Exemplare (Signatur/Inventarnummer). Jedes Buch hat mindestens eine Autorin/einen Autor (Name, optional Geburtsjahr); ein Buch kann mehrere Autoren haben. Mitglieder (Name, Mitgliedsnummer, E-Mail) leihen Exemplare aus. Eine Ausleihe verbindet ein Exemplar mit einem Mitglied und trägt Ausleihdatum, Rückgabedatum (bei offenen Ausleihen leer) und optional eine Mahngebühr. Ein Exemplar kann über die Zeit mehrmals ausgeliehen werden, aber zu einem Zeitpunkt höchstens von einem Mitglied aktiv.

Pflicht-Entities: Buch, Exemplar, Autor, Mitglied, Ausleihe.

Teil A - Faktenbasis, Entities und Attribute

1. Die Faktenbasis stichwortartig festhalten und markieren, was Entity, Attribut oder Beziehung ist.
2. Für jede Entity Attribute und einen Primärschlüsselkandidaten festlegen (künstliche ID vs. natürlicher Schlüssel wie ISBN abwägen).

Teil B - ER-Diagramm

1. Die Beziehungen Buch–Autor (n:m), Buch–Exemplar (1:n), Mitglied–Ausleihe (1:n) und Exemplar–Ausleihe (1:n) in der IE-Notation mit draw.io zeichnen, mit Kardinalitäten und Minimalbeteiligungen.

Teil C - Relationales Schema und CREATE TABLE

1. Das relationale Schema in Textform ableiten (Tabellen mit Datentyp, PK, FK). Die n:m-Beziehung Buch–Autor über eine Zwischentabelle buch_autor auflösen.
2. Eine Datenbank bibliothek anlegen und das Schema als lauffähige CREATE TABLE-Anweisungen für PostgreSQL umsetzen. Sinnvolle NOT NULL- und UNIQUE-Bedingungen setzen (z. B. isbn eindeutig, signatur eindeutig, mitgliedsnummer eindeutig).
3. Mit einigen INSERT-Zeilen Testdaten einfügen und mit SELECT prüfen.

Teil D - Expertenteil: Geschäftsregel in der Datenbank erzwingen

1. CHECK für Datumslogik: Sicherstellen, dass rueckgabedatum nie vor ausleihdatum liegt (CHECK (rueckgabedatum IS NULL OR rueckgabedatum >= ausleihdatum)).

2. Aktive Ausleihe eindeutig: Die Regel „ein Exemplar ist zu einem Zeitpunkt höchstens einmal aktiv entlehnt” mit einem partiellen Unique-Index erzwingen. Eine aktive Ausleihe ist eine mit rueckgabedatum IS NULL:

   CREATE UNIQUE INDEX uq_aktive_ausleihe
     ON ausleihe (exemplar_id)
     WHERE rueckgabedatum IS NULL;

3. Regel testen: Eine Ausleihe für ein Exemplar anlegen (offen), dann eine zweite offene Ausleihe für dasselbe Exemplar versuchen und zeigen, dass sie scheitert. Danach die erste Ausleihe zurückgeben (rueckgabedatum setzen) und zeigen, dass eine neue Ausleihe jetzt funktioniert.

4. Begründen: In drei bis vier Sätzen erklären, warum ein gewöhnlicher UNIQUE-Constraint auf exemplar_id diese Regel nicht abbilden könnte und warum der partielle Index der richtige Weg ist.

Wissenscheck

1. Wie wird eine n:m-Beziehung (Buch ↔ Autor) im relationalen Schema umgesetzt?
2. Worin unterscheiden sich Buch und Exemplar, und warum braucht man beide Tabellen?
3. Wann verwendet man einen künstlichen Schlüssel statt eines natürlichen wie der ISBN?
4. Was leistet ein partieller Unique-Index, das ein gewöhnlicher UNIQUE-Constraint nicht kann?
5. Warum genügt rueckgabedatum IS NULL als Kennzeichen einer aktiven Ausleihe?

Abgabe

• Die draw.io-Datei mit dem ER-Modell.
• Ein Dokument mit Faktenbasis, relationalem Schema und den Designentscheidungen.
• Eine SQL-Datei mit CREATE TABLE, Testdaten und dem Expertenteil D (Index und Testabfragen).

---

HTL Villach, Schuljahr 2025-2026,
https://www.htl-villach.at