Cet article, ainsi que Des fichiers Excel au SQL, bonnes pratiques avec Excel et LibreOffice Calc a été publié à la suite de la séance du 10 février 2020 du séminaire Outils du Quanti en SHS, Des fichiers Excel au SQL, introduction aux bases de données relationnelles .

Si vous stockez des données dans des fichiers Excel ou LibreOffice Calc (fichiers .ods) et que vous êtes confronté à des difficultés pour saisir vos données et les interroger, le passage à une base de données relationnelles est peut-être à envisager.

Admettons que vous souhaitiez étudier la production littéraire en langue bretonne. Vous créez un fichier Excel avec une colonne Titre pour saisir le titre des œuvres. Mais comment allez-vous gérer la saisie des auteurs ? Si une œuvre a plusieurs auteurs, allez-vous créer autant de colonnes que d’auteurs ou saisir les auteurs dans la même cellule de la colonne Auteurs ? Dans ce cas, comment trier les œuvres par auteur ? Comment afficher toutes les œuvres écrites par le même auteur ? Et si vous souhaitez par exemple connaître toutes les œuvres écrites par des auteurs nés à Quimperlé au XIXe siècle, comment saisir la ville et l’année de naissance des auteurs ? Vous pouvez choisir de créer une nouvelle feuille de calcul pour ne saisir que les données sur les auteurs, attribuer un chiffre pour identifier les auteurs et ne saisir dans la feuille des œuvres que l’identifiant des auteurs. Mais si vous supprimez un auteur, comment mettre à jour automatiquement les œuvres qui lui ont été attribuées ?

Pour résoudre ce type de difficultés, une solution : les bases de données relationnelles. Voyons tout de suite à quoi pourrait ressembler nos données sur la production littéraire en breton dans la base de données prelib.odb créée avec le logiciel LibreOffice Base. Les données de ce fichier sont une extraction de la base de données en ligne PRELIB¹ (Projet de recherche en littérature de langue bretonne) du Centre de recherche bretonne et celtique².

Les bases de données relationnelles stockent les données dans des tables ou relations, d’où le terme relationnel pour désigner ce type de bases de données. Le contenu d’une table peut être affichée sous la forme de tableau, avec des lignes et des colonnes, mais une table de base de données n’a rien à voir avec une feuille de calcul Excel.

Les systèmes de gestion de bases de données relationnelles

Pour créer et gérer des bases de données relationnelles, il faut un SGBD relationnel, un système de gestion de bases de données relationnelles. Les SGBD relationnels les plus courants sont MySQL, SQLite et PostGreSQL.

Vous n’avez probablement jamais entendu parler de ces SGBD mais lorsque vous consultez votre messagerie, quand vous mettez à jour votre blog sous WordPress, quand vous utilisez Dropbox, iTunes ou Skype, quand vous consultez en ligne votre journal favori, vous utilisez sans le savoir un SGBD.

Le principal avantage des bases de données relationnelles est le SQL, acronyme de Structured Query Language, un langage de requête qui permet de rechercher, ajouter, modifier ou supprimer des données dans les bases de données relationnelles.

LibreOffice Base

Dans ce tutoriel, vous allons utiliser LibreOffice Base, l’équivalent de Microsoft Access. Un SGBD relationnel de la suite LibreOffice. A l’inverse de MySQL, SQLite ou PostGreSQL, LibreOffice Base n’est pas adapté pour le partage des données sur un réseau informatique ou sur le web mais il fait très bien l’affaire pour un usage sur son ordinateur personnel.

Tables, clés primaires, clés étrangères et schéma de la base de données

Le fichier prelib.odb (à télécharger) contient quatre tables : Personne, Ville, Oeuvre et EcritOeuvre. Si vous ouvrez ce fichier avec LibreOffice Base et que vous double-cliquez sur chacune de ces tables, vous constaterez qu’elles ont toutes une colonne (ou attribut) “ID”. Cette colonne est la clé primaire de chaque table, sa valeur permet d’identifier de manière unique chaque ligne (ou enregistrement) de la table. Notez que si nous avions eu la garantie par exemple, qu’aucune personne ne pouvait avoir le même nom, la même date et la même ville de naissance qu’une autre, la combinaison de ces trois colonnes aurait pu servir de clé primaire pour la table Personne.

Dans la table Personne, les villes de naissance et de décès ne sont pas saisies en toutes lettres mais en faisant référence aux clés primaires de la table Ville. Par exemple, le premier individu, Jean-Guillaume Henry, a pour identifiant de ville de naissance le numéro 215, qui correspond dans la table Ville à la clé primaire de la commune de Mellac. Les attributs ville_naissance_id, ville_deces_id sont appelées clés étrangères.

Le fait de saisir les villes dans une table séparée plutôt que directement dans la table Personne a plusieurs avantages :

1. La table Ville peut contenir d’autres informations que le nom de la ville, par ex. les coordonnées géospatiales pour l’affichage d’une carte, l’identifiant de la ville dans d’autres référentiels (code INSEE pour les villes françaises, identifiant Geonames).
2. La Ville peut être utilisée pour d’autres tables. Par exemple, pour saisir la ville de l’éditeur de l’œuvre.
3. Si l’on se trompe dans l’orthographe du nom de la ville, il suffit de le mettre à jour dans la table Ville et non plus partout où il a été utilisé, comme ce serait le cas avec un fichier Excel.

♣ Le nom de la ville n’aurait pas pu servi de clé primaire (d’identifiant unique d’une ville) car le vaste Monde ne manque pas de villes qui portent le même nom. Le code postal n’aurait pas pu servir de clé primaire car certains codes postaux regroupent plusieurs communes. Les codes communes de l’INSEE n’aurait également pu faire l’affaire car nous n’aurions pas pu saisir de villes postérieures à la création de l’INSEE et des villes de pays étrangers.

Bien entendu, la gestion des clés primaires doit être transparente pour l’utilisateur. En général, on laisse au logiciel de base de données le soin de générer la clé primaire pour chaque nouvel enregistrement.

Le schéma de la base de données (menu Outils > Relations) permet d’avoir une vue synthétique des tables et des relations entre elles.

Le fichier prelib.odb ne contient pas de formulaires de saisie mais il est possible d’en créer pour faciliter le choix des villes de naissance et de décès à l’aide de menus déroulants.

Qui est né à Mellac ?

Voyons maintenant notre première requête à l’aide du langage SQL. Allez dans le menu Insertion > Requête (mode SQL), saisissez la requête ci-dessous, puis choisissez dans le menu Édition > Exécuter la requête :

SELECT nom FROM Personne WHERE ville_naissance_id = 215

Le résultat de la requête est Jean-Guillaume Henry :

La commande SELECT permet d’extraire des données de la base. Entre SELECT et FROM, on précise les informations que l’on souhaite récupérer. FROM permet de préciser la table dans laquelle effectuer la requête.  La commande WHERE permet de préciser que l’on ne veut que les individus nés à Mellac. Le résultat est un nouveau tableau qui ne contient, selon ce critère, qu’une seule colonne (le nom de l’individu) et une seule ligne.

Pour afficher toutes les colonnes de la table Personne correspondant à ce critère, il suffit d’utiliser * :

SELECT * FROM Personne WHERE Personne.ville_naissance_id = 215

Qui sont les femmes nées au XIXe siècle ?

La commande optionnelle WHERE (combinée avec AND ou OR) peut s’appliquer à n’importe quelle colonne de la table Personne. Par exemple, la requête suivante permet d’afficher les femmes nées au XIXe siècle :

SELECT nom, sexe, annee_naissance, annee_deces FROM Personne WHERE annee_naissance < 1900 AND annee_naissance >= 1800 AND sexe = 'F'

LIKE associé à % permet d’afficher les femmes dont le nom contient “Jeanne” (la requête est sensible à la case).

SELECT nom, sexe, annee_naissance, annee_deces FROM Personne WHERE sexe = 'F' AND nom LIKE '%Jeanne%'

Notez que le sexe a été précisé entre guillemets car cet attribut est de type Texte (chaîne de caractères, nombres y compris). En effet, dans une base de données relationnelle, tous les attributs (ou colonnes) des tables sont typés. Dans LibreOffice Base, les types peuvent être affichés par un clic-droit sur la table puis Éditer.

Les principaux types de données sont Texte (VARCHAR), Entier (INTEGER), Nombre (NUMERIC) et Date.

Des fonctions peuvent être appliquées aux attributs selon leur type. Pour les entiers et les nombres : SUM (somme), AVG (“average”, moyenne), SQRT (“square”, racine carrée), EXP (exponentielle), etc. Pour les dates : DATEDIFF (différence entre deux dates), DAY (jour du mois), YEAR (année de la date), etc. Toutes les fonctions sont listées dans le chapitre Built-in functions and stored procedures du LibreOffice Base Handbook (PDF).

Dans prelib.odb, les dates ont été séparées en trois attributs jour, mois et année car les dates de naissance et de décès sont parfois incomplètes. Or le format Date n’autorise que la saisie que de dates dont le jour, le mois et l’année sont connus.

Classement par année de naissance

Pour faciliter la lecture de la liste des femmes nées au XIXe siècle, la commande ORDER BY permet de classer les résultats par année de naissance croissante (ASC = ascendant, DESC = descendant).

SELECT nom, sexe, annee_naissance, annee_deces FROM Personne WHERE annee_naissance < 1900 AND annee_naissance >= 1800 AND sexe = 'F' ORDER BY annee_naissance ASC

Nombre de femmes nées au XIXe siècle

Pour afficher le nombre de femmes nées au XIXe siècle, il faut utiliser la commande COUNT(*) :

SELECT COUNT(*) FROM "Personne" WHERE "annee_naissance" < 1900 AND "annee_naissance" >= 1800 AND "sexe" = 'F'

Il est possible de préciser le nom d’une colonne dans les paramètres de COUNT. Dans ce cas, COUNT(nom_colonne) renvoie le nombre de lignes où le contenu de la colonne est non NULL (NULL correspond à “aucune valeur saisie”). Par ex. la requête suivante renvoie le nombre de personnes pour lesquelles le jour de naissance a été saisi :

SELECT COUNT(jour_naissance) FROM "Personne"

Il est même possible d’afficher le nombre de personnes pour lesquelles le jour de naissance n’a pas été saisi avec cette soustraction :

SELECT COUNT(*) - COUNT(jour_naissance) FROM "Personne"

Nombre de femmes nées dans l’année

La commande GROUP BY permet de regrouper les lignes selon certains critères. Par exemple, la requête suivante regroupe les femmes par année de naissance et affiche le nombre de femmes nées dans l’année :

SELECT COUNT(*), "annee_naissance" FROM "Personne" GROUP BY "annee_naissance" ORDER BY "annee_naissance" ASC

Puisque nous avons utilisé COUNT(*) et non pas COUNT(annee_naissance),  le résultat renvoie également le nombre de femmes (28) dont l’année de naissance n’a pas été saisie.

Les femmes nées au XIXe et leur commune de naissance

Pour afficher toutes les femmes nées au XIXe avec leur ville de naissance, nous allons faire  une jointure (JOIN) avec la table Ville, c’est à dire une table virtuelle constituée par la combinaison des tables Personne et Ville par l’intermédiaire de la clé étrangère de Personne, ville_naissance_id, et de la clé primaire de ville, ID. La jointure permet d’ajouter Ville.nom au SELECT.

SELECT Personne.nom AS NomPersonne, Ville.nom AS NomVille FROM Personne JOIN Ville ON Ville.ID = Personne.ville_naissance_id WHERE annee_naissance < 1900 AND annee_naissance >= 1800 AND sexe = 'F' ORDER BY NomPersonne ASC

L’attribut nom étant utilisé tout aussi bien dans la table Ville que dans table Personne, on préfixe les attributs par le nom de la table (par ex. Personne.nom). Et pour afficher des titres de colonnes du résultat plus explicites, on utilise des alias (commande AS), dont on se sert aussi pour afficher les résultats par ordre alphabétique du nom des femmes (ORDER BY NomPersonne ASC).

Si vous utilisez un SELECT * , vous pourrez constater que la réponse à cette requête est bien un tableau où chaque ligne est constituée de tous les attributs d’une femme (ID, sexe, nom, jour_naissance, …, ville_naissance_id, jour_deces, … ville_deces_id, …, ark_bnf, isni) et tous les attributs de sa ville de naissance (ID, nom, wikidata).

SELECT * FROM Personne JOIN Ville ON Ville.ID = Personne.ville_naissance_id WHERE annee_naissance < 1900 AND annee_naissance >= 1800 AND sexe = 'F' ORDER BY Personne.nom ASC

Notez que la requête précédente ne renvoie que 13 résultats alors qu’il y a 20 femmes nées au XIXe dans prelib.odb. Cela vient du fait que la ville de naissance n’a pas été saisie pour 7 d’entre elles. La commande LEFT JOIN permet d’intégrer dans le résultat toutes les femmes, (toutes les lignes de Personne correspondant au critère annee_naissance < 1900 AND annee_naissance >= 1800 AND sexe = 'F') même si la correspondance Ville.ID = Personne.ville_naissance_id n’est pas possible :

SELECT Personne.nom AS NomPersonne, Personne.annee_naissance, Ville.nom AS NomVille FROM Personne LEFT JOIN Ville ON Ville.ID = Personne.ville_naissance_id WHERE annee_naissance < 1900 AND annee_naissance >= 1800 AND sexe = 'F' ORDER BY Personne.annee_naissance ASC

Puisque l’on sait écrire une requête pour afficher toutes les femmes nées au XIXe et leur ville de naissance, avec une case vide si cette information est absente, on peut donc afficher les femmes qui n’ont pas encore de ville de naissance en rajoutant le critère  AND Ville.nom IS NULL (NULL correspond à “aucune valeur saisie”) :

SELECT Personne.nom AS NomPersonne, Personne.annee_naissance, Ville.nom AS NomVille FROM Personne LEFT JOIN Ville ON Ville.ID = Personne.ville_naissance_id WHERE annee_naissance < 1900 AND annee_naissance >= 1800 AND sexe = 'F' AND Ville.nom IS NULL ORDER BY Personne.annee_naissance ASC

Toutes les femmes ayant écrit une œuvre

Les personnes sont liées aux œuvres qu’ils ont écrites par l’intermédiaire de la table EcritOeuvre, car une clé étrangère oeuvre_id ajoutée à la table Personne ne permettrait pas la saisie d’un nombre illimité d’œuvres écrites par le même individu. EcritOeuvre a trois attributs, sa clé primaire, ID, et deux clés étrangères, oeuvre_id et personne_id, qui font référence aux clés primaires de Oeuvre et Personne respectivement.

Une femme auteure est donc une personne de sexe féminin qui apparait au moins une fois dans EcritOeuvre. Afin de récupérer le nom de l’auteure, on crée une jointure avec la table Personne :

SELECT Personne.nom FROM EcritOeuvre JOIN Personne ON Personne.ID = EcritOeuvre.personne_id WHERE Personne.sexe = 'F' ORDER BY Personne.nom

Notez que l’on aurait tout aussi bien faire un SELECT sur Personne et faire une jointure avec EcritOeuvre :

SELECT Personne.nom FROM Personne JOIN EcritOeuvre ON Personne.ID = EcritOeuvre.personne_id WHERE Personne.sexe = 'F' ORDER BY Personne.nom

Par contre, certaines femmes sont affichées plusieurs fois :

C’est normal, la jointure associe chaque femme avec toutes ses œuvres. Le nom d’une écrivaine apparait donc autant de fois qu’elle a écrit d’œuvres. Pour supprimer les doublons, il suffit d’utiliser DISTINCT :

SELECT DISTINCT "Personne"."nom" FROM "EcritOeuvre" JOIN "Personne" ON "Personne"."ID" = "EcritOeuvre"."personne_id" WHERE "Personne"."sexe" = 'F'

Toutes les femmes qui ne sont pas écrivaines

Pour afficher les femmes qui n’ont pas écrit d’œuvres, on peut commencer par écrire une requête pour afficher toutes les femmes, qu’elles aient écrit une œuvre ou non.

Pour cela, faut faire une jointure par la droite avec RIGHT JOIN. C’est à dire que l’on veut toutes les lignes de la table située à droite de JOIN, c’est à dire Personne, même s’il n’existe pas de correspondance à cette table dans EcritOeuvre (autrement dit, même s’il n’existe aucune référence à la personne dans EcritOeuvre). Afin de nous aider à y voir plus clair dans le résultat obtenu, on affiche le nom de la personne et l’identifiant de l’œuvre.

SELECT "Personne"."nom", "EcritOeuvre"."oeuvre_id" FROM "EcritOeuvre" RIGHT JOIN "Personne" ON "Personne"."ID" = "EcritOeuvre"."personne_id" WHERE "Personne"."sexe" = 'F' ORDER BY "Personne"."nom"

La case est vide pour les femmes n’ayant pas écrit d’œuvres :

Il suffit donc de rajouter le critère AND "EcritOeuvre"."oeuvre_id" IS NULL à la précédente requête (EcritOeuvre"."oeuvre_id dans le SELECT est optionnel) :

SELECT "Personne"."nom", "EcritOeuvre"."oeuvre_id" FROM "EcritOeuvre" RIGHT JOIN "Personne" ON "Personne"."ID" = "EcritOeuvre"."personne_id" WHERE "Personne"."sexe" = 'F' AND "EcritOeuvre"."oeuvre_id" IS NULL ORDER BY "Personne"."nom"

Pour information, on aurait aussi pu écrire une requête sur Personne et faire une jointure par la gauche (LEFT JOIN) sur EcritOeuvre, c’est à dire en récupérant toutes les lignes de la table située à gauche de JOIN, c’est à dire Personne, même s’il n’existe pas de correspondance à cette table dans EcritOeuvre:

SELECT "Personne"."nom", "EcritOeuvre"."oeuvre_id" FROM "Personne" LEFT JOIN "EcritOeuvre" ON "Personne"."ID" = "EcritOeuvre"."personne_id" WHERE "Personne"."sexe" = 'F' AND "EcritOeuvre"."oeuvre_id" IS NULL ORDER BY "Personne"."nom"

Pour en savoir plus

Nous avons abordé les possibilités qu’offrent les bases de données relationnelles et le langage SQL.

Pour en savoir plus sur la conception de bases de données avec LibreOffice Base, consultez le guide officiel Débuter avec Base (traduction française, pour la version 6.0 de LibreOffice) et le LibreOffice Base Handbook (en anglais, pour la version 4.0 de LibreOffice mais qui doit être valable pour les version ultérieures). Vous trouverez des guides pour les versions antérieures de LibreOffice sur le wiki de la Document Foundation.

Pour aller plus loin avec le langage SQL, je vous conseille la lecture de Administrez vos bases de données avec MySQL de Chantal Gribaumont qui peut être lu et téléchargé au format epub sur Zeste de Savoirs ou acheté en version imprimée.

Dans une approche plus technique, il existe également Not Only SQL de Vincent Lozano et Étienne Georges (Licence Art Libre) paru chez Framabook.

1. Nelly Blanchard, Jean-Baptiste Pressac et Mannaig Thomas, « PRELIB, base de données sur les acteurs du domaine littéraire en langue bretonne », Lengas [En ligne], 84 | 2018, mis en ligne le 15 juillet 2018, consulté le 27 mars 2020. URL : http://journals.openedition.org/lengas/1754 ; DOI : https://doi.org/10.4000/lengas.1754 [↩]
2. https://www.univ-brest.fr/crbc/ [↩]

---

---