Der logische Datenbankentwurf mit dem Relationenmodell

Im konzeptuellen Modell wurden die abzubildenden Daten aus der realen Welt in Form von Entitätstypen und Attributen abstrahiert und strukturiert. Dabei erfolgte noch keine Festlegung in welcher Art Datenbank die Daten gespeichert werden sollen: ob beispielsweise als XML-Datei, in einer hierarchischen Datenbank oder eben einer relationalen Datenbank. Diese Festlegung auf ein logisches Datenbankmodell erfolgt zu Beginn des nächsten Entwurfsschritts: im logischen Datenmodell.

Schrittweiser Entwurf einer Datenbank
Schrittweiser Entwurf einer Datenbank

Es gibt eine Vielzahl unterschiedlicher Datenmodelle mit einer noch größeren Vielzahl konkreter Programme (Implementierungen). Die bekanntesten Vertreter sind:

  • Hierarchisches Modell (z.B. Dateisystem – jeder Datensatz hat genau einen Vorgänger)

  • Netzwerkmodell (mehrere Vorgängerdaten, Struktur bei Datenabfrage festgelegt)

  • Relationales Modell (in Beziehung stehende Tabellen)

  • Objektorientiertes Modell (Objekte aus OOP können in DB gespeichert werden)

  • Objekt-relationales Modell (relationales Modell, das Vererbung zulässt)

  • Semistrukturiertes Modell / schemafreie DB (Struktur der Datenelemente ist nicht festgelegt (z.B. schemafreies XML, JSON)

Transformation des konzeptuellen Modells in ein relationales logisches Modell: das Relationenmodell

Informationen aus dem ER-Modell

Welche Eigenschaften werden im Entity-Relationship-Modell beschrieben, die übertragen werden müssen?

  • Entitätstypen

  • Attribute

  • Beziehungen (1:1, 1:N, M:N)

  • Optionalitäten der Beziehungen (1:C, 1:NC, M:NC)

  • Mehrwertigkeit von Attributen

  • Schwache Entitätstypen und identifizierende Relationen

  • Schwache Schlüsselattribute

  • abgeleitete Attribute

Transformationsschritte

Primärschlüssel wählen

Für jeden Entitätstyp muss ein Primärschlüssel (primary key) gefunden werden, der jede Entität eindeutig identifiziert.

Primärschlüssel können sich aus einem oder einer Kombination mehrerer Attribute ergeben. Primärschlüsselkandidat sind alle Attribut(-kombinationen), die für jede einzelne Entität einzigartig (unique) ist. Geeignete zusammengesetzte Primärschlüsselkandidaten enthalten nur so viele Attribute, wie zur Identifizierung einer Entität erforderlich. Neben den natürlichen Schlüsseln, die sich aus vorhandenen Attributen ergeben, besteht auch noch die Möglichkeit, künstliche Primärschlüssel zu vergeben. Diese sogenannten Surrogatschlüssel können beispielsweise fortlaufende Nummern oder anderweitig erzeugte (Hash-)werte sein.

Aus den so gebildeten Schlüsselkandidaten kann ein Primärschlüssel ausgewählt werden.

Kardinalitäten in Fremdschlüsselbeziehungen transformieren

Die Beziehungen zwischen Entitätstypen werden im ER-Modell als Assoziation mit Kardinalitäten modelliert.

Im Relationenmodell werden sie in Fremdschlüsselbeziehungen transformiert: die Primärschlüssel einer Relation werden in einer zweiten Relation als Attribut gelistet - dort erhalten sie die Bezeichnung “Fremdschlüssel” (foreign key).

Die ersten Transformationsschritte sind für alle Arten von Entitätstypen identisch:

  1. Aus allen Entitätstypen werden Relationen. Oft werden diese ale einfacher Text erfasst, klein geschrieben und mit dem Plural bezeichnet: schülerInnen()

  2. Alle zugehörigen Attribute der Entitätstypen werden hinter dem Relationennamen in Klammern kommagetrennt gelistet: schülerInnen(SchülerInID, Vorname, Nachname)

  3. Die Primärschlüsselattribute werden unterstrichen oder mit PK (für primaty key) annotiert: schuelerInnen(SchülerInID (PK), Vorname, Nachname)

Transformation einer Assoziation mit 1:N - Kardinalität

Als Beispiel dient die Assoziation zwischen den Entitätstypen Schüler*in und Klasse:

1:n Beziehung zwischen SchülerIn und LehrerIn
1:n Beziehung zwischen SchülerIn und LehrerIn

Zunächst werden beide Entitätstypen wie oben unter 1.-3. beschreiben in Relationen umgewandelt, dann folgt die Umwandlung der Assoziation und Kardinalität:

  1. Bei 1:N-Beziehungen wird der Primärschlüssel der Realtion, bei der die Kartinalität “1” steht als Fremdschlüsselattribut in die Relation, bei der das “N” steht aufgenommen. Fremdschlüsselattribute werden mit Pfeil nach oben gekennzeichnet oder mit FK (foreign key) annotiert:

Relationenschema für 1:N-Beziehungen

schülerInnen (SchülerInID, Vorname, Nachname ↑Klassenbezeichnung↑) klassen (Klassenbezeichnung, KlassenlehrerIn)

oder in Textform ohne formatierungen und Sonderzeichen:

schülerInnen (SchülerInID (PK), Vorname, Nachname, Klassenbezeichnung (FK))
klassen (Klassenbezeichnung (PK), KlassenlehrerIn)

Transformation einer Assoziation mit M:N - Kardinalität

Eine Assoziation mit einer Vielfachheit zu beiden Seiten (M:N) wird am Beispiel der Entitätstypen Lehrer*in und Klasse dargestellt:

m:n-Beziehung zwischen Klasse und LehrerIn
m:n-Beziehung zwischen Klasse und LehrerIn

Auch hier werden zunächst beide Entitätstypen wie oben unter 1.-3. beschreiben in Relationen umgewandelt, danach folgt die Umwandlung der Assoziation und Kardinalität:

  1. Im Fall einer M:N-Beziehung wird eine neue Relation gebildet. Diese zusätzliche Relation erhält die Primärschlüssel beider verknüpfter Relationen als zusammengesetzten Primärschlüssel. Diese beiden Attribute sind zugleich Fremdschlüssel.

Relationenschema für M:N-Beziehungen

lehrerInnen (LehrerInID, Vorname, Nachname) klassen (Klassenbezeichnung, Jahrgangsstufe) unterrichte (↑LehrerInID↑, ↑Klassenbezeichnung↑)

bzw. in Reintextform:

lehrerInnen (LehrerInID (PK), Vorname, Nachname)
klassen (Klassenbezeichnung (PK), Jahrgangsstufe)
unterrichte (LehrerInID(PK, FK), Klassenbezeichnung(PK, FK))

Transformation einer Assoziation mit 1:1 - Kardinalität

Relativ selten kommt es in der Praxis vor, dass zwei Entitätstypen in einer 1:1-Beziehung zueinander stehen. Bei der Umwandlung in das Relationenmodell ergeben sich hier eine Reihe von Möglichkeiten, die anhand des Beispiels Einwohner und Personalausweis dargestellt werden:

1:1 Relation zwischen Einwohner und Personalausweis
1:1 Relation zwischen Einwohner und Personalausweis

Möglichkeit 1: Zusammenfassung in einer Relation

In der Regel ist es am einfachsten, die beiden Entitätstypen in einer gemeinsamen Relation zusammenzufassen, wobei nur einer der beiden Primärschlüssel in der neuen Relation diese Funktion übernimmt (der zweite bleibt als unikales - also einzigartiges - Attribut vorhanden).

einwohner (SozialversicherungsNr, Vorname, Nachname, PersoNr (unikal), Ausstellungsdatum, Gültigkeit)

Möglichkeit 2: zwei Relationen mit gemeinsamem Primärschlüssel

Denkbar ist auch, dass zwei Relationen gebildet werden, die beide den gleichen Primärschlüssel verwenden. Diese Variante wäre zu bevorzugen, wenn einer der beiden Entitätstypen sehr selten zugeordnet würde und dieser vergleichsweise teuer ist (also Daten-/Ressourcen-aufwändig). Würde also beispielsweise ein hochaufgelöstes Passbild gespeichert bei den wenigen Einträgen der Personalausweise, wobei sehr viele Einwohner ohne Personalausweis existieren, dann wäre dies der Weg der Wahl.

einwohner (↑SozialversicherungsNr↑, Vorname, Nachname)

personalausweise (↑SozialversicherungsNr↑, PersoNr (unikal), Ausstellungsdatum, Gültigkeit)

Möglichkeit 3: Primärschlüssel als Fremdschlüssel, dieser muss unikal sein

Eine dritte, aber fehleranfälligere Variante geht von zwei Relationen mit unterschiedlichen Primärschlüsseln aus, die über einen Fremdschlüssel verknüpft sind. Damit auf diesem Weg keine 1:N-Beziehung modelliert wird und die Datenbank inkonsistent wird muss dieser Fremdschlüssel jedoch unikal, also in der Eingabe einzigartig, sein. Diesen Weg sollte man nur wählen, wenn man weiß, was man tut.

einwohner (↑SozialversicherungsNr↑, Vorname, Nachname)

personalausweise (PersoNr, ↑SozialversicherungsNr↑ (unikal), Ausstellungsdatum, Gültigkeit)

Möglichkeit 4: Verknüpfungstabelle mit zusammengesetztem Primärschlüssel, in dem beide Teilschlüssel unikal sein müssen

Eine vierte Variante ist sachgerecht, wenn beide Entitätstypen optional sind, es also im Beispiel sowohl Einwohner ohne Personalausweis als auch Personalausweise ohne Einwohner gibt. Hierbei wird analog zur M:N-Beziehung eine Zwischenrelation erstellt, bei der beide Primärschlüssel der Einzelrelationen als Fremdschlüssel übernommen werden und gemeinsam den zusammengesetzten Primärschlüssel der neuen Relation ergeben. Um Mehrfachzuordnungen auszuschließen ist auch hier wichtig, dass die beiden Primärschlüsselteile in der Zwischentabelle unikal sind.

einwohner (SozialversicherungsNr, Vorname, Nachname)

personalausweise (PersoNr, Ausstellungsdatum, Gültigkeit)

einwohner2personalausweise (↑PersoNr↑ (unikal), ↑SozialversicherungsNr↑ (unikal))

Die hier in allen Beispielen genannte Einzigartigkeit (unikal / unique) muss im Relationenmodell explizit genannt werden!

Transformation von schwachen Entitätstypen mit identifizierender Assoziation

Schwacher Entitätstyp mit identifiziernder Assoziation und schwachem Schlüssel
Schwacher Entitätstyp mit identifiziernder Assoziation und schwachem Schlüssel

Sofern zur Identifizierung einer Entität neben einem Attribut des Entitätstyps eine Beziehung zu einem anderen Entitätstyp nötig ist, spricht man von einem schwachen Entitätstypen. Die Rechnungsposition “1” existiert auf mehreren Rechnungen, daher ist zur genauen Identifizierung die Kenntnis wichtig, um welche Rechnung es sich handelt. Rechnungsposition ist in diesem Fall ein schwacher Schlüssel, Rechnungsposten ein schwacher Entitätstyp und enthält ist eine identifiziernde Beziehung.

Der schwache Entitätstyp wird in diesem Fall als Relation mit zusammengesetztem Primärschlüssel modelliert, wobei der eine Teilschlüssel dem Fremdschlüssel aus der identifizierenden Beziehung entspricht und der zweite Teilschlüssel der schwache Schlüssel des Ausgangs-Entitätstyps ist.

Relationenschema für schwache Relationen in 1:N-Beziehungen:

rechnungen(Rechnungsnummer, Rechnungsanschrift, Datum) rechnunsposten (Rechnungsposition, ↑Rechnungsnummer↑, Preis, Anzahl, Artikel)

Optionalitäten transformieren

Bei Kardinalitäten, die mit Optionalität formuliert sind (1:C, 1:NC, N:MC) wird die Optionalität in der Regel über die nicht verpflichtende Eingabe eines Fremdschlüssels modelliert. Primärschlüsselattribute gelten immer als eingabepflichtig, alle anderen Attribute (inkl. Fremdschlüssel) gelten als nicht eingabepflichtig.

Sofern für Nichtschlüsselattribute eine Eingabepflicht festgelegt werden soll kann dies durch explizite Angabe von NOT NULL dokumentiert werden, einige CASE-Tools notieren Eingabepflichtige Attribute fett, Attribute ohne Eingabepflicht in normaler Schreibweise.

Alternative Schreibweisen

Da sowohl die Pfeile für Fremdschlüssel als auch die Unterstreichungen für Primärschlüssel nicht in jedem System darstellen lassen, gibt es eine Reihe abweichender Notationsformen für Relationenmodelle.

Am häufigsten anzutreffen ist die Notation als Tabelle mit Kennzeichnung der Schlüssel über Schlüsselsymbole oder “FK” und “PK”. Einzigartigkeit wird dann häufig mit uniq, Eingabepflicht mit NOT NULL oder Sternchen * angegeben. Viele CASE-Tools zum Datenbankentwurf setzen dies so um, auch in den IHK Abschlussprüfungen für Fachinformatiker*innen ist dies häufig so anzutreffen:

Darstellung des Relationenmodells als Tabelle
Darstellung des Relationenmodells als Tabelle

PlantUML für Relationenschemata verwenden:

Mit dem UML-Werkzeug plantUML lassen sich auch Relationenschema mit Schlüsselbeziehungen abbilden. Bei einigen Symbolen muss auf die HTML-notierten Unicode-Zeichen zurückgegriffen werden. In folgendem Beispiel wurden bewusst mehrere redundante Wege der Kennzeichnung aufgezeigt - natürlich genügt zu Dokumentationszwecken eine der genannten Hervorhebungen:

Darstellung des Relationenmodells als Tabelle
Darstellung des Relationenmodells als Tabelle

Erstellt werden können die plantUML-Diagramme beispielsweise über den Webdienst planttext (wie z.B. die obige Grafik über diesen Link) IDE-Plugins oder git*-Dienste wie bitbucket, gitlab oder github.

Der Quelltext zur Erstellung des obigen Relationenschemas wäre etwa:

@startuml
hide circle
skinparam classAttributeIconSize 0

entity "entity" {
  &#9911; PrimaryKey attribute <<PK>>
  &#8593; ForeignKey attribute <<FK>>
  * <b>mandatory attribute</b> <<NOT NULL>>
    optional attribute
  &#9312; unique attribute <<UNIQ>>
}

@enduml
  • eine detaillierte Liste aller Transformationsregeln - vor allem inkl. der Sonderfälle für rekursive und optionale Beziehungen findet sich u.a. im “Grundkurs Datenbankentwurf” von Helmut Jarosch, 4. Auflage 2016

  • dbdiagram.io ist ein weiteres Onlinetool, um relationale Datenmodelle darzustellen

Quellen und offene Ressourcen (OER)

Die Ursprungstexte (als Markdown), Grafiken und zugrunde liegende Diagrammquelltexte finden sich in weiterbearbeitbarer Form im gitlab-Repository unter https://gitlab.com/oer-informatik/db-design/relationenmodell und sind zur Nutzung als Open Education Resource (OER) freigegeben gemäß der Creative Commons Namensnennung 4.0 International Lizenz (CC BY 4.0).

Creative Commons Lizenzvertrag
Creative Commons Lizenzvertrag
Kommentare gerne per Mastodon, Verbesserungsvorschläge per gitlab issue (siehe oben). Beitrag teilen: