5684
.pdf1
Министерство образования и науки Российской Федерации
Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования
«Хабаровская государственная академия экономики и права»
Кафедра информационных технологий
Работа с СУБД Microsoft Access 2007
Методические указания по выполнению лабораторных работ для бакалаврантов 1 – 3-го курсов очной формы обучения всех направлений
Хабаровск 2011
2
ББК У. в6 Х12
Работа с СУБД Microsoft Access 2007 : методические указания по выполнению лабораторных работ для бакалаврантов 1 – 3-го курсов очной формы обучения всех направлений / сост. Д. В. Тимошенко. – Хабаровск : РИЦ ХГАЭП, 2011. – 40 с.
Рецензент А.Н. Бердник, доцент кафедры ДВС ТОГУ, кандидат технических наук
Утверждено издательско-библиотечным советом в качестве методических указаний для студентов очной формы обучения
Денис Владимирович Тимошенко
Работа с СУБД Microsoft Access 2007
Методические указания по выполнению лабораторных работ для бакалаврантов 1 – 3-го курсов очной формы обучения всех направлений
Редактор Г.С. Одинцова
Подписано в печать _____________ Формат 60х84/16 Бумага писчая. Цифровая печать. Усл. п. л. 2,3. Уч.-изд. л. 1,7. Тираж 50 экз. Заказ № ____________
680042, г. Хабаровск, ул. Тихоокеанская, 134, ХГАЭП, РИЦ
© Хабаровская государственная академия экономики и права, 2011
3
Введение
В профессиональной или личной сфере очень часто приходится иметь дело с большими объёмами данных. Работа с данными может быть существенно облегчена, если последние будут организованы в виде так называемой базы данных.
Базой данных (БД) будем называть организованную на машинном носителе совокупность структурированных данных, относящихся к определённой предметной области. Типичные примеры: телефонный справочник, сведения о студентах вуза, записи о заказах товаров и т.д. Базы данных являются одним из основных компонентов современных информационных технологий.
Минимальный набор функций базы данных:
хранение больших массивов данных с обеспечением быстрого и удобного доступа к ним;
обеспечение минимально возможного дублирования данных; установление и поддержка связей между данными; предотвращение появления некорректных данных (обеспечение це-
лостности данных); обеспечение секретности и конфиденциальности;
возможность доступа к данным для нескольких пользователей. Сложная организация и большие объёмы хранимых данных, высокие
требования к скорости и эффективности их обработки приводят к необходимости использования специальных программных средств – систем управления базами данных (СУБД).
Одной из наиболее распространённых СУБД является Microsoft Access различных версий, входящая в состав известного пакета Microsoft Office.
1. Основные сведения о СУБД MS Access 2007. Принципы проектирования баз данных
СУБД MS Access использует реляционную модель данных, в которой все данные представлены в виде набора взаимосвязанных таблиц, называемых также отношениями.
Реляционная таблица состоит из полей (столбцов) и записей (строк). Порядок следования полей и записей произвольный. Запись содержит законченный объём данных о каком-либо объекте или событии. Записи являются уникальными. Поле отражает одно из интересующих свойств объекта (события), содержит однотипные данные и имеет уникальное имя (в пределах одной таблицы).
Таблицы реляционной БД должны отвечать требованиям нормализации отношений. Нормализация – это процедура разделения сложных данных на
4
несколько таблиц, что позволяет устранить дублирование данных, обеспечивает целостность, повышает скорость извлечения данных. Существует три нормальные формы отношений.
Для того чтобы однозначно отличить одну запись от другой, используется первичный ключ – одно или несколько полей, содержащих уникальные значения для каждой записи. Первичный ключ, состоящий одного поля, называется простым (первичное ключевое поле), из нескольких полей – составным. Ключевые поля используются также для установления связей между таблицами.
MS Access позволяет устанавливать следующие типы связей. «Один-к-одному» (1:1) – тип связи, при котором одной записи в первой
таблице соответствует только одна запись во второй. Связь осуществляется по первичным ключевым полям в обеих таблицах, таблицы являются равнозначными и могли бы быть объединены в одну.
«Один-ко-многим» (1:∞) – тип связи, при котором одной записи в первой таблице соответствует несколько записей во второй. Связь осуществляется по первичному ключевому полю в первой таблице и неключевому во второй (это поле во второй таблице будет называться «ключ связи»). Первая таблица считается главной, вторая – подчинённой.
«Многие-ко-многим» (∞:∞) – тип связи, при котором нескольким записям в первой таблице соответствует несколько записей во второй. В правильно спроектированной базе, обеспечивающей целостность данных, такая связь не может быть использована.
Кроме таблиц, объектами СУБД MS Access являются запросы, формы, отчёты, страницы доступа к данным, макросы и модули.
Таблицы используются для хранения данных в базе.
Запросы позволяют выбрать нужные данные из одной или нескольких таблиц. С помощью запросов можно модифицировать существующие данные (добавлять, изменять, удалять), а также создавать новые таблицы.
Формы предназначены для ввода, редактирования и просмотра данных из таблиц на экране в удобном для пользователя виде. С помощью элементов формы можно запустить на выполнение макрос или процедуру.
Отчёты позволяют создать документ для печати на основе данных из таблицы или запроса. Созданный документ также можно включить в другое приложение (например, MS Word или MS Excel).
Страницы доступа к данным – это специальные Web-страницы, выполняющие роль форм. Их можно открывать в браузере и использовать для ввода, просмотра и редактирования данных в базе.
Макросы представляют собой описание стандартных действий, которые нужно выполнить в ответ на определённое событие. Например, можно определить макрос, который в ответ на выбор некоторого элемента в одной форме открывает другую форму.
5
Модули содержат программы, написанные на языке Visual Basic for Applications (VBA). Использование модулей позволяет автоматизировать выполнение сложных действий, которые нельзя описать с помощью макросов.
Основным средством создания и редактирования таблиц, запросов, форм, отчётов, страниц доступа к данным и макросов являются конструкторы. Кроме этого, для создания основных объектов базы можно использовать специальные диалоговые средства – мастера.
Все объекты базы данных хранятся в одном общем файле. Этот файл в MS Access 2007 имеет расширение .accdb, в MS Access 2003 и более ранних версиях .mdb. Кроме этого, при работе с базой автоматически создаётся небольшой файл с расширением .laccdb (.ldb в MS Access 2003), который содержит информацию о блокировке, позволяющую предотвратить конфликты доступа и повреждения БД при работе в сети. Файлы .accde (.mde в MS Access 2003) – это файлы, которые работают в режиме «исполнения». В этих файлах удалён весь исходный код VBA, в результате пользователь файлов может только выполнять код VBA, но не может изменять его.
Любая СУБД полностью устраняет пользователей от проблем организации хранения данных на физическом уровне. Ввод, хранение, обработку и защиту данных обеспечивает основная часть СУБД, называемая ядром.
Проектирование баз данных. При проектировании баз данных последовательно рассматривают три уровня – информационно-логический (инфологический), даталогический (концептуальный) и физический, каждому из которых соответствует своя модель предметной области. Основная задача проектирования заключается в создании инфологической модели предметной области и концептуальной модели БД.
Инфологическая модель описывает содержание предметной области. При её разработке анализируется предметная область, требования пользователей, документы, отражающие события и процессы, протекающие в предметной области. В результате анализа появляются списки объектов предметной области и перечни их свойств, выявляются связи между объектами, с помощью диаграмм описывается структура предметной области. Данная модель не зависит от программных средств, в том числе и СУБД.
Концептуальная модель основывается на инфологической модели и представляет собой структуру базы данных, соответствующую выбранной СУБД. Выбор модели данных и выбор СУБД тесно взаимосвязаны.
Физическая модель определяет способ размещения данных непосредственно на машинном носителе. В современных прикладных программных средствах этот уровень организации обеспечивается автоматически.
Существуют специальные средства и технологии создания концептуальных моделей, например ER-модель (модель сущность-связь), модель семантических объектов. Для проектирования реляционных БД простой структуры можно воспользоваться упрощённым подходом, представленным в практическом задании.
6
Практическое задание 1. Проектирование базы данных. Знакомство с рабочей областью MS Access 2007
Создайте базу данных, содержащую следующую информацию (варианты заданий представлены в таблице 1).
|
Таблица 1 – Варианты заданий |
|
||
Вари- |
Назначение БД |
Информация, которая должна содержаться в БД |
||
ант |
||||
|
|
|
||
|
|
|
||
|
|
Номер маршрута; название маршрута; начальная |
||
|
|
остановка; конечная остановка; продолжительность |
||
|
|
маршрута; фамилия, имя, отчество водителя; адрес |
||
|
Учёт пассажир- |
водителя; контактный телефон |
водителя; государ- |
|
1 |
ственный номер автобуса; марка автобуса; пассажи- |
|||
ских перевозок |
||||
|
ровместимость; дата поездки; |
выполненный марш- |
||
|
|
|||
|
|
рут; время начала поездки; время окончания поездки; |
||
|
|
водитель, выполнивший маршрут; автобус, выпол- |
||
|
|
нивший маршрут |
|
|
|
|
|
||
|
|
Наименование товара поступившего на склад; коли- |
||
|
|
чество поступившего товара; дата поступления; номер |
||
|
Учёт товаров, по- |
склада; наименование поставщика товара; адрес по- |
||
2 |
ставщика; телефон поставщика; банковские реквизи- |
|||
ступающих на |
ты поставщика; наименование товара; артикул товара; |
|||
|
склады фирмы |
|||
|
вид товара; стоимость единицы товара; наименование |
|||
|
|
|||
|
|
производителя товара; адрес производителя; телефон |
||
|
|
производителя; банковские реквизиты производителя |
||
|
|
|
||
|
|
Фамилия, имя, отчество клиента; адрес клиента; кон- |
||
|
|
тактный телефон клиента; вид тура; страна и населен- |
||
|
Отслеживание |
ный пункт тура; характеристика тура; наименование |
||
|
туроператора; адрес туроператора; контактные теле- |
|||
3 |
деятельности ту- |
|||
фоны туроператора; реквизиты туроператора; дата за- |
||||
|
ристской фирмы |
|||
|
ключения договора; дата начала путешествия; дата |
|||
|
|
|||
|
|
окончания путешествия; клиент, заключивший дого- |
||
|
|
вор; приобретённый тур; туроператор, продавший тур |
||
|
|
|
||
|
|
Фамилия, имя, отчество слушателя; адрес слушателя; |
||
|
|
телефон слушателя; место работы слушателя; фамилия, |
||
|
Организация кур- |
имя, отчество преподавателя; адрес преподавателя; кон- |
||
4 |
тактный телефон преподавателя; место работы препо- |
|||
сов повышения |
давателя; название дисциплины; количество часов; дата |
|||
|
квалификации |
|||
|
занятия; прочитанная дисциплина; преподаватель, про- |
|||
|
|
|||
|
|
водивший занятие; присутствующие слушатели; время |
||
|
|
начала занятия; сколько часов проведено |
||
|
|
|
|
7
Окончание таблицы 1
|
|
Тип запчасти; название запчасти; автомобиль; основ- |
||||
|
|
ные характеристики запчасти; стоимость запчасти; |
||||
|
Отслеживание |
название |
поставщика |
запчасти; адрес |
поставщика; |
|
|
телефон |
поставщика; |
дата поставки; |
поставленные |
||
|
деятельности |
|||||
5 |
запчасти; поставщик, совершивший поставку; коли- |
|||||
фирмы по про- |
||||||
|
чество поставленных запчастей; склад, на который |
|||||
|
даже запчастей |
|||||
|
помещены запчасти; |
адрес склада; склад заполнен |
||||
|
|
|||||
|
|
(да/нет); фамилия, имя, отчество ответственного ли- |
||||
|
|
ца; телефон ответственного лица |
|
|||
|
|
Вид продукции; название продукта; его стоимость; |
||||
|
|
масса; название получателя продукции; адрес получа- |
||||
|
Отслеживание |
теля; контактный телефон получателя; дата поставки; |
||||
6 |
поставок про- |
количество поставленного продукта; получатель про- |
||||
дукции неболь- |
дукции; способ доставки продукции; название способа |
|||||
|
||||||
|
шой фирмы |
доставки продукции; фамилия, имя, отчество лица от- |
||||
|
|
ветственного за доставку; контактная информация; |
||||
|
|
стоимость доставки; предоплата доставки (да/нет) |
||||
|
|
|
|
|
|
1.Определите круг задач, решаемых с помощью БД. Определите, какие данные необходимо для этого использовать.
2.Определите количество и структуру таблиц БД:
выделите в исходной информации данные, относящиеся к отдельным информационным объектам (например, информацию об успеваемости студентов можно разделить на данные о студентах и на данные о полученных ими оценках);
каждому из полученных наборов данных поставьте в соответствие отдельную таблицу (например, данные о студентах и о полученных ими оценках будут находиться в разных таблицах). В результате, в первом приближении, будет определён набор полей для каждой из таблиц;
проверьте полученные таблицы. Таблица должна содержать данные об однотипных объектах, информация в одной таблице не должна дублироваться, не должно быть повторений между таблицами;
при необходимости переносите поля из одних таблиц в другие.
3.Выполните проверку полей в каждой полученной таблице. Дайте корректные имена полям таблиц. Определите тип данных в каждом поле таблиц:
данные следует разбивать на наименьшие логические единицы (например, в таблицу следует включить три поля – Фамилия, Имя и Отчество, а не одно общее поле ФИО);
имена полей, по возможности, должны быть краткими. Использовать пробелы в именах полей не рекомендуется.
8
4. Определите первичные ключевые поля в каждой таблице:
в качестве первичного ключевого поля следует использовать поле, содержащее уникальные значения (например поле с номером зачётной книжки, в таблице, содержащей данные о студентах);
при необходимости в таблицу можно добавить новое поле с уникальными значениями (код, номер события, индивидуальный номер и т.д.);
в ряде случаев можно использовать составной ключ, содержащий значения двух и более полей, при этом сочетание значений должно быть уникальным.
5.Установите связи между таблицами. Определите типы связей:
таблицы БД связываются по полям, содержащим одинаковые значения. Правильно установленные связи соответствуют реальным связи между информационными объектами;
связь должна обеспечивать целостность данных (например, связывать таблицу с данными о студентах с таблицей с данными об оценках необходимо по полю с номером зачетной книжки, а не по полю содержащему имя студента);
при необходимости в таблицу можно добавить новое поле или заменить существующее, если это требуется для установления связи (например, в таблицу с данными об оценках может быть добавлено поле с номером зачётной книжки, или данным полем заменено поле, содержащее имя студента).
6. Запустите программу (Пуск → Программы → Microsoft Office → Microsoft Office Access 2007). Выберите Новая база данных. Выберите ме-
сто размещения базы данных, введите имя файла. Нажмите кнопку Создать (рисунок 1). После создания «пустой» базы данных MS Access открыл в режиме непосредственного ввода данных (режим таблицы) Таблицу 1, созданную по умолчанию (рисунок 2).
Рисунок 1 – Создание файла базы данных
9
Рисунок 2 – Режим таблицы
7. Ознакомьтесь с рабочей областью MS Access 2007. Основные команды находятся в так называемой ленте Office Fluent, расположенной в верхней части главного окна (рисунок 3). Лента заменила собой меню и панели инструментов, она служит основным командным интерфейсом программы. Лента содержит ряд вкладок с командами. Основные вкладки – Главная,
Создание, Внешние данные и Работа с базами данных.
Рисунок 3 – Лента MS Access 2007. Команды вкладки Создание
Команды вкладки Главная позволяют:
–изменить представление,
–работать с буфером обмена,
–работать с записями (создавать, сохранять, удалять и т.д.),
–выполнять сортировку, отбор и поиск записей.
Команды вкладки Создание позволяют создавать и редактировать объекты базы данных.
Команды вкладки Внешние данные позволяют выполнить:
–импорт или связывание внешних данных,
–экспорт данных,
–сбор и обновление данных по электронной почте и т.д.
Команды вкладки Работа с базами данных позволяют:
–создавать и просматривать отношения между таблицами,
–запустить редактор Visual Basic или выполнение макроса,
–выполнить анализ быстродействия,
–выполнить шифрование паролем и т.д.
10
8. Перейдите в режим конструктора таблиц (вкладка Главная → Режим → Конструктор или кнопки в правом нижнем углу окна (рисунок 2)). MS Access потребует сохранить Таблицу 1, сохраните ее как первую таблицу Вашей базы данных (под соответствующим именем).
9. В столбце конструктора таблиц Имя поля введите имена полей первой таблицы базы данных. В столбце конструктора Тип данных для каждого введенного поля укажите тип данных, выбрав его из раскрывающегося списка. Если считаете необходимым, задайте описание для полей (рисунок 4).
10. Задайте ключевое поле в первой таблице. Для этого переместите курсор в строку, содержащую нужное поле, и нажмите значок Ключевое поле. Справа от имени поля появится соответствующий значок (рисунок 4). Сохраните таблицу и закройте конструктор.
Рисунок 4 – Указание типа данных и задание ключевого поля
11. Создайте вторую таблицу базы. Выполните команду вкладка Создание → Конструктор таблиц. Повторите пункты 9 и 10 для данной таблицы. Сохраните таблицу под соответствующим именем и закройте конструктор.
12. Аналогичным образом создайте следующие таблицы базы данных.
13. Создайте межтабличные связи (вкладка Работа с базами данных → Схема данных), предварительно необходимо закрыть все открытые объекты базы данных. В появившемся окне Добавление таблицы добавьте все таблицы базы к схеме данных. Если данное окно не появилось автоматически, нажмите на значок Отобразить таблицу (рисунок 5). Свяжите таблицы по требуемым полям. Для этого выделите левой кнопкой мыши требуемое поле в главной таблице, удерживая кнопку мыши, перенесите его поверх связываемого поля в подчинённую таблицу. В появившемся окне Изменение связей проверьте, по каким полям устанавливается связь, и устано-