MDADOS-03 Algebra Relacional v20121008

ÁLGEBRA RELACIONAL BÁSICA

Curso: Modelagem de DadosProf. Marcelo Lucas

25/06/2012

Álgebra Relacional

Tópicos / Agenda

� Definições do Modelo Relacional� Relações e Tuplas

� Atributos e Chaves

� Álgebra Relacional

� Descrição dos Operadores�Operações Monádicas

�Operações Diádicas

Livro de Referência

� ELMASRI, R.; NAVATHE, S.B. (2011). Sistemas de Banco de Dados. 6ª Ed.

� Capítulo sobre Modelo de Dados Relacional

� Capítulo sobre Álgebra relacional

ELMASRI, R.; NAVATHE, S.B. (2011). Capítulo sobre Modelo de Dados Relacional

ELMASRI, R.; NAVATHE, S.B. (2011). Capítulo sobre Algebra Relacional

Relações e Tuplas

Definições do Modelo Relacional

Tupla

{(A1,D1,V1) ... (An,Dn,Vn)} é uma Tupla� Ai (i=1...n) Nomes dos Atributos (distintos)

� Di (i=1...n) Domínios dos Atributos

� Vi (i=1...n) Valores para os atributos

{(A1,D1) ... (An,Dn)} são Atributos (distintos)

Ex. Tupla: CPF (char) Nome (char) Telefone (char) UF (c) Salario (#)

333444555-88 Joaquim 21-88997766 RJ 7000

Esquema

� É o conjunto: {(A1,D1) ... (An,Dn)}, onde� Ai (i=1...n) Nomes DISTINTOS dos Atributos

� Di (i=1...n) Domínios dos Atributos (não precisam ser distintos)

� (Ai, Di); i=1,...,n; Atributos

Ex. Esquema:

CPF (char) Nome (char) Telefone (char) UF (c) Salario (#)

Relação

É um Conjunto de Tuplas distintas, respeitando um Esquema de Relação

Ex. Relação R =

CPF (char) Nome (char) Telefone (char) UF (c) Salario (#)

333444555-88 Joaquim 21-88997766 RJ 7000

222444555-77 Maria 21-77889900 RJ 7500

111222999-66 José 21-66554433 DF 5000

Relações x Conjuntos

� Numa Relação todos os Elementos devem respeitar o Esquema da Relação (Homogeneidade de Elementos)

� Num Conjunto é possível Elementos de diversos Tipos. Não há uma definição de esquema (permite Heterogeneidade de Elementos)

Propriedades das Relação

� Cada TUPLA possui um único valor para cada atributo.

� Cada TUPLA é única na RELAÇÃO.

� Não existe ordenação de TUPLAs dentro de uma RELAÇÃO.

� Não existe ordenação de atributos numa TUPLA. Porém a ordenação pode ser importante em algumas formas adotadas para a representação de TUPLAs.

Relação x Tabela

� Uma Relação pode ser visualizada como uma estrutura de Tabela.

� Em outra palavras, uma Tabela é uma representação visual de uma Relação.

CPF Nome Telefone UF Salario

333444555-88 Joaquim 21-88997766 RJ 7000

222444555-77 Maria 21-77889900 RJ 7500

777222000-66 John DF 5000

777222000-66 John DF 5000

Relação x Tabela

CPF (char) Nome (char) Telefone (char) UF (c) Salario (#)

333444555-88 Joaquim 21-88997766 RJ 7000

CPF (char) Nome (char) Telefone (char) UF (c) Salario (#)

222444555-77 Maria 21-77889900 RJ 7500

CPF (char) Nome (char) Telefone (char) UF (c) Salario (#)

111222999-66 José 21-66554433 DF 5000

LINHA

COLUNA

TUPLAS RELAÇÃO

TABELA

NULL

CABEÇALHO

DUPLICADAS

TIPO OU CABEÇALHODA RELAÇÃO

CPF (char) Nome (char) Telefone (char) UF (c) Salario (#)

NULL

Atributos Espaciais em uma Relação

Definições do Modelo Relacional

Atributos Especiais em uma Relação

� Chave � Pode ser composta de um ou mais Atributos. Uma Chave é um conjunto de Atributos em uma relação. Geralmente o uso do termo Chave isoladamente se refere a uma Chave Primária ou uma Chave Candidata da Relação.

� Chaves Candidatas � Identificadores únicos de uma Tupla.� A chave candidata distingue as Tuplas numa Relação, ou seja, identifica unicamente uma

Tupla na Relação.

� Uma Chave Candidata não possui atributos supérfluos, em outras palavras: possui a quantidade mínima de Atributos necessária para a identificação unívoca.

� Chave Primária (Primary Key) � É uma Chave Candidata escolhida para ser utilizada como o identificador principal da Tupla.

� Chaves Secundárias (Alternate Key) � Demais Chaves Candidatas.

Atributos Especiais em uma Relação

� Superchave

� Chaves Candidatas com, ou sem, atributos supérfluos.

� Note que a Chave Candidata NÃO aceita atributos supérfluos enquanto a Superchave aceita.

Atributos Especiais em uma Relação

� Chave Estrangeira (Foreign Key) � Chave de Referência. É o mecanismo utilizado para se implementar Relacionamentos entre Tuplas no modelo relacional.

� A Chave Estrangeira em uma Tupla/Relação é a Chave Primária (ou Candidata) de uma segunda Tupla/Relação a qual se relaciona.

Atributos e Chaves

� Exemplo de 2 Relações: Pessoa

Departamento

Matricula (PK)cpf

nome_pessoa

cod_departamento (FK)nome_ruanum_enderecocompl_enderecobairrocepcidadeRG

cod_departamento (PK)nome_departamentocod_localidade (FK)

Definições

Álgebra Relacional

Álgebra Relacional

� É um conjunto básico de operações sobre relações.

� O resultado de uma operação sobre uma ou mais relações gera uma outra relação

Álgebra Relacional Básica

� Operações monádicas primitivas� Seleção (σσσσ)

� Projeção (ππππ)

� Renomeação (ρ)

� Operações diádicas primitivas� União (U)

� Diferença (-)

� Produto Cartesiano (XXXX)

� Operações diádicas adicionais� Intersecção (∩)

� Junção (⋈⋈⋈⋈)

� Divisão (÷)

Letras Gregas

Operações Monádicas Primitivas

Seleção

� Seleção (ou Restrição) é a operação usada para selecionar um subconjunto de tuplas de uma relação R que satisfaça uma condiçãode seleção.

� É representada por:

σσσσ<condição> (R) ou σσσσφ (R)

� φ = <condição> :� Onde <condição> compara atributos e literais com o uso dos operadores:� De comparação: =, <>, <, >, <=, >=� Lógicos: and (∧), or (∨), not (¬)

Seleção

� Representação gráfica�Obtenção de um subconjunto horizontal de uma relação R

tuplas que

satisfazem

uma condição

BD exemplo

Cód. Nome Sexo Data Admis. Salário Depto.

10 Visconde M 01/02/1983 R$ 1230,00 3

20 Pedrinho M 29/07/1990 R$ 867,00 3

30 Dona Benta F 30/11/1992 R$ 2560,00 1

40 Emília F 22/02/1998 R$ 1170,00 2

50 Rabicó M 08/09/2000 R$ 2300,00 1

CódDepto. Nome Depto.

1 Administração

2 Contabilidade

3 Informática

Funcionário

Departamento

Seleção

� Exemplo 1� Selecionar todos os funcionários do sexo feminino

σσσσSexo = ‘F’ (Funcionário)

Cód. Nome Sexo Data Admis. Salário Depto.

30 Dona Benta F 30/11/1992 R$ 2560,00 1

40 Emília F 22/02/1998 R$ 1170,00 2

Seleção

� Exemplo 2� Selecionar todos os funcionários do sexo masculino que ganham mais de R$ 1000,00

σσσσSexo = ‘M’ ∧∧∧∧ Salário > 1000 (Funcionário)

Cód. Nome Sexo Data Admis. Salário Depto.

10 Visconde M 01/02/1983 R$ 1230,00 3

50 Rabicó M 08/09/2000 R$ 2300,00 1

Atribuição

� Criação de uma nova Relação a partir do resultado de uma operação

S σσσσ<condição> (R)

� : Operador de atribuição

� S : Nova relação criada.

� O resultado da operação σσσσ<condição> (R) é atribuído à nova Relação S

Projeção

� É a operação usada para selecionar determinadas colunas de uma relação R.

� Como o resultado será também uma relação, quaisquer eventuais tuplas em duplicidade são eliminadas. (Isto deve ser observado para qualquer operação).

� É representada como:

ππππ<lista de atributos> (R)

Projeção

� Representação gráfica�Obtenção de um subconjunto vertical de uma relação R

colunas selecionadas

Projeção

� Exemplo� Listar o nome, o sexo e o salário de todos os funcionários

ππππNome, Sexo, Salário (Funcionário)

Nome Sexo Salário

Visconde M R$ 1230,00

Pedrinho M R$ 867,00

Dona Benta F R$ 2560,00

Emília F R$ 1170,00

Rabicó M R$ 2300,00

Seleção e Projeção

� As operações podem ser utilizadas em conjunto

� Exemplo� Selecionar o nome e o salário de todos os funcionários que ganham mais de R$ 2000,00

ππππNome, Salário (σσσσSalário > 2000 (Funcionário))

Nome Salário

Dona Benta R$ 2560,00

Rabicó R$ 2300,00

Renomeação

� Objetivo� Renomear o nome de uma Relação

� Renomear os nomes de atributos

� Sintaxes:

ρ S (b1, b2, b3,... bn) (ππππa1, a2,... an (R))

ρ b1, b2, b3,... bn (ππππa1, a2,... an (R))

ρ S (R)

Renomeação

� Formas equivalente de renomear a RELAÇÃO:

ρ S (b1, b2, b3,... bn) (ππππa1, a2,... an (R))

S ρ b1, b2, b3,... bn (ππππa1, a2,... an (R))

ρ S (R)

S R

Renomear

� Exemplo� Listar o nome, o sexo e o salário de todos os funcionários, substituindo o nome das colunas para Alcunha, S e Vencimento.

S ρ Alcunha, S, Vencimento (ππππNome, Sexo, Salário (Funcionário))

Alcunha S Vencimento

Visconde M R$ 1230,00

Pedrinho M R$ 867,00

Dona Benta F R$ 2560,00

Emília F R$ 1170,00

Rabicó M R$ 2300,00

Exercícios

� Listar todas as funcionárias (sexo feminino).

� Listar todos os funcionários da administração com seus salários.

Operações Diádicas Primitivas

União

� R ∪∪∪∪ S

� A união de duas relações R e S, com os mesmos tipos, é o conjunto de todas as tuplas pertencentes à relação R ou à relação S ou a ambas.

� Representação gráfica

BD exemplo

Cód. Nome Sexo Data Admis. Salário Depto.

10 Visconde M 01/02/1983 R$ 1230,00 3

20 Pedrinho M 29/07/1990 R$ 867,00 3

30 Dona Benta F 30/11/1992 R$ 2560,00 1

40 Emília F 22/02/1998 R$ 1170,00 2

50 Rabicó M 08/09/2000 R$ 2300,00 1

Funcionário

Aluno

Cód. Nome Sexo

1 Visconde M

2 Dona Benta F

3 Rabicó M

4 Cuca F

União

� Exemplo�Obter os nomes e o sexo de todos os funcionários e alunos

Nome Sexo

Visconde M

Pedrinho M

Dona Benta F

Emília F

Rabicó M

Cuca F

ππππNome, Sexo (Funcionário)

∪∪∪∪

ππππNome, Sexo (Aluno)

Resultado

Diferença

� R - S� A diferença de duas relações R e S, dos mesmos tipos, é o conjunto de todas as tuplas pertencentes à relação R e não pertencentes à relação S.

� Representação gráfica

Diferença

� Exemplo�Obter os nomes e o sexo de todos os funcionários que não são alunos

S ππππNome, Sexo (Funcionário) – ππππNome, Sexo (Aluno)

Nome Sexo

Pedrinho M

Emília F

Produto Cartesiano

� R ×××× S

�O produto cartesiano de duas relações R e S, quaisquer, é o uma relação T 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 cada tupla de S.

� Representação gráfica

A

B

C

Y

Zx

A Y

A Z

B Y

B Z

C Y

C Z

Produto Cartesiano

� Exemplo� Obter, para cada funcionário, uma lista de todos os departamentos.

Nome Sexo Data Adm. Nome Depto.

Vinconde M 01/02/1983 Administração

Visconde M 01/02/1983 Contabilidade

Visconde M 01/02/1983 Informática

(continua...)

ππππNome, Sexo, Data Admis (Funcionário) X ππππNome Depto (Departamento)

OU

ππππNome, Sexo, Data Admis, Nome Depto (Funcionário X Departamento)

Produto Cartesiano

Nome Sexo Data Adm. Nome Depto.

Pedrinho M 29/07/1990 Administração

Pedrinho M 29/07/1990 Contabilidade

Pedrinho M 29/07/1990 Informática

Dona Benta F 30/11/1992 Administração

Dona Benta F 30/11/1992 Contabilidade

Dona Benta F 30/11/1992 Informática

Emília F 22/02/1998 Administração

Emília F 22/02/1998 Contabilidade

Emília F 22/02/1998 Informática

Rabicó M 08/09/2000 Administração

Rabicó M 08/09/2000 Contabilidade

Rabicó M 08/09/2000 Informática

Exercícios

� Listar todos os funcionários da administração com seus salários (revisitado – produto cartesiano).

Operações Diádicas Adicionais

Junção

� É utilizada para combinar tuplas relacionadas de duas relações em uma única tupla

É representada como:

R ⋈⋈⋈⋈<condição de junção> S

Ou, simplesmente (Junção Natural):

R ⋈ ⋈ ⋈ ⋈ S

Junção

� Exemplo

� Listar todos os funcionários e o nome do departamento em que cada um deles trabalha

Funcionário ⋈⋈⋈⋈Depto. = CódDepto. Departamento

Nome Sexo Data Admis. Depto. CódDepto. Nome Depto.

Visconde M 01/02/1983 3 3 Informática

Pedrinho M 29/07/1990 3 3 Informática

Dona Benta F 30/11/1992 1 1 Administração

Emília F 22/02/1998 2 2 Contabilidade

Rabicó M 08/09/2000 1 1 Administração

Exercício

� Expresse a junção entre as relações R e S através de funções primitivas apenas.

Junção

R ⋈⋈⋈⋈<condição de junção> S =

σσσσ<condição> (R X S)

Exercícios

� Listar todos os funcionários da administração com seus salários (revisitado – junção).

� R ∩∩∩∩ S

� A intersecção de duas relações R e S, dos mesmos tipos, é o conjunto de todas as tuplas pertencentes à relação R e à relação S.

� Representação gráfica

Intersecção

Intersecção

� Exemplo� Obter o nome e o sexo de

todos os funcionários que são alunos

ππππNome (Funcionário)

∩∩∩∩

ππππNome (Aluno)

Nome Sexo

Visconde M

Dona Benta F

Rabicó M

Exercício

� Expresse a interseção entre as relações R e S através de funções primitivas apenas.

R ∩∩∩∩ S =

R - ( R - S)

Intersecção

� R ÷ S

� A divisão da relação R pela relação S, sendo S uma projeção de R, é o conjunto de tuplas formadas pelos atributos de R que não estão em S, desde que existam em R todas as suas combinações com as tuplas de S.

� Representação gráfica:

Divisão

A

B

C

Y

Z÷

A Y

A Z

B Y

B Z

B W

C Y

C Z

D Z

E W

BD exemplo

Cód. Pizzaria Pizza Tamanho

10 DiLidia Margerita G

10 DiLidia Margerita M

10 DiLidia Calabresa G

10 DiLidia Calabresa M

10 DiLidia Portuguesa G

10 DiLidia Portuguesa M

20 MisterPizza Peperoni G

20 MisterPizza Peperoni M

30 Dominos Margerita M

30 Dominos Calabresa G

40 Pizza Hut Margerita M

40 Pizza Hut Calabresa M

CardapioPizzaria

Pedido

Pizza Tamanho

Margerita M

Calabresa M

Divisão

Cód. Pizzaria

10 DiLidia

40 Pizza Hut

Resultado

CardapioPizzaria ÷ Pedido

Exercício (Dificuldade Alta)

� Expresse a divisão entre as relações R e S através de funções primitivas apenas.

R ÷ S =

ππππ a(R)-a(S) R – ππππa(R)-a(S)(

(ππππa(R)-a(S) R x S) – R

)

Obs.:

a(R)-a(S) – atributos de R que não são atributos de S

Divisão

Exercício 1

a) Quais os nomes dos empregados que ganham um salário menor que “2.000,00”.

b) Quais os nomes dos departamentos que ganham salário até “1.500,00”.c) Quais os nomes dos empregados que trabalham no departamento “D3”.d) Quais os nomes de empregado que trabalham no departamento

“Contabilidade”.e) Quais são os salários do departamento “Contabilidade”.f) Quais os nomes dos departamentos que não possuem empregados.

Funcionários

E # Nome D # Salário

E1 José D1 1.000,00E2 Maria D1 2.000,00E3 João D2 1.500,00E4 Sílvia D3 1.500,00E5 João D1 1.500,00

Departamentos

D# NomeDepto

D1 ContabilidadeD2 AdministraçãoD3 InformáticaD4 RH

Exercício 2

ATENDIMENTOCD_PAC DIA_ATEND DIAG

5 09/04/1996 Lombalgia crônica. Ativ. com esforço - coziinheira. limitação de mobilização. Fez tratº. 2 09/04/1996 RX Acentuada degeneração com osteofitos laterais Diminuição de espaços artic. L4-L52 07/05/1996 Terminou fisioterapia. Melhora Subjativa. Manter mais 1 série.4 25/06/1996 Terminou 20ª sessão de fisioterapia. S/ queixas. Cont. exerc. dom. DO1 26/06/1997 Dor lombar há 3 dias após esforço em má postura. Dor em ombroE. com

10 03/07/1997 Sesamoidite pé E há 04 dias com acentuado edema MF hallux e dificuldade para apoio3 08/07/1997 Melhora subjetiva- manter CUL rever 1 sem. se nec fisioterapia6 03/04/1997 Ombro doloroso a D. com queda do mesmo . Má postura para ativ. domésticas.Dor em face7 10/07/1997 Epicondilite cotovelo Dir. com 01 mes evolução.Ativ. sedentária limitação de PPS e Extensão2 10/07/1997 Tendinite dos extensores de 2º e 3º pododactilos do pé E. fez tratº. com AINE por o3

PACIENTECD_PAC NM_PAC DT_NASC CONV_PAC

1 Maureen Annelise Molinari 19/08/1980 012 David Lessa Chaves 02/02/1979 013 Marlene C. Florêncio 02/01/1949 034 Rita de Fátima S. Costa 07/10/1955 045 Solange A. Andrade 10/08/1969 056 Valéria do Rocio Pinheiro 29/11/1974 117 Sara Carmelina O.Viana 25/01/1973 108 José Lucchesi 06/12/1916 109 Arielton da Silva Pinto 20/05/1965 05

10 Maria Aparecida Azeredo 27/08/1939 01

CONVENIOCOD_CONV DESC_CONVENIO

01 Unimed - código 1134702 FUNBEP - código 7141-203 BAMERINDUS04 GRALHA – AZUL05 CASSI - código – 21.447-706 FUNCEF07 TELEPAR - código 0033508 PETROBRAS09 BANCO CENTRAL10 Particular11 AMBEP

Exercício 2 (perguntas)

a) Qual é o nome convenio da paciente “Rita de Fátima S. Costa”?

b) Quais pacientes pertences ao convenio “Unimed –código 11347” ?

c) Quais os nomes dos pacientes que tiveram atendimento no dia “09/04/1996” ?

d) Quais os nomes dos convênios não têm pacientes?

Exercício 2 (perguntas)

e) Qual comando gerou o seguinte resultado?NM_PAC DESC_CONVENIO DIAG

Filomena Ivanski Unimed - código 11347 Lombalgia cronica com sensação ...Sonia Maria Caetano Paraíso Mastek Unimed - código 11347 Luxação congênita de patelar ...João Mastek Neto Unimed - código 11347 Parestesia de Nervo Ciático Popliteo ...Angela Maria de Lima Casa Grande Unimed - código 11347 Dores difusas em ambos os joelhos s...Ricardo Pinheiro Pinto CASSI - código - 21.447-7 Lombalgia em transição de col. TL. com ...Mirian Lais Ferreira da Costa Hauari CASSI - código - 21.447-7 Processo alérgico em IFD de ivdedo mão ...Erdema Fatima Carrara Lafratta CASSI - código - 21.447-7 Portadora de 4º dedo mão Esq. em gatilho ...Erdema Fatima Carrara Lafratta CASSI - código - 21.447-7 Epicondilite de cotovelo D. com de evol...Moaré de Miranda e Silva CASSI - código - 21.447-7 Há 2 sem. entorse de joelho D. em futebol...

Exercício 3 – Relações:

Exercício 3 (perguntas)

� 1. Obter o nome completo de todos os funcionários;

� 2. Identificar todos os funcionários do sexo masculino;

� 3. Obter o nome completo e a data de admissão de todos os funcionários do sexo feminino;

� 4. Obter o nome completo, a data de admissão e o salário de todos os funcionários;

� 5. Descobrir o nome completo, a data de admissão e o salário de todos os funcionários do sexo masculino.

� 6. Todos os funcionários do departamento ‘D1’.

� 7. O nome e a matrícula de todos os funcionários do departamento ‘D1’.

� 8. A matrícula e o nome do respectivo departamento de todos os funcionários.

� 9. O nome dos funcionários que ganham mais de $500.

� 10. O ramal do funcionário ‘ANA SILVEIRA’.

� 11. Os nomes de todos os funcionários com cargo de ‘MECANICO’.

� 12. Os nomes de todos os funcionários que trabalham no mesmo departamento que ‘JOSE NOGUEIRA’.

� 13. Os nomes dos departamentos que possuem tanto funcionários como funcionárias(possuem funcionários de ambos os sexos).

ÁLGEBRA RELACIONAL ESTENDIDA ECÁLCULO RELACIONAL

(TÓPICOS EXTRAS, CASO O ALUNO TENHA INTERESSE EM SE APROFUNDAR - EXTRA)

Curso: Modelagem de DadosProf. Marcelo Lucas

25/06/2012

Operações Estendidas

Álgebra Relacional Estendida

� Operações estendidas

� Joins

� Left Outer Join (⟕⟕⟕⟕)

� Right Outer Join (⟖⟖⟖⟖)

� Full Outer Join (⟗⟗⟗⟗)

� Anti Join (▷▷▷▷)� Semi Join (⋉⋉⋉⋉) (⋊⋊⋊⋊)

� Operações estendidas primitivas

� Agregador (G) (SUM, AVG, COUNT, ...)

� Extensão (EXTEND)

Considerações Adicionais

Álgebra Relacional

� Outras características:� Linguagem procedural.

� Várias formas de obter o mesmo resultado porém com custos de processamento diferentes.

Álgebra Relacional X Cálculo Relacional

� A Álgebra Relacional e o Cálculo Relacional são potencialmente equivalentes.

� Entretanto o cálculo relacional é:� Não Procedural� Lógica de primeira ordem� Ex: R = { x | x < 10 , x S}

� Cálculo relacional� Cálculo Relacional de Tuplas� Cálculo Relacional de Domínios

AE 3

Álgebra Relacional e SQL

� SQL combina Álgebra e Cálculo Relacional.� SQL é considerada uma linguagem não procedural(mesma característica do Cálculo Relacional).

� O “otimizador” do gerenciador de banco de dados determina a sequência e combinação de operações de álgebra relacional mais eficiente. � (a ordem e precedência dos operadores relacionais utilizados no comando SQL pode ser ignorada pelo otimizados)