===== Relationenmodell ===== ==== Abbildung eines ER-Diagramms auf Relationen ==== Das ER-Diagramm stellt den Datenbankentwurf grafisch anschaulich dar, als Mensch kann man den Entwurf gut überblicken. Der Computer kommt mit dieser Form nicht so gut zurecht. Er hat seine Daten lieber in Schrift- als in Grafikform. Für die maschinelle Aufbereitung eines ER-Diagramms setzen wir die Entitäts- und Beziehungstypen deshalb in Schriftform um. ===== Grundregel 1 für Entitätstypen ===== Entitätstypen werden als Relationen dargestellt, bei der die Attribute in Klammern aufgezählt werden. Die Attribute des Primärschlüssels werden __unterstrichen__. Beispiele für Relationen Schüler(__Schülernummer__, Name, Vorname, StrasseNr, PLZOrt, Geburtsdatum)\\ Kurs(__Kursnummer__, Thema, Art, Halbjahr) In relationalen Datenbanksystemen wie z.B. MySQL, Access oder Paradox werden auch Beziehungen auf Relationen abgebildet. Dabei gilt folgende ===== Grundregel 2 für Beziehungstypen ===== Jede ER-Beziehung wird auf eine Relation abgebildet. Die Relation besteht aus den Primärschlüsseln der beteiligten Entitätstypen sowie den Attributen der Beziehung. Beispiel Die ER-Beziehung //Schüler belegt Kurs// wird auf die Relation Belegt(↑__Schülernummer__, ↑__Kursnummer__, Punkte) abgebildet. Der Primärschlüssel dieser Relation besteht aus den beiden Primärschlüsseln der Entitätstypen. Im Falle von 1:n bzw. 1:1-Beziehungen genügt ein Primärschlüssel als Primärschlüssel der Relation. Bei 1:n-Beziehungen ist dies der Primärschlüssel der n-Seite. Die Primärschlüssel Schülernummer und Kursnummer sind nur zusammen genommen Primärschlüssel für die Relation //belegt//. Jeder der beiden Primärschlüssel ist für sich genommen ein sogenannter //Fremdschlüssel//, also ein Schlüssel in einer fremden Relation. Fremdschlüssel kennzeichnen wir mit einem Pfeil ↑, der dem Fremdschlüsselattribut voran gestellt wird. ==== Optimierungen für 1:n-Beziehungen ==== Die Grundregel kann immer angewendet werden. Sie liefert unabhängig von der Kardinalität der Beziehung stets drei Relationen bei der Abbildung einer ER-Beziehung: je eine für die beiden beteiligten Entitätstypen und eine für die Beziehung. Es gibt viele Fälle, in denen man mit weniger Relationen auskommt. Diese Fälle können bei 1:1 und 1:n-Beziehungen auftreten, aber nicht bei n:m-Beziehungen. Anhand von zwei Aufgaben betrachten wir Möglichkeiten Relationen und damit Tabellen beim Abbilden von ER-Beziehungen einzusparen. **Aufgabe 1**\\ Jeder Oberstufenschüler hat einen Lehrer als Tutor. \\ a) Stelle die Tutor-Beziehung zwischen Schüler und Lehrer als ER-Diagramm dar. \\ b) Bilde gemäß der Grundregel auf Relationen ab. \\ c) Veranschauliche die Relationen mit ausgefüllten Tabellen.\\ d) Ermittle die Optionalität der Beziehung.\\ e) Reduziere die Verteilung der gesamten Information auf zwei Tabellen.\\ f) Vergleiche mit Aufgabe 2.\\ [[Lösung von Aufgabe 1]] **Aufgabe 2**\\ Bücher können von Schülern ausgeliehen werden.\\ a) Stelle die hat-ausgeliehen-Beziehung zwischen Schüler und Buch als ER-Diagramm dar. \\ b) Bilde gemäß der Grundregel auf Relationen ab. \\ c) Veranschauliche die Relationen mit ausgefüllten Tabellen.\\ d) Ermittle die Optionalität der Beziehung.\\ e) Reduziere die Verteilung der gesamten Information auf zwei Tabellen.\\ f) Vergleiche mit Aufgabe 1.\\ [[Lösung von Aufgabe 2]] Die beiden 1:n-Beziehungen unterscheiden sich hinsichtlich der Optionalität: jeder Schüler hat einen Tutor, aber nicht jedes Buch muss ausgeliehen sein. Im ersten Fall handelt es sich um eine **obligatorische** (muss) Beziehung, im zweiten um eine **optionale** (kann) Beziehung. Bei einer obligatorischen Beziehung gibt es keine sonderlichen Probleme, wenn drei Relationen zu zwei Relationen zusammengefasst werden. ===== Optimierungsregel 1 für obligatorische 1:n-Beziehungen ===== Eine auf der n-Seite obligatorische ER-Beziehung der Kardinalität 1:n kann auf zwei Relationen abgebildet werden. Die Relation der n-Seite wird dazu um den Primärschlüssel der 1-Seite ergänzt. Die beiden Grundregeln liefern diese drei Relationen: Schüler(__SNr__, Nachname, Vorname, StraßeNr, PLZ, Ort)\\ Tutor(__Kürzel__, Nachname, Vorname, Dienstbezeichnung)\\ hatTutor(↑__SNr__, ↑Kürzel) Zur Optimierung integrieren wir den Primärschlüssel //Kürzel// der 1-Seite als Fremdschlüssel in die n-Seite. Dadurch wird eine Realtion eingespart. Schüler(__SNr__, Nachname, Vorname, StraßeNr, PLZ, Ort, ↑Kürzel)\\ Tutor(__Kürzel__, Nachname, Vorname, Dienstbezeichnung) Beim zweiten Beispiel treten Nullwerte auf, wenn man die Beziehungsrelation in die Relation der n-Seite integriert. Bei einem nicht ausgeliehenden Buch bleibt der Fremdschlüssel //Schülernummer// leer. Man spricht in der Fachsprache von einem //Nullwert//. Das Attribut hat dann den Wert //NULL//. Sind viele Bücher da und nur wenige Bücher ausgeliehen, wird unnötig viel Speicherplatz für den zusätzlichen Fremdschlüssel verbraucht. Daher benutzen wir die folgende Regel: ===== Optimierungsregel 2 für optionale 1:n-Beziehungen ===== Eine auf der n-Seite optionale ER-Beziehung der Kardinalität 1:n wird auf zwei Relationen abgebildet, wenn die meisten Entitäten der n-Seite mit einer Entität der 1-Seite in Beziehung stehen. Ansonsten wird die ER-Beziehung gemäß den beiden Grundregeln auf drei Relationen abgebildet. ===== Optimierungen für 1:1-Beziehungen ===== ===== Das Spind-Problem ===== Wir betrachten die ER-Beziehung //Schüler hat Spind//. {{SchuelerHatSpind.gif}} Nach der Grundregel wird dieses ER-Diagramm auf drei Relationen abgebildet: Schüler(__SNummer__, Name, Vorname)\\ hat(↑__SNummer__, ↑Nummer)\\ Spind(__Nummer__, Standort, Größe) Lässt sich das Relationenmodell vereinfachen? Wir betrachten dazu vier unterschiedliche Fälle. **Fall A: Spinde an der Lichtenbergschule** An der Lichtenbergschule können Spinde von Schülern gemietet werden. Die Vertragsfirma hat so viele Spinde aufgestellt, wie Mietverträge abgeschlossen wurden. Jeder Spind gehört also zu einem Schüler, aber nicht jeder Schüler hat einen Spind. Die Beziehung ist also auf der Spind-Seite obligatorisch, weswegen die hat-Beziehung in die Spind-Seite integriert werden kann, ohne dass Nullwerte auftreten. Es reichen zwei Relationen aus: Schüler(__SNummer__, Name, Vorname)\\ Spind(__Nummer__, Standort, Größe, ↑SNummer) **Fall B: Spinde an einer amerikanischen Schule** An einer amerikanischen Schule hat jeder Schüler einen Spind, wegen der ständigen Fluktuation in der Schülerpopulation gibt es einige Spinde in Reserve. Jeder Schüler hat einen Spind, aber nicht jeder Spind ist genutzt. Die Beziehung ist also auf der Schüler-Seite obligatorisch und auf der Spind-Seite optional. Die hat-Beziehung kann in die Schüler-Relation integriert werden, ohne dass Nullwerte auftreten. Es reichen zwei Relationen aus: Schüler(__SNummer__, Name, Vorname, ↑Nummer)\\ Spind(__Nummer__, Standort, Größe) **Fall C: Spinde beim Bund** In einer Kaserne werden die Zimmer mit Rekruten voll belegt. Für jeden Rekruten gibt es genau einen Spind auf dem Zimmer. Die Rekrut-hat-Spind-Beziehung ist also auf beiden Seiten obligatorisch. In diesem Fall kommt man mit einer einzigen Relation aus: Rekrut(__SNummer__, Name, Vorname, Nummer, Standort, Größe) Allerdings entspricht diese Relation nicht der [[Normalformen#3. Normalform|3. Normalform]]. **Fall D: Spinde im Schwimmbad** Gehen Schüler im Sommer ins Schwimmbad, so können sie einen Spind belegen. Bei kühler Witterung sind sicherlich nicht alle Spinde belegt. Die Schüler-Spind-Beziehung ist also auf beiden Seiten optional. Obwohl eine 1:1-Beziehung vorliegt, wird in diesem Fall nur dann eine Reduktion auf zwei Relationen vorgenommen, wenn die meisten Spinde belegt sind. ===== Regel für 1:1-Beziehungen ===== Eine ER-Beziehung der Kardinalität 1:1 kann auf **eine** Relation abgebildet werden, wenn sie auf beiden Seiten obligatorisch ist. Ist sie nur auf einer Seite obligatorisch so kann die aus der Grundregel kommende Beziehungsrelation in die Relation der obligatorischen Seite integriert werden. Ist die Beziehung auf beiden Seiten optional, so wird eine Reduktion auf zwei Relationen nur dann durchgeführt, wenn die meisten Entitäten der einen Seite mit der der anderen Seite verbunden sind. ====Namenskonflikte==== Wird zwecks Optimierung ein Primärschlüssel der Relation R1 als Fremdschlüssel in die Relation R2 aufgenommen, so tritt ein Namenskonflikt auf, wenn der Fremdschlüssel den gleichen Namen wie ein Attribut der Relation R2 hat. Im Beispiel Schulamt kommen diese beiden Relationen vor: Schule(__Name__, PLZ, Landkreis, Straße, Hausnummer, Telefonnummer, Schüleranzahl) Klasse(__Name__, Schülerzahl) Die 1:n-Beziehung //Klasse gehört zu Schule// wird durch Hinzunahme des Primärschlüssels Name in die Relation Klasse abgebildet, wodurch der Namenskonflikt bei den Name-Attributen entsteht. Schule(__Name__, PLZ, Landkreis, Straße, Hausnummer, Telefonnummer, Schüleranzahl) Klasse(__Name__, Schülerzahl, ↑Name) <- Namenskonflikt Ein Namenskonflikt wird dadurch aufgelöst, dass man den Fremdschlüssel geeignet umbenennt, am besten durch Hinzunahme des Namens der Relation. Im Beispiel nennt man demgemäß den Fremdschlüssel Name in //Schulname// um. Schule(__Name__, PLZ, Landkreis, Straße, Hausnummer, Telefonnummer, Schüleranzahl) Klasse(__Name__, Schülerzahl, ↑Schulname) <- Namenskonflikt aufgelöst ====Aufgaben==== Nachfolgend sind drei ER-Diagramme gegeben. {{ERArztpraxis.png|ER-Diagramm Arztpraxis}}\\ {{ERFussball.png|ER-Diagramm Fußball}}\\ {{ERUnterricht.png|ER-Diagramm Unterricht}} sowie ein {{ER2Relationen.odt|Dokument mit den drei ER-Diagrammen}} zum direkten Bearbeiten a) Überführe die drei ER-Diagramme mit den beiden Grundregeln in nicht optimierte Relationenmodelle.\\ b) Optimiere die drei Relationenmodelle aus a).