Bancos de Dados

Curitiba 2016 Banco de Dados Giulliana Martins de Souza BancosDadosindd 1 17102016 095952 Ficha Catalográfica elaborada pela Fael Bibliotecária Cassiana Souza CRB91501 S729b Souza Giulliana Martins de Banco de dados Giulliana Martins de Curitiba Fael 2016 224 p il ISBN 9788560531653 1 Banco de dados 2 Modelagem de dados 3 Modelo racional Informática 4 SQL Programa de computador I Título CDD 00574 Direitos desta edição reservados à Fael É proibida a reprodução total ou parcial desta obra sem autorização expressa da Fael FAEL Direção de Produção Fernando Santos de Moraes Sarmento Coordenação Editorial Raquel Andrade Lorenz Revisão e Diagramação Editora Coletânea Projeto Gráfico Sandro Niemicz Capa Vitor Bernardo Backes Lopes Imagem da Capa ShutterstockcomScanrail1Frannyanne Revisão de diagramação Evelyn Caroline dos Santos Betim BancosDadosindd 2 17102016 095952 Sumário Dedicatória 5 Carta ao Aluno 7 1 Bancos de dados 9 2 Modelagem de Dados 25 3 Modelo Relacional e Normalização 43 4 SQL 59 5 SQL DML 79 6 SQL Avançado 103 7 Transações e Técnicas Avançadas 127 8 Recursos Avançados 151 9 Backup Restore Segurança e Otimização 169 10 Tecnologias Avançadas 189 Conclusão 205 Gabarito 207 Referências 219 BancosDadosindd 3 17102016 095953 BancosDadosindd 4 17102016 095953 Dedico este livro à minha família Agradeço aos meus pais Cleo nice e Rubens pela sua dedicação por me ensinar que o melhor caminho é o do estudo e por fazêlo prioridade na minha educação Obrigada tia Neide por me introduzir ao mundo fascinante da leitura desde criança e ainda melhorando a interpretação de texto na hora de contar as estórias dos livros lidos BancosDadosindd 5 17102016 095953 BancosDadosindd 6 17102016 095953 Prezadoa alunoa Hoje é comum referirse à informação como o maior patri mônio de uma organização Considerando que grande parte dos sistemas de informação e aplicativos em geral manipulam dados e informações que ficam armazenados permanentemente conhecer sobre banco de dados tornase imprescindível Banco de dados é uma das grandes áreas da computação responsável por cuidar das técnicas de coleta armazenagem recu peração e distribuição de dados O termo banco de dados também designa genericamente o software utilizado para gerenciar todos os dados de um empreendimento A área de banco de dados é ampla e fascinante com um mercado de trabalho atraente diferentes áreas de atuação e muitas vagas à espera de bons profissionais Carta ao aluno BancosDadosindd 7 17102016 095953 8 Banco de Dados Este livro foi elaborado de forma que o aprendizado seja sequencial fácil e prático O conteúdo explica a essência de banco de dados abordando a teoria acadêmica e a prática profissional Isso permite que no futuro você tenha conhecimento suficiente para se preparar para concursos se especializar em banco de dados ou seguir a carreira acadêmica Os capítulos desse livro foram cuidadosamente elaborados mostrando as características de bancos de dados atuais do mercado Ele não foi escrito especificamente para o público que deseja trabalhar diretamente com banco de dados e sim para profissionais de todas as áreas que queiram compreender a criação e funcionamento do banco para programar ou fazer a análise do sis tema Os profissionais que pretendem ser administradores de banco de dados e também gestores que tomam decisões sobre o futuro da empresa utilizando ferramentas atuais encontrarão seu espaço no conteúdo deste material Espero que esta obra seja útil para estimular o leitor sobre o tema e desta forma despertar o interesse em desbravar as técnicas dos bancos de dados Desejo a você muita diversão nesta leitura BancosDadosindd 8 17102016 095953 Banco de dados é a estrutura utilizada para armazenar dados que precisam ser recuperados posteriormente por algum motivo ou necessidade específica Por exemplo quando é necessário armaze nar um cadastro de clientes ou até mesmo uma agenda telefônica podese utilizar um caderno ou uma agenda em papel preparada para isso Bancos de dados 1 BancosDadosindd 9 17102016 095953 10 Banco de Dados Quando se utiliza uma quantidade pequena de dados é fácil recuperá los e mesmo alterálos mas tudo ainda deve ser feito de modo manual Com a automatização causada pela utilização de computadores tornouse necessá ria a mudança da forma de trabalhar com o armazenamento dos dados Objetivos de aprendizagem 2 definir bancos de dados 2 mostrar seus principais conceitos 2 apresentar seus principais tipos e estruturas 11 Definição Segundo Date 2000 sistema de banco de dados é basicamente um sis tema computadorizado de armazenamento de registros ou seja um sistema cujo propósito é armazenar informações e permitir ao usuário buscar e atualizar as informações quando solicitado Elmasri e Navathe 2013 definem banco de dados como uma coleção de dados relacionados em que dados indicam fatos conhecidos que podem ser registrados e apresentam significado implícito Bancos de dados precisam de um equipamento onde os dados possam ser armazenados e posteriormente recuperados Esses dados podem ser defi nidos como qualquer tipo de estrutura que precisa ser armazenada inicial mente os dados eram apenas letras e números por exemplo nome endereço telefone salário hoje em dia são armazenadas também voz e imagens Inicialmente bancos de dados foram criados para serem utilizados em insti tuições financeiras e principalmente pelas Forças Armadas que tinham o obje tivo de armazenar grande quantidade de dados que depois pudessem ser recu perados Com a evolução dos computadores de grande porte Mainframes para os computadores pessoais personal computers PC passaram a existir bancos de dados de pequeno e médio porte que são utilizados em empresas de menor porte Bancos de dados são formados pelo hardware que é a parte física em que os dados estão armazenados e pelo software que gerencia os dados Atual mente há equipamentos como celulares e tablets com grande capacidade de armazenamento que também utilizam recursos de bancos de dados BancosDadosindd 10 17102016 095953 11 Bancos de dados A figura 11 mostra a representação de ações que podem ser feitas em um sistema de banco de dados caracterizandose por um conjunto de discos em que se pode executar várias operações como 2 recuperar dados 2 inserir dados 2 remover dados 2 alterar dados 2 transferir dados para outros dispositivos como pen drives ou outros discos 2 eliminar dados que não são mais necessários 2 copiar dados 2 manter a segurança das informações que estão armazenadas 2 executar a manutenção física e lógica dos dados que estão armazenados Figura 11 Banco de dados Fonte ShutterstockcomColorlife BancosDadosindd 11 17102016 095953 12 Banco de Dados Outro ponto que deve ser observado é a diferença de nomencla tura por exemplo banco de dados é qualquer estrutura que armazena os dados mas também temos os sistemas gerenciadores de banco de dados SGBD e os sistemas de banco de dados SBD Qual seria a diferença entre eles Com o aumento da complexidade dos dados e a necessidade de segu rança para esses dados que eram simplesmente armazenados em um lugar onde qualquer um pudesse acessar a qualquer momento mesmo sem a permissão necessária ou até mesmo a necessidade de criação de cópia de segurança dos dados em caso de alguma falha no equipamento observouse a necessidade de criar recursos de segurança e melhorias na estrutura do banco de dados Para isso foram criados o que chamamos de sistemas de banco de dados ou sistemas gerenciadores de bancos de dados A diferença é que os sistemas gerenciadores de banco de dados SGBD além do arma zenamento dos dados fornecem uma infraestrutura de software que protege e otimiza a utilização e o acesso aos dados ou seja um SGBD é composto por dados e programas Atualmente não existe uma padronização para nomenclatura então muitas vezes um SGBD é referido simplesmente como banco de dados Para utilizar um SGBD figura 12 utilizase os aplicati vos de usuários ou de administra dores de SGBD que via software acessam o SGBD Nesse caso é feita a validação dos privilégios e em caso deste ser válido segue para o módulo que escolhe o caminho e acessa os dados retornando para quem solicitou o acesso Até o ponto de acesso aos dados tudo é gerenciado por software SW a partir deste ponto solicitase o acesso aos dispositivos de hard ware HW onde estão armazena dos fisicamente os dados Figura 12 Visão macro de um SGBD HW Aplicativos de usuários Validação de privilégios Acesso aos Dados Metadados Dados SW Fonte elaborado pelo autor BancosDadosindd 12 17102016 095953 13 Bancos de dados 12 Conceitos Os programas que foram incluídos nos sistemas gerenciadores de banco de dados servem para auxiliar e resolver problemas como os apresentados por Silberchatz 1999 2 Inconsistência e redundância de dados evita que a informação seja repetida em diversos lugares e de maneira diferente umas das outras Por exemplo a mudança de endereço de um cliente poderia ser atualizada em um lugar e não no outro deixando a dúvida de qual o endereço deve ser o correto 2 Dificuldade de acesso aos dados e isolamento dos dados garantir que ao projetar o banco de dados sejam atendidas todas as solici tações e ações necessárias às tarefas dos usuários agregando dados que estejam dispersos em vários arquivos 2 Problemas de integridade os valores dos dados atribuídos e arma zenados devem satisfazer restrições para garantir a consistência Por exemplo todo cliente cadastrado deve ter um nome 2 Problemas de atomicidade em caso de falhas em um sistema de banco de dados devese garantir que a transação executada seja completa mente finalizada ou totalmente desfeita Por exemplo no caso de trans ferência de saldo entre contas correntes se após o saque da conta ori ginal ocorrer uma falha o sistema deve garantir já que não foi possível crédito na conta destino que o dinheiro seja devolvido à conta origem 2 Anomalias no acesso recorrente muitas vezes acontece o acesso simultâneo a determinados dados em um banco nesse caso o SGBD deve garantir que o dado seja acessado pelas demais solicita ções somente depois de finalizada a ação já iniciada 2 Problemas de segurança somente as pessoas autorizadas devem acessar o banco de dados e de acordo com privilégio permitido a cada um dos usuários Importante A sigla ACID resume os itens que são garantidos pelo SGBD Atomici dade Consistência Integridade e Durabilidade BancosDadosindd 13 17102016 095953 14 Banco de Dados Em relação à utilização de bancos de dados podese definir vários tipos conforme segue 2 analista ou projetista de banco de dados analisa o negócio e as necessidades para a modelagem e criação do banco 2 administrador de bancos de dados DBA responsável por cria ção gestão e manutenção dos bancos de dados Podem ser dividi dos em DBA de desenvolvimento projeto ou produção 2 usuários finais utilizam os bancos de dados por meio de aplicati vos que acessam o SGBD 2 usuários sofisticados analistas de negócios que muitas vezes utili zam ferramentas de business intelligence BI para acessar os bancos de dados e extrair relatórios e gráficos Todos os usuários são importantes para o projeto e criação do banco de dados afinal cada uma de suas tarefas vai definir o que precisa ou não ser armazenado 13 Estrutura de banco de dados Os bancos de dados são divididos e armazenados fisicamente em arqui vos Para o sistema operacional esses arquivos têm estruturas próprias e geralmente só podem ser abertos corretamente pelo SGBD Ao tentar aces sar os arquivos por outra ferramenta tentar mover o arquivo ou até mesmo apagálo enquanto o SGBD estiver em funcionamento a ação não será permitida pelo sistema operacional que enviará a mensagem de erro de arquivo em uso Como banco de dados é um consumidor muito grande dos recursos do sistema operacional principalmente na entrada e saída de dados input e output IO utilização da CPU processador e de memória existem funções dentro do SGBD que se comunicam diretamente com o sistema operacional via chamadas de sistema system calls Dependendo do SGBD utilizado ele pode oferecer diferentes estru turas de auxílio no gerenciamento dos recursos Alguns oferecem somente BancosDadosindd 14 17102016 095953 15 Bancos de dados o acesso aos dados com tratamento básico de proteção e privilégios outros oferecem recursos de programação dentro do banco de dados como triggers functions e stored procedures além de um nível profundo de gerenciamento de proteção e segurança como criptografia O mais importante é conhecer as estruturas e analisar qual o SGBD mais apropriado para as necessidades de determinado cliente Para conversar com o banco de dados é necessária uma linguagem espe cífica para que o SGBD entenda quais ações devem ser executadas Atual mente a linguagem mais utilizada para banco de dados é a SQL Structured Query Language que permite definir manipular e gerenciar estruturas de dados de um banco de dados Um item especial da linguagem SQL são as instruções DDL Data Definition Language Essas instruções de linguagem de definição de dados são utilizadas para criar os SGBDs e as estruturas que o compõem A figura 13 apresenta os DBAs que podem executar comandos privile giados e instruções DDL DBAs têm privilégios de system administrator dentro do banco de dados ou seja eles podem executar qualquer tarefa dentro do banco de dados Após as instruções DDL vem o compilador DDL que verifica se a instrução está escrita de maneira correta após essa etapa a execução é feita numa área especial de armazenamento do banco de dados que é conhecida como catálogo do sistema dicionário de dados ou metadados Nessa área também são armazenados os dados estatísticos que direcionam a melhor forma de conduzir a pesquisa dentro do banco de dados Além dos DBAs acessam banco de dados usuários casuais programas de aplicação e usuários paramétricos Podese ter consultas interativas e consultas já existentes nos programas de aplicação As consultas podem passar por um compilador de consulta e por um compilador de DML Data Manipulation Language outro grupo de instruções SQL utilizado para a manipulação de dados para verificar se a instrução SQL está correta Após a verificação da estrutura podese acessar o otimizador de consulta para escolher nas estatís ticas do catálogo do sistema o melhor caminho a ser percorrido para buscar o resultado necessário para a solicitação BancosDadosindd 15 17102016 095953 16 Banco de Dados Na parte inferior da figura 13 além do catálogo do sistema existe a área utilizada para o armazenamento dos dados do banco de dados O subsistema controla a concorrência de acesso aos dados e também é responsável por backup cópia de segurança e recuperação de dados em caso de alguma falha física do equipamento ou do usuário O subsistema gerenciador de dados armazenados verifica em qual arquivo físico do sis tema operacional deve armazenar os dados e como a manutenção física deve ser feita Figura 13 Módulos componentes de um SGBD Entradasaída do banco de dados Instruções DDL Comandos privilegiados Consulta interativa Programas de aplicação Usuários DBAs Usuários casuais Programadores de aplicação Usuários paramétricos Compilador DDL Compilador de consulta Précompilador Compilador de linguagem hospedeira Otimizador de consulta Compilador DML Transações compiladas Catálogo do sistema Dicionário de dados Processador de banco de dados em tempo de execução Comandos consultas e transações do DBA Gerenciador de dados armazenados Subsistemas de controle de concorrênciabackuprecuperação Banco de dados armazenado Execução de consulta e transação Fonte Elmasri Navathe 2013 BancosDadosindd 16 17102016 095953 17 Bancos de dados O processador de banco de dados em tempo de execução recebe coman dos consultas e transações verifica o catálogo do sistema e finalmente faz o acesso aos dados devolvendo o resultado para quem solicitou a execução Bancos de dados podem ser classificados de acordo com a sua estrutura Podem ser de modelo hierárquico redes relacionais e orientados a objetos Atualmente o modelo mais utilizado é o relacional A arquitetura de um SGBD SILBERSCHATZ 1999 pode ser defi nida em três esquemas figura 14 cujo objetivo é separar o banco de dados físico do usuário comum de acordo com a visibilidade da estrutura que cada um pode ter Segundo a literatura eles podem ser divididos em níveis 2 Externo mostra a visão que o usuário tem do banco de dados Para cada usuário ou grupo de usuário é apresentada somente a parte do banco necessária para sua utilização 2 Conceitual também conhecido como modelo lógico é a descrição do banco de dados inteiro mostra entidades relacionamentos e restrições 2 Físico ou interno apresenta como o banco de dados está armaze nado fisicamente descrevendo detalhes e caminhos de acesso Figura 14 Arquitetura de três esquemas Usuários Finais Visão Externa 1 Visão Externa n Esquema Conceitual Nível Conceitual lógico Esquema Interno Nível Interno físico Nível Externo usuário Fonte Elmasri Navathe 2013 BancosDadosindd 17 17102016 095953 18 Banco de Dados Todas as estruturas apresentadas dos SGBDs têm suas tarefas específi cas e complexidade cujo objetivo é facilitar e otimizar as tarefas de acordo com a funcionalidade Por exemplo as estatísticas permitem que se escolha o melhor caminho para acessar os dados otimizando o tempo de resposta enquanto os compiladores de DDL e DML verificam o comando antes de acessar os dados garantindo que não executem acessos desnecessários cau sados por comandos errados A arquitetura facilita a visibilidade de cada um dos agentes que utilizam o SGBD e a sua classificação auxilia na escolha daquele que melhor se adéqua a cada situação 14 Utilitários de SGBD Os SGBDs além de apresentação de gerenciamento dos dados e da segurança destes podem oferecer outras ferramentas para auxiliar no geren ciamento de banco de dados As ferramentas podem ser as seguintes 2 carga existe a necessidade de carregar arquivos de dados exis tentes txt csv etc para facilitar o arquivo a ser lido precisa estar no formato que o SGBD consiga reconhecer com isso a ferramenta de carga recebe o arquivo e importa para o SGBD Existe também a opção de importar e exportar dados que per mite a conexão com outros SGBDs para carregar dados ou enviar dados 2 backup na história da computação existe uma ferramenta impor tante que deve ser usada em todos os aplicativos o backup afinal erros físicos e lógicos podem acontecer Um usuário pode apagar registros tabelas ou até mesmo o banco de dados intencionalmente ou não falhas físicas também podem acontecer nos discos memó ria CPU etc A melhor forma de garantir que pode se recuperar o banco de dados é fazer uma cópia de segurança também chamada de backup Essas cópias podem ser feitas em disco ou fitas devem ser feitas com frequência e guardadas em lugar seguro Os backups podem ser criptografados e protegidos por senha para evitar acesso indevido aos dados BancosDadosindd 18 17102016 095953 19 Bancos de dados 2 reorganização à medida que os dados vão sendo inseridos o banco de dados vai expandindo a sua ocupação nos discos físicos mas à medida que os dados são apagados os espaços ocupados por esses dados são deixados vazios Dependendo da frequências das opera ções e da quantidade de dados que estão sendo apagados os arqui vos de dados e índices do banco de dados vão ficando fragmentados semelhante a fragmentação de disco do sistema operacional para melhorar o acesso e desempenho além de liberar espaço em disco o SGBD permite a reorganização dos dados e índices 2 monitoração o SGBD pode monitorar o ambiente do banco de dados e gerar alertas para determinados erros ou condições enviando emails mensagens etc 2 análise de desempenho essa ferramenta é muito importante pois consegue analisar o ambiente de memória CPU disco consultas Query e outras estruturas do SGBD sugerindo melhorias criação de índices e aumento de memória Dependendo do SGBD existem outras ferramentas para Data Mining Dataware house Business Inteligence e outras mais À medida que a necessi dade dos usuários vai crescendo os SGBDs vão evoluindo e apresentando ferramentas integradas ou não que podem ser o diferencial na escolha do SGBD pelo cliente 15 Arquiteturas de banco de dados A arquitetura de banco de dados segue a evolução das arquiteturas com putacionais Existem as seguintes arquiteturas 2 centralizada 2 cliente servidor 2 distribuídos 2 orientado a objeto 2 virtualização 2 nuvem BancosDadosindd 19 17102016 095953 Banco de Dados A tendéncia dos SGBDs é evoluir da mesma forma Devese levar em considerag4o a necessidade de cada um Nao existe arquitetura melhor e per feita o que existe é a melhor arquitetura de acordo com cada necessidade Atualmente as mais utilizadas s4o centralizada e clienteservidor 16 Arquitetura centralizada O processamento é executado remotamente em um computador central e os dados sao enviados via rede para os computadores locais Foi muito uti lizada no inicio depois muitas aplicac6es foram direcionadas para arquitetura clienteservidor com a virtualizacao passou a ser muito utilizada novamente A figura 15 apresenta um modelo de arquitetura centralizada em que ha um computador de grande porte Mainframe rodando sobre um sistema operacional e varios terminais geralmente mdquinas sem processamento local ou PCs que emulam terminais Figura 15 Arquitetura centralizada Terminais Monitor Monitor ae Monitor de video de video de video Rede Programas Controle de Editores de aplicacao lexibicao do terminal de texto Cofrmare SGBD Compiladores Sistema Operacional Barramento do sistema Dispositivos de EIS limpressorss unidades de fita HardwareFirmware Fonte Elmasri Navathe 2013 20 BancosDadosindd 20 oe 17102016 095953 21 Bancos de dados O hardware foi se tornando mais barato e muitos usuários foram adqui rindo PCs Da mesma forma a internet passou a ser mais accessível e len tamente os sistemas SGBDs foram acompanhando a evolução e utilizando o poder de processamento oferecido pelo usuário levando à utilização de arquiteturas clienteservidor 17 Arquitetura clienteservidor Na arquitetura clienteservidor há vários clientes e podese ter um ou mais servidores Segundo Elmasri e Navathe 2013 a arquitetura cliente servidor foi desenvolvida para utilizar computadores pessoais estações de trabalho e servidores de arquivos A ideia era definir servidores especializados para cada funcionalidade A figura 16 mostra a arquitetura clienteservidor no nível lógico e a figura 17 mostra arquitetura clienteservidor em duas camadas Nessa abordagem o nível de servidor inclui o software do SGBD responsável por armazenamento concorrência e recuperação e o nível do cliente trata de funções de interações do SGBD como tipos de dados e dicionário de dados Figura 16 Arquitetura clienteservidor Rede Servidor de impressão Servidor de arquivos Servidor de SGBD Cliente Cliente Cliente Fonte Elmasri Navathe 2013 Arquitetura em duas camadas figura 18 é formada por componentes de softwares distribuídos uma parte rodando no cliente a outra rodando no servidor cuja vantagem são simplicidade e compatibilidade com os sistemas existentes BancosDadosindd 21 17102016 095953 22 Banco de Dados Figura 17 Arquitetura clienteservidor Servidor Cliente Cliente Cliente Servidor Local 1 Local 2 Local 3 Local n Rede de comunicação Cliente sem disco Cliente com disco Servidor Servidor e cliente Fonte Elsmari 2013 Com a evolução dos softwares de aplicação figura 19 passou a existir uma camada intermediária denominada servidor de aplicação que pode um servidor web por exemplo Esse serviço executa uma função interme diária entre a execução do programa de aplicação e o armazenamento de regras de negócios que podem ser procedimentos ou restrições Nesse caso o cliente tem a interface gráfica e algumas regras específicas da aplicação já o servidor de aplicação aceita e processa as solicitações do cliente e envia consultas e comandos do banco de dados para o servidor de banco de dados ELMASRI NAVATHE 2013 BancosDadosindd 22 17102016 095954 23 Bancos de dados Figura 18 Arquitetura clienteservidor em três camadas Cliente Servidor de aplicação ou Servidor Web Servidor de banco de dados Camada de apresentação Camada lógica de negócios b Camada de serviços de banco de dados GUI Interface Web Programas de aplicação páginas Web Sistema gerenciador de banco de dados a Fonte Elmasri Navathe 2013 Outra melhoria ocorrida com os avanços da tecnologia de criptografia é a possibilidade de trafegar dados confidenciais pela rede em formato crip tografado do servidor ao cliente e viceversa Isso pode ser feito tanto por hardware quanto software outro item importante é compactação de dados que ajuda a transferir grande quantidade de dados entre cliente e servidor por redes com ou sem fio Wifi Síntese O capítulo trouxe o banco de dados para o mundo conceitual Inicialmente tinhase uma visão abstrata e obscura do funcionamento de banco de dados e de um sistema gerenciador de banco de dados Apresentouse suas principais dife renças e a evolução de serviços oferecidos garantindo integridade durabilidade BancosDadosindd 23 17102016 095954 24 Banco de Dados atomicidade e consistência aos dados armazenados além das ferramentas que foram criadas para facilitar todas as tarefas necessárias Graças aos sistemas geren ciadores de bancos de dados o mundo computacional jamais será o mesmo Foram mostradas também as ferramentas que podem ser utilizadas com os SGBDs e a evolução das arquiteturas Da teoria para a prática Pesquise os SGBDs existentes no mercado e elabore um texto justifi cando qual seria o sistema adequado para cada um dos clientes a seguir 2 Confeitaria de pequeno porte utilizando sistema operacional Win dows 10 2 Papelaria de médio porte utilizando sistema operacional Linux Ubuntu 2 Sistema de agendamento de pacotes de viagens com mais de 300 lojas no Brasil utilizando sistema Windows 81 2 Sistemas de geoprocessamento para análise da geografia do estado do Amazonas BancosDadosindd 24 17102016 095954 Banco de dados BD são utilizados para armazenar dados importantes e que posteriormente possam ser recuperados Mas devese armazenar todos os dados Tudo é importante Como dife renciar e compreender o que realmente deve ir para o banco e o que não precisa ser armazenado Cada negócio e cada empresa têm dados diferentes por isso cada situação deve ser analisada cuidado samente Após essa análise quais são os próximos passos Modelagem de Dados 2 BancosDadosindd 25 17102016 095954 26 Banco de Dados Objetivos de aprendizagem 2 apreender entidade 2 conhecer relacionamentos 2 Modelo Entidade Relacionamento 2 Modelo Entidade Relacionamento Estendido 21 Modelo Entidade Relacionamento MER O Modelo Entidade Relacionamento foi criado para mapear o negócio que está sendo analisado em estruturas de modelagem de banco de dados Importante Quando analistas começam a avaliar o negócio para construir uma solução escolhem uma modelagem para desenvolvimento que pode ser análise estruturada orientada a objeto entre outras Essas solu ções são utilizadas para o desenvolvimento do sistema em si e não do banco de dados para a modelagem e o desenvolvimento do banco de dados são utilizadas outras ferramentas Durante a criação dos sistemas acontecem várias reuniões e discussões das tarefas necessárias para os clientes começando pelo levantamento e pela análise de requisitos Figura21 e quais dados são tramitados em cada etapa das tarefas Nesse momento o analista responsável pelo projeto de banco de dados deve preparar o modelo conceitual evoluir para o projeto lógico iden tificando quais dados devem ser armazenados e como e finalmente chegar ao modelo físico e à implementação Importante Durante o levantamento de dados nunca ache tenha sempre cer teza pergunte quantas vezes forem necessárias e revise o projeto com o cliente BancosDadosindd 26 17102016 095954 Modelagem de Dados Figura 21 Principais fases do projeto de BD LEVANTAMENTO E ANALISE DE REQUISITOS Requisitos funcionais Requisitos de dados ANALISE FUNCIONAL IPROJETO CONCEITUA Especificagao da transagao de alto nivel Esquema once tual em um modelo de dados de alto nivel A Independente do SGBD Marl MENTO DO Especifico do SGBD pees MODELO DE DADOS PROJETO DO J RAMA Esquema légico conceitual no modelo PROG de dados de um SGBD especifi DE APLICACAO aacos ee um Se IMPLEMENTAGCAO PROJETO FISICO DA TRANSACAO Esquema interno Programas de aplicacao Fonte Elmasri Navathe 2013 p 133 Para saber quais dados devem ser armazenados devese discutir com o cliente quais informacées transitam entre os processos identificar as caracteristicas de cada um deles e verificar quais realmente precisam ser armazenados Essa é uma etapa cansativa e delicada e nao deve ser feita com pressa e incertezas Nos itens a seguir comecaremos a estudar elementos que constituem a base para os modelos légicos fisicos 27 BancosDadosindd 27 oe 17102016 095954 28 Banco de Dados 22 Entidade Entidade é o elemento principal para a criação do modelo de entidade relacionamento É nessa estrutura que se esclarecem quais são as característi cas do objeto ou elemento que se pretende definir DATE 2000 Uma entidade pode ser uma estrutura do mundo real por exemplo um carro como também pode ser uma estrutura abstrata como o aluguel de um carro No caso do carro temse características que separam um carro de uma moto ou de um caminhão marca modelo ano de fabricação chassi quantidade de portas entre outros Podese tocar no carro ele existe de forma concreta mas no caso do aluguel do carro não existe um elemento concreto que se possa tocar existem somente características que o definem placa data da locação cliente entre outros Uma entidade é composta por atributos que são características proprie dades elementos que a definem como uma estrutura única diferente de outra entidade Aquilo que diferencia um carro de uma moto por exemplo 23 Atributos Os atributos definem a entidade e cada atributo é um elemento com valor que caracteriza a entidade Segundo Silberchatz Korth e Sudarshan 1999 os tipos de atributos são 2 simples ou compostos os atributos simples são estruturas atômi cas que não podem ser dividas em outras menores por exemplo idade E os atributos compostos são formados por mais de uma estrutura como data de nascimento formada por dia mês ano 2 monovalorados ou multivalorados monovalorados são atribu tos que só podem ter um único valor em determinado momento por exemplo a data de nascimento é única para cada pessoa mas telefones podem ser vários celular residencial e comercial nesse caso são multivalorados 2 nulos são atributos que ainda não têm valores mas em determi nado momento receberão valor por exemplo existe um registro BancosDadosindd 28 17102016 095954 29 Modelagem de Dados da análise e da publicação de um livro enquanto está em fase de análise o valor da data de publicação é nulo pois ainda não foi publicado Zero e espaço em branco não são valores nulos e sim valores válidos para os respectivos tipos de dados 2 derivados são atributos que podem ser calculados a partir de outros atributos ou a partir de fórmulas aplicadas sobre atributos Caso exis tam os atributos idade e data de nascimento podese aplicar facil mente a subtração da data atual pela data de nascimento obtendose a idade portanto a idade nesse caso é um atributo derivado Importante Siga sempre um padrão de nomenclatura para todas as entidades e atributos seja para início com letras maiúsculas ou minúsculas seja com abreviações entre outros Na Figura 22 a entidade foi nomeada Cliente e tem os atributos de CPF RG Endereço DtNasc e Telefone Em cada modelo nomes de entida des não podem ser repetidos e cada entidade deve ter um nome diferente do outro A escolha do nome da entidade deve ser feita de maneira que indique claramente o que ela representa dessa forma qualquer pessoa que ler a docu mentação compreenderá facilmente o que ela representa Figura 22 Entidade Fonte elaborado pela Autora BancosDadosindd 29 17102016 095954 30 Banco de Dados Saiba mais Para nomes de entidades e atributos é importante utilizar somente letras números e sublinhado começando sempre por letra Isso evita problemas posteriores com importação e exportação de dados e estruturas e facilita a criação de comandos e acesso a outros ban cos de dados É possível utilizar caracteres especiais e acentos mas não é recomendável Sendo a entidade formada por atributos é importante selecionar de forma crítica e com bastante atenção todos os atributos que representam a entidade de forma completa Qualquer falha no projeto do banco de dados pode comprometer toda a solução 24 Relacionamento Entre as entidades podem existir associações que são chamadas de rela cionamentos Os relacionamentos podem ter vários níveis ELMASRI NAVATHE 2013 2 binários entre duas entidades 2 ternário entre três entidades 2 nário entre mais de três entidades Quanto maior for a quantidade de relacionamentos entre as entidades mais complexo ficará o banco de dados Relacionamentos também podem ter atributos e isso acontece quando a associação entre as entidades gera mais dados que precisam ser armazenados Basicamente são as entidades que não são concretas o resultado e alguma ação ou tarefa que precisa ser armazenada por exemplo o aluguel de um carro Definir os relacionamentos é mais complexo do que se imagina As enti dades têm características que as definem claramente Os relacionamentos podem existir entre várias tabelas e dependendo da ação do processo que pode causar essa associação nem sempre é fácil visualizar Algumas vezes é neces sário olhar os possíveis valores de atributos para encontrar o relacionamento BancosDadosindd 30 17102016 095954 31 Modelagem de Dados Para facilitar a compreensão utilizase a letra E para representar uma Entidade Importante Outro fator a ser considerado é se o relacionamento e obrigatório ou não Quando for obrigatório participação total para cada elemento existente na E1 existirá pelo menos um elemento relacionado na E2 Para relacionamentos não obrigatórios participação parcial para cada elemento na E1 pode não existir nenhum elemento relacionado na E2 ou podem existir um ou mais elementos referenciados na E2 Também temos a cardinalidade restrições de mapeamento de relaciona mentos que indica o número de relacionamentos dos quais uma entidade pode participar A cardinalidade apresenta os seguintes tipos 2 11 relacionamentos um para um indicam que para cada um elemento na entidade E1 existe somente um elemento na entidade E2 A Figura 23 apresenta esse relacionamento no qual um fun cionário gerencia somente um departamento e cada departamento é gerenciado por apenas um funcionário Figura 23 Cardinalidade 11 F1 FUNCIONARIO F2 D2 F9 F4 F5 F6 F11 D3 D1 Gerencia DEPARTAMENTO Fonte elaborado pela autora BancosDadosindd 31 17102016 095954 32 Banco de Dados 2 1N relacionamentos um para N indicam que para cada elemento de E1 podem existir N vários elementos na E2 A Figura 24 apresenta esse relacionamento no qual um departamento pode ter vários empre gado mas cada empregado pertence a somente um departamento Figura 24 Cardinalidade 1N FUNCIONARIO F1 F2 F3 F4 F5 F6 F7 Tem DEPARTAMENTO D2 D3 D1 Fonte elaborado pela autora 2 NM relacionamentos N para M indicam que para cada elemento de E1 podem existir N vários elementos na E2 e para cada ele mento em E2 podem existir M vários elementos em E1 Esse relacio namento é apresentado na Figura 25 no qual um funcionário pode trabalhar em vários projetos e um projeto pode ter vários funcionários Figura 25 Cardinalidade NM F1 FUNCIONARIO F2 P2 F3 F4 F5 P1 P3 Trabalha PROJETO Fonte elaborado pela autora BancosDadosindd 32 17102016 095954 33 Modelagem de Dados Existe também um relacionamento incomum mas completamente válido chamado de relacionamento recursivo ou autorrelacionamento que ocorre quando a entidade se relaciona com ela mesma A Figura 26 mostra esse relacionamento no qual um funcionário supervisor supervisiona um ou vários funcionários Nesse caso não existe a necessidade de criar uma enti dade supervisor com os mesmos atributos de funcionários e sim criar um relacionamento da entidade com ela mesma Figura 26 Relacionamento recursivo Funcionario Supervisiona Fonte elaborado pela autora Com a definição de entidade e relacionamento podese apresentar o Diagrama de Entidade Relacionamento uma das formas de representação gráfica do modelo entidade relacionamento 25 Diagrama de Entidade Relacionamento O Diagrama de Entidade Relacionamento DER é formado por um conjunto de objetos gráficos que representa todos os objetos do modelo Enti dade Relacionamento tais como entidades atributos atributoschaves rela cionamentos restrições estruturais etc BancosDadosindd 33 17102016 095954 34 Banco de Dados O DER fornece uma visão lógica do banco de dados com um conceito mais generalizado de como estão estruturados os dados de um sistema Exis tem inúmeras formas de representação para um DER uma delas foi criada por Peter Chen como mostrado na Figura 27 na qual podese representar entidades relacionamentos cardinalidades tipos de atributos e restrições Figura 27 DER TIPO ENTIDADE ATRIBUTO TIPO ENTIDADE FRACA ATRIBUTO CHAVE TIPO RELACIONAMENTO ATRIBUTO MULTI VALORADO ATRIBUTO COMPOSTO TIPO RELACIONAMENTO IDENTIFICADOR ATRIBUTO DERIVADO E1 1 N R E2 E1 R R Taxa de Cardinalidade 1N para E1E2 em R Participação Parcial de E1 em R Participação Total de E2 em R Restrição Estrutural min max na Participação de E1 em R E2 Fonte adaptado de Chen 1990 Inicialmente essa abordagem é muito interessante para o aprendizado principalmente quando se está atuando no modelo lógico recuperando e identificando as informações de negócios BancosDadosindd 34 17102016 095954 35 Modelagem de Dados O modelo da Figura 28 mostra o DER de uma empresa que tem vários departamentos nos quais trabalham vários funcionários Os funcionários podem ter nenhum um ou vários dependentes Existem vários projetos vin culados a departamento nos quais vários funcionários podem atuar Figura 28 DER de uma empresa Fonte Elmasri Navathe 2013 p 149 Existem inúmeras maneiras de representar o mesmo modelo Analisan dose a forma gráfica é muito fácil compreender quais atributos formam as entidades quais são chaves primárias os relacionamentos existentes a cardi nalidade e as entidades fracas BancosDadosindd 35 17102016 095954 36 Banco de Dados 251 Chaves Outras restrições muito importantes em uma entidade são as chaves A entidade possui um atributo cujos valores são distintos para cada elemento entidade individual Esse atributo é chamado chave e seus valores podem ser utilizados para identificar cada elemento da entidade de forma única ou apli car a integridade referencial entre relacionamentos As chaves podem ser 2 candidatas atributos que atendem à condição de chave primária Por exemplo CPF RG 2 primárias primary key atributo cujo conteúdo não se repete em nenhum elemento dentro da entidade dessa forma esse atri buto consegue identificar cada elemento individualmente Ele deve ser único e não nulo Por exemplo CNPJ 2 concatenadas ou compostas quando a entidade não apresenta nenhum atributo que seja chave candidata ela não teria chave Mas ainda existe a opção de juntar um ou mais atributos e a combina ção desses atributos atender às necessidades da chave primária Por exemplo no relacionamento Aluguel que tem os atributos CPF Dthrlocacao ao combinarmos esses dois temos uma chave pri mária afinal um cliente pode alugar dois carros no mesmo dia mas não na mesma hora minuto e segundo 2 estrangeiras foreign key servem para criar a integridade referencial ou seja criar dentro do banco o relacionamento existente no modelo Saiba mais Quando existe mais de uma chave candidata quais critérios devem ser utilizados para escolher a melhor para ser a chave primária 2 Escolher a de menor tamanho 2 Entre atributos numéricos e strings escolher o numérico 2 Escolher o atributo mais usado pela aplicação BancosDadosindd 36 17102016 095954 37 Modelagem de Dados A escolha correta da chave primária é muito importante para o bom funcionamento do banco de dados 252 Entidades fracas As entidades fracas são aquelas que não têm chave primária nem mesmo ao tentar a composição entre os atributos chave primária composta Isso implica que as combinações dos valores de seus atributos podem ser idênticas As entidades desse tipo precisam estar relacionadas com uma entidade per tencente ao tipo entidade proprietária E esse relacionamento é chamado de relacionamento identificador Um tipo de entidade fraca possui uma chave parcial que juntamente com a chave primária da entidade proprietária forma uma chave primária composta Outra forma de se resolver essa condição é criar um campo identi ficador geralmente um atributo de autoincremento chamado código 253 Relacionamentos com grau maior que dois Relacionamentos binários ocorrem entre duas entidades mas pode haver relacionamentos ternários ou até com graus maiores A quantidade de entidades relacionadas entre si define o grau do relacionamento A Figura 29 mostra um exemplo de relacionamento ternário no qual há um fornecedor enviando peças para um projeto Nesse caso temos de dizer exa tamente quantas peças foram enviadas pelo fornecedor para o projeto Temos também mais um exemplo de um relacionamento FORNECE com atributos Figura 29 Relacionamento ternário FORNECEDOR FORNECE Fnome Quantidade PROJETO PECA Projnome Numpeca Fonte Elmasri Navathe 2013 p 151 BancosDadosindd 37 17102016 095954 38 Banco de Dados Para fazermos uma comparação veja a Figura 210 Temos três relacio namentos binários um representa quais peças o fornecedor pode fornecer quais fornecedores atendem aos projetos e quais peças podem ser enviadas para o projeto essa representação diz o que pode ser feito o que pode ser for necido e a quem bem diferente do exemplo da Figura 29 que mostra quem forneceu quais e quantas peças para quais projetos Figura 210 Relacionamento binário FORNECEDOR FORNECE Fnome PROJETO PECA Projnome Numpeca PODEFORNECER USA M M N N M N Fonte Elmasri Navathe 2013 p 151 Importante Modelar corretamente exige treino testes e experiência Cada vez que os requisitos são analisados o Administrador de banco de dados DBA aprofunda o conhecimento e melhora o modelo e somente a prática dirá quais das soluções citadas pode ser a melhor Em caso de dúvida devese perguntar ao cliente de que ele precisa exatamente se deve ser de acordo com a Figura 28 ou a Figura 29 Ambas estão corretas tudo depende da necessidade do cliente 26 Modelo Entidade Relacionamento Estendido O modelo Entidade Relacionamento permite modelar a maioria dos bancos de dados mas devido a determinadas aplicações surgiu a necessidade de novas estruturas para a modelagem de informações mais complexas BancosDadosindd 38 17102016 095955 39 Modelagem de Dados O modelo Entidade Relacionamento Estendido ERE visa fornecer essas estruturas para permitir a representação de informações complexas O modelo estendido engloba todos os conceitos do modelo ER mais os concei tos de subclasse superclasse generalização e especialização Segundo Elmasri e Navathe 2013 o modelo estendido permite demonstrar estruturas gráficas como apresentado na Figura 211 Observe que temos a entidade Funcionário com as características que são comuns às entidades Secretária Técnico e Engenheiro Essas entidades têm atributos independentes secretária tem velocidadedigitacao Técnico tem grautec e Engenheiro tem tipoeng 261 Superclasse e subclasse Supertipo e subtipo A entidade Funcionário é chamada de superclasse ou supertipo pois apresenta características comuns às demais entidades enquanto as demais entidades fazem parte da subclasse ou subtipo Além dos atributos comuns e específicos outro fator importante é identi ficar os relacionamentos quais se relacionam com cada entidade Se o relacio namento entre entidades estiver vinculado somente com Engenheiro ele deve ser vinculado à entidade engenheiro e não ao Funcionário Relacionamentos associados a todas as subclasses devem ser apontados para Funcionário Figura 211 Superclasse subclasse Pnome Minicial Unome Nome Cpf Datanascimento Endereco Tipoemprego FUNCIONARIO Tipoemprego d Secretaria Engenheiro Velocidadedigitacao SECRETARIA TECNICO Grautec Tecnico Tipoeng ENGENHEIRO Fonte Elmasri Navathe 2013 p 166 BancosDadosindd 39 17102016 095955 40 Banco de Dados Importante Superclasse e subclasse não se referem ao modelo orientado a objetos e sim à definição de grupos de relacionamento em banco de dados A subclasse nos permite 2 apresentar os subtipos de uma entidade 2 mostrar os atributos específicos para cada subtipo 2 estabelecer os relacionamentos específicos entre subtipos e outros tipos de entidades Isso é importante para que se possa decidir qual processo será seguido generalização ou especialização 262 Generalização e especialização Quando é possível definir as superclasses e subclasses podemos ava liar qual é a melhor forma de implementar a solução se será generalizada ou especializada Especialização é o processo de definição de um conjunto de classes de um tipo onde a superclasse será especializada será implementada cada enti dade separadamente A Figura 212 apresenta a especialização da superclasse Veículo a qual foi separada em carro e caminhão Figura 212 Especialização Numeropassageiros Velocidademaxima Codveiculo Placa CARRO Preco Preco CAMINHAO Numeroeixos Capacidadepeso Idveiculo Placa Fonte Elmasri Navathe 2013 p 165 BancosDadosindd 40 17102016 095955 41 Modelagem de Dados A generalização mostrada na Figura 213 é um processo inverso ao da especialização no qual são suprimidas as diferenças entre diversos tipos enti dades identificando suas características comuns e generalizando essas entida des em uma superclasse Nesse caso criando uma única entidade na qual será acrescentado o atributo tipo de dado e esse tipo terá o valor que indica se o veículo será um carro ou um caminhão Figura 213 Generalização Preco VEICULO d Numeropassageiros CARRO Numeroeixos CAMINHAO Velocidademaxima Codveiculo Placa Capacidadepeso Fonte Elmasri Navathe 2013 p 165 Qual é o melhor generalizar ou especializar Depende do modelo da solução O importante é gerar a superclasse com subclasses e analisar quantos e quais atributos e relacionamentos estão na superclasse ou na subclasse e a partir dessa etapa decidir se o modelo será especializado ou generalizado Se existirem várias superclasses e subclasses no mesmo modelo cada uma deve ser analisada individualmente Síntese Este capítulo trouxe o banco de dado do mundo conceitual para o modelo lógico e apresentou as etapas de modelagem de banco de dados Mos trouse o que é um modelo de entidade relacionamento suas estruturas seus relacionamentos suas cardinalidades o que são chaves e quais são seus tipos e como escolher a chave primária entre as chaves candidatas Foi apresentado ainda como identificar superclasse e subclasse para auxiliar na decisão de especializar ou generalizar BancosDadosindd 41 17102016 095955 42 Banco de Dados Este capítulo é muito complexo e com muitas informações e somente a prática aprimorará seu conhecimento Leia a bibliografia e crie vários mode los apresentados nos exercícios Da teoria para a prática Crie um modelo Entidade Relacionamento que apresente os pedidos de produtos de cliente Um cliente pode comprar vários produtos no mesmo pedido e os produtos podem ser vendidos para vários clientes Devese saber o nome o tipo e o valor do produto Do cliente precisase no mínimo do nome da cidade e do pedido além da quantidade comprada Precisase tam bém armazenar o valor unitário e o valor total 2 Entidades 2 Relacionamento 2 Chaves primárias 2 Tipos de dados BancosDadosindd 42 17102016 095955 O modelo entidade relacionamento MER e o modelo enti dade relacionamento estendido ERE são partes do modelo concei tual por isso é necessário modificar o modelo para definir suas estru turas de modo que fique mais próximo da implementação física Para isso temos a transformação do modelo lógico em modelo relacional logo após aplicar as regras de normalização Modelo Relacional e Normalização 3 BancosDadosindd 43 17102016 095955 44 Banco de Dados Objetivos de aprendizagem 2 compreender o modelo relacional 2 mapear o modelo entidade relacionamento para o modelo relacional 2 mostrar normalização 2 normalizar 1a 2a e 3a forma normal 31 Modelo relacional O modelo relacional MR define uma entidade como relação na qual cada conjunto de valores dos atributos pode ser considerado um registro pois seus valores estão interligados representando um elemento Na terminologia do modelo relacional temos as seguintes nomenclaturas 2 entidade tabela ou relação 2 cada linha de uma tabela linha tupla ou registro 2 cada coluna atributo ou coluna 2 cada tipo de dado de cada domínio Importante Guarde as nomenclaturas pois elas são usadas em diferentes literaturas concursos e no Exame Nacional de Desempenho de Estudantes Enade Veremos a aplicação desse modelo no mapeamento do modelo rela cional no qual os conceitos serão explicados passo a passo 32 Mapeamento de modelo entidade relacionamento para modelo relacional Existem regras para se realizar o correto mapeamento do MER e modelo ERE Elmasri e Navathe 2013 criaram um algoritmo com um conjuro de BancosDadosindd 44 17102016 095955 45 Modelo Relacional e Normalização passos para cada situação que precise ser mapeada para exemplificar será utilizado o modelo da Figura 31 Figura 31 Modelo entidade relacionamento Fonte Elmasri Navathe 2013 p 190 2 Mapeamento de tipos de entidade regular para cada entidade forte E no MER crie uma relação R tabela e inclua todos os atributos simples e todos os componentes simples de atributos compostos de E Entre as chaves candidatas escolha a chave pri mária que pode ser simples ou composta Exemplo utilizando a Figura 31 temos Funcionário Projeto e Departamento que são entidades fortes então devem ser criadas tabelas para cada uma e escolhida a chave primária como mostrado na Figura 32 BancosDadosindd 45 17102016 095955 46 Banco de Dados Figura 32 Passo 1 Mapeamento Fonte elaborado pelo Autor 2 Mapeamento de tipos de entidades fracas para cada entidade fraca F no MER com a entidade proprietária Entidade pai crie uma relação R e inclua todos os atributos simples e todos os com ponentes simples de atributos compostos de F Inclua como atri butos de chave estrangeira de R as chaves primárias das relações correspondentes às Entidades proprietárias A chave primária de F é a combinação das chaves estrangeiras e a chave parcial da enti dade F caso exista Exemplo temos a entidade fraca Dependente após aplicar a regra Figura 33 Figura 33 Passo 2 Fonte elaborado pelo autor BancosDadosindd 46 17102016 095955 47 Modelo Relacional e Normalização 2 Mapeamento de relacionamentos binários 11 para esse mapea mento existem três técnicas possíveis 2 Chave estrangeira escolha uma das relações S e inclua como chave estrangeira de S a chave primaria de T Inclua todos os atributos simples ou componentes simples dos atributos compostos 2 Relação mesclada mescle os dois tipos de entidades e o relacio namento em uma única relação R isso acontece quando ambas as participações são totais 2 Relação de referência cruzada crie uma terceira relação R com as chaves primárias de T e S Essa técnica é obrigatória nos relaciona mentos NM e é chamada de tabela associativa tabela de pesquisa ou relação de relacionamento A Figura 34 mostra esse passo criando a tabela FuncionarioDepartamento com o relacionamento 11 Figura 34 Passo 3 Fonte elaborado pelo autor BancosDadosindd 47 17102016 095955 48 Banco de Dados 2 Mapeamento de tipo de relacionamento binário 1N para cada relacionamento 1N do MER identifique a relação que fica do lado N do relacionamento e inclua como chave estrangeira em S a chave primária de T Inclua em S todos os atributos simples e todos os componentes simples de atributos compostos A Figura 35 apre senta o exemplo sendo aplicado em Funcionário e Departamento onde um departamento tem vários funcionários Figura 35 Passo 4 Fonte Elaborado pelo Autor 2016 2 Mapeamento de relacionamento binário MN nesse caso para cada tipo de relacionamento MN crie uma nova relação S e inclua como atributos as chaves primárias das relações das entidades parti cipantes Inclua em S todos os atributos simples e todos os compo nentes simples de atributos compostos caso existam Figura 36 Passo 5 Fonte elaborado pelo autor BancosDadosindd 48 17102016 095955 49 Modelo Relacional e Normalização 2 Mapeamento de atributos multivalorados para cada atributo multivalorado A crie uma Relação R com o atributo correspon dente e a chave primária da entidade proprietária como chave estrangeira de R Caso existam atributos compostos os componen tes simples serão incluídos Exemplo na entidade Departamento existia o atributo multivalorado localizações o passo foi aplicado gerando a relação Localização removendo assim os atributos monovalorados Figura 37 Figura 37 Passo 6 Fonte elaborado pelo autor 2 Mapeamentos de tipo de relacionamento Nário crie uma rela ção S para representar R e inclua como chave estrangeira em S as chaves primárias das entidades relacionadas Inclua em S todos os atributos simples e todos os componentes simples de atributos compostos No modelo da Figura 31 não temos como aplicar o passo mas imagine que existisse o seguinte relacionamento pro jeto funcionário departamentos no qual o relacionamento fosse ternário Nesse caso seria criada uma tabela associativa para rela cionar as três tabelas A Figura 38 apresenta de forma gráfica os passos apresentados 1 a 7 equivalem de a a g Assim é possível visualizar de forma rápida e resumida cada passo BancosDadosindd 49 17102016 095955 50 Banco de Dados Figura 38 Mapeamento para o modelo relacional Fonte Meira 1997 Os passos para o mapeamento do modelo são claros e fáceis de serem executados O problema surge quando temos modelos muitos grandes e com plexos nos quais é necessário observar detalhadamente cada entidade e seus relacionamentos no momento de mapear 33 Mapeamento de modelo entidade relacionamento estendido para modelo relacional Das formas de mapeamento de generalização e especialização as mais apli cadas são generalizar completamente ou especializar Existem outras formas na literatura mas na prática se tornam inviáveis por questões de performance Elmasri e Navathe 2013 apresentam as seguintes soluções BancosDadosindd 50 17102016 095955 51 Modelo Relacional e Normalização 2 múltiplas relações apenas relações de subclasses especialização nesse caso devese criar uma nova relação para cada subclasse Uti lizando como base a Figura 39 seriam criadas duas relações uma para carro e outra para caminhão 2 relação única com atributo de tipo crie uma única relação com os atributos da superclasse e de todas as subclasses crie um atri buto chamado de atributo de tipo ou atributos discriminador que indique a qual subclasse pertence o elemento Utilizando a Figura 39 como exemplo seria criada apenas uma única relação Veículo com todos os atributos de veículo carro e caminhão e mais um atributo tipo que indicaria se esse veículo é um carro ou um caminhão Figura 39 Especialização generalização Preco VEICULO Numeropassageiros CARRO Numeroeixos CAMINHAO Velocidademaxima Codveiculo Placa Capacidadepeso d Fonte Elmasri Navathe 2016 p 165 O item de especialização generalização pode ser considerado um oitavo passo no mapeamento do MER para o MR 34 Normalização Segundo Silberchatz Korth e Sudarshan 1999 existem determinadas condições que podem interferir na qualidade de um projeto de banco de dados Essas propriedades indesejáveis podem ser 2 informações repetidas 2 inconsistências de dados 2 incapacidade de representação de algumas informações BancosDadosindd 51 17102016 095955 52 Banco de Dados Repetições de dados ocasionam desperdício de espaço e dificuldades de atualização pois como os dados são redundantes todos devem ser atualiza dos e nem sempre isso ocorre causando inconsistências dos dados A normalização é o processo para resolver essas repetições e inconsis tências decompondo as relações por meio da separação de seus atributos em relações menos complexas DATE 2000 Existem cinco formas normais mas na prática só normalizamos até a terceira pois a normalização resolve muitos problemas de inconsistência dependência e redundância Em compensação quanto mais decomposições de relações são feitas mais junções entre as tabelas são necessárias para a recuperação dos dados dessa forma a performance fica reduzida Saiba mais Para maiores informações sobre anomalias 4a e 5a formas normais consulte a bibliografia do capítulo 35 1a forma normal 1FN Segundo Date 2000 uma variável de relação tabela está na 1FN se e somente se todo valor válido dessa variável de relação contiver exatamente um valor para cada atributo Isso quer dizer que os atributos devem ser atômicos não permitindo atributos multivalorados ou atributos compostos Exemplo apresentase a Relação empregado com os atributos CPF Nome Dependente e Telefone onde dependentes e telefone são multi valorados representados por chaves afinal o empregado pode ter vários dependentes tais como filhos e cônjuge e vários telefones como celular residencial e comercial Empregado CPF Nome Dependente Telefone Essa relação não está na primeira forma normal então devese decom por a relação empregado em relações menores ou seja criar novas relações para atender dependentes e telefones conforme a Figura 310 BancosDadosindd 52 17102016 095955 53 Modelo Relacional e Normalização Figura 310 1FN Fonte elaborado pelo autor Devese proceder dessa maneira em todo o modelo Depois devese verificar se além da primeira forma normal está na segunda forma normal 36 2a forma normal 2FN Uma variável de relação está na 2FN se e somente se está na 1FN e todo o atributo não chave é completamente dependente da chave primária Essa condição é chamada de dependência total DATE 2000 Já a dependência parcial acontece quando uma coluna depende apenas de parte de uma chave primária composta Nesse caso temse uma chave primária composta e os atributos devem ter vínculo em todos os atributos da chave primária Veja o exemplo da Figura 311 a chave primária é composta formada por CodProjeto e Empregado existe o atributo horas e nomeProjeto O atributo horas indica quais foram as horas que o empregado trabalhou no projeto portanto precisase saber qual é o projeto e qual é o empregado a dependência do atributo Horas é total BancosDadosindd 53 17102016 095955 54 Banco de Dados O atributo nomeProjeto indica qual é o nome do projeto independen temente de qual empregado trabalha no projeto o nome do projeto mudará somente de acordo com o CodProjeto portanto nomeProjeto só depende do atributo CodProjeto dessa forma a dependência é parcial e não atende à segunda forma normal Figura 311 2FN Fonte elaborado pelo autor Para corrigir essa condição devese separar a relação inicial em relações menores criando uma nova relação para cada chave parcial e incluindo os atributos que dependem dessa chave Figura 312 Figura 312 2FN Fonte elaborado pelo autor Observe a Figura 312 Para resolver o problema foi criada uma tabela projeto e adicionado o nome do projeto atributo projeto Agora o modelo está de acordo com a 2FN BancosDadosindd 54 17102016 095956 55 Modelo Relacional e Normalização 37 3a forma normal 3FN A 3a Forma Normal apresenta o conceito de dependência transitiva Uma variável da relação está na 3FN se e somente se estiver na 2FN e os atri butos não forem chaves DATE 2000 2 Mutuamente independentes Quando os atributos não partici pam da chave primária da variável de relação considerada Isto é se nenhum deles é funcionalmente dependente de qualquer combi nação dos outros Para que isso aconteça cada um desses atributos pode ser atualizado independentemente dos demais 2 Completamente dependentes da chave primária A Figura 313 mostra um exemplo de dependência transitiva Foi criada uma tabela chamada dependênciaTransitiva para a explicação na qual temos cla ramente as informações do empregado e do departamento e a seguinte condição 2 CPF NomeEmpregado Indica a dependência do nome do empregado com o CPF 2 CPF CPFGerenteDepto Indica a dependência do CPF do empregado com o gerente do departamento 2 CodDepto NomeDepto CPFGerenteDepto O nome do departamento e do gerente do departamento são dependentes do código do departamento Figura 313 Dependência transitiva Fonte elaborado pelo autor BancosDadosindd 55 17102016 095956 56 Banco de Dados Normalizando a Figura 313 para ficar na 3FN temos o resultado na Figura 314 Figura 314 3FN Fonte elaborado pelo autor A relação foi decomposta em duas Empregado e Departamento as dependências transitivas foram separadas e as relações atendem à 3FN Quando se normaliza não é necessário fazer passo a passo com a prá tica basta analisar cada tabela e verificar se atendem aos requisitos de cada uma das formas normais Saiba mais Existem inúmeros exemplos na bibliografia analise e tente normali zar vários deles A experiência deixará esses conceitos muito mais consolidados e fáceis de reconhecer 38 Dicionário de dados Para finalizar a documentação além dos modelos criados é preciso documentar cada tabela e cada atributo de tabela e definir o domínio tipo de dado e precisão Para isso é necessário criar um documento que contenha 2 todas as tabelas com a descrição da finalidade de cada uma BancosDadosindd 56 17102016 095956 57 Modelo Relacional e Normalização 2 todos os atributos com nome descrição tipo de dado precisão Muitos sistemas de gerenciamento de banco de dados SGBDs já ofe recem essa opção nos metadados no momento da criação das tabelas e dos atributos Podese inserir a descrição de cada um deles e posteriormente gerar relatórios para obter essas informações Saiba mais Precisão indica até que faixa de valor do tipo de dados será utilizada por exemplo no MS SQL server temos int smallint e bigint todos são inteiros com tamanhos diferentes para suportar faixas diferentes de valores Síntese Com o conhecimento adquirido neste capítulo você deve estar pronto para preparar todo o material necessário e implementar o banco de dados físico Depois de converter para o modelo relacional normalizar até a terceira forma normal e preparar a documentação Agora falta somente escolher o SGBD que será utilizado e fazer a última análise e os ajustes dos tipos de dados no dicionário de dados afinal cada SGBD oferece vários tipos diferen tes e então construir o banco de dados Da teoria para a prática Dado o modelo lógico a seguir crie o modelo relacional que atenda aos critérios 2 Converta para o modelo relacional 2 Normalize até 1FN 2 Normalize até 2FN 2 Normalize até 3FN BancosDadosindd 57 17102016 095956 58 Banco de Dados BancosDadosindd 58 17102016 095956 O Modelo Entidade Relacionamento MER está pronto o modelo físico também A análise do negócio foi feita e a documen tação está pronta os próximos passos são criar o banco de dados e as estruturas dentro do banco de dados Neste livro os exemplos apresentados serão demonstrados pelo SGBD Microsoft SQL server a linguagem será TransactSql que é a extensão da linguagem SQL para este SGBD SQL 4 BancosDadosindd 59 17102016 095956 60 Banco de Dados Importante Os SGBDs têm características detalhadas sobre os serviços e paco tes que estão incluídos em cada versão Observe cuidadosamente o que é realmente necessário para esta solução qual a versão do SGBD se ela é adequada ao sistema operacional utilizado nos ser vidores e nas máquinas do cliente Objetivos de aprendizagem 2 apresentar a estrutura física de um SGBD 2 criar banco dados 2 utilizar Data Definition Language DDL 41 Structured Query Language SQL Existem diversas versões de SQL Segundo Siberchatz 1999 o SQL foi criado na década de 1970 pela IBM implementada como parte do projeto do sistema R então a linguagem foi evoluindo e hoje inúmeros produtos dão suporte para a linguagem SQL Em 1986 o American National Standard Institute ANSI e a International Standard Organization ISO publicaram os padrões para SQL86 Uma extensão do padrão SQL chamado de SQL 89 foi publicada em 1989 logo depois veio a SQL92 Existem ainda as versões 2003 e 2011 Saiba mais Qualquer SGBD que utilize o padrão SQL ANSI pode ser facil mente portável entre diferentes arquiteturas de bancos de dados Os SGBDs criam extensões do padrão SQL no caso do Oracle PLSQL e da Microsoft SQL Server existe o Transact SQL Essas extensões oferecem muitos serviços e funções que podem ser utilizadas dentro do comando SQL BancosDadosindd 60 17102016 095956 61 SQL 42 SQL DDL DDL Data Definition Language é parte da linguagem SQL utilizada para definições de dados utilizando esses comandos podese criar dados e suas estruturas Um banco de dados é dividido em um ou vários arquivos físicos que são gerenciados pelo sistema operacional no caso do SQL Temse um ou vários arquivos para dados como a extensão MDF master data file para o primeiro arquivo de dados e a extensão NDF Secondary Data File para os demais arquivos de dados Existe também um ou vários arquivos de LOG Log Data File para o armazenamento das transações Estas extensões de arquivos são meramente informativas É possível colocar a extensão que se desejar mas como manter o padrão é extremamente importante para qualquer solução devese utilizar o padrão existente Cada SGBD tem sua estrutura própria para arquivos de dados log índices entre outros O DB2 banco relacional da IBM ou o Oracle tem formatos variações nomenclaturas e arquivos físicos bem diferentes entre si 421 Create Database O primeiro passo é criar um banco de dados lembrese do padrão de nomenclatura de banco de dados que pode ser formado por números letras e caracteres especiais Mas para evitar problemas de acessibilidade e portabi lidade devese utilizar letras números e sublinhado O nome do banco de dados deve ser o mais próximo a sua finalidade No quadro 41 temse o exemplo do comando básico de Create database O comando completo é bem mais complexo mas o objetivo deste capítulo é mostrar as principais propriedades e características de criação de banco de dado Observe que o nome do banco é inserido neste comando Este é o nome pelo qual o banco de dados será reconhecido dentro do SQL Server Na sequência aparece o Name onde é inserido um nome lógico do arquivo de dados e outro para o nome lógico do arquivo de log Estes nomes são referências internas no catálogo do sis tema para fazer referência entre o caminho e o nome físico que foi dado para ambos os arquivos BancosDadosindd 61 17102016 095956 62 Banco de Dados Quadro 41 Create database CREATE DATABASE NomeDobanco ON PRIMARY NAME NomeLogicodoArquivodeData FILENAME PathNomeFisicomdf SIZE 5120KB FILEGROWTH 1024KB LOG ON NAME NomeLogicodoArquivodeLog FILENAME PathNomeFisicologldf SIZE 1024KB FILEGROWTH 10 Fonte Elaborado pela autora A demonstração deste comando na prática é apresentada na figura 42 O nome do banco é DbFael seguido do nome lógico dbFael para o arquivo físico DbFaelmdf Este arquivo vai ter o tamanho inicial de 5120 KB e o crescimento automático de 1024KB O nome lógico dbFaellofldf faz refe rência ao DbFaellogldf O tamanho inicial deste arquivo é de 1024KB com crescimento automático de 10 O caminho físico no disco do computa dor para cada um desses arquivos é CProgram FilesMicrosoft SQL Server MSSQL12FAELMSSQLDATA BancosDadosindd 62 17102016 095956 63 SQL Quadro 42 Create Database Exemplo prático CREATE DATABASE DbFael ON PRIMARY NAME NDbFael FILENAME NCPro gram FilesMicrosoft SQL ServerMSSQL12 FAELMSSQLDATADbFaelmdf SIZE 5120KB FILEGROWTH 1024KB LOG ON NAME NDbFaellog FILENAME NC Program FilesMicrosoft SQL ServerMSSQL12 FAELMSSQLDATADbFaellogldf SIZE 1024KB FILEGROWTH 10 GO Fonte elaborado pela autora É possível ter inúmeros bancos de dados dentro de um SGBD depende da solução que foi escolhida para isto geralmente são criadas instâncias por aplicação desta forma a SGBD fica mais organizador Os arquivos físicos de cada um destes bancos podem ficar em vários drives e discos diferentes desde que estejam disponíveis para o gerenciamento do sistema operacional Em aplicações de grande porte devese utilizar storage no lugar de discos locais além da redundância recuperação de dados em caso de falha de disco eles são muito mais rápidos Figura 41 Arquivos físicos de banco de dados Fonte elaborado pela autora 2016 BancosDadosindd 63 17102016 095956 64 Banco de Dados Os arquivo físicos se encontram no caminho indicado no comando create database a DbFaelmdf para o arquivo de dados e DbFaellogldf para os arquivos de log figura 41 E na figura 42 temse o banco de dados DbFael aparecendo na lista de bancos de dados da ferramenta Figura 42 Representação no SSMS Fonte elaborado pela autora 2016 O banco de dados foi criado e o próximo passo é criar as estruturas dentro do banco de dados que podem ser tabelas visões procedimentos armazenados funções e outros Estas estruturas variam de acordo com o SGBS usado Para eliminar o banco de dados com suas estruturas utilizase o comando Drop Database Sintaxe Drop database nomeDoBanco Este comando apaga inclusive os arquivos físicos do banco Para criar cada uma das tabelas devese obedecer o modelo físico gerado com os mesmos nomes tipos de dados e precisão documentados 422 Tipos de dados Como representar a tabela apresentada no modelo de dados dentro do banco de dados Para isso utilizase o comando create table Neste comando é importante saber o tipo de dado para cada atributo sua precisão e quanto de armazenamento em disco vai ocupar cada um Dos principais tipos de dados temse BancosDadosindd 64 17102016 095956 65 SQL Tipos de dados numéricos quadro 43 podem armazenar inteiros ou números decimais Existem também os tipos numeric e decimal onde pode se indicar quantos números o tipo de dados vai ter e quantos destes números vão compor a parte inteira e a parte decimal Declaração decimal p s e numeric p s onde P é a quantidade total de caracteres e S indica a quanto do total vai ser a parte decimal Exemplo Numeric 52 são 3 intei ros e 2 casas decimais Quadro 43 Tipos de dados numéricos Tipo de dado Faixa Armazenamento bigint 263 9223372036854775808 to 2631 9223372036854775807 8 Bytes int 231 2147483648 to 2311 2147483647 4 Bytes smallint 215 32768 to 2151 32767 2 Bytes tinyint 0 to 255 1 Byte Float 179E308 to 223E308 0 and 223E308 to 179E308 Depends on the value of n Real 340E 38 to 118E 38 0 and 118E 38 to 340E 38 4 Bytes Money 9223372036854775808 to 9223372036854775807 8 bytes smallmoney 2147483648 to 2147483647 4 bytes Fonte elaborado pela autora 423 Tipos de dado data e hora Servem para armazenar data hora e data e hora Existem tipos times tamp datetime e smalldate time Os principais tipos para data e hora estão apresentados no quadro 44 Dependendo do tipo de dado a precisão vai até milissegundos Portanto devese cuidar com a precisão no momento da pesquisa pela data e hora completa BancosDadosindd 65 17102016 095956 66 Banco de Dados Quadro 44 Tipos de dados data e hora Tipo de dados Faixa Armazenamento Date 00010101 até 99991231 DC 3 bytes Datetime Janeiro 1 1753 até dezembro 31 9999 000000 até 235959997 8 bytes Time 0000000000000 até 2359599999999 5 bytes Fonte elaborado pela autora Estes tipos de dados têm tratamento especial por serem complexos o SGBD oferece funções prontas para trabalhar com data como 2 adicionar ou subtrair dias meses ou anos 2 subtrair duas datas 2 recuperar somente o dia mês ou ano 424 Tipos de dados caracteres strings São tipos que armazenam qualquer tipo de caractere inclusive acentos espaço em branco e caracteres especiais Os principais são 2 char n Caracteres não Unicode1 de comprimento fixo Onde n define o quantidade de caracteres e deve ser um valor de 1 a 8000 2 varchar n max Caracteres não Unicode de tamanho variá vel n define a quantidade de caracteres e pode ser um valor de 1 a 8000 max indica o tamanho máximo de armazenamento que é 2311 bytes 2 GB Em caso de acesso de outra plataforma ou posterior migração do banco de dados para outra arquitetura pode ser necessária a utilização do padrão Unicode nestes tipos de dados basta utilizar nchar e nvarchar na declaração da variável 1 Para maiores informações sobre unicode acess os sites httpstoolsietforghtmlrfc3629 ou httpunicodetablecomen BancosDadosindd 66 17102016 095956 67 SQL As recomendações da Microsoft para utilização de char e varchar são 2 char quando os tamanhos dados da coluna forem consistentes 2 use varchar quando os dados da coluna variarem consideravelmente 2 Use varcharmax quando os tamanhos das entradas de dados da coluna variarem consideravelmente e o tamanho puder exceder 8000 bytes Saiba mais Existem outros tipos de dados e estes variam tanto na precisão quanto na declaração nos diferentes SGBDs consulte sempre as informações de cada fabricante para ter certeza da utilização do tipo de dado correto isso também pode variar de acordo com o sistema operacional no qual o banco foi instalado existem diferen ças entre tamanho ocupado por tipo de dado numa arquitetura 32 ou 64 bits 425 Create table Para criar a tabela precisase criar os atributos com seus respectivos tipos de dados e também suas restrições As restrições ou constraints servem para proteger os dados e meta dados de valores estruturas ou condições inválidas as principais restrições são 2 chave primária primary key indica cada registro como único den tro de uma tabela o campo que forma a chave primária não pode ser nulo nem se repetir dentre os registros da tabela 2 Sintaxe Create Table nomeTabela Atributo Tipo de dado Constraint pknomeDaConstraint Primary key Atributo2 Tipo BancosDadosindd 67 17102016 095956 68 Banco de Dados Exemplo Create table Cliente CPF Numeric11 Constraint pkcliente primary key nome varchat70 Idade smallint 2 chave estrangeira foreign key responsável por manter a integridade referencial apresentada no capítulo de modelagem é importante para manter a relação entre as tabelas de forma correta efetiva o relacionamento dentro do banco de dados onde a chave primária da tabela pai vai para a tabela filho como chave estrangeira Create Table nomeTabela Atributo tipodedado constraint foreign key fkattibuto refe rences TabelaPaiChave primaria Exemplo Create table Dependentes Nome varchar70 Responsavel numeric11 Constraing FK AlunoDependente foreign key Responsavel references AlunoCPF 2 Nullnot null Indica se o campo poderá ter valor nulo ou não Create Table nomeTabela Atributo tipodedado not null BancosDadosindd 68 17102016 095956 69 SQL Exemplo Create table Cliente CPF Numeric11 Constraint pkcliente primary key nome varchat70 Not Null Idade smallint Null 2 check verifica se o valor atribuído ao campo é válido a partir de determinadas regras definidas pelo negócio Create Table nomeTabela Atributo tipodedado constraint ckattibuto Attibuto Regra deValidacao Exemplo Create table Cliente CPF Numeric11 Constraint pkcliente primary key nome varchat70 Idade smallint Sexo char 1 Constraint cksexo Check sexo in M F 2 default caso não seja inserido um valor para o campo no momento da inserção do registro o banco de dados insere um valor prédefi nido para o atributo como valor padrão Create Table nomeTabela Atributo tipodedado default valor BancosDadosindd 69 17102016 095956 70 Banco de Dados Exemplo Create table Cliente CPF Numeric11 Constraint pkcliente primary key nome varchat70 Idade smallint Estado char2 default PI 2 unique garante que o valor deste atributo dentre os vários regis tros não pode ser repetido Sintaxe Create Table nomeTabela Atributo tipodedado Constraint UKAttributo Unique Exemplo Create table Cliente CPF Numeric11 Constraint pkcliente primary key nome varchat70 RG varchar 15 Contraint UKRG Unique Idade smallint BancosDadosindd 70 17102016 095956 71 SQL Figura 43 Modelo para representar a criação das tabelas Fonte elaborado pela autora Como podem existir inúmeros bancos de dados em uma instância de banco de dados devese sempre indicar em qual banco está sendo manuseado no momento Para isso utilizase o comando Use Nomedobanco Quadro 45 Aluno Use DbFael Go Create table Aluno CPF Numeric 11 not null constraint pk cpf primary key Nome varchar100 not null Endereco varchar100 not null Complemento varchar100 DtNasc date not null Sexo char1 constraint cksexo check sexo in FM Telefone char 15 Fonte elaborado pela autora BancosDadosindd 71 17102016 095956 72 Banco de Dados O quadro 45 mostra a criação da tabela Aluno Inicialmente se indica qual banco de dados deve ser utilizado neste caso DbFael Dentro do comando create table indicase o nome da tabela e depois cada um dos atributos com suas respectivas restrições Para as constraints de primary key foreign key check e unique recomendase fortemente utilizar padrão de nomenclatura isso facilita na manutenção futura Figura 44 Após a execução do comando Fonte elaborado pela autora Ao executar o comando exibido no quadro 45 o banco de dados DbFael terá sua primeira tabela como demonstrado na figura 44 A interface gráfica do SQL Server permite que seja visualizado as chaves e restrições existentes em cada tabela Estas informações se encontram no catálogo do sistema de cada SGBD BancosDadosindd 72 17102016 095956 73 SQL Quadro 46 Create table dependente Create table Dependente Codigo int identity 11 CPF Numeric 11 not null constraint fk cpf foreign key references AlunoCPF DtNasc date not null Sexo char1 constraint cksexoDep check sexo in FM Fonte elaborado pela autora 2016 Ao executar o comando do quadro 46 criase a tabela dependente Observe que como é uma entidade fraca foi criada o atributo código do tipo autoin cremento chamado de identity com valor inicial igual a 1 e incrementando de 1 em 1 Nesta tabela o CPF é chave estrangeira que faz referência a tabela pai Aluno o nome do campo não precisa ser o mesmo mas o tipo de dados e precisão devem ser exatamente o mesmo entre chaves primárias e estrangeiras Figura 45 Tabela Dependente criada no banco de dados Fonte elaborado pela autora BancosDadosindd 73 17102016 095956 74 Banco de Dados No banco de dados DbFael existem duas tabelas figura 45 a tabela Aluno e a tabela Dependente Procedese da mesma forma para criar as demais tabelas do modelo utili zando o padrão de nomenclatura para as restrições As tabelas pai devem ser cria das antes das tabelas filhos para que o banco de dados consiga reconhecer a inte gridade referencial Outra possibilidade é criar todas as tabelas primeiro e usando o comando Alter table podese adicionar ou remover atributos e restrições Para eliminar uma tabela com todo o seu conteúdo utilizase o comando Drop Table Sintaxe Drop table nome da Tabela Após executar este comando com sucesso tanto os dados são removidos do banco quanto as informações da tabela no catálogo do sistema A tabela será removida se o usuário tiver privilégio suficiente para executar o comando e nenhuma regra ou integridade referencial seja afetada Saiba mais Os nomes dos atributos devem ser únicos por tabela e o nome das restrições devem ser únicos no banco de dados 43 Álgebra relacional A álgebra relacional é um conjunto de operações tendo uma ou duas tabelas relações e produzindo como resultado uma diferente relação Sil berschatz 1999 Estas operações são utilizadas para selecionar linhas e colu nas de tabelas para especificar uma consulta em um determinado banco de dados O resultado de cada operação é uma nova tabela que pode ser utilizada para novas operações As operações fundamentais também conhecidas como primárias atuam sobre uma única relação são elas 2 select 2 project 2 rename BancosDadosindd 74 17102016 095956 75 SQL Os SGBDs aplicam muitos comandos de álgebra relacional nas suas consultas mas os operadores de SQL DML oferecem muitos recursos que os existentes na álgebra relacional 431 Select A operação Select é utilizada para selecionar um conjunto de linhas tuplas de uma relação sendo que estas tuplas devem satisfazer uma condi ção de seleção A sintaxe uma operação select é σ condição de seleção nome da relação 2 A letra grega minúscula sigma é utilizada para representar a ope ração de seleção 2 Condição de seleção É uma expressão lógica booleana aplicada sobre os atributos da relação 2 Nome da relação É o nome da tabela sobre a qual será aplicada a operação select Exemplo este exemplo recupera todas as colunas da relação tabela Aluno desde que o Sexo seja feminino e armazena o resultado na Consulta Consulta Sexo F Aluno Os operadores que podem ser aplicados na operação Select são 2 relacionais 2 lógicos and or not 432 Project A operação Project seleciona um conjunto especifico de colunas de uma relação A sintaxe da operação project é Π lista de atributos nome da relação 2 A letra grega Π representa a operação project 2 Lista de atributos indica quais os atributos que o usuário deseja selecionar BancosDadosindd 75 17102016 095956 76 Banco de Dados 2 Nome da relação É a tabela sobre a qual a operação project será aplicada Exemplo este exemplo mostra as colunas nome e data de nascimento da relação dependentes Todos os registros são mostrados Consulta Π Nome DtNasc DEPENDENTES 433 Operação sequencializada As operações project e select podem ser utilizadas de forma combinada permitindo que apenas determinadas colunas de determinadas tuplas possam ser selecionadas A sintaxe de uma operação sequencializada é Π lista de atributos σcondição de seleção nome da relação Exemplo neste caso será apresentado o nome e a data de nascimento da tabela Aluno cujo sexo seja masculino Consulta Π nome dtnasc σ sexo MAluno A álgebra relacional é muito importante para a criação de consultas em SQL Os bancos de dados relacionais utilizam muitos conceitos da matemá tica em sua criação e execução Síntese Este capítulo apresentou como funciona a estrutura de armazenamento de um bando de dados com seus arquivos físicos e suas referências lógicas que são armazenados nos metadados do banco Como criar o banco de dados a partir de um modelo físico de dados criando tabelas e restrições que existem para proteger os dados e a integridade Da teoria para a prática A partir do modelo apresentado BancosDadosindd 76 17102016 095956 77 SQL 2 crie o Banco de Dados 2 crie a tabela Produtos com suas restrições 2 crie a tabela Cliente com suas restrições 2 crie a tabela Pedidos com suas restrições BancosDadosindd 77 17102016 095956 BancosDadosindd 78 17102016 095956 DML linguagem de manipulação de dados é uma lingua gem utilizada para recuperar e trabalhar com dados em SQL Use estas instruções para inserir atualizar alterar consultar ou apagar dados de um banco de dados Neste capítulo apresentaremos os recursos de SQL DML para especificar consultas simples SQL DML 5 BancosDadosindd 79 17102016 095956 80 Banco de Dados Importante A tarefas de DML podem ser definidas pelos acrônimos CRUD Create read update delete ou CERA criar excluir recuperar e apagar Objetivos de aprendizagem 2 apresentar DML 2 mostrar a utilização dos comandos básicos de DML 2 mostrar exemplos práticos de DML 51 Select Um banco de dados é formado por uma coleção de tabelas tuplas ou relação cada uma designada por um único nome Cada tabela possui várias colunas atributos Para acessar o conteúdo destes atributos registro ou linha utilizamos a linguagem de manipulação de dados especificamente o comando Select Esta instrução é utilizada para recuperar os dados do banco ela não é a mesma operação Select da álgebra relacional existem muitas opções para o comando Select que não são apresentados na álgebra relacional A estrutura básica de uma expressão Select em SQL consiste em três cláusulas Siberschatz 1999 2 select é operação de projeção Π na álgebra relacional É usada para mostrar os atributos desejados no resultado de uma consulta 2 from é operação do produto cartesiano X Associa as tabelas que serão pesquisadas durante a evolução de uma expressão 2 where é a operação de seleção σ Consiste em um predicado envolvendo atributos da tabela que aparece no from Uma consulta simples em SQL figura 51 onde C são as colunas refe renciadas T são as tabelas e P é o predicado BancosDadosindd 80 17102016 095956 81 SQL DML Figura 51 Select Select C1 C2 Cn From T1 T2 Tn Where P Fonte elaborado pelo autor Em álgebra relacional Π C1 C2 Cn σP T1 x T2 x x Tn Ao ser executado o SQL forma um produto cartesiano combinação de todas as linhas de todas as tabelas envolvidas entre as tabelas existentes no From executa uma seleção usando o predicado do Where e projeta os resultados sobre os atributos da cláusula Select Obtendo como resultado da consulta outra tabela chamada de result set ou conjunto resultado Exemplo Encontre os nomes de todos os alunos da tabela Aluno figura 52 Figura 52 Select Fonte elaborado pelo autor 2016 BancosDadosindd 81 17102016 095957 82 Banco de Dados Neste exemplo figura 52 a coluna é denominada nome e a tabela aluno Saiba mais Se na query consulta não existir cláusula Where o predicado P é verdadeiro ou seja não haverá nenhuma restrição então todos os registros serão apresentados O padrão do SQL é apresentar duplicidade dos registros nas tabelas pois a eliminação da duplicidade demanda muito tempo Isso não significa que bra de chave primária pois no exemplo da figura 52 apresentase somente o campo nome e podem existir homônimos Para forçar a eliminação de duplicidade utilizase a cláusula distinct logo após o Select figura 53 neste caso se existir repetição do registro utilizase todos os campos apresentados no Select para esta avaliação esta será suprimida e somente será apresen tado um único registro para cada repetição independente da quantidade de repetições existentes Select nome from Aluno e Select ALL nome from aluno trazem o mesmo resultado pois o default é ALL e esta cláusula pode ser omitida sem nenhum prejuízo Figura 53 Distinct Select distinct nome From Aluno Fonte elaborado pelo autor 2016 Quando é necessário trazer todos os atributos devese utilizar o aste risco figura 54 para indicar todos os atributos Esta opção só deve ser utilizada se todos os atributos forem realmente utilizados pois trazer dados que não são necessários tornam a consulta mais pesada e geralmente temos vários usuários acessando o banco ao mesmo tempo quanto mais dados não BancosDadosindd 82 17102016 095957 83 SQL DML necessários utilizarem os recursos do SGBD ES e rede mas tempo e recur sos são desperdiçados quando poderiam estar sendo utilizados para trazer os resultados importantes para o usuário Figura 54 Asterisco Select From Aluno Ou Select Aluno From Aluno Fonte elaborado pelo autor É permitido aplicar expressões aritméticas e fórmulas entre números e colunas no Select Podese utiliza as expressões aritméticas e Por exemplo figura 55 caso se deseje um relatório para calcular um aumento de 5 no salário de cada aluno o Select e o resultado para a query acima é demonstrada na figura 56 observe que no quadro azul aparece a fórmula para o cálculo que segue as mesmas regras matemáticas de ordem para aplicação dos operadores Outro detalhe importante é que quando a coluna está na cláusula Select o nome do atributo é apresentado no result set mas quando se aplica uma fórmula ou operador matemático sobre a coluna o nome do atributo não aparece mais no cabeçalho do resultado Select não altera nenhum valor do atributo as fórmulas aplicadas afetam somente o resultado apresentado e não o conteúdo da tabela original BancosDadosindd 83 17102016 095957 84 Banco de Dados Figura 55 Operadores matemáticos Fonte elaborado pelo autor 2016 Existe uma forma de melhorar a apresentação e alterar o conteúdo mos trado no cabeçalho são chamados Aliases e sua demonstração será apre sentada mais adiante 52 Where Da mesma maneira que muitas vezes recuperar todos os atributos não é necessário também podese aplicar uma condição predicado sobre as colu nas mas neste caso para restringir a quantidade de registros no resultado de acordo com o conteúdo da coluna O where é utilizado para aplicar a condição lógica e retornar os registros caso estejam de acordo com a condição Para selecionar o nome de todos os alunos do sexo feminino a query é demonstrada na figura 56 Figura 56 Where Fonte elaborado pelo autor BancosDadosindd 84 17102016 095957 85 SQL DML Veja que a cláusula Where não interfere na cláusula Select neste exem plo foi utilizado a projeção para recuperar o nome e o salário em conjunto com o predicado Sexo F no Where O SQL usa os operadores lógicos AND OR e NOT figura 57 e os operadores de comparação e figura 58 na cláusula Where Figura 57 Exemplo de OR Figura 58 Exemplo de And Fonte elaborad pelo autor Não trazer nenhum resultado figura 59 também conhecido como resultar um conjunto vazio não significa erro ou falha apenas mostra que não existe nenhum registro que atenda a condição estipulada Um result set pode ser vazio unitário ou com vários elementos Figura 59 Resultado é um conjunto vazio Fonte elaborado pelo autor BancosDadosindd 85 17102016 095957 86 Banco de Dados O operador de comparação Between figura 510 pode ser usado para simplificar a cláusula Where quando especificar um valor que seja maior que ou igual a valor e menor que ou igual a outro valor Figura 510 Between Figura 511 Not Between Fonte elaboradas pelo autor Da mesma maneira o not between figura 511 especifica os valores que estão fora da faixa apresentada neste caso são apresentados os valores que sejam menores e maiores à faixa Importante Quando o resultado não for aquele esperado verifique a cláusula Where os exemplos citados são bem simples mas na prática um Where pode ser muito complexo 53 Aliases O result set tem um cabeçalho onde geralmente fica o nome de cada coluna ou sem valor no caso de aplicar fórmulas ou operadores matemáticos Em determinadas situações existe a necessidade de nomear ou renomear o nome de uma tabela ou coluna em uma query 2 seja para eliminar ambiguidades quando existem referências a atri butos com o mesmo nome em tabelas diferentes ou no caso de acesso recursivo a tabela BancosDadosindd 86 17102016 095957 87 SQL DML 2 para deixar o cabeçalho padronizado 2 para se utilizar esta consulta como tabela de entrada para outra consulta Podese renomear tanto atributos quanto tabelas Para renomear tabe las ou atributos utilizamos a cláusula AS que pode aparecer tanto no Select quanto no From Pode ser representado de duas formas com o AS entre o nome antigo e o novo ou com espaço em banco substituindo o AS 2 nomeantigo as nomenovo figura 12 2 nomeantigo nomenovo figura 13 Exemplo Figura 512 Aliases Fonte elaborado pelo autor O alias está sendo utilizado para mudar o campo que estaria sem cabe çalho na aplicação de fórmula para o valor de Salário observe que está acen tuado O resultado é mesmo utilizando AS como na figura 12 ou utilizando espaço como na figura 13 A utilização de um ou outro depende de dois fatores se existir um padrão utilizeo senão a escolha fica a critério do DBA que utiliza o que for mais natural mas após a escolha de usar ou não o AS todas as queries devem seguir o mesmo padrão BancosDadosindd 87 17102016 095957 88 Banco de Dados Figura 513 Alias com espaço Fonte elaborado pelo autor O alias pode ser usado para nomear tabelas Em SQL avançado será muito útil principalmente para evitar ambiguidades A figura 514 mostra que além de nomear a tabela o alias pode ser utilizado para especificar qual o campo se refere a cada tabela Figura 514 Alias em tabela Fonte elaboradas pelo autor BancosDadosindd 88 17102016 095957 89 SQL DML Também podese utilizar alias no atributo e na tabela como na figura 515 e quando for necessário um alias formado por mais de um nome como Data Nascimento ele deve ficar entre aspas Figura 515 Alias em atributo e tabela Fonte elaborado pelo autor O alias não altera qualquer valor na tabela original ele só muda o result set Nas recomendações de nome de atributos e tabelas não se recomenda caracteres especiais caso seja necessária acentuação ou semelhante devese utilizar os aliases 54 Ordenação No mundo dos negócios muitas vezes se faz necessária a utilizada de dados ordenados para que sejam exibidos em relatórios O SQL oferece a cláusula order by que permite que os registros de um resultado de uma con sulta apareçam em uma determinada ordem A sintaxe da ordenação é Order by C1 C2 CN BancosDadosindd 89 17102016 095957 90 Banco de Dados Para cada atributo do order by podese definir se a ordenação vai ser ascendente asc figura 15 que é default e pode ser omitida na query ou se vai ser descendente desc que deve ser declarada explicitamente figura 17 Figura 516 Order by Figura 517 Order by desc Fonte elaborado pelo autor Fonte elaborado pelo autor A ordenação varia de acordo com o tipo de dado e segue a ordem do pri meiro campo se existir duplicidade e ordena o registro pelo segundo campo e assim sucessivamente Saiba mais Utilize ordenação com cuidado muitos recursos são utilizados principal mente em banco de dados com tabelas muitos grandes A ordenação não altera a posição dos registros no banco de dados ele ordena somente o result set 55 Valores nulos É importante relembrar que valores nulos indicam que a coluna não teve nenhum valor atribuído serve para indicar a ausência de informação Podese utilizar a palavra is null figura 518 para testar a existência de valores nulos enquanto se utiliza is not null figura 19 para verificar a ausên cia de valores nulos BancosDadosindd 90 17102016 095957 91 SQL DML Figura 518 Is Null Figura 519 Is not null Fonte elaboradas pelo autor Se qualquer um dos valores de entrada for nulo o resultado de uma expressão aritmética e é nula representado pela figura 520 Figura 520 Cálculo com valores nulos Fonte Elaborado pelo autor BancosDadosindd 91 17102016 095957 92 Banco de Dados Saiba mais Nulo quer dizer ausência de valor ou seja espaço em branco não é nulo é caractere zero não é nulo é um valor numérico válido 56 Insert Uma das funções importantes do SQL é o Insert este comando valida a restrições de valores de cada atributo no momento em que os dados estão sendo inseridos dentro do banco de dados As restrições constrainst de Check Primary Key Foreign Key Unique Not Null Default são validada neste momento A sintaxe do Insert é Insert into tabela Values V1 V2 Vn Neste caso os valores V devem ter o mesmo tipo de dado especifi cado no Create Table e também os valores a serem inseridos devem estar na mesma ordem em que as colunas foram criadas no momento em que a tabela foi criada Ou Insert into tabela C1 C2 Cn Values V1 V2 Vn Neste caso os valores V não precisam estar na mesma ordem de criação das colunas no Create Table e sim na ordem que foram definidas as colunas C na cláusula insert Esta forma é obrigatória quando 2 existir campo auto incremento na tabela BancosDadosindd 92 17102016 095957 93 SQL DML 2 quando não é necessário inserir todos os atributos desde que sejam Not Null 2 quando se deseja utilizar o valor default que foi atribuído na criação do atributo na forma de restriçãoobserve a integridade referencial ao inserir dados em uma tabela filho tenha certeza que existe chave estrangeira correspondente a chave primária da tabela pai senão o registro não será inserido De acordo com o tipo de dados no Insert as seguintes regras devem ser observadas na atribuição de valores 2 ao inserir um valor nulo utilize NULL maiúsculo ou minúsculo sem aspas 2 ao inserir datas caracteres e strings utilize sempre aspas simples 2 para valores numéricos utilize o ponto para casa decimal 2 em datas verifique o formato No Brasil é DDMMAAAA mas o formato americano é MMDDAAAA Verifique sempre o for mato para evitar erros como 03072016 que pode ser armazenado como 07032016 Neste caso a data que se deseja é 3 de julho e pode ser armazenado como 7 de março Utilizando a Tabela Aluno para exemplificar os erros que são gerados pelas constraints são erros totalmente válidos para garantir as regras de um SGBD dados inválidos não devem ser inseridos no banco de dados Na figura 521 temos um exemplo de violação de PK primary key Este erro demostra que já existe um CPF do aluno com valor 11 Figura 521 Falha de PK Fonte elaborado pelo autor BancosDadosindd 93 17102016 095957 94 Banco de Dados Na figura 522 existe a tentativa de inserção de valor nulo no atributo endereço que não pode ser nulo tendo a restrição Not Null Figura 522 Erro de Not Null Fonte elaborado pelo autor Na figura 523 existe a tentativa de se inserir o valor A para sexo que só permite M ou F Figura 523 Erro de Check Fonte elaborado pelo autor E finalmente depois de todas as correções feitas figura 24 o comando Insert foi executado com sucesso BancosDadosindd 94 17102016 095957 95 SQL DML Figura 524 Insert completo Fonte elaborado pelo autor Na figura 525 temos o exemplo de Insert utilizando a segunda forma onde as colunas são apresentadas indicando a posição de cada valor para cada atributo Facilita bastante em tabelas muito grandes onde não é necessário inserir todos os campos que podem ser preenchidos depois Figura 525 Segunda opção de insert com declaração de colunas Fonte elaborado pelo autor Sempre são inseridos registros inteiros de cada vez mesmo que nem todos os atributos recebam valor 57 Delete Assim como precisamos inserir dados no banco existem momentos que apagar os dados é muito necessário Este comando remove tuplas registros BancosDadosindd 95 17102016 095957 96 Banco de Dados de uma tabela Ele pode remover vários registros por vez ou somente um ou grupos de registros que atendem a uma cláusula Where Outro fator importante é a integridade referencial caso a intenção seja deletar dados que tem correspondentes em outras tabelas isso não será permi tido para que não existam registros órfãos Importante Sem cláusula where o delete vai apagar todos os registros da tabela as vezes é necessário mas tome cuidado se é realmente isso que se quer fazer o mesmo vale para o Where que pode não ser tão restritiva quanto se deseja Sintaxe do Delete Delete Tabela Onde todos os registros serão apagados Ou Delete Tabela Where Condição Onde somente os registros que atenderem o predicado Where serão apa gados se nenhum registro atender a condição nada será apagado A figura 526 apresenta um erro de integridade referencial na tentativa de deletar todos os alunos inclusive aqueles que tem dependente Neste caso nada foi apagado Figura 526 Delete Integridade Referencial Fonte elaborado pelo autor BancosDadosindd 96 17102016 095957 97 SQL DML O delete apresentado na figura 27 mostra que não ocorreu nenhum erro de restrição portanto o registro cujo o CPF PK que era 33 foi apagado Figura 527 Delete Fonte elaborado pelo autor Existe outra opção para apagar todos os registros de uma tabela conhe cido como comando truncate Ele elimina todos os registros de uma tabela mas precisa de privilégios especiais para ser executado além de permissão de leitura e escrita O truncate é mais rápido que o delete pois não registra as exclusões das linhas individuais na Log do banco de dados A sintaxe é Truncate Table nome da tabela A figura 528 apresenta um exemplo da eliminação de todas a linhas de uma tabela utilizando o comando Trucate Figura 528 Truncate Table Fonte elaborado pelo autor BancosDadosindd 97 17102016 095957 98 Banco de Dados Após a efetivação da deleção dos registros estes podem ser recuperados somente com a restauração de um backup 58 Update Com as atualizações da vida real como mudança de endereço telefone aumento de preços de salários promoções entre outros passa a ser necessá ria a atualização do banco de dados para que ele corresponda com a realidade Para isso o SQL tem o comando Update este comando é utilizado para modificar valores de um ou mais atributos de um ou mais registros selecionados A sintaxe para o Update é Update Tabela Set C1 V1 C2 V2 CN VN Where Condição O Where pode ser omitido caso se deseje alterar todas as linhas da tabela Os valores a serem atribuídos para as colunas podem ser valores fixos fórmulas e até mesmo cálculos de outras colunas A figura 529 apresenta a execução de um Update com Where que atua liza dois específicos registros Figura 529 Update Fonte elaborado pelo autor BancosDadosindd 98 17102016 095957 99 SQL DML A figura 530 apresenta um cálculo sobre o atributo salário o qual recebe um aumento de R 50000 e o atributo complemento também é atualizado para um registro específico Figura 530 Update com cálculo de atributos Fonte elaborado pelo autor Podese alterar qualquer campo desde que não quebre nenhuma res trição Alguns SGBDs não permitem a atualização da chave primária só os demais campos Se no Update houver alguma tentativa de quebra de restri ções uma mensagem de erros será apresentada e o Update não será executado 59 Operações com strings Strings são conjuntos de caracteres que podem conter letras números e caracteres especiais Como é um tipo de dado complexo podese utilizar e tratar a pesquisa de diferentes maneiras Umas das opções mais usadas são as verificações de coincidências usando o operador like O like pode ser combinado com alguns caracteres especiais para melho rar as opções de pesquisa eles pode ser 2 porcentagem compara qualquer substring 2 sublinhado compara qualquer caractere 2 barra utilizando como caractere de escape para comparar caracteres especiais BancosDadosindd 99 17102016 095957 100 Banco de Dados Observe que 2 La strings que comecem com La 2 aro strings que possua uma substring aro 2 strings com somente 3 caracteres 2 strings com pelo menos 3 caracteres Dependendo da configuração de linguagem podemos ter algumas dife renças na pesquisa No SQL server existe um item chamado Collation que define alguns parâmetros para consultas com strings Seguem alguns deles 2 AS Accent Sensitive palavras acentuadas são diferentes de palavras não acentuadas ou seja leva em consideração a acentuação a é diferente de A 2 AI Accent Insensitive desconsidera a acentuação por exemplo a á à 2 CS Case Sensitive Maiúsculas não são iguais a minúsculas 2 CI Case Insensitive Maiúsculas e minúsculas são iguais Comparando o resultado da querie nas figuras 531 e 532 podese observas o funcionamento do parâmetro AS Accent Sensitive na figura 31 ele trouxe um registro mas na figura 532 o result set foi vazio pois ão é diferente de ao Figura 531 Case Insensitive Figura 532 Case Sensitive Fonte elaboradas pelo autor 2016 BancosDadosindd 100 17102016 095958 101 SQL DML Existem inúmeras formas de tratar strings cada linguagem oferece forma diferentes de pesquisa e resultado A vantagem que isso oferece uma flexibilidade imensa permitindo a utilização do SGBD sem se restringir a linguagem utilizada Saiba mais Acesse a internet e procure por SQL Server TransactSQL DML Reference Microsoft você vai ver o manual completo com todas as opções que cada comando apresentado aqui oferecem Síntese Neste capítulo foi apresentada uma introdução ao SQL DML come çando com o Select e sua combinação com álgebra relacional cada uma das cláusulas do Select foi apresentado o From e o Where e quais operadores lógicos e de comparação podem ser usados Depois foi apresentada a ordenação de registros para o result set e a forma de tratamento de valores nulos O comando Insert veio a seguir com exemplos práticos e diferentes for mar de adicionar dados da tabela seguido pelo Delete para apagar registros e o Update atualizar dados no banco Finalizamos o capítulo com formas de comparação de string e detalhes Saiba mais Acesse a internet e procure mais no site da Microsoft sobre Collation Isso vai melhorar bastante o entendimento sobre as diversas linguagens como o chinês árabe russo entre outras são tratados pelo SGBD BancosDadosindd 101 17102016 095958 102 Banco de Dados Atividades Utilizando o modelo a seguir faça o que se pede 2 Selecione o nome e o preço de todos os produtos 2 Selecione o nome de todos os clientes que moram em Brasília 2 Selecione o nome e o preço dos produtos que custam menos de R 100 ordenado pelo preço começando pelo menor valor 2 Selecione todos os clientes que tenha a substring ANA no nome BancosDadosindd 102 17102016 095958 Os comandos essenciais do SQL já foram apresentados mas ainda existem outros que auxiliam na recuperação de dados Esses comandos facilitam muito o resultado para relatórios e pes quisas mais complexas como agregação e recuperação de dados de várias tabelas SQL Avançado 6 BancosDadosindd 103 17102016 095958 104 Banco de Dados Objetivos de aprendizagem 2 mostrar funções agregadas 2 apresentar junções 2 subconsultas aninhadas 2 uniões 2 visões 61 Funções agregadas Funções agregadas são utilizadas quando se precisa aplicar cálculos sobre grupos de registros Estas funções recebem os valores como entrada e retor nam um valor simples As funções agregadas do SQL são 2 contador count count os dados podem ser não numéricos 2 média average avg somente com números 2 mínimo minimum min os dados podem ser não numéricos 2 máximo maximum max os dados podem ser não numéricos 2 total total sum somente números Sintaxe Select Countatributo from Tabela Select Minatributo from Tabela Select Avgatributo from Tabela Select Maxatributo from Tabela Select Sumatributo from Tabela O contador é usado quando se precisa calcular quantos registros aten dem determinada condição representado na figura 61 Podese utilizar tanto BancosDadosindd 104 17102016 095958 105 SQL Avançado o nome do atributo quando o neste caso temse a quantidade total de todos os registros existentes na tabela Aluno Figura 61 Count Fonte elaborada pelo autor Mínimo e máximo são usados quando se precisa saber quais os valores mínimos figura 62 e máximo figura 63 respectivamente de um atributo Figura 62 Min Fonte elaborada pelo autor Podese utilizar aliases e outros comando no SQL Veja que as figuras 62 e 63 mostram o Menor Salário e o Maior Salário como nome da coluna no result set BancosDadosindd 105 17102016 095958 106 Banco de Dados Figura 63 Max Fonte elaborada pelo autor A soma totaliza os valores de uma coluna que atenda a condição No exemplo da figura 64 é totalizado o salário dos alunos mas somente aqueles que são do sexo feminino Figura 64 Sum Fonte elaborada pelo autor A média calcula a média aritmética dos valores de um determinado atri buto Na figura 65 temos a média salarial dos alunos do sexo masculino BancosDadosindd 106 17102016 095958 107 SQL Avançado Figura 65 Avg Fonte elaborada pelo autor Podese utilizar as funções agregadas em uma query Tudo que é apre sentado na linguagem SQL pode ser combinado para se obter consultas melhores Na figura 66 temse a utilização da média máximo e contador na mesma consulta Figura 66 Funções agregadas Fonte elaborada pelo autor BancosDadosindd 107 17102016 095958 108 Banco de Dados Importante As repetições são importantes no cálculo da média mas se pre cisa eliminar as repetições podese utilizar o distinct na expressão agregada SQL não permite o uso do distinct com count somente distinct count atributo 62 Group By e Having A cláusula Group By é usada quando existe a necessidade de se agrupar registros ou aplicar uma função agregada a um grupo de conjunto de registros Sintaxe dados entre colchetes são opcionais Select Atributos ou funções agregadas From Tabela Where condição Group By Atributos a serem agrupados Having condição Os atributos em uma cláusula Group Gy ésão utilizados para for mar grupos ou seja os registros com os mesmos valores de todos os atributos da cláusula Group By são colocados em um grupo O Group By executa uma ordenação dos dados para formar os grupos O exemplo apresentado na figura 67 atende a seguinte solicitação selecione todos os alunos por sexo Isto significa agrupar os dados por sexo e contar quantos alunos existem para cada sexo São 3 mulheres e 4 homens BancosDadosindd 108 17102016 095958 109 SQL Avançado Figura 67 Group By Fonte elaborada pelo autor A cláusula having é utilizada quando se necessita aplicar condições sobre os dados agrupados O having é como uma cláusula where a diferença é que having se aplica somente a grupos ou seja as linhas grupos enquanto a cláu sula where se aplica a linhas individuais Se uma cláusula where e having apare cem na mesma consulta a condição que aparece no where é aplicada primeiro A figura 68 representa a utilização da cláusula having Neste caso a soli citação é traga a média salarial por sexo mas somente daqueles que tiverem a média salarial maior que R 200000 Assim o agrupamento é feito por sexo depois a média salarial é calculada com o having verificando quais são as condições do grupo maiores que R 200000 e finalmente esses dados são apresentados A figura 69 comprova que a média salarial masculina é abaixo de R 200000 exatamente por isso o grupo não aparece no resultado final Figura 68 Having Fonte elaborada pelo autor BancosDadosindd 109 17102016 095958 110 Banco de Dados Figura 69 Média por sexo Fonte elaborada pelo autor Group By Having e funções de agregação são muito importantes na geração de relatórios uma vez que já devolvem o result set com o cálculo e o agrupamento prontos sem a necessidade de trafegar todos os dados pela rede para calcular dentro do programa 63 Junções Junções são utilizadas quando precisamos acessar dados de outras tabelas que tem relacionamento entre si A junções podem ser 2 inner join interna 2 left outer join externa à esquerda 2 right outer join externa à direita 2 outer Full join externa à direita e à esquerda combinadas 2 cross join cruzada 631 Junção interna Inner Join A Junção Interna Inner Join conecta duas ou mais tabelas e retorna ape nas as linhas que satisfazem a condição de junção Esta condição de junção BancosDadosindd 110 17102016 095958 111 SQL Avançado é feita pelos mesmos atributos que compõe a integridade referencial ou seja chave primária comparada com chave estrangeira Sintaxe Select atributos From tabela1 Inner Join tabela2 On Condição de Junção Inner Join tabelaN On Condição de Junção Neste caso a condição é primary key da tabela1 foreign key da tabela2 No exemplo da figura 610 temse a junção da tabela Aluno e da tabela Dependente A integridade referencial é formada pelo atributo CPF chave primária em Aluno e chave estrangeira em Dependente Como os atributos CPF e Nome tem o mesmo nome é necessária a criação de alias para indicar de qual tabela vem cada atributo Figura 610 Inner Join Fonte elaborada pelo autor BancosDadosindd 111 17102016 095958 112 Banco de Dados Caso exista a necessidade de fazer junção com mais tabelas é só adicio nar mais uma cláusula inner join com a condição de junção 632 Junção externa à esquerda Left Outer Join Uma junção externa Left Outer Join mostra todas as linhas da tabela 1 e as combinações existentes na tabela 2 Sintaxe Select atributos From tabela1 Left Outer Join tabela2 On Condição de Junção A utilização da palavra Outer é facultativa e pode ser omitida A condição de junção é a mesma do Inner Join Na figura 611 temos o exemplo do Left Join e o resultado são todos os registros da tabela Aluno e para os alunos que têm depen dentes aparece em seguida o nome do dependente corres pondente Para os alunos que não têm dependentes o valor do atributo é NULL Figura 611 Left Join Fonte elaborada pelo autor BancosDadosindd 112 17102016 095958 113 SQL Avançado 633 Junção externa à direita Right Outer Join Uma junção externa Right Outer Join mostra todas as linhas da tabela 2 e as combinações existentes na tabela 1 Sintaxe Select atributos From tabela1 Right Outer Join tabela2 On Condição de Junção No exemplo da figura 612 temse como resultado todos os registros da tabela dependente que no caso são somente 3 e todas as combinações destes 3 registros com a tabela aluno Figura 612 Right Join Fonte elaborada pelo autor BancosDadosindd 113 17102016 095958 114 Banco de Dados Um fator importante é que a utilização do left e right podem mudar os resultados dependendo da ordem das tabelas na junção Veja que a Figura 613 foi a primeira apresentada na tabela dependente e o Right Join foi colo cada na tabela Aluno Figura 613 Right Join Fonte elaborada pelo autor Se você observar o resultado é exatamente o mesmo da figura 611 onde se usou o Left Join Portanto tenha muito cuidado ao usar o Left e o Right Join Tenha certeza que o resultado apresentado é exatamente aquele que você espera atingir 634 Junção cruzada e completa Cross Join e Full Join A junção cruzada Cross Join de tabelas também denominada junção irrestrita faz um produto cartesiano tabela1 X tabela2 ou seja mostra um resultado formado pela combinação de todas a linhas da primeira tabela por todas as linhas da segunda Não existe uma condição de junção BancosDadosindd 114 17102016 095958 115 SQL Avançado O Full Join faz uma combinação trazendo todas as linhas da primeira tabela com seus respectivos registros relacionado na tabela 2 e depois traz todos os registros da tabela 2 e seus registros relacionados na tabela 1 Na figura 614 temos o exemplo de Cross Join e na figura 615 o exem plo de Full Join A junção cruzada trouxe todas as combinações possíveis entre linhas das tabelas enquanto a Junção completa apresentou o resultado de um Left Join e Right Join nas mesmas tabelas Figura 614 Cross Join Fonte elaborada pelo autor BancosDadosindd 115 17102016 095958 116 Banco de Dados Figura 615 Full Join Fonte elaborada pelo autor Não é recomendável a utilização de nenhuma das junções apresentadas cruzada e completa Estas junções têm um grande custo e a utilização de recursos para um resultado que não é aplicado com frequência na prática 64 Subconsultas aninhadas Subconsultas aninhadas são utilizadas quando existe a necessidade de colocar uma expressão Select From Where inserida dentro de outra consulta Esta subconsulta gera um conjunto de valores e serão validadas de acordo com a condição da cláusula where BancosDadosindd 116 17102016 095958 117 SQL Avançado Sintaxe Select atributos From Tabela Where Atributo IN Select atributo from Tabela2 Where con dição Ou Select atributos From Tabela1 Where Atributo NOT IN Select atributo from Tabela2 Where con dição O conectivo in testa se os valores resultantes da consulta aninhada aten dem a condição do Where para tabela 1 enquanto o conectivo not in testa a ausência dos valores de um conjunto No exemplo da figura 16 temos na cláusula Where e o CPF in Select CPF From Dependente Neste caso o SGBD vai na tabela dependente e recu pera todos os CPFs gerando assim um result set R1 com uma única coluna Depois ele compara o CPF do aluno com cada CPF do result set trazendo os registros da tabela Aluno que atendem a condição Esta comparação é feita uma a uma como se fosse um Where CPF V1 or CPF V2 or CPF VN sendo V1 a Vn todos os valores trazidos no result set R1 Nesta query figura 616 ele está trazendo o nome do todos os alu nos que tem dependentes Neste momento ele não precisa saber quais são os dependentes BancosDadosindd 117 17102016 095958 118 Banco de Dados Figura 616 Consulta aninhada In Fonte elaborada pelo autor A figura 617 faz a mesma execução da figura 616 com a diferença que traz somente os Alunos que não têm dependentes Ou seja enquanto no IN a comparação é feita como uma igualdade no NOT IN a comparação é feita de maneira que não seja igual a cada elemento do result set Figura 617 Consulta aninhada Not In Fonte elaborada pelo autor BancosDadosindd 118 17102016 095958 119 SQL Avançado Importante Tome cuidado ao usar as subconsultas aninhadas em tabelas muitos gran des pois a comparação feita uma a uma vai consumir muito tempo e muitos recursos 65 Uniões As uniões union combinam os resultados de duas ou mais queries em um único result set Todas as linhas de todas as consultas dentro da união vão sendo adicionas As uniões exigem que as seguintes regras sejam obedecidas 2 os tipos de dados não precisam ser iguais mas devem ser compatíveis para a conversão implícita 2 a quantidade e a ordem das colu nas devem ser as mesmas em todas as consultas O exemplo da figura 618 apresenta a utiliza ção de union Observe que o select é feito em duas tabelas Aluno e Fun cionário Os atributos não têm o mesmo nome mas são de tipos compa tíveis então o comando funciona corretamente Figura 618 Union Fonte elaborada pelo autor BancosDadosindd 119 17102016 095958 120 Banco de Dados A figura 619 apresenta uma union das mesmas tabelas mas além do Nome também é solicitado o sexo A tabela Aluno tem este atributo mas a tabela funcionário não tem Neste caso houve a necessidade de se colocar NULL para indicar que nas linhas que não tivesse o valor para sexo será preenchido com ele Figura 619 Union Fonte elaborada pelo autor 66 Visões Views Um SGBD oferece várias estruturas ou objetos não confundir com orientação a objeto Primeiro foi apresentado o banco de dados Dentro do banco já foram criadas tabelas relacionamentos e restrições As views são mais uma das estruturas do banco de dados que auxiliam ao usuário BancosDadosindd 120 17102016 095958 121 SQL Avançado View visão é uma tabela virtual formada por linhas e colunas de dados vindos de outras tabelas ou views criadas por uma query A importância de uma view é que ao criála podese filtrar os nomes de colunas conteúdo de uma tabela ou mais tabelas a serem exibidas agrupando e protegendo determinadas colunas e simplificando o código para o programador As views não ocupam espaço no banco de dados Suas linhas e colunas são geradas dinamicamente no momento em que é feita uma referência a ela Sintaxe Create view NomedaVisao AS Query A figura 620 apresenta a criação de uma view para a tabela aluno apre sentando somente os atributos Nome Sexo e Salário Veja que foi utilizado um alias no nome e no salário para ocultar os verdadeiros nomes de colunas Figura 620 Criação de view Fonte elaborada pelo autor A figura 621 nos mostra que podemos criar views com union A flexi bilidade de utilização das visões é bem grande praticamente podese utilizar qualquer query para criar uma view BancosDadosindd 121 17102016 095958 122 Banco de Dados Figura 621 Criação de view Fonte elaborada pelo autor Para utilizar uma view você aplica um select como se a view fosse uma tabela como as demais Podese aplicar também Order By Group By Where etc figuras 622 e 623 Figura 622 Utilizando view Fonte elaborada pelo autor 2016 BancosDadosindd 122 17102016 095958 123 SQL Avançado Figura 623 Usando View com Where Fonte elaborado pelo autor 2016 Em uma view podem ser feitas as operações select insert update e delete desde que não interfira em nenhuma restrição Portanto seja cuidadoso na criação de views somente para leitura Para alterar o conteúdo de uma visão utilize a seguinte sintaxe Alter view NomedaVisao AS Query Para visualizar as visões existentes em um banco de dados basta selecio nar o nome do banco na interface gráfica do MS SQL SERVER e clicar em exibições do sistema Figura 624 BancosDadosindd 123 17102016 095959 124 Banco de Dados Figura 624 Exibição de view Fonte elaborada pelo autor Para eliminar uma visão utilize a seguinte sintaxe Drop view NomedaVisao Após executar o seguinte comando Drop View vwVisaoNomeSexo a view será eliminada do SGBD Síntese Este capítulo apresentou várias funções oferecidas pela SQL para que o SGBD seja utilizado da melhor maneira Foram vistas funções agregadas que já fazem vários cálculos sobre os atributos e que podem ser utilizadas funções de agrupamento e aplicar condições sobre os grupos utilizando a cláusula having As junções mostraram que se pode recuperar dados entre várias tabelas seguindo os relacionamentos entre elas Já a consultas aninhadas podem uti lizar um result set como entrada para uma cláusula where Atividades Dado o modelo a seguir faça as seguintes tarefas BancosDadosindd 124 17102016 095959 125 SQL Avançado 2 Crie um banco de dados dentro dele crie as tabelas e insira 3 regis tros em cada 2 Crie uma consulta que selecione o nome do funcionário e nome do cargo 2 Crie uma visão para o item B 2 Utilize a view ordenando por nome cargo descendente BancosDadosindd 125 17102016 095959 BancosDadosindd 126 17102016 095959 Os Sistema de gerenciamento de banco de dados SGBDs além de fornecerem funções básicas de Structured Query Language SQL Linguagem de Consulta Estruturada em português ofe recem o controle de transações concorrências e outros recursos que permitem a programação avançada dentro do banco de dados Os servidores de banco de dados são equipamentos muito potentes com excelente configuração de hardware e sistema operacional o que permite que os programas oferecidos dentro do SGBD sejam executados com muita rapidez e eficiência evitando que os dados trafeguem na rede sem necessidade Transações e Técnicas Avançadas 7 BancosDadosindd 127 17102016 095959 128 Banco de Dados Objetivos de aprendizagem 2 explicar o conceito de transação 2 apresentar a extensão da linguagem SQL Transact SQL 2 compreender Stored Procedures 71 Transação Existem determinadas ações dentro de um banco de dados que atuali zam vários registros Às vezes durante essa atualização ocorrem falhas que podem ser falhas de sistemas como falta de energia ou falhas de hardware como um disco defeituoso ou uma fonte com problemas O SGBD deve garantir que caso essas falhas ocorram o conteúdo do banco de dados continue sendo confiável Para resolver esse problema foi criado o conceito de transação Segundo Elmasri e Navathe 2013 uma transação é uma unidade atô mica de trabalho que deve ser completamente concluída ou completamente desfeita As transações possuem várias propriedades conhecidas como ACID sigla formada por 2 atomicidade uma transação deve ser totalmente realizada ou deverá ser desfeita 2 consistência uma transação deve ser executada do começo ao fim sem a interferência de outras transações garantindo a consistência dos dados 2 isolamento mesmo existindo várias transações sendo executadas ao mesmo tempo a transação deve ser executada isoladamente sem interferir em outras transações 2 durabilidade as mudanças aplicadas pela transação no banco de dados devem ser armazenadas no banco e não podem ser perdida por causa de falhas BancosDadosindd 128 17102016 095959 129 Transações e Técnicas Avançadas Para garantir a recuperação dos dados e manter a integridade do banco o sistema precisa registrar quando cada transação começa termina confirma ou cancela Para atender às condições da transação foram criadas as seguintes operações ELMASRI NAVATHE 2013 2 begin transaction marcando o início da transação 2 readwrite leitura ou gravação nos registros do banco que podem ser insert update delete e select 2 end transaction indica que a operação de leitura e escrita terminou 2 commit transaction indica que as operações foram bemsucedidas e podem ser efetivadas no banco 2 rollback abort indica que as operações não obtiveram sucesso e não podem ser efetivadas no banco Log file é outra estrutura utilizada para auxiliar no gerenciamento da transação é um arquivo físico no qual são inseridas as transações que estão sendo efetuadas O SGBD tem um buffer de log quando esse buffer fica cheio os dados são armazenados no arquivo de log cada transação é inserida nesse arquivo e quando a transação é finalizada com sucesso o Commit é executado e os dados armazenados no log são efetivados no arquivo de dados Caso o Rollback seja executado os dados são eliminados do log file sem serem efeti vados no banco A execução das operações de transação ocorrem conforme a Figura 71 primeiro indicase que a partir de determinado ponto começará a transação a partir daí todos os comandos que fazem parte dessa transação são armaze nados no log até que seja 2 abortada ou ocorra uma falha executa o rolback 2 ocorra a confirmação da transação efetuada com sucesso executa o Commit Dessa forma o SGBD garante a consistência do banco de dados BancosDadosindd 129 17102016 095959 Banco de Dados Figura 71 Transagao Read Write Begin End Parcialment Commit transaction transaction confirmada 7 Abort Abort Ci Fonte Elmasri Navathe 2013 p 506 Para exemplificar a utilizacdo da transacao utilizaremos a tabela Aluno com o contetido mostrado na Figura 72 Figura 72 Contetido da tabela Vela tad me Sl Fenkacee fy Mesnagene oF home Pie Complements Ones Sen Teele Cocke cee Sais 1 11 senda Stee Flas dee Gudea 37 SaieeCerte MULL RT MLL FEE z 8 Mare da Sho Aus do Chamty 28 Me 1d te F MALL Uri iy deme 1 Pace cam Marcie 117 Fe dd i oF UAL fer os WALL 4 44 ado Perey Bus Pia Bertcan 35 WALL io uM MAL Pot Angry a Ta 5 SS Mace brah Mivarmeg Bay MW che Meortenben 179 MALL BL MULL Brie hat oe 7d E Be Moraes Section End oceege Bes IT 1H OF ULL Beis Hesoni AG 200006 T OOTY Diego Queen Pup clue flores MULL noe om AALL Mace AL TLS Fonte elaborado pelo autor 2016 Agora vejamos um exemplo pratico de transagao no MS SQL Server Sintaxe observe que vocé pode utilizar a palavra transaction ou tran e o resultado sera o mesmo e o Commit pode até ser omitido sem problemas Begin transaction Commit Tran Ou Rollback Tran 130 BancosDadosindd 130 oe 17102016 095959 131 Transações e Técnicas Avançadas Na Figura 73 temos o início da transação no código SQL pelo comando Begin Transaction depois o comando Delete que apaga todos os dados da tabela Aluno dos alunos que não têm dependentes Quatro linhas foram apagadas mas a seguir temos o Rollback desfazendo todas as alterações feitas pelo Delete Figura 73 Executando transações Fonte elaborado pelo autor Ao fazer o select novamente na tabela Aluno todos os registros conti nuam lá comprovando que quando o Rollback foi executado desfez todos os deletes Figura 74 Rollback executado Fonte elaborado pelo autor BancosDadosindd 131 17102016 095959 132 Banco de Dados Na Figura 75 a transação é inicializada com o Begin Tran depois é feito um Update que aumenta o salário de todos os Alunos em 10 Em seguida é realizado um Commit que vai efetivar as atualizações no banco de dados Podese colocar inúmeros comandos dentro de uma transação que podem ser de select insert update e delete sendo que no momento que for feito o Com mit ou o Rollback serão efetivados ou desfeitos todos os comandos pendentes no log de transação independentemente de seu tipo Após as operações serem efetivadas commit não há mais como desfazer a não ser restaurando backup Figura 75 Execução com Commit Fonte elaborado pelo autor Após a execução do Commit a Figura 76 mostra um select compro vando que o salário de todos os alunos sofreu aumento de 10 Figura 76 Após o Commit Fonte elaborado pelo autor BancosDadosindd 132 17102016 095959 133 Transações e Técnicas Avançadas Um fator importante quando as transações são armazenadas no arquivo de log é o momento em que ocorre uma falha o serviço de SQL cai ou até mesmo o servidor é reiniciado Quando a instância do banco de dados sobe o SGBD verifica todas as transações pendentes no log e desfaz todas elas 72 Transact SQL TSQL Muitos SGBDs oferecem extensão do SQL padrão o Oracle tem o PL SQL o SQL Server tem o Transact SQL e assim para os demais SGBDs Esse complemento da linguagem SQL é muito útil pois oferece novas funcionali dades a serem utilizadas dentro do banco Importante Aprenda mais sobre Transact SQL executando o tutorial da Microsoft sobre T SQL httpsmsdnmicrosoftcomptbr libraryms365303vsql120aspx Vejamos algumas funções da Transact SQL Quadro 71 Transact SQL Função Sintaxe Declaração de variáveis DECLARE V1 V2 Vn Tipo de dados Indicadores de blocos BEGIN END Atribuição de valores SELECT V1 valor1 V2 valor2 Ou SET V1 valor1 While WHILE condição BancosDadosindd 133 17102016 095959 134 Banco de Dados Função Sintaxe Case CASE expressão WHEN Condição THEN resultado ELSE resultado do Else END Convert CONVERT tipo de dado des tino tamanho expressão Cast CAST expressão AS tipo de dado tamanho Data do sistema GETDATE Retorna parte da data DATEPART Parte que se deseja data Parte que se deseja é DD para dia MM para mês YYYY para ano Adicionasubtrai valores a data DATEADD Parte que se deseja número data DATEDIFF Parte que se deseja data1 data2 Imprimir Print Fonte elaborado pelo autor No exemplo da Figura 77 no início temse a declaração das variáveis basta separar por vírgula e é possível declarar quantas variáveis forem neces sárias Variáveis locais que são vistas apenas na sessão são indicadas por já nas variáveis globais e de sistemas são utilizadas Logo depois vem a atribuição utilizando o comando set que só aceita uma atribuição por vez O comando Print imprime o conteúdo da variável nome Print somente mostra strings e qualquer outro tipo de dado deve ser convertido para usar o comando Depois temse o comando While que segue o mesmo padrão de lin guagens de programação se for somente uma linha de execução não é pre ciso colocar os blocos de início e fim begin e end mas se for mais de um comando dentro do while é obrigatório o uso de definição de blocos BancosDadosindd 134 17102016 095959 135 Transações e Técnicas Avançadas Figura 77 Exemplos de TSQL Fonte elaborado pelo autor Dentro do while temos a impressão incremento da variável num a cada volta do loop Observe que foi necessária a conversão para char de tama nho 5 e poderia ter sido usado o comando Cast no lugar do convert BancosDadosindd 135 17102016 095959 136 Banco de Dados A Figura 78 mostra a declaração de variáveis do tipo datetime e inteiro Para se trabalhar com datas é preciso funções especiais afinal data é do tipo composto formado por dia mês e ano e datetime é formado por dia mês ano hora minuto segundo e milissegundo Outra informação importante em relação a tipos de data são os formatos no Brasil temos DDMMAAAA mas em outras regiões os formatos são diferentes como em países de idioma inglês nos quais o formato é MMDDYYYY Figura 78 Exemplos TSQL Fonte elaborado pelo autor Temse a atribuição de GETDATE que traz a data e a hora do servi dor e a dtfim atribuída manualmente Depois vem a utilização da Função DateDiff trazendo a diferença das datas em anos meses e dias BancosDadosindd 136 17102016 095959 137 Transações e Técnicas Avançadas Na Figura 79 temse a demonstração da utilização do CASE nesse caso dentro do Select O case executa determinadas funções de acordo com o valor da variável de entrada O Select traz o nome e o sexo mas ao invés de mostrar F e M foi criado um case para sexo para mudar o conteúdo a ser apresentado de acordo com o valor da coluna Figura 79 Case Fonte elaborado pelo autor Existem muitas outras funções e comandos oferecidos pelo TSQL quanto mais comandos você aprender melhor irá usálos 73 Stored Procedures SP Uma Stored Procedure também conhecida como um procedimento armazenado funciona de forma semelhante aos procedimentos de linguagem de programação São códigos previamente escritos e armazenados no servidor BancosDadosindd 137 17102016 095959 138 Banco de Dados e quando necessário solicitase sua execução No SQL Server é um grupo de uma ou mais instruções TSQL Os procedimentos podem 2 aceitar parâmetros de entrada e retornar vários valores de saída para o programa de chamada 2 ter instruções de programação que executam comandos no banco de dados Podese também ter chamada a outros procedimentos 2 devolver um valor de retorno que mostra o status para quem cha mou o procedimento indicando sucesso ou erro As vantagens do uso de procedimentos armazenados segundo as reco mendações da Microsoft são cada stored procedure segue a extensão de lin guagem SQL específica do SGBDs portanto escolheuse o padrão de TSQL para o estudo neste capítulo 2 tráfego de rede de servidorcliente reduzido a execução de uma em um único lote de código pode reduzir significativamente o trá fego da rede entre cliente e servidor porque a única chamada para executar o procedimento é enviada pela rede e somente o resultado é devolvido pela rede 2 segurança mais forte o procedimento controla quais processos e comados são executados e protege o banco pois nem o usuário nem o programador tem acesso direto ao código Ajuda prote gendo contra os ataques de injeção sql e os procedimentos podem ser criptografados 2 reutilização de código em sps o código é um só acessado e executado por qualquer usuário ou programa 2 manutenção facilitada quando existe a necessidade de alte rar o código de uma sp não é necessária nenhuma alteração na aplicação a menos que os parâmetros mudem os privilégios e acessos continuam os mesmos e outra vantagem é que quando o procedimento é atualizado todos as chamadas a essa sp acessarão a nova versão automaticamente 2 melhor desempenho como o procedimento é compilado na primeira vez em que é executado e cria um plano de execução em BancosDadosindd 138 17102016 095959 139 Transações e Técnicas Avançadas cada execução caso não haja nenhuma mudança significativa o mesmo plano é utilizado cada vez que a SP é executada econo mizando tempo Vejamos a seguir exemplos de criação de Stored Procedures 731 Criando SPs A sintaxe para criar uma Stored Procedure é CREATE PROC EDURE procedurename parametro tipo de dados VARYING default OUTPUT n WITH RECOMPILE ENCRYPTION RECOMPILE ENCRYPTION FOR REPLICATION AS Comandossql n Todas as SPs devem ter um nome único em cada banco de dados Colo case o nome da SP o mais próximo de sua utilização os parâmetros são facultativos e podem ser de INPUT quando vêm com entrada para a SP ou OUTPUT quando são retornados pela SP RECOMPILE e ENCRYPTION são as opções para recompilar e criptografar as SPs e FOR REPLICATION indica que a SP é usada para replicação Logo depois do AS vêm os coman dos SQL Todos os que foram vistos até agora podem ser utilizados dentro de uma SP e muitos outros O exemplo da Figura 710 mostra a criação de uma Stored Procedure nesse caso chamada pLeAalunos que faz o select de todas as colunas da tabela aluno e ordena por nome BancosDadosindd 139 17102016 095959 140 Banco de Dados Figura 710 Criando SPs Fonte elaborado pelo autor A Stored Procedure pLeAluno no momento da criação foi armazenada dentro do banco de dados que é possível visualizar na Figura 711 Dento do item programação todas as Stored Procedures criadas são armazenadas dentro do banco de dados e ficam até que sejam eliminadas Dessa forma qualquer usuário ou aplicativo que tenha privilégio pode executálas Figura 711 Mostrando SPs no Catálogo Fonte elaborado pelo autor BancosDadosindd 140 17102016 095959 141 Transações e Técnicas Avançadas Existem duas formas de se executar uma Stored Procedure como mostra a Figura 712 utilizando o comando Exec seguido pelo nome da Stored Pro cedure ou utilizando Execute e o nome da SP Em alguns casos é permitido somente chamar o nome da SP principalmente no ambiente gráfico do SQL Server e utilizando SPs sistemas Figura 1712 Executando SP Fonte elaborado pelo autor É muito fácil a criação e a execução de Stored Procedures e sua utili zação é fortemente recomendada principalmente em aplicações de duas e três camadas 732 Criando SPs com parâmetros de entrada Vimos a criação de uma Stored Procedure simples sem parâmetros de entrada agora conheceremos como funciona a passagem de parâme tros nas SPs Para criar uma SP com passagem de parâmetros logo depois da declara ção do nome da Procedure são declarados os parâmetros seguindo o mesmo padrão da declaração de variáveis o nome da variável e depois o tipo de dado Se necessário podese declarar um valor default caso não seja enviado nenhum valor para o parâmetro na chamada da SP No exemplo da Figura 713 temse o parâmetro nome com o valor default ou seja qualquer string Esse exemplo recupera o nome e o sexo do Aluno cujos nomes sejam equivalentes ao parâmetro ordenado por nome BancosDadosindd 141 17102016 095959 142 Banco de Dados Figura 713 SP com parâmetro de Input Fonte elaborado pelo autor Existem diversas formas de se chamar uma SP com passagem de parâ metros e vemos alguns deles na Figura 714 Como existe valor default no primeiro item não é passado nada o parâmetro assumirá o e trará todos os registros No segundo trará todos os alunos que começam com a letra J No terceiro todos os Alunos que comecem com a letra A mas mostrando que existe a declaração explícita do parâmetro No quarto exemplo trará os Alunos cujo nome comece com as letras C ou K tenha substring ars no meio seguido de O ou E e termine com a letra n E no último exemplo recuperará o Aluno que se chame somente João Figura 714 Execução de SP com parâmetro de Input Fonte elaborado pelo autor BancosDadosindd 142 17102016 095959 143 Transações e Técnicas Avançadas Os resultados da execução da SP de acordo com o parâmetro são repre sentados no Quadro 72 No primeiro caso apresenta todos os alunos pois como não foi passado parâmetro assumiu o No segundo caso todos que começaram com a letra J e nos últimos três não foi encontrado resultado que atenda às condições enviadas no parâmetro Quadro 72 Execução de SP com parâmetro de Input Num Forma de Chamar SP Resultado 1 EXECUTE pLe AlunosNome 2 EXECUTE pLeA lunosNome J 3 EXECUTE pLe AlunosNome Nome A 4 EXECUTE pLe AlunosNome CKarsOEn 5 EXECUTE pLe AlunosNome João Fonte elaborado pelo autor BancosDadosindd 143 17102016 095959 144 Banco de Dados A passagem de parâmetro flexibiliza bastante o uso das Stored Procedu res permitindo que sejam atendidas outras condições Podese passar quantos parâmetros forem necessários existe uma limitação mas ela é bem extensa em torno de 21 mil 733 Criando SPs com parâmetros de saída Outra facilidade fornecida são os parâmetros de OUTPUT que permi tem a saída de resultados da SP São permitidos vários parâmetros de saída em uma SP A Figura 715 mostra o exemplo da criação de uma SP com parâmetro de OUTPUT A SP pSomaSalario recebe sexo como parâmetro de input e retorna salário como OUTPUT Essa Procedure executa a soma dos salários da tabela Aluno de acordo com o valor do sexo que foi enviado como parâ metro de entrada Figura 715 Criando SP com parâmetro de Output Fonte elaborado pelo autor Para executar uma SP com parâmetro de OUTPUT é necessário declarar a variável que receberá o parâmetro de OUTPUT devese decla rar quantas variáveis forem necessárias para o número de parâmetros de OUTPUT existentes No caso da Figura 716 foi declarada uma variável total do tipo smallmoney BancosDadosindd 144 17102016 095959 145 Transações e Técnicas Avançadas Observe que na Figura 716 não é passado o parâmetro sexo a SP assumirá o valor default e trará a soma do salário de todos os alunos Na segunda execução é passado o parâmetro F feminino então a SP trará a soma dos salários dos alunos do sexo feminino E na terceira chamada é importante salientar que quando se declara o nome do parâmetro na cha mada não é necessário manter a ordem de chamada da SP O parâmetro de OUPUT salario foi colocado antes de Sexo funcionando corretamente será analisado o nome declarado na chamada e não a posição Figura 716 Executando SP com parâmetro de Output Fonte elaborado pelo autor O resultado da execução de cada chamada da Figura 716 se encontra no Quadro 73 Quadro 73 Resultado SP com parâmetro de Output Num Chamada Resultado 1 EXECUTE pSomaSalario Salario Total OUTPUT 2 EXECUTE pSomaSalario F Salario Total OUTPUT BancosDadosindd 145 17102016 100000 146 Banco de Dados Num Chamada Resultado 3 EXECUTE pSomaSala rio Salario Total OUTPUT Sexo M Fonte elaborado pelo autor Com parâmetros de OUPUT a flexibilidade da utilização das SPs fica maior ainda Quanto mais opções o SGBD oferece melhor é o trabalho de programadores e usuários que utilizam esses recursos 734 Tipos de procedimentos armazenados Existem três tipos de procedimentos armazenados no MS SQL Server 2 usuário são procedimentos criados pelo usuário e podem ser cria dos nos bancos de dados existentes Para criar qualquer objeto no banco de dados de sistema catálogo o usuário deve ter permissão 2 temporário procedimentos temporários são procedimentos definidos pelo usuário Geralmente as sps são armazenadas de forma permanente exceto os procedimentos temporários que são armazenados no banco de dados especial tempdb Assim como as variáveis existem dois tipos de procedimentos temporários local e global Os procedimentos temporários locais são representados por uma única como primeiro caractere no nome visíveis somente na sessão atual do usuário e excluídas quando a conexão é fechada Os procedimentos temporários globais são representados por como os dois primeiros caracteres de seus nomes que ficam visíveis para qualquer usuário depois de criados e são excluídos no fim da última sessão do procedimento 2 sistema os procedimentos do sistema são fornecidos com o MS SQL Server Como os procedimentos do sistema começam com o prefixo sp recomendase que utilize um padrão diferente ao criar as SPs de usuário BancosDadosindd 146 17102016 100000 147 Transações e Técnicas Avançadas Saiba mais Para obter uma lista completa dos procedimentos do sistema procure na internet por SQL Server Procedimentos armazenados do sistema 735 Alterar eliminar e verificar uma SP As Stored Procedures ficam armazenadas de forma permanente no banco enquanto o banco existir ou até que se elimine explicitamente utili zando o comando Drop Procedure Sintaxe Drop Procedure nome da procedure Caso se deseje alterar o conteúdo da SP basta utiliza o comando alter definindo os novos comandos da SP Sintaxe Alter PROC EDURE procedurename parametro tipo de dados VARYING default OUTPUT n WITH RECOMPILE ENCRYPTION RECOMPILE ENCRYPTION FOR REPLICATION AS Comandossql n BancosDadosindd 147 17102016 100000 148 Banco de Dados E quando se necessita verifi car o conteúdo da SP basta utiliza uma procedure de sistema cha mada sphelptext colocando em seguida o nome da SP que se deseja visualizar o conteúdo figura 717 Síntese Este capítulo é muito impor tante para a utilização dos recursos avançados de banco de dados Por isso foi apresentado o conceito de transação que garante as regras ACID mantendo o banco consis tente quando uma ou várias ope rações são efetuadas no banco de dados e quando várias transações são geradas simultaneamente Também foi apresentada a função do arquivo de log de transação que auxilia a gerenciar as transações Foram exploradas funções de extensão da linguagem SQL Transact SQL permitindo que sejam criados programas dentro do banco de dados e também foi visto que esses programas podem ser armazenados permanente mente no banco de dados com as Stored Procedures Da teoria para a prática Dado o modelo a seguir crie uma SP que 2 receba o CPF do aluno como parâmetro de entrada 2 delete o aluno que não tenha dependente 2 delete somente se o salário do aluno for maior ou igual a R 1000000 2 utilize o conceito de transação Figura 717 Verificando o conteúdo de uma SP Fonte elaborado pelo autor BancosDadosindd 148 17102016 100000 149 Transações e Técnicas Avançadas BancosDadosindd 149 17102016 100000 BancosDadosindd 150 17102016 100000 Neste capítulo vamos ver outros recursos oferecidos pelo MS SQL Server A grande maioria desses recursos existe em outros SGBDs A sintaxe varia mas são muito importantes para utilização correta e otimizada de banco de dados Recursos Avançados 8 BancosDadosindd 151 17102016 100000 152 Banco de Dados Objetivos de aprendizagem 2 apresentar as variáveis de ambiente 2 cursores 2 tabelas temporárias 2 triggers 81 Variáveis de Ambiente Cada SGBD oferece um conjunto de variáveis do ambiente para que se possa utilizar e verificar situações do sistema No caso do MS SQL Server basta utilizar o comando SET ou SELECT antes da variável para obter o resultado Relembrando que as variáveis locais são precedidas por e as vari áveis globais são precedidas por Eis algumas das principais variáveis de ambiente MSDN 2016 2 DATEFIRST define o primeiro dia da semana para os EUA Seu valor é 7 considerado o primeiro dia 2 SERVERNAME indica qual o nome do servidorinstância que está rodando o SQL 2 LANGUAGE indica qual a linguagem está sendo executada atualmente no SQL 2 LOCKTIMEOUT mostra configuração de tempo limite do bloqueio atual em milissegundos para a sessão corrente 2 SPID mostra o ID de sessão do processo de usuário atual 2 VERSION é uma função muito importante pois retorna muitas variáveis de ambientes de configuração do sistema apresen tado na figura 81 2 versão do SQL Server 2 arquitetura do processador 2 data de compilação do SQL Server 2 instrução de direitos autorais BancosDadosindd 152 17102016 100000 153 Recursos Avançados 2 edição do SQL Server 2 versão do sistema operacional Figura 81 Version Fonte elaborado pelo autor Importante Existe um comando que é utilizado para matar suspender a exe cução processos que estão processando ou travados e por algum motivo não há mais necessidade de continuar sua execução este é o comando KILL Esta instrução poderá demorar algum tempo para ser concluída principalmente quando envolver um rollback de uma transação longa O comando KILL pode ser usado para terminar uma conexão nor mal o que finaliza internamente as transações associadas à ID pode ser identificado usando spid ou a SP de sistema spwho2 da sessão especificada 82 Cursores Existe uma estrutura especial que permite retornar um result set e tra balhar com esse conjunto linha a linha para fazer várias ações dentro de um TSQL ou de uma SP BancosDadosindd 153 17102016 100000 154 Banco de Dados Sintaxe DECLARE nomecursor INSENSITIVE SCROLL CURSOR FOR Comandoselect FOR READ ONLY UPDATE OF nome da coluna n Explicando a sintaxe temse MSDN 2016 2 nomeCursor é o nome do cursor que vai ser utilizado deve seguir o mesmo padrão de nomenclatura de variáveis 2 INSENSITIVE esse cursor faz uma cópia temporária no tempdb dos dados a serem usados por ele e as modificações feitas posterior mente na tabela não são refletidas nos dados desse cursor 2 SCROLL o cursor tem um ponteiro que faz a movimentação entre as linhasAs opções podem ser FIRST LAST PRIOR NEXT RELATIVE ABSOLUTE Se SCROLL não for especifi cado NEXT será a única opção de busca com suporte 2 SELECTSTATEMENT é uma instrução SELECT padrão que define o conjunto de resultados de um cursor Podemse usar as funções de agregação e criar SQL com cláusula where que faça com parações com variáveis 2 READ ONLY previne atualizações feitas por esse cursor Essa opção anula a funcionalidade padrão de um cursor para ser atua lizado 2 UPDATE OF columnname n define colunas atualizá veis em um cursor Se OF columnname n for especificado somente as colunas listadas permitirão modificações Se a UPDATE for especificada sem uma lista de colunas todas as colunas poderão ser atualizadas BancosDadosindd 154 17102016 100000 155 Recursos Avançados Agora que foi apresentada a sintaxe do cursor temse mais uma ferra menta para auxiliar no seu trabalho 2 open cursor utilizado para executar o select que foi definido den tro do cursor Sintaxe Open Cursor nomeCursor 2 fetch utilizado para navegar entre as linhas do cursor e recuperar valores do result set para variáveis Sintaxe FETCH NEXT PRIOR FIRST LAST ABSOLUTE n nvar RELATIVE n nvar FROM cursorname cursorvariablename INTO variablename n 2 NEXT retorna a linha de resultado imediatamente seguinte à atual e adiciona a linha atual à retornada Se FETCH NEXT for a primeira busca de um cursor a primeira linha do conjunto de resul tados será retornada NEXT é a opção padrão de busca de cursor 2 PRIOR retorna a linha de resultado imediatamente anterior à atual e decrementa a linha atual da retornada Se FETCH PRIOR for a primeira busca de um cursor nenhuma linha será retornada e o cursor será deixado posicionado antes da primeira BancosDadosindd 155 17102016 100000 156 Banco de Dados 2 FIRST retorna a primeira linha no cursor e a torna a linha atual 2 LAST retorna a última linha no cursor e a torna a linha atual 2 ABSOLUTE n nvar se n ou nvar for positivo a linha que está n linhas a partir do início do cursor será retornada e a linha retornada se tornará a nova linha atual Se n ou nvar for negativo a linha que está n linhas antes do final do cursor será retornada e a linha retornada se tornará a nova linha atual Se n ou nvar for 0 nenhuma linha será retornada N deve ser uma constante de número inteiro e nvar deve ser smallint tinyint ou int 2 RELATIVE n nvar se n ou nvar for positivo a linha que está n linhas após a linha atual será retornada e a linha retornada se tornará a nova linha atual Se n ou nvar for negativo a linha que está n linhas antes da linha atual será retornada e a linha retornada se tornará a nova linha atual Se n ou nvar for 0 a linha atual será retornada Se FETCH RELATIVE for especificado com n ou nvar definido como números negativos ou 0 na primeira busca feita em um cursor nenhuma linha será retornada N deve ser uma cons tante de número inteiro e nvar deve ser smallint tinyint ou int 2 cursorname é o nome do cursor aberto a partir do qual a busca deve ser feita Se um cursor global e um cursor local existirem com cursorname como seu nome cursorname será o cursor global se GLOBAL for especificado e será o cursor local se GLOBAL não for especificado 2 cursorvariablename é o nome de uma variável de cursor que faz referência ao cursor aberto a partir do qual a busca deve ser feita 2 INTO variablename n permite que os dados das colunas de uma busca sejam colocados em variáveis locais Cada variável na lista da esquerda para a direita está associada à coluna corres pondente no conjunto de resultados do cursor O tipo de dados de cada variável deve corresponder ou ser uma conversão implícita com suporte do tipo de dados da coluna do conjunto de resulta dos correspondente O número de variáveis deve corresponder ao número de colunas na lista de seleção do cursor MSDN 2016 BancosDadosindd 156 17102016 100000 157 Recursos Avançados Título do box Tabelas temporárias no TempDb Existe uma variável para validar a ação do Fetch FetchStatus Ela indica se o fetch foi executado com sucesso ou não Os possíveis valores para FetchStatus são 2 0 O FETCH foi realizado com sucesso 2 1O FETCH falhou 2 2 O registro trazido foi perdido 2 close utilizado para fechar um cursor Sintaxe Close nomeCursor 2 deallocate utilizado para liberar os recursos alocados pelo cursor Sintaxe Deallocate nomeCursor O exemplo da figura 82 mostra a utilização de cada um dos itens apresentados Figura 82 Cursor CreateProcedure pLeAluno Nome varchar70 as Declare idade smallint Datadate DataAtualdatetime Set dataAtual Getdate Declaração do Cursor Declare cTeste cursorfor BancosDadosindd 157 17102016 100000 158 Banco de Dados Select Nome DtNasc FromAluno Where Nome like nome Orderby Nome Abrir o cursor executar o select Open cTeste Verifica error na abertura do cursor Iferror 0 Begin Print Erro na abertura do cursor return End Fetchnextfrom cTeste into nome data WhileFETCHSTATUS 0 Begin Print nome Idade Convertchar5Datediffyy Data DataA tual Fetchnextfrom cTeste into nome data End Close cTeste Deallocate cTeste Fonte elaborado pelo autor O objetivo desta SP é mostrar o uso do Cursor A SP recebe o nome de um Aluno como parâmetro de entrada e caso não seja enviado nada assume o por default É criado um cursor que faz um select utilizando o parâmetro BancosDadosindd 158 17102016 100000 159 Recursos Avançados como condição na cláusula where logo depois é feito o Open Cursor que executa o comando que foi declarado dentro do cursor É neste momento que o select é realmente executado A variável Error é validada para garantir que a execução do cursor ocorreu corretamente quando aberto O ponteiro aponta para o primeiro registro do result set na sequência é feito um fetch que recupera os dados do result set da linha apontada naquele momento e atribui o valor de cada coluna em cada variável seguindo a ordem do select ou seja de acordo com a posição das colunas devem estar as variáveis que vão recebêlas Após associar as variáveis com o conteúdo da coluna o fetch NEXT posiciona o ponteiro do cursor para o próximo registro do result set No while é verificado o valor de Fetchstatus Enquanto for 0 os registros estão sendo recuperados com sucesso do result set Dentro do while temse o Print do nome do aluno seguido da idade que é calculada a partir da subtração da data atual recuperada pelo getdate pela data de nascimento Um novo Fetch é feito dentro do while para ir recuperando as demais linhas do result set Quando chegar ao último registro na próxima leitura o fetchstatus passa a ser 1 ocasionando a saída do while Depois o cursor é fechado e todos os recursos são liberados com o Deallocate A figura 83 mostra a execução da SP sem passar parâmetro e traz o resultado da execução Resultado Figura 83 Cursor Fonte elaborado pelo autor BancosDadosindd 159 17102016 100000 160 Banco de Dados Você pode utilizar um cursor com conceitos de transação com selects mais complexos dentro e fora de SPs As aplicações são inúmeras acres centando muitas funções que podem ser executadas pelo banco de dados para atender às necessidades das aplicações O exemplo apresentado utilizou somente NEXT para pesquisar linha a linha do cursor mas você pode utilizar o PRIOR LAST FIRST conceito de posição absoluto ou relativo de acordo com a posição do cursor para acessar os dados 83 Tabelas temporárias A criação de tabelas temporárias é muito útil em situações em que se precisa utilizar uma tabela por um determinado tempo e depois ela pode ser dropada eliminada Tabelas temporárias são criadas em um banco especial chamado tem pdb que armazena os dados temporários de uma instância SQL Para criar uma tabela com visualização local utilize o no início do nome da tabela Para criar a tabela com visualização global utilize em vez de Sintaxe Create Table nomeTabela Atributo Tipo de dado Constraint pknome DaConstraint Primary key Atributo2 Tipo OU Create Table nomeTabela Atributo Tipo de dado Constraint pknome DaConstraint Primary key Atributo2 Tipo BancosDadosindd 160 17102016 100000 161 Recursos Avançados A cada exemplo mostramos conceitos mais complexos para ampliar o conhecimentoNa figura 84 temos primeiro a introdução ao conceito de select intoEste comando permite que seja inserido o resultado de um select em uma tabela Se for uma tabela comum ela deve ser previamente criada se for uma tabela temporária não precisa ser previamente criada neste caso específico Logo após é feito um select na tabela temporária para mostrar que o conteúdo da tabela Aluno realmente foi inserido na tabela tempTable E no fim do TSQL a tabela foi explicitamente eliminada Figura 84 Tabela temporária Fonte elaborado pelo autor Figura 85 Tabelas temporárias no TempDb Fonte elaborado pelo autor Observe que a tabela temporária aparece no banco de dados TEMPDB até que seja dropada ou a sessão eli minada BancosDadosindd 161 17102016 100000 162 Banco de Dados Podemse criar quantas tabelas temporárias foram necessárias Você pode criar explicitamente utilizando o comando create table da mesma forma que é criada uma tabela normal 84 Triggers Uma trigger também conhecida como gatilho é um tipo especial de procedimento armazenado que é disparado automaticamente quando uma determinada ação ocorre no servidor de banco de dados Triggers são estruturas simples para criação mas pode conter códigos complexos Portanto devese analisar bem a solução criada Existem dois tipos de triggers 2 Data Manipulation Language DML 2 Data Definition Language DDL Triggers DML são executadas quando um usuário tenta modificar dados por meio de comandos de INSERT UPDATE ou DELETE em uma tabela ou view Elas são disparadas quando qualquer evento válido é acionado e não depende de quais linhas da tabela serão afetadas ou não Sintaxe CREATE TRIGGER nometrigger ON tabela view WITH dmltrigger n FOR AFTER INSTEAD OF INSERT UPDATE DELETE NOT FOR REPLICATION AS Comando SQL n BancosDadosindd 162 17102016 100000 163 Recursos Avançados Triggers DDL são executadas em resposta a diversos comandos de CREATE ALTER e DROP Existem também triggers de logon executadas em resposta ao evento LOGON que é gerado quando as sessões de um usuá rio estão sendo estabelecidas Sintaxe CREATE TRIGGER nometrigger ON ALL SERVER DATABASE WITH ddltrigger n FOR AFTER tipo do evento grupo de eventos n AS sqlstatement n Explicação da sintaxe MSDN 2016 2 nometrigger é o nome da trigger que vai ser utilizada deve seguir o mesmo padrão de nomenclatura de variáveis 2 tabela ou view é a tabela ou view onde DML é executada Uma view só pode ser referenciada por uma trigger INSTEAD OF Triggers DML não podem ser definidas para tabelas temporárias locais ou globais 2 database aplica o escopo de uma trigger DDL ao banco de dados atual Se for especificada a trigger será acionada sempre que event type ou eventgroup ocorrer no banco de dados atual 2 FOR ou AFTER AFTER especifica que a trigger DML é dis parada iniciada apenas quando todas as operações especificadas na instrução SQL de trigger são executadas com êxito Todas as verificações de restrição devem finalizar com sucesso para que esta trigger seja disparada AFTER é o padrão quando FOR é a única palavrachave especificada Triggers AFTER não podem ser defi nidas em views BancosDadosindd 163 17102016 100000 164 Banco de Dados 2 instead of especifica que a trigger DML será executada em vez da instrução SQL de trigger substituindo as ações das instruções de trigger INSTEAD OF não pode ser especificado para triggers DDL ou de logon No máximo uma trigger INSTEAD OF pode existir por instrução INSERT UPDATE ou DELETE 2 DELETE INSERT UPDATE especifica quais são as instruções de modificação de dados que disparam a trigger DML É necessário especificar pelo menos uma opção É permitida qualquer combinação dessas opções em qualquer ordem na definição da trigger Para exemplificar figura 86 o uso da trigger criouse um exemplo que grava o histórico das ações na tabela Aluno para uma tabela AlunoHist que além de todos os campos da tabela Aluno tem também a ação que foi feita insert delete ou update e a data e hora em que foi modificada Logo no início temos o nome da trigger como traudit aplicada na tabela aluno para insert delete ou update Observe que o comando está dentro do quadro vermelho exists Este comando serve para verificar se há a existência de algum registro em determinada tabela Ele pode ser utilizado em vários lugares de TSQL não somente entre triggers O MS SQL Server trata a inserção deleção e atualização de uma forma diferenciada Ele cria tabelas temporárias importantes A tabela inserted vai ter o conteúdo exato dos dados que vão ser inseridos na nova tabela Caso esses dados não quebrem nenhuma regra de integridade referencial ou de restrições o SQL pega os dados da tabela inserted e efetiva neste caso na tabela aluno Temos a tabela deleted que faz exatamente a mesma coisa mas quando os dados estão sendo deleta dos de uma tabela Então primeiramente o SQL SERVER grava esses dados numa tabela temporária que tem a mesma estrutura da tabela que está sendo modificada e se a deleção não ferir nenhuma regra de integridade ou restrição o delete é efetivado Já para o update o tratamento é diferente pois o SQL server pega os dados correntes da tabela aluno insere na tabela deleted e os novos dados são inseridos na tabela inserted Caso o update não quebre nenhuma regra de integridade referencial ou restrição é feito um delete na tabela daquele registro e o insert do novo registro BancosDadosindd 164 17102016 100000 165 Recursos Avançados Figura 86 Trigger Fonte elaborado pelo autor Figura 87 Mostrar Trigger Fonte elaborado pelo autor Ao criar a trigger com sucesso ela aparece na inter face gráfica na opção triggers ou gatilhos depende da lin guagem com a qual o SQL Server foi instalado BancosDadosindd 165 17102016 100000 166 Banco de Dados A figura 88 mostra a execução do delete e o disparo da trigger Figura 88 Execução da trigger Fonte elaborado pelo autor Observe que na figura 89 o registro com CPF 22 foi deletado Figura 89 Registro deletado Fonte elaborado pelo autor Enquanto na figura 810 ele foi inserido na tabela AlunoHist com Acão D e a data de alteração Figura 810 Registro inserido Fonte elaborado pelo autor BancosDadosindd 166 17102016 100000 167 Recursos Avançados Para alterar uma trigger usase o comando a seguir Sintaxe ALTER TRIGGER nometrigger ON tabela view WITH dmltrigger n FOR AFTER INSTEAD OF INSERT UPDATE DELETE NOT FOR REPLICATION AS Comando SQL n Para eliminar uma trigger utilizase o comando a seguir Sintaxe Drop Trigger Nome da trigger Triggers podem parecer complexas e difíceis de trabalhar mas com a prática e muitos exercícios você consegue dominar essa técnica e elaborar grandes soluções Síntese Este capítulo apresenta vários recursos avançados como utilização de funções de sistema e outras técnicas que permitem a criação de programas mais complexos como cursores que permitem o acesso e manipulação linha a linha de cada registro e atributo Também a utilização de tabelas temporárias que são muito úteis em situação que se precisa de armazenamento volátil dos dados para auxiliar nos cálculos e ações E finalizando com a utilização de triggers que permite geração de relatórios proteção de dados armazena mento de histórico para futuras auditorias BancosDadosindd 167 17102016 100000 168 Banco de Dados Da teoria para a prática Com a estrutura da tabela Aluno a seguir faça o que se pede 2 Crie a tabela adicionando os campos Ação I D U Insert Delete Update e da Data e hora da modificação conforme a tabela a seguir 2 Crie uma ou mais triggers que para cada ação gravem o registro do aluno a ação que está sendo executada e a data e hora da modificação 2 Crie para Insert delete e update 2 Apresente uma alternativa de solução diferente da exposta como exemplo no livro teste a trigger para cada ação BancosDadosindd 168 17102016 100000 Entre todas as ações tarefas e planejamento que devem ser feitos a mais importante de todas é o backup cópia de segurança Por que é tão importante assim A importância de um backup bem feito e seguro tem a mesma importância que o seu banco de dados e todas as informações que ele contém afinal caso algum problema ocorra seja ele físico ou lógico existe a possibilidade de recupera ção Tão importante quanto o backup é a capacidade de restaurá lo pois de nada serve um backup se você não consegue recuperar as informações Backup Restore Segurança e Otimização 9 BancosDadosindd 169 17102016 100000 170 Banco de Dados Outra tarefa importante é a criação de logins e usuários É preciso dar privilégios necessários a cada um deles de forma adequada E finalizando este capítulo veremos uma introdução à otimização de queries Objetivos de aprendizagem 2 apresentar backup e restore 2 mostrar criação manutenção de ids e privilégios 2 otimização de comandos SQL 91 Backup O que aconteceria se por algum motivo o banco de dados fosse per dido Qual o impacto que ele teria para a empresa Falhas podem acontecer e devese estar preparado para recuperar os dados caso esses problemas aconteçam Os principais tipos de falha são 2 lógicas erros de usuário sejam propositais ou não que removem ou alteram dados ou estruturas 2 sistema operacional ou instância do banco corrompida 2 físicas problemas no hardware em alguma peça do servidor ou storage armazenamento ou até mesmo crash dano completo de um servidor 2 desastres naturais ou artificiais incêndios alagamentos O backup oferece uma proteção fundamental para registros críticos dos bancos de dados Para minimizar o risco de perda deve ser feito backup dos bancos para manter salvas as ações frequentes nos dados cuja melhor forma de recuperação em caso de perda é uma estratégia de backup e restauração bem planejada Um backup é a geração de um ou mais arquivos físicos que podem ser armazenados em discos fitas e outros dispositivos Esse arquivo contém os regis tros do banco de dados de forma que possam ser recuperados posteriormente BancosDadosindd 170 17102016 100000 171 Backup Restore Segurança e Otimização Pela disponibilidade podese classificar o banco de dados como 2 online é executado com o Banco de dados online com pequeno impacto aos usuários que estão utilizando o banco no momento 2 offline neste caso é feita uma cópia física de todos os arquivos que compõem o banco de dados Para isso o serviço do banco tem que ser parado de forma que o sistema operacional permita que a cópia seja feita Os tipos de backup são 2 completo full todos os dados existentes no bando de dados no momento do backup incluindo todas as entradas do log de transa ções são gravados no arquivo de backup 2 diferencial incremental todos os dados diferentes ou seja que sofreram alterações desde o último backup completo são gravados no arquivo de backup 2 log de transação os dados da log de transação são gravados no arquivo de backup Importante O início de qualquer estratégia dever ser sempre com o backup completo Para usar o incremental ou o backup de log deve existir um backup completo Para montar uma estratégia de backup precisase calcular 2 qual período do dia os aplicativos precisam acessar o banco de dados Essa informação é importante para escolher o melhor horário para agendar o backup do banco de dados porque dependendo do tamanho do banco isso pode durar horas e às vezes dias para terminar o backup completo Devese agendar um horário em que a utilização do banco seja muito pequena para que o impacto seja o menor possível na concorrência entre o backup e os usuários pelo uso do banco e para que termine mais rápido BancosDadosindd 171 17102016 100000 172 Banco de Dados 2 com que frequência as alterações e atualizações no banco deverão ocorrer É importante verificar em caso de recuperação por backup qual o tempo de dados perdidos a empresa pode suportar Por exemplo caso as alterações sejam raras podese agendar um backup de log a cada hora ou se forem frequentes geralmente a agenda fica para fazer o backup de log a cada 5 minutos já que em caso de neces sidade de recuperar dados do backup somente os últimos cinco minutos de dados são perdidos Também podese intercalar com backup incremental 2 qual o tamanho de um backup completo de banco de dados É importante saber qual a capacidade de armazenamento dos arquivos de backup se eles irão para disco ou fita e qual a retenção destes arquivos dias semanas meses Com essas informações já se deve criar a estratégia de backup por exem plo um backup completo aos domingos ao meiodia backups incrementais terça e quinta à meia noite e backups de log a cada quinze minutos backup completo diariamente à meia noite backup de log a cada 10 minutos Saiba mais Existem bancos de dados que devem estar online 24 horas por dia para isso além do backup existem soluções de alta disponibilidade O backup completo segue a seguinte sintaxe BACKUP DATABASEnomebancodedados TO backupdevice n WITH comopções o O backup incremental segue a seguinte sintaxe BancosDadosindd 172 17102016 100000 173 Backup Restore Segurança e Otimização BACKUP DATABASE nomebancodedados TO backupdeviceWITH DIFFERENTIAL O backup incremental segue a seguinte sintaxe BACKUP Lognomebancodedados TO backupdevice n WITH comopções o Recomendamse extensões para cada um dos tipos de backup porém não são obrigatórias e sim sugestões de padrão 2 completo BAK 2 incremental INC 2 LOG TRN Outro ponto importante é identificar os backups com data e hora assim temse o tempo exato em que foi criado O SGBD precisa ser informado quais são os tipos de backup que vão ser feitos Isso é indicado no atributo do banco de dados chamado de modelo de recuperação recovery model As operações de backup e restaura ção ocorrem dentro desse contexto que controla a forma de gerenciamento do log de transações O modelo de recuperação de um banco de dados indica para quais tipos de backups e cenários de restauração o banco de dados oferece suporte É utilizado o modelo de recuperação simples para backups completos ou o modelo de recuperação completa para backups de log de transação Importante O modelo de recuperação deve ser selecionado no banco de dados antes de começar qualquer estratégia de backup e restore pois só funcionarão corretamente depois da solução escolhida BancosDadosindd 173 17102016 100001 174 Banco de Dados Sintaxe para backup somente completos USE master ALTER DATABASE NomeBanco SET RECOVERY SIMPLE Sintaxe para backup completo e de log USE master ALTER DATABASE NomeBanco SET RECOVERY FULL A figura 91 apresenta o exemplo de um backup completo do banco de dados dbFael Esse backup está armazenado no cFaelDbFaelbak as opções de backup apresentadas estão logo depois do WITH Compression é uma opção para compactar o backup usando o sistema de compactação do SQL Server e STATS indica o percentual de conclusãoNeste caso mostrar a mensagem a cada cinco por certo completo Figura 91 Backup completo Fonte elaborado pelo autor BancosDadosindd 174 17102016 100001 175 Backup Restore Segurança e Otimização Observe que o arquivo foi gerando no caminho path especificado figura 92 Figura 92 Arquivo de backup Fonte elaborado pelo autor Importante Banco de dados e os backups devem estar em dispositivos separa dos pois se o dispositivo que contém o banco de dados falhar seus backups ficarão indisponíveis A figura 93 demonstra como apagar alguns registros Figura 93 Alterando o conteúdo do banco Fonte elaborado pelo autor Agora fazendo backup de log figura 94 o Stats foi alterado para 20 BancosDadosindd 175 17102016 100001 176 Banco de Dados Figura 94 Backup de log de transação Fonte elaborado pelo autor Os dois arquivos de backup se encontram no diretório indicado no comando como mostra a figura 95 Figura 95 Arquivos de backup Fonte elaborado pelo autor Com os backups prontos agora devese compreender como funciona o processo de restauração 92 Restore recuperação do backup O restore é um processo com várias fases que copia todos os dados e páginas de log de um backup do SQL Server para um banco de dados espe cificado e logo após executa rollforward de todas as transações registradas no backup de log aplicando as alterações registradas TECHNET 2016 Assim como no backup tudo se inicia no backup completo o restore funciona da mesma maneira BancosDadosindd 176 17102016 100001 177 Backup Restore Segurança e Otimização Quando ocorrer um problema que precise restaurar o backup qual a forma correta de fazer Primeiro verifique qual o backup completo que existe antes de o pro blema acontecer Esse é o backup que vai ser restaurado Depois restaure os backups incrementais se existirem do mais antigo para o mais recente Por último restaure todos os backups de log do mais antigo ao mais novo até o horário anterior ao horário em que ocorreu o problema Saiba mais Durante o processo de restore o banco não fica disponível para utilização pelo usuário Somente quando o SBDB executar com pletamente todas as fases do restore o banco ficará disponível para utilização Os SGBDs controlam a ordem em que o backup foi feito para garantir que o restore siga essa ordem e não haja falhas O SQL Server usa o conceito de LSN número de sequência de log para definir o ponto de recuperação para uma operação de restore Os LSNs são usados internamente durante uma sequência RESTORE para localizar o pointintime para o qual os dados foram restaurados Quando um backup é restaurado os dados são restaurados ao LSN que corresponde ao pointintime em que o backup foi realizado O backup diferencial e o backup de log avançam o banco de dados restaurado para uma hora posterior que corresponde a um LSN mais alto Cada registro do log de transações é identificado de forma exclusiva por um LSN número da sequência de log Os LSNs são ordenados de tal modo que se LSN2 for maior do que LSN1 a alteração descrita pelo registro de log mencionado por LSN2 ocorreu depois da alteração descrita pelo registro de log LSN MSDN 2016b Se houver quebra de LSN algum arquivo de backup faltando ou corrom pido o restore não poderá prosseguir para os próximos backup files portanto seja cuidadoso em relação ao ambiente de armazenamento dos files de backup BancosDadosindd 177 17102016 100001 178 Banco de Dados Sintaxe RESTORE DATABASE NomeDatabase FROM Disk Tape Caminho omebackup WITH MOVE nome logico TO Caminho nomefile REPLACE RECOVERY NORECOVERY STATS Pontos importantes em relação à sintaxe 2 se o banco de dados já existir utilize a opção REPLACE para sobrescrever 2 se precisar mudar os arquivos do banco de lugar use MOVE para cada arquivo colocando o nome lógico do file e apontando para onde vai ficar o arquivo físico 2 se precisar aplicar outros backups log por exemplo utilize a opção NORECOVERY Nesse caso o banco fica esperando próximos arquivos a serem restaurados Caso não seja necessário aplicar mais nenhum backup utilize o RECOVERY A figura 96 demonstra o restore do backup que foi feito no passo anteriorComo o banco já existia foi usado REPLACE para sobrescrever e NORECOVERY para deixar o banco à espera de novos restores Figura 96 Restore Fonte elaborado pelo autor BancosDadosindd 178 17102016 100001 179 Backup Restore Segurança e Otimização Enquanto espera outros restores o banco fica indisponível e no status de restaurando conforme a figura 97 Figura 97 Restaurando Fonte elaborado pelo autor Após o restore da log com NORECOVERY que foi omitido pois é a opção default observe figura 98 que o banco agora está disponível para utilização Figura 98 Restore de log Fonte elaborado pelo autor O planejamento da estratégia de backup e restore é crucial para a recu peração em caso de problemas Depois de criar o plano teste várias vezes veja o tempo necessário para restaurar tudo e confira se está de acordo com a necessidade do cliente 93 Otimização Existem inúmeras técnicas de otimização que podem ser utilizadas em cada SGBD e cada uma delas depende das especificidades com a qual o SGBD executa suas queries e planos de execução Neste item será discutida a importância do índice sua otimização Os índices são estruturas utilizadas para recuperar informações em uma tabela com o menor número possível de operações de leituras deixando a pesquisa mais rápida e eficiente Os índices podem ser BancosDadosindd 179 17102016 100001 180 Banco de Dados 2 clusterizados conhecidos como índices físicos só pode existir um por tabela Geralmente utilizase a chave primária para criar esses índices 2 não clusterizados chamados de índices lógicos podem existir vários por tabelas Índices devem ser criados com o mínimo de colunas possível Utilizeos para melhorar o desempenho da consulta em tabelas com baixos requisitos de atualização insert delete e update mas com grandes volumes de dados Segundo MSDN 2016a existem algumas recomendações para criar índices 2 índices em views podem melhorar bastante o desempenho quando tiver agregações junções de tabela ou uma combinação de agrega ções e junções 2 índices não clusterizados podem ser usados nas colunas frequente mente usadas em predicados e condições de junção em consultas 2 mantenha o comprimento da chave de índice curto para os índices clusterizados 2 as colunas que forem do tipo de dados ntext text image var charmax nvarcharmax e varbinarymax não podem ser espe cificadas como colunas de chave de índice A ordem das colunas que são usadas na cláusula WHERE é importante Em um critério de consulta igual a maior que menor que ou BETWEEN ou que participa em uma junção deve ser posicionada pri meiro Colunas adicionais devem ser ordenadas com base em seu nível de distinção ou seja do mais distinto ao menos distinto A sintaxe para criação de um índice é CREATE UNIQUE CLUSTERED NONCLUS TERED INDEX NomeIndice ON tabela coluna ASC DESC n INCLUDE columnname n BancosDadosindd 180 17102016 100001 181 Backup Restore Segurança e Otimização Observe o plano de execução apresentado na figura 99 Ele mostra como o SQL Server faz para pesquisar os dados nas tabelas compara as esta tísticas e escolhe um caminho a seguir Neste caso ele faz uma pesquisa pelo índice clusterizado e depois executa o Sort para fazer o orderby Figura 99 Plano de execução Fonte elaborado pelo autor O exemplo de criação do índice está na figura 910 Foi criado um índice ixnome na tabela Aluno utilizando a coluna Nome como indexador Figura 910 Criação Index Fonte elaborado pelo autor BancosDadosindd 181 17102016 100001 182 Banco de Dados A figura 911 apresenta o resultado da mesma pesquisa após a criação do índice Nem sempre a criação do índice garante que o SGBD vai utilizálo por isso devese analisar para ter certeza de que o índice está sendo realmente útil Figura 911 Após criação do índice Fonte elaborado pelo autor Sintaxe para excluir um índice DROP INDEX nomeTabelaNomeindice Grandes números de índices em uma tabela afetam o desempenho das instruções INSERT UPDATE e DELETE porque todos os índices precisam ser ajustados adequadamente à medida que os dados são alterados em uma tabela Sempre valide a real necessidade de criação de índice 94 Segurança Para acessar os bancos de dados é necessário que existam logins e usuários Os logins são utilizados para conexão com o banco de dados e BancosDadosindd 182 17102016 100001 183 Backup Restore Segurança e Otimização os usuários são vinculados aos logins e têm os privilégios relacionados ao banco de dados O SQL Server utiliza dois tipos de logins 2 Windows são criados no Active Directory AD do Windows e utilizados no SQL server 2 SQL são criados dentro do próprio SQL precisam de definição de senha e se necessário data de expiração mudança da senha e checar a política de segurança para verificar a qualidade da senha Sintaxe para criar login do Windows Este login já deve existir no AD CREATE LOGIN Dominiologin FROM WINDOWS GO Sintaxe para criar login do SQL USE master GO CREATELOGIN LogindoSQL WITHPASS WORDNPasswordMUSTCHANGEDEFAULT DATABASEDefaultDatabaseCHECKEXPIRA TIONONOFFCHECKPOLICYONOFF GO Para nomear logins utilize as mesmas regras para definição de nomes de banco e tabelas Comece por uma letra utilize letras números e sublinhado Não utilize caracteres especiais A figura 912 mostra um exemplo de criação de login SQL até o momento em que não foi dado nenhum privilégio ainda BancosDadosindd 183 17102016 100001 184 Banco de Dados Figura 912 Criação de logins Fonte elaborado pelo autor O SQL Server já vem com ROLES regras de privilégios preparadas demonstradas na figura 913 para leitura escrita proprietário do banco entre outros Figura 913 Roles Fonte elaborado pelo autor Podese dar o privilégio direto utilizando o comando GRANT quanto der o privilégio pela role se o usuário não existir no banco ele vai ser criado BancosDadosindd 184 17102016 100001 185 Backup Restore Segurança e Otimização e vinculado ao login Se já existir o SQL só faz o vínculo e adiciona como membro da role assim um usuário pode acessar o banco e seus objetos de acordo com os privilégios figura 914 Figura 914 Adiciona role Fonte elaborado pelo autor Figura 915 Funções do sistema Fonte elaborado pelo autor Para remover os privilégios podese uti lizar o REVOKE ou remover o login da role Além das roles temos as funções do ser vidor que podem ser dadas aos usuários mas essas funções são recomendadas somente para DBAs e demais administradores BancosDadosindd 185 17102016 100001 186 Banco de Dados É muito importante fazer a verificação e dar permissões corretamente pois muitos desses sistemas participam de auditorias que podem desclassifi car eou até cancelar privilégios das empresas por falta de segurança Síntese Esse capítulo é um dos mais importantes porque garante a manutenção do banco de dados com as estratégias de backup e restore que devem ser muito bem planejadas Geralmente os backups ficam fisicamente em outro centro de dados separado do local de armazenamento do banco Em relação à otimização um ponto a ser bem analisado é a criação de índices e verificar sua utilização o que pode ser feito vendo o plano de exe cução e observando qual caminho está sendo seguido E por último a estrutura de logins usuários e privilégios Lembrese se o seu sistema não está corretamente protegido você pode ter dados maquia dos alterados apagados e inconsistentes portanto planeje muito bem quem pode acessar o banco de dados e o que pode ser feito Da teoria para a prática Dado o modelo lógico a seguir crie as tarefas solicitadas BancosDadosindd 186 17102016 100001 187 Backup Restore Segurança e Otimização 2 Faça um backup completo do banco de dados 2 Faça um backup de log do banco de dados 2 Restore os backups 2 Crie um login chamado IdTest e dê privilegio de leitura no banco de dadosdbAluno BancosDadosindd 187 17102016 100001 BancosDadosindd 188 17102016 100001 A tomada de decisão nas empresas é atualmente uma das mais complexas e importantes tarefas dos administradores O mundo está mudando rapidamente novas estruturas de comuni cação e comércio aparecem a cada ano existem redes sociais smar tphones tablets e outros equipamentos que mudaram a forma de o consumidor escolher produtos Existem muitos dados sendo atua lizados o tempo todo Tecnologias Avançadas 10 BancosDadosindd 189 17102016 100001 190 Banco de Dados Desta forma alguém precisa analisálos e selecionar o que de fato é infor mação importante para a tomada de decisão correta A capacidade de arma zenamento e velocidade de processamento em banco de dados acompanhou de perto essa evolução pois hoje se tem Terabytes Petabytes de dados para analisar Isso seria humanamente impossível pois quando se conseguissem analisar todos esses dados eles já estariam obsoletos Para ajudar a tomada de decisão foram criadas inúmeras tecnologias algumas já embutidas em banco de dados outras não mas agora o administrador tem ferramentas que auxiliam a analisar e até projetar o futuro Objetivos de aprendizagem 2 introdução ao Business Intelligence 2 apresentação do Data Warehouse 2 mostra do Data Mining 101 Business Intellingence BI Existem inúmeras definições para BI Côrtes 2002 define como um conjunto de conceitos e metodologias com o objetivo de apoio à tomada de decisões nos negócios a partir da transformação do dado em informação e da informação em conhecimento fazendo a análise de dados e visando à desco berta de novas oportunidades À medida que os sistemas de informação evoluíram as empresas recebe ram novas ferramentas para auxiliar na tomada de decisão e hoje sua utiliza ção correta é fundamental para o futuro das empresas A figura 101 apresenta a arquitetura básica de BI Temos como fonte de dados os sistemas operacionais embora tendo o mesmo nome esses sis temas não são os operacionais de computadores e sim os sistemas aplicativos utilizados para manter o operacional das empresas as transações Observe que temos vários sistemas por departamentos com seus respectivos armazena mentos São utilizadas ferramentas de ETL Extract Transformand Load para extrair os dados da fonte transformálos limpar e padronizar e finalmente carregálos em novas bases que podem ser data warehouse ou data mart A BancosDadosindd 190 17102016 100001 191 Tecnologias Avançadas partir deste ponto temos dados prontos para serem analisados relatórios data mining OLAP Online Analytical Processing e outros recursos Figura 101 Arquitetura BI Fonte Silva 2011 p 34 As ferramentas de BI podem auxiliar em vários departamentos das empresas permitindo a análise de 2 tendências do mercado 2 mudanças no comportamento de clientes 2 padrões de consumo Ferramentas de BI conseguem analisar as bases de dados e fornecer informações de forma que as empresas possam direcionar oferecendo novos serviços ou produtos de forma diferenciada de acordo com os recursos e perfil de mercado Mas essas informações ainda precisam ser analisadas por administradores para a tomada de decisão BancosDadosindd 191 17102016 100002 192 Banco de Dados 102 Data Warehouse DW Geralmente em uma empresa temos os dados distribuídos em sistemas setoriais como financeiro contábil recursos humanos entre outros Esses sistemas não estão integrados e cada uma tem sua própria arquitetura com formas de armazenamento diferentes e dados replicados O data warehouse funciona como um armazém de dados Inmon 1993 p31 definiu um DW como um conjunto de dados orientados por assunto integrado variável com tempo e não volátil que fornece suporte ao processo de tomada de decisão do negócio A seguir algumas características 2 orientado por assunto especifica suas tabelas em temas de negócio 2 integrado contém informações de vários setores e sistemas da empresa 2 variável com o passar do tempo vai recebendo mais informações 2 não volátil o armazenamento dos dados é permanente Um DW dá suporte para a análise e tomada de decisões do negócio criando e mantendo um banco de dados integrado com informações históri cas consistentes orientadas para o assunto com benefícios de custo econo mia de tempo e aumento de produtividade As decisões podem ser tomadas mais rapidamente com a certeza de que os dados são atuais e precisos Para melhor compreensão da utilização de um DW ou de Banco de Dados Operacional Transacional veja o comparativo na figura 102 Figura 102 DW x Banco Transacional DW Banco Operacional Orientado ao assunto Orientado à transação Dados Históricos Dados atuais Grande quantidade de dados Média e grande quantidade de dados Carga de acordo com a granularidade Atualizações diárias Desnormalizado Normalizado BancosDadosindd 192 17102016 100002 193 Tecnologias Avançadas DW Banco Operacional Fonte elaborado pelo autor O ciclo de vida do DW é formado por SINGH 2001 2 análise 2 design 2 importação dos dados 2 instalação das ferramentas 2 teste e implementação A figura 103 apresenta os componentes do DW onde se apresentam dados operacionais externos que podem vir de inúmeros SGBDs e até arquivos texto planilhas entre outros Inicialmente o DW deve ter a estrutura de acessar os dados depois transformálos pois podem ter diferentes representações tipo de dado formato e precisão e uma conversão explícita não funcionária por exem plo em um sistema Sexo estaria como M ou F em outro poderia ser 0 para masculino e 1 para feminino em outro sistema poderia vir como H ou M para homem e mulher Nesse caso é necessário primeiro escolher qual padrão vai ser utilizado no DW e em seguida converter todas as entradas diferentes Depois montar a solução de como serão distribuídos e modelados os dados seguido do armazenamento a localização e finalmente utilização de ferramentas para análise BancosDadosindd 193 17102016 100002 Banco de Dados Figura 103 Componentes do DW Dados Operacionais e Externos t Acessar Transformar Distribuir Armazenar Analisar Dados Limpar Organizar Dados Catdlogo de Consulta e Operacionais Reconciliar Combinar Relacionais Informagées Relatério e Externos Aprimorar Miultiplas Caches Visualizagées Andlise Sumarizar Fontes Especializado de Negécios Multidi Agregar Popular sob s Modelos mensional Demanda Miltiplas Data Mining Plataformas Hardware Fluxo Fonte Singh 2001 Cada uma das etapas deve ser discutida e revisada antes da sua implementagao 103 Modelo multidimensional O modelo multidimensional apresenta informagées do ponto de vista de tempo diferente do banco transacional que se observa por transacgdes Essa técnica é uma modelagem conceitual de negécios e é formada por tabelas dimensées e fatos O modelo multidimensional relaciona tabelas de fatos com tabelas de dimens6es em um banco de dados A figura 104 mostra esse modelo que é conhecido como Cubo onde se tem o produto por regiao e més 194 BancosDadosindd 194 oe 17102016 100002 195 Tecnologias Avançadas Figura 104 Modelo dimensional Suco N S O Cola Leite Creme Cadeira Sabonte 1 2 3 4 5 6 7 Produto Mês Região Fonte MSDN 2007 Para fazer essa modelagem devese levar em consideração o que é necessário do negócio para ela quais são os fatos que precisam ser medidos produto x mês x região é o fato e cada um desses itens individualmente é a dimensão Esta etapa é extremamente importante pois deve considerar somente os dados necessários Qual é a granularidade ou seja qual o nível de detalhamento sumarização porque quanto menor a granularidade mais detalhada a informação Isso afeta o tamanho do banco de dados pois quanto mais detalhe mais espaço será ocupado No exemplo da figura 103 foi esco lhida a granularidade por mês mas poderia ser por dia ou semana Isso vai variar de acordo com a necessidade da informação solicitada pelo cliente E finalmente quais os atributos serão necessários para cada dimensão Basicamente são utilizados dois tipos para a modelagem de um DW são 2 estrela star 2 floco de neve snowflake BancosDadosindd 195 17102016 100002 196 Banco de Dados O esquema estrela é apresentado na figura 105 Basicamente temse a tabela fatos e suas dimensões sem outros níveis de relacionamento O banco de dados é desnormalizado para ser mais rápido na geração das consultas e relatórios Figura 105 Star Fonte MSDN 2007 BancosDadosindd 196 17102016 100002 197 Tecnologias Avançadas O modelo floco de neve permite uma ligeira normalização figura 106 para melhor estruturar os atributos entre as tabelas Mas é preciso ter cui dado pois quanto mais normalizado mais lento vai ficar Figura 106 Snowflake Fonte MSDN 2007 BancosDadosindd 197 17102016 100002 198 Banco de Dados Depois de definir o modelo devese começar a inserção dos dados Um DW é somente de leitura não se alteram dados somente se inserem os dados O período para a transformação e carga dos dados deve ser esco lhido por exemplo se os dados vão ser carregados semanalmente ou men salmente Geralmente são criados Jobs tarefas agendadas para executar essa função Um DW tende a tornarse um banco de dados muito grande portanto devese analisar a escalabilidade de hardware e software para garantir que irá suportar o crescimento Outro fator importante é que para criar um DW completo é muito pesado e complexo então são criadas partes do DW chamadas de data marts A diferença está no escopo pois enquanto o DW é feito para atender a uma empresa como um todo o data mart é criado para atender a um ou vários setores da empresa Saiba mais Faça o tutorial de Modelagem dimensional para SQL Server em httpsmsdnmicrosoftcomptbrlibraryhh231691vsql120 aspx 104 Data Mining DM O Data mining também chamado de mineração de dados é o processo de extração de conhecimento de grandes bases de dados utilizando técnicas de IA que procuram relações que existam entre os dados num banco A mineração de dados é o processo de descoberta de informações poten cialmente úteis buscando relações e padrões globais em grandes conjuntos de dados DM usa análise matemática para derivar padrões e tendências que existem nos dados Geralmente esses padrões não podem ser descobertos com a exploração de dados tradicional pelo fato de as relações serem muito complexas ou por haver muitos dados MSDN 2016 BancosDadosindd 198 17102016 100002 199 Tecnologias Avançadas O conhecimento descoberto durante a aplicação de técnicas de DM ajuda na tomada de decisões eou avaliação de resultados A utilização de DM é capaz de 2 criar parâmetros para entender o comportamento do consumi dor o que é mais consumido em períodos do mês ou estações do ano quais compras têm produtos associados ou seja prever hábitos de compra 2 identificar as relações das escolhas de produtos e serviços quais grupos procuram determinados produtos e serviços quando são procurados 2 analisar comportamentos habituais para detectar fraudes muito utilizado em bancos e companhias de cartões de crédito usando o comportamento usual do cliente para identificar possíveis fraudes O data mining é na realidade uma fase no processo de descoberta de conhecimento em base de dados KDD Knowledge Discovery in Databases Figura 107 KDD Fonte Usama 1996 Definição de metas definição do problema a ser resolvido pelo processo KDD FAYAD 1996 2 Seleção seleciona os dados apropriados para análise de acordo com a necessidade do cliente BancosDadosindd 199 17102016 100002 200 Banco de Dados 2 Processamento é a fase de limpeza dos dados onde certos dados são removidos se forem indicados como desnecessários 2 Transformação os dados são padronizados para assegurar um for mato consistente afinal vindos de ambientes diferentes os dados podem não ter o mesmo padrão ou até não estarem preenchidos Neste caso precisase resolver se vão ser descartados ou receberem valores padrão 2 Mineração nesta fase são aplicadas as técnicas de DM 2 Avaliação os resultados das técnicas são avaliados DM tem várias técnicas de mineração porém não existe uma técnica específica para resolver todos os problemas assim métodos devem ser utiliza dos para propósitos diferentes e cada um deles oferece vantagens e desvanta gens As principais técnicas são 2 regras de associação estabelecem uma relação estatística entre certos itens de dados em um conjunto São utilizadas na tarefa de associação 2 árvores de decisão é a técnica indicada no não terminal Repre senta um teste ou decisão sobre o item de dado considerado cujo objetivo é separar as classes Listas de classes diferentes tendem a ser alocadas em subconjuntos diferentes Árvores de decisão em geral são apropriadas para classificação e regressão 2 algoritmos genéticos AG são métodos de busca e otimização que simulam os processos naturais de evolução Os AGs utilizam operadores de seleção cruzamento e mutação para desenvolver gerações de solução Essa técnica é apropriada para tarefas de clas sificação e segmentação 2 redes neurais RN são uma classe especial de sistemas que seguem analogia com funcionamento do cérebro humano já que são formados neurônios artificiais similares aos neurônios do ser humano Geralmente são apropriadas para as tarefas de classifica ção e segmentação BancosDadosindd 200 17102016 100002 201 Tecnologias Avançadas Técnicas de mineração de dados podem ser aplicadas a várias tarefas Segundo Fayad 1996 estas tarefas são 2 classificação a análise das características de um conjunto de dados de treinamento construindo um modelo para cada classe baseado nas características dos dados 2 associação consiste em identificar fatos que possam ocorrer numa mesma transação pode ser usada para avaliar alguma relação temporal entre os itens de um banco de dados 2 agregação também conhecida como clustering é utilizada para buscar similaridades entre os dados como agrupamento de classes ou categorias Ajuda a identificar grupos significativos que decompõem o sistema de grande escala em componentes menores 2 modelagem de dependência procura encontrar um modelo que mostre as dependências entre as variáveis 2 regressão procura por uma função que represente o comporta mento apresentado pelo fenômeno de um estudo Dá para prever valores futuros a partir de valores existentes 2 Sumarização envolve métodos para encontrar uma descrição compacta de um subconjunto de dados Segundo MSDN 2016 o modelo de mineração representa apenas uma parte de um processo que inclui desde perguntas sobre dados e criação de um modelo até respostas para as perguntas feitas e implantação do modelo em um ambiente de trabalho Esse processo é apresentado utilizando as seis etapas a seguir 2 definindo o problema 2 preparando dados 2 explorando dados 2 criando modelos 2 explorando e validando modelos BancosDadosindd 201 17102016 100002 202 Banco de Dados Figura 108 Implantando e atualizando modelos Fonte MSDN 2007 O diagrama indica um processo cíclico ou seja criar um modelo de mineração de dados é um processo dinâmico e repetitivo Depois de explorar os dados você pode descobrir que eles são insuficientes para criar modelos de mineração apropriados e que terá portanto que obter mais dados Ou você pode criar vários modelos e depois perceber que os modelos não respondem adequadamente o problema definido e que você deverá redefinilo Talvez seja necessário atualizar os modelos depois de eles serem implantados pois haverá mais dados disponíveis Cada etapa do processo pode precisar ser repetida muitas vezes para criar um bom modelo MSDN 2016 Os modelos de mineração podem ser se aplicados a cenários específi cos como 2 previsão estimando vendas prevendo cargas de servidor ou tempo de inatividade de servidor 2 risco e probabilidade escolhendo os melhores clientes para malas diretas determinando o ponto equilibrado provável para cenários de risco atribuindo probabilidades a diagnósticos ou outros resultados BancosDadosindd 202 17102016 100002 203 Tecnologias Avançadas 2 recomendações determinando quais produtos são mais prováveis de serem vendidos juntos gerando recomendações 2 localizando sequências analisando seleções de cliente em um carrinho de compras prevendo os próximos eventos prováveis 2 agrupamento separando clientes ou eventos em cluster de itens relacionados analisando e prevendo afinidades MSDN 2016 Saiba mais Faça o tutorial de Data mining do SQL Server em httpsmsdn microsoftcomptbrlibraryms167167vsql120aspx Síntese Neste capítulo foi apresentada uma introdução às técnicas avançadas que utilizam banco de dados Muitas dessas técnicas já são utilizadas em empresas de grande e médio porte mas podem ser aplicadas em empresas pequenas Foi apresentado BI com suas etapas e as tecnologias aplicadas entre elas temos o OLAP para criação e demonstração das informações obtidas nas aplicações de data warehouse e data mining Alguns SGBDs oferecem essas ferramentas embutidas na compra da fer ramenta outras oferecem em pacotes separados Os tutoriais sugeridos para o MS SQL Server auxiliam na melhor compreensão do funcionamento prático Da teoria à prática 2 Pesquise e explique qual a diferença entre OLAP x OLTP 2 Explique por que BI é importante para as empresas 2 Qual a importância da utilização de data warehouse 2 Qual a importância da utilização de data mining BancosDadosindd 203 17102016 100002 BancosDadosindd 204 17102016 100002 A disciplina de banco de dados está inserida no currículo de diversos cursos em áreas de conhecimento diferentes cada um abordandoa de acordo com seus interesses Por exemplo um bibliotecário preocupase em catalogar e disponibilizar dados de forma eficiente enquanto um biólogo se interessa em armazenar e consultar dados sobre suas pesquisas com proteínas e suas fun ções Para o pessoal da área da computação o interesse é diferente pois são eles os responsáveis pela análise projeto implementação e manutenção dos dados Conclusão BancosDadosindd 205 17102016 100002 206 Banco de Dados Quem trabalha na área de banco de dados atua em uma das três frentes principais construção de aplicativos para clientes utilizando um gerenciador de dados administração do gerenciador que é quem autoriza o uso dos diver sos conjuntos de dados armazenados para os diversos usuários setores eou funcionários da empresa e se responsabiliza pela manutenção do sistema em operação adequada e na construção dos gerenciadores de dados Por ser uma pessoa que trabalha há muito tempo com SGBDs e por ser totalmente apaixonada por essa área preparei o livro de forma que com o conhecimento adquirido neste estudo você esteja preparado para começar a trabalhar na área de banco de dados caso tenha interesse De qualquer forma o livro atende ao público em geral mesmo para quem vai seguir outra área e precise do conhecimento básico em bancos de dados Com o material apresentado você terá o conhecimento necessário para fazer análise e modelagem do banco de dados criação do banco no SGBD programação em SQL e planejamento de backups para garantir a segurança dos dados em caso de falha Aproveite para executar os tutoriais apresentados nas dicas e não esqueça das recomendações importantes como seguir padrão e rever todas as etapas com o cliente Este é apenas o começo da viagem a área de banco de dados é muito ampla e com muitas oportunidades de mercado Obrigada e bom estudo BancosDadosindd 206 17102016 100002 Gabarito BancosDadosindd 207 17102016 100003 208 Banco de Dados 1 Banco de dados 2 Confeitaria de pequeno porte utilizando sistema operacional Windows 10 Resposta SQL Server Express ou My SQL 2 Papelaria de médio porte utilizando sistema operacional Linux Ubuntu Resposta My SQL 2 Sistema de agendamento de pacotes de viagens com mais de 300 lojas no Brasil utilizando sistema Windows 81 Resposta SQL Server 2 Sistemas de geoprocessamento para análise da geografia do estado do Amazonas Resposta Oracle 2 Modelagem de Dados Este modelo é somente para referência não é obrigatório inserir os tipos de dados nem as chaves estrangeiras BancosDadosindd 208 17102016 100003 209 Gabarito 4 SQL 2 crie o Banco de dados CREATE DATABASE Exercicio ON PRIMARY NAME NExercicio FILENAME NC Program FilesMicrosoft SQL ServerMSSQL12 FAELMSSQLDATA Exerciciomdf SIZE 5120KB FILEGROWTH 1024KB LOG ON NAME NExerciciolog FILENAME NCProgram FilesMicrosoft SQL Server MSSQL12FAELMSSQLDATA Exercicio logldf SIZE 1024KB FILEGROWTH 10 2 crie a tabela Produto com suas restrições BancosDadosindd 209 17102016 100003 210 Banco de Dados Create table Produto CodProduto integer Constraint PKproduto primary key Nome Varchar10 not null Preco decimal82 not null Tipo varchar10 2 crie a tabela Cliente com suas restrições Create table Cliente CodCliente integer Constraint PKcliente primary key Nome varchar200 Cidade varchar 200 2 crie a tabela Pedidos com suas restrições BancosDadosindd 210 17102016 100003 211 Gabarito Create table pedido Pedido integer Constraint PKpedido pri mary key Cliente integer Constraint fkpedido Cliente foreign key Cliente references ClientecodCliente Produto integer Constraint fkpedidoPro duto foreign key Produto references Pro duto codProduto QTDE Integer not null ValorUnitario Decimal82 not null ValorTotal Decimal82 not null 5 SQL DML 2 Selecione o nome e o preço de todos os produtos R Select Nome Preço From Produto 2 Selecione o nome de todos os clientes que moram em Brasília R Select Nome From Cliente Where Cidade like Brasilia 2 Selecione o nome e o preço dos produtos que custam menos de R 100 ordenado pelo preço começando pelo menor valor R Select Nome Preco from Produto Where preco 100 Order by Preco 2 Selecione todos os clientes que tenha a substring ANA no nome R Select From Cliente Where Nome like ana BancosDadosindd 211 17102016 100003 212 Banco de Dados 6 SQL Avançado a Create database Teste Go Use Teste Go CREATE TABLE Cargo CodCargo smallint NOT NULL NomeCargo varchar50 NULL ValorCargo smallmoney NULL PRIMARY KEYCodCargo go CREATE TABLE Funcionario CPF numeric 11 NomeFuncionario varchar70 NOT NULL CodCargo Smallint Not NULL PRIMARY KEYCPF FOREIGN KEY CodCargo REFERENCES CARGO Cod Cargo GO INSERT CARGO CodCargo NomeCargo ValorCargo VALUES 1 Recepcionista 140000 INSERT CARGO CodCargo NomeCargo ValorCargo VALUES 2 Vendedor 120000 INSERT CARGO CodCargo NomeCargo ValorCargo VALUES 3 Faxineiro 105000 GO Povoando a tabela FUNCIONARIO INSERT FUNCIONARIO CPF NomeFuncionario Cod Cargo VALUES 11 João da Silva 1 INSERT FUNCIONARIO CPF NomeFuncionario Cod Cargo VALUES 12 Maria de Souza 2 INSERT FUNCIONARIO CPF NomeFuncionario Cod Cargo VALUES 13 Carla Brunni 3 GO BancosDadosindd 212 17102016 100003 213 Gabarito b Select NomeFuncionario nomeCargo From Funcionario F Inner join Cargo C On FCodCargo CCodCargo c Create view vwExercicio as Select NomeFuncionario nomeCargo From Funcionario F Inner join Cargo C On FCodCargo CCodCargo d Select From vwExercicio Order by nomeCargo desc BancosDadosindd 213 17102016 100003 214 Banco de Dados 7 Transações e Técnicas Avançadas Create procedure pExercicio CPF Numeric11 as Declare Salario Smallmoney Set Salario 0 Select Salario Salario From Aluno Where CPF CPF Begin Tran Transação Delete Aluno Where CPF CPF IF Salario 10000 Commit Else Rollback BancosDadosindd 214 17102016 100003 215 Gabarito 8 Recursos Avançados Create trigger TrExercicio on Aluno as SELECT I Getdate FROM INSERTED WHERE NOT EXISTS SELECT FROM DELETED WHERE INSERTEDCPF DELETED CPF SELECT U Getdate FROM INSERTED WHERE EXISTS SELECT FROM DELETED WHERE INSERTED CPF DELETED CPF SELECT D Getdate FROM DELETED WHERE NOT EXISTS SELECT FROM INSERTED WHERE DELETED CPF INSERTED CPF 9 Backup Restore Segurança e Otimização Resposta a Backup database dbAluno to disk d bancodbAlunobak with compresion BancosDadosindd 215 17102016 100003 216 Banco de Dados Resposta b Backup log dbAluno to disk dbanco dbAlunotrn with compresion Resposta c Restore database dbAluno from disk d bancodbAlunobak with norecovery Go Restore log dbAluno from disk d bancodbAlunotrn with recovery Go Resposta d USE master GO CREATE LOGIN IdTest WITH PASS WORDNPw0rd MUSTCHANGE DEFAULTDATABASEdbAluno CHECKEXPIRATIONON CHECKPOLICYON GO USE DbAluno GO CREATE USER IdTest FOR LOGIN IdTest GO USE DbAluno GO ALTER ROLE IdTest ADD MEMBER IdTest GO BancosDadosindd 216 17102016 100003 217 Gabarito 10 Tecnologias Avançadas Resposta a OLAP online Analytical Processing sistemas que permitem a análise da informação OLTP online Transaction Processing sistemas de processamento de transações ou seja sistemas transacionais onde são inseridas as operações das empresas OLTP é voltado para sistema de transações regras de negócio que são aplicadas no sistema por exemplo um sistema de supermercado São as compras e vendas de produtos e serviços das empresas OLAP é voltado para análise das informações ou seja cálculos e consultas mais complexas relatórios e gráficos sobre os dados opera cionais após o processamento e transformação Resposta b Com a concorrência das empresas e vários fatores modificando o mundo dos negócios é extremamente importante para a sobrevi vência das empresas se manterem atualizadas e conseguir rapidamente informações sobre o seu negócio e o comportamento dos clientes e do mercado para tomar decisões que irão direcionar o caminho que a empresa vai percorrer para crescer melhor Resposta c Permite tomar decisões embasadas em um histórico dos dados integrado Identifica tendências de forma a posicionar a empresa melhorando a competitividade ampliando lucros e minimizando os erros Como é um banco à parte do banco operacional não gera con corrência de utilização com o sistema transacional da empresa BancosDadosindd 217 17102016 100003 218 Banco de Dados Resposta d Os benefícios do DM variam desde ajudar a tomar decisões cor retas e mais rápidas apontar falhas nos sistemas e trazer mais lucros da empresa melhorando o Marketing Suas funções são descobrir as principais características dos consumidores melhorar o processo pro dutivo avaliar o risco detectar e prevenir fraudes acompanhar e ante cipar uma mudança no comportamento do mercado BancosDadosindd 218 17102016 100003 Referências BancosDadosindd 219 17102016 100003 220 Banco de Dados CHEN P Modelagem de dados a abordagem entidade relacionamento para projeto lógico São Paulo Makron Books 1990 DATE C J Introdução a sistemas de bancos de dados 7 ed Rio de Janeiro Campus 2000 ELSMARI R NAVATHE S B Sistemas de bancos de dados 6 ed São Paulo Pearson 2013 FAYYAD U M et al Advanced in knowledge discovery and data mining Cambridge AAAI press 1996 GOEBEL M GRUENWALD E A survey of data mining and knowledge SIGKDD Explorations v 1 n 1 1999 INMON W H Building the data warehouse Indianapolis Wiley 1998 KIMBALL R The data warehouse toolkit New York John Wiley Sons 2000 MEIRA F Sistemas de banco de dados São Paulo UFRGS 1997 Dispo nível em httpschasquewebufrgsbrpaulfisherapostilasbasdadunimar indexhtm Acesso em 20 ago 2016 MSDN Conceitos de mineração de dados 2016 Disponível em https msdnmicrosoftcomptbrlibraryms174949aspx Acesso em 30 ago 2016 Fundamentos e Modelagem de Bancos de Dados Multidimen sionais 2007 Disponível em httpsmsdnmicrosoftcomptbrlibrary cc518031aspx Acesso em 30 ago 2016 Guia de criação de índice do SQL Server Disponível em https msdnmicrosoftcomptbrlibraryjj835095vsql120aspx Acesso em 29 ago 2016a Microsoft Disponível em httpsmsdnmicrosoftcomptbr libraryms180169aspx Acesso em 28 ago 2016 Recuperar para um número de sequência de log SQL Server Disponível em httpsmsdnmicrosoftcomptbrlibraryms191459 aspx Acesso em 29 ago 2016b BancosDadosindd 220 17102016 100003 221 Referências PROCEDIMENTO armazenados Mecanismo de Banco de Dados Micro soft 2016 Disponível em httpsmsdnmicrosoftcomptbrlibrary ms190782aspx Acesso em 20 ago 2016 SELL D Uma arquitetura para business intelligence baseada em tecno logias semânticas para suporte a aplicações analíticas 2006 Florianópo lis Universidade Federal de Santa Catarina programa de pósgraduação em Engenharia de Produção 2006 SIBERCHATZ A KORTH H F SUDARSHAN S Sistemas de banco de dados 3 ed São Paulo Makron Books 1999 SILVA D C da Uma arquitetura de business intelligence para proces samento analítico baseado em tecnologias semânticas e em linguagem natural Florianópolis Universidade Federal de Santa Catarina 2011 TECHNET Estratégias de backup Disponível em httpstechnetmicro softcomptbrlibrarycc974133aspx Acesso em 29 ago 2016 BancosDadosindd 221 17102016 100003 BancosDadosindd 222 17102016 100003 BancosDadosindd 223 17102016 100003 BancosDadosindd 224 17102016 100003