Base de données
Séance 7
Algèbre relationnelle et SQL :
- Opérations ensemblistes
- Jointures
Algèbre relationnelle et SQL
Opérations ensemblistes (binaires) :
1. Union
2. Intersection
3. Différence
4. Division
5. Produit cartésien
Union
Opération portant sur deux relations de même
schéma R1 et R2, consistant à construire une
relation R3
de même schéma
ayant pour enregistrements ceux appartenant à R1 ou R2
ou aux deux relations.
Résultat
R3 = R1 U R2
U
Relation 1 Relation 2
Union : exemple
VINS Cru Mill Région VINS Cru Mill Région
CHENAS 1983 BEAUJOLAIS CHENAS 1983 BEAUJOLAIS
TOKAY 1980 ALSACE TOKAY 1980 ALSACE
TAVEL 1986 RHONE CHABLIS 1986 BOURGOGNE
ST-EMILION 1987 BORDELAIS
VINS Cru Mill Région
CHENAS 1983 BEAUJOLAIS
TOKAY 1980 ALSACE
TAVEL 1986 RHONE
R1 U R2 = CHABLIS 1986 BOURGOGNE
ST-EMILION 1987 BORDELAIS
Union en SQL
Même nombre de colonnes
Même type de colonnes
Opération ensembliste : pas de doublons
Exemple : nom et prénom des étudiants
et professeurs de l’EISTI
SELECT nom, prenom FROM etudiant
UNION
SELECT nom, prenom FROM professeur
Intersection
Opération portant sur deux relations de même
schéma R1 et R2, consistant à construire une
relation R3 :
de même schéma
ayant pour enregistrements ceux appartenant aux deux
relations.
Résultat
R3 = R1 Π R2 Π
Relation 1 Relation 2
Intersection : exemple
VINS Cru Mill Région VINS Cru Mill Région
CHENAS 1983 BEAUJOLAIS CHENAS 1983 BEAUJOLAIS
TOKAY 1980 ALSACE TOKAY 1980 ALSACE
TAVEL 1986 RHONE CHABLIS 1986 BOURGOGNE
ST-EMILION 1987 BORDELAIS
VINS Cru Mill Région
CHENAS 1983 BEAUJOLAIS
R1 Π R2 = TOKAY 1980 ALSACE
Intersection en SQL
Exemple :
nom et prénom des professeurs étudiants :
SELECT nom, prenom FROM etudiant
INTERSECT SELECT nom, prenom FROM professeur;
Différence
Opération portant sur deux relations de même
schéma R1 et R2, consistant à construire une
relation R3 :
de même schéma
ayant pour enregistrements ceux appartenant à R1 et
n’appartenant pas à R2.
Résultat
R3 = R1 – R2 -
Relation 1 Relation 2
Exemple
VINS Cru Mill Région VINS Cru Mill Région
CHENAS 1983 BEAUJOLAIS CHENAS 1983 BEAUJOLAIS
TOKAY 1980 ALSACE TOKAY 1980 ALSACE
TAVEL 1986 RHONE CHABLIS 1986 BOURGOGNE
ST-EMILION 1987 BORDELAIS
VINS Cru Mill Région
TAVEL 1986 RHONE
R1 - R2 =
Différence en SQL
Exemple :
nom et prénom des étudiants qui ne sont pas
professeurs :
SELECT nom, prenom FROM etudiant
MINUS SELECT nom, prenom FROM professeur;
Division
Opération portant sur deux relations R1 et R2, le schéma
de R2 est inclus dans celui de R1.
Construit une relation dont le schéma
contient tous les attributs de R1
qui ne sont pas dans R2. Résultat
Le résultat est formé de tous les
n-uplets qui, concaténés à
chacun des n-uplets du ÷
diviseur R2, donne toujours
un n-uplet du dividende R1
R1 R2
R3 = R1 ÷ R2
Division : Exemple
INSCRIT Etudiant Matiere MATIERE Matiere
DERAY BDD1 BDD1
DUPONT BDD1 JAVA
DUPONT ELEC
DERAY JAVA
DURAND JAVA
DIVISION Etudiant
DERAY
R1 ÷ R2 =
Division en SQL
Pas d’équivalent direct en SQL
=> cours 8 : requêtes complexes !
Produit cartésien
Opération portant sur deux relations R1 et R2
consistant à construire une relation R3 :
ayant pour schéma la concaténation de ceux des relations
opérandes
et pour enregistrements toutes les combinaisons des
enregistrements des relations opérandes.
Résultat
R3 = R1 x R2
x
Relation 1 Relation 2
Produit cartésien : exemple
VINS Cru Mill Région BUVEURS Nom Prénom
CHENAS 1983 BEAUJOLAIS Dupont Jacques
TOKAY 1980 ALSACE Martin Jean
TAVEL 1986 RHONE
VB Cru Mill Région Nom Prénom
CHENAS 1983 BEAUJOLAIS Dupont Jacques
TOKAY 1980 ALSACE Dupont Jacques
TAVEL 1986 RHONE Dupont Jacques
R1 x R2 = CHENAS 1983 BEAUJOLAIS Martin Jean
TOKAY 1980 ALSACE Martin Jean
TAVEL 1986 RHONE Martin Jean
Produit cartésien
En SQL, le produit cartésien s’obtient en faisant un
select sur deux tables :
SELECT * FROM table1, table2;
Le produit cartésien présente peu d’intérêt en soi :
lien entre les données ?
jointure !
Jointure à partir du produit cartésien
Pour effectuer le produit A x B, on
confronte chaque enregistrement de A à
tous les enregistrements de B.
18
Jointure à partir du produit cartésien
Toutes les combinaisons ne sont pas
pertinentes.
Mobilier
Nombre de pieds Table 4
Table 4
Chaise 3
Tabouret 2 Chaise 4
Echelle
x
Tabouret 3
On doit indiquer comment
sélectionner les bonnes Echelle 2
19
1) Jointure avec pivot
La jointure de relations (ou tables) s'effectue
en effectuant une restriction sur le produit
cartésien à l’aide d’un pivot.
σA.c1=B.c2(A x B)
SELECT * FROM A, B
WHERE A.c1 = B.c2;
Le pivot introduit une contrainte qui réduit les
croisements possibles.
20
Croiser plus de deux tables
Pour effectuer des jointures
multiples sur plus de deux tables, on
étend le pivot :
σ<PIVOT1> and <PIVOT2>(R1 x R2 x R3)
SELECT * FROM T1, T2, T3
WHERE <PIVOT1> AND <PIVOT2>;
21
Jointure sur un identifiant : ambiguïtés
Attention aux ambigüités lorsqu’un attribut du
pivot est présent dans les 2 relations
σid_manager = id(employe x manager)
id ?
SELECT * FROM employe, manager employe
ou manager
WHERE id_manager = id;
Préfixer ou renommer :
σid_manager = [Link](employe x manager)
SELECT * FROM employe, manager
WHERE id_manager = [Link];
22
2) Opération de jointure : définition
Opération consistant à rapprocher selon une
condition les enregistrements de deux relations R1
et R2
Produit une relation R3 qui contient l’ensemble de tous
les enregistrements obtenus en concaténant un
enregistrement de R1 et un enregistrement de R2
vérifiant la condition de rapprochement.
Equivalente à la restriction avec pivot sur le produit
cartésien
Jointure
Notations algébriques :
Résultat
R3 = R1 Ai op Bi R2
Ξ σAi op Bi (R1 x R2)
Ai op Bi
Relation 1 Relation 2
SQL :
SELECT * FROM T1 [INNER] JOIN T2
ON [Link] op [Link];
Différentes jointures
Équi-jointure : opérateur =
θ-jointure : opérateurs <, ≤, >, ≥, <>
Jointure naturelle :
La jointure se fait sur les attributs de même
nom (avec les mêmes types)
Les attributs de la relation résultat sont l’union
des attributs de R1 et R2
Notations
R3 = R1 R2
SELECT * FROM T1 NATURAL JOIN T2
La jointure naturelle
Pivot naturel
Tab1 Tab2
col1 col12 col1 col22
a x Lignes avec a 1
correspondance
b y b 2
Lignes sans
c z d 3
correspondance
26
Jointure naturelle vs équi-jointure
tab1 tab2 tab1 tab2
SELECT *
col1 col12 col1 col22
FROM tab1
a x a 1 NATURAL JOIN tab2;
b y b 2
c z d 3 ax1
by2
tab1 tab1.col1=tab2.col1 tab2
SELECT * FROM tab1 t1 axa1
JOIN tab2 t2 byb2
ON t1.col1 = t2.col1;
27
La jointure naturelle : exemple 1
VINS Cru Mill Qualité
VOLNAY 1983 A
VOLNAY 1979 B
CHABLIS 1983 A
JULIENAS 1986 C
LOCALISATION Cru Région QualMoy
VOLNAY Bourgogne A
CHABLIS Bourgogne A
CHABLIS Californie B
VINSREG Cru Mill Qualité Région QualMoy
VOLNAY 1983 A Bourgogne A
VOLNAY 1979 B Bourgogne A
CHABLIS 1983 A Bourgogne A
CHABLIS 1983 A Californie B
La jointure naturelle : exemple 2
Liste des noms de producteur ayant récolté
du vin :
recolte producteur
SELECT nom
FROM recolte NATURAL JOIN producteur ;
29
Jointure interne ou pivot ?
tab1 tab2 SELECT *
col11 col12 col21 col22 FROM tab1 t1,tab2 t2
WHERE t1.col11 =
a x a 1 t2.col21;
b y b 2
c z d 3
axa1
SELECT * FROM tab1
byb2
INNER JOIN tab2
ON tab1.col11 =
tab2.col21
30
Exemple complet
Individu(num_ind, nom, prenom)
Jouer(num_ind, num_film, role)
Film(num_film, num_ind, titre, genre, annee)
Projection(num_cine, num_film, pdate)
Cinéma(num_cine, nom, adresse)
Quels sont les cinémas qui ont projeté des
films dans lesquels a joué Travolta ?
Exemple complet
Individu(num_ind, nom, prenom)
Jouer(num_ind, num_film, role)
Film(num_film, num_ind, titre, genre, annee)
Projection(num_cine, num_film, pdate)
Cinéma(num_cine, nom, adresse)
Quels sont les cinémas qui ont projeté des
films dans lesquels a joué Travolta ?
Exemple complet
Individu(num_ind, nom, prenom)
Jouer(num_ind, num_film, role)
Film(num_film, num_ind, titre, genre, annee)
Projection(num_cine, num_film, pdate)
Cinéma(num_cine, nom, adresse)
Quels sont les cinémas qui ont projeté des
films dans lesquels a joué Travolta ?
Exemple complet
Individu(num_ind, nom, prenom)
Jouer(num_ind, num_film, role)
Film(num_film, num_ind, titre, genre, annee)
Projection(num_cine, num_film, pdate)
Cinéma(num_cine, nom, adresse)
Quels sont les cinémas qui ont projeté des
films dans lesquels a joué Travolta ?
Exemple complet
Individu(num_ind, nom, prenom)
Jouer(num_ind, num_film, role)
Film(num_film, num_ind, titre, genre, annee)
Projection(num_cine, num_film, pdate)
Cinéma(num_cine, nom, adresse)
Quels sont les cinémas qui ont projeté des
films dans lesquels a joué Travolta ?
Exemple complet
Individu(num_ind, nom, prenom)
Jouer(num_ind, num_film, role)
Film(num_film, num_ind, titre, genre, annee)
Projection(num_cine, num_film, pdate)
Cinéma(num_cine, nom, adresse)
Quels sont les cinémas qui ont projeté des
films dans lesquels a joué Travolta ?
1. Jointure avec pivot
Individu(num_ind, nom, prenom)
Jouer(num_ind, num_film, role)
Film(num_film, num_ind, titre, genre, annee)
Projection(num_cine, num_film, pdate)
Cinéma(num_cine, nom, adresse)
Quels sont les cinémas qui ont projeté des films dans
lesquels a joué Travolta ?
1. Jointure avec pivot
Individu(num_ind, nom, prenom)
Jouer(num_ind, num_film, role)
Film(num_film, num_ind, titre, genre, annee)
Projection(num_cine, num_film, pdate)
Cinéma(num_cine, nom, adresse)
Quels sont les cinémas qui ont projeté des films dans
lesquels a joué Travolta ?
1. Jointure avec pivot
Individu(num_ind, nom, prenom)
Jouer(num_ind, num_film, role)
Film(num_film, num_ind, titre, genre, annee)
Projection(num_cine, num_film, pdate)
Cinéma(num_cine, nom, adresse)
Quels sont les cinémas qui ont projeté des films dans
lesquels a joué Travolta ?
1. Jointure avec pivot
Individu(num_ind, nom, prenom)
Jouer(num_ind, num_film, role)
Film(num_film, num_ind, titre, genre, annee)
Projection(num_cine, num_film, pdate)
Cinéma(num_cine, nom, adresse)
Quels sont les cinémas qui ont projeté des films dans
lesquels a joué Travolta ?
1. Jointure avec pivot
Individu(num_ind, nom, prenom)
Jouer(num_ind, num_film, role)
Film(num_film, num_ind, titre, genre, annee)
Projection(num_cine, num_film, pdate)
Cinéma(num_cine, nom, adresse)
Quels sont les cinémas qui ont projeté des films dans
lesquels a joué Travolta ?
1. Jointure avec pivot
SELECT DISTINCT [Link]
FROM cinema c, projection p, film f, jouer j, individu i
WHERE c.num_cine = p.num_cine
AND p.num_film = f.num_film
AND f.num_film = j.num_film
AND j.num_ind = i.num_ind
AND [Link] = 'Travolta';
4 pivots + 1 sélection + 1 projection
2. Jointure interne
SELECT DISTINCT [Link]
FROM cinema c
JOIN projection p ON c.num_cine = p.num_cine
JOIN film f ON p.num_film = f.num_film
JOIN jouer j ON f.num_film = j.num_film
JOIN individu i ON j.num_ind = i.num_ind
WHERE [Link] = 'Travolta';
4 jointures + 1 sélection + 1 projection
3. Jointure naturelle
SELECT DISTINCT nom
FROM cinema NATURAL JOIN projection
NATURAL JOIN film
NATURAL JOIN jouer
NATURAL JOIN individu
WHERE nom = 'Travolta';
Problème : ambiguïtés sur les colonnes qui
portent le même nom, on peut préciser celle(s)
à utiliser avec la clause USING mais à EVITER !
Ambigüité jointure naturelle
Individu(num_ind, nom, prenom)
Jouer(num_ind, num_film, role)
Film(num_film, num_ind, titre, genre, annee)
Projection(num_cine, num_film, pdate)
Cinéma(num_cine, nom, adresse)
Quels sont les cinémas qui ont projeté des films dans
lesquels a joué Travolta ? Donner le nom des cinémas
et le titre des films.
Ambigüité jointure naturelle
Individu(num_ind, nom, prenom)
Jouer(num_ind, num_film, role)
Film(num_film, num_ind, titre, genre, annee)
Projection(num_cine, num_film, pdate)
Cinéma(num_cine, nom, adresse)
Quels sont les cinémas qui ont projeté des films dans
lesquels a joué Travolta ? Donner le nom des cinémas
et le titre des films.
Ambigüité jointure naturelle
Individu(num_ind, nom, prenom)
Jouer(num_ind, num_film, role)
Film(num_film, num_ind, titre, genre, annee)
Projection(num_cine, num_film, pdate)
Cinéma(num_cine, nom, adresse)
Quels sont les cinémas qui ont projeté des films dans
lesquels a joué Travolta ? Donner le nom des cinémas
et le titre des films.
