ROTEIRO. Operações da Álgebra Relacional Operadores Binários da Álgebra Relacional Produto Cartesiano

BANCO DE DADOS

Universidade do Estado de Santa Catarina Centro de Ciências Tecnológicas

Departamento de Ciência da Computação Prof. Alexandre Veloso de Matos

alexandre.matos@udesc.br

ROTEIRO

Operações da Álgebra Relacional

Operadores Binários da Álgebra Relacional

Produto Cartesiano

Operadores Especiais da Álgebra Relacional

Atribuição

Introdução a SQL [Parte 2]

ÁLGEBRA RELACIONAL

Operações que permitem ao usuário especificar solicitações de recuperação de dados, cujo resultado será uma nova relação.

Uma sequência de operações que utilizem operadores algébricos forma

uma expressão de álgebra relacional.

OPERAÇÕES DA ÁLGEBRA RELACIONAL

Operações da AR desenvolvidas especificamente para os bancos de dados relacionais:

Seleção.

Projeção.

Produto Cartesiano.

Junção.

Renomear.

União.

...

OPERADORES NA ÁLGEBRA RELACIONAL

Classificação dos operadores:

Fundamentais:

Unários: seleção, projeção;

Binários: produto cartesiano, união e diferença.

Derivados:

Binários: intersecção, junção e divisão.

Especiais

Renomeação (unário) e atribuição Operador de alteração (unário)

PRODUTO CARTESIANO

Retorna todas as combinações de tuplas de duas relações R

e R

; Grau do resultado (número de colunas):

grau(R

) + grau(R

)

Cardinalidade do resultado (número de linhas):

cardinalidade(R

) * cardinalidade(R

)

Notação: relação 1 Χ relação 2

EXEMPLO

Exemplo:

x y z

1 1 1

2 2 2

3 3 3

w y

1 1

2 2

R1 R2

2

1 R

R Χ

x R1.y z w R2.y

1 1 1 1 1

1 1 1 2 2

2 2 2 1 1

2 2 2 2 2

3 3 3 1 1

3 3 3 2 2

2

1 R

R Χ

MAPEAMENTO PARA SQL

π a1, ..., an ( σ _c (tab ₁ X ... X tab _m ))

SELECT a

, ..., a

FROM tab

, ..., tab

WHERE c

EXEMPLOS

Considerando as seguintes tabelas:

Medico(#cdMedico, nmMedico, dtAdm, dtNasc, deEspecializacao) Convenio(#cdConvenio, deRazaoSocial, nuTelefone, deEndereco)

Paciente(#cdPaciente, nmPaciente, deEndPaciente, deTelPaciente, dtNasc, &cdPlanoSaude)

Consulta(#cdConsulta, &cdMedico, &cdPaciente, deConsulta, dtConsulta, hrConsulta, deMedicacao, deDiagnostico, nuValor)

Medico_Convenio(#&cdMedico, #&cdConvenio) Laboratorio(#cdLabo, deLabo)

Plano_Saude(#cdPlanoSaude, dePlanoSaude)

Onde: “cd” – código, “dt” – data, “de” – descrição, “hr” – hora, “nm” – nome, “nu” - número

EXEMPLOS

Álgebra SQL

(paciente X consulta)

π

(

σ

(paciente X consulta) )

∧paciente.cdPaciente=consulta.cdPaciente

SELECT nmPaciente, dtConsulta FROM paciente, consulta

WHERE hrConsulta > “12:00”

AND paciente.cdPaciente = consulta.cdPaciente

π

(

σ

(( ρ

(medico)) X ( ρ

(medico))))

SELECT m2.nmMedico

FROM medico m1, medico m2 WHERE m1.nmMedico = “João”

AND m1.deEspecializacao = m2.especializacao

ATRIBUIÇÃO

Armazena o resultado de uma expressão algébrica em uma variável de relação;

Permite o processamento de uma consulta por etapas.

Notação: A

g l b é i r a c x e

r p s e ã s o l e o n

e m a V i r v á

←

EXEMPLO

Exemplo: Produto cartesiano de médicos e consultas

)

,_dtConsulta

( Consulta

cdMedico

π

R ←

1

)

,_nmMedico

( Medico

cdMedico

π

R ←

2

)) 2 1

(

(

,_{dtConsulta R cdMedico R cdMedico}

R R

nmMedico

Χ

← π σ

e r

p s

INTRODUÇÃO A SQL

[PARTE 2]

SQL: CONSULTAS BÁSICAS

Consultar dados de uma tabela:

SELECT lista_atributos FROM tabela

[WHERE condição]

Exemplos:

SELECT * FROM paciente;

SELECT nmPaciente, dtNasc FROM paciente

WHERE cdPlanoSaude = 1;

SQL: SELECT

Facilidades para projeção de informações:

Não há eliminação de duplicatas no SELECT;

Retorno de valores calculados:

Uso de operadores aritméticos (+,-,*,/).

SQL: SELECT

Facilidades para projeção de informações:

Invocação de funções de agregação

COUNT: contador de ocorrências [de um atributo];

MAX / MIN: valores máximo / mínimo de um atributo;

SUM: somador de valores de um atributo;

AVG: média de valores de um atributo.

SQL: SELECT

Eliminação de duplicatas:

SELECT [DISTINCT] lista_atributos ...

Exemplo:

Buscar as especializações dos médicos

SELECT DISTINCT deEspecializacao

FROM medico

SQL: SELECT

Retorno de valores calculados, exemplos:

Qual o salário líquido dos médicos (desc. 10%)?

SELECT cdMedico,nuSalario – (nuSalario * 0.1) AS liquido

FROM medico

SQL: SELECT

Função COUNT, exemplos:

Informar o total de médicos geriatras:

SELECT COUNT(*) AS total_geriatras FROM medico

WHERE deEspecializacao = “Geriatra”

Quantos pacientes tem plano de saúde:

SELECT COUNT(cdPlanoSaude) FROM paciente

não conta nulos

SQL: SELECT

Função SUM, exemplo:

Informar o valor total das consultas do médico 1:

SELECT SUM(nuValor) FROM consulta

WHERE cdMedico = 1;

SQL: SELECT

Função AVG, exemplo:

Informar o valor médio das consultas dos médicos:

SELECT AVG(nuValor)

FROM consulta

SQL: SELECT

Função MAX/MIN, exemplo:

Informar o menor e o maior valor de consulta:

SELECT MIN(nuValor) AS minimo,

MAX(nuValor) AS maximo

FROM consulta

SQL: SELECT

Funções de Agregação com DISTINCT:

Valores duplicados não são computados;

Exemplos:

SELECT COUNT(DISTINCT cdGerente) FROM funcionario

SELECT AVG(DISTINCT nuValor)

FROM consulta

SQL: CLÁUSULA WHERE

Facilidades para seleção de dados:

Busca por padrões:

Cláusula [NOT] LIKE

Teste de existência de valores nulos:

Cláusula IS [NOT] NULL

Busca por intervalos de valores:

Cláusula [NOT] BETWEEN valor1 AND valor2

Teste de pertinência elemento-conjunto:

Cláusula [NOT] IN

SQL: CLÁUSULA WHERE

Busca por padrões:

where atributo like “padrão”

% : casa com qualquer cadeia de caracteres

‘_’ : casa com um único caractere

Exemplo:

Buscar nome e data de nascimento dos médicos com inicial M:

SELECT nmMedico, dtNasc FROM medico

WHERE nmMedico LIKE ‘M%’

SQL: CLÁUSULA WHERE

Exemplo:

Buscar nomes e endereço de pacientes cujo endereço termine com ‘H’ ou ‘B’:

SELECT nmPaciente, deEndPaciente FROM paciente

WHERE deEndPaciente LIKE “%H”

OR deEndPaciente LIKE “%B”

Observações:

Não é possível testar padrões em atributos DATETIME.

SQL: CLÁUSULA WHERE

Teste de valores nulos, exemplo:

Buscar nome dos pacientes que não possuem plano de saúde:

SELECT nmPaciente FROM paciente

WHERE cdPlanoSaude IS NULL

SQL: CLÁUSULA WHERE

Busca por intervalos de valores, exemplo:

Buscar as consultas com datas entre os anos de 2005 e 2006:

SELECT *

FROM consulta

WHERE dtConsulta BETWEEN “2005-01-01”

AND “2006-12-31”

SQL: CLÁUSULA WHERE

Teste de pertinência elemento-conjunto:

Exemplo:

Buscar os dados dos pacientes que moram na “Rua I” e que tenham plano de sáude com código 1 ou 2.

SELECT *

FROM paciente

WHERE deEndPaciente = “Rua I”

AND cdPlanoSaude IN(1,2)

EXERCÍCIOS DE SQL

1) Crie um BD com nome clinica.

2) Crie as seguintes tabelas neste BD:

Medico(#cdMedico, nmMedico, dtAdm, dtNasc, deEspecializacao) Convenio(#cdConvenio, deRazaoSocial, nuTelefone, deEndereco)

Paciente(#cdPaciente, nmPaciente, deEndPaciente, deTelPaciente, dtNasc,

&cdPlanoSaude)

Consulta(#cdConsulta, &cdMedico, &cdPaciente, deConsulta, dtConsulta, hrConsulta, deMedicacao, deDiagnostico, nuValor)

Medico_Convenio(#&cdMedico, #&cdConvenio) Laboratorio(#cdLabo, deLabo)

Plano_Saude(#cdPlanoSaude, dePlanoSaude)

EXERCÍCIOS DE SQL – DICIONÁRIO DE DADOS

Atributo Domínio Tamanho Restrição

cdMedico Numérico Não Nulo

Chave Primária

nmMedico Texto 50 Não Nulo

dtAdm Data Não Nulo

dtNasc Data

deEspecialização Texto 50 Não Nulo

Médico

Atributo Domínio Tamanho Restrição

cdConvenio Numérico Não Nulo

Chave Primária

deRazaoSocial Texto 30 Não Nulo

nuTelefone Texto 12 Não Nulo

deEndereco Texto 100 Não Nulo

Convênio

EXERCÍCIOS DE SQL – DICIONÁRIO DE DADOS

Atributo Domínio Tamanho Restrição

cdPaciente Numérico Não Nulo

Chave Primária

nmPaciente Texto 50 Não Nulo

deEndPaciente Texto 50 Não Nulo

deTelPaciente Texto 10 Não Nulo

dtNascimento Data Não Nulo

cdPlanoSaude Numérico Não Nulo

Paciente

EXERCÍCIOS DE SQL – DICIONÁRIO DE DADOS

Atributo Domínio Tamanho Restrição

cdConsulta Numérico Não Nulo

Chave Primária Auto Incrementado

cdMedico Numérico Não Nulo

cdPaciente Numérico Não Nulo

deConsulta Texto 50 Não Nulo

dtConsulta Data

hrConsulta Horario

deMedicação Texto 100

deDiagnostico Texto 200

nuValor Numérico 10,2

Consulta

EXERCÍCIOS DE SQL – DICIONÁRIO DE DADOS

Atributo Domínio Tamanho Restrição

cdMedico Numérico Não Nulo

Chave Primária

cdConvenio Numérico Não Nulo

Chave Primária

Médico_Convenio

Atributo Domínio Tamanho Restrição

cdLabo Numérico Não Nulo

Chave Primária

deLabo Texto 50 Não Nulo

Laboratorio

Atributo Domínio Tamanho Restrição

cdPlanoSaude Numérico Não Nulo

Chave Primária

dePlanoSaude Texto 50 Não Nulo

Plano_Saude

EXERCÍCIOS DE SQL

Popule as tabelas, conforme os seguintes dados:

cdMedico nmMedico dtAdm dtNasc deEspecializacao

1 Pedro Antônio 10/10/1999 09/09/1955 Cirurgião Plástico

2 Maria da Luz 12/01/1987 01/01/1945 Ginecologista

3 Antonia Menezes 10/12/1979 29/05/1935 Geriatra

Medico

cdPlanoSaude dePlanoSaude

1 PlanoSaude 1

2 PlanoSaude 2

3 PlanoSaude 3

Plano_Saude

cdConvenio deRazaoSocial nuTelefone deEndereco cdLabo

1 Sul América Seguros 34556778 Rua H 1

2 Unimed Seguros 34545645 Rua B 2

3 Bradesco Seguros 45324524 Rua Y 3

Convenio

cdPaciente nmPaciente deEndPaciente deTelPaciente dtNasc cdPlanoSaude

1 José Santos Rua H 32223333 11/11/2000 NULL

2 Marcos Freitas Rua B 32224444 22/10/1956 1

3 Alberta Daltro Rua I 32225555 01/03/1999 NULL

4 Antonio dos

Santos

Rua I NULL NULL 2

Paciente

cdMedico cdConvenio

1 1

2 2

3 2

1 3

1 2

Medico_Convenio

cdLabo deLabo 1 Laboratório 1 2 Laboratório 2 3 Laboratório 3

Laboratorio

EXERCÍCIOS DE SQL

cdConsulta cdMedico cdPaciente deConsulta dtConsulta hrConsulta deMedicacao deDiagnostico nuValor

1 1 1 Consulta Básica 11/11/2005 14:00 Loratadina Inflamação na

garganta

143

2 2 3 Consulta

Especialista

01/10/1997 16:00 AAS Gripe 255

3 3 2 Consulta

Especialista

21/12/2006 13:00 Tylenol Febre 156

4 1 1 Consulta

Especialista

01/10/2007 15:00 Veroxitatina Amidalite 103

5 1 2 Consulta Básica 21/12/1965 13:00 Tylenol Febre 83

6 2 1 Consulta Básica 12/03/1998 16:00 Novalgina Febre 115

7 1 1 Consulta Básica 15/07/2011 15:00 150

8 2 1 Consulta Básica 18/03/2011 20:00 230

9 3 2 Consulta

Especialista

13/05/2011 19:30 100

10 1 3 Consulta Básica 20/07/2011 17:00 200

11 1 2 Consulta Básica 20/07/2011 18:00 300

Consulta

EXERCÍCIOS DE FIXAÇÃO - SQL

1) Selecione as consultas que tenham valor superior a 100,00 reais.

2) Buscar o nome e o endereço dos pacientes que não residem na Rua I.

3) Mostre todos os médicos que não preencheram a data de nascimento.

4) Buscar o nome dos médicos que têm consulta marcada e as datas das suas consultas.

5) Qual o total de convênios que cada médico atende?

6) Buscar os códigos dos pacientes que tiveram como indicação de medicação Tylenol, Veroxitatina ou AAS.

Envie os scripts SQL das consultas (até 06/05) para alexandre.matos@udesc.br