Baixe o app para aproveitar ainda mais
Prévia do material em texto
1 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO BANCO DE DADOS II 2 BANCO DE DADOS II FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017SUMÁRIO A Faculdade Multivix está presente de norte a sul do Estado do Espírito Santo, com unidades em Cachoeiro de Itapemirim, Cariacica, Castelo, Nova Venécia, São Mateus, Serra, Vila Velha e Vitória. Desde 1999 atua no mercado capixaba, des- tacando-se pela oferta de cursos de gradua- ção, técnico, pós-graduação e extensão, com qualidade nas quatro áreas do conhecimen- to: Agrárias, Exatas, Humanas e Saúde, sem- pre primando pela qualidade de seu ensino e pela formação de profissionais com cons- ciência cidadã para o mercado de trabalho. Atualmente, a Multivix está entre o seleto grupo de Instituições de Ensino Superior que possuem conceito de excelência junto ao Ministério da Educação (MEC). Das 2109 institui- ções avaliadas no Brasil, apenas 15% conquistaram notas 4 e 5, que são consideradas conceitos de excelência em ensino. Estes resultados acadêmicos colocam todas as unidades da Multivix entre as melhores do Estado do Espírito Santo e entre as 50 melhores do país. MISSÃO Formar profissionais com consciência cida- dã para o mercado de trabalho, com ele- vado padrão de qualidade, sempre mantendo a credibilidade, segurança e modernidade, visando à satisfação dos clientes e colaboradores. VISÃO Ser uma Instituição de Ensino Superior reconheci- da nacionalmente como referência em qualidade educacional. GRUPO MULTIVIX 3 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO BIBLIOTECA MULTIVIX (Dados de publicação na fonte) As imagens e ilustrações utilizadas nesta apostila foram obtidas no site: http://br.freepik.com Edward Costa Boncompagni Charles Dificuldades de Aprendizagem / Edward Costa Boncompagni Charles. – Serra: Multivix, 2018. EDITORIAL Catalogação: Biblioteca Central Anisio Teixeira – Multivix Serra 2019 • Proibida a reprodução total ou parcial. Os infratores serão processados na forma da lei. FACULDADE CAPIXABA DA SERRA • MULTIVIX Diretor Executivo Tadeu Antônio de Oliveira Penina Diretora Acadêmica Eliene Maria Gava Ferrão Penina Diretor Administrativo Financeiro Fernando Bom Costalonga Diretor Geral Helber Barcellos da Costa Diretor da Educação a Distância Pedro Cunha Conselho Editorial Eliene Maria Gava Ferrão Penina (presidente do Conselho Editorial) Kessya Penitente Fabiano Costalonga Carina Sabadim Veloso Patrícia de Oliveira Penina Roberta Caldas Simões Revisão de Língua Portuguesa Leandro Siqueira Lima Revisão Técnica Alexandra Oliveira Alessandro Ventorin Graziela Vieira Carneiro Design Editorial e Controle de Produção de Conteúdo Carina Sabadim Veloso Maico Pagani Roncatto Ednilson José Roncatto Aline Ximenes Fragoso Genivaldo Félix Soares Multivix Educação a Distância Gestão Acadêmica - Coord. Didático Pedagógico Gestão Acadêmica - Coord. Didático Semipresencial Gestão de Materiais Pedagógicos e Metodologia Direção EaD Coordenação Acadêmica EaD 4 BANCO DE DADOS II FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017SUMÁRIO Aluno (a) Multivix, Estamos muito felizes por você agora fazer parte do maior grupo educacional de Ensino Superior do Espírito Santo e principalmente por ter escolhido a Multivix para fazer parte da sua trajetória profissional. A Faculdade Multivix possui unidades em Cachoei- ro de Itapemirim, Cariacica, Castelo, Nova Venécia, São Mateus, Serra, Vila Velha e Vitória. Desde 1999, no mercado capixaba, destaca-se pela oferta de cursos de graduação, pós-graduação e extensão de qualidade nas quatro áreas do conhecimento: Agrárias, Exatas, Humanas e Saúde, tanto na mo- dalidade presencial quanto a distância. Além da qualidade de ensino já comprova- da pelo MEC, que coloca todas as unidades do Grupo Multivix como parte do seleto grupo das Instituições de Ensino Superior de excelência no Brasil, contando com sete unidades do Grupo en- tre as 100 melhores do País, a Multivix preocupa- -se bastante com o contexto da realidade local e com o desenvolvimento do país. E para isso, pro- cura fazer a sua parte, investindo em projetos so- ciais, ambientais e na promoção de oportunida- des para os que sonham em fazer uma faculdade de qualidade mas que precisam superar alguns obstáculos. Buscamos a cada dia cumprir nossa missão que é: “Formar profissionais com consciência cidadã para o mercado de trabalho, com elevado padrão de quali- dade, sempre mantendo a credibilidade, segurança e modernidade, visando à satisfação dos clientes e colaboradores.” Entendemos que a educação de qualidade sempre foi a melhor resposta para um país crescer. Para a Multivix, educar é mais que ensinar. É transformar o mundo à sua volta. Seja bem-vindo! APRESENTAÇÃO DA DIREÇÃO EXECUTIVA Prof. Tadeu Antônio de Oliveira Penina Diretor Executivo do Grupo Multivix 5 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO LISTA DE FIGURAS > FIGURA 2 - Painel de controle XAMPP 17 > FIGURA 3 - Representação do conjunto resultado INNER JOIN 34 > FIGURA 4 - Representação do conjunto resultado LEFT JOIN 35 > FIGURA 5 - Representação do conjunto resultado RIGHT JOIN 35 > FIGURA 6 - Representação do conjunto resultado FULL JOIN 36 > FIGURA 7 - Papéis dos sistemas de informação nos níveis organizacionais 43 > FIGURA 8 - Os quatro componentes de BI 46 > FIGURA 9 - Ambiente de banco de dados no ecossistema de BI 47 > FIGURA 10 - Diferentes arquiteturas de acesso ao ambiente de DW 50 > FIGURA 11 - Representação de Data Mart como subconjuntos resultado do DW 51 > FIGURA 12 - Inclusão do ODS no ambiente de DW 53 > FIGURA 13 - Processo de ETL 55 > FIGURA 14 - Arquitetura de um DW empresarial 56 > FIGURA 15 - Arquitetura de Data Mart 57 > FIGURA 16 - Arquitetura em estrela de Data Mart 57 > FIGURA 17 - Arquiteturas alternativas para empreendimentos de DW 58 > FIGURA 18 - Abstração conceitual de integração de banco de dados 66 > FIGURA 19 - Cenário conceitual simplificado de login em uma aplicação 68 > FIGURA 20 - Tela inicial da IDE Visual Studio Community 72 > FIGURA 21 - Figura 4 – Tela do administrador de fontes de dados ODBC 73 > FIGURA 22 - Exemplo de arquivo XML 77 > FIGURA 23 - Exemplo de retorno XML a uma consulta 78 > FIGURA 24 - O conceito de Big Data 86 6 BANCO DE DADOS II FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017SUMÁRIO LISTA DE FIGURAS > FIGURA 25 - Arquitetura do Sedna 112 > FIGURA 26 - Organização e armazenamento de dados Sedna 114 > FIGURA 27 - Funcionamento conceitual de login 122 7 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO LISTA DE QUADROS > QUADRO 1 - Operadores na cláusula WHERE 21 > QUADRO 2 - Dados na tabela pedidos 33 > QUADRO 3 - Dados na tabela clientes 33 > QUADRO 4 - Conjunto resultado da instrução INNER JOIN (pedidos e clientes) 34 > QUADRO 5 - Outras características de Data Warehouse 49 > QUADRO 6 - Comparativo BI versus Big Data Analytics 84 > QUADRO 7 - Comparativo entre Analista de BI e Cientista de Dados 95 > QUADRO 8 - Resumo comparativo entre BD convencional e não convencional 104 > QUADRO 9 - Comparativo de terminologia entre bancos de dados SQL e NoSQL 110 > QUADRO 10 - Comparativo entre BDR x BDOO x BDOR 116 > QUADRO 11 - Explanação de invasão SQL Injection simplificada 122 8 BANCODE DADOS II FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017SUMÁRIO SUMÁRIO 1UNIDADE 2UNIDADE 1 PRÁTICA EM DECLARAÇÕES DE CONSULTAS SQL 15 1.1 CONCEITOS BÁSICOS SOBRE SGBD 15 1.1.1 SERVIDOR LOCAL - XAMPP 16 1.1.2 AS PARTES DA SQL - DDL E DML 18 1.2 CONSULTAS EM SQL 18 1.2.1 INSTRUÇÃO SELECT 19 1.2.2 ALIAS (AS) 20 1.2.3 CLÁUSULA WHERE 21 1.2.4 OPERADORES AND, OR, NOT 22 1.2.5 CLÁUSULA LIMIT 24 1.2.6 FUNÇÕES AGREGADAS MIN() E MAX() 25 1.2.7 FUNÇÃO AGREGADA COUNT() 27 1.2.8 FUNÇÕES AGREGADAS AVG(), SUM() 27 1.2.9 OPERADOR LIKE 28 1.2.10 OPERADORES IN, NOT IN 29 1.2.11 ORDER BY E GROUP BY 31 1.3 ENTENDENDO JUNÇÕES DE TABELAS COM INNER JOIN 32 1.3.1 LEFT JOIN 35 1.3.2 RIGHT JOIN 35 1.3.3 FULL OUTER JOIN 36 1.4 STORED PROCEDURES 36 CONCLUSÃO 39 2 BANCO DE DADOS NO CONTEXTO DE BUSINESS INTELLIGENCE 42 2.1 SISTEMAS TRANSACIONAIS COMO FONTE DE DADOS 42 2.2 BUSINESS INTELLIGENCE (BI) 45 2.2.1 COMPONENTES DE BI 46 2.3 O DATA WAREHOUSE (DW) 47 2.3.1 CARACTERÍSTICAS DO DW 48 2.3.2 ARQUITETURAS DE ACESSO AO DW 49 2.3.3 COMPOSIÇÃO DO DW 51 2.3.4 DATA MART 51 9 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO 3UNIDADE SUMÁRIO 4UNIDADE 2.3.5 DATA STORES OPERACIONAIS (ODS) 52 2.4 EXTRAÇÃO, TRANSFORMAÇÃO E CARGA (ETL) 54 2.5 OUTRAS ARQUITETURAS DE UM AMBIENTE DE DW 56 2.6 O SUCESSO NA ADOÇÃO DE DATA WAREHOUSE 59 CONCLUSÃO 61 3 INTEGRAÇÃO DE BANCO DE DADOS E INTERNET 64 3.1 ENTENDENDO SOBRE INTEGRAÇÃO 64 3.2 INTEGRAÇÃO DE LINGUAGEM COM BANCO DE DADOS 66 3.2.1 O CONTEXTO DA SQL NA INTEGRAÇÃO DE LINGUAGEM E BD 67 3.3 API ODBC PARA CONEXÃO AO BANCO DE DADOS 70 3.3.1 ODBC NA INTEGRAÇÃO C# E MYSQL 71 3.4 INTEGRAÇÃO DE BANCO DE DADOS ENTRE SISTEMAS 74 3.4.1 API E WEB SERVICES 75 3.4.2 XML EM WEB SERVICES 76 CONCLUSÃO 79 4 CARACTERÍSTICAS DE BIG DATA 82 4.1 INTRODUÇÃO EM BIG DATA 82 4.1.1 MOTIVOS DE ADOÇÃO 86 4.1.2 IMPACTOS DA UTILIZAÇÃO 88 4.1.3 EXEMPLOS DE SUCESSO 89 4.2 TECNOLOGIAS PARA BIG DATA 92 4.2.1 HADOOP 92 4.3 PROFISSIONAIS NO CONTEXTO BIG DATA 95 4.3.1 CAPACITAÇÃO 96 4.3.2 O PROFISSIONAL CDO (CHIEF DATA OFFICCER) 97 10 BANCO DE DADOS II FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017SUMÁRIO CONCLUSÃO 98 5 BANCOS DE DADOS NÃO CONVENCIONAIS 100 INTRODUÇÃO DA UNIDADE 100 5.1 CONCEITO DE BANCOS DE DADOS NÃO CONVENCIONAIS 101 5.1.1 COMPARATIVO: DADOS RELACIONAIS E NÃO CONVENCIONAIS 103 5.1.2 APLICAÇÕES NÃO CONVENCIONAIS 104 5.2 O CONCEITO NOSQL 105 5.2.1 DUAS PRINCIPAIS CARACTERÍSTICAS TÉCNICAS 107 5.2.2 TIPOS NOSQL 108 5.2.3 COMPARATIVO DE TERMOS ENTRE BASES SQL E NOSQL 109 5.3 SEDNA - UMA ABORDAGEM DE BANCO DE DADOS XML 110 5.3.1 ARQUITETURA BÁSICA DO SEDNA 112 5.3.2 ARMAZENAMENTO DE DADOS NO SEDNA 113 5.4 BD ORIENTADO A OBJETOS E BD OBJETO-RELACIONAL 114 CONCLUSÃO 116 6 ASPECTOS DE SEGURANÇA EM BANCOS DE DADOS 118 INTRODUÇÃO DA UNIDADE 118 6.1 SEGURANÇA NO CONTEXTO DA APLICAÇÃO 119 6.1.1 SQL INJECTION 121 6.1.2 AUMENTANDO A SEGURANÇA DE DADOS COM USO DE HASH 124 6.1.3 OUTROS FATORES DE SEGURANÇA LIGADOS A SENHAS 127 6.2 SEGURANÇA NO CONTEXTO SGBD 128 6.2.1 PERDA DE INTEGRIDADE 130 6.2.2 PERDA DE DISPONIBILIDADE 130 6.2.3 PERDA DE CONFIDENCIALIDADE 131 6.2.4 O PAPEL DO ADMINISTRADOR – DBA 131 SUMÁRIO 5UNIDADE 6UNIDADE 11 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO 6.2.5 CONTROLES DE SEGURANÇA 131 6.2.6 CONTROLE DE ACESSO 132 6.2.6.1 GRANT E REVOKE 132 6.2.7 CONTROLE DE INFERÊNCIA 134 CONCLUSÃO 136 REFERÊNCIAS 137 SUMÁRIO 12 BANCO DE DADOS II FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017SUMÁRIO ICONOGRAFIA ATENÇÃO PARA SABER SAIBA MAIS ONDE PESQUISAR DICAS LEITURA COMPLEMENTAR GLOSSÁRIO ATIVIDADES DE APRENDIZAGEM CURIOSIDADES QUESTÕES ÁUDIOSMÍDIAS INTEGRADAS ANOTAÇÕES EXEMPLOS CITAÇÕES DOWNLOADS 13 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO APRESENTAÇÃO DA DISCIPLINA Olá, aluno! Seja bem-vindo à disciplina Banco de Dados I! Ao lado da programação e desenvolvimento de sistemas, o banco de dados é uma peça essencial de qualquer aplicação/programa. Atualmente, vivemos em um cená- rio de constante troca e compartilhamento de informação que acontece por meio dos mais diferentes tipos de dispositivos de hardware. Podemos citar, como exemplo, as aplicações desktop operando em computadores pessoais e também aplicações diversas em nosso cotidiano com a utilização de smartphones e tablets. O profissio- nal de tecnologia, membro de uma equipe de desenvolvimento, precisa demonstrar domínio em assuntos diversos ligados a banco de dados. Na disciplina de Banco de Dados II, é proposto o aprofundamento de conceitos fun- damentais, esperando, com isso, que você se torne um profissional com conhecimen- to avançado em diversos temas no contexto de análise de dados e entendimento do ambiente macro em sistemas de banco de dados. Inicialmente, serão aborda- das diversas formas de consultas SQL envolvendo as principais funções agregadas, operadores e cláusulas, além de expor como realizar junções de dados de diferentes tabelas, agrupamento e ordenação, através de diversos exemplos práticos. Você verá também a conceituação e características de Big Data no armazenamento de gran- des volumes de dados, Business Intelligence - BI e suas características, comparação de abordagens não convencionais para bancos de dados e também a integração de bancos de dados e internet. É importante que você sempre consulte a bibliografia deste material, além da participação ativa nos fóruns de discussão. Bons estudos! Objetivos da disciplina Ao final desta disciplina, esperamos que você seja capaz de: • Descrever os conceitos avançados de consultas SQL através de exemplos. • Apontar as junções entre tabelas e Stored Procedures. • Definir os conceitos de Big Data. • Identificar as características e os elementos básicos de Business Intelligence. • Identificar as diferenças entre banco de dados não convencionais. • Explicar a integração de banco de dados relacionais e a internet. • Explicar os conceitos de segurança e integridade de dados. 14 BANCO DE DADOS II FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017SUMÁRIO OBJETIVO Ao final desta unidade, esperamos que você: > Discuta os conceitos de dificuldades de aprendizagem e o fracasso escolar. > Analise o fenômeno das dificuldades de aprendizagem e seus aspectos. > Aponte reflexões sobre o fracasso escolar no processo de ensino- aprendizagem. UNIDADE 1 15 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO 1 PRÁTICA EM DECLARAÇÕES DE CONSULTAS SQL Ao se pensar em banco de dados, deve-se ter em mente alguns pontos, tais como: a realização de consultas em um banco de dados, comparando os valores informados por um usuário de uma aplicação; como um campo de pesquisa ou um formulário de login e senha, a partir do entendimento do modelo relacional, baseado em regis- tros em tabelas físicas; como se faz possível juntar dados, por exemplo, de clientes e seus pedidos em uma aplicação de venda do mundo real. Você pode escrever de- clarações SQL de forma otimizada que se mostrem padronizadas e fáceis de aplicar manutenção. Esta unidade irá tratar especificamente das soluções para esses pontos. Neste momento, será dada continuidade ao que você já conhece a respeito de banco de dados! A sua caminhada no conteúdo será embasada em teoria, visando à diferen- ciação no mercado detrabalho em tecnologia da informação. Vamos juntos, então! “Pra trás, nem pra pegar impulso!” - Clóvis de Barros Filho 1.1 CONCEITOS BÁSICOS SOBRE SGBD Para assimilação do conteúdo desta unida- de, faz-se necessário o conhecimento em Sis- tema Gerenciador Banco de Dados (SGBD). De forma objetiva, um SGBD é um pacote de programas que compõe um Sistema de Ban- co de Dados (SBD), conforme demonstrado na figura a seguir. A utilização do SGBD no gerenciamento de dados traz, ao SBD, várias vantagens, confor- me a seguir: FIGURA 1 - REPRESENTAÇÃO DE UM SISTEMA DE BANCO DE DADOS Programas de aplicação Sistema gerenciador de banco de dados Dados Fonte: CARDOSO, 2008. 16 BANCO DE DADOS II FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017SUMÁRIO • Um SGBD multiusuário permite que vários usuários acessem o banco de da- dos simultaneamente. • Provê formas complexas de relacionamento de dados. • Mantém o controle de concorrência. • recursos para recuperação de falhas (software e hardware). Como exemplo, temos o MYSQL, sendo um sistema geren- ciador de banco de dados adquirido, em 2009, pela empresa Oracle. É mundialmente utilizado e considerado o mais po- pular entre todos. Segundo do site do Mysql, na página “Why Mysql?” (Acesso em novembro de 2018), aplicações como Fa- cebook, Twitter e até o YouTube fazem uso do MySQL em algum momento, em seus sistemas. O MySQL constitui um banco de dados relacional e é operado utilizando a Lingua- gem de Consulta Estruturada, conhecida como Structured Query Language (SQL). O modelo relacional é um destaque na utilização para criação de banco de dados corporativos em todos os níveis, atualmente. 1.1.1 SERVIDOR LOCAL - XAMPP Para se conseguir trabalhar tecnicamente com as declarações SQL, que será visto nesta unidade, serão apresentados exemplos com os quais você também poderá im- plementar, sendo necessário um servidor local. Desse modo, utilizaremos a iniciati- va de componentes XAMPP que implementa localmente um SGDB Mysql e outros componentes, como servidor Apache, para implementação de soluções programá- veis através da linguagem PHP, por exemplo. Fique tranquilo! Caso você não tenha familiaridade, a instalação do XAMPP é algo muito simples de ser realizada. Em seu computador, utilize um buscador de inter- net de sua preferência e pesquise por: xampp download. Por ser uma tecnologia 17 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO amplamente difundida, serão listadas várias páginas para que você possa obtê-lo gra- tuitamente. Aqui vamos aconselhar o download através do site Apache Friends. Feito o download e instalação do XAMPP e o iniciando, temos então aberto o painel de controle do XAMPP, conforme figura a seguir. FIGURA 2 - PAINEL DE CONTROLE XAMPP Fonte: Tela do aplicativo XAMPP . É necessário ativar o módulo Mysql, através do botão start respectivo a ele, e também o módulo Apache, para ser pos- sível acessar a interface PhpMyAdmin, através de <http:// localhost/phpmyadmin/> no seu navegador. A instalação e ativação do XAMPP não são objetivos desta disciplina. Caso você tenha dificuldades na instalação ou ativação dos módulos, pesquise mais a respeito em outras fontes como a própria internet e também nos livros disponíveis a você no sistema Minha Biblioteca. 18 BANCO DE DADOS II FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017SUMÁRIO 1.1.2 AS PARTES DA SQL - DDL E DML Daqui em diante, você irá estudar a fundo os conhecimentos específicos em SQL e, para isso, será necessário relembrar suas partes. A SQL é dividida entre declarações DDL e DML. Temos que a Data Definition Language – DDL, ou seja, Linguagem de Definição de Dados é responsável por estabelecer os chamados metadados de uma base de dados. Lembrando-se que metadados são dados de outro dado! Através de declarações DDL, fazemos criação de tabelas, alteramos estruturas ou as excluímos (CREATE, ALTER, DROP, entre outros). Já os comandos responsáveis pela manipulação de dados são a parte da SQL deno- minada como Data Manipulation Language – DML. A manipulação dos dados se dá principalmente por: • Inserção de dados de tabelas. • Atualização de dados de tabelas. • Exclusão de dados em uma tabela. • Seleção de dados de uma tabela. Na DML, temos os comandos: INSERT, UPDATE, DELETE e SELECT, principalmente. Daqui em diante, nesta unidade iremos focar nas várias possibilidades de seleção de dados com uso da declaração SELECT. É importante que, nesse ponto, você já consiga assimilar o nível básico sobre banco de dados relacionais e já tenha iniciado seus estudos em declarações DML básicas, afinal esta é a disciplina de Banco de Dados II. Não prossiga, caso você ainda não tenha tido contato nenhum com SQL. Você tem, à sua disposição, diversos livros no sistema Minha Biblioteca ao qual poderá ler muito a respeito sobre o tema. Consulte a bibliografia desta unidade sempre que precisar para saber os livros indicados! 1.2 CONSULTAS EM SQL As consultas em banco de dados relacionais são vistas na prática de análise e desen- volvimento, como um dos pontos mais importantes em aplicações computacionais. Imagine como o motor de busca de grandes buscadores como o Google Search im- plementa declarações para atender à sua necessidade de busca em milésimos de 19 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO segundos. Ele deve apresentar resultados relevantes, ou seja, levando em considera- ção questões como ranqueamento de resultados (o que deve vir primeiro e em se- quência), além de considerar resultados de anunciantes que pagam para ser exibidos a você, de acordo com o termo pesquisado. Iremos então focar nossos esforços, até o final desta unidade, nessa parte importan- te da DML em SQL. A seguir, iremos apresentar conceitos mais avançados acerca da instrução SELECT. 1.2.1 INSTRUÇÃO SELECT Nosso grande amigo SELECT! Como poderíamos sobreviver sem ele no cenário da computação atual. A declaração SELECT basicamente informa que precisamos se- lecionar dados em uma ou mais tabelas, atendendo a junções, uniões, distinções, condições, agrupamentos, ordenação e limitação de resultados. Vamos evoluir pas- so a passo! A sintaxe mais simples da declaração SELECT se dá: SELECT * FROM nome_da_tabela; Através disso, teremos o resultado da tabela integralmente. Podemos também solicitar apenas algumas colunas de uma tabela, como exemplo: SELECT nome, telefone FROM tbl_cliente; Aqui, teremos o resultado apenas do nome e telefone de todos os registros da tabela cliente (tbl_cliente). 20 BANCO DE DADOS II FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017SUMÁRIO 1.2.2 ALIAS (AS) De maneira prática, os chamados aliases são formas de apelidar determinada tabela ou coluna em uma consulta. Veja a declaração: SELECT cli.nome, cli.telefone FROM tbl_cliente AS cli; Nesse exemplo, chamou-se como cli a tabela tbl_cliente e, com isso, selecionamos as colunas cli.nome e cli.telefone (nome e telefone da tabela cliente). Podemos também dar nomes às colunas como exemplo: SELECT sal_base AS salario_base_funcionario FROM tbl_funcionario; Perceba que agora demos o nome salario_base_funcionario para o atributo sal_base, ou seja, para a coluna de salário base do funcionário, da tabela tbl_funcionario. Dar nomes a tabelas e colunas é algo imprescindível? Não necessariamente. Você poderá perfeitamente tra- balhar com consultas sem usar aliases, mas fazendo uso deles. O benefício é escrever menos e conseguir organizar/pensar melhor na declaração SQL como um todo. Vocêirá perceber isso logo adiante nesta unidade. Eles são extremamente úteis quando temos cláusulas JOIN em uma consulta. 21 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO 1.2.3 CLÁUSULA WHERE A cláusula WHERE é utilizada para “filtrar” os resultados, ou seja, restringir o que se es- pera em uma consulta. É utilizada então para fazer uma extração de dados somente do que atenda à condição lógica. Veja o exemplo: SELECT nome, telefone FROM tbl_cliente WHERE ddd_telefone = 21; Perceba agora que conseguimos buscar os nomes e te- lefones de clientes ONDE o campo ddd_telefone seja 21 (Ex.: RJ). Dessa forma, os clientes que têm o ddd_telefo- ne de MG não são listados. Falou-se de condição lógica anteriormente. Isso quer dizer que podemos usar, por exemplo, um campo igual a um valor (ddd_telefone=21), mas também podemos usar: maior que, menor que, entre outros. Exemplo: ddd_telefone > 11. Dessa for- ma, seriam exibidos os registros de clientes com DDD maior que 11 e, nesse caso, os clientes de Minas Gerais (DDD 31) seriam impressos. Temos também o diferente de, veja: ddd_telefone <> 11. Assim, teríamos como resultado todos os clientes com DDD diferente de 11. Esses são os chamados operadores na cláusula WHERE. Veja mais detalhes no quadro a seguir. QUADRO 1 - OPERADORES NA CLÁUSULA WHERE OPERADOR DESCRIÇÃO = Igual a <> Diferente. Observação: Em algumas versões do SQL esse operador pode ser es- crito como != > Maior que < Menor que >= Maior que ou igual 22 BANCO DE DADOS II FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017SUMÁRIO OPERADOR DESCRIÇÃO <= Menor que ou igual BETWEEN Entre um determinado intervalo. LIKE Pesquisar por um padrão. Observação: Iremos tratar, à frente, sobre isso. IN Para especificar valores possíveis para uma coluna. Observação: Iremos tratar, à frente, sobre isso. Fonte: Elaborado pelo autor. 1.2.4 OPERADORES AND, OR, NOT Os operadores AND, OR ou NOT podem e aparecem constantemente na prática de desenvolvimento de sistemas. Através deles, pode-se estabelecer uma maior abran- gência nos filtros com WHERE, por exemplo. Veja: SELECT nome, telefone FROM tbl_cliente WHERE ddd_telefone = 31 AND cidade = ‘Belo Horizonte’ AND endereco_compl = ‘Apto’; Aqui temos o nome e telefone de clientes que tenham o DDD do telefone igual a 31, como cidade, Belo Horizonte, e como complemento do endereço, apartamento. Logicamente, quem não morar em um apartamento não será listado, bem como quem não for de Belo Horizonte e também quem não tiver DDD 31. Perceba mais um exemplo a seguir. SELECT nome, telefone FROM tbl_cliente WHERE ddd_telefone = 31 OR ddd_telefone = 21 Agora usando o operador OR, temos um filtro de clientes com ddd_telefone = 31 ou 21. 23 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO Com operador AND são exibidos os resultados caso todas as condições que estiverem separadas por AND forem verdadeiras. Já o operador OR mostra os resultados se alguma das condições que estiverem separadas por OR forem atendidas, ou seja, se forem verdadeiras. É impor- tante entender e praticar esses operadores, pois um erro lógico, nesse sentido, pode ocasionar problemas em uma aplicação. Imagine a situação em que se precise buscar registros a partir de datas em uma tabela, onde é neces- sário exibir apenas registros do ano de 2017 ou 2018 e do mês fevereiro até junho. Veja, a seguir, uma possível condição WHERE: ... WHERE (ano = 2017 OR ano = 2018) AND (mes >= 2 AND mes<=6) ... Uma troca entre OR/AND poderia trazer registros errados ou não trazer todos os cer- tos. É importante estar atento! Não menos importante, o operador NOT filtra os resultados para quando a condição não é verdadeira. Veja um exemplo: SELECT nome, telefone FROM tbl_cliente WHERE NOT pais = ‘Brasil’; Ficou simples de entender que essa consulta trará os clientes que não são brasilei- ros, certo? 24 BANCO DE DADOS II FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017SUMÁRIO Podemos também combinar NOT com outro operador. Veja: SELECT nome, telefone FROM tbl_cliente WHERE NOT pais = ‘Brasil’ AND NOT pais = ‘Japão’; Aqui, teremos resultados de clientes que não são brasileiros e também não são ja- poneses. 1.2.5 CLÁUSULA LIMIT Através da cláusula LIMIT, você consegue limitar a quantidade de resultados que pre- cisamos em uma instrução SQL. Veja um exemplo: SELECT nome, telefone FROM tbl_cliente WHERE NOT pais = ‘Brasil’ AND NOT pais = ‘Japão’ LIMIT 3; Aqui, teremos retorno de apenas 3 resultados de clientes que não são brasileiros e também não são japoneses. Caso existam 10 clientes não brasileiros e não japo- neses, apenas 3 deles serão listados. Caso exista apenas 1, apenas ele será listado. Dessa forma, o LIMIT restringe a quantidade máxima de registros que devem ser exi- bidos atendendo à(s) condição(es). Em SGBD SQL Server da Microsoft, diferente do MYSQL, o correspondente ao LIMIT se dá pela declaração SELECT TOP. Um exemplo, nesse caso, seria: SELECT TOP 3 FROM tbl_cliente WHERE ddd_telefone = 21; 25 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO 1.2.6 FUNÇÕES AGREGADAS MIN() E MAX() MIN() e MAX() são funções que podemos utilizar para retornar os valores mínimos e máximos de um campo da tabela. Observe os exemplos, a seguir, e note a presença dos parênteses envolvendo o campo em questão. SELECT idproduto, nome, MIN(preco) FROM tbl_produto; Nesse exemplo, teremos o resultado do produto com menor preço da tabela produ- tos, sendo apresentadas então as colunas idproduto, nome e preço. SELECT idproduto, nome, MAX(preco) FROM tbl_produto; Aqui, teremos o resultado do produto com maior preço da tabela produtos, sendo apresentadas então as colunas idproduto, nome e preço. As funções nativas em um SGBD se mostram auxiliares diretos ao analista/desenvol- vedor no âmbito de delegar as responsabilidades de processamento a ele (SGBD) e não ao código escrito em alguma linguagem. O conhecimento das funções nativas do SGDB é então algo bem importante. Nesse caso, as funções MIN() e MAX() tratam de funções simples, mas que, se não existissem, o desenvolvedor precisaria criar ro- tinas na aplicação desenvolvida para selecionar todos os dados, verificar qual seria o registro mínimo ou máximo para então exibi-los. Ao contrário disso, com uma sim- ples instrução SQL, o resultado desejado já se torna visível como retorno à consulta. É interessante notar que as funções MIN() e MAX() restringem o resultado, pois logi- camente irão atender à função de valor máximo ou mínimo do campo em questão. E, se tivermos dois produtos com mesmos valores mínimos na tabela? Por exemplo, um livro no valor de R$35 e um caderno também com o valor de R$35, sendo nessa 26 BANCO DE DADOS II FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017SUMÁRIO tabela de produtos ambos com menores preços. Nesse caso, vale refletir se o SGBD retornaria os dois registros ou apenas um deles quando aplicado a função MIN(). Des- cubra o direcionamento para a reflexão treinando essa função em uma tabela, usan- do seu servidor local - XAMPP! Crie uma tabela de produtos constando uma coluna de preço, inclua alguns registros com preços iguais e opere o SQL utilizando a função MIN(). Outras funções bastante úteis na prática de consultas em banco de dados são as fun- ções relacionadas a campos do tipo data: day(), month(), year(), hour(), entre diversas outras. São usadas para extrair/comparar de um campo dotipo data, informações como o dia, o mês, o ano. Um exemplo simples, nesse sentido, seria: SELECT month(data_vencimento) FROM tbl_financeiro; Em MYSQL, temos nesse exemplo o resultado apenas do mês do campo data_venci- mento de uma possível tabela financeira do banco de dados, que por exemplo pode- ria ter o valor: 25/12/2018. Nesse caso, automaticamente já viria no retorno apenas o mês, nesse caso 12. Nesse exemplo, foi usada a função Month() para mostrar apenas o mês de um campo, mas a função pode também ser usada em condições WHERE. Exemplo: SELECT <...> FROM <tabela> WHERE month(data_vencimento) = 12 Cada SGBD possui particularidades em relação à sintaxe (for- ma de estruturar) as funções SQL. No caso do MYSQL, você poderá ler mais a respeito de todas as funções disponíveis, consultando o site oficial de desenvolveres MYSQL. Faça uma pesquisa no buscador de sua preferência pelo termo: dev my- sql reference manual functions. Da mesma maneira, você poderá buscar conhecimento em funções de outros SGBDs como SQL Server, Oracle e demais. 27 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO 1.2.7 FUNÇÃO AGREGADA COUNT() A função COUNT() é um dos destaques também entre as funções presentes nos SGBDs. Através dela, você pode contar a quantidade de registros atendendo a condi- ções WHERE, por exemplo. Veja: SELECT COUNT(idproduto) FROM tbl_produto WHERE preco <= 10; Tem-se, com essa instrução, o retorno da quantidade de registros que atendam ao que foi proposto na cláusula WHERE, sendo, nesse caso, a quantidade de produtos com preço menor ou igual a 10. Vale ressaltar aqui a quantidade de registros que são retornados a partir de uma instrução envolvendo a função COUNT(). É possível descobri-la, através do seu banco de dados, mais uma vez utilizando o XAMPP. Você precisa praticar e entender, partindo de treinos! Vamos lá, você já pode responder a essa questão! 1.2.8 FUNÇÕES AGREGADAS AVG(), SUM() As funções AVG() e SUM() retornam à média e à soma, respectivamente, de campos numéricos de uma tabela. Perceba os exemplos a seguir: SELECT AVG(preco) FROM tbl_produto WHERE peso <= 0.5; Nesse pequeno exemplo, estamos pedindo a média de preço dos produtos com peso menor ou igual a 0.5. Percebe-se que as funções são verdadeiras amigas do desen- volvedor! 28 BANCO DE DADOS II FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017SUMÁRIO SELECT SUM(preco) FROM tbl_produto; Aqui temos a soma de todos os preços de produtos; afinal, não houve nenhum filtro aplicado utilizando condições a serem atendidas com WHERE. 1.2.9 OPERADOR LIKE O operador LIKE é utilizado juntamente à cláusula WHERE para pesquisar um pa- drão. Pense na seguinte situação, que aliás é bastante comum em sistemas de qual- quer tipo: dentro da aplicação, existe a necessidade de existir um campo de pesquisa pelo nome do cliente. Partindo disso, o usuário dessa aplicação poderá informar o início do nome, partes do meio do nome, ou apenas o último sobrenome do cliente para realização da pesquisa no banco de dados. Veja três exemplos a seguir: SELECT nome FROM tbl_cliente WHERE nome LIKE ‘Maria%’; Nesse exemplo, estamos pesquisando por clientes que têm o nome iniciando pelo termo Maria. Perceba que usamos o curinga de porcentagem logo após o termo. Como resultado, teríamos os clientes: Maria Joana, Maria Silva. E não teríamos ne- nhuma Ana Maria, pois o curinga (%) foi incluído ao final e, dessa forma, o nome deve iniciar com Maria. SELECT nome FROM tbl_cliente WHERE nome LIKE ‘%Maria’; 29 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO Agora alteramos a posição do curinga de porcentagem para o início. Já aqui teríamos, como resultado, por exemplo, a cliente Ana Maria, pois, nesse caso, o termo Maria está ao final do nome. SELECT nome FROM tbl_cliente WHERE nome LIKE ‘%Maria%’; Por fim, temos o curinga tanto no início quanto no fim. Dessa forma, teremos retorno de clientes com o termo Maria em qualquer posição do nome. Com uso do operador LIKE, consegue-se construir instruções de pesquisa bem mais refinadas do que esses exemplos. Aqui, foram apresentas as formas básicas de sua utilização. Existem, por exemplo, ainda outras formas de se trabalhar com curingas e combinações deles no uso do operador LIKE, como uso do curinga underline para verificar o termo infor- mado em posição específica do valor de um campo na tabela do banco de dados. Pesquise mais a respeito de acordo com o SGBD que você está ou estará utilizando em seus projetos. 1.2.10 OPERADORES IN, NOT IN O operador IN fornece à instrução SELECT a possibilidade de especificar vários valo- res na cláusula WHERE. Como você irá perceber a seguir, temos que esse operador é uma forma alternativa, digamos uma abreviação do uso de vários operadores OR, conforme visto anteriormente. Acompanhe os exemplos a seguir para consolidar o seu entendimento. SELECT nome FROM tbl_cliente WHERE pais IN ( ‘Brasil’ , ‘Alemanha’ , ‘USA’ ); 30 BANCO DE DADOS II FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017SUMÁRIO Com o conhecimento que você já tem até aqui, ficou simples entender o operador IN apenas pelo exemplo. Nesse caso, busca-se clientes com atribuição de país, sendo Brasil OU Alemanha OU USA. Atenção! Perceba na estruturação dos parênteses a presença de aspas simples e vír- gulas separando cada termo. SELECT nome FROM tbl_cliente WHERE pais NOT IN ( ‘Inglaterra’ ); Já aqui estamos selecionando clientes que não têm a atribuição de país, sendo o termo Inglaterra. Tem-se também a possibilidade de usar os operadores IN, NOT IN para especificar dentro dos parênteses outra instrução SQL. SELECT nome, telefone FROM tbl_cliente WHERE pais IN ( SELECT pais FROM tbl_fornecedores ); Aqui, são selecionados o nome e telefone dos clientes que são dos mesmos países que os fornecedores, ou seja, o SQL interno ao operador IN apenas traz a lista de paí- ses de fornecedores, como: ‘Brasil’, ‘Alemanha’, ‘Japão’. 31 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO SELECT nome, email FROM tbl_cliente WHERE idcliente NOT IN ( SELECT idcliente FROM tbl_envio_email WHERE tipo = ‘Natal2018’ ); O objetivo aqui é trazer o nome e e-mail de clientes que ainda não receberam um e-mail de Natal 2018. Perceba que, dentro dos parênteses do operador NOT IN, te- mos uma segunda instrução SQL que retorna apenas o idcliente de quem já recebeu e-mail de Natal. Com isso, haverá o processamento dessa consulta interna ao NOT IN retornando, por exemplo: 1,5,7,10. Ou seja, os clientes 1,5,7,10 já receberam e-mail e logo o SQL principal trará o nome e e-mail de clientes onde idcliente NOT IN (1,5,7,10). 1.2.11 ORDER BY E GROUP BY Com uso do ORDER BY, conseguimos classificar o conjunto de resultados de maneira crescente ou decrescente. Veja exemplos: SELECT nome, email FROM tbl_cliente ORDER BY nome; Aqui, estamos buscando nome e e-mail de clientes, sendo o resultado de clientes apresentados, ordenados alfabeticamente pelo nome. 32 BANCO DE DADOS II FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017SUMÁRIO SELECT nome, email FROM tbl_cliente ORDER BY nome DESC; Aqui, estamos buscando nome e e-mail de clientes, sendo o resultado de clientes apresentados, ordenados alfabeticamente pelo nome de forma decrescente. Já a instrução GROUP BY realiza o agrupamento do conjunto de resultados em uma ou mais colunas. Esse recurso é muito usado em conjunto comas funções agregadas (COUNT, MAX, MIN, SUM, AVG). Veja um exemplo de uso em conjunto com a função COUNT(): SELECT COUNT(idcliente), cidade FROM tbl_cliente GROUP BY cidade; O resultado dessa consulta é uma contagem de clientes e cidade realizando um agru- pamento por cidade, ou seja, nas linhas do conjunto o resultado seria, por exemplo: COUNT(IDCLIENTE) CIDADE 4 Belo Horizonte 15 Espírito Santo 25 Rio de Janeiro 1.3 ENTENDENDO JUNÇÕES DE TABELAS COM INNER JOIN As chamadas junções, expressas em SQL, como cláusulas JOIN, são recursos funda- mentais para realização de consultas mais complexas em banco de dados. A impor- tância desse recurso se dá pelo fato de que através do uso de JOIN podemos combi- nar registros de duas ou mais tabelas do banco de dados, tomando como base um campo relacionado entre elas, geralmente a chave primária de uma tabela em rela- ção à chave estrangeira em outra. Perceba no QUA. 2 os dados populados em uma possível tabela de pedidos; e no QUA. 3, a tabela de clientes. 33 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO QUADRO 2 - DADOS NA TABELA PEDIDOS IDPEDIDO IDCLIENTE DATA_PEDIDO 1010 1 18/11/2018 1021 36 19/11/2018 1031 76 20/11/2018 Fonte: Elaborado pelo autor. QUADRO 3 - DADOS NA TABELA CLIENTES IDCLIENTE NOME PAIS 1 Jorge Brasil 2 Maria Brasil 36 John USA 76 Honda Japão Fonte: Elaborado pelo autor. Note que a coluna idcliente na tabela de pedidos faz re- ferência à coluna idcliente na tabela de clientes. Agora imagine se você juntar, de forma relacionada, os dados tanto da tabela pedi- dos como da tabela de clientes. É nesse ponto que temos as cláusulas JOIN. Veja um exemplo simples dessas duas tabelas. SELECT Ped.idpedido, Ped.data_pedido, Cli.nome, Cli.pais FROM tbl_pedido AS Ped INNER JOIN tbl_cliente AS Cli ON Ped.idcliente=Cli.idcliente; Nesse exemplo, temos como resultado o idpedido, data_pedido da tabela de pedi- dos com uma junção do nome e pais do cliente da tabela de clientes. Nesse caso, as linhas do conjunto resultado dessa instrução seria conforme apresentado no quadro a seguir (Conjunto resultado da instrução INNER JOIN (pedidos e clientes). 34 BANCO DE DADOS II FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017SUMÁRIO Para facilitar sua assimilação, perceba que na instrução SQL foram usadas as cores, apenas como forma didática: • Em laranja temos: Uso de alias para chamar de “Ped” a tabela tbl_pedido. • Em verde temos: Uso de alias para chamar de “Cli” a ta- bela tbl_cliente. • Em azul temos: Cláusula INNER JOIN e ON necessário para relacionamento. A utilização de aliases foi esclarecida anteriormente nesta unidade! QUADRO 4 - CONJUNTO RESULTADO DA INSTRUÇÃO INNER JOIN (PEDIDOS E CLIENTES) IDPEDIDO DATA_PEDIDO NOME PAIS 1010 18/11/2018 Jorge Brasil 1021 19/11/2018 John USA 1031 20/11/2018 Honda Japão Fonte: Elaborado pelo autor. FIGURA 3 - REPRESENTAÇÃO DO CONJUNTO RESULTADO INNER JOIN A B O recurso INNER JOIN então retorna no conjunto resultado o(s) registro(s) que são comuns nas duas tabelas. Fonte: Elaborado pelo autor. O INNER JOIN é forma de junção de dados mais conhecida, pois comumente nas aplicações o que mais se precisa é o retorno relacionado de dados entre as tabelas, assim como visto no exemplo anterior: dados de clientes de acordo com a relação de seus pedidos. Daqui em diante, serão apresentadas outras formas possíveis da cláu- sula JOIN, mas não entraremos em exemplos específicos. Caso você tenha interesse 35 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO em suas aplicações, poderá testá-las localmente em servidor local com uso do XAM- PP, criando então duas ou mais tabelas e operando instruções SELECT com uso de tais variações JOIN. 1.3.1 LEFT JOIN Existe também a possibilidade de uso do LEFT JOIN. Conforme apresentado na figura a seguir, nesse caso o conjunto resultado da instrução SQL será todos os registros que estão presentes na tabela A (mesmo que não estejam presentes na tabela B) e tam- bém os registros da tabela B que são comuns à tabela A (interseção). FIGURA 4 - REPRESENTAÇÃO DO CONJUNTO RESULTADO LEFT JOIN A B Fonte: Elaborado pelo autor. 1.3.2 RIGHT JOIN Com utilização do RIGHT JOIN, de acordo com a FIG. 5 a seguir, haverá como conjun- to o resultado de todos os registros que estiverem na tabela B (mesmo não presentes na tabela A) e também os registros da tabela A que são comuns à tabela B. FIGURA 5 - REPRESENTAÇÃO DO CONJUNTO RESULTADO RIGHT JOIN A B Fonte: Elaborador pelo autor. 36 BANCO DE DADOS II FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017SUMÁRIO 1.3.3 FULL OUTER JOIN Agora, com utilização do OUTER JOIN (também denominado por FULL OUTER JOIN ou apenas FULL JOIN), conforme ilustrado na figura abaixo, temos no conjunto re- sultado de todos os registros que estão presentes na tabela A e todos os registros da tabela B. FIGURA 6 - REPRESENTAÇÃO DO CONJUNTO RESULTADO FULL JOIN A B Fonte: Elaborado pelo autor. É importante que, neste momento, você busque mais exemplos e treine as variações do JOIN. Acesse os livros indicados na bibliografia desta unidade disponíveis no sistema Minha Biblioteca. 1.4 STORED PROCEDURES Um procedimento armazenado, conhecido como Stored Procedures, trata de uma instrução SQL previamente preparada ao qual poderá ser salva, para que então possa ser reutilizada repetidamente quando necessário. Assim, se você tiver, por exemplo, uma consulta SQL que necessite escrever repetidamente em sua aplicação, desenvol- vida em alguma linguagem, basta salvá-la como um procedimento armazenado no SGBD e, quando necessário, apenas realizar uma chamada para executá-la. 37 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO Em Stored Procedures, é permitida a passagem de parâmetros para a instrução ar- mazenada e, dessa forma, conseguir dinamismo nas ações com base nos valores de parâmetro que são passados. A sintaxe de criação de Stored Procedures varia de acor- do com o SGBD. Aqui, exemplifica-se com o uso do SGBD MYSQL, para que você consiga reproduzir local- mente com uso do pacote XAMPP, em seu servidor local. Acompanhe um exemplo básico de criação de uma Procedure, mas, para isso, certifi- que-se de criar primeiramente uma tabela de clientes, conforme a seguir. CREATE TABLE tbl_cliente ( `idcliente` INT(15) NOT NULL AUTO_INCREMENT , `nome` VARCHAR(50) NOT NULL , `pais` VARCHAR(50) NOT NULL , PRIMARY KEY (`idcliente`) ) ENGINE = InnoDB; Aqui, temos a estrutura básica da tabela cliente para realizar nosso exemplo de cria- ção e execução da Procedure: • idcliente do tipo inteiro(15), como chave primária e autoincremental. • nome do tipo varchar(50). • pais do tipo varchar(50). Agora, com nossa tabela criada, iremos inserir três registros de clientes: INSERT INTO `cliente` (`nome`, `pais`) VALUES (‘Jorge’, ‘Brasil’); INSERT INTO `cliente` (`nome`, `pais`) VALUES (‘John’, ‘USA’); INSERT INTO `cliente` (`nome`, `pais`) VALUES (‘Honda’, ‘Japão’); 38 BANCO DE DADOS II FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017SUMÁRIO Vamos criar nossa Procedure. O objetivo será criar o procedimento para listar todos os clientes, quando nenhum parâmetro for passado ao procedimento, e também listar somente o cliente específico, quando for passado o parâmetro de número do idcliente específico. Em resumo, ao chamar o procedimento, quando não informado nenhum parâmetro, irá listar todos os clientes. Quando informado por exemplo, o parâmetro1, irá listar somente o cliente Jorge. Veja a seguir o código de criação: 1 2 3 4 5 6 7 8 9 10 11 DELIMITER $$ DROP PROCEDURE IF EXISTS `listar_clientes` $$ CREATE PROCEDURE `listar_clientes`(IN _id INT) BEGIN IF(_id IS NULL) THEN SELECT * FROM tbl_cliente; ELSE SELECT * FROM tbl_cliente WHERE idcliente = _id; END IF; END $$ DELIMITER ; Nas linhas 1 e 11, temos a presença de DELIMITER informando onde se inicia e se termina a procedure. Na linha 2, é informado para remover a procedure listar_clien- tes caso ela já exista. A linha 3 informa a criação em si da procedure informando o nome listar_clientes e o parâmetro _id do tipo inteiro. Isso informa que esse proce- dimento aceitará receber um parâmetro que definimos ser um número. Ele servirá para interagir como uma variável para a consulta SQL interna a procedure. As linhas 4 e 10 informam onde inicia e onde termina o que será executado no procedimento. As linhas 5, 7 e 9 são responsáveis pela condicional (IF, ELSE, END IF) que irá tratar caso seja informado ou não o parâmetro. Caso o parâmetro chegue nulo, conforme linha 5, a execução entrará na linha 6, que fará a consulta exibindo todos os clientes. Caso contrário, ou seja, se chegou um parâmetro numérico, será executada a linha 8, que fará a consulta somente do cliente específico ao ID informado. 39 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO A partir disso, a procedure está criada e, dessa forma, podemos chamá-la e verificar o conjunto resultado. Para chamar a procedure, basta informarmos: call listar_clientes( null ); Fazendo a chamada, estamos passando o parâmetro nulo dentro dos parênteses e, dessa forma, o conjunto resultado será todos os clientes da tabela. call listar_clientes( 1 ); Agora, quando especificamos o número 1 dentro dos parênteses, estamos informan- do à procedure que queremos como retorno somente o cliente que tenha o campo idcliente = 1. Você já tem capacidade de saber mais respeito de procedures, construir procedimen- tos mais complexos de acordo com suas necessidades. Você poderá pesquisar sobre essa fantástica forma de programar procedimentos em banco de dados relacionais, através dos livros disponíveis a você no sistema Minha Biblioteca e também consul- tando o site oficial do MYSQL ou do SGBD que você irá utilizar em suas aplicações. CONCLUSÃO As consultas em banco de dados relacionais são um dos pontos mais importantes no âmbito de análise de desenvolvimento de softwares, pois através delas é possível comunicar o usuário de uma aplicação com os dados salvos fisicamente em um am- biente computacional. Isso é importante para a recuperação de dados em diferentes abordagens, desde a simples seleção de dados para exibição em tela, bem como para elaboração complexa de relatórios para tomadas de decisão do cliente e proce- dimentos automatizados para seleção e alteração de dados no servidor. Nesta unidade, você teve contato com os principais assuntos relacionados às decla- rações de consultas de dados em SQL. Viu inicialmente como é construída uma con- sulta utilizando o básico da instrução SELECT e, em sequência, observou como filtrar consultas utilizando aliases e a cláusula WHERE. 40 BANCO DE DADOS II FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017SUMÁRIO Foi demonstrado também através de exemplos específicos, diversas funções de agre- gação, tais como as funções: MIN() e MAX(), COUNT(), AVG() e SUM(). Além disso, você observou também, a partir de exemplos práticos, como construímos consultas fazen- do uso de operadores em conjunto da cláusula WHERE para definir filtros a partir de condições, tais como: AND, OR, NOT, IN e LIKE. Ainda, nesta unidade, teve uma explanação sobre a junção de dados de diferentes tabelas, através do uso de INNER JOIN partindo de colunas relacionadas entre duas tabelas com benefício de aliases. Não menos importante, foram abordadas formas básicas de agrupamento e ordenação do conjunto resultado de uma declaração SELECT, com uso de ORDER BY e GROUP BY. Ao final, foi apresentado o que são os procedimentos armazenados – Procedures, além de como podemos criá-las uti- lizando o SGBD MYSQL, através do uso dos componentes de servidor local XAMPP. Nessa explicação, criamos uma procedure com passagem de um parâmetro, e tam- bém demonstramos como realizar sua chamada através da palavra reservada CALL. Recomenda-se que os seus estudos, em banco de dados, sejam aprofundados com a leitura dos livros da bibliografia básica e complementar deste material. Bons estudos! 41 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO OBJETIVO Ao final desta unidade, esperamos que possa: > Explicar o que são fontes de dados em ambientes de Data Warehouse. • Definir o conceito de Business Intelligence e seus componentes. • Definir o conceito de Data Warehouse, características e arquiteturas. • Apontar o que são Data Marts e ODS e onde estes aparecem no ambiente de Data Warehouse. • Explicar o que são os processos de ETL. • Identificar outras arquiteturas de um ambiente de Data Warehouse. • Identificar exemplos reais de sucesso na implantação de Data Warehouse em grandes organizações. UNIDADE 2 42 BANCO DE DADOS II FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017SUMÁRIO 2 BANCO DE DADOS NO CONTEXTO DE BUSINESS INTELLIGENCE O que vem a ser Business Intelligence (BI) e qual a ligação desse universo com banco de dados de ambientes computacionais? Como as organizações de grande porte conseguem descobrir informações a partir de dados armazenados para criar intuição que proporcionam uma alavancagem nos negócios, bem como uma vantagem com- petitiva? Esta unidade tratará especificamente de clarear essas questões a partir do entendimento de como um banco de dados em aplicações pode apoiar as empresas em nível operacional, criando um novo cenário para tratamento das empresas. Esse cenário é fomentado por um grande volume de dados transacionais que podem ser analisados para a descoberta de poderosos insights em diferentes tipos de negócio. Esta unidade de estudo tratará especificamente de como são armazenados dados e criadas informações em banco de dados no contexto de Business Intelligence. Você verá como um acumulado de dados em aplicações operacionais podem se trans- formam em aumento de faturamento nas empresas. Basicamente, você perceberá como os dados e as informações passam a ter valor quando são bem analisados. Vamos lá! “É tolo quem erra o alvo e culpa o arco, ao invés de corrigir a própria mira.” - Sun Tzu 2.1 SISTEMAS TRANSACIONAIS COMO FONTE DE DADOS Neste momento da disciplina e se baseando em todo seu conhecimento pregres- so acerca de banco de dados em ambientes computacionais, já é sabido como se dão os modelos de dados e as características de sistemas gerenciadores de banco de dados. Também é conhecido como modelamos um banco de dados relacional 43 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO utilizando ferramentas, como o MySQLWorkBench, como o criamos fisicamente, por meio de declarações em SQL, bem como a realização de inserções, alterações e con- sultas a dados. Dessa forma, entende-se como são construídos banco de dados em aplicações de software para a utilização em empresas. Para que você entenda o que é Business Intelligence (BI), a partir de agora serão apre- sentadas essas aplicações que fazem uso de banco de dados relacionais por sistemas transacionais. Os sistemas transacionaissão notadamente o coração das organizações. São a base e, com eles, temos dados armazenados de diversas finalidades. Tal sistema de infor- mação possui papel vital em todo tipo de empresa para apoio em três níveis, como podemos observar na figura a seguir. FIGURA 7 - PAPÉIS DOS SISTEMAS DE INFORMAÇÃO NOS NÍVEIS ORGANIZACIONAIS Nível estratégico Sistemas de apoio para vantagem estratégica. Sistemas de apoio para decisões gerenciais. Sistemas de apoio para as operações. Nível gerencial Nível operacional Fonte: Elaborado pelo autor, 2018. 44 BANCO DE DADOS II FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017SUMÁRIO Para exemplificar: em um cenário de uma organização que tem como finalidade vendas de produtos, temos de forma generalizada três níveis. Estratégico: sistema para realizar previsões das tendências de vendas em determinados pe- ríodos. Gerencial: sistema para realizar acompanhamento mensal por regiões e relatórios. Operacional: sistema para realizar o armazenamento de dados das vendas diárias e, consequentemente, realizar os processamentos de pedidos. Os sistemas transacionais, como o próprio nome remete, dá auxílio ao nível mais elementar da organização, ou seja, o nível operacional. Dessa forma, fazem o moni- toramento de vendas, fluxos, atividades diárias, receitas e demais. Os sistemas transa- cionais fazem registro, mantêm e executam as transações de rotina da organização. Têm como principal característica o auxílio na administração operacional e o objetivo de capacitar a organização na execução de tarefas mais importantes de forma efi- ciente. São sistemas utilizados diariamente na organização. Em sistemas transacionais, temos: Grande quantidade de entradas de dados. Produção de grande saída de dados. Necessidade direta de processamento e�ciente. Entradas e saídas de dados de forma rápida. Computação simples. 45 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO Vale pensar em empresas que usam sistemas transacio- nais. Para organizações do setor de marketing e vendas, por exemplo, temos que os sistemas transacionais facilitam o movimento de produtos para os consumidores, explorando suporte a vendas, telemarketing, Módulos de Ponto de Ven- da (PDV) e módulos de processamento de pedidos. Já para empresas da área de produção/fabricação, os sistemas tran- sacionais fornecem dados para operação, monitoramento e controle do processo de produção, explorando compras, re- cebimento, expedição e estoque. Outra área que podemos exemplificar é a área de recursos humanos, em que empre- sas desse setor fazem avaliações de candidatos, lidam com avaliações de salários e colocação. Com isso, exploram nos sistemas transacionais a manutenção de registros de pes- soas e o controle de candidatos, cargos e treinamentos. Sendo assim, tem-se que os sistemas transacionais são o que se entende como uma das principais fontes de dados para entendermos Business Intelligence (BI). 2.2 BUSINESS INTELLIGENCE (BI) Business Intelligence (BI) é um termo que engloba arquiteturas e ferramentas, além de banco de dados, aplicações e métodos. Tem por objetivo permitir acesso de ma- neira interativa aos dados e proporcionar manipulação deles fornecendo aos gerentes e analistas de negócios a forma de fazerem análises mais adequadas para a organiza- ção. Os tomadores de decisões, ao analisarem esses dados, conseguem os chamados insights, ou seja, uma compreensão sobre algo ou determinada situação que, por sua vez, podem servir de base para melhores decisões. Assim sendo, o BI tem sua base em transformações de dados em informações, que, por sua vez, se transformam em decisões e, finalizando, em ações. 46 BANCO DE DADOS II FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017SUMÁRIO 2.2.1 COMPONENTES DE BI Turban (2009) aponta que um ambiente de BI é composto por quatro componentes, conforme podemos observar na figura 8. FIGURA 8 - OS QUATRO COMPONENTES DE BI Ferramentas de DW Data Warehouse (DW) Ferramentas de DW Data Warehouse (DW) BI Esse componente é o nosso foco para esta unidade de estudo na disciplina. Fonte: Elaborado pelo autor, 2018. No DW, temos os dados extraídos das fontes de dados da organização, como visto anteriormente, dados dos sistemas transacionais. No componente de ferramentas do DW, temos os recursos para manipulação e análise dos dados extraídos. O componente Business Performance Management (BPM) tem função de monitorar e analisar o desempenho, enquanto o componente de interface ao usuário tem a função de estabelecer a interação através, por exemplo de um navegador de Internet, apresentando um dashboard, ou seja, um painel de controle para a equipe. Você verá agora, de forma mais aprofundada, o que vem a ser o Data Warehouse, conceito mais importante para esta disciplina de Banco de Dados II, no contexto de Business Intelligence. 47 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO 2.3 O DATA WAREHOUSE (DW) Conforme figura a seguir ilustra, o DW é um componente de BI que tem como fonte de dados os sistemas transacionais. Também há outras fontes que podem fazer parte do ambiente de transações da organização, como planilhas, arquivos textuais, entre outros. FIGURA 9 - AMBIENTE DE BANCO DE DADOS NO ECOSSISTEMA DE BI FONTES DE DADOS Presente em sistemas transacionais, como SGBDs relacionais, principalmente. EQUIPE TÉCNICA Constroi o Data Warehouse por meio de processamentos: limpeza; combinação; tratamento de duplicidades presentes em banco de dados relacionais. DATA WAREHOUSE O armazém de dados em si, com as fontes já processadas. Fonte: Adaptado de TURBAN, 2009. Dessa maneira, o ambiente se constrói envolvendo uma equipe técnica para proces- sar as fontes de dados e criar o Data Warehouse em si, com os dados inicialmente presentes em sistemas transacionais, principalmente. Para a definição do conceito de Data Warehouse (DW), Turban afirma que: Um DW (...) é um banco ou repositório de dados especial preparado para dar suporte a aplicações de tomada de decisão. As aplicações variam de simples gerações de relatórios ou consultas a complexas otimizações. O DW é construído com as metodologias, principalmente metadados e ETL (TURBAN, 2009, p. 29). 48 BANCO DE DADOS II FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017SUMÁRIO Portanto, por definição, observa-se que um DW é um outro repositório de dados, ou seja, um banco de dados separado, tratado e com outra estrutura do(s) banco(s) de dados dos sistemas transacionais. 2.3.1 CARACTERÍSTICAS DO DW Por ser um novo repositório com intuito de oferecer suporte à tomada de decisões, segundo Turban (2009), o DW possui as características listadas a seguir. 1. O DW é orientado por assunto: dentro do DW, os dados são mantidos de forma organizada por assunto em detalhes, de forma minuciosa, ou seja, é organiza- do por assunto pormenorizado. Somente dados para tomadas de decisão são armazenados no DW. O fato de o DW ser orientado por assunto permite esta- belecer o desempenho da empresa e também o motivo do desempenho. Um DW se diferencia de banco de dados operacionais e de sistemas transacionais, sendo estes, em sua maioria, orientados por produto e feitos para lidar com transações. Essa orientação por assunto em um DW cria uma visão maior da organização. 2. O DW é integrado: ele deve armazenar dados de fontes diferentes de maneira consistente e integrada. Deve processar, por exemplo, conflitos de nomencla- turas e discrepâncias entre unidades de medida vindos das fontes de dados transacionais.3. O DW é variável ao tempo: isso significa que o DW mantém dados históricos. Os dados não necessariamente mostram o status atual (exceto em sistemas em tempo real). Eles mantêm dados para detecção de tendências e variações, além de relações a longo prazo, com intuito de previsão e comparações, o que leva então a insights para a tomada de decisões. O tempo é importante e todo DW deve oferecer suporte, pois os dados de análise que chegaram de variadas fon- tes apresentam diversos pontos de tempo, como visualizações de meses, sema- nas e dias. 49 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO 4. O DW é não volátil: os usuários não podem alterar/atualizar os dados nele inse- ridos. Com isso, dados obsoletos serão desprezados e alterações são registradas como sendo novos dados. Isso permite que o DW seja configurado quase que exclusivamente para o acesso a dados. Turban (2009) aponta ainda outras características inerente aos DWs, conforme pode- mos observar no quadro a seguir. QUADRO 5 - OUTRAS CARACTERÍSTICAS DE DATA WAREHOUSE Característica Descrição Baseado na web Os Data Warehouses normalmente são cria- dos para apresentar um ambiente informati- zado eficiente às aplicações baseadas na web. Relacional/multidimensional Usa ou uma estrutura relacional ou uma es- trutura multidimensional. Cliente/servidor Usa a arquitetura cliente/servidor para pro- porcionar aos usuários acesso fácil. Em tempo real Os Data Warehouses mais novos oferecem acesso a dados e recursos de análise em tem- po real ou ativos (consulte Basu, 2003 e Bon- de; Kuckuk, 2004). Inclui metadados Contém metadados (dados sobre dados) acerca de como os dados estão organizados e como usá-los de forma eficiente. Fonte: Adaptado de TURBAN, 2009. 2.3.2 ARQUITETURAS DE ACESSO AO DW Um Data Warehouse pode se apresentar em diferentes arquiteturas para acesso, con- forme veremos a seguir e na lista e na figura. 1. Duas camadas: a estação de trabalho se comunica diretamente com o servidor de aplicação e banco de dados de forma conjunta; é bastante comum. 2. Três camadas: a estação de trabalho se comunica com o servidor de aplicação, que, 50 BANCO DE DADOS II FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017SUMÁRIO por sua vez, vai se comunicar com o servidor de banco de dados; também é comum. 3. Baseado na web: o cliente se conecta, por um navegador, ao servidor da web, que, por sua vez, se conecta ao servidor de aplicação que atende ao DW. FIGURA 10 - DIFERENTES ARQUITETURAS DE ACESSO AO AMBIENTE DE DW Fonte: Adaptado de TURBAN, 2009. 51 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO 2.3.3 COMPOSIÇÃO DO DW Como já visto, o Data Warehouse possui quatro características principais e cinco ou- tras características observadas por Turban (2009). Sabendo então como eles são ca- racterizados, tem-se como eles são compostos, independentemente da arquitetura de acesso: duas camadas, três camadas ou baseado na web. O ambiente em si de um DW é composto pelos elementos, conforme será apresen- tado a seguir. Para que você não fique confuso, imagine que agora você enxergará o DW com uma lupa dentro do ambiente em que ele está incluído, para então com- preender suas partes/funcionamento do ambiente. 2.3.4 DATA MART Em um ambiente de um DW, temos subconjuntos menores denominados como Data Marts. Entenda ambiente como sendo o macro que envolve o DW, como banco de dados. Um Data Mart se concentra em assunto específico ou também em um depar- tamento. Portanto, consiste sempre em um tema, por exemplo, no departamento de marketing ou no departamento de operações da organização, conforme figura a seguir. FIGURA 11 - REPRESENTAÇÃO DE DATA MART COMO SUBCONJUNTOS RESULTADO DO DW Data Warehouse Data Warehouse Data Mart Vendas Data Mart Compras Data Mart Estoque ... Fonte: Elaborado pelo autor, 2018. 52 BANCO DE DADOS II FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017SUMÁRIO Os Data Marts podem se apresentar como dependen- tes ou independentes, segund aponta Turban (2009): Um Data Mart dependente é um subconjunto cria- do diretamente a partir do Data Warehouse. Ele tem a vantagem de usar um modelo de dados consistente e apresentar dados de qualidade. Os Data Marts de- pendentes suportam o conceito de um único modelo de dados em toda a empresa, mas o Data Warehouse deve ser construído antes. Eles garantem que o usuário final visualize a mesma versão de dados acessada por todos os outros usuários do Data Warehouse. O alto custo deste último limita seu uso às grandes empre- sas. Como alternativa, muitas empresas usam uma ver- são de Data Warehouse reduzida em custo e escala, denominada Data Mart independente. Um Data Mart independente é um warehouse pequeno, projetado para uma Unidade Estratégica de Negócios (UEN) ou um departamento, mas cuja fonte não é um Data Wa- rehouse Empresarial (EDW) (TURBAN, 2009, p. 58). 2.3.5 DATA STORES OPERACIONAIS (ODS) Um ambiente de DW pode conter também o chamado Data Store Operacional (ODS), que estabelece uma forma de arquivar informações recentes para consumo. Trata-se de um banco de dados constantemente usado como área para processa- mento temporário de um DW. 53 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO Ao contrário de valores estáticos de um DW, os conteú- dos inerentes de um ODS são atualizados durante as operações comerciais. Um ODS é usado então para de- cisões de curto prazo que envolvem, por sua vez, aplica- ções vitais, e não para decisões de médio/longo prazo. Então, por comparação, um DW é como um repositório de dados de longo prazo, pois armazena dados de for- ma permanente. Um ODS, por sua vez, consolida dados de diversas fontes e dá uma visão integrada e quase em tempo real dos dados voláteis das fontes de dados. FIGURA 12 - INCLUSÃO DO ODS NO AMBIENTE DE DW Data Warehouse Data Warehouse Data Mart Vendas Data Mart Compras Fontes de dados Staging Area ODS Fontes de dados Data Mart Estoque ... Fonte: Elaborado pelo autor, 2018. Conforme a figura apresenta, temos que em um ambiente de Data Warehouse o ODS está um passo antes do DW em si, como área para processamento temporário entre o DW e as fontes de dados. Essa área ao qual está incluído o ODS é conhecida como Staging Area, ou seja, área de preparação. 54 BANCO DE DADOS II FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017SUMÁRIO 2.4 EXTRAÇÃO, TRANSFORMAÇÃO E CARGA (ETL) Agora você verá o processo de entrada de dados para o DW. Para que as fontes de dados sejam processadas na Staging Area, são usadas tecnologias de extração, trans- formação e carga, conhecidas como Extract, Transform, Load (ETL). Turban (2009) aponta que o processo envolvendo o ETL se dá pela leitura das fontes de dados (extração), a conversão de dados que foram extraídos (transformação) para que sejam inseridos (carga) no DW. O passo de transformação acontece por meio de regras ou tabelas de busca ou pela combinação dos dados com outros dados. Observa-se que essas três operações são integradas em uma ferramenta para extrair dados de um ou mais bancos e colocá-los no Data Warehouse de forma con- solidada. Turban (2009) aponta também que a ETL é de extrema importância na integração de dados no am- biente de DW, sendo objetivo do processo carregar da- dos integrados e limpos no DW. Os dados usados nesses processos podem ser oriundos de qualquer fonte: uma aplicação mainframe, software de ERP, umaferramenta de CRM, arquivos de texto, planilhas Excel, entre outras. O processo de ETL pode ser observado na figura a seguir. 55 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO FIGURA 13 - PROCESSO DE ETL Aplicação pronta Sistema legado Extrair Transformar Limpar Carregar Outras aplicações internas Fonte de dados temporário Data warehouse Data mart Fonte: TURBAN, 2009, p. 72. O processo de realizar a carga dados em um DW pode ser realizado por meio de ferramentas de transformação de dados que fornecem e disponibilizam Interfaces Gráficas (GUI) para auxiliar no desenvolvimento/manutenção de regras de negócios. É importante notar que esse procedimento pode tam- bém ser realizado por meios tradicionais, como desen- volvimento próprio de aplicações com finalidade de carregar o DW, usando, para isso, linguagens de progra- mação diversas, como a Linguagem C, com uso direto do .Net Framework. Diversas questões afetam a decisão de uma empresa que está em busca de ferramentas de ETL entre adotar ou construir por si própria. Conforme aponta Turban (2009): As ferramentas de transformação de dados são caras. As ferramentas de trans- formação de dados têm uma longa curva de aprendizado. É difícil medir o desempenho da organização de TI até que ela aprenda a usar as ferramentas de transformação de dados (TURBAN, 2009, p. 73). 56 BANCO DE DADOS II FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017SUMÁRIO 2.5 OUTRAS ARQUITETURAS DE UM AMBIENTE DE DW Com a compreensão das características, dos componentes e da forma de composi- ção de dados em um DW, podemos visualizar arquiteturas alternativas em um am- biente de DW. As figuras a seguir representam essas outras arquiteturas, segundo Turban (2009). FIGURA 14 - ARQUITETURA DE UM DW EMPRESARIAL Sistemas-fonte Metadados centrais Análise de dados Análise de dados RDBMS Data warehouse central Data mart E T L Fonte: TURBAN, 2009, p. 65. 57 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO FIGURA 15 - ARQUITETURA DE DATA MART RDBMS Metados locais Data mart de vendas Data mart �nanceiro Data mart de recursos humanos Metados locais Metados locais RDBMS MDB Sistemas-fonte E T L Fonte: TURBAN, 2009, p. 66. FIGURA 16 - ARQUITETURA EM ESTRELA DE DATA MART E T L Metados locais Metados locais Metados locais Metados central MDBRDBMSRDBMS Análise de dados Análise de dados Análise de dados Sistemas-fonte Fonte: TURBAN, 2009, p. 66. 58 BANCO DE DADOS II FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017SUMÁRIO FIGURA 17 - ARQUITETURAS ALTERNATIVAS PARA EMPREENDIMENTOS DE DW Fonte: TURBAN, 2009, p. 68. Você poderá realizar a leitura complementar sobre essas diversas arquiteturas consultando o livro “Business In- telligence: um enfoque gerencial”, nas páginas 62 a 70. 59 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO É importante entender que, para a compreensão com- pleta de Business Intelligence, se faz necessário o estudo de outras partes que envolvem esse universo aplicado aos negócios, como, por exemplo, a parte que trata es- pecificamente das ferramentas On-line Analytical Pro- cessing (OLAP) para a análise dos dados já contidos no warehouse. Nesta unidade de estudo, o foco foram os conceitos de banco de dados no contexto de BI. Acon- selha-se, então, que você aprofunde seu conhecimento realizando a leitura dos livros indicados na bibliografia desta unidade, para compreender por completo as ou- tras áreas de estudo em BI. 2.6 O SUCESSO NA ADOÇÃO DE DATA WAREHOUSE Você pode estar se perguntando os motivos de se trabalhar com DW e toda esse con- junto de componentes, ferramentas e arquiteturas, visto que é possível criar nas pró- prias aplicações as análises necessárias para tomadas de decisões dentro das organi- zações. Primeiramente, é necessário entender que Business Intelligence é aplicado em organizações que geram gigantescos volumes de dados, ou seja, é uma prática adotada em grandes organizações. Certo, com isso você pode imaginar: 60 BANCO DE DADOS II FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017SUMÁRIO Grandes redes de supermercado, grandes lojas de vendas on-line podem operar em diferentes países, atendendo a milhões de usuários com quantidade enorme de transa- ções diárias. Não menos importante, pode-se enxergar setores industriais ou até mesmo de varejo, que são gran- des organizações que vendem produtos a outras grandes organizações em grande escala. Para ficar ainda mais cla- ro: uma grande fornecedora de aço que comercializa seu produto final para outras grandes empresas montadoras de automóveis. Em todos esses cenários, há grandes volumes de transações acontecendo a todo momento e cada uma dessas organizações precisa focar prioritariamente em suas vendas, em logística, em qualidade, etc. Mas e os insights que abordamos no início deste estudo? Ou seja, e a descoberta de novas informações para tomadas de decisão? É aí que entra todo o resultado da adoção de Business Intelligence e, consequentemente, as tecnologias que envolvem o ambiente de Data Warehouse, suas ferramentas de análises e interfaces para dispo- nibilização de resultados. Você vai perceber exemplos reais de insight gerados através disso tudo tratado até aqui. Segundo noticiado na Revista Exame (2011), o governo do estado de Massachusetts, nos EUA, realizava compilação de informações financeiras pela impressão de diversas telas dos terminais de computadores. Com isso, eram necessárias seis pessoas traba- lhando somente para reunir os relatórios do orçamento anual. Com a implantação do Data Warehouse, as informações de forma atualizada ficam à disposição on-line para 1.300 usuários. Isso resultou em economia de 250 mil dólares ao ano. Observamos a diminuição de gastos como benefício da adoção de DW. A notícia ainda revela que outra rede de varejo conseguiu descobrir, por meio do uso de tecnologias de DW e BI, que a venda de colírios subia em vésperas de feriados. Dessa forma, passou a organizar promoções e estoque do produto com base nessa nova informação. Observam-se no- vas oportunidades de vendas a partir de sazonalidade, ou seja, em certos períodos. Continuando com exemplos da matéria noticiada, o banco Itaú realizava envios de mais de 1 milhão de cartas para todos os correntistas. Anteriormente à adoção das 61 FACULDADE CAPIXABA DA SERRA/EAD Credenciada pela portaria MEC nº 767, de 22/06/2017, Publicada no D.O.U em 23/06/2017 BANCO DE DADOS II SUMÁRIO técnicas envolvendo BI, foi observado que, no máximo, 2% deles respondiam às pro- moções. Após uso de DW, em 2011 o banco tinha toda a movimentação financeira de seus 3 milhões de clientes dos últimos 18 meses. Assim, possibilitou que as cartas fossem enviadas apenas a quem tinha maior potencial de respondê-las. O resultado disso: aumento de 30% da taxa de retorno e o custo com correio reduziu em um quinto no ano. Observa-se a mudança estratégica positiva com a adoção de DW. Um dos líderes do mercado americano em telefonia, chamada Sprint, criou, a partir de seu warehouse, um método capaz de realizar previsões, atestando, inclusive com 61% de segurança, se um consumidor realizaria a mudança de companhia telefônica dentro de um período futuro de dois meses. Usando marketing agressivo, conseguiu evitar a saída de 120 mil clientes e a perda de 35 milhões de dólares no faturamento. Outra empresa de telefonia fez a detecção, ao implantar seu
Compartilhar