Домашнее задание - Логическая и физическая независимость данных

Выполняйте только если преподаватель назначил это задание. Срок сдачи сообщит преподаватель.

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

Комплексный инженерный кейс: Рефакторинг информационной системы маркетплейса

В ходе аудита высоконагруженной базы данных маркетплейса отдел архитектуры выявил две критические проблемы:

Проблема на концептуальном уровне: Единая монолитная таблица Products(product_id, title, price, stock, weight, dimensions, supplier_id, warehouse_id) разрослась до 50 атрибутов. Мобильное приложение покупателей запрашивает только витринные данные (title, price, stock), однако внутренняя служба логистики требует нормализации схемы и разделения сущности на две независимые таблицы: Store_Catalog (каталог витрины) и Logistics_Details (логистические параметры: габариты, масса, класс опасности).
Проблема на физическом уровне: Время выполнения запроса поиска товаров по категории и диапазону цен SELECT * FROM Products WHERE category_id = 15 AND price BETWEEN 1000 AND 5000; на массиве в 2 000 000 записей выросло до 1.8 секунды из-за отсутствия индексов и перемешивания актуальных товаров с архивными товарами, снятыми с продажи 5 лет назад.

Задание 1. Обеспечение логической независимости (2 балла)

Разработайте техническое решение, которое позволит разделить монолитную сущность на две таблицы (Store_Catalog и Logistics_Details), сохранив при этом полную работоспособность мобильного приложения покупателей без изменения его исходного кода.

В ответе требуется предоставить:

DDL-скрипт создания экранирующего представления (CREATE VIEW Products AS ...).
Обоснование: почему простого представления недостаточно для сценария, когда система администрирования витрины обновляет цену товара через запрос UPDATE Products SET price = ... WHERE product_id = ...?
Описание логики триггера INSTEAD OF UPDATE для маршрутизации изменений в нужную таблицу.

Задание 2. Обеспечение физической независимости (2 балла)

Предложите комплексную оптимизацию дисковой подсистемы и методов доступа для таблицы Store_Catalog, которая не потребует переписывания ни одного SQL-запроса в приложении покупателей.

В ответе требуется предоставить:

Выбор типа индекса: сравните применимость составного индекса B-Tree (category_id, price) и двух отдельных Hash-индексов для условия фильтрации WHERE category_id = 15 AND price BETWEEN 1000 AND 5000. Поясните, почему Hash-индекс не способен ускорить диапазонный поиск BETWEEN.
Проектирование секционирования (Table Partitioning): опишите, по какому критерию целесообразно секционировать исторический архив товаров (например, по признаку is_active или диапазону дат), и поясните механизм Partition Pruning.
Обоснование физической независимости: почему выполнение команд CREATE INDEX и изменение дискового tablespace не ломают контракт прикладного API?

Требования к оформлению и критерии проверки

Отчет оформляется в текстовом редакторе или в виде рукописного конспекта (с фотокопией высокого качества) и отправляется преподавателю по согласованному каналу связи.
В шапке работы строго указываются: ФИО обучающегося, номер учебной группы и дата выполнения.
Критерии оценки (максимум 4 балла):
- 2 балла — корректное решение Задания 1 (логическая схема, правильный синтаксис VIEW и обоснование триггера INSTEAD OF).
- 2 балла — полное решение Задания 2 (обоснование выбора B-Tree против Hash, объяснение принципа прозрачности индексов и секционирования для клиента).