Глава 2. Проектирование таблиц

Прежде чем приступить к проектированию таблиц, нужно открыть имеющуюся базу данных или создать новую. Чтобы создать новую базу данных, запустите MS Access (например, Пуск\Программы\Microsoft Office\Microsoft Office Access 2003).

Следующие действия по созданию файла базы данных зависят от версии пакета Microsoft Office. Это может быть вводное окно (в версиях до Microsoft Office 2000 включительно или область задач в Microsoft Office XP). Мы повсюду в этой книге будем придерживаться последней на текущий момент версии Microsoft Office Access 2003

В области задач щелкните по гиперссылке «Новая база данных…». Теперь нужно дать имя файлу базы данных и указать папку, в которой она будет храниться. Выполнив эти действия, нажмите кнопку "Создать".

В отличие от многих других приложений, в которых создаваемые документы могут быть записаны на диск в любое время, в Access файл базы данных сразу создается на диске. Это обусловлено спецификой работы с базами данных, требующей предварительного создания структуры, в которой будут впоследствии сохраняться все объекты базы данных. Поэтому команда Сохранить в меню Файл будет сохранять текущий объект (таблица, форма, отчет и т.д.) внутри уже имеющегося файла базы данных. В то же время сохранение данных (записей) может быть выполнено автоматически, когда редактируемая запись перестает быть текущей, т.е. при переходе к любой другой записи. Если же по какой либо причине переход к другой записи после редактирования текущей нецелесообразен, для сохранения записи можно выполнить команду "Записи\Сохранить запись".

При открытии или создании базы данных на экране появляется главное окно базы данных. Заметим, что закрытие этого окна приводит к завершению работы приложения, но не завершает работу с Access. Главное окно базы данных имеет ряд вкладок, каждая из которых содержит соответствующие объекты базы данных (формы, отчеты, запросы…) и стандартные кнопки – Открыть, Конструктор, Создать. Помимо этих кнопок, непосредственно в окне имеется несколько гиперссылок для более быстрого выбора команды создания объекта. Как правило, это мастера и режим конструктора.

Поэтому, чтобы создать таблицу базы данных, нужно в окне базы данных перейти на вкладку Таблицы, выбрать одну из гиперссылок или нажать кнопку Создать. Во втором случае на экране появится диалоговое окно выбора режима создания таблицы . Режим таблицы позволяет сразу вводить информацию в таблицу с принятыми по умолчанию именами столбцов (полей); при этом тип данных поля будет определен по типу вводимой информации. Изменить имя столбца можно, если выполнить двойной щелчок по имени поля.

Окно конструктора таблиц разбито на две части. В верхней части окна в столбце Имя поля вводятся имена полей (до 64 символов с учетом пробелов), после чего в соседнем столбце из списка выбирается тип данных. В столбце Описание можно ввести поясняющий текст. Справку по типам данных можно получить, если нажать кнопку F1 на клавиатуре, когда курсор находится в столбце "Тип Данных".

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

Подпись – текст, вводимый в это поле, будет использоваться в качестве заголовка столбца в режиме Таблица, а также в качестве присоединенной надписи для элемента управления на форме, в котором будут выводиться значения этого поля таблицы. Если оставить это поле пустым, то будет использоваться имя поля таблицы.

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

Условие на значение – Microsoft Access автоматически накладывает условия на значение, определяемые типом данных поля; например, не допускается ввод нечисловых символов в числовые поля. Свойство Условие на значение позволяет указать логическое выражение, которое будет проверяться при редактировании или вводе новой информации в данное поле таблицы. При нарушении этого условия (значение логического выражение ложно) будет появляться сообщение об ошибке. Сохранить запись не удастся до тех пор, пока значение не будет приведено в соответствие с указанным условием.

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

Обязательное поле. Это свойство может иметь одно из двух значений – Да/Нет. Если поле определить обязательным, то нельзя будет сохранить запись с незаполненным значением в данном поле. Введите значение “Да” в это поле, если наличие информации в данном поле таблицы необходимо для правильного функционирования приложения. Например, обязательными можно считать поля, содержащие наименование товара, дату его приема, цену и т.д. Незаполненному полю записи соответствует специальный маркер, называемый NULL-значением. Это значение является характерной особенностью реляционной теории и имеет смысл отсутствия информации или неприменимости свойства объекта, описываемого значением данного поля.

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

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

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

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

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

Чтобы создать составной индекс в MS Access, нужно в режиме конструктора требуемой таблицы выполнить команду Вид\Индексы. Затем в окне Индексы в столбец Индекс ввести название индекса, а в столбце Имя поля перечислить подряд одно за другим все необходимые поля индексируемой таблицы, не повторяя в каждой строке название индекса. При этом имена полей можно выбирать из ниспадающего списка. Кроме перечисленных действий в столбце Порядок сортировки следует указать, как будут сортироваться значения по каждому полю индекса. Легко понять, что порядок сортировки будет ускорять выборку, особенно в случае повторяющихся значений индексных полей (неуникальный индекс). Отметим, что для таблицы может быть создано несколько индексов. Создавая большое число индексов, мы можем получить выигрыш в производительности за счет избыточности хранения, поскольку каждый индекс будет занимать место на диске. Обычно индексы создаются в соответствии с назначением базы данных и, как правило, включают те поля таблицы, по которым предположительно будет вестись поиск в запросах к базе данных. Если заранее нельзя определить, из каких полей (кроме первичных ключей, для которых индекс создается автоматически) и сколько индексов требуется создать для той или иной таблицы, к этому вопросу можно вернуться уже в процессе эксплуатации базы данных, проанализировав производительность и характер выполняемых запросов.

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

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

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

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

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

Свойство Порядок сортировки определяет порядок сортировки записей в таблице. Значение этого свойства задается с помощью строкового выражения, содержащего имена полей, по которым проводится сортировка записей. Если указано несколько имен, необходимо разделять их запятой «,». Если в значении свойства Порядок сортировки заданы имена одного или нескольких полей, будет выполнена сортировка по возрастанию. Для сортировки записей по убыванию следует ввести ключевое слово DESC после имени соответствующего поля. Например, для таблицы Продажи задана сортировка записей по двум полям – дате продажи (по убыванию) и коду товара (по возрастанию).

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

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

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

MS Access помогает формально выполнить нормализацию таблиц, о которой шла речь в главе 1. После создания таблицы можно воспользоваться одной из сервисных программ, называемых мастерами (в оригинале – Wizard). Для этого нужно выполнить команду Сервис\Анализ\Таблица. Мастер разбивает таблицу, которая создана с нарушением условий нормальности, на ряд таблиц, связанных при помощи внешних ключей. Мы еще поговорим о связях при обсуждении схемы данных учебной базы данных

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

Чтобы установить связи между таблицами, нужно проделать следующее:

1)Выполнить команду Сервис\Схема данных…

2)С помощью контекстного меню или меню Связи добавить на макет схемы данных необходимые таблицы (команда Добавить таблицу…).

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

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

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

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

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

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

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

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