Modélisation catégories, sous-catégories, rubriques

**fsmrel** · 14/10/2015, 04h39

Bonsoir almoha,

Envoyé par almoha

l'aménagement proposé remplit parfaitement son office

A nouveau : Ouf !

Envoyé par almoha

je sollicite votre savant éclairage

Hum... N’exagérons pas, l’adjectif pourrait bien vite être invalidé...

Envoyé par almoha

A votre avis, quel est le meilleur moyen d'implémenter cette nouvelle fonctionnalité dans le MCD actuel ? Faut-il scinder ce dernier en deux modules, "apprentissage" d'une part et "examen" d'autre part ?

La partie "apprentissage" étant considérée comme stabilisée, on devrait pouvoir compléter le MCD en lui ajoutant la partie "examen" sans avoir à altérer ce qui a déjà été fait.

Voici un premier brouillon d’essai de proposition :

Si je comprends bien, lorsque le tout nouveau candidat Raoul va commencer une épreuve, en tout premier lieu il lui est proposé de choisir le nombre de questions que devra comporter le questionnaire (50, 100, 200 phrases). Dans un 2e temps, le système accouchera d’un questionnaire tout neuf, respectant le pourcentage de phrases proposées, en fonction de leur niveau de difficulté (1 ou 2 ou 3), phrases prises de la table PHRASE et présentées de façon aléatoire.

Pour éviter une inflation de questionnaires au fil du temps, et qu’il faudra conserver, je propose un scénario (récusable bien sûr !) selon lequel les questionnaires seront créés d’avance, avec un contenu fixé par vous-même, ou carrément aléatoire si vous le souhaitez (vous-même jouant les Raoul), et selon lequel l’ordre aléatoire de présentation des phrases n’est en fait à prendre en compte qu’au moment où l’on présente un questionnaire à Raoul lui-même. Dans ce contexte, il se peut que Raoul et Fernand peuvent passent leur examen en se servant d’un même questionnaire, mais avec l’illusion qu’il s’agit de questionnaires différents, du fait du grand nombre de questions et de l’ordre aléatoire — donc différent — de leur présentation.
Dans ce contexte, du point de vue de la modélisation, on ajoute dans le MCD une entité-type QUESTIONNAIRE, dotée, outre son identifiant QuestionnaireId, d’un attribut « naturel » QuestionnaireCode, identifiant alternatif (unicité garantie), et dont l’utilisateur (vous, pas le candidat...) a la maîtrise. Exemples de valeurs : "Q001", "Q002", etc.

La mise en œuvre d’une entité-type PHRASES_NB référencée par l’entité-type QUESTIONNAIRE permet de distinguer les questionnaires selon le nombre de phrases qu’ils comportent (50, 100, 200).

On définit ensuite une association entre les entités-types QUESTIONNAIRE et PHRASE, que par manque d’imagination je nomme QUESTIONNAIRE_PHRASE. Comme un questionnaire est composé de plusieurs phrases et qu’une phrase donnée peut servir pour plusieurs questionnaires (je suppose du moins qu’il en est ainsi), le MCD actuel est ainsi enrichi :

Pour anticiper sur la suite (utilisation des questionnaires par les candidats), on peut transformer l’association QUESTIONNAIRE_PHRASE en entité-type (de toute façon, dans tous les cas, QUESTIONNAIRE_PHASE fera l’objet d’une table au stade du MLD) :

Au stade SQL, l’identifiant de cette entité-type donnera lieu à la clé primaire suivante :

{QuestionnaireId, RegleId, PhraseId}

Ce qui garantit la règle :

Pour un même questionnaire, chaque phrase ne doit figurer qu'une seule fois.

Envoyé par almoha

Le questionnaire devra comporter 35 % de phrases de niveau 1; 35 % de phrases de niveau 2; 30 % de phrases de niveau 3 (mon application n'aura que 3 niveaux de difficulté).

Ces pourcentages ne relèvent pas à proprement parler de la modélisation des données et sont à charge de l’application. En l’occurrence, dans un but d’automatisation, on peut envisager la mise en oeuvre d’une procédure MySQL allant puiser dans le réservoir des phrases (table PHRASE) et alimentant la table QUESTIONNAIRE_PHRASE en respectant les pourcentages voulus.

Par ailleurs, on sait traiter des niveaux de difficulté, puisque ceux-ci ont déjà été modélisés en relation avec les phrases. Étant donné qu’un questionnaire comporte au moins trois et au plus trois niveaux de difficulté, on en tient compte pour la cardinalité portée par la patte connectant l’entité-type QUESTIONNAIRE et l’association QD :

Mais apparaît un problème de chemin (entraînant la mise en oeuvre d’une contrainte d’inclusion, cf. le message #13) : on peut en effet avoir un « cheminement » différent, selon qu’on aille de QUESTIONNAIRE à DIFFICULTE directement via l’association QD, ou indirectement via les associations QPQ, QPP et ETRE. Ce problème a toutefois sa solution, non pas au niveau MCD, mais MLD.

Dérivons donc cette partie du MCD. Au résultat :

J’ai fait figurer en rouge et en pointillés la table QD et ses associations(le but étant de la faire disparaître). Comme on vient de le voir, on peut « naviguer » de QUESTIONNAIRE à DIFFICULTE selon deux voies distinctes, donc avec des résultats possiblement différents. Puisqu’il y a risque, on va chercher à ne conserver qu’une seule voie de navigation, à savoir celle qui passe par QUESTIONNAIRE_PHRASE et PHRASE.

Dans le cas de la table PHRASE, une phrase détermine une difficulté et l’idée est de propager l’attribut DifficulteId jusqu’à QUESTIONNAIRE_PHRASE. J’utilise à cet effet une vieille ruse qui est la suivante : la table PHRASE a pour clé primaire la paire {RegleId, PhraseId}, mais il est parfaitement légal et légitime de définir en plus le triplet {RegleId, PhraseId, DifficulteId} comme surclé de la table PHRASE (une surclé garantit la règle d’unicité mais pas nécessairement celle d’irréductibilité des clés candidates, ce qui en l’occurrence n’est pas gênant, puisque c’est la paire {RegleId, PhraseId} qui garantit la règle d’irréductibilité). Ceci fait, comme il est licite qu’une clé étrangère puisse référencer une surclé non clé primaire, on fait hériter QUESTIONNAIRE_PHRASE du triplet {RegleId, PhraseId, DifficulteId} qui y remplace la paire {RegleId, PhraseId} en tant que clé étrangère vis-à-vis de PHRASE. Le MLD devient le suivant :

La table QD a disparu, car pour connaître les difficultés, on a directement la réponse dans la table QUESTIONNAIRE_PHRASE. Exit le problème du chemin (et la vieille ruse permet d’éviter de devoir en passer par des triggers...)

Incidemment, un lecteur averti et attentif pourra faire observer que la table QUESTIONNAIRE_PHRASE n’est pas en deuxième forme normale, car elle contient la dépendance fonctionnelle {RegleId, PhraseId} -> {DifficulteId} dont le déterminant n’est pas clé candidate, mais en l’occurrence ça n’est pas un problème, les dangers inhérents étant écartés du fait de la contrainte référentielle (clé étrangère) liant les tables PHRASE et QUESTIONNAIRE_PHRASE.

Script SQL (observer que la table PHRASE est enrichie de la surclé, laquelle est référencée par la table QUESTIONNAIRE_PHRASE) :


CREATE TABLE PHRASE 
(
        RegleId              INT             NOT NULL
      , PhraseId             INT             NOT NULL
      , DifficulteId         INT             NOT NULL
      , Position             INT             NOT NULL
      , Texte                VARCHAR(64)     NOT NULL
    , CONSTRAINT PHRASE_PK PRIMARY KEY (RegleId, PhraseId)
    , CONSTRAINT PHRASE_AK UNIQUE (RegleId, PhraseId, DifficulteId)
    , CONSTRAINT PHRASE_REGLE_FK FOREIGN KEY (RegleId)
      REFERENCES REGLE (RegleId)
    , CONSTRAINT PHRASE_DIFFICULTE_FK FOREIGN KEY (DifficulteId)
      REFERENCES DIFFICULTE (DifficulteId)
) ;

CREATE TABLE PHRASES_NB 
(
        PhrasesNbId          INT             NOT NULL
      , PhrasesNb            INT             NOT NULL
    , CONSTRAINT PHRASES_NB_PK PRIMARY KEY (PhrasesNbId)
    , CONSTRAINT PHRASES_NB_AK UNIQUE (PhrasesNb)
) ;

CREATE TABLE QUESTIONNAIRE 
(
        QuestionnaireId      INT             NOT NULL
      , QuestionnaireCode    CHAR(4)         NOT NULL
      , PhrasesNbId          INT             NOT NULL
    , CONSTRAINT QUESTIONNAIRE_PK PRIMARY KEY (QuestionnaireId)
    , CONSTRAINT QUESTIONNAIRE_AK UNIQUE (QuestionnaireCode)
    , CONSTRAINT QUESTIONNAIRE_PHRASES_NB_FK FOREIGN KEY (PhrasesNbId)
          REFERENCES PHRASES_NB (PhrasesNbId)
) ;


CREATE TABLE QUESTIONNAIRE_PHRASE 
(
        QuestionnaireId      INT             NOT NULL
      , RegleId              INT             NOT NULL
      , PhraseId             INT             NOT NULL
      , DifficulteId         INT             NOT NULL
    , CONSTRAINT QUESTIONNAIRE_PHRASE_PK PRIMARY KEY (QuestionnaireId, RegleId, PhraseId)
    , CONSTRAINT QUESTIONNAIRE_PHRASE_PHRASE_FK FOREIGN KEY (RegleId, PhraseId, DifficulteId)
          REFERENCES PHRASE (RegleId, PhraseId, DifficulteId)
    , CONSTRAINT QUESTIONNAIRE_PHRASE_QUESTIONNAIRE_FK FOREIGN KEY (QuestionnaireId)
          REFERENCES QUESTIONNAIRE (QuestionnaireId) ON DELETE CASCADE
) ;

Envoyé par almoha

Le questionnaire devra suivre la séquence suivante : d'abord les phrases de niveau 1, puis celles de niveau 2 et enfin celles de niveau 3.

Étant donné que la table QUESTIONNAIRE_PHRASE a été dotée de l’attribut DifficulteId, on saura présenter les phrases dans l’ordre des niveaux.

Envoyé par almoha

hormis l’ordre des niveaux à respecter, l'ordre des phrases au sein de chaque niveau devra être aléatoire.

Par référence à mon idée de départ, d’utiliser des questionnaires déjà créés par vous, il ne s’agit plus que d’un problème de présentation des données et non pas de stockage interne. Pour présenter les phrases de façon aléatoire (au sein d’un niveau), on peut utiliser la fonction RAND et la clause ORDER BY :


SELECT *, RAND() AS Alea FROM QUESTIONNAIRE_PHRASE ORDER BY DifficulteId, Alea ;

Envoyé par almoha

Chaque questionnaire devra être conservé dans la base de façon à pouvoir retrouver le ou les score(s) de l'utilisateur et la ou les date(s) à laquelle ou auxquelles il y a répondu.

Au vu de ce qui précède, les questionnaires sont conservés, mais l’ordre aléatoire de présentation des phrases n’est pas préservé.

Envoyé par almoha

Dans le but d'améliorer son score, l'utilisateur peut décider de répondre à nouveau à un questionnaire auquel il a déjà répondu.

Raoul pouvant répondre plus d’une fois à un même questionnaire, un attribut « Iteration » permettra de distinguer ses réponses (entité-type INTERROGATION).

Les phrases de ce questionnaire seront les mêmes, mais leur ordre de présentation doit-il être différent la fois suivante ? Peu importe ? Le même ? (Auquel cas on devra stocker dans la base de données la séquence de la présentation des phrases par formulaire et par candidat).

Question : un candidat peut-il plancher deux fois le même jour sur le même questionnaire ?

MCD (avec la prise en compte de l’utilisateur)

INTERROGATION est une association déguisée en entité-type, et représente une paire {utilisateur, questionnaire}.

SELECTION est une association permettant de représenter les choix de phrases pour un utilisateur et un questionnaire donnés.

MLD

Suite du script SQL :


CREATE TABLE INTERROGATION 
(
        UtilisateurId        INT             NOT NULL
      , QuestionnaireId      INT             NOT NULL
      , Iteration            INT             NOT NULL  default 1
      , DateExamen           DATE            NOT NULL
      , Score                INT             NOT NULL
      , CONSTRAINT INTERROGATION_PK PRIMARY KEY (UtilisateurId, QuestionnaireId, Iteration)
      , CONSTRAINT INTERROGATION_UTILISATEUR_FK FOREIGN KEY (UtilisateurId)
            REFERENCES UTILISATEUR (UtilisateurId) 
      , CONSTRAINT INTERROGATION_QUESTIONNAIRE_FK FOREIGN KEY (QuestionnaireId)
            REFERENCES QUESTIONNAIRE (QuestionnaireId)
) ;

CREATE TABLE SELECTION 
(
        UtilisateurId        INT             NOT NULL
      , QuestionnaireId      INT             NOT NULL
      , Iteration            INT             NOT NULL
      , RegleId              INT             NOT NULL
      , PhraseId             INT             NOT NULL
      , Validite             INT             NOT NULL      
      , CONSTRAINT SELECTION_PK PRIMARY KEY (UtilisateurId, QuestionnaireId, Iteration, RegleId, PhraseId)
      , CONSTRAINT SELECTION_INTERROGATION_FK FOREIGN KEY (UtilisateurId, QuestionnaireId, Iteration)
            REFERENCES INTERROGATION (UtilisateurId, QuestionnaireId, Iteration) 
      , CONSTRAINT SELECTION_QUESTIONNAIRE_PHRASE_FK FOREIGN KEY (QuestionnaireId, RegleId, PhraseId)
            REFERENCES QUESTIONNAIRE_PHRASE (QuestionnaireId, RegleId, PhraseId)
) ;

Maintenant, si ce scénario ne convient pas, on peut le remettre à plat...

**almoha** · 14/10/2015, 21h03

Bonsoir fsmrel,

Merci. Je réponds à vos questions et remarques :

Si je comprends bien, lorsque le tout nouveau candidat Raoul va commencer une épreuve, en tout premier lieu il lui est proposé de choisir le nombre de questions que devra comporter le questionnaire (50, 100, 200 phrases). Dans un 2e temps, le système accouchera d’un questionnaire tout neuf, respectant le pourcentage de phrases proposées, en fonction de leur niveau de difficulté (1 ou 2 ou 3), phrases prises de la table PHRASE et présentées de façon aléatoire.

C'est tout à fait cela.

je propose un scénario...

Votre scénario emporte mon adhésion !

...avec un contenu fixé par vous-même, ou carrément aléatoire si vous le souhaitez

J'opte pour la solution du contenu fixé de façon aléatoire.

Comme un questionnaire est composé de plusieurs phrases et qu’une phrase donnée peut servir pour plusieurs questionnaires (je suppose du moins qu’il en est ainsi)

Tout à fait.

Les phrases de ce questionnaire seront les mêmes, mais leur ordre de présentation doit-il être différent la fois suivante ? Peu importe ? Le même ?

Peu importe.

Question : un candidat peut-il plancher deux fois le même jour sur le même questionnaire ?

Dans l'absolu, oui.

Les pourcentages ne relèvent pas à proprement parler de la modélisation des données et sont à charge de l’application. En l’occurrence, dans un but d’automatisation, on peut envisager la mise en oeuvre d’une procédure MySQL allant puiser dans le réservoir des phrases (table PHRASE) et alimentant la table QUESTIONNAIRE_PHRASE en respectant les pourcentages voulus.

Effectivement, la mise en place d'une telle procédure correspond à l'automatisation que je souhaite obtenir.

Par ailleurs, j'ai les interrogations suivantes :

- par quel mécanisme va être valorisé l'attribut "Score" de la table INTERROGATION ? Pour déterminer que l'utilisateur a bien répondu à une phrase et donc obtenu 5 points, j'imagine qu'il faut créer une table équivalente à la table REPONDRE ? La table REPONDRE serait réservée à l'apprentissage tandis qu'une table "REPONDRE_EXAM" serait dédiée à l'examen ? Un trigger sera sans doute également de mise ?

- quel est le rôle de l'attribut "Validite" de la table SELECTION ?

- la valorisation des attributs "Itération" relèvera-t-elle de triggers ?

**fsmrel** · 16/10/2015, 20h57

Bonsoir almoha,

Envoyé par almoha

quel est le rôle de l'attribut "Validite" de la table SELECTION ?

Bonne question. Le rôle de cet attribut est de permettre de savoir si le candidat a fourni une réponse vraie ou fausse. Mais comme la valeur « vrai » ou « faux » dépend directement de la position retenue par le candidat dans la phrase, le mieux est de remplacer cet attribut par un attribut « Position », comme dans le cas de la table REPONDRE :

Envoyé par almoha

Pour déterminer que l'utilisateur a bien répondu à une phrase et donc obtenu 5 points, j'imagine qu'il faut créer une table équivalente à la table REPONDRE ?

Vu ce qui précède, il s’agit de la table SELECTION :

Envoyé par fsmrel

SELECTION est une association permettant de représenter les choix de phrases pour un utilisateur et un questionnaire donnés.

Illustrons avec l’exemple suivant :

Le 15 octobre 2015, Raoul s’est attaqué pour la 1re fois au questionnaire Q001 et il a obtenu un score égal à 10.

Selon la table SELECTION ci-dessous, Raoul a fourni une bonne réponse pour la phrase "Il a un petit soucis" (RegleId = 1, PhraseId = 2), en jugeant qu’elle contient une erreur puisqu’il a valorisé Position à 5, ce qui est conforme au contenu de la table PHRASE.

Raoul a encore bon pour sa 2e réponse, mais il s’est planté avec sa 3e réponse.

Deux bonnes réponses sur trois pour Raoul : son score est égal à 10.

On observe à cette occasion que l’attribut Score de la table INTERROGATION pourrait disparaître, puisqu’on sait calculer un score en comparant les valeurs des attributs Position de la table PHRASE et de la table SELECTION. Maintenant, si vous préférez conserver cet attribut Score, il faudra le calculer en temps différé par appel d’une procédure, ou en temps réel à l’aide d’un trigger (faisabilité assurée avec la plupart des SGBD, mais à vérifier dans le cas de MySQL...)


Table PHRASE

RegleId  PhraseId  DifficulteId  Position  Texte    ... 
      1         2             2         5  Il a un petit soucis
     10         3             1         5  Le Biglotron est un extrordinaire appareil
     10         4             3         0  Le flugdug prenant appui sur la muffée du connecteur à rustine

Table QUESTIONNAIRE

QuestionnaireId    QuestionnaireCode    ...
              1    Q001

Table QUESTIONNAIRE_PHRASE

QuestionnaireId    RegleId    PhraseId    DifficulteId
              1          1           2               2
              1         10           3               1
              1         10           4               3

Table UTILISATEUR

UtilisateurId    UtilisateurNom
            2    Raoul

Table INTERROGATION

UtilisateurId    QuestionnaireId    Iteration    DateExamen    Score
            2                  1            1    2015-10-15       10

Table SELECTION

UtilisateurId    QuestionnaireId    Iteration    RegleId    PhraseId    Position
            2                  1            1         1            2           5
            2                  1            1        10            3           5
            2                  1            1        10            4           7

Complétons le script de création des tables SQL :



CREATE TABLE PHRASES_NB 
(
        PhrasesNbId          INT             NOT NULL
      , PhrasesNb            INT             NOT NULL
    , CONSTRAINT PHRASES_NB_PK PRIMARY KEY (PhrasesNbId)
    , CONSTRAINT PHRASES_NB_AK UNIQUE (PhrasesNb)
) ;

CREATE TABLE QUESTIONNAIRE 
(
        QuestionnaireId      INT             NOT NULL
      , QuestionnaireCode    CHAR(4)         NOT NULL
      , PhrasesNbId          INT             NOT NULL
    , CONSTRAINT QUESTIONNAIRE_PK PRIMARY KEY (QuestionnaireId)
    , CONSTRAINT QUESTIONNAIRE_AK UNIQUE (QuestionnaireCode)
    , CONSTRAINT QUESTIONNAIRE_PHRASES_NB_FK FOREIGN KEY (PhrasesNbId)
          REFERENCES PHRASES_NB (PhrasesNbId)
) ;

CREATE TABLE QUESTIONNAIRE_PHRASE 
(
        QuestionnaireId      INT             NOT NULL
      , RegleId              INT             NOT NULL
      , PhraseId             INT             NOT NULL
      , DifficulteId         INT             NOT NULL
    , CONSTRAINT QUESTIONNAIRE_PHRASE_PK PRIMARY KEY (QuestionnaireId, RegleId, PhraseId)
    , CONSTRAINT QUESTIONNAIRE_PHRASE_PHRASE_FK FOREIGN KEY (RegleId, PhraseId, DifficulteId)
          REFERENCES PHRASE (RegleId, PhraseId, DifficulteId)
    , CONSTRAINT QUESTIONNAIRE_PHRASE_QUESTIONNAIRE_FK FOREIGN KEY (QuestionnaireId)
          REFERENCES QUESTIONNAIRE (QuestionnaireId) ON DELETE CASCADE
) ;

CREATE TABLE INTERROGATION 
(
        UtilisateurId        INT             NOT NULL
      , QuestionnaireId      INT             NOT NULL
      , Iteration            INT             NOT NULL DEFAULT 0
      , DateExamen           DATE            NOT NULL
      , Score                INT             NOT NULL DEFAULT 0
      , CONSTRAINT INTERROGATION_PK PRIMARY KEY (UtilisateurId, QuestionnaireId, Iteration)
      , CONSTRAINT INTERROGATION_UTILISATEUR_FK FOREIGN KEY (UtilisateurId)
            REFERENCES UTILISATEUR (UtilisateurId) 
      , CONSTRAINT INTERROGATION_QUESTIONNAIRE_FK FOREIGN KEY (QuestionnaireId)
            REFERENCES QUESTIONNAIRE (QuestionnaireId)
) ;

CREATE TABLE SELECTION 
(
        UtilisateurId        INT             NOT NULL
      , QuestionnaireId      INT             NOT NULL
      , Iteration            INT             NOT NULL
      , RegleId              INT             NOT NULL
      , PhraseId             INT             NOT NULL
      , Position             INT             NOT NULL      
      , CONSTRAINT SELECTION_PK PRIMARY KEY (UtilisateurId, QuestionnaireId, Iteration, RegleId, PhraseId)
      , CONSTRAINT SELECTION_INTERROGATION_FK FOREIGN KEY (UtilisateurId, QuestionnaireId, Iteration)
            REFERENCES INTERROGATION (UtilisateurId, QuestionnaireId, Iteration) 
      , CONSTRAINT SELECTION_QUESTIONNAIRE_PHRASE_FK FOREIGN KEY (QuestionnaireId, RegleId, PhraseId)
            REFERENCES QUESTIONNAIRE_PHRASE (QuestionnaireId, RegleId, PhraseId)
) ;

En passant, changez le nom des tables comme vous le sentez : par exemple SELECTION renommée en REPONDRE_EXAM. Il faut que vous vous sentiez à l’aise dans vos baskets conceptuelles...

Envoyé par almoha

La valorisation des attributs "Itération" relèvera-t-elle de triggers ?

A priori, oui.

Exemple :


CREATE TRIGGER INTERROGATION_BEFORE_INSERT BEFORE INSERT ON INTERROGATION 
FOR EACH ROW
    BEGIN
        SET new.Iteration = (SELECT COALESCE(MAX(Iteration) + 1, 1)
                             FROM   INTERROGATION
                             WHERE  UtilisateurId = new.UtilisateurId
                               AND  QuestionnaireId = new.QuestionnaireId
                            ) ;
    END
GO

Il y a pas mal de programmation en perspective...

De mon côté, je vais déjà attaquer le calcul du score.

**fsmrel** · 19/10/2015, 18h10

Bonsoir almoha,

Remarque préliminaire.

Table UTILISATEUR : ajout de l’attribut UtilisateurCode (clé alternative), permettant d’accéder aux données de Raoul sans utiliser l’attribut UtilisateurId, invariant, artificiel et en principe caché.


CREATE TABLE UTILISATEUR
(
        UtilisateurId        INT             NOT NULL
      , UtilisateurCode      CHAR(16)        NOT NULL        
      , UtilisateurNom       VARCHAR(32)     NOT NULL         
    , CONSTRAINT UTILISATEUR_PK PRIMARY KEY (UtilisateurId) 
    , CONSTRAINT UTILISATEUR_AK UNIQUE (UtilisateurCode)     
) ;

Je traite maintenant du calcul du score d’un candidat.

Pour obtenir le score d’un candidat, il n’est pas nécessaire de définir un attribut Score pour la table INTERROGATION.

En effet, réduisons l’en-tête de cette table à :

{UtilisateurId, QuestionnaireId, Iteration, DateExamen}

Pour connaître le score de Raoul (UtilisateurCode = 'RV') quand il a répondu pour la 2e fois (Iteration = 2) au questionnaire 'Q002', une requête suffit :


SELECT COUNT(*) * 5 AS Score
FROM   SELECTION AS x JOIN UTILISATEUR AS z ON x.UtilisateurId = z.UtilisateurId
                      JOIN QUESTIONNAIRE AS t ON x.QuestionnaireId = t.QuestionnaireId
                      JOIN PHRASE AS y ON x.RegleId = y.RegleId AND x.PhraseId = y.PhraseId AND x.Position = y.Position
WHERE  UtilisateurCode = 'RV' AND QuestionnaireCode = 'Q002' AND Iteration = 2
GROUP BY x.UtilisateurId, x.QuestionnaireId, x.Iteration 
;

La clause GROUP BY permet de compter les réponses de Raoul. La jointure avec la table PHRASE permet de ne retenir que les bonnes réponses (égalité sur l’attribut Position). Une fois qu’on a compté (COUNT(*)), on multiplie par 5 pour obtenir le score.

En s’inspirant de cette requête, on peut demander le score de n’importe quel candidat, pour n’importe quel questionnaire et n’importe quelle itération. A cet effet, on crée une procédure, appelons-la par exemple ScoreCandidatCalcul :


DELIMITER GO 

DROP PROCEDURE IF EXISTS ScoreCandidatCalcul

GO

CREATE PROCEDURE ScoreCandidatCalcul
(
   IN RaoulCodeIn CHAR(16), QuestionaireCodeIn CHAR(4), IterationIn INT
)

BEGIN
    SELECT COUNT(*) * 5 AS Score
    FROM   SELECTION AS x JOIN UTILISATEUR AS z ON x.UtilisateurId = z.UtilisateurId
                          JOIN QUESTIONNAIRE AS t ON x.QuestionnaireId = t.QuestionnaireId
                          JOIN PHRASE AS y ON x.RegleId = y.RegleId AND x.PhraseId = y.PhraseId AND x.Position = y.Position
    WHERE  UtilisateurCode = RaoulCodeIn AND QuestionnaireCode = QuestionaireCodeIn AND Iteration = IterationIn
    GROUP BY x.UtilisateurId, x.QuestionnaireId, x.Iteration
    ; 

END

GO

DELIMITER ;

Application : pour connaître le score de Raoul quand il a répondu pour la 2e fois au questionnaire 'Q002' :


    CALL ScoreCandidatCalcul('RV', 'Q002', 2) ;

Histoire de généraliser un maximum :

Puisque l’en-tête de la table INTERROGATION est le suivant, c'est-à-dire dépourvu de l’attribut Score :

{UtilisateurId, QuestionnaireId, Iteration, DateExamen}

Alors on peut encore définir une vue, c'est-à-dire une table virtuelle, appelons-la INTERROGATION_SCORE, et dont l’en-tête est le suivant, où cette fois-ci l’attribut Score est présent :

{UtilisateurId, QuestionnaireId, Iteration, DateExamen, Score}

Ou mieux, pour n’avoir affaire qu’aux attributs naturels :

{UtilisateurCode, QuestionnaireCode, Iteration, DateExamen, Score}


CREATE VIEW INTERROGATION_SCORE (UtilisateurCode, QuestionnaireCode, Iteration, Score) AS
SELECT UtilisateurCode, QuestionnaireCode, Iteration, Score
FROM   UTILISATEUR AS u 
       JOIN 
           (SELECT x.UtilisateurId, x.QuestionnaireId, x.Iteration, COUNT(*)*5 AS Score
            FROM   SELECTION AS x JOIN PHRASE AS y ON x.RegleId = y.RegleId AND x.PhraseId = y.PhraseId AND x.Position = y.Position
            GROUP BY UtilisateurId, QuestionnaireId, Iteration) AS t
       ON u.UtilisateurId = t.UtilisateurId
       JOIN QUESTIONNAIRE AS v
       ON v.QuestionnaireId = t.QuestionnaireId
;

Attention ! Cette instruction est valide pour les SGBD normalement constitués, mais MySQL étant mal bâti, il ne sait pas traiter des sous-requêtes présentes au sein d’une vue (il le signale pudiquement ainsi : « Error Code: 1349. View's SELECT contains a subquery in the FROM clause » ).

Pour pallier, de la sous-requête en cause on fait une vue, appelons-la INTERROGATION_T :


CREATE VIEW INTERROGATION_T (UtilisateurId, QuestionnaireId, Iteration, Score) AS
    SELECT x.UtilisateurId, x.QuestionnaireId, x.Iteration, COUNT(*)*5 AS Score
    FROM   SELECTION AS x JOIN PHRASE AS y ON x.RegleId = y.RegleId AND x.PhraseId = y.PhraseId AND x.Position = y.Position
    GROUP BY UtilisateurId, QuestionnaireId, Iteration
;

Et l’on réécrit ainsi le code de définition de la vue INTERROGATION_SCORE :


CREATE VIEW INTERROGATION_SCORE (UtilisateurCode, QuestionnaireCode, Iteration, Score) AS
    SELECT UtilisateurCode, QuestionnaireCode, Iteration, Score
    FROM   UTILISATEUR AS u JOIN INTERROGATION_T AS t ON u.UtilisateurId = t.UtilisateurId
                            JOIN QUESTIONNAIRE AS v ON v.QuestionnaireId = t.QuestionnaireId
;

Pour visualiser le contenu complet de la table virtuelle INTERROGATION_SCORE :


SELECT UtilisateurCode, QuestionnaireCode, Iteration, Score
FROM   INTERROGATION_SCORE
;

A son tour on peut affecter une procédure à cette table virtuelle, permettant de connaître le score de n’importe quel candidat, pour n’importe quel questionnaire et n’importe quelle itération :


DELIMITER GO 

DROP PROCEDURE IF EXISTS ScoreCandidatCalculVariante

GO

CREATE PROCEDURE ScoreCandidatCalculVariante
(
   IN RaoulCodeIn CHAR(16), QuestionaireCodeIn CHAR(4), IterationIn INT
)

BEGIN
    SELECT Score
    FROM   INTERROGATION_SCORE 
    WHERE  UtilisateurCode = RaoulCodeIn AND QuestionnaireCode = QuestionaireCodeIn AND Iteration = IterationIn    
    ;
END

GO

DELIMITER ;

Application : pour connaître le score de Raoul quand il a répondu pour la 2e fois au questionnaire 'Q002' :


    CALL ScoreCandidatCalculVariante('RV', 'Q002', 2) ;

2e partie de l’histoire...

De même qu’un manchot a toujours mal au bras dont il a été amputé, peut-être souffririez-vous de l’absence de l’attribut Score ?

Si vous tenez viscéralement à cet attribut, pas de problème, on le rétablit dans l’en-tête de la table INTERROGATION... :

{UtilisateurId, QuestionnaireId, Iteration, DateExamen, Score}

Il y a deux façons de le mettre à jour :

— En temps réel, c'est-à-dire de façon automatique : dès que Raoul fournit une réponse ;

— En temps différé, c'est-à-dire à la demande.

Calcul du score de Raoul en temps réel : quand a lieu un INSERT affectant la table SELECTION, un trigger ad-hoc met à jour la table INTERROGATION (Score = Score + 5 si la réponse de Raoul est bonne).

Prévoyez-vous qu’une réponse de Raoul puisse être modifiée ? supprimée ? Si oui, il faudra créer des triggers en conséquence, je ne traite ici que des ajouts.

Le trigger pour augmenter de 5 points le score de Raoul en cas de bonne réponse :


DELIMITER GO

DROP TRIGGER IF EXISTS SELECTION_AFTER_INSERT 

GO

CREATE TRIGGER SELECTION_AFTER_INSERT AFTER INSERT ON SELECTION 
FOR EACH ROW
    BEGIN
        UPDATE INTERROGATION
            SET Score = Score + 5 
            WHERE UtilisateurId = new.UtilisateurId
             AND QuestionnaireId = new.QuestionnaireId
             AND Iteration = new.Iteration
             AND new.RegleId = (SELECT regleId FROM PHRASE WHERE regleId = new.regleId and PhraseId = new.PhraseId) 
             AND new.PhraseId = (SELECT PhraseId FROM PHRASE WHERE regleId = new.regleId and PhraseId = new.PhraseId) 
             AND new.position = (SELECT Position FROM PHRASE WHERE regleId = new.regleId and PhraseId = new.PhraseId)
        ;
    END
GO 

DELIMITER ;

Calcul du score de Raoul en temps différé :

Je propose l’utilisation d’une procédure, appelons-la ScoronsJoyeusement, à laquelle on passe le code de Raoul, le code du questionnaire sur lequel il planche et le numéro de l’itération (puisque, rappelons-le, Raoul peut plancher plus d’une fois sur le même questionnaire) :

Appel de la procédure :


CALL ScoronsJoyeusement('RV', 'Q002', 2) ;

Code de la procédure :


DELIMITER GO 

DROP PROCEDURE IF EXISTS ScoronsJoyeusement 

GO

CREATE PROCEDURE ScoronsJoyeusement
(
   IN RaoulCodeIn CHAR(16), QuestionaireCodeIn CHAR(4), IterationIn INT
)

BEGIN
    UPDATE INTERROGATION
        SET Score = (SELECT Score
                     FROM   INTERROGATION_SCORE
                     WHERE  UtilisateurCode = RaoulCodeIn AND QuestionnaireCode = QuestionaireCodeIn AND  Iteration = IterationIn)
            WHERE UtilisateurId = (SELECT UtilisateurId FROM UTILISATEUR WHERE UtilisateurCode = RaoulCodeIn)
              AND QuestionnaireId = (SELECT QuestionnaireId FROM QUESTIONNAIRE WHERE QuestionnaireCode = QuestionaireCodeIn)
              AND Iteration = IterationIn
    ;              
END

GO

DELIMITER ;

A vous de secouer le cocotier (j’espère ne pas m’être planté dans les copier/coller...)

**almoha** · 20/10/2015, 22h07

Bonsoir fsmrel,

Merci. Avant de tester le calcul du score du candidat, j'ai souhaité repartir d'une nouvelle base de données comportant toutes les tables du MLD incluant celles ajoutées dans le message 43. Mais étant confronté à des erreurs (notamment "Error Code: 1215. Impossible d'ajouter des contraintes d'index externe"), je vous serais vraiment reconnaissant si vous pouviez me communiquer le script complet de création des tables SQL ainsi que votre jeu d'essai associé. J'espère que je n'en demande pas trop

**fsmrel** · 21/10/2015, 00h29

Bonsoir almoha,

Le message de code d’erreur 1215 indique manifestement un viol d’intégrité référentielle : présence d’une clé étrangère dans une table T2 référençant une table T1 et contenant des orphelins.

Avant de recréer les tables, avez-vous exécuté un DROP TABLE pour chaque table (dans l’ordre figurant dans le script ci-dessous) ?

Notez que j’utilise une base de données nommée « temp » (cf. instruction USE temp).

Pour ne pas surcharger, je ne traite ici que de la partie « examen ». Si vous avez besoin de la partie « entraînement », dites-le moi.


USE temp ;

DROP VIEW IF EXISTS INTERROGATION_T ;
DROP VIEW IF EXISTS INTERROGATION_SCORE ;

DROP TABLE IF EXISTS SELECTION ;
DROP TABLE IF EXISTS INTERROGATION ;
DROP TABLE IF EXISTS QUESTIONNAIRE_PHRASE ;
DROP TABLE IF EXISTS QUESTIONNAIRE ;
DROP TABLE IF EXISTS PHRASES_NB ;

DROP TABLE IF EXISTS REPONDRE ;
DROP TABLE IF EXISTS CHOISIR ;

DROP TABLE IF EXISTS UTILISATEUR ;
DROP TABLE IF EXISTS CORRIGE ;
DROP TABLE IF EXISTS PHRASE ;
DROP TABLE IF EXISTS DIFFICULTE ;
DROP TABLE IF EXISTS COMPOSITION ;
DROP TABLE IF EXISTS REGLE ;
DROP TABLE IF EXISTS ELEMENT ;
DROP TABLE IF EXISTS TYPE_ELEMENT ;

-------------------------------------------------------------------------------------------------

CREATE TABLE TYPE_ELEMENT 
(
        TypeElementId        INT             NOT NULL
      , TypeElementLibelle   VARCHAR(16)     NOT NULL
    , CONSTRAINT TYPE_ELEMENT_PK PRIMARY KEY (TypeElementId)
) ;

CREATE TABLE ELEMENT 
(
        ElementId            INT             NOT NULL
      , TypeElementId        INT             NOT NULL
      , ElementTexte         VARCHAR(64)     NOT NULL
    , CONSTRAINT ELEMENT_PK PRIMARY KEY (ElementId)
    , CONSTRAINT ELEMENT_TYPE_ELEMENT_FK FOREIGN KEY (TypeElementId)
      REFERENCES TYPE_ELEMENT (TypeElementId) ON DELETE CASCADE
) ;

CREATE TABLE COMPOSITION 
(
        ElementComposantId       INT             NOT NULL
      , ElementComposeId         INT             NOT NULL
    , CONSTRAINT COMPOSITION_ELEMENT_COMPOSANT_FK FOREIGN KEY (ElementComposantId)
      REFERENCES ELEMENT (ElementId) ON DELETE CASCADE
    , CONSTRAINT COMPOSITION_ELEMENT_COMPOSE_FK FOREIGN KEY (ElementComposeId)
      REFERENCES ELEMENT (ElementId)      
) ;

CREATE TABLE REGLE 
(
        RegleId              INT             NOT NULL
      , ElementId            INT             NOT NULL        
      , RegleTexte           VARCHAR(64)     NOT NULL
      , NbPhrases            INT             NOT NULL
    , CONSTRAINT REGLE_PK PRIMARY KEY (RegleId)
    , CONSTRAINT REGLE_ELEMENT_FK FOREIGN KEY (ElementId)
      REFERENCES ELEMENT (ElementId)
 ) ;
 
 CREATE TABLE DIFFICULTE 
(
        DifficulteId         INT             NOT NULL
      , DifficulteNiveau     INT             NOT NULL DEFAULT 1	
      , DifficulteTexte      VARCHAR(32)     NOT NULL
    , CONSTRAINT DIFFICULTE_PK PRIMARY KEY (DifficulteId)
    , CONSTRAINT DIFFICULTE_AK UNIQUE (DifficulteNiveau)    
) ;

CREATE TABLE PHRASE 
(
        RegleId              INT             NOT NULL
      , PhraseId             INT             NOT NULL
      , DifficulteId         INT             NOT NULL
      , Position             INT             NOT NULL
      , Texte                VARCHAR(64)     NOT NULL
    , CONSTRAINT PHRASE_PK PRIMARY KEY (RegleId, PhraseId)
    , CONSTRAINT PHRASE_AK UNIQUE (RegleId, PhraseId, DifficulteId)
    , CONSTRAINT PHRASE_REGLE_FK FOREIGN KEY (RegleId)
      REFERENCES REGLE (RegleId)
    , CONSTRAINT PHRASE_DIFFICULTE_FK FOREIGN KEY (DifficulteId)
      REFERENCES DIFFICULTE (DifficulteId)
) ;

CREATE TABLE PHRASES_NB 
(
        PhrasesNbId          INT             NOT NULL
      , PhrasesNb            INT             NOT NULL
    , CONSTRAINT PHRASES_NB_PK PRIMARY KEY (PhrasesNbId)
    , CONSTRAINT PHRASES_NB_AK UNIQUE (PhrasesNb)
) ;

CREATE TABLE QUESTIONNAIRE 
(
        QuestionnaireId      INT             NOT NULL
      , QuestionnaireCode    CHAR(4)         NOT NULL
      , PhrasesNbId          INT             NOT NULL
    , CONSTRAINT QUESTIONNAIRE_PK PRIMARY KEY (QuestionnaireId)
    , CONSTRAINT QUESTIONNAIRE_AK UNIQUE (QuestionnaireCode)
    , CONSTRAINT QUESTIONNAIRE_PHRASES_NB_FK FOREIGN KEY (PhrasesNbId)
          REFERENCES PHRASES_NB (PhrasesNbId)
) ;

CREATE TABLE QUESTIONNAIRE_PHRASE 
(
        QuestionnaireId      INT             NOT NULL
      , RegleId              INT             NOT NULL
      , PhraseId             INT             NOT NULL
      , DifficulteId         INT             NOT NULL
    , CONSTRAINT QUESTIONNAIRE_PHRASE_PK PRIMARY KEY (QuestionnaireId, RegleId, PhraseId)
    , CONSTRAINT QUESTIONNAIRE_PHRASE_PHRASE_FK FOREIGN KEY (RegleId, PhraseId, DifficulteId)
          REFERENCES PHRASE (RegleId, PhraseId, DifficulteId)
    , CONSTRAINT QUESTIONNAIRE_PHRASE_QUESTIONNAIRE_FK FOREIGN KEY (QuestionnaireId)
          REFERENCES QUESTIONNAIRE (QuestionnaireId) ON DELETE CASCADE
) ;

CREATE TABLE UTILISATEUR
(
        UtilisateurId        INT             NOT NULL
      , UtilisateurCode      CHAR(16)        NOT NULL        
      , UtilisateurNom       VARCHAR(32)     NOT NULL         
    , CONSTRAINT UTILISATEUR_PK PRIMARY KEY (UtilisateurId) 
    , CONSTRAINT UTILISATEUR_AK UNIQUE (UtilisateurCode)     
) ;

CREATE TABLE INTERROGATION 
(
        UtilisateurId        INT             NOT NULL
      , QuestionnaireId      INT             NOT NULL
      , Iteration            INT             NOT NULL DEFAULT 0
      , DateExamen           DATE            NOT NULL
      , Score                INT             NOT NULL DEFAULT 0
      , CONSTRAINT INTERROGATION_PK PRIMARY KEY (UtilisateurId, QuestionnaireId, Iteration)
      , CONSTRAINT INTERROGATION_UTILISATEUR_FK FOREIGN KEY (UtilisateurId)
            REFERENCES UTILISATEUR (UtilisateurId) 
      , CONSTRAINT INTERROGATION_QUESTIONNAIRE_FK FOREIGN KEY (QuestionnaireId)
            REFERENCES QUESTIONNAIRE (QuestionnaireId)
) ;

CREATE TABLE SELECTION 
(
        UtilisateurId        INT             NOT NULL
      , QuestionnaireId      INT             NOT NULL
      , Iteration            INT             NOT NULL
      , RegleId              INT             NOT NULL
      , PhraseId             INT             NOT NULL
      , Position             INT             NOT NULL      
      , CONSTRAINT SELECTION_PK PRIMARY KEY (UtilisateurId, QuestionnaireId, Iteration, RegleId, PhraseId)
      , CONSTRAINT SELECTION_INTERROGATION_FK FOREIGN KEY (UtilisateurId, QuestionnaireId, Iteration)
            REFERENCES INTERROGATION (UtilisateurId, QuestionnaireId, Iteration) 
      , CONSTRAINT SELECTION_QUESTIONNAIRE_PHRASE_FK FOREIGN KEY (QuestionnaireId, RegleId, PhraseId)
            REFERENCES QUESTIONNAIRE_PHRASE (QuestionnaireId, RegleId, PhraseId)
) ;

COMMIT ;

DELIMITER GO

DROP TRIGGER IF EXISTS INTERROGATION_BEFORE_INSERT ;

GO

CREATE TRIGGER INTERROGATION_BEFORE_INSERT BEFORE INSERT ON INTERROGATION 
FOR EACH ROW
    BEGIN
        SET new.Iteration = (SELECT COALESCE(MAX(Iteration) + 1, 1)
                             FROM   INTERROGATION
                             WHERE  UtilisateurId = new.UtilisateurId
                               AND  QuestionnaireId = new.QuestionnaireId
                            ) ;        
    END
GO 

DELIMITER ;

INSERT INTO TYPE_ELEMENT (TypeElementId, TypeElementLibelle) VALUES 
    (1, 'Partie'), (2, 'Sous-partie'), (3, 'Rubrique') ;  -- , (4, 'Règle') ;
    
INSERT INTO ELEMENT (ElementId, TypeElementId, ElementTexte) VALUES
    (1, 1, 'Formes et accords du verbe')
  , (2, 1, 'Formes et accords du nom, de l''adjectif et de l''adverbe')
  , (3, 1, 'Orthographe lexicale, signes graphiques et syntaxe')
  , (4, 1, 'Ces mots que l''on confond')
  , (5, 2, 'Le verbe : ses formes')
  , (6, 2, 'Syntaxe')
  , (7, 3, 'L''infinitif')
  , (8, 3, 'Les noms : leur féminin, leur pluriel')
  , (9, 3, 'L''imparfait du subjonctif')    
  , (10, 2, 'Le verbe, ses pièges')
  , (14, 3, 'Rubrique-à-brac')
  , (15, 2, 'sous-partie, piège à AK de PILE')  
  , (91, 1, 'Piège à récursivité')   , (92, 1, 'Piège à récursivité')
  , (93, 2, 'Piège à récursivité')
 ;
 
INSERT INTO COMPOSITION  (ElementComposantId, ElementComposeId) VALUES
     (5,1), (6, 3), (7, 5), (8, 2), (9, 5), (10, 1), (93, 92), (14, 1), (15, 1) 
     ;
 
INSERT INTO REGLE (RegleId, ElementId, RegleTexte, NbPhrases) VALUES
    (1, 8, 'Le pluriel des noms : cas généraux', 5)
  , (2, 6, 'est-ce que', 15)
  , (3, 4, 'a ; à', 2)
  , (4, 4, 'ou ; où', 2)  
  , (5, 4, 'or ; hors', 4)  
  , (6, 9, 'La 3e personne du singulier', 5)
  , (7, 9, 'La 3e personne du pluriel', 3) 
  , (8, 5, 'Une règle de la forme spéciale du verbe', 2)
  , (9, 1, 'Accord dissonant du verbe', 2)
  , (10, 14, 'règle-à-brac',5) 
  , (11, 10, 'rg11, enfant de SP10', 5)
  , (12, 10, 'rg12, enfant de SP10', 5)
  , (13, 10, 'rg13, enfant de SP10', 5)
  , (14, 10, 'rg14, enfant de SP10', 5)
  , (15, 1, 'règle, piège à AK de PILE', 2)
  ;

INSERT INTO DIFFICULTE (DifficulteId, DifficulteNiveau, DifficulteTexte) VALUES
    (1, 1, 'Niveau 1'), (2, 2, 'Niveau 2'), (3, 3, 'Niveau 3') ;  
 
 -- SELECT * FROM DIFFICULTE ;

INSERT INTO PHRASE (RegleId, PhraseId, DifficulteId, Position, Texte) VALUES
    (1, 1, 1, 5, 'rg1, ph1, le petit chat est morts')
  , (1, 2, 2, 5, 'rg1, ph2, il a un petit soucis')
  , (1, 3, 1, 3, 'rg1, ph3, des yeux marrons')
  , (1, 4, 2, 4, 'rg1, ph4, il est huit heure')
  , (1, 5, 3, 0, 'rg1, ph5, Huit cent deux kilos')
  , (3, 1, 2, 0, 'rg3, ph1 , Je vais à Paris')
  , (3, 2, 2, 0, 'rg3, ph2, Pierre a faim')
  , (3, 3, 3, 0, 'rg3, ph3, Ce crayon est a moi') 
  , (4, 1, 1, 2, 'rg4, Il et fort et agile')
  , (4, 2, 1, 0, 'rg4, Elle est grande')
  , (4, 3, 3, 0, 'rg4, Il aime le cuissot de chevreuil')
  , (4, 4, 3, 5, 'rg4, Il aime aussi le cuissot de veau')
  , (4, 5, 3, 0, 'rg4, C''est une imbécillité')
  , (5, 1, 2, 0, 'rg5, ph1')
  , (5, 2, 2, 0, 'rg5, ph2')
  , (6, 1, 2, 0, 'rg6, ph1, Qu''il aimât cela !')
  , (6, 2, 3, 0, 'rg6, ph2, Qu''il n''aimat pas cela !')
  , (6, 3, 2, 0, 'rg6, ph3, Qu''il eût aimé')
  , (7, 1, 3, 0, 'rg7, ph1, Qu''ils cinématographassent cela !')  
  , (7, 2, 3, 2, 'rg7, ph2, Qu''ils n''aimassent pas cela !')
  , (7, 3, 2, 0, 'rg7, ph3, Fallait-il que je vous aimasse')
  , (7, 4, 2, 0, 'rg7, ph4, et que je vous idolâtrasse')
  , (7, 5, 2, 0, 'rg7, ph5, pour que vous m''assassinassiez')
  , (8, 1, 2, 0, 'rg8, ph1')
  , (8, 2, 2, 0, 'rg8, ph2')
  , (9, 1, 2, 0, 'rg9, ph1')
  , (9, 2, 1, 0, 'rg9, ph2')
  , (9, 3, 2, 0, 'rg9, ph3')
  , (10, 1, 2, 0, 'rg10, ph1')
  , (10, 2, 2, 0, 'rg10, ph2')
  , (10, 3, 1, 5, 'Le Biglotron est un extrordinaire appareil')
  , (10, 4, 3, 0, 'Le flugdug prenant appui sur la muffée du connecteur à rustine')  
  , (11, 1, 2, 0, 'rg11, ph1')
  , (11, 2, 2, 0, 'rg11, ph2')
  , (12, 1, 2, 0, 'rg12, ph1')
  , (12, 2, 2, 0, 'rg12, ph2')
  , (13, 1, 2, 0, 'rg13, ph1')
  , (13, 2, 2, 0, 'rg13, ph2')
  , (14, 1, 2, 0, 'rg14, ph1')
  , (14, 2, 2, 0, 'rg14, ph2')
;
 SELECT *, rand() as alea FROM PHRASE order by DifficulteId, alea ; 

INSERT INTO PHRASES_NB (PhrasesNbId, PhrasesNb) VALUES
    (1, 50), (2, 100), (3, 200) ;

INSERT INTO QUESTIONNAIRE (QuestionnaireId, QuestionnaireCode, PhrasesNbId)
    VALUES 
    (1, 'Q001', 1) 
  , (2, 'Q002', 2)
  , (3, 'Q003', 1)  
; 

INSERT INTO QUESTIONNAIRE_PHRASE (QuestionnaireId, RegleId, PhraseId, DifficulteId)
    VALUES 
    (1, 1, 1, 1) , (1, 1, 2, 2), (1, 1, 3, 1), (1, 10, 3, 1), (1, 10, 4, 3)  
  , (2, 1, 1, 1) , (2, 1, 2, 2), (2, 1, 3, 1), (2, 10, 3, 1), (2, 10, 4, 3)  
; 

SELECT *, RAND() AS Alea FROM QUESTIONNAIRE_PHRASE ORDER BY QuestionnaireId, DifficulteId, Alea ;

INSERT INTO UTILISATEUR (UtilisateurId, UtilisateurCode, UtilisateurNom) VALUES
    (1, 'FN', 'Fernand'), (2, 'RV', 'Raoul'), (3, 'PV', 'Paul'), (4, 'almoha', 'Alain'), (5, 'fsmrel', 'François')
;

CREATE VIEW INTERROGATION_T (UtilisateurId, QuestionnaireId, Iteration, Score) AS
    SELECT x.UtilisateurId, x.QuestionnaireId, x.Iteration, COUNT(*)*5 AS Score
    FROM   SELECTION AS x JOIN PHRASE AS y ON x.RegleId = y.RegleId AND x.PhraseId = y.PhraseId AND x.Position = y.Position
    GROUP BY UtilisateurId, QuestionnaireId, Iteration
;


CREATE VIEW INTERROGATION_SCORE (UtilisateurCode, QuestionnaireCode, Iteration, Score) AS
    SELECT UtilisateurCode, QuestionnaireCode, Iteration, Score
    FROM   UTILISATEUR AS u JOIN INTERROGATION_T AS t ON u.UtilisateurId = t.UtilisateurId
                            JOIN QUESTIONNAIRE AS v ON v.QuestionnaireId = t.QuestionnaireId
;

DELIMITER GO

DROP TRIGGER IF EXISTS SELECTION_AFTER_INSERT 

GO

CREATE TRIGGER SELECTION_AFTER_INSERT AFTER INSERT ON SELECTION 
FOR EACH ROW
    BEGIN
        UPDATE INTERROGATION
            SET Score = Score + 5 
                 WHERE UtilisateurId = new.UtilisateurId
                   AND QuestionnaireId = new.QuestionnaireId
                   AND Iteration = new.Iteration
                   AND new.RegleId = (SELECT RegleId FROM PHRASE WHERE RegleId = new.RegleId and PhraseId = new.PhraseId) 
                   AND new.PhraseId = (SELECT PhraseId FROM PHRASE WHERE RegleId = new.RegleId and PhraseId = new.PhraseId) 
                   AND new.Position = (SELECT Position FROM PHRASE WHERE RegleId = new.RegleId and PhraseId = new.PhraseId)
 ;
    END
GO 

DELIMITER ;

INSERT INTO INTERROGATION (UtilisateurId, QuestionnaireId, DateExamen) VALUES

    (1, 1, '2015-10-14'), (1, 1, '2015-10-16')   -- Fernand
  , (2, 1, '2015-10-15'), (2, 1, '2015-10-15')   -- Raoul
  , (2, 2, '2015-10-17'), (2, 2, '2015-10-18')   -- Raoul  
;

SELECT *, '' AS R1 FROM INTERROGATION ;

INSERT INTO SELECTION (UtilisateurId, QuestionnaireId, Iteration, RegleId, PhraseId, Position) VALUES

    (1, 1, 1, 1, 2, 5), (1, 1, 1, 10, 3, 5), (1, 1, 1, 10, 4, 7)   -- Fernand
  , (2, 1, 1, 1, 2, 5), (2, 1, 1, 10, 3, 5), (2, 1, 1, 10, 4, 7)   -- Raoul
  , (2, 2, 1, 1, 2, 0), (2, 2, 1, 10, 3, 0), (2, 2, 1, 10, 4, 0)   -- Raoul
  , (2, 2, 2, 1, 2, 5), (2, 2, 2, 10, 3, 5), (2, 2, 2, 10, 4, 0)   -- Raoul
;

SELECT * FROM SELECTION ;

SELECT * from INTERROGATION_T ;

-- ------------------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------------

SELECT *, '' AS 'select simple' FROM   SELECTION AS x JOIN PHRASE AS y ON x.RegleId = y.RegleId AND x.PhraseId = y.PhraseId ;


SELECT x.UtilisateurId, x.QuestionnaireId, x.Iteration, COUNT(*)*5 AS Score
FROM   SELECTION AS x JOIN PHRASE AS y ON x.RegleId = y.RegleId AND x.PhraseId = y.PhraseId AND x.Position = y.Position
GROUP BY UtilisateurId, QuestionnaireId, Iteration 
;

-- On doit remplacer un subquery par une vue ! Pas très orthogonal, MySQL !
-- Error Code: 1349. View's SELECT contains a subquery in the FROM clause

-- CREATE VIEW INTERROGATION_SCORE (UtilisateurCode, QuestionnaireCode, Iteration, Score) AS
SELECT UtilisateurCode, QuestionnaireCode, Iteration, Score as ScoreTruc 
FROM   UTILISATEUR AS u 
       JOIN 
           (SELECT x.UtilisateurId, x.QuestionnaireId, x.Iteration, COUNT(*)*5 AS Score
            FROM   SELECTION AS x JOIN PHRASE AS y ON x.RegleId = y.RegleId AND x.PhraseId = y.PhraseId AND x.Position = y.Position
            GROUP BY UtilisateurId, QuestionnaireId, Iteration) AS t
       ON u.UtilisateurId = t.UtilisateurId
       JOIN QUESTIONNAIRE AS v ON v.QuestionnaireId = t.QuestionnaireId       
;

-- Score de Raoul au questionnaire 2, 2e itération

SELECT COUNT(*) * 5 AS ScoreRaoulHimself
FROM   SELECTION AS x JOIN UTILISATEUR AS z ON x.UtilisateurId = z.UtilisateurId
                      JOIN QUESTIONNAIRE AS t ON x.QuestionnaireId = t.QuestionnaireId
                      JOIN PHRASE AS y ON x.RegleId = y.RegleId AND x.PhraseId = y.PhraseId AND x.Position = y.Position
WHERE  UtilisateurCode = 'RV' AND QuestionnaireCode = 'Q002' AND Iteration = 2
GROUP BY x.UtilisateurId, x.QuestionnaireId, x.Iteration
;

-- Généralisation à un candidat quelconque

DELIMITER GO 

DROP PROCEDURE IF EXISTS ScoreCandidatCalcul 

GO

CREATE PROCEDURE ScoreCandidatCalcul
(
   IN RaoulCodeIn CHAR(16), QuestionaireCodeIn CHAR(4), IterationIn INT
)

BEGIN
    SELECT COUNT(*) * 5 AS ScoreRaoul
    FROM   SELECTION AS x JOIN UTILISATEUR AS z ON x.UtilisateurId = z.UtilisateurId
                          JOIN QUESTIONNAIRE AS t ON x.QuestionnaireId = t.QuestionnaireId
                          JOIN PHRASE AS y ON x.RegleId = y.RegleId AND x.PhraseId = y.PhraseId AND x.Position = y.Position
    WHERE  UtilisateurCode = RaoulCodeIn AND QuestionnaireCode = QuestionaireCodeIn AND Iteration = IterationIn
    GROUP BY x.UtilisateurId, x.QuestionnaireId, x.Iteration
;

END

GO

DELIMITER ;

CALL ScoreCandidatCalcul('RV', 'Q002', 2) ;

--- Variante

SELECT *, '' AS ScoreVarianteAll
    FROM   INTERROGATION_SCORE ;

DELIMITER GO 

DROP PROCEDURE IF EXISTS ScoreCandidatCalculVariante

GO

CREATE PROCEDURE ScoreCandidatCalculVariante
(
   IN RaoulCodeIn CHAR(16), QuestionaireCodeIn CHAR(4), IterationIn INT
)

BEGIN
    SELECT Score
    FROM   INTERROGATION_SCORE 
    WHERE  UtilisateurCode = RaoulCodeIn AND QuestionnaireCode = QuestionaireCodeIn AND Iteration = IterationIn    
    ;
END

GO

DELIMITER ;

CALL ScoreCandidatCalculVariante('RV','Q002', 2) ;

SELECT * FROM INTERROGATION_SCORE ;

SELECT * FROM INTERROGATION ;

DELIMITER GO 

DROP PROCEDURE IF EXISTS ScoronsJoyeusement 

GO

CREATE PROCEDURE ScoronsJoyeusement
(
   IN RaoulCodeIn CHAR(16), QuestionaireCodeIn CHAR(4), IterationIn INT
)

BEGIN
    UPDATE INTERROGATION
        SET Score = (SELECT Score
                     FROM   INTERROGATION_SCORE
                     WHERE  UtilisateurCode = RaoulCodeIn AND QuestionnaireCode = QuestionaireCodeIn AND  Iteration = IterationIn)
            WHERE UtilisateurId = (SELECT UtilisateurId FROM UTILISATEUR WHERE UtilisateurCode = RaoulCodeIn)
              AND QuestionnaireId = (SELECT QuestionnaireId FROM QUESTIONNAIRE WHERE QuestionnaireCode = QuestionaireCodeIn)
              AND Iteration = IterationIn
;              
END

GO

DELIMITER ;

UPDATE INTERROGATION SET Score = 0 ;

CALL ScoronsJoyeusement('RV', 'Q002', 1) ;

SELECT *, '' AS 'Proc Scorons 1' FROM INTERROGATION ;

CALL ScoronsJoyeusement('RV', 'Q002', 2) ;

SELECT *, '' AS 'Proc Scorons 2' FROM INTERROGATION ;

N.B. Je suis en train de mettre au point la partie qui traite de la fabrication des questionnaires.

**almoha** · 21/10/2015, 22h20

Bonsoir fsmrel,

Pour ne pas surcharger, je ne traite ici que de la partie « examen ». Si vous avez besoin de la partie « entraînement », dites-le moi.

Merci pour le script de la partie « examen ». J'ai pu reconstruire ma base de tests. Je suis preneur effectivement du script de la partie « entraînement »

Pour revenir à la partie « examen », mes essais sont concluants. Notez que j'ai privilégié la solution faisant l'impasse sur l'attribut "Score", ce sans regret

Je suis en train de mettre au point la partie qui traite de la fabrication des questionnaires.

Merci !

**fsmrel** · 22/10/2015, 03h57

Bonsoir almoha,

Envoyé par almoha

j'ai privilégié la solution faisant l'impasse sur l'attribut "Score", ce sans regret

Je comprends !

Pour la partie « entraînement », je remettrai de l’ordre un peu plus tard, car c’est un peu le b... dans mes fichiers scripts...

Allons-y pour la partie qui traite de la production de questionnaires. Dans le script qui suit, je n’ai pas pris en compte les tables qui ne sont pas parties prenantes, en conséquence n’y figurent que les suivantes :

REGLE ;
DIFFICULTE ;
PHRASE ;
PHRASES_NB ;
QUESTIONNAIRE ;
QUESTIONNAIRE_PHRASE.

Création des tables et chargement par INSERT de celles qui peuvent l’être :


USE temp2 ;

DROP TABLE IF EXISTS QUESTIONNAIRE_PHRASE ;
DROP TABLE IF EXISTS QUESTIONNAIRE ;
DROP TABLE IF EXISTS PHRASES_NB ;

DROP TABLE IF EXISTS PHRASE ;
DROP TABLE IF EXISTS DIFFICULTE ;
DROP TABLE IF EXISTS REGLE ;


CREATE TABLE REGLE 
(
        RegleId              INT             NOT NULL
      , ElementId            INT             NOT NULL DEFAULT 1        
      , RegleTexte           VARCHAR(16)     NOT NULL
      , NbPhrases            INT             NOT NULL DEFAULT 0
    , CONSTRAINT REGLE_PK PRIMARY KEY (RegleId)    
 ) 
     MAX_ROWS = 200
 ;


  CREATE TABLE DIFFICULTE 
(
        DifficulteId         INT             NOT NULL
      , DifficulteNiveau     INT             NOT NULL DEFAULT 1	
      , DifficulteTexte      VARCHAR(32)     NOT NULL
    , CONSTRAINT DIFFICULTE_PK PRIMARY KEY (DifficulteId)
    , CONSTRAINT DIFFICULTE_AK UNIQUE (DifficulteNiveau)    
) ;

INSERT INTO DIFFICULTE (DifficulteId, DifficulteNiveau, DifficulteTexte) VALUES
    (1, 1, 'niveau 1'), (2, 2, 'niveau 2'), (3, 3, 'niveau 3') ;


CREATE TABLE PHRASE 
(
        RegleId              INT             NOT NULL
      , PhraseId             INT             NOT NULL
      , DifficulteId         INT             NOT NULL
      , Position             INT             NOT NULL DEFAULT 0
      , Texte                VARCHAR(64)     NOT NULL
    , CONSTRAINT PHRASE_PK PRIMARY KEY (RegleId, PhraseId)
    , CONSTRAINT PHRASE_AK UNIQUE (RegleId, PhraseId, DifficulteId)
    , CONSTRAINT PHRASE_REGLE_FK FOREIGN KEY (RegleId)
      REFERENCES REGLE (RegleId)
    , CONSTRAINT PHRASE_DIFFICULTE_FK FOREIGN KEY (DifficulteId)
      REFERENCES DIFFICULTE (DifficulteId)
) 
    MAX_ROWS = 5000
;


CREATE TABLE PHRASES_NB 
(
        PhrasesNbId          INT             NOT NULL
      , PhrasesNb            INT             NOT NULL
    , CONSTRAINT PHRASES_NB_PK PRIMARY KEY (PhrasesNbId)
    , CONSTRAINT PHRASES_NB_AK UNIQUE (PhrasesNb)
) ;


INSERT INTO PHRASES_NB (PhrasesNbId, PhrasesNb) VALUES
    (1, 50), (2, 100), (3, 200) ;

CREATE TABLE QUESTIONNAIRE 
(
        QuestionnaireId      INT             NOT NULL
      , QuestionnaireCode    CHAR(4)         NOT NULL
      , PhrasesNbId          INT             NOT NULL
    , CONSTRAINT QUESTIONNAIRE_PK PRIMARY KEY (QuestionnaireId)
    , CONSTRAINT QUESTIONNAIRE_AK UNIQUE (QuestionnaireCode)
    , CONSTRAINT QUESTIONNAIRE_PHRASES_NB_FK FOREIGN KEY (PhrasesNbId)
          REFERENCES PHRASES_NB (PhrasesNbId)
) ;


INSERT INTO QUESTIONNAIRE (QuestionnaireId, QuestionnaireCode, PhrasesNbId)
    VALUES 
    (1, 'Q001', 1) 
  , (2, 'Q002', 2)
  , (3, 'Q003', 1)  
  , (4, 'Q004', 3)   
; 

CREATE TABLE QUESTIONNAIRE_PHRASE 
(
        QuestionnaireId      INT             NOT NULL
      , RegleId              INT             NOT NULL
      , PhraseId             INT             NOT NULL
      , DifficulteId         INT             NOT NULL
    , CONSTRAINT QUESTIONNAIRE_PHRASE_PK PRIMARY KEY (QuestionnaireId, RegleId, PhraseId)
    , CONSTRAINT QUESTIONNAIRE_PHRASE_PHRASE_FK FOREIGN KEY (RegleId, PhraseId, DifficulteId)
          REFERENCES PHRASE (RegleId, PhraseId, DifficulteId)
    , CONSTRAINT QUESTIONNAIRE_PHRASE_QUESTIONNAIRE_FK FOREIGN KEY (QuestionnaireId)
          REFERENCES QUESTIONNAIRE (QuestionnaireId) ON DELETE CASCADE
) ;

Pour arriver à voir à peu près ce qui se passe dans la génération des lignes de la table QUESTIONNAIRE_PHRASE, j’ai besoin d’une table de règles et d’une table de phrases relativement conséquentes, je crée donc des jeux d’essai à cet effet, mais dont le contenu est arbitraire (l’intégrité référentielle est quand même garantie !)

Génération d’un jeu d’essai d’une centaine de règles :


DELIMITER GO

-- ----------------------------------------------------
-- Procédure de création d'un jeu d'essai de règles,
-- En entrée : NbReglesIn = nombre de règles à créer.
--
-- Les attributs ElementId et NbPhrasesId sont à 0,
-- en effet je n’en ai pas besoin pour la suite.
-- ----------------------------------------------------

DROP PROCEDURE IF EXISTS GenerationDesRegles 

GO

CREATE PROCEDURE GenerationDesRegles (IN NbReglesIn INT)

BEGIN
    SET @Kount = 1 ;
    
    REPEAT
        SET @Texte = CONCAT('règle ', @Kount) ;
        INSERT INTO REGLE (RegleId, ElementId, RegleTexte, NbPhrases) VALUES (@Kount, 0, @Texte, 0) ;
        SET @Kount = @Kount + 1 ;
    
	UNTIL @Kount > NbReglesIn 
    END REPEAT ;

END

GO

DELIMITER ;

-- ------------------------------------------------------
-- C'est parti pour créer 100 règles
-- ------------------------------------------------------

CALL GenerationDesRegles(100) ;  --  100 règles

SELECT * FROM REGLE LIMIT 10 ;

Génération d’un jeu d’essai d’environ 2000 phrases. Pour cela je crée une routine GenerationDesPhrases, que j’appelle 3 fois : une fois par niveau de difficulté, histoire de produire :

1100 phrases de niveau 1, à raison de 20 phrases par règle ;

700 phrases de niveau 2, à raison de 35 phrases par règle ;

195 phrases de niveau 3, à raison de 15 phrases par règle.


DELIMITER GO

DROP PROCEDURE IF EXISTS GenerationDesPhrases 

GO

-- -----------------------------------------------------------------------------------------
-- Procédure de création des phrases
--
-- En entrée : Niveau = niveau de difficulté
--             NbPhrasesParRegle = nombre de phrases par règle
--             NbPhrases = nombre de phrases à créer pour le niveau
--             de difficulté traité.
--
-- -----------------------------------------------------------------------------------------

CREATE PROCEDURE GenerationDesPhrases (IN Niveau INT, NbPhrasesParRegle INT, NbPhrases INT)

BEGIN
    -- -----------------------------------------------------------------------
    -- L'identifiant de la prochaine règle à prendre en compte a pour valeur
    -- la dernière valeur calculée + 1 
    -- -----------------------------------------------------------------------
    SET @RegleId = (SELECT COALESCE(MAX(RegleId) + 1, 1) FROM PHRASE) ; 
    
    -- -----------------------------------------------------------------------
    -- Nombre de phrases pour la règle en cours
    -- -----------------------------------------------------------------------
   
    SET @NbRegles = CEIL(NbPhrases / NbPhrasesParRegle) ;  -- 700/35 = 20
    
    -- -----------------------------------------------------------------------
    -- Plus grand identifiant à produire pour la règle en cours
    -- -----------------------------------------------------------------------
    
    SET @RegleIdMax = @RegleId + @NbRegles - 1 ;
    
    -- -----------------------------------------------------------------------
    -- On génère les phrases
    -- -----------------------------------------------------------------------
    
    REPEAT
        SET @PhraseId = 1 ;
        REPEAT
        -- --------------------------------------------------
        -- génération des phrases pour la règle en cours
        -- --------------------------------------------------
            SET @Texte = CONCAT('règle ', @RegleId, ', phrase ',  @PhraseId) ;
            INSERT INTO PHRASE (RegleId, PhraseId, DifficulteId, Position, Texte) 
                VALUES (@RegleId, @PhraseId, Niveau, 0,  @Texte) ;
            SET @PhraseId = @PhraseId + 1 ;    
        UNTIL @PhraseId > NbPhrasesParRegle 
        END REPEAT ;
        -- --------------------------------------
        -- On passe à la règle suivante
        -- --------------------------------------
        SET @RegleId = @RegleId + 1 ;
    
    UNTIL @RegleId > @RegleIdMax 
    END REPEAT ;
   
END

GO

DELIMITER ;

CALL GenerationDesPhrases(1, 20, 1100) ;  -- niveau 1, 20 phrases par règle, 1100 phrases (1100/20 = 55 règles)

SELECT COUNT(*) AS NbPhrases FROM PHRASE ;

CALL GenerationDesPhrases(2, 35, 700) ;  -- niveau 2, 35 phrases par règle, 700 phrases (700/35 = 20 règles)

SELECT COUNT(*) AS NbPhrases FROM PHRASE ;

CALL GenerationDesPhrases(3, 15, 195) ;  -- niveau 3, 15 phrases par règle, 195 phrases (195/15 = 13 règles)

SELECT COUNT(*) AS NbPhrases FROM PHRASE ;

SELECT * FROM PHRASE WHERE DifficulteId = 3 ;

On en vient à la génération de lignes de la table QUESTIONNAIRE_PHRASE. Pour cela, on utilise la routine GenerationRandom qui permet de générer ces lignes pour un questionnaire donné (un seul questionnaire donc à la fois).


-- -----------------------------------------------------------------------------------
-- Génération des lignes de la table QUESTIONNAIRE_PHRASE, en mode random.
--
-- En entrée : QuestionnaireCodeIn = le questionnaire pour lequel on veut créer des
--             lignes dans la table QUESTIONNAIRE_PHRASE.
-- -----------------------------------------------------------------------------------
DELIMITER GO

DROP PROCEDURE IF EXISTS GenerationRandom 

GO

CREATE PROCEDURE GenerationRandom (IN QuestionnaireCodeIn VARCHAR(8))

BEGIN

    DECLARE theKount INT ;       -- prendra la valeur 50 ou 100 ou 200 selon le questionnaire50 ou 100 ou 200
    DECLARE theNbPhrases INT ;   -- nombre de lignes de la table PHRASE 
    DECLARE theLimit INT  ;      -- pourcentage de phrases par niveau de difficulté. Exemple, pour un paquet de 50 phrases : 17, 18, 15 
    DECLARE theQuestionnaireId INT ;  -- identifiant du questionnaire    

    -- ----------------------------------------------------------------
    -- Nb de phrases pour le questionnaire à traiter : 50, 100 , 200
    -- ----------------------------------------------------------------
    SET theKount = (SELECT PhrasesNb FROM QUESTIONNAIRE AS x JOIN PHRASES_NB AS y ON x.PhrasesNbId = y.PhrasesNbId AND QuestionnaireCode = QuestionnaireCodeIn) ;
    
    -- ----------------------------------------------------------------
    -- Nombre de total phrases dont on dispose (table PHRASE)
    -- ----------------------------------------------------------------
    SET theNbPhrases = (SELECT COUNT(*) FROM PHRASE) ;
    
    -- ----------------------------------------------------------------
    -- Déterminer QuestionnaireId en fonction de QuestionnaireCode
    -- ----------------------------------------------------------------
    SET theQuestionnaireId = (SELECT QuestionnaireId FROM QUESTIONNAIRE WHERE QuestionnaireCode = QuestionnaireCodeIn) ;

-- ---------------------------------------------
-- C’est parti pour les INSERT.
-- ---------------------------------------------

    CASE theKount
    WHEN 50 THEN    -- Questionnaire à 50 phrases
        INSERT INTO QUESTIONNAIRE_PHRASE (QuestionnaireId, RegleId, PhraseId, DifficulteId) 
            SELECT theQuestionnaireId, Regleid, PhraseId, DifficulteId
            FROM
           (SELECT Regleid, PhraseId, DifficulteId, FLOOR(1+RAND()*theNbPhrases) as Alea   -- choix parmi 1995
            FROM   PHRASE
            WHERE  DifficulteId = 1  
            ORDER BY Alea) as t
            LIMIT 17   -- 17% de phrases de niveau 1
        ;
        INSERT INTO QUESTIONNAIRE_PHRASE (QuestionnaireId, RegleId, PhraseId, DifficulteId) 
            SELECT theQuestionnaireId, Regleid, PhraseId, DifficulteId
            FROM
           (SELECT Regleid, PhraseId, DifficulteId, FLOOR(1+RAND()*theNbPhrases) as Alea   -- choix parmi 1995
            FROM   PHRASE
            WHERE  DifficulteId = 2  
            ORDER BY Alea) as t
            LIMIT 18   -- 18% de phrases de niveau 1
        ;
        INSERT INTO QUESTIONNAIRE_PHRASE (QuestionnaireId, RegleId, PhraseId, DifficulteId) 
            SELECT theQuestionnaireId, Regleid, PhraseId, DifficulteId
            FROM
           (SELECT Regleid, PhraseId, DifficulteId, FLOOR(1+RAND()*theNbPhrases) as Alea   -- choix parmi 1995
            FROM   PHRASE
            WHERE  DifficulteId = 3  
            ORDER BY Alea) as t
            LIMIT 15   -- 15% de phrases de niveau 1
        ;
        
    WHEN 100 THEN   -- Questionnaire à 100 phrases
        INSERT INTO QUESTIONNAIRE_PHRASE (QuestionnaireId, RegleId, PhraseId, DifficulteId) 
            SELECT theQuestionnaireId, Regleid, PhraseId, DifficulteId
            FROM
           (SELECT Regleid, PhraseId, DifficulteId, FLOOR(1+RAND()*theNbPhrases) as Alea   -- choix parmi 1995
            FROM   PHRASE
            WHERE  DifficulteId = 1  
            ORDER BY Alea) as t
            LIMIT 35    -- 35% de phrases de niveau 1
        ;
        INSERT INTO QUESTIONNAIRE_PHRASE (QuestionnaireId, RegleId, PhraseId, DifficulteId) 
            SELECT theQuestionnaireId, Regleid, PhraseId, DifficulteId
            FROM
           (SELECT Regleid, PhraseId, DifficulteId, FLOOR(1+RAND()*theNbPhrases) as Alea   -- choix parmi 1995
            FROM   PHRASE
            WHERE  DifficulteId = 2  
            ORDER BY Alea) as t
            LIMIT 35    -- 35% de phrases de niveau 2
        ;
        INSERT INTO QUESTIONNAIRE_PHRASE (QuestionnaireId, RegleId, PhraseId, DifficulteId) 
            SELECT theQuestionnaireId, Regleid, PhraseId, DifficulteId
            FROM
           (SELECT Regleid, PhraseId, DifficulteId, FLOOR(1+RAND()*theNbPhrases) as Alea   -- choix parmi 1995
            FROM   PHRASE
            WHERE  DifficulteId = 3  
            ORDER BY Alea) as t
            LIMIT 30    -- 30% de phrases de niveau 3
        ;   
   
    WHEN 200 THEN   -- Questionnaire à 200 phrases
        INSERT INTO QUESTIONNAIRE_PHRASE (QuestionnaireId, RegleId, PhraseId, DifficulteId) 
            SELECT theQuestionnaireId, Regleid, PhraseId, DifficulteId
            FROM
           (SELECT Regleid, PhraseId, DifficulteId, FLOOR(1+RAND()*theNbPhrases) as Alea   -- choix parmi 1995
            FROM   PHRASE
            WHERE  DifficulteId = 1  
            ORDER BY Alea) as t
            LIMIT 70    -- 35% de phrases de niveau 1
        ;
        INSERT INTO QUESTIONNAIRE_PHRASE (QuestionnaireId, RegleId, PhraseId, DifficulteId) 
            SELECT theQuestionnaireId, Regleid, PhraseId, DifficulteId
            FROM
           (SELECT Regleid, PhraseId, DifficulteId, FLOOR(1+RAND()*theNbPhrases) as Alea   -- choix parmi 1995
            FROM   PHRASE
            WHERE  DifficulteId = 2  
            ORDER BY Alea) as t
           LIMIT 70    -- 35% de phrases de niveau 2
        ;
        INSERT INTO QUESTIONNAIRE_PHRASE (QuestionnaireId, RegleId, PhraseId, DifficulteId) 
            SELECT theQuestionnaireId, Regleid, PhraseId, DifficulteId
            FROM
           (SELECT Regleid, PhraseId, DifficulteId, FLOOR(1+RAND()*theNbPhrases) as Alea   -- choix parmi 1995
            FROM   PHRASE
            WHERE  DifficulteId = 3  
            ORDER BY Alea) as t
            LIMIT 60     -- 30% de phrases de niveau 1
        ;       
    
    END CASE ;
 
END

GO

Créons les lignes pour le questionnaire 'Q004' (200 phrases) :


DELIMITER  ;


CALL GenerationRandom ('Q004') ;


SELECT * FROM QUESTIONNAIRE_PHRASE 
ORDER BY QuestionnaireId, RegleId, PhraseId ;

SELECT COUNT(*) FROM QUESTIONNAIRE_PHRASE GROUP BY DifficulteId

Si le résultat ne correspond pas à votre attente (au hasard : mauvais rendement de la « randomisation » ), on essaiera d’améliorer, mais vous connaissez le principe de Peter...

**almoha** · 22/10/2015, 21h23

Bonsoir fsmrel,

Merci encore pour votre contribution. Pouvez-vous me dire quelle est l'utilité de "MAX_ROWS" dans le script de création des tables ?

Si le résultat ne correspond pas à votre attente

Le résultat est on ne peut plus conforme à mes attentes

Les tests que j'ai effectués démontrent que les rouages de la procédure GenerationRandom sont bien huilés.

Une interrogation par rapport à cette dernière. Vous déclarez la variable suivante :

Code :

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

DECLARE theLimit INT  ;

Je n'ai pas su voir où cette variable est utilisée dans la suite du code.

Pour la partie « entraînement », je remettrai de l’ordre un peu plus tard, car c’est un peu le b... dans mes fichiers scripts...

C'est mon cas aussi

, d'où ma requête.

**fsmrel** · 23/10/2015, 00h22

Bonsoir almoha,

Envoyé par almoha

quelle est l'utilité de "MAX_ROWS" ?

Ce paramètre est pour moi un garde-fou, une sécurité, tel le filet pour le trapéziste... Considérez la procédure GenerationDesPhrases, elle comporte des boucles imbriquées, et si, au moins dans mes premiers tests, je rate une condition d’arrêt de boucle, je risque tout bonnement de partir dans une boucle infinie, avec explosion du disque, etc. En codant « MAX_ROWS = 5000 » pour la table PHRASE, je dis à MySQL : « arrête-moi après que j’ai créé 5000 lignes dans la table ».

Envoyé par almoha

Vous déclarez la variable suivante :

DECLARE theLimit INT ;

Je n'ai pas su voir où cette variable est utilisée dans la suite du code.

Vous pouvez supprimer cette variable : initialement j’avais prévu de m’en servir pour le pourcentage de phrases par niveau de difficulté, mais finalement j’ai codé ce pourcentage « en dur » (voyez l’emploi que je fais de la clause LIMIT).

Script « entraînement » :

Le code qui suit est normalement en phase avec la situation au 9 octobre dernier (message #39) :


USE temp ;

DROP TABLE IF EXISTS PILE ; 

DROP TABLE IF EXISTS SELECTION ;
DROP TABLE IF EXISTS INTERROGATION ;
DROP TABLE IF EXISTS QUESTIONNAIRE_PHRASE ;
DROP TABLE IF EXISTS QUESTIONNAIRE ;
DROP TABLE IF EXISTS PHRASES_NB ;

DROP TABLE IF EXISTS REPONDRE ;
DROP TABLE IF EXISTS CHOISIR ;

DROP TABLE IF EXISTS UTILISATEUR ;
DROP TABLE IF EXISTS CORRIGE ;
DROP TABLE IF EXISTS PHRASE ;
DROP TABLE IF EXISTS DIFFICULTE ;
DROP TABLE IF EXISTS COMPOSITION ;
DROP TABLE IF EXISTS REGLE ;
DROP TABLE IF EXISTS ELEMENT ;
DROP TABLE IF EXISTS TYPE_ELEMENT ;

-------------------------------------------------------------------------------------------------

CREATE TABLE TYPE_ELEMENT 
(
        TypeElementId        INT             NOT NULL
      , TypeElementLibelle   VARCHAR(16)     NOT NULL
    , CONSTRAINT TYPE_ELEMENT_PK PRIMARY KEY (TypeElementId)
) ;

CREATE TABLE ELEMENT 
(
        ElementId            INT             NOT NULL
      , TypeElementId        INT             NOT NULL
      , ElementTexte         VARCHAR(64)     NOT NULL
    , CONSTRAINT ELEMENT_PK PRIMARY KEY (ElementId)
    , CONSTRAINT ELEMENT_TYPE_ELEMENT_FK FOREIGN KEY (TypeElementId)
      REFERENCES TYPE_ELEMENT (TypeElementId) ON DELETE CASCADE
) ;

CREATE TABLE COMPOSITION 
(
        ElementComposantId       INT             NOT NULL
      , ElementComposeId         INT             NOT NULL
    , CONSTRAINT COMPOSITION_ELEMENT_COMPOSANT_FK FOREIGN KEY (ElementComposantId)
      REFERENCES ELEMENT (ElementId) ON DELETE CASCADE
    , CONSTRAINT COMPOSITION_ELEMENT_COMPOSE_FK FOREIGN KEY (ElementComposeId)
      REFERENCES ELEMENT (ElementId)      
) ;

CREATE TABLE REGLE 
(
        RegleId              INT             NOT NULL
      , ElementId            INT             NOT NULL        
      , RegleTexte           VARCHAR(64)     NOT NULL
      , NbPhrases            INT             NOT NULL
    , CONSTRAINT REGLE_PK PRIMARY KEY (RegleId)
    , CONSTRAINT REGLE_ELEMENT_FK FOREIGN KEY (ElementId)
      REFERENCES ELEMENT (ElementId)
 ) ;
 
 CREATE TABLE DIFFICULTE 
(
        DifficulteId         INT             NOT NULL
      , DifficulteNiveau     INT             NOT NULL DEFAULT 1	
      , DifficulteTexte      VARCHAR(32)     NOT NULL
    , CONSTRAINT DIFFICULTE_PK PRIMARY KEY (DifficulteId)
    , CONSTRAINT DIFFICULTE_AK UNIQUE (DifficulteNiveau)    
) ;

CREATE TABLE PHRASE 
(
        RegleId              INT             NOT NULL
      , PhraseId             INT             NOT NULL
      , DifficulteId         INT             NOT NULL
      , Position             INT             NOT NULL
      , Texte                VARCHAR(64)     NOT NULL
    , CONSTRAINT PHRASE_PK PRIMARY KEY (RegleId, PhraseId)
    , CONSTRAINT PHRASE_REGLE_FK FOREIGN KEY (RegleId)
      REFERENCES REGLE (RegleId)
    , CONSTRAINT PHRASE_DIFFICULTE_FK FOREIGN KEY (DifficulteId)
      REFERENCES DIFFICULTE (DifficulteId)
) ;

-- --------------------------------------------------------------------
-- TABLE PILE - Simulation de la jointure récursive
-- --------------------------------------------------------------------
CREATE TABLE PILE 
(
        PileId               INT             NOT NULL AUTO_INCREMENT
      , Niveau               INT             NOT NULL
      , TypeParentId         INT             NOT NULL
      , TypeParentNom        VARCHAR(16)     NOT NULL
      , ElementParentId      INT             NOT NULL
      , ElementParentNom     VARCHAR(64)     NOT NULL
      , TypeElementId        INT             NOT NULL
      , TypeElementNom       VARCHAR(16)     NOT NULL
      , ElementId            INT             NOT NULL
      , ConcatenationNum     VARCHAR(64)     NOT NULL     
      , ElementNom           VARCHAR(64)     NOT NULL DEFAULT ''
      , Difficulte           INT             NOT NULL DEFAULT 0
    , CONSTRAINT PILE_PK PRIMARY KEY (PileId)   
    , CONSTRAINT PILE_AK UNIQUE (ElementParentId, TypeElementId, ElementId)  
) ;

-- -------------------------------------------------------------
-- Jeu d'essai
-- -------------------------------------------------------------

INSERT INTO TYPE_ELEMENT (TypeElementId, TypeElementLibelle) VALUES 
    (1, 'Partie'), (2, 'Sous-partie'), (3, 'Rubrique') ;
    
 SELECT * FROM TYPE_ELEMENT ;

INSERT INTO ELEMENT (ElementId, TypeElementId, ElementTexte) VALUES
    (1, 1, 'Formes et accords du verbe')
  , (2, 1, 'Formes et accords du nom, de l''adjectif et de l''adverbe')
  , (3, 1, 'Orthographe lexicale, signes graphiques et syntaxe')
  , (4, 1, 'Ces mots que l''on confond')
  , (5, 2, 'Le verbe : ses formes')
  , (6, 2, 'Syntaxe')
  , (7, 3, 'L''infinitif')
  , (8, 3, 'Les noms : leur féminin, leur pluriel')
  , (9, 3, 'L''imparfait du subjonctif')    
  , (10, 2, 'Le verbe, ses pièges')
  , (14, 3, 'Rubrique-à-brac')
  , (15, 2, 'sous-partie, piège à AK de PILE')  
  , (91, 1, 'Piège à récursivité')
  , (92, 1, 'Piège à récursivité')
  , (93, 2, 'Piège à récursivité')
 ;
 
SELECT * FROM ELEMENT ;

INSERT INTO COMPOSITION  (ElementComposantId, ElementComposeId) VALUES
     (5,1), (6, 3), (7, 5), (8, 2), (9, 5), (10, 1), (93, 92), (14, 1), (15, 1) 
     ;

SELECT * FROM COMPOSITION ; 
 
INSERT INTO REGLE (RegleId, ElementId, RegleTexte, NbPhrases) VALUES
    (1, 8, 'Le pluriel des noms : cas généraux', 5)
  , (2, 6, 'est-ce que', 15)
  , (3, 4, 'a ; à', 2)
  , (4, 4, 'ou ; où', 2)  
  , (5, 4, 'or ; hors', 4)  
  , (6, 9, 'La 3e personne du singulier', 5)
  , (7, 9, 'La 3e personne du pluriel', 3)
-- /*  
  , (8, 5, 'Une règle de la forme spéciale du verbe', 2)
  , (9, 1, 'Accord dissonant du verbe', 2)
  , (10, 14, 'règle-à-brac',5) 
  , (11, 10, 'rg11, enfant de SP10', 5)
  , (12, 10, 'rg12, enfant de SP10', 5)
  , (13, 10, 'rg13, enfant de SP10', 5)
  , (14, 10, 'rg14, enfant de SP10', 5)
  , (15, 1, 'règle, piège à AK de PILE', 2)
-- */  
  ;

-- SELECT * FROM REGLE ;

INSERT INTO DIFFICULTE (DifficulteId, DifficulteNiveau, DifficulteTexte) VALUES
    (1, 1, 'Facile'), (2, 2, 'Assez facile'), (3, 3, 'Piège classique'), (4, 4, 'Niveau Mérimée') ;  
 
 -- SELECT * FROM DIFFICULTE ;

INSERT INTO PHRASE (RegleId, PhraseId, DifficulteId, Position, Texte) VALUES
    (1, 1, 1, 5, 'rg1, ph1, le petit chat est morts')
  , (1, 2, 2, 5, 'rg1, ph2, il a un petit soucis')
  , (1, 3, 1, 3, 'rg1, ph3, des yeux marrons')
  , (1, 4, 2, 4, 'rg1, ph4, il est huit heure')
  , (1, 5, 3, 0, 'rg1, ph5, Huit cent deux kilos')
  , (3, 1, 2, 0, 'rg3, ph1 , Je vais à Paris')
  , (3, 2, 2, 0, 'rg3, ph2, Pierre a faim')
  , (3, 3, 4, 0, 'rg3, ph3, Ce crayon est a moi') 
  , (4, 1, 1, 2, 'rg4, Il et fort et agile')
  , (4, 2, 1, 0, 'rg4, Elle est grande')
  , (4, 3, 4, 0, 'rg4, Il aime le cuissot de chevreuil')
  , (4, 4, 4, 5, 'rg4, Il aime aussi le cuissot de veau')
  , (4, 5, 3, 0, 'rg4, C''est une imbécillité')
  , (5, 1, 2, 0, 'rg5, ph1')
  , (5, 2, 2, 0, 'rg5, ph2')
  , (6, 1, 2, 0, 'rg6, ph1, Qu''il aimât cela !')
  , (6, 2, 3, 0, 'rg6, ph2, Qu''il n''aimat pas cela !')
  , (6, 3, 2, 0, 'rg6, ph3, Qu''il eût aimé')
  , (7, 1, 3, 0, 'rg7, ph1, Qu''ils cinématographassent cela !')  
  , (7, 2, 3, 2, 'rg7, ph2, Qu''ils n''aimassent pas cela !')
  , (7, 3, 2, 0, 'rg7, ph3, Fallait-il que je vous aimasse')
  , (7, 4, 2, 0, 'rg7, ph4, et que je vous idolâtrasse')
  , (7, 5, 2, 0, 'rg7, ph5, pour que vous m''assassinassiez')
  , (8, 1, 2, 0, 'rg8, ph1')
  , (8, 2, 2, 0, 'rg8, ph2')
  , (9, 1, 2, 0, 'rg9, ph1')
  , (9, 2, 1, 0, 'rg9, ph2')
  , (9, 3, 2, 0, 'rg9, ph3')
  , (10, 1, 2, 0, 'rg10, ph1')
  , (10, 2, 2, 0, 'rg10, ph2')
  , (11, 1, 2, 0, 'rg11, ph1')
  , (11, 2, 2, 0, 'rg11, ph2')
  , (12, 1, 2, 0, 'rg12, ph1')
  , (12, 2, 2, 0, 'rg12, ph2')
  , (13, 1, 2, 0, 'rg13, ph1')
  , (13, 2, 2, 0, 'rg13, ph2')
  , (14, 1, 2, 0, 'rg14, ph1')
  , (14, 2, 2, 0, 'rg14, ph2')
-- */  
;

-- SELECT * FROM PHRASE ; 

COMMIT ;

-- ----------------------------------------------------------------
-- Les procédures
-- ----------------------------------------------------------------

DROP PROCEDURE IF EXISTS RaoulNavigue ;
DROP PROCEDURE IF EXISTS RecursonsJoyeusement ;

DELIMITER GO

CREATE PROCEDURE RaoulNavigue
(
   IN TypeParentChoisiId INT, ParentChoisiId INT, DifficulteChoisie INT
)

BEGIN

-- --------------------------------------------------------------------------------------
-- Requête Rxy
-- Raoul a choisi un sujet (TypeElementId = x et ElementId = y)
-- Pour avoir la descendance directe : TypeParentId = x et ElementParentId = y.
-- Si le descendant est une phrase (TypeElementId = 5), et si Raoul veut 
-- seulement travailler sur un certain niveau de difficulté (DifficulteChoisie > 0),
-- alors on filtre sur ce niveau.  
-- --------------------------------------------------------------------------------------
 
IF DifficulteChoisie = 0 OR TypeParentChoisiId < 4 THEN
    SELECT  TypeParentId, TypeParentNom, ElementParentId, ElementParentNom
          , TypeElementId, TypeElementNom, Elementid, ElementNom 
    FROM    PILE
    WHERE   TypeParentId = TypeParentChoisiId AND ElementParentId = ParentChoisiId 
    ORDER BY ElementParentId, TypeElementId ; -- Pour avoir dans l'ordre des types d'élément par partie
ELSE
    SELECT  TypeParentId, TypeParentNom, ElementParentId, ElementParentNom
          , TypeElementId, TypeElementNom, Elementid, ElementNom 
    FROM    PILE
    WHERE   TypeParentId = TypeParentChoisiId AND ElementParentId = ParentChoisiId
      AND   Difficulte = DifficulteChoisie
      AND   TypeElementId = 5                      -- Element de type phrase = 5
    ORDER BY ElementParentId, TypeElementId ;
END IF ;

END

GO

CREATE PROCEDURE RecursonsJoyeusement
(
   IN Amorce BOOLEAN, ElementIdIn VARCHAR(10)
)

BEGIN

    DECLARE theNiveau INT ;
    DECLARE NiveauRegle INT ;
    DECLARE NiveauPhrase INT ; 
    DECLARE Kount INT ;
     
    SET theNiveau = (SELECT DISTINCT COALESCE(MAX(Niveau) + 1, 1) FROM PILE) ;
        
    IF Amorce = TRUE THEN
    
    -- ----------------------------------------
    -- Niveau 1 (racine)
    -- ----------------------------------------
    
        IF ElementIdIn > 0 THEN
            INSERT INTO PILE (ElementId, TypeElementId, TypeElementNom, Niveau, ElementParentId, ElementParentNom, TypeParentId, TypeParentNom, ConcatenationNum, ElementNom)
                SELECT ElementIdIn                                                                                    -- Identifiant de l'élément, passé par l'appelant
                     , x.TypeElementId                                                                                -- Identifiant du type de l'élément
                     , COALESCE((SELECT z.TypeElementlibelle                                                          -- Nom du type de l'élément
                                 FROM   COMPOSITION AS x JOIN ELEMENT AS y ON x.ElementComposantId = y.ElementId
                                                         JOIN TYPE_ELEMENT AS z ON y.TypeElementId = z.TypeElementId 
                                 WHERE  x.ElementComposantId = ElementIdIn), '/')  
                     , 1                                                                                              -- Niveau
                     , COALESCE((SELECT ElementComposeId                                                              -- Identifiant du parent de l'élément
                                 FROM   COMPOSITION 
                                 WHERE ElementComposantId = ElementIdIn), 0)
                     , COALESCE((SELECT y.ElementTexte                                                                -- Nom du parent de l'élément
                                 FROM   COMPOSITION AS x JOIN ELEMENT AS y ON x.ElementComposeId = y.ElementId                               
                                 WHERE  x.ElementComposantId = ElementIdIn), '/')
                     , COALESCE((SELECT y.TypeElementId                                                               -- Identifiant du type du parent de l'élément
                                 FROM   COMPOSITION AS x JOIN ELEMENT AS y ON x.ElementComposeId = y.ElementId 
                                 WHERE  x.ElementComposantId = ElementIdIn), 0)
                     , COALESCE((SELECT z.TypeElementlibelle                                                          -- Nom du type du parent de l'élément 
                                 FROM   COMPOSITION AS x JOIN ELEMENT AS y ON x.ElementComposeId = y.ElementId  
                                                         JOIN TYPE_ELEMENT AS z ON y.TypeElementId = z.TypeElementId
                                 WHERE  x.ElementComposantId = ElementIdIn), 0)
                     , RTRIM(CAST(ElementIdIn AS CHAR(4)))                                                            -- Début concaténation des id
                     , x.ElementTexte                                                                                 -- Nom de l'élément   
                FROM   ELEMENT AS x
                WHERE  x.ElementId = ElementIdIn  
            ;                          
        ELSE
            -- ---------------------------------------------
            -- ElementIdIn = 0 : on prend tout
            -- ---------------------------------------------
            INSERT INTO PILE (ElementId, TypeElementId, TypeElementNom, Niveau, ElementParentId, ElementParentNom, TypeParentId, TypeParentNom, ConcatenationNum, ElementNom)
                SELECT ElementId                             -- Identifiant de chaque élément racine (0 : on prend tout)
                     , TypeElementId                          -- Identifiant du type de l'élément (à savoir : 1)
                     , '/'                                    -- TypeElementNom
                     , 1                                       -- Niveau
                     , 0                                       -- Identifiant du parent de l'élément
                     , '/'                                     -- Nom du parent de l'élément
                     , 0                                       -- Identifiant du type du parent de l'élément
                     , '/'                                     -- Nom du type du parent de l'élément                 
                     , RTRIM(CAST(ElementId AS CHAR(4)))       -- Début concaténation des id
                     , ElementTexte                            -- Nom de l'élément           
                FROM   ELEMENT
                WHERE  TypeElementId = 1 ;
        END IF ;
        
     -- ------------------------------------------------------------------------------
    -- Niveau 2 (rattachement direct à un racine : sous-partie ou rubrique ou règle)
    -- -------------------------------------------------------------------------------
        
        INSERT INTO PILE (ElementId, TypeElementId, TypeElementNom, Niveau, ElementParentId, ElementParentNom, TypeParentId, TypeParentNom, ConcatenationNum, ElementNom) 
             SELECT x.ElementComposantId                                                                                    -- Identifiant de l'élément à récupérer
                  , y.TypeElementId                                                                                         -- Identifiant du type de l'élément
                  , u.TypeElementlibelle                                                                                    -- Nom du type de l'élément           
                  , 2                                                                                                       -- Niveau
                  , x.ElementComposeId                                                                                      -- Identifiant du parent de l'élément
                  , z.ElementTexte                                                                                          -- Nom du parent de l'élément
                  , z.TypeElementId                                                                                         -- Identifiant du type du parent de l'élément
                  , t.TypeElementlibelle                                                                                    -- Nom du type du parent de l'élément                 
                  , CONCAT(RTRIM(CAST(x.ElementComposeId AS CHAR(4))), ', ', RTRIM(CAST(x.ElementComposantId AS CHAR(4))))  -- Concaténation des id  
                  , y.ElementTexte                                                                                          -- Nom de l'élément                     
             FROM   COMPOSITION AS x JOIN ELEMENT AS y ON x.ElementComposantId = y.ElementId
                                     JOIN ELEMENT AS z ON x.ElementComposeId = z.ElementId
                                     JOIN TYPE_ELEMENT AS t ON z.TypeElementId = t.TypeElementId
                                     JOIN TYPE_ELEMENT AS u ON y.TypeElementId = t.TypeElementId   
             WHERE  x.ElementComposeId = y.ElementId 
             ;
             
        -- -------------------------------------------
        -- et un tour de manège
        -- -------------------------------------------

        CALL RecursonsJoyeusement(FALSE, '') ;

    ELSE

    -- -----------------------------------------------
    -- Niveau > 2 (sous-partie ou rubrique ou règle)
    -- ----------------------------------------------
    
        SET Kount = (SELECT COUNT(*) 
                     FROM  (SELECT x.ElementComposantId, Niveau  
                            FROM   COMPOSITION AS x INNER JOIN PILE AS y 
                                   ON x.ElementComposeId = y.ElementId
                            WHERE  Niveau = TheNiveau - 1) as truc) ;
        IF Kount > 0 THEN
        
    -- ---------------------------------------------------
    -- Niveau > 2 (on n'a pas atteint le niveau règle)
    -- ---------------------------------------------------
        
            INSERT INTO PILE (ElementId, TypeElementId, TypeElementNom, Niveau, ElementParentId, ElementParentNom, TypeParentId, TypeParentNom, ConcatenationNum, ElementNom) 
                SELECT x.ElementComposantId                                                                 -- Identifiant de l'élément à récupérer
                     , z.TypeElementId                                                                      -- Identifiant du type de l'élément
                     , v.TypeElementlibelle                                                                 -- Nom du type de l'élément
                     , theNiveau                                                                            -- Niveau
                     , x.ElementComposeId                                                                   -- Identifiant du parent de l'élément
                     , t.ElementTexte                                                                       -- Nom du parent de l'élément                     
                     , t.TypeElementId                                                                      -- Identifiant du type du parent de l'élément
                     , u.TypeElementlibelle                                                                 -- Nom du type du parent de l'élément             
                     , CONCAT(y.ConcatenationNum, ', ', RTRIM(CAST(x.ElementComposantId AS CHAR(4))))       -- Concaténation des id
                     , z.ElementTexte                                                                       -- Nom de l'élément
                 FROM   COMPOSITION AS x JOIN PILE AS y ON x.ElementComposeId = y.ElementId 
                                        JOIN ELEMENT AS z ON x.ElementComposantId = z.ElementId
                                        JOIN ELEMENT AS t ON x.ElementComposeId = t.ElementId
                                        JOIN TYPE_ELEMENT AS u ON t.TypeElementId = u.TypeElementId
                                        JOIN TYPE_ELEMENT AS v ON z.TypeElementId = v.TypeElementId
               WHERE  Niveau = TheNiveau - 1 ; 
             
            -- -------------------------------------------
            -- et un tour de manège
            -- -------------------------------------------

            CALL RecursonsJoyeusement(FALSE, '') ;
            
        ELSE
        
    -- ----------------------------------------
    -- On a atteint le niveau règle
    -- ----------------------------------------
        
            SET NiveauRegle = (SELECT MAX(TypeElementId) + 1 FROM TYPE_ELEMENT) ;  -- Identifiant du type de la règle : id du plus grand type d'élément + 1      
            SET NiveauPhrase = NiveauRegle + 1 ;                                   -- Identifiant du type de la phrase 
         
            -- ----------------------------------------------
            -- Récupération des règles
            -- ----------------------------------------------

            INSERT INTO PILE (ElementId, TypeElementId, TypeElementNom, Niveau, ElementParentId, ElementParentNom, TypeParentId, TypeParentNom, ConcatenationNum, ElementNom)
                 SELECT x.RegleId                                                             -- Identifiant de la règle à récupérer
                     , NiveauRegle                                                            -- Identifiant du type de la règle : id du plus grand type d'élément + 1
                     , 'Règle'                                                                -- Nom du type de la règle : "Règle"
                     , theNiveau                                                              -- Niveau
                     , x.ElementId                                                            -- Identifiant du parent de la règle
                     , z.ElementTexte                                                         -- Nom du parent de la règle                 
                     , z.TypeElementId                                                        -- Identifiant du type du parent de la règle
                     , t.TypeElementlibelle                                                   -- Nom du type du parent de la règle                 
                     , CONCAT(y.ConcatenationNum, ', ', RTRIM(CAST(x.RegleId AS CHAR(6))))    -- Concaténation des id
                     , RegleTexte                                                             -- Nom de la règle
                 FROM  REGLE AS x JOIN PILE AS y ON x.ElementId = y.ElementId
                                  JOIN ELEMENT AS z ON x.ElementId = z.ElementId
                                  JOIN TYPE_ELEMENT AS t ON z.TypeElementId = t.TypeElementId 
            ;

            -- ----------------------------------------------
            -- Récupération des phrases
            -- ----------------------------------------------

            INSERT INTO PILE (ElementId, TypeElementId, TypeElementNom, Niveau, ElementParentId, ElementParentNom, TypeParentId, TypeParentNom, ConcatenationNum, ElementNom, Difficulte)
                SELECT x.PhraseId                                                              -- Identifiant de la phrase à récupérer
                     , NiveauPhrase                                                            -- Identifiant du type de la phrase : id du type "règle" + 1
                     , 'Phrase'                                                                -- Nom du type de la phrase : "Phrase"
                     , theNiveau + 1                                                           -- Niveau atteint
                     , x.RegleId                                                               -- Identifiant de la règle parente
                     , y.RegleTexte                                                            -- Nom de la règle parente 
                     , NiveauRegle                                                             -- Identifiant du type "Règle"
                     , 'Règle'                                                                 -- Nom du type "Règle"
                     , CONCAT(z.ConcatenationNum, ', ', RTRIM(CAST(x.PhraseId AS CHAR(6))))    -- Concaténation des id
                     , x.Texte                                                                 -- Nom de la phrase
                     , t.DifficulteNiveau                                                      -- Niveau de difficulté de la phrase                     
                FROM   PHRASE AS x JOIN REGLE AS y ON x.RegleId = y.RegleId
                                   JOIN PILE As z ON x.RegleId = z.ElementId AND z.TypeElementId = NiveauRegle    
                                   JOIN DIFFICULTE AS t ON x.DifficulteId = t.DifficulteId                                        
           ;

        END IF ;

    END IF;

END

GO

DELIMITER ;

-- /*
-- --------------------------------------------------------
-- Appel à la procédure récursive RecursonsJoyeusement
-- --------------------------------------------------------

SET @@GLOBAL.max_sp_recursion_depth = 4;  -- Pour éviter les boucles infinies...
SET @@session.max_sp_recursion_depth = 4; 

-- -------------------------------------------------------------------------------
-- @Entree : L'élément d'identifiant ElementId = @Entree dans la table ELEMENT
-- -------------------------------------------------------------------------------

SET @Entree := 1 ;  -- L'élément d'identifiant ElementId = 1 dans la table ELEMENT (Formes et accords du verbe)
--   SET @Entree := 5 ;  -- L'élément d'identifiant ElementId = 5 dans la table ELEMENT (Le verbe : ses formes)
 -- SET @Entree := 9 ;  -- L'élément d'identifiant ElementId = 9 dans la table ELEMENT (l'imparfait du subjonctif)
   SET @Entree := 0 ;  -- La totale

SET @Amorce  := TRUE ;

DELETE FROM PILE ;

CALL RecursonsJoyeusement(@Amorce, @Entree);

-- ----------------------------
-- Au résultat
-- ----------------------------

SELECT * FROM PILE
ORDER BY ElementParentId, ElementId ;

SELECT x.ElementId
     , x.TypeElementNom AS TypeElement
     , ConcatenationNum       
     , CASE x.TypeElementId 
           WHEN 1 THEN ElementNom 
           WHEN 2 THEN CONCAT('---- ', ElementNom)
           WHEN 3 THEN CONCAT('-------- ', ElementNom)
           WHEN 4 THEN CONCAT('------------ ', ElementNom)
        ELSE CONCAT('---------------- ', ElementNom) 
      END
       AS ElementNom       
FROM   PILE AS x LEFT JOIN ELEMENT AS y ON x.ElementId = y.ElementId
ORDER BY ConcatenationNum ;

-- ------------------------------
-- Sans les phrases
-- ------------------------------

SELECT x.elementId
     , x.TypeElementNom AS TypeElement
     , ConcatenationNum       
     , CASE x.TypeElementId 
           WHEN 1 THEN ElementNom 
           WHEN 2 THEN CONCAT('---- ', ElementNom)
           WHEN 3 THEN CONCAT('-------- ', ElementNom)
           WHEN 4 THEN CONCAT('------------ ', ElementNom)
      END
       AS ElementNom       
FROM   PILE AS x LEFT JOIN ELEMENT AS y ON x.ElementId = y.ElementId
WHERE   x.TypeElementId < 5
ORDER BY ConcatenationNum ;

-- */

-- -----------------------------------------------------------------------
-- Requête R1
-- Sélection des dépendants directs des parties
-- -----------------------------------------------------------------------

SELECT  TypeParentId, TypeParentNom, ElementParentId, ElementParentNom
      , TypeElementId, TypeElementNom, Elementid, ElementNom  
      , '' AS 'Requête R1'
FROM    PILE
WHERE   TypeParentId = 1 -- AND ElementParentId = 0 
ORDER BY ElementParentId, TypeElementId -- Pour avoir dans l'ordre des types d'élément par partie
;

SET @TypeParentId  = 1 ;
SET @ElementParentId = 0 ;  -- signifie : ne pas tenir compte (on est au niveau racine)

-- ---------------------------------------------------------------------------------
-- Requête R2
-- Raoul a choisi "Le verbe, ses formes" (TypeElementId = 2, ElementId = 5).
-- Pour avoir la descendance directe : TypeParentId = 2 et ElementParentId = 5.
-- ---------------------------------------------------------------------------------

SELECT  TypeParentId, TypeParentNom, ElementParentId, ElementParentNom
      , TypeElementId, TypeElementNom, Elementid, ElementNom
      , Difficulte      
      , '' AS 'Requête R2'
FROM   PILE
WHERE  TypeParentId = 2 AND ElementParentId = 5 -- sous-partie 5, le verbe, ses formes
-- WHERE  TypeParentId = 4 AND ElementParentId = 9 -- sous-partie 9
ORDER BY ElementParentId, TypeElementId -- Pour avoir dans l'ordre des types d'élément par partie
;


-- --------------------------------------------------------
-- Appel à la procédure récursive RaoulNavigue
-- --------------------------------------------------------

SET @TypeParentId  = 2 ;          -- 'sous-partie'
SET @ElementParentId = 5 ;        -- 'le verbe, ses formes'
SET @Difficulte = 2 ;             -- 'niveau de difficulté (0 si pas de filtre)'


SET @TypeParentId  = 4 ;
SET @ElementParentId = 9 ;
SET @Difficulte = 2 ;
SET @Difficulte = 0 ;

CALL RaoulNavigue(@TypeParentId, @ElementParentId, @Difficulte) ;

-- --------------------------------------------------------------------------------------
-- Requête R3
-- Raoul a choisi "L'imparfait du subjonctif" (TypeElementId = 3 et ElementId = 9)
-- Pour avoir la descendance directe : TypeParentId = 3 et ElementParentId = 9.
-- --------------------------------------------------------------------------------------

SELECT  TypeParentId, TypeParentNom, ElementParentId, ElementParentNom
      , TypeElementId, TypeElementNom
      , Elementid
      , ElementNom
      , Difficulte
      , '' AS 'Requête R3'
FROM   PILE
WHERE  TypeParentId = 3 AND ElementParentId = 9
ORDER BY ElementParentId, TypeElementId -- Pour avoir dans l'ordre des types d'élément par partie -- ConcatenationNum
;
SET @TypeParentId  = 3 ;
SET @ElementParentId = 9 ;  
SET @Difficulte = 2 ;

CALL RaoulNavigue(@TypeParentId, @ElementParentId, @Difficulte) ;

-- --------------------------------------------------------------------------------------
-- Requête R4
-- Raoul a choisi "La 3e personne du singulier" (TypeElementId = 4 et ElementId = 6)
-- Pour avoir la descendance directe : TypeParentId = 4 et ElementParentId = 6.
-- --------------------------------------------------------------------------------------

SELECT  TypeParentId, TypeParentNom, ElementParentId, ElementParentNom
      , TypeElementId, TypeElementNom, Elementid, ElementNom
      , Difficulte      
      , '' AS 'Requête R4'
FROM    PILE
WHERE   TypeParentId = 4 AND ElementParentId = 6
ORDER BY ElementParentId, TypeElementId
;

SET @TypeParentId  = 4 ;
SET @ElementParentId = 6 ;  
SET @Difficulte = 2 ;

-- CALL RaoulNavigue(@TypeParentId, @ElementParentId, @Difficulte) ;
CALL RaoulNavigue(4, 6, 2) ;

Là encore, à tester à fond...

**almoha** · 24/10/2015, 14h10

Bonjour fsmrel,

Merci pour le script. J'ai pu mettre en place la structure complète. Je vais commencer les (longs) tests avec mes véritables données. Une question préalable : à l'instar de l'attribut Iteration de la table INTERROGATION, je suppose que la valorisation de l'attribut Iteration de la table SELECTION doit passer par un trigger ?

**fsmrel** · 24/10/2015, 16h12

Bonsoir almoha,

Envoyé par almoha

à l'instar de l'attribut Iteration de la table INTERROGATION, je suppose que la valorisation de l'attribut Iteration de la table SELECTION doit passer par un trigger ?

Négatif, comme disait mon capitaine... En effet, vous observerez que l’attribut Iteration est élément de la clé étrangère {UtilisateurId, QuestionnaireId, Iteration} (contrainte SELECTION_INTERROGATION_FK), en compagnie des attributs UtilisateurId et QuestionnaireId :


CREATE TABLE SELECTION 
(
        UtilisateurId        INT             NOT NULL
      , QuestionnaireId      INT             NOT NULL
      , Iteration            INT             NOT NULL
      , RegleId              INT             NOT NULL
      , PhraseId             INT             NOT NULL
      , Position             INT             NOT NULL      
      , CONSTRAINT SELECTION_PK PRIMARY KEY (UtilisateurId, QuestionnaireId, Iteration, RegleId, PhraseId)
      , CONSTRAINT SELECTION_INTERROGATION_FK FOREIGN KEY (UtilisateurId, QuestionnaireId, Iteration)
            REFERENCES INTERROGATION (UtilisateurId, QuestionnaireId, Iteration) 
      , CONSTRAINT SELECTION_QUESTIONNAIRE_PHRASE_FK FOREIGN KEY (QuestionnaireId, RegleId, PhraseId)
            REFERENCES QUESTIONNAIRE_PHRASE (QuestionnaireId, RegleId, PhraseId)
) ;

**almoha** · 25/10/2015, 08h59

Bonjour fsmrel,

Mais oui, au temps pour moi !

**almoha** · 26/10/2015, 22h30

Bonsoir fsmrel,

Je suis en train de tester à nouveau la partie "Entrainement". Je n'avais pas vu une problématique. Lorsque l'utilisateur choisit une règle, cela implique que va lui être soumis, l'une après l'autre, 10 à 40 phrases associées à cette règle.
Or à chaque session d'apprentissage d'une règle donnée, les phrases ne devront être soumises qu'une seule fois, si possible dans un ordre aléatoire, le nombre de ces phrases correspondant à l’attribut NbPhrases de la table REGLE.
Plutôt que de confier le respect de ces contraintes à l'application, peut-être pourrait-on s’inspirer de ce qui a été prévu pour l'élaboration des questionnaires de la partie "Examen".

Le "questionnaire apprentissage" répondrait aux règles suivantes :

* le nombre de phrases associées à une règle serait celui contenu dans l’attribut NbPhrases de la table REGLE
* une seule occurrence par phrase
* ordre aléatoire des phrases
* le contenu du questionnaire serait écrasé à chaque session d'apprentissage d'une règle, quelle qu'elle soit (nul besoin de conserver les questionnaires comme dans la partie "Examen")

A moins qu'il ne soit pas utile de passer par un questionnaire "tampon" pour arriver à mes fins, mon but étant de soumettre à l'utilisateur l'une après l'autre 10 à 40 phrases associées à la règle étudiée...
Quel est votre avis sur la question ? Merci.

**fsmrel** · 27/10/2015, 15h47

Bonjour almoha,

Si les règles R2 à R5 sont parties prenantes dans cette affaire, on peut rendre aléatoire l’ordre dans lequel les lignes sélectionnées sont présentées. Pour cela on peut à nouveau mettre à profit la fonction RAND (cf. message #41).

Exemple avec la règle R2 :


SELECT  TypeParentId, TypeParentNom, ElementParentId, ElementParentNom
      , TypeElementId, TypeElementNom, Elementid, ElementNom
      , Difficulte, RAND() as Alea      
      , '' AS 'Requête R2'
FROM   PILE
WHERE  TypeParentId = 2 AND ElementParentId = 5 -- sous-partie 5, le verbe, ses formes
ORDER BY ElementParentId, TypeElementId, Alea -- Dans l'ordre des types d'élément par partie, et aléatoire pour le dernier niveau
;

Cela convient-il ?

Comme celle-ci, notre conversation me repose, après les turbulences vécues ailleurs et où il est impossible de ne pas perdre patience...

**almoha** · 27/10/2015, 22h09

Bonsoir fsmrel,

Merci. Je vais effectivement utiliser la fonction RAND mais uniquement pour la dernière requête utilisée, c'est-à-dire celle affichant les phrases. Je pensais jouer avec la clause LIMIT pour afficher successivement les phrases associées à la règle choisie :

* La 1re phrase s'affiche; l'utilisateur indique sa réponse; l'application lui indique s'il a fourni la bonne réponse et affiche la règle associée;
* La 2e phrase s'affiche; l'utilisateur indique sa réponse; l'application lui indique s'il a fourni la bonne réponse et affiche la règle associée;
* etc, jusqu'à ce que toutes les phrases associées à la règle aient été affichées et que l'utilisateur ait fourni ses réponses.

Faut-il faire varier la clause LIMIT de la requête (0,1 ; 1;1; 2;1 etc.) pour permettre cet affichage successif ? L'utilisation de la fonction RAND ne rend-t-elle pas l’entreprise délicate, d’autant que les phrases ne doivent être présentées qu’une seule fois par série ?

**almoha** · 28/10/2015, 18h56

Bonjour fsmrel,

Par ailleurs :

En continuant mes tests, je me rends compte avec effarement

que je n'avais pas prévu le fait que l'erreur présente dans une phrase peut concerner un groupe de mots, ce qui suppose plusieurs positions pour une même phrase. Par exemple dans cette phrase :

Code :

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

Cette cérémonie reste un moment mémorable, au temps pour moi que pour mes enfants.

l'utilisateur est fondé à localiser l'erreur dans le groupe de mots "au temps pour moi", soit au choix il peut cliquer sur les mots ayant comme position 7, 8, 9 ou 10 dans la phrase. Il s'agit certes d'une seule et même erreur mais il y a plusieurs mots qui la composent à des positions différentes. Dès lors la table PHRASE ne peut plus contenir l'attribut Position, ce dernier devant être externalisé dans une table distincte, c'est bien cela ?. Ce changement va aussi impliquer un aménagement de la procédure ScoreCandidatCalculVariante... Et peut-être d'autres aménagements que je ne soupçonne pas... Je suis désolé de ne pas avoir décelé plus tôt le possible caractère multiple de la position des mots

**fsmrel** · 28/10/2015, 19h10

Bonsoir almoha,

Envoyé par almoha

l'erreur présente dans une phrase peut concerner un groupe de mots, ce qui suppose plusieurs positions pour une même phrase

Fichtre ! Bon. Je suis bien chargé, mais je vais regarder ça

**fsmrel** · 30/10/2015, 01h14

Bonsoir almoha,

Envoyé par almoha

Dès lors la table PHRASE ne peut plus contenir l'attribut Position, ce dernier devant être externalisé dans une table distincte, c'est bien cela ?

Exactement. On vire l’attribut Position de la table PHRASE et on définit une table POSITION, grâce à laquelle une phrase pourra contenir plus d’une erreur, chacune repérée par sa position, d’où présence de l’attribut Position dans cette nouvelle table, attribut participant à la clé primaire d’icelle :

Partie « entraînement » :

J’ai été amené à changer trois lignes dans le trigger REPONDRE_INSERT_AFTER qui calcule le statut de Raoul (cf. le message #18).

Côté SQL, la structure de la table PHRASE est donc modifiée et la table POSITION est nouvelle :


CREATE TABLE PHRASE 
(
        RegleId              INT             NOT NULL
      , PhraseId             INT             NOT NULL
      , DifficulteId         INT             NOT NULL
      , Texte                VARCHAR(96)     NOT NULL
    , CONSTRAINT PHRASE_PK PRIMARY KEY (RegleId, PhraseId)
    , CONSTRAINT PHRASE_REGLE_FK FOREIGN KEY (RegleId)
      REFERENCES REGLE (RegleId)
    , CONSTRAINT PHRASE_DIFFICULTE_FK FOREIGN KEY (DifficulteId)
      REFERENCES DIFFICULTE (DifficulteId)
) ;

CREATE TABLE POSITION 
(
        RegleId              INT             NOT NULL
      , PhraseId             INT             NOT NULL
      , Position             INT             NOT NULL
    , CONSTRAINT POSITION_PK PRIMARY KEY (RegleId, PhraseId, Position)
    , CONSTRAINT POSITION_PHRASE_FK FOREIGN KEY (RegleId, PhraseId)
      REFERENCES PHRASE (RegleId, PhraseId) ON DELETE CASCADE
) ;

Le trigger devient (modifs en bleu) :


CREATE TRIGGER REPONDRE_INSERT_AFTER AFTER INSERT ON REPONDRE 
FOR EACH ROW
    BEGIN
        SET @StatutAcquerir = 'à acquérir' ;
        SET @StatutEnCours = 'acquisition en cours' ;
        SET @StatutAcquis = 'acquis' ;
 
 -- Raoul a répondu au moins une fois à une phrase d'une règle donnée. Si son statut pour cette règle est 'à acquérir', 
 -- ce statut passe à 'acquisition en cours', que sa réponse soit bonne ou mauvaise.
 
        SET @StatutRaoul = (SELECT Statut 
                            FROM   CHOISIR 
                            WHERE  UtilisateurId = new.UtilisateurId 
                              AND  RegleId = new.RegleId) ;
                            
        IF @StatutRaoul = @StatutAcquerir THEN                            
            UPDATE CHOISIR SET Statut =  @StatutEnCours 
                WHERE UtilisateurId = new.UtilisateurId 
                  AND RegleId = new.RegleId ;
        END IF ;
 
-- On récupère le nombre de tours de manège complets déjà réussis par Raoul

        SET @CompteurChoisir = (SELECT Compteur
                                FROM  CHOISIR
                                WHERE UtilisateurId = new.UtilisateurId 
                                  AND RegleId = new.RegleId) ;
 
-- On récupère le nombre de phrases auxquelles Raoul n'a pas encore répondu pour la règle en cours

        SET @NbPhrasesSansReponse = (SELECT COUNT(*) 
                                     FROM   PHRASE AS x
                                     WHERE  RegleId = new.RegleId
                                       AND  NOT EXISTS
                                           (SELECT ''
                                            FROM   REPONDRE AS y 
                                            WHERE  x.RegleId = y.RegleId
                                              AND  x.PhraseId = y.PhraseId)
                                    ) ;

-- Si Raoul a répondu à toutes les phrases de la règle en cours, pour chacune d'elles 
-- on regarde combien de fois il a bien répondu.
-- On récupère le plus petit nombre de fois où il a bien répondu à une phrase. 
        
        SET @MinKount = 0 ;
        
        IF @NbPhrasesSansReponse = 0 THEN
            SET @MinKount = (SELECT DISTINCT MIN(Kount) AS MinKount
                             FROM  (SELECT COUNT(*) AS Kount
                                    FROM   REPONDRE AS x 
                                    WHERE  x.UtilisateurId = new.UtilisateurId AND x.RegleId = new.RegleId
                                      AND  EXISTS (SELECT '' 
                                                   FROM   POSITION AS y
                                                   WHERE  x.Regleid = y.Regleid AND x.PhraseId = y.PhraseId
                                                     AND  x.Position = y.Position)  
                                    GROUP BY x.UtilisateurId, x.RegleId, x.PhraseId) AS t) ; 
        END IF ;

-- Si le plus petit nombre de fois où Raoul a bien répondu est au moins égal à 3, 
-- on fait passer le statut à 'acquis' et on mémorise ce plus petit nombre.

        IF @MinKount >= 3 THEN
            UPDATE CHOISIR SET Statut = @StatutAcquis
                             , Compteur = @MinKount
                WHERE UtilisateurId = new.UtilisateurId AND RegleId = new.RegleId ; 
 
-- Si le plus petit nombre de fois où Raoul a bien répondu est inférieur à 3, 
-- on mémorise ce plus petit nombre s'il est supérieur à celui déjà mémorisé.
               
        ELSEIF @MinKount > @CompteurChoisir THEN
            UPDATE CHOISIR SET Compteur = @MinKount
               WHERE UtilisateurId = new.UtilisateurId AND RegleId = new.RegleId ;
        END IF ;  
    END 
GO

Jeu d’essai :


INSERT INTO PHRASE (RegleId, PhraseId, DifficulteId, Texte) VALUES
    (1, 1, 1, 'Le petit chat est morts')
  , (1, 2, 2, 'Cette cérémonie reste un moment mémorable, au temps pour moi que pour mes enfants')  
  , (1, 3, 1, 'Des yeux marrons')
  , (1, 4, 2, 'Il est huit heure')
  , (1, 5, 3, 'Huit cent deux kilos')
  , (3, 1, 2, 'Je vais à Paris')
  , (3, 2, 2, 'Pierre a faim')
  , (3, 3, 3, 'Ce crayon est à moi')  
  , (4, 1, 1, 'Il et fort et agile')
  , (4, 2, 1, 'Elle est grande')
  , (4, 3, 3, 'Il aime le cuissot de chevreuil')
  , (4, 4, 3, 'Il aime aussi le cuissot de veau')
  , (4, 5, 3, 'C''est une imbécillité')
  ;
  
-- SELECT * FROM PHRASE ; 

INSERT INTO POSITION (RegleId, PhraseId, Position) VALUES
    (1, 1, 5)
  , (1, 2, 7)
  , (1, 2, 8)  
  , (1, 3, 3)
  , (1, 4, 4)
  , (1, 5, 0)
  
  , (3, 1, 0)
  , (3, 2, 0)
  , (3, 3, 0)  
  , (4, 1, 2)
  , (4, 2, 0)
  , (4, 3, 0)
  , (4, 4, 5)
  , (4, 5, 0)
;

-- SELECT * FROM POSITION ; 

-- Raoul choisit la règle 1. Son statut pour cette règle est celui par défaut ('à acquérir').
-- Le nombre de fois (Compteur) où il a satisfait à l'ensemble des phrase de cette règle est égal à 0 par défaut. 
    
INSERT INTO CHOISIR (UtilisateurId, RegleId) VALUES
    (2, 1) 
;

SELECT *, 'on amorce. Le statut doit être à ''à acquérir'' et Compteur = 0' FROM CHOISIR ; 

INSERT INTO REPONDRE (UtilisateurId, RegleId, PhraseId, Position) VALUES 
    (2, 1, 1, 5)  -- bonne réponse 1re
  , (2, 1, 1, 5)  -- bonne réponse 2e
  , (2, 1, 1, 5)  -- bonne réponse 3e
  , (2, 1, 1, 5)  -- bonne réponse 4e 
  , (2, 1, 1, 5)  -- bonne réponse 5e
  , (2, 1, 1, 5)  -- bonne réponse 6e  
  
  , (2, 1, 2, 7) -- bonne réponse 1re
  , (2, 1, 2, 7) -- bonne réponse 2e
  , (2, 1, 2, 0)   -- erreur => Raoul n'a pas tout acquis 3 fois
  
  , (2, 1, 3, 3)  
  , (2, 1, 3, 3)
  , (2, 1, 3, 3)  
  
  , (2, 1, 4, 4)
  , (2, 1, 4, 4)
  , (2, 1, 4, 4)  
  
  , (2, 1, 5, 0)
  , (2, 1, 5, 0) 
  , (2, 1, 5, 0)  
;

SELECT *, 'Le statut doit encore être à ''acquisition en cours'' et Compteur = 2' FROM CHOISIR ;  
SELECT * FROM REPONDRE ;

SELECT *, '' AS 'CHOISIR'  FROM CHOISIR ;

-- Cette fois-ci Raoul ne se plante pas

-- INSERT INTO REPONDRE (UtilisateurId, RegleId, PhraseId, Position) VALUES (2, 1, 2, 9) ;  -- => acquisition reste à 2  
-- INSERT INTO REPONDRE (UtilisateurId, RegleId, PhraseId, Position) VALUES (2, 1, 2, 7) ;  -- => acquisition passe à 3 
INSERT INTO REPONDRE (UtilisateurId, RegleId, PhraseId, Position) VALUES (2, 1, 2, 8) ;  -- => acquisition passe à 3 

SELECT *, '' AS 'CHOISIR'  FROM CHOISIR ;
SELECT *, '' AS 'REPONDRE' FROM REPONDRE ;

Je vais essayer de me dépatouiller dans le marais de la partie examens...

En attendant, ne touchez pas à la table PHRASE dans les scripts concernant les examens !

**fsmrel** · 31/10/2015, 23h13

Bonsoir almoha,

En ce qui concerne la partie Examens :

Je confirme la modification du schéma, avec le transfert de l’attribut Position dans la table POSITION :

D’où les structures en SQL, reprises du message précédent (entraînement) :


CREATE TABLE PHRASE 
(
        RegleId              INT             NOT NULL
      , PhraseId             INT             NOT NULL
      , DifficulteId         INT             NOT NULL
      , Texte                VARCHAR(96)     NOT NULL
    , CONSTRAINT PHRASE_PK PRIMARY KEY (RegleId, PhraseId)
    , CONSTRAINT PHRASE_REGLE_FK FOREIGN KEY (RegleId)
      REFERENCES REGLE (RegleId)
    , CONSTRAINT PHRASE_DIFFICULTE_FK FOREIGN KEY (DifficulteId)
      REFERENCES DIFFICULTE (DifficulteId)
) ;

CREATE TABLE POSITION 
(
        RegleId              INT             NOT NULL
      , PhraseId             INT             NOT NULL
      , Position             INT             NOT NULL
    , CONSTRAINT POSITION_PK PRIMARY KEY (RegleId, PhraseId, Position)
    , CONSTRAINT POSITION_PHRASE_FK FOREIGN KEY (RegleId, PhraseId)
      REFERENCES PHRASE (RegleId, PhraseId) ON DELETE CASCADE
) ;

La vue INTERROGATION_T est modifiée pour prendre en compte la table POSITION :


CREATE VIEW INTERROGATION_T (UtilisateurId, QuestionnaireId, Iteration, Score) AS
    SELECT x.UtilisateurId, x.QuestionnaireId, x.Iteration, COUNT(*)*5 AS Score
    FROM   SELECTION AS x 
    WHERE  EXISTS (SELECT ''
                   FROM   POSITION AS y    
                   WHERE  x.Regleid = y.Regleid AND x.PhraseId = y.PhraseId AND  x.Position = y.Position)  
    GROUP BY UtilisateurId, QuestionnaireId, Iteration
;

Au cas où, même chose pour le trigger SELECTION_AFTER_INSERT :


CREATE TRIGGER SELECTION_AFTER_INSERT AFTER INSERT ON SELECTION 
FOR EACH ROW
    BEGIN
        UPDATE INTERROGATION
            SET Score = Score + 5 
                 WHERE UtilisateurId = new.UtilisateurId
                   AND QuestionnaireId = new.QuestionnaireId
                   AND Iteration = new.Iteration
                   AND new.RegleId = (SELECT RegleId FROM PHRASE WHERE RegleId = new.RegleId and PhraseId = new.PhraseId) 
                   AND new.PhraseId = (SELECT PhraseId FROM PHRASE WHERE RegleId = new.RegleId and PhraseId = new.PhraseId) 
                   AND new.Position IN (SELECT Position FROM POSITION WHERE RegleId = new.RegleId and PhraseId = new.PhraseId)
;
    END
GO

Sans oublier la procédure ScoreCandidatCalcul :


CREATE PROCEDURE ScoreCandidatCalcul
(
   IN RaoulCodeIn CHAR(16), QuestionaireCodeIn CHAR(4), IterationIn INT
)

BEGIN
    SELECT COUNT(*) * 5 AS ScoreRaoul
    FROM   SELECTION AS x JOIN UTILISATEUR AS z ON x.UtilisateurId = z.UtilisateurId
                          JOIN QUESTIONNAIRE AS t ON x.QuestionnaireId = t.QuestionnaireId
    WHERE  UtilisateurCode = RaoulCodeIn AND QuestionnaireCode = QuestionaireCodeIn AND Iteration = IterationIn
      AND  EXISTS (SELECT ''
                   FROM   POSITION AS y
                   WHERE  x.RegleId = y.RegleId AND x.PhraseId = y.PhraseId AND x.Position = y.Position)             
    GROUP BY x.UtilisateurId, x.QuestionnaireId, x.Iteration
;

END

GO

Un paquet avec jeu d’essai :


-- ---------------------------------------------------------------------------------
--
--   Examens : positions multiples 
--
-- ---------------------------------------------------------------------------------

USE almoha ;

DROP VIEW IF EXISTS INTERROGATION_T ;
DROP VIEW IF EXISTS INTERROGATION_SCORE ;

DROP TABLE IF EXISTS SELECTION ;
DROP TABLE IF EXISTS INTERROGATION ;
DROP TABLE IF EXISTS QUESTIONNAIRE_PHRASE ;
DROP TABLE IF EXISTS QUESTIONNAIRE ;
DROP TABLE IF EXISTS PHRASES_NB ;

DROP TABLE IF EXISTS REPONDRE ;
DROP TABLE IF EXISTS CHOISIR ;

DROP TABLE IF EXISTS UTILISATEUR ;
DROP TABLE IF EXISTS CORRIGE ;
DROP TABLE IF EXISTS POSITION ;
DROP TABLE IF EXISTS PHRASE ;
DROP TABLE IF EXISTS DIFFICULTE ;
DROP TABLE IF EXISTS COMPOSITION ;
DROP TABLE IF EXISTS REGLE ;
DROP TABLE IF EXISTS ELEMENT ;
DROP TABLE IF EXISTS TYPE_ELEMENT ;

-------------------------------------------------------------------------------------------------

CREATE TABLE TYPE_ELEMENT 
(
        TypeElementId        INT             NOT NULL
      , TypeElementLibelle   VARCHAR(16)     NOT NULL
    , CONSTRAINT TYPE_ELEMENT_PK PRIMARY KEY (TypeElementId)
) ;

CREATE TABLE ELEMENT 
(
        ElementId            INT             NOT NULL
      , TypeElementId        INT             NOT NULL
      , ElementTexte         VARCHAR(64)     NOT NULL
    , CONSTRAINT ELEMENT_PK PRIMARY KEY (ElementId)
    , CONSTRAINT ELEMENT_TYPE_ELEMENT_FK FOREIGN KEY (TypeElementId)
      REFERENCES TYPE_ELEMENT (TypeElementId) ON DELETE CASCADE
) ;

CREATE TABLE COMPOSITION 
(
        ElementComposantId       INT             NOT NULL
      , ElementComposeId         INT             NOT NULL
    , CONSTRAINT COMPOSITION_ELEMENT_COMPOSANT_FK FOREIGN KEY (ElementComposantId)
      REFERENCES ELEMENT (ElementId) ON DELETE CASCADE
    , CONSTRAINT COMPOSITION_ELEMENT_COMPOSE_FK FOREIGN KEY (ElementComposeId)
      REFERENCES ELEMENT (ElementId)      
) ;

CREATE TABLE REGLE 
(
        RegleId              INT             NOT NULL
      , ElementId            INT             NOT NULL        
      , RegleTexte           VARCHAR(64)     NOT NULL
      , NbPhrases            INT             NOT NULL
    , CONSTRAINT REGLE_PK PRIMARY KEY (RegleId)
    , CONSTRAINT REGLE_ELEMENT_FK FOREIGN KEY (ElementId)
      REFERENCES ELEMENT (ElementId)
 ) ;
 
 CREATE TABLE DIFFICULTE 
(
        DifficulteId         INT             NOT NULL
      , DifficulteNiveau     INT             NOT NULL DEFAULT 1	
      , DifficulteTexte      VARCHAR(32)     NOT NULL
    , CONSTRAINT DIFFICULTE_PK PRIMARY KEY (DifficulteId)
    , CONSTRAINT DIFFICULTE_AK UNIQUE (DifficulteNiveau)    
) ;

CREATE TABLE PHRASE 
(
        RegleId              INT             NOT NULL
      , PhraseId             INT             NOT NULL
      , DifficulteId         INT             NOT NULL
      , Texte                VARCHAR(96)     NOT NULL
    , CONSTRAINT PHRASE_PK PRIMARY KEY (RegleId, PhraseId)
    , CONSTRAINT PHRASE_AK UNIQUE (RegleId, PhraseId, DifficulteId)
    , CONSTRAINT PHRASE_REGLE_FK FOREIGN KEY (RegleId)
      REFERENCES REGLE (RegleId)
    , CONSTRAINT PHRASE_DIFFICULTE_FK FOREIGN KEY (DifficulteId)
      REFERENCES DIFFICULTE (DifficulteId)
) ;

CREATE TABLE POSITION 
(
        RegleId              INT             NOT NULL
      , PhraseId             INT             NOT NULL
      , Position             INT             NOT NULL
    , CONSTRAINT POSITION_PK PRIMARY KEY (RegleId, PhraseId, Position)
    , CONSTRAINT POSITION_PHRASE_FK FOREIGN KEY (RegleId, PhraseId)
      REFERENCES PHRASE (RegleId, PhraseId) ON DELETE CASCADE
) ;

CREATE TABLE PHRASES_NB 
(
        PhrasesNbId          INT             NOT NULL
      , PhrasesNb            INT             NOT NULL
    , CONSTRAINT PHRASES_NB_PK PRIMARY KEY (PhrasesNbId)
    , CONSTRAINT PHRASES_NB_AK UNIQUE (PhrasesNb)
) ;

CREATE TABLE QUESTIONNAIRE 
(
        QuestionnaireId      INT             NOT NULL
      , QuestionnaireCode    CHAR(4)         NOT NULL
      , PhrasesNbId          INT             NOT NULL
    , CONSTRAINT QUESTIONNAIRE_PK PRIMARY KEY (QuestionnaireId)
    , CONSTRAINT QUESTIONNAIRE_AK UNIQUE (QuestionnaireCode)
    , CONSTRAINT QUESTIONNAIRE_PHRASES_NB_FK FOREIGN KEY (PhrasesNbId)
          REFERENCES PHRASES_NB (PhrasesNbId)
) ;

CREATE TABLE QUESTIONNAIRE_PHRASE 
(
        QuestionnaireId      INT             NOT NULL
      , RegleId              INT             NOT NULL
      , PhraseId             INT             NOT NULL
      , DifficulteId         INT             NOT NULL
    , CONSTRAINT QUESTIONNAIRE_PHRASE_PK PRIMARY KEY (QuestionnaireId, RegleId, PhraseId)
    , CONSTRAINT QUESTIONNAIRE_PHRASE_PHRASE_FK FOREIGN KEY (RegleId, PhraseId, DifficulteId)
          REFERENCES PHRASE (RegleId, PhraseId, DifficulteId)
    , CONSTRAINT QUESTIONNAIRE_PHRASE_QUESTIONNAIRE_FK FOREIGN KEY (QuestionnaireId)
          REFERENCES QUESTIONNAIRE (QuestionnaireId) ON DELETE CASCADE
) ;

CREATE TABLE UTILISATEUR
(
        UtilisateurId        INT             NOT NULL
      , UtilisateurCode      CHAR(16)        NOT NULL        
      , UtilisateurNom       VARCHAR(32)     NOT NULL         
    , CONSTRAINT UTILISATEUR_PK PRIMARY KEY (UtilisateurId) 
    , CONSTRAINT UTILISATEUR_AK UNIQUE (UtilisateurCode)     
) ;

CREATE TABLE INTERROGATION 
(
        UtilisateurId        INT             NOT NULL
      , QuestionnaireId      INT             NOT NULL
      , Iteration            INT             NOT NULL DEFAULT 0
      , DateExamen           DATE            NOT NULL
      , Score                INT             NOT NULL DEFAULT 0
      , CONSTRAINT INTERROGATION_PK PRIMARY KEY (UtilisateurId, QuestionnaireId, Iteration)
      , CONSTRAINT INTERROGATION_UTILISATEUR_FK FOREIGN KEY (UtilisateurId)
            REFERENCES UTILISATEUR (UtilisateurId) 
      , CONSTRAINT INTERROGATION_QUESTIONNAIRE_FK FOREIGN KEY (QuestionnaireId)
            REFERENCES QUESTIONNAIRE (QuestionnaireId)
) ;

CREATE TABLE SELECTION 
(
        UtilisateurId        INT             NOT NULL
      , QuestionnaireId      INT             NOT NULL
      , Iteration            INT             NOT NULL
      , RegleId              INT             NOT NULL
      , PhraseId             INT             NOT NULL
      , Position             INT             NOT NULL      
      , CONSTRAINT SELECTION_PK PRIMARY KEY (UtilisateurId, QuestionnaireId, Iteration, RegleId, PhraseId)
      , CONSTRAINT SELECTION_INTERROGATION_FK FOREIGN KEY (UtilisateurId, QuestionnaireId, Iteration)
            REFERENCES INTERROGATION (UtilisateurId, QuestionnaireId, Iteration) 
      , CONSTRAINT SELECTION_QUESTIONNAIRE_PHRASE_FK FOREIGN KEY (QuestionnaireId, RegleId, PhraseId)
            REFERENCES QUESTIONNAIRE_PHRASE (QuestionnaireId, RegleId, PhraseId)
) ;

COMMIT ;

DELIMITER GO

DROP TRIGGER IF EXISTS INTERROGATION_BEFORE_INSERT ;

GO

CREATE TRIGGER INTERROGATION_BEFORE_INSERT BEFORE INSERT ON INTERROGATION 
FOR EACH ROW
    BEGIN
        SET new.Iteration = (SELECT COALESCE(MAX(Iteration) + 1, 1)
                             FROM   INTERROGATION
                             WHERE  UtilisateurId = new.UtilisateurId
                               AND  QuestionnaireId = new.QuestionnaireId
                            ) ;        
    END
GO 

DELIMITER ;

INSERT INTO TYPE_ELEMENT (TypeElementId, TypeElementLibelle) VALUES 
    (1, 'Partie'), (2, 'Sous-partie'), (3, 'Rubrique') ; 
    
INSERT INTO ELEMENT (ElementId, TypeElementId, ElementTexte) VALUES
    (1, 1, 'Formes et accords du verbe')
  , (2, 1, 'Formes et accords du nom, de l''adjectif et de l''adverbe')
  , (3, 1, 'Orthographe lexicale, signes graphiques et syntaxe')
  , (4, 1, 'Ces mots que l''on confond')
  , (5, 2, 'Le verbe : ses formes')
  , (6, 2, 'Syntaxe')
  , (7, 3, 'L''infinitif')
  , (8, 3, 'Les noms : leur féminin, leur pluriel')
  , (9, 3, 'L''imparfait du subjonctif')    
  , (10, 2, 'Le verbe, ses pièges')
  , (14, 3, 'Rubrique-à-brac')
  , (15, 2, 'sous-partie, piège à AK de PILE')  
  , (91, 1, 'Piège à récursivité')   , (92, 1, 'Piège à récursivité')
  , (93, 2, 'Piège à récursivité')
 ;
 
INSERT INTO COMPOSITION  (ElementComposantId, ElementComposeId) VALUES
     (5,1), (6, 3), (7, 5), (8, 2), (9, 5), (10, 1), (93, 92), (14, 1), (15, 1) 
     ;
 
INSERT INTO REGLE (RegleId, ElementId, RegleTexte, NbPhrases) VALUES
    (1, 8, 'Le pluriel des noms : cas généraux', 5)
  , (2, 6, 'est-ce que', 15)
  , (3, 4, 'a ; à', 2)
  , (4, 4, 'ou ; où', 2)  
  , (5, 4, 'or ; hors', 4)  
  , (6, 9, 'La 3e personne du singulier', 5)
  , (7, 9, 'La 3e personne du pluriel', 3) 
  , (8, 5, 'Une règle de la forme spéciale du verbe', 2)
  , (9, 1, 'Accord dissonant du verbe', 2)
  , (10, 14, 'règle-à-brac',5) 
  , (11, 10, 'rg11, enfant de SP10', 5)
  , (12, 10, 'rg12, enfant de SP10', 5)
  , (13, 10, 'rg13, enfant de SP10', 5)
  , (14, 10, 'rg14, enfant de SP10', 5)
  , (15, 1, 'règle, piège à AK de PILE', 2)
  ;

INSERT INTO DIFFICULTE (DifficulteId, DifficulteNiveau, DifficulteTexte) VALUES
    (1, 1, 'Niveau 1'), (2, 2, 'Niveau 2'), (3, 3, 'Niveau 3') ;  
 
 -- SELECT * FROM DIFFICULTE ;

INSERT INTO PHRASE (RegleId, PhraseId, DifficulteId, Texte) VALUES
    (1, 1, 1,  'rg1, ph1, le petit chat est morts')

  , (1, 2, 2, 'Cette cérémonie reste un moment mémorable, au temps pour moi que pour mes enfants')  

  , (1, 3, 1,  'rg1, ph3, des yeux marrons')
  , (1, 4, 2,  'rg1, ph4, il est huit heure')
  , (1, 5, 3,  'rg1, ph5, Huit cent deux kilos')
  , (3, 1, 2,  'rg3, ph1 , Je vais à Paris')
  , (3, 2, 2,  'rg3, ph2, Pierre a faim')
  , (3, 3, 3,  'rg3, ph3, Ce crayon est a moi') 
  , (4, 1, 1,  'rg4, Il et fort et agile')
  , (4, 2, 1, 'rg4, Elle est grande')
  , (4, 3, 3, 'rg4, Il aime le cuissot de chevreuil')
  , (4, 4, 3, 'rg4, Il aime aussi le cuissot de veau')
  , (4, 5, 3, 'rg4, C''est une imbécillité')
  , (5, 1, 2, 'rg5, ph1')
  , (5, 2, 2, 'rg5, ph2')
  , (6, 1, 2, 'rg6, ph1, Qu''il aimât cela !')
  , (6, 2, 3, 'rg6, ph2, Qu''il n''aimat pas cela !')
  , (6, 3, 2, 'rg6, ph3, Qu''il eût aimé')
  , (7, 1, 3, 'rg7, ph1, Qu''ils cinématographiassent cela !')  
  , (7, 2, 3, 'rg7, ph2, Qu''ils n''aimassent pas cela !')
  , (7, 3, 2, 'rg7, ph3, Fallait-il que je vous aimasse')
  , (7, 4, 2, 'rg7, ph4, et que je vous idolâtrasse')
  , (7, 5, 2, 'rg7, ph5, pour que vous m''assassinassiez')
  , (8, 1, 2, 'rg8, ph1')
  , (8, 2, 2, 'rg8, ph2')
  , (9, 1, 2, 'rg9, ph1')
  , (9, 2, 1, 'rg9, ph2')
  , (9, 3, 2, 'rg9, ph3')
  , (10, 1, 2, 'rg10, ph1')
  , (10, 2, 2, 'rg10, ph2')
  , (10, 3, 1, 'Le Biglotron est un extrordinaire appareil')
  , (10, 4, 3, 'Le flugdug prenant appui sur la muffée du connecteur à rustine')  
  , (11, 1, 2, 'rg11, ph1')
  , (11, 2, 2, 'rg11, ph2')
  , (12, 1, 2, 'rg12, ph1')
  , (12, 2, 2, 'rg12, ph2')
  , (13, 1, 2,  'rg13, ph1')
  , (13, 2, 2, 'rg13, ph2')
  , (14, 1, 2, 'rg14, ph1')
  , (14, 2, 2, 'rg14, ph2')
;
 SELECT *, rand() as alea FROM PHRASE order by DifficulteId, alea ; 

INSERT INTO POSITION (RegleId, PhraseId, Position) VALUES
    (1, 1, 5)

  , (1, 2, 7)   -- multiple : 'au'
  , (1, 2, 8)  --  multiple : 'temps' 

  , (1, 3, 3)
  , (1, 4, 4)
  , (1, 5, 0)
  
  , (3, 1, 0)
  , (3, 2, 0)
  , (3, 3, 0)

  , (4, 1, 2)
  , (4, 2, 0)
  , (4, 3, 0)
  , (4, 4, 5)
  , (4, 5, 0)
  
  , (5, 1, 0)
  , (5, 2, 0)
  
  , (6, 1, 0)
  , (6, 2, 0)
  , (6, 3, 0)
  
  , (7, 1, 0)
  , (7, 2, 2)
  , (7, 3, 2)
  , (7, 4, 0)
  , (7, 5, 0)
  
  , (8, 1, 0)
  , (8, 2, 2)
  
  , (9, 1, 0)
  , (9, 2, 0)
  , (9, 3, 0)
  
  , (10, 1, 0)
  , (10, 2, 0)
  , (10, 3, 5)
  , (10, 4, 0)
  
  , (11, 1, 0)
  , (11, 2, 0)
  
  , (12, 1, 0)
  , (12, 2, 0)
  
  , (13, 1, 0)
  , (13, 2, 0)
  
  , (14, 1, 0)
  , (14, 2, 0)
 
 ;

-- SELECT * FROM POSITION ; 

INSERT INTO PHRASES_NB (PhrasesNbId, PhrasesNb) VALUES
    (1, 50), (2, 100), (3, 200) ;

INSERT INTO QUESTIONNAIRE (QuestionnaireId, QuestionnaireCode, PhrasesNbId)
    VALUES 
    (1, 'Q001', 1) 
  , (2, 'Q002', 2)
  , (3, 'Q003', 1)  
; 

INSERT INTO QUESTIONNAIRE_PHRASE (QuestionnaireId, RegleId, PhraseId, DifficulteId)
    VALUES 
    (1, 1, 1, 1) , (1, 1, 2, 2), (1, 1, 3, 1), (1, 10, 3, 1), (1, 10, 4, 3)  
  , (2, 1, 1, 1) , (2, 1, 2, 2), (2, 1, 3, 1), (2, 10, 3, 1), (2, 10, 4, 3)  
; 

SELECT *, RAND() AS Alea FROM QUESTIONNAIRE_PHRASE ORDER BY QuestionnaireId, DifficulteId, Alea ;

INSERT INTO UTILISATEUR (UtilisateurId, UtilisateurCode, UtilisateurNom) VALUES
    (1, 'FN', 'Fernand'), (2, 'RV', 'Raoul'), (3, 'PV', 'Paul'), (4, 'almoha', 'Alain'), (5, 'fsmrel', 'François')
;

CREATE VIEW INTERROGATION_T (UtilisateurId, QuestionnaireId, Iteration, Score) AS
    SELECT x.UtilisateurId, x.QuestionnaireId, x.Iteration, COUNT(*)*5 AS Score
    FROM   SELECTION AS x 
    WHERE  EXISTS (SELECT ''
                   FROM   POSITION AS y    
                   WHERE  x.Regleid = y.Regleid AND x.PhraseId = y.PhraseId AND  x.Position = y.Position)  
    GROUP BY UtilisateurId, QuestionnaireId, Iteration
;


CREATE VIEW INTERROGATION_SCORE (UtilisateurCode, QuestionnaireCode, Iteration, Score) AS
    SELECT UtilisateurCode, QuestionnaireCode, Iteration, Score
    FROM   UTILISATEUR AS u JOIN INTERROGATION_T AS t ON u.UtilisateurId = t.UtilisateurId
                            JOIN QUESTIONNAIRE AS v ON v.QuestionnaireId = t.QuestionnaireId
;

DELIMITER GO

DROP TRIGGER IF EXISTS SELECTION_AFTER_INSERT 

GO

CREATE TRIGGER SELECTION_AFTER_INSERT AFTER INSERT ON SELECTION 
FOR EACH ROW
    BEGIN
        UPDATE INTERROGATION
            SET Score = Score + 5 
                 WHERE UtilisateurId = new.UtilisateurId
                   AND QuestionnaireId = new.QuestionnaireId
                   AND Iteration = new.Iteration
                   AND new.RegleId = (SELECT RegleId FROM PHRASE WHERE RegleId = new.RegleId and PhraseId = new.PhraseId) 
                   AND new.PhraseId = (SELECT PhraseId FROM PHRASE WHERE RegleId = new.RegleId and PhraseId = new.PhraseId) 
                   AND new.Position IN (SELECT Position FROM POSITION WHERE RegleId = new.RegleId and PhraseId = new.PhraseId)
;
    END
GO 

DELIMITER ;

INSERT INTO INTERROGATION (UtilisateurId, QuestionnaireId, DateExamen) VALUES

    (1, 1, '2015-10-14'), (1, 1, '2015-10-16')   -- Fernand
  , (2, 1, '2015-10-15'), (2, 1, '2015-10-15')   -- Raoul
  , (2, 2, '2015-10-17'), (2, 2, '2015-10-18')   -- Raoul  
;

-- vérif auto-incrémentation de Iteration

SELECT *, '' AS R1 FROM INTERROGATION ;

INSERT INTO SELECTION (UtilisateurId, QuestionnaireId, Iteration, RegleId, PhraseId, Position) VALUES

    (1, 1, 1, 1, 2, 7)     -- Fernand, Q1, iter1, Rg1, Ph2, Pos 7          ok
  , (1, 1, 1, 10, 3, 5)    -- Fernand, Q1, iter1, Rg10, Ph3, Pos 5         ok
  , (1, 1, 1, 10, 4, 99)   -- Fernand, Q1, iter1, Rg10, Ph4, Pos 99        erreur

  , (2, 1, 1, 1, 2, 99)   --  Raoul, Q1, iter1, Rg1, Ph2, Pos 99            erreur
  , (2, 1, 1, 10, 3, 99)   -- Raoul, Q1, iter1, Rg10, Ph3, Pos 99           erreur
  , (2, 1, 1, 10, 4, 99)   -- Raoul, Q1, iter1, Rg10, Ph4, Pos 99           erreur

  , (2, 1, 2, 1, 2, 8)    --  Raoul, Q1, iter2, Rg1, Ph2, Pos 8             ok
  
  , (2, 2, 1, 1, 2, 8)    --  Raoul, Q2, iter1, Rg1, Ph2, Pos 8           ok            
  , (2, 2, 1, 10, 3, 99)  --  Raoul, Q2, iter1, Rg10, Ph3, Pos 99         erreur
  , (2, 2, 1, 10, 4, 99)   -- Raoul, Q2, iter1, Rg10, Ph4, Pos 99         erreur

  , (2, 2, 2, 1, 2, 7)    --  Raoul, Q2, iter2, Rg1, Ph2, Pos 7           ok 
  , (2, 2, 2, 10, 3, 5)   --  Raoul, Q2, iter2, Rg10, Ph3, Pos 5          ok          
  , (2, 2, 2, 10, 4, 0)   --  Raoul, Q2, iter2, Rg10, Ph4, Pos 0          ok  
;

SELECT * FROM SELECTION ;

SELECT * FROM INTERROGATION_T ;

-- ------------------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------------

SELECT *, '' AS 'select simple' FROM   SELECTION AS x JOIN PHRASE AS y ON x.RegleId = y.RegleId AND x.PhraseId = y.PhraseId ;

-- Généralisation à un candidat quelconque

DELIMITER GO 

DROP PROCEDURE IF EXISTS ScoreCandidatCalcul 

GO

CREATE PROCEDURE ScoreCandidatCalcul
(
   IN RaoulCodeIn CHAR(16), QuestionaireCodeIn CHAR(4), IterationIn INT
)

BEGIN
    SELECT COUNT(*) * 5 AS ScoreRaoul
    FROM   SELECTION AS x JOIN UTILISATEUR AS z ON x.UtilisateurId = z.UtilisateurId
                          JOIN QUESTIONNAIRE AS t ON x.QuestionnaireId = t.QuestionnaireId
    WHERE  UtilisateurCode = RaoulCodeIn AND QuestionnaireCode = QuestionaireCodeIn AND Iteration = IterationIn
      AND  EXISTS (SELECT ''
                   FROM   POSITION AS y
                   WHERE  x.RegleId = y.RegleId AND x.PhraseId = y.PhraseId AND x.Position = y.Position)             
    GROUP BY x.UtilisateurId, x.QuestionnaireId, x.Iteration
;

END

GO

DELIMITER ;

CALL ScoreCandidatCalcul('RV', 'Q002', 2) ;

--- Variante

SELECT *, '' AS ScorevARIANTEALLLL
    FROM   INTERROGATION_SCORE ;

DELIMITER GO 

DROP PROCEDURE IF EXISTS ScoreCandidatCalculVariante

GO

CREATE PROCEDURE ScoreCandidatCalculVariante
(
   IN RaoulCodeIn CHAR(16), QuestionaireCodeIn CHAR(4), IterationIn INT
)

BEGIN
    SELECT Score
    FROM   INTERROGATION_SCORE 
    WHERE  UtilisateurCode = RaoulCodeIn AND QuestionnaireCode = QuestionaireCodeIn AND Iteration = IterationIn    
    ;
END

GO

DELIMITER ;

CALL ScoreCandidatCalculVariante('RV','Q002', 2) ;

SELECT * FROM INTERROGATION_SCORE ;

SELECT * FROM INTERROGATION ;

DELIMITER GO 

DROP PROCEDURE IF EXISTS ScoronsJoyeusement 

GO

CREATE PROCEDURE ScoronsJoyeusement
(
   IN RaoulCodeIn CHAR(16), QuestionaireCodeIn CHAR(4), IterationIn INT
)

BEGIN
    UPDATE INTERROGATION
        SET Score = (SELECT COALESCE(MAX(Score), 0)
                     FROM   INTERROGATION_SCORE
                     WHERE  UtilisateurCode = RaoulCodeIn AND QuestionnaireCode = QuestionaireCodeIn AND  Iteration = IterationIn)
            WHERE UtilisateurId = (SELECT UtilisateurId FROM UTILISATEUR WHERE UtilisateurCode = RaoulCodeIn)
              AND QuestionnaireId = (SELECT QuestionnaireId FROM QUESTIONNAIRE WHERE QuestionnaireCode = QuestionaireCodeIn)
              AND Iteration = IterationIn
;              
END

GO

DELIMITER ;

UPDATE INTERROGATION SET Score = 0 WHERE UtilisateurId = 2  ; -- on efface les calcul de Raoul et on recalcule 

CALL ScoronsJoyeusement('RV', 'Q001', 1) ;   

CALL ScoronsJoyeusement('RV', 'Q001', 2) ;

SELECT *, '' AS 'Proc Scorons 1' FROM INTERROGATION  ;

CALL ScoronsJoyeusement('RV', 'Q002', 1) ;

SELECT *, '' AS 'Proc Scorons 2' FROM INTERROGATION  ;

CALL ScoronsJoyeusement('RV', 'Q002', 2) ;

SELECT *, '' AS 'Proc Scorons 3' FROM INTERROGATION ;

Aux erreurs de copier/coller près...

Modélisation catégories, sous-catégories, rubriques

Schéma

Discussions similaires

Partager

Partager