Access

Лабораторная работа

«Введение в СУБД MS Access».

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

Обратите внимание: в настоящей лабораторной работе описывается MS Access 2000. Если Вы работаете с другой версией (MS Access 97, например), то некоторые (несущественные) детали будут отличаться.

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

0. Запуск приложения Access.

Чтобы запустить Access, воспользуйтесь кнопкой «Пуск» («Start»), выберите в появившемся меню пункт «Программы» («Program») и подпункт «MS Office» («Office»). В открывшемся списке найдите пункт «Microsoft Access» и щелкните по этому элементу списка мышкой. Вы увидите окно приложения с главным меню и панелью инструментов, которые похожи на уже изученные Вами продукты MS Word и MS Excel. Отличие от этих приложений состоит в том, что открывается только окно приложения. Текстовый редактор и табличный процессор после запуска содержат (по умолчанию) также окно нового документа. Когда Access загрузится, можно либо создать новую базу данных, либо открыть уже существующую. Вместе с Access поставляется учебная база данных «Борей», которая демонстрирует все основные возможности продукта. Она достаточно сложна по своему внутреннему устройству, однако изучение ее часто может помочь пользователю решить свои проблемы. Кроме того, Access снабжен развитой справочной системой.

1. Создание базы данных.

Чтобы создать базу данных, нужно выбрать в меню «Файл» пункт «Создать» или нажать соответствующую кнопку на панели инструментов. Access может автоматизировать процесс создания базы данных, использовать готовые шаблоны. Однако первое время, пока Вы будете учиться работать с системой, лучше большую часть работы выполнять «вручную». Поэтому из всех вариантов, предоставленных Access, выберите пункт «База данных» и нажмите кнопку «ОК». В результате будет открыто диалоговое окно «Файл новой базы данных». Это диалоговое окно должно напомнить хорошо известное Вам диалоговое окно «Сохранить файл как…». Выберите в выпадающем списке Ваш каталог и измените данное по умолчанию имя db1.mdb на более содержательное, сохранив при этом расширение файла. Назовите его, например, firma.mdb (в качестве первого упражнения Вам предстоит разработать базу данных, описывающую структуру фирмы). Нажмите на кнопку «Создать». В результате в окне приложения появится документ с заголовком «firma: база данных». В левой части этого

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

—создание таблицы в режиме конструктора

—создание таблицы с помощью мастера

—создание таблицы путем ввода данных

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

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

2. Создание таблиц.

В открывшемся окне Вы увидите таблицу из трех столбцов: «Имя поля», «Тип данных» и «Описание». Первые два столбца являются обязательными для заполнения. Столбец «Описание» предназначен для «внутренних» нужд пользователя: в нем Вы можете поместить комментарий, описание поля, объяснение, почему оно нужно и т.п. Теперь можно заполнять строки, чтобы получить макет таблицы.

Фирма. Какие таблицы потребуются для описания фирмы? Во-первых, это таблица «Сотрудники». В ней будут содержаться данные о сотрудниках: табельный номер, личные данные (фамилия, имя, отчество; почтовый индекс, адрес, домашний телефон; мы ограничимся только ФИО), в каком отделе работает, является ли начальником. Во-вторых, это таблица «Отделы». В этой таблице можно сохранять данные об отделах: их названиях, их начальниках. В-третьих, в таблице «Должности» будут храниться сведения о должностных окладах. Наконец, должна быть таблица «Телефоны», в которой указаны данные о номерах телефонах и о том, к какому отделу они относятся.

Замечание 1. О нормализации таблиц говорилось в лекциях.

Создайте макет таблицы «Телефоны», имеющий следующие поля: «номер телефона», тип – текстовый (допустим и числовой; см. Замечание 1) и «номер отдела», тип – числовой. Название поля набирается в столбце «Имя поля», а тип выбирается из выпадающего списка в столбце «Тип данных». Если хотите, добавьте описания. Сделайте поле «номер телефона» ключевым. Вы можете, выделив это поле, нажать на панели инструментов кнопку с изображением ключа или выбрать соответствующий пункт меню «Правка». Можно также воспользоваться контекстным меню, которое вызывается нажатием правой клавиши мыши. Проще всего сохранить сделанную работу просто закрывая окно с таблицей. Нажмите на крестик в правом верхнем углу окна с таблицей, и Access поинтересуется, нужно ли сохранять сделанные изменения. Следует ответить положительно, и тогда появится диалоговое окно «Сохранение». Это окно позволяет изменить имя таблицы «Таблица 1» на более содержательное. Назовите эту таблицу «Телефоны» и нажмите «ОК». Следующая таблица, которая будет разработана – таблица «Должности». Эта таблица будет играть роль справочника. Ради экономии места в других таблицах лучше использовать номера должностей – разрядов, а не их текстовые названия (к тому же это ускоряет ввод и снижает вероятность

ошибок). Разряд и будет ключевым полем в этой таблице. Кроме этого, нужны поля «наименование должности» и «должностной оклад». Поле «разряд» будет иметь числовой тип, поле «должность» – текстовый, поле «должностной оклад» – денежный.

Теперь таким же образом создайте таблицы «Отделы» и «Сотрудники».

Замечание 2. Тип поля «номер телефона» выбран текстовым, чтобы можно было использовать разделители (черточки) при записи номеров. Если просто выбрать числовой тип, придется записывать телефоны в таком виде: 332211. Есть и другая возможность – использовать числовой тип и маску ввода. В нижней части окна макета таблицы можно определить свойства поля (активного на данный момент). На странице «Общие» есть пункт «маска ввода». Активизация этого пункта приведет к появлению в правой (пустой) части списка свойств полей (напротив выделенного пункта) кнопки с многоточием. Нажатие на эту кнопку приводит к вызову Мастера масок ввода, однако для полей числового и денежного типа требуется задавать маску ввода вручную. Справочная система MS Access предоставляет подробное описание масок ввода.

Замечание 3. Если при создании макета таблицы у Вас возникла потребность что-то изменить, исправить, дополнить, это легко сделать. Режим разработки макета таблицы называется режимом конструктора. Двойной щелчок по уже созданной таблице откроет ее для заполнения, а щелчок по кнопке «конструктор» в окне документа базы данных приведет к открытию именно макета таблицы.

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

Замечание 5. Конечно, Вы можете воспользоваться меню для сохранения таблиц, но на первых порах «избыточная» информация может Вас запутать. Поэтому (пока Вы начинающий пользователь) следует больше доверять Access.

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

Когда Вы закончите работу, у Вас должны получиться (примерно): следующие макеты:

Вопросы:

1.В чем отличие между таблицей в режиме конструктора и таблицей в режиме заполнения?

2.Как сделать поле таблицы ключевым?

3.Что произойдет, если ни одно поле таблицы пользователь не сделал ключевым?

4.Обязательно ли добавлять описания в макет таблицы?

3. Заполнение таблиц.

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

Фирма. Итак, давайте пока ограничимся тремя описанными выше отделами: бухгалтерией, производственным отделом и рекламным отделом. Присвоим им номера: 01, 02 и 03 соответственно. Предположим, что в бухгалтерии работает три сотрудника, в рекламном отделе – четверо, а в производственном – пятеро (итого – двенадцать человек), включая начальников этих отделов. Придумайте сами фамилии, имена и отчества сотрудников фирмы, а также их табельные номера и должности (например, начальник отдела, заместитель начальника отдела, бухгалтер, инженер, менеджер по рекламе, стажер). Теперь осталось разобраться с телефонами. Допустим, что в бухгалтерии и производственном отделе установлено по два телефона, а в рекламном отделе – четыре. Таким образом, нужно придумать восемь номеров телефонов.

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

Пример заполненных таблиц:

Вопросы:

1.Какие поля в приведенных примерах таблиц можно изменить, не опасаясь за согласованность данных?

2.По какому принципу сформирован табельный номер сотрудника?

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

Следующим шагом будет установка связей между таблицами. Нажмите кнопку «Схема данных» на панели инструментов главного окна MS Access. В результате в главном окне появится еще одно окно – документа схемы данных, а в меню главного окна – новый пункт «Связи». Либо с помощью открывшегося вместе со схемой данных диалогового окна, либо с помощью пункта меню «Связи» | «Добавить таблицу», либо с помощью соответствующего пункта контекстного меню добавьте по очереди все созданные Вами таблицы в окно схемы данных.

Замечание 1. Обратите внимание: добавленная в схему данных таблица не исчезает из диалогового окна «Добавить»! Если Вы поместили несколько экземпляров одной и той же таблицы, удалите лишние, чтобы не загромождать схему данных.

Установить связи между таблицами очень легко. Например, чтобы связать таблицы «Отделы» и «Телефоны», достаточно просто перетащить мышкой поле «Отделы: номер отдела» на поле «Телефоны: номер отдела». Когда Вы отпустите клавишу мыши, появится диалоговое окно «Изменение связей», в котором указаны таблицы и поля, между которыми устанавливается связь, а также тип этой связи (один – ко – многим в нашем случае). Можно сразу нажать кнопку «Создать», однако лучшим подходом будет использовать возможности по обеспечению целостности данных. Отметьте (поставьте галочку) опцию «Обеспечение целостности данных», и две других опции – «Каскадное обновление связанных полей» и «Каскадное удаление связанных записей» также станут доступными. Отметьте первую из этих опций. Вторая опция – каскадное удаление связанных записей – не менее полезна, чем первая, однако использование ее может привести к потере данных, и мы пока не будем ею пользоваться.

После того, как Вы нажмете кнопку «Создать», поля таблиц соединит черная линия, на одном конце которой (касающемся поля «Отделы: номер отдела») будет стоять «1», а

на другом – знак бесконечности «∞». Так обозначается связь «один – ко – многим».

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

Установите связи между всеми таблицами. У Вас должна получиться (примерно) следующая картина:

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

Вопросы:

1.Как добавить таблицу в схему данных?

2.Опишите, как установить связи между таблицами и как изменить их.

5. Создание форм (часть 1).

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

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

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

Какие поля нужно отображать? Очевидно, что отобразить придется поля «табельный номер» и «ФИО»: эти поля присутствуют только в таблице «Сотрудники». Поля «разряд» и «номер отдела» используются для связи с таблицами «Должности» и «Отделы» соответственно. Поэтому лучше использовать данные, взятые непосредственно из этих таблиц. Мастер форм прекрасно справляется с такой работой, и единственная проблема состоит в том, что созданная им форма не слишком удобна для ввода данных. Вот почему чуть позже нам придется призвать на помощь другого Мастера. А пока переместите поля «табельный номер» и «ФИО» в список выбранных полей и нажмите кнопку «Далее». В следующем диалоговом окне предлагается выбрать внешний вид формы. Справа Вы можете видеть список вариантов, а слева – образцы форм (Access 2000, в частности, предлагает следующие виды форм: в один столбец, ленточный, табличный и выровненный). Для наших целей вполне подходит предлагаемый по умолчанию вид «в один столбец», однако (если есть желание) можно использовать любой другой вариант. Выбрав вариант, снова нажмите кнопку «Далее». Новое диалоговое окно позволяет выбрать стиль. Наиболее простое оформление

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

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

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

Вопросы:

1.Поясните, какие поля и почему были выбраны для отображения в форме,

2.Как создать поле со списком и для чего оно нужно?

6. Создание форм (часть 2).

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

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

Оставим на время форму «Сотрудники» (если хотите, можете пока закрыть ее) и создадим форму, выводящую данные об отделах и телефонах, установленных в них.

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

—Отделы

—Телефоны,

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

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

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

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

Следующий шаг состоит в присоединении формы «Отделы» в качестве подчиненной к форме «Сотрудники».

Замечание. Отметим, что Access допускает создание подчиненных форм до 7 уровней вложенности.

Чтобы это сделать, закройте форму «Отделы» и откройте в режиме конструктора форму «Сотрудники». Если необходимо, увеличьте (просто перемещая мышкой границу) размер области данных. После этого просто перетащите мышкой значок формы «Отделы» со страницы «Формы» окна базы данных на форму «Сотрудники». Теперь форма «Отделы» является подчиненной для формы «Сотрудники». Сохраните свою работу, закрыв форму «Сотрудники» (при этом будет выдан запрос на сохранение внесенных изменений, на который следует дать положительный ответ) и откройте ее вновь, уже обычным образом (дважды щелкнув на ее значке на странице «Формы» окна базы данных или выделив ее и нажав кнопку «Открыть»). Обратите внимание: при навигации по записям о сотрудниках подчиненная форма «Отделы» всегда отображает в поле номера отдела число «1», и, таким образом, предлагает телефоны бухгалтерии