Вариант 1: Биллинговая система расчетно-кассового центра ЖКХ
Предметная область: Автоматизация расчетов коммунальных платежей многоквартирных домов. Схема хранит данные абонентов (лицевые счета), индивидуальных приборов учета (счетчиков) и ежемесячных начислений. Требуется спроектировать изолированную схему billing, последовательность для генерации номеров платежных квитанций и материализованное представление для ежемесячного отчета с агрегацией дебиторской задолженности.
Задание 1.1. Проектирование схемы и генератора квитанций (SEQUENCE)
Составьте DDL-скрипт создания схемы billing и последовательности receipt_num_seq (старт с 100000, шаг 1, минимальное значение 100000, кэш 20 значений). Напишите SQL создания таблицы квитанций billing.receipts с автоматическим вызовом nextval() по умолчанию:
Задание 1.2. Проектирование материализованного представления (MATERIALIZED VIEW)
Напишите DDL создания billing.mv_district_debt_summary, агрегирующего по районам (district_id) и месяцам: количество абонентов, сумму начислений, сумму оплат и суммарную задолженность. Опишите создание обязательного уникального индекса и команду регламентного обновления REFRESH ... CONCURRENTLY:
Задание 1.3. Инженерный анализ и обоснование архитектурных решений
Объясните: 1) Почему для сводки долгов выбрано именно MATERIALIZED VIEW, а не динамическое VIEW? 2) Допустимы ли пропуски номеров в receipt_num_seq при отмене платежных транзакций (ROLLBACK) и почему?
Вариант 2: Медицинская радиологическая система архивации снимков (PACS)
Предметная область: Централизованный архив лучевой диагностики клиники. Хранит учет пациентов, цифровые DICOM-исследования и протоколы врачебных заключений. Требуется спроектировать изолированную схему imaging, перечислимый тип ENUM для модальностей томографов и безопасное динамическое представление VIEW со строгим сокрытием персональных данных (ФИО, паспорт) для внешних исследовательских систем.
Задание 2.1. Проектирование схемы и перечислимого типа модальностей (ENUM)
Составьте DDL создания схемы imaging и типа modality_type со значениями: 'CT' (компьютерная томография), 'MRI' (магнитно-резонансная), 'XRAY' (рентген), 'ULTRASOUND' (УЗИ), 'MAMMOGRAPHY'. Создайте таблицу imaging.dicom_studies с использованием этого типа:
Задание 2.2. Проектирование защищенного представления (VIEW) для исследователей
Составьте DDL представления imaging.v_anonymized_studies, объединяющего данные пациентов и снимков. Фамилия, имя и персональные коды должны быть замаскированы (или заменены хешем/суррогатным кодом субъекта), оставляя доступными для научной аналитики только пол, возрастную группу, модальность и заключение врача:
Задание 2.3. Инженерный анализ и управление привилегиями (DCL)
Опишите: 1) Почему для сокрытия ПДн используется динамическое VIEW, а не материализованное? 2) Напишите команды GRANT / REVOKE для роли ai_researcher, запрещающие прямой доступ к базовым таблицам и открывающие доступ только к представлению:
Вариант 3: Информационная система службы экспресс-доставки посылок
Предметная область: Логистический оператор федерального масштаба. Обрабатывает миллионы отправлений, перемещения между сортировочными складами и маршрутные листы курьеров. Требуется спроектировать схему logistics, генератор трек-номеров через SEQUENCE с префиксом и партиционированную архитектуру таблицы истории трекинга.
Задание 3.1. Генератор трек-номеров через SEQUENCE и вспомогательную функцию/триггер
Составьте DDL создания схемы logistics, последовательности tracking_seq (от 10000000 до 99999999, кэш 50). Напишите DDL таблицы logistics.parcels, где трек-номер формируется с префиксом (например, 'RU' || nextval('logistics.tracking_seq') || 'CN'):
Задание 3.2. Архитектура таблицы трекинга и материализованная витрина складов
Опишите партиционированную по месяцам таблицу контрольных точек logistics.tracking_events. Составьте DDL материализованного представления logistics.mv_warehouse_daily_load с расчетом суточной загрузки складов (принято/отправлено посылок) и уникальным индексом для обновления:
Задание 3.3. Анализ производительности и преимуществ партиционирования
Объясните: 1) Зачем таблицу трекинга событий разделять на партиции? 2) Почему генерация трек-номеров через SEQUENCE масштабируется лучше, чем вычисление через строковую конкатенацию в приложении?
Вариант 4: Информационная система электронного деканата университета
Предметная область: Автоматизация учебного процесса вуза/колледжа. Хранение личных дел студентов, учебных планов кафедр и экзаменационных ведомостей. Требуется спроектировать разделение схем academic (учебный процесс) и personal (паспортные и финансовые данные студентов) и защищенные представления ведомостей для преподавателей.
Задание 4.1. Многосхемная архитектура: разграничение personal и academic
Напишите DDL создания схем personal и academic. Опишите таблицы personal.students (ФИО, паспорт, СНИЛС, телефон) и academic.student_registry (номер зачетки, код специальности, статус обучения). Свяжите их внешним ключом:
Задание 4.2. Проектирование защищенного представления экзаменационной ведомости
Составьте DDL представления academic.v_exam_grade_sheet, объединяющего данные студентов и ведомостей. Представление должно выводить только номер зачетной книжки, краткое имя (Иванов И.И.), дисциплину и итоговую оценку, блокируя доступ к персональным данным:
Задание 4.3. Настройка безопасности ролей и архитектурное обоснование
Опишите: 1) Какие команды DCL (GRANT/REVOKE) нужно выдать роли teacher_role, чтобы преподаватель не имел доступа к паспортам схемы personal? 2) В каких случаях для деканата потребуется создать MATERIALIZED VIEW?