ER-Diagramm: von der Beschreibung zum Schema in SQL

Problem

Das Notizbuch der Bibliothek

Eine Bibliothek im Viertel führt ihre Ausleihen in einem Notizbuch. Die Bibliothekarin beschreibt, was sie vom neuen Programm will:

„Wir haben etwa 2 000 Bücher, manche davon in mehreren Exemplaren. Mitglieder melden sich mit Name und E-Mail an. Jeder kann ein Buch für drei Wochen ausleihen. Ich will wissen, welche Bücher draußen sind, wer sie hat und wer zu spät ist. Und ich möchte nach Autor suchen.“

Vor dem ersten CREATE TABLE musst du wissen, welche Tabellen es gibt, was sie speichern und wie sie zusammenhängen. Sonst baust du das Schema einen Monat später um, bei voller Datenbank.

Die Idee

Entitäten und Attribute

Du gehst die Beschreibung durch und suchst die Entitäten: die Dinge, zu denen du Daten speicherst. Meistens sind es Nomen.

Mitglied: Name, E-Mail, Anmeldedatum.
Buch: Titel, Jahr.
Autor: Name.
Exemplar: ein physisches Exemplar des Buchs, in einem bestimmten Regal. „Manche davon in mehreren Exemplaren“ heißt: Buch und Exemplar sind verschiedene Dinge.
Ausleihe: welches Exemplar, welches Mitglied, wann genommen, bis wann fällig, wann zurückgegeben.

Jede Entität wird zu einer Tabelle und ihre Attribute werden zu Spalten. Eine Ausleihe ist ein Verb, hat aber eigene Attribute (Daten), also ist sie auch eine Tabelle.

„Welche sind draußen“ und „wer ist zu spät“ sind keine Spalten. Es sind Fragen, die Abfragen beantworten.

Die Idee

Beziehungen und Kardinalität

Als Nächstes legst du fest, wie die Entitäten zusammenhängen und wie viele zu wie vielen gehören. Das Bild dazu heißt ER-Diagramm (Entity-Relationship). Die gebräuchlichste Notation ist der Krähenfuß (crow's foot), benannt nach dem Zeichen für „viele“, das wie ein Vogelfuß aussieht.

Jedes Ende einer Linie hat zwei Zeichen. Das Zeichen neben der Tabelle sagt, wie viele es höchstens sind, das andere, wie viele es mindestens sind. So werden sie am rechten Ende geschrieben:

|| genau eins;
o| null oder eins;
|{ eins oder viele;
o{ null oder viele. { ist der „Fuß“.

Am linken Ende sind sie gespiegelt: ||, |o, }|, }o.

Hier ist die Bibliothek. Die Notation ist Mermaid, das GitHub und viele Editoren als Bild zeichnen:

erDiagram
members ||--o{ loans : "borrows"
copies  ||--o{ loans : "is lent"
books   ||--|{ copies : "has"
books   ||--|{ book_authors : "is by"
authors ||--o{ book_authors : "writes"

Der Text nach dem Doppelpunkt ist die Beschriftung der Linie. Mermaid verlangt sie, für das Schema bedeutet sie nichts.

members ||--o{ loans liest sich so: Ein Mitglied hat null oder viele Ausleihen, und jede Ausleihe gehört zu genau einem Mitglied. books ||--|{ copies: Jedes Buch hat mindestens ein Exemplar, und jedes Exemplar gehört zu genau einem Buch.

Probier es aus

Welche Beziehung ist das?

1. Ein Buch kann mehrere Autoren haben, und ein Autor hat viele Bücher. Welche Beziehung ist das?1:N1:1M:N

2. Ein Buch hat mehrere Exemplare, und jedes Exemplar gehört zu einem Buch. Welche Beziehung ist das?1:1M:N1:N

Die Idee

Vom Diagramm zu den Tabellen

Die Regeln kennst du aus der vorigen Lektion:

1:N: ein Fremdschlüssel auf der „viele“-Seite (copies.book_id, loans.copy_id, loans.member_id).
M:N: eine Verbindungstabelle mit einem Primärschlüssel aus beiden Spalten (book_authors).
1:1: ein Fremdschlüssel mit UNIQUE. Die Bibliothek hat keine.

members

PKidinteger
nametext *
emailtext *
joined_attext *

authors

PKidinteger
nametext *

books

PKidinteger
titletext *
yearinteger

book_authors

PKbook_idinteger→ books
PKauthor_idinteger→ authors

copies

PKidinteger
FKbook_idinteger *→ books
shelftext *

loans

PKidinteger
FKcopy_idinteger *→ copies
FKmember_idinteger *→ members
loaned_attext *
due_attext *
returned_attext

Die Felder unten haben dieses Schema und ein paar Bücher und Mitglieder. Welche Exemplare von „Unter dem Joch“ gibt es, und in welchem Regal stehen sie?

SQLleere Datenbank

SELECT c.id, c.shelf
FROM copies c
JOIN books b ON b.id = c.book_id
WHERE b.title = 'Unter dem Joch';

Genauer, für Neugierige

returned_at ist NULL, solange das Buch draußen ist. Eine Spalte speichert also beides: „ist es zurückgegeben“ und „wann“. Eine andere Möglichkeit ist eine Spalte status, aber dann musst du dafür sorgen, dass sich die beiden nie widersprechen.

Diagramme zeichnet man auch mit Werkzeugen wie dbdiagram.io und draw.io, und die meisten Datenbankprogramme (DBeaver, DataGrip, pgAdmin) zeichnen aus einer vorhandenen Datenbank ein ER-Diagramm. Die Notation ist dieselbe.

Sieh es dir an

Buch oder Exemplar?

Die Versuchung ist, die Ausleihe direkt auf das Buch zeigen zu lassen. Sieh dir den Verlauf von „Unter dem Joch“ an:

SQLleere Datenbank

SELECT l.copy_id, m.name, l.loaned_at, l.returned_at
FROM loans l
JOIN copies c ON c.id = l.copy_id
JOIN members m ON m.id = l.member_id
WHERE c.book_id = 1;

Exemplar 1 war nacheinander bei zwei Mitgliedern, und Exemplar 2 hat jetzt Borislav. Würde die Ausleihe nur „Unter dem Joch“ speichern, wüsstest du nicht, welches der drei draußen ist, welches zerrissen zurückkam oder wie viele im Regal stehen.

Deshalb fragst du beim Entwerfen: Was passiert genau in der Welt? Das Mitglied nimmt einen Gegenstand aus dem Regal, keinen Titel.

Probier es aus

Was ist draußen

Die Bibliothekarin fragt, welche Bücher gerade draußen sind und wer sie hat. Zeige den Titel, die Exemplarnummer und das Mitglied.

SQLleere Datenbank

SELECT * FROM loans;

Lösung

SELECT b.title, c.id AS copy, m.name
FROM loans l
JOIN copies c ON c.id = l.copy_id
JOIN books b ON b.id = c.book_id
JOIN members m ON m.id = l.member_id
WHERE l.returned_at IS NULL;

Übung

Zwei Aufgaben

1. Heute ist der 25. Oktober 2026. Wer ist zu spät? Zeige das Mitglied, den Titel und das Datum, an dem das Buch fällig war.

Die Ausleihen, die nicht zurückgegeben wurden und deren Fälligkeitsdatum vorbei ist:

SQLleere Datenbank

Lösung

SELECT m.name, b.title, l.due_at
FROM loans l
JOIN copies c ON c.id = l.copy_id
JOIN books b ON b.id = c.book_id
JOIN members m ON m.id = l.member_id
WHERE l.returned_at IS NULL AND l.due_at < '2026-10-25';

2. Die Suche nach Autor im Katalog zeigt jedes Buch mit seinen Autoren. Zeige den Titel und den Namen des Autors.

Alle Bücher mit ihren Autoren:

SQLleere Datenbank

SELECT title FROM books;

Lösung

SELECT b.title, a.name
FROM books b
JOIN book_authors ba ON ba.book_id = b.id
JOIN authors a ON a.id = ba.author_id;

Test

Teste dich

1. Das Diagramm sagt members ||--o{ loans. Wie liest du das?Jedes Mitglied hat genau eine AusleiheEin Mitglied hat null oder viele Ausleihen, und jede Ausleihe gehört zu genau einem MitgliedEine Ausleihe gehört zu vielen Mitgliedern

2. Was in der Beschreibung wird normalerweise zu einer Tabelle?Die Nomen, zu denen du Attribute speicherst: Mitglied, Buch, ExemplarDie Verben: „leiht aus“, „gibt zurück“Die Adjektive: „überfällig“, „verfügbar“

3. Warum zeigt loans auf copies und nicht auf books?Weil im Regal ein Exemplar steht: Du musst wissen, welches der drei ausgeliehen istWeil books keinen Primärschlüssel hatDamit die Abfrage kürzer wird

4. Warum gibt es in books keine Spalte „verfügbare Exemplare“?Weil die Spalte zu lang wäreWeil SQLite keine Zahlen erlaubtWeil sie sich aus copies und loans berechnen lässt und die Spalte früher oder später falsch wäre

ER-Diagramm: von der Beschreibung zum Schema

Cookies