Mehrfachverbindungen (n:m) in SQL (MySQL )

Dreifache kartesische Produkte

Hinter »FROM« dürfen beliebig viele Tabellen angegeben werden. Bisher hatten wird dort jedoch immer noch eine Tabelle oder zwei Tabellen angegeben.

Protokoll (Anfang)

WARNINGS; SET sql_mode = 'ANSI,TRADITIONAL';
DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE A ( A VARCHAR ( 255 )); 
INSERT INTO A ( A ) VALUES ( 'A' );
SELECT * FROM A;

+------+
| A    |
+------+
| A    |
+------+

CREATE TABLE B ( B VARCHAR ( 255 )); 
INSERT INTO B ( B ) VALUES ( 'B' );
SELECT * FROM A, B;

+------+------+
| A    | B    |
+------+------+
| A    | B    |
+------+------+

CREATE TABLE C ( C VARCHAR ( 255 )); 
INSERT INTO C ( C ) VALUES ( 'C' );
SELECT * FROM A, B, C;

+------+------+------+
| A    | B    | C    |
+------+------+------+
| A    | B    | C    |
+------+------+------+

Es sind also auch drei Tabellen hinter »FROM« erlaubt.

Statt eines Kommas darf auch »INNER JOIN« verwendet werden.

Protokoll (Fortsetzung)

SELECT * FROM A INNER JOIN B INNER JOIN C;

+------+------+------+
| A    | B    | C    |
+------+------+------+
| A    | B    | C    |
+------+------+------+

Wie bisher können hinter jedem »INNER JOIN« noch Verküpfungsbedingungen mit »ON« angegeben werden. Genauso sind auch andere Arten von Verbindungen, wie beispielsweise Verbindungen mit »NATURAL JOIN« möglich.

Mehrfachbeziehungen

Wir haben uns schon mit der Frage beschäftigt, wie Autoren Bücher zugeordnet werden können, wenn es vorkommen kann, das ein Autor mehrere Bücher geschrieben hat.

Jedoch haben wir dabei ignoriert, daß es auch vorkommen kann, daß ein Buch mehrere Autoren hat.

mögliche Bücher: Das Grünkern-Kochbuch von Emma Schneider und Finn Weber; Entspannen durch Radfahren von Emma Schneider; Heiteres Berufstraining für Vertriebsbeauftragte von Finn Weber; Straßenbeleuchtung im Wandel der Zeiten von Charlotte Wagner

Wir hatten das Problem mit mehreren Büchern pro Autor durch Einführung einer Fremdschlüsselspalte bei den Büchern gelöst. Wenn ein Buch nun aber mehrere Autoren haben können soll, bräuchte ein Buch mehrere Fremdschlüsselattribute, was aber eine zu vermeidendes Wiederholungsattribut wäre.

Eine übliche Lösung des Problems besteht in der Einführung einer weiteren Tabelle, die nur die Verknüpfungsinformation zwischen Autoren und Büchern enthält, einer sogenannten Beziehungstabelle.

Protokoll (Anfang)

WARNINGS; SET sql_mode = 'ANSI,TRADITIONAL';
DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE PERSON ( PERSON VARCHAR ( 255 ), VORNAME VARCHAR ( 255 ), NACHNAME VARCHAR ( 255 ), RUFNUMMER VARCHAR ( 255 )); 
INSERT INTO PERSON ( PERSON, VORNAME, NACHNAME, RUFNUMMER ) VALUES ( '1', 'Emma',      'Schneider',  '55523' );
INSERT INTO PERSON ( PERSON, VORNAME, NACHNAME, RUFNUMMER ) VALUES ( '2', 'Finn',      'Weber',      '55547' );
INSERT INTO PERSON ( PERSON, VORNAME, NACHNAME, RUFNUMMER ) VALUES ( '3', 'Charlotte', 'Wagner',     '55521' );
INSERT INTO PERSON ( PERSON, VORNAME, NACHNAME, RUFNUMMER ) VALUES ( '7', 'Andreas',   'Budde',      '55597' );
CREATE TABLE BUCH ( BUCH VARCHAR ( 255 ), TITEL VARCHAR ( 255 ), VERLAGSORT VARCHAR ( 255 )); 
INSERT INTO BUCH ( BUCH, TITEL, VERLAGSORT ) VALUES ( '1', 'Das Gruenkern-Kochbuch',                            'Muenchen'  );
INSERT INTO BUCH ( BUCH, TITEL, VERLAGSORT ) VALUES ( '4', 'Entspannen durch Radfahren',                        'Muenchen'  );
INSERT INTO BUCH ( BUCH, TITEL, VERLAGSORT ) VALUES ( '5', 'Heiteres Berufstraining fuer Vertriebsbeauftragte', 'Stuttgart' );
INSERT INTO BUCH ( BUCH, TITEL, VERLAGSORT ) VALUES ( '9', 'Strassenbeleuchtung im Wandel der Zeiten',          'Hamburg'   );
CREATE TABLE AUTOR ( PERSON VARCHAR ( 255 ), BUCH VARCHAR ( 255 )); 
INSERT INTO AUTOR ( PERSON, BUCH ) VALUES ( '1', '1' );
INSERT INTO AUTOR ( PERSON, BUCH ) VALUES ( '2', '1' );
INSERT INTO AUTOR ( PERSON, BUCH ) VALUES ( '2', '4' );
INSERT INTO AUTOR ( PERSON, BUCH ) VALUES ( '3', '9' );

Basistabelle »PERSON«

+--------+-----------+-----------+-----------+
| PERSON | VORNAME   | NACHNAME  | RUFNUMMER |
+--------+-----------+-----------+-----------+
| 1      | Emma      | Schneider | 55523     |
| 2      | Finn      | Weber     | 55547     |
| 3      | Charlotte | Wagner    | 55521     |
| 7      | Andreas   | Budde     | 55597     |
+--------+-----------+-----------+-----------+

Basistabelle »BUCH«

+------+---------------------------------------------------+------------+
| BUCH | TITEL                                             | VERLAGSORT |
+------+---------------------------------------------------+------------+
| 1    | Das Gruenkern-Kochbuch                            | Muenchen   |
| 4    | Entspannen durch Radfahren                        | Muenchen   |
| 5    | Heiteres Berufstraining fuer Vertriebsbeauftragte | Stuttgart  |
| 9    | Strassenbeleuchtung im Wandel der Zeiten          | Hamburg    |
+------+---------------------------------------------------+------------+

Basistabelle »AUTOR«

+--------+------+
| PERSON | BUCH |
+--------+------+
| 1      | 1    |
| 2      | 1    |
| 2      | 4    |
| 3      | 9    |
+--------+------+

Dreifachverbindungen

Zur „Entschlüsselung“ der Tabelle »AUTOR« kann ein zwiefache innere Verbindung hergestellt werden

Protokoll (Fortsetzung)

SELECT * FROM 
           AUTOR
INNER JOIN PERSON ON AUTOR.PERSON = PERSON.PERSON
INNER JOIN BUCH   ON AUTOR.BUCH   = BUCH.BUCH;

+--------+------+--------+-----------+-----------+-----------+------+------------------------------------------+------------+
| PERSON | BUCH | PERSON | VORNAME   | NACHNAME  | RUFNUMMER | BUCH | TITEL                                    | VERLAGSORT |
+--------+------+--------+-----------+-----------+-----------+------+------------------------------------------+------------+
| 1      | 1    | 1      | Emma      | Schneider | 55523     | 1    | Das Gruenkern-Kochbuch                   | Muenchen   |
| 2      | 1    | 2      | Finn      | Weber     | 55547     | 1    | Das Gruenkern-Kochbuch                   | Muenchen   |
| 2      | 4    | 2      | Finn      | Weber     | 55547     | 4    | Entspannen durch Radfahren               | Muenchen   |
| 3      | 9    | 3      | Charlotte | Wagner    | 55521     | 9    | Strassenbeleuchtung im Wandel der Zeiten | Hamburg    |
+--------+------+--------+-----------+-----------+-----------+------+------------------------------------------+------------+

Die Abfrage erstellt zunächst alle Kombination aller Zeilen aus »AUTOR« mit allen Zeilen aus »PERSON« und allen Zeilen aus »BUCH«. Durch die beiden Äquirestriktionsbedingungen werden dann daraus nur die Zeilen ausgewählt, bei denen »AUTOR.PERSON = PERSON.PERSON« und »AUTOR.BUCH = BUCH.BUCH« erfüllt ist.

Eine natürliche Verbindung ist etwas einfacher zu schreiben und zu lesen und verzichtet automatisch auf die oben doppelten Spalten.

Protokoll (Fortsetzung)

SELECT * FROM 
             AUTOR
NATURAL JOIN PERSON
NATURAL JOIN BUCH;

+------+--------+-----------+-----------+-----------+------------------------------------------+------------+
| BUCH | PERSON | VORNAME   | NACHNAME  | RUFNUMMER | TITEL                                    | VERLAGSORT |
+------+--------+-----------+-----------+-----------+------------------------------------------+------------+
| 1    | 1      | Emma      | Schneider | 55523     | Das Gruenkern-Kochbuch                   | Muenchen   |
| 1    | 2      | Finn      | Weber     | 55547     | Das Gruenkern-Kochbuch                   | Muenchen   |
| 4    | 2      | Finn      | Weber     | 55547     | Entspannen durch Radfahren               | Muenchen   |
| 9    | 3      | Charlotte | Wagner    | 55521     | Strassenbeleuchtung im Wandel der Zeiten | Hamburg    |
+------+--------+-----------+-----------+-----------+------------------------------------------+------------+

Man könnte sich vorstellen, daß jemand die Beziehungen zwischen Büchern und ihren Autoren in einer Basistabelle festhält, die wie das Ergebnis der letzten Abfrage aussieht. Derjenige würde dann jedoch bemerken, daß bestimmte Informationen, wie etwa die Rufnummer einer Person oder der Verlagsort eines Buches, mehrfach festgehalten sind. Um dies zu korrigieren, könnte man diese Informationen dann in zwei extra Tabellen (Nachschlagetabellen für Personen beziehungsweise Bücher) auslagern. So würde man dann schließlich auf das Prinzip der Verknüpfungstabelle kommen, die keine anderen Informationen mehr enthält, als Informationen zur Verknüpfung.

Beziehungstabellen

Eine Beziehung, bei beliebig viele Zuordnungen zwischen zwei Dingen aus zwei verschiedenen Domänen möglich sein können, nennt man auch eine n:m-Beziehung.

Regel Um eine n:m-Beziehung zu realisieren, legt man eine extra Beziehungstabelle mit zwei Fremdschlüsseln zu den beiden Teilen der Beziehung an.

Der Primärschlüssel einer Beziehungstabelle sollte normalerweise aus allen ihren Fremdschlüsselspalten bestehen (zusammengesetzter Schlüssel).

Beziehungs- und Entitätstabellen

Man unterscheidet oft zwischen Entitäts- und Beziehungstabellen:

Eine Zeile aus einer Entitätstabelle beschreibt eine Entität aus einer bestimmten Domäne.

Ein Beziehungstabelle beschreibt eine Beziehung zwischen Entitäten, falls diese nicht einfach als 1:n- oder n:1-Beziehung durch Fremdschlüssel realisiert werden kann.

Die Unterscheidung zwischen Entitäts- und Beziehungstabellen stammt von Chen (1976), während Codd skeptisch darauf hinweist, daß eine Beziehungstabelle auch wieder als Entitätstabelle interpretiert werden kann, wenn man noch weitere Eigenschaften zu ihr hinzufügt.

Datenbankgestaltung

Einige Gestaltungsregeln lauten:

jede Zelle einer Basistabelle soll nur einen Wert enthalten
In einer Basistabelle nicht mehrere Spalten (»Autor1«, »Autor2«, »Autor3«) für eine Eigenschaft anlegen
keine Redundanz in den Basistabellen
Zu jedem Entitätstyp genau eine Entitätstabelle (als Basistabelle) anlegen
Keine Informationen über Entitäten vom Typ B in einer Basistabelle speichern, die Entitäten vom Typ A beschreibt
1:n-Beziehungen oder n:1-Beziehungen mit Fremdschlüsselspalte auf der n-Seite realisieren
n:m-Beziehung mit extra Beziehungstabelle (als Basistabelle) realisieren

Die fünfte Normalform

Im allgemeinen kann man davon ausgehen, daß ein Relationenschema sich in der fünften Normalform befindet, wenn es aus Entitätstabellen und Beziehungstabellen besteht, wobei eine Entitätstabelle einen Schlüssel hat, der nur eine Spalte umfaßt, und die anderen Spalten jeweils eine Eigenschaft der durch den Schlüssel identifizierten Entität beschreiben, und die Beziehungstabelle nicht mehr als zwei Spalten hat. Viele Fälle, in denen Normalformen verletzt sind, ergeben sich nämlich erst bei Verwendung von Schlüsseln mit mehr als einer Spalte oder von Beziehungstabellen mit mehr als zwei Spalten.

Übungsaufgaben

/ Auswertung von n:m-Beziehungen

Geben Sie die Namen aller Bücher aus, an welchen Herr Finn Weber mitgeschrieben hat. (Im besten Fall sollte in der Abfrage nicht die Kennzahl von Herrn Weber vorkommen, sondern nur sein Name.) Es ist ausreichend, wenn in der Abfrage nur der Nachname verwendet wird.

Geben Sie die Namen aller Personen aus, welche am Buch »Das Gruenkern-Kochbuch« mitgeschrieben haben. (Im besten Fall sollte in der Abfrage nicht die Kennzahl des Buches »Das Gruenkern-Kochbuch« vorkommen, sondern nur der Name dieses Buches.)

/ Mehrfache Selbstverbindungen (ungerichteter Graph) *

Die folgende Tabelle eines sozialen Netzwerkes gibt an, zwischen welchen Personen eine eingetragene Bekanntschaft besteht. (Hierbei wird durch die Tabelle eine „Relation“ auch im anschaulichen Sinne des Wortes dargestellt.) Dabei werden Bekanntschaften immer nur in der Reihenfolge vom alphabetisch ersten Namen zum alphabetisch zweiten Namen eingetragen, gelten aber auch immer in der anderen Richtung: Wenn Alfred mit Fritz bekannt ist, so ist also auch Fritz mit Alfred bekannt, auch wenn letzteres nicht extra eingetragen wird.

.-------------------------------------------------------------.
| PERSON   | PMAIL             | BEKANNTE | BMAIL             |
|----------+--------------------------------------------------|
| Alfred   | alf42@example.com | Fritz    | fritz@example.com |
| Alfred   | alf42@example.com | Helene   | helen@example.com |
| Alfred   | alf42@example.com | Ilse     | ilse2@example.com |
| Alfred   | alf42@example.com | Klara    | klara@example.com |
| Alfred   | alf42@example.com | Lotte    | lotte@example.com |
| Berta    | berta@example.com | Helene   | helen@example.com |
| Berta    | berta@example.com | Klara    | klara@example.com |
| Fritz    | fritz@example.com | Klara    | klara@example.com |
| Fritz    | fritz@example.com | Wilhelm  | willy@example.com |
| Hans     | hansi@example.com | Lotte    | lotte@example.com |
| Hans     | hansi@example.com | Wilhelm  | willy@example.com |
| Helene   | helen@example.com | Oskar    | oskar@example.com |
| Ilse     | ilse2@example.com | Klara    | klara@example.com |
| Klara    | klara@example.com | Wilhelm  | willy@example.com |
| Oskar    | oskar@example.com | Wilhelm  | willy@example.com |
'-------------------------------------------------------------'

Normalisieren Sie diese Informationen, indem Sie sie auf mehrere Tabellen aufteilen, so daß möglichst wenig Informationen mehrfach abgespeichert werden.

Erstellen Sie ein SQL-Skript, um diese Tabellen anzulegen.

Erstellen Sie dann ein View, welches daraus wieder die obige Tabelle rekonstruiert.