Текст книги "Искусство создания базы данных в Microsoft Office Access 2007"

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

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

Для простого ключа должны быть установлены свойства: Обязательное поле (Да), Индексированное поле (Да). Если ключ составной, то все ключевые поля должны иметь свойство Обязательное поле (Да). Между тем, свойство Индексированное поле следует установить в состояние Нет, т.к. отдельное ключевое поле может иметь повторяющиеся значения.

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

Для определения первичного (простого или составного) ключа требуется выполнить действия.

1. Открыть таблицу в Конструкторе таблиц.

2. Выбрать поле, которое нужно использовать в качестве ключа. Чтобы выбрать несколько ключевых полей, следует при выделении полей удерживать клавишу Ctrl.

3. На вкладке Конструктор в группе Сервис щелкнуть команду Ключевое поле. Можно воспользоваться также командой Ключевое поле контекстного меню. В результате слева от имени каждого ключевого поля появится индикатор ключа ‒ символ «».

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

1. Открыть таблицу в Конструкторе таблиц.

2. Выбрать ключевые поля.

3. На вкладке Конструктор в группе Сервис щелкнуть команду Ключевое поле. Индикатор ключа исчезнет.

☞ Примечание. Базовая таблица может иметь только один первичный ключ. Чтобы его изменить, надо старый ключ удалить и определить новый первичный ключ. Эти действия возможны лишь в Конструкторе таблиц.

Когда все поля добавлены, необходимо сохранить таблицу. Для этого требуется выполнить действия.

1. Щелкнуть кнопку Microsoft Office.

2. Выбрать команду Сохранить.

3. Ввести имя таблицы, если таблица сохраняется впервые. Щелкнуть кнопку Ok.

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

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

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

• каждая связь устанавливает отношение между двумя таблицами, т.е. является бинарной. В этой связи одна из таблиц играет роль основной, а другая – подчиненной. Разделение ролей условное;

• связываемые таблицы должны иметь первичные ключи;

• связываемые таблицы должны иметь общие поля. Только через общие поля устанавливается связь между таблицами. Общие поля (их называют полями связи) – поля с одинаковыми именами, одним и тем же типом данных;

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

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

В Access всякая межтабличная связь имеет параметры, которые представлены в табл.1.5. Совокупность всех межтабличных связей базы данных называют схемой данных.

☞ Рекомендация. Не следует вводить данные в таблицы до формирования схемы данных, поскольку возможно нарушение целостности данных. Если в дальнейшем при связывании таблиц будет обнаружено нарушение целостности данных, Access не разрешит создать связь между таблицами, и потребует вначале удалить из них противоречивые данные.

Схема данных формируется следующим образом.

1. Открыть базу данных.

2. Перейти на вкладку Работа с базами данных.

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

4. В окне Добавление таблицы выбрать таблицы базы данных, и затем это окно закрыть.

5. Установить связи между таблицами. Определение каждой отдельной связи сводится к перетаскиванию поля связи из подчиненной таблицы в поле связи основной таблицы. Если связываемые таблицы равноправны, то направление перетаскивания не имеет значения. Если перетаскивание состоялось (об этом свидетельствует отображение символа «1» рядом с подчиненной таблицей и символа «∞» или «1» вблизи основной таблицы), открывается окно Изменение связи.

6. Задать параметры связи. Нажать кнопку Ok.

7. Схему данных сохранить.

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

Таблица 1.5

Хотя формат, используемый по умолчанию в Access для оформления таблиц, достаточно удобный и простой, при желании его можно изменить. Например, можно увеличить шрифт, изменить оформление ячеек, отменить или установить линии сетки, отрегулировать ширину столбцов и т.п. Средства форматирования таблицы доступны в Режиме таблицы на вкладке Главная в группе команд Шрифт. Если щелкнуть стрелку в правой части группы команд Шрифт, то откроется окно Формат таблицы, в котором можно установить дополнительные параметры форматирования внешнего вида таблицы.

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

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

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

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

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

Чтобы определить подтаблицу необходимо открыть Окно свойств исходной таблицы. Затем щелкнуть стрелку в свойстве Имя подтаблицы. Откроется список, в котором нужно выбрать имя таблицы, которая будет выступать в роли подтаблицы. В свойствах Подчиненные поля и Основные поля указать поля связи таблиц.

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

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

☞ Ограничение. В Access разрешено до восьми уровней вложенности подтаблиц.

Таблица 1.6

Если в таблице есть поля, имеющие тип данных Числовой или Денежный, то для них допускается вычисление статистических функций. Для этого на вкладке Главная в группе Записи следует щелкнуть команду Итоги. В последней строке таблицы выведется строка итогов. По умолчанию всегда отображается значение функции Сумма. Однако, при необходимости для каждого столбца можно выбрать свою статистическую функцию, либо вообще отменить итог, указав Нет. Повторный щелчок по команде Итоги скроет строку итогов.

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

☞ Ограничение. Запрещена сортировка по полям типа Вложение и Поле объекта OLE.

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

1. Открыть таблицу в Режиме таблицы.

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

3. В контекстном меню выбрать команду, определяющую порядок сортировки. Команды сортировки зависят от типа данных, содержащихся в выделенном столбце (табл. 1.7).

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

Таблица 1.7

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

Таблица 1.8

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

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

1. Открыть таблицу в Режиме таблицы.

2. На вкладке Главная в группе Найти щелкнуть команду Найти. Откроется диалоговое окно Поиск и замена.

3. Выделить поле, по которому нужно выполнить поиск.

4. На вкладке Поиск в поле Образец ввести значение, которое требуется найти. Это будет условием поиска.

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

6. В списке Совпадение определить режим сравнения с образцом: С любой частью поля, Поля целиком или С началом поля.

7. В списке Просмотр указать область поиска: Все ‒ поиск среди всех записей таблицы; Вверх ‒ поиск среди записей, расположенных выше выделенной записи; Вниз ‒ поиск среди записей, расположенных вслед за выделенной записью.

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

9. Чтобы закрыть окно Поиск и замена, необходимо щелкнуть кнопку Отмена.

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

☞ Примечание. Допускается при поиске и замене учитывать регистр и формат полей.

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

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

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

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

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

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

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

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

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

• значения полей, входящие в индекс, упорядочены;

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

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

☞ Ограничения. В Access индексированными не могут быть поля типа Вложение и Поле объекта OLE. Составной индекс не может содержать более 10 индексированных полей.

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

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

1. Открыть таблицу в Конструкторе таблиц.

2. Щелкнуть Имя поля для поля, которое следует проиндексировать – создать по нему индекс.

3. В области Свойства поля отрыть вкладку Общие.

4. В свойстве Индексированное поле установит значение Да (Допускаются совпадения), если следует разрешить повторяющиеся значения, или значение Нет (Совпадения не допускаются), чтобы создать уникальный индекс.

5. Изменения сохранить.

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

1. Открыть таблицу в Конструкторе таблиц.

2. На вкладке Конструктор в группе Показать или скрыть щелкнуть команду Индексы. Откроется окно Индексы, которое содержит бланк с тремя столбцами (Индекс, Имя поля, Порядок сортировки) и область Свойства индекса.

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

4. Внести в бланк сведения об индексах. Учесть, что в столбце Индекс указывается имя индекса, в столбце Имя поля – имена полей, входящих в индекс. Если индекс составной, то имя индекса задается только для первого поля. Для задания имен полей можно использовать выпадающий список всех полей таблицы. По умолчанию значения ключевых полей упорядочиваются по возрастанию. Однако этот порядок можно изменить, воспользовавшись столбом Порядок сортировки.

5. Установить свойства индексов: Первичный – значение Да, если индекс является первичным ключом; Уникальный – значение Да, если не допускается повторение значений индекса; Пропуск пустых полей – значение Да, если записи с пустыми индексированными полями следует исключить из индекса.

6. Изменения сохранить.

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

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