Декомпозиция отношений и запросы на соединение таблиц

Корректность декомпозиции отношений

 

Итак, структура информации в БД одной и той же предметной области (ПО) УЧЕБА может быть представлена двумя способами:

1) в виде схемы одного так называемого «универсального» отношения (в нашем примере в виде таблицы «Учёба»);

2) в виде комбинации схем нескольких отношений (в нашем примере в виде таблиц «Кафедра» и «Экзамен»).

Эквивалентность того и другого представления обеспечивается правилами корректной декомпозиции, при которой отсутствуют искажения хранимых данных и сохраняются все неизбыточные функциональные зависимости между атрибутами. Корректность декомпозиции можно проверить, выполнив так называемую операцию естественного соединения компонентов декомпозиции. Декомпозиция считается корректной с точки зрения сохранения данных, если при помощи естественного соединения компонентов декомпозиции можно восстановить исходное отношение.

Запрос на соединение таблиц

 

Естественное соединение – это реляционная операция, которая порождает итоговую таблицу как соединение исходных таблиц по условию равенства однотипных (одноименных) полей-атрибутов без дублей одноименных столбцов [1–5].

Задание 3. Создайте запрос на соединение двух независимых таблиц «Кафедра» и «Экзамен»:

1) в окне БД выберите закладку Запрос и нажмите экранную кнопку ;

2) в окне «Новый запрос» выберите вариант Конструктор;

3) в окне «Добавление таблицы» выберите по закладке источник данных к запросу: Таблицы | Запросы | Таблицы и Запросы; в данном случае это Таблицы;

4) выберите в списке таблицы «Кафедра» и «Экзамен» и нажмите экранную кнопку ;

5) нажмите экранную кнопку – появится основное окно конструктора запросов в стиле QBE (рис. 25). Обратите внимание на связи между одноименными атрибутами разных таблиц в верхней части бланка запроса;

6) перенесите в структуру запроса в нижней части из разных таблиц разноименные поля (рис. 25);

7) выполните запрос по команде Запрос | Запуск – результат запроса будет выглядеть, как на рис. 26;

8) завершите результаты формирования запроса так, как это обсуждалось ранее;

9) сохраните результат под именем «Естественное соединение».

Обратите внимание на важный результат: итоговая таблица (рис. 26) по запросу на соединение двух таблиц не совпадает с исходной таблицей «Учеба» по числу строк. В ней присутствуют лишние строки, которые содержат семантически некорректные данные, например о том, что студенты учатся по двум специальностям. Из полученного результата следует важный вывод: декомпозиция таблицы-отношения «Учёба» выполнена некорректно.

Рис. 25. Структура запроса на соединение таблиц «Кафедра» и «Экзамен»

 

Рис. 6. Результат запроса на соединение таблиц «Кафедра» и «Экзамен»

Корректная декомпозиция

 

Декомпозиция исходного отношения (таблицы) на его проекции считается корректной, если выполняются два условия:

1. Сохранение исходных данных в результате естественного соединения компонентов декомпозиции.

2. Сохранение неизбыточных функциональных зависимостей между атрибутами исходного отношения.

Из теории БД, занимающейся проблемой оптимизации структуры данных, следует, что для реализации корректирующих запросов на добавление, удаление и изменение данных предпочтительнее представление БД в виде не одной таблицы («универсального» отношения), а виде совокупности так называемых нормализованных таблиц-отношений. Проблемы нормализации отношений здесь не обсуждаются. Достаточно понять, что в основе нормализации таблиц-отношений лежит процедура декомпозиции ненормализованного «универсального» отношения на её нормализованные компоненты, которые объединяют семантически однородные данные.

Задание 4. Выполните декомпозицию «универсального» отношения со схемой:

Учёба (номер студента, фамилия, специальность, факультет,предмет, семестр, оценка)

на ее проекции со схемами:

Кафедра (специальность, факультет)

Студент (номер студента, фамилия, специальность)

Сессия (номер студента, предмет, семестр, оценка)

Таблица «Кафедра» уже создана, поэтому:

1) создайте остальные таблицы по алгоритму, описанному на стр. 26;

2) установите связи между таблицами так, как обсуждалось на стр. 28;

3) проверьте корректность декомпозиции по новой схеме как результат операции естественного соединения трех таблиц (см. стр. 29).

Итоговые таблицы «Студент» и «Сессия» должны выглядеть так, как показано на рис. 27 и 28.

Схема данных нового варианта БД на основе трех таблиц должна выглядеть, как на рис. 29.

Структура запроса на естественное соединение таблиц «Кафедра», «Студент» и «Сессия» представлена на рис. 30.

Результат запроса на естественное соединение таблиц «Кафедра», «Студент» и «Сессия» приведен на рис. 31.

Рис. 27. Наполнение таблицы «Студент»

 

Рис. 28. Наполнение таблицы «Сессия»

Рис. 29. Схема данных трех таблиц

 

Рис. 30. Структура запроса на соединение трех таблиц

Рис. 31. Результат запроса на естественное соединение трех таблиц

 

Обратите внимание на важнейший результат: результирующая таблица (рис. 11) по запросу на соединение трех таблиц с точностью до порядка следования строк и столбцов (что не существенно) совпадает с исходной таблицей «Учёба» в [6, стр. 18], из которой новые таблицы были получены как её проекции.

Из полученного результата следуют важные выводы:

1. Декомпозиция таблицы-отношения «Учёба» на её компоненты- проекции «Кафедра», «Студент» и «Сессия» выполнена корректно, т. е. Без потери данных.

2. Представления предметной области (ПО) «Учёба» в виде одноименного «универсального» отношения и в виде комбинации нормализованных отношений- таблиц «Кафедра», «Студент» и «Сессия» эквивалентны. Отныне наша база данных УЧЕБА будет представлена не одной таблицей «Учеба», а комбинацией нормализованных связанных таблиц «Кафедра», «Студент» и «Сессия». Все простые запросы-выборки, которые были выполнены ранее на примере одной таблицы «Учеба», теперь с равным успехом могут быть воспроизведены на основе связанных таблиц. Рассмотрим в качестве примера запрос на группировку данных.

 

Задание 5. Допустим, что для каждого студента из БД УЧЕБА, представленной теперь тремя таблицами, требуется подсчитать количество сданных экзаменов и средний балл по результатам сдачи всех экзаменов.

Сформируйте запрос на выборку с группированием данных из связанных таблиц «Студент» и «Сессия» и вычислением групповых характеристик:

1) в окне БД на закладке Запрос приступите к созданию нового запроса (нажмите экранную кнопку);

2) в окне «Новый запрос» выберите вариант Конструктор;

3) в окне «Добавление таблицы» по закладке Таблицы выделите в списке таблицы «Студент» и «Сессия» и нажмите экранные кнопки и;

4) в окне конструктора запросов выберите в структуру запроса поля

«номер студента», «фамилия» (из таблицы «Студент»), «оценка», «оценка» (из таблицы «Сессия»);

5) выполните команду Вид | Групповые операции; в структуре запроса в строке «Групповая операция» для полей «номер студента» и «фамилия» включите настройку «Группировка»;

6) для первого поля «оценка» в строке «Групповая операция» выберите из списка функцию подсчета количества Count, для второго поля –функцию вычисления среднего значения Avg (рис. 32);

7) в таблице результата запроса полям, по которым вычисляются групповые характеристики, присвойте содержательные имена «количество экзаменов» и «средний балл»;

8) результаты запроса будут иметь вид, как на рис. 33;

9) сохраните запрос под именем «Группировка2»

Рис. 32. Структура запроса на группирование данных из связанных таблиц

 

Рис. 33. Результаты запроса на группирование данных из связанных таблиц

 

Сравните полученные результаты с результатами аналогичного запроса к одной таблице. Совпадение очевидно.

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

Таким образом, для запросов-выборок желательно иметь все данные в одном месте, в одной таблице. Что же касается корректирующих запросов, то там ситуация прямо противоположная. Эффективность корректирующих запросов и качество структуры базы данных требуют представление БД в виде совокупности связанных таблиц. Решение проблемы требует взвешенного подхода с учетом всех «за» и «против».

 

 

КОРРЕКТИРУЮЩИЕ ЗАПРОСЫ

 

Выполнение корректирующих запросов

В результате корректной декомпозиции отношения «Учеба» мы развели семантически однородные данные по разным таблицам. Тем самым мы решили проблему аномалий манипулирования данными и создали предпосылки для корректного выполнения корректирующих запросов. Корректирующие запросы бывают трех типов:

1) запросы на добавление данных;

2) запросы на удаление данных;

3) запросы на обновление данных.

Алгоритм выполнения этих запросов состоит из двух этапов [7–10]:

1. Построение запроса-выборки строк-записей, подлежащих добавлению, удалению или обновлению. Тем самым создается подмножество корректируемых данных.

2. Выполнение корректирующего запроса.

Продемонстрируем эти типы запросов на примере таблицы «Студент».

 

Запрос на удаление

Задание 6. Создайте запрос на удаление из таблицы «Студент» данных о студенте, заданном его фамилией.

1) в качестве таблицы-источника данных к запросу выберите таблицу «Студент»;

2) сформируйте запрос-выборку всех полей по условию, задаваемому параметрически; в поле «фамилия» в строке «Условие отбора» наберите в квадратных скобках текст подсказки к вводу конкретного значения [Фамилия студента];

3) выполните запрос по команде Запрос | Запуск; в диалоговом окне, которое появится при реализации запроса, введите в кавычках конкретное значение, например «Андреев А.А.»;

4) просмотрите результаты запроса в Режиме таблицы и примите решение, удовлетворяют ли вас результаты выборки или нет;

5) вернитесь в режим Конструктора. На этом первая часть алгоритма заканчивается;

выборка данных на удаление подготовлена;

6) в режиме Конструктора выполните команду Запрос | Удаление – в бланке структуры запроса появится строка «Удаление» (рис. 34);

Рис. 34. Структура запроса на удаление данных из таблицы

7) выполните запрос по команде Запрос | Запуск;

8) завершите результаты формирования запроса. На этом заканчивается вторая часть алгоритма;

9) в окне БД на закладке Таблицы выберите таблицу «Студент» и посмотрите её наполнение. Убедитесь, что удаление записи действительно произошло.

Запрос на добавление

Задание 7. Речь идет о добавлении данных в одну таблицу из другой. Создайте запрос на добавление данных о студенте, заданном его фамилией из таблицы «Учёба» в таблицу «Студент»:

1) в качестве таблицы-источника данных выберите таблицу «Учёба»;

2) сформируйте запрос-выборку полей, которые должны быть добавлены

(«номер студента», «фамилия», «специальность») по условию, задаваемому параметрически; в поле «фамилия» в строке «Условие отбора» наберите в квадратных скобках текст подсказки к вводу конкретного значения [ Фамилия студента ];

3) чтобы избавиться от дублей строк в выборке для добавления в таблицу «Студент» дополните условие выборки требованием «без дублей записей». Для этого в режиме Конструктора выполните команду Вид | Свойства;

4) в диалоговом окне «Свойства запроса» переключите мышью свойство

«Уникальные значения» в состояние «Да»;

выполните запрос по команде Запрос | Запуск; в диалоговом окне, которое появится при реализации запроса, введите в кавычках конкретное значение, например «Андреев А.А.»;

просмотрите результаты запроса в Режиме таблицы и примите решение, удовлетворяют ли вас результаты выборки или нет;

Рис. 15. Структура запроса на добавление данных в таблицу

5) вернитесь в режим Конструктора. На этом первая часть алгоритма заканчивается; выборка данных на добавление подготовлена.

6) в режиме Конструктора выполните команду Запрос | Добавление;

7) в диалоговом окне «Добавление» в поле «имя таблицы» выберите из списка таблицу – приёмник данных «Студент»;

8) в бланке структуры запроса появится строка «Добавление» (рис. 15);

9) выполните запрос по команде Запрос | Запуск;

10) завершите результаты формирования запроса. На этом заканчивается вторая часть алгоритма;

11) в окне БД на закладке Таблицы выберите таблицу «Студент» и убедитесь, что добавление записи действительно произошло.

 

Запрос на обновление

 

Задание 8. Создайте запрос на замену в таблице «Студент», например, фамилии «Николаева Н.Н.» на «Сергеева Н.Н.»:

1) в качестве таблицы-источника данных выберите таблицу «Студент»;

2) сформируйте параметрический запрос-выборку всех полей по условию «фамилия» = [Старая фамилия]: в поле «фамилия» в строке «Условие отбора» наберите

[ Старая фамилия ];

3) выполните запрос по команде Запрос | Запуск;

4) в диалоге задайте в кавычках старую фамилию, например «Николаева Н.Н.»

5) просмотрите результаты запроса в Режиме таблицы и примите решение, удовлетворяют ли вас результаты выборки или нет;

6) вернитесь в режим Конструктора. На этом первая часть алгоритма заканчивается; выборка данных на обновление подготовлена.

7) в режиме Конструктора выполните команду Запрос | Обновление; в бланке структуры запроса появится строка «Обновление» (рис. 36);

8) в стро ку « Обновление» в поле «фамилия» введите подсказку для ввода [Новая фамилия ];

9) выполните запрос по команде Запрос | Запуск;

10) в диалоге задайте новую фамилию, например «Сергеева Н.Н.»;

11) завершите результаты формирования запроса. На этом заканчивается вторая часть алгоритма;

12) в окне БД на закладке Таблицы выберите таблицу «Студент» и

убедитесь, что обновление записи действительно произошло.

Рис. 36. Структура запроса на обновление данных