Baixe o app para aproveitar ainda mais
Prévia do material em texto
BANCO DE DADOS 1 Banco de dados Muitos autores definem Banco de Dados (BD) de forma diferente, porém em todas as definições tem-se uma idéia de coleção ou conjunto de dados armazenados que servem ou são usados por algumas situações específicas. A definição de banco de dados como ‘uma coleção de dados relacionados’ é muito generalista. Por exemplo, considere a coleção de palavras deste texto como sendo dados relacionados e, portanto, constitui um banco de dados. Entretanto, o uso comum do termo ‘banco de dados’ é usualmente mais restrito. O conceito de banco de dados esta muito presente em nosso dia-a-dia e faz parte de nossa vida. Bancos de dados desempenham um papel crítico em muitas áreas onde computadores são utilizados. Estão presentes em muitas áreas diferentes (negócios, engenharia, educação, medicina, etc.). Sendo assim, um arranjo aleatório de dados não pode ser considerado um banco de dados. Existem muitas definições para o termo banco de dados, entre elas podemos citar: “É um conjunto de dados inter-relacionados” (KORTH) “É uma coleção lógica e coerente de dados com algum significado inerente” (ELMARSI) “É um conjunto de dados estruturado de maneira adequada de forma que possa ser utilizado com eficiência por uma diversidade de aplicações dentro de uma organização” (Yong) “É uma coleção de dados persistentes utilizados pelos sistemas de aplicação de uma organização” (Date) Banco de dados é um conjunto de registros dispostos em estrutura regular que possibilita a reorganização dos mesmos e produção de informação. Um banco de dados normalmente agrupa registros utilizáveis para um mesmo fim. Um banco de dados é usualmente mantido e acessado por meio de um software conhecido como Sistema Gerenciador de Banco de Dados (SGBD). Normalmente um SGBD adota um modelo de dados, de forma pura, reduzida ou estendida. Muitas vezes o termo banco de dados é usado, de forma errônea, como sinônimo de SGBD. Modelagem de Dados é a atividade de especificação das estruturas de dados e regras de negócio necessárias para suportar uma área de negócios. Representa um conjunto de requerimentos de informações de negócio. É uma parte importante do desenho de um sistema de informação. O modelo de dados mais adotado hoje em dia é o modelo relacional, onde as estruturas têm a forma de tabelas, compostas por tuplas (linhas) e colunas. O modelo relacional é um modelo de dados, adequado a ser o modelo subjacente de um Sistema Gerenciador de Banco de Dados (SGBD), que se baseia no princípio em que todos os dados estão guardados em tabelas (ou, matematicamente falando, relações). Toda sua definição é teórica e baseada na lógica de predicados e na teoria dos conjuntos. O conceito foi criado por Edgar Frank Codd em 1970, sendo descrito no artigo "Relational Model of Data for Large Shared Data Banks". Na verdade, o modelo relacional foi o primeiro modelo de dados descrito teoricamente. Os bancos de dados já existentes passaram então a ser conhecidos como modelo hierárquico, modelo em rede ou Codasyl e modelo de listas invertidas. Uma base de dados hierárquica é um tipo de sistema de gerenciamento de banco de dados que conecta registros numa estrutura de dados em árvore através de ligações de tal modo que cada tipo de registro tenha apenas um possuidor. A base de dados se baseia em um Modelo de Entidades e Relacionamentos: cada registro é uma coleção de atributos (campos), cada um dos quais contendo somente uma informação; uma ligação é a associação entre dois registros. Por exemplo: em uma dada base de dados comercial, uma encomenda (i.e. registro) é possuída por um único cliente. As estruturas hierárquicas foram muito usadas nos primeiros sistemas de gestão de bases de dados mainframe. No entanto, devido às suas restrições, é freqüente que não possam ser usados para relacionar estruturas que existem no mundo real. As relações hierárquicas entre diferentes tipos de dados podem tornar muito fácil a resposta a algumas questões, mas muito difícil a resposta a outras. Se a relação um-para-muitos for violada (por exemplo, um paciente pode ter mais do que um médico) então a hierarquia transforma-se numa rede. 1.1 Histórico Evolutivo de Banco de Dados Até 1960: Sistema de Arquivos (Pascal, C, etc.) Final de 1960: Modelo Hierárquico. Exemplo: IMS (IBM) 1970 e início de 1980: Modelo de Redes. Exemplo: IDMS, DMS-II (Unisys) Meados de 1980: Modelo Relacional (Codd). Exemplo: DB-2, SQL-DS (IBM), Oracle, Ingres, etc. Final de 1980: Modelo Orientado a Objetos e Objeto-Relacional. Exemplo: Orion, Informix, Jasmine, Oracle, etc. 1.2 Sistema de Gerenciamento de Banco de Dados Um Sistema Gerenciador de Banco de Dados (SGBD) ou simplesmente sistema de banco de dados é constituído por um conjunto de dados associados a um conjunto de programas para acesso a esses dados. O conjunto de dados, comumente chamado de banco de dados, contém informações sobre uma empresa em particular. O principal objetivo de um SGBD é proporcionar um ambiente tanto conveniente quanto eficiente para a recuperação e armazenamento das informações do banco de dados. A Figura 1 abaixo ilustra este conceito. Figura 1: Configuração de um sistema de banco de dados simplificado Há muitos tipos diferentes de SGBD. Desde pequenos sistemas que funcionam em computadores pessoais a sistemas enormes que estão associados a mainframes. Um SGBD implica na criação e manutenção de bases de dados, elimina a necessidade de especificação de definição de dados, age como interface entre os programas de aplicação e os arquivos de dados físicos e separa as visões lógica e de concepção dos dados. Assim sendo, são basicamente três as componentes de um SGBD: Linguagem de definição de dados (especifica conteúdos, estrutura a base de dados e define os elementos de dados); Linguagem de manipulação de dados (para poder alterar os dados na base); Dicionário de dados (guarda definições de elementos de dados e respectivas características – descreve os dados, quem os acede, etc. SGBDs são projetados para gerir grandes volumes de informações. Devem possuir mecanismos para definição e manipulação de dados, além de prover compartilhamento e segurança dos mesmos. 1.2.1 Arquitetura de Três-Esquemas O objetivo da arquitetura de três-esquemas, apresenta na Figura 2 abaixo, é separar o usuário da aplicação do banco de dados físico. Figura 2: A arquitetura de três-esquemas Como podemos observar na Figura 2, os esquemas podem ser definidos em 3 níveis: Visão Externa: É aquela vista pelo usuário que opera os sistemas aplicativos, por meio de interfaces desenvolvidas pelo analista (programas), buscando o atendimento de suas necessidades. Visão (nível) Conceitual: É aquela vista pelo analista de desenvolvimento e pelo administrador das bases de dados. Existe a preocupação na definição de normas e procedimentos para manipulação dos dados, para garantir a sua segurança e confiabilidade, o desenvolvimento de sistemas e programas aplicativos e a definição no banco de dados de novos arquivos e campos. Visão (nível) Interna: É aquela vista pelo responsável pela manutenção e desenvolvimento do SGBD. Existe a preocupação com a forma de recuperação e manipulação dos dados dentro do Banco de Dados. 1.2.2 Ambiente de um SGBD Em um pequeno banco de dados de uso pessoal uma única pessoa vai definir, construir e manipular o BD. Em um grande banco de dados com muitos (ou milhares) de usuários e com restrições de acesso podem-se identificar alguns perfis de pessoas que interagem com o banco dedados: Administrador do Banco de Dados (DBA) • É o supervisor do banco de dados, responsável pela autorização de acesso ao banco, monitoramento e coordenação do uso • Esta envolvido com os aspectos físicos do banco de dados (estruturas de armazenamento, métodos de acesso, etc.) Projetista do Banco de Dados • São responsáveis pela identificação dos dados e a elaboração de estruturas apropriadas para armazená-los • Compreender os requisitos necessários aos grupos de usuários do banco de dados antes de sua implementação Analista de Sistemas • Determina os requisitos dos usuários e desenvolvem especificações que atendam estes requisitos Programador de Aplicações • Implementam as especificações na forma de programas elaborando toda a documentação Usuário (final) • Um banco de dados existe para a utilização do usuário final, onde normalmente o seu trabalho requer consultas e atualizações • A maioria dos usuários utilizam programas voltados ao desempenho profissional, utilizando-os em seu dia-a-dia A Figura 3 ilustra de forma simplificada os componentes típicos de um SGBD. O BD e o catálogo de SGBD são (normalmente) armazenados em disco. O acesso ao disco é controlado pelo Sistema Operacional (SO), que organiza as entradas e saídas. Figura 3: Módulos componentes de um SGBD e suas interações 1.3 Modelos de Dados São modelos utilizados para organização dos dados de um BD. Definem um conjunto de conceitos para a representação de dados, como por exemplos entidade, tabela, atributo, etc. Existem modelos para diferentes níveis de abstração de representação de dados: • modelos conceituais • modelos lógicos • modelos físicos 1.3.1 Modelo Entidade-Relacionamento O modelo de entidade e relacionamento é o mais utilizado atualmente, devido a sua simplicidade e eficiência. Baseia-se na percepção de mundo real, que consiste em uma coleção de objetos básicos, chamados entidades e relacionamentos entre esses objetos. 1.3.1.1 Modelo Conceitual O modelo de entidade-relacionamento (MER) é baseado na percepção do mundo real que consiste em um conjunto de objetos básicos chamados entidades e nos relacionamentos entre estes objetos. Ele foi desenvolvido para facilitar o projeto de banco de dados, permitindo a especificação de um esquema de “negócio”, onde tal esquema representa a estrutura lógica geral do banco de dados. Os elementos básicos que compõem este modelo são os seguintes: Entidade: O conceito fundamental da abordagem entidade-relacionamento (E-R) é o conceito de entidade. Conjunto de objetos da realidade modelada sobre os quais deseja-se colecionar dados no banco de dados. Pode ser concreta (pessoa,disco, etc.) ou abstrata (curso, conceito, circulação, etc.). Uma entidade representa um conjunto de objetos que se deseja guardar dados. Exemplo: Sistema bancário: as entidades podem ser: clientes, contas correntes, cheques, agências Cliente: representa o conjunto de clientes que se deseja manter dados no banco de dados Relacionamento: Conjunto de associações entre entidades. Um conjunto de relacionamentos é uma coleção de ocorrências das entidades relacionadas. A função que uma entidade exerce em um relacionamento é chamada de papel, normalmente implícito, mas muito esclarecedor. Também pode ter atributos descritivos (por exemplo: data, hora, etc.). A ocorrência de um relacionamento particular dentro de um conjunto de relacionamentos de um mesmo tipo é chamada de instância do relacionamento. Exemplo: Suponha o relacionamento “lotação” entre as entidades Departamento e Pessoa. Este exemplo expressa que o BD armazenará dados sobre: um conjunto de objetos classificados como pessoa entidade Pessoa um conjunto de objetos classificados como departamentos entidade Departamento um conjunto de associações entre cada pessoa e um departamento relacionamento lotação Atributo: Dado que é associado a cada ocorrência de uma entidade ou um relacionamento. Uma entidade é representada por um conjunto de atributos. Para cada atributo existe um conjunto de valores permitidos, chamado domínio daquele atributo. O atributo mapeia uma entidade em um domínio. Exemplo: Cliente: cada ocorrência de cliente terá associado exatamente os seus atributos (nome, CPF, telefone, endereço) Dependência de Existência: Considere os conjuntos de entidades Conta e Transação, com o relacionamento “lançamento” entre estes conjuntos, especificando que uma conta particular pode efetuar diversas transações (lançamentos). Caso uma entidade Conta seja eliminada todas as suas Transações também deverão ser. Porém se uma Transação for eliminada, nada acontecerá com o registro da Conta especificada. A entidade Conta é dominante e a Transação é subordinada. Exemplo: No controle de dados de contas bancárias são necessários o armazenamento de alguns dados. Supondo que estes dados sejam o número e o saldo da conta para a identificação da conta corrente e a data, valor e o tipo de operação para a operação a ser executada em uma conta. O relacionamento entre estas possíveis entidades é identificado por transação. Chave: É um ou mais atributos que permite identificar unicamente uma entidade no conjunto de entidades. Chave candidata é o atributo, ou a composição de mais de um atributo, que pode identificar unicamente uma entidade. Chave primária identifica a chave candidata escolhida pelo projetista de dados para identificar uma entidade 1.3.1.2 Diagrama Entidade-Relacionamento A estrutura lógica geral de um banco de dados pode ser representada graficamente por um Diagrama de Entidade-Relacionamento (DE-R) O DE-R é composto por: Retângulos: representam as entidades Losangos: representam os relacionamentos Círculo rotulado: representam os atributos Linhas: ligam os atributos aos conjuntos de entidades e os conjuntos de entidades aos relacionamentos Exemplo: Cardinalidade: É uma restrição de mapeamento que expressa o número de entidades as quais outra entidade pode ser associada via um conjunto de relacionamentos. Supondo as entidades A e B, a cardinalidade pode ser: Um para um (1:1): uma entidade de A está associada a uma única entidade de B, e uma entidade de B está associada a uma única entidade de A. Um para muitos (1:N): uma entidade de A está associada a qualquer quantidade da entidade de B, e uma entidade de B esta associada somente a uma única entidade de A. Muitos para um (N:1): uma entidade de A está associada a uma única entidade de B, e uma única entidade de B pode estar associada a qualquer quantidade de entidades de A. Muitos para muitos (N:M): uma entidade de A está associada a qualquer quantidade de entidades de B, e uma entidade de B esta associada a qualquer quantidade de entidades de A. Exemplo: As entidades são classificadas em duas categorias que apresentam características distintas, sendo tratadas de forma diferente no banco de dados projetado. Suponha as entidades PAI e FILHO que possuem um relacionamento progenitor da seguinte forma: 1.3.1.3 Notação Utilizada no DE-R Agregação: Uma limitação do modelo E-R é que não é possível expressar relacionamentos entre relacionamentos. Considere um BD descrevendo informações sobre funcionários que trabalham em um determinado projeto e utilizam uma série de diferentes máquinas em seus trabalhos. A agregação, ou entidade associativa, é uma abstração por meio da qual os relacionamentossão tratados como entidades de nível superior. No exemplo, o relacionamento trabalho e as entidades funcionário e projeto são tratados como um conjunto de entidades de nível superior – trabalho. 1.3.1.4 Exercícios Construir o digrama E-R para cada um dos casos a seguir: 1. Para uma seguradora de automóveis. Entidades: Cliente, Apólice, Carro e Acidentes. Requisitos: Um cliente pode ter várias apólices (no mínimo uma); Cada apólice somente dá cobertura a um carro; Um carro pode ter zero ou n registros de acidentes a ele. Atributos: Cliente: Número, Nome e Endereço; Apólice: Número e Valor; Carro: Registro e Marca; Acidente: Data, Hora e Local; 2. Para um consultório clínico. Entidades: Médico, Paciente e Exame. Requisitos: O banco de dados deverá armazenar informações sobre os vários exames de um determinado paciente, com o resultado e o valor pago (pode-se dar desconto para determinados pacientes); Atributos: Médico: Número, Nome e Especialidade; Paciente: Número, Nome, Endereço; Exame: Tipo Exame, Aceita Convênio, Requisitos, Valor exame. 3. Para uma Indústria. Entidades: Peças, Depósitos, Fornecedor, Projeto, Funcionário e Departamento. Requisitos: Cada Funcionário pode estar alocado a somente um Departamento; Cada Funcionário pode pertencer a mais de um Projeto; Um projeto pode utilizar-se de vários Fornecedores e de várias Peças; Uma Peça pode ser fornecida por vários Fornecedores e atender a vários Projetos; Um Fornecedor pode atender a vários Projetos e fornecer várias Peças; Um Depósito pode conter várias Peças; Deseja-se ter um controle do material utilizado por cada Projeto, identificando inclusive o seu Fornecedor. Gravar as informações de data de Início e Horas Trabalhadas no Projeto. Atributos: Peças: Número, Peso e Cor; Depósito: Número e Endereço; Fornecedor: Número e Endereço; Projeto: Número e Orçamento; Funcionário: Número, Salário e Telefone; Departamento: Número e Setor. 4. Uma companhia discográfica necessita de uma base de dados com informação sobre os seus músicos e equipamentos da empresa, com os seguintes pressupostos: Existem vários instrumentos musicais Os músicos em início de carreira muitas vezes partilham um local (morada) Cada álbum gravado na companhia tem um título, uma data, um formato (ex. CD) e uma data de lançamento As músicas são gravadas na companhia e têm um título. Cada músico pode tocar vários instrumentos, e cada instrumento pode ser tocado por vários músicos. É importante registrar desde quando o músico toca cada instrumento. Cada disco tem um certo número de músicas, mas cada música só pode aparecer num disco. Cada música pode ter a participação de vários músicos, e cada músico pode participar em várias músicas. Cada disco tem um músico que é o seu produtor. Os músicos podem produzir vários discos, porém um disco é produzido por apenas um produtor. 5. Determinada instituição tem diversas bibliotecas departamentais espalhadas no seu campus, sendo que cada uma tem os seus próprios livros que podem ser emprestados aos estudantes, funcionários e docentes da universidade. Será desenvolvido um sistema de informação para a gestão de empréstimos nas várias bibliotecas. Cada biblioteca está ligada a um departamento. Cada biblioteca está instalada em determinado edifício pertencente ao departamento e tem um horário de atendimento que pode variar (há departamentos nos quais existem aulas noturnas, enquanto em alguns há aulas ao sábado, etc.), mesmo durante os dias em que está em funcionamento, podendo algumas fechar em alguns períodos pré-definidos durante os dias em que está em funcionamento. Existem algumas regras que são comuns a todas às bibliotecas: Cada estudante inscrito numa licenciatura pode estar com um livro durante 7 dias, se for um aluno de mestrado pode estar com um livro até 15 dias e sendo aluno de doutoramento poderá estar com o livro até 25 dias; Um funcionário pode estar com um livro durante 15 dias; Um docente pode estar com um livro requisitado durante 30 dias. Naturalmente quando um determinado dia em que deveria ser devolvido um livro está fora do período de funcionamento da biblioteca, passa para o próximo dia em que seja possível a sua devolução. Periodicamente serão emitidas estatísticas sobre o cumprimento dos prazos definidos. O número de livros que podem ser requisitados varia de biblioteca para biblioteca, estando dependente do requisitante ser aluno e de que nível, funcionário ou docente. Além de livros, as bibliotecas podem ter outro tipo de material que não pode sair das bibliotecas. 6. Para um determinado aeroporto com os seguintes pressupostos: Cada avião tem um número de registro, e cada avião é de um modelo específico. O aeroporto pode acolher um certo número de modelos de aviões, e cada modelo tem um código de modelo, uma capacidade e um peso. Um certo número de empregados (técnicos e controladores) trabalham no aeroporto. É necessário guardar o seu BI, endereço, telefone e salário. Cada técnico é perito num ou mais modelos de aviões, e vários técnicos podem ser peritos em modelos iguais. Cada técnico tem um nível de qualificação. O aeroporto tem um certo número de testes que são usados periodicamente para verificar o estado dos aviões. Cada teste é feito por um técnico que atribui uma pontuação. 7. Determinada organização XPTO a qual estão ligadas diversas instituições de ensino, promove conferências, workshops, simpósios e outros tipos de eventos, designando uma ou mais instituições para a organização de cada evento. Dado o grande volume de informação que é necessário tratar, decidiram construir uma base de dados que vai suportar toda a informação gerada. Considere as seguintes características: Para cada evento são recebidos diversos artigos e um evento realiza-se numa determinada instituição, podendo ser organizado por várias instituições diferentes. Um evento é de determinado tipo (conferência, workshop, etc.), tem um título, tem vários temas que são previamente divulgados e realiza-se num determinado período de tempo. Cada artigo pode ser sobre um ou mais temas, podendo ser especificados até 5 temas, pela importância relativa, ou seja, pode-se especificar o 1º tema, o 2º,... Cada artigo tem um título, um ou mais autores (há o primeiro autor e pode haver o 2º, o 3º,...) e pertence a uma das seguintes categorias: pôster, short paper ou full paper. Cada autor tem obrigatoriamente um nome, uma data de nascimento, um endereço de correio eletrônico, reside em determinado país e pode ainda submeter um ou mais artigos. Um autor tem de estar previamente registrado na organização XPTO. Um autor pode trabalhar em várias instituições, por exemplo: um autor pode trabalhar numa instituição A e B e submeter dois artigos, um pela instituição A porque está relacionado com o trabalho desenvolvido em A e outro pela instituição B (repare que o autor é o mesmo). Para cada instituição com a qual um autor colabora, pode-se especificar a sua função na organização. 8. Projetar um Banco de Dados satisfazendo as seguintes restrições e requisitos: Para um Vendedor, armazenar seu código, nome, endereço e comissão; Para um cliente, armazenar o seu código, nome, endereço, faturamento acumulado e limite de crédito. Além disso, armazenar o código e o nome do vendedor que o atende. Um vendedor pode atender muitos clientes, porém um cliente deve ter exatamente um vendedor; Para uma peça, armazenar seu código, descrição, preço quantidade em estoque e o número do armazém onde a peça está estocada. Uma peça somente pode estar estocadanum único armazém. Para um armazém, armazenar seu código e endereço; Para um pedido, armazenar seu número, data, código, nome e endereço do cliente, que fez o pedido e o código do vendedor para cálculo da comissão. Além disso, para cada item do pedido armazenar o código da peça, quantidade e preço cotado. Há somente um cliente por pedido e um vendedor; O preço cotado no pedido pode ser mesmo que o preço corrente no arquivo de peças, mas não necessariamente. 9. O banco de dados de uma empresa mantém informações sobre empregados, departamentos e projetos. Após a coleta de requisitos e a fase de análise, chegou-se à seguinte descrição para modelagem de dados: A empresa é organizada em departamentos. Cada departamento possui um nome e código únicos, além de um empregado que gerencia o departamento. O banco de dados deve armazenar a data em que o empregado passou a gerenciar o departamento. Cada departamento controla um certo número de projetos, cada qual com seu título e número únicos. Para cada empregado, armazena-se seu nome, CPF, salário, sexo e data de nascimento. Cada empregado é vinculado a um único departamento, mas pode trabalhar em vários projetos que não são necessariamente controlados pelo seu departamento. Deve-se registrar no BD a carga horária semanal do empregado em cada projeto. Também deve-se manter informação sobre o supervisor direto de cada empregado. Deseja-se igualmente que o BD armazene dados de dependentes de cada empregado. Para cada dependente, mantém-se seu nome, sexo, data de nascimento e relação com o empregado. 10. Uma pequena locadora de vídeos possui ao redor de 2.000 DVDs cujo empréstimo deve ser controlado. Cada DVD possui um número. Para cada filme, é necessário saber seu título e sua categoria (comédia, drama, aventura, ...). Cada filme recebe um identificador próprio. Para cada DVD é controlado que filme ele contém. Para cada filme há pelo menos um DVD e cada DVD contém somente um filme. Alguns poucos filmes necessitam de 2 DVDs. Os clientes podem desejar encontrar filmes por categoria e também filmes estrelados por atores prediletos. Por isso, é necessário manter informação sobre atores que estrelam cada filme. A locadora possui muitos clientes cadastrados. Somente clientes cadastrados podem alugar DVDs. Para cada cliente é necessário saber seu nome, telefone e endereço. Além disso, cada cliente recebe um número de associado. Por fim, deseja-se saber que DVDs cada cliente tem emprestado no momento e qual o histórico de aluguéis efetuado. Um cliente pode ter vários DVDs alugados em um instante do tempo. 11. O banco de dados de um museu de arte mantém várias informações sobre objetos, exposições e artistas, conforme a descrição abaixo. Todo objeto é criado por um artista. Para cada artista, mantém-se seu nome, data de nascimento e falecimento, país e estilo principal. Para cada objeto, mantém-se um código, título, estilo, ano de criação e descrição. Os objetos relevantes para o museu podem ser esculturas, pinturas ou de outros tipos. No caso de esculturas, registra-se largura, altura, peso e o material principal. Um tipo especial de escultura são as estátuas, para as quais armazena-se também o nome do homenageado. No caso de pinturas, armazena-se as dimensões e o tipo de moldura. Para outros tipos de objetos, armazena-se uma descrição do tipo. Os objetos podem compor exposições do museu. Cada exposição tem um nome, uma data inicial e uma data final. Toda exposição possui um conjunto de objetos expostos. Um objeto só pode estar em uma exposição por vez, mas pode compor diferentes exposições ao longo do tempo. Os objetos cadastrados no museu podem ser do seu próprio acervo (objetos permanentes) ou podem ser emprestados temporariamente. No caso de objetos permanentes, mantém-se a data de aquisição, o custo, a fonte vendedora e seu estado de exposição (exposto ou não). No caso de objetos emprestados, mantém-se as datas de empréstimo e retorno do objeto, além da informação sobre a coleção de origem. Uma coleção é um conjunto temático de objetos mantidos por outra instituição, que podem ser emprestados para o museu quando necessário. Para cada coleção, mantém-se seu nome, descrição, pessoa responsável e telefone. 12. As empresas em geral necessitam controlar seus processos de compra e venda de produtos, além de ser extremamente importante para essas empresas consultar as informações envolvidas nestes processos. Uma grande empresa de comércio varejista deseja desenvolver um sistema de computação que automatize suas atividades, a fim de fornecer aos seus clientes produtos com preços competitivos e um serviço de melhor qualidade. O projeto dessa aplicação atenderá às seguintes funcionalidades básicas: Registro das movimentações: compras e vendas; Cadastros: produtos, clientes, fornecedores, funcionários e departamentos; Controle de estoque dos produtos; As informações que devem ser armazenadas em um banco de dados são mostradas a seguir. Dos produtos deseja-se armazenar: o código do produto, sua descrição, a unidade de medida, o valor para compra, o valor para venda, a quantidade em estoque, além da quantidade máxima e mínima desejável em estoque. Dos clientes deseja-se guardar: nome, endereço, cpf/cnpj, sexo, estado civil, data de nascimento, profissão e telefone. Dos fornecedores é preciso manter: razão social, nome de fantasia, endereço, cnpj e telefone. Dos funcionários deve-se armazenar: nome, endereço, cpf, sexo, estado civil, data de nascimento, cargo, telefone, departamento em que trabalha e salário. Dos departamentos é preciso guardar o seu código e sua descrição. Para cada compra (realizada pela empresa) deseja-se saber: o fornecedor, os produtos adquiridos e suas respectivas quantidades, além da data em que a compra foi efetuada. Para cada venda (realizada pela empresa) deseja-se saber: o cliente, o vendedor, os produtos adquiridos e suas respectivas quantidades, além da data em que a venda foi efetuada. 13. O DETRAN deseja constituir um banco de dados para controlar as infrações ocorridas no estado. Os veículos são identificados pela placa e também descritos por chassi, cor predominante, modelo, categoria e ano de fabricação. Cada veículo possui um único proprietário, que é identificado por seu CPF. Deve-se saber o nome, endereço, bairro, cidade estado, telefone, sexo, data de nascimento. Todo veículo possui um único modelo, por exemplo: GOL MI, GOL 1.8, UNO CS, etc. Similarmente ao modelo, uma categoria deve ser atribuída a cada veículo, por exemplo: AUTOMÓVEL, MOTOCICLETA, CAMINHÃO, etc. Existem diversos tipos de infração: AVANÇO DE SINAL VERMELHO, PARADA SOBRE A FAIXA DE PEDESTRES, etc. A cada tipo de infração é associado um valor que deverá ser cobrado na ocorrência de infração. Uma infração é identificada pelo veículo infrator, data/hora e tipo de infração. Também é importante conhecer o local, velocidade aferida (se possível) e o agente de trânsito. Cada local é descrito pelo código, posição geográfica e velocidade permitida; um local é geralmente referenciado por seu código. Um agente de trânsito é conhecido através de sua matrícula, sendo também descrito pelo nome e data de contratação. 14. Deseja desenvolver um software para auxiliar o controle de um aeroclube que possui como sócios pilotos, instrutores e alunos de pilotagem. Todos sócios são identificados pelo número de matrícula, e caracterizados por nome, endereço e idade. Os pilotos possuem um número de brevê (único). Os instrutores são pilotos com formação adicional de instrutor, e deve ser registrado o nome do curso, a data de obtenção do diploma, bem como a instituição. Para os alunos de pilotagem, guarda-se os registros de todas suas saídas (aulas) para contabilizaçãode horas para obtenção do brevê. Para cada saída registra-se a data, instrutor, hora de saída e de chegada, bem como o parecer do instrutor sobre o vôo. A escola só ministra cursos básicos, e, portanto não há professores que são alunos de cursos avançados. Para emissão do brevê, é necessário que o aluno comprove ter o número de horas mínimo de vôo, bem como apresente os pareceres dos instrutores sobre as habilidades desenvolvidas a cada aula prática. 15. Construa um diagrama E-R (incluindo as cardinalidades) para controle do prontuário de pacientes de um hospital. O hospital possui um conjunto de pacientes e um conjunto de médicos. No registro dos pacientes, temos o nome, RG, CPF, endereço e telefone. No registro dos médicos temos o nome, especialidade, RG, CPF, CRM, endereço e telefone. Cada paciente tem associado a si um prontuário (sua ficha), onde são registradas basicamente todas ocorrências, exames, consultas, medicamentos ministrados associadas a ele. Cada registro no seu prontuário, além de ter a data tem que estar relacionado a um médico, que é o responsável pela informação do registro cadastrado no sistema. 16. Construa um diagrama E-R para uma companhia de seguros de automóveis com um conjunto de clientes, onde cada um possui um certo número de carros. Os dados do cliente são nome, RG, CPF, endereço e telefone. Do carro deve-se armazenar a placa, código Renavan, fabricante, modelo, e ano. Associado a cada carro há um histórico de ocorrências. Um carro podem possuir várias ocorrências ou nenhuma. Cada ocorrência deve ter uma data, local e descrição. 17. Uma determinada empresa possui diversos veículos para realizar atividades do dia a dia e deseja acompanhar e controlar a utilização dos mesmos. Para tanto deseja criar um banco de dados com informações sobre o uso que os funcionários fazem destes veículos. Veículos são de propriedade da empresa e possuem placa, modelo, marca, ano de fabricação, data da compra e cor. A utilização de qualquer veículo está restrita aos funcionários da empresa mediante uma autorização prévia de algum funcionário responsável. O objetivo é realizar a autorização diretamente no sistema, o funcionário responsável insere uma nova autorização especificando data e hora da autorização, data e hora prevista para saída, data e hora prevista para retorno, funcionário autorizado a fazer uso do veículo e o motivo alegado para solicitação do veículo. Uma autorização irá possuir um número único. Na autorização também fica registrado quem autorizou (funcionário).No retorno do veículo deverá ser preenchido um registro de uso, especificando km saída, km chegada, data e hora real de saída, data e hora real de retorno, descrição do roteiro percorrido. Podem haver autorizações que não são efetivadas, ou seja, por algum motivo o veículo não é utilizado, neste caso não há registro de uso, mas a autorização permanecerá cadastrada no sistema. Durante a utilização podem ocorrer problemas ou suspeitas de haver algum problema. Ao registrar a utilização o usuário deverá especificar todos problemas detectados. A descrição de cada problema inclui sintomas apresentados, componente do veículo que é objeto do problema ou suspeita, e uma sugestão para solução ou descrição de uma solução já implementada, caso o problema já tenha sido corrigido. Um exemplo de problema que pode ocorrer é o carro não ligar quando o funcionário está andando ou tenta voltar. Será registrado o sintoma “Carro não ligou, pode ser falta de bateria”, o problema seria “Carro não liga” e se for chamado um mecânico, deverá ser descrito o que foi feito como solução ou caso volte ao normal, a solução não existirá, apenas um comentário “Voltou ao normal” ou algo semelhante. Também é objetivo controlar as manutenções e abastecimentos de cada veículo. Quando um veículo é encaminhado para um conserto ou qualquer tipo de manutenção será necessário registrar a data em que foi realizada a manutenção, o motivo de ter sido realizada, uma descrição das atividades realizadas, além do valor pago. Os abastecimentos devem ser registrados para acompanhamento dos gastos de combustível. A data, quantidade de litros, o valor devem ser registrados. Um mesmo veículo pode possuir diversas manutenções ou abastecimentos em uma mesma data. Problemas ocorridos estão associadas aos registros de uso, mas as manutenções e abastecimentos estão associadas somente aos veículos, sem relação com registro de uso. Se um funcionário realizar um abastecimento durante o uso de um veículo deverá comunicar o fato, porém o mesmo não estará associado ao registro de uso. Algumas consultas que a empresa deseja realizar sobre os dados armazenados: Ocorrências por veículo: Registros de autorizações e uso de cada veículo. Autorização por motivo: Deve ser possível escolher um motivo e visualizar todas as autorizações do mesmo. Autorizações e uso por funcionário: Deverão ser mostrados dados do funcionário e a lista de uso de cada veículo. Autorizações por responsável: Dados do funcionário responsável pela autorização e a lista de todas as autorizações feitas por ele em determinado período. Problemas por veículo e por tipo de problema: Deve ser possível por exemplo escolher um problema (tipo de problema) e/ou um veículo e ver as ocorrências. Valores de manutenção e abastecimento por veículo em um período e por motivo de manutenção. Manutenções por tipo: Classificar as manutenções como “Mecanica preventiva”, “Por acidente” e outros tipos auxilia a empresa a verificar quais os tipos mais frequentes e com que veículos são realizadas. 1.3.2 Modelo Lógico Mapeamento do ME-R para Relacional O Modelo Entidade-Relacionamento é responsável por realizar uma representação, mais conceitual, dos dados de uma aplicação. Esta representação é um pouco distante da forma como realmente os elementos (entidades e relacionamentos) serão implementados. O modelo relacional fornece uma representação dos dados de forma mais próxima de como estes dados se encontrarão quando forem definidos os arquivos para o BD. Regras para o Mapeamento Os passos necessários, a partir de um projeto conceitual (ME-R), para realizar a modelagem do projeto lógico de implementação do BD por meio do Modelo Relacional é realizado seguindo as seguintes regras: 1- Todas as entidades são mapeadas para uma relação contendo os mesmos atributos do ME-R. 2- Para entidade fraca é criada a relação contendo todos os seus atributos, tendo acrescido, como chave estrangeira, a chave primária da entidade forte (pai). 3- Para relacionamentos 1:1 - dentre as relações que mapeiam as entidades participantes escolha uma delas (a que possuir participação total) e inclua como chave estrangeira a chave primária da outra. 4- Para relacionamentos 1:N – escolha a relação que representa a entidade presente no lado N, e acrescente como chave estrangeira a chave primária da entidade do lado 1 (esta regra não se aplica para os relacionamentos de identificação - fracos) 5- Para relacionamentos N:M – é criado uma nova relação contendo como chaves estrangeiras as chaves primárias das entidades participantes, mais os atributos do relacionamento. 6- Os atributos multivalorados (A) - é criada uma relação R que terá como atributos os mesmos de A, mais a chave primária da entidade (ou relacionamento) no qual A é atributo. Se o atributo multivalorado é composto, será incluído seus componentes. 7- Para relacionamentos triplos o mapeamento ocorre de forma semelhante ao descrito pela regra 5, apenas considerando que se uma das entidades apresentar um limite máximo de participação igual a 1, a chave desta entidade pode determinar arelação montada. 2 Linguagem SQL 2.1 Breve Histórico A história da SQL começa em 1970 com a publicação por E. F. Codd, no ACM Journal, de um artigo intitulado “A Relational Model of Data for Large Shared Data Banks”. A SQL foi desenvolvido originalmente no início dos anos 70 nos laboratórios da IBM em San Jose, dentro do projeto System R, que tinha por objetivo demonstrar a viabilidade da implementação do modelo relacional proposto por E. F. Codd. O nome original da linguagem era SEQUEL, acrônimo para "Structured English Query Language" (Linguagem de Consulta Estruturada em Inglês), vindo daí o faco de, até hoje, a sigla, em inglês, ser comumente pronunciada "síquel" ao invés de "és-kiú-él", letra a letra. No entanto, em português, a pronúncia mais corrente é a letra a letra: "ésse-quê-éle". Em 1986 o American National Standards Institute (ANSI) e o International Standards Organization (ISO) publicaram os padrões para a SQL, a chamada SQL-86. Embora a linguagem SQL é referida sempre como uma “linguagem de consulta”, ela possui muitos outros recursos além da consulta ao BD, como meios para definição de estruturas de dados, modificação dos dados no BD e para especificação de restrições de segurança, como podemos ver abaixo, (SILBERSCHATZ, 2006): Linguagem de definição de dados (Data-definition Language – DDL): proporciona comandos para definição de esquemas de relações, exclusão de relações, criação de índices e modificação nos esquemas de relações. Linguagem interativa de manipulação de dados (Data-manipulation Language – DML): abrange uma linguagem de consulta baseada na álgebra relacional e calculo relacional de tuplas, além de comandos para inserção, exclusão e modificação de tuplas. Incorporação DML (Embedded DML): projetado para aplicação em linguagens de programação de uso geral como PL/I, Cobol, Pascal, Fortran e C. Definição de visões: a SQL DDL possui comandos para definição de visões. Autorização: a AQL DDL engloba comandos para especificação de direitos de acesso a relações e visões. Integridade: a SQL DDL possui comandos para especificação de regras de integridade que os dados que serão armazenados no BD devem satisfazer. Atualizações que violarem regras de integridade serão desprezadas. Controle de transações: a SQL inclui comandos para especificação de iniciação e finalização de transações. Algumas implementações permitem também explicitar bloqueios a dados para controle de concorrência. 2.2 Tipos de dados Os tipos de dados SQL se classificam em 13 tipos de dados primários e de vários sinônimos válidos reconhecidos por tais tipos de dados. Os tipos de dados primários são: Tipo de Dado Comprimento Descrição BINARY 1 byte Para consultas sobre tabela anexa de produtos de banco de dados que definem um tipo de dados Binário. BIT 1 byte Valores Sim/Não ou True/False BYTE 1 byte Um valor inteiro entre 0 e 255. COUNTER 4 bytes Um número incrementado automaticamente (de tipo Long) CURRENCY 8 bytes Um inteiro escalável entre 922.337.203.685.477,5808 e 922.337.203.685.477,5807. DATETIME 8 bytes Um valor de data ou hora entre os anos 100 e 9999. SINGLE 4 bytes Um valor em ponto flutuante de precisão simples com uma classificação de - 3.402823*1038 a -1.401298*10-45 para valores negativos, 1.401298*10- 45 a 3.402823*1038 para valores positivos, e 0. DOUBLE 8 bytes Um valor em ponto flutuante de dupla precisão com uma classificação de - 1.79769313486232*10308 a - 4.94065645841247*10-324 para valores negativos, 4.94065645841247*10-324 a 1.79769313486232*10308 para valores positivos, e 0. SHORT 2 bytes Um inteiro curto entre -32,768 e 32,767. LONG 4 bytes Um inteiro longo entre -2,147,483,648 e 2,147,483,647. LONGTEXT 1 byte por caractere De zero a um máximo de 1.2 gigabytes. LONGBINARY Segundo se necessite De zero 1 gigabyte. Utilizado para objetos OLE. TEXT 1 byte por caractere De zero a 255 caracteres. A seguinte tabela apresenta os sinônimos dos tipos de dados definidos: Tipo de Dado Sinônimos BINARY VARBINARY BIT BOOLEAN, LOGICAL, LOGICAL1, YESNO BYTE INTEGER1 COUNTER AUTOINCREMENT CURRENCY MONEY DATETIME DATE, TIME, TIMESTAMP SINGLE FLOAT4, IEEESINGLE, REAL DOUBLE FLOAT, FLOAT8, IEEEDOUBLE, NUMBER, NUMERIC SHORT INTEGER2, SMALLINT LONG INT, INTEGER, INTEGER4 LONGBINARY GENERAL, OLEOBJECT LONGTEXT LONGCHAR, MEMO, NOTE TEXT ALPHANUMERIC, CHAR – CHARACTER STRING – VARCHAR VARIANT (Não Admitido) VALUE 2.3 Criação de tabelas: CREATE TABLE CREATE TABLE minha_primeira_tabela ( primeira_coluna char (30), segunda_coluna integer ); Exemplo: CREATE TABLE produtos ( cod_prod integer, nome char (40), preco numeric ); 2.4 Apagar tabelas: DROP TABLE DROP TABLE minha_primeira_tabela; DROP TABLE produtos; 2.5 Valor Padrão: Pode ser atribuído um valor padrão a uma coluna. Quando é criada uma nova linha, e não é especificado nenhum valor para algumas de suas colunas, estas colunas são preenchidas com o valor padrão de cada uma delas. Além disso, um comando de manipulação de dados pode requerer explicitamente que a coluna receba o seu valor padrão, sem saber qual é este valor. Se não for declarado explicitamente nenhum valor padrão, o valor nulo será o valor padrão. Exemplo: CREATE TABLE produtos ( cod_prod integer PRIMARY KEY, nome char (40), preco numeric DEFAULT 9.99 ); 2.6 Restrições 2.6.1 Restrições de verificação Uma restrição de verificação é o tipo mais genérico de restrição. Permite especificar que os valores de uma determinada coluna devem estar de acordo com uma expressão booleana. Por exemplo, para permitir apenas preços com valores positivos: Exemplo: CREATE TABLE produtos ( cod_prod integer, nome char (30), preco numeric CHECK (preco > 0) ); Como pode ser observado, a definição da restrição vem após o tipo de dado, assim como a definição do valor padrão. O valor padrão e a restrição podem estar em qualquer ordem. A restrição de verificação é formada pela palavra chave CHECK seguida por uma expressão entre parênteses. A expressão da restrição de verificação deve envolver a coluna sendo restringida, senão não fará muito sentido. Uma restrição de verificação também pode referenciar várias colunas. Supondo que serão armazenados o preço normal e o preço com desconto, e que se deseje garantir que o preço com desconto seja menor que o preço normal: CREATE TABLE produtos ( cod_prod integer, nome char (20), preco numeric, CHECK (preco > 0), preco_com_desconto numeric, CHECK (preco_com_desconto > 0), CHECK (preco > preco_com_desconto) ); ou ainda CREATE TABLE produtos ( cod_prod integer, nome text, preco numeric CHECK (preco > 0), preco_com_desconto numeric, CHECK (preco_com_desconto > 0 AND preco > preco_com_desconto) ); 2.6.2 Restrições de não-nulo Uma restrição de não-nulo simplesmente especifica que uma coluna não pode assumir o valor nulo. Um exemplo da sintaxe: CREATE TABLE produtos ( cod_prod integer NOT NULL, nome char (30) NOT NULL, preco numeric ); A restrição de não-nulo é sempre escrita como restrição de coluna. Obviamente, uma coluna pode possuir mais de uma restrição, bastando apenas escrever uma restrição em seguida da outra: CREATE TABLE produtos ( cod_prod integer NOT NULL, nome char (30) NOT NULL, preco numeric NOT NULL CHECK (preco > 0) ); A ordem dasrestrições não importa, porque não determina, necessariamente, a ordem de verificação das restrições. 2.6.3 Restrições de unicidade A restrição de unicidade garante que os dados contidos na coluna, ou no grupo de colunas, é único em relação a todas as outras linhas da tabela. A sintaxe é CREATE TABLE produtos ( cod_prod integer UNIQUE, nome char (30), preco numeric ); quando escrita como restrição de coluna, e CREATE TABLE produtos ( cod_prod integer, nome char(30), preco numeric, UNIQUE (cod_prod) ); quando escrita como restrição de tabela. Se uma restrição de unicidade faz referência a um grupo de colunas, as colunas são listadas separadas por vírgula: CREATE TABLE exemplo ( a integer, b integer, c integer, UNIQUE (a, c) ); Isto especifica que a combinação dos valores das colunas indicadas deve ser único para toda a tabela, embora não seja necessário que cada uma das colunas seja única (o que geralmente não é). Também é possível atribuir nomes às restrições de unicidade: CREATE TABLE produtos ( cod_prod integer CONSTRAINT unq_cod_prod UNIQUE, nome char (40), preco numeric ); De um modo geral, uma restrição de unicidade é violada quando existem duas ou mais linhas na tabela onde os valores de todas as colunas incluídas na restrição são iguais. Entretanto, os valores nulos não são considerados iguais nesta comparação. 2.6.4 Chaves primárias Tecnicamente a restrição de chave primária é simplesmente a combinação da restrição de unicidade com a restrição de não-nulo. Portanto, as duas definições de tabela abaixo aceitam os mesmos dados: CREATE TABLE produtos ( cod_prod integer UNIQUE NOT NULL, nome char (30), preco numeric ); CREATE TABLE produtos ( cod_prod integer PRIMARY KEY, nome varchar (30), preco numeric ); As chaves primárias também podem restringir mais de uma coluna; a sintaxe é semelhante à da restrição de unicidade: CREATE TABLE exemplo ( a integer, b integer, c integer, PRIMARY KEY (a, c) ); A chave primária indica que a coluna, ou grupo de colunas, pode ser utilizada como identificador único das linhas da tabela (Isto é uma conseqüência direta da definição da chave primária. Deve ser observado que a restrição de unicidade não fornece, por si só, um identificador único, porque não exclui os valores nulos). A chave primária é útil tanto para fins de documentação quanto para os aplicativos cliente. Por exemplo, um aplicativo contendo uma Interface de Usuário Gráfica (GUI), que permite modificar os valores das linhas, provavelmente necessita conhecer a chave primária da tabela para poder identificar as linhas de forma única. Uma tabela pode ter no máximo uma chave primária (embora possa ter muitas restrições de unicidade e de não- nulo). 2.6.5 Chaves Estrangeiras A restrição de chave estrangeira especifica que o valor da coluna (ou grupo de colunas) deve corresponder a algum valor existente em uma linha de outra tabela. Diz-se que a chave estrangeira mantém a integridade referencial entre duas tabelas relacionadas. Supondo que já temos a tabela de produtos utilizada diversas vezes anteriormente: CREATE TABLE produtos ( cod_prod integer PRIMARY KEY, nome char (30), preco numeric ); Agora vamos assumir a existência de uma tabela armazenando os pedidos destes produtos. Desejamos garantir que a tabela de pedidos contenha somente pedidos de produtos que realmente existem. Para isso é definida uma restrição de chave estrangeira na tabela de pedidos fazendo referência à tabela de produtos: CREATE TABLE pedidos ( cod_pedido integer PRIMARY KEY, cod_prod integer REFERENCES produtos (cod_prod), quantidade integer ); Isto torna impossível criar um pedido com cod_prod não existente na tabela de produtos. Nesta situação é dito que a tabela de pedidos é a tabela que faz referência, e a tabela de produtos é a tabela referenciada. Da mesma forma existem colunas fazendo referência e sendo referenciadas. O comando acima pode ser abreviado escrevendo CREATE TABLE pedidos ( cod_pedido integer PRIMARY KEY, cod_prod integer REFERENCES produtos, quantidade integer ); porque, na ausência da lista de colunas, a chave primária da tabela referenciada é usada como a coluna referenciada. A chave estrangeira também pode restringir e referenciar um grupo de colunas. Como usual, é necessário ser escrito na forma de restrição de tabela. Abaixo está mostrado um exemplo artificial da sintaxe: CREATE TABLE t1 ( a integer PRIMARY KEY, b integer, c integer, FOREIGN KEY (b, c) REFERENCES outra_tabela (c1, c2) ); 2.7 Inserção de dados A tabela recém-criada não contém dados. A primeira ação a ser realizada para o banco de dados ter utilidade é inserir dados. Conceitualmente, os dados são inseridos uma linha de cada vez. É claro que é possível inserir mais de uma linha, mas não existe maneira de inserir menos de uma linha por vez. Mesmo que se conheça apenas o valor de algumas colunas, deve ser criada uma linha completa. Para criar uma linha é utilizado o comando INSERT. Este comando requer o nome da tabela, e um valor para cada coluna da tabela. Por exemplo, considere a tabela produtos: CREATE TABLE produtos ( cod_prod integer, nome char (30), preco numeric ); Um exemplo de comando para inserir uma linha é: INSERT INTO produtos VALUES (1, 'mouse', 9.99); Geralmente os valores dos dados são literais (constantes), mas também são permitidas expressões escalares. A sintaxe mostrada acima tem como desvantagem ser necessário conhecer a ordem das colunas da tabela. Para evitar isto, as colunas podem ser relacionadas explicitamente. Por exemplo, os dois comandos mostrados abaixo possuem o mesmo efeito do comando mostrado acima: INSERT INTO produtos (cod_prod, nome, preco) VALUES (1, 'Queijo', 9.99); INSERT INTO produtos (nome, preco, cod_prod) VALUES ('Queijo', 9.99, 1); 2.8 Atualização de dados A modificação dos dados armazenados no banco de dados é referida como atualização. Pode ser atualizada uma linha, todas as linhas, ou um subconjunto das linhas da tabela. Uma coluna pode ser atualizada separadamente; as outras colunas não são afetadas. Para realizar uma atualização são necessárias três informações: 1. O nome da tabela e da coluna a ser atualizada; 2. O novo valor para a coluna; 3. Quais linhas serão atualizadas. Não é necessariamente possível especificar diretamente a linha a ser atualizada. Em vez disso, devem ser especificadas as condições que a linha deve atender para ser atualizada. Somente havendo uma chave primária na tabela (não importando se foi declarada ou não), é possível endereçar uma linha específica com confiança, escolhendo uma condição correspondendo à chave primária. Ferramentas gráficas de acesso a banco de dados dependem da chave primária para poderem atualizar as linhas individualmente. Por exemplo, o comando mostrado abaixo atualiza todos os produtos com preço igual a 5, mudando estes preços para 10: UPDATE produtos SET preco = 10 WHERE preco = 5; Este comando pode atualizar nenhuma, uma, ou muitas linhas. Não é errado tentar uma atualização que não corresponda a nenhuma linha. Vejamos este comando em detalhe: Primeiro aparece a palavra chave UPDATE seguida pelo nome da tabela. Como usual, o nome da tabela pode ser qualificado pelo esquema, senão é procurado nocaminho. Depois aparece a palavra chave SET, seguida pelo nome da coluna, por um sinal de igual, e pelo novo valor da coluna. O novo valor da coluna pode ser qualquer expressão escalar, e não apenas uma constante. Por exemplo, se for desejado aumentar o preço de todos os produtos em 10% pode ser utilizado: UPDATE produtos SET preco = preco * 1.10; Como pode ser visto, a expressão para obter o novo valor pode fazer referência ao valor antigo. Também foi deixada de fora a cláusula WHERE. Quando esta cláusula é omitida, significa que todas as linhas da tabela serão atualizadas e, quando está presente, somente as linhas que atendem à condição desta cláusula serão atualizadas. Deve ser observado que o sinal de igual na cláusula SET é uma atribuição, enquanto o sinal de igual na cláusula WHERE é uma comparação, mas isto não cria uma ambigüidade. Obviamente, a condição da cláusula WHERE não é necessariamente um teste de igualdade, estão disponíveis vários outros operadores, mas a expressão deve produzir um resultado booleano. Também pode ser atualizada mais de uma coluna pelo comando UPDATE, colocando mais de uma atribuição na cláusula SET. Por exemplo: UPDATE minha_tabela SET a = 5, b = 3, c = 1 WHERE a > 0; 2.9 Exclusão de dados Até aqui foi mostrado como adicionar dados a tabelas, e como modificar estes dados. Está faltando mostrar como remover os dados que não são mais necessários. Assim como só é possível adicionar dados para toda uma linha, uma linha também só pode ser removida por inteiro da tabela. Na seção anterior foi explicado que o SQL não fornece uma maneira para endereçar diretamente uma determinada linha. Portanto, a remoção das linhas só pode ser feita especificando as condições que as linhas a serem removidas devem atender. Havendo uma chave primária na tabela, então é possível especificar exatamente a linha. Mas também pode ser removido um grupo de linhas atendendo a uma determinada condição, ou podem ser removidas todas as linhas da tabela de uma só vez. É utilizado o comando DELETE para remover linhas; a sintaxe deste comando é muito semelhante a do comando UPDATE. Por exemplo, para remover todas as linhas da tabela produtos possuindo preço igual a 10: DELETE FROM produtos WHERE preco = 10; Se for escrito simplesmente DELETE FROM produtos; então todas as linhas da tabela serão excluídas. 2.10 Consultas Nos itens anteriores vimos como criar tabelas, como preenchê-las com dados, e como manipular estes dados. Agora, finalmente, é mostrado como trazer estes dados para fora do banco de dados. 2.10.1 Visão geral O processo de trazer, ou o comando para trazer os dados armazenados no banco de dados, é chamado de consulta. No SQL, o comando SELECT é utilizado para especificar consultas. A sintaxe geral do comando SELECT é SELECT lista_de_seleção FROM expressão_de_tabela [especificação_da_ordenação] As próximas seções descrevem em detalhes a lista de seleção, a expressão de tabela, e a especificação da ordenação. O tipo mais simples de consulta possui a forma: SELECT * FROM tabela1; Supondo existir uma tabela chamada tabela1, este comando traz todas as linhas e todas as colunas da tabela1. A forma de trazer depende do aplicativo cliente. A especificação da lista de seleção * significa todas as colunas que a expressão de tabela possa fornecer. A lista de seleção também pode selecionar um subconjunto das colunas disponíveis, ou efetuar cálculos utilizando as colunas. Por exemplo, se a tabela1 possui colunas chamadas a, b e c (e talvez outras), pode ser feita a seguinte consulta: SELECT a, b + c FROM tabela1; 2.10.2 Expressões de tabela Uma expressão de tabela computa uma tabela. A expressão de tabela contém a cláusula FROM seguida, opcionalmente, pelas cláusulas WHERE, GROUP BY e HAVING. As expressões de tabela triviais fazem, simplesmente, referência as tão faladas tabelas em disco, chamadas de tabelas base, mas podem ser utilizadas expressões mais complexas para modificar ou combinar tabelas base de várias maneiras. As cláusulas opcionais WHERE, GROUP BY e HAVING, da expressão de tabela, especificam um processo de transformações sucessivas realizadas na tabela produzida pela cláusula FROM. Todas estas transformações produzem uma tabela virtual que fornece as linhas passadas para a lista de seleção, para então serem computadas as linhas de saída da consulta. 2.10.2.1 A cláusula FROM A Cláusula FROM deriva uma tabela a partir de uma ou mais tabelas especificadas na lista, separada por vírgulas, de referências a tabela. FROM referência_a_tabela [, referência_a_tabela [, ...]] Uma referência a tabela pode ser um nome de tabela (possivelmente qualificado pelo esquema) ou uma tabela derivada,como uma subconsulta, uma junção de tabelas ou, ainda, uma combinação complexa destas. Se for listada mais de uma referência a tabela na cláusula FROM, é feita uma junção cruzada (cross-join) (veja abaixo) para formar a tabela virtual intermediária que poderá, então, estar sujeita às transformações das cláusulas WHERE, GROUP BY e HAVING, gerando o resultado final de toda a expressão de tabela. Quando uma referência a tabela especifica uma tabela ancestral em uma hierarquia de herança de tabelas, a referência a tabela não produz linhas apenas desta tabela, mas inclui as linhas de todas as tabelas descendentes, a não ser que a palavra chave ONLY preceda o nome da tabela. Entretanto, esta referência produz apenas as colunas existentes na tabela especificada são ignoradas todas as colunas adicionadas às tabelas descendentes. 2.10.3 Junção de tabelas Uma tabela juntada é uma tabela derivada de outras duas tabelas (reais ou derivadas), de acordo com as regras do tipo particular de junção. Estão disponíveis as junções internas, externas e cruzadas. 2.10.3.1 Tipos de junção Junção cruzada T1 CROSS JOIN T2 Para cada combinação de linhas de T1 e T2, a tabela derivada contém uma linha formada por todas as colunas de T1 seguidas por todas as colunas de T2. Se as tabelas possuírem N e M linhas, respectivamente, a tabela juntada terá N * M linhas. FROM T1 CROSS JOIN T2 equivale a FROM T1, T2. Também equivale a FROM T1 INNER JOIN T2 ON TRUE (veja abaixo). Junções qualificadas T1 {[INNER] | {LEFT | RIGHT | FULL} [OUTER]} JOIN T2 ON expressão_booleana T1 {[INNER] | {LEFT | RIGHT | FULL} [OUTER]} JOIN T2 USING (lista de colunas de junção) T1 NATURAL {[INNER] | {LEFT | RIGHT | FULL} [OUTER]} JOIN T2 As palavras INNER e OUTER são opcionais em todas as formas. INNER é o padrão; LEFT, RIGHT e FULL implicam em junção externa. A condição de junção é especificada na cláusula ON ou USING, ou implicitamente pela palavra NATURAL. A condição de junção determina quais linhas das duas tabelas de origem são consideradas correspondentes , conforme explicado detalhadamente abaixo. A cláusula ON é o tipo mais geral de condição de junção: recebe uma expressão de valor booleana do mesmo tipo utilizado na cláusula WHERE. Um par de linhas de T1 e T2 são correspondentes se a expressão da cláusula ON for avaliado como verdade para este par de linhas. USING é uma notação abreviada: recebe uma lista de nomes de colunas, separados por vírgula, que as tabelas juntadas devem possuir em comum, e forma a condição de junção especificando a igualdade de cada par destas colunas. Além disso, a saída de JOIN USING possui apenas uma coluna para cada par da igualdade de colunas da entrada, seguidas por todas as outras colunas de cada tabela. Portanto,USING (a, b, c) equivale a ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c), mas quando ON é utilizado existem duas colunas a, b e c no resultado, enquanto usando USING existe apenas uma de cada. Finalizando, NATURAL é uma forma abreviada de USING: gera uma lista USING formada pelas colunas cujos nomes aparecem nas duas tabelas de entrada. Assim como no USING, estas colunas aparecem somente uma vez na tabela de saída. Os tipos possíveis de junção qualificada são: INNER JOIN Para cada linha L1 de T1, a tabela juntada possui uma linha para cada linha de T2 que satisfaz a condição de junção com L1. LEFT OUTER JOIN Primeiro, é realizada uma junção interna. Depois, para cada linha de T1 que não satisfaz a condição de junção com nenhuma linha de T2, é adicionada uma linha juntada com valores nulos nas colunas de T2. Portanto, a tabela juntada possui, incondicionalmente, no mínimo uma linha para cada linha de T1. RIGHT OUTER JOIN Primeiro, é realizada uma junção interna. Depois, para cada linha de T2 que não satisfaz a condição de junção com nenhuma linha de T1, é adicionada uma linha juntada com valores nulos nas colunas de T1. É o oposto da junção esquerda: a tabela resultante possui, incondicionalmente, uma linha para cada linha de T2. FULL OUTER JOIN Primeiro, é realizada uma junção interna. Depois, para cada linha de T1 que não satisfaz a condição de junção com nenhuma linha de T2, é adicionada uma linha juntada com valores nulos nas colunas de T2. Também, para cada linha de T2 que não satisfaz a condição de junção com nenhuma linha de T1, é adicionada uma linha juntada com valores nulos nas colunas de T1. As junções de todos os tipos podem ser encadeadas ou aninhadas: tanto T1 como T2, ou ambas, podem ser tabelas juntadas. Podem colocados parênteses em torno das cláusulas JOIN para controlar a ordem de junção. Na ausência de parênteses, as cláusulas JOIN são aninhadas da esquerda para a direita. Para reunir tudo isto, vamos supor que temos as tabelas t1 num | nome ----+------ 1 | a 2 | b 3 | c e t2 num | valor ----+------- 1 | xxx 3 | yyy 5 | zzz e mostrar os resultados para vários tipos de junção: => SELECT * FROM t1 CROSS JOIN t2; num | nome | num | valor ----+------+-----+------- 1 | a | 1 | xxx 1 | a | 3 | yyy 1 | a | 5 | zzz 2 | b | 1 | xxx 2 | b | 3 | yyy 2 | b | 5 | zzz 3 | c | 1 | xxx 3 | c | 3 | yyy 3 | c | 5 | zzz (9 linhas) => SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num; num | nome | num | valor ----+------+-----+------- 1 | a | 1 | xxx 3 | c | 3 | yyy (2 linhas) => SELECT * FROM t1 INNER JOIN t2 USING (num); num | nome | valor ----+------+------- 1 | a | xxx 3 | c | yyy (2 linhas) => SELECT * FROM t1 NATURAL INNER JOIN t2; num | nome | valor ----+------+------- 1 | a | xxx 3 | c | yyy (2 linhas) => SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num; num | nome | num | valor ----+------+-----+------- 1 | a | 1 | xxx 2 | b | | 3 | c | 3 | yyy (3 linhas) => SELECT * FROM t1 LEFT JOIN t2 USING (num); num | nome | valor ----+------+------- 1 | a | xxx 2 | b | 3 | c | yyy (3 linhas) => SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num; num | nome | num | valor ----+------+-----+------- 1 | a | 1 | xxx 3 | c | 3 | yyy | | 5 | zzz (3 linhas) => SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num; num | nome | num | valor ----+------+-----+------- 1 | a | 1 | xxx 2 | b | | 3 | c | 3 | yyy | | 5 | zzz (4 linhas) A condição de junção especificada em ON também pode conter condições não relacionadas diretamente com a junção. Pode ser útil em algumas consultas, mas deve ser usado com cautela. Por exemplo: => SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.valor = 'xxx'; num | nome | num | valor ----+------+-----+------- 1 | a | 1 | xxx 2 | b | | 3 | c | | (3 linhas) 2.10.4 A cláusula WHERE A sintaxe da Cláusula WHERE é WHERE condição_de_pesquisa onde a condição_de_pesquisa é qualquer expressão de valor que retorne um valor do tipo booleano. Após o processamento da cláusula FROM ter sido feito, cada linha da tabela virtual derivada é verificada com relação à condição de pesquisa. Se o resultado da condição for verdade, a linha é mantida na tabela de saída, senão (ou seja, se o resultado for falso ou nulo) a linha é desprezada. Normalmente a condição de pesquisa faz referência a pelo menos uma coluna da tabela gerada pela cláusula FROM; embora isto não seja requerido, se não for assim a cláusula WHERE não terá utilidade. Nota: A condição de junção de uma junção interna pode ser escrita tanto na cláusula WHERE quanto na cláusula JOIN. Por exemplo, estas duas expressões de tabela são equivalentes: FROM a, b WHERE a.id = b.id AND b.val > 5 e FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5 ou talvez até mesmo FROM a NATURAL JOIN b WHERE b.val > 5 Qual destas formas deve ser utilizada é principalmente uma questão de estilo. A sintaxe do JOIN na cláusula FROM provavelmente não é muito portável para outros sistemas gerenciadores de banco de dados SQL. Para as junções externas não existe escolha em nenhum caso: devem ser feitas na cláusula FROM. A cláusula ON/USING da junção externa não é equivalente à condição WHERE, porque determina a adição de linhas (para as linhas de entrada sem correspondência) assim como a remoção de linhas do resultado final. Abaixo estão mostrados alguns exemplos de cláusulas WHERE: SELECT ... FROM fdt WHERE c1 > 5 SELECT ... FROM fdt WHERE c1 IN (1, 2, 3) SELECT ... FROM fdt WHERE c1 IN (SELECT c1 FROM t2) SELECT ... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) SELECT ... FROM fdt WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100 SELECT ... FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > fdt.c1) sendo que fdt é a tabela derivada da cláusula FROM. As linhas que não aderem à condição de pesquisa da cláusula WHERE são eliminadas de fdt. Deve ser observada a utilização de subconsultas escalares como expressões de valor. Assim como qualquer outra consulta, as subconsultas podem utilizar expressões de tabela complexas. Deve ser observado, também, como fdt é referenciada nas subconsultas. A qualificação de c1 como fdt.c1 somente é necessária se c1 também for o nome de uma coluna na tabela de entrada derivada da subconsulta. Entretanto, a qualificação do nome da coluna torna mais clara a consulta, mesmo quando não é necessária. Este exemplo mostra como o escopo do nome da coluna de uma consulta externa se estende às suas consultas internas. 2.10.5 Funções de agregação Como a maioria dos produtos de banco de dados relacional, o PostgreSQL suporta funções de agregação. Uma função de agregação computa um único resultado para várias linhas de entrada. Por exemplo, existem funções de agregação para contar (count), somar (sum), calcular a média (avg), o valor máximo (max) e o valor mínimo (min) para um conjunto de linhas. Para servir de exemplo, é possível encontrar a maior temperatura mínima ocorrida em qualquer lugar usando SELECT max(temp_min) FROM clima; max ----- 46(1 linha) Se for desejado saber a cidade (ou cidades) onde esta temperatura ocorreu pode-se tentar usar SELECT cidade FROM clima WHERE temp_min = max(temp_min); ERRADO mas não vai funcionar, porque a função de agregação max não pode ser usada na cláusula WHERE (Esta restrição existe porque 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). Entretanto, como é geralmente o caso, a consulta pode ser reformulada para obter o resultado pretendido, o que será feito por meio de uma subconsulta: SELECT cidade FROM clima WHERE temp_min = (SELECT max(temp_min) FROM clima); cidade --------------- São Francisco (1 linha) Isto está correto porque a subconsulta é uma ação independente, que calcula sua agregação em separado do que está acontecendo na consulta externa. 2.10.6 As cláusulas GROUP BY e HAVING Após passar pelo filtro WHERE, a tabela de entrada derivada pode estar sujeita ao agrupamento, utilizando a cláusula GROUP BY, e à eliminação de grupos de linhas, utilizando a cláusula HAVING. SELECT lista_de_seleção FROM ... [WHERE ...] GROUP BY referência_a_coluna_de_agrupamento [, referência_a_coluna_de_agrupamento] ... A Cláusula GROUP BY é utilizada para agrupar linhas da tabela que compartilham os mesmos valores em todas as colunas da lista. Em que ordem as colunas são listadas não faz diferença. O efeito é combinar cada conjunto de linhas que compartilham valores comuns em uma linha de grupo que representa todas as linhas do grupo. Isto é feito para eliminar redundância na saída, e/ou para calcular agregações aplicáveis a estes grupos. Por exemplo: => SELECT * FROM teste1; x | y --+--- a | 3 c | 2 b | 5 a | 1 (4 linhas) => SELECT x FROM teste1 GROUP BY x; x -- a b c (3 linhas) Na segunda consulta não poderia ser escrito SELECT * FROM teste1 GROUP BY x, porque não existe um valor único da coluna y que poderia ser associado com cada grupo. As colunas agrupadas podem ser referenciadas na lista de seleção, desde que possuam um valor único em cada grupo. De modo geral, se uma tabela for agrupada as colunas que não são usadas nos agrupamentos não podem ser referenciadas, exceto nas expressões de agregação. Um exemplo de expressão de agregação é: => SELECT x, sum(y) FROM teste1 GROUP BY x; x | sum --+----- a | 4 b | 5 c | 2 (3 linhas) Aqui sum() é a função de agregação que calcula um valor único para o grupo todo. Dica: Um agrupamento sem expressão de agregação computa, efetivamente, o conjunto de valores distintas na coluna. Também poderia ser obtido por meio da cláusula DISTINCT. Referencias DATE, C.J. Introdução a Sistemas de Banco de Dados. 8 ed. Rio de Janeiro: Campus, 2003. ElSMASRI, R.; NAVATHE, S. B. Sistemas de Banco de Dados. 4. ed. São Paulo Pearson, 2005. ELMASRI, R. e NAVATHE, S. B. Bancos de Dados. Rio de Janeiro: LTC, 2002. HEUSER. C.A. Projeto de Banco de Dados. 6a ED. Bookman, Porto Alegre, 2009. SILBERSCHATZ, A; KORTH, H. F.; S. Sudarschan. Sistema de banco de dados. Rio de Janeiro: Elsevier, 2006.
Compartilhar