О курсе

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

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

Что даёт курс

• Понимание реляционной модели, ключей и ограничений целостности, реляционной алгебры и соответствия ей конструкций SQL.
• Умение проектировать схему базы данных по требованиям предметной области (модель «сущность — связь», функциональные зависимости, нормальные формы до НФБК) и обосновывать решения.
• Умение писать на SQL запросы с соединениями, подзапросами, группировкой, оконными функциями и рекурсией, изменять данные; учитывать трёхзначную логику SQL (NULL).
• Знание устройства хранения и индексов (B-дерево, GIN, BRIN), умение читать план выполнения запроса, находить причину низкой производительности и устранять её.
• Понимание свойств ACID, аномалий параллельного доступа, уровней изоляции, MVCC и журнала упреждающей записи; умение выбирать уровень изоляции и способ блокировки.
• Умение создавать функции, процедуры и триггеры на PL/pgSQL и разрабатывать приложения на Python (psycopg 3), работающие с базой безопасно и эффективно.
• Умение выполнять резервное копирование и восстановление, разграничивать права доступа; представление о масштабировании, теореме CAP и нереляционных системах.
• Владение psql, системным каталогом и представлениями статистики, EXPLAIN (ANALYZE, BUFFERS), pg_stat_statements, pageinspect, pg_dump, pg_restore, pg_basebackup, pgbench.

Связь с другими курсами

Курс опирается на программирование на Python на уровне первого курса, базовые навыки работы в командной строке Linux и дискретную математику (множества, отношения, логика высказываний и предикатов). Специальной подготовки по базам данных не требуется. Результаты курса пригодятся в курсах «Разработка Web-приложений», «Облачные технологии», по анализу данных, распределённым системам, информационной безопасности и в дипломной работе.

Темы курса

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

Учебная среда

Все лабораторные работы выполняются в PostgreSQL 16. Рекомендуемая платформа — Ubuntu 24.04 LTS: установленная на компьютер, в виртуальной машине (VirtualBox, VMware, Hyper-V, UTM) или в подсистеме WSL2 на Windows. В Ubuntu 24.04 версия 16 входит в стандартный репозиторий:

sudo apt update
sudo apt install -y postgresql postgresql-contrib python3-psycopg python3-venv
sudo -u postgres createuser --superuser "$USER"
createdb bookstore

В WSL2 без systemd сервер запускается командой sudo service postgresql start. Допустима и установка PostgreSQL 16 на Windows или macOS (официальные сборки с postgresql.org) либо запуск в контейнере Docker — различия в этих средах в лабораторных работах не разбираются.

Учебная база «Книжный узел» создаётся в первой лабораторной работе из двух скриптов архива окружения: schema.sql (таблицы и ограничения) и data.sql (детерминированный генератор данных; у всех получаются одинаковые строки). Масштаб scale=1 (2 тыс. книг, 5 тыс. покупателей, 20 тыс. заказов) используется в темах 1–9 и 14–16, масштаб scale=50 (100 тыс. книг, 250 тыс. покупателей, 1 млн заказов, около 450 МБ) — в темах 10–13. К остальным лабораторным работам прилагаются архивы окружения со скриптами подготовки, данными и программами самопроверки.

Минимальные требования: 4 ГБ ОЗУ, 5 ГБ свободного места, доступ в Интернет на этапе установки пакетов.

Как работать с материалами

Тема изучается в следующем порядке.

1. Теория. Лекция читается целиком, с остановками на схемах и примерах. Каждый запрос из лекции полезно выполнить на своей учебной базе и сравнить результат с приведённым: данные детерминированы, поэтому результаты должны совпасть. Ссылки в тексте вида ¹ ведут к списку литературы в конце темы; к документации PostgreSQL и классическим статьям стоит обращаться, когда хочется понять, почему механизм устроен именно так.
2. Дополнительно. Вопросы для самопроверки показывают, усвоен ли материал: если на вопрос не удаётся ответить своими словами, соответствующий раздел стоит перечитать. Глоссарий и шпаргалка нужны во время лабораторной работы.
3. Лабораторная работа или практическое занятие. Главный принцип — предсказать, затем проверить: прежде чем выполнить запрос, следует записать, сколько строк он вернёт, какой план выберет планировщик, что увидит вторая транзакция, — а после объяснить расхождение. Базовая часть работы — основная, продвинутая углублённая и выполняется по желанию.

Темы следует проходить по порядку. Если хочется сократить путь, минимальная траектория, дающая цельную картину, — темы 1–5, 7, 8, 10, 11, 12 и 15.

Итоговые вопросы для самопроверки

Вопросы охватывают весь курс; если на вопрос не удаётся ответить своими словами, стоит вернуться к соответствующей теме.

1. База данных и СУБД. Преимущества СУБД перед файлами. Трёхуровневая архитектура ANSI/SPARC и независимость данных.
2. Архитектура PostgreSQL: процессы, кластер, базы данных, схемы, роли.
3. Реляционная модель: домен, атрибут, кортеж, отношение. Отличия таблицы SQL от отношения.
4. Ключи: суперключ, потенциальный, первичный, внешний. Естественные и суррогатные ключи.
5. Ограничения целостности в SQL. Ссылочная целостность и действия при удалении и изменении.
6. Значение NULL и трёхзначная логика. Типичные ошибки при работе с NULL.
7. Операции реляционной алгебры. Деление и его выражение в SQL.
8. Логический порядок выполнения запроса SELECT. Сортировка, ограничение числа строк, операции над множествами.
9. Внутренние и внешние соединения. Условие в ON и в WHERE для внешнего соединения.
10. Подзапросы: скалярные, табличные, коррелированные. EXISTS, IN, ANY/ALL. Полусоединение и антисоединение.
11. Агрегатные функции и группировка. WHERE и HAVING. GROUPING SETS, ROLLUP, CUBE.
12. Оконные функции: разделы, порядок, рамка окна. Ранжирование и накопительные итоги.
13. Изменение данных: INSERT, UPDATE, DELETE, RETURNING, ON CONFLICT, MERGE.
14. Общие табличные выражения и рекурсивные запросы.
15. Представления: обновляемые и материализованные представления.
16. Модель «сущность — связь»: сущности, атрибуты, связи, кардинальность, слабые сущности. Преобразование в реляционную схему.
17. Отображение наследования в реляционные таблицы: три способа и их сравнение.
18. Аномалии модификации. Функциональные зависимости, аксиомы Армстронга, замыкание множества атрибутов.
19. Нормальные формы: 1НФ, 2НФ, 3НФ, НФБК. Декомпозиция без потерь и с сохранением зависимостей.
20. Денормализация: цели, способы и цена.
21. Выбор типов данных: числа, строки, дата и время, UUID, перечисления. Ошибки округления и часовые пояса.
22. Хранение данных в PostgreSQL: страницы, кортежи, выравнивание, TOAST. Секционирование таблиц.
23. JSON и JSONB: возможности, индексирование, уместность применения.
24. Индекс B-дерево: устройство, поиск, высота, поддерживаемые операции. Составные, частичные и покрывающие индексы.
25. Индексы GIN, GiST, BRIN и Hash: назначение и выбор. Цена индексов.
26. Этапы обработки запроса. Методы доступа к таблице.
27. Методы соединения: вложенные циклы, хеширование, слияние. Условия эффективности каждого.
28. Стоимостная оптимизация: модель стоимости, статистика, оценка кардинальности и её ошибки.
29. Чтение плана выполнения. Типичные причины медленных запросов и способы их устранения.
30. Транзакции и свойства ACID. Аномалии параллельного выполнения.
31. Уровни изоляции стандарта SQL и их реализация в PostgreSQL. Изоляция снимков и асимметрия записи. SSI.
32. Многоверсионное управление параллельным доступом в PostgreSQL. Видимость версий, VACUUM.
33. Блокировки таблиц и строк. Взаимоблокировки. Оптимистическая и пессимистическая блокировки.
34. Журнал упреждающей записи. Контрольные точки. Восстановление после сбоя.
35. Резервное копирование: логическое и физическое. Восстановление на момент времени.
36. Физическая и логическая репликация.
37. Функции и процедуры на PL/pgSQL. Категории изменчивости функций.
38. Триггеры: виды, применение, риски.
39. Взаимодействие приложения с базой данных: пулы соединений, транзакции, параметризованные запросы, проблема N+1.
40. Безопасность базы данных: роли, привилегии, защита строк (RLS), аутентификация.
41. Масштабирование: репликация, секционирование, шардирование. Распределённые транзакции и двухфазная фиксация.
42. Теорема CAP. Модели данных NoSQL-систем. OLTP и OLAP, колоночное хранение.

Рекомендуемая литература

Основная

1. Silberschatz A., Korth H. F., Sudarshan S. Database System Concepts. 7th ed. — McGraw-Hill, 2019.
2. Новиков Б. А., Горшкова Е. А., Графеева Н. Г. Основы технологий баз данных. 2-е изд. — М.: ДМК Пресс, 2020.
3. Рогов Е. В. PostgreSQL 16 изнутри. — М.: ДМК Пресс, 2024. Свободная электронная версия: postgrespro.ru/education/books/internals.
4. Дейт К. Дж. Введение в системы баз данных. 8-е изд. — М.: Вильямс, 2005.

Дополнительная

1. Garcia-Molina H., Ullman J. D., Widom J. Database Systems: The Complete Book. 2nd ed. — Pearson, 2008.
2. Ramakrishnan R., Gehrke J. Database Management Systems. 3rd ed. — McGraw-Hill, 2003.
3. Клеппман М. Высоконагруженные приложения. Программирование, масштабирование, поддержка. — СПб.: Питер, 2018.
4. Winand M. SQL Performance Explained. — 2012. Свободная версия: use-the-index-luke.com.
5. Моргунов Е. П. PostgreSQL. Основы языка SQL. — СПб.: БХВ-Петербург, 2018. Свободная электронная версия: postgrespro.ru/education/books/sqlprimer.
6. Hellerstein J. M., Stonebraker M., Hamilton J. Architecture of a Database System. — Foundations and Trends in Databases, 2007, Т. 1, № 2.

Интернет-ресурсы

• Документация PostgreSQL 16 и её русский перевод — основной справочник курса.
• Учебные курсы и книги Postgres Professional — свободно доступные курсы по администрированию и разработке на PostgreSQL.
• CMU 15-445/645 Database Systems — лекции Энди Павло о внутреннем устройстве СУБД.
• Use The Index, Luke! — индексы и производительность SQL для разработчиков.
• explain.depesz.com и explain.dalibo.com — наглядное отображение планов выполнения.
• Modern SQL — возможности стандарта SQL и их поддержка в разных СУБД.
• Jepsen — анализы согласованности распределённых баз данных.

Курсовой проект. База данных информационной системы

• Разделы курса: темы 2–15

Назначение проекта

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

Результат проекта — база данных PostgreSQL для информационной системы по варианту, набор скриптов, воссоздающих её «с нуля», небольшое приложение и проектная документация с обоснованием решений.

Варианты

Вариант выбирается по интересу (или случайно — по номеру от 1 до 20); при желании одно требование можно заменить своим. Для каждого варианта указано ядро предметной области и одно характерное бизнес-правило, которое должно быть обеспечено базой данных (ограничением, триггером или процедурой).

Требования

Базовый уровень

1. Требования и концептуальная модель. Документ требований по шаблону templates/requirements.md, ER-диаграмма (нотация «вороньи лапки») не менее чем с 8 сущностями, хотя бы одной связью «многие ко многим» и одной слабой или ассоциативной сущностью (темы 7).
2. Логическая модель. Схема в НФБК; для трёх ключевых таблиц выписаны функциональные зависимости и доказано, что схема в НФБК; каждое отступление от нормализации (денормализация) обосновано и обеспечено механизмом поддержания согласованности (тема 8).
3. Физическая модель. Скрипты sql/*.sql, создающие базу «с нуля»: обоснованные типы данных (деньги, даты и время, перечисления), первичные и внешние ключи с осмысленными действиями ON DELETE, ограничения NOT NULL, UNIQUE, CHECK (не менее пяти), характерное бизнес-правило варианта обеспечено базой данных (темы 2, 9).
4. Данные. Детерминированный генератор тестовых данных: не менее 100 000 строк в главной таблице, правдоподобные распределения (не все значения равновероятны), граничные случаи — NULL там, где он допустим, пустые связи (темы 5–6).
5. Запросы. 15 запросов к бизнес-вопросам в queries/: среди них соединения с внешним соединением, антисоединение, группировка с HAVING, не менее трёх запросов с оконными функциями, рекурсивный запрос (если в модели нет иерархии — по календарю или генерации рядов), запрос с подзапросом в EXISTS (темы 3–6).
6. Производительность. Для пяти самых тяжёлых запросов — планы EXPLAIN (ANALYZE, BUFFERS) до и после создания индексов, таблица времён и объяснение выбора каждого индекса; все внешние ключи покрыты индексами либо отсутствие индекса обосновано (темы 10–11).
7. Серверная логика. Не менее двух функций или процедур и двух триггеров с осмысленным назначением (например, поддержание денормализованного счётчика, журнал изменений, проверка бизнес-правила) и тесты к ним — SQL-скрипт, демонстрирующий срабатывание и отказ (тема 14).
8. Параллельный доступ. Сценарий конкурентной операции варианта (например, одновременная выдача последнего экземпляра): описание возможной аномалии, выбранный способ защиты (уровень изоляции, SELECT … FOR UPDATE, атомарное обновление, ограничение исключения) и демонстрация двумя сессиями или скриптом (тема 12).
9. Безопасность. Не менее двух ролей приложения с минимальными привилегиями, защита строк (RLS) хотя бы для одной таблицы, проверка отказов (тема 15).
10. Резервное копирование. Описанная стратегия (что, как часто, где хранится) и проверенное восстановление из pg_dump в новую базу с подсчётом строк (тема 13).
11. Приложение. Консольное или веб-приложение на Python с psycopg 3, реализующее 3–5 сценариев пользователя, в том числе один изменяющий данные в транзакции; только параметризованные запросы, параметры подключения из переменных окружения (тема 15).
12. Скрипт check_project.py из окружения проходит без ошибок FAIL.

Повышенный уровень

Углублённая часть, по желанию; рекомендуется выполнить хотя бы два пункта:

13. Секционирование главной таблицы по времени с демонстрацией отсечения секций и процедурой создания новой секции (тема 9).
14. Материализованное представление для отчёта с обновлением REFRESH … CONCURRENTLY по расписанию или по событию и сравнением времени (тема 6).
15. Миграции: схема ведётся как последовательность версионированных миграций (самописная таблица schema_migrations или Alembic), включая одну миграцию, безопасную для работающего приложения (добавление столбца с последующим заполнением и ограничением NOT VALID → VALIDATE) (тема 15).
16. Нагрузочный тест: сценарий pgbench или Python-скрипт с параллельными клиентами для основной операции, измерение пропускной способности при разных уровнях изоляции, анализ ошибок сериализации и pg_stat_statements (темы 11–12).
17. Физическое резервирование: pg_basebackup, архив WAL и восстановление на момент времени до «ошибочной» операции (тема 13).

Окружение

Архив окружения: cp-env.zip. Содержит каркас репозитория проекта (sql/, queries/, app/), шаблоны документов (templates/requirements.md, design-decisions.md, report-outline.md) и скрипт автоматической проверки:

python3 check_project.py --schema <имя_схемы>

Скрипт создаёт чистую базу, загружает в неё sql/*.sql по порядку, проверяет структуру схемы (первичные ключи, внешние ключи и их индексы, ограничения, объём данных, функции, триггеры, роли, RLS) и выполняет все запросы из queries/. Пример вывода для учебной базы «Книжный узел» до доработки:

== структура схемы bookstore
  ok  таблиц в схеме bookstore: 12
  ok  таблицы без первичного ключа: нет
  ok  внешних ключей: 14
  ok  ограничений CHECK: 12 (рекомендуется не менее 5)
WARN  внешние ключи без индекса: book.book_category_id_fkey, ..., review.review_customer_id_fkey
FAIL  пользовательских триггеров: 0 (нужно не менее 2)

Проверка ловит типичные пропуски, но не оценивает качество проекта: о нём лучше судить по критериям готовности ниже.

Рекомендуемые этапы

Проектная документация

Структура — в templates/report-outline.md. Проектные решения фиксируются по мере работы в templates/design-decisions.md: к завершению проекта в журнале должны быть обоснованы выбор ключей, способ отображения наследования (если есть), отступления от НФБК, выбор типов для денег, дат и перечислений, каждый индекс, способ защиты от аномалии параллельного доступа и стратегия резервного копирования. К проектной документации прикладывается вывод check_project.py.

Критерии готовности

• Проектирование: требования записаны, ER-модель и нормализация выполнены, решения обоснованы.
• Реализация: схема и ограничения, данные, запросы, серверная логика и приложение работают; база воссоздаётся из скриптов «с нуля», check_project.py проходит без ошибок FAIL.
• Производительность и надёжность: индексы подтверждены планами, аномалия параллельного доступа воспроизведена и устранена, восстановление из резервной копии проверено.
• Записка и журнал проектных решений полны и честно описывают ограничения.
• Выбранные пункты углублённой части выполнены (по желанию).