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-008 Helena E-005 Ana E-001 João num_emprestimo nome_cliente devedor 1800 0008 SAL-2 3750 0007 SAL-2 200 0006 NOH-1 1500 0005 POA-1 4000 0004 POA-1 4500 0003 NOH-1 3000 0002 SAL-1 1200 0001 SAL-1 saldo numero_conta nome_agencia conta 125588 São Leopoldo SAL-2 1250369 Porto Alegre POA-1 455580 São Leopoldo SAL-1 260050 Novo Hamburgo NOH-1 saldo cidade_agencia nome_agencia Novo Hamburgo Floriano Peixoto Beatriz Porto alegre Assis Brasil Ana Novo Hamburgo João Goulart José São Leopoldo Cecília Meireles Paulo Porto Alegre João Pessoa Maria Novo Hamburgo Olavo Bilac Francisco São Leopoldo Getúlio Vargas Pedro São Leopoldo Getúlio Vargas João cidade rua nome cliente 0005 Ana 0006 José 0007 Paulo 0004 Maria 0003 Francisco 0002 Pedro 0001 João numero_conta nome_cliente depositante 5420 E-008 NOH-1 25400 E-005 POA-1 40000 E-001 SAL-1 total num_emprestimo nome_agencia emprestimo
• 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.
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 (π), 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.
3000 0002 SAL-1 1200 0001 SAL-1 saldo numero_conta nome_agencia 125588 São Leopoldo SAL-2 1250369 Porto Alegre POA-1 455580 São Leopoldo SAL-1 260050 Novo Hamburgo NOH-1 saldo cidade_agencia nome_agencia agencia
Ex: πnome_agência,saldo(agencia)
125588 SAL-2 1250369 POA-1 455580 SAL-1 260050 NOH-1 saldo nome_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
- Juntando operações SELECT E PROJECT: pode-se unir operações desde que elas tratem de relações.
πnome_agencia,saldo(σnome_agencia = “SAL-1”(conta) )
3000 SAL-1 1200 SAL-1 saldo nome_agencia 3000 0002 SAL-1 1200 0001 SAL-1 saldo numero_conta nome_agencia 3750 0007 SAL-2 200 0006 NOH-1 1500 0005 POA-1 4000 0004 POA-1 4500 0003 NOH-1 3000 0002 SAL-1 1200 0001 SAL-1 saldo numero_conta nome_agencia conta
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”.
π
nome_cliente(devedor)
∪
π
nome_cliente(depositante)
Helena Ana José Paulo Maria Francisco Pedro João nome_cliente (depositante) (devedor)- 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. πnome_cliente(depositante) - πnome_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.
π
nome_cliente(devedor) ∩π
nome_cliente(depositante) 0005 Ana 0006 José 0007 Paulo 0004 Maria 0003 Francisco 0002 Pedro 0001 João numero_conta nome_cliente depositante E-008 Helena E-005 Ana E-001 João num_emprestimo nome_client e devedor Ana João nome_client er
∩
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
5420 E-008 NOH-1 25400 E-005 POA-1 40000 E-001 SAL-1 5420 E-008 NOH-1 25400 E-005 POA-1 40000 E-001 SAL-1 5420 E-008 NOH-1 25400 E-005 POA-1 40000 E-001 SAL-1 total emprestimo.num_emprestimo nome_agencia E-001 João E-001 João E-008 Helena E-008 Helena E-008 Helena E-005 Ana E-005 Ana E-005 Ana E-001 João devedor.num_emprestimo nome_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
40000 E-001 SAL-1 40000 E-001 SAL-1 40000 E-001 SAL-1 total emprestimo.núm_emprestimo nome_agencia E-008 Helena E-005 Ana E-001 João devedor.num_emprestimo nome_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))
πnome_cliente(σdevedor.num_emprestimo = emprestimo.num_emprestimo (σnome_agência = “SAL-1” (devedor x empréstimo))) 40000 E-001 SAL-1 total emprestimo.num_emprestimo nome_agencia E-001 João devedor.num_emprestimo nome_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:
πcidade_cliente(σnome_cliente = “Francisco”(cliente))
Para encontrar os outros clientes, é necessário fazer uma varredura na relação cliente, comparando o atributo cidade_cliente com o atributo da relação obtida após a projeção anterior, ficando assim:
πnome_cliente(σcliente.cidade_cliente = cid_francisco.cidade_cliente
(cliente x
ρ
cid_francisco(πcidade_cliente(σnome_cliente = “Francisco”(cliente)))))Novo Hamburgo Floriano Peixoto Beatriz Porto alegre Assis Brasil Ana Novo Hamburgo João Goulart José São Leopoldo Cecília Meireles Paulo Porto Alegre João Pessoa Maria Novo Hamburgo Olavo Bilac Francisco São Leopoldo Getúlio Vargas Pedro São Leopoldo Getúlio Vargas João cidade_cliente rua_cliente nome_cliente cliente Novo Hamburgo Olavo Bilac Francisco cidade_cliente rua_cliente nome_cliente Novo Hamburgo cidade_cliente Novo Hamburgo Floriano Peixoto Beatriz Porto alegre Assis Brasil Ana Novo Hamburgo João Goulart José São Leopoldo Cecília Meireles Paulo Porto Alegre João Pessoa Maria Novo Hamburgo Olavo Bilac Francisco São Leopoldo Getúlio Vargas Pedro São Leopoldo Getúlio Vargas João cidade_cliente rua_cliente nome_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:
π
nome_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.
π
nome_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 =
π
R∪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”;
b4 a3 b4 a2 b4 a1 b3 a4 b3 a3 b3 a2 b2 a3 b2 a1 b1 a4 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
1800 0008 SAL-2 3750 0007 SAL-2 200 0006 NOH-1 1500 0005 POA-1 4000 0004 POA-1 4500 0003 NOH-1 3000 0002 SAL-1 1200 0001 SAL-1 saldo numero_conta nome_agencia 125588 São Leopoldo SAL-2 1250369 Porto Alegre POA-1 455580 São Leopoldo SAL-1 260050 Novo Hamburgo NOH-1 saldo cidade_agencia nome_agencia conta agencia 0008 João 0005 Ana 0006 José 0007 Paulo 0004 Maria 0003 Francisco 0002 Pedro 0001 João numero_conta nome_cliente depositante 125588 São Leopoldo SAL-2 455580 São Leopoldo SAL-1 saldo cidade_agência nome_agência SAL-2 SAL-1 nome_agência SAL-2 João POA-1 Ana NOH-1 José SAL-2 Paulo POA-1 Maria NOH-1 Francisco SAL-1 Pedro SAL-1 João nome_agencia nome_cliente João nome_cliente- Ex: encontrar todos os clientes que tenham conta em todas as agências localizadas em
São Leopoldo.
π
nome_agencia(σcidade_agencia = “São Leopoldo”(agencia))π
nome_cliente, nome_agencia(depositante conta)÷
π
nome_agencia(σcidade_agencia = “São Leopoldo”(agencia))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 ←
π
nome_agencia(σcidade_agencia = “São Leopoldo”(agencia)) temp2 ←π
nome_cliente, nome_agencia(depositante conta)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.
π
F1, 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.
π
nome_cliente, limite_credito – credito_usado(credito)3600 3600 Flávia 5000 5800 Maria 1500 1200 Pedro 100 3200 Ana 2200 4000 José credito_usado limite_credito nome_cliente credito 0 Flávia 800 Maria -300 Pedro 3100 Ana 1800 José limite_credito - credito_usado nome_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 Alegre Maria São Leopoldo Pedro Porto Alegre Ana Novo Hamburgo José cidade nome_empregado POA-1 SAL-1 POA-1 NOH-1 nome_agencia 6500 Maria 3200 Flávia 4800 Ana 5000 José salario nome_empregado trabalhador_integral empregado
Descobrir a rua, cidade, agência e salário de todos os empregados. Alternativa natural é fazer uma seleção com uma junção natural:
π
nome_empregado, rua, cidade, nome_agencia, salario(empregado trabalhador_integral)João Pessoa Assis Brasil João Goulart rua Porto Alegre Maria Porto Alegre Ana Novo Hamburgo José cidade nome_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.
5.5. Álgebra Relacional – operações estendidas
São Leopoldo Getúlio Vargas Pedro João Pessoa Assis Brasil João Goulart rua Porto Alegre Maria Porto Alegre Ana Novo Hamburgo José cidade nome_empregado 6500 POA-1 Nulo POA-1 NOH-1 nome_agencia Nulo 4800 5000 salario empregado trabalhador_integral Nulo Nulo Flávia João Pessoa Assis Brasil João Goulart rua Porto Alegre Maria Porto Alegre Ana Novo Hamburgo José cidade nome_empregado 6500 POA-1 SAL-1 POA-1 NOH-1 nome_agencia 3200 4800 5000 salario empregado trabalhador_integral São Leopoldo Getúlio Vargas Pedro Nulo Nulo Flávia João Pessoa Assis Brasil João Goulart rua Porto Alegre Maria Porto Alegre Ana Novo Hamburgo José cidade nome_empregado Nulo Nulo 6500 POA-1 SAL-1 POA-1 NOH-1 nome_agencia 3200 4800 5000 salario empregado trabalhador_integralA 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 pega todas 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
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 6500 Maria 3200 Flávia 4800 Ana 5000 José salario nome_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_agenciag
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;
3000 NOH-1 Bruno 1000 SAL-1 Beatriz 4000 POA-1 Fernando POA-1 SAL-1 POA-1 NOH-1 nome_agencia 6500 Maria 3200 Flávia 4800 Ana 5000 José salario nome_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 ←π
nome_agencia, numero_conta, saldo (r1)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 ←
π
nome_agencia, num_emprestimo (r1) conta ← conta ∪ (r2 x {(100)})depositante ← depositante ∪
π
nome_cliente, num_emprestimo (r1)1800 0008 SAL-2 3750 0007 SAL-2 200 0006 NOH-1 1500 0005 POA-1 4000 0004 POA-1 4500 0003 NOH-1 3000 0002 SAL-1 1200 0001 SAL-1 saldo numero_conta nome_agencia conta 0005 Ana 0006 José 0007 Paulo 0004 Maria 0003 Francisco 0002 Pedro 0001 João numero_conta nome_cliente depositante E-008 Helena E-005 Ana E-001 João num_emprestimo nome_client e devedor 5420 E-008 NOH-1 25400 E-005 POA-1 40000 E-001 SAL-1 total num_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 ←
π
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 ←
π
nome_agencia, numero_conta, saldo ← saldo * 1.05 (conta)Ex: As contas com saldo acima de 2500 recebem 10%, e os demais 5%.
conta ←
