Bases de Dados Relacionais: Implementação com MySQL by Grupo Lidel

9cm x 24cm

23,5mm

16,7cm x 24cm

9cm x 24cm

Orlando Belo O MySQL tornou-se num dos mais populares e poderosos sistemas de gestão de bases de dados relacionais atualmente disponíveis no mercado. Através desta ferramenta, este livro explora de uma forma abrangente e detalhada o desenvolvimento de sistemas de bases de dados relacionais, ao longo das suas principais etapas de implementação e de exploração.

Obra com conceitos, exemplos e exercícios que ajuda estudantes e profissionais a compreenderem os SGBDR e suas características. 2.ª edição com secções novas e novo capítulo sobre outros modelos.

CMY

Livro essencial e prático que aborda os princípios, conceitos e bases algorítmicas da computação gráfica, ligando-os a situações de aplicação concreta. Para alunos do ensino superior e profissionais.

Uma obra de referência para estudantes e profissionais que inclui os últimos avanços tecnológicos dos processadores comerciais, memórias e periféricos, em especial na arquitetura dos PC.

Através dos vários exemplos resolvidos, sumários da matéria abordada e exercícios teóricos e práticos, construa e desenvolva os seus programas em Python. Para estudantes e profissionais.

Nos vários capítulos, abordam-se as tarefas mais usuais que se desenvolvem no ambiente de um sistema de bases de dados relacional, dando-se particular atenção ao planeamento e implementação do seu esquema físico, à administração e gestão do seu repositório de dados, à definição de esquemas de segurança e de recuperação de dados, à exploração da informação que contém, através de queries simples e complexas, e ao desenvolvimento de scripts SQL para a implementação de procedimentos, funções e gatilhos, para a gestão de erros ou para a definição e controlo de transações. Ao longo da exposição e desenvolvimento, de forma gradual, de um exemplo de aplicação concreto, vai-se fornecendo o conhecimento essencial para compreender o que é uma base de dados relacional, qual a sua estrutura e serviços base e como é que pode ser criada e explorada para sustentar a operacionalidade de uma dada organização. Este livro destina-se a estudantes dos ensinos superior e profissional, bem como a profissionais de empresas de tecnologias da informação e da comunicação, que pretendam adquirir ou reforçar conhecimento no domínio das bases de dados relacionais, sustentado por uma abordagem teórico-prática robusta, acompanhada por um conjunto vasto de exercícios práticos, desenvolvidos na linguagem SQL, nas suas vertentes de descrição, manipulação e controlo de dados.

Capítulos estruturados de acordo com as várias etapas do processo de implementação de um sistema de bases de dados relacional. Exemplos de demonstração e de aplicação prática real compatíveis com o MariaDB. Principais temas abordados no livro: Implementação de bases de dados;

. . . . . . . . . . .

Povoamento e atualização de dados; Exploração básica e avançada de dados; Procedimentos, funções e gatilhos; Programação em MySQL.

Inclui: Exemplo de aplicação concreto; Conjunto vasto de exercícios práticos.

Material complementar em www.fca.pt, até o livro se esgotar ou ser publicada nova edição atualizada ou com alterações. ISBN 978-972-722-921-5

9 789727 229215

Doutorado em Informática – área científica de Inteligência Artificial Distribuída e Sistemas Multiagente – pela Universidade do Minho. Professor Associado com Agregação em Sistemas de Data Warehousing, no Departamento de Informática da Escola de Engenharia da Universidade do Minho. Licenciado em Engenharia de Sistemas e Informática pela mesma Universidade. As suas principais atividades de ensino e de investigação desenvolvem-se nos domínios científicos das bases de dados, data warehousing, processamento analítico e análise de dados. Atualmente, é membro do centro de investigação ALGORITMI da Universidade do Minho.

Edição FCA – Editora de Informática Av. Praia da Vitória, 14 A – 1000-247 Lisboa Tel: +351 213 511 448 fca@fca.pt www.fca.pt

Distribuição Lidel – Edições Técnicas, Lda. Rua D. Estefânia, 183, R/C Dto. – 1049-057 Lisboa Tel: +351 213 511 448 lidel@lidel.pt www.lidel.pt

Livraria Av. Praia da Vitória, 14 A – 1000-247 Lisboa Tel: +351 213 541 418 livraria@lidel.pt Copyright © 2021, FCA – Editora de Informática ® Marca registada da FCA PACTOR Editores, Lda. ISBN edição impressa: 978-972-722-921-5 1.ª edição impressa: setembro 2021 Paginação: Carlos Mendes Impressão e acabamento: Tipografia Lousanense, Lda. – Lousã Depósito Legal n.º 487178/21 Capa: José Manuel Reis Todos os nossos livros passam por um rigoroso controlo de qualidade, no entanto aconselhamos a consulta periódica do nosso site (www.fca.pt) para fazer o download de eventuais correções. Não nos responsabilizamos por desatualizações das hiperligações presentes nesta obra, que foram verificadas à data de publicação da mesma. Os nomes comerciais referenciados neste livro têm patente registada. Reservados todos os direitos. Esta publicação não pode ser reproduzida, nem transmitida, no todo ou em parte, por qualquer processo eletrónico, mecânico, fotocópia, digitalização, gravação, sistema de armazenamento e disponibilização de informação, sítio Web, blogue ou outros, sem prévia autorização escrita da Editora, exceto o permitido pelo CDADC, em termos de cópia privada pela AGECOP – – Associação para a Gestão da Cópia Privada, através do pagamento das respetivas taxas.

ÍNDICE GERAL AGRADECIMENTOS XI NOTA DO AUTOR 1

XIII

INTRODUÇÃO 1 SUMÁRIO......................................................................................................................................................................................... 1 1.1

ARMAZENAMENTO DE DADOS................................................................................................................................. 1

1.2

SISTEMAS DE GESTÃO DE BASES DE DADOS................................................................................................... 3

1.3

IMPLEMENTAÇÃO DE SISTEMAS DE BASES DE DADOS.............................................................................. 4

1.4

SISTEMA MYSQL.............................................................................................................................................................. 6 1.4.1

APRESENTAÇÃO GERAL................................................................................................................................. 6

1.4.2 INSTALAÇÃO DO MYSQL................................................................................................................................ 11 1.5

MYSQL COMMAND-LINE CLIENT.............................................................................................................................. 23 1.5.1

APRESENTAÇÃO GERAL................................................................................................................................. 23

1.5.2 UTILIZAÇÃO PRÁTICA DA FERRAMENTA............................................................................................... 25 1.6

MYSQL WORKBENCH...................................................................................................................................................... 26 1.6.1

CARACTERIZAÇÃO E SERVIÇOS................................................................................................................. 26

1.6.2 INSTALAÇÃO DA FERRAMENTA................................................................................................................. 27 1.6.3 ARRANQUE E UTILIZAÇÃO BÁSICA.......................................................................................................... 29 1.6.4 ORGANIZAÇÃO DO AMBIENTE DE TRABALHO.................................................................................... 34 RESUMO........................................................................................................................................................................................... 40 EXERCÍCIOS..................................................................................................................................................................................... 41

IMPLEMENTAÇÃO DE BASES DE DADOS

SUMÁRIO......................................................................................................................................................................................... 43 2.1

BASES DE DADOS RELACIONAIS............................................................................................................................. 43 2.1.1

SISTEMAS DE DADOS RELACIONAIS........................................................................................................ 43

2.1.2 ESTRUTURA DE UMA BASE DE DADOS RELACIONAL...................................................................... 44 2.1.3 A LINGUAGEM SQL............................................................................................................................................ 48 2.2 UMA BASE DE DADOS................................................................................................................................................... 50 2.3 IMPLEMENTAÇÃO DO SISTEMA DE DADOS........................................................................................................ 53 2.4 CRIAÇÃO DAS TABELAS DE UM ESQUEMA........................................................................................................ 60 2.5 ALTERAÇÃO E REMOÇÃO DE TABELAS................................................................................................................ 70 2.6 DEFINIÇÃO DE RELACIONAMENTOS....................................................................................................................... 79 2.7 GESTÃO DE ÍNDICES........................................................................................................................................................ 84 2.7.1 ÍNDICES E SUA UTILIDADE............................................................................................................................. 84 2.7.2 CRIAÇÃO DE ÍNDICES........................................................................................................................................ 86 2.7.3 REMOÇÃO DE ÍNDICES..................................................................................................................................... 88 2.8 UTILIZADORES E PRIVILÉGIOS.................................................................................................................................. 89 2.8.1 DEFINIÇÃO DE PRIVILÉGIOS.......................................................................................................................... 89 2.8.2 GESTÃO DE UTILIZADORES........................................................................................................................... 89 2.8.3 ATRIBUIÇÃO E REMOÇÃO DE PRIVILÉGIOS........................................................................................... 93 © FCA

VII

BASES DE DADOS RELACIONAIS: IMPLEMENTAÇÃO COM MYSQL

2.9 DIMENSÃO DE UMA BASE DE DADOS................................................................................................................... 96 2.9.1 DETERMINAÇÃO DO ESPAÇO EM DISCO................................................................................................. 96 2.9.2 CAPACIDADE DE ACOLHIMENTO DO MYSQL........................................................................................ 99 2.10 SEGURANÇA E RECUPERAÇÃO DE DADOS......................................................................................................... 100 2.10.1 CÓPIAS DE SEGURANÇA................................................................................................................................. 100 2.10.2 RECUPERAÇÃO DE DADOS............................................................................................................................ 106 2.11 GERAÇÃO DE ESQUEMAS LÓGICOS....................................................................................................................... 107 RESUMO........................................................................................................................................................................................... 113 EXERCÍCIOS..................................................................................................................................................................................... 113

POVOAMENTO E ATUALIZAÇÃO DE DADOS

115

POVOAMENTO DA BASE DE DADOS...................................................................................................................... 115

3.2 INSERÇÃO DE DADOS.................................................................................................................................................... 116 3.3 IMPORTAÇÃO DE DADOS............................................................................................................................................. 125 3.3.1 IMPORTAÇÃO DE DADOS DE FICHEIROS DE TEXTO.......................................................................... 125 3.3.2 IMPORTAÇÃO DE DADOS DE FICHEIROS XML...................................................................................... 128 3.3.3 IMPORTAÇÃO DE DADOS ASSISTIDA....................................................................................................... 130 3.4 INSERÇÃO DE DADOS COM SUBSTITUIÇÃO........................................................................................................ 133 3.5 MODIFICAÇÃO DE REGISTOS...................................................................................................................................... 137 3.5.1 MODIFICAÇÃO DE DADOS SOBRE UMA TABELA............................................................................... 137 3.5.2 MODIFICAÇÃO DE DADOS SIMULTÂNEA EM VÁRIAS TABELAS................................................. 142 3.6 ELIMINAÇÃO DE DADOS............................................................................................................................................... 143 3.6.1 REMOÇÃO DE DADOS SOBRE UMA TABELA........................................................................................ 143 3.6.2 REMOÇÃO DE DADOS SIMULTÂNEA EM VÁRIAS TABELAS......................................................... 145 RESUMO........................................................................................................................................................................................... 150 EXERCÍCIOS..................................................................................................................................................................................... 150

4 EXPLORAÇÃO BÁSICA DE DADOS

153

AMBIENTE DE EXPLORAÇÃO..................................................................................................................................... 153

4.2 INTERROGAÇÕES SIMPLES......................................................................................................................................... 155 4.3 UTILIZAÇÃO DE CRITÉRIOS DE FILTRAGEM........................................................................................................ 162 4.4 MANIPULAÇÃO DE VALORES DE DATAS E DE HORAS.................................................................................. 166 4.5 UTILIZAÇÃO DE FUNÇÕES E OPERADORES NUMÉRICOS ........................................................................... 170 4.6 ANÁLISE E MANIPULAÇÃO DE STRINGS.............................................................................................................. 172 4.6.1 FUNÇÕES PARA A MANIPULAÇÃO DE STRINGS................................................................................. 172 4.6.2 UTILIZAÇÃO DE CARACTERES ESPECIAIS: WILDCARDS................................................................. 176 4.7 INCORPORAÇÃO DE EXPRESSÕES REGULARES EM QUERIES.................................................................. 179 4.8 MANIPULAÇÃO DE VALORES NULOS..................................................................................................................... 182 4.9 ORDENAÇÃO DE RESULTADOS................................................................................................................................. 186 4.10 AGRUPAMENTO DE RESULTADOS........................................................................................................................... 189 4.10.1 OPERAÇÕES DE AGREGAÇÃO E DE AGRUPAMENTO....................................................................... 189 4.10.2 UTILIZAÇÃO DA CLÁUSULA GROUP BY................................................................................................... 190 4.10.3 UTILIZAÇÃO DO MODIFICADOR ROLLUP................................................................................................. 193 VIII

ÍNDICE GERAL

4.11 REDIRECIONAMENTO DE RESULTADOS................................................................................................................ 194 RESUMO........................................................................................................................................................................................... 201 EXERCÍCIOS..................................................................................................................................................................................... 202

5 EXPLORAÇÃO AVANÇADA DE DADOS

203

QUERIES DITAS COMPLEXAS..................................................................................................................................... 203

5.2 UTILIZAÇÃO DE SUBQUERIES.................................................................................................................................... 204 5.3 OPERAÇÕES DE JUNÇÃO DE DADOS..................................................................................................................... 212 5.3.1 COMBINAÇÕES DE DADOS............................................................................................................................ 212 5.3.2 PRODUTOS CARTESIANOS............................................................................................................................ 214 5.3.3 JUNÇÕES INTERNAS......................................................................................................................................... 216 5.3.4 OPERAÇÕES DE CONJUNTOS....................................................................................................................... 222 5.3.5 JUNÇÕES EXTERNAS........................................................................................................................................ 225 5.3.6 AUTOJUNÇÕES.................................................................................................................................................... 228 5.3.7 OUTRAS OPERAÇÕES DE JUNÇÃO............................................................................................................ 230 5.4 CRIAÇÃO E MANIPULAÇÃO DE VISTAS................................................................................................................. 233 5.4.1 VISTAS E SUA UTILIDADE.............................................................................................................................. 233 5.4.2 CRIAÇÃO E UTILIZAÇÃO DE VISTAS.......................................................................................................... 234 5.4.3 MODIFICAÇÃO E REMOÇÃO DE VISTAS.................................................................................................. 239 5.4.4 MANIPULAÇÃO DE DADOS COM VISTAS................................................................................................ 240 5.5 PROCESSOS DE PROCURA SOBRE TEXTOS........................................................................................................ 242 5.6 TABELAS DE SISTEMA.................................................................................................................................................. 247 5.7 HISTORIAIS DE EXECUÇÃO DE QUERIES............................................................................................................... 252 5.7.1 FICHEIROS DE LOG............................................................................................................................................. 252 5.7.2 LOG GERAL DO SISTEMA............................................................................................................................... 253 5.7.3 LOG DE ERROS.................................................................................................................................................... 257 RESUMO........................................................................................................................................................................................... 258 EXERCÍCIOS..................................................................................................................................................................................... 259

PROCEDIMENTOS, FUNÇÕES E GATILHOS

261

DESENVOLVIMENTO DE PROGRAMAS................................................................................................................. 261

6.2 IMPLEMENTAÇÃO DE PROCEDIMENTOS............................................................................................................... 263 6.2.1 CRIAÇÃO DE PROCEDIMENTOS ARMAZENADOS............................................................................... 263 6.2.2 PARÂMETROS DE ENTRADA E DE SAÍDA.............................................................................................. 268 6.2.3 REMOÇÃO E ALTERAÇÃO DE PROCEDIMENTOS................................................................................. 274 6.3 FUNÇÕES DEFINIDAS PELO UTILIZADOR............................................................................................................. 279 6.3.1 IMPLEMENTAÇÃO E EXECUÇÃO.................................................................................................................. 279 6.3.2 REMOÇÃO E ALTERAÇÃO DE FUNÇÕES.................................................................................................. 284 6.4 DESENVOLVIMENTO DE GATILHOS......................................................................................................................... 287 6.4.1 DEFINIÇÃO E IMPLEMENTAÇÃO.................................................................................................................. 287 6.4.2 ALTERAÇÃO E REMOÇÃO DE GATILHOS................................................................................................ 293 RESUMO........................................................................................................................................................................................... 296 EXERCÍCIOS..................................................................................................................................................................................... 296 © FCA

BASES DE DADOS RELACIONAIS: IMPLEMENTAÇÃO COM MYSQL

PROGRAMAÇÃO EM MYSQL

299

DESENVOLVIMENTO DE PROGRAMAS................................................................................................................. 299 7.1.1

ESTRUTURAS CONDICIONAIS....................................................................................................................... 299

7.1.2 IMPLEMENTAÇÃO DE CICLOS....................................................................................................................... 304 7.2 DETEÇÃO E MANIPULAÇÃO DE ERROS................................................................................................................. 310 7.3 UTILIZAÇÃO DE CURSORES......................................................................................................................................... 313 7.4 UTILIZAÇÃO DE TRANSAÇÕES.................................................................................................................................. 318 7.5 PREPARAÇÃO PRÉVIA DE INSTRUÇÕES............................................................................................................... 336 7.6 PROGRAMAÇÃO DE EVENTOS.................................................................................................................................. 340 7.6.1 EVENTOS E SUA UTILIDADE......................................................................................................................... 340 7.6.2 CRIAÇÃO DE EVENTOS.................................................................................................................................... 341 7.6.3 ALTERAÇÃO E ANULAÇÃO DE EVENTOS............................................................................................... 344 RESUMO........................................................................................................................................................................................... 345 EXERCÍCIOS..................................................................................................................................................................................... 346

BIBLIOGRAFIA 349 ÍNDICE REMISSIVO

353

1 INTRODUÇÃO SUMÁRIO Desde a década de 80, do século XX, que se verifica um aumento acentuado da influência e aplicação dos sistemas de bases de dados nos mais diversos domínios de aplicação. Conforme tal influência ia aumentando, novos sistemas de acolhimento e de gestão de bases de dados emergiam de forma bastante espontânea, acompanhando a evolução tecnológica dos sistemas de bases de dados, com funcionalidades e serviços de grande utilidade, e com grandes capacidades de adaptação e de trabalho. Os sistemas de gestão de bases de dados foram dos produtos de software que mais evoluíram nas últimas décadas, não só devido à popularidade e adoção dos sistemas de bases de dados, como também à grande concorrência que se verificava (e ainda se verifica) no mercado de software no qual estão inseridos. Após uma breve introdução sobre a forma como podemos armazenar informação em plataformas computacionais, abordaremos uma das peças de software mais sofisticadas para acolher e gerir grandes volumes de elementos de dados: os sistemas de gestão de bases de dados. Através de uma breve apresentação das suas características e funcionalidades gerais, introduzimos um desses sistemas, o MySQL, descrevendo e fundamentando a sua utilização, bem como algumas das suas principais ferramentas de trabalho.

1.1 ARMAZENAMENTO DE DADOS Dados, informação e conhecimento. Três dos pilares essenciais de qualquer processo de análise e de tomada de decisão. A ordem pela qual são referidos nem sempre é consensual. Pode variar de profissional para profissional. Porém, independentemente disso, raramente encontramos alguém que não reconheça a importância de cada um desses pilares em tudo aquilo que fazemos. De facto, no nosso quotidiano, sem nos apercebermos disso, estamos permanentemente a analisar variadíssimas situações e a tomar decisões sobre inúmeras coisas. Em todos esses casos, sustentamos as nossas ações com informação que vamos recolhendo ao longo das nossas vidas, com tudo aquilo que vamos fazendo. Hoje, poderíamos dizer que os nossos processos de análise de tomada de decisão estão mais facilitados, devido ao enorme manancial de fontes de informação que temos à nossa disposição. Isso é verdade, sem dúvida. A qualquer hora do dia e, praticamente, a partir de qualquer lugar, podemos aceder a uma enorme diversidade de dados, contidos em variadíssimas fontes de informação, através dos meios computacionais que hoje temos à nossa disposição – à distância de um simples “clique”, como vulgarmente ouvimos dizer. Todas essas fontes de informação, de uma forma ou de outra, foram povoadas das mais diferentes maneiras. Os dados que contêm podem ter sido inseridos por utilizadores, aplicações ou por sistemas de angariação de dados específicos. O enredo do processo de angariação e armazenamento de dados é, porém, bastante complexo e intrincado. Para além da angariação inicial dos dados, é necessário garantir, também, a sua atualização ao longo do tempo, uma vez que os dados tendem a variar ao longo do tempo, por diversos motivos e circunstâncias. Veja-se, por exemplo, o que acontece com a idade de uma pessoa, com o preço ou a disponibilidade de um produto, com a evolução de © FCA

BASES DE DADOS RELACIONAIS: IMPLEMENTAÇÃO COM MYSQL uma epidemia, ou com a informação meteorológica que recebemos diariamente. Todos estes elementos de dados são frequentemente utilizados por nós e por imensas aplicações distribuídas por esse mundo fora, praticamente numa base diária. Manter essa informação acessível e atualizada em repositórios de dados adequados exige meios específicos que garantam o seu armazenamento de forma adequada, bem como a sua atualização ao longo do tempo. Quando conjugamos estes vários serviços num só sistema acabamos por obter aquilo que podemos designar, em termos gerais, por um sistema de dados. Os sistemas de dados podem estar organizados de muitas e diferentes maneiras. Porém, a sua configuração mais vulgar incorpora dois componentes essenciais, que são os principais responsáveis pelo armazenamento e exploração dos dados que estes sistemas têm sob a sua alçada: a base de dados, usualmente constituída por um ou mais ficheiros de dados, com diferentes estruturas, que é responsável pela manutenção dos dados ao longo do tempo, e o sistema de gestão de bases de dados (SGBD), uma peça de software extremamente sofisticada, que assegura a execução e o controlo da realização de todas as operações que possam ser realizadas sobre o conteúdo da base de dados. Quando construímos uma base de dados, de facto, estamos a criar uma estrutura de armazenamento de informação bastante sofisticada, capaz de acolher elementos de dados bastante diversificados, organizados de uma forma bem concreta, para que o seu processamento e disponibilização sejam eficientes, rápidos e fiáveis. Hoje, as bases de dados organizam-se de acordo com modelos de dados muito versáteis, tanto em termos de acolhimento como em termos de relacionamento de dados. Estas características potenciam fortemente a implementação de variadíssimas aplicações, em domínios muito diversificados, como, por exemplo, o registo, a validação e a confirmação de matrículas em sistemas de ensino, o rastreamento de clientes e a gestão de promoções em sites de comércio eletrónico, o suporte operacional e a comunicação de mensagens em redes sociais, a análise e o diagnóstico em sistemas médicos, ou a gestão de reservas de viagens e de hotéis em empresas especializadas, entre muitas outras coisas. Além disso, orientando-se por esses modelos, nas bases de dados atuam mecanismos de processamento e de análise de dados muito poderosos, que são capazes de realizar inúmeras tarefas sobre os dados que as bases de dados contêm, como processos de procura envolvendo filtros de dados complexos ou operações de ordenação ou de agregação envolvendo mais do que um atributo. Todas estas características e funcionalidades tornam-se ainda mais impactantes, quando nos posicionamos em ambientes que suportam um grande número de utilizadores, envolvendo a execução de centenas ou milhares de operações de manipulação de dados por minuto. Nas instalações modernas de bases de dados, tudo isso é garantido pelos SGBD. Genericamente, é o SGBD que determina a forma como os dados deverão ser armazenados nos repositórios de dados. É ele que determina o modelo de dados a utilizar, bem como a linguagem com que podemos descrever e manipular a informação da base de dados. Adicionalmente, o SGBD gere e suporta tudo aquilo que se relaciona com os utilizadores do sistema e seus privilégios de acesso, a segurança e a recuperação da informação que está contida na base de dados, a garantia da consistência e controlo da redundância dos dados, entre muitas outras coisas. A adoção de um SGBD por parte de uma instituição contribui para o aumento da sua organização e sofisticação dos seus repositórios de dados, bem como propicia meios bastante efetivos para quebrar, de forma definitiva, eventuais focos de desordem nos seus ambientes de informação, tanto em termos de dados como de metadados.

2 IMPLEMENTAÇÃO DE BASES DE DADOS SUMÁRIO A implementação do esquema físico de uma base de dados no ambiente de um SGBD não é uma tarefa difícil. Contudo, requer que se faça previamente um trabalho detalhado e cuidado na identificação, caracterização e relacionamento de todos os elementos que o constituem. Usualmente, o resultado desse trabalho é materializado num documento de especificação próprio, vulgarmente reconhecido como dicionário de dados. Tendo esse documento disponível, a construção do esquema físico torna-se bastante simplificada. Neste capítulo, após uma breve introdução sobre sistemas de bases de dados relacionais, vamos construir de raiz uma base de dados relacional, explicando e demonstrando como se procede à construção do seu repositório de dados, das suas tabelas base e seus relacionamentos, à definição dos seus utilizadores e à criação e gestão de índices para melhorar o acesso aos diversos elementos da base de dados. Adicionalmente, veremos também como determinar o espaço de memória ocupado por uma base de dados e estabelecer medidas de segurança e de recuperação de dados para situações nas quais possam ocorrer erros ou falhas do sistema nas quais estão instaladas. Todo este trabalho será realizado com a ajuda do SGBD MySQL e de alguns dos seus programas utilitários e ferramentas.

2.1 BASES DE DADOS RELACIONAIS 2.1.1 SISTEMAS DE DADOS RELACIONAIS As bases de dados (Garcia-Molina, Ullman, & Widom, 2011; Gouveia, 2014) são um instrumento fundamental para todos aqueles que precisem de guardar informação, de uma forma permanente, e manipulá-la adequadamente de acordo com as suas necessidades. Nem sempre a posse de informação é, por si só, um ativo. De facto, para que o seja, necessita de mecanismos de manipulação, que permitam desenvolver processos de pesquisa e de análise que, de alguma forma, sejam vantajosos, que tragam valor acrescentado. Desde a década de 60 do século XX, muito trabalho tem sido desenvolvido no domínio das TIC com o objetivo de desenvolver sistemas que disponibilizem duas valências essenciais: armazenamento e manipulação de dados. Ao longo dos anos, vários modelos e sistemas de dados foram criados e desenvolvidos. Muitos deles desapareceram tão depressa quanto apareceram. Tiveram uma vida curta. Foram soluções que não captaram a atenção nem o interesse dos utilizadores, nem apresentaram características suficientemente atrativas para este ou para aquele domínio de aplicação. Usualmente, a sobrevivência das soluções desenvolvidas coincidia com a conjugação da oferta de serviços e características coincidentes com as necessidades correntes dos utilizadores ou com a capacidade das plataformas computacionais que as poderiam suportar. Mas, como sabemos, há sempre exceções. Por vezes, sucede que algumas das muitas soluções que vão emergindo ganham popularidade e atingem uma posição invejável no mercado. Foi o que aconteceu com os sistemas de bases de dados relacionais. © FCA

BASES DE DADOS RELACIONAIS: IMPLEMENTAÇÃO COM MYSQL A emergência dos sistemas de bases de dados relacionais remonta à década de 70 do século XX, quando E. F. Codd, um investigador da IBM, propôs pela primeira vez as bases do modelo de dados relacional (Codd, 1970, 1990). Apesar de um outro investigador, D. L. Childs, um pouco antes, em 1968 (Childs, 1968), já ter apresentado um modelo de dados similar, orientado ao conjunto, foi o modelo criado por E. F. Codd que fez nascer e desenvolver aquilo que hoje reconhecemos como as bases de dados relacionais. Desde o momento em que foi proposto, o modelo de dados relacional tem-se mantido praticamente na sua forma original. Muitos trabalhos foram realizados durante as décadas seguintes ao seu aparecimento, reforçando a sua teoria e definindo novas propriedades e extensões. Porém, a estrutura e os elementos base do modelo de dados relacional continuam a basear-se e a seguir de perto aquilo que E. F. Codd propôs, o mesmo acontecendo com os princípios relativos à independência de dados (lógica e física), à garantia de consistência e ao controlo da redundância dos dados. Hoje, os sistemas relacionais sustentam grande parte da oferta das soluções para armazenamento e análise de dados. Os SGBD relacionais continuam a ser as ferramentas preferidas por uma grande maioria dos utilizadores de bases de dados. Isso deve-se, em grande parte, ao conhecimento generalizado do modelo de dados relacional na comunidade das TIC, ao excelente desempenho que os sistemas relacionais têm demonstrado em aplicações intensivas de processamento de dados e à segurança e fiabilidade que têm revelado ao longo dos últimos anos nos mais variados domínios de aplicação.

2.1.2 ESTRUTURA DE UMA BASE DE DADOS RELACIONAL Uma base de dados relacional é um repositório de dados estruturado, cujos elementos estão organizados de acordo com o modelo de dados relacional. Todos os elementos de uma base de dados relacional são definidos e mantidos num único tipo de estrutura de dados: a tabela. Uma tabela é uma estrutura de dados matricial, constituída por um dado conjunto de colunas e de linhas. As colunas representam os atributos da tabela (as suas propriedades) e as linhas representam as instâncias de dados (os registos). Usualmente, uma tabela é definida para acolher uma peça de informação específica, com características bem definidas. As tabelas podem representar entidades (e.g., livros, vendas, clientes, profissões) ou relacionamentos (e.g., livros vendidos numa venda, vendas de um cliente, profissões de um cliente). Normalmente, estes últimos são utilizados para estabelecer elos de ligação entre uma ou mais entidades. Independentemente dessa classificação, uma base de dados relacional resume-se, de facto, a um conjunto de tabelas, que podem estar (ou não) relacionadas entre si. Uma tabela deve ter um identificador próprio, que deve ser único no contexto da base de dados na qual foi criada, um conjunto de atributos (não nulo), que constitui o esquema da tabela e define as suas propriedades específicas, e um conjunto de registos (que pode ser vazio) que integra os elementos de dados dessa mesma tabela. O número de atributos que integram o esquema de uma tabela designa-se por grau, enquanto o número dos seus registos por cardinalidade. À interseção de uma coluna (atributo) com uma linha (registo) atribui-se a designação de célula. Uma célula deve conter apenas um único valor, um valor atómico. A forma (ordem) como os atributos e os registos aparecem numa relação não tem qualquer relevância em termos de modelo. Os atributos de uma tabela caracterizam as instâncias do objeto de dados que representam. Numa mesma tabela, os atributos devem ter um identificador único. Os valores dos atributos são definidos de acordo com um dado domínio (tipo de dados), que determina quais os valores que o atributo pode receber, e um conjunto de restrições, que regula a forma como esses valores devem ser validados. 44

3 POVOAMENTO E ATUALIZAÇÃO DE DADOS SUMÁRIO O povoamento de uma base de dados é um processo essencial na garantia da sua utilidade. Não é um processo pontual, mas algo que vai ocorrendo, gradualmente, ao longo da vida de uma base de dados. Para que seja possível iniciar a exploração de uma base de dados, bem como o desenvolvimento de algumas das suas transações essenciais, é necessário que as suas tabelas base contenham a informação necessária para sustentar tais operações. Assim, neste capítulo, revelamos como realizar as operações mais comuns de inserção, modificação e remoção de dados num ambiente de um SGBD. Adicionalmente, revelamos também como alimentar as tabelas de uma base de dados com informação, em diversos formatos, provenientes de fontes de informação externas. Todas estas operações são essenciais para o “bem-estar” de uma base de dados, devendo ser realizadas de forma cuidada para que seja possível garantir a qualidade dos seus dados e, consequentemente, satisfazer as necessidades de informação dos utilizadores com dados fiáveis.

3.1 POVOAMENTO DA BASE DE DADOS Habitualmente, o povoamento de uma base de dados é um processo gradual, que se realiza ao longo da vida operacional do sistema de bases de dados. Os dados relativos aos vários objetos que a base de dados integra vão sendo progressivamente inseridos nas suas tabelas, de acordo com esta ou aquela operação, realizada por um determinado processo de negócio, ativado por um ou mais utilizadores. Não é, de facto, um processo de uma só ação, de um só utilizador. Para que seja possível utilizar a base de dados que criámos no capítulo anterior e, consequentemente, iniciar a sua exploração, é necessário alimentar algumas das suas tabelas, em particular, aquelas que irão sustentar a atividade principal da LLonRoad: a venda de livros. Se observarmos, de novo, o esquema da base de dados de trabalho (Secção 2.2), facilmente verificamos que, para fazermos o registo de uma venda de livros precisamos de ter, a priori, alguma informação já armazenada na base de dados, nomeadamente aquela que está relacionada com clientes, funcionários, viaturas, localidades e, obviamente, livros. Nada mais do que os elementos de dados que garantem a consistência de um registo de uma venda. Todos os elementos de dados referidos pertencem a tabelas de referência que sustentam as chaves estrangeiras, respetivamente, Cliente, Funcionario, Viatura e Localidade, da tabela Vendas, e Livro na tabela VendasDetalhes. Assim, sem seguirmos qualquer critério especial de inserção de dados, a não ser o de garantir que se tenham disponíveis os valores necessários para as diversas chaves estrangeiras envolvidas no registo de uma venda, na Secção 3.2, vamos iniciar a apresentação e discussão do povoamento da base de dados LLonRoad. Todas as operações que iremos realizar de seguida serão desenvolvidas, executadas e analisadas no ambiente de interrogação do MySQL Workbench (Secção 1.6). Na © FCA

115

BASES DE DADOS RELACIONAIS: IMPLEMENTAÇÃO COM MYSQL generalidade das tarefas que vamos executar ao longo deste capítulo, iremos utilizar preferencialmente a linguagem SQL, editando e executando os scripts que vamos desenvolvendo no painel de edição do MySQL Workbench. Todavia, em situações esporádicas, ilustraremos também a execução de algumas das instruções que desenvolveremos no MySQL Command-Line Client.

3.2 INSERÇÃO DE DADOS Usualmente, em SQL, o povoamento de uma tabela de uma base de dados é realizado através da instrução INSERT. Esta instrução permite inserir dados numa tabela de acordo com a sua estrutura base e com as restrições que nela estejam definidas. A instrução INSERT envolve sempre a referência de uma tabela da base de dados, na qual pretendemos inserir os novos dados, e a indicação dos valores a inserir. INSERT [INTO] <tabela> [(<atributo1>,(<atributo2>,...)] VALUES (<valor1>,<valor2>,...);

Opcionalmente, podemos também apresentar um esquema de atributos ([(atributo,...)]) da tabela para indicar a ordem dos atributos pela qual queremos introduzir os seus valores. Apesar de ter um leque de opções um pouco diversificado, esta instrução não é complicada. Porém, na prática, a sua utilização restringe-se, frequentemente, a um conjunto de casos que têm grande utilização prática. De seguida, iremos apresentar e discutir cada um desses casos. Sempre que acharmos útil ou necessário introduziremos uma nova opção de configuração, explicando-a dentro do contexto aplicacional em causa. Vamos iniciar o povoamento da nossa base de dados pela tabela Livros. Esta tabela é, provavelmente, a tabela mais relevante no modelo de negócio subjacente à nossa base de dados de trabalho. Como podemos ver pelo seu esquema, é uma tabela de grau nove (número de atributos da tabela), constituída, respetivamente, pelos atributos Id, Titulo, Editora, Lingua, Categoria, Stock, StockViaturas, PrecoUnitario e Observacoes (Figura 3.1). Através do esquema apresentado, podemos ver que a tabela Livros se relaciona diretamente com quatro outras tabelas, nomeadamente Categorias, Linguas, Editoras e LivrosAutores. Por sua vez, esta última tabela está relacionada com a tabela Autores. Os diversos relacionamentos estabelecidos entre estas tabelas estão assegurados, respetivamente, através das chaves estrangeiras incluídas nas tabelas Livros (Editora, Lingua e Categoria) e LivrosAutores (Autor), que foram definidas anteriormente no Capítulo 2. A existência de chaves estrangeiras numa tabela deve ser tomada em consideração quando pretendemos realizar uma operação de inserção, atualização ou remoção de dados que envolvam dados em tabelas de referência. Como vimos anteriormente, a definição de uma chave deste tipo numa tabela instrui o SGBD que, durante a realização das operações referidas, deve verificar se o valor inserido no atributo que é chave estrangeira está definido (ou não) na correspondente tabela de referência (parent table). Caso esse valor não se encontre inserido na tabela de referência, o SGBD emitirá uma mensagem de erro, alertando o utilizador para a ocorrência de uma quebra de integridade referencial. Consequentemente, inviabilizará a realização da operação solicitada. Todavia, se não atribuirmos qualquer valor ao atributo da chave estrangeira e este permitir a admissão de valores nulos, o processo de verificação de integridade referencial não será despoletado. No nosso caso, vamos considerar que essa restrição (NULL) não foi concedida. Ao introduzirmos um novo registo na tabela Livros, teremos de assegurar que os valores que vamos inserir nos seus atributos da chave estrangeira 116

4 EXPLORAÇÃO BÁSICA DE DADOS SUMÁRIO Numa organização, a existência de uma base de dados significa que, por qualquer motivo, houve, em algum momento, a necessidade de armazenar informação sobre um ou mais domínios aplicacionais, com o objetivo de suportar as suas atividades operacionais e de tomada de decisão. Porém, para que esse suporte exista, para além da base de dados, é necessário ter a capacidade e os meios para fazer a sua exploração. Neste capítulo, vamos aprender como fazer a exploração de uma base de dados, abordando alguns casos de aplicação simples, mas pertinentes e úteis em muitas situações práticas reais. Veremos como realizar consultas diretas sobre o conteúdo de uma tabela, aplicar um conjunto de filtros para selecionar dados específicos ou estabelecer critérios de análise entre dois limites de valores. Por fim, estudaremos também como estabelecer agregados de valores e redirecionar os resultados de uma query para uma fonte de dados externa.

4.1 AMBIENTE DE EXPLORAÇÃO A exploração da informação contida numa base de dados é uma operação essencial. É importante que se tenha a possibilidade de manipular – consultar, inserir, atualizar ou remover – a informação contida numa dada base de dados, para que esta possa ser utilizada em benefício do nosso trabalho e justificar, obviamente, o esforço despendido na sua construção e consequente povoamento. Hoje, no domínio dos sistemas de bases de dados, temos acesso a uma panóplia muito diversificada de ferramentas computacionais que, de uma forma simples e rápida, nos permitem explorar o conteúdo de um sistema de dados, independentemente do seu tipo ou do seu construtor. De facto, não é preciso possuir um grande conhecimento sobre sistemas de bases de dados para se poder utilizar tais ferramentas. Para isso, basta ter um conhecimento sólido de utilização e exploração de plataformas computacionais. De qualquer maneira, achamos que é importante que os utilizadores de um sistema de bases de dados, independentemente do seu perfil de utilização, compreendam e saibam aplicar de forma efetiva a linguagem SQL em operações de manipulação de dados. Tal torna-se particularmente relevante para todos aqueles que pretendam conceber um sistema de bases de dados. A SQL é reconhecida nos dias de hoje como a linguagem padrão para a manipulação de bases de dados, sendo utilizada praticamente em todos os sistemas de gestão de bases de dados atuais. Na preparação e execução dos nossos exemplos de queries, vamos utilizar como base de trabalho o ambiente principal do MySQL Workbench (Figura 4.1), tal como fizemos anteriormente aquando da criação do esquema da base de dados e do consequente povoamento das suas tabelas. Apesar de podermos fazer este trabalho utilizando o MySQL Command-Line Client, o MySQL Workbench facilita imenso a sua realização, dado o grande número de serviços e funcionalidades que coloca à nossa disposição para editar, validar, executar e analisar as queries que formos realizando ao longo deste © FCA

153

BASES DE DADOS RELACIONAIS: IMPLEMENTAÇÃO COM MYSQL capítulo. Contudo, sempre que for oportuno e pertinente, demonstraremos um ou outro exemplo de query utilizando o MySQL Command-Line Client. Antes de começarmos a preparar as primeiras queries de demonstração, relembremos a forma como está organizado o ambiente de trabalho que vamos utilizar e quais os elementos desse ambiente que nos podem ser úteis no desenvolvimento das queries de consulta.

Instruções SQL

Tabelas da base de dados de trabalho

Texto de ajuda sobre a instrução em edição

Metadados de uma tabela

Mensagens do sistema

FIGURA 4.1 – AMBIENTE DE TRABALHO DO MYSQL WORKBENCH PARA EDIÇÃO E EXECUÇÃO DE QUERIES

Na Figura 4.1, podemos ver o ambiente de trabalho do MySQL Workbench, no qual está evidenciado o painel de edição de scripts SQL. Como já referimos, este painel será a principal área de trabalho, uma vez que é nele que iremos desenvolver todos os scripts SQL orientados para a exploração dos dados armazenados no nosso sistema. Todavia, os restantes painéis disponíveis no MySQL Workbench também serão muito úteis no suporte ao desenvolvimento das nossas consultas, em particular: ■

Painel de navegação de esquemas – que utilizaremos para aceder às várias bases de dados disponíveis no sistema, bem como aos metadados de todos os elementos que estão integrados em cada uma delas;

■

Painel de resultados – que nos revelará os resultados das queries que executarmos;

■

Painel de mensagens – que nos comunicará as mensagens de execução das queries e das mensagens de alerta ou de erro que o sistema possa emitir.

No painel de edição do ambiente da Figura 4.1 podemos ver duas instruções SQL específicas. De referir, a instrução USE LLonROAD, que foi utilizada para indicar ao sistema qual a base de dados com que queríamos trabalhar, e a instrução SELECT * FROM Livros, que foi executada para obter o conteúdo da tabela Livros, os seus elementos de dados. Logo abaixo desse painel, podemos ver os resultados produzidos pela execução das queries referidas e as mensagens emitidas pelo MySQL para reportar a execução dessas instruções. Sabendo utilizar estes painéis, os seus serviços e as funcionalidades gerais do MySQL Workbench, bem como interpretar as mensagens emitidas pelo sistema no 154

5 EXPLORAÇÃO AVANÇADA DE DADOS SUMÁRIO A exploração da informação contida num sistema de bases de dados nem sempre se revela simples e traduzível em queries de conceção básica. Muitas das necessidades dos utilizadores impõem a combinação de dados que usualmente estão armazenados em diferentes tabelas. Neste capítulo vamos ver como poderemos realizar processos de combinação de dados entre diferentes objetos de uma base de dados (tabelas ou vistas), aplicando diversos tipos de operações de junção de dados em processos de interrogação sofisticados, integrando em alguns casos vistas e índices full-text definidos pelos utilizadores, para melhorar a pesquisa de valores em atributos que contenham conjuntos de strings de grandes dimensões. Por fim, veremos como utilizar os dados contidos nas tabelas e nos historiais (logs) de sistema em processos regulares de exploração de dados.

5.1 QUERIES DITAS COMPLEXAS A grande generalidade das queries realizadas e lançadas por um utilizador regular numa aplicação convencional de bases de dados podem ser consideradas como queries simples. Porém, em alguns serviços de exploração de dados, a informação que pretendemos obter de uma base de dados nem sempre pode ser conseguida através da aplicação direta de uma query que possua uma simples lista de atributos, que foi selecionada sobre uma dada tabela, que aplique um ou dois filtros de dados, e, por fim, que ordene e limite o conjunto de registos obtidos. De facto, alguns utilizadores, em especial aqueles que desempenham alguma função ou serviço relacionado com as atividades de análise de dados de negócio, têm necessidades de exploração bem mais complicadas, que envolvem frequentemente a conjugação de dados de várias tabelas, com operações de seleção e ordenação de resultados, não raramente envolvendo também a aplicação simultânea de vários critérios de filtragem ou de agrupamento de dados. Por exemplo, se imaginarmos uma situação prática de negócio na qual um dos gestores da nossa base de dados quisesse saber, em determinado momento, quais são os cinco livros que mais venderam até hoje na LLonRoad, apresentados por ordem decrescente, pelo valor das suas vendas, como é que satisfaríamos esta necessidade de negócio através de uma “simples” query? Em termos de interrogação à base de dados a solução a essa pergunta não é muito complicada. De facto, qualquer utilizador de um sistema de bases de dados, possuidor de um conhecimento sólido de SQL, rapidamente consegue idealizar e implementar uma query para fornecer a informação solicitada pelo referido gestor. Veja-se, por exemplo, a solução apresentada em SQL 5.1. Nesta proposta de solução podemos ver a definição de uma query complexa, envolvendo uma pequena série de “ingredientes” bem interessantes, nomeadamente uma seleção específica de atributos, a aplicação de critérios de filtragem (WHERE), o ordenamento de resultados (ORDER BY), a aplicação de um agrupamento de valores (GROUP BY), a combinação de dados de diferentes tabelas (INNER © FCA

203

BASES DE DADOS RELACIONAIS: IMPLEMENTAÇÃO COM MYSQL JOIN), a limitação de resultados (LIMIT), entre outras coisas. Os resultados da execução da query apresentada em SQL 5.1 podem ser observados na Figura 5.1. Ao analisarmos a estrutura da query apresentada, facilmente reparamos que é bastante mais complexa do que qualquer uma daquelas que apresentámos anteriormente e que a sua definição envolve um conhecimento mais profundo e uma boa perícia na utilização e aplicação da linguagem SQL. -- Determinação dos cinco livros mais vendidos até hoje (top 5), da categoria policial, -- apresentados por ordem decrescente pelo valor das suas vendas. -SELECT LI.Id, LI.Titulo, SUM(VD.Valor) AS Vendas FROM Livros AS LI INNER JOIN ( SELECT ID, Designacao FROM Categorias WHERE Designacao = 'Policial') AS CA ON LI.Categoria=CA.Id INNER JOIN VendasDetalhes AS VD ON LI.id = VD.Livro GROUP BY LI.Id ORDER BY SUM(Valor) DESC LIMIT 5;

SQL 5.1 – EXEMPLO DE UMA QUERY COMPLEXA

Além disso, neste exemplo vemos também algum do enorme potencial da SQL em processos de interrogação de dados. Na realidade, sabendo lidar com modelos de dados relacionais e com o conjunto de instruções mais relevantes da SQL, temos acesso a um espaço de análise gigantesco, no qual uma qualquer interrogação de dados complexa pode ser mapeada por uma “simples” instrução SQL e satisfeita de forma rápida e efetiva. Se associarmos a estes elementos a perícia dos utilizadores neste domínio, bem como a sua capacidade para articular as instruções de forma adequada e correta, facilmente obtemos uma ferramenta muito poderosa para lidar com processos de exploração e análise de dados.

FIGURA 5.1 – EXECUÇÃO DA QUERY “TOP 5 CLIENTES” (SQL 5.1)

Neste capítulo, vamos abordar novas formas de exploração de dados, mais complexas e poderosas, nas quais vamos desenvolver queries que nos permitem alcançar soluções mais sofisticadas e efetivas, de forma a poder suportar processos de análise de dados mais exigentes. Para tal, vamos apresentar e explorar, de forma gradual, um conjunto de novas instruções, bem como a sua parametrização, que nos irão permitir realizar consultas de dados bastante úteis e interessantes, mesmo quando aplicadas à base de dados de exemplo. Assim, iremos ver como combinar dados provenientes de diferentes tabelas, desenvolver subqueries (queries aninhadas) para melhorar o desempenho das queries, ordenar ou agrupar resultados de forma a propiciar relatórios de dados mais sintéticos e legíveis, ou criar vistas (views) como forma de simplificar o processo de elaboração de queries complexas.

5.2 UTILIZAÇÃO DE SUBQUERIES O nosso estudo sobre a elaboração de queries mais avançadas vai começar com a conceção e implementação de subqueries, isto é, a utilização de instruções SQL dentro de outras instruções SQL, 204

6 PROCEDIMENTOS, FUNÇÕES E GATILHOS SUMÁRIO No seio de um sistema de bases de dados, é usual realizarem-se algumas operações de exploração e de manipulação de dados de forma repetitiva. Para evitar a repetição da construção dos scripts SQL que normalmente asseguram operações como essas, os administradores de bases de dados e os programadores de aplicações-cliente costumam guardar esses scripts em sistemas de ficheiros específicos nos sistemas operativos ou implementá-los, de forma permanente, no próprio ambiente do SGBD. Hoje, os SGBD mais poderosos disponibilizam linguagens de programação específicas – habitualmente baseadas em extensões à linguagem SQL padrão –, para fazerem a implementação desses scripts sob a forma de programas – procedimentos armazenados, funções ou gatilhos –, que são especificamente desenvolvidos à medida, de acordo com as funcionalidades e características de cada SGBD. Neste capítulo iremos explicar como construir, gerir e implementar esses programas em MySQL, utilizando um conjunto de casos de estudo bastante diversificado, com grande utilização prática em domínios de aplicação do mundo real.

6.1 DESENVOLVIMENTO DE PROGRAMAS As atividades empresariais nem sempre seguem o mesmo padrão de desenvolvimento nas suas atividades de programação. Esporadicamente, no seio de uma empresa, surge a necessidade de se desenvolverem novas ações despoletadas por eventos muito diversos, que podem ser originados, por exemplo, por rearranjos na categorização da carteira de clientes, implementação de novos processos de fabrico, aplicação de um novo procedimento de qualidade. Assim, por vezes, não é possível garantir o suporte operacional a todas essas novas atividades, pelo simples motivo de que as aplicações e programas operacionais existentes não estão simplesmente preparados para isso. Não raras vezes, tais situações são provocadas por novas necessidades, eventualmente criadas por novos processos que a empresa decidiu implementar para responder a uma nova exigência do mercado no qual está inserida. Isto pode parecer um pouco estranho, mas, de facto, até é muito comum. Porém, não é só em processos ditos empresariais que estas situações acontecem. Também os sistemas de bases de dados que garantem a preservação e a manipulação dos seus dados são afetados por elas. O quotidiano de um administrador de um sistema de bases de dados pode requerer, também, a implementação de novos serviços de processamento de dados, quer ao nível do próprio servidor de bases de dados, quer ao nível das suas aplicações-cliente. Hoje, os principais sistemas de bases de dados disponíveis no mercado apresentam um nível tecnológico de tal forma avançado e uma carteira de serviços tão grande e diversificada, que praticamente todas as atividades mais comuns de processamento de dados estão, por demais, garantidas. Na realidade, toda esta enorme “bagagem” de serviços é um exagero para a maioria das organizações, já que grande parte desses serviços raramente, ou mesmo nunca, é utilizada. Mas esse é o “preço” a © FCA

261

BASES DE DADOS RELACIONAIS: IMPLEMENTAÇÃO COM MYSQL pagar pela oferta de ferramentas de aplicação genérica, que cobrem vários campos de aplicação. Isto foi algo que os produtores de sistemas de gestão de software adotaram para garantir que o tempo e o esforço de adaptação do seu produto a um novo contexto organizacional fossem mínimos, entre outros motivos. Desta forma, deveria ser fácil, fosse qual fosse o problema, encontrar uma solução válida nesse leque de serviços tão grande e diversificado. E, de facto, até é. Mas não através de uma aplicação específica que uma dada ferramenta providencia, uma vez que as características de novos problemas emergentes só aparecem depois da disponibilização da ferramenta de gestão de bases de dados. Estas questões, porém, não são novas. Novas potencialidades trazem novos campos de aplicação e, consequentemente, novos problemas para resolver. Para estes casos, os SGBD mais poderosos disponibilizam uma linguagem de programação específica – uma extensão à linguagem SQL padrão – para que os administradores de bases de dados ou os programadores de aplicações-cliente possam desenvolver os seus próprios programas, especificamente desenvolvidos à medida e de forma compatível com as funcionalidades e características do SGBD que a empresa adotou, instalou e tem em exploração. Como sabemos, a SQL (2006) é uma linguagem declarativa que foi concebida e desenvolvida especificamente para o desenvolvimento de serviços de interrogação em sistemas de bases de dados relacionais. Não é, pois, uma linguagem de programação imperativa, não tendo na sua base de conceção qualquer tipo de instrução orientada, em particular, para o desenvolvimento de programas. Porém, desde cedo que os fabricantes de software para sistemas de bases de dados relacionais decidiram estender a linguagem SQL “à sua maneira”, acrescentando-lhe muitas outras funcionalidades, incluindo novas instruções típicas de linguagens de programação, para a implementação de estruturas condicionais ou de controlo de fluxo, por exemplo. Desta forma, cada fabricante acabou por desenvolver a sua própria SQL, o seu próprio dialeto, provendo a linguagem SQL com uma bagagem de funcionalidades bastante específicas. Isto fez com que emergissem diversos dialetos da SQL, cada um com características muito próprias, mas ao mesmo tempo bastante semelhantes, dada a incorporação nas suas próprias definições da linguagem SQL padrão. Apesar das vantagens óbvias de iniciativas como estas, também é verdade que tal diversidade de dialetos SQL veio complicar um pouco a portabilidade dos sistemas de bases de dados implementados. Todavia, por outro lado, permitiu aos administradores de sistemas de bases de dados abranger novos domínios de aplicações e de serviços, como o desenvolvimento de “pequenos” programas, com base no seu próprio dialeto SQL, de acordo com as suas necessidades de administração ou de interrogações de dados dos processos de negócio da própria organização. No caso do MySQL, a linguagem SQL implementa SQL/Persistent Stored Module (SQL/PSM, 2020; Harrison e Feuerstein, 2008), um padrão ISO, que transformou a SQL numa linguagem procedimental que pode ser usada na criação e manutenção de procedimentos armazenados (stored procedures). Com a SQL/PSM podemos definir e utilizar variáveis, definir expressões de cálculo e atribuir o seu resultado a uma variável, criar estruturas de condicionais e de controlo de fluxos e utilizar cursores para tratamento em memória de resultados de queries, entre outras possibilidades. Na realidade, as funcionalidades que a linguagem do MySQL nos coloca à disposição permitem-nos criar e desenvolver programas muito interessantes, que depois são mantidos e executados no seio do próprio MySQL. Apesar da sua reconhecida utilidade, a utilização de procedimentos armazenados não é algo consensual no meio da comunidade dos administradores dos SGBD, gerando algumas discussões acerca do seu uso. Porém, aqui não iremos discutir essa problemática. Apenas iremos mostrar como se podem criar, manter e executar procedimentos armazenados em MySQL, deixando o leitor avaliar a sua utilidade, vantagens e desvantagens em futuras aplicações.

262

7 PROGRAMAÇÃO EM MYSQL SUMÁRIO O desenvolvimento de programas específicos – procedimentos armazenados, funções ou gatilhos – num sistema de bases de dados é uma atividade regular. Por vezes, a sua implementação alcança patamares de complexidade e sofisticação elevados, requerendo modos de programação e estruturas de dados avançadas na sua construção. Seguindo uma linha de exposição e de trabalho similar à do capítulo anterior, neste capítulo vamos avançar um pouco mais na conceção e implementação de programas no ambiente de um sistema de bases de dados, implementando estruturas condicionais, ciclos de execução, instruções para a deteção e tratamento de erros e instruções preparadas. Estas operações permitirão desenvolver programas bastante avançados. Veremos, também, como utilizar estruturas de dados avançadas, como os cursores e as tabelas temporárias, e implementar transações dentro de procedimentos armazenados. Terminaremos a nossa abordagem ao desenvolvimento de programas, ensinando como construir e gerir eventos dentro do ambiente de um sistema de bases de dados.

7.1 DESENVOLVIMENTO DE PROGRAMAS No capítulo anterior, vimos como podíamos desenvolver pequenos blocos de programas – procedimentos, funções e gatilhos – para realizar algumas operações que, de uma forma ou de outra, são executadas regularmente com vista à satisfação deste ou daquele requisito operacional do sistema. Os programas que apresentámos até ao momento, quer tenham sido procedimentos, funções ou gatilhos, foram bastante simples, não tendo grandes estruturas condicionais ou de controlo implementadas. Apesar de essa simplicidade se verificar na maioria dos casos de aplicações práticas desses programas, existem, porém, situações nas quais esses programas revelam um grau de complexidade significativo, exigindo aos seus criadores bons conhecimentos de programação. Neste livro não pretendemos, em particular, abordar qualquer aspeto relacionado com a programação de computadores, mas sim revelar que qualquer objeto de uma base de dados que acolha uma qualquer porção de código pode assumir alguma complexidade. Nesta secção iremos apresentar e analisar alguns exemplos de procedimentos armazenados nos quais se utilizam estruturas de programação um pouco mais sofisticadas do que aquelas que temos utilizado até agora. Estes exemplos permitir-nos-ão introduzir tais estruturas e revelar alguns casos interessantes de implementação de procedimentos.

7.1.1 ESTRUTURAS CONDICIONAIS Apesar de já termos utilizado no capítulo anterior algumas estruturas condicionais em exemplos de aplicação prática, nesta secção vamos dedicar-lhes um pouco mais de atenção. As estruturas condicionais são implementadas através de instruções que nos permitem executar um conjunto de ações © FCA

299

BASES DE DADOS RELACIONAIS: IMPLEMENTAÇÃO COM MYSQL de acordo com o valor lógico de uma dada condição, expressão ou valor. São estruturas com uma enorme utilidade prática, uma vez que nos ajudam a tomar decisões ao longo da execução de um programa, permitindo analisar o conteúdo de variáveis durante a execução do programa implementado e, assim, decidir qual a ação (ou ações) a tomar, como definir o valor de uma variável, realizar queries ou redirecionar a execução de um programa. A utilização de estruturas condicionais, vulgarmente designadas por estruturas IF-THEN-ELSE, deve respeitar, obviamente, a sintaxe que está estabelecida para a sua utilização no sistema. IF <condição> THEN <instruções> [ELSEIF <condição> THEN <instruções>] ... [ELSE <instruções>] END IF

Estas estruturas não são difíceis de implementar, desde que se saiba o que se pode e quer fazer com elas. Vejamos alguns exemplos de programas nos quais foram utilizadas estas estruturas. Em SQL 7.1, apresentamos um exemplo de um procedimento – spViaturaVendeuLivro – no qual se aplica uma estrutura condicional simples. Este procedimento foi desenvolvido especificamente para determinar se uma viatura da LLonRoad vendeu algum exemplar de um dado livro. Se assim tiver acontecido, o procedimento revela a data e a localidade na qual o livro foi vendido e o número de exemplares que foram adquiridos. -- Criação de um procedimento para determinar se uma viatura vendeu algum -- exemplar de um dado livro. Se assim tiver acontecido, o procedimento -- revela, através de uma pequena mensagem, a data e a localidade na qual foi -- vendido esse livro, bem como o número de exemplares adquiridos. -DELIMITER $$ CREATE PROCEDURE spViaturaVendeuLivro (IN IdViatura INT, IN IdLivro INT, OUT InfLivro VARCHAR(150)) BEGIN DECLARE vLocalidade VARCHAR(75); DECLARE vExemplares VARCHAR(10); -SELECT LO.Designacao, VD.Quantidade INTO vLocalidade, vExemplares FROM Vendas AS VE INNER JOIN VendasDetalhes AS VD ON VE.Nr=VD.Venda INNER JOIN Localidades AS LO ON VE.Localidade=LO.Id WHERE VE.Viatura = IdViatura AND VD.Livro= IdLIvro ORDER BY VE.Data DESC LIMIT 1; -IF vLocalidade IS NULL THEN SET InfLivro = '- Não foi realizada qualquer venda do livro na viatura.'; ELSE SET InfLivro = CONCAT('- Localidade: ',vLocalidade, ' - Exemplares vendidos: ', vExemplares,'.'); END IF; END; $$ -- Execução do procedimento "spViaturaVendeuLivro". -- CALL spViaturaVendeuLivro(IN <viatura>, IN <livro>, OUT <informação>); CALL spViaturaVendeuLivro(1,6502, @Informacao); SELECT @Informacao AS Informacao;

SQL 7.1 – INFORMAÇÃO SOBRE A VENDA DE UM LIVRO NUMA VIATURA

De seguida, em SQL 7.2, podemos ver um outro procedimento – spNivelVendas –, que foi criado para apresentar o nível ('Nulo', 'Baixo', 'Regular' ou 'Muito Bom') relativo às vendas de 300