problème de conception MCD/cardinalité

**Laure94** · 27/10/2011, 14h07

Bonjour,

je bloque sur mes cardinalités et la conception de mon MCD

je vous explique :

On a des salariés et des formateurs qui spécialisés dans plusieurs domaines.
Les salariés participent à des formations.
Un formateur dirige une formation à la fois.
Un salarié peut participer à une formation à la fois.
Une formation peut accueillir 10 salariés au maximum.
Un formation dure un jour.

alors pour moi ça donne ça :

FORMATEUR -----(1,n)-----SPÉCIALISER-----(1,n)-----DOMAINE
du coup SPÉCIALISER devient une table.
ensuite vient mon problème avec les formations.

SALARIE--(1,1)---(1,10)--FORMATION--(1,1)---(1,n)--FORMATEUR
|(1,1)
|(1,n)
DATE_FORMATION

En fait je ne sais pas comment gérer le fait qu'il faille entre 1 et 10 salariés pour une formation à une date donnée et qu'en même temps il y a un formateur pour cette formation à la date donnée.

Merci pour vos réponses !

**punkoff** · 27/10/2011, 14h23

Bonjour,

Je penses qu'il faut distinguer vos règle de gestion de votre MCD.

En particulier ceci :
Salarié peut participer à une formation à la fois.
Une formation peut accueillir 10 salariés au maximum.

Vosu allez le gérer soit applicativement, soir avec des trigger / contraintes sur vos tables.

Du coup, pour le MCD :
SALARIE-(0,n)--PARTICIPE--(0,n)-FORMATION-(1,1)--DISPENSE--(0,n)-FORMATEUR

Ensuite pour la date, je ne sais pas si vous avez une table de date de formation ?
Si oui : FORMATION-(1,1)-----PLANNIFIER-----(0,n)-DATE_FORMATIONS

Concernant :
"Salarié peut participer à une formation à la fois"

Lors du passage au MPD l'association PARTICIPE se transformera en table et là vous pourrez mettre une contrainte d'unicité entre l'id du salarié et de la formation.
Si vous voullez voir qu'un salarié ne participe pas déjà à une formation el même jour il faudra vérifier ceci via une requête ..ou peut-être un trigger.

"Une formation peut accueillir 10 salariés au maximum"

Vous pouvez créer un trigger lors de l'insertion qui remontera une exception si il y a 10 occurances déjà présente votre table d'association PARTICIPE.

Je ne suis pas spécialiste en modélisation ceci dit, attendez d'autre affirmation / infirmation.

**fsmrel** · 30/10/2011, 19h12

Bonsoir,

La réalisation des modèles conceptuels des données (MCD) en Merise pose quelques problèmes par toujours simples dès lors qu’on souhaite intégrer des contraintes du genre : « Un salarié ne peut pas suivre deux formations au même moment ». Comme le dit punkoff dans son message, on pourrait attendre de passer à la génération des tables pour mettre en œuvre une telle contrainte.

Mais pour aller plus avant dans la modélisation conceptuelle merisienne, on peut quand même s’essayer à monter la barre d’un cran et explorer certaines pistes concernant la formalisation de ces contraintes...

Supposons qu’une première tentative de modélisation ait donné lieu à la représentation graphique suivante :

Let’s go. Essayons de prendre en compte la contrainte qui veut qu’un formateur ne puisse pas donner deux formations en même temps : une technique consiste à passer par la mise en œuvre de ce que l’on appelle une contrainte d’unicité complète, telle qu’elle est définie dans [Afcet90] :

« Une contrainte d’unicité complète est définie sur la relation R, appelée sa "portée", avec pour "cible" l’un des individus C reliés par R, si et seulement si l’occurrence de C est déterminée de façon unique lorsque l’on connaît les occurrences des autres individus reliés par R. »

Traduisons cela dans le contexte des formations :

Une contrainte d’unicité complète est définie sur l’association-type DONNER_FORMATION, appelée sa "portée", avec pour "cible" l’entité-type FORMATION, si et seulement si chaque occurrence de FORMATION est déterminée de façon unique lorsque l’on connaît les occurrences des autres entités-types reliées par DONNER_FORMATION. Partons de la contrainte :

A une date donnée, un formateur ne donne qu’une formation

De façon symbolique on peut écrire (la flèche représente une dépendance fonctionnelle) :

{DATE, FORMATEUR} → {FORMATION}

Concrètement, cela veut dire que si l’on veut prendre la contrainte en compte, alors l’entité-type DATE doit être représentée dans le MCD, au même titre que les entités-types FORMATEUR et FORMATION, et l’on peut alors compléter l’énoncé précédent :

Une contrainte d’unicité complète est définie sur l’association-type DONNER_FORMATION, appelée sa "portée", avec pour "cible" l’entité-type FORMATION, si et seulement si chaque occurrence de FORMATION est déterminée de façon unique lorsque l’on connaît les occurrences des autres entités-types reliées par DONNER_FORMATION, à savoir DATE et FORMATEUR.

Commençons donc par représenter en ce sens les formations données par les formateurs :

Faisons maintenant intervenir la contrainte d’unicité complète (symbolisée ici en vert par la lettre U cerclée et que l’on pourrait remplacer par l’abréviation CIF plus habituelle) :

Cette fois-ci, la contrainte {DATE, FORMATEUR} → {FORMATION} est effectivement prise en compte.

Cela dit, en faisant l’économie du cercle, on peut simplifier ainsi la représentation :

De la même façon, prenons en compte la contrainte qui veut qu’à une date donnée, une formation soit donnée par un seul formateur :

En prenant en compte à la fois les deux contraintes, la situation est la suivante :

Passons aux formations suivies par les salariés. On doit faire figurer la contrainte :

A une date donnée, un salarié ne suit qu’une formation.

Ou encore, de façon symbolique :

{DATE, SALARIE} → {FORMATION}

Ce que l’on symbolise par la flèche bleue ci-dessous, toujours avec la même technique :

A nouveau, prenons en compte l’ensemble des contraintes :

So far, so good. Mais une autre contrainte est à prendre en considération : la date à laquelle un salarié suit une formation doit être la date à laquelle cette formation est donnée. On se sert alors d’une contrainte d’inclusion.

Pour citer à nouveau [Afcet90] :

« La relation R est soumise à une "inclusion" dans la relation S vis-à-vis d’une liste non vide (P, P’, ...) d’individus communs appelée "pivot" de la contrainte, si et seulement si aucune occurrence de (PxP’x...) ne peut participer à R sans participer à S. La relation R est appelée la "portée", la relation S la cible de la contrainte. »

Autrement dit, la relation SUIVRE_FORMATION est soumise à une inclusion dans la relation DONNER_FORMATION vis-à-vis le la collection (FORMATION, DATE) parce que chaque valeur <FormationId, FormationDate> obtenue par la projection de SUIVRE_FORMATION sur les attributs FormationId et FormationDate doit être aussi une valeur obtenue par la projection de DONNER_FORMATION sur les mêmes attributs.
Graphiquement, on représente la contrainte d’inclusion par un cercle contenant la lettre I, accompagné de traits pointillés reliant ce cercle aux entités-types FORMATION et DATE pour symboliser le pivot, la flèche ayant pour source SUIVRE_FORMATION et pour cible DONNER_FORMATION précisant quelles relations (portée et cible) sont parties prenantes dans cette affaire.

Ouf, un bon dessin... :

Il faut reconnaître que la modélisation au niveau logique est quand même plus simple car tous les attributs nécessaires sont présents dans les en-têtes des tables pour la prise en compte des contraintes :

Observations :

La date retrouve son statut d’attribut lambda, il n’y a pas de table DATE.

La table DONNER_FORMATION a pour clé primaire {FormationId, FormationDate}, en conséquence de quoi, à une date donnée, une formation donnée ne peut être assurée que par un seul formateur.
Cette table est dotée d’une clé alternative {FormateurId, FormationDate} (alternate key, symbolisée par le mickey <ak>), en conséquence de quoi, à une date donnée, un formateur ne peut donner qu’une seule formation.

La table SUIVRE_FORMATION a pour clé primaire {SalarieId, FormationDate}, en conséquence de quoi, à une date donnée un salarié ne peut suivre qu’une seule formation. La table est dotée d’une clé étrangère {FormationId, FormationDate} établissant ainsi une contrainte référentielle vis-à-vis de la clé primaire {FormationId, FormationDate} de la table DONNER_FORMATION, contrainte qui permet de définir quelle formation est suivie à quelle date par les salariés inscrits.

Le code SQL correspondant est le suivant :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
CREATE TABLE FORMATION
(
        FormationId           CHAR(8)        NOT NULL
      , FormationLibelle      VARCHAR(32)    NOT NULL
   , CONSTRAINT FORMATION_PK PRIMARY KEY (FormationId) 
) ;
CREATE TABLE FORMATEUR
(
        FormateurId       CHAR(8)        NOT NULL
      , FormateurNom      VARCHAR(32)    NOT NULL
   , CONSTRAINT FORMATEUR_PK PRIMARY KEY (FormateurId) 
) ;
CREATE TABLE SALARIE
(
        SalarieId         CHAR(8)        NOT NULL
      , SalarieNom        VARCHAR(32)    NOT NULL
   , CONSTRAINT SALARIE_PK PRIMARY KEY (SalarieId)
) ;
CREATE TABLE DONNER_FORMATION  
(
        FormationId       CHAR(8)        NOT NULL
      , FormationDate     DATE           NOT NULL
      , FormateurId       CHAR(8)        NOT NULL
      , HeureDebut        INT            NOT NULL
      , Duree             INT            NOT NULL
   , CONSTRAINT DONNER_FOR_PK PRIMARY KEY (FormationId, FormationDate)
   , CONSTRAINT DONNER_FOR_AK UNIQUE (FormateurId, FormationDate)
   , CONSTRAINT DONNER_FOR_FK1 FOREIGN KEY (FormationId) REFERENCES FORMATION
   , CONSTRAINT DONNER_FOR_FK2 FOREIGN KEY (FormateurId) REFERENCES FORMATEUR
) ;
CREATE TABLE SUIVRE_FORMATION  
(
        SalarieId         CHAR(8)        NOT NULL
      , FormationId       CHAR(8)        NOT NULL
      , FormationDate     DATE           NOT NULL
   , CONSTRAINT SUIVRE_FOR_PK PRIMARY KEY (SalarieId, FormationDate)
   , CONSTRAINT SUIVRE_FOR_SAL FOREIGN KEY (SalarieId) REFERENCES SALARIE
   , CONSTRAINT SUIVRE_FOR_FOR FOREIGN KEY (FormationId, FormationDate) REFERENCES DONNER_FORMATION
) ;

Un début de jeu d’essai, à compléter :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
 
INSERT INTO FORMATION VALUES ('f01', 'Merise, Présentation') ;
INSERT INTO FORMATION VALUES ('f02', 'Merise, MCD') ;
INSERT INTO FORMATION VALUES ('f03', 'Merise, MCT') ;
INSERT INTO FORMATION VALUES ('f04', 'SQL, Présentation') ;
INSERT INTO FORMATION VALUES ('f05', 'SQL, Opérateurs') ;
INSERT INTO FORMATION VALUES ('f06', 'Billard, l''américaine en 1 heure') ;
 
SELECT '' AS FORMATION, * FROM FORMATION ;
 
INSERT INTO FORMATEUR VALUES ('p01', 'Albert') ;
INSERT INTO FORMATEUR VALUES ('p02', 'Bernard') ;
INSERT INTO FORMATEUR VALUES ('p03', 'Charles') ;
INSERT INTO FORMATEUR VALUES ('p04', 'Philou') ;
 
SELECT '' AS FORMATEUR, * FROM FORMATEUR ;
 
INSERT INTO SALARIE VALUES ('s01', 'Louis') ;
INSERT INTO SALARIE VALUES ('s02', 'Gérard') ;
INSERT INTO SALARIE VALUES ('s03', 'Pierre') ;
INSERT INTO SALARIE VALUES ('s04', 'Arthur') ;
INSERT INTO SALARIE VALUES ('s05', 'Léa') ;
INSERT INTO SALARIE VALUES ('s06', 'André') ;
INSERT INTO SALARIE VALUES ('s07', 'Louise') ;
INSERT INTO SALARIE VALUES ('s08', 'Agathe') ;
INSERT INTO SALARIE VALUES ('s09', 'Marcel') ;
INSERT INTO SALARIE VALUES ('s10', 'René') ;
INSERT INTO SALARIE VALUES ('s11', 'Jeanne') ;
INSERT INTO SALARIE VALUES ('s12', 'Marc') ;
 
SELECT '' AS SALARIE, * FROM SALARIE ;
 
INSERT INTO DONNER_FORMATION VALUES ('f01', '2011-10-25', 'p01', 08, 8) ;
INSERT INTO DONNER_FORMATION VALUES ('f02', '2011-10-25', 'p02', 14, 4) ;
INSERT INTO DONNER_FORMATION VALUES ('f06', '2011-10-29', 'p04', 15, 3) ;
INSERT INTO DONNER_FORMATION VALUES ('f02', '2011-10-29', 'p02', 09, 5) ;
INSERT INTO DONNER_FORMATION VALUES ('f01', '2011-10-29', 'p01', 08, 8) ;
 
SELECT '' AS DONNER_FORMATION, * FROM DONNER_FORMATION ;
 
INSERT INTO SUIVRE_FORMATION VALUES ('s01', 'f01', '2011-10-25') ;
INSERT INTO SUIVRE_FORMATION VALUES ('s02', 'f01', '2011-10-25') ;
INSERT INTO SUIVRE_FORMATION VALUES ('s01', 'f02', '2011-10-29') ;
INSERT INTO SUIVRE_FORMATION VALUES ('s03', 'f02', '2011-10-29') ;
INSERT INTO SUIVRE_FORMATION VALUES ('s04', 'f02', '2011-10-29') ;
INSERT INTO SUIVRE_FORMATION VALUES ('s05', 'f01', '2011-10-25') ;
INSERT INTO SUIVRE_FORMATION VALUES ('s05', 'f02', '2011-10-29') ;
INSERT INTO SUIVRE_FORMATION VALUES ('s06', 'f02', '2011-10-29') ;
INSERT INTO SUIVRE_FORMATION VALUES ('s07', 'f02', '2011-10-29') ;
INSERT INTO SUIVRE_FORMATION VALUES ('s08', 'f02', '2011-10-29') ;
INSERT INTO SUIVRE_FORMATION VALUES ('s09', 'f02', '2011-10-29') ;
INSERT INTO SUIVRE_FORMATION VALUES ('s10', 'f02', '2011-10-29') ;
INSERT INTO SUIVRE_FORMATION VALUES ('s11', 'f02', '2011-10-29') ;
INSERT INTO SUIVRE_FORMATION VALUES ('s12', 'f02', '2011-10-29') ;
INSERT INTO SUIVRE_FORMATION VALUES ('s12', 'f01', '2011-10-25') ;
 
SELECT '' AS SUIVRE_FORMATION, * FROM SUIVRE_FORMATION ;

La limitation à 10 des inscriptions à une formation fait l’objet d’une assertion SQL :

Code SQL :

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

1
2
3
4
5
6
7
8
 CREATE ASSERTION SUIVRE_FOR_ASSERT01
    CHECK NOT EXISTS 
      (
       SELECT   FormationId, FormationDate, COUNT(*) 
       FROM     SUIVRE_FORMATION
       GROUP BY FormationId, FormationDate
       HAVING COUNT (*) > 10
      ) ;

Si le SGBD ne connaît pas l’instruction CREATE ASSERTION, passer par un trigger (exemple basique en SQL Server, améliorable par des spécialistes comme iberserk, mikedavem, elsuket, ...) :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE TRIGGER SUIVRE_FORMATION_Trigger_Insert_01 ON SUIVRE_FORMATION INSTEAD OF INSERT AS
         DECLARE @Compteur INT
         DECLARE @Err VARCHAR(254) 
         SET @Compteur = 10  
 
         SELECT   FormationId, FormationDate, COUNT(*)
         FROM  (
                 SELECT FormationId, FormationDate
                 FROM   SUIVRE_FORMATION
                 UNION ALL 
                 SELECT FormationId, FormationDate
                 FROM   INSERTED
               ) AS x     
        GROUP BY FormationId, FormationDate
        HAVING COUNT (*) > @Compteur 
 
  IF @@Rowcount > 0
      BEGIN 
          SET @Err = 'Insert dans SUIVRE_FORMATION : le nombre d''inscriptions à une formation est limité à '  
                   + CAST(@Compteur AS VARCHAR(2))
          SELECT 'Erreur' AS 'Table SUIVRE_FORMATION, tentative Insert : échec', * FROM INSERTED
          RAISERROR (@Err, 10, 1) 
          RETURN
      END
  INSERT INTO SUIVRE_FORMATION 
      SELECT *
      FROM   INSERTED ;

Pardonnez-moi si j’ai été bavard, mais le sujet le voulait...

________________________________

[Afcet90] Journée Afcet du 15 novembre 1990 - Le formalisme de données Merise : extensions du pouvoir d’expression

**fsmrel** · 02/11/2011, 03h42

En passant, une solution alternative au trigger SUIVRE_FORMATION_Trigger_Insert_01 :

Mise en œuvre d’une fonction qui calcule le nombre d’inscrits à une formation et vérification par une contrainte CHECK du nombre maximum autorisé (inspiré de ce message et suivants, SGBD : SQL Server).

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE FUNCTION NB_INSCRITS_FONCTION (@FormationId CHAR(8), @FormationDate DATE)
    RETURNS INT
    AS 
        BEGIN
           RETURN (SELECT COUNT(*)
                   FROM   SUIVRE_FORMATION 
                   WHERE  FormationId = @FormationId 
                   AND    FormationDate = @FormationDate) ;
        END
GO
ALTER TABLE SUIVRE_FORMATION
      ADD CONSTRAINT SUIVRE_FORMATION_NB_INSCRITS
      CHECK (dbo.NB_INSCRITS_FONCTION (FormationId, FormationDate) <= 10); 
GO

problème de conception MCD/cardinalité

Schéma

Discussions similaires

Partager

Partager