Übungsaufgaben zu Normalisierung und Anomalien

Bewertungsvorschlag:

• zusammengesetzte Werte in Attribute zerlegt: Hobby/Kategorie, Treffpunkt/PLZ/Ort, Nachname/Vorname, je 1 Punkt → 3 Punkte

• Listen in Zeilen aufgelöst, eine Zeile je Teilnahme, Nummer, Name und Sterne positionsrichtig zugeordnet → 3 Punkte

• übrige Attributwerte vollständig übernommen (leere Bewertung bleibt leer bzw. NULL) → 2 Punkte

• Primärschlüssel (AktNr, BenNr) → 2 Punkte

Hinweise: Ein Zeitpunkt aus Datum und Uhrzeit ist ein einzelner Wert (Datentyp DATETIME) und darf zusammenbleiben; eine Aufteilung in Datum und Uhrzeit ist ebenfalls richtig. Das Datum muss nicht in den Schlüssel, weil jede Aktivität nur einmal stattfindet – bei wiederholten Terminen (z. B. „jeden Sonntag“) gehörte der Zeitpunkt in den Schlüssel. Die Abweichung „Novak“/„Nowak“ wird in der 1NF unverändert übernommen (siehe bb).

Der Primärschlüssel aus a) ist zusammengesetzt. Titel, Beginn, Hobby, Kategorie und alle Treffpunktangaben hängen nur vom Teilschlüssel AktNr ab, Nachname und Vorname nur von BenNr. Nur Sterne hängt vom ganzen Schlüssel ab (eine Bewertung je Person und Aktivität) und bleibt in der Ausgangstabelle teilnahme.

Bewertungsvorschlag:

• je Tabelle mit richtigen Attributen 1 Punkt → 3 Punkte

• Primärschlüssel aktivitaet und benutzer je 1 Punkt, zusammengesetzter Primärschlüssel mit Fremdschlüsseln in teilnahme 1 Punkt → 3 Punkte

• je Beziehung mit Kardinalität 1 Punkt → 2 Punkte

• Ursache (2 Punkte): Der Name jeder teilnehmenden Person ist in der Altliste bei jeder Teilnahme erneut gespeichert (Redundanz). Bei der Erfassung oder einer späteren Korrektur wurde er nicht überall gleich geschrieben bzw. nur in einer Zeile geändert. Die Daten sind dadurch inkonsistent: Für dieselbe Person 6 stehen „Novak“ und „Nowak“ in der Liste – eine Folge der Änderungsanomalie.

• Umgang (1 Punkt): Welche Schreibweise stimmt, lässt sich aus der Liste allein nicht entscheiden. Abgleich mit dem Benutzerkonto (dort „Novak“) bzw. Rückfrage bei der Person; in der Tabelle benutzer der 2NF steht der Name danach nur noch einmal.

In aktivitaet gibt es zwei transitive Abhängigkeiten: Die Kategorie folgt aus dem Hobby (Hobby → Kategorie), Name, PLZ und Ort des Treffpunkts folgen aus der Treffpunktnummer (TreffNr → Treffpunkt, PLZ, Ort). Beide Gruppen werden in eigene Tabellen ausgelagert; Hobby und TreffNr bleiben als Fremdschlüssel in aktivitaet. teilnahme und benutzer sind bereits in der 3NF.

Bewertungsvorschlag:

• Tabellen hobby und treffpunkt mit Attributen je 1 Punkt → 2 Punkte

• deren Primärschlüssel je 0,5 Punkte → 1 Punkt

• Fremdschlüssel Hobby und TreffNr in aktivitaet je 0,5 Punkte → 1 Punkt

• neue Beziehungen mit Kardinalitäten je 1 Punkt → 2 Punkte

Alternativen: Statt der Bezeichnung kann ein künstlicher Schlüssel HobbyNr (bzw. hobby_id wie in der neuen Datenbank) vergeben werden. Wer zusätzlich eine Tabelle ort (PLZ (PK), Ort) auslagert, löst auch die transitive Abhängigkeit PLZ → Ort auf – das ist ebenfalls richtig.

Je beschriebenem Problem 2 Punkte, z. B.:

• Redundanz und Änderungsanomalie: Bezeichnung und Kategorie stehen in jeder Aktivität erneut (bei rund 3.100 neuen Aktivitäten im Monat sehr oft). Wird ein Hobby umbenannt (z. B. „Brettspiele“ in „Brett- und Kartenspiele“) oder einer anderen Kategorie zugeordnet, müssen alle betroffenen Aktivitäten geändert werden; wird eine vergessen, sind die Daten inkonsistent.

• Inkonsistente Schreibweisen: Ohne Wertetabelle können Hobbys frei eingetippt werden („Fussball“, „Fußball“, „fußball“). Filter und das Matching nach Interessen finden dann passende Aktivitäten nicht mehr.

• Einfüge- und Löschanomalie: Ein neues Hobby existiert erst, wenn es eine Aktivität dazu gibt; wird die letzte Aktivität eines Hobbys gelöscht, verschwindet das Hobby samt Kategorie. Außerdem verweist benutzer_hobby auf die Hobbys der Benutzer*innen – ohne Tabelle hobby gäbe es keinen gemeinsamen Bezugspunkt mehr.

Der Zeitgewinn ist gering: Ein JOIN über den Primärschlüssel einer kleinen Tabelle ist dank Index sehr schnell.

SELECT a.aktivitaet_id, a.titel, a.beginn,
       h.bezeichnung AS hobby, h.kategorie,
       t.treffpunkt_id, t.name AS treffpunkt, t.plz, t.ort,
       b.benutzer_id, b.nachname, b.vorname,
       bw.sterne
FROM aktivitaet a
  JOIN hobby h       ON h.hobby_id = a.hobby_id
  JOIN treffpunkt t  ON t.treffpunkt_id = a.treffpunkt_id
  JOIN teilnahme tn  ON tn.aktivitaet_id = a.aktivitaet_id
  JOIN benutzer b    ON b.benutzer_id = tn.benutzer_id
  LEFT JOIN bewertung bw ON bw.aktivitaet_id = tn.aktivitaet_id
                        AND bw.bewerter_id   = tn.benutzer_id
WHERE a.aktivitaet_id IN (2, 8)
  AND tn.status = 'teilgenommen'
ORDER BY a.aktivitaet_id, b.benutzer_id;

Ergebnis im Datenbestand (gekürzt um Titel und Beginn):

Bewertungsvorschlag:

• Verbund von aktivitaet, hobby, treffpunkt, teilnahme und benutzer mit richtigen Bedingungen → 2 Punkte

• LEFT JOIN auf bewertung mit beiden Bedingungen (Aktivität und bewertende Person) → 2 Punkte

• Filter auf die Aktivitäten 2 und 8 und den Status teilgenommen → 1 Punkt

• Spaltenauswahl und Sortierung → 1 Punkt

Typische Fehler: Mit einem inneren JOIN auf bewertung fehlen Sabine Hoffmann und Jörg Albrecht (nur 4 statt 6 Zeilen). Fehlt die Bedingung bw.bewerter_id = tn.benutzer_id, wird jede Teilnahme mit allen Bewertungen der Aktivität kombiniert. Ohne den Statusfilter erschiene bei Aktivität 2 auch Tobias Lange, der sich abgemeldet hatte.

SQL-Anweisung (2 Punkte):

UPDATE treffpunkt
SET name = 'Wanderparkplatz Hünenburg (Fernsehturm)'
WHERE treffpunkt_id = 2;

MariaDB meldet 1 row affected.

Erläuterung (2 Punkte): Der Name des Treffpunkts ist nur noch einmal in der Tabelle treffpunkt gespeichert. Aktivitäten verweisen über den Fremdschlüssel treffpunkt_id darauf und zeigen nach dem Verbund automatisch den neuen Namen. In der Altliste stand der Name dagegen in jeder Teilnahmezeile; dort hätten alle Zeilen geändert werden müssen, und eine vergessene Zeile hätte zu widersprüchlichen Daten geführt.

Redundanz bedeutet, dass dieselbe Information mehrfach gespeichert ist (1 Punkt). Beispiel (1 Punkt): Firma, Anschrift und Ansprechperson der Senne Velo Handel KG stehen in jeder ihrer drei Bestellzeilen; Datum und Lieferant einer Bestellung stehen in jeder Position (EK-26-031 zweimal); die Bezeichnung „Lichtset Nabendynamo StVZO“ steht bei jeder Bestellung dieses Artikels und außerdem schon in der Tabelle artikel.

Bewertungsvorschlag: je Anomalie 2 Punkte für Erläuterung mit konkretem Beispiel aus der Liste und 1 Punkt für die Wirkung der Normalisierung → 9 Punkte. Andere passende Beispiele sind ebenfalls richtig.

• Abweichungen (2 Punkte): Bei der Ravensberger Radteile GmbH steht die Anschrift einmal als „Mindener Straße 12“ und einmal als „Mindener Str. 12“, die Ansprechperson einmal als „Gül Yıldız“ und einmal als „Gül Yildiz“. Die Daten sind inkonsistent; welche Schreibweise gilt, ist aus der Liste nicht erkennbar.

• Folge (1 Punkt): Wird nach Lieferant und Anschrift (oder Ansprechperson) gruppiert oder werden Duplikate über den Text erkannt, erscheint derselbe Lieferant zweimal. Das Einkaufsvolumen wird geteilt (EK-26-031: 10 · 89,90 € + 20 · 49,50 € = 1.889,00 €; EK-26-033: 20 · 10,20 € + 10 · 49,50 € = 699,00 €) statt richtig 2.588,00 € für einen Lieferanten. Rabattstaffeln oder Lieferantenbewertungen werden dann falsch berechnet.

In der 3NF darf kein Nichtschlüsselattribut von einem anderen Nichtschlüsselattribut abhängen (1 Punkt). Die Tabelle enthält zwei transitive Abhängigkeiten:

• plz → ort (1 Punkt): Der Ort ergibt sich aus der Postleitzahl und hängt nur über sie vom Schlüssel lieferant_id ab.

• ansprechperson → ansprech_telefon, ansprech_email (2 Punkte): Telefonnummer und E-Mail-Adresse beschreiben die Ansprechperson, nicht den Lieferanten. Wechselt die Ansprechperson, müssen alle drei Attribute zusammen geändert werden; bekommt ein Lieferant eine zweite Ansprechperson, passt sie nicht in die Tabelle.

Die 1NF und die 2NF sind erfüllt: Die Anschrift ist bereits in Straße/Hausnummer, PLZ und Ort zerlegt, und der Primärschlüssel ist nicht zusammengesetzt.

lieferant      (lieferant_id (PK), firma, strasse_hausnr, plz (FK))
ort            (plz (PK), ort)
ansprechperson (ansprechperson_id (PK), name, telefon, email, lieferant_id (FK))

Bewertungsvorschlag: Tabelle ansprechperson mit Fremdschlüssel auf lieferant (1:n) 2 Punkte, Auslagerung von PLZ und Ort 1 Punkt. Bleiben PLZ und Ort mit Begründung in lieferant (in der Praxis üblich, weil plz → ort nicht streng gilt: Es gibt Postleitzahlen für mehrere Orte), ist das ebenfalls richtig. Den Namen weiter in Vor- und Nachname zu zerlegen, ist sinnvoll, aber nicht gefordert.

Der Vorschlag ist abzulehnen (1 Punkt). Begründung (3 Punkte), z. B.:

• Listenpreis und Bestellpreis sind zwei verschiedene Informationen: Der Listenpreis gilt heute für einen Artikel bei einem Lieferanten, der Bestellpreis galt zum Zeitpunkt der Bestellung. Da sich die Preise etwa zweimal im Jahr ändern, würde jede Preisänderung rückwirkend alle alten Bestellungen verändern.

• Rechnungsprüfung, Buchhaltung und Auswertungen (Einkaufsvolumen) brauchen den tatsächlich vereinbarten Preis; Belege müssen nachvollziehbar bleiben.

• Der Preis in der Bestellposition lässt sich nicht aus anderen Daten ableiten und ist deshalb keine Redundanz. Er kann außerdem vom Listenpreis abweichen (Sonderpreis, Mengenrabatt).

Eine Stellungnahme, die den Vorschlag nur für den Fall gelten lässt, dass Preise nie geändert werden und nie abweichen, ist mit Begründung ebenfalls vertretbar.

Erläuterungen:

• einkauf nutzt die vorhandene EK-Nr als Primärschlüssel; der Lieferant steht als Fremdschlüssel in der Bestellung, weil jede Bestellung an genau einen Lieferanten geht.

• einkaufsposition löst die m:n-Beziehung zwischen Bestellung und Artikel auf. Menge und Bestellpreis hängen vom ganzen Schlüssel (ek_nr, artikel_id) ab.

• lieferant_artikel ist die Preisliste: Lieferantenartikelnummer und Listenpreis hängen von Lieferant und Artikel ab (RR-4415 und SV-BB-220 bezeichnen denselben Bremsbelag-Satz bei verschiedenen Lieferanten).

• Bezeichnung und Shop-Artikelnummer stehen nur in artikel, Firmendaten nur in lieferant.

Bewertungsvorschlag:

• neue Tabellen mit richtigen Attributen je 1 Punkt → 4 Punkte

• Primärschlüssel je 1 Punkt → 4 Punkte, zusätzlich 0,5 Punkte, wenn die zusammengesetzten Primärschlüssel vollständig sind → 4,5 Punkte

• Fremdschlüssel (fünf) je 0,5 Punkte → 2,5 Punkte

• Beziehungen mit Kardinalitäten je 1 Punkt → 5 Punkte

Alternativen: Künstliche Schlüssel einkauf_id oder position_id statt der zusammengesetzten Schlüssel sind richtig, wenn die fachliche Eindeutigkeit (ein Artikel je Bestellung) erhalten bleibt. Wer einkaufsposition über den zusammengesetzten Fremdschlüssel (lieferant_id, artikel_id) mit lieferant_artikel verbindet, stellt zusätzlich sicher, dass nur Artikel aus der Preisliste des Lieferanten bestellt werden – das ist eine gute Erweiterung.

Je 1 P, z. B.:

• Die Daten stehen in Tabellen (Relationen) mit eindeutigem Namen (klasse, nutzer, haltepunkt).

• Jede Zeile ist ein Datensatz, jede Spalte ein Attribut mit festem Datentyp.

• Jede Tabelle hat einen Primärschlüssel, der jeden Datensatz eindeutig kennzeichnet (klasse_id …).

• Beziehungen werden über Fremdschlüssel hergestellt (nutzer.klasse_id verweist auf klasse).

• Beziehungen haben Kardinalitäten (1:n zwischen Klasse und Nutzenden).

• Funktionale Abhängigkeit (2 P): Ein Attribut B ist funktional abhängig von A (A → B), wenn zu jedem Wert von A genau ein Wert von B gehört. Beispiel (1 P): nutzer_id → email_schule oder klasse_id → bezeichnung.

• Transitive Abhängigkeit (2 P): Ein Nichtschlüsselattribut hängt nicht direkt, sondern über ein anderes Nichtschlüsselattribut vom Primärschlüssel ab: Schlüssel → A → B. Beispiel (1 P): haltepunkt_id → plz → ort (wird in der Praxis meist geduldet) oder, wenn zum Bildungsgang weitere Angaben gespeichert würden, klasse_id → bildungsgang → ausbildungsdauer.

• bildungsgang (bildungsgang_id (PK), kuerzel, bezeichnung, dauer_monate) – 2 P

• klasse (klasse_id (PK), bezeichnung, bildungsgang_id (FK), schultage, unterricht_beginn, unterricht_ende) – Text bildungsgang entfällt, Fremdschlüssel ergänzt – 1 P

• ausbildungsbetrieb (betrieb_id (PK), name, strasse, plz, ort, telefon) – 2 P

• nutzer (…, klasse_id (FK), betrieb_id (FK), …) – betrieb_id darf NULL sein (Lehrkräfte) – 1 P

• Beziehungen (2 P): bildungsgang 1 : n klasse; ausbildungsbetrieb 1 : n nutzer.

Begründung: Würden Kürzel und Dauer in klasse stehen, hingen sie über den Bildungsgang transitiv vom Schlüssel ab und stünden für jede Klasse desselben Bildungsgangs mehrfach (Änderungsanomalie). Der Betrieb ist ein eigener Sachverhalt mit eigenen Eigenschaften und wird deshalb ausgelagert. Statt bildungsgang_id ist auch kuerzel als Primärschlüssel richtig, wenn es eindeutig und unveränderlich ist.

Hinweis: Das vorhandene Attribut schultage mit Werten wie „Mo,Do“ ist eine Liste in einer Zelle und verletzt die 1NF. Sauber wäre eine Tabelle klasse_schultag (klasse_id (PK, FK), wochentag (PK)) mit einer Zeile je Klasse und Schultag (klasse 1 : n klasse_schultag). Diese Korrektur ist in der Aufgabe nicht gefordert.

Je Schritt 1 P, z. B.:

1. Zusammengesetzte Felder zerlegen: Anschrift in Straße mit Hausnummer, PLZ und Ort aufteilen (1NF).

2. Schreibweisen vereinheitlichen: „Str.“/„Straße“, „Moebelwerk“/„Möbelwerk“ (Umlaute, Zeichenkodierung UTF-8 prüfen).

3. Dubletten erkennen und zusammenführen: Die beiden Möbelwerk-Zeilen sind derselbe Betrieb; bei „Systemhaus Ostwestfalen“ und „Systemhaus OWL“ mit gleicher Anschrift klärst Du mit Thomas Becker bzw. den Auszubildenden, ob es derselbe Betrieb ist.

4. Fehlende Werte klären: Telefonnummer des zweiten Systemhaus-Eintrags fehlt – aus dem Dublettenpartner übernehmen oder NULL lassen.

5. Schlüssel vergeben und importieren: Jeder Betrieb erhält eine betrieb_id; danach wird in nutzer der passende Fremdschlüssel eingetragen und per Abfrage geprüft, dass jede Anschrift nur einmal vorkommt.

Je 1 P: Alle Attributwerte sind atomar (keine zusammengesetzten Werte wie „Name, Vorname“ oder „Anschrift“); keine Listen bzw. Wiederholungsgruppen in einem Feld (wie mehrere Telefonnummern); jeder Datensatz ist über einen Primärschlüssel eindeutig identifizierbar.

Die Normalisierung sorgt dafür, dass jede Information nur einmal gespeichert wird (Redundanz vermeiden, 1 P). Dadurch werden Einfüge-, Änderungs- und Löschanomalien verhindert (1 P), und die Daten bleiben konsistent: Ändert sich z. B. die Ausbildungsdauer eines Bildungsgangs, wird sie an genau einer Stelle angepasst. Außerdem lässt sich das Modell leichter erweitern (1 P).