Discussion :

[nanneb]

Bonjour,

Etant nouvelle sur ce forum, j'espère ouvrir correctement ma première discussion...
Je démarre dans la conception de BD en m'appuyant sur l'ouvrage "Brouard, Frédéric. Modélisation de bases de données: UML et les modèles entité-association - Avec 30 exercices corrigés inspirés de cas réels (Noire) (French Edition) . Eyrolles. "
Pour commencer à mettre en pratique les notions relatives au MCD, je me suis penchée sur ce cas dont voici le cahier des charges :

Un historien souhaite établir des statistiques sur des soldats de la Première Guerre mondiale. Pour chaque soldat, outre l'état-civil, il souhaite avoir la trace :
•de la date de son décès si celui-ci est survenu suite aux combats
•des blessures reçues (type et date de la blessure, en plus de la bataille où elle a été infligée. Les batailles seront référencées dans une liste comportant le lieu, les dates de début et de fin)
•des grades obtenus (avec les dates)
•de l'unité de rattachement (avec les dates)

Le MCD proposé en solution et celui que j'imagine diffèrent, en particulier parce que j'ai préféré éviter les relations n-aires.
Je vous soumets donc les deux schémas en vous demandant votre avis sur chacun d'eux.

Merci d'avance pour votre aide !

[escartefigue]

Bonsoir nanneb,

Dans le 1er schéma
décès au combat
Cette entité-type n'a pas d'existence, il s'agit d'un simple attribut facultatif ("nullable" donc) du soldat
D'ailleurs, si vous suiviez cette logique jusqu'au bout, vous auriez pu également faire une entité-type "date de naissance"


soldat dans une bataille
Evitez les noms à rallonge pour les entités-type comme pour les associations : c'est comme les super tankers, difficile à manœuvrer !
Pour les associations, le mieux est un verbe à l'infinitif
Ici, il s'agit visiblement tout simplement des batailles.
Et il manque éventuellement la guerre dont la bataille dépend
[SOLDAT] 0,n --- (participer) ---1,n [BATAILLE] <1,1> ---(inclure) --- 1,n [GUERRE]

Il faut ensuite mettre en oeuvre une Contrainte d'Intégrité Fonctionnelle (CIF) qui stipule qu'un soldat ne peut être bléssé que dans une bataille dans laquelle il a participé, ce qui donne
[SOLDAT] 0,n --- (participer) ---1,n [BATAILLE] <1,1> ---(inclure) --- 1,n [GUERRE]
......│.......................^......................│
......│.......................│......................│
......└........ 0,n---(blesser) --- 0,n -----┘
La flèche de blesser vers participer matérialise la CIF
L'association blesser peut comporter un attribut booleen létal O/N ou un attribut date de décès qui n'est pas forcément incluse dans la période de la bataille, mais forcément supérieure ou égale à la date de début de la bataille


Soldat
La cardinalité mini de 1 vers "promu" implique que tout soldat a été promu au moins une fois...


Dans le deuxième schéma
Les noms des entité-type devraient être au singulier

Là aussi, il faut vérifier qu'un soldat blessé dans une bataille a bien participé à cette bataille, il faut mettre en oeuvre deux associations (participer et blesser) et une CIF comme expliqué ci-dessus.


Dans les deux schémas
Rien n'interdit qu'un soldat soit dans deux unités à la même date, ce qui n'est certainement pas conforme à la réalité

[nanneb]

Bonsoir Escartefigue,

Merci pour votre réponse, qui appelle les questions suivantes :

Dans le 1er schéma
décès au combat
Cette entité-type n'a pas d'existence, il s'agit d'un simple attribut facultatif ("nullable" donc) du soldat
D'ailleurs, si vous suiviez cette logique jusqu'au bout, vous auriez pu également faire une entité-type "date de naissance"
=> Je voulais éviter un attribut nullable et permettre la recherche de tous les soldats morts une année donnée. Comment faire autrement ?


soldat dans une bataille
Il faut ensuite mettre en oeuvre une Contrainte d'Intégrité Fonctionnelle (CIF) qui stipule qu'un soldat ne peut être bléssé que dans une bataille dans laquelle il a participé, ce qui donne
[SOLDAT] 0,n --- (participer) ---1,n [BATAILLE] <1,1> ---(inclure) --- 1,n [GUERRE]
......│.......................^......................│
......│.......................│......................│
......└........ 0,n---(blesser) --- 0,n -----┘
La flèche de blesser vers participer matérialise la CIF
L'association blesser peut comporter un attribut booleen létal O/N ou un attribut date de décès qui n'est pas forcément incluse dans la période de la bataille, mais forcément supérieure ou égale à la date de début de la bataille
==> Je suppose que "TYPE_BLESSURE" forme une association avec "Blesser" ?


Soldat
La cardinalité mini de 1 vers "promu" implique que tout soldat a été promu au moins une fois...
Un soldat a toujours un grade. Promu est donc un terme inapproprié, DETENIR sera plus juste.

Dans les deux schémas
Rien n'interdit qu'un soldat soit dans deux unités à la même date, ce qui n'est certainement pas conforme à la réalité
==> Comment feriez-vous ?

[escartefigue]

Bonjour,

Je voulais éviter un attribut nullable et permettre la recherche de tous les soldats morts une année donnée. Comment faire autrement ?

Il faut chasser les erreurs de modélisation qui conduisent à trouver pléthore d'attributs nullables à tort, mais ici ce n'est pas le cas, les dates de fin (de validité, de réservation, de période...) sont très souvent nullables.
Pour faciliter les opérations de recherche, on les type en général 'not null' avec une valeur '9999-12-31' par défaut. Ainsi, les soldats décédés sont ceux dont la dates de décès est différente de '9999-12-31'

Je suppose que "TYPE_BLESSURE" forme une association avec "Blesser" ?

Non : une association n'est pas en relation avec une autre association, s'il faut connaitre le type de blessure, il faut modéliser une entité-type blessure

Un soldat a toujours un grade. Promu est donc un terme inapproprié, DETENIR sera plus juste.

Quoique je ne suis pas certain qu'on "détienne" un grade, "obtenir" est peut être plus adapté (je n'y connais pas grand chose dans la chose militaire, désolé)

Comment feriez-vous ?

Voir ci-dessous

Pièce jointe 524418


Association PA_participer et table résultante :
J'ai créé une entité-type CA_calendrier, qui ne générera pas de table, et qui permet d'ajouter une date de début et de fin de participation dans la PK de la table issue de l'association "PA_participer"
Ce sont ces dates qui permettront de contrôler au moyen d'un trigger qu'un soldat ne peut pas participer à deux batailles dans une même période.
Il faudra également intervenir dans le script pour évacuer l'identifiant de la bataille de la PK de la table PA_participer (voir plus bas)
Un contrainte de type check est ajoutée pour vérifier que la date de début est inférieure ou égale à la date de fin de participation


entité-type BL_blessure :
Je l'avais omise dans mon schéma précédent, elle est identifiée relativement au soldat, est en lien avec un type de blessure et n'est pas obligatoirement infligée lors d'une bataille (le soldat a pu se blesser hors combat).


Script (DDL) :

Code :

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
CREATE TABLE SO_soldat(
   SO_ident INT,
   SO_nom VARCHAR(50) NOT NULL,
   PRIMARY KEY(SO_ident)
);

CREATE TABLE BA_bataille(
   BA_ident INT,
   BA_nom VARCHAR(50) NOT NULL,
   PRIMARY KEY(BA_ident)
);

CREATE TABLE YB_type_blessure(
   YB_ident INT,
   YB_description VARCHAR(50) NOT NULL,
   PRIMARY KEY(YB_ident)
);

CREATE TABLE BL_blessure(
   SO_ident INT,
   BL_ident INT,
   BL_severite SMALLINT,
   BL_date DATE NOT NULL,
   YB_ident INT NOT NULL,
   PRIMARY KEY(SO_ident, BL_ident),
   FOREIGN KEY(SO_ident) REFERENCES SO_soldat(SO_ident),
   FOREIGN KEY(YB_ident) REFERENCES YB_type_blessure(YB_ident)
);

CREATE TABLE PA_participer(
   SO_ident INT,
   BA_ident INT,
   CA_date DATE,
   CA_date_1 DATE,
   PRIMARY KEY(SO_ident, BA_ident, CA_date, CA_date_1),
   FOREIGN KEY(SO_ident) REFERENCES SO_soldat(SO_ident),
   FOREIGN KEY(BA_ident) REFERENCES BA_bataille(BA_ident)
);

CREATE TABLE IN_infliger(
   BA_ident INT,
   SO_ident INT,
   BL_ident INT,
   PRIMARY KEY(BA_ident, SO_ident, BL_ident),
   FOREIGN KEY(BA_ident) REFERENCES BA_bataille(BA_ident),
   FOREIGN KEY(SO_ident, BL_ident) REFERENCES BL_blessure(SO_ident, BL_ident)
);

alter table PA_participer
        add constraint PA_CK01_DATES (CA_date_1 >= CA_date)

Create TRIGGER...

[fsmrel]

Bonsoir,


nanneb, nos propos peuvent vous paraître parfois hermétiques : n’hésitez pas à demander des explications...

Il faut ensuite mettre en oeuvre une Contrainte d'Intégrité Fonctionnelle (CIF) qui stipule qu'un soldat ne peut être blessé que dans une bataille dans laquelle il a participé

Pour tous les auteurs de référence, Hubert Tardieu, Arnold Rochfeld, Dominique Nanci, etc., une CIF est une dépendance fonctionnelle, c’est-à-dire une contrainte d’unicité (voyez par exemple Ingénierie des systèmes d'information : Merise deuxième génération (4e édition, 2001), pages 114 et suivantes).

Dans la mesure où l’on ne considère que les blessures infligées dans des batailles, une contrainte d’inclusion est à mettre en oeuvre (cf. le même ouvrage, page 124) :

Association PA_participer et table résultante :
J'ai créé une entité-type CA_calendrier, qui ne générera pas de table, et qui permet d'ajouter une date de début et de fin de participation dans la PK de la table issue de l'association "PA_participer"
Ce sont ces dates qui permettront de contrôler au moyen d'un trigger qu'un soldat ne peut pas participer à deux batailles dans une même période.
Il faudra également intervenir dans le script pour évacuer l'identifiant de la bataille de la PK de la table PA_participer (voir plus bas)
Un contrainte de type check est ajoutée pour vérifier que la date de début est inférieure ou égale à la date de fin de participation

Si le SGBD utilisé est PostgreSQL, plutôt qu’utiliser des dates de début et de fin, utiliser des intervalles de dates (type DATERANGE avec ses fonctions de contrôle de recouvrement, permettant de simplifier les triggers de contrôle).

Pour que l’identifiant de BATAILLE soit de facto évacué de la clé primaire de la table PARTICIPATION, mettre en oeuvre cette fois-ci une CIF, Looping se chargera de l’évacuation :

A partir du MCD ci-dessus, au stade SQL, on aura ceci :

CREATE TABLE PARTICIPATION
(
        soldatId              INTEGER            NOT NULL
      , batailleId            INTEGER            NOT NULL
      , participationPeriode  DATERANGE          NOT NULL
    , CONSTRAINT PARTICIPATION_PK PRIMARY KEY(soldatId, participationPeriode)
    , CONSTRAINT PARTICIPATION_SOLDAT_FK FOREIGN KEY(soldatId) 
          REFERENCES SOLDAT(soldatId)
    , CONSTRAINT PARTICIPATION_BATAILLE_FK FOREIGN KEY(batailleId)
          REFERENCES BATAILLE(batailleId)
); 

Maintenant, si le SGBD utilisé ne permet pas de traiter des intervalles, alors on se rabattra effectivement sur la paire (date début, date fin), mais sans faire intervenir la date de fin à partir de l’entité-type CALENDRIER (rebaptisée ici DATEDEBUT), en effet au stade SQL le triplet {soldatId, participationDebut, participationFin} est surclé de la table PARTICIPATION (unicité respectée) mais pas clé candidate (irréductibilité non respectée) ; la clé primaire doit être la paire {soldatId, participationDebut}, ce que Looping assure. Pour des raisons de symétrie, on lui demande d’ajouter la règle d’unicité {soldatId, participationFin}. Maintenant, il est évident qu’il faudra éviter la bilocation par trigger (si l’on admet qu’un soldat ne peut pas avoir participé le même jour à deux batailles distinctes...)

Exemple de MCD :

Code SQL :

CREATE TABLE PARTICIPATION
(
        soldatId              INTEGER            NOT NULL
      , batailleId            INTEGER            NOT NULL
      , participationDebut    DATE               NOT NULL
      , participationFin      DATE               NOT NULL
    , CONSTRAINT PARTICIPATION_PK PRIMARY KEY(soldatId, participationDebut)
    , CONSTRAINT PARTICIPATION_SOLDAT_FK FOREIGN KEY(soldatId) 
          REFERENCES SOLDAT(soldatId)
    , CONSTRAINT PARTICIPATION_BATAILLE_FK FOREIGN KEY(batailleId)
          REFERENCES BATAILLE(batailleId)
);

ALTER TABLE PARTICIPATION
    ADD CONSTRAINT PARTICIPATION_AK UNIQUE (soldatId, participationFin) ;

ALTER TABLE PARTICIPATION
    ADD CONSTRAINT PARTICIPATION_CHK1 CHECK (participationFin >= participationDebut
        AND participationDebut between '1914-08-03' AND '1918-11-10'
        AND participationFin between '1914-08-03' AND '1918-11-10') ;

A noter qu’un soldat peut avoir participé plus d’une fois à la même bataille, mais à des périodes distinctes (trigger en vue...)

On peut produire le même code SQL en simplifiant le MCD, par transformation de l’association PARTICIPATION en entité-type et en utilisant l’identification relative :

Retour sur les blessures.

Les blessures peuvent être rendues strictement dépendantes des participations des soldats :

J’ai supposé qu’un soldat pouvait être blessé plus d’une fois à la même date (sinon, rendre clé alternative (voire primaire) le triplet {soldatId, participationDebut, blessureDate}).

Restera à s’assurer par trigger que la date d’une blessure se situe dans l’intervalle date début/date fin de participation du soldat concerné.

code SQL

CREATE TABLE BLESSURE
(
        soldatId              INTEGER            NOT NULL
      , participationDebut    DATE               NOT NULL
      , blessureId            INTEGER            NOT NULL
      , blessureDate          DATE               NOT NULL
      , blessureTypeId        INTEGER            NOT NULL
    , CONSTRAINT BLESSURE_PK PRIMARY KEY(soldatId, participationDebut, blessureId)
    , CONSTRAINT BLESSURE_PARTICIPATION_FK FOREIGN KEY(soldatId, participationDebut) 
          REFERENCES PARTICIPATION(soldatId, participationDebut)
    , CONSTRAINT BLESSURE_BLESSURE_TYPE_FK FOREIGN KEY(blessureTypeId) 
          REFERENCES BLESSURE_TYPE(blessureTypeId)
);

Il faudra qu’on parle des grades et des unités.

nanneb, quel est votre SGBD ?

Et n’hésitez pas à utiliser la balise QUOTE.

En tout cas, bon courage !

 

[escartefigue]

Bonjour François

Dans la mesure où l’on ne considère que les blessures infligées dans des batailles, une contrainte d’inclusion est à mettre en oeuvre (cf. le même ouvrage, page 124) :

Je n'étais pas parti sur cette approche, mais bien sur l'ensemble des blessures possibles :

entité-type BL_blessure :
Je l'avais omise dans mon schéma précédent, elle est identifiée relativement au soldat, est en lien avec un type de blessure et n'est pas obligatoirement infligée lors d'une bataille (le soldat a pu se blesser hors combat).

nanneb choisira selon le besoin : blessures de guerre seulement ou ensemble des blessures

Si le SGBD utilisé est PostgreSQL, plutôt qu’utiliser des dates de début et de fin, utiliser des intervalles de dates (type DATERANGE avec ses fonctions de contrôle de recouvrement, permettant de simplifier les triggers de contrôle).

Excellente idée , j'avoue ne pas être familier de ce type, c'est pourquoi je n'y avais pas pensé.

[nanneb]

Bonjour,

Je vous remercie escartefigue et fsmrel de vous être penchés sur le MCD.

Je parviens à saisir quelques unes de vos explications, mais il va me falloir un peu de temps pour comprendre et digérer l'ensemble de vos propositions : je ne suis encore qu'un bébé en base données...