Discussion :

[JimPear]

Bonjour à tous ,

Je dois réaliser, dans le cadre d'un stage, un site permettant à des organismes de pouvoir élire leurs représentants en ligne. N'étant pas très familier avec les bases de données, j'aurais besoin de votre avis concernant ma modélisation de base de données (schéma Merise que je vous joins avec ce message).

Je vous explique le principe du site en quelques lignes : il y aura un compte administrateur pour chaque organisme qui se chargera d'organiser chaque élection propre à l'organisme (pour quel rôle au sein de ce dernier) et de répertorier toutes les personnes pouvant voter à ces élections.
Les personnes "lambda" recevront leurs identifiants par mail et se connecteront sur le site uniquement pour voter dans le cadre des élections dans lesquelles l'admin les a répertoriés. En dehors de ça elles ne feront rien sur le site.

J'ai donc 4 tables :

• une table "Organismes" qui contient les organismes/associations
• une table "Personnes" qui regroupe toutes les personnes qui seront inscrites sur le site et faisant partis des divers organismes/associations
• une table "Elections" qui répertorie les différents événements électoraux qui seront définis par les organismes
• une table "Roles" qui contient les fonctions à pourvoir lors des élections (ce sont les rôles pour lesquels on va voter et non les rôles déjà occupés par les personnes).

Je réfléchis pas mal aux différentes contraintes à respecter et je m'embrouille pas mal au niveau de la récupération des votes de chacun. En effet, je dois pouvoir comptabiliser les votes effectués par les personnes aussi bien au premier tour de vote qu'au second, sachant que les élections seront parfois en un tour et d'autres fois en deux tours de votes.

Au niveau des contraintes, un petit récapitulatif :

• Une personne peut appartenir à plusieurs organismes
• Une personne peut voter pour plusieurs élections mais seulement aux élections organisées par son/ses organismes
• Une personne peut postuler pour plusieurs rôles au sein d'un organisme
• Une personne ne peut pas voter plusieurs fois au cours du même tour de vote
• Un organisme peut contenir plusieurs personnes
• Un organisme peut organiser plusieurs élections

Je me demande en fait s'il ne faudra pas établir un schéma récursif pour la table "Personnes" (puisqu'au final les personnes votent pour d'autres personnes .. ?) et/ou si je ne devrais pas créer une table Votes à côté ou une table permettant de répertorier qui a voté pour qui à quel moment et à quel tour de vote, pour quel organisme et quel rôle


Enfin je ne sais pas si toute mon explication était vraiment claire mais j'espère que certains experts en base de données pourront m'aiguiller au niveau de ma conception ^^ Désolé d'avance si je pose des questions bêtes ou si je ne me suis pas bien exprimé. Merci et bonne journée

[fsmrel]

Bonsoir Jérémy,


Les fondations paraissent bonnes, mais il va falloir bétonner, sinon les anomalies vont pouvoir arriver en foule et miner l’édifice...


Je vais essayer de creuser ce sujet original. En attendant, si pour une élection il y a un 2e tour, une personne doit-elle nécessairement avoir voté au 1er tour pour voter au 2e ? Je suppose que non, mais allez savoir...


Les attributs quantite_personnes devraient disparaître au motif de la redondance, car on sait compter au niveau SQL (fonction COUNT appliquées aux tables APPARTENIR, VOTER qui seront dérivées à partir du MCD).

[fsmrel]

Bonsoir à nouveau Jérémy,


Vous avez écrit :

Une personne peut postuler pour plusieurs rôles au sein d'un organisme.

Question :

A l’occasion d’une élection donnée ? d'un ensemble d'élections ?

Autre question :

Un candidat peut-il changer de rôle entre le 1er et le 2e tour ?

J’ai repris votre MCD mais en passant au niveau MLD, car les possibilités offertes par Merise sont trop limitées. J’utilise ici MySQL Workbench (gratuit), outil pour lequel je fournis ça et là des explications quant à son emploi (voyez ici chez jogodepau, ou là ou là, ou là ou là, etc.

Par exemple, pour représenter l’appartenance d’une personne à un organisme :





Comme on est au niveau du MLD, on parle de clés tandis qu’au niveau MCD on parle d’identifiants. Les clés sont symbolisées par un mickey jaune. La table APPARTENANCE est la traduction de votre association APPARTENIR. Attention, les cardinalités dans le MLD (notation UML ici) sont inversées par rapport à celles du MCD, il n’en demeure pas moins qu’un organisme héberge de 1 à N personnes et qu’une personne appartient de 1 à N organismes.

Le singleton {OrganismeId} est clé primaire de la table ORGANISME, le singleton {PersonneId} est clé primaire de la table PERSONNE et la paire {OrganismeId, PersonneId} est clé primaire de la table APPARTENANCE. Notez l’emploi que je fais des accolades : en effet, une clé est un ensemble au sens de la théorie des ensembles, à savoir un ensemble de noms d’attributs.

Je rappelle par ailleurs qu’une table en relation avec une autre table peut être dotée de contraintes référentielles décrites par des clés étrangères :

L’attribut OrganismeId de la table APPARTENANCE fait l’objet d’une clé étrangère {OrganismeId} permettant d’établir la contrainte référentielle qui veut que chaque valeur prise par l’attribut OrganismeId de la table APPARTENANCE soit nécessairement une valeur prise par l’attribut OrganismeId de la table ORGANISME (application dans le cas général, surjection dans le cas particulier qui nous intéresse ici).

De la même façon, l’attribut PersonneId de la table APPARTENANCE fait l’objet d’une clé étrangère {PersonneId} permettant d’établir la contrainte référentielle qui veut que chaque valeur prise par l’attribut PersonneId de la table APPARTENANCE soit nécessairement une valeur prise par l’attribut PersonneId de la table PERSONNE (surjection à nouveau).


Cas des élections

Une élection est la chose d’un organisme, elle n’est pas transférable à un autre organisme, c’est « à la vie à mort ». Une élection est la propriété d’un organisme, au niveau conceptuel on dit qu’un organisme est une entité forte et une élection une entité faible, qui ne vit qu’au travers de l’entité forte à laquelle elle appartient. Pour exprimer cela, pour marquer le coup, techniquement on utilise l’identification relative, selon laquelle une élection est identifiée par l’organisme. Au niveau MLD : la clé de la table ELECTION a pour éléments l’attribut OrganismeId hérité de la table ORGANISME, plus un numéroteur pour dédoubler les différentes élections ressortissant à l’organisme (attribut ElectionId).





Je propose ensuite la mise en œuvre d’une table ROLE_A_POURVOIR, traduisant une association entre les tables ELECTION et ROLE. Une élection fait l’objet d’au moins un rôle à pourvoir et un rôle peut intervenir dans plusieurs élections :





Passons aux candidats aux élections :


Je fais ici l’hypothèse qu’une personne ne peut être candidate que pour un seul rôle dans le cadre d’une élection, mais si vous préférez que ce soit pour plusieurs rôles, il n’y aura pas de problème pour aménager le MLD qui en attendant peut être le suivant :



On observera qu’en vertu de l’hypothèse faite, l’attribut RoleId ne participe pas à la clé primaire de la table CANDIDAT, laquelle est dotée de deux clés étrangères :

{OrganismeId, CandidatId} faisant référence à la clé primaire {OrganismeId, PersonneId} de la table APPARTENANCE,

{OrganismeId, ElectionId, RoleId} faisant référence à la clé primaire {OrganismeId, ElectionId, RoleId} de la table ROLE_A_POURVOIR.

Il est important de noter que l’attribut OrganismeId participe aux deux clés étrangères : on a ainsi la garantie qu’une personne ne peut être candidate que pour une élection concernant l’organisme auquel elle appartient.

N.B. CandidatId est synonyme de PersonneId, simplement le mot est plus parlant.

Une ébauche pour conclure :





Sommes nous sur le bon chemin ?

[JimPear]

Bonjour ,

Tout d'abord merci beaucoup d'avoir pris le temps d'étudier mon sujet. C'est super gentil et cela va beaucoup m'aider par la suite (avoir l'avis d'un expert ne peut être que bénéfique ).

Le schéma que vous me proposez me semble beaucoup plus cohérent que le mien même s'il faut que j'étudie encore un peu plus votre schéma car il me faut un peu de temps pour assimiler et comprendre ce dernier. Dans tous les cas vos explications sont claires dans l'ensemble et je vous en remercie.

Pour répondre à votre premier message :
"une personne doit-elle nécessairement avoir voté au 1er tour pour voter au 2e ? Je suppose que non, mais allez savoir..." => en effet une personne n'est pas obligée d'avoir voter au premier tour pour pouvoir voter au deuxième tour. En revanche elle ne peut donc pas voter deux fois pour le même tour pour une élection.

Concernant votre second message :
=> Une personne peut exercer plusieurs rôles au sein d'un organisme, mais chaque rôle sera lié à une élection propre à ce rôle à chaque fois. Par exemple, si une personne veut être secrétaire et trésorier, il sera dans la liste des prétendants de l'élection de secrétaire, et dans celle de l'élection du trésorier. Il sera lié à une élection à chaque fois pour chaque rôle.
=> Non le candidat ne peut pas changer de rôle entre deux tours.

Concernant le site en général : il y aura un compte administrateur pour chaque organisme qui se chargera de créer les élections et de gérer toutes les personnes y participant (prétendants et membres de l'organisme en question qui voteront). Il enverra par mail aux votants leurs identifiants, ces derniers ne pourront que se connecter et voter pour les élections auxquelles l'admin les a liés. De ce fait, il n'y aura au final que le compte admin de chaque organisme qui gérera tout sur le site. Les votants ne feront "que" de se connecter et de voter.

D'autres contraintes essentielles : je dois pouvoir donc récupérer à chaque fois qui vote pour qui à quelle élection pour quel rôle. Je dois pouvoir récupérer le nombre de votes pour chaque prétendant et pour chaque tour.

Hier soir j'ai refait un MCD avec un ami, je vous le joins au message au cas où le temps que j'étudie votre schéma.

Encore merci et j'espère à tout à l'heure pour continuer à résoudre ce problème qui fait surchauffer mon cerveau depuis quelques jours

[fsmrel]

Bonsoir JimPear,

chaque rôle sera lié à une élection propre à ce rôle à chaque fois.
Par exemple, si une personne veut être secrétaire et trésorier, il sera dans la liste des prétendants de l'élection de secrétaire, et dans celle de l'élection du trésorier. Il sera lié à une élection à chaque fois pour chaque rôle.

D’accord, pour une élection donnée, une personne ne peut être candidate que pour un rôle. Si Raoul brigue le poste de trésorier et celui de secrétaire, ça ne pourra être qu’à l’occasion de deux élections distinctes. Ceci confirme donc l’hypothèse que j’avais formulée.

Mais comment interpréter « chaque rôle sera lié à une élection propre à ce rôle » ? On a l’impression qu’une élection ne concerne qu’un rôle. Qu’en est-il ? Une élection ne concerne vraiment qu’un seul rôle ? Pour le moment j'en reste à plus d'un rôle par élection.

je dois pouvoir donc récupérer à chaque fois qui vote pour qui à quelle élection pour quel rôle. Je dois pouvoir récupérer le nombre de votes pour chaque prétendant et pour chaque tour.

J’ai un peu simplifié mon MLD, en faisant l’économie de la table VOTE qui n’apporte pas de valeur ajoutée. Au passage, je renomme ELECTEUR en VOTANT :




Traduction en SQL (DB2 ou SQL Server) :

Table ORGANISME

Code SQL :

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

1
2
3
4
5
6
CREATE TABLE ORGANISME 
(
  OrganismeId           INT           NOT NULL,
  OrganismeNom          VARCHAR(64)   NOT NULL,
  CONSTRAINT ORGANISME_PK PRIMARY KEY (OrganismeId) 
) ;

Table PERSONNE

Code SQL :

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

1
2
3
4
5
6
CREATE TABLE PERSONNE 
( 
  PersonneId            INT           NOT NULL,
  PersonneNom           VARCHAR(64)   NOT NULL,
  CONSTRAINT PERSONNE_PK PRIMARY KEY (PersonneId) 
) ;

Table APPARTENANCE

Code SQL :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE APPARTENANCE 
(
  OrganismeId           INT           NOT NULL,
  PersonneId            INT           NOT NULL,
  DateAdhesion          DATE          NOT NULL,
  CONSTRAINT APPARTENANCE_PK PRIMARY KEY (OrganismeId, PersonneId),
  CONSTRAINT APPARTENANCE_ORGANISME_FK FOREIGN KEY (OrganismeId) REFERENCES ORGANISME,
  CONSTRAINT APPARTENANCE_PERSONNE_FK FOREIGN KEY (PersonneId) REFERENCES PERSONNE
) ;

Table ELECTION

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE ELECTION 
(
  OrganismeId           INT           NOT NULL,
  ElectionId            INT           NOT NULL,
  ElectionDate          DATE          NOT NULL,
  ElectionNom           VARCHAR(64)   NOT NULL,
  NbTours               INT           NOT NULL,
  CONSTRAINT ELECTION_PK PRIMARY KEY (OrganismeId, ElectionId),
  CONSTRAINT ELECTION_ORGANISME_NbTours CHECK (NbTours IN (1, 2)),
  CONSTRAINT ELECTION_ORGANISME_FK FOREIGN KEY (OrganismeId) REFERENCES ORGANISME
) ;

TABLE ROLE

Code SQL :

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

1
2
3
4
5
6
CREATE TABLE ROLE 
(
  RoleId                INT           NOT NULL,
  RoleNom               VARCHAR(64)   NOT NULL,
  CONSTRAINT ROLE_PK PRIMARY KEY (RoleId) 
) ;

TABLE ROLE_A_POURVOIR

Code SQL :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE ROLE_A_POURVOIR 
(
  OrganismeId           INT           NOT NULL,
  ElectionId            INT           NOT NULL,
  RoleId                INT           NOT NULL,
  CONSTRAINT ROLE_A_POURVOIR_PK PRIMARY KEY (OrganismeId, ElectionId, RoleId),
  CONSTRAINT ROLE_A_POURVOIR_ELECTION_FK FOREIGN KEY (OrganismeId, ElectionId) REFERENCES ELECTION,
  CONSTRAINT ROLE_A_POURVOIR_ROLE_FK FOREIGN KEY (RoleId) REFERENCES ROLE
) ;

TABLE CANDIDAT

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE CANDIDAT 
(
  OrganismeId           INT           NOT NULL,
  CandidatId            INT           NOT NULL,
  ElectionId            INT           NOT NULL,
  RoleId                INT           NOT NULL,
  CONSTRAINT CANDIDAT_PK PRIMARY KEY (OrganismeId, CandidatId, ElectionId),
  CONSTRAINT CANDIDAT_APPARTENANCE_FK FOREIGN KEY (OrganismeId, CandidatId) REFERENCES APPARTENANCE,
  CONSTRAINT CANDIDAT_ROLE_A_POURVOIR_FK FOREIGN KEY (OrganismeId, ElectionId, RoleId) REFERENCES ROLE_A_POURVOIR
) ;

TABLE VOTANT

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
 CREATE TABLE VOTANT 
(
  OrganismeId           INT           NOT NULL,
  VotantId              INT           NOT NULL,
  CandidatId            INT           NOT NULL,
  ElectionId            INT           NOT NULL,
  TourNo                INT           NOT NULL,
  CONSTRAINT VOTANT_PK PRIMARY KEY (OrganismeId, VotantId, ElectionId, TourNo),
  CONSTRAINT VOTANT_TourNo CHECK (TourNo IN (1, 2)),
  CONSTRAINT VOTANT_APPARTENANCE_FK FOREIGN KEY (OrganismeId, VotantId) REFERENCES APPARTENANCE,
  CONSTRAINT VOTANT_CANDIDAT_FK FOREIGN KEY (OrganismeId, CandidatId, ElectionId) REFERENCES CANDIDAT
) ;

Un début de soupçon de jeu d’essai :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
INSERT INTO ORGANISME (OrganismeId, OrganismeNom) VALUES (1, 'Organisme 1') ;
INSERT INTO ORGANISME (OrganismeId, OrganismeNom) VALUES (2, 'Organisme 2') ;
INSERT INTO ORGANISME (OrganismeId, OrganismeNom) VALUES (3, 'Organisme 3') ;
INSERT INTO ORGANISME (OrganismeId, OrganismeNom) VALUES (4, 'Organisme 4') ;
INSERT INTO ORGANISME (OrganismeId, OrganismeNom) VALUES (5, 'Organisme 5') ;
 
SELECT '' AS 'ORGANISME', * FROM ORGANISME ;
 
INSERT INTO PERSONNE (PersonneId, PersonneNom) VALUES (1, 'Albert') ;
INSERT INTO PERSONNE (PersonneId, PersonneNom) VALUES (2, 'Bernard') ;
INSERT INTO PERSONNE (PersonneId, PersonneNom) VALUES (3, 'Carole') ;
INSERT INTO PERSONNE (PersonneId, PersonneNom) VALUES (4, 'Denis') ;
INSERT INTO PERSONNE (PersonneId, PersonneNom) VALUES (5, 'Emile') ;
INSERT INTO PERSONNE (PersonneId, PersonneNom) VALUES (6, 'Fernand') ;
INSERT INTO PERSONNE (PersonneId, PersonneNom) VALUES (7, 'Paul') ;
INSERT INTO PERSONNE (PersonneId, PersonneNom) VALUES (8, 'Raoul') ;
INSERT INTO PERSONNE (PersonneId, PersonneNom) VALUES (9, 'Patricia') ;
INSERT INTO PERSONNE (PersonneId, PersonneNom) VALUES (10, 'Mado') ;
INSERT INTO PERSONNE (PersonneId, PersonneNom) VALUES (11, 'Jean') ;
 
SELECT '' AS 'PERSONNE', * FROM PERSONNE ;
 
INSERT INTO APPARTENANCE (OrganismeId, PersonneId, DateAdhesion) VALUES (1, 1, '1960-12-01') ;
INSERT INTO APPARTENANCE (OrganismeId, PersonneId, DateAdhesion) VALUES (1, 3, '1970-04-01') ;
INSERT INTO APPARTENANCE (OrganismeId, PersonneId, DateAdhesion) VALUES (1, 11, '1930-01-15') ;
 
INSERT INTO APPARTENANCE (OrganismeId, PersonneId, DateAdhesion) VALUES (1, 7, '1930-01-15') ;
INSERT INTO APPARTENANCE (OrganismeId, PersonneId, DateAdhesion) VALUES (1, 8, '1930-01-15') ;
INSERT INTO APPARTENANCE (OrganismeId, PersonneId, DateAdhesion) VALUES (1, 5, '1930-01-15') ;
 
 
SELECT '' AS 'APPARTENANCE', * FROM APPARTENANCE ;
 
INSERT INTO ROLE (RoleId, RoleNom) VALUES (1, 'Président') ;
INSERT INTO ROLE (RoleId, RoleNom) VALUES (2, 'Vice-président') ;
INSERT INTO ROLE (RoleId, RoleNom) VALUES (3, 'Secrétaire') ;
INSERT INTO ROLE (RoleId, RoleNom) VALUES (4, 'Trésorier') ;
 
SELECT '' AS 'ROLE', * FROM ROLE ;
 
INSERT INTO ELECTION (OrganismeId, ElectionId, ElectionDate, ElectionNom, NbTours) VALUES (1, 1, '2010-04-01', 'O1, E1', 2) ;
INSERT INTO ELECTION (OrganismeId, ElectionId, ElectionDate, ElectionNom, NbTours) VALUES (1, 2, '2012-04-01', 'O1, E2', 2) ;
INSERT INTO ELECTION (OrganismeId, ElectionId, ElectionDate, ElectionNom, NbTours) VALUES (2, 2, '2005-03-01', 'O2, E1', 1) ;
 
SELECT '' AS 'ELECTION', * FROM ELECTION ;
 
INSERT INTO ROLE_A_POURVOIR (OrganismeId, ElectionId, RoleId) VALUES (1, 1, 1) ;
INSERT INTO ROLE_A_POURVOIR (OrganismeId, ElectionId, RoleId) VALUES (1, 1, 3) ;
 
SELECT '' AS 'ROLE_A_POURVOIR', * FROM ROLE_A_POURVOIR ;
 
INSERT INTO CANDIDAT (CandidatId, OrganismeId, ElectionId, RoleId) VALUES (7, 1, 1, 3) ;
INSERT INTO CANDIDAT (CandidatId, OrganismeId, ElectionId, RoleId) VALUES (8, 1, 1, 3) ;
INSERT INTO CANDIDAT (CandidatId, OrganismeId, ElectionId, RoleId) VALUES (5, 1, 1, 1) ;
 
SELECT '' AS 'CANDIDAT', * FROM CANDIDAT ;
 
INSERT INTO VOTANT (OrganismeId, VotantId, CandidatId, ElectionId, TourNo) VALUES (1, 1, 7, 1, 1) ;
INSERT INTO VOTANT (OrganismeId, VotantId, CandidatId, ElectionId, TourNo) VALUES (1, 3, 7, 1, 1) ;
INSERT INTO VOTANT (OrganismeId, VotantId, CandidatId, ElectionId, TourNo) VALUES (1, 11, 8, 1, 1) ;
INSERT INTO VOTANT (OrganismeId, VotantId, CandidatId, ElectionId, TourNo) VALUES (1, 11, 8, 1, 2) ;
INSERT INTO VOTANT (OrganismeId, VotantId, CandidatId, ElectionId, TourNo) VALUES (1, 3, 8, 1, 2) ;
INSERT INTO VOTANT (OrganismeId, VotantId, CandidatId, ElectionId, TourNo) VALUES (1, 1, 7, 1, 2) ;
 
SELECT '' AS 'VOTANT', * FROM VOTANT ;

Pour savoir combien de voix ont obtenus les candidats de tel organisme à telle élection, pour quel rôle, à quel tour :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT OrganismeNom AS Organisme, ElectionNom AS Election, RoleNom AS Rôle, PersonneNom AS Candidat, TourNo AS Tour, NbVotes AS Voix
FROM   (SELECT x.OrganismeId, x.ElectionId, x.CandidatId, x.RoleId, y.TourNo, COUNT(*) AS NbVotes
        FROM   CANDIDAT AS x JOIN VOTANT AS y ON x.OrganismeId = y.OrganismeId
                                              AND x.ElectionId = y.ElectionId
                                              AND x.CandidatId = y.CandidatId
        GROUP BY x.OrganismeId, x.ElectionId, x.CandidatId, x.RoleId, y.TourNo) AS x
 
       JOIN ORGANISME AS y ON x.OrganismeId = y.OrganismeId
       JOIN ELECTION AS z ON x.OrganismeId = z.OrganismeId 
                          AND x.ElectionId = z.ElectionId
       JOIN ROLE AS t ON x.RoleId = t.RoleId
       JOIN PERSONNE AS u ON x.CandidatId = u.PersonneId         
;

Au résultat :

Organisme      Election    Rôle          Candidat    Tour    Voix
-----------------------------------------------------------------
Organisme 1    O1, E1      Secrétaire    Paul           1       2
Organisme 1    O1, E1      Secrétaire    Paul           2       1
Organisme 1    O1, E1      Secrétaire    Raoul          1       1
Organisme 1    O1, E1      Secrétaire    Raoul          2       2 

A propos de votre MCD :

Supposons que Raoul et Paul se présentent comme candidats à l’élection "O1, E1" organisée par l’organisme "Organisme 1", tandis qu’Albert vote à cette élection : selon votre MCD, Albert n’est pas à même de décider du candidat qui a sa préférence. Cela revient à dire qu’il faut établir une association entre les associations VOTER et SE_PRESENTE, or le dogme officiel merisien l’interdit. Il vous reste à transformer ces associations en entités-types puis associer celles-ci. Ceci fait, on peut savoir qui vote pour qui. Concernant le rôle : Raoul ne se présentant que pour le seul rôle "Secrétaire" à l’élection "O1, E1", il suffit de brancher l’entité-type ROLE sur l’association SE_PRESENTE et définir une contrainte d'intégrité fonctionnelle (CIF) pour respecter la contrainte d’un seul rôle par candidat et par élection :



La prise en compte effectif de la CIF au niveau MLD puis SQL dépend de l’AGL utilisé.

En ce qui concerne les tours : pour ma part je me suis contenté d’un attribut TourNo pour le numéro du tour, table VOTANT, sachant que cette table sera dotée d’une contrainte limitant les valeurs prises par TourNo à 1 er 2. Pour mieux mettre en évidence le concept de tour, conservez l’entité-type TOUR et son branchement sur l’association VOTER, mais au niveau SQL vous pourrez envisager de mettre en œuvre la contrainte et ainsi supprimer la table TOUR.

Quant à la récupération du nombre de votes par candidat et par tour, c’est du traitement et, comme je l’ai montré ci-dessus, une requête SQL suffira (si bien entendu les votes ont tous été engrangés (table VOTANT)... )


Contrôles à prévoir :

Des contrôles supplémentaires sont à prévoir en ce qui concerne ce que j’ai proposé :

2e tour bien précédé d’un 1er tour.

Compatibilité de l’attribut NbTours (table ELECTION) et de l’attribut TourNo (table VOTANT) : rien n’empêche que pour une élection, TourNo vaille deux tandis que NbTours vaut 1. La compatibilité peut être garantie à l’aide d’un trigger.

Interdire que quelqu’un vote pour lui-même ?

AU fait, quel est votre SGBD ?

N’hésitez pas à voter quand j’ai pu vous aider.

Bon courage ! et bonne fête de Pentecôte.

[JimPear]

Bonjour,

Encore merci pour le temps que vous avez pris pour étudier mon sujet. Je vais essayer de continuer mon projet en tenant compte de tout ce que vous m'avez dit. Je vous tiens au courant au cas où.

Pour répondre à vos questions,
- Oui, il y a bien un seul rôle par élection.
- Il ne faut pas interdire quelqu'un de voter pour lui-même
- Mon SGBD est Mysql.

Encore merci et bonne journée !

[fsmrel]

Bonsoir JimPear,

Encore merci pour le temps que vous avez pris pour étudier mon sujet. Je vais essayer de continuer mon projet en tenant compte de tout ce que vous m'avez dit. Je vous tiens au courant au cas où.

Ce fut un plaisir, n’hésitez à revenir en cas de problème.

Oui, il y a bien un seul rôle par élection.

Ce qui conduit à simplifier le modèle, la table ROLE_A_POURVOIR n’ayant plus de raison d’être :



Bon vent !

[JimPear]

Bonjour,

J'ai essayé d'intégrer votre script de base de données avec phpmyadmin et j'obtiens toujours la même erreur (#1005 - Can't create table 'fsmrel_votes.appartenance' (errno: 150)).
J'ai consulté plusieurs forums pour résoudre ce problème lié aux clés étrangères : vérifier si les clé primaires étaient bien toutes du même type (c'était le cas), vérifier que le moteur de la base était le même (InnoDB), ajouter la ligne "SET FOREIGN_KEY_CHECKS=0;", changer le charset en utf8, ... sans succès.

J'ai également essayé d'ajouter toutes les tables sans clés étrangères, puis d'ajouter ensuite les contraintes une à une mais je n'obtiens pas le même résultat que vous et/ou je suis à nouveau confronté à des erreurs. Je suis désolé si ces problèmes sont courants ou pas très compliqués à résoudre mais je n'ai pas réussi à les corriger

En fait, quelques points m'échappent : par exemple, concernant les lignes du type : "CONSTRAINT ROLE_A_POURVOIR_ELECTION_FK FOREIGN KEY (OrganismeId, ElectionId) REFERENCES ELECTION" ; ne faut-il pas normalement ajouter les id de la table election vers lesquels pointent la clé étrangère de Role_a_pourvoir ? Comme pour une requête du type :

"ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)"


Par ailleurs, en parallèle de ces problèmes, je me suis intéressé à la conception des différentes requêtes Sql qui vont m'être utiles par la suite pour les fonctionnalités de mon site et j'avoue être un peu perdu.

En effet des requêtes comme celle que vous m'aviez montré en exemple "pour savoir combien de voix ont obtenus les candidats de tel organisme à telle élection, pour quel rôle, à quel tour" ne me semblent pas du tout à ma portée pour mon niveau en base de données Pourriez-vous détailler un peu plus le fonctionnement de cette requête ?

J'essaye donc actuellement d'écrire les requêtes qui me serviront plus tard, à savoir :

• Ajouter une personne à une élection d'un organisme et pour un rôle
• Ajouter un rôle et une élection pour un organisme
• Ajouter un rôle à un candidat
• Récupérer qui a voté pour le 1er, le 2eme tour d'une election d'un organisme pour un role (j'essaierai de m'inspirer de votre requête)

Voilà, j'espère ne pas trop abuser de votre temps et que vous pourrez (encore une fois ) m'aider dans ma tâche ^^. Encore merci pour tout.

Bonne journée !

[fsmrel]

Bonjour JimPear,

J'ai essayé d'intégrer votre script de base de données avec phpmyadmin.

Le script que j'ai produit a été aménagé et fonctionne avec DB2 ou SQL Server. Je n’utilise pas MySQL qui est incomplet et dont le métamodèle et l’architecture laissent à désirer (mélange des concepts logiques et physiques), aussi je vous conseille de recommencer avec un script généré par Workbench et adapté à MySQL. Pour cela, utilisez la fonction Export de Workbench :



Je regarde le reste.

[fsmrel]

Bonsoir JimPear,

En parallèle de ces problèmes, je me suis intéressé à la conception des différentes requêtes Sql qui vont m'être utiles par la suite pour les fonctionnalités de mon site et j'avoue être un peu perdu.

En effet des requêtes comme celle que vous m'aviez montré en exemple "pour savoir combien de voix ont obtenus les candidats de tel organisme à telle élection, pour quel rôle, à quel tour" ne me semblent pas du tout à ma portée pour mon niveau en base de données Pourriez-vous détailler un peu plus le fonctionnement de cette requête ?

Commençons par parler de l’opération relationnelle Jointure naturelle traditionnellement abrégée par flemme en Jointure. Je vous conseille de commencer par lire la discussion ouverte par Age_of_Aquarius, notamment ce post dans lequel je commente la réflexion de Hugh Darwen (aka Andrew Warden) qui insiste sur l’importance de la jointure naturelle, par contraste avec ce qu’on appelle la thêta-jointure dans les cercles d’esthètes.


Pour mieux comprendre l’insistance de Darwen sur le rôle fondamental de la jointure naturelle, intéressons-nous à ce qu’il a écrit en 1987 et repris en tant que contribution darwenienne (signée du pseudonyme anagrammique Andrew Warden) dans l’ouvrage de Chris Date Relational Database, Writings 1985-1989, paru en 1990, je traduis :

« Étudions maintenant l’opérateur Jointure naturelle et voyons comment il nous change la vie. Je le représente selon la syntaxe suivante :

MARRIAGE (table-expression-1, table-expression-2)

On peut voir ainsi la merveilleuse union accomplie par cet opérateur :

Dans un premier temps, il y a les tendres préliminaires à l’occasion desquels les colonnes des deux opérandes sont examinées, afin de voir lesquelles portent le même nom. Celles-ci sont appelées « colonnes communes » quand, outre le même nom, elles sont du même domaine.

Dans un deuxième temps a lieu l’accouplement : à cette occasion les lignes compatibles des deux opérandes sont jointes (à savoir les lignes qui ont la même valeur pour les colonnes communes).

Il y a enfin, l’orgasme grandiose quand un ensemble de colonnes communes est éjaculé.

Le fruit de cette union est un individu aussi parfait qu’on peut le souhaiter, ayant hérité des caractéristiques de ses parents. »

En fait, JOIN (jointure naturelle) est l’opérateur par excellence en relationnel, c’est lui vous permet de marier par exemple les tables CANDIDAT et VOTANT, permettant ainsi de savoir qui a voté pour qui.

Dans le style de Darwen :

Code D :

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

 MARRIAGE (CANDIDAT, VOTANT)

En relationnel pur :

Code D :

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

 CANDIDAT JOIN VOTANT

En SQL :

Code SQL :

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

 CANDIDAT NATURAL JOIN VOTANT

La jointure se fait à partir des colonnes des deux tables qui ont même nom, qui sont du même type et prennent la même valeur. Le problème est qu’avec nos SGBD SQL, les types « personnalisés » dont on a besoin sont bien difficiles, sinon impossibles à construire, et le type des colonnes communes de nos deux tables se réduisent à ce brave INT (entier), alors qu’il faudrait ne pouvoir comparer des organismes qu’avec des organismes, etc. Aussi est-il prudent d’utiliser un système lourd, mais moins risqué, en utilisant une variante de la jointure SQL :

Code SQL :

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

1
2
3
4
 
CANDIDAT AS x JOIN VOTANT AS y ON x.OrganismeId = y.OrganismeId  
                               AND x.ElectionId = y.ElectionId
                               AND x.CandidatId = y.CandidatId

Où OrganismeId, ElectionId et CandidatId sont donc les colonnes communes, bases du mariage des deux tables. Le résultat du mariage est un bébé, une table héritant des attributs des ses parents, sa structure est donc :

{OrganismeId, ElectionId, CandidatId, VotantId, TourNo}

Et comme c’est une table, en vertu du principe fondamental de fermeture, elle est mariable à son tour. Au passage, on lui fait une beauté en comptant le nombre de votes obtenus par chaque candidat, grâce à la fonction COUNT et au regroupement par candidat (GROUP BY).

On complète par un SELECT qui permet d’effectuer une opération relationnelle de projection, laquelle permet de ne retenir que les colonnes dont on a besoin pour la suite : par exemple, la colonne VotantId ne nous servira plus, on ne la retient donc pas :

Code SQL :

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

1
2
3
4
5
6
 
SELECT x.OrganismeId, x.ElectionId, x.CandidatId, x.RoleId, y.TourNo, COUNT(*) AS NbVotes
FROM   CANDIDAT AS x JOIN VOTANT AS y ON x.OrganismeId = y.OrganismeId
                                      AND x.ElectionId = y.ElectionId
                                      AND x.CandidatId = y.CandidatId
GROUP BY x.OrganismeId, x.ElectionId, x.CandidatId, x.RoleId, y.TourNo

Vous noterez que l’on a donné un nom au résultat du comptage : COUNT(*) AS NbVotes sinon le bébé serait handicapé.

En passant, notez aussi que SELECT précède FROM dans l'instruction, mais il est plus naturel de lire : FROM ... SELECT.

Comme prévu par Darwen, le résultat du mariage est bien une table à laquelle on peut donner un nom, par exemple « Baby » :

Code SQL :

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

1
2
3
4
5
(SELECT x.OrganismeId, x.ElectionId, x.CandidatId, x.RoleId, y.TourNo, COUNT(*) AS NbVotes
 FROM   CANDIDAT AS x JOIN VOTANT AS y ON x.OrganismeId = y.OrganismeId
                                       AND x.ElectionId = y.ElectionId
                                       AND x.CandidatId = y.CandidatId
 GROUP BY x.OrganismeId, x.ElectionId, x.CandidatId, x.RoleId, y.TourNo) AS Baby

Ce nom donné à ce bébé, à cette expression de table au sens SQL, n’est en réalité pas nécessaire mais SQL l’exige : dont acte, on ne va pas en débattre ici.

Quoi qu’il en soit, puisque Baby est une [expression de] table, à son tour elle peut être mariée (fermeture oblige) pour donner naissance à un nouveau bébé :

Code SQL :

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

1
2
3
4
5
6
7
(SELECT x.OrganismeId, x.ElectionId, x.CandidatId, x.RoleId, y.TourNo, COUNT(*) AS NbVotes
        FROM   CANDIDAT AS x JOIN VOTANT AS y ON x.OrganismeId = y.OrganismeId
                                              AND x.ElectionId = y.ElectionId
                                              AND x.CandidatId = y.CandidatId
        GROUP BY x.OrganismeId, x.ElectionId, x.CandidatId, x.RoleId, y.TourNo) AS Baby
 
JOIN ORGANISME AS y ON Baby.OrganismeId = y.OrganismeId

Et ainsi de suite :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
(SELECT x.OrganismeId, x.ElectionId, x.CandidatId, x.RoleId, y.TourNo, COUNT(*) AS NbVotes
        FROM   CANDIDAT AS x JOIN VOTANT AS y ON x.OrganismeId = y.OrganismeId
                                              AND x.ElectionId = y.ElectionId
                                              AND x.CandidatId = y.CandidatId
        GROUP BY x.OrganismeId, x.ElectionId, x.CandidatId, x.RoleId, y.TourNo) AS Baby
 
JOIN ORGANISME AS y ON Baby.OrganismeId = y.OrganismeId
JOIN ELECTION AS z ON Baby.OrganismeId = z.OrganismeId 
                   AND Baby.ElectionId = z.ElectionId
JOIN ROLE AS t ON Baby.RoleId = t.RoleId
JOIN PERSONNE AS u ON Baby.CandidatId = u.PersonneId

A la fin de cette succession de mariages, on effectue une ultime projection :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT OrganismeNom AS Organisme, ElectionNom AS Election, RoleNom AS Rôle, PersonneNom AS Candidat, TourNo AS Tour, NbVotes AS Voix
 
FROM   (SELECT x.OrganismeId, x.ElectionId, x.CandidatId, x.RoleId, y.TourNo, COUNT(*) AS NbVotes
        FROM   CANDIDAT AS x JOIN VOTANT AS y ON x.OrganismeId = y.OrganismeId
                                              AND x.ElectionId = y.ElectionId
                                              AND x.CandidatId = y.CandidatId
        GROUP BY x.OrganismeId, x.ElectionId, x.CandidatId, x.RoleId, y.TourNo) AS Baby
 
       JOIN ORGANISME AS y ON Baby.OrganismeId = y.OrganismeId
       JOIN ELECTION AS z ON Baby.OrganismeId = z.OrganismeId 
                          AND Baby.ElectionId = z.ElectionId
       JOIN ROLE AS t ON Baby.RoleId = t.RoleId
       JOIN PERSONNE AS u ON Baby.CandidatId = u.PersonneId         
;

Incidemment, c’est grâce à Hugh Darwen qu’en SQL on peut marier des expressions de tables, avant on ne pouvait le faire que pour des tables (et bien sûr des vues). Hugh a représenté IBM (de 1988 à 2004) au comité ISO SQL qui a agréé sa demande d’utilisation des expressions de tables. Merci Hugh !

Est-ce que ça va mieux comme cela ?