Sportfest-Datenbank: eine Tabelle, drei Anomalien
Für das Schulsportfest pflegt eine Lehrkraft alle Ergebnisse in einer einzigen Tabelle ergebnis mit den Spalten startnr, schueler_name, klasse, wettbewerb, punktzahl, kampfrichter, kampfrichter_raum; derzeit 640 Zeilen. Beim Umbenennen der Kampfrichterin „Meier“ in „Meier-Lopez“ müssen 37 Zeilen geändert werden, eine wird übersehen. Ein Schüler wechselt von der 10a in die 10b, woraufhin beide Klassenbezeichnungen für dieselbe Person in der Tabelle stehen. Ein neuer Wettbewerb, für den noch niemand gemeldet ist, lässt sich nicht eintragen, weil startnr Primärschlüssel ist. Die Auswertung nach Klasse liefert außerdem 13 Gruppen statt 12, weil die Bezeichnung einmal „10 a“ und einmal „10a“ geschrieben wurde. Bei einer Disqualifikation wurde punktzahl auf NULL gesetzt; die Abfrage „Anteil der gewerteten Starter“ liefert dadurch einen falschen Prozentwert, und niemand im Kollegium kann erklären, warum.
1. Geben Sie die funktionalen Abhängigkeiten an und bestimmen Sie, welche Normalform verletzt ist. Begründen Sie ausdrücklich, warum die 2. Normalform hier nicht der richtige Vorwurf ist.
Read the worked answer
Unter der Annahme, dass Schülernamen an der Schule eindeutig sind, gilt:
startnr → schueler_name, klasse, wettbewerb, punktzahl, kampfrichterschueler_name → klassekampfrichter → kampfrichter_raum
2. Normalform: Sie verlangt, dass jedes Nichtschlüsselattribut voll vom gesamten Schlüssel abhängt. Der Schlüssel ist hier das einzelne Attribut startnr; ein echtes Teilabhängigkeitsproblem kann bei einem einelementigen Schlüssel gar nicht entstehen. Wer „2. NF“ schreibt, hat die Definition nicht auf den konkreten Schlüssel angewendet.
Verletzt ist die 3. Normalform. klasse hängt nicht direkt von startnr ab, sondern transitiv über schueler_name; ebenso hängt kampfrichter_raum transitiv über kampfrichter vom Schlüssel ab. Beides sind Nichtschlüsselattribute, die von anderen Nichtschlüsselattributen bestimmt werden – genau das schließt die 3. NF aus.
Die Einfüge-Anomalie beim Wettbewerb ohne Starter ist streng genommen keine Normalformverletzung, sondern eine Folge der Schlüsselwahl: Ein Wettbewerb ist kein eigenständiges Objekt, weil er nur als Attribut einer Ergebniszeile existiert. Das ist ein Modellierungsfehler, der durch die Zerlegung mitbehoben wird.
2. Entwerfen Sie ein Schema in der 3. Normalform mit Primär- und Fremdschlüsseln. Erläutern Sie, wie Ihr Entwurf die drei beschriebenen Anomalien beseitigt und warum Sie „10 a“ und „10a“ damit in den Griff bekommen.
Read the worked answer
Vorschlag (surrogate Schlüssel, damit Namensänderungen keine Schlüssel berühren):
klasse(klasse_id PK, bezeichnung UNIQUE NOT NULL)schueler(schueler_id PK, name NOT NULL, klasse_id FK → klasse)wettbewerb(wettbewerb_id PK, bezeichnung NOT NULL)kampfrichter(kampfrichter_id PK, name NOT NULL, raum)ergebnis(startnr PK, schueler_id FK, wettbewerb_id FK, kampfrichter_id FK, punktzahl NULL)
Änderungsanomalie: „Meier-Lopez“ steht genau einmal in kampfrichter; die 37 Ergebniszeilen verweisen per ID und bleiben unberührt. Ein vergessener Datensatz kann nicht mehr entstehen.
Klassenwechsel: Die Klasse hängt an schueler, nicht an jeder Ergebniszeile. Ein Wechsel ist ein UPDATE auf einer Zeile; historische Ergebnisse bleiben zuordenbar, wenn man bei Bedarf zusätzlich klasse_id in ergebnis als „Klasse zum Wettkampfzeitpunkt“ führt – das ist eine bewusste Entscheidung, keine Redundanz aus Versehen.
Einfüge-Anomalie: Ein Wettbewerb ist jetzt eine eigene Zeile in wettbewerb und existiert unabhängig von Meldungen.
Schreibweisen: bezeichnung ist UNIQUE und wird nur zentral gepflegt; Auswertungen gruppieren über klasse_id statt über Freitext. Zusätzlich sollte beim Import normalisiert werden (trim, einheitliches Leerzeichenmuster), sonst legt der Import weiterhin Dubletten an – die Datenbank kann nur prüfen, was sie als gleich erkennt.
3. Formulieren Sie eine SQL-Abfrage, die je Klasse die Anzahl der gewerteten Starter und den Punkteschnitt ausgibt, aber nur Klassen mit mindestens drei gewerteten Startern berücksichtigt. Erklären Sie die Behandlung von NULL und die Rolle von `COUNT(*)` gegenüber `COUNT(punktzahl)`.
Read the worked answer
SELECT k.bezeichnung,
COUNT(e.punktzahl) AS gewertet,
AVG(e.punktzahl) AS schnitt
FROM ergebnis e
JOIN schueler s ON s.schueler_id = e.schueler_id
JOIN klasse k ON k.klasse_id = s.klasse_id
GROUP BY k.klasse_id, k.bezeichnung
HAVING COUNT(e.punktzahl) >= 3
ORDER BY schnitt DESC;
NULL-Semantik: NULL bedeutet „unbekannt/nicht zutreffend“, nicht 0. AVG ignoriert NULL-Zeilen und teilt durch die Anzahl der gewerteten Zeilen – das ist hier gewollt, denn eine Disqualifikation soll den Schnitt nicht nach unten ziehen. COUNT(*) zählt Zeilen, COUNT(e.punktzahl) zählt nur Zeilen mit Wert; für „Anteil gewertet“ braucht man daher COUNT(e.punktzahl) * 1.0 / COUNT(*), wobei der Faktor 1.0 die Ganzzahldivision verhindert, die in manchen DBMS sonst 0 liefert.
Warum HAVING und nicht WHERE: WHERE filtert vor der Gruppierung und kennt die Aggregate nicht; die Bedingung „mindestens drei gewertete“ bezieht sich auf die Gruppe und gehört deshalb in HAVING.
Gruppierung über klasse_id: Über die ID werden „10 a“ und „10a“ zusammengeführt, sobald der Datenbestand bereinigt ist. Über den Freitext würde die Auswertung weiterhin 13 Gruppen liefern.