Общие вопосы разработки баз данных Access

И.Б. Кушнир

асс. кафедры «Информатика»

Е.А. Ревякина

Рецензент:

к.т.н., доцент кафедры «Информатика»

И.В. Барилов

Кушнир И.Б., Ревякина Е.А. Проектирование информационных технологий управления в организации: Пособие / И.Б. Кушнир, Е.А Ревякина. – Шахты: Изд-во ЮРГУЭС, 2004. – 16с.

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

Пособие предназначено для студентов дневной и заочной форм обучения по специальности 081100 «Менеджмент организации» и направления 521500 «Менеджмент»

УДК 004.6 (07)

ББК 32.973 – 081.2 я7

О 641

ОГЛАВЛЕНИЕ

Введение. 4

1 Общие вопосы разработки баз данных Access. 5

2 Создание и корректировка базы данных в системе управления базой данных 21

Библиографический список. 40

Приложение 1 «Синтаксис масок ввода». 41

Приложение 2 «Типы связей». 43

Введение

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

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

Access представляет собой функционально полную реляционную СУБД и имеет все необходимые средства для выполнения трех комплексных типов функций:

1) определение данных (задание структуры базы данных и их описание);

2) обработка данных;

3) управление данными (можно указать кому данные доступны их корректность, можно выделить правила коллективного пользования и др.).

Кроме того, Access – это не только классическая СУБД, но и система для разработки приложений. Можно разрабатывать приложения для работы с базой данных, не зная язык программирования.

Вот почему в качестве в качестве такого учебного объекты мы выбираем СУБД Microsoft Access, входящую в пакет Microsoft Office наряду с Microsoft Word и Microsoft Excel.

Общие вопосы разработки баз данных Access

Цель: знакомство-с рабочей средой и изучение элементов Access; изучение общих вопросов разработки баз данных на примере учебной базы Борей.

Краткие сведения

Рабочая среда

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

Рисунок 1 – Интерфейс в режиме конструктора форм

Окно базы данных

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

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

Форма является удобным средством для ввода и поиска информации в таблицах.

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

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

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

Модуль это программы на языке Visual Basic, с помощью которых определенный процесс разбивается на несколько небольших процедур.

Страницы доступа к данным это файлы HTML и вспомогательные файлы для доступа к данным из MS Internet Explorer к данным базы данных.

Концепция взаимосвязи объектов приведена на рисунке 2.

Рисунок 2 – Взаимосвязи основных объектов в Microsoft Access

Задание для самостоятельной работы

1. Загрузить Access

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

C:\Programs Files\Microsoft Office\Office\Samples\Борей.mdb

3. Изучить основные элементы интегрированной среды:

· система меню;

· панели инструментов;

· окно базы данных;

· окно конструктора объектов базы данных;

· окно свойств;

· окно редактора кода.

4. Знакомство с объектами базы данных:

Таблицы;

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

Рисунок 3

– Окно таблицы Поставщики в режиме «таблица»

Обозначения:

1. Поля

2. Поле кода

3. Записи

4. Текущая запись

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

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

7. Первая запись.

8. Предыдущая запись.

9. Выбранная запись.

10. Следующая запись.

11. Последняя запись.

12. Новая запись.

Далее выполните следующую последовательность действий.

В поле кода щелкните на сером квадратике слева от номера 5. Теперь запись с номером 5 выбрана.

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

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

Нажмите кнопку новая запись. Новая запись будет выбрана и готова к вводу данных.

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

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

Рисунок 4 – Таблица Поставщики в режиме конструктора

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

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

Нажмите кнопку закрытия таблицы. Таблица Поставщики закрыта.

Самостоятельно откройте и просмотрите таблицы Клиенты, Заказы, Заказано.

Запросы

В этом упражнении в окне базы данных перейдите на вкладку Запросы и дважды щелкните на имени запроса Товары по типам.

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

На панели инструментов нажмите кнопку Вид, после чего установится режим конструктора, приведенный на рисунке 5.

Рисунок 5 – Запрос Товары по типам в режиме конструктора

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

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

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

Нажмите кнопку закрытия окна запроса.

Самостоятельно откройте и просмотрите запросы Промежуточная сумма заказа, Продажи по типам, Продажи по сотрудникам и странам.

Формы

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

В окне базы данных откройте вкладку Формы и дважды щелкните на имени формы Поставщики. Форма Поставщики откроется в режиме формы, и первое поле будет выбрано, как показано на рисунке 6.

Рисунок 6 – Окно формы Поставщики

Нажмите клавишу ТАВ. Теперь будет выбрано следующее поле формы. Чтобы вернуться к предыдущему полю, нажмите клавиши SHIFT+TAB. Обратите внимание, что область управления формой соответствует области управления таблицей.

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

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

Рисунок 7 – Окно формы в режиме конструктора

На панели инструментов нажмите кнопку Вид , после чего установится режим формы.

Выберите в меню Окно команду По размеру формы. Окно изменится в соответствии с размером формы.

Нажмите кнопку закрытия окна формы.

Самостоятельно откройте и просмотрите формы Сотрудники, Типы, Заказы.

Отчеты

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

Переместите указатель мыши на отчет – указатель приобретет вид увеличительного стекла. Щелкните указателем в виде увеличительного стекла в поле отчета. Масштаб изменится, и вы увидите всю печатную страницу.

На панели инструментов нажмите кнопку Несколько страниц и выделите шесть страниц. На экране появятся шесть страниц, как показано на рисунке 8.

Рисунок 8 – Окно отчета Каталог

Щелкните на одной из страниц. Масштаб снова изменится: в увеличенном виде отобразится то место, на котором вы щелкнули.

Нажмите кнопку закрытия окна, чтобы закрыть отчет.

Страницы

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

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

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

Макросы

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

Некоторые задачи нельзя решить путем создания макросов – в таком случае необходимо программировать задачи на языке Visual Basic for Application (модули).

Модули

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

5. Разработка структуры базы данных на примере учебной базы «Борей»

· определение цели создания базы данных;

· определение таблиц, которые должна содержать база данных;

· конкретизация полей таблиц;

· определение связей между таблицами;

· просмотр связей в окне «Схема данных»

6. Разработать структуру базы данных по индивидуальному заданию.

Задание к работе:

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

· цель и словесное описание информационных объектов предметной области.

· инфологическую модель

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

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

Варианты заданий:

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

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

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

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

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

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

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

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

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

10. Предметная область - отдел сбыта предприятия. Решаемые задачи - учет товаров (название, цена и др.), их реализации (дата отгрузки, кому, сумма) и оплаты (дата оплаты, от кого, сумма); используется справочник клиентов фирмы. Вам потребуются таблицы с информацией о сотрудниках предприятия, товарах, клиентах, отгрузке и оплате.

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

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

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

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

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

Пример выполнения задания

Задание

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

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

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

1) ТИП;

2) ТОВАР;

3) ПОСТАВЩИК;

4) КЛИЕНТ;

5) ПРОДАВЕЦ;

6) ЗАКАЗ;

7) ЗАКАЗАНО;

8) ДОСТАВКА

Инфологическая модель представлена на рисунке 9.

Рисунок 9– Инфологическая модель предметной области

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

Прежде всего, в рассматриваемом примере проекта существует сущность “Клиент”. Если клиент производит заказ на покупку партии товаров впервые, осуществляется первичная регистрация его данных и сведений о сделанном заказе, следовательно, необходимо ввести еще одну сущность “Заказ”. Если же клиент производит заказ повторно, осуществляется регистрация только данного заказа. Вне зависимости от того сколько раз данный клиент производил заказы, он имеет уникальный идентификационный номер (уникальный ключ клиента). Информация о каждом клиенте включает: наименование клиента, адрес, телефон и др. Таким образом, атрибутами сущности КЛИЕНТ являются “УНИКАЛЬНЫЙ КЛЮЧ КЛИЕНТА”, “НАИМЕНОВАНИЕ КЛИЕНТА”, “АДРЕС КЛИЕНТА” и т.д. Теперь рассмотрим атрибуты сущности “Заказ”, содержащая сведения о дате заказа, клиенте, продавце, способе доставки заказа и т.д. Каждому заказу присваивается уникальный код заказа. Анализируя взаимосвязь между сущностями КЛИЕНТ и ЗАКАЗ получим, что один клиент в разное время может производить несколько заказов. С другой стороны, один заказ принадлежит только одному клиенту и поэтому между сущностями КЛИЕНТ и ЗАКАЗ установлена взаимосвязь “один ко многим”.

Теперь нам необходимо определить как в нашей системе будут представлены продавцы. Естественно предположить ввести для этого сущность “Продавец”, каждый экземпляр которой будет соответствовать конкретному сотруднику фирмы. Каждому сотруднику присваивается уникальный номер, который будет однозначно его идентифицировать и являться ключевым атрибутом. Кроме того, в сущности Продавец должны присутствовать дополнительные атрибуты, которые требуются для решения поставленных задач: Фамилия, Имя, должность и т.д. Между сущностями “Заказ” и “Сотрудники” установлена связь один-ко-многим (1:М), так как за один рабочий день один и тот же сотрудник может оформить множество заказов.

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

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

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

Мы можем получить нормализованные таблицы на основе ER – диаграммы предметной области: Каждая сущность и каждая связь выделяется в отдельную таблицу. Таблица, отражающая сущность, хранит экземпляры сущности, т.е. записи. Свойства сущности, называемые атрибутами, хранятся в полях (столбцах) сущность - таблицы. Таблица, отражающая связь, хранит связи между конкретными экземплярами сущностей.

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

Однако для разрешения связей типа «многие-ко-многим» рекомендуется применять теорию нормализации.

Рисунок 10 – Логическая модель

Подготовить содержимое каждой таблицы без указания ее структуры, например, содержимое таблицы Товары будет выглядеть так, как показано в таблице 1, а таблицы Типы – в таблице 2

Таблица 1

Товары

Код Товара	Марка	Код Поставщика	Единица Измерения	Цена
	Genen Shouyu		24 бутылок по 250 мл	697,50р.
	Pavlova		32 коробок по 500 г	785,25р.
	Alice Mutton		20 банок по 1 кг	1 755,00р.
	Carnarvon Tigers		16 кг упаковка	2 812,50р.
	Teatime Chocolate Biscuits		10 коробок по 12 шт.	414,00р.

Таблица 2

Поставщики

КодПоставщика	Название	Телефон
	ООО Экзотика	(095) 325-2222
	New Orleans Cajun Delights	(100) 555-4822
	Grandma Kelly's Homestead	(313) 555-5735
	Tokyo Traders	(03) 3555-5011
	Cooperativa de Quesos 'Las Cabras'	(98) 598 76 54