Datenbanken, Teil 4: Eins-zu-Eins-Beziehungen | Spontan•wild•und•Kuchen

Teil 2 der Serie über Datenbanken, in dem es um das Entity-Relationship-Modell ging, liegt nun schon ungefähr 2 Jahre zurück, aber relationale Datenbanken haben natürlich nichts von ihrer Aktualität eingebüßt und in der Zwischenzeit haben wir in unserem Podcast darüber berichtet. Bisher haben wir 1:N- und N:M-Beziehungen betrachtet. In diesem Teil geht es weiter, denn mit den Beziehungen ist es manchmal kompliziert.

Dieser vierte Teil der Serie setzt Kenntnisse der Grundbegriffe des E/R-Modells und der Umsetzung von 1:N- und N:M-Beziehungen ins relationale Modell voraus. In den ersten drei Teilen der Serie wurden diese erklärt.

Beispiele für 1:1-Beziehungstypen; Quelle;
CC-BY-SA 2.5 Maximilian Dörrbecker

Für 1:N- und N:M-Beziehungen wurde im vorherigen Teil bereits diskutiert, wie die Umsetzung ins relationale Modell erfolgt. 1:N-Beziehungen können am einfachsten mit einer Fremdschlüsselspalte auf der N-Seite umgesetzt werden während für N:M-Beziehungen immer eine Beziehungstabelle notwendig ist.

1:1-Beziehungen

Eine weitere Beziehungsart zwischen zwei Entitätstypen ist die 1:1-Beziehung. Betrachten wir zunächst als Beispiel die Beziehung zwischen einer Person und ihrem Ausweis.

Eine Person besitzt nur einen Ausweis und ein Ausweis gehört zu einer Person. Wir gehen in dem Beispiel weiterhin davon aus, dass jede Person in der Datenbank auch einen Ausweis besitzt und es nur personalisierte Ausweise gibt.

Tatsächlich gibt es bei 1:1-Beziehungen mehrere Möglichkeiten, wie sie ins relationale Modell umgesetzt werden können. Betrachten wir zunächst die Varianten, die wir schon kennen und versuchen, ob die Beziehung mit einem Fremdschlüssel auf einer der beiden Seiten umgesetzt werden kann.

Es spricht erst einmal nichts dagegen, in einer Tabelle „Person“, die wir auf Basis des Entitätstyps „Person“ erstellen, als Fremdschlüsselspalte die Nummer des Ausweises zu setzen. Da eine Person in unserem Szenario ja nur einen Ausweis haben kann, reicht ein einzelner Eintrag in einer Fremdschlüsselspalte aus. Da ein umgekehrt Ausweis auch nur einer Person zugeordnet sein kann, muss verhindert werden, dass in der Tabelle Person eine Ausweisnummer mehrfach aufritt. Wir kennen bereits aus den vorherigen Beiträgen, dass der Wert einer Primärschlüsselspalte für die Tabelle eindeutig sein muss. Es besteht aber auch die Möglichkeit, andere Spalten als den Primärschlüssel so zu kennzeichnen, dass Werte nur einmal auftreten dürfen. Das SQL-Schlüsselwort dafür lautet UNIQUE.

Eine Fremdschlüsselspalte in der Tabelle Ausweis, die auf Basis des Entitätstyps Ausweis erstellt wurde, wäre genau so möglich. Im Gegensatz zu einer 1:N-Beziehung, bei deren Umsetzung die Fremdschlüsselspalte stets auf der N-Seite erscheint, können wir uns also bei dieser 1:1-Beziehung zunächst einmal aussuchen, auf welcher Seite wir eine Fremdschlüsselspalte anlegen.

Betrachten wir das vorliegende Beispiel genauer, stellen wir fest, dass sich kein Attribut des Entitätstyps Person als Primärschlüssel eignet. In diesem Fall würde man normalerweise einen künstlichen Primärschlüssel anlegen, wie auch in Teil 2 beschrieben. Im aktuellen Beispiel allerdings gibt es noch eine andere Option. Die Nummer des Ausweises eignet sich nämlich auch als eindeutiger Schlüssel für die Person. In diesem Fall hätten die Tabellen Person und Ausweis den selben Primärschlüssel. Da zudem einer Person genau ein Ausweis und jeder Ausweis genau einer Person zugeordnet ist, besteht auch die Möglichkeit, nur eine einzige, gemeinsame Tabelle für Personen und Ausweise zu erstellen. Die Spalten dieser Tabelle ergeben sich dann aus den Attributen beider an der Beziehung beteiligten Entitätstypen, im Beispiel ergeben sich also die Spalten Nummer (als Primärschlüssel), Name und Datum.

Betrachten wir nun als ein anderes, leicht abweichendes Beispiel die Beziehung zwischen einer Person und ihrem Führerschein. In diesem Beispiel gehen wir nun davon aus, dass, anders als beim Ausweis, nicht jede Person in der Datenbank einen Führerschein haben muss.

Die Führerscheinnummer eignet sich in diesem Beispiel nicht als Schlüssel für die Person, weil ja nicht jede Person einen Führerschein hat. Eine Umsetzung mit zwei Tabellen und einem künstlichen Schlüssel für die Person, der als Fremdschlüssel bei Führerschein verwendet wird, funktioniert. Auf diese Art sichergestellt, dass jeder Führerschein einer Person zugeordnet wird. Wird wieder UNIQUE (s.o.) verwendet, ist auch sichergestellt, dass eine Person nur höchstens einen Führerschein haben kann. Da bei Person keine Fremdschlüsselspalte existiert, können auf einfache Art und Weise auch Personen in der Datenbank eingetragen werden, die keinen Führerschein haben.

Von Zugmaschinen und Aufliegern

Betrachten wir als drittes Beispiel einer 1:1-Beziehung die Beziehung zwischen den beiden Teilen eines Sattelzugs: der Sattelzugmaschine und dem Sattelauflieger.

Sattelzug bestehend aus Zugmaschine (blau) und Auflieger (rot); Quelle

Eine Sattelzugmaschine kann bei einem gewöhnlichen Sattelzug nur einen Sattelauflieger ziehen und ein Sattelauflieger wird auch nur von einer Zugmaschine gezogen.

Wie das folgende Bild klar beweist, können Zugmaschine und Auflieger aber getrennt voneinander existierten. Eine Zugmaschine kann auch ohne Auflieger fahren und ein Auflieger kann ohne Zugmaschine abgestellt werden, zum Beispiel um ihn zu be- oder entladen oder auf einen Eisenbahnzug oder eine Fähre zu verladen, um am Zielbahnhof oder -hafen von einer anderen Zugmaschine abgeholt zu werden.

Die Beziehung zwischen einer Zugmaschine und einem Auflieger ist aus diesem Grund also völlig anderer Natur als die Beziehung zwischen einer Person und ihrem Ausweis. Im E/R-Modell ist beim Beziehungstyp aber zunächst kein Unterschied zu erkennen.

Ein Zusammenziehen der Zugmaschine und des Aufliegers in eine Tabelle scheidet aus. Zwar haben diese scheinbar den gleichen Schlüssel, aber das Kennzeichen des Aufliegers ist nicht das selbe Kennzeichen wie das der Zugmaschine. Die beiden Entitätstypen haben also nicht den selben Schlüssel, obwohl die Attribute bei beiden Entitätstypen den gleichen Namen haben.

Betrachten wir nun, was passiert, wenn wir versuchen, den Beziehungstyp aus dem E/R-Modell mit einer Fremdschlüsselspalte auf einer der beiden Seiten umzusetzen. In der Tabelle Zugmaschine könnte man das Kennzeichen des Aufliegers als Fremdschlüsselspalte hinzufügen. Hierbei gilt es, drei Dinge zu beachten: Erstens muss die Fremdschlüsselspalte wie in den anderen Beispielen auch mit UNIQUE belegt werden, da ein Auflieger nicht gleichzeitig an zwei oder mehr Zugmaschinen angehängt sein kann. Zweitens dürfen keine zwei Spalten in einer Tabelle den gleichen Namen haben. In der konkreten Umsetzung mit SQL müssen wir das beachten. Es ist in SQL aber nicht notwendig, dass eine Fremdschlüsselspalte den gleichen Namen hat wie die (Primärschlüssel-) Spalte auf die sie sich bezieht. Die Syntax in SQL betrachten wir am Ende dieses Artikels. Drittens muss es möglich sein, dass es Zugmaschinen gibt, die gerade nicht mit einem Auflieger gekoppelt sind. Es muss also möglich sein, den Fremdschlüssel für eine Zugmaschine frei zu lassen.

NULL

In relationalen Datenbanken ist es möglich, einen Wert als unbekannt, undefiniert oder bewusst ausgelassen zu kennzeichnen. Das geht auf eine Erweiterung des relationalen Modells von Edgar F. Codd zurück. Das Schlüsselwort in SQL dafür lautet NULL. Standardmäßig können alle Spalten statt eines konkreten Werts auch NULL sein. Davon ausgenommen sind Primärschlüssel. Da ein Primärschlüssel eine Zeile der Tabelle eindeutig definieren muss, ist nicht erlaubt, ihn undefiniert zu lassen. Für alle anderen Spalten kann beim Anlegen der Tabelle angegeben werden, ob NULL zulässig ist oder nicht. Das Freilassen von Werten kann mit dem Schlüsselwort NOT NULL verboten werden. Konkrete Beispiele von SQL-Statements mit NOT NULL werden am Ende des Artikels gezeigt. Auch Fremdschlüsselspalten und Spalten mit UNIQUE können prinzipiell NULL sein, wenn es beim Anlegen der Tabelle nicht mit NOT NULL ausgeschlossen wurde. Es gilt dann bei UNIQUE, dass ein Wert nur dann, wenn er tatsächlich angegeben wurde, eindeutig sein muss. Ein unbekannter Wert, also NULL, darf mehrmals vorkommen. Bei Fremdschlüsselspalten gilt, dass nur dann, wenn tatsächlich ein Wert eingetragen ist, er auf einen vorhandenen Wert in der Spalte, auf die sich der Fremdschlüssel bezieht, verweisen muss. Ist eine Fremdschlüsselspalte NULL, bedeutet das, dass die Beziehung in diesem konkreten Fall nicht eingegangen wird.

Im Sattelzug-Beispiel müsste also bei der Umsetzung mit einer Fremdschlüsselspalte auf einer der beiden Seiten jeweils NULL zugelassen werden.

Beim Führerschein-Beispiel dagegen muss, wenn eine Fremdschlüsselspalte bei der Tabelle Führerschein hinzugefügt wird, NULL verboten werden, da es keinen Führerschein gibt, der nicht einer Person zugeordnet ist. Hätten wir das E/R-Modell mit einer Fremdschlüsselspalte bei der Tabelle Person umgesetzt, hätten wir NULL erlauben müssen. Allerdings wäre das keine gute Idee gewesen: Es wäre dann möglich, Führerscheine in der Datenbank einzutragen, die keiner Person zugeordnet sind.

Bemerkenswert ist, dass es nicht möglich war, an den E/R-Modellen in der Notation nach Chen, wie wir sie bisher verwendet haben, zu erkennen, welche der Varianten die beste war. Die Chen-Notation gibt für eine 1:1-Beziehung nämlich nur an, dass eine Entität auf einer Seite mit höchstens einer Entität auf der anderen Seite in Beziehung stehen kann. Sie macht keine Aussage darüber, ob eine Entität auf einer Seite auch mit mindestens einer Entität auf der anderen in Beziehung stehen muss.

Die (min,max)-Notation

Eine Abhilfe schafft hier die (min,max)-Notation nach Jean-Raymond Abrial, die in Verbindung mit E/R-Diagrammen die Art einer Beziehung genauer bestimmt. Für jeden an einer Beziehung beteiligten Entitätstyp gibt die (min,max)-Notation an, mit wie vielen Entitäten auf der anderen Seite eine Entität dieses Typs mindestens und höchstens in Beziehung steht. Die E/R-Diagramme der Beispiele sehen in der (min,max)-Notation wie folgt aus:

Liest man in der (min,max)-Notation, geht man jeweils von dem Entitätstyp aus. Im mittleren Beispiel also bedeutet die (0,1) bei „Person“, dass eine Person mindestens gar keinen und höchstens einen Führerschein hat und die (1,1) bei Führerschein, dass ein Führerschein mindestens einer und höchstens einer, also genau einer, Person gehört.

Die (min,max)-Notation gibt also bei 1:1-Beziehungen Aufschluss darüber, welche Art der Umsetzung im relationalen Modell die beste ist. Bei einer (1,1)-(1,1)-Beziehung kann man üblicherweise die beiden Entitätstypen zu einer Tabelle zusammenfassen. Bei einer (0,1)-(1,1)-Beziehung ist die beste Umsetzung normalerweise, eine Fremdschlüsselspalte auf der (1,1)-Seite der Beziehung zu verwenden und diese mit NOT NULL und UNIQUE (siehe oben) zu belegen. Auf diese Art sind alle Bedingungen, die sich aus dem E/R-Modell ergeben, auch tatsächlich in der Datenbank forciert: Eine Person kann, wegen UNIQUE, nicht mehrere Führerscheine haben und ein Führerschein kann wegen NOT NULL nicht ohne eine zugeordnete Person existieren. Umgekehrt können Personen ohne Führerschein aber ganz normal in der Datenbank auftauchen.

Im Falle der (0,1)-(0,1)-Beziehung wie im letzten Beispiel kann die Umsetzung über eine Fremdschlüsselspalte auf einer der beiden Seiten erfolgen. Die Fremdschlüsselspalte muss dann UNIQUE sein, aber NULL zulassen. Alternativ ist auch eine Realisierung über eine Beziehungstabelle möglich! Abweichend zur Beziehungstabelle bei N:M-Beziehungen, bei denen der Primärschlüssel aus dem beiden Fremdschlüsselspalten zusammengesetzt ist, wird man bei einer Beziehungstabelle einer (0,1)-(0,1)-Beziehung üblicherweise nur einen der beiden Fremdschlüssel gleichzeitig zum Primärschlüssel machen und die andere Fremdschlüsselspalte mit UNIQUE belegen. Der Grund ist, dass bei einer 1:1-Beziehungstabelle beide Fremdschlüsselspalten jeweils für sich genommen eindeutig sein müssen und nicht wie bei N:M-Beziehungen nur die Kombination der beiden.

Generell kann man sagen, dass ein relationales Modell, das ein konzeptionelles E/R-Modell implementiert, dann gut ist, wenn alle Zwangsbedingungen, die sich aus dem konzeptionellen Modell ergeben, auch forciert werden. Es ist ein einem guten relationalen Modell also nur möglich, Zustände zu erzeugen, die auch konzeptionell erlaubt sind. Im Falle der 1:1-Beziehungen bedeutet das, wie beschrieben, den Einsatz von UNIQUE und ein Zulassen von NULL nur dort, wo es erforderlich ist. Mit der (min,max)-Notation kann das E/R-Modell so weit präzisiert werden, dass diese konzeptionellen Zwangsbedinungen direkt abgelesen werden können.

Dies ist im übrigen nicht nur bei 1:1-Beziehungen relevant, sondern auch bei den bereits diskutierten 1:N-Beziehungen kann eine Präzisierung vorteilhaft sein. Betrachten wir folgendes Beispiel:

Anders als im Beispiel des Sattelzugs, bei der eine Zugmaschine nur einen Auflieger transportiert, besteht ein Güterzug im Normalfall aus vielen Waggons. Es handelt sich also um eine 1:N-Beziehung. Ein Waggon kann aber auch allein abgestellt sein, ohne in einen Zug eingereiht zu sein. In der (min,max)-Notation ist dies an der (0,1) zu erkennen. Da eine 1:N-Beziehung üblicherweise mit einer Fremdschlüsselspalte auf der N-Seite umgesetzt wird, sagt die 0 hier aus, dass für den Fremschlüssel NULL zugelassen werden muss. Ein Güterzug besteht aus mindestens keinem Waggon (wenn eine Lokomotive allein durch die Gegend fährt) und höchstens beliebig vielen. „Beliebig viel“ wird in der (min,max)-Notation durch einen Stern dargestellt. Wir könnten hier im E/R-Modell auch eine Einschränkung vornehmen, z.B. dass ein Zug mindestens einen Wagen haben muss (wir also eine Lok allein nicht als Zug betrachten) und höchstens aus 20 Wagen bestehen kann, weil er sonst zu lang ist. Die Notation wäre dann (1,30). Anders als beim Unterschied zwischen (0,1) und (1,1) – im ersten Fall muss für den Fremdschlüssel NULL zugelassen, im zweiten Fall verboten werden – ist für das relationale Modell der Unterschied zwischen (0,*) und (1,30) aber nicht relevant. Die konzeptionelle Zwangsbedingung „mindestens 1 und höchstens 30 Waggons“ muss also an anderer Stelle geprüft werden.

Umsetzung mit SQL

Zum Abschluss noch kurz, wie die Beispiele hier in SQL umgesetzt werden. Wie immer gilt, dass der Deutlichkeit halber SQL-Befehle groß geschrieben sind.

Zunächst die Person und der Ausweis, wenn man die Tabellen zusammenzieht:

CREATE TABLE Person_Ausweis(
  Nummer INTEGER PRIMARY KEY,
  Name VARCHAR(100) NOT NULL,
  Datum DATE NOT NULL
);

Personen und Führerscheine:

CREATE TABLE Person(
  PersNr INTEGER PRIMARY KEY,
  Name VARCHAR(100) NOT NULL
);

CREATE TABLE Fuehrerschein(
  Nummer INTEGER PRIMARY KEY,
  PersNr INTEGER NOT NULL UNIQUE,
  Klasse CHAR(3) NOT NULL,
  FOREIGN KEY(PersNr) REFERENCES Person(PersNr)
);

Zugmaschine und Auflieger, wenn die Beziehung mit einem Fremdschlüssel bei Zugmaschine umgesetzt wird:

CREATE TABLE Auflieger(
  Kennz CHAR(12) PRIMARY KEY,
  Ladung VARCHAR(100)
);

CREATE TABLE Zugmaschine(
  Kennz CHAR(12) PRIMARY KEY,
  Typ VARCHAR(50) NOT NULL,
  Auflieger_Kennz CHAR(12) UNIQUE,
  FOREIGN KEY(Auflieger_Kennz) REFERENCES Auflieger(Kennz)
);

Wenn die Beziehung zwischen Auflieger und Zugmaschine mit einer Beziehungstabelle hergestellt wird, ist das eine mögliche Umsetzung:

CREATE TABLE Auflieger(
  Kennz CHAR(12) PRIMARY KEY,
  Ladung VARCHAR(100)
);

CREATE TABLE Zugmaschine(
  Kennz CHAR(12) PRIMARY KEY,
  Typ VARCHAR(50) NOT NULL
);
 
CREATE TABLE Auflieger_Zugmaschine(
  Zugmaschine_Kennz CHAR(12) PRIMARY KEY,
  Auflieger_Kennz CHAR(12) NOT NULL UNIQUE,
  FOREIGN KEY(Zugmaschine_Kennz) REFERENCES Zugmaschine(Kennz),
  FOREIGN KEY(Auflieger_Kennz) REFERENCES Auflieger(Kennz)
);

Besonders zu beachten bei den Beispielen ist folgendes:

Bei 1:1-Beziehungen sind die Fremdschlüsselspalten UNIQUE.
Manche Fremdschlüsselspalten sind NOT NULL, andere nicht! Wenn die Beziehung eingegangen werden muss, sind sie NOT NULL. Das ist bei (1,1) in der (min,max)-Notation der Fall.
Im Fall der Beziehungstabelle hätte man auch Auflieger_Kennz als PRIMARY KEY und Zugmaschine_Kennz als UNIQUE nehmen können. NULL darf man hier im Gegensatz zur Variante davor (Fremdschlüssel bei Zugmaschine) nicht zulassen. Wenn die Beziehung nicht eingegangen wird, ist einfach kein Eintrag in der Beziehungstabelle.
Wie üblich gilt, dass Klammern wichtig sind und dass die Reihenfolge, in der die Tabellen angelegt werden, wichtig ist, wenn Fremdschlüssel verwendet werden.

Viel Spaß beim Ausprobieren und Experimentieren!

Primärliteratur

Peter Pin-Shan Chen (1976): The Entity-Relationship Model – Toward a Unified View of Data, ACM Transactions on Database Systems, Vol. 1, No. 1.
Jean-Raymond Abrial (1974): Data Semantics, IFIP Working Conference Data Base Management, S. 1–60
Edgar F. Codd (1979): Extending the Database Relational Model to Capture More Meaning, ACM Transactions on Database Systems, Volume 4, Issue 4, S. 397–434

Referenzen

Oracle (2019): MySQL 8.0 Reference Manual (zuletzt abgerufen am 12.06.2019)
Jonathan Gennick (2006): SQL – kurz & gut, 2. Auflagen, O’Reilly

Lehrbücher

Kemper, Alfons und Eickler, André (2009): Datenbanksysteme – Eine Einführung, 7. Auflage

Vorlesungsfolien

Andreas Heß: Vorlesungsfolien aus dem Studiengang Wirtschaftsinformatik der Hochschule Furtwangen aus dem Wintersemester 2018/2019, Datenbanken, Vorlesung 3: Datenbankentwurf und DDL