Discussion :

[Invité]

bonjour,

Je souhaiterai faire un programme de vente. Le schéma ci-dessous vous parait-il juste?
Il s'agit donc d'une pme "classique" (vente de divers produit, un tout petit magasin alimentaire).
Selon moi, un client possède éventuellement une carte de fidélité. Ce client achète un produit en étant servi par un vendeur. Pour ce produit, il y a un mouvement qui correspond soit à un achat soit à une vente.

J'ai du mal... qu'en pensez-vous?

[kevin254kl]

Bonjour, le principe de la carte de fidélité n'ai t-il pas de récupérer les informations du client quel intérêt d'une table carte de fidélité?

[Invité]

je pensai que c'était obligatoire car chaque personne ne doit pas avoir obligatoirement une carte de fidélité. Les personnes "de passage" n'en on pas, tout comme les personnes qui ne veulent pas en avoir (oui ça existe).

Est-ce que je me trompe?

[kevin254kl]

Dans réception_ commande tu stock le statut de la livraison? et pour la table achat c'est l'achat avec les fournisseurs si c'est cela tu devrais mettre achat_fournisseur et pour client achat_client je pense.

[fsmrel]

Bonjour julieen03 et kevin254kl,

quel intérêt d'une table carte de fidélité?

L'intérêt est d'éviter de polluer la table CLIENT avec le marqueur NULL.

je pensai que c'était obligatoire car chaque personne ne doit pas avoir obligatoirement une carte de fidélité. Les personnes "de passage" n'en on pas, tout comme les personnes qui ne veulent pas en avoir (oui ça existe).

Est-ce que je me trompe?

Non ! Les Grands magasins du P* furent un jour (il y a plus de 20 ans...) obligés de restructurer leur base de données polluée par le bonhomme Null et d’éliminer celui-ci grâce à la mise en oeuvre de tables telles que CARTE_FIDELITE, tellement il fichait la patouille... Et j’ai plein d’exemples allant dans le même sens.


Examinons maintenant l’association ACHETE. Les cardinalités portées par les pattes de cette association comptent le nombre de fois où les entités connectées peuvent participer à l’association :

Un client peut participer au moins une fois, et au plus plusieurs fois à l’association, c'est-à-dire effectuer plusieurs achats : d’accord.

Un vendeur ne participe qu’une seul fois à l’association : il ne peut donc pas vendre plus d’un [type de] produit, ce qui est quand même bien peu, de même qu’il ne pourra pas être en relation avec plus d’un client, ce qui là aussi est plutôt restreint...

De la même façon, un [type de] produit ne peut être vendu qu’une fois...

Ainsi, en amont du MCD, il est préférable de fournir la liste des règles de gestion des données qui lui ont donné naissance, car il peut y avoir un certain décalage, pour ne pas dire des contradictions. Vous pourriez vous inspirer du travail réalisé par Redreams pour inventorier et présenter les règles de gestion des données.


Dans un MCD, un concept ne doit être présent qu’une seule fois, or le vôtre comporte des données redondantes (et la redondance est l’ennemie des bases de données) :

L’attribut id_client est présent dans les entités-types CLIENT et VENTE_EFFECTUEE ;

L’attribut id_vendeur est présent dans les entités-types VENDEUR, VENTE et VENTE_EFFECTUEE ;

L’attribut cnk_produit est présent dans les entités-types PRODUIT, MOUVEMENT, LIGNE_ACHAT, LIGNE_VENTE...

Du fait de la cardinalité 0,N portée par la patte connectant l’entité-type MOUVEMENT et l’association EXISTE, un mouvement peut concerner plusieurs [types de] produits. Est-ce bien la règle de gestion attendue ? Merci de donner votre définition complète de ce qu’est un mouvement.


Entité-type VENTE_EFFECTUEE

A quoi correspond l’attribut num_ticket ? Concerne-t-il l’ensemble de produits d’une vente effectuée ?

A quoi correspond l’attribut code_barre ? A l’EAN du [type de] produit ?

A propos de l’identification des entités-types :

Un attribut participant à l’identifiant d’une entité-type doit être invariant, c'est-à-dire non significatif : cnk_produit et nom_fournisseur doivent être relégués au rang d’identifiants alternatifs, voyez ici ce qu’a écrit l’excellent Yves Tabourier à propos des dangers inhérents.


J’ai d’autres observations à faire (notamment sur les mouvements), mais vous pouvez déjà vous pencher sur celles que je viens de faire.

[Invité]

Bonjour,

Merci beaucoup pour votre réponse!

J'ai supprimé toutes les redondances.

Voici le nouveau MCD après modifications:



J'ai fait des règles de gestion, avec des images qui ne passent pas si je fais un copier coller. Voici donc le fichier contenant les règles de gestion

Un client achète un ou plusieurs produits.docx

Voici mes règles de gestion sans image:



Un client achète un ou plusieurs produits
Un vendeur vend un ou plusieurs produits
Un produit peut être acheté par un ou plusieurs clients


Un client possède au plus une carte de fidélité.
Une carte de fidélité peut être possédée par une et une seule personne


Un client peut avoir 0 ou plusieurs factures.
Une facture correspond à un seul client.



Une facture comprend une ou plusieurs lignes.
Une ligne de facture correspond à une et une seule facture



Une facture correspond à une et une seule vente effectuée (si deux ventes effectuées  2 factures)
Une vente effectuée correspond à une et une seule facture



Une vente effectuée correspond à une et une seule vente
Une vente correspond à une et une seule vente.

Une vente comprend une ou plusieurs lignes de vente
Une ligne de vente correspond à une seule vente


Une vente correspond à un seul mouvement
Un mouvement correspond à 0 ou une vente


Un mouvement correspond au maximum à une commande
Une commande correspond à un et un seul mouvement.


Une commande comprend une ou plusieurs lignes
Une ligne de commande concerne une et une seule commande.



Pour un produit, il existe 0 ou plusieurs mouvements
Pour un mouvement, il existe 0 ou plusieurs produits.



Pour un produit, il existe 1 ou plusieurs fournisseurs (oui, deux fournisseurs peuvent vendre le même produit)
Un fournisseur possède 0 ou plusieurs fois le produit.








Pour moi, mouvement doit correspondre aux achats fournisseurs ou aux ventes aux clients. Donc par exemple si j'achète 5 poires, mon "nbre_produit" de l'entité produit doit augmenter de 5. A l'inverse, si j'en vends 3 à un client, il doit diminuer de 3.

En ce qui concerne l'entité "produit", peut-on bien la comprendre comme je le pense? C'est-à-dire comme étant 5 poires, et non pas 1 poire par exemple.

Entité-type VENTE_EFFECTUEE


A quoi correspond l’attribut num_ticket ? Concerne-t-il l’ensemble de produits d’une vente effectuée ?

A quoi correspond l’attribut code_barre ? A l’EAN du [type de] produit ?

Oui, num_ticket correspond à l'ensemble des produits d'une vente effectuée par Monsieur Dupont à 9 heures par exemple.
Oui, code_barre correspond à l'EAN du produit. Quand on le scanne, on a droit à tel produit. Il se différencient du CNK en ce que ce dernier n'est qu'une suite de 8 chiffres.

[fsmrel]

Bonsoir julieen03,


On voit mieux le système grâce aux règles de gestion des données.

Une vente effectuée correspond à une et une seule vente
Une vente correspond à une et une seule vente.

A la 2e ligne, je suppose qu’il faut lire : « une vente correspond à une et une seule vente effectuée ».



Entité-type CLIENT

Le nombre de points acquis par un client n’est-il pas calculable, à partir des achats effectués par celui-ci ?

Si oui, l’attribut Points devrait disparaître ;

Sinon, on le conserve, mais c’est une donnée dont la valeur change, varie systématiquement au fil des achats, contrairement à des données stables, invariantes telles que le nom du client. On est ici au niveau conceptuel, où l’utilisateur de la base de données est censé être infiniment patient, mas il n’est pas interdit de réfléchir aux conséquences concrètes : blocage de l’ensemble des données d’un client lors de la mise à jour de l’attribut Points, blocage de l’ensemble des données des clients cohabitant physiquement dans la même page :
Il serait bon de voir l’intérêt d’isoler cet attribut dans une table ad-hoc (avec identification relative, comme dans l’exemple ci-dessous de la carte de fidélité).


Entité-type CARTE_FIDELITE

Vous devriez identifier cette entité-type relativement à l’entité-type CLIENT. Techniquement, pour mettre en œuvre l’identification relative avec JMerise , cliquer sur la patte d’association connectant CARTE_FIDELITE et POSSEDE, puis cocher la case « Lien relatif » :




Vous noterez que l’entité-type CARTE_FIDELITE n’a pas d’identifiant en propre, puisqu’elle hérite de l’identifiant de l’entité-type CLIENT.

Quelle différence y a-t-il entre les points d’une carte de fidélité et ceux du client possesseur de la carte (entité-type CLIENT) ?


Entité-type LIGNE_FACTURE

Au vu de votre MCD, on n’a aucun moyen de savoir à quel produit précis correspondent une ligne de facture et le prix facturé.


Entité-type VENTE_EFFECTUEE

Cette entité-type est en bijection avec l’entité-type FACTURE. Il est d’usage de dire que « ça n’est pas bien » et qu’il faut fondre les deux entités-types en une seule. En fait, rien ne prouve qu’il faille le faire ici (au contraire ! ne serait-ce que d’un point de vue sémantique), vous pouvez donc parfaitement conserver les deux entités-types distinctes. Mais attention au stade SQL ! (cf. la mise en garde concernant l’intégrité référentielle).


Entité-type VENTE

Cette entité-type est en bijection avec l’entité-type VENTE_EFFECTUEE : par contraste avec le cas précédent, cette fois-ci j’ai du mal à voir le distinguo entre ces deux entités-types. Quel inconvénient y aurait-il à les fondre en une entité-type unique ?


Entité-type LIGNE_VENTE

N’y a -t-il pas bijection entre les entités-types LIGNE_VENTE et LIGNE_FACTURE ?

Attribut Quantite_ligne_commande : le nom de cet attribut est vraisemblablement à changer.


Entité-type COMMANDE

Je suppose qu’il manque un attribut Numéro de commande.


Entité-type LIGNE_COMMANDE

Comme dans le cas des lignes de facture, au vu de votre MCD, on n’a aucun moyen de savoir à quel produit précis correspondent une ligne de commande et le prix d’achat.


Entité-type PRODUIT

De quoi « CNK » est-il le sigle ? Le numéro d'identification des produits pharmaceutiques en Belgique ? Quoi qu’il en soit, cet attribut ne doit pas être l’identifiant principal puisqu’il est porteur de sens, il doit faire l’objet d’un identifiant alternatif.

A quoi correspond l’attribut Groupe_produit ?

Les attributs Marque et Laboratoire pourraient faire l’objet d’entités-types.

Le prix de vente est manifestement celui du jour. Vous ne souhaitez pas tenir un historique des prix de vente ? Des taux de TVA ? (Attention aux prix de vente devant figurer sur les factures).

La présence ici du prix d’achat signifie que ce prix est le même pour l’ensemble des fournisseurs. En est-il vraiment ainsi ? Incidemment, y a-t-il des remises ? Vous ne souhaitez pas tenir un historique des prix d’achat ?

Si je vous suis, l’attribut Nbre_produits sert pour la tenue des stocks. Là encore, il s’agit d’une donnée hautement instable, cause de blocages potentiels lors des mises à jour, et devrait donc prudemment faire l’objet d’une entité-type « faible », identifiée relativement à PRODUIT (techniquement, faire comme pour l’entité-type CARTE_FIDELITE).


Entité-type MOUVEMENT

Si je comprends bien, un mouvement correspond soit à un achat auprès d’un fournisseur, soit à une vente à un client : il s’agit d’une factorisation dont le bien fondé reste à démontrer (pour être à la mode : ne faisons pas d’amalgame...)

Quel est le rôle de l’attribut Reception_commande ? En quoi concerne-t-il les ventes ?

[Invité]

J'ai pris note des vos réflexions pour les entités carte_fidélite, produit, client, ligne_facture, vente_effectuée.

Entité-type VENTE

Cette entité-type est en bijection avec l’entité-type VENTE_EFFECTUEE : par contraste avec le cas précédent, cette fois-ci j’ai du mal à voir le distinguo entre ces deux entités-types. Quel inconvénient y aurait-il à les fondre en une entité-type unique ?

Je voulais que vente_effectuee constitue un "historique", de sorte que lorsqu'on entre le numéro de ticket, on puisse voir toutes les produits vendus qui correspondent ce ticket. Comment fait-on un historique ?

Entité-type COMMANDE

Je suppose qu’il manque un attribut Numéro de commande.

Oui c'est juste. Je n'y avais pas pensé mais il est impératif que pouvoir faire une différence deux commandes d'un même fournisseur.

Entité-type PRODUIT

De quoi « CNK » est-il le sigle ? Le numéro d'identification des produits pharmaceutiques en Belgique ? Quoi qu’il en soit, cet attribut ne doit pas être l’identifiant principal puisqu’il est porteur de sens, il doit faire l’objet d’un identifiant alternatif.

Oui, c'est bien ça. L' objectif est d'identifier de façon non équivoque tous les différents conditionnements de produits.

A quoi correspond l’attribut Groupe_produit ?

Je l'ai supprimé car il est devenu inutile.

Les attributs Marque et Laboratoire pourraient faire l’objet d’entités-types.

J'ai donc fait deux entités-types : marque et laboratoire.
Marque :
Un produit ne peut être que d'une seule marque
une marque peut posséder un ou plusieurs produits. J'aurai même plutôt dit qu'une marque possède plusieurs produits, mais a relation n,n n'existe pas apparemment.
laboratoire:

un produit ne peut être que d'un seul laboratoire.
une laboratoire comprend un ou plusieurs produits.

Le prix de vente est manifestement celui du jour. Vous ne souhaitez pas tenir un historique des prix de vente ? Des taux de TVA ? (Attention aux prix de vente devant figurer sur les factures).

Comment faire un historique?

La présence ici du prix d’achat signifie que ce prix est le même pour l’ensemble des fournisseurs. En est-il vraiment ainsi ? Incidemment, y a-t-il des remises ? Vous ne souhaitez pas tenir un historique des prix d’achat ?

Non, le prix n’est pas le même pour tous les fournisseurs ! Je devrais donc placer Prix_achat_htva, Prix_achat_tvac, prix_vente_htva, prix_vente_tvac dans « Catalogue » ?

Si je vous suis, l’attribut Nbre_produits sert pour la tenue des stocks. Là encore, il s’agit d’une donnée hautement instable, cause de blocages potentiels lors des mises à jour, et devrait donc prudemment faire l’objet d’une entité-type « faible », identifiée relativement à PRODUIT (techniquement, faire comme pour l’entité-type CARTE_FIDELITE).

J'ai rectifié.

Entité-type MOUVEMENT

Si je comprends bien, un mouvement correspond soit à un achat auprès d’un fournisseur, soit à une vente à un client : il s’agit d’une factorisation dont le bien fondé reste à démontrer (pour être à la mode : ne faisons pas d’amalgame...)

Oui, c'est bien ce que je souhaitai faire : mouvement = achat auprès d'un fournisseur ou vente à un client. Y-a-t-il moyen de faire mieux?

En vous souhaitant déjà une bonne année et heureuse année 2016

[fsmrel]

Bonsoir julieen03,


Merci pour vos vœux, et à mon tour vous adresse les miens.


Identifiants alternatifs

Comme je vous l’ai conseillé, vous avez bien mis en oeuvre des identifiants dépourvus de signification, invariants. Toutefois, les attributs ayant perdu leur statut initial d’identifiant doivent conserver leur propriété fondamentale d’unicité.

Ainsi en va-t-il du numéro de facture (entité-type FACTURE), qui doit devenir identifiant alternatif, l’attribut num_facture doit conserver sa propriété d’unicité. Avec JMerise, vous procédez comme ci-dessous :




Il faudra définir les identifiants alternatifs des autres entités-types concernées, telles que VENTE (attribut numero_ticket), PRODUIT (cnk_produit), COMMANDE (numero_commande).

Identification des fournisseurs : à l’usage des utilisateurs, vous pourriez utiliser un identifiant alternatif tel que le numéro de TVA intracommunautaire ou équivalent.

Votre MCD contient peut-être d’autres attributs pouvant faire l’objet de la contrainte d’unicité : par exemple, si deux clients ne peuvent pas avoir la même adresse de courriel, alors l’attribut email_fournisseur est concerné par cette contrainte, même chose pour le code barre des produits.


Identification relative

Situons-nous au niveau tabulaire, SQL, et examinons le contenu de la table LIGNE_FACTURE :

id_facture    id_ligne_facture    id_produit    quantite
----------    ----------------    ----------    --------
         1                   1            48          10
         1                   2            14           2
         1                   3            25           1

         2                   1             31          8
         2                   2             27         14
         2                   3             48          3
         2                   4             79         10

         3                   1             14          5
         3                   2             48          4

       ...                 ...            ...        ...

Les valeurs prises par l’attribut id_facture permettent de repérer chacune des factures 1, 2, 3, etc.

Chacune des factures comporte un certain nombre de lignes de facture, chacune d’elles repérée par un numéro 1, 2, 3, etc. La numérotation commence à 1 pour chaque ligne de facture : l’attribut id_ligne_facture permet de distinguer chaque ligne au sein d’une facture : il se comporte en identifiant relatif.

En conséquence, dans le contexte SQL, la paire K = {id_facture, id_ligne_facture} constitue une clé candidate, plus précisément la clé « primaire » de la table LIGNE_FACTURE : il n’existe pas deux lignes de la table ayant même valeur pout K, c'est-à-dire que cette clé garantit la règle d’unicité des clés candidates, et par ailleurs il n’existe pas de sous-ensemble strict K’ de K garantissant aussi cette règle d’unicité (règle d’irréductibilité).

En remontant au MCD, l’attribut id_ligne_facture est identifiant relatif, et l’entité-type LIGNE_FACTURE est identifiée par la paire {COMPOSER, id_ligne_facture}, où « COMPOSER » est le nom de l’association entre les entités-types FACTURE et LIGNE_FACTURE.

Dans le contexte JMerise, on coche la case « Lien relatif » de la fenêtre « Cardinalité » :





Le script SQL produit par JMerise n’est pas correct : l’ordre des colonnes de la clé primaire générée est en effet celui-ci : (id_ligne_facture, id_facture), alors qu’il doit être celui-là : (id_facture, id_ligne_facture).


En passant, dans le document résumant les travaux des merisiens en 1990 (Afcet – Le formalisme de données Merise - Extensions du pouvoir d’expression - Journée d’étude organisée par le Groupe de Travail 135 « Conception des systèmes d’information » (Collège AFCET-GID) - Jeudi 15 novembre 1990, Paris.), l’identifiant relatif est officiellement caractérisé par le fait qu’il « comprend » au moins une relation (association) :





A cette occasion, on peut noter que cette définition vaut pour l’entité-type CARTE_FIDELITE, laquelle n’est pas dotée d’un attribut jouant le rôle d’identifiant relatif, puisqu’un client a au plus une carte :





De manière générale, l’identification relative est utilisée pour les entités-types « faibles », celles qui n’ont pas d’existence propre, mais dépendant d’une autre entité-type. Ainsi en l’absence de son attachement à une facture, une ligne de facture n’a pas de sens, elle n’est qu’un attribut multivalué d’une facture : à supposer qu’une facture meure, ses lignes meurent de facto, au même titre, par exemple, que la date de facture.

Votre MCD comporte d’autres entités-types faibles : Ligne_commande, Ligne_vente, Ligne_livraison...

[fsmrel]

Pour éviter de tout mélanger, j'isole dans des messages distincts les différents grands sujets.


Historisation


Vous posez la question : « Comment faire un historique ? »

Prenons le cas des ventes de produits. Le prix de vente peut évoluer dans le temps, même chose en ce qui concerne la TVA. Dans ces conditions, en ce qui concerne l’actualité, c'est-à-dire ce qui est en cours, l’entité-type PRODUIT est porteuse :

— D’un attribut permettant de connaître le prix de vente actuel d’un produit (prix_vente_htva) ;

— D’un attribut permettant de connaître la date depuis laquelle ce produit est vendu à ce prix (prix_vente_htva_depuis) ;

— D’un attribut permettant de connaître la tva actuellement applicable à ce produit prix (taux_tva) ;

— D’un attribut permettant de connaître la date depuis laquelle cette tva est applicable à ce produit (taux_tva_depuis).

Le passé du produit concerne les changements de son prix de vente, ainsi que les changements de taux de TVA qui d’autre part ont pu l’affecter. Pour cela, on met en oeuvre :

— Une entité-type (identifiée relativement à PRODUIT) permettant de savoir quel fut le prix de vente du produit pendant telle période ;

— Une entité-type (identifiée relativement à PRODUIT) permettant de savoir quel était le taux de TVA applicable pendant telle période :

N.B. Le prix de vente TTC étant calculable à partir du prix de vente HT et du taux de TVA, on ne le fait pas figurer dans le MCD.


En ce qui concerne les ventes :

Une vente Vx correspond exactement à une facture Fx (bijection), aussi pour connaître les produits de la vente Vx, on sélectionne les lignes de facture de Fx, lesquelles déterminent chacune un produit : logiquement, les entités-types LIGNE_FACTURE et LIGNE_VENTE sont aussi en bijection, et s’il en est bien ainsi, la 2e est une redondance, un clone de la 1re et devrait disparaître. Qu’en est-il ?

De la même façon, on sait déterminer les ventes du produit Pz à partir des lignes de facture. Quant aux mouvements de vente, on sait aussi les déterminer à partir des factures et de leurs lignes. Eventuellement, vous pouvez regrouper des ventes dans une expédition (avec date d’expédition). Même principe pour les achats, en regroupant les réceptions dans une entité-type ad-hoc (avec date de réception).

[fsmrel]

Contraintes de chemin

Le prix n’est pas le même pour tous les fournisseurs ! Je devrais donc placer Prix_achat_htva, Prix_achat_tvac, prix_vente_htva, prix_vente_tvac dans « Catalogue » ?

D’accord en ce qui concerne le prix d’achat (je ne vois pas ce que vient faire le prix de vente...)

Voyons voir maintenant votre association ternaire CATALOGUE :




Une commande peut participer N fois à l’association, ce qui a pour conséquence qu’une commande peut être associée à plusieurs produits, ce qui est normal, mais aussi à plusieurs fournisseurs, ce qui cette fois-est problématique...

Si la règle est qu’une commande est passée auprès d’un seul fournisseur, la représentation doit devenir la suivante :





En complétant avec les lignes de commande :





Mais attention, on voit apparaître une boucle : il y a un 1er chemin, appelons-le Chemin_1, permettant d’aller de l’entité-type LIGNE_COMMANDE à l’entité-type PRODUIT, via l’association REFERENCER, mais il existe aussi un 2e chemin, appelons-le Chemin_2, permettant d’aller de l’entité-type LIGNE_COMMANDE à l’entité-type PRODUIT, via les associations COMPOSER > PASSER_A > CATALOGUE, et le risque est que, via le chemin Chemin_2, le fournisseur impliqué dans cette affaire ne fournisse pas le produit déterminé par la ligne de commande (chemin_1).

La résolution du problème est ici à effectuer au niveau relationnel. Avec un langage conforme à la théorie relationnelle, ceci passe par une contrainte :

CONSTRAINT CHK01 
    JOIN (LIGNE_COMMANDE, COMMANDE, FOURNISSEUR) {id_fournisseur, id_produit} ⊆ CATALOGUE {id_fournisseur, id_produit} ;

Ce qui se lit : l’ensemble défini par la projection {id_fournisseur, id_produit} appliquée à la jointure naturelle des variables LIGNE_COMMANDE, COMMANDE, FOURNISSEUR, doit être inclus dans l’ensemble défini par la projection {id_fournisseur, id_produit} appliquée à la variable CATALOGUE.

En SQL, la contrainte fera l’objet d’une assertion (instruction CREATE ASSERTION), mais aujourd’hui les SGBD du marché ne proposent pas cette instruction. Donc, soit on met en œuvre des triggers, soit on agit directement au niveau des instructions CREATE TABLE.

Agissons au niveau des CREATE TABLE. Ceci nécessite déjà la transformation de l’association CATALOGUE en entité-type et sa mise en relation avec l’entité-type LIGNE_COMMANDE :





A cette occasion, vous observerez que, à l’instar de CARTE_FIDELITE, la nouvelle entité-type CATALOGUE n’a pas d’identifiant en propre, mais elle a un identifiant relatif composé, hérité de PRODUIT d’une part (par le truchement de l’association CAT_PRO) et de FOURNISSEUR d’autre part (par le truchement de l’association CAT_FOU).

Je crois qu’il est temps de passer au diagramme relationnel puis au script SQL de définition des tables pour mieux voir comment on procède. J’abandonne JMerise qui est déficient à ce stade, et passe à MySQL Workbench :





La notation utilisée pour les cardinalités est dite patte d’oie ou de corbeau (Crow’s foot) : « » pour 0,N, et « » pour 1,N.

Les traits continus symbolisent les associations dans lesquelles l'identification relative est partie prenante.

Les losanges rougeâtres symbolisent des clés étrangères. Les clés primaires sont repérées par les mickeys et (rouge : la clé est en même temps clé étrangère, conséquence de l’identification relative).

Script SQL correspondant :

CREATE TABLE FOURNISSEUR 
(
        id_fournisseur                INT                    NOT NULL
      , nom_fournisseur               VARCHAR(25)            NOT NULL
    , CONSTRAINT FOURNISSEUR_PK PRIMARY KEY (id_fournisseur)
) ;

CREATE TABLE PRODUIT 
(
        id_produit                    INT                    NOT NULL
      , cnk_produit                   VARCHAR(15)            NOT NULL
      , nom_produit                   VARCHAR(48)            NOT NULL
      , code_barre                    VARCHAR(13)            NOT NULL
      , prix_vente_htva               DECIMAL(7,2)           NOT NULL
      , prix_vente_htva_depuis        DATE                   NOT NULL
      , taux_tva                      DECIMAL(5,2)           NOT NULL
      , taux_tva_depuis               DATE                   NOT NULL
    , CONSTRAINT PRODUIT_PK PRIMARY KEY (id_produit)
    , CONSTRAINT PRODUIT_CNK_AK UNIQUE (cnk_produit)
    , CONSTRAINT PRODUIT_EAN_AK UNIQUE (code_barre)
) ;

CREATE TABLE CATALOGUE 
(
        id_fournisseur                INT                    NOT NULL
      , id_produit                    INT                    NOT NULL
      , prix_achat_ht                 DECIMAL(7,2)           NOT NULL
      , taux_tva_achat                DECIMAL(5,2)           NOT NULL
    , CONSTRAINT CATALOGUE_PK PRIMARY KEY (id_fournisseur, id_produit)
    , CONSTRAINT CATALOGUE_PRODUIT_FK FOREIGN KEY (id_produit)
          REFERENCES PRODUIT (id_produit)
    , CONSTRAINT CATALOGUE_FOURNISSEUR_FK FOREIGN KEY (id_fournisseur)
          REFERENCES FOURNISSEUR (id_fournisseur)
) ;

CREATE TABLE COMMANDE 
(
        id_fournisseur                INT                    NOT NULL
      , id_commande                   INT                    NOT NULL
      , numero_commande               CHAR(12)               NOT NULL
      , date_commande                 DATE                   NOT NULL
    , CONSTRAINT COMMANDE_PK PRIMARY KEY (id_commande)
    , CONSTRAINT COMMANDE_AK UNIQUE (numero_commande)
    , CONSTRAINT COMMANDE_FOURNISSEUR_FK FOREIGN KEY (id_fournisseur)
          REFERENCES FOURNISSEUR (id_fournisseur)
) ;

CREATE TABLE LIGNE_COMMANDE 
(
        id_commande                   INT                    NOT NULL
      , id_ligne_commande             INT                    NOT NULL
      , id_produit                    INT                    NOT NULL
      , id_fournisseur                INT                    NOT NULL
      , quantite_ligne_commande       INT                    NOT NULL
    , CONSTRAINT LIGNE_COMMANDE_PK PRIMARY KEY (id_commande, id_ligne_commande)
    , CONSTRAINT LIGNE_COMMANDE_COMMANDE_FK FOREIGN KEY (id_commande)
          REFERENCES COMMANDE (id_commande) ON DELETE CASCADE
    , CONSTRAINT LIGNE_COMMANDE_CATALOGUE_FK FOREIGN KEY (id_fournisseur, id_produit)
          REFERENCES CATALOGUE (id_fournisseur, id_produit)
) ;

Dans ce script, vous observerez que {cnk_produit}, {code_barre} et {numero_commande} sont des clés alternatives (contrainte UNIQUE).


Mais, dans le contexte des SGBD du jour, le problème de la contrainte de chemin n’est pas encore résolu. Pour y parvenir, il faut propager l’attribut id_fournisseur depuis la table COMMANDE jusqu’à la table LIGNE_COMMANDE, et pour cela :

1) On dote la table COMMANDE d’une surclé {id_commande, id_fournisseur} :

CREATE TABLE COMMANDE 
(
        id_fournisseur                INT                    NOT NULL
      , id_commande                   INT                    NOT NULL
      , numero_commande               CHAR(12)               NOT NULL
      , date_commande                 DATE                   NOT NULL
    , CONSTRAINT COMMANDE_PK PRIMARY KEY (id_commande)
    , CONSTRAINT COMMANDE_SK UNIQUE (id_commande, id_fournisseur)
    , CONSTRAINT COMMANDE_AK UNIQUE (numero_commande)
    , CONSTRAINT COMMANDE_FOURNISSEUR_FK FOREIGN KEY (id_fournisseur)
          REFERENCES FOURNISSEUR (id_fournisseur)
) ;

2) On modifie la contrainte référentielle LIGNE_COMMANDE_COMMANDE_FK de la table LIGNE_COMMANDE pour qu’elle fasse référence à la surclé de la table COMMANDE :

CREATE TABLE LIGNE_COMMANDE 
(
        id_commande                   INT                    NOT NULL
      , id_ligne_commande             INT                    NOT NULL
      , id_produit                    INT                    NOT NULL
      , id_fournisseur                INT                    NOT NULL
      , quantite_ligne_commande       INT                    NOT NULL
    , CONSTRAINT LIGNE_COMMANDE_PK PRIMARY KEY (id_commande, id_ligne_commande)
    , CONSTRAINT LIGNE_COMMANDE_COMMANDE_FK FOREIGN KEY (id_commande, id_fournisseur)
          REFERENCES COMMANDE (id_commande, id_fournisseur) ON DELETE CASCADE
    , CONSTRAINT LIGNE_COMMANDE_CATALOGUE_FK FOREIGN KEY (id_fournisseur, id_produit)
          REFERENCES CATALOGUE (id_fournisseur, id_produit)
) ;

Dans cette table, l’attribut id_fournisseur n’est présent qu’une seule fois et il est utilisé aussi bien avec la contrainte référentielle LIGNE_COMMANDE_COMMANDE_FK ciblant la table COMMANDE qu’avec la contrainte référentielle LIGNE_COMMANDE_CATALOGUE_FK ciblant la table CATALOGUE : désormais il n’est plus possible, selon le chemin emprunté, qu’une ligne de commande fasse référence à des fournisseurs et produits distincts.


Je conviens que l'exercice n’est pas basique, mais l’intégrité des données a un prix...


Une autre solution, plus simple (absence de surclé), aurait été, dans le dernier diagramme, d’identifier l’entité-type COMMANDE relativement à l’entité-type FOURNISSEUR : vous pouvez vous y exercer.