Álgebra relacional e sqledrdo/aulas/bd/teoricas/bd_ar_sql.pdf · funções de agregação em sql...

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

Álgebra Relacional — operações elementares

ℱ⋈

seleção

projeção

renomeação

agregação

junção

∪∩−×

união

intersecção

diferença

produto cartesiano

Projeção, seleção e renomeação

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

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.)

Projeção

π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

Projeção — exemplo

R ← πNumMec,Nome(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

NumMec Nome

798764544 JoãoPinto

345673451 CarlosSemedo

487563546 MariaSilva

452212348 PedroCosta

348588664 MiguelFaria

SELECTNumMec,NomeFROMALUNO;

(elementos em Rque verificam a condição C )

Seleção

σC(R) Rrelação Ccondição

Notação

Correspondência em SQL

SELECT*FROMRWHEREC;

σC(R) = {r ∈ R : C(r)}Definição

798764544 12345678 JoãoPinto LCC

Seleção — exemplo

798764544 12345678 JoãoPinto LCC

R ← σCurso='LCC' ∨ Curso='MIERSI'(ALUNO)

SELECT*FROMALUNOWHERECurso='LCC'ORCurso='MIERSI';

Exemplo simples de consulta SQL

SELECTNumMec,NomeFROMALUNOWHERECurso='LCC'ORCurso='MIERSI';

condição de

selecção

projeção

relação (neste caso uma tabela)

Podemos expressar a derivação de relações como uma sequência de operações na álgebra relacional:

Exemplo simples de consulta SQL (cont.)

R0 ← σCurso='LCC' ∨ Curso='MIERSI'(ALUNO)

R ← πNumMec,Nome(R0)

corresponde em SQL à consulta anterior:

Exemplo simples de consulta SQL (cont.)

798764544 12345678 JoãoPinto LCC

NumMec Nome

798764544 JoãoPinto

345673451 CarlosSemedo

348588664 MiguelFaria

R0 ← σCurso='LCC' ∨ Curso='MIERSI'(ALUNO)

R ← πNumMec,Nome(R0)

Renomeação

ρ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 .

Renomeação — exemplo

SELECTNome,YEAR(DataNasc)ASAnoDeNascFROMUTENTEWHERENum>3;

R ← ρNome,AnoDeNasc(R1)

R1 ← πNome,YEAR(DataNasc)(R0)

R0 ← σNum>3(UTENTE)

Agregação simples

ℱ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.

Agregação simples — notação com renomeação

ℱ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.

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

SELECT<FUNÇÃODEAGREGAÇÃO>(<ATRIBUTO>)[ASNome],FROM…

Sintaxe — forma mais simples:

Agregação simples — exemplo (cont.)

/*Obtémnºtotaldeutentes.*/SELECTCOUNT(*)ASNum_UtentesFROMUTENTE;

+----------------+ | Num_Utentes | +----------------+ | 7 | +----------------+

COUNT(*) : caso especial — conta o nº de registos.

ℱNum_Utentes=COUNT(*)(UTENTE)

Agregação combinada com outros operadores

R1 ← ℱN=COUNT(*) (R0)

SELECTCOUNT(*)ASNFROMALUNOWHERECurso='LCC';

R0 ← σ Curso = 'LCC' (ALUNO)

798764544 12345678 JoãoPinto LCC

<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

Função Operação

COUNTCOUNT(DISTINCT)SUMMINMAXAVGSTDDEV

Contagem Contagem de elementos distintos Soma Mínimo Máximo Média Desvio padrão

Agregação simples com vários operadores

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

Agregação simples com vários operadores (cont.)

/*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)

Agregação agrupada

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

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

SELECT<ATRIBUTODEAGRUPAMENTO>,<FUNÇÃODEAGREGAÇÃO>(<ATRIBUTO>)[ASNOME],FROM<TABELA>[WHERE<CONDIÇÃO>]GROUPBY<ATRIBUTOPARAAGRUPAMENTO>;

Agregação agrupada — exemplo

SELECTCurso,COUNT(*)ASNFROMALUNOGROUPBYCurso;

798764544 12345678 JoãoPinto LCC

R ← Curso ℱ N=COUNT(*)(ALUNO)

Curso N

MIERSI 1

LBIO 1

LMAT 1

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

SELECT[<ATRIBUTODEAGRUPAMENTO>],<FUNÇÃODEAGREGAÇÃO>(<ATRIBUTO>)[ASNOME]FROM<TABELA>[WHERE<CONDIÇÃO>]GROUPBY<ATRIBUTODEAGRUPAMENTO>HAVING<CONDIÇÃOSOBREGRUPO>;

Agregação agrupada com HAVING — exemplo

SELECTCurso,COUNT(*)ASNFROMALUNOGROUPBYCurso;HAVINGN=1;

798764544 12345678 JoãoPinto LCC

R0 ← Curso ℱ N=COUNT(*)(ALUNO)

Curso N

MIERSI 1

LBIO 1

LMAT 1

R ← σ N=1 (R0)

Operações sobre conjuntos

A ∪ B

União

A − B

Diferença

A ∩ B

Intersecção

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}

Operações sobre conjuntos e SQL

R ∩ S

R ∪ S

R − S

RUNIONS

RINTERSECTS

REXCEPTS

R × S SELECT*FROMR,S

Nota: Não suportados em MySQL

RCROSSJOINS ou

União, interseção, e diferença

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

União / UNION— exemplo (MovieStream)

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.

Interseção / INTERSECT— exemplo (MovieStream)

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.

Diferença / EXCEPT— exemplo (MovieStream)

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.

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

SELECTMovieIdFROMMOVIEINTERSECTSELECTMovieIdFROMSTREAM;

SELECTMovieIdFROMMOVIEEXCEPTSELECTMovieIdFROMSTREAM;

SELECTMovieIdFROMMOVIEWHEREMovieIdIN(SELECTMovieIdFROMSTREAM);

SELECTMovieIdFROMMOVIEWHEREMovieIdNOTIN(SELECTMovieIdFROMSTREAM);

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

SELECT…FROM…|DELETEFROM…|UPDATE…WHERE<EXPR><OPERADOR>( SELECT…/*Sub-consulta*/)…

Forma geral

Operadores de sub-consulta

<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”

Sub-consultas — exemplos

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)

Sub-consultas — exemplos (cont.)

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 |+----------+---------+------------+---------------------+--------+

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 |+----------+---------+------------+---------------------+--------+

SELECTCOUNT(*)ASNFROMSTREAMWHERECustomerIdIN(SELECTCustomerIdFROMCUSTOMERWHERECountryIN(SELECTNameFROMCOUNTRYWHERERegionId=(SELECTRegionIdFROMREGIONWHEREName='Africa')));

+------+| N |+------+| 1374 |+------+

DELETEFROMSTREAMWHERECustomerIdIN(SELECTCustomerIdFROMCUSTOMERWHERECountryIN(SELECTNameFROMCOUNTRYWHERERegionId=(SELECTRegionIdFROMREGIONWHEREName='Africa')));

Query OK, 1374 rows affected (0.05 sec)

Produto cartesiano

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

Na cláusula FROM podemos especificar várias tabelas para consultas, o que poderá ser bastante conveniente.

Produto cartesiano — exemplo (MovieStream)

/*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;

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.)

+------------------+-----------------+| 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)

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.)

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)

Produto cartesiano — outro exemplo (cont.)

+----------------------------------+-----------------+ | 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;

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

SELECT<ATRIBUTOS>FROM<TABELA1>[ID1],…,<TABELAn>[<IDn>]…

Forma geral

Exemplos de consultas em múltiplas tabelas

/*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)

Exemplos de consultas em múltiplas tabelas (cont.)

/*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)

Exemplos de consultas em múltiplas tabelas (cont.)

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)

Junções de tabelas

Operações de junção e SQL

RJOINSON(C)R ⋈C SJunção

Junção natural

R * S RNATURALJOINS

Junções externas

RLEFTOUTERJOINSON(C)

RRIGHTOUTERJOINSON(C)

RFULLOUTERJOINSON(C)

R *Attrs S RJOINSUSING(Attrs)

Junção

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)

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

SELECT*FROMCUSTOMER,COUNTRYWHERECUSTOMER.Country=COUNTRY.Name;

SELECT*FROMCUSTOMERJOINCOUNTRYON(CUSTOMER.Country=COUNTRY.Name);

R ← CUSTOMER ⋈ CUSTOMER.Country = COUNTRY.Name COUNTRY

equivalente a

Junção — exemplos (cont.)

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.

Neste caso não temos uma equi-junção, já que uma das condições de junção não é uma igualdade entre atributos.

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 )

+-------------------------+------+---------------------+| 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;

Muitas equi-junções são baseadas em atributos com nomes comuns. São “naturais”.

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

+--------------------------------+------+| 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)

Junção natural

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

Exemplo anterior com junção natural (cont.)

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

Junção natural restrita

Denotamos

pela junção natural restrita a um sub-conjunto de atributos

R *A1, … ,AnS

Em SQL: RJOINSUSING(A1,…,AN)

A1, … , An

Em contraste COUNTRY NATURAL JOIN REGION é uma relação vazia …Porquê?

Junção natural restrita — exemplo

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 |. . .

Para duas relações R eS

Junção externa

definimos a junção externa à esquerda:

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)

Junção externa — exemplos

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.

e finalmente a junção externa completa R S=

Junção externa (cont.)

Analogamente definimos a junção externa à direita

R ⋈C S

{(NULL, . . . , NULL, s[B1], . . . , s[Bn], )∪

∣ s ∈ s ∧ /∃r ∈ R : C(r, s)}

∪R SC

Em SQL: RRIGHTOUTERJOINSON(C)RFULLOUTERJOINSON(C)

Junção externa — exemplos (cont.)

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.

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 |. . .

(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 |. . .

Álgebra relacional e sqledrdo/aulas/bd/teoricas/bd_ar_sql.pdf · funções de agregação em sql...

Documents

word count: 4,268 - jason mitchell's lab...

ny.audubon.org€¦ · christmas bird count decembÇr uaw 5...

helder luís da costa o impacto do aβ alves the impact of...

anexo ii-i guia de contagem de pontos de funÇÃo 1

results of survey about€¦ · poland 30 20,00 20%...

variaÇÃo da composiÇÃo quÍmica em funÇÃo da … ·...

new folklore tradiciones canto... · 2008. 4. 29. ·...

deteksi bakteri coliform, total plate count (tpc) dan ph

sociedade hÍpica paulista 27º torneio ranking shp -...

caracterización hematológica de hicoteas trachemys ... ·...

hemÁcias - cenapro · cilindro no aumento de 400x, e...

início | seguranet...inovação educativa, estando ligado a...

agar plate count apc

horarios - safesc -...

[filezilla 다운로드 [filezilla 다운로드] 1....

total plate count dan cemaran escherichia coli pada … ·...

presentación1 - potencia electromecanica...microsoft...

caracterização de...

ejercicios fogueo. ejercicio fogueo for 1 ¿qué muestra el...

antigo.condsef.org.br€¦ · parágrafo segundo: o direito...