CREATE TABLE T_CHECK
(
    datetime date NOT NULL,
    employer varchar2(255) NOT NULL,
    department varchar2(255),
    result varchar(255) NOT NULL,
    cost number(10),
    PRIMARY KEY (datetime,employer),
    CHECK(cost > -1)
);

CREATE TABLE T_CHECK_SOURCE
(
    variation number(10) NOT NULL,
    datetime date NOT NULL,
    employer varchar2(255) NOT NULL,
    PRIMARY KEY(datetime,employer),
    FOREIGN KEY (datetime,employer) REFERENCES T_CHECK(datetime,employer)
);

CREATE TABLE T_CHECK_SCADA
(
    errors_count number(10) NOT NULL,
    max_critics number(10) NOT NULL,
    comments varchar2(3000),
    regulations varchar(255),
    datetime date NOT NULL,
    employer varchar2(255) NOT NULL,
    PRIMARY KEY(datetime,employer),
    FOREIGN KEY (datetime,employer) REFERENCES T_CHECK(datetime,employer),
    CHECK(errors_count > -1),
    CHECK(max_critics > -1)
);

CREATE TABLE T_SOURCE
(
    code number(10) NOT NULL,
    description varchar(3000),
    PRIMARY KEY (code)

);

CREATE TABLE T_ASSOC_CH_SRC
(
    datetime date NOT NULL,
    employer varchar2(255) NOT NULL,
    code number(10) NOT NULL,
    PRIMARY KEY (datetime,employer,code),
    FOREIGN KEY (datetime,employer) REFERENCES T_CHECK_SOURCE(datetime,employer),
    FOREIGN KEY (code) REFERENCES T_SOURCE(code)
);

CREATE TABLE T_SCADA
(
    code number(10) NOT NULL,
    code_scada number(10) NOT NULL,
    description varchar(3000),
    cost_per_month number(10),
    department varchar(255) NOT NULL,
    UNIQUE (code_scada),
    PRIMARY KEY (code_scada,code),
    FOREIGN KEY (code) REFERENCES T_SOURCE(code),
    CHECK(cost_per_month > -1)
);

CREATE TABLE T_ASSOC_CH_SCADA
(
    datetime date NOT NULL,
    employer varchar2(255) NOT NULL,
    code_scada number(10) NOT NULL,
    PRIMARY KEY (datetime,employer,code_scada),
    FOREIGN KEY (datetime,employer) REFERENCES T_CHECK_SCADA(datetime,employer),
    FOREIGN KEY (code_scada) REFERENCES T_SCADA(code_scada)
);