capa_cuvas.pdf
1
21/11/14
14:50
w w w. u n i s u l . b r
Universidade do Sul de Santa Catarina
Princípios de Bancos de Dados
UnisulVirtual Palhoça, 2015
Créditos Universidade do Sul de Santa Catarina – Unisul Reitor
Sebastião Salésio Herdt Vice-Reitor
Mauri Luiz Heerdt Pró-Reitor de Ensino, de Pesquisa e de Extensão
Mauri Luiz Heerdt
Pró-Reitor de Desenvolvimento Institucional
Luciano Rodrigues Marcelino
Pró-Reitor de Operações e Serviços Acadêmicos
Valter Alves Schmitz Neto
Diretor do Campus Universitário de Tubarão
Heitor Wensing Júnior
Diretor do Campus Universitário da Grande Florianópolis
Hércules Nunes de Araújo
Diretor do Campus Universitário UnisulVirtual
Fabiano Ceretta
Campus Universitário UnisulVirtual Diretor
Fabiano Ceretta Unidade de Articulação Acadêmica (UnA) – Ciências Sociais, Direito, Negócios e Serviços
Amanda Pizzolo (coordenadora)
Unidade de Articulação Acadêmica (UnA) – Educação, Humanidades e Artes
Felipe Felisbino (coordenador)
Unidade de Articulação Acadêmica (UnA) – Produção, Construção e Agroindústria
Anelise Leal Vieira Cubas (coordenadora)
Unidade de Articulação Acadêmica (UnA) – Saúde e Bem-estar Social
Aureo dos Santos (coordenador)
Gerente de Operações e Serviços Acadêmicos
Moacir Heerdt
Gerente de Ensino, Pesquisa e Extensão
Roberto Iunskovski
Gerente de Desenho, Desenvolvimento e Produção de Recursos Didáticos
Márcia Loch
Gerente de Prospecção Mercadológica
Eliza Bianchini Dallanhol
Luciano José Sávio Marcelo Medeiros
Princípios de Bancos de Dados
Livro didático
Designer instrucional Flavia Lumi Matuzawa
UnisulVirtual Palhoça, 2015
Copyright © UnisulVirtual 2015
Nenhuma parte desta publicação pode ser reproduzida por qualquer meio sem a prévia autorização desta instituição.
Livro Didático
Professor(es) Conteudista(s) Luciano José Sávio Marcelo Medeiros Designer instrucional Flavia Lumi Matuzawa
Projeto gráfico e capa Equipe UnisulVirtual Diagramador(a) Noemia Mesquita Revisor(a) Diane Dal Mago
005.74 P95 Sávio, Luciano José. Princípios de banco de dados: livro didático/Luciano José Sávio, Marcelo Medeiros; design instrucional Flavia Lumi Matuzawa. – Palhoça : UnisulVirtual, 2015. 168 p. : il. ; 28 cm. Inclui bibliografia.
1. Banco de dados. 2. Engenharia de software. 3. Tecnologia da informação. I. Medeiros, Marcelo. II. Matuzawa, Flavia Lumi. IV. Título.
Ficha catalográfica elaborada pela Biblioteca Universitária da Unisul
Sumário Introdução | 7
Capítulo 1
Banco de Dados e a Tecnologia da Informação1 | 9
Capítulo 2
Conceitos de bancos de dados | 21
Capítulo 3
Modelagem de Dados e Projeto de Banco de Dados | 51
Capítulo 4
Ferramentas para modelagem de dados e produtos de banco de dados | 79
Capítulo 5
Implementação do Projeto de Banco de Dados | 93
Capítulo 6
Acesso e Manipulação de Dados | 111
Capítulo 7
Recuperação de Informações do Banco de Dados | 125
Capítulo 8
Recuperação de Informações através de várias tabelas | 145 Considerações Finais | 163 Referências | 165 Sobre os Professores Conteudistas | 167
Introdução Bem vindo ao estudo da área de banco de dados! Através desta Unidade de Aprendizagem, Princípios de Banco de Dados, você terá um contato inicial com uma área que é cada vez mais indispensável para o bom funcionamento dos sistemas de informação. Ao acessar qualquer sistema informatizado, seja ele um caixa eletrônico, um sistema de compras on-line, realizar um cadastro em uma loja, é cada vez mais frequente a utilização de um ambiente de armazenamento de dados, denominado banco de dados. Este ambiente proporciona o armazenamento e a recuperação de informações de forma rápida e segura, proporcionando maior confiabilidade às informações armazenadas. Durante os estudos, você estará em contato com as formas de armazenamento e com os produtos de banco de dados, bem como com ferramentas de apoio à utilização de um ambiente deste tipo. Além de conhecer como as informações são armazenadas e as ferramentas utilizadas para este fim, você também aprenderá a utilizar este ambiente, realizando um projeto de banco de dados, e utilizando o banco de dados através do armazenamento e da recuperação de informações neste ambiente. Ótimos estudos a você!
Capítulo 1 Banco de Dados e a Tecnologia da Informação1
Habilidades
•• Contextualizar a utilização da informática para facilitar os processos organizacionais e para gerar produtos de tecnologia. •• Identificar a importância dos bancos de dados no contexto da área de Tecnologia da Informação. •• Compreender a evolução dos sistemas de armazenamento de dados. •• Contextualizar a atuação do profissional de banco de dados na área de tecnologia de informação das empresas.
Seções de estudo
Seção 1: Banco de Dados e a área de Tecnologia da Informação Seção 2: Armazenamento de dados: de sistemas de arquivos a Sistemas Gerenciadores de Banco de Dados Seção 3: O mercado de trabalho e seus profissionais
9
Capítulo 1
Seção 1 Banco de Dados e a área de Tecnologia da Informação Neste capítulo, será contextualizada a área de banco de dados nas organizações, sua importância, bem como dos profissionais que nela atuam e também como os bancos de dados evoluíram e se consolidaram no mercado. Porém, para compreender melhor o mercado de informática voltado à área de banco de dados, é preciso analisá-lo a partir de dois importantes aspectos, que são: a informática como meio de produção ou a informática como produto final. - Mas o que significa analisar estes aspectos dos Bancos de Dados? Quando uma empresa utiliza a informática como complemento à sua produção ou à criação do seu produto final, não está vendendo um serviço de informática, mas se utilizando desse serviço para alcançar os seus objetivos. Essa empresa usa a informática como um processo complementar às suas atividades, por isso é referida como empresa que utiliza a informática como meio. Um exemplo disso são os supermercados. Um supermercado utiliza a informática, os sistemas de banco de dados e softwares para permitir que seus clientes tenham acesso aos serviços oferecidos pela empresa. Entretanto, esse serviço não está sendo diretamente vendido ao cliente, que muitas vezes nem se dá conta da quantidade de recursos de informática que foram necessários para a realização de determinada tarefa. Há também no mercado as empresas que têm a informática como seu principal produto, criando softwares, realizando consultorias na área da computação, instalando e montando equipamentos. Essas empresas têm a informática como seu produto final. Enquadram-se neste ramo as fábricas de software e software houses, que trabalham diretamente com a produção de softwares e/ou fornecendo consultoria para a área de tecnologia da informação para outras empresas. Por exemplo, a MicroSoft é uma empresa que tem o foco na criação de produtos de informática, nesse caso, softwares, o seu principal recurso financeiro. A informática é o produto final da sua linha de produção, e seus produtos serão utilizados como meio de produção em muitas empresas de todo o mundo.
- Você deve estar se perguntando: “e daí”?
10
Princípios de Bancos de Dados Bem, como temos cenários diferentes para o uso da informática, temos também necessidades de profissionais diferentes para desenvolver as tarefas do dia a dia. E é nesse escopo que este capítulo está inserido, de forma a apresentar a você, o contexto da área de banco de dados dentro da Tecnologia da Informação das empresas, o papel dos profissionais que estão envolvidos com a informática, seja ela tendo a informática como produto meio ou fim. Você também vai conhecer como ocorreu a evolução dos sistemas de armazenamento de dados desde o princípio dos sistemas de armazenamento até o advento dos Sistemas Gerenciadores de Banco de dados ou SGBDs. A Tecnologia da Informação, ou simplesmente TI, está em constante evolução, assim como tudo na área da informática. A cada dia surgem novos conceitos e jargões utilizados pelo mercado, a fim de representar uma nova tecnologia, técnica ou mesmo um novo produto. Se vocês fizerem uma pesquisa pela rede mundial, poderão se deparar com termos já não mais utilizados, ou em desuso no mercado da informática, como por exemplo, downsizing, rightsizing, mainframe, entre outros, mas que em algum momento da história estiveram presentes e indicavam algum tipo de técnica ou tecnologia utilizada. Atualmente, o banco de dados está posicionado nas áreas de TI das empresas, junto às áreas de redes, telecomunicações e hardware, formando a infraestrutura base da área de informática dessas empresas. Ter uma infraestrutura consolidada e robusta é indispensável para qualquer organização, pois representa segurança nas operações fundamentais para o fluxo das informações na empresa. Entre essas operações fundamentais destacam-se o fornecimento de informações e os mecanismos que permitem essas informações circularem pela organização.
Os elementos formadores da área de infraestrutura são ditos funções críticas, ou seja, muito importantes para que toda a área de TI funcione adequadamente. Imagine você em uma empresa com 500 pontos de redes interconectados, utilizando-se de 25 tipos de softwares. Todo esse pessoal depende exclusivamente das redes de comunicação, para que consiga trocar e armazenar informações, e também do banco de dados, onde as informações utilizadas por esses usuários são registradas pelos sistemas de informação e processadas. Seguindo essa lógica, qualquer problema envolvendo algum elemento da área de infraestrutura pode causar uma paralização das atividades de grande parte da empresa, inviabilizando o trabalho de muitas pessoas. Daí, atualmente, a grande importância atribuída a essa área e, consequentemente, ao banco de dados das organizações.
11
Capítulo 1 O elemento principal, responsável pelo fornecimento das informações na área de TI, é, sem dúvida, o banco de dados, ou melhor, o Sistema Gerenciador de Banco de Dados (SGBD). Ele é o responsável por toda a manutenção da informação que é mantida pela empresa, conforme apresentado na próxima seção.
Seção 2 Armazenamento de Dados: de sistemas de arquivos a Sistemas Gerenciadores de Banco de Dados O armazenamento de dados passou por algumas transformações e adaptações ao longo do tempo. Essas mudanças se deram em função da evolução da tecnologia, com novos equipamentos e soluções para o armazenamento de dados, e também pela própria evolução das novas necessidades de armazenamento, entre elas, o aumento do número de informações, exigindo sempre mais espaço para colocá-las, além da recuperação e armazenamento mais rápidos e eficientes, com maior segurança. Até 30 anos atrás, o foco principal da área de informática estava no processamento dos dados e no aprimoramento da tecnologia para que esse processamento tivesse melhor performance. Trabalhou-se muito para que os processadores, memória e o conjunto de componentes internos evoluíssem e se tornassem cada vez mais performáticos e seguros nas suas operações. Uma vez que se adquiriu uma qualidade significativa com esses elementos, uma necessidade pelo armazenamento mais seguro das informações começou a ser delineada, já que, com muita capacidade de processamento à disposição, deveria haver mecanismos para que se armazenasse o resultado desse processamento ou que servisse de fonte de informações para serem processadas. Essa nova necessidade de armazenar mais e mais informações levou a área de informática a desenvolver novas tecnologias para que pudesse suportar maiores capacidades de armazenamento, sendo essas informações armazenadas com um mínimo de segurança e confiabilidade. Com o aumento da capacidade de armazenamento e recuperação das informações, e também com o desenvolvimento de novas metodologias de desenvolvimento de softwares, tornou viável o processamento de grandes volumes de informações, e as organizações passaram a se informatizar num ritmo mais acelerado, vislumbrando maior agilidade e, consequentemente, maior competitividade no mercado em que estavam inseridas.
12
Princípios de Bancos de Dados A estrutura para armazenamento dos dados utilizada pelos sistemas de informação era bastante simples, conforme mostra a figura a seguir. Figura 1.1 – Estrutura para armazenamento dos dados para Sistemas de Informação
SISTEMAS Contabilidade
Estoque
Arq1
Arq2
RH
Arq3
ARQUIVOS DE DADOS Fonte: Elaboração do autor (2015).
De acordo com a Figura 1.1, nesta estrutura cada sistema de informação é proprietário, detentor da sua própria base de dados. Isso significa que cada sistema de informação detém o conhecimento, tendo na sua programação o código de como manipular os dados mantidos e quais as regras que devem ser seguidas para que se insira, altere ou elimine cada uma das informações ali armazenadas. Nesse tipo de estrutura, fica evidente algumas limitações, como, por exemplo, a necessidade de redundância das informações, ou seja, informações idênticas cadastradas em mais de um sistema. Um exemplo claro dessa limitação é o fato de que existem informações que são de uso de vários departamentos da empresa. Essas informações são denominadas informações corporativas. Como nesta estrutura cada sistema mantém seu próprio arquivo de dados, se o sistema X necessita do endereço do cliente e o sistema Y também necessita dessa informação, essa será registrada em duplicidade. Ao longo do tempo, essa redundância pode acarretar problemas, pois caso o cliente atualize o endereço no departamento X, eventualmente o departamento Y pode não ser informado dessa atualização, gerando dados inexatos na organização. Outra limitação que pode ser identificada é no que diz respeito ao compartilhamento das informações. Como cada sistema mantém o controle de como acessar e manipular as informações nos seus arquivos de dados, os demais sistemas estão limitados a trabalharem apenas com as informações de seus próprios arquivos, por não possuírem conhecimento da estrutura de organização dos arquivos de dados dos outros sistemas, não sendo possível efetuar o seu acesso. Mas apesar das limitações, essa estrutura cumpria com o seu papel, uma vez que a consolidação das informações e o compartilhamento dos dados não era uma necessidade premente, pois as empresas estavam organizadas departamentalmente.
13
Capítulo 1 Houve, porém, uma mudança significativa na forma como as empresas estavam sendo administradas. Por uma nova necessidade de mais agilidade, as organizações começaram a ter o estilo de administração por processos, ao invés do estilo departamental. Nesse tipo de administração, uma ação ocorrida num departamento pode ecoar em alterações em diversos outros setores da empresa. Veja um exemplo prático. Caso o setor de almoxarifado pretenda contratar um novo funcionário, esse deve comunicar ao setor de RH, o qual deve comunicar ao pessoal de recrutamento e seleção, além de comunicar ao setor financeiro que a folha de pagamento vai passar a ter gastos extras. A área de finanças, por sua vez, deve comunicar ao pessoal de vendas que as essas devem aumentar, pois os gastos aumentaram. O pessoal de vendas se comunica com a produção, a qual deve ser incrementada, a fim de suprir as novas vendas.
Um novo desafio foi lançado: como prover tamanha integração entre os sistemas de forma a suportar essa nova necessidade administrativa? Um dos reflexos imediatos foi que esta estrutura, na qual estavam arquitetados os sistemas de informação, logo começou a apresentar dificuldades para se manter, uma vez que uma nova necessidade passou a ser cobrada por parte das organizações, o compartilhamento das informações entre os diversos sistemas. Isso decorre das práticas administrativas das empresas que estavam em franca evolução, e à troca de informações entre os diversos departamentos passou a ser elemento fundamental para que as empresas pudessem ter maior agilidade nos seus processos, consequentemente, adquirir maior competitividade no mercado. A partir dessa necessidade, a estrutura para a implementação de sistemas de informação passou a ter que vencer um grande obstáculo: permitir o compartilhamento de informações entre os diversos sistemas existentes. Cabe citar aqui, que há 20, 30 anos, não havia tecnologias tão avançadas para interoperabilidade e interconectividade entre as tecnologias existentes, dessa forma, não era trivial encontrar sistemas operacionais conversando entre si, nem tampouco sistemas de informação compartilhando informações. Nesse momento, você pode perguntar: mas não basta apenas o Sistema X acessar a base de dados do Sistema Y para que se compartilhem as informações? Essa dúvida é um desafio, por duas razões: 1. O sistema de informação X não tem conhecimento de como o sistema Y trabalha os seus dados, pois toda a estrutura e as regras de manipulação dos dados armazenados estão codificadas dentro do sistema Y.
14
Princípios de Bancos de Dados Como exemplo, imagine que o sistema X precise armazenar uma nova informação na base de dados do sistema Y. Como ele vai conseguir isso se não tem conhecimento de quais as regras estabelecidas para que se faça a inserção dessa nova informação? Assim sendo, fica muito difícil um sistema compartilhar uma informação de outro sistema, inclusive tendo que manter informações duplicadas em vários sistemas, incorrendo na redundância de dados, causando mais dificuldades para a manutenção e o seu controle. Novamente você pode concluir: simples, basta que um sistema conheça como o outro sistema trabalha os seus dados! Isso mesmo, mas isso implica uma questão de mercado, de concorrência, apresentada no próximo item. 2. O mercado de algum tempo atrás era muito segmentado e com atuações pontuais. Dessa forma, a empresa A fornecia o software para RH e Finanças, por exemplo. A empresa B fornecia o software para Vendas e Estoque, assim por diante. Como as organizações estavam orientadas a trabalhar de forma departamental, ou seja, cada departamento resolvia os seus problemas internamente, normalmente cada departamento também escolhia no mercado o software que mais se adaptava às suas necessidades e o adquiria, sem a preocupação com o compartilhamento das informações com outros departamentos e outros sistemas. Com essa nova necessidade de compartilhamento de informações, passou-se a ter grandes dificuldades, pois os softwares adquiridos pelos diversos departamentos não conversavam entre si, e por duas razões principais: ou a tecnologia utilizada era incompatível entre si, dificultando a interoperabilidade, ou o próprio fabricante do software não tinha o menor interesse em abrir a estrutura dos dados do seu software para outro fabricante, que inclusive poderia ser seu concorrente em outros softwares. Como pode ser percebido, a estrutura para os sistemas de informação apresentada na Figura 1.1 já não era a mais adequada para as novas necessidades das organizações. Então, começaram a ser trabalhadas novas alternativas para a problemática do compartilhamento das informações e para a diminuição da redundância dos dados. A alternativa que obteve mais êxito e aceitação foi a de que os sistemas de informação se tornassem clientes das bases de dados, e não mais proprietários, a fim de proporcionar um método de acesso único a essas bases, de forma que qualquer sistema de informação pudesse se conectar a qualquer base de dados e dela compartilhar as informações de seu interesse.
15
Capítulo 1 Para que os sistemas de informação pudessem se tornar clientes de bases de dados, alguém deveria prover um método único e padronizado para o acesso às bases, e também armazenar as regras de manipulação dos dados mantidos por ela. Começava a nascer um novo tipo de aplicação, o gerente para as bases de dados, ou mais propriamente falando, o Sistema Gerenciador de Banco de Dados - SGBD. Para que o SGBD pudesse ser contemplado, uma nova estrutura para os sistemas de informação deveria ser definida. Essa estrutura é apresentada na Figura 1.2. Figura 1.2 – Estrutura para Sistemas de Informação com a utilização de SGBD
SISTEMAS Contabilidade
Estoque
RH
SGBD
BD1
BD4
BD2
BD3
Fonte: Elaboração do Autor (2015).
O SGBD atua como um elemento intermediário entre os sistemas de informação e a base de dados, padronizando a forma de acesso às informações ali mantidas, proporcionando o compartilhamento das informações entre os sistemas e atuando fortemente no controle da redundância dos dados, uma vez que vários sistemas podem acessar uma mesma informação, não necessitando armazená-la novamente, com duplicação.
2.1 O que é SGBD O Sistema Gerenciador de Banco de Dados – SGBD nada mais é do que um sistema intermediário que atua entre os sistemas de informação e a base de dados, provendo aos sistemas as informações armazenadas por meio de mecanismos eficientes e seguros, a fim de proporcionar maior performance no armazenamento e recuperação das informações, além de garantir integridade e consistência aos dados.
16
Princípios de Bancos de Dados O SGBD assume a responsabilidade pela manutenção das informações, retirando essa responsabilidade dos sistemas de informação. Portanto, se uma data é inválida ou se uma informação depende de uma outra para ser armazenada, o sistema de informação não precisa mais se preocupar com isso, pois essas questões ficam a cargo do SGBD. Assim sendo, os sistemas de informação puderam se preocupar muito mais e dedicar mais esforços em ergonomia, fluxo de informação e apresentação das informações ao usuário, deixando a tarefa de tratamento das informações com os SGBDs. Agora que está mais claro o surgimento e a consolidação dos SGBDs no mercado da informática, veja a importância e o papel dos profissionais que atuam nessa área.
Seção 3 O mercado de trabalho e seus profissionais Dentro de uma análise em que a informática é tida como produto fim, de forma resumida, o mercado de trabalho na área de informática pode ser dividido em três grandes linhas profissionais: ••
desenvolvimento de software - programadores;
••
análise de sistemas.
Na primeira opção tem-se o especialista em linguagens e técnicas de programação de computadores, é aquele profissional que domina algumas linguagens de programação, tendo como sua principal base de conhecimento os algoritmos. Já na segunda, tem-se o profissional com alta capacidade analítica, que domina metodologias de modelagem e desenvolvimento de sistemas.
3.1 A terceira linha profissional na área da informática A terceira linha é aquela que entra o profissional de banco de dados. Ele não está em uma única categoria dentro das áreas citadas. Talvez esteja aí a grande complexidade nas atividades desse profissional. Um programador de computador é responsável por transformar as tarefas executadas pelo ser humano em um conjunto de comandos que serão executados pelo computador. Porém, quando essas atividades envolvem armazenamento de dados, passa a ser necessário um especialista para projetar e manipular o modelo de banco de dados criado.
17
Capítulo 1 O mesmo tipo de problema pode ocorrer quando um analista de sistemas precisa modelar um sistema que envolva banco de dados. Será necessário que ele tenha um especialista em banco de dados para auxiliá-lo nesse processo de modelagem. É importante que se distinga a tarefa de modelar um sistema da tarefa de modelar um banco de dados: ••
quando o analista está modelando um sistema, ele está definindo todas as atividades de interação do usuário para com o sistema que será implementado. Estamos nos referindo à interface utilizada para que o usuário consiga manipular adequadamente os dados;
••
já a modelagem do banco de dados representa a forma como os dados que são manipulados pelo sistema serão armazenados. Aqui nos referimos à acomodação e às regras de acesso aos dados nos sistemas de armazenamento. Modelar sistema - define as regras de interação do usuário com o sistema. Modelar banco de dados - define como serão armazenados e as regras de armazenamento dos dados que serão manipulados pelo sistema.
O profissional de banco de dados interage com o programador e com o analista de sistemas, formando uma equipe de desenvolvimento. Quando se analisa as empresas em que a informática é o meio de produção, ela se torna maior ainda, pois na maioria das vezes esse profissional interage com outros das mais diversas áreas. Dentro de um ambiente desses, quem trabalha em um banco de dados possui as mais diversas tarefas, que são de grande importância para o funcionamento da empresa, pois todos os processos são executados com o apoio dos sistemas de informação, os quais estão conectados a SGBDs e dependem de seu desempenho. Conheça algumas atividades executadas por esse profissional:
18
••
População das tabelas dos bancos de dados – cadastramento de dados.
••
Manutenção do banco de dados – garantir que ele não pare de funcionar.
••
Backup dos dados – realizar a cópia de segurança dos dados para garantir a fidelidade deles.
Princípios de Bancos de Dados ••
Gerenciar o banco de dados – definir senhas e prioridades de acesso ao sistema, evitando que acessos indevidos sejam realizados.
••
Manter o contato com o fabricante da ferramenta de banco de dados – para realização de atualizações e instalações de novos recursos.
Observe que essas atividades são fundamentais para que a empresa se mantenha em funcionamento. A maioria das atividades de produção é dependente do sistema de banco de dados, por isso é grande a responsabilidade desse profissional, ou da equipe que ele gerencia. Quanto maior for a organização, maior a complexidade do sistema de banco de dados e, consequentemente, maior deve ser a equipe envolvida. Em pequenas organizações é comum encontrar o analista de sistemas atuando como programador, modelando banco de dados e gerenciando os projetos de informática. Em pequenas organizações é comum encontrar o analista de sistemas atuando como programador, modelando banco de dados e gerenciando os projetos de informática. Agora, quanto maior a organização, maior a complexidade do sistema de banco de dados e, consequentemente, maior deve ser a equipe envolvida. Assim, identificam-se mais alguns papéis para a divisão das tarefas de um especialista em banco de dados: 3.1.1 Administrador de banco de dados (DBA) Em uma organização onde muitas pessoas utilizam os mesmos recursos, existe a necessidade de um administrador para gerenciar esses recursos. Em ambiente de banco de dados, o recurso primário é o banco de dados propriamente dito e o recurso secundário é o SGBD, ambos sobre a supervisão do administrador desse banco (DBA). O DBA é responsável pela autorização de acesso ao banco de dados, monitoração e coordenação de seu uso, e está envolvido com os seus aspectos físicos (estruturas de armazenamento, métodos de acesso etc.). 3.1.2 Projetistas de banco de dados Projetistas de banco de dados são responsáveis pela identificação desses e pela escolha de estruturas apropriadas para representar e gravar tais dados. Essas tarefas são executadas antes da implementação desse banco. É necessária uma comunicação com os usuários do banco de dados e analistas de sistemas para entender seus requisitos, de modo que o projeto possa atendê-los. A visão de cada grupo de usuários deve ser entendida, e o projeto final deverá suportar os requisitos de todos os grupos de usuários.
19
Capítulo 1 3.1.3 Usuário final Usuário final é a pessoa cujo trabalho requer acessar o banco de dados para consulta e atualizar os dados; um banco de dados existe primariamente para seu uso. A maioria dos usuários finais utiliza programas voltados ao desempenho de suas funções profissionais, interagindo com tais programas em seu dia a dia. Nessa classe, pode-se citar caixa bancário, caixa de supermercado, agente de turismo, vendedores de varejo etc. Alguns usuários mais sofisticados, como engenheiros e cientistas, estão mais familiarizados com as facilidades de um SGBD e são capazes de utilizar ferramentas para elaborar suas consultas.
20
Capítulo 2 Conceitos de bancos de dados
Habilidades
•• Diferenciar o sistema de armazenamento baseado em arquivos do sistema de armazenamento de um sistema gerenciador de banco de dados. •• Distinguir arquiteturas de acesso ao armazenamento dos dados. •• Caracterizar e qualificar os tipos de sistemas gerenciadores de banco de dados.
Seções de estudo
Seção 1: Conceito de banco de dados Seção 2: Tipos de sistemas de gerenciamento de banco de dados Seção 3: Introdução à modelagem de dados
21
Capítulo 2
Seção 1 Conceitos de banco de dados O computador é uma máquina e como tal deve ser sempre assim referenciado. Essa máquina possui algumas características que a distingue das demais, como a velocidade de processamento e a capacidade de armazenamento. É considerado um equipamento, que com o passar dos anos se tornou de uso pessoal e profissional, podendo ser usado no lazer e no dia a dia, nas mais variadas tarefas e de complexidades mais diversas. Olhando por um prisma mais direto, o computador possui três tarefas básicas, que são: entrada de dados, processamento de dados e saída de dados. ••
A entrada de dados é responsável pela importação das informações para o computador e está diretamente relacionada à interação com usuário, pois na maioria das vezes depende da informação digitada por ele, na forma mais simples de interação, o teclado.
••
Já a saída de dados representa os dados informados na entrada, mas que de alguma forma foram transformados atendendo as expectativas do usuário e são apresentados por meio de algum dispositivo de saída, como impressora ou monitor.
••
A essa transformação dos dados da entrada em dados da saída chama-se processamento.
Até aqui, se fosse apenas isso, seria muito mais simples compreender o ambiente dessa máquina, porém, com o passar dos anos, uma nova propriedade foi adicionada a essas três tarefas, e não sendo menos importante: o armazenamento dos dados. Tão importante quanto informar os dados, processá-los e apresentá-los, a organização e o armazenamento desses dados passaram a ser uma tarefa essencial, principalmente pelo grande número de usuários que interagem com o equipamento ao mesmo tempo. Com passar dos anos, o número de dados processados por um computador cresceu de forma considerável, e, de forma inversamente proporcional, o computador tem diminuído de dimensões físicas e aumentado a sua capacidade de armazenamento e processamento de dados, tornando-se cada vez mais pessoal. Gerenciar e armazenar dados tornou-se uma tarefa complexa, que envolve muitas ferramentas e tecnologias de armazenamento e acesso aos dados. Com a grande evolução da computação em geral, ou da informática em geral, os equipamentos têm evoluído muito, deixando seu custo mais acessível.
22
Princípios de Bancos de Dados Com isso, o uso dos computadores tem atingido os mais variados perfis de usuários, dos mais experientes – que o usam sem nenhuma dificuldade –, aos mais novatos – que o vêem como uma “máquina do outro mundo”. Dessa forma, o parque tecnológico em todo o mundo tem atingido números expressivos. Fazendo uma matemática simples, imagine que a principal finalidade do computador é armazenar dados que possam ser acessados a qualquer momento e em qualquer lugar do mundo; quanto maior o número de computadores, maior o número de dados para serem gerenciados e processados. Como a tendência é que esse número deva aumentar a cada ano, a complexidade dos dados armazenados deverá ser um grande problema da área de informática. Em paralelo ao grande número de novos usuários de computadores, bem como a expansão na facilidade de acesso aos computadores e às novas tecnologias em hardware, há também o surgimento de novas ferramentas computacionais para gerenciamento e integridade dos dados. Essa demanda gera uma nova expectativa ao profissional da informática quanto ao seu envolvimento com as tecnologias de banco de dados. Além de ser um especialista em banco de dados, o profissional deve ter capacidade de compreender as diferentes tecnologias do mercado, de escolher aquela que melhor atenda às necessidades do seu usuário e deve propor e modelar soluções aos diversos problemas, fazendo disso parte das suas atividades profissionais. A esse conjunto que envolve ferramentas computacionais, gerenciamento de dados, armazenamento e integridade aos dados, chama-se sistema de gerenciamento de banco de dados.
- E é nesse mundo que você embarca a partir de agora, interagindo com um mundo de armazenamento, gerenciamento e manipulação de dados. Quando nos referimos ao computador como uma máquina, temos como foco a sua capacidade de gerenciamento e armazenamento de informações. Vale salientar que as outras propriedades, como velocidade de processamento e fácil interação com usuário, são também muito importantes, porém, como o escopo desta unidade de aprendizagem é banco de dados, será dada maior ênfase ao armazenamento dos dados. De forma simplificada, pode-se conceituar banco de dados como sendo um sistema de armazenamento de dados baseado em computador, cujo objetivo é registrar e manter informações consideradas significativas a qualquer organização ou a um único usuário. (DATE, 1990)
23
Capítulo 2 O banco de dados possui uma estrutura física que representa os dados armazenados, com regras de manipulação bem definidas. No caso da computação, o banco de dados representa o arquivo físico de dados armazenados em dispositivos periféricos, nos quais estão os dados de diversos sistemas disponíveis para manipulação pelos usuários. Sendo assim, a partir dos conceitos anteriormente citados, pode-se chegar a um conceito mais amplo de banco de dados: Banco de dados é um sistema de armazenamento que possui arquivos físicos de dados, localizados em dispositivos periféricos, nos quais estão armazenados os dados relacionados entre si, de diversos sistemas, disponíveis para serem manipulados pelos usuários.
1.1 Definindo “dado” e “informação” Uma vez que você teve o primeiro contato com o conceito de banco de dados, é importante que se defina um aspecto importante, a diferença entre dado e informação. A princípio parecem ter o mesmo significado, mas quando se refere a esse tema no escopo de banco de dados, existe sim uma significativa diferença. O dado é o valor cadastrado no banco de dados, que será exportado para o usuário por meio das consultas realizadas. Cada conjunto de informações exportadas, ou listadas, está diretamente relacionado à forma pela qual foi definida a regra de seleção dos dados. Nesse contexto, um dado pode ser representado pelo nome de um cliente, pela rua onde mora, número da casa, enfim, dado representa um elemento de informação. A informação diz respeito a um conjunto de dados que, devidamente contextualizado, representa algo para o usuário. Por exemplo, rua, número da casa, bairro, cep, cidade, estado (UF) podem ser encarados como dados cadastrais de um cliente, porém, o conjunto deles compõe uma informação, representando o endereço completo do cliente. Os dados abrangem uma grande e variada forma de representação. Como qualquer informação armazenada no banco de dados é um dado, eles podem representar qualquer tipo de informação, como uma data, uma hora, um nome, uma idade etc. Assim sendo, os dados serão armazenados num banco sob um determinado padrão preestabelecido, visando a delimitar o que pode ser armazenado como um valor para o dado. Esses padrões são denominados Tipos de Dado, sendo que cada um é definido como elemento componente de um SGBD e deve estar associado ao seu tipo de dado correspondente.
24
Princípios de Bancos de Dados Seguindo esse raciocínio, podemos encontrar num banco de dados um tipo de dado que só armazena números inteiros, utilizando, por exemplo, para armazenar o número de uma nota fiscal, ou o código do DDD de um número de telefone. Já para armazenar uma data de nascimento, o tipo inteiro não consegue representá-la, pois as regras que regem as datas são mais complexas, por exemplo, se tentarmos armazenar a data de 31/02/2008 em um tipo data, certamente o SGBD não aceitará este valor, uma vez que essa data é inválida. As regras de definição dos dados são essenciais para mantê-los dentro de um padrão esperado e de fácil compreensão. Por exemplo, não faz muito sentido utilizar um dado do tipo inteiro, que compreende apenas números, para se armazenar o nome de uma pessoa, pois dessa forma o armazenamento dos dados não estará representando fielmente a realidade.
1.2 Sistemas de gerenciamento de banco de dados (SGBD) Dentro da informática em geral, principalmente quando se refere ao desenvolvimento de softwares, o banco de dados recebe uma nomenclatura bem mais ampla: sistema gerenciador de banco de dados, ou simplesmente SGBD. O SGBD é o software responsável pelo gerenciamento, armazenamento e recuperação dos dados no banco de dados.
Os sistemas gerenciadores de banco de dados surgiram no início da década de 70, com a finalidade de promover maior performance e segurança na tarefa de armazenamento, recuperação e manutenção dos dados, impactando também numa maior padronização de acesso a eles por parte das aplicações. Os primeiros produtos de SGBDs tinham um custo elevado, desenvolvidos sob uma tecnologia que necessitava de um alto grau de especialização para serem operados. À medida que novos investimentos foram sendo aplicados na área e novas pesquisas desenvolvidas, os SGBDs evoluíram consideravelmente, tornando a sua utilização muito mais simplificada e os seus recursos mais avançados. Atualmente, encontram-se como padrão no mercado o banco de dados relacional, que opera sob o enfoque de relacionamento entre os dados armazenados, o que será apresentado em maiores detalhes no decorrer da disciplina. A partir da década de 80, com o barateamento na produção de equipamentos e a produção em grande escala de computadores, esse sistema de banco de dados passou a dominar o mercado, tornando-se um padrão internacional, principalmente pela sua facilidade de uso e alta performance com o gerenciamento dos dados.
25
Capítulo 2 Entretanto, as pesquisas não pararam por aí. Com a evolução tecnológica dos equipamentos e da tecnologia, o mercado passou a investir na pesquisa de novas metodologias, técnicas e padrões para a modelagem ou projeto de bancos de dados. Sendo assim, atualmente existem várias metodologias ou técnicas que buscam auxiliar os profissionais na tarefa de projetar soluções para o armazenamento da dados em sistemas gerenciadores de banco de dados. Diante dessa informação, podemos concluir que um sistema gerenciador de banco de dados é uma ferramenta computacional em constante atualização e evolução. 1.2.1 Fundamentos de um sistema gerenciador de banco de dados Algumas características importantes dos sistemas de banco de dados os diferenciam dos sistemas de arquivos tradicionais, que também são formas de armazenamento de dados. Essas características são: ••
a separação entre programas e dados;
••
o compartilhamento de dados e processamento multiusuário;
••
o armazenamento no próprio banco de dados da sua estrutura ou esquema.
Você poderá ler a seguir uma breve explicação de cada um desses itens, a fim de facilitar a sua compreensão sobre o que há de diferente em um sistema de banco de dados que o torna tão especial. 1.2.2 Separação entre “programas” e “dados” Esse tópico refere-se a uma propriedade fundamental para o bom desempenho de um sistema de banco de dados. Planejar um banco de dados não é tarefa simples, ou fácil. O projeto de um banco de dados deve representar fielmente as tarefas executadas diariamente fora do computador e que, por algum motivo, foram implementadas nele. Ou seja, um projeto de banco de dados deve representar o mundo real, de forma eficiente e íntegra, procurando sempre atender às necessidades dos usuários. Quando se refere à computação, principalmente à área de desenvolvimento de softwares, o projeto de um banco de dados envolve duas tarefas distintas:
26
••
a criação do modelo de banco de dados – realizada por meio de uma ferramenta computacional;
••
a implementação de um programa de computador – que será responsável por interagir com esse banco de dados. A criação do programa de computador se faz por meio das linguagens de programação.
Princípios de Bancos de Dados A criação do modelo de dados e sua implementação num SGBD é tarefa geralmente designada a um especialista da área, o qual deve ter conhecimentos específicos de como desenvolver e implementar o projeto do banco de dados, de forma a obter maior performance da ferramenta. Já o desenvolvedor ou analista de sistemas deve implementar o programa de computador de forma que esse consiga interagir com o SGBD de modo eficiente e com performance nas operações realizadas no banco de dados. Esse é um dos pontos cruciais de um projeto de um sistema de informação. O modelo de dados é armazenado num Sistema Gerenciador de Banco de Dados, que cuida do acesso e do gerenciamento deles, já o programa de computador, ou sistema de informação, trata da comunicação com o SGBD para acessar e apresentar os dados aos usuários.
Por vezes, porém, tanto o projeto do banco de dados quanto o programa em si podem ser desenvolvidos pelo mesmo profissional, uma vez que tenha as qualificações adequadas às funções. Veja a figura a seguir, ela ilustra um esquema de desenvolvimento de software com acesso a banco de dados: Figura 2.1 – Esquema de desenvolvimento de Software com acesso a Banco de Dados Linguagem de programação
Banco de dados
Programador Sistema Gerenciador de Banco de Dados
Especialista em Banco de Dados
Fonte: Elaboração do autor (2015).
O programador é responsável por criar o programa de computador que fará a integração da interface gráfica com os dados do banco de dados. Será por meio dessa aplicação que os dados serão cadastrados e manipulados pelo usuário. As regras de funcionamento, ou de negócio, dessa aplicação serão criadas pela linguagem de programação utilizada pelo programador, sem que ele saiba como é o funcionamento interno do gerenciador de banco de dados.
27
Capítulo 2 O banco de dados representa as tabelas que foram criadas para o armazenamento e relacionamento deles. Esse esquema segue as regras definidas pelo sistema gerenciador de banco de dados. O sistema gerenciador de banco de dados é a ferramenta computacional utilizada pelo especialista nisso, para implementar o banco de dados físico, que deverá representar as tarefas executadas pelo mundo real. O especialista em banco de dados, que em alguns casos é também o programador, é responsável por modelá-lo, identificando junto ao cliente final quais são as suas reais necessidades. É importante salientar que a aplicação desenvolvida possui regras separadas das implantadas no banco de dados. A aplicação desenvolvida ou o programa de computador representa as regras de utilização do sistema, chamadas de regras de negócio. Os dados armazenados estão em outra camada que representa a forma pela qual os dados serão manipulados e armazenados. Uma camada não precisa saber detalhes de como a outra foi desenvolvida, que linguagem usou ou como os dados são fisicamente armazenados no computador. Isso diz respeito ao conceito de abstração de dados, visto anteriormente. Veja a Figura 2.2. Ela representa essa separação entre programa e dados, e ainda acrescenta uma camada que representa a interface gráfica de interação entre usuário e programa de computador. Solução computacional Regras de negócio – programação Regras de armazenamento – Banco de dados Interface Gráfica – Programador
28
Princípios de Bancos de Dados Figura 2.2 – Separação entre programas e dados
Solução Computacional Programador
Interface Gráfica (Telas Relatórios)
Regras de negócio
Regras de armazenamento do Banco de dados Especialista em Banco de Dados Fonte: Elaboração do autor (2015)
1.2.3 O compartilhamento de dados e processamento multiusuário Tão importante quanto a definição de uma solução em camadas, na qual há um grande nível de abstração como visto anteriormente, o compartilhamento de dados é uma característica predominante em soluções que utilizam banco de dados. Essa característica é fundamental para que dados cadastrados em um sistema possam ser acessados por vários usuários ao mesmo tempo, de lugares distintos. Além de permitir que os dados sejam acessados por vários usuários, evita-se o cadastro repetido da mesma informação em banco de dados diferentes, o que acarretará uma redundância de informações e imprecisão dos dados. Cabe salientar que aqui se tem mais um exemplo da separação entre dados e programa, pois ao programador da aplicação computacional não é necessário que saiba como esse multiacesso funciona, mas é essencial que ele possa utilizar-se desses recursos do sistema gerenciador de banco de dados. Na representação a seguir, temos uma solução em que cada usuário acessa uma única base de dados, idêntica, porém sem integração. Em cada base de dados
29
Capítulo 2 estão os produtos vendidos por cada filial, gerando um controle de estoque essencial ao funcionamento da empresa. Figura 2.3 – Acesso a dados em bancos independentes
Filial A
Filial B Banco de dados
Banco de dados
Aplicação
Aplicação
Produtos
Produtos
Usuário
Usuário Fonte: Elaboração do autor (2015).
Além de ter um único acesso à base de dados idêntica, o que pode gerar dados incorretos e redundância das informações, não há comunicação entre os equipamentos, pois tanto a filial A como a filial B terão um computador dedicado ao armazenamento dos dados, bem como um computador para cada usuário, pois o acesso aos dados é local. Dessa forma, para cada usuário haverá um computador com acesso a uma base de dados local e, consequentemente, mais uma réplica (cópia) dos dados armazenados, o que com certeza resultará em uma grande redundância de informações e dados incorretos. Além disso, há o custo mais alto para se manter essa solução que parece ser a mais simples. Ao se utilizar de uma solução não compartilhada, normalmente observamos a ocorrência dos seguintes problemas: ••
entrada repetida da mesma informação;
••
inconsistência dos dados;
••
excesso de dados;
••
falta de padrão dos dados cadastrados.
Para resolver esse problema é primordial que se utilize o recurso de compartilhamento de dados que um sistema gerenciador de banco de dados disponibiliza. Além de uma base mais confiável, o custo de manutenção e instalação da solução será mais barato, pois será um computador dedicado ao armazenamento de dados e a vários terminais de acesso, que não necessariamente precisam ser computadores de alto valor no mercado.
30
Princípios de Bancos de Dados Veja essa solução no diagrama: Figura 2.4 – Acesso a banco de dados centralizado
Filial A Banco de dados
Aplicação
Produtos
Filial B
Usuário Usuário Aplicação
Usuário
Usuário Usuário
Fonte: Elaboração do autor (2015).
1.2.4 O armazenamento no próprio banco de dados da sua estrutura ou esquema De forma sucinta, um banco de dados pode ser visto como sendo uma estrutura formada pelos dados armazenados e pelas regras que devem ser obedecidas para a sua manipulação no SGBD. Assim como os dados são armazenados fisicamente no banco de dados, no dispositivo de armazenamento disponibilizado para ele, o esquema do banco de dados que define quais são as tabelas do banco, o relacionamento entre elas e o tipo de dado de cada coluna também são armazenados junto ao banco de dados. Assim sendo, as regras de manipulação e o armazenamento não precisam ser tratados de forma diferente pelo especialista em banco de dados que utiliza a ferramenta, na verdade, é mais um nível de abstração, pois para ele é indiferente a forma pela qual a ferramenta computacional armazena essas regras.
31
Capítulo 2 Veja a figura 2.5: Figura 2.5 – Base de Dados
SGDB Esquema Dados Fonte: Elaboração do autor (2015).
1.3 Arquitetura lógica de SGBD Três características importantes da abordagem de banco de dados são: abstração de dados, isolamento entre dados e programas e uso de um catálogo para gravar a descrição do banco de dados, comumente denominado de dicionário de dados. 1.3.1 Abstração de Dados As ferramentas de banco de dados têm como principal propriedade a sua abstração. Isso significa dizer que o programador de uma linguagem de programação não precisa implementar o funcionamento das estruturas que compõem o banco de dados, muito menos saber como elas funcionam e foram criadas. Cabe ao programador usar os recursos que a ferramenta de banco de dados disponibiliza, independente da linguagem de programação usada. Pode-se entender o conceito de abstração de dados por meio da visão que três usuários possuem da estrutura de um sistema de informação, vejamos:
32
••
Um profissional de banco de dados deve possuir um nível de detalhamento das informações de armazenamento do banco de dados bastante completo, sendo importante para ele identificar em qual disco é armazenada uma determinada informação, se há espaço de armazenamento suficiente nesse disco, se a performance do disco é aceitável.
••
Já para um analista de sistemas, ou para um desenvolvedor, ele não precisa saber exatamente onde está armazenada uma determinada informação, mas ele precisa apenas ter o acesso a ela ao se conectar ao SGBD, podendo identificar as regras que regem o armazenamento daquela informação. Perceba que o nível de detalhe do ambiente é bem menor.
Princípios de Bancos de Dados ••
Para um usuário do sistema de informação, o nível de detalhamento do sistema mais é superficial ainda, ele precisa apenas ter o conhecimento de como acessar e de como operar o sistema de informação, não se importando com as regras de armazenamento ou onde a informação vai ser armazenada, o importante para esse usuário é que operando devidamente o sistema, as informações serão armazenadas com segurança. Dessa forma, podemos concluir que a abstração de dados nos permite trabalhar com um nível de detalhamento das informações adequado à tarefa que se precisa executar.
A partir do entendimento desse conceito, a abstração de dados pode ser formalmente contextualizada na literatura de banco de dados três níveis de abstração: Figura 2.6 – Níveis de abstração de dados
Usuários Finais Visão Interna
Visão Externa
Nível Externo
Nível conceitual
Esquema Conceitual
Esquema Interno
Nível Interno
Banco de Dados Armazenado Fonte: Elaboração do autor (2015).
33
Capítulo 2 O nível de abstração interno, ou também denominado de nível físico de abstração, tem um esquema interno que descreve detalhadamente a estrutura de armazenamento físico do banco de dados. O esquema interno usa um modelo de dados físico e descreve detalhes de armazenamento de dados. Esse nível de abstração pode ser utilizado por profissionais de banco de dados que precisam de detalhamento amplo do ambiente de armazenamento para conseguir fazer as devidas manutenções nesse ambiente. O nível de abstração conceitual tem um esquema lógico, que descreve os objetos mantidos pelo banco de dados, como esses se relacionam com outros objetos e quais as regras para manipulação e armazenamento desses objetos. Perceba que nesse nível estamos nos abstraindo de todo o detalhamento de armazenamento físico das informações. Dessa forma, o esquema conceitual é a descrição global do banco de dados que esconde os detalhes da estrutura física de armazenamento e concentra-se em descrever objetos, suas características, relacionamentos e restrições. Esse nível de abstração pode ser utilizado por analistas de sistemas e programadores no desenvolvimento de seus projetos e softwares. O nível externo ou nível de visão inclui um número de esquemas externos ou visões do usuário. Cada esquema externo envolve a visão de parte do banco de dados para um grupo de usuários. Cada visão tipicamente descreve a parte do banco de dados que um particular grupo de usuários está interessada e esconde o resto do banco daquele grupo. Esse nível de abstração pode ser utilizado, por exemplo, para consultores e/ou terceiros que necessitem do acesso à parte da base de dados da empresa. 1.3.2 Independência entre dados e aplicação A independência entre dados e aplicação pode ser definida como a capacidade de se conseguir alterar o esquema de armazenamento dos dados, tanto no nível interno quanto no nível conceitual, sem a necessidade de se alterar as aplicações. Dessa forma, desse conceito derivam-se outros dois: a independência lógica entre dados e aplicação e a independência física entre dados e as aplicações, apresentados a seguir. ••
34
Independência de dados física - é a capacidade de alterar o esquema interno sem alterar o esquema conceitual ou as aplicações. Como exemplo pode ser citada a situação na qual se necessita armazenar mais uma informação para os funcionários, o tipo sanguíneo, que até então não era registrado no sistema. Essa informação é adicionada na tabela de funcionários, assim, poderíamos dizer que temos 5 módulos do sistema de RH da empresa que manipulam as informações dos funcionários, porém, apenas um deles trabalha com essa nova informação. Dessa forma, não precisamos alterar estes módulos, apenas aqueles onde a informação será utilizada.
Princípios de Bancos de Dados ••
Independência de dados lógica - é a capacidade de alterar o esquema conceitual sem alterar o esquema externo ou as aplicações. Dessa forma, pode-se alterar as regras de armazenamento mantidas pelo SGBD, sem a necessidade de se alterar as aplicações. Para exemplificar essa característica, podemos alterar o número mínimo de parcelas geradas para uma compra, passando de 0 (zero) ou nenhuma) para uma parcela, ou ainda permitir que um cliente possa cadastrar ao invés de um endereço, vários endereços de entrega, isso modificando apenas o banco de dados, sem alterar as aplicações.
1.4 Arquiteturas de acesso a banco de dados ••
Plataformas centralizadas - na arquitetura centralizada, existe um computador com grande capacidade de processamento, o qual é o hospedeiro do SGBD e o emulador para os vários aplicativos. Essa arquitetura tem como principal vantagem a de permitir que muitos usuários manipulem grande volume de dados. Sua principal desvantagem está no seu alto custo, pois exige ambiente especial para mainframes e soluções centralizadas.
Figura 2.7 – Arquitetura centralizada
Minicomputador ou Mainframe Terminais locais
Modem Terminal remoto Modem Fonte: Elaboração do autor (2015).
35
Capítulo 2 ••
Sistemas de computador pessoal /PC – computadores pessoais trabalham em sistema stand-alone, ou seja, fazem seus processamentos sozinhos. No começo, esse processamento era bastante limitado, porém, com a evolução do hardware, tem-se hoje PCs com grande capacidade de processamento. Eles utilizam o padrão Xbase, e, quando se trata de SGBDs, funcionam como hospedeiros e terminais. Dessa maneira, possuem um único aplicativo a ser executado na máquina. A principal vantagem dessa arquitetura é a simplicidade.
••
Banco de dados cliente-servidor - na arquitetura cliente-servidor, o cliente (front_end) executa as tarefas do aplicativo, ou seja, fornece a interface do usuário (tela, e processamento de entrada e saída). O servidor (back_end) executa as consultas no SGBD e retorna os resultados ao cliente. Apesar de ser uma arquitetura bastante popular, são necessárias soluções sofisticadas de software que possibilitem: o tratamento de transações, as confirmações de transações (commits), desfazer transações (rollbacks), acesso a programas armazenados no SGBD (stored procedures) e gatilhos (triggers). A principal vantagem dessa arquitetura é a divisão do processamento entre dois sistemas, o que reduz o tráfego de dados na rede.
Figura 2.8 – Arquitetura cliente-servidor
consulta
pc
pc
Resultado da consulta
Cabo de rede
pc
Fonte: Elaboração do autor (2015).
36
Servidor de Banco de Dados
Princípios de Bancos de Dados Arquitetura de processamento distribuída em camadas - nessa arquitetura, a informação está distribuída em diversos servidores, servidores web, de aplicação e de banco de dados. Como exemplo, observe a Figura 9. Nesse caso, os servidores web se encarregam da disponibilização do serviço de acesso à rede, os servidores de aplicação de permitirem o acesso às aplicações e os servidores de banco de dados liberam os dados para as aplicações. Esta arquitetura pode ser utilizada com banco de dados único, na forma que apenas um servidor desse banco fornece os dados para as aplicações ou podese configurar com múltiplos servidores, caracterizando uma arquitetura com banco de dados distribuídos, em que cada servidor atua como no sistema cliente-servidor único, porém, as consultas oriundas dos aplicativos são feitas para qualquer servidor de banco de dados indistintamente. Caso a informação solicitada seja mantida por outro servidor ou servidores, o sistema encarrega-se de obter a informação necessária, de maneira transparente para o aplicativo, que passa a atuar consultando a rede, independente de conhecer seus servidores. Exemplos típicos são as bases de dados corporativas, em que o volume de informação é muito grande e, por isso, deve ser distribuído em diversos servidores. Porém, não é dependente de aspectos lógicos de carga de acesso aos dados, ou base de dados fracamente acopladas, em que uma informação solicitada vai sendo coletada numa propagação da consulta, em uma cadeia de servidores. A característica básica é a existência de diversos programas aplicativos consultando a rede para acessar os dados necessários, porém, sem o conhecimento explícito de quais servidores dispõem desses dados. Figura 2.9 – Arquitetura distribuída (N camadas)
Browser (Desktop) Browser (Desktop)
Servidor de dados Servidor Web
Console App.
BD Servidor de aplicação Servidor de dados
PDA Celular
BD
Smart Card Quiosque Camada de apresentação
Camada web
Camada de aplicação
Camada de dados
Fonte: Elaboração do autor (2015).
37
Capítulo 2 Nesta primeira seção, você teve um contato inicial com os conceitos do sistema gerenciador de banco de dados, ou simplesmente SGBD. Você deve ter percebido que em alguns momentos nos referimos aos dados armazenados como sendo tabelas. Entretanto, essa é uma notação normalmente utilizada quando se interage com banco de dados, mas que não reflete a realidade de mercado atual. Quando nos referimos a um SGBD, ele pode ter duas formas básicas de representação dos dados, as tabelas, base de um SGBD relacional, e as classes, que são a base de um SGBD orientado a objetos.
Seção 2 Tipos de sistemas de gerenciamento de banco de dados Dentro das características de cada ferramenta computacional de banco de dados, ou SGBD, os bancos podem ser divididos em duas formas de representação: banco de dados relacional e banco de dados orientado a objetos. Alguns autores poderiam ampliar essa definição, porém, essa definição está voltada para as ferramentas de gerenciamento de banco de dados existentes no mercado atual, ou seja, ao escopo de ferramenta computacional e à forma pela qual os dados são armazenados e gerenciados pelo sistema.
2.1 Banco de dados relacional Em um banco de dados relacional, os dados são armazenados em tabelas físicas, que possuem relacionamentos entre si. Essas estruturas físicas recebem o nome de tabelas relacionais. Uma tabela relacional é uma simples estrutura de linhas e colunas. Cada linha contém um mesmo conjunto de colunas ou campos, porém, as linhas seguem um determinado tipo de ordem.
Cada linha da tabela, formada por um conjunto de colunas, representa um registro (ou tupla). Os registros não precisam necessariamente conter dados em todas as colunas, ocorrendo a ausência de informação, caracterizando o que denominamos valor nulo.
38
Princípios de Bancos de Dados As colunas, identificadas por um nome e um tipo de dado, de uma tabela também são chamadas de campos. Os campos possuem características que definem o dado que será armazenado na tabela. Os sistemas de banco de dados possuem regras para consistir os dados que serão armazenados. Em um banco de dados relacional podem existir uma ou centenas de tabelas. O limitador é imposto exclusivamente pela ferramenta computacional, ou o SGBD utilizado. Comparando uma tabela relacional com um arquivo tradicional, do sistema de arquivos de um computador, identificam-se as seguintes diferenças: Quadro 2.1 - Diferença entre tabela relacional e arquivos
Diferença entre tabela relacional e arquivos Tabela relacional
Arquivos
As linhas de uma tabela não possuem nenhum tipo de ordenação. Os registros podem ser armazenados de A busca a um desejado dado na tabela não forma ordenada, por meio da aplicação de pode ser feita por indexação. algortimos de ordenação. Não é possível acessar uma linha da tabela com base na sua posição dentro da estrutura. Um campo da tabela é monovalorado, ou seja, formado por um único valor.
Cada registro do arquivo pode ser representado por vários valores de tipos diferentes.
O acesso aos dados de uma tabela pode obedecer aos mais variados critérios, sem que novas estruturas precisem ser criadas.
O acesso ao arquivo pode ser randômico, seqüencial ou indexado.
As tabelas são formadas por linhas e colunas. Os arquivos são formados por registros. Fonte: Elaboração do autor (2015).
Para ilustrar melhor uma tabela relacional, veja a representação a seguir:
Colunas /Campos
Linhas / Registros
39
Capítulo 2 Cada coluna de uma tabela obedece às regras definidas na sua criação, recebendo um tipo de dado, o qual representa o conjunto de valores que podem ser armazenados. Uma coluna do tipo numérico só poderá armazenar dados do tipo numérico. Ao se tentar inserir dados que não sejam numéricos, o SGBD recusará o cadastro, da forma pela qual a ferramenta executa essa tarefa. Cada ferramenta pode executar essa tarefa de forma diferente, porém, todas elas evitaram o cadastro indevido dos dados. Basicamente, cada coluna representa o tipo de cada dado, ou seja, as regras de entrada dos dados, evitando a incompatibilidade dos dados. Já as linhas representam todos os dados cadastrados, ou seja, um conjunto de colunas ou campos. Imagine o cadastro de alunos de uma escola. Os Lembre-se que as dados ou colunas que representam um aluno poderiam ser linhas representam o número de registros nomeados como: Matrícula, Nome, Idade e Sexo entre cadastrados na tabela e outros. as colunas representam campos que compõem a estrutura.
As linhas da tabela representam um registro único, ou seja, os dados de um único aluno, com os valores cadastrados:
Matricula
Nome
Idade
Sexo
1
Flavio Souza
35
M
Alguns campos da tabela, além do tipo de dados e do nome de identificação, possuem uma propriedade adicional. Esses campos recebem o nome de chave. 2.1.1 Chaves O conceito básico para identificar linhas e estabelecer relações entre linhas de tabelas de um banco de dados relacional é o de chave. Em um banco de dados relacional há ao menos dois tipos de chaves a considerar: a chave primária e a chave estrangeira. As chaves são restrições de integridades impostas ao banco de dados. 2.1.2 Chave primária A chave primária é a chave que identifica cada registro dando-lhe unicidade. Essa chave primária nunca se repetirá dentro da estrutura da tabela. Ela pode ser formada por um único campo ou coluna ou por vários campos. A chave primária é uma coluna ou conjunto de colunas cujos valores distinguem uma linha das demais dentro de uma mesma tabela.
40
Princípios de Bancos de Dados Assim, duas entidades (de mesmo tipo) não podem ter o mesmo valor para o atributo chave. Essa não é uma propriedade de uma extensão particular, mas é uma restrição para todas extensões do tipo de entidade. Por exemplo, o atributo CodigoAluno do tipo de entidade Aluno possui um valor diferente para cada entidade aluno (dois alunos não podem ter o mesmo código). Quando uma chave é formada por vários campos, diz-se que ela é uma chave primária composta. Uma tabela não pode ter mais de uma chave primária. Na tabela de Alunos vista anteriormente, o campo Matrícula é uma coluna sujeita a ser uma chave primária, uma vez que não poderão ser cadastrados vários alunos com a mesma matrícula. Entretanto, os campos Idade, Nome e Sexo podem ser repetidos durante o cadastro de alunos, dessa forma, não podem ser de forma alguma chaves primárias.
Você verá a forma de criar e definir essas chaves a partir da Unidade 5. Por isso, não se preocupe por enquanto com a forma de como se fazer isso em um sistema gerenciador de banco de dados, mas sim com a finalidade de cada uma dessas estruturas dentro de um banco de dados relacional. 2.1.3 Chave estrangeira A chave estrangeira corresponde aos campos, ou campo, de uma tabela, cujos valores cadastrados aparecem necessariamente na chave primária de outra tabela. A chave estrangeira é o mecanismo que permite a implementação de relacionamentos em um banco de dados relacional. Uma chave estrangeira impõe algumas restrições ao banco de dados modelado, são elas: ••
quando há a inclusão de uma linha na tabela que contém a chave estrangeira é necessário que o valor inserido exista na tabela principal, caso contrário, a inclusão não poderá ser realizada;
Observe a figura a seguir. Os valores inseridos na tabela “pai” ou principal estão todos cadastrados na tabela “filho” ou secundária. Caso tentássemos inserir uma cidade não cadastrada na tabela aluno, o SGBD emitiria uma mensagem de erro.
41
Capítulo 2 Figura 2.10 – Exemplo de inclusão de uma linha na tabela Tabela Aluno
CodigoAluno 1
João da Silva
Nome
Idade
2
Maria Silveira
33
20
3
Pedro Souza
34
10
35
CodigoCidade 10
Tabela Cidade
CodigoCidade
Cidade
10
Florianópolis
20
Brasília
Fonte: Elaboração do autor (2015).
••
quando há alteração de valores da chave estrangeira é necessário que o novo valor cadastrado exista na chave primária da tabela principal, caso contrário, a alteração não será realizada.
Observe na figura a seguir, que a cidade do aluno 3 foi alterada. Essa alteração só foi possível porque a cidade cadastrada existia na tabela secundária. Figura 2.11 – Exemplo de alteração de valores da chave estrangeira
CodigoAluno 1
Nome
João da Silva
Idade
CodigoCidade
2
Maria Silveira
33
20
3
Pedro Souza
34
20
35
10
CodigoCidade
Cidade
10
Florianópolis
20
Brasília
Fonte: Elaboração do autor (2015).
••
quando há exclusão de um dado da tabela principal, cujo valor exista em alguma tabela relacionada, a exclusão deve ser cancelada ou o registro na tabela relacionada também deverá ser excluído;
No momento da criação da chave estrangeira, temos a opção de criar, permitindo a exclusão dos dados envolvidos ou não. Não permitindo a exclusão, o SGBD emitirá uma mensagem de erro, acusando que existem dados relacionados. Permitindo a exclusão, todos os dados relacionados serão excluídos.
42
Princípios de Bancos de Dados Figura 2.12 – Exemplo de exclusão de um dado da tabela
CodigoAluno 1
Nome
João da Silva
Idade 35
CodigoCidade 10
CodigoCidade 10
Cidade
Florianópolis
Fonte: Elaboração do autor (2015).
••
quando há alteração de um campo da chave primária, que é chave estrangeira, para outra tabela, a alteração deve se refletir a todas as tabelas relacionais, contendo o novo valor do campo.
Exemplo: observe que foi alterado o código da cidade de Brasília para 30, essa alteração se estendeu a todos os registros relacionados. Figura 2.13 – Exemplo de alteração de um campo de chave
CodigoAluno 1
Nome
João da Silva
Idade
CodigoCidade
2
Maria Silveira
33
30
3
Pedro Souza
34
30
35
10
CodigoCidade
Cidade
10
Florianópolis
30
Brasília
Fonte: Elaboração do autor (2015).
Continuando no estudo da estrutura de uma banco de dados relacional, a definição de uma chave estrangeira é essencial ao sistema de tabelas relacionais. A chave estrangeira é o elemento do SGBD por meio do qual se possibilita a criação de um relacionamento entre tabelas. Imagine um sistema computacional para o cadastramento, cursos e matrículas de alunos. As tabelas de Alunos e Cursos representam diretamente os dados cadastrados. A entrada de dados acontece diretamente nas tabelas. Já na tabela de Matrículas, as linhas ou registros devem representar o aluno e o curso no qual se matriculou. Diferente das tabelas anteriores, a tabela de Matrícula tem dois relacionamentos diretos: um com o aluno que está cadastrado na tabela de Alunos e outro com o curso que está cadastrado na tabela de Cursos.
43
Capítulo 2 Sendo assim, diz-se que a tabela de matrículas está relacionada com a tabela de alunos e a de cursos. Isso é possível porque a tabela de Matrículas possui uma chave estrangeira para tabela de Alunos, que se relaciona com o campo matrícula dessa tabela, e outra chave estrangeira para tabela de cursos, que se relaciona com o campo de Código dessa tabela. Veja a seguir uma representação da chave estrangeira: Figura 2.14 – Representação de Chaves Estrangeiras
Alunos
Cursos
CodigoAluno
CodigoAluno
Nome
Descricao
Idade
Vagas
Sexo
Chave estrangeira
Alunos CodigoAluno CodigoCurso
Chave estrangeira
Data Fonte: Elaboração do autor (2015).
Sempre que se for cadastrar uma matrícula de aluno, não é necessário recadastrar os dados dele e nem mesmo do curso. A tabela de matrícula cadastrará como aluno a matrícula do aluno desejado na tabela de alunos e em curso o código do curso originário da tabela de cursos. Isso garante uma propriedade importante de um SGBD: a integridade dos dados. Como os campos estão relacionados, não será possível cadastrar numa matrícula um aluno inexistente ou um curso inexistente.
2.2 Banco de dados orientado a objetos Hoje, o banco de dados orientado a objetos é um fator emergente que integra o banco de dados e a tecnologia de orientação a objetos. Os bancos de dados orientados a objetos iniciaram-se primeiramente em projetos de pesquisa nas universidades e centros de pesquisa. Em meados dos anos 80, eles começaram a se tornar produtos comercialmente viáveis e se mantêm em constante evolução até os dias atuais.
44
Princípios de Bancos de Dados O desenvolvimento dos sistemas de gerenciamento de banco de dados orientado a objetos (SGBDOO) teve origem na combinação de ideias dos modelos de dados tradicionais, SGBD, e de linguagens de programação orientada a objetos, LPOO.
Os modelos de dados orientados a objetos têm um papel adicional nos SGBDs, pois são mais adequados para o tratamento de objetos complexos, como: textos, gráficos e imagens. 2.2.1 Modelos de dados orientados a objetos Resumidamente, pode-se dizer que orientação a objetos corresponde à organização de sistemas como uma coleção de objetos que integram estruturas de dados e comportamentos. Dessa forma, um banco de dados orientados a objetos não possui a sua arquitetura baseada em tabelas, como no sistema relacional, mas sim em objetos. Afinal, o que são esses objetos?
2.3 Objeto Os objetos são abstrações de dados do mundo real, com um estado descrito por atributos que podem apenas ser acessados ou modificados por meio de operações definidas pelo criador do objeto. Um objeto individual é chamado de instância ou ocorrência de objeto. A parte estrutural de um objeto é similar à noção de tabelas no modelo relacionamento. Ou seja, fazendo uma correlação com o banco de dados relacional, a sua estrutura é formada por tabelas, que são a junção de linhas e colunas. As informações de cada tabela são definidas pelas estruturas de cada campo ou coluna. Em um objeto não há a figura de uma tabela, mas sim um conjunto de atributos ou propriedades que definem as suas características e a forma pela qual ele pode ser acessado. A forma de acesso acontece por meio dos métodos que foram atribuídos a ele, caso contrário, seus valores não podem ser modificados. Para quem cria as regras dos objetos, é importantíssimo que sejam avaliadas todas as possibilidades de uso desse objeto. Já para quem usa esses objetos, há um alto grau de abstração, pois não precisa saber como essas regras foram implementadas, mas é essencial que saiba como utilizá-las. Importante: vale destacar que, em alguns casos, a mesma pessoa responsável pela definição do objeto pode ser o seu usuário final.
45
Capítulo 2 De forma sucinta, pode-se imaginar que o criador do objeto define as suas propriedades e as regras de uso dele. Já o usuário do objeto é o responsável por aplicá-lo nas resoluções dos seus problemas. Em uma solução orientada a objetos podem ser utilizados vários objetos, como se fossem as tabelas de um modelo relacional. A esse conjunto de objetos chamamos de classe. Cada classe segue as regras implementadas nos objetos que a compõem. O objeto pode ser visto como a descrição ou especificação de objetos. Esse objeto possui duas partes: a interface, que é visível para o usuário, e a implementação, visível só para o usuário construtor do objeto. Representação do objeto Em um modelo relacional, cada tabela representa unicamente a estrutura dos dados que serão armazenados ali e as regras de integridade, como chaves primárias e estrangeiras. Em um modelo orientado a objetos, essa representação é alterada significativamente, pois além dos dados armazenados, também são armazenadas as regras de uso do objeto, com as operações e validações que serão permitidas. Veja a figura a seguir, que representa um objeto Aluno: Figura 2.15 – Representação do Objeto Aluno
Aluno Matricula Nome Idade Sexo Fonte: Elaboração do autor (2015).
Observe que não há mais a representação em forma de tabelas, com linhas e colunas. O que temos é um único objeto Aluno, o qual possui como propriedades a matrícula, o nome, a idade e o sexo. Os métodos representam a forma de acesso ao objeto. É pelos métodos que se executa uma determinada tarefa do objeto ou se alteram as suas propriedades. Os métodos podem ser do tipo construtor, recuperador ou modificador.
46
O objeto Aluno deve conter as regras de uso desse objeto. As propriedades do objeto só podem ser alteradas pelos métodos implementados pelo criador do objeto. Assim, o objeto Aluno passa a ter uma estrutura mais complexa, como a seguir:
Princípios de Bancos de Dados Figura 2.16 – Objeto Aluno e seus métodos
Aluno Matricula Nome Idade Sexo AlterarMatricula AlterarNome AlterarIdade AlterarSexo RecuperarMatricula RecuperarNome RecuperarIdade RecuperarSexo LerDados VisualizarDados Fonte: Elaboração do autor (2015).
Para cada atributo de Alunos, há um método para alterar o seu valor e um método para visualizar o seu valor atual. Essas são as únicas formas de se alterar e visualizar os dados de um aluno cadastrado. Além dos métodos modificadores e recuperadores, há um método para cadastrar todos os dados do aluno (LerDados) e outro para listar os dados de um determinado aluno (VisualizarDados). Muitos outros métodos poderão ser agregados a esse objeto, com as mais variadas finalidades. O usuário que for interagir com esse objeto não precisa saber como essas regras foram implementadas ou criadas, porém, precisa saber usá-las de forma a alcançar os resultados esperados. Para o cadastramento de vários alunos, não há uma estrutura como tabela, mas sim um conjunto de instâncias (criações) de objetos Alunos, conforme pode ser visto na figura a seguir:
47
Capítulo 2 Figura 2.17 – Instâncias de Aluno
Alunos Aluno Aluno Matricula Nome Aluno Matricula Idade Nome Matricula Sexo Idade Nome Sexo Idade Sexo Fonte: Elaboração do autor (2015).
Para efeito didático, para o nosso estudo será adotado o modelo relacional, por se tratar ainda do modelo mais usado no mercado de trabalho. Entretanto, os conhecimentos adquiridos poderão ser aplicados a quaisquer tipos de banco de dados, independente de ferramenta, tipo ou fabricante.
Seção 3 Introdução à modelagem de dados Sobre a modelagem de dados, será melhor detalhada no próximo capítulo desta Unidade de Aprendizagem, porém, como o tema é pertinente aos conceitos de banco de dados, vamos trabalhar um pouco e já construir uma visão do que se trata a modelagem de dados. Um modelo de banco de dados é uma descrição dos tipos de informações que estão armazenadas em um banco de dados. No caso da tabela de alunos, vista na Seção 2, o modelo de dados poderia informar que o banco de dados armazena informações de alunos, para cada aluno, são armazenados a sua matrícula, nome, idade e sexo. Em um modelo de dados não são informados os valores dos campos cadastrados para cada tabela, mas sim a estrutura que compõe a tabela de maneira formal e padronizada. A criação de um modelo de dados deve seguir algumas regras para que qualquer pessoa possa compreender a sua finalidade. Para tanto, é necessária a utilização de uma metodologia de modelagem de dados. Existem, basicamente, duas formas de se representar um modelo de banco de dados: textuais e gráficas.
48
Princípios de Bancos de Dados O modelo criado para um banco de dados é denominado esquema de banco de dados. Para que você possa compreender melhor as definições apresentadas, veja a figura a seguir: Figura 2.18 – Esquema de Banco de Dados
ESQUEMA Modelo de Dados Descrição formal das Tabelas
Fonte: Elaboração do autor (2015).
Em um modelo de banco de dados, normalmente são considerados dois níveis de representação: o modelo conceitual e o modelo lógico.
3.1 Modelo conceitual
O modelo conceitual é conhecido como Modelo de Entidade-Relacionamento (MER) e é uma descrição do banco de dados que será projetado de forma independente da ferramenta computacional de SGBD. Esse modelo representa os dados de cada tabela sem se preocupar com a forma pela qual esses serão registrados no SGBD utilizado. A estrutura de cada tabela passa a ser referenciada como entidade, que compõe o esquema de banco de dados, porém, de forma independente do SGBD a ser utilizado. O modelo ainda adota uma técnica chamada de entidade-relacionamento que descreve as entidades, atributos e relacionamentos. Figura 2.19 – MER e duas tabelas
Atributo Relacionamento Idade
Alunos
Nome
Entidade
Sexo n
Matricula
1
Vagas
Cursos Descricao
Codigo
Fonte: Elaboração do autor (2015).
49
Capítulo 2
3.2 Modelo lógico O modelo lógico é uma representação do esquema de banco de dados que leva em consideração a ferramenta computacional que será utilizada para o projeto do banco de dados. Sua principal finalidade é descrever as tabelas que compõem o banco de dados e as suas respectivas colunas, sem se preocupar em demonstrar o tipo de relacionamento entre elas. Se o SGBD for relacional, ele terá um tipo de representação. Caso seja orientado a objetos, ele terá outra representação. Em outras palavras, o modelo torna-se dependente direto do SGBD utilizado. - Como esta unidade de aprendizagem se baseia no modelo relacional, que é o padrão para o mercado atual de SGBDs, adotaremos o modelo lógico relacional, que se estrutura a partir de tabelas de banco de dados. Essas tabelas compõem o esquema de banco de dados e, para cada tabela, os nomes das colunas. Veja o mesmo modelo conceitual apresentado anteriormente, agora em uma representação de modelo lógico: Figura 2.20 – MER nível lógico
ALUNOS Matricula Integer NN (PK) Nome Char(20) N Idade Integer NN Sexo Char(1) NN Codigo Integer NN (PFK)
CURSOS Relacionamento
Codigo Integer NN (PK) Descricao Char(20) NN Vagas Integer NN
Fonte: Elaboração do autor (2015).
Perceba que o nível de detalhamento das informações é bem maior. São indicadas as colunas que são chaves primárias (PK), as que pertencem como chaves estrangeiras (FK), tipos de dados, entre outras informações. Cabe observar que um modelo não exclui o outro, ou seja, em um projeto de banco de dados pode-se adotar ambos os modelos, dependendo do grau de representação que o especialista deseja implantar. Para cada um dos modelos utilizados há um conjunto de regras para auxiliar na definição de como os dados se comportam. No próximo capítulo, vamos tratar especificamente do projeto para banco de dados, abordando mais detalhadamente como construir tanto o modelo conceitual como o lógico, e ainda como gerar a base de dados, etapa também conhecida como modelagem no âmbito físico, utilizada para implementar o projeto do banco de dados.
50
Capítulo 3 Modelagem de Dados e Projeto de Banco de Dados
Habilidades
•• Compreender o processo de um projeto de banco de dados •• Identificar a finalidade e os elementos presentes no modelo de dados em termos conceitual e lógico •• Construir um modelo de dados conceitual e lógico
Seções de estudo
Seção 1: Como projetar um banco de dados Seção 2: Modelagem de dados
51
Capítulo 3
Seção 1 Como projetar um banco de dados Como você observou na unidade anterior, um banco de dados é formado por muitas tabelas, cuja principal finalidade é proporcionar um acesso rápido aos dados e garantir a sua integridade. O ato de projetar uma solução de banco de dados é uma tarefa muito importante, pois deverão estar representadas todas as informações manipuladas por uma determinada organização. Junto a essas informações há um conjunto de regras que são realizadas por cada usuário no manuseio com os dados. Essas regras, na maioria das vezes, são de conhecimento e domínio de cada usuário a respeito das tarefas executadas. Transcrever essas tarefas, informações, dados e regras de utilização consiste em um processo bem detalhado, que deve ser realizado de forma consciente, exigindo um forte padrão de análise e documentação das metodologias utilizadas. Dessa forma, nesta unidade você estudará algumas regras que devem ser adotadas ao se modelar um projeto de banco de dados e quais as formas de se representar o relacionamento entre eles. Projetar um banco de dados significa estruturar a solução que será implementada mais tarde em um SGBD. Ou seja, define-se o que será feito e como será feito antes de se por a mão realmente na massa. O SGBD é uma ferramenta computacional que apenas formalizará as regras definidas no projeto da solução de banco de dados. Regras definidas de forma inconsistente serão criadas pelo SGBD de forma inconsistente. E é aí que está o perigo.
A ação de projetar o esquema de banco de dados objetiva especificar as tabelas que serão criadas, que campos essa tabela terá, quais os tipos de cada campo e de que forma essas tabelas se inter-relacionam. Essa tarefa deve ser realizada de forma a se evitar, ao máximo, constantes alterações, pois alterações no modelo de banco de dados significam retrabalho e, consequentemente, um maior custo de produção.
52
Princípios de Bancos de Dados Sendo assim, algumas regras devem ser seguidas para definição do modelo de banco de dados: ••
Determinar qual a finalidade do banco que está sendo modelado. Isso é importante para definição de quais dados serão armazenados;
••
Dividir o problema em várias tabelas. Dividir em quantas tabelas forem necessárias, de forma que representem um conjunto de dados semelhantes ao mundo real, gerando regras específicas para cada uma delas e definindo um formato para cada campo;
••
Determinar os relacionamentos. Gerar as chaves primárias e estrangeiras de cada tabela, de forma que a integridade dos dados seja mantida;
••
Refinar o modelo criado. Validar e comparar os resultados obtidos com os valores do mundo real.
Assim como você teve acesso a um conjunto de regras para se alcançar o objetivo desejado, veja os problemas mais comuns em um projeto de banco de dados: ••
Número excessivo de campos nas tabelas. Certifique-se de que os campos definidos para uma tabela dizem realmente respeito ao assunto;
••
Campos que normalmente não são cadastrados. Isso é um grande indício de que essas informações estão no lugar errado e que devem ser melhor estruturados;
••
Várias tabelas com os mesmos campos. Isso gera redundância de informação, podendo acarretar em informações desatualizadas;
••
Tabelas sem chaves primárias e estrangeiras. Conforme abordado na unidade 2, as chaves primárias e estrangeiras são elementos muito importantes no banco de dados. Durante o projeto do banco de dados deve-se analisar individualmente cada dado, identificando claramente a função de cada um deles, elegendo-os como membros participantes de chaves primárias e/ou estrangeiras.
Você está lembrado do conceito de abstração abordado no Capítulo 2? Pois bem, aqui você deverá coloca-lo em prática, uma vez que durante a etapa de projeto de um banco de dados é imprescindível que você tenha seu foco nos dados e na sua função dentro das atividades que estão sendo informatizadas, sem se preocupar ou se deixar influenciar por aspectos relativos ao software que vai acessar esses dados. Então, devemos nos abstrair dos elementos de software e nos concentrar no armazenamento dos dados. Lembre-se de que um determinado dado pode ser acessado por diversas aplicações, conforme abordado nos Capítulos 1 e 2.
53
Capítulo 3 Com o advento dos SGBDs se conseguiu a independência entre programas e dados, dessa forma, o endereço de um cliente, por exemplo, não pertence mais exclusivamente a um determinado setor ou software da empresa, mas poderá ser acessado pelo software de mala direta, pelo de cobrança, de crediário, e tantos quantos necessitarem dessa informação. É muito importante que você se atenha ao escopo de um profissional de banco de dados, principalmente aquele que vai modelar o projeto. Quando esse profissional executar as suas tarefas, partirá de um modelo real de processos, que são executados no dia a dia por várias pessoas, as quais, por algum motivo, desejam se automatizar por meio do computador. As informações que fazem parte do escopo do problema a ser resolvido precisam ser bem compreendidas por quem vai projetar o banco de dados, pois a solução deve refletir o mesmo funcionamento atual, de forma mais rápida, precisa e automática. Basicamente, a solução esperada pelo cliente tem que ser transformada em um conjunto de tabelas relacionais que representem fielmente o processo atual e que, de preferência, agilizem as tarefas executadas diariamente e garantam a integridade dos dados.
Seção 2 Modelagem de dados Para que se consiga mapear as necessidades de um ambiente real para uma ferramenta de SGBD, devemos utilizar uma técnica denominada modelagem de dados, a qual consiste em agrupar as informações e as regras presentes neste ambiente com os elementos disponibilizados pelo banco de dados. Não importa se a solução para o ambiente real é simples, de fácil entendimento, ou não: a correta modelagem dos dados tornará a sua aplicação mais robusta, com melhor performance e de mais fácil manutenção. Para efeito didático, apresentamos os conceitos básicos sobre modelagem de dados, pois esse assunto é muito abrangente e Vale a pena lembrar que uma das finalidades de se projetar extenso. Aqui, você o conhece de forma mais é representar os assuntos objetiva, tendo em vista a aplicabilidade, imediata, pertinentes ao ambiente real e dos conceitos aprendidos. à forma como interagem entre si em entidades e os seus relacionamentos no ambiente do SGBD, de forma que atendam as necessidades do ambiente real.
54
As finalidades de se projetar um problema são as mais variadas. Com a modelagem pode-se:
Princípios de Bancos de Dados ••
representar formalmente o ambiente observado;
••
obter uma visão global dos dados envolvidos no ambiente;
••
documentar e formalizar;
••
fornecer processos de validação.
Ao se construir um modelo de dados estamos projetando um ambiente real para um ambiente informatizado, isso implica transformar o mundo real em representações formais aceitas por um SGBD. Segundo Elmasri (2005) em seu capítulo 3, o projeto de um banco de dados normalmente é constituída de três etapas: 1. Conhecimento e entendimento do ambiente real: como os profissionais que vão projetar a solução para o ambiente podem não conhecer em detalhes como este funciona e quais são as suas características, é realizada a modelagem conceitual dos dados, quando é gerado um documento com a representação gráfica de quais são as necessidades de informação e as regras que estão presentes e afetam os dados. Esse documento é denominado modelo conceitual de dados. Nessa etapa, não se leva em consideração aspectos de implementação da base de dados ou de como a solução vai ser implementada. 2. Detalhamento técnico do modelo conceitual de dados: Uma vez que na etapa 1 foram identificados os dados a serem registrados, quais as regras que o ambiente real possui e demais detalhamentos, agora esse modelo conceitual vai ser detalhado em termos técnicos, ou seja, agora será verificado de que forma vamos implementar essas necessidades numa ferramenta de SGBD específica. Portanto, nessa etapa o modelo de dados passa a ser dependente da ferramenta de SGBD que foi escolhida para ser implementado. Nessa etapa, é gerado um novo documento, também baseado numa representação gráfica dos dados e como estarão implementados no SGBD, esse documento é denominado modelo de dados lógico. 3. Modelagem física da base de dados: Nesta etapa o modelo de dados lógico será convertido para sentenças na linguagem do SGBD, normalmente a linguagem SQL, para que possa ser implementado na ferramenta de banco de dados. O resultado dessa etapa é um documento texto onde estão descritos todos os comandos para que o modelo de dados possa ser corretamente implementado no banco de dados.
55
Capítulo 3 A seguir serão detalhados como gerar o modelo de dados conceitual, lógico e físico.
2.1 Construindo o modelo conceitual de dados Conforme visto anteriormente, o modelo conceitual de dados consiste numa representação gráfica dos dados e das regras que atuam sobre esses dados no ambiente real. Cabe ressaltar que nessa etapa de trabalho é muito importante que o usuário final participe ao máximo dos trabalhos, portanto, não é utilizado nenhum termo técnico da área de SGBD, o linguajar utilizado deve permitir que o usuário entenda o que se está realizando. Por quê? Por uma razão muito simples: se os profissionais que projetam o banco de dados não entendem da área de negócios que estão informatizando, ou não possuem detalhamento suficiente, de que outra forma conseguir as informações necessárias para o trabalho senão via usuário? Para executar a etapa de modelagem, vamos reduzir os elementos do mundo real em três componentes: a. Entidade: Uma entidade vai representar os assuntos pertinentes ao ambiente que se está modelando. Em uma loja de calçados, para a área de vendas os assuntos relacionados a esta área podem ser Produtos (sapatos, botas, meias etc.), clientes, vendedores, venda, crediário, entre outros. Dessa forma, uma entidade é um elemento por meio do qual serão armazenadas as informações referentes ao assunto abordado. b. Atributos: Cada assunto identificado deve possuir características, ou seja, o que se deseja armazenar em termos de informações sobre o assunto. A essas características é que denominamos atributos. São exemplos de atributos para o assunto vendedor: código do vendedor, seu nome, data de nascimento, salário, percentual de comissão etc. c. Relacionamentos: Os assuntos presentes num determinado ambiente real fazem referência a outros assuntos. Se perguntássemos a você se cliente tem algo a ver com produto, ou se venda tem algo a ver com crediário, ou ainda, se vendedor tem algo a ver com venda, certamente a sua resposta seria sim. Isso porque os assuntos em um ambiente possuem ligações entre si e também no ambiente computacional devemos representar essas ligações, e o fazemos estabelecendo o que formalmente chamamos de relacionamento. Um relacionamento deve estabelecer uma relação entre dois assuntos, retratando como esses assuntos se comportam no ambiente real.
56
Princípios de Bancos de Dados 2.1.1 Definindo as entidades e seus atributos As entidades vão representar os assuntos sobre os quais se deseja armazenar as informações por meio dos sistemas de informação. Ao se identificar as necessidades de um determinado ambiente real, estaremos agrupando as informações que devem ser armazenadas em entidades. Dessa forma, para um ambiente de vendas podemos ter a necessidade de armazenar as informações do nome do empregado, seu endereço, carteira de habilitação, telefone de contato, a data em que a loja efetuou as vendas para ele, qual o vendedor que realizou a venda. Nesse caso, vamos agrupar as informações por assuntos, ou melhor dizendo, por entidades. Podemos definir, para essa situação, duas entidades: ••
empregado: atrelado a ela os atributos nome, endereço, CNH e Telefone de Contato;
••
venda: tendo atrelado a essa os atributos código da venda, data e nome do vendedor.
A Figura 3.1 representa a entidade empregados e seus atributos graficamente no modelo de dados Figura 3.1- Entidade e seus atributos
Nome
CNH (0,1)
Empregado Endereço
TelefoneContato Rua
Cidade
Número Fonte: Elaboração do autor (2015).
Para representar os atributos nas entidades devemos estar atentos às características que esses atributos possuem, de forma a representá-los adequadamente. Que características são essas? Vejamos a seguir: ••
Atributo simples ou composto: Ao definirmos um atributo, temos que verificar se ele representa a informação de forma elementar, ou se nessa informação existem outras mais embutidas nele. Veja o exemplo da Figura 3.1. O atributo endereço é uma informação composta, porque nele estão representadas outras informações, como nome da rua, número, cidade.
57
Capítulo 3 É importantíssimo que todos os atributos das entidades sejam representados como atributos simples, ou seja, que representem a informação na sua forma mais elementar.
••
Atributo obrigatório ou opcional: Perceba que para o atributo CNH, da Figura 3.1 existe uma indicação (0,1). Essa indicação nos informa que esse atributo pode não conter a informação, ou seja, um determinado empregado pode não ter um número de CNH, ou conter uma informação, que seria o número da CNH. Os demais atributos, por não conterem indicação nenhuma são de caráter obrigatório, ou seja, ao se registrar um empregado, obrigatoriamente deve ser informado o nome, o telefone e os dados do endereço e, opcionalmente, pode ser informado a sua CNH. O fato de um atributo ser de caráter opcional (0), não quer dizer que ele é menos importante que outros atributos, mas nos indica que no momento do registro do elemento, aquela informação pode não estar disponível, sendo preenchida posteriormente ou não ser preenchida.
••
58
Atributo monovalorado ou multivalorado: Digamos que haja a necessidade de se armazenar as informações dos dependentes do empregado, como seu nome, grau de parentesco, data de nascimento. Inicialmente, podemos incluir essas informações junto com a entidade empregado, mas temos que perceber uma característica muito importante para o dependente e a identificamos pelo seguinte questionamento: para cada empregado pode haver a necessidade do registro de mais de um dependente? Caso a resposta for afirmativa, concluímos que os dados dos dependentes são multivalorados para os empregados, ou seja, cada empregado pode ter mais de um dependente. Nesse caso, nós vamos representar os dependentes numa outra entidade, específica para ele conforme demonstra a Figura 3.2 e, posteriormente, vamos relacionar o empregado ao seu dependente, mas os relacionamentos são tratados mais adiante neste capítulo.
Princípios de Bancos de Dados Figura 3.2 – Atributos Multivalorados em outra entidade
Nome
CNH (0,1)
Empregado TelefoneContato
Endereço Rua
Dependente
Cidade
nome grau nascimento
Número Fonte: Elaboração do autor (2015).
Os atributos que forem multivalorados para a entidade, devem ser representados numa segunda entidade.
2.1.2 Definindo um identificador único para a entidade Para cada entidade que for incluída no modelo de dados, devemos eleger um, ou mais atributos que identifiquem unicamente cada elemento daquela entidade e, como esse(s) atributo(s) vão identificar unicamente cada elemento, eles são por natureza de caráter obrigatório. No exemplo apresentado para a Figura 3.2, o atributo CNH não pode ser eleito como o identificador único para os empregados, uma vez que pode haver diversos empregados que não possuem CNH. O nome do empregado pode ser um candidato a identificador único desta entidade, porém, temos que nos ater ao fato de que pode haver dois ou mais empregados com o mesmo nome. Basta fazer uma breve pesquisa e você verá quantos Joãos da Silva existem por aí! Dessa forma, o nome do empregado não é uma boa alternativa a essa situação, uma vez que, se for eleito como identificador único, jamais poderemos cadastrar no sistema duas pessoas com o mesmo nome. O SGBD não aceitará a situação. Podemos então complementar esse modelo, acrescentando uma nova informação ao empregado, como por exemplo o número da sua carteira de trabalho e eleger esse atributo como identificador único, uma vez que cada trabalhador possui um número único na sua carteira de trabalho e esse documento é obrigatório de ser apresentado para se empregar na empresa. O identificador único é representado graficamente por meio de um círculo preenchido, para diferenciá-lo dos demais atributos que não fazem parte do identificador único. A Figura 3.3 representa o empregado com o atributo CTPS eleito como identificador único para cada empregado.
59
Capítulo 3 Figura 3.3 – Representação do identificador único
Nome
CNH (0,1) Empregado Endereço
TelefoneContato CTPS Rua
Cidade
Número Fonte: Elaboração do autor (2015).
Em muitas situações, uma entidade pode necessitar de mais de um atributo para conseguir identificar cada um de seus elementos. Observe a entidade representada na Figura 3.4. Figura 3.4 – Entidade com identificador único composto
Capacidade
NúmeroCorredor
Estante NúmeroEstante Fonte: Elaboração do autor (2015).
Perceba que a estante necessita além do seu númeroEstante, também do NúmeroCorredor para ser identificada corretamente, uma vez que podem existir várias estantes número 1, mas em corredores distintos. Dessa forma, tem-se a necessidade de registrar uma numeração, independente de corredor, para cada estante, uma vez que existe a estante 1 do corredor 1, a estante 1 do corredor 3, e assim por diante. Lembre-se: Toda entidade deve ter algum atributo definido como identificador único!
2.1.3 Definindo os relacionamentos entre as entidades As entidades, ou assuntos, presentes num ambiente real interagem entre si, ao modelarmos essa característica o faremos por meio dos relacionamentos, por exemplo, ao modelarmos países e suas unidades federativas (UF), vamos compor duas entidades, uma para país outra para UF, uma vez que em um país podem haver muitas UFs e, como cada estado pertence a um país, devemos relacionar essas duas entidades. Esse relacionamento está demonstrado na Figura 3.5.
60
Princípios de Bancos de Dados Figura 3.5 – Exemplo de Relacionamento
UF
PAIS
UF_Pais
Fonte: Elaboração do autor (2015).
Ao se estabelecer essa interação, devemos estar atentos à forma como essa interatividade acontece, e projetarmos adequadamente o modelo de dados de forma a representar fielmente o ambiente que está sendo informatizado. Definimos essas interações por meio de um elemento denominado cardinalidade, vejamos. Heuser (2008,p.26) alerta de que “...Para fins de projeto de banco de dados, uma propriedade importante de um relacionamento é a de quantas ocorrências de uma entidade podem estar associadas a uma determinada ocorrência através do relacionamento.” 2.1.4 Tipos de cardinalidade As cardinalidades classificam-se em mínima e máxima. Veja a seguir as características de cada uma delas. Cardinalidade mínima A cardinalidade mínima define, no mínimo, com quantos elementos da entidade B cada elemento da entidade A vai se relacionar. Dessa forma, estamos definindo se o relacionamento entre duas entidades é obrigatório ou não. Para que você compreenda melhor a cardinalidade mínima entre as entidades, observe o exemplo a seguir, que representa a relação entre País e Unidades Federativas (UF). Figura 3.6 - Exemplo de cardinalidade mínima
Cada PAIS pode se relacionar ou não com uma UF, ou seja, cada país pode se relacionar no mínimo com nenhuma UF.
UF
(0,n)
Cada UF deve se relacionar com no mínimo um PAIS.
UF_Pais
(1,1)
PAIS
Fonte: Elaboração do autor (2015).
61
Capítulo 3 Analisando a figura, é possível perceber que um país pode ser cadastrado sem que haja uma unidade federativa para ele. Por isso, a representação de um zero (0) na cardinalidade mínima da entidade País para entidade UF. Por outro lado, na cardinalidade mínima de UF para País há uma cardinalidade mínima de 1. Ou seja, uma unidade federativa deve pertencer a um único país. Dessa forma, o diagrama acima pode ser lido como: Cardinalidade mínima: ••
Cada País pode ou não possuir UF;
••
Cada UF deve pertencer a um único País. Uma abordagem prática do comportamento dos países com relação a suas Unidades Federativas e vice-versa, abordado acima, pode ser observada na Figura 3.7.
Figura 3.7 - Representação do relacionamentos entre os elementos de duas entidades
PAÍSES
O P1,U3 O P1,U1
1
O P2,U4 OP5,U2
4
O P3,U5
Relacionamentos de Países e UFs
UF
Fonte: Elaboração do autor (2015).
Perceba que cada país pode ou não estar associado à UF. No caso do país 4, não está associado a nenhuma UF. Já cada UF deve obrigatoriamente estar associada a um PAÍS, portanto, não há a possibilidade de encontrarmos uma UF sem estar atrelada a um PAÍS.
62
Princípios de Bancos de Dados
Cardinalidade máxima A cardinalidade máxima define a quantidade máxima de ocorrências entre as entidades que participam de um relacionamento, ou seja, no máximo com quantos elementos da entidade B cada elemento da entidade A vai se relacionar. Observe na Figura 3.8 as mesmas entidades País e UF, porém, representadas pela cardinalidade máxima. Figura 3.8 Exemplo de cardinalidade máxima
Cada PAIS pode manter no máximo muitas UF.
UF
(0,n)
Cada UF pode ser mantida por no máximo um PAIS.
UF_Pais
(1,1)
PAIS
Fonte: Elaboração do autor (2015).
Analisando a figura, é possível perceber que um país pode ter várias unidades federativas. Por isso a representação por meio do símbolo N. Por outro lado, na cardinalidade máxima de UF para País continua com a cardinalidade máxima de 1. Ou seja, uma unidade federativa deve pertencer a um único país. Dessa forma, o diagrama acima pode ser lido como: Cardinalidade máxima: ••
Cada País pode não possuir, possuir uma ou várias UF;
••
Cada UF deve pertencer a um único País.
Como pode ser percebido na Figura 3.8 que explana de maneira prática o estabelecimento dos relacionamentos, o país P4 não está ligado a nenhuma UF, já o país P3 está ligado a apenas uma UF, a UF4, e o país P1 estão ligados a duas UFs, a UF1 e a UF3. Quando estabelecemos um relacionamento entre duas entidades, estamos na verdade formalizando uma relação de dependência entre elas. Veja na regra apresentada para o exemplo da Figura 3.8. Se cada UF deve possuir um país, significa que a entidade UF depende da entidade PAIS, de forma que não se consegue cadastrar uma UF sem que antes se cadastre o PAIS para ela.
63
Capítulo 3 Aqui na modelagem conceitual dos dados essa dependência é representada pelo relacionamento, mas no SGBD esse conceito é implementado, copiandose o atributo que identifica a “entidade pai”, nesse caso PAIS, para a entidade dependente, nesse caso UF. Dessa forma, a entidade UF quando for implementada no SGBD terá um atributo a mais, CD_PAIS, que é uma cópia do atributo que identifica a entidade PAIS. Por meio dos conceitos de cardinalidade máxima e mínima, os relacionamentos podem se estabelecer formalmente de três formas, apresentadas a seguir.
a. Relacionamentos com cardinalidade um para um (1:1) Na cardinalidade um para um, o relacionamento entre as entidades sempre ocorre de um para um, ou seja: para cada elemento de uma entidade, só existe uma única ocorrência relacionada na outra entidade. Observe a Figura 3.9: Figura 3.9 - Exemplo de cardinalidade um para um
Nome_UF Cod_UF
UF
(1,1)
PAIS_Capital
(1,1) Nome_Capital
Capital
Cod_Capital
Fonte: Elaboração do autor (2015).
Na representação da Figura 3.9, a entidade UF possui uma única Capital e a entidade Capital pertence a uma única UF.
64
Princípios de Bancos de Dados Na grande maioria dos casos, as duas entidades que estão envolvidas num relacionamento um para um se fundem numa única entidade, e o motivo é bastante simples, veja: Vamos supor que a entidade UF possua dois campos: COD_UF e NOME_UF, e a entidade CAPITAL possua também dois campos COD_CAP e NOME_CAP. Agora vamos cadastrar duas UFs e suas respectivas capitais. Figura 3.10 – UF e Capitais relacionadas
UF
CAPITAL
COD_UF
NOME_UF
COD_CAP
NOME_CAP
1
SC
1
FLORIANOPOLIS
2
SP
2
SÃO PAULO
Fonte: Elaboração do autor (2015).
Temos então que cada UF está associada a sua CAPITAL e a leitura inversa também é válida. Neste modelo de dados, conforme visto na Figura 3.10, temos duas entidades e um relacionamento entre elas. Agora eu lhes pergunto: Se cada UF se relaciona com uma, e somente uma CAPITAL e, por conseguinte, cada CAPITAL se relaciona com uma e somente uma UF, não seria mais simples se fosse incluída o nome da capital diretamente na entidade UF? Vamos ver como ficaria a entidade UF sob essa nova abordagem. Figura 3.11 – UFs cadastradas
UF COD_UF
NOME_UF
NOME_CAP
1
SC
FLORIANOPOLIS
2
SP
SÃO PAULO
Fonte: Elaboração do autor (2015).
Perceba que conseguimos representar as UFs e suas respectivas CAPITAIS simplesmente com uma entidade. Com isso, eliminamos muita complexidade do modelo de dados e, consequentemente, deixaremos o projeto do banco de dados com menos entidades e relacionamentos, o que deve permitir mais performance à ferramenta de SGBD quando esse projeto for implementado.
65
Capítulo 3 Essa abordagem deve ser levada em consideração sempre que for estabelecido um relacionamento um para um, a fim de verificar a viabilidade de se reduzir a situação a apenas uma entidade. Caso existam implicações em função do restante do modelo de dados, então, deve ser mantido o relacionamento e as duas entidades. Para efeito de uma utilização didática do relacionamento um para um, vamos continuar mantendo nos exemplos as duas entidades com o relacionamento.
b. Relacionamentos com cardinalidade um para N (1:N) Nesse segundo tipo de cardinalidade, comumente denominada um para muitos, já é possível que um elemento de uma determinada entidade se relacione com vários elementos de outra entidade. Observe a figura 3.12: Figura 3.12 - Exemplo de cardinalidade um para N Nome_UF Cod_UF
UF
(0,n)
Nome_Pais Cod_Pais
PAIS_Capital
(1,1)
Pais (1,n) PAIS_Continente
(1,1) Nome_Cont
Continente
Cod_Cont
Fonte: Elaboração do autor (2015).
A entidade Continente possui vários países. Já a entidade País pertence a um único Continente. Assim como foi demonstrado, a possibilidade de redução de um modelo de dados a apenas uma entidade, onde existir um relacionamento um para um entre duas entidades, vamos verificar agora por que não é possível a mesma operação quando houver um relacionamento um para N.
66
Princípios de Bancos de Dados Vamos supor que a entidade CONTINENTE possua dois atributos: COD_CON e NOME_CON, representando respectivamente o código e o nome do continente, e a entidade PAIS possua também dois campos COD_PAIS e NOME_PAIS, representando, respectivamente, o código e o nome do país. Agora vamos cadastrar um continente e três países, que em tese estão associados àquele continente. Figura 3.13 – Continente e países relacionados
CONTINENTE
PAÍS
COD_CON
NOME_CON
COD_PAIS
NOME_PAIS
1
EUROPA
1
ALEMANHA
2
ITÁLIA
3
FRANÇA
Fonte: Elaboração do autor (2015).
Ao reduzir o modelo com o relacionamento um para N para apenas uma entidade, veja como ficaria a entidade CONTINENTE: Figura 3.14 – Continente cadastrado com vários países
CONTINENTE COD_CON
NOME_VOM
NOME_PAIS
1
EUROPA
ALEMANHA, ITÁLIA, FRANÇA
Fonte: Elaboração do autor (2015).
É possível perceber claramente que podem existir situações onde vários países pertençam a um mesmo continente, acarretando o registro de todos estes países num mesmo atributo, NOME_PAIS. Você pode então indagar: que problemas isso pode acarretar? Uma das dificuldades mais óbvias a apresentar se refere à impossibilidade de se definir, a priori, com grau suficiente de certeza quantos países poderão pertencer a um continente, a fim de que possamos definir qual o número máximo de caracteres (letras) vamos reservar para armazenar os países. Outra dificuldade diz respeito à necessidade, por exemplo, de se extrair deste banco de dados o nome do continente relativo ao país chamado ITÁLIA. Para conseguirmos essa informação, vamos ter que pesquisar no valor do campo NOME_PAIS por parte do seu conteúdo, isso acarreta uma série de dificuldades de implementação e também prejudica o desempenho do SGBD.
67
Capítulo 3 Dessa forma, quando o relacionamento entre duas entidades for da ordem de um para muitos, não reduzimos o modelo a apenas uma entidade, mantendo as duas com o relacionamento entre elas.
c. Relacionamentos com cardinalidade N para N (N:N) Agora você chegou à última forma de se estabelecer um relacionamento, comumente denominada muitos para muitos, que representa um modelo em que vários elementos de uma determinada entidade se relacionam com vários elementos de outra entidade. Veja o diagrama apresentado na Figura 3.15: Figura 3.15 - Exemplo de cardinalidade N para N
Nome_UF Cod_UF
UF_HABITANTE
(1,n)
UF
(0,n) HABITANTE
Nome_Hab Cod_Hab Fonte: Elaboração do autor (2015).
De acordo com a representação acima, a entidade Habitante pode pertencer a várias unidades federativas. Já a entidade UF possui vários habitantes. Em um projeto de banco de dados em nível conceitual, pode-se aceitar o estabelecimento de um relacionamento N para N, conforme visto na Figura 3.15, porém, ao refinarmos esse projeto para um modelo lógico ou ao implementarmos esse modelo num SGBD, não será possível implementar um relacionamento desse tipo, veja o porquê.
68
Princípios de Bancos de Dados Suponha que as referidas entidades UF e HABITANTE, apresentadas na Figura 3.15, possuam, respectivamente, os seguintes atributos e que existam cadastrados em ambas as entidades, conforme o exposto abaixo: Figura 3.16 – UF e seus habitantes relacionados
UF
HABITANTES
COD_UF (PK)
NOME_UF
COD_HAB(PK)
NOME_HAB
1
SC
1
JOAO BIS
2
SP
2
MARIA HOFF
3
TECO TROTSK
Fonte: Elaboração do autor (2015).
Segundo o exposto acima, o UF SC possui como habitantes JOAO BIS E MARIA HOFF; MARIA HOFF é considerada habitante de duas UFs, SC e SP. O relacionamento está demonstrado por meio dos traços entre os habitantes e suas UFs. Podemos perceber que cada UF poderá estar associada a vários habitantes, isso nos indica que o identificador da UF será copiado para habitantes, ao se implementar o SGBD, a fim de identificar a qual UF cada habitante pertence. Porém, aqui temos um agravante: cada habitante pode pertencer a várias UFs, da mesma forma que cada UF pode abrigar vários habitantes. Então também temos o identificador de habitantes transferido para a entidade UF Cabe informar aqui, e isto será melhor detalhado quando trabalharmos na segunda etapa da modelagem, a modelagem em nível lógico, cujo atributo que é identificador único na entidade é denominado chave primária, quando implementado no SGBD, referenciada como PK- primary key, assim como o atributo que é copiado para a tabela dependente é denominado chave estrangeira, referenciada como FK – foreign key. Como nesse caso, temos uma chave estrangeira em UF e outra em HABITANTES, estão apresentadas abaixo as duas entidades, agora contendo os atributos que serão referência para as chaves estrangeiras.
69
Capítulo 3 Figura 3.17 – Habitantes relacionados com várias UF
UF
HABITANTES
NOME_UF
HABIT (FK)
COD_HAB (PK)
1
SC
1,2
1
JOAO BIS
1
2
SP
2,3
2
MARIA HOFF
1,2
3
TECO TROTSK
2
COD_UF (PK)
NOME_HAB
UF (FK)
Fonte: Elaboração do autor (2015).
Neste quadro, você pode notar que ocorre a mesma situação ocorrida com a entidade continente, ao tentarmos incluir todos os países num único atributo. Como as colunas HABIT e UF possuem a indicação de que são chaves estrangeiras (FK), obviamente, nesse caso, elas somente podem aceitar os valores das colunas que são chaves primárias (PK) das entidades na qual dependem, não faz sentido informar para um habitante uma UF que não exista cadastrada, portanto, a coluna HABIT só pode assumir valores que existam na coluna COD_ HAB, e a coluna UF só pode receber valores que existam na coluna COD_UF. Mas atenção para um detalhe, quantos valores existem para cada coluna HABIT e UF? Para alguns UF, existem dois habitantes, já para alguns habitantes existem 1 ou 2 UFs, mas poderiam existir N habitantes para cada UF, assim como poderiam existir N UF para cada habitante, afinal esse é um relacionamento N para N. Nessa situação, estamos novamente com várias informações numa mesma coluna, assim como ocorreu na coluna NOME_PAIS da entidade continente. Já ficou claro que vai ser impossível, a priori, definirmos um número máximo de habitantes na entidade UF e um número máximo de UF na entidade HABITANTES. Para que seja resolvido esse impasse, ou seja, para que consigamos claramente identificar quais são os habitantes de cada UF e quais são os UFs de cada habitante, é implementado uma terceira entidade, denominada entidade associativa, que vai resolver a problemática do relacionamento N para N. A entidade que relaciona duas entidades de um relacionamento N para N chama-se entidade associativa.
70
Princípios de Bancos de Dados No caso representado pela Figura 3.18, o relacionamento entre as entidades UF e Habitantes ficaria da seguinte forma: Figura 3.18 - Exemplo de entidade associativa
Nome_UF Cod_UF UF_HABITANTE
Relação_2
(1,n)
UF
(0,n) HABITANTE
Nome_Hab Cod_Hab Fonte: Elaboração do autor (2015).
Dessa forma, transforma-se um relacionamento N para N em dois relacionamentos um para N, pois a entidade associativa sempre se relaciona com as entidades externas, por meio de uma cardinalidade mínima de um e máxima de muitos. Toda vez que tivermos um relacionamento N para N, precisamos resolvê-lo, criando uma entidade associativa. Toda entidade associativa vai ter na composição de seu identificador único, no mínimo, os atributos do identificador único das entidades que a originaram, no caso da entidade UF_Habitantes, sua estrutura seria a seguinte:
71
Capítulo 3 Figura 3.19 – Relacionamento UF_Habitantes
UF COD_UF
UF_HABITANTES
NOME_UF
(PK)
HABITANTES
COD_HAB
COD_HAB
COD_HAB NOME_HAB
(PFK)
(PFK)
(PK)
1
SC
1
1
1
JOAO BIS
2
SP
1
2
2
MARIA HOFF
2
2
3
TECO TROTSK
2
3
Fonte: Elaboração do autor (2015).
O identificador único da entidade UF_HABITANTES é composto (identificador único composto) por dois atributos. Não esqueça, que toda entidade só poderá ter um identificador único, e esse pode ser composto por N atributos. Lembre-se, na representação do modelo conceitual de dados, os atributos que são transferidos pelos relacionamentos não aparecem, ficando, dessa forma, a entidade associativa UF_Habitantes, aparentemente sem atributos, porém, devemos ter em mente que os identificadores únicos das entidades UF e Habitante estarão sendo transferidos para ela.
Agora fica fácil identificar quem é quem entre UF e HABITANTE, por exemplo, o UF de código 1 (SC) possui os habitantes de código 1 e 2, o HABITANTE de código 3 pertence ao UF 2. Com o uso da entidade associativa UF_Habitante, o modelo passa a ter para cada UF um grupo único de habitantes. E cada habitante pertence a um único grupo de UFs. À medida que identificamos os assuntos e suas características, vamos descrevendo-os no modelo de dados, sob a forma de entidades, atributos e seus relacionamentos. Abaixo um exemplo de um modelo de dados em nível conceitual, um pouco mais completo.
72
Princípios de Bancos de Dados Figura 3.20 – Modelo de dados a nível conceitual completo Nome_Pais
Nome_UF
Cod_Pais
Cod_UF UF_HABITANTE
Relação_2
(1,n)
UF
(0,n)
UF_Pais
HABITANTE
Pais (1,n)
(1,1)
(0,n)
(1,1)
Pais_Continente
UF_Capital
(1,1)
(1,1) Nome_Capital
Capital
Cod_Capital
Continente
Cod_Hab Nome_Hab
Nome_Cont Cod_Cont
Fonte: Elaboração do autor (2015).
Depois dessas representações todas, você deve estar se perguntando: tenho que fazer esses desenhos para modelar um banco de dados? Essa é uma forma padrão para representação de relacionamento entre entidades, e mais do que isso, essa representação é o primeiro passo para que a solução implementada venha a atender as necessidades do usuário final. Sendo assim, é necessário que você desenhe essas representações, porém, não é preciso que você faça isso à mão. A informática disponibiliza algumas ferramentas que permitem desenhar e analisar o modelo de dados proposto, também é possível a conversão para alguns tipos de SGBD. Essas ferramentas serão apresentadas e discutidas num capítulo específico desta Unidade de Aprendizagem.
2.2 Construindo o modelo de dados em nível lógico O modelo de dados em nível lógico consiste numa representação gráfica dos elementos que estarão presentes no banco de dados quando esses forem implementados, portanto, é um modelo mais técnico e mais detalhado que o modelo em nível conceitual. Costuma-se dizer que o modelo de dados em nível lógico é um refinamento do modelo de dados conceitual. Vamos tomar como base o modelo de dados conceitual apresentado na Figura 3.20. A partir dele nós vamos gerar o modelo de dados lógico.
73
Capítulo 3 Na Figura 3.21, está apresentado o mapeamento do modelo lógico da Figura 3.8 para o nível lógico, realizando-se um maior detalhamento das informações. Figura 3.21 – Modelo de dados a nível lógico
Fonte: Elaboração do autor (2015).
Vamos verificar quais as mudanças ocorridas neste refinamento, do modelo conceitual para o modelo lógico. A primeira mudança são os termos utilizados. Você está observando alguns termos como PRIMARY, FK, INT, VARCHAR. Eles se referem a elementos técnicos que existem no banco de dados e, como agora este modelo é bastante técnico, nós temos que apresentá-lo na forma como será implementado no SGBD. A seguir, você encontrará, de forma detalhada, como ocorreram as mudanças do modelo conceitual para o modelo em nível lógico: A partir de agora cada entidade presente no modelo conceitual passa a ser tratada como uma tabela no SGBD, então, temos a tabela UF, a tabela CONTINENTE, assim por diante. Figura 3.22 – Exemplo de uma Tabela no modelo em nível lógico
Fonte: Elaboração do autor (2015).
74
Princípios de Bancos de Dados Os atributos das entidades passam a ser denominados agora de colunas das tabelas, e recebem um tipo de dado, que é a forma pela qual são representados. Para uma coluna que armazena apenas números, utilizamos o tipo de dado INT (números inteiros). Para uma coluna que armazena literais, strings, utilizamos o tipo de dado VARCHAR, que armazena dados alfanuméricos. Para colunas que armazenam datas, utilizamos o tipo de dado DATE. Com as colunas também ocorre outra mudança. Perceba o símbolo de uma chave ao lado esquerdo da coluna. Ela indica que a coluna pertence à chave primária da tabela. A coluna que possui um losango na cor clara indica que o atributo é obrigatório, já a coluna que possui um losango escuro indica que o elemento pertence a uma chave estrangeira. Os conceitos de chave primária e chave estrangeira você verá logo em seguida. Figura 3.23 – Exemplo de uma Tabela com suas Colunas
Fonte: Elaboração do autor (2015).
O identificador único de cada entidade passa a ser agora um elemento denominado chave primária, ou primary key. É o mecanismo de primary key que vai nos garantir lá no SGBD, que nenhum habitante possua o mesmo código que um outro, por exemplo. Figura 3.24 – Tabela com sua Primary Key
Fonte: Elaboração do autor (2015).
75
Capítulo 3 Os relacionamentos transformam-se em chaves estrangeiras, ou foreign keys. As foreign keys são quem garantem a integridade referencial dos dados. Explicando melhor, é a foreign key fk_PAIS_CONTINENTE presente na tabela PAIS quem garante que cada valor para a coluna COD_CONT, presente nesta tabela, é de um continente devidamente cadastrado lá na tabela CONTINENTE. Figura 3.25 – Tabela com chave estrangeira
Fonte: Elaboração do autor (2015).
Nessa situação, cabe uma observação muito importante. Uma chave estrangeira garante a integridade referencial entre as informações de duas tabelas, ou seja, nesse exemplo, ao cadastrar o código do continente (COD_CONT) para o país, o objeto fk_PAIS_CONTINENTE1 que existirá no SGBD será acionado e esse verificará na tabela Continente se esse código é válido para um continente cadastrado. Porém, nese exemplo cabe um questionamento: onde está indicado que é a coluna COD_CONT da tabela País que será fiscalizada pela chave estrangeira? Não poderia ser outra qualquer da tabela País? Bem, para responder essa questão, temos que ter em mente que uma chave estrangeira sempre vai fiscalizar uma coluna de uma tabela, tendo como base a coluna da chave primária na qual ela depende, portanto, nesse caso, como a coluna COD_CONT na tabela Continente é a coluna da chave primária desta tabela, fica claro que a coluna da tabela País, a qual será fiscalizada pela chave estrangeira, é a coluna COD_CONT, porque ela armazenará o código do continente. Uma chave estrangeira sempre fiscaliza uma tabela com base na(s) coluna(s) da chave primária da tabela na qual ela depende.
76
Princípios de Bancos de Dados Agora então nós temos um modelo de dados bem mais técnico, onde estão especificados os elementos que vão estar presentes no SGBD. Esses elementos são tabelas, chaves primárias, chaves estrangeiras, colunas (obrigatórias ou opcionais). Resta agora transformar esse modelo de dados em nível lógico num modelo em nível físico, que consiste em construir as sentenças da linguagem SQL para que se consiga implementar os objetos no SGBD. Como para realizar essa tarefa é importante que você tenha um contato com uma ferramenta de SGBD, e também que conheça um pouco da linguagem SQL, o modelo de dados em nível físico será discutido num capítulo mais adiante, primeiro conheça melhor os produtos de SGBD e também da linguagem SQL nos capítulos seguintes.
77
Capítulo 4 Ferramentas para modelagem de dados e produtos de banco de dados Habilidades
•• Distinguir as características de software livre e de software proprietário. •• Contextualizar a aplicação de produtos de SGBD construídos sob o âmbito do software livre e do software proprietário. •• Identificar as características de alguns produtos de SGBD. •• Realizar a instalação e configuração de um produto de SGBD.
Seções de estudo
Seção 1: O mercado de software livre e de software proprietário Seção 2: Ferramentas de apoio à modelagem de dados Seção 3: Novos modelos de banco de dados Seção 4: Ferramentas de SGBD Seção 5: Instalação de um SGBD
79
Capítulo 4
Seção 1 O mercado de software livre e de software proprietário Existem aspectos de modelagem de dados para o projeto de um banco de dados. assim como técnicas para mapear as necessidades de um ambiente do mundo real para um modelo de dados. Tal processo permite que esse modelo de dados, quando implementado num produto de banco de dados, possa corresponder e atender a essas necessidades. Passado esse passo, o próximo é o da implementação do modelo de dados num produto de banco de dados. Porém, para a escolha de um produto desse banco é necessário que se conheça esses produtos, suas características, bem como compreender como o mercado está organizado no fornecimento desses softwares. Para tanto, neste capítulo, será abordado e consolidado o mercado de softwares, ditos proprietários, e uma nova classe de softwares: a do mercado de softwares livres. Além disso, estão apresentados os conceitos de software proprietário e livre, bem como algumas ferramentas de banco de dados e o mercado ao qual estão relacionados. A tecnologia de banco de dados, assim como a informática em geral, vem sofrendo atualizações constantes, e uma das atividades dos profissionais que atuam nesta área é se manter informado sobre as atualizações do mercado no que se refere aos novos produtos. Nos últimos anos, a área de banco de dados sofreu uma grande modificação com a chegada das ferramentas gratuitas, ou de uso livre, como normalmente são chamadas. Tão importante quanto o fato de essas serem gratuitas foi o fato de as ferramentas se apresentarem robustas e confiáveis, o que agradou em muito o mercado de trabalho. As ferramentas gratuitas estão baseadas na ideia de software livre, que tem como princípio: “Software livre é uma questão de liberdade, não de preço”. Software livre se refere à liberdade dos usuários executarem, copiarem, distribuírem, estudarem, modificarem e aperfeiçoarem o software. Mais precisamente, refere-se a quatro liberdades para os usuários do software:
80
••
a liberdade de executar o programa, para qualquer propósito;
••
a liberdade de estudar como o programa funciona e adaptá-lo para as suas necessidades. Acesso ao código-fonte é um pré-requisito para essa liberdade;
Princípios de Bancos de Dados ••
a liberdade de redistribuir cópias de modo que você possa ajudar ao seu próximo;
••
a liberdade de aperfeiçoar o programa e liberar os seus aperfeiçoamentos, de modo que toda a comunidade se beneficie.
Bem, como você pode ter notado, o software livre é uma tendência de mercado. As ferramentas atuais, como: MySQL, PostgreSQL, FireBird e outros, têm apresentado a cada ano versões mais confiáveis e amigáveis, que permitem ao usuário realizar as mesmas rotinas de outros bancos de dados proprietários, com a mesma qualidade e confiabilidade. Essa é uma tendência de mercado e caberá a você como gestor da área de tecnologia da informação escolher a ferramenta que melhor agrade e atenda as suas necessidades. Já, o mercado de softwares proprietários apresenta as seguintes características: ••
Normalmente é cobrado um valor pelo uso do software, a tão conhecida licença de uso. Esse valor pode ser cobrado por meio da venda do direito do uso por tempo ilimitado, ou ainda outra modalidade de comercialização é a venda do direito de uso por um determinado período de tempo.
••
Há a figura do proprietário do software, ou seja, de quem o construiu e que responde pelo seu adequado funcionamento.
••
O proprietário é quem fornece possíveis correções no produto comercializado, isso representa um certo nível de garantia ao consumidor, uma vez que se tem identificado a quem recorrer no caso de alguma anormalidade no funcionamento do software.
••
Geralmente, o software proprietário possui uma política de atualização e/ou evolução, em que estão contempladas as inovações a serem incorporadas ao software num determinado período de tempo. Essa política não representa qualquer garantia de continuidade do produto, uma vez que depende do proprietário do software a decisão de continuar a sua evolução e atualização.
As ferramentas proprietárias existentes no mercado, como Oracle, Ms-SQLServer, IBM-DB2, também têm a sua grande contribuição no mercado de trabalho como ferramentas robustas, confiáveis e de grande utilização no mercado atual, principalmente pelas empresas de médio e grande porte.
81
Capítulo 4 Diante desse contexto, inevitavelmente uma dúvida passa a nos instigar: Por que determinadas empresas utilizam-se de SGBDs proprietários, pagando licenciamento e suporte de software e estão muito satisfeitas, já outras tantas se utilizam de SGBDs gratuitos, e também se encontram satisfeitas com estes produtos? Para responder a esse questionamento, temos que observar o quão crítica é a área de TI para a empresa, ou seja, o quanto a empresa depende da área de TI para alavancar seus negócios. Quanto mais dependente da TI o negócio da empresa for, mais recursos e garantias os serviços de TI devem proporcionar. Vejamos o exemplo de uma pequena empresa. Como nesta empresa o volume de dados não é muito expressivo, digamos que para fazer a cópia de segurança (backup) dos dados dispende-se em torno de duas horas. Essa empresa funciona normalmente no período comercial, então, no período noturno temos tempo suficiente para parar os sistemas todos e realizarmos esse procedimento. Essa empresa também não necessita ter suas informações distribuídas em vários servidores de banco de dados, uma vez que não atua em diversas regiões do país. Agora vejamos a situação de uma empresa maior, uma instituição financeira por exemplo. Dificilmente você acessa o internet banking do seu banco e encontra a mensagem de que o sistema encontra-se em backup e somente poderá atendêlo em duas horas!!! Ou seja, para essa empresa, os sistemas não podem ser desligados para tarefas de manutenção. Outra característica é a de trabalhar com as informações distribuídas em vários servidores, uma vez que essa empresa possui agências em diversas regiões do país. Mais ainda, caso um servidor venha a falhar, outro deve assumir o seu lugar e receber as solicitações dos usuários. Diante dessas duas situações, podemos claramente identificar níveis de criticidade distintos, para tanto, os produtos de software devem ser compatíveis com estss necessidades. Na primeira situação, podemos ter um SGBD que não necessite de características para backup, com o banco em funcionamento, pois todos os sistemas podem parar durante o período noturno, já na segunda situação, o SGBD deve ter a característica de poder realizar cópia de segurança com os sistemas em funcionamento, inclusive realizar cópias parciais dos dados a cada hora, para se evitar ao máximo qualquer problema com os dados, deve possuir mecanismos de replicação de dados e de contingência, em caso de falha de um dos servidores. Como pode ser observado, nesses dois casos temos necessidades distintas, levando-nos a eleger um produto mais sofisticado e preciso, no segundo caso, e um produto que eventualmente não possua tantos recursos, exigindo recursos computacionais para o caso da pequena empresa. Nessa situação, pode acontecer de um produto não proprietário não conseguir atender a essa demanda, em função da complexidade e nível de tecnologia envolvido, ou ainda em função de que a empresa queira que alguém responda diretamente por qualquer eventualidade a qual venha a ocorrer durante a utilização do produto.
82
Princípios de Bancos de Dados Agora acredito que você já possa concluir a resposta para o nosso questionamento inicial. O mercado de banco de dados está em constante atualização, dessa forma, abre espaço para todos os tipos de ferramentas, o que é bom, pois abre as portas para os profissionais da área de tecnologia da informação, em especial aqueles com conhecimento em banco de dados. Lembre-se de que um especialista em tecnologia da informação não é um especialista em ferramentas, mas em soluções, ele não deve se prender a fabricantes ou produtos, deve estar preparado para as constantes mudanças que o mercado possa sofrer. Independente das mudanças, as ferramentas de banco de dados tendem a facilitar a vida do profissional de informática, tornando a sua produtividade maior e mais eficiente, evitando que ele tenha de editar comandos manualmente, ou criar tabelas por meio de comandos extensos. As novas ferramentas apresentam uma interface amigável de maneira que o usuário cria e testa seus modelos de banco de dados sem a necessidade de escrever comandos, com apenas um clique do mouse.
Seção 2 Ferramentas de apoio à modelagem de dados (Ferramentas Case) Um SGBD é uma ferramenta relativamente complexa, onde existe uma série de mecanismos implementados bem como algumas regras a serem seguidas para a sua utilização, todas visando preservar e manter os dados de maneira íntegra. O projeto de um banco de dados demanda muito mais esforço do que a sua implementação ou implantação. Isto se deve ao fato de que se, ao ser projetado o banco de dados, for omitido ou registrado de forma equivocada alguma informação, este não contemplará adequadamente as necessidades do ambiente de negócio que estiver sendo informatizado, refletindo diretamente em problemas na sua utilização. Para que um projeto de banco de dados seja bem sucedido é imprescindível que se construa um modelo de dados, ou seja, que sejam expostos os elementos que fazem parte do projeto do banco de dados.
83
Capítulo 4 Um modelo de dados relacional é composto, principalmente, pelas tabelas que compõem este banco de dados e seus relacionamentos, e indicam como as informações se comportam e como estão inter-relacionadas. Para se construir um modelo de dados com poucos elementos, devidamente documentado, não é tarefa das mais desafiadoras. Agora, você já se imaginou projetando um banco de dados e tendo que construir um modelo de dados com centenas de tabelas e outras centenas de relacionamentos, tudo manualmente? Parece ser um pouco mais complicado, não é mesmo? Ainda mais se pensarmos na manutenção desse modelo durante o seu tempo de vida. Para que esses modelos possam ser mais facilmente construídos e mantidos, entram em cena as ferramentas case, ferramentas que possuem funcionalidades específicas para a construção de modelos de dados, e é o que será apresentado neste capítulo. As ferramentas de apoio a modelagem de dados, denominadas ferramentas case (Computer Aided Software Engineering) estão para a Engenharia de Software assim como o CAD (Computer Aided Design) está para a Engenharia Civil. Ambas as ferramentas são programas que auxiliam o analista na construção do sistema, prevendo ainda na etapa de estudos, como será sua estrutura, quais serão suas entidades e relacionamentos. Para tanto, são elaborados vários diagramas que em conjunto constituem praticamente uma “planta” do sistema a ser desenvolvido. Cabe ressaltar que as ferramentas case disponíveis no mercado têm abrangências distintas, implicando no seu grau de utilização e envolvimento durante um projeto de um sistema informatizado. Algumas destas ferramentas limitam-se ao auxílio no projeto do banco de dados, gerando apenas uma visualização gráfica do seu projeto, outras possibilitam também a geração do código para a implementação do banco de dados, outras são ainda mais abrangentes e complexas, possibilitando também o projeto do software (telas, relatórios...) gerando inclusive o código de implementação em alguma linguagem de programação. As ferramentas case também se distinguem pelo nível de modelagem e documentação que proporcionam. Algumas delas se limitam ao projeto nível lógico, onde são definidos diretamente as tabelas, suas colunas e seus relacionamentos, outras já são mais abrangentes, permitindo uma modelagem inicial a nível conceitual, onde procura-se dar uma visão de negócio ao modelo de dados, uma vez que o modelo lógico é um modelo bastante técnico. Isto auxilia na definição dos elementos do modelo junto ao usuário final, que geralmente não é um especialista na área de informática.
84
Princípios de Bancos de Dados Encontramos ainda no mercado ferramentas que proporcionam desde uma visão a nível de modelagem de processos, onde são mapeados os processos de negócio do ambiente que se está informatizando, até a implementação do banco de dados. Isto permite um maior controle na obtenção das informações de quais elementos estão envolvidos no ambiente a informatizar, reduzindo a possibilidade de erros no mapeamento do mundo real para o modelo de dados, aumentando porém a complexidade da ferramenta e o volume de trabalho a ser realizado. Outra característica importante neste tipo de ferramenta é a possibilidade de se executar o que denominamos “engenharia reversa”. Apesar do nome imponente, esta atividade se resume a realizar o processo inverso da criação de um banco de dados, ou seja, ao invés de criá-lo a partir do modelo de dados, é criado o modelo de dados a partir de um banco de dados já existente. Isto se torna interessante quando encontramos uma determinada base de dados já em operação que necessita de manutenção, porém não existe um modelo de dados documentado sobre aquela base de dados. Neste caso, primeiro é realizada a engenharia reversa, na qual a ferramenta case pesquisa na base de dados quais objetos existem e traça um mapeamento destes elementos, construindo um modelo de dados a nível lógico, identificando cada tabela, suas colunas, chaves primárias e estrangeiras. A partir deste modelo de dados podemos ter uma visão completa do banco de dados que está implementado e assim pode-se trabalhar com mais precisão nas alterações a serem realizadas. Existem inúmeras ferramentas case disponíveis no mercado. Entre elas podemos citar: Rational Rose, ERwin, Oracle Designer, Genexus, Clarify, Dr.Case, Paradigm, PowerDesigner, Arena, Visio, MySQL Workbench, etc. No mercado existem ferramentas com diversos níveis de abrangência para mapeamento dos dados, bem como diversos níveis de detalhamento de um modelo de dados. Este capítulo contempla a utilização da ferramenta case MySQL Workbench para que você possa compreender de maneira bastante prática o funcionamento e as facilidades implementadas por uma ferramenta deste tipo. Como os fabricantes lançam constantemente novas versões, e para flexibilizar a atualização do material didático, a demonstração da ferramenta está disponível em uma webaula, cujo link encontra-se no EVA.
85
Capítulo 4
Seção 3 Novos modelos de banco de dados Assim como as ferramentas de desenvolvimento de banco de dados têm evoluído, novos modelos de banco de dados também têm sido apresentados ao mercado nas últimas décadas. O modelo mais recente e com bastante aceitação pelo mercado é o banco de dados orientado a objetos. O banco de dados orientado a objetos trata basicamente de um sistema em que a unidade de armazenamento é vista como sendo um objeto, dessa forma, ele passa a ter propriedades e não campos. A principal característica desse tipo de banco de dados é a abstração dos dados, que significa: ••
esconder os detalhes da implementação de seus módulos;
••
atrelar a manipulação dos dados a métodos previamente programados no banco de dados;
••
compartilhar objetos;
••
expandir seus sistemas por meio de outros módulos já existentes.
O banco de dados orientado a objeto combina os benefícios e os conceitos da programação orientada a objetos, com a funcionalidade dos bancos de dados. Já os bancos de dados relacionais trabalham com tabelas e relacionamentos, não tratando esses elementos como objetos do mundo real. Mas qual a diferença entre esses dois tipos de banco de dados? Os bancos de dados relacionais usam uma arquitetura tabular ou matricial, em que os dados são referenciados por meio de linhas e colunas. Já os bancos de dados orientados a objetos podem ser mais completos e complexos também, combinando a lógica e dados. No que se refere aos bancos de dados relacionais:
86
••
As tabelas são definidas tendo como base a teoria da normalização, evitando a redundância dos dados e facilitando a pesquisa e as atualizações.
••
O mercado hoje é dominado pelos relacionais.
••
São descritos em duas dimensões, por meio de linhas e colunas.
Princípios de Bancos de Dados A linguagem SQL (Structure Query Language) é um padrão aberto para consulta e manipulação dos bancos de dados relacionais de todos os fornecedores. O SQL permite que os sistemas relacionais desenvolvidos por muitos fornecedores possam se comunicar entre si e acessar banco de dados comuns. No que se refere aos bancos de dados orientados a objetos: ••
Não possuem uma linguagem padrão, dificultando a conexão entre os bancos de dados de diferentes fornecedores.
••
Com o crescimento do mercado de multimídia, videogames e aplicações Web que utilizam a linguagem orientada a objetos Java, o uso de bancos orientados a objetos está crescendo. Para atender a essa demanda, os fornecedores de bancos de dados relacionais estão introduzindo facilidades de armazenamento de objetos em seus bancos de dados, chamando-os de banco de dados relacional por objetos.
••
São exemplos de ferramentas de banco de dados orientados a objetos, banco de dados Caché e Oracle.
A vantagem do banco de dados orientado a Objetos é a lógica contida no objeto e a possibilidade de ser reutilizado várias vezes em diversas aplicações. É importante salientar que os bancos de dados comuns, tradicionais, foram desenvolvidos para empresas relativamente estáveis, com grandes volumes de dados de baixa complexidade. O mercado atual é liderado pela tecnologia relacional. Mas qual o motivo dessa liderança, uma vez que a tecnologia orientada a objetos já é realidade para o desenvolvimento de software? Para responder a essa questão, é preciso observar não apenas a tecnologia puramente empregada, mas também como o mercado de banco de dados encara essa mudança de tecnologia, da relacional para a orientada a objetos. Com relação à questão da tecnologia, está claro que a orientada a objetos possui vantagens significativas, principalmente no que diz respeito à reutilização de código e manipulação dos elementos do software, tratados como objetos, possuindo os dados atrelados a seus métodos de manipulação. O grande e principal obstáculo da tecnologia orientada a objetos, como padrão para ferramentas de banco de dados, está no fato da sua padronização, principalmente na linguagem de acesso aos objetos, e também numa questão de mercado, ou seja, no custo da migração de uma base de dados relacional para uma base orientada a objetos e seu impacto nos softwares que se utilizam dessa base.
87
Capítulo 4 ••
Esse elemento de mercado merece especial atenção devido ao fato de que os bancos de dados com tecnologia relacional possuem atualmente características muito importantes para o mercado:
••
Padronização;
••
Performance;
••
Compatibilidade entre os produtos com a mesma tecnologia.
Assim sendo, sob o ponto de vista tecnológico, ficam relativamente claras as vantagens que se têm ao utilizar-se de produtos orientados a objetos. Porém, sob o ponto de vista empresarial, do consumidor que paga pela mudança, por vezes, fica difícil argumentar ou justificar os custos envolvidos numa mudança de tecnologia em banco de dados, em que será modificada a tecnologia, mas os resultados continuarão sendo os mesmos, ou seja, o banco de dados orientado a objetos vai continuar fornecendo os dados aos aplicativos, como os que operam sob a tecnologia relacional o fazem hoje, e já estão devidamente instalados na empresa e com pessoas devidamente qualificadas a operar com essa tecnologia. Um aspecto que deve ser levado em consideração é de que o banco de dados concentra importância crítica no ambiente de tecnologia da informação, uma vez que fornece dados para inúmeros departamentos das empresas e qualquer problema envolvendo essa área vai ecoar para os softwares que atendem a esses departamentos. Qualquer modificação na área de banco de dados é sempre questionada e deve ser muito bem dimensionada, avaliando-se os riscos e os benefícios que serão acarretados com a mudança. Isso não significa afirmar que a área de banco de dados é estanque e que a tecnologia relacional vai se perpetuar. Mas sim, que a mudança de padrão, de relacional para orientado a objetos, está em curso, acontece de modo mais cauteloso. Provavelmente, daqui a algum tempo estará se consolidando definitivamente como um padrão para a área.
88
Princípios de Bancos de Dados
Seção 4 Ferramentas de SGBD O objetivo dessa seção não é avaliar critérios para definir qual ferramenta é a mais eficiente, mas sim apresentar as diferentes plataformas existentes no mercado e quais as suas principais características. a. MySQL O aplicativo MySQL é um sistema de gerenciamento de banco de dados relacionais baseado em comandos SQL (Structured Query Language Linguagem Estruturada para Pesquisas), ele vem ganhando www.mysql.com grande popularidade. Atualmente, é um dos bancos de dados mais populares. O MySQL foi criado na Suécia por dois suecos e um finlandês: David Axmark, Allan Larsson e Michael Widenius, que trabalham juntos desde a década de 80. O sucesso do aplicativo deve-se em grande parte à fácil integração com linguagens de programação para web, como o PHP, principalmente por se tratar de um banco de dados gratuito, ou seja, o usuário não tem custo algum para adquirir o produto, que pode ser baixado diretamente da internet. b. PostgreSQL O aplicativo PostgreSQL é um sistema de gestão de bases de dados relacionais, desenvolvido como projeto de software livre. Sua origem PostgreSQL está de certo modo ligada ao projeto Ingres, desenvolvido na Universidade de Berkeley, Califórnia. O líder do projeto, Michael Stonebraker, um dos pioneiros das bases de dados relacionais, deixou a universidade em 1982 para comercializar o Ingres, acabando por regressar a Berkeley. Em 1985, Stonebraker iniciou um novo projeto pós-Ingres, com o objetivo de responder a muitos dos problemas que surgiam relacionados a bases de dados relacionais. Esse novo projeto receberia o nome de Postgres e, apesar do parentesco, não partilhou o código base com o Ingres, seguindo sempre caminhos separados. Em 1993 o projeto Postgres foi oficialmente abandonado pela Universidade de Berkeley, mas devido ao fato do seu código-fonte estar sob uma licença gratuita, foi possível manter o desenvolvimento pela comunidade BSD. Em 1995, foi adicionado um interpretador SQL, para substituir a linguagem QUEL (desenvolvida para o Ingres) e o projeto foi renomeado, primeiro para Postgres95 e mais tarde para PostgreSQL .
89
Capítulo 4 c. InterBase O aplicativo InterBase é um gerenciador de banco de dados relacionais da Borland, mesmo fabricante das linguagens de programação Delphi, Borland C++ e Borland Java. Ele é uma opção alternativa aos bancos de dados tradicionais como o SQL Serverda Microsoft e tem as vantagens de ser grátis e código aberto, o que significa que seu código pode ser modificado e melhorado por qualquer usuário. Dessa forma, a ferramenta se mantém em constante evolução, sem custo algum aos seus usuários. d. SQLLite O aplicativo SQLite é uma biblioteca em linguagem C que implementa um banco de dados SQL embutido. Programas que usam a biblioteca SQLite podem ter acesso a banco de dados SQL sem executar um processo separado. O SQLite é um servidor de banco de dados. A biblioteca SQLite lê e escreve diretamente no arquivo do banco de dados. Algumas características do SQLite: ••
software livre/domínio público e multiplataforma;
••
não necessita de instalação, configuração ou administração;
••
implementa a maioria do padrão SQL;
••
o banco de dados é guardado em um único arquivo;
••
suporta bases de dados acima de dois terabytes;
••
sem dependências externas.
e. MS SQL Server O aplicativo MS SQL Server é um gerenciador de banco de dados fabricado pela Microsoft, portanto, proprietário. É um banco de dados muito robusto e usado em empresas, atendendo, principalmente, a sistemas corporativos. f. Oracle O aplicativo é um sistema de banco de dados que surgiu no final dos anos 70, quando Larry Ellison vislumbrou uma oportunidade que outras companhias não haviam percebido, nesse período encontrou uma descrição de um protótipo funcional de um banco de dados relacional e descobriu que nenhuma empresa tinha se empenhado em comercializar essa tecnologia. Então, Ellison e os cofundadores da Oracle Corporation, Bob Miner e Ed Oates, perceberam que havia um tremendo potencial de negócios no modelo de banco de dados relacional, tornando-os, assim, a maior empresa de software empresarial do mundo.
90
Princípios de Bancos de Dados A empresa oferece seus produtos de bancos de dados, ferramentas e aplicativos, bem como serviços relacionados de consultoria, treinamento e suporte. A tecnologia Oracle pode ser encontrada em quase todos os setores do mundo inteiro e nos escritórios de 98 das empresas citadas na lista da Fortune 100. Constitui-se, atualmente, de um dos softwares de banco de dados mais confiáveis e estáveis do mercado. g. Caché O banco de dados Caché é considerado um banco de dados pós-relacional. A sua arquitetura unificada de dados constrói uma camada de descrição para objetos e tabelas relacionais, tendo como uma das características importantes permitir uma integração entre dois mundos: SQL (Relacional) e Objetos. As tecnologias de desenvolvimento para a Web, por exemplo, Java, tendem a ser orientadas a objetos, e assim muitos desenvolvedores perceberam que faz sentido aliar o desenvolvimento com um banco de dados com orientação a objetos. Além disso, os objetos promovem o desenvolvimento rápido, não só pela sua modularidade, mas também por oferecerem uma modelagem mais próxima da realidade. Um problema dos bancos de dados que trabalham com objetos foi a falta de uma linguagem de consulta rápida e simples, já que os bancos relacionais possuem o SQL.
Seção 5 Instalação de um SGBD A ferramenta base de SGBD que será usada para estudo nesta Unidade de Aprendizagem é a ferramenta MySQL, onde serão implementados os modelos de dados e sob a qual serão desenvolvidos os exercícios e exemplos práticos da UA. Por quê MySQL? ••
É uma ferramenta que possui como linguagem de acesso a linguagem padrão SQL;
••
Permite portabilidade entre várias plataformas, entre as quais a Windows e Linux;
••
Possui todos os mecanismos básicos necessários a uma ferramenta de SGBD;
91
Capítulo 4 ••
É uma ferramenta amplamente de código aberto, grátis e muito difundida atualmente no mercado de trabalho;
••
De instalação fácil e rápida, conforme poderá ser observado no decorrer desta unidade;
••
Com muito pouco consumo de recursos dos equipamentos computacionais, permite que seja instalada em equipamentos não muito sofisticados;
Assim como as ferramentas CASE, o banco de dados MySQL encontra-se em constante evolução, para que possamos mantê-los atualizados sempre com as novas versões, os passos e requisitos para a instalação do MySQL encontramse no ambiente EVA, na forma de tutorial. Verifique nas atividades deste capítulo no EVA onde encontra-se o tutorial, baixe-o e siga as instruções para baixar e instalar o banco de dados MySQL.
92
Capítulo 5 Implementação do Projeto de Banco de Dados
Habilidades
•• Utilizar os comandos de definição de dados da linguagem SQL. •• Definir tabelas e suas constraints num SGBD. •• Alterar a estrutura de um projeto de SGBD.
Seções de estudo
Seção 1: A estrutura da linguagem SQL. Seção 2: Como criar tabelas. Seção 3: Como definir tabelas relacionais em SQL. Seção 4: Como remover as tabelas do banco de dados.
93
Capítulo 5
Seção 1 A estrutura da linguagem SQL A linguagem SQL (Structure Query Language) surgiu no início dos anos 70 como resultado de um projeto da IBM para o desenvolvimento de uma linguagem que se adequasse ao modelo relacional. O primeiro sistema de banco de dados baseado em SQL ficou disponível comercialmente no final dos anos 70, juntamente aos outros sistemas de banco de dados relacionais. Por volta da década de 80 foi publicada a primeira versão padronizada da linguagem SQL, seguindo a padronização ANSI e ISO. Desde então, a linguagem vem evoluindo, gerando novas versões padronizadas, como a SQL-92 e SQL-99, assim chamadas em referências aos anos em que foram publicadas. Com suas seguidas evoluções, a linguagem tornou-se a mais poderosa ferramenta de definição e manipulação de bancos de dados relacionais, e é hoje utilizada na grande maioria dos sistemas de bancos de dados. É importante salientar que apesar da padronização oferecida à linguagem SQL, isso se dá no âmbito das operações a serem realizadas bem como com os comandos base, uma vez que cada fabricante implementa funcionalidades extras ao seu produto, exigindo novas implementações na linguagem SQL. Devido a essas características especiais que cada fabricante implementa em seus produtos de SGBDs, adotaremos, a partir desta unidade, a sintaxe e os comandos segundo o aceito pelo produto MySQL, produto de SGBD, apresentados no Capítulo 4. A linguagem SQL é basicamente uma linguagem de consulta a banco de dados, diferente das linguagens comuns de programação por não ser uma linguagem procedural, sendo mais apropriada para Linguagem procedural é aquela na qual os comandos o tratamento de dados relacionados, aqueles que são agrupados em podem ser arranjados em uma tabela, na qual cada procedimentos ou funções. linha forma uma unidade lógica de dados. Essa linguagem funciona tanto como uma linguagem de definição quanto de manipulação de dados, ou seja, usando a linguagem SQL é possível criar tabelas e também acessar os dados ali cadastrados. Sendo assim, neste capítulo você fará parte desse seleto grupo de programadores que usam a linguagem SQL para manipulação e definição de dados. Bons estudos!
94
Princípios de Bancos de Dados A linguagem de programação SQL é formada por um conjunto de comandos que podem ser executados para os mais diversos objetivos. Para facilitar o entendimento e o uso dessa poderosa linguagem de banco de dados, os comandos são divididos em linguagem de definição de dados (DDL) e linguagem de manipulação de dados (DML). a. Linguagem de definição de dados (data definition language – DDL): fornece comandos para criação e modificação das tabelas do modelo de banco de dados, bem como a remoção de tabelas e criação de índices. Os principais comandos que fazem parte da DDL são: Create table, Drop table, Alter table. b. Linguagem de manipulação de dados (data manipulation language – DML): inclui uma linguagem de consulta aos dados das tabelas. Compreende os comandos para inserir, remover e modificar informações em um banco de dados. Os comandos básicos da DML são: Select, Insert, Update, Delete.
Os comandos da linguagem de definição de dados (DDL) O principal objetivo dos comandos da linguagem de definição de dados (DDL) é possibilitar ao usuário criar as tabelas do seu modelo de banco de dados, bem como os relacionamentos entre as tabelas. Para cada comando desse grupo você verá sua finalidade e sintaxe, ou seja, as regras para escrita dele. Antes de iniciar o estudo dos comandos de criação de tabelas, é importante que você fique por dentro dos tipos de dados em um banco de dados, pois esse item é essencial para a especificação dos valores que podem ser aceitos em cada campo, ou coluna, de uma tabela.
Os tipos de dados em SQL O padrão SQL aceita uma variedade de tipos de dados, incluindo os que seguem. ••
Varchar (n). É uma cadeia de caracteres de tamanho variável, com o tamanho “n” máximo definido pelo usuário. Por exemplo, um campo do tipo Varchar (30) significa que ali podem ser cadastrados no máximo 20 símbolos quaisquer, como números e letras.
••
Int. Define um tipo inteiro.
••
Numeric (p, d). É um número de ponto fixo cuja precisão é definida pelo usuário. O número consiste de “p” dígitos e “d” casas decimais. Por exemplo, um campo do tipo Numeric (9,2), tem a seguinte representação: 9999999.99, ou seja, 7 dígitos antes da vírgula e 2 após a vírgula, totalizando os 9 dígitos.
95
Capítulo 5 ••
Real. É um número com ponto flutuante, ou seja, a representação de número real pode mudar de acordo com a representação atual. Por exemplo, 100.0 é igual a 10.0 E+2.
••
Float(n). É um número com ponto flutuante com precisão definida pelo usuário em pelo menos “n” dígitos.
••
Date – representa uma data que contém um ano (com quatro dígitos), mês e dia do mês. Por exemplo, a data 23/01/1998 será representada no MySQL como sendo 1998-01-23. O formato padrão é YYYY-MM-DD.
••
Char (n) – strings de tamanho fixo entre 1 e 255 caracteres.
••
Text, Blob – strings de tamanho indefinido. A diferença entre TEXT e BLOB é que o primeiro armazena o texto como caractere e o segundo como objeto binário.
••
Bool ou boolean – tipo boleano (true/false), em que no valor igual a zero temos false e diferente de zero, então, temos true.
••
Time. Representa horário, em horas, minutos e segundos.
É importante salientar que outros tipos de dados podem existir, dependendo da tecnologia e do grau de sofisticação da ferramenta de SGBD. Bem, agora que você já conhece os tipos de dados existentes para linguagem SQL, eles serão úteis quando você for definir as tabelas que farão parte de seu banco de dados.
Seção 2 Como criar tabelas A partir desta seção estaremos diretamente tratando da terceira e última etapa da metodologia de modelagem de dados, onde a primeira, modelagem conceitual, e a segunda, modelagem em nível lógico, foram estudadas no Capítulo 3. Esta terceira etapa, denominada modelagem em nível físico, é responsável por transformar o modelo de dados em nível lógico em sentenças da linguagem SQL, para que o projeto possa ser implementado no SGBD. Antes da criação das tabelas no banco de dados é preciso criar uma área para receber essas tabelas. Essa área no MySQL é chamada de database.
96
Princípios de Bancos de Dados O database é considerado um agrupador de tabelas, ou seja, uma área de armazenamento na qual o projeto do banco de dados é armazenado. Podemos criar um database para cada modelo lógico, ou seja, para cada conjunto de tabelas que será utilizado num sistema. Sua sintaxe é: create database nome_do_database;
Para melhor exemplificar o trabalho com o SGBD, observe o modelo de dados apresentado na Figura 5.1 Figura 5.1 – Modelo relacional com as tabelas alunos, matrículas e cursos
Fonte: Elaboração do autor (2015).
Para o modelo lógico, representado na Figura 5.1, vamos criar o seguinte database: mysql> create database escola;
Após criar o database, é preciso informar ao MySQL qual database que ele deverá utilizar. Para isso, utilizamos o seguinte comando: mysql> use escola;
Uma vez criado um database, ao sair do MySQL e retornar, você não precisará criar novamente, apenas utilize o comando Use nome_database para continuar o seu trabalho. Para remover um database e, consequentemente, todas as tabelas contidas nele, utilize a seguinte sintaxe: mysql> drop database escola;
97
Capítulo 5 Para visualizar os databases existentes no banco de dados utilize: mysql> show databases;
Observe na figura a seguir como ficaram os comandos na ferramenta MySQL Command Line Client: Figura 5.2 – Resultado dos comandos de criação e manipulação de database
Fonte: MySQL Server (2015)
Há outras variações de comandos para criação e manipulação de database, mas, por enquanto, a variação aqui apresentada é suficiente para você continuar os estudos. Vamos, então, à criação das tabelas. A tarefa de criar uma tabela significa especificar seu nome, o nome de cada coluna e o tipo de dado que será armazenado em cada coluna. Porém, esse processo pode ser mais amplo. A tabela é um conjunto de dados e com isso pode-se especificar as regras de acesso e uso dessas tabelas, que recebem o nome de regras de integridade, ou tecnicamente falando, constraints. Uma tabela em SQL é definida usando o comando Create Table, que possui a seguinte sintaxe:
98
Princípios de Bancos de Dados
Create Table nome da tabela ( Atributo1 Tipo1, Atributo2 Tipo2, .., AtributoN TipoN, , , ..., );
Normalmente, um banco de dados armazena um conjunto grande de tabelas e para cada uma delas deverá ser executado um comando Create Table para sua definição. A esse conjunto de tabelas chama-se modelo lógico de dados, que representa de forma gráfica as tabelas do banco de dados. Para facilitar a sua compreensão, veja o modelo lógico a seguir: Os comandos a seguir descrevem os passos para implementação desse modelo, criando cada uma das tabelas, por meio do comando Create Table. Inicialmente, descreva o comando para criação física no computador da tabela Alunos: Create table Alunos ( Codigo_Aluno Int, Nome_Aluno Varchar(30), Idade Integer, Sexo Char );
Após a execução do comando, as tabelas podem ser manipuladas das mais diversas formas, como: inserção de dados, Na execução de comandos maiores, procure fazê-los mais definição de regras, alteração de dados etc. divididos em linhas, como no exemplo. Assim, se ocorrer um erro, o MySQL mostrará melhor onde ele está.
Para visualizar a estrutura da tabela criada usa-se o comando Describe.
Comando Describe: representa de forma estrutural os campos da tabela e seus respectivos tipos de dados.
99
Capítulo 5 O comando Describe possui a seguinte sintaxe: Describe Nome_Da_Tabela;
Nesse caso, após a execução do comando Describe Alunos, a seguinte estrutura será apresentada: Quadro 5.1 – Resultado da execução do comando describe alunos
Campos
Tipo
Primary Key
Null
Codigo_Aluno
Int
Sim
Nome_Aluno
Varchar(30)
Sim
Idade
Integer
Sim
Sexo
Char
Sim
Fonte: Elaboração do autor (2015).
O comando Describe pode ser abreviado por Desc, ficando assim: Desc Nome_Da_Tabela;
Observe que a tabela Alunos possui os campos Codigo_Aluno, do tipo de dado Inteiro (Int), Nome_Aluno do tipo Varchar, Idade do tipo Inteiro(int) e Sexo do tipo Char. Essas regras, até o momento, definem apenas o escopo de cada coluna, ou seja, nas colunas Código_Aluno e Idade só podem ser cadastrados números. Já o campo Nome_Aluno aceita um conjunto de caracteres de tamanho máximo 30, por fim, o campo Sexo aceita apenas um caractere (F ou M, por exemplo).
O que significa a expressão null? Todas as colunas da tabela Alunos possuem como valor para coluna null um “Sim”. Essa configuração determina ao banco de dados que todas as colunas da tabela Alunos podem ser cadastradas com valores nulos, nesse caso não são campos obrigatórios. Toda vez que criamos uma tabela, as colunas por padronização sempre começam com o status de null, ou seja, não obrigatórios.
100
Princípios de Bancos de Dados
Como definir para que o campo seja “obrigatoriamente cadastrado”? Você estudou anteriormente que toda vez que se cria uma tabela, suas colunas, por padrão, podem ser cadastradas com null, certo? Para definir que o campo de uma tabela é obrigatório, deve-se colocar a expressão not null na sua definição, da seguinte forma: Nome da Coluna Tipo de Dado Not Null. Vamos criar a tabela de Alunos novamente, mas dessa vez obrigando o cadastramento do código do aluno e do nome. Observe como ficaria o comando SQL: Create Table Alunos ( Codigo_Aluno Int not null, Nome_Aluno Varchar(30) not null, Idade Integer, Sexo Char );
A definição dos campos obrigatórios vale apenas para os campos definidos como not null (não nulos). Caso seja necessário especificar todos os campos como obrigatórios, deverá ser colocada a cláusula not null ao lado de cada campo. Após a execução desse comando, a estrutura da tabela passará a ter a seguinte formatação: Quadro 5.2 – Resultado do comando describe alunos
Campos
Tipo
Primary Key
Null
Codigo_Aluno
Int
Sim
Nome_Aluno
Varchar(30)
Sim
Idade
Integer
Sim
Sexo
Char
Sim
Fonte: Elaboração do autor (2015).
Outras regras de integridade dos dados As regras de integridade são importantíssimas para que um banco de dados evite redundância dos dados e garanta a sua integridade. A obrigatoriedade de informação dos dados de uma tabela é uma regra de integridade do banco de dados, e existem muitas outras que podem ser associadas a uma tabela, as regras de integridade permitidas englobam:
101
Capítulo 5
Primary Key(Atributo1, Atributo2, ..., AtributoN); Check (P); Foreign Key(Atributo1, Atributo2, ..., AtributoN) References Nome da Tabela;
Sobre a sintaxe apresentada, considere: ••
a especificação Primary Key diz que os atributos Atributo1, Atributo2,..., Atributo N formam a chave primária da tabela. A chave primária corresponde ao campo, ou campos, que não pode de forma alguma ter valores repetidos dentro da tabela. No caso da chave primária ser composta por mais de uma coluna, a combinação dessas colunas não poderá ter valores repetidos.
••
A cláusula check impõe um domínio a um determinado campo da tabela, forçando-o a aceitar apenas as opções pertencentes ao domínio estabelecido. Por exemplo, para a coluna Sexo pode ser estabelecido o domínio (‘M’,’F’). Isso é implementado por meio da seguinte cláusula check, ao se implementar uma tabela: ...check Sexo in (‘M’,’F’)... Dessa forma, o SGBD fará uma verificação sempre que um valor for inserido ou alterado na coluna Sexo, a fim de averiguar se seu valor encontra-se dentro do domínio estabelecido.
••
a cláusula Foreign Key inclui a relação dos atributos que constituem a chave estrangeira (Atributo1, Atributo2, ..., AtributoN) e em quais tabelas esses campos fazem referência aos campos formadores da chave primária em outra tabela.
••
todos os atributos de uma chave primária são declarados implicitamente como not null.
Agora que já temos a tabela Alunos criada, vamos definir um campo do tipo chave primária para essa estrutura. Como o Nome Chave Primária é aquele do Aluno, Idade e Sexo são campos que podem ter o campo que não pode, de mesmo valor, será adotado o Código do Aluno como forma alguma, repetir-se dentro da tabela. chave primária.
Como alterar a estrutura da tabela? A alteração da estrutura de uma tabela do banco de dados pode ser realizada de duas formas: ou se acrescenta um campo novo ou se altera a estrutura do campo desejado. A alteração realizada se refere SEMPRE à estrutura da tabela e NUNCA aos seus campos cadastrados. A alteração dos dados cadastrados é realizada por meio de outro comando.
102
Princípios de Bancos de Dados Retomando o exemplo da nossa tabela: Como a tabela Alunos já existe no nosso banco de dados, a tarefa que será executada representa uma alteração na estrutura da tabela. Será então adicionada a regra de chave primária ao campo Codigo_Aluno. Para isso, será necessária a utilização do comando em SQL: Alter Table, que possui a seguinte sintaxe: Alter Table Nome_ da_Tabela Operação Nome_ do_ Campo Nova Regra;
A operação pode ser: Quadro 5.3 – Formas de uso do comando Alter Table
Operação
Funcionalidade
ADD
Adiciona um novo campo.
MODIFY
Altera a estrutura de um campo.
Fonte: Elaboração do autor (2015).
Ao alterar o campo Codigo_Aluno para ser uma chave primária, sua sintaxe será: Alter Table Alunos Add Primary Key(Codigo_Aluno);
Observe se a alteração foi realizada. Para tanto, o comando Describe deve ser novamente executado, com a finalidade de descrever a estrutura da tabela. Quadro 5.4 – Estrutura da tabela de alunos após a execução do comando describe alunos
Campos
Tipo
Primary Key
Null
SIM
Não
Codigo_Aluno
Int
Nome_Aluno
Varchar(30)
Sim
Idade
Integer
Sim
Sexo
Char
Sim
Fonte: Elaboração do autor (2015).
Importante notar que a coluna Null para o campo Codigo_Aluno possui o valor Não, pois toda chave primária, obrigatoriamente, deve ser cadastrada. Caso você tenha esquecido de colocar o campo Codigo_Aluno como not null, por se tratar de uma chave primária, ele receberia automaticamente o not null.
103
Capítulo 5 Antes de continuar a criar as tabelas Cursos e Matrículas que fazem parte do modelo proposto nesta seção, realizaremos outras alterações na tabela Alunos, para exemplificar a sintaxe do comando Alter Table.
Como alterar o tamanho do campo Nome_Aluno? O comando a ser utilizado será: Alter Table Alunos Modify Nome_Aluno Varchar(40);
Observe que ao invés de utilizar a cláusula ADD, foi utilizada a cláusula Modify, pois o campo Nome_Aluno já existia, porém, com outro tamanho. Por isso, o campo foi apenas modificado. Acompanhe outras aplicações de comandos: Adicionando o campo Endereço, do tipo Varchar(30): Alter Table Alunos ADD Endereco Varchar(30); Adicionando o campo Cidade, do tipo Varchar(25): Alter Table Alunos ADD Cidade Varchar(25); Adicionando o campo UF, do tipo Varchar(02): Alter Table Alunos ADD UF Varchar(02); Tem-se, então, a estrutura da tabela: Quadro 5.5 – Resultado do comando describe alunos após as alterações
Campos
Tipo
Primary Key
Null
SIM
Não
Codigo_Aluno
Int
Nome_Aluno
Varchar (40)
Sim
Idade
Integer
Sim
Sexo
Char
Sim
Endereco
Varchar (30)
Sim
Cidade
Varchar (25)
Sim
UF
Varchar (02)
Sim
Fonte: Elaboração do autor (2015).
104
Princípios de Bancos de Dados
Como criar a estrutura da tabela em um grupo único de comandos? No item acima, na criação da tabela de Alunos, realizamos vários comandos para definição da tabela. Basicamente uma tabela é um conjunto de campos, chaves primárias e campos obrigatórios, sendo assim, podem-se criar todas essas regras num único passo, como será feito a seguir, na criação da tabela de Cursos, observe: Create Table Cursos ( Codigo_Curso Integer, Descricao Varchar(30) not null, Numero_Vagas Integer not null, Periodo Char, Primary Key(Codigo_Curso) );
Executando o comando Describe para visualizar a estrutura da tabela, tem-se: Quadro 5.6- Visualizando a estrutura da tabela Cursos
Campos
Tipo
Primary Key
Null
SIM
Não
Codigo_Curso
Int
Descricao
Varchar (30)
Não
Numero_Vagas
Integer
Não
Período
Char
Sim
Fonte: Elaboração do autor (2015).
Como característica dessa tabela, observe que os campos Codigo_Curso, Descrição e Número_Vagas são obrigatórios, e o campo Codigo_Curso também está associado a uma chave primária. O único campo que não necessita de valores é o campo Período.
Seção 3 Como definir tabelas relacionais em SQL Até aqui você criou um conjunto de tabelas que não estavam relacionadas e cuja principal finalidade era cadastrar diretamente os dados.
105
Capítulo 5 Figura 5.3 – Modelo relacional com as tabelas alunos, matrículas e cursos
Fonte: Elaboração do autor (2015).
Porém, no modelo de dados do início desta unidade, foi apresentada uma tabela associativa chamada Matrícula, a qual deverá conter o Aluno matriculado e o Curso em que ele se matriculou e a data na qual efetuou isso. Note que como essa tabela é associativa, não há necessidade de recadastrar os dados do aluno e do curso, mas sim fazer uma referência das tabelas Alunos e Cursos à tabela Matrícula. A referência entre tabelas chama-se de chave estrangeira. A tabela Matrícula terá duas chaves estrangeiras, os campos cod_alu_mat e cod_curso_mat, que referenciam a tabela Alunos pelo campo Codigo_Aluno e a tabela Cursos pelo campo Codigo_Curso, respectivamente. Em outras palavras, pode-se dizer que o código identificador do aluno matriculado na tabela de Matrículas, que estará armazenado no campo cod_ alu_mat, deverá vir da tabela de Alunos, originário do campo Codigo_Aluno. Já o Código do Curso na tabela Matrículas, que será armazenado no campo cod_ curso_mat, deverá vir da tabela Cursos, pelo campo Codigo_Curso. Veja o diagrama a seguir: Figura 5.4 – Origem dos valores das colunas COD_ALU_MAT E COD_CURSO_MAT
Fonte: Elaboração do autor (2015).
106
Princípios de Bancos de Dados Essas referências possuem a seguinte regra e são feitas na seguinte sintaxe: ••
para o campo cod_alu_mat da tabela de matrículas, que os dados vêm da tabela de Alunos, pelo campo Codigo_Aluno, o comando em SQL é : Foreign Key (cod_alu_mat) references Alunos (Codigo_Aluno);
••
para o campo cod_curso_mat da tabela de matrículas, que os dados vêm da tabela de Cursos, pelo campo Codigo_Curso, o comando em SQL é: Foreign Key (cod_curso_mat) references Cursos (Codigo_Curso);
Como definir os tipos de dados das chaves estrangeiras? O que é e como referenciar uma chave estrangeira você aprendeu no item anterior, porém, qual o tipo de dados de um campo cujos seus dados vêm de outra tabela? Bem, como o campo cod_alu_mat da tabela de matrículas vem do campo Codigo_Aluno em alunos, os dois campos devem ter o mesmo tipo, sendo assim, o campo cod_alu_mat será do tipo: Int (inteiro). A mesma regra deve ser aplicada ao campo cod_curso_mat. Como o campo Codigo_Curso na tabela de Cursos é Integer, o campo cod_curso_mat deverá ser inteiro (Int) também. Como o campo Data_Matricula não é um campo chave estrangeira, ele será do tipo Date. Como um aluno não pode se matricular no mesmo curso, na mesma data, todos os campos da tabela de Matrículas serão chaves primárias. Dessa forma, a tabela Matrículas seria criada pelo comando SQL a seguir: Create Table Matriculas ( Cod_Alu_Mat Int, Cod_Curso_Mat Int, Data Date, Primary Key (Cod_Alu_Mat, Cod_Curso_Mat, Data), Foreign Key (Cod_Alu_Mat) references Alunos (Codigo_Aluno), Foreign Key (Cod_Curso_Mat) references Cursos(Codigo_Curso) );
107
Capítulo 5 A representação da tabela Matrículas a partir do comando Describe: Quadro 5.7 – Estrutura da tabela de matrículas após a execução do comando describe
Campos - Field
Tipo - Type
Primary Key - Key
Null
Cod_Alu_Mat
Integer
Sim
Não
Cod_Curso_Mat
Integer
Sim
Não
Data_Matricula
Date
Sim
Não
Fonte: Elaboração do autor (2015).
Note que todos os campos são marcados com Primary Key igual a SIM, numa referência à chave primária. Bem, com isso você finalizou o processo de transformar o modelo lógico do início desta unidade num modelo físico, ou seja, você transformou um modelo lógico num banco de dados de verdade.
Seção 4 Como remover as tabelas do banco de dados Assim como você pode criar novas tabelas para um banco de dados, você também pode removê-las por meio do comando Drop Table. A sintaxe desse comando é: Drop Table Nome_da_tabela;
Porém, algumas regras devem ser respeitadas para que uma tabela seja removida, ou “dropada” no jargão de banco de dados. E essas regras são:
108
••
uma tabela só pode ser removida se ela não possuir campos que são chave estrangeira para outra tabela;
••
se uma tabela possui referência como chave estrangeira para outra tabela, será preciso primeiro remover as tabelas para qual são feitas as referências, para depois removê-la;
••
uma tabela só pode ser removida se não possuir nenhum dado cadastrado, caso contrário, primeiro ela deverá ser esvaziada para depois ser removida.
Princípios de Bancos de Dados Para remover a tabela Alunos do banco de dados criado anteriormente, por meio do comando Drop Table, executa-se o comando com a seguinte sintaxe: Drop Table Alunos Porém, ao se excluir a tabela de Alunos será criada uma inconsistência no esquema do banco de dados, pois a tabela Matrículas estará fazendo referência a uma tabela que não existe mais, dessa forma, os dados não refletem a realidade esperada. Para que esse problema não ocorra, o SGBD não permitirá que a tabela Alunos seja removida, pois há alguma outra tabela no esquema que depende dela. Veja o diagrama a seguir: Figura 5.5 – Representação da remoção de uma tabela com campos que são foreign key para outra tabela
Fonte: Elaboração do autor (2015).
Para se excluir a tabela de Alunos, é necessário que se exclua antes a tabela de Matrículas, para que a ligação entre elas não exista mais. Note que o processo de exclusão de tabelas pode resultar em problemas graves
109
Capítulo 5 de inconsistência de dados, gerando uma necessidade de exclusão de várias tabelas do banco de dados, devido ao número de relacionamento entre elas. Portanto, é fundamental que ao se modelar um banco de dados sejam previstos todos os relacionamentos possíveis e as tabelas que são realmente necessárias, a fim de se evitar o retrabalho. Nesse casso, para excluir a tabela Alunos é necessário dois comandos Drop Table, com as seguintes sintaxes: ••
Drop Table Matriculas; Figura 5.6 – Remoção da tabela matrículas
Fonte: Elaboração do autor (2015).
••
Drop Table Alunos; Figura 5.7 – Remoção da tabela alunos
Fonte: Elaboração do autor (2015).
Após esses dois comandos, o modelo de banco de dados ficará assim: Figura 5.8 – Tabelas restantes após as remoções
Fonte: Elaboração do autor (2015).
O próximo passo é compreender os comandos que permitam manipular os dados desse banco de dados, fazendo inserções, alterações, exclusões e consultas nas tabelas, os chamados comandos de DML (linguagem de manipulação de dados). Este assunto será tratado no Capítulo 6.
110
Capítulo 6 Acesso e Manipulação de Dados
Habilidades
•• Compreender a estrutura dos comandos de manipulação dos dados •• Manipular os dados em tabelas do banco de dados
Seções de estudo
Seção 1: A estrutura dos comandos de manipulação de dados. Seção 2: Inserção de dados na tabela. Seção 3: Alteração de dados da tabela. Seção 4: Exclusão de dados da tabela.
111
Capítulo 6
Seção 1 A estrutura dos comandos de manipulação de dados Nesta Unidade de Aprendizagem, você já utilizou uma ferramenta de banco de dados para criação de um modelo de banco de dados, ou seja, já criou um conjunto de tabelas que atendem a alguma necessidade específica. Porém, você criou as tabelas, implementando, inclusive, as regras de chaves primárias e estrangeiras, mas não utilizou efetivamente o modelo criado. Pensando bem, de pouca valia seria você ter um modelo de banco de dados vazio, sem a possibilidade de cadastrar e manipular os dados. Por isso, neste capítulo, você será apresentado um novo conjunto de comandos, os comandos de manipulação de dados, chamados abreviadamente de DML, do ingês, Data Manipulation Language. Por meio desses comandos você poderá cadastrar, alterar, remover e consultar os dados de uma determinada tabela. Sendo assim, nesta unidade você aprimorará seu conhecimento sobre comandos de SQL e que normalmente um profissional de banco de dados, ou um analista de sistemas utiliza no seu dia a dia, gerando relatórios, consultando ou fazendo o cadastramento de dados. A linguagem de programação SQL é formada por dois grupos de comandos. Nesta unidade, você estudará um desses grupos: o grupo dos comandos DML ou Linguagem de Manipulação de Dados. O outro grupo já foi discutido no capítulo 5, o grupo dos comandos DDL ou Linguagem de Definição de Dados.
1.1 Linguagem de manipulação de dados – DML A linguagem de manipulação de dados é utilizada para a consulta e atualização do banco de dados, compreende os comandos para inserir, remover e modificar informações. Os comandos básicos da DML são: Select, Insert, Update, Delete. Basicamente o papel de cada comando dentro da DML é: ••
112
SELECT é o comando mais usado do DML, permite ao usuário especificar uma pesquisa com uma descrição do resultado desejado;
Princípios de Bancos de Dados ••
INSERT é usado para cadastrar dados em uma tabela. Serve para popular uma tabela com dados. Essa inserção segue as regras definidas na criação da tabela, como por exemplo: campos referenciados por chave primária não poderão ter valores repetidos, campos referenciados por chave estrangeira devem ter o seu valor correspondente na tabela da qual ele depende, entre outras;
••
UPDATE altera os valores dos campos já cadastrados na tabela;
••
DELETE permite remover registros existentes em uma tabela.
Cada comando possui uma sintaxe própria e é isso que você vai aprender a partir de agora, as características de cada um desses comandos, como escrevê-lo e o que esperar como resposta do banco de dados após a sua execução.
Seção 2 Inserção de dados em uma tabela O comando Insert permite a inclusão de novas linhas na tabela. Pode ser incluída uma linha de cada vez, ou várias linhas resultantes de uma consulta. As colunas da lista de inserção podem estar em qualquer ordem. Caso o valor inserido não atenda o tipo de dado definido para aquela coluna, a inserção não será realizada, sendo apresentada uma mensagem indicando qual ou quais campos não atendem a alguma regra Inserção de integridade. Os campos do tipo not null são No caso de inserção de dados em colunas referenciadas por obrigatórios, dessa forma, a inserção só será chave primária, a inserção só será realizada se os dados forem informados. realizada se o valor a ser inserido não existir para a mesma coluna da tabela em outros registros. Para colunas referenciadas por chave estrangeira, o valor inserido deve corresponder a um valor válido presente na tabela da qual ele depende.
Acompanhe o exemplo a seguir. A estrutura da tabela para cadastramento de produtos é:
113
Capítulo 6
2.1 Caso 1. A inserção não precisa seguir a ordem das colunas Figura 6.1 – Estrutura da tabela
Produto Codigo_Produto Integer NN (PK) Descricao_Produto Varchar (30) NN Preco_Produto Float NN Fonte: Elaboração do autor, 2015.
A inserção não precisa ser na ordem das colunas da tabela. Você pode inserir um dado informando primeiro o Preço, depois a Descrição e, por último, o Código.
2.2 Caso 2. A Inserção deve obedecer ao tipo de dado da Coluna Na mesma tabela Produtos, se for inserido o código do produto valendo “ABCD”, a inserção não será realizada, pois a coluna é do tipo inteiro (integer). Portanto, só aceita números como dados de cadastro.
2.3 Caso 3. A Inserção de campos not null Na tabela de produtos, se você analisar, perceberá que todos os campos são do tipo not null, pois há um NN (not null) ao lado de cada campo da tabela. Isso significa que todos os campos são obrigatórios. Se a inserção for executada sem que todos os campos tenham seu valor informado, a inserção será cancelada.
2.4 Caso 4. Campos do tipo chave primária O campo Codigo_Produto é do tipo chave primária, pois o campo possui um “PK” ao lado da definição do tipo. Assim sendo, caso seja feita uma inserção com um valor que essa coluna já possui, a inserção será cancelada. Agora que você já sabe dos cuidados a serem tomados na inserção de dados, é hora de aprender a sintaxe desse comando: Insert into Nome_Da_tabela (Coluna1,Coluna2,...,ColunaN) values (Valor1, Valor2,..., ValorN);
114
Princípios de Bancos de Dados Cadastrando os elementos na tabela Produtos apresentada anteriormente, tem-se: Codigo_Produto
Descricao_Produto
Preco_Produto
1
Banana
0,79
2
Uva
2,50
3
Maçã
1,80
4
Mamão
2,00
Os campos que não são do tipo numérico devem ficar entre aspas (‘). Insert into Produtos values (1, ’Banana’, 0.79); Veja o que acontece ao se executar esse comando: Figura 6.2 – Cadastramento de produtos na tabela
Insert into Produtos Values
Produtos
(1,
BANANA,
0,79);
Codigo_Produto
Descricao_Produto
Preco_Produto
Fonte: Elaboração do autor, 2015.
Continuando as inserções: Insert into Produtos values (2, ’Uva’, 2.50); Insert into Produtos values (3, ’Maçã’, 1.80); Insert into Produtos values (4, ’Mamão’, 2.00);
Nos casos acima, a inserção dos dados é para todos os campos da tabela, nesta situação não há a necessidade de especificar as colunas da tabela no Insert, porém, os valores devem estar na ordem em que as colunas são apresentadas na tabela. Se você seguisse a regra geral do Insert, o comando ficaria assim: Insert into Produtos (Codigo_Produto,Descricao_Produto,Preco_Produto) Values (1, ’Banana’, 0.79);
115
Capítulo 6 Sempre que estiver construindo uma interface de um software, ou qualquer outro programa que necessite incluir um comando de inserção, adote como padrão sempre definir os nomes das colunas, isso pode minimizar erros futuros, devido a alterações na base de dados, como por exemplo, a inclusão de uma nova coluna não obrigatória (null), a qual o seu insert não está preparado. Ao inserir dados informalmente nas tabelas, aí sim, para obter maior produtividade e digitar um número mínimo de caracteres, sugiro que omita a definição dos nomes das colunas nos comandos de inserção. Adote a definição dos nomes das colunas somente quando não for inserir dados para todas as colunas da tabela. Agora, vamos tentar cadastrar um novo produto, porém, com o código de produto valendo 1, que já foi cadastrado anteriormente: Insert into Produtos (Descricao_Produto, Preco_Produto, Codigo_Produto) Values (’Banana’, 0.79,1);
Note que, ao inverter a ordem dos campos, foi necessário escrever o nome de cada coluna que receberá os valores da inserção. Por padrão, a inserção segue a ordem da criação da tabela, que pode ser visualizada pelo comando Describe. O resultado da execução do comando será: Figura 6.3 – Execução do comando describe
Fonte: MySQL Server, 2015.
Dessa forma, a inserção não será realizada. No caso a seguir, vamos forçar a inserção de um campo literal (varchar) na coluna de preços: Insert into Produtos Values (10, ’Banana’, ’OLA’);
116
Princípios de Bancos de Dados O resultado da execução do comando será: Figura 6.4 – Execução do comando describe – inserção campo literal
Fonte: MySQL Server, 2015.
Sendo assim, a inserção não será realizada. Para finalizar, vamos tentar inserir alguns campos nulos: Insert into Produtos (Codigo_Produto, Descricao_Produto) Values (100, ’Batata’);
O resultado da execução do comando será: Figura 6.5 – Execução do comando describe – inserção campo nulo
Fonte: MySQL Server, 2015
Sendo assim, a inserção não será realizada. Agora que você já sabe como inserir dados em uma tabela, que tal você aprender a alterar as informações cadastradas? Esse será seu próximo aprendizado.
117
Capítulo 6
Seção 3 Alteração de dados da tabela O comando Update altera os valores das colunas especificadas em todas as linhas que satisfaçam a uma determinada condição. As colunas a serem alteradas devem ser declaradas no comando Update e seguidas dos novos valores para cada coluna, respectivamente. Somente as colunas a serem modificadas devem aparecer na lista de colunas da declaração. Os novos valores de cada coluna devem obedecer ao tipo de dados da coluna, ou seja, campos do tipo numérico só podem receber números e campos não numéricos devem ter o valor de atualização entre aspas simples (‘), assim como realizado no comando de inserção. Atenção: Caso não seja especificada uma condição, a alteração valerá para todas as linhas da tabela.
3.1 O que é uma condição? Uma condição é qualquer decisão que você inclua no seu comando de SQL. Essa decisão está associada a um operador lógico. Se a condição retornar verdadeiro (true) a execução do comando será realizada. O objetivo da condição é de restringir a atuação do comando que está sendo executado para um conjunto de registros da tabela. Os operadores lógicos são: Operador
Função
=
Igual
<>
Diferente
>
Maior
<
Menor
>=
Maior ou Igual
<=
Menor ou Igual
AND
E Lógico – Verdadeiro quando todas as condições são verdadeiras.
OR
OU Lógico – Verdadeiro quando pelo menos uma condição é verdadeira.
NOT
Não – Inverte o valor. Se for verdadeiro, troca para falso, e vice-versa.
Fonte: Elaboração do autor, 2015.
118
Princípios de Bancos de Dados Bem, agora que você já sabe dos cuidados a serem tomados na alteração de dados, é hora de aprender a sintaxe desse comando: Update Nome_Da_tabela set Coluna1= valor1,...,ColunaN = valor N Where (Condição);
Para exercitar esse comando, serão alterados alguns dados da tabela de produtos que foram inseridos por meio do comando Insert. As alterações serão as seguintes: Figura 6.6 – Comando insert
Codigo_Produto
Descricao_Produto
Preco_produto
1
Banana
0.79
2
Uva
2.50
3
Maçã
1.80
4
Mamão
2.00
Trocar o código para 15
Trocar a descrição para Melancia
Trocar o preço para 2.10
Fonte: Elaboração do autor, 2015.
a. Trocando a descrição do produto “Banana” para “Melancia” e o preço para 2.10: Update Produtos set Descricao_Produto = ‘Melancia’, Preco _Produto = 2.10 Where Codigo_Produto = 1;
ou Update Produtos set Descricao_Produto = ‘Melancia’, Preco _Produto= 2.10 Where Descricao_Produto = ‘Banana’;
119
Capítulo 6 b. Trocando o Código do produto “Uva” para 15: Update Produtos set Codigo_Produto = 15 Where Codigo_Produto = 2;
ou Update Produtos set Codigo_Produto = 15 Where Descricao_Produto = ‘Uva’;
c. Dar um aumento de 10% em todos os produtos cadastrados: Update Produtos set Preco_Produto = Preco_Produto * 1.1
Como não foi apresentada uma condição Where, a atualização será realizada em todas as linhas da tabela, na coluna Preco_Produto. Veja a tabela com os dados atualizados: Quadro 2 – Dados da tabela produtos
Codigo_Produto
Descricao_Produto
Preco_produto
1
Melancia
2.31
15
Uva
2.75
3
Maçã
1.98
4
Mamão
2.20
Fonte: Elaboração do autor, 2015.
Seção 4 Exclusão de dados da tabela O comando Delete exclui as linhas que satisfazem a uma determinada condição na tabela especificada. Se a condição estiver ausente, o efeito é a exclusão de todas as linhas da tabela. O resultado vai ser uma tabela válida, porém, vazia.
120
Princípios de Bancos de Dados Caso a tabela possua alguma coluna que seja chave estrangeira para outra tabela, e os valores dessa tabela estão sendo usados como referência, a exclusão não poderá ser realizada, pois a exclusão resultará em campos nulos na tabela referenciada. Veja a representação abaixo: Figura 6.7 – Exclusão de dados na tabela.
Produtos Codigo_Produto
Descricao_Produto
Preco
1
Melancia
2.10
2
Uva
2.50
4
Goiaba
2.00
10
Maçã
1.80
Nota_Fiscal Numero
Data
Valor
1
2005-07-28
100.00
ItensDaNota Produto
Item
Qde
NotaFiscal
1
1
2
1
2
2
10
1
4
3
15
1
10
4
7
1
Fonte: Elaboração do autor, 2015.
A tabela ItensDaNota é uma entidade associativa, pois para que essa tabela possua dados cadastrados é necessário que existam dados na tabela de produtos e na de notas fiscais. Se for solicitado um comando para excluir os dados da tabela Nota_Fiscal, a referência a essa tabela na entidade ItensDaNota ficará com qual valor?
121
Capítulo 6 Caso você tenha imaginado que a tabela ItensDaNota ficaria inalterada, isso não pode ocorrer, pois a tabela estaria referenciando um valor que não existe mais na tabela de origem (Nota_Fiscal). Por outro lado, a tabela não pode ficar com esses campos nulos (vazios), pois são do tipo not null. Assim sendo, o comando SQL que exclui os dados da tabela Nota_Fiscal será cancelado, de forma que a exclusão não seja realizada. Com certeza, agora você deve estar se perguntando, como excluir esses dados, então? Bem, no caso de exclusão de dados em uma tabela que tem dados compartilhados, primeiro devemos excluir os dados nas tabelas que fazem a associação para depois excluir na tabela principal. Por exemplo, no caso acima, que utilizava a tabela Nota_Fiscal e ItensDaNota, primeiro devem ser excluídos os dados da tabela ItensDaNota, para depois excluí-los na tabela Nota_Fiscal. À medida que você avançar nos exercícios de SQL esses cuidados ficarão mais claros, por enquanto são apenas lembretes para a parte prática dos comandos. Bem, agora só falta você conhecer a sintaxe do comando de exclusão de dados: Delete from Nome_Da_Tabela where Condição;
Veja como é fácil a utilização desse comando, mas antes vejamos os dados que devem ser excluídos: Tarefa
Comando em SQL
Remover os produtos que custam menos de dois (2) reais
Delete from Produtos where Preco_produto<2
Remover o produto de descrição Melancia
Delete from Produtos where Descricao_produto = ‘Melancia’
Remover o produto de código 3
Delete from Produtos where Codigo_Produto=3
122
Princípios de Bancos de Dados Caso a intenção seja excluir todos os dados da tabela, basta executar o comando: Delete from Nome_Da_Tabela; É importante que você atente para as condições que envolvem tipos que não são numéricos. Imagine que na tabela de produtos tenha-se um produto com a descrição “PÊRA”. A palavra PERA é diferente de Pera que é diferente de PÊRA. Ou seja, os dados alfanuméricos são “case sensitive”, em que maiúsculo é diferente de minúsculo, além da acentuação. Nesse caso, ao usar o comando a seguir, esse não excluíra o registro, pois PERA é diferente de PÊRA: Delete from Produtos where Descricao_Produto = ‘PERA’
Para solucionar esse problema, você pode utilizar a função de conversão para maiúscula, que se chama Upper e possui a seguinte sintaxe: Upper (Coluna); Essa função retorna o valor literal que está na coluna especificada com todos os caracteres em maiúsculo. Veja: Nome
Upper(Nome)
marcelo
MARCELO
Marcelo
MARCELO
MARCELO
MARCELO
MArCElO
MARCELO
Aplicando essa função ao comando que exclui o produto PERA, o comando ficaria assim: Delete from Produtos where Upper(Descricao_Produto) = Upper(‘pêra’);
123
Capítulo 7 Recuperação de Informações do Banco de Dados
Habilidades
•• Recuperar dados armazenados na base de dados. •• Utilizar-se do recurso de agrupamento de dados •• Construir consultas complexas utilizando-se de subconsultas e de funções de agregação.
Seções de estudo
Seção 1: A estrutura do comando Select. Seção 2: Ordenando e agrupando dados. Seção 3: Funções de agregação. Seção 4: Condições especiais.
125
Capítulo 7
Seção 1 A estrutura do comando Select Para criar e trabalhar com tabelas de um banco de dados é necessário utilizar um conjunto de comandos da linguagem de definição de dados (create table) e de comandos que permitam a manipulação desses, permitindo o cadastramento, a alteração e a exclusão dos dados (conforme já abordado em capítulos anteriores, isso se dá por meio dos comandos Insert, Update e Delete, respectivamente). Como podemos perceber no decorrer desta Unidade de Aprendizagem, a transformação de processos executados por pessoas num ambiente real em um modelo de dados por meio de tabelas e relacionamentos, pode ser relativamente complexa, dependendo do ambiente que se está informatizando. Cada processo possui um conjunto de possibilidades e todas elas devem ser amplamente contempladas, por meio da sua solução. Por isso, a criação do banco de dados dever ser realizada analisando-se todo o escopo do problema. Uma vez projetado o banco de dados e devidamente implementado, podemos armazenar e manipular os dados contidos nessa base. Entre as possibilidades de manipulação dos dados temos o cadastramento de informações, por meio do comando insert, a atualização de informações, através do comando update e a eliminação de registros, pelo comando delete. Tanto a modelagem, implementação e manipulação dos dados já foram abordadas em capítulos anteriores nesta Unidade de Aprendizagem, porém, ainda não foi trabalhada a operação de recuperação das informações ali armazenadas e, convenhamos, não teria muita valia uma base de dados repleta de informações, se não conseguirmos recuperá-las posteriormente. Neste capítulo estaremos tratando pontualmente desta operação, a recuperação dos dados armazenados. Estará sendo abordada a estrutura do comando SELECT, responsável por esta tarefa, bem como suas cláusulas adicionais para permitir que consigamos extrair as informações com o máximo de eficiência. Para ilustrar e facilitar a compreensão da estrutura do comando select, será utilizada inicialmente como base uma tabela bastante simples, cuja estrutura está apresentada na Figura 7.1.
126
Princípios de Bancos de Dados Figura 7.1 – Tabela Produtos
Produto Codigo_Produto Integer NN (PK) Descricao_Produto Varchar (30) NN Preco_Produto Float NN Fonte: Elaboração do autor (2015).
A estrutura inicial do comando SELECT é a que se segue: SELECT FROM WHERE
As informações entre os símbolos < > devem ser fornecidas. Como a tabela é a junção entre linhas e colunas, para recuperarmos os dados é necessário ter-se duas informações: os dados de quais colunas serão recuperados e de quais linhas devem ser recuperadas. A definição das linhas a serem mostradas é resultado direto de uma condição que especifica os dados a serem apresentados. Por exemplo, para recuperar os produtos cujo preço_ é menor que 200, a condição deve explicitar esta regra: preco_produto < 200. Como definir quais as colunas que devem ser mostradas de uma tabela? É muito simples! Você deverá especificar o nome das colunas que deseja listar. Caso queira listar todas as colunas, não precisa escrever o nome de todas elas, mas sim representá-las por um asterisco (*). A seleção dos dados que serão mostrados é realizada pelo comando Select, que retorna as linhas ou registros de uma ou mais tabelas que atendam a uma determinada condição. Como no comando Update e Delete, uma condição é representada pela cláusula Where. Se a cláusula Where for omitida, todas as linhas da tabela serão listadas. Veja a figura a seguir representando o que foi descrito anteriormente:
127
Capítulo 7 Figura 7.2 – Exemplo de tabela Área de atuação do SELECT (colunas)
Coluna 1
Coluna 2
Área de atuação da cláusula WHERE (linhas)
Coluna N
–
Fonte: Elaboração do autor (2015).
A cláusula Select especifica quais as colunas que serão apresentadas no resultado da seleção dos dados. Já a cláusula Where especifica quais as linhas da tabela que serão apresentadas no final da execução do comando. Um comando Select determina quais as colunas e linhas de uma tabela que formam um subconjunto de dados desejado. Apresentando agora a estrutura mais completa do comando Select: Select (Coluna1, Coluna2,..., ColunaN) From Nome_Tabela [ou tabelas] Where Condição Group By expressão Having condição Order By expressão.
Como você pode perceber, o comando Select é bem amplo, porém, algumas cláusulas só serão usadas quando realmente necessário, diminuindo sensivelmente o tamanho do comando a ser escrito. Quais os termos básicos do Comando Select? Como a estrutura do comando Select é bem ampla, vou descrevê-los a seguir para que você possa compreender o papel de cada termo existente nessa estrutura. Você vai começar a ter contato com a estrutura básica do comando Select, que é: Select (Coluna1, Coluna2,..., ColunaN) From Nome_Tabela
128
Princípios de Bancos de Dados a. Colunas do Select A expressão Coluna1, Coluna2... ColunaN representa as colunas que devem ser listadas pelo comando Select. Caso você deseja selecionar todos os campos, pode simplesmente colocar um asterisco (*) no lugar do nome das colunas. O Quadro 7.1 ilustra como ficaria o comando select para selecionar algumas colunas. Quadro 7.1 – Tarefas e respectivos comandos
Tarefa
Comando em SQL
...
...
...
...
Lista os Preços, Códigos e Descrições
Select Preco_Produto, Codigo_Produto, Descricao_Produto
...
...
Fonte: Elaboração do autor (2015).
b. A cláusula From A cláusula From especifica uma ou mais tabelas de origem para o Select. Se múltiplas tabelas de origem forem especificadas, o resultado será, conceitualmente, o conjunto das linhas de todas as tabelas envolvidas na seleção, que se chama produto cartesiano. Por exemplo, com base na tabela Produtos, as mesmas consultas anteriores seriam escritas especificando a tabela de origem da consulta, dessa forma: Quadro 7.2 - Tarefas e respectivos comandos acerca da tabela Produtos
Tarefa
Comando em SQL
Listar os códigos dos produtos
Select Codigo_Produto from Produtos
Listar os códigos e as descrições
Select Codigo_Produto, Descricao_Produto from Produtos
Listar os preços, códigos e descrições
Select Preco_Produto, Codigo_Produto, Descricao_Produto from Produtos
Listar todos os campos
Select Codigo_Produto, Descricao_Produto, Preco_Produto from Produtos Ou simplesmente Select *from Produtos
Fonte: Elaboração do autor (2015).
- Agora vamos um pouco além do básico, vamos verificar como funciona a cláusula where do comando Select.
129
Capítulo 7 c. A cláusula Where A cláusula Where é responsável por restringir quais as linhas das tabelas serão apresentadas. A cláusula Where possui a forma geral: Where expressão_booleana;
A expressão_booleana pode ser qualquer expressão que retorne um valor booleano (verdadeiro ou falso). Só para fixar, os operadores lógicos são: Quadro 7.3 – Operadores lógicos
Operadores lógicos
Significado
=
Igual
>
Maior
<
Menor
<>
Diferente
>=
Maior ou igual
<=
Menor ou igual
AND
É lógico
OR
OU lógico
Fonte: Elaboração do autor (2015).
Para melhor exemplificar utilizaremos a tabela de Produtos com os dados abaixo: Quadro 7.4 – Tabela Produtos – dados
Codigo_Produto 1
Descricao_Produto Banana
1.00
2
Maçã
2.80
3
Uva
3.25
4
Pera
1.00
5
Maracujá
2.00
6
Mamão
2.55
7
Goiaba
2.00
8
Laranja
3.25
9
Limão
0.85
Melancia
2.55
10
Fonte: Elaboração do autor (2015).
130
Preco_Produto
Princípios de Bancos de Dados 1. Listar todos os Produtos: utilize o comando que listará a tabela de modo idêntico à tabela anterior. Select * from Produtos.
Resultado: esse comando listará a tabela de modo idêntico à tabela anterior. Lembre-se que a cláusula Where atua nas linhas da tabela, realizando um filtro. Como no comando acima não foi especificada a cláusula Where, não haverá restrição de linhas. A cláusula Select especifica quais as colunas serão apresentadas. No caso acima, o símbolo asterisco (*) representa todas as colunas da tabela.
2. Listar os produtos com preço inferior a 3.00: Select * from Produtos where preco_produto < 3.00;
Resultado: Quadro 7.5 – Tabela Produtos – resultado
Codigo_Produto 1
Descricao_Produto Banana
Preco_Produto 1.00
2
Maçã
2.80
4
Pera
1.00
5
Maracujá
2.00
6
Mamão
2.55
7
Goiaba
2.00
9
Limão
0.85
Melancia
2.55
10
Fonte: Elaboração do autor (2015).
3. Listar o código e a descrição dos produtos com preço de 1.00: Select Codigo_Produto,Descricao_Produto from Produto where Preco_Produto = 1.00;
Resultado: Codigo_Produto
Descricao_Produto
1
Banana
4
Pêra
131
Capítulo 7 4. Listar os dados do produto de código 5: Select * from Produtos where Codigo_Produto = 5;
Resultado: Codigo_Produto 5
Descricao_Produto Maracujá
Preco_Produto 2.00
5. Listar os dados de todos os produtos com preço = 1.00 e código = 10: Select * from Produtos where Preco_Produto = 1 AND Codigo_Produto = 10;
Resultado: Codigo_Produto
Descricao_Produto
Preco_Produto
OBS: Nenhuma linha da tabela atende a condição acima.
6. Listar a descrição e os preços dos produtos: Select Descricao_Produto, Preco_Produto from Produtos;
Resultado: Quadro 7.6 – Tabela Produtos – resultado
Descricao_Produto
Preco_Produto
Banana
1.00
Maçã
2.80
Uva
3.25
Pera
1.00
Maracujá
2.00
Mamão
2.55
Goiaba
2.00
Laranja
3.25
Limão
0.85
Melancia
2.55
Fonte: Elaboração do autor (2015).
O comando Select possui várias atribuições e possibilita inúmeras combinações. Assim, tem-se a possibilidade de construir comandos que extraiam exatamente os dados desejados, seguindo as suas necessidades.
132
Princípios de Bancos de Dados
Seção 2 Ordenando e agrupando dados Você aprendeu até agora sobre como acessar e listar os dados da tabela. Nesta seção, aprenderá como melhorar a apresentação dos dados que são listados, ordenando-os e agrupando-os. Para isso, será necessário que você conheça mais duas cláusulas do comando Select, as cláusulas Order By e Group By. a. A cláusula Order by A cláusula Order By deve ser sempre a última cláusula do comando select e serve para ordenar os dados que serão listados. A ordem de apresentação pode ser crescente, utilizando-se a cláusulaASC ou decrescente, utilizando-se a cláusula DESC. Se não for informada a ordem de apresentação, ela será por padrão crescente. A sintaxe da cláusula é: Order By (coluna1,coluna2,...,colunaN ) ASC ou DESC;
Um item do Order By pode ser o nome ou o número da coluna na estrutura da tabela. Veja a tabela abaixo após a execução do comando Select * from Produtos; Quadro 7.7 – Tabela de código, descrição e preço
Codigo_Produto
Descricao_Produto
Preco_Produto
1
Banana
1.00
2
Maçã
2.80
3
Uva
3.25
4
Pera
1.00
5
Maracujá
2.00
6
Mamão
2.55
7
Goiaba
2.00
8
Laranja
3.25
9
Limão
0.85
Melancia
2.55
10
Fonte: Elaboração do autor (2015).
133
Capítulo 7 Usando o Order By, a tabela será listada em ordem decrescente de Código: Select * from Produtos Order By Codigo_Produto Desc;
Resultado: Quadro 7.8 – Tabela Produtos – resultado usando o Order By
Codigo_Produto
Descricao_Produto
10
Preco_Produto
Melancia
2.55
9
Limão
0.85
8
Laranja
3.25
7
Goiaba
2.00
6
Mamão
2.55
5
Maracujá
2.00
4
Pera
1.00
3
Uva
3.25
2
Maçã
2.80
1
Banana
1.00
Fonte: Elaboração do autor (2015).
Veja outros exemplos do uso do Order By para mesma tabela. 1. Listar código e descrição dos produtos com preço menor que 3.00, em ordem crescente de descrição: Select Codigo_Produto,Descricao_Produto from Produtos where Preco_Produto < 3.00 Order By Descricao_Produto;
Resultado: Quadro 7.9 – Tabela Produtos
Codigo_Produto 1 7 9 2 6 5 10 4
Descricao_Produto Banana Goiaba Limão Maçã Mamão Maracujá Melancia Pera
Fonte: Elaboração do autor (2015).
134
Princípios de Bancos de Dados 2. Listar os produtos com código inferior a 5 e ordenados crescentemente por preço: Select * from Produtos where Codigo_Produto > 5 Order By Preco_Produto;
Resultado: Codigo_Produto
Descricao_Produto
Preco_Produto
1
Banana
1.00
4
Pera
1.00
5
Maracujá
2.00
2
Maçã
2.80
3
Uva
3.25
3. Listar os produtos ordenados por preço e descrição: Select * from Produtos Order By Preco_Produto,Descricao_Produto;
Resultado: Codigo_Produto
Descricao_Produto
Preco_Produto
9
Limão
0.85
1
Banana
1.00
4
Pera
1.00
7
Goiaba
2.00
5
Maracujá
2.00
6
Mamão
2.55
10
Melancia
2.55
2
Maçã
2.80
8
Laranja
3.25
3
Uva
3.25
Observe que nas linhas em que os preços são os mesmos, a ordenação foi feita também pela descrição do produto em ordem crescente. b. A cláusula Group By A cláusula Group By condensa em uma única linha todas as linhas selecionadas que compartilham os mesmos valores para as colunas agrupadas. Um item do Group By pode ser o nome de uma coluna ou o número da coluna na estrutura do comando select.
135
Capítulo 7 A sintaxe da cláusula é: Group By (coluna1,coluna2,...,colunaN);
Para exemplificar esta cláusula Group By, vamos utilizar o seguinte modelo de banco de dados: Figura 7.3 – Modelo de banco de dados - cláusula Group By PRODUTOR
Codigo_Produtor (PK) Nome_Produtor
REGIAO
Codigo_Regiao (PK) Nome_Regiao
PRODUTOS
Codigo_Produto (PK) Regiao_ID (FK) Preco_Produto Descricao_Produto Fonte: Elaboração do autor (2015).
Listando os dados de cada tabela, tem-se: 1. Select * from Região:
Resultado: Quadro 7.10 – Tabela Produtos
Código_Regiao 1
Nome_Regiao SUL
2
NORTE
3
CENTRO-OESTE
4
NORDESTE
Fonte: Elaboração do autor (2015).
2. Select * from Produtor:
Resultado: Quadro 7.11 – Tabela Produtos
Código_Regiao
Nome_Regiao
1
PRODUTOR 001
2
PRODUTOR 002
3
PRODUTOR 003
Fonte: Elaboração do autor (2015).
136
Princípios de Bancos de Dados
3. Select * from Produtos:
Resultado: Quadro 7.12 – Tabela Produtos
Codigo_Produto
Regiao_ID
Produto_ID
Preco_Produto
Descricao_Produto
11
1
1
1,44
PRODUTO A
12
2
2
1,55
PRODUTO B
13
1
1
2,99
PRODUTO C
14
3
3
1,45
PRODUTO D
15
1
1
1,99
PRODUTO E
16
4
2
1,59
PRODUTO F
17
2
1
1,55
PRODUTO G
Fonte: Elaboração do autor (2015).
Lembre-se que a tabela de Produtos faz referência à tabela de Produtor e à tabela de Região, por meio das colunas Produtor_ID e Região_ID, respectivamente. Dessa forma, pode-se afirmar que o produto de Código 11, cuja descrição é PRODUTO A, é produzido pelo Produtor 001, na região SUL, ao preço de 1,44. Sendo assim, a tabela de PRODUTOS possui os seguintes dados se vista pelas chaves estrangeiras: Quadro 7.13 – Tabela Produtos. Codigo_Produto
Nome_Regiao
Nome_Produtor
Preco_Produto
Descricao_Produto
11
SUL
PRODUTOR 001
1,44
PRODUTO A
13
SUL
PRODUTOR 001
2,99
PRODUTO C
15
SUL
PRODUTOR 001
1,99
PRODUTO E
17
NORTE
PRODUTOR 001
1,55
PRODUTO G
12
NORTE
PRODUTOR 002
1,55
PRODUTO B
16
NORDESTE
PRODUTOR 002
1,59
PRODUTO F
14
CENTRO-OESTE
PRODUTOR 003
1,45
PRODUTO D
Fonte: Elaboração do autor (2015).
Com base nessa tabela de Produtos, serão executados alguns comandos utilizando a cláusula Group By, veja:
137
Capítulo 7 1. Listar todos os produtores por região: Select Produtor_ID,Regiao_ID from Produtos Group By Regiao_ID
Resultado: Quadro 7.14 – Tabela Produtos
Produtor_id
Região_id
1
1
1
2
2
2
3
3
2
4
Fonte: Elaboração do autor (2015).
2.Listar produtos por produtor: Select Descricao_Produto,Produtor_ID from Produtos Group By Produtor_ID;
Resultado: Quadro 7.15 – Tabela Produtos
descricao_produto
Produtor_ID
PRODUTO G
1
PRODUTO E
1
PRODUTO C
1
PRODUTO A
1
PRODUTO F
2
PRODUTO B
2
PRODUTO D
3
Fonte: Elaboração do autor (2015).
Note que ao fazer o agrupamento, os itens do agrupamento que possuem o mesmo valor não são reapresentados.
138
Princípios de Bancos de Dados
Seção 3 Funções de agregação Uma função de agregação retorna um único resultado para várias linhas de entrada de uma tabela. Por exemplo, existem funções de agregação para contar, somar, calcular a média, calcular o valor máximo e o valor mínimo para um conjunto de linhas. Uma função de agregação recebe os dados de várias linhas de uma tabela, porém, um único valor de retorno é apresentado. As principais funções de agregação são: Quadro 7.16 – Principais funções de agregação
Função
O que faz Count
Retorna o número de ocorrências da coluna da tabela.
Sum
Retorna a soma dos valores da coluna da tabela.
Avg
Retorna a média de valores da coluna da tabela.
Max
Retorna o maior valor da coluna da tabela.
Min
Retorna o menor valor da coluna da tabela.
Distinct
Não permite que valores iguais de uma coluna sejam apresentados na consulta.
Fonte: Elaboração do autor (2015).
A função de agregação sempre está associada a uma coluna da tabela, dessa forma, a sintaxe da função é: Função_de_Agregação (Coluna)
Veja alguns exemplo de uso das funções de Agregação
139
Capítulo 7
1. Selecionar o maior preço, o menor preço, a média de preços, o total de preços e a quantidade de produtos cadastrados: select max(preco_produto) as maior, min(preco_produto) as menor, avg(preco_produto) as media, count(codigo_produto)as Qtde, sum(preco_produto) as somatório from produtos; Resultado: Maior 2,99
Menor 1,44
Media 1,7943
Qtde 7
Somatório 12,56
Note que foi acrescentado ao lado de cada função um nome para apresentação da coluna. Por exemplo, a função max(preco_produto) será mostrada como sendo a coluna maior. Foi utilizado este recurso para deixar mais claro o que representa cada resultado da função executada. Veja outros exemplos! 1. Listar a quantidade de produtos com preço de 2,99: Select count(codigo_produto) from produtos where preco_produto = 2,99; 2. Listar a soma dos preços de produtos com código maior que 6: Select sum(preco_produto) from produtos where codigo_produto > 13; 3. Listar todos os preços de produtos sem que os preços iguais sejam repetidos: Select distinct(preco_produto) from produtos; Veja o resultado desse comando acima: Preco_produto 1,44 1,45 1,55 1,59 1,99 2,99
140
Princípios de Bancos de Dados
Seção 4 Condições especiais Para restringir as linhas que serão processadas pelo comando select, usa-se a cláusula Where. Entretanto, há casos em que não poderá ser dessa forma. Como apresentado no exemplo anterior, para selecionar o maior preço da tabela de Produtos foi usado o comando: Select max(preco_produto) From produtos;
Mas se você desejasse apresentar o nome do produto com o maior preço? Talvez, de início você imaginaria algo como: Select descricao_produto From produtos Where preco_produto = max(preco_produto);
Porém, isso não pode ser feito porque a função de agregação Max não pode ser usada na cláusula Where. A cláusula Where determina as linhas que vão passar para o estágio de agregação e, portanto, precisa ser avaliada antes das funções de agregação serem computadas. A consulta pode ser reformulada para obter o resultado pretendido, o que será feito por meio de uma subconsulta: Select descricao_produto From produtos Where preco_produto = (Select max(preco_produto ) from produtos);
Nesse caso, primeiro o banco processa o select mais interno, (Select max(preco_ produto) from produtos), substitui o resultado, e então processa o select mais externo como se fosse um select normal, substituindo a subconsulta pelo valor retornado por ela. Veja a representação abaixo:
141
Capítulo 7 Figura 7.4 – Subconsulta.
PRODUTO C
Select descrição_produto from Produtos Where preço_produto =
Select max (preço_produto) from Produtos
Preço_produto
Descrição_Produto
1,44
PRODUTO A
1,55
PRODUTO B
2,99
PRODUTO C
1,45
PRODUTO D
1,99
PRODUTO E
1,59
PRODUTO F
1,55
PRODUTO G
Fonte: Elaboração do autor (2015).
Isso está correto porque a subconsulta é uma ação independente, que calcula sua agregação isoladamente do que está acontecendo na consulta externa. As agregações também são muito úteis quando combinadas com a cláusula Group By. Select Regiao_ID, max(Preco_Produto) From Produtos Group By Regiao_ID;
Para produzir uma linha de saída para cada região, cada resultado da agregação é calculado sobre as linhas da tabela correspondendo a uma região. Essas linhas agrupadas podem ser filtradas utilizando a cláusula Having: Select Regiao_ID, max(Preco_Produto) From Produtos Group By Regiao_ID; Having max(Preco_Produto) > 100;
Mostrando o resultado apenas para as regiões cujo preço máximo alcançado foi 100 Reais.
142
Princípios de Bancos de Dados Para concluir, se desejarmos somente produtos cuja descrição comece pela letra “S”, podemos escrever: Select Descricao_Produto From Produtos Where Descricao_Produto LIKE ‘S%’
É importante que você compreenda a interação entre as agregações e as cláusulas Where e Having do SQL. A diferença fundamental entre essas duas cláusulas é: ••
WHERE: seleciona as linhas de entrada antes dos grupos e agregações serem computados, portanto, controla quais linhas irão para o computo da agregação;
••
HAVING: seleciona grupos de linhas após os grupos e agregações serem computados.
Essa restrição entre as cláusulas Where e Having se dá em função de como os SGBDs processam o comando Select. Por mais estranho que possa parecer, o comando Select começa a ser executado pela cláusula From, não pelo próprio Select. Vejamos a ordem de execução das cláusulas do comando Select: Quadro 7.17 – Ordem de execução das cláusulas do Comando Select
ORDEM
CLÁUSULA
6
Select
1
From
2
Where
3
Group by
4
Having
5
Order By
Assim sendo, perceba que ao ser executado o Where, o banco de dados ainda não tem conhecimento de como serão constituídos os grupos de dados, portanto, não podem processar as funções de agregação. Conforme o exposto, a projeção das colunas selecionadas é sempre a última operação na execução do comando select, isso explica, por exemplo, porque podemos ordenar as informações a serem apresentadas por uma informação que não está sendo selecionada.
143
Capítulo 7 Para concluir, se desejarmos somente produtos cuja descrição comece pela letra “S”, podemos utilizar a função LIKE, por exemplo: Select Descricao_Produto From Produtos Where Descricao_Produto LIKE ‘S%’;
144
Capítulo 8 Recuperação de Informações através de várias tabelas
Habilidades
•• Construir sentenças SQL utilizando-se de mais de uma tabela •• Utilizar o recurso de junção interna e junção externa na construção de sentenças SQL
Seções de estudo
Seção 1: O que é uma junção. Seção 2: Junções de produto cartesiano. Seção 3: Junções internas. Seção 4: Junções externas.
145
Capítulo 8
Para início de estudo O comando de manipulação de dados chamado Select normalmente é um dos mais executados por qualquer aplicação de computador que envolva um SGBD. A estrutura de um comando de seleção é bem simples. Definem-se as colunas que serão apresentadas, quais as tabelas relacionadas que fazem parte da seleção e qual a condição de filtro para a definição das linhas que farão parte do conjunto de dados recuperado. Isto foi apresentado no capítulo 7 desta Unidade de Aprendizagem. Porém, na maioria das vezes, a seleção de dados que será executada envolve o uso de mais de uma tabela no mesmo comando select, isto ocorre devido ao fato de que os dados estão distribuídos em várias tabelas relacionais ligadas pelas chaves estrangeiras, os chamados relacionamentos. Assim, durante a construção de consultas em SQL, é muito comum surgir a necessidade de extrair informações de diferentes tabelas para se gerar o resultado esperado. Neste capítulo você terá uma integração maior com o comando Select, criando consultas que envolvam mais de uma tabela.
Seção 1 O que é uma junção Nos modelos de banco de dados, principalmente naqueles que envolvem um número elevado de tabelas, é muito comum na elaboração das consultas a necessidade da seleção de dados em diferentes tabelas. Essa operação necessita que o especialista em banco de dados defina os critérios para o agrupamento dessas tabelas. Os critérios para o agrupamento são chamados de condições de junção, ou apenas junção. A junção de tabelas ocasiona uma tabela derivada de outras duas tabelas de acordo com as regras de junção elaborada. As regras de junção são muito parecidas com as regras da teoria dos conjuntos da matemática, como união, intersecção e diferença. Veja a figura abaixo:
146
Princípios de Bancos de Dados Figura 8.1 - Representação da junção entre tabelas
Fonte: Elaboração do autor (2015).
Na Figura 8.1 tem-se duas tabelas com dados quaisquer. A junção executada resulta em uma terceira tabela que agrupa dados em comum entre as tabelas A e B, o que se pode chamar de intersecção entre as tabelas. Porém uma nova junção entre essas tabelas poderia ser realizada, de forma a retornar uma terceira tabela com todos os dados das duas tabelas, fazendo a união dos dados, conforme a figura a seguir: Figura 8.2 - Junção com a união dos dados das duas tabelas
Fonte: Elaboração do autor (2015).
147
Capítulo 8 Veja na figura 8.3 um terceiro caso, em que a junção retorna os dados que estão na Tabela A, mas não estão na Tabela B, correspondendo à operação de conjunto A-B: Figura 8.3 - Junção que retorna os dados que estão na tabela a, mas não estão na tabela b
Fonte: Elaboração do autor (2015).
As formas de junção são definidas pela cláusula Where do comando Select como qualquer outra condição da consulta, e possuem influência direta sobre a escolha do método de junção que será realizado pelo especialista em banco de dados. Essas condições de consulta são utilizadas para realizar a união entre os dados contidos nas diferentes tabelas, combinando os registros ou formando um único registro que contém os campos definidos na cláusula Select. Conforme as condições de junção é possível obter os seguintes tipos de junção:
148
••
produto cartesiano – retorna toda a combinação de registros das duas tabelas de dados, uma vez que não existe nenhuma restrição definida na cláusula where;
••
junção interna (inner-join) – a consulta retornará apenas os registros que atendem às condições definidas nas tabelas de dados;
••
junçõão externa (outer join – semelhante à junção simples, porém retornará também os registros da fonte de dados externa que não estão presentes na tabela interna.
Princípios de Bancos de Dados Na definição da junção, o especialista em banco de dados deve tomar alguns cuidados, como: ••
identificar as chaves primárias das tabelas;
••
identificar o relacionamento entre as tabelas, normalmente através das chaves estrangeiras;
••
visualizar toda estrutura do modelo de dados que está sendo utilizado, com nome dos campos e tipos de cada campo;
••
ater-se aos dados que serão apresentados pelo comando de seleção.
De forma resumida, independentemente do tipo de junção, a criação da junção deve levar em consideração os dados que serão apresentados pela seleção, qual o relacionamento existente entre as tabelas que fazem parte da pesquisa a ser elaborada, quais os campos únicos e quais os nomes e tipos de cada campo. Na próxima seção você estudará a forma de representar cada um dos tipos de junções vistos anteriormente utilizando o comando Select.
Seção 2 Junções de produto cartesiano Esse método de junção se baseia nas regras de relacionamento entre as tabelas que fazem parte da seleção, sendo que as chaves estrangeiras são referenciadas diretamente na formulação do comando Select. Esse método de junção deve ser utilizado quando as tabelas possuem poucos registros e existe uma relação direta entre as tabelas. Lembre-se que a relação direta entre tabelas é realizada pelas chaves estrangeiras. Essa junção faz uma pesquisa em todas as tabelas que fazem parte do comando Select, de forma que o próprio SGBD define uma tabela principal, que servirá de base para busca dos registros nas tabelas dependentes. Para cada registro existente na tabela principal, o SGBD buscará os registros relacionados na tabela dependente, desde que atendam às condições da cláusula Where. Antes de apresentar exemplos de comandos Select que implementam a junção com produto cartesiano, você verá o modelo de dados que usaremos como exemplo, bem como:
149
Capítulo 8 ••
os comandos de definição de dados que criam as tabelas do modelo;
••
os comandos de inserção de dados de cada tabela do modelo;
••
a representação gráfica de cada tabela com os dados já cadastrados.
Veja todas as características do modelo de dados que será utilizado, bem como relembre de alguns comandos que já foram vistos nas unidades anteriores. A seguir está a representação gráfica do modelo que usaremos como exemplo: Figura 8.4 - Modelo de dados para o exemplo de junção por produto cartesiano Cidade
Codigo_cidade (PK)
2
Sub Regiao
Codigo_subRegiao (PK)
Nome_cidade
Nome_SubRegiao
Codigo_SubRegiao (FK)
Codigo_Pais (FK) 1 Pais
Codigo_Pais (PK) Nome_Pais Fonte: Elaboração do autor (2015).
Nesse modelo de dados, pode-se notar que a tabela País é a única tabela que não depende de dados das outras tabelas. Por outro lado, a tabela de SubRegião possui um campo que é a chave estrangeira para a tabela País, representada pelo relacionamento número 1 da figura acima. Dessa forma, tem-se uma regra de chave estrangeira na tabela Sub-Região da seguinte forma: ••
a coluna chamada codigo_pais da tabela Sub-Região referencia a tabela País pelo campo codigo_pais.
Já a tabela Cidade possui a sua coluna codigo_subregiao como chave estrangeira para tabela Sub-Região representada pelo relacionamento número 2 da figura acima, da seguinte forma: ••
150
a coluna codigo_regiao da tabela Cidade referencia a tabela SubRegião pelo campo codigo_subregiao.
Princípios de Bancos de Dados Para deixar mais claro o modelo de dados acima, representado na Figura 8.4, está apresentado logo a seguir os comandos de definição de dados que criaram esse modelo, veja na figura 8.5: Figura 8.5 - Comandos de definição de dados
Create Table Pais ( Codigo_Pais integer, Nome_Pais Varchar(30), Primary Key (Codigo_Pais) ); Create Table SubRegiao ( Codigo_Subregiao integer, Nome_Subregiao Varchar(30), Codigo_Pais integer, Primary Key (Codigo_Subregiao), Foreign Key(Codigo_Pais) references Pais(Codigo_Pais) ); Create Table Cidade ( Codigo_Cidade integer, Nome_Cidade Varchar(30), Codigo_SubRegiao integer, Primary Key (Codigo_Cidade), Foreign Key(Codigo_SubRegiao) references SubRegiao(Codigo_SubRegiao) ); Fonte: Elaboração do autor (2015).
A partir de agora vamos estudar os comandos em SQL para preenchimento das tabelas. Começaremos pela tabela País. Em SQL, por meio do comando Insert, o preenchimento da tabela de países foi feito assim:
151
Capítulo 8 Figura 8.6 - Comandos de insert na tabela país
Cadastro na Tabela País Insert Into Pais Values (1,’Brasil’) Insert Into Pais Values (2,’Argentina’) Insert Into Pais Values (3, ‘Estados Unidos’) Fonte: Elaboração do autor (2015).
Graficamente a tabela ficou desta forma: Figura 8.7 - Tabela país com os dados cadastrados
Codigo_Pais
Nome_Pais
1
Brasil
2
Argentina
3
Estados Unidos
Fonte: Elaboração do autor (2015).
Agora, os comandos de inserção para tabela Sub-Região: Figura 8.8 – comandos insert na tabela sub-regiao
Cadastro na Tabela Sub-Região Insert Into Subregiao Values (1, ‘Parana’, 1) Insert Into Subregiao Values (2, ‘Sao Paulo’, 1) Insert Into Subregiao Values (3, ‘Santa Catarina’, 1) Insert Into Subregiao Values (4, ‘Buenos Aires’, 2) Insert Into Subregiao Values (5, ‘Cordoba’, 2) Insert Into Subregiao Values (6,’California’, 3) Insert Into Subregiao Values (7,’Rio Grande do Sul’, 1) Fonte: Elaboração do autor (2015).
152
Princípios de Bancos de Dados Graficamente a tabela ficou desta forma: Figura 8.9 - Tabela de sub-região com os dados cadastrados
Codigo_Subregiao
Nome_Subregiao
Codigo_Pais
1
Parana
1
2
Sao Paulo
1
3
Santa Catarina
1
4
Buenos Aires
2
5
Cordoba
2
6
California
3
7
Rio Grande do Sul
1
Fonte: Elaboração do autor (2015).
Para finalizar, os comandos de inserção na tabela Cidade: Figura 8.10- comandos de insert na tabela cidade
Cadastro na Tabela Cidade Insert Into Cidade Values (1, ‘Sao Paulo’ ,2) Insert Into Cidade Values (2, ‘Florianopolis’, 3) Insert Into Cidade Values (3, ‘Buenos Aires’, 4) Insert Into Cidade Values (4, ‘La Plata’, 4) Insert Into Cidade Values (5, ‘Los Angeles’, 6) Insert Into Cidade Values (6,’San Francisco’, 6) Fonte: Elaboração do autor (2015).
153
Capítulo 8 Graficamente a tabela ficou desta forma: Figura 8.11 – tabela cidade com os dados cadastrados
Codigo_Cidade
Nome_cidade
Codigo_Subregiao
1
Sao Paulo
2
2
Florianopolis
3
3
Buenos Aires
4
4
La Plata
4
5
Los Angeles
6
6
San Francisco
6
Fonte: Elaboração do autor (2015).
Bem, agora que está tudo preparado, veja alguns comandos em SQL que fazem a seleção de dados pela junção por produto cartesiano. Selecionando todos os dados da tabela Cidade: Select * from Cidade;
O resultado do Select será: Figura 8.12 - resultado do comando select * from cidade
Codigo_Cidade
Codigo_Subregiao
1
Sao Paulo
2
2
Florianopolis
3
3
Buenos Aires
4
4
La Plata
4
5
Los Angeles
6
6
San Francisco
6
Fonte: Elaboração do autor (2015).
154
Nome_cidade
Princípios de Bancos de Dados Até aqui não há nada de novo, contudo essa tabela fica um pouco estranha ao se perceber que as cidades pertencem a sub-regiões como: 2, 3 ,4 e 6. Esses valores correspondem aos códigos das sub-regiões cadastradas na tabela SubRegião, conforme a figura a seguir: Figura 8.13 - Relacionamento entre as tabelas cidade e sub-região
Codigo_ Cidade
Nome_cidade
Codigo_ SubRegiao
1
Sao Paulo
2
2
Florianopolis
3
3
Buenos Aires
4
4
La Plata
4
5
Los Angeles
6
6
San Francisco
6 Codigo_ Cidade
Nome_SubRegiao Codigo_ Pais
1
Parana
1
2
Sao Paulo
1
3
Santa Catarina
1
4
Buenos Aires
2
5
Cordoba
2
6
California
3
Fonte: Elaboração do autor (2015).
Sendo assim, o comando de seleção de todos os registros da tabela de cidades pode ser escrito da seguinte forma: Select Cidade.Codigo_Cidade,Cidade.Nome_Cidade,SubRegiao.Nome_SubRegiao From Cidade,SubRegiao Where Cidade.Codigo_SubRegiao = SubRegiao.Codigo_SubRegiao;
Nesse caso, há uma junção entre as tabelas de cidades e sub-região, por meio da chave estrangeira codigo_subregiao da tabela Cidade que a relaciona com a tabela Sub-Região.
155
Capítulo 8 O resultado do comando acima será: Figura 8.14 - resultado da junção entre as tabelas cidade e sub-região
Codigo_Cidade
Nome_cidade
Nome_Subregiao
1
Sao Paulo
Sao Paulo
2
Florianopolis
Santa Catarina
3
Buenos Aires
Buenos Aires
4
La Plata
Buenos Aires
5
Los Angeles
Califórnia
6
San Francisco
Califórnia
Fonte: Elaboração do autor (2015).
Veja mais alguns comandos de seleção. Listar a sub-região da cidade de Florianópolis: Select SubRegiao.Nome_SubRegiao From Cidade,SubRegiao Where Upper(Cidade.Nome_Cidade) = ‘FLORIANÓPOLIS’ AND Cidade.Codigo_SubRegiao = SubRegiao.Codigo_SubRegiao;
O resultado do comando será: Figura 8.15 – Resultado do comando Select
Fonte: Elaboração do autor (2015).
Listar o nome das sub-regiões que pertencem ao país chamado Brasil: Select SubRegiao.Nome_SubRegiao From SubRegiao,Pais Where Upper(Pais.Nome_Pais) = ‘BRASIL’ AND Pais.Codigo_Pais = SubRegiao.Codigo_Pais;
156
Princípios de Bancos de Dados O resultado do comando será: Figura 8.16 – Resultado do comando Select
Fonte: Elaboração do autor (2015).
Listar o nome de todas as sub-regiões e seus respectivos países: Select SubRegiao.Nome_SubRegiao From SubRegiao,Pais Where Pais.Codigo_Pais = SubRegiao.Codigo_Pais;
O resultado do comando será: Figura 8.17 – Resultado do comando Select
Fonte: Elaboração do autor (2015).
Listar o nome da cidade e o nome da sua respectiva sub-região: Select Cidade.Nome_Cidade,SubRegiao.Nome_SubRegiao,Pais.Nome_Pais From Cidade,SubRegiao,Pais Where Cidade.Codigo_SubRegiao = SubRegiao.Codigo_SubRegiao AND SubRegiao.Codigo_Pais = Pais.Codigo_Pais;
157
Capítulo 8 O resultado do comando será: Figura 8.18 – Resultado do comando Select.
Fonte: Elaboração do autor (2015).
Note que a condição Where sempre especifica as chaves estrangeiras que interligam as tabelas da seleção. Isso é uma característica da junção do tipo produto cartesiano. Na próxima seção você verá como criar junções sem usar a chave estrangeira diretamente.
Seção 3 Junções internas A utilização da cláusula Inner é o que caracteriza o comando para uma junção interna, porém ele não é obrigatório. Pode parecer à primeira vista que as junções internas se equiparam com as junções de produto cartesiano vistas anteriormente. Mas nas junções internas é sempre obrigatória a especificação de condição de junção, ou seja, quais linhas de uma tabela têm alguma ligação com a linha de outra tabela. Para isso deve-se utilizar a cláusula On. A cláusula On se assemelha em muito com a cláusula Where, ou seja, um par com uma linha da Tabela A e uma linha da Tabela B são correlacionados se a expressão da cláusula On for verdadeira.
158
Princípios de Bancos de Dados Por exemplo, a seleção de todos os registros da tabela Cidade pode ser escrita da seguinte forma: SELECT * FROM Cidade INNER JOIN subregiao ON Cidade.Codigo_subregiao = Subregiao.Codigo_Subregiao;
Note que a cláusula On está sendo utilizada de forma semelhante à cláusula Where das junções de produto cartesiano. Porém o resultado do Select será diferente. Veja a saída realizada pelo comando anterior: Figura 8.19 - resultado de uma junção interna entre as tabelas de cidade e sub-região
Codigo_ Cidade
Nome_ Cidade
Codigo_ SubRegiao
Codigo_ Subregiao_1
Nome_ subRegiao
Codigo_ Pais
1
São Paulo
2
2
São Paulo
1
2
florianopolis
3
3
Santa Catarina
1
3
Buenos Aires
4
4
Buenos Aires
2
4
La Plata
4
4
Buenos Aires
2
5
Los Angeles
6
6
California
3
6
San Francisco
6
6
California
3
Fonte: Elaboração do autor (2015).
Como ambas as tabelas possuem o campo Codigo_SubRegiao, uma coluna se manteve como Codigo_SubRegiao e outra foi renomeada para Codigo_ SubRegiao_1, que representa exatamente o relacionamento entre as tabelas e a condição da cláusula On. Veja um exemplo de Select com junção interna para selecionar o nome de todas as sub-regiões e seus respectivos países: Select SubRegiao.Nome_SubRegiao,Pais.Nome_Pais From SubRegiao INNER JOIN Pais ON SubRegiao.Codigo_Pais = Pais.Codigo_Pais;
159
Capítulo 8 O resultado desse comando será: Figura 8.20 – Resultado de uma junção interna entre as tabelas de SubRegiao e Pais.
Nome_SubRegiao
Nome_Pais
Parana
Brasil
São Paulo
Brasil
Santa Catarina
Brasil
Rio Grande do Sul
Brasil
Buenos Aires
Argentina
California
Argentina
California
Estados Unidos
Fonte: Elaboração do autor (2015).
Bem, o que era importante a respeito do assunto junção interna foi apresentado nesta seção. Na próxima seção você terá contato com as junções externas.
Seção 4 Junções externas Para representar uma junção externa utiliza-se a cláusula Outer, no entanto, ela não é obrigatória. O que caracteriza realmente as junções externas são as cláusulas Left e Right. Junção externa left outer join Esse tipo de junção externa funciona da seguinte forma: ••
executa uma junção interna;
••
verifica quais linhas da primeira tabela não possuem relacionamento com a segunda tabela da seleção;
••
para cada linha da segunda tabela que não possui relacionamento é inserido um campo com valor null.
Veja o exemplo a seguir: SELECT * FROM SubRegiao LEFT OUTER JOIN Cidade ON Cidade.Codigo_SubRegiao = SubRegiao.Codigo_SubRegiao;
160
Princípios de Bancos de Dados O resultado do Select acima será: Figura 8.21 - Resultado de um left outer joiner entre as tabelas sub-região e cidade
Codigo_ SubRegiao
Nome_ SubRegiao
Codigo_ Pais
Codigo_ Cidade
Nome_Cidade
Codigo_ SubRegiao
1
Paraná
1
2
São Paulo
1
1
São Paulo
2
3
Santa Catarina
1
2
Florianópolis
3
4
Buenos Aires
2
3
Buenos Aires
4
4
Buenos Aires
2
4
La Plata
4
5
California
2
6
California
3
5
Los Angeles
6
6
California
3
6
San Francisco
6
7
Rio Grande do Sul
1
Fonte: Elaboração do autor (2015).
Perceba nas linhas destacadas acima em que as sub-regiões que não possuem cidade relacionada às colunas codigo_cidade, nome_cidade e codigo_ subregiao_2 ficaram sem valores, ou seja, foram preenchidas com null. Em uma seleção normal essas linhas seriam ignoradas. Com o uso de left outer joiner todas as linhas das tabelas da esquerda que não possuem correspondentes na tabela da direita são apresentadas no resultado da consulta. Junção externa right outer join Esse tipo de junção externa funciona da seguinte forma: ••
executa uma junção interna;
••
verifica quais linhas da segunda tabela não possuem relacionamento com a primeira tabela da seleção;
••
para cada linha da primeira tabela que não possui relacionamento é inserido um campo com valor null.
161
Capítulo 8 Ou seja, a junção externa right outer join é o inverso da junção left outer join. Veja o próximo exemplo, supondo que não há nenhuma sub-região cadastrada para o país Canadá: SELECT * FROM SubRegiao RIGHT OUTER JOIN Pais ON Pais.Codigo_Pais = SubRegiao.Codigo_Pais;
O resultado do comando Select será: Figura 8.22 - Resultado de um right outer joiner entre as tabelas sub-região e país
Codigo_ SubRegiao
Nome_SubRegiao
Codigo_Pais
Codigo_Pais_1
Nome_Pais
1
Paraná
1
1
Brasil
2
São Paulo
1
1
Brasil
3
Santa Catarina
1
1
Brasil
7
Rio Grande do Sul
1
1
Brasil
4
Buenos Aires
2
2
Argentina
5
California
2
2
Argentina
6
California
3
3
Estados Unidos
4
Canada
Fonte: Elaboração do autor (2015).
Basicamente, a diferença entre right e left está na escolha da tabela em que os elementos que não possuem relacionamento serão escolhidos para serem acrescidos com null.
162
Considerações Finais Chegamos ao final desta Unidade de Aprendizagem, o que representa mais um passo concluído no conhecimento de como funciona um ambiente informatizado. Quanto mais você avança neste curso, deve perceber que a tecnologia está mais e mais integrada ao nosso dia-a-dia, oferecendo soluções para facilitar nossas atividades diárias. Aqui você pôde ter um contato direto com mais uma tecnologia que atualmente possui uma importante participação nestas soluções. A unidade de aprendizagem Princípios de Banco de Dados colocou você em contato direto com a utilização de um ambiente de banco de dados, proporcionando o entendimento de como as informações são armazenadas e recuperadas neste ambiente, bem como de realizar corretamente um projeto de banco de dados, utilizando-se de mecanismos existentes para garantir que as informações sejam armazenadas corretamente. Assim como transformamos várias interrogações em exclamações, acreditamos ter despertado muitas outras interrogações em suas mentes. Isto também é compromisso desta unidade de aprendizagem, fomentar a busca por novos conhecimentos, permitindo que você acrescente continuamente informações necessárias ao exercício da sua atividade profissional. Esteja certo de que após a conclusão de mais esta etapa você está mais próximo de ser um ótimo profissional da área de tecnologia da informação. Um grande abraço e sucesso a todos!
163
164
Referências ABREU, Mauricio, MACHADO, Felipe Nery Rodrigues. Projeto de Banco de Dados. Editora Érica , 2009. CHU, S. Y. Banco de dados: organização, sistemas e administração. São Paulo: Atlas, 1983. CHEN, P. Modelagem de dados: a abordagem entidade-relacionamento para projeto lógico. São Paulo: Makron Books, 1990. COSTA, Rogério Luís de Carvalho. SQL: Guia prático. São Paulo. Brasport, 2004. DATE, C. J. Banco de dados: fundamentos. Rio de Janeiro: Campus, 1985. DATE, C. J. Introdução a Sistemas de Bancos de Dados. Rio de Janeiro: Campus, 1991. FONSECA, Luiz Claudio. Trabalhando com 10g Oracle database. Ciência Moderna, 2005. GUIMARÃES, Célio Cardoso. Fundamentos de bancos de dados. Editora Unicamp, 2008. HERNANDEZ, M. J. Aprenda a projetar seu próprio banco de dados. São Paulo: Pearson Education do Brasil Ltda, 1999. HEUSER, CARLOS ALBERTO. Projeto de Banco de Dados. São Paulo: Bookman, 2009. JESUS, J. B. de. Ansi SQL 89 92. Axcel Books do Brasil Ltda, 2004. KORTH, H.F.; Silberschatz, A. Sistema de Banco de Dados. 3. ed. São Paulo: Makron Books, 1999. COUGO, P. Modelagem conceitual e projeto de banco de dados. 7. Reimp. Rio de Janeiro: Campus, 2001. LIMA, A. da S. ERwin 4.0: modelagem de dados. 2. ed. São Paulo: Érica, 2002. MECENAS, Ivan. OLIVEIRA, Viviane de. Banco de dados: do modelo conceitual à implementação física. Alta Books, 2005.
165
Universidade do Sul de Santa Catarina MEDEIROS, M. Oracle 8i: conceitos básicos. Florianópolis: Advanced, 2000. NASSU, E. A. Banco de dados orientado a objetos. Edgard Blucher, 1999. OLIVEIRA, A. R de. Modelagem de dados. São Paulo: Senac, 2004. OPEEL, Andy. Banco de dados desmistificado. Rio de Janeiro. Alta Books, 2005. PRESTES, R. Mysql guia de consulta rápida. Novatec, 2000. RANGEL, Alexandre. Mysql - Projeto, modelagem e desenvolvimento. Rio de Janeiro. Alta Books, 2005. SETZER, V. W.; SILVA, F. S. da. Banco de dados. São Paulo: Edgard Blucher, 2005. SOARES, S. P. de M. Dominando o ERwin: modelagem de dados para banco oracle. São Paulo: Ciência Moderna, 2004. SOARES, W. MySQL conceitos e aplicações. Erica, 2003.
166
Sobre os Professores Conteudistas Luciano José Sávio Possui graduação em Ciência da Computacão pela Universidade do Sul de Santa Catarina (1995), mestrado em Engenharia de Produção pela Universidade Federal de Santa Catarina (2000) e cursa doutorado em Engenharia de Produção pela Universidade Federal de Santa Catarina (2004). Atualmente, é professor titular da Universidade do Sul de Santa Catarina na área de banco de dados. Tem experiência na área de Ciência da Computação, com ênfase em Sistemas de Computação, atuando principalmente, nos seguintes temas: Agentes, Inteligência Artificial, Banco de Dados.
Marcelo Medeiros É bacharel em Ciências da Computação pela Universidade Regional de Blumenau e professor da Unisul desde 1995, na qual leciona as disciplinas de Banco de Dados, Programação e Estrutura de Dados para os cursos de Engenharia, Ciência da Computação e Sistemas de Informação. Atualmente participa do projeto de Incubadora da Unisul, desenvolvendo projetos de software na área de automação e segurança via IP. Atua como consultor junto a empresas no desenvolvimento de novos produtos de informática.
167
capa_cuvas.pdf
1
21/11/14
14:50
w w w. u n i s u l . b r