2.
Практическая часть
2.1.
Постановка задачи
Результатом выполнения данной работы является создание базы
данных «Книги» в СУБД Access.
Данные таблиц базы данных и их структура должны
соответствовать приведённым выше таблицам 1 – 3.
При помощи запросов, должна быть реализована возможность
получения следующих данных:
·
сколько всего книг серии «Финансы» было продано
в течение первого полугодия 2000 года?
·
кто из продавцов продавал книги серии «Спорт» в
2001 году?
·
кто из продавцов стал лидером продаж в 2001 году?
2.2.
Создание структуры таблиц
2.2.1.
Структура таблицы Продавцы
|
|
Имя поля
|
Тип данных
|
|
B
|
КодПродавца
|
Счетчик
|
|
Продавец
|
Текстовый
|
|
Телефон
|
Текстовый
|
После создание необходимых полей в режиме Конструктора, нужно сохранить созданную
таблицу Продавцы и заполнить её
данными из Таблицы 2 (см. Теоретическую часть).
2.2.2.
Структура таблицы Серии
книг
|
|
Имя поля
|
Тип данных
|
|
B
|
КодСерии
|
Счетчик
|
|
Серия
|
Текстовый
|
После создания структуры таблицы Серии книг в неё нужно занести данные из Таблицы 1 (см. Теоретическую часть).
2.2.3.
Структура таблицы Продажи
|
|
Имя поля
|
Тип данных
|
|
B
|
КодПродажи
|
Счетчик
|
|
Дата
|
Дата/время
|
|
КодПродавца
|
Числовой
|
|
КодСерии
|
Числовой
|
|
Количество
|
Числовой
|
|
Цена
|
Денежный
|
После создания структуры таблицы Продажи в неё нужно занести данные из Таблицы 3 (см. Теоретическую часть).
2.3.
Создание связей между таблицами
Связывание нескольких таблиц в базе данных позволяет
производить выборку данных из разных таблиц.
Для связывания таблиц, необходимо открыть окно Схема данных, нажав одноименную кнопку
на панели инструментов. После этого
нужно при помощи контекстного меню
добавить в Схему данных все ранее
созданные 3 таблицы. После чего, путем перетаскивания полей из одной таблицы на
соответствующие поля другой таблицы, создать связи. При создании каждой связи,
для неё нужно установить такие параметры, как: Обеспечение целостности данных, Каскадное
обновление связанных полей, Каскадное
удаление связанных записей.
В итоге Схема данных
примет следующий вид:
2.4.
Создание запросов
Для отбора и анализа данных, хранящихся в базе данных служат
запросы. Ещё запросы позволяют
выводить необходимые данные в компактном виде, наподобие электронной таблицы.
При этом можно производить различные математические вычисления.
2.4.1.
Создание запроса для подсчёта
количества проданных книг серии «Финансы» в течение первого полугодия 2001 года
Создадим простой запрос.
Добавим в него поля из трёх ранее созданные таблиц. Поля Дата и Количество из
таблицы Продажи, поле Серия из таблицы СерииКниг, а поле Продавец
– из таблицы Продавцы.
Установим Условия
отбора для поля Дата, задающее
интервал с 1 января до 30 июня 2000 г. И Финансы
для поля Серия, позволяющее отобрать
только книга данной серии.
В Конструкторе запроса он будет выглядеть
следующим образом:
В результате выполнения созданного запроса будет выведен список
книг по финансам, проданным за первое полугодие 2000 года:
|
Количество проданных книг по финансам за 1 полугодие 2000 года
|
|
Дата
|
Серия
|
Продавец
|
Количество
|
|
05.05.2000
|
Финансы
|
Николаев
|
23
|
2.4.2.
Создание запроса для отбора
продавцов, продававших книги серии «Спорт» в 2001 году
Первоначально создадим простой
запрос, включающий поля Дата
и Количество из таблицы Продажи, поле Серия из таблицы СерииКниг и поле Продавец
из таблицы Продавцы.
Зададим в качестве условия отбора для поля Дата интервал
с 1 января 2001 г. по 31.12.2001 г., а для поля Серия - Спорт.
В результате Конструктор
запроса примет вид:
Сохраним полученный запрос присвоим ему имя «Продавцы серии
спорт в 2001 году».
Этот запрос выдаст следующий набор записей:
|
Продавцы серии Спорт в 2001 году
|
|
Дата
|
Серия
|
Продавец
|
Количество
|
|
16.05.2001
|
Спорт
|
Петров
|
25
|
|
16.05.2001
|
Спорт
|
Николаев
|
1
|
|
13.09.2001
|
Спорт
|
Николаев
|
21
|
|
14.09.2001
|
Спорт
|
Петров
|
14
|
|
18.11.2001
|
Спорт
|
Петров
|
25
|
|
15.12.2001
|
Спорт
|
Сомолов
|
15
|
|
20.12.2001
|
Спорт
|
Петров
|
27
|
По только что созданному простому
запросу создадим перекрестный запрос,
нажав кнопку Создать в главном меню Microsoft Access и выбрав Перекрестный запрос.
Первоначально запустившийся мастер требует выбрать простой запрос на основании которого и
будет строиться перекрестный запрос.
Выберем только что созданный запрос.
Для Заголовков строк
выберем поле Продавец, а в качестве Заголовков столбцов выберем поле Дата. В качестве Интервала группировки – Месяц
и для поля Количество зададим функцию
Сумма.. Сохраним полученный запрос.
В результате выполнения запроса получим следующую Сводную таблицу:
|
Продавцы серии Спорт в 2001 году_перекрестный
|
|
Продавец
|
Итоговое значение Количество
|
янв
|
фев
|
мар
|
апр
|
май
|
июн
|
июл
|
авг
|
сен
|
окт
|
ноя
|
Дек
|
|
Николаев
|
22
|
|
|
|
|
1
|
|
|
|
21
|
|
|
|
|
Петров
|
91
|
|
|
|
|
25
|
|
|
|
14
|
|
25
|
27
|
|
Сомолов
|
15
|
|
|
|
|
|
|
|
|
|
|
|
15
|
2.4.3.
Создание запроса для определения
лидера продаж в 2001 году
Создадим простой
запрос с полями Дата и Количество из таблицы Продажи и полем Продавец из таблицы Продавцы.
Зададим Условие отбора для поля Дата с 01.01.2001 по 31.12.2001. Также
зададим сортировку по убыванию для
поля Количество. В режиме Конструктора созданный запрос будет
иметь вид:
Выполнив созданный запрос, получим следующие данные:
|
Продажи в 2001 году
|
|
Дата
|
Продавец
|
Количество
|
|
07.12.2001
|
Рябов
|
36
|
|
20.12.2001
|
Петров
|
27
|
|
25.04.2001
|
Сомолов
|
26
|
|
25.04.2001
|
Рябов
|
26
|
|
25.04.2001
|
Рябов
|
26
|
|
18.11.2001
|
Петров
|
25
|
|
13.04.2001
|
Рябов
|
25
|
|
13.04.2001
|
Соловьев
|
25
|
|
16.05.2001
|
Петров
|
25
|
|
23.11.2001
|
Петров
|
23
|
|
13.09.2001
|
Николаев
|
21
|
|
28.02.2001
|
Николаев
|
21
|
|
14.12.2001
|
Сомолов
|
19
|
|
17.11.2001
|
Николаев
|
16
|
|
15.12.2001
|
Сомолов
|
15
|
|
25.04.2001
|
Рябов
|
14
|
|
14.09.2001
|
Петров
|
14
|
|
14.09.2001
|
Петров
|
12
|
|
18.05.2001
|
Петров
|
11
|
|
14.09.2001
|
Петров
|
11
|
|
20.09.2001
|
Рябов
|
11
|
|
30.11.2001
|
Николаев
|
6
|
|
06.05.2001
|
Рябов
|
3
|
|
30.11.2001
|
Николаев
|
3
|
|
15.12.2001
|
Петров
|
3
|
|
14.09.2001
|
Рябов
|
3
|
|
16.05.2001
|
Николаев
|
1
|
|
09.06.2001
|
Соловьев
|
1
|
Теперь на основе созданного простого запроса создадим перекрестный
запрос. Выберем его в качестве источника.
В качестве заголовков
строк выберем поле Продавец, а в
качестве Заголовков столбцов выберем
поле Дата. Как интервал группировки зададим месяц.
Для поля Количество зададим функцию Сумма.
Созданный запрос выдаст следующий результат:
|
Продажи в 2001 году_перекрестный
|
|
Продавец
|
Итоговое значение Количество
|
янв
|
фев
|
мар
|
Апр
|
май
|
июн
|
июл
|
авг
|
сен
|
окт
|
ноя
|
дек
|
|
Николаев
|
68
|
|
21
|
|
|
1
|
|
|
|
21
|
|
25
|
|
|
Петров
|
151
|
|
|
|
|
36
|
|
|
|
37
|
|
48
|
30
|
|
Рябов
|
144
|
|
|
|
91
|
3
|
|
|
|
14
|
|
|
36
|
|
Соловьев
|
26
|
|
|
|
25
|
|
1
|
|
|
|
|
|
|
|
Сомолов
|
60
|
|
|
|
26
|
|
|
|
|
|
|
|
34
|
Для того, чтобы упорядочить продавцов в порядке убывания проданных книг, создадим простой запрос по только что созданному перекрестному запросу. Добавив в него
все поля. Зададим Сортировку по убыванию
для поля Итоговое значение. В режиме Конструктора созданный запрос имеет вид:
Полученный запрос выведет упорядоченные по убыванию итоговые
данные по Количеству продаж:
|
Продажи в 2001 году_по_убыванию
|
|
Продавец
|
Итоговое значение Количество
|
янв
|
фев
|
мар
|
Апр
|
май
|
июн
|
июл
|
авг
|
сен
|
окт
|
ноя
|
дек
|
|
Петров
|
151
|
|
|
|
|
36
|
|
|
|
37
|
|
48
|
30
|
|
Рябов
|
144
|
|
|
|
91
|
3
|
|
|
|
14
|
|
|
36
|
|
Николаев
|
68
|
|
21
|
|
|
1
|
|
|
|
21
|
|
25
|
|
|
Сомолов
|
60
|
|
|
|
26
|
|
|
|
|
|
|
|
34
|
|
Соловьев
|
26
|
|
|
|
25
|
|
1
|
|
|
|
|
|
|