Черноусова А.М. Создание и использование баз данных

Подождите немного. Документ загружается.

5 Многотабличная база данных в Microsoft Office Access 2007

Целью работы является создание и работа с реляционной базой данных,

состоящей из нескольких таблиц, связанных друг с другом посредством общих

ключевых полей, в Microsoft Office Access 2007.

5.1 Общие положения

5.1.1 Отношения между таблицами в базе данных

Access является системой управления реляционными базами данных. Ре-

ляционные базы данных в настоящее время наиболее распространены и факти-

чески являются промышленным стандартом. Единицей хранящейся в реляци-

онной базе данных информации является таблица. Каждая таблица представля-

ет собой совокупность строк и столбцов, где строки (записи) соответствуют

конкретному объекту, событию или явлению, а столбцы - атрибутам (призна-

кам, характеристикам, параметрам) объекта, события, явления.

Между отдельными таблицами базы данных могут существовать связи [1,

3 - 7].

Установление связи между таблицами обеспечивает следующее:

- повышение достоверности хранимой в базе данных информации, так как

многие СУБД автоматически выполняют контроль целостности данных, вводи-

мых в базу, в соответствии с установленными связями;

- облегчение доступа к данным при выполнении таких операций, как по-

иск, просмотр, редактирование, выборка и подготовка отчетов, при этом

уменьшается количество явных обращений к таблицам данных и число мани-

пуляций в каждой их них.

В каждой таблице базы данных может существовать первичный ключ -

поле или набор полей, однозначно идентифицирующий запись. Значение пер-

вичного ключа в таблице базы данных должно быть уникальным, то есть в таб-

лице не должно существовать

двух или более записей с одинаковым значением

первичного ключа. Первичные ключи облегчают установление связи между

таблицами.

В базах данных возможно четыре типа отношений между таблицами:

«один-к-одному», «один-ко-многим», «много-к-одному», «много-ко-многим».

При отношении «один-к-одному» каждая запись в первой таблице может

иметь не

более одной связанной записи во второй таблице и наоборот. Отноше-

ния этого типа используются нечасто, поскольку обычно сведения, связанные

таким образом, хранятся в одной таблице. Отношение «один-к-одному» ис-

пользуется для разделения таблицы, содержащей много полей, с целью отделе-

ния части таблицы по соображениям безопасности, а также с целью сохранения

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

ределения такого отношения у обеих таблиц должно быть общее поле.

Например, имеются две таблицы «Персональная карточка студента» и

«Медицинская карточка студента». В таблице «Персональная карточка студен-

та» содержатся общие сведения о студенте (код, фамилия, имя, отчество, адрес,

дата рождения и другие данные), а в таблице «Медицинская карточка студента»

- сведения для студенческой поликлиники. Между этими таблицами существует

отношение «один-к-одному», поскольку для одного студента может существо-

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

поддерживается при помощи совпадающих полей: «Код студента» (таблица

«Персональная карточка студента

») и «Код студента» (таблица «Медицинская

карточка студента»). Эти поля имеют одинаковые наименования, однако могут

иметь и различные. Связь между таблицами устанавливается на основании зна-

чений совпадающих полей, но не их наименований.

Отношение «один-ко-многим» означает, что одной записи из родитель-

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

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

данных. Связанные отношениями таблицы взаимодействуют по принципу

«главная – подчиненная». Главную таблицу часто называют родительской, а

подчиненную - дочерней. Одна и та же таблица может быть главной по отно-

шению к одной таблице базы данных и дочерней по отношению к другой. На-

пример,

связь «один-ко-многим» имеется между таблицами «Читательский би-

лет студента» и «Выдача книг». Связь между таблицами осуществляется на ос-

новании значений совпадающих полей «Код читательского билета».

Отношение «много-к-одному» аналогичен типу «один-ко-многим». Тип

отношения зависит от выбранной точки зрения. Например, если рассматривать

отношение между сделанными заказами и

клиентами, то получится отношение

«много-к-одному».

Отношение «много-ко-многим» возникает между таблицами тех случаях,

когда:

- одна запись из первой таблицы может быть связана более, чем с одной

записью из второй таблицы;

- одна запись из таблицы может быть связана более чем с одной записью

из первой таблицы.

Например, отношение «много-ко-многим» существует между таблицами

«Дисциплины» и «Преподаватели». Одну дисциплину могут вести несколько

преподавателей. Каждый преподаватель может вести несколько разных предме-

тов. Обычно связь с отношением «многие-ко-многим» представляется в виде

двух связей с отношением «один-ко-многим» через третью таблицу, назовем ее

«Связи». Эта таблица

будет содержать только два поля «Код дисциплины» и

«Код преподавателя». Между таблицами «Дисциплина» и «Связи» отношение

«один-ко-многим», между таблицами «Связи» и «Преподаватели» - «много-к-

одному».

5.1.2 Нормализация базы данных

Для реляционной базы данных проектирование логической структуры за-

ключается в том, чтобы разбить всю информацию по таблицам (или в терминах

реляционной модели - по отношениям), а также определить состав полей (в

терминах реляционной теории - атрибутов) для каждой из этих таблиц.

Процесс проектирования является творческим, в немалой степени зави-

сит от опыта и интуиции разработчика. Основными целями при разработке эф-

фективной структуры данных являются:

- обеспечение быстрого доступа к данным в таблицах;

- исключение ненужного повторения данных, которое может являться

причиной ошибок при вводе и нерационального использования дискового про-

странства компьютера;

- обеспечение

целостности данных таким образом, чтобы при изменении

одних объектов автоматически происходило соответствующее изменение свя-

занных с ними объектов.

Процесс построения эффективной структуры данных для уменьшения

избыточности информации в базе данных называется нормализацией. В теории

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

по разбиению данных, обладающих сложной структурой, среди нескольких

таблиц

. Теория нормализации оперирует с пятью нормальными формами таб-

лиц. Избыточность информации уменьшается от первой до пятой нормальной

формы. Поэтому каждая последующая нормальная форма должна удовлетво-

рять требованиям предыдущей формы и некоторым дополнительным условиям.

При практическом проектировании баз данных четвертая и пятая формы, как

правило, не используются, поэтому ограничимся рассмотрением первых

трех

нормальных форм.

В качестве примера рассмотрим таблицу «Продажи», которая содержит

информацию о клиентах-предприятиях и их представителях, заказах, продан-

ном товаре. Структура таблицы «Продажи» приведена в таблице 5.1. Эту таб-

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

держится значительное количество повторяющейся информации. Например,

сведения о каждом клиенте

повторяются для каждого сделанного им заказа. Та-

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

те с базой данных:

- тратится значительное время на ввод повторяющихся данных;

- при изменении адреса или телефона клиента необходимо корректиро-

вать все записи, содержащие сведения о его заказах;

- наличие повторяющейся информации приведет к неоправданному

уве-

личению размера базы данных;

- любые внештатные ситуации потребуют значительного времени для по-

лучения требуемой информации, например, при поиске ошибок.

Таблица в первой нормальной форме должна удовлетворять следующим

требованиям:

а) таблица не должна иметь повторяющихся записей;

б) в таблице должны отсутствовать повторяющиеся группы полей;

в) поля должны быть неделимыми.

Для

удовлетворения первого условия каждая таблица должна иметь уни-

кальный ключ. Таблица «Продажи» не содержит уникального ключа, что до-

пускает наличие в ней повторяющихся записей. Для выполнения этого условия

создадим уникальный ключ, содержащий поле «Код клиента».

Таблица 5.1 – Структура таблицы «Продажи»

Наименование Тип

1 Предприятие Текстовый

2 ФИО руководителя Текстовый

3 Телефон руководителя Числовой

4 Адрес Текстовый

5 Банковские реквизиты Текстовый

6 ФИО представителя Текстовый

7 Паспортные данные представителя Текстовый

8 Телефон представителя Числовой

9 Дата заказа Дата/Время

10 Код товара Числовой

11Количество Числовой

12 Дата продажи Дата/Время

13 Накладная Числовой

14 Наименование товара Текстовый

15 Цена Денежный

16 Характеристики Текстовый

Второе требование постулирует устранение повторяющихся групп. По-

скольку каждый клиент может сделать несколько заказов, а каждый из которых

может содержать несколько товаров, то разобьем таблицу «Продажи» на две

таблицы «Клиенты» и «Заказы». Каждая запись первой таблицы будет содер-

жать сведения об одном из клиентов, а второй - информацию о каждом из

зака-

зов. Между таблицами будет отношение «один-ко-многим» при помощи поля

«Код клиента». Таблица «Заказы» имеет уникальный составной ключ, состоя-

щий из полей «Код клиента», «Код товара» и «Дата заказа».

Неделимость поля означает, что содержащиеся в нем значения не должны

делиться на более мелкие. Например, поле, содержащее фамилию, имя

и отче-

ство, следует разделить на три поля - отдельно для фамилии, имени и отчества,

адрес - на «Индекс», «Город», «Улица_дом». Структуры таблиц «Клиенты» и

«Заказы» в первой нормальной форме показаны на рисунке 5.1.

Таблица находится во второй нормальной форме, если:

- она удовлетворяет условиям первой нормальной формы;

- все поля таблицы зависят от первичного ключа, то есть первичный ключ

однозначно определяет запись и не является избыточен.

Если все возможные ключи отношения содержат по одному атрибуту, то

это отношение задано во второй нормальной форме, так как в этом случае все

атрибуты, не являющиеся первичными, полностью зависят от возможных клю-

чей. Приведение отношений ко второй нормальной форме заключается в обес-

печении полной функциональной зависимости всех атрибутов от ключа. Функ-

циональной зависимостью между полями А и В называется зависимость, при

которой каждому значению А в любой момент времени соответствует единст-

венное значение В из возможных. Полной функциональной зависимостью меж-

ду составным полем А и полем В называется зависимость, при которой поле В

зависит функционально от поля А и не зависит функционально от любого под-

множества поля А.

Понятие второй нормальной формы применимо только к таблицам, имею-

щим составной ключ. В рассматриваемом примере такой таблицей является

«Заказы», в которой составной ключ образуют поля «Код клиента», «Код това-

ра» и «Дата заказа». Данная таблица не является таблицей во второй нормаль-

ной форме, поскольку поля «Наименование товара», «Цена», «Характеристики»

однозначно определяются только одним из ключевых полей («Код товара»).

Для приведения таблицы ко второй нормальной форме выделим из таблицы

«Заказы» таблицу «Товары», которая будет содержать информацию о товарах

каждого типа. Для связывания таблиц «Заказы» и «Товары» используется поле

«Код товара». Результат представлен на рисунке 5.2.

Таблица «Клиенты» Таблица «Заказы»

Наименование Тип Наименование Тип

1 Код клиента * Числовой 1 Код клиента * Числовой

2 Предприятие Текстовый 2 Дата заказа * Дата/Время

3 Фамилия руководителя Текстовый 3 Код товара * Числовой

4 Имя Текстовый 4 Количество Числовой

5 Отчество Текстовый 5 Дата продажи Дата/Время

6 Телефон руководителя Числовой 6 Накладная Числовой

7 Индекс Числовой 7 Наименование товара Текстовый

8 Город Текстовый 8 Цена Денежный

9 Улица_дом Текстовый 9 Характеристики Текстовый

10 Банковские реквизиты Текстовый

11 ФИО представителя Текстовый

12 Паспортные данные Текстовый

13 Телефон представителя Числовой

Рисунок 5.1 – Первая нормальная форма

Таблица «Клиенты» Таблица «Заказы»

Наименование Тип Наименование Тип

1 Код клиента * Числовой 1 Код заказа * Числовой

2 Предприятие Текстовый 2 Дата заказа Дата/Время

3 Фамилия руководителя Текстовый 3 Код клиента Числовой

4 Имя Текстовый 4 Количество Числовой

5 Отчество Текстовый 5 Дата продажи Дата/Время

6 Телефон руководителя Числовой 6 Накладная Числовой

7 Индекс Числовой 7 Код товара Числовой

8 Город Текстовый Таблица «Товары»

9 Улица_дом Текстовый Наименование Тип

10 Банковские реквизиты Текстовый 1 Код товара * Числовой

11 ФИО представителя Текстовый 2 Наименование товара Текстовый

12 Паспортные данные Текстовый 3 Цена Денежный

13 Телефон представителя Текстовый

4 Характеристики Текстовый

Рисунок 5.2 – Вторая нормальная форма

Таблица находится в третьей нормальной форме, если:

- она удовлетворяет условиям второй нормальной формы;

- каждый атрибут этого отношения, не являющийся первичным, не тран-

зитивно зависит от каждого возможного ключа этого отношения.

Транзитивная зависимость между полями А и С существует в том слу-

чае, когда поле С функционально зависит от поля В, а поле В функционально

зависит от поля А; при этом не существует функциональной зависимости поля

А от поля В. Требование третьей нормальной формы

сводится к тому, чтобы

все неключевые поля зависели только от первичного ключа и не зависели друг

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

ление таблицы с целью помещения в отдельную таблицу (или несколько таб-

лиц) столбцов, которые не зависят от значения составного индекса. В результа-

те такого

разбиения каждое из неключевых полей должно оказаться независи-

мым от какого-либо другого неключевого поля.

В таблице «Клиенты» поля «Паспортные данные» и «Телефон представи-

теля» содержат сведения о представителе предприятия-клиента, которые одно-

значно определяются значением поля «ФИО представителя». Поскольку не-

ключевые поля «Паспортные данные» и «Телефон представителя» однозначно

определяются другим

неключевым полем «ФИО представителя», таблица

«Клиенты» не является таблицей в третьей нормальной форме. Для приведения

этой таблицы к третьей нормальной форме создадим новую таблицу «Предста-

витель» (рисунок 5.3).

После определения структуры таблиц, отношений между ними и совпа-

дающих полей, которые будут использованы для связывания отдельных таблиц,

можно создавать многотабличную базу данных

в Access.

Таблица «Клиенты» Таблица «Заказы»

Наименование Тип Наименование Тип

1 Код клиента * Числовой 1 Код заказа * Числовой

2 Предприятие Текстовый 2 Дата заказа Дата/Время

3 Фамилия руководителя Текстовый 3 Код клиента Числовой

4 Имя Текстовый 4 Количество Числовой

5 Отчество Текстовый 5 Дата продажи Дата/Время

6 Телефон руководителя Числовой 6 Накладная Числовой

7 Индекс Числовой 7 Код товара Числовой

8 Город Текстовый Таблица «Товары»

9 Улица_дом Текстовый Наименование Тип

10 Банковские реквизиты Текстовый 1 Код товара * Числовой

11 Код представителя Числовой 2 Наименование товара Текстовый

Таблица «Представитель» 3 Цена Денежный

1 Код представителя * Числовой

4 Характеристики Текстовый

2 ФИО представителя Текстовый

3 Паспортные данные Текстовый

4 Телефон представителя Числовой

Рисунок 5.3 – Третья нормальная форма

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

В Access можно устанавливать постоянные связи между таблицами, ко-

торые будут поддерживаться при создании форм, отчетов и запросов. Устанав-

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

держит одну и ту же информацию. Поля, с помощью которых устанавливается

связь между таблицами, могут иметь различные

имена, но удобнее использо-

вать совпадающие имена.

Перед определением связей между таблицами или их удалением необхо-

димо закрыть все открытые таблицы. Создание связей между таблицами в Ac-

cess осуществляется в окне диалога «Схема данных», которое открывается по

команде Схема данных

, находящейся на Ленте в группе «Показать или

скрыть» на вкладке Работа с базами данных. Если в базе данных никаких свя-

зей не определено, автоматически открывается диалоговое окно «Добавление

таблицы». Если оно не отображается, то на вкладке Конструктор в группе

«Связи» нажать кнопку Добавить таблицу. В диалоговом окне «Добавление

таблицы»

отображаются все таблицы и запросы базы данных. Чтобы просмот-

реть только таблицы, выбрать пункт Таблицы. Чтобы просмотреть только за-

просы, выбрать пункт Запросы. Чтобы просмотреть и таблицы, и запросы, вы-

брать пункт Все.

На рисунке 5.4 показана схема данных для рассмотренной выше базы

данных «Продажи».

Для установления связей необходимо:

- в окне «

Добавление таблицы» в списке таблиц выделить первую добав-

ляемую таблицу (например, «Клиенты») и нажать кнопку Добавить, затем вы-

брать вторую добавляемую таблицу (например, «Заказы») и также нажать

кнопку Добавить, затем закрыть окно диалога «Добавление таблицы» кнопкой

Закрыть; в окне диалога «Схема данных» появятся две связываемые таблицы;

- для связывания таблиц выбрать

поле первой связываемой таблицы и пе-

реместить его с помощью мыши на соответствующее поле второй таблицы, для

связывания сразу нескольких полей выбрать эти поля при нажатой клавише

Ctrl и переместить во вторую таблицу группу выделенных полей;

- на экране откроется окно диалога «Изменение связей», как показано на

рисунке 5.5, в котором необходимо

проверить правильность имен связываемых

полей, находящихся в столбцах, при необходимости выбрать другие имена по-

лей; затем нажать кнопку Создать, по нажатию которой осуществится возврат

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

В окне диалога «Схема данных» имеется возможность не только созда-

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

выполнить следующие действия:

- изменить структуру таблицы;

- изменить существующую связь;

- удалить связь;

- удалить таблицу из окна диалога «Схема данных»;

- вывести на экран все существующие связи или связи только для кон-

кретной таблицы;

- определить связи для запросов, не задавая условия целостности данных.

Если при создании связи в окне диалога «Схема данных» возникает необ-

ходимость изменения структуры таблицы, то можно, не покидая окна диалога,

внести нужные изменения:

- установить указатель мыши

на модифицируемую таблицу;

- нажать правую клавишу мыши и выбрать из контекстного меню коман-

ду Конструктор таблиц;

- внести в структуру таблицы необходимые изменения;

- закончив внесения изменений, нажать кнопку закрытия окна в строке

заголовка; в ответ на запрос о сохранении изменений выбрать Да для сохране-

ния изменений и возвращения в окно

диалога «Схема данных».

Рисунок 5.4 – Окно диалога «Схема данных» с добавленными таблицами

Рисунок 5.5 – Окно диалога «Изменение связей»

Для изменения существующей связи выполняется следующая последова-

тельность действий:

- находясь в окне базы данных, нажать кнопку Изменить связи на Ленте

в группе «Сервис»;

- установить указатель на линию связи, которую требуется изменить, и

дважды нажать кнопку мыши;

- в открывшемся окне диалога «Изменение связей» внести нужные изме-

нения и нажать кнопку

ОК.

Для удаления связи необходимо открыть окно диалога «Схема данных»,

установить указатель на удаляемую линию связи, выделить ее, нажав кнопку

мыши, а затем нажать клавишу Delete. Когда Access предложит подтвердить

удаление связи, нажать кнопку Да.

Для удаления таблицы из макета схемы данных необходимо открыть окно

диалога «Схема данных», выбрать удаляемую таблицу и

нажать клавишу

Delete. Таблица будет удалена из макета схемы данных вместе с определенны-

ми для нее связями.

В окне диалога «Изменение связей» возможно определение условий це-

лостности данных. Целостность данных является одним из самых важных тре-

бований, предъявляемых к базам данных. Условиями целостности данных на-

зывают набор правил, используемых для

поддержания связей между записями в

связанных таблицах. Эти правила делают невозможным случайное удаление

или изменение связанных данных.

Условия целостности данных выполняются при следующих условиях:

- связанное поле главной таблицы является ключевым полем или имеет

уникальный индекс;

- связанные поля имеют один тип данных;

- обе таблицы принадлежат одной базе данных Access.

Невозможно определить

условия целостности данных для присоединен-

ных таблиц из баз данных других форматов.

При определении условия целостности данных действуют следующие ог-

раничения:

- невозможно ввести в поле внешнего ключа связанной таблицы значе-

ние, не содержащееся в ключевом поле главной таблицы, однако возможен

ввод в поле внешнего ключа пустых значений, показывающих, что записи

не

являются связанными; например, это означает, что нельзя сохранить запись,

регистрирующую заказ в таблице «Заказы», сделанный несуществующим кли-

ентом, но можно создать запись для заказа, который пока не отнесен ни к одно-

му из клиентов, если ввести пустое значение в поле «Код клиента»;

- не допускается удаление записи из главной таблицы,

если существуют

связанные с ней записи в подчиненной таблице; например, невозможно удалить

запись из таблицы «Клиенты», если в таблице «Заказы» имеются заказы, отно-

сящиеся к данному покупателю;

- невозможно изменить значение ключевого поля в главной таблице, если

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

клиента в таблице «Клиенты»,

если в таблице «Заказы» имеются заказы, отно-

сящиеся к данному покупателю.

Определение целостности данных предполагает выполнение следующих

действий:

а) в окне диалога «Схема данных» нажать два раза мышью на линии свя-

зи между двумя таблицами, откроется окно диалога «Связь»;

б) установить флажок Обеспечение целостности данных;

в) нажать ОК.

После выполнения указанных действий откроется снова окно диалога

«Схема

данных». Темная линия между двумя списками полей стала гораздо

темнее, и около нее появились два новых символа. Около главной таблицы те-

перь стоит символ «1», который указывает на часть «один» отношения «один-

ко-многим». Рядом с подчиненной таблицей отображается символ «∞» (беско-

нечность), который обозначает часть отношения «много».

Теперь любая попытка

выполнить действие, нарушающее перечисленные

выше ограничения, приведет к открытию окна диалога с предупреждением, а

само действие выполнено не будет.

При установке опции Обеспечение целостности данных стали доступны

опции Каскадное обновление связанных полей и Каскадное удаление свя-

занных полей. При выборе этих опций Access выполняет изменения в связан-

ных таблицах таким образом,

чтобы сохранить целостность данных, даже если

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

Если опция Каскадное обновление связанных полей не установлена, то

Access не позволит внести изменения в ключевое поле записи, связанной по ти-

пу «один-ко-многим». Вместо изменения будет выдано предупреждение о том,

что нарушена целостность

данных. Если установлена опция Каскадное обнов-

ление связанных полей, то Access выполнит все необходимые изменения в

связанных таблицах автоматически. Если установлена опция Каскадное уда-

ление связанных полей, можно удалять запись из главной таблицы. Связанные

с нею записи в подчиненных таблицах будут также автоматически удалены, со-

блюдая правила целостности данных.

5.1.4 Многотабличные

запросы

При выборе данных из таблиц часто используются многотабличные за-

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

отдельной таблице, а в совокупности связанных таблиц. Запросы в реляцион-

ных базах данных аналогичны обыкновенным запросам. Наличие в Access язы-

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

данных

путем заполнения предлагаемой СУБД запросной формы. При вклю-

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

других свойств окна запроса осуществляется практически также, как при работе

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

Рассмотрим составление запросов к связанным таблицам с отношением

«один-ко-многим» на

примере базы данных, схема данных которой приведена

на рисунке 5.4. База данных содержит таблицы «Клиенты», «Заказы», «Пред-