Концептуальное проектирование: модель «сущность — связь»

В темах 2–6 учебная база «Книжный узел» была дана готовой: таблицы, ключи и ограничения целостности существовали заранее, и задача состояла в том, чтобы понимать их смысл и писать к ним запросы. Однако схема базы данных не возникает сама собой. Кто-то решил, что автор — это отдельная таблица, а не текстовое поле книги, что у книги может не быть издательства, что покупатель оставляет не более одного отзыва на книгу, а цена в позиции заказа хранится отдельно от цены в каталоге. Каждое такое решение отражает представление о предметной области, и ошибка в нём обходится дорого: исправить схему, в которой уже лежат миллионы строк и с которой работают десятки программ, многократно сложнее, чем исправить рисунок на этапе обсуждения.

Раздел II курса посвящён проектированию — переходу от словесного описания предметной области к схеме базы данных. Тема открывает раздел и отвечает на первый вопрос: как описать предметную область точно, но ещё без таблиц, столбцов и типов данных. Для этого служит модель «сущность — связь» (англ. entity-relationship model, ER-модель), предложенная П. Ченом в 1976 году ¹. Её словарь — сущности, атрибуты, связи, кардинальности — достаточно прост, чтобы обсуждать модель с заказчиком, и достаточно строг, чтобы почти механически превратить её в реляционную схему.

Изложение идёт от этапов проектирования к основным и более тонким конструкциям ER-модели (слабые сущности, атрибуты связей, тернарные связи), затем к нотациям — Чена, «вороньи лапки», UML, IDEF1X — и к обобщению со способами хранить иерархию типов в таблицах. Последний раздел формулирует правила преобразования ER-модели в реляционную схему, показывает обратный путь — восстановление модели учебной базы по системному каталогу — и разбирает типичные ошибки моделирования. Вопрос о том, какие из полученных таблиц избыточны и как это строго проверить, отложен до темы 8 (функциональные зависимости и нормализация), а выбор типов данных и физическое размещение — до темы 9.

Этапы проектирования базы данных

Требования, концептуальная, логическая и физическая модели

Проектирование базы данных принято делить на четыре этапа, каждый из которых отвечает на свой вопрос и даёт свой результат ²³.

Концептуальная модель (англ. conceptual model) описывает предметную область независимо от модели данных и СУБД: в ней нет таблиц, внешних ключей и типов integer, есть «книга», «автор» и утверждение «у книги может быть несколько авторов». Логическая модель (англ. logical model) — та же информация, выраженная в терминах конкретной модели данных; для реляционной СУБД это набор схем отношений с ключами и ограничениями (тема 2). Физическая модель (англ. physical model) добавляет то, что нужно для производительности и эксплуатации: конкретные типы, индексы, размещение по табличным пространствам.

Разделение этапов отделяет вопросы разной природы. Утверждение «покупатель может оставить не более одного отзыва на книгу» — факт о предметной области, его подтверждает заказчик; утверждение «столбец email имеет тип text» — инженерное решение, о котором заказчику знать не нужно.

Следует оговорить терминологическую тонкость. В трёхуровневой архитектуре ANSI/SPARC (тема 1) «концептуальным уровнем» называется общая логическая схема всей базы данных — то, что в таблице выше названо логической моделью. В литературе по проектированию слово «концептуальный» обычно обозначает более ранний, не зависящий от модели данных уровень ³. В этой теме оно используется во втором смысле.

На практике этапы повторяются итеративно: построение ER-модели выявляет пробелы в требованиях, логическое проектирование — избыточность модели, физическое — слишком дорогие запросы. Но порядок вопросов сохраняется: сначала смысл, затем структура, затем эффективность.

Бизнес-правила и словарь предметной области

Исходный материал проектирования — требования: интервью с будущими пользователями, существующие документы (бланки заказов, отчёты, выгрузки из старых систем), описания процессов. Результат анализа требований удобно фиксировать в двух формах.

Словарь предметной области (англ. glossary) даёт понятиям точные определения. «Заказ» кажется очевидным, пока не выясняется, что для отдела продаж это то, что оформил покупатель, а для склада — одна посылка; если заказ может уйти двумя посылками, в модели это две разные сущности.

Бизнес-правило (англ. business rule) — утверждение, ограничивающее допустимые состояния данных или действия над ними ⁴. Для «Книжного узла» из описания магазина извлекаются, например, такие правила:

• каждая книга относится ровно к одной рубрике; рубрики образуют дерево;
• у книги может не быть издательства (самиздат, издания без выходных данных), а издательство может выпускать много книг;
• у книги может быть несколько авторов, и их порядок на обложке важен;
• покупатель может оставить не более одного отзыва на книгу, оценка — от 1 до 5;
• цена в заказе — это цена на момент оформления; последующее изменение цены в каталоге не меняет уже оформленные заказы;
• заказ отгружается с одного склада.

Каждое правило станет элементом структуры (таблицей, внешним ключом), ограничением (NOT NULL, UNIQUE, CHECK) или, если декларативно его не выразить, триггером либо проверкой в приложении (тема 14). Например, последнее правило превратилось в столбец orders.warehouse_id, правило об отзывах — в ограничение UNIQUE (book_id, customer_id) таблицы review, а правило о цене — в столбец order_item.price, хранящийся отдельно от book.price.

Кандидатов в элементы модели подсказывает грамматическая эвристика: существительные — сущности или атрибуты, глаголы — связи, слова «каждый», «может», «не более одного» — кардинальности ³. Это лишь подсказка: «город» может оказаться и атрибутом, и сущностью (см. раздел о типичных ошибках).

Зачем нужен отдельный концептуальный уровень

У отдельного концептуального уровня три преимущества, которые растут с размером системы. Во-первых, он служит общим языком с заказчиком: диаграмма из десятка прямоугольников читается за минуты и сразу вызывает правильные вопросы («почему у отзыва нет связи с заказом — можно ли оценить некупленную книгу?»), тогда как текст DDL такого обсуждения не порождает. Во-вторых, он отделяет решения о смысле от решений о реализации: одна ER-модель может быть реализована реляционной схемой, документной базой (тема 16) или классами программы. В-третьих, правила преобразования ER-модели в таблицы почти механические, а получаемая схема при аккуратном моделировании сразу оказывается близкой к третьей нормальной форме (тема 8) ¹¹, так что многие ошибки обнаруживаются ещё на рисунке.

Сущности, атрибуты и связи

Сущность и экземпляр сущности

Сущность (англ. entity) — это вещь или понятие предметной области, которое можно отличить от других и о котором нужно хранить сведения: конкретная книга «Пикник на обочине», конкретный покупатель, конкретный заказ № 5028 ¹. Множество однотипных сущностей, описываемых одинаковым набором свойств, называется типом сущности (англ. entity type) или множеством сущностей (англ. entity set): «Книга», «Покупатель», «Заказ». Отдельная сущность этого множества называется экземпляром (англ. instance).

На практике слово «сущность» почти всегда употребляют в смысле «тип сущности», а для отдельных объектов говорят «экземпляр»; в теме принято то же соглашение. Имена сущностей — существительные в единственном числе («Книга», а не «Книги»): диаграмма описывает, каким бывает один экземпляр и с чем он связан.

Сущность выделяют, если у понятия есть собственная идентичность и свойства, которые нужно хранить. Издательство — сущность: у него есть название и город, и при переименовании издательства все его книги должны «узнать» новое название. Переплёт книги — не сущность, а свойство книги: о самом переплёте хранить нечего.

Атрибуты и их виды

Атрибут (англ. attribute) — свойство сущности, значения которого хранятся для каждого экземпляра: название и год издания книги, адрес электронной почты покупателя. Множество допустимых значений атрибута называется доменом (англ. domain), как и в реляционной модели (тема 2). Чен различает атрибуты по нескольким независимым признакам ¹³:

• простой (англ. simple, atomic) атрибут не делится на части, имеющие самостоятельный смысл (год издания); составной (англ. composite) состоит из частей: «ФИО» из фамилии, имени и отчества, «адрес» из города, улицы и дома. Составной атрибут моделируют, если части нужны по отдельности (поиск по городу, сортировка по фамилии);
• однозначный (англ. single-valued) атрибут имеет у экземпляра одно значение (ISBN книги); многозначный (англ. multivalued) — несколько (телефоны издательства, ключевые слова книги);
• хранимый (англ. stored) атрибут вводится в базу; производный (англ. derived) вычисляется из других данных: число отзывов о книге, сумма заказа, возраст автора по году рождения;
• атрибут может быть необязательным: значение у части экземпляров отсутствует или неприменимо (город покупателя, год рождения автора). В реляционной схеме это станет столбцом, допускающим NULL (тема 2).

Деление относительно: для магазина адрес — одна строка на посылке, для службы доставки — составной атрибут, разбираемый на город и улицу. Решение определяется тем, какие запросы будут выполняться, — ещё одна причина начинать проектирование с перечня запросов.

Особое место занимает ключ (англ. key) сущности — атрибут или набор атрибутов, значение которых однозначно идентифицирует экземпляр: ISBN книги, адрес электронной почты покупателя. Понятия суперключа, потенциального, первичного, естественного и суррогатного ключа были введены в теме 2; на концептуальном уровне важно выявить естественные ключи предметной области (ISBN, ИНН организации, номер паспорта) и правила их уникальности, а решение о суррогатных ключах integer GENERATED ... AS IDENTITY относится к логическому проектированию. В «Книжном узле» ISBN объявлен уникальным, но первичным ключом выбран суррогатный book_id: у части изданий ISBN может отсутствовать, а ISBN, присвоенный по ошибке, придётся исправлять.

Связи, их степень и роли

Связь (англ. relationship) — ассоциация между экземплярами сущностей: «автор написал книгу», «покупатель оформил заказ», «книга относится к рубрике» ¹. Как и для сущностей, различают тип связи (множество однотипных ассоциаций) и экземпляр связи (конкретную пару «Аркадий Стругацкий — Пикник на обочине»). Формально тип связи между сущностями E1, …, En — это отношение, то есть подмножество декартова произведения E1 × … × En; связь — математическое отношение на множествах сущностей, и именно эта аналогия делает переход к реляционной модели естественным.

Степень связи (англ. degree) — число участвующих в ней сущностей. Подавляющее большинство связей бинарные (степень 2). Унарная, или рекурсивная, связь (англ. recursive relationship) соединяет сущность саму с собой: рубрика вложена в родительскую рубрику, сотрудник подчиняется руководителю. Связи степени 3 и выше (тернарные, n-арные) встречаются реже и требуют осторожности — им посвящён следующий раздел.

В рекурсивной связи один и тот же тип сущности участвует дважды, и, чтобы различать участников, им назначают роли (англ. role names): в связи «вложена в» рубрика выступает в ролях «потомок» и «родитель». В учебной базе эта связь реализована столбцом category.parent_id, ссылающимся на ту же таблицу; рубрики верхнего уровня имеют parent_id, равный NULL:

SELECT c.name AS category, p.name AS parent
FROM bookstore.category c
LEFT JOIN bookstore.category p ON p.category_id = c.parent_id
WHERE c.category_id IN (1, 13, 21)
ORDER BY c.category_id;

         category          |         parent
---------------------------+-------------------------
 Художественная литература |
 Базы данных               | Компьютерная литература
 PostgreSQL                | Базы данных
(3 rows)

Обход дерева на произвольную глубину — задача рекурсивных запросов (тема 6). На концептуальном уровне фиксируется, что у рубрики не более одного родителя и циклов нет; последнее правило внешним ключом не выражается и требует триггера или проверки в приложении.

Кардинальность и обязательность участия

Самое содержательное в связи — её ограничения. Кардинальность (англ. cardinality ratio), или максимальное число участий, указывает, со сколькими экземплярами другой сущности может быть связан один экземпляр ³. Для бинарных связей различают три случая:

• «один к одному» (1:1): экземпляр каждой стороны связан не более чем с одним экземпляром другой; пример — «покупатель — карта лояльности», если у покупателя бывает не более одной карты, а карта принадлежит одному покупателю;
• «один ко многим» (1:N): издательство выпускает много книг, но у книги не более одного издательства; покупатель оформляет много заказов, у заказа один покупатель;
• «многие ко многим» (M:N): у книги может быть несколько авторов, автор может написать несколько книг.

Обязательность участия (англ. participation constraint), или минимальное число участий, указывает, должен ли каждый экземпляр сущности участвовать в связи. Участие полное (англ. total, обязательное), если каждый экземпляр обязан участвовать: каждый заказ оформлен каким-то покупателем. Участие частичное (англ. partial, необязательное), если нет: покупатель может зарегистрироваться и не сделать ни одного заказа.

Оба ограничения удобно записывать парой (min, max) для каждой стороны связи: участие заказа в связи «оформил» — (1, 1), участие покупателя — (0, N). Такая запись точнее, чем «1:N», потому что различает, например, «у книги ноль или одно издательство» (0, 1) и «у книги ровно одно издательство» (1, 1). Следует внимательно относиться к тому, на какой стороне линии записывается пара: в нотации (min, max) по Элмасри и Навате пара ставится у сущности и говорит о её участии, тогда как в «вороньих лапках» символ у сущности говорит о том, сколько экземпляров этой сущности приходится на один экземпляр противоположной ³. Путаница сторон — самая частая ошибка чтения диаграмм.

Кардинальности — утверждения о предметной области, а не о текущих данных, но данные помогают проверить гипотезы. Например, утверждение «у книги до трёх авторов, а у автора может быть много книг» согласуется с учебной базой:

SELECT max(n) AS max_authors_per_book,
       (SELECT max(cnt) FROM (SELECT count(*) AS cnt FROM bookstore.book_author
                              GROUP BY author_id) s) AS max_books_per_author
FROM (SELECT count(*) AS n FROM bookstore.book_author GROUP BY book_id) t;

 max_authors_per_book | max_books_per_author
----------------------+----------------------
                    3 |                   22
(1 row)

Частичное участие покупателя в связи «оформил» тоже видно по данным: 621 покупатель из 5000 не сделал ни одного заказа.

SELECT count(*) AS customers,
       count(*) FILTER (WHERE NOT EXISTS (SELECT 1 FROM bookstore.orders o
                                          WHERE o.customer_id = c.customer_id)) AS without_orders
FROM bookstore.customer c;

 customers | without_orders
-----------+----------------
      5000 |            621
(1 row)

Но если данные не опровергают правило, это ещё не доказывает его: правило «у книги не более трёх авторов» в учебной базе выполняется, хотя магазин наверняка продаёт сборники с десятью авторами. Кардинальности устанавливаются по требованиям, а данные лишь помогают найти противоречия; тот же принцип в теме 8 будет сформулирован для функциональных зависимостей.

Не все кардинальности одинаково легко обеспечить. Правило «у заказа ровно один покупатель» выражается столбцом orders.customer_id NOT NULL с внешним ключом, а правило «в заказе есть хотя бы одна позиция» — минимум 1 на стороне «многих» — внешним ключом не выражается: схема учебной базы позволяет создать пустой заказ.

BEGIN;
INSERT INTO bookstore.orders (customer_id, status, warehouse_id)
VALUES (1, 'new', 1)
RETURNING order_id, status;
ROLLBACK;

 order_id | status
----------+--------
    20001 | new
(1 row)

Номер заказа в выводе зависит от предыдущих экспериментов: значение генератора идентификаторов не возвращается при откате транзакции. Такие правила обеспечиваются триггерами, отложенными проверками (тема 2) или логикой приложения — оформлением заказа и позиций в одной транзакции (темы 12 и 14).

Слабые сущности, атрибуты связей и связи высокой степени

Слабые сущности и идентифицирующие связи

Сильная, или обычная, сущность (англ. strong entity) имеет собственный ключ. Слабая сущность (англ. weak entity) собственного ключа не имеет: её экземпляры различимы только в пределах экземпляра другой, владеющей (англ. owner, identifying) сущности ¹³. Связь слабой сущности с владельцем называется идентифицирующей (англ. identifying relationship); участие слабой сущности в ней всегда полное, а кардинальность со стороны владельца — 1.

Классический пример — позиция заказа с номером строки: «позиция 3» сама по себе ничего не обозначает, а «позиция 3 заказа № 5028» — определённая строка. Атрибут, различающий экземпляры слабой сущности в пределах владельца, называется частичным ключом (англ. partial key, discriminator), а полный ключ слабой сущности составляется из ключа владельца и частичного ключа. Другие примеры — том многотомного издания (том 2 книги «Война и мир»), квартира в доме, глава в книге.

Из определения следует поведение при удалении: экземпляр слабой сущности не может существовать без владельца, поэтому удаление владельца должно удалять и зависимые экземпляры (ON DELETE CASCADE, тема 2) или быть запрещено. В учебной базе позиции заказа (order_item) удаляются вместе с заказом, а первичный ключ (order_id, book_id) включает ключ заказа. Роль частичного ключа здесь играет не номер строки, а книга: в одном заказе одна книга встречается не более одного раза, а количество экземпляров хранится в атрибуте quantity. Поэтому order_item правильнее считать не слабой сущностью в чистом виде, а ассоциативной сущностью связи «заказ — книга» (следующий подраздел); граница между этими понятиями на практике размыта, и обе конструкции отображаются в таблицы одинаково.

Слабость — свойство предметной области, а не реализации: суррогатный ключ order_item_id не сделал бы позицию независимой от заказа.

Атрибуты связей и ассоциативные сущности

Атрибуты могут быть не только у сущностей, но и у связей ¹. Порядок автора на обложке — не свойство книги (у книги несколько авторов) и не свойство автора (у автора несколько книг), а свойство пары «автор — книга», то есть атрибут связи «написал». Аналогично количество экземпляров книги на складе — атрибут связи «склад хранит книгу», а количество и цена в заказе — атрибуты связи «заказ содержит книгу».

Атрибут связи 1:N можно перенести на сторону «многих», а у связи M:N он остаётся атрибутом связи и попадает в таблицу связи: book_author.author_order, stock.quantity, order_item.quantity и order_item.price.

Если у связи появляются не только атрибуты, но и собственные связи с другими сущностями, её моделируют как ассоциативную сущность (англ. associative entity; в расширенной модели Элмасри и Наваты — агрегация) ³⁹. Пусть магазин принимает возвраты: покупатель возвращает конкретную позицию заказа, и о возврате хранятся дата, причина и количество. Возврат связан не с заказом в целом и не с книгой вообще, а с парой «этот заказ — эта книга», то есть с экземпляром связи «содержит». Значит, «позиция заказа» должна быть полноправной сущностью, на которую может ссылаться «возврат». В реляционной схеме это означает внешний ключ (order_id, book_id) из таблицы возвратов на таблицу order_item — задача лабораторной работы 7.

Тернарные связи и когда их нельзя заменить бинарными

Связь степени 3 соединяет три сущности одновременно. Пусть магазин работает с поставщиками и нужно хранить, какой поставщик какую книгу на какой склад поставляет. Экземпляр связи «поставляет» — тройка (поставщик, книга, склад). Возникает соблазн заменить её тремя бинарными связями: «поставщик поставляет книгу», «книга поступает на склад», «поставщик работает со складом». Такая замена в общем случае теряет информацию ³.

Проверим это на маленьком примере. Пусть известно ровно три факта о поставках:

CREATE TABLE t07.supply (supplier text, book text, warehouse text,
                         PRIMARY KEY (supplier, book, warehouse));
INSERT INTO t07.supply VALUES
    ('Логос',  'Война и мир', 'Казань'),
    ('Логос',  'Основание',   'Москва'),
    ('Вектор', 'Война и мир', 'Москва');

Построим три бинарные проекции — таблицы sb (supplier, book), bw (book, warehouse), sw (supplier, warehouse) командами CREATE TABLE ... AS SELECT DISTINCT — и попробуем восстановить тройки, соединив их:

SELECT sb.supplier, sb.book, bw.warehouse
FROM t07.sb
JOIN t07.bw ON bw.book = sb.book
JOIN t07.sw ON sw.supplier = sb.supplier AND sw.warehouse = bw.warehouse
EXCEPT
SELECT * FROM t07.supply;

 supplier |    book     | warehouse
----------+-------------+-----------
 Логос    | Война и мир | Москва
(1 row)

Соединение дало тройку, которой не было: «Логос» действительно поставляет «Войну и мир» (в Казань), «Война и мир» действительно поступает в Москву (от «Вектора»), «Логос» действительно работает с московским складом (поставляя «Основание»), но «Логос» не поставляет «Войну и мир» в Москву. Три бинарные связи описывают три проекции тернарной связи, а по проекциям исходное отношение в общем случае не восстанавливается. Это частный случай общего явления — декомпозиции с потерями, которое строго разбирается в теме 8 (там же — зависимости соединения и пятая нормальная форма, описывающие ровно тот случай, когда тернарную связь можно разложить на бинарные).

Отсюда правило моделирования: если факт по смыслу утверждает нечто об одновременном сочетании трёх сущностей («поставщик поставляет эту книгу на этот склад»), связь тернарная, и заменять её бинарными нельзя. Если же факты независимы («поставщик в принципе работает с этим складом» и «поставщик в принципе поставляет эту книгу»), в модели действительно две бинарные связи, а тернарная была бы ошибкой — она вынудила бы хранить все сочетания. Тернарную связь всегда можно корректно заменить ассоциативной сущностью «Поставка» с тремя обязательными бинарными связями «один ко многим» к поставщику, книге и складу; именно так тернарная связь и выглядит в нотациях, не допускающих связей степени выше двух (раздел о нотациях), и именно такой таблицей она реализуется.

Кардинальности тернарной связи читаются иначе, чем бинарной: число у сущности обозначает, сколько её экземпляров может приходиться на одну пару экземпляров двух других. Например, «1» у склада означает, что данный поставщик поставляет данную книгу не более чем на один склад; в реляционной схеме это превратится в ограничение UNIQUE (supplier, book).

Нотации ER-диаграмм

Нотация Чена

ER-модель обычно представляют диаграммой. Исходная нотация Чена ¹ использует прямоугольники для сущностей, ромбы для связей и овалы для атрибутов; линии соединяют атрибуты с их владельцами и сущности со связями, а у линий связи пишутся кардинальности «1», «N», «M». Дополнительные обозначения, закрепившиеся в учебниках ³²:

• ключевой атрибут подчёркивается;
• многозначный атрибут изображается двойным овалом, производный — пунктирным;
• составной атрибут — овалом, к которому присоединены овалы его частей;
• слабая сущность — двойным прямоугольником, идентифицирующая связь — двойным ромбом, частичный ключ подчёркивается пунктиром;
• полное участие — двойной линией, частичное — одинарной.

Диаграмма на рисунке ниже показывает фрагмент «Книжного узла» в нотации Чена: связь «написал» типа M:N с атрибутом «порядок», связь «выпустило» типа 1:N с частичным участием книги, составной атрибут «ФИО», многозначный атрибут «телефоны» и производный атрибут «число книг».

Достоинство нотации Чена — выразительность: связи — полноправные элементы диаграммы с именами и атрибутами, легко изображаются связи любой степени. Недостаток — громоздкость: диаграмма реальной схемы с сотней атрибутов-овалов нечитаема, поэтому в инженерной практике преобладает более компактная нотация «вороньих лапок».

Нотация «вороньи лапки»

В нотации «вороньи лапки» (англ. crow's foot notation) сущность изображается прямоугольником, внутри которого перечислены атрибуты, а связь — линией между прямоугольниками без ромба ⁵². Нотация получила распространение благодаря методологии информационного инжиниринга (англ. Information Engineering, IE) Дж. Мартина и К. Финкельштейна, поэтому её также называют нотацией IE; её вариант, описанный Р. Баркером, использовался в инструментах Oracle CASE ⁵.

Кардинальность и обязательность изображаются символами на концах линии, причём символ у сущности говорит о том, сколько её экземпляров может приходиться на один экземпляр сущности на другом конце. Внешний символ (ближе к прямоугольнику) задаёт максимум, внутренний — минимум:

• черта «|» — один, кружок «○» — ноль, «лапка» из трёх расходящихся линий — много;
• «||» — ровно один, «○|» — ноль или один, «|<» (лапка с чертой) — один или много, «○<» — ноль или много.

Связь читается от одной сущности к другой по символу у дальнего конца: «один покупатель оформляет ноль или много заказов» — смотрим на символ у заказа; «один заказ оформлен ровно одним покупателем» — на символ у покупателя. Связи M:N в логической модели «вороньих лапок» обычно сразу заменяют ассоциативной сущностью с двумя связями 1:N; на концептуальных диаграммах допускается и прямая линия с лапками на обоих концах. Многие инструменты различают сплошной линией идентифицирующую связь (ключ родителя входит в ключ потомка) и пунктирной — неидентифицирующую.

Диаграмма учебной базы «Книжный узел» в этой нотации приведена на рисунке ниже. Она построена по схеме schema.sql и запросам к системному каталогу из последнего раздела; для компактности показаны только ключевые и наиболее важные атрибуты.

По диаграмме читаются бизнес-правила из начала темы: у книги ноль или одно издательство («○|» у publisher), ровно одна рубрика, авторы через book_author; у рубрики ноль или один родитель; на заказ приходится ноль или несколько платежей. Видны и два расхождения схемы с требованиями. Со стороны позиций заказа стоит минимум «○», хотя заказ без позиций бессмыслен (схема этого не запрещает). Связь «заказ — склад» имеет вид «○|», так как warehouse_id допускает NULL, хотя правило «заказ отгружается с одного склада» подразумевает «||»; возможно, NULL разрешён сознательно — для нового заказа, склад которого ещё не выбран. Такие расхождения и есть предмет обсуждения при проектировании.

UML-диаграммы классов и IDEF1X

В объектно-ориентированной разработке для тех же целей используют диаграммы классов унифицированного языка моделирования (англ. Unified Modeling Language, UML), стандартизованного консорциумом OMG ⁶. Сущности соответствует класс с атрибутами, связи — ассоциация, а кардинальности записываются кратностями (англ. multiplicity) у концов ассоциации: 1, 0..1, * (то же, что 0..*), 1..*. Кратность, как и в «вороньих лапках», пишется у того класса, о числе экземпляров которого идёт речь. Атрибуты связи M:N изображаются классом-ассоциацией (англ. association class), присоединённым к линии ассоциации пунктиром; обобщение — линией с незаполненным треугольником у суперкласса; композиция (зависимая часть, аналог слабой сущности) — линией с закрашенным ромбом у владельца. Стандартного обозначения ключей в UML нет, его добавляют стереотипами или комментариями.

Нотация IDEF1X (англ. Integration Definition for Information Modeling) утверждена в США как федеральный стандарт FIPS PUB 184 в 1993 году ⁷. Она ближе всех к реляционной схеме: у каждой сущности над чертой перечисляются атрибуты первичного ключа, под чертой — остальные; внешние ключи помечаются (FK). Независимые сущности изображаются прямоугольниками с прямыми углами, зависимые (ключ которых включает ключ родителя) — со скруглёнными; идентифицирующая связь — сплошной линией, неидентифицирующая — пунктирной; на конце «многих» ставится закрашенная точка, возле которой может стоять P (один или более), Z (ноль или один) или число.

В курсе нотация Чена используется для объяснения понятий, «вороньи лапки» — для диаграмм схем.

Обобщение, специализация и наследование в таблицах

Подтипы и супертипы

Часто экземпляры одной сущности делятся на группы с разными наборами атрибутов. Покупателем магазина может быть физическое лицо (ФИО, дата рождения) или организация (наименование, ИНН, КПП); у обоих есть адрес электронной почты, история заказов и отзывы. Расширенная модель «сущность — связь» (англ. Enhanced ER, EER) описывает такую ситуацию отношением «супертип — подтип» (англ. supertype, subtype), или «суперкласс — подкласс» ³⁹.

Специализация (англ. specialization) — процесс выделения подтипов в существующей сущности: «покупатель» специализируется на «физическое лицо» и «организацию». Обобщение (англ. generalization) — обратный процесс: из сущностей «физическое лицо» и «организация» с общими атрибутами выделяется общий супертип «покупатель». Понятие обобщения как абстракции, на равных с агрегацией, ввели в теорию баз данных Дж. и Д. Смит в 1977 году ⁹. Экземпляр подтипа является и экземпляром супертипа (связь «является», англ. is-a) и наследует все его атрибуты и связи; у подтипа могут быть собственные атрибуты и собственные связи: например, только с организацией связан «договор поставки».

Специализацию характеризуют два независимых ограничения:

• непересечение (англ. disjointness): специализация непересекающаяся (англ. disjoint), если экземпляр может принадлежать не более чем одному подтипу (покупатель — либо физическое лицо, либо организация), и перекрывающаяся (англ. overlapping), если нескольким (человек может быть одновременно автором и покупателем);
• полнота (англ. completeness): специализация полная (англ. total), если каждый экземпляр супертипа принадлежит хотя бы одному подтипу, и частичная (англ. partial), если могут существовать экземпляры супертипа без подтипа.

Специализация «физическое лицо / организация» — непересекающаяся и полная. Подтипы оправданы, только если у них разные атрибуты или связи; иначе достаточно атрибута «категория».

Три способа отображения иерархии в таблицы

Реляционная модель не знает наследования, поэтому иерархию типов приходится отображать в таблицы. Существуют три основных способа; в литературе по объектно-реляционному отображению их называют по Фаулеру ⁸, в учебниках по базам данных — по стилю: «стиль ER», «объектно-ориентированный стиль», «стиль с NULL» ¹⁰. Схема на рисунке ниже сравнивает их для покупателей — физических лиц и организаций.

Одна таблица на иерархию (англ. single table inheritance): все атрибуты всех подтипов собираются в одну таблицу, добавляется столбец-признак типа (англ. discriminator). Атрибуты чужого подтипа остаются пустыми, а правило «у физического лица есть ФИО и нет ИНН» выражается ограничением CHECK:

CREATE TABLE t07.client_single (
    client_id  integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email      text NOT NULL UNIQUE,
    kind       text NOT NULL CHECK (kind IN ('person', 'org')),
    full_name  text,              -- только для физического лица
    org_name   text,              -- только для организации
    inn        text UNIQUE,       -- только для организации
    CHECK (kind = 'person' AND full_name IS NOT NULL AND org_name IS NULL AND inn IS NULL
        OR kind = 'org'    AND org_name IS NOT NULL AND inn IS NOT NULL AND full_name IS NULL)
);
INSERT INTO t07.client_single (email, kind, full_name, inn)
VALUES ('x@example.com', 'person', 'Иван Жуков', '7700000000');

ERROR:  new row for relation "client_single" violates check constraint "client_single_check"
DETAIL:  Failing row contains (3, x@example.com, person, Иван Жуков, null, 7700000000).

Таблица на каждый класс (англ. class table inheritance): общие атрибуты хранятся в таблице супертипа, собственные атрибуты каждого подтипа — в отдельной таблице, первичный ключ которой одновременно является внешним ключом на супертип (связь 1:1). Чтобы СУБД обеспечивала и непересечение — одна строка client не может иметь и «физическое», и «юридическое» продолжение, — признак типа включают во внешний ключ:

CREATE TABLE t07.client (
    client_id  integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email      text NOT NULL UNIQUE,
    kind       text NOT NULL CHECK (kind IN ('person', 'org')),
    UNIQUE (client_id, kind)
);
CREATE TABLE t07.client_person (
    client_id  integer PRIMARY KEY,
    kind       text NOT NULL DEFAULT 'person' CHECK (kind = 'person'),
    full_name  text NOT NULL,
    FOREIGN KEY (client_id, kind) REFERENCES t07.client (client_id, kind) ON DELETE CASCADE
);
-- client_org — аналогично: kind = 'org', org_name, inn NOT NULL UNIQUE

После вставки физического лица с client_id = 1 попытка добавить ему «организационное» продолжение отвергается:

INSERT INTO t07.client_org (client_id, org_name, inn) VALUES (1, 'Лицей № 1', '7701234567');

ERROR:  insert or update on table "client_org" violates foreign key constraint "client_org_client_id_kind_fkey"
DETAIL:  Key (client_id, kind)=(1, org) is not present in table "client".

Полноту (у каждой строки client есть продолжение) декларативно обеспечить не удаётся — нужна транзакция, вставляющая обе строки, или триггер.

Таблица на конкретный класс (англ. concrete table inheritance): таблицы создаются только для подтипов, и каждая содержит все атрибуты — и унаследованные, и собственные. Таблицы супертипа нет. Цена — потеря общих ограничений: уникальность адреса электронной почты и идентификатора теперь обеспечивается в каждой таблице отдельно, и ничто не мешает физическому лицу и организации получить одинаковые значения:

SELECT client_id, email, 'person' AS kind FROM t07.person_client
UNION ALL
SELECT client_id, email, 'org' FROM t07.org_client;

 client_id |      email       |  kind
-----------+------------------+--------
         1 | same@example.com | person
         1 | same@example.com | org
(2 rows)

Кроме того, на «покупателя вообще» нельзя сослаться внешним ключом: таблице заказов понадобятся два необязательных внешних ключа с проверкой «заполнен ровно один».

Сравнение способов

Одна таблица проста и быстра, когда подтипов мало и собственных атрибутов немного; таблица на класс точнее всего отражает модель, но требует соединений; таблица на конкретный класс уместна, если на супертип никто не ссылается. В PostgreSQL есть собственный механизм наследования таблиц (CREATE TABLE ... INHERITS), но ограничения уникальности и внешние ключи на нём не распространяются на всю иерархию, что документация прямо оговаривает как ограничение механизма ¹³; поэтому для моделирования подтипов его обычно не применяют, а используют для других задач (исторически — для секционирования, тема 9).

Преобразование ER-модели в реляционную схему

Правила для сущностей и атрибутов

Преобразование ER-модели в реляционную схему выполняется по набору правил, которые почти не требуют творческих решений ³¹¹. Для сущностей и атрибутов они таковы.

1. Сильная сущность становится таблицей. Простые однозначные атрибуты — столбцами; один из потенциальных ключей выбирается первичным ключом, остальные объявляются UNIQUE; при необходимости вводится суррогатный ключ (тема 2). Необязательные атрибуты допускают NULL, обязательные получают NOT NULL.
2. Составной атрибут раскладывается на простые столбцы-части (last_name, first_name); сам составной атрибут как целое в таблице не хранится.
3. Многозначный атрибут выносится в отдельную таблицу с внешним ключом на владельца и первичным ключом «ключ владельца + значение»: телефоны издательства — таблица publisher_phone (publisher_id, phone). Хранить несколько значений в одном столбце через запятую — нарушение первой нормальной формы, последствия которого разобраны в теме 8.
4. Производный атрибут обычно не хранится, а вычисляется запросом или представлением; если вычисление дорого, его можно хранить, но тогда нужен механизм поддержания согласованности — это сознательная денормализация (тема 8, механизмы — тема 14).
5. Слабая сущность становится таблицей, в которую добавляется внешний ключ на владельца; первичный ключ — ключ владельца плюс частичный ключ; внешний ключ обязательный, обычно с ON DELETE CASCADE.

Правила для связей

Связи отображаются по-разному в зависимости от кардинальности.

1. Связь 1:N реализуется внешним ключом в таблице стороны «многих», ссылающимся на таблицу стороны «одного»: book.publisher_id ссылается на publisher. Полное участие стороны «многих» превращается в NOT NULL (book.category_id), частичное — в допустимость NULL (book.publisher_id). Атрибуты связи переносятся в ту же таблицу.
2. Связь 1:1 реализуется внешним ключом в одной из таблиц с ограничением UNIQUE на этом столбце — именно UNIQUE отличает 1:1 от 1:N. Внешний ключ ставят на стороне с полным участием: если каждая карта лояльности принадлежит покупателю, а не каждый покупатель имеет карту, столбец customer_id NOT NULL UNIQUE размещают в таблице карт. Если участие обеих сторон полное и у сущностей нет собственных связей, их можно слить в одну таблицу.
3. Связь M:N всегда требует отдельной таблицы связи (англ. junction table, associative table) с внешними ключами на обе сущности; первичный ключ — пара внешних ключей (если одна и та же пара не может встречаться дважды), атрибуты связи — дополнительные столбцы: book_author (book_id, author_id, author_order).
4. Связь степени n реализуется таблицей с n внешними ключами. Её первичный ключ определяется кардинальностями: если все стороны «многие», ключ — все n внешних ключей; если у одной стороны «1», эта сторона в ключ не входит, как в примере с поставками: UNIQUE (supplier, book).
5. Рекурсивная связь реализуется внешним ключом таблицы на саму себя (1:N, как category.parent_id) или таблицей связи с двумя внешними ключами на одну таблицу (M:N, например «книга рекомендуется вместе с книгой»); имена столбцов отражают роли.
6. Иерархия подтипов — одним из трёх способов, рассмотренных выше.

Правила затрагивают только структуру. Бизнес-правила, не выражаемые кардинальностями, превращаются в ограничения CHECK (оценка от 1 до 5), UNIQUE (один отзыв на книгу от покупателя), исключающие ограничения или триггеры (тема 14). Схема, полученная по этим правилам из аккуратной ER-модели, обычно находится в третьей нормальной форме, но гарантии этого нет: если в сущность попал атрибут, зависящий от другого неключевого атрибута (город издательства в сущности «Книга»), правила перенесут избыточность в таблицу. Строгая проверка — предмет темы 8.

Восстановление модели по системному каталогу

Часто решается обратная задача — понять модель существующей базы без документации; её называют обратным проектированием (англ. reverse engineering). Нужная информация есть в системном каталоге: таблицы, столбцы, допустимость NULL, первичные, уникальные и внешние ключи. Стандарт SQL определяет для доступа к ней информационную схему information_schema — набор представлений, одинаковый (с точностью до особенностей реализации) в разных СУБД ¹².

Связи между таблицами — это внешние ключи. Их перечисляет следующий запрос: представление table_constraints содержит ограничения, key_column_usage — столбцы, на которых ограничение определено, constraint_column_usage — столбцы, на которые оно ссылается, columns — допустимость NULL:

SELECT tc.table_name        AS child,
       kcu.column_name      AS fk_column,
       ccu.table_name       AS parent,
       c.is_nullable        AS nullable
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
     ON kcu.constraint_schema = tc.constraint_schema
    AND kcu.constraint_name = tc.constraint_name
JOIN information_schema.constraint_column_usage ccu
     ON ccu.constraint_schema = tc.constraint_schema
    AND ccu.constraint_name = tc.constraint_name
JOIN information_schema.columns c
     ON c.table_schema = tc.table_schema
    AND c.table_name = tc.table_name
    AND c.column_name = kcu.column_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_schema = 'bookstore'
ORDER BY child, fk_column;

    child    |  fk_column   |  parent   | nullable
-------------+--------------+-----------+----------
 book        | category_id  | category  | NO
 book        | publisher_id | publisher | YES
 book_author | author_id    | author    | NO
 book_author | book_id      | book      | NO
 category    | parent_id    | category  | YES
 order_item  | book_id      | book      | NO
 order_item  | order_id     | orders    | NO
 orders      | customer_id  | customer  | NO
 orders      | warehouse_id | warehouse | YES
 payment     | order_id     | orders    | NO
 review      | book_id      | book      | NO
 review      | customer_id  | customer  | NO
 stock       | book_id      | book      | NO
 stock       | warehouse_id | warehouse | NO
(14 rows)

Каждая строка — связь «один ко многим» от parent к child; столбец nullable даёт минимум на стороне родителя: NO — «ровно один» (||), YES — «ноль или один» (○|). Таблицы, у которых два внешних ключа входят в первичный ключ (book_author, stock, order_item), — ассоциативные сущности, реализующие связи M:N; столбцы первичных ключей перечисляет то же представление key_column_usage при constraint_type = 'PRIMARY KEY'. Строка category → category — рекурсивная связь. Связей 1:1 в учебной базе нет: их выдало бы ограничение UNIQUE на столбце внешнего ключа. Чего каталог не расскажет — это минимум на стороне «многих» (есть ли у каждой книги хотя бы один автор) и смысл связей: их имена восстанавливаются по названиям таблиц и знанию предметной области.

Запрос имеет два ограничения, которые нужно знать. Во-первых, constraint_column_usage не сообщает, какой столбец родителя соответствует какому столбцу потомка, поэтому для составных внешних ключей запрос даст декартово произведение столбцов; для них нужно соединять key_column_usage с referential_constraints по position_in_unique_constraint или обращаться к каталогу PostgreSQL pg_constraint (массивы conkey и confkey). Во-вторых, представления информационной схемы показывают только объекты, к которым у текущей роли есть доступ, а constraint_column_usage — только таблицы, принадлежащие текущей роли ¹². Действия при удалении (ON DELETE CASCADE у book_author.book_id и order_item.order_id) можно получить из представления referential_constraints: столбец delete_rule.

По результату такого запроса можно автоматически построить текст диаграммы для инструментов, принимающих текстовое описание. Например, в синтаксисе Mermaid ¹⁴ связи учебной базы записываются строками вида:

erDiagram
    publisher |o--o{ book : "выпускает"
    category  ||--o{ book : "содержит"
    category  |o--o{ category : "родитель"
    book      ||--o{ book_author : ""
    author    ||--o{ book_author : ""

Здесь || и |o — «ровно один» и «ноль или один», o{ и |{ — «ноль или много» и «один или много»; два дефиса обозначают идентифицирующую связь (сплошная линия), две точки — неидентифицирующую (пунктир). Построение полной диаграммы учебной базы по каталогу — задание лабораторной работы 7.

Кроме текстовых описаний диаграмм (Mermaid, PlantUML, язык DBML сервиса dbdiagram.io) существуют графические редакторы (draw.io), средства моделирования с генерацией DDL и обратным проектированием (pgModeler, ER-редактор DBeaver) и генераторы документации по живой базе (SchemaSpy). Текстовые описания удобно хранить в системе контроля версий рядом с миграциями схемы (тема 15).

Типичные ошибки моделирования

Атрибут вместо сущности. Если понятие имеет собственные свойства или участвует в связях, его нельзя делать атрибутом. Город покупателя и город склада в учебной базе — текстовые атрибуты; это оправдано, пока о городе ничего не нужно хранить. Если появится требование «срок доставки зависит от пары „город склада — город покупателя“», город станет сущностью, иначе срок пришлось бы привязывать к строке текста, в которой «Санкт-Петербург» и «С.-Петербург» — разные значения. Самый распространённый вариант этой ошибки — список через запятую: «авторы» как текстовое поле книги вместо связи M:N; его последствия подробно разбираются в теме 8 и в лабораторной работе 8.

Сущность вместо атрибута — обратная ошибка: сущность «Переплёт» с единственным атрибутом «название». Она безвредна для целостности, но добавляет соединения; выбор между справочником, перечислимым типом и CHECK обсуждается в теме 9.

Связь вместо сущности. Связь, у которой появились собственные связи, должна стать ассоциативной сущностью (пример с возвратами выше). Признак — фраза вида «… относится к конкретной позиции заказа».

Ловушка «веер» (англ. fan trap) возникает, когда из одной сущности выходят две связи 1:N, а вопрос касается связи между двумя «дальними» сущностями ⁴. Пусть модель содержит «Город — Склад» (1:N) и «Город — Покупатель» (1:N). Вопрос «с какого склада обслуживается покупатель» по такой модели не решается: если в городе два склада, путь «покупатель — город — склад» даёт оба. Связь, которую хотели выразить, либо прямая («покупатель прикреплён к складу»), либо определяется событиями («заказ отгружен со склада»), и её нужно моделировать явно. В учебной базе «веер» не возникает потому, что склад указан непосредственно в заказе (orders.warehouse_id), а не выводится через город.

Ловушка «разрыв» (англ. chasm trap) возникает, когда путь между сущностями существует, но проходит через связь с частичным участием, и для части экземпляров обрывается ⁴. Если город издания книги получать через издательство, книги без издательства (или издательства без города) из ответа выпадают:

SELECT count(*) AS books, count(p.city) AS with_pub_city
FROM bookstore.book b
LEFT JOIN bookstore.publisher p USING (publisher_id)
WHERE b.category_id = 12;   -- рубрика «Программирование»

 books | with_pub_city
-------+---------------
   119 |            99

Для двадцати книг рубрики, включая «Искусство программирования» Кнута без издательства, город издания так не узнать; если он важен, это должен быть атрибут книги, а не вывод через необязательную связь.

Избыточная связь выводится из других: при наличии «Покупатель оформил заказ» и «Заказ отгружен со склада» связь «Покупатель получал книги со склада» — композиция двух первых, и её отдельное хранение грозит рассогласованием. Но не всякая связь, похожая на композицию, избыточна: «покупатель оставил отзыв о книге» не выводится из «покупатель заказал книгу» — купленную книгу можно не оценивать, а правила магазина могут разрешать отзывы на книги, купленные в другом месте. Избыточность определяется по смыслу, а не по форме диаграммы.

Итоги темы

Проектирование базы данных разделяется на этапы, отвечающие на вопросы разной природы: анализ требований фиксирует словарь и бизнес-правила, концептуальное проектирование описывает предметную область в терминах сущностей и связей, логическое — переводит описание в реляционную схему, физическое — в объекты конкретной СУБД. Концептуальная модель не зависит от реализации, служит языком общения с заказчиком и позволяет обнаружить ошибки до того, как в схеме появятся данные.

Модель «сущность — связь» описывает предметную область сущностями с ключами, атрибутами разных видов и связями, у которых есть степень, роли, кардинальность и обязательность участия. Слабые сущности идентифицируются через владельца, связи могут иметь атрибуты и становиться ассоциативными сущностями, а тернарная связь в общем случае не сводится к трём бинарным: её проекции, соединённые обратно, порождают ложные факты.

Нотации различаются выразительностью и компактностью: нотация Чена удобна для объяснения понятий, «вороньи лапки» и IDEF1X — для схем реального размера, UML — когда модель данных обсуждается вместе с программой. Иерархии подтипов отображаются в таблицы тремя способами — одной таблицей, таблицей на класс или таблицей на конкретный класс, — и выбор между ними — компромисс между числом пустых значений, обеспечением ограничений средствами СУБД и стоимостью запросов.

Преобразование ER-модели в таблицы выполняется по почти механическим правилам: сущность — таблица, связь 1:N — внешний ключ, 1:1 — внешний ключ с UNIQUE, M:N и n-арная связь — отдельная таблица. Обратный путь — восстановление модели по системному каталогу — показан на учебной базе запросом к information_schema. Типичные ошибки — атрибут вместо сущности, ловушки «веер» и «разрыв», избыточные связи — обнаруживаются по смыслу модели, а не по её форме.

Правила преобразования гарантируют корректную структуру, но не отсутствие избыточности: если в сущность попал атрибут, зависящий не от ключа, а от другого атрибута, таблица унаследует аномалии обновления. Строгий аппарат для обнаружения и устранения таких проблем — функциональные зависимости и нормальные формы — рассматривается в теме 8.

Литература

1. Chen P. P.-S. The Entity-Relationship Model — Toward a Unified View of Data. — ACM Transactions on Database Systems, 1976, Т. 1, № 1, С. 9–36, DOI: 10.1145/320434.320440.
2. Silberschatz A., Korth H. F., Sudarshan S. Database System Concepts. 7th ed. — McGraw-Hill, 2019.
3. Elmasri R., Navathe S. B. Fundamentals of Database Systems. 7th ed. — Pearson, 2016.
4. Connolly T., Begg C. Database Systems: A Practical Approach to Design, Implementation, and Management. 6th ed. — Pearson, 2015.
5. Barker R. CASE*Method: Entity Relationship Modelling. — Addison-Wesley, 1990.
6. Object Management Group. OMG Unified Modeling Language (OMG UML). Version 2.5.1. — OMG, 2017. https://www.omg.org/spec/UML/2.5.1
7. National Institute of Standards and Technology. Integration Definition for Information Modeling (IDEF1X). — FIPS PUB 184, 1993.
8. Fowler M. Patterns of Enterprise Application Architecture. — Addison-Wesley, 2002.
9. Smith J. M., Smith D. C. P. Database Abstractions: Aggregation and Generalization. — ACM Transactions on Database Systems, 1977, Т. 2, № 2, С. 105–133, DOI: 10.1145/320544.320546.
10. Garcia-Molina H., Ullman J. D., Widom J. Database Systems: The Complete Book. 2nd ed. — Pearson Prentice Hall, 2008.
11. Teorey T. J., Yang D., Fry J. P. A Logical Design Methodology for Relational Databases Using the Extended Entity-Relationship Model. — ACM Computing Surveys, 1986, Т. 18, № 2, С. 197–222, DOI: 10.1145/7474.7475.
12. PostgreSQL Global Development Group. PostgreSQL 16 Documentation. The Information Schema. — 2024. https://www.postgresql.org/docs/16/information-schema.html
13. PostgreSQL Global Development Group. PostgreSQL 16 Documentation. Section 5.10. Inheritance. — 2024. https://www.postgresql.org/docs/16/ddl-inherit.html
14. Mermaid. Entity Relationship Diagrams. — Документация проекта Mermaid. https://mermaid.js.org/syntax/entityRelationshipDiagram.html

Дополнительные материалы

Вопросы для самопроверки

1. Чем концептуальная модель отличается от логической? К какому уровню относятся утверждения «у книги может быть несколько авторов», «таблица book_author имеет первичный ключ (book_id, author_id)» и «столбец price имеет тип numeric(10,2)»?
2. Приведите пример атрибута, который в одной системе разумно считать простым, а в другой — составным. От чего зависит выбор?
3. Почему производный атрибут «число отзывов о книге» обычно не хранят? Что придётся сделать, если его всё же решили хранить?
4. Запишите парами (min, max) обе стороны связей «издательство — книга», «покупатель — заказ» и «покупатель — карта лояльности» в «Книжном узле» с расширением из лабораторной работы 7.
5. На диаграмме «вороньи лапки» у сущности «Заказ» на линии к «Платежу» стоит «||», а у «Платежа» — «○<». Прочитайте связь в обе стороны.
6. Чем слабая сущность отличается от ассоциативной? Почему order_item учебной базы можно отнести к обеим категориям?
7. Дана тернарная связь «поставщик поставляет книгу на склад». Приведите набор фактов, при котором замена её тремя бинарными связями даёт ложную тройку. Когда, наоборот, тернарная связь была бы ошибкой?
8. Что означает «1» у склада в тернарной связи «поставщик — книга — склад»? Каким ограничением это выражается в таблице?
9. Почему связь 1:1 «покупатель — карта» реализуют внешним ключом в таблице карт, а не в таблице покупателей? Что изменилось бы, если бы каждая карта была необязательной, а каждый покупатель обязан был иметь карту?
10. Как с помощью внешнего ключа (client_id, kind) обеспечить непересечение подтипов при отображении «таблица на каждый класс»? Почему так нельзя обеспечить полноту?
11. Какие сведения о модели нельзя получить из системного каталога? Почему запрос к constraint_column_usage неверен для составных внешних ключей?
12. Объясните ловушки «веер» и «разрыв» на примерах, отличных от приведённых в теории.
13. Почему хранение списка ISBN через запятую в одном столбце — ошибка моделирования, а не только «неудобство»? Какие гарантии СУБД при этом теряются?
14. Почему механизм наследования таблиц PostgreSQL (INHERITS) обычно не применяют для моделирования подтипов?

Глоссарий темы

Шпаргалка по инструментам

Конструкции ER-модели в DDL

-- 1:N, полное участие книги: у книги ровно одна рубрика
category_id integer NOT NULL REFERENCES category (category_id)
-- 1:N, частичное участие: у книги ноль или одно издательство
publisher_id integer REFERENCES publisher (publisher_id)
-- 1:1: у покупателя не более одной карты, у карты ровно один владелец
customer_id integer NOT NULL UNIQUE REFERENCES customer (customer_id)
-- M:N с атрибутом связи
CREATE TABLE book_author (
    book_id      integer REFERENCES book (book_id) ON DELETE CASCADE,
    author_id    integer REFERENCES author (author_id),
    author_order smallint NOT NULL,
    PRIMARY KEY (book_id, author_id)
);
-- ссылка на ассоциативную сущность — составной внешний ключ
FOREIGN KEY (order_id, book_id) REFERENCES order_item (order_id, book_id)
-- значение из списка и интервал
CHECK (level IN ('basic', 'silver', 'gold'))
CHECK (ends_on >= starts_on)
-- не более одной строки с признаком у владельца (частичный уникальный индекс, тема 10)
CREATE UNIQUE INDEX ON delivery_address (customer_id) WHERE is_default;
-- тернарная связь, где на пару (поставщик, книга) приходится не более одного склада
UNIQUE (supplier_id, book_id)

Mermaid erDiagram

erDiagram
    publisher |o..o{ book : "выпускает"
    book ||--o{ book_author : "написана"
    orders ||--|{ order_item : "содержит"
    book {
        integer book_id PK
        text isbn UK
        integer publisher_id FK
        text title "обязательно"
    }

Концы связи (символ ближе к имени сущности — максимум, дальний — минимум):

• || — ровно один (одинаково слева и справа);
• |o слева, o| справа — ноль или один;
• }| слева, |{ справа — один или много;
• }o слева, o{ справа — ноль или много;
• -- — идентифицирующая связь (сплошная линия), .. — неидентифицирующая (пунктир).

Каталог

-- первичные ключи таблиц схемы
SELECT kcu.table_name,
       string_agg(kcu.column_name, ', ' ORDER BY kcu.ordinal_position) AS pk
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
     ON kcu.constraint_schema = tc.constraint_schema
    AND kcu.constraint_name = tc.constraint_name
WHERE tc.table_schema = 'bookstore' AND tc.constraint_type = 'PRIMARY KEY'
GROUP BY kcu.table_name;

-- внешние ключи с правильным сопоставлением столбцов (и для составных)
SELECT c.conrelid::regclass AS child, ca.attname AS child_column,
       c.confrelid::regclass AS parent, pa.attname AS parent_column,
       c.confdeltype AS on_delete          -- a, r, c, n, d
FROM pg_constraint c
CROSS JOIN LATERAL unnest(c.conkey, c.confkey) AS k(child_att, parent_att)
JOIN pg_attribute ca ON ca.attrelid = c.conrelid AND ca.attnum = k.child_att
JOIN pg_attribute pa ON pa.attrelid = c.confrelid AND pa.attnum = k.parent_att
WHERE c.contype = 'f' AND c.connamespace = 'bookstore'::regnamespace;

Коды confdeltype: a — NO ACTION, r — RESTRICT, c — CASCADE, n — SET NULL, d — SET DEFAULT. Представление information_schema.referential_constraints даёт то же словами (delete_rule).

Метакоманды psql

Что почитать и посмотреть

• Chen P. P.-S. The Entity-Relationship Model — Toward a Unified View of Data (ACM TODS, 1976). Исходная статья: сущности, связи, роли, слабые сущности и первая нотация с ромбами. Читается легко и показывает, что модель задумывалась как объединяющий взгляд на сетевую, реляционную и табличную модели.
• Elmasri R., Navathe S. B. «Fundamentals of Database Systems» — главы об ER- и EER-моделировании и об отображении их в реляционную схему. Наиболее подробное учебное изложение нотации (min, max), специализации и правил преобразования.
• Silberschatz A., Korth H. F., Sudarshan S. «Database System Concepts», 7-е изд., глава 6 «Database Design Using the E-R Model». Компактное изложение с упражнениями и сравнением нотаций; сайт книги — db-book.com.
• Karwin B. «SQL Antipatterns: Avoiding the Pitfalls of Database Programming» (Pragmatic Bookshelf, 2010). Главы «Jaywalking» (списки через запятую), «Polymorphic Associations» и «Entity-Attribute-Value» — ровно те ошибки моделирования, которые разбирались в части 5 лабораторной работы, с последствиями и исправлениями.
• Hay D. C. «Data Model Patterns: Conventions of Thought» (Dorset House, 1996). Каталог типовых фрагментов моделей (стороны договора, адреса, товары, заказы); полезен, чтобы не изобретать заново модель «физическое лицо / организация».
• Fowler M. Single Table Inheritance, Class Table Inheritance, Concrete Table Inheritance — краткие описания трёх способов отображения иерархии из каталога шаблонов книги «Patterns of Enterprise Application Architecture».
• Mermaid: Entity Relationship Diagrams и редактор mermaid.live. Полный синтаксис: кардинальности, идентифицирующие связи, атрибуты, ключи и комментарии.
• DBML — язык описания схем сервиса dbdiagram.io, и PlantUML: IE-диаграммы. Альтернативные текстовые нотации; DBML умеет генерировать DDL для PostgreSQL.
• Документация PostgreSQL 16: The Information Schema и pg_constraint. Описание представлений, использованных в работе, и ограничения видимости объектов по правам роли.
• pgModeler и SchemaSpy — графический редактор моделей PostgreSQL с обратным проектированием и генератор HTML-документации со схемой связей по живой базе.
• Entity–relationship model в англоязычной Википедии — обзор нотаций с иллюстрациями и историческими ссылками.

Углубление (по желанию)

Диаграммы до Чена

Графическое изображение структуры данных появилось раньше ER-модели. Ч. Бахман в 1969 году описал диаграммы структуры данных для сетевых баз данных: прямоугольники — типы записей, стрелки — «наборы» (связи «владелец — член» типа 1:N). Связей M:N сетевая модель CODASYL напрямую не поддерживала, и их приходилось реализовывать промежуточным типом записи — тем же приёмом, что и таблица связи в реляционной схеме. Заслуга Чена в том, что он отделил описание предметной области от модели данных: в ER-модели связь M:N или тернарная связь — полноправный элемент, а способ её хранения решается позже. Нотация «вороньи лапки» в некотором смысле вернулась к диаграммам Бахмана, заменив стрелки символами кардинальности.

Когда тернарную связь всё же можно разложить

В теме 7 показано, что три бинарные проекции тернарной связи в общем случае порождают ложные факты. Но бывают предметные области, где правило «если поставщик поставляет книгу, книга поступает на склад и поставщик работает со складом, то поставщик поставляет эту книгу на этот склад» выполняется по смыслу (например, по договору поставщик обязан везти весь свой ассортимент на все свои склады). Тогда тройки однозначно восстанавливаются из пар, хранение тернарной связи избыточно, и её корректно заменить тремя бинарными. Такое правило называется зависимостью соединения, а отношение, свободное от нетривиальных зависимостей соединения, — находящимся в пятой нормальной форме. Строгие определения — в теме 8; здесь важно, что ответ на вопрос «можно ли разложить» даёт не форма диаграммы, а бизнес-правило.

Полиморфные связи

В схемах, созданных объектно-реляционными преобразователями, встречается конструкция «комментарий может относиться к книге, автору или заказу»: в таблице комментариев хранятся столбцы target_type и target_id, а target_id ссылается то на одну, то на другую таблицу в зависимости от target_type. Внешний ключ на такой столбец объявить нельзя, и ссылочная целостность обеспечивается только приложением. С точки зрения ER-модели это связь сущности «Комментарий» с супертипом «Комментируемый объект», которого нет в модели. Корректные решения — ввести супертип явно (таблица на каждый класс, как для покупателей) или завести несколько необязательных внешних ключей с проверкой CHECK (num_nonnulls(book_id, author_id, order_id) = 1); оба варианта возвращают СУБД контроль над ссылками.

Лабораторная работа 7. ER-модель и её реализация

• Раздел курса: тема 7 «Концептуальное проектирование: модель „сущность — связь“»

Введение

До сих пор схема «Книжного узла» была дана готовой. В этой работе проходится путь проектировщика в обе стороны. Сначала — обратное проектирование: по системному каталогу восстанавливается ER-модель существующей базы, связи записываются с кардинальностями и обязательностью участия, а по тексту модели строится диаграмма. Затем — прямое проектирование: по словесному описанию требований к расширению магазина (программа лояльности, серии книг и подписки на них, акции, адреса доставки, возвраты) строится ER-модель, она преобразуется в DDL по правилам из теории, реализуется в отдельной схеме и проверяется: допустимые данные должны приниматься, недопустимые — отвергаться самой СУБД.

Работа опирается на тему 7 и на ограничения целостности из темы 2. Нормализация схемы — предмет темы 8, выбор типов данных — темы 9, поэтому типы столбцов выбираются простые (integer, text, date, timestamptz, boolean) и не оцениваются.

Работа состоит из основной части (части 1–5) и продвинутой (части 6–7), которую можно выполнить по желанию. Продвинутые проверки скрипта самопроверки (один основной адрес, суммарный возврат) относятся к продвинутой части.

Цель работы

Научиться строить ER-модель предметной области по требованиям и по существующей базе данных и реализовывать её реляционной схемой, в которой бизнес-правила обеспечиваются ограничениями СУБД.

После выполнения работы можно будет:

• восстановить связи, ключи и обязательность участия существующей базы запросами к information_schema и объяснить, чего каталог не сообщает;
• записать ER-модель в текстовой нотации Mermaid erDiagram и читать кардинальности в нотации «вороньи лапки» в обе стороны;
• выделить по тексту требований сущности, атрибуты, связи, слабые и ассоциативные сущности и обосновать пару (min, max) для каждой стороны каждой связи;
• преобразовать ER-модель в DDL и указать, каким ограничением обеспечено каждое бизнес-правило, а какие правила декларативно не выражаются;
• найти в чужой схеме ошибки моделирования по «грязным» данным и исправить их;
• сравнить способы отображения иерархии подтипов в таблицы запросами.

Теоретический минимум

Теория изложена в теме 7: разделы «Сущности, атрибуты и связи» (кардинальность и обязательность участия), «Слабые сущности, атрибуты связей и связи высокой степени», «Нотации ER-диаграмм», «Обобщение, специализация и наследование в таблицах» и «Преобразование ER-модели в реляционную схему». Ниже — сжатая справка.

Правила преобразования ER-модели в таблицы:

Представления информационной схемы, нужные в работе:

Таблицы учебной базы и их внешние ключи:

category (category_id PK, parent_id → category, name), UNIQUE (parent_id, name)
publisher (publisher_id PK, name UNIQUE, city)        author (author_id PK, full_name, ...)
book (book_id PK, isbn UNIQUE, title, publisher_id → publisher NULL,
      category_id → category, pub_year, pages, price, attrs, description)
book_author (book_id → book, author_id → author, author_order), PK (book_id, author_id)
warehouse (warehouse_id PK, city UNIQUE)
stock (warehouse_id → warehouse, book_id → book, quantity), PK (warehouse_id, book_id)
customer (customer_id PK, email UNIQUE, full_name, city, registered_at)
orders (order_id PK, customer_id → customer, created_at, status, warehouse_id → warehouse NULL)
order_item (order_id → orders, book_id → book, quantity, price), PK (order_id, book_id)
payment (payment_id PK, order_id → orders, paid_at, amount, method)
review (review_id PK, book_id → book, customer_id → customer, rating, body, created_at),
       UNIQUE (book_id, customer_id)

Синтаксис Mermaid erDiagram в объёме работы:

erDiagram
    customer ||--o{ orders : "оформляет"
    orders {
        integer order_id PK
        integer customer_id FK
        text status "new, paid, shipped, delivered, cancelled"
    }

Строка связи имеет вид сущность1 левый_конец--правый_конец сущность2 : "подпись". Конец связи описывает, сколько экземпляров этой сущности приходится на один экземпляр сущности на другом конце. Как и на рисунке «вороньих лапок», символ, ближайший к имени сущности, задаёт максимум, дальний — минимум; поэтому один и тот же конец записывается слева и справа зеркально:

• || — ровно один;
• |o слева или o| справа — ноль или один;
• }| слева или |{ справа — один или много;
• }o слева или o{ справа — ноль или много.

Два дефиса -- между концами обозначают идентифицирующую связь (ключ родителя входит в первичный ключ потомка, сплошная линия), две точки .. — неидентифицирующую (пунктир). Атрибут записывается как «тип имя», после имени через запятую — PK, FK, UK, в конце — необязательный комментарий в кавычках. Имя типа не должно содержать пробелов (timestamp_with_time_zone, а не timestamp with time zone). Чтобы увидеть рисунок, вставьте текст диаграммы в редактор mermaid.live или в Markdown-файл на GitHub или GitLab (блок с языком mermaid); синтаксис — в документации Mermaid.

Полезные метакоманды psql: \dn (схемы), \dt lab07.* (таблицы схемы), \d lab07.return_item (столбцы, ключи, ограничения и ссылки на таблицу), \pset format unaligned, \pset tuples_only on и \o файл (вывод «чистого» текста в файл).

Перечень оснащения

• ОС Ubuntu 24.04 (физическая машина, виртуальная машина или WSL2).
• PostgreSQL 16 (sudo apt install postgresql postgresql-contrib), ваша роль и база bookstore из лабораторной работы 1.
• Python 3 с пакетом psycopg 3 для самопроверки: sudo apt install python3-psycopg или pip install "psycopg[binary]" в виртуальном окружении.
• Архив окружения lr-env: fk_query.sql (исходный запрос к каталогу), broken_design.sql (схема с ошибками для части 5), check.py (самопроверка части 4), README.md.
• Браузер с доступом к mermaid.live или редактор с поддержкой Mermaid; для части 3 допустимо рисовать диаграмму от руки или в draw.io, но текст Mermaid стоит сохранить в журнале работы.
• Учебная база bookstore с scale=1.

Порядок выполнения работы

Распакуйте архив и убедитесь, что учебная база на месте:

unzip lr-env.zip -d ~/lab07 && cd ~/lab07/lr-env
psql -d bookstore -c 'SELECT count(*) FROM bookstore.book'

Должно получиться 2010 книг. Если база изменялась в предыдущих работах так, что результаты расходятся с ожидаемыми, пересоздайте её по инструкции из README.md (файлы schema.sql и data.sql — из окружения лабораторной работы 1).

Схема bookstore не изменяется. Расширение создаётся в схеме lab07 скриптом extension.sql, который вы пишете сами; он начинается с DROP SCHEMA IF EXISTS lab07 CASCADE; CREATE SCHEMA lab07; и может запускаться повторно.

Задания, помеченные словом «Предсказание», выполняются в два шага: сначала письменный ответ, затем проверка.

Часть 1. Связи учебной базы по системному каталогу

Задание

1. Предсказание. Не обращаясь к базе, по описанию схемы из теоретического минимума ответьте: сколько внешних ключей в схеме bookstore? Какие из них допускают NULL? Есть ли в схеме связи 1:1?

2. Выполните исходный запрос из окружения: psql -d bookstore -f fk_query.sql. Сравните результат с предсказанием. Для каждой строки результата запишите связь в виде «родитель (min, max) — потомок (min, max)» по Элмасри и Навате и в виде строки Mermaid. Минимум на стороне родителя следует из столбца nullable; максимум на стороне потомка для внешнего ключа без UNIQUE — «много».

3. Напишите запрос, выводящий для каждой таблицы схемы bookstore столбцы её первичного ключа одной строкой через запятую в порядке их следования в ключе (string_agg(... ORDER BY ordinal_position) по key_column_usage для ограничений PRIMARY KEY). Какие таблицы являются ассоциативными сущностями? Какие связи M:N предметной области они реализуют и какие у этих связей атрибуты?

4. Дополните запрос из fk_query.sql, не меняя исходных четырёх столбцов и числа строк, тремя столбцами:

   • in_pk — входит ли столбец внешнего ключа в первичный ключ потомка (признак идентифицирующей связи);
   • fk_unique — объявлен ли на этом столбце отдельный UNIQUE или первичный ключ из одного этого столбца (признак связи 1:1);
   • delete_rule — действие при удалении родителя из referential_constraints.

   Сохраните запрос в файл fk_query_ext.sql. Какие связи идентифицирующие? У каких из них ON DELETE CASCADE и почему это согласуется с понятиями слабой и ассоциативной сущности? Почему у stock и order_item.book_id каскада нет, хотя связи тоже идентифицирующие, — что произошло бы с историей заказов при удалении книги?

5. Каталог сообщает минимум только на стороне родителя. Минимум на стороне потомка (есть ли у каждой книги хотя бы один автор, у каждого заказа — хотя бы одна позиция и хотя бы один платёж, у каждой рубрики — хотя бы одна книга) приходится устанавливать по требованиям, а данные лишь проверяют гипотезу. Напишите запросы, подсчитывающие книги без авторов, заказы без позиций, заказы без платежей (с разбивкой по статусу) и рубрики без книг. Для каждой связи решите, какой минимум должен стоять на диаграмме по смыслу и какой допускает схема.

Что должно получиться

Предсказания пункта 1 и их сравнение с фактом; таблица из 14 связей с парами (min, max) и строками Mermaid; запрос первичных ключей и его результат; текст fk_query_ext.sql и его результат с ответами на вопросы пункта 4; запросы и результаты пункта 5 с выводом о минимумах на стороне «многих».

Часть 2. Диаграмма учебной базы в Mermaid

Задание

1. По результатам части 1 и описаниям таблиц (\d bookstore.book и т. д.) напишите текст диаграммы Mermaid erDiagram для всех двенадцати таблиц учебной базы:
   • все столбцы с типами, пометками PK, FK, UK (найдите все ограничения UNIQUE запросом к table_constraints; составные ограничения, например UNIQUE (book_id, customer_id) таблицы review, отметьте комментарием, так как UK у отдельного столбца означал бы уникальность самого столбца);
   • все 14 связей с концами, отражающими обязательность участия с обеих сторон по решениям пункта 5 части 1;
   • идентифицирующие связи — двумя дефисами, неидентифицирующие — двумя точками;
   • подписи связей — глаголами с точки зрения родителя («выпускает», «содержит», «оформляет»).
2. Отобразите диаграмму в mermaid.live (или другом инструменте) и сравните её с рисунком «ER-диаграмма учебной базы „Книжный узел“» из теории. Где символы на концах связей расходятся, объясните причину: рисунок отражает схему (что СУБД позволяет), а ваша диаграмма может отражать требования (что должно быть).
3. Прочитайте вслух (письменно) в обе стороны три связи: «издательство — книга», «заказ — платёж», «рубрика — рубрика». Для рекурсивной связи укажите роли.
4. Предсказание. Сколько строк вернёт соединение book JOIN book_author JOIN author, если на диаграмме у book_author со стороны книги стоит «ноль или много»? Что изменится при LEFT JOIN? Проверьте и свяжите разницу с минимумом на стороне потомка из части 1.

Что должно получиться

Полный текст диаграммы (текстом, а также снимок отрисованной диаграммы); перечень расхождений с рисунком из теории с объяснением; три связи, прочитанные в обе стороны; предсказание и результат пункта 4.

Часть 3. ER-модель расширения магазина

Задание

Магазин расширяет функциональность. Ниже приведены требования в том виде, в каком их сформулировал заказчик. Каждое пронумерованное правило должно найти отражение в модели — структурой, кардинальностью или ограничением, — либо в отчёте должно быть объяснено, почему оно декларативно не выражается.

Программа лояльности.

• П1. Покупатель может вступить в программу лояльности и получить карту. Карта выдаётся конкретному покупателю; у покупателя не бывает двух карт, но многие покупатели в программе не участвуют.
• П2. У карты есть номер (например, LC-0001), уровень и дата выдачи. Уровней три: basic, silver, gold; новая карта получает уровень basic.

Серии и подписки.

• П3. Книги объединяются в серии («Миры Стругацких», «Библиотека программиста»). У серии есть название, и двух серий с одинаковым названием не бывает.
• П4. Книга может не входить ни в одну серию, но не может входить в несколько. Внутри серии у книги есть номер тома — целое положительное число; два тома одной серии не могут иметь одинаковый номер.
• П5. Покупатель может подписаться на серию, чтобы получать уведомления о новых томах, и на одну и ту же серию подписывается не более одного раза. Для подписки хранится момент её оформления. Покупатель может быть подписан на много серий.

Акции.

• П6. Акция — это скидка в процентах на набор книг, действующая в период с даты начала по дату окончания включительно; однодневная акция допустима. У акции есть название.
• П7. Скидка — целое число процентов от 1 до 90.
• П8. В акцию входит хотя бы одна книга; одна книга может участвовать во многих акциях, но в одну акцию дважды не включается.

Адреса доставки.

• П9. У покупателя может быть несколько адресов доставки. Адрес состоит из города, улицы, дома и необязательного номера квартиры; по городу нужно строить отчёты.
• П10. Один из адресов покупателя может быть помечен как основной; основных адресов у покупателя не больше одного.

Возвраты.

• П11. Покупатель может вернуть книги из полученного заказа. Возврат относится к конкретной позиции заказа — к этой книге в этом заказе. По одной позиции возможно несколько возвратов (сначала один экземпляр, через неделю — ещё один).
• П12. Для возврата хранятся число возвращённых экземпляров (положительное), момент возврата и причина (необязательно).
• П13. Суммарно по позиции нельзя вернуть больше экземпляров, чем было куплено.

Выполните задание.

1. Составьте словарь: выпишите из требований существительные и глаголы — кандидатов в сущности, атрибуты и связи (грамматическая эвристика из темы 7) — и для каждого укажите решение: сущность, атрибут (чей и какого вида), связь или ничего.
2. Для каждой связи заполните таблицу: участники, имя связи, пара (min, max) каждой стороны, номер правила-обоснования, атрибуты связи. Отдельно ответьте:
   • почему «карта лояльности» — отдельная сущность, а не набор необязательных столбцов в customer, и на какой стороне связи 1:1 должен стоять внешний ключ;
   • почему «номер тома» — атрибут связи «книга — серия», а не атрибут книги или серии, и какова кардинальность этой связи;
   • почему адрес доставки нельзя хранить ни столбцом customer.address, ни одной строкой со списком адресов (какой это вид атрибута по классификации Чена);
   • с какой сущностью связан возврат и почему его нельзя связать отдельными связями с заказом и с книгой (раздел «Атрибуты связей и ассоциативные сущности» и ошибка «связь вместо сущности»).
3. Отметьте слабые и ассоциативные сущности и идентифицирующие связи.
4. Запишите модель текстом Mermaid erDiagram (с атрибутами и пометками ключей) и отобразите её. Из учебной базы включите в диаграмму только сущности, с которыми связано расширение: customer, book, orders, order_item — с одними ключевыми атрибутами.
5. Перечислите правила, которые не выражаются ни структурой, ни кардинальностью, и для каждого предложите механизм: CHECK, частичный уникальный индекс, триггер (подробно — в теме 14), проверка в приложении. Какое из правил П1–П13 требует просмотра нескольких строк таблицы?
6. Может ли схема гарантировать минимум 1 из правила П8 (у акции есть хотя бы одна книга)? Сравните с правилом «в заказе есть хотя бы одна позиция» из теории.

Что должно получиться

Словарь с решениями; таблица связей с парами (min, max) и ссылками на правила; ответы на вопросы пунктов 2, 5 и 6; текст диаграммы Mermaid и её изображение.

Часть 4. Реализация в DDL и самопроверка

Задание

1. Преобразуйте модель части 3 в DDL по правилам из раздела «Преобразование ER-модели в реляционную схему» и запишите в скрипт extension.sql, создающий таблицы в схеме lab07 и ссылающийся на таблицы bookstore.customer, bookstore.book и bookstore.order_item. Скрипт самопроверки знает о вашей схеме только «контракт» — имена таблиц и столбцов, приведённые ниже; остальные решения (типы, дополнительные столбцы и таблицы, имена ограничений) свободны.

   Таблица	Обязательные столбцы	Смысл
   loyalty_card	card_no, customer_id, level, issued_at	карта лояльности (П1, П2)
   series	series_id, name	серия книг (П3)
   book_series	book_id, series_id, volume_no	вхождение книги в серию (П4)
   subscription	customer_id, series_id, subscribed_at	подписка (П5)
   promotion	promo_id, name, discount_pct, starts_on, ends_on	акция (П6, П7)
   promotion_book	promo_id, book_id	книги акции (П8)
   delivery_address	address_id, customer_id, city, street, house, flat, is_default	адрес доставки (П9, П10)
   return_item	return_id, order_id, book_id, quantity, returned_at, reason	возврат (П11–П13)

   Дополнительные требования контракта: суррогатные ключи series_id, promo_id, address_id и return_id генерируются СУБД (GENERATED ALWAYS AS IDENTITY), скрипт их не передаёт; issued_at, subscribed_at и returned_at — моменты времени или даты, которые скрипт передаёт явно; is_default — логическое значение.

2. Предсказание. Для каждого правила П1–П13 запишите, каким ограничением вашего DDL оно обеспечено (имя столбца и вид ограничения) или почему не обеспечено. Отдельно отметьте правила, для которых понадобился составной внешний ключ, частичный уникальный индекс или триггер.

3. Добавьте в конец extension.sql тестовые данные: 2–3 карты, серию «Миры Стругацких» с книгами 4 и 5 («Пикник на обочине» и «Трудно быть богом») в качестве томов 1 и 2, две подписки, акцию с двумя книгами, два адреса одного покупателя, один возврат по существующей позиции заказа (найдите её запросом к bookstore.order_item). Выполните скрипт: psql -d bookstore -v ON_ERROR_STOP=1 -f extension.sql.

4. Запустите самопроверку: python3 check.py. Скрипт выполняет допустимые и недопустимые вставки в одной транзакции, проверяет, что СУБД принимает первые и отвергает вторые, и откатывает все изменения. При полностью корректной реализации последние строки вывода таковы:

   Базовые проверки: 26 из 26; продвинутые: 2 из 2.
   База данных не изменена (все операции откачены).
   

   Таблицы без единого ограничения проходят только 11 базовых проверок из 26 — те, где вставка должна быть принята. Для каждой строки «НЕТ» найдите проверяемое правило, исправьте DDL и повторите запуск.

5. Найти ошибку в инструменте. Выполните запрос из fk_query.sql, заменив в условии схему bookstore на lab07. Сколько строк приходится на таблицу return_item и сколько у неё на самом деле внешних ключей и столбцов в них? Объясните результат, используя замечание о составных внешних ключах из раздела «Восстановление модели по системному каталогу». Напишите исправленный запрос к pg_constraint, выводящий для каждого внешнего ключа схемы lab07 пары «столбец потомка — столбец родителя» в правильном соответствии (используйте unnest(conkey, confkey) WITH ORDINALITY и pg_attribute).

6. Напишите два запроса к новой схеме, проверяющие, что модель отвечает на вопросы заказчика:

   • какие акции действуют 3 июня 2024 года и сколько книг в каждой;
   • какие подписчики серии «Миры Стругацких» уже купили хотя бы один её том (используйте bookstore.orders и bookstore.order_item).

Что должно получиться

Текст extension.sql; таблица «правило — ограничение» из пункта 2; первый и последний выводы check.py; результат запроса пункта 5 с объяснением и исправленный запрос; запросы пункта 6 и их результаты на тестовых данных.

Часть 5. Найти ошибку: схема без ER-модели

Задание

Первую версию расширения разработчик сделал за один день, не строя ER-модели. Через неделю эксплуатации в данных обнаружились странности. Схема и накопившиеся данные создаются скриптом из окружения:

psql -d bookstore -f broken_design.sql

1. Изучите схему lab07_broken (\dt lab07_broken.*, \d lab07_broken.promotion и т. д.) и сравните её с вашей моделью из части 3. До выполнения запросов к данным выпишите, какие нарушения требований П1–П13 эта схема допускает.

2. Для каждой из пяти таблиц напишите запросы, находящие в данных нарушения требований. Всего в данных не менее десяти нарушений разного рода. Для разбора списков, хранящихся в одной строке, пригодятся функции string_to_array, unnest, trim; для сравнения написаний — lower и regexp_replace(s, '\s+', ' ', 'g').

3. Для каждого найденного нарушения назовите ошибку моделирования по терминологии темы 7 (атрибут вместо сущности, многозначный атрибут в одном поле, пропущенное ограничение кардинальности или обязательности, связь вместо сущности) и укажите, какой элемент вашей схемы из части 4 делает это нарушение невозможным.

4. Найти ошибку в запросе. Коллега проверял, все ли ISBN из акций существуют в каталоге, и получил пустой результат:

   SET search_path = lab07_broken;
   SELECT p.promo_id, trim(isbn) AS missing
   FROM promotion p, unnest(string_to_array(p.books, ',')) AS isbn
   WHERE NOT EXISTS (SELECT 1 FROM bookstore.book b WHERE b.isbn = trim(isbn));
   

    promo_id | missing
   ----------+---------
   (0 rows)
   

   Почему запрос ничего не нашёл, хотя несуществующий ISBN в данных есть? Подсказка: к какому столбцу относится имя isbn внутри подзапроса? Исправьте запрос и объясните, почему эта ошибка вообще стала возможной (какое решение моделирования вынудило писать такой запрос).

5. Какие строки lab07_broken нельзя перенести в схему lab07 без решения заказчика (например, какая из двух карт покупателя 1 настоящая)?

Что должно получиться

Список допускаемых нарушений (до запросов); запросы и их результаты по каждой таблице; таблица «нарушение — ошибка моделирования — исправление»; объяснение и исправление запроса из пункта 4; решения по переносу данных.

Часть 6. Иерархия подтипов (продвинутая)

Задание

Магазин начинает продавать книги организациям (школам, библиотекам). Покупатель становится либо физическим лицом (ФИО), либо организацией (наименование, ИНН из 10 цифр, уникальный). Специализация непересекающаяся и полная. Для эксперимента условно считайте организацией каждого десятого покупателя учебной базы (customer_id % 10 = 0); наименование организации — 'ООО «' || full_name || '»', ИНН — lpad((7700000000 + customer_id)::text, 10, '0').

1. Реализуйте в схеме lab07 два способа отображения из раздела «Три способа отображения иерархии в таблицы»: одну таблицу на иерархию (client_single) и таблицу на каждый класс (client, client_person, client_org). Правила «у физического лица есть ФИО и нет ИНН», «у организации есть наименование и ИНН» и непересечение подтипов должны обеспечиваться ограничениями СУБД. Заполните обе реализации из bookstore.customer запросами INSERT ... SELECT.
2. Предсказание. Сколько значений NULL окажется в столбцах full_name, org_name, inn таблицы client_single? Проверьте одним запросом.
3. Напишите в обеих реализациях запросы: «отображаемое имя» покупателей 9 и 10 (ФИО или наименование); число организаций и суммарное число их заказов. Результаты двух реализаций должны совпасть (организаций — 500). Сравните тексты запросов: где понадобились соединения и какие?
4. Попробуйте вставить в каждую реализацию недопустимые строки: физическое лицо с ИНН; организацию без ИНН; «организационное» продолжение для покупателя, уже являющегося физическим лицом. Какие из нарушений отвергаются и каким ограничением?
5. Ответьте письменно: почему третий способ (таблица на конкретный класс) не позволил бы оставить внешний ключ orders.customer_id, и какое из правил полноты специализации не удаётся обеспечить декларативно во втором способе.

Что должно получиться

DDL двух реализаций; предсказание и результат пункта 2; запросы и результаты пункта 3; таблица «попытка нарушения — способ 1 — способ 2»; ответы на вопросы пункта 5.

Часть 7. Генерация диаграммы по каталогу (продвинутая)

Задание

1. Напишите один запрос к information_schema, результат которого — готовый текст диаграммы Mermaid учебной базы: первая строка erDiagram, затем строки связей (идентифицирующие — с --, остальные — с ..; конец у родителя || или |o в зависимости от nullable; подпись — имя столбца внешнего ключа), затем блоки атрибутов каждой таблицы с пометками PK, FK, PK, FK. Пробелы в именах типов замените подчёркиваниями. Удобная заготовка — UNION ALL трёх подзапросов, каждый в скобках со своим ORDER BY, и функция format.

2. Получите «чистый» текст без заголовков и рамок и сохраните его в файл:

   \pset format unaligned
   \pset tuples_only on
   \o bookstore_generated.mmd
   -- ваш запрос
   \o
   

   Отобразите файл в mermaid.live. Проверьте, что в нём 14 связей и 12 таблиц.

3. Какую информацию добавила диаграмма, написанная вручную в части 2, по сравнению со сгенерированной, и почему её нельзя получить из каталога?

4. Выполните запрос для схемы lab07. Что произойдёт со связью return_item → order_item, если запрос построен на constraint_column_usage? Доработайте запрос так, чтобы составной внешний ключ давал одну связь, и проверьте на lab07.

Что должно получиться

Текст запроса; файл bookstore_generated.mmd и снимок диаграммы; ответ на вопрос пункта 3; результат для lab07 до и после доработки.

Журнал работы

Журнал работы — записи для себя в формате «предсказание — команда или запрос — фактический результат — объяснение». Для каждой части полезно записать предсказание до выполнения, текст запроса или команды, существенный фрагмент результата и объяснение расхождений. Тексты диаграмм Mermaid, таблицы связей и обоснования кардинальностей, файлы fk_query_ext.sql и extension.sql, первый и последний выводы check.py удобно хранить рядом; в выводах отметьте, какие решения модели оказались спорными и какие правила потребовали средств сверх структуры таблиц. В конце стоит сформулировать несколько выводов: какие предсказания не оправдались и почему.

Контрольные вопросы

1. Почему каталог базы данных позволяет определить минимум на стороне родителя связи 1:N, но не позволяет определить минимум на стороне потомка? Как обеспечить правило «у каждой книги хотя бы один автор»?
2. Чем на уровне DDL связь 1:1 отличается от связи 1:N? На какой стороне связи «покупатель — карта лояльности» должен стоять внешний ключ и почему?
3. Почему номер тома — атрибут связи «книга — серия»? Как изменилась бы реализация, если бы книга могла входить в несколько серий?
4. Почему внешний ключ возврата должен ссылаться на позицию заказа составным ключом, а не на заказ и книгу двумя отдельными внешними ключами? Какую ошибку моделирования иллюстрирует второй вариант?
5. Какие правила расширения не выражаются ни структурой, ни ограничениями NOT NULL, UNIQUE, CHECK и внешними ключами? Чем правило П10 отличается от правила П13 с точки зрения средств его обеспечения?
6. Почему хранение ISBN книг акции списком через запятую лишает СУБД возможности обеспечить ссылочную целостность? Какие ещё запросы становятся сложными и ненадёжными?
7. Как в нотации «вороньи лапки» прочитать связь «заказ — платёж» в обе стороны? На каком конце линии смотреть символ в каждом случае?
8. Почему запрос к information_schema.constraint_column_usage дублирует строки для составного внешнего ключа? Какая информация для правильного сопоставления столбцов есть в pg_constraint?
9. Чем идентифицирующая связь отличается от неидентифицирующей и как это видно по каталогу? Почему для связи order_item → book идентифицирующая связь не сопровождается ON DELETE CASCADE?
10. В каком способе отображения иерархии подтипов больше всего значений NULL и в каком — больше всего соединений в запросах? Какой способ вы выбрали бы для «Книжного узла», если на покупателя ссылаются заказы, отзывы, карты и адреса?