Проектирование информационных баз данных

 

* Звездочкой  помечены измененные или добавленные реквизиты, а так же новые ИО. Удаленные реквизиты перечеркнуты. Подчеркнутые реквизиты формируют для ИО первичный ключ. Для некоторых ИО ключи оказались составными. Например, для ИО «План выпуска» ключ состоит из реквизитов «Номер цеха», «Код изделия» и «Месяц», так как каждый цех может выпускать несколько наименований изделий в пределах одного месяца и выпуск планируется помесячно. По аналогичным причинам возникают составные ключи в спецификациях документов.

3.2 Определение связей информацонных обьектов и построение информационно-логической модели.

Рассмотрим связи  между полученными ИО:

• Договора ◄─►► Спецификации договоров (связь 1:М) Один договор может быть заключен на несколько изделий, но ни одно изделие не может проходить по договору дважды.
• Цеха ◄─►► Цеховые накладные (связь 1:М) В каждой цеховой накладной указывается один цех – тот, что ее выписал.
• Цеха ◄─►► План выпуска (связь 1:М) План выпуска существует для всех цехов. Вместе с тем, месячный план на цех на изделие не может повторяться.
• Единицы измерения ◄─►► Изделия (связь 1:М)
• Изделия ◄─►► Спецификации договоров (связь 1:М) В каждый договор одно изделие входит один раз, но договоров-то много.
• Изделия ◄─►► План выпуска (связь 1:М) В плане выпуска одно и то же изделие может быть запланировано в разные месяцы и в разные цеха.
• Изделия ◄─►► Спецификации цеховых накладных (связь 1:М) Изделия выпускаются постоянно в соответствии с планом. Поэтому на одно и то же изделие цеховые накладные будут приходить много раз.
• Цеховые накладные ◄─►► Спецификации цеховых накладных (связь 1:М) Цеховая накладная однозначно определяется номером цеха и номером накладной (номер накладной уникален для цеха). В спецификациях цеховых накладных этот ключ из двух реквизитов может встречаться несколько раз для разных изделий.
• Календарь ◄─►► Спецификации договоров (связь 1:М)
• Календарь ◄─►► План выпуска (связь 1:М)

 

ИО, соответствующие  условно-постоянным обьектам, по сути, являются справочниками. Для справочников естественное отношение – это 1:M с еденичкой на стороне справочника. 

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

• На уровень иерархии 0 попадают следующие ИО: «Заказчики», «Цеха», «Склады», «Еденицы измерения», «Календарь».
• На уровень иерархии 1 попадают следующие ИО: «Изделия», «Договора», «Цеховые накладные».
• На уровень иерархии 2 попадают следующие ИО: «План выпуска», «Товарнотранспортные накладные», «Спецификации договоров», «Спецификации цеховых накладных».
• На уровень иерархии 3 попадают следующие ИО: «Спецификации товарнотранспортных накладных», «Платежные поручения».
• На уровень иерархии 4 попадают следующие ИО: «Спецификации платежных поручений».

 

3.3 Логическая структура базы данных.

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

Логическая структура  Реляционной Базы Данных:

 

Здесь кружок означает элемент 1 отношения (1:M), а стрелка – M.

3.4 Физическое моделирование базы данных.

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

Соответствие  имени ИО и имени таблицы базы данных установлено естественным образом:

Наименование документа	Наименование  таблицы БД
Цеха	Цеха
Изделия	Изделия
Единицы измерения*	Ед. изм.
Договора	Договора
Спецификации  договоров*	СДОГ
Цеховые накладные	ЦН
Спецификации  цеховых накладных*	СЦН
План  выпуска	План выпуска
Календарь	Календарь

 

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

При реализации отчета возникли следующие технические  сложности:

• В заголовке отчета должно быть указано название изделия и еденица измерения для этого изделия. Для того, чтобы реализовать это требование, необходимо ввести в заголовок отчета поля, формируемые по дополнительному запросу. А это невозможно в СУБД Microsoft Access. Поэтому применяется следующий прием: В основной запрос отчета вводятся фиктивные столбцы «Наименование изделия», «Еденица измерения изделия». Запрос переделывается так, чтобы в них в каждой строке стояли одинаковые значения, соответствующие параметру запроса. Тогда по этим столюцам запрос можно сгруппировать (а отображение в отчете динамических заголовков групп Access поддерживает). Получится одна группа с требуемым заголовком (см. рис.).

 

• В отчете должны учитываться пустые записи. Так в некоторый месяц может  не быть заказов на изделие, а в  плане производства оно может  и быть. Обратная ситуация тоже возможна. В соответствующих ячейках таблицы результатов запроса должны быть проставлены нули для корректного отображения отклонения. Кроме того, месяцы, в которые не было заказов на изделие и изделие не было запланировано к производству, должны так же участвовать в отчете (см. рис.). Это реализуется через обьединение (UNION) изначального запроса (он выдает только строки с ненулевыми значениями) и пустого запроса (в нем присутствуют все месяцы, а в столбцах «Количество...» и «Сумма...» стоят нули).

 

• В отчете должны быть указаны имена месяцев  в естественном порядке. Для этого в отчет введена таблица «Календарь». Это позволяет сортировать строки отчета по числовому значению месяца, а выводить в отчет его имя.

 

 

Помесячная  статистика по планам на заданное изделие

 
 
 
 
 

Помесячная  статистика по договорам  на заданное изделие

 

3.5 Контрольный пример.

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

В первую очередь заполняются справочники: 

Цеха
Номер цеха	Наименование  цеха
1	Электротехнический 1
2	Колбасный
3	Электротехнический 2

  
 
 
 
 

Ед. изм
Код еденицы измерения	Наименование  ед. измерения
001	кг.
002	г.
003	шт.

 

Изделия
Код изделия	Наименование Изделия	Код еденицы измерения	Цена	Номер цеха
001	Лампочка	003	20 р.	1
002	Окорок	001	140 р.	2
003	Сосиски "Съешь  меня"	001	81,20 р.	2

 

 

Календарь
Номер месяца	Имя месяца
1	Январь
2	Февраль
3	Март
4	Апрель
5	Май
6	Июнь
7	Июль
8	Август
9	Сентябрь
10	Октябрь
11	Ноябрь
12	Декабрь

 

Далее заведем  несколько договоров: 

Договора
Номер договора	Код заказчика
er6788	0350000001
er6789	0350000001
er6791	0970000001
er8755	0674555001

 

СДОГ
Номер договора	Месяц отгрузки	Код изделия	Количество
er6788	1	001	10
er6788	1	002	2
er6789	2	001	111
er8755	1	001	50

 
 

Пусть далее  сформирован некоторый план выпуска: 

План  выпуска
Номер цеха	Код изделия	Количество	Месяц
1	001	170	2
1	001	90	4
2	003	10	1
2	003	1	2
3	001	30	2