СИСТЕМА УПРАВЛЕНИЯ БАЗАМИ ДАННЫХ MICROSOFT ACCESS
Предлагаемый цикл лабораторных работ предназначен для формирования основных навыков работы в системах управления базами данных (СУБД). Обучение проводится на основе программного продукта Microsoft Access 2000. Он позволяет создавать, редактировать и обрабатывать реляционные базы данных, в которых текстовая, числовая, графическая и иная информация сгруппирована в двумерные таблицы, которые должны обладать рядом свойств:
· каждый элемент таблицы представляет собой один элемент данных;
· повторяющиеся элементы отсутствуют;
· все столбцы в таблице однородны, характеризуют один и тот же параметр и имеют уникальное имя; совокупность данных в одном столбце образует множество, которое называется полем таблицы;
· совокупность данных в одной строке таблицы называется записью и содержит информацию об описываемом объекте; в таблице не должно быть двух одинаковых строк;
· при обработке таких таблиц их строки и столбцы могут быть использованы в любом порядке независимо от их содержания.
MS Access – это одна из самых популярных СУБД общего назначения, работающая в различных версиях операционных систем Windows. Она является частью пакета Microsoft Office, полностью совместима с программными средствами Word и Excel и имеет схожий интерфейс.
ОПИСАНИЕ УЧЕБНОГО ПРИМЕРА
Для освоения основ работы сСУБД MS Access будем использовать следующий пример.
Рассмотрим работу некоторого научно-консультативного центра, который приглашает специалистов для чтения лекций и проведения консультаций по различным научно-техническим и другим проблемам. Очевидно, администрация центра должна иметь информацию о каждом эксперте – фамилия, ученая степень, должность, место работы, адрес, телефоны, электронный адрес, область компетенции. Необходимо также знать сведения об организациях, где они работают – название, ведомство, адрес, телефон, факс и т.п. Кроме того, необходимо накапливать сведения о работе, выполненной экспертами по заявкам центра, а также об оплате их услуг. При желании всю эту информацию можно разместить в одной большой таблице. При этом в столбцах таблицы (полях) будет собрана информация определенного типа (фамилия, степень, должность...), а строки будут содержать всю информацию о специалисте. Но такая таблица будет иметь существенный недостаток – многие сведения будут повторяться. Так, в строках, содержащих информацию о специалистах, работающих в одной организации, будут повторяться сведения об этой организации. Очевидно, такой недостаток ведет не только к избыточности информации, но и к ошибкам, которые неизбежны при вводе повторяющихся данных, а это послужит причиной ошибок при обработке информации. Такие проблемы можно избежать, если разбить исходную таблицу на несколько таблиц, содержащих сведения об отдельных объектах. Например, в нашем примере сведения об экспертах можно хранить в такой таблице:
Таблица 1. Эксперты
| Код эксперта | Фамилия | Ученая степень | Организация | Подразделение | Должность | Телефон | |
Сведения об организациях могут быть помещены в таблицу:
Таблица 2. Организации
| Код организации | Организация | Ведомство | Адрес | Телефон | Факс | Примечание |
Таблица для хранения данных о работе экспертов:
Таблица 3. Выполненная работа
| № | Дата работы | Фамилия | Кол-во часов | Тема |
Чтобы получить необходимые сведения из разных таблиц, необходимо создать связи, объединяющие эти таблицы. В нашем примере третья таблица может быть связана с первой через поле «Фамилия», присутствующее в обеих таблицах, первая со второй – через поле «Организация». Благодаря таким связям можно легко получить информацию, например, о том, специалисты каких ведомств работали в центре в определенные дни. Необходимая информация будет выбрана из третьей и второй таблиц, которые напрямую между собой не связаны.
Сейчас можно приступить к созданию базы данных научно-консультативного центра. В процессе ее создания будем исправлять и дополнять наши таблицы, а также создавать схему связей между ними.
СОЗДАНИЕ БАЗЫ ДАННЫХ. ТАБЛИЦЫ
Запуск программы Access в среде Windows осуществляется обычным образом, в результате на экран компьютера выводится пользовательский интерфейс со строками командного меню и пиктограмм, набор которых может быть изменен при помощи команды Вид/Панели инструментов/Настройка…, а также диалоговое окно с предложением открыть имеющуюся базу данных или создать новую.
Встроенная в Access справочная система предоставляет необходимую информацию о текущей ситуации. Кроме того, Access оснащен системой карточек подсказок. Ее особенность состоит в том, что диалоговое окно постоянно присутствует на кране. Это позволяет шаг за шагом выполнять действия, предлагаемые для решения выбранной задачи.
После выбора команды Новая база данных желательно ввести имя нового файла базы данных и указать папку, где он будет храниться. После этого на экране появляется окно базы данных, обеспечивающее доступ ко всем объектам базы – таблицам, запросам, формам, отчетам и т.д. (это же окно появляется, если выбрана команда Открыть базу данных).
Создание любой базы данных начинается с разработки таблиц. Таблицы могут быть созданы несколькими способами – в режиме конструктора, с помощью мастера или путем ввода данных. Наиболее удобным является режим конструктора, который предоставляет возможность непосредственно вводить имена полей, тип данных и примечания.
Как правило, первое поле таблицы используется для уникальной идентификации данных, образующих запись. Обычно такие ключевые поля, которые не могут иметь повторяющиеся элементы, называются «№» или «Код объекта». Ключевые поля (ключи) используются для однозначного определения каждой записи таблицы. Любая таблица должна иметь ключ, который присваивается одному или нескольким полям. Обычно в качестве ключа используются числовые поля. Например, поле «Код эксперта» должно однозначно определять каждую запись об эксперте.
Для выбора или изменения типа данных используются спускающиеся меню, которые вызываются щелчком мыши по соответствующей ячейке таблицы. Наиболее часто применяется тип данных ТЕКСТОВЫЙ, который допускает использование всех символов стандарта ASCII. Для хранения текстов, содержащих более 255 символов, используется тип данных MEMO, для которого размер поля выбирается автоматически. Такие поля не могут быть ключевыми и по ним не проводится сортировка.
Для полей первой таблицы установите следующие типы данных:
Таблица 1. Эксперты
| Поля | Типы данных |
| Код эксперта | Числовой |
| Фамилия | Текстовый |
| Ученая степень | Текстовый |
| Организация | Поле MEMO |
| Подразделение | Поле MEMO |
| Должность | Текстовый |
| Телефон | Текстовый |
| Текстовый |
Параметры выбранного типа данных отображены и могут быть изменены в окне "Свойства поля". Так, для текстовых полей можно задать размер поля (количество символов) или маску ввода при использовании определенных форматов записи (номера телефонов или формат даты, например). Для дополнительных параметров Access задает стандартные значения, поэтому желательно изменять их только в исключительных случаях. Перейдя в режим таблицы (пиктограмма "Вид", при этом можно присвоить таблице имя), приступите к вводу данных.
Создайте вторую и третью таблицы. В столбце "Имя поля" введите названия полей и для них выберите тип данных:
Таблица 2. Организация
| Поля | Типы данных |
| Код организации | Числовой |
| Организация | Текстовый |
| Ведомство | Текстовый |
| Руководитель | Текстовый |
| Адрес | Поле MEMO |
| Телефон | Текстовый |
| Факс | Текстовый |
| Примечание | Поле MEMO |
Таблица 3. Выполненная работа
| Поля | Типы данных |
| № | Числовой |
| Дата работы | Дата/Время |
| Код эксперта | Числовой |
| Кол-во часов | Числовой |
| Тема | Поле MEMO |
Сохраните создаваемую базу данных в вашей персональной папке. Эта база данных будет использоваться при дальнейшем изучении методов работы с СУБД.
СВЯЗИ МЕЖДУ ТАБЛИЦАМИ. ЗАПОЛНЕНИЕ И РЕДАКТИРОВАНИЕ ТАБЛИЦ
После создания таблиц следует приступить к установлению связей между ними через поля с одинаковым типом данных, содержащие однотипную информацию. Используется несколько типов реляционных связей между таблицами – один-ко-многим (используется наиболее часто), один-к-одному, многие-ко-многим. Тип связи и, возможно, конструкция базы данных позволяют выбрать главную и подчиненную таблицы в связываемой паре. Например, при создании связи типа один-ко-многим главной является таблица с индексом 1 (которая содержит уникальное значение в поле связи).
Необходимо поле связи в главной таблице сделать ключевым. Конструктор таблиц автоматически задал первичный ключ при первом сохранении таблицы. Чтобы определить в качестве первичного ключа иное поле, следует в режиме Конструктора таблиц установить курсор в соответствующее поле и включить пиктограмму Ключевое поле.
В первую очередь установите связь между таблицами Эксперты и Организации. Очевидно, связь может быть создана по полю, характеризующему организацию (название или код) и присутствующему в обеих таблицах. Причем, для увеличения скорости обработки информации желательно, чтобы поле связи было числовым. Поэтому предлагается отредактировать таблицу Эксперты, заменив поле Организация на поле Код организации. Для этого откройте таблицу Эксперты в режиме конструктора и измените название поля (что не обязательно) и тип данных на числовой (длинное целое). Поскольку рассматриваемые таблицы объединяются связью типа «один-ко-многим», то в качестве главной следует выбрать таблицу Организации.
В версиях Access более ранних, чем Access 2000, поле связи необходимо делать индексированным.
Для установки связи выполните команду Сервис/Схема данных. В диалоговом окне Добавление таблицы укажите названия связываемых таблиц. После этого при помощи мышки отбуксируйте поле Код организации из главной таблицы в поле связи подчиненной таблицы. Включите опцию Обеспечение целостности данных в диалоговом окне " Изменение связи " и нажмите кнопку Создать. Если все процедуры выполнены правильно, то связь будет установлена и возле линии связи появятся символы «1» и «
» (один-ко-многим). Если программа сообщает о невозможности создания связи или на схеме отсутствуют упомянутые символы, то следует исправить сделанные ошибки.
Свяжите таблицы Эксперты (главная) и Выполненная работа (подчиненная), используя для связи поле Код эксперта.
Заполните созданные таблицы, используя приведенные ниже данные. Причем, если был выбран режим обеспечения целостности данных, то сначала необходимо заполнить более главные таблицы. Access автоматически следит за обеспечением целостности данных. Запись в подчиненной таблице будет сохранена лишь в том случае, если введенное значение связующего поля присутствует в главной таблице. Запись в главной таблице можно удалить лишь в том случае, если она не связана с записями подчиненной таблицы. В нашем случае в таблице Эксперты коды организации можно ввести, если они уже имеются в таблице Организации. Если при редактировании данных нарушены правила целостности, то Access не сможет сохранить изменения и появится соответствующее сообщение об ошибке.
Ввод, изменение и удаление записей производится в режиме таблицы. Для создания новой записи следует нажать мышью пустую строку и приступить к вводу данных. Методы редактирования в Access такие же, как в Word и Excel. Особенностью является комбинация клавиш [Shift]+[F2], использование которой открывает окно Область ввода, которое удобно использовать при работе с большими текстами. Введите две записи в таблицу Организации, используя приведенные ниже данные. Вы почувствуете неудобства при заполнении таблицы.
Для упрощения процедуры ввода данных создайте Автоформу, используя пиктограмму Новый объект. Вы получите бланк для ввода данных в таблицу Организации, использование которого значительно упростит ввод. Позже Вы научитесь создавать и редактировать формы, а сейчас введите оставшуюся часть данных в таблицы.
Таблица 2. Организации
| Код организации | Организация | Ведомство | Руководитель | Адрес | Телефон | Факс | Примечание |
| МГЭУ | Министерство образования | Кундас С.П. | Минск | ||||
| Белгосуниверситет | Министерство образования | А.Козулин | Минск | ||||
| Комчернобыль | МинЧС | В.Цалко | Минск | ||||
| Институт физики | НАН Беларуси | С.Гапоненко | Минск | ||||
| Институт радиобиологии | НАН Беларуси | Е.Конопля | Минск | ||||
| Институт радиологии | МинЧС | В.Агеец | Гомель | ||||
| Институт леса | НАН Беларуси | А.Ипатьев | Гомель | ||||
| Институт радиационной медицины | Минздрав | Я.Кенингсберг | Минск | ||||
| Институт метрологии | Белстандарт | А.Гордеев | Минск | ||||
| Атомтех | Минпром | А.Кожемякин | Минск |
Таблица 1. Эксперты
| Код эксперта | Фамилия | Ученая степень | Код организации | Подразделение | Должность | Телефон | Электронный адрес |
| Альшевский В. | докт.ф.-м.н | физический факультет | профессор | alf@bdu.unibel.by | |||
| Бережнов А. | канд.т.н. | Институт экологических проблем | зам. директора | ||||
| Жуков Ю. | докт.б.н. | лаб. мониторинга | зав. лаб. | ||||
| Лещинская В. | докт.б.н. | факультет радиобиологии | зав. кафедрой | ||||
| Матусевич С. | канд.т.н. | факультет радиоэкологии | доцент | ||||
| Муркин В. | отдел мед. статистики | ст. н. сотр. | |||||
| Соболевский О. | управление науки | гл. специалист |
Таблица 3. Выполненная работа
| № | Дата работы | Код эксперта | Кол-во часов | Тема |
| 12.11.2002 | Организация радиоэкологического мониторинга | |||
| 15.01.2003 | Основы радиационной спектрометрии | |||
| 22.11.2004 | Радиоактивное загрязнение продуктов питания на территории Гомельской области | |||
| 15.03.2004 | Заболеваемость населения на радиационно-загрязненных территориях | |||
| 11.06.2004 | Радиационные риски | |||
| 13.09.2005 | Радиационные биологические эффекты | |||
| 26.02.2005 | Оценка поглощенных радиационных доз | |||
| 20.10.2005 | Миграция радионуклидов в почвах |
Произведите сортировку данных по различным полям используя пиктограммы или команду Записи/Сортировка.
Проведите поиск различных данных в таблицах, используя команду Правка/Найти.
ЗАПРОСЫ В ТАБЛИЦАХ
Формирование таблиц и заполнение их данными – основной этап в создании баз данных. Это необходимый и достаточный процесс с точки зрения хранения данных, но недостаточный с точки зрения доступа и использования информации. Запросы – это одно из основных дополнений к таблицам. Их назначение – обеспечить быстрый и эффективный доступ к хранящимся в таблицах данным для их обработки – отбора данных по разным критериям, проведения вычислений, создания таблиц с выборочными данными и т.п. При выполнении запроса СУБД Access собирает данные из разных таблиц, производит их обработку и представляет результат в виде новой таблицы. При этом результат запроса не сохраняется. В запросе хранятся только инструкции по обработке данных.
Запрос на выборку
В окне базы данных выберите объект Запросы и режим Создание запроса в режиме конструктора. Затем в открывшемся диалоговом окне Добавление таблицы необходимо выбрать необходимые таблицы. Затем при помощи мыши необходимо перетащить в нужной последовательности требуемые поля из разных таблиц в бланк запросов, а также задать условия отбора информации из таблиц. Нажатие пиктограммы Запуск приведет к созданию нужной нам таблицы.
В условии отбора можно использовать различные операторы сравнения: >, <, =, >=, <=, Between…And…, а также логические операторы сравнения And, Or, Not.
Например, чтобы отобрать данные о работе экспертов начиная с 1 января 2005 года, нужно в режиме конструктора в строке Условие отбора ( соответствующей полю Дата работы) добавить запись: >=01.01.05.
Отбор сведений о работе в 2004 году может быть выполнен условием отбора Between 01.01.04 And 31.12.014 или >01.01.04 And <31.12.04, а сведения о работе до и после 2004 года могут быть получены условием отбора <01.01.04 Or >=01.01.05.
Следующий пример. Если нужно найти сведения о работе кандидатов наук, то надо ввести условие отбора в поле Ученая степень, например: к*. Это означает, что отбор будет выполнен по тексту, начинающегося с буквы к с любыми последующими символами. Более подробные сведения об условиях отбора можно получить в справочной системе Access.
Создайте следующие запросы.
1. Сформируйте запрос на вывод записей таблицы Выполненная работа (выводить все поля), у которых дата работы 2003 и 2004 год, а количество часов больше 2.
2. Создайте запрос на вывод из базы данных записей следующей структуры:
§ Фамилия
§ Ученая степень
§ Организация
§ Дата работы
§ Тема
Установите в запросе условие вывода информации только о докторах наук.
Итоговые запросы
В том случае, если необходимо сгруппировать записи согласно определенным условиям и вычислить итоговые значения, используют итоговые запросы. В таких запросах разделяют два вида полей – по одним полям осуществляется группировка данных, по другим – вычисления. Например, итоговые поля удобно использовать для подсчета выполненной экспертами работы.
Создайте новый запрос из базы данных, содержащий поля Фамилия и Кол-во часов. Проверьте результат в режиме таблицы.
Вернитесь в режим конструктора и выполните команду Вид/Групповые операции. В появившейся строке для поля Фамилия выберите функцию Группировка, для поля Кол-во часов – функцию Sum из списка Групповая операция. В результате запроса будут сгруппированы записи с одинаковыми фамилиями и суммированы соответствующие часы.
Сохраните запрос под именем «Нагрузка».
Выполните аналогичный запрос с помощью мастера (командой Создать/Простой запрос).
