Discussion :

[seabs]

Bonjour,

Je vous présente une modélisation pour assurer la facturation des bons de livraison. Mon inquiétude se situe sur l'entité calcul de la facture sachant que les règles de gestion sont les suivantes :
R1 - un bon de livraison peut comporter une ou plusieurs lignes ;
R2 - Les bons de livraison sont émis au cours du mois au fur et à mesure des expéditions ;
R3 - Les bons de livraisons du mois sont facturés en fin de mois (En principe dernier jour du mois) ;
R5 - Une facture comporte obligatoirement un bon de livraison, mais pour un client elle peut comprendre tous ceux émis dans la mois.

Pour la modélisation, j'ai utilisé PowerAMC version 15.1
Le MCD que j'ai construit est présenté ci-dessous (Entité / association)



J'aurais eu la possibilité d'utiliser une version (Entité / Relation) ce qui aurait conduit au même résultat mais en plus simple dans la modification du MPD.

La construction du MPD doit permettre de répondre aux règles ci-après :

• Ne pas créer des colonnes comportant le bonhomme NULL
• Éviter, dans la mesure du possible, de recourir à un trigger
• Une facture doit comporter obligatoirement un ou plusieurs bons de livraison
• Une livraison doit être facturée à la bonne entité
• Un bon de livraison ne doit pas être facturé plusieurs fois
• Un bon de livraison ne doit pas être attaché à une facture dont le n° n'existe pas

De mon MCD ci-dessus, j'ai obtenu le MPD brut



Après contrôle, j'ai remarqué que la clé entité était présente deux fois, je l'ai éliminé
Puis, pour contrôle qu'un bon n'était facturé qu'une fois, j'ai mis en place une clé alternative {ENTITE_PK, LIVR_PK}

Le MPD final est



Sa traduction en SQL sera

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
create table CALCUL (
ENTITE_PK            D_INDEX                        not null,
LIVR_PK              D_INDEX                        not null,
FACT_PK              D_INDEX                        not null,
constraint PK_CALCUL primary key (ENTITE_PK, LIVR_PK, FACT_PK),
constraint AK_AK_CALCUL unique (ENTITE_PK, LIVR_PK)
);

alter table CALCUL
   add constraint FK_CALCUL_R1_LIVRAISO foreign key (ENTITE_PK, LIVR_PK)
      references LIVRAISON (ENTITE_PK, LIVR_PK);

alter table CALCUL
   add constraint FK_CALCUL_R3_FACTURE foreign key (ENTITE_PK, FACT_PK)
      references FACTURE (ENTITE_PK, FACT_PK);

Est-il possible de me confirmer que la table SQL peut répondre à toutes les contraintes indiquées.
Merci également de m'indiquer les erreurs commises et les améliorations possibles.

[JPhi33]

Bonjour seabs,

L'entité CALCUL, qui peut être vue comme le regroupement dans une FACTURE des BL du mois pour une ENTITE, est en fait une association.

[ LIVRAISON ]--0,1----( CALCUL )----1,n--[ ENTITE ]

Une association de ce type (0,1 - x,n) se dérive en table au niveau relationnel de la manière suivante :

CALCUL (LIV_IndexEntite, IndexLivraison, IndexEntite, IndexFacture)

où {IndexEntite, IndexLivraison} est la clé identifiante de CALCUL qui référence LIVRAISON et {IndexEntite, IndexFacture} est une clé étrangère qui référence FACTURE.

Après contrôle, j'ai remarqué que la clé entité était présente deux fois, je l'ai éliminé

La suppression d'une des clés IndexEntité cache l'absence d'une règle :

R6 - Les livraisons facturées ensemble (regroupées dans une même facture) doivent appartenir à la même ENTITE que celle à laquelle la facture est rattachée

Cette règle se modélise par une contrainte d'inclusion de CALCUL et LIVRER dans FACTURER et doit être implémentée par trigger.

[seabs]

Bonjour JPhi33,

Merci pour ta réponse rapide.

Une association de ce type (0,1 - x,n) se dérive en table au niveau relationnel de la manière suivante :

CALCUL (LIV_IndexEntite, IndexLivraison, IndexEntite, IndexFacture)

où {IndexEntite, IndexLivraison} est la clé identifiante de CALCUL qui référence LIVRAISON et {IndexEntite, IndexFacture} est une clé étrangère qui référence FACTURE.

Je suis d'accord avec toi. J'avais utilisé cette approche en premier, mais elle m'obligeait à mettre en place un trigger. Or, j'ai recherché s'il n'y avait pas une solution sans trigger. C'est pour cette raison que j'ai modifié mon MCD et MPD comme il est indiqué dans mon premier message.

En conclusion, la méthode avec trigger est-elle préférable à celle sans trigger ? Je n'ai pas un avis très tranché sur ce point.

Encore merci, je vais réfléchir et attendre de savoir si quelqu'un peut répondre à mon interrogation.

[SQLpro]

En conclusion, la méthode avec trigger est-elle préférable à celle sans trigger ? Je n'ai pas un avis très tranché sur ce point.

Vous ne devez pas raisonner comme cela... Votre modèle doit faire abstraction du SGBDR cible... et en cette matière, certains utilisent directement les ASSERTIONS (contraintes normatives SQL au niveau de la base) en lieu et place des déclencheurs mais ils sont rares...

De toutes faiçons, les triggers restent très intéressant d'un point de vu performances, tant qu'ils restent ensembliste (éviter les trigger PER ROW comme ce que fait hélas MySQLmerde ou PostGreSQL...)

Si fsmrel passer par là... il va vous passer un savon en vous parlant de la dunette et de la cale... Je le sent venir !

A +

[fsmrel]

Ave omnes,

Je selle mon cheval et j'arrive au galop !

François

[fsmrel]

Salve omnes,


Je comprends les scrupules de notre vieil ami seabs, qui ne tient pas — entre autres choses — à ce que le bonhomme Null vienne ficher la zoubia dans la base de données. Partant du MCD suivant :

[ LIVRAISON ]--0,1----( CALCUL )----1,n--[ ENTITE ]

On sait que PowerAMC va produire le MLD suivant, selon lequel Null (horresco referens!) trouvera la porte ouverte :




Pour empêcher cette intrusion, JPhi33 propose donc au stade MLD la mise en œuvre de la table CALCUL suivante :

CALCUL (LIV_IndexEntite, IndexLivraison, IndexEntite, IndexFacture)

Pour remonter au MCD, côté PowerAMC, on part alors du diagramme suivant :





Ce qui donne au stade MLD :




Diagramme dans lequel est présent un cycle qu’il faut évidemment faire disparaître sur le champ, à la mimine :



Je fais observer en passant que la clé de CALCUL n’est pas la paire {LivraisonId, EntiteId} mais le singleton {LivraisonId}, sinon cela veut dire que la cardinalité 0,1 a été transformée en 0,N.

J’ai renommé les attributs originaux LIV_IndexEntite et IndexLivraison respectivement en EntiteId et LivraisonId, car le terme « index » me fait loucher, il est trop connoté (un index est un concept du niveau physique, pour faire court, c’est la réalisation d’un arbre).

Je n’ai pas fait intervenir l’attribut EntiteId dans l’en-tête de l’entité-type LIVRAISON (donc dans l’en-tête de la table du même nom), mais c’est indépendant du problème dont je traite : EntiteId interviendra en son temps.


@seabs

L’entité-type CALCUL est bien une association déguisée, ce qui n’est pas peccamineux . Un remarque toutefois : la patte connectant CALCUL et R2 doit être porteuse d’une cardinalité 1,1 et non pas (1,1), sinon PowerAMC va faire participer l’attribut LIV_IndexEntite à la clé primaire de CALCUL, alors qu’il existe une dépendance fonctionnelle LIVRAISON -> CALCUL :



Du fait de la DF LIVRAISON -> CALCUL, la clé primaire de la table calcul n’est pas le triplet {EntiteIndex, IndexBonDeLivraison, IndexDesFactures} mais la paire {EntiteIndex, IndexBonDeLivraison}.

Sinon votre MCD est correct (à une contrainte d'inclusion près, mais qu'on ne sait pas représenter dans le cas de PowerAMC) .


De mon côté j’ai vérifié ce que donnait PowerAMC à partir du MCD suivant :





Bien entendu, il génère un MLD où est présent le cycle évoqué ci-dessus, et que l’on supprime . Il reste :




Il n’y a plus qu’à mettre en œuvre la contrainte de chemin pour interdire que les livraisons de Fernand soient affectées au factures de Raoul, ce qui consiste simplement à éliminer l’attribut faisant double emploi FAC_EntiteId :




Votre instruction CREATE TABLE CALCUL est donc correcte, à condition de virer FACT_PK de la clé primaire, sinon vous violeriez la règle « Un bon de livraison ne doit pas être facturé plusieurs fois » :

Code SQL :

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

constraint PK_CALCUL primary key (ENTITE_PK, LIVR_PK)

Dans ces conditions, pas besoin d’assertion ou de trigger pour faire respecter la contrainte de chemin, cette clé primaire et les clés étrangères suffisent. On retrouve des scénarios alternatifs, analogues à ceux dont on avait débattu avec Benallasiham (cf. la discussion : trigger ou propagation de l’identification relative).

Certains utilisent directement les ASSERTIONS (contraintes normatives SQL au niveau de la base) en lieu et place des déclencheurs mais ils sont rares...

Ben alors Fred, c’est pour quand les assertions en ce qui concerne les SGBD du marché ? Il y a 40 ans, l’ancêtre SEQUEL 2 en était pourvu (cf. SEQUEL 2: A Unified Approach to Data Definition, Manipulation, and Control (1976, IBM Journal of Research and Development, Chamberlin et son équipe)...






Je sais que tu vas me dire que les assertions ça coûte la feau des pesses (comme on disait ça de l'intégrité référentielle en 1984), mais on n’est plus en 1976...

[JPhi33]

Bonsoir à tous,

Partant du MCD suivant :

[ LIVRAISON ]--0,1----( CALCUL )----1,n--[ ENTITE ]

Et là, je me rends compte que j'ai commis une bourde avec ce schéma ! En effet, cette phrase :

L'entité CALCUL, qui peut être vue comme le regroupement dans une FACTURE des BL du mois pour une ENTITE, est en fait une association.

se modélise ainsi :

[ LIVRAISON ]--0,1----( CALCUL )----1,n--[ FACTURE ]

Ce qui ne change rien au reste de mon message... et n'enlève rien à celui de François avec lequel je suis tout à fait en phase.

[fsmrel]

Bonjour,

Et là, je me rends compte que j'ai commis une bourde avec ce schéma !

Qui pourrait t’en vouloir ? On est tous sujets à ce genre de distraction ; je m’étais du reste fait la réflexion comme quoi ça n’était pas du JPhi33, et que tu avais sans doute plusieurs choses qui se bousculaient dans ta tête à ce moment-là... De mon côté, après avoir relu plus d’une fois mon message précédent, mot à mot, et l’avoir publié, j’ai pu constater, avec stupeur, qu’au lieu d’écrire :

Du fait de la DF LIVRAISON -> CALCUL, la clé primaire de la table calcul n’est pas le triplet {EntiteIndex, IndexBonDeLivraison, IndexDesFactures} mais la paire {EntiteIndex, IndexBonDeLivraison}

J’avais pondu ceci (copier/coller mal contrôlé ...) :

Du fait de la DF LIVRAISON -> CALCUL, la clé primaire de la table calcul n’est pas le triplet {EntiteIndex, IndexBonDeLivraison, IndexDesFactures} mais la paire {IndexBonDeLivraison, IndexDesFactures}

Tous autant que nous sommes, depuis que nous nous activons chez DVP, combien de coquilles de ce genre traînent encore dans nos réponses ? Combien en commettrons-nous d'autres, en dépit d'une relecture attentive ?

Nul n’est à l’abri. Dans ce genre de blague, j’avais attiré l’attention de mon copain du Relationland, car j’avais en son temps relevé dans un de ses ouvrages (scripta manent!) une erreur propre à semer un très grand trouble dans les esprits, alors que le sujet n’est pas trivial (dépendances de jointure et 5e forme normale).

Sa réponse :

[...] thank you for drawing my attention to the bug in Fig. 9.2! I don't know how that happened. Probably I was so tied up in the mechanics of drawing all those arrows etc. that I didn't notice that the example made no sense. The odd thing is that my notes, on which the figure was based, were correct. Anyway, I attach a list of all currently known bugs in the text. [...]

Ah ! Distraction, quand tu nous tiens...

[seabs]

Bonjour,

Je remercie tous les intervenants pour leurs réponses.

Etant absent depuis quelques jours, je n'ai pas étudié, en détail les explications fournies, mais un regard rapide me permet de penser que j'ai la réponse à mes interrogations.

Dès que j'ai un peu disponibilité, je regarde tout cela en détail.

Je ne mets donc pas résolu dans l'immédiat.

A+