Estude fácil! Tem muito documento disponível na Docsity
Ganhe pontos ajudando outros esrudantes ou compre um plano Premium
Prepare-se para as provas
Estude fácil! Tem muito documento disponível na Docsity
Prepare-se para as provas com trabalhos de outros alunos como você, aqui na Docsity
Os melhores documentos à venda: Trabalhos de alunos formados
Prepare-se com as videoaulas e exercícios resolvidos criados a partir da grade da sua Universidade
Responda perguntas de provas passadas e avalie sua preparação.
Ganhe pontos para baixar
Ganhe pontos ajudando outros esrudantes ou compre um plano Premium
Comunidade
Peça ajuda à comunidade e tire suas dúvidas relacionadas ao estudo
Descubra as melhores universidades em seu país de acordo com os usuários da Docsity
Guias grátis
Baixe gratuitamente nossos guias de estudo, métodos para diminuir a ansiedade, dicas de TCC preparadas pelos professores da Docsity
Modelagem, importação de dados e performance de consultas no MySQL.
Tipologia: Trabalhos
1 / 14
Esta página não é visível na pré-visualização
Não perca as partes importantes!
Número total de casos de COVID-19.
Número de mortos por COVID-19 por 100 mil habitantes.
Número de casos de COVID-19 por 100 mil habitantes.
Razão entre o total de mortos e o total de casos de COVID-19.
Indica a fonte dos dados.
Indica a data da última atualização dos dados. Está no formato padrão do MySQL, porém as linhas sem a especificação da localização apresentam o campo como null.
A base de dados utilizada trouxe 15 colunas, entretanto eliminaremos 7 colunas na migração dos dados para o MySQL, pois são informações derivadas de outras colunas e, assim, podem ser descartadas para melhorar a estrutura da tabela auxiliar que será criada no banco de dados. A coluna country pode ser descartada por que não é necessário manter as linhas dos totais parciais (cuja existência é explicada na descrição dessa coluna), já que se trata de informação derivada das linhas anteriores. As colunas deaths, totalCases, deaths_per_100k_inhabitants, totalCases_per_100k_inhabitants e deaths_by_totalCases são todas derivadas de informações de colunas adjacentes e, portanto, também não precisam ser mantidas. A coluna _source, que indica a origem dos dados, não precisa ser mantida por uma questão prática, já que não influenciará diretamente na análise dos dados e pode facilmente ser consul- tada no arquivo de origem. Criaremos um banco de dados, chamado covid, para receber a estrutura de tabelas que des- creveremos a seguir. A tabela auxiliar será chamada covid_completa e possuirá as seguintes colunas:
semana_epi data ibge_id novas_mortes novos_casos data_ultima_info
Tabela 1: registros - 1FN
O diagrama ER, apresentado na figura 1, pode ser descrito em mais detalhes a partir de um modelo ER. Para tanto, é necessário avaliar os tipos de cada atributo especificado na figura. O campo nome na tabela estado é um campo de texto simples, contendo apenas dois carac- teres alfabéticos. Logo, o mesmo pode ser modelado como varchar( 2 ). O campo ibge_id na tabela municipios será modelado como int( 7 ), pois é o padrão do IBGE. Esse campo será usado como chave primária; optamos por não criar uma chave primária auto- incremental, mantendo assim o código do IBGE para futuras referências. O campo nome na tabela municipios pode ser modelado como varchar( 35 ), pois o mu- nicípio brasileiro com maior nome é “Vila Bela da Santíssima Trindade” em “MT”, com 32 caracteres; os 3 caracteres adicionais são colocados para comportar uma barra e o símbolo do estado, ficando o registro assim: “Vila Bela da Santíssima Trindade/MT”. O campo semana_epi na tabela registros será modelado como int( 3 ). Como explicado na Seção 1, o primeiro dígito indica o ano e os dois últimos a semana. Os campos data e data_ultima_info na tabela registros serão modelados como date. Por último, ainda na tabela registros, modelaremos o campo novas_mortes com int( 4 ) e novos_casos com int( 6 ). A razão para as escolhas destes números são as informações divulga- das de que não houve até o presente momento mais que 10 000 novas mortes diárias e mais que 1 000 000 de novos casos diários. Na verdade, os valores são muito menores, mas extrapolamos os campos para não termos problemas em futuras inserções de dados. Portanto, o modelo ER ficará como mostrado abaixo.
Segue o script de criação das tabelas do nosso banco de dados:
create database covid character set utf8mb collate utf8mb4_0900_ai_ci;
use covid;
create table estados( id int not null auto_increment, nome varchar(2) not null, primary key(id));
create table municipios( ibge_id int(7) not null, nome varchar(35) not null, estado_id int not null, primary key(ibge_id), foreign key(estado_id) references estados(id));
create table registros( id int not null auto_increment, semana_epi int(3) not null, data date not null, data_ultima_info date not null, ibge_id int(7) not null, novas_mortes int(4) not null, novos_casos int(6) not null, primary key(id), foreign key(ibge_id) references municipios(ibge_id));
create table covid_completa( semana_epi int(3), data date, estado varchar(5), municipio varchar(35), ibge_id int(7), novas_mortes int(4), novos_casos int(6), data_ultima_info varchar(10));
Buscaremos a melhoria de desempenho no banco de dados a partir da criação de índices. A tabela estados possui apenas 27 registros, correspondentes às siglas das UF no Brasil. Como são poucos dados e essas informações não serão alteradas, então não é necessário criar índices. Na tabela municipios, criaremos um índice na coluna nome; apesar desta coluna conter apenas 5596 registros, são distintos e, portanto, deve compensar a criação de índice. Segue, abaixo, o comando de criação:
alter table municipios add index idx_nome(nome); As outras colunas da tabela municipios já possuem índices, pois são ou chave primária (coluna ibge_id) ou chave estrangeira (coluna estado_id). Assim, nada mais há para fazer nesse sentido. Com o índice criado na tabela municipios já podemos observar uma melhoria de desempe- nho. Antes da criação do índice, realizamos a seguinte consulta:
select r.novas_mortes, r.novos_casos, r.data from registros as r, municipios as m where m.ibge_id = r.ibge_id and m.nome like "%CASO SEM LOCALIZA% DEFINIDA/MT"; A resposta da consulta foi: 530 rows in set Time: 0.251s Após a criação do índice, obtivemos a seguinte resposta ao refazer a consulta: 530 rows in set Time: 0.056s Assim, obtivemos o seguinte ganho:
(Tempo obtido sem index) − (Tempo obtido com index) = 0. 251 − 0. 056 = 0 .195 segundo.
Em outras palavras, a consulta levou 195 milésimos de segundo a menos para ser concluída, correspondente a aproximadamente 79, 69 % menos tempo de processamento.
Na tabela registros, a coluna semana_epi possui apenas 80 registros distintos; ainda assim, criaremos um índice para a coluna semana_epi, pois existem 2 679 384 linhas na tabela regis- tros, todas com referência à semana epidemiológica. Segue, abaixo, o comando de criação:
alter table registros add index idx_semana_epi(semana_epi); Com o índice criado na coluna semana_epi da tabela registros já podemos observar uma melhoria de desempenho. Antes da criação do índice, realizamos a seguinte consulta para determinar a média de novas mortes na semana epidemiológica 12:
select sum(novas_mortes)/count(distinct data) as media from registros where semana_epi = 12 and novas_mortes != 0 having count(data); A resposta da consulta foi: 1 row in set Time: 0.397s Após a criação do índice, obtivemos a seguinte resposta ao refazer a consulta: 1 row in set Time: 0.008s Assim, obtivemos o seguinte ganho:
(Tempo obtido sem index) − (Tempo obtido com index) = 0. 397 − 0. 008 = 0 .389 segundo.
Em outras palavras, a consulta levou 389 milésimos de segundo a menos para ser concluída, correspondente a aproximadamente 97, 99 % menos tempo de processamento.
Seguindo a mesma linha de raciocínio utilizada para justificar a criação de um índice na coluna semana_epi, criaremos um índice para cada uma das outras colunas da tabela registros (exceto, é claro, nas colunas id e ibge_id, que por serem chaves primária e estrangeira, respec- tivamente, já possuem índice). Segue, abaixo, os comandos de criação dos novos índices e os resultados de algumas consultas antes e depois da criação.
Índice na coluna data: alter table registros add index idx_data(data); Consulta da quantidade de dados alterados em Primavera do Leste - MT: select data_ultima_info, data, novas_mortes from registros as r, municipios as m where m.ibge_id = r.ibge_id and m.nome like "Primavera do Leste/MT" and r.data_ultima_info != data; Resposta sem o índice: 512 rows in set Time: 0.317s Resposta com o índice: 512 rows in set Time: 0.137s Ganho obtido: 56, 78 %
Índice na coluna data_ultima_info:
select m.nome, r.novos_casos, r.data from municipios as m, registros as r where m.ibge_id = r.ibge_id and r.novos_casos = (select max(novos_casos) from registros ); Resposta sem índice na coluna novos_casos: 1 row in set Time: 1.793s Resposta com índice: 1 row in set Time: 0.013s Ganho obtido: 99, 28 %
Conclusão: Em todas as consultas os ganhos obtidos com a utilização de um ou mais índices foram bastante significativos, chegando a um surpreendente ganho de 99, 28 % na última consulta aqui realizada.
[1] Cota, W. Monitoring the number of COVID- 19 cases and deaths in brazil at municipal and federative units level. SciELOPreprints:362 (2020), 10.1590/scielopreprints.362.
[2] COVID- 19 NO BRASIL Disponível em: https://qsprod.saude.gov.br/extensions/covid- 19_html/covid-19_html.html. Acesso em: 25 de ago. 2021.
