ER диаграма: от описание до схема в SQL

Проблем

Тетрадката на читалището

Квартално читалище води заеманията в тетрадка. Библиотекарката ти описва какво иска от новата програма:

„Имаме около 2 000 книги, от някои по няколко бройки. Читателите се записват с име и имейл. Всеки може да вземе книга за три седмици. Искам да знам кои книги са навън, у кого са и кой закъснява. И да търся по автор.“

Преди първия CREATE TABLE трябва да е ясно какви таблици има, какво пазят и как се свързват. Иначе след месец ще преправяш схемата с пълна база.

Идеята

Същности и свойства

Минаваш през описанието и търсиш същностите: нещата, за които пазиш данни. Обикновено са съществителни.

Читател: име, имейл, кога се е записал.
Книга: заглавие, година.
Автор: име.
Екземпляр: една бройка от книгата, на определен рафт. „От някои по няколко бройки“ значи, че книгата и бройката са различни неща.
Заемане: кой екземпляр, кой читател, кога е взет, до кога трябва да се върне, кога е върнат.

Всяка същност става таблица, а свойствата ѝ стават колони. Заемането е глагол, но има свои свойства (дати), затова също е таблица.

„Кои са навън“ и „кой закъснява“ не са колони. Това са въпроси, на които ще отговарят заявки.

Идеята

Връзки и кратност

После казваш как се свързват същностите и колко към колко. Картинката с това се казва ER диаграма (entity-relationship, „същност и връзка“). Най-разпространеният ѝ запис е crow's foot, „гарванов крак“, по знака за „много“, който прилича на птичи крак.

Всеки край на линията има два знака. Знакът до таблицата казва колко най-много, а другият колко най-малко. Така се пишат в десния край:

|| точно един;
o| нула или един;
|{ един или много;
o{ нула или много. { е „крачето“.

В левия край са огледални: ||, |o, }|, }o.

Ето библиотеката. Записът е на езика Mermaid, който GitHub и много редактори рисуват като картинка:

erDiagram
members ||--o{ loans : "взема"
copies  ||--o{ loans : "се заема"
books   ||--|{ copies : "има"
books   ||--|{ book_authors : "е от"
authors ||--o{ book_authors : "пише"

Текстът след двоеточието е надписът на линията. Mermaid го иска, а за схемата не значи нищо.

members ||--o{ loans се чете така: един читател има нула или много заемания, а всяко заемане е на точно един читател. books ||--|{ copies: всяка книга има поне един екземпляр, а всеки екземпляр е от точно една книга.

Пробвай

Каква е връзката?

1. Една книга може да е от няколко автори, а един автор има много книги. Каква е връзката?1:NM:N1:1

2. Една книга има няколко екземпляра, а всеки екземпляр е от една книга. Каква е връзката?1:11:NM:N

Идеята

От диаграмата към таблиците

Правилата са от предишния урок:

1:N: външен ключ от страната на „много“-то (copies.book_id, loans.copy_id, loans.member_id).
M:N: таблица за връзка с първичен ключ от двете колони (book_authors).
1:1: външен ключ с UNIQUE. В библиотеката няма такава.

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

Полетата долу са с тази схема и с няколко книги и читатели. Кои екземпляри на „Под игото“ има и на кой рафт?

SQLпразна база

SELECT c.id, c.shelf
FROM copies c
JOIN books b ON b.id = c.book_id
WHERE b.title = 'Под игото';

По-точно, за любопитните

returned_at е NULL, докато книгата е навън. Така една колона пази и „върната ли е“, и „кога“. Друг вариант е колона status, но тогава трябва да внимаваш двете да не си противоречат.

Диаграмите се рисуват и с инструменти като dbdiagram.io и draw.io, а повечето програми за бази (DBeaver, DataGrip, pgAdmin) правят ER диаграма от готова база. Нотацията е същата.

Виж го

Книга или екземпляр?

Изкушението е заемането да сочи направо към книгата. Пусни историята на „Под игото“:

SQLпразна база

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;

Екземпляр 1 е бил у двама читатели един след друг, а екземпляр 2 е у Борислав сега. Ако заемането пазеше само „Под игото“, нямаше да знаеш кой от трите е навън, кой е върнат скъсан и колко са на рафта.

Затова в проектирането питаш: какво точно се случва в света? Читателят взема предмет от рафта, а не заглавие.

Пробвай

Кое е навън

Библиотекарката пита кои книги са навън в момента и у кого. Покажи заглавието, номера на екземпляра и читателя.

SQLпразна база

SELECT * FROM loans;

Решение

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;

Упражнение

Две задачи

1. Днес е 25 октомври 2026. Кой закъснява? Покажи читателя, заглавието и до кога трябваше да върне книгата.

Заеманията, които още не са върнати и срокът им е минал:

SQLпразна база

Решение

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. Търсенето по автор в каталога показва всяка книга с авторите ѝ. Покажи заглавието и името на автора.

Всички книги с авторите им:

SQLпразна база

SELECT title FROM books;

Решение

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;

Тест

Провери се

1. В диаграмата пише members ||--o{ loans. Как се чете?Един читател има нула или много заемания, а всяко заемане е на точно един читателЕдно заемане е на много читателиВсеки читател има точно едно заемане

2. Кое от описанието обикновено става таблица?Съществителните, за които пазиш свойства: читател, книга, екземплярПрилагателните: „просрочена“, „свободна“Глаголите: „заема“, „връща“

3. Защо loans сочи към copies, а не към books?Защото books няма първичен ключЗа да е по-кратка заявкатаЗащото на рафта стои екземпляр: трябва да знаеш кой точно от трите е навън

4. Защо в books няма колона „свободни екземпляри“?Защото SQLite не позволява числаЗащото това се смята от copies и loans и колоната рано или късно ще лъжеЗащото колоната е прекалено дълга

ER диаграма: от описание до схема

Бисквитки