Aufgabe
Ausgangssituation
Ein mittelständisches Unternehmen betreibt eine interne Fortbildungsverwaltung. Bislang werden alle Daten zu Seminaren, Dozenten und Anmeldungen in einer einzigen Tabelle Fortbildung gepflegt, die als Tabellenkalkulationsdatei geführt wird. Sie übernehmen im Rahmen Ihres Ausbildungsbetriebs die Aufgabe, diese Tabelle fachlich zu bewerten und in ein normalisiertes relationales Datenbankmodell zu überführen.
Nachfolgend ein Auszug aus der aktuellen Tabelle:
| seminar_id | seminar_titel | dozent_name | dozent_telefon | raum_nr | raum_kapazitaet | mitarbeiter_id | mitarbeiter_name | anmeldedatum |
|---|---|---|---|---|---|---|---|---|
| S100 | SQL Grundlagen | Frau Keller | 0231-55012 | R201 | 20 | M045 | Herr Brandt | 2024-03-04 |
| S100 | SQL Grundlagen | Frau Keller | 0231-55012 | R201 | 20 | M078 | Frau Nolte | 2024-03-05 |
| S101 | Projektmanagement | Herr Sorg | 0231-55099 | R105 | 15 | M045 | Herr Brandt | 2024-03-10 |
| S102 | Konfliktgespräche | Frau Keller | 0231-55012 | R201 | 20 | M112 | Frau Arslan | 2024-03-12 |
Jede Zeile entspricht einer Anmeldung eines Mitarbeiters zu einem Seminar. Ein Seminar findet stets in genau einem festen Raum statt und wird von genau einem Dozenten gehalten. Ein Dozent kann mehrere Seminare leiten, ein Mitarbeiter kann sich für mehrere Seminare anmelden.
a) Funktionale Abhängigkeiten (4 Punkte)
Betrachten Sie die Tabelle Fortbildung mit dem zusammengesetzten Primärschlüssel aus seminar_id und mitarbeiter_id.
Nennen Sie vier funktionale Abhängigkeiten, die in dieser Tabelle bestehen, und ordnen Sie jede davon ihrem Abhängigkeitstyp zu (voll funktional abhängig vom gesamten Schlüssel, partiell abhängig von einem Teil des Schlüssels oder transitiv abhängig über ein Nicht-Schlüsselattribut).
b) Normalisierung in drei Schritten (6 Punkte)
Erläutern Sie anhand der Tabelle Fortbildung die drei Schritte der Normalisierung von der 1. bis zur 3. Normalform. Gehen Sie dabei jeweils darauf ein, ob und welche konkrete Verletzung in dieser Tabelle vorliegt, die beim Übergang in die jeweils nächste Normalform behoben wird.
c) Grenzen der Normalisierung
ca) Bewusste Denormalisierung (3 Punkte)
In einem monatlichen Reporting benötigt die Personalabteilung regelmäßig eine Übersicht mit Seminartitel, Dozentenname und Anzahl der Anmeldungen je Seminar, die bei vollständig normalisiertem Schema erst über mehrere Tabellen zusammengeführt werden muss. Beschreiben Sie zwei Konsequenzen, die eine gezielte Denormalisierung (z. B. durch eine zusätzliche redundante Spalte oder Auswertungstabelle) für dieses Reporting hätte.
cb) Entscheidung zwischen zwei Varianten (3 Punkte)
Für die Raumverwaltung gibt es zwei Modellierungsvarianten: Variante 1 speichert raum_nr und raum_kapazitaet direkt in der Tabelle Seminar. Variante 2 führt eine separate Tabelle Raum ein, auf die Seminar per Fremdschlüssel verweist. Wählen Sie eine der beiden Varianten aus und begründen Sie Ihre Wahl anhand der fachlichen Anforderung, dass sich Kapazitätsangaben von Räumen künftig unabhängig von einzelnen Seminaren pflegen lassen sollen.
d) Relationales Datenbankschema (4 Punkte)
Erstellen Sie auf Basis Ihrer Normalisierung aus b) ein relationales Datenbankschema in der 3. Normalform für die Entitäten Seminar, Dozent, Raum, Mitarbeiter und Anmeldung. Kennzeichnen Sie Primärschlüssel durch Unterstreichung und Fremdschlüssel durch ein Sternchen (*).
▶Musterlösung anzeigen
Musterlösung
a) Funktionale Abhängigkeiten (4 Punkte)
seminar_id→seminar_titel,dozent_name,dozent_telefon,raum_nr,raum_kapazitaet– partiell abhängig, da nur ein Teil des zusammengesetzten Schlüssels (seminar_id) ausreicht, um diese Attribute zu bestimmen.mitarbeiter_id→mitarbeiter_name– partiell abhängig, da nurmitarbeiter_idbenötigt wird, nicht der gesamte Schlüssel.dozent_name→dozent_telefon– transitiv abhängig, da die Telefonnummer über den Umweg des Dozentennamens vom Schlüssel abhängt, nicht direkt.raum_nr→raum_kapazitaet– transitiv abhängig, da die Kapazität über den Umweg der Raumnummer vom Schlüssel abhängt.- (
seminar_id,mitarbeiter_id) →anmeldedatum– voll funktional abhängig, da erst die Kombination beider Schlüsselteile das Anmeldedatum eindeutig bestimmt.
(Bewertet werden vier der genannten Abhängigkeiten mit korrekter Typzuordnung.)
b) Normalisierung in drei Schritten (6 Punkte)
1. Normalform (2 Punkte): Die 1. NF verlangt atomare Attributwerte und das Fehlen von Wiederholungsgruppen. In der vorliegenden Tabelle sind alle Attribute bereits atomar (z. B. ist dozent_telefon ein einzelner Wert, keine Liste), sodass hier keine Verletzung vorliegt – die Tabelle erfüllt die 1. NF bereits. Problematisch ist jedoch, dass pro Anmeldung alle Seminar- und Dozentendaten wiederholt gespeichert werden (siehe die beiden Zeilen zu S100), was zu Redundanz führt und in der 2. NF behoben wird.
2. Normalform (2 Punkte): Die 2. NF verlangt, dass jedes Nicht-Schlüsselattribut voll funktional vom gesamten Primärschlüssel abhängt. Hier verletzen seminar_titel, dozent_name, dozent_telefon, raum_nr, raum_kapazitaet und mitarbeiter_name diese Regel, da sie jeweils nur von einem Teil des Schlüssels (seminar_id bzw. mitarbeiter_id) abhängen, nicht vom vollständigen Schlüssel (seminar_id, mitarbeiter_id). Diese Attribute werden in eigene Tabellen Seminar und Mitarbeiter ausgelagert, in der Tabelle Anmeldung verbleiben nur die Schlüssel und anmeldedatum.
3. Normalform (2 Punkte): Die 3. NF verlangt, dass kein Nicht-Schlüsselattribut transitiv von einem anderen Nicht-Schlüsselattribut abhängt. In der nach Schritt 2 entstandenen Tabelle Seminar hängt dozent_telefon transitiv über dozent_name vom Schlüssel seminar_id ab, ebenso raum_kapazitaet über raum_nr. Diese Attribute werden in eigene Tabellen Dozent und Raum ausgelagert, sodass in Seminar nur noch Fremdschlüssel auf diese Tabellen verbleiben.
c) Grenzen der Normalisierung
ca) Bewusste Denormalisierung (3 Punkte)
- Lesezugriffe für das Reporting werden beschleunigt, da die benötigten Werte ohne Verknüpfung mehrerer Tabellen direkt verfügbar sind – dies reduziert den Aufwand für Join-Operationen bei einer regelmäßig wiederkehrenden Auswertung.
- Gleichzeitig entsteht Redundanz: Ändert sich z. B. der Seminartitel oder der Dozentenname nachträglich, muss dieser Wert zusätzlich in der denormalisierten Auswertungsstruktur gepflegt werden, wodurch das Risiko von Dateninkonsistenzen zwischen Ursprungstabellen und Reporting-Tabelle steigt.
cb) Entscheidung zwischen zwei Varianten (3 Punkte)
Empfohlen wird Variante 2 (separate Tabelle Raum mit Fremdschlüsselbeziehung zu Seminar). Begründung: Da Raumkapazitäten künftig unabhängig von einzelnen Seminaren gepflegt werden sollen, muss eine Änderung der Kapazität eines Raums genau einmal zentral erfolgen und wirkt sich automatisch auf alle Seminare aus, die in diesem Raum stattfinden. Bei Variante 1 müsste die Kapazität bei jeder betroffenen Seminarzeile einzeln aktualisiert werden, was das Risiko von Inkonsistenzen (Update-Anomalie) birgt und der fachlichen Anforderung einer seminarunabhängigen Pflege widerspricht; zudem könnte ein Raum ohne zugeordnetes Seminar gar nicht erfasst werden (Einfüge-Anomalie).
d) Relationales Datenbankschema (4 Punkte)
Erläuterung: Da der Dozentenname als Schlüssel ungeeignet ist (Namensgleichheit möglich), wird der künstliche Primärschlüssel dozent_id eingeführt. Seminar.dozent_id und Seminar.raum_nr sind Fremdschlüssel auf Dozent und Raum. Die Tabelle Anmeldung bildet die m:n-Beziehung zwischen Seminar und Mitarbeiter ab und besitzt einen zusammengesetzten Primärschlüssel aus den beiden Fremdschlüsseln seminar_id und mitarbeiter_id.