Курсовая работа: Методология проектирования баз данных

Московский Государственный Технический Университет Им. Н.Э. Баумана

Калужский филиал

Кафедра САУ и электротехники

Методология проектирования баз данных

Расчётно-пояснительная записка к курсовой работе

по курсу «Проектирование алгоритмов и

программного обеспечения»

Выполнил: студент группы САУ-81

Лебедев А.Л.

Руководитель: доцент, к. т. н.

Николаенко С. И.

Калуга

2005 г.

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

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

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

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

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

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

Работники и фирмы-клиенты могут иметь телефон. Их может быть несколько.

При работе с электронными документами должна быть доступна следующая информация:

1. штат сотрудников предприятия (таб. №, ФИО, дом. адрес, телефон, дата приёма на работу и назначения на должность, должность, номер бригады);

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

3. какая бригада работает на делянке;

4. сколько леса бригада привезла на базу. Сколько всего леса и какого хранится на базе;

5. какую продукцию и по какой цене производит предприятие;

6. какой план, когда и на какой срок выдан начальником сбыта цеховым бригадам;

7. сколько леса выписал начальник бригады на базе и сдал продукции на склад. Сколько и какой продукции хранится на складе;

8. с какими фирмами-клиентами сотрудничает предприятие (название фирмы, адрес и телефон);

9. какой заказ и на какой срок сделан предприятию фирмой-клиентом (вид, количество продукции и по какой цене);

10. какой товар, сколько, по какой цене и когда отгружен фирме-клиенту.

Типовые операции (транзакции)

· приём на работу, приписывание к бригадам, назначение на должность, увольнение, снятие с должности работников, перевод в другую бригаду;

· создание новых и расформирование старых бригад;

· выписывание лесобилетов у лесничества;

· выдача делянок бригадам;

· приём делянок у бригад;

· возвращение делянок лесничеству;

· привоз леса бригадой на базу;

· выдача плана цеховым бригадам;

· получение леса бригадирами на базе;

· сдача продукции на склад;

· добавление и удаление фирм-клиентов;

· получение заказа от фирмы-клиента;

· продажа продукции.

Общий вид накладных и других документов

Лесобилет №_____

Лесничество «____________» Предприятие «____________»

Заведующий делянками ____________

«____»___________200 г.

Накладная на возврат делянки №___ лесничеству «____________» №___ от ______

Заведующий делянками ___________

Накладная на заказ товара №___ от ____

Фирма-заказчик __________Начальник сбыта __________

Адрес фирмы __________

Телефон __________

Срок выполнения заказа_____

Всего на сумму _______

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

Подготовка отчёт о штате сотрудников по форме:

Подготовка отчёта о наличии товара на складе по форме:

Кладовщик ______________

Всего на сумму __________

Подготовка отчёта об ассортименте товара по форме:

Подготовка отчёта о поставках товара фирмам-клиентам по форме:

Название фирмы «_____________»

Адрес

Телефон Срок поставок с___ по ___

Всего на сумму: _________

Подготовка плана работы цеховым бригадам по форме:

План работы № __

Бригада №___Начальник сбыта ____________

Бригадир ___________Дата ____ Срок выполнения, дней ____

II Концептуальное проектирование

1. Типы сущности

Выделим следующие типы сущностей:

1. древесина;

2. лесобилет;

3. делянка;

4. работник;

5. бригада;

6. бригадир;

7. зав. делянками;

8. нач. базы;

9. продукция (товар);

10. зав. складом

11. нач. сбыта;

12. фирма-клиент;

13. телефон;

14. накладная на возврат делянки лесничеству;

15. накладная на заказ товара;

16. накладная на продажу товара;

17. план работы цеховой бригады;

Типы сущностей

2. Типы связей

Определим типы связей:

1. работник приписан к бригаде;

2. бригаду возглавляет бригадир;

3. зав. делянками выписывает лесобилет;

4. лесобилет содержит делянку;

5. древесина указана для делянки;

6. зав. делянки оформляет накладную на возврат делянки лесничеству;

7. накладная на возврат делянки лесничеству содержит делянку;

8. делянка закреплена за бригадиром;

9. древесина имеется у бригадира;

10. лес хранится у начальника базы;

11. начальник сбыта оформляет накладную на заказ;

12. фирма-клиент выписывает накладную на заказ;

13. продукция входит в накладную на заказ;

14. начальник сбыта выдаёт план работы;

15. бригадир получает план работы;

16. продукция входит в план работы;

17. бригадир сдаёт продукцию;

18. продукция хранится у зав. складом;

19. зав. складом оформляет накладную на продажу;

20. фирма-клиент выписывает накладную на продажу;

21. продукция входит в накладную на продажу;

22. фирма-клиент имеет телефон;

23. работник имеет телефон.

Типы связей

3. Атрибуты

4. Домены атрибутов

5. Определение потенциальных ключей и выбор среди них первичных

6 . Построение ER -модели

III Логическое проектирование

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

1.1 Удаление связей типа M : N и связей с атрибутами

Связи «Древесина имеется у Бригадир» и «Древесина хранится у Нач. базы» заменяем на тип сущности «Наличие» с наследуемыми атрибутами «Таб. номер» и «Порода» и с собственным атрибутом «Количество» и типы связей «Древесина имеется в Наличие», «Нач. базы имеет в Наличие», «Бригадир имеет в Наличие».

Связь «Древесина указана для Делянка» заменяем на тип сущности «Наличие древесины» с наследуемыми атрибутами «Порода» и «Номер» и собственным атрибутом «Количество» и типы связей «Древесина характеризует Наличие древесины» и «Наличие древесины указано для Делянка»

Связь «Продукция хранится у Зав. складом» заменяем на тип сущности «Хранение» с наследуемыми атрибутами «Наименование» и «Цена» и собственным атрибутом «Количество» и типы связей «Продукция имеется на Хранение» и «Зав. складом имеет на хранение»

Связи «Входит в», связывающие сущности «Продукция» и «Накладная на заказ», «Накладная на продажу» и «План работы» заменяем на типы сущности «Строка накладной на заказ», «Строка накладной на продажу» и «Строка плана работы», типы связей «Продукция входит в Строка накладной на заказ», «Продукция входит в Строка накладной на продажу», «Продукция входит в План работы»; «Накладная на заказ содержит Строка накладной на заказ», «Накладная на продажу содержит Строка накладной на продажу» и «План работы содержит Строка плана работы» соответственно. При этом типы сущности «Строка накладной на заказ», «Строка накладной на продажу» и «Строка плана работы» наследуют атрибуты «Номер», «Наименование», «Цена» и имеют собственный атрибут «Количество».

Связи «Лесобилет содержит Делянка» и «Накладная на возврат делянки лесничеству содержит Делянка» заменяем на два типа сущности «Строка лесобилета» и «Строка накладной на возврат делянки лесничеству» с наследуемыми атрибутами «Номер лесобилета», «Номер накладной» и «Номер делянки» и типы связей «Лесобилет содержит Строка лесобилета» и «Накладная на возврат делянки лесничеству содержит Строка накладной на возврат делянки лесничеству»; «Делянка входит в Строка лесобилета» и «Делянка входит в Строка накладной на возврат делянки лесничеству» соответственно.

Связь «Бригадир сдаёт Продукция» заменяем на тип сущности «Объём товара» с наследуемыми атрибутами «Таб. номер», «Наименование» и «Цена» и типы связей «Продукция входит в Объём товара» и «Бригадир выпускает Объём товара»

Скорректированная таблица «Типы сущностей»

Скорректированная таблица «Типы связей»

Ключи

2. Построение скорректированной ER – модели

3. Определение наборов отношений

1. Фирма-клиент (название, адрес, номер телефона)

ПК: название, адрес

ВК: нет

1. Древесина (порода)

ПК: порода

ВК: нет

2. Делянка (номер, квадрат, площадь)

ПК: номер

ВК: нет

3. Работник (таб. номер, ФИО, адрес, телефон, дата поступления, дата назначения, должность, номер бригады)

ПК: таб. номер

ВК: номер бригады ссылается на бригада (номер)

4. Бригадир (таб. номер, ФИО, адрес, телефон, дата поступления, дата назначения, номер бригады)

ПК: таб. номер

ВК: номер бригады ссылается на бригада (номер)

5. Бригада (номер, специализация, номер цеха)

ПК: номер

ВК: нет

6. Продукция (наименование, цена, единицы измерения)

ПК: наименование, цена

ВК: нет

7. Телефон (номер, название фирмы-клиента, адрес фирмы-клиента, таб. номер работника)

ПК: номер

ВК: название фирмы-клиента, адрес фирмы-клиента ссылается на фирма-клиент (название, адрес)

ВК: таб. номер работника ссылается на работник (таб. номер)

8. Накладная на заказ (номер, дата, срок, таб. номер нач. сбыта, название фирмы-клиента, адрес фирмы-клиента, номер телефона фирмы-клиента)

ПК: номер

ВК: название фирмы-клиента, адрес фирмы-клиента ссылается на фирма-клиент (название, адрес)

ВК: таб. номер начальника сбыта ссылается на работник(таб. номер)

9. Строка накладной на заказ (кол-во, наименование продукции, цена продукции, номер накладной)

ПК: номер накладной, наименование продукции, цена продукции

ВК: номер накладной ссылается на накладная на заказ (номер)

ВК: наименование продукции, цена продукции ссылается на продукция (наименование, цена)

10. Накладная на продажу (номер, дата, таб. номер зав. складом, название фирмы-клиента, адрес фирмы-клиента, номер телефона фирмы-клиента)

ПК: номер

ВК: название фирмы-клиента, адрес фирмы-клиента ссылается на фирма-клиент(название, адрес)

ВК: таб. номер зав. складом ссылается на работник(таб. номер)

11. Строка накладной на продажу(кол-во, наименование продукции, цена продукции, номер накладной)

ПК: номер накладной, наименование продукции, цена продукции

ВК: номер накладной ссылается на накладная на продажу(номер)

ВК: наименование продукции, цена продукции ссылается на продукция(наименование, цена)

12. План работы(номер, дата, срок, объём древесины, таб. номер нач. сбыта, номер бригады)

ПК: номер

ВК: таб. номер начальника сбыта ссылается на работник(таб. номер)

ВК: номер бригады ссылается на бригада(номер)

13. Строка плана работы(кол-во, наименование продукции, цена продукции, номер плана)

ПК: номер плана, наименование продукции, цена продукции

ВК: номер плана ссылается на план работы(номер)

ВК: наименование продукции, цена продукции ссылается на продукция(наименование, цена)

14. Объём товара(кол-во, таб. номер бригадира, наименование продукции, цена продукции)

ПК: таб. номер бригадира, наименование продукции, цена продукции

ВК: таб. номер бригадира ссылается на работник(таб. номер)

ВК: наименование продукции, цена продукции ссылается на продукция(наименование, цена)

15. Наличие древесины(кол-во, порода, номер делянки)

ПК: номер делянки, порода

ВК: номер делянки ссылается на делянка(номер)

ВК: порода ссылается на древесина(порода)

16. Лесобилет(номер, таб. номер зав. делянками, дата)

ПК: номер

ВК: таб. номер зав. делянками ссылается на работник(таб. номер)

17. Строка лесобилета(номер лесобилета, номер делянки)

ПК: номер лесобилета, номер делянки

ВК: номер лесобилета ссылается на лесобилет(номер)

ВК: номер делянки ссылается на делянка(номер)

18. Накладная на возврат делянки лесничеству(номер, таб. номер зав. делянками, дата)

ПК: номер

ВК: таб. номер зав. делянками ссылается на работник(таб. номер)

19. Строка накладной на возврат делянки лесничеству(номер накладной, номер делянки)

ПК: номер накладной, номер делянки

ВК: номер накладной ссылается на накладная на возврат делянки лесничеству(номер)

ВК: номер делянки ссылается на делянка(номер)

20. Наличие(таб. номер бригадира, таб.номер нач. базы, порода, кол-во)

ПК: таб. бригадира, таб.номер нач. базы, порода

ВК: таб. номер бригадира ссылается на бригадир(таб. номер)

ВК: таб. номер нач. базы ссылается на работник(таб. номер)

ВК: порода ссылается на древесина(порода)

21. Хранение(таб. номер зав. складом, наименование продукции, цена продукции, кол-во)

ПК: наименование продукции, цена продукции

ВК: таб. номер зав. складом ссылается на работник(таб. номер)

ВК: наименование продукции, цена продукции ссылается на продукция(наименование, цена)

4. Проверка набора отношений на соответствие требованиям нормализации

Для каждого из отношений выпишем функциональные зависимости.

1. Фирма-клиент

название, адрес – номер телефона

2. Древесина

порода –

3. Делянка

номер – квадрат

номер – площадь

4. Работник

таб. номер – ФИО

таб. номер – адрес

таб. номер – телефон

таб. номер – дата поступления

таб. номер – дата назначения

таб. номер – должность

таб. номер – номер бригады

5. Бригадир

таб. номер – ФИО

таб. номер – адрес

таб. номер – телефон

таб. номер – дата поступления

таб. номер – дата назначения

таб. номер – номер бригады

6. Бригада

номер – специализация

номер – номер цеха

7. Продукция

наименование, цена – единицы измерения

8. Телефон

номер – название фирмы-клиента

номер – адрес фирмы-клиента

номер – таб. номер работника

9. Накладная на заказ

номер – дата

номер – срок

номер – таб. номер начальника сбыта

номер – название фирмы-клиента

номер – адрес фирмы-клиента

номер – телефон фирмы-клиента

10. Строка накладной на заказ

номер накладной, наименование продукции, цена продукции – количество

11. Накладная на продажу

номер – дата

номер – таб. номер зав. складом

номер – название фирмы-клиента

номер – адрес фирмы-клиента

номер – телефон фирмы-клиента

12. Строка накладной на продажу

номер накладной, наименование продукции, цена продукции – количество

13. План работы

номер – дата

номер – срок

номер – таб. номер нач. сбыта

номер – номер бригады

14. Строка плана работы

номер плана, наименование продукции, цена продукции – количество

номер плана, наименование продукции, цена продукции – объём древесины

15. Объём товара

таб. номер бригадира, наименование продукции, цена продукции – количество

16. Наличие древесины

номер делянки, порода – количество

17. Лесобилет

номер – таб. номер зав. делянками

номер – дата

18. Строка лесобилета

номер лесобилета, номер делянки –

19. Накладная на возврат делянки лесничеству

номер – таб. номер зав. делянками

номер – дата

20. Строка накладной на возврат делянки лесничеству

номер накладной, номер делянки

21. Наличие

таб. номер бригадира, таб. номер нач. базы, порода – количество

22. Хранение

таб. номер зав. складом, наименование продукции, цена продукции – количество

5. Проверка модели на возможность выполнения всех транзакций

Для каждой транзакции на ER-модели обозначим путь её выполнения.

6. Построение окончательного варианта ER – модели

ER–модель, изображённую в пункте 2 логического проектирования будет окончательным вариантом, т.к. пунктах 3 -5 ER – модель не претерпела никаких изменений.

7. Определение набора ограничений целостности

1 Обязательные данные. К ним относятся все атрибуты, составляющие первичные ключи + ещё некоторые атрибуты, которые по постановке задачи или из смысловых соображений не могут быть пустыми (см. таблицу «Атрибуты» и таблицу ниже).

2 Ограничение доменов атрибутов

См. таблицу доменов.

3 Ограничение ссылочной целостности

Для всех внешних ключей определим действие на случай удаления или изменения родительских записей.

Работник

ВК: номер бригады ссылается на бригада(номер)

при изменении CASCADE, при удалении NO ACTION

Бригадир

ВК: номер бригады ссылается на бригада(номер)

при изменении CASCADE, при удалении NO ACTION

Телефон

ВК: название фирмы-клиента, адрес фирмы-клиента ссылается на фирма-клиент(название, адрес)

при изменении CASCADE, при удалении CASCADE

ВК: таб. номер работника ссылается на работник(таб. номер)

при изменении CASCADE, при удалении CASCADE

Накладная на заказ

ВК: название фирмы-клиента, адрес фирмы-клиента ссылается на фирма-клиент(название, адрес)

при изменении NO ACTION, при удалении NO ACTION

ВК: таб. номер начальника сбыта ссылается на работник(таб. номер)

при изменении CASCADE, при удалении NO ACTION

Строка накладной на заказ

ВК: номер накладной ссылается на накладная на заказ(номер)

при изменении CASCADE, при удалении NO ACTION

ВК: наименование продукции, цена продукции ссылается на продукция(наименование, цена)

при изменении NO ACTION, при удалении NO ACTION

Накладная на продажу

ВК: название фирмы-клиента, адрес фирмы-клиента ссылается на фирма-клиент(название, адрес)

при изменении NO ACTION, при удалении NO ACTION

ВК: таб. номер зав. складом ссылается на работник(таб. номер)

при изменении CASCADE, при удалении NO ACTION

Строка накладной на продажу

ВК: номер накладной ссылается на накладная на продажу(номер)

при изменении CASCADE, при удалении NO ACTION

ВК: наименование продукции, цена продукции ссылается на продукция(наименование, цена)

при изменении NO ACTION, при удалении NO ACTION

План работы

ВК: таб. номер начальника сбыта ссылается на работник(таб. номер)

при изменении CASCADE, при удалении NO ACTION

ВК: номер бригады ссылается на бригада(номер)

при изменении CASCADE, при удалении NO ACTION

Строка плана работы

ВК: номер плана ссылается на план работы(номер)

при изменении CASCADE, при удалении NO ACTION

ВК: наименование продукции, цена продукции ссылается на продукция(наименование, цена)

при изменении NO ACTION, при удалении NO ACTION

Объём товара

ВК: таб. номер бригадира ссылается на работник(таб. номер)

при изменении CASCADE, при удалении NO ACTION

ВК: наименование продукции, цена продукции ссылается на продукция(наименование, цена)

при изменении NO ACTION, при удалении NO ACTION

Наличие древесины

ВК: номер делянки ссылается на делянка(номер)

при изменении CASCADE, при удалении NO ACTION

ВК: порода ссылается на древесина(порода)

при изменении NO ACTION, при удалении NO ACTION

Лесобилет

ВК: таб. номер зав. делянками ссылается на работник(таб. номер)

при изменении CASCADE, при удалении NO ACTION

Строка лесобилета

ВК: номер лесобилета ссылается на лесобилет(номер)

при изменении CASCADE, при удалении NO ACTION

ВК: номер делянки ссылается на делянка(номер)

при изменении CASCADE, при удалении NO ACTION

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

ВК: таб. номер зав. делянками ссылается на работник(таб. номер)

при изменении CASCADE, при удалении NO ACTION

Строка накладной на возврат делянки лесничеству

ВК: номер накладной ссылается на накладная на возврат делянки лесничеству(номер)

при изменении CASCADE, при удалении NO ACTION

ВК: номер делянки ссылается на делянка(номер)

при изменении CASCADE, при удалении NO ACTION

Наличие

ВК: таб. номер работника ссылается на работник(таб. номер)

при изменении CASCADE, при удалении NO ACTION

ВК: порода ссылается на древесина(порода)

при изменении NO ACTION, при удалении NO ACTION

Хранение

ВК: таб. номер зав. складом ссылается на работник(таб. номер)

при изменении CASCADE, при удалении NO ACTION

ВК: наименование продукции, цена продукции ссылается на продукция(наименование, цена)

при изменении NO ACTION, при удалении NO ACTION

4 Бизнес правила

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

IV Физическое проектирование

1. Введение контролируемой избыточности

1. Замена ПК семантически незначащими атрибутами

Т.к. значение первичного ключа «Таб. номер» для типа экземпляра сущности «Работник» освобождается, например, при увольнении работника, но в качестве внешнего ключа может встречаться в одной или нескольких дочерних таблицах, то были введён суррогатный ключ «Условный номер» и новый атрибут «Уволен» для логического удаления работника.

Чтобы не отслеживать уникальность первичного ключа в таблицах «Работник» и «Бригадир», они были объединены в одну таблицу «Работник». В то же время, для уменьшения объёма таблицы «Работник» совокупность значений атрибута «Должность» было выделено в отдельную таблицу «Должность» с полями «Условный номер», «Название» и «Занята». Последнее поле служит для контроля выполнения бизнес-правил.

Те же действия были проведены и в отношении типа сущности «Бригада».

Для таблиц «Фирма-клиент» и «Продукция» также были введены суррогатные ключи «Условный номер» и атрибут «Удалён» для повышения скорости выполнения запросов и логического удаления кортежей отношений.

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

В таблице «Древесина» первичный ключ «Порода» был заменён на суррогатный «Условный номер» для уменьшения объёма дочерних таблиц.

В таблице «Хранение» ввиду своей бесполезности был удалён внешний ключ «Таб. номер зав. складом».

2 Создание таблиц и реализация ограничений

Создание таблиц и реализация ограничений, в соответствие с ранее определённым набором отношений, при помощи выбранной СУБД. В качестве СУБД выберем InterBase 6.0.

Скрипты создания таблиц

Замечание: различного рода ограничения реализуются с помощью доменов, триггеров и хранимых процедур.

CREATE TABLE "Brigada" (

"Usl_nomer" INTEGER NOT NULL,

"Specialisacia" VARCHAR(20) NOT NULL,

"Nomer_Ceha" INTEGER,

"N_Brigada" INTEGER NOT NULL,

"Uv" "Bool" NOT NULL);

ALTER TABLE "Brigada" ADD CONSTRAINT "FK_Brigada" PRIMARY KEY ("Usl_nomer");

CREATE TABLE "Delanka" (

"Numer" "Nomer",

"Kvadrat" VARCHAR(5) NOT NULL,

"Plotschad" DOUBLE PRECISION NOT NULL,

"NBrigadir" INTEGER,

"Isp" SMALLINT DEFAULT 0 NOT NULL);

ALTER TABLE "Delanka" ADD CONSTRAINT "FK_Delanka" PRIMARY KEY ("Numer");

ALTER TABLE "Delanka" ADD CONSTRAINT "FK_N_Brigadir_Delanka" FOREIGN KEY ("NBrigadir") REFERENCES "Rabotnik" ("Usl_nomer") ON DELETE CASCADE ON UPDATE CASCADE;

CREATE TABLE "Dolgnost" (

"Zanata" "Bool",

"N_Dolgn" INTEGER NOT NULL,

"Name_Dolgn" VARCHAR(20) NOT NULL COLLATE PXW_CYRL);

ALTER TABLE "Dolgnost" ADD CONSTRAINT "FK_Dolgnost" PRIMARY KEY ("N_Dolgn");

CREATE TABLE "Drevesina" (

"Poroda" VARCHAR(20) NOT NULL,

"Usl_nomer" INTEGER NOT NULL);

ALTER TABLE "Drevesina" ADD CONSTRAINT "FK_Drevesina" PRIMARY KEY ("Usl_nomer");

CREATE TABLE "Hranenie" (

"N_Produkcia" INTEGER NOT NULL,

"Kolvo" DOUBLE PRECISION NOT NULL);

ALTER TABLE "Hranenie" ADD CONSTRAINT "FK_Hranenie" PRIMARY KEY ("N_Produkcia");

ALTER TABLE "Hranenie" ADD CONSTRAINT "FK_N_Produkcia_Hranenie" FOREIGN KEY ("N_Produkcia") REFERENCES "Produkcia" ("Usl_nomer") ON UPDATE CASCADE;

CREATE TABLE "Klient" (

"Usl_nomer" INTEGER NOT NULL,

"Name" FIO COLLATE PXW_CYRL,

"Adres" "Adress" COLLATE PXW_CYRL,

"Uvolen" "Bool" NOT NULL);

ALTER TABLE "Klient" ADD CONSTRAINT "FK_Klient" PRIMARY KEY ("Usl_nomer");

CREATE TABLE "Lesobilet" (

"Numer" "Nomer",

"Data" DATE NOT NULL,

"N_Zav_Delankami" INTEGER NOT NULL);

ALTER TABLE "Lesobilet" ADD CONSTRAINT "PK_Lesobilet" PRIMARY KEY ("Numer");

ALTER TABLE "Lesobilet" ADD CONSTRAINT "FK_N_Zav_Del_Lesobilet" FOREIGN KEY ("N_Zav_Delankami") REFERENCES "Rabotnik" ("Usl_nomer") ON UPDATE CASCADE;

CREATE TABLE "Naklad_Lesnichestvo" (

"Numer" "Nomer",

"Data" DATE NOT NULL,

"N_Zav_Delankami" INTEGER NOT NULL);

ALTER TABLE "Naklad_Lesnichestvo" ADD CONSTRAINT "PK_Naklad_Lesnichestvo" PRIMARY KEY ("Numer");

ALTER TABLE "Naklad_Lesnichestvo" ADD CONSTRAINT "FK_N_Zav_Del_Naklad_Lesn" FOREIGN KEY ("N_Zav_Delankami") REFERENCES "Rabotnik" ("Usl_nomer") ON UPDATE CASCADE;

CREATE TABLE "Naklad_Prodaga" (

"Data" DATE NOT NULL,

"N_Firma" INTEGER NOT NULL,

"Numer" "Nomer" NOT NULL,

"N_Zav_Skladom" INTEGER NOT NULL);

ALTER TABLE "Naklad_Prodaga" ADD CONSTRAINT "PK_Naklad_Prodaga" PRIMARY KEY ("Numer");

ALTER TABLE "Naklad_Prodaga" ADD CONSTRAINT "FK_N_Firma_Nakl_Prodaga" FOREIGN KEY ("N_Firma") REFERENCES "Klient" ("Usl_nomer") ON UPDATE CASCADE;

ALTER TABLE "Naklad_Prodaga" ADD CONSTRAINT "FK_N_Zav_Skl_Naklad_Prodaga" FOREIGN KEY ("N_Zav_Skladom") REFERENCES "Rabotnik" ("Usl_nomer") ON UPDATE CASCADE;

CREATE TABLE "Naklad_Zakaz" (

"Numer" "Nomer",

"Data" DATE NOT NULL,

"Srok" INTEGER NOT NULL,

"N_Firma" INTEGER NOT NULL,

"N_Nach_Sbita" INTEGER NOT NULL);

ALTER TABLE "Naklad_Zakaz" ADD CONSTRAINT "FK_Naklad_Zakaz" PRIMARY KEY ("Numer");

ALTER TABLE "Naklad_Zakaz" ADD CONSTRAINT "FK_N_Firma_Nakl_Zakaz" FOREIGN KEY ("N_Firma") REFERENCES "Klient" ("Usl_nomer") ON UPDATE CASCADE;

ALTER TABLE "Naklad_Zakaz" ADD CONSTRAINT "FK_N_Nach_Sbita_Nakl_Zakaz" FOREIGN KEY ("N_Nach_Sbita") REFERENCES "Rabotnik" ("Usl_nomer") ON UPDATE CASCADE;

CREATE TABLE "Nalichie" (

"N_Brigadir" INTEGER,

"N_Nach_Baza" INTEGER,

"N_Drevesina" INTEGER NOT NULL,

"Kolvo" INTEGER NOT NULL);

ALTER TABLE "Nalichie" ADD CONSTRAINT "FK_N_Brigadir" FOREIGN KEY ("N_Brigadir") REFERENCES "Rabotnik" ("Usl_nomer") ON UPDATE CASCADE;

ALTER TABLE "Nalichie" ADD CONSTRAINT "FK_N_Drevesina_Nalichie" FOREIGN KEY ("N_Drevesina") REFERENCES "Drevesina" ("Usl_nomer") ON UPDATE CASCADE;

ALTER TABLE "Nalichie" ADD CONSTRAINT "FK_N_Nach_Bazi" FOREIGN KEY ("N_Nach_Baza") REFERENCES "Rabotnik" ("Usl_nomer") ON UPDATE CASCADE;

CREATE TABLE "Nalichie_Drevesina" (

"N_Drevesina" INTEGER NOT NULL,

"N_Delanka" INTEGER NOT NULL,

"Kolvo" DOUBLE PRECISION NOT NULL);

ALTER TABLE "Nalichie_Drevesina" ADD CONSTRAINT "FK_Nalichie_Drevesina" PRIMARY KEY ("N_Drevesina", "N_Delanka");

ALTER TABLE "Nalichie_Drevesina" ADD CONSTRAINT "FK_N_Delanka_Nal_Drev" FOREIGN KEY ("N_Delanka") REFERENCES "Delanka" ("Numer") ON UPDATE CASCADE;

ALTER TABLE "Nalichie_Drevesina" ADD CONSTRAINT "FK_N_Drevesina_Nal_Drev" FOREIGN KEY ("N_Drevesina") REFERENCES "Drevesina" ("Usl_nomer") ON UPDATE CASCADE;

CREATE TABLE "Objem" (

"N_Brigadir" INTEGER NOT NULL,

"N_Produkcia" INTEGER NOT NULL,

"Kolvo" DOUBLE PRECISION NOT NULL);

ALTER TABLE "Objem" ADD CONSTRAINT "FK_Objem" PRIMARY KEY ("N_Brigadir", "N_Produkcia");

ALTER TABLE "Objem" ADD CONSTRAINT "FK_N_Brigadir_Objem" FOREIGN KEY ("N_Brigadir") REFERENCES "Rabotnik" ("Usl_nomer") ON UPDATE CASCADE;

ALTER TABLE "Objem" ADD CONSTRAINT "FK_N_Produkcia_Objem" FOREIGN KEY ("N_Produkcia") REFERENCES "Produkcia" ("Usl_nomer") ON UPDATE CASCADE;

CREATE TABLE "Plan" (

"Numer" "Nomer",

"Data" DATE NOT NULL,

"Srok" INTEGER NOT NULL,

"N_Brigada" INTEGER NOT NULL,

"Objem_Drevesini" DOUBLE PRECISION NOT NULL,

"Isp" SMALLINT DEFAULT 0 NOT NULL,

"N_Nach_Sbita" INTEGER NOT NULL);

ALTER TABLE "Plan" ADD CONSTRAINT "FK_Plan" PRIMARY KEY ("Numer");

ALTER TABLE "Plan" ADD CONSTRAINT "FK_N_Brigada_Plan" FOREIGN KEY ("N_Brigada") REFERENCES "Brigada" ("Usl_nomer") ON UPDATE CASCADE;

ALTER TABLE "Plan" ADD CONSTRAINT "FK_N_Nach_Sbita_Plan" FOREIGN KEY ("N_Nach_Sbita") REFERENCES "Rabotnik" ("Usl_nomer") ON UPDATE CASCADE;

CREATE TABLE "Produkcia" (

"Usl_nomer" INTEGER NOT NULL,

"Name" FIO,

"Cena" DOUBLE PRECISION NOT NULL,

"Ed_Izm" VARCHAR(10) NOT NULL,

"Old" SMALLINT DEFAULT 0 NOT NULL);

ALTER TABLE "Produkcia" ADD CONSTRAINT "FK_Produkcia" PRIMARY KEY ("Usl_nomer");

CREATE TABLE "Rabotnik" (

"Usl_nomer" INTEGER NOT NULL,

"Tab_numer" "Tab_nomer",

"R_Fio" FIO COLLATE PXW_CYRL,

"Adres" "Adress" COLLATE PXW_CYRL,

"Data_Postup" DATE NOT NULL,

"Data_Nazn" DATE,

"Dolgnost" INTEGER,

"N_Brigadi" INTEGER,

"Uvolen" "Bool" NOT NULL);

ALTER TABLE "Rabotnik" ADD CONSTRAINT "FK_Rabotnik" PRIMARY KEY ("Usl_nomer");

ALTER TABLE "Rabotnik" ADD CONSTRAINT "FK_N_Brigada" FOREIGN KEY ("N_Brigadi") REFERENCES "Brigada" ("Usl_nomer") ON UPDATE CASCADE;

ALTER TABLE "Rabotnik" ADD CONSTRAINT "FK_N_Dolgnost" FOREIGN KEY ("Dolgnost") REFERENCES "Dolgnost" ("N_Dolgn") ON UPDATE CASCADE;

CREATE TABLE "Stroka_Lesn" (

"N_Naklad_Lesn" INTEGER NOT NULL,

"N_Delanka" INTEGER NOT NULL);

ALTER TABLE "Stroka_Lesn" ADD CONSTRAINT "FK_Stroka_Lesn" PRIMARY KEY ("N_Naklad_Lesn", "N_Delanka");

ALTER TABLE "Stroka_Lesn" ADD CONSTRAINT "FK_N_Delanka_Stroka_Lesn" FOREIGN KEY ("N_Delanka") REFERENCES "Delanka" ("Numer") ON UPDATE CASCADE;

ALTER TABLE "Stroka_Lesn" ADD CONSTRAINT "FK_N_Nakl_Lesn_Stroka_Lesn" FOREIGN KEY ("N_Naklad_Lesn") REFERENCES "Naklad_Lesnichestvo" ("Numer") ON UPDATE CASCADE;

CREATE TABLE "Stroka_Lesobilet" (

"N_Lesobilet" INTEGER NOT NULL,

"N_Delanka" INTEGER NOT NULL);

ALTER TABLE "Stroka_Lesobilet" ADD CONSTRAINT "FK_Stroka_Lesobilet" PRIMARY KEY ("N_Lesobilet", "N_Delanka");

ALTER TABLE "Stroka_Lesobilet" ADD CONSTRAINT "FK_N_Delanka_Str_Bilet" FOREIGN KEY ("N_Delanka") REFERENCES "Delanka" ("Numer") ON UPDATE CASCADE;

ALTER TABLE "Stroka_Lesobilet" ADD CONSTRAINT "FK_N_Lesobilet_Str_Bilet" FOREIGN KEY ("N_Lesobilet") REFERENCES "Lesobilet" ("Numer") ON UPDATE CASCADE;

CREATE TABLE "Stroka_Plana" (

"N_Plan" INTEGER NOT NULL,

"N_Produkcia" INTEGER NOT NULL,

"Kolvo" DOUBLE PRECISION NOT NULL);

ALTER TABLE "Stroka_Plana" ADD CONSTRAINT "FK_Stroka_Plana" PRIMARY KEY ("N_Plan", "N_Produkcia");

ALTER TABLE "Stroka_Plana" ADD CONSTRAINT "FK_N_Plan_Stroka_Plana" FOREIGN KEY ("N_Plan") REFERENCES "Plan" ("Numer") ON UPDATE CASCADE;

ALTER TABLE "Stroka_Plana" ADD CONSTRAINT "FK_N_Produkcia_Stroka_Plana" FOREIGN KEY ("N_Produkcia") REFERENCES "Produkcia" ("Usl_nomer") ON UPDATE CASCADE;

CREATE TABLE "Stroka_Prodaga" (

"N_Naklad_Prodaga" INTEGER NOT NULL,

"N_Produkcia" INTEGER NOT NULL,

"Kolvo" DOUBLE PRECISION NOT NULL);

ALTER TABLE "Stroka_Prodaga" ADD CONSTRAINT "FK_Stroka_Prodaga" PRIMARY KEY ("N_Naklad_Prodaga", "N_Produkcia");

ALTER TABLE "Stroka_Prodaga" ADD CONSTRAINT "FK_N_Produkcia_Str_Prod" FOREIGN KEY ("N_Produkcia") REFERENCES "Produkcia" ("Usl_nomer") ON UPDATE CASCADE;

CREATE TABLE "Stroka_Zakaz" (

"N_Naklad_Zakaz" INTEGER NOT NULL,

"N_Produkcia" INTEGER NOT NULL,

"Kolvo" DOUBLE PRECISION NOT NULL);

ALTER TABLE "Stroka_Zakaz" ADD CONSTRAINT "FK_Stroka_Zakaz" PRIMARY KEY ("N_Naklad_Zakaz", "N_Produkcia");

ALTER TABLE "Stroka_Zakaz" ADD CONSTRAINT "FK_N_Nakl_Z_Stroka_Zakaz" FOREIGN KEY ("N_Naklad_Zakaz") REFERENCES "Naklad_Zakaz" ("Numer") ON UPDATE CASCADE;

ALTER TABLE "Stroka_Zakaz" ADD CONSTRAINT "FK_N_Produkcia_Stroka_Zakaz" FOREIGN KEY ("N_Produkcia") REFERENCES "rodukcia" ("Usl_nomer") ON UPDATE CASCADE;

CREATE TABLE "Telefon" (

"Nomer" "Nomer_Tlf" NOT NULL,

"N_Firma" INTEGER,

"N_Rabotnik" INTEGER);

ALTER TABLE "Telefon" ADD CONSTRAINT "PK_Telefon" PRIMARY KEY ("Nomer");

ALTER TABLE "Telefon" ADD CONSTRAINT "FK_N_Rabotnik_Tlf" FOREIGN KEY ("N_Rabotnik") REFERENCES "Rabotnik" ("Usl_nomer") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "Telefon" ADD CONSTRAINT "FK_N_firma_Tlf" FOREIGN KEY ("N_Firma") REFERENCES "Klient" ("Usl_nomer") ON DELETE CASCADE ON UPDATE CASCADE;

3 Создание вторичных индексов

Для повышения производительности в таблицах «Работник», «Продукция», «Клиент» были созданы вторичные индексы.

Скрипты создания вторичных индексов

CREATE INDEX "Rabotnik_IDX1" ON "Rabotnik" ("R_Fio");

CREATE INDEX "Rabotnik_IDX2" ON "Rabotnik" ("Adres");

CREATE INDEX "Produkcia_IDX1" ON "Produkcia" ("Name");

CREATE INDEX "Produkcia_IDX2" ON "Produkcia" ("Cena");

CREATE UNIQUE INDEX "Klient_IDX1" ON "Klient" ("Name");

CREATE INDEX "Klient_IDX2" ON "Klient" ("Adres");

4 Права доступа

Транзакции 1,2 выполняет отдел кадров

Транзакции 3-6 - зав. делянками

Транзакции 9,7 - нач. базы

Транзакции 8,11,12 - нач. сбыта

Транзакции 10,13 - зав. складом

Приведем примеры таблиц и хранимых процедур.

Листинг процедуры добавления накладной на заказ товара

procedure TDob_Nakl_Zakaz.Button1Click(Sender: TObject);

var i,j:Integer;

Ok:Boolean;

begin

Ok:=False;

if(not DM4.IBTrans_Write.Active)

then DM4.IBTrans_Write.StartTransaction;

j:=0;

if((StringReplace(MaskEdit1.EditText,' ','',[rfReplaceAll])<>'') and

(StringReplace(MaskEdit2.EditText,' ','',[rfReplaceAll])<>''))

then

begin

DM4.IBSP_Dob_Nakl_Z.ParamByName('Name').AsString:=

ComboBox1.Text;

DM4.IBSP_Dob_Nakl_Z.ParamByName('Adres').AsString:=

Edit2.Text;

DM4.IBSP_Dob_Nakl_Z.ParamByName('Nomer').AsInteger:=

StrToInt(StringReplace(MaskEdit1.Text,' ','',[rfReplaceAll]));

DM4.IBSP_Dob_Nakl_Z.ParamByName('Srok').AsInteger:=

StrToInt(StringReplace(MaskEdit2.Text,' ','',[rfReplaceAll]));

DM4.IBSP_Dob_Nakl_Z.ParamByName('Data').AsDate:=

DateTimePicker1.Date;

try

DM4.IBSP_Dob_Nakl_Z.Prepare;

DM4.IBSP_Dob_Nakl_Z.ExecProc;

Ok:=True;

if(Ok) then

begin

for i:=1 to ValueListEditor1.RowCount-1 do

begin

if(StringReplace(StringReplace(ValueListEditor1.Values[ValueListEditor1.Keys[i]],' ','',[rfReplaceAll]),'.',',',[rfReplaceAll])<>'')

then

begin

DM4.IBSP_Dob_Str_Nakl_Z.ParamByName('NNakl_Zakaz').AsInteger:=

StrToInt(StringReplace(MaskEdit1.EditText,' ','',[rfReplaceAll]));

DM4.IBSP_Dob_Str_Nakl_Z.ParamByName('Naimen').AsString:=

ValueListEditor1.Keys[i];

DM4.IBSP_Dob_Str_Nakl_Z.ParamByName('Kolvo').AsFloat:=

StrToFloat(StringReplace(StringReplace(ValueListEditor1.Values[ValueListEditor1.Keys[i]],' ','',[rfReplaceAll]),'.',',',[rfReplaceAll]));

DM4.IBSP_Dob_Str_Nakl_Z.ParamByName('Cena').AsFloat:=

StrToFloat(ListBox1.Items[i-1]);

DM4.IBSP_Dob_Str_Nakl_Z.Prepare;

DM4.IBSP_Dob_Str_Nakl_Z.ExecProc;

j:=j+1;

end;

end

end;

if((Ok) and (j<>0))

then

DM4.IBTrans_Write.Commit

else ShowMessage('Ошибка добавления накладной !');

except

on E: Exception do

begin

if(Pos('Накладная с таким номером уже есть !!!',E.Message)<>0) then

ShowMessage(' Накладная с таким номером уже есть !')

else

ShowMessage('Ошибка БД');

DM4.IBTrans_Write.Rollback;

end;

end;

end

else ShowMessage('Введите номер накладной или срок выполнения !');

end;

Листинг процедуры добавления лесобилета

procedure TDob_Lesobilet.Button1Click(Sender: TObject);

var i,j:Integer;

begin

if(not DM1.IBTrans_Write.Active)

then DM1.IBTrans_Write.StartTransaction;

j:=0;

try

if(Kol_Del=0) then

begin

if(Trim(MaskEdit1.EditText)<>'') then

begin

DM1.IBSP_Dob_Lesobilet.ParamByName('Nomer').AsInteger:=

StrToInt(Trim(MaskEdit1.EditText));

DM1.IBSP_Dob_Lesobilet.ParamByName('Data').AsDate:=

DateTimePicker1.Date;

DM1.IBSP_Dob_Lesobilet.Prepare;

DM1.IBSP_Dob_Lesobilet.ExecProc;

Ok:=True;

MaskEdit1.ReadOnly:=True;

Kol_Del:=Kol_Del+1;

end

else

begin

ShowMessage('Введите номер лесобилета !');

Ok:=False;

end

end;

if((Ok) and (Trim(MaskEdit2.EditText)<>'') and

(Trim(MaskEdit3.EditText)<>'*') and (Trim(MaskEdit4.EditText)<>','))

then

begin

DM1.IBSP_Dob_Delanki.ParamByName('N_Delanka').AsInteger:=

StrToInt(Trim(MaskEdit2.EditText));

DM1.IBSP_Dob_Delanki.ParamByName('Kvadrat').AsString:=

Trim(MaskEdit3.EditText);

DM1.IBSP_Dob_Delanki.ParamByName('Plotschad').AsFloat:=

StrToFloat(Trim(MaskEdit4.EditText));

DM1.IBSP_Dob_Delanki.Prepare;

DM1.IBSP_Dob_Delanki.ExecProc;

DM1.IBSP_Dob_Str_Bilet.ParamByName('NLesobilet').AsInteger:=

StrToInt(Trim(MaskEdit1.EditText));

DM1.IBSP_Dob_Str_Bilet.ParamByName('NDelanka').AsInteger:=

StrToInt(Trim(MaskEdit2.EditText));

DM1.IBSP_Dob_Str_Bilet.Prepare;

DM1.IBSP_Dob_Str_Bilet.ExecProc;

for i:=1 to ValueListEditor1.RowCount-1 do

begin

if(ValueListEditor1.Values[ValueListEditor1.Keys[i]]<>'') then

begin

if(StrToFloat(ValueListEditor1.Values[ValueListEditor1.Keys[i]])>0)

then

begin

DM1.IBSP_Dob_Nal_Drevesini.ParamByName('Kolvo').AsFloat:=

StrToFloat(ValueListEditor1.Values[ValueListEditor1.Keys[i]]);

DM1.IBSP_Dob_Nal_Drevesini.ParamByName('Poroda').AsString:=

ValueListEditor1.Keys[i];

DM1.IBSP_Dob_Nal_Drevesini.ParamByName('Nomer').AsInteger:=

StrToInt(Trim(MaskEdit2.EditText));

DM1.IBSP_Dob_Nal_Drevesini.Prepare;

DM1.IBSP_Dob_Nal_Drevesini.ExecProc;

end

end

else

j:=j+1;

end

end;

if((Ok) and (j<i)) then

DM1.IBTrans_Write.Commit;

except

on E: Exception do

begin

if(Pos('Лесобилет с таким номером уже существует !!!',E.Message)<>0) then

ShowMessage(' Лесобилет с таким номером уже существует !')

else if(Pos(‘Делянка с таким номером уже существует !!!',E.Message)<>0) then

ShowMessage(' Делянка с таким номером уже существует !')

else

ShowMessage('Ошибка БД !');

DM1.IBTrans_Write.Rollback;

end;

end

end;