Lernziele

WS 2004/2005Prof. Dr. J. Dunker

Entwurf normalisierter Datenbankschemata

Folie 1

Lernziele

• Sie wissen was unter Normalisierung eines Datenbankschemas verstanden wird.

• Sie können Einfüge-, Lösch- und Änderungsanomalien beschreiben.

• Optional: Sie kennen den Begriff der (vollen) funktionalen Abhängigkeit.

• Optional: Sie wissen, was unter dem Begriff der Normalform einer Relation verstanden wird und können entscheiden, ob sich eine Relation in der ersten, zweiten oder dritten Normalform befindet.

• Sie kennen die allgemeine Faustregel zum Tabellendesign, die sagt, dass eine Tabelle nicht mehr als einen (Entitäts-)Typ repräsentieren sollte und wissen warum.



Folie 2

Normalisierung: Einführung

• Zentrale Frage des Normalisierungsprozesses:

• Vorweggenommenes Hauptergebnis:

• Beispiel: Bilde zwei separate Tabellen zu Professor und Kurs, statt Kurs- und Professorendaten in einer zu vermengen!

Wie lässt sich ein relationales Datenbankschema derart entwickeln, dass unnötige Redundanzen bzw.

Anomalien vermieden werden können?

Ein Relationenschema ist immer so zu entwerfen, dass es genau einen Objekttyp (Entity-Typ) bzw. genau einen

Beziehungstyp beschreibt.



Folie 3

Anomalien I

• Hält man sich nicht an obige Entwurfsregel, so erzeugt man Datenhaltungsprobleme, die als Einfüge-, Lösch- und Änderungsanomalie bekannt sind.

• Beispiel:Betrachte folgendes Relationenschema zum Informationssystem einer Hochschule:Auskunft(KNr, KursName, Klausurtermin, ProfNr, ProfName, TelefonNr)

• Beobachtung:Die Telefonnummer gehört zum Entitäts-Typ Professor und hat direkt nicht mit dem vom Professor betreuten Kurs zu tun. Die Entitäts-Typen Professor und Kurs sind vermengt!



Folie 4

Anomalien II

• BeobachtungenWas fällt Ihnen auf? Ist diese Tabelle „vernünftig“ entworfen? Kann man die Einträge überhaupt vornehmen wie sie dargestellt sind?KNr KursName Klausurtermin ProfNr ProfName TelefonNr100 HTML 10.02.2004 1000 Meier 0509/606060101 XML 19.03.2004 1000 Meier 0509/606060102 NixML 20.03.2004 1000 Meier 0509/606060103 Java-Script 20.03.2004 1001 Claasen 0509/606061

1010 Franzen 0509/6060101011 Möllers 0509/606011



Folie 5

Anomalien III

• Einfügeanomalie:Über neu an die Hochschule berufene Professoren können so lange keine Daten abgelegt werden, so lange diese keine Kurse geben; obwohl ProfNr, ProfName und Telefonnummer bekannt sind!

• Löschanomalie (Umkehrung der Einfügeanomalie):Wird der einzige Kurs, den ein Professor gibt, nicht mehr angeboten, so geht mit Löschen des Kurses aus der Tabelle auch die Professoreninformation verloren.

• Änderungsanomalie:Ändert sich die Telefonnummer des Professors, so ist diese Änderung so häufig durchzuführen, wie es redundante Ablagen der Telefonnummer gibt: so viele, wie es vom Professor gehaltene Kurse gibt.



Folie 6

Anomalien IV• Im Rahmen der Normalisierung würde man nun aus dem

RelationenschemaAuskunft(KNr, KursName, Klausurtermin, ProfNr, ProfName, TelefonNr)folgende zwei Relationenschemata machenKurs(KNr, Kursname, Klausurtermin)Professor(ProfNr, ProfName, TelefonNr)sowie zur Abbildung der 1:n-Beziehung zwischen Professor und Kurs das Relationenschema (alternativ natürlich auch ProfNr als Fremdschlüssel in Kurs) :gibtKurs(KNr,ProfNr) Beachte:

bei sauberer ER-Modellierung kommt es zu solchen Anomalien i.d.R. nicht!



Folie 7

Anomalien V

Beboachtungen ?

ProfNr ProfName TelefonNr1000 Meier 0509/6060601001 Claasen 0509/6060611010 Franzen 0509/6060101011 Möllers 0509/606011

KNr KursName Klausurtermin ProfNr100 HTML 10.02.2004 1000101 XML 19.03.2004 1000102 NixML 20.03.2004 1000103 Java-Script 20.03.2004 1001



Folie 8

Hinweis

• Die Inhalte der folgenden Folien sind nicht prüfungsrelevant und damit optionaler Vorlesungsbestandteil. Wer Lust hat, sich dem Anomalienthema formal zu nähern, kann die Folien gern studieren.

• Beherzigt man – ausgehend von einem ER-Modell - die folgende Regel, so erhält man in der Regel für die Praxis „hinreichend normalisierte“ Datenbankschemata, die die vorab diskutierten Anomalien nicht aufweisen:

Ein Relationenschema ist immer so zu entwerfen, dass es genau einen Objekttyp (Entity-Typ) bzw.

genau einen Beziehungstyp beschreibt.



Folie 9

Funktionale Abhängigkeit I

• Bei der Normalisierung spielen Abhängigkeiten zwischen Attributen eine wesentliche Rolle; zentral für den Normalisierungsprozess ist deshalb der Begriff der funktionalen Abhängigkeit.

• Definition (funktionale Abhängigkeit)Gegeben sei ein Relationenschema R(A1,...,An). X bzw. Y seien echte Teilmengen von {A1,...,An}. Dann heißt Y funktional abhängig von X, wenn es keine zwei Tupel geben kann, die bei gleichen X-Werten unterschiedliche Y-Werte aufweisen. Wir schreiben dann X Y im Sinne von X legt Y eindeutig fest.

• Also: „einem X können nicht zwei Y zugeordnet sein“ „zu einer KursNr kann es nicht zwei Kursnamen geben“

x

y



Folie 10

Funktionale Abhängigkeit II

• Beachte: ob Attributmengen funktional abhängig voneinander sind, läßt sich nur durch „semantische Analyse“ (Analyse der Realwelt) ermitteln; nicht aber formal!

• Beispiel: Auskunft(KNr, KursName, Klausurtermin, ProfNr, ProfName, TelefonNr)Funktionale Abhängigkeiten (Auszug):{KNr} {KursName, Klausurtermin, ProfNr} {KNr} {KursName}

{Klausurtermin}{ProfNr}

{ProfNr} {ProfName}{TelefonNr}

• Gegenbeispiele (keine funktionalen Abhängigkeiten):Zu einem ProfNamen kann es zwei ProfNummern gebenZu einem Klausurtermin kann es zwei KursNamen geben



Folie 11

Eigenschaften funktionaler Abhängigkeiten

• TransitivitätseigenschaftGilt X Y und Y Z, so gilt auch X Z und Z heißt dann transitiv abhängig von X.

• Beispiel 1Aus{KNr} {ProfNr} {TelefonNr}folgt{KNr} {TelefonNr}

• Beispiel 2Abteilung(MitarbeiterNr, Name, Gehalt, Abteilungsleiter, Budget)Aus{MitarbeiterNr} {Abteilungsleiter} {Budget}folgt: {MitarbeiterNr} {Budget}



Folie 12

Volle funktionale Abhängigkeit

• Zur Definition der Normalformen wird weiter der Begriff der vollen funktionalen Abhängigkeit benötigt.

• Definition (volle funktionale Abhängigkeit)Gegeben sei ein Relationenschema R(A1,...,An). X bzw. Y seien echte Teilmengen von {A1,...,An}. Dann heißt Y voll funktional abhängig von X, wenn• X Y (d.h. Y ist funktional abhängig von X) und• es existiert keine echte Teilmenge X‘ von X derart, dass

X‘ Y gilt.• Also: X bestimmt Y eindeutig und zugleich gilt, dass keine echte

Teilmenge von X den Y-Wert eindeutig bestimmt.• Beispiele:

{Klausurtermin} voll funktional abhängig von {KursNr}{Klausurtermin} nicht voll funkt. abhängig von {KursNr, ProfNr}



Folie 13

Schlüssel -und Nichtschlüsselattribute

• Gegeben sei ein Relationenschema R(A1,...,An), dann heißt ein Attribut A {A1,...,An}:

• Schlüsselattribut, wenn es Element irgendeines Schlüssels von R ist

• Nichtschlüsselattribut im anderen Fall.• Beispiel:

Abteilung(AbtNr, AbtName, Leiter, Budget, Geschäftsbereich)Schlüssel sind: {AbtNr} sowie {AbtName, Geschäftsbereich}also sind:Schlüsselattribute: AbtNr, AbtName, GeschäftsbereichNichtschlüsselattribute: Leiter, Budget



Folie 14

1. Normalform• Eine Relation ist in 1. Normalform, wenn sämtliche

Attributwerte atomar sind.Anschaulich: in jeder Zelle einer entsprechenden Tabelle steht nur ein einziger Wert.

• Gegenbeispiele:Attribut Hobbies mit listenartigen Zelleinträgen der Form:Lesen, Wandern, Musizieren Attribut Telefonnummern mit:02581/171717, 0171/262626

• Beobachtung: Auskunft(KNr, KursName, Klausurtermin, ProfNr, ProfName, TelefonNr)ist in 1-NF, wenn man davon ausgeht, dass es nur einen Klausurtermin und nur eine Diensttelefonnummer gibt; erste Normalform allein hilft also noch nicht...



Folie 15

Erzeugung der 1. Normalform

• Betrachte: Auskunft(KNr, KursName, Klausurtermin, ProfNr, ProfName, TelefonNr)und gehen wir nun von nicht atomaren Attributwerten zu Klausurtermin aus, so ist eine separate Klausurtermintabelle anzulegen. KNr KursName Klausurtermin ProfNr ProfName TelefonNr

100 Datenbanken 15.2.04, 15.3.04 10 Meier 187101 Programmierung 15.2.04, 17.3.04 11 Müller 289102 Betriebssysteme 14.2.04, 18.3.04 11 Müller 289

KNr Klausurtermin100 15.02.2004100 15.03.2004101 15.02.2004101 17.03.2004102 14.02.2004102 18.03.2004

KNr KursName ProfNr ProfName TelefonNr100 Datenbanken 10 Meier 187101 Programmierung 11 Müller 289102 Betriebssysteme 11 Müller 289



Folie 16

2. Normalform

• Eine Relation R ist in 2. Normalform, wenn• R in 1. Normalform ist und zudem• jedes Nichtschlüsselattribut voll funktional abhängig von jedem

Schlüssel von R ist.• ... findet man ein Nichtschlüsselattribut, dass nur von Teilen eines

Schlüssels funktional abhängig ist, so ist die Relation nicht in 2-NF.• Beispiele:

Abteilung(AbtNr, AbtName, Leiter, Budget, Geschäftsbereich) ist in 2-NFProjekt(MitarbNr, Name, Vorname, ProjektNr, Bezeichnung, ProzArb)ist nicht in 2-NF, da {Name} funkt. abh. von {MitarbNr}



Folie 17

Erzeugen der 2. Normalform

• Betrachte: Projekt(MitarbNr, Name, Vorname, ProjektNr, Bezeichnung, ProzArb)...Bezeichnung hängt lediglich von ProjektNr ab...Name und Vorname hängen lediglich von MitarbNr ab

• Ansatz:Bringe solche Attribute in einer separaten Relation unter, die nur von einem Teil eines Schlüssels abhängen - und zwar zusammen mit dem entsprechenden Schlüssel-Teil; wir erhalten:

Mitarbeiter(MitarbNr, Name, Vorname)

Projekt(ProjektNr, Bezeichnung)

ProjMitarb(ProjektNr, MitarbNr, ProzArb)



Folie 18

3. Normalform

• Wir betrachten wiederum: Auskunft(KNr, KursName, Klausurtermin, ProfNr, ProfName, TelefonNr)...unterstellen wir wiederum Atomarität der Attributwerte zu Klausurtermin und Telefonnummer, so ist die Relation in 2-NF. 2-NF allein schützt immer noch nicht vor Anomalien!

• Eine Relation R ist in 3. Normalform, wenn gilt

• R in 2. Normalform ist und

• jedes Nichtschlüsselattribut ist direkt, d.h. nicht transitiv abhängig von jedem Schlüssel

• ...findet man ein Nichtschlüsselattribut, das funktional abhängig ist von einer Attributmenge, die selbst kein Schlüssel ist, so ist die Relation nicht in 3-NF.



Folie 19

3. Normalform: Beispiel / Herstellung 3-NF

• Betrachte: Auskunft(KNr, KursName, Klausurtermin, ProfNr, ProfName, TelefonNr)

• Es gilt:{KNr} {ProfNr} {ProfName}...also ist das Nichtschlüsselattribut ProfName transitiv abhängig von KNr und die Relation ist damit nicht in 3-NF.

• Zur Herstellung der 3. Normalform wird die Ausgangsrelation wiederum zerlegt und zwar so, dass Nichtschlüsselattribute zusammen mit den Attributmengen, die selbst nicht Schlüssel sind und von denen sie abhängen, in eine separate Relation „ausgelagert“ werden.



Folie 20

Herstellung 3-NF: Beispiel

• Beispiel:Auskunft(KNr, KursName, Klausurtermin, ProfNr, ProfName, TelefonNr)

• Relevante transitive Abhängigkeiten:{KNr} {ProfNr} {ProfName}{KNr} {ProfNr} {TelefonNr}...also: betroffene Nichtschlüsselattribute ProfName und TelefonNr sind funktional abhängig von ProfNr Auslagerung von ProfNr, ProfName, TelefonNr

• Wir erhalten:

Kurs(KNr, KursName,Klausurtermin,ProfNr)

Professor(ProfNr,ProfName,TelefonNr)



Folie 21

3-NF und Anomalien

• Beobachtungen:

• Einfügeanomalie besteht nicht mehr: über neue Professoren kann jetzt Information abgelegt werden, auch wenn diese noch keine Kurse geben.

• Löschanomalie besteht nicht mehr: Kurseinträge können gelöscht werden, ohne dass dabei Professoreninformation verloren gehen würde.

• Änderungsanomalie besteht nicht mehr: muss z.B. die Telefonnummer verändert werden, so muss das nur an einer Stelle in der DB passieren.

Kurs(KNr, KursName,Klausurtermin,ProfNr)

Professor(ProfNr,ProfName,TelefonNr)



Folie 22

Normalformen: zusammenfassende Übersicht

Nicht normalisierte Relation R

Relation in 1-NF

Relation in 2-NF

Relation in 3-NF

Anomalien durch Objektver-mischungen, mehwertige Attribute...

Anomalien durch Objektver-mischungen, atomare Attribute...

Anomalien, alle Nichtschlüssel-attribute voll funkt. abh. von jedem Schlüssel

Jedes Nichtschlüsselattribut ist direkt abhängig von jedem Schlüssel

Weitere Normalformen(weniger praxisrelevant)



Folie 23

Entwurfsregel

Ein Relationenschema ist immer so zu entwerfen, dass es genau einen Objekttyp (Entity-Typ) bzw.

genau einen Beziehungstyp beschreibt.

…und genau so geht man (hoffentlich) beim Aufbau von ER-Modellen vor. Folge: kommt man über ein ER-Modell zum Tabellenentwurf, so schützt man sich fast automatisch vor Anomalien …



Folie 24

Zusammenfassung I

• Der Normalisierungsprozess dient der Behebung von Anomalien.

• Anomalien lassen sich weitgehend vermeiden, wenn ein Relationenschema so entworfen wird, dass es genau einen Objekttyp bzw. Beziehungstyp beschreibt.

• Bei Vorliegen der Einfügeanomalie lassen sich verfügbare Daten nicht in Tabellen aufnehmen; bei der Löschanomalie wird mehr gelöscht als nötig; bei der Änderungsanomalie muss ein Datum an vielen Stellen verändert werden.

• Y ist funktional abhängig von X, wenn der X-Wert den Y-Wert eindeutig festlegt oder: für den gleichen X-Wert kann es nicht zwei unterschiedliche Y-Werte geben.



Folie 25

Zusammenfassung II• Y ist voll funktional abhängig von X, wenn Y nicht zugleich

von einem Teil von X funktional abhängig ist.• Schlüsselattribute sind Elemente irgendeines Schlüssels; die

anderen heißen Nichtschlüsselattribute.• Eine Relation ist in 1-NF, wenn alle Attributwerte atomar sind.• Eine Relation ist in 2-NF, wenn zudem jedes

Nichtschlüsselattribut voll funktional abhängig von jedem Schlüssel ist.

• Eine Relation ist in 3-NF, wenn zudem jedes Nichtschlüsselattribut direkt abhängig von jedem Schlüssel ist.

• Bei 3-NF treten die wichtigsten Anomalien nicht mehr auf.• Es gibt weitere Normalformen, die jedoch in der Praxis kaum

berücksichtigt werden, da die Relationenzerlegung zu einer Vielzahl von Relationen und i.d.R. entsprechend ineffizienter Verarbeitung führt.

Lernziele

Documents

Transcript of Lernziele