Лабораторная работа №4
Microsoft Office ACCESS 2007.
Оглавление
Задания для подготовки к выполнению лабораторной работы: 2
Задания для выполнения лабораторной работы: 10
Задания для подготовки к выполнению лабораторной работы:
Постановка задачи
Реализовать базу данных (БД) по теме «Учет выдачи и возврата книг» в СУБД Microsoft Access 2007.
В результате проектирования БД «Учет выдачи и возврата книг» получены следующие таблицы:
Тематика (код тематики, наим. тематики)
Издательство (код изд-ва, наим. изд-ва)
Читатель (номер ЧБ, фам, адрес, год рожд, образование)
Книги (№ книги, наим. книги, авторы, код тематики, код изд-ва, адрес хранения)
Учет книг (номер ЧБ, № книги, дата возврата, дата выдачи, дата факт. возврата)
Перед созданием таблиц в СУБД необходимо для каждого поля (столбца) таблиц определить некоторые характеристики (полужирным шрифтом выделены ключевые поля):
Тематика
Характеристики поля Поле | Тип поля | Списочный характер | Возможные ограничения | Индексируемость | Обязательность заполнения |
Код тематики | Счетчик | - | - | - | + |
Наименование тематики | Текстовый | - | - | + | + |
Читатель
Характеристики поля Поле | Тип поля | Списочный характер | Возможные ограничения | Индексируемость | Обязательность заполнения |
Номер ЧБ | Числовой | - | - | - | + |
Фамилия | Текстовый | - | - | + | + |
Адрес | Текстовый | - | - | - | + |
Год рождения | Числовой | - | >1920 And <2005 | - | - |
Образование | Мастер подстановок | неполное среднее, среднее, высшее | - | - | - |
Издательство
Характеристики поля Поле | Тип поля | Списочный характер | Возможные ограничения | Индексируемость | Обязательность заполнения |
Код издательства | Счетчик | - | - | - | + |
Наименование издательства | Текстовый | - | - | + | + |
Книги
Характеристики поле Поле | Тип поля | Списочный характер | Возможные ограничения | Индексируемость | Обязательность заполнения |
№ книги | Числовой | - | - | - | + |
Наименование книги | Текстовый | - | - | - | + |
Авторы | Текстовый | - | - | + | + |
Код тематики | Мастер подстановок (поле Наименование тематики из таблицы «Тематика») | - | - | - | + |
Код издательства | Мастер подстановок (поле Наименование издательства из таблицы «Издательство») | - | - | - | + |
Адрес хранения | Мастер подстановок | Отдел 1 Отдел 2 | - | - | + |
Учет книг
Характеристики поля Поле | Тип поля | Списочный характер | Возможные ограничения | Индексируемость | Обязательность заполнения |
Номер ЧБ | Мастер подстановок (Номер ЧБ из таблицы «Читатель») | - | - | - | + |
№ книги | Мастер подстановок (№ книги из таблицы «Книги») | - | - | - | + |
Дата выдачи | Дата/время | - | - | - | + |
Дата возврата | Дата/время | - | - | - | + |
Дата фактвозврата | Дата/время | - | - | - | - |
Запустить MS Access 2007, создать новую базу данных «Учет выдачи и возврата книг». Создание таблицы «Тематика»:
- на закладке Главная с помощью кнопки Вид перейти в режим Конструктора:
- внести названия и типы полей таблицы «Тематика»:
- в разделе Свойства поля на закладке Общие для поля Наименование тематики установить характеристики индексируемости и обязательности:
- на закладке Главная с помощью кнопки Вид перейти в Режим таблицы и ввести данные в таблицу «Тематика»:
Создание таблицы «Читатель»:
- на закладке Создание с помощью кнопки Таблица создать новую таблицу и сохранить под именем «Читатель»:
- перейти в режим Конструктора и внести названия и типы полей таблицы «Читатель»:
- для поля Год рождения в разделе Свойства поля на закладке Общие внести соответствующее ограничение целостности в строку Условие на значение;
- так как поле Образование должно быть представлено в виде списка, поэтому для его создания выберите тип данных Мастер подстановок. Затем в появившемся окне необходимо выбрать опцию Будет введен фиксированный набор значений, далее, отвечая на вопросы Мастера, ввести значения элементов списка;
- в разделе Свойства поля на закладке Общие для введенных полей установить соответствующие характеристики индексируемости и обязательности; внести данные в таблицу «Читатель».
Аналогично создать таблицы «Издательство», «Книги», «Учет книг», учитывая характеристики полей в соответствующих таблицах, и внести данные. Обратите внимание, в каком порядке создавались таблицы и вносились в них данные!
Схема данных:
- на закладке Работа с базами данных с помощью кнопки Схема данных вызвать схему данных и поместить на неё все имеющиеся таблицы.
В случае использования Мастера подстановок, реализующего связи между таблицами, на Схеме данных уже будут отображены связи между таблицами (в противном случае, с помощью мыши установить связи между таблицами по одинаковым по смыслу полям). Для каждой связи вызвать команду Изменение связи и в появившемся окне установить флажок Обеспечение целостности данных:
Внешний вид схемы данных должен быть следующий:
Создание форм:
Экранные формы позволяют организовать наглядную и удобную работу с базой данных, состоящей из большого количества связанных таблиц реляционной базы данных. Имеющийся в системе Мастер разработки экранных форм позволяет легко создавать экранные формы нескольких видов (простые - для работы с данными одной таблицы, более сложные - для работы с несколькими таблицами с использованием подчиненных форм).
- на закладке Создание с помощью кнопки Форма создать формы для каждой таблицы, улучшая внешний вид каждой формы при использовании закладки Формат.
Создание отчета
Пусть требуется создать отчет по читателям и темам с итогами (число выданных книг) по читателям и темам. Для реализации отчета необходимо выбрать из базы данных соответствующие данные, то есть создать запрос:
- на закладке Создание с помощью кнопки Конструктор запросов создать новый запрос и сохранить под именем «Книги читателя»; поместить в созданный запрос те таблицы, сведения из которых необходимы для будущего отчета, и выбрать из этих таблиц соответствующие поля:
- проверить правильность работы запроса с помощью закладки Конструктор и команды Выполнить; на закладке Создание с помощью кнопки Мастер отчетов создать простой настраиваемый отчет на основе запроса «Книги читателей». При работе с Мастером отчетов необходимо установить следующие уровни группировки:
- для добавления итогов откройте созданный отчет в Режиме макета и на закладке Формат в списке Итоги выберите Количество значений. Таким образом, общий вид отчета должен быть следующим:
Элементы администрирования базы данных
В MicroSoft Office Access 2007 предусмотрена улучшенная модель безопасности, которая упрощает процесс защиты базы данных и ее открытия с включенной защитой. Примечание: хотя предлагаемые методы повышают уровень безопасности, наилучший способ защиты данных — хранение таблиц на специальном сервере (например, на компьютере, на котором выполняется Службы Microsoft Windows SharePoint Services 3.0) и хранение форм и отчетов на локальных компьютерах или в общих сетевых ресурсах. Cредства обеспечения безопасности в Office Access 2007:
Шифрование базы данных паролем. В средстве шифрования, доступном в MicroSoft Office Access 2007, объединены и усовершенствованы два предыдущих средства — применение паролей и шифрование базы данных. При использовании пароля базы данных для шифрования базы данных эти данные становятся недоступны для других средств, и другие пользователи вынуждены вводить пароль, чтобы получить доступ к этой базе данных. Для шифрования в Access 2007 используется более эффективный алгоритм, чем в более ранних версиях Access.
- Откройте базу данных в монопольном режиме, для этого:
- щелкните значок Кнопка Office
, а затем выберите команду Открыть; в диалоговом окне Открытие найдите файл, который нужно открыть, и выделите его (одним щелчком); нажмите стрелку рядом с кнопкой Открыть и выберите вариант Монопольно: 
- на закладке Работа с базами данных в группе Работа с базами данных щелкните Зашифровать паролем:
- откроется диалоговое окно Задание пароля базы данных, введите пароль в поле Пароль, повторите его в поле Подтверждение и нажмите кнопку ОК; самостоятельно изучить возможности дешифровки базы данных.
Создание резервной копии базы данных.
- щелкните значок Кнопка Office
, выберите команду Управление, затем Резервная копия базы данных; в появившемся окне Сохранение укажите место для сохранения резервной копии базы данных. Упрощенное открытие баз данных.
В предыдущих версиях Access пользователю приходилось отвечать на различные предупреждающие сообщения — например о безопасности макросов и изолированном режиме. По умолчанию, если в Office Access 2007 открывается база данных, расположенная не в доверенном месте, отображается только панель сообщений.
Если файлы базы данных (как в новом формате Office Access 2007, так и в более ранних) расположены в надежном месте, например в папке или в общем сетевом ресурсе, которые указаны как надежные, они будут открываться и обрабатываться без сообщений с предупреждениями и запроса о включении или отключении содержимого. Описанная ниже последовательность шагов объясняет, как найти или создать надежное расположение, а затем добавить туда базу данных.
- щелкните значок Кнопка Office
, а затем - кнопку Параметры Access. Примечание: открывать базу данных не требуется; в открывшемся диалоговом окне Параметры Access выберите пункт Центр управления безопасностью и в группе Центр управления безопасностью Microsoft Office Access нажмите кнопку Параметры центра управления безопасностью; выберите Надежные расположения, просмотрите уже созданные надежные расположения; для создания нового надежного расположения используйте кнопку Добавить новое расположение, а затем укажите значения параметров в диалоговом окне Надежное расположение Microsoft Office; для размещения базы данных в надежном расположении можно воспользоваться проводником Windows или открыть файл в Access и сохранить его в надежном расположении; для открытия базы данных в надежном расположении можно использовать любой привычный способ. Например, выбрать и затем дважды щелкнуть файл в проводнике Windows либо, если уже запущен Access, нажать кнопку Microsoft Office 
для поиска и открытия файла. Если база данных Office Access 2007 размещена в надежном расположении, при ее открытии работают все коды VBA, макросы и безопасные выражения. При этом не возникает необходимость решать вопросы доверия.
Задания для выполнения лабораторной работы:
Создание запросов с параметрами
Пусть в созданной БД «Учет выдачи и возврата книг» требуется получить сведения о книгах по определенной тематике, для этого необходимо создать запрос и в условие отбора ввести нужное значение:
В случае необходимости выдать сведения о книгах по другой тематике нужно изменить условие отбора. Для того чтобы не менять условие отбора каждый раз, можно создать запрос, в котором Наименование тематики будет параметром, запрашиваемым при выполнении, для этого в строку Условие отбора для поля Наименование тематики ввести вместо конкретного значения приглашение к вводу параметра [Введите тематику:]:
После запуска такого запроса появится диалоговое окно с введенным вами приглашением к вводу параметра:
В результирующее множество запроса попадут все книги с тематикой Информатика, которые имеются в БД. Запрос может содержать не один, а несколько параметров, и все они по очереди будут запрашиваться при его выполнении.
Использование выражений в запросах с параметрами
При создании запроса часто используются выражения.
Выражение — это сочетание нескольких (или всех) из указанных элементов: встроенные или пользовательские функции, идентификаторы, операторы и константы.
Например, следующее выражение содержит все четыре элемента:
=Sum([Закупочная цена])*0,08
В данном примере Sum() — встроенная функция, [Закупочная цена] — идентификатор, * — математический оператор, а 0,08 — константа.
Логические операторы
Логические операторы применяются для объединения двух логических значений и возврата значения «истина», «ложь» или null.
Оператор | Назначение | Пример |
And | Возвращает значение «истина», если Выражение1 и Выражение2 истинны. | Выражение1 And Выражение2 |
Or | Возвращает значение «истина», если Выражение1 или Выражение2 истинны. | Выражение1 Or Выражение2 |
Eqv | Возвращает значение «истина», если Выражение1 и Выражение2 истинны или Выражение1 и Выражение2 ложны. | Выражение1 Eqv Выражение2 |
Not | Возвращает значение «истина», если выражение ложно. | Not Выражение |
Xor | Возвращает значение «истина», если истинно Выражение1 или Выражение2 (но не оба выражения). | Выражение1 Xor Выражение2 |
Специальные операторы
Принципы использования специальных операторов для возвращения значения «истина» или «ложь» представлены в следующей таблице.
Оператор | Назначение | Пример |
Is Null или Is Not Null | Определяет, является ли значение Null или не Null. | Поле1 Is Not Null |
Like «шаблон» | Сопоставляет строковые значения с помощью логических операторов? и *. | Поле1 Like "инструк*" |
Between значение1 And значение2 | Определяет, попадает ли числовое значение или значение даты в указанный диапазон. | Поле1 Between 1 And 10 |
In(значение1,значение2...) | Определяет, входит ли значение в набор значений. | Поле1 In ("красный","зеленый","синий") |
Выражения можно использовать и в запросах с параметрами, например, запрос по выборке книг, выданных в определенный период, будет выглядеть следующим образом:
При вводе значений параметров типа Дата необходимо контролировать значения дат, для этого вызовите окно Параметры запроса (контекстное меню на свободном поле верхней панели запроса/ Параметры или Конструктор/ Показать или скрыть/ Параметры). В столбец Параметр введите значение параметра точно так, как он определен в условии отбора. В столбце Тип данных выберите из раскрывающегося списка тип Дата/время. Аналогично введите второй параметр:
Пример использования специального оператора In. Пусть требуется отобрать книги нескольких авторов. Это можно сделать двумя способами:
1 способ
2 способ
Выражения можно формировать с помощью Построителя выражений. Это средство предоставляет удобный доступ к именам полей и элементов управления, используемым в базе данных, а также к множеству других встроенных функций, применяемых во время написания выражений.
Построитель выражений предоставляет возможность поиска и вставки компонентов выражения, которые сложно запомнить, например идентификаторов (имен полей, таблиц, форм, запросов и т. д.), а также имен и аргументов функций.
С помощью Построителя выражений можно написать выражение «с нуля» или выбрать одно из готовых выражений для отображения номеров страниц, текущей даты, а также текущей даты и времени.
Поле выражения
Верхняя область Построителя содержит поле выражения, применяемое для формирования выражения. Можно ввести выражение в поле вручную или выбрать нужные элементы из трех столбцов в нижней области построителя, а затем вставить их в поле выражения. Чтобы добавить элемент, дважды щелкните его и нажмите кнопку Вставить.
Кнопки операторов
В средней части Построителя выражений отображаются кнопки для вставки в выражение наиболее распространенных арифметических и логических операторов. Чтобы вставить оператор в поле выражения, нажмите соответствующую кнопку. Чтобы отобразить более длинный список операторов, которые можно использовать в выражениях, щелкните папку Операторы в левом нижнем столбце, содержащую элементы выражения, а затем щелкните нужную категорию в среднем столбце. В правом столбце отобразятся все операторы выбранной категории. Чтобы вставить оператор, дважды щелкните его.
Элементы выражения
В нижней области содержатся три столбца.
В левом столбце отображаются папки с таблицами, запросами, формами и отчетами в базе данных, а также доступные встроенные функции и заданные пользователем функции, константы, операторы и часто используемые выражения.
В среднем столбце отображаются определенные элементы или типы элементов из папки, выбранной в левом столбце. Например, если выбрать в левом столбце Встроенные функции, то в среднем столбце появится список типов функций.
Запуск Построителя выражений из запроса
Откройте запрос в режиме Конструктора.
Щелкните ячейку в бланке запроса, куда требуется вставить выражение. Например, щелкните ячейку Условие отбора в столбце, для которого требуется задать условие отбора, или ячейку Поле в столбце, где требуется создать вычисляемое поле.
На вкладке Конструктор в группе Настройка запроса щелкните Построитель.
Использование статистических функций в запросе
Другим типом расчетов, которые могут выполняться в запросе, является подсчет итоговых значений. Например, для числового поля таблицы можно вычислить среднее значение или сумму значений для всех или отобранных записей, можно подсчитать количество записей, возвращаемых запросом, и т. д. В этих запросах используются статистические функции. Описание этих функций приведено в таблице.
Функция | Описание |
Avg() | Вычисляет арифметическое среднее набора чисел, содержащихся в указанном поле запроса |
Count() | Вычисляет количество непустых записей, возвращаемых запросом |
First() | Возвращает значение поля из первой записи результирующего запроса |
Last() | Возвращает значение поля из последней записи результирующего набора |
Max() | Возвращает максимальное значение из набора, содержащегося в указанном поле |
Min() | Возвращает минимальное значение из набора, содержащегося в указанном поле |
Sum() | Возвращает сумму набора значений, содержащихся в заданном поле |
Использование статистических функций для расчета итоговых значений тесно связано с применением групповых операций в запросе. Групповые операции позволяют задать группы, для которых выполняются вычисления. Ниже приведены примеры таких расчетов, как на всем множестве записей, так и на подмножествах, отобранных условиями в запросе.
Необходимо подсчитать количество книг в библиотеке по каждой тематике, для этого:
с помощью Конструктора создайте запрос на основе таблиц Книги и Тематика; поместите в бланк запроса поля Наименование тематики и Наименование книги; щелкните на кнопке
Групповые операции на панели инструментов. В бланк запроса добавляется строка Групповая операция, содержащая по умолчанию в каждой ячейке значение Группировка; для поля Наименование тематики оставьте значение Группировка, для поля Наименование книги выберите из списка функцию Count; нажмите кнопку Запуск на панели инструментов для просмотра результатов запроса. Вы получите таблицу, которая содержит список тематик книг, и для каждой тематики указывается количество книг в библиотеке, относящихся к ней. В предыдущем примере расчеты производились над всеми имеющимися в таблице Тематика записями. Однако иногда требуется выполнить вычисления только над отобранным набором записей. Для этого вместе с групповыми операциями необходимо использовать и параметры.
Способы объединения таблиц в запросах
Когда в запрос включатся несколько таблиц, для получения нужных результатов используются объединения. Объединение помогает извлечь из каждой включенной в запрос таблицы только нужные записи, на основании сведений о том, как эти таблицы связаны с другими таблицами в запросе.
Существует следующие основные типы объединений: внутренние объединения и внешние.
Внутренние объединения — наиболее часто встречающийся тип объединений. Они представляют собой запросы, в которых строки одной из объединяемых таблиц соответствуют строкам другой таблицы по значениям в связанных полях. При выполнении запроса с внутренним объединением в операцию включаются только строки, имеющие одинаковые значения в обеих связанных таблицах.
Когда следует использовать внутреннее объединение?
Внутреннее объединение используется в том случае, когда запрос должен возвращать только те строки обеих таблиц объединения, значения связанных полей которых совпадают.
Как следует использовать внутреннее объединение?
В большинстве случаев для использования внутреннего объединения не нужно предпринимать каких-либо действий. Если ранее в окне схемы данных были созданы связи между таблицами, то при добавлении связанных таблиц в режиме конструктора запроса линии объединения создаются автоматически.
Внешние объединения представляют собой запросы, в которые включаются все строки одной таблицы, а также строки другой таблицы, значения которых в первой и второй таблице совпадают, несмотря на то, что некоторые из строк с обеих сторон полностью совпадают.
Внешние объединения являются направленными, они могут быть левыми или правыми. Можно легко понять, какая таблица в данном объединении является левой или правой, дважды щелкнув линию связи и посмотрев в диалоговое окно Параметры объединения.
Поскольку некоторые строки с одной стороны внешнего объединения не будут иметь в другой таблице соответствующих им строк, некоторые поля, принадлежащие другой таблице и возвращаемые в результатах запроса, будут иметь пустые значения.
Когда следует использовать внешнее объединение?
Используйте внешнее объединение, когда возникнет необходимость, чтобы в результаты объединения были включены все строки одной из таблиц и только те строки другой таблицы, значения связанных полей в которых соответствуют значениям связанных полей в первой таблице.
Как следует использовать внешнее объединение?
Внешние объединения создаются путем изменения внутренних объединений.
Пусть в таблице Тематика присутствует тематика, по которой нет книг в таблице Книги. Рассмотрим различные варианты выбора книг по тематикам:
с помощью Конструктора создайте запрос на основе таблиц Книги и Тематика; в режиме Конструктора запроса дважды щелкните объединение между таблицами, которое следует изменить. На экране появится диалоговое окно Параметры объединения. В этом окне обратите внимание на варианты: параметр 1 соответствует внутреннему объединению, параметр 2 – левое внешнее объединение, параметр 3 – правое внешнее объединение. Установите объединение всех записей из таблицы Тематика и только тех записей из таблицы Книги, в которых связанные поля совпадают; В Microsoft Access отобразится объединение и стрелка, соединяющая источник данных, все строки которого включаются в объединение, с источником данных, из которого выбираются только строки, удовлетворяющие условию объединения; нажмите кнопку Запуск на панели инструментов для просмотра результатов запроса. Вы получите таблицу, которая содержит список всех тематик книг, для каждой тематики указаны книги, относящиеся к ней, при этом одна их тематик будет иметь пустое значение; установите для этой связи внутреннее объединение, просмотрите результат и сравните его с результатом внешнего объединения.Задание для выполнения лабораторной работы
Создать запрос: выводящий информацию о книгах, взятых в определенный день; позволяющий отобрать всех читателей, записавшихся в библиотеку в октябре текущего года; подсчитывающий количество обращений читателей в библиотеку (предусмотреть ситуацию, когда читатель записался в библиотеку, но не брал книг, в этом случае запрос должен выдавать для такого читателя 0); выводящий информацию о книгах из всех тематик, содержащих корень «инфо» (например, «Информатика», «Информационные технологии в образовании»); выводящий фамилии задолжников вместе с названиями книг, которые они не вернули.