Conception d'un MCD pour une assurance automobile

**fsmrel** · 28/10/2020, 16h54

A propos du coût de la police.

Envoyé par Zidane7

à part la remorque, il y a aussi le cas des flottes dont la police peut varier donc à ce niveau on laisse la main à l'utilisateur de saisir la police.

Est-ce à dire que le montant de la police ferait l’objet d’une colonne de la table VEHICULE ? Ou de la table CONTRAT ? Ou de la table CLIENT ? Sinon de quelle autre table ?

**fsmrel** · 28/10/2020, 18h01

Envoyé par Zidane7

En principe la CEDEAO se trouve non seulement dans la catégorie mais aussi dans la sous catégorie à des différence près à savoir de la catégorie 1 jusqu'à la catégorie 4 et ses sous-catégories elle est égale à 100 0000 et de la catégorie 5 et sous-catégories (MOTO), elle est égale à 70 000. Pour se fait ne serait-il pas possible d'en faire une table(sous forme de constante qui contient les valeurs suivantes: 100 000 pour toutes sortes de véhicules et 70 000 pour toutes sortes de motos) qui li non seulement la catégorie mais aussi les sous-catégories?

« En principe », ou de façon pérenne ?

Quoi qu’il en soit, si je comprends bien, pour les catégories 1, 2, 3, 4, la CEDEAO est égale à 100 000, donc on ne descend pas au niveau des sous-catégories. Pour la catégorie 5 (MOTO) la CEDEAO est égale à 70 000 et on ne descend pas non plus au niveau des sous-catégories. Dans ces conditions on peut effectivement mettre en oeuvre une entité-type CEDEAO permettant d’affecter les montants de la CEDEAO. Cette entité-type est associable à l’entité-type CATEGORIE :

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

Au stade SQL :

CREATE TABLE CEDEAO
(
   cedeaoId        INT IDENTITY,
   cedeaoCode      CHAR(4) NOT NULL,
   cedeaoMontant   INT NOT NULL,
   CONSTRAINT CEDEAO_PK PRIMARY KEY(cedeaoId),
   CONSTRAINT CEDEAO_AK UNIQUE(cedeaoCode),
);

INSERT INTO CEDEAO (cedeaoCode, cedeaoMontant)
VALUES
    ('c1', 100000)
  , ('c2', 70000)
 ;
SELECT cedeaoCode, cedeaoMontant 
FROM   CEDEAO 
;

=>

cedeaoCode   cedeaoMontant

c1           100000
c2           70000

CREATE TABLE CATEGORIE
(
   catId        INT IDENTITY,
   catCode      VARCHAR(4) NOT NULL,
   catLibelle   VARCHAR(64) NOT NULL,
   cedeaoId     INT NOT NULL,  
   CONSTRAINT CATEGORIE_PK PRIMARY KEY(catId),
   CONSTRAINT CATEGORIE_AK UNIQUE(catCode),
   CONSTRAINT CATEGORIE_CEDEAO_FK FOREIGN KEY (cedeaoId)
       REFERENCES CEDEAO (cedeaoId)
);

INSERT INTO CATEGORIE (catCode, catLibelle, cedeaoId)
VALUES
     ('1', 'Catégorie 1 : affaires et promenades (AP)',
      (SELECT cedeaoId FROM   CEDEAO WHERE cedeaoCode = 'c1'))
  ,
    ('2', 'Catégorie 2 : transport pour le compte de l''assuré (TPC)',
      (SELECT cedeaoId FROM   CEDEAO WHERE cedeaoCode = 'c1'))
  ,
    ('3', 'Catégorie 3 : transport public de marchandises (TPM)',
      (SELECT cedeaoId FROM   CEDEAO WHERE cedeaoCode = 'c1'))
  ,
    ('4', 'Catégorie 4 : taxis / transport public)',
       (SELECT cedeaoId FROM   CEDEAO WHERE cedeaoCode = 'c1'))
  ,
    ('5', 'Catégorie 5 : motos',
      (SELECT cedeaoId FROM   CEDEAO WHERE cedeaoCode = 'c2'))
;
SELECT catCode, cedeaoMontant, catLibelle 
FROM   CATEGORIE as x JOIN CEDEAO as y ON x.cedeaoId = y.cedeaoId 
;

=>

catCode   cedeaoMontant catLibelle

1         100000        Catégorie 1 : affaires et promenades (AP)
2         100000        Catégorie 2 : transport pour le compte de l'assuré (TPC)
3         100000        Catégorie 3 : transport public de marchandises (TPM)
4         100000        Catégorie 4 : taxis / transport public)
5         70000         Catégorie 5 : motos

**Zidane7** · 28/10/2020, 18h27

Bonsoir Monsieur fsmrel.

Vous avez précisé que la défense recours est égale à 25% de la responsabilité civile, mais dans vos tableaux c’est plutôt 20% (ce qui n’est pas toujours respecté, par exemple pour la catégorie 1, puissance < 3 CV, responsabilité civile = 203657, votre tableau fournit la valeur 10183 pour la défense recours, alors que, sur la base de 20%, le calcul donne 10083).

Excusez-moi de m'avoir trompé sur la défense recours. C'est plutôt égale= Reponsabilité civile multiplié par 5% (RC*5%). Pardonnez moi du fait de se tromper sur le pourcentage.

Par ailleurs, attention ! Si un jour le pourcentage change, alors par ALTER TABLE on pourra modifier la règle de calcul de la colonne defenseRecours, mais il faudra réfléchir aux conséquences sur l’existant.
Votre sentiment à propos de la colonne calculée defenseRecours ?

Ici je dirai de créer une table dans laquelle on met les taux et ensuite crée une table similaire qu'on appellera historique des taux. Ça revient à la même chose pour la table PFC car elle contient les tarif.
Que pensez-vous?
Merci par avance.

**fsmrel** · 29/10/2020, 02h24

Envoyé par Zidane7

créer une table dans laquelle on met les taux et ensuite créer une table similaire qu'on appellera historique des taux.

D’accord. Prévoir donc deux tables (deux entités-types avec Looping) :

Une 1re table pour les taux actuels de la défense recours :

CREATE TABLE TAUX_DEPUIS
(
        tauxId        INT IDENTITY
      , tauxDepuis    DATE            NOT NULL
      , tauxValeur    DECIMAL(5,2)    NOT NULL
    ,  CONSTRAINT TAUX_DEPUIS_PK PRIMARY KEY (tauxId)
    ,  CONSTRAINT TAUX_DEPUIS_AK UNIQUE (tauxDepuis)
) ;

Taux actuel (20%, en vigueur par exemple depuis le 25 octobre 2015) :

INSERT INTO TAUX_DEPUIS
VALUES 
( '2015-10-25', 0.05)
;

Une 2e table pour les anciens taux :

CREATE TABLE TAUX_HISTO
(
        tauxId            INT            NOT NULL
      , tauxDurantDebut   DATE           NOT NULL           
      , tauxDurantFin     DATE           NOT NULL           
      , tauxValeur        DECIMAL(5,2)   NOT NULL
    ,  CONSTRAINT TAUX_HISTO_PK PRIMARY KEY (tauxId, tauxDurantDebut)
    ,  CONSTRAINT TAUX_HISTO_AK UNIQUE (tauxId, tauxDurantFin)
) ;

Une requête avec le taux actuel des défenses recours (aménagement de la dernière requête du post #55) :

SELECT unite, borneInf, borneSup
     , LEFT(catLibelle,12) as cat, RIGHT(sousCatLibelle,24) as sousCat
     , respCivile
     , CEILING(respCivile * (SELECT  tauxValeur FROM TAUX_DEPUIS)) AS defenseRecours   
FROM   PFC AS x 
  JOIN PUISSANCE AS y ON x.puissanceId = y.puissanceId
  JOIN CATEGORIE AS z ON x.catId = z.catId
  JOIN SOUS_CATEGORIE AS t ON x.catId = t.catId and x.sousCatId = t.sousCatId
ORDER BY cat, sousCat, unite, borneInf    
;

=>

unite        borneInf    borneSup    cat          sousCat                  respCivile   defenseRecours
CV           0           2           Catégorie 1   /                       201657       10083
CV           3           6           Catégorie 1   /                       244399       12220
CV           7           10          Catégorie 1   /                       285114       14256
CV           11          14          Catégorie 1   /                       346233       17312
CV           15          23          Catégorie 1   /                       407332       20367
CV           24          9999        Catégorie 1   /                       427698       21385
CV           3           6           Catégorie 2   /                       401383       20070
CV           7           10          Catégorie 2   /                       563124       28157
CV           11          14          Catégorie 2   /                       583830       29192
CV           15          23          Catégorie 2   /                       693298       34665
CV           24          9999        Catégorie 2   /                       716102       35806
remorque     0           0           Catégorie 3  marchandises (remorque)  1000000      50000
tonne        0           1           Catégorie 3  marchandises (tonnage)   578610       28931
tonne        2           5           Catégorie 3  marchandises (tonnage)   694332       34717
tonne        6           10          Catégorie 3  marchandises (tonnage)   921888       46095
tonne        11          15          Catégorie 3  marchandises (tonnage)   1383054      69153
tonne        16          25          Catégorie 3  marchandises (tonnage)   1843776      92189
tonne        26          9999        Catégorie 3  marchandises (tonnage)   2764164      138209
CV           3           6           Catégorie 4  taxis 4 places           690102       34506
CV           7           10          Catégorie 4  taxis 4 places           728486       36425
CV           11          14          Catégorie 4  taxis 4 places           806813       40341
CV           15          23          Catégorie 4  taxis 4 places           846877       42344
CV           3           6           Catégorie 4  taxis 5 places           784239       39212
CV           7           10          Catégorie 4  taxis 5 places           822563       41129
CV           11          14          Catégorie 4  taxis 5 places           900947       45048
CV           15          23          Catégorie 4  taxis 5 places           941015       47051

Si vous souhaitez que les tables servent aussi pour d’autres taux que la défense recours, il faudra prévoir une colonne pour le type de taux :

CREATE TABLE TAUX_DEPUIS
(
        tauxId        INT IDENTITY
      , tauxType      CHAR(4)         NOT NULL
      , tauxDepuis    DATE            NOT NULL
      , tauxValeur    DECIMAL(5,2)    NOT NULL
    ,  CONSTRAINT TAUX_DEPUIS_PK PRIMARY KEY (tauxId)
    ,  CONSTRAINT TAUX_DEPUIS_AK UNIQUE (tauxType, tauxDepuis)
) ;

Les anciens taux :

CREATE TABLE TAUX_HISTO
(
        tauxId            INT            NOT NULL
      , tauxType          CHAR(4)        NOT NULL
      , tauxDurantDebut   DATE           NOT NULL           
      , tauxDurantFin     DATE           NOT NULL           
      , tauxValeur        DECIMAL(5,2)   NOT NULL
    ,  CONSTRAINT TAUX_HISTO_PK PRIMARY KEY (tauxId, tauxDurantDebut)
    ,  CONSTRAINT TAUX_HISTO_AK1 UNIQUE (tauxId, tauxDurantFin)
    ,  CONSTRAINT TAUX_HISTO_AK2 UNIQUE (tauxType, tauxDurantDebut)
) ;

**Zidane7** · 30/10/2020, 18h42

Bonsoir Monsieur
Par rapport à la CEDEAO, je viens de faire une modification dont voici la regle ci-dessous:

[SOUS_CATEGORIE]----1,1---(CEDEAO_SOUS_CAT)---1,N----[CEDEAODEPUIS]

[CATEGORIE]----1,1---(CEDEAO_CAT)---1,N----[CEDEAODEPUIS]

[CEDEAOHISTO]----1,1---(CEDEAODEPUIS_HISTO)---1,N----[CEDEAODEPUIS]

Dont voici le code sql qui suit:

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(2) 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 CEDEAODEPUIS(
cedeaodepuisId INT,
codecedeaodepuis CHAR(2) NOT NULL,
datecedeaodepuis DATE NOT NULL,
cedeaodepuisveh INT NOT NULL,
cedeaodepuismoto INT NOT NULL,
PRIMARY KEY(cedeaodepuisId),
UNIQUE(codecedeaodepuis)
);

CREATE TABLE CEDEAOHISTO(
cedeaohistoId INT,
codecedeaohisto INT NOT NULL,
cedeaohistodurantdebut DATE NOT NULL,
cedeaohistodurantfin DATE NOT NULL,
cedeaohistoveh INT NOT NULL,
cedeaohistomoto INT NOT NULL,
cedeaodepuisId INT NOT NULL,
PRIMARY KEY(cedeaohistoId),
UNIQUE(codecedeaohisto),
FOREIGN KEY(cedeaodepuisId) REFERENCES CEDEAODEPUIS(cedeaodepuisId)
);

CREATE TABLE CATEGORIE(
categorieId INT IDENTITY,
codecategorie CHAR(4) NOT NULL,
libelleCategorie VARCHAR(50) NOT NULL,
nombreplacecategorie INT NOT NULL,
cedeaodepuisId INT NOT NULL,
PRIMARY KEY(categorieId),
UNIQUE(codecategorie),
FOREIGN KEY(cedeaodepuisId) REFERENCES CEDEAODEPUIS(cedeaodepuisId)
);

CREATE TABLE Sous_Garantie(
sousGarantieId INT IDENTITY,
codesousgarantie CHAR(2) NOT NULL,
libelleSousGarantieId VARCHAR(50) NOT NULL,
categorieId INT NOT NULL,
garantieId INT NOT NULL,
PRIMARY KEY(sousGarantieId),
UNIQUE(codesousgarantie),
FOREIGN KEY(categorieId) REFERENCES CATEGORIE(categorieId),
FOREIGN KEY(garantieId) REFERENCES GARANTIE(garantieId)
);

CREATE TABLE PFC(
categorieId INT,
puissanceId INT,
responsabilitecivile VARCHAR(50) NOT NULL,
PRIMARY KEY(categorieId, puissanceId),
FOREIGN KEY(categorieId) REFERENCES CATEGORIE(categorieId),
FOREIGN KEY(puissanceId) REFERENCES PUISSANCE(puissanceId)
);

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 SOUS_CATEGORIE(
souscategorieId INT IDENTITY,
codesouscategorie VARCHAR(4) NOT NULL,
libelleSousCategorie VARCHAR(50) NOT NULL,
cedeaodepuisId INT NOT NULL,
categorieId INT NOT NULL,
PRIMARY KEY(souscategorieId),
UNIQUE(codesouscategorie),
FOREIGN KEY(cedeaodepuisId) REFERENCES CEDEAODEPUIS(cedeaodepuisId),
FOREIGN KEY(categorieId) REFERENCES CATEGORIE(categorieId)
);

CREATE TABLE PFC_SC(
categorieId INT,
puissanceId INT,
souscategorieId INT NOT NULL,
PRIMARY KEY(categorieId, puissanceId),
FOREIGN KEY(categorieId, puissanceId) REFERENCES PFC(categorieId, puissanceId),
FOREIGN KEY(souscategorieId) REFERENCES SOUS_CATEGORIE(souscategorieId)
);

CREATE TABLE CONTRAT(
clientId INT,
contratId INT IDENTITY,
codecontrat VARCHAR(10) NOT NULL,
dateEffetContrat DATE NOT NULL,
dateExpirationContrat DATE NOT NULL,
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 INT,
contratId INT,
avenantId INT IDENTITY,
codeavenant VARCHAR(10) NOT NULL,
libelleAvenant VARCHAR(50) NOT NULL,
dateEffetAvenant DATE NOT NULL,
dateExpirationAvenant DATE NOT NULL,
avenantlibelleId INT NOT NULL,
apporteurId INT,
PRIMARY KEY(clientId, contratId, avenantId),
UNIQUE(codeavenant),
FOREIGN KEY(clientId, contratId) REFERENCES CONTRAT(clientId, contratId),
FOREIGN KEY(avenantlibelleId) REFERENCES AVENANT_LIBELLE(avenantlibelleId),
FOREIGN KEY(apporteurId) REFERENCES APPORTEUR(apporteurId)
);

CREATE TABLE VEHICULE(
clientId INT,
contratId INT,
vehiculeId INT IDENTITY,
codevehicule CHAR(5) NOT NULL,
marque VARCHAR(50) 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,
clientId_1 INT NOT NULL,
categorieId INT NOT NULL,
puissanceId INT NOT NULL,
PRIMARY KEY(clientId, contratId, vehiculeId),
UNIQUE(codevehicule),
FOREIGN KEY(clientId, contratId) REFERENCES CONTRAT(clientId, contratId),
FOREIGN KEY(clientId_1) REFERENCES CLIENT(clientId),
FOREIGN KEY(categorieId, puissanceId) REFERENCES PFC(categorieId, puissanceId)
);

CREATE TABLE PROTEGER(
categorieId INT,
garantieId INT,
PRIMARY KEY(categorieId, garantieId),
FOREIGN KEY(categorieId) REFERENCES CATEGORIE(categorieId),
FOREIGN KEY(garantieId) REFERENCES GARANTIE(garantieId)
);

CREATE TABLE ASSOCIER(
clientId INT,
contratId INT,
garantieId INT,
PRIMARY KEY(clientId, contratId, garantieId),
FOREIGN KEY(clientId, contratId) REFERENCES CONTRAT(clientId, contratId),
FOREIGN KEY(garantieId) REFERENCES GARANTIE(garantieId)
);

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, contratId, avenantId),
FOREIGN KEY(agenceId, caissiereId) REFERENCES CAISSIERE(agenceId, caissiereId),
FOREIGN KEY(modepaiemenId) REFERENCES MODEPAIEMENT(modepaiemenId)
);

CREATE TABLE CLI_AV(
clientId INT,
clientId_1 INT,
contratId INT,
avenantId INT,
dateemissioncliav DATE NOT NULL,
PRIMARY KEY(clientId, clientId_1, contratId, avenantId),
FOREIGN KEY(clientId) REFERENCES CLIENT(clientId),
FOREIGN KEY(clientId_1, contratId, avenantId) REFERENCES AVENANT(clientId, contratId, avenantId)
);

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

Pour celui du coût de police, à mon avis il est comme suit:
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.
Avez-vous des remarques et suggestions?
Merci par avance.

**fsmrel** · 31/10/2020, 20h18

Bonsoir Zidane7,

Manifestement, la modélisation du post #55 ne vous convient pas, vous ne souhaitez pas avoir de sous-catégorie pour les catégories 1, 2, 3. D’accord, voyons voir ce que l’on peut faire à partir de la modélisation du post #24 (mise en oeuvre de l’entité-type PFC_SC).

La modification principale à apporter au MCD concerne la responsabilité civile. En effet, pour les catégories sans sous-catégorie, si la RC est bien une propriété de l’entité-type PFC, ça n’est pas le cas pour les catégories avec des sous-catégories. En conséquence, l’entité-type PFC_SC doit elle aussi être porteuse d’une propriété responsabilité civile (attribut RespCivile). Partant de là (pour cause de redondance de la RC), PFC_SC est à déconnecter de PFC et devient une association (matérialisée) entre SOUS_CATEGORIE et PUISSANCE :

Nom : Zidane7_assurance_auto (3)PFC_SC.png
Affichages : 202
Taille : 17,1 Ko

A noter la non prise en compte (provisoire) de la sécurité routière et du coût de la police.

Jeu d’essai correspondant :

Les puissances (pas de changement par rapport au post #55) :

CREATE TABLE PUISSANCE
(
   puissanceId     INT IDENTITY,
   puissanceCode   VARCHAR(8)  NOT NULL,
   borneInf        INT NOT NULL,
   borneSup        INT NOT NULL,
   unite           VARCHAR(12) NOT NULL,
   CONSTRAINT PUISSANCE_PK PRIMARY KEY(puissanceId),
   CONSTRAINT PUISSANCE_AK UNIQUE(puissanceCode),
   CONSTRAINT PUISSANCE_UK UNIQUE(borneInf, unite)
);
SET IDENTITY_INSERT PUISSANCE OFF ;
DBCC CHECKIDENT (PUISSANCE, RESEED, 1)
;
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') 
;
SELECT '' as PUISSANCE, * FROM PUISSANCE ;

Les catégories (pas de changement par rapport au post #55) :

CREATE TABLE CATEGORIE
(
   catId        INT IDENTITY,
   catCode      VARCHAR(4) NOT NULL,
   catLibelle   VARCHAR(64) NOT NULL,
   CONSTRAINT CATEGORIE_PK PRIMARY KEY(catId),
   CONSTRAINT CATEGORIE_AK UNIQUE(catCode)
);
;
SET IDENTITY_INSERT CATEGORIE OFF ;
DBCC CHECKIDENT (CATEGORIE, RESEED, 1) ;

INSERT INTO CATEGORIE (catCode, catLibelle)
VALUES
    ('1', 'Catégorie 1 : affaires et promenades (AP)')
  , ('2', 'Catégorie 2 : transport pour le compte de l''assuré (TPC)')
  , ('3', 'Catégorie 3 : transport public de marchandises (TPM)')
  , ('4', 'Catégorie 4 : taxis / transport public)')
  , ('5', 'Catégorie 5 : motos)')
;
SELECT '' as CATEGORIE, catCode, catLibelle 
FROM   CATEGORIE ;

Les sous-catégories (pas de changement par rapport au post #55, sinon qu’on ne tient pas compte de la sécurité routière et du coût de la police) :

CREATE TABLE SOUS_CATEGORIE
(
   catId INT,
   sousCatId INT IDENTITY,
   sousCatCode VARCHAR(4) NOT NULL,
   sousCatLibelle VARCHAR(96) NOT NULL,
   CONSTRAINT SOUS_CATEGORIE_PK PRIMARY KEY(catId, sousCatId),
   CONSTRAINT SOUS_CATEGORIE_AK UNIQUE(sousCatCode),
   CONSTRAINT SOUS_CATEGORIE_CATEGORIE_FK FOREIGN KEY(catId) 
       REFERENCES CATEGORIE(catId)
);

SET IDENTITY_INSERT SOUS_CATEGORIE OFF ;
DBCC CHECKIDENT (SOUS_CATEGORIE, RESEED, 1) ;

INSERT INTO SOUS_CATEGORIE (catId, sousCatCode, sousCatLibelle)
VALUES
    ((SELECT catId FROM CATEGORIE WHERE catCode = '3'), '3-1', 'marchandises (tonnage)')
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '3'), '3-2', 'remorque')
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '4'), '4-1', 'taxis 4 places')
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '4'), '4-2', 'taxis 5 places')
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '4'), '4-3', 'taxis 6 places')
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '4'), '4-5', 'TPV 18 places')
;
SELECT '' as SOUS_CATEGORIE, catCode, sousCatCode, sousCatLibelle
FROM   CATEGORIE as x JOIN SOUS_CATEGORIE as y ON x.catId = y.catId   
;

=>

SOUS_CATEGORIE catCode   sousCatCode   sousCatLibelle

               3         3-1           marchandises (tonnage)
               3         3-2           remorque
               4         4-1           taxis 4 places
               4         4-2           taxis 5 places
               4         4-3           taxis 6 places
               4         4-5           TPV 18 places

A noter, comme convenu, que les catégories 1 et 2 n’ont pas de sous-catégorie. Pour le moment, comme les remorques fichent la patouille, j’ai conservé les sous-catégories pour la catégorie 3. Faudrait-il plutôt créer une catégorie dédiée aux remorques ? Autre procédé ?

Les PFC. Changement par rapport au post #55 : pas d’attribut defenseRecours, et surtout remplacement de la relation avec SOUS-CATEGORIE par la relation avec CATEGORIE :

CREATE TABLE PFC
(
   puissanceId INT,
   catId INT,
   respCivile INT NOT NULL,
   CONSTRAINT PFC_PK PRIMARY KEY(catId, puissanceId),
   CONSTRAINT PFC_CATEGORIE_FK FOREIGN KEY(catId) 
       REFERENCES CATEGORIE(catId),
   CONSTRAINT PFC_PUISSANCE_FK FOREIGN KEY(puissanceId) 
       REFERENCES PUISSANCE(puissanceId)
); 

-- PFC, cas de la catégorie 1

INSERT INTO PFC (puissanceId, catId, respCivile)
VALUES
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C1')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '1')
    , 201657)
,
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '1')
    , 244399) 
,
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '1')
    , 285114) 
;
-- PFC, cas de la catégorie 2

INSERT INTO PFC (puissanceId, catId, respCivile)
VALUES
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '2')
    , 401383)
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '2')
    , 563124)
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '2')
    , 583830)
;
SELECT unite, borneInf, borneSup 
     , LEFT(catLibelle,12) as cat
     , respCivile
FROM   PFC AS x 
  JOIN PUISSANCE AS y ON x.puissanceId = y.puissanceId
  JOIN CATEGORIE AS z ON x.catId = z.catId 
ORDER BY cat, unite, borneInf
;

=>

unite        borneInf    borneSup    cat            respCivile

CV           0           2           Catégorie 1    201657
CV           3           6           Catégorie 1    244399
CV           7           10          Catégorie 1    285114
CV           3           6           Catégorie 2    401383
CV           7           10          Catégorie 2    563124
CV           11          14          Catégorie 2    583830

Les PFC_SC. Les PFC_SC sont absentes dans le post #55 et reviennent donc (cf. post #24). La relation avec PFC n’a plus lieu d’être, par contre la relation avec PUISSANCE devient nécessaire. A cause des remorques, la catégorie 3 fait l’objet de deux sous-catégories.

CREATE TABLE PFC_SC
(
   puissanceId INT,
   catId INT,
   sousCatId INT NOT NULL,
   respCivile INT NOT NULL,
   CONSTRAINT PFC_SC_PK PRIMARY KEY(catId, sousCatId, puissanceId),
   CONSTRAINT PFC_SC_SOUS_CATEGORIE_FK FOREIGN KEY(catId, sousCatId) 
       REFERENCES SOUS_CATEGORIE(catId, sousCatId),
  CONSTRAINT PFC_SC_PUISSANCE_FK FOREIGN KEY(puissanceId) 
       REFERENCES PUISSANCE(puissanceId)
);

-- PFC_SC, cas de la catégorie 3

iNSERT INTO PFC_SC (puissanceId, catId, sousCatId, respCivile)
VALUES
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'T1')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '3')
   , (SELECT sousCatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '3-1')
   , 578610)
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'T2')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '3')
   , (SELECT sousCatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '3-1')
   , 694332)
,
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'T3')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '3')
    , (SELECT sousCatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '3-1')
    , 921888) 
,
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'R1')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '3')
    , (SELECT sousCatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '3-2')
    , 1000000) 
;

-- PFC_SC, cas de la catégorie 4

iNSERT INTO PFC_SC (puissanceId, catId, sousCatId, respCivile)
VALUES
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
   , (SELECT sousCatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '4-1')
   , 690102)
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
    , (SELECT sousCatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '4-1')
    , 728486) 
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
    , (SELECT sousCatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '4-1')
    , 806813) 
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C5')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
    , (SELECT sousCatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '4-1')
    , 846877) 
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
    , (SELECT sousCatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '4-2')
    , 784239) 
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
    , (SELECT sousCatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '4-2')
    , 822563) 
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
    , (SELECT sousCatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '4-2')
    , 900947) 
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C5')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
    , (SELECT sousCatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '4-2')
    , 941015) 
; 

SELECT unite, borneInf, borneSup 
     , LEFT(catLibelle,12) as cat, RIGHT(sousCatLibelle,24) as sousCat
     , respCivile
FROM   PFC_SC AS x 
  JOIN PUISSANCE AS y ON x.puissanceId = y.puissanceId
  JOIN CATEGORIE AS z ON x.catId = z.catId
  JOIN SOUS_CATEGORIE AS t ON x.catId = t.catId and x.sousCatId = t.sousCatId 
ORDER BY cat, sousCat, unite, borneInf 
;

=>

unite        borneInf    borneSup    cat           sousCat                  respCivile

tonne        0           1           Catégorie 3    marchandises (tonnage)   578610
tonne        2           5           Catégorie 3    marchandises (tonnage)   694332
tonne        6           10          Catégorie 3    marchandises (tonnage)   921888
remorque     0           0           Catégorie 3    remorque                 1000000
CV           3           6           Catégorie 4    taxis 4 places           690102
CV           7           10          Catégorie 4    taxis 4 places           728486
CV           11          14          Catégorie 4    taxis 4 places           806813
CV           15          23          Catégorie 4    taxis 4 places           846877
CV           3           6           Catégorie 4    taxis 5 places           784239
CV           7           10          Catégorie 4    taxis 5 places           822563
CV           11          14          Catégorie 4    taxis 5 places           900947
CV           15          23          Catégorie 4    taxis 5 places           941015

L’ensemble des responsabilités civiles, toutes catégories :

SELECT unite, borneInf, borneSup 
     , LEFT(catLibelle,12) as cat, '/' as sousCat
     , respCivile
FROM   PFC AS x 
  JOIN PUISSANCE AS y ON x.puissanceId = y.puissanceId
  JOIN CATEGORIE AS z ON x.catId = z.catId 
UNION   
SELECT unite, borneInf, borneSup 
     , LEFT(catLibelle,12) as cat, RIGHT(sousCatLibelle,24) as sousCat
     , respCivile
FROM   PFC_SC AS x 
  JOIN PUISSANCE AS y ON x.puissanceId = y.puissanceId
  JOIN CATEGORIE AS z ON x.catId = z.catId
  JOIN SOUS_CATEGORIE AS t ON x.catId = t.catId and x.sousCatId = t.sousCatId 
ORDER BY cat, sousCat, unite, borneInf

=>

unite        borneInf    borneSup    cat            sousCat                  respCivile

CV           0           2           Catégorie 1    /                        201657
CV           3           6           Catégorie 1    /                        244399
CV           7           10          Catégorie 1    /                        285114
CV           3           6           Catégorie 2    /                        401383
CV           7           10          Catégorie 2    /                        563124
CV           11          14          Catégorie 2    /                        583830
tonne        0           1           Catégorie 3    marchandises (tonnage)   578610
tonne        2           5           Catégorie 3    marchandises (tonnage)   694332
tonne        6           10          Catégorie 3    marchandises (tonnage)   921888
remorque     0           0           Catégorie 3    remorque                 1000000
CV           3           6           Catégorie 4    taxis 4 places           690102
CV           7           10          Catégorie 4    taxis 4 places           728486
CV           11          14          Catégorie 4    taxis 4 places           806813
CV           15          23          Catégorie 4    taxis 4 places           846877
CV           3           6           Catégorie 4    taxis 5 places           784239
CV           7           10          Catégorie 4    taxis 5 places           822563
CV           11          14          Catégorie 4    taxis 5 places           900947
CV           15          23          Catégorie 4    taxis 5 places           941015

Conclusion : qu’on utilise le MCD du post #24 (en le modifiant) ou celui du post #55, la responsabilité civile ne pose pas de problème. A vous de choisir.

Du fait de PFC_SC, il y a quand même au moins un effet secondaire portant sur les véhicules. En effet, selon qu’un véhicule relève d’une catégorie sans sous-catégorie ou d’une catégorie avec des sous-catégories, l’entité-type VEHICULE est associée soit à PFC, soit à PFC_SC. Dans ces conditions, il faut spécialiser les véhicules selon la catégorie dont ils relèvent. Techniquement, on spécialise VEHICULE en VEHICULE_CAT et VEHICULE_SCAT et on branche soit sur PFC soit sur PFC_SC :

Nom : Zidane7_assurance_auto (3)PFC_SC_vehicule.png
Affichages : 199
Taille : 23,5 Ko

CREATE TABLE VEHICULE
(
   vehiculeId INT,
   vehiculeSerie VARCHAR(48) NOT NULL,
   vehiculePuissance INT NOT NULL,
   CONSTRAINT VEHICULE_PK PRIMARY KEY(vehiculeId)
);

CREATE TABLE VEHICULE_CAT
(
   vehiculeId INT,
   catId INT NOT NULL,
   puissanceId INT NOT NULL,
   CONSTRAINT VEHICULE_CAT_PK PRIMARY KEY(vehiculeId),
   CONSTRAINT VEHICULE_CAT_VEHICULE_FK FOREIGN KEY(vehiculeId) 
       REFERENCES VEHICULE(vehiculeId)
       ON DELETE CASCADE,
   CONSTRAINT VEHICULE_CAT_PFC_FK FOREIGN KEY(catId, puissanceId) 
       REFERENCES PFC(catId, puissanceId)
);

CREATE TABLE VEHICULE_SCAT
(
   vehiculeId INT,
   catId INT NOT NULL,
   sousCatId INT NOT NULL,
   puissanceId INT NOT NULL,
   CONSTRAINT VEHICULE_SCAT_PK PRIMARY KEY(vehiculeId),
   CONSTRAINT VEHICULE_SCAT_VEHICULE_FK FOREIGN KEY(vehiculeId) 
       REFERENCES VEHICULE(vehiculeId)
       ON DELETE CASCADE,
   CONSTRAINT VEHICULE_SCAT_PFC_SC_FK FOREIGN KEY(catId, sousCatId, puissanceId) 
       REFERENCES PFC_SC(catId, sousCatId, puissanceId)
);

Je note qu’initialement, disons avant le post #54, vous avez identifié SOUS_CATEGORIE relativement à CATEGORIE, puis de manière absolue. Il faudrait revenir à la situation initiale pour connaître immédiatement CatId dans PFC_SC.

**Zidane7** · 02/11/2020, 17h49

Bonsoir Monsieur fsmrel.

Bonjour Zidane7,

Vos tableaux des catégories et sous-catégories (post #54) sont éclairants.

Comme précédemment, la responsabilité civile et la défense recours dépendent soit de la catégorie (cas des catégories 1 et 2), soit de la sous-catégorie (cas des catégories 3 et 4). A la réflexion, le plus simple du point de vue de la modélisation (puis de la mise en œuvre SQL) est que chaque catégorie possède au moins une sous-catégorie (ce qui vaut donc automatiquement pour les catégories 1 et 2, même si fonctionnellement ça n’est pas le cas), ce qui permet aussi de n’avoir qu’un seul endroit où représenter la sécurité routière et la défense recours, c’est-à-dire dans l’entité-type SOUS_CATEGORIE.

Nom : POSTE55.png
Affichages : 200
Taille : 20,0 Ko

Avec cette présentation du poste #55.

Merci Monsieur par rapport à votre explication sur cette partie ci-dessus.
Si je comprend bien vous avez pris par exemple la catégorie 1 avec sous-catégorie 510 qui appartient à une et seule catégorie. Mais deux sous-catégories peuvent avoir même numéro mais de nom différent ? Par exemple la sous-Catégorie 540 : Taxis Urbains et Inter Urbains - Taxis 4 places, sous-Catégorie 540 : Taxis Urbains et Inter Urbains - Taxis 5 places, sous-Catégorie 540 : Taxis Urbains et Inter Urbains - Taxis 6 Places qui sont toutes de la catégorie 4?, etc....
Et avec votre schéma : [PFC]----1,1---(PFC_SC)---1,N----[SOUS_CATEGORIE] une Responsabilité civile appartient à une et une seule sous-catégories.
Que pensez-vous?
Merci par avance.

**fsmrel** · 03/11/2020, 17h28

Bonsoir Zidane7,

Envoyé par Zidane7

Si je comprend bien vous avez pris par exemple la catégorie 1 avec sous-catégorie 510 qui appartient à une et seule catégorie.

Récapitulons.

Concernant le post #55, je suis parti du post #54, dans lequel les tableaux fournissent la valeur de la responsabilité civile en fonction de la puissance fiscale.

Dans ce post #55, j’ai fourni un MCD alternatif à celui du post #24 (puis en dernier lieu une variante de ce dernier, cf. post #66).

Vous revenez sur le MCD du post #55 : d’accord.

Vous observerez que pour la catégorie 1, j’ai effectivement une et une seule sous-catégorie, mais il s’agit d’une pseudo sous-catégorie, strictement technique, puisque fonctionnellement la catégorie1 n’a pas de sous-catégorie (par référence au tableau correspondant, post #54).

En reprenant le code SQL du post #55 (sécurité routière et coût de la police en moins) :

INSERT INTO SOUS_CATEGORIE (catId, sousCatCode, sousCatLibelle)
VALUES
    ((SELECT catId FROM CATEGORIE WHERE catCode = '1'), '1-1' ' /')
;

SELECT catCode, sousCatCode, sousCatLibelle
FROM   CATEGORIE as x JOIN SOUS_CATEGORIE as y ON x.catId = y.catId   
;

=>

catCode   sousCatCode   sousCatLibelle

1         1-1          /

Observez que dans le résultat qui précède, il n’y a pas de sous-catégorie '510', mais une pseudo sous-catégorie pour laquelle l’attribut sousCatCode a pris la valeur '1-1' et l’attribut sousCatLibelle a pris la valeur '/'. Votre valeur '510' et ma valeur '1-1' sont-elles synonymes ? Si oui, pas de problème, dans mes jeux d'essai je remplacerai '1-1' par '510' pour clarifier et éviter les quiproquos.

Envoyé par Zidane7

Mais deux sous-catégories peuvent avoir même numéro mais de nom différent ?

Qu’entendez-vous précisément par numéro de sous-catégorie ? S’agit-il en fait du code sous-catégorie ayant fait dans mes MCD l’objet de l’attribut sousCatCode dans l’entité-type SOUS_CATEGORIE ? Quoi qu’il en soit, je fais observer que l’attribut sousCatCode est identifiant alternatif de l’entité-type SOUS_CATEGORIE (contrainte UNIQUE dans le CREATE TABLE), donc si “numéro de sous-catégorie” est synonyme de “code sous-catégorie”, alors deux sous-catégories ne peuvent pas avoir le même numéro.

Envoyé par Zidane7

Par exemple la sous-Catégorie 540 : Taxis Urbains et Inter Urbains - Taxis 4 places, sous-Catégorie 540 : Taxis Urbains et Inter Urbains - Taxis 5 places, sous-Catégorie 540 : Taxis Urbains et Inter Urbains - Taxis 6 Places qui sont toutes de la catégorie 4?

Revenons sur ce que j’ai codé dans le post #55 (une fois de plus sécurité routière et coût de la police en moins) :

INSERT INTO SOUS_CATEGORIE (catId, sousCatCode, sousCatLibelle)
VALUES
    ((SELECT catId FROM CATEGORIE WHERE catCode = '1'), '1-1', ' /')
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '2'), '2-1', ' /')
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '3'), '3-1', 'marchandises (tonnage)')
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '3'), '3-2', 'remorque')
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '4'), '4-1', 'taxis 4 places')
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '4'), '4-2', 'taxis 5 places')
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '4'), '4-3', 'taxis 6 places')
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '4'), '4-5', 'TPV 18 places')
;

Pour voir le résultat :

SELECT catCode, sousCatCode, sousCatLibelle
FROM   CATEGORIE as x JOIN SOUS_CATEGORIE as y ON x.catId = y.catId   
;

=>

catCode   sousCatCode   sousCatLibelle

1         1-1          /
2         2-1          /
3         3-1          marchandises (tonnage)
3         3-2          marchandises (remorque)
4         4-1          taxis 4 places
4         4-2          taxis 5 places
4         4-3          taxis 6 places
4         4-5          TPV 18 places

Quand vous écrivez :

« sous-Catégorie 540 : Taxis Urbains et Inter Urbains - Taxis 4 places, sous-Catégorie 540 : Taxis Urbains et Inter Urbains - Taxis 5 places, sous-Catégorie 540 : Taxis Urbains et Inter Urbains - Taxis 6 Places qui sont toutes de la catégorie 4 »

Alors si je comprends bien, '540' est bien une sous-catégorie de la catégorie 4, mais affectée aux Taxis Urbains et Inter Urbains, indépendamment du nombre de places. Autrement dit, la sous-catégorie se déclinerait en sous-sous-catégories, permettant d’associer le nombre de places à la puissance pour la détermination de la responsabilité civile (entité-type PFC).

Cela commence à devenir un peu scabreux, dans la mesure où (toujours en référence au post #55) on ajoute un niveau, chaque catégorie comportant au moins une sous-catégorie et chaque sous-catégorie comportant au moins une sous-sous-catégorie...

Mais cela est jouable. Votre position ?

**Zidane7** · 04/11/2020, 13h21

Bonjour Monsieur fsmrel,

Quand vous écrivez :

« sous-Catégorie 540 : Taxis Urbains et Inter Urbains - Taxis 4 places, sous-Catégorie 540 : Taxis Urbains et Inter Urbains - Taxis 5 places, sous-Catégorie 540 : Taxis Urbains et Inter Urbains - Taxis 6 Places qui sont toutes de la catégorie 4 »

Alors si je comprends bien, '540' est bien une sous-catégorie de la catégorie 4, mais affectée aux Taxis Urbains et Inter Urbains, indépendamment du nombre de places. Autrement dit, la sous-catégorie se déclinerait en sous-sous-catégories, permettant d’associer le nombre de places à la puissance pour la détermination de la responsabilité civile (entité-type PFC).

Cela commence à devenir un peu scabreux, dans la mesure où (toujours en référence au post #55) on ajoute un niveau, chaque catégorie comportant au moins une sous-catégorie et chaque sous-catégorie comportant au moins une sous-sous-catégorie...

Mais cela est jouable. Votre position ?

Voici comment le problème se situe :
1 Pour déterminer la Responsabilité Civile(RC) partout on se base sur deux critères à savoir :
a) La catégorie
b) La puissance qui est en intervalle (que vous avez déjà réglé)
2 Deux catégories fond exception à savoir :
a) La catégorie 3
Ici c’est quand il s’agit d’un produit ‘’inflammable’’ ou ‘’dangereux’’ la RC de base est augmenté de 25%.
b) La catégorie 4 :
Ici la Responsabilité se détermine non seulement à travers :
1) La catégorie
2) La puissance qui est en intervalle (que vous avez déjà réglé)
3) Et aussi le nombre de place (qui est aussi pris en compte)
Raison pour laquelle j'avais ajouté la sous catégorie.
Que pensez-vous?

Merci par avance.

**fsmrel** · 04/11/2020, 19h10

Bonsoir Zidane7,

Envoyé par Zidane7

1 Pour déterminer la Responsabilité Civile(RC) partout on se base sur deux critères à savoir :
a) La catégorie
b) La puissance qui est en intervalle (que vous avez déjà réglé)

2 Deux catégories fond exception à savoir :

a) La catégorie 3
[...]
b) La catégorie 4

Pour faire un point :

C’est à cause de ces deux exceptions dont vous faites mention que dans le post #17 j’avais proposé un MCD permettant d’associer la responsabilité civile (entité-type PFC) soit à l’entité-type CATEGORIE soit à l’entité-type SOUS_CATEGORIE, via l’entité-type CAT_SCAT. Il s’agit ici de la modélisation par spécialisation/généralisation, technique développée dans l’ouvrage de D. Nanci (RIP) et B. Espinasse, ouvrage référencé dans ce même post #17.

Comme la modélisation par spécialisation/généralisation n’a pas emporté votre adhésion, dans le post #24 je vous ai proposé la mise en oeuvre de l’entité-type PFC_SC, permettant elle aussi de prendre en compte les sous-catégories pour le calcul de la responsabilité civile. Petit inconvénient : on va de PFC_SC à CATEGORIE par deux chemins différents (soit via les associations PFC_SC_P > PFC_C, soit via les associations PFC_SC_S > C_SC) et ceci est peccamineux, mais se résout par intervention manuelle au niveau SQL comme je l’ai précisé dans le post #24.

Dans le post #55 j’ai proposé un 3e MCD selon lequel toute catégorie a au moins une sous-catégorie, certes factice pour les catégories 1 et 2 puisque fonctionnellement elles n’ont pas de sous-catégories, mais cela ne perturbe pas notre système.

Envoyé par Zidane7

b) La catégorie 4 :
Ici la Responsabilité se détermine non seulement à travers :
1) La catégorie
2) La puissance qui est en intervalle (que vous avez déjà réglé)
3) Le nombre de place (qui est aussi pris en compte).

Vous prenez le nombre de places en compte, mais au niveau de la catégorie (attribut nombreplacecategorie dans l’entité-type CATEGORIE), et c’est une erreur. En effet, cet attribut doit migrer dans l’entité-type SOUS_CATEGORIE pour que les calculs soient possibles. Dans ces conditions, plus besoin des sous-sous-catégories envisagées dans le post #68, ouf...

Vérification au moyen de SQL.

CREATE TABLE SOUS_CATEGORIE
(
   catId INT,
   sousCatId          INT IDENTITY,
   sousCatCode        VARCHAR(4) NOT NULL,
   nbPlaces           INT NOT NULL,  
   securiteRoutiere   INT NOT NULL,  
   coutPolice         INT NOT NULL,
   sousCatLibelle     VARCHAR(96) NOT NULL,
   CONSTRAINT SOUS_CATEGORIE_PK PRIMARY KEY(catId, sousCatId),
   CONSTRAINT SOUS_CATEGORIE_AK UNIQUE(sousCatCode, nbPlaces),
   CONSTRAINT SOUS_CATEGORIE_CATEGORIE_FK FOREIGN KEY(catId) 
       REFERENCES CATEGORIE(catId)
);

Notez la présence donc de la colonne nbPlaces (et son introduction dans la composition de la clé alternative {sousCatCode, nbPlaces}, cf. la contrainte SOUS_CATEGORIE_AK.

Mon propre jeu d’essai prend l’allure suivante :

INSERT INTO SOUS_CATEGORIE (catId, sousCatCode, nbPlaces, securiteRoutiere, coutPolice, sousCatLibelle)
VALUES
    ((SELECT catId FROM CATEGORIE WHERE catCode = '1'), '510', 0, 17500, 10000, ' /')
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '2'), '520', 0, 10500, 10000, ' /')
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '3'), '531', 0, 10500, 10000, 'marchandises (tonnage)')
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '3'), '532', 0, 00000, 00000, 'marchandises (remorque)')
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '4'), '540', 4,  00000, 10000, 'taxis')
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '4'), '540', 5, 00000, 10000, 'taxis')
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '4'), '540', 6, 00000, 10000, 'taxis')
  , ((SELECT catId FROM CATEGORIE WHERE catCode = '4'), '540', 18, 00000, 10000, 'TPV')
;

Pour la table PFC (débarrassée des montants autres que la responsabilité civile) :

CREATE TABLE PFC
(
   puissanceId     INT,
   catId           INT,
   sousCatId       INT,
   respCivile      DECIMAL(9) NOT NULL,
   CONSTRAINT PFC_PK PRIMARY KEY(puissanceId, catId, sousCatId),
   CONSTRAINT PFC_SOUS_CATEGORIE_FK FOREIGN KEY(catId, sousCatId) 
       REFERENCES SOUS_CATEGORIE(catId, sousCatId)
);

Les inserts dans PFC :

-- inserts - cas de la catégorie 1 

INSERT INTO PFC (puissanceId, catId, souscatId, respCivile)
VALUES 
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C1')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '1')
    , (SELECT souscatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '510')
    , 201657)
 
,
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '1')
    , (SELECT souscatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '510')
    , 244399) 
,
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '1')
    , (SELECT souscatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '510')
    , 285114) 
,
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '1')
    , (SELECT souscatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '510')
    , 346233) 
,
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C5')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '1')
    , (SELECT souscatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '510')
    , 407332) 
,
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C6')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '1')
    , (SELECT souscatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '510')
    , 427698) 
;

-- cas de la catégorie 2 

INSERT INTO PFC (puissanceId, catId, souscatId, respCivile)
VALUES 
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '2')
    , (SELECT souscatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '520')
    , 401383) 
,
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '2')
    , (SELECT souscatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '520')
    , 563124) 
,
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '2')
    , (SELECT souscatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '520')
    , 583830) 
,
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C5')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '2')
    , (SELECT souscatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '520')
    , 693298) 
,
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C6')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '2')
    , (SELECT souscatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '520')
    , 716102) 
;

-- cas de la catégorie 3 

INSERT INTO PFC (puissanceId, catId, souscatId, respCivile)
VALUES 
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'T1')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '3')
    , (SELECT souscatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '531')
    , 578610) 
,
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'T2')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '3')
    , (SELECT souscatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '531')
    , 694332) 
,
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'T3')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '3')
    , (SELECT souscatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '531')
    , 921888) 
, 
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'T4')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '3')
    , (SELECT souscatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '531')
    , 1383054) 
,
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'T5')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '3')
    , (SELECT souscatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '531')
    , 1843776) 
,
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'T6')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '3')
    , (SELECT souscatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '531')
    , 2764164) 
,
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'R1')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '3')
    , (SELECT souscatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '532')
    , 1000000) 
;

-- cas de la catégorie 4 (partiel)

INSERT INTO PFC (puissanceId, catId, souscatId, respCivile)
VALUES 
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
    , (SELECT souscatId FROM SOUS_CATEGORIE WHERE sousCatCode = '540' AND nbPlaces = 4)
    , 690102) 
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
    , (SELECT souscatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '540' AND nbPlaces = 4)
    , 728486) 
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
    , (SELECT souscatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '540' AND nbPlaces = 4)
    , 806813) 
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C5')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
    , (SELECT souscatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '540' AND nbPlaces = 4)
    , 846877) 
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
    , (SELECT souscatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '540' AND nbPlaces = 5)
    , 784239) 
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
    , (SELECT souscatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '540' AND nbPlaces = 5)
    , 822563) 
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
    , (SELECT souscatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '540' AND nbPlaces = 5)
    , 900947) 
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C5')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
    , (SELECT souscatId FROM SOUS_CATEGORIE WHERE sousCatCode  = '540' AND nbPlaces = 5)
    , 941015) 
;

Au résultat :

SELECT unite, borneInf, borneSup
     , LEFT(catLibelle,12) as cat
     , CASE
           WHEN catCode = 3 THEN
               RIGHT(sousCatLibelle, 24) 
           WHEN catCode = 4 THEN
               CONCAT (RIGHT(sousCatLibelle, 18), ' ', CAST(nbPlaces as VARCHAR), ' places')
           ELSE ''
       END
          as sousCat
     , respCivile
FROM   PFC AS x 
  JOIN PUISSANCE AS y ON x.puissanceId = y.puissanceId
  JOIN CATEGORIE AS z ON x.catId = z.catId
  JOIN SOUS_CATEGORIE AS t ON x.catId = t.catId AND x.souscatId = t.souscatId
ORDER BY cat, sousCat, unite, borneInf    
;

=>

unite        borneInf  borneSup  cat          sousCat                 respCivile

CV           0         2         Catégorie 1                          201657
CV           3         6         Catégorie 1                          244399
CV           7         10        Catégorie 1                          285114
CV           11        14        Catégorie 1                          346233
CV           15        23        Catégorie 1                          407332
CV           24        9999      Catégorie 1                           427698
CV           3         6         Catégorie 2                          401383
CV           7         10        Catégorie 2                          563124
CV           11        14        Catégorie 2                          583830
CV           15        23        Catégorie 2                          693298
CV           24        9999      Catégorie 2                          716102
remorque     0         0         Catégorie 3  marchandises (remorque) 1000000
tonne        0         1         Catégorie 3  marchandises (tonnage)  578610
tonne        2         5         Catégorie 3  marchandises (tonnage)  694332
tonne        6         10        Catégorie 3  marchandises (tonnage)  921888
tonne        11        15        Catégorie 3  marchandises (tonnage)  1383054
tonne        16        25        Catégorie 3  marchandises (tonnage)  1843776
tonne        26        9999      Catégorie 3  marchandises (tonnage)  2764164
CV           3         6         Catégorie 4  taxis 4 places          690102
CV           7         10        Catégorie 4  taxis 4 places          728486
CV           11        14        Catégorie 4  taxis 4 places          806813
CV           15        23        Catégorie 4  taxis 4 places          846877
CV           3         6         Catégorie 4  taxis 5 places          784239
CV           7         10        Catégorie 4  taxis 5 places          822563
CV           11        14        Catégorie 4  taxis 5 places          900947
CV           15        23        Catégorie 4  taxis 5 places          941015

Votre position ? Etes-vous d’accord avec cette migration de l’attribut nbPlaces vers SOUS_CATEGORIE ?

**Zidane7** · 11/11/2020, 10h55

Bonjour Monsieur fsmrel,
D'après mes analyses, j'ai jugé nécessaire de supprimer l'entité Sous-catégorie et maintenir seulement l'entité Catégorie. Pour cela, on ajoutera un champ libellé catégorie dans l'entité PFC pour pouvoir mettre toutes les références de toutes les catégories. Ensuite ajouter les champs défense recours(obligatoire pour toutes les catégories sauf les remorques), coût de police(obligatoire pour toutes les catégories sauf la catégorie4 et 5) et CEDEAO(obligatoire pour toutes les catégories).
Que pensez-vous?
Merci par avance.

**Zidane7** · 13/11/2020, 17h49

Bonsoir Monsieur fsmrel,
D'après ce que j'ai écrit dans le poste #72 , voici le code sql ci-dessous:

CREATE TABLE CATEGORIE(
categorieId INT IDENTITY,
codecategorie CHAR(4) NOT NULL,
libelleCategorie VARCHAR(50) NOT NULL,
nombreplacecategorie INT 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(2) NOT NULL,
Libelle_Garantie VARCHAR(50) NOT NULL,
PRIMARY KEY(garantieId),
UNIQUE(codegarantie)
);

CREATE TABLE Sous_Garantie(
sousGarantieId INT IDENTITY,
codesousgarantie CHAR(2) NOT NULL,
libelleSousGarantieId VARCHAR(50) NOT NULL,
categorieId INT NOT NULL,
garantieId INT NOT NULL,
PRIMARY KEY(sousGarantieId),
UNIQUE(codesousgarantie),
FOREIGN KEY(categorieId) REFERENCES CATEGORIE(categorieId),
FOREIGN KEY(garantieId) REFERENCES GARANTIE(garantieId)
);

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 TARIF(
categorieId INT,
puissanceId INT,
tarifId INT,
datetarifdepuis DATE NOT NULL,
responsabilitecivile VARCHAR(50) NOT NULL,
defenserecours INT,
coutpolice INT,
cedeao INT NOT NULL,
PRIMARY KEY(categorieId, puissanceId, tarifId),
FOREIGN KEY(categorieId) REFERENCES CATEGORIE(categorieId),
FOREIGN KEY(puissanceId) REFERENCES PUISSANCE(puissanceId)
);

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 TARIFHISTO(
categorieId INT,
puissanceId INT,
tarifId INT,
tarifhistoId INT,
tarifhistodurantdebut DATE NOT NULL,
tarifhistodurantfin DATE NOT NULL,
responsabilitecivilehisto INT NOT NULL,
defenserecourshisto INT NOT NULL,
cedeaohisto INT NOT NULL,
PRIMARY KEY(categorieId, puissanceId, tarifId, tarifhistoId),
FOREIGN KEY(categorieId, puissanceId, tarifId) REFERENCES TARIF(categorieId, puissanceId, tarifId)
);

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 CONTRAT(
clientId INT,
contratId INT IDENTITY,
codecontrat VARCHAR(10) NOT NULL,
dateEffetContrat DATE NOT NULL,
dateExpirationContrat DATE NOT NULL,
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 INT,
contratId INT,
avenantId INT IDENTITY,
codeavenant VARCHAR(10) NOT NULL,
libelleAvenant VARCHAR(50) NOT NULL,
dateEffetAvenant DATE NOT NULL,
dateExpirationAvenant DATE NOT NULL,
avenantlibelleId INT NOT NULL,
apporteurId INT,
PRIMARY KEY(clientId, contratId, avenantId),
UNIQUE(codeavenant),
FOREIGN KEY(clientId, contratId) REFERENCES CONTRAT(clientId, contratId),
FOREIGN KEY(avenantlibelleId) REFERENCES AVENANT_LIBELLE(avenantlibelleId),
FOREIGN KEY(apporteurId) REFERENCES APPORTEUR(apporteurId)
);

CREATE TABLE VEHICULE(
clientId INT,
contratId INT,
vehiculeId INT IDENTITY,
codevehicule CHAR(5) NOT NULL,
marque VARCHAR(50) 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,
clientId_1 INT NOT NULL,
categorieId INT NOT NULL,
puissanceId INT NOT NULL,
tarifId INT NOT NULL,
PRIMARY KEY(clientId, contratId, vehiculeId),
UNIQUE(codevehicule),
FOREIGN KEY(clientId, contratId) REFERENCES CONTRAT(clientId, contratId),
FOREIGN KEY(clientId_1) REFERENCES CLIENT(clientId),
FOREIGN KEY(categorieId, puissanceId, tarifId) REFERENCES TARIF(categorieId, puissanceId, tarifId)
);

CREATE TABLE PROTEGER(
categorieId INT,
garantieId INT,
PRIMARY KEY(categorieId, garantieId),
FOREIGN KEY(categorieId) REFERENCES CATEGORIE(categorieId),
FOREIGN KEY(garantieId) REFERENCES GARANTIE(garantieId)
);

CREATE TABLE ASSOCIER(
clientId INT,
contratId INT,
garantieId INT,
PRIMARY KEY(clientId, contratId, garantieId),
FOREIGN KEY(clientId, contratId) REFERENCES CONTRAT(clientId, contratId),
FOREIGN KEY(garantieId) REFERENCES GARANTIE(garantieId)
);

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, contratId, avenantId),
FOREIGN KEY(agenceId, caissiereId) REFERENCES CAISSIERE(agenceId, caissiereId),
FOREIGN KEY(modepaiemenId) REFERENCES MODEPAIEMENT(modepaiemenId)
);

CREATE TABLE CLI_AV(
clientId INT,
clientId_1 INT,
contratId INT,
avenantId INT,
dateemissioncliav DATE NOT NULL,
PRIMARY KEY(clientId, clientId_1, contratId, avenantId),
FOREIGN KEY(clientId) REFERENCES CLIENT(clientId),
FOREIGN KEY(clientId_1, contratId, avenantId) REFERENCES AVENANT(clientId, contratId, avenantId)
);

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

Voici le MCD ci-dessous:
Avez-vous des remarques et suggestion?
Merci par avance.

**fsmrel** · 14/11/2020, 03h47

Bonsoir Zidane7,

Je suis bien chargé ces jours-ci, mais je ne vous oublie évidemment pas et dès que possible je regarderai la faisabilité de votre dernier MCD.

Courage !

**fsmrel** · 14/11/2020, 18h53

Bonjour Zidane7,

On peut effectivement se passer des sous-catégories.

Tout d’abord, une remarque importante : dans votre MCD l’entité-type CATEGORIE contient un attribut nombreplacecategorie : je suppose qu’il est censé jouer un rôle dans le cas de la catégorie 4, pour distinguer les taxis 4 places, 5 places, etc. Malheureusement, selon votre modélisation, pour une catégorie donnée il y a au plus une valeur pour l’attribut nombreplacecategorie, par exemple la valeur 4 ou la valeur 5, etc., mais de façon exclusive. Autrement dit cet attribut ne convient pas et ne peut que disparaître...

En remplacement des sous-catégories, je propose la mise en oeuvre d’une entité-type CAT_COMPLEMENT dont l’objet est de fournir les compléments des libellés pour les catégories qui en ont besoin :

Pour la catégorie 3 :

Marchandises (tonnage)
Remorque

Pour la catégorie 4 :

Taxis urbains et inter-urbains - taxis 4 places
Taxis urbains et inter-urbains - taxis 5 places
Taxis urbains et inter-urbains - taxis 6 places
Transport public de voyageurs - TPV 18 places

Le MCD prend l’allure suivante :

Nom : Zidane7_assurance_auto (5)cat_complement.png
Affichages : 163
Taille : 17,2 Ko

L’entité-type TARIF est sémantiquement et par essence une association entre PUISSANCE, CATEGORIE et CAT_COMPLEMENT. On en a fait une entité-type pour pouvoir l’associer avec VEHICULE.

Un jeu d’essai :

Les puissances :

CREATE TABLE PUISSANCE
(
   puissanceId     INT IDENTITY,
   puissanceCode   VARCHAR(8)  NOT NULL,
   borneInf        SMALLINT NOT NULL,
   borneSup        SMALLINT NOT NULL,
   unite           VARCHAR(12) NOT NULL,
   CONSTRAINT PUISSANCE_PK PRIMARY KEY(puissanceId),
   CONSTRAINT PUISSANCE_AK UNIQUE(puissanceCode),
   CONSTRAINT PUISSANCE_UK UNIQUE(borneInf, unite)
);

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') 
;
SELECT * FROM PUISSANCE ;

=>

puissanceCode   borneInf   borneSup   unite

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

Les catégories :

CREATE TABLE CATEGORIE
(
   catId        INT IDENTITY,
   catCode      VARCHAR(4) NOT NULL,
   catLibelle   VARCHAR(64) NOT NULL,
   CONSTRAINT CATEGORIE_PK PRIMARY KEY(catId),
   CONSTRAINT CATEGORIE_AK UNIQUE(catCode)
);

INSERT INTO CATEGORIE (catCode, catLibelle)
VALUES
    ('1', 'Catégorie 1 : Affaires et promenades (AP)')
  , ('2', 'Catégorie 2 : Transport pour le compte de l''assuré (TPC)')
  , ('3', 'Catégorie 3 : Transport public de marchandises (TPM)')
  , ('4', 'Catégorie 4 :')
  , ('5', 'Catégorie 5 : Motos')
;
SELECT catCode, catLibelle FROM CATEGORIE ;

=>

catCode   catLibelle

1         Catégorie 1 : Affaires et promenades (AP)
2         Catégorie 2 : Transport pour le compte de l'assuré (TPC)
3         Catégorie 3 : Transport public de marchandises (TPM)
4         Catégorie 4 :
5         Catégorie 5 : Motos

Les libellés complémentaires :

CREATE TABLE CAT_COMPLEMENT
(
   catComplementId INT IDENTITY,
   catComplementCode SMALLINT NOT NULL,
   catComplementLibelle VARCHAR(96) NOT NULL,
   CONSTRAINT CAT_COMPLEMENT_PK PRIMARY KEY(catComplementId),
   CONSTRAINT CAT_COMPLEMENT_AK UNIQUE(catComplementCode),
);

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

Les tarifs :

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

-- TARIF, échantillon de la catégorie 1

INSERT INTO TARIF (puissanceId, catId, catComplementId, respCivile)
VALUES
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C1')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '1')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 0)
    , 201657)
,
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '1')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 0)
    , 244399) 
,
     ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '1')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 0)
    , 285114) 
;

-- TARIF, échantillon de la catégorie 2

INSERT INTO TARIF (puissanceId, catId, catComplementId, respCivile)
VALUES
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '2')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 0)
    , 401383)
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '2')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 0)
    , 563124)
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '2')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 0)
   , 583830)
;

-- Echantillon de la catégorie 3

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

-- Echantillon de la catégorie 4

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

    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C5')
   , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 41)
   , 846877) 
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')
   , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 42)
   , 784239) 
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3')
   , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 42)
   , 822563) 
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4')
   , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 42)
   , 900947) 
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C5')
   , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 42)
   , 941015) 
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')
   , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 48)
   , 1097723) 
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3')
   , (SELECT catId FROM CATEGORIE WHERE catCode = '4')
   , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 48)
   , 1115468) 
;

-- Au résultat

SELECT unite, borneInf, borneSup 
     , LEFT(CONCAT(catLibelle , ' ', catComplementLibelle),80) as cat
     , 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.catComplementId = t.catComplementId 
ORDER BY cat, unite, borneInf ;

=>

unite        borneInf  borneSup   cat                                                                          respCivile

CV           0         2          Catégorie 1 : Affaires et promenades (AP)                                    201657
CV           3         6          Catégorie 1 : Affaires et promenades (AP)                                    244399
CV           7         10         Catégorie 1 : Affaires et promenades (AP)                                    285114
CV           3         6          Catégorie 2 : Transport pour le compte de l'assuré (TPC)                     401383
CV           7         10         Catégorie 2 : Transport pour le compte de l'assuré (TPC)                     563124
CV           11        14         Catégorie 2 : Transport pour le compte de l'assuré (TPC)                     583830
tonne        0         1          Catégorie 3 : Transport public de marchandises (TPM) Marchandises (tonnage)  578610
tonne        2         5          Catégorie 3 : Transport public de marchandises (TPM) Marchandises (tonnage)  694332
remorque     0         0          Catégorie 3 : Transport public de marchandises (TPM) Remorque                1000000
CV           3         6          Catégorie 4 : Taxis urbains et inter-urbains - Taxis 4 places                690102
CV           7         10         Catégorie 4 : Taxis urbains et inter-urbains - Taxis 4 places                728486
CV           11        14         Catégorie 4 : Taxis urbains et inter-urbains - Taxis 4 places                806813
CV           15        23         Catégorie 4 : Taxis urbains et inter-urbains - Taxis 4 places                846877
CV           3         6          Catégorie 4 : Taxis urbains et inter-urbains - Taxis 5 places                784239
CV           7         10         Catégorie 4 : Taxis urbains et inter-urbains - Taxis 5 places                822563
CV           11        14         Catégorie 4 : Taxis urbains et inter-urbains - Taxis 5 places                900947
CV           15        23         Catégorie 4 : Taxis urbains et inter-urbains - Taxis 5 places                941015
CV           3         6          Catégorie 4 : Transport public de voyageurs - TPV 18 places                  1097723
CV           7         10         Catégorie 4 : Transport public de voyageurs - TPV 18 places                  1115468

Conclusion : manifestement avec la mise en oeuvre de l’entité-type CAT_COMPLEMENT, on peut se passer des sous-catégories.

L’entité-type CAT_COMPLEMENT doit participer à l’identification de TARIF. En conséquence, cela vaut même pour les tarifs des catégories 1 et 2, mais ça n’est pas grave, leur libellé de complément est une chaîne vide, comme on le voit ci-dessus.

Est-ce que cela vous convient ?

N’hésitez pas à lever le pouce pour les différentes réponses où je tente des propositions avec code SQL à la clé, ne serait-ce que par égard au travail fourni...

**Zidane7** · 20/11/2020, 18h33

Bonsoir Monsieur fsmrel,

D'après mes analyses, je viens d'apporter d'autres modifications concernant la CEDEAO, la catégorie complémentaire.
Peut on lier la catégorie et la catégorie complémentaire?
Voici le MCD et le code SQL ci-dessous:

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(2) 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 CATCOMPLEMENT(
catcomplementId INT,
catcomplementcode CHAR(2) NOT NULL,
catcomplementlibelle VARCHAR(100) NOT NULL,
PRIMARY KEY(catcomplementId),
UNIQUE(catcomplementcode)
);

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

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 CEDEAO(
cedeaohistoId CHAR(2),
cedeaoId INT,
cedeaoepuis DATE NOT NULL,
cedeaovehicule INT NOT NULL,
PRIMARY KEY(cedeaohistoId, cedeaoId),
UNIQUE(cedeaovehicule),
FOREIGN KEY(cedeaohistoId) REFERENCES CEDEAOHISTO(cedeaohistoId)
);

CREATE TABLE CATEGORIE(
cedeaohistoId CHAR(2),
cedeaoId INT,
categorieId INT IDENTITY,
codecategorie CHAR(4) NOT NULL,
categorie VARCHAR(50) NOT NULL,
PRIMARY KEY(cedeaohistoId, cedeaoId, categorieId),
UNIQUE(codecategorie),
FOREIGN KEY(cedeaohistoId, cedeaoId) REFERENCES CEDEAO(cedeaohistoId, cedeaoId)
);

CREATE TABLE CONTRAT(
clientId INT,
contratId INT IDENTITY,
codecontrat VARCHAR(10) NOT NULL,
dateEffetContrat DATE NOT NULL,
dateExpirationContrat DATE NOT NULL,
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 INT,
contratId INT,
avenantId INT IDENTITY,
codeavenant VARCHAR(10) NOT NULL,
libelleAvenant VARCHAR(50) NOT NULL,
dateEffetAvenant DATE NOT NULL,
dateExpirationAvenant DATE NOT NULL,
avenantlibelleId INT NOT NULL,
apporteurId INT,
PRIMARY KEY(clientId, contratId, avenantId),
UNIQUE(codeavenant),
FOREIGN KEY(clientId, contratId) REFERENCES CONTRAT(clientId, contratId),
FOREIGN KEY(avenantlibelleId) REFERENCES AVENANT_LIBELLE(avenantlibelleId),
FOREIGN KEY(apporteurId) REFERENCES APPORTEUR(apporteurId)
);

CREATE TABLE Sous_Garantie(
sousGarantieId INT IDENTITY,
codesousgarantie CHAR(2) NOT NULL,
libelleSousGarantieId VARCHAR(50) NOT NULL,
cedeaohistoId CHAR(2) NOT NULL,
cedeaoId INT NOT NULL,
categorieId INT NOT NULL,
garantieId INT NOT NULL,
PRIMARY KEY(sousGarantieId),
UNIQUE(codesousgarantie),
FOREIGN KEY(cedeaohistoId, cedeaoId, categorieId) REFERENCES CATEGORIE(cedeaohistoId, cedeaoId, categorieId),
FOREIGN KEY(garantieId) REFERENCES GARANTIE(garantieId)
);

CREATE TABLE TARIF(
catcomplementId INT,
cedeaohistoId CHAR(2),
cedeaoId INT,
categorieId INT,
puissanceId INT,
tarifId INT,
datetarifdepuis DATE NOT NULL,
responsabilitecivile VARCHAR(50) NOT NULL,
defenserecours INT,
coutpolice INT,
PRIMARY KEY(catcomplementId, cedeaohistoId, cedeaoId, categorieId, puissanceId, tarifId),
FOREIGN KEY(catcomplementId) REFERENCES CATCOMPLEMENT(catcomplementId),
FOREIGN KEY(cedeaohistoId, cedeaoId, categorieId) REFERENCES CATEGORIE(cedeaohistoId, cedeaoId, categorieId),
FOREIGN KEY(puissanceId) REFERENCES PUISSANCE(puissanceId)
);

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

CREATE TABLE VEHICULE(
clientId INT,
contratId INT,
vehiculeId INT IDENTITY,
codevehicule CHAR(5) NOT NULL,
marque VARCHAR(50) 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,
clientId_1 INT NOT NULL,
catcomplementId INT NOT NULL,
cedeaohistoId CHAR(2) NOT NULL,
cedeaoId INT NOT NULL,
categorieId INT NOT NULL,
puissanceId INT NOT NULL,
tarifId INT NOT NULL,
PRIMARY KEY(clientId, contratId, vehiculeId),
UNIQUE(codevehicule),
FOREIGN KEY(clientId, contratId) REFERENCES CONTRAT(clientId, contratId),
FOREIGN KEY(clientId_1) REFERENCES CLIENT(clientId),
FOREIGN KEY(catcomplementId, cedeaohistoId, cedeaoId, categorieId, puissanceId, tarifId) REFERENCES TARIF(catcomplementId, cedeaohistoId, cedeaoId, categorieId, puissanceId, tarifId)
);

CREATE TABLE PROTEGER(
cedeaohistoId CHAR(2),
cedeaoId INT,
categorieId INT,
garantieId INT,
PRIMARY KEY(cedeaohistoId, cedeaoId, categorieId, garantieId),
FOREIGN KEY(cedeaohistoId, cedeaoId, categorieId) REFERENCES CATEGORIE(cedeaohistoId, cedeaoId, categorieId),
FOREIGN KEY(garantieId) REFERENCES GARANTIE(garantieId)
);

CREATE TABLE ASSOCIER(
clientId INT,
contratId INT,
garantieId INT,
PRIMARY KEY(clientId, contratId, garantieId),
FOREIGN KEY(clientId, contratId) REFERENCES CONTRAT(clientId, contratId),
FOREIGN KEY(garantieId) REFERENCES GARANTIE(garantieId)
);

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, contratId, avenantId),
FOREIGN KEY(agenceId, caissiereId) REFERENCES CAISSIERE(agenceId, caissiereId),
FOREIGN KEY(modepaiemenId) REFERENCES MODEPAIEMENT(modepaiemenId)
);

CREATE TABLE CLI_AV(
clientId INT,
clientId_1 INT,
contratId INT,
avenantId INT,
dateemissioncliav DATE NOT NULL,
PRIMARY KEY(clientId, clientId_1, contratId, avenantId),
FOREIGN KEY(clientId) REFERENCES CLIENT(clientId),
FOREIGN KEY(clientId_1, contratId, avenantId) REFERENCES AVENANT(clientId, contratId, avenantId)
);

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

Quelles sont vos remarques et suggestions?
Merci par avance.

**fsmrel** · 23/11/2020, 16h03

Bonsoir Zidane7,

Envoyé par Zidane7

Peut on lier la catégorie et la catégorie complémentaire?

Appelons CAT_CO l’association correspondante à mettre en oeuvre entre CATEGORIE et CAT_COMPLEMENT. En procédant ainsi, soit on en revient aux sous-catégories qui n’ont pas votre faveur et on débranche TARIF de CATEGORIE (suppression de l’association (PFC_C), soit on ne débranche pas, auquel cas il existe désormais une boucle selon laquelle CATEGORIE est accessible depuis TARIF (anciennement PFC) directement via PFC_C et indirectement via TA_CO puis CAT_CO, d’où nécessité de contrôler que par le 1er chemin on atteint la même catégorie que par le 2e chemin (contrôle de redondance).

De toute façon, au stade SQL pour afficher le libellé de la catégorie, il faudra le récupérer dans la table CATEGORIE, même chose pour afficher le libellé complémentaire, il faudra le récupérer dans la table CAT_COMPLEMENT...

Tout cela fait que dans tous les cas, je trouve inutile la mise en oeuvre de l’association CAT_CO et les requêtes SQL qui suivent montrent qu’on s’en passe sans problème.

Envoyé par Zidane7

D'après mes analyses, je viens d'apporter d'autres modifications concernant la CEDEAO, la catégorie complémentaire.

Je reviendrai un peu plus tard sur ces modifications. A noter déjà :

– 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----(CEH_CE)----1,1(R)--[CEDAO_HISTO]

– CATEGORIE n’est pas à identifier relativement à CEDEAO (elle n’en est pas la propriété), d’où :

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

– La défense recours et le coût de la police ne doivent pas faire l’objet d’attributs dans TARIF. En effet, si ces données y sont présentes, alors s’il y a un changement de RC (responsabilité civile), on doit historiser cette RC dans TARIF_HISTO, donc aussi la DR (défense recours) et le CP (coût de la police), alors que la DR et CP n’ont pas changé. Plus embêtant, si la DR change, on doit historiser la RC et le CP, et si c’est le CP qui change, on doit historiser la RC et la DR. Bref, une chatte finirait par ne pas retrouver ses chatons dans TARIF_HISTO...

Par voie de conséquence, TARIF_HISTO ne concerne elle aussi que la responsabilité civile.

L’historisation est un sujet délicat, que j’ai abordé dans mon article sur la normalisation, notamment au paragraphe 6.3 et il s’agit en l’occurrence d’un bref aperçu de ce qui est développé en plus de 500 pages dans Time and Relational Theory (par C.J. Date, H. Darwen et N. Lorentzos).

Voyons déjà ce que cela donne avec la responsabilité civile, indépendamment de la CEDEAO, la défense recours et le coût de la police.

Soit la vue suivante du MCD :

Nom : Zidane7_assurance_auto (7)rc_histo.png
Affichages : 149
Taille : 23,9 Ko

Un jeu d’essai avec pour objectif de retrouver la RC pour un véhicule, les RC pour un client ou un contrat donnés :

Les puissances :

CREATE TABLE PUISSANCE
(
   puissanceId     INT IDENTITY,
   puissanceCode   CHAR(2)  NOT NULL,
   borneInf        SMALLINT NOT NULL,
   borneSup        SMALLINT NOT NULL,
   unite           VARCHAR(12) NOT NULL,
   CONSTRAINT PUISSANCE_PK PRIMARY KEY(puissanceId),
   CONSTRAINT PUISSANCE_AK UNIQUE(puissanceCode),
   CONSTRAINT PUISSANCE_UK UNIQUE(borneInf, unite)
);
SET IDENTITY_INSERT PUISSANCE OFF ;
DBCC CHECKIDENT (PUISSANCE, RESEED, 1)
;
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') 
;
SELECT '' as PUISSANCE, puissanceCode, borneInf, borneSup, unite FROM PUISSANCE ;

=> :

puissanceCode   borneInf   borneSup   unite

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

Les catégories :

CREATE TABLE CATEGORIE
(
   catId        INT IDENTITY,
   catCode      VARCHAR(4) NOT NULL,
   catLibelle   VARCHAR(64) NOT NULL,
   CONSTRAINT CATEGORIE_PK PRIMARY KEY(catId),
   CONSTRAINT CATEGORIE_AK UNIQUE(catCode)
);

INSERT INTO CATEGORIE (catCode, catLibelle)
VALUES
    ('1', 'Catégorie 1 : Affaires et promenades (AP)')
  , ('2', 'Catégorie 2 : Transport pour le compte de l''assuré (TPC)')
  , ('3', 'Catégorie 3 : Transport public de marchandises (TPM)')
  , ('4', 'Catégorie 4 :')
  , ('5', 'Catégorie 5 : Motos')
;
SELECT catCode, catLibelle FROM CATEGORIE ;

=>

catCode   catLibelle

1         Catégorie 1 : Affaires et promenades (AP)
2         Catégorie 2 : Transport pour le compte de l'assuré (TPC)
3         Catégorie 3 : Transport public de marchandises (TPM)
4         Catégorie 4 :
5         Catégorie 5 : Motos

Les libellés complémentaires :

CREATE TABLE CAT_COMPLEMENT
(
   catComplementId INT IDENTITY,
   catComplementCode SMALLINT NOT NULL,
   catComplementLibelle VARCHAR(96) NOT NULL,
   CONSTRAINT CAT_COMPLEMENT_PK PRIMARY KEY(catComplementId),
   CONSTRAINT CAT_COMPLEMENT_AK UNIQUE(catComplementCode),
);

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

Les tarifs en vigueur :

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_CATEGORIE_FK FOREIGN KEY(catId) 
       REFERENCES CATEGORIE(catId),
   CONSTRAINT TARIF_CAT_COMPLEMENT_FK FOREIGN KEY(catComplementId) 
       REFERENCES CAT_COMPLEMENT(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  = 0)
    , '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  = 0)
    , '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  = 0)
    , '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  = 0)
    , '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  = 0)
    , '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  = 0)
    , '2020-10-01', 401383)
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '2')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 0)
    , '2020-10-01', 563124)
,
    ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '2')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode  = 0)
   , '2020-10-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)
,
    ((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) 
;
-- 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)
,
    ((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) 
,

    ((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) 
,
    ((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) 
,
    ((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) 
,
    ((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) 
,
    ((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) 
,
    ((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) 
,
    ((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) 
;
--------------------------------------------------------------
-- Pour voir
--------------------------------------------------------------
SELECT puissanceCode as puissance, unite, borneInf, borneSup 
     , LEFT(CONCAT(catLibelle , ' ', catComplementLibelle),76) as cat
     , tarifDateDepuis
     , 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.catComplementId = t.catComplementId 
ORDER BY cat, unite, borneInf    
;

=>

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)           2020-10-01  401383
C3        CV        7   10   Catégorie 2 : Transport pour le compte de l'assuré (TPC)           2020-10-01  563124
C4        CV        11  14   Catégorie 2 : Transport pour le compte de l'assuré (TPC)           2020-10-01  583830
T1        tonne     0   1    Catégorie 3 : Transport public de marchandises (TPM) Marchandises  2020-10-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      2020-10-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      2020-10-04  728486
C4        CV        11  14   Catégorie 4 : Taxis urbains et inter-urbains - Taxis 4 places      2020-10-04  806813
C5        CV        15  23   Catégorie 4 : Taxis urbains et inter-urbains - Taxis 4 places      2020-10-04  846877
C2        CV        3   6    Catégorie 4 : Taxis urbains et inter-urbains - Taxis 5 places      2020-10-04  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

L’historique des anciens 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  = 0)
   , '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  = 0)
   , '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  = 0)
   , '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  = 0)
   , '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  = 0)
   , '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  = 0)
   , '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  = 0)
   , '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  = 0)
   , '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  = 0)
   , '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  = 0)
   , '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  = 0)
   , '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  = 0)
   , '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  = 0)
   , '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  = 0)
   , '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  = 0)
   , '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  = 0)
   , '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  = 0)
   , '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  = 0)
   , '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)
;

--------------------------------------------------------------
-- pour voir
--------------------------------------------------------------
SELECT 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 respCivile
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

=>

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

Pour se faciliter la vie, on peut créer une vue avec laquelle on peut voir à la fois le contenu de la table TARIF et celui de la table TARIF_HISTO (pour le tarif en cours, la date de fin est simulée, affichée '9999-12-31' (l’infini))
:

go
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.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.catComplementId = t.catComplementId 
;
go

Les clients :

CREATE TABLE CLIENT
(
   clientId INT IDENTITY,
   clientCode VARCHAR(10) NOT NULL,
   clientNom VARCHAR(48) NOT NULL,
   clientPrenom VARCHAR(48) NOT NULL,
   clientAdresse VARCHAR(95) NOT NULL,
   clientTelephone VARCHAR(20) NOT NULL,
   CONSTRAINT CLIENT_PK PRIMARY KEY(clientId),
   CONSTRAINT CLIENT_AK UNIQUE(clientCode)
);

INSERT INTO CLIENT (clientCode, clientNom, clientPrenom, clientAdresse, clientTelephone)
VALUES
    ('cli001', 'Naudin', 'Fernand', '3, rue Spirou, Montauban', '0567890123')
  , ('cli002', 'Volfoni', 'Raoul', 'La péniche, Paris', '0123456789')
  , ('cli003', 'Volfoni', 'Paul', 'La péniche, Paris', '0123456789')
  , ('cli004', 'Folace', '', '5, rue en pente, Paris', '0123456780')
;
SELECT * FROM CLIENT ;

=>

clientId  clientCode   clientNom  clientPrenom  clientAdresse                clientTelephone

1         cli001       Naudin     Fernand       3, rue Spirou, Montauban     0567890123
2         cli002       Volfoni    Raoul         La péniche, Paris            0123456789
3         cli003       Volfoni    Paul          La péniche, Paris            0123456789
4         cli004       Folace                   5, rue en pente, Paris       0123456780

Les contrats (avec la référence aux clients, et pour aérer,sans les données relatives aux agences, producteurs, etc.) :

CREATE TABLE CONTRAT
(
   clientId INT,
   contratId INT IDENTITY,
   contratCode VARCHAR(10) NOT NULL,
   contratDateEffet DATE NOT NULL,
   contratDateExpiration DATE NOT NULL,
   CONSTRAINT CONTRAT_PK PRIMARY KEY(contratId),
   CONSTRAINT CONTRAT_AK UNIQUE(contratCode),
   CONSTRAINT CONTRAT_CLIENT_FK FOREIGN KEY(clientId) 
       REFERENCES CLIENT(clientId)
);
INSERT INTO CONTRAT (clientId, contratCode, contratDateEffet, contratDateExpiration)
VALUES
    ((SELECT clientId FROM CLIENT WHERE clientCode = 'cli001')
     , 'ctr001', '2019-06-14', '2023-06-13')
  ,
    ((SELECT clientId FROM CLIENT WHERE clientCode = 'cli001')
     , 'ctr002', '2019-06-14', '2023-06-13')
  ,       
    ((SELECT clientId FROM CLIENT WHERE clientCode = 'cli002')
     , 'ctr021', '2018-05-04', '2022-05-03')
  ,
    ((SELECT clientId FROM CLIENT WHERE clientCode = 'cli002')
     , 'ctr022', '2019-01-03', '2022-01-02')
  ,
    ((SELECT clientId FROM CLIENT WHERE clientCode = 'cli002')
     , 'ctr023', '2020-12-01', '2025-11-30')
  ,
    ((SELECT clientId FROM CLIENT WHERE clientCode = 'cli003')
     , 'ctr031', '2017-12-01', '2021-11-30')

;
SELECT * FROM CONTRAT ;

=>

clientId    contratId   contratCode   contratDateEffet   contratDateExpiration

1           1           ctr001        2019-06-14         2023-06-13
1           2           ctr002        2019-06-14         2023-06-13
2           3           ctr021        2018-05-04         2022-05-03
2           4           ctr022        2019-01-03         2022-01-02
2           5           ctr023        2020-12-01         2025-11-30
3           6           ctr031        2017-12-01         2021-11-30

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,
   catId INT NOT NULL,
   puissanceId INT NOT NULL,
   catComplementId INT NOT NULL,
   contratId INT NOT NULL,
   CONSTRAINT VEHICULE_PK PRIMARY KEY(vehiculeId),
   CONSTRAINT VEHICULE_AK UNIQUE(vehiculeCode),
   CONSTRAINT VEHICULE_AK UNIQUE(vehiculeSerie),
   CONSTRAINT VEHICULE_TARIF_FK FOREIGN KEY(catId, puissanceId, catComplementId) 
       REFERENCES TARIF(catId, puissanceId, 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 = 0)  
    , (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 = 0)  
    , (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')
    , (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'))
;
SELECT vehiculeId as vehId, vehiculeCode as vehCode
     , vehiculeSerie as vehSerie, vehiculePuissance as puissance, vehiculeMarque as marque
     , catId, puissanceId as puissId, catComplementId as compId, contratId as ctrId
FROM VEHICULE ;

=>

vehId   vehCode  vehSerie            puissance   marque           catId       puissId     compId      ctrId
1       veh001   FN345678901234567   8           Ford             4           2           4           1
2       veh011   VM345678901234567   15          Mercedes         1           5           1           3
3       veh012   VR345678901234567   7           Renault          1           3           1           3
4       veh013   VR456789012345678   8           Ford             4           2           4           4
5       veh014   VR567890123456789   6           Ford             4           2           4           5

Une vue pour simplifier les recherches des responsabilités civiles des véhicules :

CREATE VIEW VEHICULE_TARIF
    (vehiculeCode, vehiculePuissance, contratCode, contratDateEffet
   , puissanceCode, unite, borneInf, borneSup
   , cat 
   , tarifDebut, tarifFin, respCivile)
AS
SELECT vehiculeCode, vehiculePuissance, contratCode, contratDateEffet
     , puissanceCode, unite, borneInf, borneSup
     , cat 
     , z.dateDebut as tarifDebut, z.dateFin as tarifFin, respCivile
FROM   VEHICULE as x
  JOIN CONTRAT as y ON x.contratId = y.contratId
  JOIN TARIF_TOUT as z ON x.catId = z.catId
                      AND x.catComplementId = z.catComplementId
                      AND x.puissanceId= z.puissanceId
   AND z.dateDebut <= contratDateEffet AND z.dateFin >= contratDateEffet
   AND x.catComplementId = z.catComplementId
;

Recherche de la responsabilité civile pour le véhicule 'veh011'. A priori on ne sait pas si la RC est la plus récente (table TARIF) ou si elle est historisée (table TARIF_HISTO).
Pour la retrouver, on utilise donc la vue VEHICULE_TARIF qui contient tout :

SELECT vehiculeCode as vehCode, vehiculePuissance as vehPui
     , contratCode as ctrCode, contratDateEffet as dateEffet
     , borneInf as inf, borneSup as sup
     , cat
     , tarifDebut as tarifDeb, tarifFin, respCivile as RC
  FROM VEHICULE_TARIF WHERE vehiculeCode = 'veh011' ;

=>

vehCode  vehPui  ctrCode  dateEffet   inf  sup cat                                        tarifDeb    tarifFin     RC

veh011   15      ctr021   2018-05-04  15   23  Catégorie 1 : Affaires et promenades (AP)  2018-01-01  2018-12-31   395000

Retrouver la responsabilité civile pour le véhicule veh014' :

SELECT vehiculeCode as vehCode, vehiculePuissance as vehPui
     , contratCode as ctrCode, contratDateEffet as dateEffet
     , borneInf as inf, borneSup as sup
     , cat
     , tarifDebut as tarifDeb, tarifFin, respCivile as RC
  FROM VEHICULE_TARIF WHERE vehiculeCode = 'veh014' ;

=>

vehCode  vehPui  ctrCode  dateEffet   inf  sup cat                                        tarifDeb    tarifFin     RC

veh014   6       ctr023   2020-12-01  3   6   Catégorie 4 : [...] Taxis 4 places          2020-10-04  9999-12-31   690102

Manifestement, la RC du véhicule 'veh011' est dans l’historique, tandis que la RC du véhicule 'veh014' est celle du tarif le plus récent (date fin = '9999-12-31').

Rechercher les responsabilités civiles des clients (par exemple pour le client 'cli002') :

SELECT clientCode, clientNom, clientPrenom, x.* 
FROM   VEHICULE_TARIF as x
  JOIN CONTRAT as y ON x.contratCode = y.contratCode
  JOIN CLIENT as z ON y.clientId = z.clientId
WHERE clientCode = 'cli002' ;

Je vous laisse exécuter la requête.

A suivre.

N.B. N’hésitez pas à vous procurer le livre de Paprick, son étude vous sera d’un grand profit.

**fsmrel** · 25/11/2020, 04h00

Bonjour Zidane7,

A propos de la défense recours.

J’ai écrit (post #64) « Si vous souhaitez que les tables servent aussi pour d’autres taux que la défense recours, il faudra prévoir une colonne pour le type de taux »

En fait cela compliquerait pas mal les choses : dédions plutôt une table aux taux de la défense recours.

Partons de votre proposition (post #63) :

Envoyé par Zidane7

Ici je dirai de créer une table dans laquelle on met les taux et ensuite crée une table similaire qu'on appellera historique des taux.

En fait, une seule table suffit. nommons-la TAUX_DEFENSE_RECOURS :

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)
);

Un jeu d’essai :

INSERT INTO TAUX_DEFENSE_RECOURS (tauxDrDebut, tauxDrFin, tauxDrValeur)
VALUES
    ('1900-02-01', '2015-01-31', 0.05)
  , ('2015-02-01', '2017-01-31', 0.07)
  , ('2017-02-01', '2017-05-31', 0.06)
  , ('2017-06-01', '2018-01-31', 0.08)
  , ('2018-02-01', '2019-01-31', 0.04)
  , ('2019-02-01', '2020-01-31', 0.06)
  , ('2020-02-01', '2020-11-30', 0.09)
  , ('2020-12-01', '9999-12-31', 0.05)
;

J’ai amorcé les taux avec une date de début en 1900 : c’est seulement pour éviter les mauvaises surprises dans le rapprochement des tarifs (responsabilité civile) et des taux. Par ailleurs, il est impératif que la date de début d’un taux (autre que le 1er) soit égale à la date de fin du taux précédent + 1 jour.

=>

tauxDrDebut   tauxDrFin     tauxDrValeur

1900-02-01    2015-01-31    0,05
2015-02-01    2017-01-31    0,07
2017-02-01    2017-05-31    0,06
2017-06-01    2018-01-31    0,08     
2018-02-01    2019-01-31    0,04
2019-02-01    2020-01-31    0,06
2020-02-01    2020-11-30    0,09
2020-12-01    9999-12-31    0,05

Pour se simplifier la vie par la suite, on définit les paires constituées de deux taux consécutifs dans le temps. Ceci donne lieu à une vue, nommons-la TAUX_PAIRE :

go

CREATE VIEW TAUX_PAIRE
    (tauxDrDebut1, tauxDrFin1
   , tauxDrDebut2, tauxDrFin2
   , tauxDrValeur1, tauxDrValeur2)
AS
SELECT x.tauxDrDebut, x.tauxDrFin
       , y.tauxDrDebut, y.tauxDrFin
       , x.tauxDrValeur, y.tauxDrValeur
FROM   TAUX_DEFENSE_RECOURS as x  
  JOIN TAUX_DEFENSE_RECOURS as y
       ON dateadd(day, 1, x.tauxDrFin) = y.tauxDrDebut
       WHERE x.tauxDrFin < '9999-12-31'
;
go 

SELECT * from TAUX_PAIRE ;

=>

tauxDrDebut1   tauxDrFin1     tauxDrDebut2   tauxDrFin2     taux1   taux2

1900-02-01     2015-01-31     2015-02-01     2017-01-31     0.05    0.07
2015-02-01     2017-01-31     2017-02-01     2017-05-31     0.07    0.06
2017-02-01     2017-05-31     2017-06-01     2018-01-31     0.06    0.08
2017-06-01     2018-01-31     2018-02-01     2019-01-31     0.08    0.04
2018-02-01     2019-01-31     2019-02-01     2020-01-31     0.04    0.06
2019-02-01     2020-01-31     2020-02-01     2020-11-30     0.06    0.09
2020-02-01     2020-11-30     2020-12-01     9999-12-31     0.09    0.05

Voici une vue permettant de voir quelles sont les défenses recours en fonction de l’évolution dans le temps des responsabilité s civiles et des taux de défense recours.

CREATE VIEW DEFENSE_RECOURS
    (catCode, catComplCode, puissanceCode
   , dateDebut, dateFin
   , respCivile, taux, defenseRecours)
AS
SELECT catCode, catComplCode, puissanceCode
     , x.dateDebut
     , CASE 
           WHEN y.tauxDrFin1 < x.dateFin 
               THEN y.tauxDrFin1
           ELSE x.dateFin
       END
     , respCivile, tauxDrValeur1
     , respCivile * tauxDrValeur1
FROM   TARIF_TOUT as x, TAUX_PAIRE as y
WHERE  x.dateDebut >= y.tauxDrDebut1
   AND x.dateDebut < y.tauxDrFin1
   AND y.tauxDrFin1 < x.dateFin 

UNION

SELECT catCode, catComplCode, puissanceCode 
     , y.tauxDrDebut2
     , CASE 
           WHEN y.tauxDrFin2 < x.dateFin 
               THEN y.tauxDrFin2              
           ELSE x.dateFin
       END
     , respCivile
     , tauxDrValeur2 
     , respCivile * tauxDrValeur2
FROM   TARIF_TOUT as x, TAUX_PAIRE as y
WHERE  x.dateFin > y.tauxDrDebut1 
   AND x.dateDebut < y.tauxDrFin1
   AND x.dateFin > y.tauxDrFin1 
go

Pour ne pas surcharger l’affichage, contentons-nous de visualiser par exemple les tarifs et l’impact du changement de taux dans le cas de la catégorie 1 et de la puissance C1 (0 à 2 CV) :

SELECT catCode, catComplCode, puissanceCode
     , dateDebut, dateFin  
     , respCivile, taux, defenseRecours    
FROM DEFENSE_RECOURS
WHERE catCode = 1 and puissanceCode = 'C1'
ORDER BY catCode, catComplCode, puissanceCode, dateDebut, dateFin

=>

catCode catComplCode puissanceCode   dateDebut    dateFin       respCivile   taux   defenseRecours
1       0            C1              2015-01-01   2015-01-31    180000       0.05   9000.00
1       0            C1              2015-02-01   2015-12-31    180000       0.07   12600.00
1       0            C1              2016-01-01   2017-01-31    185000       0.07   12950.00
1       0            C1              2017-02-01   2017-05-31    185000       0.06   11100.00
1       0            C1              2017-06-01   2017-10-31    185000       0.08   14800.00
1       0            C1              2017-11-01   2018-01-31    190000       0.08   15200.00
1       0            C1              2018-02-01   2018-10-03    190000       0.04   7600.00
1       0            C1              2018-10-04   2019-01-31    195000       0.04   7800.00
1       0            C1              2019-02-01   2019-08-03    195000       0.06   11700.00
1       0            C1              2019-08-04   2020-01-31    196000       0.06   11760.00
1       0            C1              2020-02-01   2020-09-30    196000       0.09   17640.00
1       0            C1              2020-10-01   2020-11-30    201657       0.09   18149.13
1       0            C1              2020-12-01   9999-12-31    201657       0.05   10082.85

Pour mémoire, la vue DEFENSE_RECOURS fait référence aux vues TARIF_TOUT et TAUX_PAIRE. Concernant l’affichage du contenu de TAUX_PAIRE, voir ci-dessus. Le contenu de TARIF_TOUT pour catCode = 1 et puissanceCode = 'C1' est le suivant :

catCode   catComplCode   puissanceCode   dateDebut    dateFin        respCivile
1         0              C1              2015-01-01   2015-12-31     180000
1         0              C1              2016-01-01   2017-10-31     185000
1         0              C1              2017-11-01   2018-10-03     190000
1         0              C1              2018-10-04   2019-08-03     195000
1         0              C1              2019-08-04   2020-09-30     196000
1         0              C1              2020-10-01   9999-12-31     201657

Je rappelle que la vue TARIF_TOUT est l’UNION des tables TARIF et TARIF_HISTO, voir à ce sujet le post #76.

Une vue pour voir la responsabilité civile et la défense recours des véhicules :

go
CREATE VIEW VEHICULE_TARIF_RECOURS
    (vehiculeCode, vehiculePuissance, contratCode, contratDateEffet
   , catCode, catComplCode
   , dateDebut, dateFin, respCivile, defenseRecours, taux 
   , catLibelle     
   , puissanceCode, unite, borneInf, borneSup
)
AS
SELECT vehiculeCode as vehCode, vehiculePuissance as vehPuiss
     , contratCode as ctrCode, contratDateEffet as ctrEffet
     , x.catCode, x.catComplCode as complCode
     , y.dateDebut, y.dateFin, y.respCivile, y.defenseRecours, y.taux 
     , x.catLibelle     
     , x.puissanceCode, x.unite, x.borneInf, x.borneSup
FROM   VEHICULE_TARIF as x
  JOIN DEFENSE_RECOURS as y ON x.catCode = y.catCode
                           AND x.catComplCode = y.catComplCode
                           AND x.puissanceCode = y.puissanceCode
WHERE x.contratDateEffet >= y.dateDebut
   AND x.contratDateEffet <= y.dateFin
;
go

SELECT
       vehiculeCode as vehCode, vehiculePuissance as vehPuiss
     , contratCode as ctrCode, contratDateEffet as ctrEffet
     , catCode, catComplCode as complCode
     , dateDebut, dateFin, respCivile, defenseRecours, taux 
     , catLibelle     
     , puissanceCode, unite, borneInf, borneSup
FROM VEHICULE_TARIF_RECOURS
ORDER BY vehiculeCode
;

=>

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.00        0.06   ...
veh011   15          ctr021     2018-05-04   1        0           2018-02-01   2018-12-31    395000       15800.00        0.04   ...
veh012   7           ctr021     2018-05-04   1        0           2018-02-01   2019-01-31    270000       10800.00        0.04   ...
veh013   8           ctr022     2019-01-03   4        41          2018-02-01   2019-01-31    670000       26800.00        0.04   ...
veh014   6           ctr023     2020-12-01   4        41          2020-12-01   9999-12-31    690102       34505.10        0.05   ...

La programmation des intersections des historiques n’est pas chose aisée, mais on y arrive quand même...

Merci de me signaler les éventuelles erreurs.

N.B. Dans la vue VEHICULE_TARIF j'ai renommé la colonne cat en catLibelle.

**fsmrel** · 25/11/2020, 15h29

Bonjour Zidane7,

Dans le post précédent #77 j’ai omis la requête permettant de rechercher les responsabilités civiles et les défenses recours des clients. Pour effectuer cette recherche, je complète en reprenant et en l’adaptant la requête du post #76, laquelle permet de rechercher les responsabilités civiles des clients, par exemple pour le client 'cli002', la requête du post #76 est la suivante :

SELECT clientCode, clientNom, clientPrenom, x.* 
FROM   VEHICULE_TARIF as x
  JOIN CONTRAT as y ON x.contratCode = y.contratCode
  JOIN CLIENT as z ON y.clientId = z.clientId
WHERE clientCode = 'cli002' ;

On l’adapte pour les défenses recours, en remplaçant VEHICULE_TARIF par VEHICULE_TARIF_RECOURS :

SELECT clientCode as cliCode, clientNom as cliNom, clientPrenom as cliPrenom
     , vehiculeCode as vehCode, vehiculePuissance as vehPuis
     , x.contratCode as ctrCode, x.contratDateEffet as dateEffet
     , x.catCode, x.catComplCode as complCode
     , x.dateDebut, x.dateFin, x.respCivile, x.defenseRecours as defRecours, x.taux 
FROM   VEHICULE_TARIF_RECOURS as x
  JOIN CONTRAT as y ON x.contratCode = y.contratCode
  JOIN CLIENT as z ON y.clientId = z.clientId
WHERE clientCode = 'cli002' ;

=>

cliCode  cliNom   cliPrenom  vehCode vehPuis ctrCode dateEffet   catCode  complCode  dateDebut   dateFin     respCivile  defRecours  taux  
cli002   Volfoni  Raoul      veh011   15     ctr021  2018-05-04  1        0          2018-02-01  2018-12-31  395000      15800.00    0.04
cli002   Volfoni  Raoul      veh012   7      ctr021  2018-05-04  1        0          2018-02-01  2019-01-31  270000      10800.00    0.04
cli002   Volfoni  Raoul      veh013   8      ctr022  2019-01-03  4        41         2018-02-01  2019-01-31  670000      26800.00    0.04
cli002   Volfoni  Raoul      veh014   6      ctr023  2020-12-01  4        41         2020-12-01  9999-12-31  690102      34505.10    0.05

**fsmrel** · 30/11/2020, 03h25

Bonsoir Zidane7,

Passons à la CEDEAO.

1) On crée la table CEDEAO, sans relation avec les autres tables :

CREATE TABLE CEDEAO
(
   cedeaoId INT IDENTITY,
   cedeaoCode CHAR(4) NOT NULL,
   cedeaoDepuis DATE NOT NULL,
   cedeaoMontant INT NOT NULL,
   CONSTRAINT CEDEAO_PK PRIMARY KEY(cedeaoId),
   CONSTRAINT CEDEAO_AK UNIQUE(cedeaoCode)
);

Selon ce que vous avez écrit dans le post #59, le montant est égal à 100000 pour les catégories 1 à 4, et 70000 pour la catégorie 5. Pour les tests je valorise cedeaoDepuis avec des valeurs fictives.

=>

INSERT INTO CEDEAO (cedeaoCode, cedeaoDepuis, cedeaoMontant)
VALUES
    ('c1', '2019-01-02', 100000)
  , ('c2', '2020-02-14', 70000)
;
SELECT cedeaoCode, cedeaoDepuis, cedeaoMontant 
FROM   CEDEAO ;

=>

cedeaoCode   cedeaoDepuis   cedeaoMontant

c1           2019-01-02     100000
c2           2020-02-14     70000

2) L’historique des CEDEAO :

CREATE TABLE CEDEAO_HISTO
(
   cedeaoId INT,
   cedeaoDebutHisto DATE,
   cedeaoFinHisto DATE NOT NULL,
   cedeaoMontantHisto INT NOT NULL,
   CONSTRAINT CEDEAO_HISTO_PK PRIMARY KEY(cedeaoId, cedeaoDebutHisto),
   CONSTRAINT CEDEAO_HISTO_CEDEAO_FK FOREIGN KEY(cedeaoId) 
       REFERENCES CEDEAO(cedeaoId)
);
INSERT INTO CEDEAO_HISTO (cedeaoId, cedeaoDebutHisto, cedeaoFinHisto, cedeaoMontantHisto)  
VALUES 
    ((select cedeaoId from CEDEAO WHERE cedeaoCode = 'c1')
     , '2018-01-01', '2019-01-01', 95000)
,    ((select cedeaoId from CEDEAO WHERE cedeaoCode = 'c1')
     , '2016-01-01', '2017-12-31', 90000)
,    ((select cedeaoId from CEDEAO WHERE cedeaoCode = 'c1')
     , '1900-01-01', '2015-12-31', 85000)
,    ((select cedeaoId from CEDEAO WHERE cedeaoCode = 'c2')
     , '2019-08-01', '2020-02-13', 65000)
,    ((select cedeaoId from CEDEAO WHERE cedeaoCode = 'c2')
     , '1900-01-01', '2019-07-31', 60000)
;

SELECT * FROM CEDEAO_HISTO ;

=>

cedeaoId    cedeaoDebutHisto   cedeaoFinHisto   cedeaoMontantHisto

1           1900-01-01         2015-12-31       85000
1           2016-01-01         2017-12-31       90000
1           2018-01-01         2019-01-01       95000
2           1900-01-01         2019-07-31       60000
2           2019-08-01         2020-02-13       65000

Pour assurer le coup et éviter les mauvaises surprises, j’ai valorisé la plus ancienne CEDEAO au 1er janvier 1900.

3) Une vue d’union pour voir l’ensemble des valeurs de la CEDEAO :

go
CREATE VIEW CEDEAO_TOUT (cedeaoId, cedeaoCode, cedeaoDebut, cedeaoFin, cedeaoMontant)
AS
SELECT cedeaoId, cedeaoCode, cedeaoDepuis, '9999-12-31', cedeaoMontant 
FROM   CEDEAO
UNION
SELECT x.cedeaoId, cedeaoCode
     , cedeaoDebutHisto, cedeaoFinHisto, cedeaoMontantHisto
FROM   CEDEAO as x 
  JOIN CEDEAO_HISTO as y ON x.cedeaoId = y.cedeaoId 
;
go

SELECT '' as CEDEAO_TOUT, * FROM CEDEAO_TOUT
;

=>

cedeaoId    cedeaoCode   cedeaoDebut   cedeaoFin   cedeaoMontant

1           c1           1900-01-01    2015-12-31  85000
1           c1           2016-01-01    2017-12-31  90000
1           c1           2018-01-01    2019-01-01  95000
1           c1           2019-01-02    9999-12-31  100000
2           c2           1900-01-01    2019-07-31  60000
2           c2           2019-08-01    2020-02-13  65000
2           c2           2020-02-14    9999-12-31  70000

On couvre bien la période allant de « - l’infini » à « + l’infini ».

4) Chaque catégorie doit faire référence à une CEDEAO, on ajoute donc l’attribut cedeaoId à la table CATEGORIE ainsi que la clé étrangère qui va bien (CATEGORIE_CEDEAO_FK) :

CREATE TABLE CATEGORIE
(
   catId        INT IDENTITY,
   catCode      VARCHAR(4) NOT NULL,
   catLibelle   VARCHAR(64) NOT NULL,
   cedeaoId     INT NOT NULL,   
   CONSTRAINT CATEGORIE_PK PRIMARY KEY(catId),
   CONSTRAINT  CATEGORIE_AK UNIQUE(catCode)
 , CONSTRAINT CATEGORIE_CEDEAO_FK FOREIGN KEY(cedeaoId) 
       REFERENCES CEDEAO(cedeaoId) 
);

5) Les catégories :

INSERT INTO CATEGORIE (catCode, catLibelle, cedeaoId)
VALUES
    ('1', 'Catégorie 1 : Affaires et promenades (AP)'
     , (select cedeaoId from CEDEAO where cedeaoCode = 'c1'))
  , ('2', 'Catégorie 2 : Transport pour le compte de l''assuré (TPC)'
     , (select cedeaoId from CEDEAO where cedeaoCode = 'c1'))
  , ('3', 'Catégorie 3 : Transport public de marchandises (TPM)'
     , (select cedeaoId from CEDEAO where cedeaoCode = 'c1'))
  , ('4', 'Catégorie 4 :'
     , (select cedeaoId from CEDEAO where cedeaoCode = 'c1'))
  , ('5', 'Catégorie 5 : Motos'
     , (select cedeaoId from CEDEAO where cedeaoCode = 'c2'))
;
SELECT catCode, catLibelle, cedeaoId 
FROM   CATEGORIE ;

=>

catCode   catLibelle                                                       cedeaoId

1         Catégorie 1 : Affaires et promenades (AP)                        1
2         Catégorie 2 : Transport pour le compte de l'assuré (TPC)         1
3         Catégorie 3 : Transport public de marchandises (TPM)             1
4         Catégorie 4 :                                                    1
5         Catégorie 5 : Motos                                              2

6) Les tarifs.

Il est préférable que les tarifs qui n’ont pas connu l’historisation commencent à « – l’infini », disons au 1er janvier 1900.

Par rapport à celui du post #76,le jeu d’essai devient donc le suivant pour la table TARIF :


-- 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 = 0)
    , '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  = 0)
    , '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  = 0)
    , '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  = 0)
    , '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  = 0)
    , '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  = 0)
    , '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  = 0)
    , '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  = 0)
   , '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)
    , '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)
    , '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)
    , '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)
   , '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)
   , '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)
   , '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)
   , '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)
   , '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)
   , '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)
   , '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)
   , '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)
   , '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  = 0)
    , '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  = 0)
    , '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  = 0)
    , '1900-01-01', 85000)
;
--------------------------------------------------------------
-- voyons voir
--------------------------------------------------------------
SELECT '' as TARIF,
       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.catComplementId = t.catComplementId 
ORDER BY cat, unite, borneInf    
;

=>

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

7) En complément de ce que j’ai fait dans le post #76, j’enrichis la table TARIF_HISTO pour les motos :

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  = 0)
   , '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  = 0)
   , '1900-01-01', '2019-02-01', 77000)
;

Contenu de la table TARIF_HISTO :

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

Un coup d’oeil pour s’assurer qu’il n’y a pas de tarifs sans historique et non datés à « – l'infini » ;
Le comptage doit être égal à 0 :

SELECT COUNT(*) as comptage   
FROM   TARIF as x
WHERE NOT EXISTS
    (SELECT * FROM TARIF_HISTO as y 
     WHERE   x.catid = y.catId 
         AND x.puissanceId = y.puissanceId 
         AND x.catComplementId = y.catComplementId)
AND x.tarifDateDepuis > '1900-01-01'
;

=>

comptage

0

8) Les clients et les contrats : voir post #76.

9) Les véhicules : en plus de ce qui est fait dans le post #76, j’ajoute deux motos (contrat concerné : 'ctr021') :

INSERT INTO VEHICULE (vehiculeCode, vehiculeSerie, vehiculePuissance, vehiculeMarque
                    , catId, puissanceId, catComplementId, contratId)
VALUES

    ('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 = 0)  
    , (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 = 0)  
    , (SELECT contratId FROM CONTRAT WHERE contratCode = 'ctr021'))

SELECT vehiculeCode as vehCode
     , vehiculeSerie as vehSerie, vehiculePuissance as puissance, vehiculeMarque as marque
     , catId, puissanceId as puissId, catComplementId as compId, contratId as ctrId
FROM VEHICULE ;

=>

vehCode  vehSerie            puissance   marque        catId   puissId   compId   ctrId

veh001   FN345678901234567   8           Ford          4       2         3        1
veh011   VM345678901234567   15          Mercedes      1       5         0        3
veh012   VR345678901234567   7           Renault       1       3         0        3
veh013   VR456789012345678   8           Ford          4       2         3        4
veh014   VR567890123456789   6           Ford          4       2         3        5
veh015   MO567890123456789   10          Honda         5       3         0        3
veh016   MO678901234567890   12          Triumph       5       4         0        3

Rappel : La vue VEHICULE_TARIF permet de simplifier les recherches des responsabilités civiles des véhicules (voir post #76).

Cas par exemple du véhicule 'veh015' :

SELECT vehiculeCode as vehCode, vehiculePuissance as vehPui
     , contratCode as ctrCode, contratDateEffet as dateEffet
     , borneInf as inf, borneSup as sup
     , catLibelle as cat
     , tarifDebut as tarifDeb, tarifFin, respCivile as RC
  FROM VEHICULE_TARIF WHERE vehiculeCode = 'veh015' ;

=>

vehCode  vehPui  ctrCode   dateEffet   inf   sup    cat                    tarifDeb     tarifFin     RC

veh015   10      ctr021    2018-05-04  7     10     Catégorie 5 : Motos    1900-01-01   2019-02-01   77000

10) Une vue VEHICULE_TARIF_RECOURS_CEDEAO pour voir en même temps la responsabilité civile, la défense recours et la CEDEAO pour les véhicules. Cette vue est construite à partir de CATEGORIE, VEHICULE_TARIF (voir post #76), DEFENSE_RECOURS (voir post #77) et CEDEAO_TOUT (voir ci-dessus) :

go
CREATE VIEW VEHICULE_TARIF_RECOURS_CEDEAO
    (vehiculeCode, vehiculePuissance, contratCode, contratDateEffet
   , catCode, catComplCode
   , tarifDebut, tarifFin, respCivile, defenseRecours, taux
   , cedeaoCode, cedeaoDebut, cedeaoFin, montant  
   , catLibelle     
   , puissanceCode, unite, borneInf, borneSup
)
AS
SELECT vehiculeCode, vehiculePuissance
     , contratCode, contratDateEffet
     , x.catCode, x.catComplCode
     , y.dateDebut, y.dateFin, y.respCivile, y.defenseRecours, y.taux
     , t.cedeaoCode, t.cedeaoDebut, t.cedeaoFin, t.cedeaoMontant 
     , x.catLibelle     
     , x.puissanceCode, x.unite, x.borneInf, x.borneSup
FROM   VEHICULE_TARIF as x
  JOIN DEFENSE_RECOURS as y ON x.catCode = y.catCode
                           AND x.catComplCode = y.catComplCode
                           AND x.puissanceCode = y.puissanceCode
  JOIN CATEGORIE as z ON y.catCode = z.catCode
  JOIN CEDEAO_TOUT as t ON z.cedeaoId = t.cedeaoId

WHERE x.contratDateEffet >= y.dateDebut
   AND x.contratDateEffet <= y.dateFin

  AND x.contratDateEffet >= t.cedeaoDebut
  AND x.contratDateEffet <= t.cedeaoFin 
;
go 

SELECT vehiculeCode as vehCode, vehiculePuissance as vehPuiss
     , contratCode as ctrCode, contratDateEffet as ctrEffet
     , catCode, catComplCode as complCode
     , tarifDebut, tarifFin, respCivile, defenseRecours as defRecours, taux
     , cedeaoCode, cedeaoDebut, cedeaoFin, cedeaoMontant  
     , catLibelle     
     , puissanceCode, unite, borneInf, borneSup    
FROM VEHICULE_TARIF_RECOURS_CEDEAO
ORDER BY vehCode ;

=>

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        0          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        0          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        0          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        0          2018-02-01   2019-01-31  85000       3400        0.04   c2          1900-01-01   2019-07-31  60000    ...

11) Responsabilité civile, défense recours, CEDEAO des véhicules du client 'cli002' :

SELECT clientCode as cliCode, clientNom as cliNom, clientPrenom as prenom
     , vehiculeCode as vehCode, vehiculePuissance as vehP 
     , x.contratCode as ctrCode, x.contratDateEffet as dateEffet
     , x.catCode, x.catComplCode as compl
     , x.tarifDebut, x.tarifFin
     , x.respCivile as respCiv, x.defenseRecours as defRecours, x.taux 
     , cedeaoCode, cedeaoDebut as cedDebut, cedeaoFin as cedFin, cedeaoMontant as cedMt    
FROM   VEHICULE_TARIF_RECOURS_CEDEAO as x
  JOIN CONTRAT as y ON x.contratCode = y.contratCode
  JOIN CLIENT as z ON y.clientId = z.clientId
WHERE clientCode = 'cli002' 
ORDER BY vehCode ;

=>

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       0      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       0      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       0      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       0      2018-02-01  2019-01-31  85000   3400       0.04  c2         1900-01-01  2019-07-31  60000

Sommes-nous en phase ?

**Zidane7** · 30/11/2020, 09h47

Bonjour Monsieur fsmrel,
Voici les différentes modifications que j'ai effectué sur le MCD ci-dessous:

Le code SQL du MCD est le suivant:

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(2) 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 CEDEAOHISTO(
cedeaohistoId CHAR(2),
cedeaohistodebut DATE NOT NULL,
cedeaohistofin DATE NOT NULL,
cedeaohistomontant INT NOT NULL,
PRIMARY KEY(cedeaohistoId)
);

CREATE TABLE DEFENSE_RECOURS(
_defencerecoursId INT IDENTITY,
tauxdefencerecours INT NOT NULL,
PRIMARY KEY(_defencerecoursId)
);

CREATE TABLE coutpoliceId(
coutpoliceId INT IDENTITY,
montantcoutpolice INT NOT NULL,
PRIMARY KEY(coutpoliceId)
);

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 CEDEAO(
cedeaohistoId CHAR(2),
cedeaoId INT,
cedeaoepuis DATE NOT NULL,
cedeaovehicule INT NOT NULL,
PRIMARY KEY(cedeaohistoId, cedeaoId),
UNIQUE(cedeaovehicule),
FOREIGN KEY(cedeaohistoId) REFERENCES CEDEAOHISTO(cedeaohistoId)
);

CREATE TABLE CATEGORIE(
cedeaohistoId CHAR(2),
cedeaoId INT,
categorieId INT IDENTITY,
codecategorie CHAR(4) NOT NULL,
categorie VARCHAR(50) NOT NULL,
coutpoliceId INT NOT NULL,
PRIMARY KEY(cedeaohistoId, cedeaoId, categorieId),
UNIQUE(codecategorie),
FOREIGN KEY(cedeaohistoId, cedeaoId) REFERENCES CEDEAO(cedeaohistoId, cedeaoId),
FOREIGN KEY(coutpoliceId) REFERENCES coutpoliceId(coutpoliceId)
);

CREATE TABLE CONTRAT(
clientId INT,
contratId INT IDENTITY,
codecontrat VARCHAR(10) NOT NULL,
dateEffetContrat DATE NOT NULL,
dateExpirationContrat DATE NOT NULL,
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 INT,
contratId INT,
avenantId INT IDENTITY,
codeavenant VARCHAR(10) NOT NULL,
libelleAvenant VARCHAR(50) NOT NULL,
dateEffetAvenant DATE NOT NULL,
dateExpirationAvenant DATE NOT NULL,
avenantlibelleId INT NOT NULL,
apporteurId INT,
PRIMARY KEY(clientId, contratId, avenantId),
UNIQUE(codeavenant),
FOREIGN KEY(clientId, contratId) REFERENCES CONTRAT(clientId, contratId),
FOREIGN KEY(avenantlibelleId) REFERENCES AVENANT_LIBELLE(avenantlibelleId),
FOREIGN KEY(apporteurId) REFERENCES APPORTEUR(apporteurId)
);

CREATE TABLE Sous_Garantie(
sousGarantieId INT IDENTITY,
codesousgarantie CHAR(2) NOT NULL,
libelleSousGarantieId VARCHAR(50) NOT NULL,
cedeaohistoId CHAR(2) NOT NULL,
cedeaoId INT NOT NULL,
categorieId INT NOT NULL,
garantieId INT NOT NULL,
PRIMARY KEY(sousGarantieId),
UNIQUE(codesousgarantie),
FOREIGN KEY(cedeaohistoId, cedeaoId, categorieId) REFERENCES CATEGORIE(cedeaohistoId, cedeaoId, categorieId),
FOREIGN KEY(garantieId) REFERENCES GARANTIE(garantieId)
);

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

CREATE TABLE REMORQUE(
cedeaohistoId CHAR(2),
cedeaoId INT,
categorieId INT,
remorqueId INT IDENTITY,
montantremorque INT,
cedeaoremorque INT,
PRIMARY KEY(cedeaohistoId, cedeaoId, categorieId, remorqueId),
UNIQUE(cedeaohistoId, cedeaoId, categorieId),
FOREIGN KEY(cedeaohistoId, cedeaoId, categorieId) REFERENCES CATEGORIE(cedeaohistoId, cedeaoId, categorieId)
);

CREATE TABLE VEHICULE(
clientId INT,
contratId INT,
vehiculeId INT IDENTITY,
codevehicule CHAR(5) NOT NULL,
marque VARCHAR(50) 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,
puissanceId INT NOT NULL,
cedeaohistoId CHAR(2) NOT NULL,
cedeaoId INT NOT NULL,
categorieId INT NOT NULL,
catcomplementId INT NOT NULL,
clientId_1 INT NOT NULL,
PRIMARY KEY(clientId, contratId, vehiculeId),
UNIQUE(codevehicule),
FOREIGN KEY(clientId, contratId) REFERENCES CONTRAT(clientId, contratId),
FOREIGN KEY(puissanceId) REFERENCES PUISSANCE(puissanceId),
FOREIGN KEY(cedeaohistoId, cedeaoId, categorieId, catcomplementId) REFERENCES CATCOMPLEMENT(cedeaohistoId, cedeaoId, categorieId, catcomplementId),
FOREIGN KEY(clientId_1) REFERENCES CLIENT(clientId)
);

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

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

CREATE TABLE PROTEGER(
cedeaohistoId CHAR(2),
cedeaoId INT,
categorieId INT,
garantieId INT,
PRIMARY KEY(cedeaohistoId, cedeaoId, categorieId, garantieId),
FOREIGN KEY(cedeaohistoId, cedeaoId, categorieId) REFERENCES CATEGORIE(cedeaohistoId, cedeaoId, categorieId),
FOREIGN KEY(garantieId) REFERENCES GARANTIE(garantieId)
);

CREATE TABLE ASSOCIER(
clientId INT,
contratId INT,
garantieId INT,
PRIMARY KEY(clientId, contratId, garantieId),
FOREIGN KEY(clientId, contratId) REFERENCES CONTRAT(clientId, contratId),
FOREIGN KEY(garantieId) REFERENCES GARANTIE(garantieId)
);

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, contratId, avenantId),
FOREIGN KEY(agenceId, caissiereId) REFERENCES CAISSIERE(agenceId, caissiereId),
FOREIGN KEY(modepaiemenId) REFERENCES MODEPAIEMENT(modepaiemenId)
);

CREATE TABLE CLI_AV(
clientId INT,
clientId_1 INT,
contratId INT,
avenantId INT,
dateemissioncliav DATE NOT NULL,
PRIMARY KEY(clientId, clientId_1, contratId, avenantId),
FOREIGN KEY(clientId) REFERENCES CLIENT(clientId),
FOREIGN KEY(clientId_1, contratId, avenantId) REFERENCES AVENANT(clientId, contratId, avenantId)
);

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

Avez-vous des remarques et suggestions?
Merci par avance.

Conception d'un MCD pour une assurance automobile

Schéma

Discussions similaires

Partager

Partager