01. Álgebra Relacional (Resumo)

Prévia do material em texto

Sistemas de Bancos de Dados 26/09/2014
Prof. Jorge Soares <jasoares@uerj.br> 1
Sistemas de Bancos de Dados
Prof. Jorge Soares
jasoares@uerj.br
Tema
Álgebra Relacional
Agenda
• Introdução
• Operadores
– Seleção
– Projeção
– Produto Cartesiano
– Junção Interna e Natural
– Renomeação
– União, Interseção e Diferença
– Junção Externa
– Agregação
– Divisão
Banco de Dados – Prof. Jorge Soares 2
Sistemas de Bancos de Dados 26/09/2014
Prof. Jorge Soares <jasoares@uerj.br> 2
Introdução
• Álgebra que sustenta o modelo relacional, proposto por
E. F. Codd
• Composto por operadores aplicados às relações, que as
modificam e geram novas relações (tabelas).
• Os operadores podem ser unários ou binários.
Banco de Dados – Prof. Jorge Soares 3
Operadores
Seleção (σσσσ)
• A partir de uma relação, aplica um critério sobre o seu estado,
selecionando as tuplas que a atendem
Exemplo:
Relação PEÇA (cod, nome, precoUnit, qtdEstoque)
Se quisermos selecionar as tuplas com valor maior do que
R$ 100,00, faremos:
PEÇA_CARA ← σσσσ (PEÇA)
Banco de Dados – Prof. Jorge Soares 4
precoUnit > 100,00
Sistemas de Bancos de Dados 26/09/2014
Prof. Jorge Soares <jasoares@uerj.br> 3
Operadores
Seleção (σσσσ)
PEÇA_CARA ←←←← σσσσ (PEÇA)
Banco de Dados – Prof. Jorge Soares 5
precoUnit > 100,00
Operadores
Projeção (pi)
• A partir de uma relação, cria uma nova relação que contém todas as
colunas especificadas
• As repetições de linhas são eliminadas
Exemplo:
Relação PEÇA (cod, nome, precoUnit, qtdEstoque)
Se quisermos selecionar o nome e o preço unitário da
relação PECA, faremos:
NOME_PRECO_PECA ← pi (PEÇA)
Banco de Dados – Prof. Jorge Soares 6
nome, precoUnit
Sistemas de Bancos de Dados 26/09/2014
Prof. Jorge Soares <jasoares@uerj.br> 4
Operadores
Projeção (pi)
NOME_PRECO_PECA ←←←← pipipipi (PEÇA)
Banco de Dados – Prof. Jorge Soares 7
nome, precoUnit
Operadores
Produto Cartesiano (×)
• Combina todas as tuplas do estado de uma relação com todas as tuplas do
estado da outra relação.
• A nova relação gerada possui como estado a união dos atributos das duas
tabelas envolvidas
Exemplo:
Relações FUNCIONARIO (codF, nomeF, codDepto) e
DEPARTAMENTO (codD, nomeD)
A relação PRODUTO CARTESIANO é indicada por:
NovaRel ← FUNCIONARIO × DEPARTAMENTO
Banco de Dados – Prof. Jorge Soares 8
Sistemas de Bancos de Dados 26/09/2014
Prof. Jorge Soares <jasoares@uerj.br> 5
Operadores
Produto Cartesiano (×)
NovaRel ←←←← FUNCIONARIO ×××× DEPARTAMENTO
Banco de Dados – Prof. Jorge Soares 9
Operadores
Produto Cartesiano (×)
NovaRel ←←←← FUNCIONARIO ×××× DEPARTAMENTO
Banco de Dados – Prof. Jorge Soares 10
Sistemas de Bancos de Dados 26/09/2014
Prof. Jorge Soares <jasoares@uerj.br> 6
Operadores
Junção ( )
• Combina todas as tuplas do estado de uma relação com todas as tuplas do
estado da outra relação.
• A nova relação gerada possui como estado a união dos atributos das duas
tabelas envolvidas
Exemplo:
Relações FUNCIONARIO (codF, nomeF, codDepto) e
DEPARTAMENTO (codD, nomeD)
A relação PRODUTO CARTESIANO é indicada por:
NovaRel ← FUNCIONARIO DEPARTAMENTO
Banco de Dados – Prof. Jorge Soares 11
codDepto = codD
Operadores
Junção ( )
NovaRel ←←←← FUNCIONARIO DEPARTAMENTO
Banco de Dados – Prof. Jorge Soares 12
codDepto = codD
Sistemas de Bancos de Dados 26/09/2014
Prof. Jorge Soares <jasoares@uerj.br> 7
Operadores
Junção ( )
NovaRel ←←←← FUNCIONARIO DEPARTAMENTO
Banco de Dados – Prof. Jorge Soares 13
codDepto = codD
Operadores
Junção ( )
NovaRel ←←←← FUNCIONARIO DEPARTAMENTO
Banco de Dados – Prof. Jorge Soares 14
codDepto = codD
Sistemas de Bancos de Dados 26/09/2014
Prof. Jorge Soares <jasoares@uerj.br> 8
Operadores
Junção Natural (*)
• Junção entre duas tabelas, onde o(s) atributo(s) de igualdade
possui o mesmo nome nas duas relações
Exemplo:
Relações FUNCIONARIO (codF, nomeF, codD) e
DEPARTAMENTO (codD, nomeD)
Junção Natural: FUNCIONARIO * DEPARTAMENTO
Banco de Dados – Prof. Jorge Soares 15
Operadores
Junção Natural (*)
FUNCIONARIO * DEPARTAMENTO
Banco de Dados – Prof. Jorge Soares 16
Sistemas de Bancos de Dados 26/09/2014
Prof. Jorge Soares <jasoares@uerj.br> 9
Operadores
Renomeação (ρ)
• Altera as características do esquema de uma relação
Exemplo:
Relação FUNCIONARIO (cod, nome, codDepto)
1. Alterando o nome da relação:
ρ (FUNCIONARIO)
2. Alterando o nome de dois atributos:
ρ (FUNCIONARIO)
3. Alterando o nome da relação e dos atributos:
ρ (FUNCIONARIO)
Banco de Dados – Prof. Jorge Soares 17
FUNC
(codFunc, nomeFunc, codDepto)
FUNC (codFunc, nomeFunc, codDepto)
Exercícios
FUNCIONARIO (matricula, nome_func, anos_servico, salario, cod_orgao <FK>)
cod_orgao REFERENCIA ORGAO(cod_orgao)
ORGAO (cod_orgao, nome_orgao)
DEPARTAMENTO (cod_depto <FK>, localizacao)
cod_depto REFERENCIA ORGAO(cod_orgao)
DIVISAO (cod_div <FK>)
cod_div REFERENCIA ORGAO(cod_orgao)
SUBORDINADO (cod_depto <FK>, cod_div <FK>)
cod_depto REFERENCIA ORGAO(cod_orgao)
cod_div REFERENCIA ORGAO(cod_orgao)
GERENCIA (mat_ger <FK>, cod_orgao <FK>)
mat_ger REFERENCIA FUNCIONARIO(matricula)
cod_orgao REFERENCIA ORGAO(cod_orgao)
Banco de Dados – Prof. Jorge Soares 18
Sistemas de Bancos de Dados 26/09/2014
Prof. Jorge Soares <jasoares@uerj.br> 10
Exercícios
FUNC_COMUM (mat_func <FK>, cargo)
mat_func REFERENCIA FUNCIONARIO(matricula)
PROJETO (cod_proj, nome_proj, inicio, fim)
ALOCA (cod_proj <FK>, mat_func <FK>, data_alocacao)
cod_proj REFERENCIA PROJETO(cod_proj)
mat_func REFERENCIA FUNCIONARIO(matricula)
COORDENA (cod_projeto <FK>, mat_coord <FK>, data_alocacao)
cod_proj REFERENCIA PROJETO(cod_proj)
mat_func REFERENCIA FUNCIONARIO(matricula)
SUPERVISIONA (mat_superior <FK>, mat_func <FK>)
mat_superior REFERENCIA FUNCIONARIO(matricula)
mat_func REFERENCIA FUNCIONARIO(matricula)
PROJ_TECNICO (cod_proj <FK>, area_pesquisa)
cod_proj REFERENCIA PROJETO(cod_proj)
Banco de Dados – Prof. Jorge Soares 19
Exercícios
Modelo de classes aproximado
Banco de Dados – Prof. Jorge Soares 20
Sistemas de Bancos de Dados 26/09/2014
Prof. Jorge Soares <jasoares@uerj.br> 11
Exercícios
1. Liste os nomes dos funcionários que tenham entre 10 e 12 anos de serviço 
(inclusive).
2. Liste o nome, a matrícula e o salário de todas as pessoas cujo salário não esteja 
entre R$ 1.000,00 e R$ 3.000,00.
3. Liste os nomes dos funcionários com exatamente 5, 8 ou 13 anos de serviço.
4. Liste a matrícula, nome e salário de todas as pessoas.
5. Liste o código, o nome e as datas de todos os projetos técnicos.
6. Liste o nome do departamento, a matrícula, o nome e o salário dos funcionários.
7. Liste os nomes dos gerentes de divisão e o número e o nome da respectiva divisão.
8. Liste matrícula, nome, anos de serviço de todos os gerentes de departamentos que 
recebem salários maiores que R$ 2.000,00 ou que possuam matrículas maiores 
que 30, e que tenham mais de 6 anos de serviço.
Banco de Dados – Prof. Jorge Soares 21
Exercícios
9. Liste o nome do departamento, matrícula, nome e salário de todos os gerentes.
10. Liste a matrícula, nome de todos os supervisores e os nomes de seus respectivos 
subordinados.
11. Liste o nome dos funcionários, o nome de seu departamento e o nome de seus 
respectivos supervisores.
12. Liste o número e o nome das divisões, o código e o nome dos respectivos 
departamentos subordinados a esta.
13. Liste as matrículas, os nomes e os departamentos de todos os empregados que 
gerenciam algum departamento.
14. Liste a matrícula, nome, cargo, anos de serviço e salário de todas as pessoas com 
4 ou mais anos de serviço ou que ganhem salário superior a R$ 500,00.
Banco de Dados – Prof. Jorge Soares 22
Sistemas de Bancos deDados 26/09/2014
Prof. Jorge Soares <jasoares@uerj.br> 12
Operadores
União, Interseção e Diferença
• As duas relações devem possuir o mesmo esquema
Exemplo:
Relações T1 (a,b,c) e T2 (a,b,c)
Banco de Dados – Prof. Jorge Soares 23
Operadores
União (∪)
T1 ∪∪∪∪ T2
�Observe que a tupla (2,8,1) só aparece uma única vez
Banco de Dados – Prof. Jorge Soares 24
Sistemas de Bancos de Dados 26/09/2014
Prof. Jorge Soares <jasoares@uerj.br> 13
Operadores
Interseção (∩)
T1 ∩ T2
Banco de Dados – Prof. Jorge Soares 25
Operadores
Diferença (−)
T1 − T2
Banco de Dados – Prof. Jorge Soares 26
Sistemas de Bancos de Dados 26/09/2014
Prof. Jorge Soares <jasoares@uerj.br> 14
Exercícios
15. Liste o nome e a matrícula dos funcionários que não são gerentes.
16. Mostre os nomes dos gerentes de departamentos ou divisões que 
também sejam coordenadores de projetos.
17. Liste o nome de funcionários que supervisionem outros empregados ou 
que sejam coordenadores de projetos.
Banco de Dados – Prof. Jorge Soares 27
Operadores
Junção Externa (Outer Join)
• Combina todas as tuplas do estado de uma relação com todas as tuplas do
estado da outra relação.
• Todas as tuplas de um lado da relação ocorrem no resultado, e as do outro
lado somente se os valores combinarem
• Tipos de junções externas:
o Junção Externa à Esquerda – Left Outer Join
o Junção Externa à Direita – Right Outer Join
o Junção Externa Completa – Full Outer Join
Banco de Dados – Prof. Jorge Soares 28
Sistemas de Bancos de Dados 26/09/2014
Prof. Jorge Soares <jasoares@uerj.br> 15
Operadores
Junção Externa (Outer Join)
Exemplo:
Relações FUNCIONARIO (codF, nomeF, codProj) e PROJETO (codP,
nomeP)
Estado Inicial das relações:
Banco de Dados – Prof. Jorge Soares 29
Operadores
Junção Externa (Outer Join)
� Resultado com a junção interna (Inner Join)
Banco de Dados – Prof. Jorge Soares 30
Sistemas de Bancos de Dados 26/09/2014
Prof. Jorge Soares <jasoares@uerj.br> 16
Operadores
Left Outer Join ( )
FUNCIONARIO PROJETO
Banco de Dados – Prof. Jorge Soares 31
codProj = codP
Operadores
Right Outer Join ( )
FUNCIONARIO PROJETO
Banco de Dados – Prof. Jorge Soares 32
codProj = codP
Sistemas de Bancos de Dados 26/09/2014
Prof. Jorge Soares <jasoares@uerj.br> 17
Operadores
Full Outer Join ( )
FUNCIONARIO PROJETO
Banco de Dados – Prof. Jorge Soares 33
codProj = codP
Exercícios
18. Liste o código, o nome e as datas de todos os projetos. Caso sejam 
projetos técnicos, liste também a área de pesquisa.
19. Liste a matrícula e o nome de todos os funcionários. Caso eles estejam 
alocados a projetos, liste também o nome destes projetos.
Banco de Dados – Prof. Jorge Soares 34
Sistemas de Bancos de Dados 26/09/2014
Prof. Jorge Soares <jasoares@uerj.br> 18
Operadores
Agregação (ℱ)
• Separa o estado de uma tabela em grupos, em função de um ou mais
atributos de agregação
• Realiza estatísticas sobre os valores dos atributos de uma relação
• Funções Agregadas utilizadas:
Banco de Dados – Prof. Jorge Soares 35
• COUNT
• SUM
• AVG
• MIN
• MAX
Operadores
Agregação (ℱ)
Exemplo:
Estado inicial das relações
Banco de Dados – Prof. Jorge Soares 36
Sistemas de Bancos de Dados 26/09/2014
Prof. Jorge Soares <jasoares@uerj.br> 19
Operadores
Agregação (ℱ)
Exemplo:
Estado inicial das relações
Banco de Dados – Prof. Jorge Soares 37
Operadores
Agregação (ℱ)
R ←←←← ℱ (FUNCIONARIO)
Resultado:
Banco de Dados – Prof. Jorge Soares 38
COUNT salario
Sistemas de Bancos de Dados 26/09/2014
Prof. Jorge Soares <jasoares@uerj.br> 20
Operadores
Agregação (ℱ)
R ←←←← ℱ (FUNCIONARIO)
Resultado:
Banco de Dados – Prof. Jorge Soares 39
AVG salariosexo
Operadores
Agregação (ℱ)
O que aconteceu nas internas da última operação realizada:
Banco de Dados – Prof. Jorge Soares 40
Sistemas de Bancos de Dados 26/09/2014
Prof. Jorge Soares <jasoares@uerj.br> 21
Operadores
Agregação (ℱ)
Liste a média salarial dos funcionários por nome do departamento e sexo
Resposta:
R1 ←←←← FUNCIONARIO * DEPARTAMENTO
R2 ←←←← ℱℱℱℱ (R1)
Banco de Dados – Prof. Jorge Soares 41
nomeD, sexo AVG salario
Operadores
Agregação (ℱ)
Processamento interno:
Banco de Dados – Prof. Jorge Soares 42
Sistemas de Bancos de Dados 26/09/2014
Prof. Jorge Soares <jasoares@uerj.br> 22
Operadores
Agregação (ℱ)
Resultado:
Banco de Dados – Prof. Jorge Soares 43
Operadores
Agregação (ℱ)
Outro exemplo...
Liste a média salarial dos funcionários por sexo e nome do departamento
Resposta:
R1 ←←←← FUNCIONARIO * DEPARTAMENTO
R2 ←←←← ℱℱℱℱ (R1)
Banco de Dados – Prof. Jorge Soares 44
sexo, nomeD AVG salario
Sistemas de Bancos de Dados 26/09/2014
Prof. Jorge Soares <jasoares@uerj.br> 23
Operadores
Agregação (ℱ)
Processamento interno:
Banco de Dados – Prof. Jorge Soares 45
Operadores
Agregação (ℱ)
Resultado:
Banco de Dados – Prof. Jorge Soares 46
Sistemas de Bancos de Dados 26/09/2014
Prof. Jorge Soares <jasoares@uerj.br> 24
Exercícios
20. Forneça o número total de empregados da companhia.
21. Liste a média salarial por anos de serviço dos funcionários da companhia.
22. Liste, para cada departamento, o seu nome e sua média salarial.
23. Liste para cada número de anos de serviço o número de empregados e 
seu salário médio, mas somente para aqueles grupos que possuam mais 
de duas pessoas.
24. Liste para cada supervisor, o seu nome e o número de subordinados que 
este possui.
25. Liste o salário médio dos empregados por local e cargo para grupos de 
mais de um empregado.
Banco de Dados – Prof. Jorge Soares 47
Operadores
Divisão (÷)
• A partir de duas relações, onde os atributos da segunda relação
representem um subconjunto dos atributos da primeira, deriva-se uma
terceira tabela, que tem como esquema os atributos da 1ª relação que não
aparecem na 2ª, e como estado valores oriundos de tuplas da 1ª relação
que combinem com todas as tuplas da 2ª relação, numa dada combinação
de valores.
Banco de Dados – Prof. Jorge Soares 48
Sistemas de Bancos de Dados 26/09/2014
Prof. Jorge Soares <jasoares@uerj.br> 25
Operadores
Divisão (÷)
Exemplo:
Banco de Dados – Prof. Jorge Soares 49
Operadores
Divisão (÷)
Combinação de grupos (b,c) de T2 em T1:
Banco de Dados – Prof. Jorge Soares 50
Sistemas de Bancos de Dados 26/09/2014
Prof. Jorge Soares <jasoares@uerj.br> 26
Operadores
Divisão (÷)
Resultado:
Banco de Dados – Prof. Jorge Soares 51
Exercícios
26. Liste o nome e matrícula dos funcionários que gerenciam todos os 
projetos técnicos.
27. Liste o nome, matrícula e lotação dos funcionários que gerenciam todos 
os projetos que ainda não terminaram.
Banco de Dados – Prof. Jorge Soares 52