Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- q2:das_relationenmodell [2017/07/14 09:47] – admin03
+++ q2:das_relationenmodell [2017/07/14 18:54] (aktuell) – admin03
@@ Zeile 1: / Zeile 1: @@
-====== Beziehungen ======
+===== Relationenmodell =====
+==== Abbildung eines ER-Diagramms auf Relationen ====
+Im letzten Theorieteil wurde das ER-Modell als Hilfsmittel zum Erstellen einer Abstraktion der Realität vorgestellt und an Beispielen erläutert. Leider lassen sich so erstellte Konzepte nicht so ohne weiteres auf einem Rechner implementieren. Die klassischen DBMS-Systeme, die als Software auf Computern zur Verfügung stehen, beruhen alle auf Modellen, deren Ge¬stal-tungsmöglichkeit gegenüber dem ER-Modell weiter eingeschränkt ist. Man unter¬scheidet zwischen dem hierarchischen, dem Netzwerk- und dem Relationalen Modell. Das mit Abstand wichtigste ist das Relationale, bei uns kurz als R-Modell bezeichnet, deshalb soll es hier als einziges vorgestellt werden. es geht zurück auf Arbeiten von E.F. Codd Anfang der 70er Jahre. Auch ACCESS verwendet dieses Datenmodell (ebenso wie Dbase, MySql, Interbase, DB2, Oracle usw.), über die beiden anderen Konzepte kann man in der einschlägigen Literatur nachlesen. Im Moment werden neue Datenmodelle debattiert, die Schwächen dieser klassischen Modelle vermeiden und in ihrer Reichhaltigkeit das ER-Modell erreichen. Das wichtigste ist das soge-nannte Objektorientierte Modell, von dem es auch erste Realisierungen als DBMS gibt.
-Zwischen den einzelnen Entity-Typen können Beziehungen (relationships) bestehen. Auch diese Beziehungen können Attribute besitzen. Als Bsp. betrachten wir die Beziehung „besucht“ zwischen den Typen „Schüler“ und „Kurs“, die „Note“ als Attribut hat.
+Eine Relationale Datenbank besteht aus einer oder mehreren Tabellen (zweidimensionalen Feldern in der Sprache der Informatik), wobei man die Spalten meist als Attribute oder Feldbezeichner tituliert, die Zeilen als Datensatz oder Entity. Die Namen lassen schon deutlich werden, dass es eine enge Beziehung zwischen ER- und R-Modell gibt. So würde der Entity-Typ „Kurs“ im großen Bsp. des letzten Theoriekapitels durch folgende Tabelle erfasst:
-{{ :q2:erschuelerbesuchtkurs.png?600 |}}
+^Kurs-Nummer^Kurs-Thema^Jahrgangsstufe^Typ^
+|11|	Mechanik I|E1	|GK|
+|12|	Mechanik I|E1	|GK|
+|3|	Datenbanken|Q2 |GK|
+|25|	Short Stories|Q1| LK|
+Dabei wird einschränkend vorausgesetzt, dass jeder Eintrag für ein Attribut elementar ist, d.h. nicht aus Teilattributen zusammengesetzt ist und auch höchstens einen Wert umfassen kann. Sogenannte Nullwerte, d.h. kein zugeordneter Wert für einen Eintrag, sind möglich. Ein Bsp. wäre das Attribut Telefon-Nr bei einer Tabelle für Personendaten. Ein Leerlassen (Nulleintrag) bedeutet, dass die entsprechende Person kein Telefon hat oder die Nummer geheim bleiben soll. Tabellen, bei denen die Einträge elementar sind, werden als in 1. Normalform befindlich bezeichnet. Hier erkennt man sofort eine Einschränkung gegenüber dem ER-Modell, denn das Attribut Vorher besuchte Schuler des Entity-Typs Schüler kann so nicht in eine Tabelle umgesetzt werden, es sei denn, jeder Schüler hätte vorher nur eine einzige Schule besucht. Auch zusam-mengesetzte Attribute wie Adresse beim Typ Schüler kann man so nicht erfassen. Das letzte Problem lässt sich allerdings leicht lösen. Man nimmt einfach für jedes Teilattribut wie Postleitzahl, Wohnort, Straße mit Haus-Nr. eine eigene Spalte.
-Allerdings sind die zweiwertigen Beziehungen die weitaus wichtigsten. Bei ihnen unterscheidet man zwischen drei Komplexitäten:
+Jedes Attribut in einer Tabelle besitzt einen bestimmten vorbestimmten Wertebereich, z.B. Zeichenkette mit maximal 80 Zeichen oder Ganzzahl zwischen 0 und 255. Diese Wertebereiche werden oft als „domains“ bezeichnet.
-<WRAP round info>
+Wie im ER-Modell definiert man Schlüssel. Ein Attribut oder eine Kombination von Attributen ist dann ein Schlüssel, wenn es keine zwei Entities in der Tabelle mit denselben Werten der Kombination gibt. Einer davon wird als Primärschlüssel ausgewählt. Mit Hilfe solcher Schlüssel werden Beziehungen zwischen verschiedenen Tabellen hergestellt. So könnten wir unsere Tabelle für Kurse um das Attribut Lehrer-Nr. er¬weitern, das einen Bezug zur Tabelle Lehrer schafft, die so aussehen könnte.
-(1)	1:1-Beziehungen: jedes Entity vom Typ E1 steht höchstens mit einem Entity vom Typ E2 in Beziehung und umgekehrt.
-Schulbsp.: Schüler - erhält - Abiturzeugnis. Jeder Schüler erhält (höchstens) ein Abiturzeugnis, und jedes Abiturzeugnis gehört eindeutig zu einem Schüler.
+^Lehrer-Nr.	^Name	^Vorname	^Titel^
+|421	|Müller|	Manfred	|StR|
+|665	|Maier	|Marlene	|OStR|
+Damit haben wir gleichzeitig die 1:n - Beziehung Lehrer - unterrichtet - Kurs im ER-Modell er¬fasst. Man bezeichnet die Lehrer-Nr in der Kurs-Tabelle als Fremdschlüssel.
+Jetzt findet sich auch eine Lösung für das Problem mit dem Mehrfachattribut Vorher besuchte Schule. Es muss eine eigene Tabelle für Schulen erstellt werden, die eine Beziehung über Schüler-Nr. zur Tabelle LSchüler schafft.
+^Schüler-Nr.	^Schule^
+|421	|Grundschule Fuldatal|
+|421	|Gesamtschule Geistal|
+|665	|Integrierte Gesamtschule Obersuhl|
+|665	|Gesamtschule Obersberg|
+Einziger Schlüssel für diese Tabelle ist die Kombination Lehrer-Nr. - Fach.
+Hier kann auch eine Lösung sinnvoll sein, die eine Tabelle Schule ohne Schülernummern verwendet, insbesondere, wenn man noch mehr Attribute über die Schulen speichern will und eine Beziehung zu den Schülern mit einer Zwischentabelle herstellt, wie es weiter unten erläutert wird.
+Man erkennt, dass sich das ER-Bsp. in das R-Modell umsetzen lässt. Wir haben schon Mehrfach- und zusammengesetzte Attribute erledigt und 1:n - Beziehungen eingebaut (damit ist natürlich auch der Fall 1:1 erfasst). Es bleibt noch die Frage, wie man n:m - Beziehungen transformiert. Dazu betrachten wir im Bsp. die relationship besucht zwischen Schüler und Kurs. Weder die Hinzunahme eines Attributs Kurs-Nr. zu Schüler löst das Problem, denn ein Schüler besucht meist mehr als einen Kurs, noch die Hinzunahme der Schüler-Nr. zur Tabelle Kurs, da jeder Kurs von mehreren Schülern besucht wird. Es muss eine dritte Tabelle Kursbelegung her in der Form
+^Schüler-Nr.	^Kurs-Nr.^	Note	^Fehlstunden^
+|7	|12|	12	|0|
+|8	|11	|4	|14|
+|9	|11	|9	|2|
+Wie man sieht, lassen sich hier auch ohne Probleme die Attribute der Beziehung unterbringen. Schlüssel in dieser Tabelle ist die Kombination Schüler - Kurs-Nr.. Man spricht häufig von einer Zwischentabelle.
+Wir versuchen jetzt, die allgemeinen Prinzipien einer Umsetzung vom ER-Modell ins R-Modell anzugeben:
+=====1 Grundregel: Jeder Entity-Typ wird eine eigene Tabelle.=====
+<WRAP box round>
+Entitätstypen werden als Relationen dargestellt, bei der die Attribute in Klammern aufgezählt werden.
+Die Attribute des Primärschlüssels werden __unterstrichen__.
 </WRAP>
-<WRAP round info>
-(2)	1:n-Beziehungen: jedes Entity vom Typ E2 steht höchstens mit einem Entity 	vom Typ E1 in Beziehung, es können aber mehrere aus E2 zum selben Entity 	aus E1 eine Beziehung haben.
+<WRAP example round>
-Schulbsp.: Lehrer - ist Tutor von - Schüler. Jeder Schüler hat nur einen Tutor, aber jeder Lehrer, der Tutor ist, hat mehrere Schüler in seiner Tutorengruppe.
+Beispiele für Relationen
-n:1-Beziehungen sind natürlich ganz analog definiert.
+<WRAP pre>
+Schüler(__Schülernummer__, Name, Vorname, StrasseNr, PLZOrt, Geburtsdatum)\\
+Kurs(__Kursnummer__, Thema, Art, Halbjahr)
 </WRAP>
-<WRAP round info>
-(3)	n:m-Beziehungen: jedes Entity aus E1 kann zu mehreren aus E2 eine 	Beziehung haben, und jedes Entity aus E2 zu mehreren aus E1.
-Schulbsp.: Schüler - besucht - Kurs. Jeder Schüler besucht mehrere Kurse, und Kurse werden von mehreren Schülern besucht.
 </WRAP>
-In der grafischen Realisierung des ER-Modells werden die Komplexitäten an die Linien, die die Beziehung darstellen, geschrieben.
-Die Entscheidung über die Kardinalität einer Richtung der Beziehung wird über die sogenannte **KaMe-Frage**
+In relationalen Datenbanksystemen wie z.B. MySQL, Access oder Paradox werden auch Beziehungen auf Relationen abgebildet. Dabei gilt folgende
-getroffen.
+===== Grundregel 2 für Beziehungstypen =====
 <WRAP box round>
-==KaMe-Frage==
+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. 1:1 - und 1:n - Beziehungen lassen sich allerdings durch zusätzliche Attribute in einer Tabelle erfassen. Dabei wird der Schlüssel der 1-Tabelle ein zusätzliches Attribut in der n-Tabelle. (vgl: Optimierung)
+</WRAP>
+<WRAP example round>
+Beispiel
-**Kann eine** Entität des Typs A **mit mehreren** Entitäten des Typs B in Beziehung stehen?\\
+Die ER-Beziehung //Schüler belegt Kurs// wird auf die Relation
-  Ja    →  Kardinalität ist n
+<WRAP pre>
-  Nein  →  Kardinalität ist 1
+Belegt(↑__Schülernummer__, ↑__Kursnummer__, Punkte) abgebildet.
+</WRAP>
 </WRAP>
-Die Komplexität lässt sich noch weiter spezifizieren, wenn man bei den Beziehungen nicht nur Höchstgrenzen, sondern auch Mindestgrenzen angibt. Wir betrachten dazu die 1:n-Beziehungen. Hier war verlangt, dass jedes Entity aus E2mit höchsten einem Entity aus E1 in Beziehung steht. Man kann sich natürlich auch fragen, ob jedes Entity mit genau einem Entity aus E1 in Beziehung steht. Im Beispiel bedeutet, dass jeder Schüler einen Tutor haben muss.
+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.
-Manchmal wird diese Unterscheidung durch die Angabe von Intervallen gekennzeichnet, d.h. statt der 1 wird das Intervall [0,1] bzw. [1] angegeben.
+==== Optimierungen für 1:n-Beziehungen ====
-Man spricht auch von Kardinalitäten (für 1:1, n:1, usw) und von Optionalitäten
+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.
-Auch bei Attributen wird diese Unterscheidung manchmal durchgeführt. So könnte bei Adressen die Angabe von Straße, PLZ und Ort zwingend vorgeschrieben sein, die Angabe einer Telefonnummer jedoch optional sein. Man kennzeichnet solche optionalen Attribute manchmal mit einem Kreis an die Verbindungslinie zwischen Entity und Attribut.
-====== Mehrfache Beziehungen ======
+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.
+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.
-Beziehungen werden grafisch durch Rauten dargestellt. Zwischen Entity-Typen kann es mehr als eine Beziehung geben, wie das folgende Bsp. zeigt.
+===== Optimierungsregel 1 für obligatorische 1:n-Beziehungen =====
+<WRAP box round>
+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.
+</WRAP>
-{{ :q2:erschuelerwohntinort.png?400 |}}
+Die beiden Grundregeln liefern diese drei Relationen:
+<WRAP pre>
+Schüler(__SNr__, Nachname, Vorname, StraßeNr, PLZ, Ort)\\
+Tutor(__Kürzel__, Nachname, Vorname, Dienstbezeichnung)\\
+hatTutor(↑__SNr__, ↑Kürzel)
+ </WRAP>
-Eine Beziehung kann auch zwischen mehr als Typen bestehen. Wir betrachten die Schulstunde als Entity. Das kann durchaus sinnvoll sein, damit Attribute wie Anfangs- und Endzeit gespeichert werden können, dann ergibt sich der Stundenplan als eine Dreierbeziehung zwischen Stunde, Raum und Kurs.
+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.
+<WRAP pre>
+Schüler(__SNr__, Nachname, Vorname, StraßeNr, PLZ, Ort, ↑Kürzel)\\
+Tutor(__Kürzel__, Nachname, Vorname, Dienstbezeichnung)
+</WRAP>
-{{ :q2:erstundekursraum.png?600 |}}
+Beim zweiten Beispiel treten Nullwerte auf, wenn man die Beziehungsrelation in die Relation der n-Seite integriert.
-Ein Entitytyp kann auch mehrfach an einer Beziehung teilnehmen. Man spricht dann von Rekursion.
+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:
-{{ :q2:errekursion.png?400 |}}
+===== Optimierungsregel 2 für optionale 1:n-Beziehungen =====
+<WRAP box round>
+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.
+</WRAP>
+===== Optimierungen für 1:1-Beziehungen =====
+===== Das Spind-Problem =====
+Wir betrachten die ER-Beziehung //Schüler hat Spind//.
-====== Optionalität von Beziehungen======
+{{SchuelerHatSpind.gif}}
-Die Komplexität lässt sich noch weiter spezifieren, wenn man bei den Beziehungen nicht nur Höchstgrenzen, sondern auch Mindestgrenzen angibt. Wir betrachten dazu die 1:n-Be-ziehungen. Hier war verlangt, dass jedes Entity aus E2mit höchsten einem Entity aus E1 in Beziehung steht. Man kann sich natürlich auch fragen, ob jedes Entity mit genau einem Entity aus E1 in Beziehung steht. Im Beispiel bedeutet, dass jeder Schüler einen Tutor haben muss.Mit der **Optionalität** einer Beziehung wird also ausgedrückt, ob eine Entität eine Beziehung eingehen kann oder muss, ob diese Beziehung also **optional** oder **obligatorisch** ist. Wie bei der Kardinaliät muss die Frage nach der Optionalität für beide Beziehungstyprichtungen geklärt werden.
-**MuMi-Frage**
+Nach der Grundregel wird dieses ER-Diagramm auf drei Relationen abgebildet:
+<WRAP pre>
+Schüler(__SNummer__, Name, Vorname)\\
+hat(↑__SNummer__, ↑Nummer)\\
+Spind(__Nummer__, Standort, Größe)
+</WRAP>
-Die Entscheidung über die Optionalität einer Richtung der Beziehung wird über die sogenannte **MuMi-Frage**
+Lässt sich das Relationenmodell vereinfachen? Wir betrachten dazu vier unterschiedliche Fälle.
-getroffen.
+**Fall A: Spinde an der Modellschuleschule**
+An der Modellschuleschule 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:
+<WRAP pre>
+Schüler(__SNummer__, Name, Vorname)\\
+Spind(__Nummer__, Standort, Größe, ↑SNummer)
+</WRAP>
+**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:
+<WRAP pre>
+Schüler(__SNummer__, Name, Vorname, ↑Nummer)\\
+Spind(__Nummer__, Standort, Größe)
+</WRAP>
+**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:
+<WRAP pre>
+Rekrut(__SNummer__, Name, Vorname, Nummer, Standort, Größe)
+</WRAP>
+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 =====
 <WRAP box round>
-**Muss eine** Entität des Typs A mit **mindestens einer** Entität des Typs B in Beziehung stehen?
+Eine ER-Beziehung der Kardinalität 1:1 kann auf **eine** Relation abgebildet werden, wenn sie auf
-  Ja     →   nicht optional, obligatorisch, muss-Beziehung
+beiden Seiten obligatorisch ist. Ist sie nur auf einer Seite obligatorisch so kann die aus der
-  Nein   →   optional, nicht obligatorisch, kann-Beziehung
+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.
 </WRAP>
+====Namenskonflikte====
+Wird zwecks Optimierung ein Primärschlüssel der Relation R<sub>1</sub> als Fremdschlüssel in die Relation R<sub>2</sub> aufgenommen, so tritt ein Namenskonflikt auf, wenn der Fremdschlüssel den gleichen Namen
+wie ein Attribut der Relation R<sub>2</sub> hat.
-Manchmal wird diese Unterscheidung durch die Angabe von Intervallen gekennzeichnet, d.h. statt der 1 wird das Intervall [0,1] bzw. [1] angegeben.
+Im Beispiel Schulamt kommen diese beiden Relationen vor:
-Man spricht auch von Kardinalitäten (für 1:1, n:1, usw) und von Optionalitäten
-Auch bei Attributen wird diese Unterscheidung manchmal durchgeführt. So könnte bei Adressen die Angabe von Straße, PLZ und Ort zwingend vorgeschrieben sein, die Angabe einer Telefonnummer jedoch optional sein. Man kennzeichnet solche optionalen Attribute manchmal mit einem Kreis an die Verbindungslinie zwischen Entity und Attribut.
+<WRAP pre>
+Schule(__Name__, PLZ, Landkreis, Straße, Hausnummer, Telefonnummer, Schüleranzahl)
+Klasse(__Name__, Schülerzahl)
+</WRAP>
+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.
+<WRAP pre>
+Schule(__Name__, PLZ, Landkreis, Straße, Hausnummer, Telefonnummer, Schüleranzahl)
+Klasse(__Name__, Schülerzahl, ↑Name)  <- Namenskonflikt
+</WRAP>
+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.