Banco de Dados - Princípios e Prática

Curitiba, 2007

banco_de_dados...

Luciano Frontino de Medeiros

...princípios_e_prática_

Curitiba, 2007

Rua Tobias de Macedo Junior, 319 Santo Inácio_CEP 82010-340_Curitiba_PR_Brasil diretor presidente_Wilson Picler conselho editorial_Ivo José Both, Dr. (presidente)

_Elena Godoy, Drª. _ José Raimundo Facion, Dr. _Sérgio Roberto Lopes, Dr. _Ulf Gregor Baranow, Dr. supervisão editorial_Lindsay Azambuja, M.e análise de informação_Adriane Ianzen revisão de texto_Sandra Regina Klippel capa _Denis Kaio Tanaami projeto gráfico_Raphael Bernadelli diagramação_Regiane de Oliveira Rosa

M488b

Medeiros, Luciano Frontino de Banco de dados : princípios e prática / Luciano Frontino de Medeiros – Curitiba: Ibpex, 2007. 186 p. : il. ISBN 978-85-87053-89-2

1. Banco de dados. 2. Tecnologia da informação. 3. Informática. I. Título. CDD 005.74 20.ed.

Informamos que é de inteira responsabilidade do autor a emissão de conceitos. Nenhuma parte desta publicação poderá ser reproduzida por qualquer meio ou forma sem a prévia autorização da Editora Ibpex. A violação dos direitos autorais é crime estabelecido na Lei nº 9.610/98 e punido pelo artigo 184 do Código Penal.

apresentação_

Este livro conta com o apoio da Faculdade Internacional de Curitiba (Facinter) e da Faculdade de Tecnologia Internacional (Fatec), ambas instituições do Grupo Uninter, como obra a ser consultada nas disciplinas e nos módulos referentes a bancos de dados nos cursos da área de tecnologia em informática e análise de sistemas. A presente obra procura fazer uma ponte entre a área de banco de dados e os conceitos de sistemas de informação, buscando agregar um sentido maior à necessidade de informações no desempenho das atividades de uma organização. A idéia também foi a de não nos atermos a uma implementação específica de banco de dados, mas tentar apresentar de forma genérica o padrão SQL em si e resumir todos os comandos abordados nas seções do capítulo 4 e 5 no apêndice B. Assim o

livro apresenta a possibilidade de servir, inclusive, como um manual de consulta para comandos. O modelo E-R (Entidade-Relacionamento) é descrito em capítulo específico. Isso porque tais conceitos permitem o tratamento dos dados e de suas relações sob um contexto semântico (concedendo um maior sentido às representações de dados do que o modelo relacional utilizado genericamente). Esse modelo foi explorado aqui de conformidade com a literatura existente na área através de uma abordagem bastante prática. Tal conceito pode servir de suporte a pesquisas mais detalhadas, em seu âmbito de aplicação, inclusive como complemento ao estudo de ontologias em representação do conhecimento. Os capítulos apresentam ao final uma série de exercícios para fixação dos conteúdos pertinentes, realizando explicações referentes à parte conceitual, bem como à parte prática de banco de dados. Alguns exemplos trazem atributos sem a acentuação usual da língua portuguesa, pois levamos em consideração que no padrão SQL e nas implementações de bancos de dados não são permitidos caracteres acentuados.

sumário_

0000_0001 =

I

= introdução_ao_banco_de_dados = 11

0000_0010 =

I

= o_modelo_entidade-relacionamento_(E-R) = 33

0000_0011 =

I

= álgebra_relacional = 64

0000_0100 = IV = standard_query_language_SQL = 93 0000_0101 = V = modificações_no_banco_de_dados = 145 referências_por_capítulo = 157 referências = 159 apêndices = 161 anexo = 181

introdução_

Este material surgiu da compilação de várias notas de aulas e de exercícios praticados com alunos das disciplinas de Banco de Dados I e II ministradas na Faculdade Internacional de Curitiba no período entre 2002 e 2004. A abordagem procura ser simples, com ênfase em comandos SQL, de acordo com o padrão, ao que adicionamos os conceitos de álgebra relacional e fazemos um paralelo entre as duas linguagens de consulta ao longo de todo o conteúdo. Considerando que o banco de dados se constitui no elemento chave para a adequada representação da informação e do conhecimento, o conteúdo descrito nesta obra objetiva auxiliar o leitor já consciente de certos conceitos da área de informática – e inclusive aqueles com um grau inicial de prática – a aprofundar-se em aspectos teóricos de modelagem de dados e de linguagens de consulta.

A área de banco de dados dá suporte a uma série de disciplinas, envolvendo programação e desenvolvimento de sistemas, e, também, subsídios para o estudo de áreas mais avançadas, como a construção de Data Warehouses e a mineração de dados. Portanto, é de esperar-se que o leitor possa enriquecer mais seus conhecimentos nessa área. Este livro, portanto, destina-se àqueles usuários com certos conhecimentos e prática em banco de dados. O conhecimento de SQL é comparado com a abordagem simbólica da modelagem E-R e a linguagem da álgebra relacional. Propõe-se tanto como literatura adicional ou complementar para disciplinas de banco de dados e àquelas correlatas em cursos de graduação e pós-graduação, além de ferramenta de auxílio a profissionais desse campo de atuação.

0000_0001 = I

introdução ao_banco de_dados_

Os sistemas de informação estão na atualidade profun damente arraigados empresas.

nas

Estamos

na era da informação, portanto não poderia ser diferente. Uma empresa tem seu grau de competitividade proporcional à importância que a mesma – representada pelos seus dirigentes, executivos e colaboradores – dá à informação. As informações são as “molas-mestras” para a tomada de decisão, e uma decisão errônea pode acarretar sérias conseqüências ao desempenho da empresa como um todo. A busca de qualidade de informação deve, então, ser uma constante no dia-a-dia das organizações, e ao armazenamento e processamento adequado de informação é atribuído um papel fundamental no âmbito de um sistema de informação. A evolução da informática permitiu uma grande mudança nos paradigmas organizacionais. Uma grande empresa pode, atualmente, empregar seus esforços de crescimento e desenvolvimento apenas em função da internet* relacionando-se com seus clientes e fornecedores apenas por este meio. Hoje, as empresas fazem seus negócios de forma virtual e

* Como, por exemplo, o caso da livraria virtual Amazon Books. Para mais informações acesse: http://www.amazon.com

divulgam os seus produtos a uma massa globalizada de consumidores, portanto os sistemas de informação tornaram-se vitais como suporte para tal situação. Isso fez com que a necessidade de processos de bancos de dados eficientes e eficazes ficasse cada vez mais evidente. Mas o que são efetivamente bancos de dados? Date1 afirma que “um banco de dados é uma coleção de dados persistentes utilizada pelos sistemas de aplicação de uma empresa”, sendo a persistência entendida como os dados que são diferentes daqueles que têm características efêmeras, e que apenas podem ser removidos por alguma solicitação explícita externa. Grassmann e Tremblay2 dizem que os atributos ou itens que descrevem entidades do mundo real, tal como uma pessoa, coisa ou evento, são estruturados em registros que, por sua vez, compõem os arquivos. Se o conjunto destes é utilizado por programas ou aplicações em certa área de uma empresa, então, a esse conjunto denominamos de banco de dados. Turban, Rainer Júnior e Potter3 conceituam banco de dados como sendo um grupo lógico de arquivos relacionados entre si, armazenando dados e associações entre eles, para evitar uma variedade de problemas associados a um ambiente tradicional de arquivos. Em Laudon e Laudon4, temos caracterizado o aspecto hierárquico envolvido na organização de um banco de dados, indo desde o bit que se agrupa em bytes, os quais, por sua vez, compõem os campos. Os campos constituem um registro. Vários registros produzem finalmente um arquivo – o conjunto destes arquivos forma o banco de dados. É, ainda, O’Brien5 que se refere ao banco de dados como “um conjunto integrado de elementos de dados relacionados logicamente”. Baseando-nos nisso, podemos conceituar banco de dados (ou, abreviadamente, BD) como sendo um conjunto de dados com 14

banco_de_dados_

certa organização característica, com o objetivo de armazenamento persistente dos dados e dotado de mecanismos de manipulação para obtenção de informações e recuperação posterior, dentro de um sistema de informação. O banco de dados vem a ser uma representação dinâmica, visto que os dados podem sofrer alterações temporais. Podemos dizer que o BD procura ter em sua representação uma “imagem” de uma situação do mundo real constituída de objetos, das relações entre esses objetos e de eventos. A partir dessa imagem, o BD, então, tem condições de fornecer informações, evidenciando situações que podem ter importância para um processo de tomada de decisão, pois os dados podem ter representações diversas para uma mesma situação. É necessário que um BD tenha uma representação eficiente que possibilite acesso a informações corretas, em tempo hábil. Além disso, certos princípios devem ser levados em conta para se obter um BD eficiente. São eles: redundância, inconsistência e integração. 1. Redundância: o armazenamento dos dados de determinada empresa, ao longo de suas atividades, pode tender à redundância, ou seja, setores que dependem de informações comuns podem fazer a guarda dos mesmos dados simultaneamente. A falta de cuidado na análise do sistema de informações pode acarretar em redundância, incorrendo em custos de armazenamento. 2. Inconsistência: os dados armazenados referentes à determinada situação que apresente a possibilidade de sofrer alterações ao longo do tempo necessitam de atualização, uma vez que dados desatualizados podem gerar inconsistência de representação. Outro fator que também pode acarretar inconsistência é a redundância, pois dados armazenados em 15

princípios_e_prática_

locais diferentes podem sofrer alterações diferenciadas no transcorrer do tempo. A inconsistência, por sua vez, pode gerar tomada de decisões defasadas ou errôneas. 3. Integração: os dados existentes em um BD geralmente são compartilhados por várias pessoas ou setores em uma empresa. Assim surge a necessidade de integração, estabelecendo-se procedimentos para o acesso em vários níveis com a contínua atualização dos dados, de forma a manter a “imagem” do mundo real única e evitar ruídos na comunicação entre setores.

[breve histórico] Em termos de histórico de BD, podemos dizer que a forma de armazenamento físico dos dados está associada, desde o princípio da computação, aos meios de gravação existentes. Os primeiros computadores, comercializados nas décadas de 1950 e 1960, tais como o Univac ou os modelos IBM, utilizavam unidades seqüenciais de fita para gravação permanente dos dados6. Nesse período, a recuperação das informações era feita de maneira também seqüencial. Em termos de armazenamento lógico, as primeiras linguagens de programação implementavam funções e procedimentos de acesso aos dispositivos para gravação e leitura dos dados, não caracterizando um sistema próprio de banco de dados, sendo manipulados diretamente pelos sistemas desenvolvidos. Assim, linguagens como Assembler e Cobol possuíam direto, em seu conjunto de comandos, o acesso aos dados; o que permitia aos sistemas trabalhar com estruturas e operações primitivas para manipulação. Podemos de certa forma resumir o histórico de BD em cinco fases distintas que passaremos a detalhar na seqüência. 16

banco_de_dados_

1. A primeira fase surgiu com o advento das primeiras linguagens de programação pela necessidade de armazenagem dos dados processados na memória de forma permanente. Os primeiros computadores possuíam unidades de fita magnética que gravavam os dados de forma seqüencial, assim as primeiras linguagens trabalhavam com procedimentos ou funções embutidos no seu código, de forma que o programador tinha que desenvolver, além do próprio sistema ou aplicativo, os procedimentos para gravar ou ler dados do meio permanente. 2. A segunda fase, na década de 1970, caracterizou-se pelo surgimento de linguagens que traziam bibliotecas específicas para acesso a dados permanentes. Tal como a linguagem C – desenvolvida, em 1974, por Kernigham e Ritchie – que trabalhava com o conceito de cabeçalhospadrão*. Porém, a especificação dos arquivos não seguia um padrão ou formato predeterminado, sendo que os aplicativos ainda tinham a sua metodologia de acesso a dados permanentes de forma customizada. Não obstante, os primeiros modelos de bancos de dados relacionais surgiram naquela época com a pesquisa inicial de Codd**. A preocupação de Codd embasava-se na independência dos dados e na proliferação de tipos de dados em aplicações que ocasionavam inconsistências7. 3. Na terceira fase, final da década de 1970 e início de 1980, apareceram as primeiras padronizações para acesso a dados. As companhias de software forneciam junto com os pacotes de linguagem de programação o software responsável pelo tratamento de BD, com formatos de arquivo padronizados e linguagem específica de acesso. Tais sistemas foram chamados de

* Alguns desses cabeçalhos tratavam de especificar os tipos e funções para tratamento de arquivos. ** E. F. Codd foi um pesquisador britânico que publicou as primeiras contribuições para a teoria dos bancos de dados relacionais, enquanto trabalhava para a IBM.

17


sistemas de gerenciamento de banco de dados – SGBD, que trabalhavam proporcionando à linguagem os meios para acesso a BD. Os SGBD funcionavam como entidades separadas do sistema, permitindo que funções de armazenamento e recuperação de dados pudessem ser feitas em seu próprio domínio de gerenciamento. Porém, tais softwares de SGBD estavam ainda restritos ao ambiente de programação do fabricante. 4. Na quarta fase, advinda no final da década de 1980 e início da década de 1990, os SGBD foram tratados como softwares autônomos, sendo comercializados separadamente das linguagens de programação ou dos ambientes de desenvolvimento. O sistema operacional fornecia o padrão de comunicação entre o software e o SGBD. Nessa fase, um padrão de linguagem universal de acesso a BD, o Standard Query Language – SQL surgiu e permitiu que os fabricantes de SGBD fornecessem interfaces de acesso de forma declarativa. Assim a evolução da metodologia de programação e a sistematização do acesso a BD permitiram a separação entre o programa em si e os procedimentos de manipulação de BD. 5. É possível ainda citarmos uma quinta fase, na qual podemos inserir modelos avançados como BD orientados a objeto, os conceitos de BD distribuídos, além da aplicação de aspectos de Inteligência Artificial – IA a BD, como mineração de dados, data warehouses e sistemas de descoberta de conhecimento – KDD (Knowledge Discovery Data). Para mais detalhes sobre a história da computação, sugerimos as obras de Meyer8 e para aplicações de banco de dados e novas tendências, Elmasri9 e Date10.

[hierarquia do conhecimento] Do ponto de vista puramente físico, um arquivo nada mais é do que uma seqüência de 0´s e 1´s gravada em um meio de armazenamento estático. 18

banco_de_dados_

A seqüência de bits é ininteligível do ponto de vista do tratamento com os dados, considerado, esse, o primeiro nível ou o mais baixo no tratamento de dados na hierarquia do conhecimento. Num segundo nível, quando consideramos uma seqüência de 8 bits, podemos identificar um dígito ou caractere Ascii* e, assim, a informação começa a fazer um certo sentido. Em vez de 0´s e 1´s, temos agora seqüência de caracteres padrões codificados de 8 em 8 bits. No exemplo (Figura 1.1), a seqüência de bits 01100001 corresponde ao número 61 hexadecimal ou 97 decimal. Pela tabela Ascii**, o 97 corresponde ao caractere ‘a’. Figura 1.1 – Seqüência de bits / dígito Ascii

0

1

1

0

0

0

0

1

→

61

→

97

→

a

As seqüências de dígitos ou caracteres agrupadas, num terceiro nível, formam os dados (Figura 1.2). Caso tal agrupamento seja quebrado, perde-se o sentido do mesmo. Podemos dizer que, dessa forma, temos os dados caracterizados como “átomos”, em termos de indivisibilidade. O nome próprio de uma pessoa (vamos exemplificar com MARIA) não fará nenhum sentido se for separado em duas partes (MAR e IA). Figura 1.2 – Seqüência de dígitos Ascii

P

a

r

a

f

u

s

o

* Ascii: sigla de American Standard Code for Information Interchange, que se constitui em um código numérico usado para representar os caracteres e é entendido por quase todos os computadores. ** Um modelo de “tabela Ascii” pode ser visto no anexo desta obra.

19


Os dados isolados, no entanto, não identificam bem elementos ou entidades da vida cotidiana que necessitamos trabalhar, quando dados de diferentes naturezas precisam ser armazenados, como o endereço de um cliente (nome, endereço, complemento, cidade, estado, CEP), o saldo de uma conta bancária (cliente, conta, débito, crédito) ou a quantidade fabricada em uma linha de produção (produto, código, quantidade, custo). Dessa forma, num quarto nível temos os dados ou átomos agrupados – mesmo chamando tais grupos de “moléculas” –, possibilitando que mais tarde, em conjunto ou em confrontação com outros conjuntos de dados e a transformação dos mesmos, venham a produzir o que chamamos de “informação”. A informação, que podemos considerar o quinto nível, diz respeito a algo novo, a partir do sentido isolado dos dados ou átomos e de grupos de dados inseridos num certo contexto. Já os arquivos com a característica de um BD referem-se a uma seqüência de dados ou átomos de diferentes naturezas (Figura 1.3), armazenados conforme uma disposição pré-definida muitas vezes denominada de cabeçalho ou estrutura. Figura 1.3 – Seqüência de dados nome

endereço

telefone

cidade

Maria

Rua das Camélias

3222-3300

Curitiba

salário 2.500,00

Assim, ao especificarmos a informação como o quinto nível da hierarquia, o acervo formado pela geração de informações nos processos de gestão, devidamente filtradas e sistematizadas ao longo do tempo em um certo ambiente, tal como um sistema de informação de uma empresa, constitui o conhecimento que compõe, dessa forma, o sexto nível da pirâmide. Podemos, ainda, elaborar um sétimo nível, onde o conhecimento gerado pelas informações, sendo manipulado por pessoas ou sistemas com vistas 20

banco_de_dados_

a atender certos objetivos, constitui a inteligência. Neste último nível da hierarquia de conhecimento, o processo de tomada de decisão faz uso de todo o edifício elaborado, desde a estrutura simplificada dos bits até a ponte com o pensamento humano ou mesmo de um agente de software utilizando inteligência artificial. Observamos, portanto, que dentro dessa pirâmide, os processos de BD representam um importante fundamento aos procedimentos de mais alto nível necessários para a vida das organizações. Existe uma série de representações da hierarquia do conhecimento. O leitor pode obter mais pesquisando, por exemplo, sobre sistemas de conhecimento em Rezende11, e sobre introdução à gestão do conhecimento em Turban, Rainer Júnior e Potter12. Figura 1.4 – Representação da hierarquia do conhecimento

Inteligência Conhecimento Informação Grupos de Dados / Moléculas Dados / Átomos Dígitos Bits

[registro, atributo, campo, item e tabela] Ampliando o conceito de cabeçalho ou estrutura, fornecidos no tópico anterior, a melhor maneira de visualizarmos um registro é através de uma lista ou relação. 21


Numa lista, os dados referentes a certo contexto serão colocados em colunas, um abaixo do outro, e as colunas dos dados de diferentes naturezas são colocadas uma ao lado da outra. Como exemplo, a lista a seguir consta de quatro colunas. A primeira refere-se a um número seqüencial, a segunda ao nome de um produto, a terceira à quantidade do mesmo e a quarta ao valor referente ao custo de fabricação. Logo os dados referentes a cada um dos registros têm naturezas diferentes, e na primeira linha, estão os nomes das colunas. Figura 1.5 – Exemplo de lista ou tabela codigo

nome

1

Parafuso

2

Chave de fenda

3

Prego

quantidade

custo 1.000

0,05

20

4,50

2.000

0,02

O registro é identificado como uma linha da lista, com conteúdo em cada tipo de dado representado pela coluna. Temos, então, três registros nessa lista. A primeira coluna identifica o código de cada um dos registros de forma numérica e seqüencial. A lista é denominada de tabela e possui um nome ou identificador próprio. Um banco de dados de certo sistema pode ser constituído de várias tabelas, e estas possuem uma estrutura padronizada composta por atributos ou campos: código, nome, quantidade e custo. Esse conjunto de atributos ainda é referenciado como o esquema da tabela. Cada valor em um registro referente a um atributo ou campo é denominado de item de um registro. Ilustramos, a seguir, o armazenamento físico dos dados acima em termos de bytes: 22

banco_de_dados_

Figura 1.6 – Armazenamento físico de dados 0

0

0

1

P

a

r

a

f

u

s

o

0

0

3

E8

0

0

0

0

5

0

0

0

2

C

h

a

v

e

d

e

F

e

0

0

0

20

0

0

0

4

32

0

0

0

3

P

r

e

g

o

0

0

7

D0

0

0

0

0

2

n

d

a

Desde que consideremos para nomes os campos com 15 bytes, campos numéricos inteiros com 4 bytes e campos de números de moeda com 5 bytes, a representação física (apenas dos dados), que você viu acima, pode ser considerada. Notamos, ali, que os dados estão dispostos como numa fila de bytes. O tamanho total em bytes do registro que estamos considerando é de 28 (dois campos numéricos inteiros de 4 bytes, um campo string* de 15 bytes e um campo moeda de 5 bytes). É importante verificar que os valores inteiros são convertidos na representação hexadecimal para depois serem armazenados. Com 4 bytes, e cada bit guardando um número na faixa de 0 a 255, existe a possibilidade de guardar valores inteiros positivos numa faixa de 0 a 4.294.967.296 (considerando números cardinais ou não-negativos). Assim, no primeiro registro o número 1.000 (em hexadecimal 3E8) é armazenado em 2 bytes e com os outros 2 contendo zeros: 00 00 03 E8. A seguir, temos a representação da fila de bytes convertidos em caracteres Ascii hexadecimais. Figura 1.7 – Bytes convertidos em caracteres Ascii 0

0

0

1 50 61 72 61 66 75 73 6F 20 20 20 20 20 20 20 0

0

0

0

0

0

0

0

2 43 68 61 76 65 20 64 65 20 46 65 6E 64 61

20 0

0

0 14 0

0

0

4 32 0

0

0

3 50 72 65 67 6F 20 20 20 20

20 20 20 20 20 20 0

0

7 D0 0

0

0

0

5

0

3 E8

2

* Cadeia de caracteres.

23


Desse modo, cada caractere é convertido em seu correspondente hexadecimal Ascii, quando consideramos strings. Em geral, os campos string em BD correspondem a itens com tamanho máximo de 255 caracteres, embora possam ser variáveis em outros casos. No exemplo anterior, devemos considerar que manipulamos uma estrutura de registro de tamanho fixo, ou seja, todo e qualquer registro gravado no arquivo sempre terá um tamanho de 28 bytes. Se quisermos identificar onde começa o segundo registro, é só nos deslocarmos até a posição 29 do arquivo, pois os próximos 28 bytes referem-se a ele. Ou, então, de forma genérica, se quisermos saber onde começa o registro n, basta fazermos np + 1, onde p é igual ao tamanho do registro. Na maioria dos sistemas de BD atuais, trabalhamos também com o conceito de registro de tamanho variável, sendo que os espaços em branco (caractere Ascii 20) são suprimidos, permitindo, dessa maneira, um aproveitamento maior do dispositivo de armazenamento. O conceito de registro está presente nas linguagens de programação, representando uma estrutura de dados heterogênea, ou seja, composta de vários tipos. Se quiséssemos expressar em linguagem Pascal, por exemplo, a definição da estrutura acima colocada, poderíamos ter na cláusula type, no início de uma unit, a definição de um record, seguido de uma declaração de variável na cláusula var: type PRODUTO = record Codigo: integer; Nome: string; Quantidade: integer; Custo: real; end; var PROD: PRODUTO;

24

banco_de_dados_

Podemos dizer que o código acima trabalha com o modelo lógico de registro. Caso queiramos ler um arquivo com essa estrutura, precisamos de um procedimento especial, montado de forma a ler o conteúdo do arquivo. Uma proposta para isso é: Procedure Ler; Var F: File; Begin Assign(F, ’PRODUTO’); Reset(F); While Not Eof(F) Do Begin ReadLn(F, Prod); Write(Prod.Codigo); Write(Prod.Nome); Write(Prod.Quantidade); Write(Prod.Custo); WriteLn; End; Close(F); End;

O que podemos entender do algoritmo colocado acima é que cada registro é lido dentro do loop while e mostrado na tela. Ou seja, é necessário especificar todo o procedimento preciso para listarmos o conteúdo do arquivo ou seus registros. As metodologias utilizadas atualmente em softwares de BD permitem que tal esforço seja economizado em termos de programação; pois, desde que fornecidas as interfaces adequadas, o processo inteiro de uma listagem ou consulta (no inglês, query) pode ser obtido com apenas uma declaração SQL simples. Podemos, assim, enviar o seguinte comando, para uma interface de um SGBD, desde que a estrutura da tabela (o modelo lógico) esteja criada: SELECT * FROM produto;

Após a execução, obtemos o resultado da consulta já formatado, como na figura a seguir. 25


Figura 1.8 – Resultado da consulta codigo

nome

1

Parafuso

2

Chave de Fenda

3

Prego

quantidade

custo 1.000

0,05

20

4,50

2.000

0,02

O comando SELECT indica que está sendo feita uma consulta, o asterisco indica que devem ser listados todos os atributos ou campos e a cláusula FROM seguida do nome da tabela PRODUTO indicam a origem dos dados. Portanto, como você percebeu, não é necessário fornecer nenhuma informação a respeito do tipo dos dados que queremos, nem do tamanho de cada um, nem mesmo interessa como os dados são guardados.

[sgbds e sistemas de informação] Como foi visto anteriormente, um SGBD é o responsável por todas as tarefas pertinentes ao armazenamento, à recuperação, à segurança e ao gerenciamento dos dados. Na atualidade, podemos verificar a existência de vários SGBDs no mercado. Um sistema de informação é desenvolvido de forma conjunta com um SGBD, pois, enquanto um sistema de informação está encarregado do processamento da informação em si, o SGBD tem a tarefa do gerenciamento da armazenagem da informação. A partir de um modelo de dados requisitado* para o suporte a um sistema de informação, qualquer SGBD deve ser capaz de permitir a implementação

* Tal modelo de dados pode ser obtido através do uso de ferramentas CASE – Computer-Aided Software Engineering.

26

banco_de_dados_

desse modelo em um conjunto de estruturas, bem como permitir modificações e consultas eficientes aos dados armazenados. Os SGBDs fornecem uma interface de conexão ao banco de dados, a qual permite a comunicação do sistema de informação com o mesmo. Nesse processo de conexão, um usuário requisita os processos de um sistema de informação, este, em função disso, interage com o SGBD emitindo solicitações de consultas ou modificações, e o SGBD retorna os dados necessários. Além da interação com o usuário, durante a atividade de desenvolvimento de um sistema de informação, que pode ser feito numa ferramenta RAD*, a interface permite o acesso aos dados em tempo real, otimizando bastante o processo de desenvolvimento. Com o advento da internet, os sistemas de informação romperam a barreira das redes locais e internas das empresas para disponibilizarem informações de forma global na web. Qualquer cliente de uma empresa pode acessar a página da mesma e comprar produtos remotamente, em qualquer parte do mundo. Os SGBDs foram, portanto, adaptados para contemplarem essa possibilidade de conexão de bancos de dados com sistemas na web. A operação de tais sistemas – seja em redes intranet, seja extranet ou, ainda, internet – ficou facilitada para os usuários e permitiu grande economia para as companhias em função da diminuição da redundância. Empresas com filiais ao redor do mundo podem compartilhar dados a partir de um único local físico, onde se encontram os servidores.

* Rapid Application Development, tais como o Delphi (Borland) e Visual C ou Visual Basic (Microsoft).

27


O advento da internet também influiu no desenvolvimento de SGBDs mais seguros e confiáveis, visto que as páginas colocadas na web têm visibilidade irrestrita, ou seja, podem ser acessadas por qualquer pessoa, o que corresponde ao que desejam as empresas, pois procuram maximizar a aquisição de mais clientes em suas operações. Com a difusão dos sistemas de informação em variadas plataformas, desenvolvidos em ferramentas Case ou em ambientes de desenvolvimento diversos – suportados por SGBDs de diferentes fabricantes –, a profusão de dados é exponencialmente grande e ainda continua crescendo. A aquisição de novos sistemas por parte de uma empresa, com novas tecnologias em relação ao que ela já possui, faz com que, na maior parte das vezes, ela compartilhe diferentes sistemas de informação em um mesmo período de tempo. Costuma-se denominar os sistemas antigos e existentes na empresa como sistemas legados. Para que os diferentes sistemas de informação compartilhem uma mesma base de dados, é necessário conjugar os diferentes SGBDs ou mesmo pastas de tabelas em arquivos em um único local. Em certas situações, é até possível considerar a redundância em certos conjuntos de dados, que poderão ser tratados e filtrados mais tarde*. Em relação a essa situação, o conceito de data warehouse atende a expectativa, no sentido de agrupar uma grande quantidade de dados localizados em diferentes fontes (inclusive fisicamente distantes) em um único repositório. Os data warehouses, de acordo com a conceituação de Inmon13, são um conjunto de dados granulares integrados, armazenando * Tal tratamento pode ser a uniformização de dados, como, por exemplo, gravar todos os nomes de clientes em caixa-alta, sempre separar o código postal com um hífen etc., dependendo da definição da forma de armazenagem dos dados. Diz-se também que os dados em um data warehouse podem estar não-normalizados.

28

banco_de_dados_

e gerenciando os dados em um certo período de tempo, que podem ser resumidos ou agregados para a criação de novas formas de dados. A partir da formação de um data warehouse, que disponibiliza, por sua vez, uma grande quantidade de dados, fica possível por parte da empresa a busca de certas informações referentes a padrões nos dados que se repitam num certo período de tempo. Por exemplo, pode ser constatado, num sistema de CRM*, um padrão de comportamento de um certo grupo de clientes, numa faixa etária bem definida, que compram determinado produto em um período específico de tempo. Tal informação pode ser útil para que a empresa defina políticas de marketing direcionadas para esse grupo de clientes**. É importante ressaltar que tal padrão é invisível em primeira mão, não pode ser captado simplesmente a partir das funções específicas do sistema de informação, mas apenas após seu agrupamento em um repositório (via data warehouse) e abarcando por sua vez um grande período de tempo. Isso permite, então, a identificação sistemática de padrões subjacentes aos dados, e tal processo consiste nos procedimentos da mineração de dados ou data mining. “No data mining, os dados de um data warehouse são processados para identificar fatores e tendências-chave nos padrões das atividades de negócios”14, segundo afirma O’Brien, pois a mineração de dados constitui-se e de diferentes técnicas que podem ser aplicadas a um conjunto de dados para a extração de padrões. Assim, embora a mineração de

* Sigla de Customer Relationship Management, um sistema com o objetivo de gerir relacionamentos da empresa com seus clientes. ** O clássico que se refere à correlação entre a compra de fraldas e de cervejas, que acontece em supermercados, próximo ao final de semana, é um ótimo exemplo de identificação de padrão.

29


dados seja um campo relativamente novo na área de ciência da computação, pode possibilitar às empresas a identificação de boas oportunidades de negócios a partir dos dados armazenados em seus SGBDs.

[resumo] Banco de dados, podemos, portanto, conceituar como sendo conjuntos de dados com certa organização definida, dotado de procedimentos para manipulação dos dados e com o objetivo de armazenamento e posterior recuperação de dados. Em relação ao desenvolvimento do BD, consideramos alguns aspectos fundamentais e os sintetizamos a seguir. _ Nesse processo, três princípios devem ser levados em conta: redundância, inconsistência e integração. _ Em termos de histórico é possível enumerarmos cinco fases da evolução dos BD, desde o seu início até os dias atuais; já na hierarquia do conhecimento, podemos mostrar como os bits transformam-se em dados e conhecimento até chegar ao nível da “inteligência” das empresas. _ Outros conceitos importantes são registro, item de registro, campo ou atributo, tabela e esquema. _ Os registros podem ser de tamanho fixo ou variável e, ainda, serem descritos em termos de seu armazenamento físico ou mesmo considerando o seu modelo lógico. _ O uso de linguagens padrão para consultas de BD, como SQL, simplificam bastante o processo de obtenção ou manipulação dos dados em um SGBD.

30

banco_de_dados_

_ SGBDs trabalham em conjunto com sistemas de informação, fornecendo a conexão aos dados e às ferramentas de suporte da gestão dos dados. _ A internet permitiu aos SGBDs adaptarem-se às novas possibilidades de sistemas de informação via web, mas também evidenciou certas necessidades de segurança. _ Data warehouses são importantes para a unificação de sistemas novos e legados, e podem ser o início da adoção de processos de data mining por parte de uma empresa, para a identificação de padrões ocultos e repetitivos no tempo.

[exercícios] 1. Defina banco de dados. 2. Cite os princípios que devem ser considerados para termos um BD eficiente e dê um exemplo real do impacto de um deles. 3. De que forma trabalhavam as primeiras linguagens em termos de manipulação dos dados? 4. O que é um SGBD? 5. Faça um resumo sobre a hierarquia do conhecimento. 6. Conceitue registro. 7. Conceitue atributo ou campo. 8. Conceitue item de registro. 31


9. Conceitue tabela. 10. Diferencie registros de tamanho fixo e variável. 11. Por que é mais simples usar uma consulta SQL em vez de um algoritmo para ler dados em um arquivo/tabela? 12. Qual o impacto do advento da internet sobre os SGBDs? 13. O que é data warehouse? 14. Descreva uma situação em que certo tipo de dado precisa ser uniformizado em um data warehouse. 15. O que é data mining? 16. Dê um exemplo de um padrão repetitivo subjacente de dados.

32

banco_de_dados_

0000_0010 = II

o_modelo entidaderelacionamento (E-R)_

Em 1976, Chen introduziu a idéia de um modelo de entidade-relacionamento para representar bancos de dados1. O modelo baseia-se em uma descrição dos dados com maior ênfase nos aspectos semânticos de representação, não sendo necessário compreender o modelo lógico subjacente. Os modelos ao longo dos anos sofreram variações2, porém o modelo de Chen é um dos mais difundidos. Apesar do modelo entidade-relacionamento (ou modelo E-R) não ser implementado em SGBDs, tal como o modelo relacional, ele apresenta um bom ponto de partida para a compreensão entre os elementos existentes em um determinado contexto e as relações entre os mesmos. De certa forma, ele antecede o projeto lógico que pode ser feito em um modelo relacional, o qual através de regras para conversão pode ser montado a partir de um diagrama E-R, desde que este esteja bem definido. Os sistemas de BD, em geral, não possuem uma “compreensão” estendida do significado de certos valores de atributos. Tipicamente, os BD têm apenas uma compreensão limitada a certos valores atômicos simples e alguns vínculos de integridade simples aplicados a esses valores, mas qualquer interpretação adicional tem que ser dada pelo ser humano. Como exemplo, seria interessante se numa consulta ao BD

pudesse ser entendido que pesos de peças e quantidades de remessas, embora sejam ambos valores numéricos, são de espécies diferentes, ou seja, semanticamente distintos. Portanto uma comparação direta entre peso e quantidade deveria ao menos ser questionada, se não rejeitada de todo pelo modelador. Passaremos agora aos conceitos envolvidos com a abordagem E-R para modelagem de bancos de dados. Durante a exposição do conteúdo, daremos preferência à abordagem de design e de modelagem de entidade-relacionamento encontrada em Heuser3.

[entidade] Entidades são elementos ou objetos perfeitamente distinguíveis. No processo de modelagem E-R, as entidades são os primeiros elementos a serem considerados por estarem explícitos ou evidentes. Esses elementos podem ainda representar algo concreto – como, por exemplo, uma pessoa ou um produto – ou abstrato – tal como uma data ou mesmo uma seção de uma empresa. Uma entidade também pode ser vista como sendo pessoal (empregado, funcionário), local (endereço, cidade, estado), objeto (produto, matéria-prima), evento (venda, registro, cadastramento) ou entidade conceitual (seção, conta). A representação é feita através de retângulos (Figura 2.1) contendo no seu interior o nome das entidades. Figura 2.1 – Representação de entidades FUNCIONÁRIO

PRODUTO

36

banco_de_dados_

SEÇÃO

Assim, quando simbolizamos a entidade “funcionário”, não quer dizer que se trata de um funcionário específico, mas de um conjunto de funcionários, portanto ela pode ser valorada pelos elementos do conjunto que representa. Contudo, quando falamos de um elemento ou dado referente a uma entidade, diz-se que tal dado representa uma instância desta entidade. Por conseguinte ainda que tenhamos outros conceitos, que são vistos adiante, envolvidos com a abordagem E-R, como atributos, relacionamentos e subtipos; tais conceitos devem ser vistos como propriedades das entidades, as quais são assumidas conforme a necessidade da modelagem E-R.

[atributos] Atributos são as características de uma entidade. Eles podem ter uma faixa de valores ou de domínio e, ainda, caracterizarem-se por serem atômicos (simples) ou não-atômicos (compostos). Assim, apesar da possibilidade de fazermos o contrário, devemos sempre procurar construir atributos simples. Podemos, inclusive, ter atributos identificadores ou, então, chaves que indiquem uma entidade sem ambigüidade, bem como atributos básicos ou derivados, como por exemplo, a quantidade total para um determinado produto ser resumido a partir da soma das peças separadamente para este produto. Date4 dá preferência ao uso do termo propriedade em vez de atributo. A representação de atributos é feita com elipses ligadas à entidade (Figura 2.2) ou com círculos, e a identificação do atributo é colocada ao lado do mesmo.

37


Figura 2.2 – Representação de atributos

CÓDIGO

ENDEREÇO

NOME

FUNCIONÁRIO

CÓDIGO NOME

FUNCIONÁRIO

ENDEREÇO

Os atributos podem ser: _ univalorado ou multivalorado: no caso de o atributo assumir um único valor ou, então, quando assume mais de um valor; _ vazio: quando um atributo em determinado momento não assumir um dado específico ou ser desconhecido (semelhante ao valor NULL existente em SQL); _ chave ou identificador: um atributo pode unicamente representar uma instância de uma entidade, situação em que ele é simbolizado como identificador ou chave mediante o nome sublinhado ou com o círculo do atributo preenchido.

[relacionamento] Através de um relacionamento, duas ou mais entidades podem estar associadas, situação em que elas são representadas por losangos ligando uma entidade a outra (Figura 2.3). Portanto um relacionamento é uma associação entre entidades, em que as pertencentes a um relacionamento se dizem participantes do mesmo. 38

banco_de_dados_

Figura 2.3 – Representação de relacionamentos

CLIENTE

CARTEIRA

PEDIDO

Como visto, os relacionamentos podem ter um nome descrito no losango. Nesse exemplo, temos duas entidades envolvidas: “cliente” – representando o conjunto de pessoas que são vistas como clientes de uma empresa – e “pedido”, sendo este o conjunto de pedidos que são efetuados pelo cliente na empresa. O relacionamento denominado “carteira” refere-se à associação de elementos da entidade “pedido” que, por sua vez, estão associados a seus respectivos elementos representados pela entidade “clientes”. Quanto aos relacionamentos, podemos considerar a cardinalidade, o tipo de relacionamento e a obrigatoriedade de participação. Heuser exemplifica também relacionamentos sem nomes explícitos5. Cardinalidade A cardinalidade ou multiplicidade define a quantidade de elementos de uma entidade associada com a quantidade de elementos de outra entidade. Podemos ter relações de 1:1 (um para um), 1:N (um para N) e N:N (N para N). Por exemplo, a cardinalidade para o relacionamento representado na Figura 2.3 pode ser visto como 1:N, isto é, um cliente pode possuir vários pedidos, porém um pedido pode ser somente de um único cliente. Figura 2.4 – Representação de relacionamentos e cardinalidade

CLIENTE

CARTEIRA 1

PEDIDO N

39


Na Figura 2.4, vemos, então, que do lado da entidade “cliente” aparece o número “1” e do lado da “pedido” aparece a letra “N”. Para a interpretação da cardinalidade, um artifício que auxilia na identificação da mesma é a elaboração de um diagrama de ocorrências. Num diagrama de ocorrências, representamos as entidades e relacionamentos, na forma de conjuntos, bem como os elementos pertencentes a cada conjunto. No caso das entidades, representamos os elementos individualmente e, no caso do conjunto do relacionamento, representamos os pares de elementos associados entre si*. Figura 2.5 – Diagrama de ocorrências para o exemplo de cardinalidade 1:N CLIENTE

CARTEIRA

c1

PEDIDO

p1 c1,p1

p2 c2,p2 c2 p3 c2,p3

c3

p4 c2,p4

Dessa forma, os pares c2, p2 e c2, p3 indicam que um cliente pode possuir mais de um pedido. Porém, não ocorre um par, onde, para um mesmo pedido, tenhamos mais de um cliente.

* Um diagrama de ocorrências, de certa forma, antecipa a visualização de como estariam os dados associados entre si, em termos de um registro, em uma tabela de um banco de dados.

40

banco_de_dados_

No caso de um relacionamento com cardinalidade N:N, entre as entidades “funcionário” e “projeto”, denominado “alocação” (veja a Figura 2.6), um funcionário pode estar alocado em mais de um projeto (veja o caso dos pares f2, p2 e f2, p3), e um projeto, por sua vez, pode ter mais de um funcionário (ver os pares f1, p1 e f3, p1). Figura 2.6 – Diagrama de ocorrências para o exemplo de cardinalidade N:N

FUNCIONÁRIO

ALOCAÇÃO N

FUNCIONÁRIO

PROJETO N

ALOCAÇÃO

f1

PROJETO

p1 f1,p1

f3,p1

p2

f2 f2,p2 p3

f2,p3 f3

p4 f3,p4

Para o caso de um relacionamento de cardinalidade 1:1, no exemplo a seguir, temos as entidades “empregado” e “cargo”, e o relacionamento “ocupação” indicando a associação entre as entidades (Figura 2.7). Nesse caso, as ocorrências do diagrama indicam que um empregado pode ocupar apenas um cargo e vice-versa (um cargo não pode ter dois empregados). 41


Figura 2.7 – Diagrama de ocorrências para o exemplo de cardinalidade 1:1

EMPREGADO

OCUPAÇÃO 1

EMPREGADO

CARGO 1

OCUPAÇÃO

e1

CARGO

c1 e1,c1

e2 e2,c3

e3

c2 c3

e3,c4

Quanto à denominação de um relacionamento, não há uma regra para atribuirmos um nome específico. Em vez de “ocupação”, poderíamos denominar de “cargo do empregado” ou “cargo_empregado”. Tipo de relacionamento Um relacionamento pode ser, de acordo com o número de entidades que participam na relação, unário, binário ou ternário. 1. O relacionamento binário (associando duas entidades) já foi abordado anteriormente, quando da explicação do conceito de relacionamento, sendo que nos concentraremos agora nos relacionamentos unário e ternário. 2. O relacionamento do tipo unário, é identificado também como um auto-relacionamento, em que a entidade participante relaciona-se com 42

banco_de_dados_

ela mesma. Isso não implica que um registro dessa entidade esteja relacionado com ele mesmo. Vejamos o caso de um produto em fabricação. De acordo com o grau de montagem, um conjunto de peças é montado de forma a produzir uma peça mais complexa, mas que não é ainda o produto final. Essa peça complexa se junta a outras peças ou a matérias-primas, de forma, então, a compor o produto final. Todas essas peças são, aqui, representadas por uma entidade “peça”, sendo o relacionamento denominado “parte de”. O conceito de papel também é importante para a definição de um relacionamento unário, para que possamos entender como as instâncias estão associadas. No caso da peça ou peças que “compõem” outra peça, e da peça que é “composta por” outras peças, temos os dois papéis (o do que contém e do que está contido). A definição dos papéis auxilia posteriormente na determinação da cardinalidade do relacionamento. Os papéis devem ser explicitados no diagrama do relacionamento ao lado das ligações. Pela Figura 2.8, vemos que existem os pares p1, p2 e p3, p2, indicando que a peça p2 é composta pelas peças p1 e p3. Por sua vez, vemos também que p4 é composta pela peça p2. A cardinalidade N:1 indica que mais de uma peça pode compor outra peça. Essa representação ilustra bem a estrutura de materiais, também chamada Bill Of Materials (BOM) para certa linha de produção, o que é necessário em um programa de Planejamento das Necessidades de Materiais (Materials Requirement Planning – MRP). Outros exemplos de relacionamentos unários são os “empregado-chefia” ou “contas-subcontas” (tal como num plano de contas contábil).

43


Figura 2.8 – Diagrama de ocorrências para o auto-relacionamento PEÇA Composta por

compõem

PARTE_DE

PEÇA

PARTE_DE

p1

p1,p2

p2

p2,p4

p4

p3

p3,p2

Figura 2.9 – Exemplo de relacionamento ternário

FUNCIONÁRIO

1

1

ÁREA

OCUPAÇÃO

N PROJETO

Um relacionamento ternário implica a associação de três entidades ao mesmo tempo, que pode ser exemplificado por uma associação “funcionário-área-projeto”; desde que um funcionário trabalhe apenas em uma área, 44

banco_de_dados_

porém em mais de um projeto. O relacionamento ilustrado (Figura 2.9) mostra como ficaria esta associação e o relacionamento “ocupação”. Figura 2.10 – Diagrama de ocorrências para o exemplo de relacionamento ternário FUNCIONÁRIO

PROJETO

f1

p1

f2

p2

p3 f1,p1,a2

f3

p4 f2,p2,a1

ÁREA

f2,p3,a1

a1

f3,p4,a2 OCUPAÇÃO

a2

O diagrama de ocorrências mostra, agora, não mais os “pares”, mas “ternos” ou “triplas” mostrando as instâncias associadas nesse modelo (Figura 2.10). Veja que os ternos f2, p2, a1 e f2, p3, a1 validam a cardinalidade de 1:1 para a associação “funcionário-área” e 1:N para a associação “funcionário-projeto”. 45


É possível, ainda, representarmos relacionamentos contendo mais de três entidades, o que caracteriza relacionamentos quaternários e subseqüentes. Obrigatoriedade Em certos relacionamentos entre entidades podem aparecer situações onde a presença de uma entidade não é obrigatória. Um bom exemplo é o relacionamento “empregado-dependente” na figura abaixo. Figura 2.11 – Relacionamento empregado-dependente

EMPREGADO

FILIAÇÃO 1

DEPENDENTE N

O empregado pode ter dependentes ou não. Para representar isso num diagrama E-R, expandimos o conceito de cardinalidade para mínima e máxima. _ Quando um empregado não possuir dependentes, caracterizamos a cardinalidade mínima para 0 (zero) do lado da entidade dependente. Como a entidade empregado sempre participa do relacionamento, a cardinalidade mínima será 1 (um) do lado do empregado. _ Quanto à cardinalidade máxima (mantemos o que foi especificado anteriormente), do lado do dependente será N, pois um empregado pode ter vários dependentes. E a cardinalidade máxima para empregado será obviamente 1 (um dependente só pode estar relacionado a um empregado). _ Para representar agora as cardinalidades mínima e máxima, utilizamos o par: min e max. Assim, do lado do empregado, a representação 46

banco_de_dados_

das cardinalidades será (1, 1) e do lado do dependente será (0, N), conforme a Figura 2.12. Da entidade que participa num relacionamento em que não seja obrigatória a presença, diz-se que é uma entidade fraca. Assim, elas podem ser divididas em fortes e fracas. A entidade fraca também é representada na literatura como sendo um retângulo com linha dupla incluindo a ligação com o relacionamento. Figura 2.12 – Relacionamento empregado-dependente representando obrigatoriedade

EMPREGADO

FILIAÇÃO (1,1)

EMPREGADO

DEPENDENTE (0,N)

FILIAÇÃO

e1

DEPENDENTE

d1 e1,d1

e2

d2 e1,d2

e3

d3 e2,d3

e4

d4 e3,d4

e5

d5 e3,d5

Na figura acima, você vê a representação do diagrama de ocorrências. Como nele, a entidade “dependente” possui cardinalidade mínima 0 (não há obrigatoriedade), existem instâncias de “empregado” que não 47


estão associados a qualquer instância de “dependente” (e4 e e5). Note também que todas as instâncias de “dependente” estão associadas a suas respectivas instâncias de “empregado”.

[atributos de relacionamento] Assim como uma entidade pode possuir atributos, um relacionamento também pode ter atributos que não ficariam bem localizados nas suas entidades associadas. No relacionamento “funcionário-projeto” (Figura 2.13), usaremos um atributo, a ser colocado, chamado tempo para guardar as horas trabalhadas pelo funcionário no projeto específico. Se o atributo “tempo” ficar ligado à entidade “funcionário”, a informação referenciada nesse atributo será relativa ao funcionário, não importando o projeto. Por outro lado, se o atributo “tempo” ficar ligado à entidade “projeto”, ele é compreendido mais como o tempo trabalhado no respectivo projeto, não importando qual funcionário trabalhou. Portanto, se for ligado a qualquer uma das entidades, o atributo não atinge o objetivo, sendo, em nosso exemplo (Figura 2.13) necessário colocar o atributo “tempo” ligado ao relacionamento “alocação” para dessa forma significar as horas trabalhadas pelo funcionário no projeto específico. Figura 2.13 – Exemplo de atributo de relacionamento

FUNCIONÁRIO

ALOCAÇÃO N TEMPO

48

banco_de_dados_

PROJETO N

[generalização/especialização] Também chamada de subtipo, a generelização/especialização permite que uma entidade se diferencie em vários tipos. Por exemplo, se alguns empregados são programadores, e todos os programadores são empregados, então, podemos dizer que “programador” é um subtipo do supertipo “empregado”. nessa situação, se analistas também existem como empregados, então “analista” também será um subtipo do supertipo “empregado” (Figura 2.14). Podemos, portanto, discutir a existência de herança entre entidades, bem como a de uma hierarquia de tipos, porquanto, com a generalização/especialização, a entidade caracterizada como supertipo assume as propriedades do subtipo em determinadas ocorrências. A representação de generalização/especialização num diagrama E-R se faz com um triângulo invertido. A aqui utilizada (representação) se deve a Korth e Silberschatz7, mas outras formas também são encontradas em Heuser8. Figura 2.14 – Exemplo de generalização/especialização EMPREGADO

PROGRAMADOR

ANALISTA

A herança entre as entidades pressupõe que uma entidade subtipo ou filha pode herdar as propriedades da que é supertipo ou pai. Como propriedades são compreendidos os atributos e relacionamentos da entidade-pai.

49


No diagrama da Figura 2.15, vemos um exemplo onde (no contexto de um sistema de livraria) a entidade “cliente” está associada à entidade “mídia” através do relacionamento “venda”. Veja que pelo diagrama, “mídia” pode assumir uma ocorrência da entidade-filha “livro” ou uma ocorrência da outra entidade-filha “revista”. Veja que “mídia” possui o atributo identificador “ID” e “nome”, que identifica, por sua vez, uma instância de “mídia”. Atributo que irá servir tanto para “livro” quanto para “revista”. Agora, quando “mídia” assume a ocorrência da entidade-filha “livro”, esta irá herdar os atributos que pertencem à mídia (ID e nome) mais os seus atributos específicos (ou seja, ISBN e ano). Quando “mídia” assume a ocorrência da entidade-filha “revista”, esta irá herdar os atributos de “mídia” (ID e nome) mais os seus atributos específicos (que agora são ISSN e periodicidade). Além disso, as entidades-filha irão herdar também o relacionamento “vendas” que existe com a entidade “cliente”. Figura 2.15 – Exemplo de generalização/especialização com atributos e relacionamento QUANTIDADE (1,N) CLIENTE

VENDA (1,N)

ID NOME

ID MÍDIA PREÇO

NOME

ISBN

ISSN LIVRO

ANO

REVISTA PERIODICIDADE

Num diagrama E-R, contemplando generalização/especialização, podemos também fazer com que existam vários níveis, uma vez que um subtipo pode ser ao mesmo tempo um supertipo e assim há um novo 50

banco_de_dados_

nível de hierarquia de tipos. O exemplo da Figura 2.16 pode ser estendido de forma a ilustrar a inclusão de mais um nível. Veja que agora mídia pode se projetar na entidade-filha “filme”. Esta, por sua vez, é a entidade-pai de outras duas entidades: “VHS” e “DVD”. Note que o atributo duração está colocado na entidade-pai “filme”, pois tanto “VHS” quanto “DVD” possuem certa duração. Figura 2.16 – Exemplo de generalizacão/especialização com mais níveis

QUANTIDADE (1,N) CLIENTE

VENDA (1,N)

ID NOME

ID MÍDIA PREÇO

NOME

ISBN

ISSN LIVRO

FILME

REVISTA

ANO

PERIODICIDADE DURAÇÃO

VHS

DVD

[um diagrama E-R mais complexo] Na Figura 2.17, encontra-se um diagrama E-R com um número maior de entidades e relacionamentos descrevendo uma parte de um sistema comercial. 51


Nele observamos duas entidades – “clientes” e “pedido” – relacionadas entre si. O relacionamento não apresenta um nome, mas poderia ter qualquer descrição que fosse considerada a mais apropriada ao contexto. A descrição, ali encontrada, significa que os clientes fazem pedidos. A cardinalidade máxima N ao lado da entidade “pedido” indica que um cliente pode fazer mais de um pedido, porém um pedido pode ser associado somente a um cliente, como indica a cardinalidade máxima 1 que está ao lado da entidade “cliente”. Na questão da cardinalidade mínima ou obrigatoriedade, vemos que um pedido sempre estará associado a pelo menos um cliente, porém a cardinalidade mínima 0 ao lado de “pedido” indica que um cliente pode não estar associado a um pedido. Pode-se notar também que a entidade “cliente” possui um atributo chave ou identificador “código”. Assim, este atributo indicará individualmente cada instância ou ocorrência de um cliente simbolizado pela entidade “cliente”. Quanto à entidade “pedido”, também possui um código identificando cada pedido individualmente. Porém, o relacionamento indica que para o pedido estar associado ao cliente, a instância de “pedido” precisa estar associada a uma instância de “cliente”, e assim a ligação do relacionamento identificador estar enfatizada do lado de pedido*. Deve-se imaginar a entidade “pedido” como sendo uma “aglutinadora” de itens. Um cliente pode solicitar mais de um item constando em um pedido. Assim, “pedido” está associada à entidade “itens” por um relacionamento não nominado. A obrigatoriedade indicada pelas cardinalidades mínimas mostra que deve existir pelo menos um item para um pedido (logicamente, se existe um pedido deve existir pelo menos um item associado a este pedido). A cardinalidade máxima indica que um * No modelo lógico relacional isso é entendido como sendo o código do cliente fazendo parte da relação “pedido” como uma chave externa ou estrangeira, como será visto mais adiante.

52

banco_de_dados_

pedido pode estar associado a vários itens, porém um item deve estar associado a um único pedido. Da mesma forma que o relacionamento anterior, deve-se notar que existe um atributo chave ou identificador para o item. Um item poderá ser numerado para cada pedido começando, por exemplo, do item número 1 até o item máximo. Como poderemos ter vários itens de número 1, precisamos fazer o relacionamento identificador do lado da entidade “item”, de forma que o atributo chave esteja associado ao atributo chave da entidade “pedido”. Agora será permitido referenciar adequadamente o pedido 1000 com o seu item 1 e o pedido 1001 também com o seu item 1 respectivo. O relacionamento “item-produto” associa as entidades “itens” e “estoque”. A cardinalidade indica que um item pode ter mais de um produto em estoque (ou seja, um item de pedido poderá possuir mais de um produto. Isso pode acontecer em termos reais como numa promoção, por exemplo, em que dois ou mais produtos são oferecidos com certo desconto). A cardinalidade mínima 0 do lado da entidade “estoque” indica que uma instância de “estoque” não é obrigada a participar de uma instância de “item”. Notamos também que o relacionamento “item-produto” possui dois atributos: “preço” e “desconto”. Esses atributos não poderiam estar ligados à entidade “estoque”, pois iriam indicar que o preço e desconto seriam sempre os mesmos não importasse o item. Por outro lado, caso estivessem ligada à entidade “item”, se houvesse mais de uma instância de produto para um item, o preço e o desconto teriam que ser entendidos como os mesmos para os produtos associados a este item. Assim, o relacionamento fica com os dois atributos*.

* Num modelo relacional, esse relacionamento se transformará em uma tabela com seus atributos próprios e os códigos (que são identificadores) respectivos de cada entidade associada.


53

Figura 2.17 – Exemplo de diagrama E-R sobre uma parte de um sistema comercial

LIMITE_COMPRA

CODIGO DATA

TELEFONE (1,1)

(0,N)

CLIENTE

PEDIDO VALOR (1,1)

CODIGO NOME QTD DESCRIÇAO

DESCONTO

CODIGO PTOPED

(0,N)

ESTOQUE ESTMIN

(1,N) ITEM_ PRODUTO

(1,1)

CODIGO

ITENS QTD SUBTOTAL

CUSTO

PRAZOENT

PREÇO

(1,N) PROD_ FORNECEDORES

(1,N) CODIGO FORNECEDOR NOME TELEFONE

UF ULTENT

O último relacionamento “prod_fornecedor” associa as entidades “estoque” e “fornecedor”. Este relacionamento significa quais produtos no estoque são fornecidos por quais fornecedores. Um produto em estoque pode ser fornecido por vários fornecedores. Um fornecedor pode fornecer mais de um produto (veja a cardinalidade máxima N dos dois lados). O relacionamento é obrigatório também, ou seja, um item sempre deve estar associado a pelo menos um fornecedor. E um forne54

cedor deve fornecer pelo menos um item.

banco_de_dados_

[entidade associativa] Em certos momentos, num processo de modelagem de um diagrama E-R, veremos que será necessário associar uma entidade a um relacionamento. Porém, não se pode pela regra de associação de diagramas E-R associar um relacionamento a outro. Por exemplo, no diagrama E-R explicado anteriormente, caso queiramos associar uma entidade denominada “ordem de compra” para que possamos controlar os produtos que são solicitados aos fornecedores, a qual entidade associar? Se associarmos diretamente à entidade “estoque”, não teremos como ligar a qual fornecedor será feita a ordem de compra. Caso associemos com a entidade “fornecedor”, não teremos a informação de produto para solicitar. A solução seria relacionar diretamente com o próprio relacionamento “prod_fornecedor”, que vem a ser onde encontramos as duas informações juntas. Porém, não podemos ligar dois relacionamentos. Assim, transformamos o relacionamento “prod_fornecedor” numa entidade associativa (Figura 2.18), e agora poderemos estabelecer um relacionamento entre a entidade associativa “prod_fornecedor” e a entidade “ordem de compra”. O símbolo para uma entidade associativa é um retângulo sobrescrevendo um losango. Podemos pensar num modelo equivalente, caso não queiramos trabalhar com a entidade associativa*. Assim, para o novo modelo, transforma-se a entidade associativa “prod_fornecedor” em uma entidade, e associa-se esta a cada entidade do diagrama E-R com seus respectivos relacionamentos exigidos pelo padrão de diagramação.

* Na conversão de um diagrama E-R para um modelo relacional, o processo é mais simples sem o uso de entidade associativa.

55


Figura 2.18 – Exemplo de entidade associativa

QTD DESCRIÇAO CODIGO PTOPED ESTOQUE ESTMIN

CUSTO

PRAZOENT (1,N)

PROD_ FORNECEDOR

ORDEM DE COMPRA

(1,N) CODIGO FORNECEDOR NOME TELEFONE

UF ULTENT

Note que no diagrama E-R, da Figura 2.19, foi substituída a entidade associativa pela entidade “prod_fornecedor”, tendo relacionamentos com as outras entidades: “estoque” e “fornecedor”. Com a entidade “ordem de compra”, existe um relacionamento que permite identificar a ordem de compra com o código respectivo, e esta instância pode aglutinar (como acontece na entidade “pedido” descrita anteriormente) vários pares produto-fornecedor. E a quantidade a ser requisitada é colocada no relacionamento, pois, a cada nova ordem de compra, quantidades diferentes poderão ser solicitadas. Não faria sentido se fosse colocado o atributo da quantidade na entidade “ordem de compra”, nem mesmo na entidade “prod_fornecedor”.

56

banco_de_dados_

Figura 2.19 – Uma alternativa para o diagrama anterior sem entidade associativa

QTD DESCRIÇAO CODIGO PTOPED ESTOQUE ESTMIN

CUSTO

PRAZOENT (1,1)

(1,N)

PROD_ FORNECEDOR

CODIGO

QTD

(1,N)

(1,1)

ORDEM DE COMPRA

(1,N)

(1,1) CODIGO FORNECEDOR NOME TELEFONE

UF ULTENT

57


[resumo] O modelo entidade-relacionamento, difundido por Chen, permite agregar à representação dos dados aspectos semânticos, apresentando um bom ponto de partida para a compreensão daquilo que normalmente não transparece num banco de dados relacional. Basicamente o modelo propõe a representação da entidade, que pode ser um elemento concreto ou abstrato, referente a algo pessoal, lugar, objeto, evento ou conceito; do atributo, que são as características da entidade, pode ser simples ou derivado; e do relacionamento, que associa uma ou mais entidades. O tipo de relacionamento, de acordo com o número de entidades que participam da relação, pode ser unário, binário ou ternário. A obrigatoriedade refere-se a situações em que há necessidade da presença de uma entidade, ou não, contendo o conceito de cardinalidade (o número de elementos associados à entidade). Uma entidade que participa de um relacionamento é dita fraca no caso de não ser obrigatória a sua presença. Um relacionamento também pode possuir atributos, assim como uma entidade, na qual a presença de tais atributos faça mais sentido ao modelo do que se estivessem ligados às entidades participantes. A diferenciação de uma entidade em subtipos e supertipos também é possível, sendo tal representação hierárquica denominada de generalização-especialização. A entidade associativa permite a representação de uma entidade que evita a conexão entre dois relacionamentos, o que não é permitido pelo modelo E-R.

58

banco_de_dados_

[exercícios] 1. Porque a abordagem modelo entidade-relacionamento é útil para a modelagem de bancos de dados? 2. Quais são os elementos que podem constar em um diagrama E-R? 3. O que vem a ser uma instância? 4. Dê um exemplo de relacionamento para cada tipo de cardinalidade: 1:1, 1:N e N:N. 5. Elabore os seguintes diagramas E-R de acordo com as entidades e atributos a seguir enumerados: a) Aluno (matricula, nome), curso (código, nome). Um aluno pode fazer mais de um curso. 6. Na terminologia da abordagem E-R, o que são papéis? 7. Num relacionamento unário ou auto-relacionamento, uma entidade se relaciona com ela mesma. Explique. 8. Monte o auto-relacionamento para o exemplo de um plano de contas, onde temos contas e subcontas associadas entre si. Expresse os papéis e monte um diagrama de ocorrências. 9. De acordo com o exemplo de relacionamento ternário da Figura 2.9, como ficaria o modelo para o caso de um funcionário poder atuar em mais de uma área? Exemplifique com o diagrama de ocorrências.


59

10. De acordo com o exemplo de relacionamento ternário da Figura 2.9, como ficaria o modelo para o caso de mais de um funcionário poder atuar em mais de um projeto e área? Exemplifique com o diagrama de ocorrências. 11. Dê um exemplo de relacionamento quaternário. Expresse um diagrama de ocorrências para exemplificar as instâncias. 12. Fale sobre obrigatoriedade. 13. Utilize o exemplo de relacionamento da Figura 2.6 e especifique a cardinalidade mínima e máxima para a situação em que o funcionário pode estar ou não em um projeto, e um projeto pode possuir ou não funcionários. 14. Com base nos esquemas abaixo, elabore um diagrama E-R para um sistema de biblioteca: a) Mídia(cod midia,titulo) Usuário(cod usuario, nome) Autor(cod autor,nome) b) Editora(cod editora,nome,telefone) Algumas considerações para a construção do modelo: _ cada esquema denota uma entidade que fará parte do diagrama; _ entre parênteses constam os atributos que fazem parte de cada entidade; 60

banco_de_dados_

_ por mídia deve ser entendido tudo que existe na biblioteca (livros, fitas, CDs etc.); _ um usuário pode tomar emprestadas várias mídias existentes no acervo de mídias; _ para cada mídia que seja emprestada ao usuário, deve ser registrada uma data de empréstimo, a de previsão da entrega e a da entrega; _ devemos considerar também a existência de um relacionamento para as entidades Mídia e Usuário; _ uma mídia pode ter vários autores, porém uma única editora.

61


0000_0011 = III

63


álgebra relacional_

A álgebra relacional é uma linguagem de consultas procedural. Consiste em um conjunto de operações que tem como entrada uma ou duas relações ou tabelas e produz, como resultado, uma nova relação ou tabela. Tal propriedade é conhecida como a propriedade de fechamento. As operações existentes na álgebra relacional são a seleção, a projeção, a junção, a união, a diferença, a interseção, o produto cartesiano, a divisão e a atribuição. Na proposta original de Codd haviam oito operadores1 (todos os relacionados acima exceto a “atribuição”) que eram divididos em primitivos ou fundamentais* (seleção, projeção, produto cartesiano, união, interseção e diferença) e derivados ou adicionais (junção e divisão). Algumas operações, como a seleção e a projeção, são denominadas de primárias ou unitárias, por operarem com apenas uma relação. Outras, como a junção e o produto cartesiano, são consideradas binárias, por necessitarem de duas relações.

* Operações compostas podem ser realizadas a partir de combinações das operações fundamentais.

[seleção] A operação seleção escolhe linhas ou registros que satisfaçam uma determinada condição. Dizemos que o operador de seleção faz uma restrição em relação à linha ou ao registro – restrição horizontal. Sendo que nesse processo: _ usamos a letra grega minúscula sigma (σ) para denotar seleção; _ a condição aparece subscrita a σ; _ o argumento da relação ou tabela é dado entre parênteses, seguindo o σ. 1. Assim, para selecionar aqueles registros da relação “pedido”, cujo código é 1010, escrevemos: σcodigo=1010 (pedido) Portanto, se a tabela “Pedido” (relação ou tabela de entrada) conter os seguintes registros: PEDIDO codigo

codcli

data

1005

202

10/03/2004

valor 1.200,00

1008

221

13/03/2004

960,00

1010

233

17/03/2004

1.020,00

1015

282

20/03/2004

755,00

o resultado (ou tabela de saída) é mostrado, conforme codigo

codcli

data

1010

233

17/03/2004

valor 1.020,00

2. No caso de fazermos comparações, por exemplo, encontrando registros com valores maiores que R$1.000,00, escrevemos: σvalor>1.000,00 (pedido) 66

banco_de_dados_

Assim, obtemos: codigo

codcli

data

1005

202

10/03/2004

valor 1.200,00

1010

233

17/03/2004

1.020,00

3. Também podem ser efetuadas operações de seleção envolvendo condições compostas: σvalor>1.000,00 AND data<‘15/03/2004’ (pedido) ou σ(valor>1.000,00) AND (data<‘15/03/2004’) (pedido) ou, ainda, σ(valor>1.000,00) E (data<‘15/03/2004’) (pedido) 4. Para os operadores lógicos utilizaremos a seguinte convenção: Quadro 3.1 – Operadores lógicos Matemática

Português

Inglês

∧

e

and

∨

ou

or

¬

não

not

[projeção] A operação de projeção permite que façamos uma restrição em termos de atributos ou campos (ou mesmo colunas) de uma relação ou tabela – aplica uma restrição vertical. Assim, os atributos de maior relevância são escolhidos para que certa consulta seja feita ao banco de dados. A projeção é denotada pela letra grega pi (π), a qual aparece subscrita na lista de atributos ou campos desejados para a operação. Nesse procedimento, a 67


relação ou a tabela aparece, como na seleção, logo após entre parênteses. Assim, se quisermos da tabela anterior “Pedido” apenas os campos código e valor, fazemos a seguinte operação de projeção: πcodigo,valor (pedido) Com essa consulta, produzimos, então, a seguinte relação ou tabela de saída: codigo 1005

valor 1.200,00

1008

960,00

1010

1.020,00

1015

755,00

[junção] Também denominada de junção natural, tal operação caracteriza-se por ser binária, necessitando de duas relações ou tabelas. É também necessária a existência de atributos ou campos em comum entre as duas tabelas envolvidas. Por exemplo, suponhamos que a informação dos clientes que fizeram pedidos esteja na tabela “Clientes”. Assim, caso queiramos saber o nome do cliente que comprou certo pedido de determinado código, não dispomos de tal informação na tabela “Pedido”. No entanto, com a operação de junção, é possível obtermos essa informação.

Ao compararmos as tabelas “Clientes” e “Pedido”, percebemos que os campos que se correlacionam são codigo na tabela “Clientes”, e codcli na tabela “Pedido”. Assim, é possível fazermos a junção das duas tabelas através desses campos em comum. A tabela “Clientes” apresenta-se da seguinte forma:

68

banco_de_dados_

A tabela “Clientes” apresenta-se da seguinte forma: CLIENTES

codigo

nome

telefone

202

Ernesto

3222-0809

limite_compra 1.500,00

221

Amélia

3233-2474

2.000,00

233

Luís Alberto

3323-0071

1.500,00

282

José Antonio

3343-9021

800,00

295

Carlos Silva

3224-5678

800,00

Agora, vamos observar a tabela “Pedido”: PEDIDO codigo

codcli

data

1005

202

10/03/2004

valor 1.200,00

1008

221

13/03/2004

960,00

1010

233

17/03/2004

1.020,00

1015

282

20/03/2004

755,00

Nesse processo, expressamos a operação que foi efetuada através do símbolo

, colocando em subscrito à direita a igualdade entre os cam-

pos em comum. Caso os campos venham a ter a mesma designação nas duas tabelas, omitimos a igualdade e colocamos apenas uma vez o nome do atributo ou campo. pedidocodcli=codigo clientes Note que na igualdade, o campo codcli está à esquerda, pois pertence à tabela “Pedido” e o campo codigo está à direita, pois pertence, por sua vez, à tabela “Clientes”. A tabela resultante da operação de junção, nesse caso, fica assim:

69


limite_ compra

codigo

codcli

data

valor

nome

telefone

1005

202

10/03/2004

1.200,00

Ernesto

3222-0809

1.500,00

1008

221

13/03/2004

960,00

Amélia

3233-2474

2.000,00

1010

233

17/03/2004

1.020,00

Luis Alberto

3323-0071

1.500,00

1015

282

20/03/2004

755,00

José Antonio

3343-9021

800,00

Veja que a tabela de saída tem sete atributos e que o atributo em comum não é repetido*. Note também que na junção, os registros que não têm um correspondente na outra tabela desaparecem da tabela resultante (no caso, o cliente de código 295, Carlos Silva). Agora, é possível sabermos pelo nome do cliente o pedido relacionado a ele. A operação de junção é comutativa, ou seja, se trocarmos de lado as tabelas envolvidas, clientescodigo=codcli pedido produziremos uma tabela semelhante à obtida anteriormente, porém com os atributos dispostos de maneira diferente: clientes. codigo

nome

telefone

limite_ compra

pedido. codigo

data

valor

202

Ernesto

32220809

1.500,00

1005

10/03/2004

1.200,00

221

Amélia

32332474

2.000,00

1008

13/03/2004

960,00

233

Luís Alberto

33230071

1.500,00

1010

17/03/2004

1.020,00

282

José Antonio

33439021

800,00

1015

20/03/2004

755,00

* Em consultas na linguagem SQL, por exemplo, costumam aparecer os dois campos, precedidos do nome da relação ou tabela.

70

banco_de_dados_

Veja que, agora, diferenciamos o atributo codigo da tabela “Clientes” colocando o prefixo referente ao nome da tabela logo após um ponto e o nome do atributo, o mesmo acontecendo com “Pedido”. É também possível fazer junções de três ou mais tabelas, desde que respeitados os critérios colocados para a operação de junção.

[operações compostas] Com as três operações que já foram explicadas, já é possível fazermos consultas compostas. Suponha que queiramos consultar apenas o código do cliente que comprou o pedido 1010. Assim, além de fazermos uma seleção, é necessário também realizarmos uma projeção para obtermos somente a informação do campo codcli, por exemplo: πcodcli (σcodigo=1010 (pedido)) Como a seleção produz uma tabela de saída, esta serve de entrada para a próxima operação que é a projeção. A tabela de saída após a projeção fica: codcli 233

Devemos ter cuidado quando fizermos a operação de forma inversa: σcodigo=1010 (πcodcli (pedido)) Nesse caso, como a tabela resultante da projeção retorna apenas o atributo “codcli”, iremos perder o campo “código” envolvido na seleção logo a seguir, produzindo, desse modo, uma tabela nula: codcli 233

71


Assim, devemos ter cuidado com a precedência das operações. Uma boa regra para seguir é fazermos a operação de junção seguida da seleção e apenas no final fazermos a projeção. Dessa forma, se quisermos obter apenas o nome do cliente referente ao pedido 1010, precisaremos da junção efetuada anteriormente, além de fazermos a operação de seleção apenas para o pedido 1010, para, depois, projetarmos para o atributo nome, ou seja: πnome (σpedido.codigo=1010 (pedido

codcli=codigo

clientes))

Nesse processo, a tabela de saída tem a forma: nome Luís Alberto

[união] A operação de união é binária e necessita de duas tabelas para operar. A restrição aqui colocada é: as tabelas envolvidas devem ter o mesmo conjunto de atributos. Assim, se tivermos duas tabelas relativas, uma à conta-corrente de clientes e outra às contas de poupança, e quisermos obter uma consulta recuperando todos os números de contas, não importando de que tipos sejam, é só fazermos a união das tabelas. Com as tabelas apresentadas, a seguir, vamos fazer o processo descrito acima. CONTA_CORRENTE conta

nome

telefone

1123-8

Ernesto

3222-0809

750,00

2124-0

Amélia

3233-2474

(130,00)

3113-6

Luís Alberto

3323-0071

1.100,00

4563-7

José Antonio

3343-9021

(330,00)

72

banco_de_dados_

saldo

CONTA_POUPANÇA conta

nome

telefone

1333-5

Luís Paulo

3222-0809

valor 150,00

2124-0

Amélia

3233-2474

320,00

3243-6

Carlos Alberto

3323-0071

800,00

4563-7

José Antonio

3343-9021

330,00

A expressão de consulta, a seguir, retorna todas as contas, não importando sua função, sem repetir registros existentes nas duas tabelas: πconta (conta_corrente) U πconta (conta_poupanca) produzindo a seguinte tabela: conta 1123-8 1333-5 2124-0 3113-6 3243-6 4563-7

[diferença] A operação de diferença também é binária, de forma semelhante à união e à junção. Se quisermos, numa consulta, retirar registros ou linhas que sejam comuns às tabelas envolvidas, o fazemos através da operação de diferença. Se quisermos, dentro do mesmo contexto colocado para a união, excluir das contas correntes aquelas que também são contas de poupança, a expressão fica: πconta (conta_corrente) − πconta (conta_poupanca) produzindo a seguinte tabela: 73


conta 1123-8 3113-6

Para a operação diferença, vale a restrição das tabelas envolvidas, com relação ao conjunto de atributos iguais, aplicada à operação de união.

[interseção] A operação de interseção, como na teoria dos conjuntos, tem a finalidade de retornar os registros em comum entre duas tabelas. Essa operação é binária, necessitando de duas tabelas para ser efetuada. Para exemplificar, vamos considerar as tabelas, dos exemplos anteriores, usadas para união e diferença. CONTA_CORRENTE conta

nome

telefone

1123-8

Ernesto

3222-0809

saldo 750,00

2124-0

Amélia

3233-2474

(130,00)

3113-6

Luís Alberto

3323-0071

1.100,00

4563-7

José Antonio

3343-9021

(330,00)

CONTA_POUPANÇA conta

nome

telefone

1333-5

Luís Paulo

3222-0809

valor 150,00

2124-0

Amélia

3233-2474

320,00

3243-6

Carlos Alberto

3323-0071

800,00

4563-7

José Antonio

3343-9021

330,00

Caso queiramos obter as contas que aparecem tanto na relação “Conta_corrente” quanto na “Conta_poupança”, fazemos a operação de interseção: 74

banco_de_dados_

πconta (conta_corrente) ∩ πconta (conta_poupanca) obtendo, assim, a seguinte tabela de saída: conta 2124-0 4563-7

Dadas duas tabelas “r” e “s” quaisquer, a operação de interseção pode ser escrita utilizando somente a operação diferença: r ∩ s = r − (r − s)

[produto cartesiano] A operação produto cartesiano também é binária, sendo bastante semelhante ao produto cartesiano da teoria dos conjuntos. Enquanto a junção une duas tabelas de acordo com campos em comum, no produto cartesiano não são considerados tais campos. Para cada registro de uma tabela, repetem-se todos os registros da outra. Assim ao fazer o segundo registro da tabela, repetem-se todos os registros da outra, e assim sucessivamente até o último registro da primeira tabela. Assim, para o produto cartesiano das tabelas do exemplo anterior referente às tabelas “Pedido” e “Clientes”, fazemos a operação “pedido × clientes”, o que resulta na seguinte tabela: pedido. codcli data codigo

valor

clientes. codigo

nome

telefone

limite_ compra

1005

202

10/03/2004 1.200,00

202

Ernesto 3222-0809 1.500,00

1005

202

10/03/2004 1.200,00

221

Amélia

1005

202

10/03/2004 1.200,00

233

Luís 3323-0071 1.500,00 Alberto

1005

202

10/03/2004 1.200,00

282

José 3343-9021 Antonio

3233-2474 2.000,00

800,00

75


pedido. codcli data codigo

valor

clientes. codigo

nome

telefone 3224-5678

limite_ compra

1005

202

10/03/2004 1.200,00

295

Carlos Silva

1008

221

13/03/2004

202

Ernesto 3222-0809 1.500,00

960,00

800,00

1008

221

13/03/2004

960,00

221

Amélia

1008

221

13/03/2004

960,00

233

Luís 3323-0071 1.500,00 Alberto

3233-2474 2.000,00

1008

221

13/03/2004

960,00

282


800,00

1008

221

13/03/2004

960,00

295

Carlos Silva

800,00

1010

233

17/03/2004 1.020,00

202

Ernesto 3222-0809 1.500,00

1010

233

17/03/2004 1.020,00

221

Amélia

1010

233

17/03/2004 1.020,00

233

Luís 3323-0071 1.500,00 Alberto

1010

233

17/03/2004 1.020,00

282


800,00

1010

233

17/03/2004 1.020,00

295

Carlos Silva

800,00

3224-5678

3233-2474 2.000,00

3224-5678

1015

282

20/03/2004

755,00

202

Ernesto 3222-0809 1.500,00

1015

282

20/03/2004

755,00

221

Amélia

1015

282

20/03/2004

755,00

233

Luís 3323-0071 1.500,00 Alberto

1015

282

20/03/2004

755,00

282


800,00

1015

282

20/03/2004

755,00

295

Carlos Silva

800,00

3233-2474 2.000,00

3224-5678

Nesse caso, podemos obter a consulta composta sobre a operação do produto cartesiano em vez da operação de junção, pois, desde que na operação de seleção façamos os campos comuns iguais entre si, é possível operacionalizarmos: πnome (σ(pedido.codigo=1010) ∧ (codcli=clientes.codigo)(pedido × clientes)) Chegamos, assim, ao resultado da consulta que é: nome

76

Luís Alberto

banco_de_dados_

A operação do produto cartesiano é dispendiosa. O número de registros da tabela resultante de uma operação de produto cartesiano é a multiplicação do número de registros de cada uma das tabelas. No nosso exemplo, existem quatro registros na tabela “Pedido” e cinco registros na tabela “Clientes”, resultando 20 registros na tabela de saída da operação do produto cartesiano.

[renomeação (rename ou renaming)] Conforme vimos até aqui, com as consultas na álgebra relacional, o resultado de uma expressão não possui um nome que possa ser usado para referenciá-la. No entanto, em certos momentos, é útil dar um nome a uma tabela de saída. O operador de renomeação é representado pela letra grega rho (ρ). Assim, dada uma certa expressão da álgebra relacional E, a expressão a seguir: ρr (E) resulta a expressão E com o nome r. Outra forma de utilizar a operação de renomeação é a forma que apresentamos a seguir, (isso, desde que tenhamos uma expressão E): ρr(A1 ,A2 ,...,An) (E) Nesse caso, retorna o resultado da expressão E pelo nome r, e os atributos, desta nova tabela, são alterados e recebem novos nomes A1, A2,..., An. Vamos considerar o seguinte tipo de consulta2: “Qual o pedido de valor mais alto existente em uma tabela?”. Vamos considerar como ponto de partida a tabela utilizada anteriormente para as outras operações: 77


PEDIDO codigo

codcli

data

1005

202

10/03/2004

valor 1.200,00

1008

221

13/03/2004

960,00

1010

233

17/03/2004

1.020,00

1015

282

20/03/2004

755,00

A estratégia a ser adotada, nessa situação, consiste em primeiro estabelecermos uma tabela temporária que irá conter os valores de pedidos que não são altos. A partir deste conjunto, obtemos, a seguir, a diferença entre a projeção da tabela original “Pedido” e essa tabela temporária. Para computar a tabela temporária, iremos comparar os valores de todos os pedidos existentes. Isso pode ser obtido a partir do produto cartesiano “pedido x pedido”, e aplicar uma seleção para comparar os valores de dois pedidos quaisquer que venham a aparecer em um registro. Precisaremos, então, do mecanismo discutido aqui, para diferenciar entre dois campos valor. Utilizamos, na seqüência, a operação renomeação para mudar o nome da relação “pedido”, o que nos permite, dessa maneira, a referência à segunda relação sem problemas de duplicidade. A tabela temporária, que consiste dos pedidos que não sejam os mais altos, pode ser expressa da seguinte forma: πpedido.valor (σpedido.valor
banco_de_dados_

pedido. pedido. pedido. codigo codcli data

pedido. valor

q. q. q.data codigo codcli

q.valor

1005

202

10/03/2004 1.200,00 1005

202

10/03/2004 1.200,00

1005

202

10/03/2004 1.200,00 1008

221

13/03/2004

1005

202

10/03/2004 1.200,00 1010

233

17/03/2004 1.020,00

1005

202

10/03/2004 1.200,00 1015

282

20/03/2004

1008

221

13/03/2004

960,00 1005

202

10/03/2004 1.200,00

1008

221

13/03/2004

960,00 1008

221

13/03/2004

1008

221

13/03/2004

960,00 1010

233

17/03/2004 1.020,00

1008

221

13/03/2004

960,00 1015

282

20/03/2004

1010

233

17/03/2004 1.020,00 1005

202

10/03/2004 1.200,00

1010

233

17/03/2004 1.020,00 1008

221

13/03/2004

1010

233

17/03/2004 1.020,00 1010

233

17/03/2004 1.020,00

1010

233

17/03/2004 1.020,00 1015

282

20/03/2004

1015

282

20/03/2004

755,00 1005

202

10/03/2004 1.200,00

1015

282

20/03/2004

755,00 1008

221

13/03/2004

1015

282

20/03/2004

755,00 1010

233

17/03/2004 1.020,00

1015

282

20/03/2004

755,00 1015

282

20/03/2004

960,00

755,00

960,00

755,00

960,00

755,00

960,00

755,00

A seleção, (a seguir), operando sobre a tabela acima, restringe os valores: σpedido.valor
pedido. q. q. q.data valor codigo codcli

q.valor

1008

221

13/03/2004

960,00 1005

202

10/03/2004 1.200,00

1008

221

13/03/2004

960,00 1010

233

17/03/2004 1.020,00

1010

233

17/03/2004 1.020,00 1005

202

10/03/2004 1.200,00

1015

282

20/03/2004

755,00 1005

202

10/03/2004 1.200,00

1015

282

20/03/2004

755,00 1008

221

13/03/2004

1015

282

20/03/2004

755,00 1010

233

17/03/2004 1.020,00

960,00

Quando realizamos a projeção final sobre toda a operação, πpedido.valor (σpedido.valor

resulta na seguinte tabela: valor 960,00 960,00 1.020,00 755,00 755,00 755,00

Perceba que na projeção acima, o valor maior R$1.200,00 não está contido na tabela. Com a projeção sobre a tabela original “Pedido”, que é πpedido.valor (pedido) e com os registros, que são: valor 1.200,00 960,00 1.020,00 755,00

obtemos uma tabela temporária (acima) e, dessa forma, ao acrescentá-la aos dados (passos) que a antecederam, estamos em condições de representar a expressão da diferença, πpedido.valor (pedido) − πpedido.valor (σpedido.valor
que é o maior valor entre os pedidos existentes dentro da tabela. 80

banco_de_dados_

[atribuição ou designação] É interessante, algumas vezes, utilizarmos a designação para uma tabela, de modo a usá-la como variável temporária. A operação de atribuição ou designação, denotada por ←, trabalha de maneira assemelhada à atribuição de variáveis nas linguagens de programação. Com essa operação, é possível fazer uma consulta de maneira seqüencial, consistindo de uma série de designações seguidas por uma expressão cujo valor é exibido como o resultado daquela consulta. Portanto, se quisermos representar o exemplo da operação de renomeação tratado anteriormente, ,πpedido.valor (pedido) − πpedido.valor (σpedido.valor

onde “P” é o “dividendo” e “Q” é o “divisor”. A idéia por trás da operação de divisão é obter o conjunto de registros em “P” que “casa” ou coincide com todos em “Q”. Ou seja, devem aparecer, como resultado da consulta, os registros de “P” que estão relacionados a todos os registros em “Q”, ao mesmo tempo. Digamos que as tabelas “P” e “Q” assumam o seguinte conteúdo: P

Q a

b

b

a1

b1

b1

a1

b2

b2

a2

b1

b3

a2

b3

a1

b3

a3

b1

a3

b2

A operação P ÷ Q resulta em: P÷Q a a1

Ou seja, o resultado ou quociente da divisão contém os atributos de “P” que não estão presentes em “Q”. Assim, o atributo “a” faz parte da tabela de resultado e o valor “a1” aparece como resultado na tabela quociente, em função de “Q” ter os valores que aparecem no atributo “b” e que se repetem no atributo “a” (ou seja, os pares (a1, b1), (a1, b2) e (a1, b3)) da tabela “P”. Agora, no caso da tabela “S” assumindo o seguinte conteúdo, temos:

82

banco_de_dados_

S b b1 b2

Então, a operação “P ÷ S” resulta, agora, em: P÷S a a1 a3

Isso por existirem os pares (a1, b1), (a1, b2) e (a3, b1), (a3, b2) na tabela “P”. E, por fim, se a tabela “T” assumir o seguinte conteúdo: T b b1

a operação “P ÷ T” resulta em: P÷T a a1 a2 a3

Isso em razão da existência de (a1, b1), (a2, b1) e (a3, b1) na tabela “P”. Com um exemplo do modelo relacional já descrito anteriormente, a consulta seguinte irá necessitar da operação de divisão: “Qual é o código do fornecedor que fornece todos os produtos?”. Veja a operação: prodfor ÷ πcodigo (estoque) 83


A seguir a tabela “Prodfor”: PRODFOR codprod

codfor

2001

130

2044

130

2050

141

2050

152

2050

184

2080

152

2001

152

2044

152

3020

163

3020

152

3033

184

3050

184

4101

163

4101

184

3033

152

3050

152

4101

152

E a tabela “Estoque”: ESTOQUE codigo

descricao

2001

Grade de Aço

2044

Bomba Injetora

2050

Multímetro

2080

Acoplador

3020

Quadro Padrão

3033

Esmeril

3050

Furadeira

4101

Serra Tico-Tico

6

84

banco_de_dados_

qtd

ptoped

estmin

custo

prazoent

150

70

30

80,00

15

5

2

1

320,00

30

20

5

2

35,00

10

4

6

2

280,00

30

3

8

4

140,00

20

5

3

2

110,00

10

9

5

3

85,00

10

4

2

75,00

10

A tabela resultado ou o quociente da divisão é: codfor 152

Isso porque o fornecedor de código 152 fornece todos os produtos existentes na tabela “Estoque”.

[resto] A operação de divisão pode ser relacionada com o produto cartesiano, da mesma forma que na álgebra normal com a multiplicação e a divisão. Se, com a operação inteira 5 ÷ 3 = 1, quisermos fazer o inverso, multiplicando 1 × 3, precisaremos do resto 2 para achar o valor original 5. Verificando o exemplo abordado em divisão, depois de obter “P ÷ Q”, se, agora, quisermos reproduzir a tabela “P” a partir da operação inversa: (P ÷ Q) × Q Porém, o resultado desse produto cartesiano será: (P ÷ Q) × Q a

b

a1

b1

a1

b2

a1

b3

Que, como você pode notar, é diferente da tabela “P” original. Assim, a operação “P ÷ Q” gerou um resto que são os registros que não fizeram parte da tabela quociente. Para obtermos o resto, precisamos utilizar a operação de diferença: P − (P ÷ Q) × Q 85


Dessa forma, se fizermos “R = P − (P ÷ Q) × Q”, como sendo a tabela que contém o resto, de acordo com o exemplo anterior, R a

b

a2

b1

a2

b3

a3

b1

a3

b2

é possível, nesse processo, restaurar a tabela “P” original fazendo a operação de união: ((P ÷ Q) × Q) U R O resto não é entendido como sendo uma operação da álgebra que serve apenas para mostrar as relações existentes entre os operadores do produto cartesiano e da divisão. No exemplo anterior de fornecedores, para saber os fornecedores que não fornecem todos os produtos, basta obter o resto por meio da operação de diferença: πcodigo (prodfor) − (prodfor ÷ πcodigo (estoque)) PRODUZINDO codfor 130 141 163 184

Note que na operação de diferença, precisa-se respeitar o critério dos atributos comuns. 86

banco_de_dados_

[resumo] A “álgebra relacional” é uma linguagem de consultas procedural que consiste num conjunto de operações que são efetuadas sobre tabelas ou relações de entrada, produzindo uma nova relação ou tabela na saída. As operações da álgebra relacional são: seleção, projeção, produto cartesiano, junção, união, diferença, intersecção, divisão, renomeação e atribuição ou designação. A operação de seleção extrai linhas ou registros que satisfaçam uma determinada condição (restrição horizontal); por sua vez, a operação de projeção permite a extração de atributos ou colunas específicas de uma relação ou tabela (restrição vertical). A junção permite combinar duas tabelas que possuam um ou mais atributos em comum; enquanto a união combina as linhas de duas tabelas, desde que tenham o mesmo conjunto de atributos; já a intersecção retira as linhas comuns nessa mesma situação. A diferença permite a extração daqueles registros que são comuns a duas relações, permanecendo os registros da tabela que aparece representada à esquerda. O produto cartesiano faz a combinação de uma linha de uma tabela com todas as linhas de outra e, assim, sucessivamente, não importando os atributos em comum. A renomeação permite a modificação de nomes de atributos nas expressões de consulta; sendo que a atribuição ou designação permite renomear tabelas resultantes de consultas, possibilitando uma melhor compreensão de um conjunto de consultas subseqüentes. A operação de divisão permite obter um conjunto de registros de uma tabela que se associam com outra tabela enquanto o resto é a operação que obtém os registros que não se associam.

87


[exercícios] Com base nas tabelas a seguir, expresse com as operações da álgebra relacional e mostre a tabela resultante para cada questão pedida. PEDIDO codigo

codcli

data

0001

100

03/10/2003

valor 133,00

0002

102

04/10/2003

45,00

0003

105

05/11/2003

339,50

0004

110

10/11/2003

30,00

0005

100

12/11/2003

152,50

CLIENTE codigo

nome

telefone

cidade

UF

100

Luís Paulo

3355-1027

Curitiba

PR

102

José Antonio

3452-3528

Lapa

PR

105

Carlos Lima

3233-3456

Joinville

SC

110

Maria de Castro

3441-8930

Ponta Grossa

PR

114

Danilo Silva

3353-4020

Curitiba

PR

ITENS codped

coditem

qtdped

0001

2.010

3

108,00

0001

2.020

10

25,00

0002

3.004

30

45,00

0003

4.011

2

50,00

0003

4.013

5

137,50

0003

4.025

4

152,00

0004

3.004

20

30,00

0005

3.004

10

15,00

0005

4.013

5

137,50

88

banco_de_dados_

subtotal

FORNECEDOR codigo

nome

telefone

cidade

UF

501

ABC Cirúrgica

3772-4001

São Paulo

SP

502

Thermo

3873-5030

Salvador

BA

535

Distrib.Silva

3444-5523

Joinville

SC

550

CLS

3352-2353

Curitiba

PR

590

AKL Equip.

3330-8252

Porto Alegre

RS

ESTOQUE codigo

descricao

un

qtd

estmin

ptoped

codfor

2000

Termômetro

un

preco 12,00

36

5

15

501

2010

Termômetro Digital

un

36,00

12

5

15

501

2020

Compressa Cirúrgica

Pct c/10

2,50

40

10

50

535

3004

Esparadrapo

Rl c/10m

1,50

130

20

100

535

4011

Agulha Desc. 10mmx1mm

Cx. c/ 100 un

25,00

43

15

60

550

4013


Cx. c/ 100 un

27,50

43

15

60

550

4025


Cx. c/ 100 un

38,00

43

15

60

550

5001

Ap.Pressão

un

6

1

5

590

205,00

1. Quais os itens que foram comprados pelo cliente de número 100? Mostre o código do item. 2. Quais os itens no estoque que estão abaixo do ponto de pedido? Apresente a descrição, a quantidade e o ponto de pedido. 3. Liste os pedidos efetuados pelo cliente Luís Paulo. 4. Dê o nome do cliente que comprou o produto mais caro. Apresente o nome e o telefone. 89


5. Quais os pedidos adquiridos no mês de novembro? Apresente o código. 6. Dê o código e a descrição dos produtos que estão abaixo do estoque mínimo. 7. Quais os itens no estoque que não foram comprados? Apresente código e descrição. 8. Quais clientes não efetuaram pedido? Mostre nome e telefone. 9. Faça uma lista de nomes e telefones de clientes e de fornecedores. 10. Qual o nome e o telefone dos fornecedores do pedido 0005? Com base no conjunto de tabelas a seguir, efetue as consultas conforme a álgebra relacional: CLIENTES codigo

nome

telefone

datanasc

1010

Carlos Alberto

3323-2090

12/05/1975

1012

Maria Albertina

3221-0304

15/07/1980

1015

João Carlos

3234-5678

10/04/1977

1021

Teresa Silva

3313-4455

23/02/1982

FILMES codigo

titulo

300

Soldado Universal VI

2,10

303

Robin Hood

1,70

310

A Bela e a Fera

2,20

330

Bye Bye Brasil

1,50

335

Titanic

3,20

90

banco_de_dados_

duracao

FITA codigo

codfilme

5110

300

valorloc 1,50

5111

300

1,50

5112

303

1,70

5113

310

1,70

5117

330

2,00

5125

335

2,10

5127

335

2,10

LOCAÇÃO codfita

codcli

dataloc

dataent

5110

1021

10/04/2004

12/04/2004

5111

1015

10/04/2004

5113

1010

11/04/2004

5127

1010

12/04/2004

13/04/2004

11. Qual o código das fitas que não foram locadas? 12. Qual é o valor da locação do filme Titanic? 14. Qual o nome de quem locou a fita no dia do seu aniversário? 15. Quais clientes (nome e telefone) fazem aniversário no mês de fevereiro? 16. Identifique (nome e telefone) os clientes que não locaram fitas.

91


0000_0100 = IV

standard_query language_SQL_

A abordagem de SQL, aqui contida, fundamenta-se nos trabalhos de Ullman1, complementadas com Korth e Silberschatz2 e parte da abordagem de Date3. A SQL pode ser considerada a linguagem mais comum dentre as de consulta. Aliás, deve ser vista como bem mais que uma linguagem de consulta, caracterizandose como uma linguagem de banco de dados. Pode-se identificar a equivalência entre consultas em álgebra relacional e em SQL, porém existem muitas características que estendem bastante o poder de expressão de uma consulta em SQL. Dentre vários dialetos, os mais populares são o ANSI SQL e a versão SQL-92 ou SQL2, e ainda a versão SQL3 ou SQL-99 que incorpora mais recursos como triggers, procedures, objetos e recursividade. A SQL pode ser encontrada em várias implementações de bancos de dados existentes no mercado, cada uma apresentando certas diferenças em relação ao padrão estabelecido. Porém, é de praxe a manutenção do padrão SQL entre os fabricantes, minimizando significativamente impactos na portabilidade de banco de dados. As consultas em álgebra relacional (colocadas com a abreviação AR no texto) podem ser convertidas em SQL de acordo com regras bem definidas. Ainda que certos recursos, como a diferença e intersecção da AR, tenham sido depreciados, formas equivalentes podem ser geradas para produzir o mesmo resultado. Ao longo da explanação serão comparadas as consultas em SQL com as expressões equivalentes da álgebra relacional. Deve também ser ressaltado que a sintaxe da linguagem SQL, apesar de manter o seu padrão, pode variar de uma implementação para outra.

Além disso, algumas implementações podem oferecer mais mecanismos e recursos para geração de consultas mais eficazes. Procuraremos aqui manter o padrão da linguagem SQL-99. Salvo raras exceções, não se permitem caracteres acentuados para os nomes de atributos ou tabelas, quando estamos lidando com SQL. Os comandos em SQL geralmente são divididos em dois grupos: DML e DDL. 1. Linguagem de Manipulação de Dados ou DML (Data Manipulation Language): são os comandos para a manipulação de consultas ou mesmo de modificações de conteúdo de banco de dados. 2. Linguagem de Definição de Dados ou DDL (Data Definition Language): são os comandos para definição das estruturas das tabelas e dos tipos de atributos respectivos. Portanto, neste capítulo, serão vistos os comandos DML referentes às consultas em bancos de dados, e, no capítulo 5, aqueles que se referem à modificação (inclusão, alteração e exclusão) do banco de dados.

[sintaxe geral] Podemos definir a seguinte sintaxe geral para um comando SQL, composta de cláusulas padrão da linguagem mais um complemento: SQL (1): SELECT FROM WHERE

Onde: _ atributos: são os campos ou atributos das tabelas que serão mostrados separados por vírgulas; 96

banco_de_dados_

_ tabelas: são as tabelas pertinentes à consulta, também separadas por vírgulas; _ condição: deve conter uma condição de acordo com o objetivo da consulta. O comando pode ser expresso em uma única linha, não havendo restrições quanto à distribuição das cláusulas em linhas diferentes. SELECT FROM WHERE

Os comandos SQL são case insensitive*, sendo aqui colocados em maiúsculas por questões didáticas. Como exemplo, se considerarmos uma consulta, na tabela “Pedido”, que requisita a seleção apenas do registro cujo código é 1010, temos: PEDIDO codigo

codcli

data

1005

202

10/03/2004

valor 1.200,00

1008

221

13/03/2004

960,00

1010

233

17/03/2004

1.020,00

1015

282

20/03/2004

755,00

1023

202

21/03/2004

900,00

Nesse caso, o resultado (ou tabela de saída) a ser mostrado é: codigo

codcli

data

1010

233

17/03/2004

valor 1.020,00

E a consulta SQL que produz o resultado é:

* Ou seja, tanto faz a expressão formalizada em letras maiúsculas ou minúsculas.

97


SQL (2): SELECT * FROM pedido WHERE codigo = 1010

O asterisco (*), na linha da cláusula SELECT, significa que devem ser mostrados todos os atributos, economizando a expressão de todos os campos. Da mesma forma, é possível fazer comparações: SQL (3): SELECT * FROM pedido WHERE valor > 1.000,00

Produzindo, nesse caso: codigo

codcli

data

1005

202

10/03/2004

valor 1.200,00

1010

233

17/03/2004

1.020,00

Consultas SQL com condições compostas também são passíveis de serem geradas: SQL (4): SELECT * FROM pedido WHERE(valor > 1.000,00)AND(data < DATE’15/03/2004’)

Note o uso da cláusula lógica AND e também as condições entre parênteses. Observe que essas consultas são equivalentes a utilizar a operação de seleção (σ) da AR. Os operadores de comparação permitidos são semelhantes aos encontrados na linguagem Pascal: =, <>, <, >, <= e >=. Os operadores lógicos são AND, OR e NOT.

98

banco_de_dados_

Quadro 4.1 – Analogias entre comandos em AR e SQL SQL

Álgebra Relacional

SELECT * FROM pedido WHERE codigo = 1010

σcodigo=1010(pedido)

SELECT * FROM pedido WHERE valor > 1.000,00

σvalor>1000,00(pedido)

SELECT * FROM pedido WHERE(valor > 1.000,00) AND data < DATE’15/03/2004’)

σ(valor

(pedido)

>1.000,00) and (data <’15/03/2004’)

O conteúdo da tabela “Pedido” pode ser apresentado apenas por um comando: SQL (5): SELECT * FROM pedido

Sendo assim, as cláusulas SELECT e FROM são obrigatórias para qualquer comando SQL, enquanto que a cláusula WHERE é opcional. O equivalente a essa consulta, na AR, é apenas o nome da tabela “Pedido”. Ainda, convém destacarmos que em vez de querer apresentar todos os campos, podemos restringir a apresentação da tabela “Pedido” para campos específicos: codigo

valor

1005

1.200,00

1008

960,00

1010

1.020,00

1015

755,00

1023

900,00

Sendo, então, a consulta expressa como: SQL (6): SELECT codigo, valor FROM pedido

99


Nesse procedimento, a restrição ao código do pedido 1010 pode ser adicionada: SQL (7): SELECT codigo, valor FROM pedido WHERE codigo = 1010

Produzindo, como resultado: codigo

valor

1010

1.020,00

A equivalência da operação de projeção (π) da álgebra relacional é encontrada na sintaxe do SQL presente na cláusula SELECT. Quadro 4.2 – Analogias entre comandos em AR e SQL SQL

Álgebra Relacional

SELECT * FROM pedido

pedido

SELECT codigo, valor FROM pedido

π codigo,valor(pedido)

SELECT codigo, valor FROM pedido WHERE codigo = 1010

π codigo,valor(πcodigo=1010(pedido))

[junção] Como visto anteriormente em AR, a operação de junção tem por finalidade aglutinar duas tabelas mediante atributos em comum existentes nas mesmas. A operação de junção (em AR expressa pelo símbolo

)

tem sua contrapartida em SQL através do comando INNER JOIN (junção interna), sendo essa a expressão mais básica para operações de junção em SQL. A sintaxe é: INNER JOIN ON

100

banco_de_dados_

Onde “att_comum1” e “att_comum2” são respectivamente os atributos comuns das tabelas (tabela1 e tabela2). Lembramos que a operação de junção é binária, necessita, portanto, sempre de duas tabelas para ocorrer a operação, e que a igualdade entre os campos em comum é expressa após o comando ON. Porém, o comando INNER JOIN geralmente não é expresso como um comando SQL na maneira como foi apresentado na sintaxe, pois é colocado em conjunto com as cláusulas da sintaxe geral, vistos na seção anterior. Como a junção produz outra tabela em sua saída, esta é, por sua vez, colocada na cláusula FROM do comando SQL. No software Microsoft Access é obrigatória a expressão desse tipo de junção como INNER JOIN, porém em outras linguagens de consulta (como nas implementadas em MySQL e Interbase) basta a colocação da palavra JOIN. Utilizando-nos do mesmo exemplo abordado na AR, temos as tabelas “Clientes” e “Pedido”, a seguir: CLIENTES codigo

nome

telefone

202

Ernesto

3222-0809


221

Amélia

3233-2474

2.000,00

233

Luís Alberto

3323-0071

1.500,00

282

José Antonio

3343-9021

800,00

295

Carlos Silva

3224-5678

800,00

101


PEDIDO codigo

codcli

data

1005

202

10/03/2004

valor 1.200,00

1008

221

13/03/2004

960,00

1010

233

17/03/2004

1.020,00

1015

282

20/03/2004

755,00

1023

202

21/03/2004

900,00

Os campos em comum nas tabelas são “clientes.codigo” e “pedido. codcli”. A expressão da junção em SQL é: SQL (8): SELECT * FROM pedido INNER JOIN clientes ON pedido.codcli = clientes.codigo

O resultado produzido é: pedido. codcli data codigo

valor

clientes. nome codigo

telefone

limite_ compra

1005

202

10/03/2004 1.200,00 202

Ernesto 3222-0809 1.500,00

1008

221

13/03/2004

Amélia

1010

233

17/03/2004 1.020,00 233

Luís 3323-0071 1.500,00 Alberto

1015

282

20/03/2004

755,00 282


1023

202

21/03/2004

900,00 202

Ernesto 3222-0809 1.500,00

960,00 221

3233-2474 2.000,00

800,00

No resultado, podemos notar que a saída produzida contém os dois atributos que têm nomes idênticos (“codigo”), e, assim, os atributos são expressos com o nome da tabela de origem colocado antes e separado por um ponto (“.”). Comandos compostos podem ser efetuados facilmente, se utilizarmos tudo o que foi visto até aqui. Por exemplo, para mostrar apenas o nome e o telefone de quem possui o pedido 1010:

102

banco_de_dados_

SQL (9): SELECT nome, telefone FROM pedido INNER JOIN clientes ON pedido.codcli = clientes.codigo WHERE pedido.codigo = 1010

O uso de parênteses pode ajudar na compreensão do comando, principalmente em casos de comandos mais complexos, com mais de uma junção: SQL (10): SELECT nome, telefone FROM(pedido INNER JOIN clientes ON pedido.codcli = clientes.codigo) WHERE pedido.codigo = 1010

Tendo como resultado: nome

telefone

Luís Alberto

3323-0071

Na seqüência, apresentamos mais algumas analogias entre comandos em AR e SQL utilizando comandos compostos. Quadro 4.3 – Analogias entre comandos em AR e SQL SQL

Álgebra Relacional

SELECT * FROM pedido INNER JOIN clientes ON pedido.codcli=clientes.codigo

Pedido

SELECT nome, telefone FROM pedido INNER JOIN clientes ON pedido.codcli=clientes.codigo SELECT pedido.codigo, valor FROM pedido INNER JOIN clientes ON pedido. codcli= clientes.codigo WHERE pedido.codigo=1010

πnome, codigo

π

clientes

pedido.codcli=clientes.codigo

(pedido clientes)

telefone

pedido.codigo,valor

(σ

pedido.codcli=clientes.

(pedido

pedido.codigo=1010


clientes))

Agora, vamos a um caso mais complexo de junção, adicionando às tabelas anteriores (“Clientes” e “Pedido”) a tabela “Itens”. Os itens de pedido são relacionados à tabela “Pedido” através do campo em comum “codped”.

103


ITENS codigo

codped

qtd

001

1005

10

subtotal 1.200,00

002

1008

12

720,00

003

1008

2

240,00

004

1010

1

500,00

005

1010

2

335,00

006

1010

1

185,00

007

1015

1

125,00

008

1015

1

630,00

009

1023

1

400,00

010

1023

1

300,00

011

1023

1

200,00

Esse tipo de junção é referido na literatura como sendo equivalente à junção “theta” ou theta join existente na AR, a qual necessita de uma igualdade entre os atributos comuns. Outra sintaxe existente no padrão SQL é a junção natural, ou natural join, que não necessita da igualdade. Ainda existe a cross join (junção cruzada), que é equivalente ao produto cartesiano. As implementações de banco de dados apresentam diferentes formas de abordagem para os tipos de junção dados pelo padrão.

[consulta SQL com duas junções] Caso queiramos saber quais os itens do pedido comprado pelo cliente Luís Alberto, precisaremos de duas junções. A expressão da AR , para essa situação, é: πitens.codigo,itens.codped,itens.qtd,itens.total (σ clientes.nome= ‘ Luís Alberto ’ ((pedido 104

banco_de_dados_


clientes)

pedido.codigo=itens.codped

itens)

Como a operação de junção é comutativa, a seguinte expressão da AR gera o mesmo resultado: πitens.codigo,itens.codped,itens.qtd,itens.total (σclientes.nome= ‘ Luís Alberto ’ (itens

pedido.codigo=itens.codped

(pedido


clientes))

E a expressão da consulta em SQL torna-se: SQL (11): SELECT itens.codigo, itens.codped, itens.qtd, itens.subtotal FROM itens INNER JOIN(pedido INNER JOIN clientes ON pedido.codcli = clientes.codigo) ON pedido.codigo = itens.codped WHERE clientes.nome = ’Luís Alberto’

Ou para simplificar os campos a serem mostrados da tabela “Itens”, colocamos novamente o asterisco no termo a ela referente: SQL (12): SELECT itens.* FROM itens INNER JOIN(pedido INNER JOIN clientes ON pedido.codcli = clientes.codigo) ON pedido.codigo = itens.codped WHERE clientes.nome = ’Luís Alberto’

Ou, ainda, lembrando a propriedade comutativa da operação de junção, alterando a ordem das tabelas temos: SQL (13): SELECT itens.* FROM(pedido INNER JOIN clientes ON pedido.codcli = clientes. codigo) INNER JOIN itens ON pedido.codigo = itens.codped WHERE clientes.nome = ’Luís Alberto’

Com essa operacionalização, a tabela resultado é: ITENS codigo

codped

qtd

004

1010

1

subtotal 500,00

005

1010

2

335,00

006

1010

1

185,00

105


Também utilizamos esse raciocínio para montar consultas mais complexas usando mais de duas junções, bem como encadeando os comandos INNER JOIN e colocando os parênteses para separar cada junção.

[consultas SQL e produto cartesiano] O uso da operação de produto cartesiano (×), como visto anteriormente em AR, é equivalente a operações de junção. Por exemplo, para mostrar apenas o nome do cliente para o pedido 1010, vejamos o comando a seguir: SQL (14): SELECT clientes.nome FROM pedido INNER JOIN clientes ON pedido.codcli = clientes.codigo WHERE pedido.codigo = 1010

Com a seguinte expressão equivalente em AR: πcliente.nome (σ pedido.codigo=1010 (pedido


clientes))

Com produto cartesiano, temos a consulta equivalente: πclientes.nome (σ pedido.codigo=1010 e pedido.codcli=clientes.codigo(pedido × clientes)) E o comando SQL fica: SQL (15): SELECT clientes.nome FROM pedido, clientes WHERE pedido.codcli = clientes.codigo AND pedido.codigo = 1010

Note que a expressão para os campos em comum foi colocada na cláusula WHERE, juntamente com a condição da seleção, separadas as condições pela cláusula lógica AND. E as tabelas que fazem parte do produto cartesiano foram dispostas na cláusula FROM. 106

banco_de_dados_

Para a consulta com duas junções, colocada na seção anterior, também podemos fazer um comando equivalente em AR: πitens.codigo,itens.codped,itens.qtd,itens.total (σ(clientes.nome= ‘ LuÍs Alberto ’) e (pedido.codcli=clientes. codigo) e (pedido.codigo=itens.codped)

(pedido × clientes × itens))

Resultando no seguinte comando em SQL: SQL (16): SELECT itens.* FROM itens, pedido, WHERE pedido.codcli AND pedido.codigo = AND clientes.nome =

clientes = clientes.codigo itens.codped ’Luís Alberto’

Consultas mais complexas podem ser formuladas com mais tabelas, desde que sejam explicitadas as igualdades entre os campos em comum de cada tabela. No quadro, a seguir, são apresentadas algumas analogias adicionais para o produto cartesiano em AR e SQL. Quadro 4.4 – Analogias adicionais para o produto cartesiano em AR e SQL SQL

Álgebra Relacional

SELECT * FROM pedido,clientes

pedido × clientes

SELECT * FROM pedido,clientes WHERE pedido.codcli=clientes. codigo

pedido


clientes

[comparação de

campos alfanuméricos (strings)]

Podemos utilizar os operadores de comparação para strings, da mesma maneira que para números. A ordem a ser seguida nesses casos é a alfabética ou lexicográfica para cada posição da string. Assim, a palavra “casa” 107


será menor que “caso”, bem como menor que “casarão”. A ordem numérica também está associada à alfabética, de forma que a palavra “casa1” será menor que “casa2”. Outra forma poderosa de comparação de strings é sobre o casamento de padrões entre elas. Numa comparação desse tipo, expressões que tenham a forma: “s LIKE p”, retornam strings que atendam um certo padrão, sendo s uma variável ou string e p o padrão de ocorrência. Os caracteres especiais “%” e “_” são utilizados para identificar os padrões procurados em um conjunto de strings. Por exemplo, caso queiramos obter todos os nomes da tabela “Clientes” que comecem com Carlos, é possível utilizarmos a expressão SQL: SQL (17): SELECT nome FROM clientes WHERE nome LIKE “Carlos%”

O símbolo “%”, ao final, indica que serão considerados para a tabela de saída todos os nomes de clientes que comecem com Carlos. Caso queiramos todos os nomes que terminem com Silva, o símbolo “%” precisa estar no início do padrão de comparação: SQL (18): SELECT nome FROM clientes WHERE nome LIKE “%Silva”

Ou, se quisermos encontrar uma palavra qualquer dentro de um campo, não importando a posição onde ela esteja, colocamos no início e no final do padrão: SQL (19): SELECT nome FROM clientes WHERE nome LIKE “%Silva%”

108

banco_de_dados_

No caso de compararmos com um padrão que case exatamente em termos de caractere em vez de uma string, utilizamos o símbolo “_”. Assim, para uma consulta que deve retornar apenas os nomes que começam com a letra “L” e tenham apenas cinco letras, a obtemos com a consulta: SQL (20): SELECT nome FROM clientes WHERE nome LIKE “L _ _ _ _“

Os caracteres para casamento de padrões podem variar entre certas implementações. Em Microsoft Access, por exemplo, em vez de “%” usa-se “∗” (asterisco), e em vez de “_” usa-se “?”. Certos desenvolvedores web, quando utilizam sistemas de buscas em bancos de dados baseadas em SQL, na maior parte dos casos não se preocupam em interceptar a possibilidade do usuário colocar o caractere % no campo para busca. Uma busca que tenha a expressão campo LIKE ‘%’ retorna todos os registros que existem dentro da tabela para o atributo “campo”. Dependendo da quantidade de registros, o servidor de banco de dados pode ficar sobrecarregado com a pesquisa a ser efetuada. A utilização de operadores de comparação para data e para hora também é válida, pois internamente os bancos de dados utilizam formatos numéricos para conversão de data e de hora.

[trabalhando com data e com hora] Existe uma variedade de formatos de data e de hora que podem ser trabalhados em bancos de dados. A maioria implementa o tratamento de 109


forma diferenciada, não seguindo o padrão. De acordo com o padrão SQL2, um valor constante de hora deve ser expresso com o prefixo DATE, antes da data e entre aspas, assim como: DATE‘15/09/2004’

Para constantes referentes a hora, usamos o prefixo TIME: TIME‘09:03:02’

Os bancos de dados implementam formas diferentes de tratar data e hora. O Microsoft Access exige que seja colocado cerquilha (#) em vez de aspas e que seja sem a palavra-chave DATE. Assim, deve ser utilizado #15/09/2004# e #09:03:02#. Bancos de dados como o Interbase e o MySQL não necessitam de prefixo para ambos, data e hora, desde que estejam nos formatos corretos.

[ordenando os resultados] Uma consulta pode retornar os resultados ordenados de acordo com o tipo do atributo. Através do uso da cláusula ORDER BY, podemos colocar um ou mais atributos pelos quais queremos que apareça a ordem da consulta efetuada. Por exemplo, para retornar a tabela de clientes ordenada pelo nome do cliente: SQL (21): SELECT nome FROM clientes ORDER BY nome

Ou, ainda, pelo nome do cliente e telefone:

110

SQL (22): SELECT nome FROM clientes ORDER BY nome, telefone

banco_de_dados_

Se for atendido o critério de o nome do cliente começar com a letra “A”, temos: SQL (23): SELECT nome FROM clientes WHERE nome LIKE “A%” ORDER BY nome, telefone

Em vez de utilizarmos o nome do atributo, podemos também colocar o número de ordem, do mesmo, na tabela: SQL (24): SELECT nome FROM clientes WHERE nome LIKE “A%” ORDER BY 2,3

A ordenação pode ter sua seqüência invertida, ao colocarmos o qualificador DESC após o atributo. Assim a seguinte consulta apresenta os dados na ordem inversa de nomes: SQL (25): SELECT nome FROM clientes ORDER BY nome DESC

[consultas aninhadas ou subconsultas] Veremos a partir daqui formas mais aprofundadas de utilização da cláusula WHERE. Quando falamos em comparações nas seções anteriores, utilizamos números, caracteres ou strings. No entanto é possível fazer a comparação também com resultados provenientes de outras consultas que estejam acontecendo dentro da principal. Denominamos a isso de aninhamento de consultas ou subconsultas. Recordando o exemplo, já visto para o produto cartesiano, onde buscávamos o nome do cliente para o pedido 1010: 111


SQL (26): SELECT clientes.nome FROM pedido, clientes WHERE pedido.codcli = clientes.codigo AND pedido.codigo = 1010

Observe que, nesse exemplo, a expressão pode ser modificada considerando-se o uso de uma subconsulta. Assim, deve ser verificado se o código do cliente é igual ao retornado pelo resultado da subconsulta à tabela “Pedido”: SQL (27): SELECT clientes.nome FROM clientes WHERE clientes.codigo = SELECT pedido.codcli FROM pedido WHERE pedido.codigo = 1010)

Observe que essa expressão retorna o mesmo resultado e que, na cláusula WHERE, a comparação é feita usando o campo clientes.codigo com o resultado que vem da subconsulta. Analisando, agora, a subconsulta, são projetados para o campo pedido.codcli os registros da tabela “Pedido” que atendam à condição de que o código do pedido seja igual a 1010. Nesse caso, para aquele registro que atender à condição de igualdade (=) da cláusula WHERE da consulta principal (campo codigo na tabela “Clientes” igual ao campo codcli na tabela “Pedido”), o nome do cliente é mostrado no resultado. nome Luís Alberto

Um fato a ser considerado é que a subconsulta pode retornar apenas um valor. É como se a consulta que gerasse esse resultado fosse: SELECT cliente.nome FROM clientes WHERE clientes.codigo = 233

112

banco_de_dados_

Será gerado um erro, caso exista mais de um registro no resultado da subconsulta. Assim, o operador de igualdade (=) deve ser substituído por outro tipo de operador, para que seja possível executar a consulta. Tais operadores serão vistos na próxima seção. Outra observação pertinente a essa consulta aninhada deve ser feita. Note que o comando a seguir irá retornar apenas os campos presentes na tabela “Clientes”, devido ao uso do asterisco na cláusula SELECT e da referência à tabela “Clientes” na cláusula FROM da consulta principal: SQL (28): SELECT * FROM clientes WHERE clientes.codigo = SELECT pedido.codcli FROM pedido WHERE pedido.codigo = 1010

E o comando a seguir retorna todos os campos presentes nas tabelas “Clientes” e “Pedido”, devido à presença das duas tabelas na cláusula FROM. Portanto as duas formas de expressão não são perfeitamente equivalentes: SELECT * FROM pedido, clientes WHERE pedido.codcli = clientes.codigo AND pedido.codigo = 1010

[condições em consultas aninhadas] Certos operadores – também chamados de predicados ou qualificadores – em SQL podem ser aplicados a uma tabela ou relação e produzirem um valor lógico. Uma subconsulta pode retornar tal valor para uma consulta principal. Os operadores disponíveis são: IN, EXISTS, ALL e ANY.

113


O operador IN Vejamos a seguir um exemplo onde o resultado possa ser mais de um registro. Supondo que é preciso retornar os nomes de clientes com valor de pedido acima de R$1.000.00, fazemos: SQL (29): SELECT clientes.nome FROM clientes WHERE clientes.codigo IN (SELECT pedido.codcli FROM pedido WHERE pedido.valor > 1.000,00)

Resultando: nome Ernesto Luís Alberto

Note que foi substituído o operador de igualdade (=) pelo operador IN. Este operador atua na consulta indicando se o código do cliente está contido no resultado da subconsulta. Dessa maneira, o operador IN opera de forma mais genérica que o operador de igualdade (=). O operador EXISTS O operador EXISTS P indica uma condição que é verdadeira se e somente se P não for vazio. Entende-se aqui “P” como sendo o resultado (ou relação) de uma subconsulta aninhada. Se “P” não possui nenhum registro, então EXISTS P retorna falso, caso contrário retorna verdadeiro. O operador IN não deve ser interpretado como semelhante ao operador EXISTS. Enquanto IN faz a pergunta: “está contido?”, o operador EXISTS, por sua vez, pergunta: “existe algum registro?” 114

banco_de_dados_

A consulta a seguir modifica o exemplo dado para IN, utilizando agora EXISTS: SQL (30): SELECT clientes.nome FROM clientes WHERE EXISTS (SELECT pedido.codcli FROM pedido WHERE pedido.valor > 1.000,00)

A interpretação agora é diferente: se existir algum pedido maior que R$1.000.00, os nomes de clientes serão mostrados. Como existem dois registros, o conjunto da subconsulta não é vazio. Assim temos o resultado mostrando todos os nomes de clientes que estão na tabela “Clientes”. nome Ernesto Amélia Luís Alberto José Antonio Carlos Silva

O operador lógico NOT pode ser aplicado aos operadores IN e EXISTS, de forma a apresentar o complemento da operação. Assim, caso a consulta utilizando IN seja: SQL (31): SELECT clientes.nome FROM clientes WHERE clientes.codigo NOT IN (SELECT pedido.codcli FROM pedido WHERE pedido.valor > 1.000,00)

O resultado responde à seguinte pergunta: “Quais os nomes de clientes cujos pedidos não são maiores que 1.000.00?”. Nesse caso, a resposta é:

115


nome Amélia José Antonio Carlos Silva

No entando, se aplicarmos o operador NOT à consulta utilizada para o operador EXISTS, SQL (32): SELECT clientes.nome FROM clientes WHERE NOT EXISTS (SELECT pedido.codcli FROM pedido WHERE pedido.valor > 1.000,00)

o resultado será vazio, pois a pergunta da consulta agora é: “Não existem registros com valor de pedido maior que R$1.000,00?”. Como tais registros existem, a resposta para a pergunta retorna falso: nome

O operador ALL O operador ALL deve ser disposto na cláusula WHERE tal como campo > ALL subconsulta retornando uma condição verdadeira se e somente se campo for maior que todo e qualquer valor localizado em subconsulta. O operador de comparação (>) pode ser trocado ainda por outros operadores (>=, <, <=, = ou <>). Por sua vez, o operador lógico NOT pode ser aplicado à frente, negando a condição. Assim, para selecionar os registros, na consulta principal, que venham a satisfazer a comparação com todos os registros retornados pela subconsulta, utilizamos o predicado ALL. 116

banco_de_dados_

Como exemplo, montamos uma consulta com a cláusula ALL para retornar a informação do código e valor do pedido com maior subtotal existente dentro da tabela “Itens”, mencionada anteriormente: SQL (33): SELECT pedido.codigo, pedido.valor FROM pedido WHERE pedido.valor >= ALL (SELECT itens.subtotal FROM itens)

Com a seguinte relação como resultado: codigo

valor

1005

1.200,00

O operador ANY O operador ANY deve ser disposto tal como campo > ANY subconsulta retornando uma condição verdadeira se e somente se campo for maior que pelo menos um valor em subconsulta. O operador de comparação (>) pode ser trocado ainda por outros operadores (>=, <, <=, = ou <>). Sendo que o operador lógico NOT pode ser aplicado à frente, negando a condição. Assim, para selecionar os registros, na consulta principal, que venham a satisfazer a comparação com algum dos registros retornados pela subconsulta, utilizamos o predicado ANY (ou também SOME). Como exemplo, desejamos saber, dentro da tabela “Pedido”, quais são os que possuem itens cujo subtotal são 50% ou mais do valor do pedido. Então:

117


SQL (34): SELECT pedido.codigo, pedido.valor FROM pedido WHERE pedido.valor >= ANY (SELECT itens.subtotal FROM itens WHERE itens.subtotal > pedido.valor * 0.5 AND itens.codped = pedido.codigo)

E a seguinte relação como resultado: codigo

valor

1005

1.200,00

1008

960,00

1015

755,00

Ou seja, retornaram aqueles pedidos e seus valores nos quais existe itens cujo subtotal seja maior ou igual a 50% do valor total do pedido. Note que é permitido usar expressões na comparação utilizando operações algébricas. Algumas equivalências podem ser encontradas entre os operadores. Por exemplo campo = ANY subconsulta campo é igual a pelo menos um registro em subconsulta. E o mesmo que campo IN subconsulta campo está contido em subconsulta. Assim como para o operador ALL campo <> ALL subconsulta campo não é igual a todo e qualquer registro em subconsulta. É o mesmo que campo NOT IN subconsulta campo não está contido em subconsulta. 118

banco_de_dados_

[eliminando registros duplicados] Em alguns resultados de consultas, pode ser que apareçam registros com valores duplicados. O uso da palavra-chave DISTINCT, presente na cláusula SELECT, faz com que seja apresentado apenas um dentre os registros repetidos que possam aparecer. Por exemplo, se quisermos obter os códigos de pedidos a partir da tabela “Itens”, lançamos a seguinte consulta: SELECT codped FROM itens

Obtendo o seguinte resultado: codped 1005 1008 1008 1010 1010 1010 1015 1015 1023 1023 1023

Para eliminar os registros que aparecem em duplicata, modificamos o comando SQL para: SQL (35): SELECT DISTINCT codped FROM itens

119


Obtendo somente um código para cada pedido. Assim: codped 1005 1008 1010 1015 1023

[união] A operação de união em SQL é equivalente à existente para AR. A representação de união de duas tabelas “A” e “B” faz-se: A U B. Uma ressalva a ser feita é que o esquema das duas tabelas tem que ser igual, ou seja, conter os mesmos atributos. Assim, se temos duas tabelas, “Computador” e “Notebook”, com os seguintes atributos: COMPUTADOR(codigo, CPU, RAM, HD, CD, preco) NOTEBOOK(codigo, CPU, RAM, HD, Tela, preco)

A seguinte união em AR pode ser efetuada: πcodigo,preco(Computador) U πcodigo,preco(Notebook) Em SQL, utilizamos a cláusula UNION para efetuar a união. O comando SQL equivalente à consulta acima é: SQL (36): SELECT codigo, preco FROM computador UNION SELECT codigo, preco FROM notebook

120

banco_de_dados_

Veja que é preciso fazer dois comandos SQL e ligá-los pela cláusula UNION. Caso existam registros repetidos, a operação de união irá automaticamente eliminá-los. Porém, se for desejado o contrário, o qualificador ALL pode ser usado para a operação apresentar todos os registros, inclusive os repetidos: SQL (37): SELECT codigo, preco FROM computador UNION ALL SELECT codigo, preco FROM notebook

[diferença] A operação de diferença, apesar de não ser implementada em alguns bancos de dados, também é equivalente à existente na AR. A representação de diferença de duas tabelas “A” e “B” faz-se A − B*. A seguinte diferença em AR, portanto, é possível de ser efetuada: πcodigo (clientes) − πcodcli (pedido) Em SQL, utilizamos a cláusula EXCEPT (do padrão SQL) ou MINUS para efetuar a diferença. O comando SQL equivalente à consulta acima é: SQL (38): SELECT codigo FROM clientes EXCEPT SELECT codcli FROM pedido

ou

* A mesma ressalva feita à operação de união vale aqui, ou seja, que o esquema das duas tabelas seja igual, contenha os mesmos atributos.

121


SELECT codigo FROM clientes MINUS SELECT codcli FROM pedido

Produzindo: codigo 295

Os bancos de dados PostgreSQL e DB2 utilizam o EXCEPT. Em Oracle é utilizado o MINUS. O Microsoft SQL Server, o Sybase, o Microsoft Access, o Interbase e o MySQL não implementam a operação de diferença, devendo a mesma ser simulada. O PostgreSQL e o DB2 também suportam o EXCEPT ALL. Para simular um comando equivalente em SQL para diferença, utilizamos o operador EXISTS, visto anteriormente. Assim, para saber qual cliente não efetuou pedido, é verificado se existe na tabela “Pedido” algum código de pedido que esteja presente nas duas tabelas: SQL (39): SELECT clientes.codigo FROM clientes WHERE NOT EXISTS (SELECT pedido.codcli FROM pedido WHERE pedido.codcli = clientes.codigo)

A subconsulta fornece a existência de algum registro que atende à condição na sua cláusula WHERE, ou seja, se o código do cliente está presente na tabela “Pedido”. Note que na igualdade o atributo “codcli” da subconsulta é comparado com o atributo “codigo” da consulta principal. Esta é, então, equivalente à consulta com a cláusula EXCEPT.

122

banco_de_dados_

[interseção] Da mesma forma que a operação de diferença, a representação da operação de interseção é equivalente à AR. Duas tabelas “A” e “B” são operadas com interseção fazendo A ∩ B*. A seguinte interseção em AR pode ser efetuada: πcodigo (clientes) ∩ πcodcli (pedido) Em SQL, utilizamos a cláusula INTERSECT do padrão SQL para efetuar a interseção. O comando SQL equivalente à consulta supracitada é: SQL (40): SELECT codigo FROM clientes INTERSECT SELECT codcli FROM pedido

Produzindo: codigo 202 221 233 282

Os bancos de dados PostgreSQL, DB2 e Oracle utilizam o INTERSECT. O Microsoft SQL Server, o Sybase, o Microsoft Access, o Interbase e o MySQL não implementam a operação de interseção, devendo a mesma ser simulada. O PostgreSQL, o DB2 e o Oracle também suportam o INTERSECT ALL. * A mesma ressalva feita à operação de união e diferença vale aqui, ou seja, que os esquemas das duas tabelas sejam iguais, contenham os mesmos atributos.

123


O comando equivalente, para simular o comportamento de interseção, utiliza também a cláusula EXISTS de maneira complementar à diferença: SQL (41): SELECT clientes.codigo FROM clientes WHERE exists (SELECT pedido.codcli FROM pedido WHERE pedido.codcli = clientes.codigo)

O comando seria idêntico ao utilizado para a diferença se não fosse a ausência do operador lógico NOT.

[renomeando tabelas] Vimos anteriormente que na AR temos um operador para modificar o nome de uma tabela, o operador de renomeação (ρ). Se quisermos mudar o nome da tabela “Clientes” para “C”, fazemos: ρC (clientes) Em SQL, utilizamos a palavra-chave AS para renomear uma tabela. Isso é justificável quando tem o objetivo de simplificar a escrita dos comandos, e, também, quando fazemos referência à mesma tabela mais de uma vez. Desse modo, o seguinte comando, para buscar o nome do cliente para o pedido 1010, pode ser simplificado de: SELECT cliente.nome FROM pedido, clientes WHERE pedido.codcli = clientes.codigo AND pedido.codigo = 1010

para: SQL (42): SELECT cliente.nome FROM pedido AS p, clientes AS c WHERE p.codcli = c.codigo AND p.codigo = 1010

124

banco_de_dados_

A consulta equivalente em AR é: πc.nome (σ p.codigo=1010 e p.codcli=c.codigo(ρp (pedido) × ρc (clientes))) No próximo exemplo, se quisermos conhecer o código dos clientes que efetuaram mais de um pedido, podemos fazer: SQL (43): SELECT p.codcli FROM pedido AS p WHERE p.valor < ANY (SELECT pedido.valor FROM pedido WHERE pedido.codcli = p.codcli)

Outro comando SQL, utilizando renomeação de tabelas, pode ser feito para uma consulta na qual queiramos obter o valor maior ou menor dentro de certo atributo. Por exemplo, se quisermos obter o pedido de maior valor, fazemos: SQL (44): SELECT pedido.valor FROM pedido WHERE pedido.valor NOT IN (SELECT p1.valor FROM pedido AS p1, pedido AS p2 WHERE pedido.codigo = p1.codigo) AND p1.valor < p2.valor)

Em AR, vimos um exemplo, utilizando diferença, que pode ser afirmado como equivalente ao comando SQL anterior: πpedido.valor (pedido) − πp1.valor (σp1.valor
[renomeando atributos] Assim como é possível renomear tabelas, também, o é em relação aos atributos de uma consulta em SQL. Tal operação tem como objetivo melhorar o formato do resultado de maneira a mostrar outra denominação em vez 125


do nome dos atributos. Sendo, inclusive, permitido fazer operações matemáticas com eles. A palavra-chave AS também é utilizada para essa tarefa. Por exemplo, se quisermos modificar o nome dos campos de uma consulta para, assim, retornar o código do pedido e o valor para o pedido 1010, fazemos: SQL (45): SELECT codcli AS codigo_do_cliente, valor AS total_do_pedido FROM pedido WHERE código = 1010

O resultado mostrado é: codigo_do_cliente 233

total_do_pedido 1.020,00

Outra variação permitida é “embutir” um atributo constante junto à saída: SQL (46): SELECT ‘codigo’ AS titulo, codcli AS codigo_do_cliente, valor AS total_do_pedido FROM pedido WHERE codigo = 1010

Onde “codigo” é uma string constante que aparece à esquerda do código do cliente, e “titulo” é o nome do campo fictício: titulo

codigo_do_cliente

codigo

233


Algo que aumenta ainda mais o poder de formatação de uma saída em SQL é o uso de operações matemáticas sobre os atributos. Caso queiramos mostrar a saída em outro formato de moeda (dólar), podemos multiplicar o atributo pelo valor de conversão (no exemplo, R$2,90 por dólar) na própria cláusula SELECT:

126

banco_de_dados_

SQL (47): SELECT ’codigo’ AS titulo, codcli AS codigo_do_cliente, valor*2.90 AS total_do_pedido FROM pedido WHERE codigo = 1010

E assim: titulo

codigo_do_cliente

codigo

233


[agregação] Através da agregação, efetuamos uma operação que resulta num valor único para uma lista de valores que aparecem em um atributo. Alguns exemplos são soma de valores e a contagem absoluta de registros. A operação é feita mediante o uso de uma função para o atributo desejado, inserido na cláusula SELECT. Podemos renomear a função de agregação para apresentação dos dados na tabela de resultado. Veja as funções de agregação previstas para o padrão SQL no quadro a seguir: Quadro 4.5 – Funções de agregação para o padrão SQL Comando de agregação

Finalidade

SUM

Executa a soma dos valores existentes sobre o atributo especificado. É válido apenas para atributos de tipo numérico.

AVG

Calcula a média dos valores existentes sobre o atributo especificado. Também é válido apenas para atributos de tipo numérico.

MIN

Retorna o valor mínimo dentre os valores existentes no atributo especificado. Válido apenas para atributos de tipo numérico.

MAX

Retorna o valor máximo dentre os valores existentes no atributo especificado. Válido apenas para atributos de tipo numérico.

COUNT

Retorna a quantidade de valores existentes no atributo especificado. Válido também para atributos de outros tipos que não o numérico.

127


Exemplos 1. Encontre o valor total dos pedidos: SQL (48): SELECT SUM(valor) FROM pedido

Resultado: SUM(valor) 4.835,00

2. Encontre o valor médio dos pedidos maiores que R$1.000,00: SQL (49): SELECT AVG(valor) AS media FROM pedido WHERE valor > 1.000,00

Resultado: media 1.110,00

3. Encontre o valor mínimo dos pedidos feitos antes de 15/03/2004: SQL (50): SELECT MIN(valor) AS minimo FROM pedido WHERE data < DATE’15/03/2004’

Com o resultado: minimo 960,00

4. Encontre o valor máximo dos pedidos feitos entre 15/03/2004 e 30/03/2004: 128

banco_de_dados_

SQL (51): SELECT MAX(valor) AS maximo FROM pedido WHERE data > DATE’15/03/2004’ AND data < DATE’30/03/2004’

Com o resultado: maximo 1.020,00

5. Encontre quantos são os clientes existentes na tabela “Clientes”: SQL (52): SELECT COUNT(codigo) AS total FROM clientes

Com o resultado: total 5

A função de agregação COUNT pode ser expressa também de forma genérica, sem especificar campo, colocando um asterisco como argumento: SQL (53): SELECT COUNT(*) AS total FROM clientes

COUNT considera todos os valores inclusive os duplicados. Para considerar na contagem apenas um registro de cada valor repetido, podemos usar DISTINCT.

[agrupamento] Considere, agora, que não apenas é desejada uma consulta com agregação para trazer um valor único sobre os valores de um atributo, mas que possamos agrupar conforme um atributo e obter mais de um resultado. 129


Utilizando a cláusula GROUP BY, é possível retornar um valor de agregação agrupado pelo atributo especificado. Assim, se quisermos obter a soma dos itens para cada pedido na tabela “Itens”, utilizaremos a função de agregação SUM para somar os subtotais referentes a cada item, agrupando pelo código do pedido: SQL (54): SELECT codped, SUM(subtotal) FROM itens GROUP BY codped

Obteremos assim o seguinte resultado, com os valores agrupados pelo atributo “codped”: codped

SUM(subtotal)

1005

1.200,00

1008

960,00

1010

1.020,00

1015

755,00

1023

900,00

[a cláusula HAVING] Utilizando a cláusula HAVING podemos expressar uma condição à qual os grupos tenham que atender. Assim é possível fazer com que o resultado de uma consulta agrupada seja restringido a determinado critério, como, por exemplo, que certos grupos não apareçam no resultado. Nesse processo, a cláusula HAVING sempre sucede a cláusula GROUP BY definindo tal condição. Por exemplo, se quiséssemos, na consulta anterior, modificá-la para obter apenas os pedidos que tenham dois itens, utilizaríamos a seguinte consulta: 130

banco_de_dados_

SQL (55): SELECT codped, SUM(subtotal) FROM itens GROUP BY codped HAVING COUNT(codped)=2

codped

SUM(subtotal)

1008

960,00

1015

755,00

Comparações na cláusula HAVING também são permitidas para atributos simples, desde que os atributos estejam devidamente especificados no agrupamento na cláusula SELECT. Porém, é recomendável a utilização da cláusula WHERE para esse tipo de comparação. Por exemplo, se quisermos agrupar apenas para os pedidos maiores que 1010, faremos: SQL (56): SELECT codped, SUM(subtotal) FROM itens GROUP BY codped HAVING codped > 1010

codped

SUM(subtotal)

1015

755,00

1023

900,00

[valores nulos] A SQL possui um valor especial de atributo denominado NULL representando a inexistência de valor. O NULL é diferente de zero (0) ou de um campo preenchido com brancos. Geralmente o NULL é criado após uma inserção de um registro, na qual não foi prevista a colocação de um determinado valor para o atributo em questão. Comparações, para verificar se um campo é nulo ou não, são válidas e devemos usar o operador IS para tais comparações. Por exemplo, para verificar se existe algum 131


cliente onde o telefone seja nulo (ou seja, não existe valor colocado), efetuamos a consulta: SQL (57): SELECT nome FROM clientes WHERE telefone IS NULL

O resultado é: nome

O operador lógico NOT pode ser colocado para negar a consulta, ou seja, verificar se não existem valores nulos para telefone ou, ainda, se existe algum valor no atributo telefone: SQL (58): SELECT nome FROM clientes WHERE telefone IS NOT NULL

Retornando no caso todos os registros presentes: nome Ernesto Amélia Luís Alberto José Antonio Carlos Silva

132

banco_de_dados_

[junção externa] Outro tipo de junção utilizado é a junção externa (ou outer join), pois, como vimos anteriormente, nesta o resultado mostra a tabela aglutinada pelos atributos em comum. Nesse caso, registros que não possuam campos em comum não são mostrados. Com outer join, é possível mostrar na tabela do resultado os campos que não tiveram correspondentes, sendo que os valores destes campos serão mostrados com NULL. A sintaxe do SQL utiliza o comando OUTER JOIN. A junção externa pode acontecer em dois caminhos. Como temos duas tabelas na operação de junção, a ocorrência de registros adicionais, vindos da primeira ou da segunda tabela, são possíveis de se verificarem. Para mostrar registros da primeira tabela, a junção externa é referida como “à esquerda” (LEFT OUTER JOIN); para mostrar registros da segunda tabela, a referência é “à direita” (RIGHT OUTER JOIN) e para mostrar os registros de ambas, devemos colocar como FULL OUTER JOIN. a. Por exemplo, se quisermos mostrar a junção externa para todos os registros à esquerda e à direita das tabelas “Clientes” e “Pedido”, fazemos: SQL (59) SELECT * FROM clientes FULL OUTER JOIN pedido ON clientes.codigo = pedido. codcli

b. Se quisermos apenas os registros adicionais da tabela “Clientes” (que está à esquerda da junção), executamos: SQL (60): SELECT * FROM clientes LEFT OUTER JOIN pedido ON clientes.codigo = pedido. codcli

133


Dessa maneira, conseguimos o seguinte resultado: clientes. nome codigo

limite_ compra

telefone


valor

202

Ernesto 3222-0809 1.500,00 1005

202

10/03/2004 1.200,00

202

Ernesto 3222-0809 1.500,00 1023

202

21/03/2004

900,00 960,00

221

Amélia

3233-2474 2.000,00 1008

221

13/03/2004

233

Luís 3323-0071 1.500,00 1010 Alberto

233

17/03/2004 1.020,00

282


800,00 1015

282

20/03/2004

295

Carlos Silva

800,00 NULL

NULL

NULL

3224-5678

755,00 NULL

Observe que para o cliente que possui dois pedidos, estes aparecem agrupados, pois os registros são agrupados respeitando a seqüência do atributo comum. Isso é o que verificamos, no resultado acima, para os campos na tabela da esquerda que repetem o atributo comum na tabela da direita (“clientes.codigo”). c. Para os registros adicionais da tabela “Pedido” (que está à direita da junção), veja, a seguir, a consulta com a junção externa à direita: SQL (61): SELECT * FROM clientes RIGHT OUTER JOIN pedido ON clientes.codigo = pedido.codcli

Temos o seguinte resultado: clientes. nome codigo

telefone

limite_ compra


valor

202

Ernesto 3222-0809 1.500,00 1005

202

10/03/2004 1.200,00

221

Amélia

3233-2474 2.000,00 1008

221

13/03/2004

233

Luís 3323-0071 1.500,00 1010 Alberto

233

17/03/2004 1.020,00

282


800,00 1015

282

20/03/2004

755,00

202

Ernesto 3222-0809 1.500,00 1023

202

21/03/2004

900,00

134

banco_de_dados_

960,00

Onde o que ocorreu foi que, agora, o agrupamento mudou, sendo que a ordem dos registros foi dada pela seqüência do atributo comum (pedido. codcli) na tabela “Pedido”. d. Exemplo de uma consulta prática utilizando OUTER JOIN, com as tabelas anteriores, é obter aqueles clientes que não tenham feito pedido. Mediante a operação de diferença da AR é possível expressar uma consulta e utilizando OUTER JOIN, fazemos: SQL (62): SELECT * FROM clientes LEFT OUTER JOIN pedido ON clientes.codigo = pedido. codcli WHERE pedido.codigo IS NULL

Onde, na condição da cláusula WHERE, comparamos se o código do pedido possui o valor NULL. O resultado, então, a ser mostrado é: clientes. nome codigo 295

telefone

Carlos 3224Silva 5678

limite_ compra

pedido. codigo

codcli

data

valor

800,00

NULL

NULL

NULL

NULL

A maioria dos bancos de dados concorda quanto à sintaxe dada pelo padrão SQL. Em Oracle é implementada uma forma simplificada de representar outer join utilizando a forma equivalente do produto cartesiano em SQL e adicionando o conjunto de símbolos (+) na igualdade dos campos em comum, à esquerda ou à direita.

[divisão e resto] Apesar de existir em SQL um padrão para consultas utilizando união, interseção e diferença, não há uma forma para a operação de divisão da AR4. No entanto é viável simular a operação utilizando consultas 135


aninhadas com NOT EXISTS. Assim, se quisermos saber os códigos de fornecedores que fornecem todos os produtos (retomamos aqui o exemplo anterior, visto em AR) a consulta em AR é: prodfor ÷ πcodigo(estoque) Por sua vez, o comando SQL que executa essa operação é: SQL (63): SELECT DISTINCT p1.codfor FROM prodfor AS P1 WHERE NOT EXISTS (SELECT e1.codigo FROM estoque AS E1 WHERE NOT EXISTS (SELECT * FROM prodfor AS P2 WHERE p1.codfor = p2.codfor AND p2.codprod = e1.codigo ))

Para o cálculo do resto, é preciso utilizar NOT IN. Assim a base do comando SQL para a consulta é o mesmo anterior, porém aplicando agora a diferença, portanto: SQL (64): SELECT p3.codfor FROM prodfor AS P1 WHERE p3.codigo NOT IN (SELECT DISTINCT p1.codfor FROM prodfor AS P1 WHERE NOT EXISTS (SELECT e1.codigo FROM estoque AS E1 WHERE NOT EXISTS (SELECT * FROM prodfor AS P2 WHERE p1.codfor = p2.codfor AND p2.codprod = e1.codigo )))

Essa consulta tem o seu equivalente em AR, que é: πcodfor (prodfor) − prodfor ÷ πcodigo (estoque)

136

banco_de_dados_

[cláusula SELECT com

expressões aritméticas]

Como foi visto, renomeamos os campos a serem mostrados em uma consulta SQL. Porém, convém lembrarmos que é possível efetuar cálculos aritméticos usando várias operações de agregação para obter algum resultado. Por exemplo, pelas tabelas “Itemprod” e “Estoque”, visualizamos o preço praticado e o custo de cada produto. Para fazer um cálculo do lucro obtido e da margem para todos os produtos vendidos, podemos utilizar a seguinte consulta: SQL (65): SELECT SUM(itemprod.preco - estoque.custo) AS lucro, (AVG(itemprod.preco / estoque.custo)-1)*100 AS margem FROM itemprod LEFT OUTER JOIN estoque ON itemprod.codprod = estoque.codigo

Note que foram utilizadas duas cláusulas de agregação: SUM para o cálculo do lucro e AVG para o cálculo da margem. Agora, se for necessário entrar no detalhe do lucro de cada produto ou da margem, exclui-se as operações de agregação, ordenando-se a consulta de forma inversa: SQL (66): SELECT itemprod.codprod, (itemprod.preco - estoque.custo) AS lucro,((itemprod.preco / estoque.custo)-1)*100 AS margem FROM itemprod LEFT OUTER JOIN estoque ON itemprod.codprod = estoque.codigo ORDER BY lucro DESC

O que produz o seguinte resultado: codprod

lucro

margem

2044

180,00

56,25

2044

180,00

56,25

3020

160,00

114,29

3020

160,00

114,29

2080

140,00

50,00

137


codprod

lucro

margem

3033

70,00

63,64

4101

50,00

66,67

4101

50,00

66,67

3050

45,00

52,94

2001

40,00

50,00

2001

40,00

50,00

2050

25,00

71,43

Ou ordenando pela margem de maneira inversa: SQL (67): SELECT itemprod.codprod, (itemprod.preco - estoque.custo) AS lucro,((itemprod.preco / estoque.custo)-1)*100 AS margem FROM itemprod LEFT OUTER JOIN estoque ON itemprod.codprod = estoque.codigo ORDER BY margem DESC

O que por sua vez produz como resultado o seguinte: codprod

lucro

margem

3020

160,00

114,29

3020

160,00

114,29

2050

25,00

71,43

4101

50,00

66,67

4101

50,00

66,67

3033

70,00

63,64

2044

180,00

56,25

2044

180,00

56,25

3050

45,00

52,94

2080

140,00

50,00

2001

40,00

50,00

2001

40,00

50,00

Notamos, desse modo, que, apesar do produto 2044 resultar em maior lucro dentre todos, é o produto 3020 que apresenta maior margem.

138

banco_de_dados_

[sintaxe estendida] Com os outros comandos vistos aqui, é possível elaborar uma sintaxe mais estendida para uso de consultas SQL: SELECT [[AS ] | [[SUM|MIN|MAX|AVG|COUNT](atributo)],... FROM [] | [ AS ] | [ [INNER|[LEFT|RIGHT OUTER]] JOIN ON ]... WHERE [NOT] [AND|OR] [NOT] ... GROUP BY HAVING ORDER BY [DESC]

Ainda quanto às condições da cláusula WHERE podemos ter: ::= [=|>|>=|<|<=|<>|LIKE] | IS [NOT] NULL

[resumo] A linguagem SQL pode ser considerada a linguagem mais comum entre as linguagens de consulta, devendo ser considerada também como uma linguagem de banco de dados. As características da linguagem SQL estendem bastante a força de expressão da “álgebra relacional”. Os comandos SQL são divididos em DML (Data Manipulation Language) e DDL (Data Definition Language). A sintaxe geral possui as palavras-chave, que são: SELECT, que seleciona os campos para a consulta; FROM, que se refere às tabelas envolvidas na consulta; WHERE, que encerra a condição da consulta. Essas palavras-chave ou cláusulas permitem representar uma consulta equivalente à seleção, à projeção, ao produto cartesiano e à junção da “álgebra relacional”. Existem ainda as consultas aninhadas, que possuem dois conjuntos de cláusulas SELECT, uma interna à outra. As operações de união, intersecção e diferença também podem ser simuladas em SQL. A linguagem SQL permite ainda as operações de agrupamento, agregação, manipulação de valores nulos, vários tipos de junção e expressões aritméticas. princípios_e_prática_

139

[exercícios] 1. Com base nas tabelas a seguir, expresse as consultas na linguagem SQL e mostre a tabela resultante para cada questão pedida. PEDIDO codigo

codcli

data

0001

100

03/10/2003

valor 133,00

0002

102

04/10/2003

45,00

0003

105

05/11/2003

339,50

0004

110

10/11/2003

30,00

0005

100

12/11/2003

52,50

CLIENTE codigo

nome

telefone

cidade

UF

100

Luís Paulo

3355-1027

Curitiba

PR

102

José Antonio

3452-3528

Lapa

PR

105

Carlos Lima

3233-3456

Joinville

SC

110

Maria de Castro

3441-8930

Ponta Grossa

PR

114

Danilo Silva

3353-4020

Curitiba

PR

ITENS codped

coditem

qtdped

0001

2010

3

108,00

0001

2020

10

25,00

0002

3004

30

45,00

0003

4011

2

50,00

0003

4013

5

137,50

0003

4025

4

152,00

0004

3004

20

30,00

0005

3004

10

15,00

0005

4013

5

137,50

140

banco_de_dados_

subtotal

FORNECEDOR codigo

nome

telefone

cidade

UF

501

ABC Cirúrgica

3772-4001

São Paulo

SP

502

Thermo

3873-5030

Salvador

BA

535

Distrib. Silva

3444-5523

Joinville

SC

550

CLS

3352-2353

Curitiba

PR

590

AKL Equip.

3330-8252

Porto Alegre

RS

ESTOQUE codigo

descricao

un

qtd

estmin

ptoped

codfor

2000

Termômetro

un

preco 12,00

36

5

15

501

2010

Termômetro Digital

un

36,00

12

5

15

501

2020


Pct c/10

2,50

40

10

50

535

3004

Esparadrapo

Rl c/10m

1,50

130

20

100

535

4011


Cx. c/ 100 un

25,00

43

15

60

550

4013


Cx. c/ 100 un

27,50

43

15

60

550

4025


Cx. c/ 100 un

38,00

43

15

60

550

5001

Ap.Pressão

un

6

1

5

590

205,00

a. Quais os itens que foram comprados pelo cliente de número 100? Mostre o código do item. b. Quais os itens no estoque que estão abaixo do ponto de pedido? Apresente a descrição, quantidade e ponto de pedido. c. Liste os pedidos efetuados pelo cliente Luís Paulo. 141


d. Dê o nome do cliente que comprou o produto mais caro. Apresente o nome e o telefone. e. Quais os pedidos adquiridos no mês de novembro? Apresente o código. f. Dê o código e a descrição dos produtos que estão abaixo do estoque mínimo. g. Quais os itens no estoque que não foram comprados? Apresente código e descrição. h. Quais clientes não efetuaram pedido? Mostre nome e telefone. i. Faça uma lista de nomes e telefones de clientes e fornecedores. j. Qual o nome e o telefone dos fornecedores do pedido 0005? k. Qual o valor total do estoque? l. Qual o item de menor valor no estoque? m. Qual o item de maior valor no estoque? n. Qual o valor médio dos itens no estoque? o. Quantos itens existem no estoque? p. Quantos clientes estão cadastrados?

142

banco_de_dados_

2. Com base no conjunto de tabelas a seguir, efetue as consultas na linguagem SQL: CLIENTES codigo

nome

telefone

datanasc

1010

Carlos Alberto

3323-2090

12/05/1975

1012

Maria Albertina

3221-0304

15/07/1980

1015

João Carlos

3234-5678

10/04/1977

1021

Teresa Silva

3313-4455

23/02/1982

FILMES codigo

titulo

duracao

300

Soldado Universal VI

303

Robin Hood

1,70

310

A Bela e a Feia

2,20

330

Bye Bye Brasil

1,50

335

Titanic

3,20

2,10

FITA codigo

codfilme

valorloc

5110

300

1,50

5111

300

1,50

5112

303

1,70

5113

310

1,70

5117

330

2,00

5125

335

2,10

5127

335

2,10

LOCAÇÃO codfita

codcli

dataloc

dataent

5110

1021

10/04/2004

12/04/2004

5111

1015

10/04/2004

5113

1010

11/04/2004

5127

1010

12/04/2004

13/04/2004

143


a. Qual o código das fitas que não foram locadas? b. Qual é o valor da locação do filme Titanic? c. Qual é o filme mais demorado? (Presuma saber antecipadamente o filme.) d. Qual o nome de quem locou a fita no dia do seu aniversário? e. Quais clientes (nome e telefone) fazem aniversário no mês de fevereiro? f. Identifique (nome e telefone) os clientes que não locaram fitas. g. Qual o total em reais (R$) de fitas existentes no estoque? h. Qual o valor médio em reais (R$) de fitas existentes no estoque? i. Quantos clientes existem na tabela CLIENTES?

144

banco_de_dados_

0000_0101 = V

modificações no_banco_de dados_

No capítulo anterior, abordamos de diversas formas como extrair informações de tabelas existentes em um banco de dados mediante consultas com comandos SQL. Esse é um processo, agora,

para

inserir registros, excluir registros ou alterar os dados existentes em um registro, é preciso utilizar outra série de comandos DML. A seguir veremos em detalhes a sintaxe para cada uma dessas modificações a serem feitas, as quais sejam: inclusão (comando INSERT), alteração (comando UPDATE) ou exclusão (comando DELETE).

[inserção de registros] Para inserir um novo registro em uma tabela, utilizamos o comando INSERT, cuja sintaxe genérica é: INSERT INTO (, ,..., ) VALUES (, ,... ))

Onde: _ < tabela> é o nome da tabela escolhida para inclusão de dados; _ < campo1>, ,..., são os campos da tabela onde serão incluídos dados; _ < valor1>, ,... são os valores que serão incluídos nos campos respectivos.

Por exemplo, vamos supor que desejemos incluir mais um campo na tabela “Pedido” trabalhada nos exemplo de consulta do capítulo anterior. Assim: SQL (68): INSERT INTO pedido(codigo, codcli, data, valor) VALUES(1028, 202, DATE’01/04/2004’, 600,00)

Portanto o tipo de dado do campo em questão deve ser considerado para o valor a ser incluído, senão o SGBD irá emitir um erro. Quando queremos incluir dados em todos os campos, podemos omitir a descrição dos mesmos no comando INSERT: SQL (69): INSERT INTO pedido VALUES(1028, 202, DATE’01/04/2004’, 600,00)

A inclusão de dados em um registro não precisa contemplar, necessariamente, todos os campos. Caso queiramos incluir apenas o código do pedido e do cliente, o comando pode ser dado da seguinte maneira: SQL (70): INSERT INTO pedido(codigo, codcli) VALUES(1028, 202)

Na tabela, um registro é incluído com os dados de código do pedido e código do cliente. O campo data e valor assumem o valor NULL. PEDIDO codigo

codcli

data

1005

202

10/03/2004

1.200,00

1008

221

13/03/2004

960,00

1010

233

17/03/2004

1.020,00

1015

282

20/03/2004

755,00

1023

202

21/03/2004

900,00

1028

202

NULL

148

banco_de_dados_

valor

NULL

A inclusão de registros deve respeitar as restrições definidas para a tabela em questão. Se um campo for definido para não aceitar valores nulos, a inclusão resulta em erro. Campos que são definidos como chaves não tem condições de assumir o valor NULL, e, assim, obrigatoriamente devem ter um valor expresso num comando de inserção. Como um atributo chave deve ser unívoco, a inserção de um registro com um campo chave que já exista também irá gerar um erro de inclusão. A maioria dos SGBD’s existentes no mercado implementa o tipo de dado autonumeração para codificar os registros automaticamente a cada inserção. Assim, num comando INSERT, não é necessário incluir tal campo especificando um valor, pois isso é feito automaticamente, eximindo o controle da codificação por parte do aplicativo. O comando INSERT permite também a inclusão em lote a partir de uma consulta SELECT. Vamos supor que queiramos incluir os fornecedores localizados no estado do Paraná como clientes. Nessa situação, fazemos essas inclusões aninhando os comandos: SQL (71): INSERT INTO clientes(codigo, nome) SELECT codigo, nome FROM fornecedores WHERE UF=’PR’

[alteração de registros] Para alterar um valor em um campo específico de uma ou mais tabelas, utilizamos o comando UPDATE. A sintaxe genérica é: UPDATE SET =, =,..., = WHERE

149


Onde na cláusula SET o campo respectivo de “” assume um novo valor caso a condição na cláusula WHERE seja verdadeira. Por exemplo, para alterar o valor do pedido 1023 para R$1.000,00, o comando é: SQL (72): UPDATE pedido SET valor = 1.000,00 WHERE codigo = 1023

Após a execução do comando, o valor atual de R$900,00 será sobreposto pelo novo valor de R$1.000.00. A cláusula WHERE é opcional, porém na maioria dos casos é necessário utilizá-la. Caso a atualização deva ser feita para todos os registros, basta utilizar o comando UPDATE sem a cláusula WHERE, e o comando seguinte zera todos os valores da tabela “Pedido” por exemplo: SQL (73): UPDATE pedido SET valor = 0,00

O uso de condições utilizando as consultas aninhadas SELECT também é possível. Desse modo, caso queiramos zerar o limite de compra de clientes que tenham ultrapassado a mesma, podemos utilizar o seguinte comando SQL: SQL (74): UPDATE clientes SET limite_compra = 0 WHERE EXISTS (SELECT * FROM pedido WHERE pedido.valor > clientes.limite_compra AND pedido.codcli = clientes.codigo)

Ou seja, o limite de compra é igual a zero para aqueles clientes nos quais o valor de um pedido tenha extrapolado o limite de compra. Como resultado, o cliente 282 tem o limite de compra zerado e a tabela 150

banco_de_dados_

“Clientes” fica com a seguinte apresentação: CLIENTES codigo

nome

telefone

202

Ernesto

3222-0809


221

Amélia

3233-2474

2.000,00

233

Luís Alberto

3323-0071

1.500,00

282

José Antonio

3343-9021

0,00

295

Carlos Silva

3224-5678

800,00

Podemos utilizar mais de uma tabela para fazer uma atualização. Por exemplo, o comando que apresentamos na seqüência irá atualizar os valores dos pedidos a partir dos respectivos subtotais existentes nos itens da tabela “Pedido”. Desde que o valor do pedido seja zerado com antecedência, o seguinte comando pode ser fornecido logo após: SQL (75): UPDATE pedido, itens SET pedido.valor = pedido.valor + itens.subtotal WHERE pedido.codigo = itens.codped

Note que são utilizadas as duas tabelas “Pedido” e “Itens”, tal como num produto cartesiano em uma consulta SQL. Nesse caso, o valor do pedido é incrementado dos valores dos subtotais respeitando que o código do pedido seja igual ao campo codped da tabela “Itens”.

[exclusão de registros] A sintaxe SQL para exclusão de registros é a seguinte: DELETE FROM WHERE

Como exemplo, para excluir o registro da tabela “Pedido” de código 1028, fazemos: 151


SQL (76): DELETE FROM pedido WHERE codigo = 1028

A cláusula WHERE, assim como no comando UPDATE, é opcional. No entanto, quando não utilizada, exclui todos os registros de uma tabela. Assim, o comando: SQL (77): DELETE FROM pedido

Apaga todos os registros existentes na tabela. Trata-se, portanto, de um comando que não deixa dúvidas quanto ao cuidado necessário na sua utilização.

[resumo] Foi visto neste capítulo como fazer modificações em tabelas dentro de bancos de dados. Observamos que os comandos INSERT e DELETE têm atuação sobre registros no banco de dados, enquanto que o comando UPDATE atua sobre um item de registro. Vimos, também, que as modificações podem conflitar com as restrições adotadas para que uma determinada tabela mantenha a sua integridade. Assim, para estendermos a nossa compreensão do assunto, veremos na seqüência a criação de uma tabela na qual serão definidos, além dos campos e seus tipos, as restrições a serem respeitadas no caso de modificações em banco de dados.

[exercícios] 1. Com base nas tabelas a seguir, expresse os comandos de inserção, modificação ou exclusão na linguagem SQL. 152

banco_de_dados_

PEDIDO codigo

codcli

data

0001

100

03/10/2003

valor 133,00

0002

102

04/10/2003

45,00

0003

105

05/11/2003

339,50

0004

110

10/11/2003

30,00

0005

100

12/11/2003

152,50

CLIENTE codigo

nome

telefone

cidade

UF

100

Luís Paulo

3355-1027

Curitiba

PR

102

José Antonio

3452-3528

Lapa

PR

105

Carlos Lima

3233-3456

Joinville

SC

110

Maria de Castro

3441-8930

Ponta Grossa

PR

114

Danilo Silva

3353-4020

Curitiba

PR

ITENS codped

coditem

qtdped

0001

2010

3

subtotal

0001

2020

10

25,00

0002

3004

30

45,00

0003

4011

2

50,00

0003

4013

5

137,50

0003

4025

4

152,00

0004

3004

20

0005

3004

10

0005

4013

5

108,00

30,00 15,00 137,50

FORNECEDOR codigo

nome

telefone

cidade

UF

501

ABC Cirúrgica

3772-4001

São Paulo

SP

502

Thermo

3873-5030

Salvador

BA

535

Distrib. Silva

3444-5523

Joinville

SC

550

CLS

3352-2353

Curitiba

PR

590

AKL Equip.

3330-8252

Porto Alegre

RS

153


ESTOQUE codigo

descrição

un

qtd

estmin

ptoped

codfor

2000

Termômetro

un

preço 12,00

36

5

15

501

2010

Termômetro Digital

un

36,00

12

5

15

501

2020


Pct c/10

2,50

40

10

50

535

3004

Esparadrapo

Rl c/10m

1,50

130

20

100

535

4011


Cx. c/ 100 un

25,00

43

15

60

550

4013


Cx. c/ 100 un

27,50

43

15

60

550

4025


Cx. c/ 100 un

38,00

43

15

60

550

5001

Ap.Pressão

un

6

1

5

590

205,00

a. Inclua o cliente João Carlos, que mora em Fortaleza-CE, com telefone 3344-0909, no código 115. b. Inclua a cliente Ana Flávia, com telefone 2454-8008. c. Altere o telefone do cliente Carlos Lima para 3233-3457. d. Altere a cidade, UF e telefone de Maria de Castro para Londrina, PR e 3455-3321, respectivamente. e. Exclua o cliente José Antônio. f. Acrescente o item “Aparelho de Pressão” ao pedido 0005. g. Exclua o item 4025 do pedido 0003. 154

banco_de_dados_

h. Altere o preço do item 2010 para R$105,00 (apenas no pedido). i. Altere a quantidade do item 4011 do pedido 0003 para 3. j. Zere todos os valores de ponto de pedido da tabela “Estoque”. k. Exclua os itens de pedido abaixo de R$100,00. l.

Exclua

todos

os

registros

da

tabela

“Fornecedor”.

155


referências por_capítulo_

Capítulo 1 1 2 3 4 5 6 7 8 9 10 11 12 13 14

DATE, 2000. GRASSMANN; TREMBLAY, 1996, p. 593. TURBAN; RAINER JUNIOR; POTTER, 2005, p. 523. LAUDON; LAUDON, 2004, p. 227. O’BRIEN, 2004, p. 136. MEYER, 1999. CODD, 1970, p. 377-387. MEYER, op. cit., 1999. ELMASRI; NAVATHE, 2005. DATE, 2000. REZENDE, 2003, p. 65. TURBAN; RAINER JUNIOR; POTTER, 2005, p. 100. INMON, 2001, p. 10-11. O’BRIEN, 2004, p. 143.

Capítulo 2 1 2 3 4 5 6 7 8

CHEN, 1976, p. 9-37. EARP; BAGUI, 2003. HEUSER, 2004. DATE, 2000. HEUSER, op. cit., 2004. MARTINS; LAUGENI, 2001. KORTH; SILBERSCHATZ, 1999. HEUSER, 2004.

Capítulo 3 1 2

GRASSMANN; TREMBLAY, 1996, p. 605. KORTH; SILBERSCHATZ, 1999, p. 76.

Capítulo 4 1 2 3 4

ULLMAN, 1997. KORTH; SILBERSCHATZ, 1999. DATE, 2000. GRASSMANN; TREMBLAY, 1996, p. 613.

referências_

CHEN, P. P. The entity relationship model: toward a unified view of data. ACM Transactions on Database Systems, n. 1, p. 9-37, mar. 1976. CODD, E. F. A relational model of data for large share data banks. Communications of the ACM, v. 13-16, p. 377-387, 1970. DATE, C. J. Introdução a sistemas de bancos de dados. 7. ed. Rio de Janeiro: Campus, 2000. EARP, R.; BAGUI, S. Database design using entity-relationship diagrams. Washington: Auerbach Publications, 2003. ELMASRI, R.; NAVATHE, S. B. Sistemas de banco de dados. 4. ed. São Paulo: Pearson, 2005. GRASSMANN, W. K.; TREMBLAY, J. P. Logic and discrete mathematics: a computer science perspective. New York: Prentice-Hall, 1996. p. 593. HEUSER, C. A. Projeto de banco de dados. 5. ed. Porto Alegre: Sagra Luzzato, 2004. INMON, W. H. et al. Data warehousing: como transformar informações em oportunidades de negócios. São Paulo: Berkeley, 2001. p. 10-11. KORTH, H. F.; SILBERSCHATZ, A. Sistemas de banco de dados. 3. ed. São Paulo: Makron Books, 1999. LAUDON, K.; LAUDON, J. Sistemas de informação gerenciais. 5. ed. São Paulo: Prentice-Hall, 2004. p. 227. MARTINS, P. G.; LAUGENI, F. P. Administração da produção. São Paulo: Saraiva, 2001. MEYER, M. et al. Nosso futuro e o computador. 3. ed. Porto Alegre: Makron Books, 1999.

O’BRIEN, J. A. Sistemas de informação e as decisões gerenciais na era da internet. São Paulo: Saraiva, 2004. p. 143. REZENDE, D. Planejamento de sistemas de informação e informática. São Paulo: Atlas, 2003. p. 65. TURBAN, E.; RAINER JUNIOR, R. K.; POTTER, R. E. Administração de tecnologia da informação. Rio de Janeiro: Campus, 2005. p. 523. ULLMAN, J. D. A first course in database systems. Upper Sadler River: Prentice Hall, 1997.

160

banco_de_dados_

apêndices_

[apêndice a] Modelo (exemplo) A seguir, encontram-se as tabelas do modelo (exemplo) utilizado ao longo da explanação sobre linguagens de consulta, com seus respectivos registros. CLIENTES codigo

nome

telefone

202

Ernesto

3222-0809


221

Amélia

3233-2474

2.000,00

233

Luís Alberto

3323-0071

1.500,00

282

José Antonio

3343-9021

800,00

295

Carlos Silva

3224-5678

800,00

codigo

codcli

data

1005

202

10/03/2004

1.200,00

1008

221

13/03/2004

960,00

1010

233

17/03/2004

1.020,00

1015

282

20/03/2004

755,00

1023

202

21/03/2004

900,00

codigo

codped

qtd

001

1005

10

1.200,00

002

1008

12

720,00

003

1008

2

240,00

004

1010

1

500,00

005

1010

2

335,00

006

1010

1

185,00

007

1015

1

125,00

008

1015

1

630,00

009

1023

1

400,00

010

1023

1

300,00

011

1023

1

200,00

PEDIDO valor

ITENS subtotal

FORNECEDOR codigo

nome

telefone

ultent

UF

130

Casa das Ferramentas

3345-2238

20/02/04

RS

141

LG Máquinas

3255-9090

31/01/04

SP

152

SuperMax Maq e Ferr

3414-3990

14/02/04

PR

163

Lux Materiais de Construção

3334-0808

01/03/04

SP

184

Viamar Equipamentos

3267-3377

05/03/04

SC

custo

prazoent

ESTOQUE codigo

descricao

2001

Grade de Aço

2044

Bomba Injetora

2050

Multímetro

2080

Acoplador

3020

qtd

ptoped

estmin

150

70

30

80,00

15

5

2

1

320,00

30

20

5

2

35,00

10

4

6

2

280,00

30

Quadro Padrão

3

8

4

140,00

20

3033

Esmeril

5

3

2

110,00

10

3050

Furadeira

9

5

3

85,00

10

4101

Serra Tico-Tico

6

4

2

75,00

10

ITEMPROD codprod

coditem

preco

desconto

2001

1

120,00

0,00

2050

2

60,00

0,00

2001

3

120,00

0,00

2044

4

500,00

0,00

3033

5

180,00

25,00

3050

6

130,00

5,00

4101

7

125,00

0,00

3020

8

300,00

0,00

2044

8

500,00

170,00

2080

9

420,00

20,00

3020

10

300,00

0,00

4101

11

125,00

25,00

164

banco_de_dados_

PRODFOR codprod

codfor

2001

130

2044

130

2050

141

2050

152

2050

184

2080

152

2001

152

2044

152

3020

163

3020

152

3033

184

3050

184

4101

163

4101

184

3033

152

3050

152

4101

152

165


[apêndice b] Lista de consultas em SQL A seguir estão resumidas as consultas SQL tratadas nos capítulos 4 e 5. Cada consulta possui uma numeração, um enunciado e o comando SQL propriamente dito. SQL (1): “Sintaxe geral”. SELECT FROM WHERE

SQL (2): “Selecione o pedido cujo código seja 1010”. SELECT * FROM pedido WHERE valor = 1010

SQL (3): “Encontre os pedidos cujo valor seja maior que R$1.000,00”. SELECT * FROM pedido WHERE valor > 1.000,00

SQL (4): “Encontre os pedidos cujo valor seja maior que R$1.000,00 e a data seja anterior (menor) a 15/03/2004”. SELECT * FROM pedido WHERE(valor > 1.000,00) AND(data < DATE’15/03/2004’)

SQL (5): “Selecione para todos os atributos o conteúdo da tabela ‘pedido’ ”. SELECT * FROM pedido

166

banco_de_dados_

SQL (6): “Selecione apenas o código e o valor de todos os pedidos”. SELECT codigo, valor FROM pedido

SQL (7): “Selecione o código e o valor do pedido de código 1010”. SELECT codigo, valor FROM pedido WHERE codigo = 1010

SQL (8): “Encontre todos os atributos para os registros dos pedidos e dos seus respectivos clientes”. SELECT * FROM pedido INNER JOIN clientes ON pedido.codcli = clientes.codigo

SQL (9): “Encontre o nome e o telefone do cliente cujo pedido tem o código 1010”. SELECT nome, telefone FROM pedido INNER JOIN clientes ON pedido.codcli = clientes.codigo WHERE pedido.codigo = 1010

SQL (10): “Encontre o nome e o telefone do cliente cujo pedido tem o código 1010 (com o uso de parênteses)”. SELECT nome, telefone FROM(pedido INNER JOIN clientes ON pedido.codcli = clientes.codigo) WHERE pedido.codigo = 1010

SQL (11): “Encontre os valores dos atributos dos itens referentes ao cliente de nome Luís Alberto”. SELECT itens.codigo, itens.codped, itens.qtd, itens.subtotal FROM itens INNER JOIN(pedido INNER JOIN clientes ON pedido.codcli = clientes.codigo) ON pedido.codigo = itens.codped WHERE clientes.nome = ’Luís Alberto’

167


SQL (12): “Encontre os valores dos atributos dos itens referentes ao cliente de nome Luís Alberto (similar à anterior)”. SELECT itens.* FROM itens INNER JOIN(pedido INNER JOIN clientes ON pedido.codcli = clientes.codigo) ON pedido.codigo = itens.codped WHERE clientes.nome = ’Luís Alberto’

SQL (13): “Encontre os valores dos atributos dos itens referentes ao cliente de nome Luís Alberto (comutativa da anterior)”. SELECT itens.* FROM(pedido INNER JOIN clientes ON pedido.codcli = clientes. codigo) INNER JOIN itens ON pedido.codigo = itens.codped WHERE clientes.nome = ’Luís Alberto’

SQL (14): “Selecione o nome do cliente cujo pedido tenha o código 1010 (junção)”. SELECT clientes.nome FROM pedido INNER JOIN clientes ON pedido.codcli = clientes.codigo WHERE pedido.codigo = 1010

SQL (15): “Selecione o nome do cliente cujo pedido tenha o código 1010 (produto cartesiano)”. SELECT clientes.nome FROM pedido, clientes WHERE pedido.codcli = clientes.codigo AND pedido.codigo = 1010

SQL (16): “Encontre os valores dos atributos dos itens referentes ao cliente de nome Luís Alberto (produto cartesiano)”. SELECT itens.* FROM itens, pedido, WHERE pedido.codcli AND pedido.codigo = AND clientes.nome =

168

banco_de_dados_

clientes = clientes.codigo itens.codped ’Luís Alberto’

SQL (17): “Encontre todos os nomes de clientes que comecem com Carlos”. SELECT nome FROM clientes WHERE nome LIKE “Carlos%”

SQL (18): “Encontre todos os nomes de clientes que terminem com Silva”. SELECT nome FROM clientes WHERE nome LIKE “%Silva”

SQL (19): “Encontre todos os nomes de clientes que comecem ou terminem com Silva”. SELECT nome FROM clientes WHERE nome LIKE “%Silva%”

SQL (20): “Encontre todos os nomes de clientes que tenham cinco letras e comecem com a letra L”. SELECT nome FROM clientes WHERE nome LIKE “L_ _ _ _“

SQL (21): “Selecione os nomes de todos os clientes ordenados pelo nome”. SELECT nome FROM clientes ORDER BY nome

SQL (22): “Selecione os nomes de todos os clientes ordenados por nome e telefone”. SELECT nome FROM clientes ORDER BY nome, telefone

169


SQL (23): “Selecione os nomes dos clientes que comecem com A ordenados por nome e telefone”. SELECT nome FROM clientes WHERE nome LIKE “A%” ORDER BY nome, telefone

SQL (24): “Selecione os nomes dos clientes que comecem com A ordenados pelo segundo e pelo terceiro atributo”. SELECT nome FROM clientes WHERE nome LIKE “A%” ORDER BY 2,3

SQL (25): “Selecione os nomes de todos os clientes ordenados pelo nome na ordem inversa”. SELECT nome FROM clientes ORDER BY nome DESC

SQL (26): “Encontre o nome do cliente que tenha o pedido de código 1010 (consulta aninhada)”. SELECT clientes.nome FROM pedido, clientes WHERE pedido.codcli = clientes.codigo AND pedido.codigo = 1010

SQL (27): “Encontre todos os atributos do cliente que tenha o pedido de código 1010 (consulta aninhada)”. SELECT clientes.nome FROM clientes WHERE clientes.codigo = (SELECT pedido.codcli FROM pedido WHERE pedido.codigo = 1010)

170

banco_de_dados_

SQL (28): “Encontre todos os atributos do cliente que tenha o pedido de código 1010 (consulta aninhada)”. SELECT * FROM clientes WHERE clientes.codigo = (SELECT pedido.codcli FROM pedido WHERE pedido.codigo = 1010)

SQL (29): “Encontre os nomes de clientes com valor de pedido acima de R$1.000,00 (cláusula IN)”. SELECT clientes.nome FROM clientes WHERE clientes.codigo IN (SELECT pedido.codcli FROM pedido WHERE pedido.valor > 1.000,00)

SQL (30): “Mostre os nomes de clientes se existir algum pedido cujo valor seja maior que R$1.000,00”. SELECT clientes.nome FROM clientes WHERE EXISTS (SELECT pedido.codcli FROM pedido WHERE pedido.valor > 1.000,00)

SQL (31): “Encontre os nomes de clientes que não tenham valor de pedido maior que R$ 1.000,00”. SELECT clientes.nome FROM clientes WHERE clientes.codigo NOT IN (SELECT pedido.codcli FROM pedido WHERE pedido.valor > 1.000,00)

SQL (32): “Mostre os nomes de clientes caso não exista algum pedido de valor maior que R$1.000,00”. SELECT clientes.nome FROM clientes WHERE NOT EXISTS (SELECT pedido.codcli FROM pedido WHERE pedido.valor > 1.000,00)

171


SQL (33): “Encontre o código e o valor do pedido que possua o maior valor de subtotal de um item”. SELECT pedido.codigo, pedido.valor FROM pedido WHERE pedido.valor >= ALL (SELECT itens.subtotal FROM itens)

SQL (34): “Encontre o código e o valor do pedido para aqueles que possuam itens cujo subtotal seja maior do que 50% do valor do pedido”. SELECT pedido.codigo, pedido.valor FROM pedido WHERE pedido.valor >= ANY (SELECT itens.subtotal FROM itens WHERE itens.subtotal > pedido.valor*0.5 AND itens.codped = pedido.codigo)

SQL (35): “Encontre os códigos de pedido, a partir da tabela “Itens”, sem repetição de código”. SELECT DISTINCT codped FROM itens

SQL (36): “Encontre todos os códigos e os preços de computadores e notebooks”. SELECT codigo, preco FROM computador UNION SELECT codigo, preco FROM notebook

SQL (37): “Encontre todos os códigos e preços de computadores e notebooks sem suprimir os códigos e preços repetidos”. SELECT codigo, preco FROM computador UNION ALL SELECT codigo, preco FROM notebook

172

banco_de_dados_

SQL (38): “Encontre os clientes que não tenham efetuado pedidos (diferença com EXCEPT)”. SELECT codigo FROM clientes EXCEPT SELECT codcli FROM pedido

SQL (39): “Encontre os clientes que não tenham efetuado pedidos (diferença com NOT EXISTS)”. SELECT clientes.codigo FROM clientes WHERE NOT EXISTS (SELECT pedido.codcli FROM pedido WHERE pedido.codcli = clientes.codigo)

SQL (40): “Encontre os clientes que efetuaram pedidos (interseção com INTERSECT)”. SELECT codigo FROM clientes INTERSECT SELECT codcli FROM pedido

SQL (41): “Encontre os clientes que efetuaram pedidos (interseção com EXISTS)”. SELECT clientes.codigo FROM clientes WHERE EXISTS (SELECT pedido.codcli FROM pedido WHERE pedido.codcli = clientes.codigo)

SQL (42): “Encontre o cliente que efetuou o pedido de código 1010 (renomeação de tabelas)”. SELECT cliente.nome FROM pedido AS p, clientes AS c WHERE p.codcli = c.codigo AND p.codigo = 1010

173


SQL (43): “Encontre o código dos clientes que efetuaram mais de um pedido”. SELECT p.codcli FROM pedido AS p WHERE p.valor < ANY (SELECT pedido.valor FROM pedido WHERE pedido.codcli = p.codcli)

SQL (44): “Encontre o pedido de maior valor”. SELECT pedido.valor FROM pedido WHERE pedido.valor NOT IN (SELECT p1.valor FROM pedido AS p1, pedido AS p2 WHERE pedido.codigo = p1.codigo) AND p1.valor < p2.valor)

SQL (45): “Mostre o código do cliente e o valor do pedido cujo código seja 1010 (formatado versão 1)”. SELECT codcli AS codigo_do_cliente, valor AS total_do_pedido FROM pedido WHERE codigo = 1010

SQL (46): “Mostre o código do cliente e o valor do pedido cujo código seja 1010 (formatado versão 2)”. SELECT ’codigo’ AS titulo, codcli AS codigo_do_cliente, valor AS total_do_pedido FROM pedido WHERE codigo = 1010

SQL (47): “Mostre o código do cliente e o valor do pedido cujo código seja 1010 (formatado versão 3)”. SELECT ’codigo’ AS titulo, codcli AS codigo_do_cliente, valor*2.90 AS total_do_pedido FROM pedido WHERE codigo = 1010

SQL (48): “Encontre a soma dos valores dos pedidos”. SELECT SUM(valor) FROM pedido

174

banco_de_dados_

SQL (49): “Encontre o valor médio dos pedidos cujo valor seja maior que R$1.000,00”. SELECT AVG(valor) AS media FROM pedido WHERE valor > 1.000,00

SQL (50): “Encontre o valor mínimo dos pedidos cuja data seja anterior (menor) a 15/03/2004”. SELECT MIN(valor) AS minimo FROM pedido WHERE data < DATE’15/03/2004’

SQL (51): “Encontre o valor máximo dos pedidos cuja data esteja entre 15/03/2004 e 30/03/2004, exceto estas datas”. SELECT MAX(valor) AS maximo FROM pedido WHERE data > DATE’15/03/2004’ AND data < DATE’30/03/2004’

SQL (52): “Encontre a quantidade de clientes (versão 1)”. SELECT COUNT(codigo) AS total FROM clientes

SQL (53): “Encontre a quantidade de clientes (versão 2)”. SELECT COUNT(*) AS total FROM clientes

SQL (54): “Encontre a soma dos subtotais dos itens para cada código de pedido”. SELECT codped, SUM(subtotal) FROM itens GROUP BY codped

SQL (55): “Encontre a soma dos subtotais dos itens para cada código de pedido, isso apenas para pedidos que tenham dois itens”. SELECT codped, SUM(subtotal) FROM itens GROUP BY codped HAVING COUNT(codped) = 2

175


SQL (56): “Encontre a soma dos subtotais dos itens para cada código de pedido, isso apenas para os pedidos maiores que 1010”. SELECT codped, SUM(subtotal) FROM itens GROUP BY codped HAVING codped > 1010

SQL (57): “Encontre somente os clientes que não possuam número de telefone cadastrado”. SELECT nome FROM clientes WHERE telefone IS NULL

SQL (58): “Encontre somente os clientes que possuam número de telefone”. SELECT nome FROM clientes WHERE telefone IS NOT NULL

SQL (59): “Liste todos os atributos de clientes e seus respectivos pedidos incluindo os que não tenham efetuado pedidos ou pedido que não esteja ligado a algum cliente”. SELECT * FROM clientes FULL OUTER JOIN pedido ON clientes.codigo = pedido. codcli

SQL (60): “Liste todos os atributos de clientes e seus respectivos pedidos incluindo os que não tenham efetuado pedido”. SELECT * FROM clientes LEFT OUTER JOIN pedido ON clientes.codigo = pedido. codcli

SQL (61): “Liste todos os atributos de clientes e seus respectivos pedidos incluindo os pedidos que não estejam ligados a algum cliente”. SELECT * FROM clientes RIGHT OUTER JOIN pedido ON clientes.codigo = pedido.codcli

176

banco_de_dados_

SQL (62): “Encontre os clientes que não efetuaram pedidos”. SELECT * FROM clientes LEFT OUTER JOIN pedido ON clientes.codigo = pedido. codcli WHERE pedido.codigo IS NULL

SQL (63): “Encontre os fornecedores que fornecem todos os produtos (divisão)”. SELECT DISTINCT p1.codfor FROM prodfor AS P1 WHERE NOT EXISTS (SELECT e1.codigo FROM estoque AS E1 WHERE NOT EXISTS SELECT * FROM prodfor AS P2 WHERE p1.codfor = p2.codfor AND p2.codprod = e1.codigo))

SQL (64): “Encontre os fornecedores que não fornecem todos os produtos (resto)”. SELECT p3.codfor FROM prodfor AS p1 WHERE p3.codigo NOT IN (SELECT DISTINCT p1.codfor FROM prodfor AS p1 WHERE NOT EXISTS (SELECT e1.codigo FROM estoque AS e1 WHERE NOT EXISTS (SELECT * FROM prodfor AS p2 WHERE p1.codfor = p2.codfor AND p2.codprod = e1.codigo)))

SQL (65): “Calcule o lucro bruto (preço de venda dos itens menos o custo do produto) e a margem para todos os produtos vendidos”. SELECT SUM(itemprod.preco - estoque.custo) AS lucro, (AVG(itemprod.preco / estoque.custo)-1)*100 AS margem FROM itemprod LEFT OUTER JOIN estoque ON itemprod.codprod = estoque.codigo

177


SQL (66): “Calcule o lucro bruto (preço de venda dos itens menos o custo do produto) e a margem para cada produto vendido, ordenando do maior ao menor lucro”. SELECT itemprod.codprod, (itemprod.preco - estoque.custo) AS lucro,((itemprod.preco / estoque.custo)-1)*100 AS margem FROM itemprod LEFT OUTER JOIN estoque ON itemprod.codprod = estoque.codigo ORDER BY lucro DESC

SQL (67): “Calcule o lucro bruto (preço de venda dos itens menos o custo do produto) e a margem para cada produto vendido, ordenando da maior à menor margem”. SELECT itemprod.codprod, (itemprod.preco - estoque.custo) AS lucro,((itemprod.preco / estoque.custo)-1)*100 AS margem FROM itemprod LEFT OUTER JOIN estoque ON itemprod.codprod = estoque.codigo ORDER BY margem DESC

SQL (68): “Insira uma linha na tabela ‘Pedido’”. INSERT INTO pedido(codigo, codcli, data, valor) VALUES(1028, 202, DATE’01/04/2004’, 600,00)

SQL (69): “Insira uma linha na tabela ‘Pedido’ (versão simplificada)”. INSERT INTO pedido VALUES(1028, 202, DATE’01/04/2004’, 600,00)

SQL (70): “Insira um registro contendo apenas o código do pedido e o código do cliente”. INSERT INTO pedido(codigo, codcli) VALUES(1028, 202)

SQL (71): “Insira registros da tabela de fornecedores na tabela ‘Clientes’ (código e nome) para fornecedores localizados no Estado do Paraná”. INSERT INTO clientes(codigo, nome) SELECT codigo, nome FROM fornecedores WHERE UF = ’PR’

178

banco_de_dados_

SQL (72): “Altere o valor do pedido 1023 para R$1.000,00”. UPDATE pedido SET valor = 1.000,00 WHERE codigo = 1023

SQL (73): “Zere todos os valores de pedido”. UPDATE pedido SET valor = 0.00

SQL (74): “Zere o limite de compra de clientes que tenham em algum pedido ultrapassado o mesmo”. UPDATE clientes SET limite_compra = 0 WHERE EXISTS (SELECT * FROM pedido WHERE pedido.valor > clientes.limite_compra AND pedido.codcli = clientes.codigo)

SQL (75): “Totalize os valores dos pedidos a partir dos subtotais existentes nos respectivos itens de pedido (desde que o campo valor esteja zerado)”. UPDATE pedido, itens SET pedido.valor = pedido.valor + itens.subtotal WHERE pedido.codigo = itens.codped

SQL (76): “Exclua os registros da tabela ‘Pedido’ que contenham o código 1028”. DELETE FROM pedido WHERE codigo = 1028

SQL (77): “Exclua todos os registros da tabela ‘Pedido’”. DELETE FROM pedido

179


anexo_

[tabela ascii*] Caracter

Decimal

Hexadecimal

Binário

Comentário

NUL

00

00

0000 0000

Caracter nulo

SOH

01

01

0000 0001

Começo de cabeçalho de transmissão

STX

02

02

0000 0010

Começo de texto

ETX

03

03

0000 0011

Fim de texto

EOT

04

04

0000 0100

Fim de transmissão

ENQ

05

05

0000 0101

Interroga

ACK

06

06

0000 0110

Confirmação

BEL

07

07

0000 0111

Sinal sonoro

BS

08

08

0000 0100

Volta um caracter

HT

09

09

0000 1001

Tabulação horizontal

LF

10

0A

0000 1010

Próxima linha

VT

11

0B

0000 1011

Tabulação vertical

FF

12

0C

0000 1100

Próxima página

CR

13

0D

0000 1101

Início da linha

SO

14

0E

0000 1110

Shift-out

SI

15

0F

0000 1111

Shift-in

DLE

16

10

0001 0000

Data link escape

D1

17

11

0001 0001

Controle de dispositivo

D2

18

12

0001 0010

Controle de dispositivo (continua)

* CRUZ, Adriano Joaquim de Oliveir. Apêndice 1 – TABELA ASCII. Disponível em: . Acesso em: out. 2006.

182

banco_de_dados_

Caracter

Decimal

Hexadecimal

Binário

Comentário

D3

19

13

0001 0011


D4

20

14

0001 0100


NAK

21

15

0001 0101

Negativa de confirmação

SYN

22

16

0001 0110

Synchronous idle

ETB

23

17

0001 0111

Fim de transmissão de bloco

CAN

24

18

0001 1000

Cancela

EM

25

19

0001 1001

Fim de meio de transmissão

SUB

26

1A

0001 1010

Substitui

ESC

27

1B

0001 1011

Escape

FS

28

1C

0001 1100

Separador de arquivo

GS

29

1D

0001 1101

Separador de grupo

RS

30

1E

0001 1110

Separador de registro

US

31

1F

0001 1111

Separador de Unidade

Espaço

32

20

0010 0000

!

33

21

0010 0001

“

34

22

0010 0010

#

35

23

0010 0011

$

36

24

0010 0100

%

37

25

0010 0101

&

38

26

0010 0110

‘

39

27

0010 0111

(

40

28

0010 1000

)

41

29

0010 1001

*

42

2A

0010 1010

+

43

2B

0010 1011

,

44

2C

0010 1100 (continua)

183


Caracter

Decimal

Hexadecimal

Binário

-

45

2D

0010 1101

.

46

2E

0010 1110

/

47

2F

0010 FFFF

0

48

30

0011 0000

1

49

31

0011 0001

2

50

32

0011 0010

3

51

33

0011 0011

4

52

34

0011 0100

5

53

35

0011 0101

6

54

36

0011 0110

7

55

37

0011 0111

8

56

38

0011 1000

9

57

39

0011 1001

:

58

3A

0011 1010

;

59

3B

0011 1011

<

60

3C

0011 1100

=

61

3D

0011 1101

>

62

3E

0011 1110

?

63

3F

0011 1111

@

64

40

0100 0000

A

65

41

0100 0001

B

66

42

0100 0010

C

67

43

0100 0011

D

68

44

0100 0100

E

69

45

0100 0101

F

70

46

0100 0110

G

71

47

0100 0111

H

72

48

0100 1000

I

73

49

0100 1001

J

74

4A

0100 1010

K

75

4B

0100 1011

L

76

4C

0100 1100

M

77

4D

0100 1101

N

78

4E

0100 1110

Comentário

(continua)

184

banco_de_dados_

Caracter

Decimal

Hexadecimal

Binário

O

79

4F

0100 1111

P

80

50

0101 0000

Q

81

51

0101 0001

R

82

52

0101 0010

S

83

53

0101 0011

T

84

54

0101 0100

U

85

55

0101 0101

V

86

56

0101 0110

W

87

57

0101 0111

X

88

58

0101 1000

Y

89

59

0101 1001

Z

90

5A

0101 1010

[

91

5B

0101 1011

\

92

5C

0101 1100

]

93

5D

0101 1101

^

94

5E

0101 1110

_

95

5F

0101 1111

`

96

60

0110 0000

a

97

61

0110 0001

b

98

62

0110 0010

c

99

63

0110 0011

d

100

64

0110 0100

e

101

65

0110 0101

f

102

66

0110 0110

g

103

67

0110 0111

h

104

68

0110 1000

i

105

69

0110 1001

j

106

6A

0110 1010

k

107

6B

0110 1011

l

108

6C

0110 1100

m

109

6D

0110 1101

n

110

6E

0110 1110

o

111

6F

0110 1111

p

112

70

0111 0000

Comentário

(continua)


185

(conclusão)

Caracter

Decimal

Hexadecimal

Binário

q

113

71

0111 0001

r

114

72

0111 0010

s

115

73

0111 0011

t

116

74

0111 0100

u

117

75

0111 0101

v

118

76

0111 0110

w

119

77

0111 0111

x

120

78

0111 1000

y

121

79

0111 1001

z

122

7A

0111 1010

{

123

7B

0111 1011

|

124

7C

0111 1100

}

125

7D

0111 1101

~

126

7E

0111 1110

DELETE

127

7F

0111 1111

Comentário

exit_

Este livro foi impresso no inverno de 2007, na Fotolaser Gráfica, sobre papel offset 75g/m².

Banco de Dados - Princípios e Prática

Recommend Documents