Запросы выборки
При сохранении запросов рекомендуется присваивать им содержательные имена, которые указывали бы на смысл запроса.
Вывод всех полей
Задание 4. Сформируйте запрос-выборку всех полей таблицы «Учёба» БД УЧЕБА:
1) перенесите мышью в строку «Поле» таблицы запроса имена всех полей из списка в верхней части таблицы;
2) включите щелчком мыши по квадратикам в строке «Вывод на экран» режим просмотра соответствующих полей – спецификация запроса примет вид таблицы, приведенной на рис. 6;
3) завершите результаты формирования запроса так. Результаты запроса представлены на рис. 7;
4) перейдите в SQL-режим по команде Вид | Режим SQL, чтобы получить представление о структуре SQL-запроса;
5) сохраните запрос под именем «Все поля».
Рис. 6. Структура QBE - запроса «Все поля»
Рис. 7. Результаты запроса «Все поля»
Выборка полей
В структуре запроса могут быть представлены не все поля, а только некоторые (вертикальная выборка). В результате в итоговой таблице могут присутствовать дубли строк. Это происходит по понятной причине: строки-дубли образуются потому, что поля, по которым они различались в исходной таблице, остались за пределами выборки.
Задание 5. Сформируйте запрос-выборку некоторых полей таблицы «Учёба» БД УЧЕБА.
1) сформируйте запрос, в котором присутствовали бы следующие поля: «номер студента», «фамилия», «специальность», «факультет» (технология построения запроса такая же, как в примере «Все поля», отличается только количеством полей);
2) просмотрите результаты запроса. Обратите внимание на присутствие в итоговой таблице дублирующих записей;
3) назовите запрос «Дубли».
Выборка полей без дублей записей (проекция)
Дубли записей в таблице являются её недостатком потому, что не несут никакой полезной информации. Избавиться от дублей можно, если настроить свойство запроса, которое запрещает совпадение значений данных в выбранных полях. Итоговая таблица по такому запросу представляет собой результат реляционной операции проекции исходной таблицы на выбранные поля-атрибуты.
Задание 6. Создайте запрос на выполнение проекции исходной таблицы:
1) сформируйте запрос, в котором присутствовали бы следующие поля: «номер студента», «фамилия», «специальность», «факультет» (технология построения запроса такая же, как в предыдущем примере);
2) в режиме Конструктора выполните команду Вид | Свойства;
3) в диалоговом окне «Свойства запроса» переключите мышью свойство
«Уникальные значения» в состояние «Да» (рис. 8);
Рис. 8. Спецификация свойств запроса «без дублей записей»
4) завершите результаты формирования запроса;
5) назовите запрос «Проекция».
Запросы на основе операции селекции
Очень распространенным типом запросов являются запросы выборки записей-строк по условию (горизонтальная выборка). Итоговая таблица в таком случае представляет собой результат реляционной операции селекция.
Условия отбора записываются в строке «Условие отбора» структуры запроса и могут состоять из литералов, констант и идентификаторов, соединенных операторами. Условия отбора можно записывать «по памяти» или при помощи специального инструмента, так называемого Построителя выражений.
Доступ к этому средству осуществляется в контексте построения условия при нажатии правой клавиши мыши и выборе пункта Построить контекстного меню (рис. 9).
Рис. 9. Окно построителя выражений условий
Выборка по значению поля
Задание 7. Для выбора всех данных о студентах, обучающихся на факультете «экономический»:
1) выберите все поля в структуру запроса, как в примере «Все поля»;
2) в поле «факультет» в строке «Условие отбора» наберите вручную значение «экономический» (рис. 10);
3) завершите результаты формирования запроса;
4) назовите запрос «Селекция».
Рис. 10. Структура запроса-выборки по значению поля
Параметрический запрос
Значение поля в условие запроса можно вводить не только непосредственно в шаблон структуры запроса, как в предыдущем примере, но и динамически в диалоге в процессе выполнения запроса. Для этой цели используется параметрический запрос.
Задание 8. Сформируйте параметрический запрос всех данных о результатах сдачи экзаменов по предмету, заданному его названием:
1) выберите все поля в структуру запроса, как в примере «Все поля»;
2) в поле «предмет» в строке «Условие отбора» наберите в квадратных скобках текст подсказки к вводу конкретного значения (рис. 11);
Рис. 11. Структура параметрического запроса
3) завершите результаты формирования запроса;
4) в поле диалогового окна, которое появится при выполнении запроса, введите конкретное значение, например «математика» (рис. 12);
5) сохраните запрос под именем «Параметрический».
Рис. 12. Диалоговое окно параметрического запроса
Выборка по условию с операторами сравнения
В спецификации условий выборки можно использовать операторы сравнения (=, <>, >, <, >=, >=). Для этого в шаблоне структуры запроса в строке «Условие запроса» в соответствующем поле указывается оператор сравнения.
Задание 9. Сформируйте запрос на выборку всех данных о студентах-младшекурсниках (1–3 курсы) может быть формализован условием «<7» по полю «семестр»:
1) выберите все поля в структуру запроса, как в примере «Все поля»;
2) в поле «семестр» в строке «Условие отбора» наберите «<7»;
3) завершите результаты формирования запроса;
4) назовите запрос «Меньше».
Выборка по условию с логическими операторами
В спецификации условий выборки можно использовать логические операторы (Not, And, Or и т. д.). Речь идет о реализации сложных условий запроса. Для спецификации условия с And операнды логического выражения должны располагаться в строке «Условие запроса» на одном уровне в шаблоне структуры запроса. Для спецификации условия с Or операнды логического выражения должны располагаться в строке «Условие запроса» и последующих строках «или» на разных уровнях в шаблоне структуры запроса.
Задание 10. Выполните пример на тему And. Сформируйте запрос на выборку всех данных о результатах сдачи экзаменов студентами факультета «Технологический» на оценки 4 и 5 (т.е. более 3).
1) перенесите все поля в структуру запроса, как в примере «Все поля»;
2) в поле «факультет» в строке «Условие отбора» наберите «Технологический», а в поле «оценка» в той же строке наберите «>3» (рис. 13);
3) завершите результаты формирования запроса;
4) назовите запрос «And».
Рис. 13. Структура запроса с And
Задание 11. Выполните пример на тему Or. Сформируйте запрос на выборку всех данных о результатах сдачи экзаменов студентами, которые или учатся на факультете «Экономический», или получили оценки 4 и 5:
1) перенесите все поля в структуру запроса, как в примере «Все поля»;
2) в поле «факультет» в строке «Условие отбора» наберите «Экономический», а в поле «оценка» в строке или наберите «>3»;
3) завершите результаты формирования запроса;
4) назовите запрос «Or».
Выборка по условию с оператором Between...And
Оператор Between определяет принадлежность значения поля таблицы указанному диапазону значений и имеет следующий синтаксис применения:
Between «значение1» And «значение2», здесь «значение1» и «значение2» – выражения, задающие границы диапазона. Порядок следования операндов «значение1» и «значение2» значения не имеет.
Если значение поля попадает в диапазон, задаваемый выражениями «значение1» и «значение2» (включительно), оператор Between...And возвращает значение «истина»; в противном случае возвращается значение «ложь». Допускается проверка с помощью логического оператора Not обратного условия (т.е. того, что выражение не принадлежит диапазону значение1 – значение2).
Задание 12. Сформируйте запрос на выборку всех данных о результатах сдачи экзаменов студентами, которые сдали экзамены на 4 или 5 (Between 4 And 5):
1) перенесите все поля в структуру запроса, как в примере «Все поля»;
2) в поле «оценка» в строке «Условие отбора» наберите Between 4 And 5 (рис. 14);
3) завершите результаты формирования запроса так, как это обсуждалось в алгоритме формирования запроса на стр. 18;
4) назовите запрос «Between».
Рис. 14. Структура запроса с Between
Границы диапазона можно задать параметрически по образцу Between [от] And [до].
Выборка по условию с оператором Like
Оператор Like имеет синтаксис Like «шаблон» и используется для поиска в поле текстового типа значений, совпадающих с указанным образцом. Допускается как точное указание аргумента «шаблон» (например, Like «Иванов И.И.»), так и использование поддерживаемых операционной системой символов шаблона, определяющих диапазон значений (например, Like «И∗»).
При подстановке в выражение оператор Like позволяет сравнивать значение поля со строковым значением. Например, при вводе в строку «Условие отбора» таблицы запроса выражения Like «И∗» в запросе будут выведены все поля, содержимое которых начинается с буквы «И».
Контрольное задание 13. Сформируйте запрос на выборку всех данных о результатах сдачи экзаменов студентами, чьи фамилии заканчиваются на «ов» (Петров, Иванов и т.д.):
1) перенесите все поля в структуру запроса, как в примере «Все поля»;
2) в поле «фамилия» в строке «Условие отбора» наберите Like «∗ов *» (после «ов» пробел);
3) завершите результаты формирования запроса;
4) назовите запрос «Like».
Запрос с Like, как любой запрос, может быть задан параметрически.
В запросе с параметром допускается вывод для пользователя приглашения ввести образец поиска.
Задание 14. Сформируйте параметрический запрос на выборку всех данных о студентах, чьи фамилии начинаются с первых букв (одной, двух, сколько нужно по контексту поиска), задаваемых в диалоге:
1) перенесите все поля в структуру запроса, как в примере «Все поля»;
2) в поле «фамилия» в строке «Условие отбора» наберите Like [Введите первые буквы фамилии:]&«∗» (рис. 15);
3) завершите результаты формирования запроса.
При выполнении запроса будет открыто окно диалога с надписью над полем ввода «Введите первые буквы фамилии». Если пользователь введет в поле буквы «Ив», то в запросе будет выполняться поиск по шаблону «Ив∗» (т.е. будут найдены все фамилии, начинающиеся с букв «Ив»);
назовите запрос «Like параметрический».
Рис. 15. Структура запроса с Like
Выборка по условию с оператором In
Оператор In имеет синтаксис In(«значение1»;«значение2»; ...) и проверяет значение поля таблицы на совпадение с любым из элементов указанного списка значений. Если содержимое поля совпадает со значением одного из элементов списка, оператор In возвращает значение «истина»; в противном случае возвращается «ложь». Допускается проверка с помощью логического оператора Not обратного условия (т.е. того, что выражение не принадлежит списку значений).
Задание 15. Сформируйте запрос на выборку всех данных о результатах сдачи экзаменов по базовым дисциплинам (физика, математика и др.):
1) перенесите все поля в структуру запроса, как в примере «Все поля»;
2) в поле «специальность» в строке «Условие отбора» наберите In («физика»;«математика») (рис. 16);
3) завершите результаты формирования запроса;
4) сохраните запрос под именем «In».
Рис. 16. Структура запроса с In
Выборка с вычисляемыми полями
В структуре запроса наряду с полями реальных таблиц могут быть представлены данные, которые отсутствуют в реальных таблицах, но могут быть выведены из хранимых данных при помощи арифметических, логических и других операторов, а также встроенных функций. Типичными примерами такого рода данных являются вычисляемые (виртуальные) и агрегированные данные (количество, сумма, среднее, минимальное, максимальное значения и др.).
Задание 16. Сформируйте запрос-выборку с вычисляемыми данными из БД УЧЕБА, представленной одной таблицей «Учёба». Допустим, что по условиям функционирования нашей модельной ПО «Учёба» две первые цифры номера студента указывают на год его зачисления в вуз. Допустим также, что датой зачисления студента является 1 сентября года приема. Добавим в структуру запроса на выборку всех данных после поля «номер студента» вычисляемое поле «дата зачисления», построенное по следующей формуле:
DateValue(«20»+Mid([Учеба]![номер студента];1;2)+«.01.09»)
Здесь функция DateValue(«строка символов») преобразует строковое представление даты в данное типа дата.
Функция Mid(«строка символов»; «начало»; «длина») вырезает из «строки символов», начиная с позиции «начало» (целое число), подстроку символов длиной «длина» (целое число) символов.
Оператор + выполняет сцепление (конкатенацию) строк символов.
Построение формулы для вычисления виртуального поля удобно производить под управлением Построителя выражений.
Сформируйте запрос по следующему алгоритму:
1) перенесите все поля в структуру запроса, как в примере «Все поля»;
2) для добавления нового поля выделите поле, слева от которого будет выполнена вставка. В нашем случае это поле «фамилия». (Для выбора столбца следует поместить курсор в любую его ячейку или нажать кнопку в области маркировки полей в верхней части таблицы запроса);
3) выполните команду Вставка | Столбцы. (Для удаления поля следует выделить соответствующий столбец и выполнить команду Правка | Удалить);
4) в строке «Поле» наберите формулу для вычисляемого поля. Формулу можно вводить вручную или конструировать при помощи Построителя выражений (рис. 17) по принципу «снаружи внутрь»;
5) в списке «Функции» («Встроенные функции») в категории «Дата/время» найдите функцию DateValue («stringexpr»);
6) аргумент функции «stringexpr» замените на сцепление трех строк символов: «20»+ Mid («stringexpr»; «start»; «length»)+ «01.09»;
7) функцию Mid найдете в списке «Функции» («Встроенные функции») вкатегории «Текстовые»: Mid («stringexpr»; «start»; «length»);
8) аргумент «start» замените на «1» (с первой позиции), аргумент «length»– на «2» (подстрока длиной 2 символа);
9) аргумент «stringexpr» найдите в списке «Таблицы» («Учеба», поле «номер студента») [Учеба]![номер студента];
Рис. 17. Построение выражения для вычисляемого поля
10) в таблице результата запроса новое поле будет иметь имя «Выражение1». Чтобы присвоить ему содержательное имя, установите курсор в ячейку «Поле» и нажмите правую клавишу мыши;
11) в контекстном меню выполните команду Свойства и введите в диалоговом окне «Свойства поля» в строку «Подпись» имя поля «дата зачисления»;
12) завершите результаты формирования запроса;
13) результат запроса будет иметь вид, как на рис. 18;
14) сохраните запрос под именем «Вычисляемое поле».
Рис. 18. Результат запроса с вычисляемым полем
Сортировка записей
Изначально записи-строки таблицы БД оказываются отсортированными по возрастанию ключа. Если по смыслу запроса требуется рассортировать записи по другому критерию, то формируют запрос на сортировку. Сортировка может выполняться по возрастанию или по убыванию значений поля. Сортировка может выполняться по нескольким полям. В этом случае порядок следования полей в структуре запроса определяет порядок сортировки (вложенная сортировка). Сначала выполняется сортировка по первому полю. В пределах внешней сортировки выполняется сортировка по второму полю и т. д.
Задание 17. Постройте запрос на сортировку результатов сдачи экзаменов в таблице «Учеба» по факультетам (по полю «факультет» по возрастанию), а в пределах факультета по специальностям (по полю «специальность» по убыванию):
1) перенесите все поля в структуру запроса, как в предыдущем примере;
2) в структуре запроса поменяйте местами поля «факультет» (первое поле) и «специальность» (второе поле);
3) в поле «факультет» в строке «Сортировка» мышью включите список способов сортировки и выберите «По возрастанию» (по алфавиту);
4) в поле «специальность» в строке «Сортировка» мышью включите список способов сортировки и выберите «По убыванию»;
5) завершите результаты формирования запроса;
6) сохраните запрос под именем «Сортировка».
Выборка с группированием данных и вычислением функций агрегации
Для просмотра интегральных характеристик данных по подмножествам записей удобно выполнять группирование данных. Для вычисления итоговых по группе характеристик (количество, сумма, среднее, минимальное, максимальное значения и др.) применяются групповые функции. Группировка данных может выполняться по нескольким полям, образуя группы и подгруппы. Так же как и в случае сортировки, порядок следования полей определяет порядок формирования групп и подгрупп. Особенность запроса на группировку данных состоит в том, что в структуре запроса можно использовать только те поля, по которым выполняется группировка, а также вычисляемые поля с функциями агрегации.
Задание 18. Допустим, что для каждого студента из БД УЧЕБА, представленной одной таблицей «Учёба», требуется подсчитать количество сданных экзаменов и средний балл по результатам сдачи всех экзаменов. Основанием для группирования является поле «номер студента» и «фамилия» (если мы хотим увидеть это поле в структуре запроса). Группировка по полю «фамилия» будет фиктивной, поскольку с каждым номером студента связана только одна фамилия. Вычисление групповых характеристик (количество и среднее значение) будет выполняться по полю «оценка».
Сформируйте запрос на выборку с группированием данных и вычислением групповых характеристик:
1) перенесите в структуру запроса поля «номер студента», «фамилия», «оценка», «оценка»;
2) выполните команду Вид | Групповые операции – в структуре запроса появится строка «Групповая операция». Группирование данных будет выполняться по полю «номер студента»; поле «фамилия» не создаст вложенной группы, так как уникальной характеристикой студента является его учетный номер.
Поэтому значением групповой операции для этих полей будет значение по умолчанию «Группировка»;
3) в строке «Групповая операция» для первого поля «оценка» щелкните мышью и выберите из списка функцию подсчета количества Count, для второго поля – функцию вычисления среднего значения Avg (рис. 19);
4) в таблице результата запроса полям, по которым вычисляются групповые характеристики, присвойте содержательные имена;
5) установите курсор в ячейку «Поле» и нажмите правую клавишу мыши;
6) в контекстном меню выполните команду Свойства и введите в диалоговом окне «Свойства поля» в строку «Подпись» имена вычисляемых полей «количество экзаменов» и «средний балл». Результаты запроса будут иметь вид, как на рис. 20;
7) завершите результаты формирования запроса;
8) сохраните запрос под именем «Группировка».
Рис. 19. Структура запроса на группирование данных
Рис. 20. Результаты запроса на группирование данных
Лабораторная работа №3
Тема: Закрепление изученного материала
Задания для самостоятельной работы
В предлагаемых ниже вариантах задач выполнить следующие задания:
1. Создать базу данных в соответствии с вариантом.
2. Определить ключевые поля.
2. Заполнить таблицу данными. Количество записей должно быть не менее 20.
3. Продумать и выполнить следующие типы запросов-выборок:
1) проекция на подмножество однородных атрибутов;
2) параметрический запрос на примере селекции по условию на основе операций сравнения (равно, не равно, больше, меньше, больше или равно, меньше или равно);
3) запрос-выборка, селекция по сложному условию на основе оператора конъюнкции And;
4) запрос-выборка, селекция по сложному условию на основе оператора дизъюнкции Or;
5) запрос-выборка, селекция по условию на основе оператора Between…And;
6) запрос-выборка, селекция по условию на основе оператора Like;
7) запрос-выборка, селекция по условию на основе оператора In;
8) запрос-выборка, содержащий вычисляемое поле;
9) запрос-выборка с сортировкой по двум-трем полям;
10) запрос-выборка с группировкой данных и вычислением статистических функций Count, Sum, Avg, Min, Max и др.
Самостоятельные упражнения выполнить на основе одного из нижеследующих примеров. Номер варианта выбирается по последней цифре в зачетке. При создании таблиц описание свойств полей задать самостоятельно. Предусмотреть соответствующие маски ввода.
По данной лабораторной работе сформировать отчет с подробным описанием всех действий.
Вариант I. База данных Поставки (номер поставщика, фамилия поставщика, город, код детали, город, код детали, количество за месяц).
Вариант II. База данных Больница (номер пациента, фамилия пациента, адрес, номер хирурга, фамилия хирурга, дата операции, тип операции).
Вариант III. База данных Библиотека (номер абонента, фамилия абонента, номер книги, автор, название, год издания, дата выдачи, дата возврата).
Вариант IV. База данных Аренда (номер клиента, фамилия клиента, номер объекта, плата, фамилия собственника, дата начала, дата конца).
Вариант V. База данных Кадры (номер сотрудника, фамилия сотрудника, дата рождения, место работы, должность, прием, увольнение, учебное заведение, образование)
Лабораторная работа №4. ПРОБЛЕМЫ МАНИПУЛИРОВАНИЯ ДАННЫМИ И ЗАПРОСЫ НА СОЗДАНИЕ ТАБЛИЦ
Проблемы выполнения корректирующих запросов
Корректирующие запросы на добавление, обновление или удаление данных приводят к изменению содержания базы данных. Если они выполняются некорректно, то в БД будет храниться противоречивая информация.
При выполнении корректирующих запросов могут встретиться проблемы, которые являются следствием недостатков структуры данных «универсального» отношения. Это так называемые аномалии добавления, обновления и удаления. Такими дефектами обладает и таблица «Учеба».
Например, нельзя ввести в таблицу данные о зачисленном в вуз студенте до тех пор, пока он не сдаст первый экзамен и не будут определены ключевые атрибуты «предмет» и «семестр». Это пример аномалии добавления. Другой пример: при изменении фамилии студента необходимо обновить поле «фамилия» не один, а много раз во всех строках-записях, содержащих информацию о результатах сдачи экзаменов этим студентом. При этом есть вероятность ошибочного ввода в разные строки разных данных. Это пример аномалии обновления. А при удалении единственной строки, содержащей информацию о сдаче экзаменов студентом, приведет к потере информации и о самом студенте. Это пример аномалии удаления.
Аномалии манипулирования данными являются следствием избыточного дублирования данных, присущего «универсальному» отношению. Дело в том, что в «универсальном» отношении присутствуют семантически разнородные данные и попытки манипулирования данными одного рода влекут за собой проблемы в отношении данных другого рода.
Решением проблемы аномалии манипулирования данными является корректная декомпозиция «универсального» отношения на его проекции (подмножества, содержащие семантически однородные данные).
В реляционной алгебре операция построения из одной таблицы- отношения другой, являющейся её подмножеством, называется проекцией исходной таблицы на подмножество её столбцов-атрибутов.
Запрос на создание таблицы
Запрос на создание новой таблицы как проекции исходной рассмотрим в контексте проблемы декомпозиции «универсального» отношения. Выполним декомпозицию «универсального» отношения со схемой:
Учёба (номер студента, фамилия, специальность, факультет, предмет, семестр, оценка)
на ее проекции со схемами:
Кафедра (специальность, факультет)
Экзамен (номер студента, фамилия, факультет, предмет, семестр, оценка)
Рассмотрим, как в Access реализуется операция проекция–создание одной таблицы как проекции другой. Алгоритм выполнения этой процедуры состоит из трёх этапов:
1. Построение запроса-выборки столбцов-атрибутов без дублей строк (Задание 6 на стр. 21]. Тем самым создается проекция – подмножество данных исходной таблицы, которое определит структуру и наполнение новой таблицы.
2. Выполнение запроса на создание новой таблицы.
3. Определение ключа вновь созданной таблицы .
Задание 1. На основе исходной таблицы «Учёба» со схемой отношения:
Учёба (номер студента, фамилия, специальность, факультет, предмет, семестр, оценка)
создайте две новые таблицы со схемами:
Кафедра (специальность, факультет)
Экзамен (номер студента, фамилия, факультет, предмет, семестр, оценка)
Алгоритм построения и выполнения запроса на создание новой таблицы рассмотрим на примере отношения «Кафедра»:
1) в режиме Конструктора запросов в качестве таблицы-источника данных к запросу выберите таблицу «Учёба»;
2) сформируйте запрос-выборку, в котором присутствовали бы следующие поля таблицы «Кафедра»: специальность, факультет;
3) в режиме Конструктора выполните команду Вид | Свойства;
4) в диалоговом окне «Свойства запроса» переключите мышью свойство «Уникальные значения» в состояние «Да»;
5) завершите результаты формирования запроса (на этом первая часть алгоритма заканчивается);
6) в режиме Конструктора выполните команду Запрос | Создание таблицы;
7) в диалоговом окне «Свойства запроса» введите в поле «Таблица» имя новой таблицы «Кафедра» (рис. 21);
8) в режиме Конструктора выполните команду Запрос | Запуск. В новую таблицу будет скопировано соответствующее количество записей
9) перейдите в режим Конструктора и завершите работу с запросом (можно без сохранения структуры запроса, так как результатом запроса является созданная таблица). На этом заканчивается вторая часть алгоритма;
Рис. 21. Окно наименования новой таблицы
10) в окне БД выберите закладку Таблицы и убедитесь, что новая таблица «Кафедра» представлена в списке таблиц БД; это является признаком успешного выполнения запроса на создание таблицы;
11) выберите новую таблицу «Кафедра» в списке и нажмите экранную кнопку;
12) в режиме Конструктора таблиц в окне структуры таблицы определите ключевые атрибуты (в таблице «Кафедра» это поле «специальность»);
13) сохраните результаты спецификации новой таблицы «Кафедра» по команде Файл | Сохранить. На этом заканчивается третья часть алгоритма;
14) просмотрите в Режиме таблицы наполнение новой таблицы «Кафедра» (рис. 22);
15) по рассмотренному алгоритму создайте вторую таблицу «Экзамен».
Рис. 22 Наполнение таблицы «Кафедра»
Создание связей между таблицами
В результате декомпозиции «универсального» отношения «Учеба» мы получили представление той же самой предметной области в виде совокупности двух отношений – таблиц «Кафедра» и «Экзамен». Теперь установим связь между новыми таблицами.
Правила установления связей между таблицами в Access таковы, что разрешены только бинарные (между двумя таблицами) связи. Связи могут быть двух типов «один к одному» и «один ко многим». Причём одна таблица выступает в роли старшей (отцовской), например таблица «Кафедра» в отношении «Кафедра» : «Экзамен», а другая – в роли младшей (сыновьей), например таблица «Экзамен» в отношении «Кафедра» : «Экзамен». Однако одна и та же таблица в разных отношениях может выступать в роли старшей и младшей одновременно. Связь между таблицами устанавливается по общим полям-атрибутам (поле «факультет» в нашем случае). Связь между таблицами Access устанавливается при помощи инструмента, называемого «Схемой данных».
Задание 2. Для установления связи между таблицами «Кафедра» и «Экзамен»:
1) выполните команду Сервис | Схема данных – появится окно макета схемы данных БД;
2) в контекстном меню выполните команду Связи | Добавить таблицу;
3)
в окне «Добавление таблицы» выберите
в списке таблицу «Кафедра» и нажмите
экранную кнопку
;
4) в окне «Добавление таблицы» выберите в списке таблицу «Экзамен» и нажмите экранную кнопку ;
5) для установления связи между таблицами «Кафедра» и «Экзамен» захватите мышью и перенесите поле «факультет» таблицы «Кафедра» на поле «факультет» таблицы «Экзамен» – раскроется окно «Изменение связей» (рис. 23);
6) не настраивая свойства связи, нажмите экранную кнопку.
Схема базы данных примет вид, аналогичный представленному на рис. 24.
Рис. 23. Окно спецификации связей в схеме данных
Рис. 24. Схема данных двух таблиц
ДЕКОМПОЗИЦИЯ ОТНОШЕНИЙ И ЗАПРОСЫ НА СОЕДИНЕНИЕ ТАБЛИЦ
Корректность декомпозиции отношений
Итак, структура информации в БД одной и той же предметной области (ПО) УЧЕБА может быть представлена двумя способами:
1) в виде схемы одного так называемого «универсального» отношения (в нашем примере в виде таблицы «Учёба»);
2) в виде комбинации схем нескольких отношений (в нашем примере в виде таблиц «Кафедра» и «Экзамен»).
Эквивалентность того и другого представления обеспечивается правилами корректной декомпозиции, при которой отсутствуют искажения хранимых данных и сохраняются все неизбыточные функциональные зависимости между атрибутами. Корректность декомпозиции можно проверить, выполнив так называемую операцию естественного соединения компонентов декомпозиции. Декомпозиция считается корректной с точки зрения сохранения данных, если при помощи естественного соединения компонентов декомпозиции можно восстановить исходное отношение.
Запрос на соединение таблиц
Естественное соединение – это реляционная операция, которая порождает итоговую таблицу как соединение исходных таблиц по условию равенства однотипных (одноименных) полей-атрибутов без дублей одноименных столбцов [1–5].
Контрольное задание 3. Создайте запрос на соединение двух независимых таблиц «Кафедра» и «Экзамен» [7–10]:
1) в окне БД выберите закладку Запрос и нажмите экранную кнопку
;
2) в окне «Новый запрос» выберите вариант Конструктор;
3) в окне «Добавление таблицы» выберите по закладке источник данных к запросу: Таблицы–>Запросы–>Таблицы и Запросы; в данном случае это Таблицы;
4) выберите в списке таблицы «Кафедра» и «Экзамен» и нажмите экранную кнопку ;
5) нажмите экранную кнопку – появится основное окно
конструктора запросов в стиле QBE (рис. 5). Обратите внимание на связи между одноименными атрибутами разных таблиц в верхней части бланка запроса;
6) перенесите в структуру запроса в нижней части из разных таблиц разноименные поля (рис. 5);
7) выполните запрос по команде Запрос–>Запуск – результат запроса будет выглядеть, как на рис. 6;
8) завершите результаты формирования запроса так, как это обсуждалось ранее;
9) сохраните результат под именем «Естественное соединение».
Обратите внимание на важный результат: итоговая таблица (рис. 6) по запросу на соединение двух таблиц не совпадает с исходной таблицей «Учеба» в [6, стр. 18] по числу строк. В ней присутствуют лишние строки, которые содержат семантически некорректные данные, например о том, что студенты учатся по двум специальностям. Из полученного результата следует важный вывод: декомпозиция таблицы-отношения «Учёба» выполнена некорректно.
Рис. 5. Структура запроса на соединение таблиц «Кафедра» и «Экзамен»
Рис. 6. Результат запроса на соединение таблиц «Кафедра» и «Экзамен»
Корректная декомпозиция
Декомпозиция исходного отношения (таблицы) на его проекции считается корректной, если выполняются два условия [1–5]:
1. Сохранение исходных данных в результате естественного соединения компонентов декомпозиции.
2. Сохранение неизбыточных функциональных зависимостей между атрибутами исходного отношения.
Правила корректной декомпозиции «универсального» отношения на компоненты здесь не обсуждаются1. Однако из теории БД, занимающейся проблемой оптимизации структуры данных, следует, что для реализации корректирующих запросов на добавление, удаление и изменение данных предпочтительнее представление БД в виде не одной таблицы («универсального» отношения), а виде совокупности так называемых нормализованных таблиц-отношений. Проблемы нормализации отношений здесь не обсуждаются. Достаточно понять, что в основе нормализации таблиц-отношений лежит процедура декомпозиции ненормализованного «универсального» отношения на её нормализованные компоненты, которые объединяют семантически однородные данные.
Контрольное задание 4. Выполните декомпозицию «универсального»
отношения со схемой:
Учёба ( номер студента, фамилия, специальность, факультет,
предмет, семестр, оценка)
на ее проекции со схемами:
Кафедра (специальность, факультет)
Студент (номер студента, фамилия, специальность)
Сессия (номер студента, предмет, семестр, оценка)
Таблица «Кафедра» уже создана, поэтому:
1) создайте остальные таблицы по алгоритму, описанному на стр. 3;
2) установите связи между таблицами так, как обсуждалось на стр. 5;
3) проверьте корректность декомпозиции по новой схеме как результат
операции естественного соединения трех таблиц (см. стр. 8).
Итоговые таблицы «Студент» и «Сессия» должны выглядеть так, как
показано на рис. 7 и 8.
Схема данных нового варианта БД на основе трех таблиц должна
выглядеть, как на рис. 9.
Структура запроса на естественное соединение таблиц «Кафедра»,
«Студент» и «Сессия» представлена на рис. 10.
Результат запроса на естественное соединение таблиц «Кафедра»,
«Студент» и «Сессия» приведен на рис. 11.
Рис. 7. Наполнение таблицы «Студент»
Рис. 8. Наполнение таблицы «Сессия»
Рис. 9. Схема данных трех таблиц
Рис. 10. Структура запроса на соединение трех таблиц
Рис. 11. Результат запроса на естественное соединение трех таблиц
Обратите внимание на важнейший результат: результирующая таблица
(рис. 11) по запросу на соединение трех таблиц с точностью до порядка
следования строк и столбцов (что не существенно) совпадает с исходной
таблицей «Учёба» в [6, стр. 18], из которой новые таблицы были получены как
её проекции.
Из полученного результата следуют важные выводы:
1. Декомпозиция таблицы-отношения «Учёба» на её компоненты-
проекции «Кафедра», «Студент» и «Сессия» выполнена корректно, т. е. без
потери данных.
2. Представления предметной области (ПО) «Учёба» в виде одноименного
«универсального» отношения и в виде комбинации нормализованных отношений-
таблиц «Кафедра», «Студент» и «Сессия» эквивалентны.
Отныне наша база данных УЧЕБА будет представлена не одной таблицей
«Учеба», а комбинацией нормализованных связанных таблиц «Кафедра»,
«Студент» и «Сессия». Все простые запросы-выборки, которые были
выполнены ранее на примере одной таблицы «Учеба», теперь с равным успехом
могут быть воспроизведены на основе связанных таблиц. Рассмотрим в
качестве примера запрос на группировку данных.
Контрольное задание 5. Допустим, что для каждого студента из БД УЧЕБА,
представленной теперь тремя таблицами, требуется подсчитать количество
сданных экзаменов и средний балл по результатам сдачи всех экзаменов.
Сформируйте запрос на выборку с группированием данных из связанных таблиц
«Студент» и «Сессия» и вычислением групповых характеристик:
1) в окне БД на закладке Запрос приступите к созданию нового запроса
(нажмите экранную кнопку );
2) в окне «Новый запрос» выберите вариант Конструктор;
3) в окне «Добавление таблицы» по закладке Таблицы выделите в списке
таблицы «Студент» и «Сессия» и нажмите экранные кнопки и
;
4) в окне конструктора запросов выберите в структуру запроса поля
«номер студента», «фамилия» (из таблицы «Студент»), «оценка», «оценка»
(из таблицы «Сессия»);
5) выполните команду Вид|Групповые операции; в структуре запроса
в строке «Групповая операция» для полей «номер студента» и «фамилия»
включите настройку «Группировка»;
6) для первого поля «оценка» в строке «Групповая операция» выберите из
списка функцию подсчета количества Count, для второго поля –функцию
вычисления среднего значения Avg (рис. 12);
7) в таблице результата запроса полям, по которым вычисляются
групповые характеристики, присвойте содержательные имена «количество
экзаменов» и «средний балл»;
8) результаты запроса будут иметь вид, как на рис. 13;
9) сохраните запрос под именем «Группировка2»
Рис. 12. Структура запроса на группирование данных из связанных таблиц
Рис. 13. Результаты запроса на группирование данных из связанных таблиц
Сравните полученные результаты с результатами аналогичного запроса к одной таблице в [6, контрольное задание 18 на стр. 39]. Совпадение очевидно.
Однако следует заметить, что выборка данных из связанных таблиц, особенно если их много и они содержат большое количество строк, требует для своего выполнения больше компьютерного времени, чем выборка из одной таблицы.
Таким образом, для запросов-выборок желательно иметь все данные в одном месте, в одной таблице. Что же касается корректирующих запросов, то там ситуация прямо противоположная. Эффективность корректирующих запросов и качество структуры базы данных требуют представление БД в виде совокупности связанных таблиц. Решение проблемы требует взвешенного подхода с учетом всех «за» и «против».