Algèbre relationnelle

Algèbre relationnelle : interrogation de données
BD4
S.B. F.C. N. G.dR.
Licence MIASHS, Master ISIFAR, Paris-Diderot
Janvier 2015
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
1/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
1 / 54
.
Introduction
Le modèle relationnel
▷ Inventé par Codd en 1970
▷ Fondements théoriques basés sur la théorie des relations.
▷ Propose un modèle pour la modélisation et l’interrogation de
données
▷ Facile à traduire dans des langages ”réels” (ex. SQL)
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
2/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
2 / 54
.
Les tables/relations
Un exemple de table
Relations
Example
Table player dans la base tennis
pid
71
72
73
74
75
name
Vince Spadea
Igor Andreev
Jiri Vanek
Benjamin Becker
Dudi Sela
gender
M
M
M
M
M
code
USA
RUS
CZE
GER
ISR
lastname
Spadea
Andreev
Vanek
Becker
Sela
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
4/54
.
.
.
.
.
. . . .
. . . .
firstname
Vince
Igor
Jiri
Benjamin
Dudi
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
4 / 54
.
Les tables/relations
Un exemple de table
Relations
Example
Table player dans la base tennis
pid
71
72
73
74
75
name
Vince Spadea
Igor Andreev
Jiri Vanek
Benjamin Becker
Dudi Sela
gender
M
M
M
M
M
code
USA
RUS
CZE
GER
ISR
lastname
Spadea
Andreev
Vanek
Becker
Sela
firstname
Vince
Igor
Jiri
Benjamin
Dudi
Une table comme player est un tableau à deux dimensions, chaque
ligne ou tuple contient des informations concernant un individu (ici un
joueur de tennis professionnel. Ces informations sont des valeurs
d’attributs. Ici les attributs qui définissent le schéma de la table sont pid,
name, gender, code, last_name, first_name. Dans la vie d’un table, on ajoute,
on supprime et on modifie souvent les lignes d’une table. On modifie
beaucoup plus rarement les attributs.
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
4/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
4 / 54
.
Les tables/relations
Un exemple de table
Schema de la table player
Table "tennis.player"
Column
pid
name
gender
code
lastname
firstname
Indexes :
"Playerpkey"
Type
integer
character
character
character
character
character
varying(30)
varying(1)
varying(3)
varying
varying
Modifiers
not null
default NULL ::character varying
default NULL ::character varying
default NULL ::character varying
PRIMARY KEY, btree (pid)
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
5/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
5 / 54
.
Les tables/relations
Formalisation
Un peu de formalisation
- Domaine : ensemble dénombrable d’éléments
Example
entiers, chaines de caractères, date, ...
- Un domaine peut se définir aussi bien par extension (en donnant la
liste de toutes les valeurs possibles) ou par intention (en en donnant
une propriété caractéristique).
- Produit Cartésien : d’une liste de domaines D1 , D2 , . . . , Dk , noté
D1 × D2 × · · · × Dk , est ensemble des k-uplets (ou tuples)
(v1 , v2 , . . . , vk )
où, pour chaque i, vi ∈ Di .
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
6/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
6 / 54
.
Les tables/relations
Formalisation
Relations
- Relations : sous-ensemble de D1 × D2 × · · · × Dk où D1 , ..., Dk sont des
domaines.
Example
D1 = {1, 2, 5}, D2 = {2, 4}.
D1 × D2 = {(1, 2), (1, 4), (2, 2), (2, 4), (5, 2), (5, 4)}.
R = {(1, 2), (1, 4), (5, 2), (5, 4)}.
On peut représenter R par le tableau :
1
1
5
5
2
4
2
4
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
7/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
7 / 54
.
Les tables/relations
Cardinalité et arité
Relations
Cardinalité d’une relation R
nombre de tuples (lignes) de R.
Example
Si R = D1 × D2 alors |R| = |D1 | × |D2 |.
Arité d’une relation R
Nombre de colonnes de la relation R (nombre d’attributs qui
constituent chaque tuple de R)
On parle parfois de degré d’une relation.
Example
Soit R ⊆ D1 × D2 × · · · × Dk ,
Arité de R = longueur d’un tuple de R = k
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
8/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
8 / 54
.
Les tables/relations
Cardinalité et arité
Relations
- Un domaine peut apparaitre plusieurs fois dans la définition d’une
relation.
- Attribut : nom/rôle de la colonne. Précise la sémantique de celle-ci.
Pas deux attributs identiques dans une table.
- Schéma d’une relation : liste des attributs et domaines de la relation.
Peut se noter
R(A1 : D1 , A2 : D2 , . . . , Ak : Dk )
où Ai : attribut et Di : domaine.
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
9/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
9 / 54
.
Les tables/relations
Clés et références
Relations
Example
Table player dans la base tennis
pid
71
72
73
74
75
name
Vince Spadea
Igor Andreev
Jiri Vanek
Benjamin Becker
Dudi Sela
gender
M
M
M
M
M
code
USA
RUS
CZE
GER
ISR
lastname
Spadea
Andreev
Vanek
Becker
Sela
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
10/54
.
.
.
.
.
. . . .
. . . .
firstname
Vince
Igor
Jiri
Benjamin
Dudi
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
10 / 54
.
Les tables/relations
Clés et références
Clé, références, etc
- La clé d’une relation est un ensemble minimal d’attributs dont la
valeur détermine complètement un tuple.
Example
Ville(code_postal, nom, population)
la valeur de Code_postal détermine entièrement un tuple.
- Une référence ou (clé étrangère) est un (groupe d’) attribut dont les
valeurs sont prises parmi celles d’une clé d’une autre table.
Example
Ville(code_postal, nom, population)
Département(numéro, nom, population, CP_préfecture)
la valeur de Code_préfecture fait référence au Code_postal de sa
préfecture qui est une ville.
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
11/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
11 / 54
.
Les tables/relations
Clés et références
Schema de country_codes
Table "tennis.countrycodes"
Type
Modifiers
Column
code
character varying(3)
not null
country
character varying(100)
default NULL ::character varying
Indexes :
"CountryCodespkey" PRIMARY KEY, btree (code)
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
13/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
13 / 54
.
Les tables/relations
Clés et références
Schema de country_codes
Table "tennis.countrycodes"
Type
Modifiers
Column
code
character varying(3)
not null
country
character varying(100)
default NULL ::character varying
Indexes :
"CountryCodespkey" PRIMARY KEY, btree (code)
Identifiant
code est une clé primaire ou identifiant pour la table country_code.
Référence
Dans la table player, c’est une clé étrangère ou référence qui permet de lier un tuple de player à un
tuple de country_code
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
13/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
13 / 54
.
Les tables/relations
Clés et références
Résumé informel
▷ RELATION = TABLE A DEUX DIMENSIONS
▷ (NOM DE) COLONNE = ATTRIBUT
▷ EN-TÊTE DU TABLEAU = SCHEMA DE LA RELATION (description du type,
définition intentionnelle)
▷ LIGNE = TUPLE
▷ ENSEMBLE DES LIGNES = CONTENU DE LA RELATION (définition
extensionnelle)
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
14/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
14 / 54
.
Algèbre relationnelle
Algèbre relationnelle
Une algèbre
C’est un ensemble E munis d’opérations internes ⊤, ⊥, +, ×, . . .. Une
opération prend en argument des (ou un) éléments de l’ensemble et
produit un élément de l’ensemble.
Exemple : R muni de +, ×, −, /, . . .
Algèbre relationnelle
C’est une algèbre qui opère sur l’ensemble des tables/relations.
Dans un SGBD
le langage de manipulation de données (SQL) met en oeuvre
l’algèbre relationnelle (et bien d’autres choses).
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
15/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
15 / 54
.
Opérateurs de base
Liste des opérateurs
Liste des opérateurs
L’algèbre relationnelle comme Langage de Manipulation de données
(LMD).
Opérateurs de base
- Union ∪
- Intersection ∩
- Différence \
- Projection Π
- Sélection σ
- Produit cartésien (déjà vu) ×
Manipulent des relations pour produire d’autres relations (le résultat).
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
16/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
16 / 54
.
Opérateurs de base
Opérateurs unaires
Projection Π
▷ La projection d’une relation R de schéma R(A1 , . . . , Ak ) sur les
attributs Ai1 , . . . , Aip est la relation S
- de schéma S(Ai1 , . . . , Aip )
- dont les tuples sont obtenus par élimination des attributs non mentionnés
dans Ai1 , . . . , Aip (et par élimination des doublons).
▷ On note S = πAi
1
,...,Aip (R).
Élimination des doublons
car une projection peut produire plusieurs fois le même tuple.
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
17/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
17 / 54
.
Opérateurs de base
Opérateurs unaires
Schema de tournament
Table ”tennis.tournament”
Column
Type
Modifiers
tid
integer
not null
name
character varying
location
character varying
startdate date
enddate
date
numrounds integer
ttype
character varying
surface
character varying
Indexes :
"Tournamentpkey" PRIMARY KEY, btree (tid)
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
18/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
18 / 54
.
Opérateurs de base
Opérateurs unaires
Tuples de tournament
tid
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
name
Australian Open
Australian Open
French Open
French Open
US Open
US Open
Wimbledon
Wimbledon
Heineken Open
Heineken Open
Brasil Open 2007
Brasil Open 2007
Countrywide Classic
Countrywide Classic
BMW Open
location
Melbourne, Australia
Melbourne, Australia
Roland Garros, Paris
Roland Garros, Paris
Flushing Meadows, NY
Flushing Meadows, NY
London, Great Britain
London, Great Britain
Auckland, New Zealand
Auckland, New Zealand
Costa do Sauipe, Brasil
Costa do Sauipe, Brasil
Los Angeles, CA, USA
Los Angeles, CA, USA
Munich, Germany
startdate
2007-01-15
2007-01-15
2007-05-26
2007-05-26
2007-08-27
2007-08-27
2007-06-25
2007-06-25
2007-01-08
2007-01-08
2007-02-12
2007-02-12
2007-07-16
2007-07-16
2007-04-30
enddate
2007-01-28
2007-01-28
2007-06-10
2007-06-10
2007-09-09
2007-09-09
2007-07-08
2007-07-08
2007-01-14
2007-01-14
2007-02-18
2007-02-18
2007-07-22
2007-07-22
2007-05-06
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
19/54
rounds
7
6
7
6
7
6
7
6
5
4
5
4
5
4
5
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
ttype
Singles
Doubles
Singles
Doubles
Singles
Doubles
Singles
Doubles
Singles
Doubles
Singles
Doubles
Singles
Doubles
Singles
. . . .
. . . .
.
surface
Hard
Hard
Clay
Clay
Hard
Hard
Grass
Grass
Hard
Hard
Clay
Clay
Hard
Hard
Clay
.
.
.
.
.
.
.
.
Janvier 2015
19 / 54
.
Opérateurs de base
Opérateurs unaires
Projection
Projection de la table tournament sur les colonnes location et surface.
location
Melbourne, Australia
Auckland, New Zealand
Flushing Meadows, NY, USA
Munich, Germany
London, Great Britain
...
surface
Hard
Hard
Hard
Clay
Grass
...
πlocation,surface tournament
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
20/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
20 / 54
.
Opérateurs de base
Opérateurs unaires
Sélection
- La sélection d’une relation R par une condition C est une relation S
- de même schéma que R (même arité)
- dont les tuples sont ceux de R qui satisfont la condition C (cardinalité de S
≤ cardinalité de R)
- On note S = σC (R).
- La condition C s’exprime
- à l’aide des noms d’attributs de la relation ou de constantes (pour les
opérandes)
- peut utiliser des opérateurs arithmétique de comparaisons (=, ̸=, ≤, ≥, <, >)
ainsi que des connecteurs logique (¬, ∧, ∨)..
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
22/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
22 / 54
.
Opérateurs de base
Opérateurs unaires
Tuples de tournament
tid
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
name
Australian Open
Australian Open
French Open
French Open
US Open
US Open
Wimbledon
Wimbledon
Heineken Open
Heineken Open
Brasil Open 2007
Brasil Open 2007
Countrywide Classic
Countrywide Classic
BMW Open
location
Melbourne, Australia
Melbourne, Australia
Roland Garros, Paris
Roland Garros, Paris
Flushing Meadows, NY
Flushing Meadows, NY
London, Great Britain
London, Great Britain
Auckland, New Zealand
Auckland, New Zealand
Costa do Sauipe, Brasil
Costa do Sauipe, Brasil
Los Angeles, CA, USA
Los Angeles, CA, USA
Munich, Germany
startdate
2007-01-15
2007-01-15
2007-05-26
2007-05-26
2007-08-27
2007-08-27
2007-06-25
2007-06-25
2007-01-08
2007-01-08
2007-02-12
2007-02-12
2007-07-16
2007-07-16
2007-04-30
enddate
2007-01-28
2007-01-28
2007-06-10
2007-06-10
2007-09-09
2007-09-09
2007-07-08
2007-07-08
2007-01-14
2007-01-14
2007-02-18
2007-02-18
2007-07-22
2007-07-22
2007-05-06
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
23/54
rounds
7
6
7
6
7
6
7
6
5
4
5
4
5
4
5
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
ttype
Singles
Doubles
Singles
Doubles
Singles
Doubles
Singles
Doubles
Singles
Doubles
Singles
Doubles
Singles
Doubles
Singles
. . . .
. . . .
.
surface
Hard
Hard
Clay
Clay
Hard
Hard
Grass
Grass
Hard
Hard
Clay
Clay
Hard
Hard
Clay
.
.
.
.
.
.
.
.
Janvier 2015
23 / 54
.
Opérateurs de base
Opérateurs unaires
Sélection sur tournament
Sélection des tuples pour lesquels l’attribut ttype vaut ’Singles’
tid
14
12
6
8
0
10
2
4
name
BMW Open
Countrywide Classic
Wimbledon
Heineken Open
Australian Open
Brasil Open 2007
French Open
US Open
location
Munich, Germany
Los Angeles, CA,
London, Great Britain
Auckland, New Zealand
Melbourne, Australia
Costa do Sauipe, Brasil
Roland Garros, Paris
Flushing Meadows, NY, USA
startdate
2007-04-30
2007-07-16
2007-06-25
2007-01-08
2007-01-15
2007-02-12
2007-05-26
2007-08-27
enddate
2007-05-06
2007-07-22
2007-07-08
2007-01-14
2007-01-28
2007-02-18
2007-06-10
2007-09-09
rounds
5
5
7
5
7
5
7
7
ttype
Singles
Singles
Singles
Singles
Singles
Singles
Singles
Singles
surface
Clay
Hard
Grass
Hard
Hard
Clay
Clay
Hard
σttype=′ Singles′ tournament
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
24/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
24 / 54
.
Opérateurs de base
Opérateurs unaires
Renommage
Renommage d’une table
consiste à renommer explicitement certains attributs.
On peut souhaiter renommer des attributs pour
- mettre en correspondance deux schémas (préalable à certaines
opérations binaires)
- traduire une base d’usage internationale (localisation)
Renommage dans country_codes
pays
Afghanistan
Netherlands Antilles
Albania
Algeria
Andorra
code
AFG
AHO
ALB
ALG
AND
ρcountry→pays country_codes
Cette opération ne change rien au contenu de la table, elle définit un
nouveau schéma, avec un nouveau nom d’attribut
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
26/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
26 / 54
.
Opérateurs de base
Opérateurs binaires
Union et intersection
L’union et l’intersection sont des opérations portant sur deux relations R1
et R2 de même schéma.
- T = R1 ∪ R2 est constituée des tuples appartenant à R1 ou à R2 mais
pas nécessairement aux deux
- T = R1 ∩ R2 est constituée des tuples appartenant à R1 et à R2
Le schémas de R1 ∪ R2 , R1 ∩ R2 est égal à celui de R1 (et à celui de R2 !)
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
27/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
27 / 54
.
Opérateurs de base
Opérateurs binaires
Différence ensembliste
- La différence de deux relations R1 et R2 (de même schéma) est une
relation T de même schéma constituée des tuples appartenant à R1
et n’appartenant pas à R2 .
- On note T = R1 − R2 .
- Opération non commutative : R1 − R2 ̸= R2 − R1 (en général).
Example
On veut calculer la liste des joueurs qui ont participé à Roland Garros
(French Open) mais pas à Wimbledon.
Il faut (et il suffit de) calculer la liste des pid des joueurs qui ont participé
à Roland Garros, la liste des pid des joueurs qui ont participé à Wimbledon
et faire la différence.
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
29/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
29 / 54
.
Opérateurs de base
Opérateurs binaires
Produit Cartésien
- Déjà défini : construit des ”produits” de relation.
- Le schéma de R1 × R2 est la concaténation des schémas de R1 et R2
- arité de R1 × R2 = arité de R1 × arité de R2
- En algèbre relationnelle, cette opération est commutative :
R1 × R2 = R2 × R1
- cardinalité de R1 × R2 = cardinalité de R1 × cardinalité de R2
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
30/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
30 / 54
.
Opérateurs de base
Opérateurs binaires
Résumé
Algèbre relationnelle
- opérations ensemblistes classiques.
- Projection : élimine des colonnes
- Sélection : élimine des lignes
Pour interroger une BD, on combine ces opérateurs :
Example
πnum (σdest=′ Lyon′ (Train))
Numéro des trains dont la ville de destination est ’Lyon’.
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
32/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
32 / 54
.
Opérations complémentaires : les jointures
Les jointures
On va définir des opérations pratiques pour la manipulation de
données : les jointures.
- Jointure
- Jointure naturelle
- θ-jointure et équi-jointure
- Les jointures externes
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
33/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
33 / 54
.
Opérations complémentaires : les jointures
Jointure
La jointure T = R1 ▷◁C R2 de deux relations R1 et R2 sous la condition C
est la relation T
- de schéma : la concaténation des schéma de R1 et R2
- formée des tuples du produit cartésien R1 × R2 qui satisfont la
condition C
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
34/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
34 / 54
.
Opérations complémentaires : les jointures
Jointure
π... (player ▷◁C country_codes)
lastname
Spadea
Andreev
Vanek
Becker
Sela
Srichaphan
Gicquel
Jones
Tsonga
Lapentti
firstname
Vince
Igor
Jiri
Benjamin
Dudi
Paradorn
Marc
Alun
Jo-Wilfried
Nicolas
country
United States
Russia
Czech Republic
Germany
Israel
Thailand
France
Australia
France
Ecuador
code
USA
RUS
CZE
GER
ISR
THA
FRA
AUS
FRA
ECU
gender
M
M
M
M
M
M
M
M
M
M
Jointure de player et de country_codes sur la condition player.code =
country_codes.code, puis projection sur last_name, first_name, country, code,
gender
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
35/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
35 / 54
.
Opérations complémentaires : les jointures
Équi-Jointure et θ-jointure
Équi-jointure
La condition de jointure porte sur des attributs de types comparables et
impose l’égalité.
Dans l’exemple précédent, la jointure est une équi-jointure.
θ-jointure
condition de jointure entre attributs de types comparables et avec un
opérateur différents de l’égalité i.e. {<, >, ≤, ≥, ̸=}.
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
37/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
37 / 54
.
Opérations complémentaires : les jointures
Jointure naturelle
R1 d’attributs A1 , .., Ak , B1 , ..., Bh
R2 d’attributs A1 , .., Ak , C1 , ..., Cl
A1 , ..., Ak : attributs communs.
- La jointure naturelle sur deux relations R1 et R2 est la relation
- d’attributs A1 , .., Ak , B1 , ..., Bh , C1 , ..., Cl
- définie par :
σA1 ,..,Ak ,B1 ,...,Bh ,C1 ,...,Cl (R1 ▷◁C R2 )
où C = R1 .A1 = R2 .A1 ∧ . . . ∧ R1 .Ah = R2 .Ah
Autrement dit, on réalise une équi-jointure sur tous les attributs
communs et on ne garde qu’un seul ”exemplaire” de ces attributs
communs par projection.
- On note : R1 ▷◁ R2 .
- Lorsqu’on prend seulement une partie des attributs communs, on
spécifie le nom de ceux-ci en indice.
Example
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
La jointure entre player et country_codes est une jointure naturelle
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
38/54
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
38 / 54
.
Opérations complémentaires : les jointures
Représentation graphique - arbre de requête
πname,country
σgender=′ female′
▷◁code
player country_codes
Pour : πname,country σgender=′ female′ (player ▷◁code country_codes)
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
41/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
41 / 54
.
Encore des opérations...
Jointure externe
- Perte d’information dans jointure naturelle : les tuples ne satisfaisant
pas la condition disparaissent.
- On ajoute ”symboliquement” une ligne dont les valeurs sont vides (ou
avec valeur spéciale NULL) pour garder les tuples initiaux ”non
satisfaisants” après la jointure.
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
42/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
42 / 54
.
Encore des opérations...
Jointure externe (définition)
Jointure externe à droite ▷◁ext
entre deux tables R et S, sous la condition C, est une table :
- de même schéma que R ▷◁C S
- contenant les tuples de R ▷◁C S ...
- et pour chaque tuple t2 de S tel qu’il n’existe pas de tuple t1 tel que
(t1 , t2 ) vérifie la condition C, un tuple de la forme (NULL, t2 ) où les
attributs provenant de R ne sont pas renseignés ...
- Notation R ▷◁ext
C S
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
43/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
43 / 54
.
Encore des opérations...
Jointure externe
Jointure externe
Jointure externe à droite entre player et country_code avec une
equi-jointure sur l’attribut commun code, puis sélection sur la condition
last_name est nulle et projection sur quelques attributs provenant des
tables player et country_codes (10 premiers résultats affichés).
lastname
firstname
country
Venezuela
Tajikistan
Iraq
Côte d Ivoire
Cameroon
Malaysia
Egypt
Indonesia
Organization of Eastern Caribbean States (OECS) (Davis Cup Entity)
Saudi Arabia
code
VEN
TJK
IRQ
CIV
CMR
MAS
EGY
INA
ECA
KSA
gender
A noter
Dans un SGBD, les jointures externes sont un moyen commode pour
réaliser la différence entre tables.
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
45/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
45 / 54
.
Encore des opérations...
Division
Division
Division de R par S
- relation R de schéma R(A1 , A2 , . . . , Ak )
- relation S de schéma S(Ap+1 , . . . , Ak ) (Schema de S ⊂ Schema de R)
La division (ou le quotient) de R par S est une relation T de schéma
T (A1 , . . . , Ap ) formée des tuples qui, concaténés à chaque tuple de S,
donnent un tuple de R.
On note
T =R÷S
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
46/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
46 / 54
.
Encore des opérations...
Division
Tables played_in et registration
Dans la base tennis, on trouve les tables registration et played_in, qui
permettent de déterminer quels joueurs se sont inscrits à quel tournoi.
Chaque tuple de played_in représente l’inscription d’un joueur ou d’une
équipe de double à un tournoi
Column
tid
registrnum
seed
Type
integer
integer
integer
Modifiers
not null
not null
- L’attribut tid indique le tournoi concerné (un tuple de tournament
identifié par son attribut tid.
- L’attribut registration est un numéro d’inscription
- L’attribut seed, quand il est renseigné, permet de repérer les têtes de
série.
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
47/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
47 / 54
.
Encore des opérations...
Division
Table registration
Si la base tennis ne concernait que des compétitions en simple (single),
nous pourrions représenter les inscriptions de manière très simple : par
des couples (tid,pid). Pour pouvoir représenter de façon homogène les
inscriptions aux tournois en simple et en double, on a choisi d’utiliser
deux tables played_in et registration.
Table "tennis.registration"
Column
registrnum
pid
Type
integer
integer
Modifiers
not null
not null
Pour chaque inscription à un tournoi (tuple de played_in identifié par
registrnum), on trouvera un ou deux tuples dans registration. Ce ou ces
tuples permettent de savoir quel(s) joueur(s) portent quels numéros
d’inscription.
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
48/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
48 / 54
.
Encore des opérations...
Division
Division : les joueurs qui ont participé à tous les
tournois du grand schlem
Le grand schlem est formé par quatre tournois : Autralian Open, French
Open, Wimbledon et US Open.
Attention
Cette question n’est pas la même que la question :
quels sont les joueurs qui ont participé à au moins un tournoi du grand
schlem ?
Pourtant ...
Nous allons commencer par construire une requête qui correspond
presque à cette question intermédiaire.
Nous calculons les couples (tid,pid) qui correspondent à une inscription
à un tournoi du grand schlem (table GSI).
Pour répondre à la question de départ, il suffit de diviser GSI par sa
projection sur tid.
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
49/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
49 / 54
.
Encore des opérations...
Division
Division : les joueurs qui ont participé à tous les
tournois du grand schlem (...)
Calcul des tid des tournois du grand schlem
GS ← πtid σname∈(′ FrenchOpen′ ,...,′ Wimbledon′ ) tournament
Calcul des couples (tid,pid) correspondant aux jours participant à au
moins un tournoi du grand schlem
GSI ← πtid,pid (GS ▷◁ played_in ▷◁ registration)
Les pid des joueurs ayant participé à tous les tournois du grand schlem
R ← GSI ÷ (πtid GSI)
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
50/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
50 / 54
.
Les opérateurs de base et les autres
Interdéfinissabilité des opérateurs
L’union, la différence, le produit cartésien, la sélection et la
projection sont suffisants pour définir tous les opérateurs que l’on a
vu.
Mais avoir un panel plus large d’opérateurs simplifie l’écriture des
requêtes.
Quelques définitions
- Pour la Jointure : R ▷◁C S ≡ σC (R × S)
- Pour l’intersection : A ∩ B = A ∪ B − ((B − A) ∪ (A − B))
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
53/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
53 / 54
.
Les opérateurs de base et les autres
Définition de la division
- R de schéma R(A1 , A2 , . . . , Ak ), S de schéma S(Ap+1 , . . . , Ak )
est la relation T = R ÷ S de schéma T (A1 , . . . , Ap ).
−
πA1 ,...,Ap
πA1 ,...,Ap
R
−
×
R
πA1 ,...,Ap S
R
.
BD4 (Licence MIASHS, Master ISIFAR, Paris-Diderot)
Algèbre relationnelle
54/54
.
.
.
.
.
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
Janvier 2015
54 / 54
.