Conception d'un MCD pour une assurance automobile

**fsmrel** · 02/12/2020, 04h51

Bonsoir Zidane7,

Je rappelle ce que j’ai signalé dans le post #76 mais vous avez oublié de faire la modification en conséquence :

CATEGORIE n’est pas à identifier relativement à CEDEAO (elle n’en est pas la propriété), il faut remplacer 1,1(R) par 1,1 sur la patte connectant CATEGORIE et CAT_CEDEAO :

=>

[CEDEAO]--1,N----(CAT_CEDEAO)---- 1,1--[CATEGORIE]

Je rappelle aussi ce que j’ai déjà signalé dans le post #76 :

Les cardinalités portées par les pattes de l’association connectant les entités-types CEDEAO et CEDAO_HISTO sont à permuter et deviennent :

[CEDEAO]--0,N----( )----1,1(R)--[CEDAO_HISTO]

Sinon, si vous persistez dans votre choix de modélisation, cela revient à dire que la CEDEAO active aujourd’hui dépend d’une CEDEAO historisée ce qui n’a strictement aucun sens, et qu’à une CEDEAO historisée peuvent correspondre plusieurs CEDEAO actives ce qui là encore n’a vraiment aucun sens.

Au stade SQL (les CREATE TABLE), regardez de près les conséquences de votre choix quant à la structure de votre table CEDEAO, laquelle est donc dotée d’une clé étrangère faisant référence à la table CEDAO_HISTO et confirmant que la CEDEAO active doit faire référence à une CEDEAO historisée : jamais un tel montage ne pourra fonctionner !

Question 1 : au vu de tout cela, quel est votre degré de connaissance du langage SQL ? (Non seulement en ce qui concerne la structure des tables, mais aussi leur manipulation, à savoir SELECT, INSERT, etc., sans oublier les triggers).

Retour des sous-catégories

Dans le post #71, vous aviez écrit :

Envoyé par Zidane7

D'après mes analyses, j'ai jugé nécessaire de supprimer l'entité Sous-catégorie

En conséquence, dans le post #75 vous associez l’entité-type TARIF aux trois entités-types CATEGORIE, CAT_COMPLEMENT et PUISSANCE, ce qui est un choix tout à fait pertinent.

Dans le post #80, vous supprimez cette fois-ci le lien entre les entités-types TARIF et CATEGORIE, et pour pallier, vous créez un lien entre les entités-types CATEGORIE et CAT_COMPLEMENT, en identifiant la seconde relativement à la première :

Votre nouvelle modélisation (qui est correcte elle aussi) est en fait analogue à celle que j’avais proposée dans le post #55 où l’entité-type TARIF est nommée PFC et CAT_COMPLEMENT est nommée SOUS_CATEGORIE.

Question 2 : pour quelle raison êtes-vous revenu à cette modélisation alors que dans le post #71 vous aviez jugé nécessaire de supprimer l'entité SOUS-CATEGORIE ?

A propos des véhicules

Vous établissez une association VEHICULE_PUIS entre les entités-types VEHICULE et PUISSANCE. Au vu du MCD ceci est légitime puisque vous avez supprimé le lien entre les entités-types VEHICULE et TARIF, lien permettant via TARIF d’associer transitivement VEHICULE et PUISSANCE (voir votre post #75). Mais comme l’entité-type CAT_COMPLEMENT est maintenant identifiée relativement à l’entité-type CATEGORIE, l’entité-type VEHICULE contient les attributs catId, catComplementId outre l’attribut puissanceId, et grâce à ces trois attributs, on sait déterminer le tarif pour chaque véhicule : ouf !

Les remorques

Vous avez isolé les remorques en mettant en oeuvre une entité-type REMORQUE qui est en fait une spécialisation de l’entité-type CATEGORIE, d’accord sur le principe, mais il y a quand même un problème : chaque occurrence de l’entité-type CATEGORIE fait référence à une occurrence de l’entité-type CEDEAO, ce qui vaut donc pour les remorques . Etant donné que l’entité-type REMORQUE contient un attribut cedeaoRemorque, d’une part il y a redondance et d’autre part se pose le problème de la mise en relation des remorques et de l’historique des CEDEAO.

Question 3 : comment vous positionnez-vous au sujet des remorques quant à la redondance et à l’historique des CEDEAO ?

J’ai d’autres remarques à faire (notamment la défense recours), mais pour le moment j’attendrai déjà que vous répondiez à mes questions ci-dessus.

En attendant, pour les tables dont la structure a changé (CAT_COMPLEMENT, TARIF, VEHICULE) le code SQL devient :

CREATE TABLE CAT_COMPLEMENT
(
   catId        INT,
   catComplementId INT IDENTITY,
   catComplementCode TINYINT NOT NULL,
   catComplementLibelle VARCHAR(96) NOT NULL,
   CONSTRAINT CAT_COMPLEMENT_PK PRIMARY KEY(catId, catComplementId),
   CONSTRAINT CAT_COMPLEMENT_AK UNIQUE(catComplementCode),
   CONSTRAINT CAT_COMPLEMENT_CATEGORIE_FK FOREIGN KEY (catId)
       REFERENCES CATEGORIE  (catId)
);

INSERT INTO CAT_COMPLEMENT (catId, catComplementCode, catComplementLibelle)
VALUES
    ((SELECT catId FROM CATEGORIE WHERE catCode = '1')
     , 11, '')  
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '2')
     , 21, '')  
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '3')
     , 31, 'Marchandises (tonnage)')  
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '3')
     , 32, 'Remorque')  
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '4')
     , 41, 'Taxis urbains et inter-urbains - Taxis 4 places')  
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '4')
     , 42, 'Taxis urbains et inter-urbains - Taxis 5 places')  
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '4')
     , 43, 'Taxis urbains et inter-urbains - Taxis 6 places')  
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '4')
     , 48, 'Transport public de voyageurs - TPV 18 places')  
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '5')
     , 51, '')  
;
SELECT catCode, catLibelle
     , catComplementCode, catComplementLibelle 
FROM CATEGORIE as x JOIN CAT_COMPLEMENT as y ON x.catId = y.catId 
;

=>

catCode catLibelle                                                 catComplCode catComplementLibelle

1       Catégorie 1 : Affaires et promenades (AP)                  11           
2       Catégorie 2 : Transport pour le compte de l'assuré (TPC)   21           
3       Catégorie 3 : Transport public de marchandises (TPM)       31           Marchandises (tonnage)
3       Catégorie 3 : Transport public de marchandises (TPM)       32           Remorque
4       Catégorie 4 :                                              41           Taxis urbains et inter-urbains - Taxis 4 places
4       Catégorie 4 :                                              42           Taxis urbains et inter-urbains - Taxis 5 places
4       Catégorie 4 :                                              43           Taxis urbains et inter-urbains - Taxis 6 places
4       Catégorie 4 :                                              48           Transport public de voyageurs - TPV 18 places
5       Catégorie 5 : Motos                                        51

Les tarifs

CREATE TABLE TARIF
(
   catId INT,
   puissanceId INT,
   catComplementId INT,
   tarifDateDepuis DATE NOT NULL,
   respCivile INT NOT NULL,
   CONSTRAINT TARIF_PK PRIMARY KEY(catId, puissanceId, catComplementId),
   CONSTRAINT TARIF_PUISSANCE_FK FOREIGN KEY(puissanceId) 
       REFERENCES PUISSANCE(puissanceId),
   CONSTRAINT TARIF_CAT_COMPLEMENT_FK FOREIGN KEY(catId, catComplementId) 
       REFERENCES CAT_COMPLEMENT(catId, catComplementId)
);

-- TARIF, cas de la catégorie 1

INSERT INTO TARIF (puissanceId, catId, catComplementId, tarifDateDepuis, respCivile)
VALUES
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C1')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '1')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 11)
    , '2020-10-01', 201657)
,
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '1')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 11)
    , '2020-10-01', 244399) 
,
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '1')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 11)
    , '2020-10-01', 285114) 
,
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '1')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 11)
    , '2020-10-01', 346213) 
,
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C5')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '1')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 11)
    , '2020-10-01', 407332) 
;
-- TARIF, cas de la catégorie 2

INSERT INTO TARIF (puissanceId, catId, catComplementId, tarifDateDepuis, respCivile)
VALUES
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '2')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 21)
--------------------------    , '2020-10-01', 401383)
    , '1900-01-01', 401383)
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '2')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 21)
------------------    , '2020-10-01', 563124)
    , '1900-01-01', 563124)
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '2')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 21)
---------------   , '2020-10-01', 583830)
   , '1900-01-01', 583830)
;

-- Cas de la catégorie 3

iNSERT INTO TARIF (puissanceId, catId, catComplementId, tarifDateDepuis, respCivile)
VALUES
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'T1')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '3')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 31)
---------------    , '2020-10-01', 578610)
    , '1900-01-01', 578610)
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'T2')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '3')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 31)
-----------------    , '2020-10-01', 694332)
    , '1900-01-01', 694332)
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'R1')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '3')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 32)
 -----------------   , '2020-10-01', 1000000) 
    , '1900-01-01', 1000000) 
;

-- Cas de la catégorie 4

INSERT INTO TARIF (puissanceId, catId, catComplementId, tarifDateDepuis, respCivile)
VALUES
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')
   , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 41)
   , '2020-10-04', 690102)
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3')
   , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 41)
-------------   , '2020-10-04', 728486)
   , '1900-01-01', 728486)
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4')
   , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 41)
---------   , '2020-10-04', 806813) 
   , '1900-01-01', 806813) 
,

    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C5')
   , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 41)
-------------   , '2020-10-04', 846877) 
   , '1900-01-01', 846877) 
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')
   , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 42)
 -----------------  , '2020-10-04', 784239) 
   , '1900-01-01', 784239) 
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3')
   , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 42)
 ---------- , '2020-10-04', 822563) 
   , '1900-01-01', 822563) 
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4')
   , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 42)
----------   , '2020-10-04', 900947) 
   , '1900-01-01', 900947) 
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C5')
   , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 42)
------------   , '2020-10-04', 941015) 
   , '1900-01-01', 941015) 
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')
   , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 48)
 -----------  , '2020-10-04', 1097723) 
   , '1900-01-01', 1097723) 
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3')
   , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 48)
--------------   , '2020-10-04', 1115468) 
   , '1900-01-01', 1115468) 
;
-- Cas de la catégorie 5

INSERT INTO TARIF (puissanceId, catId, catComplementId, tarifDateDepuis, respCivile)
VALUES
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '5')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 51)
    , '2020-12-05', 75000)
, 
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '5')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 51)
    , '2019-02-02', 80000)
, 
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '5')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 51)
--------------    , '2019-05-02', 85000)
    , '1900-01-01', 85000)
;

--------------------------------------------------------------
-- voyons voir
--------------------------------------------------------------
SELECT puissanceCode as puissance, unite, borneInf as inf, borneSup as sup 
     , LEFT(CONCAT(catLibelle , ' ', catComplementLibelle),76) as cat
     , tarifDateDepuis as depuis
     , respCivile
FROM   TARIF AS x 
  JOIN PUISSANCE AS y ON x.puissanceId = y.puissanceId
  JOIN CATEGORIE AS z ON x.catId = z.catId
  JOIN CAT_COMPLEMENT as t ON x.catId = t.catId 
                          AND x.catComplementId = t.catComplementId
ORDER BY cat, puissance     
;

Résultat identique à celui du post #79 :

puissance unite     inf sup  cat                                                                depuis      respCivile

C1        CV        0   2    Catégorie 1 : Affaires et promenades (AP)                          2020-10-01  201657
C2        CV        3   6    Catégorie 1 : Affaires et promenades (AP)                          2020-10-01  244399
C3        CV        7   10   Catégorie 1 : Affaires et promenades (AP)                          2020-10-01  285114
C4        CV        11  14   Catégorie 1 : Affaires et promenades (AP)                          2020-10-01  346213
C5        CV        15  23   Catégorie 1 : Affaires et promenades (AP)                          2020-10-01  407332
C2        CV        3   6    Catégorie 2 : Transport pour le compte de l'assuré (TPC)           1900-01-01  401383
C3        CV        7   10   Catégorie 2 : Transport pour le compte de l'assuré (TPC)           1900-01-01  563124
C4        CV        11  14   Catégorie 2 : Transport pour le compte de l'assuré (TPC)           1900-01-01  583830
T1        tonne     0   1    Catégorie 3 : Transport public de marchandises (TPM) Marchandises  1900-01-01  578610
T2        tonne     2   5    Catégorie 3 : Transport public de marchandises (TPM) Marchandises  2020-10-01  694332
R1        remorque  0   0    Catégorie 3 : Transport public de marchandises (TPM) Remorque      1900-01-01  1000000
C2        CV        3   6    Catégorie 4 : Taxis urbains et inter-urbains - Taxis 4 places      2020-10-04  690102
C3        CV        7   10   Catégorie 4 : Taxis urbains et inter-urbains - Taxis 4 places      1900-01-01  728486
C4        CV        11  14   Catégorie 4 : Taxis urbains et inter-urbains - Taxis 4 places      1900-01-01  806813
C5        CV        15  23   Catégorie 4 : Taxis urbains et inter-urbains - Taxis 4 places      1900-01-01  846877
C2        CV        3   6    Catégorie 4 : Taxis urbains et inter-urbains - Taxis 5 places      1900-01-01  784239
C3        CV        7   10   Catégorie 4 : Taxis urbains et inter-urbains - Taxis 5 places      2020-10-04  822563
C4        CV        11  14   Catégorie 4 : Taxis urbains et inter-urbains - Taxis 5 places      2020-10-04  900947
C5        CV        15  23   Catégorie 4 : Taxis urbains et inter-urbains - Taxis 5 places      2020-10-04  941015
C2        CV        3   6    Catégorie 4 : Transport public de voyageurs - TPV 18 places        2020-10-04  1097723
C3        CV        7   10   Catégorie 4 : Transport public de voyageurs - TPV 18 places        2020-10-04  1115468
C2        CV        3   6    Catégorie 5 : Motos                                                2020-12-05  75000
C3        CV        7   10   Catégorie 5 : Motos                                                2019-02-02  80000
C4        CV        11  14   Catégorie 5 : Motos                                                1900-01-01  85000

Historique des tarifs

CREATE TABLE TARIF_HISTO
(
   catId INT,
   puissanceId INT,
   catComplementId INT,
   tarifDebutHisto DATE,
   tarifFinHisto DATE NOT NULL,
   responsabiliteCivileHisto INT NOT NULL,
   CONSTRAINT TARIF_HISTO_PK PRIMARY KEY(catId, puissanceId, catComplementId, tarifDebutHisto),
   CONSTRAINT TARIF_HISTO_AK UNIQUE(catId, puissanceId, catComplementId, tarifFinHisto),
   CONSTRAINT TARIF_HISTO_TARIF_FK FOREIGN KEY(catId, puissanceId, catComplementId) 
       REFERENCES TARIF(catId, puissanceId, catComplementId)
 , CONSTRAINT TARIF_HISTO_DATES CHECK (tarifFinHisto > tarifDebutHisto)
);

INSERT INTO TARIF_HISTO (catId, puissanceId, catComplementId, tarifDebutHisto, tarifFinHisto, responsabiliteCivileHisto)
VALUES
     ((SELECT catId FROM CATEGORIE WHERE catCode = '1')
   , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C1')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 11)
   , '2015-01-01', '2015-12-31', 180000)
,
    ((SELECT catId FROM CATEGORIE WHERE catCode = '1')
   , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C1')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 11)
   , '2016-01-01', '2017-10-31', 185000)
,
   ((SELECT catId FROM CATEGORIE WHERE catCode = '1')
   , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C1')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 11)
   , '2017-11-01', '2018-10-03', 190000)
,
    ((SELECT catId FROM CATEGORIE WHERE catCode = '1')
   , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C1')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 11)
   , '2018-10-04', '2019-08-03', 195000)
,
    ((SELECT catId FROM CATEGORIE WHERE catCode = '1')
   , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C1')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 11)
   , '2019-08-04', '2020-09-30', 196000)
,
--------- C2
    ((SELECT catId FROM CATEGORIE WHERE catCode = '1')
   , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 11)
   , '2015-01-01', '2015-12-31', 200000)
,
    ((SELECT catId FROM CATEGORIE WHERE catCode = '1')
   , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 11)
   , '2016-01-01', '2017-12-31', 210000)
,
    ((SELECT catId FROM CATEGORIE WHERE catCode = '1')
   , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 11)
   , '2018-01-01', '2020-09-30', 220000)
,
--------- C3
    ((SELECT catId FROM CATEGORIE WHERE catCode = '1')
   , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 11)
   , '2015-01-01', '2015-12-31', 240000)
,
    ((SELECT catId FROM CATEGORIE WHERE catCode = '1')
   , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 11)
   , '2016-01-01', '2017-12-31', 260000)
,
    ((SELECT catId FROM CATEGORIE WHERE catCode = '1')
   , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 11)
   , '2018-01-01', '2020-09-30', 270000)
,
--------- C4
    ((SELECT catId FROM CATEGORIE WHERE catCode = '1')
   , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 11)
   , '2015-01-01', '2015-12-31', 310000)
,
    ((SELECT catId FROM CATEGORIE WHERE catCode = '1')
   , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 11)
   , '2016-01-01', '2017-12-31', 320000)
,
    ((SELECT catId FROM CATEGORIE WHERE catCode = '1')
   , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 11)
   , '2018-01-01', '2020-09-30', 330000)
,
--------- C5
    ((SELECT catId FROM CATEGORIE WHERE catCode = '1')
   , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C5')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 11)
   , '2015-01-01', '2015-12-31', 380000)
,
    ((SELECT catId FROM CATEGORIE WHERE catCode = '1')
   , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C5')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 11)
   , '2016-01-01', '2017-12-31', 390000)
,
    ((SELECT catId FROM CATEGORIE WHERE catCode = '1')
   , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C5')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 11)
   , '2018-01-01', '2018-12-31', 395000)
,
    ((SELECT catId FROM CATEGORIE WHERE catCode = '1')
   , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C5')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 11)
   , '2019-01-01', '2020-09-30', 400000)
;

INSERT INTO TARIF_HISTO (catId, puissanceId, catComplementId, tarifDebutHisto, tarifFinHisto, responsabiliteCivileHisto)
VALUES
    ((SELECT catId FROM CATEGORIE WHERE catCode = '4')
   , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 41)
   , '2015-01-01', '2017-12-31', 650000)
,
    ((SELECT catId FROM CATEGORIE WHERE catCode = '4')
   , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 41)
   , '2018-01-01', '2019-10-31', 670000)
,
    ((SELECT catId FROM CATEGORIE WHERE catCode = '4')
   , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 41)
   , '2019-11-01', '2020-10-03', 690000)
;

INSERT INTO TARIF_HISTO (catId, puissanceId, catComplementId, tarifDebutHisto, tarifFinHisto, responsabiliteCivileHisto)
VALUES
    ((SELECT catId FROM CATEGORIE WHERE catCode = '5')
   , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 51)
   , '1900-01-01', '2020-12-04', 68000)
,
    ((SELECT catId FROM CATEGORIE WHERE catCode = '5')
   , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 51)
   , '1900-01-01', '2019-02-01', 77000)
;

--------------------------------------------------------------
-- voyons voir
--------------------------------------------------------------
SELECT '' as TARIF_HISTO,
       puissanceCode as puissance, unite, borneInf as inf, borneSup as sup
     , LEFT(CONCAT(catLibelle , ' ', catComplementLibelle),76) as cat
     , tarifDebutHisto as debut, tarifFinHisto as fin
     , responsabiliteCivileHisto as respCiv
FROM   TARIF_HISTO AS x 
  JOIN PUISSANCE AS y ON x.puissanceId = y.puissanceId
  JOIN CATEGORIE AS z ON x.catId = z.catId
  JOIN CAT_COMPLEMENT as t ON x.catComplementId = t.catComplementId 
ORDER BY cat, unite, borneInf, tarifDebutHisto    
;

Résultat identique à celui du post #79 :

puissance unite     inf sup  cat                                                            début      fin        respCiv

C1        CV        0   2    Catégorie 1 : Affaires et promenades (AP)                      2015-01-01 2015-12-31 180000
C1        CV        0   2    Catégorie 1 : Affaires et promenades (AP)                      2016-01-01 2017-10-31 185000
C1        CV        0   2    Catégorie 1 : Affaires et promenades (AP)                      2017-11-01 2018-10-03 190000
C1        CV        0   2    Catégorie 1 : Affaires et promenades (AP)                      2018-10-04 2019-08-03 195000
C1        CV        0   2    Catégorie 1 : Affaires et promenades (AP)                      2019-08-04 2020-09-30 196000
C2        CV        3   6    Catégorie 1 : Affaires et promenades (AP)                      2015-01-01 2015-12-31 200000
C2        CV        3   6    Catégorie 1 : Affaires et promenades (AP)                      2016-01-01 2017-12-31 210000
C2        CV        3   6    Catégorie 1 : Affaires et promenades (AP)                      2018-01-01 2020-09-30 220000
C3        CV        7   10   Catégorie 1 : Affaires et promenades (AP)                      2015-01-01 2015-12-31 240000
C3        CV        7   10   Catégorie 1 : Affaires et promenades (AP)                      2016-01-01 2017-12-31 260000
C3        CV        7   10   Catégorie 1 : Affaires et promenades (AP)                      2018-01-01 2020-09-30 270000
C4        CV        11  14   Catégorie 1 : Affaires et promenades (AP)                      2015-01-01 2015-12-31 310000
C4        CV        11  14   Catégorie 1 : Affaires et promenades (AP)                      2016-01-01 2017-12-31 320000
C4        CV        11  14   Catégorie 1 : Affaires et promenades (AP)                      2018-01-01 2020-09-30 330000
C5        CV        15  23   Catégorie 1 : Affaires et promenades (AP)                      2015-01-01 2015-12-31 380000
C5        CV        15  23   Catégorie 1 : Affaires et promenades (AP)                      2016-01-01 2017-12-31 390000
C5        CV        15  23   Catégorie 1 : Affaires et promenades (AP)                      2018-01-01 2018-12-31 395000
C5        CV        15  23   Catégorie 1 : Affaires et promenades (AP)                      2019-01-01 2020-09-30 400000
C2        CV        3   6    Catégorie 4 : Taxis urbains et inter-urbains - Taxis 4 places  2015-01-01 2017-12-31 650000
C2        CV        3   6    Catégorie 4 : Taxis urbains et inter-urbains - Taxis 4 places  2018-01-01 2019-10-31 670000
C2        CV        3   6    Catégorie 4 : Taxis urbains et inter-urbains - Taxis 4 places  2019-11-01 2020-10-03 690000
C2        CV        3   6    Catégorie 5 : Motos                                            1900-01-01 2020-12-04 68000
C3        CV        7   10   Catégorie 5 : Motos                                            1900-01-01 2019-02-01 77000

Vue générale des tarifs (depuis et durant)

CREATE VIEW TARIF_TOUT (puissanceId, puissanceCode, unite, borneInf, borneSup
                      , catId, catCode, catComplementId, catComplCode, cat
                      , dateDebut, dateFin, respCivile)
AS 
SELECT x.puissanceId, puissanceCode, unite, borneInf, borneSup 
     , x.catId, catCode, x.catComplementId, catComplementCode
     , LEFT(CONCAT(catLibelle , ' ', catComplementLibelle),76) as cat
     , tarifDateDepuis, '9999-12-31'
     , respCivile
FROM   TARIF AS x 
  JOIN PUISSANCE AS y ON x.puissanceId = y.puissanceId
  JOIN CATEGORIE AS z ON x.catId = z.catId
  JOIN CAT_COMPLEMENT as t ON x.catId = t.catId 
                          AND x.catComplementId = t.catComplementId

UNION

SELECT x.puissanceId, puissanceCode, unite, borneInf, borneSup 
     , x.catId, catCode, x.catComplementId, catComplementCode
     , LEFT(CONCAT(catLibelle , ' ', catComplementLibelle),76) as cat
     , tarifDebutHisto, tarifFinHisto
     , responsabiliteCivileHisto
FROM   TARIF_HISTO AS x 
  JOIN PUISSANCE AS y ON x.puissanceId = y.puissanceId
  JOIN CATEGORIE AS z ON x.catId = z.catId
  JOIN CAT_COMPLEMENT as t ON x.catId = t.catId 
                          AND x.catComplementId = t.catComplementId
;

Les clients et les contrats : voir post #76.

Les véhicules :

CREATE TABLE VEHICULE
(
   vehiculeId INT IDENTITY,
   vehiculeCode CHAR(8) NOT NULL,
   vehiculeSerie CHAR(17) NOT NULL,
   vehiculePuissance INT NOT NULL,
   vehiculeMarque VARCHAR(24) NOT NULL,
   puissanceId INT NOT NULL,
   catId INT NOT NULL,
   catComplementId INT NOT NULL,
   contratId INT NOT NULL,
   CONSTRAINT VEHICULE_PK PRIMARY KEY(vehiculeId),
   CONSTRAINT VEHICULE_AK UNIQUE(vehiculeCode),
   CONSTRAINT VEHICULE_S_AK UNIQUE(vehiculeSerie),
   CONSTRAINT VEHICULE_PUISSANCE_FK FOREIGN KEY(puissanceId) 
       REFERENCES PUISSANCE(puissanceId),
   CONSTRAINT VEHICULE_TARIF_FK FOREIGN KEY(catId, catComplementId) 
       REFERENCES CAT_COMPLEMENT (catId, catComplementId),
   CONSTRAINT VEHICULE_CONTRAT_FK FOREIGN KEY(contratId) 
       REFERENCES CONTRAT(contratId)
);

INSERT INTO VEHICULE (vehiculeCode, vehiculeSerie, vehiculePuissance, vehiculeMarque
                    , catId, puissanceId, catComplementId, contratId)
VALUES
    ('veh001', 'FN345678901234567', 8, 'Ford' 
    , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
    , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode= 'C2')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 41)  
    , (SELECT contratId FROM CONTRAT WHERE contratCode = 'ctr001'))

,
    ('veh011', 'VM345678901234567', 15, 'Mercedes' 
    , (SELECT catId FROM CATEGORIE WHERE catCode = '1')
    , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode= 'C5')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 11)  
    , (SELECT contratId FROM CONTRAT WHERE contratCode = 'ctr021'))
,  
    ('veh012', 'VR345678901234567', 7, 'Renault' 
    , (SELECT catId FROM CATEGORIE WHERE catCode = '1')
    , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode= 'C3')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 11)  
    , (SELECT contratId FROM CONTRAT WHERE contratCode = 'ctr021'))
,  
    ('veh013', 'VR456789012345678', 8, 'Ford' 
    , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
    , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')  /* 8 CV => C3....*/
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 41)  
    , (SELECT contratId FROM CONTRAT WHERE contratCode = 'ctr022'))
,  
    ('veh014', 'VR567890123456789', 6, 'Ford' 
    , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
    , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 41)  
    , (SELECT contratId FROM CONTRAT WHERE contratCode = 'ctr023'))
,
    ('veh015', 'MO567890123456789', 10, 'Honda' 
    , (SELECT catId FROM CATEGORIE WHERE catCode = '5')
    , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 51)  
    , (SELECT contratId FROM CONTRAT WHERE contratCode = 'ctr021'))
,
    ('veh016', 'MO678901234567890', 12, 'Triumph' 
    , (SELECT catId FROM CATEGORIE WHERE catCode = '5')
    , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 51)  
    , (SELECT contratId FROM CONTRAT WHERE contratCode = 'ctr021'))
;
SELECT vehiculeCode as vehCode
     , vehiculeSerie as vehSerie, vehiculePuissance as vehPuiss, vehiculeMarque as marque
     , puissanceCode as puissCode
     , catCode
     , catComplementCode as compCode
     , contratCode as ctrCode
FROM   VEHICULE as x 
  JOIN CATEGORIE as y ON x.catId = y.catId
  JOIN CAT_COMPLEMENT as z ON x.catId = z.catId
                          AND x.catComplementId = z.catComplementId
  JOIN PUISSANCE as t ON x.puissanceId = t.puissanceId 
  JOIN CONTRAT as u ON x.contratId = u.contratId
;

=>

vehCode  vehSerie            vehPuiss  marque                   puissCode catCode compCode ctrCode

veh001   FN345678901234567   8         Ford                     C2        4       41       ctr001
veh011   VM345678901234567   15        Mercedes                 C5        1       11       ctr021
veh012   VR345678901234567   7         Renault                  C3        1       11       ctr021
veh013   VR456789012345678   8         Ford                     C2        4       41       ctr022
veh014   VR567890123456789   6         Ford                     C2        4       41       ctr023
veh015   MO567890123456789   10        Honda                    C3        5       51       ctr021
veh016   MO678901234567890   12        Triumph                  C4        5       51       ctr021

Une vue permettant de voir l’ensemble des véhicules avec leur responsabilité civile et leur CEDEAO :

CREATE VIEW VEHICULE_TARIF_CEDEAO
    (vehiculeCode, vehiculePuissance, contratCode, contratDateEffet
   , catCode, catComplCode
   , tarifDebut, tarifFin, respCivile
   , cedeaoCode, cedeaoDebut, cedeaoFin, cedeaoMontant  
   , catLibelle     
   , puissanceCode, unite, borneInf, borneSup
)
AS
SELECT vehiculeCode, vehiculePuissance
     , contratCode, contratDateEffet
     , x.catCode, x.catComplCode
     , x.tarifDebut, x.tarifFin , x.respCivile
     , z.cedeaoCode, z.cedeaoDebut, z.cedeaoFin, z.cedeaoMontant 
     , x.catLibelle     
     , x.puissanceCode, x.unite, x.borneInf, x.borneSup
FROM   VEHICULE_TARIF as x
  JOIN CATEGORIE as y ON x.catCode = y.catCode
  JOIN CEDEAO_TOUT as z ON y.cedeaoId = z.cedeaoId
WHERE  x.contratDateEffet >= z.cedeaoDebut
   AND x.contratDateEffet <= z.cedeaoFin 
;

=>

vehCode  vehPuiss ctrCode ctrEffet    catCode complCode tarifDebut tarifFin   respCiv  cedeaoCode cedeaoDebut cedeaoFin  montant   catLibelle       puisCode unite  borneInf borneSup

veh001   8        ctr001  2019-06-14  4       41        2018-01-01 2019-10-31 670000   c1         2019-01-02  9999-12-31 100000    Taxis 4 places   C2       CV    3        6
veh011   15       ctr021  2018-05-04  1       11        2018-01-01 2018-12-31 395000   c1         2018-01-01  2019-01-01 95000     Affaires, prom   C5       CV    15       23
veh012   7        ctr021  2018-05-04  1       11        2018-01-01 2020-09-30 270000   c1         2018-01-01  2019-01-01 95000     Affaires, prom   C3       CV    7        10
veh013   8        ctr022  2019-01-03  4       41        2018-01-01 2019-10-31 670000   c1         2019-01-02  9999-12-31 100000    Taxis 4 places   C2       CV    3        6
veh014   6        ctr023  2020-12-01  4       41        2020-10-04 9999-12-31 690102   c1         2019-01-02  9999-12-31 100000    Taxis 4 places   C2       CV    3        6
veh015   10       ctr021  2018-05-04  5       51        1900-01-01 2019-02-01 77000    c2         1900-01-01  2019-07-31 60000     Motos            C3       CV    7        10
veh016   12       ctr021  2018-05-04  5       51        1900-01-01 9999-12-31 85000    c2         1900-01-01  2019-07-31 60000     Motos            C4       CV    11       14

A noter que, par rapport à la table PUISSANCE, la puissance du véhicule 'veh013' (8 CV) est hors des limites (3 CV, 6 CV). Un trigger de contrôle s’impose...

**Zidane7** · 02/12/2020, 18h45

Bonsoir Monsieur fsmrel,
Merci pour vos remarques.

Question 1 : au vu de tout cela, quel est votre degré de connaissance du langage SQL ? (Non seulement en ce qui concerne la structure des tables, mais aussi leur manipulation, à savoir SELECT, INSERT, etc., sans oublier les triggers).

Pour ce qui conserne les select et les insert, j'ai un peu la maîtrise mais pour celui des triggers non.

Dans le post #80, vous supprimez cette fois-ci le lien entre les entités-types TARIF et CATEGORIE, et pour pallier, vous créez un lien entre les entités-types CATEGORIE et CAT_COMPLEMENT, en identifiant la seconde relativement à la première :
Votre nouvelle modélisation (qui est correcte elle aussi) est en fait analogue à celle que j’avais proposée dans le post #55 où l’entité-type TARIF est nommée PFC et CAT_COMPLEMENT est nommée SOUS_CATEGORIE.

Question 2 : pour quelle raison êtes-vous revenu à cette modélisation alors que dans le post #71 vous aviez jugé nécessaire de supprimer l'entité SOUS-CATEGORIE ?

C'était un problème d'incompréhension et je me suis rendu compte que vous aviez raison dans votre explication du poste #55 et cela après plusieurs analyses. Voulez-vous que je revienne sur la sous-catégorie au lieu de categorie complement?

A propos des véhicules
Vous établissez une association VEHICULE_PUIS entre les entités-types VEHICULE et PUISSANCE. Au vu du MCD ceci est légitime puisque vous avez supprimé le lien entre les entités-types VEHICULE et TARIF, lien permettant via TARIF d’associer transitivement VEHICULE et PUISSANCE (voir votre post #75). Mais comme l’entité-type CAT_COMPLEMENT est maintenant identifiée relativement à l’entité-type CATEGORIE, l’entité-type VEHICULE contient les attributs catId, catComplementId outre l’attribut puissanceId, et grâce à ces trois attributs, on sait déterminer le tarif pour chaque véhicule : ouf !

Ici comme vous avez bien remarqué, pour déterminer le tarif d'un véhicule, on passe par transitivité à travers la catégorie et la puissance qui sont liées au tarif.

Les remorques
Vous avez isolé les remorques en mettant en oeuvre une entité-type REMORQUE qui est en fait une spécialisation de l’entité-type CATEGORIE, d’accord sur le principe, mais il y a quand même un problème : chaque occurrence de l’entité-type CATEGORIE fait référence à une occurrence de l’entité-type CEDEAO, ce qui vaut donc pour les remorques . Etant donné que l’entité-type REMORQUE contient un attribut cedeaoRemorque, d’une part il y a redondance et d’autre part se pose le problème de la mise en relation des remorques et de l’historique des CEDEAO.
Question 3 : comment vous positionnez-vous au sujet des remorques quant à la redondance et à l’historique des CEDEAO ?

Je n'avais pas prêter attention à votre remarque. Merci une fois de plus.
Voici le MCD qui accompagne.
Quelles sont vos remarques et suggestions?
Merci par avance.

**fsmrel** · 02/12/2020, 19h56

Bonjour Zidane7,

Edit : J’ai envoyé mon message sans avoir vu que vous veniez de répondre au précédent. Je répondrai donc dès que possible. Ce qui suit concerne uniquement le post #80.

Au sujet de la défense recours

Je rappelle que dans le post #63, vous avez précisé que les taux de la défense recours sont à historiser.

Dans le post #64, j’ai proposé de mettre en oeuvre deux tables, une pour le taux en vigueur ce jour, et une table pour les taux historisés.

Dans le post #77, j’ai proposé de préférence ne faire qu’une seule table contenant à la fois le taux en vigueur ce jour et les taux historisés :

CREATE TABLE TAUX_DEFENSE_RECOURS
(
   tauxDrDebut DATE,
   tauxDrFin DATE NOT NULL,
   tauxDrValeur DECIMAL(4,2) NOT NULL,
   CONSTRAINT TAUX_DEFENSE_RECOURS_PK PRIMARY KEY(tauxDrDebut),
   CONSTRAINT TAUX_DEFENSE_RECOURS_AK UNIQUE(tauxDrFin)
 , CONSTRAINT TAUX_DEFENSE_RECOURS_DATES CHECK (tauxDrDebut < tauxDrFin)
);

De mon côté le MCD est à ce jour le suivant pour la partie qui nous intéresse :

Nom : Zidane7_assurance_auto (11)retour des sous-catégories(looping).png
Affichages : 274
Taille : 37,1 Ko

Dans ce MCD, vous noterez que l’entité-type TAUX_DEFENSE_RECOURS est un historique, avec date de début et date de fin des valeurs successives du taux de la défense recours. Il est inutile d’établir une relation matérialisée avec quelque autre entité-type que ce soit, comme on peut le voir dans mes posts précédents et dans ce que je propose ci-dessous.

De votre côté, vous ne modélisez que le dernier taux en vigueur, sans qu’on sache par ailleurs depuis quand il est applicable. Tout ceci est manifestement insuffisant et je vous invite à éplucher ce qui suit.

Dans le post #77 j’ai proposé quelques périodes et valeurs pour le changement de taux, voir l’instruction "INSERT INTO TAUX_DEFENSE_RECOURS".

Dans ce post #77 j’ai proposé le code SQL permettant de traiter du montant de la défense recours en relation avec le montant de chaque responsabilité civile, tout en tenant compte des changements de taux.

J’ai donc proposé un jeu d’essai dans ce post, notamment avec la vue DEFENSE_RECOURS permettant d’afficher pour chaque tarif (en vigueur ou historisé) le montant de la défense recours correspondante. Il y a une subtilité qui tient au fait que la date de changement de taux peut se produire en plein dans la période d’une responsabilité civile, laquelle peut donc être l’objet de deux montants différents de défense recours pour une seule valeur de responsabilité civile, comme je l’ai montré, ce qui m’a conduit à créer une vue des paires de taux consécutifs (post #77, vue TAUX_PAIRE). Au résultat :

catCode catComplCode puissanceCode   dateDebut    dateFin       respCivile   taux   defenseRecours
1       11           C1              2015-01-01   2015-01-31    180000       0.05   9000
1       11           C1              2015-02-01   2015-12-31    180000       0.07   12600
1       11           C1              2016-01-01   2017-01-31    185000       0.07   12950
1       11           C1              2017-02-01   2017-05-31    185000       0.06   11100
1       11           C1              2017-06-01   2017-10-31    185000       0.08   14800
1       11           C1              2017-11-01   2018-01-31    190000       0.08   15200
1       11           C1              2018-02-01   2018-10-03    190000       0.04   7600
1       11           C1              2018-10-04   2019-01-31    195000       0.04   7800
1       11           C1              2019-02-01   2019-08-03    195000       0.06   11700
1       11           C1              2019-08-04   2020-01-31    196000       0.06   11760
1       11           C1              2020-02-01   2020-09-30    196000       0.09   17640
1       11           C1              2020-10-01   2020-11-30    201657       0.09   18149
1       11           C1              2020-12-01   9999-12-31    201657       0.05   10082
...     ...          ...             ...          ...           ...          ...    ...

Dans ce résultat, on voit que pour la catégorie 1 et la puissance C1 (0 à 2 CV), le montant de la responsabilité civile fut égal à 180000 pendant la période allant du 1er janvier 2015 au 31 décembre 2015, mais qu’il y a eu un changement de taux de la défense recours le 1er février 2015, d’où changement du montant de celle-ci (passage de 9000 à 12600 francs).

De la même façon, on voit que pour la catégorie 1 et la puissance C1 (0 à 2 CV), le montant de la responsabilité civile en cours est égal à 201657 depuis le 1er octobre 2020, mais qu’il y a eu un changement de taux de la défense recours le 1er décembre 2020, d’où changement du montant de celle-ci (passage de 18149 à 10082 francs).

Je rappelle encore que la vue VEHICULE_TARIF_RECOURS permet de connaître le montant de la défense recours (en plus de celui de la responsabilité civile) pour chaque véhicule (voir post #77) :

vehCode  vehPuiss    ctrCode    ctrEffet    catCode complCode dateDebut  dateFin      respCivile  defenseRecours   taux

veh001   8           ctr001     2019-06-14  4       41        2019-02-01 2019-10-31   670000      40200            0.06
veh011   15          ctr021     2018-05-04  1       11        2018-02-01 2018-12-31   395000      15800            0.04
veh012   7           ctr021     2018-05-04  1       11        2018-02-01 2019-01-31   270000      10800            0.04
veh013   8           ctr022     2019-01-03  4       41        2018-02-01 2019-01-31   670000      26800            0.04
veh014   6           ctr023     2020-12-01  4       41        2020-12-01 9999-12-31   690102      34505            0.05
veh015   10          ctr021     2018-05-04  5       51        2018-02-01 2019-01-31   77000       3080             0.04
veh016   12          ctr021     2018-05-04  5       51        2018-02-01 2019-01-31   85000       3400             0.04

Je rappelle encore que la vue VEHICULE_TARIF_RECOURS_CEDEAO permet de connaître le montant de la défense recours et celui de la CEDEAO (en plus de celui de la responsabilité civile) pour chaque véhicule (voir post #79) :

vehCode  vehPuiss  ctrCode  ctrEffet     catCode  complCode  tarifDebut  tarifFin     respCivile defRecours  taux   cedeaoCode cedeaoDebut cedeaoFin   montant  ...

veh001   8         ctr001   2019-06-14   4        41         2019-02-01   2019-10-31  670000     40200       0.06   c1         2019-01-02  9999-12-31  100000   ...
veh011   15        ctr021   2018-05-04   1        11         2018-02-01   2018-12-31  395000     15800       0.04   c1         2018-01-01  2019-01-01  95000    ...
veh012   7         ctr021   2018-05-04   1        11         2018-02-01   2019-01-31  270000     10800       0.04   c1         2018-01-01  2019-01-01  95000    ...
veh013   8         ctr022   2019-01-03   4        41         2018-02-01   2019-01-31  670000     26800       0.04   c1         2019-01-02  9999-12-31  100000   ...
veh014   6         ctr023   2020-12-01   4        41         2020-12-01   9999-12-31  690102     34505       0.05   c1         2019-01-02  9999-12-31  100000   ...
veh015   10        ctr021   2018-05-04   5        51         2018-02-01   2019-01-31  77000      3080        0.04   c2         1900-01-01  2019-07-31  60000    ...
veh016   12        ctr021   2018-05-04   5        51         2018-02-01   2019-01-31  85000      3400        0.04   c2         1900-01-01  2019-07-31  60000    ...

Toujours dans le post #79, on montre les montants de la responsabilité civile, de la défense recours, de la CEDEAO des véhicules du client 'cli002'.

cliCode cliNom  prenom  vehCode  vehP ctrCode  dateEffet   catCode compl  tarifDebut  tarifFin    respCiv defRecours taux  cedeaoCode cedDebut    cedFin      cedMt  

cli002  Volfoni Raoul   veh011   15   ctr021   2018-05-04  1       11     2018-02-01  2018-12-31  395000  15800      0.04  c1         2018-01-01  2019-01-01  95000 
cli002  Volfoni Raoul   veh012   7    ctr021   2018-05-04  1       11     2018-02-01  2019-01-01  270000  10800      0.04  c1         2018-01-01  2019-01-01  95000
cli002  Volfoni Raoul   veh013   8    ctr022   2019-01-03  4       41     2018-02-01  2019-01-31  670000  26800      0.04  c1         2019-01-02  9999-12-31 100000
cli002  Volfoni Raoul   veh014   6    ctr023   2020-12-01  4       41     2020-12-01  9999-12-31  690102  34505      0.05  c1         2019-01-02  9999-12-31 100000
cli002  Volfoni Raoul   veh015   10   ctr021   2018-05-04  5       51     2018-02-01  2019-01-31  77000   3080       0.04  c2         1900-01-01  2019-07-31  60000
cli002  Volfoni Raoul   veh016   12   ctr021   2018-05-04  5       51     2018-02-01  2019-01-31  85000   3400       0.04  c2         1900-01-01  2019-07-31  60000

Voilà pour la défense recours. Certes il y a des subtilités, mais c’est la conséquence de la rencontre des historiques, celui des tarifs d’une part, celui du taux de la défense recours d’autre part.

Merci de me tenir au courant de votre propre réflexion.

**fsmrel** · 03/12/2020, 02h36

Rebonsoir,

On avance !

Envoyé par Zidane7

Voulez-vous que je revienne sur la sous-catégorie au lieu de categorie complement

A priori, peu importe le nom, mais si on doit faire référence aux derniers posts, le plus simple est d’en rester à 'CAT_COMPLEMENT'.

Concernant la maîtrise des triggers, je pense que vous pourriez vous exercez à le faire. Le plus important est de repérer les trous dans la raquette (c’est-à-dire les contradictions), par exemple celui que j’ai mentionné dans le post #81, quand la puissance du véhicule 'veh013' (8 CV) est hors des limites (3 CV, 6 CV).

Autre trou éventuel : pour un véhicule, la cohérence du nombre de places figurant sur la carte grise par rapport à la paire catégorie / complément pour les taxis.

J’essaierai de trouver un moment pour fournir un exemple de trigger contrôlant la puissance des véhicules par rapport aux bornes définies dans la table PUISSANCE.

Cas de la défense recours

Votre message a devancé le mien.

Vous noterez que nous divergeons au sujet des taux applicables aux défenses recours. A moins que ayez des objections justifiées, vous devriez normalement vous caler sur ma proposition : une entité-type TAUX_DEFENSE_RECOURS sans lien avec les autres.

Votre position ?

Cas des remorques

Dans votre MCD, l’entité-type REMORQUE est toujours présente.

Dans le post #81 j’ai posé la question suivante :

Envoyé par fsmrel

Question 3 : comment vous positionnez-vous au sujet des remorques quant à la redondance et à l’historique des CEDEAO ?

Je n’ai pas eu de réponse. Oubli ?

Coût de la police

Selon votre modélisation, chaque catégorie détermine un coût de la police.

Question : pour une catégorie donnée, ce coût reste-il-il forcément le même les années passant ? Dans la négative, il y a une datation à envisager...

Courage !

**Zidane7** · 06/12/2020, 20h53

Bonsoir Monsieur fsmrel,

Votre message a devancé le mien.
Vous noterez que nous divergeons au sujet des taux applicables aux défenses recours. A moins que ayez des objections justifiées, vous devriez normalement vous caler sur ma proposition : une entité-type TAUX_DEFENSE_RECOURS sans lien avec les autres.
Votre position ?

Ici puisse que la defense recours est fonction de la responsabilité civile(RC) et pour déterminer celle-ci il faut un taux qui est unique et le calcul se fait par ligne par rapport à chaque RC raison pour laquelle j'ai lié le taux à chaque RC. Que pensez-vous?

Cas des remorques
Dans votre MCD, l’entité-type REMORQUE est toujours présente.
Dans le post #81 j’ai posé la question suivante :
Envoyé par fsmrel
Question 3 : comment vous positionnez-vous au sujet des remorques quant à la redondance et à l’historique des CEDEAO ?
Je n’ai pas eu de réponse. Oubli ?

Voulez-vous qu'on laisse la remorque dans l’entité TARIF ou quoi?

Coût de la police
Selon votre modélisation, chaque catégorie détermine un coût de la police.
Question : pour une catégorie donnée, ce coût reste-il forcément le même les années passant ? Dans la négative, il y a une datation à envisager...

Faut -il mettre [CEDEAO]--1,N----(CATCOMP_CEDEAO)---- 1,1--[CATCOMPLEMENT] . C'est a dire lié la CEDEAO à CATCOMPLEMENT au lieu de lier avec la catégorie ? Puisse que CATCOMPLEMENT est plus explicite?
D'après votre message ci-dessous:

Dans ce MCD, vous noterez que l’entité-type TAUX_DÉFENSE_RECOURS est un historique, avec date de début et date de fin des valeurs successives du taux de la défense recours. Il est inutile d’établir une relation matérialisée avec quelque autre entité-type que ce soit, comme on peut le voir dans mes posts précédents et dans ce que je propose ci-dessous.

Pouvez-vous me donner deux taux de défense recours différent avec des dates différentes c'est-à dire une ancienne et une nouvelle dans la même table?
En,suite dans votre poste #83, vous n'avez pas lier la table PUISSANCE et la table VEHICULE. Que pensez-vous?

Coût de la police
Selon votre modélisation, chaque catégorie détermine un coût de la police.
Question : pour une catégorie donnée, ce coût reste-il-il forcément le même les années passant ? Dans la négative, il y a une datation à envisager...

Je trouve encore des conséquences sur la partie car le cout de police est fonction de chaque contrat, si c'est un mono le cout de police est 10 000 excepté les remorques, si c'est une flotte le cout de police peut être égal à 10 000 ou différent c'est à dire supérieur à 10 000. Peut-on le laisser dans la table PUISSANCE? Que pensez-vous?
Voici le MCD ci-dessous:
Merci par avance.

**fsmrel** · 08/12/2020, 17h46

Bonsoir Zidane7,

Envoyé par Zidane7

dans votre poste #83, vous n'avez pas lier la table PUISSANCE et la table VEHICULE.

A proprement parler, puisque vous ne faites pas mention des entités-types mais des tables, le lien est en l'occurrence effectif, je vous renvoie à ce sujet au post #81, où l’on voit que l’instruction CREATE TABLE VEHICULE comporte bien la clé étrangère VEHICULE_PUISSANCE_FK qui traduit l’association VEHICULE_PUIS que vous aviez établie dans votre post #80.

Il est vrai que le MCD que j’ai fourni dans le post #83 n’est pas le bon, car graphiquement parlant on y a perdu l’association VEHICULE_PUIS entre les entités-types VEHICULE et PUISSANCE que vous aviez établie dans votre post #80. Il se trouve que dans l’étape de dessin, l’entité-type TAUX_DEFENSE_RECOURS est venu « écraser » l’association VEHICULE_PUIS, mais ce n’est donc que le graphique qui est en cause.

Je répare donc le dessin. La vue correspondante du MCD pour la partie qui nous intéresse :

Nom : Zidane7_assurance_auto (12)retour des sous-catégories(looping).png
Affichages : 262
Taille : 45,2 Ko

La défense recours

Vous écrivez :

Envoyé par Zidane7

Ici puisse que la defense recours est fonction de la responsabilité civile(RC) et pour déterminer celle-ci il faut un taux qui est unique et le calcul se fait par ligne par rapport à chaque RC raison pour laquelle j'ai lié le taux à chaque RC. Que pensez-vous?

Vouloir associer directement le taux à chaque RC est évidemment naturel, mais selon votre MCD, cela ne servirait que pour le taux de chaque dernière responsabilité civile en vigueur. Vous auriez alors aussi à associer le taux pour chaque responsabilité civile historisée pour pouvoir déterminer la défense recours correspondante. Je n’ai pas tenté l’expérience, mais la programmation correspondante n’est certainement pas triviale.

Je reviens sur ce que j’ai proposé, à savoir une entité-type TAUX_DEFENSE_RECOURS sans aucun lien matérialisé :

Dans un 1er temps, soit Dx la date de référence (par exemple une date d’effet de contrat) pour laquelle on a besoin de connaître la responsabilité civile à appliquer. Cette RC est déterminée sans ambiguïté, car pour une catégorie, un complément et une puissance donnés, Dx se situe dans une période unique, comme on le voit dans la vue TARIF_TOUT (voir post #81), dont le contenu correspondant à mon jeu d’essai est le suivant :

SELECT catCode, catComplCode as Compl, puissanceCode as pCode 
    , dateDebut, dateFin, respCivile, cat 
FROM TARIF_TOUT
ORDER BY catCode, catComplCode, puissanceCode 
;

=>

catCode Compl pCode dateDebut  dateFin    respCivile  cat

1       11    C1    2015-01-01 2015-12-31 180000      Catégorie 1 : Affaires et promenades (AP) 
1       11    C1    2016-01-01 2017-10-31 185000      Catégorie 1 : Affaires et promenades (AP) 
1       11    C1    2017-11-01 2018-10-03 190000      Catégorie 1 : Affaires et promenades (AP) 
1       11    C1    2018-10-04 2019-08-03 195000      Catégorie 1 : Affaires et promenades (AP) 
1       11    C1    2019-08-04 2020-09-30 196000      Catégorie 1 : Affaires et promenades (AP) 
1       11    C1    2020-10-01 9999-12-31 201657      Catégorie 1 : Affaires et promenades (AP) 
1       11    C2    2015-01-01 2015-12-31 200000      Catégorie 1 : Affaires et promenades (AP) 
1       11    C2    2016-01-01 2017-12-31 210000      Catégorie 1 : Affaires et promenades (AP) 
1       11    C2    2018-01-01 2020-09-30 220000      Catégorie 1 : Affaires et promenades (AP) 
1       11    C2    2020-10-01 9999-12-31 244399      Catégorie 1 : Affaires et promenades (AP) 
1       11    C3    2015-01-01 2015-12-31 240000      Catégorie 1 : Affaires et promenades (AP) 
1       11    C3    2016-01-01 2017-12-31 260000      Catégorie 1 : Affaires et promenades (AP) 
1       11    C3    2018-01-01 2020-09-30 270000      Catégorie 1 : Affaires et promenades (AP) 
1       11    C3    2020-10-01 9999-12-31 285114      Catégorie 1 : Affaires et promenades (AP) 
1       11    C4    2015-01-01 2015-12-31 310000      Catégorie 1 : Affaires et promenades (AP) 
1       11    C4    2016-01-01 2017-12-31 320000      Catégorie 1 : Affaires et promenades (AP) 
1       11    C4    2018-01-01 2020-09-30 330000      Catégorie 1 : Affaires et promenades (AP) 
1       11    C4    2020-10-01 9999-12-31 346213      Catégorie 1 : Affaires et promenades (AP) 
1       11    C5    2015-01-01 2015-12-31 380000      Catégorie 1 : Affaires et promenades (AP) 
1       11    C5    2016-01-01 2017-12-31 390000      Catégorie 1 : Affaires et promenades (AP) 
1       11    C5    2018-01-01 2018-12-31 395000      Catégorie 1 : Affaires et promenades (AP) 
1       11    C5    2019-01-01 2020-09-30 400000      Catégorie 1 : Affaires et promenades (AP) 
1       11    C5    2020-10-01 9999-12-31 407332      Catégorie 1 : Affaires et promenades (AP) 
2       21    C2    1900-01-01 9999-12-31 401383      Catégorie 2 : Transport pour le compte de l'assuré (TPC) 
2       21    C3    1900-01-01 9999-12-31 563124      Catégorie 2 : Transport pour le compte de l'assuré (TPC) 
2       21    C4    1900-01-01 9999-12-31 583830      Catégorie 2 : Transport pour le compte de l'assuré (TPC) 
3       31    T1    1900-01-01 9999-12-31 578610      Catégorie 3 : Transport public de marchandises (TPM) Marchandises (tonnage)
3       31    T2    1900-01-01 9999-12-31 694332      Catégorie 3 : Transport public de marchandises (TPM) Marchandises (tonnage)
3       32    R1    1900-01-01 9999-12-31 1000000     Catégorie 3 : Transport public de marchandises (TPM) Remorque
4       41    C2    2015-01-01 2017-12-31 650000      Catégorie 4 : Taxis urbains et inter-urbains - Taxis 4 places
4       41    C2    2018-01-01 2019-10-31 670000      Catégorie 4 : Taxis urbains et inter-urbains - Taxis 4 places
4       41    C2    2019-11-01 2020-10-03 690000      Catégorie 4 : Taxis urbains et inter-urbains - Taxis 4 places   
4       41    C2    2020-10-04 9999-12-31 690102      Catégorie 4 : Taxis urbains et inter-urbains - Taxis 4 places
4       41    C3    1900-01-01 9999-12-31 728486      Catégorie 4 : Taxis urbains et inter-urbains - Taxis 4 places
4       41    C4    1900-01-01 9999-12-31 806813      Catégorie 4 : Taxis urbains et inter-urbains - Taxis 4 places
4       41    C5    1900-01-01 9999-12-31 846877      Catégorie 4 : Taxis urbains et inter-urbains - Taxis 4 places
4       42    C2    1900-01-01 9999-12-31 784239      Catégorie 4 : Taxis urbains et inter-urbains - Taxis 5 places
4       42    C3    1900-01-01 9999-12-31 822563      Catégorie 4 : Taxis urbains et inter-urbains - Taxis 5 places
4       42    C4    1900-01-01 9999-12-31 900947      Catégorie 4 : Taxis urbains et inter-urbains - Taxis 5 places
4       42    C5    1900-01-01 9999-12-31 941015      Catégorie 4 : Taxis urbains et inter-urbains - Taxis 5 places
4       48    C2    1900-01-01 9999-12-31 1097723     Catégorie 4 : Transport public de voyageurs - TPV 18 places
4       48    C3    1900-01-01 9999-12-31 1115468     Catégorie 4 : Transport public de voyageurs - TPV 18 places
5       51    C2    1900-01-01 2020-12-04 68000       Catégorie 5 : Motos 
5       51    C2    2020-12-05 9999-12-31 75000       Catégorie 5 : Motos 
5       51    C3    1900-01-01 2019-02-01 77000       Catégorie 5 : Motos 
5       51    C3    2019-02-02 9999-12-31 80000       Catégorie 5 : Motos 
5       51    C4    1900-01-01 9999-12-31 85000       Catégorie 5 : Motos

Par exemple, pour les taxis 4 places de puissance C2 (3 CV à 6 CV) si la date de référence Dx est le 15 février 2020, on voit que la responsabilité civile vaut 690000, car dans la fourchette unique <2019-11-01 : 2020-10-03>.

Dans un deuxième temps, concernant la défense recours, et pour répondre à votre question :

« Pouvez-vous me donner deux taux de défense recours différent avec des dates différentes c'est-à dire une ancienne et une nouvelle dans la même table? »

S’il ne s’agit que du taux, alors la réponse est dans le post #77, où la table en question est nommée TAUX_DEFENSE_RECOURS et dotée d’un jeu d’essai fournissant des valeurs du taux correspondant aux dates de changement de celui-ci :

Voyez pour cela l’instruction INSERT INTO TAUX_DEFENSE_RECOURS dans ce même post.

Pour savoir quelle valeur de défense recours correspond à telle responsabilité civile à telle date (et pour quelle valeur du taux), on utilise la vue DEFENSE_RECOURS (voir là encore le post #77) :

SELECT catCode, catComplCode as compl, puissanceCode as pCode
     , dateDebut, dateFin  
     , respCivile, taux, defenseRecours    
FROM DEFENSE_RECOURS
ORDER BY catCode, catComplCode, puissanceCode, dateDebut, dateFin
;

Incidemment, je fais observer que dans la théorie relationnelle une vue est une table virtuelle, que l’on peut consulter et mettre à jour tout comme une table de base. Sur ce plan, la norme SQL est conforme à la théorie relationnelle. Pour sa part, SQL Server est peccamineux et interdit la mise à jour des vues (jusqu’à quand ?)

En tout cas, le résultat de la requête est le suivant :

catCode compl pCode dateDebut  dateFin    respCivile  taux     defenseRecours

1       11    C1    2015-01-01 2015-01-31 180000      0.05     9000
1       11    C1    2015-02-01 2015-12-31 180000      0.07     12600
1       11    C1    2016-01-01 2017-01-31 185000      0.07     12950
1       11    C1    2017-02-01 2017-05-31 185000      0.06     11100
1       11    C1    2017-06-01 2017-10-31 185000      0.08     14800
1       11    C1    2017-11-01 2018-01-31 190000      0.08     15200
1       11    C1    2018-02-01 2018-10-03 190000      0.04     7600
1       11    C1    2018-10-04 2019-01-31 195000      0.04     7800
1       11    C1    2019-02-01 2019-08-03 195000      0.06     11700
1       11    C1    2019-08-04 2020-01-31 196000      0.06     11760
1       11    C1    2020-02-01 2020-09-30 196000      0.09     17640
1       11    C1    2020-10-01 2020-11-30 201657      0.09     18149
1       11    C1    2020-12-01 9999-12-31 201657      0.05     10082
1       11    C2    2015-01-01 2015-01-31 200000      0.05     10000
1       11    C2    2015-02-01 2015-12-31 200000      0.07     14000
1       11    C2    2016-01-01 2017-01-31 210000      0.07     14700
1       11    C2    2017-02-01 2017-05-31 210000      0.06     12600
1       11    C2    2017-06-01 2017-12-31 210000      0.08     16800
1       11    C2    2018-01-01 2018-01-31 220000      0.08     17600
1       11    C2    2018-02-01 2019-01-31 220000      0.04     8800
1       11    C2    2019-02-01 2020-01-31 220000      0.06     13200
1       11    C2    2020-02-01 2020-09-30 220000      0.09     19800
1       11    C2    2020-10-01 2020-11-30 244399      0.09     21995
1       11    C2    2020-12-01 9999-12-31 244399      0.05     12219
1       11    C3    2015-01-01 2015-01-31 240000      0.05     12000
1       11    C3    2015-02-01 2015-12-31 240000      0.07     16800
1       11    C3    2016-01-01 2017-01-31 260000      0.07     18200
1       11    C3    2017-02-01 2017-05-31 260000      0.06     15600
1       11    C3    2017-06-01 2017-12-31 260000      0.08     20800
1       11    C3    2018-01-01 2018-01-31 270000      0.08     21600
1       11    C3    2018-02-01 2019-01-31 270000      0.04     10800
1       11    C3    2019-02-01 2020-01-31 270000      0.06     16200
1       11    C3    2020-02-01 2020-09-30 270000      0.09     24300
1       11    C3    2020-10-01 2020-11-30 285114      0.09     25660
1       11    C3    2020-12-01 9999-12-31 285114      0.05     14255
1       11    C4    2015-01-01 2015-01-31 310000      0.05     15500
1       11    C4    2015-02-01 2015-12-31 310000      0.07     21700
1       11    C4    2016-01-01 2017-01-31 320000      0.07     22400
1       11    C4    2017-02-01 2017-05-31 320000      0.06     19200
1       11    C4    2017-06-01 2017-12-31 320000      0.08     25600
1       11    C4    2018-01-01 2018-01-31 330000      0.08     26400
1       11    C4    2018-02-01 2019-01-31 330000      0.04     13200
1       11    C4    2019-02-01 2020-01-31 330000      0.06     19800
1       11    C4    2020-02-01 2020-09-30 330000      0.09     29700
1       11    C4    2020-10-01 2020-11-30 346213      0.09     31159
1       11    C4    2020-12-01 9999-12-31 346213      0.05     17310
1       11    C5    2015-01-01 2015-01-31 380000      0.05     19000
1       11    C5    2015-02-01 2015-12-31 380000      0.07     26600
1       11    C5    2016-01-01 2017-01-31 390000      0.07     27300
1       11    C5    2017-02-01 2017-05-31 390000      0.06     23400
1       11    C5    2017-06-01 2017-12-31 390000      0.08     31200
1       11    C5    2018-01-01 2018-01-31 395000      0.08     31600
1       11    C5    2018-02-01 2018-12-31 395000      0.04     15800
1       11    C5    2019-01-01 2019-01-31 400000      0.04     16000
1       11    C5    2019-02-01 2020-01-31 400000      0.06     24000
1       11    C5    2020-02-01 2020-09-30 400000      0.09     36000
1       11    C5    2020-10-01 2020-11-30 407332      0.09     36659
1       11    C5    2020-12-01 9999-12-31 407332      0.05     20366
2       21    C2    2015-02-01 2017-01-31 401383      0.07     28096
2       21    C2    2017-02-01 2017-05-31 401383      0.06     24082
2       21    C2    2017-06-01 2018-01-31 401383      0.08     32110
2       21    C2    2018-02-01 2019-01-31 401383      0.04     16055
2       21    C2    2019-02-01 2020-01-31 401383      0.06     24082
2       21    C2    2020-02-01 2020-11-30 401383      0.09     36124
2       21    C2    2020-12-01 9999-12-31 401383      0.05     20069
2       21    C3    2015-02-01 2017-01-31 563124      0.07     39418
2       21    C3    2017-02-01 2017-05-31 563124      0.06     33787
2       21    C3    2017-06-01 2018-01-31 563124      0.08     45049
2       21    C3    2018-02-01 2019-01-31 563124      0.04     22524
2       21    C3    2019-02-01 2020-01-31 563124      0.06     33787
2       21    C3    2020-02-01 2020-11-30 563124      0.09     50681
2       21    C3    2020-12-01 9999-12-31 563124      0.05     28156
2       21    C4    2015-02-01 2017-01-31 583830      0.07     40868
2       21    C4    2017-02-01 2017-05-31 583830      0.06     35029
2       21    C4    2017-06-01 2018-01-31 583830      0.08     46706
2       21    C4    2018-02-01 2019-01-31 583830      0.04     23353
2       21    C4    2019-02-01 2020-01-31 583830      0.06     35029
2       21    C4    2020-02-01 2020-11-30 583830      0.09     52544
2       21    C4    2020-12-01 9999-12-31 583830      0.05     29191
3       31    T1    2015-02-01 2017-01-31 578610      0.07     40502
3       31    T1    2017-02-01 2017-05-31 578610      0.06     34716
3       31    T1    2017-06-01 2018-01-31 578610      0.08     46288
3       31    T1    2018-02-01 2019-01-31 578610      0.04     23144
3       31    T1    2019-02-01 2020-01-31 578610      0.06     34716
3       31    T1    2020-02-01 2020-11-30 578610      0.09     52074
3       31    T1    2020-12-01 9999-12-31 578610      0.05     28930
3       31    T2    2015-02-01 2017-01-31 694332      0.07     48603
3       31    T2    2017-02-01 2017-05-31 694332      0.06     41659
3       31    T2    2017-06-01 2018-01-31 694332      0.08     55546
3       31    T2    2018-02-01 2019-01-31 694332      0.04     27773
3       31    T2    2019-02-01 2020-01-31 694332      0.06     41659
3       31    T2    2020-02-01 2020-11-30 694332      0.09     62489
3       31    T2    2020-12-01 9999-12-31 694332      0.05     34716
3       32    R1    2015-02-01 2017-01-31 1000000     0.07     70000
3       32    R1    2017-02-01 2017-05-31 1000000     0.06     60000
3       32    R1    2017-06-01 2018-01-31 1000000     0.08     80000
3       32    R1    2018-02-01 2019-01-31 1000000     0.04     40000
3       32    R1    2019-02-01 2020-01-31 1000000     0.06     60000
3       32    R1    2020-02-01 2020-11-30 1000000     0.09     90000
3       32    R1    2020-12-01 9999-12-31 1000000     0.05     50000
4       41    C2    2015-01-01 2015-01-31 650000      0.05     32500
4       41    C2    2015-02-01 2017-01-31 650000      0.07     45500
4       41    C2    2017-02-01 2017-05-31 650000      0.06     39000
4       41    C2    2017-06-01 2017-12-31 650000      0.08     52000
4       41    C2    2018-01-01 2018-01-31 670000      0.08     53600
4       41    C2    2018-02-01 2019-01-31 670000      0.04     26800
4       41    C2    2019-02-01 2019-10-31 670000      0.06     40200
4       41    C2    2019-11-01 2020-01-31 690000      0.06     41400
4       41    C2    2020-02-01 2020-10-03 690000      0.09     62100 
4       41    C2    2020-10-04 2020-11-30 690102      0.09     62109
4       41    C2    2020-12-01 9999-12-31 690102      0.05     34505
4       41    C3    2015-02-01 2017-01-31 728486      0.07     50994
4       41    C3    2017-02-01 2017-05-31 728486      0.06     43709
4       41    C3    2017-06-01 2018-01-31 728486      0.08     58278
4       41    C3    2018-02-01 2019-01-31 728486      0.04     29139
4       41    C3    2019-02-01 2020-01-31 728486      0.06     43709
4       41    C3    2020-02-01 2020-11-30 728486      0.09     65563
4       41    C3    2020-12-01 9999-12-31 728486      0.05     36424
4       41    C4    2015-02-01 2017-01-31 806813      0.07     56476
4       41    C4    2017-02-01 2017-05-31 806813      0.06     48408
4       41    C4    2017-06-01 2018-01-31 806813      0.08     64545
4       41    C4    2018-02-01 2019-01-31 806813      0.04     32272
4       41    C4    2019-02-01 2020-01-31 806813      0.06     48408
4       41    C4    2020-02-01 2020-11-30 806813      0.09     72613
4       41    C4    2020-12-01 9999-12-31 806813      0.05     40340
4       41    C5    2015-02-01 2017-01-31 846877      0.07     59281
4       41    C5    2017-02-01 2017-05-31 846877      0.06     50812
4       41    C5    2017-06-01 2018-01-31 846877      0.08     67750
4       41    C5    2018-02-01 2019-01-31 846877      0.04     33875
4       41    C5    2019-02-01 2020-01-31 846877      0.06     50812
4       41    C5    2020-02-01 2020-11-30 846877      0.09     76218
4       41    C5    2020-12-01 9999-12-31 846877      0.05     42343
4       42    C2    2015-02-01 2017-01-31 784239      0.07     54896
4       42    C2    2017-02-01 2017-05-31 784239      0.06     47054
4       42    C2    2017-06-01 2018-01-31 784239      0.08     62739
4       42    C2    2018-02-01 2019-01-31 784239      0.04     31369
4       42    C2    2019-02-01 2020-01-31 784239      0.06     47054
4       42    C2    2020-02-01 2020-11-30 784239      0.09     70581
4       42    C2    2020-12-01 9999-12-31 784239      0.05     39211
4       42    C3    2015-02-01 2017-01-31 822563      0.07     57579
4       42    C3    2017-02-01 2017-05-31 822563      0.06     49353
4       42    C3    2017-06-01 2018-01-31 822563      0.08     65805
4       42    C3    2018-02-01 2019-01-31 822563      0.04     32902
4       42    C3    2019-02-01 2020-01-31 822563      0.06     49353
4       42    C3    2020-02-01 2020-11-30 822563      0.09     74030
4       42    C3    2020-12-01 9999-12-31 822563      0.05     41128
4       42    C4    2015-02-01 2017-01-31 900947      0.07     63066
4       42    C4    2017-02-01 2017-05-31 900947      0.06     54056
4       42    C4    2017-06-01 2018-01-31 900947      0.08     72075
4       42    C4    2018-02-01 2019-01-31 900947      0.04     36037
4       42    C4    2019-02-01 2020-01-31 900947      0.06     54056
4       42    C4    2020-02-01 2020-11-30 900947      0.09     81085
4       42    C4    2020-12-01 9999-12-31 900947      0.05     45047
4       42    C5    2015-02-01 2017-01-31 941015      0.07     65871
4       42    C5    2017-02-01 2017-05-31 941015      0.06     56460
4       42    C5    2017-06-01 2018-01-31 941015      0.08     75281
4       42    C5    2018-02-01 2019-01-31 941015      0.04     37640
4       42    C5    2019-02-01 2020-01-31 941015      0.06     56460
4       42    C5    2020-02-01 2020-11-30 941015      0.09     84691
4       42    C5    2020-12-01 9999-12-31 941015      0.05     47050
4       48    C2    2015-02-01 2017-01-31 1097723     0.07     76840
4       48    C2    2017-02-01 2017-05-31 1097723     0.06     65863
4       48    C2    2017-06-01 2018-01-31 1097723     0.08     87817
4       48    C2    2018-02-01 2019-01-31 1097723     0.04     43908
4       48    C2    2019-02-01 2020-01-31 1097723     0.06     65863
4       48    C2    2020-02-01 2020-11-30 1097723     0.09     98795
4       48    C2    2020-12-01 9999-12-31 1097723     0.05     54886
4       48    C3    2015-02-01 2017-01-31 1115468     0.07     78082
4       48    C3    2017-02-01 2017-05-31 1115468     0.06     66928
4       48    C3    2017-06-01 2018-01-31 1115468     0.08     89237
4       48    C3    2018-02-01 2019-01-31 1115468     0.04     44618
4       48    C3    2019-02-01 2020-01-31 1115468     0.06     66928
4       48    C3    2020-02-01 2020-11-30 1115468     0.09     100392
4       48    C3    2020-12-01 9999-12-31 1115468     0.05     55773
5       51    C2    2015-02-01 2017-01-31 68000       0.07     4760
5       51    C2    2017-02-01 2017-05-31 68000       0.06     4080
5       51    C2    2017-06-01 2018-01-31 68000       0.08     5440
5       51    C2    2018-02-01 2019-01-31 68000       0.04     2720
5       51    C2    2019-02-01 2020-01-31 68000       0.06     4080
5       51    C2    2020-02-01 2020-11-30 68000       0.09     6120
5       51    C2    2020-12-01 2020-12-04 68000       0.05     3400
5       51    C3    2015-02-01 2017-01-31 77000       0.07     5390
5       51    C3    2017-02-01 2017-05-31 77000       0.06     4620
5       51    C3    2017-06-01 2018-01-31 77000       0.08     6160
5       51    C3    2018-02-01 2019-01-31 77000       0.04     3080
5       51    C3    2019-02-01 2019-02-01 77000       0.06     4620
5       51    C3    2019-02-02 2020-01-31 80000       0.06     4800
5       51    C3    2020-02-01 2020-11-30 80000       0.09     7200
5       51    C3    2020-12-01 9999-12-31 80000       0.05     4000
5       51    C4    2015-02-01 2017-01-31 85000       0.07     5950
5       51    C4    2017-02-01 2017-05-31 85000       0.06     5100
5       51    C4    2017-06-01 2018-01-31 85000       0.08     6800
5       51    C4    2018-02-01 2019-01-31 85000       0.04     3400
5       51    C4    2019-02-01 2020-01-31 85000       0.06     5100
5       51    C4    2020-02-01 2020-11-30 85000       0.09     7650
5       51    C4    2020-12-01 9999-12-31 85000       0.05     4250

Pour reprendre l’exemple des taxis 4 places de puissance C2 (3 CV à 6 CV) et pour la date de référence Dx (15 février 2020), on voit que la responsabilité civile est évidemment toujours égale à 690000, tandis que la défense recours est égale à 62100 (taux 0.09).

A cette occasion, je rappelle ce que j’avais écrit dans le post #84 :

Envoyé par fsmrel

Vous noterez que nous divergeons au sujet des taux applicables aux défenses recours. A moins que ayez des objections justifiées, vous devriez normalement vous caler sur ma proposition : une entité-type TAUX_DEFENSE_RECOURS sans lien avec les autres.

Revoyez aussi ce que j’ai écrit dans les posts #76 et #77.

En tout cas, je vous recommande à nouveau de vous caler sur ma proposition, sinon vous vous compliquerez singulièrement la vie pour créer les liens entre chaque RC (active ou historisée) et chaque taux (actif ou historisé).

Pour les autres points de votre message, je vais regarder de plus près (cas par exemple des remorques).

**fsmrel** · 09/12/2020, 02h56

Bonsoir Zidane7,

Les remorques

Envoyé par Zidane7

Voulez-vous qu'on laisse la remorque dans l’entité TARIF ou quoi?

Je dis seulement que c’est une possibilité et mes jeux d’essai montrent que ça marche. En revanche ma solution est un peu laborieuse : nécessité de créer une ligne dans la table PUISSANCE, avec des bornes valant 0, nécessité de créer une ligne dans la table CAT_COMPLEMENT. Sur ce plan, votre approche est plus satisfaisante puisque les tables PUISSANCE et CAT_COMPLEMENT ne sont pas concernées, donc pas touchées.

Je rappelle que dans le post #81 j’ai posé la question 3 :

« Comment vous positionnez-vous au sujet des remorques quant à la redondance et à l’historique des CEDEAO ? »

Vous avez donc modifié votre MCD du post #82 en ajoutant une association entre les entités-types REMORQUE et CEDEAO (voir post #85).

1re remarque :

Pour éviter de trop charger, je fais pour le moment abstraction de la CEDEAO. Dans vos deux MCD vous avez identifié REMORQUE relativement à CATEGORIE, ce qui dans l’intention veut dire que chaque remorque appartient à une et une seule catégorie, à savoir la catégorie 3 « Transports publics de marchandises » par référence à votre tableau éclairant du post #54, mais aussi qu’une 1re remorque peut appartenir à la catégorie 3 (ou à toute autre !) et d’autres remorques à d’autres catégories.

Par ailleurs, la patte connectant l’entité-type CATEGORIE et l’association CAT_REMORQUE est porteuse d’une cardinalité 0,1 ce qui signifie que la catégorie évoquée (Transports publics de marchandises), fait référence à au plus une occurrence de l’entité-type REMORQUE : en conjugaison avec l’identification relative, cela veut dire que l’attribut remorqueId de REMORQUE est inutile et doit disparaître, c’est le singleton {catId} qui prend le relais pour être clé primaire de la table REMORQUE, d’où le code SQL :

CREATE TABLE REMORQUE
(
   catId INT,
   montantRemorque INT NOT NULL,
   CONSTRAINT REMORQUE_PK PRIMARY KEY(catId),
   CONSTRAINT REMORQUE_CATEGORIE_FK FOREIGN KEY(catId) 
       REFERENCES CATEGORIE(catId)
);

En relation avec la 1re remarque, on peut très bien programmer

INSERT INTO REMORQUE (catId, montantRemorque)
VALUES
     ((SELECT catId FROM CATEGORIE WHERE catCode = '3'), 1000000)
   , ((SELECT catId FROM CATEGORIE WHERE catCode = '5'), 1000000)  
;

SELECT catLibelle, montantRemorque 
FROM   REMORQUE as x 
  JOIN CATEGORIE AS y ON x.catId = y.catId
;

=>

catLibelle                                             montantRemorque
Catégorie 3 : Transport public de marchandises (TPM)   1000000
Catégorie 5 : Motos                                    1000000

Pour interdire qu’il y ait plus d’une catégorie à laquelle on puisse accrocher (sic) une remorque, il faudra créer une fonction ad-hoc, d’où le code complété :

CREATE TABLE REMORQUE
(
   catId INT,
   montantRemorque INT NOT NULL,
   CONSTRAINT REMORQUE_PK PRIMARY KEY(catId),
   CONSTRAINT REMORQUE_CATEGORIE_FK FOREIGN KEY(catId) 
       REFERENCES CATEGORIE(catId)
); 

GO  
CREATE FUNCTION REMORQUE_COUNT_FN()      
RETURNS INT  
AS   
BEGIN  
   DECLARE @n INT  
   SELECT @n = COUNT(*) FROM REMORQUE  
   RETURN @n  
END ;  
GO  

ALTER TABLE REMORQUE  
ADD CONSTRAINT REMORQUE_COUNT CHECK (dbo.REMORQUE_COUNT_FN() < 2) ;  
GO  

INSERT INTO REMORQUE (catId, montantRemorque)
VALUES
     ((SELECT catId FROM CATEGORIE WHERE catCode = '3'), 1000000)
   , ((SELECT catId FROM CATEGORIE WHERE catCode = '5'), 1000000)  
;

Comme on vient de le faire, en cas de violation de la contrainte :

Envoyé par SQL Server

Msg 547, Niveau 16, État 0
L'instruction INSERT est en conflit avec la contrainte CHECK "REMORQUE_COUNT". Le conflit s'est produit dans la base de données "test", table "dbo.REMORQUE".
L'instruction a été arrêtée.

2e remarque :

Prenons en compte la CEDEAO. Il y a maintenant une boucle dans le MCD, à savoir deux chemins pour associer une occurrence REMORQUE et une occurrence CEDEAO :

(a) Le chemin REMORQUE > CATEGORIE > CEDEAO (via les associations CAT_REMORQUE et CAT_CEDEAO) ;

(b) Le chemin REMORQUE > CEDEAO (via l’association CEDEAO_REMORQUE).

Ainsi, dans mes jeux d’essai, la catégorie 3 fait référence à la CEDEAO de code 'c1', mais rien n’empêche que, via l’association CEDEAO_REMORQUE, la remorque associée à cette catégorie fasse référence à une autre CEDEAO, comme dans cet exemple :

INSERT INTO REMORQUE (catId, cedeaoId, montantRemorque)
VALUES
     ((SELECT catId FROM CATEGORIE WHERE catCode = '3')
    , (SELECT cedeaoId FROM CEDEAO WHERE cedeaoCode = 'c2') 
    , 10000)
;

SELECT catLibelle
     , t.cedeaoCode as 'CEDEAO(GARANTIE)', z.cedeaoCode as 'CEDEAO(REMORQUE)'
     , montantRemorque 
FROM   REMORQUE as x 
  JOIN CATEGORIE AS y ON x.catId = y.catId
  JOIN CEDEAO AS z ON x.cedeaoId = z.cedeaoId
  JOIN CEDEAO AS t ON y.cedeaoId = t.cedeaoId
;

=>

catLibelle                                            CEDEAO(GARANTIE) CEDEAO(REMORQUE) montantRemorque
Catégorie 3 : Transport public de marchandises (TPM)  c1               c2               1000000

Moralité : si la CEDEAO de la remorque doit nécessairement être celle de sa catégorie, alors l’association CEDEAO_REMORQUE doit disparaître. Pour connaître la CEDEAO de la remorque :

SELECT catLibelle
     , z.cedeaoCode
     , montantRemorque 
FROM   REMORQUE as x 
  JOIN CATEGORIE AS y ON x.catId = y.catId
  JOIN CEDEAO AS z ON y.cedeaoId = z.cedeaoId
;

=>

catLibelle                                            cedeaoCode  montantRemorque
Catégorie 3 : Transport public de marchandises (TPM)  c1          1000000

3e remarque :

Tout ce qui précède est intemporel, avec une valeur immuable du montant de la responsabilité civile de la remorque et celui de sa CEDEAO... Si depuis la nuit des temps jusqu’à leur consommation, le montant de la RC reste à 1000000, d’accord. Il faudra néanmoins tenir compte de l’historique de la CEDEAO, comme on l’a fait pour les tarifs, au moyen de vues ad-hoc. Si dans le temps le montant de la RC REMORQUE évolue, alors il faudra dater et aussi prévoir une table pour l’historique des montants, à l’image de ce qui a été fait pour les tarifs.

Après les remarques que j’ai faites, je suis favorable à votre approche de la modélisation des remorques, mais avec les aménagements et compléments que j'ai évoqués.

J’en resterai là pour ce soir, j’ai sommeil...

**fsmrel** · 09/12/2020, 04h35

Je me réveille !

Contrôle de la puissance des véhicules

J’ai écrit ceci dans le post #81 :

Envoyé par fsmrel

A noter que, par rapport à la table PUISSANCE, la puissance du véhicule 'veh013' (8 CV) est hors des limites (3 CV, 6 CV). Un trigger de contrôle s’impose...

En fait, au lieu d’un trigger on peut utiliser une fonction.
A la suite de l’instruction CREATE TABLE VEHICULE, on code :

DROP FUNCTION IF EXISTS VEHICULE_PUISSANCE_FN ;
GO  
CREATE FUNCTION VEHICULE_PUISSANCE_FN()  
RETURNS INT  
AS   
BEGIN  
   DECLARE @n INT  
   SELECT @n = COUNT(*) FROM VEHICULE as x
    JOIN   PUISSANCE as y ON x.puissanceId = y.puissanceId
    WHERE  x.vehiculePuissance > y.borneSup
        OR x.vehiculePuissance < y.borneInf
   ;
   RETURN @n  
END ;  
GO  
  
ALTER TABLE VEHICULE  
ADD CONSTRAINT VEHICULE_PUISSANCE_CHECK CHECK (dbo.VEHICULE_PUISSANCE_FN() = 0) ;  
GO

Et voilà. C’est un peu fruste (on se contente de compter) mais ça marche, 'veh013' sera rejeté !

**fsmrel** · 09/12/2020, 19h35

Bonsoir Zidane7,

Dans le post #85, vous écrivez :

Envoyé par Zidane7

Faut -il mettre [CEDEAO]--1,N--(CATCOMP_CEDEAO)--1,1--[CATCOMPLEMENT]. C'est a dire lié la CEDEAO à CATCOMPLEMENT au lieu de lier avec la catégorie ? Puisse que CATCOMPLEMENT est plus explicite?

Autrement dit, la maille actuelle (la catégorie) ne serait pas assez fine, c’est-à-dire que pour tel et tel compléments d’une catégorie donnée, la CEDEAO serait différente. Si c’est le cas, l’entité-type CEDEAO est effectivement à associer à l’entité-type CAT_COMPLEMENT.

Mais ceci est quand même contradictoire avec le post #59, où vous écrivez au sujet de la CEDEAO :

« de la catégorie 1 jusqu'à la catégorie 4 et ses sous-catégories elle est égale à 100000 et de la catégorie 5 et sous-catégories (MOTO), elle est égale à 70000 »

Sur la base de ce dernier énoncé, je ne vois pas de raison valable pour associer l’entité-type CEDEAO à l’entité-type CAT_COMPLEMENT.

Qu'entendez-vous par « être plus explicite » ? Du point de vue fonctionnel, qu’en est-il exactement ?

**fsmrel** · 10/12/2020, 17h45

Bonjour Zidane7

Remarque concernant l’association des entités-types CAT_COMPLEMENT et CEDEAO

Je rappelle que dans votre post #85, vous évoquez la suppression de l’association CAT_CEDEAO entre les entités-types CATEGORIE et CEDEAO, et son remplacement par une association « plus explicite » entre les entités-types CAT_COMPLEMENT et CEDEAO.

Cela a des conséquences notamment en ce qui concerne les remorques.

En effet, si l’association CAT_CEDEAO entre les entités-types CATEGORIE et CEDEAO est supprimée et remplacée par une association entre les entités-types CAT_COMPLEMENT et CEDEAO, l’association CAT_CEDEAO disparaissant, il en va de même de la redondance (deux chemins pour naviguer de REMORQUE vers CEDEAO) que je dénonçai dans ma 2e remarque du post #87. Dans ces conditions, la suppression de l’association CAT_CEDEAO entraîne évidemment la conservation de l’association CEDEAO_REMORQUE (mais sans identification relative !), sinon on n’aurait plus aucun moyen de savoir quel montant de CEDEAO appliquer aux remorques.

Mais attention, comme je l’ai fait observer dans ma 3e remarque du post #87, ce qui vaut pour les tarifs vaut pour les remorques, c’est-à-dire la nécessité de la mise en oeuvre d’une entité-type REMORQUE_HISTO et de vues permettant de suivre les valeurs de CEDEAO prises par les remorques, par exemple une vue REMORQUE_MONTANT_TOUT à l’image de la vue TARIF_TOUT, une vue REMORQUE_MONTANT_CEDEAO à l’image de la vue VEHICULE_TARIF_CEDEAO.

**Zidane7** · 10/12/2020, 18h33

Bonsoir Monsieur fsmrel,
Merci pour vos réponses dans les postes précédents.

Qu'entendez-vous par « être plus explicite » ? Du point de vue fonctionnel, qu’en est-il exactement ?

Pour éviter les surcharges, j'ai pensé mettre la remorque dans l'entité CATCOMPLEMENT. C'est pour cela que je disais de lier la CEDEAO à la CATCOMPLEMENT qui nous permet de déterminer le tarif de chaque CATCOMPLEMENT à travers le TARIF.

Mais ceci est quand même contradictoire avec le post #59, où vous écrivez au sujet de la CEDEAO :
« de la catégorie 1 jusqu'à la catégorie 4 et ses sous-catégories elle est égale à 100000 et de la catégorie 5 et sous-catégories (MOTO), elle est égale à 70000 »

Oui vous avez raison mais à chaque fois qu'on cherche la valeur de la CEDEA et du TARIF d'un engin, en passant par le véhicule ou contrat, on passe directement par la CATCOMPLEMENT sans passer d'abord par la catégorie raison pour laquelle je disais de lier la CATCOMPLEMENT à la CEDEAO.
Que pensez-vous?
Merci par avance.

**Zidane7** · 11/12/2020, 18h17

Bonsoir Monsieur fsmrel,
J'avais déjà écrit avant de voir votre réponse #90 mais je vous demande de faire une analyse de mon poste #91 et me faire un retour.
Merci d'avance.

**fsmrel** · 11/12/2020, 21h32

Bonsoir Zidane7,

Envoyé par Zidane7

Pour éviter les surcharges, j'ai pensé mettre la remorque dans l'entité CATCOMPLEMENT. C'est pour cela que je disais de lier la CEDEAO à la CATCOMPLEMENT qui nous permet de déterminer le tarif de chaque CATCOMPLEMENT à travers le TARIF.

Donc on en revient à ce que j’ai d’abord fourni dans les posts #55 et #76.

(a) On réintroduit la « puissance » des remorques (post #55) :

INSERT INTO PUISSANCE (puissanceCode, borneInf, borneSup, unite)
VALUES
    ('C1', 0, 2, 'CV')
  , ('C2', 3, 6, 'CV') 
  , ('C3', 7, 10, 'CV') 
  , ('C4', 11, 14, 'CV') 
  , ('C5', 15, 23, 'CV') 
  , ('C6', 24, 9999, 'CV') 
  , ('T1', 0, 1, 'tonne') 
  , ('T2', 2, 5, 'tonne') 
  , ('T3', 6, 10, 'tonne') 
  , ('T4', 11, 15, 'tonne') 
  , ('T5', 16, 25, 'tonne') 
  , ('T6', 26, 9999, 'tonne') 
  , ('R1', 0, 0, 'remorque')   
;

(b) On réintroduit le complément de catégorie 'Remorque' dans la table CAT_COMPLEMENT :

INSERT INTO CAT_COMPLEMENT (catComplementCode, catComplementLibelle)
VALUES
    (0, '')  /* chaîne vide pour les catégories sans compléments */
  , (31, 'Marchandises (tonnage)')
  , (32, 'Remorque')   
  , (41, 'Taxis urbains et inter-urbains - Taxis 4 places')
  , (42, 'Taxis urbains et inter-urbains - Taxis 5 places')
  , (43, 'Taxis urbains et inter-urbains - Taxis 6 places')
  , (48, 'Transport public de voyageurs - TPV 18 places')
;
SELECT catComplementCode, catComplementLibelle FROM CAT_COMPLEMENT ;

=>

catComplementCode   catComplementLibelle

0                 
31                  Marchandises (tonnage)
32                  Remorque  
41                  Taxis urbains et inter-urbains - Taxis 4 places
42                  Taxis urbains et inter-urbains - Taxis 5 places
43                  Taxis urbains et inter-urbains - Taxis 6 places
48                  Transport public de voyageurs - TPV 18 places

(c) Puis en prenant en compte maintenant la CEDEAO (voir le code SQL relatif à la CEDEAO dans le post #79), la structure de la table CAT_COMPLEMENT devient :

CREATE TABLE CAT_COMPLEMENT
(
   catId        INT,
   catComplementId INT IDENTITY,
   catComplementCode TINYINT NOT NULL,
   catComplementLibelle VARCHAR(96) NOT NULL,
   cedeaoId INT NOT NULL,  
   CONSTRAINT CAT_COMPLEMENT_PK PRIMARY KEY (catId, catComplementId),
   CONSTRAINT CAT_COMPLEMENT_AK UNIQUE (catComplementCode),
   CONSTRAINT CAT_COMPLEMENT_CATEGORIE_FK FOREIGN KEY (catId)
       REFERENCES CATEGORIE (catId)
 , CONSTRAINT CATEGORIE_CEDEAO_FK FOREIGN KEY (cedeaoId) 
       REFERENCES CEDEAO (cedeaoId)
);

INSERT INTO CAT_COMPLEMENT (catId, cedeaoId, catComplementCode, catComplementLibelle)
VALUES
    ((SELECT catId FROM CATEGORIE WHERE catCode = '1')
     , (select cedeaoId from CEDEAO where cedeaoCode = 'c1')
     , 11, '')  
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '2')
      , (select cedeaoId from CEDEAO where cedeaoCode = 'c1')
    , 21, '')  
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '3')
      , (select cedeaoId from CEDEAO where cedeaoCode = 'c1')
     , 31, 'Marchandises (tonnage)')  
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '3')
      , (select cedeaoId from CEDEAO where cedeaoCode = 'c1')
     , 32, 'Remorque')    
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '4')
      , (select cedeaoId from CEDEAO where cedeaoCode = 'c1')
     , 41, 'Taxis urbains et inter-urbains - Taxis 4 places')  
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '4')
      , (select cedeaoId from CEDEAO where cedeaoCode = 'c1')
     , 42, 'Taxis urbains et inter-urbains - Taxis 5 places')  
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '4')
      , (select cedeaoId from CEDEAO where cedeaoCode = 'c1')
     , 43, 'Taxis urbains et inter-urbains - Taxis 6 places')  
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '4')
      , (select cedeaoId from CEDEAO where cedeaoCode = 'c1')
     , 48, 'Transport public de voyageurs - TPV 18 places')  
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '5')
      , (select cedeaoId from CEDEAO where cedeaoCode = 'c2')
     , 51, '')  
;
SELECT catCode
     , catComplementCode
     , cedeaoCode
     , catLibelle + catComplementLibelle as libellé    
FROM   CATEGORIE as x 
  JOIN CAT_COMPLEMENT as y ON x.catId = y.catId 
  JOIN CEDEAO as z ON y.cedeaoId = z.cedeaoId
;

=>

catCode  catComplementCode  cedeaoCode  libellé
1        11                 c1          Catégorie 1 : Affaires et promenades (AP)
2        21                 c1          Catégorie 2 : Transport pour le compte de l'assuré (TPC)
3        31                 c1          Catégorie 3 : Transport public de marchandises (TPM)Marchandises (tonnage)
3        32                 c1          Catégorie 3 : Transport public de marchandises (TPM)Remorque   
4        41                 c1          Catégorie 4 :Taxis urbains et inter-urbains - Taxis 4 places
4        42                 c1          Catégorie 4 :Taxis urbains et inter-urbains - Taxis 5 places
4        43                 c1          Catégorie 4 :Taxis urbains et inter-urbains - Taxis 6 places
4        48                 c1          Catégorie 4 :Transport public de voyageurs - TPV 18 places
5        51                 c2          Catégorie 5 : Motos

(d) Ainsi l’entité-type REMORQUE n’a plus de raison d’être et disparaît purement et simplement. Etes-vous bien d’accord ?

Si oui, les montants des remorques réintègrent l’entité-type TARIF. On en revient alors à nouveau au post #76 :

-- Cas de la catégorie 3

INSERT INTO TARIF (puissanceId, catId, catComplementId, tarifDateDepuis, respCivile)
VALUES
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'T1')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '3')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 31)
    , '2020-10-01', 578610)
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'T2')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '3')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 31)
    , '2020-10-01', 694332)
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'R1')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '3')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 32)
    , '2020-10-01', 1000000)    
;

(e) Si on historise aussi pour les remorques :

INSERT INTO TARIF_HISTO (catId, puissanceId, catComplementId, tarifDebutHisto, tarifFinHisto, responsabiliteCivileHisto)
VALUES
    ((SELECT catId FROM CATEGORIE WHERE catCode = '3')
   , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'R1')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 32)
-------------       , '2020-10-01', 1000000) 
      , '1900-01-01', '2020-09-30', 900000) 
;

SELECT catCode, catComplCode as Compl, puissanceCode as pCode 
    , dateDebut, dateFin, respCivile, cat 
FROM TARIF_TOUT 
WHERE catCode = '3' AND catComplCode = '32'
;

=>

catCode Compl pCode dateDebut  dateFin    respCivile  cat

3       32    R1    1900-01-01 2020-09-30 900000      Catégorie 3 : Transport public de marchandises (TPM) Remorque
3       32    R1    2020-10-01 9999-12-31 1000000     Catégorie 3 : Transport public de marchandises (TPM) Remorque

(f) Je suppose que les remorques n’ont pas à être présentes dans la table VEHICULE. Mais alors qu’en est-il des contrats ?

(g) Applique-t-on de la défense recours aux remorques ?

Que devient votre MCD ?

En passant, vous ne votez plus ?

**Zidane7** · 21/12/2020, 14h45

Bonjour Monsieur fsmrel,
Je vous envoie la condition particulière c'est à dire la fiche finale de la production. C'est sur cela que ça doit aboutir.
Merci d'avance.

**Zidane7** · 03/01/2021, 13h03

Bonjour Monsieur fsmrel,
J'ai fait des analyses par rapport aux garanties et les différents taux, voici le chema du MCD ci-dessous:
Comme je vous disais dans l'un de mes postes que:

Le coût de police est lié au type de contrat:
- Si c'est un mono alors le coût de police est égale à 10 000.
-Sinon si c'est une flotte alors le coût de police varie ainsi on laisse la main à l'utilisateur de saisie le coût de police. Ensuite mettre un plafond à ce niveau nommé par exemple coutpolicemax.
Sinon dans le cas des remorques, pas de coût de police.

Pour le moment je l'ai placé dans Type de contrat. Ensuite le cout de police est unique pour un contrat donné qu'il soit mono ou flotte.
Que pensez-vous par rapport à ma proposition?
Le code sql correspondant est le suivant:

CREATE TABLE CATEGORIE(
categorieId INT IDENTITY,
codecategorie CHAR(3) NOT NULL,
categorie VARCHAR(50) NOT NULL,
PRIMARY KEY(categorieId),
UNIQUE(codecategorie)
);

CREATE TABLE CLIENT(
clientId INT IDENTITY,
codeclient VARCHAR(10) NOT NULL,
nomClient VARCHAR(50) NOT NULL,
prenomClient VARCHAR(50) NOT NULL,
adresseClient VARCHAR(50) NOT NULL,
telephoneClient VARCHAR(30) NOT NULL,
PRIMARY KEY(clientId),
UNIQUE(codeclient)
);

CREATE TABLE APPORTEUR(
apporteurId INT IDENTITY,
codeapporteur VARCHAR(5) NOT NULL,
nomApporteur VARCHAR(50) NOT NULL,
prenomApporteur VARCHAR(50) NOT NULL,
PRIMARY KEY(apporteurId),
UNIQUE(codeapporteur)
);

CREATE TABLE TYPECONTRAT(
typeContratId INT IDENTITY,
codetypecontrat CHAR(2) NOT NULL,
libelleTypeContrat VARCHAR(50) NOT NULL,
coutpolice INT,
PRIMARY KEY(typeContratId),
UNIQUE(codetypecontrat)
);

CREATE TABLE GARANTIE(
garantieId INT IDENTITY,
codegarantie CHAR(1) NOT NULL,
Libelle_Garantie VARCHAR(50) NOT NULL,
PRIMARY KEY(garantieId),
UNIQUE(codegarantie)
);

CREATE TABLE PUISSANCE(
puissanceId INT IDENTITY,
codepuissance VARCHAR(3) NOT NULL,
borneInf INT NOT NULL,
borneSup INT NOT NULL,
unite INT NOT NULL,
PRIMARY KEY(puissanceId),
UNIQUE(codepuissance)
);

CREATE TABLE AGENCE(
agenceId INT IDENTITY,
codeagence VARCHAR(3) NOT NULL,
nomAgence VARCHAR(50) NOT NULL,
dateCreation DATE NOT NULL,
PRIMARY KEY(agenceId),
UNIQUE(codeagence)
);

CREATE TABLE CAISSIERE(
agenceId INT,
caissiereId INT IDENTITY,
codecaissiere VARCHAR(4) NOT NULL,
prenomcaissiere VARCHAR(40) NOT NULL,
nomcaissiere VARCHAR(40) NOT NULL,
PRIMARY KEY(agenceId, caissiereId),
UNIQUE(codecaissiere),
FOREIGN KEY(agenceId) REFERENCES AGENCE(agenceId)
);

CREATE TABLE AVENANT_LIBELLE(
avenantlibelleId INT IDENTITY,
avenantlibellecode CHAR(3) NOT NULL,
avenantlibellevaleur VARCHAR(50),
PRIMARY KEY(avenantlibelleId),
UNIQUE(avenantlibellecode)
);

CREATE TABLE MODEPAIEMENT(
modepaiemenId INT IDENTITY,
modepaiement_libelle VARCHAR(50),
PRIMARY KEY(modepaiemenId)
);

CREATE TABLE CEDEAO(
cedeaoId INT,
codecedeao INT NOT NULL,
cedeaodepuis DATE,
cedeaovehicule INT NOT NULL,
PRIMARY KEY(cedeaoId),
UNIQUE(codecedeao),
UNIQUE(cedeaovehicule)
);

CREATE TABLE CEDEAOHISTO(
cedeaoId INT,
cedeaohistoId CHAR(2),
cedeaohistodebut DATE NOT NULL,
cedeaohistofin DATE NOT NULL,
cedeaohistomontant INT NOT NULL,
PRIMARY KEY(cedeaoId, cedeaohistoId),
FOREIGN KEY(cedeaoId) REFERENCES CEDEAO(cedeaoId)
);

CREATE TABLE TAUX_DEFENSE_RECOURS(
tauxDrId INT IDENTITY,
tauxDrdebut DATE NOT NULL,
tauxDrfin DATE NOT NULL,
tauxvaleure DECIMAL(2,2) NOT NULL,
PRIMARY KEY(tauxDrId)
);

CREATE TABLE MARQUES(
marqueId INT IDENTITY,
libellemarque VARCHAR(50),
PRIMARY KEY(marqueId)
);

CREATE TABLE TAUXFGA(
tauxfgaId INT IDENTITY,
tauxfgadebut DATE,
tauxfgafin DATE,
tauxfgavaleur DECIMAL(2,2),
PRIMARY KEY(tauxfgaId)
);

CREATE TABLE TAUXTAXE(
tauxtaxeId INT IDENTITY,
tauxtaxedebut DATE,
tauxtaxefin DATE,
tauxtaxevaleur DECIMAL(2,2),
PRIMARY KEY(tauxtaxeId)
);

CREATE TABLE TAUXPROTDRIVER(
tauxprotdriverId INT IDENTITY,
tauxprotdriverdebut DATE,
tauxprotdriverfin DATE,
tauxprotdrivervaleur DECIMAL(2,2),
PRIMARY KEY(tauxprotdriverId)
);

CREATE TABLE PRORATA(
prorataId INT IDENTITY,
proratadatemini CHAR(3),
proratadatemaxi CHAR(3),
PRIMARY KEY(prorataId)
);

CREATE TABLE PRODUCTEUR(
agenceId INT,
producteurId INT IDENTITY,
codeproducteur VARCHAR(5) NOT NULL,
Nomproducteur VARCHAR(50) NOT NULL,
Prenomprodcteur VARCHAR(50) NOT NULL,
PRIMARY KEY(agenceId, producteurId),
UNIQUE(codeproducteur),
FOREIGN KEY(agenceId) REFERENCES AGENCE(agenceId)
);

CREATE TABLE CATCOMPLEMENT(
categorieId INT,
catcomplementId INT,
catcomplementcode CHAR(3) NOT NULL,
catcomplementlibelle VARCHAR(100) NOT NULL,
cedeaoId INT NOT NULL,
PRIMARY KEY(categorieId, catcomplementId),
FOREIGN KEY(categorieId) REFERENCES CATEGORIE(categorieId),
FOREIGN KEY(cedeaoId) REFERENCES CEDEAO(cedeaoId)
);

CREATE TABLE CONTRAT(
clientId INT,
contratId INT IDENTITY,
codecontrat VARCHAR(10) NOT NULL,
dateEffetContrat DATE NOT NULL,
dateExpirationContrat DATE NOT NULL,
reduction DECIMAL(2,2),
apporteurId INT,
agenceId INT NOT NULL,
agenceId_1 INT NOT NULL,
producteurId INT NOT NULL,
typeContratId INT NOT NULL,
PRIMARY KEY(clientId, contratId),
UNIQUE(codecontrat),
FOREIGN KEY(clientId) REFERENCES CLIENT(clientId),
FOREIGN KEY(apporteurId) REFERENCES APPORTEUR(apporteurId),
FOREIGN KEY(agenceId) REFERENCES AGENCE(agenceId),
FOREIGN KEY(agenceId_1, producteurId) REFERENCES PRODUCTEUR(agenceId, producteurId),
FOREIGN KEY(typeContratId) REFERENCES TYPECONTRAT(typeContratId)
);

CREATE TABLE AVENANT(
clientId_1 INT,
contratId INT,
avenantId INT IDENTITY,
codeavenant VARCHAR(10) NOT NULL,
libelleAvenant VARCHAR(50) NOT NULL,
dateemissionavenant DATE,
dateEffetAvenant DATE NOT NULL,
dateExpirationAvenant DATE NOT NULL,
bonus DECIMAL(2,2),
malus DECIMAL(2,2),
clientId INT,
avenantlibelleId INT NOT NULL,
apporteurId INT,
PRIMARY KEY(clientId_1, contratId, avenantId),
UNIQUE(codeavenant),
FOREIGN KEY(clientId_1, contratId) REFERENCES CONTRAT(clientId, contratId),
FOREIGN KEY(clientId) REFERENCES CLIENT(clientId),
FOREIGN KEY(avenantlibelleId) REFERENCES AVENANT_LIBELLE(avenantlibelleId),
FOREIGN KEY(apporteurId) REFERENCES APPORTEUR(apporteurId)
);

CREATE TABLE TARIF(
categorieId INT,
catcomplementId INT,
puissanceId INT,
tarifId INT,
datetarifdepuis DATE NOT NULL,
responsabilitecivile VARCHAR(50) NOT NULL,
PRIMARY KEY(categorieId, catcomplementId, puissanceId, tarifId),
FOREIGN KEY(categorieId, catcomplementId) REFERENCES CATCOMPLEMENT(categorieId, catcomplementId),
FOREIGN KEY(puissanceId) REFERENCES PUISSANCE(puissanceId)
);

CREATE TABLE TARIFHISTO(
categorieId INT,
catcomplementId INT,
puissanceId INT,
tarifId INT,
tarifhistoId INT,
tarifhistodurantdebut DATE NOT NULL,
tarifhistodurantfin DATE NOT NULL,
responsabilitecivilehisto INT NOT NULL,
PRIMARY KEY(categorieId, catcomplementId, puissanceId, tarifId, tarifhistoId),
FOREIGN KEY(categorieId, catcomplementId, puissanceId, tarifId) REFERENCES TARIF(categorieId, catcomplementId, puissanceId, tarifId)
);

CREATE TABLE VEHICULE(
marqueId INT,
clientId INT,
contratId INT,
vehiculeId INT IDENTITY,
codevehicule CHAR(5) NOT NULL,
immatriculation VARCHAR(10) NOT NULL,
type VARCHAR(50) NOT NULL,
energie VARCHAR(15) NOT NULL,
serie VARCHAR(40) NOT NULL,
vehiculepuissance INT NOT NULL,
nombreDePlaceCarteGrise INT NOT NULL,
nombreDePlaceCabine BIGINT NOT NULL,
nombrePlacehorscabine INT,
puissanceId INT NOT NULL,
categorieId INT NOT NULL,
catcomplementId INT NOT NULL,
clientId_1 INT NOT NULL,
PRIMARY KEY(marqueId, clientId, contratId, vehiculeId),
UNIQUE(codevehicule),
FOREIGN KEY(marqueId) REFERENCES MARQUES(marqueId),
FOREIGN KEY(clientId, contratId) REFERENCES CONTRAT(clientId, contratId),
FOREIGN KEY(puissanceId) REFERENCES PUISSANCE(puissanceId),
FOREIGN KEY(categorieId, catcomplementId) REFERENCES CATCOMPLEMENT(categorieId, catcomplementId),
FOREIGN KEY(clientId_1) REFERENCES CLIENT(clientId)
);

CREATE TABLE ENCAISSE_C(
clientId INT,
contratId INT,
agenceId INT,
caissiereId INT,
modepaiemenId INT,
dateEncaisseC DATE NOT NULL,
montantEncaisseC BIGINT NOT NULL,
PRIMARY KEY(clientId, contratId, agenceId, caissiereId, modepaiemenId),
FOREIGN KEY(clientId, contratId) REFERENCES CONTRAT(clientId, contratId),
FOREIGN KEY(agenceId, caissiereId) REFERENCES CAISSIERE(agenceId, caissiereId),
FOREIGN KEY(modepaiemenId) REFERENCES MODEPAIEMENT(modepaiemenId)
);

CREATE TABLE ENCAISSE_A(
clientId INT,
contratId INT,
avenantId INT,
agenceId INT,
caissiereId INT,
modepaiemenId INT,
dateEncaisseA DATE NOT NULL,
montantEncaisseA BIGINT NOT NULL,
PRIMARY KEY(clientId, contratId, avenantId, agenceId, caissiereId, modepaiemenId),
FOREIGN KEY(clientId, contratId, avenantId) REFERENCES AVENANT(clientId_1, contratId, avenantId),
FOREIGN KEY(agenceId, caissiereId) REFERENCES CAISSIERE(agenceId, caissiereId),
FOREIGN KEY(modepaiemenId) REFERENCES MODEPAIEMENT(modepaiemenId)
);

CREATE TABLE VEH_AV(
marqueId INT,
clientId INT,
contratId INT,
vehiculeId INT,
clientId_1 INT,
contratId_1 INT,
avenantId INT,
dateemissionvehav DATE NOT NULL,
PRIMARY KEY(marqueId, clientId, contratId, vehiculeId, clientId_1, contratId_1, avenantId),
FOREIGN KEY(marqueId, clientId, contratId, vehiculeId) REFERENCES VEHICULE(marqueId, clientId, contratId, vehiculeId),
FOREIGN KEY(clientId_1, contratId_1, avenantId) REFERENCES AVENANT(clientId_1, contratId, avenantId)
);

CREATE TABLE VEHIC_GARANT(
marqueId INT,
clientId INT,
contratId INT,
vehiculeId INT,
garantieId INT,
PRIMARY KEY(marqueId, clientId, contratId, vehiculeId, garantieId),
FOREIGN KEY(marqueId, clientId, contratId, vehiculeId) REFERENCES VEHICULE(marqueId, clientId, contratId, vehiculeId),
FOREIGN KEY(garantieId) REFERENCES GARANTIE(garantieId)
);

CREATE TABLE GARANT_VEHIC(
clientId INT,
contratId INT,
avenantId INT,
garantieId INT,
PRIMARY KEY(clientId, contratId, avenantId, garantieId),
FOREIGN KEY(clientId, contratId, avenantId) REFERENCES AVENANT(clientId_1, contratId, avenantId),
FOREIGN KEY(garantieId) REFERENCES GARANTIE(garantieId)
);

Quelles sont vos remarques et suggestions?
Merci d'avance.
NB: Mes meilleurs voeux du nouvel an 2021.

**fsmrel** · 05/01/2021, 19h14

Bonsoir Zidane7,

Je vous remercie pour vos voeux et vous adresse les miens. Que votre projet réussisse !

En relation avec le coût de la police :

Envoyé par Zidane7

Pour le moment je l'ai placé dans Type de contrat. Ensuite le cout de police est unique pour un contrat donné qu'il soit mono ou flotte.

Combien y a-t-il de types de contrats ? Les deux que vous avez mentionnés (mono, flotte) ? D’autres ?

Points divers

L’entité-type TAUX_DEFENSE_RECOURS a des petites soeurs taux, TAUXGFA et autres, avec date début et date de fin : d’accord.

Dans votre MCD, l’entité-type CONTRAT est identifiée relativement à CLIENT, mais cela n’apporte rien sinon une complication des requêtes SQL de sélection et autres : à éviter donc. Notez que dans mes posts (notamment #35, #76), je n’identifie aucunement CONTRAT relativement à CLIENT.

L’entité-type VEHICULE est identifiée relativement à l’entité-type MARQUE : certes un véhicule ne changera jamais de marque, mais la marque ne reste malgré tout qu’une référence. Par ailleurs, du fait de cette identification relative, vous observerez qu’au stade SQL l’attribut marqueId entre dans la composition des clés primaires des tables VEH_AV et VEHIC_GARANT, etc., ce qui n’aurait d’intérêt que si effectivement cela permettait de résoudre un quelconque problème dans le cas des avenants et des garanties : si tel n’est pas le cas (ce qui est fort probable !), évitez surtout d’identifier VEHICULE relativement à MARQUE.

En passant, le nom d’une entité-type est à mettre au singulier car ce nom symbolise celui d’un prédicat au sens de la logique. Exemple de l’entité-type CONTRAT : le contrat C a pour code codeContrat, il prend effet à la date dateEffetContrat, il expire à la date dateExpirationContrat et il fait l’objet d’une réduction reduction. Cette emploi du singulier vaut pour la marque d’un véhicule : remplacer 'MARQUES' par 'MARQUE'.

Dans votre MCD, l’entité-type VEHICULE est identifiée relativement à l’entité-type CONTRAT. Là encore on peut douter de la pertinence de ce mode d’identification, même si le lien est fort entre un véhicule et un contrat. D’une façon générale, l’identification relative ne se justifierait au plan fonctionnel que si un véhicule V1 devait tout au long de son existence être attaché au seul contrat C1, sans possibilité de changement. Qu’en est-il ? Que se passe-t-il si Raoul rachète un véhicule à Fernand, alors que tous les deux sont vos clients ? Cela dit, je vous recommande de ne pas identifier VEHICULE relativement à CONTRAT, voyez ci-dessous.

Certes, le MCD comporte une boucle :

VEHICULE > EST_SOUSCRIT > CONTRAT > AVENANT > VEH_AV > VEHICULE

Et dans ce genre de situation piégeuse, on doit s’assurer qu’il n’est pas possible de souscrire au contrat C1 pour le véhicule V1, et en même temps associer V1 à un avenant d’un autre contrat, disons C2. En effet, la boucle fait qu’on a deux chemins pour aller de VEHICULE à CONTRAT, le chemin VEHICULE > EST_SOUSCRIT > CONTRAT et le chemin VEHICULE > VEH_AV > AVENANT > CONTRAT et on a déjà évoqué les risques induits par les boucles (posts #76 et #87).

Qu’en est-il ici ? Descendons au niveau de la structure des tables SQL (je ne conserve ici que les attributs strictement nécessaires) :

CREATE TABLE CONTRAT
(
        contratId INT IDENTITY,
        contratCode VARCHAR(10) NOT NULL,
   CONSTRAINT CONTRAT_PK PRIMARY KEY(contratId),
   CONSTRAINT CONTRAT_AK UNIQUE(contratCode)
);

CREATE TABLE VEHICULE
(
        vehiculeId INT IDENTITY,
        vehiculeCode CHAR(8) NOT NULL,
        contratId INT NOT NULL,
   CONSTRAINT VEHICULE_PK PRIMARY KEY(vehiculeId),
   CONSTRAINT VEHICULE_AK UNIQUE(vehiculeCode),
   CONSTRAINT VEHICULE_CONTRAT_FK FOREIGN KEY(contratId) 
       REFERENCES CONTRAT(contratId)
);

CREATE TABLE AVENANT
(
        contratId INT,
        avenantId INT IDENTITY,
        avenantCode VARCHAR(8) NOT NULL,
   CONSTRAINT AVENANT_PK PRIMARY KEY(contratId, avenantId),
   CONSTRAINT AVENANT_AK UNIQUE(avenantCode),
   CONSTRAINT AVENANT_CONTRAT_FK FOREIGN KEY(contratId) 
       REFERENCES CONTRAT(contratId)
);

CREATE TABLE VEH_AV
(
        vehiculeId INT,
        contratId INT,
        avenantId INT,
   CONSTRAINT VEH_AV_PK PRIMARY KEY(vehiculeId, contratId, avenantId),
   CONSTRAINT VEH_AV_VEHICULE_FK FOREIGN KEY(vehiculeId) 
       REFERENCES VEHICULE(vehiculeId),
   CONSTRAINT VEH_AV_AVENANT_FK FOREIGN KEY(contratId, avenantId) 
       REFERENCES AVENANT(contratId, avenantId)
);

Quelques contrats :

INSERT INTO CONTRAT (contratCode)
VALUES 
    ('ctr01')
,   ('ctr02') 
,   ('ctr03') 
;

SELECT contratCode
FROM   CONTRAT
;

=>

contratCode

ctr01
ctr02
ctr03

Quelques véhicules :

INSERT INTO VEHICULE (vehiculeCode, contratId)
VALUES
    ('veh01-01',(SELECT contratId FROM CONTRAT WHERE contratCode = 'ctr01')) 
,   ('veh01-02',(SELECT contratId FROM CONTRAT WHERE contratCode = 'ctr01'))
,   ('veh02-01',(SELECT contratId FROM CONTRAT WHERE contratCode = 'ctr02'))
,   ('veh02-02',(SELECT contratId FROM CONTRAT WHERE contratCode = 'ctr02'))
;
SELECT vehiculeCode, contratCode  
FROM   VEHICULE as x 
  JOIN CONTRAT as y ON x.contratId = y.contratId
;

=>

vehiculeCode   contratCode

veh01-01       ctr01
veh01-02       ctr01
veh02-01       ctr02
veh02-02       ctr02

Quelques avenants :

INSERT INTO AVENANT (contratId, avenantCode)
VALUES
    ((SELECT contratId FROM CONTRAT WHERE contratCode = 'ctr01'), 'av01-1')
,
    ((SELECT contratId FROM CONTRAT WHERE contratCode = 'ctr02'), 'av01-2') 
,
    ((SELECT contratId FROM CONTRAT WHERE contratCode = 'ctr02'), 'av02-1') 
;
SELECT contratCode, avenantCode 
FROM   AVENANT as x JOIN CONTRAT as y ON x.contratId = y.contratId
;

=>

contratCode   avenantCode

ctr01         av01-1
ctr01         av01-2
ctr02         av02-1

Rattachement d’un véhicule à un avenant :

INSERT INTO VEH_AV (vehiculeId, contratId, avenantId)
VALUES
(   
     (SELECT vehiculeId 
      FROM   VEHICULE  
      WHERE  vehiculeCode = 'veh01-01')
   , (SELECT x.contratId 
      FROM   CONTRAT as x 
        JOIN VEHICULE as y ON x.contratId = y.contratId
      WHERE vehiculeCode = 'veh01-01' AND contratCode = 'ctr01')
   , (SELECT avenantId  
      FROM   AVENANT
      WHERE  avenantCode = 'av01-1') 
) ;

Le véhicule 'veh01-01' a été directement rattaché au contrat 'ctr01' via le 1er chemin, VEHICULE > EST_SOUSCRIT > CONTRAT. Vous pourrez vérifier qu’il est alors absolument impossible de le rattacher au contrat 'ctr02' par le biais de l’avenant 'av02-1' (donc via le chemin VEHICULE > VEH_AV > AVENANT > CONTRAT) : en effet la contrainte d’intégrité référentielle VEH_AV_AVENANT_FK est là pour surveiller que cet avenant 'av02-1' est rattaché au contrat 'ctr01', or tel n’est pas le cas puisque 'av02-1' est en fait rattaché au contrat 'ctr02' qui n’est pas référencé par le véhicule 'veh01-01', lequel ne peut faire référence qu’à un seul contrat, en l’occurrence le contrat 'ctr01' et seulement lui seul.
■
Par conséquent, inutile d’identifier VEHICULE relativement à CONTRAT, cela n’apporterait que des lourdeurs de programmation des requêtes SQL.

Association CLI_AV

Votre MCD comporte une association CLI_AV connectant un avenant A1 à un client disons C1. Mais cet avenant fait référence à un contrat, lequel fait référence à un client disons C2 : A1 fait donc référence transitivement à C2, d’où la question, C1 = C2 ?

Remarque concernant l’identification relative :

On peut user de l’identification relative comme dans le cas par exemple de l’entité-type TARIF parce que celle-ci est à l’origine une association et donc qu’au stade SQL la paire d’attributs est clé de la table TARIF. On use aussi de l’identification relative dans le cas de l’historisation (cf. TARIFHISTO, CEDEAOHISTO) ou dans le cas, des propriété multivaluées (par exemple les lignes de commande d’une commande), mais sinon c’est pour résoudre des problèmes techniques tels que ceux engendrés par les boucles (contraintes de chemin), ou des problèmes de performance (effet cluster), mais on n’en est pas là...

Virer les attributs inutiles, je dirais même pénalisants, tarifId (entité-type TARIF), cedeaohistoid (entité-type CEDEAOHISTO).

Les associations binaires ENCAISSE_A et ENCAISSE_C sont devenues des ternaires du fait de la participation de l’entité-type MODEPAIEMENT. Cela veut dire qu’un encaissement donné peut se faire avec plusieurs moyens de paiement. Ne serait-ce pas au contraire avec un seul moyen de paiement ?

**Zidane7** · 07/01/2021, 14h02

Bonjour Monsieur fsmrel,

Dans votre MCD, l’entité-type CONTRAT est identifiée relativement à CLIENT, mais cela n’apporte rien sinon une complication des requêtes SQL de sélection et autres : à éviter donc. Notez que dans mes posts (notamment #35, #76), je n’identifie aucunement CONTRAT relativement à CLIENT.

L’entité-type VEHICULE est identifiée relativement à l’entité-type MARQUE : certes un véhicule ne changera jamais de marque, mais la marque ne reste malgré tout qu’une référence. Par ailleurs, du fait de cette identification relative, vous observerez qu’au stade SQL l’attribut marqueId entre dans la composition des clés primaires des tables VEH_AV et VEHIC_GARANT, etc., ce qui n’aurait d’intérêt que si effectivement cela permettait de résoudre un quelconque problème dans le cas des avenants et des garanties : si tel n’est pas le cas (ce qui est fort probable !), évitez surtout d’identifier VEHICULE relativement à MARQUE.

En passant, le nom d’une entité-type est à mettre au singulier car ce nom symbolise celui d’un prédicat au sens de la logique. Exemple de l’entité-type CONTRAT : le contrat C a pour code codeContrat, il prend effet à la date dateEffetContrat, il expire à la date dateExpirationContrat et il fait l’objet d’une réduction reduction. Cette emploi du singulier vaut pour la marque d’un véhicule : remplacer 'MARQUES' par 'MARQUE'.

Dans votre MCD, l’entité-type VEHICULE est identifiée relativement à l’entité-type CONTRAT. Là encore on peut douter de la pertinence de ce mode d’identification, même si le lien est fort entre un véhicule et un contrat. D’une façon générale, l’identification relative ne se justifierait au plan fonctionnel que si un véhicule V1 devait tout au long de son existence être attaché au seul contrat C1, sans possibilité de changement. Qu’en est-il ? Que se passe-t-il si Raoul rachète un véhicule à Fernand, alors que tous les deux sont vos clients ? Cela dit, je vous recommande de ne pas identifier VEHICULE relativement à CONTRAT, voyez ci-dessous.

Ici j'ai apporté les modifications que vous m'avez demandé de faire. Voir le MCD ci-dessous.

Certes, le MCD comporte une boucle :
VEHICULE > EST_SOUSCRIT > CONTRAT > AVENANT > VEH_AV > VEHICULE

Et dans ce genre de situation piégeuse, on doit s’assurer qu’il n’est pas possible de souscrire au contrat C1 pour le véhicule V1, et en même temps associer V1 à un avenant d’un autre contrat, disons C2. En effet, la boucle fait qu’on a deux chemins pour aller de VEHICULE à CONTRAT, le chemin VEHICULE > EST_SOUSCRIT > CONTRAT et le chemin VEHICULE > VEH_AV > AVENANT > CONTRAT et on a déjà évoqué les risques induits par les boucles (posts #76 et #87).
Qu’en est-il ici ?

A ce niveau, un véhicule est lié à un et un seul contrat. Pour les autres modifications liés au véhicule ou à la police on passe par un ou plusieurs avenant(s). Par exemple un vehicule mono V1 appartenant à un client C1, si C1 veut vendre son véhicule à l'un de nos clients C2 et que C2 veut maintenir le même contrat alors on passe par un avenant de remplacement de nom ou propriétaire lié au contrat V1 jusqu'au jour où le client demande sa résiliation ou encore si le client demande de remplacer son véhicule par un autre de même catégorie, on fait cas à un avenant.
Qu'en pansez-vous?

Votre MCD comporte une association CLI_AV connectant un avenant A1 à un client disons C1. Mais cet avenant fait référence à un contrat, lequel fait référence à un client disons C2 : A1 fait donc référence transitivement à C2, d’où la question, C1 = C2 ?

Non pas dans tous les cas car si c1 vend V1 à C2 alors C1 peut etre remplacé par C2 et ainsi de suite.

Les associations binaires ENCAISSE_A et ENCAISSE_C sont devenues des ternaires du fait de la participation de l’entité-type MODEPAIEMENT. Cela veut dire qu’un encaissement donné peut se faire avec plusieurs moyens de paiement. Ne serait-ce pas au contraire avec un seul moyen de paiement ?

Ici supposons qu'un client qui a un contrat d'une flotte de véhicule en tous risques et le montant est de 1Milliard. Il vient le 01/01/2021 payé une partie de sa dette de 200 000 000 en espece après 10 jours il vient payer une partie 300 000 000 et il vient payer le reste de son contrat par cheque c'est a dire les 500 000 000. Comment faire?
Peut être je n'ai pas compris ou répondu à toutes vos questions.
Quelles sont vos remarques et suggestions?
Merci d'avance.
Le code sql:

CREATE TABLE CATEGORIE(
categorieId INT IDENTITY,
codecategorie CHAR(3) NOT NULL,
categorie VARCHAR(50) NOT NULL,
PRIMARY KEY(categorieId),
UNIQUE(codecategorie)
);

CREATE TABLE CLIENT(
clientId INT IDENTITY,
codeclient VARCHAR(10) NOT NULL,
nomClient VARCHAR(50) NOT NULL,
prenomClient VARCHAR(50) NOT NULL,
adresseClient VARCHAR(50) NOT NULL,
telephoneClient VARCHAR(30) NOT NULL,
PRIMARY KEY(clientId),
UNIQUE(codeclient)
);

CREATE TABLE APPORTEUR(
apporteurId INT IDENTITY,
codeapporteur VARCHAR(5) NOT NULL,
nomApporteur VARCHAR(50) NOT NULL,
prenomApporteur VARCHAR(50) NOT NULL,
PRIMARY KEY(apporteurId),
UNIQUE(codeapporteur)
);

CREATE TABLE TYPECONTRAT(
typeContratId INT IDENTITY,
codetypecontrat CHAR(2) NOT NULL,
libelleTypeContrat VARCHAR(50) NOT NULL,
PRIMARY KEY(typeContratId),
UNIQUE(codetypecontrat)
);

CREATE TABLE GARANTIE(
garantieId INT IDENTITY,
codegarantie CHAR(1) NOT NULL,
Libelle_Garantie VARCHAR(50) NOT NULL,
PRIMARY KEY(garantieId),
UNIQUE(codegarantie)
);

CREATE TABLE PUISSANCE(
puissanceId INT IDENTITY,
codepuissance VARCHAR(3) NOT NULL,
borneInf INT NOT NULL,
borneSup INT NOT NULL,
unite INT NOT NULL,
PRIMARY KEY(puissanceId),
UNIQUE(codepuissance)
);

CREATE TABLE AGENCE(
agenceId INT IDENTITY,
codeagence VARCHAR(3) NOT NULL,
nomAgence VARCHAR(50) NOT NULL,
dateCreation DATE NOT NULL,
PRIMARY KEY(agenceId),
UNIQUE(codeagence)
);

CREATE TABLE CAISSIERE(
agenceId INT,
caissiereId INT IDENTITY,
codecaissiere VARCHAR(4) NOT NULL,
prenomcaissiere VARCHAR(40) NOT NULL,
nomcaissiere VARCHAR(40) NOT NULL,
PRIMARY KEY(agenceId, caissiereId),
UNIQUE(codecaissiere),
FOREIGN KEY(agenceId) REFERENCES AGENCE(agenceId)
);

CREATE TABLE AVENANT_LIBELLE(
avenantlibelleId INT IDENTITY,
avenantlibellecode CHAR(3) NOT NULL,
avenantlibellevaleur VARCHAR(50),
PRIMARY KEY(avenantlibelleId),
UNIQUE(avenantlibellecode)
);

CREATE TABLE MODEPAIEMENT(
modepaiemenId INT IDENTITY,
modepaiement_libelle VARCHAR(50),
PRIMARY KEY(modepaiemenId)
);

CREATE TABLE CEDEAO(
cedeaoId INT,
codecedeao INT NOT NULL,
cedeaodepuis DATE,
cedeaovehicule INT NOT NULL,
PRIMARY KEY(cedeaoId),
UNIQUE(codecedeao),
UNIQUE(cedeaovehicule)
);

CREATE TABLE CEDEAOHISTO(
cedeaoId INT,
cedeaohistoId CHAR(2),
cedeaohistodebut DATE NOT NULL,
cedeaohistofin DATE NOT NULL,
cedeaohistomontant INT NOT NULL,
PRIMARY KEY(cedeaoId, cedeaohistoId),
FOREIGN KEY(cedeaoId) REFERENCES CEDEAO(cedeaoId)
);

CREATE TABLE TAUX_DEFENSE_RECOURS(
tauxDrId INT IDENTITY,
tauxDrdebut DATE NOT NULL,
tauxDrfin DATE NOT NULL,
tauxvaleure DECIMAL(2,2) NOT NULL,
PRIMARY KEY(tauxDrId)
);

CREATE TABLE MARQUE(
marqueId INT IDENTITY,
libellemarque VARCHAR(50),
PRIMARY KEY(marqueId)
);

CREATE TABLE TAUXFGA(
tauxfgaId INT IDENTITY,
tauxfgadebut DATE,
tauxfgafin DATE,
tauxfgavaleur DECIMAL(2,2),
PRIMARY KEY(tauxfgaId)
);

CREATE TABLE TAUXTAXE(
tauxtaxeId INT IDENTITY,
tauxtaxedebut DATE,
tauxtaxefin DATE,
tauxtaxevaleur DECIMAL(2,2),
PRIMARY KEY(tauxtaxeId)
);

CREATE TABLE TAUXPROTDRIVER(
tauxprotdriverId INT IDENTITY,
tauxprotdriverdebut DATE,
tauxprotdriverfin DATE,
tauxprotdrivervaleur DECIMAL(2,2),
PRIMARY KEY(tauxprotdriverId)
);

CREATE TABLE ANPRORATA(
prorataId INT IDENTITY,
proratadatemini CHAR(3),
proratadatemaxi CHAR(3),
PRIMARY KEY(prorataId)
);

CREATE TABLE COUTPOLICE(
coupoliceId VARCHAR(2),
coutpolice INT NOT NULL,
libellecoupolice VARCHAR(50),
PRIMARY KEY(coupoliceId)
);

CREATE TABLE PRODUCTEUR(
agenceId INT,
producteurId INT IDENTITY,
codeproducteur VARCHAR(5) NOT NULL,
Nomproducteur VARCHAR(50) NOT NULL,
Prenomprodcteur VARCHAR(50) NOT NULL,
PRIMARY KEY(agenceId, producteurId),
UNIQUE(codeproducteur),
FOREIGN KEY(agenceId) REFERENCES AGENCE(agenceId)
);

CREATE TABLE CATCOMPLEMENT(
categorieId INT,
catcomplementId INT,
catcomplementcode CHAR(3) NOT NULL,
catcomplementlibelle VARCHAR(100) NOT NULL,
cedeaoId INT NOT NULL,
PRIMARY KEY(categorieId, catcomplementId),
FOREIGN KEY(categorieId) REFERENCES CATEGORIE(categorieId),
FOREIGN KEY(cedeaoId) REFERENCES CEDEAO(cedeaoId)
);

CREATE TABLE CONTRAT(
contratId INT IDENTITY,
codecontrat VARCHAR(10) NOT NULL,
dateEffetContrat DATE NOT NULL,
dateExpirationContrat DATE NOT NULL,
reduction DECIMAL(2,2),
coupoliceId VARCHAR(2) NOT NULL,
clientId INT NOT NULL,
apporteurId INT,
agenceId INT NOT NULL,
agenceId_1 INT NOT NULL,
producteurId INT NOT NULL,
typeContratId INT NOT NULL,
PRIMARY KEY(contratId),
UNIQUE(codecontrat),
FOREIGN KEY(coupoliceId) REFERENCES COUTPOLICE(coupoliceId),
FOREIGN KEY(clientId) REFERENCES CLIENT(clientId),
FOREIGN KEY(apporteurId) REFERENCES APPORTEUR(apporteurId),
FOREIGN KEY(agenceId) REFERENCES AGENCE(agenceId),
FOREIGN KEY(agenceId_1, producteurId) REFERENCES PRODUCTEUR(agenceId, producteurId),
FOREIGN KEY(typeContratId) REFERENCES TYPECONTRAT(typeContratId)
);

CREATE TABLE AVENANT(
contratId INT,
avenantId INT IDENTITY,
codeavenant VARCHAR(10) NOT NULL,
libelleAvenant VARCHAR(50) NOT NULL,
dateemissionavenant DATE,
dateEffetAvenant DATE NOT NULL,
dateExpirationAvenant DATE NOT NULL,
bonus DECIMAL(2,2),
malus DECIMAL(2,2),
clientId INT,
avenantlibelleId INT NOT NULL,
apporteurId INT,
PRIMARY KEY(contratId, avenantId),
UNIQUE(codeavenant),
FOREIGN KEY(contratId) REFERENCES CONTRAT(contratId),
FOREIGN KEY(clientId) REFERENCES CLIENT(clientId),
FOREIGN KEY(avenantlibelleId) REFERENCES AVENANT_LIBELLE(avenantlibelleId),
FOREIGN KEY(apporteurId) REFERENCES APPORTEUR(apporteurId)
);

CREATE TABLE TARIF(
categorieId INT,
catcomplementId INT,
puissanceId INT,
tarifId INT,
datetarifdepuis DATE NOT NULL,
responsabilitecivile VARCHAR(50) NOT NULL,
PRIMARY KEY(categorieId, catcomplementId, puissanceId, tarifId),
FOREIGN KEY(categorieId, catcomplementId) REFERENCES CATCOMPLEMENT(categorieId, catcomplementId),
FOREIGN KEY(puissanceId) REFERENCES PUISSANCE(puissanceId)
);

CREATE TABLE TARIFHISTO(
categorieId INT,
catcomplementId INT,
puissanceId INT,
tarifId INT,
tarifhistoId INT,
tarifhistodurantdebut DATE NOT NULL,
tarifhistodurantfin DATE NOT NULL,
responsabilitecivilehisto INT NOT NULL,
PRIMARY KEY(categorieId, catcomplementId, puissanceId, tarifId, tarifhistoId),
FOREIGN KEY(categorieId, catcomplementId, puissanceId, tarifId) REFERENCES TARIF(categorieId, catcomplementId, puissanceId, tarifId)
);

CREATE TABLE VEHICULE(
vehiculeId INT IDENTITY,
codevehicule CHAR(5) NOT NULL,
immatriculation VARCHAR(10) NOT NULL,
type VARCHAR(50) NOT NULL,
energie VARCHAR(15) NOT NULL,
serie VARCHAR(40) NOT NULL,
vehiculepuissance INT NOT NULL,
nombreDePlaceCarteGrise INT NOT NULL,
nombreDePlaceCabine BIGINT NOT NULL,
nombrePlacehorscabine INT,
marqueId INT NOT NULL,
puissanceId INT NOT NULL,
categorieId INT NOT NULL,
catcomplementId INT NOT NULL,
contratId INT NOT NULL,
clientId INT NOT NULL,
PRIMARY KEY(vehiculeId),
UNIQUE(codevehicule),
FOREIGN KEY(marqueId) REFERENCES MARQUE(marqueId),
FOREIGN KEY(puissanceId) REFERENCES PUISSANCE(puissanceId),
FOREIGN KEY(categorieId, catcomplementId) REFERENCES CATCOMPLEMENT(categorieId, catcomplementId),
FOREIGN KEY(contratId) REFERENCES CONTRAT(contratId),
FOREIGN KEY(clientId) REFERENCES CLIENT(clientId)
);

CREATE TABLE ENCAISSE_C(
contratId INT,
agenceId INT,
caissiereId INT,
modepaiemenId INT,
dateEncaisseC DATE NOT NULL,
montantEncaisseC BIGINT NOT NULL,
PRIMARY KEY(contratId, agenceId, caissiereId, modepaiemenId),
FOREIGN KEY(contratId) REFERENCES CONTRAT(contratId),
FOREIGN KEY(agenceId, caissiereId) REFERENCES CAISSIERE(agenceId, caissiereId),
FOREIGN KEY(modepaiemenId) REFERENCES MODEPAIEMENT(modepaiemenId)
);

CREATE TABLE ENCAISSE_A(
contratId INT,
avenantId INT,
agenceId INT,
caissiereId INT,
modepaiemenId INT,
dateEncaisseA DATE NOT NULL,
montantEncaisseA BIGINT NOT NULL,
PRIMARY KEY(contratId, avenantId, agenceId, caissiereId, modepaiemenId),
FOREIGN KEY(contratId, avenantId) REFERENCES AVENANT(contratId, avenantId),
FOREIGN KEY(agenceId, caissiereId) REFERENCES CAISSIERE(agenceId, caissiereId),
FOREIGN KEY(modepaiemenId) REFERENCES MODEPAIEMENT(modepaiemenId)
);

CREATE TABLE VEH_AV(
vehiculeId INT,
contratId INT,
avenantId INT,
dateemissionvehav DATE NOT NULL,
PRIMARY KEY(vehiculeId, contratId, avenantId),
FOREIGN KEY(vehiculeId) REFERENCES VEHICULE(vehiculeId),
FOREIGN KEY(contratId, avenantId) REFERENCES AVENANT(contratId, avenantId)
);

CREATE TABLE VEHIC_GARANT(
vehiculeId INT,
garantieId INT,
PRIMARY KEY(vehiculeId, garantieId),
FOREIGN KEY(vehiculeId) REFERENCES VEHICULE(vehiculeId),
FOREIGN KEY(garantieId) REFERENCES GARANTIE(garantieId)
);

CREATE TABLE GARANT_VEHIC(
contratId INT,
avenantId INT,
garantieId INT,
PRIMARY KEY(contratId, avenantId, garantieId),
FOREIGN KEY(contratId, avenantId) REFERENCES AVENANT(contratId, avenantId),
FOREIGN KEY(garantieId) REFERENCES GARANTIE(garantieId)
);

Le MCD ci-dessous:

**fsmrel** · 07/01/2021, 17h08

Bonjour Zidane7,

J’ai pas mal de fers au feu, aussi je ne réponds qu’à une petite partie des problèmes évoqués, pour le reste j’essaierai de revenir au plus tôt.

Envoyé par Zidane7

Supposons qu'un client qui a un contrat d'une flotte de véhicule en tous risques et le montant est de 1Milliard. Il vient le 01/01/2021 payé une partie de sa dette de 200 000 000 en espece après 10 jours il vient payer une partie 300 000 000 et il vient payer le reste de son contrat par cheque c'est a dire les 500 000 000. Comment faire?

Le MCD ci-dessous présente une solution concernant les règlements pour les contrats, et le principe est évidemment le même pour les règlements des avenants. A noter qu’un règlement se fait selon un seul mode : si le 01/01/2021 le client effectue à la fois un règlement de 200 000 000 en espèces et 500 000 000 par chèque, on aura deux occurrences de règlement, une par mode de paiement. Techniquement pour qu’un règlement se fasse selon un seul mode, l’association ENCAISSE_C est transformée en entité-type associative, identifiée relativement à CONTRAT et CAISSIERE et dotée en plus d’un identifiant relatif EncaisCtrId pour que plusieurs règlements d’un même contrat CTR1 puissent être encaissés par la même caissière CAIS1.

Nom : Zidane7_assurance_auto_mode_paiement.png
Affichages : 209
Taille : 19,3 Ko

Envoyé par fsmrel

Virer les attributs inutiles, je dirais même pénalisants, tarifId (entité-type TARIF), cedeaohistoid (entité-type CEDEAOHISTO).

Le ménage reste à faire !

Concernant CEDEAOHISTO, c’est l’attribut cedaoHistoDebut qui participe à l’identifiant de l’entité-type.

Voyez ci-dessous.

Nom : Zidane7_assurance_auto_cedeao_histo.png
Affichages : 214
Taille : 33,3 Ko

**Zidane7** · 07/01/2021, 17h16

Bonsoir Monsieur fsmrel,

Certes, le MCD comporte une boucle :
VEHICULE > EST_SOUSCRIT > CONTRAT > AVENANT > VEH_AV > VEHICULE

Et dans ce genre de situation piégeuse, on doit s’assurer qu’il n’est pas possible de souscrire au contrat C1 pour le véhicule V1, et en même temps associer V1 à un avenant d’un autre contrat, disons C2. En effet, la boucle fait qu’on a deux chemins pour aller de VEHICULE à CONTRAT, le chemin VEHICULE > EST_SOUSCRIT > CONTRAT et le chemin VEHICULE > VEH_AV > AVENANT > CONTRAT et on a déjà évoqué les risques induits par les boucles (posts #76 et #87).
Qu’en est-il ici ?

Si je comprend bien votre question c'est d'éviter de passer par plusieurs chemin pour aboutir à la même solution. Comme par exemple c'est le client qui souscrit à un contrat et qu'il détient un ou plusieurs véhicules et ses véhicules sont liés aux garanties alors on doit pas joindre encore le contrat au véhicule car à travers le client on peut connaître les véhicules qui appartiennent au client.
Voici le code sql et le MCD ci-dessous:

CREATE TABLE CATEGORIE(
categorieId INT IDENTITY,
codecategorie CHAR(3) NOT NULL,
categorie VARCHAR(50) NOT NULL,
PRIMARY KEY(categorieId),
UNIQUE(codecategorie)
);

CREATE TABLE CLIENT(
clientId INT IDENTITY,
codeclient VARCHAR(10) NOT NULL,
nomClient VARCHAR(50) NOT NULL,
prenomClient VARCHAR(50) NOT NULL,
adresseClient VARCHAR(50) NOT NULL,
telephoneClient VARCHAR(30) NOT NULL,
PRIMARY KEY(clientId),
UNIQUE(codeclient)
);

CREATE TABLE APPORTEUR(
apporteurId INT IDENTITY,
codeapporteur VARCHAR(5) NOT NULL,
nomApporteur VARCHAR(50) NOT NULL,
prenomApporteur VARCHAR(50) NOT NULL,
PRIMARY KEY(apporteurId),
UNIQUE(codeapporteur)
);

CREATE TABLE TYPECONTRAT(
typeContratId INT IDENTITY,
codetypecontrat CHAR(2) NOT NULL,
libelleTypeContrat VARCHAR(50) NOT NULL,
PRIMARY KEY(typeContratId),
UNIQUE(codetypecontrat)
);

CREATE TABLE GARANTIE(
garantieId INT IDENTITY,
codegarantie CHAR(1) NOT NULL,
Libelle_Garantie VARCHAR(50) NOT NULL,
PRIMARY KEY(garantieId),
UNIQUE(codegarantie)
);

CREATE TABLE PUISSANCE(
puissanceId INT IDENTITY,
codepuissance VARCHAR(3) NOT NULL,
borneInf INT NOT NULL,
borneSup INT NOT NULL,
unite INT NOT NULL,
PRIMARY KEY(puissanceId),
UNIQUE(codepuissance)
);

CREATE TABLE AGENCE(
agenceId INT IDENTITY,
codeagence VARCHAR(3) NOT NULL,
nomAgence VARCHAR(50) NOT NULL,
dateCreation DATE NOT NULL,
PRIMARY KEY(agenceId),
UNIQUE(codeagence)
);

CREATE TABLE CAISSIERE(
agenceId INT,
caissiereId INT IDENTITY,
codecaissiere VARCHAR(4) NOT NULL,
prenomcaissiere VARCHAR(40) NOT NULL,
nomcaissiere VARCHAR(40) NOT NULL,
PRIMARY KEY(agenceId, caissiereId),
UNIQUE(codecaissiere),
FOREIGN KEY(agenceId) REFERENCES AGENCE(agenceId)
);

CREATE TABLE AVENANT_LIBELLE(
avenantlibelleId INT IDENTITY,
avenantlibellecode CHAR(3) NOT NULL,
avenantlibellevaleur VARCHAR(50),
PRIMARY KEY(avenantlibelleId),
UNIQUE(avenantlibellecode)
);

CREATE TABLE MODEPAIEMENT(
modepaiemenId INT IDENTITY,
modepaiement_libelle VARCHAR(50),
PRIMARY KEY(modepaiemenId)
);

CREATE TABLE CEDEAO(
cedeaoId INT,
codecedeao INT NOT NULL,
cedeaodepuis DATE,
cedeaovehicule INT NOT NULL,
PRIMARY KEY(cedeaoId),
UNIQUE(codecedeao),
UNIQUE(cedeaovehicule)
);

CREATE TABLE CEDEAOHISTO(
cedeaoId INT,
cedeaohistoId CHAR(2),
cedeaohistodebut DATE NOT NULL,
cedeaohistofin DATE NOT NULL,
cedeaohistomontant INT NOT NULL,
PRIMARY KEY(cedeaoId, cedeaohistoId),
FOREIGN KEY(cedeaoId) REFERENCES CEDEAO(cedeaoId)
);

CREATE TABLE TAUX_DEFENSE_RECOURS(
tauxDrId INT IDENTITY,
tauxDrdebut DATE NOT NULL,
tauxDrfin DATE NOT NULL,
tauxvaleure DECIMAL(2,2) NOT NULL,
PRIMARY KEY(tauxDrId)
);

CREATE TABLE MARQUE(
marqueId INT IDENTITY,
libellemarque VARCHAR(50),
PRIMARY KEY(marqueId)
);

CREATE TABLE TAUXFGA(
tauxfgaId INT IDENTITY,
tauxfgadebut DATE,
tauxfgafin DATE,
tauxfgavaleur DECIMAL(2,2),
PRIMARY KEY(tauxfgaId)
);

CREATE TABLE TAUXTAXE(
tauxtaxeId INT IDENTITY,
tauxtaxedebut DATE,
tauxtaxefin DATE,
tauxtaxevaleur DECIMAL(2,2),
PRIMARY KEY(tauxtaxeId)
);

CREATE TABLE TAUXPROTDRIVER(
tauxprotdriverId INT IDENTITY,
tauxprotdriverdebut DATE,
tauxprotdriverfin DATE,
tauxprotdrivervaleur DECIMAL(2,2),
PRIMARY KEY(tauxprotdriverId)
);

CREATE TABLE ANPRORATA(
prorataId INT IDENTITY,
proratadatemini CHAR(3),
proratadatemaxi CHAR(3),
PRIMARY KEY(prorataId)
);

CREATE TABLE COUTPOLICE(
coupoliceId VARCHAR(2),
coutpolice INT NOT NULL,
libellecoupolice VARCHAR(50),
PRIMARY KEY(coupoliceId)
);

CREATE TABLE PRODUCTEUR(
agenceId INT,
producteurId INT IDENTITY,
codeproducteur VARCHAR(5) NOT NULL,
Nomproducteur VARCHAR(50) NOT NULL,
Prenomprodcteur VARCHAR(50) NOT NULL,
PRIMARY KEY(agenceId, producteurId),
UNIQUE(codeproducteur),
FOREIGN KEY(agenceId) REFERENCES AGENCE(agenceId)
);

CREATE TABLE CATCOMPLEMENT(
categorieId INT,
catcomplementId INT,
catcomplementcode CHAR(3) NOT NULL,
catcomplementlibelle VARCHAR(100) NOT NULL,
cedeaoId INT NOT NULL,
PRIMARY KEY(categorieId, catcomplementId),
FOREIGN KEY(categorieId) REFERENCES CATEGORIE(categorieId),
FOREIGN KEY(cedeaoId) REFERENCES CEDEAO(cedeaoId)
);

CREATE TABLE VEHICULE(
vehiculeId INT IDENTITY,
codevehicule CHAR(5) NOT NULL,
immatriculation VARCHAR(10) NOT NULL,
type VARCHAR(50) NOT NULL,
energie VARCHAR(15) NOT NULL,
serie VARCHAR(40) NOT NULL,
vehiculepuissance INT NOT NULL,
nombreDePlaceCarteGrise INT NOT NULL,
nombreDePlaceCabine BIGINT NOT NULL,
nombrePlacehorscabine INT,
marqueId INT NOT NULL,
puissanceId INT NOT NULL,
categorieId INT NOT NULL,
catcomplementId INT NOT NULL,
clientId INT NOT NULL,
PRIMARY KEY(vehiculeId),
UNIQUE(codevehicule),
FOREIGN KEY(marqueId) REFERENCES MARQUE(marqueId),
FOREIGN KEY(puissanceId) REFERENCES PUISSANCE(puissanceId),
FOREIGN KEY(categorieId, catcomplementId) REFERENCES CATCOMPLEMENT(categorieId, catcomplementId),
FOREIGN KEY(clientId) REFERENCES CLIENT(clientId)
);

CREATE TABLE CONTRAT(
contratId INT IDENTITY,
codecontrat VARCHAR(10) NOT NULL,
dateEffetContrat DATE NOT NULL,
dateExpirationContrat DATE NOT NULL,
reduction DECIMAL(2,2),
coupoliceId VARCHAR(2) NOT NULL,
clientId INT NOT NULL,
apporteurId INT,
agenceId INT NOT NULL,
agenceId_1 INT NOT NULL,
producteurId INT NOT NULL,
typeContratId INT NOT NULL,
PRIMARY KEY(contratId),
UNIQUE(codecontrat),
FOREIGN KEY(coupoliceId) REFERENCES COUTPOLICE(coupoliceId),
FOREIGN KEY(clientId) REFERENCES CLIENT(clientId),
FOREIGN KEY(apporteurId) REFERENCES APPORTEUR(apporteurId),
FOREIGN KEY(agenceId) REFERENCES AGENCE(agenceId),
FOREIGN KEY(agenceId_1, producteurId) REFERENCES PRODUCTEUR(agenceId, producteurId),
FOREIGN KEY(typeContratId) REFERENCES TYPECONTRAT(typeContratId)
);

CREATE TABLE AVENANT(
contratId INT,
avenantId INT IDENTITY,
codeavenant VARCHAR(10) NOT NULL,
libelleAvenant VARCHAR(50) NOT NULL,
dateemissionavenant DATE,
dateEffetAvenant DATE NOT NULL,
dateExpirationAvenant DATE NOT NULL,
bonus DECIMAL(2,2),
malus DECIMAL(2,2),
avenantlibelleId INT NOT NULL,
apporteurId INT,
PRIMARY KEY(contratId, avenantId),
UNIQUE(codeavenant),
FOREIGN KEY(contratId) REFERENCES CONTRAT(contratId),
FOREIGN KEY(avenantlibelleId) REFERENCES AVENANT_LIBELLE(avenantlibelleId),
FOREIGN KEY(apporteurId) REFERENCES APPORTEUR(apporteurId)
);

CREATE TABLE TARIF(
categorieId INT,
catcomplementId INT,
puissanceId INT,
tarifId INT,
datetarifdepuis DATE NOT NULL,
responsabilitecivile VARCHAR(50) NOT NULL,
PRIMARY KEY(categorieId, catcomplementId, puissanceId, tarifId),
FOREIGN KEY(categorieId, catcomplementId) REFERENCES CATCOMPLEMENT(categorieId, catcomplementId),
FOREIGN KEY(puissanceId) REFERENCES PUISSANCE(puissanceId)
);

CREATE TABLE TARIFHISTO(
categorieId INT,
catcomplementId INT,
puissanceId INT,
tarifId INT,
tarifhistoId INT,
tarifhistodurantdebut DATE NOT NULL,
tarifhistodurantfin DATE NOT NULL,
responsabilitecivilehisto INT NOT NULL,
PRIMARY KEY(categorieId, catcomplementId, puissanceId, tarifId, tarifhistoId),
FOREIGN KEY(categorieId, catcomplementId, puissanceId, tarifId) REFERENCES TARIF(categorieId, catcomplementId, puissanceId, tarifId)
);

CREATE TABLE ENCAISSE_C(
contratId INT,
agenceId INT,
caissiereId INT,
modepaiemenId INT,
dateEncaisseC DATE NOT NULL,
montantEncaisseC BIGINT NOT NULL,
PRIMARY KEY(contratId, agenceId, caissiereId, modepaiemenId),
FOREIGN KEY(contratId) REFERENCES CONTRAT(contratId),
FOREIGN KEY(agenceId, caissiereId) REFERENCES CAISSIERE(agenceId, caissiereId),
FOREIGN KEY(modepaiemenId) REFERENCES MODEPAIEMENT(modepaiemenId)
);

CREATE TABLE ENCAISSE_A(
contratId INT,
avenantId INT,
agenceId INT,
caissiereId INT,
modepaiemenId INT,
dateEncaisseA DATE NOT NULL,
montantEncaisseA BIGINT NOT NULL,
PRIMARY KEY(contratId, avenantId, agenceId, caissiereId, modepaiemenId),
FOREIGN KEY(contratId, avenantId) REFERENCES AVENANT(contratId, avenantId),
FOREIGN KEY(agenceId, caissiereId) REFERENCES CAISSIERE(agenceId, caissiereId),
FOREIGN KEY(modepaiemenId) REFERENCES MODEPAIEMENT(modepaiemenId)
);

CREATE TABLE VEHIC_GARANT(
vehiculeId INT,
garantieId INT,
PRIMARY KEY(vehiculeId, garantieId),
FOREIGN KEY(vehiculeId) REFERENCES VEHICULE(vehiculeId),
FOREIGN KEY(garantieId) REFERENCES GARANTIE(garantieId)
);

NB: Vous avez repondu à certaines questions avant que je ne postule cette partie. Merci beaucoup.
A ce qui concerne l'encaissement, une caissière peut ne pas être aussi un producteur. selon votre schema ci-dessous, j'ai fait une modification du MCD.
Quelles sont vos remarques et suggestions?
Merci d'avance.

**Zidane7** · 11/01/2021, 18h32

Bonsoir Monsieur,
A present je coonstate qu'il y a une erreur au niveau dans le CODE SQL de mon MCD mais je ne vois pas d'où vient l'erreur.
Code sql:

Erreur dans le MLD : impossible de générer le script SQL

Regardez le MCD en question.
Que voyez-vous?
Merci d'avance.