Разработка базы данных с помощью MS Access

Разработка базы данных с помощью MS Access

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

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

Агентство организует индивидуальные и групповые туры в различные страны. Договор включает название компании-клиента, данные о контактном лице, описание предмета договора (страна, число, туристов, тур), дату начала исполнения договора, дату окончания исполнения, дату оплаты.

В функции БД входит, например, получение информации по следующим пунктам:

1. Клиенты:

   • о клиентах агентства для реализации контактной деятельности;

   • о постоянных клиентах агентства;

   • о клиентах, дающих наибольший доход.

2. Договор:

   • о платежах по договору;

   • о турах, пользующихся наибольший доход.

3. Контроль исполнения:

   • объем работ, выполненный каждым из сотрудников;

   • договоры, срок действия которых заканчивается в текущем месяце.

4. Бизнес-анализ:

   • список всех туров, сгруппированный по странам (регионам);

   • индивидуальные туры;

   • групповые туры;

   • число туров в каждый регион;

   • финансовый отчет и др.

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

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

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

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

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

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

Задания для выполнения:

Создание таблиц БД

1. Запустите MS Access. Нажмите по команде Новая база данных, затем по папке (в правой части), находим свой диск, пишем имя БД Туризм, и Создать.

1. Создайте таблицу Сотрудники в режиме Конструктора, Главная

–вид - конструктор. Имя таблицы Сотрудники. Имя поля и Тип данных

представлены в приведенной таблице.

и

Заполняем Свойства поля: для Даты найма и Даты рождения установить маску ввода с помощью кнопки Построить Мастера масок краткий формат даты; для поля Домашний телефон задайте маску, набрав, следующий шаблон (999)999-99-99; для поля Размер оклада задайте условие, что он больше 5000 р., но не больше 10000, для этого всвойстве поля Условие на значение установите (=5000) And ( Определите нужный формат поля. Предусмотрите выдачу сообщения при ошибке ввода данных.

1. Создайте первичный ключ для поля Код сотрудника (установлен по умолчанию), Конструктор - ключевое поле.

2. Посмотрите полученную таблицу, Главная – вид – режим таблицы.

3. В режиме таблицы введите 10 разнообразных записей по своему усмотрению, но должности выберите следующие: менеджер, специалист, ведущий специалист, директор, несколько туроператоров. Сохранить.

4. Создайте с помощью Мастера таблиц новую таблицу Клиенты, Создание – таблица. Изменяем название полей, тип данных, Режим таблицы – тип данных.

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

1. Перейдите в режим таблицы и заполните ее произвольными данными на 10 клиентов, не повторяя кодов. Для поля название клиента, используем данные “групповой” и “корпоративный”.

2. Составляем третью таблицу Страны.

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

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

Лабораторная работа 2

Сущности вступают во взаимоотношения, называемы связями. Наиболее распространены связи «многие-ко-многим» и «один-ко-многим». В данном примере сущности Клиент, Страна и Сотрудник связаны с сущностью Договора связями «один-ко-многим»: один клиент может заключить несколько договоров, один сотрудник выполняет работу по нескольким договорам, заказ на туры в определенную страну встречается в нескольких договорах.

Фильтры предназначены для отбора определенных записей базы данных. Создание фильтра – простейший способ отбора части записей в таблице.

Задания для выполнения:

Связи между таблицами

1. Откройте БД Туризм, содержащую три таблицы: Клиенты, Сотрудники и Страны.

2. Создайте в режиме Конструктора таблицу Договора, используя

Столбец подстановок, которая должна иметь поля:

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

1. В режиме Конструктора в Свойствах поля устанавливаем Маску ввода для Типа данных Дата/время. Создаем первичный ключ в таблице Договора. Перейти в режим таблицы, вносим свои записи (около 10).

2. Создаем схему данных. Работа с базами данных – схема данных. Добавляем таблицы (4 шт.), убираем ненужные связи. Из таблицы Клиенты (главная таблица) берем мышкой поле Код клиента и накладываем на поле Код клиента в таблице Договора (подчиненная таблица). Затем появляется окно, определяем обеспечение целостности данных, и каскадное обновление связанных полей (ставим галочки), тип отношений один-ко-многим.

1. Связываем таблицу Страны (главная) с таблицей Договора (подчиненная) по коду тура; связываем таблицу Сотрудники с таблицей Договора по коду сотрудника. Тип отношений один-ко-многим. Сохраняем.

Простейшие операции поиска и фильтрации данных

1. Откройте БД Туризм.

2. Откройте таблицу Сотрудники.

1. С помощью Главная – Найти осуществляем следующие операции поиска:

   • найдите все записи о служащих в должности «Менеджер» (или в любой другой должности);

   • подберите все записи о служащих, которые живут в Москве (или в другом городе).

2. Используя команду Главная – Заменить, замените все должности «Менеджер» на «Специалист по работе с клиентами».

3. Отсортируйте фамилии сотрудников по алфавиту. Для этого установите курсор на поле ФИО и выберете Главная – сортировка и фильтр – опция По возрастанию или По убыванию.

4. Отсортируйте записи по должностям, а для одинаковых должностей – по фамилиям. Для этого расположите поле Должность слева от поля ФИО, выделите оба поля и выполните сортировку.

5. Примените расширенный фильтр. Главная – Дополнительно – Расширенный фильтр:

   • определить размер оклада выше 5500 р., затем Применить фильтр;

   • определить сотрудников у кого размер оклада выше 6000 р., но ниже 8000 р.

Лабораторная работа 3

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

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

Использование запросов представляется неотъемлемой составляющей работы любого разработчика СУБД.

Задания для выполнения: Создание запросов

1. Откройте БД Туризм и перейдите на вкладку Создание, нажмите

Конструктор запросов, определив нужные таблицы создайте:

• запрос всех путешествий в определенную страну (выбираем нужные поля из таблиц, в условии отбора пишем нужную страну), переходим в Конструктор – выполнить, сохраните запрос под именем

«Страна-выборка».

• запрос сотрудников, работающих с 2000 года и раньше (в поле пишем, используя построитель: год принятия на работу: Year([Сотрудники]![Дата найма]), в условии отбора =2000, Выполнить, сохранить под именем «Почетные»;

• запрос на нахождение тура в конкретную страну, оформленного заданным сотрудником (например, «Какие туры на посещение Турции заключил Петров?»). Сохранить.

Запросы с вычисляемыми полями.

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

Для поля Стаж нужно использовать формулу, построенную с помощью Конструктор – Построитель, где учитывается текущий год и Дата найма на работу: Стаж: Year(Date())-Year([Сотрудники]![Дата найма]);

Для поля Надбавка нужно исходить из того, что она составляет 10% от Размера оклада, если Стаж меньше 5 лет, и 20% - если Стаж больше 5 лет:

Надбавка: IIf([Стаж] оклада];0,2*[Сотрудни- ки]![Размер оклада]);

Поле Налог рассчитывается как 13% от Размера оклада: Налог:

[Сотрудники]![Размер оклада]*0,13;

Поле На руки рассчитывается: На руки: [Сотрудники]![Размер оклада]+[Надбавка]-[Налог].

1. В результате выполнения запроса будет получена ведомость заработной платы:

Формируем параметрические запросы.

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

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

Итоговые запросы.

1. Создайте запрос, используя подходящие функции, найдите наибольший (max) и средний размеры цены тура (avg). Добавить подходящие поля в запрос, затем Конструктор – Перекрестный, добавляем нужную функцию в строке Групповая операция, затем нажимаем Выборка, Выполнить, оценим результат.

2. Создайте запрос для подсчета объема продаж в конкретную страну. Для этого:

   • в Конструкторе запросов добавляем таблицы Договора и Страны, добавляем поля Страну, и расчетное поле Цена тура*Число туристов, которому присвоим название Стоимость

   • используем Групповая операция и для поля Стоимость путевок устанавливаем функцию SUM;

   • Выполнить запрос, оценить результат. Перекрестные запросы

3. Составить запрос для выяснения: сколько туров организовано в каждую страну. Конструктор – Перекрестный. Добавим четыре поля (будет достаточно, но можно и больше), для каждого поля запроса может быть выбрана одна из установок в строке Перекрестная таблица:

«Заголовки строк», «Заголовки столбцов», «Значение», которое выводится в ячейках таблицы, и не отображается. Для «Значение» в строке Групповая операция выбрать Count. Выполнить.

Составьте перекрестный запрос по теме: сколько туров и в какую страну произошло с мая по июнь 2012 г. В Построителе выражений используем Операторы – Сравнения Between 01.05.2012 And 31.07.2012. Сохранить. Получилась ведомость:

Лабораторная работа 4

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

Задания для выполнения:

Создание Автоформ

Работа с формами

1. Откройте БД Туризм. Выберите в объектах Таблицы таблицу Клиенты. Создайте для нее Автоформу. Создание – Раздельная форма. Измените Автоформат. Добавьте две новых записи, используя кнопку со звездочкой:

Сохранить.

Создание формы с помощью Мастера

1. Создайте с помощью Мастера форм, Создание – Другие формы

–Мастер форм, форму Сотрудники для одноименной таблицы:

• включите в нее поля исходной таблицы.

• внешний вид формы – в один столбец;

• стиль – на свое усмотрение, готово;

• перейдите в режим Конструктора. Вставьте новый заголовок

«Сотрудники фирмы», с помощью Элементов управления – Надпись. Отформатируйте заголовок с помощью панели Шрифт.

• Сохраните форму.

Создание формы с помощью Конструктора форм

1. Создайте форму для таблицы Договора в режиме Конструктор форм. Для этого:

• открываем таблицу Договора, нажимаем Создание – Конструктор форм;

• перетаскиваем в форму все поля, располагаем поля в два столбика

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

  • отформатируйте поля, выделить нужные поля, Упорядочить – В столбик, Упорядочить – Внутренние поля элемента управления – Широкое, должно остаться два столбца полей:

1. Задайте всплывающую подсказку «Номер договора не должен повторяться» для поля Номер договора, Конструктор – Страница свойств – Другие – Всплывающая подсказка.

2. Добавьте любую картинку в заголовок форм, Эмблема.

3. Добавьте кнопки, Конструктор – Элементы управления – Кнопка, для перехода к следующей записи, предыдущей записи. Сохранить.

Создание подчиненных форм

Перед созданием подчиненных форм следует проверить наличие связи

«один-ко-многим» между таблицами.

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

2. Постройте подчиненную форму для таблицы Клиенты и

Договоры.

Элементы управления

1. Все изменения, дополнения, редактирование производятся в режиме Конструктора.

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

1. В этой же форме Сотрудники создайте Поле со списком для поля

Размер оклада. Перейдите в режим Форма, оцените результат.

1. В этой же форме Сотрудники создайте Группу переключателей – в Элементах управления. Группу переключателей поместите в Область данных, в появившемся окне, перечислите: утренний рейс, дневной рейс, вечерний рейс, ночной рейс, далее на ваше усмотрение, далее Подпись для Группы «Рейс», готово.