Wiki zum Unterricht

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
====== Relationales Modell ======

Im relationalen Modell werden aus dem ER-Diagramm die konkreten Tabellen abgeleitet. Dabei müssen Redundanzen, Anomalien und Inkonsistenzen vermieden werden. Deshalb müssen beim Ableiten von Tabellen einige Regeln eingehalten werden.

===== Begriffe =====

Bevor wir zu den einzelnen Regeln kommen, benötigen wir zu den bisher bekannten Begriffen noch einen weiteren Begriff:

<WRAP box round>
  * Ein **Fremdschlüssel** ist ein Attribut in einer Tabelle, welches in einer anderen Tabelle Primärschlüssel ist.
</WRAP>
===== Darstellung der Tabellen =====

Unsere Tabellen werden wir in folgender Form aufschreiben:

$\text{Tabellenname}\left( \underline{\text{Primärschlüssel}}, \text{Atribut1, Attribut2, . . .}, \overline{\text{Fremdschlüssel}}\right)$

Zunächst wird der Tabellenname geschrieben. Danach folgen in Klammern die Attribute, die die Spaltenüberschriften der Tabelle bilden. Der Primärschlüssel wird einfach unterstrichen. Sollten mehrere Attribute den Primärschlüssel bilden so werden diese **zusammen** unterstrichen. Fremdchlüssel werden überstrichen. Ein Attribut kann auch gleichzeitig Primär und Fremdschlüssel sein. Dann wird es über und unterstrichen.

===== Regeln =====
Die Regeln für das Bilden von Tabellen sind nicht immer eindeutig. Zunächst sollen hier alle möglichen Regeln vorgestellt werden. Anschließend werden einige praktikable Regeln zusammengefasst, an die wir uns in Zukunft halten wollen. 
==== Regeln für Entitätsmengen ====

Jede Entitätsmenge bildet eine eigene Tabelle in der die Attribute als Spaltennamen vorkommen. Der Primärschlüssel wird einfach unterstrichen. Eventuell können (Siehe unten!) in der Tabelle noch Fremdschlüssel vorkommen. Diese werden überstrichen.

==== Regeln für Relationen ====

Theoretisch kann man für jeden Relationstyp eine Tabelle erstellen, die die Primärschlüssel der beiden verbundenen Entitätsmengen als als Fremdschlüssel enthält. Damit entstehen aber meist mehr Tabellen, als erforderlich sind. Andererseits können bei zu starker Optimierung der Tabellenanzahl sehr unübersichtliche Tabellen entstehen. 

=== 1 zu 1 - Relation ===

{{ :datenbanken:relational:bild1-1-1.png?direct&600 |}}

Bei einer 1-1-Relation ohne Attribut gibt es maximal drei Möglichkeiten zum Ableiten der Tabellen:

  * Bei der ersten Möglichkeit bilden die beiden Entitätsmengen  jeweils eine Tabelle, die die Attribute und den jeweiligen Primärschlüssel enthalten. Für die die Relation wird eine neue Tabelle gebildet, die die beiden Primärschlüssel als Fremdschlüssel enthält. Die Kombination aus den den beiden Fremdschlüsseln bildet den Primärschlüssel, alternativ kann ein eigener Primärschlüssel eingeführt werden. Damit ergeben sich die folgenden Tabellen: \\ \\ $\text{E1}\left(\underline{\text{P1}}\text{, A1-1, A1-2}\right)$ \\ $\text{E2}\left(\underline{\text{P2}}\text{, A2-1, A2-2}\right)$ \\ $\text{R}\left(\underline{\overline{\text{P1}},\overline{\text{P2}}}\right)$ oder $\text{R}\left(\underline{\text{PR}},\overline{\text{P1}},\overline{\text{P2}}\right)$ \\ \\
  * Bei der zweiten Möglichkeit erscheint der Primärschlüssel der einen Entitätsmenge als Fremdschlüssel in der Tabelle der anderen Entitätsmenge. Somit erspart man sich eine Tabelle: \\ \\ $\text{E1}\left(\underline{\text{P1}}\text{, A1-1, A1-2, }\overline{\text{P2}}\right)$ \\ $\text{E2}\left(\underline{\text{P2}}\text{, A2-1, A2-2}\right)$ \\ \\ oder \\ \\ $\text{E1}\left(\underline{\text{P1}}\text{, A1-1, A1-2}\right)$ \\ $\text{E2}\left(\underline{\text{P2}}\text{, A2-1, A2-2, }\overline{\text{P1}}\right)$ \\ \\
  * Die dritte Möglichkeit funktioniert nur, wenn die 1-1-Relation die einzige Relation zwischen den beiden Entitätsmengen ist. In diesem Fall können die beiden Entitätsmengen in einer Tabelle zusammengefasst werden. Einer der beiden Primärschlüssel wird dabei zu einem normalen Attribut und kann eventuell auch weggelassen werden. \\ \\ $\text{E1E1}\left(\underline{\text{P1}}\text{, A1-1, A1-2, }\text{[P2]}\text{, A2-1, A2-2}\right)$

Zusätzlich kann es jetzt noch sein, dass die 1-1-Relation ein Attribut besitzt:

{{ :datenbanken:relational:bild2-1-1.png?direct&600 |}}

Dieses Attribut wird in den drei Fällen wie folgt bearbeitet:

  * Im ersten Fall erscheint das Attribut in der Relationstabelle:  \\ \\ $\text{E1}\left(\underline{\text{P1}}\text{, A1-1, A1-2}\right)$ \\ $\text{E2}\left(\underline{\text{P2}}\text{, A2-1, A2-2}\right)$ \\ $\text{R}\left(\underline{\overline{\text{P1}},\overline{\text{P2}}},\text{AR}\right)$ oder $\text{R}\left(\underline{\text{PR}},\overline{\text{P1}},\overline{\text{P2}},\text{AR}\right)$ \\ \\
  * Im zweiten Fall erscheint das Attribut in der Tabelle mit dem Fremdschlüssel: \\ \\ $\text{E1}\left(\underline{\text{P1}}\text{, A1-1, A1-2, }\overline{\text{P2}},\text{AR}\right)$ \\ $\text{E2}\left(\underline{\text{P2}}\text{, A2-1, A2-2}\right)$ \\ \\ oder \\ \\ $\text{E1}\left(\underline{\text{P1}}\text{, A1-1, A1-2}\right)$ \\ $\text{E2}\left(\underline{\text{P2}}\text{, A2-1, A2-2, }\overline{\text{P1}},\text{AR}\right)$ \\ \\
  * Im dritten Fall erscheint das Attribut mit in der gemeinsamen Tabelle: \\ \\ $\text{E1E1}\left(\underline{\text{P1}}\text{, A1-1, A1-2, }\text{[P2]}\text{, A2-1, A2-2},\text{AR}\right)$

=== n zu 1 - Relation ===

{{ :datenbanken:relational:bild1-n-1.png?direct&600 |}}

Bei einer n-1-Relation gibt es zwei Möglichkeiten zum Ableiten der Tabellen:

  * Bei der ersten Möglichkeit bilden die beiden Entitätsmengen  jeweils eine Tabelle, die die Attribute und den jeweiligen Primärschlüssel enthalten. Für die die Relation wird eine neue Tabelle gebildet, die die beiden Primärschlüssel als Fremdschlüssel enthält. Die Kombination aus den den beiden Fremdschlüsseln bildet den Primärschlüssel, alternativ kann ein eigener Primärschlüssel eingeführt werden. Damit ergeben sich die folgenden Tabellen: \\ \\ $\text{E1}\left(\underline{\text{P1}}\text{, A1-1, A1-2}\right)$ \\ $\text{E2}\left(\underline{\text{P2}}\text{, A2-1, A2-2}\right)$ \\ $\text{R}\left(\underline{\overline{\text{P1}},\overline{\text{P2}}}\right)$ oder $\text{R}\left(\underline{\text{PR}},\overline{\text{P1}},\overline{\text{P2}}\right)$ \\ \\
  * Bei der zweiten Möglichkeit erscheint der Primärschlüssel der Seite 1 als Fremdschlüssel in der Tabelle der Seite n. Somit erspart man sich eine Tabelle: \\ \\ $\text{E1}\left(\underline{\text{P1}}\text{, A1-1, A1-2, }\overline{\text{P2}}\right)$ \\ $\text{E2}\left(\underline{\text{P2}}\text{, A2-1, A2-2}\right)$ \\ \\ 

Zusätzlich kann es jetzt noch sein, dass die n-1-Relation ein Attribut besitzt:

{{ :datenbanken:relational:bild2-n-1.png?direct&600 |}}

Dieses Attribut wird in den beiden Fällen wie folgt bearbeitet:

  * Im ersten Fall erscheint das Attribut in der Relationstabelle:  \\ \\ $\text{E1}\left(\underline{\text{P1}}\text{, A1-1, A1-2}\right)$ \\ $\text{E2}\left(\underline{\text{P2}}\text{, A2-1, A2-2}\right)$ \\ $\text{R}\left(\underline{\overline{\text{P1}},\overline{\text{P2}}},\text{AR}\right)$ oder $\text{R}\left(\underline{\text{PR}},\overline{\text{P1}},\overline{\text{P2}},\text{AR}\right)$ \\ \\
  * Im zweiten Fall erscheint das Attribut in der Tabelle mit dem Fremdschlüssel: \\ \\ $\text{E1}\left(\underline{\text{P1}}\text{, A1-1, A1-2, }\overline{\text{P2}},\text{AR}\right)$ \\ $\text{E2}\left(\underline{\text{P2}}\text{, A2-1, A2-2}\right)$ \\ \\ 

=== n zu m - Relation === 

{{ :datenbanken:relational:bild1-n-m.png?direct&600 |}}

Bei einer n-m-Relation ist eine zusätzliche Tabelle unbedingt erforderlich, da es sonst zu Redundanzen kommt. Deshalb bleibt beim Ableiten der Tabellen nur eine Möglichkeit:
 
  * Bei dieser Möglichkeit bilden die beiden Entitätsmengen  jeweils eine Tabelle, die die Attribute und den jeweiligen Primärschlüssel enthalten. Für die die Relation wird eine neue Tabelle gebildet, die die beiden Primärschlüssel als Fremdschlüssel enthält. Die Kombination aus den den beiden Fremdschlüsseln bildet den Primärschlüssel, alternativ kann ein eigener Primärschlüssel eingeführt werden. Damit ergeben sich die folgenden Tabellen: \\ \\ $\text{E1}\left(\underline{\text{P1}}\text{, A1-1, A1-2}\right)$ \\ $\text{E2}\left(\underline{\text{P2}}\text{, A2-1, A2-2}\right)$ \\ $\text{R}\left(\underline{\overline{\text{P1}},\overline{\text{P2}}}\right)$ oder $\text{R}\left(\underline{\text{PR}},\overline{\text{P1}},\overline{\text{P2}}\right)$ \\ \\

Zusätzlich kann es jetzt noch sein, dass die n-m-Relation ein Attribut besitzt:

{{ :datenbanken:relational:bild2-n-m.png?direct&600 |}}

Dieses Attribut wird wie folgt bearbeitet:

  * Das Attribut in der Relationstabelle:  \\ \\ $\text{E1}\left(\underline{\text{P1}}\text{, A1-1, A1-2}\right)$ \\ $\text{E2}\left(\underline{\text{P2}}\text{, A2-1, A2-2}\right)$ \\ $\text{R}\left(\underline{\overline{\text{P1}},\overline{\text{P2}}},\text{AR}\right)$ oder $\text{R}\left(\underline{\text{PR}},\overline{\text{P1}},\overline{\text{P2}},\text{AR}\right)$ \\ \\

==== Zusammenfassung der wichtigsten Regeln ====

Damit wir den Überblick nicht verlieren, wollen wir in Zukunft die folgenden Regeln beim Ableiten der Tabellen verwenden:


<WRAP box round>
  - Jede **Entitätsmenge** bildet eine eigene Tabelle in der die Attribute als Spaltennamen vorkommen. Der Primärschlüssel wird einfach unterstrichen. Eventuell können (Siehe unten!) in der Tabelle noch Fremdschlüssel vorkommen. Diese werden überstrichen.
  - Bei einer **1-1-Relation** erscheint der Primärschlüssel der einen Entitätsmenge als Fremdschlüssel in der Tabelle der anderen Entitätsmenge. Wenn die Relation zusätzlich ein Attribut (mehrere Attribute) enthält, so erscheint dieses (oder diese) in der Tabelle mit dem Fremdschlüssel.  
  - Bei einer **n-1-Relation** erscheint der Primärschlüssel der Seite 1 als Fremdschlüssel in der Tabelle der Seite n. Wenn die Relation zusätzlich ein Attribut (mehrere Attribute) enthält, so erscheint dieses (oder diese) in der Tabelle der Seite n.  
  - Bei einer **n-m-Relation**  wird eine zusätzliche Tabelle gebildet, die die beiden Primärschlüssel als Fremdschlüssel enthält. Die Kombination aus den den beiden Fremdschlüsseln bildet den Primärschlüssel, alternativ kann ein eigener Primärschlüssel eingeführt werden. Wenn die Relation zusätzlich ein Attribut (mehrere Attribute) enthält, so erscheint dieses (oder diese) in der neuen Tabelle.
</WRAP>

===== Beispiele zu relationalen Modellierung =====

Als Beispiele zum Ableiten der Tabellen sollen die Lösungen zu den Modellierungsaufgaben aus dem letzten Abschnitt dienen.

**Beispiel 1**

{{ :datenbanken:relational:er-beispiel1.png?direct&600 |}}

Im ersten Beispiel gibt es die Entitätsmengen Museum und Kunstwerk. Dazu schreiben wir nach Regel 1 die entsprechenden Tabellen auf und lassen die Klammern noch offen:

$\text{Museum}\left(\underline{\text{name}},\text{adresse, fläche }\right.$ \\
$\text{Kunstwerk}\left(\underline{\text{kennziffer}},\text{künstler, entstehungsjahr}\right.$ \\

Zwischen Museum und Kunstwerk besteht die Relation "befindet sich", eine n-1-Relation mit Attribut. Nach Regel 3 erscheint der Primärschlüssel der Seite 1 als Fremdschlüssel in der Tabelle der Seite n. Das Attribut erscheint auch als Attribut in der Tabelle der Seite n. Da es keine weiteren Relationen gibt, können die Klammern der Tabellen jetzt geschlossen werden.

$\text{Museum}\left(\underline{\text{name}},\text{adresse, fläche }\right)$ \\
$\text{Kunstwerk}\left(\underline{\text{kennziffer}},\text{künstler, entstehungsjahr},\overline{\text{name}}, \text{seit}\right)$ \\ \\

**Beispiel 2**

{{ :datenbanken:relational:er-beispiel2.png?direct&600 |}}

In diesem ER-Diagramm treten die Entitätsmengen Schüler, Kurs und Lehrer auf. Wir bilden wieder nach Regel 1 die entsprechenden Tabellen und lassen diese offen:

$\text{Schüler}\left(\underline{\text{schnr}},\text{stufe },\text{name }\right.$ \\
$\text{Kurs}\left(\underline{\text{kursnr}},\text{fach, art, bezeichnung }\right.$ \\
$\text{Lehrer}\left(\underline{\text{kürzel}},\text{name, fächer }\right.$ \\

Zwischen Kurs und Schüler besteht die Relation "belegt". Hier handelt es sich um eine n-m-Relation mit Attribut, also kommt Regel 4 zur Anwendung. Wir bilden also eine neue Tabelle, die die beiden Primärschlüssel als Fremdschlüssel, und das Attribut der Relation enthält. Die Kombination aus beiden Fremdschlüsseln bildet den Primärschlüssel der Tabelle "belegt". Alternativ könnte man einen Extra-Primärschlüssel (belnr) einführen.

$\text{Schüler}\left(\underline{\text{schnr}},\text{stufe },\text{name }\right.$ \\
$\text{Kurs}\left(\underline{\text{kursnr}},\text{fach, art, bezeichnung }\right.$ \\
$\text{Lehrer}\left(\underline{\text{kürzel}},\text{name, fächer }\right.$ \\
$\text{belegt}\left(\underline{\overline{\text{schnr}},\overline{\text{kursnr}}},\text{noten }\right)$ oder $\text{belegt}\left(\underline{\text{belnr}},\overline{\text{schnr}},\overline{\text{kursnr}},\text{noten }\right)$ \\

Zwischen Schüler und Lehrer besteht die n-1-Relation "ist Tutor". Hier kommt Regel 3 zur Anwendung und der Primärschlüssel der Tabelle Lehrer erscheint als Fremdschlüssel in der Tabelle Schüler. Da alle mit der Tabelle Schüler verbundenen Relationen bearbeitet sind, kann diese jetzt geschlossen werden.

$\text{Schüler}\left(\underline{\text{schnr}},\text{stufe},\text{name }, \overline{\text{kürzel}}\right)$ \\
$\text{Kurs}\left(\underline{\text{kursnr}},\text{fach, art, bezeichnung }\right.$ \\
$\text{Lehrer}\left(\underline{\text{kürzel}},\text{name, fächer }\right.$ \\
$\text{belegt}\left(\underline{\overline{\text{schnr}},\overline{\text{kursnr}}},\text{noten }\right)$ oder $\text{belegt}\left(\underline{\text{belnr}},\overline{\text{schnr}},\overline{\text{kursnr}},\text{noten }\right)$ \\


Zwischen Lehrer und Kurs besteht die 1-n-Relation "unterrichtet". Auch hier kommt Regel 3 zur Anwendung und der Primärschlüssel der Tabelle Lehrer erscheint als Fremdschlüssel in der Tabelle Kurs. Damit sind alle Relationen bearbeitet und die restlichen Tabellen können geschlossen werden. Wir sind also fertig.


$\text{Schüler}\left(\underline{\text{schnr}},\text{stufe},\text{name }, \overline{\text{kürzel}}\right)$ \\
$\text{Kurs}\left(\underline{\text{kursnr}},\text{fach, art, bezeichnung},  \overline{\text{kürzel}}\right)$ \\
$\text{Lehrer}\left(\underline{\text{kürzel}},\text{name, fächer }\right)$ \\
$\text{belegt}\left(\underline{\overline{\text{schnr}},\overline{\text{kursnr}}},\text{noten }\right)$ oder $\text{belegt}\left(\underline{\text{belnr}},\overline{\text{schnr}},\overline{\text{kursnr}},\text{noten }\right)$ \\

===== Erklärvideo =====

Im folgenden Video werden die Inhalte dieser Seite nochmal erklärt.

{{youtube>zB4ZBbQDMDU}}


\\
===== Aufgaben =====

Überführe die folgenden ER-Diagramme in das relationale Modell (Leite die Tabellen ab!)

**Aufgabe 1**

{{ :datenbanken:relational:loesung-er-1.png?direct&600 |}}

**Aufgabe 2**

{{ :datenbanken:relational:loesung-er-2.png?direct&600 |}}

[[datenbanken:relational:loesungtabellen|>> Lösungen]]


[[datenbanken:sql|>> Auswertung der Datenbasis - SQL]]