провести анализ предметной области и разработать начальную схему базы данных.
письменный отчет.
На первом этапе вам необходимо выбрать предметную область, в рамках которой вы будете осуществлять все дальнейшие работы в течение курса. Это может быть компания, организация, интернет-проект или любая другая сфера деятельности. Для этой предметной области сформулируйте описание функциональных требований. На основе составленных описаний вы выделяете сущности, их атрибуты с указанием типов и связи между ними. Составьте ER диаграмму, которая должна полностью отображать информацию о сущностях. Важно: содержательное и полное описание атрибутов является важным шагом для правильного проектирования БД, поэтому описание атрибутов должно содержать в себе информацию о типе данных, первичных и внешних ключах. Последним этапом является нормализация схемы базы данных, что включает в себя проведение анализа составленной ранее схемы БД для определения функциональных зависимостей. Описывая этот процесс, важно рассмотреть каждую таблицу и выявить функциональные зависимости между ее атрибутами. Затем следует представить описание процесса нормализации с учетом полученных зависимостей, переводя схему в нормальную форму. Этот раздел также должен содержать в себе обоснование нахождения базы данных в нормальной форме, демонстрируя, какие конкретные нормальные формы были достигнуты и каким образом это обеспечивает эффективное хранение и управление данными в базе данных.
- Рекомендованное количество отношений: 10 (Принимающий оставляет за собой право добавить ФТ для увеличения кол-ва отношений);
- Нормальная форма: 3НФ.
развернуть СУБД, создание таблиц и заполнение данными.
письменный отчет, демонстрация работы БД.
На втором этапе вам необходимо развернуть СУБД PostgreSQL на сервере или локальной машине внутри docker контейнера.
После успешного создания базы данных - создайте отношения, на основе нормализованной схемы, составленной на предыдущем этапе. Таблицы должны полностью соответствовать структуре, отображенной на схеме, с учетом всех полей данных, ограничений и связей с другими таблицами. Для этого вам нужно написать идемпотентные файлы миграции. Так же позаботьтесь о том, чтобы при создании нового контейнера ваши миграции выполнялись автоматически. Каждая новая правка или исправление должна быть в новом файле. Файлы миграций должны соответствовать конверсии о семантическом версионировании. Так же через env должна быть возможность передать версию, до которой будут происходить миграции. Если env не указан, то до последней. Для вашей БД в кластере СУБД должнен быть создан пользователь с правами на создания БД. И уже от его имени должна создаваться БД и выполнятся миграции.
После создания таблиц их следует заполнить тестовыми данными, соответствующими вашей предметной области. Это позволит вам проверить корректность структуры таблиц и их соответствие функциональным требованиям, а также в будущем тестировать производительность запросов. Для этого вам так же нужно написать скрипты и они так же должны выполняться автоматически при развертывании контейнера, основные отношения должны иметь кол-во кортежей больше 1кк. В идеале написать скрипт, генерирующий любое кол-во записей и передавать динамически переменную через env.
Также нужно добавить еще роли, которые имеют возможность подключения к БД - это будут роли: reader, writer. Reader может только читать данные из таблиц, но не изменять их. Writer может читать и добавлять, изменять данные, но не может их удалять. Затем создадите пользователя analytic и дайте ему доступ на чтение ровно одной таблицы. Также должна быть групповая роль, зайти под которой в базу данных нельзя. Она имеет все возможности манипуляций над базой данных: запись новых данных, удаление и чтение. Создать n пользователей, с возможностью только подключиться к БД и присоединить его в групповой роли. Для всего этого должны быть написаны SQL команды в bash скрипте, который возможно применить к любой базе данных просто запустив его на сервере с заранее известными переменными POSTGRES_DB, POSTGRES_USER и массивом имен. Во время сдачи этого этапа вам должны выдаться условия запросов, которые вам понадобятся на следующем этапе.
повышение производительности и отказоустойчивости СУБД.
письменный отчет, демонстрация работы БД.
В первую очередь необходимо написать и выполнить запросы к базе данных, выданные преподавателем в момент защиты второго этапа. Для того чтобы оценить и сравнить производительность различных запросов, вам потребуется создать сервис (на любом ЯП), который будет вызывать Explain Analyze для каждого запроса и измерять Cost. Полученные результаты форматировать и записывать в отдельный файл: лучший, средний, худший случай для каждого запроса. (Каждый новый запуск сервиса пишет новый файл, старые сохраняются. Кол-во попыток к каждому запросу определяется в env)
Далее добавьте индексы к вашим отношениям и запустите сервис снова. Повторите процедуру несколько раз и попытайтесь добиться оптимальной производительности для каждого запроса. Сделайте сводную таблицу на сколько процентов ваши запросы стали работать лучше/хуже от первого запуска до крайнего. Так же вы можете попробовать посмотреть Explain запроса и переписать его(допустим используя CTE) для улучшения производительности. Так же можете повесить триггер для деморализации некоторых данных. (Данный параграф является необязательным.)
Создайте партисипант для таблицы имеющей больше всего записей. Подумайте каким способов лучше делить таблицу. Снова запустите сервис и полученные результаты добавьте в сводную таблицу.
Напишите скрипт, который будет создавать бэкапы базы данных, каждые n- часов, последние m-бекапов должны храниться, более старые должны удаляться. (параметры передаются через env)
Наконец, для отказоустойчивости вашего кластера СУБД разверните 2 реплики Postgres. С использованием Patroni. Проверьте что при отказе мастера происходит автоматическое переключение.