- •Введение
- •1. Основные понятия и определения
- •2. Модели субд
- •3. Реляционный подход
- •3.1. Реляционная структура данных
- •Степень
- •3.2. Реляционная алгебра
- •4. Проектирование реляционных бд
- •4.1. Системный анализ предметной области
- •В случае выдачи экземпляра книги читателю в библиотеке хранится специальный вкладыш, в котором должны быть записаны следующие сведения: номер билета читателя, даты выдачи и возврата книги.
- •4.2. Инфологическое проектирование
- •Читатели
- •Связана
- •Расписание
- •Нормализованное расписание
- •4.5. Выбор субд
- •4.5.1. Архитектура ms Access
- •4.5.2. Создание таблиц
- •Сотрудники
- •4.5.3. Создание формы
- •4.5.4. Запросы
- •Вычисляемые поля
- •Перекрестные запросы
- •Многотабличные запросы
- •Итоговые запросы
- •Итоговые функции
- •Аргумент distingctrow команды select
- •4.5.5. Отчеты
- •Продажи
- •4.5.6. Построение макросов
- •Литература
- •Варианты заданий для выполнения лабораторных работ Задание 1
- •Задание 2
- •Задание 3
- •Задание 4
- •Задание 5
- •Задание 6
- •Задание 7
- •Задание 8
- •Задание 9
- •Задание 10
- •Задание 11
- •Задание 12
- •Задание 13
- •Задание 14
- •Задание 15
- •Задание 16
- •Задание 17
- •Задание 18
- •Задание 19
Перекрестные запросы
Этот тип запросов рассмотрим на примере БД «Канцелярские товары» (табл. 4.9).
Таблица 4.9
БД «Канцелярские товары»
-
Товар
Месяц
Количество
Продано
Продавец
Ручка
Май
1000
500
Иванов
Линейка
Апрель
1000
800
Магазин №2
Линейка
Май
500
500
Магазин №10
Тетрадь
Май
1200
700
Магазин №36
Ручка
Февраль
800
750
Петров
Ручка
Март
2000
1800
Магазин №10
Тетрадь
Апрель
1200
1000
Иванов
Access поддерживает специальный тип итоговых запросов, называемый перекрестным запросом, который позволяет увидеть вычисляемые значения в виде перекрестной таблицы (табл. 4.10).
Таблица 4.10
Перекрестная таблица к БД «Канцелярские товары»
Товар |
Продано |
||||
Февраль |
Март |
Апрель |
Май |
Сумма |
|
Ручка |
750 |
1800 |
|
500 |
3800 |
Линейка |
|
|
800 |
500 |
1500 |
Тетрадь |
|
|
100 |
700 |
2400 |
Для построения перекрестного запроса необходимо:
выбрать в качестве источника нужную таблицу в окне БД, затем активизировать кнопку «Новый запрос» на панели инструментов;
в открывшемся диалоговом окне «Создание запроса» выбрать «Запрос», затем выбрать тип запроса «Перекрестный». Access добавит в бланк QBE новую строку «Перекрестная таблица». В этой строке для любого поля перекрестного запроса может быть выбрана одна из четырех установок:
а) заголовки строк,
б) заголовки столбцов,
в) значение (выводимое в сетке перекрестного запроса),
г) не выводить.
Для перекрестного запроса должно быть определено по крайне мере одно поле в качестве заголовка строки, одно поле в качестве заголовка столбца и одно поле значений. Любое поле, являющееся заголовком строки или столбца, должно иметь в строке «Групповая операция» установку «Группировка». Для поля, которое в бланке QBE имеет установку «Значение» (рис. 20), необходимо выбрать одну из групповых функций (sum, max и т.п.).
Рис. 20. Бланк перекрестного запроса
Многотабличные запросы
Рассмотрим алгоритм построения многотабличные запросы на примере.
Пример. Даны табл. 4.11 «Изделие» и табл. 4.12 «Поставщик».
Таблица 4.11
Изделие
-
Изделие
Цена
Цвет
Болт
2 р
Синий
Винт
1 р
Зеленый
Гайка
0,50
Красный
Таблица 4.12
Поставщик
-
Изделие
Поставщик
Количество
Болт
Иванов
500
Винт
Петров
1000
Шуруп
Сидоров
1500
Гайка
Антонов
1000
1. Установить связь между таблицами. Для этого выбираем вкладку «Запрос» «Создать» «Новый запрос». Access открывает диалоговое окно «Добавление таблицы», позволяющее выбрать таблицы и запросы, связанные между собой и будут базовыми для нового запроса.
2. Если правильно определена связь между табл. 4.11 и 4.12, верхняя панель запроса будет выглядеть следующим образом: обе таблицы будут соединены линией «∞ - 1», поскольку Access сам связывает таблицы по одинаковым полям.
3. В бланк QBE включаются поля из табл. 4.11 «Изделие» и поля из табл. 4.12 «Поставщик», которые будут нужны пользователю для создания общего запроса (рис. 21).
Рис. 21. Конструктор многотабличного запроса
Результат многотабличного запроса
-
Цена
Изделие
Поставщик
Количество
Цвет
2,00 руб
Болт
Иванов
500
Синий
1,00 руб
Винт
Петров
1000
Зеленый
0,50 руб
Гайка
Антонов
1000
Красный
Результат многотабличного запроса содержит данные тех изделий, которые входят в таблицу «Изделие» и не входят в таблицу «Поставщик». Такой тип многотабличных запросов называется симметричным объединением.
Симметричное объединение – объединение нескольких таблиц, которые в общей таблице содержат только те записи, которые пересекаются (являются общими) по какому-либо атрибуту во всех связанных таблицах. В рассмотренном примере, общий атрибут – изделие, а записи, соответствующие общему атрибуту – это болт, винт и гайка.
Если необходимо увидеть те записи, которые не вошли в запрос (в данном примере – шуруп), то для этого строят внешнее объединение.
Внешнее объединение – объединение нескольких таблиц не по общему атрибуту, а по желанию пользователя.
Для создания внешнего объединения нужно изменить параметры объединения:
открыть уже созданный запрос в режиме конструктора;
дважды щелкнуть мышью по линии связи таблиц в верхней панели окна запроса;
открыть диалоговое окно «Параметры объединения», в котором по умолчанию устанавливается параметр № 1. Для пользователя имеются еще 2 параметра (рис. 22);
для выведения в запросе дополнительно изделия шуруп необходимо выбрать параметр № 2. Тогда стрелка линии, связывающая обе таблицы, будет направлена от «Поставщика» к «Изделию» и будет показывать внешнее объединение (рис. 23), хотя в результирующей таблице не будет цены и цвета у шурупа, т.е. эти поля будут пустыми.
Рис. 22. Параметры объединения
Рис. 23. Внешнее объединение
Результат внешнего объединения
-
Цена
Изделие
Поставщик
Количество
Цвет
2,00 руб
Болт
Иванов
500
Синий
1,00 руб
Винт
Петров
1000
Зеленый
0,50 руб
Гайка
Антонов
1000
Красный
Сидоров
1500
В многотабличном запросе также можно задавать различные условия отбора с помощью операторов <, >, <>, Like, In, Between и т.п., групповых операторов sum, max, min, avg и т.п., строить перекрестные запросы. В этом случае все операторы и групповые операции будут выполняться для всех связанных таблиц, как для одной объединенной таблицы по общему атрибуту или объединенной внешними связями.