SCRIPT DE CRIAÇÃO DA BASE DE DADOS

CREATE TABLE TTCC_SERVICE_DESK ( COD_USUARIO SERIAL,

NOM_USUARIO VARCHAR(100) NOT NULL, TXT_LOGIN VARCHAR(32) NOT NULL, TXT_SENHA VARCHAR(32) NOT NULL );

CREATE TABLE TTCC_INCIDENTE ( COD_INCIDENTE SERIAL,

IND_SITUACAO INTEGER NOT NULL DEFAULT 0, DSC_INCIDENTE TEXT NULL,

DSC_SOLUCAO TEXT NULL );

CREATE TABLE TTCC_PRIORIDADE ( COD_PRIORIDADE SERIAL,

NOM_PRIORIDADE VARCHAR(100) NOT NULL, NUM_TEMPO_RESOLUCAO INTEGER NOT NULL );

CREATE TABLE TTCC_SISTEMA_CONFIGURACAO ( NOM_CAMPO VARCHAR(100) NOT NULL, VAL_CAMPO_INT INTEGER NULL, VAL_CAMPO_TXT TEXT NULL );

CREATE TABLE TTCC_CATEGORIA ( COD_CATEGORIA SERIAL,

COD_CATEGORIA_PAI INTEGER NULL, NOM_CATEGORIA VARCHAR(255) NOT NULL );

CREATE TABLE TTCC_TIPO_USUARIO ( COD_TIPO_USUARIO SERIAL,

NOM_TIPO_USUARIO VARCHAR(100) NOT NULL );

CREATE TABLE TTCC_CI ( COD_CI SERIAL,

COD_CI_PAI INTEGER NULL, NOM_CI VARCHAR(255) NOT NULL );

CREATE TABLE TTCC_SERVICE_DESK_TIPO ( COD_USUARIO INTEGER NOT NULL, COD_TIPO_USUARIO INTEGER NOT NULL );

CREATE TABLE TTCC_PROBLEMA ( COD_PROBLEMA SERIAL,

COD_USUARIO INTEGER NOT NULL, COD_CATEGORIA INTEGER NULL, COD_PRIORIDADE INTEGER NULL,

TXT_CAUSA_PROBLEMA VARCHAR(255) NOT NULL, DAT_CRIACAO TIMESTAMP NOT NULL,

DAT_REVISAO TIMESTAMP NULL, IND_TEMPO_PREVISTO INTEGER NULL, IND_TEMPO_REAL INTEGER NULL, TXT_DIAGNOSTICO TEXT NOT NULL, TXT_PERGUNTA VARCHAR(255) NULL );

CREATE TABLE TTCC_CAUSA ( COD_CAUSA SERIAL,

COD_PROBLEMA INTEGER NOT NULL, COD_PROBLEMA_2 INTEGER NOT NULL, COD_CAUSA_PAI INTEGER NULL, NOM_CAUSA VARCHAR(255) NOT NULL );

CREATE TABLE TTCC_KEYWORD ( COD_KEYWORD SERIAL,

COD_PROBLEMA INTEGER NOT NULL, IND_IMPORTANCIA INTEGER NOT NULL, TXT_KEYWORD VARCHAR(50) NOT NULL );

CREATE TABLE TTCC_ERRO (

COD_PROBLEMA INTEGER NOT NULL, COD_USUARIO INTEGER NOT NULL,

DAT_CADASTRO TIMESTAMP NOT NULL DEFAULT NOW(), DAT_REVISAO TIMESTAMP NULL,

IND_TEMPO_PREVISTO INTEGER NULL, IND_TEMPO_REAL INTEGER NULL, TXT_ANOTACAO TEXT NULL,

TXT_SOLUCAO_PALIATIVA TEXT NOT NULL );

CREATE TABLE TTCC_INCIDENTE_PROBLEMA ( COD_PROBLEMA INTEGER NOT NULL, COD_INCIDENTE INTEGER NOT NULL );

CREATE TABLE TTCC_PROBLEMA_CI ( COD_PROBLEMA INTEGER NOT NULL, COD_CI INTEGER NOT NULL

);

CREATE TABLE TTCC_RFC (

COD_PROBLEMA INTEGER NOT NULL, COD_USUARIO INTEGER NOT NULL, DSC_RFC TEXT NOT NULL

);

CREATE TABLE TTCC_LIBERACAO ( COD_LIBERACAO SERIAL,

COD_PROBLEMA INTEGER NOT NULL,

IND_ENTREGUE BOOL NOT NULL DEFAULT FALSE );

CREATE TABLE TTCC_FEEDBACK ( COD_FEEDBACK SERIAL,

COD_PROBLEMA INTEGER NOT NULL, VAL_FEEDBACK INTEGER NOT NULL );

ALTER TABLE TTCC_SERVICE_DESK ADD CONSTRAINT "ktcc_service_desk_01" PRIMARY KEY (COD_USUARIO);

ALTER TABLE TTCC_INCIDENTE ADD CONSTRAINT "ktcc_incidente_01" PRIMARY KEY (COD_INCIDENTE);

ALTER TABLE TTCC_PRIORIDADE ADD CONSTRAINT "ktcc_prioridade_01" PRIMARY KEY (COD_PRIORIDADE);

ALTER TABLE TTCC_SISTEMA_CONFIGURACAO ADD CONSTRAINT "ktcc_sistema_configuracao_01" PRIMARY KEY (NOM_CAMPO);

ALTER TABLE TTCC_CATEGORIA ADD CONSTRAINT "ktcc_categoria_01" PRIMARY KEY (COD_CATEGORIA);

CREATE INDEX "itcc_categoria_01" on TTCC_CATEGORIA(COD_CATEGORIA_PAI);

ALTER TABLE TTCC_CATEGORIA ADD CONSTRAINT "ftcc_categoria_01" FOREIGN KEY(COD_CATEGORIA_PAI) REFERENCES TTCC_CATEGORIA(COD_CATEGORIA)

ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE TTCC_TIPO_USUARIO ADD CONSTRAINT "ktcc_tipo_usuario_01" PRIMARY KEY (COD_TIPO_USUARIO);

ALTER TABLE TTCC_CI ADD CONSTRAINT "ktcc_ci_01" PRIMARY KEY (COD_CI);

CREATE INDEX "itcc_ci_01" on TTCC_CI(COD_CI_PAI);

ALTER TABLE TTCC_CI ADD CONSTRAINT "ftcc_ci_01" FOREIGN KEY(COD_CI_PAI) REFERENCES TTCC_CI(COD_CI)

ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE TTCC_SERVICE_DESK_TIPO ADD CONSTRAINT "ktcc_service_desk_tipo_01" PRIMARY KEY (COD_USUARIO, COD_TIPO_USUARIO);

CREATE INDEX "itcc_service_desk_tipo_01" on TTCC_SERVICE_DESK_TIPO(COD_USUARIO);

CREATE INDEX "itcc_service_desk_tipo_02" on TTCC_SERVICE_DESK_TIPO(COD_TIPO_USUARIO);

ALTER TABLE TTCC_SERVICE_DESK_TIPO ADD CONSTRAINT "ftcc_service_desk_tipo_01" FOREIGN KEY(COD_USUARIO)

REFERENCES TTCC_SERVICE_DESK(COD_USUARIO) ON DELETE RESTRICT

ON UPDATE CASCADE;

ALTER TABLE TTCC_SERVICE_DESK_TIPO ADD CONSTRAINT "ftcc_service_desk_tipo_02" FOREIGN KEY(COD_TIPO_USUARIO)

REFERENCES TTCC_TIPO_USUARIO(COD_TIPO_USUARIO) ON DELETE RESTRICT

ON UPDATE CASCADE;

ALTER TABLE TTCC_PROBLEMA ADD CONSTRAINT "ktcc_problema_01" PRIMARY KEY (COD_PROBLEMA);

CREATE INDEX "itcc_problema_01" on TTCC_PROBLEMA(COD_PRIORIDADE);

CREATE INDEX "itcc_problema_02" on TTCC_PROBLEMA(COD_CATEGORIA);

CREATE INDEX "itcc_problema_03" on TTCC_PROBLEMA(COD_USUARIO);

ALTER TABLE TTCC_PROBLEMA ADD CONSTRAINT "ftcc_problema_01" FOREIGN KEY(COD_PRIORIDADE) REFERENCES TTCC_PRIORIDADE(COD_PRIORIDADE)

ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE TTCC_PROBLEMA ADD CONSTRAINT "ftcc_problema_02" FOREIGN KEY(COD_CATEGORIA) REFERENCES TTCC_CATEGORIA(COD_CATEGORIA)

ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE TTCC_PROBLEMA ADD CONSTRAINT "ftcc_problema_03" FOREIGN KEY(COD_USUARIO) REFERENCES TTCC_SERVICE_DESK(COD_USUARIO)

ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE TTCC_CAUSA ADD CONSTRAINT "ktcc_causa_01" PRIMARY KEY (COD_CAUSA, COD_PROBLEMA);

CREATE INDEX "itcc_causa_01" on TTCC_CAUSA(COD_CAUSA_PAI, COD_PROBLEMA_2);

CREATE INDEX "itcc_causa_02" on TTCC_CAUSA(COD_PROBLEMA);

ALTER TABLE TTCC_CAUSA ADD CONSTRAINT "ftcc_causa_01" FOREIGN KEY(COD_CAUSA_PAI, COD_PROBLEMA_2) REFERENCES TTCC_CAUSA(COD_CAUSA, COD_PROBLEMA)

ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE TTCC_CAUSA ADD CONSTRAINT "ftcc_causa_02" FOREIGN KEY(COD_PROBLEMA) REFERENCES TTCC_PROBLEMA(COD_PROBLEMA)

ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE TTCC_KEYWORD ADD CONSTRAINT "ktcc_keyword_01" PRIMARY KEY (COD_KEYWORD, COD_PROBLEMA);

CREATE INDEX "itcc_keyword_01" on TTCC_KEYWORD(COD_PROBLEMA);

ALTER TABLE TTCC_KEYWORD ADD CONSTRAINT "ftcc_keyword_01" FOREIGN KEY(COD_PROBLEMA) REFERENCES TTCC_PROBLEMA(COD_PROBLEMA)

ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE TTCC_ERRO ADD CONSTRAINT "ktcc_erro_01" PRIMARY KEY (COD_PROBLEMA);

CREATE INDEX "itcc_erro_01" on TTCC_ERRO(COD_PROBLEMA);

CREATE INDEX "itcc_erro_02" on TTCC_ERRO(COD_USUARIO);

ALTER TABLE TTCC_ERRO ADD CONSTRAINT "ftcc_erro_01" FOREIGN KEY(COD_PROBLEMA) REFERENCES TTCC_PROBLEMA(COD_PROBLEMA)

ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE TTCC_ERRO ADD CONSTRAINT "ftcc_erro_02" FOREIGN KEY(COD_USUARIO) REFERENCES TTCC_SERVICE_DESK(COD_USUARIO)

ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE TTCC_INCIDENTE_PROBLEMA ADD CONSTRAINT "ktcc_incidente_problema_01" PRIMARY KEY (COD_PROBLEMA, COD_INCIDENTE);

CREATE INDEX "itcc_incidente_problema_01" on TTCC_INCIDENTE_PROBLEMA(COD_PROBLEMA);

CREATE INDEX "itcc_incidente_problema_02" on TTCC_INCIDENTE_PROBLEMA(COD_INCIDENTE);

ALTER TABLE TTCC_INCIDENTE_PROBLEMA ADD CONSTRAINT "ftcc_incidente_problema_01" FOREIGN KEY(COD_PROBLEMA)

REFERENCES TTCC_PROBLEMA(COD_PROBLEMA) ON DELETE CASCADE

ON UPDATE CASCADE;

ALTER TABLE TTCC_INCIDENTE_PROBLEMA ADD CONSTRAINT "ftcc_incidente_problema_02" FOREIGN KEY(COD_INCIDENTE)

REFERENCES TTCC_INCIDENTE(COD_INCIDENTE) ON DELETE CASCADE

ON UPDATE CASCADE;

ALTER TABLE TTCC_PROBLEMA_CI ADD CONSTRAINT "ktcc_problema_ci_01" PRIMARY KEY (COD_PROBLEMA, COD_CI);

CREATE INDEX "itcc_problema_ci_01" on TTCC_PROBLEMA_CI(COD_PROBLEMA);

CREATE INDEX "itcc_problema_ci_02" on TTCC_PROBLEMA_CI(COD_CI);

ALTER TABLE TTCC_PROBLEMA_CI ADD CONSTRAINT "ftcc_problema_ci_01" FOREIGN KEY(COD_PROBLEMA) REFERENCES TTCC_PROBLEMA(COD_PROBLEMA)

ON DELETE CASCADE

ON UPDATE CASCADE;

ALTER TABLE TTCC_PROBLEMA_CI ADD CONSTRAINT "ftcc_problema_ci_02" FOREIGN KEY(COD_CI) REFERENCES TTCC_CI(COD_CI)

ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE TTCC_RFC ADD CONSTRAINT "ktcc_rfc_01" PRIMARY KEY (COD_PROBLEMA);

CREATE INDEX "itcc_rfc_01" on TTCC_RFC(COD_PROBLEMA);

CREATE INDEX "itcc_rfc_02" on TTCC_RFC(COD_USUARIO);

ALTER TABLE TTCC_RFC ADD CONSTRAINT "ftcc_rfc_01" FOREIGN KEY(COD_PROBLEMA) REFERENCES TTCC_ERRO(COD_PROBLEMA)

ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE TTCC_RFC ADD CONSTRAINT "ftcc_rfc_02" FOREIGN KEY(COD_USUARIO) REFERENCES TTCC_SERVICE_DESK(COD_USUARIO)

ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE TTCC_LIBERACAO ADD CONSTRAINT "ktcc_liberacao_01" PRIMARY KEY (COD_LIBERACAO, COD_PROBLEMA);

CREATE INDEX "itcc_liberacao_01" on TTCC_LIBERACAO(COD_PROBLEMA);

ALTER TABLE TTCC_LIBERACAO ADD CONSTRAINT "ftcc_liberacao_01" FOREIGN KEY(COD_PROBLEMA) REFERENCES TTCC_RFC(COD_PROBLEMA)

ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE TTCC_FEEDBACK ADD CONSTRAINT "ktcc_feedback_01" PRIMARY KEY (COD_FEEDBACK, COD_PROBLEMA);

CREATE INDEX "itcc_feedback_01" on TTCC_FEEDBACK(COD_PROBLEMA);

ALTER TABLE TTCC_FEEDBACK ADD CONSTRAINT "ftcc_feedback_01" FOREIGN KEY(COD_PROBLEMA) REFERENCES TTCC_ERRO(COD_PROBLEMA)

ON DELETE RESTRICT ON UPDATE CASCADE;