Bonsoir Zidane7,
Je rappelle ce que j’ai signalé dans le post #76 mais vous avez oublié de faire la modification en conséquence :
CATEGORIE n’est pas à identifier relativement à CEDEAO (elle n’en est pas la propriété), il faut remplacer 1,1(R) par 1,1 sur la patte connectant CATEGORIE et CAT_CEDEAO :
=>
[CEDEAO]--1,N----(CAT_CEDEAO)---- 1,1--[CATEGORIE]
Je rappelle aussi ce que j’ai déjà signalé dans le post #76 :
Les cardinalités portées par les pattes de l’association connectant les entités-types CEDEAO et CEDAO_HISTO sont à permuter et deviennent :
[CEDEAO]--0,N----( )----1,1(R)--[CEDAO_HISTO]
Sinon, si vous persistez dans votre choix de modélisation, cela revient à dire que la CEDEAO active aujourd’hui dépend d’une CEDEAO historisée ce qui n’a strictement aucun sens, et qu’à une CEDEAO historisée peuvent correspondre plusieurs CEDEAO actives ce qui là encore n’a vraiment aucun sens.
Au stade SQL (les CREATE TABLE), regardez de près les conséquences de votre choix quant à la structure de votre table CEDEAO, laquelle est donc dotée d’une clé étrangère faisant référence à la table CEDAO_HISTO et confirmant que la CEDEAO active doit faire référence à une CEDEAO historisée : jamais un tel montage ne pourra fonctionner !
Question 1 : au vu de tout cela, quel est votre degré de connaissance du langage SQL ? (Non seulement en ce qui concerne la structure des tables, mais aussi leur manipulation, à savoir SELECT, INSERT, etc., sans oublier les triggers).
Retour des sous-catégories
Dans le post #71, vous aviez écrit :
En conséquence, dans le post #75 vous associez l’entité-type TARIF aux trois entités-types CATEGORIE, CAT_COMPLEMENT et PUISSANCE, ce qui est un choix tout à fait pertinent.
Dans le post #80, vous supprimez cette fois-ci le lien entre les entités-types TARIF et CATEGORIE, et pour pallier, vous créez un lien entre les entités-types CATEGORIE et CAT_COMPLEMENT, en identifiant la seconde relativement à la première :
Votre nouvelle modélisation (qui est correcte elle aussi) est en fait analogue à celle que j’avais proposée dans le post #55 où l’entité-type TARIF est nommée PFC et CAT_COMPLEMENT est nommée SOUS_CATEGORIE.
Question 2 : pour quelle raison êtes-vous revenu à cette modélisation alors que dans le post #71 vous aviez jugé nécessaire de supprimer l'entité SOUS-CATEGORIE ?
A propos des véhicules
Vous établissez une association VEHICULE_PUIS entre les entités-types VEHICULE et PUISSANCE. Au vu du MCD ceci est légitime puisque vous avez supprimé le lien entre les entités-types VEHICULE et TARIF, lien permettant via TARIF d’associer transitivement VEHICULE et PUISSANCE (voir votre post #75). Mais comme l’entité-type CAT_COMPLEMENT est maintenant identifiée relativement à l’entité-type CATEGORIE, l’entité-type VEHICULE contient les attributs catId, catComplementId outre l’attribut puissanceId, et grâce à ces trois attributs, on sait déterminer le tarif pour chaque véhicule : ouf !
Les remorques
Vous avez isolé les remorques en mettant en oeuvre une entité-type REMORQUE qui est en fait une spécialisation de l’entité-type CATEGORIE, d’accord sur le principe, mais il y a quand même un problème : chaque occurrence de l’entité-type CATEGORIE fait référence à une occurrence de l’entité-type CEDEAO, ce qui vaut donc pour les remorques . Etant donné que l’entité-type REMORQUE contient un attribut cedeaoRemorque, d’une part il y a redondance et d’autre part se pose le problème de la mise en relation des remorques et de l’historique des CEDEAO.
Question 3 : comment vous positionnez-vous au sujet des remorques quant à la redondance et à l’historique des CEDEAO ?
J’ai d’autres remarques à faire (notamment la défense recours), mais pour le moment j’attendrai déjà que vous répondiez à mes questions ci-dessus.
En attendant, pour les tables dont la structure a changé (CAT_COMPLEMENT, TARIF, VEHICULE) le code SQL devient :
=>CREATE TABLE CAT_COMPLEMENT ( catId INT, catComplementId INT IDENTITY, catComplementCode TINYINT NOT NULL, catComplementLibelle VARCHAR(96) NOT NULL, CONSTRAINT CAT_COMPLEMENT_PK PRIMARY KEY(catId, catComplementId), CONSTRAINT CAT_COMPLEMENT_AK UNIQUE(catComplementCode), CONSTRAINT CAT_COMPLEMENT_CATEGORIE_FK FOREIGN KEY (catId) REFERENCES CATEGORIE (catId) ); INSERT INTO CAT_COMPLEMENT (catId, catComplementCode, catComplementLibelle) VALUES ((SELECT catId FROM CATEGORIE WHERE catCode = '1') , 11, '') , ((SELECT catId FROM CATEGORIE WHERE catCode = '2') , 21, '') , ((SELECT catId FROM CATEGORIE WHERE catCode = '3') , 31, 'Marchandises (tonnage)') , ((SELECT catId FROM CATEGORIE WHERE catCode = '3') , 32, 'Remorque') , ((SELECT catId FROM CATEGORIE WHERE catCode = '4') , 41, 'Taxis urbains et inter-urbains - Taxis 4 places') , ((SELECT catId FROM CATEGORIE WHERE catCode = '4') , 42, 'Taxis urbains et inter-urbains - Taxis 5 places') , ((SELECT catId FROM CATEGORIE WHERE catCode = '4') , 43, 'Taxis urbains et inter-urbains - Taxis 6 places') , ((SELECT catId FROM CATEGORIE WHERE catCode = '4') , 48, 'Transport public de voyageurs - TPV 18 places') , ((SELECT catId FROM CATEGORIE WHERE catCode = '5') , 51, '') ; SELECT catCode, catLibelle , catComplementCode, catComplementLibelle FROM CATEGORIE as x JOIN CAT_COMPLEMENT as y ON x.catId = y.catId ;
Les tarifscatCode catLibelle catComplCode catComplementLibelle 1 Catégorie 1 : Affaires et promenades (AP) 11 2 Catégorie 2 : Transport pour le compte de l'assuré (TPC) 21 3 Catégorie 3 : Transport public de marchandises (TPM) 31 Marchandises (tonnage) 3 Catégorie 3 : Transport public de marchandises (TPM) 32 Remorque 4 Catégorie 4 : 41 Taxis urbains et inter-urbains - Taxis 4 places 4 Catégorie 4 : 42 Taxis urbains et inter-urbains - Taxis 5 places 4 Catégorie 4 : 43 Taxis urbains et inter-urbains - Taxis 6 places 4 Catégorie 4 : 48 Transport public de voyageurs - TPV 18 places 5 Catégorie 5 : Motos 51
Résultat identique à celui du post #79 :CREATE TABLE TARIF ( catId INT, puissanceId INT, catComplementId INT, tarifDateDepuis DATE NOT NULL, respCivile INT NOT NULL, CONSTRAINT TARIF_PK PRIMARY KEY(catId, puissanceId, catComplementId), CONSTRAINT TARIF_PUISSANCE_FK FOREIGN KEY(puissanceId) REFERENCES PUISSANCE(puissanceId), CONSTRAINT TARIF_CAT_COMPLEMENT_FK FOREIGN KEY(catId, catComplementId) REFERENCES CAT_COMPLEMENT(catId, catComplementId) ); -- TARIF, cas de la catégorie 1 INSERT INTO TARIF (puissanceId, catId, catComplementId, tarifDateDepuis, respCivile) VALUES ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C1') , (SELECT catId FROM CATEGORIE WHERE catCode = '1') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 11) , '2020-10-01', 201657) , ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2') , (SELECT catId FROM CATEGORIE WHERE catCode = '1') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 11) , '2020-10-01', 244399) , ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3') , (SELECT catId FROM CATEGORIE WHERE catCode = '1') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 11) , '2020-10-01', 285114) , ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4') , (SELECT catId FROM CATEGORIE WHERE catCode = '1') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 11) , '2020-10-01', 346213) , ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C5') , (SELECT catId FROM CATEGORIE WHERE catCode = '1') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 11) , '2020-10-01', 407332) ; -- TARIF, cas de la catégorie 2 INSERT INTO TARIF (puissanceId, catId, catComplementId, tarifDateDepuis, respCivile) VALUES ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2') , (SELECT catId FROM CATEGORIE WHERE catCode = '2') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 21) -------------------------- , '2020-10-01', 401383) , '1900-01-01', 401383) , ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3') , (SELECT catId FROM CATEGORIE WHERE catCode = '2') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 21) ------------------ , '2020-10-01', 563124) , '1900-01-01', 563124) , ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4') , (SELECT catId FROM CATEGORIE WHERE catCode = '2') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 21) --------------- , '2020-10-01', 583830) , '1900-01-01', 583830) ; -- Cas de la catégorie 3 iNSERT INTO TARIF (puissanceId, catId, catComplementId, tarifDateDepuis, respCivile) VALUES ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'T1') , (SELECT catId FROM CATEGORIE WHERE catCode = '3') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 31) --------------- , '2020-10-01', 578610) , '1900-01-01', 578610) , ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'T2') , (SELECT catId FROM CATEGORIE WHERE catCode = '3') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 31) ----------------- , '2020-10-01', 694332) , '1900-01-01', 694332) , ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'R1') , (SELECT catId FROM CATEGORIE WHERE catCode = '3') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 32) ----------------- , '2020-10-01', 1000000) , '1900-01-01', 1000000) ; -- Cas de la catégorie 4 INSERT INTO TARIF (puissanceId, catId, catComplementId, tarifDateDepuis, respCivile) VALUES ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2') , (SELECT catId FROM CATEGORIE WHERE catCode = '4') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 41) , '2020-10-04', 690102) , ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3') , (SELECT catId FROM CATEGORIE WHERE catCode = '4') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 41) ------------- , '2020-10-04', 728486) , '1900-01-01', 728486) , ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4') , (SELECT catId FROM CATEGORIE WHERE catCode = '4') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 41) --------- , '2020-10-04', 806813) , '1900-01-01', 806813) , ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C5') , (SELECT catId FROM CATEGORIE WHERE catCode = '4') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 41) ------------- , '2020-10-04', 846877) , '1900-01-01', 846877) , ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2') , (SELECT catId FROM CATEGORIE WHERE catCode = '4') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 42) ----------------- , '2020-10-04', 784239) , '1900-01-01', 784239) , ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3') , (SELECT catId FROM CATEGORIE WHERE catCode = '4') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 42) ---------- , '2020-10-04', 822563) , '1900-01-01', 822563) , ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4') , (SELECT catId FROM CATEGORIE WHERE catCode = '4') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 42) ---------- , '2020-10-04', 900947) , '1900-01-01', 900947) , ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C5') , (SELECT catId FROM CATEGORIE WHERE catCode = '4') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 42) ------------ , '2020-10-04', 941015) , '1900-01-01', 941015) , ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2') , (SELECT catId FROM CATEGORIE WHERE catCode = '4') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 48) ----------- , '2020-10-04', 1097723) , '1900-01-01', 1097723) , ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3') , (SELECT catId FROM CATEGORIE WHERE catCode = '4') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 48) -------------- , '2020-10-04', 1115468) , '1900-01-01', 1115468) ; -- Cas de la catégorie 5 INSERT INTO TARIF (puissanceId, catId, catComplementId, tarifDateDepuis, respCivile) VALUES ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2') , (SELECT catId FROM CATEGORIE WHERE catCode = '5') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 51) , '2020-12-05', 75000) , ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3') , (SELECT catId FROM CATEGORIE WHERE catCode = '5') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 51) , '2019-02-02', 80000) , ((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4') , (SELECT catId FROM CATEGORIE WHERE catCode = '5') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 51) -------------- , '2019-05-02', 85000) , '1900-01-01', 85000) ; -------------------------------------------------------------- -- voyons voir -------------------------------------------------------------- SELECT puissanceCode as puissance, unite, borneInf as inf, borneSup as sup , LEFT(CONCAT(catLibelle , ' ', catComplementLibelle),76) as cat , tarifDateDepuis as depuis , respCivile FROM TARIF AS x JOIN PUISSANCE AS y ON x.puissanceId = y.puissanceId JOIN CATEGORIE AS z ON x.catId = z.catId JOIN CAT_COMPLEMENT as t ON x.catId = t.catId AND x.catComplementId = t.catComplementId ORDER BY cat, puissance ;
Historique des tarifspuissance unite inf sup cat depuis respCivile C1 CV 0 2 Catégorie 1 : Affaires et promenades (AP) 2020-10-01 201657 C2 CV 3 6 Catégorie 1 : Affaires et promenades (AP) 2020-10-01 244399 C3 CV 7 10 Catégorie 1 : Affaires et promenades (AP) 2020-10-01 285114 C4 CV 11 14 Catégorie 1 : Affaires et promenades (AP) 2020-10-01 346213 C5 CV 15 23 Catégorie 1 : Affaires et promenades (AP) 2020-10-01 407332 C2 CV 3 6 Catégorie 2 : Transport pour le compte de l'assuré (TPC) 1900-01-01 401383 C3 CV 7 10 Catégorie 2 : Transport pour le compte de l'assuré (TPC) 1900-01-01 563124 C4 CV 11 14 Catégorie 2 : Transport pour le compte de l'assuré (TPC) 1900-01-01 583830 T1 tonne 0 1 Catégorie 3 : Transport public de marchandises (TPM) Marchandises 1900-01-01 578610 T2 tonne 2 5 Catégorie 3 : Transport public de marchandises (TPM) Marchandises 2020-10-01 694332 R1 remorque 0 0 Catégorie 3 : Transport public de marchandises (TPM) Remorque 1900-01-01 1000000 C2 CV 3 6 Catégorie 4 : Taxis urbains et inter-urbains - Taxis 4 places 2020-10-04 690102 C3 CV 7 10 Catégorie 4 : Taxis urbains et inter-urbains - Taxis 4 places 1900-01-01 728486 C4 CV 11 14 Catégorie 4 : Taxis urbains et inter-urbains - Taxis 4 places 1900-01-01 806813 C5 CV 15 23 Catégorie 4 : Taxis urbains et inter-urbains - Taxis 4 places 1900-01-01 846877 C2 CV 3 6 Catégorie 4 : Taxis urbains et inter-urbains - Taxis 5 places 1900-01-01 784239 C3 CV 7 10 Catégorie 4 : Taxis urbains et inter-urbains - Taxis 5 places 2020-10-04 822563 C4 CV 11 14 Catégorie 4 : Taxis urbains et inter-urbains - Taxis 5 places 2020-10-04 900947 C5 CV 15 23 Catégorie 4 : Taxis urbains et inter-urbains - Taxis 5 places 2020-10-04 941015 C2 CV 3 6 Catégorie 4 : Transport public de voyageurs - TPV 18 places 2020-10-04 1097723 C3 CV 7 10 Catégorie 4 : Transport public de voyageurs - TPV 18 places 2020-10-04 1115468 C2 CV 3 6 Catégorie 5 : Motos 2020-12-05 75000 C3 CV 7 10 Catégorie 5 : Motos 2019-02-02 80000 C4 CV 11 14 Catégorie 5 : Motos 1900-01-01 85000
Résultat identique à celui du post #79 :CREATE TABLE TARIF_HISTO ( catId INT, puissanceId INT, catComplementId INT, tarifDebutHisto DATE, tarifFinHisto DATE NOT NULL, responsabiliteCivileHisto INT NOT NULL, CONSTRAINT TARIF_HISTO_PK PRIMARY KEY(catId, puissanceId, catComplementId, tarifDebutHisto), CONSTRAINT TARIF_HISTO_AK UNIQUE(catId, puissanceId, catComplementId, tarifFinHisto), CONSTRAINT TARIF_HISTO_TARIF_FK FOREIGN KEY(catId, puissanceId, catComplementId) REFERENCES TARIF(catId, puissanceId, catComplementId) , CONSTRAINT TARIF_HISTO_DATES CHECK (tarifFinHisto > tarifDebutHisto) ); INSERT INTO TARIF_HISTO (catId, puissanceId, catComplementId, tarifDebutHisto, tarifFinHisto, responsabiliteCivileHisto) VALUES ((SELECT catId FROM CATEGORIE WHERE catCode = '1') , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C1') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 11) , '2015-01-01', '2015-12-31', 180000) , ((SELECT catId FROM CATEGORIE WHERE catCode = '1') , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C1') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 11) , '2016-01-01', '2017-10-31', 185000) , ((SELECT catId FROM CATEGORIE WHERE catCode = '1') , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C1') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 11) , '2017-11-01', '2018-10-03', 190000) , ((SELECT catId FROM CATEGORIE WHERE catCode = '1') , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C1') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 11) , '2018-10-04', '2019-08-03', 195000) , ((SELECT catId FROM CATEGORIE WHERE catCode = '1') , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C1') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 11) , '2019-08-04', '2020-09-30', 196000) , --------- C2 ((SELECT catId FROM CATEGORIE WHERE catCode = '1') , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 11) , '2015-01-01', '2015-12-31', 200000) , ((SELECT catId FROM CATEGORIE WHERE catCode = '1') , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 11) , '2016-01-01', '2017-12-31', 210000) , ((SELECT catId FROM CATEGORIE WHERE catCode = '1') , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 11) , '2018-01-01', '2020-09-30', 220000) , --------- C3 ((SELECT catId FROM CATEGORIE WHERE catCode = '1') , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 11) , '2015-01-01', '2015-12-31', 240000) , ((SELECT catId FROM CATEGORIE WHERE catCode = '1') , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 11) , '2016-01-01', '2017-12-31', 260000) , ((SELECT catId FROM CATEGORIE WHERE catCode = '1') , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 11) , '2018-01-01', '2020-09-30', 270000) , --------- C4 ((SELECT catId FROM CATEGORIE WHERE catCode = '1') , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 11) , '2015-01-01', '2015-12-31', 310000) , ((SELECT catId FROM CATEGORIE WHERE catCode = '1') , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 11) , '2016-01-01', '2017-12-31', 320000) , ((SELECT catId FROM CATEGORIE WHERE catCode = '1') , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 11) , '2018-01-01', '2020-09-30', 330000) , --------- C5 ((SELECT catId FROM CATEGORIE WHERE catCode = '1') , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C5') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 11) , '2015-01-01', '2015-12-31', 380000) , ((SELECT catId FROM CATEGORIE WHERE catCode = '1') , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C5') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 11) , '2016-01-01', '2017-12-31', 390000) , ((SELECT catId FROM CATEGORIE WHERE catCode = '1') , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C5') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 11) , '2018-01-01', '2018-12-31', 395000) , ((SELECT catId FROM CATEGORIE WHERE catCode = '1') , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C5') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 11) , '2019-01-01', '2020-09-30', 400000) ; INSERT INTO TARIF_HISTO (catId, puissanceId, catComplementId, tarifDebutHisto, tarifFinHisto, responsabiliteCivileHisto) VALUES ((SELECT catId FROM CATEGORIE WHERE catCode = '4') , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 41) , '2015-01-01', '2017-12-31', 650000) , ((SELECT catId FROM CATEGORIE WHERE catCode = '4') , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 41) , '2018-01-01', '2019-10-31', 670000) , ((SELECT catId FROM CATEGORIE WHERE catCode = '4') , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 41) , '2019-11-01', '2020-10-03', 690000) ; INSERT INTO TARIF_HISTO (catId, puissanceId, catComplementId, tarifDebutHisto, tarifFinHisto, responsabiliteCivileHisto) VALUES ((SELECT catId FROM CATEGORIE WHERE catCode = '5') , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 51) , '1900-01-01', '2020-12-04', 68000) , ((SELECT catId FROM CATEGORIE WHERE catCode = '5') , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 51) , '1900-01-01', '2019-02-01', 77000) ; -------------------------------------------------------------- -- voyons voir -------------------------------------------------------------- SELECT '' as TARIF_HISTO, puissanceCode as puissance, unite, borneInf as inf, borneSup as sup , LEFT(CONCAT(catLibelle , ' ', catComplementLibelle),76) as cat , tarifDebutHisto as debut, tarifFinHisto as fin , responsabiliteCivileHisto as respCiv FROM TARIF_HISTO AS x JOIN PUISSANCE AS y ON x.puissanceId = y.puissanceId JOIN CATEGORIE AS z ON x.catId = z.catId JOIN CAT_COMPLEMENT as t ON x.catComplementId = t.catComplementId ORDER BY cat, unite, borneInf, tarifDebutHisto ;
puissance unite inf sup cat début fin respCiv C1 CV 0 2 Catégorie 1 : Affaires et promenades (AP) 2015-01-01 2015-12-31 180000 C1 CV 0 2 Catégorie 1 : Affaires et promenades (AP) 2016-01-01 2017-10-31 185000 C1 CV 0 2 Catégorie 1 : Affaires et promenades (AP) 2017-11-01 2018-10-03 190000 C1 CV 0 2 Catégorie 1 : Affaires et promenades (AP) 2018-10-04 2019-08-03 195000 C1 CV 0 2 Catégorie 1 : Affaires et promenades (AP) 2019-08-04 2020-09-30 196000 C2 CV 3 6 Catégorie 1 : Affaires et promenades (AP) 2015-01-01 2015-12-31 200000 C2 CV 3 6 Catégorie 1 : Affaires et promenades (AP) 2016-01-01 2017-12-31 210000 C2 CV 3 6 Catégorie 1 : Affaires et promenades (AP) 2018-01-01 2020-09-30 220000 C3 CV 7 10 Catégorie 1 : Affaires et promenades (AP) 2015-01-01 2015-12-31 240000 C3 CV 7 10 Catégorie 1 : Affaires et promenades (AP) 2016-01-01 2017-12-31 260000 C3 CV 7 10 Catégorie 1 : Affaires et promenades (AP) 2018-01-01 2020-09-30 270000 C4 CV 11 14 Catégorie 1 : Affaires et promenades (AP) 2015-01-01 2015-12-31 310000 C4 CV 11 14 Catégorie 1 : Affaires et promenades (AP) 2016-01-01 2017-12-31 320000 C4 CV 11 14 Catégorie 1 : Affaires et promenades (AP) 2018-01-01 2020-09-30 330000 C5 CV 15 23 Catégorie 1 : Affaires et promenades (AP) 2015-01-01 2015-12-31 380000 C5 CV 15 23 Catégorie 1 : Affaires et promenades (AP) 2016-01-01 2017-12-31 390000 C5 CV 15 23 Catégorie 1 : Affaires et promenades (AP) 2018-01-01 2018-12-31 395000 C5 CV 15 23 Catégorie 1 : Affaires et promenades (AP) 2019-01-01 2020-09-30 400000 C2 CV 3 6 Catégorie 4 : Taxis urbains et inter-urbains - Taxis 4 places 2015-01-01 2017-12-31 650000 C2 CV 3 6 Catégorie 4 : Taxis urbains et inter-urbains - Taxis 4 places 2018-01-01 2019-10-31 670000 C2 CV 3 6 Catégorie 4 : Taxis urbains et inter-urbains - Taxis 4 places 2019-11-01 2020-10-03 690000 C2 CV 3 6 Catégorie 5 : Motos 1900-01-01 2020-12-04 68000 C3 CV 7 10 Catégorie 5 : Motos 1900-01-01 2019-02-01 77000
Vue générale des tarifs (depuis et durant)
CREATE VIEW TARIF_TOUT (puissanceId, puissanceCode, unite, borneInf, borneSup , catId, catCode, catComplementId, catComplCode, cat , dateDebut, dateFin, respCivile) AS SELECT x.puissanceId, puissanceCode, unite, borneInf, borneSup , x.catId, catCode, x.catComplementId, catComplementCode , LEFT(CONCAT(catLibelle , ' ', catComplementLibelle),76) as cat , tarifDateDepuis, '9999-12-31' , respCivile FROM TARIF AS x JOIN PUISSANCE AS y ON x.puissanceId = y.puissanceId JOIN CATEGORIE AS z ON x.catId = z.catId JOIN CAT_COMPLEMENT as t ON x.catId = t.catId AND x.catComplementId = t.catComplementId UNION SELECT x.puissanceId, puissanceCode, unite, borneInf, borneSup , x.catId, catCode, x.catComplementId, catComplementCode , LEFT(CONCAT(catLibelle , ' ', catComplementLibelle),76) as cat , tarifDebutHisto, tarifFinHisto , responsabiliteCivileHisto FROM TARIF_HISTO AS x JOIN PUISSANCE AS y ON x.puissanceId = y.puissanceId JOIN CATEGORIE AS z ON x.catId = z.catId JOIN CAT_COMPLEMENT as t ON x.catId = t.catId AND x.catComplementId = t.catComplementId ;
Les clients et les contrats : voir post #76.
Les véhicules :
=>CREATE TABLE VEHICULE ( vehiculeId INT IDENTITY, vehiculeCode CHAR(8) NOT NULL, vehiculeSerie CHAR(17) NOT NULL, vehiculePuissance INT NOT NULL, vehiculeMarque VARCHAR(24) NOT NULL, puissanceId INT NOT NULL, catId INT NOT NULL, catComplementId INT NOT NULL, contratId INT NOT NULL, CONSTRAINT VEHICULE_PK PRIMARY KEY(vehiculeId), CONSTRAINT VEHICULE_AK UNIQUE(vehiculeCode), CONSTRAINT VEHICULE_S_AK UNIQUE(vehiculeSerie), CONSTRAINT VEHICULE_PUISSANCE_FK FOREIGN KEY(puissanceId) REFERENCES PUISSANCE(puissanceId), CONSTRAINT VEHICULE_TARIF_FK FOREIGN KEY(catId, catComplementId) REFERENCES CAT_COMPLEMENT (catId, catComplementId), CONSTRAINT VEHICULE_CONTRAT_FK FOREIGN KEY(contratId) REFERENCES CONTRAT(contratId) ); INSERT INTO VEHICULE (vehiculeCode, vehiculeSerie, vehiculePuissance, vehiculeMarque , catId, puissanceId, catComplementId, contratId) VALUES ('veh001', 'FN345678901234567', 8, 'Ford' , (SELECT catId FROM CATEGORIE WHERE catCode = '4') , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode= 'C2') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 41) , (SELECT contratId FROM CONTRAT WHERE contratCode = 'ctr001')) , ('veh011', 'VM345678901234567', 15, 'Mercedes' , (SELECT catId FROM CATEGORIE WHERE catCode = '1') , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode= 'C5') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 11) , (SELECT contratId FROM CONTRAT WHERE contratCode = 'ctr021')) , ('veh012', 'VR345678901234567', 7, 'Renault' , (SELECT catId FROM CATEGORIE WHERE catCode = '1') , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode= 'C3') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 11) , (SELECT contratId FROM CONTRAT WHERE contratCode = 'ctr021')) , ('veh013', 'VR456789012345678', 8, 'Ford' , (SELECT catId FROM CATEGORIE WHERE catCode = '4') , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2') /* 8 CV => C3....*/ , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 41) , (SELECT contratId FROM CONTRAT WHERE contratCode = 'ctr022')) , ('veh014', 'VR567890123456789', 6, 'Ford' , (SELECT catId FROM CATEGORIE WHERE catCode = '4') , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 41) , (SELECT contratId FROM CONTRAT WHERE contratCode = 'ctr023')) , ('veh015', 'MO567890123456789', 10, 'Honda' , (SELECT catId FROM CATEGORIE WHERE catCode = '5') , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 51) , (SELECT contratId FROM CONTRAT WHERE contratCode = 'ctr021')) , ('veh016', 'MO678901234567890', 12, 'Triumph' , (SELECT catId FROM CATEGORIE WHERE catCode = '5') , (SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4') , (SELECT catComplementId FROM CAT_COMPLEMENT WHERE catComplementCode = 51) , (SELECT contratId FROM CONTRAT WHERE contratCode = 'ctr021')) ; SELECT vehiculeCode as vehCode , vehiculeSerie as vehSerie, vehiculePuissance as vehPuiss, vehiculeMarque as marque , puissanceCode as puissCode , catCode , catComplementCode as compCode , contratCode as ctrCode FROM VEHICULE as x JOIN CATEGORIE as y ON x.catId = y.catId JOIN CAT_COMPLEMENT as z ON x.catId = z.catId AND x.catComplementId = z.catComplementId JOIN PUISSANCE as t ON x.puissanceId = t.puissanceId JOIN CONTRAT as u ON x.contratId = u.contratId ;
vehCode vehSerie vehPuiss marque puissCode catCode compCode ctrCode veh001 FN345678901234567 8 Ford C2 4 41 ctr001 veh011 VM345678901234567 15 Mercedes C5 1 11 ctr021 veh012 VR345678901234567 7 Renault C3 1 11 ctr021 veh013 VR456789012345678 8 Ford C2 4 41 ctr022 veh014 VR567890123456789 6 Ford C2 4 41 ctr023 veh015 MO567890123456789 10 Honda C3 5 51 ctr021 veh016 MO678901234567890 12 Triumph C4 5 51 ctr021
Une vue permettant de voir l’ensemble des véhicules avec leur responsabilité civile et leur CEDEAO :
CREATE VIEW VEHICULE_TARIF_CEDEAO (vehiculeCode, vehiculePuissance, contratCode, contratDateEffet , catCode, catComplCode , tarifDebut, tarifFin, respCivile , cedeaoCode, cedeaoDebut, cedeaoFin, cedeaoMontant , catLibelle , puissanceCode, unite, borneInf, borneSup ) AS SELECT vehiculeCode, vehiculePuissance , contratCode, contratDateEffet , x.catCode, x.catComplCode , x.tarifDebut, x.tarifFin , x.respCivile , z.cedeaoCode, z.cedeaoDebut, z.cedeaoFin, z.cedeaoMontant , x.catLibelle , x.puissanceCode, x.unite, x.borneInf, x.borneSup FROM VEHICULE_TARIF as x JOIN CATEGORIE as y ON x.catCode = y.catCode JOIN CEDEAO_TOUT as z ON y.cedeaoId = z.cedeaoId WHERE x.contratDateEffet >= z.cedeaoDebut AND x.contratDateEffet <= z.cedeaoFin ;
=>
A noter que, par rapport à la table PUISSANCE, la puissance du véhicule 'veh013' (8 CV) est hors des limites (3 CV, 6 CV). Un trigger de contrôle s’impose...vehCode vehPuiss ctrCode ctrEffet catCode complCode tarifDebut tarifFin respCiv cedeaoCode cedeaoDebut cedeaoFin montant catLibelle puisCode unite borneInf borneSup veh001 8 ctr001 2019-06-14 4 41 2018-01-01 2019-10-31 670000 c1 2019-01-02 9999-12-31 100000 Taxis 4 places C2 CV 3 6 veh011 15 ctr021 2018-05-04 1 11 2018-01-01 2018-12-31 395000 c1 2018-01-01 2019-01-01 95000 Affaires, prom C5 CV 15 23 veh012 7 ctr021 2018-05-04 1 11 2018-01-01 2020-09-30 270000 c1 2018-01-01 2019-01-01 95000 Affaires, prom C3 CV 7 10 veh013 8 ctr022 2019-01-03 4 41 2018-01-01 2019-10-31 670000 c1 2019-01-02 9999-12-31 100000 Taxis 4 places C2 CV 3 6 veh014 6 ctr023 2020-12-01 4 41 2020-10-04 9999-12-31 690102 c1 2019-01-02 9999-12-31 100000 Taxis 4 places C2 CV 3 6 veh015 10 ctr021 2018-05-04 5 51 1900-01-01 2019-02-01 77000 c2 1900-01-01 2019-07-31 60000 Motos C3 CV 7 10 veh016 12 ctr021 2018-05-04 5 51 1900-01-01 9999-12-31 85000 c2 1900-01-01 2019-07-31 60000 Motos C4 CV 11 14
Partager