Chapitre 6 - Le modèle relationnel

Toutes les applications web modernes exploitent des bases de données qui assurent un stockage pérenne et des accès rapides aux données.

La structuration d'une base de données est fondamentale dans la conception d'une application web. Une mauvaise structuration rendra l'élaboration des requêtes d'accès aux données plus complexe, et sera donc potentiellement source d'erreurs. Cela sera aussi la source de problèmes de performance pour accéder aux données.

Ce chapitre introduit les concepts de la structuration des bases de données relationnelles en précisant les enjeux et la finalité.

Structuration des données

Les applications web suivent toutes le même processus : récupérer des données brutes, les structurer afin qu'elles soient exploitables, et enfin proposer de la connaissance sur celles-ci.

Les données brutes sont fournies par les utilisateurs lorsqu'ils saisissent des formulaires. Prenons notre exemple d'application qui permet de gérer des images. Lorsqu'un utilisateur veut ajouter une image, l'application lui demande de saisir le nom de l'image, son orientation (portrait ou paysage) et la date à laquelle elle a été créée. L'utilisateur saisit ces données mais elles n'ont pas encore beaucoup de sens car elles sont à l'état brut.

Dès lors que des données brutes ont été saisies, l'application doit les structurer afin qu'elles soient exploitables. L'objectif de la structuration est de valider le format des données et de les mettre en cohérence. Avec notre exemple, l'application va valider que l'orientation est soit 'portrait' soit 'paysage', que la date a été bien saisie, puis regrouper toutes les données pour former une description complète et cohérente d'une image.

Une fois validées et structurées, les données deviennent exploitables ce qui permet d'obtenir de la connaissance. Avec notre application de gestion des images, dès que plusieurs images auront été stockées dans la base de données, il sera alors possible de chercher certaines d'entre elles, par exemple, celles qui sont orientées en mode 'portrait' et qui ont été créées avant 1900.

La mise en place d'une base de données nécessite de définir une structuration qualitative des données. Par qualitative, on entend que les connaissances puissent être exprimées facilement, calculées rapidement et que les données nécessaires à leur établissement prennent le moins de place possible.

S'il existe plusieurs façons de structurer les données (liste, tableau, arbre, graphe, etc.), les bases de données relationnelles ont pris le parti de n'utiliser que des tableaux avec des valeurs élémentaires (booléen, nombre, chaîne de caractères, etc.). On appelle ces tableaux les tables de la base de données. Ce choix est motivé par la simplicité de la structure et la facilité de manipulation des données. En effet, dès lors qu'on a défini la structure des tables, on peut facilement et rapidement accéder aux données.

Les tables

La structuration d'une base de données relationnelle passe par la définition des tables contenues dans la base. Une table représente un concept cohérent de l'application, elle a un nom qui correspond à ce concept. Par exemple, dans le cas d'une application de gestion d'images, on peut définir la table des images, la table des auteurs, etc.

Une table contient un ensemble fini de colonnes. Chaque colonne a un nom et est typée par un type de base (booléen, nombre, chaîne de caractères dont la taille est bornée, date, etc.). Il est important de noter qu'il n'est pas possible de mettre une valeur complexe dans une colonne (pas de liste, de tableau, d'arbre, etc.). Les colonnes des tables représentent les attributs du concept représenté par la table. Par exemple, dans la table des auteurs, on peut définir les colonnes nom et prénom, toutes les deux ayant un type chaine de caractères dont la taille ne dépasse pas 30 caractères (le choix de 30 est ici arbitraire, il nous parait suffisant pour la saisie de tous les noms et tous les prénoms possibles). Ces deux colonnes (nom, prénom) définissent ce qu'est un auteur.

Une ligne d'une table représente une entité unique. Par exemple, dans la table des auteurs, chaque ligne représente un auteur. Il n'est pas possible d'avoir deux lignes identiques car cela voudrait dire qu'on stocke deux fois la même entité.

Enfin, pour bien différencier chaque entité, une table doit définir une ou plusieurs colonnes qui servent de clé pour distinguer les entités les unes des autres. La ou les colonnes qui servent de clé sont appelées clé primaire de la table. Si on ne trouve pas de clé dans les colonnes existantes, une façon simple consiste à ajouter une nouvelle colonne qui servira de clé (colonne souvent nommée id).

La table des auteurs de notre application exemple est présentée par le Tableau 6.1. On peut voir que cette table est composée de trois colonnes dont une est la clé primaire (la colonne id). On a en effet ajouté cette colonne car ni le prénom ni le nom ne peuvent servir de clé dans cette table (certaines personnes ont le même prénom, d'autres le même nom et il y a même des homonymes). Dans l'exemple, trois entités sont enregistrées dans la table. Chacune de ses entités a un id différent.

Tableau 6.1 : La table auteur avec trois entités.

Les associations entre les tables

Les concepts manipulés par une application web sont souvent associés entre eux. Dans notre exemple d'application de gestion d'images, chaque image est associée à un auteur et chaque auteur est associé à plusieurs images.

La structuration d'une base de données doit représenter ces associations. Pour cela, on utilise des clés étrangères qui définissent des liens entre deux tables.

La définition des clés étrangères est une opération importante de la structuration des bases de données. Le point le plus important et le plus complexe est de savoir où placer les clés étrangères (dans quelles tables ? ). Pour ce faire il vaut mieux considérer la multiplicité d'une association plutôt que sa signification. Il existe notamment deux multiplictés à considérer : la multiplicité 1 vers Plusieurs (noté 1-*) et la multiplicité Plusieurs vers Plusieurs (noté *-*).

Une association de multiplicité 1-* entre une table A et une table B (noté A 1-* B) est une association où une entité de la table A est associée à plusieurs entités de la table B, mais où une entité de la table B ne peut être associée qu'à une seule entité de la table A. Dans notre exemple d'application de gestion d'images, un auteur peut être associé à plusieurs images mais une image ne peut être associée qu'à un seul auteur. On a donc l'association auteurs 1-* Images.

Dans le cas d'une association 1-*, la clé étrangère est placée dans la table qui contient les entités référencées plusieurs fois. Pour notre exemple, la clé étrangère est donc placée dans la table des images. Grâce à la clé étrangère, chaque entité de la table des images référence une entité de la table des auteurs. Il est donc possible que plusieurs entités de la table des images référencent la même entité de la table des auteurs (un auteur peut donc être associé à plusieurs images). Le Tableau 6.2 présente la table des images et illustre l'association entre un auteur et plusieurs images. La colonne auteur de la table des images est une clé étrangère qui référence la clé primaire "id" de la table des auteurs. Grâce à cette clé étrangère, on peut retrouver l'auteur associé à une image. Par exemple, on peut voir que "Robert Doisneau" est l'auteur de l'image nommée "Le baiser de l'hôtel de ville".

Tableau 6.2 : La table Images avec quatre entités.

Une association de multiplicité *-* entre une table A et une table B (noté A *-* B) est une association où une entité de la table A est associée à plusieurs entités de la table B et où une entité de la table B est associée à plusieurs entités de la table A. Dans notre exemple, un album d'images peut être associé à plusieurs images et une image peut être associée à plusieurs albums d'images (on parle ici d'images virtuelles). On a donc l'association Albums-Image *-* Images.

Pour définir une association dans une base de données *-*, il faut ajouter une nouvelle table qui représente l'association entre les deux tables associées, puis placer deux clés étrangères dans cette nouvelle table (une vers chacune des deux tables associées). Pour notre exemple, la table des albums d'images est présentée par le Tableau 6.3 . Elle contient deux albums d'images (Incontournables et Noir et Blanc).

Tableau 6.3 : La table des albums d'images avec deux entités.

La table qui représente l'association entre les albums d'images et les images est représentée par le Tableau 6.4. Cette table contient deux colonnes qui sont deux clés étrangères (une vers Albums-Image et l'autre vers Images). Les entités de cette table expriment les associations entre les albums d'images et les images. On voit que l'album d'images Incontournables est associé aux images 2 et 4. L'album d'images Noir et Blanc est, quant à lui, associé aux images 1, 2 et 3.

Tableau 6.4 : La table qui représente l'association "*-*" entre les albums d'images et les images.

Optimisation

La structuration d'une base de données ne s'arrête pas à la définition des tables et de leurs associations. Il faut également optimiser la base de données pour qu'elle soit la plus efficace possible. Un des points importants est de réduire autant que possible la redondance des données. On dit qu'une donnée est redondante si elle est répliquée plusieurs fois dans la base de données. Cela pose alors des problèmes d'efficacité (en place et en accès) mais aussi des problèmes d'intégrité car il faut s'assurer que les données redondantes sont cohérentes notamment lors de leurs modifications.

Reprenons l'exemple de la table des images mais en y ajoutant la colonne orientation dont le type est chaîne de caractères dont la taille ne dépasse pas 10 caractères (voir Tableau 6.5). On voit que les données relatives à l'orientation sont redondantes car on répète les informations sur chacune des entités. La structure de la base de données ne peut garantir ici l'intégrité des données. En effet, rien n'interdit de mettre des valeurs non valides dans la colonne orientation (n'importe quelle chaîne de caractères). Cela peut donc poser des problèmes d'intégrité.

Tableau 6.5 : La table qui des images avec l'orientation.

On peut optimiser cette base de données en créant une nouvelle table (la table des orientations) qui contient toutes les orientations possibles (voir Tableau 6.6 ).

Tableau 6.6 : La table des orientations.

Grâce à cette table on peut mieux préciser les orientations des images dans la Table des images. Plus précisément, il s'agit d'une association Orientations 1-* Images. On peut alors mettre en place cette association en considérant que la colonne orientation de la Table Images est maintenant une clé étrangère vers la clé primaire de la table Orientation (voir Tableau 6.7). Cette optimisation fait gagner de la place mais surtout elle garantie le fait que l'orientation ne peut être que portrait ou paysage tel que cela est défini dans la table Orientation.

Tableau 6.7 : La table des images avec l'optimisation pour ajouter les orientations.

Formalisation

Les principes de structuration des bases de données sont définis de manière formelle avec une base théorique issue des mathématiques. Nous présentons dans cette section des définitions plus rigoureuses des concepts que nous venons de présenter dans les sections précédentes.

D'un point de vue formel, une base de données stocke les données sous forme de relations. Le terme relation est utilisé ici dans son sens mathématique : étant donné des domaines D₁, D₂, ..., D_n, R est une relation sur ces n domaines si elle est un ensemble fini de nuplets appartenant au produit cartésien des domaines (D₁ X D₂ ... X D_n). Une relation est donc un sous ensemble fini du produit cartésien des domaines.

Par exemple, les données contenues dans la table des images sont définies formellement par le produit cartésien : (id X nom X date). Un élément de la relation est le nuplet (1, 'Charles Baudelaire', 1863).

Les relations sont des concepts abstraits. Pour les présenter facilement on leur donne un nom et on représente chacun de leurs domaines par un nom et par un type : c'est ce qu'on appelle un schéma relationnel.

Le schéma relationnel auteur(id, prenom(char(30)), nom(char(30))) représente la structuration de relation auteurs. Il définit trois attributs dont id, prenom et nom.

Le concept de schéma relationnel permet de définir formellement le concept de clé primaire qui correspond à un ensemble d'attributs permettant d'identifier de manière unique chaque nuplet de la relation. Dit autrement, la connaissance de la clé permet d'identifier le nuplet correspondant.

En prenant comme exemple la relation des auteurs, on voit que ni l'attribut prenom ni l'attribut nom ne respectent la contrainte d'unicité. En effet, il est tout à fait envisageable d'avoir deux auteurs avec le même nom et/ou le même prénom. On pourrait alors considérer que les attributs id et prenom forment une clé primaire mais cela ne respecterait pas la contrainte de minimalité car on pourrait retirer l'attribut $prenom$ sans violer la contrainte d'unicité. Avec la relation des images, seul l'attribut $id$ peut être clé primaire.

Le concept de clé étrangère est lui aussi défini formellement grâce au concept de schéma relationnel. Il permet de créer un lien entre deux relations.

En prenant comme exemple la relation des images, on voit que l'attribut orientation est une clé étrangère vers la relation des orientations. De même, l'attribut auteur est une clé étrangère vers la relation des auteurs. Il faut souligner que le type de l'attribut orientation est char(30) (de même pour le type de l'attribut auteur). Pour autant, les valeurs possibles sont contraintes par l'existence d'une clé primaire dans la relation des orientations (de même pour les auteurs).

Les définitions que nous venons de présenter permettent de définir formellement les concepts fondamentaux des bases de données relationnelles. Nous allons définir les propriétés des dépendances fonctionnelles qui permettent de mieux comprendre les objectifs d'optimisation des bases de données.

Dans la relation des auteurs, on a id -> nom car on connait le nom d'un auteur en connaissant son identifiant. Par contre, on a la dépendance fonctionnelle suivante nom -> prenom qui est fausse car on ne connait pas le prénom d'un auteur en connaissant son nom. Il faut noter que les dépendances fonctionnelles sont transitives (Si X -> Y et si Y -> Z alors X -> Z).

D'un point de vue formel, l'optimisation d'une base de données vise à respecter des contraintes précisées dans ce qu'on appelle des formes normales. Nous nous intéressons ici au trois premières formes normales des bases de données. Celles-ci posent formellement les bases d'une structuration qualitative des données.

Les relations que nous avons présentées pour notre exemple d'application respectent la troisième forme normale. Elles sont bien formées dans le sens où toutes les valeurs sont élémentaires, toutes les relations possèdent au moins une clé primaire, et tous les attributs sont en dépendance fonctionnelle avec une clé primaire.

SQL

La structure d'une base de données relationnelle peut être décrite en utilisant le langage SQL (Structured Query Language, en français langage de requête structurée). SQL est un langage standard mais chaque système de gestion de bases de données (SGBD) peut avoir des variations qui lui sont propres. Nous allons nous intéresser ici à la syntaxe de Postgres qui est un SGBD libre et gratuit.

Avant de créer les tables, il faut commencer par construire une base de données. Il faut noter qu'un système de gestion de bases de données permet de créer plusieurs bases de données. Chacune est identifiée par un nom. Le Code 6.1 présente la commande SQL pour créer la base de données nommée application_image.

CREATE DATABASE application_image;

Code 6.1 : Création d'une base de données en SQL.

Une fois que la base est créée il est possible de créer les tables. La création d'une table se fait en donnant le nom de la table, les attributs et leur type. Les types SQL sont CHAR pour une chaîne de caractères de longueur fixe, VARCHAR pour une chaîne de caractères de longueur bornée, TEXT pour une chaîne de caractères, DEC pour un décimal, INT pour un entier, REAL pour un réel à virgule flottante dont la représentation est binaire, BOOLEAN pour vrai ou faux et DATE pour une date.

Le Code 6.2 présente la commande SQL pour de créer la table auteurs. On voit que les attributs prenom et nom sont de type VARCHAR(100) et que l'attribut prenom ne peut pas être vide.

CREATE TABLE auteurs (
    prenom VARCHAR(100) NOT NULL,
    nom VARCHAR(100)
);

Code 6.2 : Création d'une table en SQL.

Postgres propose aussi le type SERIAL qui permet de créer un attribut entier qui est automatiquement incrémenté à chaque insertion d'une nouvelle ligne dans la table. Ce type est très pratique pour créer des clés primaires. Le Code 6.3 présente la commande SQL permettant de créer la table auteurs avec une clé primaire de type SERIAL.

CREATE TABLE auteurs (
    id SERIAL PRIMARY KEY,
    prenom VARCHAR(100) NOT NULL,
    nom VARCHAR(100)
);

Code 6.3 : Création d'une table en SQL avec le type SERIAL.

La création d'une table avec une clé étrangère doit se faire après la création de la table ciblée par la clé étrangère. Il faut préciser dans la commande SQL que l'attribut est une clé étrangère et qu'il référence une clé d'une autre table. Le Code 6.4 présente la commande SQL permettant de créer la table images avec une clé primaire et une clé étrangère vers la table auteurs (attribut id).

CREATE TABLE images (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(100) NOT NULL,
    annee DATE,
    orientation VARCHAR(10),
    auteur_id int references auteurs(id)
);

Code 6.4 : Création d'une table en SQL avec une clé étrangère.

Dès lors que les tables sont construites, il est alors possible d'insérer des lignes dans la base de données. Le Code 6.5 présente les insertions de trois auteurs et trois images.

INSERT INTO auteurs (prenom, nom) VALUES ('Etienne', 'Carjat');
INSERT INTO auteurs (prenom, nom) VALUES ('Robert', 'Doisneau');
INSERT INTO auteurs (prenom, nom) VALUES ('Yann', 'Arthus-Bertrand');
INSERT INTO images (nom, annee, orientation, auteur) VALUES ('Charles Baudelaire', '1863-01-01', 'portrait', 1);
INSERT INTO images (nom, annee, orientation, auteur) VALUES ('Le baiser de l hotel de ville', '1950-01-01', 'paysage', 2);
INSERT INTO images (nom, annee, orientation, auteur) VALUES ('Les freres', '1936-01-01', 'portrait', 2);
INSERT INTO images (nom, annee, orientation, auteur) VALUES ('Coeur de Voh', '1992-01-01', 'paysage', 3);

Code 6.5 : Insertion en SQL d'une entité dans une table .

Pour modifier une entité présente dans une table, il faut utiliser la commande SQL UPDATE en précisant le nom de la table, les nouvelles valeurs des colonnes (SET) et un filtre pour sélectionner les entités à modifier (WHERE). Par exemple, le Code 6.6 est une commande SQL qui modifie l'année de la 4ème image avec '1950-02-03' comme nouvelle valeur.

UPDATE images SET annee = '1950-02-03' WHERE id=4;

Code 6.6 : Modification en SQL d'une entité dans une table .

Enfin, pour supprimer une entité il faut utiliser la commande SQL DELETE. Les commandes DROP TABLE et DROP DATABASE permettent quant à elles de supprimer entièrement une table ou même une base de données. Par exemple, le Code 6.7 présente plusieurs commandes qui suppriment une entité, deux tables puis la base de données.

DELETE FROM images WHERE id=4;
DROP TABLE images;
DROP TABLE auteurs;
DROP DATABASE application_image;

Code 6.7 : Suppression en SQL d'une entité, d'une table et d'une base.

Pour s'exercer

Questions de cours

Exercice 1 - Compétition sportive

Projet - mur d'images