Álgebra relacional e sqledrdo/aulas/bd/teoricas/bd_ar_sql.pdf · funções de agregação em sql...
TRANSCRIPT
Bases de Dados (CC2005) Departamento de Ciência de Computadores
Faculdade de Ciências da Universidade do Porto
Eduardo R. B. Marques — DCC/FCUP
Álgebra Relacional e SQL
Bases de Dados Álgebra Relacional e SQL 2
Introdução
Bases de Dados Álgebra Relacional e SQL
Álgebra relacional Formalismo usado para modelar relações em uma BD relacional. Usada internamente por SGBDs para representação e optimização de consultas. Tem uma forte correspondência com SQL.
Aspectos fundamentais: Caso base de uma relação: tabela de BD. Operação na álgebra relacional: toma uma ou mais relações e devolve uma nova relação. Derivação de uma relação: sequência de operações de álgebra relacional.
Álgebra relacional
3
Bases de Dados Álgebra Relacional e SQL
Álgebra Relacional — operações elementares
4
σπ
ρ
ℱ⋈
seleção
projeção
renomeação
agregação
junção
∪∩−×
união
intersecção
diferença
produto cartesiano
Bases de Dados Álgebra Relacional e SQL 5
Projeção, seleção e renomeação
Bases de Dados Álgebra Relacional e SQL
Um esquema de relação R(A1, …, An) tem nome R e atributos A1,…,Anpor ex. ALUNO(NumMec,NumCC,Nome,Curso).
A cada atributo Ai está associado um domínio de valores dom(Ai). Os valores no domínio de um atributo são atómicos e podem incluir o valor especial NULLi.e., podemos ter NULL∈dom(Ai).
Esquema de relação — definição mais geral
6
Bases de Dados Álgebra Relacional e SQL
Uma relação com esquema R(A1, …, An) é um conjunto não ordenado de registos r que têm a forma de tuplos
r=(v1,…,vn)tal que vi∈dom(Ai)e é denotado por r[Ai].
A noção de relação pode ser também generalizada a multi-conjuntos (em vez de conjuntos), permitindo a existência de valores duplicados, em linha com o que é permitido em SQL (ex. consulta de um atributo não-chave).
Detalhe: dada uma relação não ser ordenada o operadores SQL ORDERBY e LIMIT não podem ser expressos em álgebra relacional.
Relação — definição mais geral (cont.)
7
Bases de Dados Álgebra Relacional e SQL
Projeção
8
πA1,…,An(R)
(projeção de sub-conjunto de atributos de R)
A1, …, An atributos de RDefinição
Correspondência em SQL
SELECTA1,…,AnFROMR;
πA1,…,An(R) = {(r[A1], …, r[An]) : r ∈ R}
NotaçãoR relação
Bases de Dados Álgebra Relacional e SQL
Projeção — exemplo
9
R ← πNumMec,Nome(ALUNO)
ALUNO
NumMec NumCC Nome Curso
798764544 12345678 JoãoPinto LCC
345673451 17222303 CarlosSemedo MIERSI
487563546 12021999 MariaSilva LBIO
452212348 18392100 PedroCosta LMAT
348588664 12848585 MiguelFaria LCC
R
NumMec Nome
798764544 JoãoPinto
345673451 CarlosSemedo
487563546 MariaSilva
452212348 PedroCosta
348588664 MiguelFaria
SELECTNumMec,NomeFROMALUNO;
Bases de Dados Álgebra Relacional e SQL
(elementos em Rque verificam a condição C )
Seleção
10
σC(R) Rrelação Ccondição
Notação
Correspondência em SQL
SELECT*FROMRWHEREC;
σC(R) = {r ∈ R : C(r)}Definição
Bases de Dados Álgebra Relacional e SQL
R
NumMec NumCC Nome Curso
798764544 12345678 JoãoPinto LCC
345673451 17222303 CarlosSemedo MIERSI
348588664 12848585 MiguelFaria LCC
Seleção — exemplo
11
ALUNO
NumMec NumCC Nome Curso
798764544 12345678 JoãoPinto LCC
345673451 17222303 CarlosSemedo MIERSI
487563546 12021999 MariaSilva LBIO
452212348 18392100 PedroCosta LMAT
348588664 12848585 MiguelFaria LCC
R ← σCurso='LCC' ∨ Curso='MIERSI'(ALUNO)
SELECT*FROMALUNOWHERECurso='LCC'ORCurso='MIERSI';
Bases de Dados Álgebra Relacional e SQL
Exemplo simples de consulta SQL
12
SELECTNumMec,NomeFROMALUNOWHERECurso='LCC'ORCurso='MIERSI';
condição de
selecção
projeção
relação (neste caso uma tabela)
σ
π
Bases de Dados Álgebra Relacional e SQL
Podemos expressar a derivação de relações como uma sequência de operações na álgebra relacional:
Exemplo simples de consulta SQL (cont.)
13
R0 ← σCurso='LCC' ∨ Curso='MIERSI'(ALUNO)
R ← πNumMec,Nome(R0)
SELECTNumMec,NomeFROMALUNOWHERECurso='LCC'ORCurso='MIERSI';
corresponde em SQL à consulta anterior:
Bases de Dados Álgebra Relacional e SQL
Exemplo simples de consulta SQL (cont.)
14
ALUNO
NumMec NumCC Nome Curso
798764544 12345678 JoãoPinto LCC
345673451 17222303 CarlosSemedo MIERSI
487563546 12021999 MariaSilva LBIO
452212348 18392100 PedroCosta LMAT
348588664 12848585 MiguelFaria LCC
R
NumMec Nome
798764544 JoãoPinto
345673451 CarlosSemedo
348588664 MiguelFaria
SELECTNumMec,NomeFROMALUNOWHERECurso='LCC'ORCurso='MIERSI';
R0 ← σCurso='LCC' ∨ Curso='MIERSI'(ALUNO)
R ← πNumMec,Nome(R0)
Bases de Dados Álgebra Relacional e SQL
Renomeação
15
ρB1, … , Bn(R)
Sendo R uma relação com atributos
Correspondência em SQL:
SELECTA1ASB1,…,AnASBnFROMR;
B1, … , Bn
A1, … , An
é a mesma relação com os atributos
renomeados para .
Bases de Dados Álgebra Relacional e SQL
Renomeação — exemplo
16
SELECTNome,YEAR(DataNasc)ASAnoDeNascFROMUTENTEWHERENum>3;
R ← ρNome,AnoDeNasc(R1)
R1 ← πNome,YEAR(DataNasc)(R0)
R0 ← σNum>3(UTENTE)
Bases de Dados Álgebra Relacional e SQL 17
Agregação simples
Bases de Dados Álgebra Relacional e SQL
Agregação simples
18
ℱop(A)(R) = r1[A] op r2[A] op … op rn[A]
Sendo R uma relação, A um atributo de R e op um operador binário (ex. COUNT, SUM, MIN, …) sobre dom(A):
denota o resultado agregado de aplicar op à sequência de valores de A para tuplos em R.
Em SQL:
SELECTop(A)FROMR
onde op é um operador de agregação.
Bases de Dados Álgebra Relacional e SQL
Agregação simples — notação com renomeação
19
ℱX=op(A)(R) = r1[A] op r2[A] op … op rn[A]
denota o resultado agregado de aplicar op à sequência de valores de A para tuplos em R e dar um nome X ào atributo da relação resultante. Está implícita uma operação de renomeação.
Em SQL:
SELECTop(A)FROMRASX
onde op é um operador de agregação.
Bases de Dados Álgebra Relacional e SQL
Semântica: 1) Agrupa todos os valores de <ATRIBUTO>ignorando valores NULL2) Aplica sobre estes<FUNÇÃODEAGREGAÇÃO>3) Devolve o resultado final.
Nota: generaliza-se para múltiplos operadores, como ilustramos a seguir.
Agregações simples em SQL
20
SELECT<FUNÇÃODEAGREGAÇÃO>(<ATRIBUTO>)[ASNome],FROM…
Sintaxe — forma mais simples:
Bases de Dados Álgebra Relacional e SQL
Agregação simples — exemplo (cont.)
21
/*Obtémnºtotaldeutentes.*/SELECTCOUNT(*)ASNum_UtentesFROMUTENTE;
+----------------+ | Num_Utentes | +----------------+ | 7 | +----------------+
COUNT(*) : caso especial — conta o nº de registos.
ℱNum_Utentes=COUNT(*)(UTENTE)
Bases de Dados Álgebra Relacional e SQL
Agregação combinada com outros operadores
22
R1 ← ℱN=COUNT(*) (R0)
SELECTCOUNT(*)ASNFROMALUNOWHERECurso='LCC';
R0 ← σ Curso = 'LCC' (ALUNO)
ALUNO
NumMec NumCC Nome Curso
798764544 12345678 JoãoPinto LCC
345673451 17222303 CarlosSemedo MIERSI
487563546 12021999 MariaSilva LBIO
452212348 18392100 PedroCosta LMAT
348588664 12848585 MiguelFaria LCC
2
Bases de Dados Álgebra Relacional e SQL
<FUNÇÃODEAGREGAÇÃO> identifica operação associativa e cumutativa — ex. soma, máximo mas não subtração ou divisão. Alguns dos operadores mais comuns são listados abaixo
Ver por exemplo referência MySQL para estes e outros.
Funções de agregação em SQL
23
Função Operação
COUNTCOUNT(DISTINCT)SUMMINMAXAVGSTDDEV
Contagem Contagem de elementos distintos Soma Mínimo Máximo Média Desvio padrão
Bases de Dados Álgebra Relacional e SQL
Agregação simples com vários operadores
24
A noção de agregação generaliza-se para vários operadores:
Em SQL:
ℱX1=op1(A1), … ,Xk=opk(Ak)(R) = (ℱop1(A1)(R), . . . , ℱopk(Ak)(R))
SELECTop1(A1)ASX1,…,opk(Ak)ASXkFROMR
Bases de Dados Álgebra Relacional e SQL
Agregação simples com vários operadores (cont.)
25
/*Obtémmínimo,máximoemédiadoanodenascimento.*/SELECTMIN(YEAR(DataNasc))AsMin,MAX(YEAR(DataNasc))AsMax,AVG(YEAR(DataNasc))AsMediaFROMUTENTE;
+------+------+-----------+ | Min | Max | Media | +------+------+-----------+ | 1975 | 2005 | 1988.2857 | +------+------+-----------+
ℱMin=MIN(YEAR(DataNasc)), Max=MAX(...),Media=AVG(...)(UTENTE)
Bases de Dados Álgebra Relacional e SQL 26
Agregação agrupada
Bases de Dados Álgebra Relacional e SQL
Agregação agrupada
27
G1, …,Gnℱop1(A1), … ,opk(Ak)(R)
generaliza o conceito de agregação simples para agrupada tendo como atributos de agrupamento
G1, …, Gn
Em SQL:
SELECTop1(A1),…,opk(Ak)FROMRGROUPBYG1…,Gn
Bases de Dados Álgebra Relacional e SQL
Semântica: 1) Agrupa todos os valores de <ATRIBUTO>mas em grupos distintos por cada valor diferente em vez de um só (quando não temos GROUP BY). 2) Aplica sobre cada grupo <FUNÇÃODEAGREGAÇÃO>3) Devolve o resultado final para cada grupo.
Nota: Podemos especificar várias funções de agregação e vários atributos de agrupamento.
Agregação com GROUPBY
28
SELECT<ATRIBUTODEAGRUPAMENTO>,<FUNÇÃODEAGREGAÇÃO>(<ATRIBUTO>)[ASNOME],FROM<TABELA>[WHERE<CONDIÇÃO>]GROUPBY<ATRIBUTOPARAAGRUPAMENTO>;
Sintaxe — forma mais simples:
Bases de Dados Álgebra Relacional e SQL
Agregação agrupada — exemplo
29
SELECTCurso,COUNT(*)ASNFROMALUNOGROUPBYCurso;
ALUNO
NumMec NumCC Nome Curso
798764544 12345678 JoãoPinto LCC
345673451 17222303 CarlosSemedo MIERSI
487563546 12021999 MariaSilva LBIO
452212348 18392100 PedroCosta LMAT
348588664 12848585 MiguelFaria LCC
R ← Curso ℱ N=COUNT(*)(ALUNO)
R
Curso N
LCC 2
MIERSI 1
LBIO 1
LMAT 1
Bases de Dados Álgebra Relacional e SQL
Semântica: <CONDIÇÃOSOBREGRUPO> filtra resultados por grupo HAVING funciona como cláusula WHERE ao nível de cada grupo Em álgebra relacional: corresponde a uma operação de agregação seguida de uma operação de seleção.
Agregação com GROUPBY-HAVING
30
SELECT[<ATRIBUTODEAGRUPAMENTO>],<FUNÇÃODEAGREGAÇÃO>(<ATRIBUTO>)[ASNOME]FROM<TABELA>[WHERE<CONDIÇÃO>]GROUPBY<ATRIBUTODEAGRUPAMENTO>HAVING<CONDIÇÃOSOBREGRUPO>;
Sintaxe — forma mais simples:
Bases de Dados Álgebra Relacional e SQL
Agregação agrupada com HAVING — exemplo
31
SELECTCurso,COUNT(*)ASNFROMALUNOGROUPBYCurso;HAVINGN=1;
ALUNO
NumMec NumCC Nome Curso
798764544 12345678 JoãoPinto LCC
345673451 17222303 CarlosSemedo MIERSI
487563546 12021999 MariaSilva LBIO
452212348 18392100 PedroCosta LMAT
348588664 12848585 MiguelFaria LCC
R0 ← Curso ℱ N=COUNT(*)(ALUNO)
R
Curso N
MIERSI 1
LBIO 1
LMAT 1
R ← σ N=1 (R0)
Bases de Dados Álgebra Relacional e SQL 32
Operações sobre conjuntos
Bases de Dados Álgebra Relacional e SQL
Operações sobre conjuntos
33
A B
A ∪ B
União
A B
A − B
Diferença
A ∩ B
Intersecção
A B
A × B
A BProduto cartesiano
A ∩ B = {x : x ∈ A ∧ x ∈ B}
A ∪ B = {x : x ∈ A ∨ x ∈ B}
A − B = {x : x ∈ A ∧ x ∉ B}
A × B = {(x, y) : x ∈ A ∧ y ∈ B}
Bases de Dados Álgebra Relacional e SQL
Operações sobre conjuntos e SQL
34
R ∩ S
R ∪ S
R − S
RUNIONS
RINTERSECTS
REXCEPTS
R × S SELECT*FROMR,S
Nota: Não suportados em MySQL
RCROSSJOINS ou
Bases de Dados Álgebra Relacional e SQL
União, interseção, e diferença
35
R ∩ S R ∪ S R − S
As operações de união, interseção e diferença de relações R e S
estão restritas a relações compatíveis
R(A1, … , An) S(B1, … , Bn)e
tais que ∀i = 1, … , n, dom(Ai) = dom(Bi)Não é imposto que os atributos tenham nomes iguais, mas por convenção retêm os nomes dos atributos de R.
R ∩ S R ∪ S R − S
Bases de Dados Álgebra Relacional e SQL
União / UNION— exemplo (MovieStream)
36
R ← π Manager (DEPARTMENT)
U ← R ∪ S
S ← π RegionManager (REGION)
/*Obtémoiddetodososfuncionáriosquesãogerentesdedepartamentoouderegião*/SELECTManagerFROMDEPARTMENTUNIONSELECTRegionManagerFROMREGION;
+---------+| Manager |+---------+| 2 || 7 || 11 || 14 || 15 || 16 || 17 |+---------+Obs: coluna da relação é Manager em linha com
convençãomencionadanoslideanterior.
Bases de Dados Álgebra Relacional e SQL
Interseção / INTERSECT— exemplo (MovieStream)
37
R ← π MovieId (MOVIE)
I ← R ∩ S
S ← π MovieId (STREAM)
/*Obtémiddefilmescomalgum“stream”feito[nãosuportadoemMySQL]*/
SELECTMovieIdFROMMOVIEINTERSECTSELECTMovieIdFROMSTREAM;Obs: já veremos como expressar uma consulta em MySQL com resultado equivalente.
Bases de Dados Álgebra Relacional e SQL
Diferença / EXCEPT— exemplo (MovieStream)
38
R ← π MovieId (MOVIE)
D ← R − S
S ← π MovieId (STREAM)
/*Obtémiddefilmessemnenhum“stream”feito[nãosuportadaemMySQL]*/
SELECTMovieIdFROMMOVIEEXCEPTSELECTMovieIdFROMSTREAM;Obs: já veremos como expressar uma consulta em MySQL com resultado equivalente.
Bases de Dados Álgebra Relacional e SQL
Em MySQL (e SQL em geral) podemos fazer uso de consultas imbricadas (“nested queries”), que são muito convenientes em uma série de casos. No caso usamos os operadores IN e NOTIN para suprir a falta de suporte de INTERSECT e EXCEPT.
Consultadores anteriores em MySQL
39
SELECTMovieIdFROMMOVIEINTERSECTSELECTMovieIdFROMSTREAM;
SELECTMovieIdFROMMOVIEEXCEPTSELECTMovieIdFROMSTREAM;
SELECTMovieIdFROMMOVIEWHEREMovieIdIN(SELECTMovieIdFROMSTREAM);
SELECTMovieIdFROMMOVIEWHEREMovieIdNOTIN(SELECTMovieIdFROMSTREAM);
Bases de Dados Álgebra Relacional e SQL
Podemos imbricar consultas usando na cláusula WHERE outra(s) consulta(s), chamadas sub-consulta(s). Vários operadores podem ser usados para o encadeamento, como veremos a seguir. Podemos ter consultas imbricadas em associação a várias outras instruções como por exemplo UPDATE ou DELETE além de SELECT.
Consultas imbricadas
40
SELECT…FROM…|DELETEFROM…|UPDATE…WHERE<EXPR><OPERADOR>( SELECT…/*Sub-consulta*/)…
Forma geral
Bases de Dados Álgebra Relacional e SQL
Operadores de sub-consulta
41
<EXPR>IN<CONSULTA> Verdadeiro se <EXPR> é devolvido por <CONSULTA>
<EXPR>NOTIN<CONSULTA> Verdadeiro se <EXPR> não é devolvido por <CONSULTA>
EXISTS<CONSULTA>Verdadeiro se <CONSULTA>devolve pelo menos um resultado.
<EXPR><OPERADORDECOMPARAÇÃO><CONSULTA>Operadores de comparação (os usuais):=<>><>=<=…Verdadeiro se <CONSULTA> devolve um único resultado R e <EXPR> <OPERADOR DECOMPARAÇÃO>Rseverifica.
<EXPR><OPERADORDECOMPARAÇÃO>ALL<CONSULTA>Similar ao caso anterior mas se a comparação é verdadeira para todos os resultados da consulta.
<EXPR><OPERADORDECOMPARAÇÃO>ANY<CONSULTA>Similar ao caso anterior mas se a comparação é verdadeira para pelo menos um os resultados da consulta.
ReferênciaMySQL→“Subqueries”
Bases de Dados Álgebra Relacional e SQL
Sub-consultas — exemplos
42
SELECTTitleFROMMOVIEMWHERENOTEXISTS(SELECT*FROMSTREAMWHEREMovieId=M.MovieId)ORDERBYTitleLIMIT5;
/*Filmessem“streams”-variações*/SELECTTitleFROMMOVIEWHEREMovieIdNOTIN(SELECTMovieIdFROMSTREAM)ORDERBYTitleLIMIT5;
+----------------------+| Title |+----------------------+| (500) Days of Summer || 1408 || 21 || 300 || 8 Mile |+----------------------+5 rows in set (0.00 sec)
Bases de Dados Álgebra Relacional e SQL
Sub-consultas — exemplos (cont.)
43
INSERTINTOSTREAM(CustomerId,MovieId,StreamDate,Charge)VALUES((SELECTCustomerIdFROMCUSTOMERWHEREName='AaronSelby'),(SELECTMovieIdFROMMOVIEWHERETitle='Batman'),'2020-03-2411:24:30',2.50);
SELECT*FROMSTREAMWHERECustomerId=(SELECTCustomerIdFROMCUSTOMERWHEREName='AaronSelby')ANDMovieId=(SELECTMovieIdFROMMOVIEWHERETitle='Batman');
+----------+---------+------------+---------------------+--------+| StreamId | MovieId | CustomerId | StreamDate | Charge |+----------+---------+------------+---------------------+--------+| 10162 | 248 | 375 | 2020-03-24 11:24:30 | 2.50 |+----------+---------+------------+---------------------+--------+
Bases de Dados Álgebra Relacional e SQL
Sub-consultas — exemplos (cont.)
44
INSERTINTOSTREAM(CustomerId,MovieId,StreamDate,Charge)VALUES((SELECTCustomerIdFROMCUSTOMERWHEREName='AaronSelby'),(SELECTMovieIdFROMMOVIEWHERETitle='Batman'),'2020-03-2411:24:30',2.50);
SELECT*FROMSTREAMWHERECustomerId=(SELECTCustomerIdFROMCUSTOMERWHEREName='AaronSelby')ANDMovieId=(SELECTMovieIdFROMMOVIEWHERETitle='Batman');
+----------+---------+------------+---------------------+--------+| StreamId | MovieId | CustomerId | StreamDate | Charge |+----------+---------+------------+---------------------+--------+| 10162 | 248 | 375 | 2020-03-24 11:24:30 | 2.50 |+----------+---------+------------+---------------------+--------+
Bases de Dados Álgebra Relacional e SQL
Sub-consultas — exemplos (cont.)
45
SELECTCOUNT(*)ASNFROMSTREAMWHERECustomerIdIN(SELECTCustomerIdFROMCUSTOMERWHERECountryIN(SELECTNameFROMCOUNTRYWHERERegionId=(SELECTRegionIdFROMREGIONWHEREName='Africa')));
+------+| N |+------+| 1374 |+------+
DELETEFROMSTREAMWHERECustomerIdIN(SELECTCustomerIdFROMCUSTOMERWHERECountryIN(SELECTNameFROMCOUNTRYWHERERegionId=(SELECTRegionIdFROMREGIONWHEREName='Africa')));
Query OK, 1374 rows affected (0.05 sec)
Bases de Dados Álgebra Relacional e SQL
Produto cartesiano
46
Para relações a relação
e elementos
R(A1, … , Am) S(B1, … , Bn)e
R × S
tem atributos A1, … , Am, B1, …, Bn
(r[A1], … , r[Am], s[B1], … , s[Bn])onde
r ∈ R s ∈ S
R × S
R S
Bases de Dados Álgebra Relacional e SQL
Na cláusula FROM podemos especificar várias tabelas para consultas, o que poderá ser bastante conveniente.
Produto cartesiano — exemplo (MovieStream)
47
/*NãosuportadoemMySQL*/SELECTCOUNTRY.NameASCName,REGION.NameASCNameFROMCOUNTRYCROSSJOINREGION;
C0 ← π Name (COUNTRY)
P ← C × R
R0 ← π Name (REGION)
C ← ρ CName (C0)
R ← ρ RName (R0)
/*SuportadodeformageralporSGBDsSQL*/SELECTCOUNTRY.NameASCName,REGION.NameASRNameFROMCOUNTRY,REGION;
Bases de Dados Álgebra Relacional e SQL
Obtemos todas as combinações de nomes de países e regiões, o que não é informação muito útil.
Produto cartesiano — exemplo (cont.)
48
+------------------+-----------------+| CName | RName |+------------------+-----------------+| Anguilla | Other countries || Anguilla | America || Anguilla | Asia || Anguilla | Europe || Anguilla | Africa || French Polynesia | Other countries || French Polynesia | America || French Polynesia | Asia || French Polynesia | Europe || French Polynesia | Africa |. . .
SELECTCOUNTRY.NameASCName,REGION.NameASRNameFROMCOUNTRY,REGION;
C0 ← π Name (COUNTRY)
P ← C × R
R0 ← π Name (REGION)
C ← ρ CName (C0)
R ← ρ RName (R0)
Bases de Dados Álgebra Relacional e SQL
Obtemos neste caso todas as combinações de nomes de países e das regiões correspondentes, o que é muito mais relevante. O exemplo acima corresponde a uma “junção natural”, conceito que introduziremos mais à frente nestes “slides”.
Produto cartesiano — outro exemplo (cont.)
49
SELECTCOUNTRY.NameASCName,REGION.NameASRNameFROMCOUNTRY,REGIONWHERECOUNTRY.RegionId=REGION.RegionId;
P0 ← C × R
C ← ρ Name/CName (COUNTRY)R ← ρ Name/RName (REGION)
P1 ← σ COUNTRY.RegionId=REGION.RegiondId (P0)P ← π CName,RName (P1)
Bases de Dados Álgebra Relacional e SQL
Produto cartesiano — outro exemplo (cont.)
50
+----------------------------------+-----------------+ | CName | RName |+----------------------------------+-----------------+| Anguilla | Other countries || French Polynesia | Other countries || Greenland | Other countries || Nauru | Other countries || New Zealand | Other countries || Reunion | Other countries || Saint Vincent and the Grenadines | Other countries || Tonga | Other countries || Tuvalu | Other countries || Virgin Islands | Other countries || American Samoa | America || Argentina | America || Bolivia | America || Brazil | America |. . .
SELECTCOUNTRY.NameASCName,REGION.NameASRNameFROMCOUNTRY,REGIONWHERECOUNTRY.RegionId=REGION.RegionId;
Bases de Dados Álgebra Relacional e SQL
De forma geral a cláusula FROMpode referir várias tabelas. Por forma a desambiguar atributos com o mesmo nome ou tornar a consulta mais legível podemos opcionalmente identificar cada tabela com um identificador. Nota: Muitas vezes estas consultas correspondem implicitamente a operações de junção (“join”) a ver mais tarde e para as quais há suporte SQL mais especializado.
Consultas em múltiplas tabelas
51
SELECT<ATRIBUTOS>FROM<TABELA1>[ID1],…,<TABELAn>[<IDn>]…
Forma geral
Bases de Dados Álgebra Relacional e SQL
Exemplos de consultas em múltiplas tabelas
52
/*Obtémnomesdesupervisoresecorrespondentesfuncionáriossupervisionados.*/SELECTS1.NameASSupervisor,S2.NameASSupervisedFROMSTAFFS1,STAFFS2WHERES1.StaffId=S2.SupervisorORDERBYSupervisor,Supervised;
+-----------------+-------------------+| Supervisor | Supervised |+-----------------+-------------------+| António Mota | Felícia Antunes || António Mota | Gabriela Silva || António Mota | Gastão Pinto || Augusto Sousa | Alexandra Romeu || Augusto Sousa | Fábio Cruz || Eva Mendes | Maria Silva || Eva Mendes | Pedro Simões || João Pinto | António Mota || João Pinto | Augusto Sousa || João Pinto | José Santos || João Pinto | Xavier Semedo || João Santorini | Joana Moreira || José Santos | Filipa Magalhães || José Santos | João Santorini || Xavier Semedo | Eva Mendes || Xavier Semedo | Filipa Mendes |+-----------------+-------------------+16 rows in set (0.01 sec)
Bases de Dados Álgebra Relacional e SQL
Exemplos de consultas em múltiplas tabelas (cont.)
53
/*Obtémnomesdesupervisoresecorrespondentenúmerodefuncionáriossupervisionados.*/SELECTS1.NameASSupervisor,COUNT(*)ASNFROMSTAFFS1,STAFFS2WHERES1.StaffId=S2.SupervisorGROUPBYSupervisorORDERBYSupervisor;
+-----------------+---+| Supervisor | N |+-----------------+---+| António Mota | 3 || Augusto Sousa | 2 || Eva Mendes | 2 || João Pinto | 4 || João Santorini | 1 || José Santos | 2 || Xavier Semedo | 2 |+-----------------+---+7 rows in set (0.01 sec)
Bases de Dados Álgebra Relacional e SQL
Exemplos de consultas em múltiplas tabelas (cont.)
54
SELECTA.NameASName,M.TitleASTitle,M.YearASYearFROMACTORA,MOVIE_ACTORMA,MOVIEMWHEREA.ActorId=MA.ActorIdANDMA.MovieId=M.MovieIdANDA.NameLIKE'Brad%'ANDM.Year>=2010ORDERBYName,Year,Title;
+----------------+----------------------------+------+| Name | Title | Year |+----------------+----------------------------+------+| Brad Pitt | Megamind | 2010 || Brad Pitt | Moneyball | 2011 || Brad Pitt | 12 Years a Slave | 2013 || Brad Pitt | World War Z | 2013 || Brad Pitt | Fury | 2014 || Brad Pitt | The Big Short | 2015 || Bradley Cooper | The A-Team | 2010 || Bradley Cooper | Limitless | 2011 || Bradley Cooper | The Hangover Part II | 2011 || Bradley Cooper | Silver Linings Playbook | 2012 || Bradley Cooper | The Place Beyond the Pines | 2012 || Bradley Cooper | American Hustle | 2013 || Bradley Cooper | The Hangover Part III | 2013 || Bradley Cooper | American Sniper | 2014 || Bradley Cooper | Guardians of the Galaxy | 2014 |+----------------+----------------------------+------+15 rows in set (0.00 sec)
Bases de Dados Álgebra Relacional e SQL 55
Junções de tabelas
Bases de Dados Álgebra Relacional e SQL
Operações de junção e SQL
56
RJOINSON(C)R ⋈C SJunção
Junção natural
R * S RNATURALJOINS
Junções externas
R SC
R SC
R SC
RLEFTOUTERJOINSON(C)
RRIGHTOUTERJOINSON(C)
RFULLOUTERJOINSON(C)
R *Attrs S RJOINSUSING(Attrs)
Bases de Dados Álgebra Relacional e SQL
Junção
57
Para relações e condição Csobre
é chamada a junção de R e S com condição C. Os elementos têm a forma:
R(A1, … , Am) S(B1, … , Bn)e
R ⋈C S = σC ( R × S )
(r[A1], … , r[Am], s[B1], … , s[Bn])
tais que r ∈ R s ∈ S C(r, s)
R × S
Em SQL: RJOINSON(C)
Bases de Dados Álgebra Relacional e SQL
Esta junção diz-se uma equi-junção: a condição é expressa pela igualdade de atributos. Junções envolvem muitas vezes igualdade de atributos.
Junção — exemplos
58
SELECT*FROMCUSTOMER,COUNTRYWHERECUSTOMER.Country=COUNTRY.Name;
SELECT*FROMCUSTOMERJOINCOUNTRYON(CUSTOMER.Country=COUNTRY.Name);
R ← CUSTOMER ⋈ CUSTOMER.Country = COUNTRY.Name COUNTRY
equivalente a
Bases de Dados Álgebra Relacional e SQL
Junção — exemplos (cont.)
59
SELECT*FROMCUSTOMERJOINCOUNTRYON(CUSTOMER.Country=COUNTRY.Name);
R ← CUSTOMER ⋈ CUSTOMER.Country = COUNTRY.Name COUNTRY
CustomerId Name … Country … Name RegionId
381 Bobby Boudreau … Anguilla … Anguilla 6
43 Christine Roberts … French Polynesia … French Polynesia 6
56 Gloria Cook … French Polynesia … French Polynesia 6
207 Gertru de Castillo … Greenland … Greenland 6
513 Duane Tubbs … Nauru … Nauru 6
. . . . . .
Observe que ficamos com atributos com valores duplicados (Country em Customer e Name em Regio e também dois atributos com o mesmo nome (coluna Name nas 2 tabelas) embora não relacionados.
Bases de Dados Álgebra Relacional e SQL
Neste caso não temos uma equi-junção, já que uma das condições de junção não é uma igualdade entre atributos.
Junção — exemplos (cont.)
60
SELECTM.Title,M.Year,S.StreamDateFROMMOVIEMJOINSTREAMSON(M.MovieId=S.MovieIdANDM.Year>=YEAR(S.StreamDate)-5)WHEREM.Duration>=180;
(abreviandoMOVIEporMeSTREAMporS)
J ← M0 ⋈ M.MovieId = S.StreamId ⋀ M.Year ≥ YEAR(S.StreamDate) − 5 STREAMM0 ← σ Duration>=180 (M)
R ← π Title,Year,StreamDate (J )
Bases de Dados Álgebra Relacional e SQL
Junção — exemplos (cont.)
61
+-------------------------+------+---------------------+| Title | Year | StreamDate |+-------------------------+------+---------------------+| The Wolf of Wall Street | 2013 | 2017-02-15 06:05:00 || The Wolf of Wall Street | 2013 | 2017-03-04 12:12:00 || The Wolf of Wall Street | 2013 | 2017-07-17 08:04:00 || The Wolf of Wall Street | 2013 | 2017-08-26 05:52:00 || The Wolf of Wall Street | 2013 | 2017-11-04 15:39:00 || The Wolf of Wall Street | 2013 | 2018-01-09 06:38:00 || The Wolf of Wall Street | 2013 | 2018-02-03 08:20:00 || The Wolf of Wall Street | 2013 | 2018-03-22 14:46:00 || The Wolf of Wall Street | 2013 | 2018-04-27 05:53:00 || The Wolf of Wall Street | 2013 | 2018-06-20 01:51:00 || The Wolf of Wall Street | 2013 | 2018-07-04 15:48:00 || The Wolf of Wall Street | 2013 | 2018-08-12 13:00:00 || The Wolf of Wall Street | 2013 | 2018-12-29 08:39:00 || The Hateful Eight | 2015 | 2017-02-06 02:53:00 || The Hateful Eight | 2015 | 2017-06-16 06:16:00 || The Hateful Eight | 2015 | 2018-03-28 22:24:00 || The Hateful Eight | 2015 | 2018-04-04 10:19:00 || The Hateful Eight | 2015 | 2018-05-22 09:18:00 || The Hateful Eight | 2015 | 2018-06-24 07:20:00 || The Hateful Eight | 2015 | 2018-07-26 13:44:00 || The Hateful Eight | 2015 | 2018-09-14 16:10:00 || The Hateful Eight | 2015 | 2018-09-26 21:47:00 || The Hateful Eight | 2015 | 2018-11-14 12:45:00 |+-------------------------+------+---------------------+
SELECTM.Title,M.Year,S.StreamDateFROMMOVIEMJOINSTREAMSON(M.MovieId=S.MovieIdANDM.Year>=YEAR(S.StreamDate)-5)WHEREM.Duration>=180;
Bases de Dados Álgebra Relacional e SQL
Muitas equi-junções são baseadas em atributos com nomes comuns. São “naturais”.
Junção — exemplos (cont.)
62
SELECTM.Title,M.YearFROMMOVIEMJOINMOVIE_GENREMGJOINGENREGON(M.MovieId=MG.MovieIdANDMG.GenreId=G.GenreId)WHEREM.Year>=2010ANDG.Label='Horror';
(abreviandoMOVIEporM,MOVIE_GENREporMGeGENREporG)
J1 ← M ⋈ M.MovieId = MG.MovieId MG
R ← σYear ≥ 2010 ⋀ Label = 'Horror' (J2)
J2 ← J1 ⋈J1.MovieId = G.GenreId G
Bases de Dados Álgebra Relacional e SQL
Junção — exemplos (cont.)
63
SELECTM.Title,M.YearFROMMOVIEMJOINMOVIE_GENREMGJOINGENREGON(M.MovieId=MG.MovieIdANDMG.GenreId=G.GenreId)WHEREM.Year>=2010ANDG.Label='Horror';
+--------------------------------+------+| Title | Year |+--------------------------------+------+| The Purge | 2013 || Dark Shadows | 2012 || Warm Bodies | 2013 || Sinister | 2012 || The Conjuring 2 | 2016 || Don't Breathe | 2016 || 10 Cloverfield Lane | 2016 || The Conjuring | 2013 || The Cabin in the Woods | 2011 || World War Z | 2013 || The Woman in Black | 2012 || Insidious | 2010 || It Follows | 2014 || Hansel & Gretel: Witch Hunters | 2013 || Split | 2016 || The Babadook | 2014 |+--------------------------------+------+16 rows in set (0.00 sec)
Bases de Dados Álgebra Relacional e SQL
Junção natural
64
A junção natural de
é definida e denotada por
R(A1, … , Am) S(B1, … , Bn)e
R * S = R ⋈C Stal que: (1) a condição de junção Cexprime a igualdade entre cada
par de atributos das duas relações com o mesmo nome (2) a relação resultante não duplica os atributos comuns.
Em SQL: RNATURALJOINS
Bases de Dados Álgebra Relacional e SQL
Exemplo anterior com junção natural (cont.)
65
SELECTM.Title,M.YearFROMMOVIEMNATURALJOINMOVIE_GENRENATURALJOINGENREGWHEREM.Year>=2010ANDG.Label='Horror';
(abreviandoMOVIEporM,MOVIE_GENREporMGeGENREporG)
J1 ← M ⋈ M.MovieId = MG.MovieId MG
R ← σYear ≥ 2010 ⋀ Label = 'Horror' (J2)
J2 ← J1 ⋈J1.MovieId = G.GenreId G
J1 ← M * MG
R ← σ Year ≥ 2010 ⋀ Label = 'Horror' (J2)
J2 ← J1 * G
SELECTM.Title,M.YearFROMMOVIEMJOINMOVIE_GENREMGJOINGENREGON(M.MovieId=MG.MovieIdANDMG.GenreId=G.GenreId)WHEREM.Year>=2010ANDG.Label='Horror';
Bases de Dados Álgebra Relacional e SQL
Junção natural restrita
66
Denotamos
pela junção natural restrita a um sub-conjunto de atributos
R *A1, … ,AnS
Em SQL: RJOINSUSING(A1,…,AN)
A1, … , An
Bases de Dados Álgebra Relacional e SQL
Em contraste COUNTRY NATURAL JOIN REGION é uma relação vazia …Porquê?
Junção natural restrita — exemplo
67
SELECTC.NameASCName,R.NameASRNameFROMCOUNTRYCJOINREGIONRUSING(RegionId);
J ← C *RegionId R
C ← ρ Name/CName (COUNTRY)R ← ρ Name/RName (REGION)
P ← π CName,RName (P1)
+----------------------------------+-----------------+ | CName | RName |+----------------------------------+-----------------+| Anguilla | Other countries || French Polynesia | Other countries || Greenland | Other countries || Nauru | Other countries || New Zealand | Other countries || Reunion | Other countries || Saint Vincent and the Grenadines | Other countries || Tonga | Other countries || Tuvalu | Other countries || Virgin Islands | Other countries || American Samoa | America || Argentina | America || Bolivia | America || Brazil | America |. . .
Bases de Dados Álgebra Relacional e SQL
Para duas relações R eS
Junção externa
68
definimos a junção externa à esquerda:
R S=C
R ⋈C S
{(r[A1], . . . , r[Am], NULL, . . . , NULL)∪
∣ r ∈ R ∧ /∃s ∈ S : C(r, s)}
R(A1, … , Am) S(B1, … , Bn)
A relação engloba valores(NULL,…,NULL)pararegistosemSquenãoverificamacondiçãoC.
Em SQL: RLEFTOUTERJOINSON(C)
Bases de Dados Álgebra Relacional e SQL
Junção externa — exemplos
69
SELECTS1.NameASSupervised,S2.NameASSupervisorFROMSTAFFS1LEFTOUTERJOINSTAFFS2ON(S1.Supervisor=S2.StaffId)ORDERBYSupervised,Supervisor;
+-------------------+-----------------+| Supervised | Supervisor |+-------------------+-----------------+| Alexandra Romeu | Augusto Sousa || António Mota | João Pinto || Augusto Sousa | João Pinto || Eva Mendes | Xavier Semedo || Fábio Cruz | Augusto Sousa || Felícia Antunes | António Mota || Filipa Magalhães | José Santos || Filipa Mendes | Xavier Semedo || Gabriela Silva | António Mota || Gastão Pinto | António Mota || Joana Moreira | João Santorini || João Pinto | NULL || João Santorini | José Santos || José Santos | João Pinto || Maria Silva | Eva Mendes || Pedro Simões | Eva Mendes || Xavier Semedo | João Pinto |+-------------------+-----------------+17 rows in set (0.01 sec)
O resultado para João Pinto não constaria da consulta alternativa com uma junção (JOIN) normal.
Bases de Dados Álgebra Relacional e SQL
e finalmente a junção externa completa R S=
C
Junção externa (cont.)
70
Analogamente definimos a junção externa à direita
R S=C
R ⋈C S
{(NULL, . . . , NULL, s[B1], . . . , s[Bn], )∪
∣ s ∈ s ∧ /∃r ∈ R : C(r, s)}
R SC
∪R SC
Em SQL: RRIGHTOUTERJOINSON(C)RFULLOUTERJOINSON(C)
Bases de Dados Álgebra Relacional e SQL
Junção externa — exemplos (cont.)
71
SELECTS1.NameASSupervised,S2.NameASSupervisorFROMSTAFFS1RIGHTOUTERJOINSTAFFS2ON(S1.Supervisor=S2.StaffId)ORDERBYSupervised,Supervisor;
+-------------------+-------------------+| Supervised | Supervisor |+-------------------+-------------------+| NULL | Alexandra Romeu || NULL | Fábio Cruz || NULL | Felícia Antunes || NULL | Filipa Magalhães || NULL | Filipa Mendes || NULL | Gabriela Silva || NULL | Gastão Pinto || NULL | Joana Moreira || NULL | Maria Silva || NULL | Pedro Simões || Alexandra Romeu | Augusto Sousa || António Mota | João Pinto || Augusto Sousa | João Pinto || Eva Mendes | Xavier Semedo || Fábio Cruz | Augusto Sousa || Felícia Antunes | António Mota || Filipa Magalhães | José Santos |. . .
Os resultados para funcionários que não supervisionam nenhum outro não constariam de uma consulta com uma junção (JOIN) normal.
Bases de Dados Álgebra Relacional e SQL
Junção externa — exemplos (cont.)
72
SELECTS1.NameASSupervised,S2.NameASSupervisorFROMSTAFFS1FULLOUTERJOINSTAFFS2ON(S1.Supervisor=S2.StaffId)ORDERBYSupervised,Supervisor;
MySQL não suporta FULLOUTERJOINmas podemos fazer a união de consultas usando de LEFTOUTERJOINeRIGHTOUTERJOIN(a seguir).
+-------------------+-------------------+| Supervised | Supervisor |+-------------------+-------------------+| NULL | Alexandra Romeu || NULL | Fábio Cruz || NULL | Felícia Antunes || NULL | Filipa Magalhães || NULL | Filipa Mendes || NULL | Gabriela Silva || NULL | Gastão Pinto || NULL | Joana Moreira || NULL | Maria Silva || NULL | Pedro Simões || Alexandra Romeu | Augusto Sousa || António Mota | João Pinto || Augusto Sousa | João Pinto |. . . || João Pinto | NULL || João Santorini | José Santos |. . .
Bases de Dados Álgebra Relacional e SQL
Junção externa — exemplos (cont.)
73
(SELECTS1.NameASSupervised,S2.NameASSupervisorFROMSTAFFS1LEFTOUTERJOINSTAFFS2ON(S1.Supervisor=S2.StaffId))UNION(SELECTS1.NameASSupervised,S2.NameASSupervisorFROMSTAFFS1RIGHTOUTERJOINSTAFFS2ON(S1.Supervisor=S2.StaffId))ORDERBYSupervised,Supervisor;
+-------------------+-------------------+| Supervised | Supervisor |+-------------------+-------------------+| NULL | Alexandra Romeu || NULL | Fábio Cruz || NULL | Felícia Antunes || NULL | Filipa Magalhães || NULL | Filipa Mendes || NULL | Gabriela Silva || NULL | Gastão Pinto || NULL | Joana Moreira || NULL | Maria Silva || NULL | Pedro Simões || Alexandra Romeu | Augusto Sousa || António Mota | João Pinto || Augusto Sousa | João Pinto |. . .| João Pinto | NULL || João Santorini | José Santos |. . .