Презентация_Занятие4_Реляционные_БД

1.

И Н Ф О Р М АТ И К А · 1 1 К Л А С С · П О Д Г О Т О В К А К Е Г Э
Занятие 4
Поиск информации в реляционных базах данных
Задание 3 ЕГЭ · теория и практика для занятия
От картотек и указателей к таблицам Эдгара Кодда: как родилась реляционная модель

2.

ПЛАН ЗАНЯТИЯ
Две части одного рассказа
①
Часть 1. Теория
②
Часть 2. Практика
Общий алгоритм решения задания
Учебная база данных «Магазины сладостей»
Тип А: фильтр по одной таблице
Тип Б/В: соединение с одним и двумя
справочниками
От иерархических баз к модели Кодда
SQL и гонка Oracle против IBM
Где эта тема встречается в профессии
Термины: ключи, отношения, целостность
Нормальные формы и схема «звезда»
Тип Г: подсчёт уникальных значений
Как устроено современное задание 3
Ошибки, самопроверка, лайфхаки и чек-лист
2

3.

ТЕОР ИЯ · ДО 1970 ГОД А
Указательный ад: базы данных до Кодда
Заказ
IBM IMS (1966), созданная для программы «Аполлон», хранила
данные деревом: у каждой записи — прямые ссылки на «родителя»
и «детей».
Клиент
Сетевая модель CODASYL разрешила ссылкам ветвиться и в стороны
— данные превращались в сложную паутину указателей.
Товар
жёсткий путь указателей —
заранее продуман разработчиком
Проблема: чтобы получить любые данные, программист обязан идти по
конкретному пути указателей, продуманному заранее. Новый вид отчёта —
новый громоздкий код.
3

4.

ТЕОР ИЯ · 1970 ГОД · ПОВОРОТНЫЙ МОМЕНТ
Эдгар Кодд и рождение таблиц
Математик из исследовательской лаборатории IBM в Сан-Хосе
предложил забыть об указателях вовсе.
Любые данные — как простые прямоугольные
таблицы. Связь — через совпадающее значение, а не
через физический указатель.
Внутри IBM идею встретили скептически — понадобился проект System R. Ларри
Эллисон опередил IBM и выпустил Oracle уже в 1979 году, за два года до DB2.
«Реляционная модель данных для
больших совместно используемых
банков данных»
Э. Ф. Кодд, Communications of the ACM, июнь 1970
1974: Чемберлин и Бойс публикуют SEQUEL —
позже переименован в SQL.
4

5.

Т Е О Р И Я · З АЧ Е М Э Т О И Н Ж Е Н Е Р У
Где эта тема встречается в профессии
1
BI-аналитика и JOIN
2
Аналитик каждый день пишет запросы с объединением таблиц по
общему ключу — тот же навык, только вручную и на маленьком
фрагменте.
3
Проектирование приложений
Первичные и внешние ключи между таблицами — ошибка здесь
означает потерянные данные в проде, а не минус балл.
Хранилища данных
Схема «звезда» (Кимболл): таблица фактов в центре, таблицыизмерения вокруг — точная структура задания 3 из настоящей
индустрии.
4
ВПР в Excel
Функция ВПР / ИНДЕКС-ПОИСКПОЗ — «бедный родственник»
JOIN, доступный любому офисному сотруднику без знания SQL.
5

6.

ТЕОРИЯ · КЛЮЧЕВЫЕ ПОНЯТИЯ
Отношения, ключи, целостность
Отношение
то, что мы называем «таблицей»: строки = кортежи (записи), столбцы = атрибуты (поля)
Первичный ключ
столбец с уникальным кодом — однозначно определяет каждую запись таблицы
Внешний ключ
столбец в фактовой таблице, ссылающийся на первичный ключ другой таблицы
Ссылочная целостность
гарантия, что значение внешнего ключа реально существует в связанной таблице
«Реляционная» — от математического термина «отношение» (relation), а не от слова «связь»
6

7.

Т Е О Р И Я · З АЧ Е М Н Е С К О Л Ь К О ТА Б Л И Ц
Аномалии одной большой таблицы
Если хранить магазин, район, товар, цену и продажу в одной строке, район магазина будет продублирован в каждой его продаже.
магазин
район
товар
...
Магазин №3
Центральный
Алёнка
...
Магазин №3
Центральный
Ирис
...
Магазин №3
Центральный
Барбарис
...
«Центральный» повторяется в каждой строке — опечатка хотя бы в одной из них незаметно «расколет» район на два разных
Решение Кодда (нормальные формы): вынести район в отдельный справочник магазинов. Переименовать район — значит
исправить ОДНУ строку, а не тысячи.
1НФ — атомарность и отсутствие повторов · 2НФ — атрибут зависит от ключа целиком · 3НФ — неключевые атрибуты не зависят друг от друга
7

8.

ТЕОРИЯ · КАК УСТРОЕНА БАЗА ДАННЫХ ЗАДАНИЯ 3
Схема «звезда»: факты и справочники
Одна таблица фактов в центре (растёт с каждым днём) и компактные таблицы-измерения вокруг (меняются редко).
Товары
Магазины
Движение
товаров
(таблица фактов)
Даты
Индустриальный термин: dimensional modeling (Ральф Кимболл) — та же архитектура, что строят инженеры данных для розничных
сетей и банков.
8

9.

Т Е О Р И Я · Ф О Р М АТ З А Д А Н И Я 3
Как устроено современное задание 3
Первое задание экзамена, где нужен компьютер: файл (обычно ODS) с двумя-тремя связанными таблицами по схеме «звезда». Нужно
отобрать записи по нескольким критериям и посчитать или просуммировать.
1
Изучить структуру
названия полей и типы данных каждой таблицы
2
Выделить критерии
все условия отбора записей из текста задания
3
Сопоставить полям
к какой таблице и полю относится каждый критерий
4
Применить критерии
последовательно, отбрасывая неподходящие записи
5
Посчитать/просуммировать
то, что осталось после отбора
6
Записать ответ
в требуемом формате
Официальный алгоритм ФИПИ — основа, на которую мы наложим собственную методику в практической части.
9

10.

П Р А К Т И К А · О Б Щ А Я С Т Р АТ Е Г И Я
Алгоритм решения задания 3
1
Разложить критерии по таблицам
справочники или фактовая таблица
2
Перевести названия в коды
через соответствующий справочник
3
Выписать списки подходящих кодов
отдельно для товаров, магазинов и т. п.
4
Пройти по фактовой таблице
оставить строки с нужными кодами и датой
5
Посчитать нужную величину
записи, сумма поля или уникальные значения
Инструменты Excel/Calc: Автофильтр · ВПР (VLOOKUP) · СУММЕСЛИМН (SUMIFS) · СЧЁТЕСЛИМН (COUNTIFS) · Сводная таблица
10

11.

П РА К Т И К А · С К В О З Н О Й П Р И М Е Р
Учебная база данных «Магазины сладостей»
Магазины
Движение товаров (фрагмент — всего 23
строки)
Товары
код
район
код
категория
название
цена
тов.
маг.
кол-во
1
Северный
1
конфеты
Ирис Кис-кис
180
3
4
20
2
Северный
2
конфеты
Барбарис
120
1
4
16
3
Центральный
3
шоколад
Алёнка
95
2
3
4
4
Центральный
4
шоколад
210
1
2
14
5
мармелад
160
4
3
6
3
3
13
...
...
...
Тёмный
горький
Мармелад
ассорти
Соотношение размеров типично для схемы «звезда»: 4 магазина, 5 товаров — и уже 23 строки продаж, которые продолжали бы
расти с каждым днём торговли.
11

12.

П Р А К Т И К А · Т И П А · Ф И Л ЬТ Р П О О Д Н О Й Т А Б Л И Ц Е
Фильтр по таблице-справочнику
Определите, сколько наименований товаров имеют цену не менее 150 рублей.
Критерий один и относится только к справочнику «Товары» — фактовая таблица вообще не нужна.
Проверяем все пять строк: Ирис Кис-кис (180) ✓, Барбарис (120) ✗, Алёнка (95) ✗, Тёмный горький (210) ✓, Мармелад ассорти (160) ✓.
Ответ: 3 товара
(Ирис Кис-кис, Тёмный горький, Мармелад ассорти)
Методический акцент: не каждое задание 3 требует соединения таблиц — иногда достаточно простого фильтра по одному справочнику.
12

13.

П РА К Т И К А · Т И П Б · С О Е Д И Н Е Н И Е С О Д Н И М С П РА В О Ч Н И КО М
Фактовая таблица + один справочник
Сколько всего единиц товара «Ирис Кис-кис» продано во всех магазинах со 2 по 6 июня включительно?
«Ирис Кис-кис» = код товара 1. Переводим название в код через справочник — это и есть операция соединения.
Отбираем строки с кодом товара 1 и датой 02.06–06.06: строки №5 (14), №9 (12), №14 (10), №17 (10), №19 (3).
Ответ: 49 единиц
14 + 12 + 10 + 10 + 3 = 49
В Excel: добавьте столбец «название» через ВПР по коду товара, затем СУММЕСЛИМН по названию и диапазону дат.
13

14.

П РА К Т И К А · Т И П В · С О Е Д И Н Е Н И Е С Д ВУ М Я С П РА В О Ч Н И К А М И
Фактовая таблица + два справочника
Определите выручку от продажи товаров категории «шоколад» в Центральном районе за 1–6 июня.
«Шоколад» → коды товара 3 (Алёнка, 95₽) и 4 (Тёмный горький, 210₽). «Центральный» → коды магазина 3 и 4.
Подходят: №1 (20×95=1900), №6 (6×210=1260), №7 (13×95=1235), №15 (15×210=3150), №16 (15×210=3150).
Выручка — это произведение количества и цены, а не просто количество единиц.
Ответ: 10 695 рублей
1900 + 1260 + 1235 + 3150 + 3150 = 10 695
Ловушка: запись с товаром «шоколад», но магазином не из Центрального района — отбрасывается, несмотря на совпадение по товару.
14

15.

П РА К Т И К А · Т И П Г · П О Д С Ч Ё Т У Н И К А Л Ь Н Ы Х З Н АЧ Е Н И Й
COUNT vs COUNT DISTINCT
В скольких различных магазинах хотя бы раз продавался «Тёмный горький»?
маг. 3
маг. 1
маг. 4
маг. 3
маг. 1
5 строк с товаром «Тёмный горький», но магазины 1 и 3 повторяются
COUNT (строки): 5 — если бы спрашивали «сколько раз продавался».
COUNT DISTINCT (уникальные): 3 — магазины 1, 3, 4 (магазин 2 не встречается).
Ответ: 3 магазина — не 5!
15

16.

П Р А К Т И К А · Ф О Р М УЛ И Р О В К И И О Ш И Б К И
Формулировка ФИПИ и типичные ошибки
«Используя информацию из приведённой базы данных, определите…» — ответ почти никогда лежит в базе в готовом виде.
!
Искать критерий из условия сразу в фактовой таблице, не заглянув в справочник, где хранятся названия и категории
!
Небрежно читать границы дат: «включительно» значит обе границы входят в диапазон
!
Путать количество записей с количеством уникальных значений (COUNT vs COUNT DISTINCT)
!
Забыть, что искомая величина иногда производная (выручка = количество × цена), а не готовое поле
!
Принять контекстную фразу условия за дополнительный критерий — или наоборот, пропустить настоящий критерий в придаточном
предложении
16

17.

П РА К Т И К А · С А М О П Р О В Е Р К А
Как убедиться, что фильтр отобрал нужное
Промежуточный результат — список подходящих кодов — невидим и никем не проверяется, кроме вас самих.
full = (movement
.merge(products, left_on='товар_id', right_on='код')
.merge(stores,
left_on='магазин_id', right_on='код'))
full['выручка'] = full['количество'] * full['цена']
mask = ((full['категория'] == 'шоколад') &
(full['район'] == 'Центральный') &
(full['дата'] >= '06.01.2026') & (full['дата'] <= '06.06.2026'))
print('Выручка:', full.loc[mask, 'выручка'].sum())
# 10695
Правило: считайте ключевую величину двумя независимыми способами (вручную и формулой) — расхождение почти всегда означает забытый
критерий.
17

18.

ИТОГИ ЗАНЯТИЯ
Лайфхаки и чек-лист
★
Лайфхаки
Сначала код, потом фильтр — переводите название
в код
Выручка — это произведение, а не готовое поле
Считайте уникальные значения, а не строки
Явно выписывайте промежуточные списки кодов
Разложите критерии по таблицам, прежде чем
фильтровать
Проверяйте величину двумя независимыми
способами
✓
Чек-лист перед сдачей
☐ Все критерии условия выделены по отдельности
☐ Каждый критерий сопоставлен с таблицей и полем
☐ Названия и категории переведены в коды
☐ Границы дат учтены буквально
☐ Понятно: записи, сумма или уникальные значения
☐ Величина перепроверена вторым способом
18

19.

СЛЕДУЮЩЕЕ ЗАНЯТИЕ
Задание 4
Кодирование и декодирование информации. Условие Фано
Снова список объектов с уникальным соответствием — только теперь это не коды строк таблицы, а коды
символов алфавита; то же внимание к однозначности снова окажется решающим.
Спасибо за внимание!
19