Ch. 4 : Le modèle relationnel

Cours – Partie 2

Définition 1 (Modèle relationnel)

Le modèle relationnel est une manière de représenter les informations sous la forme de tables, appelées relations, reliées entre elles grâce à des clefs.

1 Relation, attributs et domaines

Une relation peut être vue comme un tableau composé d’une en-tête (première ligne) et d’un corps. Chaque ligne de la relation est un p-uplet (ou enregistrement) et chaque colonne est un attribut ; l’en-tête contient les intitulés des attributs.

Remarque. Attention au vocabulaire : en modèle relationnel, une relation désigne la table elle-même, et non un lien entre deux informations. Une relation est un ensemble de p-uplets : l’ordre des lignes n’a donc aucune importance.

La relation ci-dessous regroupe des données sur les établissements scolaires du second degré dans le Cantal (extrait).

code nom statut commune codepostal
0150001A Lycée Émile Duclaux Public AURILLAC 15000
0150002B Collège Jules Ferry Public AURILLAC 15000
0150003C Collège Saint-Joseph Privé AURILLAC 15000
0150004D Collège Marcel Pagnol Public MAURIAC 15200
0150005E Lycée Blaise Pascal Public SAINT-FLOUR 15100
0150006F Collège Notre-Dame Privé SAINT-FLOUR 15100

Cette relation a 5 attributs et, dans cet extrait, 6 p-uplets. Le premier p-uplet est (0150001A, Lycée Émile Duclaux, Public, AURILLAC, 15000).

Pour chaque attribut d’une relation, il est nécessaire de définir un domaine.

Définition 2 (Domaine)

Le domaine d’un attribut est l’ensemble, fini ou infini, des valeurs admissibles pour cet attribut.

Par exemple, le domaine de l’attribut statut est l’ensemble des deux chaînes {« Public », « Privé »}. L’attribut nom a pour domaine l’ensemble des chaînes de caractères (noté TEXT). L’attribut codepostal a pour domaine l’ensemble des entiers (noté INT).

Au moment de la création d’une relation, il est nécessaire de renseigner le domaine de chaque attribut. Un SGBD rigoureux s’assure alors qu’un élément ajouté à une relation respecte bien le domaine de l’attribut correspondant : c’est la contrainte de domaine (voir la Section 3).

Remarque. SQLite applique un typage souple : une colonne déclarée INTEGER accepte sans erreur la valeur 8.5 (elle est simplement stockée telle quelle). Pour que SQLite vérifie strictement les types, il faut créer la table avec l’option STRICT (SQLite 3.37 ou plus). Avec SQLite, le respect des domaines est donc en partie laissé à la charge du programmeur.

Attention. Dans une relation, il est nécessaire de respecter les deux règles ci-dessous :

  • les valeurs des attributs doivent être atomiques, c’est-à-dire d’un type simple et non d’un type construit (pas de listes, de tableaux, de p-uplets…). Par exemple, une cellule contenant « Maths, NSI » dans un attribut specialites n’est pas atomique ;
  • il n’y a pas de doublons : les p-uplets sont tous différents.

Pour s’assurer qu’il n’y a pas de doublons dans une relation, on ajoute en pratique un critère d’unicité sur l’un des attributs : la clef.

2 Clef primaire, clef étrangère

Définition 3 (Clef primaire)

Dans une relation, une clef primaire est un attribut (ou un groupe d’attributs) qui définit de manière unique chacun des p-uplets.

En d’autres termes, il s’agit d’un attribut tel que deux p-uplets sont égaux si, et seulement si, ils ont la même clef primaire.

Par exemple, dans la relation des établissements scolaires du Cantal, l’attribut code peut être choisi comme clef primaire, car il identifie de façon unique chaque établissement. L’attribut commune ne peut pas être choisi comme clef primaire, car plusieurs établissements existent dans une même commune.

Remarque. Une clef primaire n’est jamais vide (on dit qu’elle ne peut pas valoir NULL, la valeur qui représente l’absence d’information). Une clef peut aussi être composée de plusieurs attributs : dans une relation emprunts(id_livre, id_lecteur, date_emprunt), un même lecteur peut emprunter plusieurs fois le même livre, mais pas le même jour : c’est le triplet des trois attributs qui identifie un emprunt.

Pourquoi plusieurs relations ?

On pourrait naïvement penser qu’il suffit de créer une unique relation et de tout mettre dedans pour avoir une base de données. En fait, une telle approche est inapplicable et il est indispensable de créer plusieurs relations, associées les unes aux autres.

Prenons l’exemple des établissements scolaires. Un parcours de la relation précédente montre que certaines informations sont répétées plusieurs fois, comme le nom de la commune et son code postal (cellules colorées) :

code nom statut commune codepostal
0150001A Lycée Émile Duclaux Public AURILLAC 15000
0150002B Collège Jules Ferry Public AURILLAC 15000
0150003C Collège Saint-Joseph Privé AURILLAC 15000
0150004D Collège Marcel Pagnol Public MAURIAC 15200
0150005E Lycée Blaise Pascal Public SAINT-FLOUR 15100
0150006F Collège Notre-Dame Privé SAINT-FLOUR 15100

Cette duplication de l’information n’est pas souhaitable dans une base de données. La solution est de travailler avec deux relations au lieu d’une, chacune étant munie d’une clef primaire. Nous considérons pour cela la relation des communes du Cantal, qui contient un attribut Codecommune pouvant être choisi comme clef primaire. Le code postal dépendant de la commune, nous le plaçons dans cette relation. Dans la relation des établissements, nous remplaçons le nom de la commune par la valeur du code de la commune correspondante (attribut id_commune). Voici un extrait des deux relations obtenues :

Relation etablissements

code nom statut id_commune
0150001A Lycée Émile Duclaux Public 14
0150002B Collège Jules Ferry Public 14
0150003C Collège Saint-Joseph Privé 14
0150004D Collège Marcel Pagnol Public 120
0150005E Lycée Blaise Pascal Public 187
0150006F Collège Notre-Dame Privé 187

Relation communes

Codecommune Nomdelacommune Populationtotale Codepostal
14 AURILLAC 25 100 15000
85 LABROUSSE 360 15130
120 MAURIAC 3 600 15200
187 SAINT-FLOUR 6 600 15100
201 SANSAC-DE-MARMIESSE 1 900 15130
245 VALJOUZE 23 15170

L’attribut id_commune permet de lier les deux relations : les communes sont représentées dans la relation des établissements par leur code dans la relation des communes. On dit que l’attribut id_commune est une clef étrangère.

Définition 4 (Clef étrangère)

Soient deux relations R et S de clefs primaires respectives c_R et c_S. Une clef étrangère de S dans R est un attribut c_e de R dont la valeur est toujours égale exactement à une des valeurs de c_S.

Autrement dit, c_e correspond à un et un seul p-uplet de S.

Dans notre exemple, l’attribut id_commune est une clef étrangère de la relation des communes dans la relation des établissements, car c’est un attribut de la relation des établissements dont la valeur est toujours égale à une des valeurs de la clef primaire Codecommune de la relation des communes.

3 Contraintes d’intégrité

Les concepts du modèle relationnel (domaine, clef primaire, clef étrangère) permettent d’exprimer trois contraintes d’intégrité que le SGBD vérifie à chaque ajout ou modification de données.

Contrainte Énoncé Exemple de p-uplet refusé
Contrainte de domaine La valeur de chaque attribut appartient au domaine de cet attribut. Un établissement de statut « Mixte » (le domaine de statut est {Public, Privé}).
Contrainte de relation Une valeur de clef primaire apparaît au plus une fois dans la relation, et n’est jamais vide. Un second établissement de code 0150001A.
Contrainte de référence (intégrité référentielle) La valeur d’une clef étrangère est toujours une des valeurs de la clef référencée. Un établissement avec id_commune égal à 999 (aucune commune n’a ce code).

La contrainte de référence joue aussi dans l’autre sens : le SGBD refuse de supprimer une commune tant qu’un établissement y fait encore référence.

Remarque. Ces contraintes garantissent la cohérence des données vis-à-vis du schéma, mais pas à elles seules l’absence de redondances : celle-ci dépend de la qualité de la conception du schéma (voir la Section 5).

4 Schéma relationnel

Le schéma relationnel d’une base de données contient les informations suivantes :

  • les noms des différentes relations ;
  • pour chaque relation, la liste des attributs avec leur domaine respectif ;
  • pour chaque relation, la clef primaire et, éventuellement, les clefs étrangères.

Pour nos deux relations, le schéma relationnel s’écrit :

  • communes(Codecommune : INT, Nomdelacommune : TEXT, Populationtotale : INT, Codepostal : INT)
  • etablissements(code : TEXT, nom : TEXT, statut : TEXT, #id_commune : INT, latitude : FLOAT, longitude : FLOAT)

Les attributs soulignés sont des clefs primaires, le # signifie que l’on a une clef étrangère.

Le schéma relationnel peut aussi être représenté sous forme graphique :

Schéma graphique : la relation etablissements (code, nom, statut, #id_commune, latitude, longitude) est reliée par une flèche de id_commune vers la clef primaire Codecommune de la relation communes (Codecommune, Nomdelacommune, Populationtotale, Codepostal).

Structure et contenu d’une base

Définition 5 (Structure et contenu)

La structure d’une base de données relationnelle est l’ensemble de ses schémas relationnels (relations, attributs, domaines, clefs), qui doit respecter les contraintes du modèle relationnel. Son contenu est l’ensemble des p-uplets qu’elle contient à un instant donné.

Structure Contenu
Ce que c’est Les schémas : noms des relations, attributs, domaines, clefs. Les p-uplets enregistrés dans les relations.
Évolution Définie à la conception, elle change rarement. Évolue en permanence : ajouts, modifications, suppressions de p-uplets.
Exemple La relation communes possède les attributs Codecommune, Nomdelacommune… La commune de code 245 s’appelle VALJOUZE et compte 23 habitants.

5 Anomalies d’un schéma

Activité d’approche

On reprend nos données sur les livres, en ajoutant la nationalité des auteurs, dans une seule relation livres :

titre annee nom prenom nationalite
L’Usage du monde 1963 Bouvier Nicolas suisse
Chronique japonaise 1975 Bouvier Nicolas suisse
Voyage avec un âne dans les Cévennes 1879 Stevenson Robert Louis écossais
  1. Quelles informations sont répétées ?
  2. Peut-on enregistrer un nouvel auteur sans aucun livre pour l’instant ?
  3. Que perd-on si l’on supprime le livre de Stevenson ?
  4. Pour corriger une faute de frappe dans la nationalité de Bouvier, combien de lignes faut-il modifier ? Que risque-t-on en oubliant une ligne ?

Un schéma mal conçu entraîne des problèmes qu’on appelle des anomalies.

Définition 6 (Anomalies d’un schéma)

Un schéma relationnel mal conçu peut présenter :

  • de la redondance : une même information est enregistrée plusieurs fois ;
  • des anomalies d’insertion : on ne peut pas enregistrer une information sans en enregistrer une autre qui n’a rien à voir ;
  • des anomalies de suppression : supprimer une information en fait disparaître involontairement une autre ;
  • des anomalies de mise à jour : modifier une information oblige à modifier plusieurs p-uplets, au risque d’incohérences.

Exemple 1 (Les établissements du Cantal dans une seule relation)

Reprenons la relation unique etablissements(code, nom, statut, commune, codepostal).

  • Redondance : le nom de la commune AURILLAC et son code postal sont répétés pour chacun de ses établissements.
  • Insertion : pour enregistrer la commune de LABROUSSE, qui n’a aucun établissement, il faudrait inventer un établissement fictif (le code, clef primaire, ne peut pas être vide).
  • Suppression : si l’on supprime l’unique établissement de MAURIAC, toute trace de la commune MAURIAC disparaît.
  • Mise à jour : si la commune change de nom (fusion de communes), il faut modifier trois lignes pour AURILLAC ; en oublier une rend la base incohérente (deux noms pour la même commune).

La décomposition en deux relations etablissements et communes, reliées par une clef étrangère, supprime ces anomalies : chaque information n’est enregistrée qu’une fois, la commune LABROUSSE peut exister sans établissement, supprimer un établissement n’efface pas la commune, et un changement de nom ne modifie qu’une seule ligne.

À retenir

  • Une relation est une table ; ses colonnes sont des attributs, ses lignes des p-uplets. Chaque attribut a un domaine, et ses valeurs sont atomiques.
  • Une clef primaire identifie de façon unique chaque p-uplet. Une clef étrangère fait référence à la clef primaire d’une autre relation.
  • Trois contraintes d’intégrité : contrainte de domaine, de relation (unicité de la clef primaire) et de référence (intégrité référentielle).
  • Le schéma relationnel décrit la structure de la base ; les p-uplets en forment le contenu.
  • Un schéma mal conçu entraîne de la redondance et des anomalies d’insertion, de suppression et de mise à jour : on les évite en répartissant les données dans plusieurs relations liées par des clefs étrangères.