Проектирование БД (зачетное задание)

Модель создаётся в MS SQL Server 2022. Для каждой темы достаточно 5-7 взаимосвязанных таблиц с первичными и внешними ключами и обязательными CHECK, UNIQUE, DEFAULT- ограничениями. Базу нужно наполнить демонстрационные данными (≈ 10-20 строк в таблице). В результате работы сохранить dump файл с полученной системой + концептуальная и физическая модель.

Прим. Номер варианта вычисляется как «(Номер зачетки + 4) mod 10 + 1».

Условия зачета: 5-7 хорошо нормализованных таблиц, корректных ограничений ссылочной и предметной целостности, качественных демонстрационных данных и 7-8 правильно выполненных запросов.

Вариант 1 — Школьная лига по киберспорту

Лига объединяет команды общеобразовательных школ, соревнующиеся в двух популярных дисциплинах (например, CS и Dota 2). Для каждой школы хранится уникальный набор игроков с паспортными данными, учебным классом и никнеймами в игре; для дисциплин — карты, регламент раунда и возрастные ограничения. Расписание турнира состоит из группового этапа и плей-офф: база фиксирует дату, время, сервер, счёт раундов и продолжительность каждого матча. Параллельно ведётся учёт зрительской аудитории: система регистрирует пиковое и среднее онлайн-число зрителей на платформах YouTube и Twitch, а также продажи билетов на оффлайн-финал (партер, фан-зона, VIP-ложи). Призовой фонд формируется из спонсорских взносов компаний-партнёров и распределяется по этапам. Для аналитики хранения статистики предусмотрены показатели KDA каждого участника и история переходов игроков между командами.

Контрольные запросы

  1. календарь игр выбранной дисциплины на текущую неделю;
  2. турнирная таблица группы «А» c разницей раундов;
  3. команды без поражений после трёх туров;
  4. игроки, сменившие команду в этом сезоне;
  5. топ-5 матчей по пиковым зрителям;
  6. распределение призовых между тремя лучшими командами;
  7. продажи билетов в фан-зону по дням;
  8. суммарный онлайн-watch-time спонсора X;
  9. игроки с KDA > 3, сыгравшие минимум два матча;
  10. сформированная сетка плей-офф на основе итогов групп.

Вариант 2 — Платформа онлайн-курсов

Платформа размещает короткие и длительные курсы: каждый курс состоит из модулей, модули — из уроков-видео. Для урока фиксируются длительность, язык, дата обновления и рекомендуемая литература; для модуля — минимальный проходной балл теста. Пользовательская часть хранит слушателей с учётными записями, их платежи (стоимость, метод, дата), попытки прохождения контрольных и результаты тестов. При успешном закрытии курса платформа генерирует цифровой сертификат с уникальным номером и временем выдачи. Система рейтингов позволяет оставлять отзыв и числовую оценку как курсу в целом, так и каждому уроку. Отдельно учёт идёт по преподавателям: для них сохраняется портфель курсов, договорная ставка и налоги, а также средний рейтинг по их контенту. Для финансовой аналитики хранится помесячная выручка, источники трафика и статус возврата средств в течение гарантийного периода.

Контрольные запросы

  1. список слушателей курса «SQL Basics» и их прогресс;
  2. среднее время прохождения модуля № 2;
  3. курсы, где завершение < 60 % записавшихся;
  4. пять самых высоко оценённых уроков;
  5. лекции, просмотренные > 1000 раз;
  6. выручка по месяцам за текущий год;
  7. преподаватели, у которых более трёх активных курсов;
  8. слушатели, получившие сразу два сертификата в один день;
  9. рейтинг курсов по соотношению «оценка / длительность»;
  10. критические отзывы (оценка ≤ 2) за последние 30 дней.

Вариант 3 — Городской велопрокат

Сервис охватывает сеть станций по всему городу. Для каждой станции фиксируются координаты, вместимость и статус (активна, на ремонте). Велопарк делится на механические и электрические модели с разной стартовой ценой и тарифом за минуту; для электробайков дополнительно хранится остаточный заряд и дата последней замены аккумулятора. Пользователь профилируется по ID приложения, возрасту и способу оплаты; указывается наличие годового абонемента. Каждая поездка содержит время начала и окончания, начальную и конечную станцию, протяжённость и списанную сумму. Журнал технического обслуживания отражает дату последнего ТО, пробег, обнаруженные неисправности и затраты на ремонт. Для анализа популярности тарифов сохраняется справочник тарифных планов (разовый, дневной, месячный) и счётчик активированных планов по датам.

Контрольные запросы

  1. свободные велосипеды на станции «Парк Горького»;
  2. пользователи, совершившие > 15 поездок за месяц;
  3. средняя длительность аренды по дням недели;
  4. станции с наибольшим числом возвратов вечером (18-22);
  5. велосипеды, проехавшие > 500 км со дня последнего ТО;
  6. динамика общего пробега парка помесячно;
  7. коэффициент оборачиваемости для каждой станции;
  8. популярность тарифов за квартал;
  9. топ-10 самых длинных поездок;
  10. список пользователей, у которых было два и более штрафа.

Вариант 4 — Городская библиотека

Каталог библиотеки содержит книги, разделённые по жанрам и возрастным категориям, с указанием автора, издательства, года издания и уникального ISBN. Для каждой книги хранится несколько экземпляров с инвентарным номером, текущим местоположением (зал, на руках, ремонт) и состоянием (отличное, хорошее, ветхое). Читательская карточка фиксирует паспортные данные, контактную информацию и историю всех визитов. Операции выдачи и возврата записываются с датой, сроком возврата и автоматически рассчитываемым штрафом при просрочке. Культурно-просветительский блок описывает мероприятия — презентации, клубы по интересам, встречи авторов — c датой, организатором, списком зарегистрированных читателей и максимальной вместимостью зала. Дополнительно ведётся журнал ремонтных работ экземпляров: дата отправки, предполагаемая дата возврата и стоимость ремонта.

Контрольные запросы

  1. книги без свободных экземпляров;
  2. читатели с долгом > 300 ₽;
  3. выдачи за последний месяц по жанрам;
  4. мероприятия на ближайшие 14 дней и число регистраций;
  5. экземпляры в ремонте дольше двух недель;
  6. самые читаемые авторы года;
  7. средняя задержка возврата по жанрам;
  8. читатели, не посещавшие библиотеку > 90 дней;
  9. книги, выданные > 50 раз с начала года;
  10. динамика штрафных оплат помесячно.

Вариант 5 — Сеть кофеен

Каждое заведение хранит общий справочник рецептур: ингредиенты (зёрна, молоко, сиропы) с нормой на порцию, калорийностью и себестоимостью. Заказы делятся на «зал» и «take-away»; для каждого заказа фиксируются позиции меню, время от принятия до выдачи и способ оплаты. Программа лояльности хранит кошелёк бонусов, дату последней транзакции и сегмент клиента (новый, регуляр, VIP). Инвентаризация ведётся по остаткам ингредиентов на начало смены, поступлению и списанию; суточная норма выводится из суммарных продаж. Рабочие смены бариста содержат дату, длительность, количество приготовленных напитков и итоговый средний рейтинг по чек-беку пользователя. Для оценки эффективности маркетинга базируется справочник акций, период действия и правила (скидка %, 2 + 1, подарок).

Контрольные запросы

  1. хиты продаж недели (топ-5 позиций);
  2. средний чек по типу обслуживания;
  3. ингредиенты с остатком ниже дневной нормы;
  4. бариста с наивысшим средним рейтингом;
  5. 95-й перцентиль времени ожидания заказа;
  6. выручка каждой кофейни за вчера;
  7. клиенты, сделавшие ≥ 3 покупок за 7 дней;
  8. продажи акционных позиций и их доля;
  9. корреляция «рейтинг точки ↔ средний чек»;
  10. динамика расхода молока по неделям.

Вариант 6 — Мини-отель

Отель насчитывает до 25 номеров различных категорий (эконом, стандарт, улучшенный, люкс). Для номера хранятся площадь, количество спальных мест и цена за ночь, вариативная по сезону. Бронирование указывает дату заезда и выезда, канал брони (сайт, агрегатор, телефон), статус оплаты и флаг «гарантированное». В карточке гостя сохраняются личные данные, предпочтения (тип подушки, этаж), а также история прошлых проживаний и оценок. Каталог дополнительных услуг включает завтрак-шведский стол, поздний выезд, трансфер в аэропорт; каждая услуга привязывается к бронированию отдельной записью. Модуль «Хозяйственная служба» фиксирует статусы уборки, даты и стоимость ремонтных работ. Выручка агрегируется по дням: проживание, услуги, штрафы за незаезд.

Контрольные запросы

  1. свободные номера на произвольный диапазон дат;
  2. загрузка по категориям за месяц;
  3. гости с более чем двумя повторными визитами;
  4. средняя длительность пребывания;
  5. доход от дополнительных услуг;
  6. бронирования, отменённые < 24 ч до заезда;
  7. номера, находившиеся на ремонте > 5 дней;
  8. динамика выручки по каналам;
  9. коэффициент no-show по месяцам;
  10. средняя цена номера в разрезе сезонов.

Вариант 7 — Учебное расписание университета

Учебный модуль хранит список дисциплин с трудоёмкостью, семестр и формой контроля. Преподавательский справочник включает учёную степень, должность и норму часов в нагрузке. Каждая аудитория имеет вместимость, тип (лекционная, лаборатория, компьютерный класс) и оборудование (проектор, системы видеоконференций). Для занятия фиксируются группа, дисциплина, преподаватель, аудитория, дата, пара и формат (очно, онлайн). Посещаемость хранится отдельной таблицей с флагами «присутствовал», «опоздал», «уважительная причина». Для ведомости оценок указывается семестр, дисциплина, студент, балл, дата сдачи и номер перепроверки. Справочник групп содержит факультет, направление, год набора и старосту.

Контрольные запросы

  1. расписание группы ИВТ-21-1 на неделю;
  2. аудитории, занятые > 80 % времени;
  3. преподаватели, имеющие «окно» > 2 ч между парами;
  4. пересечения бронирований одной аудитории;
  5. средний балл по дисциплине «Базы данных»;
  6. список студентов-отличников факультета;
  7. нагрузка преподавателей в часах;
  8. дисциплины без назначенной аудитории;
  9. посещаемость занятий по датам;
  10. доля очных занятий, заменённых на онлайн.

Вариант 8 — Автосервис

Сервис обслуживает легковые автомобили: для каждой машины хранится VIN, марка, модель, год выпуска и пробег. Заказ-наряд включает работы (диагностика, замена масла и т.п.), стоимость запчастей, зарплату мастера и итоговую сумму счета. Каталог работ содержит нормативное время и тариф; каталог запчастей — поставщика, складской остаток, минимальный запас и цену закупки. Расписание постов состоит из даты, мастера, поста и статуса «свободен/занят». Лабораторный блок хранит дефек­товку деталей: выявленные неисправности, фотографию повреждений (путь к файлу) и заключение инженера. Для повторных обращений фиксируется ссылка на предыдущий заказ-наряд и причина возврата клиента.

Контрольные запросы

  1. автомобили, ожидающие выдачи > 2 дней после готовности;
  2. топ-5 самых частых услуг;
  3. мастера с выработкой > 160 ч за месяц;
  4. запас запчастей ниже минимального;
  5. средняя стоимость ремонта по моделям;
  6. заказы, где срок ремонта превысил плановый;
  7. доход автосервиса по неделям;
  8. заказы с повторным обращением по той же проблеме;
  9. модели, требующие больше всего времени на обслуживание;
  10. коэффициент использования постов в дневную смену.

Вариант 9 — Студия групповых тренировок

Расписание студии формируется из программ (йога, HIIT, пилатес), каждая из которых имеет минимальное и максимальное количество участников, длительность и уровень сложности. Для тренера хранится квалификация, специализация и средний рейтинг по отзывам. Абонементы делятся на типы (4/8/12 занятий, безлимит) с разной ценой и сроком действия; система продаж фиксирует дату покупки, канал (касса, онлайн), использованные бонусы. Таблица визитов отражает факт присутствия, опоздание, заморозку абонемента и списание занятия. Бар-магазин хранит ассортимент мерча и напитков; продажи барной зоны поддерживаются отдельными записями, но могут быть связаны с клиентом. Для анализа загрузки залов указываются интервалы суток и вместимость.

Контрольные запросы

  1. посещаемость занятия «HIIT 18:00» за месяц;
  2. клиенты без визитов > 30 дней;
  3. тренеры с средним рейтингом ≥ 4,8;
  4. загруженность залов по часам;
  5. выручка от абонементов и разовых посещений;
  6. занятия, отменённые по недоуплотнению (< 3 записей);
  7. среднее заполнение групп по программам;
  8. продажи мерча и воды в баре;
  9. топ-10 самых лояльных клиентов;
  10. динамика продаж абонементов перед Новым годом.

Вариант 10 — Центр помощи бездомным животным

Для каждого животного хранятся кличка, вид, порода, дата поступления, чип-ID и текущий статус (в приюте, на домашнем передержке, усыновлено). Медицинская карта содержит диагоноз, прививки и назначения ветеринара с датой следующего осмотра. Профиль волонтёра фиксирует контакты, доступные смены и общее отработанное время. Заявка на усыновление связывает потенциального хозяина, выбранное животное, этапы одобрения (звонок, визит, проверка условий), дату финальной передачи и возможный возврат. Модуль фонда отражает пожертвования по каналам, целевые программы (корма, стерилизация) и фактическое расходование средств. Складской раздел хранит остатки кормов и лекарств с датой окончания годности.

Контрольные запросы

  1. животные, находящиеся в центре > 90 дней;
  2. среднее время пристройства по породам;
  3. поступления животных за месяц по причинам;
  4. активные заявки на усыновление и их статус;
  5. волонтёры с > 20 ч помощи за месяц;
  6. пожертвования по каналам;
  7. животные, требующие повторного мед-осмотра;
  8. доля усыновлений, завершившихся возвратом;
  9. потребность в кормах и лекарствах на неделю;
  10. динамика поступлений и пристройств за год.