Baixe o app para aproveitar ainda mais
Prévia do material em texto
Claudinete Vicente Borges Banco de Dados: Notas de aulas 2 SUMÁRIO I. INTRODUÇÃO............................................................................................................................................................ 4 1.1. CONCEITOS ............................................................................................................................................................... 4 1.2. OBJETIVOS ................................................................................................................................................................ 4 1.3. SGBD X SISTEMAS DE ARQUIVOS ............................................................................................................................ 4 1.4. ARQUITETURAS DE SISTEMAS DE BANCO DE DADOS ................................................................................................ 5 Sistemas Centralizados .............................................................................................................................................. 5 Sistema Cliente-Servidor ........................................................................................................................................... 5 Sistemas Paralelos ..................................................................................................................................................... 5 Sistemas distribuídos.................................................................................................................................................. 6 1.5. ABSTRAÇÃO DE DADOS............................................................................................................................................ 6 1.6. MODELOS DE BANCO DE DADOS............................................................................................................................... 7 Sistemas Relacionais.................................................................................................................................................. 7 Sistemas Hierárquicos ............................................................................................................................................... 7 Sistemas Rede............................................................................................................................................................. 8 1.7. INSTÂNCIAS E ESQUEMAS ........................................................................................................................................ 9 1.8. INDEPENDÊNCIA DE DADOS ...................................................................................................................................... 9 1.9. TAREFAS DE UM SGBD ............................................................................................................................................ 9 1.10. TAREFAS DE UM ADMINISTRADOR DE BANCO DE DADOS (DBA) ........................................................................... 9 1.11. ESTRUTURA GERAL DO SISTEMA ........................................................................................................................... 10 1.11.1. Linguagem de Definição de Dados (DDL) ................................................................................................. 11 1.11.2. Linguagem de Manipulação de Dados (DML) ........................................................................................... 11 2. PROJETO LÓGICO DE BANCO DE DADOS RELACIONAIS .......................................................................... 13 2.1. MODELO RELACIONAL............................................................................................................................................ 13 2.2. ESTRUTURA BÁSICA DOS BANCOS DE DADOS RELACIONAIS................................................................................... 14 2.2.1. Chaves ............................................................................................................................................................ 15 2.2.2. Definições....................................................................................................................................................... 16 2.2.3. Propriedades do Modelo Relacional ............................................................................................................. 17 2.3. TRADUÇÃO DO MODELO ER PARA O RELACIONAL ................................................................................................. 17 2.3.1 Diagrama Relacional...................................................................................................................................... 18 2.3.2. Relacionamentos 1 : 1 ................................................................................................................................... 19 2.3.3. Relacionamentos 1 : N................................................................................................................................... 20 2.3.4. Relacionamentos N : N.................................................................................................................................. 21 2.3.5. Auto-Relacionamento .................................................................................................................................... 22 2.3.6. Particionamento............................................................................................................................................. 23 2.3.7. Atributos Multivalorados ............................................................................................................................... 24 2.4. NORMALIZAÇÃO ..................................................................................................................................................... 25 2.4.1. Primeira Forma Normal (1FN) .................................................................................................................... 25 2.4.2. Segunda Forma Normal (2FN)..................................................................................................................... 25 2.4.3. Terceira Forma Normal (3FN) ..................................................................................................................... 26 3. LINGUAGENS DE CONSULTA .............................................................................................................................. 27 3.1. ÁLGEBRA RELACIONAL........................................................................................................................................... 27 3.1.1. Operações Fundamentais .............................................................................................................................. 28 Exercícios ................................................................................................................................................................. 31 3.1.2. Operações não fundamentais ........................................................................................................................ 32 Exercícios ................................................................................................................................................................. 34 3.2. SQL (STRUCTURED QUERY LANGUAGE) ............................................................................................................... 35 3.2.1. Estrutura Básica ............................................................................................................................................ 35 3.2.2. Linhas (tuplas) duplicadas ............................................................................................................................ 36 3.2.3. Predicados e ligações .....................................................................................................................................36 3.2.4. Operações de conjunto................................................................................................................................... 37 3.2.5 Ordenando a exibição de tuplas ..................................................................................................................... 38 3.2.6. Membros de conjuntos................................................................................................................................... 39 3.2.7. Variáveis tuplas (renomeação) ...................................................................................................................... 39 Claudinete Vicente Borges Banco de Dados: Notas de aulas 3 3.2.8. Comparação de conjuntos ............................................................................................................................. 39 3.2.9. Testando relações vazias................................................................................................................................ 40 Exercícios de SQL.................................................................................................................................................... 41 3.2.10. Funções agregadas ...................................................................................................................................... 42 Exercícios ................................................................................................................................................................. 43 3.2.11. Modificando o Banco de Dados .................................................................................................................. 45 Exercícios ................................................................................................................................................................. 48 3.2.12. Definição de dados....................................................................................................................................... 49 3.2.13. Tipos de Domínios em SQL ......................................................................................................................... 49 4. SEGURANÇA E INTEGRIDADE ............................................................................................................................ 50 4.1. SEGURANÇA............................................................................................................................................................ 50 4.1.1. Autorizações ................................................................................................................................................... 51 4.1.2. Especificação de Segurança em SQL............................................................................................................ 51 4.1.3. Criptografia.................................................................................................................................................... 52 4.1.3. Visões.............................................................................................................................................................. 52 4.2. INTEGRIDADE.......................................................................................................................................................... 54 4.2.1. Restrições de Domínio ................................................................................................................................... 54 4.2.2. Integridade Referencial ................................................................................................................................. 54 4.2.3. Triggers .......................................................................................................................................................... 56 Exercícios – SQL ..................................................................................................................................................... 56 5. GERENCIAMENTO DE TRANSAÇÕES ............................................................................................................... 57 5.1. INTRODUÇÃO .......................................................................................................................................................... 57 5.2. EXECUÇÕES SIMULTÂNEAS ..................................................................................................................................... 57 5.3. ESTADOS DE UMA TRANSAÇÃO ............................................................................................................................... 59 5.4. DEFININDO TRANSAÇÕES EM SQL .......................................................................................................................... 60 6. PROCEDIMENTOS ARMAZENADOS (STORED PROCEDURE) .................................................................... 61 6.1. GATILHOS (TIPO ESPECIAL DE PROCEDIMENTO ARMAZENADO) ............................................................................... 62 6.2. CURSORES .............................................................................................................................................................. 64 7. SISTEMAS DE RECUPERAÇÃO............................................................................................................................ 66 7.1. CLASSIFICAÇÃO DE FALHAS .................................................................................................................................... 66 7.2. CORREÇÃO E ATOMICIDADE.................................................................................................................................... 66 7.3. RECUPERAÇÃO BASEADA EM LOG ......................................................................................................................... 67 7.3.1. Modificação do banco de dados adiada ........................................................................................................ 67 7.3.2. Modificação imediata no banco de dados ..................................................................................................... 69 7.3.3. Pontos de verificação (checkpoints) .............................................................................................................. 70 7.4. FALHAS COM PERDA DE MEMÓRIA NÃO-VOLÁTIL .................................................................................................... 70 8. CONTROLE DE CONCORRÊNCIA....................................................................................................................... 71 8.1. ESCALONAMENTOS ................................................................................................................................................. 71 8.1.1. Escalonamentos Seriais e Não-Seriais.......................................................................................................... 71 8.2. PROTOCOLOS BASEADOS EM BLOQUEIOS ................................................................................................................ 73 8.2.1. Bloqueios........................................................................................................................................................ 73 Claudinete Vicente Borges Banco de Dados: Notas de aulas 4 I. Introdução 1.1. Conceitos Um banco de dados, muitas vezes também chamado de base de dados, é um conjunto de arquivos estruturados de forma a facilitar o acesso a conjuntos de dados que descrevem determinadas entidades do mundo. Por exemplo, um banco de dados de funcionários de uma firma contém ao menos três arquivos: dados pessoais (nome, endereço, dados de cocumentos), dados funcionais (cargo, data de admissão, etc) e dados para pagamento (salário base, faixas, etc). A medida que o volume e os tipos de dados armazenados aumentam, é preciso utilizarsoftwares especiais para gerenciar os dados, os chamados SGBDs (Sistemas de Gerenciamento de Banco de Dados). Um SGBD é um software de caráter geral para a manipulação eficiente de grandes coleções de informações estruturadas e armazenadas de uma forma consistente e integrada. Tais sistemas incluem módulos para consulta, atualização e as interfaces entre o sistema e o usuário. Podemos definir então que um SGBD é constituído por um conjunto de dados associados a um conjunto de programas para acesso a esses dados [KORTH,2006]. 1.2. Objetivos Disponibilizar dados integrados para uma grande variedade de usuários e aplicações através de interfaces amigáveis; Garantir a Privacidade dos dados através de medidas de segurança dentro do sistema (como visões, permissões, senhas de acesso); Permitir compartilhamento dos dados de forma organizada, mediando a comunicação entre aplicações e banco de dados e administrando acessos concorrentes; Possibilitar independência dos dados, poupando ao usuário a necessidade de conhecer detalhes de implementação interna, organização de arquivos e estruturas de armazenamento. 1.3. SGBD x Sistemas de Arquivos Sistemas de processamento de arquivos caracterizam-se por uma série de registros guardados em diversos arquivos e uma série de programas aplicativos para extrair e adicionar registros nos arquivos apropriados. � Desvantagens deste sistema (arquivos) Redundância e inconsistência de dados; Dificuldade no acesso aos dados; Isolamento de dados; Problemas de atomicidade; Anomalias de acesso concorrente; Problemas de segurança; Problemas de integridade; Claudinete Vicente Borges Banco de Dados: Notas de aulas 5 1.4. Arquiteturas de Sistemas de Banco de Dados A arquitetura de um sistema de banco de dados está fortemente relacionada com as características do sistema operacional sobre o qual o SGBD estará sendo executado. Sistemas Centralizados Sistemas centralizados são aqueles executados sobre um único sistema operacional, não interagindo com outros sistemas. Tais sistemas podem ter a envergadura de um sistema de banco de dados de um só usuário executado em um computador pessoal ou em sistemas de alto desempenho, denominados de grande porte. Sistema Cliente-Servidor Como os computadores pessoais tem se tornado mais rápidos, mais poderosos e baratos, há uma tendência de seu uso nos sistemas centralizados. Terminais conectados a sistemas centralizados estão sendo substituídos por computadores pessoais. Como resultado, sistemas centralizados atualmente agem como sistemas servidores que atendem a solicitações de sistemas clientes. As funcionalidades de um banco de dados podem ser superficialmente divididas em duas categorias – front-end e back-end . O back-end gerencia as estruturas de acesso, desenvolvimento e otimização de consultas, controle de concorrência e recuperação. O front-end consiste em ferramentas como formulários, gerador de relatórios e recursos de interface gráfica. A interface entre o front-end e o back-end é feita através de SQL ou de um programa de aplicação. Sistemas Paralelos Sistemas paralelos imprimem velocidade ao processamento e à CPU por meio do uso em paralelo de CPU’s e discos. No processamento paralelo, muitas operações são realizadas ao mesmo tempo, ao contrário do processamento serial, no qual os passos do processamento são sucessivos. Um equipamento paralelo de granulação-grossa consiste em poucos e poderosos processadores (maioria dos servidores atuais) enquanto que um paralelismo intensivo ou de granulação fina usa milhares de pequenos processadores. Computadores paralelos com centenas de processadores já estão disponíveis comercialmente. As duas principais formas de avaliar o desempenho de um sistema de banco de dados são através do throughput e tempo de resposta. A primeira diz respeito ao número de tarefas que podem ser executadas em um dado intervalo de tempo. Um sistema que processa um grande número de pequenas transações pode aumentar o throughput por meio do processamento de diversas transações em paralelo. O tempo de resposta diz respeito ao tempo total que o sistema pode levar para executar uma única tarefa. Um sistema que processa um grande número de pequenas transações pode aumentar o throughput por meio do processamento de diversas transações em paralelo. Um sistema que processa um grande volume de transações pode reduzir o tempo de resposta por meio de processamento em paralelo. Claudinete Vicente Borges Banco de Dados: Notas de aulas 6 Sistemas distribuídos Em um sistema distribuido o banco de dados é armazenado em diversos computadores. Os computadores de um sistema de banco de dados distribuídos comunicam-se com outros por intermédio de vários meios de comunicações, como redes de alta velocidade ou linhas telefônicas. As principais diferenças entre os banco de dados paralelos sem compartilhamento e os bancos de dados distribuídos são que, nos bancos de dados distribuídos, há a distribuição física geográfica, administração separada e uma intercomunicação menor. Outra importante diferença é que, nos sistemas distribuídos, distinguimos transações locais (acessa um único computador) de globais (envolve mais de um computador). Há diversas razões para a utilização de sistemas de banco de dados distribuídos, dentre as quais podemos citar: Compartilhamento dos dados (usuários de um local podem ter acesso a dados residentes em outros – Ex. Bancos), autonomia (Cada local administra seus próprios dados) e disponibilidade (Se porventura uma SGBD sai do ar, os demais podem continuar em operação). por um outro lado, existem algumas desvantagens relacionadas ao seu uso, dentre as quais podemos citar: Custo de desenvolvimento de software, maior possibilidades de bugs e aumento do processamento e overhead. 1.5. Abstração De Dados Os SGBD’s provêem aos usuários uma visão abstrata dos dados. I. E., os usuários não precisam saber como os dados são armazenados e mantidos para poder usá-los. Níveis de abstração de dados: De Visão: Diz respeito a forma como os Dados são vistos pelos usuários (individualmente). Um determinado usuário, tanto pode ser um programador de aplicações, como um usuário final. O DBA é um caso especial importante. Ao contrário dos usuários comuns, o DBA terá de se interessar pelos níveis conceitual e físico também. Lógico : É a visão global do grupo de usuários. A visão lógica é a representação de todo o conteúdo de informações do banco de dados, também um tanto abstrato quando comparada à forma como os dados são fisicamente armazenados, que também pode ser bem diferente da maneira como os dados são vistos por qualquer usuário em particular. A grosso modo, podemos dizer que a visão lógica é a visão dos dados “como realmente são”, e não como os usuários são forçados a vê-los devido às restrições de linguagem ou hardware. Físico: Diz respeito a forma como os dados estão armazenados fisicamente. Visão 1 Visão 2 Visão n Nível Lógico Nível Físico ... Claudinete Vicente Borges Banco de Dados: Notas de aulas 7 1.6. Modelos de banco de dados Sistemas Relacionais O modelo relacional usa um conjunto de tabelas para representar tanto os dados como a relação entre eles. Cada tabela possui múltiplas colunas, conforme mostra abaixo as tabelas: Cliente e Conta. Nome Rua Cidade Número_Conta Ana Avenida Vitória Vitória 900 Maria João da Cruz Vitória 647 José Henrique Moscoso Vila Velha 801 João João Santos Neves Aracruz 556 Tabela 1.1: A relação Cliente Nome_ Agência número saldo Centro 900 62,00 Praia do Canto 556 6589,00 Bento Ferreira 647 3245,20 Jucutuquara 801 2258,98 Tabela 1.2: A relação Conta Sistemas Hierárquicos Um banco de dados hierárquico compõe-se de um conjunto ordenado de árvores, mais precisamente, de um conjunto ordenado de ocorrênciasmúltiplas de um tipo único de árvore. O tipo árvore compõe-se de um único tipo de registro “raiz”, juntamente com um conjunto ordenado de zero ou mais (nível inferior) tipos de subárvores dependentes. Um tipo de subárvore, por sua vez, também se compõe de um único tipo de registro. A associação entre tipos de registros segue uma hierarquia estabelecida através de diversos níveis . No primeiro nível, ou superior, situa-se o tipo de registro “Raiz” . Subordinado a ele, em nível 2, uma série de outros tipos de registros em nível 2. A cada tipo de registro em nível 2 subordina-se um outro conjunto de tipos de registros. A própria estrutura hierárquica define as suas rotas de acesso, facilitando, portanto, a manutenção do banco de dados. É importante notar que um determinado tipo de registro A, num determinado nível K, possui ligação com um e somente um tipo de registro B, de nível K-1 (superior). Nestas condições A é dito registro PAI de B, que por sua vez é registro FILHO de A . No entanto, um tipo de registro A pode estar ligado a diversos filhos ao nível de B. Todas as ocorrências de um dado tipo de filho que compartilham uma ocorrência de pai comum são chamadas de Gêmeas. Claudinete Vicente Borges Banco de Dados: Notas de aulas 8 Ana Maria José Vitória José Henrique Rosetti Vitória Maria João da Cruz Vitória 900 62 556 6589 647 3245 647 3245 801 2258 Figura 1.3: Exemplo de “Modelo Hierárquico”. Sistemas Rede Um banco de dados em rede consiste em uma coleção de registros que são concatenados uns aos outros por meio de ligações. Um registro é em muitos aspectos similar a uma entidade no modelo entidade-relacionamento. Uma ligação é uma associação entre precisamente dois registros. Assim, uma ligação pode ser vista como uma forma restrita (binária) de relacionamento no modelo ER. Tanto o Modelo Rede como o Modelo Hierárquico podem ser considerados como estruturas de dados a nível lógico mais próximo do nível físico. Devido a esta proximidade ao nível físico, as estruturas de dados rede e hierárquica exibem as rotas lógicas de acesso de dados de forma acentuada, possibilitando a localização lógica de um determinado registro no banco de dados. O Modelo Relacional, quando comparado com a Estrutura Rede e Hierárquica é mais orientado para modelagem do que como modelo com rotas de acesso, muito embora, podemos considerar as diversas redundâncias existentes em diversas tabelas como sendo uma forma de rota de acesso. O Modelo Rede utiliza como elemento básico de dados a ocorrência de registro. Um conjunto de ocorrência de registro de um mesmo tipo determina um tipo de registro. Um conjunto de tipos de registros relacionados entre si, através de referências especiais, forma uma estrutura de dados em rede. As referências especiais são conhecidas como LINKS (ligações), que por sua vez podem ser implementadas sob a forma de ponteiros. As referências estão normalmente inseridas junto com as ocorrências de registro; assim, todo o acesso a um próximo registro utiliza o ponteiro inserido no registro corrente disponível. Ana Maria José Vitória 900 62 Maria João da Cruz Vitória 556 6589 647 3245 José Henrique Rosetti Vitória 801 2258 Figura 1.4: Exemplo de “Modelo Rede” Claudinete Vicente Borges Banco de Dados: Notas de aulas 9 1.7. Instâncias e Esquemas Um banco de dados muda ao longo do tempo por meio das informações que nele são inseridas ou excluídas. O conjunto de informações contidas em determinado banco de dados, em um dado momento, é chamado instância do banco de dados. O projeto geral do banco de dados é chamado esquema. 1.8. Independência de Dados Pode ser definida como a “Imunidade das aplicações à estrutura de armazenamento e à estratégia de acesso”, ou seja, diminuir as alterações de programas devido a modificações nos dados do banco de dados. As modificações nos dados não se referem às atualizações temporais dos valores dos itens de dados e sim quanto ao tamanho, formato, precisão, índices, etc... , de itens de dados. Existem dois níveis de Independência de Dados: a) Independência Física de dados: É a habilidade de modificar o esquema físico sem a necessidade de reescrever os programas aplicativos. As modificações no nível físico são ocasionalmente necessárias para melhorar o desempenho. b) Independência Lógica de dados: É a habilidade de modificar o esquema conceitual sem a necessidade de reescrever os programas aplicativos. As modificações no nível conceitual são necessárias quando a estrutura lógica do banco de dados é alterada. Qual abordagem é mais fácil de ser alcançada? Normalmente, a independência física de dados é mais fácil de ser alcançada do que a lógica. 1.9. Tarefas de um SGBD Interação com o gerenciador de arquivos; Cumprimento da integridade; Cumprimento da segurança; Cópias de segurança e recuperação; Controle de concorrência. 1.10. Tarefas de um Administrador de Banco de Dados (DBA) O Administrador do Banco de Dados (DBA) é a pessoa (ou grupo de pessoas) responsável pelo controle do banco de dados. Dentre essas funções citamos: a) Decidir a Estrutura de Armazenamento e a Estratégia de Acesso: Estruturas apropriadas de armazenamento e métodos de acesso são criadas escrevendo-se um conjunto de definições que são traduzidas pelo compilador de estruturas de dados e de linguagem de definição (DDL). Claudinete Vicente Borges Banco de Dados: Notas de aulas 10 b) Definir os controles de segurança e integridade: Pode ser considerado parte do esquema conceitual. A DDL conceitual incluirá os recursos para a especificação de tais controles. c) Definir a estratégia de reserva e recuperação: É de suma importância, na eventualidade de danos à parte do banco de dados causados por erro humano ou por falha de hardware, fazer retornar os dados envolvidos com um mínimo de demora e com as menores conseqüências ao restante do sistema. O DBA deve definir e implementar uma estratégia de recuperação apropriada envolvendo, por exemplo, o descarregamento periódico do banco de dados na memória auxiliar de armazenamento e procedimentos para recarregá- lo, quando necessário. d) Monitorar o desempenho e atender as necessidades de modificações: O DBA deve organizar o sistema de tal maneira que obtenha “o melhor desempenho para a empresa”; e efetuar os ajustes adequados quanto às necessidades de modificações. 1.11. Estrutura geral do sistema O sistema de banco de dados está dividido em módulos específicos, de modo a atender a todas as suas funções, onde algumas delas são fornecidas pelo sistema operacional. Os componentes de processamentos de consultas incluem: Compilador DML: traduz comandos DML da linguagem de consulta em instruções de baixo nível, inteligíveis ao componente de execução de consultas; Pré-compilador para comandos DML: inseridos em programas de aplicação, que convertem comandos DML em chamadas de procedimentos normais da linguagem hospedeira. Interpretador DDL: interpreta os comandos DDL e registra-os em um conjunto de tabelas que contêm metadados, “dados sobre dados”; Componentes para o tratamento de consultas: executam instruções de baixo nível geradas pelo compilador DML. Os componentes para administração do armazenamento de dados incluem: Gerenciamento de autorizações e integridade: testam o cumprimento das regras de integridade e a permissão ao usuário no acesso aos dados; Gerenciamento de transações: garante que o banco de dados permanecerá em estado consistente a despeito de falhas no sistema e que as transações concorrentes serão executadas sem conflitos em seus procedimentos; Administração de arquivos: gerencia a alocação de espaço no armazenamento em disco e as estruturas de dados usadas para representar estasinformações armazenadas em disco; Administração do buffer: intermediar os dados entre o disco e a memória principal e decide quais dados colocar em cache; Diversas estruturas de dados são requeridas como parte da implementação do sistema físico, incluído: a) Arquivo de Dados: Armazena o banco de dados. b) Dicionário de Dados: Armazena informações sobre os dados do banco de dados; c) Índices: Permite o acesso mais rápido aos dados. Claudinete Vicente Borges Banco de Dados: Notas de aulas 11 d) Estatísticas: Armazenam informações sobre o banco de dados e é usado pelo seletor de estratégias. 1.11.1. Linguagem de Definição de Dados (DDL) Contém a especificação dos esquemas de banco. O resultado da compilação dos parâmetros DDLs é armazenado em um conjunto de tabelas que constituem um arquivo especial chamado dicionário de dados. Um dicionário de dados é um arquivo de metadados. Principais comandos DDL: CREATE TABLE; ALTER TABLE; DROP TABLE; CREATE INDEX; ALTER INDEX; DROP INDEX; 1.11.2. Linguagem de Manipulação de Dados (DML) A linguagem de manipulação de dados (DML) é a linguagem que viabiliza o acesso ou a manipulação dos dados de forma compatível ao modelo de dados apropriado. São responsáveis pela: Recuperação da informação armazenada no banco de dados (SELECT); Inserção de novos dados nos bancos de dados (INSERT); Eliminação de dados nos bancos de dados (DELETE); Modificação de dados armazenados no banco de dados (UPDATE); Dividem-se basicamente em dois tipos: DMLs Procedurais: exigem que o usuário especifique quais dados são necessários, e como obtê-los; DMLs Não Procedurais: exige que o usuário especifique quais dados são necessários, sem especificar como obtê-los. Claudinete Vicente Borges Banco de Dados: Notas de aulas 12 Figura 1.5: Estrutura geral de um Sistema de Banco de Dados. Claudinete Vicente Borges Banco de Dados: Notas de aulas 13 2. Projeto Lógico de Banco de Dados Relacionais Um aspecto fundamental da fase de projeto consiste em estabelecer de que forma serão armazenados os dados do sistema. Em função da plataforma de implementação, diferentes soluções de projeto devem ser adotadas. Isto é, se o software tiver de ser implementado em um banco de dados hierárquico, por exemplo, um modelo hierárquico deve ser produzido, adequando a modelagem conceitual de dados (ER ou diagrama de classes) a esta plataforma de implementação. Atualmente, a plataforma de implementação para armazenamento de dados mais difundida é a dos Bancos de Dados Relacionais e, portanto, neste texto, discutiremos apenas o projeto de bancos de dados para este modelo, o relacional. 2.1. Modelo Relacional Em um modelo de dados relacional, os conjuntos de dados são representados por tabelas de valores. Cada tabela, denominada de relação, é bidimensional, sendo organizada em linhas e colunas. Este modelo está fortemente baseado na teoria matemática sobre relações, daí o nome relacional. Exemplo: Claudinete Vicente Borges Banco de Dados: Notas de aulas 14 2.2. Estrutura Básica dos Bancos de Dados Relacionais Um banco de dados relacional consiste em uma coleção de tabelas, cada uma das quais com um nome único. Uma linha em uma tabela representa um relacionamento entre um conjunto de valores. Uma vez que essa tabela é uma coleção de tais relacionamentos, há uma estreita correspondência entre o conceito de tabela e o conceito matemático de relação, a partir das quais se origina o nome desse modelo de dados. Considere a tabela Conta, mostrada abaixo. Ela possui três colunas: Nome_ Agência, numero e saldo. Seguindo a terminologia do modelo relacional, tratamos os nomes dessas colunas como atributos. Para cada atributo, há um conjunto de valores permitidos, chamado domínio do atributo em questão. Para o atributo Nome_Agencia, por exemplo, o domínio é o conjunto de todos os nomes de agências. Suponha que D1 denote esse conjunto, D2 denota o conjunto de todos os números de contas e D3, o conjunto de todos os saldos. Qualquer linha da tabela Conta consiste necessariamente de uma 3-tupla (v1, v2, v3), em que v1 é o nome de uma agência (isto é, v1 está no domínio D1), v2 é um número de conta e assim por diante. Em geral, a conta é um conjunto de D1 x D2 x D3. Matematicamente, define-se uma relação como um subconjunto de um produto cartesiano de uma lista de domínios. Essa definição corresponde quase exatamente à definição de uma tabela. A única diferença é que designamos nomes aos atributos, ao passo que, matematicamente se usam apenas "nomes" numéricos. Como as tabelas em essência são relações, podemos usar os termos matemáticos relação e tupla no lugar de tabela e linhas. Nome_ Agência número saldo Centro 900 62,00 Praia do Canto 556 6589,00 Bento Ferreira 647 3245,20 Jucutuquara 801 2258,98 Figura 2.1: A Relação Conta É possível, para alguns atributos, possuir o mesmo domínio. Por exemplo, suponha que tenhamos a relação cliente, possuindo três atributos: nome_cliente, rua_cliente e cidade; e a relação empregado, que inclui o atributo nome_empregado. É possível que os atributos nome_cliente e nome_empregado possuam os mesmos domínios: o conjunto formado pelos nomes de pessoas. Os domínios de saldo e nome_agencia certamente são distintos. Entretanto, talvez não esteja tão claro o fato de nome_cliente e nome_agência não possuírem o mesmo domínio. No nível físico, ambos os nomes, de agência e cliente, são compostos por sequências de caracteres. Entretanto, no nível lógico, podemos considerar os domínios de nome_cliente e nome_agência distintos. Um valor de domínio que pertence a qualquer domínio possível é o valor nulo, que indica que um valor é desconhecido ou não existe. Por exemplo, supomos que incluamos o atributo numero_telefone na relação cliente. Pode ser que um cliente não possua telefone ou mesmo o seu número pode ser desconhecido. Claudinete Vicente Borges Banco de Dados: Notas de aulas 15 2.2.1. Chaves É importante especificar como as entidades dentro de um dado conjunto de entidades e os relacionamentos dentro de um conjunto de relacionamentos podem ser identificados. Conceitualmente, entidades e relacionamentos individuais são distintos, entretanto, na perspectiva do banco de dados, a diferença entre ambos deve ser estabelecida em termos de seus atributos. O conceito de chaves nos permite fazer tais distinções. Uma superchave é um conjunto de um ou mais atributos que, tomados coletivamente permitem identificar de maneira unívoca uma entidade em um conjunto de entidades. Por exemplo, os atributos (nome,numero_conta) e (nome,CPF) do conjunto de entidades cliente são suficientes para distinguir cada elemento do conjunto de entidades, podendo ser considerados como superchaves. Da mesma forma, podemos considerar o atributo CPF como superchave de cliente. O atributo nome não é superchave de cliente, pois algumas pessoas podem ter o mesmo nome. Nosso interesse maior é por superchaves para as quais nenhum subconjunto possa ser uma superchave. Essas chaves são chamadas de chaves candidatas. Das superchaves mencionadas acima, somente (nome,CPF) não poderia ser considerada uma chave candidata, visto que o CPF,sozinho, já o é. Podemos usar o termo chave primária para caracterizar a chave candidata que é escolhida pelo projetista do banco de dados como de significado principal para a identificação de entidades dentro de um conjunto de entidades. Quaisquer duas entidades individuais em um conjunto de entidades não podem ter, simultaneamente, mesmos valores em seus atributos-chave. Eventualmente, pode-se adotar uma solução de projeto em que a chave primária não foi escolhida entre as chaves canditadas e sim, um atributo ou combinação destes que não dizem respeito ao negócio em si. Particulamente, prefiro adotar esta solução em relação a primeiraapresentada. É importante lembrar a todos que o negócio muda e com isso, os valores dos atributos também. Uma alteração feita em valores de chaves primárias desencadearão em propagação para inúmeras tabelas relacionadas, o que pode ser muito trabalhoso. Em SGBD’s, apenas os conceitos de chaves primárias e chaves candidatas são implementados! Nome Rua Cidade Número_Conta Ana Avenida Vitória Vitória 900 Maria João da Cruz Vitória 647 José Henrique Moscoso Vila Velha 801 João João Santos Neves Aracruz 556 Figura 2.2: A Relação Cliente Claudinete Vicente Borges Banco de Dados: Notas de aulas 16 2.2.2. Definições Relação: Tabela de valores bidimensional organizada em linhas e colunas. Representa um conjunto de entidades do Modelo E/R ou uma classe em um Diagrama de Classes. Matrícula Nome CPF Endereço Dt-Nasc Dt-Adm 0111 Marcos 17345687691 Vila Velha 11/04/66 20/08/86 0208 Rita 56935101129 Vila Velha 21/02/64 18/03/90 0789 Mônica 81176628911 Vitória 01/11/70 15/07/92 1589 Márcia 91125769120 Serra 20/10/80 01/02/98 ... Figura 2.3: A Relação Funcionários Grau da Relação: Número de colunas da tabela. Ex: 6 Linha (Tupla): Representa uma entidade do conjunto de entidades, ou um objeto de uma classe. Ex: Funcionário 0789 do conjunto de Funcionários. Colunas: Representam os vários atributos do conjunto de entidades ou classe. Ex: Matrícula, Nome, CPF, Endereço, Dt-Nasc, Dt-Adm. Célula: Item de dado elementar da linha i, coluna j. Ex: Vitória (linha 3, coluna 4); 01/02/98 (linha 4, coluna 6) Chave Primária: Atributo ou combinação de atributos que possuem a propriedade de identificar de forma única uma linha da tabela. Corresponde a um atributo determinante do Modelo Conceitual. Cada relação só poderá ter uma chave primária. Ex: Matrícula Chaves Candidatas: Ocorrem quando existe, em uma relação, mais de uma combinação de atributos possuindo a propriedade de identificação única. Não há limite de chaves candidatas para uma relação. Ex: Matrícula, CPF Chave Estrangeira: Ocorre quando um atributo de uma relação for chave primária em outra relação. Ligações: Representam os relacionamentos do Modelo E/R ou Diagrama de Classes. A ligação entre duas relações é feita, em geral, transportando-se a chave de uma relação para outra (item transposto). Claudinete Vicente Borges Banco de Dados: Notas de aulas 17 Figura 2.4: Chaves Estrangeiras A chave da tabela Departamentos foi transposta para a tabela Funcionários. Neste caso, “Departamentos” é denominada relação origem e “Funcionários” relação destino. 2.2.3. Propriedades do Modelo Relacional Nenhum campo componente de uma chave primária pode ser nulo; Cada célula de uma relação pode ser vazia (exceto de uma chave primária), ou ao contrário, conter no máximo um único valor; A ordem das linhas é irrelevante; Não há duas linhas iguais; Cada coluna tem um nome e colunas distintas devem ter nomes distintos; Usando-se os nomes para se fazer referência às colunas, a ordem destas torna-se irrelevante; Cada relação recebe um nome próprio distinto do nome de qualquer outra relação da base de dados; Os valores de uma coluna são retirados todos de um mesmo conjunto, denominado domínio da coluna; Duas ou mais colunas distintas podem ser definidas sobre um mesmo domínio; Um campo que seja uma chave estrangeira ou um item transposto só pode assumir valor nulo ou um valor para o qual exista um registro na tabela onde ela é chave primária. 2.3. Tradução do Modelo ER para o Relacional O objetivo deste tópico é apresentar como se procede na elaboração do projeto lógico de um banco de dados relacional. Para isso, será apresentado como proceder a transformação do modelo conceitual, que é um modelo mais abstrato, para um modelo que contém mais detalhes de implementação. Neste material, somente o projeto de banco de dados relacional será tratato. Para outros modelos de SGBD’s, por exemplo, para o orientado a objetos, outras regras de tradução são necessárias. Claudinete Vicente Borges Banco de Dados: Notas de aulas 18 2.3.1 Diagrama Relacional O Diagrama Relacional é uma representação gráfica das ligações entre tabelas do modelo relacional. Nele são representados os seguintes elementos: As relações (tabelas) provenientes de conjuntos de entidades e dos agregados do Modelo E/R. São representadas por retângulos, com uma referência à chave primária da tabela em cima. As ligações, que derivam dos relacionamentos, são representadas por linhas contínuas, associadas aos símbolos abaixo: No caso de transposição de chave, iremos representá-la em cima do retângulo desta relação com um subscrito “t”. Atributos não são representados nos diagramas, mas sim em um dicionário de tabelas do modelo relacional. Nos itens que se seguem, serão discutidos os mapeamentos de um Modelo de Entidades e Relacionamentos no paradigma para um Modelo Relacional. Claudinete Vicente Borges Banco de Dados: Notas de aulas 19 2.3.2. Relacionamentos 1 : 1 Se B for total em R (todo B está associado a um A), é melhor colocar a chave de A(#A) em B. Caso contrário, é melhor colocar a chave de B(#B) em A. Exemplo: Todo departamento tem um empregado gerente, mas nem todo empregado gerencia um departamento. Observação: Se ambos forem totais pode-se trabalhar com uma única tabela escolhendo uma das chaves #A ou #B. Claudinete Vicente Borges Banco de Dados: Notas de aulas 20 2.3.3. Relacionamentos 1 : N Transpor a chave do conjunto de entidades de cardinalidade máxima N para a tabela que representa o conjunto de entidades cuja cardinalidade máxima é 1. Um A pode estar associado a vários B’s, mas um B só pode estar associado a um A, logo transpor a chave primária de A para B. Claudinete Vicente Borges Banco de Dados: Notas de aulas 21 2.3.4. Relacionamentos N : N Cria-se uma terceira tabela que tenha a chave das duas entidades que participam do relacionamento N:N (agregado). Se existirem atributos do relacionamento, estes deverão ser colocados na nova tabela. Claudinete Vicente Borges Banco de Dados: Notas de aulas 22 2.3.5. Auto-Relacionamento Auto-Relacionamento 1:N Criar uma chave #A' a ser colocada na própria tabela que representa A. Transpomos a chave do empregado-gerente para o registro do empregado gerenciado, mostrando, assim, que um empregado pode gerenciar vários outros, mas é gerenciado por somente um empregado. Claudinete Vicente Borges Banco de Dados: Notas de aulas 23 Auto-Relacionamento N:N Assim como feito para qualquer relacionamento N:N, devemos criar uma tabela de ligação com as chaves #A e #A', transformando a ligação N:N em duas ligações 1:N. 2.3.6. Particionamento Cria-se uma tabela para o supertipo e tantas tabelas quantos forem os subtipos, todos com a mesma chave. Caso não haja no modelo conceitual um atributo determinante no supertipo, uma chave primária deve ser criada para fazer a amarração com os subtipos. Claudinete Vicente Borges Banco de Dados: Notas de aulas 24 2.3.7. Atributos Multivalorados Segundo a propriedade do modelo relacional que nos diz que: Cada célula de uma relação pode ser vazia (exceto de uma chave primária), ou ao contrário, conter no máximo um único valor"; não podemos representar atributos multivalorados como uma única coluna da tabela. Soluções: 1. Criar tantas colunas quantas necessárias para representar o atributo. Esta solução, contudo, pode, em muitos casos, não ser eficiente segundo uma perspectiva de armazenamento de dados. 2. Criar uma tabela em separado, como mostra a seguir. Claudinete Vicente Borges Banco de Dados: Notas de aulas 25 2.4. Normalização De uma maneira genérica,uma relação pode conter atributos cujos domínios sejam também relações: dizemos então que a relação esta na forma “não normalizada” e os domínios são do tipo “não simples”. Para a devida utilização de operadores da teoria de conjuntos é necessário que a relação não- normalizada seja transformada numa forma onde os atributos só contenham domínios simples. O processo de transformação de relações não normalizadas às formas melhores de expressão é conhecido como “normalização”. Normalização refere-se ao processo de eliminar informações redundantes, além de outros problemas no projeto de um banco de dados. 2.4.1. Primeira Forma Normal (1FN) Um banco de dados está na Primeira Forma Normal, quando não existem dados repetidos em nenhuma das linhas da tabela. Segundo DATE: “Uma relação R existe na primeira forma normal (1FN) se, e somente se, todos os domínios subjacentes contiverem apenas valores atômicos”. Exemplo: Funcionário Matricula (chave) Nome Endereço Telefones Solução: Funcionário Matricula (chave) Nome Endereço Telefones Matricula / chave composta \ Telefone \ chave composta / 2.4.2. Segunda Forma Normal (2FN) Quando uma tabela possui uma chave composta, suas colunas devem ser dependentes de toda a chave, e não de apenas uma parte dela. Assim, a tabela estará na Segunda Forma Normal. Segundo DATE: “Uma relação R existe na segunda forma normal (2FN) se, e somente se, estiver na (1FN) e todos os atributos não chaves forem dependentes da chave principal”. Exemplo: Considere a seguinte tabela, na qual a chave principal é uma combinação das colunas Numero da Fita e Numero do Ator: Fita Numero da Fita / chave composta \ Numero do Ator \ chave composta / Título Nome do Ator Claudinete Vicente Borges Banco de Dados: Notas de aulas 26 Título depende somente do Número da Fita, e Nome do Ator, depende somente do Número do Ator. Quase sempre, a solução é dividir as colunas em duas tabelas e criar uma terceira tabela que correlacione as linhas das duas tabelas: Fita Numero da Fita (chave simples) Título Ator Numero do Ator (chave simples) Nome do Ator Ator/Fita Numero da Fita / chave composta \ Numero do Ator \ chave composta / 2.4.3. Terceira Forma Normal (3FN) Uma tabela encontra-se na Terceira Forma Normal se todas as suas colunas dependerem de toda a chave principal, e não houver interdependência entre as colunas que não tem chave da tabela. Segundo DATE: “Uma relação R existe na terceira forma normal (3FN) se, e somente se, estiver na (2FN) e todos os atributos não chave, forem intransitivamente dependentes da chave principal”. Exemplo: Fita Numero da Fita (chave) Título Categoria Preço Suponha que a loja determine o preço das fitas por categoria: filmes de Terror, Drama e Comédia custam R$2,00; Musicais por R$2,50 e Lançamentos por R$3,00. Nesse caso, a coluna Preço dependeria não só da coluna Numero da Fita, mas também da coluna Categoria. Esta tabela não está na Terceira Forma Normal. A solução seria criar uma tabela adicional para Preços: Fita Numero da Fita (chave) Título Categoria Preço Categoria (chave) Preço Claudinete Vicente Borges Banco de Dados: Notas de aulas 27 3. Linguagens de Consulta Uma linguagem de consulta é uma linguagem na qual um usuário requisita informações do banco de dados. São classificadas como procedurais e não-procedurais. Numa linguagem procedural, um usuário instrui o sistema para executar uma seqüência de operações no banco de dados a fim de computar o resultado desejado. Numa linguagem não-procedural, o usuário descreve a informação desejada sem fornecer um procedimento específico para obter tal informação. Inicialmente, devemos referir-nos apenas às consultas. Uma linguagem completa de manipulação inclui não apenas uma linguagem de consulta, mas também uma linguagem para modificação do banco de dados. 3.1. Álgebra Relacional A álgebra relacional é uma linguagem de consulta procedural. Ela consiste em um conjunto de operações que tomam uma ou duas relações como entrada e produzem uma nova relação como resultado. As operações fundamentais na álgebra relacional são: selecionar, projetar, renomear, (unárias) - produto cartesiano, união e diferença de conjuntos (binárias). Além das operações fundamentais, existem outras operações, a saber, interseção de conjuntos, ligação natural, dentre outras, que são definidas em termos das operações fundamentais. a b c x y a a b b c c x y x y x y a1 a2 a3 b1 b1 b2 b1 b2 b3 c1 c2 c3 a1 a2 a3 b1 b1 b2 c1 c1 c2 a a a b c x y z x y x z a Ligação (natural) Dividir União Diferença Intersecção Selecionar Projetar Produto Figura 2.1: Panorama geral da Álgebra Relacional. Claudinete Vicente Borges Banco de Dados: Notas de aulas 28 Considerar o seguinte esquema para exemplos posteriores. Agencias = (Cod_Agencia, Nome_Agencia, Cidade_Agencia) Clientes = (Cod_Cliente, Nome, rua, cidade) Depositos = ( Cod_Agencia, Conta_Numero, Cod_Cliente, saldo) Emprestimos = (Cod_Agencia, Cod_Cliente, Empréstimo_numero, quantia) 3.1.1. Operações Fundamentais Operação selecionar Seleciona tuplas que satisfazem um dado predicado. Usamos a letra minúscula grega sigma "σ" para representar a seleção. O predicado aparece subscrito em σ. A relação argumento aparece entre parênteses seguindo o σ. Assim, para selecionar estas tuplas da relação empréstimo onde o código da agência é 0662, escreve-se: σCod_Agencia=0662(Empréstimos) Podemos encontrar todas as tuplas onde a quantia emprestada seja maior que 1200 σquantia >1200(Empréstimos) As comparações são permitidas usando =, ≠,<, ≤, > e ≥ e os conectivos e (^) e ou (∨). Operação Projetar A operação projetar é uma operação unária que retorna sua relação argumento, com certas colunas deixadas de fora. A projeção é representada pela letra grega pi (π). Suponha que desejemos uma relação mostrando os clientes e as agências nas quais eles tomaram empréstimos, mas não nos importamos com a quantia e o número do empréstimo. Escrevemos: πCod_Agencia,Cod_Cliente(Emprestimos) Encontre todos os clientes (Nome) que moram em “Aracruz”. Devemos fazer uma seleção de todos os clientes que moram em Aracruz, em seguida projetar o código destes clientes. π Nome(σclientes-cidade=”Aracruz” (Clientes)) Operação Produto Cartesiano Capaz de combinar informações a partir de diversas relações. Trata-se de uma operação binária. Esta operação nos mostra todos os atributos das relações envolvidas. Cuidado com ambigüidade de nomes de atributos! Para selecionarmos todos os nomes dos clientes que possuam empréstimo na agência cujo código é 0662, escrevemos: Claudinete Vicente Borges Banco de Dados: Notas de aulas 29 π Nome (σCod_Agencia=0662 ^ Empréstimo. Cod_Cliente = Clientes. Cod_Cliente (Emprestimos X Clientes)) Operação União (binária) A operação união de conjuntos, representada por ∪∪∪∪, permite-nos encontrar tuplas que estão em uma das relações envolvidas. Vamos supor se quiséssemos saber todas as pessoas que possuam Depósitos, Empréstimos ou ambos numa determinada agência, com os recursos que temos até agora, não seria possível conseguirmos tal informação. Nessa situação, deveríamos fazer a união de todos que possuam depósitos com todos que possuam empréstimos nessa agência. Como veremos a seguir. Ex. Selecionar todos os clientes que possuam depósitos ou empréstimos ou ambos na agência 051. πNome (σCod_Agencia=”051” ^depositos. Cod_Cliente = Clientes. Cod_Cliente(Depositos X Clientes)) ∪∪∪∪ πNome (σCod_Agencia=”051” ^emprestimos. Cod_Cliente = Clientes. Cod_Cliente(Emprestimos X Clientes)) Uma vez que as relações são conjuntos, as linhas duplicadas são eliminadas. Para uma operação União r ∪∪∪∪ s serválida, necessita-se que duas condições sejam cumpridas: � As relações r e s precisam ter a mesma paridade. Isto é, elas precisam ter o mesmo número de atributos; � Os domínios do i-ésimo atributo de r e do i-ésimo atributo de s devem ser os mesmos. A operação Diferença de conjuntos A operação diferença de conjuntos, representada por -, permite-nos encontrar tuplas que estão em uma relação e não em outra. A expressão r - s resulta em uma relação que contém todas as tuplas que estão em r e não em s. Ex. Encontrar todos os clientes que possuam um depósito, mas não possuem um empréstimo na agência 051. πNome (σCod_Agencia=”051” ^ depósitos.Cod_Cliente = Clientes.Cod_Cliente(Depositos X Clientes)) - πNome (σCod_Agencia=”051” ^Empréstimos.Cod_Cliente = Clientes.Cod_Cliente(Emprestimos X Clientes)) Operação Renomear Operação necessária sempre que uma relação aparece mais de uma vez em uma consulta. Representação: ρ Ex. Encontre todos os clientes que moram na mesma rua e cidade que João. Podemos obter a rua e a cidade de João da seguinte forma: t � πrua, cidade (σNome=”João” (Clientes)) ou t � σNome=”João” (Clientes) Claudinete Vicente Borges Banco de Dados: Notas de aulas 30 Entretanto, para encontrar outros clientes com esta rua e cidade, devemos referir-nos à relação Clientes pela segunda vez. Perceba que se for inserida novamente uma relação clientes na consulta gerará ambigüidade. Por isso, deve-se renomeá-la. ρClientes2 (Clientes) πcliente2.cliente-.nome (σclientes.cidade = clientes2.cidade ^ clientes.rua = clientes2.rua (t X ρClientes2 (Clientes)) Clientes Cod_Cliente Nome rua Cidade 1 Maria Rua 1 Cariacica 2 João Rua 2 Vitória 3 Ana Rua 3 Cariacica 4 José Rua 2 Vitória Clientes X Clientes2 Cod_C liente Nome Rua cidade Cod_Clie nte Nome rua Cidade 1 Maria Rua 1 Cariacica 1 Maria Rua 1 Cariacica 1 Maria Rua 1 Cariacica 2 João Rua 2 Vitória 1 Maria Rua 1 Cariacica 3 Ana Rua 3 Cariacica 1 Maria Rua 1 Cariacica 4 José Rua 2 Vitória 2 João Rua 2 Vitória 1 Maria Rua 1 Cariacica 2 João Rua 2 Vitória 2 João Rua 2 Vitória 2 João Rua 2 Vitória 3 Ana Rua 3 Cariacica 2 João Rua 2 Vitória 4 José Rua 2 Vitória 3 Ana Rua 3 Cariacica 1 Maria Rua 1 Cariacica 3 Ana Rua 3 Cariacica 2 João Rua 2 Vitória 3 Ana Rua 3 Cariacica 3 Ana Rua 3 Cariacica 3 Ana Rua 3 Cariacica 4 José Rua 2 Vitória 4 José Rua 2 Vitória 1 Maria Rua 1 Cariacica 4 José Rua 2 Vitória 2 João Rua 2 Vitória 4 José Rua 2 Vitória 3 Ana Rua 3 Cariacica 4 José Rua 2 Vitória 4 José Rua 2 Vitória Claudinete Vicente Borges Banco de Dados: Notas de aulas 31 Exercícios Considere o esquema bancário, abordado anteriormente, para as questões que se seguem. 1. Selecionar todos os clientes (nomes) que possuam depósitos. 2. Selecionar todos os clientes que possuam depósito na mesma cidade onde moram. 3. Encontre todas as agências que possuam clientes com nome “Maria” (depósitos ou empréstimos). 4. Usando as Operações Fundamentais, encontre a conta com maior saldo. Considere o esquema seguinte para construir, usando operações fundamentais, da álgebra relacional, as questões que se seguem. Pessoas = (CodPessoa, NomePessoa, Cidade, Chefe) Empresas = (CodEmpresa, NomeEmpresa, Cidade) Trabalha = (CodPessoa, CodEmpresa, Salario) 5. Consulte todas as pessoas que trabalham. A consulta deverá retornar o nome das pessoas e a cidade onde moram. 6. Consulte o nome das empresas que possuem funcionários que ganham menos que um salário mínimo. (considere o valor do salário de R$300,00) 7. Consulte todas as pessoas (nomes) que trabalham em Vitória. 8. Consulte todas as pessoas (nomes) que trabalham na mesma cidade onde moram. 9. Consulte todas as pessoas (nomes) que moram na mesma cidade do chefe. 10. Consulte todas as empresas (nomes) que funcionam em cidades que não moram Maria. 11. Consulte todas as pessoas (nomes) que não trabalham em Vitória e que ganham acima de 2000. 12. Selecionar o nome do funcionário da empresa de código “01” que possui o menor salário. Claudinete Vicente Borges Banco de Dados: Notas de aulas 32 3.1.2. Operações não fundamentais Usando as operações fundamentais da álgebra relacional podemos expressar qualquer consulta da álgebra relacional. Entretanto, algumas consultas são longas demais para serem expressas. Operação Intersecção de conjuntos A operação Intersecção de conjuntos pode ser expressa em função das operações fundamentais da seguinte forma: r ∩ s = r - (r – s). Representação: ( ∩ ). Suponha que desejemos encontrar todos os clientes com um empréstimo e uma conta na agência “051”. Escrevemos da seguinte forma: πNome (σCod_Agencia=”051” ^ depositos.Cod_Cliente = Clientes.Cod_Cliente(Depositos X Clientes)) ∩ πNome (σCod_Agencia=”051” ^ emprestimos.Cod_Cliente = Clientes.Cod_Cliente(Emprestimos X Clientes)) Operação Ligação natural A ligação natural é uma operação binária que permite combinar certas seleções e um produto cartesiano em uma operação. É representada pelo símbolo |X|. A operação ligação natural forma um produto cartesiano de seus dois argumentos, faz uma seleção forçando uma equidade sobre os atributos que aparecem em ambos os esquemas relação. Ex1: “Encontre todos os cliente que tem empréstimos e a cidade em que vivem”. πNome, cidade (Emprestimos |X| Clientes) Ex2: “Encontre todos os cliente que tem empréstimos e que moram em “Vitória””. πNome, cidade (σcidade=”Vitória” (Emprestimos |X| Clientes)) Operação Divisão A operação divisão, representada por ÷, serve para consultas que incluem a frase “para todos”. Suponha que desejemos encontrar todos os clientes que têm uma conta em todas as agências localizadas em “Vitória”. Podemos obter todas as agências de Vitória através da expressão: r1 � πCod_Agencia (σcidade=”Vitória” (Agências)) Podemos encontrar todos os pares Nome, Cod_Agencia nos quais um cliente possui uma conta em uma agência escrevendo: r2 � πNome, Cod_Agencia (Depositos |X| Clientes) Agora precisamos encontrar clientes que apareçam em r2 com cada nome de agência em r1. Escrevemos esta consulta da seguinte forma: πNome, Cod_Agencia (Depositos |X| Clientes) ÷ πCod_Agencia (σcidade=”Vitória” (Agências)) Claudinete Vicente Borges Banco de Dados: Notas de aulas 33 Modificando o banco de dados Remoção r = r – E , onde E é uma consulta da álgebra relacional Exemplo1: Excluir todas as contas do cliente de código 01 Depositos = depositos - (σCod_Cliente = “01” (Depositos)) Exemplo2: Excluir todas as contas de “joão” T � πCod_Agencia, Conta_Numero, Cod_Cliente, saldo (σNome=”joão” (Depositos |X| Clientes)) Depositos = depositos - T Inserção r = r ∪ E Exemplo1: Depositos = Depositos ∪ {(51, 980987, 1, 1200)} Exemplo2: Gerar um depósito para todas as pessoas que possuem empréstimos. A conta gerada terá o mesmo número do empréstimo e o valor do depósito será igual ao da quantia emprestada. Depositos = Depositos ∪ πCod_Agencia, empréstimo--numero, Cod_Cliente, quantia (Emprestimos |X| Clientes) Atualização Operação Fundamental. Representação: δ. δA � E ( r ) Acrescer o saldo de todas as pessoas em 5 %. δsaldo � saldo * 1.05 ( Depositos ) Suponhamos que contas com saldos superiores a 10.000 recebam 6% de juros e as demais 5%. δsaldo � saldo * 1.06 (σsaldo > 10000 ( Depositos )) δsaldo � saldo * 1.05 (σsaldo <= 10000 ( Depositos )) Claudinete Vicente Borges Banco de Dados: Notas de aulas 34 Exercícios Considere o esquema seguinte para construir, usando operações da álgebra relacional, as questões que se seguem. Fabricante = (codf, nomef) Automovel = (coda, nomea, preço, codf) Pessoa = (codp, nomep) Venda = (codp, coda, data, valor, cor) 1) Relacionar os nomes das pessoas que compraram algum carro? 2) Relacionaros automóveis (nomes) da “Fiat”. 3) Quem comprou “Ford”? 4) Quem comprou carro com ágio (valor da venda maior que o valor do automóvel)? 5) Quem comprou carro entre 01/01/97 e 01/01/98? 6) Quem não comprou “Ford”? 7) Quem comprou “Ford” e não comprou “Volks”? 8) Qual é o carro mais caro (nome)? 9) Atualizar os valores dos automóveis da “GM “ em 15%. 10) Excluir todas as vendas anteriores a ‘01/01/1990’. 11) Selecionar todas as pessoas que compraram mais de um carro de diferentes modelos do mesmo fabricante. Considere o esquema abaixo para as questões que se seguem Alunos = (Codigo_Aluno, Nome_Aluno, Codigo_Curso, Telefone, Coeficiente) Cursos = (Codigo_Curso, Nome_Curso) Disciplinas = (Codigo_Disciplina, Codigo_Curso, Nome_Disc) Historico = (Codigo_Aluno, Codigo_Disciplina, Período, Nota) Pre_Requisitos = (Codigo_Disciplina_Pos, Codigo_Disciplina_Pre) 13. Selecionar todos os alunos que foram aprovados na disciplina ‘fisica’ no período ‘96/2’. 14. Selecionar todas as disciplinas do curso ‘Ciencia da Computação’. 15. Selecionar todas os nomes das disciplinas pré-requisitos da disciplina ‘Estrutura de Dados’. 16. Selecionar o aluno com maior coeficiente de rendimento no curso de ‘Sistemas de informação’. 17. Selecionar todos os alunos que não cursaram a disciplina ‘Projeto Final’. 18. Selecionar todas as disciplinas que ainda não foram cursadas por nenhum aluno. 19. Selecionar todas as disciplinas que foram cursadas por mais de um aluno. 20. Selecionar todos os alunos que cursaram todas as disciplinas do curso de ‘Sistemas de Informação’ 21. Selecionar todos os alunos que cursaram Programação I e Cálculo I. 22. Selecionar todos os alunos que foram reprovados em disciplinas no período ‘99/1’ e foram aprovados, nas respectivas disciplinas, no período ‘99/2’. 23. Atualizar a nota do aluno ‘Gabriel Borges’ para 9.8. 24. Excluir o curso ‘Biblioteconomia’. Claudinete Vicente Borges Banco de Dados: Notas de aulas 35 3.2. SQL (Structured Query Language) Certamente a SQL tem representado um padrão para linguagens de banco de dados relacionais. Existem diversas versões de SQL. A versão original foi desenvolvida no Laboratório de Pesquisa da IBM. Esta linguagem, originalmente chamada Sequel foi implementada como parte do projeto System R no início dos anos 70. A linguagem evoluiu desde então, e seu nome foi mudado para SQL (Structured Query Language). Em 1986, o Americam National Standard Institute (ANSI) e a International Standard Organization (ISO) publicaram um padrão SQL. A IBM publicou o seu próprio SQL, standard, o Systems Application Database Interface (SAA-SQL). A versão mais recente do padrão ANSI/ISO SQL é o padrão SQL-99. A linguagem SQL possui diversas partes: Linguagem de Definição de Dados (DDL) - Inclui comandos para definição de esquemas de relações, exclusão de relações, criação de índices e modificações do esquema de relações; Linguagem de manipulação de dados (DML) - Inclui comandos para inserção, exclusão e modificação de tuplas no banco de dados; Incorporação DML (SQL Embutida) - Uso de SQL em linguagens de programação de uso geral, como Pascal, C,...; Definição de Visões - A SQL DDL inclui comandos para definição de visões; Autorização - A SQL DDL inclui comandos para especificação de direitos de acesso à relações e visões; Integridade - A SQL DDL inclui comandos para especificação de regras de integridade que os dados que serão armazenados no banco de dados devem satisfazer; Controle de Transações - A SQL DDL inclui comandos para especificação de iniciação e finalização de transações. 3.2.1. Estrutura Básica A estrutura básica de uma expressão SQL consiste em três cláusulas: select, from e where. A cláusula select corresponde à operação projeção da álgebra relacional. É usada para listar os atributos desejados no resultado de uma consulta. A cláusula from corresponde à operação produto cartesiano da álgebra relacional. Ela lista as relações a serem examinadas na avaliação da expressão. A cláusula where corresponde ao predicado de seleção da álgebra relacional. Consiste em um predicado envolvendo atributos de relações que aparecem na cláusula from. Uma típica consulta SQL tem a forma: select A1, A2, ..., An � 3 from r1, r2, ..., rn � 1 where P � 2 Cada Ai representa um atributo e cada ri é uma relação. P é um predicado. Esta consulta é equivalente à expressão da álgebra relacional π A1, A2, ..., An (σP (r1 x r2 x ...x rn)) Claudinete Vicente Borges Banco de Dados: Notas de aulas 36 A lista de atributos A1, A2, ..., An pode ser substituída por um (*) para selecionar todos os atributos presentes na cláusula from. A SQL forma o produto cartesiano das relações chamadas na cláusula from, executa uma seleção da álgebra relacional usando o predicado da cláusula where e, então, projeta o resultado para os atributos da cláusula select. Na prática, a SQL pode converter esta expressão em uma forma equivalente que pode ser processada mais eficientemente. Considerar o seguinte esquema para exemplos que se seguem. Agencias = (Cod_Agencia, Nome_Agencia, Cidade_Agencia) Clientes = (Cod_Cliente, Nome, rua, cidade) Depositos = ( Cod_Agencia, Conta_Numero, Cod_Cliente, saldo) Emprestimos = (Cod_Agencia, Cod_Cliente, Numero_Emprestimo, quantia) O resultado de uma consulta SQL é, obviamente, uma relação. Vamos considerar uma consulta muito simples usando nosso banco como exemplo. “Encontre os nomes de todos os clientes na relação clientes” . select Nome from Clientes 3.2.2. Linhas (tuplas) duplicadas Em algumas situações uma consulta SQL pode retornar uma relação que contenha tuplas duplicadas. Nessa situação, inserimos a palavra-chave distinct depois do select para eliminá-las. Aproveitando o exemplo anterior, a relação resultante poderá ter clientes que possuam o mesmo nome. Neste caso, podemos eliminar estas duplicações, como se segue: select distinct Nome from Clientes 3.2.3. Predicados e ligações A SQL não tem uma representação da operação ligação natural. No entanto, uma vez que a ligação natural é definida em termos de um produto cartesiano, uma seleção e uma projeção, é relativamente simples escrever uma expressão SQL para uma ligação natural. Ex.: “Encontre os nomes e cidades de clientes que possuam empréstimos em alguma agência”. Na SQL, isto pode ser escrito como: Select distinct Nome, cidade From Clientes, Emprestimos Where Clientes.Cod_Cliente=Emprestimos.Cod_Cliente A SQL inclui os conectores and, or e not ; caracteres especiais: (, ), ., :, _, %,<, >, <= , >= , = , <>, +, - ,* e /; operador para comparação: between, como mostra o exemplo a seguir. Claudinete Vicente Borges Banco de Dados: Notas de aulas 37 “Selecionar todas as contas que possuam saldo entre 10000 e 20000”. Select Conta_Numero From Depositos Where saldo between 10000 and 20000 Que equivale a consulta Select Conta_Numero From Depositos Where saldo >= 10000 and saldo <= 20000 A SQL inclui também um operador para comparações de cadeias de caracteres, o like. Ele é usado em conjunto com dois caracteres especiais: � Por cento (%). Substitui qualquer subcadeia de caracteres; � Sublinhado (_). Substitui qualquer caractere. Ex.: “Encontre os nomes de todos os clientes cujas ruas incluem a subcadeia “na””. Select distinct Nome From Clientes Where rua like “ %na%” Ou também Ex.: “Encontre os nomes de todos os clientes cujas ruas finalizem com a subcadeia “na”, seguido de um caractere”. Select distinct Nome From Clientes Where rua like “ %na” Para que o padrão possa incluir os caracteres especiais ( isto é, % , _ , etc...), a SQL permite a especificação de um caractere de escape. O caractere de escape é usado imediatamente antes de um caractere especial para indicar que o caractere especial deverá ser tratado como um caractere normal.Definimos o caractere de escape para uma comparação like usando a palavra-chave escape. Para ilustrar, considere os padrões seguintes que utilizam uma barra invertida como caractere de escape. Like “ ab\%cd%” escape “\” substitui todas as cadeias começando com “ ab%cd”; Like “ ab\_cd%” escape “\” substitui todas as cadeias começando com “ ab_cd”. A procura por não-substituições em vez de substituições dá-se através do operador not like. 3.2.4. Operações de conjunto A SQL inclui a operações de conjunto union que opera em relações e corresponde à operação ∪ da álgebra relacional. Uma vez que as relações são conjuntos, na união destas, as linhas duplicadas são eliminadas. Claudinete Vicente Borges Banco de Dados: Notas de aulas 38 Para que uma operação r ∪∪∪∪ s seja válida, necessitamos que duas condições sejam cumpridas: As relações r e s precisam ter a mesma paridade. Isto é, elas precisam ter o mesmo número de atributos; Os domínios do i-ésimo atributo de r e do i-ésimo atributo de s devem ser os mesmos. Ex. “Se quiséssemos saber todos os clientes que possuam empréstimo na agência de código “ 051””, fazemos: Select distinct Nome From Clientes, Emprestimos Where Clientes.Cod_Cliente=Emprestimos.Cod_Cliente and Cod_Agencia = “051” Da mesma forma, se quiséssemos saber “todos os clientes que possuam depósitos na agência de código “051””, fazemos: Select distinct Nome From Clientes, Depositos Where Clientes.Cod_Cliente= Depositos.Cod_Cliente and Depositos.Cod_Agencia = “051” "Para achar todos os clientes que possuam um depósito, um empréstimo ou ambos na agência de código “051””, faz-se: Select distinct Nome From Clientes, Depositos Where Clientes.Cod_Cliente= Depositos.Cod_Cliente and Depositos.Cod_Agencia = “051” Union Select distinct Nome From Clientes, Emprestimos Where Clientes.Cod_Cliente=Emprestimos.Cod_Cliente and Cod_Agencia = “051” 3.2.5 Ordenando a exibição de tuplas A cláusula order by ocasiona o aparecimento de tuplas no resultado de uma consulta em uma ordem determinada. Para listar em ordem alfabética todos os clientes do banco, fazemos: Select distinct Nome From Clientes Order by Nome Como padrão, SQL lista tuplas na ordem ascendente. Para especificar a ordem de classificação, podemos especificar asc para ordem ascendente e desc para descendente. Podemos ordenar uma relação por mais de um elemento. Como se segue: Select * From Emprestimos Order by quantia desc, Cod_Agencia asc Claudinete Vicente Borges Banco de Dados: Notas de aulas 39 3.2.6. Membros de conjuntos O conectivo in testa os membros de conjunto, onde o conjunto é uma coleção de valores produzidos por uma cláusula select. Para ilustrar, considere a consulta “Encontre todos os clientes que possuem uma conta e um empréstimo na agência “Princesa Isabel””. Select distinct Nome From Clientes Where Clientes.Cod_Cliente in (select Cod_Cliente from depositos, agencias where depositos.Cod_Agencia = agencias.Cod_Agencia and Nome_Agencia = “Princesa Isabel”) and Clientes.Cod_Cliente in (select Cod_Cliente from emprestimos, agencias where emprestimos.Cod_Agencia = agencias.Cod_Agencia and Nome_Agencia = “Princesa Isabel”) da mesma forma, pode ser usada a expressão not in. 3.2.7. Variáveis tuplas (renomeação) Ilustração: “encontre o nome e a cidade de todos os clientes com um depósito e qualquer agência”. Select distinct T.Nome, T.cidade from Clientes as T, Depositos as S where T.Cod_Cliente = S.Cod_Cliente Redefinindo o esquema do banco para os exemplos que se seguem: Agencias = (Cod_Agencia, Nome_Agencia, Cidade_Agencia, ativos) Clientes = (Cod_Cliente, Nome, rua, cidade) Depositos = ( Cod_Agencia, Conta_Numero, Cod_Cliente, saldo) Emprestimos = (Cod_Agencia, Cod_Cliente, Numero_Emprestimo, quantia) 3.2.8. Comparação de conjuntos Considere a consulta “encontre todas as agências que possuem ativos maiores que alguma agência de Vitória”. Podemos escrever a expressão SQL: select distinct t.Nome_Agencia from agencias as t, agencias as s where t.ativos > s.ativos and agencias.cidade = “Vitória” Uma vez que isto é uma comparação “maior que”, não podemos escrever a expressão usando a construção in. Claudinete Vicente Borges Banco de Dados: Notas de aulas 40 A SQL oferece o operador any que pode ser usado para construir a consulta anterior. Comparações do tipo >any, <any, >=any, <=any, =any são aceitos pela linguagem. A consulta anterior pode ser escrita: select Nome_Agencia from agencias where ativos > any (select ativos from agencias where agencias.cidade = “Vitória”) Modificando a consulta anterior levemente. Vamos encontrar todas as agências que possuem ativos maiores do que todas as agências de Vitória. A construção > all corresponde a frase “maior que todos”. A consulta fica como se segue: select Nome_Agencia from agencias where ativos > all (select ativos from agencias where agencias.cidade = “Vitória”) Como o operador some, o operador all pode ser usado como: >all, <all, >=all, <=all, =all e <> all. 3.2.9. Testando relações vazias A SQL possui um recurso para testar se uma subconsulta tem alguma tupla em seus resultados. A construção exists retorna true se o argumento subconsulta está não-vazio. Usando a construção exists, podemos escrever a consulta “Encontre todos os clientes que possuam uma conta e um empréstimo na agência “Princesa Isabel” ”. Select Nome from clientes where exists (select * from depositos, agencias where depositos.Cod_Cliente= clientes.Cod_Cliente and agencias.Cod_Agencia = depositos.Cod_Agencia and Nome_Agencia = “Princesa Isabel”) and exists (select * from emprestimos, agencias where emprestimos.Cod_Cliente= clientes.Cod_Cliente and agencias.Cod_Agencia = emprestimos.Cod_Agencia and Nome_Agencia = “Princesa Isabel”) Pode-se usar também a expressão not exists. Claudinete Vicente Borges Banco de Dados: Notas de aulas 41 Exercícios de SQL Considerando o esquema: Pessoas = (CodPessoa, NomePessoa, Cidade, Chefe) Empresas = (CodEmpresa, NomeEmpresa, Cidade) Trabalha = (CodPessoa, CodEmpresa, Salario) 1. Consulte todas as pessoas que trabalham em Vitória. 2. Consulte todas as pessoas que trabalham na mesma cidade onde moram. 3. Consulte todas as pessoas que moram na mesma cidade do chefe. 4. Consulte todas as empresas que funcionam em cidades que não moram pessoas cujo primeiro nome seja Maria (usar operações de conjunto). 5. Consulte todas as pessoas que não trabalham em Vitória e que ganham acima de 2000 em ordem decrescente. 6. Consulte todas as pessoas que não trabalham na empresa que comece com “inf_” em ordem alfabética. Considere o esquema seguinte para as questões que se seguem. Fabricante = (codf, nomef) Automovel = (coda, nomea, preço, codf) Pessoa = (codp, nomep) Venda = (codp, coda, valor, cor, data) 1) Quem comprou “Ford”? ( 2) Quem não comprou “Ford”? 3) Quem comprou carro com ágio? 4) Quem comprou “Ford” e não comprou “Volks”? 5) Quem comprou carro entre 01/01/97 e 01/01/98? Claudinete Vicente Borges Banco de Dados: Notas de aulas 42 3.2.10. Funções agregadas A SQL oferece a habilidade para computar funções em grupos de tuplas usando a cláusula group by. O(s) atributo(s) dados na cláusula group by são usados para formar grupos. Tuplas com o mesmo valor em todos os atributos na cláusula group by são colocados em um grupo. A SQL inclui funções para computar: Média: AVG Mínimo: MIN Máximo: MAX Soma: SUM Contar: COUNT Para ilustrar, considere as consultas “ Encontre o saldo médio das contas em cada agência” Select Nome_Agencia, AVG(Depósitos.saldo) as Saldo_Medio From Depositos, Agencias Where Depositos.Cod_Agencia = Agencias.Cod_Agencia Group by Nome_Agencia “Encontre o número de depositantes
Compartilhar