Особенности проектирования базы данных для информационной системы «Учет заказов по выполнению отделочных работ»



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

1. Анализ предметной области

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

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

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

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

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

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

2. Определение сущностей и связей между ними

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

Рис. 1. Форма оформления заказа

Компоненты формы определены на основе функций, которые должны выполняться информационной системой.

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

− работы;

− заказ;

− бригада;

− мастер;

− клиент.

Представим информационную модель задачи в виде диаграммы «сущность–связь» (рис. 2).

Рис. 2. Диаграмма «сущность — связь»

Описание связей между объектами представлено в таблице 1 [2, с.12].

Таблица 1

Описание связей между объектами (сущностями)

Связь	Тип связи	Пояснение
Работы — Заказ	«многие-ко-многим»	одна работа может присутствовать в нескольких заказах, а в одном заказе может быть указано несколько работ
Клиент — Заказ	«один-ко-многим»	один клиент может участвовать в нескольких заказах, но один заказ оформляется только на одного клиента
Бригада — Заказ	«один-ко-многим»	одна бригада может работать по нескольким заказам, но один заказ обрабатывается только одной бригадой
Бригада — Мастера	«один-ко-многим»	к одной бригаде относится несколько мастеров, но один мастер относится только к одной бригаде

3. Построение реляционной модели

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

Связь «многие-ко-многим» будет преобразована за счет введения дополнительной сущности СОСТАВ, которая должна содержать первичные ключи сущностей ЗАКАЗ и РАБОТЫ (они будут внешними ключами), а также атрибуты, имеющие отношение к отдельной заказанной работе (например, количество, стоимость выполнения оной работы в указанном количестве) — атрибуты связи.

Таким образом, получим эквивалентный вариант диаграммы сущность-связь (см. рис. 3).

Рис. 3. Диаграмма «сущность — связь» после удаления связи «многие-ко-многим»

4. Задание первичных ключей, определение атрибутов сущностей

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

Таблица 2

Атрибуты ипервичные ключи сущностей

Сущность	Первичный ключ	Атрибуты
Заказ	код_заказа	код_заказа код_клиента код_бригады дата_заказа срок_исполнения сумма дата_начала дата_испол_ориент дата_испол_факт адрес_исполнения предоплата доплата_по_исполн дата_полной_оплаты
Состав		код_работы код_заказа кол_во цена_работы
Мастер	код_мастера, код_профиля	код_мастера код_бригады ФИО_мастера код_профиля профиль
Клиент	код_клиента	код_клиента ФИО_клиента тел_клиента адрес_клиента
Сущность	Первичный ключ	Атрибуты
Работа	код_работы, код_категории, код_ед_измерения	код_работы код_ед_измерения ед_измерения код_категории категория наименование цена_ед
Бригада	код_бригады	код_бригады занятость

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

Отношение находится в первой нормальной форме [2, с.22], если для него выполняются условия:

− отсутствуют повторяющиеся атрибуты;

− каждый атрибут неделим.

Согласно требованиям первой нормальной формы, необходимо преобразовать атрибуты «ФИО_клиента», «адрес_клиента» в сущности «Клиент», «адрес_исполнения» в сущности «Заказ», «ФИО_мастера» в сущности «Мастер» так, чтобы получить неделимые атрибуты. В дальнейшем это позволит реализовать такие функции, например, как поиск информации о клиентах или мастерах по фамилии, поиск заказов по адресу и

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

Отношение находится во второй нормальной форме [2, с.23], если оно удовлетворяет следующим требованиям:

− выполняются условия первой нормальной формы;

− все поля записи функционально полно зависят от первичного ключа.

В сущности «Работы» атрибуты «ед_измерения», «категория» зависят только от частей составного первичного ключа «код_ед_измерения» и «код_категории» соответственно. Поэтому отношение «Работа» не находится во второй нормальной форме и его следует преобразовать, выделив из него отдельные сущности «Категория» и «Ед_измерения». Атрибуты «код_ед_измерения» и «код_категории» в сущности «Работы» станут внешними ключами.

В сущности «Мастер» атрибут «профиль» зависит только от частей составного первичного ключа: «код_профиля». Поэтому отношение «Мастер» не находится во второй нормальной форме и его следует преобразовать, выделив из него отдельную сущность «Профиль». Атрибут «код_профиля» в сущности «Мастер» станет внешним ключом.

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

Отношение находится в третьей нормальной форме [2, с.23], если оно удовлетворяет следующим требованиям:

− должны выполняться условия второй нормальной формы;

− внутри каждой сущности должны отсутствовать транзитивные связи.

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

Дата_исполн_ориент = дата_начала + срок_исполнения,(1)

где «дата_начала», «срок_исполнения» — поля сущности «Заказ».

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

Доплата_по_исполн = сумма — предоплата,(2)

где «сумма» — поле сущности «Заказ».

В сущности «Состав» атрибут «сумма_работы» зависит от атрибута «кол_во» этой же сущности. Поэтому удалим данный атрибут из сущности, создав на форме поле, вычисляемое по формуле:

Сумма_работы = цена_ед * кол_во,(3)

где «цена_ед» — поле сущности «Работа»;

«кол_во» — поле сущности «Состав».

Приведем диаграмму взаимосвязей между атрибутами сущностей в третьей нормальной форме (рис. 4).

Рис. 4. Диаграмма взаимосвязей между атрибутами сущностей в третьей нормальной форме

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

Литература:

1. Хомоненко А., Цыганков В., Мальцев М. Базы данных: Учебник для высших учебных заведений. — М.: КОРОНА-принт, 2011.
2. Кузин А. В. Базы данных: учеб. пособие для студ. высш. учеб. заведений / А. В. Кузин, С. В. Левонисова. — 5-е изд., испр. — М.: Издательский центр «Академия», 2012.