Проектирование баз данных и нормализация

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

ER-модель: сущности, связи, кардинальность

Проектирование начинается не с таблиц, а с концептуальной модели. Сущность (entity) — то, о чём мы храним данные: ученик, курс, товар. Атрибут — её свойство: имя, цена. Связь (relationship) соединяет сущности: ученик записывается на курс, покупатель заказывает товар. Эту модель в виде диаграмм «сущность–связь» (ER) предложил Питер Чен в 1976 году.

Функциональные зависимости и ключи

Главное понятие нормализации — функциональная зависимость (ФЗ). Запись X → Y означает «значение X однозначно определяет значение Y»: course_id → teacher — зная курс, мы знаем и учителя. ФЗ — не случайное свойство данных, а бизнес-правило: его узнают у специалистов предметной области, а текущие строки таблицы могут его лишь опровергнуть.

Множество атрибутов, определяющее все остальные атрибуты, — суперключ; суперключ, из которого нельзя убрать ни одного атрибута, — потенциальный ключ. Один из потенциальных ключей выбирают первичным. Атрибут, входящий хотя бы в один потенциальный ключ, называют ключевым (prime) — это различие понадобится, чтобы отличать 3НФ от НФБК.

Аномалии: чем опасна избыточность

• Аномалия обновления — один и тот же факт записан в нескольких строках, и при изменении одной копии остальные остаются старыми.
• Аномалия вставки — один факт нельзя записать без другого (добавить курс, на который ещё никто не записан).
• Аномалия удаления — удаление одного факта уничтожает другой (вместе с последним зачислением теряется и учитель курса).

-- a denormalised copy: the teacher is repeated in every row
CREATE TABLE report_flat AS
SELECT e.student_id, s.first_name, e.course_id, c.title, c.teacher, e.score
FROM enrollments AS e
JOIN students AS s ON s.id = e.student_id
JOIN courses  AS c ON c.id = e.course_id;

-- the name is corrected in one row only
UPDATE report_flat SET teacher = 'R. Safarov'
WHERE course_id = 1 AND student_id = 1;

SELECT course_id, teacher, COUNT(*) AS rows_with_it
FROM report_flat
WHERE course_id = 1
GROUP BY course_id, teacher
ORDER BY teacher;

course_id | teacher | rows_with_it
1 | R. Safarov | 1
1 | Ramin Səfərov | 3

Нормальные формы: от 1НФ до НФБК

Декомпозиция должна быть без потерь: соединение частей через JOIN обязано вернуть исходную таблицу без лишних и недостающих строк. Для разбиения на две части это проверяют по теореме Хита: общие столбцы должны быть ключом одной из частей.

Когда денормализация оправдана

Нормализация идеальна для повседневных операций (OLTP): много мелких записей, точность, отсутствие противоречий. Аналитические хранилища (OLAP) в основном читают данные, поэтому их денормализуют намеренно: в «схеме звезды» в центре таблица фактов (продажи), а вокруг — широкие таблицы измерений (товар, покупатель, дата), и отчётам хватает нескольких JOIN. Золотое правило: сначала проектируй в 3НФ, а денормализуй только при измеренной проблеме производительности и всегда вместе с механизмом синхронизации (триггер, представление, ночное обновление).

SELECT s.first_name, s.city, c.title, c.teacher, e.score
FROM enrollments AS e
-- join students and courses
WHERE c.title = 'Mechanics'
ORDER BY e.score DESC;

first_name | city | title | teacher | score
Fidan | Naxçıvan | Mechanics | Ülviyyə Axundova | 94
Murad | Gəncə | Mechanics | Ülviyyə Axundova | 81
Rəşad | Bakı | Mechanics | Ülviyyə Axundova | 77
Elvin | Sumqayıt | Mechanics | Ülviyyə Axundova | 68

SELECT teacher
       -- number of different subjects
FROM courses
GROUP BY teacher
ORDER BY teacher;

teacher | subjects
Elnur Qasımov | 1
Nərmin Vəliyeva | 1
Ramin Səfərov | 1
Samir Hacıyev | 1
Sara Mitchell | 1
Ülviyyə Axundova | 1