Многотабличные базы данных — продолжение

1.

Многотабличные
базы данных
Почему данным нужна не одна таблица, а шесть связанных — и как соединять их в
запросах. Продолжение работы с базой данных колледжа CollegeDB.

2.

Где мы сейчас
ПРОЙДЕНО РАНЬШЕ
1–4
5
6
«Команды SQL в Management Studio»
создали базу колледжа CollegeDB: шесть таблиц Students, Groups,
Teachers, Subjects, Grades, Schedule; данные загружены, связи
FOREIGN KEY настроены
«Индексы»
ускорили поиск: CREATE INDEX, B-Tree, кластерный и
некластерный — ни один запрос не изменился
«Язык SQL»
декларативный: описываем ЧТО получить; диалект T-SQL;
группы операторов DDL, DML, DCL
ВОПРОС, С КОТОРОГО НАЧНЁМ
В CollegeDB уже шесть таблиц. Но зачем
шесть? Что сломается, если сложить
студентов, группы, предметы и
преподавателей в одну большую?
Ответ дадим экспериментом: соберём всё в
одну таблицу — и увидим три беды,
которые разобьют её за пять минут.

3.

Э к с п е р и м ен т : в с я б а з а в о д н о й т а б л и ц е
Сложим студентов, предметы, преподавателей и кафедры в одну таблицу College_All.
Е Д И Н А Я Т А Б Л И Ц А College_All
ФИО студента
Группа
Предмет
Преподаватель
Кафедра
Телефон кафедры
Козлов М.
ИС-301
SQL Server
Смирнов А.В.
Программирование
32-12
Козлов М.
ИС-301
Матанализ
Кузнецова И.П.
Математика
55-34
Волкова Е.
ИС-301
SQL Server
Смирнов А.В.
Программирование
32-12
Волкова Е.
ИС-301
Матанализ
Кузнецова И.П.
Математика
55-34
Новиков Д.
ИС-302
SQL Server
Смирнов А.В.
Программирование
32-12
Новиков Д.
ИС-302
Физика
Орлов П.С.
Естественные науки
41-18
36
Ячеек в шести строках
17
уникальных значений
среди них
составляют
53% таблицы
дубликаты
ИС-301 повторяется 4 раза,
Смирнов А.В. — 3 раза, телефон
32-12 — 3 раза
Чем больше данных, тем хуже: 100 студентов × 6 предметов = 600 строк, и каждая тащит за собой одни и те же фамилии, названия групп
и телефоны кафедр.

4.

Т р и а н о м а л и и о д н о та б л ич но й б а з ы
Аномалия — операция, которая приводит данные к противоречию. В College_All их ровно три — по одной на каждую команду DML.
UPDATE
1
INSERT
2
DELETE
3
Аномалия обновления
Аномалия вставки
Аномалия удаления
Меняется телефон кафедры
Программирование: 32-12 → 48-22.
Нельзя занести нового
преподавателя, пока у него нет ни
одного студента: строке нужен
студент и группа.
Новиков Д. отчислен — удаляем его
две строки.
Править придётся все три строки —
пропустили одну, и в базе два разных
«правильных» номера.
Причина — неявная избыточность:
телефон повторяется в каждой
строке кафедры.
→ Д АН Н Ы Е П Р О Т И В О Р Е Ч И В Ы
Выдумывать данные или оставлять
пустые поля — значит получать
неверные ответы на запросы.
Вместе с ними исчезают предмет
«Физика», преподаватель Орлов П.С.
и кафедра с телефоном: эта
информация жила только в его
строках.
→ Д АН Н Ы Е Н Е Д О С Т У П Н Ы
→ Д АН Н Ы Е П О Т Е Р Я Н Ы
Причина у всех трёх одна — избыточность. Лекарство тоже одно: разнести данные по связанным таблицам — этим мы и займёмся.

5.

В н е ш н и й к л ю ч : к а к та б л и ц ы д е р ж а тс я д р у г з а д р у г а
Внешний ключ (FOREIGN KEY) — столбец, в котором хранится значение первичного ключа другой таблицы. Пара «первичный ключ
→ внешний ключ» и создаёт связь между таблицами.
Groups
Id
Students
Id
PK
Name
PK
FirstName
Course
1:M
LastName
GroupId
FK
Students.GroupId хранит значения Groups.Id — каждый студент ссылается ровно на одну группу (связь уже настроена с ограничением
FK_Students_Groups)
Grades
StudentId → Students.Id
FK
SubjectId → Subjects.Id
FK
Grade
GradeDate
В одной таблице может быть несколько внешних ключей: Grades
одновременно соединяет студентов и предметы.

6.

Т р и ти п а с в я з е й
Любую схему данных описывают три вида отношений — у каждого своя линия на диаграмме.
Один к одному
1:1
Teachers
AuthData
Строка одной таблицы соответствует
ровно одной строке другой. В
CollegeDB таких связей нет — и это
нормально: тип нужен редко.
Классика — вынести логины и пароли
преподавателей в отдельную таблицу
AuthData с повышенной защитой: мало
данных либо секретные данные.
Один ко многим
1:M
Многие ко многим
M:N
Groups
Students
Teachers
Students
Одной группе соответствует множество
студентов, но каждый студент учится
ровно в одной группе.
Самый частый тип связи — основа
любой базы. В CollegeDB: Groups →
Students, Students → Grades, Subjects →
Grades.
Преподаватель ведёт занятия в
нескольких группах, и в каждой группе
преподают несколько преподавателей.
Физически такая связь в базе не
хранится: между таблицами ставят
связующую — она распадается на две
связи 1 : M.
Правило проектирования: встретили M : N — добавьте третью таблицу с двумя внешними ключами. В CollegeDB эту роль играют
Grades (студенты предметы) и Schedule (преподаватели группы).

7.

Ц е л о стн о с т ь д а н н ы х : ш е с ть о г р а н ич ен ий
Ограничения (constraints) проверяет сам сервер — раньше и быстрее любых приложений.
NOT NULL
DEFAULT
CHECK
В столбце каждой записи обязательно
должно быть значение
Если значение не указано, подставляется
заданное по умолчанию
Значение должно удовлетворять условию
— иначе запись не добавится
ПР ИМЕР Students.FirstName
ПР ИМЕР Groups.Course DEFAULT 1
ПР ИМЕР Grades.Grade BETWEEN 2 AND 5
UNIQUE
PRIMARY KEY
FOREIGN KEY
Значения столбца не повторяются
Первичный ключ: комбинация UNIQUE
+ NOT NULL, только один на таблицу
Значение должно существовать в
связанной таблице — ссылочная
целостность
ПР ИМЕР Students.Email
ПР ИМЕР Groups.Id
ПР ИМЕ Р Students.GroupId → Groups.Id
Защитить данные можно и триггерами либо проверками в приложении — но сервер проверяет ограничения в первую очередь, поэтому
это самый быстрый и надёжный способ.

8.

Каскадные действия внешнего ключа
Что сервер сделает со связанными строками, когда удаляют или меняют строку, на которую ссылаются?
NO ACTION
1
ON DELETE CASCADE
Вариант по умолчанию: операция
запрещена. Удалить группу, в которой
учатся студенты, не выйдет — сервер
ответит ошибкой ссылки. Именно так
защищена CollegeDB сейчас.
Удалить и все связанные строки:
удалили группу — сервер сам удалил
всех её студентов и их оценки. Мощно и
опасно.
→ ОШИБКА ССЫЛКИ
→ У Д АЛ Е Н И Е Ц Е П О Ч К О Й
ON DELETE SET NULL
3
ON UPDATE CASCADE
Разорвать связь, не удаляя: у
студентов GroupId станет NULL —
формально «без группы». Столбец
должен разрешать NULL.
При изменении первичного ключа
обновить все внешние ключи, которые
на него ссылаются.
→ СВЯЗЬ ОБНУЛЕНА
→ КЛЮЧИ ОБНОВЯТСЯ СИНХРОННО
DDL
2
4
ALTER TABLE Students
ADD CONSTRAINT FK_Students_Groups
FOREIGN KEY (GroupID)
REFERENCES Groups(GroupID)
ON DELETE CASCADE;
-- каскад добавляется прямо в ограничение связи
CASCADE действует цепочкой: одно DELETE затрагивает строки сразу в нескольких таблицах — включайте осознанно и только для зависимых сущностей.
✓ Для CollegeDB разумно: NO ACTION на Groups → Students, CASCADE на Grades → Students — оценки без студента не имеют смысла.

9.

Н о р м а л из а ци я : п у т ь к п р а в и л ь н о й с т р у к ту р е
Нормализация — разбиение одной таблицы на несколько связанных, при котором исчезают избыточность и аномалии.
Правила — нормальные формы — ввёл Эдгар Кодд; сегодня их восемь, но на практике хватает первых трёх.
1 1НФ
Значения атомарны: одна
ячейка — одно значение. У
каждой записи есть
первичный ключ. College_All
нарушает уже 1НФ: ФИО
свалено в один столбец,
ключа нет вовсе.
2 2НФ
→
1НФ + неключевые столбцы
зависят от всего первичного
ключа, а не от его части.
Название группы не должно
зависеть от половины
составного ключа «студент +
предмет».
3 3НФ
→
2НФ + нет транзитивных
зависимостей. Телефон
зависит от кафедры, а не от
студента: цепочка Id →
Кафедра → Телефон
разрывается выносом кафедр
в отдельную таблицу.
4 BCNF
→
Усиленная 3НФ: ключи не
зависят от неключевых
столбцов. Актуальна при
двух перекрывающихся
составных ключах —
например, в журнале оценок.
Формы — рекомендации, а не законы: чем выше форма, тем больше соединений в запросах и тем больше ресурсов нужно системе. Для
учебной и большинства рабочих баз разумный потолок — 3НФ.

10.

Проверка: CollegeDB уже нормализована
Прогоним College_All через три ступени и получим знакомые шесть таблиц.
College_All — как не надо
✗ 7 столбцов, 53% дубликатов
CollegeDB — 6 таблиц, 3НФ
Ш А Г 11НФ
Students
Groups
Добавляем ключ Id, раскладываем ФИО на
FirstName и LastName
Teachers
Subjects
Grades
Schedule
↓
✗ нет ключа → нарушена 1НФ
✓ Каждая таблица — об одной сущности
Ш А Г 22НФ
✗ группа повторяется в строках
студентов → не 2НФ
Выносим группы — таблица Groups; оценки
живут в своей Grades
✓ PK есть в каждой таблице
↓
✗ телефон зависит от кафедры, а не
✓ Связи через FOREIGN KEY
от студента → не 3НФ
Ш А Г 33НФ
UPDATE
INSERT
DELETE
Выносим предметы → Subjects,
преподавателей → Teachers, расписание →
Schedule
✓ Частичных и транзитивных
зависимостей нет

11.

Д е н о р м а л и за ц ия : к о г д а п р а в и л а н а р у ш а ю т
Денормализация — осознанный возврат избыточности в нормализованную базу ради скорости чтения.
→
Зачем это делают
Чем платят
→
Отчёт читается без JOIN-ов — быстрее в десятки раз
Рассинхронизация: фамилия сменилась — копии в сотнях
строк устарели
→
Копия фамилии студента прямо в Grades: журнал — один
SELECT без соединений
Аномалии обновления возвращаются — вспомните
College_All
→
Кэш агрегатов: число студентов в группе считается один
раз, а не при каждом запросе
Лишний объём и логика синхронизации: триггеры или
ETL-процессы
ГДЕ ВСТРЕЧАЕТСЯ
отчётные витрины
аналитические хранилища данных
реплики только для чтения
Правило: сначала нормализуем до 3НФ. Денормализация — исключение по измеренной причине: сначала план выполнения показал, что JOIN
тормозит, — потом дублируем данные и строим механизм синхронизации.

12.

За п р о с ч ер ез тр и та б л и ц ы
Какие оценки получил каждый студент и по каким предметам? Имена — в Students, оценки — в Grades, предметы — в Subjects.
Students
1
∞
∞
Grades
Id · FirstName · LastName
1
StudentId · SubjectId ·
Grade
Subjects
Id · Name
связующая: два внешних ключа + составной PRIMARY KEY (StudentId, SubjectId)
T-SQL
SELECT S.FirstName + ' ' + S.LastName AS Student,
Sub.SubjectName
AS Subject,
Gr.Grade
FROM Students AS S
JOIN Grades AS Gr ON S.StudentID = Gr.StudentID
JOIN Subjects AS Sub ON Sub.SubjectID = Gr.SubjectID;
ПОЧЕМУ GRADES ПОДХОДИТ
она хранит FK обеих сторон:
StudentId → Students.Id
SubjectId → Subjects.Id
Вместе они образуют составной первичный ключ — запись
не задублировать
ФОРМУЛА СОЕДИНЕНИЯ
N таблиц в FROM требуют N − 1 условий связи в JOIN:
РЕЗУЛЬТАТ ЗАПРОСА
Student
Subject
Grade
Волкова Елена
Базы данных
5
Козлов Михаил
Программирование
4
Козлов Михаил
Базы данных
5
2 таблицы → 1 условие
3 таблицы → 2 условия (соединяем AND-ом)

13.

Д ву с м ыс л енно сть им ё н и п с е в до н и м ы
Столбец Id есть и в Students, и в Groups. Просим просто Id — сервер отказывается угадывать.
ОШИБКА
Полное имя
SELECT FirstName,
1 Таблица.Столбец — например, Students.Id —
Id -- а чей Id?
снимает двусмысленность
FROM Students, Groups
WHERE Students.GroupId = Groups.Id;
Msg 209: Ambiguous column name 'Id'.
ИСПРАВЛЕНО
SELECT
S.FirstName,
S.StudentID AS StudentId,
G.GroupName AS GroupName
FROM Students AS S
JOIN Groups AS G ON S.GroupID = G.GroupID;
AS
2 Псевдоним
Students AS S делает запрос короче: S.Id вместо
Students.Id
действия
3 Область
Псевдоним живёт только внутри одного запроса

14.

С о в р е м е нн ый с и н т а к с и с : I N N E R J O I N
Запятая в FROM — наследие стандарта-86. ANSI-92 разделяет связи и фильтры — и это спасает от ошибок.
Классика: через WHERE
SELECT S.FirstName, G.GroupName
FROM Students AS S, Groups AS G
WHERE S.GroupID = G.GroupID;
Современный: JOIN … ON
SELECT S.FirstName, G.GroupName
FROM Students AS S
JOIN Groups AS G ON S.GroupID = G.GroupID;
= Результат одинаковый: соединяются только строки, у которых нашлась пара
1 Связь не потеряется
2 Декартово произведение невозможно
3 Стандарт де-факто
Условие написано в ON — месте, которое
без связи не имеет смысла
Забыли ON — запрос просто не
выполнится: сервер не позволит соединить
таблицы без условия
так пишут в документации, учебниках и
ORM; запрос читается легче: связи —
отдельно, фильтры — в WHERE отдельно
✓ С этого слайда в примерах курса — только JOIN … ON. Старый стиль не запрещён, но новый безопаснее.

15.

В н е ш н и е с о е д и н е н ия : L E F T J O I N
INNER выбрасывает строки без пары. А если нужны все студенты — даже те, у кого нет ни одной оценки?
LE FT J OIN — в с е с тр ок и лев ой + п а р ы и з п р а вой
RIGHT JOIN — зеркально
T-SQL
SELECT
S.FirstName + ' ' + S.LastName AS Student,
Gr.Grade
FROM Students AS S
LEFT JOIN Grades AS Gr
ON S.StudentID = Gr.StudentID;
РЕЗУЛЬТАТ ЗАПРОСА
Student
Grade
Волкова Елена
5
Козлов Михаил
4
Новиков Дмитрий
NULL
FULL JOIN — всё из обеих
Приём «найти без пары»
WHERE Gr.StudentId IS NULL отбирает строки, которым
не нашлась пара, — классическое анти-соединение:
АНТИ-СОЕДИНЕНИЕ
SELECT S.FirstName + ' ' + S.LastName
FROM Students AS S
LEFT JOIN Grades AS Gr
ON S.StudentID = Gr.StudentId
WHERE Gr.StudentId IS NULL;
→ Оценок нет — но строка выведена: LEFT сохранил студента
Практический смысл: списки «студенты без оценок», «предметы без расписания», «группы без куратора» — всё это LEFT JOIN + IS NULL.

16.

С о е ди н е ние та б л иц в S ELEC T
Первый многотабличный запрос: данные теперь берём из двух таблиц — Students и Groups
T-SQL
SELECT
FirstName + ' ' + LastName AS FullName,
GroupName
FROM Students, Groups
WHERE Students.GroupID = Groups.GroupID;
таблицы
1 Перечислить
в FROM через запятую: Students, Groups —
сервер увидит обе
Связать по ключам
WHERE условие «внешний ключ = первичный ключ»:
2 вStudents.GroupId
= Groups.Id — сердце
многотабличного запроса
(3 rows affected)
РЕЗУЛЬТАТ ЗАПРОСА
FullName
GroupName
Волкова Елена
ИС-301
Козлов Михаил
ИС-301
Новиков Дмитрий
ИС-302
столбцы
3 Выбрать
в SELECT можно указывать столбцы любой из таблиц;
AS даёт понятные имена
Всё, что мы знали об операторах, работает без изменений: WHERE фильтрует, ORDER BY сортирует — просто данные теперь из двух
таблиц.

17.

А г р е г а ци я п о с в я з а н н ы м та б л и ц а м
Соединение + группировка = готовые отчёты: сколько оценок и какой средний балл у каждого предмета.
T-SQL
Один столбец — одна группа
SELECT
Sub.SubjectName AS Subject,
COUNT(*)
AS Grades,
AVG(Gr.Grade) AS AvgGrade
FROM Grades AS Gr
INNER JOIN Subjects AS Sub
ON Gr.SubjectID = Sub.SubjectID
GROUP BY Sub.SubjectName
ORDER BY AvgGrade DESC;
1 всё, что в SELECT без агрегата, обязано попасть в
GROUP BY — иначе сервер напомнит ошибкой
WHERE и HAVING
2 WHERE фильтрует строки ДО группировки, HAVING
— группы ПОСЛЕ: HAVING AVG(Gr.Grade) > 3.5
оставит только «хорошие» предметы
ОТЧЁТ: ОЦЕНКИ И СРЕДНИЙ БАЛЛ ПО ПРЕДМЕТАМ
Subject
Grades
AvgGrade
SQL Server
12
4
Матанализ
8
4
Физика
5
3
Пять агрегатов
3 COUNT считает строки, SUM суммирует, AVG
усредняет, MIN и MAX находят крайние значения
Это уже настоящий отчёт для журнала колледжа — и всего восемь строк SQL: соединение сделано в ON, агрегаты посчитал сервер.

18.

Ло в у ш к а : дека р то во п р о и з в е де н и е
Уберём условие связи — и сервер честно приставит каждую строку одной таблицы к каждой строке другой.
БЕЗ WHERE
30 × 8
SELECT S.FirstName, G.GroupName
FROM Students AS S, Groups AS G;
студентов и групп в CollegeDB
-- условие связи забыли
Ошибка не возникнет: запрос выполнится успешно — и вернёт бессмыслицу
Ф Р А Г М Е Н Т
Р Е З У Л Ь Т А Т А
—
В С Е Г О
2 4 0
С Т Р О К
Козлов М.
ИС-301
Козлов М.
ИС-302
строк бессмыслицы: каждый студент «учится» во всех восьми группах сразу
… ещё 236 строк
Волкова Е.
240
ИС-302
Ошибка растёт лавинообразно: четыре таблицы по 100 строк — это 100 000 000 комбинаций. Сервер не отличит её от осмысленного
запроса — отличить должны вы.
✓
Лечение: вернуть условие связи — WHERE S.GroupId = G.Id. Правило N − 1 из прошлого слайда — ваша страховка.

19.

Индексы на внешних ключах
Почему одни соединения летают, а другие сканируют таблицы целиком.
Индексы
→
Внешние ключи
→
JOIN
каждое условие ON S.GroupId = G.Id — это поиск по столбцу GroupId
DDL
индексируется сам
1 PK
первичный ключ автоматически получает индекс (кластерный)
CREATE NONCLUSTERED INDEX
IX_Students_GroupId
ON Students (GroupId);
FK — не индексируется
2 внешний ключ сервер индексировать не обязан: без нашего
индекса каждое соединение сканирует таблицу целиком
Где увидеть
-- имя по соглашению из §05: IX_<Таблица>_<Поле>
3 SSMS: узел Indexes у таблицы или запрос к sys.indexes; в плане
выполнения Index Seek вместо Table Scan
Правило проектирования: на каждый внешний ключ — индекс. Мы соединили три темы курса: ускоряем JOIN так же, как ускоряли
SELECT.

20.

П р а к ти ч еско е за да ни е
✓ КЛЮЧЕВЫЕ ВЫВОДЫ
1
2
3
Построить диаграмму CollegeDB: Database Diagrams → New Database
Diagram → добавить все шесть таблиц. Найти все «вороньи лапки» связей
1:M.
✓ Одна таблица порождает аномалии: обновления, вставки,
Вывести ФИО студентов и названия их групп (Students + Groups) двумя
синтаксисами: через WHERE и через INNER JOIN … ON. Псевдонимы S
и G, столбец G.Course.
✓ Связи: 1:1 — редко, 1:M — основа, M:N — через
LEFT JOIN Grades + WHERE Gr.StudentId IS NULL: вывести
студентов, у которых нет ни одной оценки.
удаления
✓ FK хранит PK другой таблицы и создаёт связь
связующую таблицу
✓ Целостность: шесть ограничений от NOT NULL до
FOREIGN KEY
✓ NO ACTION защищает группу со студентами,
CASCADE удаляет цепочкой
балл по каждому предмету: INNER JOIN + GROUP BY + AVG,
4 Средний
сортировка ORDER BY AvgGrade DESC. В отчёт добавить COUNT(*) —
число оценок.
ФИО, предмет и оценку (Students + Grades + Subjects) для
5 Вывести
группы ИС-301, отсортировав по фамилии через ORDER BY.
✓ Нормализация до 3НФ — правило, денормализация — по
измеренной причине
✓ Два синтаксиса соединений: WHERE и JOIN … ON
✓ LEFT JOIN + IS NULL — приём «найти без пары»
✓ GROUP BY по соединённым таблицам — готовые
отчёты
6
Убрать условия связи из задания 05, сравнить число строк (COUNT(*)),
объяснить декартово произведение. Затем создать IX_Grades_StudentId и
объяснить, как он ускорит соединение.
✓ N таблиц → N − 1 условий; на каждый FK — индекс