n:1-Beziehungen in SQL (MySQL )

Bei einer n:1-Beziehung können mehrere („n“) Zeilen einer Tabelle in einer Beziehung zu einer („1“) Zeile einer anderen Tabelle stehen.

Wir hatte solch eine n:1-Beziehung bereits in einem früheren Beispiel mit Länder und Kontinenten kennengelernt: Mehrere Länder können auf einem Kontinent liegen.

n:1-Beziehung zu Nachschlagetabelle (lookup table, Schlüsselverzeichnis)

Ein Anwendungsfall einer n:1-Beziehung ist beispielsweise eine Nachschlagetabelle (lookup table), welche immer dann empfehlenswert ist, wenn für eine Spalte nur bestimmte Werte verwendet werden können sollen.

In der folgenden einfachen Tabelle finden wir beispielsweise zu jeder Person einen Wohnort angegeben. Dies könnte beispielsweise der Ort sein, in welcher die Person ihren ersten Wohnsitz hat. Wir werden im folgenden eine alternative Darstellung der in jener Tabelle enthaltenen Informationen beschreiben.

Tabelle ohne Nachschlagetabelle

PERSONEN (Tabelle)

VORNAME NACHNAME WOHNORT BUNDESLAND

Emma Schneider Berlin Berlin
Finn Weber Stuttgart Baden-Württemberg
Charlotte Wagner Stuttgart Baden-Württemberg

Wir können den Wohnort einer Person auch erfassen, indem wir in der Tabelle der Personen an Stelle des vollständigen Namens des Wohnorts einer Person nur eine Kennzahl des Wohnortes erfassen. Die Bedeutung der Kennzahl wird dann durch eine andere Tabelle gegeben: die Nachschlagetabelle.

In der Nachschlagetabelle findet sich dann zu jeder Kennzahl der Name des Wohnorts – und außerdem noch in einer separaten Spalte das Bundesland des Wohnortes.

Die Nachschlagetabelle enthält also in einer Spalte einen eindeutigen kurzen Wert (hier: die Kennzahl), der eigentliche Text kann länger sein und steht dann in einem anderen Feld (hier: das Feld »NAME«), außerdem sind in der Nachschlagetabelle noch weitere Felder mit ergänzenden Informationen möglich (wie hier: die Spalte »BUNDESLAND«)

Tabelle mit Nachschlagetabelle

PERSON (Tabelle)
VORNAME     NACHNAME    WOHNORT
Emma        Schneider   3 ----------------------------.
                                                      |
Finn        Weber       2 ------------------------.   |
                                                  |   |
Charlotte   Wagner      2 ------------------------.   |
                                                  |   |
                                                  |   |
                                                  |   |
ORT (Nachschlagetabelle)                          |   |
                                                  |   |
ORT         NAME        BUNDESLAND                |   |
                                                  |   |
1           Hamburg     Hamburg                   |   |
                                                  |   |
2           Stuttgart   Baden-Württemberg <-------'   |
                                                      |
3           Berlin      Berlin <----------------------'

4           Neustadt    Schleswig-Holstein

7           Neustadt    Niedersachsen

Wir sprechen hier von einer „n:1-Beziehung“, weil mehrere („n“) Personen einem („1“) Ort zugeordnet werden können.

Das folgende Skript erlaubt es uns, die beiden gezeigten Tabellen anzulegen.

Skript (MySQL )

DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE PERSON ( VORNAME VARCHAR ( 255 ), NACHNAME VARCHAR ( 255 ), WOHNORT VARCHAR ( 255 ));
INSERT INTO PERSON ( VORNAME, NACHNAME, WOHNORT ) VALUES ( 'Emma',      'Schneider', '3'  );
INSERT INTO PERSON ( VORNAME, NACHNAME, WOHNORT ) VALUES ( 'Finn',      'Weber',     '2'  );
INSERT INTO PERSON ( VORNAME, NACHNAME, WOHNORT ) VALUES ( 'Charlotte', 'Wagner',    '2'  );
CREATE TABLE ORT ( ORT VARCHAR ( 255 ), NAME VARCHAR ( 255 ), BUNDESLAND VARCHAR ( 255 ));
INSERT INTO ORT ( ORT, NAME, BUNDESLAND ) VALUES ( '1', 'Hamburg',   'Hamburg'            );
INSERT INTO ORT ( ORT, NAME, BUNDESLAND ) VALUES ( '2', 'Stuttgart', 'Baden-Württemberg'  );
INSERT INTO ORT ( ORT, NAME, BUNDESLAND ) VALUES ( '3', 'Berlin',    'Berlin'             );
INSERT INTO ORT ( ORT, NAME, BUNDESLAND ) VALUES ( '4', 'Neustadt',  'Schleswig-Holstein' );
INSERT INTO ORT ( ORT, NAME, BUNDESLAND ) VALUES ( '7', 'Neustadt',  'Niedersachsen'      );
SELECT * FROM PERSON;
SELECT * FROM ORT;

Wir zeigen hier die Tabelle »PERSON«.

Konsole

SELECT * FROM PERSON;

+-----------+-----------+---------+
| VORNAME   | NACHNAME  | WOHNORT |
+-----------+-----------+---------+
| Emma      | Schneider | 3       |
| Finn      | Weber     | 2       |
| Charlotte | Wagner    | 2       |
+-----------+-----------+---------+

Wir zeigen hier die Tabelle »ORT«.

Konsole

SELECT * FROM ORT;

+------+-----------+--------------------+
| ORT  | NAME      | BUNDESLAND         |
+------+-----------+--------------------+
| 1    | Hamburg   | Hamburg            |
| 2    | Stuttgart | Baden-Württemberg  |
| 3    | Berlin    | Berlin             |
| 4    | Neustadt  | Schleswig-Holstein |
| 7    | Neustadt  | Niedersachsen      |
+------+-----------+--------------------+

Wir können durch eine Äquiverbindung nun dafür sorgen, daß an jeder Zeile einer Person der Name ihres Wohnortes angefügt wird.

Konsole

SELECT * FROM PERSON, ORT WHERE WOHNORT = ORT;

+-----------+-----------+---------+------+-----------+--------------------+
| VORNAME   | NACHNAME  | WOHNORT | ORT  | NAME      | BUNDESLAND         |
+-----------+-----------+---------+------+-----------+--------------------+
| Finn      | Weber     | 2       | 2    | Stuttgart | Baden-Württemberg  |
| Charlotte | Wagner    | 2       | 2    | Stuttgart | Baden-Württemberg  |
| Emma      | Schneider | 3       | 3    | Berlin    | Berlin             |
+-----------+-----------+---------+------+-----------+--------------------+

In Abwandlung der vorigen Abfrage können wir nun noch dafür sorgen, daß im Ergebnis der Abfrage nur die drei interessierenden Spalten »VORNAME«, »NACHNAME« und »WOHNORT« angezeigt werden.

Konsole

SELECT VORNAME, NACHNAME, NAME AS WOHNORT FROM PERSON, ORT WHERE WOHNORT = ORT;

+-----------+-----------+-----------+
| VORNAME   | NACHNAME  | WOHNORT   |
+-----------+-----------+-----------+
| Finn      | Weber     | Stuttgart |
| Charlotte | Wagner    | Stuttgart |
| Emma      | Schneider | Berlin    |
+-----------+-----------+-----------+

Vorteile der Verwendung einer Nachschlagetabelle

Mehrere gleiche Texte in einer Spalte sind nicht unbedingt eine mehrfach wiederholte (redundante) Information, da ein einzelner Text alleine noch keine Information darstellt. Erst eine Beziehung zwischen mindestens zwei Spalten drückt eine Information aus. Trotzdem ist schon die Wiederholung längerer Texte alleine schlecht, beispielsweise weil

die Wiederholung längerer Text kostet mehr Speicherplatz und Rechenaufwand,
es ist schwieriger, längere Texte zu überarbeiten, wenn diese mehrfach wiederholt abgespeichert sind,
die Eingaben und Überarbeitungen mehrfacher Wiederholungen langer Texte ist fehlerträchtig.

Bei Verwendung einer Nachschlagetabelle wird zwar immer noch ein Text wiederholt, nämlich eine kurze Kennzahl, aber die Wiederholung eines kurzen Textes ist besser als die Wiederholung eines langen Textes. Falls ein Ortsname nun korrigiert werden muß, reicht es ihn an genau einer Stelle ein einziges Mal zu korrigieren, er wird nicht wiederholt.

Die Verwendung einer Nachschlagetabelle hat die folgenden möglichen Vorteile:

Mehrere Städte mit dem gleichen Namen können durch unterschiedliche Angaben in den anderen Spalten (wie in der Spalte »BUNDESLAND«) unterschieden werden. Eine Kennzahl gibt dann immer eindeutig einen bestimmten Ort an, während der Name eines Ortes nicht immer eindeutig sein muß.
Durch die separate Tabelle für Städte gibt es überhaupt erst einen Ort, an dem Zusatzinformationen zu Städten (wie deren Bundesländer) gespeichert werden können. Würde man den Wohnort einer Person in einer einzigen Tabelle in der Zeile der Person ausschreiben, so müßte man dort solche Zusatzinformationen möglicherweise auch jedesmal wiederholen, wenn der Ort in einer Zeile einer Person vorkommt.
Durch Eingabe der Kennzahl eines Ortes an Stelle des Namens des Orts wird sichergestellt, daß nur solche Städt angegeben werden können, die auch in der Nachschlagetabelle »ORT« erfaßt wurden. Dies erschwert die versehentliche Eingabe nicht existierender Städte.
Schreibfehler bei der Eingabe eines Ortsnamens werden unwahrscheinlicher, da jeder Ortsname nur einmal richtig in die Tabelle »ORT« eingegeben werden muß und nicht wiederholt in die Tabelle »PERSON«. Und selbst wenn der Name eines Ortes in der Tabelle »ORT« einmal falsch geschrieben sein sollte, dann reicht es, diesen Namen einmalig in einer einzigen Zeile zu korrigieren.

Übungsaufgaben

/ Tabelle mit Nachschlagetabelle (20')

Teilen Sie die folgende Tabelle auf zwei Tabellen auf: Eine für die Komponisten und eine für die Epochen.

In der Basistabelle der Komponisten sollen die Epochen nur noch durch Kennzahlen angegeben werden, die auf die separate Tabelle der Epochen verweisen. In der Tabelle der Epochen darf es zu jeder Epoche nur eine Zeile geben.

Dazu sollten Sie jene beiden Tabellen anlegen und anschließend die Daten richtig eintragen. Hierzu sollen die Informationen aus der untenstehenden Tabelle also in geeigneter Weise auf jene beiden Tabellen aufgeteilt werden.

Schließlich sollten Sie dann noch eine Äquiverbindungsabfrage (englisch “equijoin ”, “equi-join ” oder “equi join ”) schreiben, welche wieder die folgende Tabelle ausgibt. (Die Details der Formatierung der Ausgabe und die Reihenfolge der Zeilen ist dabei aber nicht wichtig.)

Tabelle ohne Nachschlagetabelle

KOMPONIST (Tabelle)

NAME GEBURTSJAHR EPOCHE EPOCHENANFANG

Bach 1685 Barock 1600
Mozart 1756 Klassik 1730
Beethoven 1770 Klassik 1730
Chopin 1810 Romantik 1800
Schumann 1810 Romantik 1800

Aussprachehinweis

Chopin ʃɔpɛ̃

Beispiel nicht-eindeutiger Namen von Musikstilen Ab zirka 1950 steht “Rhythm and Blues ” für einen bestimmten Musikstil. Seit den 1980er Jahren steht “(Contempory )Rhythm and Blues ” für einen anderen Musikstil.