47681

Работа с системой управления базами данных Microsoft Access 2007. Учебно-практическое пособие

Книга

Информатика, кибернетика и программирование

Нестерова Работа с системой управления базами данных Microsoft ccess 2007 Учебнопрактическое пособие Киров 2010 Работа с системой управления базами данных Microsoft ccess 2007 Учебнопрактическое пособие. Может понадобиться ввод новых данных редактирование имеющихся их просмотр и представление в виде отчета. Табличный процессор Microsoft Excel позволяет хранить данные в одной или нескольких электронных таблицах проводить однотипные расчеты с большими наборами данных что характерно для экономических задач формировать сводные таблицы и...

Русский

2013-12-01

1.17 MB

16 чел.

PAGE   \* MERGEFORMAT 45

Федеральное агентство по образованию

Государственное образовательное учреждение

высшего профессионального образования

«Вятский государственный университет»

Кафедра прикладной информатики

А.А. Голованов, Л.А. Нестерова

Работа с системой управления базами данных Microsoft Access 2007

Учебно-практическое пособие

Киров 2010

Работа с системой управления базами данных Microsoft Access 2007 / Учебно-практическое пособие. Для студентов экономических специальностей.

Составители: А.А. Голованов, Л.А. Нестерова

©  Вятский государственный университет,

Киров, 2010


Введение

Любое предприятие, организация, учреждение обрабатывает большой объем информации, связанной как с внешней средой, так и с внутренними операциями, начиная от начисления заработной платы сотрудникам и заканчивая учетом операций с материальными и денежными средствами внутри и вне фирмы. Эти данные могут представлять собой бизнес-информацию (суммы платежей в платежных ведомостях, данные о сотрудниках и контрагентах и т.д.), предоставляться другой системой (информация о курсах валют, акций), носить временный характер (результаты промежуточных преобразований информации, имена временных файлов и т.д.). Независимо от типа используемой информации и способа работы с ней, данные должны быть структурированы и логически организованы. Может понадобиться ввод новых данных, редактирование имеющихся, их просмотр и представление в виде отчета.

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

Для многих экономических задач, связанных с необходимостью накопления, хранения и обработки больших объемов данных, весьма удобна СУБД Microsoft Access 2007, входящая в состав пакета Microsoft Office 2007.

СУБД Microsoft Access 2007 поддерживает самую распространенную модель хранения данных – реляционную. Основа реляционной модели – отношение (relation). Оно представляется обычной двумерной таблицей при соблюдении определенных ограничивающих условий. Таблица понятна, обозрима и привычна для человека. Набор отношений (таблиц) может быть использован для хранения данных об объектах реального мира и моделирования связей между ними. Далее вместо термина «отношение» будем применять термин «таблица», как наиболее часто используемый в практике, но с учетом ограничений, определенных ниже.

Реляционная база данных (БД) – набор взаимосвязанных таблиц. Каждая таблица содержит информацию об однотипных объектах (предметах, процессах, явлениях) и представляется в памяти компьютера, как правило, в виде файла. Каждая строка в таблице содержит информацию о конкретном объекте и называется записью. Запись представляет собой набор полей, каждое из которых соответствует одному свойству (атрибуту) объекта (Фамилия, Имя, Адрес, Количество, Цена и т.д.). Все записи в одной таблице содержат одинаковый по количеству, именам и типам набор полей.

Различают 2 класса таблиц в зависимости от содержания:

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

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

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

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

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

Еще одно ограничение в реляционной БД – каждая таблица должна быть нормализована, т.е. каждый атрибут должен быть простым. Если в таблице наблюдается избыточность, повторяемость значений атрибутов, ее нужно нормализовать. Нормализация - это пошаговый обратимый процесс разложения исходных таблиц на другие, более мелкие и простые таблицы.

Реляционная модель данных накладывает на таблицы следующие условия и ограничения:

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

2. все строки таблицы должны иметь одну и ту же структуру, т.е. одно и то же количество атрибутов с соответственно совпадающими именами;

3. имена столбцов таблицы должны быть различны, а значения в столбцах должны быть однотипными;

4. значения атрибутов должны быть простыми (атомарными);

5. должна соблюдаться ссылочная целостность для внешних ключей.

Таблицы, с которыми будем работать в дальнейшем, должны удовлетворять этим условиям.

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

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

Данные целесообразно размещать в базу данных, если:

  •  отсутствует фиксированный перечень стандартных форм отчета;
  •  отсутствует перечень стандартных запросов;
  •  данные должны использоваться несколькими приложениями или пользователями;
  •  данные уже находятся в базе данных.

1 Основные понятия

База данных (БД) наиболее часто определяется, как совокупность взаимосвязанных данных конкретной предметной области различного назначения. Отличительные особенности БД:

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

2. Наличие специальных языковых и программных средств, облегчающих для пользователей выполнение всех операций, связанных с организацией хранения данных, их корректировки, обработки и доступа к ним. Такая совокупность языковых и программных средств называется системой управления базой данных (СУБД).

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

База данных СУБД Microsoft Access 2007 представляет собой файл данных, независимый от прикладных программ. Этот файл сохраняется в памяти с расширением .accdb. Это новый формат, используемый только в Access 2007. При необходимости базу данных можно сохранить в формате предыдущих версий с расширением .mdb.

Таблица – это способ логической группировки взаимосвязанных элементов данных, при котором они организуются в виде строк и столбцов. Столбцы соответствуют полям, строки – записям. Таким образом, таблица – это группа записей данных, каждая из которых содержит информацию об одном объекте. Например, таблица может содержать номер телефона абонента, его адрес и сумму платежей за междугородние переговоры. Каждая таблица имеет свое Имя.

В БД каждая запись содержит информацию об одном объекте. Записи в БД нумеруются, и нужная запись может быть найдена по ее номеру.

Каждой колонке таблицы в БД соответствует поле. Поля являются элементами записи. Каждая запись состоит из одинакового набора полей. Каждое поле имеет набор свойств:

  •  Имя поля позволяет обращаться к данным соответствующего столбца таблицы при автоматических операциях с БД;
  •  Тип поля определяет, какого типа данные могут содержаться в поле. В Microsoft Access 2007 используются следующие типы полей:
    •  текстовый – для хранения текстовой информации любого рода (букв, цифр, знаков пунктуации, различных символов), размер текстового поля – не более 255 символов;
    •  числовой – для хранения действительных чисел (целых, длинных целых, с плавающей точкой, действительных и др.),
    •  денежный – для хранения денежных единиц, в отличие от числового он ограничен 4 знаками после точки десятичной дроби, что позволяет избавиться от проблем с округлением значений, поэтому вычисления, связанные с денежными единицами всегда будут точными;
    •  дата/время – для хранения календарных дат и текущего времени,
    •  счетчик – специальный вариант числового типа полей для порядковой нумерации записей, это поле заполняется не пользователем, а Access;
    •  Мемо – текстовый тип, не имеющий ограничений для хранения больших объемов информации (хранят ссылки на место в памяти, где эта информация хранится, благодаря этому длина примечаний практически не ограничена);

и др.

  •  Размер поля определяет максимальную длину данных в символах (или в байтах, поскольку один символ представляется в компьютере восемью двоичными разрядами, т.е. одним байтом информации), которые могут храниться в поле;
  •  Формат поля – указывает способ изображения данных в поле;
  •  Маска ввода определяет форму ввода данных пользователем указанием последовательности символов, с которой должны совпадать вводимые в поле данные;
  •  Подпись – заголовок столбца таблицы (если свойство не установлено, то по умолчанию оно имеет то же значение, что свойство Имя поля);
  •  Значение по умолчанию – значение, которое вводится в ячейки автоматически;
  •  Условие на значение используется для проверки правильности данных в поле;
  •  Обязательное поле устанавливает обязательность заполнения поля;
  •  Индексированное поле позволяет ускорить операции, связанные с поиском или сортировкой данных, хранящихся в поле.

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

Индекс – специальный тип данных в таблице, который может содержать значения ключевых полей и указатели позиции записи. Эти значения и указатели записываются в порядке, определяемом пользователем, и могут быть использованы для представления данных в БД в том или и ином порядке. Индексы можно использовать для сортировки информации, например, по фамилии в алфавитном порядке. Можно создать другой индекс, который будет сортировать информацию по номеру телефона.

Обращение к таблице базы данных за какой-либо информацией или для выполнения операций над таблицами называется запросом. В результате запроса к БД может быть получено новое логическое представление запрошенных данных, то есть будет получена виртуальная таблица из числа имеющихся в БД.

2 Структура реляционной базы данных

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

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

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

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

Разработчики БД используют определенные руководящие принципы, следуя которым исходная модель БД приводится к необходимой НФ, являющейся для нее наиболее оптимальной. На практике чаще используются 1НФ, 2НФ и ЗНФ, иногда –НФ Бойса-Кодда, редко – 4НФ и крайне редко – 5НФ. Отметим, что экономические задачи приводятся максимум к 3НФ.

Существуют два требования, которые выдвигаются для любой НФ:

  •  каждая последующая НФ должна быть лучше предыдущей;
  •  при переходе к следующей НФ свойства предыдущих нормальных форм сохраняются.

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

Вначале подробно исследуем предметную область.

Процедура заказа сопровождается оформлением документа, пример которого показан на рис. 1.

Заказ № _1_

Дата 02.12.09

Покупатель Атлант

Код покупателя 1

Фамилия Балаганов

Город Черноморск

Адрес ул. Садовая, 45, кв.8

№ пп

Наименование товара

Код товара

Ед.изм.

Цена за ед.

Кол-во

Сумма,

руб

НДС,

%

Сумма с НДС,

руб

1

Бананы

1

кг

50

100

5000

25

5750

2

Конфеты

2

кг

125

80

10000

25

11500

3

Яблоки

3

кг

45

100

4500

25

5175

Итого:

22425

Рисунок 1 Пример заполненного бланка заказа

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

Вначале исследуем предметную область. Прежде всего в базе должна храниться информация о каждом покупателе, в частности его наименование, код, фамилия контактного лица, город, где он находится и адрес регистрации. Необходима информация о товарах, имеющихся на оптовой базе: наименования товаров, их условные коды, единицы измерения, цена единицы каждого товара. Целесообразно иметь информацию о поставщиках товаров, а также знать, сколько каждого товара имеется в наличии на складе. Кроме того, необходимо учитывать информацию о каждом заказе, фиксирующую дату заказа, покупателя, количество конкретного товара в каждом заказе, сумму сделки и сумму сделки с НДС.

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

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

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

Проведем процесс нормализации с помощью нормальных форм.

Первая нормальная форма (1НФ).

Свойства 1НФ:

  •  в таблице нет одинаковых строк (в терминологии реляционной модели данных – кортежей);
  •  атрибуты (поля) не упорядочены и различны по наименованию;
  •  кортежи не упорядочены;
  •  значения атрибутов неделимы.

При построении логической модели данных можно всю информацию, описывающую предметную область, представить в виде одной таблицы, которая будет содержать необходимые поля и удовлетворять 1НФ. Заметим, что в 1НФ можно представить любой экономический документ.

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

  •  КодЗаказа – учетный номер заказа,
  •  Дата – дата сделки,
  •  Покупатель – наименование организации-покупателя,
  •  ИдКл – идентификатор или код покупателя,
  •  Фамилия – фамилия контактного лица,
  •  Город – местонахождение организации
  •  Адрес – юридический адрес организации,
  •  Товар – наименование товара,
  •  КодТовара – идентификатор или код товара,
  •  Поставщик – наименование поставщика,
  •  ЕдИзм – единица измерения количества товара,
  •  Цена – цена единицы товара,
  •  Кол-во – количество товара в заказе
  •  Сумма – сумма сделки,
  •  НДС – налог на добавленную стоимость,
  •  СуммаНДС – сумма сделки с учетом НДС,
  •  В наличии – количество товара на складе

Фрагмент сводной таблицы о заказах показан в таблице 1.

В ней поле КодЗаказа однозначно определяет каждый заказ и является первичным ключом. Поскольку для оформления заказа необходимо указать покупателя, товар и дату сделки, обязательными полями, которые необходимо заполнить являются ИдКл, КодТовара и Дата. Таким образом, составной ключ сводной таблицы будет состоять из полей КодЗаказа, ИдКл, КодТовара и Дата.

В результате образовалась довольно большая таблица, которая удовлетворяет всем свойствам 1НФ. Однако с ней неудобно работать ввиду ее громоздкости и избыточности. Многие данные дублируются по несколько раз. Например, для каждого покупателя дублируются значения полей ИдКл, Фамилия, Город, Адрес. При работе с такой таблицей будут возникать аномалии, которые ухудшают скорость выполнения задач и создают несоответствие модели данных предметной области.


Таблица 1

Сводная информация о заказах

КодЗа-каза

Дата

Поку-па-тель

ИдКл

Фами-лия

Город

Адрес

Товар

Код

Това-ра

Пос-тав-щик

Ед

Изм

Цена, руб

Кол-во

Сум-ма, руб.

НДС

Сум-ма

НДС, руб.

В нали-чии

1

02.12.09

Атлант

1

Бала-ганов

Черно-морск

ул. Садовая, 45, кв.8

Бананы

1

АО Экзо-тика

Кг.

50

100

5000

25

5750

200

1

02.12.09

Атлант

1

Бала-ганов

Черно-морск

ул. Садовая, 45, кв.8

Конфе-ты

2

ЗАО Рот Фронт

Кг.

125

80

10000

25

11500

300

1

02.12.09

Атлант

1

Бала-ганов

Черно-морск

ул. Садовая, 45, кв.8

Яблоки

3

АО Уме-лец

Кг.

45

100

4500

25

5175

500

2

02.12.09

Рога и копыта

2

Бендер

Москва

пр. Героев, 31, кв.5

Бананы

1

АО Экзо-тика

Кг.

50

50

2500

25

3125

200

2

02.12.09

Рога и копыта

2

Бендер

Москва

пр. Героев, 31, кв.5

Конфе-ты

2

ЗАО Рот Фронт

Кг.

125

50

6250

25

7813

300

3

02.12.09

Метеор

3

Щуки-на

Москва

Речной пер., 3, кв.17

Конфе-ты

2

ЗАО Рот Фронт

Кг.

125

40

5000

25

6250

300

3

02.12.09

Метеор

3

Щуки-на

Москва

Речной пер., 3, кв.17

Яблоки

3

АО Уме-лец

Кг.

45

50

2250

25

2813

500

5

06.12.09

Атлант

1

Бала-ганов

Черно-морск

ул. Садовая, 45, кв.8

Бананы

1

АО Экзо-тика

Кг.

50

30

1500

25

1875

200

5

06.12.09

Атлант

1

Бала-ганов

Черно-морск

ул. Садовая, 45, кв.8

Яблоки

3

АО Уме-лец

Кг.

45

20

900

25

1125

500


Во многих источниках, описывающих проблемы работы с БД, такие аномалии получили название Аномалии обновления. При изменении состояния базы данных могут возникнуть следующие аномалии:

  •  аномалии вставки,
  •  аномалии обновления,
  •  аномалии удаления.

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

Аномалии обновления происходят при изменении данных в таблице. Например, если изменился адрес покупателя, то такое изменение необходимо выполнить одновременно во всех кортежах, где эта информация встречается. Для поддержания такой таблицы в целостном состоянии вводится специальная последовательность операций, которая называется триггер. При обновлении одной записи триггер корректно исправляет данные и в других местах. Однако при этом увеличивается объем программного кода и усложняется сопровождение БД.

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

Как видно, возникновение описанных аномалий свидетельствует, что созданная таблица не оптимальна и 1НФ, в которой она находится, недостаточна для корректной работы БД. Поэтому перейдем к рассмотрению 2НФ.

Вторая нормальная форма (2НФ)

Отношение находится в 2НФ только тогда, когда оно находится в 1НФ и нет неключевых атрибутов, которые зависят от части сложного ключа.

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

  •  (КодЗаказа, Дата, ИдКл, КодТовара) Кол-во
  •  (КодЗаказа, Дата, ИдКл, КодТовара) Сумма
  •  (КодЗаказа, Дата, ИдКл, КодТовара) СуммаНДС

Примечание. Знак означает функциональную зависимость.

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

  •  КодТовара  Товар
  •  КодТовара  ЕдИзм
  •  КодТовара  Цена
  •  КодТовара  Поставщик
  •  КодТовара  В наличии
  •  КодТовара  НДС

Аналогично, сведения о покупателе зависят от кода покупателя:

  •  ИдКл  Покупатель
  •  ИдКл  Фамилия
  •  ИдКл  Город
  •  ИдКл  Адрес

Следовательно, нашу сводную таблицу можно декомпозировать (разделить) на три меньших таблицы: Покупатели (данные о покупателях, табл.2), Товары (данные о товарах, табл.3) и Заказы (данные о заказах, табл.4).

Таблица 2

Покупатели

ИДКл

Покупатель

Фамилия

Адрес

Город

1

Атлант

Балаганов

ул. Садовая, 45, кв.8

Черноморск

2

Рога и копыта

Бендер

пр. Героев, 31, кв.5

Москва

3

Метеор

Щукина

Речной пер., 3, кв.17

Москва

Таблица 3

Товары

Код товара

Товар

Поставщик

Ед.изм.

Цена

НДС

В наличии

1

Бананы

АО Экзотика

кг.

50

25

200

2

Конфеты

ЗАО Рот Фронт

кг.

125

25

300

3

Яблоки

АО Умелец

кг.

45

25

500

Таблица 4

Заказы

КодЗаказа

Дата

ИдКл

КодТовара

Кол-во

Сумма

СуммаНДС

1

02.12.09

1

1

100

5000

5750

1

02.12.09

1

2

80

10000

11500

1

02.12.09

1

3

100

4500

5175

2

02.12.09

2

1

50

2500

3125

2

02.12.09

2

2

50

6250

7813

3

02.12.09

3

2

40

5000

6250

3

02.12.09

3

3

50

2250

2813

5

06.12.09

1

1

30

1500

1875

5

06.12.09

1

3

20

900

1125

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

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

На этом этапе нормализации потенциальные ключи во всех зависимостях, на которые раздробилось исходное отношение, являются простыми и соответствуют требованиям 1НФ, следовательно, полученные отношения уже находятся в 2НФ. При этом имевшиеся аномалии устранены. При переходе к 2НФ отношения разрабатываемой БД стали адекватными предметной области.

Возможны ситуации, когда в полученных таблицах останутся описанные выше аномалии, возникающие из-за взаимной зависимости неключевых атрибутов. Устранить аномалии позволяет Третья нормальная форма (ЗНФ)

Таблицу можно считать принадлежащей ЗНФ, если она находится в 2НФ и в ней отсутствуют взаимные зависимости между неключевыми атрибутами. В полученных таблицах таких зависимостей нет, поэтому полученная в нашем примере в 2НФ база данных находится и в 3НФ.

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

В реляционных моделях могут быть применены связи видов:

  •  один к одному,
  •  один ко многим,
  •  многие к одному,
  •  многие ко многим.

Связь «один к одному» подразумевает, что в любой момент времени каждой записи в Таблице 1 соответствует не более одной записи в Таблице 2, и наоборот – каждой записи в Таблице 2 соответствует не более одной записи в Таблице 1. Это наиболее простой вид связи между таблицами. Например, такая связь будет установлена между таблицами, когда описывается что каждый человек имеет только одно свидетельство о рождении или же каждый стол в отделе имеет только один инвентаризационный номер.

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

При связи между отношениями «многие к одному» каждой группе записей в Таблице 1 может соответствовать не более одной записи в Таблице 2, а любой записи Таблицы 2 могут соответствовать несколько записей Таблицы 1. Например, таким образом можно связать таблицы, в которых описывается ситуация, когда несколько студентов работают над одним общим проектом.

Связь «многие ко многим» представляет собой такое взаимодействие между таблицами, когда множеству записей в Таблице 1 может соответствовать множество записей в Таблице 2. Примером подобной связи может служить такое соответствие, когда у множества студентов читают лекции несколько преподавателей вуза. Заметим, что такой тип связи в реляционных БД напрямую не поддерживается, а выражается через более простые: «один ко многим» и «многие к одному».

Итак, наша учебная база данных состоит из следующих таблиц:

Таблица 5

Имя таблицы

Поля таблицы

Покупатели

ИДКл – идентификатор или код покупателя,

Покупатель – наименование организации-покупателя,

Фамилия – фамилия контактного лица,

Адрес – юридический адрес организации,

Город – местонахождение организации

Товары

КодТовара – идентификатор или код товара,

Товар – наименование товара,

Поставщик – наименование поставщика,

ЕдИзм – единица измерения количества товара,

Цена – цена единицы товара,

В наличии – количество товара на складе

Заказы

КодЗаказа – учетный номер заказа,

ИДКл – идентификатор или код покупателя,

КодТовара – идентификатор или код товара,

Дата – дата сделки,

НДС – налог на добавленную стоимость,

Кол-во – количество товара в заказе

В таблицу Заказы можно добавить поля Сумма и СуммаНДС, однако это не сделано с целью экономии памяти компьютера. Значения этих атрибутов будем вычислять при выполнении запросов к БД по формулам:

Сумма = Товары.Цена * Заказы.Кол-во

СуммаНДС = Товары.Цена * Заказы.Кол-во * ( 1 + НДС )

Как указывалось выше, каждая запись имеет ключ. Если при создании таблицы ключевое поле не указано, то Microsoft Access автоматически устанавливает в качестве ключевого первое поле таблицы, назначая ему тип данных – Счетчик. Данные в таком поле формирует сама СУБД по заданному закону: по умолчанию СУБД устанавливает режим присваивания записям таблицы последовательных значений.

В таблице 6 показана структура таблиц базы данных. Первичные ключи выделены жирным шрифтом, внешние ключи – жирным курсивом.

Таблица 6

Покупатели

Заказы

Товары

ИДКл

КодЗаказа

КодТовара

Покупатель

ИДКл

Товар

Фамилия

КодТовара

Поставщик

Адрес

Дата

Ед_изм

Город

НДС

Цена

Кол-во

В наличии

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

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

В приведенном примере таблицы нормализованы, поэтому можно изменять данные о заказах, исправив всего одну запись в таблицах Покупатели или Товары. Кроме того, используя нормализованные таблицы, можно составить новые, виртуальные таблицы, содержащие требуемую информацию, сделать удобным ввод данных в БД.

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

Таблица 7

Таблица Покупатели

Имя поля

Тип данных

Размер поля

Формат поля

Обязатель-ное поле

Индексированное поле

ИДКл

Счетчик

Длинное целое

Да (Совпадения не допускаются)

Покупатель

Текстовый

25

Да

Нет

Фамилия

Текстовый

25

Нет

Нет

Адрес

Текстовый

40

Да

Нет

Город

Текстовый

25

Да

Нет

Таблица 8

Таблица Товары

Имя поля

Тип данных

Размер поля

Формат поля

Обязатель-ное поле

Индексированное поле

КодТовара

Счетчик

Длинное целое

Да (Совпадения не допускаются)

Товар

Текстовый

25

Да

Нет

Поставщик

Текстовый

25

Нет

Нет

Ед_изм

Текстовый

10

Да

Нет

Цена

Денежный

Число десятичных знаков – 2

Денежный

Да

Нет

В наличии

Числовой

Длинное целое

Да

Нет

Таблица 9

Таблица Заказы

Имя поля

Тип данных

Размер поля

Формат поля

Обязательное поле

Индексиро-ванное поле

КодЗаказа

Счетчик

Длинное целое

Да

(Совпадения не допускаются)

ИДКл

Числовой

Длинное целое

Да

(Допускаются совпадения)

Нет

КодТовара

Числовой

Длинное целое

Да

(Допускаются совпадения)

Нет

Дата

Дата/время

Краткий формат даты

Да

Нет

НДС

Числовой

Одинар-ное с пла-вающей точкой

Процент-ный

Да

Нет

Кол-во

Числовой

Длинное целое

Да

Нет

Этой информацией будем пользоваться при создании таблиц базы данных.

3 Создание базы данных

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

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

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

Рисунок 1 Рабочее окно СУБД

СУБД MS Access 2007 предлагает два режима создания таблиц: режим таблицы и режим конструктора. Возможно использование шаблонов, предлагаемых СУБД. Наиболее целесообразен режим конструктора, который позволяет сразу создавать структуру таблицы, определять свойства полей и задавать ключевые атрибуты. Перейдите в режим конструктора таблиц: щелкните по кнопке Режим в левой части «ленты» и укажите в выпавшем меню строку Конструктор.

СУБД предложит ввести имя создаваемой таблицы для ее сохранения:

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

Рисунок 2 Окно СУБД в режиме конструктора таблиц

На рис.3 показан экран, соответствующий сконструированной таблице Покупатели.

Рисунок 3 Структура таблицы Покупатели (режим Конструктор)

Сохраните и закройте полученную таблицу. Для этого щелкните по значку в верхнем левом углу экрана, а затем по крестику в правом верхнем углу рабочего окна.

Аналогично создайте таблицы Товары и Заказы, структура которых определена в таблицах 8 и 9. Не забудьте сохранить созданные таблицы.

Далее необходимо установить связи между таблицами.

Внимание! Перед выполнением этой операции закройте все открытые таблицы.

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

Если диалоговое окно Добавление таблицы на экране не появилось, щелкните правой кнопкой мыши в рабочем окне и в появившемся меню выберите пункт Добавить.

Рисунок 4 Диалоговое окно Добавление таблицы

После появления на экране всех связываемых таблиц щелкните кнопку диалогового окна Закрыть. Результат показан на рис. 5.

Рисунок 5 Выбранные таблицы в окне Схема данных

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

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

Рисунок 6 Диалоговое окно Изменение связей

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

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

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

Рисунок 7 Окно Схема данных

4 Ввод данных в таблицы

Заполнить таблицу данными можно в режиме открытой таблицы. Это не всегда удобно, особенно для таблиц больших размеров. Лучше пользоваться для этой цели специально созданными формами.

Microsoft Access предлагает несколько способов создания форм.

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

Для большинства пользователей наиболее удобен Мастер форм.

Пометьте в области переходов таблицу Покупатели (Внимание! Таблица Покупатели должна быть закрыта), выберите на «ленте» закладку Создание, в предложенных инструментах раздела Формы щелкните по значку Другие формы , и в выпавшем списке выберите Мастер форм.

Рисунок 8 Панель Создание форм

В появившейся панели Создание форм (Рис.8) выберите все поля таблицы Покупатели (щелчком по кнопке перенесите имена всех полей в правое окно). После этого нажмите кнопку Далее. В последовательности появляющихся панелей выберите внешний вид формы – «В один столбец», затем – понравившийся вам стиль оформления формы. На последнем шаге (рис.9) СУБД предложит Вам задать имя формы и определить дальнейшие действия: либо открыть форму для ввода данных, либо изменить макет формы (в последнем случае форма выйдет на экран в режиме конструктора). Задайте имя формы – Покупатели Форма, затем щелкните кнопку Готово.

Рисунок 9 Панель Создание форм на последнем шаге

В рабочем окне появится форма, открытая для ввода данных в таблицу Покупатели (Рис.10).

Рисунок 10 Форма Покупатели, открытая для ввода данных в таблицу

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

Здесь сообщается, что в форме показана запись № 2, всего в таблице 3 записи.

С помощью открытой формы введите в таблицу Покупатели следующие данные. Учтите, что значения поля ИДКл вводятся автоматически:

ИДКл

Покупатель

Фамилия

Адрес

Город

1

Атлант

Балаганов

ул. Садовая, 45, кв.8

Черноморск

2

Рога и копыта

Бендер

пр. Героев, 31, кв.5

Москва

3

Метеор

Щукина

Речной пер., 3, кв.17

Москва

Аналогично форме Покупатели создайте форму Товары и так же введите в таблицу Товары следующие данные (значения поля Код товара вводятся автоматически):

Код товара

Товар

Поставщик

Ед.изм.

Цена

В наличии

1

Бананы

АО Экзотика

кг.

50

200

2

Конфеты

ЗАО Рот Фронт

кг.

125

300

3

Яблоки

АО Умелец

кг.

45

500

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

Выполните все описанные выше действия с мастером форм для формы Заказы, но на последней панели работы мастера пометьте «Изменить макет формы», затем щелкните кнопку Готово. На экране появится форма Заказы в режиме конструктора (Рис.11).

Рисунок 11 Окно формы Заказы в режиме конструктора

Порядок замены поля ввода ИДКл полем со списком:

  •  щелкните по полю ввода данных ИДКл, затем щелчком правой кнопки мыши вызовите контекстное меню, из которого выберите последовательно Преобразовать элемент, затем – Поле со списком;
  •  щелчком правой кнопки мыши снова вызовите контекстное меню, из него выберите Свойства;
  •  в появившемся окне установите следующие свойства поля:

на вкладке Данные свойства: Источник строк – Покупатели, Присоединенный столбец – 1, это столбец, данные из которого будут занесены в поле (первый столбец);

на вкладке Макет свойства: Число столбцов – количество столбцов таблицы, которое будет выведено в поле со списком (установите 2), Ширина списка – 10 см;

  •  закройте окно свойств.

Так же замените поле ввода Код товара полем со списком, учтите, что для него источник строк – таблица Товары.

После настройки свойств полей формы сохраните результат своей работы и закройте окно конструктора форм.

Для ввода данных в таблицу Заказы откройте форму двойным щелчком по ее имени в области переходов, ее вид в рабочем окне показан на рис. 12.

Рисунок 12 Форма для ввода данных в таблицу Заказы

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

Код заказа

ИДКл

Код товара

Дата

НДС

Кол-во

1

1

1

02.12.09

25

25

2

2

1

02.12.09

25

30

3

1

2

03.12.09

25

20

4

3

2

04.12.09

25

40

5

2

3

04.12.09

25

40

6

3

3

05.12.09

25

35

Продолжение таблицы с данными

Код заказа

ИДКл

Код товара

Дата

НДС

Кол-во

7

1

3

05.12.09

25

70

8

1

3

06.12.09

25

50

9

2

2

06.12.09

25

100

10

3

1

06.12.09

25

55

11

3

2

08.12.09

25

40

12

2

2

10.12.09

25

60

Поле Код заказа вводится автоматически, значения полей ИДКл и Код товара выбираем из списка, выпадающего при нажатии на стрелку в соответствующем поле ввода.

Закончив ввод данных, закройте соответствующую форму.

5 Создание запроса к БД

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

Дата

Покупатель

Товар

ЕдИзм

Цена

Кол-во

Сумма

СуммаНДС

Для этой цели создадим Запрос к базе данных – новую таблицу, в которой будут использованы данные таблиц Запросы, Покупатели и Товары.

  •  Выберите на «ленте» закладку Создание, в правой части ее щелкните кнопку Мастер запросов. Появится панель Новый запрос.
  •  В панели Новый запрос выберите Простой запрос и нажмите кнопку ОК.
  •  В открывшемся окне Создание простых запросов выберите из таблиц базы данных нужные поля кнопкой .

Рисунок 13 Панель мастера запросов (создание простых запросов)

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

Таблица

Поле

Заказы

Дата

Покупатели

Покупатель

Товары

Товар

Товары

Ед_изм

Товары

Цена

Заказы

Кол-во

Сумма

СуммаНДС

После выбора необходимых полей и нажмите кнопку Далее.

  •  В следующей панели Создание простых запросов оставьте помеченным переключатель подробный и нажмите кнопку Далее.
  •  В последнем окне Создание простых запросов пометьте Изменить макет запроса и нажмите кнопку Готово. 

На экране появится окно конструктора запросов (рис. 14).

Рисунок 14 Окно конструктора запросов

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

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

В таблицу-запрос добавьте вычисляемые поля Сумма и СуммаНДС, которые отсутствуют в структуре исходных таблиц. Для этого поместите курсор в пустое поле надстройки, для определения содержимого поля Сумма нажмите правую кнопку мыши, в выпавшем списке выберите строку Построить .

После этого на экране появится окно Построитель выражений (рис. 15)

Рисунок 15 Построитель выражений

В области Элементы выражений окна Построителя выражений находятся три поля.

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

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

Порядок создания выражения:

  •  в поле Элементы БД, функции и др. раскройте список таблиц и выберите нужную таблицу;
  •  в поле Категории элементов выберите нужное поле, дважды щелкните его мышкой, чтобы вставить его в Поле построителя выражений, или нажмите кнопку Вставить;
  •  вставьте необходимые операторы в выражение. Для этого поместите указатель мыши в определенную позицию Поля построителя выражений и выберите одну из кнопок операторов выражений.

Внимание! Если в строке Поле конструктора запросов указано имя вычисляемого поля, не содержащегося ни в одной из таблиц, то в построителе выражений имя этого поля отделите от соответствующего выражения двоеточием.

Выражение для вычисляемого поля Сумма должно иметь вид:

Сумма: [Заказы]![Кол-во]*[Товары]![Цена]

Выражение для вычисляемого поля СуммаНДС должно иметь вид:

СуммаНДС: [Заказы]![Кол-во]*[Товары]![Цена]*(1+[Заказы]![НДС])

Отредактируйте их, если они выглядят не так.

После создания выражения в поле построителя выражений щелкните кнопку ОК. Тем самым Вы вернетесь в окно конструктора запросов. В Надстройке появится созданное выражение.

Закончив построение всех выражений, сохраните результат работы и закройте окно конструктора.

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

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

Запустите запрос на выполнение, сохраните результат и закройте запрос.

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

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

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

Каждый перекрестный запрос должен включать следующие компоненты:

  •  заголовок строки,
  •  заголовок столбца,
  •  итоговое поле.

Возьмем за основу полученный нами выше простой запрос. Запустим его на выполнение.

Для получения перекрестного запроса будем использовать Мастер запросов.

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

Выберите на «ленте» закладку Создание, в ней – Мастер запросов. На появившейся панели Новый запрос укажите Перекрестный запрос и щелкните по кнопке ОК. Появится панель Создание перекрестных таблиц (Рис.16).

Рисунок 16 Панель мастера запросов (создание перекрестных запросов)

В появившейся панели пометьте кнопку Запросы, в верхнем ее окне пометьте простой запрос Заказы Запрос, из которого будем формировать перекрестный. После щелчка по кнопке Далее пользователю предлагается список полей исходного простого запроса, из которых нужно выбрать поле для заголовка строки. Выберите из списка Доступных полей поле Товар, щелчком по кнопке переместите его в Выбранные поля, затем щелкните по кнопке Далее.

Рисунок 17 Панель мастера запросов (создание перекрестных запросов)

На следующей панели предлагается выбрать заголовок столбца. Выберите поле Покупатель, щелкните Далее.

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

Внимание! Если Вам не нужны итоговые суммы по каждому товару, выключите флажок около слова ДА на панели.

Затем щелкните кнопку Далее.

Рисунок 18 Панель мастера запросов (задание вычислений в клетках запроса)

На последней панели пользователю предлагается задать имя запроса (пусть это будет Заказы Запрос_перекрестный) и либо просмотреть результаты запроса, либо изменить его структуру (в последнем случае на экране появляется наш запрос в режиме конструктора, что позволяет внести в него желаемые изменения). Оставьте просмотр результата и щелкните кнопку Готово.

На рис.19 показан результат выполнения перекрестного запроса.

Рисунок 19 Результат выполнения перекрестного запроса

6 Создание отчетов и диаграмм

Отчет представляет собой эффективный способ представления данных в печатном виде. Большая часть сведений в отчете поступает из базовой таблицы, запроса или инструкции SQL, являющейся источником данных для отчета. Остальные сведения отчета хранятся в его структуре.

СУБД MS Access 2007 предоставляет пользователю 3 способа создания отчетов: автоматический, с помощью мастера отчетов, с применением конструктора отчетов.

Самый простой способ – первый. СУБД пытается самостоятельно создать наиболее подходящий по ее мнению отчет. Набор действий – минимальный.

  1.  Выберите таблицу, например, Товары, щелкнув один раз на соответствующей ссылке в окне Область переходов.
  2.  Выберите закладку Создание, а в ней – Отчет.
  3.  Сохраните отчет, присвоив ему имя Товары_отчет_автомат.

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

Лучшие возможности предоставляет Мастер отчетов.

Выберите на «ленте» закладку Создание, а в ней – Мастер отчетов.

На появившейся панели (Рис.20) Вам будет предложено выбрать источники данных (таблицы или запросы) и из них – необходимые поля для создания отчета.

Рисунок 20 Начальная панель Мастера отчетов

Выберите поля в следующем порядке: из таблицы Товар – поле Товар, из таблицы Покупатели – поле Покупатель, из таблицы Заказы – поле Дата, из таблицы Заказы – поле Кол-во. Вид панели показан на рисунке 21. Затем щелкните по кнопке Далее.

Рисунок 21 Начальная панель Мастера отчетов с отобранными полями

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

Рисунок 22 Панель Мастера отчетов с макетом отчета

Следующая панель предлагает добавить уровни группировки, при этом меняется макет отчета. На рис.23 показано, как меняется макет отчета при добавлении группировки продаж по полю Покупатель (делать это не нужно!).

Рисунок 23 Макет отчета с добавленным уровнем группировки

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

Затем выберите вид макета для отчета и требуемый стиль. На последней панели задайте имя отчета Товары_отчет_мастер, просмотрите отчет, щелкнув кнопку Готово. На рис.24 показан примерный вид готового отчета.

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

Устранить эти недостатки можно с помощью конструктора отчетов.

Откройте отчет в режиме конструктора. На рис.25 показан макет отчета в режиме конструктора.

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

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

Рисунок 24 Отчет, полученный с помощью Мастера отчетов

Рисунок 25 Макет отчета в режиме конструктора

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

Для перемещения надписей в области Заголовок отчета и Нижний колонтитул нужно щелкнуть мышкой на нужном элементе. Найдите точку, где указатель мыши принимает вид крестообразной стрелки, удерживая кнопку мыши нажатой, переместите элемент в нужное место. Для изменения ширины или высоты поля отчета щелкните мышкой на нужном поле, айдите точку, где указатель мыши принимает вид двунаправленной стрелки, удерживая кнопку мыши нажатой, измените размер до нужной величины. Измененный макет отчета показан на рис. 26.

Рисунок 26 Измененный макет отчета в режиме конструктора

На рис. 27 представлен макета отчета после корректировки в режиме конструктора.

Рисунок 27 Отчет после корректировки

Теперь сформируем отчет по перекрестному запросу. Сначала создадим перекрестный запрос Объемы продаж по датам. Источником для него является, как и раньше, простой запрос Заказы Запрос. При создании запроса задайте заголовки строк – Дата, заголовки столбцов – Товар, поле Итоги – СуммаНДС, функция – Сумма, итоговое значение по строке - Да. Задайте имя запросу. Посмотрите созданный запрос (рис.28).

Рисунок 28 Результат выполнения запроса Объемы продаж по датам

  1.  На основе запроса Объемы продаж по датам создайте отчет. Воспользуйтесь для этой цели Мастером отчетов: в качестве источника данных используйте запрос Объемы продаж по датам, выберите все поля запроса, уровни группировки не добавляйте, порядок сортировки – по возрастанию даты, макет отчета – табличный, ориентация страницы – книжная. Сохраните отчет под именем Объемы продаж по датам (предлагается по умолчанию) и просмотрите его.

Рисунок 28 Результат работы мастера отчета Объемы продаж по датам

Очевидно, что отчет необходимо корректировать. Для этого откройте его в режиме конструктора.

Увеличьте ширину поля отчета Дата, уменьшите ширину полей Итоговое значениеСуммаНДС, Бананы, Яблоки и Конфеты, измените название поля Итоговое значение СуммаНДС на Итог Сумма с НДС, заголовки столбцов расположите по центру, значения в полях области данных отчета прижмите к правым границам полей, уменьшите ширину поля отчета Номер страницы и переместите его влево. Закройте конструктор и посмотрите результат (рис. 29).

Рисунок 29 Отчет Объемы продаж по датам после корректировки

Подсчитайте теперь в отчете суммы по столбцам. Для этого откройте отчет в режиме конструктора. Щелчком мыши выделите в области данных макета поле Итоговое значение СуммаНДС. В группе инструментов Группировка и итоги щелкните по значку Итоги, в выпавшем меню – Сумма. В области Примечание отчета появится поле подсчета суммы по столбцу. Проделайте такие же действия с полями макета Бананы, Конфеты и Яблоки. Щелкните по кнопке Элемент управления  (подпись), в области Примечание отчета нарисуйте прямоугольник, в нем напишите слово «Итого». Новая конструкция отчета показана на рис. 30, результат выполнения отчета – на рис. 31.

Рисунок 30 Отчет Объемы продаж по датам с итоговыми полями

Рисунок 31 Отчет Объемы продаж по датам с итоговыми суммами

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

Просмотрите отчет (рис.32).

Рисунок 32 Отчет Объемы продаж по датам с нарастающим итогом

7 Подчиненные формы и отчеты

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

Изменим структуру нашей базы данных. Имеющуюся таблицу Заказы заменим таблицами Заказы и ЗаказыТовары со следующими структурами:

Имя таблицы

Поля таблицы

Заказы

КодЗаказа – учетный номер заказа,

ИДКл – идентификатор или код покупателя,

Дата – дата сделки,

ЗаказыТовары

КодЗаказа – учетный номер заказа,

КодТовара – идентификатор или код товара,

Кол-во – количество товара в заказе

Все параметры полей оставим прежними (таблица 9), поле НДС для упрощения не используем. После создания таблиц изменим связи между таблицами, получим новую структуру базы данных (рис.33).

Рисунок 33 Новая структура базы данных в окне Схема данных

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

Выберите в форму все поля таблицы Заказы, внешний вид – в один столбец, требуемый стиль – Стандартная, имя формы Заказ, дальнейшие действия – Изменить макет формы, в результате переходим в режим конструктора.

На макете формы расширьте Область данных, переместив вниз полоску Примечание формы.

Внимание! На вкладке ленты Конструктор должна быть нажата кнопка группы Элементы управления Использовать мастера .

Щелкните в группе Элементы управления по кнопке Подчиненная форма/отчет , а затем установите крестик со значком подчиненной формы в Области данных конструктора и щелкните один раз. На макете появится пустой прямоугольник с надписью Свободный, и активируется Мастер подчиненных форм. Щелкните кнопку Далее, в появившемся окне укажите в качестве источника данных таблицу Заказы и выберите из нее поля КодТовара и Кол-во. После щелчка по кнопке Далее появляется диалоговое окно, в котором устанавливается связь между главной и подчиненной формами. СУБД по умолчанию предлагает для связи поле КодЗаказа. Согласившись с предложением, щелкните по кнопке Далее, задайте имя подчиненной формы СписокТоваров и щелкните кнопку Готово.

Теперь нужно обеспечить удобство ввода данных в форму.

Откройте форму Заказ в режиме конструктора. Преобразуйте поле ввода ИдКл в поле со списком, установите следующие свойства поля ввода:

на вкладке Данные свойства: Источник строк – Покупатели, Присоединенный столбец – 1, это столбец, данные из которого будут занесены в первый столбец таблицы;

на вкладке Макет свойства: Число столбцов – количество столбцов таблицы, которое будет выведено в поле со списком (установите 2), Ширина списка – 0 см; 5 см. Последнее действие приведет к тому, что пользователь видит в выпадающем списке перечень покупателей, а в таблицу Заказы будут вноситься коды покупателей.

Аналогичные действия произведите с полем ввода КодТовара.

Замените названия полей ввода: КодЗаказа – на Номер заказа, ИдКл – на Покупатель, Дата – на Дата продажи.

Сохраните результат работы. Посмотрите его в режиме макета (выбор режима на ленте слева. Обратите внимание: подчиненная форма по умолчанию выводится в табличном формате.

Рисунок 34 Макет формы с подчиненной формой

При вводе данных сначала в главную форму вводятся реквизиты заказа (Номер заказа, Покупатель, Дата продажи), а затем – в подчиненную форму перечень входящих в заказ товаров и их количества.

На основании введенных данных можем сформировать заполненный бланк заказа (в терминах СУБД – отчет). Для упрощения этой работы сначала создадим запрос к базе данных по конкретному заказу.

Запустите Мастер запросов, выберите Простой запрос.

Выберите поля для запроса, манипулируя окнами ввода Таблицы и запросы и Доступные поля:

Таблица

Поле

Заказы

КодЗаказа

Покупатели

Покупатель

Заказы

Дата

Товары

Товар

Товары

Ед_изм

Товары

Цена

ЗаказыТовары

Кол-во

Далее согласитесь с выводом подробного отчета, задайте запросу имя Бланк заказа, укажите дальнейшее действие Изменить макет запроса и щелкните кнопку Готово.

Добавьте в список полей вычисляемое поле

Сумма: [Заказы]![Кол-во]*[Товары]![Цена]

С описанием поля КодЗаказа выполните следующие действия.

В строке Поле укажите имя поля КодЗаказа, его значение нужно будет ввести для выполнения запроса. В строке Условие отбора наберите текстовое выражение [Введите номер заказа]. При запуске запроса на выполнение появится диалоговое окно (рис. 35), в которое нужно ввести номер заказа.

Рисунок 35 Диалоговое окно для ввода номера заказа

Теперь приступим к формированию отчета с помощью мастера отчетов. В качестве источника данных выберите запрос БланкЗаказа, перенесите все доступные поля в окно Выбранные поля, добавьте уровни группировки Код заказа, Покупатель, Дата. Щелкнув по кнопке Группировка, задайте интервал группировки Обычный для поля Дата.

Далее укажите сортировку по возрастанию для поля Товар, пометьте вид макета – Структура, требуемый стиль – Нет, имя отчета – Бланк заказа и просмотрите отчет, щелкнув кнопку Готово.

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

Подсчитайте итоговое значение суммы заказа. Щелкните по полю Сумма в области данных, правой кнопкой мыши вызовите контекстное меню, выберите в нем пункты Итог – Сумма. В примечании отчета слева от поля =Sum([Сумма]) вставьте надпись Итого по заказу. Сохраните результат и выполните отчет. Сформированный и скорректированный отчет показан на рис.36.

Рисунок 36 Сформированный отчет


 

А также другие работы, которые могут Вас заинтересовать

37071. Гимн профессии! Классный час 57.5 KB
  Маяковский 1й слайд из презентации Л. Слово Бухр возникло в Германии 2й слайд Основоположник бухучета Лука Пачоли Корейко – УБРАТЬ 3й слайд Л. 4й слайд Бухгалтерия – это наука дающая мир душе сохраняющая богатства и продвигающая к счастью Пьер Пурр – французский бухгалтер 1676 г. 5й слайд Вы бывали в бухгалтерии.
37072. Знакомство с детьми. Классный час 13.96 KB
  1 урок Например 1 апреля День смеха День дурака 1 апреля с давних времен считается праздником розыгрышей. По традиции этот день называют Днем дурака ведь в дурацкой ситуации может оказаться в этот день любой из нас.д 2 апреля – День детской книги.
37073. Безопасность дорожного движения 76.5 KB
  Конкурсы викторины праздники по ПДД для школьников 2008 г. Праздник в школе: сценарии конкурсы викторины 2009 г. 1 конкурс Вопросы Вы видите 3 квадрата в которых находится буквы обозначающая область знаний из которой будут заданы вопросы командам. Подсчитывание баллов за 1 конкурс.
37075. ценарий проведения праздника Последний звонок в 11-м классе 50.5 KB
  По старой доброй традиции вот уже многие годы звуки “Школьного вальса†звучат на праздниках подобных нашему во всех школах нашей большой страны волнуя души и сердца учителей родителей и друзей тех кто собирается покидать стены родной школы. Будем надеяться что наша школа станет для сегодняшних одиннадцатиклассников своеобразным символом нежности и чистоты детства. Любовные Истории Школа Школьный двор и смех подружек Самый чистый самый звонкий И бегут по теплым лужам Босоногие девчонки И уже других качают Наши школьные качели...
37076. ПОЗНАЙ СЕБЯ (классный час для учащихся начальных классов) 47.15 KB
  На начальных этапах развития ребенок оценивает преимущественно свои физические качества: Я большой Я сильный. Таким образом начинают осознаваться и оцениваться практические умения поступки моральные качества. Задачи: обучать учащихся умению увидеть и оценить свои возможности и способности; обучать умению объективно оценивать себя и своих друзей: свои личностные качества особенности поступков и действий их мотивы и цели. ЕСТЬ – это те умения навыки качества которыми вы располагаете уже сегодня.
37077. Права на уединенность, на выражение собственного мнения, на свободу в принятии решений 41.5 KB
  Конвенция ООН о правах ребенка статьи 1216 закон Республики Беларусь О правах ребенка статья 10. Получивший одобрение перечень зачитывается а затем соотносится с текстом закона Республики Беларусь О правах ребенка статья 10 и Конвенции ООН о правах ребенка статьи 12 16. Надо ли вмешиваться в переписку ребенка Чем нарушается право на уединенность в нашей стране Плохими жилищными условиями правовой неграмотностью родителей. Конвенция ООН о правах ребенка статьи 12 15 Статья 12 1.
37078. Учение о социальном факте и структурный функционализм Э. Дюркгейма 16.3 KB
  В методологии Э. Дюркгейма социальный факт одновременно выступает как важный компонент его концепции структурного функционализма. Социологическое объяснение фактов, реальностей и явлений, исследуемых отдельно друг от друга, должно происходить в терминах социальных причин и социальных функций...