Apostila de Banco de Dados
Propaganda
BANCO DE DADOS Professor Marcos Antonio Atualizada: 27/07/2008 Copyright © Julho-2008 - Marcos Antonio Moreira. As várias Marcas Registradas que aparecem no decorrer desta apostila. Mais do que simplesmente listar esses nomes e informar quem possui seus direitos de exploração, ou ainda imprimir o logotipo das mesmas, o autor declara estar utilizando tais nomes apenas para fins editoriais, em benefício exclusivo do dono da marca registrada, sem intenção de infringir as regras de sua utilização. SUMARIO UM POUCO DE HISTÓRIA ................................................................................................................ 1 VANTAGENS DO SQL......................................................................................................................... 3 VANTAGENS DO BANCO DE DADOS SGBD ................................................................................... 3 SQL – CONJUTO É ALGEBRA RELACIONAL 1 ................................................................................ 5 MODELO ENTIDADE-RELACIONAMENTO – E/R ........................................................................... 6 REPRESENTAÇÃO GRÁFICA ............................................................................................................. 7 CARDINALIDADE DE RELACIONAMENTOS .................................................................................... 8 GRAU DO RELACIONAMENTO ....................................................................................................... 10 DERIVAÇÃO DE TABELAS ............................................................................................................... 12 ATRIBUTO IDENTIFICADOR .......................................................................................................... 13 RELACIONAMENTOS ENTRE ENTIDADES .................................................................................... 15 NORMALIZAÇÃO DE DADOS .......................................................................................................... 15 TERCEIRA FORMA NORMAL (3FN) ............................................................................................... 18 AS PARTES DA LIGUAGEM SQL ..................................................................................................... 20 MODELO RELACIONAL – APRESENTAÇÃO SIMPLIFICADA ....................................................... 21 ESTRUTURA DA INFORMAÇÃO...................................................................................................... 23 VISÕES DO BANCO DE DADOS ..................................................................................................... 23 VANTAGENS DO BANCO DE DADOS EM RELAÇÃO À ARQUITETURA TRADICIONAL ....... 24 SQL COMANDOS .............................................................................................................................. 25 CREATE TABLE ............................................................................................................................. 25 ALTER TABLE ................................................................................................................................ 25 DROP TABLE ................................................................................................................................. 26 TIPOS DE DADOS ............................................................................................................................ 26 SELECT ALL FROM <nome-tabela> [, <nome-tabela>] ...................................................... 28 DEMAIS OPERADORES.................................................................................................................... 33 OPERADORES NEGATIVOS ............................................................................................................ 33 FUNÇÕES DE CARACTERES ........................................................................................................... 35 FUNÇÕES AGREGADAS (OU DE AGRUPAMENTO) ...................................................................... 36 AGRUPAMENTOS ............................................................................................................................. 36 JOIN - (JUNÇÃO POR IGUALDADE) .............................................................................................. 38 INNER JOIN - LEFT JOIN – RIGHT JOIN E FULL OUTER JOIN ................................................ 39 AS SUBCONSULTAS ......................................................................................................................... 41 INSERÇÕES, ALTERAÇÕES E EXCLUSÕES ................................................................................... 42 FERRAMENTA DE GERENCIAMENTO – IBEXPERT...................................................................... 43 EXEMPLO PRÁTICO – CRIAR UM BANCO DE DADOS............................................................. 43 REFERÊNCIAS BIBLIOGRÁFICAS ................................................................................................... 44 1|Página UM POUCO DE HISTÓRIA O termo banco de dados foi criado inicialmente pela comunidade de computação, para indicar coleções organizadas de dados armazenados em computadores digitais, porém o termo é atualmente usado para indicar tanto bancos de dados digitais como bancos de dados disponíveis de outra forma. DBMS (Database Management System), também conhecido como Sistema Gerenciador de Bando de Dados – SGBD e SQL (Structure Query Language) são frutos de todo um processo de desenvolvimento tecnológico na área de armazenamento e processamento da informação. Foram desenvolvidos com base no modelo hierárquico que dispõe as entidades na forma de uma arvore com diferentes níveis hierárquicos. Em 1970, E.F Codd, membro da equipe do laboratório de pesquisa da IBM em São Jose, Califórnia, publicou um trabalho agora clássico, “A Relational Modelo of Data for Large Shared Data Banks” um modelo relacional de dados para grandes bancos de dados compartilhados – (Coomunications of the ACM, Vol. 13, no 6, junho de 1970) em que se estabeleceu um grupo de princípios abstratos sobre gerencia de banco de dados: Assim chamado Modelo Relacional Todo o campo da tecnologia de banco de dados relacional tem suas origens nesse trabalho. As idéias de Codd incentivaram experiências e pesquisas em universidades, laboratórios de pesquisa industrial e estabelecimento semelhante, que resultaram em diversos produtos relacionais agora disponíveis no mercado. O modelo relacional, proposto por E.F Codd no inicio da década de 70, acabou prevalecendo e constitui a base dos modernos SGDB´s atuais. Um aspecto em particular da referida pesquisa era o projeto de implementação de protótipo de uma serie de linguagens relacionais. Uma linguagem relacional é uma linguagem que efetua, em alguma forma sintática ou concreta, alguma ou todas as características do modelo relacional abstrato. Diversas dessas linguagens foram criadas no inicio e no meio doa anos 70. Um dessas linguagens em particular foi a chamada SEQUEL (Structured English Query Language) definida por D.D Chamberlim e outros (1974) no laboratório de pesquisa da IBM em San Jose, Califórnia, e inicialmente implementada em um protótipo da IBM chamado SEQUEL-SRM (1974-1975). Em parte como resultado da experiência com o SEQUEL-XRM foi definida em 19761977 uma versão revisada do SEQUEL, chamada SEQUEL/2 (O nome foi posteriormente alterado para SQL por razões legais). Começou o trabalho em outro protótipo mais ambicioso da IBM, chamado System R. O system R, uma 2|Página implementação de um grande subconjunto da linguagem SQL, tornou-se operacional em 1977. Com o sucesso do System R, tornou-se aparente ao final dos anos 70 que a IBM provavelmente desenvolveria um ou mais produtos baseados na tecnologia System R – especificamente, produtos que implementasse a linguagem SQL. Como resultado, outros vendedores também começaram a construir seus próprios produtos baseados no SQL. De fato, pelo menos um desses produtos, a saber, o ORACLE, da Relational Software Inc. hoje, Oracle Corparation, foi introduzido no mercado antes dos próprios produtos da IBM. Depois 1981, a IBM anunciou outra versão do SQL/DS para ambiente VM/CMS (1982), e outra para MVS chamado DB2. Nos anos seguintes, diversos outros vendedores também anunciaram produtos baseados no SQL. O SQL se tornou o padrão no mundo do banco de dados relaciona. O SQL também se tornou o padrão oficial. Em 1982, o American National Standarts Institute (ANSI) encarregou seu Comitê de Banco de Dados (chamado X3H2) de desenvolver uma proposta para uma linguagem relacional padrão. Alguns bancos de dados implementam comandos que não são compatíveis com padrão ANSI tornando seu uso um pouco diferente dos outros SGBDs. A função da linguagem SQL é dar suporte a definição, manipulação e controle dos dados em um banco de dados relacional. Um banco de dados relacional é simplesmente um banco de dados que é percebido pelo usuário como um grupo de tabelas – onde tabelas é uma coleção de linhas e colunas. A linguagem SQL assume um papel muito importante nos SGBDs, podendo ter muitos enfoques. Linguagem Interativa de Consulta: Através de comandos SQL, os usuários podem montar consultas poderosas sem a necessidade de criação de um programa; podendo utilizar Forms ou ferramentas de montagem de relatórios; Linguagem de Programação para acesso a BDs: Comandos SQL embutidos em programas de aplicação que acessam dados armazenados; Linguagem de Administração de BDs: O responsável pela administração do banco de dados (DBA) pode utilizar comandos SQL para realizar suas tarefas; Linguagem Cliente/Servidor: Os programas (cliente) dos computadores pessoais usam comandos SQL para se comunicarem através de uma rede local, compartilhando os dados armazenados e um único local (servidor). A arquitetura cliente/servidor minimiza o trafego de dados pela rede. Linguagem de Banco de Dados Distribuído: A SQL auxilia na distribuição dos dados através de vários nós conectados ao sistema de computação. 3|Página VANTAGENS DO SQL Independência de fabricante: SQL é oferecida a praticamente em todos SGBDs. Múltiplas Visões de Dados: A SQL permite ao criador do banco de dados, levarem diferentes visões dos dados a diferentes usuários. Consulta Interativa: A SQL provê um acesso rápido aos dados fornecendo respostas aos usuários a questões complexas, em minutos ou segundos. Redução dos Custos com Treinamento: Baseado no item anterior, as aplicações podem se movimentar de um ambiente para outro sem que seja necessária uma reciclagem da equipe de desenvolvimento. Inglês Estruturado de Alto Nível: A SQL é formada por um conjunto bem simples de sentenças em inglês, oferecendo um rápido e fácil entendimento. Portabilidade de Computadores: A SQL pode ser utilizada desde um computador pessoal, passando por uma estação de trabalho até um computador de grande porte. VANTAGENS DO BANCO DE DADOS SGBD 1 - Redução ou Eliminação de Redundâncias – Possibilita a eliminação de dados privativos de cada sistema. Os dados, que eventualmente são comuns a mais de um sistema, são compartilhados por eles, permitindo o acesso a uma única informação sendo consultada por vários sistemas. 2 - Eliminação de Inconsistências – Através do armazenamento da informação em um único local com acesso descentralizado e, sendo compartilhada à vários sistemas, os usuários estarão utilizando uma informação confiável. A inconsistência ocorre quando um mesmo campo tem valores diferentes em sistemas diferentes. Exemplo, o estado civil de uma pessoa é solteiro em um sistema e casado em outro. Isto ocorre porque esta pessoa atualizou o campo em um sistema e não o atualizou em outro. Quando o dado é armazenado em um único local e compartilhado pelos sistemas, este problema não ocorre. 4|Página 3 - Compartilhamento dos Dados – Permite a utilização simultânea e segura de um dado, por mais de uma aplicação ou usuário, independente da operação que esteja sendo realizada. Deve ser observado apenas o processo de atualização concorrente, para não gerar erros de processamento (atualizar simultaneamente o mesmo campo do mesmo registro). Os aplicativos são por natureza multiusuário. 4 - Restrições de Segurança – Define para cada usuário o nível de acesso a ele concedido (leitura, leitura e gravação ou sem acesso) ao arquivo e/ou campo. Este recurso impede que pessoas não autorizadas utilizem ou atualizem um determinado arquivo ou campo. 5 - Padronização dos Dados – Permite que os campos armazenados na base de dados sejam padronizados segundo um determinado formato de armazenamento (padronização de tabela, conteúdo de compôs, etc) e ao nome de variáveis seguindo critérios padrões preestabelecido pela empresa. Ex. Para o campo "Sexo" somente será permitido armazenamento dos conteúdos "M" ou "F". 6 - Independência dos Dados – Representa a forma física de armazenamento dos dados no Banco de Dados e a recuperação das informações pelos programas de aplicação. Esta recuperação deverá ser totalmente independente da maneira com que os dados estão fisicamente armazenados. Quando um programa retira ou inclui dados o SGBD compacta-os para que haja um menor consumo de espaço no disco. Este conhecimento do formato de armazenamento do campo é totalmente transparente para o usuário. A independência dos dados permite os seguintes recursos: a - Os programas de aplicação definem apenas os campos que serão utilizados independente da estrutura interna dos arquivos b - Quando há inclusão de novos campos no arquivo, será feita manutenção apenas nos programas que utilizam esses campos, não sendo necessário mexer nos demais programas. Obs.: Nos sistemas tradicionais este tipo de operação requer a alteração no lay-out de todos os programas do sistema que utilizam o arquivo. 7 - Manutenção da Integridade – Consiste em impedir que um determinado código ou chave em uma tabela não tenha correspondência em outra tabela. 5|Página Ex. Um código de uma determinada disciplina na tabela “Histórico Escolar” sem a sua descrição na tabela “Disciplina”. SQL – CONJUTO É ALGEBRA RELACIONAL 1 Em banco de dados relacionais as informações são guardadas em tabelas. Para recuperar uma informação necessária ao usuário, deve-se buscá-la em várias tabelas diferentes, estabelecendo-se um relacionamento entre elas. Esta é a origem do nome deste paradigma de banco de dados. Tabelas são na verdade conjuntos. Por exemplo, quando em um sistema existe uma tabela de vendas, esta tabela corresponde ao conjunto de todas as vendas realizadas por empresa. A tabela de vendedores corresponde ao conjunto de vendedores que trabalham em uma empresa. Cada linha ou registro da tabela corresponde a um elemento do conjunto. Consultas e alterações na base de dados correspondem a operações realizadas sobre conjuntos. Estas operações são definidas pela álgebra relacional. SQL – CONJUTO É ALGEBRA RELACIONAL 2 Consultas em banco de dados não passam de problemas de álgebra relacional. Assim, como acontecem com a álgebra “tradicional”, os operadores possuem algumas propriedades. Sabemos que 2 x 3 = 3 x 2. Isto significa que, quando precisamos montar uma expressão de álgebra relacional para chegar a um determinado resultado, podemos fazê-lo de mais de uma forma, pois várias expressões levam ao mesmo resultado. Em outras palavras, quando o banco de dados precisa montar uma expressão algébrica para encontrar um resultado, ele deve escolher uma entre várias. Apesar de apresentarem o mesmo resultado, as expressões são diferentes, e a diferença fará com que o banco de dados adote um diferente caminho para resolver cada uma. Escolher o caminho mais curto e uma das grandes atribuições do banco de dados. Esta é a missão do otimizador, um subsistema do banco de dados, responsável por determinar o plano de execução para uma consulta. 6|Página MODELO ENTIDADE-RELACIONAMENTO – E/R Definição Consiste em mapear o mundo real do sistema em um modelo gráfico que irá representar o modelo e o relacionamento existente entre os dados. Entidade - Identifica o objeto de interesse do sistema e tem "vida" própria, ou seja, a representação abstrata de um objeto do mundo real sobre o qual desejamos guardar informações. Exemplo: Clientes, Fornecedores, Alunos, Funcionários, Departamentos, etc. Não são entidades: Entidade com apenas um elemento; Operações do sistema; Saídas do sistema; Pessoas que realizam trabalhos (usuários do sistema); Cargos de direção Instância de Entidade - São os elementos da entidade. Exemplo: Cliente 10, Funcionário João, Aluno Pedro, etc. Atributo - Informações que desejamos guardar sobre a instância de entidade. Exemplo: Nome do aluno, Número da turma, Endereço do fornecedor, Sexo do funcionário, etc. Domínio do Atributo - Universo de valores que um atributo pode armazenar. Exemplo: Conjunto de valores do atributo Sexo do funcionário: M ou F; Conjunto de valores do atributo Nome do aluno: 40 caracteres alfanuméricos. Conjunto de valores do atributo salário: inteiro maior que 5000 Relacionamentos Após a identificação das entidades a incluir no esquema da BD, e dos atributos que as definem, é necessário perceber o modo como estas entidades se relacionam entre si. Assim, um relacionamento é uma associação existente entre entidades. Uma entidade é definida por um conjunto de dados que de alguma forma se encontram relacionados. Os dados numa entidade encontram-se divididos em 7|Página campos ou atributos que são os elementos que a caracterizam. A cada ocorrência relativa a uma entidade dá-se o nome de registro. REPRESENTAÇÃO GRÁFICA - Entidade - Relacionamento Relacionamento Representa a associação entre os elementos do conjunto de uma entidade com outra entidade. Exemplo: O João está matriculado na disciplina de Banco de Dados 8|Página Onde: - João - Elemento do conjunto de valores do atributo Nome do aluno da entidade Aluno; - Banco de Dados - Elemento do conjunto de valores do atributo Nome da disciplina da entidade Disciplina; - Matriculado - Ligação existente entre um aluno e uma disciplina. ALUNO MATRICULADO DISCIPLINA CARDINALIDADE DE RELACIONAMENTOS Representa a freqüência com que existe o relacionamento. Exemplo: Relacionamento 1:1 - O João é casado com a Maria. 9|Página Onde: - João - Elemento do conjunto de valores do atributo Nome da entidade Homem. - Maria - Elemento do conjunto de valores do atributo Nome da entidade Mulher. - casado - Ligação entre um homem e uma mulher, sendo que um homem pode ser casado com uma e apenas uma mulher, assim como uma mulher pode ser casada com um e apenas um homem. 1 HOMEM 1 CASADO MULHER Relacionamento 1:N ou N:1 - O Pedro trabalha no Departamento Pessoal. Onde: - Pedro - Elemento do conjunto de valores do atributo Nome da entidade Funcionário. - Depart. Pessoal - Elemento do conjunto de valores do atributo Nome do departamento da entidade Departamento. - trabalha - Ligação entre um Funcionário e um Departamento, onde um funcionário pode trabalhar em um e somente um departamento e um departamento pode ter vários funcionários. 10 | P á g i n a N 1 LOTAÇÃO EMPREGADO DEPARTAMENTO Relacionamento N : M - O Antônio está matriculado na disciplina Banco de Dados. Onde: - Antônio - Elemento do conjunto de valores do atributo Nome da entidade Aluno. - Banco de Dados - Elemento do conjunto de valores do atributo Nome da Disciplina da entidade Disciplina. - matriculado - Ligação existente entre um aluno e uma disciplina, onde um aluno pode estar matriculado em várias disciplinas e cada disciplina pode ter vários alunos matriculados. N ALUNOS M MATRICULADO DISCIPLINA GRAU DO RELACIONAMENTO Indica o número de entidade que se relacionam. Relacionamento Binário Quando existe o relacionamento entre apenas duas entidades. Ex. Um fornecedor comercializa materiais que são utilizados em diversos projetos. 11 | P á g i n a N M COMERCIALIZA FORNECEDOR MATERIAIS M N UTILIZADOS PROJETOS Relacionamento Ternário Quando existe o relacionamento entre três entidades. Ex. Um fornecedor comercializa materiais que são utilizados em projetos específicos. 1 N UTILIZADO FORNECEDOR MATERIAIS N PROJETOS Exemplos de Relacionamento: - O Professor Alberto leciona Estrutura de Dados e o aluno Pedro cursa Linguagem de Programação 12 | P á g i n a N PROFESSOR M LECIONA DISCIPLINA N M ALUNOS CURSA DERIVAÇÃO DE TABELAS Analisando o grau de relacionamento e a qualidade de participação é possível identificar o número de tabelas necessárias para cada relacionamento. UMA TABELA Relacionamentos de 1:1 com participação obrigatória de ambas as entidades. DUAS TABELAS Relacionamento de 1:1 com participação obrigatória de uma das entidades, em que nesta é adicionada uma chave externa; Relacionamentos de 1:N ou N:1 com participação obrigatória do lado N, em que nesta é adicionada uma chave externa. TRÊS TABELAS A terceira tabela é responsável pelo relacionamento entre as outras duas e nela serão incluído como chaves externas as chaves primárias das outras duas. A esta tabela dá-se o nome de Entidade Associativa. 13 | P á g i n a ATRIBUTO IDENTIFICADOR Entre os diversos atributos que definem uma entidade deve existir um ou mais campos que identifiquem inequivocamente cada registro. A este(s) atributo(s) dáse o nome de Atributo Identificador. ATRIBUTO IDENTIFICADOR – É o atributo que deve identificar sem ambigüidades cada entidade concreta. Para cada entidade deve existir sempre um atributo deste tipo. Geralmente, este atributo desempenha o papel de CHAVE numa entidade ou tabela. Exemplo: Consideremos a entidade Filmes e os seus atributos: FILMES (N_Filme, Título, Ator, Realizador, Duração, Classificação) O campo N_Filme é um atributo identificador inequivocamente cada ocorrência (filme) da entidade. pelo fato de identificar CHAVE PRIMÁRIA CHAVE PRIMÁRIA – É um atributo identificador que representa univocamente cada ocorrência ou registro de uma tabela. Existem dois tipos de chave primária: Simples - constituída apenas por um atributo; Composta - constituída por dois ou mais atributos. Uma chave primária deve ser: Unívoca – O valor da chave primária deve ser único para todos os registros. Não Redundante – No caso de uma chave composta não devem ser incluídos mais campos do que os necessários. Não Nula – Nenhum dos valores que compõem a chave primária pode conter valores nulos. 14 | P á g i n a Exemplos Consideremos a entidade DVD que é caracterizada pelos seguintes atributos: ( N_DVD, Título, Intérprete, Editora) O atributo que a identifica univocamente é Nº de DVD visto ser o único cujos valores nunca se irão repetir. Deste modo, conclui-se que a chave primária da entidade DVD é simples. Consideremos a entidade FAIXA de um DVD que é caracterizada pelos seguintes atributos: Faixas ( N_DVD, N_FAIXA, Título, Duração, Gênero) Neste caso, os atributos que a identificam univocamente são N_DVD e N_FAIXA, visto serem os únicos cujos valores nunca se irão repetir. Deste modo, conclui-se que a chave primária da entidade Faixas é composta. CHAVE ESTRANGEIRA OU EXTERNA – É um atributo que definido como chave primária de uma tabela é incluído na estrutura de outra tabela. Exemplo: Consideremos as entidades DVD e FAIXA, que identificam um DVD e as suas respectivas Faixas. DVD ( N_DVD, Título, Intérprete, Editora) FAIXA ( N_DVD, N_FAIXA, Título, Duração, Gênero) O atributo N_DVD da entidade FAIXA faz parte da sua chave primária, no entanto como é chave primária da entidade DVD é considerado uma chave estrangeira na entidade FAIXA. 15 | P á g i n a RELACIONAMENTOS ENTRE ENTIDADES O relacionamento entre entidades é um dos propósitos das bases de dados relacionais, daí a importância dada à seleção da chave primária, pois é através destas que são estabelecidas as associações entre as diferentes entidades. Os símbolos convencionados para se representar estes relacionamentos são em número reduzido, com significados específicos e fáceis de distinguir: NORMALIZAÇÃO DE DADOS A normalização é um processo que consiste em estruturar as tabelas e atributos de forma a eliminar redundâncias e evitar problemas com a inserção, eliminação e atualização dos dados. Este processo é composto pelas chamadas formas normais: - 1ª Forma Normal (1ª FN); - 2ª Forma Normal (2ª FN); - 3ª Forma Normal (3ª FN); Um modelo de base de dados que respeite os princípios estipulados até a 3ª FN é considerado adequadamente elaborado para funcionar num SGBD relacional. A normalização é feita, através da análise dos dados que compõem as estruturas utilizando o conceito chamado "Formas Normais (FN)". As FN são conjuntos de restrições nos quais os dados devem satisfazê-las. Exemplo pode-se dizer que a estrutura está na primeira forma normal (1FN), se os dados que a compõem satisfizerem as restrições definidas para esta etapa. 16 | P á g i n a A normalização completa dos dados é feita, seguindo as restrições das quatro formas normais existentes, sendo que a passagem de uma FN para outra é feita tendo como base o resultado obtido na etapa anterior, ou seja, na FN anterior. Para realizar a normalização dos dados, é primordial que seja definido um campo chave para a estrutura, campo este que permite identificar os demais campos da estrutura. Formas Normais existentes: PRIMEIRA FORMA NORMAL (1FN) Consistem em retirar da estrutura os elementos repetitivos, ou seja, aqueles dados que podem compor uma estrutura de vetor. Podemos afirma que uma estrutura está normalizada na 1FN, se não possuir elementos repetitivos. Exemplo: Estrutura original: Arquivo de Notas Fiscais (Num. NF, Série, Data emissão, Cod. do Cliente, Nome do cliente, Endereço do cliente, CGC do cliente, Relação das mercadorias vendidas (onde para cada mercadoria temos: Código da Mercadoria, Descrição da Mercadoria, Quantidade vendida, Preço de venda e Total da venda desta mercadoria) e Total Geral da Nota) Analisando a estrutura acima, observamos que existem várias mercadorias em uma única Nota Fiscal, sendo, portanto elementos repetitivos que deverão ser retirados. Estrutura na primeira forma normal (1FN): Entidade de Notas Fiscais (Num. NF, Série, Data emissão, Código do Cliente, Nome Cliente, Endereço do cliente, CGC do cliente e Total Geral da Nota) Entidade de Vendas (Num. NF, Código da Mercadoria, Descrição da Mercadoria, Quantidade vendida, Preço de venda e Total da venda desta mercadoria) Obs. Os campos sublinhados identificam as chaves das estruturas. Como resultado desta etapa ocorre um desdobramento dos dados em duas estruturas, a saber: 17 | P á g i n a - Primeira estrutura (Entidade de Notas Fiscais): Dados que compõem a estrutura original, excluindo os elementos repetitivos. - Segundo estrutura (Entidade de Vendas): Dados que compõem os elementos repetitivos da estrutura original, tendo como chave o campo chave da estrutura original (Num. NF) e o campo chave da estrutura de repetição (Código da Mercadoria). SEGUNDA FORMA NORMAL (2FN) Consistem em retirar das estruturas que possuem chaves compostas (campo chave sendo formado por mais de um campo), os elementos que são funcionalmente dependentes de parte da chave. Podemos afirmar que uma estrutura está na 2FN, se ela estiver na 1FN e não possuir campos que são funcionalmente dependentes de parte da chave. Exemplo: Estrutura na primeira forma normal (1FN): Entidade de Notas Fiscais (Num. NF, Série, Data emissão, Código do Cliente, Nome do cliente, Endereço do cliente, CGC do cliente e Total Geral da Nota) Entidade de Vendas (Num. NF, Código da Mercadoria, Descrição da Mercadoria, Quantidade vendida, Preço de venda e Total da venda desta mercadoria) Estrutura na segunda forma normal (2FN): Entidade de Notas Fiscais (Num. NF, Série, Data emissão, Código do Cliente, Nome do cliente, Endereço do cliente, CGC do cliente e Total Geral da Nota) Entidade de Vendas (Num. NF, Código da Mercadoria, Quantidade vendida e Total da venda desta mercadoria) 18 | P á g i n a Entidade de Mercadorias (Código da Mercadoria, Descrição da Mercadoria, Preço de venda) Como resultado desta etapa, houve um desdobramento da Entidade de Vendas (a entidade Notas Fiscais, não foi alterado, por não possuir chave composta) em duas estruturas a saber: - Primeira estrutura (Entidade de Vendas): Contém os elementos originais, sendo excluídos os dados que são dependentes apenas do campo Código da Mercadoria. - Segundo estrutura (Entidade de Mercadorias): Contém os elementos que são identificados apenas pelo Código da Mercadoria, ou seja, independentemente da Nota Fiscal, a descrição e o preço de venda serão constantes. TERCEIRA FORMA NORMAL (3FN) Consistem em retirar das estruturas os campos que são funcionalmente dependentes de outros campos que não são chaves. Podemos afirmar que uma estrutura está na 3FN, se ela estiver na 2FN e não possuir campos dependentes de outros campos não chaves. Exemplo: Estrutura na segunda forma normal (2FN): Entidade de Notas Fiscais (Num. NF, Série, Data emissão, Código do Cliente, Nome do cliente, Endereço do cliente, CGC do cliente e Total Geral da Nota) Entidade de Vendas (Num. NF, Código da Mercadoria, Quantidade vendida e Total da venda desta mercadoria) Entidade de Mercadorias (Código da Mercadoria, Descrição da Mercadoria, Preço de venda) 19 | P á g i n a Estrutura na terceira forma normal (3FN): Entidade de Notas Fiscais (Num. NF, Série, Data emissão, Código do Cliente e Total Geral da Nota) Entidade de Vendas (Num. NF, Código da Mercadoria, Quantidade vendida e Total da venda desta mercadoria) Entidade de Mercadorias (Código da Mercadoria, Descrição da Mercadoria, Preço de venda) Entidade de Clientes (Código do Cliente, Nome do cliente, Endereço do cliente e CGC do cliente) Como resultado desta etapa, houve um desdobramento da entidade Notas Fiscais, por ser o único que possuía campos que não eram dependentes da chave principal (Num. NF), uma vez que independente da Nota Fiscal, o Nome, Endereço e CGC do cliente são inalterados. Este procedimento permite evitar inconsistência nos dados dos arquivos e economizar espaço por eliminar o armazenamento freqüente e repetidas vezes destes dados. A cada nota fiscal comprada pelo cliente, haverá o armazenamento destes dados e poderá ocorrer divergência entre eles. As estruturas alteradas foram pelos motivos, a saber: - Primeira estrutura (Entidade de Notas Fiscais): Contém os elementos originais, sendo excluídos os dados que são dependentes apenas do campo Código do Cliente (informações referentes ao cliente). - Segundo estrutura (Entidade de Clientes): Contém os elementos que são identificados apenas pelo Código do Cliente, ou seja, independente da Nota Fiscal, o Nome, Endereço e CGC dos clientes serão constantes. Após a normalização, as estruturas dos dados estão projetadas para eliminar as inconsistências e redundâncias dos dados, eliminando desta forma qualquer problema de atualização e operacionalização do sistema. A versão final dos dados poderá sofrer alguma alteração, para atender as necessidades específicas do 20 | P á g i n a sistema, a critério do analista de desenvolvimento durante o projeto físico do sistema. Chave Primária – Seu conceito está associado ao campo ou conjunto de campos de uma tabela que possuirá um único valor, que não será repetido para nenhum outro registro. Como exemplo desta situação podem-se considerar os números de CPF, CNPJ, RG ou qualquer código de controle utilizado. Chave Estrangeira – Caracteriza-se por ser uma chave de uma tabela que possui associação com a chave primária de outra tabela. Assim sendo, podem ocorrer três situações de utilização de chave estrangeira: Relacionamento Um para Um – A chave estrangeira pode ser criada em qualquer uma das tabelas; Relacionamento Um para Muitos – A chave estrangeira pode ser criada apenas na tabela do lado “muita”; Relacionamento Muitos para Muitos - A chave estrangeira deve ser criada numa terceira tabela que faça o relacionamento entre as outras duas tabelas. Integridade Referencial - é um mecanismo utilizado que evita a quebra de ligação entre os relacionamentos estabelecidos entre tabelas. AS PARTES DA LIGUAGEM SQL A linguagem SQL pode ser dividida nas seguintes partes: Data Definition Language – DDL (Linguagem Definição de Dados) A SQL DDL fornece comandos para definição e modificação de esquemas de relação, remoção de relações e criação de índices. Os principais comandos que fazem parte da DDL são: CREATE, ALTER, DROP ..... 21 | P á g i n a Data Manipulation Language – DML (Linguagem de manipulação de Dados) A SQL DML inclui uma linguagem de consulta baseada na álgebra relacional e no calculo relacional. Compreende também comandos para inserir, remover e modificar informações em um banco de dados. Os comandos básicos da DML são: SELECT, INSERT, UPDATE, DELETE .... Data Control Language – DCL (Linguagem de Controle de Dados) Comando que fazem o cadastramento de usuários e determina seu nível de privilegio para os objetos do banco de dados. Os principais comandos são: GRANT, REVOKE. Transaction Control – (Controle de Transação ) A SQL inclui comandos para especificação do inicio e fim das transações. Diversar implementações permitem o trancamento explicito de dados para o controle de concorrência. (COMMIT, ROLLBACK E SAVEPOINT) MODELO RELACIONAL – APRESENTAÇÃO SIMPLIFICADA Todos SGBD’s atuais, são baseados no modelo de dados relacional (também conhecido como modelo entidade e relacionamento E/R). Assim sendo, temos que os dados contidos nos modernos SGBD´s, não importa o produto/fabricante (Oracle, Sybase, Informix, Interbase, Firebird, MySQL, SQl Server, etc..), são passiveis de ser acessados por programas escritos em varias linguagens de programação, por intermédio de instruções padrão de SQL. 22 | P á g i n a Aplicação Escrita em CodeGear Delphi SGBD Aplicação Escrita em .Net SQL Aplicação Escrita em Visual Studio Como sempre acontecem, os diversos fabricantes de SGBD´s acabam implantando algumas extensões de funções exclusivas em seus SGBD em particular (sempre com o objetivo de tornar seu produto melhor que o SGBD do concorrente.) Essas extensões se forem usadas pelos programas que acessam um determinado SGBD, acabam “quebrando” a portabilidade desses mesmos programas para SGBD de outros fabricantes. Com SQL, é possível também efetuar diretamente consultas e manutenções às bases de dados sem uso de nenhuma linguagem de programação em particular, apenas submetendo as instruções SQL a um SGBD. Dominar a linguagem SQL é o primeiro passo para dominar qualquer SGBD. SQL – onde a pronúncia é “ess-kiú-éll” ou “sequel “ que é como a maioria das pessoas pronunciam. É composta por um grupo de facilitadores para definição, manipulação e controle de dados em um banco de dados relacional. 23 | P á g i n a ESTRUTURA DA INFORMAÇÃO Banco de Dados - Representa o arquivo físico de dados, armazenado em dispositivos periféricos, onde estão armazenados os dados de diversos sistemas, para consulta e atualização pelo usuário. Tabelas Lógicas - Representam as estruturas de armazenamento de dados (arquivos) dos sistemas. S.G.D.B. (Sistema Gerenciador de Banco de Dados) - É o software responsável pelo gerenciamento (armazenamento e recuperação) dos dados no Banco de Dados. Ex. Interbase, Firebird, SQL Server, Oracle, Informix, DB2 Dado - É o valor do campo quando é armazenado no Banco de Dados. Ex. O valor do campo "nome do cliente" para quem está fazendo a entrada de dados. Conteúdo do campo - É o valor do campo armazenado no Banco de Dados. Ex. O valor do campo "nome do cliente" sem estar, momentaneamente, sendo utilizado. Informação - É o valor que este campo representa para as atividades da empresa. Ex. Resposta a uma consulta. Quais os nomes dos clientes localizados no Rio de Janeiro? VISÕES DO BANCO DE DADOS Visão 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. Visão 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. 24 | P á g i n a Na visão conceitual, existem 2 (duas) linguagens de operação que são: a) Linguagem de definição dos dados (DDL) - Linguagem que define as aplicações, arquivos e campos que irão compor o banco de dados (comandos de criação e atualização da estrutura dos campos dos arquivos). b) Linguagem de manipulação dos dados (DML) - Linguagem que define os comandos de manipulação e operação dos dados (comandos de consulta e atualização dos dados dos arquivos). Visão Externa - É aquela vista pelo usuário que opera os sistemas aplicativos, através de interfaces desenvolvidas pelo analista (programas), buscando o atendimento de suas necessidades. VANTAGENS DO BANCO DE DADOS EM RELAÇÃO À ARQUITETURA TRADICIONAL Sistema Tradicional São aqueles em que os dados do sistema estão armazenados fisicamente separados um do outro. O acesso é feito pelos programas de aplicação, associando o nome externo dos arquivos e definindo todo o registro independente da utilização dos campos. Ex.: Paradox, Dataflex, Clipper Sistema de Banco de Dados É aquele em que os dados são definidos para o S.G.B.D., através da DDL (linguagem de definição de dados). Fisicamente estão armazenados em um único local, sendo o acesso realizado apenas através do S.G.B.D. Nos programas de aplicação, é necessário apenas definir os campos que serão utilizados pelo programa. Ex.: Interbase, Firebird, Oracle, DB2, SQL Server, Informix 25 | P á g i n a SQL COMANDOS A linguagem SQL possui uma série de comandos para manipulação do banco de Dados. Desde comandos simples até os mais complexos. Um detalhe importante é que a linguagem SQL é declarativa. Isto que dizer que nós quase que perguntamos ao banco de dados para que ele nos “responda” através de compos formando registros. Esse detalhe torna o aprendizado da SQL muito fácil para quem esta iniciando na linguagem. Vamos começar a analisar os Comandos Básicos da Linguagem SQL e fazer uma série de exemplos. COMANDOS DDL CREATE TABLE Criar à estrutura de uma tabela (entidade) definida as colunas (campos) e as chaves primárias e estrangeiras existentes. CREATE TABLE < nome_tabela > ( nome_atributo1 < tipo > [ NOT NULL ], nome_atributo2 < tipo > [ NOT NULL ], Onde: Nome_Table Nome_atributo Tipo - indica o nome da tabela a ser criada. indica o nome do campo a ser criado na tabela. indica a definição do tipo de atributo ( integer(n), char(n), real(n,m), date... ). Exemplo: CREATE TABLE DEPT (DepNume integer(4) not null, DepNome char(20) not null, DepLoca char(20) not null, DepOrca integer(12,2), primary key (DepNume) ); ALTER TABLE Alterar a estrutura de uma tabela (entidade) acrescentando, alterando, retirando e alterando nomes, formatos das colunas e a integridade referencial definidas em uma determinada tabela. 26 | P á g i n a ALTER TABLE < nome_tabela > ADD / DROP ( nome_atributo1 < tipo > [ NOT NULL ], nome_atributoN < tipo > [ NOT NULL ] ); Exemplo: ALTER TABLE DEPT ( ADD DEPSALA DECIMAL (10,2) ); DROP TABLE Objetivo Deletar a estrutura e os dados existentes em uma tabela. Após a execução deste comando estarão deletados todos dados, estrutura e índices de acessos que estejam a ela associados. Sintaxe: DROP TABLE <nome-tabela> Onde: Nome-tabela - Representa o nome da tabela que será deletada. TIPOS DE DADOS OS TIPOS NUMÉRICOS Pode ser utilizado no tratamento de valores numéricos inteiros ou reais. Estão disponíveis os seguintes tipos: Nota: O parâmetro tamanho é opcional e permite estabelecer o tamanho máximo do valor a ser exibido, podendo ser um valor máximo 255; 27 | P á g i n a SMALLINT – [(tamanho)] – Utiliza-se este tipo de dado quando houver a necessidade de fazer uso de valores inteiros curtos entre a faixa de valores de – 32.768 até 32.767. INTEGER [(tamanho)] – Utiliza-se este tipo de dado quando houver a necessidade de fazer uso de valores inteiros longos entre a faixa de valores de – 2.147.483.648 até 2.147.483.647. FLOAT – Utiliza-se este tipo de dado quando houver a necessidade de fazer uso de valores com ponto flutuante com sete dígitos de precisão decimais. Este tipo permite trabalhar com valores na faixa de 3,4 * 10-38 até 3,4 * 1038 ; NUMERIC /DECIMAL [(tamanho[,decimal])] – Visa armazenar números, cuja precisão vai de 1 a 18 dígitos a armazenar. A escala vai de 1 a 18 números de casas decimais para armazenamento. DOUBLE PRECISION [(tamanho.decimal)] – Utiliza-se este tipo de dado quando houver a necessidade de fazer uso de valores flutuantes com precisão de 15 dígitos O TIPO TEMPORAL Pode ser utilizado no tratamento de valores relacionados à data e horário. Estão disponíveis os seguintes tipos: DATE – Utiliza-se este tipo de dado quando houver a necessidade de fazer uso de uma data TIME – Utiliza-se este tipo de dado quando houver a necessidade de fazer uso de uma informação relacionada a um determinado horário. O TIPO LITERAL O Tipo Literal pode ser para a recepção de valores baseados em cadeias de caracteres (strings – dados alfanuméricos delimitados por aspas ou apóstrofos). Estão disponíveis os seguintes tipos: CHAR (tamanho) – Utiliza-se este tipo de dado quando houver a necessidade de fazer uso de seqüências de caracteres de tamanho fixo que estejam limitadas até 255 caracteres de comprimento. O parâmetro tamanho determina o valor máximo em caracteres que pode conter a seqüência; 28 | P á g i n a VARCHAR(tamanho) – Utiliza-se este tipo de dado quando houver a necessidade de fazer uso de seqüências de caracteres de tamanho variável que estejam limitadas até 255 caracteres de comprimento. A diferença entre este tipo e o CHAR é que neste caso, os espaços em branco excedentes do lado direito da seqüência de caracteres não utilizados são automaticamente desprezados. O parâmetro tamanho determina o valor máximo em caracteres que pode conter a seqüência . BINARY LARGE OBJECTS (BLOBS) Tem por objetivo armazenar dados binários, como gráficos, sons digitalizados, texto. Subtipo 0 - utilizado para acomodar dados binários ou dados de tipo indeterminado Subtipo 1 - Utilizado para acomodar texto. COMANDOS DML Objetivo Selecionar um conjunto de registros em uma ou mais tabelas que atenda a uma determinada condição definida pelo comando. Sintaxe: SELECT ALL FROM <nome-tabela> [, <nome-tabela>] DISTINCT WHERE <condição> GROUP BY <nome-coluna> HAVING <condição> ORDER BY <nome-campo> ASC DESC Onde: a) nome-tabela - Representa o nome da (s) tabela (s) que contem as colunas que serão selecionadas ou que serão utilizadas para a execução da consulta. b) condição - Representa a condição para a seleção dos registros. Este seleção poderá resultar em um ou vários registros. c) nome-coluna - Representa a (s) coluna (s) cujos resultados são grupados para atender à consulta. 29 | P á g i n a d) ALL - Opção default. Mostra todos os valores obtidos na seleção. e) DISTINCT - Opção que mostra os valores obtidos na seleção eliminando as duplicidades. f) WHERE - Especifica o critério de seleção dos registros nas tabelas especificadas. Exemplo: SELECT EMPNOME, EMPSERV FROM EMP WHERE DEPNUME > 10; g) GROUP BY - Especifica o (s) campo (s) que serão grupados para atender a consulta. Exemplo: SELECT DUPNUME, AVG(EMPSALA) FROM EMP GROUP BY DEPNUME; h) HAVING - Especifica uma condição para seleção de um grupo de dados. Esta opção só é utilizada combinada com a opção GROUP BY. i) ORDER BY - Esta opção quando utilizada apresenta o resultado da consulta ordenado de forma ii) Crescente ou decrescente pelos campos definidos. Exemplo: SELECT DEPNUME, AVG (EMPSALA) FROM EMP GROUP BY DEPNUME HAVING COUNT (*) > 3; ALGUMAS FUNÇÕES UTILIZADAS NO COMANDO SELECT. a) COUNT(*) (DISTINCT <nome-campo>) 30 | P á g i n a Objetivo Retorna a quantidade de registros existentes no campo especificado. Quando a opção * é utilizada o resultado é a quantidade de registros existentes. Quando é referenciado o nome de um campo retorna a quantidade de valores existentes na coluna. b) SUM (ALL <nome-campo>) DISTINCT Objetivo Retorna a soma dos valores existentes no campo especificado. Quando a opção DISTINCT é utilizada são considerados apenas os diferentes valores existentes no campo. Exemplo: SELECT SUM(EMPSALA) FROM EMP; c) AVG (ALL <nome-campo>) DISTINCT Objetivo Retorna a média dos valores existentes no campo especificado. Quando a opção DISTINCT é utilizada são considerados apenas os diferentes valores existentes no campo. Exemplo: SELECT AVG(EMPSALA) FROM EMP; d) MAX (ALL <nome-campo>) DISTINCT Objetivo Retorna o maior valor existente no campo especificado. Quando a opção DISTINCT é utilizada são considerados apenas os diferentes valores existentes no campo. Exemplo: SELECT MAX(EMPSALA) FROM EMP; e) MIN (ALL <nome-campo>) DISTINCT Objetivo Retorna o menor valor existente no campo especificado. Quando a opção DISTINCT é utilizada são considerados apenas os diferentes valores existentes no campo. 31 | P á g i n a Exemplo: SELECT MIN(EMPSALA) FROM EMP; WHERE COMO BASE DAS RESTRIÇÕES. A cláusula "where" corresponde ao operador de restrição da álgebra relacional. Contém a condição que as tabelas devem obedecer a fim de serem listadas. Ela pode comparar valores em colunas, literais, expressões aritmética ou funções. A seguir apresentamos operadores lógicos e complementares a serem utilizados nas expressões apresentadas em WHERE. Operadores lógicos Operador = > >= < <= Significado igual a maior que maior que ou igual a menor que menor que ou igual a ALGUNS EXEMPLOS: SELECT EMPNOME, EMPSERV FROM EMP WHERE DEPNUME > 10; SELECT EMPNOME, EMPSERV FROM EMP WHERE EMPSERV = 'GERENTE'; Nota: O conjunto de caracteres ou datas devem estar entre apóstrofes („) na cláusula "where". Apresente todos os salários existentes na empresa, porém omita eventuais duplicidades. A cláusula Distinct elimina duplicidades, significando que somente relações distintas serão apresentadas como resultado de uma pesquisa. SELECT DISTINCT EMPSERV FROM EMP; 32 | P á g i n a Selecione todos os departamentos cujo orçamento mensal seja maior que 100000. Apresente o Nome de tal departamento e seu orçamento anual, que será obtido multiplicando-se o orçamento mensal por 12 SELECT DEPNOME, DEPORCA * 12 FROM DEPT WHERE DEPORCA > 100000; Apresente a instrução anterior, porém ao invés dos "feios" DepNome e DepOrca, os Títulos Departamento e Orçamento. SELECT DEPNOME "DEPARTAMENTO", DEPORCA * 12 "ORCAMENTO ANUAL" FROM DEPT WHERE DEPORCA > 100000; Apresente os nomes e funções da cada funcionário contidas na tabela empresa, porém classificados alfabeticamente (A..Z) e depois alfabeticamente invertido (Z..A). Nota: A cláusula Order By modificará a ordem de apresentação do resultado da pesquisa (ascendente ou descendente). SELECT EMPNOME, EMPSERV FROM EMP ORDER BY EMPNOME; SELECT EMPNOME, EMPSERV FROM EMP ORDER BY EMPPNOME DESC; Selecione os Nomes dos Departamentos que estejam na fábrica. SELECT DEPNOME FROM DEPT WHERE DEPLOCA = "SAO PAULO"; 33 | P á g i n a DEMAIS OPERADORES OPERADOR SIGNIFICADO Entre dois valores (inclusive) Lista de valores Com um padrão de caracteres É um valor nulo between ... and .. in ( .... ) like is null EXEMPLOS SELECT EMPNOME, EMPSALA FROM EMP WHERE EMPSALA BETWEEN 500 AND 1000; SELECT EMPNOME, DEPNUME FROM EMP WHERE DEPNUME IN (10,30); OPERADORES NEGATIVOS OPERADOR <> not Nome_oluna not nome_coluna not between not in not like is not null DESCRIÇÃO Diferente Diferente da coluna Não maior que Não entre dois valores informados não existente numa dada lista de valores diferente do padrao de caracteres informado não é um valor nulo Selecione os Empregados cujos salários sejam menores que 1000 ou maiores que 3500. Necessitaremos aqui a utilização de expressão negativa. A seguir apresentamos operadores negativos. SELECT EMPNOME, EMPSALA FROM EMP WHERE EMPSALA NOT BETWEEN 1000 AND 3500; 34 | P á g i n a Apresente todos os funcionários com salários entre 200 e 700 e que sejam Vendedores. Necessitaremos de consultas com condições múltiplas. Operadores "AND" (E) e "OR" (OU). SELECT EMPNOME, EMPSALA, EMPSERV FROM EMP WHERE EMPSALA BETWEEN 700 AND 2000 AND EMPSERV = 'VENDEDOR'; SELECT EMPNOME, EMPSERV FROM EMP WHERE EMPNOME LIKE 'F%'; SELECT EMPNOME, EMPSERV FROM EMP WHERE EMPCOMI IS NULL; O símbolo "%" pode ser usado para construir a pesquisa ("%" = qualquer seqüência de nenhum até vários caracteres). Apresente todos os funcionários com salários entre 200 e 700 ou que sejam Vendedores. SELECT EMPNOME, EMPSALA, EMPSERV FROM EMP WHERE EMPSALA BETWEEN 700 AND 2000 OR EMPSERV = 'VENDEDOR'; Apresente todos os funcionários com salários entre 200 e 700 e que sejam Vendedores ou Balconistas. SELECT EMPNOME, EMPSALA, EMPSERV FROM EMP WHERE EMPSALA BETWEEN 700 AND 2000 AND ( EMPSERV = 'BALCONISTA' OR EMPSERV = 'VENDEDOR' ); 35 | P á g i n a FUNÇÕES DE CARACTERES Lower Upper Concat(x,y)Substring(x,y,str)termina em "y". força caracteres maiúsculos aparecerem em minúsculos. força caracteres minúsculos aparecerem em maiúsculos. concatena a string "x" com a string "y". extrai um substring da string "str", começando em "x", e To_Char(num)^Q - converte um valor numérico para uma string de caracteres. converte data para o formato apresentado. Apresente o nome de todos os empregados em letras minúsculas. SELECT LOWER( EMPNOME ) FROM EMP; Apresente o nome de todos os empregados (somente as 10 primeiras letras). SELECT SUBSTRING (1,10,EMPNOME) FROM EMP; Apresente o nome de todos os empregados admitidos em 01/01/80. SELECT * FROM EMP WHERE EMPADMI = ^Q"DD-AAA-YYYY"("01-JAN-1980"); Ou SELECT * FROM EMP WHERE EMPADMI = ^Q("01-JAN-1980"); 36 | P á g i n a FUNÇÕES AGREGADAS (OU DE AGRUPAMENTO) Função Retorno avg(n) count(expr) max(expr) min(expr) sum(n) média do valor n, ignorando nulos vezes que o número da expr avalia para algo nao nulo maior valor da expr menor valor da expr soma dos valores de n, ignorando nulos Apresente a Média, o Maior, o Menor e também a Somatória dos Salários pagos aos empregados. SELECT AVG(EMPSALA) FROM EMP; SELECT MIN(EMPSALA) FROM EMP; SELECT MAX(EMPSALA) FROM EMP; SELECT SUM(EMPSALA) FROM EMP; AGRUPAMENTOS As funções de grupo operam sobre grupos de registro (linhas). Retornam resultados baseados em grupos de registros em vez de resultados de funções por registro individual. A cláusula "GROUP BY" do comando "SELECT" é utilizada para dividir registros em grupos menores. A cláusula "GROUP BY" pode ser usada para dividir os registros de uma tabela em grupos menores. As funções de grupo devolvem uma informação sumarizada para cada grupo. Apresente a média de salário paga por departamento. SELECT DUPNUME, AVG(EMPSALA) FROM EMP GROUP BY DEPNUME; OBSERVAÇÃO Qualquer coluna ou expressão na lista de seleção, que não for uma função agregada, deverá constar da cláusula "GROUP BY". Portanto é errado tentar impor uma "restrição" do tipo agregada na cláusula Where. 37 | P á g i n a Having A cláusula "HAVING" pode ser utilizada para especificar quais grupos deverão ser exibidos, portanto restringindo-os. Retome o problema anterior, porém apresente resposta apenas para departamentos com mais de 10 empregados. SELECT DEPNUME, AVG(EMPSALA) FROM EMP GROUP BY DEPNUME HAVING COUNT(*) > 3; Observação: A cláusula "GROUP BY" deve ser colocada antes da "having", pois os grupos são formados e as funções de grupos são calculadas antes de se resolver a cláusula "HAVING". A cláusula "WHERE" não pode ser utilizada para restringir grupos que deverão ser exibidos. *** Exemplificando ERRO típico - Restringindo Média Maior que 1000: **** SELECT DEPNUME, AVG(EMPSALA) FROM EMP WHERE AVG(SALARIO) > 1000 GROUP BY DEPNUME; ( Esta seleção está ERRADA! ) SELECT DEPNUME, AVG(EMPSALA) FROM EMP GROUP BY DEPNUME HAVING AVG(EMPSALA) > 1000; ( Seleção Adequada ) SEQÜÊNCIA NO COMANDO "SELECT": SELECT FROM WHERE GROUP BY HAVING ORDER BY coluna(s) tabela(s) condição (ões) da(s) tupla(s) condição(ões) do(s) grupo(s) de tupla(s) condição (ões) do(s) grupo(s) de tupla(s) coluna(s); 38 | P á g i n a A "SQL" fará a seguinte avaliação: a) WHERE, para estabelecer tabelas individuais candidatas (não pode conter funções de grupo) b) GROUP BY, para fixar grupos. c) HAVING, para selecionar grupos para exibição. JOIN - (JUNÇÃO POR IGUALDADE) O relacionamento existente entre tabelas é chamado de equi-junção, pois os valores de colunas das duas tabelas são iguais. A Equi-junção é possível apenas quando tivermos definido de forma adequada a chave estrangeira de uma tabela e sua referência a chave primária da tabela precedente. Apesar de admitir-se em alguns casos, a equi-junção de tabelas, sem a correspondência Chave PrimáriaChave Estrangeira, recomendamos fortemente ao estudante não utilizar este tipo de construção, pois certamente em nenhum momento nos exemplos propostos em nossa disciplina ou, serão necessárias tais junções. Listar Nomes de Empregados, Cargos e Nome do Departamento onde o empregado trabalha. Observemos que dois dos três dados solicitados estão na Tabela Emp, enquanto o outro dado está na Tabela Dept. Deveremos então acessar os dados restringindo convenientemente as relações existentes entre as tabelas. De fato sabemos que DEPNUME é chave primária da tabela de Departamentos e também é chave estrangeira da Tabela de Empregados. Portanto, este campo será o responsável pelo JOIN SELECT A.EMPNOME, A.EMPSERV, B.DEPNOME FROM EMP A, DEPT B WHERE A.DEPNUME = B.DEPNUME; Nota: Note que as tabelas quando contém colunas com o mesmo nome, usa-se um apelido "alias" para substituir o nome da tabela associado à coluna. Imagine que alguém tivesse definido NOME para ser o Nome do Empregado na Tabela de Empregados e também NOME para ser o Nome do Departamento na Tabela de Departamentos. Tudo funcionaria de forma adequada, pois o aliás se encarregaria de evitar que uma ambigüidade fosse verificada. Embora SQL resolva de forma muito elegante o problema da nomenclatura idêntica para campos de tabelas, recomendamos que o estudante fortemente evitasse tal forma de nomear os campos. O SQL nunca confundirá um A.NOME com um B.NOME, porém podemos afirmar o mesmo de nós mesmos? 39 | P á g i n a Liste os Códigos do Cada Funcionário, seus Nomes, seus Cargos e o nome do Gerente ao qual este se relaciona. Precisamos criar um auto-relacionamento, ou seja, juntar uma tabela a ela própria. É possível juntarmos uma tabela a ela mesma com a utilização de apelidos, permitindo juntar registro da tabela a outra tabela da mesma tabela. SELECT A.EMPNUME, A.EMPNOME, A.EMPSERV, B.EMPNOME FROM EMP A, EMP B WHERE A.EMPGERE = B.EMPNUME; INNER JOIN - LEFT JOIN – RIGHT JOIN E FULL OUTER JOIN É através dos diferentes tipos de join que conseguimos recuperar dados de uma ou mais tabelas com auxilio dos relacionamentos existentes. Nos exemplos serão consideradas duas tabelas (PRODUTO E DVENDA) vamos supor nos exemplos que foram vendidos itens sem estarem cadastrados PRODUTO X VENDA A área em vermelho representa os produtos que foram cadastrados, mas não foram vendidos. Para selecionar esses produtos utilizamos um LEFT JOIN, ligando um filtro para listar SOMENTE as linhas EXISTENTES na tabela produto SEM CORRESPONDENTE na tabela venda; 40 | P á g i n a A área em azul representa produtos que foram vendidos e não possuem cadastro. Para selecionar esses produtos utilizaremos um RIGHT JOIN ligando um filtro para listar SOMENTE as linhas EXISTENTES na tabela venda SEM CORRESPONDENTES na tabela produto; A área em verde representa os produtos que foram vendidos e possuem cadastro. Para selecionar as linhas que possuem equivalência nas duas tabelas utilizamos o INNER JOIN. Para representar a união de todos os produtos com todas as vendas, independentemente de relacionamento (área em vermelho+verde+azul) utilizamos um FULL OUTER JOIN Vamos começar pela área verde (intersecção) Selecionar os produtos que foram vendidos e possuem cadastro – área verde SELECT DISTINCT P.CODIGO, P.DESCRICAO FROM PRODUTO P INNER JOIN DVENDA V ON P.CODIGO = V.CODPROD Selecionar os produtos que foram vendidos, mas não possuem cadastro- área azul SELECT V.CODPROD FROM PRODUTO P RIGHT OUTER JOIN DVENDA V ON V.CODPROD = P.CODIGO WHERE P.CODIGO IS NULL Selecionar os produtos que foram cadastrados, mas não foram vendidos – área vermelha SELECT P.CODIGO, P.DESCRICAO FROM PRODUTO P LEFT OUTER JOIN DVENDA V ON V.CODPROD = P.CODIGO WHERE V.CODPROD IS NULL 41 | P á g i n a AS SUBCONSULTAS Uma subconsulta é um comando "SELECT" que é aninhado dentro de outro "SELECT" e que devolve resultados intermediários. Relacione todos os nomes de funcionários e seus respectivos cargos, desde que o orçamento do departamento seja igual a 300000. SELECT EMPNOME, EMPSERV FROM EMP A WHERE 300000 IN ( SELECT DEPORCA FROM DEPT WHERE DEPT.DEPNUME = A.DEPNUME ); Nota: Observe que a cláusula IN torna-se verdadeira quando o atributo indicado está presente no conjunto obtido através da subconsulta. Relacione todos os departamentos que possuem empregados com remuneração maior que 3500. SELECT DEPNOME FROM DEPT A WHERE EXISTS (SELECT * FROM EMP WHERE EMPSALA > 3500 AND EMP.DEPNUME = A.DEPNUME'); Nota: Observe que a cláusula EXISTS indica se o resultado de uma pesquisa contém ou não registro. Observe também que poderemos verificar a não existência (NOT EXISTS) caso esta alternativa seja mais conveniente. Se eu quiser saber quais produtos participaram em qualquer pedido cuja quantidade seja maior ou igual a 10 SELECT DESCRICAO FROM PRODUTO WHERE CODIGO IN (SELECT CODPROD FROM DVENDA WHERE QTDE >= 10) Quais as vendas que tiveram seu valor abaixo da media SELECT CODIGO, VALORVENDA FROM CVENDA WHERE VALORVENDA < (SELECT AVG(VALORVENDA) FROM CVENDA) 42 | P á g i n a Quais produtos que não estão presente em nenhuma vendam SELECT CODIGO, DESCRICAO FROM PRODUTO WHERE NOT EXISTS (SELECT PRODUTO.CODIGO) * FROM DVENDA WHERE CODPROD = INSERÇÕES, ALTERAÇÕES E EXCLUSÕES De forma alguma o mesmo ocorrerá com o especialista em SQL ao ter que migrar do Banco de Dados X para o Banco de Dados Y. Naturalmente existirá a necessidade de aprendizado, mas este programador poderá ir adaptando-se aos poucos sem precisar ser retreinado, o que é um aspecto extremamente vantajoso para as empresas. INSERIR (INSERT) Sintaxe: INSERT INTO <tabela> [<campos>] [VALUES <valores>] INSERT INTO DEPT; Possibilita a inserção de registros de forma interativa. INSERT INTO DEPT (DEPNUME,DEPNOME,DEPLOCA) VALUES (70,"PRODUCAO","RIO DE JANEIRO"); INSERT INTO CLIENTE (NOME, ENDERECO) VALUES („PAULO‟, „RUA A‟) INSERT INTO VALUES (10,‟JOSÉ‟, „BAIRO‟, „ENEDERÇO‟, „CIDADE‟) Possibilita a inserção de registros em tabelas sem digitação dos dados. ATUALIZAR (UPDATE) Sintaxe: UPDATE <tabela> SET <campo> = <expressão> [WHERE <condição>]; 43 | P á g i n a UPDATE EMP SET EMPSALA = EMPSALA * 1.2 WHERE EMPSALA < 1000; UPDATE PRODUTO SET TOTAL = TOTAL * 1.1 UPDATE CLIENTE SET ENDERECO = „RUA FULANO DE TAL‟ WHERE CODIGO = 10 EXCLUIR (DELETE) Sintaxe: DELETE FROM <tabela> [WHERE <condição>]; DELETE FROM EMP WHERE EMPSALA > 5000; DELETE FROM CLIENTE WHERE CODIGO = 5 FERRAMENTA DE GERENCIAMENTO – IBEXPERT Observação: Mais informações sobre IBExpert em Vídeo Aula EXEMPLO PRÁTICO – CRIAR UM BANCO DE DADOS CONTENDO AS SEGUINTES TABELAS: CLIENTE – PRODUTO – FORNECEDOR VENDENDOR – VENDAS – ITENS_VENDAS 44 | P á g i n a REFERÊNCIAS BIBLIOGRÁFICAS APLICAÇÕES CLIENTE/SERVIDOR COM DELPHI 6 + INTERBASE 6 – UMA ABORDAGEM PRÁTICA – EDITORA ÉRICA FIREBIRD 2.0 - O BANCO DE DADOS DO NOVO MILÊNIO - CARLOS H. CANTU http://www.relativa.com.br/livros_template.asp?Codigo_Produto=60937 PROGRAMAÇÃO CLIENTE/SERVIDOR COM FIREBIRD http://www.relativa.com.br/livros_template.asp?Codigo_Produto=44288 LINKS http://www.firebase.com.br/fb/ http://www.clubedelphi.net/portal http://www.devmedia.com.br http://www.borland.com http://www.google.com.br http://www.firebirdsql.com/ http://www.msdn.com.br
