Fahrradverleih-Datenbank: 3NF, historischer Preis und SQL-Auswertung
Die Fahrradverleih-AG der Schule verwaltet ihre Ausleihen in einer Tabelle ausleihe mit den Spalten ausleihe_id, kundennr, kundenname, kunden_email, radnr, radtyp, tagespreis, startdatum, enddatum, filiale und filialleiter. Jährlich fallen 1.200 Ausleihen an; es gibt 24 Räder in 3 Filialen. Der Tagespreis wird zweimal pro Jahr angepasst und muss für Abrechnungen zehn Jahre nachvollziehbar bleiben. Bei einem Export fällt auf, dass nach der Änderung des Namens einer Kundin alte Ausleihen den neuen Namen zeigen, während eine gelöschte Filiale in alten Datensätzen fehlt. Zudem wurden bei einer Preiserhöhung alle alten Ausleihen mit dem neuen Preis überschrieben. Die AG möchte eine normalisierte Datenbank, aber die Abrechnung soll den damals vereinbarten Preis verwenden. Eine Abfrage soll für Mai 2025 die Ausleihen pro Radtyp zählen und nur Typen mit mehr als 10 Ausleihen ausgeben.
1. Zeige anhand der Tabelle drei Anomalien und ordne sie den Normalformen zu. Erkläre insbesondere, warum der überschriebene Tagespreis ein eigenes Problem darstellt.
Read the worked answer
Die Tabelle verletzt die 3NF. kundenname und kunden_email hängen von kundennr ab, nicht von ausleihe_id; das ist eine transitive Abhängigkeit. filialleiter hängt von filiale ab, ebenfalls transitiv. radtyp und tagespreis hängen von radnr ab, wenn tagespreis der aktuelle Preis ist.
Folgen: Update-Anomalie (Namensänderung muss in vielen Zeilen erfolgen; im Export wird sie inkonsistent), Insert-Anomalie (eine neue Filiale ohne Ausleihe kann nicht eingetragen werden), Delete-Anomalie (Löschen der letzten Ausleihe einer Filiale löscht die Filialinformation). Der überschriebene Preis ist eine besondere Anomalie: Wenn tagespreis als aktueller Preis verstanden wird, gehören alte Ausleihen nicht dorthin. Die Lösung ist nicht, den aktuellen Preis zu duplizieren, sondern den vereinbarten Preis als historischen Wert in der Ausleihe zu speichern.
2. Entwirf ein Schema in der dritten Normalform. Begründe, welche Spalten bewusst dupliziert bleiben dürfen, und wie der historische Preis behandelt wird.
Read the worked answer
Kunde(kundennr PK, name, email UNIQUE) Filiale(filiale_id PK, bezeichnung, filialleiter) Radtyp(radtyp_id PK, bezeichnung, aktueller_preis) Rad(radnr PK, radtyp_id FK, filiale_id FK, anschaffungsdatum) Ausleihe(ausleihe_id PK, kundennr FK, radnr FK, startdatum, enddatum, vereinbarter_preis)
Begründung: Kunde, Filiale, Radtyp und Rad sind eigene Entitäten; Ausleihe ist die Beziehung mit historischem Preis. vereinbarter_preis ist bewusst redundant zu Radtyp.aktueller_preis, aber keine Anomalie, weil er den Vertragswert zum Ausleihzeitpunkt dokumentiert und sich nicht ändern darf. Preisänderungen betreffen nur Radtyp.aktueller_preis; alte Ausleihen bleiben korrekt. Bei Bedarf kann zusätzlich ein Rabatt oder eine Preisversion gespeichert werden. Die 3NF ist damit erreicht; die historische Spalte ist eine begründete Denormalisierung im Sinne der Abrechnung.
3. Formuliere die SQL-Abfrage für Mai 2025 und erkläre die Wahl des Datumsbereichs sowie die Behandlung des Preises für Umsatzabfragen.
Read the worked answer
SELECT rt.bezeichnung, COUNT(*) AS anzahl
FROM ausleihe a
JOIN rad r ON r.radnr = a.radnr
JOIN radtyp rt ON rt.radtyp_id = r.radtyp_id
WHERE a.startdatum >= DATE '2025-05-01'
AND a.startdatum < DATE '2025-06-01'
GROUP BY rt.bezeichnung
HAVING COUNT(*) > 10;
Erklärung: Der Monat Mai wird als halboffenes Intervall [1.5., 1.6.) gefiltert, damit der 31.5. vollständig enthalten ist und der 1.6. nicht. COUNT(*) zählt Ausleihen, nicht Tage; falls Umsatz gefragt ist, müsste SUM(vereinbarter_preis * (enddatum - startdatum + 1)) verwendet werden. HAVING filtert nach Aggregation; WHERE vor GROUP BY. Der vereinbarte Preis wird für Umsatzabfragen genutzt, nicht der aktuelle Preis, sonst wären historische Abrechnungen falsch.