/******************************************************************************/
/***         Generated by IBExpert 2017.10.11.1 29.10.2017 2:40:48          ***/
/******************************************************************************/

SET SQL DIALECT 3;

SET NAMES WIN1251;

SET CLIENTLIB 'C:\Program Files\Firebird\Firebird_2_5\WOW64\fbclient.dll';

CREATE DATABASE 'C:\Users\annas\Desktop\Вуз\Бакалавриат З\Дисциплины\Управление данными\Лабараторные\Лабараторная 5\П5.FDB'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 16384
DEFAULT CHARACTER SET WIN1251 COLLATION WIN1251;



/******************************************************************************/
/***                                Domains                                 ***/
/******************************************************************************/

CREATE DOMAIN B AS
NUMERIC(10,10)
NOT NULL;

CREATE DOMAIN ESKIZ AS
BLOB SUB_TYPE 0 SEGMENT SIZE 80
NOT NULL;

CREATE DOMAIN ESKIZ1 AS
BLOB SUB_TYPE 0 SEGMENT SIZE 80
NOT NULL;

CREATE DOMAIN GPT AS
SMALLINT
NOT NULL;

CREATE DOMAIN HEL AS
NUMERIC(10,10)
NOT NULL;

CREATE DOMAIN HOP AS
NUMERIC(10,10)
NOT NULL;

CREATE DOMAIN HRABOCHAYA AS
NUMERIC(10,10)
NOT NULL;

CREATE DOMAIN HZAPAS AS
NUMERIC(10,10)
NOT NULL;

CREATE DOMAIN K AS
SMALLINT
NOT NULL;

CREATE DOMAIN K1 AS
SMALLINT
NOT NULL;

CREATE DOMAIN KO AS
NUMERIC(10,10)
NOT NULL;

CREATE DOMAIN L AS
NUMERIC(10,10)
NOT NULL;

CREATE DOMAIN M1 AS
NUMERIC(10,10)
NOT NULL;

CREATE DOMAIN MARKA AS
VARCHAR(30)
NOT NULL;

CREATE DOMAIN MARKA1 AS
VARCHAR(30)
NOT NULL;

CREATE DOMAIN MATERIAL AS
VARCHAR(30)
NOT NULL;

CREATE DOMAIN MM AS
SMALLINT
NOT NULL;

CREATE DOMAIN MPR AS
VARCHAR(30)
NOT NULL;

CREATE DOMAIN MSTR AS
NUMERIC(10,10)
NOT NULL;

CREATE DOMAIN MV AS
SMALLINT
NOT NULL;

CREATE DOMAIN N1 AS
SMALLINT
NOT NULL;

CREATE DOMAIN N2 AS
INTEGER
NOT NULL;

CREATE DOMAIN NAIMENOVANIE AS
VARCHAR(30)
NOT NULL;

CREATE DOMAIN NE AS
INTEGER
NOT NULL;

CREATE DOMAIN NRAB AS
SMALLINT
NOT NULL;

CREATE DOMAIN OM AS
NUMERIC(10,10)
NOT NULL;

CREATE DOMAIN SO AS
NUMERIC(10,10)
NOT NULL;

CREATE DOMAIN SOE AS
NUMERIC(10,10)
NOT NULL;

CREATE DOMAIN SS AS
NUMERIC(10,10)
NOT NULL;

CREATE DOMAIN T AS
NUMERIC(10,10)
NOT NULL;

CREATE DOMAIN VR AS
SMALLINT
NOT NULL;

CREATE DOMAIN VS AS
SMALLINT
NOT NULL;

CREATE DOMAIN VU AS
SMALLINT
NOT NULL;



/******************************************************************************/
/***                               Generators                               ***/
/******************************************************************************/

CREATE GENERATOR "KONSTRUKCIYA_NOMER";
SET GENERATOR "KONSTRUKCIYA_NOMER" TO 100;

CREATE GENERATOR "OSNASKA_NOMER";
SET GENERATOR "OSNASKA_NOMER" TO 101;



/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/



CREATE TABLE "SPECIPIKACIYA MONTAGNIH EL" (
    NOMER                    N1,
    "NAZVANIE KONSRUKCII"    NAIMENOVANIE,
    "MARKIROVKA KONSRUKCII"  MARKA,
    "MASSA KONSTRUKCII"      M1,
    KOLICHESTVO              K,
    "OBSHAYA MASSA"          OM,
    "ESKIZ KONSTRUKCII"      ESKIZ,
    DLINA                    L,
    SHIRINA                  B,
    TOLHINA                  T,
    "MATERIAL KONSTRUKCII"   MATERIAL
);


CREATE TABLE "VEDOMOST GRUZOZAHVATNYKH USTR" (
    NOMER1                        N2,
    "MARKIROVKA KONSRUKCII"       MARKA,
    "MARKIROVKA OSNASKI"          MARKA1,
    "ESKIZ OSNASKI"               ESKIZ1,
    GRUZOPODEMNOST                GPT,
    "MASSA STROPOV"               MSTR,
    "MASSA MONTAZHNYKH PRIBOROV"  MPR,
    KOLICHESTVO                   K1,
    "VREMYA STROPOVKI"            VS,
    "VREMYA USTANOVKI"            VU,
    VREMYARASCTROPOVKI            VR
);


CREATE TABLE "VEDOMOST MONT HARAKTERISTIK" (
    NOMER                         N1,
    "MASSA KONSTRUKCII"           M1,
    "MASSA STROPOV"               MSTR,
    "MASSA MONTAZHNYKH PRIBOROV"  MPR,
    "OBHAYA MASSA"                MM,
    "VYSOTA OPORY"                HOP,
    "ZAPAS PO VYSOTE"             HZAPAS,
    "VYSOTA ELEMENTA"             HEL,
    "VYSOTA STROPOVKI"            VS,
    "MONTAZHNAYA VYSOTA"          MV,
    "KOLICHESTVO RABOCHIH"        NRAB
);




/******************************************************************************/
/***                           Unique constraints                           ***/
/******************************************************************************/

ALTER TABLE "SPECIPIKACIYA MONTAGNIH EL" ADD CONSTRAINT PK_SPECIPIKACIYAMONTAGNIHEL UNIQUE ("NAZVANIE KONSRUKCII");
ALTER TABLE "SPECIPIKACIYA MONTAGNIH EL" ADD CONSTRAINT PK_SPECIPIKACIYAMONTAGNIHEL1 UNIQUE ("MARKIROVKA KONSRUKCII");
ALTER TABLE "SPECIPIKACIYA MONTAGNIH EL" ADD CONSTRAINT PK_SPECIPIKACIYAMONTAGNIHEL2 UNIQUE ("MASSA KONSTRUKCII");
ALTER TABLE "VEDOMOST GRUZOZAHVATNYKH USTR" ADD CONSTRAINT PK_VEDOMOSTMONTHARAKTERIS UNIQUE ("MASSA STROPOV");
ALTER TABLE "VEDOMOST GRUZOZAHVATNYKH USTR" ADD CONSTRAINT PK_VEDOMOSTMONTHARAKTERIS1 UNIQUE ("MASSA MONTAZHNYKH PRIBOROV");


/******************************************************************************/
/***                              Primary keys                              ***/
/******************************************************************************/

ALTER TABLE "SPECIPIKACIYA MONTAGNIH EL" ADD CONSTRAINT PK_SPECIPIKACIYAMONTAGNIHEL0 PRIMARY KEY (NOMER);
ALTER TABLE "VEDOMOST GRUZOZAHVATNYKH USTR" ADD CONSTRAINT PK_VEDOMOSTMONTAGNIHUSTR PRIMARY KEY (NOMER1);


/******************************************************************************/
/***                              Foreign keys                              ***/
/******************************************************************************/

ALTER TABLE "VEDOMOST GRUZOZAHVATNYKH USTR" ADD CONSTRAINT FK_VEDOMOST_MONT_HARAKTERISTIK4 FOREIGN KEY ("MARKIROVKA KONSRUKCII") REFERENCES "SPECIPIKACIYA MONTAGNIH EL" ("MARKIROVKA KONSRUKCII") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "VEDOMOST MONT HARAKTERISTIK" ADD CONSTRAINT FK_VEDOMOST_MONT_HARAKTERISTIK FOREIGN KEY (NOMER) REFERENCES "SPECIPIKACIYA MONTAGNIH EL" (NOMER) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "VEDOMOST MONT HARAKTERISTIK" ADD CONSTRAINT FK_VEDOMOST_MONT_HARAKTERISTIK1 FOREIGN KEY ("MASSA KONSTRUKCII") REFERENCES "SPECIPIKACIYA MONTAGNIH EL" ("MASSA KONSTRUKCII") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "VEDOMOST MONT HARAKTERISTIK" ADD CONSTRAINT FK_VEDOMOST_MONT_HARAKTERISTIK2 FOREIGN KEY ("MASSA STROPOV") REFERENCES "VEDOMOST GRUZOZAHVATNYKH USTR" ("MASSA STROPOV") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "VEDOMOST MONT HARAKTERISTIK" ADD CONSTRAINT FK_VEDOMOST_MONT_HARAKTERISTIK3 FOREIGN KEY ("MASSA MONTAZHNYKH PRIBOROV") REFERENCES "VEDOMOST GRUZOZAHVATNYKH USTR" ("MASSA MONTAZHNYKH PRIBOROV") ON DELETE CASCADE ON UPDATE CASCADE;


/******************************************************************************/
/***                                Indices                                 ***/
/******************************************************************************/

CREATE INDEX IDDLINAW ON "SPECIPIKACIYA MONTAGNIH EL" (DLINA);
CREATE INDEX IDKOLICHESTVOW ON "SPECIPIKACIYA MONTAGNIH EL" (KOLICHESTVO);
CREATE INDEX IDMARKIROVKAKONSRUKCIIW ON "SPECIPIKACIYA MONTAGNIH EL" ("MARKIROVKA KONSRUKCII");
CREATE INDEX IDMASSAKONSTRUKCIIW ON "SPECIPIKACIYA MONTAGNIH EL" ("MASSA KONSTRUKCII");
CREATE INDEX IDNAZVANIEKONSRUKCIIW ON "SPECIPIKACIYA MONTAGNIH EL" ("NAZVANIE KONSRUKCII");
CREATE INDEX IDNOMERW ON "SPECIPIKACIYA MONTAGNIH EL" (NOMER);
CREATE INDEX IDOBSHAYAMASSAW ON "SPECIPIKACIYA MONTAGNIH EL" ("OBSHAYA MASSA");
CREATE INDEX IDSHIRINAW ON "SPECIPIKACIYA MONTAGNIH EL" (SHIRINA);
CREATE INDEX IDTOLHINAW ON "SPECIPIKACIYA MONTAGNIH EL" (TOLHINA);
CREATE INDEX IMATERIALKONSTRUKCIIW ON "SPECIPIKACIYA MONTAGNIH EL" ("MATERIAL KONSTRUKCII");
CREATE INDEX IDGRUZOPODEMNOSTW ON "VEDOMOST GRUZOZAHVATNYKH USTR" (GRUZOPODEMNOST);
CREATE INDEX IDKOLICHESTVOW1 ON "VEDOMOST GRUZOZAHVATNYKH USTR" (KOLICHESTVO);
CREATE INDEX IDMARKIRIVKAOSNASKIW ON "VEDOMOST GRUZOZAHVATNYKH USTR" ("MARKIROVKA OSNASKI");
CREATE INDEX IDMASSAMONTAZHNYKHPRIBOROVW ON "VEDOMOST GRUZOZAHVATNYKH USTR" ("MASSA MONTAZHNYKH PRIBOROV");
CREATE INDEX IDMASSASTROPOVW ON "VEDOMOST GRUZOZAHVATNYKH USTR" ("MASSA STROPOV");
CREATE INDEX IDNOMER1W ON "VEDOMOST GRUZOZAHVATNYKH USTR" (NOMER1);
CREATE INDEX IDVREMYASTROPOVKIW ON "VEDOMOST GRUZOZAHVATNYKH USTR" ("VREMYA STROPOVKI");
CREATE INDEX IDVREMYAUSTANOVKIW ON "VEDOMOST GRUZOZAHVATNYKH USTR" ("VREMYA USTANOVKI");
CREATE INDEX IDKOLICHESTVORABOCHIHVZVENEW ON "VEDOMOST MONT HARAKTERISTIK" ("KOLICHESTVO RABOCHIH");
CREATE INDEX IDMONTAZHNAYAVYSOTAW ON "VEDOMOST MONT HARAKTERISTIK" ("MONTAZHNAYA VYSOTA");
CREATE INDEX IDOBHAYAMASSAW ON "VEDOMOST MONT HARAKTERISTIK" ("OBHAYA MASSA");
CREATE INDEX IDVYSOTAELEMENTAW ON "VEDOMOST MONT HARAKTERISTIK" ("VYSOTA ELEMENTA");
CREATE INDEX IDVYSOTAOPORYW ON "VEDOMOST MONT HARAKTERISTIK" ("VYSOTA OPORY");
CREATE INDEX IDVYSOTASTROPOVKIW ON "VEDOMOST MONT HARAKTERISTIK" ("VYSOTA STROPOVKI");
CREATE INDEX IDZAPASPOVYSOTEW ON "VEDOMOST MONT HARAKTERISTIK" ("ZAPAS PO VYSOTE");

CREATE OR ALTER TRIGGER TR_NOMER_KONSTR FOR "SPECIPIKACIYA MONTAGNIH EL"
active BEFORE INSERT POSITION 0
AS
BEGIN
 IF ("SPECIPIKACIYA MONTAGNIH EL".nomer IS NULL) THEN
 "SPECIPIKACIYA MONTAGNIH EL".nomer = gen_id(KONSTRUKCIYA_NOMER,1);
END