Normalisierung


Beispiel: Leistungskursnoten

Eine Schule entwickelt eine eigene Schulverwaltungssoftware für die Qualifikationsphase. In der Tabelle "Leistungskurse" werden folgende Daten gespeichert:

SNr Name Semester KursNr KursBez LKuerzel LName Note
S01 Musterline Q1 DE862, EN813, PH821 Deutsch, Englisch, Physik Pz, Ku, Yb Pezzi, Kullu, Ybba 09, 06, 12
S02 Abiman Q1 PH821, EK822, MA813 Physik, Erdkunde, Mathematik Yb, St, Ad Ybba, Stone, Addi 11, 08, 10

Das Attribut SNr ist der Primärschluessel der Tabelle.

Wenn die Datenbank in dieser umfangreichen Tabelle vorliegt, können Anomalien auftreten.

Anomalie 1 (Einfüge-Anomalie): Für einen dritten SuS erfolgt ein Eintrag in die Tabelle, bei welcher ein Tippfehler passiert. Der Kurs "MA813" wird in der Kurzform "Mathe" angegeben. Damit sind zwei widersprüchliche Eintragungen in der Datenbank: "MA813, Mathematik" und "MA813, Mathe".

SNr Name Semester KursNr KursBez LKuerzel LName Note
S01 Musterline Q1 DE862, EN813, PH821 Deutsch, Englisch, Physik Pz, Ku, Yb Pezzi, Kullu, Ybba 09, 06, 12
S02 Abiman Q1 PH821, EK822, MA813 Physik, Erdkunde, Mathematik Yb, St, Ad Ybba, Stone, Addi 11, 08, 10
S03 Abifrau Q1 CH832, BI824, MA813 Chemie, Biologie, Mathe Mi, In, Ad Mischi, Insi, Addi 07, 12, 10

Anomalie 2 (Einfüge-Anomalie): Es wird ein Abiturientendatensatz angelegt, aber nach Eingabe des Namens telefoniert die Oberstufenbetreuerin und vergisst den Datensatz zu vervollständigen.

SNr Name Semester KursNr KursBez LKuerzel LName Note
S01 Musterline Q1 DE862, EN813, PH821 Deutsch, Englisch, Physik Pz, Ku, Yb Pezzi, Kullu, Ybba 09, 06, 12
S02 Abiman Q1 PH821, EK822, MA813 Physik, Erdkunde, Mathematik Yb, St, Ad Ybba, Stone, Addi 11, 08, 10
S03 Abifrau Q1

Anomalie 3 (Änderungs-Anomalie): Der Mathelehrer "Addi" geht mitten im Semester in Pension und Kollegin "Subtri" übernimmt das Fach. Leider vergisst die Oberstufenbetreuerin die Änderung bei "S02" einzutragen. Für "MA813" sind in der Datenbank jetzt verschiedene Lehrkäfte eingetragen.

SNr Name Semester KursNr KursBez LKuerzel LName Note
S01 Musterline Q1 DE862, EN813, PH821 Deutsch, Englisch, Physik Pz, Ku,Yb Pezzi, Kullu, Ybba 09, 06, 12
S02 Abiman Q1 PH821, EK822, MA813 Physik, Erdkunde, Mathematik Yb, St, Ad Ybba, Stone, Addi 11, 08, 10
S03 Abifrau Q1 CH832, BI824, MA813 Chemie, Biologie, Mathe Mi, In, Su Mischi, Insi, Subtri 07, 12, 10

Anomalie 4 (Lösch-Anomalie): Der Schüler "Abiman" wird gelöscht, da er mitten im Semester die Schule verlässt. Durch die Löschung des Datensatzes verliert man auch die Information, welche Lehrkraft den Erdkundekurs unterrichtet.

SNr Name Semester KursNr KursBez LKuerzel LName Note
S01 Musterline Q1 DE862, EN813, PH821 Deutsch, Englisch, Physik Pz, Ku, Yb Pezzi, Kullu, Ybba 09, 06, 12
S03 Abifrau Q1 CH832, BI824, MA813 Chemie, Biologie, Mathe Mi, In, Su Mischi, Insi, Subtri 07, 12, 10

Anomalien erzeugen inkonsistente Daten, also Daten, die unvollständig oder widersprüchlich sind. Die Datenbank soll jetzt so umgebaut werden, dass Anomalien möglichst vermieden werden können. Den Prozess des Umbaus einer Datenbank in eine Form, die gegenüber Anomalien unempfindlicher ist, nennt man Normalisierung der Datenbank.


1. Normalform

Beim Umbau einer Datenbank in die 1. Normalform werden Attribute, die mehr als eine Information enthalten, zerlegt. Dazu verteilt man die Datensätze mit Mehrfachattributen auf mehrere Datensätze. Es entstehen dabei Datensätze mit atomaren Attributen, bei denen ein Attribut nur eine Information enthält.

Im Datensatz zu "S01" in der Tabelle "Leistungskurse" sind jeweils alle Leistungskurse eingetragen. Die 3 Fächer sollen auf einzelne Datensätze verteilt werden, um Mehrfachattribute zu vermeiden. Der Datensatz

SNr Name Semester KursNr KursBez LKuerzel LName Note
S01 Musterline Q1 DE862, EN813, PH821 Deutsch, Englisch, Physik Pz, Ku, Yb Pezzi, Kullu, Ybba 09, 06, 12

wird umgebaut zu

SNr Name Semester KursNr KursBez LKuerzel LName Note
S01 Musterline Q1 DE862 Deutsch Pz Pezzi 09
S01 Musterline Q1 EN813 Englisch Ku Kullu 06
S01 Musterline Q1 PH821 Physik Yb Ybba 12

Das wird für alle eingetragenen SuS durchgeführt. Aus der Tabelle

SNr Name Semester KursNr KursBez LKuerzel LName Note
S01 Musterline Q1 DE862, EN813, PH821 Deutsch, Englisch, Physik Pz, Ku, Yb Pezzi, Kullu, Ybba 09, 06, 12
S02 Abiman Q1 PH821, EK822, MA813 Physik, Erdkunde, Mathematik Yb, St, Ad Ybba, Stone, Addi 11, 08, 10

wird die Tabelle

SNr Name Semester KursNr KursBez LKuerzel LName Note
S01 Musterline Q1 DE862 Deutsch Pz Pezzi 09
S01 Musterline Q1 EN813 Englisch Ku Kullu 06
S01 Musterline Q1 PH821 Physik Yb Ybba 12
S02 Abiman Q1 PH821 Physik Yb Ybba 11
S02 Abiman Q1 EK822 Erdkunde St Stone 08
S02 Abiman Q1 MA813 Mathematik Ad Addi 10

Die Tabelle sieht bereits wesentlich übersichtlicher aus, trotzdem können die Anomalien weiter auftreten. Man verliert durch den Umbau auch den Primärschlüssel SNr, da es jetzt mehrere Eintragungen mit gleicher Schülernummer gibt (Redundanzen). Wenn man sich die Tabelle genauer ansieht, sieht man, dass die Kombination von SNr und KursNr eindeutig ist.

Der Umbau der Datenbank geht weiter.


2. Normalform

Die Tabelle wird so in kleinere Tabellen zerlegt, dass Attribute, die zusammengehören, in eine eigene Tabelle geschrieben werden. Die zusammengehörenden Daten werden in weiteren Tabellen über Fremdschlüssel miteinander verbunden.

Die Schülernummer SNr und der Name gehören untrennbar zusammen. Also legt man dafür eine eigene Tabelle Schueler mit dem Primärschlüssel SNr an:

Tabelle Schueler

SNr Name
S01 Musterline
S02 Abiman

Die KursNr, KursBez, LKuerzel, LName gehören untrennbar zusammen und können in eine eigene Tabelle Kurs mit dem Primärschlüssel KursNr geschrieben werden:

Tabelle Kurs

KursNr KursBez LKuerzel LName
DE862 Deutsch Pz Pezzi
EN813 Englisch Ku Kullu
PH821 Physik Yb Ybba
EK822 Erdkunde St Stone
MA813 Mathematik Ad Addi

Die beiden Informationen Semester und Note können dann in einer neuen Tabelle Semesternote mit den Primärschlüsseln der anderen Tabellen als Fremdschlüssel zusammengesetzt werden. Die Kombination SNr, KursNr und Semester liefert dann den eindeutigen Schlüssel für die Semesternote.

Tabelle Semesternote

SNr KursNr Semester Note
S01 DE862 Q1 09
S01 EN813 Q1 06
S01 PH821 Q1 12
S02 PH821 Q1 11
S02 EK822 Q1 08
S02 MA813 Q1 10

In dieser Tabelle steht die wesentliche Information: welche Note bekommt jemand in welchem Fach in welchem Semester. Die zusätzlichen Informationen sind in andere Tabellen ausgelagert. Ändert sich der Name einer Lehrkraft oder die unterrichtende Lehrkraft eines Kurses, muss man das nur an einer einzigen Stelle ändern. Die Änderung wirkt sich dann über geeignete SQL-Abfragen auf alle Datensätze der Datenbank aus.

Die Gefahr von Anomalien wurde damit deutlich reduziert. Der Preis dafür ist, dass die SQL-Abfragen komplexer werden, um die gewünschten Daten abzufragen.


3. Normalform

Mit einer Datenbank in der 2. Normalform kann man schon gut arbeiten. Wenn Sie sich die Beispieltabellen genauer ansehen, fällt auf, dass in der Tabelle Kurs die Zuordnung LKuerzel zu LName eindeutig ist:

KursNr KursBez LKuerzel LName
DE862 Deutsch Pz Pezzi
EN813 Englisch Ku Kullu
PH821 Physik Yb Ybba
EK822 Erdkunde St Stone
MA813 Mathematik Ad Addi

Man könnte die Tabellengröße weiter reduzieren, indem man eine neue Tabelle Lehrer anlegt:

LKuerzel LName
Pz Pezzi
Ku Kullu
Yb Ybba
St Stone
Ad Addi

Damit wird LKuerzel zu einem Primärschlüssel in der Tabelle Lehrer und zum Fremdschlüssel in der Tabelle Kurs, die dann wie folgt aussieht:

KursNr KursBez LKuerzel
DE862 Deutsch Pz
EN813 Englisch Ku
PH821 Physik Yb
EK822 Erdkunde St
MA813 Mathematik Ad

Für die 3. Normalform werden alle Attribute in einer Tabelle gesucht, die untrennbar zusammengehören und damit in eine eigene Tabelle geschrieben werden können.

Die 0. Normalform einer Datenbank besteht aus einer einzigen großen Tabelle, in der alle Informationen eingetragen sind. Die 3. Normalform einer Datenbank besteht aus vielen kleinen Tabellen, in denen jeweils nur noch Informationen stehen, die untrennbar zusammengehören und die über Fremdschlüssel miteinander verbunden werden.

In der Geschichte der Datenbanken wurden noch weitere Normalisierungsformen entwickelt. In der Praxis hat sich aber gezeigt, dass die 3. Normalform für eine effiziente, anomalieresistente Datenbank sehr gut geeignet ist.


Normalisierte Datenbank

Die Datenbank

SNr Name Semester KursNr KursBez LKuerzel LName Note
S01 Musterline Q1 DE862, EN813, PH821 Deutsch, Englisch, Physik Pz, Ku, Yb Pezzi, Kullu, Ybba 09, 06, 12
S02 Abiman Q1 PH821, EK822, MA813 Physik, Erdkunde, Mathematik Yb, St, Ad Ybba, Stone, Addi 11, 08, 10

besteht umgewandelt in die 3. Normalform aus folgenden Tabellen:

Tabelle Schueler

SNr Name
S01 Musterline
S02 Abiman

Tabelle Lehrer

LKuerzel LName
Pz Pezzi
Ku Kullu
Yb Ybba
St Stone
Ad Addi

Tabelle Kurs

KursNr KursBez LKuerzel
DE862 Deutsch Pz
EN813 Englisch Ku
PH821 Physik Yb
EK822 Erdkunde St
MA813 Mathematik Ad

Tabelle Semesternote

SNr KursNr Semester Note
S01 DE862 Q1 09
S01 EN813 Q1 06
S01 PH821 Q1 12
S02 PH821 Q1 11
S02 EK822 Q1 08
S02 MA813 Q1 10

Aufgabe

A1: Analysieren Sie folgende Datenbank, die nur aus einer Tabelle besteht, auf mögliche Anomalien. Denken Sie sich dazu geeignete Beispiele für Einfüge-, Änderungs- und Lösch-Anomalien aus.

A2: Überführen Sie die Datenbank in die 3. Normalform

A3: Erstellen Sie mit dem DB Browser for SQLite die Datenbank "Mensa" in der 3. Normalform und formulieren Sie eine SQL-Abfrage, um die verfügbaren Menüs mit allen Informationen abzufragen.

Datenbank "Mensa":

MenueNr MenueName Kategorie KatPreis ZusatzNr ZusatzName
M1 Spaghetti mit Tomatensoße A 3 € 03 Farbstoff
M2 Pizza B 4 € 01, 03 Geschmacksverstärker, Farbstoff
M3 Bratwurst mit Kartoffelpüree C 5 € 01, 02 Geschmacksverstärker, Konservierungsstoff
M4 Gemüseeintopf A 3 € 00 Zusatzfrei

Lösungsvorschlag:

Anomalie 1 (Einfüge-Anomalie): Bei einer neuen Speise werden die Zusatznamen versehentlich im Plural eingegeben. Die Attribute ZusatzNr und ZusatzName haben Inkonsistenzen.

MenueNr MenueName Kategorie KatPreis ZusatzNr ZusatzName
M1 Spaghetti mit Tomatensoße A 3 € 03 Farbstoff
M2 Pizza B 4 € 01, 03 Geschmacksverstärker, Farbstoff
M3 Bratwurst mit Kartoffelpüree C 5 € 01, 02 Geschmacksverstärker, Konservierungsstoff
M4 Gemüseeintopf A 3 € 00 Zusatzfrei
M5 Wienerschnitzel mit Pommes C 5 € 02, 03 Konservierungsstoffe, Farbstoffe

Anomalie 2 (Einfüge-Anomalie): Es wird eine neue Speise ohne weitere Informationen eingegeben. Kategorie und Zusatzstoffe fehlen.

MenueNr MenueName Kategorie KatPreis ZusatzNr ZusatzName
M1 Spaghetti mit Tomatensoße A 3 € 03 Farbstoff
M2 Pizza B 4 € 01, 03 Geschmacksverstärker, Farbstoff
M3 Bratwurst mit Kartoffelpüree C 5 € 01, 02 Geschmacksverstärker, Konservierungsstoff
M4 Gemüseeintopf A 3 € 00 Zusatzfrei
M5 Wienerschnitzel mit Pommes

Anomalie 3 (Änderungs-Anomalie): Der Preis für die Spaghetti wird erhöht, die Preiserhöhung für den Gemüseeintopf, der zur gleichen Kategorie gehört, bleibt unverändert. Es gibt eine Inkonsistenz bei den Attributen Kategorie und KatPreis.

MenueNr MenueName Kategorie KatPreis ZusatzNr ZusatzName
M1 Spaghetti mit Tomatensoße A 3,50 € 03 Farbstoff
M2 Pizza B 4 € 01, 03 Geschmacksverstärker, Farbstoff
M3 Bratwurst mit Kartoffelpüree C 5 € 01, 02 Geschmacksverstärker, Konservierungsstoff
M4 Gemüseeintopf A 3 € 00 Zusatzfrei

Anomalie 4 (Lösch-Anomalie): Das Menü "Pizza" wird gelöscht. Da dieses Menü das einzige der Kategorie "B" ist, wird Kategorie "B" mit gelöscht und fehlt jetzt in der Datenbank.

MenueNr MenueName Kategorie KatPreis ZusatzNr ZusatzName
M1 Spaghetti mit Tomatensoße A 3,50 € 03 Farbstoff
M3 Bratwurst mit Kartoffelpüree C 5 € 01, 02 Geschmacksverstärker, Konservierungsstoff
M4 Gemüseeintopf A 3 € 00 Zusatzfrei

1. Normalform

Beim Umbau einer Datenbank in die 1. Normalform werden Attribute, die mehr als eine Information enthalten, zerlegt. Dazu verteilt man die Datensätze mit Mehrfachattributen auf mehrere Datensätze. Es entstehen dabei Datensätze mit atomaren Attributen, bei denen ein Attribut nur eine Information enthält.

MenueNr MenueName Kategorie KatPreis ZusatzNr ZusatzName
M1 Spaghetti mit Tomatensoße A 3 € 03 Farbstoff
M2 Pizza B 4 € 01, 03 Geschmacksverstärker, Farbstoff
M3 Bratwurst mit Kartoffelpüree C 5 € 01, 02 Geschmacksverstärker, Konservierungsstoff
M4 Gemüseeintopf A 3 € 00 Zusatzfrei

wird zu

MenueNr MenueName Kategorie KatPreis ZusatzNr ZusatzName
M1 Spaghetti mit Tomatensoße A 3 € 03 Farbstoff
M2 Pizza B 4 € 01 Geschmacksverstärker
M2 Pizza B 4 € 03 Farbstoff
M3 Bratwurst mit Kartoffelpüree C 5 € 01 Geschmacksverstärker
M3 Bratwurst mit Kartoffelpüree C 5 € 02 Konservierungsstoff
M4 Gemüseeintopf A 3 € 00 Zusatzfrei

2. Normalform

Die Tabelle wird so in kleinere Tabellen zerlegt, dass Attribute, die zusammengehören, in eine eigene Tabelle geschrieben werden. Die zusammengehörenden Daten werden in weiteren Tabellen über Fremdschlüssel miteinander verbunden.

MenueNr MenueName Kategorie KatPreis ZusatzNr ZusatzName
M1 Spaghetti mit Tomatensoße A 3 € 03 Farbstoff
M2 Pizza B 4 € 01 Geschmacksverstärker
M2 Pizza B 4 € 03 Farbstoff
M3 Bratwurst mit Kartoffelpüree C 5 € 01 Geschmacksverstärker
M3 Bratwurst mit Kartoffelpüree C 5 € 02 Konservierungsstoff
M4 Gemüseeintopf A 3 € 00 Zusatzfrei

wird zu

Tabelle Menue

MenueNr MenueName Kategorie KatPreis
M1 Spaghetti mit Tomatensoße A 3 €
M2 Pizza B 4 €
M3 Bratwurst mit Kartoffelpüree C 5 €
M4 Gemüseeintopf A 3 €

Tabelle Zusatzstoff

ZusatzNr ZusatzName
00 Zusatzfrei
01 Geschmacksverstärker
02 Konservierungsstoff
03 Farbstoff

Tabelle MenueMitZusatz

MenueNr ZusatzNr
M1 03
M2 01
M2 03
M3 01
M3 02
M4 00

3. Normalform

Für die 3. Normalform werden alle Attribute in einer Tabelle gesucht, die untrennbar zusammengehören und damit in eine eigene Tabelle geschrieben werden können.

Tabelle Menue

MenueNr MenueName Kategorie KatPreis
M1 Spaghetti mit Tomatensoße A 3 €
M2 Pizza B 4 €
M3 Bratwurst mit Kartoffelpüree C 5 €
M4 Gemüseeintopf A 3 €

wird zu Tabelle Kategorie

Kategorie KatPreis
A 3 €
B 4 €
C 5 €

und Tabelle Menue

MenueNr MenueName Kategorie
M1 Spaghetti mit Tomatensoße A
M2 Pizza B
M3 Bratwurst mit Kartoffelpüree C
M4 Gemüseeintopf A

mit den bereits vorhandene Tabellen Zusatzstoff

ZusatzNr ZusatzName
00 Zusatzfrei
01 Geschmacksverstärker
02 Konservierungsstoff
03 Farbstoff

und MenueMitZusatz

MenueNr ZusatzNr
M1 03
M2 01
M2 03
M3 01
M3 02
M4 00

Normalisierte Datenbank

Die Datenbank

MenueNr MenueName Kategorie KatPreis ZusatzNr ZusatzName
M1 Spaghetti mit Tomatensoße A 3 € 03 Farbstoff
M2 Pizza B 4 € 01, 03 Geschmacksverstärker, Farbstoff
M3 Bratwurst mit Kartoffelpüree C 5 € 01, 02 Geschmacksverstärker, Konservierungsstoff
M4 Gemüseeintopf A 3 € 00 Zusatzfrei

besteht umgewandelt in die 3. Normalform aus folgenden Tabellen:

Tabelle Zusatzstoff

ZusatzNr ZusatzName
00 Zusatzfrei
01 Geschmacksverstärker
02 Konservierungsstoff
03 Farbstoff

Tabelle Kategorie

Kategorie KatPreis
A 3 €
B 4 €
C 5 €

Tabelle Menue

MenueNr MenueName Kategorie
M1 Spaghetti mit Tomatensoße A
M2 Pizza B
M3 Bratwurst mit Kartoffelpüree C
M4 Gemüseeintopf A

Tabelle MenueMitZusatz

MenueNr ZusatzNr
M1 03
M2 01
M2 03
M3 01
M3 02
M4 00

Datenbank im DB Browser erstellen

Die Datenbankstruktur im DB Browser for SQLite sieht wie folgt aus:

DB 01

mit folgenden Tabellen:

Tabelle Kategorie

DB 02

Tabelle Menue

DB 03

Tabelle MenueMitZusatz

DB 04

Tabelle Zusatzstoff

DB 05

Die Datenbank zum Herunterladen, Entpacken und Öffnen im DB Browser: mensa.zip

Die SQL-Abfrage zur Erzeugung der Menütabelle lautet:

SELECT Menue.MenueNr, Menue.MenueName, Kategorie.Kategorie, Kategorie.KatPreis, Zusatzstoff.ZusatzNr, Zusatzstoff.ZusatzName
FROM Menue JOIN Kategorie JOIN Zusatzstoff JOIN MenueMitZusatz
ON Menue.Kategorie = Kategorie.Kategorie AND MenueMitZusatz.MenueNr = Menue.MenueNr AND MenueMitZusatz.ZusatzNr = Zusatzstoff.ZusatzNr
ORDER BY Menue.MenueNr ASC

Die SQL-Abfrage liefert folgende Daten aufsteigend sortiert nach der MenueNr:

DB 06

Die usprüngliche Tabelle kann mit geeigneten SQL-Abfragen in der atomaren Form rekonstruiert werden. Ändert sich ein Preis, ein Name oder gibt es einen neuen Zusatzstoff, muss die Änderung nur an einer Stelle vorgenommen werden und wirkt sich auf alle Menü-Datensätze aus. Die Gefahr von Einfüge-, Änderungs- und Löschanomalien hat sich deutlich reduziert.