La Poste

Des ingénieurs de La Poste ont conçu un MCD en UML dans le but de créer une base de données leur permettant de recueillir des statistiques sur les courriers acheminés.

Pour chaque courrier, on enregistre l'adresse (rue et numéro uniquement) du destinataire (obligatoire) et de l'émetteur (facultative) ainsi que le type (lettre ou colis). Un suivi peut être effectué pour savoir quand le courrier a été envoyé et reçu.

Chaque adresse est gérée par un bureau de poste identifié par un code postal. Les bureaux de poste sont reliés à des centres de tri qui jouent le rôle d'intermédiaire dans l'acheminement des courriers entre bureaux de poste. Un acheminement entre un centre de tri et un bureau de poste est réalisé, dans les deux sens possibles, par un ou plusieurs types de transport (voiture, train, avion...). À chaque type de transport correspond une valeur de taxe carbone.

MCD pour les statistiques de la Poste

Question

Traduisez ce MCD en langage relationnel, en spécifiant les clés (candidates, primaires et étrangères) ainsi que les contraintes (unicité, non-nullité, etc.). Il n'est pas demandé de préciser le type des attributs.

Justifiez les choix que vous faites. Faites des hypothèses si nécessaire.

Solution

Première partie du MLD
1
BureauDePoste(#codePostal)
2
Adresse(#codePostal=>BureauDePoste.codePostal, #num, #rue)
3
CentreDeTri(#ville)
4
Acheminement(#centreDeTri=>CentreDeTri.ville, #bureauDePoste=>BureauDePoste.codePostal, #transport, taxeCarbone)
5
Contrainte : Proj(CentreDeTri, ville) IN Proj(Acheminement, centreDeTri) et Proj(BureauDePoste, codePostal) IN Proj(Acheminement, bureauDePoste)
RemarqueChoix de la transformation de l'héritage

Les différentes informations du MCD sont :

  • classe mère abstraite ;

  • héritage exclusif ;

  • héritage presque complet.

Les deux premières informations sont en faveur de la transformation par les classes filles. Les deux dernières sont en faveur de la transformation par la classe mère.

La transformation par référence reste une solution possible, mais est moins avantageuse puisqu'il faudra contrôler :

  • qu'aucune instance de Courrier ne soit référencée ni par une instance de Lettre, ni par une instance de Colis (classe mère abstraite) ;

  • qu'aucune instance de Courrier ne soit simultanément référencée par une instance de Lettre et de Colis (héritage exclusif).

Transformation par la classe mère
1
Courrier(#id, numSuivi, dateEmission, dateRéception, type:{lettre|colis}, timbreVert, poids, émetteurCodePostal=>Adresse.codePostal, émetteurNum=>Adresse.num, émetteurRue=>Adresse.rue, destinataireCodePostal=>Adresse.codePostal, destinataireNum=>Adresse.num, destinataireRue=>Adresse.rue) avec numSuivi unique et type, destinataireCodePostal, destinataireNum et destinataireRue non nuls
2
vLettre=Projection(Restriction(Courrier, type='lettre'), id, timbreVert)
3
vColis=Projection(Restriction(Courrier, type='colis'), id, poids)

Chaque enregistrement aura une valeur nulle, soit pour timbreVert soit pour poids.

Le fait qu'on ait un héritage exclusif et une classe mère abstraite limite le nombre de valeurs possibles pour type.

Transformation par les classes filles
1
Lettre(#id, numSuivi, dateEmission, dateRéception, timbreVert, émetteurCodePostal=>Adresse.codePostal, émetteurNum=>Adresse.num, émetteurRue=>Adresse.rue, destinataireCodePostal=>Adresse.codePostal, destinataireNum=>Adresse.num, destinataireRue=>Adresse.rue) avec numSuivi unique et destinataireCodePostal, destinataireNum et destinataireRue non nuls
2
Colis(#id, numSuivi, dateEmission, dateRéception, poids, émetteurCodePostal=>Adresse.codePostal, émetteurNum=>Adresse.num, émetteurRue=>Adresse.rue, destinataireCodePostal=>Adresse.codePostal, destinataireNum=>Adresse.num, destinataireRue=>Adresse.rue) avec numSuivi unique et destinataireCodePostal, destinataireNum et destinataireRue non nuls

Cette transformation est un peu plus lourde à mettre en œuvre puisqu'il faut répéter dans chaque classe fille toutes les clés étrangères issues des associations de la classe mère (avec Adresse et Suivi).

Transformation par référence
1
Courrier(#id, numSuivi, dateEmission, dateRéception, émetteurCodePostal=>Adresse.codePostal, émetteurNum=>Adresse.num, émetteurRue=>Adresse.rue, destinataireCodePostal=>Adresse.codePostal, destinataireNum=>Adresse.num, destinataireRue=>Adresse.rue) avec numSuivi unique et destinataireCodePostal, destinataireNum et destinataireRue non nuls
2
Lettre(#id=>Courrier.id, timbreVert)
3
Colis(#id=>Courrier.id, poids)
4
vLettre=Jointure(Lettre, Courrier, Lettre.id=Courrier.id)
5
vColis=Jointure(Colis, Courrier, Colis.id=Courrier.id)
6
Contraintes : Proj(Courrier, id) IN (PROJ(Lettre, id) UNION Proj(Colis, id)) et PROJ(Lettre, id) INTER Proj(Colis, id) = {} (ensemble vide)

La contrainte à implémenter rend cette transformation également plus lourde à mettre en œuvre.

RemarqueChoix de la transformation de la relation 0..1:1

Dans les trois variantes précédentes, la classe Suivi a été absorbée dans Courrier ou Lettre et Colis (selon la transformation de l'héritage). Cela semble correct d'un point de vue sémantique puisqu'un suivi ne peut concerner qu'un courrier. Il est à noter que numSuivi perd ici son caractère de clé, puisqu'il peut être nul, mais reste unique.

Il aurait également été possible d'avoir à chaque fois une relation distincte pour Suivi. Dans le cas où on a une relation pour la classe mère Courrier, cela donne :

1
Suivi(#numSuivi, dateEmission, dateRéception, courrier=>Courrier.id) avec courrier clé candidate

En revanche, si on n'a des relations que pour les classes filles Lettre et Colis, on devrait avoir dans Suivi deux clés étrangères lettre et colis, toutes deux clés candidates de la relation, mais ne pouvant être valuées simultanément, ce qui est impossible. Une solution consiste à intégrer une clé étrangère suivi dans Lettre et Colis, mais il faut alors interdire toute instance de Suivi d'être référencée simultanément par une instance de Lettre et une instance de Colis.

1
Suivi(#numSuivi, dateEmission, dateRéception)
2
Lettre(#id, ..., suivi=>Suivi.numSuivi) avec numSuivi unique
3
Colis(#id, ..., suivi=>Suivi.numSuivi) avec numSuivi unique
4
Contrôle : un suivi est forcément associé à une seule lettre ou un seul colis, et jamais à une lettre et un colis en même temps.
ConseilMLD pour la suite de l'exercice

On fera le choix de transformer l'héritage par la classe mère et l'association 0..1:1 par absorption de Suivi dans Courrier :

1
BureauDePoste(#codePostal)
2
Adresse(#codePostal=>BureauDePoste.codePostal, #num, #rue)
3
CentreDeTri(#ville)
4
Acheminement(#centreDeTri=>CentreDeTri.ville, #bureauDePoste=>BureauDePoste.codePostal, #transport, taxeCarbone) avec Proj(Acheminement, centreDeTri) In Proj(CentreDeTri, ville) et Proj(Acheminement, bureauDePoste) In Proj(BureauDePoste, codePostal)
5
Courrier(#id, numSuivi, dateEmission, dateRéception, type:{lettre|colis}, timbreVert, poids, émetteurCodePostal=>Adresse.codePostal, émetteurNum=>Adresse.num, émetteurRue=>Adresse.rue, destinataireCodePostal=>Adresse.codePostal, destinataireNum=>Adresse.num, destinataireRue=>Adresse.rue) avec numSuivi unique et type, destinataireCodePostal, destinataireNum et destinataireRue non nuls
6
vLettre=Projection(Restriction(Courrier, type='lettre'), id, numSuivi, dateEmission, dateRéception, timbreVert, émetteurCodePostal, émetteurNum, émetteurRue, destinataireCodePostal, destinataireNum, destinataireRue)
7
vColis=Projection(Restriction(Courrier, type='colis'), id, numSuivi, dateEmission, dateRéception, poids, émetteurCodePostal, émetteurNum, émetteurRue, destinataireCodePostal, destinataireNum, destinataireRue)

Question

Déterminez en quelle forme normale est le schéma que vous avez posé, et faites les modifications nécessaires pour atteindre la 3NF. Justifiez.

Solution

1NF

Tous les attributs sont atomiques et toutes les relations ont au moins une clé, on est donc bien en 1NF.

2NF

  • Les relations BureauDePoste, Courrier et CentreDeTri sont en 2NF puisque leur clé est constituée d'un seul attribut.

  • La relation Adresse est "toute-clé", donc est en 2NF.

  • Dans la relation Acheminement, on a la dépendance fonctionnelle transport→taxeCarbone. Une partie de la clé (transport) détermine donc un attribut non-clé (taxeCarbone) : on n'est pas en 2NF.

  • Voici le MLD modifié pour respecter la 2NF :

1
Transport(#type, taxeCarbone)
2
Acheminement(#centreDeTri=>CentreDeTri.ville, #bureauDePoste=>BureauDePoste.codePostal, #transport=>Transport.type)
3NF

Il n'y a aucune dépendance fonctionnelle entre des attributs non-clé dans la relation Courrier, ce qui assure sa 3NF. En particulier, il n'y a pas de dépendance fonctionnelle entre numSuivi et (dateEmission, dateRéception), puisque numSuivi peut être nul (c'est d'ailleurs pour cela qu'il n'est pas clé). Son unicité assure d'ailleurs la non-redondance.

Pour les autres relations, la 3NF est assurée : elles sont "toute-clé" ou n'ont qu'un seul attribut en dehors de la clé.

Question

Écrivez les instructions SQL pour créer les tables des relations correspondant uniquement aux classes BureauDePoste, CentreDeTri et Acheminement, en vous basant sur votre schéma relationnel normalisé.

Solution

1
CREATE TABLE CentreDeTri(
2
ville VARCHAR(30) PRIMARY KEY
3
);
4
CREATE TABLE BureauDePoste (
5
codePostal NUMBER(5) PRIMARY KEY
6
);
7
CREATE TABLE Transport (
8
type CHAR PRIMARY KEY,
9
taxeCarbone FLOAT
10
);
11
CREATE TABLE Acheminement(
12
centreDeTri VARCHAR(30),
13
bureauDePoste NUMBER(5),
14
transport CHAR,
15
FOREIGN KEY (centreDeTri) REFERENCES CentreDeTri(ville),
16
FOREIGN KEY (bureauDePoste ) REFERENCES BureauDePoste(codePostal),
17
FOREIGN KEY (transport) REFERENCES Transport(type),
18
PRIMARY KEY(centreDeTri, bureauDePoste, transport)
19
);