Тема №20

20 ТЕХНОЛОГИЯ СОЗДАНИЯ БАЗЫ ДАННЫХ В СИСТЕМЕ УПРАВЛЕНИЯ БАЗАМИ ДАННЫХ MICROSOFT ACCESS

20.1 Характеристика СУБД Microsoft Access

СУБД Microsoft Access (далее — Access) предназначена для работы с реляционными базами данных. Эта СУБД является компонентом офисного пакета Microsoft Office. Access представляет собой не просто средство для работы с базами данных, а мощный инструмент для создания специализированных информационных систем, предоставляющих пользователям доступ к данным в локальных и глобальных сетях. Эта СУБД имеет инструментальные средства для создания и работы:

• с локальными базами данных небольшого и среднего размера;

• централизованными базами данных в локальных сетях с файловым сервером;

• проектами — клиентскими приложениями, обеспечивающими эффективный, естественный доступ к базам данных Microsoft SQL Server.

При работе с многопользовательской базой данных СУБД Access может обслуживать одновременно до 255 пользователей.

В Access уделено большое внимание вопросам взаимодействия базы данных с сетями Интернет и Интранет — имеются средства для создания Web-страниц, обеспечивающих динамический доступ к данным базы.

Access поддерживает механизм OLE — связывание и внедрение объектов различных приложений Windows в базу данных. Эта СУБД позволяет импортировать данные таблиц:

• других баз данных Access;

• баз данных dBase, Microsoft FoxPro, Paradox, Oracle, Microsoft SQL Server;

• табличного процессора Microsoft Excel.

Внешними для Access могут быть также: данные почтовой программы Microsoft Exchange; таблицы и списки HTML на сервере локальной, корпоративной сети (сети масштаба предприятия), сети Internet. Access может экспортировать объекты базы данных в другие приложения, отправлять содержимое базы данных по электронной почте.

В данной СУБД имеется возможность разработки прикладных программ, работающих с базой данных, на встроенном языке программирования Visual Basic for Applications (VBA).

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

Эта СУБД снабжена развитой справочной системой.

20.2 Характеристика объектов базы данных

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

Таблица предназначена для хранения информации пользователя в базе данных. Ее строки — это записи, а столбцы — поля базы данных. Записи идентифицируются ключом таблицы. Таблицам и их полям присваиваются символьные имена. В них запрещено использование точки (.), восклицательного знака (!), надстрочного символа, прямых скобок ([]) и управляющих символов с кодами ASCII от 00 до 31.

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

СУБД Access позволяет создавать запросы трех типов: запросы на выборку, перекрестные запросы, запросы на внесение изменений в базу данных (активные запросы).

Запрос на выборку является наиболее часто используемым типом запроса. Он дает возможность:

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

• выбирать записи, удовлетворяющие условиям отбора;

• вычисления над полями базы данных;

• группировать записи и находить для групп записей или для всех записей итоговые значения по некоторым числовым полям с помощью статистических функций: Sum — сумма значений поля, Avg — среднее арифметическое значений поля, Мах — максимальное из значений поля, Min — минимальное из значений поля, Count — число значений поля и др.

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

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

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

Активные запросы существуют четырех типов:

1) запрос на удаление — удаляет группу записей из одной таблицы или нескольких взаимосвязанных таблиц базы данных;

2) запрос на обновление — служит для изменения значений полей таблицы;

3) запрос на добавление — производит добавление записей из одной таблицы в другую;

4) запрос на создание таблицы — создает новую таблицу на основе всех или части данных из одной или нескольких таблиц базы данных.

Формы, в СУБД Access используются для ввода в базу данных, просмотра, редактирования, удаления, фильтрации (отбора по критерию), сортировки записей.

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

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

СУБД Access позволяет создавать различные типы форм, например: простые; составные — включающие другие (подчиненные) формы; кнопочные — позволяющие открывать другие формы и просматривать отчеты.

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

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

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

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

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

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

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

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

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

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

СУБД Access позволяет создавать макросы, различные по структуре:

• линейные, в которых макрокоманды выполняются последовательно, одна за другой;

• макросы с условием, в которых отдельные макрокоманды или их наборы выполняются в зависимости от выполнения некоторого условия;

• групповые, в которых объединены несколько логически связанных макросов;

• макросы с циклом, в которых есть многократное выполнение другого макроса.

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

Модули — это программы для работы с базой данных, написанные на языке VBA.

Объекты базы данных могут быть объединены в именованные группы объектов по функциональному или иному признаку.

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

Страницы сохраняются как самостоятельные файлы с расширением .html вне базы данных, с которой они связаны.

Отчеты можно сохранять в файле с расширением .snp (формат снимка отчета) и распространять среди пользователей по электронной почте.

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

20.3 Инструментальные средства для создания объектов базы данных

СУБД Access имеет разнообразные инструментальные средства, дающие возможность непрофессиональным пользователям решать задачи без использования языка запросов или языка программирования VBA.

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

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

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

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

20.4 Данные в Access

СУБД Access может обрабатывать данные полей следующих типов.

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

Поле MEMO. Длинный текст (до 65535) символов.

Числовой. Числовые данные, используемые в математических вычислениях, за исключением денежных расчетов. Размеры числовых полей — 1, 2, 4, 8, 12 байтов.

Дата/время. Значения даты и времени. Даты из диапазона от 100 по 9999 год.

Денежный. Денежные значения. Их максимальная точность составляет 15 знаков слева от десятичной запятой и 4 знака справа от нее.

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

Логический. Поле этого типа может иметь только одно значение из пары значений, таких как Да/Нет, Истина/Ложь или Вкл/Выкл.

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

Гиперссылка. В поле этого типа хранятся гиперссылки — путь к файлу на жестком диске либо адрес в сети Интернет или Интранет.

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

Размер поля определяет максимальную длину текстового или числового поля. Так, для числового поля с целыми значениями используются следующие размеры:

• байт — целые числа от 0 до 255 (поле занимает 1 байт);

• целое — целые числа от -32 768 до 32 767 (2 байта);

• длинное целое — целые числа от -2 147 483 648 до 2 147 483 647 (4 байта).

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

Формат поля определяет способ отображения текста, чисел, дат и значений времени на экране и на печати.

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

Маска ввода задает маску ввода, облегчающую ввод данных в поле.

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

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

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

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

Обязательное поле указывает, требует ли поле обязательного ввода значения.

Пустые строки определяют, допускается ли ввод в текстовое поле пустых строк (« »).

Индексированное поле служит для создания индекса.

Сжатие Юникод разрешает или запрещает сжатие Unicode для поля.

20.5 Выражения в Access

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

Значениями могут быть константы, литералы, функции и идентификаторы.

В качестве констант в Access используются встроенные значения True, False, Null и пустая строка.

Литерал — это фактическое значение в виде числа, текстовой строки, даты, например, 100, «Минск», #1-янв-99#.

Функция — это небольшая программа, которая всегда возвращает значение (число или строку символов), являющееся результатом расчетов или выполнения других операций. К функции обращаются по имени, за которым в круглых скобках указывают ее аргументы. Имеются и безаргументные функции, например, Date(). В Access определены различные типы функций: статистические, математические, даты/время, текстовые, финансовые и др.

Идентификатор — это ссылка на значение поля, элемента управления или свойства. Например, Forms![Заказы]![ДатаРазмещения]. DefaultValue представляет ссылку на свойство DefaultValue элемента управления «ДатаРазмещения» в форме «Заказы». В квадратные скобки заключаются имена форм, отчетов, их элементов управления, полей. Символ «!» указывает, что следующий за ним элемент является элементом, определяемым пользователем. Он предопределяет ссылку на открытую форму, на открытый отчет или их элемент управления. Символ «.» указывает, что следующий за ним элемент определен в Access. Он предопределяет ссылку на свойства форм, отчетов и элементов управления.

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

Арифметические: + (сложение), — (вычитание), * (умножение), / (деление), % (процент), ^ (возведение в степень) и др.

Операторы сравнения: = (равно), > (больше), < (меньше), >= (больше или равно), <= (меньше или равно), <> (не равно).

Логические операторы: AND (логическое умножение), OR (логическое сложение), NOT (логическое отрицание).

Оператор сцепления строк — &.

Пример выражения:

= [СуммаЗаказа] + [СтоимостьДоставки]

Это выражение позволяет вывести в запросе сумму значений полей «СуммаЗаказа» и «СтоимостьДоставки».

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

20. Справочная система Access

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

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

Пункт Что это такое? предназначен для получения краткой справки о назначении элементов в окнах Access. Его выбор приводит к появлению указателя мыши в виде ↑?, щелчок им по интересуемому элементу дает сведения о нем.

20.7 Технология работы с СУБД MS Access

В качестве основных этапов обобщенной технологии работы с СУБД можно выделить следующие:

—запуск MS Access;

—создание БД;

—открытие БД;

—работа с БД: создание структуры таблиц базы данных; ввод и редактирование данных в таблицах, вывод информации из базы данных.

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

Создание БД. Для создания новой базы данных выберите в окне MS Access параметр Новая база данных и в открывшемся окне диалога Файл новой базы данных:

► выберите из раскрывающегося списка Папка папку, в которой предполагается сохранить создаваемую базу данных, а в поле «Имя файла» введите имя базы данных;

► нажмите кнопку Создать;

►создав файл, Access раскрывает пустое окно базы данных, и в этом окне можно проводить все операции — создавать и манипулировать объектами: создавать структуру таблиц, осуществлять ввод и редактирование данных в таблицах.

Более подробно с методикой создания новой базы данных с помощью СУБД Access 2010 можно познакомиться здесь.

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

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

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

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

Более подробно с методикой создания и редактирования таблиц новой базы данных с помощью СУБД Access 2010 можно познакомиться здесь.

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

—с помощью предоставляемой по умолчанию стандартной формы в виде таблицы;

—с помощью экранных форм, специально созданных для этого пользователем.

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

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

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

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

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

Каждый пользователь, работающий с СУБД, имеет возможность использования специальных средств построения отчетов для вывода данных. Используя специальные средства создания отчетов, пользователь получает следующие дополнительные возможности вывода данных:

►включать в отчет выборочную информацию из таблиц базы данных;

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

►при необходимости выводить итоговые данные на основе информации базы данных;

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

►включать в отчет информацию из разных связанных таблиц базы данных.

20.8 Создание новой базы данных

Создание новой базы данных предполагает следующие основные этапы:

—определение таблиц, которые должны содержать БД;

—определение необходимых полей в БД, их тип и размер;

—определение ключевых полей в каждой записи;

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

—отбор необходимых данных и ввод.

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

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

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

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

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

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

Соответственно, задача удаляет данные, когда они убираются из базы данных.

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