Le modèle relationnel

Voici une table qui stocke des données :

Id Nom Prenom Adresse Age

1

Durand

Pierre

Angers

36

2

Durand

Paul

Marseille

52

3

Dugenou

Albert

Angers

25

4

Dupond

Albert

Paris

49

5

Dupont

Albert

Paris

49

Cette table (ou relation) a un schéma, déterminé par les différents attributs (Id, Nom, Prenom, Adresse, Age) et par les ensembles (domaines des attributs) dans lesquels peuvent varier les valeurs des attributs. Ici les domaines sont int pour deux attributs et str pour les trois autres.

Le schéma de la table peut s’écrire

(Id:int, Nom:str, Prenom:str, Adresse:str, Age:int)

Sur cette table, on peut faire des interrogations, par exemple : "Trouver toutes les personnes habitant à Angers et ayant moins de 30 ans".

Une base de donnée relationnelles est un ensemble de tables. Par exemple :

Clients : (Id_client:int, Nom:str, Prenom:str, Adresse:str, Date_naiss:date)
Commandes : (Num_commande:int, Id_client:int, Date:date, Id_produit:int, Quantite:int)
Produits : (Id_produit:int, Designation:str, Prix:float)

Systèmes de gestion de base de données relationnelles (SGBDR)

Un SGBDR est chargé de stocker les différentes tables qui constituent une base de données, de répondre à des requètes sur ces données et d’assurer la cohérence et l’intégrité des bases de données. Parmi les SGBD libres, on peut citer

  • SQLite : Contrairement aux serveurs de bases de données traditionnels, comme MySQL ou PostgreSQL, sa particularité est de ne pas reproduire le schéma habituel client-serveur mais d’être directement intégrée aux programmes. L’intégralité de la base de données (déclarations, tables, index et données) est stockée dans un fichier indépendant de la plateforme.

    SQLite est le moteur de base de données le plus utilisé au monde, grâce à son utilisation dans de nombreux logiciels grand public comme Firefox, Skype, Google …​

  • MySQL / MariaDB : MySQL est un SGBDR en mode client/serveur. C’est l’un des plus utilisés au monde, autant par le grand public (applications web principalement) que par des professionnels. Son nom vient du prénom de la fille du cocréateur Michael Widenius,My, et du langage de requêteSQL.

    MySQL AB a été acheté le 16 janvier 2008 par Sun Microsystems. En 2009, Sun Microsystems a été acquis par Oracle Corporation, mettant entre les mains d’une même société les deux produits concurrents que sont Oracle Database et MySQL. Depuis mai 2009, Michael Widenius a créé MariaDB (Maria est le prénom de sa deuxième fille) pour continuer son développement en tant que projet Open Source, entièrement dégagé des contraintes dues à Oracle.

  • PostgreSQL : C’est le successeur de la base de données Ingres, développée à Berkeley par Michael Stonebraker. Lorsque ce dernier décida en 1985 de recommencer le développement de zéro, il nomma le logiciel Postgres, et cela devint PostgreSQL en 1996.

    PostgreSQL est reconnu pour sa stabilité, ses possibilités de programmation étendues permettant d’utiliser plusieurs langages et sa conformité aux standards SQL.

Il existe aussi des SGBDR propriétaires : Oracle, Microsoft SQL Server, etc.

Dans ce cours, nous utiliserons SQLite et PostgreSQL.

Interaction avec les SGBD

Les logiciels d’interaction

Nous utiliserons :

  • des interfaces en ligne de commande :

    • sqlite3 pour accéder à une base SQLite

    • psql : client en ligne de commande pour PostgreSQL

  • Des interfaces graphiques pour vous connecter à une base de données

    • sqlitebrowser pour SQLite

    • dbeaver outil universel pour toutes les bases de données

  • des modules python permettant d’automatiser des requètes dans un script python :

    • sqlite3 pour SQLite

    • psycopg2 pour PostgreSQL

  • Nous utiliserons aussi des logiciels de modélisation de bases de données :

    • SQL power architect

    • pgmodeler

    • draw.io (juste pour faire des dessins)

Dans l’autre partie du cours, vous étudierez l’ORM Django. Un ORM (Object Relational Mapping) est une abstraction de haut niveau qui permet à un développeur d’écrire dans un langage objet, et de traduire les objets en données dans une base de donnée, de manière relativement indépendante du SGBD. De plus Django fournit un framework permettant d’écrire des applications web adossées à une base de données.

Le langage SQL

Pour interroger une base de données, on dispose du langage SQL, pour "Structured Query Language". Il s’agit d’une écriture, sous forme standardisée et interprétable par tous les SGBDR, d’une requête conçue en langage ordinaire. La programmation en SQL est une programmation déclarative : on décrit ce qu’on veut, et le SGBDR fait ce qu’il faut pour faire ce que vous avez demandé.

Pour une histoire du langage SQL, regarder la page de wikipedia

Une bonne source de documentation sur SQL est le site sql.sh . En particulier, on y trouve un manuel au format pdf

Voici un exemple de commande SQL d’interrogation d’une base de données :

SELECT Clients.Nom
FROM Clients, Commandes
WHERE Clients.Client_id = Commandes.Client_id
    AND Commandes.Id_produit = 1234 ;

  • Une instruction SQL est finie quand on rencontre le caractère ;.

  • Les passages à la ligne et indentations ne sont pas interprétées et servent uniquement à assurer une meilleure lisibilité.

  • De même, le langage est indifférent à la casse, mais il est d’usage de mettre tous les mots clés en majuscule.

  • Suivant le SGBD utilisé, les tables peuvent être stockées dans des fichiers à leur nom. Dans ce cas la casse devient significative pour les noms de tables.

Pour des raisons d’organisation pédagogique, on regroupe les instructions de SQL en plusieurs catégories de fonctions :

  • DDL : Data Definition Language - Les commandes destinées à définir une base de données

    • CREATE

    • ALTER

    • DROP

    • RENAME

    • TRUNCATE

    • COMMENT

  • DML : Data Manipulation Language - la manipulation des données : interrogation d’une base, insertion et modification de données

    • SELECT

    • INSERT

    • UPDATE

    • DELETE

    • MERGE

  • DCL : Data Control Language - gestion des utilisateurs (c’est plutôt du domaine de l’administrateur du système ou du SGBD, pas du data scientist)

    • GRANT

    • REVOKE

  • TCL : Transactional Control Language - gestion des accès concurrents

    • COMMIT

    • ROLLBACK

    • SAVEPOINT

Conception d’une base de données

A partir d’un problème pratique, par exemple la gestion du système de commandes dans une entreprise, il faut définir quelle est la bonne représentation par des tables, de façon à ce que les données rentrées dans la machine ne soient pas redondantes. On cherche à éliminer un certain nombre d’anomalies :

Anomalie de mise à jour :

Que se passe t-il si on modifie un enregistrement ?

Incohérence ou besoin de modification de plusieurs n-uplet!

Anomalie d’insertion :

Que se passe t-il si on insère un nouveau n-uplet ?

Incohérence ou interdiction d’insertion!

Anomalie de suppression :

Que se passe t-il si on supprime un n-uplet ?

Perte d’information éventuelle

Contraintes d’intégrité

Le but d’une base de données est que les données qui y sont stockées soient toujours cohérentes. Cela peut être garanti

  • par les applications qui interagissent avec la base de donnée. Cela impose que ces applications soient correctement programmées ; il faut donc déléguer la responsabilité aux programmeurs de ces applications. C’est délicat et potentiellement source de problèmes !

  • par le SGBD lui même. Le SGBD dispose de mécanismes adéquats pour imposer des contraintes d’intégrité. La gestion de de ces contraintes est une part importante de la bonne conception d’une base de données.

Gestion de la concurrence d’accès

Imaginons une base de donnée qui stocke des transactions banquaires. On regarde un versement de 100 € du compte A vers le compte B. On doit effectuer deux opérations :

  • prélever 100 € sur le compte A

  • verser 100€ sur le compte B

Si l’une seulement de ces deux opérations est réalisée, il y aura quelqu’un de mécontent.

Les serveurs de bases de données ont des mécanismes, appelés transactions, pour assurer que ces deux opérations soient toujours, soit effectuées toutes les deux, soit ni l’une ni l’autre.

C’est la problématique de la concurrence d’accès à une base de données.

Procédures stockées et déclencheurs

Beaucoup de contraintes assurant la cohérence des données doivent être assurées par des programmes qui agissent sur les données dès qu’on les crée / modifie / détruit. C’est le rôle des déclencheurs (trigger) de lancer ces opérations.

Les opérations en questions correspondent à des programmes, exécutés directement par le SGBD. De tels programmes sont appelés des procédures stockées. PostgresQL propose plusieurs langages de programmation pour ces procédures, le langage propre à PostgreSQL PL/pgSQL, ou des langages externes comme python.