Discussion :

[loicmidy]

bonjour,

je cherche à construire un MCD avec les règles suivantes :
un enquêteur enquête chaque mois 0 à n produits.
un mois donné un produit est enquêté par exactement un enquêteur.
on peut dans le temps réaffecter des produits entre enquêteurs.


voici ma modélisation initiale :
table enqueteur
idEnq, nom, prenom

table produit
idProd, libelle

table affectationProduitEnqueteuridProd, idEnq, anneeDebut,moisDebut, anneeFin,moisFin

ex:
le produit 5 est enquêté par l'enquêteur 1 puis 2 puis à nouveau 1 (définitivement soit l'année 9999).
5,1, 2009,1, 2009,4
5,2, 2009,5, 2009,8
5,1, 2009,9, 9999,1

l'enquêteur num 10 veut savoir quels relevés il a à faire pour une année/mois donné :
select idProd from affectationProduitEnqueteur
where idEnq=10 and anneeDebut*100+moisDebut<= annee*100+mois<= anneeFin*100+ moisFin

j'accepter de créer un enquêteur sans produits.
en revanche je veux qu'un produit ait chaque mois un enquêteur. je vois comment assurer cette intégrité au niveau des traitements (il y aura dans une transaction un insert dans produit et un insert dans table affectationProduitEnqueteur) mais est il possible de mettre cette contrainte d'intégrité dans le MCD?

bref, j'ai l'impression que ce que je fais n'est pas très pro.
Est il possible de faire mieux?

Cordialement

[fsmrel]

Bonsoir loicmidy,


Vous parlez de tables, donc vous en êtes déjà à l’étage MLD (Modèle Logique des Données). On va donc y rester pour le moment.

5,1, 2009,1, 2009,4 5,2, 2009,5, 2009,8 5,1, 2009,9, 9999,1

Ça se lit comment ? A mon avis, vous devriez (re)voir la première forme normale (cf. Bases de données relationnelles et normalisation)

un mois donné un produit est enquêté par exactement un enquêteur.

Appelons DURANT l’intervalle de temps [t1: t2] pendant lequel le PRODUIT Px est du ressort du seul ENQUETEUR Ey. t1 et t2 représentent respectivement une date de début et une date de fin.

Dans le contexte du Modèle Relationnel de Données, cette contrainte est ainsi formalisée :

{DURANT, PRODUIT} → {ENQUETEUR}

DURANT, PRODUIT et ENQUETEUR représentent des attributs de l’en-tête d’une variable relationnelle (relvar) R, ce que l’on appelle une table en SQL. Les accolades mettent en évidence le fait que {DURANT, PRODUIT} et {ENQUETEUR} sont des sous-ensembles (au sens de la théorie des ensembles) de l’en-tête (ensemble des attributs) de la relvar R.

La flèche symbolise la contrainte selon laquelle, pour chaque valeur de la paire {DURANT, PRODUIT}, il y a une seule valeur du singleton {ENQUETEUR}, ni plus, ni moins. Cette contrainte porte le nom de dépendance fonctionnelle (DF). La partie gauche de la DF, {DURANT, PRODUIT}, est appelée le déterminant et la partie droite, {ENQUETEUR}, le dépendant.

Ici, la relvar R correspond à votre table affectationProduitEnqueteur, à la représentation intervallaire près :

R {DURANT, PRODUIT, ENQUETEUR}

Exemple de valeur (valeur de relation, ou relation en abrégeant) de la relvar R :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
 R { DURANT    PRODUIT  ENQUETEUR }
    [d01:d04]    p01      e01
    [d02:d03]    p02      e01
    [d01:d02]    p03      e02
    [d05:d07]    p01      e02  
      ...        ...      ...

Pour votre part, vous avez défini le triplet {idProd, idEnq, anneeDebut,moisDebut} comme étant clé (primaire) de la relvar. Si je vous suivais, le triplet {DURANT, PRODUIT, ENQUETEUR} serait clé lui aussi de la relvar R, mais il ne peut en être ainsi.
En effet, toujours dans le contexte du Modèle Relationnel de Données, la définition de la clé (plus précisément de la clé candidate, dont la clé primaire n’est qu’un cas particulier) est la suivante :

Une clé candidate est un sous-ensemble d’attributs K de l’en-tête d’une relvar R et respectant les deux contraintes suivantes :

Unicité. Deux tuples distincts de R ne peuvent avoir même valeur de K.
Irréductibilité (ou minimalité). Il n’existe pas de sous-ensemble strict de K garantissant la règle d’unicité.

En vertu de la dépendance fonctionnelle {DURANT, PRODUIT} → {ENQUETEUR},le triplet {DURANT, PRODUIT, ENQUETEUR} est réductible. La réduction {DURANT, PRODUIT} constitue cette-fois-ci une clé candidate, car elle-même n’est pas réductible (en effet, pendant la même période, on peut avoir plusieurs enquêteurs qui enquêtent, et le même produit peut être enquêté par plusieurs enquêteurs). En soulignant la clé :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
 R { DURANT    PRODUIT  ENQUETEUR }
    [d01:d04]    p01      e01
    [d02:d03]    p02      e01
    [d01:d02]    p03      e02
    [d05:d07]    p01      e02

L’en-tête de votre table doit à son tour être le suivant :

affectationProduitEnqueteur (idProd, idEnq, anneeDebut,moisDebut, anneeFin,moisFin)

Montons à l’étage supérieur. Comment exprimer la chose au niveau MCD ?

Pour reprendre les noms que j’ai utilisés, on a trois entités-types, DURANT, PRODUIT, ENQUETEUR, participant à une association-type ternaire R, chaque patte étant porteuse d’une cardinalité maximum N.

Pour prendre en compte la dépendance fonctionnelle définie ci-dessus, on utilise une CIF (contrainte d’intégrité fonctionnelle). Par exemple :


A propos des CIF, vous vous pouvez vous reporter à la FAQ Merise (« CIF (ou dépendance fonctionnelle) de A à Z »).


Au sujet des intervalles temporels.

Redescendons à l’étage MLD. Le triplet {idProd, anneeDebut, moisDebut} constitue une clé candidate (que vous retiendrez comme clé primaire). Mais, pour des raisons de symétrie évidentes, le triplet {idProd, anneeFin, moisFin} constitue lui aussi une clé candidate. Par ailleurs, vous serez obligé de développer des triggers SQL (ou autres sous-programmes) pour contrôler qu’il n’y a pas de chevauchements illicites. En effet, dans l’état actuel des choses, le même jour, le même produit peut être enquêté par différents enquêteurs. Vous avez aussi à implémenter d’autres contraintes, du genre : Quel que soit la période, pour chaque produit on ne peut pas ne pas avoir d’enquêteur.

Tout ceci est magistralement traité dans l’ouvrage très complet du trio Date, Darwen et Lorentzos « Temporal Data and The Relational Model », mais attention : 400 pages qui ne sont pas d’une lecture toujours facile quand on ne connaît que modérément la théorie relationnelle. Quoi qu’il en soit, pour vous faire une idée, à votre moteur de recherche proposez par exemple :

"point types and interval types",
"in this chapter, we introduce a number of useful operators that apply to interval values",
"12.5 both current and historical relvars"...