Discussion :

[domlysz]

Bonjour à tous,

Je travail sur une base de données concernant des courses de lévrier. La partie qui me pose problème est la suivante : un lévrier est engagé dans une épreuve qui regroupe plusieurs lévriers. Au cours d'une épreuve est organisé plusieurs courses qui rassemblent chacune un à plusieurs lévrier. Chaque lévrier engagé participe donc à une ou plusieurs courses.

Voilà ma proposition de MCD:

[LEVRIER]----1,N----(ENGAGER)----1,N----[EPREUVE]----1,N----(ORGANISER)----1,1----[COURSE]
|---------------1,N-----------------(PARTICIPER)--------------1,N-----------------|

Ce qui me chagrine c'est la boucle ainsi formée. L'intérêt de boucler LEVRIER avec COURSE est que la table PARTICIPER obtenue portera les résultats de chaque lévrier pour chaque course (temps, classement ...). En revanche le risque est qu'un lévrier participe à une course organisée dans une épreuve pour laquelle il n'a pas été engagé. Je me demande donc s'il n'y a pas une modélisation alternative plus élégante.

Qu'en pensez-vous ?

[fsmrel]

Bonjour domlysz,


On va tâcher moyen de regarder ça...

Je selle mon cheval et j'arrive au galop ^^

[fsmrel]

Vous avez systématiquement des cardinalités 1,N, ce qui signifie, entre autres, que chaque lévrier a participé au moins à une épreuve. N’y a-t-il pas des lévriers qui n’ont pas encore participé à leur 1re course ? (voire même qui n'ont encore jamais été inscrits à une épreuve?)

[domlysz]

Merci fsmrel pour cette réactivité je n'en attendais pas tant ^^

Concernant les cardinalités, je ne renseigne un nouveau lévrier que s'il a été inscrit à au moins une épreuve. En revanche il est possible qu'un lévrier engagé soit déclaré absent ou inapte auquel cas il n'aura participé à aucune course de l'épreuve, il faut donc effectivement modifier un peu tout ça.

[fsmrel]

Le MCD devrait comporter une contrainte d’inclusion, symbolisant la contrainte :

Un lévrier ne peut participer à une course que s’il est engagé dans une épreuve à laquelle appartient cette course.

Cela dit, si on peut se contenter de dire :

L’engagement d’un lévrier dans une épreuve est connu parce qu’il participera à telle course (non encore effectuée) de cette épreuve, l’association ENGAGEMENT devient inutile.

Mais si l’on tient à ce que l’engagement soit connu, même sans participation du lévrier à une course, alors l’association doit être conservée.

Dans ces conditions, si vous utilisez WinDesign qui permet de modéliser la contrainte d’inclusion, celui-ci devrait générer les triggers qui vont bien pour le niveau SQL. Mais on peut s’en sortir au niveau MLD, sans avoir ensuite à mettre des triggers en œuvre, en modélisant avec MySQL Workbench (gratuit) :

On identifie COURSE relativement à EPREUVE, ce qui permet de propager l’attribut EpreuveId jusqu’à PARTICIPATION, table alors porteuse à la fois d’une clé étrangère référençant ENGAGEMENT et d’une clé étrangère référençant COURSE, clés étrangères contenant toutes deux l’attribut EpreuveId : dans ces conditions, un lévrier ne peut pas participer à une course sans avoir d’abord été engagé dans l’épreuve à laquelle appartient cette course :



Je reste à l'écoute...

Et n’oubliez pas de voter quand une réponse a pu vous aider...

[domlysz]

Merci beaucoup pour cette réponse très intéressante.

J'ai encore quelques difficultés à bien appréhender le mécanisme mise en place dans le MLD. Voilà comment je le comprends : pour qu'un nouvel enregistrement dans la table PARTICIPATION soit valide il faut que le couple ( id_epreuve, id_levrier ) existe dans la table ENGAGEMENT, donc que le lévrier soit effectivement engagé pour cette épreuve. Il faut également que le couple ( id_course, id_epreuve ) existe dans la table COURSE ce qui permet de valider que la course appartient bien à l'épreuve pour laquelle le lévrier a été engagé puisque id_epreuve fait référence aux deux tables ENGAGEMENT et COURSE .

En conséquence mon code SQL pour créer la table PARTICIPATION est celui-ci :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE PARTICIPATION ( 
    id_levrier  INTEGER,
    id_epreuve     INTEGER,
    id_course     INTEGER,
    temps      INTEGER,
    classement INTEGER,
    PRIMARY KEY ( id_levrier, id_epreuve, id_course ),
    FOREIGN KEY ( id_epreuve, id_levrier ) REFERENCES engagement ( id_epreuve, id_levrier ),
    FOREIGN KEY ( id_course, id_epreuve ) REFERENCES course ( id_course, id_epreuve )
);

Néanmoins cela semble complètement verrouiller les insertions dans la table, j'ai le sentiment de mal interpréter le MLD.

[fsmrel]

Bonjour domlysz,

Néanmoins cela semble complètement verrouiller les insertions dans la table, j'ai le sentiment de mal interpréter le MLD.

Qu’entendez-vous par « verrouiller » ?

Selon le MLD, un lévrier — appelons-le Zaza — peut être engagé dans l’épreuve E1 (table ENGAGEMENT) sans pour autant avoir participé à une course quelconque faisant partie de cette épreuve : normalement, jusque-là tout va bien, on ne verrouille rien.

Dès qu’on sait que Zaza va participer à la course 3 de cette épreuve, on peut procéder à l’ajout d’une ligne dans la table PARTICIPATION puisqu’on dispose du triplet qui va bien <"Zaza", "E1", "3">.

Si incertitude il y a, cela vaut pour le temps réalisé par Zaza et son classement. En effet, si l’insertion du triplet est à effectuer avant même que la course n’ait eu lieu, en toute rigueur le diagramme devrait être le suivant (une participation fait l’objet de 0 à au plus 1 résultat, et un résultat est l’objet d’une et une seule participation) :



A noter que si Zaza participe en principe à la course <"E1", "3"> et a déclaré forfait (ou n’ pas terminé), elle doit avoir un résultat et son classement et son temps peuvent par exemple valoir zéro.


Concernant votre CREATE TABLE :

Attention à l’ordre des attributs dans les clés étrangères de la table ENGAGEMENT : il doit être le même que celui de la clé (disons primaire) des tables référencées. Par ailleurs on utilise l’identification relative, c'est-à-dire que dans la paire <"E1", "3">, "3" identifie la 3e course de l’épreuve E1 et l’on peut donc aussi avoir la paire <"E7", "3"> pour identifier la 3e course de l’épreuve E7.

[domlysz]

Merci encore pour ces nouveaux éléments.

J'ai homogénéiser l'ordre des champs mais rien ni fait.

Voici un extrait de ma table ENGAGEMENT:

# |id_levrier|id_epreuve
---+----------+----------
1 |212 |1
2 |234 |1
3 |301 |1
4 |214 |2

et la table COURSE

# |id_epreuve|id_course
---+----------+---------
1 |1 |1
2 |1 |2
3 |1 |3
4 |1 |4
5 |2 |5
6 |2 |6


mais la requête suivante me retourne systématique foreign key mismatch - "participation" referencing "course"

Code :

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

1
2
3
4
INSERT INTO participation
(id_levrier, id_epreuve, id_course)
VALUES (212, 1, 1)

Pourtant le lévrier 212 a bien participé à l'épreuve 1, et la course 1 est bien associé à l'épreuve 1.

[fsmrel]

Ça ne doit pas etre bien méchant...

Faites-voir vos CREATE TABLE pour les tables ENGAGEMENT, COURSE et PARTICIPATION.

[fsmrel]

En regardant de plus près votre message, je constate la présence d'un attribut # pour les tables ENGAGEMENT et COURSE : n’ayant aucune valeur ajoutée (au contraire, présence par exemple d’index supplémentaires fort coûteux en mise à jour), ce genre d'attributs doit disparaître.

Exemple : TABLE ENGAGEMENT

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE ENGAGEMENT 
(
  LevrierId           INT                  NOT NULL,
  EpreuveId           INT                  NOT NULL,
  CONSTRAINT ENGAGEMENT_PK PRIMARY KEY (LevrierId, EpreuveId),
  CONSTRAINT ENGAGEMENT_LEVRIER_FK FOREIGN KEY (LevrierId)
    REFERENCES LEVRIER (LevrierId)
  CONSTRAINT ENGAGEMENT_EPREUVE_FK FOREIGN KEY (EpreuveId)
    REFERENCES EPREUVE (EpreuveId)
) ;

[domlysz]

L'attribut # n'existe pas dans les tables, c'est simplement un numéro de ligne afficher par le logiciel que j'utilise (Sqlitestudio) qui s'est répercuté lors du copier/coller.

Voici mes CREATE TABLE, mais attention mes noms de table sont différents (oui je fais des cachotteries c'est mal !).

La table "epreuve" se nomme en réalité "enc":

Code :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE enc ( 
    id_enc   INTEGER PRIMARY KEY,
    cyno     TEXT    NOT NULL,
    date     DATE    NOT NULL,
    type     TEXT,
    juge     TEXT,
    distance INTEGER 
);

La table "engagement" se nomme en réalité "enc_result" et l'attribut "id_levrier" correspond à "id_zoi"

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE enc_result ( 
    id_zoi       INTEGER REFERENCES zoi ( id_zoi ),
    id_enc       INTEGER REFERENCES enc ( id_enc ),
    classe       TEXT,
    classement   INTEGER,
    qualificatif TEXT,
    attribution  TEXT,
    PRIMARY KEY ( id_zoi, id_enc ) 
);

La table "course" se nomme en réalité "enc_course":

Code :

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

1
2
3
4
5
CREATE TABLE enc_course ( 
    id_enc    INTEGER REFERENCES enc ( id_enc ),
    id_course INTEGER PRIMARY KEY 
);

Enfin la table "participation" se nomme "enc_course_result":

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE enc_course_result ( 
    id_zoi     INTEGER,
    id_enc     INTEGER,
    id_course  INTEGER,
    temps      INTEGER,
    categorie  TEXT,
    classement INTEGER,
    PRIMARY KEY ( id_zoi, id_enc, id_course ),
    FOREIGN KEY ( id_zoi, id_enc ) REFERENCES enc_result ( id_zoi, id_enc ),
    FOREIGN KEY ( id_enc, id_course ) REFERENCES enc_course ( id_enc, id_course ) 
);

[fsmrel]

Votre table enc_course_result a une clé étrangère {id_enc, id_course} qui fait référence à la clé primaire de la table enc_cours, ce qui suppose que celle-ci a pour clé primaire la paire {id_enc, id_course}, or ce n’est pas le cas, elle est seulement le singleton {id_course} :

Il faut remplacer {id_course} par {id_enc, id_course}, au nom de l’identification relative qui permet de propager l’attribut id_enc de ce côté-ci, sachant qu’il est propagé de l’autre côté via la table enc_result.

[domlysz]

Merci beaucoup, cette fois ça fonctionne parfaitement et c'est plus clair dans mon esprit !

[fsmrel]

Bonjour domlysz,


Tout est donc pour le mieux, aussi n'oubliez pas de marquer la discussion comme étant résolue.


Bonne route !