Bases de données relationnelles

Introduction

Stocker et manipuler l'information est un problème fondamental en informatique. Lorsque les données se multiplient, il devient important d'en organiser le stockage et l'accès. On a pour cela recourt à des Systèmes de Gestion de Base de Données (SGBD). Il s'agit d'un logiciel qui permet de créer et de gérer une base de données. Les rôles d'un SGBD sont multiples :

• les SGBD permettent de gérer la lecture, l'écriture ou la modification des informations contenues dans une base de données
• les SGBD permettent de gérer les autorisations d'accès à une base de données. Il est en effet souvent nécessaire de contrôler les accès par exemple en permettant à l'utilisateur A de lire et d'écrire dans la base de données alors que l'utilisateur B aura uniquement la possibilité de lire les informations contenues dans cette même base de données.
• les fichiers des bases de données sont stockés sur des disques durs dans des ordinateurs, ces ordinateurs peuvent subir des pannes. Il est souvent nécessaire que l'accès aux informations contenues dans une base de données soit maintenu, même en cas de panne matérielle. Les bases de données sont donc dupliquées sur plusieurs ordinateurs afin qu'en cas de panne d'un ordinateur A, un ordinateur B contenant une copie de la base de données présente dans A, puisse prendre le relais. Tout cela est très complexe à gérer, en effet toute modification de la base de données présente sur l'ordinateur A doit entrainer la même modification de la base de données présente sur l'ordinateur B. Cette synchronisation entre A et B doit se faire le plus rapidement possible, il est fondamental d'avoir des copies parfaitement identiques en permanence. Ce sont aussi les SGBD qui assurent la maintenance des différentes copies de la base de données.
• plusieurs personnes peuvent avoir besoin d'accéder aux informations contenues dans une base données en même temps. Cela peut parfois poser problème, notamment si les 2 personnes désirent modifier la même donnée au même moment (on parle d'accès concurrent). Ces problèmes d'accès concurrent sont aussi gérés par les SGBD.

Nous nous intéresserons cette année au cas où les données sont organisées en tables, on parle alors de base de données relationnelles.

Modèle relationnel

Schéma relationnel et clé primaire

La bibliothèque nationale de France comporte plus de 16 000 000 d'ouvrages. On peut représenter la collection de ces ouvrages par la relation "Livre" :

Vocabulaire.

• relation (ou table) :: là où sont stockées les données. On peut les représenter à l'aide d'un tableau bidimensionnel.
• enregistrement (ou entité, ou tuple, ou n-uplet, ou vecteur) :: correspond à une ligne du tableau. L'enregistrement ('Le monde de rocannon', 'Le Guin', 1966) de la relation Livre signifie que Le Guin a publié son livre Le monde de rocannon en 1966. Dans le modèle relationnel, on interdit que deux enregistrements soient complètement identiques.
• cardinal d'une relation :: nombre d'enregistrement d'une relation.
• attribut d'une relation :: c'est l'équivalent d'une colonne. Il y a dans la relation un attribut auteur, un attribut titre, etc.

1. Donner l'enregistrement dont l'attribut titre a pour valeur "Dune".

   Il s'agit de l'enregistrement ('Dune', 'Herbert', 1965). 2. Donner les enregistrements associés aux livres écrits par Asimov.

   Il s'agit des enregistrements ('Fondation', 'Asimov', 1951) et ('Les robots', 'Asimov', 1950).

2. Quelles sont les valeurs prises par l'attribut auteur de la table Livre ?

   L'attribut "auteur" peut prendre pour valeurs "Orwell", "Herbert", "Asimov", "Huxley", "Bradbury", "Le Guin", "Verne", "Vance".

3. Quelles valeurs peuvent prendre les attributs annee​_​publi des enregistrements la table Livre ?

   Les valeurs prises sont des entiers.

Vocabulaire.

• domaine d'un attribut :: il s'agit du type d'un attribut. Par exemple, l'attribut titre est une chaîne de caractères, son domaine est donc String.

• schéma d'une relation :: il s'agit de la donnée de tous les attributs de la relation ainsi que des domaines correspondants. Le schéma de la relation "Livre" est noté :

  Livre(titre String, auteur String, annee​_​publi Int)

1. Donner le domaine des attributs auteur et annee​_​publi de la relation Livre.

   L'attribut "auteur" a pour domaine String, l'attribut "annee​_​publi" a pour domaine Int.

2. La bibliothèque doit gérer les profils de ses usagers. Ceux-ci sont identifiés par leur nom, leur prénom, ainsi qu'un identifiant unique (un entier) qui leur est attribué lors de leur inscription à la bibliothèque. Proposer le schéma d'une relation "Usager" qui permet de modéliser les membres de la bibliothèque.

   Usager(nom String, prenom String, id Int)

Vocabulaire.

• clé primaire d'une relation :: un (ou plusieurs) attribut(s) dont la connaissance suffit à identifier avec certitude un unique enregistrement. Pour indiquer qu'un attribut joue le rôle de clé primaire, on le souligne dans le schéma de la relation.

• L'attribut annee​_​publi ne peut pas servir de clé primaire dans la relation Livre car les deux livres Kugel l'astucieux et Le monde de rocannon ont tous les deux été publiés en 1966.
• La clé primaire de la relation des personnes nées en France peut par exemple être leur numéro de sécurité sociale (tout le monde en a un, et il est unique).

1. L'attribut auteur peut-il servir de clé primaire dans la relation Livre ? Justifier.

   Non, car il est possible qu'un auteur ait écrit plusieurs livres.

2. L'attribut titre peut-il servir de clé primaire dans la relation Livre ? Justifier.

   Non, car il est possible que deux livres partagent le même titre (rare, mais possible !).

3. Donner en justifiant un couple d'attribut pouvant être utilisé comme clé primaire de la relation Livre.

   Le couple "titre" et "auteur" de chaque enregistrement doit alors être unique. Cela est raisonnable si on considère qu'aucun auteur n'a écrit deux livres distincts avec le même titre. Cependant, il est impossible de distinguer différentes éditions d'un même livre.

4. Donner une clé primaire pour la relation Usager. Justifier votre réponse.

   "id" peut être une clé primaire pour la relation "Usager" car il est dit dans l'énoncé que chaque membre reçoit un identifiant unique lors de son inscription.

5. L'ISBN (International Standard Book Number) est un numéro unique qui permet d'identifiant de manière unique chaque édition de chaque livre publié. On modifie la relation Livre de telle sorte que l'attribut isbn soit la clé primaire de cette relation.

   Donner le schéma de la nouvelle relation Livre.

   Livre(titre String, auteur String, annee_publi Int, isbn Int)

Clés étrangères

On donne ci-dessous un extrait de la relation Usager.

On souhaite modéliser dans notre base de données l'information de l'emprunt d'un livre par un membre de la bibliothèque. Par exemple, on souhaite inscrire dans l'historique des emprunts que Djihane a emprunté 1984 le 15 septembre et l'a rendu le 22 septembre.

On propose de stocker cette information dans une table Emprunt, dont on donne un exemple ci-dessous.

1. Expliquer pourquoi c'est une mauvaise idée de stocker l'information ainsi.

   On stocke l'information de manière redondante : à chaque fois qu'un usager emprunte un livre on recopie toutes les informations connues sur l'usager, ce qui est inutile. À la fois on utilise trop d'espace mémoire, et en plus on complexifie les opérations de maintenance sur la base de donnée. Par exemple, pour modifier le nom de famille d'un usager, il faut modifier tous les enregistrement de toutes les tables concernant cet usager : ça n'est pas raisonnable.

2. Proposer un schéma pour la table Emprunt qui résolve le problème de la question précédente.

   Il n'est pas nécessaire de stocker toutes les informations d'un usager dans la table emprunt : il suffit de stocker son identifiant, qui servira à le retrouver dans la table Usager. De même, on identifie un livre à l'aide de son isbn. On peut donc supprimer de la table Emprunt les colonnes titre, auteur, annee_publi, nom, prenom.

Vocabulaire.

• clé étrangère :: un attribut qui fait référence à une clé primaire d'une autre relation. Pour signifier qu'un attribut d'une relation est une clé étrangère, on le fait précéder d'un caractère # dans le schéma de la relation.

Donner la ou les clés étrangères de la relation Emprunt. À quelles clés primaires font-elles référence ?

Il s'agit des attributs isbn faisant référece à l'attribut isbn de la table Livre et de l'attribut id faisant référence à l'attribut id de la table Usager.

Contraintes d'intégrité

Une contrainte d'intégrité est une propriété logique, préservée à tout instant par la base de donnée qui garantit la cohérence des données.

• contrainte de domaine: tout attribut d'un enregistrement doit respecter le domaine indiqué dans le schéma relationnel.
• contrainte d'entité: chaque élément d'une relation est unique et identifie une entité de manière non ambigüe. Cette contrainte est garantie par l'exitence obligatoire d'une clé primaire.
• contrainte de référence: la cohérence entre les différentes tables d'une base de donnée est assurée par les clés étrangères : dans une table, la valeur d'un attribut qui est une clé étrangère doit obligatoirement pouvoir être retrouvée dans la table dont cet attribut est clé primaire.
• contrainte utilisateur: ce sont les contraintes que l'on ne peut exprimer à l'aide des contraintes précédentes. Par exemple la contrainte qu'une date soit une chaine de caractère exprimée au format "JJ/MM/AAAA" et corresponde à une véritable date du calendrier.

Synthèse

Ce qu'il faut retenir :

• Une relation est un ensemble d'entités modélisées sous forme de tuples. Le schéma d'une relation est une description de chacune de ses composantes (ses attributs) : son nom et son domaine.
• Une clé primaire d'une relation est un ensemble d'attributs permettant d'identifier de manière unique une entité dans la relation. On la souligne dans le schéma de la relation.
• Une clé étrangère est une clé primaire d'une autre relation. On la fait précéder d'un symbole # dans le schéma de la relation.
• Une base de donnée est un ensemble de relations. Le schéma d'une base de donnée est l'ensemble des schémas des relations qui la composent.
• Un SGBD (Système de Gestion de Base de Donnée) est un logiciel chargé de gérer une base de donnée. C'est lui qui s'assure de maintenir la cohérence des données en vérifiant les différentes contraintes d'intégrité lors des opérations d'ajout, de modification, ou de suppression des éléments de la base de données.

On peut donner le schéma de la base de donnée Bibliothèque de manière textuelle :

• Livre(titre String, auteur String, annee​_​publi Int, isbn Int)
• Usager(nom String, prenom String, id Int)
• Emprunt(#id_​​emprunteur Int, ​​​date​_​emprunt​ String, date​_​retour​ String, #isbn​ String)

On peut également le donner de manière graphique :

Langage SQL et création de bases de données

Le langage SQL se base sur le modèle relationnel et permet d'opérer sur une base de données de manière simple. C'est lui qui est utilisé par les SGBD relationnels. Par exemple, pour créer les tables correspondant à la modélisation de la base de données "Bibliothèque", on peut saisir les ordres suivants.

CREATE TABLE livre (titre VARCHAR(300),
       auteur VARCHAR(90),
       annee_publi INT,
       isbn INT PRIMARY KEY);

CREATE TABLE usager (id INT,
       nom VARCHAR(90),
       prenom VARCHAR(90),
       PRIMARY KEY (id));

CREATE TABLE emprunt (id_emprunteur INT REFERENCES usager(id),
       date_emprunt CHAR(10),
       date_retour CHAR(10),
       isbn INT,
       PRIMARY KEY (date_emprunt, isbn)
       FOREIGN KEY (isbn) REFERENCES livre(isbn));

Création d'une table CREATE TABLE

La création d'une nouvelle relation s'effectue au moyen de l'instruction CREATE TABLE en fournissant son nom et son schéma. L'ordre dans lequel les attributs ont été spécifié est mémorisé et devra être utilisé lors de l'insertion de nouveaux enregistrements. Les types principaux pouvant être utilisés sont :

Contrainte de domaine	Description
INT	entier sur 32 bits signé
DECIMAL(N, M)	décimal signé de N chiffres dont M après la virgule
CHAR(N)	chaîne d'exactement N caractères.
	Les caractères manquants sont complétés par des espaces
VARCHAR(N)	chaîne d'au plus N caractères
TEXT	chaîne de caractère de taille quelconque
DATE	une date au format 'AAAA-MM-JJ'
TIME	une heure au format 'hh:mm:ss'
DATETIME	un instant au format 'AAAA-MM-JJ hh:mm:ss'

La syntaxe générale d'un ordre CREATE TABLE est (attribut_X domaine_X sont obligatoires, contrainte_X?, contrainte_globale_X sont optionnelles):

CREATE TABLE nom_table (attribut_1 domaine_1 contrainte_1?,
       ...,
       attribut_n domaine_n contrainte_n?,
       contrainte_globale_1,
       ...
       contrainte_globale_n);

Contraintes d'intégrité

• Clé primaire.: Les mots clés PRIMARY KEY permettent d'indiquer qu'un attribut est une clé primaire.

  CREATE TABLE usager (id INT PRIMARY KEY,
         nom VARCHAR(90),
         prenom VARCHAR(90));
  

  Si on veut utiliser plusieurs attributs comme clé primaire, alors il faut spécifier la contrainte globalement :

  CREATE TABLE point (x INT,
         y INT,
         couleur VARCHAR(30),
         PRIMARY KEY (x, y));
  

• Clé étrangère.: Un attribut peut être qualifié de clé étrangère en utilisant le mot clé REFERENCES suivit de la table où se trouve la clé primaire ainsi que son nom.

  CREATE TABLE emprunt (id_emprunteur INT REFERENCES usager(id),
         date_emprunt CHAR(10),
         date_retour CHAR(10),
         isbn INT REFERENCES livre(isbn),
         PRIMARY KEY (date_emprunt, isbn));
  

  Il est possible de spécifier les clés étrangères globalement :

  CREATE TABLE emprunt (id_emprunteur INT,
         date_emprunt CHAR(10),
         date_retour CHAR(10),
         isbn INT,
         PRIMARY KEY (date_emprunt, isbn)
         FOREIGN KEY (id_emprunteur) REFERENCES usager(id),
         FOREIGN KEY (isbn) REFERENCES livre(isbn));
  

• Contraintes utilisateur.: Il est possible de spécifier des contraintes arbitraires portant sur les attributs d'une même ligne avec le mot clé CHECK, suivi d'une formule booléenne (que l'on forme avec des attributs de la relation, de valeurs, des prédicats = <> < > <= >= et des connecteurs OR AND NOT).

  CREATE TABLE usager (id INT PRIMARY KEY,
         nom VARCHAR(90),
         prenom VARCHAR(90),
         age INT,
         CHECK (0 <= age) AND (age < 150));
  

• Unicité, non nullité.: Il peut être intéressant de spécifier qu'un groupe d'attributs est unique, sans pour autant en faire une clé primaire. On utilise pour cela le mot clé UNIQUE. Si on veut forcer à renseigner la valeur d'un attribut dans une table, on peut utiliser le mot clé NOT NULL (lors de l'insertion de données, une erreur sera soulevée si la valeur spéciale NULL est utilisée pour cet attribut).

  CREATE TABLE usager (id INT PRIMARY KEY,
         nom VARCHAR(90) NOT NULL,
         prenom VARCHAR(90) NOT NULL,
         pseudo VARCHAR(90) UNIQUE NOT NULL);
  

Destruction d'une table DROP TABLE table

Pour détruire définitivement une relation on utilise l'ordre SQL DROP TABLE :

-- supprimer la table nom_table
DROP TABLE nom_table;

Attention cela n'est pas toujours possible dans le cas où cette suppression entraînerait un non respect des contraintes d'intégrité de la base de données.

Insérer une ligne dans une table : INSERT INTO table VALUES valeurs

L'insertion d'éléments cohérents avec le schéma de la relation dans une table déjà existante se fait à l'aide de l'instruction INSERT INTO. Les attributs sont supposés être donnés dans le même ordre que lors de l'instruction CREATE TABLE.

INSERT INTO usager VALUES (123, 'Boudier', 'Émile'),
       (321, 'Hemmar', 'Djihane'),
       (852, 'Labib', 'Samy');

Il est possible de donner les valeurs des attributs dans un ordre différent, dans ce cas il faut le spécifier.

INSERT INTO usager (prenom, nom, id) VALUES ('Émile', 'Boudier', 123),
       ('Djihane', 'Hemmar', 321),
       ('Samy', 'Labib', 852);

Suppression de lignes d'une table : DELETE FROM table WHERE condition

L'instruction DELETE FROM table WHERE condition permet de supprimer de table toutes les lignes vérifiant la condition.

/* Les instructions ci-dessous suppriment toutes les  deux
   Djihane Hemmar de la table des usagers. */
DELETE FROM usager WHERE id = 321;
DELETE FROM usager WHERE nom = 'Hemmar' AND prenom = 'Djihane';

Le SGBD s’assure de maintenir les contraintes d’intégrité lors de la suppression d’un élément.

Mise à jour : UDPATE table SET attribut = valeur WHERE condition

L'instruction UDPATE table SET attribut = valeur WHERE condition met à jour l'attribut des enregistrements de la table vérifiant la condition : la nouvelle valeur est valeur.

UPDATE usager SET nom = 'Lotenberg' WHERE id = 321;

Le SGBD s’assure de maintenir les contraintes d’intégrité lors de la modification d’un élément.

Sélection : SELECT attribut FROM table WHERE condition

Lorsque l'on souhaite interroger la base de donnée pour obtenir une information, on utilise un ordre SQL commençant par SELECT. Plusieurs syntaxes sont possibles. On peut éliminer les doublons éventuels dans la table renvoyée en utilisant l'ordre SQL SELECT DISTINCT.

• Sélectionner toutes les lignes, toutes les colonnes de la table Usager : on utilise le caractère * :

  SELECT * FROM Usager ;
  

• Sélectionner uniqument certaines colonnes de la table Usager

  SELECT nom, prenom FROM Usager ;
  

• Sélectionner certaines lignes et certaines colonnes de la table Livre

  SELECT titre FROM Livre
  WHERE annee_publi > 1950;
  

• Sélectionner la liste des auteurs (une seule occurrence par auteur) ayant publié un livre avant 1960 :

  SELECT DISTINCT auteur FROM Livre
  WHERE annee_publi < 1960 ;
  

Jointure : JOIN table ON table.etrangere = autre_table.primaire

Lorsque les données sont réparties sur plusieurs tables, on doit réaliser une jointure. On doit alors préciser quel attribut on utilise pour faire correspondre les éléments de la première table aux éléments de la seconde.

Cette ordre s'utilise immédiatement à la suite de l'ordre SELECT ... FROM .... Il est possible de combiner plusieurs jointures entre elles. L'ordre dans lequel on effectue les jointures n'a pas d'importance.

On utilise la syntaxe table.attribut pour éviter toute ambigüité. Si tous les attributs de toutes les tables sont différents, on peut se contenter d'utiliser attribut.

• Pour obtenir les dates auxquelles Jean a emprunté ses livres.

  SELECT date_emprunt FROM Emprunt
  JOIN Usager ON Usager.id = Emprunt.id_emprunteur
  WHERE Usager.prenom = 'Jean';
  

• Pour obtenir les livres (relation Livre) empruntés (relation Emprunt) par Jean (relation Usager).

  SELECT titre FROM Livre
  JOIN Usager ON Usager.id = Emprunt.id_emprunteur
  JOIN Emprunt ON Livre.id = Emprunt.id_livre
  WHERE Usager.prenom = 'Jean';
  

Tri des colonnes : ORDER BY attribut ASC/DESC

Il est possible d'ajouter ORDER BY attribut en fin de requête afin de présenter les données triées. Par défaut le tri se fait dans l'ordre croissant, pour obtenir les informations dans l'ordre décroissante, il faut utiliser l'ordre ORDER BY attribut DESC.

• Pour obtenir la liste des livres par ordre croissant de publication

  SELECT * FROM Livre
  ORDER BY annee_publi; 
  

• Pour obtenir la liste des emprunts de Jean dans l'ordre chronologique du plus récent au plus ancien (ceci est possible car la date est au format 'YYYY-MM-JJ') :

  SELECT * FROM Emprunt
  JOIN Usager ON Usager.id = Emprunt.id_emprunteur
  WHERE Usager.prenom = 'Jean';
  ORDER BY Emprunt.date_retour DESC;
  

Agréger les données d'une table

Il est fréquent que l'on souhaite effectuer des opérations mathématiques sur une colonne. Les fonctions d'agrégation à connaître sont :

• COUNT(*) : compte les lignes de la table
• COUNT(DISTINCT attribut) : compte les lignes distinctes (non nulles) de la colonne attribut
• MIN(attribut), MAX(attribut) : calcule le minimum/maximum de la colonne attribut
• SUM(attribut) : calcule la somme des valeurs de la colonne attribut.

• AVG(attribut) : calcule la moyenne des valeurs de la colonne attribut.

• Obtenir le nombre de livres empruntés le 10 Novembre 2023 :

  SELECT COUNT(*) FROM Emprunt
  WHERE date_emprunt = '2023-11-10';
  

• Pour obtenir la moyenne des durées d'emprunts de Marie (l'instruction JULIANDAY(date_retour) - JULIANDAY(date_emprunt) n'est pas au programme : elle calcule la durée de l'emprunt).

  SELECT AVG(JULIANDAY(date_retour) - JULIANDAY(date_emprunt)) FROM Emprunt
  JOIN Usager ON Usager.id = Emprunt.id_emprunteur
  WHERE Usager.prenom = 'Marie' ;