Discussion :

[Zidane7]

Bonjour Messieurs,

Zidane7
Dans certaines agences, un producteur peut produire et encaisser en même temps c'est pour cela que je disais qu'un producteur peut être à la fois producteur et caissière simultanément et dans d'autres agences un producteur peut être différent d'une caissière. Aussi une caissière ou producteur peut être muté(e) d'une agence à une autre.

J’éclaircis cette partie.
Nous avons l'AGENCE Principale (le siège) au sein duquel existe une caissière qui encaisse les montants des contrats du siège, d'autres agences, des courtiers si on lui donne le droit d’accès (ou d’encaisser) de ces agences et courtiers etc... Les producteurs du siège peuvent aussi produire des contrats appartenant à des courtiers ou agences secondaires si on leur donne le droit d'accès aussi. Ensuite chaque contrat est codifié de la manière suivante: NumeroAgenceCodecategorieAnneeNumeropolice(incrementé). A chaque an, on change l'année et le numéro de police commence par un(1) et augustement (ou incremente) progressivement jusqu'à la fin d'année (31/12/....) Exemple: 2105102100001 (Siège, 210=Code agence, 510=code catégorie, 21=Année, 00001=premier numéro de police du siège par exemple). Et si c'est une flotte, nous remplaçons le code catégorie par code flotte.
Ensuite ils existent des bureaux directs qui ne reçoivent pas des commissions sauf s'ils amènent des affaires importantes telle qu'une flotte. Il y a aussi des agences indépendantes qui reçoivent des commissions sur chaque affaire qu'ils apportent tout comme les apporteurs du siège.

escartefigue
L'association (VENIR) entre [CAISSIERE] et [AGENCE] me surprend par son nom et ses cardinalités :
Pourquoi "venir" quelle est la raison d'être de cette association ?
Identifier la caissière relativement à l'agence sera fatal aux caissières en cas de fermeture d'agence

Merci pour cette remarque.

fsmrel
A Zidane7 de voir si les encaissements faits par Mado sont à affecter à Patricia (cuisine douteuse)

Non cela est impossible. Merci.

fsmrel
Encore une boucle
On peut aller de véhicule à CLIENT via le chemin direct VEHICULE > APPARTENIR > CLIENT ou via le chemin des écoliers VEHICULE > EST_SOUSCRIT > CONTRAT > ASSURE > CLIENT.

Si le client auquel appartient le véhicule n’est pas nécessairement celui qui est assuré pour ce véhicule, ces deux chemins sont légitimes. Par contre, si le client auquel appartient le véhicule est nécessairement celui qui est assuré pour ce véhicule, alors le chemin direct doit disparaître dans le MCD car on sait l’obtenir à partir de l’autre chemin (jointure SQL).

Ici un véhicule peut être souscrit par une société qui désir mettre le nom de chaque conducteur sur chaque carte grise sous couvert le nom de la société.
Que pensez-vous?
Merci d'avance.
Voici les modifications dont j'ai apportées sur le MCD et le code sql ci-dessous:

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

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

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

CREATE TABLE 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_RECOURS(
tauxDrId INT IDENTITY,
tauxDrdebut DATE NOT NULL,
tauxDrfin DATE NOT NULL,
tauxvaleure DECIMAL(2,2) NOT NULL,
PRIMARY KEY(tauxDrId)
);

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

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

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

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

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

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

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

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

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

CREATE TABLE TARIF(
categorieId INT,
catcomplementId INT,
puissanceId INT,
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(
contratId INT,
encaisseCtrId INT IDENTITY,
encaisseCtrMontant INT,
encaisseCtrDate DATE,
modepaiemenId INT NOT NULL,
agenceId INT NOT NULL,
caissiereId INT NOT NULL,
PRIMARY KEY(contratId, encaisseCtrId),
FOREIGN KEY(contratId) REFERENCES CONTRAT(contratId),
FOREIGN KEY(modepaiemenId) REFERENCES MODEPAIEMENT(modepaiemenId),
FOREIGN KEY(agenceId, caissiereId) REFERENCES CAISSIERE(agenceId_1, caissiereId)
);

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 CONT_CATCOMP(
contratId INT,
categorieId INT,
catcomplementId INT,
TYPECONTRAT VARCHAR(15) NOT NULL,
PRIMARY KEY(contratId, categorieId, catcomplementId),
UNIQUE(TYPECONTRAT),
FOREIGN KEY(contratId) REFERENCES CONTRAT(contratId),
FOREIGN KEY(categorieId, catcomplementId) REFERENCES CATCOMPLEMENT(categorieId, catcomplementId)
);

[escartefigue]

Bonjour Zidane7

Nous avons l'AGENCE Principale (le siège) au sein duquel existe une caissière qui encaisse les montants des contrats du siège, d'autres agences, des courtiers si on lui donne le droit d’accès (ou d’encaisser) de ces agences et courtiers etc... Les producteurs du siège peuvent aussi produire des contrats appartenant à des courtiers ou agences secondaires si on leur donne le droit d'accès aussi.

Si vous devez intégrer les privilèges des différents intervenants, alors vous pouvez modéliser quelque chose comme
AGENT 1,1 --- associer --- 0,n PROFIL_METIER 1,n --- autoriser --- 0,n FONCTION

Ensuite chaque contrat est codifié de la manière suivante : NumeroAgenceCodecategorieAnneeNumeropolice(incrementé). A chaque an, on change l'année et le numéro de police commence par un(1) et augustement (ou incremente) progressivement jusqu'à la fin d'année (31/12/....) Exemple: 2105102100001 (Siège, 210=Code agence, 510=code catégorie, 21=Année, 00001=premier numéro de police du siège par exemple). Et si c'est une flotte, nous remplaçons le code catégorie par code flotte.

Cette construction est du ressort du traitement qui concaténera et mettra en forme les différents identifiants et codes comme souhaité, c'est sans impact sur le modèle conceptuel des données.

Ici un véhicule peut être souscrit par une société qui désir mettre le nom de chaque conducteur sur chaque carte grise sous couvert le nom de la société.

Je ne comprends pas cette phrase.
Comme le MCD est tout petit, je n'arrive pas non plus à voir le lien entre véhicule et contrat (un contrat d'assurance concerne -t-il un et un seul ou éventuellement plusieurs véhicules ?)

[Zidane7]

Bonsoir Monsieur,

un contrat d'assurance concerne -t-il un et un seul ou éventuellement plusieurs véhicules ?

Un contrat d'assurance concerne un ou plusieurs véhicule(s) selon que cela soit mono ou flotte.
Vous ne comprenez pas les liaisons entre mes entités ou y a t-il des points incomplets?
Regardez le code sql et le MCD ci-dessous:

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

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

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

CREATE TABLE 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_RECOURS(
tauxDrId INT IDENTITY,
tauxDrdebut DATE NOT NULL,
tauxDrfin DATE NOT NULL,
tauxvaleure DECIMAL(2,2) NOT NULL,
PRIMARY KEY(tauxDrId)
);

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

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

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

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

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

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

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

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

CREATE TABLE CONTRAT(
agenceId INT,
contratId INT IDENTITY,
codecontrat VARCHAR(10) NOT NULL,
dateEffetContrat DATE NOT NULL,
dateExpirationContrat DATE NOT NULL,
reduction DECIMAL(2,2),
coupoliceId VARCHAR(2) NOT NULL,
clientId INT NOT NULL,
apporteurId INT,
agenceId_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 VEHIC_GARANT(
vehiculeId INT,
garantieId INT,
PRIMARY KEY(vehiculeId, garantieId),
FOREIGN KEY(vehiculeId) REFERENCES VEHICULE(vehiculeId),
FOREIGN KEY(garantieId) REFERENCES GARANTIE(garantieId)
);

CREATE TABLE CONT_CATCOMP(
agenceId INT,
contratId INT,
categorieId INT,
catcomplementId INT,
TYPECONTRAT VARCHAR(15) NOT NULL,
PRIMARY KEY(agenceId, contratId, categorieId, catcomplementId),
UNIQUE(TYPECONTRAT),
FOREIGN KEY(agenceId, contratId) REFERENCES CONTRAT(agenceId, contratId),
FOREIGN KEY(categorieId, catcomplementId) REFERENCES CATCOMPLEMENT(categorieId, catcomplementId)
);

Merci d'avance.

[escartefigue]

C'est que j'étais sur un PC portable avec un petit écran et je n'arrivais donc pas à lire correctement le MCD, le zoom étant inopérant

Un conseil : publiez le DDL (ordres CREATE TABLE) en l'encadrant de balises CODE (symbole # de la barre d’icônes) et non pas de balises QUOTE, votre message sera plus concis

Pouvez-vous détailler (avec un exemple) ce que vous entendez par

Envoyé par Zidane7
Ici un véhicule peut être souscrit par une société qui désir mettre le nom de chaque conducteur sur chaque carte grise sous couvert le nom de la société.

[fsmrel]

Bonsoir,

Un conseil : publiez le DDL (ordres CREATE TABLE) en l'encadrant de balises CODE (symbole # de la barre d’icônes) et non pas de balises QUOTE, votre message sera plus concis

Dans le post #32 j’ai demandé à Zidane7 d’utiliser la balise et il a préféré la balise QUOTE, ce qui me convient quand même. L’emploi de la balise CODE fait que les messages sont certes plus concis, mais ça me gêne dans la relecture rapide du code SQL, donc hors de question pour moi d’en passer par cette balise.

Zidane7, on peut regarder ce que cela donne d’utiliser la balise SPOILER à la place. Certes le code est masqué, mais en cliquant sur "Montrer", on devrait voir votre code en entier. A essayer.

[fsmrel]

Bonsoir à nouveau,

Nous avons l'AGENCE Principale (le siège) au sein duquel existe une caissière qui encaisse les montants des contrats du siège, d'autres agences, des courtiers si on lui donne le droit d’accès (ou d’encaisser) de ces agences et courtiers etc.

Il n’y aurait donc qu’une et une seule caissière pour le siège ?
De même, pour chaque agence secondaire il n’y aurait qu’une seule caissière ?

Exemple: 2105102100001 (Siège, 210=Code agence, 510=code catégorie, 21=Année, 00001=premier numéro de police du siège par exemple). Et si c'est une flotte, nous remplaçons le code catégorie par code flotte.

A propos du code catégorie : s’agit-il bien du code catégorie défini par l’entité-type CATEGORIE (ou plus exactement du code complété, défini dans l’entité-type CATCOMPLEMENT, avec l’attribut catComplementCode) ?

un contrat d'assurance concerne -t-il un et un seul ou éventuellement plusieurs véhicules ?

Un contrat d'assurance concerne un ou plusieurs véhicule(s) selon que cela soit mono ou flotte.
Vous ne comprenez pas les liaisons entre mes entités ou y a t-il des points incomplets?

La question posée par le Capitaine Escartefigue est pertinente !

En effet, jusqu’au post #97 l’entité-type VEHICULE était associée à l’entité-type CONTRAT (association EST_SOUSCRIT, un véhicule étant assuré par un et un seul contrat et un contrat assurant au moins un véhicule, so far, so good). Dans les posts #99, #100, #110, #121 et #123, cette association a disparu, ne restant alors pour chaque véhicule que l’association avec le client auquel il fait référence (association APPARTENIR). On ne sait plus établir de lien entre un véhicule et un contrat, est-ce bien normal ?

Par ailleurs, je note à cette occasion que l’entité-type CONTRAT est désormais en relation avec l’entité-type CATCOMPLEMENT via l’association CONT_CATCOMP. La patte d’association entre CONTRAT et CONT_CATCOMP est porteuse d’une cardinalité 1,N : cette multiplicité N est-elle la conséquence de la prise en compte des contrats de type "flotte" ? Cela dit, pour les contrats de type "mono", N = 1. Est-ce bien ça ?

Je note à cette occasion que le type de contrat est devenu une propriété de l’association CONT_CATCOMP, au moyen de l’attribut TYPECONTRAT, lequel est du type VARCHAR(15) : s’il n’y a que deux types de contrats, vous pouvez de préférence utiliser un booléen pour les distinguer(type BIT). Cela dit, si un jour arrive un 3e type de contrat (ou plus), il faudra utiliser à nouveau une entité-type TYPE_CONTRAT (et donc transformer CONT_CATCOMP en entité-type pour ne pas avoir de ternaire, ce qui avec Looping techniquement n’est pas un problème)...

Par ailleurs, pour un contrat "mono", à l’aide d’une fonction référencée par une contrainte, on pourra limiter à 1 le nombre de participations à l’association CONT_CATCOMP (comme on l’a fait avec la fonction REMORQUE_COUNT_FN dans le post #87).

[Zidane7]

Bonsoir Messieurs,

escartefigue
Si vous devez intégrer les privilèges des différents intervenants, alors vous pouvez modéliser quelque chose comme
AGENT 1,1 --- associer --- 0,n PROFIL_METIER 1,n --- autoriser --- 0,n FONCTION

Pouvez-vous me donner un exemple de schéma?

Envoyé par Zidane7
Ensuite chaque contrat est codifié de la manière suivante : NumeroAgenceCodecategorieAnneeNumeropolice(incrementé). A chaque an, on change l'année et le numéro de police commence par un(1) et augustement (ou incremente) progressivement jusqu'à la fin d'année (31/12/....) Exemple: 2105102100001 (Siège, 210=Code agence, 510=code catégorie, 21=Année, 00001=premier numéro de police du siège par exemple).

Et si c'est une flotte, nous remplaçons le code catégorie par code flotte.
Cette construction est du ressort du traitement qui concaténera et mettra en forme les différents identifiants et codes comme souhaité, c'est sans impact sur le modèle conceptuel des données.

A cet endroit, si je lie le contrat au vehécule et c'est le code contrat qui es clé étrangère dans l'entité véhicule, que faire en ce moment par rapport au code contrat si on veut le rendre automatique?

Envoyé par Zidane7
Ici un véhicule peut être souscrit par une société qui désir mettre le nom de chaque conducteur sur chaque carte grise sous couvert le nom de la société.
Je ne comprends pas cette phrase.

Comme le MCD est tout petit, je n'arrive pas non plus à voir le lien entre véhicule et contrat (un contrat d'assurance concerne -t-il un et un seul ou éventuellement plusieurs véhicules ?)

Un contrat d'assurance concerne au moins à un ou plusieurs véhicule. Mais un véhicule est assuré par un et un seul contrat, d'où cette relation dont j'avais supprimer. Véhicule-----(1,1)---(Vehicule_contrat)----(1,n)-----Contrat. Qu'en pensez vous?

escartefigue
Pouvez-vous détailler (avec un exemple) ce que vous entendez par
Envoyé par Zidane7
Ici un véhicule peut être souscrit par une société qui désir mettre le nom de chaque conducteur sur chaque carte grise sous couvert le nom de la société.

Ici je voudrais vous dire simplement que nous avons l'assuré et le souscripteur. L' assuré est la personne à qui est accordée la garantie prévue par le
contrat d’assurance.

fsmrel
Il n’y aurait donc qu’une et une seule caissière pour le siège ?
De même, pour chaque agence secondaire il n’y aurait qu’une seule caissière ?

Oui mais en cas d'absence du caissière, l'administrateur peut donner le droit d'accès à une autre personne sous l'autorisation de la direction.
Pour chaque agence secondaire, on donne accès à chaque utilisateur pour savoir qui à fait quoi en cas d'encaissement?

fsmrel
A propos du code catégorie : s’agit-il bien du code catégorie défini par l’entité-type CATEGORIE (ou plus exactement du code complété, défini dans l’entité-type CATCOMPLEMENT, avec l’attribut catComplementCode) ?

Merci beaucoup, ici il s'agit du code catégorie défini par l'entité CATEGORIE.

fsmrel
La question posée par le Capitaine Escartefigue est pertinente !

En effet, jusqu’au post #97 l’entité-type VEHICULE était associée à l’entité-type CONTRAT (association EST_SOUSCRIT, un véhicule étant assuré par un et un seul contrat et un contrat assurant au moins un véhicule, so far, so good). Dans les posts #99, #100, #110, #121 et #123, cette association a disparu, ne restant alors pour chaque véhicule que l’association avec le client auquel il fait référence (association APPARTENIR). On ne sait plus établir de lien entre un véhicule et un contrat, est-ce bien normal ?

.
Ici je suis partie par la méthode de cascade en passant par le souscripteur ou assuré on pouvait retrouver le véhicule assuré raison pour laquelle j'avais jugé nécessaire d'enlever la relation entre le contrat et le véhicule. Quel est votre point de vue?

Par ailleurs, je note à cette occasion que l’entité-type CONTRAT est désormais en relation avec l’entité-type CATCOMPLEMENT via l’association CONT_CATCOMP. La patte d’association entre CONTRAT et CONT_CATCOMP est porteuse d’une cardinalité 1,N : cette multiplicité N est-elle la conséquence de la prise en compte des contrats de type "flotte" ? Cela dit, pour les contrats de type "mono", N = 1. Est-ce bien ça ?
Je note à cette occasion que le type de contrat est devenu une propriété de l’association CONT_CATCOMP, au moyen de l’attribut TYPECONTRAT, lequel est du type VARCHAR(15) : s’il n’y a que deux types de contrats, vous pouvez de préférence utiliser un booléen pour les distinguer(type BIT). Cela dit, si un jour arrive un 3e type de contrat (ou plus), il faudra utiliser à nouveau une entité-type TYPE_CONTRAT (et donc transformer CONT_CATCOMP en entité-type pour ne pas avoir de ternaire, ce qui avec Looping techniquement n’est pas un problème)...

Effectivement, il n'y a que deux types de contrat à savoir Mono et Flotte. Si on met le type de contrat comme booléen, en ce moment on peut mettre dans l'entité contrat?
Quelle est votre suggestion?
Merci d'avance.

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
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 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 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,
   marqueId INT NOT NULL,
   puissanceId INT NOT NULL,
   categorieId INT NOT NULL,
   catcomplementId INT NOT NULL,
   clientId INT NOT NULL,
   PRIMARY KEY(vehiculeId),
   UNIQUE(codevehicule),
   FOREIGN KEY(marqueId) REFERENCES MARQUE(marqueId),
   FOREIGN KEY(puissanceId) REFERENCES PUISSANCE(puissanceId),
   FOREIGN KEY(categorieId, catcomplementId) REFERENCES CATCOMPLEMENT(categorieId, catcomplementId),
   FOREIGN KEY(clientId) REFERENCES CLIENT(clientId)
);

CREATE TABLE CONTRAT(
   agenceId INT,
   contratId INT IDENTITY,
   codecontrat VARCHAR(10) NOT NULL,
   dateEffetContrat DATE NOT NULL,
   dateExpirationContrat DATE NOT NULL,
   reduction DECIMAL(2,2),
   coupoliceId VARCHAR(2) NOT NULL,
   clientId INT NOT NULL,
   apporteurId INT,
   agenceId_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 VEHIC_GARANT(
   vehiculeId INT,
   garantieId INT,
   PRIMARY KEY(vehiculeId, garantieId),
   FOREIGN KEY(vehiculeId) REFERENCES VEHICULE(vehiculeId),
   FOREIGN KEY(garantieId) REFERENCES GARANTIE(garantieId)
);

CREATE TABLE CONT_CATCOMP(
   agenceId INT,
   contratId INT,
   categorieId INT,
   catcomplementId INT,
   TYPECONTRAT VARCHAR(15) NOT NULL,
   PRIMARY KEY(agenceId, contratId, categorieId, catcomplementId),
   UNIQUE(TYPECONTRAT),
   FOREIGN KEY(agenceId, contratId) REFERENCES CONTRAT(agenceId, contratId),
   FOREIGN KEY(categorieId, catcomplementId) REFERENCES CATCOMPLEMENT(categorieId, catcomplementId)
);

[escartefigue]

Bonjour Zidane7

Bonsoir Messieurs,

Si vous devez intégrer les privilèges des différents intervenants, alors vous pouvez modéliser quelque chose comme
AGENT 1,1 --- associer --- 0,n PROFIL_METIER 1,n --- autoriser --- 0,n FONCTION

Pouvez-vous me donner un exemple de schéma?

La représentation texte ci-dessus revient à la représentation graphique ci-dessous :

Pièce jointe 588749

[Zidane7]

Bonsoir Monsieur escartefigue?
Selon votre schéma, il n'y aura plus de relation entre l'Agent et le Contrat ou bien comment créer cette relation?
Car il ya un agent producteur qui produit les contrats dans les agences et une ou plusieurs caissière(s) selon que ça soit une agence principale ou secondaire.
Merci d'avance.

[fsmrel]

Bonsoir Zidane7,

Effectivement, il n'y a que deux types de contrat à savoir Mono et Flotte. Si on met le type de contrat comme booléen, en ce moment on peut mettre dans l'entité contrat?

Oui. Puisqu’un contrat est d’un type et un seul et qu’il n’y a que deux types de contrats, vous pouvez définir pour cela un attribut de type booléen dans l’entité-type CONTRAT.

[fsmrel]

Ici je suis partie par la méthode de cascade en passant par le souscripteur ou assuré on pouvait retrouver le véhicule assuré raison pour laquelle j'avais jugé nécessaire d'enlever la relation entre le contrat et le véhicule. Quel est votre point de vue?

Pour retrouver la relation entre le véhicule et le contrat, via la personne qui a souscrit ou qui est assurée, cela implique (a) que le véhicule détermine (fasse référence à au moins et au plus) une personne, (b) et qu’à son tour la personne détermine le contrat. J’ai des doutes concernant le point (b). Merci d’argumenter, d’autant que les assurés et souscripteurs ne sont pas pris en compte dans le MCD.

[fsmrel]

Pour chaque agence secondaire, on donne accès à chaque utilisateur pour savoir qui à fait quoi en cas d'encaissement?

Voulez-vous dire que dans chaque agence secondaire, chaque utilisateur peut effectuer des encaissements ?

[Zidane7]

Bonsoir Monsieur fsmrel,

Bonsoir Zidane7,
Envoyé par Zidane7
Effectivement, il n'y a que deux types de contrat à savoir Mono et Flotte. Si on met le type de contrat comme booléen, en ce moment on peut mettre dans l'entité contrat?

Oui. Puisqu’un contrat est d’un type et un seul et qu’il n’y a que deux types de contrats, vous pouvez définir pour cela un attribut de type booléen dans l’entité-type CONTRAT.

Merci pour la précision. Vous avez répondu à l'une de mes questions avant ma réponse.

NumeroAgenceCodecategorieAnneeNumeropolice(incrementé). A chaque an, on change l'année et le numéro de police commence par un(1) et augustement (ou incremente) progressivement jusqu'à la fin d'année (31/12/....) Exemple: 2105102100001 (Siège, 210=Code agence, 510=code catégorie, 21=Année, 00001=premier numéro de police du siège par exemple). Et si c'est une flotte, nous remplaçons le code catégorie par code flotte.

A ce niveau, je voudrais vous dire que le code contrat est la concaténation du code Agence, du code Catégorie, de l'Année et du numéro de police qui s’incrémente par exemple entre (00001 et 09999) par an et reprend la même numérotation chaque année. Pour une telle situation que faire?
A mon avis, selon le message de Monsieur fsmrel, 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. Dans cette association, mettre l'année dans l'entité contrat.

Envoyé par Zidane7
Pour chaque agence secondaire, on donne accès à chaque utilisateur pour savoir qui à fait quoi en cas d'encaissement?

Voulez-vous dire que dans chaque agence secondaire, chaque utilisateur peut effectuer des encaissements ?

Oui, dans chaque agence secondaire chaque utilisateur peut effectuer des encaissements.
Voici le shémat du MCD ET 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
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 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 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,
   marqueId INT NOT NULL,
   puissanceId INT NOT NULL,
   categorieId INT NOT NULL,
   catcomplementId INT NOT NULL,
   clientId INT NOT NULL,
   PRIMARY KEY(vehiculeId),
   UNIQUE(codevehicule),
   FOREIGN KEY(marqueId) REFERENCES MARQUE(marqueId),
   FOREIGN KEY(puissanceId) REFERENCES PUISSANCE(puissanceId),
   FOREIGN KEY(categorieId, catcomplementId) REFERENCES CATCOMPLEMENT(categorieId, catcomplementId),
   FOREIGN KEY(clientId) REFERENCES CLIENT(clientId)
);

CREATE TABLE CONTRAT(
   agenceId INT,
   contratId INT IDENTITY,
   codecontrat VARCHAR(10) NOT NULL,
   typecontrat BIT NOT NULL,
   dateEffetContrat DATE NOT NULL,
   AN INT 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(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 VEHIC_GARANT(
   vehiculeId INT,
   garantieId INT,
   PRIMARY KEY(vehiculeId, garantieId),
   FOREIGN KEY(vehiculeId) REFERENCES VEHICULE(vehiculeId),
   FOREIGN KEY(garantieId) REFERENCES GARANTIE(garantieId)
);

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

Quelle sont vos remarques et suggestions?
Merci d'avance.

[Zidane7]

Bonjour Messieurs,
Pour la gestion de l'année, je l'ai fait comme suit:
J'ai crée une entité ANNEE dont le type est date qui détermine relativement le contrat et une entité ANNEE_HISTO qui donne l'historique des années. Mais à ce niveau, mon problème est comment rendre le type date à An uniquement?
Voici le shéma du MCD et du 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
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 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,
   marqueId INT NOT NULL,
   puissanceId INT NOT NULL,
   categorieId INT NOT NULL,
   catcomplementId INT NOT NULL,
   clientId INT NOT NULL,
   PRIMARY KEY(vehiculeId),
   UNIQUE(codevehicule),
   FOREIGN KEY(marqueId) REFERENCES MARQUE(marqueId),
   FOREIGN KEY(puissanceId) REFERENCES PUISSANCE(puissanceId),
   FOREIGN KEY(categorieId, catcomplementId) REFERENCES CATCOMPLEMENT(categorieId, catcomplementId),
   FOREIGN KEY(clientId) REFERENCES CLIENT(clientId)
);

CREATE TABLE CONTRAT(
   AN DATE,
   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, agenceId, contratId),
   UNIQUE(codecontrat),
   FOREIGN KEY(AN) REFERENCES ANNEE(AN),
   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,
   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, agenceId, contratId, avenantId),
   UNIQUE(codeavenant),
   FOREIGN KEY(AN, agenceId, contratId) REFERENCES CONTRAT(AN, 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,
   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, agenceId, contratId, encaisseCtrId),
   FOREIGN KEY(AN, agenceId, contratId) REFERENCES CONTRAT(AN, agenceId, contratId),
   FOREIGN KEY(modepaiemenId) REFERENCES MODEPAIEMENT(modepaiemenId),
   FOREIGN KEY(agenceId_1, caissiereId) REFERENCES CAISSIERE(agenceId_1, caissiereId)
);

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 CONTRAT_CATEGORIE(
   categorieId INT,
   AN DATE,
   agenceId INT,
   contratId INT,
   PRIMARY KEY(categorieId, AN, agenceId, contratId),
   FOREIGN KEY(categorieId) REFERENCES CATEGORIE(categorieId),
   FOREIGN KEY(AN, agenceId, contratId) REFERENCES CONTRAT(AN, agenceId, contratId)
);

Quelles sont vos remarques et suggestions sur cette partie?
Merci d'avance.

[escartefigue]

Bonsoir Monsieur escartefigue?

Selon votre schéma, il n'y aura plus de relation entre l'Agent et le Contrat ou bien comment créer cette relation?
Car il ya un agent producteur qui produit les contrats dans les agences et une ou plusieurs caissière(s) selon que ça soit une agence principale ou secondaire.
Merci d'avance.

Bonjour Zidane7

C'est que je n'ai modélisé que la partie qui concerne la gestion des autorisations, cette partie est donc à inclure, si besoin, dans votre propre MCD.
Et donc le lien entre agent et contrat est inchangé.

[fsmrel]

Bonjour Zidane7,

On parle de tout en même temps, ce qui ne simplifie pas la tâche. Je souhaite que l’on se concentre un moment sur la codification des contrats et que l’on mette en suspens les autres (nombreux) sujets.

Votre entité-type CONTRAT est dotée d’un attribut codeContrat qui est identifiant alternatif, c’est-à-dire qu’il est interdit que deux contrats aient la même valeur pour cet attribut. So far, so good. Maintenant je vous cite :

chaque contrat est codifié de la manière suivante: NumeroAgenceCodecategorieAnneeNumeropolice(incrementé). A chaque an, on change l'année et le numéro de police commence par un(1) et augustement (ou incremente) progressivement jusqu'à la fin d'année (31/12/....) Exemple: 2105102100001 (Siège, 210=Code agence, 510=code catégorie, 21=Année, 00001=premier numéro de police du siège par exemple).

Si l’exemple que vous donnez, "2105102100001" est bien une valeur de l’attribut codeContrat, en toute logique cette valeur n’a pas à changer au fil des ans (en tout cas, heureusement pour moi ! mon code contrat chez mon propre assureur n’a pas changé depuis 50 ans !). Mais, dans la citation, je relève que vous écrivez « A chaque an, on change l'année », cela signifie-t-il que le contrat qui en 2021 a la valeur "2105102100001", avait la valeur "2105102000001" en 2020, la valeur "2105101900001" en 2019, etc. ? Ou bien le nouvel attribut AN (présent dans la table CONTRAT, hérité de la nouvelle table ANNEE) joue-t-il un rôle bien particulier, différent de celui qui semble se manifester dans l’espèce d’historisation qui transparaît ?

A cet endroit, si je lie le contrat au vehicule et c'est le code contrat qui es clé étrangère dans l'entité véhicule, que faire en ce moment par rapport au code contrat si on veut le rendre automatique?

Qu’est-ce qui doit être rendu automatique ? Par rapport à ma remarque précédente, voulez-vous dire que, du fait d’un changement d’année dans le code de chaque contrat, chaque véhicule « changerait de contrat » tous les ans ?

Tout cela me rend perplexe.

[fsmrel]

Toujours concernant l’entité-type CONTRAT.

Dans le post #123, le contrat est identifié relativement à AGENCE, ce qui n’était pas le cas précédemment. Merci de prévenir quand vous procédez à un tel changement et d’expliquer le but de la manoeuvre. En l’occurrence, pourquoi ce changement ?

En passant, quand vous faites une citation, utilisez la balise QUOTE avec référence au message et à son auteur, sinon on est vite perdu. Faites précéder votre citation de deux lignes vierges, afin de la séparer de ce qui précède.



______________________

[fsmrel]

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

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.

A mon avis, selon le message de Monsieur fsmrel, 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. Dans cette association, mettre l'année dans l'entité contrat.

Lequel de mes messages est concerné ?

Vous mettez le type de flotte dans l'entité-type CONTRAT : j’ai du mal à saisir ce que cela veut dire. (a) S’il existe différents types de flottes, voyez ma remarque précédente concernant l’existence d’une entité-type FLOTTE. (b) Sinon, s’agit-il seulement d’avoir une valeur "flotte" pour l’attribut typeContrat de l’entité-type CONTRAT ?
Pour éclairer tout cela, merci de donner des exemples de contrats avec des flottes.

Dans cette association, mettre l'année dans l'entité contrat.

Parlez-vous de l’association CONTRAT_CATEGORIE entre CONTRAT et CATEGORIE ? A cette occasion je répète la question que j’ai posée dans le post #136 : pour quel motif doter la table CONTRAT d’un attribut AN ?

[Zidane7]

Bonjour Messieurs,
Merci pour vos différentes réponses.

fsmrel
Bonjour Zidane7,

On parle de tout en même temps, ce qui ne simplifie pas la tâche. Je souhaite que l’on se concentre un moment sur la codification des contrats et que l’on mette en suspens les autres (nombreux) sujets.

Vous avez raison. Faisons comme vous avez dit. Merci.

Votre entité-type CONTRAT est dotée d’un attribut codeContrat qui est identifiant alternatif, c’est-à-dire qu’il est interdit que deux contrats aient la même valeur pour cet attribut. So far, so good. Maintenant je vous cite :

Normal.

Zidane7
chaque contrat est codifié de la manière suivante: NumeroAgenceCodecategorieAnneeNumeropolice(incrementé). A chaque an, on change l'année et le numéro de police commence par un(1) et augustement (ou incremente) progressivement jusqu'à la fin d'année (31/12/....) Exemple: 2105102100001 (Siège, 210=Code agence, 510=code catégorie, 21=Année, 00001=premier numéro de police du siège par exemple).

fsmrel
Si l’exemple que vous donnez, "2105102100001" est bien une valeur de l’attribut codeContrat, en toute logique cette valeur n’a pas à changer au fil des ans (en tout cas, heureusement pour moi ! mon code contrat chez mon propre assureur n’a pas changé depuis 50 ans !). Mais, dans la citation, je relève que vous écrivez « A chaque an, on change l'année », cela signifie-t-il que le contrat qui en 2021 a la valeur "2105102100001", avait la valeur "2105102000001" en 2020, la valeur "2105101900001" en 2019, etc. ? Ou bien le nouvel attribut AN (présent dans la table CONTRAT, hérité de la nouvelle table ANNEE) joue-t-il un rôle bien particulier, différent de celui qui semble se manifester dans l’espèce d’historisation qui transparaît ?

Je veux vous dire ici que tous les nouveaux contrats de l'an N par exemple 2010 seront connus comme des contrats de 2010, à l'an N+1 c'est à dire 2011, tous nouveaux seront identifier par rapport à l'an 2011. Aussi le numéro de police elle dans l'intervalle d'année c'est à dire par exemple entre 01/01/2020 et 31/12/2020, nous aurons par exemple 00001,00002,00010,09999 et à l'année suivante nous reprenons par exemple de 01/01/2021 au 31/12/2021 à 00001, 00002, 00003 etc...
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.
Il me semble que c'est un modèle qui ressemble à l'entité puissance, mais la différence est que à chaque an 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 .
Que pensez-vous?

Zidane7
A cet endroit, si je lie le contrat au vehicule et c'est le code contrat qui es clé étrangère dans l'entité véhicule, que faire en ce moment par rapport au code contrat si on veut le rendre automatique?

fsmrel
Qu’est-ce qui doit être rendu automatique ? Par rapport à ma remarque précédente, voulez-vous dire que, du fait d’un changement d’année dans le code de chaque contrat, chaque véhicule « changerait de contrat » tous les ans ?
Tout cela me rend perplexe.

Non.
Si maintenait par exemple la relation entre le contrat et le véhicule et que le code contrat n'est pas bien ficelé alors on se retrouverait avec un code comme suit: 00001 dans l'entité véhicule et c'est ce qui n'est pas normal. C'est dans ce contexte là que je l'ai dit.
Avez-vous bien compris ce passage ou vous avez d'autres remarque à ce sujet?

fsmrel
Toujours concernant l’entité-type CONTRAT.
Dans le post #123, le contrat est identifié relativement à AGENCE, ce qui n’était pas le cas précédemment. Merci de prévenir quand vous procédez à un tel changement et d’expliquer le but de la manoeuvre. En l’occurrence, pourquoi ce changement ?

A ce niveau, le contrat est toujours indentifié relativement à l'AGENCE. Je vous montre le MCD en question.

fsmrel
En passant, quand vous faites une citation, utilisez la balise QUOTE avec référence au message et à son auteur, sinon on est vite perdu. Faites précéder votre citation de deux lignes vierges, afin de la séparer de ce qui précède.

Merci pour la remarque.

Envoyé par Zidane7
Et si c'est une flotte, nous remplaçons le code catégorie par code flotte.
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.

Merci, donc comme il n'y a que deux types de contrat à savoir Mono et Flotte, vous voulez que je sépare les types de contrat de la sorte: une entité MONO et une entité FLOTTE ou comment voulez-vous que le schéma soit?
Dans une flotte, il peut y avoir plusieurs sous-catégories pour un contrat bien déterminé et peut varier d'un contrat à un autre. Le code flotte ne varie pas et ensuite il y a plusieurs type de flotte.

Envoyé par Zidane7
A mon avis, selon le message de Monsieur fsmrel, 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. Dans cette association, mettre l'année dans l'entité contrat.
Lequel de mes messages est concerné ?

Ici je parlais de votre poste #130 mais je trouve que cela est anormal.
Que faire?

fsmrel
Vous mettez le type de flotte dans l'entité-type CONTRAT : j’ai du mal à saisir ce que cela veut dire. (a) S’il existe différents types de flottes, voyez ma remarque précédente concernant l’existence d’une entité-type FLOTTE. (b) Sinon, s’agit-il seulement d’avoir une valeur "flotte" pour l’attribut typeContrat de l’entité-type CONTRAT ?
Pour éclairer tout cela, merci de donner des exemples de contrats avec des flottes.

Comme je l'ai repondu dans le message ci-dessus, je vous donne un exmple de flotte.
Code=591, ici on peut avoir un mélange de catégorie telle que: les motos (véhicule à deux roues code catégorie =550), VP(véhicule personnel, code catégorie =510) et ainsi de suite. Il y a aussi la flotte 597 pour la catégorie moto si on veut uniquement faire la souscription ou assuré simplement les motos ensemble. D'autres flottes existent tel que les TPV 598.

Envoyé par Zidane7
Dans cette association, mettre l'année dans l'entité contrat.
Parlez-vous de l’association CONTRAT_CATEGORIE entre CONTRAT et CATEGORIE ? A cette occasion je répète la question que j’ai posée dans le post #136 : pour quel motif doter la table CONTRAT d’un attribut AN ?

A ce sujet, regardez ma réponse ci-dessus.

escartefigue
Bonjour Zidane7

C'est que je n'ai modélisé que la partie qui concerne la gestion des autorisations, cette partie est donc à inclure, si besoin, dans votre propre MCD.
Et donc le lien entre agent et contrat est inchangé.

Merci pour votre réponse mais comme Monsieur fsmrel a dit dans son poste #136, essayons de régler le cas du contrat avant d'attaquer autres sujets.

Avez-vous d'autres remarques et suggestions?
Voici la reproduction du code sql et le MCD 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
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 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,
   marqueId INT NOT NULL,
   puissanceId INT NOT NULL,
   categorieId INT NOT NULL,
   catcomplementId INT NOT NULL,
   clientId INT NOT NULL,
   PRIMARY KEY(vehiculeId),
   UNIQUE(codevehicule),
   FOREIGN KEY(marqueId) REFERENCES MARQUE(marqueId),
   FOREIGN KEY(puissanceId) REFERENCES PUISSANCE(puissanceId),
   FOREIGN KEY(categorieId, catcomplementId) REFERENCES CATCOMPLEMENT(categorieId, catcomplementId),
   FOREIGN KEY(clientId) REFERENCES CLIENT(clientId)
);

CREATE TABLE CONTRAT(
   AN DATE,
   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, agenceId, contratId),
   UNIQUE(codecontrat),
   FOREIGN KEY(AN) REFERENCES ANNEE(AN),
   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,
   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, agenceId, contratId, avenantId),
   UNIQUE(codeavenant),
   FOREIGN KEY(AN, agenceId, contratId) REFERENCES CONTRAT(AN, 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,
   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, agenceId, contratId, encaisseCtrId),
   FOREIGN KEY(AN, agenceId, contratId) REFERENCES CONTRAT(AN, agenceId, contratId),
   FOREIGN KEY(modepaiemenId) REFERENCES MODEPAIEMENT(modepaiemenId),
   FOREIGN KEY(agenceId_1, caissiereId) REFERENCES CAISSIERE(agenceId_1, caissiereId)
);

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 CONTRAT_CATEGORIE(
   categorieId INT,
   AN DATE,
   agenceId INT,
   contratId INT,
   PRIMARY KEY(categorieId, AN, agenceId, contratId),
   FOREIGN KEY(categorieId) REFERENCES CATEGORIE(categorieId),
   FOREIGN KEY(AN, agenceId, contratId) REFERENCES CONTRAT(AN, agenceId, contratId)
);

Merci d'avance.

[fsmrel]

Bonsoir Zidane7,


Caveat. Tout ce qui suit concerne implicitement les contrats de type "mono". Je répondrai plus tard en ce qui concerne les flottes.

Je veux vous dire ici que tous les nouveaux contrats de l'an N par exemple 2010 seront connus comme des contrats de 2010, à l'an N+1 c'est à dire 2011, tous nouveaux seront identifier par rapport à l'an 2011.

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 ?

Concernant le numéro de police :

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 ?

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

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

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.

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 !
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...)

Une fois construit le code contrat, à l’occasion de l’INSERT, au moyen d’un trigger on pourra effectuer automatiquement les contrôles d’usage concernant ce code, par exemple :

S’assurer que le code agence qu’il contient est bien celui qui est déterminé par l’association CONTRAT_AGENCE ;

Que la catégorie (et le complément) sont valides.

Quelle contre-indication verriez-vous à l’utilisation d’un attribut (de type DATE) pour l’entité-type CONTRAT, tel que contratDateCreation (ou contratDateEffet si cet attribut suffit) ?

A cet endroit, si je lie le contrat au vehécule et c'est le code contrat qui es clé étrangère dans l'entité véhicule, que faire en ce moment par rapport au code contrat si on veut le rendre automatique?

Qu’est-ce qui doit être rendu automatique ? Par rapport à ma remarque précédente, voulez-vous dire que, du fait d’un changement d’année dans le code de chaque contrat, chaque véhicule « changerait de contrat » tous les ans ?
Tout cela me rend perplexe.

Non.
Si maintenait par exemple la relation entre le contrat et le véhicule et que le code contrat n'est pas bien ficelé alors on se retrouverait avec un code comme suit: 00001 dans l'entité véhicule et c'est ce qui n'est pas normal. C'est dans ce contexte là que je l'ai dit.
Avez-vous bien compris ce passage ou vous avez d'autres remarque à ce sujet?

Quel attribut de l’entité-type VEHICULE contiendrait la valeur "00001" ? L’attribut codeVehicule ? Sinon quel autre ? En relation avec un numéro de police ? Tout cela reste flou, à expliquer de façon détaillée, claire et précise, à l’aide d’exemples concrets.

A ce niveau, le contrat est toujours indentifié relativement à l'AGENCE. Je vous montre le MCD en question.

Bien sûr, le MCD le montre, mais j’ai découvert cela au hasard d’une recherche concernant un autre sujet. Pour que je n’ai pas à chaque fois à comparer les versions de MCD et relire complètement le code SQL, opérations qui consomment des heures, ça serait bien que vous signaliez systématiquement les modifications que vous apportez.


A propos des contrats impliquant des remorques. Ces contrats sont du type "mono" ?

Pour les flottes, comme annoncé en début de message, je vais essayer de faire le point.