Das Entitäten-Beziehungsmodell und die Normalformen Entitäten
Werbung
Das Entitäten-Beziehungsmodell und die Normalformen Entitäten-Beziehungsmodell Erstellen einer Datenbank Wenn man eine Datenbank erstellen will, braucht es eine genaue Analyse der Situation, damit klar wird, wie die Datenbank aufgebaut werden muss. Dazu muss man die reale Welt abstrahieren, damit sie in einem Modell dargestellt werden kann. Von dem Modell sollte dann auch wieder auf die reale Welt geschlossen werden können. Bevor man sich an das Modellieren macht, muss man sich bewusst werden, welches die wichtigen Sachverhalte sind, die in der Datenbank abgebildet werden müssen. Der Aufbau einer Datenbank kann mit Hilfe eines Entitäten-Beziehungsmodells dargestellt werden. Dafür brauchen wir einige Begriffe: Entitäten Entitäten sind unterscheidbare Objekte der realen Welt, über die Informationen gespeichert werden sollen. Es können sowohl Personen, als auch Gegenstände sein. In gewissen Fällen braucht es mehrere Angaben, damit die Entitäten unterscheidbar werden. In unserem Reisebüro-Beispiel hat es zum Beispiel mehrere „Wyss“. Daher braucht es dort auch noch das Attribut „Vorname“. Damit die beiden Kunden-Entitäten unterscheidbar werden. Entitätsmengen sind Mengen von Entitäten desselben Typs. In den Einführungsbeispielen hatten wir z.B. folgende Entitätsmengen: Inseln, Häuser, Kunden, Lehrer, Klassen, Zimmer. Entitätsmengen werden immer mit Rechtecken dargestellt: 1 Entität Attribute Jede Entität kann durch Attribute (Merkmale) weiter charakterisiert werden. Attribute werden durch Ellipsen abgebildet: Entität Attribut 1 Attribut 2 Identifikationsschlüssel Zusätzlich zu den Attributen, hat jede Entität ein eindeutiger Identifikationsschlüssel (Primärschlüssel). Durch dieser wird die Entität eindeutig identifiziert. Entität Attribut 1 Attribut 2 E_ID In unserem Reisebüro-Beispiel könnten das folgende Angaben sein: Insel Inselname Besonderheiten 2 I_ID Beziehungen Verschiedene Entitäten können durch Beziehungen verknüpft werden. Auch Beziehungen können durch Attribute weiter spezifiziert werden. Beziehungen werden durch Rauten dargestellt. Entität 1 Beziehung 1ung Entität 2 Eine Beziehung kann (muss aber nicht) einen eindeutigen Identifikationsschlüssel haben. Bei einfacheren Beispielen kann es auch sein, dass Beziehungen in andere Entitäten integriert werden und keine eigenen Tabellen repräsentieren. In unserem Beispiel haben wir für die Belegung den Primärschlüssel der Kunden und den Primärschlüssel der Häuser verwendet und eine eigene Tabelle für die Belegung angelegt: K_ID H_ID Belegung 1ung Kunde Haus Woche www.saidaonline.com/en 3 Wir haben aber auch den Identifikationsschlüssel der Insel als Fremdschlüssel in die Tabelle der Häuser integriert und die Beziehung zwischen den Häusern und Inseln so abgebildet. Primärschlüssel Haus H_ID Max_Per Miete Hausname Anz_Zi Meerbl. I_ID Fremdschlüssel Assoziationstypen Um Datenbanken zu entwerfen, ist es wichtig, die Beziehungen zwischen den Entitäten genauer zu charakterisieren. Eine Assoziation legt fest, wie viele Tupel einer Tabelle zu einem Tupel einer anderen Tabelle gehören können. Es gibt verschiedene Assoziationstypen: Abkürzung 1 c m mc Assoziationstyp einfache Assoziation konditionelle Assoziation mehrfache Assoziation mehrfach-konditionelle Assoziation Anzahl der zugehörigen Tupel genau ein Tupel Kein oder genau ein Tupel Mindestens ein Tupel Beliebig viele Tupel Eine Beziehung zwischen zwei Entitäten wird wie folgt dargestellt: Abkürzung Assoziationstyp Entität 1 zu Entität 2 Abkürzung Assoziationstyp Entität 2 zu Entität 1 Entität 1 Beziehung 1ung 4 Entität 2 Beispiel 1 mc m Beziehung 1ung Kunde Haus Dies bedeutet, dass jedes Haus von keinem, einem oder mehreren Kunden belegt werden kann, jeder Kunde aber mindestens ein Haus belegt. (Würde er kein Haus belegen, wäre er kein Kunde.) Beispiel 2 1 Insel m Beziehung 1ung Haus Jedes Haus liegt auf genau einer Insel. Auf jeder Insel hat es mindestens ein Haus. (Inseln, auf welchen es kein Haus gibt, sind nicht in der „Inseltabelle“ aufgeführt.) 5 Vorgehensweise Beim Erstellen eines Entitäten-Beziehungsmodells, kann man immer nach den gleichen Schritten vorgehen: 1. Entitätsmengen definieren Die Bezeichnungen von Entitätsmengen sind meistens Nomen (keine zusammengesetzten Wörter). Die Bezeichnungen der Entitätsmengen werden in rechteckige Kästchen geschrieben. 2. Attribute der einzelnen Entitätsmengen angeben Die Attribute werden in ovale Kästchen geschrieben und mit Linien an die Entitätsmengen angehängt. 3. Beziehungsmengen festlegen Die Bezeichnungen von Beziehungsmengen sind meist Verben. Die Beziehungen werden als Rauten dargestellt. 4. Beziehungsmerkmale anhängen Jeder Beziehung müssen immer zwei Fremdschlüssel angehängt werden – nämlich die von denjenigen Entitäten, zwischen welchen die Beziehung besteht. 5. Assoziationstypen festlegen Bei jeder Beziehung werden die Assoziationstypen 1, c, m oder mc festgelegt. 6 Aufgaben Aufgabe 1 Ordne den ER-Diagrammen die richtigen Assoziationstypen zu: a) Film spielt_in 1ung Filmschauspieler Sportler gewinnt 1ung Olympiamedaille Schweizer/in besitzt 1ung Schweizer Pass b) c) d) Fluss mündet_in 1ung Meer Aufgabe 2 Erstelle ein ER-Modell (Entity-Relationship-Modell) einer Firma, die Informatikprojekte durchführt. Bestimme dazu selbst geeignete Schlüssel und Attribute und notiere die richtigen Assoziationstypen zu den Beziehungen. • Die Firma besteht aus mehreren Abteilungen und hat mehrere Mitarbeiter. Jeder Mitarbeiter gehört zu genau einer Abteilung. • An jedem Projekt arbeitet mindestens ein Mitarbeiter. Es gibt auch Mitarbeiter, die an keinem Projekt arbeiten. 7 Aufgabe 3 Eine kleine Tierhandlung will eine Datenbank einführen, um ihre gesamten betrieblichen Aktivitäten zu unterstützen. Folgende Entitätsmengen sollen abgebildet werden: • • • • • Mitarbeiter mit den Attributen Name, Gehalt und Geburtsdatum Mitarbeiterfunktion mit dem Attribut Beschreibung Kunden mit den Attributen Name, Adresse und Loyalität Tiere mit den Attributen Name, Preis, Alter Tierart mit den Attributen Herkunft und Nahrung Weiter bestehen folgende Beziehungen: Ein Tier kann genau an einen Kunden von einem Mitarbeiter verkauft werden. Ein Kunde kann keines, eines oder mehrere Tiere kaufen und ein Mitarbeiter kann eines, keines oder mehrere Tiere verkaufen. Ein Verkaufsakt enthält zudem ein Datum. Ein Mitarbeiter wird genau in einer Funktion angestellt. Erstelle ein ER-Modell zu dieser Situation und gib auch bei jeder Beziehung die Assoziationstypen an. 8 Normalformen Redundanz Ein Merkmal einer Tabelle ist redundant, wenn einelne Werte dieses Markmals innerhalb der Tabelle ohne Informationsverlust weggelassen werden können. Eine Tabelle ist redundant, wenn sie mindestens ein redundantes Merkmal hat. Normalformen Es gibt fünf Normalformen. Für die Entwicklung von Datenbanken sind jedoch die ersten drei wichtig. Diese werden wir im Folgenden genauer anschauen. Erste Normalform (1NF) Eine Tabelle ist in 1NF, falls die Wertebereiche der Merkmale atomar sind. Atomarität Atomarität eines Wertebereiches bedeutet: Es dürfen nur einfache Werte vorliegen. Zusammengesetzte Werte, Mengen, Aufzählungen, Wiederholungsgruppen etc. sind verboten. Beispiel Folgendes Beispiel zeigt, wie eine Tabelle auf 1NF gebracht werden kann: Vorname Thomas Ursula Igor Nachname Müller Meier Müller Informatikkenntnisse Java, C++, PHP PHP, Java C++ Nach der Normalisierung auf erste Normalform sieht die Tabelle folgendermassen aus: Vorname Thomas Thomas Thomas Ursula Ursula Igor Nachname Müller Müller Müller Meier Meier Müller Informatikkenntnisse Java C++ PHP PHP Java C++ 9 Diese Tabelle ist jetzt zwar in erster Normalform, dafür haben wir Redundanz, da nach einer Zeile schon klar wäre, dass Ursula zum Nachnamen Meier heisst. Zweite Normalform (2NF) Eine Tabelle ist in 2NF, falls die Tabelle in 1NF ist und falls jedes Nichtschlüsselmerkmal voll funktional abhängig vom Schlüssel ist. Funktionale Abhängigkeit Ein Merkmal B ist funktional abhängig von Merkmal A, falls gilt: Jeder Wert von A bstimmt eindeutig einen Wert in B. (Die Umkehrung muss nicht stimmen.) Beispiel: Aus jeder Mitarbeiternummer (Merkmal A) folgt eindeutig ein Name (Merkmal B). Allerdings folgt aus einem Namen nicht eindeutig eine Mitarbeiternummer (es kann ja zwei Leute mit dem gleichen Namen geben.) Volle funktionale Abhängigkeit Ein Merkmal B ist voll funktional abhängig vom Merkmalpaar (S1,S2), falls gilt: 1. B ist funktional abhängig vom Merkmalpaar (S1,S2) 2. B ist nicht funktional abhängig von S1 3. B ist nicht funktional abhängig von S2 Beispiel ID_St 1 2 Nachname Müller Meier ID_Prof 3 2 Professor Schmid Borner Note 5 4 Diese Tabelle ist nicht in 2NF, da aus den beiden Schlüsseln ID_St (S) und ID_Prof (S2) eindeutig der Professor (B) folgt. Allerdings ist auch (3.) erfüllt, da bereits aus ID_Prof (S2) eindeutig der Professor folgt. Die folgenden drei Tabellen zeigen, wie die gleichen Angaben in Tabellen gespeichert werden können, die sowohl in der ersten, als auch in der zweiten Normalform sind. ID_St 1 2 Nachname Müller Meier ID_Prof 2 3 Professor Borner Schmid 10 ID_St 1 2 ID_Prof 3 2 Note 5 4 Die zweite Normalform bringt Redundanz weg, wenn wir zwei zusammenhängende Schlüssel haben. Dritte Normalform (2NF) Eine Tabelle ist in 3NF, falls die Tabelle in 2NF ist und falls keine Merkmale transitiv abhängig vom Schlüssel sind. Transititve Abhängigkeit Seien A, B und C drei beliebige Merkmale einer Tabelle. Das Merkmal C ist transitiv abhängig vom Merkmal A, falls ein Merkmal B existiert, sodass die drei Merkmale A, B und C folgende Eigenschaften aufweisen: 1. B ist funktional abhängig von A 2. C ist funktional abhängig von B 3. A ist nicht funktional abhängig von B Beispiel Die folgende Tabelle ist nicht in der dritten Normalform. Sie stellt die Struktur einer Firma dar. M_ID ist der Primärschlüssel des Mitarbeiters. A_ID bezeichnet den Primärschlüssel der Abteilung. Die Bezeichnung ist die Bezeichnung der Abteilung. M_ID M19 M1 M7 M4 Name Schweizer Meier Huber Becker Strasse Hauptstrasse Lindenstrasse Mattenweg Wasserweg Ort Frankendorf Liestal Basel Liestal A_ID A6 A3 A5 A6 Bezeichung Finanz Informatik Personal Finanz Diese Tabelle ist nicht in dritter Normalform, da die A_ID (B) funktional abhängig von der M_ID (A) und die Bezeichnung (C) funktional abhängig von der A_ID (B) ist, aber M_ID (A) ist nicht funktional abhängig von der A_ID (B). (A6 kann entweder auf M19 oder auf M4 abgebildet werden.) Die beiden nächsten Tabellen bilden die gleichen Dinge ab, sind aber in 3NF: M_ID M19 M1 M7 M4 Name Schweizer Meier Huber Becker Strasse Hauptstrasse Lindenstrasse Mattenweg Wasserweg Ort Frankendorf Liestal Basel Liestal 11 A_ID A6 A3 A5 A6 A_ID A6 A3 A5 Bezeichung Finanz Informatik Personal Regeln Es gibt drei Regeln, welche bei der Erstelung einer Datenbank beachtet werden müssen, damit die erstellten Tabellen in den ersten drei Normalformen sind: Regel 1 Es werden keine zusammengesetzten Schlüssel verwendet. Jeder Datensatz bekommt einen eigenen Primärschlüssel. Regel 2 Ist die Beziehung zwischen zwei der Entitätsmenge 1 und der Entitäsmenge 2 einfach-komplex, so kann die Beziehung in die Tabelle der ersten Entitätsmenge integriert werden. Der Primärschlüssel S2 der Entität 2 taucht in der Tabelle 1 als Fremdschlüssel S2 auf. Beispiel: 1 Beziehung 1ung Insel Hausname Paphos Arethoussa Malia Atrium Haus Primärschlüssel der Insel als Fremdschlüssel in der Haus-Tabelle Haus-Tabelle Haus_ID 1 2 3 4 m Insel_ID 1 2 1 3 Anz_Zimmer Max_Personen Miete Meerblick 3 5 750.00 CHF ja 2 4 600.00 CHF nein 4 6 1'100.00 CHF ja 3 4 700.00 CHF ja 12 Regel 3 Ist zwischen zwei Entitätsmengen eine komplex-komplex-Beziehung, so müssen diese auf drei Tabellen aufteteilt werden. Je eine für die Entitäten, sowie eine für die Beziehung. Diese hat die beiden Primärschlüssel S1 und S2 als Fremdschlüssel in der Tabelle. Beispiel: mc m Beziehung 1ung Kunde Haus_ID 1 2 3 4 Hausname Paphos Arethoussa Malia Atrium Kunden_ID 1 2 3 4 5 6 7 8 9 Name Meier Aeby Gessner Zumsteg Wyss Keller Meyer Bernasconi Wyss Belegung_ID 1 2 3 4 5 6 7 8 9 10 11 12 13 14 Haus_ID 1 1 1 2 2 2 3 3 3 3 3 4 2 4 Insel_ID 1 2 1 3 Vorname Ursula Paul Heidi Irène Beat Thomas Sybille Mario Heidi Haus Anz_Zimmer Max_Personen Miete Meerblick 3 5 750.00 CHF ja 2 4 600.00 CHF nein 4 6 1'100.00 CHF ja 3 4 700.00 CHF ja Anrede Frau Herr Frau Frau Herr Herr Frau Herr Frau Kunden_ID 2 2 4 5 1 1 8 6 6 7 7 3 5 9 Strasse Lindenstr. 13 Rosenweg Reichengasse 11 Brückenstr. 23 Wallisellenstr. 243 Bahnhofstr. 9 Schwanenplatz 14 Zürcherstr. 328 Rosengasse Woche 28 29 31 17 31 32 24 29 30 33 34 25 41 28 13 PLZ 4051 3001 1700 3005 8050 5000 6004 9000 8047 Ort Basel Bern Fribourg Bern Zürich Aarau Luzern St. Gallen Zürich Aufgaben Aufgabe 1 Diskutiert zu zweit, welche Tabellen mit welchen Schlüsseln bei der Aufgabe mit der Tierhandlung (Seite 8) nötig sind. Aufgabe 2 Erstelle ein Entitäten-Beziehungsmodell, welches die Beziehungen zwischen • Student (Matrikelnummer, Name, Semesterzahl) • Vorlesung (Vorlesungsnummer, Titel, ECTS-Punkte, Raum) • Professor (Personalnummer, Name, Rang) • Assistent (Personalnummer, Name, Fachgebiet) darstellt. Berücksichtige dabei folgende Abhängigkeiten: • Studenten hören Vorlesungen • Vorlesungen bauen aufeinander auf, d. h. eine bestimme Vorlesung setzt eine oder mehrere andere Vorlesungen voraus. Ebenso kann eine Vorlesung die Voraussetzung für eine oder mehrere Vorlesungen sein. • Professoren lesen Vorlesungen. • Assistenten arbeiten für einen bestimmten Professor. • Ein Professor kann mehrere Assistenten haben. • Ein Professor prüft Studenten in einer oder mehreren Vorlesungen (Hinweis: Eine Assoziation kann auch mehr als 2 Entitätsmengen verbinden). • Jede Prüfung führt zu einer Note. Liste anschliessend alle Tabellen mit ihren Atributen auf, damit die Datenbank in den drei Normalfomen vorliegt. 14 … und noch ein Rätsel zu den Häuservermietungen Fragestellung Auf einer griechischen Insel wurden sieben Ferienhäuser verkauft. Die neuen Besitzer möchten die Häuser durch Hecken klar trennen. Wie kann dies durch drei gerade Hecken geschehen? http://www.lustigestories.de Quellen: • Skript von Mohamed Kubba-von Jüchen, Datenbanken • Relationale Datenbanken, Andreas Meier, Springer 2001 • Unterlagen von Prof. Dr. Andreas Meier, Universität Fribourg • http://www.gxy.ch/6efinf/db/db-erm-pvor.pdf, 2.2.2013 15
