Aufgabe 21 - Normalformen

Aufgabe 21 - Normalformen

Worum geht es?

In dieser Übung werden Redundanzen und Anomalien systematisch beseitigt, indem eine Tabelle Schritt für Schritt bis zur dritten Normalform normalisiert wird (siehe Kapitel 12 - Normalisierung). Im Expertenteil wird das normalisierte Schema aufgebaut, die ursprüngliche Sicht rekonstruiert und die 3NF nachgewiesen.

Was Sie dafür brauchen

• Kapitel 12 - Normalisierung, Abschnitte 1NF, 2NF, 3NF.
• Ein PostgreSQL-Server und ein SQL-Editor.

Welche Kompetenzen Sie erwerben und zeigen

• Sie erkennen und beheben Verletzungen der 1NF.
• Sie bestimmen partielle Abhängigkeiten und stellen die 2NF her.
• Sie finden transitive Abhängigkeiten und stellen die 3NF her.
• Sie bauen ein vollständig normalisiertes Schema und rekonstruieren die Ausgangssicht.

Pädagogische Einordnung

• Reproduktion: NF-Verstöße erkennen und 1NF herstellen (Teil A).
• Reorganisation und Transfer: 2NF und 3NF herstellen (Teile B und C).
• Reflexion, Problemlösung und Urteilsbildung: das Schema aufbauen, rekonstruieren und die 3NF nachweisen (Teil D).

Arbeitsaufträge

Die Übung ist auf etwa zwei Stunden ausgelegt. Teil D ist der Expertenteil.

Ausgangstabelle (Schulkantine)

Primärschlüssel: (student_id, dish_id, order_date).

CREATE TABLE canteen_orders (
    student_id INT, student_name TEXT, class TEXT, classroom TEXT,
    dish_id INT, dish_name TEXT, allergen_list TEXT, category TEXT,
    price NUMERIC(5,2), quantity INT, order_date DATE
);

INSERT INTO canteen_orders VALUES
(1,'Anna Gruber','3AHITM','R204',10,'Schnitzel','Gluten, egg','meat',5.90,1,'2026-03-10'),
(1,'Anna Gruber','3AHITM','R204',12,'Veggie pan','celery','vegetarian',4.50,1,'2026-03-11'),
(2,'Ben Hofer','3AHITM','R204',10,'Schnitzel','Gluten, egg','meat',5.90,2,'2026-03-10'),
(3,'Cara Wimmer','4AHITM','R110',11,'Pasta bake','Gluten, milk, egg','vegetarian',4.80,1,'2026-03-10'),
(3,'Cara Wimmer','4AHITM','R110',12,'Veggie pan','celery','vegetarian',4.50,2,'2026-03-11');

Teil A - Verstöße erkennen und 1NF herstellen

1. Ordnen Sie jeder Beobachtung die verletzte Normalform zu (1NF, 2NF, 3NF oder „kein Verstoß”): (a) allergen_list mit mehreren Werten in einer Zelle; (b) student_name hängt nur von student_id ab; (c) classroom hängt von class, class von student_id; (d) quantity hängt vom ganzen Schlüssel; (e) price hängt nur von dish_id.
2. Beheben Sie die 1NF-Verletzung: Legen Sie eine Tabelle dish_allergens an (ein Allergen je Zeile), füllen Sie sie aus den Beispieldaten und schreiben Sie eine SELECT-Abfrage, die alle Allergene von dish_id = 11 liefert.

Teil B - Partielle Abhängigkeiten und 2NF

1. Schreiben Sie den mehrspaltigen Primärschlüssel auf und geben Sie für jede Nicht-Schlüsselspalte an, von welchem Teil des Schlüssels sie wirklich abhängt. Listen Sie die partiellen Abhängigkeiten auf.
2. Teilen Sie die Tabelle so auf, dass keine partiellen Abhängigkeiten mehr bestehen. Schreiben Sie für jede neue Tabelle ein vollständiges CREATE TABLE mit Primär- und Fremdschlüsseln. Achten Sie darauf, dass quantity in der richtigen Tabelle landet.

Teil C - Transitive Abhängigkeit und 3NF

1. In der Schülertabelle aus Teil B steckt noch eine transitive Abhängigkeit. Schreiben Sie sie als A → B → C auf und erklären Sie an einem Beispiel das Problem (etwa: Klasse 3AHITM zieht in einen anderen Raum, obwohl 30 Schüler in der Tabelle stehen).
2. Lösen Sie die transitive Abhängigkeit mit einer zusätzlichen Tabelle auf und schreiben Sie die angepasste Schülertabelle neu.

Teil D - Expertenteil: Vollständiges Schema, Rekonstruktion, 3NF-Nachweis

1. Schema aufbauen: Schreiben Sie alle CREATE TABLE-Anweisungen in der richtigen Reihenfolge (Fremdschlüssel zeigen auf bereits vorhandene Tabellen), mit benannten Constraints (pk_, fk_, chk_).
2. Daten verteilen: Fügen Sie die fünf Beispielzeilen korrekt auf die neuen Tabellen ein, jedes Gericht und jeder Schüler genau einmal.
3. Ursprüngliche Sicht rekonstruieren: Schreiben Sie eine SELECT-Abfrage mit den nötigen Joins, die genau diese Spalten liefert: student_name, class, classroom, dish_name, category, price, quantity, order_date.
4. Wirkung zeigen: In der Ausgangstabelle braucht das Ändern des Schnitzelpreises mehrere UPDATE-Zeilen. Wie viele sind es im normalisierten Schema? Erklären Sie den Unterschied.
5. 3NF nachweisen: Prüfen Sie für jede Ihrer neuen Tabellen, dass keine partielle und keine transitive Abhängigkeit mehr besteht, und begründen Sie damit, dass jede Tabelle in der dritten Normalform ist.
6. Bonus: Eine Abfrage, die pro Klasse den Gesamtbetrag aller Bestellungen (price * quantity) ausgibt, absteigend sortiert.

Wissenscheck

1. Warum ist die 2NF nur bei einem mehrspaltigen Primärschlüssel relevant?
2. Beschreiben Sie den Unterschied zwischen partieller und transitiver Abhängigkeit in einem Satz.
3. Hat ein Kollege recht, der die allergen_list als Text lassen will, weil LIKE '%Gluten%' alles findet? Wann liegt er falsch?
4. Woran erkennt man, dass eine Tabelle in der dritten Normalform ist?
5. Wie zeigt der Rekonstruktions-Join, dass bei der Normalisierung keine Information verloren ging?

Abgabe

• Die Datei aufgabe21_normalformen.sql mit allen CREATE TABLE-, INSERT- und SELECT-Anweisungen des normalisierten Schemas.
• Kurze schriftliche Antworten zu den Analysefragen und dem 3NF-Nachweis.

---

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