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

3.1.Предметная область

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

В функции отдела снабжения входит:

В функции работников склада входит:

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

Все запасные части группируются по различным так называемым тетрадям, каждая их которых содержит перечень запасных частей, относящихся к некоторому типу автомобиля (Газ-69, Газ-24, Уаз-452, Зил-130, Краз-255, Камаз-5320 и пр.), или некоторому типу оборудования (электрооборудование, приборы, подшипники, автошины, автостекло и пр.), а также коэффициенты пересчета стоимости запчастей и информацию по взаимозаменяемости запасных частей для автомобилей различных марок.

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

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

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

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

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

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

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

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

3.2. Инфологическая модель базы данных

Анализ описанной предметной области и решаемых задач позволяет выделить следующие сущности:

При этом сущности (5), (7), (10) имеет смысл классифицировать как ассоциативные сущности (связывают сущности (4)-(2), (6)-(2) и (9)-(1) соответственно), сущность (3) можно определить как характеристическую сущность, поскольку ее существование невозможно без сущности (2), сущности (8-9) можно считать обозначающими сущностями, остальные вышеперечисленные сущности ((1-2), (4), (6)) являются стержневыми. При этом сущность (9) относятся к категории супертипа и включают соответственно следующие подтипы:

Супертип Подтипы
Военные части Военкоматы
Организации ДОСААФ
Организации профобразования
Военные кафедры
Номерные военные части

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

Тетради (Код_тетради, Марка, Коэф._пересчета, . . .)

Запчасти (Лок_код, Код_тетради, Наименование, Кол_нач,

Кол_на_01.01, Поступило, Выписано, Выдано, . . .)

Записи движения (Ном_док, Тип, Лок_код, Дата, Код_орг,

Количество, Цена,. . .)

Наряд на выдачу (Номер1, Номер2, Дата, Статус, Код_в/ч, . . .)

Позиции наряда на выдачу (Номер1, Лок_код, Требуется, Выдано, . . .)

Входной наряд (Номер, Дата, Код_поставщика, . . .)

Позиции входного наряда (Номер, Лок_код, Количество, . . .)

Поставщики (Код, Название, Город, Адрес, Номер_счета,. . . )

Военкоматы (Код, Город, Район, Адрес, . . . )

Организации ДОСААФ (Код, Город, Название, Адрес, Cчет,.. . . )

Организации профобразования (Код, Город, Адрес, Профиль, Cчет, . . . )

Военные кафедры (Код, Город, Вуз, Адрес, Cчет,..)

Номерные военные части (Код, Город, Номер, Командир, . . . )

Автомобильный парк (Код_в/ч, Код_тетради, Кол_авто, Пробег, . . .)

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

Запчасти (#_карт, Код_тетр, Наименование, . . .)

Записи движения (Ном_док, Тип, #_карт, Код_тетр, Дата,

Код_орг, Количество, Цена,. . .)

Позиции наряда на выдачу (Номер1, #_карт, Код_тетр,

Требуется, Выдано, . . .)

Позиции входного наряда (Номер, #_карт, Код_тетр, Кол, . . .)

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

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

3.3. Реляционная схема базы данных

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

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

Create table Тетради (Код_тетради smallint NOT NULL,

Марка char(30), Коэф_пересчета smallint, . . .)

* (Стержневая сущность)

Первичный ключ (Код_тетради)
Ограничения

Значения поля Код_тетради должны быть уникальными;

Значения поля Марка должны принадлежать набору: Газ-69, Газ-24, Уаз-452, Зил-130, Краз-255, Камаз-5320 и пр.

Create table Запчасти (Лок_кодdecimal(6) NOT NULL,

Код_тетр smallint NOT NULL,

Наименование char(40), Кол_нач int,

Кол_на_01.01 int, Поступило int,

Выписано smalint, Выдано int,. . .)

* (Стержневая сущность)

Первичный
Ключ		 (Лок_код)
Внешний ключ

Код_тетради из Тетради

NULL-значения не допустимы

Удаления из Тетради каскадируются

Обновления Тетради. Код_тетради каскадируются
Ограничения

Значения поля Локальный код должны быть уникальными;

Значения поля Код_тетради должны принадлежать набору значений из соответствующего поля таблицы Тетради;

Значения полей Кол_нач, Кол_на_01.01, Поступило, Выписано, Выдано должны быть больше нуля

Create table Записи движения (Ном_док char(10) NOT NULL,

Тип smalint NOT NULL, Лок_код decimal(6) NOT NULL,

Дата date NOT NULL, Код_орг smallint NOT NULL,

Количество smallint, Цена decimal(9,2), . . .)

* (Характеристическая сущность для Запасные части)

Первичный
ключ		 (Ном_док, Тип, Лок_код)
Внешний ключ

Лок_код из Запасные части

NULL-значения не допустимы

Удаления из Запасные части каскадируются

Обновления Запасные части.Лок_код каскадируются
Внешний ключ

(Ном_док, Лок_код) из Позиции наряда на выдачу при значении Тип=1

NULL-значения не допустимы

Удаления из Позиции наряда на выдачу каскадируются

Обновления Позиции наряда на выдачу.Ном_док,

Позиции наряда на выдачу.Лок_код каскадируются
Внешний ключ

(Ном_док, Лок_код) из Позиции входного наряда при значении Тип=2

NULL-значения не допустимы

Удаления из Позиции входного наряда каскадируются

Обновления Позиции входного наряда.Ном_док,

Позиции входного наряда.Лок_код каскадируются
Внешний ключ

(Код_организации) из Военные части при значении Тип=1

NULL-значения не допустимы

Удаления из Военные части ограничиваются

Обновления Военные части.Код_в/ч ограничиваются
Внешний ключ

(Код_организации) из Поставщики при значении Тип=2

NULL-значения не допустимы

Удаления из Поставщики ограничиваются

Обновления Поставщики.Код ограничиваются
Ограничения

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

Значений поля Код_организации должны принадлежать набору значений из соответствующих полей таблиц Военные части и Поставщики;

Значения поля Тип должны принадлежать набору: 1 - запись наряда на выдачу и 2 - запись входного наряда;

Значения полей Количество и Цена должны быть больше нуля

Create table Наряд на выдачу (Номер1 char(10) NOT NULL,

Номер2 char(10), Дата date, Статус smallint,

Код_в/ч smallint NOT NULL, . . .)

* ( стержневая сущность)

Первичный
ключ		 (Номер1)
Внешний ключ

(Код_в/ч) из Военные части

NULL-значения не допустимы

Удаления из Военные части ограничиваются

Обновления Военные части.Код_в/ч ограничиваются
Ограничения

Значения поля Номер1 должны быть уникальными;

Значений поля Код_в/ч должны принадлежать набору значений из соответствующего поля таблицы Военные части;

Значения поля Статус должны принадлежать набору: 1 - выписано, 2 - учтено и 3 - подтверждено;

Изменение значения поля Статус с 1 на 2 ведет к увеличению значения поля Выписано таблицы Запасные части, изменение значения поля Статус с 2 на 3 ведет к увеличению значения поля Выдано, уменьшению значения поля Выписано таблицы Запасные части и копирование строк таблицы Позиции наряда на выдачу, связанных с данной строкой, в таблицу Записи движения.

Create table Позиции наряда на выдачу(Номер1 char(10) NOT NULL,

Лок_код decimal(6) NOT NULL,

Требуется smallint,Выдано smallint, . . .)

* (ассоциативная сущность - связывает Запасные части и

Наряд на выдачу)

Первичный
Ключ		 (Номер1, Лок_код)
Внешний ключ

(Номер1) из Наряд на выдачу

NULL-значения не допустимы

Удаления из Наряд на выдачу каскадируются

Обновления Наряд на выдачу.Код_в/ч каскадируется
Внешний ключ

(Локальный_код) из Запасные части

NULL-значения не допустимы

Удаления из Запасные части ограничиваются

Обновления Запасные части. Локальный_код ограничиваются
Ограничения

Значений поля Номер1 должны принадлежать набору значений из соответствующего поля таблицы Наряд на выдачу;

Значений поля Локальный_код должны принадлежать набору значений из соответствующего поля таблицы Запасные части;

Значения полей Требуется и Выдано должны быть неотрицательными.

Create table Военные части (Код smallint NOT NULL,

Город char(20) NOT NULL, Подтип smallint,

Название char(40), Адрес char(40),

Счет char(40), ВУЗ char(40),

Командир char(20), . . . )

* (Характеристическая сущность)

Первичный
Ключ		 (Код)
Ограничения

Значения поля Код должны быть уникальными;

Значения поля Подтип принимает значения от 1 до 5 по числу подтипов.

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