СОЗДАНИЕ БАЗ ДАННЫХ, ТАБЛИЦ,
РАБОТА С ТАБЛИЦАМИ В СРЕДЕ СУБД MICROSOFT ACCESS
ЦЕЛЬ РАБОТЫ
Знакомство со средой СУБД MS Access, приобретение практических навыков создания базы данных, таблиц БД, их индексирования и связывания, работы с таблицами, поиска данных в таблицах.
КРАТКИЕ ПОЯСНЕНИЯ
Обзор Access
Microsoft Access - самая популярная в мире система управления базами данных (СУБД). Она может использоваться как автономная (однопользовательская) и как сетевая СУБД в многопользовательской среде.Система MS Access позволяет:
- создавать реляционные базы данных как совокупность таблиц и сами таблицы;
- вводить и выводить информацию из БД в удобном для пользователя виде;
- создавать запросы для извлечения нужной информации и её обработки;
- разрабатывать формы, облегчающие ввод и редактирование информации;
- создавать отчеты, включающие данные, результаты их обработки, текст и графику.
Access имеет встроенные конструкторы таблиц, форм, запросов и отчетов, а также множество мастеров (Wizard), которые значительно упрощают создание баз данных с минимальным программированием.
Для профессиональной разработки приложений (прикладных программ) MS Access может использоваться язык программирования Visual Basic for Application (VBA) с добавлениями объектных расширений и языка структурированных запросов SQL. В системе программирования VBA MS Access используются многооконный интерфейс, выделение цветом при редактировании и отладке, автоматический контроль синтаксиса, динамический контроль данных, точки прерывания, пошаговое выполнение и т. д. Основы программирования на VBA изложены в ряде изданий, в частности, в книгах [2,3]. Краткие исходные сведения по языку VBA представлены в Приложении 1 к лабораторной работе.
Access 2007 предоставляет пользователю шесть различных типов объектов баз данных: таблицы, запросы, формы, отчеты, макросы и модули.
Фундаментом базы данных являются таблицы. Именно в таблицах хранятся данные. В Access могут храниться и обрабатываться данные разных форматов: текстовые, графические, числовые, календарные и др. Каждая строка таблицы соответствует отдельной записи, каждый столбец - полю данных. Для каждой логической категории данных создаётся отдельная таблица. Например, в одной таблице могут храниться сведения о товарах, во второй — сведения о заказах, а в третьей — сведения о клиентах. Создание таблицы заключается в определении состава и типа полей (столбцов), первичного ключа и их свойств. Поле первичного ключа уникально определяет каждую запись таблицы. С помощью общего поля данных можно установить связь между отдельными таблицами для совместной обработки данных в таблицах, например, для получения сведений о содержании заказов определенных клиентов. Для рационализации структуры таблиц базы данных используются процедуры нормализации.
По сравнению с предшествующими версиями в Access 2007 полностью изменен интерфейс пользователя. Основным средством интерфейса пользователя теперь является лента (полоса) Microsoft Office Fluent, которая состоит из наборов кнопок и команд (рис. 1). Ленту можно свернуть или развернуть. На ленте имеются все команды, которые необходимы для создания баз данных и приложений и для работы с ними. Под командой понимается кнопка, строка меню или поле для ввода данных. На ленте расположены вкладки. Каждая вкладка содержит группу связанных команд, которые могут открывать другие элементы интерфейса, например коллекцию.
В Access 2007 основными вкладками ленты являются Главная, Создание, Внешние данные и Работа с базами данных. Для создания объектов БД предназначена вкладка Создание. Выбрав одну из вкладок команд, можно увидеть доступные в ней команды. При работе с лентой для вызова команд можно использовать также сочетания клавиш («горячие» клавиши). Эти клавиши отображаются при нажатии Alt. Освоение инструментария ленты требует некоторой практики.
Рис.1. Лента интерфейса MS Access 2007.
В левой части окна Access расположена область переходов (панель навигации), в которой отображаются все объекты открытой базы данных. Область можно сворачивать и раскрывать. В ней размещаются ссылки на объекты базы данных: таблицы, формы, запросы, отчеты и др. Эти объекты можно упорядочить по категориям и по группам. При использовании категории Тип объекта все объекты в области переходов будут расположены в зависимости от их типа, например, таблицы, формы, отчеты и др. Чтобы открыть имеющийся объект, достаточно дважды щелкнуть по его имени в списке объектов.
Для работы с таблицами более других подходит режим Таблицы и связанные представления. При большом количестве объектов базы данных целесообразно использовать фильтрацию объектов в области переходов. Для этого можно щелкнуть правой кнопкой мыши на панели Все объекты Access и выбрать команду Параметры переходов. В открывшемся окне следует выбрать нужную категорию и снять флажки ненужных объектов в правой части окна. Двойной щелчок мыши по объекту открывает его, правая кнопка вызывает меню объекта.
Для быстрого доступа к наиболее часто используемым командам предназначена панель быстрого доступа. В неё путем настройки можно добавлять необходимые команды, что, как правило, и делается для удобства работы. Для изменения списка команд достаточно щелкнуть по правому значку панели и выбрать необходимые команды.
Вдоль нижнего края окна Access может отображаться строка состояния, которая служит для вывода сообщений о состоянии системы и свойствах текущей базы данных.
Создание БД и таблиц
В Access 2007 возможны следующие способы создания базы данных: создание базы данных с помощью шаблона (прототип готовой пустой базы данных с таблицами, формами и отчетами) и создание новой базы данных с нуля. Создание новой базы данных обычно начинается с создания пустой базы данных командой «Создать» или командой Новая база данных в списке шаблонов. При этом нужно задать имя новой базы и папку для её расположения.
Для создания новой таблицы в базе данных Access можно использовать три метода:
· Ввод данных в пустую таблицу, при этом структура таблицы (поля) создается Access автоматически,
· Использование мастера таблиц,
· Создание таблицы с помощью конструктора таблиц.
Возможно создание новой таблицы с помощью оператора CREATE TABLE языка SQL. Таблицы можно также копировать и импортировать из других баз данных.
Наиболее широкие возможности предоставляет режим конструктора. Для создания таблицы в этом режиме нужно выбрать команду Создание → Конструктор таблиц.
Вид окна Режима конструктора показан на рис. 2. На ленте появляется вкладка Конструктор.
Рис.2. Окно Режима конструктора при работе с таблицей.
Окно Режима конструктора таблицы состоит из двух частей:
- верхняя часть служит для определения имен полей, типа данных и описания полей,
- нижняя часть окна используется для определения свойств полей.
Для каждого поля вводится имя (не более 64 символов), тип данных, описание (комментарий к полю, необязательный, но нужный элемент) и определяются свойства. Желательно, чтобы имя поля было простым и коротким. Имена полей могут включать любые комбинации символов за исключением точки, восклицательного знака и квадратных скобок. Access позволяет использовать пробелы в именах полей, но делать это не рекомендуется, так как пробелы усложняют работу с объектами, в частности, в программах. Для имен полей часто используется так называемая «горбатая» запись, например, ДатаЗаказа или запись с использованием символа подчеркивания, например, Дата_заказа. Использование имен на английском языке позволяет упростить дальнейшее преобразование и экспорт таблицы в форматы других СУБД, например, MS SQL Server.
Тип данных должен обеспечивать наиболее эффективный способ хранения соответствующей информации в поле. Access поддерживает 10 типов данных. Наиболее распространенным типом является текстовый. Другие доступные типы данных: MEMO для хранения длинного текста, числовой, дата/время, денежный, счетчик для нумерации записей, логический, поле объекта OLE для графических данных, поля гиперссылка, вложение. У числовых полей можно менять размер поля, который определяет, сколько памяти выделяется для значения поля. Например, размер Байт позволяет хранить только числа от 0 до 2555. Более подробные сведения о типах данных см. в статье «Общие сведения о типах данных» справочной системы Access. В колонке «Описание » вводится подробное описание создаваемого поля.
С каждым полем таблицы связывается также набор свойств, которые могут быть использованы для настройки хранения данных в поле, методов работы с полем и способа его отображения на экране. Свойство «Размер поля » определяет максимальный размер данных, которые могут сохраняться в полях с типом данных Текстовый, Числовой или Счетчик. Свойство «Подпись » определяет заголовок поля, выводимый вместо имени в таблицах, формах и отчетах. Свойства «Маска ввода », «Значение по умолчанию », «Условие на значения » позволяют упростить ввод или обеспечить проверку ввода значений в соответствующее поле данных для исключения неправильного ввода. Например, для числового поля можно ввести Условие на значение «>50» и при этом в данное поле можно вводить только значения, превышающие 50. Кроме типа данных можно указать еще два атрибута поля - уникальность и обязательность. Первый атрибут означает, что данные в поле должны быть уникальными, например, код товара или штрих – код. Атрибут «обязательный» означает, что поле должно быть заполнено во всех записях, например, имя заказчика в одноименной таблице.
Для таблицы Access следует определять первичный ключ или ключевое поле для обеспечения уникальности записей таблицы. Первичный ключ гарантирует, что значения соответствующего набора полей (поля) будут уникальными, т.е. в таблице не будет дублирования записей. Для определения поля как ключа можно в контекстном меню (правая кнопка мыши) поля выбрать команду Ключевое поле или щелкнуть на значке Ключевое поле в меню Сервис.
После создания таблиц устанавливаются связи между ними для поддержания целостности данных и их совместной обработки. Связи между таблицами основаны на их общем поле. В большинстве случаев с первичным ключом одной таблицы, являющимся уникальным идентификатором для каждой записи, связывается внешний ключ другой таблицы. При этом одна из таблиц обычно является родительской (главной), а другая – дочерней (подчиненной). Возможны следующие основные типы связей: «один-к-одному », «один-ко-многим », «многие-ко-многим». Тип связи отражает соответствие связываемых записей в главной и подчиненной таблицах. В Access межтабличную связь можно создать с помощью окна «Схема данных », которое открывается одноименной кнопкой на панели Связи. В открытом окне связь устанавливается путем перетаскивания первичного ключа главной таблицы на внешний ключ подчиненной таблицы.
В режиме конструктора можно работать только с полями таблицы. Записи можно добавлять, удалять и редактировать только в режиме Таблицы. В группе Шрифт вкладки Главная имеются команды для оформления таблицы. В режиме таблицы можно также сортировать строки таблицы по текущему полю или по выделенным полям, используя соответствующие кнопки из группы Сортировка и фильтр.
Для отображения содержимого таблицы кроме обычного табличного режима в Access можно выбрать режим сводной таблицы или режим сводной диаграммы. Переключение режимов осуществляется кнопкой Режим вкладки Главная.
Более подробные сведения по созданию таблиц приведены в статье «Создание таблиц в базе данных» справочной система Access.
Разработка базы данных, как правило, не заканчивается созданием таблиц. Обычно после создания таблиц и связей между ними разрабатываются также формы, запросы, отчеты и приложение (программа), управляющее логикой обработки данных в Access. Эти темы составляют содержание последующих лабораторных работ.
ЗАДАНИЕ И ПОРЯДОК ВЫПОЛНЕНИЯ РАБОТЫ
1. Запустите систему Access. Откройте учебную базу данных «Борей». Эта база данных исключительно полезна для целей знакомства с БД и обучения их созданию. В ней хранится информация о вымышленной продуктовой компании: заказах, товарах, клиентах, сотрудниках компании и др. Для её открытия выберите в панели «Локальные шаблоны» значок «Борей 2007». Введите новое название для этой базы данных и щелкните кнопку Создать. При первом открытии этой БД появится окно, в котором предлагается выбрать из списка имя сотрудника и затем войти в систему.
2. С помощью области переходов и её категории Тип объекта выберите раздел (вкладку) Таблицы, последовательно откройте и просмотрите таблицы Поставщики, Клиенты, Заказы в режимах Таблица и Конструктор. Для перемещения по таблице используются клавиши управления курсором, полосы прокрутки, а также средства навигации в левом нижнем углу таблицы. Переименование и удаление таблицы осуществляется с помощью соответствующих команд контекстного меню таблицы в области переходов.
3. Ознакомьтесь с организацией и использованием Справочной системы Access. В частности, научитесь отыскивать нужную справку с помощью указателя и использовать контекстную справку.
4. Откройте таблицу Сотрудники. Выполните просмотр содержимого таблицы с перемещением по записям и полям, переход на запись с определенным номером, поиск и замену текста в полях, сортировку записей, в том числе по нескольким полям, просмотр записей с использованием фильтра. При просмотре данных некоторые столбцы можно скрыть. Для этого используются команды Скрыть/Отобразить контекстного меню имени столбца. Переключитесь из Режима таблицы в Режим Конструктора и просмотрите типы данных, описания полей и их свойства. Обратите внимание на типы полей Адрес, Примечание, Web – страница, Вложения.
На этом предварительное знакомство со средой Access заканчивается. БД «Борей» можно закрыть. В дальнейшем Вы можете возвращаться к этой БД для изучения примеров и использования их как прототипов при разработке приложений, например, при выполнении курсовой работы.
5. Создайте новую базу данных и её таблицы в соответствии с индивидуальным заданием. Название файла БД должно соответствовать заданию. Для создания таблиц используйте Режим конструктора. При создании таблиц выбирайте подходящие для полей названия, типы данных и их размеры (длину). Определите также для некоторых полей таблиц необходимые свойства, в частности, такие как формат, подпись, значение по умолчанию, условие на значение и др. Например, для поля с именем «Дата_заказа» естественно ввести подпись «Дата заказа», для этого же поля в качестве значения по умолчанию - использовать функцию «Date», для поля с номером телефона - маску ввода и т.п.
6. Для каждой таблицы в окне свойств режима Конструктора определите первичные ключи - с помощью кнопки Ключевое поле или команды контекстного меню. Поля первичных ключей для каждой таблицы указаны в задании с помощью подчеркивания. Первичный ключ является уникальным идентификатором записи. Он необходим для исключения дублирования записей в таблице, а также для установки связей с другими таблицами. В режиме Конструктора первичный ключ отмечается с помощью пиктограммы ключа. Если первичный ключ – составной, то все поля, входящие в его состав, должны быть отмечены пиктограммой ключа.
7. В режиме Конструктора в таблице «Заказы» для поля «Код заказчика» создайте с помощью Мастера подстановок подстановку в виде Название заказчика из таблицы «Заказчики». Мастер подстановок создаст в поле «Код заказчика» таблицы «Заказы» раскрывающийся список названий из значений поля «Название» таблицы «Заказчики» для упрощения ввода значений кода заказчика в таблицу «Заказы» по названию заказчика.
Для создания подстановки для поля «Код заказчика» выберите тип данных «Мастер подстановок», в окне «Создание подстановки» выберите опцию «Объект «столбец подстановки» будет иметь значение из таблицы или запроса», затем выберите таблицу «Заказчики», доступные поля «Код заказчика» и «Название», поле «Название» для сортировки списка данных и необходимую для отображения ширину столбцов для полей.
Аналогичным образом создайте подстановку для поля «Тип» таблицы «Заказано» из значений поля «Тип» таблицы «Микросхемы» для создаваемой базы данных.
8. Определите связи между таблицами БД. Связи устанавливаются по значениям общих полей связываемых таблиц. Если общее поле является первичным ключом в главной таблице, то Access позволяет поддерживать целостность данных (referential integrity), обеспечивающую возможности введения в подчиненной (дочерней) таблице только таких значений внешних ключей, которые имеются в главной таблице.
Для задания связей между соответствующими таблицами следует их закрыть и с помощью команды Работа с базами данных >> Схема данных открыть окно Схема данных, щелкнуть общее поле в главной таблице и перетащить его к месту расположения общего поля в подчиненной таблице. Схема данных отображает логическую структуру БД: таблицы и связи между ними. В диалоговом окне Изменение связей можно настроить параметры связи и установить желательный тип обеспечения целостности связей при добавлении и удалении записей и изменении ключей таблиц (см. статью «Руководство по межтабличным связям» справочной системы). Параметр Каскадное обновление связанных полей позволяет автоматически обновлять все значения связанного внешнего ключа при изменении значения первичного ключа. Параметр Каскадное удаление связанных полей автоматически удаляет все записи связанного внешнего ключа при удалении записи первичного ключа, поэтому эту опцию следует использовать только по необходимости.
Для изменения связей между таблицами следует закрыть все окна рассматриваемых таблиц, перейти в режим схемы данных и выполнить необходимые изменения.
9. В соответствии с заданием установите необходимые индексы для таблиц. Наличие индекса ускоряет выполнение запросов по соответствующим полям, а также операции сортировки и группировки данных. Для определения индекса откройте таблицу в режиме конструктора и установите необходимое поле как индексированное. В индексированных (ключевых) полях в зависимости от характера значений индекса (ключа) выберите опцию Допускаются совпадения | Совпадения не допускаются. Составные индексы, в отличие от простых, состоят из нескольких полей, порядок расположения которых в индексе определяет порядок сортировки данных по значениям индекса. Для установки составного индекса кнопкой Индексы откройте одноименное окно, введите имя для составного индекса, выберите входящие в него в необходимом порядке поля и характер сортировки.
10. Введите в каждую из созданных таблиц в Режиме таблицы минимум 5..10 записей. Новая запись добавляется в строку, отмеченную звездочкой (*). Для автоматического сохранения добавленных данных достаточно перейти на любую другую строку. При заполнении и изменении данных в таблицах БД учитываются все ограничения, обусловленные установленными связями таблиц. Поэтому изначально следует вводить данные в родительские таблицы, а затем – в дочерние. Данные по микросхемам приведены в файле «Интегральные микросхемы - справочник» или могут быть получены из специальной литературы и ресурсов Internet. Данные в остальные таблицы вводятся исходя из личного опыта студента.
Увеличение числа записей в БД способствует содержательности операций с данными. В дальнейшем количество записей в таблицах должно быть увеличено, по крайней мере, до 15 -20.
Обратите внимание, что при наличии связей с другой таблицей Access отображает подчиненные таблицы (подтаблицы), отмечаемые символом плюс (+), как элемент связей таблиц.
В процессе ввода данных изучите операции редактирования, копирования и вставки текста и записей с помощью буфера обмена, поиска и замены данных (команда Найти вкладки Главная), а также удаления записей таблице (команда Удалить панели Работа с таблицами). 11. Access, как и другие СУБД, создает системные таблицы, определяющие структуру и связи объектов БД. Для просмотра созданных ею системных таблиц следует использовать команду Параметры переходов контекстного меню (правая кнопка мыши) в верхней части Окна переходов Access. Затем в открывшемся диалоговом окне «Параметры переходов » в группе «Параметры отображения » выбирается параметр (опция) «Показывать системные объекты », позволяющая просматривать системные таблицы.
12. Рассмотрите возможности сортировки данных в таблицах. Для сортировки записей таблицы по возрастанию или убыванию значений определенного столбца необходимо выделить нужный столбец и воспользоваться кнопками Сортировка по возрастанию/убыванию на панели Сортировка и Фильтр вкладки Главная.
13. Ознакомьтесь с возможностями фильтрации данных с помощью команды Фильтр. Фильтрация применяется, когда нужно отобразить только те записи, которые удовлетворяют условиям фильтра. Фильтр можно вызвать с помощью команды Главные и перехода к панели Сортировка и фильтр, а также контекстного меню поля таблицы. Допускается последовательно накладывать несколько фильтров.
В Access имеются следующие варианты фильтров: Выделение, Дополнительно и Расширенный фильтр. Особое внимание уделите последнему варианту, как наиболее гибкому, позволяющему определить сложные выражения фильтрации с помощью диалогового окна Построитель выражений, которое открывается командой Построить контекстного меню области Условие отбора. С помощью Расширенного фильтра для таблицы «Заказы» выведите сведения о заказах после определенной даты, выполняемых определенным сотрудником.
14. Изучите программу- функцию VBA, представленную в листинге 2 приложения 3.
Предварительно ознакомьтесь с исходными сведениями по языку VBA, изложенными в Приложении 1. Более подробные сведения по программированию на языке VBA изложены в специальной литературе, например, [2,3].
Обратите внимание, что знак амперсанда & в VBA используется для сложения («склеивания») строк программы, а символ подчеркивания (_) – для разбиения одной логической строки на несколько физических, т.е. указывает, что следующая строка является продолжением предыдущей части команды. Используя программный код в качестве прототипа, создайте процедуру для поиска микросхемы по выбранному Вами критерию поиска, соответствующему Вашему индивидуальному заданию.
Для ввода программного кода откройте окно редактора VBA (команда Создание >> Макрос>>Модуль), скопируйте и измените необходимым образом код программы. Исполните программу и пронаблюдайте результаты.
Для вывода результатов поиска откройте окно Immediate командой View >> Immediate Window в редакторе VBA.
15. Составьте и выполните процедуры VBA, позволяющие
а) создать индекс для таблицы «Заказчики» по полю «Название»,
б) произвести индексированный поиск в таблице с использованием индекса.
В качестве прототипов используйте код процедур, представленных в приложении 2 (листинг 1) и приложении 3 (листинг 3).
16. Факультативное задание. Дополнительно ознакомьтесь со средствами Access по обеспечению безопасности базы данных и ограничению доступа к ней. Защитить БД от несанкционированного доступа можно с помощью пароля. Для более подробных сведений см. статью «Защита базы данных Access 2007» справочной системы. Перед установкой пароля рекомендуется сделать резервную копию базы данных и сохранить её.
Откройте в монопольном режиме базу данных. В диалоговом окне Задание пароля базы данных, открываемом командой Работа с базами данных >> Зашифровать паролем, введите и подтвердите пароль. Надежные пароли представляют собой сочетание прописных и строчных букв, цифр и символов достаточной длины.
Предупреждение! Если пароль будет забыт, то базу данных открыть невозможно!
17. Окончательно осмыслите содержание работы, завершите выполнение и оформление всех элементов задания, как части проекта создания учебной базы данных, проверьте подготовку по контрольным вопросам и защитите работу.