Реализация базы данных в среде СУБД Access 1 страница

Создание структуры таблиц

На втором этапе решения задачи построенная ИЛМ должна быть отображена в логическую структуру БД. При создании базы средствами СУБД с реляционной моделью, к которой и относится Access 97, каноническая ИЛМ предметной области не требует преобразований. Каждый ИО канонической модели отображается соответствующей реляционной таблицей. Реализуем последовательность операций по созданию структуры таблиц и схемы данных:

создать БД Сервис–центр, войдя в меню Файл→Создать базу данных;

в соответствии с бланками этапов 1 и 4 данной задачи создать структуру таблиц Услуги, Клиенты, Заказы БД Сервис–центрв режиме Конструктор. Для этого необходимо щелкнуть по кнопке Создать и в диалоговом окне Новая таблица выбрать опцию Конструктор. Чтобы выделить ключевой реквизит, надо активизировать его, а затем щелкнуть по кнопке Ключевое поле на панели инструментов. После закрытия окна таблицы СУБД предлагает ввести имя созданной таблицы. Вкладка Таблицы рабочего окна Access и окна всех таблиц БД Сервис–центр в режиме Конструктор приведены на рис. 1.3–1.6;

Рис. 1.3. Рабочее окно БД Сервис–центр

Рис. 1.4. Таблица Услуги в режиме Конструктор

Рис. 1.5. Таблица Клиенты в режиме Конструктор

Рис. 1.6. Таблица Заказы в режиме Конструктор

создать схему данных, войдя в меню Сервис→Схема данных:

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

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

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

Рис. 1.7. Окно Схема данных с установленными связями
между таблицами

Ввод и редактирование данных с помощью форм

Для справочников услуг и клиентов приемлемым решением будет создание автоформ вида Ленточная. Для оперативных данных по заказанным услугам реализуем столбцовую автоформу. Рассмотрим детально алгоритм реализации данных форм в Access:

перейти на вкладку Формы;

создать форму для таблицы Услуги:

а) щелкнуть левой кнопкой мыши по кнопке Создать;

б) в появившемся диалоговом окне Новая форма выбрать вид Автоформа: ленточная и источник данных – таблица Услуги, а затем закрыть окно (рис. 1.8);

в) закрыть новую форму после ее демонстрации на экране;

г) сохранить форму под именем Услуги;

аналогично создать форму Клиенты вида Автоформа: ленточная для таблицы Клиенты;

аналогично создать форму Заказы вида Автоформа: в столбец для таблицы Заказы;

Рис. 1.8. Диалоговое окно Новая форма

выполнить настройку формы Заказы:

а) активизировать форму Заказы и войти в режим Конструктор;

б) в форме Заказы, полученной автоматически средствами Access, реквизиты Код услуги и Код клиента представлены полями ввода, что делает заполнение таблицы данными из квитанций ненаглядным для пользователя. Оптимальное решение – это выбор соответствующих значений из справочников. Для данной цели предназначен элемент управления, называемый поле со списком. Однако предварительно необходимо удалить два поля ввода, предназначенные для кодов. Чтобы реализовать данную операцию, поле вместе с подписью надо активизировать, а затем, например, нажать клавишу Delete. Проверить правильность выполнения операции можно, переключившись в режим Формы через меню Вид→Режим формы;

в) для добавления элемента управления Поле со списком можно щелкнуть по соответствующему инструменту на панели и разместить его в разделе Область данных. Сразу после отпускания кнопки мыши на экране появляется первое диалоговое окно Создание полей со списком; в нем надо выбрать способ, которым поле со списком получает свои значения. В данном случае можно оставить предлагаемое значение – выбор из таблицы или запроса и нажать кнопку Далее (рис. 1.9). Во втором диалоговом окне Создание полей со списком следует выбрать конкретную таблицу Услуги (рис. 1.10), а в третьем диалоговом окне – отдельные реквизиты таблицы для списка с помощью кнопки > (либо все реквизиты с помощью кнопки >>). В данную форму имеет смысл включить все реквизиты: Код услуги, Наименование услуги, Стоимость, Количество дней (рис. 1.11). В следующем диалоговом окне все параметры можно оставить без изменения (рис. 1.12) и перейти к окну, где нужно указать поле Код услуги, как источник для значений (рис. 1.13). В последнем диалоговом окне необходимо ввести подпись к полю, например, Услуга (рис. 1.14). После всех этих действий следует нажать кнопку Готово;

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

д) сохранить форму и закрыть ее окно.

Рис. 1.9. Первое диалоговое окно Создание полей со списком

Рис. 1.10. Второе диалоговое окно Создание полей со списком

Рис. 1.11. Третье диалоговое окно Создание полей со списком

Рис. 1.12. Четвертое диалоговое окно Создание полей со списком

Рис. 1.13. Пятое диалоговое окно Создание полей со списком

Рис. 1.14. Шестое диалоговое окно Создание полей со списком

Рис. 1.15. Реализованная форма Заказы в режиме Конструктор

Если формы разработаны правильно, то пользователь может приступать к вводу входной информации, используя кнопку Открыть на вкладке Формы. Как указывалось выше, исходные данные для задачи содержатся в документах вида 2. Ради экономии места они представлены в обработанном виде в табл. 1.1–1.3, т.е. в виде трех списков: перечня услуг сервисного центра, списка клиентов, квитанций с заказами. Их будем использовать в качестве тестов для запросов.

Табл. 1.1. Пример перечня услуг

Наименование услуги	Стоимость, руб.	Количество дней
Ремонт телевизора	15 000
Установка антенны	8 000
Вставка ДМ–блока	3 000
Ремонт видеомагнитофона	20 000
Подключение телефона	7 000
Подключение компьютера	9 000
Диагностика компьютера	12 000
Монтаж ЛВС	18 000
Подключение модема	4 000

Табл. 1.2. Пример списка клиентов

Наименование клиента

ПКФ «Марс»

ПКП «Сож»

ЗАО «Альбатрос»

Магазин «Южный»

ПКП «Монстр»

АПК «Верас»

ПКЦ «Геркулес»

Табл. 1.3. Пример списка квитанций

Номер заказа	Наименование клиента	Дата	Наименование услуги	Стоимость, руб.	Количество дней	Оплачено
	ПКП «Сож»	25.05.04	Ремонт видеомагнитофона			Да
	ПКП «Монстр»	25.05.04	Установка антенны			Да
	АПК «Верас»	25.05.04	Установка антенны			Да
	Магазин «Южный»	25.05.04	Ремонт телевизора			Нет
	ПКЦ «Геркулес»	25.05.04	Диагностика компьютера			Да
	ПКЦ «Геркулес»	26.05.04	Монтаж ЛВС			Да
	ПКФ «Марс»	26.05.04	Ремонт видеомагнитофона			Нет
	АПК «Верас»	26.05.04	Вставка ДМ–блока			Да
	ЗАО «Альбатрос»	27.05.04	Подключение телефона			Да
	ЗАО «Альбатрос»	27.05.04	Установка антенны			Да
	ПКП «Монстр»	27.05.04	Подключение компьютера			Да
	АПК «Верас»	29.05.04	Подключение модема			Да
	ПКП «Сож»	01.06.04	Диагностика компьютера			Да
	ПКФ «Марс»	01.06.04	Ремонт телевизора			Да
	АПК «Верас»	02.06.04	Подключение компьютера			Нет
	ПКЦ «Геркулес»	03.06.04	Монтаж ЛВС			Да
	Магазин «Южный»	03.06.04	Подключение телефона			Да
	ПКЦ «Геркулес»	03.06.04	Вставка ДМ–блока			Да
	ПКЦ «Геркулес»	04.06.04	Монтаж ЛВС			Да
	ЗАО «Альбатрос»	05.06.04	Подключение модема			Нет

Создание запросов на выборку

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

Запрос на выборку является самым распространенным типом запроса. При его выполнении данные, удовлетворяющие условиям отбора, выбираются из одной или нескольких таблиц и выводятся в определенном порядке. Как правило, запросы на выборку предполагают наличие в запросе какого-либо условия, записанного в строке Условие отбора бланка QBE. Рассмотрим методику разработки запросов на примере создания списка всех услуг, предлагаемых сервисным центром, упорядоченных по наименованию:

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

создание запросов начинается с нажатия кнопки Создать и выбора режима Конструктор в диалоговом окне Новый запрос (рис. 1.16);

Рис. 1.16. Диалоговое окно Новый запрос в режиме создания запроса

после нажатия кнопки ОК появляется диалоговое окно Добавление таблицы, где отображаются все созданные пользователем таблицы и запросы. Первоначально видны только таблицы Заказы, Клиенты и Услуги. Нажимая кнопку Добавить, требуется последовательно выбрать в окно запроса участвующие в нем объекты, т.е. таблицы и запросы. В данном случае следует включить в запрос только таблицу Услуги (рис. 1.17);

после нажатия кнопки Закрыть становится активным окно запроса с бланком QBE в нижней части;

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

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

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

необходимо проверить результат выполнения запроса, приведенный на рис. 1.19 для исходных данных из табл. 1.1–1.3, нажав кнопку Открыть из окна БД или перейдя в режим Таблица через меню Вид→Режим таблицы из окна запроса. При появлении ошибок в результатах следует вернуться в режим Конструктор, найти их и исправить.

Рис. 1.17. Выбор объектов в окно запроса
в диалоговом окне Добавление таблицы

Рис. 1.18. Вид бланка QBE для запроса по всем услугам

Рис. 1.19. Результат выполнения запроса

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

· названием;

· источником данных;

· макетом бланка QBE;

· результатами выполнения запроса.

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

Список всех клиентов центра

Источник данных: таблица Клиенты.

Название запроса: Список всех клиентов.

Макет бланка QBE:

Поле:	Код клиента	Наименование клиента
Имя таблицы:	Клиенты	Клиенты
Сортировка
Вывод на экран:	√	√

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

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

Код клиента	Наименование клиента
	ПКФ "Марс"
	ПКП "Сож"
	ЗАО "Альбатрос"
	Магазин "Южный"
	ПКП "Монстр"
	АПК "Верас"
	ПКЦ "Геркулес"

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

Источник данных: таблицы Заказы, Услуги, Клиенты.

Название запроса: Список всех заказов.

Макет бланка QBE:

Поле:	Номер заказа	Наименование клиента	Дата	Наименование услуги	Стоимость	Оплачено
Имя таблицы:	Заказы	Клиенты	Заказы	Услуги	Услуги	Заказы
Сортировка		по возрастанию	по возрастанию
Вывод на экран:	√	√	√	√	√	√

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

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

Номер заказа	Наименование клиента	Дата	Наименование услуги	Стоимость	Оплачено
	АПК "Верас"	25.05.04	Установка антенны		Да
	АПК "Верас"	26.05.04	Вставка ДМ–блока		Да
	АПК "Верас"	29.05.04	Подключение модема		Да
	АПК "Верас"	02.06.04	Подключение компьютера		Нет
	ЗАО "Альбатрос"	27.05.04	Установка антенны		Да
	ЗАО "Альбатрос"	27.05.04	Подключение телефона		Да
	ЗАО "Альбатрос"	05.06.04	Подключение модема		Нет
	Магазин "Южный"	25.05.04	Ремонт телевизора		Нет
	Магазин "Южный"	03.06.04	Подключение телефона		Да
	ПКП "Монстр"	25.05.04	Установка антенны		Да
	ПКП "Монстр"	27.05.04	Подключение компьютера		Да
	ПКП "Сож"	25.05.04	Ремонт видеомагнитофона		Да
	ПКП "Сож"	01.06.04	Диагностика компьютера		Да
	ПКЦ "Геркулес"	25.05.04	Диагностика компьютера		Да
	ПКЦ "Геркулес"	26.05.04	Монтаж ЛВС		Да
	ПКЦ "Геркулес"	03.06.04	Монтаж ЛВС		Да
	ПКЦ "Геркулес"	03.06.04	Вставка ДМ–блока		Да
	ПКЦ "Геркулес"	04.06.04	Монтаж ЛВС		Да
	ПКФ "Марс"	26.05.04	Ремонт видеомагнитофона		Нет
	ПКФ "Марс"	01.06.04	Ремонт телевизора		Да

Список услуг стоимостью более 10 000 руб.,
упорядоченный по стоимости

Источник данных: таблица Услуги.

Название запроса: Список услуг стоимостью более 10 000 руб.

Макет бланка QBE:

Поле:	Наименование услуги	Стоимость	Количество дней
Имя таблицы:	Услуги	Услуги	Услуги
Сортировка		по возрастанию
Вывод на экран:	√	√	√
Условие отбора:		>10000

Примечание к бланку QBE. Значение поля Код услуги в данном запросе можно опустить. Условие отбора записей вводится в одноименной строке в столбце, соответствующем условию задачи. В данном случае – в столбце поля Стоимость.

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

Наименование услуги	Стоимость	Количество дней
Диагностика компьютера
Ремонт телевизора
Монтаж ЛВС
Ремонт видеомагнитофона

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

Источник данных: таблица Услуги.

Название запроса: Список услуг менее двух дней или за неделю.

Макет бланка QBE:

Поле:	Наименование услуги	Стоимость	Количество дней
Имя таблицы:	Услуги	Услуги	Услуги
Вывод на экран:	√	√	√
Условие отбора:			<=2
или:			>=7

Примечание к бланку QBE. Когда в запросе имеются два или более условия, связанных отношением или, то в бланке они должны отражаться на различных строках группы полей Условие отбора. Причем после сохранения ввода условий на двух строках (т.е. <=2, >=7) СУБД автоматически преобразует их в соответствующее единственное выражение SQL (языка структурных запросов) <=2 Or >=7, если два или более условий накладываются по одному и тому же полю таблицы.

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

Наименование услуги	Стоимость	Количество дней
Установка антенны
Вставка ДМ–блока
Ремонт видеомагнитофона
Подключение телефона
Подключение компьютера
Подключение модема

Список клиентов, наименование которых начинается с символов «пк»

Источник данных: таблица Клиенты.

Название запроса: Список клиентов, начинающихся с пк.

Макет бланка QBE:

Поле:	Код клиента	Наименование клиента
Имя таблицы:	Клиенты	Клиенты
Вывод на экран:	√	√
Условие отбора:		пк*

Примечание к бланку QBE. После ввода условия пк* СУБД автоматически изменит значение на соответствующее выражение SQL Like "пк*".

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

Код клиента	Наименование клиента
	ПКФ "Марс"
	ПКП "Сож"
	ПКП "Монстр"
	ПКЦ "Геркулес"

Список услуг, в наименовании которых встречается
последовательность символов «монт»

Источник данных: таблица Услуги.

Название запроса: Список услуг с символами монт.

Макет бланка QBE:

Поле:	Наименование услуги	Стоимость	Количество дней
Имя таблицы:	Услуги	Услуги	Услуги
Вывод на экран:	√	√	√
Условие отбора:	*монт*

Примечание к бланку QBE. После ввода условия *монт* СУБД автоматически изменит значение на соответствующее выражение SQL Like "*монт*".

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

Наименование услуги	Стоимость	Количество дней
Ремонт телевизора
Ремонт видеомагнитофона
Монтаж ЛВС

Список услуг стоимостью более 8 000 руб.,
которые выполняются не более чем за 3 дня

Источник данных: таблица Услуги.

Название запроса: Список услуг стоимостью более 8 000 руб и выполнением до 3 дней.

Макет бланка QBE:

Поле:	Наименование услуги	Стоимость	Количество дней
Имя таблицы:	Услуги	Услуги	Услуги
Вывод на экран:	√	√	√
Условие отбора:		>8000	<=3

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

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

Наименование услуги	Стоимость	Количество дней
Подключение компьютера
Монтаж ЛВС

Список неоплаченных заказов

Источник данных: таблицы Заказы, Услуги, Клиенты.

Название запроса: Список неоплаченных заказов.

Макет бланка QBE:

Поле:	Номер заказа	Наименование клиента	Дата	Наименование услуги	Стоимость	Оплачено
Имя таблицы:	Заказы	Клиенты	Заказы	Услуги	Услуги	Заказы
Вывод на экран:	√	√	√	√	√	√
Условие отбора:						Нет

Примечание к бланку QBE. В условии отбора логического поля Оплачено нужно ввести текст Нет.

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

Номер заказа	Наименование клиента	Дата	Наименование услуги	Стоимость	Оплачено
	ПКФ "Марс"	26.05.04	Ремонт видеомагнитофона		Нет
	Магазин "Южный"	25.05.04	Ремонт телевизора		Нет
	АПК "Верас"	02.06.04	Подключение компьютера		Нет
	ЗАО "Альбатрос"	05.06.04	Подключение модема		Нет

Список неоплаченных заказов, а также заказов, стоимость которых менее 5 000 руб.

Источник данных: таблицы Заказы, Услуги, Клиенты.

Название запроса: Список неоплаченных заказов стоимостью меньшей 5 000 руб.

Макет бланка QBE:

Поле:	Номер заказа	Наименование клиента	Дата	Наименование услуги	Стоимость	Оплачено
Имя таблицы:	Заказы	Клиенты	Заказы	Услуги	Услуги	Заказы
Вывод на экран:	√	√	√	√	√	√
Условие отбора:					<5000	Нет

Примечание к бланку QBE. Так как условия в запросе связаны отношением и, то в бланке они должны отражаться на одной строке поля Условие отбора.

Дата добавления: 2015-05-09