Skip to content

1 4 8 2 schema

Une étude de cas ⅖ : schéma de la base

Supports complémentaires

Maintenant, nous sommes prêts à implanter la base en supposant que le schéma E/A de la instance-messagerie a été validé. Avec un peu d´expérience, la production des commandes de création des tables est directe. Prenons une dernière fois le temps d´expliquer le sens des règles de passage.

Note
Pour appliquer les commandes qui suivent, vous devez disposer d´un accès à un serveur. Une base doit être créée. Par exemple:

create database Messagerie
Et vous disposez d´un utilisateur habilité à créer des tables dans cette base. Par exemple:
grant all on Messagerie.* to athénaïs identified by 'motdepasse'

On raisonne en terme de dépendance fonctionnelle. Nous avons tout d´abord celles définies par les entités.

  • \(idContact \to nom, prénom, email\)
  • \(idMessage \to contenu, dateEnvoi\)

C´est l´occasion de vérifier une dernière fois que tous les attributs mentionnés sont atomiques (email par exemple représente une seule adresse électronique, et pas une liste) et qu´il n´existe pas de dépendance fonctionnelle non explicitée. Ici, on peut trouver la DF suivante:

  • \(email \to idContact, nom, prénom\)

Elle nous dit que email est une clé candidate. Il faudra le prendre en compte au moment de la création du schéma relationnel.

Voici maintenant les dépendances données par les associations. La première lie un message au contact qui l´a émis. On a donc une dépendance entre les identifiants des entités.

  • \(idMessage \to idContact\)

Un fois acquis que la partie droite est l´identifiant du contact, le nommage est libre. Il est souvent utile d´introduire dans ce nommage la signification de l´association représentée. Comme il s´agit ici de l´émission d´un message par un contact, on peut représenter cette DF avec un nommage plus explicite.

  • \(idMessage \to idEmetteur\)

La seconde DF correspond à l´association plusieurs-à-un liant un message à celui auquel il répond. C´est une association réflexive, et pour le coup la DF \(idMessage \to idMessage\) n´aurait pas grand sens. On passe donc directement à un nommage représentatif de l´association.

  • \(idMessage \to idPrédécesseur\)

Etant entendu que idPrédécesseur est l´identifiant d´un contact. Nous avons les DF, il reste à identifier les clés. Les attributs idContact et idMessage sont les clés primaires, email est une clé secondaire, et nous ne devons pas oublier la clé définie par l´association plusieurs-plusieurs représentant l´envoi d´un message. Cette clé est la paire (idContact, idMessage), que nous nommerons plus explicitement (idDestinataire, idMessage).

Voilà, nous appliquons l´algorithme de normalisation qui nous donne les relations suivantes:

  • Contact (idContact, nom, prénom, email)
  • Message (idMessage, contenu, dateEnvoi, idEmetteur, idPrédécesseur)
  • Envoi (idDestinataire, idMessage)

Les clés primaires sont en gras, les clés étrangères (correspondant aux attributs issus des associations plusieurs-à-un) en italiques.

Nous sommes prêts à créer les tables. Voici la commande de création de la table Contact.

create table Contact (idContact integer not null,
                  nom varchar(30) not null,
                  prénom varchar(30)  not null,
                  email varchar(30) not null,
                  primary key (idContact),
                  unique (email)
               );  

On note que la clé secondaire email est indiquée avec la commande unique. Rappelons pourquoi il semble préférable de ne pas la choisir pour clé primaire: la clé primaire d´une table est référencée par des clés étrangères dans d´autres tables. Modifier la clé primaire implique de modifier de manière synchrone les clés étrangères, ce qui peut être assez délicat.

Voici la table des messages, avec ses clés étrangères.

create table Message (
    idMessage  integer not null,
    contenu text not null,
    dateEnvoi   datetime,
    idEmetteur int not null,
    idPrédecesseur int,
    primary key (idMessage),
    foreign key (idEmetteur) 
          references Contact(idContact),
    foreign key (idPrédecesseur) 
       references Message(idMessage)
 )

L´attribut idEmetteur, clé étrangère, est déclaré not null, ce qui impose de toujours connaître l´émetteur d´un message. Cette contrainte, dite "de participation" semble ici raisonnable.

En revanche, un message peut ne pas avoir de prédécesseur, et idPrédécesseur peut donc être à null, auquel cas la contrainte d´intégrité référentielle ne s´applique pas.

Et pour finir, voici la table des envois.

create table Envoi ( 
    idDestinataire  integer not null,
    idMessage  integer not null,
    primary key (idDestinataire, idMessage),
    foreign key (idDestinataire) 
           references Contact(idContact),
    foreign key (idMessage) 
           references Message(idMessage)
 )

C´est la structure typique d´une table issue d´une association plusieurs-plusieurs. La clé est composite, et chacun de ses composants est une clé étrangère. On remarque que la structure de la clé empêche d´un même message soit envoyé deux fois à un même destinataire (plus précisément: on ne saurait pas représenter des envois multiples). C´est un choix dont l´origine remonte à la conception E/A.