Datenbankentwurf, Normalisierung Normalisierung in relationalen Datenbanken Vorbemerkungen Beim Entity-Relationship-Modell wird die reale Situation auf Entitäten und Beziehungen abgebildet. Bei diesem Verfahren wird zunächst ein Modell der Realität entwickelt, das später mit Hilfe eines Datenbankmanagementsystems umgesetzt wird. Oft findet man aber bereits Datenbestände vor und möchte diese für die zu realisierende Datenbank nutzbar machen. Diese Datenbestände können in Vorläuferversionen der Datenbank vorkommen und sind mehr oder weniger zweckmäßig strukturiert. Dabei kommt es häufig vor, dass redundante Daten auftreten. Ein Attribut in einer Tabelle ist redundant, wenn man es weglassen kann, ohne einen Informationsverlust zu erleiden. Bei der Datenbankentwicklung sind redundante Merkmale zu vermeiden. So sollte z.B. in einer Entitätsmenge "Mitarbeiter" das Alter des Mitarbeiters nicht aufgenommen, da es aus dem Geburtsdatum ermittelt werden kann. Um eine Datenbank bzw. Tabellen redundanzfrei zu gestalten, wenden wir das Verfahren der Normalisierung an. Unter Umständen können Redundanzen nur mit sehr großem Aufwand vermieden werden. So kann man z.B. bei Adressdaten auf die Postleitzahl verzichten, da diese aus den Angaben des Ortes und der Straße mit Hausnummer aus einer Tabelle ermittelt werden kann. Dies würde für den Datenbestand eines Unternehmens aber sehr aufwendig im Vergleich zur Erfassung der Postleitzahlen sein. Deshalb soll eine Datenbank redundanzarm gestaltet werden, nicht unbedingt redundanzfrei. -1- K.Schrader Datenbankentwurf, Normalisierung Sollen bestehende Daten in ein relationales Datenbankmodell übernommen werde, muß zuerst eine Datenanalyse der bestehenden Daten vorgenommen werden. Dazu müssen alle bestehenden Datenerfassungs- und Datenauswertungsbögen zusammengetragen und ausgewertet werden. Lieferscheine, Rechnungen, Lagerkarteien und ähnliches gehören zu den wichtigtsten Hilfsmitteln für diese Datenanalyse. Im folgenden wird an einem Beispiel die Vorgehensweise der Normalisierung gezeigt. 1. Schritt Rechnung als Ausgangspunkt einer Datenanalsyse Diese Datensammlung widerspricht dem Grundprinzip des relationalen Datenmodells, das ein Attribut in einem Tupel nur einen Attributwert annehmen darf denn jede einzelne Rechnung bzw. Bestellung kann mehrere Positionen enthalten. Zur Datenanalyse ist es notwendig, die im Formular enthaltenen Daten in einer Tabelle zu gliedern. -2- K.Schrader Datenbankentwurf, Normalisierung 2. Schritt: Gliederung der Daten in einer Tabelle Liefer Nr Datum Kunden Nr Name 960151 02.05.96 101 Stein, Peter Liefer Nr Datum Kunden Nr Name 960152 02.05.96 103 Rande rs, Nils Artikel Nr Packung Moorda 23863 Kayh- G002 0,5 mm 34 ude G003 6er Pack K002 K003 Karton L002 125 g Straße PLZ Ort Straße PLZ Ort Am Seeufer 12 23845 Oerin g Artikel Nr Packung K001 Karton K003 Karton K004 Karton Menge Bezeichn- Lager ung Portwein 7 Bex-Bier 7 Preis MWSt 12,45 5,20 2 2 4 3 Hose 2 Damenhut 2 China-Tee 5 112,80 2 65,70 2 8,35 1 3 1 5 Bezeichnung Schuhe Damenhut Sonnenbri lle Lager Preis MWSt 2 2 2 98,50 65,70 76,00 2 2 1 Menge Daten unnormalisiert, d.h. nicht in erster Normalform 3. Schritt: 1. Normalform Eine nichtnormalisierte Datensammlung wird in die erste Normalform überführt, indem alle Wiederholungsgruppen herausgezogen werden so daß alle Attributwerte elementar sind, d.h. in jeder Zelle der Tabelle steht nur ein Wert. Im Beispiel bedeutet das, das die Lieferdaten und Kundendaten von den Lieferpositionen getrennt werden und in je eine neue Tabelle eingegliedert werden. Das Verbindungsglied dieser zwei Tabellen stellt das gemeinsame Attribut {Liefer-Nr} her. Tabelle Lieferdaten Liefer-Nr Lieferdatum 960151 02.05.96 960152 02.05.96 Kunden-Nr KundenKundenName Straße 101 Stein, Peter Moordamm 34 103 Randers, Am Seeufer Nis 12 Tabelle Lieferpositionen Liefer-Nr ArtikelArtikelNr Packung 960151 G002 0,51 960151 G003 6er Pack 960151 K002 960151 K003 Karton 960151 L002 125 g 960152 K001 Karton 960152 K003 Karton 960152 K004 Karton Artikel-Bez Artikel -Lager Portwein 7 Bex-Bier 7 Hose 2 Damenhut 2 China-Tee 5 Schuhe 2 Damenhut 2 Sonnenbrille 2 -3- ArtikelPreis 12,45 5,20 112,80 65,70 8,35 98,50 65,70 76,00 KundenPLZ 23863 Kunden-Ort 23845 Oering ArtikelMWSt 2 2 2 1 1 2 2 1 Kayhude Menge 4 3 3 1 5 10 2 12 K.Schrader 10 2 12 Datenbankentwurf, Normalisierung 4. Schritt: 2. Normalform Eine Tabelle ist in der 2. Normalform, wenn sie das Kriterium der ersten Normalform erfüllt und jedes Merkmal unmittelbar vom Schlüssel abhängt. (Jede Spalte einer Tabelle, die den Primärschlüssel nicht bildet, darf nur vom ganzen Primärschlüssel und nicht nur von einem Teil funktional abhängig sein) Im Beispiel wird der Schlüssel in der Tabelle Positionsdaten durch die Attribute {Liefer-Nr, Artikel-Nr} gebildet, die Artikel-Attribute hängen aber schon von Teil des zusammengesetzten Schlüssels, nämlich der {Artikel-Nr} ab. Zur Normalisierung dieser Tabelle wird deshalb die Artikel-Nr und alle Artikelattribute herausgezogen. Damit entstehen jetzt zwei Tabellen: Tabelle Artikel Artikel-Nr ArtikelPackung G002 0,5 l G003 6er Pack K002 K003 Karton L002 125 g K001 Karton K003 Karton K004 Karton ArtikelBezeichnung Portwein Bex-Bier Hose Damenhut China-Tee Schuhe Damenhut Sonnenbrille Tabelle Positionsdaten Liefer-Nr 960151 960151 960151 960151 960151 960152 960152 960152 Artikel-Nr G002 G003 K002 K003 L002 K001 K003 K004 Artikel-Lager Artikel-Preis 7 7 2 2 5 2 2 2 12,45 5,20 112,80 65,70 8,35 98,50 65,70 76,00 ArtikelMWSt 2 2 2 2 1 2 2 1 Menge 4 3 3 1 5 10 2 12 Die Tabelle Lieferdaten ist in der zweiten Normalform, da der Schlüssel nur aus dem Attribut {Liefer-Nr} besteht und alle Attribute nur von diesem abhängig sind. Tabelle Lieferdaten Liefer-Nr Lieferdatum 960151 02.05.96 960152 02.05.96 Kunden-Nr KundenKundenName Straße 101 Stein, Peter Moordamm 34 103 Randers, Am Seeufer Nis 12 KundenPLZ 23863 Kunden-Ort 23845 Oering Kayhude Allerdings hängen in der Tabelle Lieferdaten ein Teil der Attribute vom Nichtschlüsselattribut {Kunden-Nr} ab, deshalb folgt jetzt der nächste Normalisierungsschritt. -4- K.Schrader Datenbankentwurf, Normalisierung 5. Schritt 3. Normalform Eine Tabelle ist in der dritten Normalform, wenn zwischen den Attributen der Tabelle, die nicht den Primärschlüssel bilden, keine Abhängigkeiten existieren. In der Tabelle Lieferdaten sind aber die Attribute {Kunden-Name, KundenStraße, Kunden-PLZ, Kunden-Ort}von dem Nichtschlüsselattribut {KundenNr} abhängig. Deshalb erfolgt hier auch eine Auftrennung in zwei Tabellen: Tabelle Kunde Kunden-Nr 101 103 Kunden-Name Stein, Peter Randers, Nis Tabelle Lieferdaten Liefer-Nr 960151 960152 Kunden-Straße Moordamm 34 Am Seeufer 12 Liefer-Datum 02.05.96 02.05.96 Kunden-PLZ 23863 23845 Kunden-Ort Kayhude Oering Kunden-Nr 101 103 Die anderen Tabellen befinden sich bereits in der dritten Normalform: Tabelle Artikel Artikel-Nr ArtikelPackung G002 0,5 l G003 6er Pack K002 K003 Karton L002 125 g K001 Karton K003 Karton K004 Karton ArtikelBezeichnung Portwein Bex-Bier Hose Damenhut China-Tee Schuhe Damenhut Sonnenbrille Tabelle Positionsdaten Liefer-Nr 960151 960151 960151 960151 960151 960152 960152 960152 Artikel-Nr G002 G003 K002 K003 L002 K001 K003 K004 Artikel-Lager Artikel-Preis 7 7 2 2 5 2 2 2 12,45 5,20 112,80 65,70 8,35 98,50 65,70 76,00 ArtikelMWSt 2 2 2 2 1 2 2 1 Menge 4 3 3 1 5 10 2 12 -5- K.Schrader Datenbankentwurf, Normalisierung Grafische Darstellung des Normalisierungsprozesses: Daten in nicht-normalisierter Form: 1. Normalform 2. Normalform -6- K.Schrader Datenbankentwurf, Normalisierung 3. Normalform Ein weiteres Beispiel soll den Normalisierungsprozess vertiefen: Folgende Daten der Firma Visual Data-AG wurden zum Erstellen von Rechnungen ermittelt und in einer Tabelle zusammengestellt: ReNr 1 ReDatum ReBetrag 12.9.95 1.500,- KuNr 2 KuName Müller KuOrt Berlin ArtNr 2, 4, 11 2 15.10.95 950,- 5 Schultze München 5, 4 3 17.11.95 1.025,- 1 Mayer Hamburg 2, 5 ArtName Delphi 2.0, VB 4.0, Paradox 7.0 Word 7.0, VB 4.0 Delphi 2.0, Word 7.0 In den Feldern {ArtNr} und {ArtName} sind mehrfache Merkmalswerte eingetragen. Deshalb befindet sich diese Tabelle nicht in der ersten Normalform. Aufgabe: Führen Sie eine Normalisierung Daten der Visual Data-AG bis zur 3. Normalform durch! -7- K.Schrader Datenbankentwurf, Normalisierung Lösung: 1. Normalform Umgruppieren der Daten ReNr 1 1 1 2 2 3 3 ReDatum 12.9.95 12.9.95 12.9.95 15.10.95 15.10.95 17.11.95 17.11.95 ReBetrag 1.500,1.500,1.500,950,950,1.025,1.025,- KuNr 2 2 2 5 5 1 1 KuName Müller Müller Müller Schultze Schultze Mayer Mayer KuOrt Berlin Berlin Berlin München München Hamburg Hamburg ArtNr 2 4 11 5 4 2 5 ArtName Delphi 2.0 VB 4.0 Paradox 7.0 Word 7.0 VB 4.0 Delphi 2.0 Word 7.0 Die Merkmale KuNr, KuName, KuOrt, ArtNr und ArtName sind vom Wert des Schlüssels {ReNr} unabhängig, widersprechen also der 2 Normalform. (Zu jeder Rechnungsnummer gibt es nur ein bestimmtes Rechnungsdatum, der Inhalt der anderen Felder aber kann beliebig sein) 2. Normalform Kunden und Artikel herausziehen, Verbindung über eine Tabelle Rechnungen herstellen Kunden KuNr KuName KuOrt Artikel 1 Mayer Hamburg 2 Müller Berlin ReNr ArtNr ArtName 5 Schultze München 1 2 Delphi 2.0 1 4 VB 4.0 Rechnungen 1 11 Paradox 7.0 2 5 Word 7.0 ReNr KuNr ReDatum ReBetrag 2 4 VB 4.0 1 2 12.9.95 1.500,3 2 Delphi 2.0 2 5 15.10.95 950,3 5 Word 7.0 3 1 17.11.95 1.025,Die Tabelle Artikel weist beim Betrachten allerdings Spuren von Redundanz auf, d.h. Daten werden hier doppelt gespeichert. Der Grund ist eine Abhängigkeit zwischen dem Attribut {ArtNr} und {ArtName}, deshalb ist die zweite Normalform hier verletzt. Ausweg: Tabelle Artikel in zwei Tabellen aufteilen: Rechnungsdaten Artikel ReNr 1 1 1 2 2 3 3 ArtNr 2 4 5 11 ArtNr 2 4 11 5 4 2 5 ArtName Delphi 2.0 VB 4.0 Word 7.0 Paradox 7.0 -8- K.Schrader Datenbankentwurf, Normalisierung 3. Normalform Eine Überprüfung der Tabellen ergibt, daß kein Nichtschlüsselattribut in den einzelnen Tabellen von einem anderen Nichtschlüsselattribut abhängig ist. Deshalb ist die dritte Normalform schon erfüllt. Somit ergibt sich als Datenbankentwurf folgende Tabellenstruktur: Kunden 1 KuNr (PK) 1 2 5 KuName Mayer Müller Schultze KuOrt Hamburg Berlin München 1 Rechnungen n ReNr (PK) KuNr (FK) 1 2 2 5 3 1 ReDatum 12.9.95 15.10.95 17.11.95 ReBetrag 1.500,950,1.025,- n Rechnungsdaten n ReNr (PK,FK) ArtNr (PK,FK) 1 2 1 4 1 11 2 5 2 4 3 2 3 5 Artikel 1 ArtNr (PK) 2 4 5 11 ArtName Delphi 2.0 VB 4.0 Word 7.0 Paradox 7.0 Aufgaben: 1. 2. 3. 4. Erstellen Sie die Tabellen der Visual Data-AG mit Hilfe von SQL-Befehlen in einer Access (Interbase) -Datenbank! Füllen Sie die Daten in die Tabellen ebenfalls mit SQL-Befehlen! Testen Sie die Wirkungen verschiedener Abfragen mit dem SELECT-Befehl auf die Datenbank! Stellen Sie mit Access (Interbase) die Beziehungen unter den Tabellen her und veranschaulichen Sie die Datenstruktur in einem Entity-Relationship-Modell! Quellen: Borland Delphi 4, Hanser Verlag, Doberenz/Kowalski Relationale Datenbanken und SQL, Addison-Wesley, Matthiesen/Unterstein Anwendungsentwicklung, Stam-Verlag -9- K.Schrader Datenbankentwurf, Normalisierung Datenbank-Design und SQL Seminarunterlage, Herdt-Verlag - 10 - K.Schrader