1 pièce(s) jointe(s)
Avis pour améliorer un diagramme de classe
Bonjour à tous !
J’essaie de modéliser une base de données pour la gestion des carrières d’une administration. Le modèle répond aux spécifications suivantes :
• Les agents de l’administration sont constitués en corps de métier. Chaque corps est divisé en classes qui comprennent elles-mêmes un certain nombre d’échelons. Un échelon peut se retrouver dans plusieurs classes, tout comme une classe peut se retrouver dans plusieurs corps.
• A un instant donné, un agent appartient à un corps, est titulaire d’une classe de ce corps et relève d’un échelon au sein de cette classe.
• L’accès à un corps et l’avancement d’échelon à échelon ou de classe à classe sont effectifs à la suite d’un acte émanant de l’autorité. Un acte peut concerner plusieurs agents. Au cours de sa carrière, un agent peut aussi bénéficier de plusieurs actes. L’acte d’engagement permet à un nouvel agent d’intégrer l’administration. L’acte de reclassement permet à un agent déjà en activité de changer de corps. L’acte d’avancement permet comme son nom l’indique à un agent d’avancer d’échelon ou de classe.
• En plus d’être répartis en hiérarchies, les corps sont également classés dans des cadres lorsqu’ils participent d’un même service administratif. Ainsi, chaque corps se trouve dans un cadre et un cadre peut regrouper plusieurs corps. Le décret qui porte organisation d’un cadre constitue le statut particulier de ce cadre. Les statuts particuliers des cadres définissent la hiérarchie des classes dans chaque corps, le nombre d’échelons de chaque classe, le minimum d’ancienneté de services effectifs exigible dans chaque corps pour être promu à la classe supérieure, la durée du temps à passer dans chaque échelon.
J’ai réalisé une première esquisse du modèle et compte sur la communauté pour le corriger et l’améliorer. Mes préoccupations concernant ce modèle sont les suivantes :
• Les boucles ainsi constituées sont-elles normales ?
• La classe Acte ne doit-elle pas être en relation avec les classes Agent, Corps, Classe et Echelon ?
• La classe Decret ne doit-elle pas être en relation avec les Hiérarchie, Corps, Classe, Echelon ?
Contraintes de chemin et compagnie
Bonsoir mossane,
Citation:
Envoyé par
mossane
Il y a une petite rectification à faire concernant le précédent diagramme. Un corps peut déterminer plusieurs cadres.
Reprenons donc votre diagramme :
http://www.developpez.net/forums/att...istration7.jpg
Pour voir quelles anomalies pourraient se glisser, il est nécessaire de prendre des exemples concrets comme on l’a déjà fait, il n’y a rien de tel...
Allons-y, avec un exemple conforme au diagramme, minimal mais suffisant pour secouer celui-ci :
Code:
1 2 3 4 5 6
| CADRE
CadreId
-------
ca1
ca2 |
Code:
1 2 3 4 5
| TEXTE_CADRE
TexteId CadreId
-------
t1 ca2 |
Code:
1 2 3 4 5 6
| CORPS
CorpsId CorpsTypeId
-------
co1 coty1
co2 coty2 |
:!: Cette fois-ci, l’association entre CADRE et CORPS est du type plusieurs à plusieurs, elle fera donc l’objet d’une table au niveau SQL :
Code:
1 2 3 4 5
| CADRE_CORPS
CadreId CorpsId
------- -------
ca1 co1 |
Même principe pour l’association entre TEXTE_CADRE et CORPS :
Code:
1 2 3 4 5
| TEXTE_CADRE_CORPS
TexteId CadreId CorpsId
------- ------- -------
t1 ca1 co2 |
C’est la table CADRE_CORPS qui fait foi en matière d’association de cadres et de corps, et en l’occurrence le corps <co1> est rattaché au cadre <ca1> et à lui seul. Mais patatras ! La table TEXTE_CADRE_CORPS contient la paire <ca1, co2>, valide du point de vue du modèle, mais illégale parce qu’elle n’est n’apparaît pas comme élément de la table CADRE_CORPS. It's a nasty situation, isn’t it?
Puisque la table CADRE_CORPS est en fait une référence pour la table TEXTE_CADRE_CORPS, appelons un chat un chat (et Rollet un fripon), modélisons ainsi :
http://www.fsmwarden.com/developpez_...e_corps_v2.png
Cette fois-ci, la paire <ca1, co2> n’est plus valide pour la table TEXTE_CADRE_CORPS (viol d’intégrité référentielle). Pour être conforme, le système devient par exemple le suivant :
Code:
1 2 3 4 5 6
| CADRE
CadreId
-------
ca1
ca2 |
Code:
1 2 3 4 5
| TEXTE_CADRE
TexteId CadreId
-------
t1 ca2 |
Code:
1 2 3 4 5 6
| CORPS
CorpsId CorpsTypeId
-------
co1 coty1
co2 coty2 |
Code:
1 2 3 4 5
| CADRE_CORPS
CadreId CorpsId
------- -------
ca1 co1 |
Code:
1 2 3 4 5
| TEXTE_CADRE_CORPS
TexteId CadreId CorpsId
------- ------- -------
t1 ca1 co1 |
Pour autant, a-t-on suffisamment bétonné ?
J’avais écrit :
Citation:
Envoyé par
fsmrel
On peut naviguer de deux façons en partant de TEXTE_CADRE pour atteindre CADRE : soit en empruntant le chemin allant directement de TEXTE_CADRE à CADRE, soit en empruntant le chemin passant par CORPS, mais alors rien ne garantit que le cadre qu’on atteint est celui qui est défini par TEXTE_CADRE...
On retombe sur un problème du cheminement : si la table TEXTE_CADRE_CORPS est nécessairement conforme, en relation ave la table CADRE_CORPS, cette fois-ci, en « remontant » vers TEXTE_CADRE (association par la colonne TexteId), on a une contradiction en ce qui concerne le cadre : selon la table TEXTE_CADRE, le texte <t1> est associé au seul cadre <ca2>, alors que selon la table TEXTE_CADRE_CORPS, le texte <t1> est associé au cadre <ca1> (il pourrait être associé à <ca2>, mais ici il ne l’est pas).
Ainsi, on n’a pas suffisamment bétonné, mais conceptuellement, on ne peut pas faire plus. En conséquence, il faudra poursuivre le bétonnage au niveau SQL.
Logiquement, ça sera au moyen de triggers (tant que les SGBD SQL ne proposeront pas l’instruction CREATE ASSERTION), mais je vous propose une alternative, car les triggers sont toujours délicats à manier et parfois pris en défaut du fait de règles de gestion particulièrement subtiles (en 20 ans de triggers, j’ai eu l’occasion de le constater...)
Examinons le MLD dérivé du diagramme pour la partie qui nous intéresse :
http://www.fsmwarden.com/developpez_...rps_v2_mld.png
Et le code SQL généré avec les inserts qui vont bien :
TABLE CORPS_TYPE
Code:
1 2 3 4 5 6 7 8 9 10 11
| CREATE TABLE CORPS_TYPE
(
CorpsTypeId INT NOT NULL,
CorpsTypeLibelle VARCHAR(64) NOT NULL,
CONSTRAINT CORPS_TYPE_PK PRIMARY KEY (CorpsTypeId)
) ;
INSERT INTO CORPS_TYPE (CorpsTypeId, CorpsTypeLibelle) VALUES (1, 'corps type 1') ;
INSERT INTO CORPS_TYPE (CorpsTypeId, CorpsTypeLibelle) VALUES (2, 'corps type 2') ;
SELECT *, '' AS '<= CORPS_TYPE' FROM CORPS_TYPE ; |
TABLE HIERARCHIE
Code:
1 2 3 4 5 6 7 8 9 10 11
| CREATE TABLE HIERARCHIE
(
HierarchieId INT NOT NULL,
HierarchieLibelle VARCHAR(64) NOT NULL,
HierarchieOrdre VARCHAR(64) NOT NULL,
CONSTRAINT HIERARCHIE_PK PRIMARY KEY (HierarchieId)
) ;
INSERT INTO HIERARCHIE (HierarchieId, HierarchieLibelle, HierarchieOrdre) VALUES (1, 'hiérarchie 1', 'ordre 1') ;
SELECT *, '' AS '<= HIERARCHIE' FROM HIERARCHIE ; |
TABLE CORPS
Code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
| CREATE TABLE CORPS
(
CorpsId INT NOT NULL,
HierarchieId INT NOT NULL,
CorpsTypeId INT NOT NULL,
CorpsCode CHAR(8) NOT NULL,
CorpsLibelle VARCHAR(64) NOT NULL,
CONSTRAINT CORPS_PK PRIMARY KEY (CorpsId),
CONSTRAINT CORPS_AK UNIQUE (CorpsCode),
CONSTRAINT CORPS_CORPS_TYPE_FK FOREIGN KEY (CorpsTypeId)
REFERENCES CORPS_TYPE (CorpsTypeId),
CONSTRAINT CORPS_HIERARCHIE_FK FOREIGN KEY (HierarchieId)
REFERENCES HIERARCHIE (HierarchieId)
) ;
INSERT INTO CORPS (CorpsId, HierarchieId, CorpsTypeId, CorpsCode, CorpsLibelle) VALUES (1, 1, 1, 'co1', 'corps 1') ;
SELECT *, '' AS '<= CORPS' FROM CORPS ; |
TABLE CADRE
Code:
1 2 3 4 5 6 7 8 9 10 11 12
| CREATE TABLE CADRE
(
CadreId INT NOT NULL,
CadreCode VARCHAR(8) NOT NULL,
CadreLibelle VARCHAR(64) NOT NULL,
CONSTRAINT CADRE_PK PRIMARY KEY (CadreId)
) ;
INSERT INTO CADRE (CadreId, CadreCode, CadreLibelle) VALUES (1, 'ca1', 'cadre 1') ;
INSERT INTO CADRE (CadreId, CadreCode, CadreLibelle) VALUES (2, 'ca2', 'cadre 2') ;
SELECT *, '' AS '<= CADRE' FROM CADRE ; |
TABLE CADRE_CORPS
Code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14
| CREATE TABLE CADRE_CORPS
(
CadreId INT NOT NULL,
CorpsId INT NOT NULL,
CONSTRAINT CADRE_CORPS_PK PRIMARY KEY (CadreId, CorpsId),
CONSTRAINT CADRE_CORPS_CORPS_FK FOREIGN KEY (CorpsId)
REFERENCES CORPS (CorpsId),
CONSTRAINT CADRE_CORPS_CADRE_FK FOREIGN KEY (CadreId)
REFERENCES CADRE (CadreId)
) ;
INSERT INTO CADRE_CORPS (CadreId, CorpsId) VALUES (1, 1) ;
SELECT *, '' AS '<= CADRE_CORPS' FROM CADRE_CORPS ; |
TABLE TEXTE
Code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
| CREATE TABLE TEXTE
(
TexteId INT NOT NULL,
TexteNumero VARCHAR(4) NOT NULL,
TexteDate DATE NOT NULL,
TexteType VARCHAR(8) NOT NULL,
Observation VARCHAR(64) NOT NULL,
CONSTRAINT TEXTE_PK PRIMARY KEY (TexteId),
CONSTRAINT TEXTE_AK UNIQUE (TexteNumero)
) ;
INSERT INTO TEXTE (TexteId, TexteNumero, TexteDate, TexteType, Observation) VALUES (1, 'A001', '2014-08-03', 'cadre', '') ;
INSERT INTO TEXTE (TexteId, TexteNumero, TexteDate, TexteType, Observation) VALUES (2, 'B005', '2014-10-13', 'cadre', '') ;
SELECT *, '' AS '<= TEXTE' FROM TEXTE ; |
TEXTE_CADRE
Code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14
| CREATE TABLE TEXTE_CADRE
(
TexteId INT NOT NULL,
CadreId INT NOT NULL,
CONSTRAINT TEXTE_CADRE_PK PRIMARY KEY (TexteId),
CONSTRAINT TEXTE_CADRE_CADRE_FK FOREIGN KEY (CadreId)
REFERENCES CADRE (CadreId),
CONSTRAINT TEXTE_CADRE_TEXTE_FK FOREIGN KEY (TexteId)
REFERENCES TEXTE (TexteId)
) ;
INSERT INTO TEXTE_CADRE (TexteId, CadreId) VALUES (1, 2) ;
SELECT *, '' AS '<= TEXTE_CADRE' FROM TEXTE_CADRE ; |
TABLE TEXTE_CADRE_CORPS
Code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
| CREATE TABLE TEXTE_CADRE_CORPS
(
TexteId INT NOT NULL,
CadreId INT NOT NULL,
CorpsId INT NOT NULL,
CONSTRAINT TEXTE_CADRE_CORPS_PK PRIMARY KEY (TexteId, CadreId, CorpsId),
CONSTRAINT TEXTE_CADRE_CORPS_TEXTE_CADRE_FK FOREIGN KEY (TexteId)
REFERENCES TEXTE_CADRE (TexteId),
CONSTRAINT TEXTE_CADRE_CORPS_CADRE_CORPS_FK FOREIGN KEY (CadreId, CorpsId)
REFERENCES CADRE_CORPS (CadreId, CorpsId)
) ;
INSERT INTO TEXTE_CADRE_CORPS (TexteId, CadreId, CorpsId) VALUES (1, 1, 1) ;
SELECT *, '' AS '<= TEXTE_CADRE_CORPS' FROM TEXTE_CADRE_CORPS ; |
Pour bétonner, il faudrait que la paire {TexteId, CadreId} de la table TEXTE_CADRE_CORPS fasse référence à la paire {TexteId, CadreId} de la table TEXTE_CADRE c'est-à-dire qu’on établisse une contrainte d’inclusion : y a du trigger en vue, mais pour éviter ça, je suggère une vieille ruse, consistant à déclarer une surclé {TexteId, CadreId} pour la table TEXTE_CADRE (contrainte TEXTE_CADRE_SK), à la quelle fera référence (au motif d’intégrité référentielle) la paire {TexteId, CadreId} de la table TEXTE_CADRE_CORPS :
TABLE TEXTE_CADRE
Code:
1 2 3 4 5 6 7 8 9 10 11
| CREATE TABLE TEXTE_CADRE
(
TexteId INT NOT NULL,
CadreId INT NOT NULL,
CONSTRAINT TEXTE_CADRE_PK PRIMARY KEY (TexteId),
CONSTRAINT TEXTE_CADRE_SK UNIQUE (TexteId, CadreId),
CONSTRAINT TEXTE_CADRE_CADRE_FK FOREIGN KEY (CadreId)
REFERENCES CADRE (CadreId),
CONSTRAINT TEXTE_CADRE_TEXTE_FK FOREIGN KEY (TexteId)
REFERENCES TEXTE (TexteId)
) ; |
TABLE TEXTE_CADRE_CORPS
Code:
1 2 3 4 5 6 7 8 9 10 11
| CREATE TABLE TEXTE_CADRE_CORPS
(
TexteId INT NOT NULL,
CadreId INT NOT NULL,
CorpsId INT NOT NULL,
CONSTRAINT TEXTE_CADRE_CORPS_PK PRIMARY KEY (TexteId, CadreId, CorpsId),
CONSTRAINT TEXTE_CADRE_CORPS_TEXTE_CADRE_FK FOREIGN KEY (TexteId, CadreId)
REFERENCES TEXTE_CADRE (TexteId, CadreId),
CONSTRAINT TEXTE_CADRE_CORPS_CADRE_CORPS_FK FOREIGN KEY (CadreId, CorpsId)
REFERENCES CADRE_CORPS (CadreId, CorpsId)
) ; |
Maintenant, il faut que je souffle un peu, je traiterai de la 2e partie demain (collèges, groupes, fonctions, etc.) Si vous avez des remarques et des questions...