Многотабличные базы и связи

Задание 3 — единственное в первой части ЕГЭ, где вам выдают файл с настоящей базой данных и просят достать из неё число. Не пересказать определение «реляционная модель», не нарисовать схему — посчитать сумму, количество или найти человека. Всё решается на компьютере, и решается за три-четыре минуты, если вы понимаете, что именно с чем связано.

Почему вообще данные лежат в нескольких таблицах, а не в одной? Ответ на этот вопрос и есть тема урока — и он же подсказывает алгоритм решения. В базе «Молочные продукты», которую ФИПИ гоняет из варианта в вариант, три таблицы: «Движение товаров», «Товар», «Магазин». Цена лежит в одной таблице, район — в другой, количество упаковок — в третьей. Ни одного вопроса нельзя решить по одной таблице: любой ответ требует склеить записи по ключу.

Именно на этом теряют балл. Ученик открывает файл, видит столбец «Количество упаковок», фильтрует по датам, суммирует — и получает количество штук вместо суммы в рублях. Или суммирует всё подряд, не заметив, что половина строк — это поступления, а не продажи.

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

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

Беда начинается на второй тысяче строк.

Избыточность. Ряженка продавалась триста раз — значит, строка «Ряженка 4%, 0,5 л, 78 руб.» лежит в файле триста раз. Данные, которые по смыслу существуют в одном экземпляре, хранятся сотнями копий. Место — полбеды; хуже другое.

Аномалия обновления. Цена ряженки выросла с 78 до 82 рублей. Теперь её надо поправить в трёхстах строках. Поправили в двухстах девяноста восьми — и база стала противоречивой: в ней одновременно написано, что ряженка стоит 78 и что она стоит 82. Никакой запрос больше не даёт правильного ответа, потому что правильного ответа в базе нет.

Аномалии вставки и удаления. В сеть привезли новый товар, но он ещё ни разу не продавался. Куда его записать? В единственной таблице строка появляется только вместе с операцией — значит, товар нельзя завести, пока его не продали. Это аномалия вставки. Обратная беда: если удалить все операции по магазину, из базы исчезнет и сам магазин с его адресом. Это аномалия удаления.

Лекарство от всех трёх — одно и то же: каждый факт хранить ровно в одном месте, а остальные строки на него ссылаться. Именно это и называется многотабличной базой.

Разрезание идёт по простому правилу: всё, что относится к одной сущности, уезжает в свою таблицу.

Что в нашей базе является сущностью? Товар — да: у него есть собственные свойства (наименование, единица измерения, фасовка, цена), которые не зависят ни от какой операции. Магазин — да: район и адрес принадлежат магазину, а не продаже. Операция — тоже да: у неё своя дата, свой объём, свой тип.

Получаются три таблицы.

«Товар»: артикул, наименование, единица измерения, количество в упаковке, цена за упаковку. Каждый товар — ровно одна строка.

«Магазин»: ID магазина, район, адрес. Каждый магазин — ровно одна строка.

«Движение товаров»: ID операции, дата, ID магазина, артикул, количество упаковок, тип операции. Здесь строк много, но в каждой нет ни цены, ни района — только ссылки на них.

Обратите внимание, что произошло с ценой. Раньше она стояла в каждой строке о продаже; теперь — в одной строке таблицы «Товар». Изменение цены — правка одной ячейки, и противоречию взяться неоткуда. Ровно так же с районом: он записан один раз рядом с магазином.

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

Два термина, которые путают чаще всего, хотя различие между ними простое.

Первичный ключ — поле (или набор полей), которое однозначно определяет строку своей таблицы. В таблице «Товар» это артикул: артикул 103 — это ровно одна строка, и никакая другая. В таблице «Магазин» — ID магазина. Требования к первичному ключу два: значения не повторяются и не бывают пустыми.

Внешний ключ — поле чужой таблицы, значения которого берутся из первичного ключа своей. В «Движении товаров» внешних ключей два: «Артикул» смотрит в «Товар», «ID магазина» смотрит в «Магазин». Значения внешнего ключа, наоборот, повторяются сколько угодно: артикул 103 встретится в сотне операций, и это нормально — именно так выражается связь «один товар — много операций».

Отсюда практическое правило чтения схемы: сторона «один» — там, где ключ первичный; сторона «многие» — там, где он внешний. На схеме базы данных, которую ФИПИ показывает картинкой рядом с условием, ключевое поле подчёркнуто, а связи нарисованы линиями. Линия всегда идёт от подчёркнутого поля к неподчёркнутому — от «одного» к «многим».

Ещё одно следствие, полезное на экзамене: по внешнему ключу вы всегда идёте в сторону справочника и находите ровно одну строку. По артикулу 103 находится один товар и одна цена. Обратный путь неоднозначен: у товара 103 операций много, и «найти операцию по артикулу» — это уже не одна строка, а список.

Связь «один-ко-многим» умещается в два поля: в таблице на стороне «многие» заводят внешний ключ. А вот «многие-ко-многим» так не записывается, и понять почему стоит один раз.

Пример: ученики и кружки. Один ученик ходит на несколько кружков, на один кружок ходит несколько учеников. Попробуем обойтись двумя таблицами. Добавим в «Ученика» поле «Кружок» — но кружков у него три, а поле одно. Заводить «Кружок 1», «Кружок 2», «Кружок 3»? Тогда четвёртый кружок сломает структуру базы, а у большинства учеников два поля из трёх будут пустыми.

Выход — третья таблица, в которой каждая строка означает один факт участия: «Записи», два поля — ID ученика и ID кружка. Ученик с тремя кружками даёт три строки. Новый кружок — новая строка, структура базы не меняется.

Эта промежуточная таблица распадается на две обычные связи «один-ко-многим»: один ученик — много записей, один кружок — много записей. Поэтому в реляционной базе связи «многие-ко-многим» физически не существует: она всегда представлена парой связей через промежуточную таблицу.

В базе «Движение товаров» промежуточная таблица уже есть, только её не называют промежуточной: сама таблица операций связывает товары с магазинами многие-ко-многим и попутно хранит собственные данные операции — дату, количество, тип.

Условие линии 3 начинается словами «Задание выполняется с использованием прилагаемого файла». Файл — электронная таблица с тремя листами: «Движение товаров», «Товар», «Магазин». В листе операций обычно несколько сотен строк, и просмотреть их глазами нельзя — в этом весь смысл задания.

Дальше идёт описание таблиц, схема связей картинкой и единственный вопрос. Формулировка из реальных вариантов звучит так: «определите, на какую сумму (в руб.) был продан товар „Кефир 1%“ в магазинах Центрального района за период с 13 по 21 октября включительно. Стоимость продажи одной операции равна произведению количества проданных упаковок на цену за упаковку».

Разберём эту фразу по кусочкам — каждый кусочек превращается в условие отбора.

— «был продан» → тип операции «Продажа», поступления не считаем; — «товар „Кефир 1%“» → наименование из таблицы «Товар», а в операциях лежит только артикул, значит нужна связь по артикулу; — «в магазинах Центрального района» → район из таблицы «Магазин», в операциях лежит ID магазина, значит нужна вторая связь; и учтите, что районом «Центральный» могут быть отмечены несколько магазинов; — «с 13 по 21 октября включительно» → отбор по дате, оба конца диапазона входят; — «произведению количества на цену» → сумма произведений, а не сумма количеств.

Пять условий, два из которых требуют выхода в соседние таблицы. Схема решения всегда одна: подтянуть недостающие поля по ключу → отфильтровать → просуммировать произведение.

Почему ВПР, а не просто взгляд в соседнюю таблицу. Строк операций на экзамене несколько сотен, и вручную вы не сопоставите ни одну. ВПР делает ровно то, что описано в условии словами «поле „Артикул“ ссылается на „Артикул“ таблицы „Товар“»: берёт значение внешнего ключа и находит по нему строку справочника. Это и есть соединение таблиц, выполненное вручную.

Почему четвёртый аргумент ЛОЖЬ. Без него ВПР ищет приблизительное совпадение и требует, чтобы справочник был отсортирован. На неотсортированном справочнике приблизительный поиск молча возвращает не ту строку — цену чужого товара. Ошибка не видна: формула не ругается, просто ответ неверный. Пишите ЛОЖЬ (или 0) всегда.

Почему доллары. $A$2:$B$6 — абсолютная ссылка, она не смещается при протягивании. Напишете A2:B6 — в двенадцатой строке формула будет искать в диапазоне A13:B17, где справочника уже нет, и вернёт ошибку или пустоту.

Почему СУММПРОИЗВ, а не СУММЕСЛИМН. СУММЕСЛИМН умеет складывать один столбец по нескольким условиям, но не умеет складывать произведение двух столбцов. А в задании 3 нужна именно сумма произведений: количество на цену. СУММПРОИЗВ для этого и сделан.

Про даты. ДЕНЬ(B2:B13) работает, только если в столбце B лежат настоящие даты, а не текст. Если ФИПИ отдал даты текстом (так бывает), формула вернёт ошибку; тогда проще отсортировать лист по дате и взять диапазон строк, либо перевести столбец в даты через «Текст по столбцам». И помните: при работе с одним месяцем сравнивать достаточно день, но если диапазон пересекает границу месяца, сравнивать нужно дату целиком.

Многотабличная база — это способ хранить каждый факт ровно один раз. Цена товара живёт в таблице «Товар», район — в таблице «Магазин», а операции только ссылаются на них внешними ключами. Плата за такое хранение — необходимость соединять таблицы при каждом вопросе, и задание 3 ЕГЭ проверяет ровно это умение.

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

Решение любой задачи линии 3 укладывается в три действия: подтянуть недостающие поля по ключу, отфильтровать строки по всем условиям вопроса, посчитать итог. В электронной таблице это ВПР плюс СУММПРОИЗВ, на Python — словари-справочники плюс один цикл с continue. Второй способ надёжнее, когда условий много или когда база родословная.

И последнее, что стоит проверять перед записью ответа: не перепутан ли тип операции, взят ли весь район, а не один магазин, входят ли обе границы диапазона дат и умножено ли количество на цену. Четыре вопроса, десять секунд — и балл остаётся при вас.

Формулы быстрее, когда вопрос простой. Но линия 3 умеет усложняться: «сумма по району, где продано больше всего упаковок», «средняя цена по отделу», родословные с поиском внуков. Там, где условие перестаёт укладываться в одну строку формулы, программа надёжнее, потому что её можно писать по шагам и печатать промежуточные результаты.

Читать файл базы данных на Python проще всего библиотекой openpyxl — она входит в состав любого школьного дистрибутива с Python и умеет открывать .xlsx. Главный приём один: превратить каждый справочник в словарь, где ключ — первичный ключ таблицы, а значение — то, что нам из этой таблицы нужно. После этого соединение таблиц превращается в обычное обращение по ключу, а вся задача — в один проход по таблице операций.

Три строчки на словарь, один цикл на подсчёт — и никаких протягиваний формул.

Трассировка выше подобрана так, что каждая отброшенная строка — это ловушка из реальных вариантов. Пройдём по ним ещё раз.

Забыли тип операции. Строка от 14.10 — поступление на 15 упаковок. Если не отфильтровать тип, к ответу добавится 15 · 62 = 930 рублей, и вместо 1984 выйдет 2914. Это самая частая потеря балла в линии 3: слово «продан» в вопросе легко проскочить глазами.

Взяли один магазин вместо района. «Центральный район» — это магазины 1 и 4. Ученик, который нашёл в справочнике первую строку с нужным районом и дальше фильтровал по ID 1, потеряет операции 16.10 и 18.10, то есть 6 · 62 + 11 · 62 = 1054 рубля. Правило: фильтруем по району, а не по ID магазина, и список подходящих ID сначала выписываем целиком.

Промахнулись по границе диапазона. «С 13 по 21 включительно» означает, что 13-е и 21-е входят. Операции 12.10 и 22.10 — соседи границ с обеих сторон, и обе должны отсеяться. Если написать строгое неравенство 13 < data.day < 21, потеряются и 13-е, и 21-е число: 8 · 62 + 4 · 62 = 744 рубля мимо ответа.

Четвёртая, менее заметная ошибка — сложить количества вместо денег. 32 упаковки тоже выглядят как правдоподобный ответ, и проверить себя просто: если в вопросе есть слово «руб.», ответ обязан получаться умножением на цену.

Вторая база, которую ФИПИ использует в линии 3, — родословная. В ней тоже две таблицы, но связь устроена хитрее.

Таблица «Люди»: ID, Фамилия_И.О., Пол, Год рождения. Первичный ключ — ID.

Таблица «Родственные связи»: ID_{Родителя}, ID_{Ребёнка}. Оба поля — внешние ключи, и оба смотрят в одну и ту же таблицу «Люди». Такая связь называется рекурсивной: сущность связана сама с собой.

Вот почему её нельзя записать иначе. У человека двое родителей, а детей может быть сколько угодно — значит, связь «родитель — ребёнок» это многие-ко-многим, а такие связи, как мы разобрали, всегда живут в отдельной таблице. Строка «(1, 3)» читается как один факт: «человек 1 — родитель человека 3».

Дальше вся родня выражается через эту одну связь.

— Дети человека X: все ID_{Ребёнка} в строках, где ID_{Родителя} равен X. — Родители X: все ID_{Родителя} в строках, где ID_{Ребёнка} равен X. — Внуки X: дети его детей, то есть шаг вниз повторён дважды. — Правнуки X: три шага вниз. — Братья и сёстры X: дети его родителей, кроме самого X. — Двоюродные братья и сёстры X: дети братьев и сестёр его родителей.

Заметьте общий приём: любое родство — это путь по связям заданной длины и в заданном направлении. Умея делать один шаг вниз и один шаг вверх, вы соберёте любое родство из условия.

Код из разбора меняется под любой вопрос почти без переписывания — меняется только направление и длина пути.

Шаг вверх. Нужен второй словарь: roditeli.setdefault(reb, []).append(rod). Тот же цикл по связям, только ключ и значение поменялись местами. Через него находятся родители, бабушки и дедушки (два шага вверх), братья и сёстры.

Братья и сёстры. Это дети родителей, кроме самого человека: brat = set(), дальше по каждому родителю из roditeli[X] добавляем всех его детей, а в конце brat.discard(X). Забыть discard — типичная ошибка: человек попадает в список собственных братьев, и ответ больше правильного на единицу.

Все потомки, а не только внуки. Когда в условии написано «прямые потомки» без указания поколения, фиксированного числа шагов не хватает: дерево уходит вниз на неизвестную глубину. Обходят его очередью:

potomki = set()

ochered = list(deti.get(start, []))

while ochered:

x = ochered.pop()

if x not in potomki:

potomki.add(x)

ochered += deti.get(x, [])

Цикл забирает человека из очереди, отмечает его как потомка и дописывает в очередь его детей. Когда очередь опустела, обойдено всё поддерево. Проверка if x not in potomki обязательна: без неё человек, до которого ведут два разных пути, обработается дважды.

Фильтр по году или полу. Добавляется в самом конце, когда множество уже собрано: len([p for p in potomki if lyudi[p][2] > 1980]). Считать сразу в цикле опаснее — легко отсечь ветку, через которую шёл путь к нужным людям.