Размещено на http://www.allbest.ru/

41

Федеральное агентство по образованию

ГОУ ВПО «Уральский государственный технический университет УПИ»

Компьютерное проектирование баз данных в среде СУБД Access

В двух частях

Часть 2. Нормализация структуры данных и корректирующие запросы

Методические указания к лабораторному практикуму по дисциплинам

«Базы данных», «Управление данными» для студентов специальностей

230101-Вычислительные машины, комплексы, системы и сети,

230201-Информационные системы и технологии

Екатеринбург 2006

УДК 681.3

Составитель В.Ю. Кара-Ушанов

Научный редактор проф., канд. физ.-мат. наук В.И. Рогович

Компьютерное проектирование баз данных в среде СУБД Access. В 2 ч. Ч.2: Нормализация структуры данных и корректирующие запросы: методические указания к лабораторному практикуму по дисциплинам «Базы данных», «Управление данными» /сост. В. Ю. Кара-Ушанов. Екатеринбург: ГОУ ВПО УГТУ-УПИ, 2006. 46 с.

Методические указания предназначены для студентов, изучающих в дисциплинах компьютерного цикла основы проектирования баз данных. На примерах компьютерных упражнений осваиваются методологические принципы проектирования баз данных и основы технологии работы в среде популярного Windows-приложения СУБД Access. Обсуждаются проблемы проектирования баз данных (нормализация структуры данных, декомпозиция реляционных таблиц, контроль целостности данных) и методы их решения. Приемы работы в среде Access осваиваются в контексте общих проблем проектирования баз данных. Предлагаются контрольные задания для самостоятельных занятий.

Библиогр.: 10 назв. Рис. 25. Прил. 1.

Подготовлено кафедрой «Вычислительная техника».

Формирование запросов

Проблемы манипулирования данными и запросы на создание таблиц

Проблемы выполнения корректирующих запросов

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

При выполнении корректирующих запросов могут встретиться проблемы, которые являются следствием недостатков структуры данных «универсального» отношения [1-5]. Это так называемые аномалии добавления, обновления и удаления. Такими дефектами обладает и наша таблица «Учеба» [6, стр. 18]. Например, мы не сможем ввести в таблицу данные о зачисленном в вуз студенте до тех пор, пока он не сдаст первый экзамен и не будут определены ключевые атрибуты «предмет» и «семестр». Это пример аномалии добавления. Другой пример: при изменении фамилии студента мы должны будем обновить поле «фамилия» не один, а много раз во всех строках-записях, содержащих информацию о результатах сдачи экзаменов этим студентом. При этом есть вероятность ошибочного ввода в разные строки разных данных. Это пример аномалии обновления. Наконец, при удалении единственной строки, содержащей информацию о сдаче экзаменов студентом, мы заодно потеряем информацию и о самом студенте. Это пример аномалии удаления.

Аномалии манипулирования данными являются следствием избыточного дублирования данных, присущего «универсальному» отношению. Дело в том, что в «универсальном» отношении присутствуют семантически разнородные данные и попытки манипулирования данными одного рода влекут за собой проблемы в отношении данных другого рода.

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

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

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

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

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

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

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

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

Рассмотрим, как в Access реализуется операция проекция-создание одной таблицы как проекции другой. Алгоритм выполнения этой процедуры состоит из трёх крупных этапов [7-10]:

Построение запроса-выборки столбцов-атрибутов без дублей строк [6, контрольное задание 6 на стр. 25]. Тем самым создается проекция - подмножество данных исходной таблицы, которое определит структуру и наполнение новой таблицы.

Выполнение запроса на создание новой таблицы.

Определение ключа вновь созданной таблицы [6, стр.16].

Контрольное задание 1. На основе исходной таблицы «Учёба» со схемой отношения:

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

создайте две новые таблицы со схемами:

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

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

Алгоритм построения и выполнения запроса на создание новой таблицы рассмотрим на примере отношения «Кафедра»:

1) в режиме Конструктора запросов в качестве таблицы-источника данных к запросу выберите таблицу «Учёба» так, как описано в основном алгоритме создания запросов [6, стр.20];

2) сформируйте запрос-выборку, в котором присутствовали бы следующие поля таблицы «Кафедра»: специальность, факультет;

3) в режиме Конструктора выполните команду Вид|Свойства;

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

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

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

7) в диалоговом окне «Свойства запроса» введите в поле «Таблица» имя новой таблицы «Кафедра» (рис. 1);

8) в режиме Конструктора выполните команду Запрос|Запуск. В новую таблицу будет скопировано соответствующее количество записей. На экране появится окно просмотра данных в Режиме таблицы;

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

Рис. 1 Окно наименования новой таблицы

10) в окне БД выберите закладку Таблицы и убедитесь, что новая таблица «Кафедра» представлена в списке таблиц БД; это является признаком успешного выполнения запроса на создание таблицы;

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

12) в режиме Конструктора таблиц в окне структуры таблицы определите ключевые атрибуты (в таблице «Кафедра» это поле «специальность»);

13) сохраните результаты спецификации новой таблицы «Кафедра» по команде Файл|Сохранить. На этом заканчивается третья часть алгоритма;

14) просмотрите в Режиме таблицы наполнение новой таблицы «Кафедра» (рис. 2);

Рис. 2 Наполнение таблицы «Кафедра»

15) по рассмотренному алгоритму создайте вторую таблицу «Экзамен».

Создание связей между таблицами

В результате декомпозиции «универсального» отношения «Учеба» мы получили представление той же самой предметной области в виде совокупности двух отношений - таблиц «Кафедра» и «Экзамен». Корректность и эквивалентность этих разных способов представления одной и той же предметной области еще предстоит проверить. Пока же установим связь между новыми таблицами.

Правила установления связей между таблицами в Access таковы, что разрешены только бинарные (между двумя таблицами) связи. Связи могут быть двух типов «один к одному» и «один ко многим». Причём одна таблица выступает в роли старшей (отцовской), например таблица «Кафедра» в отношении «Кафедра» : «Экзамен», а другая - в роли младшей (сыновьей), например таблица «Экзамен» в отношении «Кафедра» : «Экзамен». Однако одна и та же таблица в разных отношениях может выступать в роли старшей и младшей одновременно. Связь между таблицами устанавливается по общим полям-атрибутам (поле «факультет» в нашем случае). Связь между таблицами Access устанавлива-ется при помощи инструмента, называемого «Схемой данных» [7-10].

Контрольное задание 2. Для установления связи между таблицами «Кафедра» и «Экзамен»:

выполните команду Сервис|Схема данных - появится окно макета схемы данных БД;

в контекстном меню выполните команду Связи|Добавить таблицу;

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

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

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

для установления связи между таблицами «Кафедра» и «Экзамен» захватите мышью и перенесите поле «факультет» таблицы «Кафедра» на поле «факультет» таблицы «Экзамен» - раскроется окно «Изменение связей» (рис. 3);

не настраивая свойства связи, нажмите экранную кнопку .

Схема базы данных примет вид, аналогичный представленному на рис. 4.

Рис. 3 Окно спецификации связей в схеме данных

Рис. 4 Схема данных двух таблиц

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Рис. 7

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Корректирующие запросы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Обеспечение целостности данных

Ограничения целостности данных

В отношении базы данных важно хорошо уяснить себе следующее. База данных - это не просто архив, хранилище полезной информации. База данных - главным образом информационная модель ПО, для которой она создана. Поэтому важнейшим условием ее успешной эксплуатации является адекватность хранимых в ней данных актуальному состоянию ПО.

Целостность БД - это синоним её системности. Для успешного исследования ПО должна рассматриваться как система. Точно так же БД - её информационная модель - должна проектироваться как системный объект. Это значит, что структура БД определяется не только составом образующих её информационных элементов, но и характером связей между ними. Связи соответствуют зависимостям между компонентами ПО. Всякие зависимости представляют собой ограничения на возможные отношения элементов системы. В теории БД эти ограничения называются ограничениями целостности данных [1-5].

При проектировании БД особое значение имеют так называемые семантические ограничения целостности (или семантические условия). Семантические ограничения присущи самой ПО и их учёт основан на анализе смыслового содержания данных. Семантические ограничения целостности не выводятся. Это свойства данных, которые выполняются или не выполняются для рассматриваемого отношения элементов данных.

Семантические ограничения целостности различают:

ограничения целостности атрибутов (доменов);

ограничения целостности отношений (таблиц);

ограничения целостности ссылок между отношениями (ограничения целостности базы данных).

Ограничением целостности атрибута является определение множества элементов данных - домена, на котором определен атрибут. Ограничения такого рода задаются при определении структуры таблицы в виде свойств образующих ее полей, таких как тип данных, размер поля, маска ввода, значение по умолчанию, условие на значение и др. Например, понятие «оценка» предполагает, что это данное «числового» и никакого другого типа со значениями в диапазоне от 2 до 5 или что «фамилия» - это строка символов определенной длины, содержащая фамилию и инициалы и т.д.

Ограничение целостности атрибутов проверяется СУБД всегда и притом немедленно. Любая попытка введения некорректного значения атрибута будет отвергнута.

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

Зависимости одних атрибутов от других являются очень распространенным типом ограничений. Особое значение имеют зависимости от так называемых потенциальных ключей. Строки-кортежи отношения соответствуют реальным экземплярам объектов, а реальные объекты различимы, идентифицируемы. Это значит, что среди атрибутов отношения всегда найдется такое их подмножество, которое однозначно определяет любой другой атрибут отношения, а значит, и всё отношение в целом. Например, понятие «номер студента» в роли потенциального, первичного ключа как ограничение означает, что не может быть двух и более персон с одинаковыми учётными номерами.

Значение Null используется в реляционной модели при решении проблемы отсутствующей информации. Null - это не одно и то же, что пробелы или числовые нули. Значения Null могут использоваться как значения атрибутов, для которых данные не известны. Null-значение для заданного атрибута может быть разрешено или запрещено. Значение Not Null (обязательное поле) является ограничением для ключевых полей и означает, что ни при каких условиях ключевые атрибуты не могут принимать неопределенные значения.

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

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

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

Роль связей как ограничений целостности проявляется в том, что они налагают ограничения на выполнение корректирующих операций (добавление, удаление и обновление), выполняемых над строками-кортежами связанных таблиц. Корректирующие операции должны выполняться таким образом, чтобы связи между таблицами всегда оставались согласованными и соответствовали семантике связей между объектами ПО.

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

Примерами ограничений целостности ссылок в нашем случае являются, например такие:

Отношение между таблицами «Кафедра» и «Студент» должно быть таково, что студент не может быть принят на несуществующую специальность.

Отношение между таблицами «Студент» и «Сессия» должно быть таково, что персона, не зачисленная как студент, не может быть допущена к сдаче экзамена.

Контроль целостности данных

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

Контрольное задание 9. Для проверки корректности процедур манипулирования данными связанных таблиц попытайтесь добавить вручную в таблицу «Студент» бесконтрольную запись такого содержания: «000000», «Одинцов О.О.», «c0» (речь идет о приёме студента на несуществующую специальность):

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

2) введите вручную новую запись: «000000», «Одинцов О.О.», «с0»;

3) сохраните результат по команде Файл|Сохранить и Файл|Закрыть.

Вы выполнили некорректную с точки зрения семантики ПО операцию: зачислили студента на несуществующую специальность. Система БД никак на это не отреагировала. Это плохо. Наша БД не защищена от некорректных действий пользователя. Причина в том, что нет корректных связей между таблицами и ничто не мешает вводить запрещенные данные (например, номер специальности, не зарегистрированной в БД, в таблице «Кафедра», как в нашем примере).

Семантика связей между таблицами

В нашем примере представлены связи обоих типов. Между таблицами «Кафедра» и «Студент» имеется неидентифицирующая связь. Роль внешнего ключа таблицы «Студент» играет атрибут «специальность». Семантика этой связи такова «каждый студент обучается по определенной специальности, но персона студента однозначно определяется его собственным учетным номером». Между таблицами «Студент» и «Сессия» имеется идентифицирующая связь. Внешним ключом таблицы «Сессия» является атрибут «номер студента». Семантика этой связи такова «результаты сдачи экзамена по данному предмету в данный семестр однозначно определяются учетным номером студента».

СУБД Access замечательна тем, что установка средств контроля целостности данных в связанных таблицах в значительной степени автоматизирована. Познакомимся с этими возможностями подробнее.

Во-первых, корректность ввода значений внешнего ключа «специальность» в таблице «Студент» может быть обеспечена настройкой свойства подстановка этого поля. Свойство подстановка позволяет связать поле с элементом управления «поле со списком» и вводить данные под управлением списка «разрешенных» значений. Источником «разрешенных» значений для поля «специальность» таблицы «Студент» является отцовская таблица «Кафедра».

Контрольное задание 10. Для обеспечения контроля ввода корректных значений внешнего ключа «специальность» в таблице «Студент» настройте его свойство подстановка (создайте на основе таблицы «Кафедра» поле со списком разрешенных значений):

1) в окне БД на закладке Таблицы откройте таблицу «Студент» в режиме Конструктор;

2) выделите поле «специальность»;

3) в нижней части диалогового окна, на закладке «Подстановка» настройте свойства элемента управления «Поле со списком» в соответствии с образцом, приведенным на рис. 17;

4) по завершении модификации структуры таблицы «Студент» сохраните результаты по команде Файл|Сохранить и закройте диалоговое окно;

5) откройте таблицу «Студент» в Режиме таблицы, для чего нажмите экранную кнопку ;

6) в любой строке таблицы щелчком мыши в поле «специальность» вызовите список «разрешенных» значений (рис. 18);

7) попробуйте ввести данные в обход элемента управления, убедитесь в тщетности этой попытки.

Рис. 17 Настройка свойств элемента управления для поля специальность

Рис. 18 Ввод данных под управлением поля со списком

Проблема ввода корректных значений внешнего ключа актуальна и для таблицы «Сессия». Внешний ключ «номер студента» в таблице «Сессия» также должен принимать только «разрешенные» значения. Источником разрешенных номеров студентов является таблица «Студент». По аналогии с рассмотренным примером настройте свойство подстановка поля «номер студента» таблицы «Сессия» (создайте на основе таблицы «Студент» поле со списком разрешенных значений).

Более универсальное средство контроля ссылочной целостности обеспечивает настройка свойств связей между таблицами [7-10].

Контрольное задание 11. Для обеспечения целостности и корректности данных в БД установите связи между таблицами, т.е. создайте корректную схему данных БД:

1) выполните команду Сервис|Схема данных - появится макет схемы данных БД (рис. 9);

2) для установления контроля целостности связи между таблицами «Кафедра» и «Студент» щелкните правой клавишей мыши по изображению связи и выберите в контекстном меню «Изменить связь» - раскроется окно «Изменение связей» (рис. 19);

3) в окне «Изменение связей» мышью «включите» настройку «Обеспечение целостности данных» и нажмите экранную кнопку OK. Попытка установления связи увенчается неудачей (рис. 20). Причина в том, что в таблице «Студент» нарушено условие целостности данных - введены данные о студенте с незарегистрированной в БД специальностью;

Рис. 19 Окно спецификации связей в схеме данных

Рис. 20 Сообщение о неудачной попытке установления связи

4) для установления контроля целостности связи между таблицами «Студент» и «Сессия» щелкните правой клавишей мыши по изображению связи и выберите в контекстном меню «Изменить связь» - раскроется окно «Изменение связей»;

5) в окне «Изменение связей» мышью «включите» состояние «Обеспечение целостности данных» и нажмите экранную кнопку OK - связь будет установлена. Корректная связь в схеме данных выделяется жирной линией и метками кардинальности связи 1 и (рис. 21);

6) сохраните результат создания связей между таблицами по команде Файл|Сохранить. Ничего, что попытка установления связи с обеспечением целостности данных между таблицами «Кафедра» и «Студент» оказалась неудачной. Схему данных (макет), как и любой объект Access, можно отредактировать в новых условиях.

Из полученных результатов важно уяснить себе следующее:

методы создания связи с обеспечением целостности данных;

причины неудачи создания связей;

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

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

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

2) удалите вручную запись: «000000 Одинцов О.О. с0»;

3) сохраните результат по команде Файл|Сохранить и Файл|Закрыть;

4) вернитесь к настройке свойств связей по команде Сервис|Схема данных - появится макет схемы данных;

5) щелкните мышью по связи «Кафедра»:«Студент» - раскроется окно «Изменение связей» (рис. 19);

6) в окне «Изменение связей» мышью «включите» состояние «Обеспечение целостности данных» и нажмите экранную кнопку OK. Макет схемы данных примет вид, как на рис. 21;

7) сохраните результат создания связей между таблицами по команде Файл|Сохранить.

Внимание! Важный результат: наша демонстрационная ПО УЧЕБА представлена БД в виде совокупности нормализованных таблиц «Кафедра», «Студент» и «Сессия», связанных корректными связями, которые обеспечивают контроль целостности данных.

Рис. 21 Корректная схема базы данных УЧЕБА

Манипулирование данными в связанных таблицах

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

Для того чтобы при манипулировании данными одной из связанных таблиц выполнялись адекватные изменения других таблиц, могут быть «включены» каскадные обновление и удаление данных [7-10].

Какие правила обеспечения целостности данных выбрать, зависит от семантики данных моделируемой ПО. Рассмотрим эти проблемы на нашем примере БД УЧЕБА.

Конкретный студент (строка в таблице «Студент») не может быть зачислен без отнесения его к определенной специальности (внешний ключ «специальность» не может принимать Null-значение), следовательно, добавление в таблицу «Студент» новой строки, не имеющей соответствующей строки в таблице «Кафедра», должно быть запрещено (нельзя принимать студентов на несуществующую специальность). По этой же причине удаление соответствующей строки в таблице «Кафедра» должно быть запрещено, пока по этой специальности обучается хотя бы один студент. Альтернативное правило каскадирования означало бы, что при закрытии (удалении) специальности все ее студенты должны быть отчислены. Вряд ли такая семантика может быть оправдана.

В случае обновления строки в таблице «Кафедра» (например, изменении номера специальности) разумно допустить каскадирование, т.е. адекватное изменение этих же данных в таблице «Студент».

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

Контрольное задание 13. Настройте контроль целостности в схеме данных на каскадные обновление и удаление данных:

1) раскройте макет схемы данных по команде Сервис|Схема данных;

2) для редактирования связи «Кафедра»:«Студент» щелкните мышью по связи - раскроется окно «Изменение связей» (рис. 22);

3) в окне «Изменение связей» мышью «включите» настройку «Каскадное обновление связанных полей» и нажмите кнопку ОК (рис. 22);

4) для редактирования связи «Студент»:«Сессия» щелкните мышью по связи. Раскроется окно «Изменение связей» (рис. 23);

3) в окне «Изменение связей» мышью «включите» состояния «Каскадное обновление связанных полей» и «Каскадное удаление связанных полей»; нажмите экранную кнопку ОК (рис. 23);

Рис. 22 Настройка свойств связи «Кафедра» : «Студент»

4) сохраните результаты редактирования связей между таблицами по команде Файл|Сохранить;

Рис. 23 Настройка свойств связи «Студент» : «Сессия»

Контрольное задание 14. Выполните простейшие манипуляции с данными в условиях контроля целостности данных:

1) попробуйте добавить в сыновью таблицу «Студент» запрещенные данные. В окне БД на закладке Таблица выберите из списка таблицу «Студент» и нажмите экранную кнопку ;

2) добавьте вручную запись: «000000 Одинцов О.О. с0». При попытке сохранить некорректные данные появится диагностическое сообщение о невозможности ввода (рис. 24);

Рис. 24 Диагностическое сообщение о невозможности добавления записей

3) попробуйте теперь удалить из отцовской таблицы «Кафедра» запись, имеющую потомков в сыновьей таблице «Студент». В окне БД на закладке Таблица выберите из списка таблицу «Кафедра» и нажмите экранную кнопку ;

4) удалите запись «с1 ф1». При попытке удаления появится диагностическое сообщение о невозможности этой операции (рис. 25);

Рис. 25 Диагностическое сообщение о невозможности удаления связанных записей

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

5) попробуйте теперь изменить в отцовской таблице «Кафедра» запись, имеющую потомков в сыновней таблице «Студент»;

6) измените в таблице «Кафедра» номер специальности «c2» на «c4» в соответствующей записи;

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

8) попробуйте изменить в таблице «Студент» её независимые данные, например, «номер студента»;

9) измените в соответствующей строке «номер студента» (это Сергеева Н.Н.) «031512» на «040000» и сохраните результаты по команде Файл|Сохранить;

10) посмотрите, как эта операция скажется на подчиненной таблице «Сессия». В окне БД на закладке Таблица выберите из списка таблицу «Сессия» и нажмите экранную кнопку . Должно произойти каскадное обновление данных в подчиненной таблице;

11) попробуйте удалить в таблице «Студент» хотя бы одну её запись. В окне БД на закладке Таблица выберите из списка таблицу «Студент» и нажмите экранную кнопку ;

12) удалите запись о студенте Андрееве А.А. в таблице «Студент» в соответствующей строке и сохраните результаты по команде Файл|Сохранить;

13) посмотрите, как эта операция скажется на подчиненной таблице «Сессия». В окне БД на закладке Таблица выберите из списка соответствующую таблицу и нажмите экранную кнопку . Зависимые записи (такая была одна о студенте Андрееве А.А. под номером 028101) должны исчезнуть. Должно произойти каскадное удаление данных в подчиненной таблице;

14) попробуйте добавить в таблицу «Сессия» некорректные данные. В окне БД на закладке «Таблица» выберите из списка таблицу «Сессия» и нажмите экранную кнопку ;

15) добавьте вручную запись: «000000 физика 2 3». При попытке сохранить некорректные данные появится диагностическое сообщение о невозможности ввода;

16) попробуйте удалить запись в таблице «Сессия» «040000 математика 1 5»;

17) убедитесь, что эта операция прошла без проблем.

Заключение

В процессе выполнения контрольных заданий были отрепетированы различные приемы формирования структуры демонстрационной БД. Должно быть понятно, насколько сложна и ответственна эта работа.

Должно быть понятно, что никто не разрабатывает БД так, как это делали мы в учебных целях. Никто не проектирует БД путем декомпозиции «универсального» отношения на нормализованные компоненты-проекции. Обычно на этапах, предшествующих рабочему проектированию (реализации) на основании анализа ПО удается сгруппировать семантически однородные данные в группы (будущие таблицы) и установить между ними связи. При этом используются модельные представления, основанные на способности интеллекта человека к абстрактному мышлению, на способности анализировать семантику данных ПО (например, модель «сущность-связь» [1-5]). Структура БД является отображением этих представлений в компьютерной среде с учетом особенностей ее программных и технических средств.

...