Особенности проектирования многотабличных баз

ЛЕКЦИЯ 6. ОСОБЕННОСТИ ПРОЕКТИРОВАНИЯ МНОГОТАБЛИЧНЫХ БАЗ ДАННЫХ

Как правило, базы данных Access являются многотабличными базами данных. Поэтому немаловажным и достаточно трудоемким процессом является проектирование структуры баз данных, состоящее, прежде всего, в эффективном распределении данных между таблицами.

База данных, состоящая из множества таблиц, устанавливает связи между этими таблицами с помощью совпадающих полей. Рассмотрим таблицы:

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

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

Сотрудник                           Информация о ком.                                   Ком

Рекомендуемые материалы

Каждая запись данных таблиц идентифицирует один объект: Сотрудника, Информацию о командировке, Информацию о предприятии-командире.

Отношения между таблицами определяются отношениями между объектами (полями).

Существуют четыре типа отношений между таблицами:

ü «один-к-одному»

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

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

ü «много-ко-многим»

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

Например, таблицы Физические лица и Сотрудники

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

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

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

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

Отношение «много-ко-многим » возникает в том случае, если:

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

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

В качестве примера обратимся к магазину оптовой торговли. Рассмотрим две группы объектов:

ü список товаров, производимых предприятиями;

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

Поставки товаров                                     Заказы потребителей

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

Проектирование нормализованной базы данных

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

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

ü исключить ненужное повторение данных;

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

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

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

Теория нормализации оперирует понятиями нормальных форм таблиц (от 1 до 5), причем каждая последующая нормализованная форма должна удовлетворять требованиям предыдущей формы и некоторым дополнительным условиям. (Мы рассмотрим три нормализованные формы).

В качестве примера рассмотрим информацию о продажах некоторых товаров.

Продажи

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

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

ü при изменении телефона или адреса покупателя эти координаты нужно выключить во всех заказах;

ü наличие новой информации снизит скорость выключения запросов и повысит вероятность ошибок.

Выполним нормализацию данных.

Первая нормальная форма таблицы

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

ü в таблице не должно быть повторения групп таблиц;

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

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

Клиенты                                         Заказы

Данными действиями мы ликвидировали повторяющиеся группы полей.

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

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

2. Первичный ключ содержит информацию, которая однозначно идентифицирует запись (не допускает повторений значений поля).

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

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

1. добавить в таблицу уникальное ключевое поле Код заказа;

2. создать уникальный составной индекс, состоящий из полей Код клиента, Код товара и Дата заказа.

После этого данные таблицы находятся в первой нормальной форме.

Вторая нормальная форма

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

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

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

Из приведенного выше определения следует, что понятие второй нормальной нормы применимо только к таблицам, имеющим составной индекс (Заказы). Данная таблица не является таблицей во второй нормальной форме, поскольку поля Категория, Наименование товара и Цена однозначно определяются только одним из ключевых полей – Код товара.

Поэтому, для приведения таблицы Заказы ко второй нормальной форме, необходимо выделить из таблицы Заказы таблицу Товары, которая будет содержать информацию о товарах каждого типа. Для связывания таблиц Заказы и Товары используются поля Код товара.

                                   Клиенты                                          Заказы

           

Третья нормальная форма таблицы

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

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

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

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

Так в таблице Клиенты поле Руководитель однозначно определяется значением поля Предприятие, поэтому следует создать таблицу Предприятия:

           

 Таким образом, в  таблице Клиенты будем хранить только название Предприятия.

Определение связей между таблицами

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

Для этого следует произвести следующие действия:

- вызвать команду Сервис/Схема данных или кнопку Схема данных из панели инструментов;

- добавить в окно связей окно Добавить таблицу (п.м. Связь/Добавить таблицу или кнопка Добавить таблицу).

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

- для связи полей выбрать поле первой таблицы (Код клиента) и переместить его мышью на соответствующее поле в первой таблице (Код клиента).

Ключевые поля в списке полей обычно отображаются полужирным шрифтом. Связанные поля не обязательно должны иметь одно название, но обязательно должны иметь одинаковые имена данных (а для Числового поля – еще и одинаковое свойство Размер поля).

На экране откроется окно диалога «Связи».

Тип создаваемой связи зависит от полей, которые были указаны при определении связи:

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

- отношение «один-к-одному »   создается в том случае, если оба связываемых поля являются уникальными или ключевыми;

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

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

В окне диалога «Схема данных» можно не только устанавливать связи между таблицами, но и выполнять следующие действия:

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

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

ü удалять связь;

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

ü вывести на экран все существующие связи или связи только для конкретной таблицы;

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

Связывание двух полей одной таблицы

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

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

Изменение структуры таблицы в окне «Схема данных»

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

Для удаления связи нужно выделить последнюю и нажать клавишу Delete. Для удаления таблицы из схемы данных ее нужно выделить и нажать Delete (только из одного окна, а не из базы данных).

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

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

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

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

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

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

В лекции "5.3. Процессы ЖЦ ПО" также много полезной информации.

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

Данное условие делает доступным следующие два режима:

ü каскадное обновление связанных полей;

ü каскадное удаление связанных полей.

Если данные режимы не установлены, то при удалении, например, записи из таблицы Клиенты будут появляться сообщения о невозможности выполнения данной операции в том случае, если в таблице Заказы есть заказы, относящиеся к данному покупателю.