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:Et vous disposez d´un utilisateur habilité à créer des tables dans cette base. Par exemple:create database Messagerie
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.