Учебные задачи по PL/SQL

Ссылки сюда (1) →

Логическая схема

База данных эмулирует географически распределенную торговую компанию, торгующую с центрального склада. Каждое низовое подразделение отвечает за определенный регион (город), где набирает клиентов и организует продажи. Ассортимент товаров, цены и закупки осуществляются централизованно. Часть схемы, относящаяся к закупкам (и остаткам) пока не наполнена данными.

Особенности отдельных сущностей.

• В интервалах цен Дата конца - всегда не входит.
• Подразделения образуют иерархию. Для отчетов (запросов) будет использоваться выборка по иерархии. Однако название однозначно идентифицирует подразделение и его достаточно при показе информации.
• Для именования клиентов и поставщиков применяется два атрибута: Название (moniker), которое должно быть уникальным и Наименование (name), которое соотвествует официальным документам и используется в печатных формах.
• Каталог товаров также иерархический. В отличие от подразделений, на каждом уровне ветки именуются коротким кодом, а для отражения в плоских отчетах и интерфейсах применяется иерархический код.
• На строку продажи действуют две скидки: указанная в продаже и дополнительная, назначенная индивидуально для строки.
• Стоимость товара в каждой строке, естественно, получается умножением цены на количество, а для продаж - дополнительно с применением скидки. Теоретически его можно не хранить, либо можно не хранить цену, однако и то и другое часто нужно в разных отчетах, и поэтому оба атрибута обычно хранимые. Кроме того, необходима воспроизводимость при печати документов, не смотря на возможные проблемы окрушгления, что тоже является аргументом за хранение всех атрибутов, а также НДС.
• Общая сумма продажи и НДС продажи, естественно, равна сумме по всем позициям. Однако из тех же соображений удобства отчетов она обычно является хранимой.
• Поставки и Продажи имеют два состояния: Черновик и Исполнена. При исполнении документа изменяются остатки на складе. Пока документ - черновик, его можно свободно изменять. А исполненные документы править нельзя. Естественно, в реальных системах состояний больше, но для модельных целей этого достаточно.

Задачи для запроса данных

• Найти товары, цены на которые отличаются от цены на модель.
• Найти продаже, цены без скидки в которых отличаются от цены на товар.
• Проверить гипотезу, что там, где цены продажи отличаются от цены каталога цены в каталоге просто были испорчены. Для этого надо для каждой модели, для которых есть продажи с ценами, отличающимися от цен на товар, посмотреть продажи с совпадающими и несовпадающими ценами.
• Вывод каталога с отступами в соответствии с уровнями каталога. Для каждого узла - число нижележащих узлов.
• Вывод таблицы подразделений с отступами в соответствии с уровнями каталога
• Вывод каталога с отступами, для листа с товарами - число моделей и товаров в ним.
• Вывод каталога с отступами, для каждого узла - число моделей и товаров в нем и нижележащих узлах
• Вычислить число продаж, их общую сумму, средневзвешенную, максимальную и минимальную скидки по каждому клиенту, у которых были продажи со скидкой более, чем 25%. Все - без учета скидок на строки
• Вычислить число строк продаж, количество, сумму средневзвешенную, максимальную и минимальную скидку по торговой марке (t_model.label).
• Выдать отчет о продажах по подразделениям, для каждого подразделения в дереве в отчет включать подчиненные.В отчете - число продаж, число строк, количество, общая сумма. Указание. Для начала сделать запрос, который бы для каждого подразделения выводил все подчиненные.
• Выявить наиболее характерные значения скидок. Для этого построить распределение по предоставляемым скидкам, для каждой определить количество позиций, товаров и моделей, колдичество и сумму продаж.
• Найти модели, на которые предоставлены нехарактерно большие скидки, с учетом предыдущего анализа. Сделать предположение о с составе этих моделей

Пока не готовы данные

• Вывод таблицы подразделений, для каждого узла - стоимость товаров на складе.
• Стандратная оборотка по товару в штуках за период: остаток на начало, приход, уход, остаток на конец. Различные группировки, выбор наилучших.
• Отчет по прибылям от продажи - в разрезе товаров и подразделений.
• Описанные выше отчеты построить по узлам каталога и по подразделениям с иерархическим включением по принадлежности.

Задачи для программирования

• Триггер, блокирующий продажу товара себе в убыток, то есть по цене, меньшей чем имеющаяся сейчас у товара на складе.
• Триггер, блокирующий установку скидки более 20% для не vip клиентов.
• Триггер, автоматически устанавливающий цену продажи товара по значению цены на дату продажи с учетом скидки.
• Триггер для строки поставки, автоматически вычисляющий сумму по строкам при изменении цены или количества.
• Триггер для строки продажи и для самой продажи, автоматически вычисляющий сумму по строкам при изменении цены или количества, а также скидки в заголовке, и изменяющие общую сумму продажи в заголовке. Задача сложная из-за mutating по простой схеме, когда изменение скидки меняет строки, а изменение суммы строки - меняет сумму в заголовке. Как альтернатива: для изменения скидки используется процедура, но тогда нужно придумать механизм блокировки прямого изменения этого поля в таблице.
• Процедура назначения цены товару на заданном интервале, соответственно изменяющая таблицу цен. Должна изменять также цены в продажах-черновиках, естественно, с учетом скидки.
• Набор процедур для элементарных операций над узлами каталога, обеспечивающих вычисление иерархического кода: вставка, перемещение в иерархии, переименование.
• Альтернатива предыдущему: триггера, обеспечивающие вычисление иерархического кода для узлов каталога. Это сложнее, чем набор процедур.
• Набор процедур элементарных действий или триггера, контролирующие отсутствие циклов в иерархии подразделений.
• Процедура или триггер исполнения поставки, изменяющая количество и стоимость товара на складе.
• Процедура или триггер исполнения продажи, изменяющая количество и стоимость товара на складе. Необходимо блокировать продаже отсутствующего товара, но при этом обязательно выдавать сообщение по всем товарам, которых не хватает, а не останавливаться на первом.

---

Репликация: База Знаний «Заказных Информ Систем» → «Учебные задачи по PL/SQL»

Любые правки этой статьи будут перезаписаны при следующем сеансе репликации. Если у вас есть серьезное замечание по тексту статьи, запишите его в раздел «discussion».