Задача проектирования (кратко)
- Построить нормализованную реляционную схему для системы управления университетом, покрывающую: студенты, преподаватели, кафедры, курсы, предложения курсов (секции), расписание (аудитории/времена), зачисления, оценки, преподы-адвайзеры, предусловия курсов.
- Требования: целостность данных (PK, FK), отсутствие аномалий обновления, поддержка типичных операций: регистрация/отчисление, проставление оценок, расписание/поиск занятий, вычисление GPA, отчёты и агрегаты.
- Поддержать масштабирование чтения (отчёты) и записи (регистрация, выставление оценок).
Нормализованная схема (предлагаемые таблицы, PK/FK)
- $Students(student\_idPK,first\_name,last\_name,dob,email,enrollment\_year,major\_dept\_idFK)$
- $Professors(professor\_idPK,first\_name,last\_name,email,dept\_idFK,title)$
- $Departments(dept\_idPK,name,chair\_professor\_idFK)$
- $Courses(course\_idPK,course\_code,title,credits,dept\_idFK)$
- $Sections(section\_idPK,course\_idFK,term,year,section\_num,professor\_idFK,capacity)$ (уникальность: $(course\_id,term,year,section\_num)$)
- $TimeSlots(timeslot\_idPK,day\_of\_week,start\_time,end\_time)$
- $Rooms(room\_idPK,building,room\_number,capacity)$
- $Schedules(section\_idFK,timeslot\_idFK,room\_idFK,PRIMARYKEY(section\_id,timeslot\_id,room\_id))$ (поддерживает несколько временных слотов)
- $Enrollments(enrollment\_idPK,student\_idFK,section\_idFK,enroll\_date,status)$ (альтернатива: PK = $(student\_id,section\_id)$)
- $Grades(grade\_idPK,enrollment\_idFK,grade,grade\_date,graded\_by\_professor\_idFK)$ (для истории/пересдач)
- $Prerequisites(course\_idFK,prereq\_course\_idFK,PRIMARYKEY(course\_id,prereq\_course\_id))$
- $Advisors(student\_idFK,professor\_idFK,PRIMARYKEY(student\_id,professor\_id))$
Функциональные зависимости и нормальные формы
- Общая идея: все таблицы проектируются так, чтобы нетривиальные функциональные зависимости имели в качестве левой части суперключ — цель: как минимум $\text{3NF}$, где нужно — $\text{BCNF}$.
- Примеры:
- $Students:student\_id\to first\_name,last\_name,dob,email,enrollment\_year,major\_dept\_id$. PK — $student\_id$. Нет частичных или транзитивных зависимостей → таблица в $\text{BCNF}$.
- $Courses:course\_id\to course\_code,title,credits,dept\_id$. PK — $course\_id$. Если существует правило «course\_code уникален в факультете», нужно учесть ограничение уникальности; в типичной модели — таблица в $\text{BCNF}$ либо $\text{3NF}$.
- $Sections:section\_id\to course\_id,term,year,section\_num,professor\_id,capacity$. Если PK = $section\_id$ — таблица в $\text{BCNF}$. Если PK составной $(course\_id,term,year,section\_num)$ — убедиться в отсутствии частичных зависимостей (переместить атрибуты при необходимости).
- $Enrollments:$ если PK = $enrollment\_id$ (или $(student\_id,section\_id)$), то зависимость PK → остальные атрибуты → $\text{3NF}$/$\text{BCNF}$ при отсутствии транзитивных зависимостей.
- $Grades:$ отдельная таблица для истории оценок устраняет повторные значения в Enrollments и даёт $\text{3NF}$.
Рекомендация: базовая схема держать в $\text{3NF}$ или $\text{BCNF}$ (при простых ключах чаще $\text{BCNF}$), чтобы исключить аномалии вставки/обновления/удаления.
Возможные денормализации для повышения производительности и последствия
1) Кешированный/вычисляемый GPA в Students
- Как: добавить колонку $gpa$ в $Students$ и обновлять её при изменении оценок (триггером или фоновой задачей).
- Выгода: быстрые запросы и сортировка по GPA (панели, подбор стипендий).
- Издержки: сложность поддержания согласованности (требуются транзакции/триггеры или периодические перерасчёты), риск рассинхронизации (сталеющие данные), небольшой расход хранения.
- Мягкая альтернатива: материализованный вид с периодическим рефрешем.
2) Дублирование имени преподавателя/названия курса в Sections/Enrollments
- Как: хранить $professor\_name$ в $Sections$ или $course\_title$ в $Sections$.
- Выгода: меньше JOIN-ов при выдаче расписания/списков — ускорение чтения.
- Издержки: при изменении имени/названия нужно обновлять все копии → возможные аномалии обновления; увеличенный размер записей; нужно обеспечивать каскадные обновления или фоновые процессы.
3) Помещение оценки прямо в Enrollments (убрать отдельную таблицу Grades)
- Как: добавить $grade,grade\_date$ в $Enrollments$.
- Выгода: упрощение запросов для текущей оценки, меньше JOIN-ов.
- Издержки: теряется история экзаменов/пересдач; если нужна история, приходится хранить её отдельно или резервировать структуру.
4) Флаттенинг расписания (одна колонка со строкой со всеми timeslot/room)
- Как: поле $schedule\_text$ в $Sections$ со всеми данными расписания (CSV/JSON).
- Выгода: мгновенное чтение и простота отображения.
- Издержки: потеря структурных возможностей (поиск по времени, индексация), нарушение $\text{1NF}$ при массивных полях, сложные обновления отдельных слотов, индексирование по содержимому ограничено (если не использовать специализированные типы/индексы).
5) Материализованные представления/агрегаты (например, число зачисленных в секцию, средний балл по курсу)
- Как: предвычислять и хранить $enrollment\_count$ в Sections или в отдельной таблице-агрегате.
- Выгода: очень быстрые отчёты и панельные метрики.
- Издержки: ресурсы на поддержание (триггеры/планировщик), возможная рассинхронизация; при интенсивных обновлениях — дополнительные блокировки и накладные расходы.
6) Репликация и шардинг
- Как: реплика чтения (read replicas) или шардинг по факультету/году.
- Выгода: масштабирование чтений, разделение нагрузки.
- Издержки: сложность конфигурации, задержка репликации, необходимость маршрутизации запросов; при шардинге сложнее выполнять глобальные транзакции/агрегации.
Последствия денормализаций (общие)
- Плюсы: уменьшение числа JOIN-ов, снижение задержки чтения, ускорение отчётов, простота некоторых запросов.
- Минусы: увеличение места хранения, сложность поддержания согласованности, риск аномалий при обновлении (необходимо проектировать транзакции/триггеры или фоновые процессы), сложнее обеспечение целостности данных и откатов, усложнение логики приложения/миграций.
- Компромисс: проводить точечную денормализацию для горячих путей (hot paths): профилирование, тесты нагрузки, автоматическое поддержание консистентности (триггеры/события) или eventual consistency с явной стратегией обновления.
Рекомендации
- Базовую модель держать в $\text{3NF}$/$\text{BCNF}$.
- Оптимизации: индексы по часто фильтруемым колонкам (student\_id, section\_id, term, year), материализованные представления для тяжёлых агрегатов, read-replicas для отчётов.
- Денормализовать только под обоснованные кейсы на основе метрик (latency, QPS) и сопровождать чёткой стратегией синхронизации (триггеры, транзакции, фоновые перерасчёты).
Если нужно — могу дать компактную диаграмму зависимостей и пример SQL DDL для предложенной схемы.