Архитектура СУБД ACCESS

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

Среди достоинств Access можно выделить также следующие:

1. Простота освоения. Возможность использования непрофессионалом;

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

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

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

5. Тесная интеграция с остальными компонентами MS Office.

Так как Access является интерпретатором, появляется необходимость в совмещении в одном приложении функций разработки приложения и собственно функций готового приложения. Для доступа к объектам базы в режиме разработки служит специальное окно БД, содержащее список всех объектов БД. С каждым объектом БД можно работать в двух режимах. Первый режим можно условно назвать режимом выполнения или рабочего состояния. Второй режим – это режим конструктора, в котором производится создание и модификация объекта. Наличие окна БД определяет открытую БД. Закрытие окна приводит к закрытию БД. После полной разработки БД появление данного окна можно отключить. Исключив также все команды разработчика из меню и панелей инструментов, можно создать распространяемую версию БД. Такая база данных Access будет функционировать при условии наличия на компьютере СУБД Access. При необходимости можно воспользоваться пакетом Access Developer Toolkit для создания инсталляционной версии БД, где СУБД Access будет представлена несколькими файлами DLL. Для работы с распространяемой версией БД пользователям предоставляется интерфейс, обычно содержащий главную переключательную (по числу решаемых задач) и несколько обычных форм.

База данных, написанная на Access, состоит из объектов. Access называет объектами все, что может иметь некоторое имя. Основными объектами в базе данных Access являются таблицы, запросы, формы, отчеты, макросы и модули. В новых версиях Access имеются также страницы доступа к данным, позволяющие создавать Web интерфейс БД, и проекты, служащие для создания клиентских приложений для работы с MS SQL Server. Рассмотрим основные характеристики основных объектов базы данных.

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

Запрос.Объект, который позволяет пользователю получить нужные данные из одной или нескольких таблиц. Запросы позволяют выбрать только нужные данные, соответствующие определенному критерию. Для создания запроса предоставляется два вида языков: QBE (Query By Example - запрос по образцу) и SQL. Можно создавать запросы на выбор, обновление, удаление или на добавление данных. С помощью запросов существует возможность создавать новые таблицы, используя данные одной или нескольких существующих таблиц и т.д. В Access не делается различия между запросами и таблицами. Соответственно запрос можно основывать и на других запросах, что очень удобно.

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

Формы также предназначены для автоматизации приложения, так как являются средством организации интерфейса в Access. С помощью формы можно в ответ на некоторые события запустить макрос или процедуру, написанную на языке Visual Basic for Application (VBA). С помощью макросов или процедур VBA можно связать несколько форм или отчетов между собой. Например, находясь в одной форме можно открыть другую форму и связать выводимые в них данные таким образом, чтобы при переходе от записи к записи в этой форме в другой форме выводились соответствующие данные.

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

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

Макрос.Объект, представляющий собой структурированное описание одного или нескольких действий, которые должен выполнить Access в ответ на определенное событие.

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

Модуль.Объект, содержащий программы на VBA, сохраненные под общим именем. Хотя в Access можно без труда создать приложение, состоящее только из таблиц, форм, отчетов и макросов, все же возникает потребность выполнения таких действий, которые нельзя запрограммировать с помощью макросов. Необходимость в модулях появляется также при необходимости обработки ошибок и оформления в виде некоторой процедуры или функции одинаковых вычислений или действий. Модули могут быть независимыми объектами, содержащими функции, которые можно вызывать из любого места приложения, но они могут быть непосредственно «привязаны» к отдельным формам или отчетам для реакции на те или иные происходящие в них события.

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

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

Рис. 7.1. Архитектура Microsoft Access

Разработка таблиц

В Access разработка таблиц производится с помощью конструктора (Рис. 7.2), а также с помощью Мастера или путем прямого ввода данных.

Рис.7.2 Окно конструктора таблиц

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

Access поддерживает 9 типов данных:

Тип Мемо используется для хранения текстовых массивов данных, превышающих 255 символов. Тип Дата/Время применяется для хранения календарных дат и/или значений времени и выполнения вычислений над ними. Данные типа Дата/Время хранятся в виде вещественного числа, целая часть которого определяет дату, дробная – время. Тип Счетчик является разновидностью числового типа (длинное целое) и создан для автоматической генерации значений первичного ключа (таблица не может иметь более одного поля типа Счетчик). Денежный тип данных удобен в денежных расчетах, так как предотвращает округления в процессе вычислений. Логический тип данных используется для хранения значений Истина и Ложь и особенно полезен для отметки выполнения некоторых действий или наличия чего-либо. Тип Поле объекта OLE позволяет хранить картинки, диаграммы, звуковые фрагменты и прочие объекты, созданные в приложениях, поддерживающих интерфейс Object Linking and Embedding (OLE). OLE позволяет одним приложениям совместно использовать объекты других приложений. После связи или внедрения объект отображается внутри клиентского приложения и остается доступен для редактирования в родительском приложении. Например, Access позволяет хранить и редактировать документы Microsoft Word, электронные таблицы Microsoft Excel, картинки Microsoft PowerPoint и Paint, звуковые файлы WAV и т.д. Данные типа Мемо и Поле объекта OLE хранятся в отдельной присоединенной таблице, разгружая основную таблицу от хранения больших объемов данных, и таким образом ускоряя операции загрузки, поиска, сортировки и фильтрации. И, наконец, в поле типа можно выбрать значение Мастер подстановок, позволяющий определить подстановку значений поля из фиксированного списка значений или из другой таблицы.

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

Свойства полей

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

Размер поля.Это свойство задает максимальный размер данных, для хранения которых предназначено данное поле. Поле с текстовым типом данных может иметь размер от 1 до 255 (символов); по умолчанию устанавливается 50 символов. Для числового типа данных размер поля может быть следующим:

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

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

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

По умолчанию Access выводит числа в стандартном формате, а денежные значения – в денежном. Можно также создать пользовательский формат, который будет использовать Access, (в зависимости от того, будет ли число положительным, отрицательным, равным нулю или равным Null), определив до четырех соответствующих спецификаций формата, разделенных точкой с запятой. Рассмотрим некоторые полезные спецификации формата:

Например, если есть желание вывести число с двумя десятичными знаками после десятичной точки, запятой в качестве разделителя тысяч, если число положительное, и заключенным в круглые скобки и красным цветом символов, если число отрицательное, “Ноль”, если равно 0, и “Не введено” для пустого значения Null нужно ввести следующее:

#, ##0.00; (#, ##0.00) [Красный]; “Ноль”; “Не введено”

Для текстовых данных также можно задать пользовательский формат, который может состоять из одной, двух либо трех частей. Если включена вторая спецификация, то Access будет использовать ее для вывода значения Null; если включена третья часть, то Access будет использовать вторую часть для вывода пустой строки, а третью для Null. Для текстовых значений определены следующие спецификации формата:

Например, формат вида

>; “Безразлично”; “Не определено”

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

** @@@@@@@@

записывает сумму чека прописью и заполняет свободное пространство звездочками, например: “Ten Dollars and*********50 Cents”.

Формат вывода логических данных также состоит из трех частей, хотя первая из них не используется. То есть первая часть спецификации формата оставляется пустой, далее следует формат для значения Истина, а за ним формат для значения Ложь. Например,

;“Счет выписан” [Красный]; “Счет не выписан” [Синий].

Число десятичных знаков.Для числового и денежного типов данных можно задать число знаков, выводимых после десятичной точки. По умолчанию устанавливается значение Авто, при котором для Денежного, Фиксированного, С разделителями тысяч и Процентного форматов поля выводятся два десятичных знака после запятой, а для Стандартного формата число выводимых знаков определяется точностью числовых значений. Можно задать фиксированное число десятичных знаков от 0 до 15. Игнорирование задания формата для вещественных чисел приводит к серьезным ошибкам. Так как числа выравниваются по правому краю, то при их отображении в окошке ввода/вывода небольшого размера может поместиться не все число, а только его дробная часть. Значимая целая часть может быть вообще проигнорирована.

Маска ввода.Для текстового, числового и денежного типов данных, а также для типа Дата/Время можно задать маску ввода, которая будет контролировать ввод данных с клавиатуры с помощью некоторого шаблона. Маску можно использовать для выполнения таких простых действий, как преобразование всех введенных символов к верхнему регистру или более сложных, например, добавление скобок и разделителей к номеру телефона. Можно также включать постоянные строки символов, которые затем будут отображаться на экране и по вашему желанию включаться в таблицу наравне с вводимыми. Например, можно установить, чтобы Access выводил разделители _/_/_ для поля типа Дата, или задать маску ввода паспорта в виде Серия __ № _______.

Маска ввода состоит из трех частей, разделенных символом точкой с запятой. Первая часть содержит собственно маску ввода, состоящую из символов, указанных в таблице; необязательная вторая часть указывает, требуется ли сохранять постоянные символы маски в этом поле. Если постоянные символы маски должны быть включены, то указывается “0”, если надо сохранять только введенные пользователем символы, то “1”. Необязательный третий компонент задает символ-указатель. По умолчанию используется ‘_’. Чтобы отобразить пустую строку, можно ввести пробел, заключенный в кавычки (" "). Маска ввода задается с помощью следующих символов:

Например, маска ввода номера телефона: “8- (”####”) “##0\-####,

маска ввода паспорта: “Серия ”LL” №0000000.

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

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

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

Значение по умолчанию.Можно определить значение поля по умолчанию для всех типов полей, кроме Счетчика, Мемо и Объекта OLE. Например, для числовых полей, участвующих в некоторых вычислениях, желательно установить значение поля по умолчанию в 0 или в 1, в зависимости от того, в какой операции будет участвовать это поле, в сложении или в умножении. В противном случае, если это поле будет пустым, то и весь результат операции будет равен пустому значению.

Условие на значение.Можно задать выражение, которое при вводе или редактировании значения этого поля всегда должно быть истинным (при этом название поля опускается). Например, <100 означает, что значение поля должно быть меньше 100. Условие задается логическим выражением, которое в общем случае состоит из операторов сравнения и значений, используемых для сравнения. Его элементами могут быть операции сравнения <, <=, >, >=, =, <>, логические операторы NOT, AND, OR, скобки и функции IN, BETWEEN, LIKE. Строки заключаются в кавычки, а константы типа Дата/Время – в символы #.

Функция IN проверяет на равенство любому значению из списка:
IN (“Минск”, “Москва”, “Киев”);

Функция BETWEEN задает диапазон значений. Границы диапазона разделяются оператором And: BETWEEN 50 And 100.

Функция LIKE проверяет на соответствие заданному шаблону символов. В качестве символов шаблона используются:

* – любое число произвольных символов;

? – один произвольный символ;

# – одна произвольная цифра;

[] – диапазон допустимых символов. К примеру, [А - Я], [3 - 9]. Если же эти символы необходимо исключить, то перед ними ставится !: [!А - Я]. Например,
LIKE “######” проверяет почтовый индекс, LIKE “*@*” – наличие символа @ в электронном адресе, а LIKE “####[К-НХВАРОСМТ][К-НХВАРОСМТ][К-НХВАРОСМТ]” – номер автомобиля.

Можно также задать условия на значения между двумя полями одной и той же таблицы, которые Access будет использовать для проверки каждой записываемой строки. Это условие проверяется только при сохранении записи, так как в таком условии одно поле сравнивается с другим, а пока вы не начнете сохранять запись, Access может не знать, окончательно вы ввели данные или потом захотите их изменить. Следовательно, это условие нельзя записать в свойстве Условие на значение отдельного поля. Для задания условия такого вида необходимо выбрать команду Свойства таблицы меню Вид. Имена полей заключаются в квадратные скобки [ ], а в строке Сообщение об ошибке задается свое предупреждающее сообщение. Например, имеет смысл проверить, не превышает ли дата увольнения даты приема на работу [Дата увольнения] > [Дата приема].

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

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

Пустые строки.Для Текстовых и Мемо полей можно разрешить ввод пустых строк. Реляционные базы данных обеспечивают возможность хранения в полях таблиц специального значения Null, называемого пустым значением, для обозначения некоторого неизвестного значения. Пустое значение имеет особые свойства. Пустое значение не может быть равно никакому другому значению, в том числе другому пустому значению. Это означает, что невозможно объединить две таблицы по значению Null. Условие «А = В», где А или В содержит значение Null, имеет значение Ложь. Пустые значения также не учитываются в групповых функциях, таких как Sum (Сумма) или Avg (Среднее значение). Можно лишь проверить наличие значения Null, сравнив значения поля со специальной константой NULL или используя встроенную функцию IsNulI. С другой стороны, можно установить значение Текстового или Мемо поля равным пустой строке (""), и это будет означать, что значение поля известно, но поле пустое. При сравнении пустые строки считаются равными друг другу, следовательно, можно соединять таблицы по пустым строкам и даже подсчитывать их количество. Для того чтобы разрешить пользователю ввод пустых строк в Текстовые и Мемо поля, следует установить свойства Пустые строки в значение Да. Если этого не сделать, Access преобразует введенные пользователем пустые строки, а также строки, состоящие только из пробелов, в строки, содержащие значение Null.

Почему же так важно различать строки со значением Null и пустые строки? Приведем пример: предположим, у вас имеется база данных, в которой хранятся результаты опросов клиентов о предпочитаемых ими характеристиках автомобилей. Если бы в анкете отсутствовал ответ на вопрос о предпочитаемом цвете автомобиля, это соответствовало бы хранению в этом поле значения Null. При этом не имеет смысла сравнивать «неизвестное» значение с другими и использовать эту строку при вычислениях итогового и среднего значений. С другой стороны, некоторые клиенты могли бы ответить на вопрос о предпочитаемом цвете: «Безразлично». В этом случае вам известен «неопределенный» ответ на ваш вопрос, и вы ставите ему в соответствие пустую строку. Вы можете подсчитать все «неопределенные» ответы и включить их в расчеты итогового и среднего значений.

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

Индексированное поле.Для полей с типом данных Текстовый, Числовой, Денежный, Дата/Время и Счетчик можно задать построение индекса с целью ускорения доступа к данным, поиска и сортировки. При создании индекса можно также включить проверку на уникальность значений индексируемого поля. С одной стороны, индексы ускоряют поиск и доступ к данным, с другой – замедляют все операции обновления данных, поскольку при этом производится обновление всех индексов, определенных для таблицы. Поэтому индексы рекомендуется создавать лишь для тех полей, по которым наиболее часто выполняется поиск либо сортировка. Поля Mемо, Гиперссылки и Объект OLE не допускают индексирования.

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

Рис. 7.3. Окно определения индексов

Сжатие Юникод.Для хранения данных типа Текстовый, Поле Mемо или Гиперссылка используется кодировка Юникод. В Юникод каждый знак представляется 16 битами, поэтому для хранения данных в полях типа Текстовый, Поле Mемо и Гиперссылка требуется больше места, чем в Microsoft Access 97 или более ранних версиях, в которых каждый знак представлялся одним байтом. Когда свойство Сжатие Юникодполя имеет значение Да, все знаки, первый байт которых равен 0, будут сжиматься при сохранении и восстанавливаться при выборке. Так как первым байтом латинских букв является 0, то кодировка Юникод при включенном сжатии не накладывает дополнительных требований к объему дискового пространства, необходимого для хранения в полях данных, состоящих только из латинских букв.

Задание свойств Размер поля, Маска ввода, Условие на значение, Значение по умолчанию,Обязательное и Индексированное поле являются очень важными, поскольку определяют целостность данных. Свойства Размер поля, Маска ввода, Условие на значение и Значение по умолчанию задают целостность значений; свойство Обязательное поле – целостность записи, поскольку гарантирует отсутствие полностью пустых записей; определение индекса с контролем уникальности значений задает целостность ключей, поскольку гарантирует отсутствие повторяющихся значений потенциальных ключей.

7.2.2 Задание ключевых полей и создание связей между таблицами

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