algebra-relacional

Prévia do material em texto

BANCO DE DADOS
aula 6 – álgebra relacional -
professor: Juliano Varella de Carvalho
INSTITUTO DE EXATAS E TECNOLÓGICAS
Curso de Ciência da Computação
agencia
E-008Helena
E-005Ana
E-001João
num_emprestimonome_cliente
devedor
18000008SAL-2
37500007SAL-2
2000006NOH-1
15000005POA-1
40000004POA-1
45000003NOH-1
30000002SAL-1
12000001SAL-1
saldonumero_contanome_agencia
conta
125588São LeopoldoSAL-2
1250369Porto AlegrePOA-1
455580São LeopoldoSAL-1
260050Novo HamburgoNOH-1
saldocidade_agencianome_agencia
Novo HamburgoFloriano PeixotoBeatriz
Porto alegreAssis BrasilAna
Novo HamburgoJoão GoulartJosé
São LeopoldoCecília MeirelesPaulo
Porto AlegreJoão PessoaMaria
Novo HamburgoOlavo BilacFrancisco
São LeopoldoGetúlio VargasPedro
São LeopoldoGetúlio VargasJoão
cidaderuanome
cliente
0005Ana
0006José
0007Paulo
0004Maria
0003Francisco
0002Pedro
0001João
numero_contanome_cliente
depositante
5420E-008NOH-1
25400E-005POA-1
40000E-001SAL-1
totalnum_emprestimonome_agencia
emprestimo
5.1. Álgebra Relacional – relações de exemplo
• Linguagens de Consulta: linguagem por meio da qual os usuários obtêm informações 
do banco de dados. Podem ser:
- procedurais: o usuário descreve uma seqüência de operações para obter o 
resultado desejado; Ex: pascal, pl-sql, transact-sql, etc.
- não procedurais: o usuário descreve a informação desejada, sem instruir como 
esta será obtida. Ex: sql, QBE (Query by example), etc.
• Álgebra Relacional: linguagem de consulta que consiste em um conjunto de operações, 
tendo como entrada uma ou duas relações e produzindo como resultado uma nova relação. 
Operações mais importantes:
- select; - set difference;
- project; - cartesian product;
- Union; - rename;
- namely - set intersection;
- natural join - division;
- assignment;
- Operações primárias: operam sobre uma única relação: select, project, rename.
- Operações binárias: operam sobre duas relações: union, set difference, cartesian 
product.
5.2. Álgebra Relacional – conceitos
O resultado da seleção é
- podem ser usados comparações do tipo: =,≠,≥,>,≤,< e os conectivos: ∧ (e) ∨ (ou)
• Operação PROJECT: denotada pela letra grega pi (pi), esta operação retorna a relação com 
somente os atributos selecionados, eliminando as linhas em duplicidade. Os atributos aparecem 
ao lado de pi, subscrito, e a relação aparece entre parênteses.
30000002SAL-1
12000001SAL-1
saldonumero_contanome_agencia
125588São LeopoldoSAL-2
1250369Porto AlegrePOA-1
455580São LeopoldoSAL-1
260050Novo HamburgoNOH-1
saldocidade_agencianome_agencia agencia
Ex: pinome_agência,saldo(agencia)
125588SAL-2
1250369POA-1
455580SAL-1
260050NOH-1
saldonome_agencia
5.3. Álgebra Relacional – operações fundamentais
• Operação SELECT: seleciona as tuplas que satisfaçam um determinado predicado. A letra 
sigma representa a seleção, o predicado aparece subscrito a sigma, e a relação utilizada é 
colocada entre parênteses.
Ex: σnome_agencia = “SAL-1”(conta) : seleciona as tuplas da relação conta cuja agência é igual 
a “SAL-1”.
- Juntando operações SELECT E PROJECT: pode-se unir operações desde que elas tratem de 
relações.
pinome_agencia,saldo(σnome_agencia = “SAL-1”(conta) )
3000SAL-1
1200SAL-1
saldonome_agencia
30000002SAL-1
12000001SAL-1
saldonumero_contanome_agencia
37500007SAL-2
2000006NOH-1
15000005POA-1
40000004POA-1
45000003NOH-1
30000002SAL-1
12000001SAL-1
saldonumero_contanome_agenciaconta
5.3. Álgebra Relacional – operações fundamentais
• Operação UNION: considere a seguinte consulta: selecionar todos os nomes de clientes 
que tenham um empréstimo, uma conta ou ambos. Deve-se utilizar as relações 
“depositante” e “devedor”.
pinome_cliente(devedor) ∪ pinome_cliente(depositante)
Helena
Ana
José
Paulo
Maria
Francisco
Pedro
João
nome_cliente
(depositante)
(devedor)
(estão nas 2 relações)
- Uma operação UNION entre duas relações “r” e “s” só é possível se:
i. as relações “r” e “s” têm o mesmo número de atributos;
ii. os domínios dos i-ésimo atributo de “r” e o i-ésimo atributo de “s” devem ser os 
mesmos para todo “i”.
• Operação Diferença entre conjuntos: sendo “r” e “s” duas relações, “r-s” tem como 
resultado o conjunto de tuplas que estão na relação “r”, mas não encontram-se na relação 
“s”.
- Ex: selecionar todos os clientes que tem conta mas não tem empréstimo.
pinome_cliente(depositante) - pinome_cliente(devedor)
5.3. Álgebra Relacional – operações fundamentais
Ana
José
Paulo
Maria
Francisco
Pedro
João
nome_cliente
Linhas eliminadas
Clientes com contas
Mas sem empréstimos
5.4. Álgebra Relacional – operações adicionais
• Operação de Interseção de conjuntos: captura todas as tuplas que encontram-se em uma 
relação “r”, e que também encontram-se na relação “s”.
- Ex: Selecionar os nomes de clientes que possuem uma conta e um empréstimo.
pinome_cliente(devedor) ∩ pinome_cliente(depositante)
0005Ana
0006José
0007Paulo
0004Maria
0003Francisco
0002Pedro
0001João
numero_contanome_cliente
depositante
E-008Helena
E-005Ana
E-001João
num_emprestimonome_client
e
devedor
Ana
João
nome_client
e
r ∩ s = r – (r - s)
r s
r-s
r 
– 
(r
 -
 s
)
• Operação Produto Cartesiano: permite combinar informações de duas relações quaisquer. Representado 
por “r x s”.
Ex: Qual o produto cartesiano entre as relações devedor e emprestimo.
(devedor)x(emprestimo) = (nome_cliente, num_emprestimo, nome_agencia, num_emprestimo, 
total)
Obs: Para evitar o problema da duplicidade de nomes de atributos insere-se o nome da relação 
antes do atributo:
(nome_cliente, devedor.num_emprestimo, nome_agencia, emprestimo.num_emprestimo, 
total)
- Quantas e quais tuplas aparecerão na relação (devedor x emprestimo) ?
Se a relação (devedor) possui “n” tuplas e a relação empréstimo tem “m” tuplas, então o produto 
cartesiano (devedor x empréstimo) tem n*m tuplas.
5.3. Álgebra Relacional – operações fundamentais
5420E-008NOH-1
25400E-005POA-1
40000E-001SAL-1
5420E-008NOH-1
25400E-005POA-1
40000E-001SAL-1
5420E-008NOH-1
25400E-005POA-1
40000E-001SAL-1
totalemprestimo.num_emprestimonome_agencia
E-001João
E-001João
E-008Helena
E-008Helena
E-008Helena
E-005Ana
E-005Ana
E-005Ana
E-001João
devedor.num_emprestimonome_cliente
devedor x emprestimo
- Agora, deve-se encontrar todos os nomes de clientes que tenham um empréstimo na 
agência “SAL-1”, através da operação:
σnome_agência = “SAL-1”(devedor x emprestimo)
5.3. Álgebra Relacional – operações fundamentais
40000E-001SAL-1
40000E-001SAL-1
40000E-001SAL-1
totalemprestimo.núm_emprestimonome_agencia
E-008Helena
E-005Ana
E-001João
devedor.num_emprestimonome_client
e
Apenas João fez empréstimo em SAL-1.
OPS !!!
Obs: Como a operação produto cartesiano associa todas as tuplas de empréstimos a todas as 
tuplas de devedor, então existem empréstimos que estão relacionados erroneamente a alguns 
clientes. Para contornar este problema, devemos exigir que o atributo devedor.num_emprestimo 
seja igual ao atributo emprestimo.num_emprestimo.
- Assim temos:
σdevedor.num_emprestimo = emprestimo.num_emprestimo (σnome_agência = “SAL-1” (devedor x empréstimo))
pinome_cliente(σdevedor.num_emprestimo = emprestimo.num_emprestimo (σnome_agência = “SAL-1” (devedor x empréstimo)))
40000E-001SAL-1
totalemprestimo.num_emprestimonome_agencia
E-001João
devedor.num_emprestimonome_client
e
João
nome_client
e
5.3. Álgebra Relacional – operações fundamentais
• Operação Rename: representado pela letra grega rho (ρ), 
permite dar um nome a uma determinada expressão.
Assim, a expressão em álgebra relacional E abaixo tem por 
resultado uma expressão sob o nome x.
ρx(E)
Ex: Desejamos o nome dos clientes que moram na mesma cidade 
de Francisco.
Primeiro, é necessário descobrir em qual cidade Francisco mora,
fazendo uma seleção:
σnome_cliente = “Francisco”(cliente),
E depois uma projeção:
 picidade_cliente(σnome_cliente = “Francisco”(cliente)) 
Para encontrar os outros clientes, é necessário fazer uma 
varredurana relação cliente, comparando o atributo 
cidade_cliente com o atributo da relação obtida após a 
projeção anterior, ficando assim:
pinome_cliente(σcliente.cidade_cliente = cid_francisco.cidade_cliente
(cliente x ρcid_francisco(picidade_cliente(σnome_cliente = “Francisco”(cliente)))))
Novo HamburgoFloriano PeixotoBeatriz
Porto alegreAssis BrasilAna
Novo HamburgoJoão GoulartJosé
São LeopoldoCecília MeirelesPaulo
Porto AlegreJoão PessoaMaria
Novo HamburgoOlavo BilacFrancisco
São LeopoldoGetúlio VargasPedro
São LeopoldoGetúlio VargasJoão
cidade_clienterua_clientenome_cliente
cliente
Novo HamburgoOlavo BilacFrancisco
cidade_clienterua_clientenome_cliente
Novo Hamburgo
cidade_cliente
Novo HamburgoFloriano PeixotoBeatriz
Porto alegreAssis BrasilAna
Novo HamburgoJoão GoulartJosé
São LeopoldoCecília MeirelesPaulo
Porto AlegreJoão PessoaMaria
Novo HamburgoOlavo BilacFrancisco
São LeopoldoGetúlio VargasPedro
São LeopoldoGetúlio VargasJoão
cidade_clienterua_clientenome_cliente
Novo Hamburgo
Novo Hamburgo
Novo Hamburgo
Novo Hamburgo
Novo Hamburgo
Novo Hamburgo
Novo Hamburgo
Novo Hamburgo
cidade_cliente
Beatriz
José
Francisco
nome_cliente
• Operação de Junção Natural: a junção natural ou natural join é uma operação binária que permite 
combinar seleções e um produto cartesiano em uma só operação.
- Ex: encontrar todos os nomes de clientes que tenham um empréstimo no banco, bem como o total 
emprestado.
Utilizando apenas as operações fundamentais, poderia ser resolvido assim:
pinome_cliente, emprestimo.num_emprestimo, total(σdevedor.num_emprestimo = emprestimo.num_emprestimo (devedor x emprestimo))
5.4. Álgebra Relacional – operações adicionais
No entanto, é possível simplificar esta operação utilizando o símbolo da junção natural: 
A junção natural forma um produto cartesiano das duas relações, e já executa uma seleção, fazendo a 
equivalência dos atributos que aparecem em ambos os esquemas da relação, e por último, remove os 
atributos em duplicidade.
pinome_cliente, num_emprestimo total(devedor empréstimo)
5420
25400
40000
total
E-008
E-005
E-001
num_emprestimo
Helena
Ana
João
nome_client
e
5.4. Álgebra Relacional – operações adicionais
Formalizando: Sendo R e S dois esquemas de relação, ou seja, uma lista de atributos e r(R) e s(S) duas 
relações, a junção natural de r e s (r s ) é uma relação formalmente definida assim:
r s = piR∪S(σr.A1=s.A1∧ r.A2=s.A2∧... ∧r.An=s.An(r x s))
Em que R ∩ S = {A1,A2,...,An}
Propriedades:
- a junção natural é associativa, portanto:
cliente conta depositante = (cliente conta) depositante = cliente (conta depositante)
- se r(R) e s(S) são relações sem atributos comuns, então r s = r x s
5.4. Álgebra Relacional – operações adicionais
• Operação de Divisão: a divisão de duas relações R ÷ S, onde os atributos de S estão contidos nos
atributos de R, A(R) ⊆ A(S), resulta na relação T, onde A(T) = { A(R) – A(S) }, onde para cada tupla
t que aparece no resultado, os valores de t devem aparecer em R, combinando com cada tupla de S. 
Esta operação é utilizada nas consultas em que se emprega a frase “para todos”;
b4a3
b4a2
b4a1
b3a4
b3a3
b3a2
b2a3
b2a1
b1a4
b1
b1
b1
A2
a3
a2
a1
A1
R
a3
a2
a1
A1
S
b4
b1
A2
R ÷ S
5.4. Álgebra Relacional – operações adicionais
18000008SAL-2
37500007SAL-2
2000006NOH-1
15000005POA-1
40000004POA-1
45000003NOH-1
30000002SAL-1
12000001SAL-1
saldonumero_contanome_agencia
125588São LeopoldoSAL-2
1250369Porto AlegrePOA-1
455580São LeopoldoSAL-1
260050Novo HamburgoNOH-1
saldocidade_agencianome_agencia
conta agencia
0008João
0005Ana
0006José
0007Paulo
0004Maria
0003Francisco
0002Pedro
0001João
numero_contanome_cliente
depositante
125588São LeopoldoSAL-2
455580São LeopoldoSAL-1
saldocidade_agêncianome_agência
SAL-2
SAL-1
nome_agência
SAL-2João
POA-1Ana
NOH-1José
SAL-2Paulo
POA-1Maria
NOH-1Francisco
SAL-1Pedro
SAL-1João
nome_agencianome_cliente
João
nome_cliente
- Ex: encontrar todos os clientes que tenham conta em todas as agências localizadas em 
São Leopoldo.
pinome_agencia(σcidade_agencia = “São Leopoldo”(agencia))
pinome_cliente, nome_agencia(depositante conta) ÷
pinome_agencia(σcidade_agencia = “São Leopoldo”(agencia))
pinome_cliente, nome_agencia(depositante conta)
5.4. Álgebra Relacional – operações adicionais
• Operação de Atribuição (Assignment Operation): trabalha de maneira similar à atribuição das 
linguagens de programação. Representada por ←
- Atribui-se a relação resultante de uma operação à direita de ←, a uma variável temporária, à 
esquerda, a qual poderá ser utilizada em relações subseqüentes.
- Ex: operação de divisão anterior poderia ser descrita como:
temp1 ← pinome_agencia(σcidade_agencia = “São Leopoldo”(agencia))
temp2 ← pinome_cliente, nome_agencia(depositante conta)
resultado ← temp2 ÷ temp1
5.5. Álgebra Relacional – operações estendidas
• Projeção generalizada: estendem as projeções, permitindo que operações aritméticas sejam 
usadas nas projeções.
piF1, F2, ..., Fn(E)
Onde E é uma expressão em álgebra relacional e cada Fi são expressões aritméticas envolvendo 
constantes e atributos no esquema de E.
- Ex: Dada a relação crédito, descobrir quanto cada cliente ainda pode utilizar
do seu crédito pessoal. 
pinome_cliente, limite_credito – credito_usado(credito)
36003600Flávia
50005800Maria
15001200Pedro
1003200Ana
22004000José
credito_usadolimite_creditonome_cliente
credito
0Flávia
800Maria
-300Pedro
3100Ana
1800José
limite_credito - credito_usadonome_cliente
5.5. Álgebra Relacional – operações estendidas
• Junção externa (outer join): extensão da operação de join para resolver informações omitidas.
- Ex: Dadas as relações a seguir com os seguintes esquemas:
empregado(nome_empregado, rua, cidade)
trabalhador_integral(nome_empregado, nome_agencia, salario)
João Pessoa
Getúlio Vargas
Assis Brasil
João Goulart
rua
Porto AlegreMaria
São LeopoldoPedro
Porto AlegreAna
Novo HamburgoJosé
cidadenome_empregado
POA-1
SAL-1
POA-1
NOH-1
nome_agencia
6500Maria
3200Flávia
4800Ana
5000José
salarionome_empregado
trabalhador_integralempregado
Descobrir a rua, cidade, agência e salário de todos os empregados.
Alternativa natural é fazer uma seleção com uma junção natural:
pinome_empregado, rua, cidade, nome_agencia, salario(empregado trabalhador_integral)
João Pessoa
Assis Brasil
João Goulart
rua
Porto AlegreMaria
Porto AlegreAna
Novo HamburgoJosé
cidadenome_empregado
POA-1
POA-1
NOH-1
nome_agencia
6500
4800
5000
salario
problema: os dados de rua e cidade do empregado Pedro foram perdidos, bem como os dados de agência 
e salário foram perdidos da empregada Flávia.
solução: utilizar as operações de junções externas para evitar perda de informações, que podem ser:
5.5. Álgebra Relacional – operações estendidas
São LeopoldoGetúlio VargasPedro
João Pessoa
Assis Brasil
João Goulart
rua
Porto AlegreMaria
Porto AlegreAna
Novo HamburgoJosé
cidadenome_empregado
6500POA-1
Nulo
POA-1
NOH-1
nome_agencia
Nulo
4800
5000
salario
empregado trabalhador_integral
NuloNuloFlávia
João Pessoa
Assis Brasil
João Goulart
rua
Porto AlegreMaria
Porto AlegreAna
Novo HamburgoJosé
cidadenome_empregado
6500POA-1
SAL-1
POA-1
NOH-1
nome_agencia
3200
4800
5000
salario
empregado trabalhador_integral
São LeopoldoGetúlio VargasPedro
NuloNuloFlávia
João Pessoa
Assis Brasil
João Goulart
rua
Porto AlegreMaria
Porto AlegreAna
Novo HamburgoJosé
cidadenome_empregado
NuloNulo
6500POA-1
SAL-1
POA-1
NOH-1
nome_agencia
3200
4800
5000
salario
empregado trabalhador_integral
A junção à esquerda pega todas as tuplas 
da relação à esquerda que não encontraram 
par entre as tuplas da relação à direita, 
preenchendo com “nulo” os valores não 
encontrados. Estas tuplas são adicionadas 
ao resultado da junção natural.
A junção à direita pegatodas as tuplas da 
relação à direita que não encontraram par 
entre as tuplas da relação à esquerda, 
preenchendo com “nulo” os valores não 
encontrados. Estas tuplas são adicionadas 
ao resultado da junção natural.
A junção externa total faz ambas operações 
acima.
Junção externa a esquerda
Junção externa a direita
Junção externa total
5.5. Álgebra Relacional – operações estendidas
• Funções agregadas: são aquelas que, quando aplicadas, tomam uma coleção de valores e 
retornam um valor simples como resultado.
- função sum: descobrir a soma total dos salários de
todos os empregados de tempo integral.
sum salario (trabalhador_integral)
-função count: descobrir o número de agências existentes
na tabela de tempo integral.
count nome_agencia (trabalhador_integral)
count-distinct nome_agencia (trabalhador_integral)
- função avg: descobrir a média dos salários.
avg salario (trabalhador_integral)
- função min: descobrir o menor salário.
min salario (trabalhador_integral)
- função max: descobrir o maior salário.
max salario (trabalhador_integral)
POA-1
SAL-1
POA-1
NOH-1
nome_agencia
6500Maria
3200Flávia
4800Ana
5000José
salarionome_empregado
trabalhador_integral
14505
sum salario
4
count nome_agencia
3
count-distinct nome_agencia
3626.25
avg salario
3200
min salario
6500
max salario
5.5. Álgebra Relacional – operações estendidas
• Em alguns casos, aplicam-se as funções agregadas não somente a um único conjunto de tuplas, mas a 
diversos grupos, onde cada grupo é um conjunto de tuplas. Isto é realizado através da operação 
chamada agrupamento.
Ex: descobrir a soma total dos salários dos empregados
de tempo integral, por agência.
solução: particionar a relação trabalhador_integral em
grupos, com base na agência as quais os empregados fazem parte e,
por fim aplicar a função agregada a cada um destes grupos.
nome_agencia g sum salario (trabalhador_integral) nome_agencia g sum salario min salario (trabalhador_integral)
- Formalmente: G1, G2, ..., Gn g F1A1, F2A2, FmAm (E), onde:
E: é uma expressão qualquer em álgebra relacional;
Cada Gi constitui um atributo que será agrupado;
Cada Fi é uma função agregada;
Cada Ai é o nome de um abtributo;
3000NOH-1Bruno
1000SAL-1Beatriz
4000POA-1Fernando
POA-1
SAL-1
POA-1
NOH-1
nome_agencia
6500Maria
3200Flávia
4800Ana
5000José
salarionome_empregado
trabalhador_integral
SAL-1
POA-1
NOH-1
nome_agencia
4200
15300
8000
sum salario
4200
15300
8000
sum salario
SAL-1
POA-1
NOH-1
nome_agencia
1000
4000
3000
min salario
5.5. Álgebra Relacional – modificações no BD
• exclusão: permite a exclusão de tuplas inteiras de uma relação. É expressa por:
r ← r – E, onde:
r: é uma relação;
E: é uma consulta em álgebra relacional
Ex: excluir todos as contas do cliente João.
conta ← conta - σ nome_cliente = “João” (conta)
Ex: excluir todas as contas das agências localizadas em São Leopoldo.
r1 ← σ cidade_agencia = “São Leopoldo” (conta agencia)
r2 ← pi nome_agencia, numero_conta, saldo (r1)
conta ← conta – r2 
5.5. Álgebra Relacional – modificações no BD
• inserção: permite a inserção de um conjunto de tuplas em uma relação. 
É expressa por: r ← r ∪ E, onde r é uma relação e E: é uma consulta 
em álgebra relacional
Obs:
i. os valores dos atributos das tuplas a inserir devem ser membros 
do domínio dos atributos;
ii. As tuplas inseridas devem ter o mesmo grau da relação.
Ex: inserir que o cliente Saulo tem 3800 na conta 010 da agência NOH-1.
conta ← conta ∪ {(“NOH-1”, 010, 3800)}
depositante ← depositante ∪ {(“Saulo”, 010)}
Ex: inserir uma nova caderneta de poupança de 100 reais para 
todos os clientes da agência SAL-1, sendo o número do empréstimo o mesmo 
número usado para esta conta poupança.
r1 ← σ cidade_agencia = “SAL-1” (devedor emprestimo)
r2 ← pi nome_agencia, num_emprestimo (r1)
conta ← conta ∪ (r2 x {(100)})
depositante ← depositante ∪ pi nome_cliente, num_emprestimo (r1)
18000008SAL-2
37500007SAL-2
2000006NOH-1
15000005POA-1
40000004POA-1
45000003NOH-1
30000002SAL-1
12000001SAL-1
saldonumero_contanome_agencia
conta
0005Ana
0006José
0007Paulo
0004Maria
0003Francisco
0002Pedro
0001João
numero_contanome_cliente
depositante
E-008Helena
E-005Ana
E-001João
num_emprestimonome_client
e
devedor
5420E-008NOH-1
25400E-005POA-1
40000E-001SAL-1
totalnum_emprestim
o
nome_agencia
emprestimo
5.5. Álgebra Relacional – modificações no BD
• atualização: permite a mudança de uma tupla sem mudar todos os valores dela. Para isso usa-se o 
operador de projeção generalizada:
r ← pi F1, F2, ..., Fn (r), em que cada Fi é o i-ésimo atributo de “r” se o i-ésimo atributo não for atualizado, 
ou, se o i-ésimo atributo for atualizado, então cada Fi é uma expressão envolvendo somente constantes e os 
atributos de “r”, que dá novos valores a esses atributos.
Ex: A taxa de juros aumentou e o saldo de todas as contas aumentou 5%.
conta ← pi nome_agencia, numero_conta, saldo ← saldo * 1.05 (conta)
Ex: As contas com saldo acima de 2500 recebem 10%, e os demais 5%.
conta ← pi nome_agencia, numero_conta, saldo ← saldo * 1.1 (σ saldo > 2500 (conta)) ∪
 pi nome_agencia, numero_conta, saldo ← saldo * 1.05 (σ saldo <= 2500 (conta))