Bases de données

(source Clément Ruef)

2 octobre 2022

Le but de ce chapitre est d’introduire cette structure fondamentale d’organisation qu’est la base de
données (BDD), et d’apprendre (un peu) à manipuler les bases de données en langage SQL (Structure
Query Language).

I Introduction
I.1 Intérêt des bases de données
Limites des structures de données "plates" :
Nous avons tous l’habitude de stocker des données simples dans, par exemple un fichier de type
excel. En Python on peut les stocker dans une liste (de listes ou de tuples).
Mais commençons par un exemple, pour montrer en quoi cela n’est pas toujours suffisant :
Considérons l’ensemble des classes de CPGE du lycée Berthollet. Chaque classe est identifiée par un
couple unique (filière,numéro). De plus, chaque classe admet une équipe professorale qui lui est propre.
Si l’on voulait stocker ces informations, on pourrait se dire qu’il suffirait d’un simple tableau, comme
par exemple le suivant, qui stocke dans une liste, l’ensemble des informations nécessaires à la définition
d’une classe, c’est-à-dire sa filière, son numéro et la liste des professeurs où l’on stocke des doublets
(nom,matière) :

lycee=[
("MPSI",1,[(’Mouton’,’Maths’),(’Desmarais,’Physique’),...]),
("MPSI",2,[(’Ruef’,’Maths’),(’Ory,’Physique’),...]),
("PCSI",1,[(’Duret’,’Maths’),(’Bourdaud,’Physique’),...]),
...
]

De la sorte, il est très simple de déterminer l’équipe professorale d’une classe donnée connaissant sa fi-
lière et son numéro (complexité linéaire dans le nombre de classes). En revanche, il devient plus difficile
de rassembler tous les professeurs d’une matière donnée, par exemple pour prévenir tous les profs de
maths de la tenue d’une réunion (complexité globalement quadratique).
Plus généralement, on peut se poser différents types de questions :
— Combien de classes y a-t-il au lycée ?
— Qui sont les profs de français ?
— Combien de profs interviennent sur plusieurs matières ?
— Quel est le prof qui a le plus de classes différentes ?
— ...

1
MP*, 2022/2023 Informatique commune Lycée Berthollet

On peut bien entendu écrire de petites fonctions Python pour répondre de ces questions, mais la struc-
ture même de la liste rend certaines recherches faciles et d’autres plus compliquées, et plus longues. Et
si cela reste rapide pour de petites listes, une complexité quadratique (ou plus) engendre un temps de
recherche très long pour un ensemble de données important.

Autres problèmes.

Ce n’est pas tout ! La structure de la liste précédente pose un certain nombre d’autres problèmes :
— Le format n’est pas standardisé (liste de tuples, de listes...), il n’y a pas de norme naturelle.
— Une partie de l’information est redondante. Cela prend de l’espace mémoire pour rien, et cela
n’est pas négligeable quand il s’agit d’une masse importante d’information.
— Non seulement la recherche d’information peut prendre du temps, mais il faut écrire une fonction
spécifique à chaque nouvelle recherche.
— Rien de permet de vérifier que les données sont cohérentes, (par exemple que les intitulés de
classes soient MPSI, PCSI ECE, ECS, HK, ... et non TOTO, TATA, TUTU ou encore que des
notes soient bien des nombres entre 0 et 20).
— Enfin, rien n’interdit d’entrer, suite à une erreur par exemple, deux lignes identiques, ou pire,
en contradiction. Dans notre exemple, on peut rentrer deux classes de MPSI 2 avec M. Mascaro
comme prof de maths dans la première, et M. Ruef dans la deuxième...

En bref, il faut trouver mieux.

I.2 Base de données, définition

Il nous faut donc trouver un système qui satisfasse les contraintes suivantes :
— Indépendance physique et logique des données (portabilité, possibilité d’enrichir les programmes
ou les données sans tout modifier, ...)
— Mécanisme d’interrogation complexe, pour un accès simple et rapide à l’information,
— Mécanisme de contrôle d’accès (pour des données potentiellement sensibles),
— Contrainte de la cohérence des données,
— Si nécessaire, vérification de contraintes sur les données,
— Contrôle de la (non-)redondance des données,
— Gestion de la cohérence multi-utilisateurs (accès en concurrence).

Ce qui nous amène à la définition (un peu vague) suivante :

Bases de données
Une base de données (BDD) est :
— une collection d’informations ou de données qui existent sur une longue période de temps
et qui décrivent les activités d’une ou plusieurs organisations ;
— un ensemble de données modélisant les objets d’une partie du monde réel et servant de
support à une application informatique.

Une BDD ne serait rien sans un système de gestion de base données :

Système de gestion de base de données, ou SGBD

Un système de gestion de bases de données, ou SGBD (DataBase Management Systems - DBMS) :
Ensemble de logiciels systèmes permettant aux utilisateurs d’insérer, de modifier, et de rechercher

2
MP*, 2022/2023 Informatique commune Lycée Berthollet

efficacement des données spécifiques dans une grande masse d’informations (pouvant atteindre
plusieurs milliards d’octets) partagée par de multiples utilisateurs.

Pour un SGBD efficace, il est alors nécessaire d’avoir un modèle de données.

I.3 Le modèle relationnel

Toute structure de BDD doit nécessairement être basé sur un modèle théorique, représentation
abstraite plus ou moins fidèle de la réalité. Un modèle doit être capable de décrire les occurences des
données, mais aussi leur structure même, c’est-à-dire quel type d’objets, quelles relations...
Il existe différents type de modèles. Nous nous intéresserons ici au modèle relationnel, prépondérant et
sur lequel est basé le langage SQL.
Ce modèle a été développé par Ted Codd en 1970. Il est très simple, car n’utilise que des tableaux à 2
entrées (2 dimensions), mais puissant grâce à l’algèbre relationnelle, que nous verrons dans la suite du
chapitre.

I.4 En pratique : client-serveur et architecture trois-tiers

Concrètement, on rencontre des BDD partout. Enfin, sur internet surtout :
— Bases de données artistiques (musique, cinéma,...) répertoriant les artistes, leur œuvre, l’année...
— Les BDD concernant les individus et leurs caractéristiques : Facebook en est le principal exemple,
mais aussi les données des clients d’un site internet...
— Les BDD répertoriant les sites internet, nécessaires à un moteur de recherche,
— Les BDD de stockage de données de recherches volumineuses, et à nombreuses entrées (archéo-
logie, géographie...),
— ...

Concrètement, on peut interroger un SGBD via un logiciel dédié, en langage SQL, et on récupère le
résultat qu’on traite alors. Cette structure est appelée architecture client-serveur. Plusieurs utilisateurs
peuvent se connecter en même temps, et de manière indépendante.
Les SGBD existantes sont nombreuses : MySQL, PostgreSQL, Oracle, SQLite Manager, ... Toutes
fonctionnent sur un même système client-serveur, sauf SQLite, plus facile d’utilisation, mais plus limité.
Dans le cadre du cours et des TPs associés, nous nous limiterons à SQLite Manager.

3
MP*, 2022/2023 Informatique commune Lycée Berthollet

Dans la pratique, on a bien plus généralement une structure appelée architecture trois tiers :
— le tiers utilisateur,
— le tiers applicatif,
— le tiers BDD.
Le tiers utilisateur (nous, et notre ordinateur) ne communique qu’avec le serveur intermédiaire (en
effectuant une recherche Google par exemple), et c’est alors ce serveur qui va, via un programme, com-
muniquer avec la BDD.
En bref, en tant qu’utilisateur, il est rare de devoir écrire une requête en SQL pour interroger directe-
ment une BDD.

II Le modèle relationnel
II.1 Bases et vocabulaire
Pour illustrer toutes les définitions qui vont suivre, nous nous servirons de la base de données Musique
ci-dessous :

Base de données Musique

Albums Artiste Titre Année Langue

Radiohead OK Computer 1997 Anglais

Georges Brassens Fernande 1972 Français
MGMT Oracular Spectacular 2007 Anglais
Radiohead In Rainbows 2007 Anglais
Les Taupins Vive la prépa ! 2019 Français
Mozart Requiem 1791 Latin

Chansons Titre Album Artiste

Kids Oracular Spectacular MGMT

La princesse et le croque-notes Fernande Georges Brassens
Le roi Fernande Georges Brassens
Les maths c’est trop d’la balle Vive la prépa ! Les Taupins
La physique c’est fantastique Vive la prépa ! Les Taupins
Karma Police OK Computer Radiohead
Paranoid Android OK Computer Radiohead
Exit Music (For a Film) OK Computer Radiohead

4
MP*, 2022/2023 Informatique commune Lycée Berthollet

Introduisons plus complètement nos premières définitions, en commençant par la notion centrale de
relation :

Une relation.
R a1 : D 1 ... aj : Dj ... an : Dn

t 1,1 ... t 1, j ... t 1,n

... ... ... ... ...
t i,1 ... t i, j ... t i,n
... ... ... ... ...
t m,1 ... t m, j ... t m,n

Définition : attributs
Dans la relation, ou table définie par le tableau ci-dessus :
— R est le nom de la relation.
— a 1 ,. . . , a n sont appelés les attributs de la relation.
— Chaque attribut est d’un certain type (entier, flottant, chaine de caractère, ...). D j est alors
appelé le domaine de l’attribut, aussi noté dom(a j )). Autrement dit, le domaine D j est
l’ensemble des valeurs que peut prendre l’attribut a j .
— Le nombre total n d’attribut de la relation est appelé arité de la relation.

Remarque : l’ordre des attributs (des colonnes) n’a pas d’importance.

Exemple : La base de données Musique est constituée de deux tables.

Dans la table Albums, on trouve 4 attributs : Artiste : str, Titre : str, Année : int et Langue :
str.
Dans la table Chansons, on trouve 3 attributs : Titre : str, Album : str et Artiste : str.

Définition : schéma relationnel

Le schéma de relation est la donnée du n-uplet (a j : D j )1≤ j≤n de la relation.
Il est donc donné en première ligne du tableau.
Le schéma de la relation définit l’intension de la relation. Autrement dit, il définit abstraitement
la relation.

Remarque : Pour simplifier, on omet souvent d’écrire le domaine des attributs.

Définition : extension de la relation

— Une ligne du tableau est un tuple, c’est-à-dire une valeur prise par l’ensemble des attributs.
— L’ensemble des tuples est appelée l’extension de la relation, soit l’ensemble des données
concrètes.
— Le nombre m de tuples (de lignes) est appelé cardinal de la relation R.

5
MP*, 2022/2023 Informatique commune Lycée Berthollet

Remarque : L’extension de relation est un ensemble, l’ordre des lignes est donc lui aussi sans importance.
Toujours en tant qu’ensemble, il est par contre interdit d’avoir deux fois exactement la même ligne.

Exemple : Considérons la première table de la BDD Musique.

Son schéma relationnel est :
Albums : (Artiste : str, Titre : str, Année : int, Langue : str)
Son arité est de 4, et son cardinal 6.
L’un des tuples de ces données est (Mozart, Requiem, 1791, Latin).

Exercice 1. : Décrire la deuxième table de le BDD Musique à l’aide du vocabulaire introduit.

II.2 Contrainte d’intégrité, clef primaire

À la création d’une table, on peut/doit définir certaines contraintes que doivent vérifier les données.
Celles-ci peuvent être de trois types :
— Contrainte de clef primaire,
— Contrainte de référence (clef étrangère),
— Contraintes de valeurs ou de domaine.

Clef primaire

Définition
Pour R une relation, une clef primaire est un sous-ensemble K d’attributs vérifiant :

∀ u 1 , u 2 ∈ R, u 1 (K) = u 2 (K) ⇒ u 1 = u 2 .

Autrement dit, si deux tuples coïncident sur K, ils doivent être égaux. la projection de R sur K
est injective.

En pratique, la clef primaire est un identifiant, le plus simple possible, pour chaque tuple.
Une bonne clef primaire sera une clef qui possède le moins possible d’attributs. Souvent, les BDD af-
fectent à chaque tuple un numéro, appelé identifiant, qui sert de clef primaire. Dans certains ouvrages,
une clef est qualifiée de primaire si elle possède un seul attribut !

Exemples :
— Dans la table Albums, la clef primaire la plus judicieuse est (Artiste,Titre).
L’attribut Album n’est pas une clef primaire pour la table Chansons.
— Un numéro de sécurité sociale est une bonne clef primaire, pour une table référençant la popu-
lation française.
— Sur internet, l’adresse e-mail est souvent utilisée. Il est en général impossible de définir deux
comptes sur un même site en utilisant la même adresse e-mail.

Remarque : On définit pour chaque table en général une clef primaire, mais on peut aussi définir d’autres
clefs.

Notation : On identifie la clef primaire en soulignant les attributs associés :

Albums : (Artiste, Titre, Année, Langue)

Contrainte de référence, clef étrangère.

Exemple : Dans la BDD de notre exemple, il y a bien entendu un lien entre les deux tables.
La deuxième table Chansons nous dit sur quels albums se trouvent les chansons référencées. Il est

6
MP*, 2022/2023 Informatique commune Lycée Berthollet

donc naturel de demander à ce que tous les albums apparaissant dans cette table soient dans la table
Albums.
C’est la contrainte de référence, ou de clef étrangère.

Définition
Une contrainte de référence est une correspondance entre des attributs {a 1 , . . . , a k } d’un schéma R
et des attributs {a01 , . . . , a0k } d’un schéma R’, telle que :
1. Pour tout tuple t de R, il existe un tuple t0 de R’ tel que t[a 1 , . . . , a k ] = t0 [a01 , . . . , a0k ],
2. {a01 , . . . , a0k } est une clef de R’.

Exemple : (Album, Artiste) est une clef étrangère pour la table Chansons.

Contrainte de valeur ou de domaine.

Enfin, on peut préciser les domaines des attributs par des contraintes booléennes. Cela se fait à la
création de la table.
Exemples :
— Pour la liste des classes de prépa du lycée, les filières (de première année) seront des chaînes de
caractères uniquement à valeurs dans {’MPSI’,’PCSI’,...},
— On va exiger d’une note qu’elle soit comprise entre 0 et 20.

II.3 Base de données

On peut enfin définit complètement ce qu’est une BDD :

Définition : schéma de base de données

Un schéma de base de données (relationnelle) est la donnée :
— d’un ensemble de schémas de relations (de tables),
— d’une contrainte de clef primaire par schéma de relation,
— d’un ensemble de contraintes de clefs étrangères,
— d’un ensemble de contraintes de valeurs.

Définition : base de données

Une base de données (relationnelle) est la donnée :
— d’un schéma de BDD
— de tuples pour peupler ce schéma, vérifiant toutes les contraintes d’intégrité.

Nous avons fini de décrire la structure de notre BDD. Il reste à apprendre comment s’en servir.

II.4 Création de table

La création d’une BDD peut se faire directement en langage SQL, ou à l’aide d’une interface gra-
phique.
C’est à la création de la BDD, et des tables qui y sont, que l’on définit les schémas de relations, et c’est
donc à ce moment-là qu’il faut tout spécifier.

Résumons donc les données nécessaires à la création de la BDD :

7
MP*, 2022/2023 Informatique commune Lycée Berthollet

— Création de toutes les tables (ou relations) de la BDD, c’est-à-dire pour chacune des tables, spé-
cification de tous les attributs et de leur domaine.
— Précision de la clef primaire pour chacune des tables.
— Précision, s’il y a lieu des contraintes des clefs étrangères.
— Précision, s’il y a lieu, des contraintes de domaines.
Le schéma de BDD étant créé, on peut alors remplir la BDD avec des tuples.

III Algèbre relationnelle, requêtes SQL

On se place désormais dans la situation où l’on dispose d’une BDD, déjà créée, et on souhaite l’inter-
roger, à l’aide de requêtes, pour récupérer des informations (sous forme de sous-table de la BDD).

Voici quelques exemples de requêtes en langage courant que nous pouvons poser sur notre base :
— Qui a écrit la chanson "Le roi" ?
— Donner tous les chansons référencées de Radiohead.
— Quelles sont les chansons sortis avant 1980 ?
Ces requêtes mettent en jeu une ou plusieurs relations. Pour combiner les différentes relations et pour
les manipuler, on utilise des opérateurs ensemblistes ou relationnels.

Pour toutes les opérations qui suivent, nous présenterons successivement les notions abstraites, puis
leur traduction en langage de l’algèbre relationnelle et enfin leur traduction en langage SQL.

Précisons tout de suite que les requêtes SQL, auront toujours la même base concernant une syntaxe :

Syntaxe en langage SQL

— Toutes les requêtes SQL commencent par le mot-clef SELECT
— La relation (table) sur laquelle on opère est précisée par FROM
— Les requêtes se terminent par un point-virgule.
— Les valeurs des attributs non numériques sont écrites entre guillemets simples ou doubles.

Remarque
Le langage SQL, contrairement à Python, n’est pas sensible à la casse (c’est-à-dire aux majuscules
et aux minuscules), mais par convention, on choisit de donner les instructions du langage SQL
en majuscules (comme SELECT, FROM, WHERE, etc.) alors que ce qui est du ressort des tables
(autant les attributs que les noms des tables) en minuscules.

Notre toute première requête SQL sera l’affichage d’une table :

SELECT * FROM table ;

Notons enfin que SQL est un langage déclaratif, c’est-à-dire que l’on dit au SGBD ce que l’on veut
faire, mais on ne lui dit pas (vraiment) comment il doit le faire. En interne, l’interpréteur va s’arranger
pour optimiser la requête pour obtenir le résultat en un minimum de temps. Les notions de complexité
abordées dans les précédents chapitre d’algorithmique n’ont donc pas vraiment de sens puisque même
une requête "mal formulée" peut s’exécuter très rapidement grâce à une reformulation interne.

III.1 Opérateurs simples de l’algèbre relationnelle

Commençons par considérer les opérateurs n’agissant que sur une table.

8
MP*, 2022/2023 Informatique commune Lycée Berthollet

III.1.a Projection

La projection, permet d’éliminer des attributs, pour ne garder que ceux qui nous intéressent. Elle
est notée πattributs (Relation).
Elle renvoie donc une nouvelle table, obtenue en enlevant certaines colonnes.

En langage SQL, une projection s’écrira :

"SELECT att1, att2,... FROM Relation ;".
Pour afficher tous les attributs, on utilise le symbole "*" :
"SELECT * FROM Relation ;"

Exemple : Considérons la relation Albums associée au schéma (Artiste,Titre,Année,Langue).

Alors
π(Titre,Année) (Albums) se notera en SQL : "SELECT Titre, Année FROM Albums ;", et donnera :

SELECT Titre, Année FROM Albums ;

π(Titre,Année) (Albums) Titre Année

OK Computer 1997
Fernande 1972
Oracular Spectacular 2007
In Rainbows 2007
Vive la prépa ! 2019
Requiem 1791

Si après la projection on retrouvait deux tuples (réduits) identiques, on n’écrirait qu’une seule fois le
tuple. L’application π n’est pas injective !
Toutefois, ce n’est pas ce que rend la requête SQL, qui garde le même nombre de tuples, et qui ne
fusionne pas les tuples réduits identiques.
Il est possible de forcer cette fusion, et donc de n’avoir que des tuples différents, à l’aide du mot-clef
"DISTINCT" :
Exemple :

SELECT DISTINCT Album FROM Chansons ;

πAlbum (Chansons) Album

Oracular Spectacular
Fernande
Vive la prépa !
OK Computer

III.1.b Sélection (ou restriction)

Très souvent, seule une partie de l’ensemble des lignes d’une table nous intéresse, on va donc sélec-
tionner les lignes intéressantes d’après une certaine contrainte. En langage de l’algèbre relationnelle,
une telle sélection se note σcontrainte (Table).
La contrainte peut être de toute sorte, comme une vérification d’égalité (Titre="Fernande") ou d’égalité
approchée (Titre LIKE "Les maths%"), une inégalité (Année>2000), ...

9
MP*, 2022/2023 Informatique commune Lycée Berthollet

Remarque
— Si plusieurs attributs sont chiffrés, on peut effectuer des opération élémentaires dans les
contraintes. Par exemple, dans une BDD géographique ou population et surface sont
des attributs, on peut écrire "population/surface>200".
— On peut ordonner la table selon un attribut (chaîne de caractère ou nombre) à l’aide de la
commande ORDER BY att (ordre croissant) ou ORDER BY att DESC (ordre décroissant).

Et en langage SQL, il suffit d’utiliser le mot-clef "WHERE" :

Exemple :

SELECT * FROM Albums WHERE Langue="Français" ;

σLangue=Français (Albums) Artiste Titre Année Langue

Georges Brassens Fernande 1972 Français

Les Taupins Vive la prépa ! 2019 Français

On peut bien entendu faire à la fois une projection et une sélection.

Exemple :

SELECT Titre FROM Chansons WHERE Artiste="Radiohead" ORDER BY Titre ;

πTitre (σArtiste=Radiohead (Chansons)) Titre

Exit Music (For a Film)

Karma Police
Paranoid Android

Remarque : D’autres opérations sont encore possibles : valeur manquante (mot-clef "IS NULL" ou "IS
NOT NULL"), vérification d’appartenance à un intervalle, à un autre ensemble (mot-clef : "IN"), vérifi-
cation de certaines propriétés (mot-clef : "EXISTS"), vérification de comment des données se comparent
à toutes celles d’une autre table (mot-clef : "ALL") ou seulement si la comparaison tient pour l’une ou
l’autre des données d’une autre table (mot-clef : "ANY""ou "SOME").
Pour plus de détails sur ces possibilités, on pourra par exemple consulter le site http://sql.sh

III.1.c Renommage

Il peut être utile de renommer un ou plusieurs attributs, en particulier lors d’un travail avec plu-
sieurs tables.
Par exemple, les deux tables de Musique ont un attribut Titre, qui correspond dans la première rela-
tion au titre de l’album, dans la seconde au titre de la chanson.
En algèbre relationnelle, un renommage se fera avec les notations : ρ A →B (R), ou pour plusieurs attri-
buts : ρ A 1 ,...,A n →B1 ,...,B n (R).
En language SQL, il suffit d’utiliser le mot-clef "AS".

Exemple :

10
MP*, 2022/2023 Informatique commune Lycée Berthollet

SELECT Titre AS Album, Artiste FROM Albums ;

πAlbum,Artiste (ρ Titre→Album (Albums)) Album Artiste

OK Computer Radiohead
Fernande Georges Brassens
Oracular Spectacular MGMT
In Rainbows Radiohead
Vive la prépa ! Les Taupins
Requiem Mozart

III.1.d Opérations ensemblistes usuelles

On peut effectuer les opérations ensemblistes habituels : l’union, l’intersection et la différence d’en-
sembles, si la structure le permet, c’est-à-dire si l’on peut rendre le résultat dans une même table. Il
faut donc le même schéma relationnel (dans le même ordre).

Union A ∪ B
Deux variantes sont possibles :
— soit deux requêtes distinctes, avec le mot-clef "UNION", entre les deux,
— soit une sélection de la forme "WHERE ... OR ...".
Exemple :

SELECT * FROM Albums WHERE Année > 2000 OR Artiste=’Mozart’ ;

s’écrit aussi
SELECT * FROM Albums WHERE Année > 2000
UNION
SELECT * FROM Albums WHERE Artiste=’Mozart’ ;
R Artiste Titre Année Langue

MGMT Oracular Spectacular 2007 Anglais

Radiohead In Rainbows 2007 Anglais
Les Taupins Vive la prépa ! 2019 Français
Mozart Requiem 1791 Latin
La table R est donnée par σAnnée>2000 (Albums)∪σArtiste=Mozart (Albums)

Intersection A ∩ B
De même que pour l’union, on peut utiliser les mots-clefs "INTERSECT" ou "AND" :

SELECT * FROM Chansons WHERE Album=’OK Computer’ AND Titre LIKE ’%ar%’ ;
s’écrit aussi
SELECT * FROM Chansons WHERE Album=’OK Computer’
INTERSECT
SELECT * FROM Chansons WHERE Titre LIKE ’%ar%’ ;
R Titre Album Artiste

Karma Police OK Computer Radiohead

Paranoid Android OK Computer Radiohead
La table R est donnée par :

11
MP*, 2022/2023 Informatique commune Lycée Berthollet

σAlbum=OK Computer (Chansons)∩σTitre LIKE \%ar\% (Chansons)

Différence A \ B

Pour la différence, la commande est "EXCEPT" 1 . Mais on peut souvent l’écrire plus simplement avec
un "AND" en notant que A \ B = A ∩ B.
Exemple :

SELECT * FROM Albums WHERE Année<2000 AND NOT Langue=’Latin’ ;

s’écrit aussi
SELECT * FROM Albums WHERE Année<2000
EXCEPT
SELECT * FROM Albums WHERE Langue=’Latin’ ;
R Artiste Titre Année Langue

Radiohead OK Computer 1997 Anglais

Georges Brassens Fernande 1972 Français
La table R est donnée par σAnnée=2000 (Albums)\σLangue=Latin (Albums)

III.2 Opérateurs complexes de l’algèbre relationnelle

Nous n’avons pour l’instant travailler que sur une table. Or, comme dans notre exemple, une BDD
en contient souvent plusieurs. Et on parvient vite à des requêtes où il est nécessaire de lire les deux
tables.
Par exemple : "Quelles sont les chansons sorties au 20e siècle ?" nécessite de lire les deux tables.

III.2.a Jointure

Le premier outils complexe à notre disposition est la jointure (symétrique), noté en algèbre relation-
nelle R ./ A = A 0 R 0 = R[A = A 0 ]R 0 .

La jointure revient à associer les lignes d’une table aux lignes d’une autre table en se basant sur la
correspondance de valeurs dans une colonne de chaque table.
Les mots-clefs SQL sont "JOIN" et "ON" :

"SELECT ... FROM R JOIN R’ ON att1=att2 ;"

Exemple : Dans notre exemple, la jointure entre nos deux tables se fera sur Titre de la table Albums
et Album de la table.

Remarque : Si l’on ne fait pas de projection, les deux colonnes de la jointure apparaissent, mais sont
identiques, au titre près. Avec une projection supplémentaire judicieuse, une seule des deux colonnes
apparaît.

Exemple :

1. D’autres logiciels utilisent le mot-clef "MINUS" plutôt que "EXCEPT".

12
MP*, 2022/2023 Informatique commune Lycée Berthollet

SELECT Chansons.Titre AS Chanson, Artiste, Année FROM Albums JOIN Chansons ON

Albums.Titre=Album WHERE Année<2000 ;
R Chanson Artiste Année

La princesse et le croque-notes Georges Brassens 1972

Le roi Georges Brassens 1972
Karma Police Radiohead 1997
Paranoid Android Radiohead 1997
Exit Music (For a Film) Radiohead 1997
La table R est donnée par Chansons σAnnee<2000 (./Albums.Titre=Album Albums)

Remarque
Si on ne souhaite pas renommer les attributs, mais que certains noms apparaissent dans chacune
des tables de la jointure, on peut/doit préfixer l’attribut par le nom de la table (c’est-à-dire écrire
"table.attribut" au lieu de "attribut") pour lever tout ambiguïté.

III.2.b Produit cartésien

Le produit cartésien de tables est celui défini en mathématiques : si on a deux tables R1 et R2 , le

produit cartésien, noté R1 ×R2 des deux tables est l’ensemble des couples (t 1 , t 2 ), où t 1 et un tuple de R1
et t 2 un tuple de R2 . Autrement dit, (t 1 , t 2 ) est un n-uplet de taille la somme des arités R1 et R2 .
En pratique, cela revient donc à la table où l’on met, sans restriction, tous les attributs de la première
et de la deuxième table.
En SQL, il suffit de demander tous les éléments des deux tables :
"SELECT * FROM R1,R2".
Exemple : Changeons un peu de notre exemple habituel, et considérons la BDD Examen constituée des
deux relations simples "Etudiants" et "Notes" :

Base de données Examen

Notes Matière Nom Notes
Etudiants Numéro Nom
Info J.Bond 4
007 J.Bond
Maths L.Luke 16
120 L.Luke
Info L.Luke 14

On aura :

SELECT * FROM Etudiants,Notes ;

Examen Numéro Etud.Nom Matière Notes.Nom Notes

007 J.Bond Info J.Bond 4

007 J.Bond Maths L.Luke 16
007 J.Bond Info L.Luke 14
120 L.Luke Info J.Bond 4
120 L.Luke Maths L.Luke 16
120 L.Luke Info L.Luke 14

13
MP*, 2022/2023 Informatique commune Lycée Berthollet

où Examen = Etudiants × Notes

On a bien réalisé le produit cartésien, qui peut être pertinent dans certains cas. Mais on voit ici que
la table obtenue n’est pas bien sensée. On peut faire une sélection, pour retrouver, avec une syntaxe
différente, ce que l’on a obtenu précédemment avec une jointure :

SELECT Numéro, Etudiants.Nom AS Nom,Matière,Notes FROM Etudiants,Notes

WHERE Etudiants.Nom=Notes.Nom ;
s’écrit aussi :
SELECT Numéro, Etudiants.Nom AS Nom,Matière,Notes
FROM Etudiants JOIN Notes ON Etudiants.Nom=Notes.Nom ;
Examen Numéro Nom Matière Notes

007 J.Bond Info 4

120 L.Luke Maths 16
120 L.Luke Info 14

Remarque
Il est, comme en maths, tout à fait possible de faire un auto-produit cartésien, c’est-à-dire de
considérer la table R × R. Le seul problème qui se présente alors est que les noms des attributs
sont dupliqués, donc ambigus.
Pour y remédier, le plus simple est de renommer les tables elles-mêmes, à l’aide de la commande
AS, et donc écrire une requête de type :
SELECT table1.att1,table2.att5 FROM R AS table1,R AS table2 WHERE ... ;

Remarque : En algèbre relationnelle, il existe une notion de division cartésienne, peu utile et n’ayant
pas de syntaxe simple en langage SQL.

III.3 Fonctions d’agrégation

Considérons la table de notes précédentes, complétée :

Table Notes
Notes Matière Nom Notes

Info J.Bond 4
Info Astérix 3
Info L.Luke 14
Maths L.Luke 16
Maths J.Bond 12
Maths Astérix 14
Sport L.Luke 9
Sport Astérix 18
Physique J.Bond 7
Physique Astérix 13

14
MP*, 2022/2023 Informatique commune Lycée Berthollet

III.3.a Fonctions d’agrégation

Nous avons vu sur les précédents exemples qu’il était assez facile de calculer des quantités "sur
une ligne" en combinant les informations que l’on peut y trouver. Néanmoins, il peut être intéressant
de calculer des quantités "sur une colonne" (dont le domaine est un ensemble de nombres), comme par
exemple déterminer la valeur moyenne, le maximum, ... des éléments d’une colonne.
On utilise pour cela les fonctions d’agrégation, notée γ en algèbre relationnelle.
Il en existe cinq différentes :
— comptage : compte le nombre de lignes (de n-uplets) d’un groupe. "COUNT" en SQL,
— max (resp. min) : détermine le plus grand (resp. le plus petit) élément d’un groupe . "MAX" et
"MIN" en SQL,
— moyenne : calcule la moyenne ! "AVG" en SQL,
— Enfin somme : calcule la somme, "SUM" en SQL.

La syntaxe en algèbre relationnelle est γ f ( A ) (R), où f est la fonction utilisée, A l’attribut concerné.

Exemple :

SELECT NOM, MAX(Notes) FROM Notes ;

γmax(Notes) (Notes) Nom Max(Notes)

Astérix 18

Plus intéressant, on peut calculer des moyennes, sommes... non pas sur l’ensemble des valeurs de notre
attribut, mais sur des sous-groupes, des agrégats, en utilisant la commande "GROUP BY", qui regroupe
suivant les valeurs d’un autre attribut.
En algèbre relationnelle, la syntaxe sera B γ f ( A ) (R), où f est la fonction utilisée, A l’attribut concerné
regroupé selon les valeurs de B.
Un exemple valant mieux qu’un long discours :

SELECT NOM, AVG(Notes) FROM Notes GROUP BY Nom ORDER BY Nom ;

πNom γavg(Notes) (Notes) Nom AVG(Notes)

Astérix 12
J.Bond 7.67
L.Luke 13

III.3.b Requêtes agrégatives

On peut souhaiter encore effectuer une sélection en aval de la fonction agrégative. L’instruction
"WHERE" sera alors remplacée par "HAVING", qui s’utilise exactement de la même manière.

Exemple :

15
MP*, 2022/2023 Informatique commune Lycée Berthollet

SELECT NOM, AVG(Notes) AS Moyenne FROM Notes GROUP BY Nom HAVING

Moyenne >=10 ;
πNom γavg(Notes) (Notes) Nom AVG(Notes)

Astérix 12
L.Luke 13

IV Pour résumer
Pour finir, un résumé des requêtes SQL que nous avons vues :

Quoi Comment
Projection SELECT A1,A2,... FROM R

Sélection SELECT A1,A2,... FROM R WHERE condition

Renommage SELECT A1 AS ..., A2 AS ..., FROM R

Ordonner par ordre croissant SELECT A1,A2,... FROM R ORDER BY A1

Ordonner par ordre décroissant SELECT A1,A2,... FROM R ORDER BY A1 DESC

Éléments distincts SELECT DISTINCT A1, ... FROM R

Union (si comptatible) (SELECT A1 FROM R) UNION (SELECT A2 FROM R’)

Intersection (si comptatible) (SELECT A1 FROM R) INTERSECT (SELECT A2 FROM R’)

Différence (si comptatible) (SELECT A1 FROM R) EXCEPT (SELECT A2 FROM R’)

Agrégations SELECT f(A1),A2,... FROM R GROUP BY A2

Fonctions d’agrégation disponibles COUNT, SUM, MAX, MIN, AVG

Sélection après agrégation SELECT f(A1),A2,... FROM R GROUP BY A2 HAVING ...

Produit cartésien de tables SELECT R1.A1,R1.A2,R2.A1,... FROM R1,R2

Jointure SELECT * FROM R JOIN R’ ON A1=A2

Produit cartésien SELECT * FROM R,R’

Rappelons tout de même que cette liste est loin d’être exhaustive.

16