Персональные инструменты
 

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

Материал из CustisWiki

Версия от 15:18, 23 ноября 2007; BenderBot (обсуждение | вклад) (реплицировано из внутренней CustisWiki)

Это снимок страницы. Он включает старые, но не удалённые версии шаблонов и изображений.
Перейти к: навигация, поиск

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

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

Model db.png

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

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

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

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


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

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



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

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