The ER diagram
An entity is a kind of thing you keep facts about. Read the brief and pick out its nouns: authors write books, members borrow books. That gives three entities: author, book and member.
Attributes are the facts: an author’s name, a book’s title. One attribute, the key, picks out exactly one thing. Each entity here gets an id key, so two authors with the same name stay apart.
A relationship links entities with a verb: an author writes a book, a member borrows a book. Entities are boxes, relationships diamonds. A relationship can hold facts too: each loan has a date.
Cardinality says how many. One author writes many books: Pratchett wrote 2 here. But each book has exactly one author. So writes is one-to-many, marked 1 and N on its lines.
A member borrows many books, and over time a book is borrowed by many members: Ana borrows 2 books, and Mort goes out 2 times. Both sides are many, so borrows is many-to-many: M and N.
From diagram to tables
Now turn the diagram into tables. Each entity becomes a table, each attribute a column, and the key becomes the PRIMARY KEY. The database will now refuse two rows with the same id.
A one-to-many relationship becomes a foreign key on the many side. Each book row stores its author’s id in author_id. REFERENCES author makes the database check that the author exists.
A many-to-many relationship needs a table of its own, a junction table. Each loan row pairs one member with one book, and the relationship’s own fact, the date, lives here too. Ana borrowed Mort; so did Ben.
The design at work
Try to record a loan of book 99, which does not exist, and PostgreSQL refuses: the foreign key finds no book with id 99. Nothing is stored, so no loan can point at nothing.
To ask who borrowed what, a query follows the keys back. For each loan row, JOIN finds the member and the book it points to. Ana borrowed Mort and Sourcery; Ben borrowed Mort.
What happens
;; step 1/10 · entities the brief: Authors write books. Members borrow books, each loan on a date. nouns -> entities (drawn as boxes) author book member an entity is a kind of thing, not one thing: "book" stands for every book the library owns
Real books can have several authors. Then writes would be many-to-many too, and it would get its own table (book_author) just like loan. The figure keeps one author per book so you can see both kinds. A foreign key does not create an index on its own column in PostgreSQL. Joins and deletes that look up book.author_id or loan.book_id are faster if you add one (CREATE INDEX ON loan (book_id)). The primary key already gives loan an index that starts with member_id.