Лабораторный практикум по информатике

После создания таблицы для каждой темы в базе данных нужно предоставить приложению Microsoft Access 2010 средства, с помощью которых можно будет вновь объединять сведения при необходимости. Это делается путем помещения общих полей в связанные таблицы и определения связей между таблицами. После этого можно создавать запросы, формы и отчеты, одновременно отображающие сведения из нескольких таблиц.

Межтабличные связи могут объединять две и более сущности. Как правило, они соответствуют некоторому взаимодействию между сущностями и описывают связь, возникающую между ними. Во время логического проектирования связи между таблицами могут обладать собственными атрибутами. Такое отношение выделяется в отдельную сущность типа связь.

Отношения делятся на три основных типа, в зависимости от количества записей сущности, связанных с записью другой сущности:

Один к одному: каждой записи первой сущности соответствует только одна запись второй сущности, а каждой записи второй сущности – только одна запись первой сущности. Пример: автор, у которого в данный момент имеется лишь одна незавершенная книга.

Один ко многим: каждой записи первой сущности могут соответствовать несколько записей второй сущности, однако каждой записи второй сущности соответствует только одна запись первой сущности. Пример: издательство, выпустившее несколько книг.

251

Многие ко многим: каждой записи первой сущности могут соответствовать несколько записей второй сущности, а каждой записи второй сущности соответствуют несколько записей первой сущности. Пример: один автор может написать несколько книг, а у одной книги может быть несколько авторов. В реляционных базах данных этот тип отношений не реализуем, поэтому создается дополнительная сущность, ассоциирующая данную связь (ассоциация).

Связи между сущностями устанавливаются по равенству значений первичного и внешнего ключей.

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

Межтабличные связи предоставляют сведения для структуры запросов.

Межтабличные связи предоставляют сведения для структуры форм и отчетов.

Межтабличные связи являются основой, с помощью которой можно обеспечить целостность данных.

Целостность данных означает систему правил, используемых в Microsoft Access для поддержания связей между записями в связанных таблицах, а также обеспечивающих защиту от случайного удаления или изменения связанных данных. Установить целостность данных можно, если выполнены следующие условия:

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

Связанные поля (первичный ключ главной таблицы и внешний ключ подчиненной таблицы) имеют один тип данных. Здесь существует исключение: поле счетчика может быть связано с числовым полем, свойство которого Размер поля (FieldSize) имеет значение Длинное целое.

252

Обе таблицы принадлежат одной базе данных Microsoft Access. Для установки целостности данных база данных, в которой находятся таблицы, должна быть открыта.

При установке целостности данных, необходимо помнить следующие правила:

Невозможно ввести в поле внешнего ключа связанной таблицы значение, не содержащееся в ключевом поле главной таблицы.

Не допускается удаление записи из главной таблицы, если существуют связанные с ней записи в подчиненной таблице.

Невозможно изменить значение первичного ключа в главной таблице, если существуют записи, связанные с данной записью.

Чтобы задать правила целостности данных для конкретной связи, при ее создании в Microsoft Office Access следует установить флажок Обеспечение целостности данных. Если данный флажок установлен, то любая попытка выполнить действие, нарушающее одно из перечисленных выше правил, приведет к выводу на экран предупреждения, а само действие будет отменено.

Чтобы преодолеть ограничения на удаление или изменение связанных записей, сохраняя при этом целостность данных, следует установить флажки Каскадное обновление связанных полей и Каскадное удаление связанных полей. Если установлен флажок Каскадное обновление связанных полей, то при изменении ключевого поля главной таблицы автоматически изменяются и соответствующие значения связанных записей. Если установлен флажок Каскадное удаление связанных полей, то при удалении записи в главной таблице удаляются и все связанные записи в подчиненной таблице.

После создания необходимых таблиц, полей и связей необходимо еще раз просмотреть структуру базы данных и выявить возможные недочеты. Желательно это сделать на данном этапе, пока таблицы не заполнены данными.

В Microsoft Access существует два инструмента, помогающих в усовершенствовании структуры баз данных:

Мастер анализа таблиц может проанализировать структуру таблицы, предложить подходящие новые структуры и связи, а также разделить таблицу на новые связанные таблицы, если это имеет смысл.

253

Анализатор быстродействия исследует всю базу данных, дает рекомендации по ее улучшению, а также осуществляет их.

4. Описание оборудования и используемых программных комплексов

При выполнении лабораторной работы необходим специализированный компьютерный класс с минимальными системными требованиями компьютеров:

Процессор – Intel Pentium III; ОЗУ – 256 Mb;

видеокарта – 32 Mb.

Требуемое программное обеспечение:

Операционная система Microsoft Windows, пакет прикладных программ Microsoft Office 2010.

5. Краткое руководство по эксплуатации оборудования

При использовании оборудования необходимо:

соблюдать общие правила нахождения в учебных лабораториях, работы с компьютером и использования программных средств; привести в порядок одежду; осмотреть рабочее место, убрать все мешающие работе предметы;

визуально проверить правильность подключения ПЭВМ к электросети.

6. Задания

1.Предметная область — отдел сбыта предприятия. Для данной предметной области: создать базу данных, создать формы для ввода данных, заполнить таблицы, выполнить сортировку записей в таблицах, создать запросы на добавление/редактирование/удаление, на выборку, на основе запроса на выборку создать отчет, создать отчет по одной из таблиц, сделать главную кнопочную форму.

2.По указанному преподавателем варианту выбрать предметную область из списка заданий для самостоятельного выполнения и спроектировать аналогичную базу данных.

254

7. Методика выполнения заданий

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

Предметная область – отдел сбыта предприятия. Решаемые задачи:

учет товаров (название, цена и др.), их реализация (дата отгрузки, кому, объем реализации) и оплаты (дата оплаты, от кого, сумма);

используется справочник клиентов фирмы. Ограничения целостности:

а) цена, объем реализации больше 0; б) клиент (кому и от кого) должен быть в справочнике фирмы.

Рекомендации. Для реализации задачи можно создать три таблицы: Товары, Реализация и Клиенты (она используется как справочник). Списки полей создаваемых таблиц и схема данных приведены на рис. 1.

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

В таблице Клиенты хранятся данные о всех клиентах предприятия, которые покупают его товары; ключевым в данной таблице является поле Номер клиента.

Чтобы описать реализацию конкретного товара конкретному клиенту используется таблица Реализация, в которой установлены связи с таблицами Товары и Клиенты (команда Работа с базами данных\Схема данных). Для всех таблиц устанавливаются ограничения целостности данных с каскадным обновлением и удалением связанных полей.

255

Рис. 1. Схема данных для базы данных «Отдел сбыта предприятия»

Поля Номер клиента и Шифр товара в таблице Реализация

определяются Мастером подстановок, используя подстановку значений соответствующих полей из ключевых таблиц Клиенты и Товары. Для ограничения ввода некорректных значений для полей

Цена за единицу и Объем реализации определено свойство Условие на значение: >0. Для единообразия при вводе значений в поле Категория товара, в нем с помощью Мастера подстановок определен фиксированный список значений, включающий в себя перечень этих категорий (например, молочная, мясная, хлебобулочная, кондитерская и т.п.). Ниже приведен список всех полей таблиц, их типов и свойств:

Ввести не менее 7 записей в таблицы Клиенты и Товары и не менее 15 записей в таблицу Реализация.

7.2. Работа с макетом таблицы

Открыть любую из таблиц базы данных в режиме таблицы и произвести изменения ее макета, пользуясь командами из меню

Формат:

поменять местами два столбца таблицы; закрепить первый столбец таблицы и скрыть один из ее столбцов при просмотре записей; изменить шрифт и оформление записей в таблице.

7.3. Сортировка записей

Открыть не ключевую таблицу базы данных и отсортировать в ней записи:

по возрастанию значений в первом поле таблицы и убыванию значений в любом из ее текстовых полей;

по убыванию значений одного числового поля и возрастанию другого числового поля.

При выполнении сортировки по нескольким полям рекомендуется использовать команду Главная/Сортировка и

фильтр/Дополнительно/Расширенный фильтр.

7.4. Выбор данных с помощью запросов

Создать запросы на выборку к одной из таблиц базы данных, используя в качестве условия отбора записей следующие критерии: точное значение в каком либо текстовом поле (например, выбрать

товары одной категории); диапазон значений в любом числовом поле (например, выбрать

товары, стоимость которых более 1 000 руб.); диапазон значений в любом текстовом поле (например, найти

товары, наименования которых начинаются на буквы М-С);

257

неточное совпадение значений в текстовом поле (например, найти клиентов, проживающих в одном городе).

логическое И, связывающее два значения одного числового поля (например товары, стоимость которых больше 200 руб. И меньше

500 руб.);

логическое И, связывающее значения двух разных полей (например, товары стоимостью свыше 200 руб., реализованные сегодня);

логическое ИЛИ, связывающее два значения в одном текстовом поле (например товары той или иной категории);

логическое ИЛИ, связывающее значения двух различных полей (например, товары, реализованные в определенный день, ИЛИ товары, чья стоимость не менее 100 руб.);

оператор NOT в текстовом поле (например, все товары, кроме определенной категории).

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

Для поиска по неточному совпадению значений использовать оператор Like, а также шаблоны для поиска (знак вопроса «?» заменяет один символ, звездочка «*» заменяет любое количество символов). Например, по шаблону Like A*, введенному в поле Фамилия, можно найти все фамилии, начинающиеся с буквы А; по шаблону Like A???, только те фамилии, которые начинаются с буквы А и состоят из 4 букв.

При определении диапазона значений, как в текстовых, так и в числовых полях, использовать операторы сравнения: >, <, >=, <=, <>.

Взапросах с логическим И следует учитывать правило: если связываемые критерии находятся в одном поле, то они разделяются оператором And или запятой; если связываемые критерии находятся

вразных полях, то они записываются на одной строке бланка запроса.

Взапросах с логическим ИЛИ следует учитывать правило: если связываемые критерии находятся в одном поле, то они разделяются оператором OR; если связываемые критерии находятся в разных полях, то они записываются на разных строках в бланке запроса.

258

Оператор Not используется для поиска значений, полностью несовпадающих с указанным. Например, критерий Not Уфа, введенный в поле Город, используется для поиска записей, для которых в данном поле указано любое другое значение.

Готовый запрос выполняется после щелчка по кнопке

7.5. Создание запросов с помощью построителя выражений

Создать запрос к базе данных, в котором одно из полей является результатом вычислений над значениями любого числового поля исходной таблицы:

подсчитать надбавку за доставку товара, равную 5% от цены; подсчитать стоимость товара одного наименования, реализованного данному клиенту.

Рекомендации. При создании вычисляемого поля в запросе формулу для вычисления следует вводить в бланке запроса в строке Поле. Для ввода формул использовать Построитель выражений

. Для определения подписи вычисляемого поля использовать свойство Подпись в окне Свойства поля (его можно вывести командой Конструктор/Показать или скрыть/Страница свойств).

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

Для сцепления текстовых полей в выражениях используется оператор &.Например, выражение:

=[Сотрудники]![Фамилия]& « » &[Сотрудники]![Имя]& « » &[Сотрудники]![Отчество]

создаст в запросе новое поле, значения которого получаются путем сцепления значений трех текстовых полей (Фамилия, Имя и Отчество) таблицы Сотрудники. В кавычках вводится один пробел для разделения слов.

7.6. Создание итоговых запросов

Создать итоговые запросы к базе данных, в которых использовать группировку по текстовому полю и итоговые функции в числовых полях:

259

оставить в запросе поля Категория и Цена, вычислить максимальное значение цены для каждой категории товара; подсчитать количество товаров у каждого клиента; подсчитать общий объем реализации для каждого товара.

Рекомендации. Для преобразования обычного запроса в

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

SUМ – вычисляет сумму всех значений заданного поля (для числовых или денежных полей), отобранных запросом;

AVG – вычисляет среднее значение в тех записях определенного поля, которые отобраны запросом (для числовых или денежных полей);

MIN – выбирает минимальное значение в записях определенного поля, отобранных запросом;

MAX – выбирает максимальное значение в записях определенного поля, отобранных запросом;

COUNT – вычисляет количество записей, отобранных запросом в определенном поле, в которых значения данного поля отличны от нуля;

FIRST – определяет первое значение в указанном поле записей; LAST – определяет последнее значение в указанном поле

записей.

7.7. Создание запросов-действий

Создайте запрос на обновление записей, позволяющий увеличить значение одного из числовых полей в два раза.

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

Создайте запрос к копии таблицы Товары, в котором будут удалены все записи о товарах одной категории.

260