# Banco | Nebula
# Script do banco para PostGresSQL
Script
CREATE TABLE IF NOT EXISTS Servidor (
idServidor SERIAL PRIMARY KEY,
Nome TEXT NOT NULL,
email TEXT NOT NULL,
tipo TEXT NOT NULL,
refresh\_token TEXT NOT NULL,
acess\_token TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS Formulario (
idFormulario SERIAL PRIMARY KEY,
nome TEXT NOT NULL UNIQUE,
descricao TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS preso (
cpf TEXT PRIMARY KEY,
Nome TEXT NOT NULL,
data\_nascimento TEXT NOT NULL,
telefone TEXT NOT NULL,
endereco TEXT NOT NULL,
genero TEXT NOT NULL,
sexo TEXT NOT NULL,
estado\_civil TEXT NOT NULL,
cor\_pele TEXT NOT NULL,
escolaridade TEXT NOT NULL,
sabe\_ler BOOLEAN NOT NULL,
pessoa\_deficiencia TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS Processo (
idProcesso SERIAL PRIMARY KEY,
numero TEXT,
preso\_cpf TEXT NOT NULL,
CONSTRAINT fk\_Processo\_preso FOREIGN KEY (preso\_cpf)
REFERENCES preso (cpf)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
CREATE TABLE IF NOT EXISTS Condicao\_Social (
idCondicao\_Social SERIAL PRIMARY KEY,
vulnerabilidade TEXT NOT NULL,
trabalho\_carteira TEXT NOT NULL,
renda\_mensal TEXT NOT NULL,
quantidade\_filhos TEXT NOT NULL,
filhos\_menores TEXT NOT NULL,
filhos\_deficiencia TEXT NOT NULL,
unico\_provedor TEXT NOT NULL,
preso\_cpf TEXT NOT NULL,
CONSTRAINT fk\_Condicao\_Social\_preso FOREIGN KEY (preso\_cpf)
REFERENCES preso (cpf)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
CREATE TABLE IF NOT EXISTS preso\_\_Formulario (
id TEXT NOT NULL,
Formulario\_idFormulario INT NOT NULL,
preso\_cpf TEXT NOT NULL,
data\_preenchimento TEXT NOT NULL,
Servidor\_idServidor INT NOT NULL,
desc\_status TEXT NOT NULL,
PRIMARY KEY (id, Formulario\_idFormulario, preso\_cpf),
CONSTRAINT fk\_preso\_has\_Formulario FOREIGN KEY (Formulario\_idFormulario)
REFERENCES Formulario (idFormulario)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk\_preso\_Formulario FOREIGN KEY (preso\_cpf)
REFERENCES preso (cpf)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk\_preso\_Servidor FOREIGN KEY (Servidor\_idServidor)
REFERENCES Servidor (idServidor)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
CREATE TABLE IF NOT EXISTS Flagrante (
idFlagrante SERIAL PRIMARY KEY,
preso\_\_Formulario\_id TEXT NOT NULL,
preso\_\_Formulario\_Formulario\_idFormulario INT NOT NULL,
preso\_\_Formulario\_preso\_cpf TEXT NOT NULL,
data\_entrevista TEXT NOT NULL,
local\_prisao TEXT NOT NULL,
fianca TEXT NOT NULL,
valor\_fianca TEXT NOT NULL,
primario\_reincidente TEXT NOT NULL,
agressao\_policial TEXT NOT NULL,
deseja\_apuracao TEXT NOT NULL,
recebeu\_alimentacao BOOLEAN NOT NULL,
descricao\_agressao TEXT NOT NULL,
consideracoes TEXT NOT NULL,
sala\_audiencia TEXT NOT NULL,
resultado\_audiencia TEXT NOT NULL,
encaminhamento\_psicossocial TEXT NOT NULL,
pagou\_fianca BOOLEAN NOT NULL,
valor\_pago TEXT NOT NULL,
CONSTRAINT fk\_Flagrante\_preso FOREIGN KEY (preso\_\_Formulario\_id, preso\_\_Formulario\_Formulario\_idFormulario, preso\_\_Formulario\_preso\_cpf)
REFERENCES preso\_\_Formulario (id, Formulario\_idFormulario, preso\_cpf)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
CREATE TABLE IF NOT EXISTS Pensao\_Alimenticia (
idPensao\_Alimenticia SERIAL PRIMARY KEY,
preso\_\_Formulario\_id TEXT NOT NULL,
preso\_\_Formulario\_Formulario\_idFormulario INT NOT NULL,
preso\_\_Formulario\_preso\_cpf TEXT NOT NULL,
data\_entrevista TEXT NOT NULL,
local\_processo TEXT NOT NULL,
data\_preso TEXT NOT NULL,
tempo\_decisao TEXT NOT NULL,
tempo\_prisao TEXT NOT NULL,
valor\_divida TEXT NOT NULL,
classificado TEXT NOT NULL,
primeira\_impressao TEXT NOT NULL,
vezes\_preso TEXT NOT NULL,
possibilidade\_acordo TEXT NOT NULL,
representacao\_processual TEXT NOT NULL,
cela TEXT NOT NULL,
conduta\_policial TEXT NOT NULL,
problema\_alimentacao TEXT NOT NULL,
problema\_higiene TEXT NOT NULL,
problema\_atendimento\_medico TEXT NOT NULL,
CONSTRAINT fk\_Pensao\_Alimenticia\_preso FOREIGN KEY (preso\_\_Formulario\_id, preso\_\_Formulario\_Formulario\_idFormulario, preso\_\_Formulario\_preso\_cpf)
REFERENCES preso\_\_Formulario (id, Formulario\_idFormulario, preso\_cpf)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
CREATE TABLE IF NOT EXISTS Presidio (
idPresidio SERIAL PRIMARY KEY,
unidade\_prisional TEXT NOT NULL,
meses\_preso TEXT NOT NULL,
primario\_reincidente INT NOT NULL,
quantidade\_presos INT NOT NULL,
estuda BOOLEAN NOT NULL,
acesso\_livros BOOLEAN NOT NULL,
trabalha TEXT NOT NULL,
contato\_mundo\_externo TEXT NOT NULL,
assistencia\_religiosa TEXT NOT NULL,
violencia\_policial TEXT NOT NULL,
tipo\_violencia TEXT NOT NULL,
banho\_sol\_semana TEXT NOT NULL,
banho\_sol\_fim\_semana TEXT NOT NULL,
recebe\_visita TEXT NOT NULL,
especificacao\_visita TEXT NOT NULL,
problema\_visita TEXT NOT NULL,
problema\_higiene TEXT NOT NULL,
problema\_alimentacao TEXT NOT NULL,
problema\_agua TEXT NOT NULL,
problema\_medico TEXT NOT NULL,
preso\_\_Formulario\_id TEXT NOT NULL,
preso\_\_Formulario\_Formulario\_idFormulario INT NOT NULL,
preso\_\_Formulario\_preso\_cpf TEXT NOT NULL,
CONSTRAINT fk\_Presidio\_preso FOREIGN KEY (preso\_\_Formulario\_id, preso\_\_Formulario\_Formulario\_idFormulario, preso\_\_Formulario\_preso\_cpf)
REFERENCES preso\_\_Formulario (id, Formulario\_idFormulario, preso\_cpf)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
CREATE TABLE IF NOT EXISTS endereco (
cep INT PRIMARY KEY,
Estado TEXT,
cidade TEXT,
bairro TEXT,
numero TEXT,
preso\_cpf TEXT NOT NULL,
CONSTRAINT fk\_endereco\_preso FOREIGN KEY (preso\_cpf)
REFERENCES preso (cpf)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
