# Занятие 07: Учебное приложение / Книжный магазин > Исходная страница в нативном Markdown: Учебное приложение / Книжный магазин --- Индекс LLMS: [llms.txt](/ru/llms.txt) --- > [!NOTE] > Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. > **HTML source:** [dev1_07_bstore_schema.html](https://edu.postgrespro.ru/16/dev1-16/dev1_07_bstore_schema.html) > **Archive:** [DEV1-handouts-16.zip](https://edu.postgrespro.ru/16/DEV1-handouts-16.zip) > **Страниц PDF:** 28 ## Page 1: Учебное приложение / Книжный магазин {#source-page-001} ### Авторские права © Postgres Professional, 2017–2024 Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал) ### Использование материалов курса Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса. ### Обратная связь Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru ### Отказ от ответственности Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения. ## Page 2: Темы {#source-page-002} - Обзор приложения «Книжный магазин» - Проектирование схемы данных, нормализация - Итоговая схема данных приложения - Организация интерфейса между клиентом и сервером ## Page 3: Приложение {#source-page-003} ![Source slide page 3: Приложение](images/page-003.png){#source-figure-003 caption="Source slide page 3: Приложение"} ### Комментарии преподавателя Приложение состоит из нескольких частей, представленных вкладками. «Магазин» — это интерфейс веб-пользователя, в котором он может покупать книги. Остальные вкладки соответствуют внутреннему интерфейсу, доступному только сотрудникам («админка» сайта). «Каталог» — интерфейс кладовщика, в котором он может заказывать закупку книг на склад магазина и просматривать операции поступлений и покупок. «Книги» и «Авторы» — интерфейс библиотекаря, в котором он может регистрировать авторов и их книги. В учебных целях вся функциональность представлена на одной общей веб-странице. Если какая-то часть функциональности недоступна из-за того, что на сервере нет подходящего объекта (таблицы, функции и т. п.), приложение сообщит об этом. Также приложение выводит текст запросов, которые оно посылает на сервер. В ходе курса мы начнем с пустой базы данных и постепенно реализуем в ней все необходимые компоненты. P. S. Исходный код приложения не является темой курса, но может быть получен в репозитории ## Page 4: Демонстрация приложения {#source-page-004} В этой демонстрации мы показываем приложение «Книжный магазин» в том виде, в котором оно будет после завершения всех практических заданий. Приложение откроется в отдельной вкладке браузера виртуальной машины курса: Открываем ... ```sh student$ xdg-open http://localhost ``` ## Page 5: Книги {#source-page-005} **ER-модель для высокоуровневого проектирования** - сущности — понятия предметной области - связи — отношения между сущностями - атрибуты — свойства сущностей и связей - сущность ### Книга - атрибуты - название - авторы - количество - операции ### Комментарии преподавателя После того, как мы посмотрели на внешний вид приложения и поняли его функциональность, нам нужно разобраться со схемой данных. Мы не будем хоть сколько-нибудь глубоко вникать в вопросы проектирования баз данных — это отдельная дисциплина, не входящая в данный курс. Но совсем обойти вниманием эту тему тоже нельзя. Часто для высокоуровневого проектирования баз данных используется модель «сущность — связи», или ER-модель (Entity — Relationship). Она оперирует сущностями (понятиями предметной области), связями между ними и атрибутами (свойствами сущностей и связей). Модель позволяет рассуждать на логическом уровне, не опускаясь до деталей представления данных на физическом (в виде таблиц). Первым подходом к проектированию может служить диаграмма, представленная на слайде: можно представить одну большую сущность «Книга», а все остальное сделать ее атрибутами. ## Page 6: Схема данных {#source-page-006} ![Source slide page 6: Схема данных](images/page-006.png){#source-figure-006 caption="Source slide page 6: Схема данных"} ```text id | title | author | qty | operation −−−−+−−−−−−−−−−−−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+−−−−−+−−−−−−−−−−− 1 | Муму | Тургенев Иван Сергеевич | 10 | +11 1 | МУМУ | Тургенев Иван Сергеевич | 10 | -1 2 | Отцы и дети | Тургенев Иван Сергеевич | 4 | +4 3 | Трудно быть богом | Стругацкий Аркадий Натанович | 7 | +7 3 | Трудно быть богом | Стругацкий Борис Натанович | 7 | 0 ``` - Данные дублируются - сложно поддерживать согласованность - сложно обновлять - сложно писать запросы ### Комментарии преподавателя Очевидно, что это неправильный подход. На диаграмме это может быть не так заметно, но давайте попробуем отобразить диаграмму на таблицы БД. Это можно сделать разными способами, например так, как показано на слайде: сущность становится таблицей, ее атрибуты — столбцами этой таблицы. Здесь хорошо видно, что часть данных дублируется (эти фрагменты выделены на рисунке). Дублирование приводит к сложностям с обеспечением согласованности — а это едва ли не главная задача СУБД. Например, каждая из двух строк, относящихся к книге 3, должна содержать общее количество (7 штук). Что нужно сделать, чтобы отразить покупку книги? С одной стороны, надо добавить строки для отражения операции покупки (а сколько строк добавлять, еще две?). С другой, во всех строках надо уменьшить количество с 7 до 6. А что делать, если в результате какой-нибудь ошибки значение количества в одной из этих строк будет отличаться от значения в другой строке? Как сформулировать ограничение целостности, которое запрещает такую ситуацию? Также усложняются и многие запросы. Как найти общее число книг? Как найти всех уникальных авторов? Итак, такая схема плохо работает для реляционных баз данных. ## Page 7: Схема данных (вариант) {#source-page-007} ```text entity | attribute | value −−−−−−−−+−−−−−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−−−− 1 | title | Муму 1 | author | Тургенев Иван Сергеевич 1 | qty | 10 1 | operation | +11 1 | operation | -1 2 | title | Отцы и дети 2 | author | Тургенев Иван Сергеевич 2 | qty | 4 2 | operation | +4 ... | ... | ... ``` - Данные без схемы - поддержка согласованности на стороне приложения - сложно писать запросы - низкая производительность (множественные соединения) ### Комментарии преподавателя Другой вариант отображения сущности в таблицу — так называемая схема EAV: сущность — атрибут — значение. Она позволяет уложить вообще все, что угодно, в одну-единственную таблицу. Формально мы получаем реляционную базу данных, а фактически здесь отсутствует схема и СУБД не может гарантировать согласованность данных. Поддержка согласованности данных целиком ложится на приложение, что, безусловно, рано или поздно приведет к ее нарушению. В такой схеме сложно писать запросы (хотя и довольно просто их генерировать), и в результате работа со сколько-нибудь большим объемом данных становится проблемой из-за постоянных многократных соединений таблицы самой с собой. Однако следует отметить, что такой подход имеет и плюсы. Например, очевидно, что такую «схему» данных легко наращивать: при добавлении новой сущности или атрибута мы просто добавляем в нашу таблицу новые строки. И все-таки так делать не стоит. ## Page 8: Схема данных (вариант) {#source-page-008} ```text book_id | description −−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− 1 | {"title": "Муму", | "authors": [ "Тургенев Иван Сергеевич" ], | "qty": 10, | "operations": [ +11, -1 ]} 3 | {"title": "Трудно быть богом", | "authors": [ "Стругацкий Аркадий Натанович", | "Стругацкий Борис Натанович" ], | "qty": 7, | "operations": [ +7 ]} ... | ... ``` - Данные без схемы - поддержка согласованности на стороне приложения - сложно писать запросы (специальный язык) - индексная поддержка есть ### Комментарии преподавателя Еще одна вариация на ту же тему — представление данных в виде JSON, в духе NoSQL. По сути, тут применимы все замечания, сделанные ранее. Кроме того, запросы к такой структуре придется писать не на SQL, а используя какой-то специализированный язык (раньше скорее всего выбор пал бы на jsQuery, а в PostgreSQL 16 удобно пользоваться возможностями SQL/JSON Path Language, определенными в стандарте SQL:2016). SON-PATH Хотя у PostgreSQL есть индексная поддержка JSON, производительность все равно под вопросом. Такую схему удобно применять, когда от базы данных требуется только получение JSON по идентификатору, но не требуется серьезная работа с данными внутри JSON. Это не наш случай. (Разумеется, это не категоричное утверждение. См. последнее задание в практике.) ## Page 9: Книги и операции {#source-page-009} ![Source slide page 9: Книги и операции](images/page-009.png){#source-figure-009 caption="Source slide page 9: Книги и операции"} **Нормализация — уменьшение избыточности данных** - разбиение крупных сущностей на более мелкие ### Книга ### Операция ### Комментарии преподавателя Итак, нам требуется устранить избыточность данных, чтобы привести их в вид, удобный для обработки в реляционной СУБД. Этот процесс называется нормализацией. Возможно, вы знакомы с понятиями нормальных форм (первая, вторая, третья, Бойса-Кодда и т. д.). Мы не будем говорить о них; на неформальном уровне достаточно понимать, что весь этот математический аппарат преследует одну-единственную цель: устранение избыточности. Средство для уменьшения избыточности — разбиение большой сущности на несколько меньших. Как именно это сделать, подскажет здравый смысл (который все равно нельзя заменить одним только знанием нормальных форм). В нашем случае все достаточно просто. Давайте начнем с отделения книг от операций. Эти две сущности связаны отношением «один ко многим»: каждая книга может иметь несколько операций, но каждая операция относится только к одной книге. ## Page 10: Схема данных {#source-page-010} ```text books ``` ```text book_id | title | author −−−−−−−−−+−−−−−−−−−−−−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− 1 | Муму | Тургенев Иван Сергеевич 2 | Отцы и дети | Тургенев Иван Сергеевич 3 | Трудно быть богом | Стругацкий Аркадий Натанович 3 | Трудно быть богом | Стругацкий Борис Натанович ``` ```text operations ``` ```text operation_id | book_id | qty_change | date_created −−−−−−−−−−−−−−+−−−−−−−−−+−−−−−−−−−−−−+−−−−−−−−−−−−−− 1 | 1 | +10 | 2020-07-13 2 | 1 | -1 | 2020-08-25 3 | 3 | +7 | 2020-07-13 4 | 2 | +4 | 2020-07-13 ``` ### Комментарии преподавателя На уровне структуры базы данных это может быть представлено двумя таблицами: книги (books) и операции (operations). Операция состоит в изменении количества книг (положительном при заказе; отрицательном при покупке). Заметьте, что теперь у книги нет атрибута «количество». Вместо этого достаточно просуммировать изменения количества по всем операциям, относящимся к данной книге. Дополнительный атрибут «количество» у книги снова привел бы к избыточности данных. Не исключено, что такое решение вас насторожило. Удобно ли вместо простого обращения к полю подсчитывать сумму? Но мы можем создать представление, которое для каждой книги будет показывать количество. Это не приведет к появлению избыточности, поскольку представление — это всего лишь запрос. Второй момент — производительность. Если подсчет суммы приведет к ухудшению производительности, мы можем выполнить обратный процесс — денормализацию: добавить в таблицу книг поле «количество» и обеспечить его согласованность с таблицей операций. Надо ли этим заниматься — вопрос, ответ на который выходит за рамки этого курса (он рассматривается в курсе QPT «Оптимизация запросов»). Но из общих соображений понятно, что для нашего «игрушечного» магазина это не требуется. Однако мы еще вернемся к вопросу денормализации в теме «Триггеры». Итак, как видно на слайде, выделение операций в отдельную сущность решило часть проблем дублирования, но не все. ## Page 11: Книги, авторы и операции {#source-page-011} ![Source slide page 11: Книги, авторы и операции](images/page-011.png){#source-figure-011 caption="Source slide page 11: Книги, авторы и операции"} ### Книга ### Операция ### Комментарии преподавателя Поэтому надо сделать еще один шаг: отделить авторов от книг и связать их соотношением «многие ко многим»: и каждая книга может быть написана несколькими авторами, и каждый автор может написать несколько книг. На уровне таблиц такая связь реализуется с помощью дополнительной промежуточной таблицы. Атрибутами автора можно сделать фамилию, имя и отчество. Это имеет смысл, поскольку нам может потребоваться работать отдельно с каждым из этих атрибутов, например, выводить фамилию и инициалы. ## Page 12: Схема данных приложения {#source-page-012} ```sh student$ psql bookstore ``` Итак, схема данных приложения состоит из четырех таблиц: ```sql => \dt ``` ```text List of relations Schema | Name | Type | Owner -----------+------------+-------+--------- bookstore | authors | table | student bookstore | authorship | table | student bookstore | books | table | student bookstore | operations | table | student (4 rows) ``` ### Книги ```sql => \d books ``` ```text Table "bookstore.books" Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+------------------------------ book_id | integer | | not null | generated always as identity title | text | | not null | Indexes: "books_pkey" PRIMARY KEY, btree (book_id) Referenced by: TABLE "authorship" CONSTRAINT "authorship_book_id_fkey" FOREIGN KEY (book_id) REFERENCES books(book_id) TABLE "operations" CONSTRAINT "operations_book_id_fkey" FOREIGN KEY (book_id) REFERENCES books(book_id) ``` Здесь мы используем типы данных: integer — целое число; text — символьный, текстовая строка произвольной длины. А также используем ограничение целостности: PRIMARY KEY — первичный ключ. Конструкция GENERATED AS IDENTITY служит для автоматической генерации уникальных значений (в версиях до 10 для этого использовался псевдотип serial). - Строки столбцов, объявленных как GENERATED AS IDENTITY, получают значения из специальных объектов базы данных — последовательностей. Имя использованной последовательности можно узнать так: ```sql => SELECT pg_get_serial_sequence('books','book_id'); ``` ```text pg_get_serial_sequence ----------------------------- bookstore.books_book_id_seq (1 row) ``` Последовательности можно при необходимости создавать вручную, а также обращаться к ним непосредственно: ```sql => SELECT nextval('books_book_id_seq'); ``` ```text nextval --------- 7 (1 row) ``` Последовательность — самый эффективный способ генерации уникальных номеров. Но следует иметь в виду, что последовательность не гарантирует: отсутствия пропусков в нумерации (так как изменение происходит не транзакционно); монотонного возрастания номеров (если используется кеширование значений в сеансах). ## Page 13: Page 13 {#source-page-013} Вот какие данные находятся в таблице книг: ```sql => SELECT * FROM books \gx ``` ```text -[ RECORD 1 ]----------------------------------------------------------------------------------------- ------------------------------------------------------ book_id | 1 title | Сказка о царе Салтане -[ RECORD 2 ]----------------------------------------------------------------------------------------- ------------------------------------------------------ book_id | 2 title | Муму -[ RECORD 3 ]----------------------------------------------------------------------------------------- ------------------------------------------------------ book_id | 3 title | Трудно быть богом -[ RECORD 4 ]----------------------------------------------------------------------------------------- ------------------------------------------------------ book_id | 4 title | Война и мир -[ RECORD 5 ]----------------------------------------------------------------------------------------- ------------------------------------------------------ book_id | 5 title | Путешествия в некоторые удаленные страны мира в четырех частях: сочинение Лемюэля Гулливера, сначала хирурга, а затем капитана нескольких кораблей -[ RECORD 6 ]----------------------------------------------------------------------------------------- ------------------------------------------------------ book_id | 6 title | Хрестоматия ``` Обратите внимание, что названия могут быть длинными. Мы используем расширенный формат вывода psql, а появившийся в PostgreSQL 16 параметр xheader\_width поможет обрезать строку заголовка "широкого" столбца: ```text => \pset xheader_width 40 ``` ```text Expanded header width is 40. ``` Сравните вывод: ```sql => SELECT * FROM books \gx ``` ```text -[ RECORD 1 ]--------------------------- book_id | 1 title | Сказка о царе Салтане -[ RECORD 2 ]--------------------------- book_id | 2 title | Муму -[ RECORD 3 ]--------------------------- book_id | 3 title | Трудно быть богом -[ RECORD 4 ]--------------------------- book_id | 4 title | Война и мир -[ RECORD 5 ]--------------------------- book_id | 5 title | Путешествия в некоторые удаленные страны мира в четырех частях: сочинение Лемюэля Гулливера, сначала хирурга, а затем капитана нескольких кораблей -[ RECORD 6 ]--------------------------- book_id | 6 title | Хрестоматия ``` ### Авторы ```sql => \d authors ``` ## Page 14: Page 14 {#source-page-014} ```text Table "bookstore.authors" Column | Type | Collation | Nullable | Default -------------+---------+-----------+----------+------------------------------ author_id | integer | | not null | generated always as identity last_name | text | | not null | first_name | text | | not null | middle_name | text | | | Indexes: "authors_pkey" PRIMARY KEY, btree (author_id) Referenced by: TABLE "authorship" CONSTRAINT "authorship_author_id_fkey" FOREIGN KEY (author_id) REFERENCES authors(author_id) ``` Здесь дополнительно используется ограничение целостности: NOT NULL — обязательность, то есть недопустимость неопределенных значений. ```sql => SELECT * FROM authors; ``` ```text author_id | last_name | first_name | middle_name -----------+------------+------------+------------- 1 | Пушкин | Александр | Сергеевич 2 | Тургенев | Иван | Сергеевич 3 | Стругацкий | Борис | Натанович 4 | Стругацкий | Аркадий | Натанович 5 | Толстой | Лев | Николаевич 6 | Свифт | Джонатан | (6 rows) ``` Обратите внимание, что отчество может отсутствовать (или быть заданным пустой строкой). Ограничение PRIMARY KEY в выводе команды \\d упоминалось вместе со словами «индекс» и «btree». Btree (B-дерево) — основной тип индекса, используемый в базах данных для ускорения поиска и для поддержки ограничений целостности (первичного ключа и уникальности). Представим себе, что в магазине продаются книги миллиона авторов-однофамильцев: ```text => BEGIN; -- начнем транзакцию, чтобы откатить потом изменения ``` ```text BEGIN ``` ```sql => INSERT INTO authors(first_name, last_name) SELECT 'Графоман', 'Графоманов' FROM generate_series(1, 1_000_000); ``` ```sql INSERT 0 1000000 ``` Сколько времени занимает поиск одного автора в такой таблице? ```text => \timing on ``` ```text Timing is on. ``` ```sql => SELECT * FROM authors WHERE last_name = 'Пушкин'; ``` ```text author_id | last_name | first_name | middle_name -----------+-----------+------------+------------- 1 | Пушкин | Александр | Сергеевич (1 row) ``` ```text Time: 157,298 ms ``` ```text => \timing off ``` ```text Timing is off. ``` Если попросить оптимизатор показать план запроса, мы увидим в нем Seq Scan — последовательное сканирование всей таблицы в поисках нужного значения (Filter): ```sql => EXPLAIN (costs off) SELECT * FROM authors WHERE last_name = 'Пушкин'; ``` ```text QUERY PLAN ---------------------------------------- Seq Scan on authors Filter: (last_name = 'Пушкин'::text) (2 rows) ``` А если искать по полю, которое проиндексировано? ```text => \timing on ``` ## Page 15: Page 15 {#source-page-015} ```text Timing is on. ``` ```sql => SELECT * FROM authors WHERE author_id = 1; ``` ```text author_id | last_name | first_name | middle_name -----------+-----------+------------+------------- 1 | Пушкин | Александр | Сергеевич (1 row) ``` ```text Time: 0,579 ms ``` ```text => \timing off ``` ```text Timing is off. ``` Время уменьшилось на несколько порядков. А в плане запроса появился индекс: ```sql => EXPLAIN (costs off) SELECT * FROM authors WHERE author_id = 1; ``` ```text QUERY PLAN ------------------------------------------ Index Scan using authors_pkey on authors Index Cond: (author_id = 1) (2 rows) ``` Можно создать индекс и по фамилии (и проанализировать таблицу, чтобы собрать актуальную статистику): ```text => ANALYZE authors; ``` ```text ANALYZE ``` ```sql => CREATE INDEX ON authors(last_name); ``` ```sql CREATE INDEX ``` ```sql => EXPLAIN (costs off) SELECT * FROM authors WHERE last_name = 'Пушкин'; ``` ```text QUERY PLAN --------------------------------------------------- Index Scan using authors_last_name_idx on authors Index Cond: (last_name = 'Пушкин'::text) (2 rows) ``` Однако индекс не является универсальным средством увеличения производительности. Обычно индекс очень полезен, если из таблицы требуется выбрать небольшую долю всех имеющихся строк. Если нужно прочитать много данных, индекс будет только мешать, и оптимизатор это понимает: ```sql => EXPLAIN (costs off) SELECT * FROM authors WHERE last_name = 'Графоманов'; ``` ```text QUERY PLAN -------------------------------------------- Seq Scan on authors Filter: (last_name = 'Графоманов'::text) (2 rows) ``` Кроме того, надо учитывать накладные расходы на обновление индексов при изменении таблицы и занимаемое ими место на диске. Отменим все наши изменения (включая создание индекса): ```text => ROLLBACK; ``` ```text ROLLBACK ``` ```text => ANALYZE authors; ``` ```text ANALYZE ``` ### Авторства С помощью этой таблицы реализуется связь «многие ко многим». ```sql => \d authorship ``` ## Page 16: Page 16 {#source-page-016} ```text Table "bookstore.authorship" Column | Type | Collation | Nullable | Default -----------+---------+-----------+----------+--------- book_id | integer | | not null | author_id | integer | | not null | seq_num | integer | | not null | Indexes: "authorship_pkey" PRIMARY KEY, btree (book_id, author_id) Foreign-key constraints: "authorship_author_id_fkey" FOREIGN KEY (author_id) REFERENCES authors(author_id) "authorship_book_id_fkey" FOREIGN KEY (book_id) REFERENCES books(book_id) ``` Здесь к уже использованным ограничениям добавляется ограничение ссылочной целостности: FOREIGN KEY — внешний ключ. Фактически таблица содержит два внешних ключа: один ссылается на таблицу книг, другой — на таблицу авторов. Столбец seq\_num определяет последовательность, в которой должны перечисляться авторы одной книги, если их несколько. Также обратите внимание на то, что первичный ключ — составной. ```sql => SELECT * FROM authorship; ``` ```text book_id | author_id | seq_num ---------+-----------+--------- 1 | 1 | 1 2 | 2 | 1 3 | 3 | 2 3 | 4 | 1 4 | 5 | 1 5 | 6 | 1 6 | 1 | 1 6 | 5 | 2 6 | 2 | 3 (9 rows) ``` ### Операции ```sql => \d operations ``` ```text Table "bookstore.operations" Column | Type | Collation | Nullable | Default --------------+---------+-----------+----------+------------------------------ operation_id | integer | | not null | generated always as identity book_id | integer | | not null | qty_change | integer | | not null | date_created | date | | not null | CURRENT_DATE Indexes: "operations_pkey" PRIMARY KEY, btree (operation_id) Foreign-key constraints: "operations_book_id_fkey" FOREIGN KEY (book_id) REFERENCES books(book_id) ``` В этой таблице используется еще один тип данных: date — дата (без указания времени). Для столбца date\_created указано значение по умолчанию (DEFAULT) — текущая дата. ```sql => SELECT * FROM operations; ``` ```text operation_id | book_id | qty_change | date_created --------------+---------+------------+-------------- 1 | 1 | 10 | 2026-04-10 2 | 1 | 10 | 2026-04-10 3 | 1 | -1 | 2026-04-10 (3 rows) ``` Кроме тех типов данных, которые используются в таблицах приложения, мы постоянно будем встречаться с логическим типом (boolean). Например, такой тип имеют логические выражения в условии WHERE. > [!NOTE] > **Note** > Важно помнить, что, в отличие от традиционных языков программирования, SQL использует трехзначную логику: кроме значений true и false, логическое значение может принимать неопределенное значение NULL (которое можно понимать как «значение неизвестно»). ## Page 17: Page 17 {#source-page-017} В примерах встречаются и некоторые другие типы данных, информацию о работе с ними можно найти в материалах курса («Основные типы данных и функции», datatypes.pdf). Кроме того, в курсе мы подробно рассмотрим более сложные типы: составной — записи, аналогичные строкам таблиц (в теме «SQL. Составные типы»); массивы (в теме «PL/pgSQL. Массивы»). ## Page 18: Организация интерфейса {#source-page-018} **Таблицы и триггеры** - чтение данных напрямую из таблицы (представления); - запись данных напрямую в таблицу (представление), - плюс триггеры для изменения связанных таблиц - приложение должно быть в курсе модели данных, - максимальная гибкость - сложно поддерживать согласованность - Функции - чтение данных из табличных функций; - запись данных через вызов функций - приложение отделено от модели данных и ограничено API - большой объем работы по изготовлению функций-оберток, - потенциальные проблемы с производительностью ### Комментарии преподавателя Есть несколько способов организации интерфейса между клиентской и серверной частями приложения. Один вариант — разрешить приложению напрямую обращаться к таблицам в базе данных и изменять их. При этом от приложения требуется детальное «знание» модели данных. Отчасти это требование можно ослабить за счет использования представлений (view). Кроме того, от приложения требуется и определенная дисциплина — иначе очень сложно поддержать согласованность данных, защищаясь на уровне БД от любых возможных некорректных действий приложения. Но в этом случае достигается максимальная гибкость. Другой вариант — запретить приложению доступ к таблицам и разрешить только вызовы функций. Чтение данных можно организовать с помощью табличных функций (которые возвращают набор строк). Изменение данных можно выполнять, вызывая другие функции и передавая им необходимые данные. В этом случае внутри функций можно реализовать все необходимые проверки согласованности — база данных будет защищена, но приложение сможет пользоваться только предоставленным и ограниченным набором возможностей. Такой вариант требует написания большого количества функций-оберток и может привести к потере производительности. Вполне возможны и промежуточные варианты. Например, разрешить чтение данных непосредственно из таблиц, а изменение выполнять только через вызов функций. ## Page 19: Интерфейс покупателя {#source-page-019} ![Source slide page 19: Интерфейс покупателя](images/page-019.png){#source-figure-019 caption="Source slide page 19: Интерфейс покупателя"} ### Комментарии преподавателя В нашем приложении мы попробуем разные варианты организации интерфейса (хотя в реальной жизни обычно лучше систематически придерживаться какого-то одного подхода). Магазин будет использовать интерфейсные функции: ● для поиска книг — get\_catalog (тема «SQL. Составные типы»); ● для покупки книг — buy\_book («PL/pgSQL. Выполнение запросов»). ## Page 20: Интерфейс сотрудника {#source-page-020} ![Source slide page 20: Интерфейс сотрудника](images/page-020.png){#source-figure-020 caption="Source slide page 20: Интерфейс сотрудника"} ### Комментарии преподавателя «Админка» для получения данных будет использовать представления (которые мы создадим в практике к этой теме): ● список книг — catalog\_v; ● список авторов — authors\_v; ● список операций — operations\_v. Добавление автора будет выполнять функция add\_author (создадим ее в теме «PL/pgSQL. Выполнение запросов»), добавление книги — функция add\_book («PL/pgSQL. Массивы»). Для заказа книг сделаем представление catalog\_v обновляемым («PL/pgSQL. Триггеры»). ## Page 21: Представления {#source-page-021} Представление — запрос, у которого есть имя. Например, можно создать представление, которое показывает только авторов без отчества: ```sql => CREATE VIEW authors_no_middle_name AS SELECT author_id, first_name, last_name FROM authors WHERE nullif(middle_name,'') IS NULL; ``` ```sql CREATE VIEW ``` Теперь имя представления можно использовать в запросах практически так же, как и таблицу: ```sql => SELECT * FROM authors_no_middle_name; ``` ```text author_id | first_name | last_name -----------+------------+----------- 6 | Джонатан | Свифт (1 row) ``` В простом случае с представлением будут работать и другие операции, например: ```sql => UPDATE authors_no_middle_name SET last_name = initcap(last_name); ``` ```sql UPDATE 1 ``` С помощью триггеров можно сделать так, чтобы и в сложных случаях для представлений работали вставка, обновление и удаление строк. Мы рассмотрим это в теме «PL/pgSQL. Триггеры». При планировании запроса представление «разворачивается» до базовых таблиц: ```sql => EXPLAIN (costs off) SELECT * FROM authors_no_middle_name; ``` ```text QUERY PLAN --------------------------------------------------- Seq Scan on authors Filter: (NULLIF(middle_name, ''::text) IS NULL) (2 rows) ``` Приложение использует три представления. Сначала они будут очень простыми, но в следующих темах мы перенесем в них часть логики приложения. Представление для авторов — конкатенация фамилии, имени и отчества (если оно есть): ```sql => SELECT * FROM authors_v; ``` ```text author_id | display_name -----------+------------------------------ 1 | Пушкин Александр Сергеевич 2 | Тургенев Иван Сергеевич 3 | Стругацкий Борис Натанович 4 | Стругацкий Аркадий Натанович 5 | Толстой Лев Николаевич 6 | Свифт Джонатан (6 rows) ``` Представление для каталога книг — пока просто название книги: ```sql => SELECT * FROM catalog_v \gx ``` ## Page 22: Page 22 {#source-page-022} ```text -[ RECORD 1 ]+-------------------------------------------------------- book_id | 1 display_name | Сказка о царе Салтане -[ RECORD 2 ]+-------------------------------------------------------- book_id | 2 display_name | Муму -[ RECORD 3 ]+-------------------------------------------------------- book_id | 3 display_name | Трудно быть богом -[ RECORD 4 ]+-------------------------------------------------------- book_id | 4 display_name | Война и мир -[ RECORD 5 ]+-------------------------------------------------------- book_id | 5 display_name | Путешествия в некоторые удаленные страны мира в четырех частях: сочинение Лемюэля Гулливера, сначала хирурга, а затем капитана нескольких кораблей -[ RECORD 6 ]+-------------------------------------------------------- book_id | 6 display_name | Хрестоматия ``` Представление для операций — дополнительно определяет тип операции (поступление или покупка): ```sql => SELECT * FROM operations_v; ``` ```text book_id | op_type | qty_change | date_created ---------+-------------+------------+-------------- 1 | Поступление | 10 | 10.04.2026 1 | Поступление | 10 | 10.04.2026 1 | Покупка | 1 | 10.04.2026 (3 rows) ``` ## Page 23: Итоги {#source-page-023} **Проектирование баз данных — отдельная тема** - теория важна, но не заменяет здравый смысл - Отсутствие избыточности в данных делает работу удобнее и упрощает поддержку согласованности - Для клиент-серверного интерфейса можно использовать таблицы, представления, функции, триггеры ## Page 24: Практика {#source-page-024} 1. В базе данных bookstore создайте схему bookstore. Настройте путь поиска к этой схеме на уровне подключения к БД. 2. В схеме bookstore создайте таблицы books, authors, authorship и operations с необходимыми ограничениями целостности так, чтобы они соответствовали показанным в демонстрации. 3. Вставьте в таблицы данные о нескольких книгах. Проверьте себя с помощью запросов. 4. В схеме bookstore создайте представления authors\_v, catalog\_v и operations\_v так, чтобы они соответствовали показанным в демонстрации. Проверьте, что приложение стало показывать данные на вкладках «Книги», «Авторы» и «Каталог». ### Комментарии преподавателя 1. Вспомните материал темы «Организация данных. Логическая структура». 2. Ориентируйтесь на показанный в демонстрации вывод команд \\d утилиты psql. 3. Вы можете использовать те же данные, что были показаны в демонстрации, или придумать свои собственные. 4. Попробуйте написать запросы к базовым таблицам, возвращающие тот же результат, что и показанные в демонстрации запросы к представлениям. Затем оформите запросы в виде представлений. После выполнения практики обязательно сверьте свои запросы с решением, приведенным к этой теме. При необходимости внесите коррективы. ## Page 25: 1. Схема и путь поиска {#source-page-025} ```sql => CREATE DATABASE bookstore; ``` ```sql CREATE DATABASE ``` ```sql => \c bookstore ``` ```text You are now connected to database "bookstore" as user "student". ``` ```sql => CREATE SCHEMA bookstore; ``` ```sql CREATE SCHEMA ``` ```sql => ALTER DATABASE bookstore SET search_path = bookstore, public; ``` ```sql ALTER DATABASE ``` ```sql => \c ``` ```text You are now connected to database "bookstore" as user "student". ``` ```text => SHOW search_path; ``` ```text search_path ------------------- bookstore, public (1 row) ``` ### 2. Таблицы Авторы: ```sql => CREATE TABLE authors( author_id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, last_name text NOT NULL, first_name text NOT NULL, middle_name text ); ``` ```sql CREATE TABLE ``` Книги: ```sql => CREATE TABLE books( book_id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, title text NOT NULL ); ``` ```sql CREATE TABLE ``` Авторство: ```sql => CREATE TABLE authorship( book_id integer REFERENCES books, author_id integer REFERENCES authors, seq_num integer NOT NULL, PRIMARY KEY (book_id,author_id) ); ``` ```sql CREATE TABLE ``` Операции: ```sql => CREATE TABLE operations( operation_id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, book_id integer NOT NULL REFERENCES books, qty_change integer NOT NULL, date_created date NOT NULL DEFAULT current_date ); ``` ```sql CREATE TABLE ``` ### 3. Данные Авторы: ```sql => INSERT INTO authors(last_name, first_name, middle_name) VALUES ('Пушкин', 'Александр', 'Сергеевич'), ('Тургенев', 'Иван', 'Сергеевич'), ('Стругацкий', 'Борис', 'Натанович'), ('Стругацкий', 'Аркадий', 'Натанович'), ('Толстой', 'Лев', 'Николаевич'), ('Свифт', 'Джонатан', NULL); ``` ```sql INSERT 0 6 ``` Книги: ```sql => INSERT INTO books(title) VALUES ('Сказка о царе Салтане'), ('Муму'), ('Трудно быть богом'), ('Война и мир'), ('Путешествия в некоторые удаленные страны мира в четырех частях: сочинение Лемюэля Гулливера, сначала хирурга, а затем капитана нескольких кораблей'), ('Хрестоматия'); ``` ```sql INSERT 0 6 ``` Авторство: ## Page 26: Page 26 {#source-page-026} ```sql => INSERT INTO authorship(book_id, author_id, seq_num) VALUES (1, 1, 1), (2, 2, 1), (3, 3, 2), (3, 4, 1), (4, 5, 1), (5, 6, 1), (6, 1, 1), (6, 5, 2), (6, 2, 3); ``` ```sql INSERT 0 9 ``` Операции: ```sql => INSERT INTO operations(book_id, qty_change) VALUES (1, 10), (1, 10), (1, -1); ``` ```sql INSERT 0 3 ``` ### 4. Представления Представление для авторов: ```sql => CREATE VIEW authors_v AS SELECT a.author_id, a.last_name || ' ' || a.first_name || coalesce(' ' || nullif(a.middle_name, ''), '') AS display_name FROM authors a; ``` ```sql CREATE VIEW ``` Представление для каталога: ```sql => CREATE VIEW catalog_v AS SELECT b.book_id, b.title AS display_name FROM books b; ``` ```sql CREATE VIEW ``` Представление для операций: ```sql => CREATE VIEW operations_v AS SELECT book_id, CASE WHEN qty_change > 0 THEN 'Поступление' ELSE 'Покупка' END op_type, abs(qty_change) qty_change, to_char(date_created, 'DD.MM.YYYY') date_created FROM operations ORDER BY operation_id; ``` ```sql CREATE VIEW ``` ## Page 27: Практика+ {#source-page-027} 1. Какие дополнительные атрибуты могут появиться у выделенных сущностей при развитии приложения? 2. Допустим, требуется хранить информацию об издательстве. Дополните ER-диаграмму и отобразите ее в таблицы. 3. Некоторые книги могут входить в серии (например, «Библиотека приключений»). Как изменится схема данных? 4. Пусть наш магазин стал торговать компьютерными комплектующими (материнскими платами, процессорами, памятью, жесткими дисками, мониторами и т. п.). Какие сущности и какие атрибуты вы бы выделили? Учтите, что на рынке постоянно появляются новые типы оборудования со своими характеристиками. ### Комментарии преподавателя 3. Разные издательства вполне могут иметь серии, названные одинаково. ## Page 28: 1. Дополнительные атрибуты {#source-page-028} Несколько примеров: Авторы: роль (автор, редактор, переводчик и т. п.); Книги: аннотация; Операции: текущий статус (оплачено, передано в службу доставки и т. п.). ### 2. Издательства Надо добавить сущность «Издательство» с атрибутом «Название» (как минимум). Книги связаны с издательствами отношением «многие ко многим»: книга может публиковаться в разных издательствах. Поэтому на физическом уровне потребуется промежуточная таблица «Публикации» с атрибутом «Год издания». (Разумеется, это упрощенная модель; при желании ее можно уточнять еще очень долго.) ### 3. Серии Добавим сущность «Серия». К серии относится не сама книга, а ее конкретная публикация, так что имеет смысл вывести «Публикацию» на уровень ER-модели и связать ее с серий отношением «один ко многим» (каждая публикация принадлежит к одной серии, каждая серия может включать несколько публикаций). Также серия связана отношением «один ко многим» с издательством (у издательства может быть несколько серий, а каждая серия принадлежит конкретному издательству). Остается вопрос о внесерийных изданиях. Его можно решить либо введением фиктивной серии «Без серии», либо возможностью не указывать для публикации внешний ключ серии. ### 4. Компьютерные комплектующие Рассматривая каждый конкретный тип комплектующих, можно без труда выделить необходимые атрибуты. Какие-то атрибуты будут общими (скажем, фирма-производитель и название модели), а какие-то будут иметь смысл только для данного конкретного типа. Например: Процессор: частота; Монитор: диагональ, разрешение; Жесткий диск: типоразмер, емкость. Проблема в том, что рынок комплектующих очень динамичен. Некоторое время назад жесткие диски определялись частотой вращения и емкостью, а сейчас важен тип (твердотельный, вращающийся, гибридный). Для мониторов во времена ЭЛТ была важна частота обновления, а сейчас важен тип матрицы. Дисководы уже никому не нужны, зато появились флеш-накопители. И так далее. Таким образом, либо придется постоянно изменять схему данных (а, значит, и постоянно изменять приложение, которое работает с этими данными!), либо искать более универсальную модель за счета отказа от жесткой структуры и контроля согласованности. Некоторые универсальные модели (например, хранение части данных в JSON) мы затрагивали в презентации.