INFO 10 Bases de données relationnelles

INFO 10
Bases de données relationnelles
Introduction au modèle relationnel et requêtes élémentaires en SQL.
10
Bases de données relationnelles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1
Les systèmes de gestion de bases de données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.1
Architecture client-serveur . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.2
Architecture trois-tiers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.3
Gestion et interrogation d’une base de données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
2
Le modèle relationnel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
2.1
Schéma relationnel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
2.2
Trouver « le bon » modèle relationnel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3
Création et alimentation d’une base de données relationnelle . . . . . . . . . . . . . . . . . . . . . . . . .
3.1
Création par import de données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.2
Création ex-nihilo . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
4
L’algèbre relationnelle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
4.1
Opérations sur les relations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
4.2
Groupes et fonctions d’agrégation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
4.3
Exemples de requêtes sur la base de données prenoms . . . . . . . . . . . . . . . . . . . . . . . . .
4.4
Exemples de requêtes sur la base de données Biblio . . . . . . . . . . . . . . . . . . . . . . . . . . .
5
Le langage SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
5.1
Traduction en SQL des opérations de l’algèbre relationnelle . . . . . . . . . . . . . . . . . . . .
5.2
Création et alimentation d’une base de données en SQL . . . . . . . . . . . . . . . . . . . . . . .
5.3
Requêtes depuis une application de gestion de SGBD . . . . . . . . . . . . . . . . . . . . . . . . . .
5.4
Requêtes depuis un interpréteur Python . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1
3
3
4
4
4
5
5
7
8
10
11
11
14
14
15
17
17
20
21
21
PCSI2 \ 2014-2015
Laurent Kaczmarek
ES ordinateurs ont été initialement créés pour calculer. Mais, depuis l’invention des disques durs
en 1956, les ordinateurs permettent aussi de collecter, classer et stocker de grandes quantités
d’informations. Le terme database (base de données) est apparu en 1964 pour désigner une collection d’informations partagées par différents utilisateurs d’un même réseau. Les premières utilisations
des bases de données furent militaires. Elles sont de nos jours très largement utilisées, de l’échelle la
plus petite (accès local, par les employés d’une entreprise par exemple) à la plus grande (accès global,
par n’importe quel internaute par exemple).
L
Deux points fondamentaux découlent des objectifs pratiques des bases de données :
. Le partage à grande échelle de données implique certaines précautions (pour garantir la pérennité
des données par exemple).
. Pour traiter un grand nombre de données, il faut également réfléchir à la meilleure manière de les
structurer afin de faciliter les algorithmes de recherche.
Partager des données
La Bibliothèque Nationale de France posséde un catalogue impressionnant d’ouvrages enregistrés dans
une base de données. Elle peut être consultée par les utilisateurs qui peuvent par exemple effectuer des
recherches thématiques : quelles sont les ouvrages disponibles écrits par tel écrivain ? quelles sont les
éditions disponibles ? quelle est la date d’acquisition de telle édition ? etc. Les bibliothécaires ont un
droit supplémentaire : modifier les données (ajouter des données, en supprimer, les altérer).
En achetant sur Amazon, un client consulte une base de donées constituée des descriptions des biens
vendus, des prix, des vendeurs, des stocks et des commentaires des acheteurs. Un achat modifie automatiquement ces données (l’état du stock par exemple). Les acheteurs ont également accès aux données
et peuvent modifier certaines données (prix, stocks, etc).
Les données des stations méteorologiques sont enregistrées dans des bases de données puis traitées par
les ingénieurs de Méteo-France.
Partager des données implique des règles de précautions :
. Les données doivent être facilement accessibles par un grand nombre de clients et il faut garantir une
certaine pérennité des données.
. Afin de protéger les données, il convient de respecter les points suivants :
† Les utilisateurs ne manipulent pas directement les fichiers.
† Les utilisateurs ne s’occupent pas de la façon dont sont stockées les informations, ni où elles le sont.
. Les utilisateurs doivent pouvoir collecter, fournir ou modifier des données sans programmer. Ceci
impose l’utilisation d’un langage de description de requêtes.
Structurer des données
Les structures « plates » que nous avons jusqu’à présant étudiées ont leur limite.
. Considérons le problème suivant : créer un catalogue des œuvres exposées dans les musées de la
RMN.
. Les œuvres et les musées ont des attributs propres : nom et auteur pour les œuvres, nom et ville pour
le musée.
. On peut représenter le catalogue par une liste de musées contenant chacune une liste d’œuvres :
cat=[["Louvre","paris",[["La Joconde","De Vinci"],["Gilles","Watteau"],...]],
["Palais des beaux-arts","Lille",[["Les vieilles","Goya"],...]],...]
. Il est facile de trouver la liste des œuvres d’un musée donné au moyen d’un parcours en temps linéaire
de la liste cat.
LLG \ PCSI2
INFO 10 \ 2
PCSI2 \ 2014-2015
Laurent Kaczmarek
. La détermination de la liste de toutes les œuvres exposées dans les musées de la RMN d’un peintre
donnée est un peu plus longue à obtenir car nécessite un parcours linéaire de cat au sein duquel il faut
parcourir linéaire la liste des œuvres de chacun des musées (ce que l’on peut réaliser au moyen de deux
boucles for imbriquées).
. La situation serait encore plus délicate dans le cas de la recherche des œuvres portant le même nom.
. La manière dont est structuré cat induit une disymétrie dans le traitement des données : l’appartenance d’une œuvre à un musée est privilégiée au lien entre l’œuvre et sont auteur.
. On aurait pu bien sûr structurer inversement : une liste d’auteurs contenant une liste d’œuvre avec un
attribut musée. Dans ce cas, les difficultés sont inversées, il est plus difficile d’obtenir la liste des œuvres
d’un musée que la liste des œuvres d’un artiste.
. Les bases de données permettent de simplifier et d’améliorer l’exploration de données en les structurant de manières à ne privilégier aucun lien.
Mais alors, comment structurer les données ?
Depuis les années soixante, plusieurs modèles ont été crées. Ils correspondent à des manières différentes de structurer des données.
1. Les systèmes de gestion de bases de données
La gestion et l’accès à une base de données sont assurés par un ensemble de programmes qui constituent un système de gestion de bases de données (SGBD).
. Un SGBD doit permettre l’ajout, la modification et la recherche de données.
. Un SGBD doit masquer la représentation physique des données et assurer la cohérence et la protection des données dans le contexte d’un partage des données entre un grand nombre d’utilisateurs.
Exemple 10.1. Principaux SGBD.
Les principaux acteurs dans le secteur des SGBD commerciaux sont Oracle Corporation (Oracle), IBM
(DB2) et Mycrosoft (SQL-Server).
La réponse apportée à cette double contrainte liée au partage et à la structure des données caractérise
un SGBD.
. Les SBGD sont conçus selon l’architecture Ansi/Sparc développée par Bachman vers 1965. Elle repose
sur une division en trois niveaux : physique (là où sont stockées les données), logique (la manière dont
elles sont structurées) et externe (l’interface avec l’utilisateur).
. Un SGBD est caractérisé par le modèle de description des données qu’il supporte.
Rapide historique des modèles :
. le modèle hiérarchique, vers 1960.
. le modèle relationnel créé par Edgar F.Codd, ingénieur chez IBM, en 1970.
. le modèle objet-relationnel au début des années 90.
Le modèle relationnel est très largement utilisé de nos jours et figure au programme.
1.1. Architecture client-serveur
. Les clients accèdent directement à la base de données (ce sont le plus souvent des programmes que des
opérateurs humains, cf. les sites de vente en ligne).
. Les communications passent le plus souvent par
l’intermédiaire d’un réseau (par exemple Internet).
. L’inconvénient de cette architecture est le lien direct
des utilisateurs aux données (bas niveau de sécurité).
LLG \ PCSI2
INFO 10 \ 3
PCSI2 \ 2014-2015
Laurent Kaczmarek
1.2. Architecture trois-tiers
La terminologie est un anglicisme, il faut comprendre architecture à trois étages (3-tier in english) :
. Un client équipé d’une interface
chargée de la présentation.
. Un serveur d’application (appelé
middleware) qui fournit la ressource,
mais en faisant appel à un autre serveur.
. Un serveur de données qui fournit au
middleware les données requises pour
répondre au client.
Cette architecture renforce la sécurité des données en supprimant le lien avec le client. Le serveur applicatif a un rôle de traducteur, de gendarme et de filtre.
1.3. Gestion et interrogation d’une base de données
La création, l’administration et l’interrogation d’une base de données de taille modeste peut se faire via
une application. Il existe de nombreuses applications MySql, PhpMyAdmin, etc. Afin d’illustrer ce cours,
nous utiliserons Sqliteman, application gratuite.
Ouvrons par exemple une base de données disponible sur le site www.data.gouv.fr, la liste des prénoms
donnés à Paris de 2004 à 2013.
Les données sont structurées en quatre colonnes : prénom, nombre, sexe et année. L’utilisateur peut
modifier et/ou interroger la base en programmant en SQL dans une des fenêtres.
Dans la pratique, il est beaucoup plus facile de modifier la base de données en utilisant les différents
boutons, on n’utilise le langage SQL que pour formuler des requêtes.
2. Le modèle relationnel
Conformément au programme, nous nous limiterons au modèle relationnel. Nous illustrerons ce paragraphe par deux exemples :
LLG \ PCSI2
INFO 10 \ 4
PCSI2 \ 2014-2015
Laurent Kaczmarek
. Un exemple élémentaire, la liste des prénoms donnés à Paris entre 2004 et 2013. Les données sont
authentiques et ont été trouvées sur le site www.data.gouv.fr sous la forme d’un fichier .csv qui a
ensuite été transformé en une base de donnée. Elles sont structurées en quatre colonnes : P (prénom),
Nb (nombre), S (sexe) et A (année).
. Un exemple plus complexe, la gestion par une bibliothèque des prêts d’enregistrements sur CD
d’œuvres lyriques. Dans ce cas, on se posera en plus la question de la conception d’une base de donnée (le modèle de conception par Entités/Associations n’est pas au programme, nous nous contenterons d’énoncer quelques principes généraux).
2.1. Schéma relationnel
Le vocabulaire est spécifique (relation au lieu de tableau, attribut au lieu de colonne, enregistrement au
lieu de ligne, etc).
Définition 10.2. Relation, enregistrements, attributs, domaine, schéma relationnel
Une base de données est structurée en un nombre fini de relations :
. Une relation est un tableau comportant des lignes distinctes (pas de doublons) et des colonnes nommées.
. Les enregistrements d’une relation sont les lignes de cette relation.
. Les attributs d’une relation sont les noms donnés aux colonnes de cette relation. Si deux relations R et
R0 ont un attribut en commun A, on les distinguera en les notant R.A et R0 .A.
. Le domaine d’un attribut est l’ensemble des valeurs admissibles de cet attribut.
. Une base de données est décrite au moyen d’un schéma relationnel, ie la donnée d’un ensemble fini de
relations, chacune étant décrites par ses attributs et leurs domaines.
Exemple 10.3. La relation prenom
Par exemple, les données sur les prénoms déclarés à Paris entre 2004 et 2013 décrites ci-dessus se
concrétisent par une base de donnnée Prepar à une seule table prenom dont on trouvera un extrait
ci-dessus :
P
Nb S A
Liz
5
F 2012
Lohan 9
M 2012
Les attributs de la relation prenom sont P, Nb,
S, A. Pour la relation Prepar, les domaines des
attributs P, Nb, S et A sont respectivement : l’ensemble des chaînes de caractères, l’ensemble
des entiers naturels, l’ensemble des chaînes de
caractères et l’ensemble des entiers naturels.
Voici le schéma relationnel de prenom :
Lohan 9 M 2012
est un enregistrement de la relation prenom.
prenom
P
Chaîne de caractères
Nb Entier naturel
S
Chaîne de caractères
A
Entier naturel
2.2. Trouver « le bon » modèle relationnel
Considérons une bibliothèque souhaitant créer une base de données pour gérer le prêt de CD d’opéras.
. Une bibliothèque souhaite répertorier les différents enregistrements d’œuvres lyriques sur CD mises
au prêt.
. Il faut pouvoir intéroger cette relation afin d’obtenir les réponses à des questions telles que : combien
de fois tel CD a-t-il été emprunté ? Quel est le compositeur dont les œuvres sont les plus empruntées ?
Quel est le client qui emprunte le plus sur une période donnée ? Quel est le numéro de téléphone du
dernier client ayant emprunté tel CD ? etc.
LLG \ PCSI2
INFO 10 \ 5
PCSI2 \ 2014-2015
Laurent Kaczmarek
. Comment la bibliothèque peut-elle (doit-elle ?) organiser ces différentes données ?
. Dans un premier temps, on envisage une seule relation avec les attributs suivants : nom de l’emprunteur, date de l’emprunt, titre du CD emprunté, numéro de téléphone et adresse de l’emprunteur.
Voici un premier essai, la relation EmpruntsCD :
CD
Client Tel
Adresse Date
Orfeo
Vrick 06.11.22.33.44 Paris
15/04/2014
Parsifal Kazan 06.23.45.67.89 Lille
12/01/2014
Jenufa Vrick 06.11.22.33.44 Paris
10/05/2014
Cette première mouture a des inconvénients évidents :
. Il y a des informations redondantes (nom du client, son numéro de téléphone et son adresse) : stockage non optimal, risque d’erreur à chaque nouvelle saisie des informations redondantes (ce qui
posera problème lors des recherches dans la base).
. Le titre de l’opéra ne suffit pas à identifier un CD car il peut y avoir plusieurs versions de la même
œuvre au catalogue.
On peut y remédier en introduisant quatre relations Opera, CD, Client et Emprunt se faisant mutuellement référence.
Emprunt
Opera
Client ?
Compositeur Chaîne
↑
Chef
Chaîne
Annee Entier
Date
Date
.
↓
Client
CD
Opera ?
?
CD
Chaîne
Titre
Nom
Chaîne
Tel
Chaîne
Adresse Chaîne
Il faut faire référence sans ambiguité à un enregistrement d’une autre relation au moyen d’un attribut,
voire d’un ensemble d’attributs (cf. les points d’interrogation dans le schéma précédent).
Définition 10.4. Clé candidate
On appelle clé candidate d’une relation tout attribut ou ensemble d’attributs d’une relation permettant
d’identifier chaque enregistrement de manière unique.
Exemple 10.5. Clé candidate.
L’attribut Titre n’est pas une clé candidate de la relation CD mais les attributs Titre, Compositeur,
Chef, Date en constituent une. L’attribut Nom n’est pas une clé candidate de la relation Client mais Tel
en est une (plutôt mauvaise car un client peut changer de numéro).
. Afin d’éviter la multiplication les attributs dans une clé candidate, il est souvent plus efficace d’introduire un attribut supplémentaire, un identifiant qui, par construction, sera unique pour chaque
enregistrement de la relation.
. Ce procédé est courant : cf. les plaques minéralogiques, les numéros de sécurité sociale, les codes
ISBN, etc.
LLG \ PCSI2
INFO 10 \ 6
PCSI2 \ 2014-2015
Laurent Kaczmarek
Définition 10.6. Clé primaire
On appelle clé primaire d’une relation la clé candidate retenue. Une clé primaire d’une relation R1 utilisée comme attribut d’une autre relation R2 sera qualifiée d’extérieure à R2 (on la notera avec #).
Après réflexion, on adopte le schéma suivant pour la base de données Biblio :
Emprunt
Opera
IdCD #IdOpera Chef
Annee
1978
IdOpera
Chaîne
#IdCD
Chaîne
CD001 OP010
Mackerras
Titre
Chaîne
#IdClient Chaîne
CD002 OP003
Knapersbuch 1954
CD003 OP009
Jacobs
CD004 OP004
Britten
1965
CD005 OP006
Boulez
1974
CD006 OP002
Gardiner
1982
CD007 OP007
Bohm
1973
CD008 OP008
Solti
1968
CD009 OP009
Haim
2004
Compositeur Chaîne
↑
.
↓
CD
Client
Chaîne
IdCD
Date
Date
#IdOpera Chaîne
IdClient Chaîne
Nom
Chaîne
Chaîne
Chef
Chaîne
Tel
Annee
Entier
Adresse Chaîne
1997
Extrait de la relation CD
Schéma relationnel de la BD Biblio
IdClient Nom
IdOpera Titre
Compositeur
Tel
Adresse
CL001
Kaczmarek 06.12.34.56.78 Paris
OP001
Orphée et Eurydice Gluck
CL002
Vrick
06.11.22.33.44 Paris
OP002
Les Boréades
Rameau
CL003
Kazan
06.98.76.54.32 Lille
OP003
Parsifal
Wagner
OP004
Billy Budd
Britten
OP005
Elektra
Strauss
OP006
Wozzeck
Berg
OP007
La Flûte Enchantée Mozart
IdEmprunt #IdCD #IdClient Date
OP008
Don Carlo
Verdi
E001
CD006 CL002
12/12/2013
OP009
Orfeo
Monteverdi
E002
CD001 CL003
13/04/2014
OP010
Katia Kabanova
Janacek
E003
CD009 CL001
16/05/2014
Extrait de la relation Opera
Extrait de la relation Client
Extrait de la relation Emprunt
3. Création et alimentation d’une base de données relationnelle
Ces aspects ne sont pas au programme officiel. Nous illustrerons ces aspects au moyen de l’environnement Sqliteman.
On dispose de trois fenêtres : une pour la structure de la base de donnée, une autre où l’on peut disposer
de vues des différentes relations de la base et une dernière dédiée aux requêtes en SQL.
LLG \ PCSI2
INFO 10 \ 7
PCSI2 \ 2014-2015
Laurent Kaczmarek
3.1. Création par import de données
. On peut facilement importer un tableau au
format .xls (Microsoft Excel), .ods (Open Office) ou encore .csv (coma separated values).
. On commence pour cela par ouvrir le fichier
avec une application appropriée (un simple
éditeur de texte pour les fichiers .csv) afin de
repérer le nombre de colonnes de la future
table et avoir une idée des contenus.
. Cf. ci-contre l’extrait de la table Prenom sous
sa forme brute.
On crée ensuite sous Sqliteman une table avec
un nombre de colonnes et des types adaptés au
fichier importé.
En selectionnant la table à alimenter dans l’aborescence, on trouve dans onglet Base de données une
option Importer les données de la table.
LLG \ PCSI2
INFO 10 \ 8
PCSI2 \ 2014-2015
Laurent Kaczmarek
On peut choisir (et prévisualiser) le fichier à importer en naviguant dans les répertoires.
On peut alors obtenir une vue de la table dans sqliteman.
Une fenêtre permet d’effectuer des requêtes SQL dans la base (fichier .db) ainsi créée.
LLG \ PCSI2
INFO 10 \ 9
PCSI2 \ 2014-2015
Laurent Kaczmarek
3.2. Création ex-nihilo
On crée une table comme vu précédemment. En la selectionnant, on accède à l’option Peupler la table.
On pourra ensuite obtenir une vue de la table et l’alimenter au moyen des boutons.
LLG \ PCSI2
INFO 10 \ 10
PCSI2 \ 2014-2015
Laurent Kaczmarek
4. L’algèbre relationnelle
Dans ce paragraphe, nous allons décrire plusieurs opérations sur les relations. Le but est d’arriver à extraire l’information que l’on cherche dans la base de données en créant une relation donnant la réponse.
Considérons par exemple la relation prenom :
P
Nb S A
Liz
5 F 2012
Lohan 9 M 2012
Pour déterminer le nombre de fois que le prénom Julie a été donné à Paris en 2012, il suffit de considérer
la relation prenom et de supprimer les lignes inutiles (celles où A 6= 2012 ou P 6= "Julie").
Afin de trouver une information dans une base de données, on effectue des opérations sur les relations.
Les traitements que nous ferons subir aux relations formant une base de données s’apparentent à des
opérations ensemblistes (intersection, etc) mais aussi à des collages, des découpages, des suppressions
de lignes ou de colonnes, etc. L’algèbre relationnelle étudie ces opérations.
4.1. Opérations sur les relations
Définition 10.7. Union, intersection et différence
Soient R et R0 deux relations ayant le même schéma relationnel. On définit les relations suivantes par le
même schéma relationnel que R et R0 et les conditions suivantes :
. la réunion de R et R0 est la relation formée des enregistrements figurant dans R ou R0 .
. L’intersection de R et R0 est la relation formée des enregistrements figurant dans R et R0 .
. R privé de R0 est la relation formée des enregistrements figurant dans R mais pas dans R0 .
On note respectivement R ∪ R0 , R ∩ R0 et R \ R0 .
Exemple 10.8. Union, intersection et différence.
Soient R et R0 les relations suivantes.
R
Prénom Classe
Guy
TS1
François TS5
Julie
TS3
Sexe
M
M
F
R’
Classe
TS2
TS1
TS5
Sexe
M
M
M
Prénom
Gustave
Viviane
François
R ∪ R’
Prénom Classe
Guy
TS1
François TS5
Julie
TS3
Gustave TS2
Viviane TS1
R \ R’
Sexe
M
M
F
M
M
Prénom Classe Sexe
Guy
TS1
M
Julie
TS3
F
R ∩ R’
Prénom Classe Sexe
François TS5
M
La projection
Définition 10.9. Projection
La projection d’une relation R sur les attributs A1 , . . ., An est la relation obtenue à partir de R en supprimant les attributs ne figurant pas dans {A1 , . . . , An } et en supprimant les éventuels enregistrements
doublons. On note π[A1 , . . . , An ](R) la projection de R sur A1 , . . ., An .
LLG \ PCSI2
INFO 10 \ 11
PCSI2 \ 2014-2015
Laurent Kaczmarek
Exemple 10.10. Une projection.
Une illustration sur une relation à trois attributs.
π[Prénom,Sexe](R) est la relation :
Soit R définie par :
Prénom
John
Julie
Julie
Marc
Classe
TS4
TS3
TS6
TS3
Sexe
M
F
F
M
Prénom
John
Julie
Marc
Sexe
M
F
M
La sélection
Définition 10.11. Sélection
La sélection de R suivant une condition C (portant sur ses attributs) est la relation de même schéma
relationnel que R mais dont les enregistrements sont les enregistrements de R vérifiant la condition C. On
note σ[C](R) .
Exemple 10.12. Une sélection.
On reprend la relation de l’exemple précédent :
σ[Sexe="F"](R) est la relation :
R est définie par :
Prénom
John
Julie
Julie
Marc
Classe
TS4
TS3
TS6
TS3
Sexe
M
F
F
M
Prénom Sexe
Julie
TS3 F
Julie
TS6 F
Le renommage
Définition 10.13. Renommage
Le renommage de l’attribut A de R en A0 est la relation obtenue en changeant dans R le nom de A en A0 .
On note ρ[A : A0 ](R) .
Exemple 10.14. Un renommage.
On reprend la même relation :
ρ[Sexe :S](R) est la relation :
R est définie par :
Prénom
John
Julie
Julie
Marc
Classe
TS4
TS3
TS6
TS3
Sexe
M
F
F
M
Prénom
John
Julie
Julie
Marc
Classe
TS4
TS3
TS6
TS3
S
M
F
F
M
Le produit cartésien
Définition 10.15. Produit cartésien
Soient R et R0 deux relations n’ayant pas d’attribut commun. de R par R0 est la relation formée de toutes
les combinaisons possibles d’enregistrements des relations R et R0 . On note R × R0 .
LLG \ PCSI2
INFO 10 \ 12
PCSI2 \ 2014-2015
Laurent Kaczmarek
Exemple 10.16. Un produit cartésien.
Toujours le même exemple.
R est définie par
Prénom
John
Julie
Julie
Marc
Prénom Classe Sexe Numéro Ville
Classe
TS4
TS3
TS6
TS3
Sexe
M
F
F
M
...et R0 par...
Numéro Ville
123
Naples
567
Venise
John
TS4
M
123
Naples
Julie
TS3
F
123
Naples
Julie
TS6
F
123
Naples
Marc
TS3
M
123
Naples
John
TS4
M
567
Venise
Julie
TS3
F
567
Venise
Julie
TS6
F
567
Venise
Marc
TS3
M
567
Venise
Le produit cartésien R × R0 est donné par :
La division cartésienne
Définition 10.17. Division cartésienne
Soient R et R0 deux relations tels que les attributs de R0 soient des attributs de R. La division cartésienne
de R par R0 est la plus grande relation R00 telle que R00 × R0 soit contenue dans R. On note R ÷ R0 .
Exemple 10.18. Une division cartésienne.
Idem :
Prénom Sexe
Julie
F
Marc
M
R est définie par...
Prénom
John
Julie
Julie
Marc
Classe
TS4
TS3
TS6
TS3
Sexe
M
F
F
M
...et R0 par...
La division cartésienne R ÷ R0 est donnée par :
Classe
TS3
C’est la liste des classes où tous les prénoms de
R0 apparaissent.
La jointure
Définition 10.19. Jointure
La jointure de deux relations R et R0 suivant une condition C donnée est la sous-relation de R×R0 formée
des enregistrements vérifiant C. On note R \ [C]R0 .
Exemple 10.20. Une jointure.
Idem !
LLG \ PCSI2
INFO 10 \ 13
PCSI2 \ 2014-2015
Laurent Kaczmarek
R définie par...
La jointure R \ [Classe=Terminale]R0 est donnée
par :
Prénom
John
Julie
Julie
Marc
Classe
TS4
TS3
TS6
TS3
Sexe
M
F
F
M
Prénom Classe Sexe Terminale Voyage
...et R0 par...
Terminale Voyage
TS3
Venise
TS4
Londres
John
TS4
M
TS4
Londres
Julie
TS3
F
TS3
Venise
Marc
TS3
M
TS3
Venise
4.2. Groupes et fonctions d’agrégation
Les fonctions d’agrégation permettent d’effectuer des opérations statistiques sur une relation R : comptage, maximum, minimum, somme et moyenne.
Soient A1 , . . ., An des attributs de R. Il est possible de n’effectuer ces opérations que sur les groupes
d’enregistrements de R ayant les mêmes attributs A1 , . . ., An .
Définition 10.21. Fonctions d’agrégation, groupes
Soit R une relation.
. Soient A, B des attributs de R et f une fonction d’agrégation, on note A γ(R)A, f (B) la relation à deux
attributs, A et f (B), dont chaque enregistrement est formé des valeurs de A et f (B) sur les groupes
d’enregistrements de R ayant le même attribut A.
. On généralise à
A1 ,...,Am γ(R)A01 ,...,A0p , f 1 (B1 ),..., f k (Bk )
avec {A1 , . . . , A0p } ⊂ {A1 , . . . , Am }.
Exemple 10.22. Fonctions d’agrégation et groupes.
Toujours le même exemple...
C γ(R)somme(N)
R est définie par :
P
John
Julie
Julien
Marc
C
TS3
TS2
TS2
TS3
N
17
15
12
14
est :
somme(N)
31
27
C γ(R)C,somme(N)
est :
C somme(N)
TS3 31
TS2 27
4.3. Exemples de requêtes sur la base de données prenoms
Dans ce paragraphe, nous allons voir comment utiliser l’algèbre relationnelle pour répondre à des questions portant sur des informations rangées dans une base de données.
Petit rappel, voici la base de données prenom :
LLG \ PCSI2
INFO 10 \ 14
PCSI2 \ 2014-2015
Laurent Kaczmarek
P
Nb S A
Liz
5
F 2012
Lohan 9
M 2012
Comment obtenir les prénoms donnés en 2008 ou 2012 ?
On effectue une sélection sur la date suivie d’une projection sur les prénoms.
. S = σ[A = 2008](prenom), T = σ[A = 2012](prenom) et U = S ∪ T :
Emilien 14 M 2005
Elio
P
Alban
Anna
Liz
Lohan
11 M 2007
P
Nb S A
Alban 24 M 2008
Anna 127 F 2008
Margot 75 F 2013
Alienor 24 F 2006
Cesar
25 M 2006
Alban
24 M 2008
Anna
127 F 2008
Nb
24
127
5
9
S
M
F
F
M
A
2008
2008
2012
2012
. π[P](U)
P
Alban
Anna
Liz
Lohan
Zakaria 32 M 2009
Abdoul 10 M 2010
Amalia 7
P
Nb S A
Liz
5 F 2012
Lohan 9 M 2012
F 2011
La relation prenom
Combien de prénoms ont-ils été donnés entre 2011 et 2013 ?
On commence par une sélecction sur la date puis on applique une fonction de comptage.
. T = σ[A > 2010](prenom)
P
Liz
Lohan
Margot
Amalia
Nb
5
9
75
7
. γ(T)somme(Nb)
S
F
M
F
F
A
2012
2012
2013
2011
somme(Nb)
96
4.4. Exemples de requêtes sur la base de données Biblio
On reprend l’exemple de la base de données Biblio permettant la gestion des emprunts de CD d’opéras
dans une bibiothèque. Voici le schéma de la base de données suivie de ses relations.
Opera
Emprunt
IdOpéra
Chaîne
#IdCD
Titre
Chaîne
#IdClient Chaîne
Compositeur Chaîne
↑
Date
Date
.
↓
CD
IdCD
Chaîne
Client
Chaîne
#IdOpéra Chaîne
IdClient
Chaîne
Nom
Chaîne
Chef
Chaîne
Téléphone Chaîne
Année
Entier
Adresse
IdCD
CD001
CD002
CD003
CD004
CD005
CD006
CD007
CD008
CD009
CD010
#IdOpéra
OP010
OP003
OP009
OP004
OP006
OP002
OP007
OP008
OP009
OP011
Chef
Mackerras
Kubelik
Jacobs
Britten
Boulez
Gardiner
Bohm
Solti
Haim
Colin Davis
Annee
1978
1982
1997
1965
1974
1982
1973
1968
2004
1978
Relation CD
Chaîne
La base de donnée Biblio
LLG \ PCSI2
INFO 10 \ 15
PCSI2 \ 2014-2015
IdOpera
OP001
OP002
OP003
OP004
OP005
OP006
OP007
OP008
OP009
OP010
OP011
Laurent Kaczmarek
Titre
Alceste
Les Boréades
Parsifal
Billy Budd
Elektra
Wozzeck
Cosi fan tutte
Don Carlo
Orfeo
Katia Kabanova
Peter Grimes
Compositeur
Gluck
Rameau
Wagner
Britten
Strauss
Berg
Mozart
Verdi
Monteverdi
Janacek
Britten
IdClient
CL001
CL002
CL003
Nom
Kaczmarek
Vrick
Kazan
Tel
06.12.34.56.78
06.11.22.33.44
06.98.76.54.32
Adresse
Paris
Paris
Lille
La relation Client
IdEmprunt
E001
E002
E003
E004
E005
E006
Relation Opera
#IdCD
CD006
CD001
CD009
CD004
CD001
CD010
#IdClient
CL002
CL003
CL001
CL002
CL003
CL001
Date
12/12/2013
13/04/2014
16/05/2014
12/12/2013
13/04/2014
16/05/2014
Relation Emprunts
Date des emprunts des opéras de Britten ?
. R = σ[Compositeur=Britten]Opera
IdOpera Titre
Compositeur
OP004 Billy Budd
Britten
OP011 Peter Grimes Britten
. S = R \ [R.IdOpera=CD.IdOpera]CD
IdOpera Titre
Compo. IdCD IdOpera Chef
Annee
OP004 Billy Budd
Britten CD004 OP004 Britten
1965
OP011 Peter Grimes Britten CD010 OP011 Colin Davis 1978
. T = S \ [S.IdCD=Emprunt.IdCD]Emprunt
IdOpera Titre
Compositeur IdCD IdOpera Chef
...
OP004 Billy Budd
Britten
CD004 OP004 Britten
...
OP011 Peter Grimes Britten
CD010 OP011 Colin Davis . . .
. . . Annee IdEmprunt #IdCD #IdClient Date
. . . 1965 E004
CD004 CL002
12/12/2013
. . . 1978 E006
CD010 CL001
16/05/2014
. π[Dat e](T)
Date
12/12/2013
16/05/2014
Nombre de CD empruntés par le client Kaczmarek ?
. R = σ[Nom="Kaczmarek"](Client), S = R \ [R.IdClient=Emprunt.IdClient]Emprunt et finalement
T = γ(S)comptage(Nom)
count(Nom)
2
LLG \ PCSI2
INFO 10 \ 16
PCSI2 \ 2014-2015
Laurent Kaczmarek
5. Le langage SQL
SQL, structured query language (langage de requête structurée) est un langage informatique normalisé
servant à exploiter des bases de données relationnelles. Il permet de rechercher, d’ajouter, de modifier
ou de supprimer des données dans les bases de données relationnelles. Créé en 1974, normalisé depuis
1986, le langage est supporté par la grande majorité des SGBD du marché. Il vpermet de traduire quasilittéralement les opérations de la’algèbre relationnelle.
Nous illustrerons ce paragraphe par la base de données à deux relations suivante :
ville
Lille
Douai
Paris
Quimper
id
01
01
02
03
id
01
02
03
pop
225787
42621
2221000
63235
population
nom
Nord-Pas-de-Calais
Ile-de-France
Bretagne
region
Distinguer les attributs
En SQL, on distinguera les colonnes id de chacune de ces relations en écrivant departement.id et
population.id.
Attention, les tables sont temporaires.
Hormis les tables de la base de données, toutes les tables créées en SQL ne sont que temporaires,
on ne peut pas les enregistrer dans une variable. Il faudra donc effectuer chacune des requêtes en
une seule ligne de commande.

5.1. Traduction en SQL des opérations de l’algèbre relationnelle
La projection
. C’est la commande SELECT qui permet d’effectuer une projection (et non une sélection).
SELECT ville,id FROM population
ville
Lille
Douai
Paris
Quimper
id
01
01
02
03
. On utilise SELECT * pour projeter sur toutes les colonnes.
. Attention, la commande SELECT n’élimine pas les doublons. On obtiendra une sélection au sens de
l’algèbre relationnelle en précisant SELECT DISTINCT.
SELECT id FROM population
id
01
01
02
03
LLG \ PCSI2
SELECT DISTINCT id FROM population
id
01
02
03
INFO 10 \ 17
PCSI2 \ 2014-2015
Laurent Kaczmarek
La sélection
. On peut effectuer une sélection en ajoutant une clause WHERE à SELECT.
SELECT ville,pop FROM population WHERE id=01
ville pop
Lille 225787
Douai 42621
. Liste des id des régions où il existe des villes de population supérieure strictement à 70000.
id
01
02
SELECT DISTINCT id FROM population WHERE pop>70000
. On peut construire des conditions plus élaborées en utilisant les opérateurs logiques AND, OR et
NOT.
ville pop
Lille 225787
SELECT ville,pop FROM population WHERE id=01 AND pop>45000
Renommage
. C’est le mot-clé AS qui permet le renommage d’une colonne.
SELECT ville,pop AS city,popu FROM population
city
Lille
Douai
Paris
Quimper
popu
225787
42621
2221000
63235
Produit cartésien
. On décrit un produit cartésien de tables en énumérant les tables après FROM.
SELECT * FROM population,region
ville
Lille
Lille
Lille
Douai
Douai
Douai
Paris
Paris
Paris
Quimper
Quimper
Quimper
population.id
01
01
01
01
01
01
02
02
02
03
03
03
pop
225787
225787
225787
42621
42621
42621
2221000
2221000
2221000
63235
63235
63235
region.id
01
02
03
01
02
03
01
02
03
01
02
03
nom
Nord-Pas-de-Calais
Ile-de-France
Bretagne
Nord-Pas-de-Calais
Ile-de-France
Bretagne
Nord-Pas-de-Calais
Ile-de-France
Bretagne
Nord-Pas-de-Calais
Ile-de-France
Bretagne
. La présence du même attribut id dans les deux tables ne pose pas de problème car ils sont en fait
manipulés comme region.id et population.id.
Division cartésienne
. Il n’existe aucune commande pour la division cartésienne en SQL ; on peut la simuler par d’autres
commandes.
LLG \ PCSI2
INFO 10 \ 18
PCSI2 \ 2014-2015
Laurent Kaczmarek
Jointure
. Une jointure s’effectue au moyen de JOIN ... ON.
SELECT ville,pop,nom FROM population JOIN region ON population.id=region.id
ville
Lille
Douai
Paris
Quimper
pop
225787
42621
2221000
63235
nom
Nord-Pas-de-Calais
Nord-Pas-de-Calais
Ile-de-France
Bretagne
. Obtenir la liste des villes de la région Nord-Pas-de-Calais :
SELECT ville FROM population,region
WHERE population.id=region.id AND nom="Nord-Pas-de-Calais"
ville
Lille
Douai
. On peut effectuer une jointure multiple par :
SELECT ... FROM table1 JOIN table2 JOIN table3 ON cond1 AND cond2 ...

Il faut éviter les produits cartésiens.
On peut simuler une jointure en parcourant un produit cartésien avec une clause WHERE. Cette
méthode est à proscrire car oblige à calculer le produit cartésien
SELECT ville,nom FROM population,region WHERE population.id=region.id
qui pour certaines tables a une taille gigantesque. La jointure permet d’éviter l’explosion de la complexité due à la taille du produit cartésien.
Opérations ensemblistes
. L’opérateur UNION réalise la réunion de deux tables.
SELECT * FROM table1 UNION SELECT * FROM table2
. L’opérateur INTERSECT réalise la réunion de deux tables.
SELECT * FROM table1 INTERSECT SELECT * FROM table2
. L’opérateur EXCEPT réalise la différence de deux tables.
SELECT * FROM table1 EXCEPT SELECT * FROM table2
Comptage, max, min, somme et moyenne
. Le comptage des enregistrements d’une table s’effectue au moyen de la fonction COUNT.
SELECT COUNT(*) AS total FROM region
total
3
. MAX, AVG, MIN et SUM permettent de déterminer le maximum, la moyenne, le minimum et la
somme d’une colonne.
SELECT MAX(pop) AS maximum FROM population
LLG \ PCSI2
maximum
2221000
INFO 10 \ 19
PCSI2 \ 2014-2015
Laurent Kaczmarek
Groupement
. On peut appliquer une fonction à un groupe de lignes en utilisant la commande GROUP BY.
SELECT nom,SUM(pop) AS popu FROM population,region
WHERE population.id=region.id GROUP BY nom
nom
Nord-Pas-de-Calais
Ile-de-France
Bretagne
popu
268408
2221000
63235
. La clause HAVING remplace WHERE lorsque les colonnes intervenant dans la condition proviennent
d’une fonction.
SELECT nom,SUM(pop) AS popu FROM population,region WHERE population.id=region.id
GROUP BY nom HAVING popu<65000
nom
popu
Bretagne 63235
Ordonner selon un attribut
. C’est la clause ORDER BY expression ASC / DESC qui permet de ranger une table selon une relation
d’ordre spécifiée (par exemple ordre alphabétique, ordre usuel sur R, dates, etc).
SELECT ville,pop FROM population ORDER BY pop ASC
ville
Douai
Quimper
Lille
Paris
pop
42621
63235
225787
2221000
Sous-requêtes
. Il est possible d’imbriquer une requête dans une clause SELECT, ou (le plus souvent) au sein d’un
filtre WHERE ou HAVING, la sous-requête devant être délimitée par des parenthèses. On peut par
exemple selectionner les villes ayant une population strictement supérieure à la moyenne nationale.
¡
¢
SELECT ville FROM population WHERE pop> SELECT AVG(pop) FROM population
ville
Paris
5.2. Création et alimentation d’une base de données en SQL
Il est possible de créer et de supprimer une base de données au moyen d’un code SQL. De même, il est
possible de l’alimenter par du code SQL. Ces aspects en sont pas au programme.
LLG \ PCSI2
INFO 10 \ 20
PCSI2 \ 2014-2015
Laurent Kaczmarek
5.3. Requêtes depuis une application de gestion de SGBD
On code en SQL dans un interpréteur, par exemple dans Sqliteman :
5.4. Requêtes depuis un interpréteur Python
Il est possible de manipuler une base de données directement depuis un interpréteur Python. On utilise
pour cela la bibliothèque sqlite3. On commence par créer une connexion à la base de données au moyen
de la méthode connect :
conn=sqlite3.connect(’nom.db’)
On crée alors un curseur au moyen de la méthode cursor :
c=conn.cursor()
On peut alors utiliser du code SQL encapsulé au moyen de execute :
c.execute(’SELECT...’)
On récupère le résultat au moyen de la méthode fetchall :
print(c.fetchall())
Exemple 10.23. Connection à une bade de données depuis un interpréteur Python.
>>> import sqlite3
>>> conn=sqlite3.connect(’films.db’)
>>> c=conn.cursor()
>>> c.execute(’SELECT prenom FROM acteur’)
<sqlite3.Cursor object at 0xb582e760>
>>> print(c.fetchall())
[(u’Tim’,), (u’Morgan’,), (u’Bob’,),...
LLG \ PCSI2
INFO 10 \ 21