Aufgabe 10 - Bibliotheksmodell und relationales Schema (PostgreSQL)

Worum geht es?

In dieser Übung schlagen Sie die Brücke vom ER-Modell zur fertigen Datenbank: Sie modellieren ein Bibliotheks-Szenario, leiten daraus ein relationales Schema ab und setzen es in echte CREATE TABLE-Anweisungen für PostgreSQL um (siehe Kapitel 4 - Relationenmodell).

In dieser Übung üben Sie:

Ableiten: aus der Beschreibung eine Faktenbasis erstellen.
Identifizieren: Entitäten (Buch, Exemplar, Autor*in, Mitglied, Ausleihe) und Attribute bestimmen.
Modellieren: Beziehungsarten (1:n, n:m) korrekt abbilden.
Überführen: ein relationales Schema mit Primär- und Fremdschlüsseln ableiten.
Auswählen: geeignete PostgreSQL-Datentypen und Integritätsbedingungen festlegen.
Anwenden: das Schema als CREATE TABLE-Anweisungen umsetzen und mit Testdaten prüfen.

Benötigte Unterlagen

Faktenbasis (Textbeschreibung, siehe unten),
Zeichenwerkzeug für ER-Modelle (z. B. draw.io oder vergleichbar),
Textverarbeitung (Word oder vergleichbar) für die Beschreibung des relationalen Schemas,
optional: Zugriff auf eine PostgreSQL-Installation, um das Schema später testen zu können.

Szenario: Schulbibliothek / Mediathek

Eine HTL betreibt eine kleine Schulbibliothek (Mediathek), in der Bücher an Schüler und Schülerinnen und Lehrer und Lehrerinnen verliehen werden.

Für die Verwaltung sollen folgende Aspekte modelliert werden:

Die Bibliothek verwaltet Bücher. Ein Buch hat u. a. einen Titel, ein Erscheinungsjahr und eine ISBN.
Von vielen Büchern gibt es mehrere Exemplare (physische Stücke). Jedes Exemplar erhält eine Signatur oder Inventarnummer, mit der es im Regal auffindbar ist.
Jedes Buch hat mindestens eine Autorin / einen Autor, oft aber auch mehrere. Von Autoren und Autorinnen sollen zumindest Name und optional ein Geburtsjahr gespeichert werden.
Es gibt Mitglieder der Bibliothek (z. B. Schüler und Schülerinnen, Lehrer und Lehrerinnen). Für jedes Mitglied werden u. a. Name, eine Mitgliedsnummer und eine Kontaktmöglichkeit (E-Mail) gespeichert.
Wenn ein Mitglied ein Exemplar ausleiht, entsteht eine Ausleihe.
- Zu jeder Ausleihe gehören das ausgeliehene Exemplar, das ausleihende Mitglied,
- ein Ausleihdatum und ein Rückgabedatum (das bei offenen Ausleihen noch leer sein kann),
- optional kann vermerkt werden, ob eine Mahngebühr angefallen ist.
Ein Exemplar kann im Laufe der Zeit mehrmals ausgeliehen werden, aber zu einem Zeitpunkt höchstens von einem Mitglied aktiv entlehnt sein.

Hinweis zur Modellkomplexität: Ihr Modell soll überschaubar bleiben (ca. 5-6 Entitäten). Die folgenden Entitäten sollen auf jeden Fall vorkommen:

Buch
Exemplar
Autor
Mitglied
Ausleihe

Eine zusätzliche Entität (z. B. Kategorie/Genre oder Verlag) ist optional möglich, aber nicht zwingend erforderlich.

Arbeitsaufträge

Faktenbasis strukturieren
- Lesen Sie die obige Beschreibung sorgfältig durch.
- Extrahieren Sie in Stichworten eine Faktenliste (z. B. “Ein Buch hat Titel, Erscheinungsjahr, ISBN”, “Ein Buch hat mindestens eine Autorin / einen Autor”, “Ein Exemplar gehört genau zu einem Buch”, …).
- Markieren Sie, welche Fakten Entitäten, welche Attribute und welche Beziehungen betreffen.
Entitäten und Attribute identifizieren
- Legen Sie die Entitäten fest (mindestens: Buch, Exemplar, Autor\*in, Mitglied, Ausleihe).
- Listen Sie für jede Entität geeignete Attribute auf (z. B. Buch.titel, Buch.erscheinungsjahr, Mitglied.mitgliedsnummer, …).
- Bestimmen Sie bereits grob, welche Attribute sich als Primärschlüssel eignen (z. B. künstliche IDs vs. natürliche Schlüssel wie ISBN).
Beziehungen modellieren (ER-Diagramm)
- Modellieren Sie die Beziehungen in einem ER-Diagramm (IE-/Martin-/Krähenfuß-Notation), z. B.:
  - Buch - Autor
  - Buch - Exemplar
  - Mitglied - Ausleihe
  - Exemplar - Ausleihe
- Geben Sie für jede Beziehung die Kardinalitäten (z. B. 1, 0..1, 0..n, 1..n) an.
Relationales Schema (Tabellenentwurf) ableiten
- Leiten Sie aus Ihrem ER-Modell ein relationales Schema in Textform ab.
- Für jede Entität bzw. für jede n:m-Beziehung definieren Sie eine Tabelle mit:
  - Tabellennamen,
  - Attributliste (Spaltenname),
  - einem vorgeschlagenen PostgreSQL-Datentyp (z. B. INTEGER, BIGINT, VARCHAR(100), DATE, TIMESTAMP, BOOLEAN),
  - Primärschlüssel (PRIMARY KEY),
  - Fremdschlüssel (FOREIGN KEY) zu anderen Tabellen.
PostgreSQL-spezifische Details ergänzen
- Entscheiden Sie, ob Sie für Primärschlüssel lieber
  - SERIAL / BIGSERIAL oder
  - GENERATED ALWAYS AS IDENTITY verwenden möchten.
- Überlegen Sie, welche Attribute NOT NULL sein sollen (z. B. titel eines Buches, name eines Mitglieds).
- Legen Sie sinnvolle Eindeutigkeiten (UNIQUE) fest (z. B. isbn pro Buch, mitgliedsnummer, signatur eines Exemplars).
SQL Anweisungen
- Erstellen Sie eine Datenbank bibliothek für PostgreSQL.
- Formulieren Sie Ihr Schema als echte CREATE TABLE-Anweisungen für PostgreSQL und erstellen Sie alle Ihre Tabellen.

Abgabe

Draw.io-Datei (oder vergleichbares Format) mit dem ER-Modell (IE-/Krähenfuß-Notation) für das Bibliotheksmodell,
Word-Datei (oder PDF) mit:
- der stichwortartigen Faktenbasis (Ihre eigene Liste),
- der Beschreibung der Tabellen (relationales Schema) in Textform inkl. Primär- und Fremdschlüsseln,
- einer Auflistung der wichtigsten Beziehungen (z. B. “1:n zwischen Buch und Exemplar”, “n:m zwischen Buch und Autor*in über Zwischentabelle BUCH_AUTOR”),
- einer kurzen Beschreibung Ihrer Annahmen und Designentscheidungen (z. B. warum Sie bestimmte Attribute als NOT NULL oder UNIQUE definiert haben).
eine SQL-Datei mit Ihren CREATE TABLE-Anweisungen für PostgreSQL.

Wissens-Check

Frage 1: Wie wird eine n:m-Beziehung (z. B. Buch ↔ Autor*in) im relationalen Schema umgesetzt?
Frage 2: Worin unterscheiden sich Buch und Exemplar, und warum braucht man beide Tabellen?
Frage 3: Wann verwenden Sie einen künstlichen Schlüssel (z. B. SERIAL-ID) statt eines natürlichen Schlüssels wie der ISBN?

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