Подразделения

Работники

Протокол работы

Электронные документы

Архив удаленных документов

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

Таблица «Работники»: поле «Имя пользователя»

Таблица «Электронные документы»: поле («Наименование» + «№ по классификатору»)

Доступ:

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

5. Физическая реализация

Серверная часть

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

/** Generated by IBExpert 2004.01.22 23.05.2004 20:38:17 ****/

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

SET SQL DIALECT 3;

SET NAMES WIN1251;

CREATE DATABASE 'Document:C:\Program Files\Borland\InterBase\bin\ELECTRDOC.GDB'

USER 'SYSDBA' PASSWORD 'administrator'

PAGE_SIZE 1024

DEFAULT CHARACTER SET WIN1251;

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

/**** Generators ****/

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

CREATE GENERATOR ARHIVN;

SET GENERATOR ARHIVN TO 16;

CREATE GENERATOR DOCN;

SET GENERATOR DOCN TO 17;

CREATE GENERATOR PODRAZDN;

SET GENERATOR PODRAZDN TO 4;

CREATE GENERATOR PROTOCOLN;

SET GENERATOR PROTOCOLN TO 52;

CREATE GENERATOR PROVERKIN;

SET GENERATOR PROVERKIN TO 13;

CREATE GENERATOR RABN;

SET GENERATOR RABN TO 19;

/*************************************************************//**** Exceptions ****/

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

CREATE EXCEPTION NODELETE 'Нельзя удалить данного работника';

CREATE EXCEPTION NOLOGIN 'Имя пользователя должно быть уникальным';

SET TERM ^ ;

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

/**** Stored Procedures ****/

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

CREATE PROCEDURE ADD_DOCUMENT (

NKLASS VARCHAR(7),

TEMA VARCHAR(30),

DATA DATE,

VID VARCHAR(15),

NAME VARCHAR(70))

AS

BEGIN

EXIT;

END^

CREATE PROCEDURE ADDDIRECTOR (

DATA DATE,

LOGIN VARCHAR(20),

FAMILY VARCHAR(20))

AS

BEGIN

EXIT;

END^

CREATE PROCEDURE ADDPODRAZDELENIE (

NAZV VARCHAR(70),

FIO VARCHAR(20))

AS

BEGIN

EXIT;

END^

CREATE PROCEDURE ADDPROTOCOL (

NUMDOC INTEGER)

AS

BEGIN

EXIT;

END^

CREATE PROCEDURE ADDPROVERKI (

NAZVPODR VARCHAR(70),

FIO VARCHAR(20),

OPISANIE VARCHAR(1000),

VID VARCHAR(15),

DATA DATE)

AS

BEGIN

EXIT;

END^

CREATE PROCEDURE CLEARARHIV

AS

BEGIN

EXIT;

END^

CREATE PROCEDURE CLEARPROTOCOL

AS

BEGIN

EXIT;

END^

CREATE PROCEDURE DELETEDIRECTOR (

FIO VARCHAR(20))

AS

BEGIN

EXIT;

END^

CREATE PROCEDURE DELETEDOC (

NUM INTEGER)

AS

BEGIN

EXIT;

END^

CREATE PROCEDURE DELETEPODRAZD (

NOMER INTEGER)

AS

BEGIN

EXIT;

END^

CREATE PROCEDURE DELETEPROVERKA (

NPROVERKI INTEGER)

AS

BEGIN

EXIT;

END^

CREATE PROCEDURE DELETERABOTNIK (

NUM INTEGER)

AS

BEGIN

EXIT;

END^

CREATE PROCEDURE EDITPROVERKI (

OPISANIE VARCHAR(1000),

NPROV INTEGER,

VID VARCHAR(15),

NAZVPODR VARCHAR(70),

FIO VARCHAR(20),

DATA DATE)

AS

BEGIN

EXIT;

END^

CREATE PROCEDURE NEW_PROCEDURE (

NAZV VARCHAR(70),

LOGIN VARCHAR(20),

FIO VARCHAR(20))

AS

BEGIN

EXIT;

END^

CREATE PROCEDURE UPDATEDOCUMENT (

IZMEN VARCHAR(30),

TEMA VARCHAR(30),

NUM INTEGER)

AS

BEGIN

EXIT;

END^

SET TERM ; ^/****************************************************************/

/**** Tables ****/

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

CREATE TABLE ARHIVDOC (

NDOC INTEGER NOT NULL,

NKLASS VARCHAR(7) NOT NULL,

NAIMENDOC VARCHAR(70) NOT NULL,

VIDDOC VARCHAR(15) NOT NULL,

DATAPRINYATIYA DATE NOT NULL,

DATADELETE DATE NOT NULL,

TEMATIKA VARCHAR(30),

IZMENENIYA VARCHAR(30)

);

CREATE TABLE DIRECTOR (

FIO VARCHAR(20) NOT NULL,

LOGIN VARCHAR(20) NOT NULL,

DATAVSTUPLENIYA DATE NOT NULL

);

CREATE TABLE DOCUMENT (

NDOC INTEGER NOT NULL,

NKLASS VARCHAR(7) NOT NULL,

NAIMENDOC VARCHAR(70) NOT NULL,

VIDDOC VARCHAR(15) NOT NULL,

DATAPRINYATIYA DATE NOT NULL,

DATAIZMEN DATE,

STATUS VARCHAR(10),

TEMATIKA VARCHAR(30),

IZMENENIYA VARCHAR(30)

);

CREATE TABLE PODRAZD (

NPODR INTEGER NOT NULL,

FIOPODR VARCHAR(20) NOT NULL,

NAZVANIE VARCHAR(70) NOT NULL

);

CREATE TABLE PROTOCOL (

NUMBER INTEGER NOT NULL,

DATA DATE NOT NULL,

NRAB INTEGER,

NDOC INTEGER,

VREMYA TIME NOT NULL

);

CREATE TABLE PROVERKI (

NPROVERKI INTEGER NOT NULL,

DATA DATE NOT NULL,

FIO VARCHAR(20) NOT NULL,

NPODR INTEGER NOT NULL,

VID VARCHAR(15) ,

OPISANIE VARCHAR(1000)

);

CREATE TABLE RABOTNIK (

NUMBER INTEGER NOT NULL,

FIO VARCHAR(20) NOT NULL,

LOGIN VARCHAR(20) NOT NULL,

NPODR INTEGER NOT NULL);/**************************************************************/

/**** Unique Constraints ****/

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

ALTER TABLE DOCUMENT ADD CONSTRAINT UNQ_DOCUMENT UNIQUE (NKLASS, NAIMENDOC);

ALTER TABLE RABOTNIK ADD CONSTRAINT UNQ_RAB UNIQUE (LOGIN);

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

/**** Primary Keys ****/

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

ALTER TABLE ARHIVDOC ADD CONSTRAINT PK_ARHIVDOC PRIMARY KEY (NDOC);

ALTER TABLE DIRECTOR ADD CONSTRAINT PK_DIRECTOR PRIMARY KEY (FIO);

ALTER TABLE DOCUMENT ADD CONSTRAINT PK_DOCUMENT PRIMARY KEY (NDOC);

ALTER TABLE PODRAZD ADD CONSTRAINT PK_PODRAZD PRIMARY KEY (NPODR);

ALTER TABLE PROTOCOL ADD CONSTRAINT PK_PROTOCOL PRIMARY KEY (NUMBER);

ALTER TABLE PROVERKI ADD CONSTRAINT PK_PROVERKI PRIMARY KEY (NPROVERKI);

ALTER TABLE RABOTNIK ADD CONSTRAINT PK_RABOTNIK PRIMARY KEY (NUMBER);

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

/**** Foreign Keys ****/

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

ALTER TABLE PROTOCOL ADD CONSTRAINT FK_PROTOCOL_DOC FOREIGN KEY (NDOC) REFERENCES DOCUMENT (NDOC) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE PROTOCOL ADD CONSTRAINT FK_PROTOCOL_RAB FOREIGN KEY (NRAB) REFERENCES RABOTNIK (NUMBER) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE PROVERKI ADD CONSTRAINT FK_PROV_DIR FOREIGN KEY (FIO) REFERENCES DIRECTOR (FIO) ON DELETE NO ACTION ON UPDATE CASCADE;

ALTER TABLE PROVERKI ADD CONSTRAINT FK_PROV_PODRAZD FOREIGN KEY (NPODR) REFERENCES PODRAZD (NPODR) ON DELETE NO ACTION ON UPDATE CASCADE;

ALTER TABLE RABOTNIK ADD CONSTRAINT FK_RAB_PODR FOREIGN KEY (NPODR) REFERENCES PODRAZD (NPODR) ON DELETE NO ACTION ON UPDATE CASCADE;

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

/**** Triggers ****/

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

SET TERM ^ ;

/* Trigger: BI_ARHIV */

CREATE TRIGGER BI_ARHIV FOR ARHIVDOC

ACTIVE BEFORE INSERT POSITION 0

AS

begin

/* Trigger text */

NEW.ndoc= GEN_ID(arhivn,1);

end

^

/* Trigger: BI_DOCUMRNT */

CREATE TRIGGER BI_DOCUMRNT FOR DOCUMENT

ACTIVE BEFORE INSERT POSITION 0

AS

begin

/* Trigger text */

Страницы: 1, 2, 3, 4, 5