INF 325 - Álgebra Relacional x SQL : conexão prática

Prof. Célio Guimarães     IC - Unicamp
Atualizado em 4 Abril 2011

SQL foi fortemente influenciada pelos trabalhos de Codd sobre Álgebra Relacional (AR) e Cálculo Relacional de Tuplas (CRT). Embora elas sejam equivalentes no que diz respeito ao poder de expressar consultas, SQL é mais poderosa devido principalmente aos seus recursos de contagem, ordenação (cláusula order by ) e agrupamento (cláusula group by ). A AR e o CRT podem ambas serem vistas como formas compactas de expressar consultas em SQL. O domínio de uma delas pode ser de grande ajuda no raciocínio lógico por trás das soluções de consultas complexas. É o que faremos através de alguns exemplos da AR vistos em aula.
É importante ressaltar, no entanto, uma diferença conceitual importante entre a AR e SQL: SQL não trata tabelas como conjuntos matemáticos, permitindo a ocorrência de linhas duplicadas. Uma forma de evitar isto é sempre especificar a chave primária ao criar uma tabela. Infelizmente isto não é suficiente, pois tabelas intermediárias produzidas ao se executar um comando SQL podem conter linhas duplicadas se certos cuidados não forem tomados: o exemplo mais simples desse efeito é a operação de projeção (Π) que elimina duplicatas na AR mas a sua tradução natural para SQL não o faz, sendo necessário colocar o qualificado distinct na cláusula select, como veremos nos exemplos. A seguir, como operações da AR são traduzidas para SQL:

  • Projeção: expressa em SQL na cláusula select do comando select

    Exemplo: "Para cada funcionário apresente o seu número e os nomes dos seus dependentes" 

         AR: Π numf, nomed Dependentes
     SQL: select numf,nomed from Dependntes

Obs: não há repetição porque por convenção, o par numf, nomed é Chave Primária de Dependentes
    "Dê uma lista dos funcionários que possuem dependentes" 
     
    Π numf Dependentes
	
    select numf from Dependentes
        problema: o funcionário 02 aparecerá duas vezes! É preciso incluir:
    select distinct numf from Dependentes
  • Seleção (operador σ): expressa em SQL na clausula where através da comparação do valor de uma coluna da tabela especificada na cláusula from com uma constante:

    Exemplo: "Para cada funcionário que possui filhas, obtenha o seu número, o nome e parentesco dos dependentes.

        Π numf, nomed, par σ(par='filha')Dependentes
    
    select numf, nomed, par
    from Funcionarios
    where par= 'filha'
  • Interseção e diferença: em SQL temos os operadores intersect, except e union (interseção, diferença e união); podemos também expressar a interseção através do operador in seguido de subconsulta e a diferença através do operador not in seguido de subconsulta.
    "Dê uma lista das pessoas que são pais e cujos pais aparecem na tabela D(p,f) " 
       Πp(D) ∩ Πf(D)
   select p from D
   intersect
   select f from D
ou
   select distinct p from D
   where p in
   (select f from D)
    "Dê uma lista das pessoas que não têm filhos". 
        Πf(D) - Πp(D)
    select f from D
    except
    select p from D
ou,
    select distinct f from D
    where f not in
    (select p frm D)
  • Produto cartesiano e renomeação de duas ou mais tabelas: expresso em SQL na cláusula   from colocando os nomes das tabelas envolvidas separados por ",": 
    Exemplos:
     select ...
     from Funcionarios, Dependentes
     ou,
     select ....
     from D as D1, D as D2, D as D3
    Observe também nesse exemplo os operadores de renomeação (alias na terminologia do SQL): as D1, as D2, etc. Usualmente o produto cartesiano será utilizado junto com uma seleção, conforme veremos.

  • Junção θ e junção natural:

    Lembrando que a junção θ e a junção natural (mais precisamente junção de igualdade) são um subconjunto do produto cartesiano, obtido através de uma operação de seleção envolvendo comparação entre colunas, elas podem ser expressas através de uma expressão de comparação na cláusula where envolvendo as colunas escolhidas. Exemplos:

    junção natural: "para cada funcionário que possui dependentes apresente o seu nome, os nomes e parentesco dos dependentes": 

        Πnomef, nomed, par  Funcionarios |X| Dependentes
    ou,
    Πnomef, nomed, par &sigma(Funcionários.numf=Dependentes.numf)(Funcionarios x Dependentes)

    select nomef, nomed, par 
    from Funcionarios, Dependentes
    where Funcionarios.numf = Dependentes.numf
    Outro exemplo: tabela de "pais e filhos", D(p,f):
    "Obtenha pares de pessoas a1, n1, onde a1 é avô de n1" 
        ΠD1.p,D2.f D1 |X D1.f=D2.p| D2
    ou, usando a definição de junção (preferível):
    ΠD1.p,D2.f σ(D1.f=D2.p)(D1 x D2) (Obs: aqui há uma renomeação implícita - qual é ela?)
SQL:        
    select D1.p, D2.f
    from D as D1, D as D2
    where D1.f= D2.p
    Exemplo de junção theta: "obtenha pares de pessoas sem repetição da tabela "Pais e Filhos", D( p, f), que são ou foram cônjuges, isto é, possuem um ou mais filhos em comum" 
        ρD1(p1,f1) D,  ρD2(p2,f2) D  
	
    Πp1,p2(σp1 < p2 (D1 |Xf1=f2| D2))
    ou,
    Πp1,p2 (σ(p1 < p2 and f1=f2 (D1 x D2))

        select distinct D1.p, D2.p
    from D as D1, D as D2
    where D1.f = D2.f and D1.p < D2.p
    Exercícios:
    (i) Por que distinct é necessário nesta solução e não na do problema anterior?
    Re-escreva as expressões da AR acima com a seguinte renomeação, que deixa a expressão em AR mais próxima da sua tradução para SQL: ρD1 D e ρD2 D  

    Exercícios complementares:
    (i) "Para cada funcionário apresente o seu numero e, em ordem decrescente, o número de seus descendentes".
    (ii) "Qual o funcionário que possui o maior número de dependentes?"
    (iii) "Para cada funcionário apresente o seu nome e, em ordem decrescente, o número de seus descendentes".

    Obs AR : não é possivel expressar: a AR não dispõe de recursos de contagem, ordenação e agrupamento.
    SQL: relativamente simples usando os recursos de ordenação (order by), contagem ( count()) e de agrupamento (group by). Tente!

    Para finalizar vamos resolver a consulta clássica do Modelo Relacional:
    (**)"Obtenha uma lista dos fornecedores que fornecem todos os materiais" 

        F - Πf(F x M - FM)  (p. 72 do livro texto)
SQL:    
    select f from F
    except
    (select f from
     (select f,m
      from F,M
      except
      select f, m
      from FM) as Temp)   Obs: Postgres requer a nomeação as XXX
    Obs: (i) A consulta em SQL é uma tradução literal da expressão da AR cujo significado foi detalhado no livro-texto. Ela seria quase ininteligível sem a explicação formulada via AR, ou seja, a AR é uma ferramenta poderosa para desenvolver o raciocínio lógico requerido para expressar consultas complexas em SQL.
    (ii) a operação except (diferença) assim como a união elimina duplicatas mas não a projeção. Se usássemos not in para expressar o primeiro except, as duas primeiras linhas acima ficariam assim: 
      
   select distinct f from F 
   where f not in

    A consulta clássica para a BD Torneios de Tênis:

    Suponha que tenhamos criado uma tabela TGslam contendo apenas a lista dos torneios do Grand Slam: 

        create table TGslam as
    (select * from Lista_torneios
    where cat= 'GS')

e agora queremos encontrar:
(i) "quais jogadores não venceram algum torneio do Grand Slam?"
    select distinct numj 
    from jogadores, TGslam           -- todas as combinações possiveis (numj,numt),
    where (numj, numt) not in        -- que não se encontram na tabela torneios
    (select numj,numt from torneios) -- nos dão pares (numj,numt) satisfazendo a consulta

e, finalmente, 
(ii) "Quais jogadores venceram todos os torneios do Grand Slam?"

    select numj from jogadores
    where numj not in
    (select distinct numj 
    from jogadores, TGslam 
    where (numj,numt) not in
     (select numj,numt 
      from torneios))  
 
 O resultado dessa consulta dos dá o jogador 29 - André Agassi, o único a ganhar os 4 torneios
 do Grand Slam desde 1970 até 2009 quando Roger Federer ganhou o torneio que lhe faltava, Roland Garros
 (em 1969 Rod Laver, australiano, ganhou os 4 torneios no mesmo ano, fato inédito até hoje).
 Exercício: modifique de forma trivial  a consulta acima de forma a exibir o nome do jogador.
 
 Solução usando os recursos de contagem e de agrupamento do SQL:
 
    select numj, count(distinct numt)
    from torneios
    group by numj
    having count(distinct numt)=
       (select count(*) from TGslam)
       
Obs: sem o qualificador distinct na cláusula having a consulta daria resultado incorreto
e se omitido na cláusula select daria o número total de torneios de Grand Slam vencidos pelo jogador.

Uma modificação simples dessa consulta nos permite responder à seguinte:
"Para cada jogador que ganhou torneios do Grand Slam, exiba em ordem decrescente 
o número de torneios que ganhou e o número do jogador":

select count(numt), numj
from torneios
where numt in (select numt from TGslam)
group by numj
order by count(numt) desc