Aufgabe 21 - Normalformen

Worum geht es?

In dieser Übung beseitigen Sie Redundanzen und Anomalien (wie Sie sie in Aufgabe 20 kennengelernt haben) systematisch, indem Sie eine Tabelle Schritt für Schritt bis zur Dritten Normalform normalisieren (siehe Kapitel 12 - Normalisierung).

In dieser Übung üben Sie:

Prüfen & Korrigieren: Verletzungen der Ersten Normalform (1NF) erkennen und beheben.
Erkennen: partielle Abhängigkeiten bestimmen und mit der Zweiten Normalform (2NF) beseitigen.
Auflösen: transitive Abhängigkeiten finden und mit der Dritten Normalform (3NF) entfernen.
Entwerfen: ein vollständig normalisiertes Schema (bis 3NF) selbstständig erstellen und implementieren.

Szenario: Schulkantine

Die Schulkantine erfasst alle Bestellungen in einer einzigen Tabelle. Eine Zeile steht dafür, dass ein Schüler an einem bestimmten Tag ein bestimmtes Gericht bestellt hat. Der Primärschlüssel ist die Kombination aus (schueler_id, gericht_id, bestelldatum).

CREATE TABLE kantine_bestellungen (
    schueler_id    INT,
    schueler_name  TEXT,
    klasse         TEXT,
    klassenraum    TEXT,
    gericht_id     INT,
    gericht_name   TEXT,
    allergen_liste TEXT,
    kategorie      TEXT,
    preis          NUMERIC(5, 2),
    anzahl         INT,
    bestelldatum   DATE
);

INSERT INTO kantine_bestellungen VALUES
(1, 'Anna Gruber',  '3AHITM', 'R204', 10, 'Schnitzel',    'Gluten, Ei',        'fleisch',      5.90, 1, '2026-03-10'),
(1, 'Anna Gruber',  '3AHITM', 'R204', 12, 'Gemüsepfanne', 'Sellerie',          'vegetarisch',  4.50, 1, '2026-03-11'),
(2, 'Ben Hofer',    '3AHITM', 'R204', 10, 'Schnitzel',    'Gluten, Ei',        'fleisch',      5.90, 2, '2026-03-10'),
(3, 'Cara Wimmer',  '4AHITM', 'R110', 11, 'Nudelauflauf', 'Gluten, Milch, Ei', 'vegetarisch',  4.80, 1, '2026-03-10'),
(3, 'Cara Wimmer',  '4AHITM', 'R110', 12, 'Gemüsepfanne', 'Sellerie',          'vegetarisch',  4.50, 2, '2026-03-11');

Arbeitsaufträge

Aufgabe 1: Normalform-Verstöße erkennen

Geben Sie für jede der folgenden Aussagen an, welche Normalform (1NF, 2NF oder 3NF) verletzt wird — oder schreiben Sie “kein Verstoß”, wenn die Spalte korrekt platziert ist.

#	Beobachtung	Verletzt welche NF?
a	`allergen_liste` enthält Werte wie `'Gluten, Milch, Ei'` — mehrere Allergene in einer Zelle.	?
b	`schueler_name` hängt nur von `schueler_id` ab, nicht vom gesamten Primärschlüssel.	?
c	`klassenraum` hängt von `klasse` ab, `klasse` hängt von `schueler_id` ab.	?
d	`anzahl` (wie viele Portionen an diesem Tag bestellt wurden) hängt von allen drei Teilen des Primärschlüssels ab.	?
e	`preis` hängt nur von `gericht_id` ab.	?

Aufgabe 2: Erste Normalform (1NF) herstellen

Die Spalte allergen_liste verletzt die 1NF, weil sie mehrere Werte in einer Zelle speichert.

a) Erstellen Sie eine neue Tabelle gericht_allergene, die jeden Inhaltsstoff als eigene Zeile speichert. Schreiben Sie das CREATE TABLE-Statement (PostgreSQL oder MariaDB/MySQL).

b) Fügen Sie die Allergen-Daten aus den fünf Beispielzeilen oben als INSERT-Anweisungen ein (jeden Inhaltsstoff als eigene Zeile).

c) Schreiben Sie eine SELECT-Abfrage, die alle Allergene des Gerichts mit gericht_id = 11 ausgibt.

Aufgabe 3: Partielle Abhängigkeiten bestimmen (Vorbereitung 2NF)

a) Der Primärschlüssel von kantine_bestellungen besteht aus mehreren Spalten. Schreiben Sie ihn auf.

b) Füllen Sie die folgende Tabelle aus. Schreiben Sie für jede Nicht-Schlüsselspalte auf, von welchem Teil des Primärschlüssels sie wirklich abhängt:

Spalte	Hängt ab von …
`schueler_name`	?
`klasse`	?
`klassenraum`	?
`gericht_name`	?
`kategorie`	?
`preis`	?
`anzahl`	?

c) Welche dieser Abhängigkeiten sind partielle Abhängigkeiten (= verletzen 2NF)? Listen Sie sie auf.

Aufgabe 4: Zweite Normalform (2NF) herstellen

Teilen Sie die Tabelle kantine_bestellungen so auf, dass keine partiellen Abhängigkeiten mehr vorhanden sind.

Schreiben Sie für jede neue Tabelle ein vollständiges CREATE TABLE-Statement inklusive Primärschlüssel und Fremdschlüsseln.

Achten Sie darauf, dass die Spalte anzahl in der richtigen Tabelle landet.

Hinweis (nur öffnen, wenn Sie wirklich nicht weiterkommen)

Sie brauchen mindestens drei Tabellen: eine für Schüler-Daten, eine für Gerichts-Daten und eine für die Bestellung selbst.

Aufgabe 5: Dritte Normalform (3NF) herstellen

Schauen Sie sich die Tabelle an, die Sie in Aufgabe 4 für die Schülerdaten erstellt haben.

a) Welche transitive Abhängigkeit steckt noch darin? Schreiben Sie sie in der Form A → B → C auf.

b) Erklären Sie mit einem konkreten Beispiel, welches Problem diese Abhängigkeit in der Praxis verursacht.
(Tipp: Was passiert, wenn Klasse 3AHITM in einen anderen Raum umzieht und 30 Schüler in der Tabelle stehen?)

c) Erstellen Sie die zusätzliche Tabelle (oder Tabellen), um diese Abhängigkeit aufzulösen. Schreiben Sie auch die angepasste Schüler-Tabelle neu.

Aufgabe 6: Vollständiges normalisiertes Schema

Sie haben jetzt alle Schritte einzeln durchgeführt. Jetzt kommt alles zusammen.

a) Schema aufbauen: Schreiben Sie alle CREATE TABLE-Statements in der richtigen Reihenfolge — so, dass Fremdschlüssel beim Erstellen bereits auf vorhandene Tabellen zeigen können. Vergeben Sie aussagekräftige Constraint-Namen (pk_, fk_, chk_).

b) Daten einfügen: Fügen Sie die ursprünglichen fünf Beispielzeilen korrekt verteilt auf die neuen Tabellen ein. Jedes Gericht und jeder Schüler darf in seiner eigenen Tabelle nur einmal vorkommen.

c) Ursprüngliche Sicht rekonstruieren: Schreiben Sie eine SELECT-Abfrage mit den nötigen JOINs, die exakt diese Spalten liefert:
schueler_name, klasse, klassenraum, gericht_name, kategorie, preis, anzahl, bestelldatum

d) Analyse: In der ursprünglichen Tabelle kostet das Ändern des Preises für “Schnitzel” mehrere UPDATE-Zeilen. In Ihrem normalisierten Schema: wie viele Zeilen muss man ändern? Erklären Sie den Unterschied.

e) Bonus: Schreiben Sie eine Abfrage, die pro Klasse den Gesamtbetrag aller Bestellungen (preis * anzahl) ausgibt, sortiert nach dem höchsten Betrag zuerst.

Abgabe & Kriterien

Abgabe der Datei aufgabe21_normalformen.sql mit allen CREATE TABLE-, INSERT- und SELECT-Anweisungen des normalisierten Schemas.
Kurze schriftliche Antworten zu den Analysefragen (Aufgaben 1, 3 und 6).
Alle Befehle müssen auf einer PostgreSQL-Datenbank fehlerfrei ausführbar sein.

Wissens-Check

Frage 1: Warum ist die Zweite Normalform nur relevant, wenn der Primärschlüssel aus mehreren Spalten besteht? Was passiert bei einem einspaltigen Primärschlüssel?
Frage 2: Beschreiben Sie mit einem Satz den Unterschied zwischen einer partiellen Abhängigkeit (2NF-Problem) und einer transitiven Abhängigkeit (3NF-Problem).
Frage 3: Ein Kollege meint: “Die allergen_liste als Text zu lassen macht Abfragen einfacher — ein LIKE '%Gluten%' findet alles.” Hat er einen Punkt? Wann wäre er trotzdem falsch?

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