Рабочий лист: Практическое занятие №8: поиск по одному и нескольким полям

Дисциплина «ОП.08 Основы проектирования баз данных» — Занятие №20 (СИЭУиП, завершение 3 семестра)
Выберите вариант практического задания:

Вариант 1: Поисковая система клиентской базы банка

Предметная область: Автоматизированная банковская система (АБС). Таблица bank_clients содержит 10 000 000 записей.

CREATE TABLE bank_clients (
    client_id BIGSERIAL PRIMARY KEY,
    contract_number VARCHAR(30) NOT NULL UNIQUE,
    full_name VARCHAR(200) NOT NULL,
    region VARCHAR(80) NOT NULL,
    status VARCHAR(20) NOT NULL,
    monthly_income NUMERIC(12,2) NOT NULL,
    created_at DATE NOT NULL DEFAULT CURRENT_DATE
);

Задание 1.1. Точечный поиск по номеру договора и маске ФИО (2 балла)

  1. Напишите SQL-запрос точечного поиска клиента по точному номеру договора contract_number = 'DOG-2024-98412'. Какой индекс обслуживает этот запрос?
  2. Напишите SQL-запрос поиска клиентов по префиксной маске ФИО 'Иван%'. Укажите DDL-команду создания индекса с учетом локали UTF-8.

Задание 1.2. Комбинированный поиск: Регион + Статус + Доход (1 балл)

Составьте запрос фильтрации: Регион = 'ХМАО-Югра' AND Статус = 'ACTIVE' AND Диапазон доходов от 80 000 до 150 000 руб. Спроектируйте составной индекс, строго соблюдая правило «Равенства вперед, диапазоны в конец».

Задание 1.3. Поисковый протокол и фиксация времени отклика (1 балл)

Заполните поисковый протокол по результатам анализа планов выполнения (EXPLAIN ANALYZE):

Сценарий Предикат WHERE Используемый индекс Узел EXPLAIN Время отклика (мс)
Точный договор contract_number = '...'
Маска ФИО full_name LIKE 'Иван%'
Комбинированный region=... AND status=... AND income...

Самопроверка

Почему запрос WHERE full_name LIKE '%Иван%' приведет к деградации до Sequential Scan, даже если индекс по full_name создан?

Вариант 2: Каталог научной библиотеки

Предметная область: Электронная библиотечная система. Таблица books содержит 3 000 000 наименований.

CREATE TABLE books (
    book_id BIGSERIAL PRIMARY KEY,
    isbn VARCHAR(20) NOT NULL UNIQUE,
    author_name VARCHAR(150) NOT NULL,
    title VARCHAR(250) NOT NULL,
    annotation TEXT,
    publication_year INT NOT NULL,
    language_code CHAR(2) NOT NULL DEFAULT 'RU'
);

Задание 2.1. Точечный поиск по ISBN и поиск по автору (2 балла)

  1. Напишите SQL-запрос поиска книги по международному стандартному номеру isbn = '978-5-97060-845-6'.
  2. Напишите SQL-запрос поиска всех трудов автора author_name = 'Дейт К. Дж.'. Какой индекс необходим для устранения Seq Scan?

Задание 2.2. Полнотекстовый поиск по названию и аннотации (1 балл)

Пользователи ищут учебники по фразам «реляционные базы» или «нормализация отношений». Спроектируйте функциональный инвертированный индекс GIN с использованием функций to_tsvector и напишите запрос с оператором @@ и to_tsquery.

Задание 2.3. Поисковый протокол и фиксация времени отклика (1 балл)

Сценарий Предикат WHERE Используемый индекс Узел EXPLAIN Время отклика (мс)
Поиск по ISBN isbn = '...'
Поиск по автору author_name = '...'
Полнотекстовый FTS to_tsvector(...) @@ to_tsquery(...)

Самопроверка

Какую роль в полнотекстовом поиске играет стемминг (stemming) и почему поиск через GIN эффективнее LIKE по тексту аннотации?

Вариант 3: Реестр медицинских карт

Предметная область: Единая медицинская информационная система (ЕМИС). Таблица medical_records содержит 8 000 000 визитов.

CREATE TABLE medical_records (
    record_id BIGSERIAL PRIMARY KEY,
    policy_number CHAR(16) NOT NULL,
    patient_name VARCHAR(150) NOT NULL,
    department_code VARCHAR(20) NOT NULL,
    visit_date TIMESTAMP NOT NULL,
    diagnosis_code VARCHAR(10) NOT NULL, -- МКБ-10
    doctor_id INT NOT NULL
);

Задание 3.1. Точечный поиск по полису ОМС и диапазон дат визитов (2 балла)

  1. Напишите SQL-запрос поиска по 16-значному номеру полиса ОМС.
  2. Напишите SQL-запрос выборки истории приемов конкретного пациента за первое полугодие 2024 года с сортировкой от новых к старым (Index Range Scan).

Задание 3.2. Комбинированный поиск по отделению и диагнозу (1 балл)

Главврачу требуется статистика: найти все записи кардиологического отделения (department_code = 'CARDIOLOGY') с диагнозом 'I20.0' за текущий месяц. Спроектируйте оптимальный составной B-Tree индекс.

Задание 3.3. Поисковый протокол и фиксация времени отклика (1 балл)

Сценарий Предикат WHERE Используемый индекс Узел EXPLAIN Время отклика (мс)
Полис ОМС policy_number = '...'
Диапазон дат policy = ... AND visit_date BETWEEN ...
Отделение + Диагноз dept = ... AND diagnosis = ...

Самопроверка

Почему для диапазона дат в связке с полисом ОМС индекс (policy_number, visit_date DESC) эффективнее, чем (visit_date, policy_number)?

Вариант 4: Служба поддержки пользователей IT-сервиса (Service Desk)

Предметная область: Система обработки заявок технической поддержки. Таблица support_tickets содержит 4 000 000 заявок.

CREATE TABLE support_tickets (
    ticket_id BIGSERIAL PRIMARY KEY,
    ticket_uuid UUID NOT NULL UNIQUE,
    subject VARCHAR(200) NOT NULL,
    priority VARCHAR(15) NOT NULL, -- 'LOW', 'MEDIUM', 'HIGH', 'CRITICAL'
    status VARCHAR(20) NOT NULL,   -- 'OPEN', 'IN_PROGRESS', 'RESOLVED', 'CLOSED'
    assigned_engineer_id INT,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    description TEXT
);

Задание 4.1. Точечный поиск тикета по ID и префикс темы (2 балла)

  1. Напишите SQL-запрос поиска обращения по уникальному числовому ticket_id = 459102.
  2. Напишите SQL-запрос поиска всех тикетов, тема которых начинается со слова 'VPN%'. Спроектируйте индекс под префикс.

Задание 4.2. Фильтрация очереди инженера по приоритету и статусу (1 балл)

Инженеру с assigned_engineer_id = 42 необходимо вывести активные критические заявки: status = 'IN_PROGRESS' AND priority IN ('HIGH', 'CRITICAL') с сортировкой по времени создания (свежие первыми). Спроектируйте составной индекс.

Задание 4.3. Поисковый протокол и фиксация времени отклика (1 балл)

Сценарий Предикат WHERE Используемый индекс Узел EXPLAIN Время отклика (мс)
Поиск по ID ticket_id = 459102
Префикс темы subject LIKE 'VPN%'
Очередь инженера engineer = ... AND status = ... AND priority IN (...)

Самопроверка

В чем преимущество использования составного индекса перед двумя отдельными индексами по колонкам assigned_engineer_id и status при высокой интенсивности запросов?

Данные сохранены локально