1. Startseite
2. Datenbanken SQL
3. Tabellen in Beziehungen

Tabellen in Beziehungen

Die Datenbanken können nicht nur Informationen bereitstellen, die wir gespeichert haben, sondern auch Beziehungen zwischen Tabellen erstellen.

Zur Demonstration erstellen wir eine neue Datenbank mit mehreren Tabellen, die wir in Beziehungen zueinander stellen.

Unterschiedliche Beziehungen

Tabellen und Ihre Informationen stehen in unterschiedlichen Verhältnissen zueinander. Betrachten wir nur mal das Verhältnis zwischen Schlüssel und Schloss, bei dem ein Schlüssel in mehrere Schlösser passen kann oder ein Schloss auch mehrere Schlüssel hat. Bei Datenbanken unterscheiden wir zwischen den folgenden 3 Arten.

1:1-Beziehung

Jeder Datensatz in einer Tabelle ist genau einem anderen Datensatz einer anderen Tabelle zugeordnet und umgekehrt.

Jeder Einkauf hat eine Rechnung, und jede Rechnung gehört zu einem Einkauf.

1:n-Beziehung

Jeder Datensatz in einer Tabelle ist einem anderen Datensatz einer anderen Tabelle zugeordnet, umgekehrt kann ein Datensatz aber beliebig viele Datensätze der anderen Tabelle zugeordnet sein.

Jedes Kind hat eine Mutter, eine Mutter kann aber mehrere Kinder haben.

m:n-Beziehung

Es werden beliebig viele Datensatz in einer Tabelle an beliebig viele anderen Datensatz einer anderen Tabelle zugeordnet. Umgekehrt werden ebenfalls beliebig viele Datensätze einer Tabelle an beliebig viele andere Datensätze einer anderen Tabelle zugeordnet.

Ein Schauspieler kann in mehrere Filme auftreten und ein Film kann mit mehreren Schauspielern besetzt sein.

Datenbank erstellen

Um eine neue Datenbank zu erstellen, müssen Administratorrechte vorliegen. Beim Erstellen der Datenbank wird neben den Namen auch der Zeichensatz festgelegt, mit dem gearbeitet wird.

CREATE DATABASE flughafen
DEFAULT CHARACTER SET utf8mb4;

1:1-Beziehungen zwischen den Tabellen

Bevor irgendwelche Beziehungen zwischen den Informationsfeldern der Tabellen hergestellt werden, ist es immer ratsam, einen Plan zu haben, nach dem die Beziehungen zueinander erstellt werden sollen.

Für eine Übersicht in komplexen Strukturen bietet es sich an, auf die "UML-Notation" zurückzugreifen. UML steht für "Unified Modeling Language" was übersetzt so viel wie vereinheitlichte Modellierungssprache bedeutet. Je größer und komplexer solche aufbauten werden, umso wichtiger sind visuelle Unterstützungen, um den Überblick zu behalten.

Tabellen erstellen

Nachdem der Plan steht, können die Tabellen angelegt werden. Wir beginnen mit der Tabelle, die einen Fluggast beschreiben soll.

CREATE TABLE fluggast (
 gast_id TINYINT PRIMARY KEY NOT NULL,
 name VARCHAR(50),
 reiseziel VARCHAR(50)
);

Die Tabelle "fluggast" verweist nicht auf andere Tabellen, daher werden hier nur der "PRIMARY KEY" und eine "NOT NULL"-definition angegeben.

Nun folgt die Tabelle für die Ticketverwaltung.

CREATE TABLE ticket (
 ticket_id TINYINT PRIMARY KEY NOT NULL,
 reiseklasse TINYINT,
 fluggesellschaft VARCHAR(50),
 fk_gast_id TINYINT,
 CONSTRAINT fk_fluggast -- Name der Einschränkung
  FOREIGN KEY (fk_gast_id) -- Spalte nimmt Fremdschlüssel auf
   REFERENCES fluggast (gast_id), -- Verweis auf Fremdschlüsselspalte
 CONSTRAINT uq_fk_gast_id
  UNIQUE (fk_gast_id)
);

Nach dem Schlüsselwort "CONSTRAINT" (Einschränkung) geben wir den Namen der Einschränkung an. Danach folgt das Schlüsselwort "FOREIGN KEY", mit dem wir die Spalte der Tabelle übergeben, die ein Fremdschlüssel aufnehmen soll. Es folgt das Schlüsselwort "REFERENCES", mit dem wir auf die Tabelle und Spalte verweisen, auf die die Fremdschlüsselspalte verweisen soll. Damit haben wir eine 1:1-Beziehung zwischen 2 Tabellen erstellt.

Übermitteln wir ein paar Daten in die Tabellen, um die Beziehung zu Überprüfung.

INSERT INTO fluggast VALUES (1, 'Jan', 'Malediven');
INSERT INTO fluggast VALUES (2, 'Anita', 'Kreta');
INSERT INTO fluggast VALUES (3, 'Clara', 'Florida');

INSERT INTO ticket VALUES (11, 1, 'ABC Fly', 1);
INSERT INTO ticket VALUES (22, 2, 'FixInDieFerien', 2);
INSERT INTO ticket VALUES (33, 2, 'Air Auszeit', 3);

Als Nächstes prüfen wir, ob die Beziehung hergestellt wurde. Dafür erstellen wir eine "SELECT"-Abfrage, bei der wir uns die Informationen aus den beiden Tabellen "fluggast" und "ticket" ausgeben lassen.

SELECT name, reiseziel, fluggesellschaft FROM fluggast, ticket WHERE gast_id=fk_gast_id;

Durch die "WHERE"-Klausel "gast_id=fk_gast_id" werden uns nur die Datensätze ausgegeben, die in einer 1:1-Beziehung zueinanderstehen.

Sollte die "WHERE"-Klausel nicht angegeben werden, so wird die Datenbank alle Datensätze der Tabelle "fluggast" und alle Datensätze der Tabelle "ticket" kombinieren und insgesamt 3*3 also 9 Zeilen ausgeben.

1:n-Beziehungen zwischen den Tabellen

Die nächste Beziehung, die wir erstellen wollen, beschreibt eine 1:n-Beziehung, in der ein Fluggast eine beliebige Anzahl an Gepäckstücken haben kann, aber jedes Gepäckstück einem Fluggast zugewiesen ist. Dafür beschreiben wir zunächst die Tabelle "gepaeck".

CREATE TABLE gepaeck (
 gepaeck_id TINYINT PRIMARY KEY NOT NULL,
 typ VARCHAR(50),
 volumen SMALLINT,
 fk_gast_gepaeck_id TINYINT,
 CONSTRAINT fk_gepaeck_fluggast -- Name der Einschränkung
  FOREIGN KEY (fk_gast_gepaeck_id) -- Spalte nimmt Fremdschlüssel auf
   REFERENCES fluggast (gast_id) -- Verweis auf Fremdschlüsselspalte
);

Jetzt brauchen wir wieder ein paar Daten.

INSERT INTO gepaeck VALUES (1, 'Koffer', 50, 1);
INSERT INTO gepaeck VALUES (2, 'Reisetasche', 40, 1);
INSERT INTO gepaeck VALUES (3, 'Koffer', 60, 2);
INSERT INTO gepaeck VALUES (4, 'Rucksack', 15, 3);
INSERT INTO gepaeck VALUES (5, 'Reisetasche', 35, 3);

Um die Beziehung zwischen Gepäckstück und Fluggast auszuwerten, reicht eine "SELECT"-Anfrage zu den beiden Tabellen "fluggast" und "gepaeck", bei denen wieder eine "WHERE"-Klausel dafür sorgt, nur die Daten auszugeben, die in Beziehung zueinanderstehen.

SELECT name, reiseziel, typ, volumen FROM fluggast, gepaeck WHERE gast_id=fk_gast_gepaeck_id;

Wenn wir die Beziehungen zwischen Fluggast, Ticket und Gepäckstücke ausgeben lassen wollen, müssen wir die Anfrage mehr einschränken. Die Anfrage selber ist etwas länger, weil die Beziehungen jetzt über 3 Tabellen geprüft werden.

SELECT name, reiseziel, fluggesellschaft, typ, volumen FROM fluggast, ticket, gepaeck WHERE fk_gast_id=fk_gast_gepaeck_id AND gast_id=fk_gast_id;

m:n-Beziehungen zwischen den Tabellen

Um eine m:n-Beziehung aufzubauen, verwenden wir eine Schlüsseltabelle, in der wir die Schlüsselwerte zusammenfügen und eindeutig ausgeben lassen können. Als Beispiel wollen wir uns ausgeben lassen, wie die Fluggäste zum Flughafen gekommen sind. Dafür erstellen wir die Tabelle "befoerderungsmittel" und die Schlüsseltabelle "anreise".

CREATE TABLE befoerderungsmittel (
 bef_id TINYINT PRIMARY KEY NOT NULL,
 fahrzeug VARCHAR(50)
);

Als Nächstes erstellen wir die Schlüsseltabelle, die jeweils die Primärschlüssel der zusammengehörigen Tabellen beinhalten soll.

CREATE TABLE anreise (
 fk_gast_id TINYINT NOT NULL,
 fk_bef_id TINYINT NOT NULL,
 PRIMARY KEY (fk_gast_id, fk_bef_id),
 CONSTRAINT fk_anreise_zum_flug -- Name der Einschränkung
  FOREIGN KEY (fk_gast_id) -- Spalte nimmt Fremdschlüssel auf
   REFERENCES fluggast (gast_id), -- Verweis auf Fremdschlüsselspalte
CONSTRAINT fk_befoerderung -- Name der Einschränkung
  FOREIGN KEY (fk_bef_id) -- Spalte nimmt Fremdschlüssel auf
   REFERENCES befoerderungsmittel (bef_id) -- Verweis auf Fremdschlüsselspalte
);

Es folgen wieder ein paar Daten für die Tabellen.

INSERT INTO befoerderungsmittel VALUES (1, 'BUS');
INSERT INTO befoerderungsmittel VALUES (2, 'Zug');
INSERT INTO befoerderungsmittel VALUES (3, 'Taxi');
INSERT INTO befoerderungsmittel VALUES (4, 'PKW');
INSERT INTO befoerderungsmittel VALUES (5, 'Fahrrad');

INSERT INTO anreise VALUES (1, 1);
INSERT INTO anreise VALUES (2, 3);
INSERT INTO anreise VALUES (3, 2);
INSERT INTO anreise VALUES (3, 4);
INSERT INTO anreise VALUES (3, 5);
INSERT INTO anreise VALUES (1, 3);
INSERT INTO anreise VALUES (2, 2);
INSERT INTO anreise VALUES (3, 1);
INSERT INTO anreise VALUES (2, 4);
INSERT INTO anreise VALUES (1, 5);

Auch hier prüfen wir wieder die Beziehungen der Tabellen untereinander.

SELECT name, fahrzeug FROM fluggast, anreise, befoerderungsmittel WHERE gast_id=fk_gast_id AND fk_bef_id=bef_id;