Algèbre relationnel et SQL

CHAPITRE 6

1
 I – Introduction :
L’algèbre relationnelle consiste en un ensemble d’opérations qui
permettent de manipuler des relations, considérées comme des
ensembles de tuples : on peut ainsi faire l’union ou la différence de
deux relations, sélectionner une partie de la relation, effectuer des
produits cartésiens ou des projections, etc.
La manipulation des données dans le modèle relationnel se fait à l’aide
d’un SGBD. Par la suite on propose d'utiliser le langage SQL (Structured
Query Langage) qui est pis en charge par plusieurs système de gestion
de BD.

2
I- Opérations unaires :
1- La sélection :
Soit R une relation de schéma S = ( A1 , A2 . ... , An ).
Soit A un des attributs de S et a une valeur dans le domaine de A.
On appelle sélection de R selon A = a la relation obtenue en
sélectionnant dans R uniquement les valeurs e de R(i.e. les lignes ou
n-uplets de la table) pour lesquelles : e.A = a.

On note la relation obtenue : σA=a (R).

σP (R): P est une condition booléenne

= , > , <, ≥ , ≤
3
Exemple 1: Matiere
Intitule Annee Semestre Coef
Maths 2 2 4
programmation 1 2 2
Base de données 2 1 3
Anglais 1 2 2

Donnez toutes les matières enseignées ayant un coefficient strict-

ement supérieur à 2.
σCoef > 2 (Matiere)
Intitule Annee Semestre Coef
Maths 2 2 4
Base de données 2 1 3

4
Exemple 2: Matiere
Intitule Annee Semestre Coef
Maths 2 2 4
programmation 1 2 2
Base de données 2 1 2
Anglais 1 2 2
Donnez toutes les matières enseignées en 2 ème année et ayant un
coefficient strictement supérieur à 2.
σ(Annee = 2 ) ET (Coef > 2) (Matiere)
Intitule Annee Semestre Coef
Maths 2 2 4

5
 Traduction de la sélection en langage SQL :

Pour effectuer la sélection σA=a (R), on évalue la requête :

SELECT * FROM R WHERE A = a;

Exemple : Pour obtenir σCoef > 2 (Matiere) ,on évalue la requête :

SELECT * FROM Matiere WHERE Coef > 2 ;

Pour obtenir σ(Annee=2) ET (Coef > 2) (Matiere) ,on évalue la requête :

SELECT * FROM Matiere WHERE Annee=2 AND Coef > 2 ;

6
 2- Projection :

Soit R une relation de schéma S. Soit X un sous-ensemble

d'attributs.
On appelle projection de R selon X la relation notée πX (R)
obtenue en ne gardant que les composantes des valeurs de
R sur les attributs de X.
Exemple: Matiere

Intitule Annee Semestre Coef

Maths 2 1 4
Maths 2 2 4
anglais 1 2 2

Sa projection sur les attributs (Intitulé, Semestre) donne :

π Intitule, Semestre (Matiere)

Intitule Semestre
Maths 1
Maths 2
anglais 2

8
Exemple: Matiere
Intitule Annee Semestre Coef
Maths 2 1 4
Maths 2 2 4
anglais 1 2 2

Sa projection sur les attributs (Intitulé, Coef) donne :

π Intitule, Coef (Matiere)

Intitule Coef
Maths 4
anglais 2

Le modèle relationnel Algèbre relationnelle et SQL

Traduction de la projection en langage SQL :

Pour effectuer la projection π A1, ··· ,An(R), on évalue la requête :

SELECT A1, ..., An FROM R;

Exemple : Pour obtenir π Intitule, Semestre (Matiere), on évalue

la requête :

SELECT Intitule, Semestre FROM Matiere;

10
Le modèle relationnel Algèbre relationnelle et SQL
 II- Operateurs binaires :
1- Union :
On considère deux relations M1 et M2 de même schéma :
S= (Intitulé, Année, Semestre, Coef)

M1 M2
Intitule Annee Semestre Coef Intitule Annee Semestre Coef

Maths 2 1 4 Maths 2 2 4
Maths 2 2 4 programmation 1 2 3
anglais 1 2 2 Base de données 2 1 3

11
II- Operateurs binaires :
1- Union :
On considère deux relations M1 et M2 de même schéma :
S= (Intitulé, Année, Semestre, Coef)
M1 U M2
Intitule Annee Semestre Coef

Maths 2 1 4
Maths 2 2 4
anglais 1 2 2
programmation 1 2 3 On élimine les
Base de données 2 1 3 doubles
12
 2- Intersection

Exemple:
On considère deux relations M1 et M2 de même schéma :
S= (Intitulé, Année, Semestre, Coef)

M1 M2
Intitule Annee Semestre Coef Intitule Annee Semestre Coef
Maths 2 1 4 Maths 2 2 4
Maths 2 2 4 programmation 1 2 3
anglais 1 2 2 Base de données 2 1 3

13
2- Intersection

Exemple:
On considère deux relations M1 et M2 de même schéma :
S= (Intitulé, Année, Semestre, Coef)

M1 M2
U

Intitule Annee Semestre Coef

Maths 2 2 4

14
 3- Différence

Exemple:
On considère deux relations M1 et M2 de même schéma :
S= (Intitulé, Année, Semestre, Coef)

M1 M2
Intitule Annee Semestre Coef Intitule Annee Semestre Coef
Maths 2 2 4
Maths 2 1 4
programmation 1 2 3
Maths 2 2 4
Base de données 2 1 3
anglais 1 2 2

15
3- Différence

Exemple:
On considère deux relations M1 et M2 de même schéma :
S= (Intitulé, Année, Semestre, Coef)

M1 ▬ M2
Intitule Annee Semestre Coef

Maths 2 1 4
anglais 1 2 2

16
 4 –Instructions SQL :

Traduction des opérations binaire ensemblistes en langage SQL :

on utilise les mots-clés UNION, INTERSECT ou EXCEPT pour
réaliser les opérations d’union, d’intersection et de différence.

Exemple : Pour obtenir M1 U M2, on évalue la requête :

SELECT * FROM M1 UNION SELECT * FROM M2 ;

17
Application :
classe
Soit les deux tables suivantes, Traduire les id Filière Numéro Professeur
requêtes suivantes en opérations de 1 MPSI 1 Euclide
l’algèbre relationnelle puis en SQL.
2 MPSI 2 Turing

1- Obtenir la liste des filières proposées 3 PCSI 1 Horner

dans ce lycée. 4 PCSI 2 eleve Euler
2- Obtenir toutes les informations Nom Prénom Classe Note
concernant les classes de PCSI.
Meyer Romain 1 9,25
3- Obtenir les prénoms des élèves des
classes 1 et 3. Martin Paul 2 7,75
4- Obtenir les noms et les notes des élèves Robert Marie 4 12,0
ayant eu une note inférieure à 10. Michel Lucile 2 11,5
5- Obtenir les noms des élèves de la classe Bernard Sylvie 1 17,5
1 qui n’existe pas dans la classe 2 Martin Romain 3 14,0
Meyer Pierre 1 10,0
Dubois Camille 3 11,5

18
Solution :
1- Il suffit d’effectuer une projection sur l’attribut correspondant. Les doublons
sont fusionnés :
π Filiere (classe)
2- Il suffit de sélectionner les classes de PCSI :

σFiliere=PCSI (classe)
3- On sélectionne les élèves selon chacune des classes, puis on ne garde que les
prénoms :
π prenom (σclasse=1 (eleve) U σclasse=3 (eleve))

4- On peut commencer par ne garder que les noms et les notes, puis sélectionner
selon cette note :
σnote<10 (π nom,note (eleve))
5- On fait la différence entre les noms des élevés de la classe 1 moins les noms
des élevés de la classe 2 :
π nom (σclasse=1(eleve)) - π nom (σclasse=2(eleve))

19
Requêtes SQL :

1- SELECT DISTINCT filiere FROM classe

On note que SQL ne fusionne pas les doublons dans une table. On
peut forcer cette fusion à l’aide du mot-clé DISTINCT.

2- SELECT * FROM classe WHERE filiere='PCSI'

3- SELECT prenom FROM eleve WHERE classe=1 OR classe=3

4- SELECT nom, note FROM eleve WHERE note<10

5- SELECT nom FROM eleve WHERE classe=1 EXCEPT SELECT nom

FROM eleve WHERE classe=2

20
 III- Opérateurs n-aires :
1 – Produit cartésien :
S1 et S2 sont disjoints
R1 de schéma S1 = ( A1 , A2 . ... , An ) (sinon renommer
les attributs )
R2 de schéma S2 = ( B1 , B2 . ... , Bp )

Le produit cartésien de R1 et R2 noté R1 × R2 est de schéma

S = ( A1 , A2 . ... , An , B1 , B2 . ... , Bp)

( a1 , a2 . ... , an , b1 , b2 . ... , bp) appartient à R1 × R2 ssi ( a1 ,

a2 . ... , an) appartient à R1 et ( b1 , b2 . ... , bn) appartient à R2

21
Exemple :
Livre
Auteur
Titre genre Nom1 Nom2 Date-naissance
Germinal roman Zola Zola 1840
Les fleurs de mal poésie Baudelaire Baudelaire 1821
L’assommoir roman Zola

Livre × Auteur
Titre genre Nom1 Nom2 Date_naissance
Germinal roman Zola Zola 1840
Germinal roman Zola Baudelaire 1821
Les fleurs de mal Poésie Baudelaire Zola 1840
Les fleurs de mal Poésie Baudelaire Baudelaire 1821
L’assommoir roman Zola Zola 1840
L’assommoir roman Zola Baudelaire 1821

22
Produit cartésien en langage SQL :

Le produit cartésien R × S s’exprime très simplement en

incluant plusieurs relations dans la clause FROM de la commande
SELECT:

SELECT * FROM R , S ;

23
2- Jointure :
La jointure consiste donc à rapprocher les lignes de deux relations pour
lesquelles les valeurs d’un (ou plusieurs) attributs sont identiques.
Titre genre Nom_auteur
Nom Date-naissance
Germinal roman Zola
Zola 1840
Les fleurs de mal poésie Baudelaire Baudelaire 1821
L’assommoir roman Zola
Livre Auteur
Nom_auteur=Nom
Titre genre Nom_auteur Nom Date_naissance
Germinal roman Zola Zola 1840
Germinal roman Zola Baudelaire 1821
Les fleurs de mal Poésie Baudelaire Zola 1840
Les fleurs de mal Poésie Baudelaire Baudelaire 1821
L’assommoir roman Zola Zola 1840
L’assommoir roman Zola Baudelaire 1821

24
 π Titre, genre, Nom, Date_naissance(Livre Auteur)
Nom_auteur=Nom

Titre genre Nom Date_naissance

Germinal roman Zola 1840
Les fleurs de mal Poésie Baudelaire 1821
L’assommoir roman Zola 1840

En langage de l’algèbre relationnelle, la jointure des tables R1 et

R2 selon une certaine contrainte sur les lignes a sa notation propre
qui, selon les auteurs, peut s’écrire :
R1[contrainte]R2 ou R1 R2
[contrainte]

Peut être simplement définie comme équivalent à

σ[Contrainte] (R1× R2)

25
La jointure simple s'exprime en utilisant l'opérateur JOIN :

SELECT * FROM R1 JOIN R2 ON A = B

Exemple :

SELECT * FROM Livre JOIN Auteur ON Nom_Auteur = Nom

Remarque : Jointure sans opérateur JOIN

SELECT * FROM Livre,Auteur where Nom_Auteur = Nom

26
 Remarque :
Si deux tables possèdent le même nom d'attribut, on préfixe chaque attribut
par le nom de sa table d’origine pour lever l’ambiguïté.
Livre Auteur
Titre genre Nom Nom Date-naissance
Germinal roman Zola Zola 1840
Les fleurs de mal poésie Baudelaire Baudelaire 1821
L’assommoir roman Zola

SELECT [Link]
FROM Livre, Auteur
WHERE [Link] = [Link]

27
 III- Renommage :
Le renommage permet de résoudre des problèmes de compatibilité
entre noms d’attributs de 2 (ou plusieurs ) relations opérandes dans
une opération.

On définit l’opérateur de renommage d’un attribut A en B (d’une

Relation R) sous la forme: ρA→B ( R)

Exemple : ρ nom −auteur →nom1 ( auteur )

SQL : SELECT nom-auteur AS nom1 FROM auteur;

28
 Application : Soit une base de données d'un hôtel lit

contenant les tables suivantes : Chambre idlit chambre batlit

batiment 1 1 Rose
numero batiment fenetres
nom etoiles 2 1 Rose
1 Rose 2
Rose 3 3 2 Rose
2 Rose 1
Jasmin 2 4 3 Rose
3 Rose 1
Lys 3 5 1 Jasmin
nuits 1 Jasmin 1
Client Lit date 6 2 Jasmin
2 Jasmin 0
Lennon 1 15-08-2014 7 2 Jasmin
3 Jasmin 1
McCartney 8 18-08-2014 8 4 Jasmin
4 Jasmin 1
Starr 3 03-07-2014 9 1 Lys
1 Lys 3
Harrison 2 01-08-2014 10 2 Lys
2 Lys 2
Page 10 05-08-2014 11 3 Lys
3 Lys 2
Plant 1 13-08-2014
1- Identifier dans chaque table les attributs qui
Jones 11 05-08-2014
définissent sa clé primaire
Bonham 7 02-08-2014 2- Identifier les différentes clés étrangères
Townshend 1 08-08-2014
29
 batiment Chambre lit
nom etoiles numero batiment fenetres idlit chambre batlit
Rose 3 1 Rose 2 1 1 Rose
Jasmin 2 2 Rose 1 2 1 Rose
Lys 3 3 Rose 1 3 2 Rose

nuits 1 Jasmin 1 4 3 Rose

Client Lit date 2 Jasmin 0 5 1 Jasmin
Lennon 1 15-08-2014 3 Jasmin 1 6 2 Jasmin
McCartney 8 18-08-2014 4 Jasmin 1 7 2 Jasmin
Starr 3 03-07-2014 1 Lys 3 8 4 Jasmin
Harrison 2 01-08-2014 2 Lys 2 9 1 Lys
Page 10 05-08-2014 3 Lys 2 10 2 Lys
Plant 1 13-08-2014 11 3 Lys
Jones 11 05-08-2014 1- Obtenir le nom des clients ayant séjourné dans le bâtiment
Jasmin.
Bonham 7 02-08-2014 2- Obtenir le nom des clients ayant séjourné dans un bâtiment 3
étoiles.
Townshend 1 08-08-2014 3- Obtenir le nom des clients ayant séjourné dans une chambre ayant
au moins 2 fenêtres.
30
Solution :

1- L’information du bâtiment est accessible depuis la relation lit ; on effectue une

jointure entre les relations nuits et lit, puis on sélectionne le bâtiment voulu :
π Client (σ )
(nuits [lit = idlit] lit)
batlit=Jasmin

2- On doit d’effectuer une jointure supplémentaire avec la relation batiment. Dans la

mesure où les jointures sont associatives, on peut les effectuer dans n’importe quel
ordre :
π Client ( σetoiles=3(nuits [lit = idlit] lit [batlit = nom] batiment)
3- Pour cette requête, Il faudra effectuer une jointure sur la clé {numero, batiment}
de la table chambre à l’aide de deux conditions de recollement:

π Client ( σFenetres>=2(nuits [lit = idlit] lit [batlit = batiment, chambre = numero]

chambre)
31
Solution SQL:
1
SELECT client FROM nuits JOIN lit ON [Link]=[Link] WHERE [Link] = 'Jasmin';

ou encore :

SELECT client FROM nuits, lit WHERE Lit=dlit AND batlit = 'Jasmin';
2
SELECT client FROM nuits, lit, batiment
WHERE Lit=idlit AND batlit=nom AND
etoiles=3;
3
SELECT client FROM nuits, lit, chambre
WHERE lit=idlit AND batlit=batiment AND chambre= numero AND
fenetres>=2;

32
 IV- Sous requête :
Dans le langage SQL une sous-requête (aussi appelé “requête
imbriquée” ou “requête en cascade”) consiste à exécuter une
requête à l’intérieur d’une autre requête.

Quel sont les noms des clients qui ont passé une nuit dans le
batiment contenant le lit numéro 1 ?

SELECT Client FROM nuits,Lit WHERE lit=idlit AND batlit = (SELECT

batlit FROM lit WHERE idlit = 1)

33
 V- Fonctions d'agrégations :
Les fonctions d’agrégation permettent de répondre aux requêtes
du type :
 "Quel est la note max dans le premier contrôle"
 "Quel est la note moyen des étudiants"

L’agrégation va servir (par exemple) à regrouper les étudiants

d’une même classe (ce groupe de valeurs est appelé un agrégat)
et à effectuer une opération sur chacun des agrégats
Exemples d'opérations : min, max, somme, moyenne, comptage

34
Exemple : Soit la relation Etudiant de schéma S = (Nom, Filière, note) :

Nom Filière note

Yasser MPSI 12

Ilyass PCSI 15

Walid PCSI 14

Reda MPSI 17

Imane PCSI 11
Filiere ϒmoyenne(note)(Etudiant)

Filière note

MPSI 14.5

PCSI 13.33

35
EN SQL, les fonctions d’agrégation s’appliquent à une colonne,
en général de type numérique. Ces fonctions sont :
1. COUNT qui compte le nombre de valeurs non nulles.
2. MAX et MIN.
3. AVG qui calcule la moyenne des valeurs de la colonne.
4. SUM qui effectue le cumul (somme).

36
En SQL: A1,…, An ϒ f1(B1),…, fm(Bm) (R) sera traduit par :
SELECT A1 , A2 ... , An , f1 (B1), f2 (B2), ... , fp (Bp)
FROM R GROUP BY A1, A2 ,…, An ;

Exemples :
Filiere ϒmoyenne(note)(Etudiant)
SELECT Filiere, AVG(note) FROM Etudiant GROUP BY Filiere;

 Filiere ϒMax(note)(Etudiant)
SELECT Filiere, MAX(note) FROM Etudiant GROUP BY Filiere;

Pour réaliser l’opération d’agrégation (regroupement des lignes d’une table par
valeurs contenues dans une colonne) avec SQL, on utilise le mot clé GROUP BY
suivi du nom de la colonne sur laquelle s’effectue l’agrégat.

37
 Releve
Filière Numéro Élève Note
Application :
MPSI 1 Meyer 17,5
PCSI 2 Martin 7,75
MPSI 1 Bernard 9,25
PCSI 1 Robert 14,0
PCSI 2 Dubois 11,5
MPSI 1 Lemaire 7,25
PCSI 1 Albert 13,0
PCSI 1 Garcia 16,5
PCSI 2 Richard 12,5
MPSI 2 Petit 15,5
Soit la table Releve suivante .TraduirePCSI
les requêtes1suivantes en Simon
opérations de l’algèbre
10,5
relationnelle puis en SQL.
1- Calculer le nombre d’étudiant qui ont eu la moyenne
2- Calculer le nombre de filières de la table Releve.
3- Calculer la moyenne de chaque classe.
4- Calculer la moyenne de la MPSI 2.
5- Sélectionner les classes dont la moyenne est supérieure ou égale à douze.
38
Requete SQL :
1-
SELECT COUNT(*) FROM releve WHERE note >= 10;
2-
SELECT COUNT(Distinct Filiere) FROM Releve
3–
SELECT filiere, numero, avg(note)
FROM releve GROUP BY filiere, numero;
4–
SELECT filiere, numero, avg(note)
FROM releve WHERE numero = 2 AND Filiere=‘MPSI’

5-
SELECT filiere, numero FROM releve
GROUP BY filiere, numero HAVING avg(note) >= 12;

39
Calcul personnalisé:
On peut construire une nouvelle colonne dont le contenu est une
combinaison des données de la table.

Écrire la requête SQL qui Récupère les noms des étudiants et

leurs note sur 10.

SELECT eleve, CAST(note AS FLOAT)/2 as Note_10 FROM releve

La fonction CAST permet de convertir le résultat de l’expression

mathématique au type demandé

40
 Opérateur IN:
L’opérateur logique IN dans SQL s’utilise avec la commande
WHERE pour vérifier si une colonne est égale à une des valeurs
comprise dans set de valeurs déterminées

SELECT nom_colonne FROM nom_table WHERE nom_colonne

IN (valeur1, valeur2, valeur3, ...)

Exemple :

SELECT * FROM releve WHERE filiere IN (‘MPSI’,’PCSI’)

41
 Opérateur BETWEEN :
L’opérateur BETWEEN est utilisé dans une requête SQL pour
sélectionner un intervalle de données dans une requête utilisant
WHERE. L’intervalle peut être constitué de chaînes de caractères,
de nombres ou de dates

SELECT * FROM nom_table WHERE nom_colonne BETWEEN

'valeur1' AND 'valeur2'

Exemple :

SELECT * FROM releve WHERE note BETWEEN 10 AND 15

42
 Opérateur LIKE :
L’opérateur LIKE est utilisé dans la clause WHERE des requêtes SQL.
Ce mot-clé permet d’effectuer une recherche sur un modèle
particulier. Il est par exemple possible de rechercher les
enregistrements dont la valeur d’une colonne commence par telle
ou telle lettre. Les modèles de recherches sont multiples.
SELECT * FROM nom_table WHERE colonne LIKE modele
Exemple :
• LIKE '%a' : rechercher toutes les chaines de caractère qui se
termine par un «a»
• LIKE 'a%‘: rechercher toutes les lignes de « colonne » qui
commence par un «a».
• LIKE ‘%a%’: rechercher tous les enregistrements qui utilisent le
caractère «a».
43
 Opérateur LIKE :

• LIKE ‘a_c’ : Le caractère “_” peut être remplacé par n’importe

quel caractère, mais un seul caractère uniquement (alors que le
symbole pourcentage “%” peut être remplacé par un nombre
incalculable de caractères .

44
 Opérateur ORDER BY:
La commande ORDER BY permet de trier les lignes dans un
résultat d’une requête SQL. Il est possible de trier les données
sur une ou plusieurs colonnes, par ordre ascendant ou
descendant.

SELECT * FROM nom_table WHERE nom_colonne ORDER BY

Colonne

Exemple :
SELECT * FROM releve ORDER BY note
SELECT * FROM releve ORDER BY note DESC
SELECT * FROM releve ORDER BY note,eleve

45
 Opérateur LIMIT :
La clause LIMIT est à utiliser dans une requête SQL pour spécifier
le nombre maximum de résultats que l’ont souhaite obtenir.
Cette clause est souvent associé à un OFFSET, c’est-à-dire
effectuer un décalage sur le jeu de résultat
Exemple :

# récupérer les 10 premier tuples

SELECT * FROM releve LIMIT 10
# récupérer les résultats de 6 à 15
SELECT * FROM releve LIMIT 10 OFFSET 5
# On peut combiner ORDER BY et LIMIT
SELECT * FROM releve ORDER BY note LIMIT 10
46
 Opérateur IS:
Dans le langage SQL, l’opérateur IS permet de filtrer les résultats
qui contiennent la valeur NULL. Cet opérateur est indispensable
car la valeur NULL est une valeur inconnue et ne peut par
conséquent pas être filtrée par les opérateurs de comparaison

SELECT * FROM nom_table WHERE nom_colonne IS NULL (ou

NOT NULL)

Exemple :

SELECT * FROM releve WHERE note IS NOT NULL

47