Bancos de Dados

Multimidia

Ticianne Darin

Álgebra Relacional Básica

Álgebra Relacional

• Álgebra relacional é uma linguagem de consulta téorica

– usuários não a usam diretamente. É muito complicada para ser usada diretamente.

• Porém, a álgebra relacional é usada internamente em todos os SGBDs

– outras linguagens de consulta são traduzidas em álgebra relacional.

Operações da Álgebra Relacional

• Operações derivadas da teoria dos conjuntos

– União

– Interseção

– Diferença

– Produto Cartesiano

• Operações relacionais especiais

– Seleção

– Projeção

– Junção (natural)

Operações da Álgebra Relacional

R U S: Obtém a união das tuplas em R e S. Ilegal se R e S têm números diferentes de atributos.

R

S

A B

a1 b1

a2 b2

S A B

a2 b2

a3 b3

R

R S A B

a1 b1

a2 b2

a3 b3

União:

Operações da Álgebra Relacional

Diferença

R-S: obtém as tuplas em R que não estão em S. Ilegal se R e S têm números diferentes de atributos

R - S A B

a1 b1

R

S

S

A B

a1 b1

a2 b2

S A B

a2 b2

a3 b3

R

Operações da Álgebra Relacional

Interseção

R S: Obtém a interseção das tuplas de R e S. Ilegal se R e S têm números diferentes de atributos.

R S A B

a2 b2

R

S

A B

a1 b1

a2 b2

S A B

a2 b2

a3 b3

R

Operações da Álgebra Relacional

Seleção:

Seleciona todas as tuplas que satisfazem à condição de seleção de uma relação R.

R [A = ‘a1’] A B

a1 b1

R [expressão de seleção]

A B

a1 b1

a2 b2

R R

Operações da Álgebra Relacional

Projeção:

Produz uma nova relação com apenas alguns atributos de R, removendo tuplas duplicadas.

R [B]

R

B

b2

b1

R [A1, ... , An]

A B

a1 b1

a2 b2

R

Operações da Álgebra Relacional

Produto Cartesiano: A relação resultante consiste de todas as possíveis

tuplas resultantes da combinação de duas tuplas, uma de cada relação envolvida na operação.

A B

a1 b1

a2 b2

S C D

c2 d2

c3 d3

R R x S A B

a1 b1

a1 b1

C D

c2 d2

c3 d3

a2 b2 c2 d2

a2 b2 c3 d3

Operações da Álgebra Relacional

Junção:

A junção de R com S = (R x S) [ expressão de seleção]

R x S [ B = C]

A B

a1 b1

a2 b2

C D

b1 d3

b2 d2

A B

a1 b1

a2 b2

S C D

b2 d2

b1 d3

R

Operações da Álgebra Relacional

Junção Natural:

Quando a condição de junção for a igualdade do valor de um atributo comum e o atributo comum aparecer só uma vez no resultado.

R * S

A B

a1 b1

a2 b2

D

d3

d2

A B

a1 b1

a2 b2

S C D

b2 d2

b1 d3

R

Consultas em SQL

Estrutura Básica de consultas SQL

• SQL é baseada em operações de conjuntos e de álgebra relacional com algumas modificações e extensões

• Uma consulta SQL básica tem a forma:

– Ai’s representam atributos

– Ri’s representam relações

– P é um predicado.

• O resultado de uma consulta SQL é uma relação.

Select A1, A2, ..., Na

From r1, r2, ..., rm

Where P

A cláusula SELECT

• A cláusula select corresponde à operação de projeção

• É utilizada para listar os atributos pretendidos no resultado da consulta.

• NOTA:

– O SQL não permite o caracter ‘-’ nos nomes, portanto deverá utilizar, por exemplo, nome_cliente em vez de nome-cliente num sistema existente.

• NOTE:

– As maiúsculas e minúsculas não são distinguidas em nomes da linguagem SQL.

A cláusula SELECT

• Exemplos

– Listar os nomes de todos os clientes

– Um asterisco na cláusula select denota “todos os atributos”

Select nome

From Cliente

Select *

From Cliente

A cláusula SELECT

• O SQL permite duplicações nas relações e nos resultados de consultas.

• Para forçar a eliminação de duplicações, inserir a palavra-chave distinct após select.

– Apresentar os endereços de todos clientes, sem repetições

• A palavra-chave all indica que os duplicados não devem ser removidos.

Select distinct endereço

From Cliente

Select all endereço

From Cliente

A cláusula SELECT

• A cláusula select pode conter expressões aritméticas envolvendo as operações, +, –, *, e /, com argumentos constantes ou atributos

• Dependendo das implementações, encontram-se normalmente definidas uma biblioteca de funções.

• A consulta:

• Devolve uma relação idêntica à relação Produto, exceto que o atributo taxa é multiplicado por 100.

Select procodigo, preço, taxa*100

From Produto

A cláusula FROM

• A cláusula from corresponde à operação de produto cartesiano

• Indica as relações a consultar na avaliação da expressão.

– Encontrar o produto cartesiano Cliente x Pedido

Select *

From Cliente, Pedido

A cláusula WHERE

• A cláusula where corresponde ao predicado de seleção

• É formada por um predicado envolvendo atributos de relações que aparecem na cláusula from.

– Encontrar telefone dos clientes que se chamam ‘João da Silva’

Select telefone

From Cliente

Where nome=‘João da Silva’

A cláusula WHERE

• Os resultados de comparações podem ser combinados por intermédio dos conectivos lógicos and, or, e not.

• Podem-se aplicar comparações ao resultado de expressões aritméticas.

– Encontrar os códigos dos produtos do pedido número 203, cujas quantidades são superiores a dois itens

Select produto

From Itens

Where pedido=203 and quantidade>2

A cláusula WHERE

• Operador de comparação between para especificar condições em que um valor deve estar contido num intervalo de valores (incluindo os seus extremos).

• Para negar a condição pode-se colocar o conectivo not antes de between.

A cláusula WHERE

• Apresentar os códigos dos produtos cujos preços estão entre R$5,00 e R$30,00

• Para negar a condição pode-se colocar o conectivo not antes de between.

Select procodigo

From Produto

Where preço between 5 and 30

Select procodigo

From Produto

Where taxa not between 0,2 and 0,35

Operações com cadeias de

Caracteres

• SQL inclui um mecanismo de concordância de padrões para comparações envolvendo cadeias de caracteres.

• Os padrões são descritos recorrendo a dois caracteres especiais:

– percentagem(%): concorda com qualquer subcadeia.

– sublinhado (_): concorda com qualquer carácter.

• A SQL suporta uma variedade de operações com cadeias de caracteres, tais como:

– concatenação (utilizando “||”), conversão de maiúsculas para minúsculas (e vice versa), calcular o comprimento, extração de subcadeias, etc.

Operações com cadeias de Caracteres

• Listar todos os clientes cujo endereço inclua “Rua Nélio Rodrigues” .

• Encontrar uma cadeia de caracteres que de fato contenha o símbolo de porcentagem

– Ex.: 10%

Select nome

From Cliente

Where endereço like ‘% Rua Nélio Rodrigues %’

like ‘10\%’

A operação de renomeação AS

• A linguagem SQL permite a renomeação de relações e atributos recorrendo à cláusula as : old_name as new_name

• Listar os nome e códigos dos pedidos de cada cliente, renomeando a coluna pedcodigo para codPedido

Select c.nome, p.pedcodigo as codPedido

From Cliente as c, Pedido as p

Where c.ccodigo = p.pedcodigo

A operação de renomeação AS

• Caso se pretenda utilizar um nome com espaços, esse nome deverá ser colocado entre aspas.

Select c.nome, p.pedcodigo as “codigo do Pedido”

From Cliente as c, Pedido as p

Where c.ccodigo = p.pedcodigo

A operação de ordenação ORDER

BY

• Listar em ordem alfabética os nomes de todos os clientes que possuem algum pedido

• Pode-se especificar desc para ordenação descrescente ou

asc para ordenação ascendente, para cada atributo; por omissão, assume-se ordem ascendente.

• Pode-se ter mais do que uma chave de ordenação, separando-as com vírgulas

Select distinct nome

From Cliente as c, Pedido as p

Where c.ccodigo = p.pedcodigo

Order by nome;

Order by nome desc;

Funções de Agregação

• Estas funções aplicam-se a multiconjuntos de valores de uma coluna de uma relação, devolvendo um único valor como resultado

– avg: valor médio

– min: valor mínimo

– max: valor máximo

– sum: soma dos valores

– count: número de valores

Funções de Agregação

• Determinar o preço médio dos produtos comprados no dia 16/03/2008.

• Calcular a quantidade de clientes

• Encontrar o número de clientes com nomes diferentes

Select AVG(preço)

From Pedido as p, Itens as i, Produto as pr

Where p.data=’16/03/2008’ and

p.pedcodigo=i.produto and

i.produto=pr.procodigo

Select COUNT(ccodigo)

From Cliente

Select COUNT(distinct nome)

From Cliente

Funções de agregação – GROUP BY

• Listar o número de pedidos por cliente.

• Atenção:

– Atributos na cláusula select fora de funções de agregação têm de aparecer na lista group by

– Se aparecer mais do que um atributo em group by, então cada grupo é formado pelas tuplas com valores iguais em todos esses os atributos

Select c.nome, COUNT(p.pedcodigo)

From Cliente as c, Pedido as p

Where c.ccodifgo=p.cliente

Group By c.nome;

Funções de agregação – Having

• Listar o número de pedidos por cliente, para clientes que tiverem mais de 5 pedidos

• Nota:

– predicados na cláusula having são aplicados depois da formação dos grupos, enquanto que os predicados na cláusula where são aplicados antes da formação dos grupos.

Select c.nome, COUNT(p.pedcodigo) as numeroPedidos

From Cliente as c, Pedido as p

Where c.ccodifgo=p.cliente

Group By c.nome

Having numeroPedidos > 5

Operações com Conjuntos

• As operações com conjuntos union, intersect, e except (minus no Oracle9i) operam sobre relações e correspondem aos operadores

• Cada uma das operações anteriores elimina as duplicações automaticamente.

• Para reter duplicados deve-se utilizar as respectivas versões multiconjunto union all, intersect all e except all

• Suponha que uma tupla ocorre m vezes em R e n vezes em S, então ele ocorre:

– m + n vezes em R union all S

– min(m,n) vezes em R intersect all S

– max(0, m – n) vezes em R except all S

Operações com Conjuntos - Union

• Listar todos os clientes que realizaram pedidos com mais de 5 itens ou contendo algum produto de preço>R$30,00

(Select c.nome

From Cliente as c, Pedido as p, Produto as pr

Where c.ccodifgo=p.cliente and

p.pedcodigo=i.pedido and

i.quantidade>5)

UNION

(Select c.nome

From Cliente as c, Pedido as p, Produto as pr

Where c.ccodifgo=p.cliente and

p.pedcodigo=i.pedido and

i.produto=pr.procodigo and

i.preço>30)

Operações com Conjuntos - Intersect

• Listar todos os clientes que realizaram pedidos com mais de 5 itens e contendo algum produto de preço>R$30,00

(Select c.nome

From Cliente as c, Pedido as p, Produto as pr

Where c.ccodifgo=p.cliente and

p.pedcodigo=i.pedido and

i.quantidade>5)

INTERSECT

(Select c.nome

From Cliente as c, Pedido as p, Produto as pr

Where c.ccodifgo=p.cliente and

p.pedcodigo=i.pedido and

i.produto=pr.procodigo and

i.preço>30)

Operações com Conjuntos - Except

• Listar todos os clientes que realizaram pedidos com mais de 5 itens mas não contendo algum produto de preço>R$30,00

(Select c.nome

From Cliente as c, Pedido as p, Produto as pr

Where c.ccodifgo=p.cliente and

p.pedcodigo=i.pedido and

i.quantidade>5)

EXCEPT

(Select c.nome

From Cliente as c, Pedido as p, Produto as pr

Where c.ccodifgo=p.cliente and

p.pedcodigo=i.pedido and

i.produto=pr.procodigo and

i.preço>30)

Exercício de Fixação

EMPREGADOS (E)

enome CPF salário CPF_Supervisor dnumero

DEPARTAMENTOS (D)

dnome dnúmero CPF_gerente

TRABALHA (T)

CPF_Emp pnumero pnome pnúmero dnúmero

PROJETOS (P)

Exercício de Fixação

• Atividades:

– 1. Fazer o esquema relacional acima;

– 2.Inserir o empregado André que trabalham no projeto chamado CID do departamento de computação;

– 3.Inserir o empregado Suzana que trabalha no projeto chamado CID do departamento de computação;

– 4. Inserir o empregado Lívia que é gerente dos empregados André e Suzana;

– 5. Inserir o departamento de estatística e seus projetos: KDE, SUDO, MQRO, ACLT.

Até a próxima aula!

Ora, a fé é a certeza daquilo que esperamos e a prova das coisas que não vemos. Hebreus 11:1