Anomalien und deren Vermeidung durch Normalisierung eines Relationenschemas
Ausgangspunkt
Wir betrachten folgende Relation:
bestellte_artikel(BestellNr (PK), Artikel (PK), KundenNr, Vorname, Nachname, BestellDatum, Anzahl, Einzelpreis)
Mit beispielhaft folgenden Einträgen (Tupel):
| KundenNr | Vorname | Nachname | BestellNr | BestellDatum | Artikel | Anzahl | EinzelEinzelpreis |
|---|---|---|---|---|---|---|---|
| 789 | Max | Meier | 007 | 17.8.2020 | 878: CPU i7 7430 | 2 | 123,80€ |
| 789 | Max | Meier | 007 | 17.8.2020 | 879: Mainboard Asus z67 | 1 | 227,95€ |
Wenn wir mit Relationen wie diesen arbeiten können im Verlauf der Datenanpassung eine Reihe von Problemen auftreten, die wir in drei Kategorien eingliedern können:
Einfüge-,
Änderungs- und
Löschanomalien.
Anomalien
Einfüge-Anomalie
Eine Einfüge-(Insertion)-Anomalie liegt vor, wenn neue Einträge (Tupel = Entitäten/Objekte/Tabellenzeilen) in ein Relationenschema nicht vorgenommen werden können, da nicht alle (Teil-)Schlüsselattribute gesetzt werden können. Die gewünschten Operationen können nicht durchgeführt werden und das Relationenschema kann nicht die Tupel enthalten, die es enthalten soll.
Wenn wir beispielsweise in unsere Beispiel-Relation einen neuen Artikel aufnehmen wollen, so ist dies in der derzeitigen Form nicht möglich, solange dieser nicht in einem Bestellvorgang genutzt wird und eine BestellNr vorliegt.
| KundenNr | Vorname | Nachname | BestellNr | BestellDatum | Artikel | Anzahl | Einzelpreis |
|---|---|---|---|---|---|---|---|
| 789 | Max | Meier | 007 | 17.8.2020 | 878: CPU i7 7430 | 2 | 123,80€ |
| ??? !!! | 901: HDD WD Red Lable | 227,95€ |
Änderungsanomalie
Von einer Änderungs-(Update)-Anomalie spricht man, wenn in einem Relationenschema in mehreren Tupeln redundante Informationen stehen, bei einem Änderungsvorgang aber nicht alle betroffenen Attribute/Tupel angepasst werden. Es entsteht ein inkonsistentes Relationenschema, das ggf. fehlerhafte oder nicht eindeutige Informationen enthält.
Beispiel:
Der Kunde Max Meier hat mitgeteilt, dass er sich wie der bayerische Maier schreibt. Die Information wurde jedoch nicht in jedem Tupel (Zeile) angepasst:
| KundenNr | Vorname | Nachname | BestellNr | BestellDatum | Artikel | Anzahl | Einzelpreis |
|---|---|---|---|---|---|---|---|
| 789 | Max | Maier | 007 | 17.8.2020 | 878: CPU i7 7430 | 2 | 123,80€ |
| 789 | Max | !!! Meier !!! | 007 | 17.8.2020 | 879: Mainboard Asus z67 | 1 | 227,95€ |
Lösch-Anomalien
Von einer Lösch-(Delete)-Anomalie spricht man, wenn in einem Relationenschema mehr Daten gelöscht werden, als ursprünglich intendiert.
Wenn in unserem Beispiel die Bestellung 007 von Max Maier gelöscht werden ist auch die Information über die Artikel und ihre Einzelpreise verloren - sofern sie nicht bei einer anderen Bestellung noch auftauchen. Ebenso sind die Informationen über Max Maier selbst verloren, sofern sie nicht anderweitig auftauchen.
| KundenNr | Vorname | Nachname | BestellNr | BestellDatum | Artikel | Anzahl | Einzelpreis |
|---|---|---|---|---|---|---|---|
| !!! |
!!! |
!!! |
!!! |
!!! |
|||
| !!! |
!!! |
!!! |
!!! |
!!! |
Normaliserung von Datenbanken
Um zu verhindern, dass Anomalien auftreten, muss ein Relationenschema in der Struktur bestimmten Regeln folgen. Schrittweise werden die Strukturen eliminiert, die Probleme verursachen könnten. Dieser Prozess nennt sich Normalisierung und erfolgt in mehreren Schritten, den Normalformen.
Wir betrachten folgendes Relationenschema:
| KundenNr | Vorname | Nachname | Telefon | BestellNr | BestellDatum | Artikel | Anzahl | Einzelpreis |
|---|---|---|---|---|---|---|---|---|
| 789 | Max | Meier | 0521 / 1234-0 | 007 | 17.8.2020 | 878: CPU i7 7430 | 2 | 123,80€ |
| 789 | Max | Meier | 0521 / 1234-0 | 007 | 17.8.2020 | 879: Mainboard Asus z67 | 1 | 227,95€ |
| 123 | Stefan | Schmidt | 0521 / 567-0, 0163 / 4567 |
013 | 19.8.2020 | 901: HDD WD Red Lable | 4 | 157,95€ |
| 123 | Stefan | Schmidt | 0521 / 567-0, 0163 / 4567 |
013 | 19.8.2020 | 879: Mainboard Asus z67 | 1 | 227,95€ |
Die erste Normalform (1NF)
Definition
| Ein Relationenschema ist in erster Normalform, wenn alle Attribute des Schemas elementar sind. |
|---|
Klartext
Attribute müssen elementar also einwertig, atomar, unteilbar sein:
keine Listen als Attribut
keine Mengen als Attribut
keine Relationen als Attribut
Prozess der Normalisierung für die 1. Normalform:
Attribute mit Listen / Mengen / Relationen identifizieren
Attribute mit Listen (also mehreren gleichwertigen Attributen) in mehrere Tupel (Zeilen) aufteilen
Attribute mit Mengen/Relationen (also Attribute mit mehreren unterschiedlichen teilbaren Werten) in mehrere neue Attribute aufteilen.
Überprüfen, ob die bisherigen Primärschlüssel noch hinreichend sind - ggf. neue Zusammengesetzte primärschlüssel bilden
An einem Beispiel
- Identifikation der Mengen und Listen: gelb hinterlegt sind Listen, grün Mengen.
- Attribute mit Listen (gelb) in Zeilen aufteilen und neuen zusammengesetzten Primärschlüssel bilden. Da Telefon für sich genommen nicht als Teilschlüssel funktionieren muss wird KundenNr und Telefon als Schlüssel ergänzt: (Artikel, BestellNr, KundenNr, Telefon)
| KundenNr | Vorname | Nachname | Telefon | BestellNr | BestellDatum | Artikel | Anzahl | Einzelpreis |
|---|---|---|---|---|---|---|---|---|
| 789 | Max | Meier | 0521 / 1234-0 | 007 | 17.8.2020 | 878: CPU i7 7430 | 2 | 123,80€ |
| 789 | Max | Meier | 0521 / 1234-0 | 007 | 17.8.2020 | 879: Mainboard Asus z67 | 1 | 227,95€ |
| 123 | Stefan | Schmidt | 0521 / 567-0 | 013 | 19.8.2020 | 901: HDD WD Red Lable | 4 | 157,95€ |
| 123 | Stefan | Schmidt | 0163 / 4567 | 013 | 19.8.2020 | 901: HDD WD Red Lable | 4 | 157,95€ |
| 123 | Stefan | Schmidt | 0521 / 567-0 | 013 | 19.8.2020 | 879: Mainboard Asus z67 | 1 | 227,95€ |
| 123 | Stefan | Schmidt | 0163 / 4567 | 013 | 19.8.2020 | 879: Mainboard Asus z67 | 1 | 227,95€ |
- Attribute mit Mengen (grün) in mehrere Attribute aufteilen und ggf. neuen Primärschlüssel bilden (ArtikelNr, BestellNr, KundenNr, Telefon)
| KundenNr | Vorname | Nachname | Telefon | BestellNr | BestellDatum | ArtikelNr | ArtikelName | Anzahl | Einzelpreis |
|---|---|---|---|---|---|---|---|---|---|
| 789 | Max | Meier | 0521 / 1234-0 | 007 | 17.8.2020 | 878 | CPU i7 7430 | 2 | 123,80€ |
| 789 | Max | Meier | 0521 / 1234-0 | 007 | 17.8.2020 | 879 | Mainboard Asus z67 | 1 | 227,95€ |
| 123 | Stefan | Schmidt | 0521 / 567-0 | 013 | 19.8.2020 | 901 | HDD WD Red Lable | 4 | 157,95€ |
| 123 | Stefan | Schmidt | 0163 / 4567 | 013 | 19.8.2020 | 901 | HDD WD Red Lable | 4 | 157,95€ |
| 123 | Stefan | Schmidt | 0521 / 567-0 | 013 | 19.8.2020 | 879 | Mainboard Asus z67 | 1 | 227,95€ |
| 123 | Stefan | Schmidt | 0163 / 4567 | 013 | 19.8.2020 | 879 | Mainboard Asus z67 | 1 | 227,95€ |
Die zweite Normalform (2NF)
Definition
Ein Relationenschema ist in zweiter Normalform, wenn es
in erster Normalform ist und
jedes Nichtschlüsselattribut voll funktional von jedem Schlüsselkandidat abhängt (und nicht nur von einem Teilschlüssel).
Klartext
Bei zusammengesetzten Primärschlüsseln hängt jedes Attribut, das kein Schlüssel ist, vom gesamten Primärschlüssel ab, und nicht nur von einzelnen Teilschlüsseln.
Prozess der Normalisierung für die 2. Normalform:
Zusammengesetzter Primärschlüssel? (falls nein: 2. NF liegt vor)
Folgen aus Teilschlüsselattributen weitere Attribute? (falls nein: 2. NF liegt vor)
Bildung neue Relation aus dem Teilschlüsselattribut (dann neuer Primärschlüssel) und allen von diesem abhängigen Nichtschlüsselattributen.
Löschen der ausgelagerten Nichtschlüsselattribute in der Ausgangsrelation (Ausgelagerte Teilschlüssel bleíben als Fremdschlüssel in der Ausgangsrelation).
Bildung neuer Relationen wiederholen, bis alle Nichtschlüsselattribute vom gesamten Schlüssel funktional abhängig sind.
An einem Beispiel
Ergebnis der 1. Normalform war das folgende Relationenschema:
bestellte_artikel(KundenNr (PK), Vorname, Nachname, Telefon (PK), BestellNr (PK), BestellDatum, ArtikelNr (PK), ArtikelName, Anzahl, Einzelpreis)
- Haben wir einen zusammengesetzten Primärschlüssel?
Ja. Der zusammengesetzte Primärschlüssel ist: (KundeNr, Telefon, BestellNr, ArtikelNr)
- Folgen aus Teilschlüsselattributen weitere Attribute?
Eine ganze Reihe (hier farblich markiert):
Abhängigkeit nur von ArtikelNr (gelb): ArtikelName, Einzelpreis
Abhängigkeit nur von BestellNr (grün): BestellDatum
Abhängigkeit nur von KundenNr und TelefonNr (blau): KundenNr, Vorname, Nachname, TelefonNr
Es ergeben sich also folgende neuen Relationen:
artikel(ArtikelNr (PK), ArtikelName, Einzelpreis)
| ArtikelNr | ArtikelName | Einzelpreis |
|---|---|---|
| 878 | CPU i7 7430 | 123,80€ |
| 879 | Mainboard Asus z67 | 227,95€ |
| 901 | HDD WD Red Lable | 157,95€ |
bestellungen(BestellNr (PK), BestellDatum)
| BestellNr | BestellDatum |
|---|---|
| 007 | 17.8.2020 |
| 013 | 19.8.2020 |
Kundentelefonnummern(Telefon (PK), KundenNr (PK), Vorname, Nachname)
| KundenNr | Vorname | Nachname | Telefon |
|---|---|---|---|
| 789 | Max | Meier | 0521 / 1234-0 |
| 123 | Stefan | Schmidt | 0521 / 567-0 |
| 123 | Stefan | Schmidt | 0163 / 4567 |
Hier müsste nochmals überprüft werden, ob weiterhin Abhängigkeiten vorliegen:
Vorname und Nachname hängen nur von Kundennummer ab, müssten also weiterhin ausgelagert werden:
Kunden(KundenNr (PK), Vorname, Nachname)
| KundenNr | Vorname | Nachname | |
|---|---|---|---|
| 789 | Max | Meier | |
| 123 | Stefan | Schmidt |
Telefon(↑KundenNr↑ (PK,FK), Telefon (PK))
(Unter der Annahme, dass mehrere Kunden die selbe Telefonnummer haben könnten verbleibt die KundenNr hier Teilschlüsselattribut)
| ↑KundenNr↑ | Telefon |
|---|---|
| 789 | 0521 / 1234-0 |
| 123 | 0521 / 567-0 |
| 123 | 0163 / 4567 |
sowie die Ausgangrelation ohne die entsprechenden Attribute:
bestellte_artikel(↑KundenNr↑ (PK, FK), ↑Telefon↑ (PK, FK), ↑BestellNr↑ (PK, FK), ↑ArtikelNr↑ (PK, FK), Anzahl)
| ↑KundenNr↑ | ↑BestellNr↑ | ↑ArtikelNr↑ | Anzahl |
|---|---|---|---|
| 789 | 007 | 878 | 2 |
| 789 | 007 | 879 | 1 |
| 123 | 013 | 901 | 4 |
| 123 | 013 | 901 | 4 |
| 123 | 013 | 879 | 1 |
| 123 | 013 | 879 | 1 |
Die dritte Normalform (3NF)
Definition
Ein Relationenschema ist in dritter Normalform (3NF), wenn es
in zweiter Normalform ist und
kein Nichtschlüsselattribut transitiv von einem Schlüssel abhängt
Klartext
Aus keinem Nichtschlüsselattribut folgt ein anderes Nichtschlüsselattribut.
Prozess der Normalisierung für die 3. Normalform:
Folgen aus einem Nichtschlüsselattribut weitere (Nichtschlüssel-)Attribute? (falls nein: 3. NF liegt vor)
Bildung neue Relation aus diesem Nichtschlüsselattribut (dann neuer Primärschlüssel) und allen von diesem abhängigen Attributen.
Nichtschlüsselattribut bleibt als Fremdschlüssel in der Ausgangsrelation, ausgelagerte Attribute werden gelöscht.
Bildung neuer Relationen wiederholen, bis keine Nichtschlüsselattribute von anderen Nichtschlüsselattributen funktional abhängig sind.
An einem Beispiel:
Wenn beispielsweise für Kunden erfasst würde, ob die Einzelpreise brutto oder netto ausgewiesen werden und dies in Abhängigkeit der Kundenart - etwa so:
Kunden(KundenNr (PK), Vorname, Nachname, Kundenart, BruttoNetto)
| KundenNr | Vorname | Nachname | Kundenart | BruttoNetto | |
|---|---|---|---|---|---|
| 789 | Max | Meier | Geschäftskunde | netto | |
| 123 | Stefan | Schmidt | Privatkunde | brutto |
Es liegt eine Abhängigkeit zwischen zwei Nichtschlüsselattributen Kundenart und BruttoNetto vor. Die abhängigen Attribute müssen in eine neue Relation überführt werden, wobei das Attribut, von dem die anderen abhängen, als Fremdschlüssel in der Ausgangsrelation verbleibt.
Die neue Relation wäre hier:
kundeart(Kundenart (PK), BruttoNetto)
| Kundenart | BruttoNetto |
|---|---|
| Geschäftskunde | netto |
| Privatkunde | brutto |
Die Ausgangsrelation würde um die anhängigen Attribute gekürzt und die neue Fremdschlüssel Kennzeichnung eingefügt:
kunden(KundenNr(PK), Vorname, Nachname, ↑Kundenart↑ (FK))
| KundenNr | Vorname | Nachname | ↑Kundenart↑ | |
|---|---|---|---|---|
| 789 | Max | Meier | Geschäftskunde | |
| 123 | Stefan | Schmidt | Privatkunde |
