Bases de données relationnelles

Introduction

Stocker et manipuler l'information est un problème fondamental en informatique. On a déjà vu que l'on pouvait représenter des objets du monde réel et leurs interactions à l'aide de la programmation objet. Cependant, lorsque les données se multiplient, il devient important d'en organiser le stockage. 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.

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: nombre d'enregistrement d'une relation.
• attribut: 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: 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: 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)

La clé primaire d'une relation est 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" ?

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

3. 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.

4. On choisit comme clé primaire le couple des attributs "titre" et "auteur". Expliquer quelle restriction ce choix impose aux enregistrements 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.

5. L'ISBN (International Standard Book Number) est un numéro unique spécifique à chaque livre. On modifie la relation "livre" de telle sorte que l'attribut "isbn" soit la clé primaire de cette relation.

   1. Quelle hypothèse doit-on faire sur les livres de la bibliothèque pour que l'on puisse utiliser l'ISBN comme clé primaire ? On se placera dans ce cas par la suite.

      Pour pouvoir utiliser l'ISBN comme clé primaire, il faut que deux livres distincts ne possèdent pas le même ISBN. Cela est possible si la bibliothèque ne possède qu'un unique exemplaire de chaque livre.

   2. 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".

id	nom	prenom
123	Boudier	Émile
321	Hemmar	Djihane
852	Labib	Samy
\ldots	\ldots	\ldots

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. On utilise pour cela la relation "Emprunt". Un utilisateur emprunte un livre à une date donnée et doit le rendre avant une certaine date. On propose donc le schéma :

Emprunt(id_​​emprunteur Int, date_​emprunt String, date_​retour String, isbn Int)

1. L'attribut "id​_​emprunteur" peut-il être utilisé commé clé primaire dans la relation "Emprunt" ? Justifier votre réponse.

   Non, car cela voudrait dire qu'un identifiant d'utilisateur n'apparaîtrait pas dans deux enregistrements distincts de la relation "Emprunt", autrement dit qu'un membre de la bibliothèque ne pourrait emprunter qu'un seul livre !

2. Donner un (ou des) attribut(s) pouvant être utilisé(s) commé clé primaire, en justifiant votre réponse.

   On pourrait utiliser l'attribut "isbn" comme clé primaire, mais dans ce cas cela voudrait dire qu'un livre ne pourrait être emprunté qu'une seule fois. On choisit donc le couple d'attributs "date_emprunt" et "isbn" comme clé primaire : en effet, aucun livre ne peut être emprunté deux fois le même jour.

Une clé étrangère est 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. Une clé étrangère peut apparaître plusieurs fois dans une relation.

Par exemple, les attributs "id​_​emprunteur" et "isbn" sont tous deux des clés étrangères. Une clé étrangère peut également être une clé primaire.

1. Donner le schéma de la relation "Emprunt", dont les attributs "isbn" et "date​_​emprunt" forment la clé primaire et les attributs "id​_​emprunteur" et "isbn" sont tous deux des clés étrangères.

   Emprunt(#id_​​emprunteur Int, ​​​date​_​emprunt​ String, date​_​retour​ String, #isbn​ String)

2. On donne ci-dessous le contenu de la relation "Emprunt".

   id​​emprunteur	date​​emprunt	date​_​retour	isbn
   123	15/09/2023	22/09/2023	9781328869333
   584	04/10/2023	11/10/2023	9780425054710
   123	05/05/2022	12/05/2022	9782253072485
   852	01/01/2021	08/01/2021	9782253072485
   123	05/09/2021	12/09/2021	9781328869333

   Utiliser le site pour répondre aux questions suivantes.

   1. Combien de livres Émile, Djihane et Samy ont-ils chacun empruntés à la bibliothèque ?

      L'attribut de Djihane, 321, n'apparaît pas dans la colonne "id_emprunteur". Elle n'a donc pas emprunté de livres. L'attribut de Samy, 852 apparait une seule fois, il a donc emprunté un livre. Émile, a son identifiant 123 qui apparaît trois fois dans la relation "Emprunt": il a fait trois emprunts (un livre deux fois et l'autre une fois).

   2. Quels sont les livres empruntés par Émile ?

      Émile a emprunté Dune deux fois, et Le monde de rocannon une fois.

   3. Qui a emprunté Le monde de rocannon, d'isbn 9782253072485 ?

      Il s'agit d'Émile et de Samy.

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

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 relation INSERT

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);

Supprimer une ligne dans une relation DELETE

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

/* 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.

Modifier une ligne dans une relation UPDATE

L'instruction UDPATE t SET a = v WHERE c met à jour l'attribut a des éléments de la table t vérifiant la condition c avec la valeur v.

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 de ligne SELECT

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

  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 ;
  

Regroupement d'information : jointure et instruction JOIN ... ON

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 coinjoitement avec l'ordre SELECT. 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

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' ;