CREATE DOMAIN DX AS INTEGER NOT NULL;
CREATE DOMAIN DX0 AS INTEGER DEFAULT 0 NOT NULL;
COMMENT ON DOMAIN DX0 IS 'Для ссылки на справочники, в которых есть пустое значение как вариант выбора';

CREATE DOMAIN STRING10 AS VARCHAR(10) CHARACTER SET WIN1251 COLLATE WIN1251;
CREATE DOMAIN STRING25 AS VARCHAR(25) CHARACTER SET WIN1251 COLLATE WIN1251;
CREATE DOMAIN STRING30 AS VARCHAR(30) CHARACTER SET WIN1251 COLLATE WIN1251;
CREATE DOMAIN STRING70 AS VARCHAR(70) CHARACTER SET WIN1251 COLLATE WIN1251;
CREATE DOMAIN BOOLEAN AS SMALLINT DEFAULT 0 CHECK (VALUE BETWEEN 0 AND 1);
CREATE DOMAIN DTS AS TIMESTAMP DEFAULT current_timestamp;

CREATE TABLE PERSONNEL (
    XPERSONNEL  DX,
    FAMILY      STRING30 NOT NULL,
    NAME        STRING25 NOT NULL 
);
ALTER TABLE PERSONNEL ADD PRIMARY KEY (XPERSONNEL);
COMMENT ON TABLE PERSONNEL IS 'Персонал';

CREATE TABLE PATIENT (
    XPATIENT  DX,
    FAMILY      STRING30 NOT NULL,
    NAME        STRING25 NOT NULL 
);
ALTER TABLE PATIENT ADD PRIMARY KEY (XPATIENT);
COMMENT ON TABLE PATIENT IS 'Пациент';


CREATE TABLE CASERECORD (
    XCASERECORD     DX,
    XBRANCH         DX /* DX = INTEGER NOT NULL */,
    XPATIENT        DX /* DX = INTEGER NOT NULL */);
ALTER TABLE CASERECORD ADD CONSTRAINT PK_CASERECORD PRIMARY KEY (XCASERECORD);
COMMENT ON TABLE CASERECORD IS 'История болезни';

CREATE TABLE GERMICIDE (
    XGERMICIDE  DX /* DX = INTEGER NOT NULL */,
    NAME        STRING25 /* STRING25 = VARCHAR(25) */,
    NAMELATIN   STRING30 /* STRING30 = VARCHAR(30) */
);
ALTER TABLE GERMICIDE ADD CONSTRAINT PK_GERMICIDE PRIMARY KEY (XGERMICIDE);
COMMENT ON TABLE GERMICIDE IS 'Бактерицид';

CREATE TABLE TESTGERMICIDE (
    XTESTGERMICIDE  DX /* DX = INTEGER NOT NULL */,
    NAME            STRING30 /* STRING30 = VARCHAR(30) */,
    USE             BOOLEAN /* BOOLEAN = SMALLINT DEFAULT 0 CHECK(VALUE BETWEEN 0 AND 1) */
);
ALTER TABLE TESTGERMICIDE ADD CONSTRAINT PK_TESTGERMICIDE PRIMARY KEY (XTESTGERMICIDE);
COMMENT ON TABLE TESTGERMICIDE IS 'Бактериологический тест';

CREATE TABLE TESTGERMICIDE$GERMICIDE (
    XTESTGERMICIDE$GERMICIDE  DX /* DX = INTEGER NOT NULL */,
    XTESTGERMICIDE            DX /* DX = INTEGER NOT NULL */,
    XGERMICIDE                DX /* DX = INTEGER NOT NULL */
);
ALTER TABLE TESTGERMICIDE$GERMICIDE ADD CONSTRAINT PK_TESTGERMICIDE$GERMICIDE PRIMARY KEY (XTESTGERMICIDE$GERMICIDE);
ALTER TABLE TESTGERMICIDE$GERMICIDE ADD CONSTRAINT FK_TESTGERMICIDE$GERMICIDE_1 FOREIGN KEY (XTESTGERMICIDE) REFERENCES TESTGERMICIDE (XTESTGERMICIDE);
ALTER TABLE TESTGERMICIDE$GERMICIDE ADD CONSTRAINT FK_TESTGERMICIDE$GERMICIDE_2 FOREIGN KEY (XGERMICIDE) REFERENCES GERMICIDE (XGERMICIDE);
COMMENT ON TABLE TESTGERMICIDE$GERMICIDE IS 'Список агентов, входящих в тест';

CREATE TABLE BACTERIUM (
    XBACTERIUM  DX /* DX = INTEGER NOT NULL */,
    NAME        STRING25 /* STRING25 = VARCHAR(25) */,
    NAMELATIN   STRING30 /* STRING30 = VARCHAR(30) */
);
ALTER TABLE BACTERIUM ADD CONSTRAINT PK_BACTERIUM PRIMARY KEY (XBACTERIUM);
COMMENT ON TABLE BACTERIUM IS 'Бактерии';

CREATE TABLE ENVIRONMENT (
    XENVIRONMENT  DX /* DX = INTEGER NOT NULL */,
    NAME          STRING25 /* STRING25 = VARCHAR(25) */,
    NAMELATIN     STRING30 /* STRING30 = VARCHAR(30) */
);
ALTER TABLE ENVIRONMENT ADD CONSTRAINT PK_ENVIRONMENT PRIMARY KEY (XENVIRONMENT);
COMMENT ON TABLE ENVIRONMENT IS 'Среда обитания бактерий';

CREATE TABLE TESTINGGERMICIDE (
    XTESTINGGERMICIDE  DX /* DX = INTEGER NOT NULL */,
    XTESTGERMICIDE     DX /* DX = INTEGER NOT NULL */,
    XPATIENT           DX /* DX = INTEGER NOT NULL */,
    XCASERECORD        DX0 /* DX0 = INTEGER DEFAULT 0 NOT NULL */,
    XPERSONNEL         DX /* DX = INTEGER NOT NULL */,
    XBACTERIUM         DX /* DX = INTEGER NOT NULL */,
    XENVIRONMENT       DX /* DX = INTEGER NOT NULL */,
    TS                 DTS /* DTS = TIMESTAMP DEFAULT 'NOW' */,
    RESULT             STRING70 /* STRING70 = VARCHAR(70) */
);
ALTER TABLE TESTINGGERMICIDE ADD CONSTRAINT PK_TESTINGGERMICIDE PRIMARY KEY (XTESTINGGERMICIDE);
ALTER TABLE TESTINGGERMICIDE ADD CONSTRAINT FK_TESTINGGERMICIDE_1 FOREIGN KEY (XTESTGERMICIDE) REFERENCES TESTGERMICIDE (XTESTGERMICIDE);
ALTER TABLE TESTINGGERMICIDE ADD CONSTRAINT FK_TESTINGGERMICIDE_2 FOREIGN KEY (XPATIENT) REFERENCES PATIENT (XPATIENT);
ALTER TABLE TESTINGGERMICIDE ADD CONSTRAINT FK_TESTINGGERMICIDE_3 FOREIGN KEY (XCASERECORD) REFERENCES CASERECORD (XCASERECORD);
ALTER TABLE TESTINGGERMICIDE ADD CONSTRAINT FK_TESTINGGERMICIDE_4 FOREIGN KEY (XPERSONNEL) REFERENCES PERSONNEL (XPERSONNEL);
ALTER TABLE TESTINGGERMICIDE ADD CONSTRAINT FK_TESTINGGERMICIDE_5 FOREIGN KEY (XBACTERIUM) REFERENCES BACTERIUM (XBACTERIUM);
ALTER TABLE TESTINGGERMICIDE ADD CONSTRAINT FK_TESTINGGERMICIDE_6 FOREIGN KEY (XENVIRONMENT) REFERENCES ENVIRONMENT (XENVIRONMENT);
COMMENT ON TABLE TESTINGGERMICIDE IS 'Бактериологическое исследование материала пациента';
COMMENT ON COLUMN TESTINGGERMICIDE.XTESTGERMICIDE IS 'Ссылка на тест';
COMMENT ON COLUMN TESTINGGERMICIDE.XPATIENT IS 'Ссылка на пациента';
COMMENT ON COLUMN TESTINGGERMICIDE.XCASERECORD IS 'Ссылка на историю болезни, если есть';
COMMENT ON COLUMN TESTINGGERMICIDE.XPERSONNEL IS 'Ссылка на персонал (лаборант)';
COMMENT ON COLUMN TESTINGGERMICIDE.XBACTERIUM IS 'Ссылка на иссл. бактерию';
COMMENT ON COLUMN TESTINGGERMICIDE.XENVIRONMENT IS 'Ссылка на среду обитания бактерии';

CREATE TABLE TESTINGGERMICIDE$GERMICIDE (
    XTESTINGGERMICIDE$GERMICIDE  DX /* DX = INTEGER NOT NULL */,
    XTESTINGGERMICIDE            DX /* DX = INTEGER NOT NULL */,
    XGERMICIDE                   DX /* DX = INTEGER NOT NULL */,
    RESULT                       STRING10 /* STRING10 = VARCHAR(10) */
);
ALTER TABLE TESTINGGERMICIDE$GERMICIDE ADD CONSTRAINT PK_TESTINGGERMICIDE$GERMICIDE PRIMARY KEY (XTESTINGGERMICIDE$GERMICIDE);
ALTER TABLE TESTINGGERMICIDE$GERMICIDE ADD CONSTRAINT FK_TESTINGGERMICIDE$GERMICIDE_1 FOREIGN KEY (XTESTINGGERMICIDE) REFERENCES TESTINGGERMICIDE (XTESTINGGERMICIDE);
ALTER TABLE TESTINGGERMICIDE$GERMICIDE ADD CONSTRAINT FK_TESTINGGERMICIDE$GERMICIDE_2 FOREIGN KEY (XGERMICIDE) REFERENCES GERMICIDE (XGERMICIDE);
COMMENT ON COLUMN TESTINGGERMICIDE$GERMICIDE.XGERMICIDE IS 'Ссылка на антибиотик';
COMMENT ON COLUMN TESTINGGERMICIDE$GERMICIDE.RESULT IS 'Результат воздействия антибиотика';

SET TERM ^ ;
CREATE OR ALTER TRIGGER TESTINGGERMICIDE_BD FOR TESTINGGERMICIDE
ACTIVE BEFORE DELETE POSITION 0
AS
begin
  delete from testinggermicide$germicide tg where tg.xtestinggermicide=old.xtestinggermicide;
end
^
SET TERM ; ^

SET TERM ^ ;
CREATE OR ALTER TRIGGER TESTGERMICIDE_BD FOR TESTGERMICIDE
ACTIVE BEFORE DELETE POSITION 0
AS
begin
  delete from testgermicide$germicide tg where tg.xtestgermicide=old.xtestgermicide;
end
^
SET TERM ; ^