      Федеральное государственное бюджетное образовательное учреждение
высшего профессионального образования
«КАЛИНИНГРАДСКИЙ ГОСУДАРСТВЕННЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ»
Кафедра систем управления и вычислительной техники
МЕТОДИЧЕСКИЕ УКАЗАНИЯ К ЛАБОРАТОРНЫМ РАБОТАМ
ПО ИЗУЧЕНИЮ СУБД ACCESS’2007
(разработаны доц. каф. СУиВТ Топорковой О.М. по материалам:
«Информационные технологии (ИТ): Методические указания к лабораторным работам по курсу ИТ для направления 552800 Информатика и вычислительная техника», автор доц. каф. СУиВТ Высоцкий Л.Г.;
«Электронный практикум по СУБД Access», автор доц. каф. СУиВТ Ломакина Г.В.)
Калининград
2014
Оглавление
Оглавление 2
Лабораторная работа № 1. Создание базы данных 3
Выполнение лабораторной работы 1 10
Лабораторная работа № 2. Сортировка и фильтрация данных. Запросы 11
Выполнение лабораторной работы 2 16
Лабораторная работа № 3. Формуляры 17
Выполнение лабораторной работы 3 25
Лабораторная работа № 4. Отчеты 26
Выполнение лабораторной работы 4 28
Лабораторная работа № 5. Кнопочные формы 29
Выполнение лабораторной работы 5 31
Лабораторная работа № 1. Создание базы данных
Цель работы: изучение и закрепление на практике методов создания и заполнения одиночных таблиц базы данных (БД) с включением функции контроля достоверности вводимых данных.
Методические указания
Создание таблицы
Начальный запуск системы управления базами данных (СУБД) Access производится одним из способов:
- последовательностью выбора команд Пуск/Программы/Microsoft Office/Microsoft Office Access;
- щелчком левой клавиши мыши (ЛКМ) по ярлыку  СУБД Access на рабочем столе.
Основным средством хранения информации в СУБД Access являются плоские таблицы, состоящих из строк (записей) и именованных столбцов (полей):
Номер заказа
|
Код покупателя
|
Код товара
|
Дата заказа
|
Заказано
|
Имена полей
|
|
|
|
|
|
|
|
|
|
|
|
Запись
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Поле
|
|
|
|
|
Каждая таблица описывает некоторый класс объектов выбранной предметной области, например, студентов вуза или преподавателей, а каждая строка-запись содержит информацию о конкретном объекте (студенте или преподавателе). Каждый столбец-поле описывает один из атрибутов некоторого объекта, например, должность или дату рождения. Поэтому все данные одного столбца характеризуются одинаковым типом - множеством допустимых значений и операций над ними. В СУБД Access определены следующие типы данных:
текстовый, длиной от 1 до 255 символов. Количество символов определяется пользователем в процессе создания таблицы исходя из семантики атрибута. Так, например, для хранения фамилий целесообразно отвести не менее 15 позиций. По умолчанию СУБД устанавливает длину текстового поля равной 50;
числовой, для которого существуют следующие форматы:
байт (целые значения в диапазоне 0..255);
целое (целые значения в диапазоне -32768..+32767);
длинное целое (целые значения в диапазоне -2147483648 .. +2147483647);
с плавающей точкой (4 байта). Вещественные числа в диапазоне -3.402823*1038 .. +3.402823*1038;
с плавающей точкой (8 байт). Вещественные числа в диапазоне -1.79769313486232*10308 .. +1.79769313486232*10308.
По умолчанию СУБД устанавливает формат числового поля с плавающей точкой (8 байт);
дата/время. Возможны следующие форматы дат: полный; длинный; средний; краткий. Аналогично, для времени также существуют три последних формата.
денежный. Аналогичен числовому типу с плавающей точкой. Позволяет вводить числа длиной до 15 знаков в целой части и 4 - в дробной;
счетчик. Значения данного типа автоматически увеличиваются на 1 для каждой новой записи. Пользователь не может сам модифицировать значения поля данного типа;
логический. Допускаются только два значения для полей данного типа: Да/Нет (True/False);
МЕМО (комментарий). Текстовое поле произвольной длины. Может хранить текст длиной до 64 000 байт.
Остальные типы данных, используемые в СУБД Access, будут рассмотрены позже.
При выборе формата типа необходимо стремиться к минимуму используемой памяти, т.е. не имеет смысла выбирать для хранения экзаменационных оценок форматы целое или длинное целое, а также с плавающей точкой, которые занимают в памяти соответственно 2, 4 или 8 байтов. В этом случае достаточно выбрать целый тип с форматом байт, который требует для своего хранения именно 1 байт памяти.
Каждое поле должно иметь имя, длина которого не может превышать 64 символа. Целесообразно имена выбирать не длиннее хранимых данных (что максимизирует при просмотре число отображаемых на экране столбцов таблицы), но такие, которые сохраняют семантику данных. В именах полей можно использовать любые комбинации букв, цифр, пробелов и других символов, за исключением ., !, ', [ и ].
Для каждой реляционной таблицы существует понятие ключа - набора полей, которые однозначно идентифицируют каждую запись таблицы. В такой таблице не допускаются две или более записи с одинаковыми значениями ключевых полей. В общем случае ключ может состоять только из одного поля (простой ключ). Составной ключ предполагает идентификацию записи на основе комбинации значений нескольких полей. В этом случае все поля такого ключа должны располагаться последовательно друг за другом. Ключевые поля целесообразно располагать в начале таблицы, поскольку СУБД автоматически производит сортировку записей по ключу.
Для создания новой таблицы необходимо в открывшемся после запуска окне выбрать опцию Новая база данных, в поле Имя файла задать произвольное имя и нажать кнопку Создать.
Активизируется вкладка Работа с таблицами и панель Режим таблицы. Существует несколько вариантов создания таблицы, но наиболее универсальным является подход на основе Конструктора.
Его запуск выполняется через опцию Режим панели Режим таблицы. В появившемся диалоговом окне вводят имя таблицы и нажимают ОК.
Эти действия приводят к открытию бланка описания структуры таблицы, состоящего из четырех столбцов:
|
Имя поля
|
Тип данных
|
Описание
|
|
|
|
|
|
|
|
|
|
|
|
|
Свойства поля
|
Размер поля
|
|
|
|
|
|
В столбце Имя поля указывается произвольное имя поля. Переход в столбец Тип данных приводит к появлению кнопки раскрытия, щелчок по которой ЛКМ отображает список допустимых типов полей. Выбор типа также может производиться ЛКМ, а затем в строке Размер поля производится его конкретизация. Столбец Описание является вспомогательным, в нем хранятся комментарии к создаваемым полям.
Треугольный индикатор в первом столбце бланка описания структуры таблицы показывает текущее поле обработки. При задании ключа необходимо его установить в требуемое поле и щелкнуть ЛКМ по пиктограмме Ключевое поле  в панели инструментов. Если в качестве ключа определяются несколько полей, то они выделяются путем протягивания мыши с нажатой левой клавишей по ячейкам первого столбца у требуемых полей, а затем щелкается пиктограмма Ключевое поле.
После описания структуры таблицы переходят к ее заполнению. Для этого в опции Режимы выбирается значение Режим таблицы  . При этом СУБД попросит указать имя создаваемой таблицы и отобразит пустой бланк созданной таблицы, готовый к заполнению.
Навигация по полям таблицы осуществляется клавишами , , , , Tab, Shift/Tab, мышью. Копирование значения поля из предыдущей строки - Ctrl/ '. Переход к заполнению следующей записи автоматически сохраняет в памяти компьютера предыдущую запись.
В процессе работы с таблицей можно в любой момент вернуться в режим конструктора для изменения её структуры путем нажатия мышью пиктограммы  , которая отображается в панели инструментов при переходе в табличный режим.
Изменению могут быть подвергнуты названия полей, их тип и последовательность. Для удаления некоторого поля таблицы необходимо его выделить щелчком правой клавиши мыши в первом столбе и нажать мышью пиктограмму  (Удалить строки) в панели инструментов. Пустая строка в описании структуры создается следующим образом:
курсор устанавливается на строку, перед которой необходимо вставить пустую;
на панели инструментов нажимается кнопка  (Вставить строки).
Перенос строки описания структуры реализуется следующим образом:
выделяется мышью в первом столбце переносимая строка (строки);
выделенная строка в первом столбце захватывается мышью и перетаскивается на новую позицию.
Контроль достоверности вводимых данных заданием условий
СУБД автоматически осуществляет проверку достоверности данных, вводимых в поля определенного типа (например, нельзя ввести букву в числовое поле). Уровень контроля может быть повышен пользователем путем задания дополнительных ограничений на значения полей в области конструктора Свойства поля из бланка описания структуры таблицы.
Для этого можно использовать следующие свойства:
Значения по умолчанию. Если в каком-либо поле формируемой таблицы будет преобладать одно значение, то целесообразно установить его в качестве значения по умолчанию, и оно будет появляться в данном поле автоматически при каждом создании новой записи. Например, если в таблицу заносится город проживания студента и преобладает Калининград, то целесообразно именно его установить в качестве значения по умолчанию. При занесении другого города данное значение необходимо стереть, например, сочетанием клавиш Ctrl/Backspace.
Обязательное поле. Установка данного свойства в значение Да приводит к тому, что переход к новой записи не будет разрешен, пока пользователь не заполнит обязательное поле.
Условие на значение. После перехода к данному свойству необходимо раскрыть путем нажатия на кнопку  окошко, в котором формируется само условие с помощью указанных в нём операций. Например, можно установить условие на экзаменационные оценки в виде ограничения >1 And <6, что позволит вводить только цифры 2, 3, 4 и 5.
Маска ввода. Это свойство используется для установки ввода данных по определенному шаблону. Шаблон состоит из трех частей, разделенных точками с запятой. Первая часть является маской, содержащей служебные символы и символы-заполнители данных, которые нужно ввести в поле. Служебными символами являются:
0 - знакоместо для цифры (от 0 до 9), ввод ее обязателен;
9 - знакоместо для цифры или пробела, ввод не обязателен;
# - знакоместо для цифры или пробела, ввод не обязателен, пустые символы преобразуются в пробелы. Разрешены символы + и -;
L - буква, ввод обязателен;
? - буква, ввод не обязателен;
А - буква или цифра, ввод обязателен;
а - буква или цифра, ввод не обязателен;
& - любой символ или пробел, ввод обязателен;
с - любой символ или пробел, ввод не обязателен;
< - принудительный перевод всех последующих символов в нижний регистр;
> - принудительный перевод всех последующих символов в верхний регистр;
! - заполнение маски справа налево (символ ! можно ставить в любую позицию маски);
\ - следующий символ является текстовой константой (отображается как литерал).
Любые символы, не включенные в этот список, отображаются как литералы.
Вторая и третья части шаблона не являются обязательными. Если во второй части указана 1 или ничего не указано, то при заполнении поля сохраняются только введенные символы, тогда как 0 означает, что нужно сохранять как введенные символы, так и символы маски. В третьей части маски указывается символ, который отображается как заполнитель исходно пустых позиций при вводе нового значения. Если этот символ не используется, то по умолчанию используется подчеркивание.
Примеры масок: Результат ввода:
00-00-00 27-34-56
17-78-32
>L
Радиотехника
Достаточно сложно реализовать шаблон для ввода ФИО в виде Фамилия И.О. Если предположить, что сама фамилия содержит не более 15 символов и не короче 2 символов, то маска имеет вид >LL.>L.;0;"_" и при переходе в данное поле изображается следующей структурой: _______________ _._.. При ее заполнении необходимо установить курсор в левую позицию, набрать фамилию в любом регистре (первый символ автоматически станет заглавной буквой, остальные отображаются в прописном варианте), а затем мышью или клавишей перенести курсор в место ввода инициалов:
Романов________ _._.
При переходе в следующее поле введенное значение преобразуется к нормальному виду, т.е. без пропусков, например, Романов П.Р.
Если в шаблоне будет отсутствовать вторая компонента, то точки после инициалов придется вводить самому пользователю, в противном случае ФИО будет отображаться в формате Фамилия ИО.
Контроль с помощью списков данных
Еще один вариант повышения достоверности вводимых данных - использование полей - фиксированных списков данных. Например, для оценки знаний студентов используются только четыре значения: неуд., удовл., хор. и отл. Для их фиксации используется мастер подстановок (последняя опция в списке допустимых типов данных). При этом СУБД запрашивает способ формирования перечня значений. В данном случае необходимо выбрать вариант Будет введен фиксированный набор значений и нажать кнопку Далее>. Затем система запросит количество столбцов для ввода данных (при небольшом количестве значений целесообразно выбирать один столбец) и предоставит место для ввода этих значений. В дальнейшем - уже при заполнении таблицы - в поле-списке появляется кнопка раскрытия, нажатие на которую приводит к отображению введенных ранее значений. Выбор конкретного значения осуществляется щелчком мыши.
Каждое поле таблицы можно снабдить сообщением-подсказкой, которое будет появляться при вводе пользователем ошибочного значения (Свойства поля – Сообщение об ошибке). Например, при вводе экзаменационных оценок сообщение может выглядеть следующим образом: "Экзаменационные оценки должны находиться в диапазоне 2 .. 5".
Контроль с помощью сверочных таблиц
Каждая БД представляет обычно несколько таблиц, число которых может доходить, в общем случае, до десятков и сотен. При этом часто оказывается, что в разных таблицах хранятся одинаковые данные. Пример: таблицы Студенты и Деканаты включают одинаковые поля Факультет (см. рисунок ниже). В этом случае контроль заполнения данного поля в таблице Студенты путем создания поля-списка нецелесообразен, так как изменение названий факультетов или их числа в таблице Деканаты потребует модификации поля-списка, т.е. изменения необходимо производить сразу в двух таблицах. В то же время связывание этих полей устраняет данную проблему, т.е. изменения таблицы Деканаты автоматически будет проявляться при заполнении таблицы Студенты. Такое поле таблицы, значения которого выбираются из значений первичного ключа некоторой другой таблицы, называется внешним ключом. В таблице может быть несколько внешних ключей, что упрощает ее заполнение корректными данными (в рассматриваемом примере на рисунке сверочные таблицы можно также создать для полей Группа и Школа):
ДЕКАНАТЫ
-
Факультет
|
Декан
|
Телефон
|
Аудитория
|
|
|
|
|
СТУДЕНТЫ
ФИО
|
Факультет
|
Группа
|
Пол
|
Адрес
|
Школа
|
Стипендия
|
|
|
|
|
|
|
|
Для рассматриваемого связывания полей из нескольких таблиц необязательно совпадение их имен (например, они могут называться в одной таблице как Факультет, а в другой - Фак-т), но обязательно совпадение их типов.
Очевидно, что формирование БД предполагает сначала создание сверочных таблиц, а затем уже формирование основных (целевых) таблиц. В дальнейшем сверочные таблицы могут подвергаться модификации (например, изменилось название факультета или в вузе появился новый факультет).
Связывание производится по следующему алгоритму:
Сверяемая таблица (например, СТУДЕНТЫ) открывается в режиме Конструктора.
В списке типов данных для нужного поля выбирается опция Мастер подстановок.
В появившейся карточке устанавливается опция Объект «столбец подстановки» будет использовать значения из таблицы или запроса и нажимается кнопка Далее>.
СУБД запрашивает имя сверочной таблицы. В появившемся списке всех таблиц БД необходимо выделить щелчком мыши необходимую и нажать кнопку Далее>.
В появившейся карточке слева приведен список всех полей выбранной таблицы, а справа находится окно, в которое переносятся связываемые поля (см. рисунок ниже). Для этого необходимо выделить мышью поле слева и нажать кнопку >. Если поле было выбрано ошибочно, то его выбор отменяется кнопкой <. Завершается выбор также нажатием кнопки Далее>.
Доступные поля
Выбранные поля
Факультет
Декан
Телефон
Аудитория
>
<
Далее >
В появившемся окне компьютер предлагает изменить название связываемого поля в целевой таблице на название связываемого поля в сверочной таблице. Целесообразно при разных названиях отказаться от замены и ввести уже существующее название. Далее снова нажимается клавиша Далее>.
В последнем окне нажимается клавиша Готово.
Связи таблиц конкретной базы данных можно просмотреть с помощью средств СУБД. Для этого на вкладке Работа с базами данных необходимо выбрать опцию  (Схема данных). Для рассмотренного примера эта схема имеет вид:
|
