Bando de Dados lII Temporada

Prévia do material em texto

1º Desafio - Compreender as responsabilidades do Administrador de Banco de Dados
Em um banco de dados de uma empresa de logística, Daniela, a DBA da empresa, tem um problema. Ela precisa que alguns usuários de cada departamento da empresa, via sistema, consigam efetuar algumas rotinas SQL no banco. Esse pedido veio da parte dos programadores do sistema. Portanto, os usuários (funcionários) da empresa, Marcos, Lais, Sandro e Ramirez, necessitam ter privilégios diferenciados nas tabelas de produtos e estoque. Descreva os procedimentos que Daniela deverá realizar para resolver o problema na empresa.
Padrão de resposta esperado
Uma das responsabilidades do administrador de banco de dados é prezar pela segurança do banco. Portanto, para que Daniela consiga efetuar o procedimento é necessário:
1) Criar uma regra para agrupar estes usuários com o comando CREATE ROLE GERENTES.
2) Agora devemos informar os objetos que terão inferência sobre a ROLE criada GRANT ALL ON PRODUTOS TO GERENTES; GRANT ALL ON ESTOQUE TO GERENTES.
3) Para finalizar, devemos cadastrar os usuários na ROLE GERENTES GRANT GERENTES TO MARCOS,LAIS,SANDRO,RAMIREZ.
2º Desafio – Projeto de banco de dados: modelos conceitual, lógico e físico
Atualmente, manter um sistema de gerenciamento de banco de dados (SGBD) confiável e eficaz é vital para o bom andamento das operações de qualquer organização, desde pequenas empresas até as chamadas multinacionais. Por meio de um banco de dados e seu SGBD, é factível que os dados possam ser armazenados de forma organizada e consistente e que permaneçam disponíveis para consulta, em tempo hábil, aos interesses da empresa e de seus usuários. Porém, o que aconteceria se uma grande empresa multinacional não investisse adequadamente nesses sistemas? Neste Desafio, acompanhe o cenário em que uma empresa necessita realizar um novo projeto de banco de dados: 
Com base nesse cenário, você deve descrever os passos a serem seguidos para propor um projeto de banco de dados que ofereça desempenho satisfatório durante o acesso ao SGBD utilizado pelo sistema para essa empresa.
Sua resposta deve:
- utilizar as principais fases para elaboração de um projeto de banco de dados:
• fase 1: levantamento e análise de requisitos;
• fase 2: projeto conceitual;
• fase 3: projeto lógico; e
• fase 4: projeto físico
- apresentar, para cada uma das fases, os principais elementos ou recursos que devem estar na lista de tarefas para a sua correta execução e que melhorariam o desempenho do SGBD como um todo nessa empresa, explicando por quê.
Padrão de resposta esperado
Fase 1: levantamento e análise de requisitos
O primeiro elemento desse projeto de banco de dados refere-se à entrevista a ser realizada com a equipe responsável pelo projeto para que haja entendimento e sejam documentados os requisitos de dados e os requisitos funcionais. Deve-se atentar para a escolha da arquitetura para instalação do SGBD, conforme o levantamento de requisitos realizado. Nesse caso, levando em consideração que a organização tem matriz e filiais, sugere-se que a arquitetura cliente/servidor seria a mais adequada, pois ela divide as responsabilidades do sistema entre as máquinas do tipo clientes (onde o sistema será instalado nas filiais) e a máquina do tipo servidor (onde o SGBD será instalado na matriz).
Fase 2: projeto conceitual
Nesta etapa, deve ser aplicada a modelagem conceitual. Levando em consideração que a organização necessita de integridade e confiabilidade entre as informações, sugere-se a utilização de um modelo de dados relacional. Esta etapa é o momento de realizar a modelagem das entidades e o estabelecimento das relações existentes entre elas, bem como a definição dos atributos. É necessário que seja elaborado um modelo entidade-relacionamento (MER) que contemple o cenário identificador na análise de requisitos.
Fase 3: projeto lógico
Nesta etapa, deve ser aplicada a modelagem lógica. Consideram-se não só os relacionamentos entre as tabelas, como também as possíveis necessidades de consultas para relatórios externos. É necessário elaborar o diagrama entidade-relacionamento (DER), pois uma característica muito importante do modelo relacional é a utilização de restrições para garantir a integridade dos dados. As restrições ou regras de integridade mais comuns em um modelo de dados relacional são as chaves primárias e as chaves estrangeiras. Outro predicado importante do modelo relacional são as formas de normalização do modelo do banco de dados. A forma como as tabelas forem elaboradas e relacionadas entre si pode impactar drasticamente no desempenho do SGBD.
Fase 4: projeto físico
Definida a modelagem lógica, o próximo passo é a escolha do SGBD para criação do banco de dados. É necessário considerar o alto fluxo de dados da aplicação. Os SGBDs relacionais oferecem controle de transações, controle de concorrência, validação dos dados, verificação e garantias de integridade dos dados, segurança, otimização de consultas e recuperação de falhas. A utilização desses recursos, de forma geral, irá facilitar o trabalho de implementação do banco de dados. Por exemplo, o MySQL poderia ser um tipo de SGBD a ser escolhido, pois vem sendo utilizado em projetos de grande porte e em grandes empresas. Ele oferece excelente desempenho e estabilidade, além de exigir pouco em relação aos recursos de hardware.
Ainda nesta etapa, pode ser realizada a migração dos dados do SGBD antigo para o novo, ou, dependendo da necessidade do cliente, realizar as etapas de criação efetiva de um novo banco de dados, com a sequência de criação das tabelas e o estabelecimento das chaves primárias e estrangeiras descritas no modelo. Esta é uma etapa importante, pois permitirá a correta relação entre os dados armazenados.
Com o banco de dados criado, será necessário desenvolver ou ajustar as consultas conforme as especificações do sistema e do novo SGBD. A orientação deve ser sempre a criação de consultas objetivas, simples e claras que auxiliem na performance do banco de dados. Durante o processo de desenvolvimento das consultas, o objetivo é evitar redundâncias, de modo a otimizar consultas e extrair o melhor resultado possível da programação SQL.
_____________________________________________________________________________
3º Desafio – Conhecer a utilização de ETL - Extract Transform Load (Extração, Transformação e Carga)
Hospitais e clínicas de saúde possuem um processo para o recebimento de visitantes pelos pacientes internados. O Hospital de Doenças Respiratórias, onde você trabalha como analista de Business Inteligence, está adotando medidas mais restritas para o acesso de visitantes. Isso ocorre devido à possibildade de os visitantes terem contato com pessoas contaminadas com determinada doença respiratória. Antes, os visitantes eram liberados nos horários de visita pré-estabeleciddos, sem necessidade de cadastro.
Contudo, atualmente a equipe de recepção do hospital passou a fazer o cadastro dos visitantes, para que possam entrar em contato para avisar as pessoas, se identificarem que podem ter tido contato com outra pessoa contaminada com a referida doença.
Os dados são salvos diariamente em uma planilha do Microsoft Excel, pelos diferentes funcionários que trabalham em 3 turnos. Abaixo temos uma figura que ilustra a planilha.
Você foi solicitado a inserir esses dados em um data warehouse. Você identifica algum problema a ser resolvido nesses dados? Quais são os passos de transformação que você faria? Com que objetivo? Mostre como ficaria a planilha caso identifique algum ajuste a ser feito.
Padrão de resposta esperado
Para a carga dos dados no data warehouse, é necessário fazer a padronização dos dados no processo de transformação. Os dados apresentam problemas de padronização nos campos telefone, parentesco, data da visita, idade e vacinado. Esses dados precisam ser padronizados conforme exemplo abaixo: 
​​​​​​​
Com a padronização, será mais eficiente a geração de informações sobre os dados coletados, qe poderão ser utilizados para análises de negócio,por exemplo, identificando quais dias da semana ocorrem mais visitas, para que a equipe de recepção esteja mais preparada para o recebimento dos visitantes.
4º Desafio - Utilizar rotinas de programação em Banco de Dados
Você trabalha em uma empresa de cosméticos e o banco de dados da sua empresa possui aproximadamente 150 tabelas, entre elas a tabela Produto. Todos os dias, o sistema emite um relatório dos últimos produtos inseridos. Logo, uma consulta é realizada fazendo a ordenação dos produtos adicionados no dia de forma decrescente pela coluna data_criacao.
No entanto, o sistema é utilizado por diversas filiais que rodam instâncias separadas do sistema e, uma possível alteração na tabela implicaria em uma alteração no código de cada uma dessas filiais.
Como forma de facilitar a manutenção desse sistema, você poderia criar uma procedure para executar o seguinte comando SQL.
SELECT nome, descricao, valor FROM produto WHERE DATE(data_criacao)=CURDATE() ORDER BY data_criacao DESC
No comando, é realizado um SELECT pedindo os valores de nome, descrição e valor da tabela Produto. A ordenação é feita pela data de criação de forma descentende.
Também é preciso limitar, com a cláusula WHERE, para retornar apenas os registros criados no dia (com CURDATE()).
Sua missão é:
1 - Criar e descrever o comando de uma PROCEDURE que consulta todos os produtos da tabela ‘Produto’ ordenando pela data de criação em ordem decrescente.
2 - Descrever o comando para executar esta procedure.
3 - Modificar a procedure criada para pesquisar por produtos pela coluna nome.
4 - Descrever como ficaria o comando para executar a procedure do item 3, buscando pelo produto “batom”.
Escreva sua resposta no campo abaixo:
​
Padrão de resposta esperado
Os comandos para a criação da procedure são:
CREATE PROCEDURE sp_produto AS
SELECT nome, descricao, valor FROM produto WHERE
DATE(data_criacao)=CURDATE() ORDER BY data_criacao DESC
Para executar o procedure será preciso:
EXECUTE sp_produto
Para criar procedure para pesquisa pelo nome:
CREATE PROCEDURE sp_produto AS
SELECT nome, descricao, valor FROM produto
WHERE DATE(data_criacao)=CURDATE()
AND nome LIKE ‘%’ + @Nome + ‘%’
ORDER BY data_criacao DESC
Para executar procedure que busca pelo nome:
EXECUTE sp_produto_nome ‘Batom’