Conception d'un MCD pour une assurance automobile

**fsmrel** · 22/01/2021, 03h14

Bonsoir à nouveau, Zidane7,

Une incidente. Avant de parler des flottes, quittons un instant les problèmes de modélisation. Je vous remercie d’avoir mieux rendu les citations à l’aide la balise QUOTE. Je vous propose de rendre encore plus confortables nos échanges, dans la façon d’utiliser cette balise.

Prenons le post #121. Dans ce post vous avez écrit :

« Et si c'est une flotte, nous remplaçons le code catégorie par code flotte. »

Dans le post #138, j’ai commenté ainsi :

« Pour les catégories, il existe une entité-type CATEGORIE. Pour les flottes, on ne voit pas d’entité-type FLOTTE dans le MCD. S’il y a différents types de flottes, créer une entité-type FLOTTE. Sinon, voir ma remarque ci-dessous. »

L’éditeur de texte de developpez.net a fait précéder ma réponse de l’information :

« Envoyé par Zidane7 » :

Nom : Zidane7_assurance_auto_reponse_avec_citation(a).png
Affichages : 231
Taille : 17,5 Ko

Pour parvenir à cela, j’ai ouvert votre message (post #121) et j’ai cliqué sur « Répondre avec citation » :

Nom : Zidane7_assurance_auto_reponse_avec_citation(b).png
Affichages : 226
Taille : 53,8 Ko

Suite à quoi j’ai eu droit à :

Nom : Zidane7_assurance_auto_reponse_avec_citation(c).png
Affichages : 237
Taille : 65,9 Ko

La balise QUOTE fournit ainsi un renvoi direct à votre post #121 (d’identifiant 11671083) ce qui est quand même bien pratique.

Après que j’ai répondu à votre message, l’affichage est le suivant :

Nom : Zidane7_assurance_auto_reponse_avec_citation(d).png
Affichages : 223
Taille : 17,3 Ko

Et maintenant, pour aller consulter votre message complet, il me suffit de cliquer sur l’icône « Voir le message » :

Nom : Zidane7_assurance_auto_reponse_avec_citation(e).png
Affichages : 222
Taille : 17,1 Ko

Notez encore que vous pouvez emboîter une citation dans une autre citation (voyez le post #140) :

Nom : Zidane7_assurance_auto_reponse_avec_citation(f).png
Affichages : 229
Taille : 70,7 Ko

Bon, je vais regarder les flottes...

**Zidane7** · 22/01/2021, 13h33

Bonjour Monsieur fsmrel,

fsmrel
Autrement dit, une fois créé, le contrat "2105101912345" ne changera jamais de valeur au fil des ans. On le considère comme invariant. C’est bien ça ?

Oui c'est effectivement comme ça.

fsmrel
Concernant le numéro de police :
Envoyé par Zidane7
A chaque an, on change l'année et le numéro de police commence par un(1)
Vous n’avez pas fait apparaître le numéro de police dans le MCD, il est donc géré sous le capot. Cela dit, chaque agence possède sa propre numérotation, recommençant à 1 quand on passe à une nouvelle année. Exact ?

Le numéro de police ici est le codecontrat mais sa gestion pour que sa change non seulement par agence mais aussi par an me semble un peu compliqué.
quand on commence une nouvelle année, le premier numéro de police doit commencer par un(1). et cela à chaque agence.
Que faire?

Envoyé par Zidane7
Il me semble que c'est un modèle qui ressemble à l'entité puissance

fsmrel
Merci d’expliquer la ressemblance, car je ne la vois pas...

J'ai fait un petit schéma ci-dessous pour vous montrer mon point de vue.
Merci.

Envoyé par Zidane7
Pour ce qui concerne l'année, j'ai crée une entité ANNÉE dans laquelle j'ai mis la propriété AN qui clé primaire de cette entité ANNÉE et qui détermine relativement le contrat. [...] on verrou l'année précédente pour éviter que les police de l'an en cours ne prennent celles de l'an N-1.

fsmrel
L’argument du verrouillage est contestable. Supposons que l’on a créé le contrat "2105102000012" en 2020 et un autre contrat "2105102100012" en 2021. Ces contrats sont désormais dans la base de données. Si par exemple on modifie ultérieurement l’année du second contrat en remplaçant "2105102100012" par "2105102000012", certes, lors de l’UPDATE le SGBD rejettera l’opération pour cause de doublon interdit dans la clé alternative (contrainte UNIQUE(codeContrat)), mais la clé primaire (AN, agenceId, contratId) n’y verra que du feu, même chose en ce qui concerne la contrainte de clé étrangère (ANNEE(AN)) ! Le verrouillage basé sur la clé primaire et la clé étrangère ne tient pas !

Ok merci.

fsmrel
Vous me direz que l’année figurant dans le code contrat doit être égale à l’année AN et l’on peut créer pour la table CONTRAT une contrainte CHECK qui s’assure de cela. Mais...
Quitte à avoir un attribut qui comme AN soit dédié à l’année, et vu notamment que la clé étrangère ne permet pas de verrouiller, on peut donc se passer de l’entité-type ANNEE, et mettre en oeuvre dans CONTRAT un attribut (de type DATE) permettant de savoir à quelle date un contrat a été créé. Si la date de création du contrat est égale à sa date d’effet on utilise la date d’effet, sinon on crée un attribut contratDateCreation. A priori, l’année serait par exemple celle de la date du jour : YEAR(GETDATE()) et donc calculable automatiquement. (N.B. Eviter de créer des contrats vers minuit le 31 décembre...)

A ce niveau, nous pouvons prendre la date d'émission du contrat car un contrat peut être émis aujourd'hui et prend effet 20/02/2021 par exemple. Mais la date d'émission est la date à laquelle le contrat a été produit. contratdateemission.

Pour ce qui concerne les flottes, j'ai jugé nécessaire de mettre dans l'entité catégorie car à ce niveau, chaque catégorie possède (0,n) catégoriecomplement et un contrat est déterminé par une et une seule catégorie. Et je reviens sur l'une de vos remarques prétendante en liant le contrat au véhicule.
Quelles sont vos remarques et suggestions?
Une fois de plus merci d'avance.
Regarder le premier schéma et son code sql et ensuite le second.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE PRODUCTIONAN(
   an CHAR(4),
   anmini DATE NOT NULL,
   anmaxi DATE NOT NULL,
   PRIMARY KEY(an)
);

CREATE TABLE CONTRAT(
   an CHAR(4),
   contratId COUNTER,
   codecontrat CHAR(5) NOT NULL,
   PRIMARY KEY(an, contratId),
   FOREIGN KEY(an) REFERENCES PRODUCTIONAN(an)
);

Petit schéma
Voici le code sql et le MCD du model complet.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
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 GARANTIE(
   garantieId INT IDENTITY,
   codegarantie CHAR(1) NOT NULL,
   Libelle_Garantie VARCHAR(50) NOT NULL,
   PRIMARY KEY(garantieId),
   UNIQUE(codegarantie)
);

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

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

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

CREATE TABLE MODEPAIEMENT(
   modepaiemenId INT IDENTITY,
   modepaiementcode VARCHAR(15) NOT NULL,
   modepaiement_libelle VARCHAR(50),
   PRIMARY KEY(modepaiemenId),
   UNIQUE(modepaiementcode)
);

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

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

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

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

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

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

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

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

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

CREATE TABLE BRANCHE(
   brancheId INT IDENTITY,
   branchecode CHAR(3) NOT NULL,
   libelle_branche VARCHAR(50),
   PRIMARY KEY(brancheId),
   UNIQUE(branchecode)
);

CREATE TABLE ANNEE(
   AN DATE,
   andebut DATE,
   PRIMARY KEY(AN)
);

CREATE TABLE ANNEE_HISTO(
   AN DATE,
   anneehistoId INT IDENTITY,
   annee_histofin DATE,
   PRIMARY KEY(AN, anneehistoId),
   UNIQUE(AN),
   FOREIGN KEY(AN) REFERENCES ANNEE(AN)
);

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

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 CAISSIERE(
   agenceId_1 INT,
   caissiereId INT IDENTITY,
   codecaissiere VARCHAR(4) NOT NULL,
   prenomcaissiere VARCHAR(40) NOT NULL,
   nomcaissiere VARCHAR(40) NOT NULL,
   agenceId INT,
   producteurId INT,
   PRIMARY KEY(agenceId_1, caissiereId),
   UNIQUE(codecaissiere),
   FOREIGN KEY(agenceId_1) REFERENCES AGENCE(agenceId),
   FOREIGN KEY(agenceId, producteurId) REFERENCES PRODUCTEUR(agenceId, producteurId)
);

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

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

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

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

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

CREATE TABLE ENCAISSEMENT_C(
   AN DATE,
   categorieId INT,
   agenceId INT,
   contratId INT,
   encaisseCtrId INT IDENTITY,
   encaisseCtrMontant INT,
   encaisseCtrDate DATE,
   modepaiemenId INT NOT NULL,
   agenceId_1 INT NOT NULL,
   caissiereId INT NOT NULL,
   PRIMARY KEY(AN, categorieId, agenceId, contratId, encaisseCtrId),
   FOREIGN KEY(AN, categorieId, agenceId, contratId) REFERENCES CONTRAT(AN, categorieId, agenceId, contratId),
   FOREIGN KEY(modepaiemenId) REFERENCES MODEPAIEMENT(modepaiemenId),
   FOREIGN KEY(agenceId_1, caissiereId) REFERENCES CAISSIERE(agenceId_1, caissiereId)
);

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

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

MCD

**fsmrel** · 25/01/2021, 02h12

Bonsoir Zidane7,

A propos de l’automatisation de la création du code contrat d’un contrat

Toujours avant d’en venir aux flottes, je vous propose un moyen pour incrémenter automatiquement les numéros de contrats dans la table CONTRAT (je vais supposer que l’on nomme numéro de série les 5 derniers caractères de l’attribut codeContrat). Pour cela on crée une fonction (cf. CONTRAT_SEQUENCE_FN ci-dessous). Mettons-nous dans la situation où l’on veut créer le contrat C pour l’agence G et l’année A. On tient compte du fait que la table CONTRAT peut ne pas encore contenir de contrat pour G, auquel cas le numéro de série pour C prendra la valeur '00001'. Sinon, soit A’ l’année du dernier contrat engrangé pour l’agence concernée G. Si A = A’ + 1, alors on considère que l’on vient de changer d’année et le numéro de série pour C prendra là aussi la valeur '00001'. Par contre, si A = A’, alors on se contente d’incrémenter d’une unité le numéro de série. On considère que si l’année A ne vérifie pas A = A’ ou A = A’ + 1, alors il y a erreur et le numéro de série sera forcé à la valeur '?????' : le SGBD sera prié de rejeter l’INSERT en cause. Pour forcer l’INSERT à tout prix, il faudra le faire sans faire appel à la fonction CONTRAT_SEQUENCE_FN.

Passons à SQL (je me base sur votre MCD du post #142).

Des catégories :

CREATE TABLE CATEGORIE
(
        categorieId        INT IDENTITY
      , categorieCode      CHAR(3) NOT NULL
      , categorieLibelle   VARCHAR(64) NOT NULL
    , CONSTRAINT CATEGORIE_PK PRIMARY KEY(categorieId)
    , CONSTRAINT  CATEGORIE_AK UNIQUE(categorieCode)
);

INSERT INTO CATEGORIE (categorieCode, categorieLibelle)
VALUES
    ('110', 'Affaires et promenades (AP)')
  , ('210', 'Transport pour le compte de l''assuré (TPC)')
  , ('310', 'Transport public de marchandises (TPM)')
  , ('410', 'Taxis')
  , ('550', 'Motos (VP)')
;
SELECT categorieCode, categorieLibelle
FROM   CATEGORIE ;

=>

categorieCode   categorieLibelle

110             Affaires et promenades (AP)
210             Transport pour le compte de l'assuré (TPC)
310             Transport public de marchandises (TPM)
410             Taxis
550             Motos (VP)

Des agences :

CREATE TABLE AGENCE
(
        agenceId INT IDENTITY 
      , agenceCode         CHAR(3)       NOT NULL
      , agenceNom          VARCHAR(48)   NOT NULL
    , PRIMARY KEY(agenceId)
    , UNIQUE(agenceCode)
);

INSERT INTO AGENCE (agenceCode, agenceNom)
VALUES 
    ('210', 'Siège')
  , ('33K', 'agence K')
  , ('44Y', 'agence Y')
;
SELECT agenceCode, agenceNom FROM AGENCE ;

=>

agenceCode   agenceNom
210          Siège
333          agence A
444          agence B

Les contrats :

Quelques remarques préalables concernant la table CONTRAT.

(1) Je ne reprends pas tous les attributs de votre table CONTRAT, je me limite ici à ce qui est nécessaire pour la suite.

(2) Concernant le type de contrat :
J’avais suggéré l’utilisation du type BIT pour l’attribut typeContrat. En fait, on se ferme la porte à la mise en oeuvre à l’avenir d’un 3e (4e, etc.) type de contrat. Je vous propose donc d’utiliser le type CHAR(1), que l’on va contraindre aujourd’hui à prendre seulement les deux valeurs 'M' (pour les contrats "mono") et 'F' (pour les contrats "flotte"), voyez la contrainte CONTRAT_TYPE_CHK.

(3) L’attribut contratDateEmission remplace votre attribut AN. A un moment vous cherchiez à exprimer des bornes, c’est-à-dire une période. En attendant des éclaircissements de votre part, je considère l’entité-type ANNEE comme inutile, et je propose de définir des bornes directement dans la table CONTRAT au moyen de la contrainte CONTRAT_EMISSION_BORNES, exprimant par exemple l’intervalle d’années <1974, année en cours>. L’année en cours est obrenue au moyen de la fonction GETDATE().

(4) Dans votre code SQL, l’attribut codeContrat est défini comme étant du type VARCHAR(10). Il faudrait remplacer ce type par CHAR(13). En effet, le code agence occupe 3 octets, le code catégorie occupe 3 octets et l’année occupe 2 octets : il ne resterait alors que 2 octets sur 10 pour le numéro de série...

(5) La contrainte CONTRAT_SERIE_CHK permet de s’assurer que le numéro de série est numérique. Vous me direz que cette contrainte est a priori inutile puisque c’est la fonction CONTRAT_SEQUENCE_FN qui en fait le calcul. Mais comme écrit au début de ce message, si la fonction détecte une anomalie, alors elle provoque une erreur en forçant le numéro de série à la valeur '?????' : le SGBD sera donc prié de rejeter l’INSERT en cause. Pour forcer la création malgré tout, il faudra le faire sans que l’INSERT ad-hoc fasse appel à la fonction CONTRAT_SEQUENCE_FN.

La table CONTRAT :

CREATE TABLE CONTRAT 
(
        contratId            INT  IDENTITY
      , contratCode          VARCHAR(13)   NOT NULL
      , contratDateEmission  DATE          NOT NULL 
      , contratDateEffet     DATE          NOT NULL
      , contratType          CHAR(1)       NOT NULL
      , agenceId             INT           NOT NULL 
      , categorieId          INT           NOT NULL 
    , CONSTRAINT CONTRAT_PK PRIMARY KEY(contratId)
    , CONSTRAINT CONTRAT_AK UNIQUE(contratCode)
    , CONSTRAINT CONTRAT_AGENCE_FK FOREIGN KEY (agenceId)
          REFERENCES AGENCE (agenceId)
    , CONSTRAINT CONTRAT_CATEGORIE_FK FOREIGN KEY (categorieId)
          REFERENCES CATEGORIE (categorieId)
    , CONSTRAINT CONTRAT_EMISSION_BORNES_CHK 
          CHECK (YEAR(contratDateEmission) BETWEEN '1974' AND YEAR(GETDATE()))
    , CONSTRAINT CONTRAT_DATE_EFFET_CHK 
          CHECK (contratDateEffet >= contratDateEmission)
    , CONSTRAINT CONTRAT_TYPE_CHK 
          CHECK (UPPER(contratType) IN ('M', 'F'))
   , CONSTRAINT CONTRAT_SERIE_CHK 
          CHECK (ISNUMERIC(RIGHT(contratCode,5)) = 1)
) ;

La fonction permettant d’automatiser l’incrémentation des numéros de série

A l’occasion de la création d’un contrat, on utilise la fonction CONTRAT_SEQUENCE_FN se chargeant de l’incrémentation.

On lui passe en entrée le code agence, le code catégorie et l’année d’émission du contrat.

En sortie, la fonction fournit la valeur du code contrat (incrémenté) à créer.

GO
CREATE FUNCTION CONTRAT_SEQUENCE_FN(@agenceCode as CHAR(3)
                                  , @categorieCode as CHAR(3)
                                  , @anneeEmission as CHAR(2))      
    RETURNS CHAR(13)  
AS   
BEGIN
DECLARE @n INT ;
DECLARE @@contratCodeLen INT = 13 ;
DECLARE @contratCode CHAR(13) ;
DECLARE @contratCodePrecedent CHAR(13) ;
DECLARE @contratCodePrecedentAlfa CHAR(13) ;
DECLARE @SequenceDebutAnnee CHAR(5) = '00001' ;
DECLARE @SequenceContratPrecedent CHAR(5) ;
DECLARE @SequenceContratPrecedentNum INT ;
DECLARE @seqLen as INT = 5
DECLARE @SequenceContrat CHAR(5) ;
DECLARE @SequenceContratNum INT ;
DECLARE @agenceId INT ; 
DECLARE @categorieId INT ;
DECLARE @anneeContratOffset INT = 7
DECLARE @anneeContratPrecedent CHAR(2) ;
DECLARE @zero CHAR(5) = '00000' ;
DECLARE @AnneeErreur CHAR(5) = '?????' ;

SET @agenceId = (SELECT agenceId FROM AGENCE WHERE agenceCode = @agenceCode)
SET @categorieId = (SELECT categorieId FROM CATEGORIE WHERE categorieCode = @categorieCode)
SET @contratCode = CONCAT(@agenceCode, @categorieCode, @anneeEmission, @AnneeErreur)

----------------------------------------------------
-- On regarde si la table CONTRAT contient au moins 
-- un contrat pour l’agence concernée.
----------------------------------------------------

SET @n = (SELECT COUNT(*) FROM CONTRAT WHERE agenceId = @agenceId) ;

-----------------------------------------------------
-- Si la table CONTRAT contient au moins un contrat
-- pour l'agence concernée, on recherche l'année 
-- du dernier contrat que la table contient pour 
-- cette agence.
-----------------------------------------------------

IF @n > 0 
    BEGIN
        SET @contratCodePrecedent = 
              (SELECT MAX(contratCode)
               FROM   CONTRAT 
               WHERE  agenceId = @agenceId)

        SET @anneeContratPrecedent = SUBSTRING(@contratCodePrecedent, @anneeContratOffset, 2) ;
   END

-----------------------------------------------------
-- Si pour l’agence concernée, le dernier contrat 
-- créé Cn est de la même année que celui qui est à
-- créer, on incrémente d'une unité le numéro de 
-- police récupéré dans le contrat Cn 
-- (variable @SequenceContrat). 
-----------------------------------------------------

IF  @n > 0 AND @anneeEmission = @anneeContratPrecedent 
    BEGIN
        SET @SequenceContratPrecedent = RIGHT(@contratCodePrecedent, @seqLen) ;
        SET @SequenceContratPrecedentNum = CAST(@SequenceContratPrecedent as INT) ;

        SET @SequenceContratNum = 1 + @SequenceContratPrecedentNum
        SET @SequenceContrat = CAST(@SequenceContratNum as CHAR(5))
        SET  @contratCode = 
               CONCAT
              (
               @agenceCode
             , @categorieCode
             , @anneeEmission
             , LEFT(@zero, LEN(@zero) - LEN(@SequenceContrat))
             , @SequenceContrat
              )
    END 
ELSE

---------------------------------------------------------
-- Si la table CONTRAT ne contient aucun contrat pour
-- l’agence concernée, ou si la date d'émission du 
-- contrat à créer est égale à 1 + celle du dernier 
-- contrat créé pour cette agence, c'est-à-dire si on 
-- commence une nouvelle année, alors on force le numéro  
-- de police à 1 pour le contrat à créer.
---------------------------------------------------------

IF  @n = 0 OR @anneeEmission = @anneeContratPrecedent + 1 
    BEGIN
           SET @contratCode = 
               CONCAT(@agenceCode
             , @categorieCode
             , @anneeEmission
             , @SequenceDebutAnnee)
    END

RETURN @contratCode 
 
END ;
GO

Création de contrats.

La table CONTRAT est vide. Pour amorcer la pompe, on crée « en dur » un 1er contrat pour l’agence 210, émis en 2020, de numéro de série '12345' :

INSERT INTO CONTRAT  (contratCode, contratDateEmission, contratDateEffet, contratType
                   , agenceId, categorieId)
VALUES
(
         '2105502012345'  
       , '2020-12-27', '2020-12-28', 'M'
       , (SELECT agenceId FROM AGENCE WHERE agenceCode = '210')
       , (SELECT categorieId FROM CATEGORIE WHERE categorieCode = '550')
) ;
 
SELECT contratCode, agenceCode, categorieCode
     , contratDateEmission, contratDateEffet, UPPER(contratType) as contratType 
FROM   CONTRAT as x
  JOIN AGENCE as y ON x.agenceId = y.agenceId
  JOIN CATEGORIE as z ON x.categorieId = z.categorieId 
;

=>

contratCode     agenceCode   categorieCode   contratDateEmission   contratDateEffet   contratType
 
2105502012345   210          550             2020-12-27            2020-12-28         M

On crée un deuxième contrat pour l’agence 210, à la date d’aujourd’hui (24 janvier 2021), donc avec changement d’année (passage de 2020 à 2021), en demandant à la fonction CONTRAT_SEQUENCE_FN de se charger de calculer le numéro de série :

INSERT INTO CONTRAT (contratCode, contratDateEmission, contratDateEffet, contratType
                   , agenceId, categorieId)
VALUES
(
         dbo.CONTRAT_SEQUENCE_FN('210', '550', RIGHT(YEAR(GETDATE()), 2))
       , ' 2021-01-25', ' 2021-01-25', 'M'
       , (SELECT agenceId FROM AGENCE WHERE agenceCode = '210')
       , (SELECT categorieId FROM CATEGORIE WHERE categorieCode = '550')
) ; 

SELECT contratCode, agenceCode, categorieCode
     , contratDateEmission, contratDateEffet, UPPER(contratType) as contratType 
FROM   CONTRAT as x
  JOIN AGENCE as y ON x.agenceId = y.agenceId
  JOIN CATEGORIE as z ON x.categorieId = z.categorieId 
;

=>

contratCode     agenceCode   categorieCode   contratDateEmission   contratDateEffet   contratType
2105502012345   210          550             2020-12-27            2020-12-28         M
2105502100001   210          550             2021-01-25            2021-01-25         M

On crée un 1er contrat pour l’agence 33K, à la date d’aujourd’hui (24 janvier 2021), en demandant à la fonction CONTRAT_SEQUENCE_FN de se charger de calculer le numéro de série :

INSERT INTO CONTRAT (contratCode, contratDateEmission, contratDateEffet, contratType
                   , agenceId, categorieId)
VALUES
(
         dbo.CONTRAT_SEQUENCE_FN('210', '550', RIGHT(YEAR(GETDATE()), 2))
       , '2021-01-25', '2021-01-25', 'M'
       , (SELECT agenceId FROM AGENCE WHERE agenceCode = '33K')
       , (SELECT categorieId FROM CATEGORIE WHERE categorieCode = '550')
) ;
 
SELECT contratCode, agenceCode, categorieCode
     , contratDateEmission, contratDateEffet, UPPER(contratType) as contratType 
FROM   CONTRAT as x
  JOIN AGENCE as y ON x.agenceId = y.agenceId
  JOIN CATEGORIE as z ON x.categorieId = z.categorieId 
;

=>

contratCode     agenceCode   categorieCode   contratDateEmission   contratDateEffet   contratType
2105502012345   210          550             2020-12-27            2020-12-28         M
2105502100001   210          550             2021-01-25            2021-01-25         M
33K5502100001   33K          550             2021-01-25            2021-01-25         M

On crée un 3e contrat pour l’agence 210, toujours à la date d’aujourd’hui, il s’agit donc du 2e contrat pour cette agence en 2021, en demandant à la fonction CONTRAT_SEQUENCE_FN de se charger de calculer le numéro de série :

INSERT INTO CONTRAT (contratCode, contratDateEmission, contratDateEffet, contratType
                   , agenceId, categorieId)
VALUES
(
          dbo.CONTRAT_SEQUENCE_FN('210', '550', RIGHT(YEAR(GETDATE()), 2))
       , '2021-01-25', '2021-01-25', 'M'
       , (SELECT agenceId FROM AGENCE WHERE agenceCode = '210')
       , (SELECT categorieId FROM CATEGORIE WHERE categorieCode = '110')
) ;
 
SELECT contratCode, agenceCode, categorieCode
     , contratDateEmission, contratDateEffet, UPPER(contratType) as contratType 
FROM   CONTRAT as x
  JOIN AGENCE as y ON x.agenceId = y.agenceId
  JOIN CATEGORIE as z ON x.categorieId = z.categorieId 
;

=>

contratCode     agenceCode   categorieCode   contratDateEmission   contratDateEffet   contratType
2105502012345   210          550             2020-12-27            2020-12-28         M
2105502100001   210          550             2021-01-25            2021-01-25         M
33K5502100001   33K          550             2021-01-25            2021-01-25         M
2101102100002   210          110             2021-01-25            2021-01-25         M

On demande la création d’un contrat pour l’agence 210, pour l’année 2019, mais du fait des contrats déjà créés pour cette agence, la routine n’accepte que les créations pour l’année 2021, voire 2022 (1er contrat pour cette année)

 INSERT INTO CONTRAT (contratCode, contratDateEmission, contratDateEffet, contratType
                   , agenceId, categorieId)
VALUES
(
          dbo.CONTRAT_SEQUENCE_FN('210', '550', RIGHT(YEAR(GETDATE()), 2))
       , '2021-01-25', '2021-01-25', 'M'
       , (SELECT agenceId FROM AGENCE WHERE agenceCode = '210')
       , (SELECT categorieId FROM CATEGORIE WHERE categorieCode = '550')
)
;

=>

Msg 547, Niveau 16, État 0, Ligne 345
L'instruction INSERT est en conflit avec la contrainte CHECK "CONTRAT_SERIE_CHK". Le conflit s'est produit dans la base de données "test", table "dbo.CONTRAT", column 'contratCode'.

Le débat est ouvert quant à l’approche que j’ai proposée et qui ne prétend pas à plus..

En tout cas, les sont favorablement acceptés...

**fsmrel** · 25/01/2021, 14h09

Bonjour Zidane7,

Ce que j’ai proposé dans mon précédent message n’est qu’une base de discussion. La fonction CONTRAT_SEQUENCE_FN est à améliorer, car par exemple les catégories risquent de ficher la patouille dans le séquencement, je vais regarder ça de plus près.

**fsmrel** · 25/01/2021, 20h45

Bonsoir Zidane7,

Envoyé par fsmrel

Ce que j’ai proposé dans mon précédent message n’est qu’une base de discussion. La fonction CONTRAT_SEQUENCE_FN est à améliorer, car par exemple les catégories risquent de ficher la patouille dans le séquencement, je vais regarder ça de plus près.

Effectivement, le séquencement laisse à désirer : je propose une version améliorée de la fonction CONTRAT_SEQUENCE_FN.

GO
CREATE FUNCTION CONTRAT_SEQUENCE_FN(@agenceCode as CHAR(3)
                                  , @categorieCode as CHAR(3)
                                  , @anneeEmission as CHAR(2))
    RETURNS CHAR(13)  
AS   
BEGIN
  
DECLARE @n INT = 0 ;
DECLARE @kount INT ;
DECLARE @agenceId INT ; 
DECLARE @categorieId INT ;
DECLARE @@contratCodeLen INT = 13 ;
DECLARE @contratCode CHAR(13) ;
DECLARE @contratCodePrecedent CHAR(13) ;
DECLARE @SequenceDebutAnnee CHAR(5) = '00001' ;
DECLARE @SequenceContrat CHAR(5) ;
DECLARE @SequenceContratNum INT ;
DECLARE @SequenceContratPrecedent CHAR(5) ;
DECLARE @SequenceContratPrecedentNum INT ;
DECLARE @seqLen as INT = 5
DECLARE @anneeContratOffset INT = 7
DECLARE @anneeMax  INT ;
DECLARE @anneeContratPrecedent CHAR(2) ;
DECLARE @zero CHAR(5) = '00000' ;
DECLARE @anneeErreur CHAR(5) = '?????' ;
DECLARE @errDatePassee BIT = 0

SET @agenceId = (SELECT agenceId FROM AGENCE WHERE agenceCode = @agenceCode)
SET @categorieId = (SELECT categorieId FROM CATEGORIE WHERE categorieCode = @categorieCode)       
SET @contratCode = CONCAT(@agenceCode, @categorieCode, @anneeEmission, @anneeErreur)

----------------------------------------------------
-- On regarde si la table CONTRAT contient au moins 
-- un contrat pour l’agence et l'année concernées.
----------------------------------------------------

SET @anneeMax = (SELECT MAX(SUBSTRING(contratCode, @anneeContratOffset, 2))
                 FROM   CONTRAT 
                 WHERE  agenceId = @agenceId)

IF @anneeEmission < @anneeMax 
    BEGIN
        SET @errDatePassee = 1
        SET @contratCode = 
            CONCAT(@agenceCode
          , @categorieCode
          , @anneeEmission
          , @anneeErreur)
    END

ELSE
    BEGIN
        SET @n = (SELECT COUNT(*) 
                  FROM   CONTRAT 
                  WHERE  agenceId = @agenceId
                    AND  SUBSTRING(contratCode, @anneeContratOffset, 2) = @anneeEmission
                 ) ;
    END

-----------------------------------------------------
-- Si la table CONTRAT contient au moins un contrat
-- pour l'agence et l'année concernées, on récupère 
-- dans le dernier contrat créé correspondant :
--   son numéro de série, 
--   son code contrat, 
--   sa date d'émission.
-----------------------------------------------------

IF @n > 0 
    BEGIN
        SET @SequenceContratPrecedent = 
              (SELECT MAX(RIGHT(contratCode,5))
               FROM   CONTRAT 
               WHERE  agenceId = @agenceId AND  SUBSTRING(contratCode, 7,2) = @anneeEmission)

        SET @contratCodePrecedent = 
              (SELECT contratCode
               FROM   CONTRAT 
               WHERE  agenceId = @agenceId And right(contratCode,5) = @SequenceContratPrecedent)

        SET @anneeContratPrecedent = SUBSTRING(@contratCodePrecedent, @anneeContratOffset, 2) ;
   END

-----------------------------------------------------
-- Si pour l’agence concernée, le dernier contrat 
-- créé Cn est de la même année que celui qui est à
-- créer, on incrémente d'une unité le numéro de 
-- police récupéré dans le contrat Cn 
-- (variable @SequenceContrat). 
-----------------------------------------------------

IF  @n > 0 AND @anneeEmission = @anneeContratPrecedent 
    BEGIN
        SET @SequenceContratPrecedent = RIGHT(@contratCodePrecedent, @seqLen) ;
        SET @SequenceContratPrecedentNum = CAST(@SequenceContratPrecedent as INT) ;

        SET @SequenceContratNum = 1 + @SequenceContratPrecedentNum
        SET @SequenceContrat = CAST(@SequenceContratNum as CHAR(5))
        SET  @contratCode = 
               CONCAT
              (
               @agenceCode
             , @categorieCode
             , @anneeEmission
             , LEFT(@zero, LEN(@zero) - LEN(@SequenceContrat))
             , @SequenceContrat
              )
    END 
ELSE

--------------------------------------------------------------
-- Si la table CONTRAT ne contient aucun contrat pour
-- l’agence et l'année concernées, ou si la date d'émission 
-- du contrat à créer est égale à 1 + celle du dernier 
-- contrat créé pour cette agence, c'est-à-dire si on 
-- commence une nouvelle année, alors on force le numéro  
-- de police à 1 pour le contrat à créer.
--------------------------------------------------------------

IF  @n = 0 AND @errDatePassee = 0 OR @anneeEmission = @anneeContratPrecedent + 1 
    BEGIN
           SET @contratCode = 
               CONCAT(@agenceCode
             , @categorieCode
             , @anneeEmission
             , @SequenceDebutAnnee)
    END

RETURN @contratCode 
 
END ;

GO

Comme précédemment, les retours dans le passé sont interdits (création de contrats pour les années précédentes). A vous de juger de la pertinence de ce genre d’interdiction.

**escartefigue** · 26/01/2021, 09h02

Bonjour,

Envoyé par fsmrel

En tout cas, les sont favorablement acceptés...

En effet ! Quelques encouragements sont toujours les bienvenus
@Zidane7 : il y a du retard à rattraper, mais il n'est jamais trop tard pour bien faire

**fsmrel** · 27/01/2021, 18h00

Bonsoir Zidane7,

A propos des contrats "mono" et "flotte".

Vous avez écrit (post #121) :

Envoyé par Zidane7

Et si c'est une flotte, nous remplaçons le code catégorie par code flotte.

Autrement dit, dans le code contrat on ne peut pas faire figurer un code catégorie pour un contrat de type "flotte", puisqu’un tel contrat assure un ensemble de véhicules pouvant appartenir à différentes catégories. Par contre, un contrat de type "mono" appartient nécessairement à une et une seule catégorie, donc là, pas de problème.

Vous avez aussi écrit (post #133) :

Envoyé par Zidane7

J'ai preferé mettre le type de flotte dans l'entité contrat car à ce niveau, il ne se manifeste une seule fois et aussi lié le contrat à l'entité catégorie.

Hum... Puisque vous évoquez un type de flotte, il doit donc y avoir une entité-type, appelons-la par exemple FLOTTE_TYPE, et la table SQL FLOTTE_TYPE contiendrait les valeurs possibles pour les contrats de type "flotte". Est-ainsi qu’il faut voir les choses ?

Dans votre dernier MCD (post #142), l’entité-type CONTRAT est identifiée relativement à l’entité-type CATEGORIE, donc chaque contrat "flotte" appartiendrait nécessairement à une catégorie : il y a là une contradiction en perspective, puisqu’un contrat "flotte" assure des véhicules a priori de catégories différentes.

Tout ce qui précède milite en faveur de la spécialisation de contrats :

Contrat "mono" d’une part, assurant un et un seul véhicule et faisant référence à une et une seule catégorie ;
Contrat "flotte" d’autre part, assurant au moins un et au plus plusieurs véhicules. Les catégories d’un contrat de ce type sont a priori celles des véhicules qu’il assure.

MCD correspondant

Nom : Zidane7_assurance_auto (18)contrats(loo).png
Affichages : 198
Taille : 42,3 Ko

Dans ce MCD, l’entité-type CONTRAT a été spécialisée en contrats "mono" (entité-type CONTRAT_MONO) et contrats "flotte" (entité-type CONTRAT_FLOTTE). La contrainte de partitionnement (totalité et exclusion) « XT » a pour objet de signifier qu’un véhicule est assuré par un contrat soit du type "mono", soit du type "flotte".

Remarque : dans la mesure où un contrat donné "flotte" C ferait référence à un type de flotte donné F, et que F ne serait compatible qu’avec certaines catégories, on pourrait alors mettre en oeuvre une association A entre les entités-types CATEGORIE et FLOTTE_TYPE afin de pouvoir contrôler que les véhicules assurés par C appartiennent bien à des catégories appartenant à A. Quelle est votre position à ce sujet ?

Dans la série bien connue « boucles dans le MCD » :

Via le chemin VEHICULE > VEH_MONO > CONTRAT_MONO > CONTRAT_CAT > CATEGORIE, le véhicule V peut transitivement faire référence à une catégorie C1.
Via le chemin VEHICULE > VE_CATCOMP > CAT_COMPLEMENT > CAT_COMP > CATEGORIE, ce même véhicule V peut transitivement faire référence à une autre catégorie C2. Au stade SQL, on devra mettre en oeuvre un trigger ou une fonction pour empêcher cela.

**Zidane7** · 27/01/2021, 18h10

Bonsoir Monsieur fsmrel,

fsmrel
(2) Concernant le type de contrat :
J’avais suggéré l’utilisation du type BIT pour l’attribut typeContrat. En fait, on se ferme la porte à la mise en oeuvre à l’avenir d’un 3e (4e, etc.) type de contrat. Je vous propose donc d’utiliser le type CHAR(1), que l’on va contraindre aujourd’hui à prendre seulement les deux valeurs 'M' (pour les contrats "mono") et 'F' (pour les contrats "flotte"), voyez la contrainte CONTRAT_TYPE_CHK.

Je vous avais dit que le type de contrat est placé dans l'entité catégorie car on a considéré que ces types ont presque les mêmes caractéristiques que les catégories, tels que le code, et libelle.

fsmrel
3) L’attribut contratDateEmission remplace votre attribut AN

Cela est bon.

A un moment vous cherchiez à exprimer des bornes, c’est-à-dire une période. En attendant des éclaircissements de votre part, je considère l’entité-type ANNEE comme inutile, et je propose de définir des bornes directement dans la table CONTRAT au moyen de la contrainte CONTRAT_EMISSION_BORNES, exprimant par exemple l’intervalle d’années <1974, année en cours>. L’année en cours est obrenue au moyen de la fonction GETDATE().

C'est vraiment génial.

(4) Dans votre code SQL, l’attribut codeContrat est défini comme étant du type VARCHAR(10). Il faudrait remplacer ce type par CHAR(13). En effet, le code agence occupe 3 octets, le code catégorie occupe 3 octets et l’année occupe 2 octets : il ne resterait alors que 2 octets sur 10 pour le numéro de série...

Ici je l'ai remplacé par 14.

fsmrel
Comme précédemment, les retours dans le passé sont interdits (création de contrats pour les années précédentes). A vous de juger de la pertinence de ce genre d’interdiction.

A ce niveau, il peut y avoir des agences qui ne souscrivent pas directement et cela peut coïncider à la fin d'année et à l'année A+1 ou 2 jours on veut enregister les contrats de l'année A-1, que faire en ce moment?
Merci pour l'effort fournit.
NB: Vous avez écrit au moment que je cherchais à repondre à vos messages des postes #143 et #145.
Une fois de plus merci.
Je vous montre le MCD et son code sql:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
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 GARANTIE(
   garantieId INT IDENTITY,
   codegarantie CHAR(1) NOT NULL,
   Libelle_Garantie VARCHAR(50) NOT NULL,
   PRIMARY KEY(garantieId),
   UNIQUE(codegarantie)
);

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

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

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,
   modepaiementcode VARCHAR(15) NOT NULL,
   modepaiement_libelle VARCHAR(50),
   PRIMARY KEY(modepaiemenId),
   UNIQUE(modepaiementcode)
);

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

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

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

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

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

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

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

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

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

CREATE TABLE BRANCHE(
   brancheId INT IDENTITY,
   branchecode CHAR(3) NOT NULL,
   libelle_branche VARCHAR(50),
   PRIMARY KEY(brancheId),
   UNIQUE(branchecode)
);

CREATE TABLE OPTIONSECURITE(
   optionsecuriteId VARCHAR(50),
   codeoptionsecurite VARCHAR(1) NOT NULL,
   optionmontant INT,
   garantieId INT NOT NULL,
   PRIMARY KEY(optionsecuriteId),
   UNIQUE(codeoptionsecurite),
   FOREIGN KEY(garantieId) REFERENCES GARANTIE(garantieId)
);

CREATE TABLE OPTIONVALEUR(
   optionvaleurId VARCHAR(50),
   optionvaleurlibelle VARCHAR(30),
   optionvaleurmontant INT NOT NULL,
   optionsecuriteId VARCHAR(50) NOT NULL,
   PRIMARY KEY(optionvaleurId),
   FOREIGN KEY(optionsecuriteId) REFERENCES OPTIONSECURITE(optionsecuriteId)
);

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

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 CAISSIERE(
   agenceId_1 INT,
   caissiereId INT IDENTITY,
   codecaissiere VARCHAR(4) NOT NULL,
   prenomcaissiere VARCHAR(40) NOT NULL,
   nomcaissiere VARCHAR(40) NOT NULL,
   agenceId INT,
   producteurId INT,
   PRIMARY KEY(agenceId_1, caissiereId),
   UNIQUE(codecaissiere),
   FOREIGN KEY(agenceId_1) REFERENCES AGENCE(agenceId),
   FOREIGN KEY(agenceId, producteurId) REFERENCES PRODUCTEUR(agenceId, producteurId)
);

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

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

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

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

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

CREATE TABLE ENCAISSEMENT_C(
   categorieId INT,
   agenceId INT,
   contratId INT,
   encaisseCtrId INT IDENTITY,
   encaisseCtrMontant INT,
   encaisseCtrDate DATE,
   modepaiemenId INT NOT NULL,
   agenceId_1 INT NOT NULL,
   caissiereId INT NOT NULL,
   PRIMARY KEY(categorieId, agenceId, contratId, encaisseCtrId),
   FOREIGN KEY(categorieId, agenceId, contratId) REFERENCES CONTRAT(categorieId, agenceId, contratId),
   FOREIGN KEY(modepaiemenId) REFERENCES MODEPAIEMENT(modepaiemenId),
   FOREIGN KEY(agenceId_1, caissiereId) REFERENCES CAISSIERE(agenceId_1, caissiereId)
);

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

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

**fsmrel** · 28/01/2021, 01h30

Bonsoir à nouveau,

Envoyé par Zidane7

Ici je l'ai remplacé par 14.
[...]
à l'année A+1 ou 2 jours on veut enregistrer les contrats de l'année A-1.

La structure du code contrat étant invariante, autant remplacer VARCHAR(14) par CHAR(14). Il serait bien qu’Escartefigue donne son avis.

Je note que passer à 14 octets fait que le numéro de série doit passer à 6 octets.

La fonction CONTRAT_SEQUENCE_FN tient compte de ce passage à 14 octets (donc 6 octets pour le numéro de série).

Etant donné que l’utilisateur doit pouvoir créer des contrats pour l’année précédente, dans la fonction je supprime les instructions empêchant cela.

GO
CREATE FUNCTION CONTRAT_SEQUENCE_FN(@agenceCode as CHAR(3)
                                  , @categorieCode as CHAR(3)
                                  , @anneeEmission as CHAR(2)) 
    RETURNS CHAR(14)  
AS   
BEGIN
  
DECLARE @n INT = 0 ;
DECLARE @agenceId INT ; 
DECLARE @categorieId INT ;
DECLARE @@contratCodeLen INT = 14 ;
DECLARE @contratCodeNouveau CHAR(14) ;
DECLARE @contratCodeNew CHAR(14) ;
DECLARE @contratCodePrecedent CHAR(14) ;
DECLARE @SequenceDebutAnnee CHAR(6) = '000001' ;
DECLARE @SequenceContrat CHAR(6) ;
DECLARE @SequenceContratNum INT ;
DECLARE @SequenceContratPrecedent CHAR(6) ;
DECLARE @SequenceContratPrecedentNum INT ;
DECLARE @seqLen as INT = 6
DECLARE @anneeContratOffset INT = 7
DECLARE @anneeMax  INT ;
DECLARE @anneeContratPrecedent CHAR(2) ;
DECLARE @zero CHAR(6) = '000000' ;
DECLARE @anneeErreur CHAR(6) = '??????' ;

SET @agenceId = (SELECT agenceId FROM AGENCE WHERE agenceCode = @agenceCode)
SET @categorieId = (SELECT categorieId FROM CATEGORIE WHERE categorieCode = @categorieCode)       
SET @contratCodeNouveau = CONCAT(@agenceCode, @categorieCode, @anneeEmission, @anneeErreur)

----------------------------------------------------
-- On regarde si la table CONTRAT contient au moins 
-- un contrat pour l’agence et l'année concernées.
----------------------------------------------------

SET @anneeMax = (SELECT MAX(SUBSTRING(contratCode, @anneeContratOffset, 2))
                 FROM   CONTRAT 
                 WHERE  agenceId = @agenceId) ;

SET @n = (SELECT COUNT(*) 
          FROM   CONTRAT 
          WHERE  agenceId = @agenceId
            AND  SUBSTRING(contratCode, @anneeContratOffset, 2) = @anneeEmission) ;

-----------------------------------------------------
-- Si la table CONTRAT contient au moins un contrat
-- pour l'agence et l'année concernées, on récupère 
-- dans le dernier contrat créé correspondant :
--   son numéro de série, 
--   son code contrat, 
--   sa date d'émission.
-----------------------------------------------------

IF @n > 0 
    BEGIN
        SET @SequenceContratPrecedent = 
              (SELECT MAX(RIGHT(contratCode,@seqLen))
               FROM   CONTRAT 
               WHERE  agenceId = @agenceId 
                 AND  SUBSTRING(contratCode, @anneeContratOffset, 2) = @anneeEmission)

        SET @contratCodePrecedent = 
              (SELECT contratCode
               FROM   CONTRAT 
               WHERE  agenceId = @agenceId 
                 AND  SUBSTRING(contratCode, @anneeContratOffset, 2) = @anneeEmission
                 AND RIGHT(contratCode,@seqLen) = @SequenceContratPrecedent) ;

        SET @anneeContratPrecedent = SUBSTRING(@contratCodePrecedent, @anneeContratOffset, 2) ;
   END

ELSE

--------------------------------------------------------------
-- Si la table CONTRAT ne contient aucun contrat pour
-- l’agence et l'année concernées, alors on force le numéro  
-- de police à '000001' pour le contrat à créer.
--------------------------------------------------------------

    BEGIN
           SET @contratCodeNouveau = 
               CONCAT(@agenceCode
             , @categorieCode
             , @anneeEmission
             , @SequenceDebutAnnee)
    END

-----------------------------------------------------
-- Si pour l’agence concernée, le dernier contrat 
-- créé Cn est de la même année que celui qui est à
-- créer, on incrémente d'une unité le numéro de 
-- police récupéré dans le contrat Cn 
-- (variable @SequenceContrat). 
-----------------------------------------------------

IF  @n > 0 AND @anneeEmission = @anneeContratPrecedent 
    BEGIN
        SET @SequenceContratPrecedent = RIGHT(@contratCodePrecedent, @seqLen) ;
        SET @SequenceContratPrecedentNum = CAST(@SequenceContratPrecedent as INT) ;

        SET @SequenceContratNum = 1 + @SequenceContratPrecedentNum
        SET @SequenceContrat = CAST(@SequenceContratNum as CHAR(6))
        SET @contratCodeNouveau = 
               CONCAT
              (
               @agenceCode
             , @categorieCode
             , @anneeEmission
             , LEFT(@zero, LEN(@zero) - LEN(@SequenceContrat))
             , @SequenceContrat
              )
    END 

--------------------------------------
-- On sort, en fournissant le nouveau
-- code contrat.
--------------------------------------

RETURN @contratCodeNouveau 
 
END 

GO

On a traité du passé, mais concernant le futur (qui pourrait être jaloux) :

Si des utilisateurs zélés exprimaient le besoin de prendre de l’avance, en créant des contrats pour 2022 alors qu’on est encore en 2021, vous pouvez reculer la borne supérieure de la date d’émission autorisée pour les contrats, en modifiant la contrainte CONTRAT_EMISSION_BORNES_CHK dans la déclaration de la table CONTRAT (cf. post #143) :

      CONSTRAINT CONTRAT_EMISSION_BORNES_CHK 
          CHECK (YEAR(contratDateEmission) BETWEEN '1974' AND YEAR(GETDATE()))

remplacé par :

      CONSTRAINT CONTRAT_EMISSION_BORNES_CHK 
          CHECK (YEAR(contratDateEmission) BETWEEN '1974' AND YEAR(GETDATE()) + 1)

Vous pouvez définir ce genre de contrainte dès le stade Looping. L’outil se charge de la faire figurer dans le code SQL. Pour cela, dans Looping : cliquer sur "Règle", et dans la fenêtre Règle, coder dans la boîte "Script SQL" :

ALTER TABLE CONTRAT
ADD CONSTRAINT CONTRAT_EMISSION_BORNES_CHK 
          CHECK (YEAR(contratDateEmission) BETWEEN '1974' AND YEAR(GETDATE()) + 1) ;

Sans oublier de cliquer sur le bouton "Après la création des tables".

De la même façon, il est possible de faire suivre dans le code SQL produit par Looping le code de la fonction CONTRAT_SEQUENCE_FN.

**fsmrel** · 28/01/2021, 01h50

En complément :

Envoyé par Zidane7

Je vous avais dit que le type de contrat est placé dans l'entité catégorie car on a considéré que ces types ont presque les mêmes caractéristiques que les catégories, tels que le code, et libelle.

J’ai eu beau écarquiller les yeux et fouiller dans vos messages, je n’ai trouvé aucune trace de quelque signalement explicite que ce soit, précisant que le type de contrat a pu faire l’objet d’un attribut dans l’entité-type CATEGORIE. Merci donc de fournir au moins le numéro du post dans lequel vous avez précisé cela, à défaut de citation.
Par contre, suite à la suggestion que j’ai faite (voyez le post #130) dans votre dernier MCD (post #142) et cela à partir du post #133, le type de contrat fait l’objet d’un attribut (nommé typecontrat) dans l’entité-type CONTRAT. J’observe qu’avant le post #133 vous aviez fait figurer le type de contrat dans l’association CONT_CATCOMP (attribut TYPECONTRAT), voyez le post #121 (puis les posts #123, #127). Certes dans ce post vous écrivez « Voici les modifications dont j'ai apportées sur le MCD et le code SQL ci-dessous », mais comme je l’ai déjà écrit, ça n’est pas à nous de passer des heures à éplucher chacun de vos MCD pour le comparer à sa version précédente, il serait tellement plus simple que vous fassiez explicitement mention des modifications que vous apportez !

Cela dit, ça n’est pas parce que des entités ont des caractéristiques syntaxiques voisines (code, libellé) qu’il faut en faire un cocktail, sinon à ce compte-là on peut en faire autant avec tout ce qui a un code et un libellé... On peut pratiquer la spécialisation/généralisation des entités-types mais seulement quand cela a un sens au plan sémantique. Concernant les catégories et les types de contrats je ne vois pas. De toute façon, la vue que je propose du MCD dans le post #147 (spécialisation de l’entité-type CONTRAT en CONTRAT_MONO et CONTRAT_FLOTTE) devrait susciter quelques réflexions...

**Zidane7** · 28/01/2021, 18h13

Bonsoir Monsieur fsmrel,

Vous avez écrit (post #121) :
Envoyé par Zidane7
Et si c'est une flotte, nous remplaçons le code catégorie par code flotte.

fsmrel
A propos des contrats "mono" et "flotte".
Autrement dit, dans le code contrat on ne peut pas faire figurer un code catégorie pour un contrat de type "flotte", puisqu’un tel contrat assure un ensemble de véhicules pouvant appartenir à différentes catégories. Par contre, un contrat de type "mono" appartient nécessairement à une et une seule catégorie, donc là, pas de problème.

Selon la réalité oui mais selon ce qui se passera à l'interne non.
Voici comment ça se passe, une police est doté soit d'un numéro de catégorie soit un numéro de flotte ou (code mono ou flotte) dont voici un exemple: Un contrat VP ou véhicule personnel de catégorie 510 du siège aura pour police 21005102100001 et si cette voiture était dans une flotte on aura pour code 591 et alors la police serait 21005912100002 si c'est le deuxième numéro de police et ainsi de suite. Aussi, ils existent plusieurs types de flottes telles que:591 FLOTTE AUTOMOBILE, 597 FLOTTE MOTO, 598 FLOTTE TPV, 599 SEMI-REMORQUE. Que pensez-vous?

fsmrel
Dans votre dernier MCD (post #142), l’entité-type CONTRAT est identifiée relativement à l’entité-type CATEGORIE, donc chaque contrat "flotte" appartiendrait nécessairement à une catégorie : il y a là une contradiction en perspective, puisqu’un contrat "flotte" assure des véhicules a priori de catégories différentes.

Tout ce qui précède milite en faveur de la spécialisation de contrats :
• Contrat "mono" d’une part, assurant un et un seul véhicule et faisant référence à une et une seule catégorie ;
• Contrat "flotte" d’autre part, assurant au moins un et au plus plusieurs véhicules. Les catégories d’un contrat de ce type sont a priori celles des véhicules qu’il assure.

Votre raisonnement est bon mais selon ce que j'ai écrit comme réponse à votre précédent message, que pensez-vous?

fsmrel
MCD correspondant

Selon votre MCD ci-dessous, vous avez crée une entité CONTRAT_MONO sur laquelle vous avez rattaché la catégorie et cela pour identifier le code catégorie dans le contrat? et une entité CONTRAT_FLOTTE, le tout attaché sur le CONTRAT par XT. Pouvez-vous donné un schéma plus claire par rapport à XT ? Ensuite les cardinalités entre CONTRAT_FLOTTE et FLOTTE_TYPE n'est elle pas relatif c'est à dire (1,1)R?

fsmrel
Remarque : dans la mesure où un contrat donné "flotte" C ferait référence à un type de flotte donné F, et que F ne serait compatible qu’avec certaines catégories, on pourrait alors mettre en oeuvre une association A entre les entités-types CATEGORIE et FLOTTE_TYPE afin de pouvoir contrôler que les véhicules assurés par C appartiennent bien à des catégories appartenant à A. Quelle est votre position à ce sujet ?

Non à ce niveau, comme le vehicule est lié à CATCOMPLEMENT et que CATCOMPLEMENT est lié à CATEGORIE alors nous pouvons savoir quelle catégorie le véhicule appartient donc inutile de lier la catégorie à catégorie au TYPE_FLOTTE car cela serait un boucle fermé.

fsmrel
La structure du code contrat étant invariante, autant remplacer VARCHAR(14) par CHAR(14). Il serait bien qu’Escartefigue donne son avis.

Merci, j'ai corrigé le passage.

fsmrel
Etant donné que l’utilisateur doit pouvoir créer des contrats pour l’année précédente, dans la fonction je supprime les instructions empêchant cela.

Ici l'idée est que la date d'émission est la date à laquelle le contrat est fait donc même c'est le 31/12/2021 que le contrat est fait alors nous considérons que ce contrat est de 2021 par ce qu'il est émis ou fait le 31/12/2021. Par contre la date d'effet ne doit pas être inférieur à la date d'émission c'est à dire DATE_ÉMISSION<=DATE_EFFET et DATE_EFFET< DATE_EXPIRATION (ça c'est normal), mais DATE_ÉMISSION>DATE_EFFET ou DATE_EFFET>= DATE_EXPIRATION ou DATE_ÉMISSION>=DATE_EXPIRATION (est anormal) Que pensez-vous?

Ensuite s'ils existent des contrats qui ne sont pas saisis le même jour alors nous pouvons ouvrir une période au producteur de l'agence en question de saisir le contrat pour la période en question et après on ferme la période pour empêcher de saisir des contrats antérieurs.
Votre MCD
Quelles sont vos remarques et suggestions?
Une fois de plus merci.

**fsmrel** · 28/01/2021, 18h27

Bonsoir Zidane7,

Envoyé par Zidane7

une police est doté soit d'un numéro de catégorie soit un numéro de flotte ou (code mono ou flotte) dont voici un exemple: Un contrat VP ou véhicule personnel de catégorie 510 du siège aura pour police 21005102100001 et si cette voiture était dans une flotte on aura pour code 591 et alors la police serait 21005912100002 si c'est le deuxième numéro de police et ainsi de suite. Aussi, ils existent plusieurs types de flottes telles que:591 FLOTTE AUTOMOBILE, 597 FLOTTE MOTO, 598 FLOTTE TPV, 599 SEMI-REMORQUE. Que pensez-vous?

Dans mon MCD (voir post #147), l’entité-type CONTRAT prend en compte tous les contrats, qu’ils soient "mono" ou "flotte". Lors de la création du 1er contrat pour l’année 2021, supposons qu’il s’agisse d’un contrat pour le siège, de catégorie 510, la fonction CONTRAT_SEQUENCE_FN lui affectera le code "21051021000001". Lors de la création du 2e contrat pour l’année 2021, supposons qu’il s’agisse d’un contrat pour le siège, de code flotte 591, la fonction CONTRAT_SEQUENCE_FN lui affectera le code "21059121000002", etc. La fonction ne vérifie pas la nature "mono" ou "flotte" du contrat, donc pas de problème.

Envoyé par fsmrel

Je note que passer à 14 octets fait que le numéro de série doit passer à 6 octets.

Je constate que dans vos exemples le numéro de série reste à 5 octets, mais qu’en revanche un "0" s’est glissé en 7e position, entre le code catégorie et l’année : de quelle information s’agit-il ? Un code particulier ? Quelles valeurs peut-il prendre ?

En tout cas, attention ! il va falloir que je modifie la fonction, opération triviale, mais si je n’avais pas prêté attention de très près à vos exemples, la fonction ne fonctionnait plus... Comme je l’ai écrit dans le post #150 :

Envoyé par fsmrel

il serait tellement plus simple que vous fassiez explicitement mention des modifications que vous apportez !

_________________________________

Envoyé par Zidane7

Votre raisonnement est bon mais selon ce que j'ai écrit comme réponse à votre précédent message, que pensez-vous?

Veuillez vous citer, expliciter ce que vous voulez dire, car mon précédent message correspond au post #150, et vous n’y avez pas répondu...

Envoyé par Zidane7

vous avez crée une entité CONTRAT_MONO sur laquelle vous avez rattaché la catégorie et cela pour identifier le code catégorie dans le contrat?

L’association CONTRAT_CAT entre CONTRAT_MONO et CATEGORIE permet de connaître la catégorie à laquelle appartient un contrat "mono".

Envoyé par Zidane7

et une entité CONTRAT_FLOTTE, le tout attaché sur le CONTRAT par XT. Pouvez-vous donné un schéma plus claire par rapport à XT ?

Comme je l’ai écrit dans le post #147 :

Envoyé par fsmrel

Dans ce MCD, l’entité-type CONTRAT a été spécialisée en contrats "mono" (entité-type CONTRAT_MONO) et contrats "flotte" (entité-type CONTRAT_FLOTTE). La contrainte de partitionnement (totalité et exclusion) « XT » a pour objet de signifier qu’un véhicule est assuré par un contrat soit du type "mono", soit du type "flotte".

Autrement dit, CONTRAT_MONO et CONTRAT_FLOTTE sont des spécialisations de CONTRAT. Pour plus de détails, je vous renvoie à la page 106 de l’ouvrage de référence de D. Nanci et B Espinasse Ingénierie des systèmes d'information : Merise deuxième génération (4e édition, 2001).

On peut aussi modéliser ainsi la spécialisation (utilisation de l’identification relative avec les associations CCM, CCF) et là encore, la contrainte partitionnement intervient (voir à nouveau l’ouvrage de référence, page 288) :

Nom : Zidane7_assurance_auto (18)contrats(loo)idrel.png
Affichages : 196
Taille : 41,7 Ko

Envoyé par Zidane7

Ensuite les cardinalités entre CONTRAT_FLOTTE et FLOTTE_TYPE n'est elle pas relatif c'est à dire (1,1)R?

C’est inutile, à moins que vous ne démontriez qu’il faille effectivement identifier relativement.
Même chose concernant l’association entre CONTRAT_MONO et CATEGORIE.
Même chose encore concernant l’association entre CONTRAT et AGENCE.

Envoyé par fsmrel

Dans la mesure où un contrat donné "flotte" C ferait référence à un type de flotte donné F, et que F ne serait compatible qu’avec certaines catégories, on pourrait alors mettre en oeuvre une association A entre les entités-types CATEGORIE et FLOTTE_TYPE afin de pouvoir contrôler que les véhicules assurés par C appartiennent bien à des catégories appartenant à A. Quelle est votre position à ce sujet ?

Envoyé par Zidane7

Non à ce niveau, comme le vehicule est lié à CATCOMPLEMENT et que CATCOMPLEMENT est lié à CATEGORIE alors nous pouvons savoir quelle catégorie le véhicule appartient donc inutile de lier la catégorie à catégorie au TYPE_FLOTTE car cela serait un boucle fermé.

Il est évident que pour chaque véhicule on sait à quelle catégorie il appartient. Il ne s’agit pas d’introduire une boucle, mais de traiter d’un problème fonctionnel : dans la mesure où seules certaines catégories seraient autorisées pour les véhicules appartenant à une flotte, alors pouvoir contrôler cela. Si par contre les véhicules d’une flotte peuvent appartenir à n’importe quelle catégorie, alors pas de problème, on ne contrôle rien et l’association A entre les entités-types CATEGORIE et FLOTTE_TYPE est inutile.

Envoyé par Zidane7

La date d'émission est la date à laquelle le contrat est fait donc même c'est le 31/12/2021 que le contrat est fait alors nous considérons que ce contrat est de 2021 par ce qu'il est émis ou fait le 31/12/2021. Par contre la date d'effet ne doit pas être inférieur à la date d'émission c'est à dire DATE_ÉMISSION<=DATE_EFFET et DATE_EFFET< DATE_EXPIRATION (ça c'est normal), mais DATE_ÉMISSION>DATE_EFFET ou DATE_EFFET>= DATE_EXPIRATION ou DATE_ÉMISSION>=DATE_EXPIRATION (est anormal) Que pensez-vous?

Dans le post #143, vous aurez noté que dans l’instruction CREATE TABLE CONTRAT, la contrainte suivante garantit le contrôle entre les date d’émission et d’effet :

     CONSTRAINT CONTRAT_DATE_EFFET_CHK 
         CHECK (contratDateEffet >= contratDateEmission)

Mais il faut effectivement ajouter le contrôle entre les dates d’effet et d’expiration :

     CONSTRAINT CONTRAT_DATE_EXPIRATION_CHK 
          CHECK (contratDateEffet < contratDateExpiration)

Comme je l’ai écrit dans le post #149, vous pouvez définir ce genre de contrainte dès le stade Looping. L’outil se charge de la faire figurer dans le code SQL.

A propos de la contrainte CONTRAT_EMISSION_BORNES_CHK (voir post #149) :

    , CONSTRAINT CONTRAT_EMISSION_BORNES_CHK 
          CHECK (YEAR(contratDateEmission) BETWEEN '1974' AND YEAR(GETDATE()) + 1)

On autorise en 2021 l’émission anticipée de contrats de 2022. On peut être plus restrictif. Par exemple, si on veut limiter ’émission à la date du 2022-01-15 :

     CONSTRAINT CONTRAT_EMISSION_BORNES_CHK 
         CHECK (contratDateEmission BETWEEN '1974-01-01' AND CONCAT((1+YEAR(GETDATE())), '-01-15'))

Mais vous avez manifestement votre propre mécanisme, plus fin, d’ouverture provisoire de saisie des contrats (je vous cite : « s'ils existent des contrats qui ne sont pas saisis le même jour alors nous pouvons ouvrir une période au producteur de l'agence en question de saisir le contrat pour la période en question et après on ferme la période pour empêcher de saisir des contrats antérieurs. »)

**Zidane7** · 01/02/2021, 13h57

Bonjour Monsieur fsmrel,
Votre MCD du poste #152 me réconforte énormément car ça règle le problème au niveau contrat. Une fois de plus merci.

Envoyé par fsmrel
Je note que passer à 14 octets fait que le numéro de série doit passer à 6 octets.

Je constate que dans vos exemples le numéro de série reste à 5 octets, mais qu’en revanche un "0" s’est glissé en 7e position, entre le code catégorie et l’année : de quelle information s’agit-il ? Un code particulier ? Quelles valeurs peut-il prendre ?

C'est le code agance qui a augmenté de 1 chiffre donc au lieu de 210 par exemple c'est 2100. Merci pour la remarque.

Envoyé par Zidane7
Votre raisonnement est bon mais selon ce que j'ai écrit comme réponse à votre précédent message, que pensez-vous?

Veuillez vous citer, expliciter ce que vous voulez dire, car mon précédent message correspond au post #150, et vous n’y avez pas répondu...

Je voudrais vous dire le MCD du poste #147 qui a sa suite de votre poste #152. Merci.

Envoyé par fsmrel
Dans la mesure où un contrat donné "flotte" C ferait référence à un type de flotte donné F, et que F ne serait compatible qu’avec certaines catégories, on pourrait alors mettre en oeuvre une association A entre les entités-types CATEGORIE et FLOTTE_TYPE afin de pouvoir contrôler que les véhicules assurés par C appartiennent bien à des catégories appartenant à A. Quelle est votre position à ce sujet ?
Envoyé par Zidane7
Non à ce niveau, comme le véhicule est lié à CATCOMPLEMENT et que CATCOMPLEMENT est lié à CATEGORIE alors nous pouvons savoir quelle catégorie le véhicule appartient donc inutile de lier la catégorie à catégorie au TYPE_FLOTTE car cela serait un boucle fermé.
Il est évident que pour chaque véhicule on sait à quelle catégorie il appartient. Il ne s’agit pas d’introduire une boucle, mais de traiter d’un problème fonctionnel : dans la mesure où seules certaines catégories seraient autorisées pour les véhicules appartenant à une flotte, alors pouvoir contrôler cela. Si par contre les véhicules d’une flotte peuvent appartenir à n’importe quelle catégorie, alors pas de problème, on ne contrôle rien et l’association A entre les entités-types CATEGORIE et FLOTTE_TYPE est inutile.

Je suis d'accord avec vous. A cet endroit, nous avons les flottes suivantes: 591 FLOTTE AUTOMOBILE (qui regroupe toute sorte de catégorie), 597 FLOTTE MOTO (qui regroupe toute sorte de moto), 598 FLOTTE TPV (qui regroupe tous les véhicules de transport de voyageurs), 599 SEMI-REMORQUE (qui regroupe tous les semi remorques)... Si on decide à la longue de créer d'autres flotte, nous pouvons l'ajouter.Voici le MCD modifié.
Que pensez-vous des cardinalités sur cette partie?

Le code sql ci-dessous:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
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 GARANTIE(
   garantieId INT IDENTITY,
   codegarantie CHAR(1) NOT NULL,
   Libelle_Garantie VARCHAR(50) NOT NULL,
   PRIMARY KEY(garantieId),
   UNIQUE(codegarantie)
);

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

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

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,
   modepaiementcode VARCHAR(15) NOT NULL,
   modepaiement_libelle VARCHAR(50),
   PRIMARY KEY(modepaiemenId),
   UNIQUE(modepaiementcode)
);

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

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

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

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

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

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

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

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

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

CREATE TABLE BRANCHE(
   brancheId INT IDENTITY,
   branchecode CHAR(3) NOT NULL,
   libelle_branche VARCHAR(50),
   PRIMARY KEY(brancheId),
   UNIQUE(branchecode)
);

CREATE TABLE OPTIONSECURITE(
   optionsecuriteId VARCHAR(50),
   codeoptionsecurite VARCHAR(1) NOT NULL,
   optionmontant INT,
   garantieId INT NOT NULL,
   PRIMARY KEY(optionsecuriteId),
   UNIQUE(codeoptionsecurite),
   FOREIGN KEY(garantieId) REFERENCES GARANTIE(garantieId)
);

CREATE TABLE OPTIONVALEUR(
   optionvaleurId VARCHAR(50),
   optionvaleurlibelle VARCHAR(30),
   optionvaleurmontant INT NOT NULL,
   optionsecuriteId VARCHAR(50) NOT NULL,
   PRIMARY KEY(optionvaleurId),
   FOREIGN KEY(optionsecuriteId) REFERENCES OPTIONSECURITE(optionsecuriteId)
);

CREATE TABLE FLOTTE_TYPE(
   flotte_typeId INT IDENTITY,
   flotte_typecode CHAR(3) NOT NULL,
   flottetypelibelle VARCHAR(40),
   PRIMARY KEY(flotte_typeId),
   UNIQUE(flotte_typecode)
);

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

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 CAISSIERE(
   agenceId_1 INT,
   caissiereId INT IDENTITY,
   codecaissiere VARCHAR(4) NOT NULL,
   prenomcaissiere VARCHAR(40) NOT NULL,
   nomcaissiere VARCHAR(40) NOT NULL,
   agenceId INT,
   producteurId INT,
   PRIMARY KEY(agenceId_1, caissiereId),
   UNIQUE(codecaissiere),
   FOREIGN KEY(agenceId_1) REFERENCES AGENCE(agenceId),
   FOREIGN KEY(agenceId, producteurId) REFERENCES PRODUCTEUR(agenceId, producteurId)
);

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

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

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

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

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

CREATE TABLE ENCAISSEMENT_C(
   agenceId INT,
   contratId INT,
   encaisseCtrId INT IDENTITY,
   encaisseCtrMontant INT,
   encaisseCtrDate DATE,
   modepaiemenId INT NOT NULL,
   agenceId_1 INT NOT NULL,
   caissiereId INT NOT NULL,
   PRIMARY KEY(agenceId, contratId, encaisseCtrId),
   FOREIGN KEY(agenceId, contratId) REFERENCES CONTRAT(agenceId, contratId),
   FOREIGN KEY(modepaiemenId) REFERENCES MODEPAIEMENT(modepaiemenId),
   FOREIGN KEY(agenceId_1, caissiereId) REFERENCES CAISSIERE(agenceId_1, caissiereId)
);

CREATE TABLE CONTRAT_MONO(
   agenceId INT,
   contratId INT,
   categorieId INT NOT NULL,
   PRIMARY KEY(agenceId, contratId),
   FOREIGN KEY(agenceId, contratId) REFERENCES CONTRAT(agenceId, contratId),
   FOREIGN KEY(categorieId) REFERENCES CATEGORIE(categorieId)
);

CREATE TABLE CONTRAT_FLOTTE(
   agenceId INT,
   contratId INT,
   contratflotteId INT IDENTITY,
   flotte_typeId INT NOT NULL,
   PRIMARY KEY(agenceId, contratId, contratflotteId),
   UNIQUE(agenceId, contratId),
   FOREIGN KEY(agenceId, contratId) REFERENCES CONTRAT(agenceId, contratId),
   FOREIGN KEY(flotte_typeId) REFERENCES FLOTTE_TYPE(flotte_typeId)
);

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

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

CREATE TABLE FLOTTE_TYPE_CAT(
   categorieId INT,
   flotte_typeId INT,
   PRIMARY KEY(categorieId, flotte_typeId),
   FOREIGN KEY(categorieId) REFERENCES CATEGORIE(categorieId),
   FOREIGN KEY(flotte_typeId) REFERENCES FLOTTE_TYPE(flotte_typeId)
);

GO
CREATE FUNCTION CONTRAT_SEQUENCE_FN(@agenceCode as CHAR(3)
                                  , @categorieCode as CHAR(3)
                                  , @anneeEmission as CHAR(2)) 
    RETURNS CHAR(14)  
AS   
BEGIN
  
DECLARE @n INT = 0 ;
DECLARE @agenceId INT ; 
DECLARE @categorieId INT ;
DECLARE @@contratCodeLen INT = 14 ;
DECLARE @contratCodeNouveau CHAR(14) ;
DECLARE @contratCodeNew CHAR(14) ;
DECLARE @contratCodePrecedent CHAR(14) ;
DECLARE @SequenceDebutAnnee CHAR(6) = '000001' ;
DECLARE @SequenceContrat CHAR(6) ;
DECLARE @SequenceContratNum INT ;
DECLARE @SequenceContratPrecedent CHAR(6) ;
DECLARE @SequenceContratPrecedentNum INT ;
DECLARE @seqLen as INT = 6
DECLARE @anneeContratOffset INT = 7
DECLARE @anneeMax  INT ;
DECLARE @anneeContratPrecedent CHAR(2) ;
DECLARE @zero CHAR(6) = '000000' ;
DECLARE @anneeErreur CHAR(6) = '??????' ;

SET @agenceId = (SELECT agenceId FROM AGENCE WHERE agenceCode = @agenceCode)
SET @categorieId = (SELECT categorieId FROM CATEGORIE WHERE categorieCode = @categorieCode)       
SET @contratCodeNouveau = CONCAT(@agenceCode, @categorieCode, @anneeEmission, @anneeErreur)

----------------------------------------------------
-- On regarde si la table CONTRAT contient au moins 
-- un contrat pour l’agence et l'année concernées.
----------------------------------------------------

SET @anneeMax = (SELECT MAX(SUBSTRING(contratCode, @anneeContratOffset, 2))
                 FROM   CONTRAT 
                 WHERE  agenceId = @agenceId) ;

SET @n = (SELECT COUNT(*) 
          FROM   CONTRAT 
          WHERE  agenceId = @agenceId
            AND  SUBSTRING(contratCode, @anneeContratOffset, 2) = @anneeEmission) ;

-----------------------------------------------------
-- Si la table CONTRAT contient au moins un contrat
-- pour l'agence et l'année concernées, on récupère 
-- dans le dernier contrat créé correspondant :
--   son numéro de série, 
--   son code contrat, 
--   sa date d'émission.
-----------------------------------------------------

IF @n > 0 
    BEGIN
        SET @SequenceContratPrecedent = 
              (SELECT MAX(RIGHT(contratCode,@seqLen))
               FROM   CONTRAT 
               WHERE  agenceId = @agenceId 
                 AND  SUBSTRING(contratCode, @anneeContratOffset, 2) = @anneeEmission)

        SET @contratCodePrecedent = 
              (SELECT contratCode
               FROM   CONTRAT 
               WHERE  agenceId = @agenceId 
                 AND  SUBSTRING(contratCode, @anneeContratOffset, 2) = @anneeEmission
                 AND RIGHT(contratCode,@seqLen) = @SequenceContratPrecedent) ;

        SET @anneeContratPrecedent = SUBSTRING(@contratCodePrecedent, @anneeContratOffset, 2) ;
   END

ELSE

--------------------------------------------------------------
-- Si la table CONTRAT ne contient aucun contrat pour
-- l’agence et l'année concernées, alors on force le numéro  
-- de police à '000001' pour le contrat à créer.
--------------------------------------------------------------

    BEGIN
           SET @contratCodeNouveau = 
               CONCAT(@agenceCode
             , @categorieCode
             , @anneeEmission
             , @SequenceDebutAnnee)
    END

-----------------------------------------------------
-- Si pour l’agence concernée, le dernier contrat 
-- créé Cn est de la même année que celui qui est à
-- créer, on incrémente d'une unité le numéro de 
-- police récupéré dans le contrat Cn 
-- (variable @SequenceContrat). 
-----------------------------------------------------

IF  @n > 0 AND @anneeEmission = @anneeContratPrecedent 
    BEGIN
        SET @SequenceContratPrecedent = RIGHT(@contratCodePrecedent, @seqLen) ;
        SET @SequenceContratPrecedentNum = CAST(@SequenceContratPrecedent as INT) ;

        SET @SequenceContratNum = 1 + @SequenceContratPrecedentNum
        SET @SequenceContrat = CAST(@SequenceContratNum as CHAR(6))
        SET @contratCodeNouveau = 
               CONCAT
              (
               @agenceCode
             , @categorieCode
             , @anneeEmission
             , LEFT(@zero, LEN(@zero) - LEN(@SequenceContrat))
             , @SequenceContrat
              )
    END 

--------------------------------------
-- On sort, en fournissant le nouveau
-- code contrat.
--------------------------------------

RETURN @contratCodeNouveau 
 
END 

GO

Une fois de plus merci.
Et le MCD:

**fsmrel** · 01/02/2021, 19h24

Bonsoir Zidane7,

On avance...

Le code contrat (attribut codecontrat de votre table CONTRAT) est donc passé à 14 octets. Désormais le code agence occupe les 4 premiers octets. Les 3 octets suivants servent soit pour un code catégorie (cas d’un contrat "mono") soit pour un code flotte (cas d’un contrat "flotte"). Question : un code catégorie et un code flotte peuvent-ils avoir la même valeur ?

**Zidane7** · 02/02/2021, 15h33

Bonsoir Monsieur fsmrel,

fsmre
Le code contrat (attribut codecontrat de votre table CONTRAT) est donc passé à 14 octets. Désormais le code agence occupe les 4 premiers octets. Les 3 octets suivants servent soit pour un code catégorie (cas d’un contrat "mono") soit pour un code flotte (cas d’un contrat "flotte"). Question : un code catégorie et un code flotte peuvent-ils avoir la même valeur ?

Non les codes catégories sont différentes de celui du code flotte, mais ils sont tous de 3 octets.
Exemple: Code catégorie 510, 520, 530.... et code flotte 591,597, 598...
Que pensez-vous?
Merci d'avance.

**fsmrel** · 04/02/2021, 20h04

Bonsoir Zidane7,

Envoyé par Zidane7

les codes catégories sont différentes de celui du code flotte. Exemple: Code catégorie 510, 520, 530.... et code flotte 591,597, 598...

Il y a donc un rapprochement sémantique fort entre les deux concepts, catégorie et type de flotte. On mettra donc en oeuvre une contrainte d’exclusion pour interdire qu’un code se retrouve à la fois dans les deux tables, CATEGORIE et FLOTTE_TYPE.

Les entités-types CATEGORIE et FLOTTE_TYPE conservent leur structure actuelle, (cf. post #152) :

Nom : Zidane7_assurance_auto (categorie_flotte)rien.png
Affichages : 165
Taille : 6,2 Ko

On pourrait envisager de procéder à leur généralisation, mais cela ne nous dispensera pas de programmer la contrainte d’exclusion évoquée.

Nom : Zidane7_assurance_auto (categorie_flotte)generalisation.png
Affichages : 167
Taille : 7,9 Ko

Même chose en passant à l’identification relative :

Nom : Zidane7_assurance_auto (categorie_flotte)idrel).png
Affichages : 179
Taille : 10,7 Ko

Conclusion : on ne touche pas à la structure actuelle, mais on n’échappera pas à la contrainte d’exclusion. En conséquence, on crée une fonction ad-hoc, nommons-la par exemple MONO_FLOTTE_EXCLUSION_FN, puis ajoutons dans les tables concernées les contraintes CATEGORIE_EXCLUSION_CHECK (table CATEGORIE) et FLOTTE_EXCLUSION_CHECK (table FLOTTE_TYPE) :

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

CREATE TABLE FLOTTE_TYPE
(
        flotteTypeId          INT  IDENTITY  
      , flotteTypeCode        CHAR(3)            NOT NULL
      , flotteTypeLibelle     VARCHAR(64)        NOT NULL
    , CONSTRAINT FLOTTE_TYPE_PK PRIMARY KEY(flotteTypeId)
    , CONSTRAINT FLOTTE_TYPE_AK UNIQUE(flotteTypeCode)
);

GO
CREATE FUNCTION MONO_FLOTTE_EXCLUSION_FN()  
RETURNS INT  
AS   
BEGIN  
   DECLARE @n INT  
   SELECT @n = COUNT(*) FROM CATEGORIE as x
    JOIN   FLOTTE_TYPE as y ON x.catCode = y.flotteTypeCode
   ;
   RETURN @n  
END ;  
GO  

ALTER TABLE CATEGORIE  
ADD CONSTRAINT CATEGORIE_EXCLUSION_CHECK CHECK (dbo.MONO_FLOTTE_EXCLUSION_FN() = 0) ;  

ALTER TABLE FLOTTE_TYPE  
ADD CONSTRAINT FLOTTE_EXCLUSION_CHECK CHECK (dbo.MONO_FLOTTE_EXCLUSION_FN() = 0) ;

Quelques remarques :

— Je n’ai pas identifié CONTRAT relativement à AGENCE, mais si cela s’avérait utile une fois le MCD complété, je verrai à le faire.

— Dans votre MCD, la cardinalité portée par la patte connectant l’entité-type CONTRAT et l’association CONTRAT_VEHICULE est porteuse d’une cardinalité 1,N,ce qui veut dire que plusieurs véhicules peuvent référencer un contrat donné même si celui-ci est "mono", ce qui est manifestement une contradiction.

Quoi qu’il en soit, observons qu’un contrat "mono" fait référence à un et un seul véhicule, et en tout cas c’est ce que j’ai modélisé pour ma part dans le post #152, avec le chemin CONTRAT > CCM > CONTRAT_MONO > VEH_MONO > VEHICULE.

Par voie de conséquence, je supprime dans mon MCD l’association CONTRAT_CAT (entre CONTRAT_MONO et CATEGORIE, cf. post #152), car la catégorie d’un contrat "mono" est connue (transitivement) via le chemin CONTRAT_MONO > VEH_MONO > VEHICULE > VE_CATCOMP > CAT_COMPLEMENT > CATEGORIE.

Observons encore que pour les contrats "flotte", c’est par l’association VEH_FLOTTE (cf. post #152) qu’on définit les véhicules d’une flotte.

Nom : Zidane7_assurance_auto (19)contrats(loo)idrel_contrat_cat ejecte.png
Affichages : 174
Taille : 49,1 Ko

Les CREATE TABLE deviennent les suivants (concernant la table CONTRAT je n’ai fait figurer que les principaux attributs). Notez que vous avez doté CONTRAT d’un attribut contrat_emission_bornes de type DATE qui doit disparaître, car une date n'est pas un intervalle temporel ! et comme dit dans le post #152, c’est la contrainte CONTRAT_EMISSION_BORNES_CHK qui contrôle les bornes. Les bornes que j’ai fait figurer sont à aménager, elles sont là à titre d’exemple. Notez encore la contrainte CONTRAT_EMISSION_COHERENCE_CHK qui permet de s’assurer que l’année figurant dans le code contrat est celle de la date d’émission du contrat.

CREATE TABLE CONTRAT
(
        agenceId                 INT          NOT NULL
      , contratId                INT   IDENTITY
      , contratCode              CHAR(14)     NOT NULL
      , contratDateEmission      DATE         NOT NULL
      , contratDateEffet         DATE         NOT NULL
      , contratDateExpiration    DATE         NOT NULL
      , clientId                 INT          NOT NULL
    , CONSTRAINT CONTRAT_PK PRIMARY KEY(contratId)
    , CONSTRAINT CONTRAT_AK UNIQUE(contratCode)
    , CONSTRAINT CONTRAT_AGENCE_FK FOREIGN KEY(agenceId) 
          REFERENCES AGENCE(agenceId)
    , CONSTRAINT CONTRAT_CLIENT_FK FOREIGN KEY(clientId) 
          REFERENCES CLIENT(clientId)
    , CONSTRAINT CONTRAT_DATE_EFFET_CHK 
         CHECK (contratDateEffet >= contratDateEmission)
    , CONSTRAINT CONTRAT_DATE_EXPIRATION_CHK 
         CHECK (contratDateEffet < contratDateExpiration) 
   , CONSTRAINT CONTRAT_EMISSION_BORNES_CHK 
         CHECK (contratDateEmission BETWEEN '1974-01-01' AND CONCAT((1+YEAR(GETDATE())), '-01-15'))
   , CONSTRAINT CONTRAT_EMISSION_COHERENCE_CHK 
         CHECK (SUBSTRING(contratCode,8,2) = SUBSTRING(CAST(contratDateEmission as CHAR(4)),3,2))
) ;

CREATE TABLE CONTRAT_MONO
(
        contratId                INT     NOT NULL 
      , vehiculeId               INT     NOT NULL
    , CONSTRAINT CONTRAT_MONO_PK PRIMARY KEY(contratId)
    , CONSTRAINT CONTRAT_MONO_AK UNIQUE(vehiculeId)
    , CONSTRAINT CONTRAT_MONO_CONTRAT_FK FOREIGN KEY(contratId) 
          REFERENCES CONTRAT(contratId)
    , CONSTRAINT CONTRAT_MONO_VEHICULE_FK FOREIGN KEY(vehiculeId) 
          REFERENCES VEHICULE(vehiculeId)
);
 
CREATE TABLE CONTRAT_FLOTTE
(
        contratId                INT     NOT NULL 
      , flotteTypeId             INT     NOT NULL
    , CONSTRAINT CONTRAT_FLOTTE_PK PRIMARY KEY(contratId)
    , CONSTRAINT CONTRAT_FLOTTE_CONTRAT_FK FOREIGN KEY(contratId) 
          REFERENCES CONTRAT(contratId)
    , CONSTRAINT CONTRAT_FLOTTE_FLOTTE_TYPE_FK FOREIGN KEY(flotteTypeId) 
          REFERENCES FLOTTE_TYPE(flotteTypeId)
);

Création des contrats "mono" et "flotte"

J’ai aménagé la fonction CONTRAT_SEQUENCE_FN pour tenir compte du passage du code agence à 4 octets. A noter que le langage utilisé est Transact-SQL (T-SQL) :

GO
CREATE FUNCTION CONTRAT_SEQUENCE_FN(@agenceCode as CHAR(4)
                                  , @catCode as CHAR(3)
                                  , @anneeEmission as CHAR(2))      
    RETURNS CHAR(14)  
AS   
BEGIN
  
DECLARE @n INT = 0 ;
DECLARE @agenceId INT ; 
DECLARE @@contratCodeLen INT = 14 ;
DECLARE @contratCodeNouveau CHAR(14) ;
DECLARE @contratCodeNew CHAR(14) ;
DECLARE @contratCodePrecedent CHAR(14) ;
DECLARE @SequenceDebutAnnee CHAR(5) = '00001' ;
DECLARE @SequenceContrat CHAR(5) ;
DECLARE @SequenceContratNum INT ;
DECLARE @SequenceContratPrecedent CHAR(5) ;
DECLARE @SequenceContratPrecedentNum INT ;
DECLARE @seqLen as INT = 5
DECLARE @anneeContratOffset INT = 8
DECLARE @anneeMax  INT ;
DECLARE @anneeContratPrecedent CHAR(2) ;
DECLARE @zero CHAR(5) = '00000' ;
DECLARE @anneeErreur CHAR(5) = '?????' ;

SET @agenceId = (SELECT agenceId FROM AGENCE WHERE agenceCode = @agenceCode)
SET @contratCodeNouveau = CONCAT(@agenceCode, @catCode, @anneeEmission, @anneeErreur)

----------------------------------------------------
-- On regarde si la table CONTRAT contient au moins 
-- un contrat pour l’agence et l'année concernées.
----------------------------------------------------

SET @anneeMax = (SELECT MAX(SUBSTRING(contratCode, @anneeContratOffset, 2))
                 FROM   CONTRAT 
                 WHERE  agenceId = @agenceId) ;

SET @n = (SELECT COUNT(*) 
          FROM   CONTRAT 
          WHERE  agenceId = @agenceId
            AND  SUBSTRING(contratCode, @anneeContratOffset, 2) = @anneeEmission) ;

-----------------------------------------------------
-- Si la table CONTRAT contient au moins un contrat
-- pour l'agence et l'année concernées, on récupère 
-- dans le dernier contrat créé correspondant :
--   son numéro de série, 
--   son code contrat, 
--   sa date d'émission.
-----------------------------------------------------

IF @n > 0 
    BEGIN
        SET @SequenceContratPrecedent = 
              (SELECT MAX(RIGHT(contratCode,@seqLen))
               FROM   CONTRAT 
               WHERE  agenceId = @agenceId 
                 AND  SUBSTRING(contratCode, @anneeContratOffset, 2) = @anneeEmission)

        SET @contratCodePrecedent = 
              (SELECT contratCode
               FROM   CONTRAT 
               WHERE  agenceId = @agenceId 
                 AND  SUBSTRING(contratCode, @anneeContratOffset, 2) = @anneeEmission
                 AND RIGHT(contratCode,@seqLen) = @SequenceContratPrecedent) ;

        SET @anneeContratPrecedent = SUBSTRING(@contratCodePrecedent, @anneeContratOffset, 2) ;
   END

ELSE

--------------------------------------------------------------
-- Si la table CONTRAT ne contient aucun contrat pour
-- l’agence et l'année concernées, alors on force le numéro  
-- de police à '000001' pour le contrat à créer.
--------------------------------------------------------------

    BEGIN
           SET @contratCodeNouveau = 
               CONCAT(@agenceCode
             , @catCode
             , @anneeEmission
             , @SequenceDebutAnnee)
    END

-----------------------------------------------------
-- Si pour l’agence concernée, le dernier contrat 
-- créé Cn est de la même année que celui qui est à
-- créer, on incrémente d'une unité le numéro de 
-- police récupéré dans le contrat Cn 
-- (variable @SequenceContrat). 
-----------------------------------------------------

IF  @n > 0 AND @anneeEmission = @anneeContratPrecedent 
    BEGIN
        SET @SequenceContratPrecedent = RIGHT(@contratCodePrecedent, @seqLen) ;
        SET @SequenceContratPrecedentNum = CAST(@SequenceContratPrecedent as INT) ;

        SET @SequenceContratNum = 1 + @SequenceContratPrecedentNum
        SET @SequenceContrat = CAST(@SequenceContratNum as CHAR(6))
        SET @contratCodeNouveau = 
               CONCAT
              (
               @agenceCode
             , @catCode
             , @anneeEmission
             , LEFT(@zero, LEN(@zero) - LEN(@SequenceContrat))
             , @SequenceContrat
              )
    END 

--------------------------------------
-- On sort, en fournissant le nouveau
-- code contrat.
--------------------------------------

RETURN @contratCodeNouveau 
 
END 

GO

Création d’un contrat "mono"

Pour créer un contrat "mono", si on utilise T-SQL on commence par définir deux variables :

— La variable @categorieCode servant à récupérer le code catégorie du véhicule impliqué par le contrat.

— La variable @contratCode servant à récupérer le nouveau code contrat calculé par la fonction CONTRAT_SEQUENCE_FN.

Créons par exemple un contrat "mono", émis le '2021-01-01', relevant de l’agence '2100', pour le véhicule 'veh001' appartenant au client dont le code est 'cli001'.

DECLARE @contratCode as CHAR(14) ;
DECLARE @categorieCode as CHAR(3) ;

------------------------------------------------ 
-- Détermination de la catégorie du véhicule
------------------------------------------------

SET @categorieCode = 
    (SELECT catCode
     FROM   VEHICULE as x
       JOIN CATEGORIE as y ON x.catId = y.catId 
     WHERE vehiculeCode ='veh001') ;

---------------------------------------------------------
-- On demande à la fonction CONTRAT_SEQUENCE_FN de
-- calculer le nouveau code contrat, en fonction du
-- code agence, de la catégorie et de l'année de la 
-- date d’émission du contrat. 
---------------------------------------------------------

SET @contratCode = dbo.CONTRAT_SEQUENCE_FN('2100', @categorieCode, '21') ;

-------------------------------------------------
-- Création du nouveau contrat, dont le code
-- est présent dans la variable @contratCode
-------------------------------------------------

INSERT INTO CONTRAT (contratCode, contratDateEmission
                   , contratDateEffet, contratdateExpiration
                   , agenceId, clientId)
VALUES
(
    @contratCode
  , '2021-01-01', '2021-01-02', '2024-12-31'
  , (SELECT agenceId FROM AGENCE WHERE agenceCode = '2100')
  , (SELECT clientId FROM CLIENT WHERE clientCode = 'cli001')
) ;

----------------------------------------------
-- Création dans la table CONTRAT_MONO 
-- (lien contrat - véhicule)
---------------------------------------------

INSERT INTO CONTRAT_MONO (contratId, vehiculeId)
VALUES 
    ((SELECT contratId FROM CONTRAT WHERE contratCode = @contratCode)
   , (SELECT vehiculeId FROM VEHICULE WHERE vehiculeCode = 'veh001'))
;

Pour voir le résultat :

SELECT contratCode
     , agenceCode as agence
     , contratDateEmission as dateEmission
     , contratDateEffet as dateEffet
     , contratdateExpiration as expiration
     , clientCode as client
     , vehiculeCode as vehCode
     , catCode 
FROM   CONTRAT as x
  JOIN CONTRAT_MONO as y ON x.contratId = y.contratId 
  JOIN AGENCE as z ON x.agenceId = z.agenceId
  JOIN VEHICULE as u ON y.vehiculeId = u.vehiculeId
  JOIN CATEGORIE as v ON u.catId = v.catId 
  JOIN CLIENT as t ON x.clientId = t.clientId
;

=>

contratCode       agence   dateEmission   dateEffet    expiration   client  vehCode  catCode

21004102100001    2100     2021-01-01     2021-01-02   2024-12-31   cli001  veh001   410

De la requête précédente on peut faire une vue pour en « encapsuler » le verbiage :

GO
CREATE VIEW CONTRAT_MONO_VUE
(
    contratCode, agence
  , dateEmission, dateEffet, expiration
  , client, vehCode, catCode   
)
AS
    SELECT contratCode, agenceCode
         , contratDateEmission, contratDateEffet, contratdateExpiration
         , clientCode, vehiculeCode, catCode 
    FROM   CONTRAT as x
      JOIN CONTRAT_MONO as y ON x.contratId = y.contratId 
      JOIN AGENCE as z ON x.agenceId = z.agenceId
      JOIN VEHICULE as u ON y.vehiculeId = u.vehiculeId
      JOIN CATEGORIE as v ON u.catId = v.catId 
      JOIN CLIENT as t ON x.clientId = t.clientId
;
GO

Pour voir le résultat (identique au précédent) :

SELECT * FROM CONTRAT_MONO_VUE
ORDER BY LEFT(contratCode,4), SUBSTRING(contratCode, 8,2), RIGHT(contratCode,5)
;

Noter que les contrats sont triés ici par code agence, année, numéro de série.

A propos des redondances dans la programmation

Pour en revenir à la création du contrat, on observe que l’on a fait deux fois référence au code véhicule, en l’occurrence 'veh011', deux fois au code agence ('2100'), deux fois à la date d’émission (une fois la date, une fois l’année) et ceci n’est pas sain (redondances !) Il est préférable de créer des variables pour assainir cela.

Avec les variables supplémentaires @vehiculeCode, @agenceCode, @dateEmission, @anneeEmission :

DECLARE @contratCode as CHAR(14) ;
DECLARE @categorieCode as CHAR(3) ;
DECLARE @vehiculeCode as CHAR(8) ; 
DECLARE @agenceCode as CHAR(4) ;
DECLARE @dateEmission as DATE ; 
DECLARE @anneeEmission as CHAR(2) ; 

SET @vehiculeCode = 'veh001' 
SET @agenceCode = '2100'
SET @dateEmission = '2021-01-01'
SET @anneeEmission = RIGHT(YEAR(@dateEmission),2) ;

------------------------------------------------ 
-- Détermination de la catégorie du véhicule
------------------------------------------------

SET @categorieCode = 
    (SELECT catCode
     FROM   VEHICULE as x
       JOIN CATEGORIE as y ON x.catId = y.catId 
     WHERE vehiculeCode = @vehiculeCode) ;

------------------------------------------------------
-- On demande à la fonction CONTRAT_SEQUENCE_FN de
-- calculer le nouveau code contrat, en fonction du
-- code agence, de la catégorie et de l'année de la 
-- date d’émission du contrat. 
------------------------------------------------------

SET @contratCode = dbo.CONTRAT_SEQUENCE_FN(@agenceCode, @categorieCode, @anneeEmission) ;

------------------------------------------------
-- Création du nouveau contrat, dont le code
-- est présent dans la variable @contratCode
-------------------------------------------------

INSERT INTO CONTRAT (contratCode, contratDateEmission
                   , contratDateEffet, contratdateExpiration
                   , agenceId, clientId)
VALUES
(
    @contratCode
  , @dateEmission, '2021-01-02', '2024-12-31'
  , (SELECT agenceId FROM AGENCE WHERE agenceCode = @agenceCode)
  , (SELECT clientId FROM CLIENT WHERE clientCode = 'cli001')

----------------------------------------------
-- Création dans la table CONTRAT_MONO 
-- (lien contrat - véhicule)
---------------------------------------------

INSERT INTO CONTRAT_MONO (contratId, vehiculeId)
VALUES 
    ((SELECT contratId FROM CONTRAT WHERE contratCode = @contratCode)
   , (SELECT vehiculeId FROM VEHICULE WHERE vehiculeCode = @vehiculeCode))
 ;

Je reviendrai un peu plus tard sur tout ça, car je n’ai pas encore complètement vérifié pour les flottes.

A bientôt

**fsmrel** · 05/02/2021, 17h45

Bonsoir Zidane7,

A propos de la création des contrats "mono" :

Envoyé par fsmrel

Créons par exemple un contrat "mono", émis le '2021-01-01', relevant de l’agence '2100', pour le véhicule 'veh001' appartenant au client dont le code est 'cli001'.

Pour chaque création d’un contrat nouveau, on devra reprendre ce qu’on a déjà fait, en changeant les valeurs des données (code agence, code véhicule, date d’émission, code client, etc.) Pour réduire encore le verbiage et se simplifier la vie, il peut être intéressant de mettre en oeuvre une procédure de création des contrats "mono".

Exemple :

Création de la procédure (à noter que le paramètre OUTPUT permet de préciser que la variable @contratCode contiendra la valeur du nouveau code contrat, récupérable donc par l’appelant) :

GO
CREATE PROCEDURE CONTRAT_MONO_CAT_PROC
(
    @contratCode as CHAR(14) OUTPUT   
  , @agenceCode as CHAR(4) 
  , @vehiculeCode as CHAR(8) 
  , @dateEmission as DATE
  , @dateEffet as DATE
  , @dateExpiration as DATE
  , @clientCode as CHAR(10))
AS
BEGIN 
 
DECLARE @categorieCode as CHAR(3) ;
DECLARE @anneeEmission as CHAR(2) ;

SET @anneeEmission = RIGHT(YEAR(@dateEmission),2) ;

------------------------------------------------ 
-- Détermination de la catégorie du véhicule
------------------------------------------------

SET @categorieCode = 
    (SELECT catCode
     FROM   VEHICULE as x
       JOIN CATEGORIE as y ON x.catId = y.catId 
     WHERE vehiculeCode = @vehiculeCode)

------------------------------------------------------
-- On demande à la fonction CONTRAT_SEQUENCE_FN de
-- calculer le nouveau code contrat, en fonction du
-- code agence, de la catégorie et de l'année de la 
-- date d’émission du contrat. 
------------------------------------------------------

SET @contratCode = dbo.CONTRAT_SEQUENCE_FN(@agenceCode, @categorieCode, @anneeEmission) ;

------------------------------------------------
-- Création du nouveau contrat, dont le code
-- est présent dans la variable @contratCode
-------------------------------------------------

INSERT INTO CONTRAT (contratCode, contratDateEmission
                   , contratDateEffet, contratdateExpiration
                   , agenceId, clientId)
VALUES
(
    @contratCode
  , @dateEmission, @dateEffet, @dateExpiration
  , (SELECT agenceId FROM AGENCE WHERE agenceCode = @agenceCode)
  , (SELECT clientId FROM CLIENT WHERE clientCode = @clientCode)
) ;

------------------------------------------------
-- Le contrat est du type "mono"
-------------------------------------------------

INSERT INTO CONTRAT_MONO (contratId, vehiculeId)
values
    ((SELECT contratId FROM CONTRAT WHERE contratCode = @contratCode)
   , (SELECT vehiculeId FROM VEHICULE WHERE vehiculeCode = @vehiculeCode))
;

END

GO

Création d’un contrat "mono" pour l’agence '2100', concernant le véhicule de code 'veh011', émis le '2021-01-07', prenant effet le '2021-01-08', expirant le '2024-01-07' et le code du client concerné étant 'cli001' :

DECLARE @contratCodeNouveau as CHAR(14) ;

EXEC CONTRAT_MONO_CAT_PROC
        '2100', 'veh011'
      , '2021-01-07', '2021-01-08', '2024-01-07'
      , 'cli001'
      , @contratCodeNouveau OUTPUT

A noter que le paramètre OUTPUT indique que la variable @contratCodeNouveau contiendra la valeur du code contrat venant d’être créé.

Au résultat :

SELECT * FROM  CONTRAT_MONO_VUE
WHERE contratCode = @contratCodeNouveau ;

=>

contratCode       agence   dateEmission   dateEffet    expiration   client  vehCode  catCode

21004102100002    2100     2021-01-07     2021-01-08   2024-01-07   cli001  veh011   110

La structure que j’ai utilisée pour la table CONTRAT est limitée aux attributs suivants (outre bien sûr contratId) :

        agenceId
      , contratCode
      , contratDateEmission
      , contratDateEffet
      , contratDateExpiration
      , clientId

Sachant que la procédure utilise la liste suivante (peu importe l’ordre) :

    @contratCode as CHAR(14) OUTPUT   
  , @agenceCode as CHAR(4) 
  , @vehiculeCode as CHAR(8) 
  , @dateEmission as DATE
  , @dateEffet as DATE
  , @dateExpiration as DATE
  , @clientCode as CHAR(10))

De votre côté, vous aurez aussi à prendre en compte d’autres attributs de la table CONTRAT : reduction, coupoliceId, etc. L’INSERT dans la table CONTRAT étant « encapsulé » dans la procédure, cette liste devra âtre enrichie en conséquence :

    @contratCode as CHAR(14) OUTPUT   
  , @agenceCode as CHAR(4) 
  , @vehiculeCode as CHAR(8) 
  , @dateEmission as DATE
  , @dateEffet as DATE
  , @dateExpiration as DATE
  , @clientCode as CHAR(10)) 
  , @reduction as DECIMAL (2,2)
  , @coupoliceId as INT
  , ...

Même chose au cas où au fil du temps vous ajouteriez des attributs dans la table CONTRAT.

A bientôt

**fsmrel** · 08/02/2021, 02h53

Bonsoir Zidane7,

Les flottes.

Les tables impliquées :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
CREATE TABLE AGENCE
(
        agenceId           INT  IDENTITY 
      , agenceCode         CHAR(4)       NOT NULL
      , agenceNom          VARCHAR(48)   NOT NULL
    , CONSTRAINT AGENCE_PK PRIMARY KEY(agenceId)
    , CONSTRAINT AGENCE_AK UNIQUE(agenceCode)
) ;

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

CREATE TABLE FLOTTE_TYPE
(
        flotteTypeId          INT  IDENTITY  
      , flotteTypeCode        CHAR(3)            NOT NULL
      , flotteTypeLibelle     VARCHAR(64)        NOT NULL
    , CONSTRAINT FLOTTE_TYPE_PK PRIMARY KEY(flotteTypeId)
    , CONSTRAINT FLOTTE_TYPE_AK UNIQUE(flotteTypeCode)
);
CREATE TABLE CAT_COMPLEMENT
(
        catId                    INT
      , catComplementId          INT IDENTITY 
      , catComplementCode        CHAR(5)         NOT NULL 
      , catComplementLibelle     VARCHAR(96)     NOT NULL
    , CONSTRAINT CAT_COMPLEMENT_PK PRIMARY KEY(catId, catComplementId)
    , CONSTRAINT CAT_COMPLEMENT_AK UNIQUE(catComplementCode)
    , CONSTRAINT CAT_COMPLEMENT_CATEGORIE_FK FOREIGN KEY (catId)
          REFERENCES CATEGORIE  (catId)
);

CREATE TABLE VEHICULE
(
        vehiculeId               INT  IDENTITY
      , vehiculeCode             CHAR(8)       NOT NULL
      , vehiculeSerie            CHAR(17)      NOT NULL
      , puissanceId              INT           NOT NULL
      , vehiculePuissance        INT           NOT NULL
      , vehiculeMarque           VARCHAR(24)   NOT NULL
      , catId                    INT           NOT NULL
      , catComplementId          INT           NOT NULL
    , CONSTRAINT VEHICULE_PK PRIMARY KEY(vehiculeId)
    , CONSTRAINT VEHICULE_AK UNIQUE(vehiculeCode)
    , CONSTRAINT VEHICULE_1_AK UNIQUE(vehiculeSerie)
    , CONSTRAINT VEHICULE_PUISSANCE_FK FOREIGN KEY(puissanceId) 
          REFERENCES PUISSANCE (puissanceId)
    , CONSTRAINT VEHICULE_TARIF_FK FOREIGN KEY(catId, catComplementId) 
          REFERENCES CAT_COMPLEMENT (catId, catComplementId)
);
CREATE TABLE CONTRAT
(
        agenceId                 INT          NOT NULL
      , contratId                INT   IDENTITY
      , contratCode              CHAR(14)     NOT NULL
      , contratDateEmission      DATE         NOT NULL
      , contratDateEffet         DATE         NOT NULL
      , contratDateExpiration    DATE         NOT NULL
      , clientId                 INT          NOT NULL
    , CONSTRAINT CONTRAT_PK PRIMARY KEY(contratId)
    , CONSTRAINT CONTRAT_AK UNIQUE(contratCode)
    , CONSTRAINT CONTRAT_AGENCE_FK FOREIGN KEY(agenceId) 
          REFERENCES AGENCE(agenceId)
    , CONSTRAINT CONTRAT_CLIENT_FK FOREIGN KEY(clientId) 
          REFERENCES CLIENT(clientId)
    , CONSTRAINT CONTRAT_DATE_EFFET_CHK 
         CHECK (contratDateEffet >= contratDateEmission)
    , CONSTRAINT CONTRAT_DATE_EXPIRATION_CHK 
         CHECK (contratDateEffet < contratDateExpiration) 
   , CONSTRAINT CONTRAT_EMISSION_BORNES_CHK 
         CHECK (contratDateEmission BETWEEN '1974-01-01' AND CONCAT((1+YEAR(GETDATE())), '-07-15'))
   , CONSTRAINT CONTRAT_EMISSION_COHERENCE_CHK 
         CHECK (SUBSTRING(contratCode,8,2) = SUBSTRING(CAST(contratDateEmission as CHAR(4)),3,2))
) ;
CREATE TABLE CONTRAT_MONO
(
        contratId                INT     NOT NULL 
      , vehiculeId               INT     NOT NULL
    , CONSTRAINT CONTRAT_MONO_PK PRIMARY KEY(contratId)
    , CONSTRAINT CONTRAT_MONO_AK UNIQUE(vehiculeId)
    , CONSTRAINT CONTRAT_MONO_CONTRAT_FK FOREIGN KEY(contratId) 
          REFERENCES CONTRAT(contratId)
    , CONSTRAINT CONTRAT_MONO_VEHICULE_FK FOREIGN KEY(vehiculeId) 
          REFERENCES VEHICULE(vehiculeId)
--    , CONSTRAINT CONTRAT_MONO_CATEGORIE_FK FOREIGN KEY(catId) 
--          REFERENCES CATEGORIE(catId)
);
 
CREATE TABLE CONTRAT_FLOTTE
(
        contratId                INT     NOT NULL 
      , flotteTypeId             INT     NOT NULL
    , CONSTRAINT CONTRAT_FLOTTE_PK PRIMARY KEY(contratId)
    , CONSTRAINT CONTRAT_FLOTTE_CONTRAT_FK FOREIGN KEY(contratId) 
          REFERENCES CONTRAT(contratId)
    , CONSTRAINT CONTRAT_FLOTTE_FLOTTE_TYPE_FK FOREIGN KEY(flotteTypeId) 
          REFERENCES FLOTTE_TYPE(flotteTypeId)
);
 
CREATE TABLE VEH_FLOTTE
(
        vehiculeId         INT
      , contratId          INT            NOT NULL
    , CONSTRAINT VEH_FLOTTE_PK PRIMARY KEY(vehiculeId)
    , CONSTRAINT VEH_FLOTTE_VEHICULE_FK FOREIGN KEY(vehiculeId) 
          REFERENCES VEHICULE(vehiculeId)
    , CONSTRAINT VEH_FLOTTE_CONTRAT_FLOTTE_FK FOREIGN KEY(contratId) 
          REFERENCES CONTRAT_FLOTTE(contratId)
);
GO

En s’inspirant de la création des contrats "mono" (cf. fonction, post #156), on peut créer un contrat "flotte" de la façon suivante avec T-SQL :

--------------------------------------------------------------------
-- Créons le contrat "flotte", émis le '2021-01-03',
-- prenant effet le '2021-01-04', expirant le '2024-01-03',
--  relevant de l’agence '2100', et appartenant au client dont
-- le code est 'cli001'.
--------------------------------------------------------------------

DECLARE @contratCode as CHAR(14)
      , @agenceCode as CHAR(4) 
      , @flotteTypeCode as CHAR(3)
      , @dateEmission as DATE
      , @dateEffet as DATE
      , @dateExpiration as DATE
      , @clientCode as CHAR(10)
 
DECLARE @anneeEmission as CHAR(2) ;

SET @flotteTypeCode = '591' ;
SET @agenceCode = '2100' ;
SET @dateEmission = '2021-01-03' ;
SET @dateEffet = '2021-01-04' ;
SET @dateExpiration = '2024-01-03' ;
SET @clientCode = 'cli001' ;

SET @anneeEmission = RIGHT(YEAR(@dateEmission),2) ;

------------------------------------------------------
-- On demande à la fonction CONTRAT_SEQUENCE_FN de
-- calculer le nouveau code contrat, en fonction du
-- code agence, du type de flotte et de l'année de 
-- la date d’émission du contrat. 
------------------------------------------------------

SET @contratCode = dbo.CONTRAT_SEQUENCE_FN(@agenceCode, @flotteTypeCode, @anneeEmission) ;

------------------------------------------------
-- Création du nouveau contrat, dont le code
-- est présent dans la variable @contratCode
-------------------------------------------------


INSERT INTO CONTRAT (contratCode, contratDateEmission
                   , contratDateEffet, contratdateExpiration
                   , agenceId, clientId)
VALUES
(
    @contratCode
  , @dateEmission, @dateEffet, @dateExpiration
  , (SELECT agenceId FROM AGENCE WHERE agenceCode = @agenceCode)
  , (SELECT clientId FROM CLIENT WHERE clientCode = @clientCode)
) ;


------------------------------------------------
-- Le contrat est du type "flotte"
-------------------------------------------------

INSERT INTO CONTRAT_FLOTTE (contratId, flotteTypeId)
VALUES
    ((SELECT contratId FROM CONTRAT WHERE contratCode = @contratCode)
   , (SELECT flotteTypeId FROM FLOTTE_TYPE WHERE flotteTypeCode = @flotteTypeCode))
;

--------------------------------------------
-- Les véhicules appartenant à la flotte :
'vehf51', 'vehf52', 'vehf53'.
--------------------------------------------

INSERT INTO VEH_FLOTTE (contratId, vehiculeId)
VALUES

   ((SELECT contratId FROM CONTRAT WHERE contratCode = @contratCode) 
  , (SELECT vehiculeId FROM VEHICULE WHERE vehiculeCode = 'vehf51'))
,
   ((SELECT contratId FROM CONTRAT WHERE contratCode = @contratCode) 
  , (SELECT vehiculeId FROM VEHICULE WHERE vehiculeCode = 'vehf52'))
,
   ((SELECT contratId FROM CONTRAT WHERE contratCode = @contratCode) 
  , (SELECT vehiculeId FROM VEHICULE WHERE vehiculeCode = 'vehf53'))
;

Au résultat :

SELECT contratCode, agenceCode as agence
     , contratDateEmission as dateEmission
     , contratDateEffet as dateEffet  
     , contratdateExpiration as expiration   
     , clientCode as client
     , flotteTypeCode as flotteCode  
     , vehiculeCode, catCode 
FROM   CONTRAT as x
  JOIN CONTRAT_FLOTTE as y ON x.contratId = y.contratId 
  JOIN AGENCE as z ON x.agenceId = z.agenceId
  JOIN CLIENT as t ON x.clientId = t.clientId
  JOIN FLOTTE_TYPE as u ON y.flotteTypeId = u.flotteTypeId
  JOIN VEH_FLOTTE as v ON v.contratId = y.contratId
  JOIN VEHICULE as w ON w.vehiculeid = v.vehiculeId
  JOIN CATEGORIE as r ON r.catId = w.catId
;

=>

contratCode     agence dateEmission dateEffet  expiration   client   flotteCode  vehiculeCode catCode

21005912100006  2100   2021-01-03   2021-01-04 2024-01-03   cli001   591         vehf51       410
21005912100006  2100   2021-01-03   2021-01-04 2024-01-03   cli001   591         vehf52       410
21005912100006  2100   2021-01-03   2021-01-04 2024-01-03   cli001   591         vehf53       410

Comme dans le cas des contrats "mono", de la requête précédente on peut faire une vue pour en « encapsuler » le verbiage :

GO
CREATE VIEW CONTRAT_FLOTTE_VUE
(
    contratCode, agence
  , dateEmission, dateEffet, expiration
  , client, flotteCode, vehiculeCode, catCode   
)
AS
    SELECT contratCode, agenceCode
         , contratDateEmission, contratDateEffet, contratdateExpiration
         , clientCode, flotteTypeCode, vehiculeCode, catCode 
    FROM   CONTRAT as x
      JOIN CONTRAT_FLOTTE as y ON x.contratId = y.contratId 
      JOIN AGENCE as z ON x.agenceId = z.agenceId
      JOIN CLIENT as t ON x.clientId = t.clientId
      JOIN FLOTTE_TYPE as u ON y.flotteTypeId = u.flotteTypeId
      JOIN VEH_FLOTTE as v ON v.contratId = y.contratId
      JOIN VEHICULE as w ON w.vehiculeid = v.vehiculeId
      JOIN CATEGORIE as r ON r.catId = w.catId
;
GO

Avec la vue, le résultat est le même :

SELECT * 
FROM   CONTRAT_FLOTTE_VUE ;

=>

contratCode     agence dateEmission dateEffet  expiration   client   flotteCode  vehiculeCode catCode

21005912100006  2100   2021-01-03   2021-01-04 2024-01-03   cli001   591         vehf51       410
21005912100006  2100   2021-01-03   2021-01-04 2024-01-03   cli001   591         vehf52       410
21005912100006  2100   2021-01-03   2021-01-04 2024-01-03   cli001   591         vehf53       410

Comme dans le cas des contrats "mono", les INSERTS peuvent faire l’objet d’une procédure :

GO
CREATE PROCEDURE CONTRAT_FLOTTE_PROC
(
    @contratCode as CHAR(14) OUTPUT 
  , @agenceCode as CHAR(4) 
  , @flotteTypeCode as CHAR(3)
  , @dateEmission as DATE
  , @dateEffet as DATE
  , @dateExpiration as DATE
  , @clientCode as CHAR(10)
)
AS
BEGIN 
 
DECLARE @anneeEmission as CHAR(2) ;

SET @anneeEmission = RIGHT(YEAR(@dateEmission),2) ;

------------------------------------------------------
-- On demande à la fonction CONTRAT_SEQUENCE_FN de
-- calculer le nouveau code contrat, en fonction du
-- code agence, du type de flotte et de l'année de 
-- la date d’émission du contrat. 
------------------------------------------------------

SET @contratCode = dbo.CONTRAT_SEQUENCE_FN(@agenceCode, @flotteTypeCode, @anneeEmission) ;

------------------------------------------------
-- Création du nouveau contrat, dont le code
-- est présent dans la variable @contratCode
-------------------------------------------------

INSERT INTO CONTRAT (contratCode, contratDateEmission
                   , contratDateEffet, contratdateExpiration
                   , agenceId, clientId)
VALUES
(
    @contratCode
  , @dateEmission, @dateEffet, @dateExpiration
  , (SELECT agenceId FROM AGENCE WHERE agenceCode = @agenceCode)
  , (SELECT clientId FROM CLIENT WHERE clientCode = @clientCode)
) ;

------------------------------------------------
-- Le contrat est du type "flotte"
-------------------------------------------------

INSERT INTO CONTRAT_FLOTTE (contratId, flotteTypeId)
VALUES
    ((SELECT contratId FROM CONTRAT WHERE contratCode = @contratCode)
   , (SELECT flotteTypeId FROM FLOTTE_TYPE WHERE flotteTypeCode = @flotteTypeCode))
;

END

GO

Pour reprendre la création du contrat qui précède (code '21005912100006'), en utilisant la procédure :

EXEC CONTRAT_FLOTTE_PROC
        @contratCode OUTPUT
      , '2100', '591'
      , '2021-01-03', '2021-01-04', '2024-01-03'
      , 'cli001'

Exclusion entre catégories et types de flottes

Le MCD (cf. post #156) comporte une contrainte d’exclusion entre les associations VEH_MONO et VEH_FLOTTE, signifiant qu’un véhicule ne peut pas appartenir à la fois à une catégorie et à un type de flotte :

Nom : Zidane7_assurance_auto (exclusion_categorie_flotte).png
Affichages : 161
Taille : 19,5 Ko

En conséquence, on crée la contrainte SQL idoine, faisant intervenir les tables VEH_FLOTTE et CONTRAT_MONO :

GO
CREATE FUNCTION VEHICULE_MONO_FLOTTE_EXCLUSION_FN()
RETURNS INT  
AS   
BEGIN  
   DECLARE @n INT  
   SELECT @n = COUNT(*) FROM VEH_FLOTTE as x
    JOIN   CONTRAT_MONO as y ON x.vehiculeId = y.vehiculeId
   RETURN @n  
END ;  
GO

Et on force les tables à respecter la contrainte :

ALTER TABLE CONTRAT_MONO  
ADD CONSTRAINT CONTRAT_MONO_EXCLUSION_CHECK CHECK (dbo.VEHICULE_MONO_FLOTTE_EXCLUSION_FN() = 0) ;  
;
ALTER TABLE VEH_FLOTTE  
ADD CONSTRAINT FLOTTE_TYPE_MONO_EXCLUSION_CHECK CHECK (dbo.VEHICULE_MONO_FLOTTE_EXCLUSION_FN() = 0) ;

Exemple. On crée le véhicule de code 'veh011' et on l’affecte à la catégorie '110' :

INSERT INTO VEHICULE (vehiculeCode, vehiculeSerie, vehiculePuissance, vehiculeMarque
                    , puissanceId, catId, catComplementId)
VALUES
    ('veh011', 'VM345678901234567', 15 , 'Mercedes' 
    , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode= 'C5')
    , (SELECT catId FROM CATEGORIE WHERE catCode = '110')
    , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode ='1100')) ;

Créons un contrat de type flotte, affecté à la flotte '591'

EXEC CONTRAT_FLOTTE_PROC
        @contratCodeNouveau OUTPUT
      , '2100', '591'
      , '2021-01-03', '2021-01-04', '2024-01-03'
      , 'cli001' ;

Tentons ensuite de lui affecter le véhicule de code 'veh011', déjà affecté à la catégorie '110' :

INSERT INTO VEH_FLOTTE (contratId, vehiculeId)
VALUES
   ((SELECT contratId FROM CONTRAT WHERE contratCode = @contratCodeNouveau) 
  , (SELECT vehiculeId FROM VEHICULE WHERE vehiculeCode = 'veh011')) ;

=>

Msg 547, Niveau 16, État 0, Ligne 1074
L'instruction INSERT est en conflit avec la contrainte CHECK "FLOTTE_TYPE_MONO_EXCLUSION_CHECK". 
Le conflit s'est produit dans la base de données "test", table "dbo.VEH_FLOTTE".

Votre MCD contient l’association FLOTTE_TYPE_CAT mettant en relation les entités-types CATEGORIE et TYPE_FLOTTE : je suppose qu’il s’agit de permettre de définir quelles catégories sont compatibles avec quels types de flottes. Pourriez-vous fournir les paires de valeurs qui constitueront la table (SQL) FLOTTE_TYPE_CAT ?

**Zidane7** · 10/02/2021, 10h27

Bonjour Monsieur fsmrel,

fsmrel
Votre MCD contient l’association FLOTTE_TYPE_CAT mettant en relation les entités-types CATEGORIE et TYPE_FLOTTE : je suppose qu’il s’agit de permettre de définir quelles catégories sont compatibles avec quels types de flottes. Pourriez-vous fournir les paires de valeurs qui constitueront la table (SQL) FLOTTE_TYPE_CAT ?

Oui à ce niveau, regardez le code sql ci-dessous:

Code :

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

1
2
3
4
5
6
CREATE TABLE FLOTTE_TYPE_CAT(
   categorieId INT,
   flotte_typeId INT,
   PRIMARY KEY(categorieId, flotte_typeId),
   FOREIGN KEY(categorieId) REFERENCES CATEGORIE(categorieId),
   FOREIGN KEY(flotte_typeId) REFERENCES FLOTTE_TYPE(flotte_typeId)

Les cardinalités sont les suivantes:
Chaque FLOTTE_TYPE à au minimum 1 et au maximum n CATEGORIE c'est à dire (1,n) et une CATEGORIE peut ne pas être dans un FLOTTE_TYPE ou dans plusieurs c'est à dire (0,n). Ici, c'est pour permettre de contrôler les catégories appartenant aux types de flotte.
Quel est votre avis?

Le code sql de l'ensemble de MCD est le suivant:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
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 GARANTIE(
   garantieId INT IDENTITY,
   codegarantie CHAR(1) NOT NULL,
   Libelle_Garantie VARCHAR(50) NOT NULL,
   PRIMARY KEY(garantieId),
   UNIQUE(codegarantie)
);

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

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

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,
   modepaiementcode VARCHAR(15) NOT NULL,
   modepaiement_libelle VARCHAR(50),
   PRIMARY KEY(modepaiemenId),
   UNIQUE(modepaiementcode)
);

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

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

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

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

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

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

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

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

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

CREATE TABLE BRANCHE(
   brancheId INT IDENTITY,
   branchecode CHAR(3) NOT NULL,
   libelle_branche VARCHAR(50),
   PRIMARY KEY(brancheId),
   UNIQUE(branchecode)
);

CREATE TABLE OPTIONSECURITE(
   optionsecuriteId VARCHAR(50),
   codeoptionsecurite VARCHAR(1) NOT NULL,
   optionmontant INT,
   garantieId INT NOT NULL,
   PRIMARY KEY(optionsecuriteId),
   UNIQUE(codeoptionsecurite),
   FOREIGN KEY(garantieId) REFERENCES GARANTIE(garantieId)
);

CREATE TABLE OPTIONVALEUR(
   optionvaleurId VARCHAR(50),
   optionvaleurlibelle VARCHAR(30),
   optionvaleurmontant INT NOT NULL,
   optionsecuriteId VARCHAR(50) NOT NULL,
   PRIMARY KEY(optionvaleurId),
   FOREIGN KEY(optionsecuriteId) REFERENCES OPTIONSECURITE(optionsecuriteId)
);

CREATE TABLE FLOTTE_TYPE(
   flotte_typeId INT IDENTITY,
   flotte_typecode CHAR(3) NOT NULL,
   flottetypelibelle VARCHAR(40),
   PRIMARY KEY(flotte_typeId),
   UNIQUE(flotte_typecode)
);

CREATE TABLE SOUSCRIPTEUR(
   souscripteurId INT IDENTITY,
   nomsouscripteur VARCHAR(45),
   prenomsouscripteur VARCHAR(45),
   PRIMARY KEY(souscripteurId)
);

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

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 CAISSIERE(
   agenceId_1 INT,
   caissiereId INT IDENTITY,
   codecaissiere VARCHAR(4) NOT NULL,
   prenomcaissiere VARCHAR(40) NOT NULL,
   nomcaissiere VARCHAR(40) NOT NULL,
   agenceId INT,
   producteurId INT,
   PRIMARY KEY(agenceId_1, caissiereId),
   UNIQUE(codecaissiere),
   FOREIGN KEY(agenceId_1) REFERENCES AGENCE(agenceId),
   FOREIGN KEY(agenceId, producteurId) REFERENCES PRODUCTEUR(agenceId, producteurId)
);

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

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

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

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

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

CREATE TABLE ENCAISSEMENT_C(
   agenceId INT,
   contratId INT,
   encaisseCtrId INT IDENTITY,
   encaisseCtrMontant INT,
   encaisseCtrDate DATE,
   modepaiemenId INT NOT NULL,
   agenceId_1 INT NOT NULL,
   caissiereId INT NOT NULL,
   PRIMARY KEY(agenceId, contratId, encaisseCtrId),
   FOREIGN KEY(agenceId, contratId) REFERENCES CONTRAT(agenceId, contratId),
   FOREIGN KEY(modepaiemenId) REFERENCES MODEPAIEMENT(modepaiemenId),
   FOREIGN KEY(agenceId_1, caissiereId) REFERENCES CAISSIERE(agenceId_1, caissiereId)
);

CREATE TABLE CONTRAT_FLOTTE(
   agenceId INT,
   contratId INT,
   contratflotteId INT IDENTITY,
   flotte_typeId INT NOT NULL,
   PRIMARY KEY(agenceId, contratId, contratflotteId),
   UNIQUE(agenceId, contratId),
   FOREIGN KEY(agenceId, contratId) REFERENCES CONTRAT(agenceId, contratId),
   FOREIGN KEY(flotte_typeId) REFERENCES FLOTTE_TYPE(flotte_typeId)
);

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

CREATE TABLE CONTRAT_MONO(
   agenceId INT,
   contratId INT,
   vehiculeId INT NOT NULL,
   PRIMARY KEY(agenceId, contratId),
   UNIQUE(vehiculeId),
   FOREIGN KEY(agenceId, contratId) REFERENCES CONTRAT(agenceId, contratId),
   FOREIGN KEY(vehiculeId) REFERENCES VEHICULE(vehiculeId)
);

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

CREATE TABLE FLOTTE_TYPE_CAT(
   categorieId INT,
   flotte_typeId INT,
   PRIMARY KEY(categorieId, flotte_typeId),
   FOREIGN KEY(categorieId) REFERENCES CATEGORIE(categorieId),
   FOREIGN KEY(flotte_typeId) REFERENCES FLOTTE_TYPE(flotte_typeId)
);

GO
CREATE FUNCTION CONTRAT_SEQUENCE_FN(@agenceCode as CHAR(3)
                                  , @categorieCode as CHAR(3)
                                  , @anneeEmission as CHAR(2)) 
    RETURNS CHAR(14)  
AS   
BEGIN
  
DECLARE @n INT = 0 ;
DECLARE @agenceId INT ; 
DECLARE @categorieId INT ;
DECLARE @@contratCodeLen INT = 14 ;
DECLARE @contratCodeNouveau CHAR(14) ;
DECLARE @contratCodeNew CHAR(14) ;
DECLARE @contratCodePrecedent CHAR(14) ;
DECLARE @SequenceDebutAnnee CHAR(6) = '000001' ;
DECLARE @SequenceContrat CHAR(6) ;
DECLARE @SequenceContratNum INT ;
DECLARE @SequenceContratPrecedent CHAR(6) ;
DECLARE @SequenceContratPrecedentNum INT ;
DECLARE @seqLen as INT = 6
DECLARE @anneeContratOffset INT = 7
DECLARE @anneeMax  INT ;
DECLARE @anneeContratPrecedent CHAR(2) ;
DECLARE @zero CHAR(6) = '000000' ;
DECLARE @anneeErreur CHAR(6) = '??????' ;

SET @agenceId = (SELECT agenceId FROM AGENCE WHERE agenceCode = @agenceCode)
SET @categorieId = (SELECT categorieId FROM CATEGORIE WHERE categorieCode = @categorieCode)       
SET @contratCodeNouveau = CONCAT(@agenceCode, @categorieCode, @anneeEmission, @anneeErreur)

----------------------------------------------------
-- On regarde si la table CONTRAT contient au moins 
-- un contrat pour l’agence et l'année concernées.
----------------------------------------------------

SET @anneeMax = (SELECT MAX(SUBSTRING(contratCode, @anneeContratOffset, 2))
                 FROM   CONTRAT 
                 WHERE  agenceId = @agenceId) ;

SET @n = (SELECT COUNT(*) 
          FROM   CONTRAT 
          WHERE  agenceId = @agenceId
            AND  SUBSTRING(contratCode, @anneeContratOffset, 2) = @anneeEmission) ;

-----------------------------------------------------
-- Si la table CONTRAT contient au moins un contrat
-- pour l'agence et l'année concernées, on récupère 
-- dans le dernier contrat créé correspondant :
--   son numéro de série, 
--   son code contrat, 
--   sa date d'émission.
-----------------------------------------------------

IF @n > 0 
    BEGIN
        SET @SequenceContratPrecedent = 
              (SELECT MAX(RIGHT(contratCode,@seqLen))
               FROM   CONTRAT 
               WHERE  agenceId = @agenceId 
                 AND  SUBSTRING(contratCode, @anneeContratOffset, 2) = @anneeEmission)

        SET @contratCodePrecedent = 
              (SELECT contratCode
               FROM   CONTRAT 
               WHERE  agenceId = @agenceId 
                 AND  SUBSTRING(contratCode, @anneeContratOffset, 2) = @anneeEmission
                 AND RIGHT(contratCode,@seqLen) = @SequenceContratPrecedent) ;

        SET @anneeContratPrecedent = SUBSTRING(@contratCodePrecedent, @anneeContratOffset, 2) ;
   END

ELSE

--------------------------------------------------------------
-- Si la table CONTRAT ne contient aucun contrat pour
-- l’agence et l'année concernées, alors on force le numéro  
-- de police à '000001' pour le contrat à créer.
--------------------------------------------------------------

    BEGIN
           SET @contratCodeNouveau = 
               CONCAT(@agenceCode
             , @categorieCode
             , @anneeEmission
             , @SequenceDebutAnnee)
    END

-----------------------------------------------------
-- Si pour l’agence concernée, le dernier contrat 
-- créé Cn est de la même année que celui qui est à
-- créer, on incrémente d'une unité le numéro de 
-- police récupéré dans le contrat Cn 
-- (variable @SequenceContrat). 
-----------------------------------------------------

IF  @n > 0 AND @anneeEmission = @anneeContratPrecedent 
    BEGIN
        SET @SequenceContratPrecedent = RIGHT(@contratCodePrecedent, @seqLen) ;
        SET @SequenceContratPrecedentNum = CAST(@SequenceContratPrecedent as INT) ;

        SET @SequenceContratNum = 1 + @SequenceContratPrecedentNum
        SET @SequenceContrat = CAST(@SequenceContratNum as CHAR(6))
        SET @contratCodeNouveau = 
               CONCAT
              (
               @agenceCode
             , @categorieCode
             , @anneeEmission
             , LEFT(@zero, LEN(@zero) - LEN(@SequenceContrat))
             , @SequenceContrat
              )
    END 

--------------------------------------
-- On sort, en fournissant le nouveau
-- code contrat.
--------------------------------------

RETURN @contratCodeNouveau 
 
END 

GO

Par rapport à vos propositions des postes #157, #158, je trouve que c'est excellent.

Merci d'avance.

**fsmrel** · 12/02/2021, 00h20

Bonsoir Zidane7,

Association VEH_FLOTTE

Dans mon MCD du post #158, il existe l’association VEH_FLOTTE :

[VEHICULE]--0,1--(VEH_FLOTTE)--1,n--[CONTRAT_FLOTTE]

Association selon laquelle un contrat "flotte" peut comporter plusieurs véhicules, mais un véhicule ne fait pas nécessairement partie d’un contrat de ce type (il s’agit alors du rattachement à un contrat "mono").

Il est impératif que l’association VEH_FLOTTE fasse en SQL l’objet d’une table (voyez encore le post #158) :

CREATE TABLE VEH_FLOTTE
(
        vehiculeId         INT
      , contratId          INT            NOT NULL
    , CONSTRAINT VEH_FLOTTE_PK PRIMARY KEY(vehiculeId)
    , CONSTRAINT VEH_FLOTTE_VEHICULE_FK FOREIGN KEY(vehiculeId) 
          REFERENCES VEHICULE(vehiculeId)
    , CONSTRAINT VEH_FLOTTE_CONTRAT_FLOTTE_FK FOREIGN KEY(contratId) 
          REFERENCES CONTRAT_FLOTTE(contratId)
);

Pour arriver à cela, il faut faire une demande à Looping : ouvrir la fenêtre d’association concernant l’association VEH_FLOTTE, cocher la case « Générer une table de correspondance dans le MLD », puis cliquer sur le bouton que j’ai référencé ainsi « Générer une table » :

Nom : Zidane7_assurance_auto (veh_flotte deviendra table).png
Affichages : 140
Taille : 17,8 Ko

Exclusion des véhicules « mono » et « flotte »

Reprenons cette partie du MCD (post #158) :

Nom : Zidane7_assurance_auto (veh_flotte exclusion XT).png
Affichages : 144
Taille : 67,5 Ko

Une fois la demande de création de la table VEH_FLOTTE satisfaite, vous pourrez créer une règle permettant de traduire la contrainte d’exclusion en SQL :

Nom : Zidane7_assurance_auto (veh_flotte exclusion XT, règle).png
Affichages : 145
Taille : 27,3 Ko

=>

Nom : Zidane7_assurance_auto (veh_flotte exclusion XT et règle).png
Affichages : 144
Taille : 81,8 Ko

Pour ne pas surcharger le MCD, vous pouvez demander à Looping de ne pas afficher la règle (ne pas cocher la case « Afficher dans le modèle »).

Envoyé par Zidane7

Chaque FLOTTE_TYPE à au minimum 1 et au maximum n CATEGORIE c'est à dire (1,n) et une CATEGORIE peut ne pas être dans un FLOTTE_TYPE ou dans plusieurs c'est à dire (0,n). Ici, c'est pour permettre de contrôler les catégories appartenant aux types de flotte.

Je suis évidemment d’accord sur le principe ! Mais la question que j’ai posée est la suivante :

« Pourriez-vous fournir les paires de valeurs qui constitueront la table (SQL) FLOTTE_TYPE_CAT ? »

J’ai besoin de voir ces paires de valeurs pour juger de la faisabilité de la valorisation raisonnable de la table.

Pour y voir vraiment clair, j’ai aussi besoin de connaître les catégories ainsi que les sous-catégories et les types de flottes.

Pour des raisons de confidentialité, vous pouvez me transmettre ces informations par message privé.

Conception d'un MCD pour une assurance automobile

Schéma

Discussions similaires

Partager

Partager