Кроме того из любого состояния «2»-«13» возможен переход в любое из состояний «2»-«13», а так же переход в конечное состояние. На диаграмме эти переходы не отражены.

 

Заключение

 

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

Список литературы

 

1  Гордиенко А. П., Салина Н. В. Моделирование процессов предметной области с использованием BPwin: Методические указания к выполнению лабораторной работы по курсу "Проектирование информационных систем в экономике". - Орел: ОрелГТУ, 1997.

2  Диго С. М. Проектирование и использование баз данных: Учебник. М.: Финансы и статистика. 1995. - 280 с.

3  Змитрович А. И. Базы данных: Учеб.пособ. для вузов. - Минск: Университетское, 1991. - 271 с.

4  Базы данных: разработка и управление: М.: ЗАО «Издательство БИНОМ», 1999. - 704 с.: ил.

Приложение А

 

Реализация целостности базы данных

 

SET SQL DIALECT 3;

 

Domains ***/

DOMAIN MONEY AS(15,2);

 

****************************************************************************/

* Generators ***/

****************************************************************************/

GENERATOR G_CONTRAGENT;GENERATOR G_CONTRAGENT TO 0;

GENERATOR G_MATERIALS;GENERATOR G_MATERIALS TO 0;

GENERATOR G_MEASURE;GENERATOR G_MEASURE TO 0;

GENERATOR G_OKNO;GENERATOR G_OKNO TO 0;

GENERATOR G_PARTY;GENERATOR G_PARTY TO 0;

GENERATOR G_POSTAVKA;GENERATOR G_POSTAVKA TO 0;

GENERATOR G_PRICE_LIST;GENERATOR G_PRICE_LIST TO 0;

GENERATOR G_RASHOD_MT;GENERATOR G_RASHOD_MT TO 0;

GENERATOR G_WORK;GENERATOR G_WORK TO 0;

GENERATOR G_ZAKAZ;GENERATOR G_ZAKAZ TO 0;

 

****************************************************************************/

Tables ***/

****************************************************************************/

 

 

TABLE CONTRAGENT (

ID INTEGER NOT NULL,

NAME VARCHAR(30),

FULLNAME VARCHAR(100),

TYPE_AGENT INTEGER,

INN VARCHAR(15),

ADDRESS VARCHAR(150),

TEL VARCHAR(20),

EMAIL VARCHAR(20),

PASSPORT VARCHAR(200)

);

 

TABLE MATERIALS (

ID INTEGER NOT NULL,

NAME INTEGER,

MEASURE INTEGER

);

 

TABLE MEASURE (

ID INTEGER NOT NULL,

NAME VARCHAR(10)

);

 

TABLE OKNO (

ID INTEGER NOT NULL,

SZ_WIDTH NUMERIC(15,3),

SZ_HEIGHT NUMERIC(15,3),

SZ_THING NUMERIC(15,3),

SZ_STEKLO_W NUMERIC(15,3),

SZ_STEKLO_H NUMERIC(15,3),

SZ_STEKLO_T NUMERIC(15,3),

WORKS INTEGER

);

 

TABLE PARTY (

ID INTEGER NOT NULL,

MATERIAL INTEGER,

POSTAVKA INTEGER,

KOL NUMERIC(15,3),

PRICE MONEY,

IN_RESERVE SMALLINT

);

 

автоматизация база данные приложение предметный

CREATE TABLE POSTAVKA (

ID INTEGER NOT NULL,

DT_POSTAV DATE,

CONTRAGENT INTEGER,

NUM INTEGER,

PROVEDENO INTEGER,

TOTALPRICE MONEY

);

 

TABLE PRICE_LIST (

ID INTEGER NOT NULL,

NAME VARCHAR(15),

COST MONEY

);TABLE RASHOD_MT (

ID INTEGER NOT NULL,

MATERIALS INTEGER,

PRICELIST INTEGER

);

 

TABLE WORK_LIST (

ID INTEGER NOT NULL,

NAME INTEGER,

KOL INTEGER,

PRICE MONEY,

P_L INTEGER

);

 

TABLE ZAKAZ (

ID INTEGER NOT NULL,

CONTRAGENT INTEGER,

DT_ZAKAZ DATE,

DT_PROIZV_START DATE,

DT_PROIZV_FINISH DATE,

DT_OTGRUZ DATE,

SUMM MONEY,

COMMENT VARCHAR(100)

);

 

 

****************************************************************************/

Primary Keys ***/

****************************************************************************/

TABLE CONTRAGENT ADD CONSTRAINT PK_CONTRAGENT PRIMARY KEY (ID);TABLE MATERIALS ADD CONSTRAINT PK_MATERIALS PRIMARY KEY (ID);TABLE MEASURE ADD CONSTRAINT PK_MEASURE PRIMARY KEY (ID);TABLE OKNO ADD CONSTRAINT PK_OKNO PRIMARY KEY (ID);TABLE PARTY ADD CONSTRAINT PK_PARTY PRIMARY KEY (ID);TABLE POSTAVKA ADD CONSTRAINT PK_POSTAVKA PRIMARY KEY (ID);TABLE PRICE_LIST ADD CONSTRAINT PK_PRICE_LIST PRIMARY KEY (ID);TABLE RASHOD_MT ADD CONSTRAINT PK_RASHOD_MT PRIMARY KEY (ID);TABLE WORK_LIST ADD CONSTRAINT PK_WORK_LIST PRIMARY KEY (ID);TABLE ZAKAZ ADD CONSTRAINT PK_ZAKAZ PRIMARY KEY (ID);

 

****************************************************************************/

* Foreign Keys ***/

****************************************************************************/TABLE MATERIALS ADD CONSTRAINT FK_MATERIALS_MEASURE FOREIGN KEY (MEASURE) REFERENCES MEASURE (ID);TABLE OKNO ADD CONSTRAINT FK_OKNO_WORK FOREIGN KEY (WORKS) REFERENCES WORK_LIST (ID);TABLE PARTY ADD CONSTRAINT FK_PARTY_MATERIAL FOREIGN KEY (MATERIAL) REFERENCES MATERIALS (ID);TABLE PARTY ADD CONSTRAINT FK_PARTY_POSTAVKA FOREIGN KEY (POSTAVKA) REFERENCES POSTAVKA (ID);TABLE POSTAVKA ADD CONSTRAINT FK_POSTAVKA_CONTRAGENT FOREIGN KEY (CONTRAGENT) REFERENCES CONTRAGENT (ID);TABLE RASHOD_MT ADD CONSTRAINT FK_RASHOD_MT_MATERIALS FOREIGN KEY (MATERIALS) REFERENCES MATERIALS (ID);TABLE RASHOD_MT ADD CONSTRAINT FK_RASHOD_MT_PRICELIST FOREIGN KEY (PRICELIST) REFERENCES PRICE_LIST (ID);TABLE WORK_LIST ADD CONSTRAINT FK_WORK_LIST_PRICELIST FOREIGN KEY (P_L) REFERENCES PRICE_LIST (ID);TABLE ZAKAZ ADD CONSTRAINT FK_ZAKAZ_CONTRAGENT FOREIGN KEY (CONTRAGENT) REFERENCES CONTRAGENT (ID);

 

 

****************************************************************************/

* Triggers ***/

****************************************************************************/

 

TERM ^ ;

 

****************************************************************************/

Triggers for tables ***/

****************************************************************************/

 

/* Trigger: CONTRAGENT_BI */TRIGGER CONTRAGENT_BI FOR CONTRAGENTBEFORE INSERT POSITION 0

if (new.id is null) then

new.id = gen_id(g_contragent,1);

^

 

 

/* Trigger: MATERIALS_BI */TRIGGER MATERIALS_BI FOR MATERIALSBEFORE INSERT POSITION 0

if (new.id is null) then

new.id = gen_id(g_materials,1);

^

 

 

/* Trigger: MEASURE_BI */TRIGGER MEASURE_BI FOR MEASUREBEFORE INSERT POSITION 0

if (new.id is null) then

new.id = gen_id(g_measure,1);

^

 

 

/* Trigger: OKNO_BI */TRIGGER OKNO_BI FOR OKNOBEFORE INSERT POSITION 0

if (new.id is null) then

new.id = gen_id(g_okno,1);

^

 

 

/* Trigger: PARTY_BI */TRIGGER PARTY_BI FOR PARTYBEFORE INSERT POSITION 0

if (new.id is null) then

new.id = gen_id(g_party,1);

^

 

 

/* Trigger: POSTAVKA_BI */TRIGGER POSTAVKA_BI FOR POSTAVKABEFORE INSERT POSITION 0

if (new.id is null) then

new.id = gen_id(g_postavka,1);

^

 

 

/* Trigger: PRICE_LIST_BI */TRIGGER PRICE_LIST_BI FOR PRICE_LISTBEFORE INSERT POSITION 0

IF (NEW.ID IS NULL) THEN

NEW.ID = GEN_ID(G_PRICE_LIST,1);

^

/* Trigger: RASHOD_MT_BI */TRIGGER RASHOD_MT_BI FOR RASHOD_MTBEFORE INSERT POSITION 0

if (new.id is null) then

new.id = gen_id(g_rashod_mt,1);

^

 

 

/* Trigger: WORK_BI */TRIGGER WORK_BI FOR WORK_LISTBEFORE INSERT POSITION 0

IF (NEW.ID IS NULL) THEN

NEW.ID = GEN_ID(G_WORK,1);

^

 

 

/* Trigger: ZAKAZ_BI */TRIGGER ZAKAZ_BI FOR ZAKAZBEFORE INSERT POSITION 0

if (new.id is null) then

new.id = gen_id(g_zakaz,1);

end

^

 

TERM ; ^

 

Приложение Б

 

Описание прав пользователей

 

grant insert on contractor to insert_into_contractor^insert on person to insert_into_contractor^update on contractor to update_contractor^update on person to update_contractor^

select on consignment to DeliveryAcceptAll^select on delivery to DeliveryAcceptAll^update on delivery to DeliveryAcceptAll^

 

role pr_suppl^

all on delivery to pr_suppl^all on consignment to pr_suppl^

all on contractor to pr_suppl^all on person to pr_suppl^all on contractor_group to pr_suppl^

select on dublicate to pr_suppl^select on dublicate_jobs to pr_suppl^

select on form to pr_suppl^select on form_group to pr_suppl^

all on material_group to pr_suppl^all on material to pr_suppl^all on measurement_unit to pr_suppl^

select on material_consumption to pr_suppl^select on price_list to pr_suppl^select on production_order to pr_suppl^

execute on procedure DeliveryAcceptAll to pr_suppl^execute on procedure insert_into_contractor to pr_suppl^execute on procedure update_contractor to pr_suppl^

 

role pr_tech^

select on delivery to pr_tech^select on consignment to pr_tech^

select on contractor to pr_tech^select on contractor_group to pr_tech^select on person to pr_tech^

all on dublicate to pr_tech^all on dublicate_jobs to pr_tech^

all on form to pr_tech^all on form_group to pr_tech^

select on material_group to pr_tech^select on material to pr_tech^select on measurement_unit to pr_tech^all on material_consumption to pr_tech^select on price_list to pr_tech^select on production_order to pr_tech^

 

 

role pr_order^

select on delivery to pr_order^select on consignment to pr_order^

all on contractor to pr_order^all on person to pr_order^all on contractor_group to pr_order^

all on dublicate to pr_order^all on dublicate_jobs to pr_order^

select on form to pr_order^select on form_group to pr_order^

select on material_group to pr_order^select on material to pr_order^select on measurement_unit to pr_order^select on material_consumption to pr_order^select on price_list to pr_order^all on production_order to pr_order^execute on procedure insert_into_contractor to pr_order^execute on procedure update_contractor to pr_order^

role pr_dir^

select on delivery to pr_dir^select on consignment to pr_dir^

select on contractor to pr_dir^select on contractor_group to pr_dir^select on person to pr_dir^

select on dublicate to pr_dir^select on dublicate_jobs to pr_dir^

select on form to pr_dir^select on form_group to pr_dir^

select on material_group to pr_dir^select on material to pr_dir^select on measurement_unit to pr_dir^

select on material_consumption to pr_dir^all on price_list to pr_dir^select on production_order to pr_dir^

 

pr_suppl to pu_suppl^pr_tech to pu_tech^pr_order to pu_order^pr_dir to pu_dir^