SCHEMA MCD SEMINAIRE

**fsmrel** · 16/11/2011, 04h27

Bonsoir,

Observations concernant les convocations des formateurs et les cumuls.

@ DJMOUS :

Selon votre dernier diagramme, les formateurs sont convoqués en fonction des commandes des clients :

Mais s’il y a 40 commandes concernant 3 sessions, au lieu de recevoir 3 convocations, le formateur en recevra 40, ce qui risquera de l’agacer...

@ Shrek92 :

Selon votre diagramme, il ne semble pas que les formateurs soient concernés par les convocations, mais s’il en était ainsi, vous seriez sur la même ligne que DJMOUS...

Bien que le sujet soit notamment la convocation des stagiaires et des formateurs, les règles de gestion qui ont été fournies sont muettes quant aux convocations de ces derniers : en principe on devrait rien modéliser en ce qui les concerne, mais on peut aussi improviser et dire par exemple qu’un formateur sera convoqué par le service responsable des séminaires, autrement dit manuellement, auquel cas, comme on ne connaît pas a priori la date d’envoi des convocations, on représente alors ainsi les choses (noter l’utilisation de l’identification relative) :

Après dérivation :

DateEnvoi sera renseigné après action du service responsable des séminaires, de telle sorte qu’il y ait quand même une trace de l’envoi de la convocation, alors que le formateur a pu ne rien recevoir.

Envoyé par DJMOUS

Il existe toujours la problématique du calcul "CUMSOC" que je n'arrive pas à positionner dans le MCD.

Envoyé par Shrek92

Ce qui me pose encore problème sont les deux rubriques statistiques:
* CUMSOC: Cumul des journées-élèves réalisées pour le compte de la société depuis le début de l'année
* NBSFOR: Nombre de séminaires dispensés par le formateur depuis le début de l'année

Le cumul des journées-élèves ne devrait théoriquement pas avoir a être modélisé « en dur ». En effet on dispose de tous les éléments permettant de le calculer. Faisons une vue sur le diagramme proposé par Shrek92 (j’ai ajouté l’attribut NBRFAC dans l’en-tête de l’entité-type FACTURE, car c’est la facture qui fait foi en cas de réclamation et une contrainte de base données devra garantir que NBRFAC = NBRCOM ; par ailleurs l’attribut DATSEM a été absorbé par l’entité-type SESSION) :

L’attribut NBRFAC permet donc de savoir combien de journées NJ ont été facturées à la société E pour la session S.

Par ailleurs il existe les dépendances fonctionnelles :

DF1 : FACTURE → COMMANDE
DF2 : COMMANDE → DEMANDE
DF3 : DEMANDE → SESSION
DF4 : SESSION → SEMINAIRE
DF5 : DEMANDE → SOCIETE

Et par transitivité :

DF6 : FACTURE → SESSION
DF7 :FACTURE → SEMINAIRE
DF8 :FACTURE → SOCIETE

Du fait de DF6 on sait donc déterminer la durée NJS en jours d’une session du type de séminaire correspondant par facture de la société E :

NJS = NBRFAC x DURSEM

Si on souhaite avoir des statistiques par année (on va supposer qu’on ne se limite pas à l’année en cours, et de toute façon, qui peut le plus peut le moins...), on sait aussi calculer le nombre NDA de demandes effectuées pour la société E et pour l’année A : on effectue une sélection (restriction au sens du Modèle Relationnel de Données, WHERE en SQL) ayant pour critère la date de la session (attribut DATSEM de l’entité-type SESSION).
Pour avoir le cumul des jours par entreprise E, on somme les NJS pour E. La valeur prise par CUMSOC est obtenue en ayant filtré par DATSEM et en groupant par année (cf. la vue SQL CUMUL_SOCIETE ci-dessous).

Il est donc inutile de matérialiser CUMSOC, mais si vous souhaitez quand même le faire, vous pouvez modéliser la chose comme l’a fait DJMOUS dans son 1er diagramme :

Pour la petite histoire, ci-joint le code SQL (MS SQL Server) basé sur le diagramme proposé par Shrek92 :

1) Définition des structures des tables :

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
58
59
60
61
62
63
64
65
66
67
68
CREATE TABLE SOCIETE
(
        CODSOC      CHAR(8)        NOT NULL
      , NOMSOC      VARCHAR(32)    NOT NULL
    , CONSTRAINT SOCIETE_PK PRIMARY KEY (CODSOC)     
) ;
 
CREATE TABLE FORMATEUR
(
        NUMFOR      CHAR(8)        NOT NULL
      , NOMFOR      VARCHAR(32)    NOT NULL
      , NBSFOR      INT            NOT NULL
    , CONSTRAINT FORMATEUR_PK PRIMARY KEY (NUMFOR)
 
) ; 
 
CREATE TABLE SEMINAIRE
(
        CODSEM      CHAR(8)        NOT NULL
      , DURSEM      INT            NOT NULL
      , NUMFOR      CHAR(8)        NOT NULL
    , CONSTRAINT SEMINAIRE_PK PRIMARY KEY (CODSEM)     
    , CONSTRAINT SEM_FOR_FK FOREIGN KEY (NUMFOR) REFERENCES FORMATEUR
                 ON DELETE NO ACTION     
) ;   
 
CREATE TABLE SESSION
(
        CODSES      CHAR(8)        NOT NULL
      , CODSEM      CHAR(8)        NOT NULL
      , DATSEM      DATE           NOT NULL
    , CONSTRAINT SESSION_PK PRIMARY KEY (CODSES)     
    , CONSTRAINT SESSION_AK UNIQUE (CODSEM, DATSEM)     
    , CONSTRAINT SES_SEM_FK FOREIGN KEY (CODSEM) REFERENCES SEMINAIRE
                 ON DELETE NO ACTION     
) ;
 
CREATE TABLE DEMANDE
(
        NUMDEM      CHAR(8)        NOT NULL
      , CODSOC      CHAR(8)        NOT NULL
      , CODSES      CHAR(8)        NOT NULL
    , CONSTRAINT DEMANDE_PK PRIMARY KEY (NUMDEM)     
    , CONSTRAINT DEM_SOC_FK FOREIGN KEY (CODSOC) REFERENCES SOCIETE
                 ON DELETE NO ACTION
    , CONSTRAINT DEM_SES_FK FOREIGN KEY (CODSES) REFERENCES SESSION
                 ON DELETE NO ACTION
) ;
 
CREATE TABLE COMMANDE
(
        NUMCOM      CHAR(8)        NOT NULL
      , NUMDEM      CHAR(8)        NOT NULL
    , CONSTRAINT COMMANDE_PK PRIMARY KEY (NUMCOM)     
    , CONSTRAINT COM_DEM_FK FOREIGN KEY (NUMDEM) REFERENCES DEMANDE
                 ON DELETE NO ACTION
) ;
 
CREATE TABLE FACTURE
(
        NUMFAC      CHAR(8)        NOT NULL
      , NUMCOM      CHAR(8)        NOT NULL
      , NBRFAC      INT            NOT NULL
    , CONSTRAINT FACTURE_PK PRIMARY KEY (NUMFAC)     
    , CONSTRAINT FACTURE_AK UNIQUE (NUMCOM)     
    , CONSTRAINT FAC_COM_FK FOREIGN KEY (NUMCOM) REFERENCES COMMANDE
                 ON DELETE NO ACTION
) ;

2) Création d’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
INSERT INTO SOCIETE VALUES (1, 'Société Un') ;
INSERT INTO SOCIETE VALUES (2, 'Société Deux') ;
INSERT INTO SOCIETE VALUES (3, 'Société Trois') ;
INSERT INTO SOCIETE VALUES (4, 'Société Quatre') ;
 
INSERT INTO FORMATEUR VALUES (1, 'Formateur Un', 0) ;
INSERT INTO FORMATEUR VALUES (2, 'Formateur Deux', 0) ;
INSERT INTO FORMATEUR VALUES (3, 'Formateur Trois', 0) ;
INSERT INTO FORMATEUR VALUES (4, 'Formateur Quatre', 0) ;
 
INSERT INTO SEMINAIRE VALUES (1, 4, 1) ;
INSERT INTO SEMINAIRE VALUES (2, 3, 1) ;
INSERT INTO SEMINAIRE VALUES (3, 5, 1) ;
INSERT INTO SEMINAIRE VALUES (4, 4, 2) ;
 
INSERT INTO SESSION VALUES (1, 1, '2006-04-03') ;
INSERT INTO SESSION VALUES (2, 1, '2006-09-11') ;
INSERT INTO SESSION VALUES (3, 1, '2007-03-12') ;
INSERT INTO SESSION VALUES (4, 1, '2007-06-04') ;
INSERT INTO SESSION VALUES (11, 2, '2008-02-25') ;
INSERT INTO SESSION VALUES (12, 2, '2008-05-12') ;
INSERT INTO SESSION VALUES (21, 3, '2009-03-17') ;
INSERT INTO SESSION VALUES (22, 3, '2009-03-24') ;
INSERT INTO SESSION VALUES (23, 3, '2009-03-31') ;
INSERT INTO SESSION VALUES (31, 1, '2010-01-18') ;
INSERT INTO SESSION VALUES (32, 1, '2010-02-15') ;
INSERT INTO SESSION VALUES (33, 1, '2010-03-22') ;
 
INSERT INTO DEMANDE VALUES (1, 1, 1) ;
INSERT INTO DEMANDE VALUES (2, 1, 3) ;
INSERT INTO DEMANDE VALUES (3, 2, 1) ;
INSERT INTO DEMANDE VALUES (4, 3, 1) ;
INSERT INTO DEMANDE VALUES (5, 3, 12) ;
INSERT INTO DEMANDE VALUES (6, 1, 12) ;
 
INSERT INTO COMMANDE VALUES (1, 1) ;
INSERT INTO COMMANDE VALUES (2, 1) ;
INSERT INTO COMMANDE VALUES (3, 3) ;
INSERT INTO COMMANDE VALUES (4, 5) ;
INSERT INTO COMMANDE VALUES (5, 6) ;
 
INSERT INTO FACTURE VALUES (1, 1, 3) ;
INSERT INTO FACTURE VALUES (2, 2, 4) ;
INSERT INTO FACTURE VALUES (3, 3, 2) ;
INSERT INTO FACTURE VALUES (4, 4, 2) ;
INSERT INTO FACTURE VALUES (5, 5, 3) ;

3) Définition de la vue permettant de calculer les cumuls par société et par année :

Code SQL :

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

1
2
3
4
5
6
7
8
9
CREATE VIEW CUMUL_SOCIETE (NOMSOC, ANNEE, CUMSOC)
AS
    SELECT v.NOMSOC, YEAR(t.DATSEM), SUM(x.NBRFAC * u.DURSEM)  
    FROM   FACTURE AS x JOIN COMMANDE  AS y ON x.NUMCOM = y.NUMCOM
                        JOIN DEMANDE   AS z ON y.NUMDEM = z.NUMDEM
                        JOIN SESSION   AS t ON z.CODSES = t.CODSES
                        JOIN SEMINAIRE AS u ON t.CODSEM = u.CODSEM
                        JOIN SOCIETE   AS v ON z.CODSOC = v.CODSOC 
    GROUP BY v.NOMSOC, YEAR(t.DATSEM) ;

4) Exploitation de la vue : recherche des cumuls par société et par année :

Code SQL :

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

1
2
SELECT NOMSOC, ANNEE, CUMSOC
FROM   CUMUL_SOCIETE ;

Au résultat :

Code :

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

1
2
3
4
5
NOMSOC         ANNEE   CUMSOC
Société Un      2006       28
Société Un      2008        9
Société Deux    2006        8
Société Trois   2008        6

Recherche des cumuls par société pour l’an dernier :

Code SQL :

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

1
2
3
SELECT NOMSOC, ANNEE, CUMSOC
FROM   CUMUL_SOCIETE
WHERE  ANNEE = YEAR(GETDATE()) - 1 ;

En ce qui concerne le calcul de NBSFOR, le principe est le même.

J'ai les yeux qui se croisent, j'en resterai là pour ce soir...

Bon courage à vous deux.

**DJMOUS** · 16/11/2011, 19h38

Bonsoir,

Merci pour tous ces éclaircissements sur ces valeurs calculées.
En définitif, les données "CUMSOC" et "NBSFOR" ont été intégrées dans l'entité SOCIETE et FORMATEUR.

Je pense ainsi avoir balayer l'ensemble du MCD. Il me reste a notion de convocation des formateurs...je ne suis pas certain qu'il faille la modéliser.

Je rends ma copie sur le sujet lundi...

Merci encore pour votre aide.

SCHEMA MCD SEMINAIRE

Schéma

Discussions similaires

Partager

Partager