Лабораторная работа №5-6
Цель работы: Изучить правила создания функций. Приобрести практические навыки создания функций в среде PostgreSQL. Научиться проектировать БД в целом на основе поставленного задания с использованием всех полученных ранее базовых знаний и навыков.
Задание. Общая часть: Ознакомиться с теоретическими сведениями о возможностях создания пользовательских функций в PostgreSQL. Разработать БД в соответствии с индивидуальным заданием. Создать функции, реализующие интерфейс для работы с базой данных. Проверить работоспособность функций путем выполнения этих функций с параметрами, обеспечивающими как успешное выполнение функции, так и невыполнение функции.
Обязательные требования к БД:
1) Наличие таблиц-справочников и таблиц, использующих справочники. Предусмотреть сохранение ссылочной целостности для таблиц, использующих таблицы-справочники.
2) Предусмотреть следующие роли:
а) оператор БД (пополнение справочников)
б) пользователь БД (основная работа с БД, с ограничениями для некоторого вида операций)
в) аналитик (разрешено выполнение запросов и функций, не изменяющих данные в БД)
г) администратор БД (просмотр протокола операций, любые изменения БД)
3) Действия, изменяющие БД пользователем с любой ролью протоколируются в таблице-журнале операций.
4) Для всех запросов необходимо создать индексы (для гарантированного использования индексов можно использовать отключение параметра enable_seqscan в текущей сессии)
Создать функции, реализующие интерфейс для работы с базой данных. Проверить работоспособность функций путем выполнения этих функций с параметрами, обеспечивающими как успешное выполнение функции, так и невыполнение функции.
Вариант 1: База данных хоккейной лиги. Должна содержать следующие данные: составы команд и информацию о каждом игроке, проведенные игры с информацией о проданных билетах и затраченных средствах. Предусмотреть анализ следующих показателей: популярность команд, рейтинг и эффективность игроков за указанный период.
Вариант 2: База данных сети магазинов продуктов питания. Должна содержать следующие данные: информация о магазинах и имеющихся запасах продуктов, данные о продажах. Предусмотреть анализ следующих показателей: рейтинг популярности товаров, сумма среднего чека по магазину в разное время суток.
Вариант 3: База данных транспортной компании. Должна содержать следующие данные: информацию о водителях, поставщиках товаров и потребителях, завершенные и незавершенные доставки. Предусмотреть анализ следующих показателей: среднее время простоя водителя, соотношение доходности междугородних и местных доставок.
Вариант 4: База данных сети книжных магазинов. Должна содержать следующие данные: текущие складские запасы печатной продукции, информацию о заказах и продажах. Предусмотреть анализ следующих показателей: наиболее часто заказываемые книги, средний чек по разным группам товаров.
Вариант 5: База данных сети автосалонов. Должна содержать следующие данные: информацию об автосалонах, продавцах-консультантах, имеющихся в наличии и проданных автомобилях. Предусмотреть анализ следующих показателей: рейтинг продаж для продавцов-консультантов по различным моделям, рекомендации к заказу моделей на основании имеющегося запаса и популярности модели.
Вариант 6: База данных складского комплекса. Должна содержать следующие данные: данные о товарах, данные о складах, имеющихся товарах и выполненных и невыполненных заявках на них. Предусмотреть анализ следующих показателей: рейтинг востребованности товаров, рейтинг дефицитности товаров.
Вариант 7: База данных библиотеки. Должна содержать следующие данные: информацию об имеющихся изданиях, информацию о читателях, формуляры для каждого издания. Предусмотреть анализ следующих показателей: выдать рекомендации для читателя с учетом его пола, возраста и прочитанных книг на основании общей статистики.
Вариант 8: База данных жилищной управляющей компании. Должна содержать следующие данные: информацию об исполнителях работ и выполненных работах, жильцах, выставленных им счетах и выполненных ими платежах. Предусмотреть анализ следующих показателей: составить рейтинг злостных неплательщиков, рейтинг исполнителей работ с указанием их доли в статье расходов.
Вариант 9: База данных гарантийного ремонта. Должна содержать следующие данные: информацию о выпускаемых производителем товарах, гарантийных мастерских в разных городах и товарах, ремонт которых они могут производить, данные о выполненных ремонтах. Предусмотреть анализ следующих показателей: рейтинг убыточности товаров, обеспеченность каждого города мастерскими по разным группам товаров и всему ассортименту в целом.
Содержание отчета
Отчет должен содержать титульный лист, цель работы, задание, коды команд на каждом этапе выполнения работы, результаты выполнения команд (скриншоты), выводы и анализ результатов работы.
Контрольные вопросы
1. Что такое агрегатные функции?
2. Как выполнить пользовательскую функцию?
3. Синтаксис команды создания функции.
4. Как в теле функции связать переменную со значением входного параметра?
5. Синтаксис языка PL/pgSQL.
6. Возможно ли использование в теле функции запросов?
7. Как организовать условие в теле функции?
8. Как создать цикл в теле функции?
9. Используются ли ограничения, наложенные на таблицу, при добавлении в нее записей через созданную функцию?
10. Можно ли использовать созданные функции за пределами базы данных, в которой они созданы? Почему?