Objetivos do trabalho:

Criar um modelo de dados relacional com no mínimo 4 tabelas e um DW com pelo menos 1 fato e 2 dimensões. Lembrem-se dos relacionamentos e índices

Utilizar dados abertos.

Apresentar, no mínimo, 3 informações, baseada nos dados do DW, através de queries e das melhores práticas de apresentação dos dados (semântica, por favor)

Tempo de apresentação 10 minutos para cada grupo.

Uma pessoa do grupo deve submeter no ambiente virtual: (1) Os bancos de dados (ODS e DW); e (2) as queries desenvolvidas.

1 Introdução

Esta página é uma apresentação do trabalho final da disciplina Projeto de Bancos de Dados, ministrada pelo professor Cláudio Bonel no Curso de Especialização em Ciência de Dados do CEPERJ.

O tema escolhido para o trabalho foi a montagem de um DW que pudesse responder perguntas sobre os Mandados de Prisão e de Internação à espera de cumprimento no Brasil. No decorrer da execução surgiram outras perguntas vinculando os mandados ao Sistema Prisional, o que levou ao acréscimo de tabelas com os dados necessários.

1.1 A origem dos dados

Para elaborar o banco relacional e o DW foram utilizadas as seguintes fontes de dados:

Os mandados de prisão e de internação em aberto no Brasil, buscados no site do Conselho Nacional de Justiça ;
A Resolução CNJ 65/2008 que define a forma da numeração única dos processos judiciais no Brasil, encontrada em outra parte do site do Conselha Nacional de Justiça;
As serventias do Estado do Rio de Janeiro, encontradas no site do Tribunal de Justiça do Estado do Rio de Janeiro; e
Dados estatísticos sobre o Sistema Prisional Brasileiro, encontrados página da Departamento Penitenciário Nacional.

2 O Banco de Dados Relacional

Depois de uma análise dos dados encontrados no sites citados acima dos foi construído o desenho do banco de dados relacional.

Os arquivos orginais, em .csv, foram carregados na base de dados utilizando SQLite.

A base de dados SQLite está no arquivo Grupo10ODS.db que pode ser baixado aqui.

src_dbi(ods)

## src:  sqlite 3.36.0 [C:\Users\ignit\Documents\Analytics\R\Mandados\Grupo10\Dados\Grupo10ODS.db]
## tbls: sqlite_sequence, TB_Mandado, TB_Processo, TB_Serventia, TB_Tribunal,
##   TB_UF

Verificando as tabelas (tbls) temos:

TB_Mandado
TB_Processo
TB_Serventia
TB_Tribunal
TB_UF

A tabela principal, TB_Mandado, ficou populada desta forma:

SELECT * 
FROM TB_Mandado
LIMIT 5

5 records
CódigoMandado	num_proc	nome	alcunha	mae	pai	dt_nasc	situacao	dt_mand	org_exped	peca	uf	ano_proc
1	0004396-57.2016.8.01.0001.10.0001-25	José Rosario Magalhães do Nascimento	Não informado	Alvacir Arino Nascimento	Maria Demetria Magalhães Nascimento	1970-03-17	Pendente de Cumprimento	2019-06-04	VARA DE EXECUÇÕES PENAIS	Mandado de Internação	AC	2016
2	0012284-14.2015.8.01.0001.10.0001-15	Rosa Maria Alves de Oliveira	Não informado	Raimunda Alves de Oliveira	João José de Oliveira	1969-04-21	Pendente de Cumprimento	2019-08-08	VARA DE EXECUÇÕES PENAIS	Mandado de Internação	AC	2015
3	0002945-02.2013.8.01.0001.01.0001-23	Willian do Nascimento Ferreira	Não informado	Antonieta Nascimento Ferreira	Alipio Vicente Ferreira	1973-07-31	Pendente de Cumprimento	2018-10-30	VARA DE EXECUÇÕES PENAS E MEDIDAS ALTERNATIVAS	Mandado de Prisão	AC	2013
4	0023362-88.2004.8.01.0001.01.0001-17	Genildo de Oliveira	Não Informado	Antonia de Oliveira	Não Informado	1976-08-21	Pendente de Cumprimento	2017-08-16	3ª VARA CRIMINAL	Mandado de Prisão	AC	2004
5	0009159-53.2006.8.01.0001.01.0001-09	Fabio de Almeida Lins	Não Informado	Estefania de Almeida Lins	João Edson dos Santos Lins	1973-04-11	Pendente de Cumprimento	2017-07-23	3ª VARA CRIMINAL	Mandado de Prisão	AC	2006

Após a conferência dos dados, passamos diretamente para a montagem do DW.

3 O Data Warehouse (DW)

O DW ficou com três tabelas Fato, quatro tabelas Dimensão e uma tabela stage:

src_dbi(dw)

## src:  sqlite 3.36.0 [C:\Users\ignit\Documents\Analytics\R\Mandados\Grupo10\Dados\Grupo10DW.db]
## tbls: BI_FT_Data_Mandado_UF, BI_STG_Mandado_UF, BI_TD_JTR_UF, BI_TD_Serventia,
##   BI_TD_Tempo, BI_TD_UF, BI_TF_Despesa_Prisional, BI_TF_Mandado_UF,
##   sqlite_sequence

A base de dados SQLite está no arquivo Grupo10DW.db que pode ser baixado aqui.

A Dimensão Tempo foi criada por script e contém os seguintes campos:

SELECT * 
FROM BI_TD_Tempo
LIMIT 5

5 records
id_tempo	Data	DataTime	Ano	DataCurta	DiaSemana	DiaMes	MesNome	MesNumero	Trimestre	Semestre
1	1900-01-01	1900-01-01 00:00:00	1900	01/01/1900	Segunda-Feira	1	Janeiro	1	1	1
2	1900-01-02	1900-01-02 00:00:00	1900	02/01/1900	Terça-Feira	2	Janeiro	1	1	1
3	1900-01-03	1900-01-03 00:00:00	1900	03/01/1900	Quarta-Feira	3	Janeiro	1	1	1
4	1900-01-04	1900-01-04 00:00:00	1900	04/01/1900	Quinta-Feira	4	Janeiro	1	1	1
5	1900-01-05	1900-01-05 00:00:00	1900	05/01/1900	Sexta-Feira	5	Janeiro	1	1	1

Foi utilizada a técnica de tabela stage para carregar dados de tabelas diretamente no DW para tratamento posterior. A tabela BI_STG_Mandado_UF é assim:

SELECT * 
FROM BI_STG_Mandado_UF
LIMIT 5

5 records
UF	Desp_Total	Pop_Prisional	Total_vagas	Mes	Ano	UF1	Total_mandados	Total_baixado	Total_carregado	Estado	JTR	J	TR	Sigla
AC	-	-	8167	12	2020	AC	2833	2833	2824	Acre	801	8	1	AC
AL	22.647.444,01	6081	5079	12	2020	AL	6768	6768	6716	Alagoas	802	8	2	AL
AM	35.973.167,03	10620	8536	12	2020	AM	7082	7082	7079	Amazonas	804	8	4	AM
AP	7.333.986,04	2940	2844	12	2020	AP	4088	4088	3797	Amapá	803	8	3	AP
BA	61.754.495,00	14502	13242	12	2020	BA	14608	10000	9909	Bahia	805	8	5	BA

Dessa tabela stage foram extraídas as tabelas:

BI_TD_UF
BI_TF_Despesa_Prisional
BI_TF_Madado_UF

A partir do Relacional foram criadas as demais tabelas que compõe o DW.

Estes são os primeiros registros da tabela Fato BI_FT_Data_Mandado_UF:

SELECT * 
FROM BI_FT_Data_Mandado_UF
LIMIT 5

5 records
id_Data_Mandado_UF	num_proc	JTR	origem	DataNascimento	Datamandado	id_uf	ano_proc	IdadequandoRec	decorridomandadoemitido	dt_carga
1	0004396-57.2016.8.01.0001.10.0001-25	801	1	25643	43619	12	2016	49	3	2021-10-17
2	0012284-14.2015.8.01.0001.10.0001-15	801	1	25313	43684	12	2015	50	4	2021-10-17
3	0002945-02.2013.8.01.0001.01.0001-23	801	1	26875	43402	12	2013	45	5	2021-10-17
4	0023362-88.2004.8.01.0001.01.0001-17	801	1	27992	42962	12	2004	41	13	2021-10-17
5	0009159-53.2006.8.01.0001.01.0001-09	801	1	26764	42938	12	2006	44	11	2021-10-17

4 Análise Exploratória dos Dados do DW

Serão utilizadas queries em SQLite para responder perguntas relacionadas aos Mandados em Aberto e ao Sistema Prisional.

4.1 Visão dos Mandados

4.1.1 Quantos mandados estavam em aberto, à espera de cumprimento, no Brasil no dia 08/10/2021?

SELECT SUM(Total_Mandados) AS 'Total de Mandados em Aberto'
FROM BI_TF_Mandado_UF;

1 records
Total de Mandados em Aberto
335522

4.1.2 Qual é o total de mandados que estavam em aberto por Estado?

SELECT 
  b.nome AS 'Estado',
  a.Total_Mandados AS 'Total de Mandados em Aberto'
FROM BI_TF_Mandado_UF a
JOIN BI_TD_UF b
    ON a.id_uf = b.id_uf
ORDER BY 2 DESC;

Displaying records 1 - 10
Estado	Total de Mandados em Aberto
São Paulo	50983
Minas Gerais	34219
Rio de Janeiro	30557
Paraná	17421
Pará	16700
Pernambuco	15030
Bahia	14608
Ceará	14388
Goiás	13801
Maranhão	12298

4.1.3 Dentro do Estado do Rio de Janeiro, qual é a serventia que tem mais mandados à espera de cumprimento?

SELECT  a.Serventias,
        COUNT(b.id_Data_Mandado_UF) AS 'Mandados em aberto'
FROM BI_FT_Data_Mandado_UF b, BI_TD_Serventia a
WHERE JTR='819' 
AND a.id_Serventia = b.origem
GROUP BY 1
ORDER BY 2 DESC;

Displaying records 1 - 10
Serventias	Mandados em aberto
Comarca da Capital	3702
Comarca de Duque de Caxias	539
Comarca de São Gonçalo	462
Comarca de Nova Iguaçu	372
Comarca de Niterói	300
Comarca de Belford Roxo	289
Regional de Jacarepaguá	213
Comarca de Campos dos Goytacazes	209
Regional de Madureira	185
Comarca de Petrópolis	164

4.2 Visão do Sistema Prisional

4.2.1 Qual é a capacidade do Sistema Prisional Brasileiro? Qual é sua população prisional? E qual é a taxa de lotação?

SELECT SUM(Total_vagas) AS 'Capacidade',
       SUM(Pop_Prisional) AS 'População Prisional',
       ROUND(SUM(Pop_Prisional) / CAST(SUM(Total_vagas) AS DOUBLE) * 100, 2) AS 'Taxa de Lotação%'
FROM BI_TF_Despesa_Prisional;

1 records
Capacidade	População Prisional	Taxa de Lotação%
545163	693632	127.23

Nota: Dados de Dezembro de 2020. Os Estados do Acre, Santa Catarina, Sergipe e Tocantins não prestaram informações.

4.2.2 Qual é a capacidade, população prisional e a taxa de lotação por Estado?

SELECT Nome AS 'Estado',
       Total_vagas AS 'Capacidade',
       Pop_Prisional AS 'População Prisional',
       ROUND(SUM(Pop_Prisional) / CAST(SUM(Total_vagas) AS DOUBLE) * 100, 1) AS 'Taxa de Lotação%'
FROM BI_TF_Despesa_Prisional a
JOIN BI_TD_UF b
    ON a.id_uf = b.id_uf
GROUP BY Nome
ORDER BY 4 DESC;

Displaying records 1 - 10
Estado	Capacidade	População Prisional	Taxa de Lotação%
Roraima	1413	4195	296.9
Pernambuco	13829	32551	235.4
Paraná	33127	72130	217.7
Mato Grosso do Sul	11282	19954	176.9
Espírito Santo	15131	22397	148.0
Pará	13519	19348	143.1
São Paulo	149759	212672	142.0
Minas Gerais	49003	62317	127.2
Rondônia	11122	14053	126.4
Amazonas	8536	10620	124.4

Nota: Dados de Dezembro de 2020. Os Estados do Acre, Santa Catarina, Sergipe e Tocantins não prestaram informações. Além disso, a alta discrepância entre os valores aponta a necessidade da validação dos dados em estudo mais detalhado.

4.3 Impacto dos Mandados Sobre o Sistema Prisional

4.3.1 Qual é o estado que mais sobrecarregaria (proporcionalmente) seu sistema prisional se cumprisse todos os mandados?

SELECT  c.nome AS 'Estado',
        CAST(a.Pop_Prisional AS INTEGER) AS 'População Prisional',
        b.Total_mandados AS 'Mandados em aberto',
        ROUND(CAST(b.Total_mandados AS DOUBLE) / CAST(a.Pop_Prisional AS DOUBLE) * 100, 2) AS 'Aumento%'
FROM BI_TF_Despesa_Prisional a
JOIN BI_TF_Mandado_UF b
    ON a.id_uf = b.id_uf
JOIN BI_TD_UF c
    ON a.id_uf = c.id_uf
ORDER BY 4 DESC;

Displaying records 1 - 10
Estado	População Prisional	Mandados em aberto	Aumento%
Amapá	2940	4088	139.05
Alagoas	6081	6768	111.30
Bahia	14502	14608	100.73
Maranhão	12827	12298	95.88
Piauí	4752	4547	95.69
Pará	19348	16700	86.31
Paraíba	12653	10792	85.29
Rio Grande do Norte	9248	7278	78.70
Goiás	18921	13801	72.94
Mato Grosso	15872	11161	70.32

4.3.2 Qual seria o custo total prisional do cumprimento de todos os mandados?

SELECT  SUM(b.Total_mandados) AS 'Mandados em aberto',
        SUM(a.Desp_Total / CAST(a.Pop_Prisional AS DOUBLE) * b.Total_mandados) AS 'Aumento Mensal(Reais)',
        SUM(a.Desp_Total / CAST(a.Pop_Prisional AS DOUBLE) * b.Total_mandados * 12) AS 'Aumento Anual(Reais)'
FROM BI_TF_Despesa_Prisional a
JOIN BI_TF_Mandado_UF b
    ON a.id_uf = b.id_uf
JOIN BI_TD_UF c
    ON a.id_uf = c.id_uf;;

1 records
Mandados em aberto	Aumento Mensal(Reais)	Aumento Anual(Reais)
335522	575697720	6908372643

4.3.3 Qual seria o custo total prisional do cumprimento de todos os mandados por Estado?

SELECT  c.nome AS 'Estado',
        a.Pop_Prisional AS 'População Prisional',
        a.Desp_Total AS 'Despesa Mensal',
        ROUND(a.Desp_Total / a.Pop_Prisional, 2) AS 'Despesa por Detento',      
        b.Total_mandados AS 'Mandados em aberto',
        (a.Desp_Total / CAST(a.Pop_Prisional AS DOUBLE)) * b.Total_mandados AS 'Aumento R$'
FROM BI_TF_Despesa_Prisional a
JOIN BI_TF_Mandado_UF b
    ON a.id_uf = b.id_uf
JOIN BI_TD_UF c
    ON a.id_uf = c.id_uf
ORDER BY 6 DESC;

Displaying records 1 - 10
Estado	População Prisional	Despesa Mensal	Despesa por Detento	Mandados em aberto	Aumento R$
São Paulo	212672	649399268	3053.52	50983	155677865
Minas Gerais	62317	150969327	2422.60	34219	82899039
Mato Grosso	15872	57703538	3635.56	11161	40576436
Ceará	22492	48527390	2157.54	14388	31042685
Goiás	18921	36119576	1908.97	13801	26345662
Alagoas	6081	22647444	3724.30	6768	25206035
Maranhão	12827	25344575	1975.88	12298	24299336
Amazonas	10620	35973167	3387.30	7082	23988886
Paraná	72130	98222744	1361.75	17421	23722978
Espírito Santo	22397	48591159	2169.54	10783	23394136

4.4 Outras perguntas que podem ser respondidas

4.4.1 A dimensão tempo pode ajudar a responder perguntas sobre:

4.4.1.1 Quantos mandados foram emitidos por mês em 2020?

SELECT Ano,
       Semestre,
       Mesnome,
       COUNT(1) AS QtdMandados
  FROM BI_FT_Data_Mandado_UF a,
       BI_TD_Tempo b
 WHERE a.Datamandado = id_tempo AND 
       Ano = 2020
 GROUP BY Ano,
          Semestre,
          Mesnome
 ORDER BY id_tempo;

Displaying records 1 - 10
Ano	Semestre	MesNome	QtdMandados
2020	1	Janeiro	2407
2020	1	Fevereiro	2389
2020	1	Março	2552
2020	1	Abril	2079
2020	1	Maio	2178
2020	1	Junho	2265
2020	2	Julho	2898
2020	2	Agosto	2848
2020	2	Setembro	3174
2020	2	Outubro	3107

4.4.1.2 Qual é a média de idade das pessoas com mandado em aberto por Estado?

SELECT  b.nome AS 'Estado',
        ROUND(AVG(a.idadequandoRec)) AS 'Média de Idade'
FROM BI_FT_Data_Mandado_UF a
JOIN BI_TD_UF b
    ON a.id_uf = b.id_uf
GROUP BY 1
ORDER BY 2 DESC;

Displaying records 1 - 10
Estado	Média de Idade
Santa Catarina	40
Rondônia	40
Mato Grosso	40
Tocantins	39
Goiás	39
São Paulo	38
Roraima	38
Rio Grande do Sul	38
Paraíba	38
Paraná	38

4.4.1.3 Histograma da Idade de Pessoas com Mandado em Aberto

4.4.1.4 Qual é a duração média dos processos com mandado em aberto por Estado?

SELECT  b.nome AS 'Estado',
        ROUND(AVG(date('now') - a.ano_proc),1) AS 'Média de Duração do Processo' 
FROM BI_FT_Data_Mandado_UF a
JOIN BI_TD_UF b
    ON a.id_uf = b.id_uf
GROUP BY 1
ORDER BY 2 DESC;

Displaying records 1 - 10
Estado	Média de Duração do Processo
Pernambuco	9.6
Ceará	9.4
Paraíba	9.3
Espírito Santo	9.3
Maranhão	8.9
Rondônia	8.8
Pará	8.8
Goiás	8.6
Sergipe	8.4
Mato Grosso	8.4

Projeto de Banco de Dados - Grupo 10

Carolina Haber, Márcio Gonzalez e Rogério B Alves

25/10/2021

Objetivos do trabalho: