Задание 3
тема 1 из 1

Реляционные базы данных

Реляционная база данных хранит сведения в нескольких таблицах, которые связаны через ключевые поля. В задании 3 нужные записи сначала ищут в справочных таблицах, затем отбирают фильтром в главной таблице по их ключам, датам и типу операции. Итог считают по отобранным строкам: сумму, массу через количество в упаковке или разность поступлений и продаж.

Главная тема в задании3На карте навыков

Как искать данные в связанных таблицах

Таблица состоит из записей (строк) и полей (столбцов). Ключ таблицы однозначно определяет запись, а ключ другой таблицы в поле записи связывает две таблицы.

  • Прочитайте вопрос и выпишите условия: что считать, за какие даты, для каких объектов, какой тип операции.
  • В справочных таблицах (магазины, товары) найдите записи, которые подходят под условия, и выпишите их ключи.
  • В главной таблице включите автофильтр и оставьте строки с этими ключами, нужными датами и типом операции.
  • Недостающие сведения о товаре перенесите в главную таблицу функцией ВПР по ключу.
  • Посчитайте итог по видимым строкам: ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9; …) в Excel, ИТОГ(9; …) в LibreOffice Calc.

Таблица, запись и поле

Реляционная база данных — набор таблиц, каждая из которых хранит сведения об однотипных объектах: магазинах, товарах, операциях. Запись — строка таблицы, сведения об одном объекте. Поле — столбец таблицы, одно свойство всех объектов. У поля есть имя (заголовок столбца) и тип: число, текст, дата.

В задании 3 база дана файлом электронной таблицы: каждая таблица базы лежит на своём листе, первая строка листа — заголовки полей. Открыть его можно в Excel или в LibreOffice Calc.

Пример

Таблица «Магазин» имеет поля «ID магазина», «Район», «Адрес». В ней записи: M1 — Центральный, просп. Мира, 45; M2 — Заречный, ул. Лесная, 3; M3 — Центральный, ул. Садовая, 8. Сколько в таблице записей и полей? Какие магазины стоят в Центральном районе?

  1. 1.Каждый магазин — одна запись: записей 3.
  2. 2.Каждое свойство — одно поле: «ID магазина», «Район», «Адрес», полей 3.
  3. 3.В поле «Район» значение «Центральный» стоит в записях M1 и M3.

Ответ: 3 записи, 3 поля; магазины M1 и M3

Ключ таблицы

Ключ таблицы (первичный ключ) — поле, значение которого не повторяется ни в одной записи и поэтому однозначно указывает на запись. Обычно это код: «ID магазина», «Артикул». Название ключом быть не может, если два объекта могут называться одинаково.

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

Пример

Таблица «Товар» имеет поля «Артикул», «Отдел», «Наименование товара», «Количество в упаковке». Какое поле — ключ таблицы?

  1. 1.В одном отделе много товаров: значение «Отдел» повторяется.
  2. 2.Одинаковое количество в упаковке бывает у разных товаров.
  3. 3.Артикул у каждого товара свой и в таблице не повторяется.

Ответ: «Артикул»

Внешний ключ и связь таблиц

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

Один магазин встречается во многих операциях, а у каждой операции один магазин: это связь один-ко-многим. Связь один-к-одному соединяет одну запись с одной записью. Связь многие-ко-многим (одна ткань идёт на много моделей, одна модель шьётся из многих тканей) задают через третью таблицу, в которой есть оба внешних ключа.

Пример

Таблица «Магазин»: M1 — Центральный, просп. Мира, 45; M2 — Заречный, ул. Лесная, 3. Таблица «Движение товаров»: операция 1 — магазин M2, артикул 5, 40 упаковок, «Поступление»; операция 2 — магазин M1, артикул 5, 12 упаковок, «Продажа». В каком районе получили товар в операции 1?

  1. 1.В записи операции 1 поле «ID магазина» равно M2: это внешний ключ.
  2. 2.Ищем M2 в ключевом поле таблицы «Магазин»: Заречный район, ул. Лесная, 3.

Ответ: Заречный

Отбор записей фильтром

Фильтр скрывает строки таблицы, которые не подходят под условие, и оставляет видимыми нужные. Включите его: «Данные» → «Фильтр» в Excel, «Данные» → «Автофильтр» в LibreOffice Calc (в обеих программах Ctrl+Shift+L). В заголовке каждого столбца появится стрелка со списком значений: отметьте нужные. Условия в разных столбцах действуют вместе, как логическое И. Несколько отмеченных значений одного столбца — это ИЛИ.

Условие «адрес содержит слово», «дата между» задают в «Текстовых фильтрах» и «Фильтрах по дате» в Excel, в «Стандартном фильтре» в LibreOffice. Слово «включительно» значит, что обе границы входят в период. Тот же список под стрелкой сортирует столбец по возрастанию или убыванию.

Пример

Операции магазина M1 в июне: 3-го — поступление 20 упаковок; 4-го — продажа 5; 6-го — поступление 30; 9-го — поступление 10; 10-го — продажа 8. Сколько упаковок поступило с 4 по 9 июня включительно?

  1. 1.Фильтр по типу операции: «Поступление». Остаются операции 3, 6 и 9 июня.
  2. 2.Фильтр по дате с 4 по 9 июня включительно: 3 июня не входит, 6 и 9 июня входят.
  3. 3.Складываем видимые строки: 30+10=4030 + 10 = 40.

Ответ: 40

Сведения из связанной таблицы: функция ВПР

Если условие или расчёт требует поля другой таблицы, перенесите это поле в главную таблицу. Функция ВПР(Критерий; Массив; Индекс; 0) ищет значение критерия в первом столбце массива и возвращает значение из столбца с номером «Индекс» той же строки. Последний аргумент 0 требует точного совпадения. Массив закрепляют знаками доллара, чтобы он не сдвигался при копировании: абсолютные ссылки.

Ссылку на другой лист программа вставит сама, если выделить диапазон мышью. Затем клавиша F4 добавит знаки доллара: Товар!$A$2:$F$61 в Excel, $Товар.$A$2:$F$61 в LibreOffice. Столбец, где значение считают формулой по другим полям, называют вычисляемым полем: масса партии =E2*G2 — упаковки, умноженные на граммы в упаковке.

Пример

На листе «Товар» в столбцах A–F лежат поля «Артикул», «Отдел», «Наименование товара», «Ед_изм», «Количество в упаковке», «Цена за упаковку»; у артикула 5 количество в упаковке 800 г. В строке 2 листа «Движение товаров» артикул 5 (ячейка D2) и 15 упаковок (E2). Найдите массу партии.

  1. 1.«Количество в упаковке» — пятый столбец массива, индекс 5.
  2. 2.В G2 пишем =ВПР(D2;Товар!$A$2:$F$61;5;0): формула находит артикул 5 и возвращает 800.
  3. 3.В H2 пишем =E2*G2: 15⋅800=12 00015 \cdot 800 = 12\,000 г. Формулы копируют вниз на все строки.
  4. 4.12 00012\,000 г — это 12 кг.

Ответ: 12 кг

Итог по отобранным записям

Когда фильтр оставил нужные строки, сложите числа вычисляемого столбца только в видимых строках. Функция СУММ складывает весь диапазон, вместе со строками, которые фильтр скрыл. Сумму видимых строк даёт ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;H2:H6481) в Excel и ИТОГ(9;H2:H6481) в LibreOffice Calc: код 9 означает сумму. Если ответ просят в килограммах, а масса в граммах, разделите итог на 1000.

Пример задания

Открытый банк ФИПИ

В файле приведён фрагмент базы данных «Кондитерские изделия» о поставках конфет и печенья в магазины районов  города. База данных состоит из трёх таблиц. 
Таблица «Движение товаров» содержит записи о поступлении товаров со склада в магазины в течение июня 2023 г., а также информацию о проданных товарах. Поле Тип операции содержит значение Поступление или Продажа, а в соответствующее поле Количество упаковок, шт. внесена информация о том, сколько упаковок товара поступило в магазин или было продано в течение дня. Заголовок таблицы имеет следующий вид:

ID операции Дата ID магазина Артикул Количество упаковок, шт. Тип операции


Таблица «Товар» содержит информацию об основных характеристиках каждого товара. Заголовок таблицы имеет следующий вид:

Артикул Отдел Наименование товара Ед. изм  Количество в упаковке Цена за упаковку


Таблица «Магазин» содержит информацию о местонахождении магазинов. Заголовок таблицы имеет следующий вид:

ID магазина Район Адрес


На рисунке приведена схема указанной базы данных:


Используя информацию из приведённой базы данных, определите общую массу (в кг) всех видов зефира, полученных магазинами, расположенными на проспекте Мира, за период с 5 по 19 июня включительно. В ответе запишите только число.

64753_3.ods

64753_3.xls

64753_3.xlsx

Изменение количества: поступления минус продажи

Вопрос «на сколько увеличилось количество товара в магазинах» спрашивает разность: сколько поступило минус сколько продали за период. Отберите строки товара и магазинов фильтром, затем найдите сумму видимых строк дважды: с типом «Поступление» и с типом «Продажа». Можно и одной формулой: вычисляемый столбец =ЕСЛИ(F2="Поступление";E2;-E2), затем сумма видимых строк. Если остаток на начало периода не дан, он не нужен: разность от него не зависит.

Пример задания

Открытый банк ФИПИ

В файле приведён фрагмент базы данных «Продукты» о поставках товаров в магазины районов города. База данных состоит из трёх таблиц.
Таблица «Движение товаров» содержит записи о поставках товаров в магазины в течение первой декады июня 2021 г., а также информацию о проданных товарах. Поле Тип операции содержит значение Поступление или Продажа, а в соответствующее поле Количество упаковок, шт. занесена информация о том, сколько упаковок товара поступило в магазин или было продано в течение дня. Заголовок таблицы имеет следующий вид.

ID операции Дата ID магазина Артикул Тип операции Количество упаковок, шт. Цена руб./шт.


Таблица «Товар» содержит информацию об основных характеристиках каждого товара. Заголовок таблицы имеет следующий вид.

Артикул Отдел Наименование товара Ед. изм. Количество в упаковке Поставщик


Таблица «Магазин» содержит информацию о местонахождении магазинов. Заголовок таблицы имеет следующий вид.

ID магазина Район Адрес


На рисунке приведена схема указанной базы данных.

Используя информацию из приведённой базы данных, определите на сколько увеличилось количество упаковок яиц диетических, имеющихся в наличии в магазинах Заречного района за период с 1 по 10 июня.
В ответе запишите только число.

52818_3.xlsx

Потренируйте навык

Все задания на этот навык

Решать