ER diagram: from a description to a schema in SQL

Problem

The library notebook

A neighbourhood library keeps its loans in a notebook. The librarian describes what she wants from the new program:

“We have about 2,000 books, some of them in several copies. Members sign up with a name and an email. Anyone can borrow a book for three weeks. I want to know which books are out, who has them and who is late. And I want to search by author.”

Before the first CREATE TABLE, you need to know what tables there are, what they keep and how they connect. Otherwise, a month later, you will be redoing the schema with a full database.

The idea

Entities and attributes

You go through the description and look for the entities: the things you keep data about. They are usually nouns.

Member: name, email, when they signed up.
Book: title, year.
Author: name.
Copy: one physical copy of the book, on a certain shelf. “Some of them in several copies” means that the book and the copy are different things.
Loan: which copy, which member, when it was taken, when it is due, when it was returned.

Each entity becomes a table, and its attributes become columns. A loan is a verb, but it has its own attributes (dates), so it is a table too.

“Which are out” and “who is late” are not columns. They are questions that queries will answer.

The idea

Relationships and cardinality

Next you say how the entities connect and how many to how many. The picture of this is called an ER diagram (entity-relationship). Its most common notation is crow's foot, named after the sign for “many”, which looks like a bird's foot.

Each end of a line has two signs. The sign next to the table says how many at most, and the other one how many at least. This is how they are written at the right end:

|| exactly one;
o| zero or one;
|{ one or many;
o{ zero or many. { is the “foot”.

At the left end they are mirrored: ||, |o, }|, }o.

Here is the library. The notation is Mermaid, which GitHub and many editors draw as a picture:

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

The text after the colon is the label on the line. Mermaid requires it, and it means nothing for the schema.

members ||--o{ loans reads like this: one member has zero or many loans, and each loan belongs to exactly one member. books ||--|{ copies: every book has at least one copy, and each copy is of exactly one book.

Try it

What is the relationship?

1. A book can have several authors, and an author has many books. What is the relationship?1:1M:N1:N

2. A book has several copies, and each copy is of one book. What is the relationship?M:N1:11:N

The idea

From the diagram to tables

The rules are from the previous lesson:

1:N: a foreign key on the “many” side (copies.book_id, loans.copy_id, loans.member_id).
M:N: a link table with a primary key of both columns (book_authors).
1:1: a foreign key with UNIQUE. The library has none.

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

The fields below have this schema and a few books and members. Which copies of “Under the Yoke” are there, and on which shelf?

SQLempty database

SELECT c.id, c.shelf
FROM copies c
JOIN books b ON b.id = c.book_id
WHERE b.title = 'Under the Yoke';

More precisely, if you are curious

returned_at is NULL while the book is out. So one column keeps both “has it been returned” and “when”. Another option is a status column, but then you have to make sure the two never contradict each other.

Diagrams are also drawn with tools such as dbdiagram.io and draw.io, and most database programs (DBeaver, DataGrip, pgAdmin) draw an ER diagram from an existing database. The notation is the same.

See it

Book or copy?

The temptation is to point the loan straight at the book. Run the history of “Under the Yoke”:

SQLempty database

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;

Copy 1 was with two members one after the other, and copy 2 is with Borislav now. If the loan kept only “Under the Yoke”, you would not know which of the three is out, which came back torn, or how many are on the shelf.

That is why, when you design, you ask: what exactly happens in the world? The member takes an object from the shelf, not a title.

Try it

What is out

The librarian asks which books are out right now and who has them. Show the title, the copy number and the member.

SQLempty database

SELECT * FROM loans;

Solution

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;

Exercise

Two tasks

1. Today is 25 October 2026. Who is late? Show the member, the title and the date the book was due.

The loans that have not been returned and are past their due date:

SQLempty database

Solution

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. Searching by author in the catalogue shows every book with its authors. Show the title and the author's name.

All books with their authors:

SQLempty database

SELECT title FROM books;

Solution

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

Check yourself

1. The diagram says members ||--o{ loans. How do you read it?One member has zero or many loans, and each loan belongs to exactly one memberEvery member has exactly one loanOne loan belongs to many members

2. What in the description usually becomes a table?The adjectives: “overdue”, “available”The verbs: “borrows”, “returns”The nouns you keep attributes for: member, book, copy

3. Why does loans point to copies and not to books?To make the query shorterBecause what sits on the shelf is a copy: you need to know which of the three is outBecause books has no primary key

4. Why is there no “available copies” column in books?Because the column would be too longBecause it is computed from copies and loans, and the column would sooner or later be wrongBecause SQLite does not allow numbers

ER diagram: from a description to a schema

Cookies