# Schulbibliothek — Musterlösung zum Projekt «SQL & Datenbanken»

Projekt der Klasse 3 · Frühjahrssemester 2026
*Freies Gymnasium Zürich — Fachschaft Informatik*

Diese Musterlösung modelliert die Bücherausleihe einer Schulbibliothek
als relationale Datenbank und beantwortet vier praxisnahe Fragen mit
SQL-Abfragen. Sie demonstriert sämtliche Bewertungskriterien des
Projektrasters (siehe `Skript/projekt.pdf`).

---

## 1 Szenario

In der FGZ-Bibliothek leihen Schüler:innen Bücher aus. Jeder Schüler
gehört zu einer Klasse, Bücher haben ein oder mehrere Genres, und für
jede Ausleihe wird festgehalten, wann das Buch geholt und wann es
zurückgegeben wurde. Die Bibliothek möchte schnell Antworten auf
Fragen wie *«Welche Bücher hat Lara bisher gelesen?»* oder
*«Welches Genre ist am beliebtesten?»* bekommen — die Datenbank
liefert sie per Knopfdruck.

## 2 Use Cases

Die folgenden vier Fragen sind in `queries.sql` als ausführbare
SELECT-Anfragen umgesetzt:

1. **Welche Bücher hat Sara bisher ausgeliehen?**
   Doppel-JOIN über `Schueler → Ausleihe → Buch`, Filter auf Name.
2. **Welche Bücher sind aktuell verliehen?**
   Verwendet den `AktiveAusleihen`-VIEW (rueckgabe_datum IS NULL).
3. **Welche Genres werden besonders oft ausgeliehen?**
   GROUP BY + HAVING über die n:m-Beziehung Buch ↔ Genre.
4. **Wer sind die drei aktivsten Leser:innen?**
   Aggregation pro Schüler:in, LEFT JOIN + LIMIT 3.

Zusätzlich enthält `queries.sql` eine Bonus-Anfrage mit `UNION`, die
Buchtitel und Genre-Namen in einer einzigen Liste zusammenfasst.

## 3 Architektur-Entscheidungen

- **5 Entitäten + 1 reine Verbindungstabelle.** `Klasse`, `Schueler`,
  `Buch`, `Genre`, `Ausleihe` plus die n:m-Tabelle `Buch_Genre`.
- **Zwei n:m-Beziehungen.** Buch ↔ Genre als reine Verbindungstabelle;
  Schueler ↔ Buch als Ausleihe mit eigenen Attributen
  (`ausleih_datum`, `rueckgabe_datum`).
- **`Ausleihe` mit künstlichem PK `id`.** Eine zusammengesetzte
  `(schueler_id, isbn, ausleih_datum)` wäre alternativ möglich, aber
  ein Surrogatschlüssel ist robuster — derselbe Schüler kann theoretisch
  dasselbe Buch am gleichen Tag mehrfach ausleihen, ohne dass die Tabelle
  bricht.
- **Constraints am richtigen Ort.** `CHECK (rueckgabe_datum >=
  ausleih_datum)` verhindert physikalisch unmögliche Daten;
  `CHECK (exemplare >= 0)` schützt vor negativen Beständen;
  `UNIQUE` auf `Klasse.bezeichnung` und `Genre.name` schliesst
  Duplikate aus.
- **`ON DELETE`-Verhalten bewusst gewählt.** Wird ein Schüler entfernt,
  werden seine `Ausleihe`-Einträge mitgelöscht (CASCADE).
  Wird eine Klasse aufgelöst, behalten die Schüler:innen ihren Eintrag,
  ihr `klasse_id` wird aber auf `NULL` gesetzt (SET NULL).
- **Performance.** Zwei Indizes auf `Ausleihe(schueler_id)` und
  `Ausleihe(isbn)` — alle Use-Case-Queries filtern auf diesen Spalten.
- **VIEW als API.** `AktiveAusleihen` kapselt die häufig benötigte
  Filter+JOIN-Kombination als wiederverwendbare Sicht.

## 4 Reflexion

**Was lief gut.**
Die Datenmodellierung von Hand auf Papier (Substantive → Entitäten,
Verben → Beziehungen) hat geholfen, früh die richtige Granularität zu
finden. Die n:m-Beziehung *Buch ↔ Genre* war auf den ersten Blick nicht
offensichtlich — erst beim Durchspielen mit konkreten Beispielen
(«Krabat ist Fantasy *und* Jugendbuch») fiel auf, dass eine reine 1:n
nicht reicht.

**Was war schwierig.**
Der `ON DELETE`-Entscheid für `Schueler.klasse_id` war heikel:
`CASCADE` hätte beim Auflösen einer Klasse alle Schüler:innen mitgelöscht,
was offensichtlich falsch ist. `SET NULL` mit anschliessender Neuzuteilung
ist semantisch korrekt, kostet aber im Schema die NOT-NULL-Garantie auf
`klasse_id`. Die Wahl ist in Kommentaren begründet.

**Was wir nächstes Mal anders machen würden.**
Das Datumsformat als `DATE` ist ausreichend, aber für eine echte
Bibliothek würden Zeitstempel (`DATETIME`) sinnvoller sein, um auch
mehrere Vorgänge am selben Tag voneinander zu unterscheiden. Ebenso
fehlt noch ein Mahnungs- oder Reservierungssystem — beides liesse sich
als zusätzliche Tabelle nahtlos integrieren.

## 5 Dateien

| Datei            | Inhalt                                              |
|------------------|-----------------------------------------------------|
| `schema.sql`     | CREATE-TABLE-Statements, Indizes, VIEW              |
| `testdaten.sql`  | INSERTs für 3 Klassen, 8 Schüler:innen, 10 Bücher, 6 Genres, 15 Ausleihen |
| `queries.sql`    | 4 Use-Case-Anfragen + 1 UNION-Bonus                 |
| `erm.pdf`        | Entity-Relationship-Diagramm                        |
| `README.md`      | dieses Dokument                                     |

## 6 Ausführen

```bash
# In SQLite (Reihenfolge ist wichtig!)
sqlite3 bibliothek.db < schema.sql
sqlite3 bibliothek.db < testdaten.sql
sqlite3 bibliothek.db < queries.sql

# Oder in der Lernplattform: «Projektraum → Musterlösung laden»
```