Системы управления базами данных

Основные этапы разработки базы данных

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

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

Разработка и описание структур таблиц данных

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

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

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

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

После выполненного проектирования будущих таблиц необходимо выполнить описание структур таблиц средствами СУБД. Это можно сделать в режиме конструктора. Можно использовать встроенные шаблоны таблиц или списков SharePoint, например, шаблоны "Контакты", "Задачи", "Вопросы" и "События". Если имена полей выбранного шаблона недостаточно информативны, их можно изменить. В режиме конструктора можно менять любые свойства полей, менять поля местами или добавлять новые поля. Использовать конструктор для модификации таблиц можно в любое время, даже после ввода данных в таблицу.

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

Поля могут иметь любой используемый в офисных приложениях тип данных: Счетчик, Текстовый, Числовой, Дата/время, Логический, Поле объекта OLE. Возможно использование поля комментариев (Memo). MS Access автоматически накладывает условия на значения, определяемые типом поля, например, не допускается ввод текста в числовые поля. Установка типа данных одновременно определяет набор дополнительных атрибутов соответствующего поля. В каждом поле могут храниться данные только одного типа. Изменение типа поля после ввода данных в таблицу может привести к потере данных.

Если поле используется в качестве первичного ключа таблицы, то оно может иметь любой тип данных. Часто применяется тип данных Счетчик, который гарантирует, что при вводе данных в это поле автоматически попадает номер записи в возрастающей последовательности. Можно задействовать в качестве первичного ключа определенный пользователем набор полей. МЕМО-поля и поля объекта OLE не могут быть первичными ключами. Во время ввода данных в поля первичного ключа MS Access автоматически следит за тем, чтобы в поля вводились уникальные или повторяющиеся значения в соответствии с заданным свойством Индексированное поле. Ключи не только идентифицируют записи таблиц, но и используются для установки межтабличных связей.

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

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

При проектировании базы данных можно предусмотреть возможности работы с внешними данными. Это импорт данных и связь с внешними таблицами данных. В случае импорта происходит создание дубликата внешних данных во вновь создаваемой таблице. К импортируемым данным могут применяться все средства манипуляции данными, обеспечивается быстродействие при обращении к ним и независимость от исходного источника данных. Однако возникают проблемы, связанные с поддержкой актуальности и соответствия друг другу двух копий одной и той же информации. Eсли актуальность данных является критичным фактором, то необходимо использовать связь с внешними данными. В этом случае в базе данных появляется лишь ссылка на внешние источники данных, и работа с ними происходит с помощью специальных драйверов, которые традиционно входят в пакет MS Access. Это драйверы для работы с данными, созданными в форматах Paradox, Excel, dBase, FoxPRO, а также в текстовом (txt) и гипертекстовом (htm) форматах. При связывании с внешними данными естественно возникают ограничения возможностей по управлению структурой таблиц, а также зависимость от состояния внешнего источника, к которому осуществляется подключение.

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

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

Механизм описания логических связей между таблицами в MS Access реализован в виде объекта, называемого Схемой данных. Схема данных (рис.8.5) представляет собой графическое изображение таблиц и их взаимосвязей. Если таблицы служат для физического хранения данных, то схема данных отражает связи между таблицами базы и возможности доступа к данным таблиц с использованием этих связей, т. е. доступ к данным на логическом уровне.

Для каждой таблицы, включенной в схему, отражены поля, выделен первичный ключ и связанные поля соединены линиями. Значки на концах линий указывают на тип отношения. На рис.8.5 все таблицы связаны наиболее часто встречающимся отношением "один-ко-многим". Все связываемые таблицы помещаются в окно схемы, при этом размещение всех таблиц базы данных в схеме не является обязательным.

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

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

MS Access автоматически следит за обеспечением целостности данных, если оно установлено (рис.8.6). К нарушению целостности данных приводят следующие действия:

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

В случае необходимости обеспечения целостности данных в MS Access предусмотрено задание дополнительных свойств для устанавливаемой связи (рис.8.6), а именно каскадное обновление значений связанных полей и каскадное удаление связанных записей. Каскадное обновление предусматривает обновление внешнего ключа во всех записях подчиненных таблиц при изменении значения поля связи в главной таблице. Т. е. если изменился бы код услуги в таблице Услуги, то автоматически код услуги заменился бы на новый в связанных записях таблиц Звонки и Контакты. Каскадное удаление приводит к удалению всех записей подчиненных таблиц, связанных с удаляемой записью главной таблицы. Если бы удалялась запись таблицы Услуги, то автоматически удалились бы связанные записи таблиц Звонки и Контакты.

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

Ввод данных в таблицы базы данных

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

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

На вкладке Внешние данные в группе Импорт можно выбрать формат импортируемого файла, например, импорт данных из MS Excel (рис.8.7). Желательно, чтобы на рабочем листе импортируемые данные располагались в виде списка, т. е. имели бы обозначения столбцов и окружались пустыми строками и столбцами. Можно импортировать именованные интервалы. Если в импортируемой таблице отсутствуют названия столбцов, то импортируемые поля получают имена по умолчанию. Если данные импортируются в новую таблицу, заголовки столбцов используются в MS Access в качестве имен полей в таблице. Эти имена можно изменить в ходе операции импорта или после ее завершения. Если данные добавляются к существующей таблице, то заголовки столбцов рабочего листа в точности должны совпадать с именами полей таблицы базы данных.

Импорт данных можно выполнить, открыв файл необходимого типа (рис.8.8). Импорт возможен и через буфер обмена.

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

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

Поиск и выборка данных из базы данных

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

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

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

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

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

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

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

В запросах можно использовать данные одной таблицы или нескольких связанных таблиц. Если таблицы не связаны в схеме, их можно связать в запросе. Если таблицы связаны в схеме, то в запросе можно изменить свойства установленной связи. Например, в запросе Стоимость звонка установлена дополнительная связь между таблицами Абоненты и Контакты (рис.8.12).

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

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

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

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

Колонки запроса могут содержать как поля источников данных запроса, так и выражения, построенные на основе этих полей (вычисляемые поля). В вычисляемых выражениях могут применяться встроенные функции. Поля данных могут переименовываться в запросе, например, поле нт на рис.8.12 и рис.8.13.

Важными отличиями между фильтрами и запросами являются следующие:

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

На рис.8.14 показано окно конструктора запроса, рассчитывающего суммарную стоимость звонков каждого абонента. При этом в бланк запроса включен запрос Стоимость звонков и таблица Абоненты, которые связаны в запросе по коду абонента. Операция суммирования значений нескольких записей запроса Стоимость звонков, осуществляемая для каждого абонента, определяется групповыми операциями Sum и Группировка, расположенными в одноименной строке. В процессе выполнения этого запроса суммируются стоимости звонков при постоянных значениях кода абонента и, соответственно, при постоянных значениях фамилии абонента, его имени и номера телефона. Результирующая запись – одна для каждого абонента (рис.8.15). При задании групповых операций доступны встроенные статистические функции.

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

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

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

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

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

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

Конструирование отчетов

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

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

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

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

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

На начальном этапе разработки отчета рекомендуется воспользоваться услугами мастера отчетов, который в диалоговом режиме создаст походящий набросок отчета, избавляя пользователя от многих рутинных операций, например, таких как добавление полей и подписей к ним или выбор стиля оформления отчета. Далее полученный макет отчета в режиме конструктора приводится пользователем к желаемому виду. На рис.8.20 видна структура отчета. Он состоит из нескольких областей (разделов): заголовка отчета, верхнего колонтитула, области данных, нижнего колонтитула и примечания отчета. Разделы содержат элементы отчета, которые в режиме конструктора представляются в виде полей разных типов и являются объектами редактирования. Некоторые области, как в данном случае примечание отчета, могут быть пустыми. В области данных располагаются поля записей источника отчета с содержательной для пользователя информацией. Здесь могут быть размещены вычисляемые поля, в том числе поле, позволяющее нумеровать строки отчета. При выполнении группировок и/или сортировок данных в отчете появляются заголовки и примечания групп, которые содержат поля записей источника отчета, используемые как вспомогательные для выполнения сортировки и группировки. Эти области располагаются в порядке, установленном разработчиком отчета.

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

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

Конструирование экранных форм для работы с данными

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

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

Формы являются основными средствами создания интерфейса пользователя – именно через формы можно управлять выполнением всего спектра задач приложения и автоматизировать действия пользователя.

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

К числу важных достоинств MS Access относится то, что работа с экранными формами универсальна и подобна работе с отчетами. Режим конструирования экранной формы аналогичен режиму конструктора отчета. Структура формы ориентирована на возможности представления табличных данных. Форма состоит из разделов, основные из которых – заголовок, примечание формы и область данных. Именно в области данных отображается информация, отличающая одну запись от другой, т. е. различные значения свойств объектов реального мира. Процесс проектирования формы в среде MS Access сводится к добавлению управляющих элементов и изменению их свойств. Можно добавить такие элементы управления, как надпись, поле, кнопка, флажок, переключатель, список, набор вкладок и др. (элементы управления показаны на рис.8.21). Помимо этого к форме можно подключать дополнительные элементы управления ActiveX, что значительно расширяет возможности представления данных. Каждый элемент управления обладает своим набором свойств, изменение которых приводит к изменению его вида и поведения, например, можно менять цвет, шрифт, изменять размеры элемента и т.п. Сама форма и ее разделы также рассматриваются как элементы управления и обладают настраиваемыми наборами свойств. Источником вычисляемого элемента, размещаемого в форме, является расчетная формула, построенная по правилам формирования выражений. Для упрощения записи выражений можно воспользоваться Построителем выражений.

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

Нажатие на кнопку Новый звонок вызывает загрузку формы, которая высвечивает данные только по выбранному абоненту, на что указывает выполняемая в форме фильтрация (рис.8.22). Это достигается за счет того, что MS Access позволяет отображать в объекте базы, вновь загружаемом при помощи командной кнопки, или все отобранные в запросе записи, или записи, которые относятся к субъекту, выбранному в основной форме. По сути, из единой формы Абоненты реализовано управление данными, находящимися в системе взаимосвязанных таблиц. Структура формы Новый звонок соответствует структуре таблицы Звонки. Но можно построить форму, в которой поля таблицы будут расположены в любом порядке.

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

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

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

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

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

Создание пользовательского приложения

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

Диспетчер кнопочных форм можно вызвать нажатием одноименной кнопки , расположенной на вкладке Работа с базами данных. Этот инструмент помогает сконструировать кнопочные формы различных уровней, т.е. помогает создать приложение базы данных, с которым впоследствии может работать любой человек. Кроме того, разработчик приложения может указать системе, какую форму следует загрузить при открытии базы данных, и тем самым полностью скрыть интерфейс СУБД.

На рис.8.23 отображена главная кнопочная форма, имеющая первый уровень иерархии. Нажатие на кнопки Просмотр форм или Просмотр отчетов высвечивает кнопочные формы второго уровня, которые содержат названия вызываемых форм или отчетов соответственно (рис.8.24). Нажатие на каждую из кнопок высвечивает выбранную форму или отчет. Возможен возврат на предыдущий уровень иерархии и выход из формы с закрытием приложения. MS Access.

Кнопочная форма состоит из страниц, каждая из которых имеет название и, в свою очередь, является формой. Одна страница кнопочной формы считается основной или главной: именно она высвечивается при открытии кнопочной формы. На каждой странице кнопочной формы располагаются кнопки и их обозначения. Для каждой кнопки определены некоторые действия, с нею связанные. Это может быть высвечивание следующей страницы кнопочной формы, отображение формы или отчета, выход из приложения и т.д. Для управления кнопочной формой в базе данных автоматически создается таблица Switchboard Items (рис.8.25), каждая строка которой описывает страницу формы или открываемые формы, отчеты и т.д.

Организация защиты данных в СУБД MS Access

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

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

По умолчанию в базе определены две группы пользователей: Admins и Users. Для каждой группы задаются разрешения на доступ к объектам базы данных (права доступа). Члены группы Admins, как правило, имеют разрешения на доступ ко всем объектам базы данных. Поскольку группа Users объединяет всех пользователей, имеет смысл ей присваивать некоторый минимальный набор прав. Можно создавать собственные учетные записи групп, предоставляя этим группам соответствующие разрешения и добавляя в группы пользователей, исходя из функциональной специфики предметной области. Один и тот же пользователь может являться членом различных групп.

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

Дополнительные режимы работы с базой данных

К этим возможностям относятся создание резервной копии, восстановление и сжатие баз данных, документирование объектов базы данных.

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

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

Восстановление базы используется после нарушения целостности файла БД, а преобразование базы необходимо для работы с базами, созданными в более ранних версиях MS Access.

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

Разделить базу данных можно вручную или при помощи служебной программы "Разделитель баз данных".

Средства макропрограммирования в MS Access

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

В MS Word и MS Excel имеется средство автоматической записи макросов – макрорекордер. После включения записи макроса можно создать процедуру, выполнив необходимые действия и остановив запись макроса. Все действия пользователя, выполненные между включением и выключением записи макроса, преобразуются в текст процедуры VBA. Этот текст затем можно просмотреть и отредактировать с помощью редактора VBA. Выполнение записанной процедуры равносильно повторению действий пользователя. В MS Access средств автоматической записи макросов нет. Автоматическая генерация кода VBA происходит только при создании объектов в режиме мастера.

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

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

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

Работа по созданию и запуску макросов выполняется так же, как и работа с любым объектом базы. Отличия определяются типом объекта. Макрос создается и редактируется в режиме конструктора. В окне конструктора выбирается макрокоманда из списка всех макрокоманд. Макрокоманды можно сгруппировать по категориям: работа с данными; выполнение; работа с объектами; импорт/экспорт данных и объектов, настройка интерфейса приложения. В категорию работы с данными входит набор макрокоманд, обеспечивающих отбор данных (фильтрация записей в формах и таблицах), перемещение по данным и элементам управления, а также обновление данных в активных формах, таблицах и запросах. К категории выполнения можно отнести выполнение команд, макросов, процедур или запросов, запуск другого приложения, прерывание выполнения и завершение работы в MS Access. Для настройки интерфейса можно вывести на экран или, наоборот, скрыть встроенную или пользовательскую панель инструментов, подать звуковой сигнал и т. п.

Наиболее обширная категория макрокоманд – это макрокоманды работы с объектами. MS Access состоит из нескольких компонентов, каждый из которых предоставляет собственный набор объектов, информация о которых содержится в библиотеках объектов. Например, библиотека объектов MS Access хранит информацию об объектах, предназначенных для отображения данных, для работы с приложением. В частности, это объекты формы или отчета. Макрокоманды для этих объектов позволяют в первую очередь выполнять копирование, переименование и сохранение объектов. Библиотека объектов DAO (Data Access Objects) предоставляет объекты доступа к данным, которые позволяют манипулировать данными из Visual Basic, т. е. управлять базами данных из любого приложения, поддерживающего VBA.

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

Выполнение макроса инициируется нажатием на кнопку Выполнить .

Можно нажать кнопку Выполнить макрос , расположенную в группе Макрос вкладки Работа с базами данных. В этой группе расположены различные пиктограммы для работы с макросами и процедурами Visual Basic (рис.8.26).

Наиболее часто макросы используются в приложении MS Access для обработки событий объектов. События – это любые действия, распознаваемые объектом, которые происходят в результате манипуляций пользователя, выполнения инструкций VBA или генерируются системой. Примером события является вывод формы на экран или нажатие кнопки. Для каждого объекта базы определены свои собственные события, перечень которых расположен на вкладке События диалогового окна свойств объекта (рис.8.27).

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

В форме Звонки абонентов (рис.8.28) размещена дополнительная командная кнопка Добавить звонок (имя кнопки n_call), при нажатии на которую открывается форма Новый звонок в режиме добавления данных. При этом открытие формы сопровождается звуковым сигналом. Для выполнения перечисленных действий создан макрос new_call (рис.8.29) из двух макрокоманд – открытия формы и подачи сигнала. Аргументы макрокоманды Открыть форму указывают на имя открываемой формы Новый звонок и режим данных Добавление. Указанный макрос выполняется автоматически при возникновении события Нажатие кнопки (рис.8.27).

Нажатие на кнопку Новый звонок (рис.8.28) открывает ту же форму, но в режиме просмотра данных. Для добавления новой записи в этом случае необходимо перейти к последней записи формы.

Разработка программных приложений для MS Access

Для программирования в MS Access используется процедурный язык Visual Basic for Applications (VBA) с добавлением объектных расширений и элементов SQL (более подробно о языке VBA см. раздел 3). Язык программирования VBA является общим инструментом для всех приложений MS Office, позволяющим решать любые задачи программирования, начиная от автоматизации действий конкретного пользователя и кончая разработкой полномасштабных приложений, использующих MS Office в качестве среды разработки.

Код VBA в приложении MS Access содержится в модулях. Модули являются такими же объектами MS Access, как таблицы, запросы, формы, отчеты и макросы. Основное содержание модулей – это процедуры на языке VBA. Модули, в отличие от макросов, являются более мощным средством разработки программных расширений в среде MS Access. Применение модулей требует от пользователя навыков программирования, а также знания основных принципов объектно-ориентированного программирования.

Если в базе данных существуют макросы, то они могут быть преобразованы в процедуры на VBA. Это касается как макросов, связанных с событиями в формах и отчетах, так и общих макросов, не связанных с формами и отчетами. Все они преобразуются в отдельные модули, причем макросы, выполняемые при возникновении событий, преобразуются в процедуры обработки событий. На рис.8.30 приведен преобразованный в процедуру макрос new_call, показанный на рис.8.29. На вкладке События для кнопки n_call (рис.8.27) в строке, соответствующей событию Нажатие кнопки, вместо названия макроса будет находиться текст [Процедура обработки событий], означающий вызов процедуры нажатия на кнопку n_call.

Процесс создания программных расширений в среде MS Access предполагает активное использование технологии объектно-ориентированного программирования (ООП). Каждый объект базы данных характеризуется набором свойств, методов и событий. Большое количество разнообразных событий определено для форм, отчетов и элементов управления, используемых в них. В процессе работы приложения при возникновении события запускаются специально разработанные программы – обработчики событий (событийные процедуры). Работа программиста, по сути, сводится к определению того события, которое требуется обработать программой, и программированию реакции на событие, а также к созданию новых объектов, описанию их свойств, методов и событий.

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

Важнейшей частью объектно-ориентированного программирования в MS Access является программирование доступа к данным, которое возможно благодаря использованию объектов DАО. С помощью этих объектов, их свойств и методов можно создавать базу данных, изменять структуру таблиц и запросов, устанавливать и изменять связи между данными, а также извлекать, добавлять, удалять и изменять данные в базе MS Access. Объекты DAO позволяют взаимодействовать с базами данных из любых приложений MS Office или работать с данными файлов разных форматов (например, MS Excel, dBase или Paradox), подключаться к базам данных на удаленных серверах и разрабатывать клиент-серверные приложения, используя источники данных ODBC.

Вопросы для самопроверки