1 Álgebra Relacional Vania Bogorny

2 Modelo Relacional - ManipulaçãoDuas categorias de linguagens Formais : álgebra relacional e cálculo relacional Alto Nível (Comerciais) - baseadas nas linguagens formais - SQL Linguagens formais – Características orientadas a conjuntos linguagens de base : linguagens relacionais devem ter no mínimo um poder de expressão equivalente ao de uma linguagem formal Fechamento resultados de consultas são relações

3 Álgebra Relacional Álgebra desenvolvida para descrever operações sobre uma base de dados relacional O conjunto de objetos são as Relações Operadores para consulta e alteração de relações Linguagem procedural uma expressão na álgebra define uma execução seqüencial de operadores a execução de cada operador produz uma relação

4 Álgebra Relacional Os operadores da álgebra relacional recebem uma ou mais relações de entrada e geram uma nova relação de saída

5 Álgebra Relacional Porque aprender:Compreendendo álgebra relacional é mais fácil apreender SQL Não há SGBD que implementa álgebra diretamente como DML (Data Manipulation Language), mas SQL incorpora cada vez mais conceitos de álgebra Algoritmos de otimização de consulta são definidos sobre álgebra (possível uso internamente no SGBD)‏

6 Álgebra Relacional Operadores sobre conjuntos (uma tabela é um conjunto de linhas): União Interseção Diferença Produto Cartesiano Operadores específicos da álgebra relacional: Seleção Projeção Junção Divisão Renomeação

7 Operações

8 Esquema Relacional: ExemploEmpregado codEmp Nome Salario idade codDep 200 Pedro 3.000,00 45 001 201 Paulo 2.200,00 43 202 Maria 2.500,00 38 203 Ana 1.800,00 25 002 Projeto codProj Descricao codDep A AATOM 001 B DW espaço-temporal 002 ProjetoEmpregado codProj codEmp dataIn dataFi A 200 01/01/2007 atual 201 202 01/02/2006 18/02/2010 B 203 15/02/2008 15/02/2010 Departamento codDep descricao 001 Pesquisa 002 Desenvolvimento

9 Seleção ()‏ Retorna tuplas que satisfazem uma condiçãoAge como um filtro que matém somente as tuplas que satisfazem a condição Ex.: selecione os funcionários com salário maior que 500 O resultado: é uma relação que contém as tuplas que satisfazem a condição Possui os mesmos atributos da relação de entrada

10 Seleção ()‏ Sintaxe:  ()‏Sigma(é o símbolo que representa a seleção é uma expressão booleana que envolve literais e valores de atributos da relação CLAUSULAS: OU Nome do atributo: é um atributo de R; Operador de comparação: =, <, <=, >, >=, <> Valor constante: é um valor do domínio do atributo Podem ser ligadas pelos operadores AND, OR e NOT é o nome de uma relação ou uma expressão da álgebra relacional de onde as tuplas serão buscadas

11 Seleção () - Exemplo Buscar os dados dos empregados que estão com salário menor que 2.000,00  salario < 2000 (Empregado) Empregado codEmp Nome Salario idade codDep 200 Pedro 3.000,00 45 001 201 Paulo 2.200,00 43 202 Maria 2.500,00 38 203 Ana 1.800,00 25 002 Resultado codEmp Nome Salario idade codDep 203 Ana 1.800,00 25 002

12 Seleção () - Exemplo Buscar os dados dos empregados com salario maior que 2000 e com menos 45 anos  salario>2000 AND idade < 45 (Empregado)‏ Empregado codEmp Nome Salario idade codDep 200 Pedro 3.000,00 45 001 201 Paulo 2.200,00 43 202 Maria 2.500,00 38 203 Ana 1.800,00 25 002 Resultado codEmp Nome Salario idade codDep 201 Paulo 2.200,00 43 001 202 Maria 2.500,00 38

13 Projeção ()‏ Retorna um ou mais atributos de interesseO resultado é uma relação que contém apenas as colunas selecionadas. Elimina duplicatas * Elimina duplicatas

14 Projeção ()‏  ()‏ Sintaxe: onde: é uma lista que contém nomes de colunas de uma ou mais relações. é o nome da relação ou uma expressão da álgebra relacional de onde a lista de atributos será buscada

15  nome, idade (Empregado)‏Projeção () – Exemplo Buscar o nome e a idade de todos os empregados  nome, idade (Empregado)‏ Empregado codEmp Nome Salario idade codDep 200 Pedro 3.000,00 45 001 201 Paulo 2.200,00 43 202 Maria 2.500,00 38 203 Ana 1.800,00 25 002 Resultado Nome idade Pedro 45 Paulo 43 Maria 38 Ana 25

16 Projeção e Seleção Operadores diferentes podem ser aninhadosExemplo: Buscar o nome e o salario dos empregados com mais de 40 anos  nome, salario ( idade > 40 (Empregado)‏) Empregado codEmp Nome Salario idade codDep 200 Pedro 3.000,00 45 001 201 Paulo 2.200,00 43 202 Maria 2.500,00 38 203 Ana 1.800,00 25 002 Resultado Nome Salario Pedro 3.000,00 Paulo 2.200,00

17 Exercícios de Seleção e ProjeçãoEmpregado Departamento codEmp Nome Salario idade codDep 200 Pedro 3.000,00 45 001 201 Paulo 2.200,00 43 202 Maria 2.500,00 38 203 Ana 1.800,00 25 002 codDep descricao 001 Pesquisa 002 Desenvolvimento Projeto ProjetoEmpregado codProj codEmp dataIn dataFi A 200 01/01/2007 atual 201 202 01/02/2006 18/02/2010 B 203 15/02/2008 15/02/2010 codProj Descricao codDep A AATOM 001 B DW espaço-temporal 002 Busque todos os empregados com menos de 30 anos Busque o código dos empregados que trabalham no projeto A Selecione o nome e o salario dos empregados que trabalham no departamento 001 Busque o código do projeto e o código do empregado dos projetos em andamento em 2009 E se quisermos buscar o nome do projeto e o nome dos empregados dos projetos em andamento em 2009?

18 Operações

19 Operações - Teoria dos ConjuntosA álgebra relacional utiliza 4 operadores da teoria dos conjuntos: União, Intersecção, Diferença e Produto Cartesiano Todos os operadores utilizam ao menos DUAS relações As relações devem ser compatíveis: possuir o mesmo número de atributos o domínio da i-ésima coluna de uma relação deve ser idêntico ao domínio da i-ésima coluna da outra relação Quando os nomes dos atributos forem diferentes, adota-se a convenção de usar os nomes dos atributos da primeira relação

20 Intersecção ()‏ Retorna uma relação com as tuplas comuns a R e SNotação: R  S R S R  S

21 Intersecção () - Exemplobuscar o nome e CPF dos funcionários de Porto Alegre que estão internados como pacientes Médico (CRM, nome, idade, cidade, especialidade, #númeroA) Paciente (RG, nome, idade, cidade, doença) Funcionário (RG, nome, idade, cidade, salário)  nome, rg (Funcionario)   nome, rg (σ cidade = ‘Porto Alegre (Paciente))

22 União ()‏ Requer que as duas relações fornecidas como argumento tenham o mesmo esquema. Resulta em uma nova relação, com o mesmo esquema, cujo conjunto de linhas é a união dos conjuntos de linhas das relações dadas como argumento. Retorna a união das tuplas de duas relações R e S Eliminação automática de duplicatas Notação: R  S R S R  S 1 2 3

23 União () - Exemplo  nome, rg (Medico)   nome, rg (Paciente)buscar o nome e o CPF dos médicos e dos pacientes cadastrados no hospital Médico (CRM, rg, nome, idade, cidade, especialidade, #númeroA) Paciente (RG, nome, idade, cidade, doença)  nome, rg (Medico)   nome, rg (Paciente)

24 Diferença (-)‏ Requer que as duas relações fornecidas como argumento tenham o mesmo esquema. Resulta em uma nova relação, com o mesmo esquema, cujo conjunto de linhas é o conjunto de linhas da primeira relação menos as linhas existentes na segunda.

25 Diferença (-)‏ Retorna as tuplas presentes em R e ausentes em SNotação: R – S R S R-S

26 Diferença (-) - Exemplobuscar o número dos ambulatórios onde nenhum médico dá atendimento Médico (CRM, nome, idade, cidade, especialidade, #númeroA) Ambulatorio (numeroA, nome, andar)  numeroA (Ambulatorio) -  numeroA (Medico)‏

27 Produto Cartesiano (x)‏Retorna todas as combinações de tuplas de duas relações R e S O resultado é uma relação cujas tuplas são a combinação das tuplas das relações R e S, tomando-se uma tupla de R e concatenando-a com uma tupla de S Notação: R x S

28 Produto Cartesiano (x)‏Total de atributos do produto cartesiano = num. atributos de R + num. atributos de S Número de tuplas do produto cartesiano = num. tuplas de R x num tuplas de R

29 Produto Cartesiano (x)‏Exemplo: R S

30 Produto Cartesiano - Exemplobuscar o nome dos médicos que têm consulta marcada e as datas das suas consultas Médico (CRM, nome, idade, cidade, especialidade, #númeroA) Consulta (#CRM, #RG, data, hora)  medico.nome, consulta.data ( medico.CRM=consulta.CRM (Medico x Consulta)‏)

31 Produto Cartesiano - Exemplobuscar, para as consultas marcadas para o período da manhã (7hs-12hs), o nome do médico, o nome do paciente e a data da consulta

32 Produto Cartesiano - Exemplobuscar, para as consultas marcadas para o período da manhã (7hs-12hs), o nome do médico, o nome do paciente e a data da consulta  medico.nome, paciente.nome, consulta.data ( consulta.hora>=7 AND consulta.hora<=12) AND medico.CRM=consulta.CRM AND consulta.RG=paciente.RG (Medico x Consulta x Paciente)‏)

33 Exercícios Empregado Departamento Projeto ProjetoEmpregadocodEmp Nome Salario idade codDep 200 Pedro 3.000,00 45 001 201 Paulo 2.200,00 43 202 Maria 2.500,00 38 203 Ana 1.800,00 25 002 codDep descricao 001 Pesquisa 002 Desenvolvimento Projeto ProjetoEmpregado codProj codEmp dataIn dataFi A 200 01/01/2007 atual 201 202 01/02/2006 18/02/2010 B 203 15/02/2008 15/02/2010 codProj Descricao codDep A AATOM 001 B DW espaço-temporal 002 Selecione o nome dos empregados que trabalham no departamento de Pesquisa Selecione o nome e o salario dos empregados que trabalham no projeto AATOM Selecione o nome dos empregados e o nome dos projetos em andamento em 2009

34 Exercícios – Dado o esquema relacionalAmbulatório (númeroA, andar, capacidade) Médico (CRM, nome, idade, cidade, especialidade, #númeroA) Paciente (RG, nome, idade, cidade, doença) Consulta (#CRM, #RG, data, hora) Funcionário (RG, nome, idade, cidade, salário) 1) buscar os dados dos pacientes que estão com sarampo 2) buscar os dados dos médicos ortopedistas com mais de 40 anos 3) buscar os dados das consultas, exceto aquelas marcadas para os médicos com CRM 46 e 79 4) buscar os dados dos ambulatórios do quarto andar que ou tenham capacidade igual a 50 ou tenham número superior a 10

35 Exercícios – Dado o esquema relacionalAmbulatório (númeroA, andar, capacidade) Médico (CRM, nome, idade, cidade, especialidade, #númeroA) Paciente (RG, nome, idade, cidade, doença) Consulta (#CRM, #RG, data, hora) Funcionário (RG, nome, idade, cidade, salário) 5) buscar o nome e a especialidade de todos os médicos 6) buscar o número dos ambulatórios do terceiro andar 7) buscar o CRM dos médicos e as datas das consultas para os pacientes com RG 122 e 725 8) buscar os números dos ambulatórios, exceto aqueles do segundo e quarto andares, que suportam mais de 50 pacientes

36 Exercícios – Dado o esquema relacionalAmbulatório (númeroA, andar, capacidade) Médico (CRM, nome, idade, cidade, especialidade, #númeroA) Paciente (RG, nome, idade, cidade, doença) Consulta (#CRM, #RG, data, hora) Funcionário (RG, nome, idade, cidade, salário) 9) buscar o nome dos médicos que têm consulta marcada e as datas das suas consultas 10) buscar o número e a capacidade dos ambulatórios do quinto andar e o nome dos médicos que atendem neles 11) buscar o nome dos médicos e o nome dos seus pacientes com consulta marcada, assim como a data destas consultas 12) buscar os nomes dos médicos ortopedistas com consultas marcadas para o período da manhã (7hs-12hs) do dia 15/04/03 13) buscar os nomes dos pacientes, com consultas marcadas para os médicos João Carlos Santos ou Maria Souza, que estão com pneumonia

37 Exercícios – Dado o esquema relacionalAmbulatório (númeroA, andar, capacidade) Médico (CRM, nome, idade, cidade, especialidade, #númeroA) Paciente (RG, nome, idade, cidade, doença) Consulta (#CRM, #RG, data, hora) Funcionário (RG, nome, idade, cidade, salário) 14) buscar os nomes dos médicos e pacientes cadastrados no hospital 15) buscar os nomes e idade dos médicos, pacientes e funcionários que residem em Florianópolis 16) buscar os nomes e RGs dos funcionários que recebem salários abaixo de R$ 300,00 e que não estão internados como pacientes 17) buscar os números dos ambulatórios onde nenhum médico dá atendimento 18) buscar os nomes e RGs dos funcionários que estão internados como pacientes

38 Exercícios buscar os dados dos ambulatórios do quarto andar. Estes ambulatórios devem ter capacidade igual a 50 ou o número do ambulatório deve ser superior a 10 buscar os números dos ambulatórios que os médicos psiquiatras atendem buscar o nome e o salário dos funcionários de Florianopolis e Sao José que estão internados como pacientes e têm consulta marcada com ortopedistas buscar o nome dos funcionários que não são pacientes Buscar o nome dos funcionários que são pacientes