Discussion :

[frm013]

re,

Ok, je comprends mieux, cela me sera utile.

On peut le dire, vous étiez sur le bon chemin (en dépit des contraintes...), bravo !

Grace à vous surtout.

Du lourd donc... Je garderai un tube d’aspirine à portée de main...

Ouuulàà non!! Uniquement la partie membres!!
D'ici ce soir je pense.

Restera le modèle des commandes, sur lequel j'ai bossé, mais qui demande validation et conseils. Dans ce cas, l'aspirine sera probablement de mise, j'en ai toujours à portée de main...

[frm013]

Bonsoir François,

Je suis toujours là... je me suis battu avec SQL Power Architect qui n'accepte pas une FK vers autre chose qu'une PK.

J'avais évalué divers outils de modélisation la semaine dernière, aucuns n'allait jusqu'au bout... il manquait toujours quelque chose d'indispensable à mon sens. Tiens, ça pourrait être l'objet d'un test sur DVP

Je suis souvent étonné de voir que des logiciels ( payants ou open source ) s'arrêtent en chemin alors que la base est correcte : deux trois détails manquent pour être complets...

Visual Paradigm, que j'avais installer comme outil UML, s'avère pouvoir créer des schémas E/R, avec vues, code correct, les options qui vont bien ( à part les contraintes déférables ). Il est un peu lourd, mais je crois que c'est le prix à payer, à propos, dans sa version avec reverse et forward il est payant (225€ ) c'est raisonnable. And last but not the least, il tourne sur MAC

Je part avec ça, il est temps de travailler dans le concret, j'espère ne pas être déçu une nouvelle fois.

à toute à l'heure ou demain matin

Bonne soirée,
François

[fsmrel]

Salve,

je me suis battu avec SQL Power Architect qui n'accepte pas une FK vers autre chose qu'une PK.

Alors que cette possibilité fait partie de la norme SQL depuis 20 ans (SQL-92). Faudra-t-il attendre encore 20 ans ?

[frm013]

Bonjour François,

Alors... Visual Paradigm, c'est encore pas génial, mais bon...



Code SQL :

Code SQL :

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
CREATE TABLE directeur (
  entreprise_id int4 NOT NULL, 
  employe_id    int4 NOT NULL, 
  magasin_id    int4 NOT NULL, 
  PRIMARY KEY (entreprise_id, 
  employe_id), 
  CONSTRAINT directeur_ak1 
    UNIQUE (entreprise_id, magasin_id));
CREATE TABLE employe_mag (
  entreprise_id int4 NOT NULL, 
  employe_id    int4 NOT NULL, 
  magasin_id    int4 NOT NULL, 
  PRIMARY KEY (entreprise_id, 
  employe_id), 
  CONSTRAINT emp_mag_ak_1 
    UNIQUE (entreprise_id, employe_id, magasin_id));
CREATE TABLE responsable (
  employe_id int4 NOT NULL, 
  PRIMARY KEY (employe_id));
CREATE TABLE employe (
  entreprise_id int4 NOT NULL, 
  employe_id    int4 NOT NULL UNIQUE, 
  PRIMARY KEY (entreprise_id, 
  employe_id));
CREATE TABLE metier (
  metier_id  SERIAL NOT NULL, 
  lib       varchar(30) NOT NULL, 
  PRIMARY KEY (metier_id));
CREATE TABLE mra (
  mra_id    int4 NOT NULL, 
  metier_id int4 NOT NULL, 
  PRIMARY KEY (mra_id));
CREATE TABLE vendeur (
  vendeur_id int4 NOT NULL, 
  PRIMARY KEY (vendeur_id));
CREATE TABLE magasin (
  vendeur_id int4 NOT NULL, 
  magasin_id int4 NOT NULL UNIQUE, 
  siret      varchar(14) NOT NULL, 
  logo       varchar(255) NOT NULL, 
  PRIMARY KEY (vendeur_id, 
  magasin_id));
CREATE TABLE prefixes (
  id         SERIAL NOT NULL, 
  lib       varchar(30) NOT NULL, 
  lib_court varchar(10) NOT NULL, 
  CONSTRAINT pk_prefixes 
    PRIMARY KEY (id));
CREATE TABLE statut (
  statut_id  SERIAL NOT NULL, 
  lib       varchar(20) NOT NULL, 
  PRIMARY KEY (statut_id));
CREATE TABLE societe (
  societe_id int4 NOT NULL, 
  statut_id  int4 NOT NULL, 
  capital    varchar(20) NOT NULL, 
  PRIMARY KEY (societe_id));
CREATE TABLE ent_immat (
  ent_immat_id int4 NOT NULL, 
  tribunal_rcs varchar(50) NOT NULL UNIQUE, 
  siren        varchar(9) NOT NULL UNIQUE, 
  tva_intra    varchar(20) NOT NULL UNIQUE, 
  PRIMARY KEY (ent_immat_id));
CREATE TABLE entreprise (
  entreprise_id int4 NOT NULL, 
  rs            varchar(50) NOT NULL, 
  PRIMARY KEY (entreprise_id));
CREATE TABLE organisme (
  organisme_id int4 NOT NULL, 
  name         varchar(50) NOT NULL, 
  PRIMARY KEY (organisme_id));
CREATE TABLE membre (
  membre_id int4 NOT NULL, 
  pseudo    varchar(30) NOT NULL, 
  logo      varchar(255) NOT NULL, 
  PRIMARY KEY (membre_id));
CREATE TABLE main_phone (
  actor_id int4 NOT NULL UNIQUE, 
  phone_id int2 NOT NULL, 
  PRIMARY KEY (actor_id, 
  phone_id));
CREATE TABLE main_address (
  actor_id    int4 NOT NULL UNIQUE, 
  addresse_id int2 NOT NULL, 
  PRIMARY KEY (actor_id, 
  addresse_id));
CREATE TABLE main_email (
  actor_id int4 NOT NULL UNIQUE, 
  email_id int2 NOT NULL, 
  PRIMARY KEY (actor_id, 
  email_id));
CREATE TABLE personne (
  personnne_id int4 NOT NULL, 
  prefixe      int4 NOT NULL, 
  name         varchar(50) NOT NULL, 
  firstname    varchar(50) NOT NULL, 
  CONSTRAINT persons_pk 
    PRIMARY KEY (personnne_id));
CREATE TABLE phone (
  actor_id int4 NOT NULL, 
  phone_id int2 NOT NULL, 
  lib      varchar(50) NOT NULL, 
  number   varchar(20) NOT NULL, 
  CONSTRAINT phones_pk 
    PRIMARY KEY (actor_id, 
  phone_id));
CREATE TABLE email (
  actor_id int4 NOT NULL, 
  email_id int2 NOT NULL, 
  lib      varchar(50) NOT NULL, 
  address  varchar(320) NOT NULL, 
  CONSTRAINT emails_pk 
    PRIMARY KEY (actor_id, 
  email_id));
CREATE TABLE addresse (
  actor_id    int4 NOT NULL, 
  addresse_id int2 NOT NULL, 
  lib         varchar(50) NOT NULL, 
  chez        varchar(38) DEFAULT '' NOT NULL, 
  line_1      varchar(38) NOT NULL, 
  line_2      varchar(38) DEFAULT '' NOT NULL, 
  line_3      varchar(38) DEFAULT '' NOT NULL, 
  code_postal char(5) NOT NULL, 
  ville       varchar(32) NOT NULL, 
  CONSTRAINT adresses_pk 
    PRIMARY KEY (actor_id, 
  addresse_id));
CREATE TABLE actor (
  actor_id  SERIAL NOT NULL, 
  CONSTRAINT actors_pk 
    PRIMARY KEY (actor_id));
CREATE TABLE demolition (
  magasin_id int4 NOT NULL, 
  agrement   varchar(20) NOT NULL, 
  qualicert  varchar(20) DEFAULT '' NOT NULL, 
  PRIMARY KEY (magasin_id));
ALTER TABLE phone ADD CONSTRAINT actor_phone_actor_id_fk FOREIGN KEY (actor_id) REFERENCES actor (actor_id) ON DELETE Cascade;
ALTER TABLE email ADD CONSTRAINT actor_email_actor_id_fk FOREIGN KEY (actor_id) REFERENCES actor (actor_id) ON DELETE Cascade;
ALTER TABLE addresse ADD CONSTRAINT actors_addresses__fk FOREIGN KEY (actor_id) REFERENCES actor (actor_id) ON DELETE Cascade;
ALTER TABLE main_email ADD CONSTRAINT email_main_email_actor_id_email_id_fk FOREIGN KEY (actor_id, email_id) REFERENCES email (actor_id, email_id) ON DELETE Cascade;
ALTER TABLE main_phone ADD CONSTRAINT phone_main_phone_actor_id_phone_id_fk FOREIGN KEY (actor_id, phone_id) REFERENCES phone (actor_id, phone_id) ON DELETE Cascade;
ALTER TABLE main_address ADD CONSTRAINT addresse_main_address_actor_id_addresse_id_fk FOREIGN KEY (actor_id, addresse_id) REFERENCES addresse (actor_id, addresse_id) ON DELETE Cascade;
ALTER TABLE membre ADD CONSTRAINT actor_membre_membre_id_fk FOREIGN KEY (membre_id) REFERENCES actor (actor_id) ON DELETE Cascade;
ALTER TABLE organisme ADD CONSTRAINT membre_organisme_organisme_id_fk FOREIGN KEY (organisme_id) REFERENCES membre (membre_id) ON DELETE Cascade;
ALTER TABLE personne ADD CONSTRAINT membre_personne_personnne_id_fk FOREIGN KEY (personnne_id) REFERENCES membre (membre_id) ON DELETE Cascade;
ALTER TABLE entreprise ADD CONSTRAINT membre_entreprise_entreprise_id_fk FOREIGN KEY (entreprise_id) REFERENCES membre (membre_id) ON DELETE Cascade;
ALTER TABLE ent_immat ADD CONSTRAINT entreprise_ent_immat_ent_immat_id_fk FOREIGN KEY (ent_immat_id) REFERENCES entreprise (entreprise_id) ON DELETE Cascade;
ALTER TABLE societe ADD CONSTRAINT ent_immat_societe_societe_id_fk FOREIGN KEY (societe_id) REFERENCES ent_immat (ent_immat_id) ON DELETE Cascade;
ALTER TABLE societe ADD CONSTRAINT statut_societe_statut_id_fk FOREIGN KEY (statut_id) REFERENCES statut (statut_id);
ALTER TABLE personne ADD CONSTRAINT prefixes_persons_fk FOREIGN KEY (prefixe) REFERENCES prefixes (id) ON UPDATE No action ON DELETE No action;
ALTER TABLE vendeur ADD CONSTRAINT ent_immat_vendeur_vendeur_id_fk FOREIGN KEY (vendeur_id) REFERENCES ent_immat (ent_immat_id) ON DELETE Cascade;
ALTER TABLE mra ADD CONSTRAINT entreprise_mra_mra_id_fk FOREIGN KEY (mra_id) REFERENCES entreprise (entreprise_id) ON DELETE Cascade;
ALTER TABLE mra ADD CONSTRAINT metier_mra_metier_id_fk FOREIGN KEY (metier_id) REFERENCES metier (metier_id);
ALTER TABLE magasin ADD CONSTRAINT vendeur_magasin_vendeur_id_fk FOREIGN KEY (vendeur_id) REFERENCES vendeur (vendeur_id) ON DELETE Cascade;
ALTER TABLE magasin ADD CONSTRAINT actor_magasin_magasin_id_fk FOREIGN KEY (magasin_id) REFERENCES actor (actor_id);
ALTER TABLE employe ADD CONSTRAINT personne_employe_employe_id_fk FOREIGN KEY (employe_id) REFERENCES personne (personnne_id) ON DELETE Cascade;
ALTER TABLE employe ADD CONSTRAINT entreprise_employe_entreprise_id_fk FOREIGN KEY (entreprise_id) REFERENCES entreprise (entreprise_id);
ALTER TABLE responsable ADD CONSTRAINT employe_responsable_employe_id_fk FOREIGN KEY (employe_id) REFERENCES employe (employe_id) ON DELETE Cascade;
ALTER TABLE employe_mag ADD CONSTRAINT employe_employe_mag_entreprise_id_employe_id_fk FOREIGN KEY (entreprise_id, employe_id) REFERENCES employe (entreprise_id, employe_id) ON DELETE Cascade;
ALTER TABLE employe_mag ADD CONSTRAINT magasin_employe_mag_entreprise_id_magasin_id_fk FOREIGN KEY (entreprise_id, magasin_id) REFERENCES magasin (vendeur_id, magasin_id) ON DELETE Cascade;
ALTER TABLE directeur ADD CONSTRAINT employe_mag_directeur_entreprise_id_employe_id_magasin_id_fk FOREIGN KEY (entreprise_id, employe_id, magasin_id) REFERENCES employe_mag (entreprise_id, employe_id, magasin_id) ON DELETE Cascade;
ALTER TABLE demolition ADD CONSTRAINT magasin_demolition_magasin_id_fk FOREIGN KEY (magasin_id) REFERENCES magasin (magasin_id) ON DELETE Cascade;
ALTER TABLE personne ADD CONSTRAINT actors_persons_fk FOREIGN KEY (personnne_id) REFERENCES actor (actor_id) ON UPDATE No action ON DELETE Cascade;
CREATE INDEX personne 
  ON personne (name, firstname);
CREATE INDEX addresse 
  ON addresse (code_postal);

Vous remarquerez que quasiment toutes les FK sont en ON DELETE CASCADE. Bien entendu, toute table en dehors de ce schéma qui référencerait un membre ou un magasin serait en RESTRICT.

Vos remarques sont vivement attendues

Bon samedi,
François

[frm013]

Petite parenthèse au sujet de outils E/R...

Visual Paradigm, n'échappe pas à la règle, beaucoup de bonnes choses :

Paramétrage des noms de clés auto, options de présentation nombreuses, de bonnes astuces d'utilisation qui gomment ce que j'avais pu prendre pour une usine à clicks.

Mais, reverse catastrophique, du moins concernant PostgreSql :

La prise en compte des types spécifiques comme SERIAL provoquent des horreurs, en effet, ceux-ci ne sont pas gérés dans leurs spécificité. Donc l'outil propage par exemple le type SERIAL dans les clés étrangères !!

A la mise à jour d'un schéma il génère les séquences en plus des SERIALs, DROP les procédures avant d'avoir supprimé les TRIGGERs, pour les re-créer à nouveau. Ingérable sur une base en prod, je crois bien que la synchronisation avec une base soit une douce illusion, peut-être les outils de bas commerciales en sont capables.

Après plusieurs heures dans la doc et dans les options, je n'ai rien trouvé pour corriger cela... bien dommage.

Fin du coup de gueule

[fsmrel]

Bonsoir François,

La prise en compte des types spécifiques comme SERIAL provoquent des horreurs, en effet, ceux-ci ne sont pas gérés dans leurs spécificité. Donc l'outil propage par exemple le type SERIAL dans les clés étrangères !!

A moins que SERIAL ne fasse partie des types prédéfinis par la norme SQL (ce dont je doute), on ne peut pas donner tort à l’outil, qui considère donc que le type SERIAL a été défini par vous, or c’est la moindre des choses que les colonnes des clés étrangères soient du même type que celui des colonnes référencées pour que les comparaisons soient pertinentes.


Concernant votre diagramme et le code SQL :

Selon le diagramme, un acteur a au plus une adresse postale, une adresse de courriel et un téléphone alors que selon SQL c’est plusieurs.


Table MAGASIN : il manque un CASCADE dans la définition de la contrainte actor_magasin_magasin_id_fk :

CONSTRAINT actor_magasin_magasin_id_fk FOREIGN KEY (magasin_id) REFERENCES actor (actor_id)

Vous confirmez le CASCADE « à la hussarde » ci-dessous ?

vendeur_magasin_vendeur_id_fk FOREIGN KEY (vendeur_id) REFERENCES vendeur (vendeur_id) ON DELETE Cascade

Table PERSONNE : contrainte actors_persons_fk en trop :

CONSTRAINT actors_persons_fk FOREIGN KEY (personnne_id) REFERENCES actor (actor_id) ON UPDATE No action ON DELETE Cascade

Table EMPLOYE_MAG :
C’est encore à la hussarde, mais comme dit l’autre, c’est vous qui voyez... :

CONSTRAINT magasin_employe_mag_entreprise_id_magasin_id_fk FOREIGN KEY (entreprise_id, magasin_id) REFERENCES magasin (vendeur_id, magasin_id) ON DELETE Cascade

Pour le reste du code SQL, pas de problème.

[frm013]

Bonjour François,

A moins que SERIAL ne fasse partie des types prédéfinis par la norme SQL (ce dont je doute), on ne peut pas donner tort à l’outil, qui considère donc que le type SERIAL a été défini par vous

Effectivement SERIAL ne fait pas parti du standard de même qu'IDENTITY de SQL Server. Mais, puisque l'outil prétend supporter PostgreSql il devrait le prendre en compte, d'ailleurs, à la génération de code, quand on lui demande une colonne auto-incrémentée, il génère bien un type SERIAL pour postgresql, donc il "sait" bien ce que c'est Il propose d'ailleurs identity ou sequence comme type d'implémentation de ce type de colonne.

or c’est la moindre des choses que les colonnes des clés étrangères soient du même type que celui des colonnes référencées pour que les comparaisons soient pertinentes.

Entièrement d'accord !

Selon le diagramme, un acteur a au plus une adresse postale, une adresse de courriel et un téléphone alors que selon SQL c’est plusieurs.

Humm, je ne comprends pas, sur le diagramme ADDRESSE possède bien {actor_id, addresse_id} comme clé primaire...

Table MAGASIN : il manque un CASCADE dans la définition de la contrainte actor_magasin_magasin_id_fk

Je pensais que ce n'était pas nécessaire puisque à la disparition d'une occurrence de ACTOR les occurrences de MEMBRE, ENTREPRISE et VENDEUR disparaissent, donc celles de magasin aussi par le CASCADE de la FK vendeur_magasin_vendeur_id_fk.

Vous confirmez le CASCADE « à la hussarde » ci-dessous ?

Table EMPLOYE_MAG :
C’est encore à la hussarde, mais comme dit l’autre, c’est vous qui voyez... :
CONSTRAINT magasin_employe_mag_entreprise_id_magasin_id_fk FOREIGN KEY (entreprise_id, magasin_id) REFERENCES magasin (vendeur_id, magasin_id) ON DELETE Cascade

Heu... oui c'est mal?

Bien entendu, si un membre vendeur possède des commandes il sera impossible de le supprimer.

Table PERSONNE : contrainte actors_persons_fk en trop

Wow ! Contrainte fantôme en provenance du reverse, je croyais l'avoir supprimée. Il faut que je vérifie tout. Je viens d'en trouver une autre qui référence une colonne supprimée depuis ! Ce n'était pas dans le code produit par contre... bizarre. Je commence à ne pas avoir confiance.

En tous cas, merci pour votre correction et le temps que vous y avez consacré.

Bon dimanche,
François

[frm013]

Pour revenir sur SQL Power Architect, qui ne savait pas créer une FK référençant autre chose qu'une PK.

J'ai réussi à implémenter ceci concernant EMPLOYE, EMPLOYE_MAG, MAGASIN et DIRECTEUR:

Code sql :

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
 
CREATE TABLE magasin (
                id_vendeur INTEGER NOT NULL,
                id_magasin INTEGER NOT NULL,
                name VARCHAR(50) NOT NULL,
                logo VARCHAR(255) DEFAULT '',
                siret VARCHAR(14) NOT NULL,
                CONSTRAINT magasin_pk PRIMARY KEY (id_vendeur, id_magasin)
);
 
CREATE UNIQUE INDEX magasin_ak
 ON magasin
 ( id_magasin );
 
CREATE TABLE employe (
                id_entreprise INTEGER NOT NULL,
                id_employe INTEGER NOT NULL,
                CONSTRAINT employe_pk PRIMARY KEY (id_entreprise, id_employe)
);
 
CREATE UNIQUE INDEX employe_ak
 ON employe
 ( id_employe );
 
CREATE TABLE employe_mag (
                id_entreprise INTEGER NOT NULL,
                id_employe INTEGER NOT NULL,
                id_magasin INTEGER NOT NULL,
                CONSTRAINT employe_mag_pk PRIMARY KEY (id_entreprise, id_employe, id_magasin)
);
 
CREATE UNIQUE INDEX employe_mag_ak
 ON employe_mag
 ( id_employe, id_magasin );
 
CREATE TABLE directeur (
                id_entreprise INTEGER NOT NULL,
                id_employe INTEGER NOT NULL,
                id_magasin INTEGER NOT NULL,
                CONSTRAINT directeur_pk PRIMARY KEY (id_entreprise, id_employe)
);
 
ALTER TABLE employe_mag ADD CONSTRAINT magasin_employe_mag_fk
FOREIGN KEY (id_vendeur, id_magasin)
REFERENCES magasin ( id_vendeur, id_magasin)
ON DELETE CASCADE
ON UPDATE NO ACTION
NOT DEFERRABLE;
 
ALTER TABLE employe_mag ADD CONSTRAINT employe_employe_mag_fk
FOREIGN KEY (id_entreprise, id_employe)
REFERENCES employe (id_entreprise, id_employe)
ON DELETE CASCADE
ON UPDATE NO ACTION
NOT DEFERRABLE;
 
ALTER TABLE directeur ADD CONSTRAINT employe_mag_directeur_fk
FOREIGN KEY (id_entreprise, id_employe, id_magasin)
REFERENCES employe_mag (id_entreprise, id_employe, id_magasin)
ON DELETE CASCADE
ON UPDATE NO ACTION
NOT DEFERRABLE;

Je pense que cela revient au même.
Qu'en pensez vous? si vous avez le temps bien sûr.

[fsmrel]

Bonjour François,

puisque l'outil prétend supporter PostgreSql il devrait le prendre en compte, d'ailleurs, à la génération de code, quand on lui demande une colonne auto-incrémentée, il génère bien un type SERIAL pour postgresql, donc il "sait" bien ce que c'est.

Dans ces conditions, il faut gronder celui qui a créé l’outil et lui demander de rectifier le tir...

Humm, je ne comprends pas, sur le diagramme ADDRESSE possède bien {actor_id, addresse_id} comme clé primaire...

Ce que je voulais dire, c’est que les cardinalités de votre diagramme disent autre chose :

[ACTOR]--|------------------O-|--[ADRESSE]

Je pensais que ce n'était pas nécessaire puisque à la disparition d'une occurrence de ACTOR les occurrences de MEMBRE, ENTREPRISE et VENDEUR disparaissent, donc celles de magasin aussi par le CASCADE de la FK vendeur_magasin_vendeur_id_fk.

Quel que soit son type, membre ou magasin, disons que d’un point de vue sémantique, si on supprime un acteur on le supprime en tant que tel, « en entier ». Vu sous cet angle, il est donc logique que l’on code CASCADE pour la contrainte actor_magasin_magasin_id_fk. Toujours du point de vue sémantique, que la suppression d’un vendeur entraîne celles de ses magasins, ça se discute, tout dépend de la règle qu’on aura obtenue auprès de l’utilisateur.

Du point de vue technique, en l’absence de CASCADE, tout dépend de la façon dont a été ficelée la version x du SGBD y par comparaison avec la version z de la norme SQL. Par exemple, passer à DB2 obligerait à ajouter le CASCADE absent. Concernant PostgreSQL, je ne l’ai pas installé, donc je ne me prononce pas, mais il faudra probablement vous assurer que INITIALLY DEFERRED est actif (ça n’est pas l’option par défaut). A titre indicatif, MS SQL Server 2005 exige que lorsqu’on supprime un acteur (au niveau de la table ACTOR), il n’y ait pas CASCADE des deux côtés (alors que DB2 l’accepte et même l’exigeait jusqu’à la V5), mais si alors si les acteurs 1 et 2 sont respectivement un vendeur et un magasin, pour MS SQL Server, grâce à CASCADE supprimer l’acteur 1 provoque la suppression du magasin 2 si celui-ci est rattaché à ce vendeur, tandis que la suppression de l’acteur 2 est refusée (MS SQL Server ne reconnaît que INITIALLY DEFERRED, c'est implicite) : il y a là une dissymétrie mal venue... Quoi qu’il en soit, à vous de vérifier quelles précautions sont à rendre avec PostgreSQL, mais à mon avis le plus simple est sans doute de coder CASCADE pour la contrainte vendeur_magasin_vendeur_id_fk...

Heu... oui c'est mal?

Tout dépend en fait de ce qu’en pense l’utilisateur, c’est à lui de vous fournir la règle.

J 'ai réussi à implémenter ceci concernant EMPLOYE, EMPLOYE_MAG, MAGASIN et DIRECTEUR

Hum... On court-circuite un concept de niveau logique (clé alternative) par le biais d’un index qui est du niveau physique. Bien que les index soient sous le capot, avec une mauvaise foi manifeste, j’irai presque jusqu’à dire que du point de vue du Modèle Relationnel de Données il y a viol de la 9e des 12 règles de Codd (indépendance physique) : le défi est que les objets du niveau physique ne soient pas des palliatifs aux manques du niveau logique. Maintenant, si vous préférez SQL Power Architect, pourquoi pas ? Votre instruction CREATE UNIQUE INDEX est en effet manifestement conforme vu des autres SGBD. A tout péché, miséricorde...


Et Joyeux Noël !

[frm013]

Bonjour François,

Ce que je voulais dire, c’est que les cardinalités de votre diagramme disent autre chose :
[ACTOR]--|------------------O-|--[ADRESSE]

Oops!! Je devais être mal réveillé! Effectivement.

Concernant le ON DELETE CASCADE entre ACTOR et MAGASIN, j'ai longuement hésité.

Effectivement, d'un point de vu sémantique ce serait logique, je n'ai pas encore fait l'essai pratique, mais je m'étais posé ce genre de questions sur la réaction de PostgreSql. Si ça passe, je ferait ainsi car effectivement je préfère comme vous cette logique.

Tout dépend en fait de ce qu’en pense l’utilisateur, c’est à lui de vous fournir la règle.

L'utilisateur c'est moi

Maintenant, si vous préférez SQL Power Architect, pourquoi pas ? Votre instruction CREATE UNIQUE INDEX est en effet manifestement conforme vu des autres SGBD. A tout péché, miséricorde...

C'est pas que je préfère, mais le fait que Visual Paradigm tolère qu'on supprime une colonne impliquée dans une contrainte de clé étrangère me dérange pas mal, que va-t-il tolérer encore ? Autant ne pas générer de SQL si je ne peut me fier à l'outil.

C'est vraiment dur de trouver un outil qui marche...

Bien entendu, ma manipulation du concept logique me dérange aussi, c'est pourquoi je vous soumettais ce code.

Joyeux Noël à vous aussi,
François

[frm013]

Bonsoir François,

Me voici de retour après ces fêtes qui, je l'espère, furent bonnes ! Bonne année! il est encore temps je crois...

Donc voici la partie commandes et livraisons. c'est assez copieux ce coup-ci, j'ai avancé le plus possible pour vous embêter le moins.

Règles de gestions:

Commandes :

RGC01 : Un membre peut passer commande de 1 ou plusieurs pièce(s).
RGC02 : Une commande concerne un et un seul magasin.
RGC03 : Chaque commande peut-être faire l’objet de plusieurs livraisons.
RGC04 : De même chaque item d’une commande peut-être livré en plusieurs fois.

Pour chaque livraison, le membre client indiquera la date de réception de celle-ci.

Retours :

Après livraison, un membre peut demander de retourner une ou plusieurs pièces, soit pour le délai de rétractation légal, soit à cause d’une anomalie dans la livraison.

RGC05 : Une commande peut faire l’objet de plusieurs demandes de retour,
RGC06 : Chaque item livré peut faire l’objet de plusieurs anomalies de livraison ( 1 cassé, 1 erreur de référence pour qté 2, par exemple ).
RGC07 : Chaque item livré peut faire l’objet de plusieurs demandes de retour pour des motifs différents. Pas nécessairement à cause d’une anomalie.

Un vendeur peut accepter ou refuser un retour à propos d’une pièce en le motivant.

RGC08 : Un item de demande de retour peut donc être accepter ou refuser pour une certaine quantité. il peut donc faire l’objet de plusieurs refus et de plusieurs acceptations.

Le vendeur validera la réception des retours et constatera d’éventuelles anomalies de retour.
RGC09 : Un item de retour peut donc comporter zéro ou plusieurs anomalies.

Le diagramme de classes :



Vous aurez surement beaucoup de remarques

J'ai une question qui concerne la mémorisation des adresses de facturation et de livraison, quelle est la bonne pratique sachant que l'utilisateur pourra changer l'une d'elles pendant la durée de vie du système ?

A vous lire,
François

[fsmrel]

Bonjour François,


Je note que tout membre P peut passer une commande C auprès du magasin M, y-compris le vendeur V à qui appartient M.
Vous ne gérez pas l’historique des taux de TVA des pièces ?


En ce qui concerne les changements d’adresse, j’utilise pour ma part la technique « depuis/durant », qui consiste à isoler le passé du présent.

Je passe au niveau MLD.

L’adresse de livraison actuelle est définie par l’attribut AdresseLivraisonDepuis (table COMMANDE). Elle a pu connaître plusieurs changements que la table LIVRAISON_HISTO permet de suivre. « Durant » est synonyme de période (date de début et date de fin). La clé de cette table est la paire {Commande No, AdresseLivraisonDurant}. Ce qui vaut pour les livraisons vaut bien sûr pour les facturations, mutatis mutandis comme dit l’autre qui n’est pas la moitié d’un.

[frm013]

Bonjour François,

Tout d'abord, merci pour vos remarques... je m'étonne qu'il y en ait si peu.

Je note que tout membre P peut passer une commande C auprès du magasin M, y-compris le vendeur V à qui appartient M.

En gros, un membre vendeur, peut se commander a lui-même!! Effectivement, ca parait stupide, mais en l'état c'est possible

Il faudra effectivement un trigger pour éviter cela. Sinon je ne vois pas comment... quoi que : la clé de magasin est {vendeur_id, magasin_id}, une contrainte CHECK ( membre_id <> vendeur_id ) devrait suffir.

Vous ne gérez pas l’historique des taux de TVA des pièces ?

Je compte le faire.
Jusqu'à présent, pour les commandes, je me contentais de prendre un instantané des données à l'insertion d'une ligne de cde ( prix, taux de tva, designation ) et pour la commande, adresse de livraison et de facturation.
Pour la tva, c'était un peu du sport en cas de changement, la mise à jour devant se faire à minuit. Donc l'idée d'une date d'application me plait bien.

Quelque chose comme ça :



Concernant les adresses, je ne comprends rien à votre schéma
Je vous avoue que dans votre cours, à cet endroit j'avais déjà décroché

Une commande pourrait avoir plusieurs versions des adresses?
Que mémorise AdresseLivraison dans COMMANDE et AdresseLivraison dans LIVRAISON_HISTO ? Je suis perdu là.

Merci d'avance pour vos lumières,
François

[fsmrel]

Bonjour François,

Je vous avais mis en priorité faible, je n’aurais pas dû...

J'ai une question qui concerne la mémorisation des adresses de facturation et de livraison, quelle est la bonne pratique sachant que l'utilisateur pourra changer l'une d'elles pendant la durée de vie du système ?

Dans le diagramme que je vous ai présenté j’ai oublié qu’un client a au plus une adresse (classe ADRESSE en relation avec ACTOR) : il faudrait déjà qu’on établisse la relation entre ADRESSE et l’adresse de livraison ainsi que l’adresse de facturation, sans oublier que selon votre diagramme, une commande détermine fonctionnellement un client, qui est un membre, donc un acteur, lequel détermine une adresse. Il faudra lever un doute (un client n'a-t-il vraiment qu'une adresse ?) car il est fort probable que la table COMMANDE viole la 3NF. En attendant, oublions (provisoirement) les tables LIVRAISON_HISTO et FACTURATION_HISTO.

Je regarde la suite de votre dernier message.

[frm013]

Bonjour François,

Je vous avais mis en priorité faible, je n’aurais pas dû...

Et vous avez bien fait, cette partie de la base sera mise en oeuvre plus tard.

j’ai oublié qu’un client a au plus une adresse

C'est mon diagramme qui était faux.

Selon le diagramme, un acteur a au plus une adresse postale, une adresse de courriel et un téléphone alors que selon SQL c’est plusieurs.

Un client peut avoir plusieurs adresses.

J'ai laissé la partie concernant les adresses de facturation et de livraison volontairement vague pour en parler avec vous.
Dans mon esprit, on détermine l'adresse de facturation comme étant l'adresse principale du client ( entité-type MAIN_ADRESSE ), pour la livraison, si pas de choix différent de la part du client on livre à l'adresse de facturation.

A vous lire donc,
François

[fsmrel]

Dans mon esprit, on détermine l'adresse de facturation comme étant l'adresse principale du client (entité-type MAIN_ADRESSE), pour la livraison, si pas de choix différent de la part du client on livre à l'adresse de facturation.

Mais, loi de l’em... maximum oblige ! il se trouve que le client Raoul (décidemment...) a une adresse de facturation (la péniche) différente de l’adresse de livraison (le clandé à Tomate). Le diagramme ci-dessous, repris de votre diagramme général des acteurs nous met en porte-à-faux au vu des cardinalités, mais pas de la clé primaire de la table ADRESSE...) :




En conséquence, il serait préférable d’ajuster la cardinalité max portée par la patte connectant ACTOR et ADRESSE. Par ailleurs, comme Raoul n’a qu’une adresse principale (la péniche ^^), si la table MAIN_ADDRESS a pour clé étrangère {actor_id, adresse_id}, elle a pour clé primaire {actor_id} seulement :



A votre avis ?

[fsmrel]

Concernant l’historisation de la TVA, reprenons votre diagramme :



Mais le présent (la TVA en cours) y est tricoté avec le passé. Même si l’on s’en sort au plan de la programmation, d’expérience, je trouve bien plus raisonnable de détricoter et suivre ce qu’ont écrit les meilleurs spécialistes du sujet, Date, Darwen et Lorentzos (qui ont fait plier ceux qui font la norme SQL), et de séparer le présent du passé :




Maintenant, si vous avez des arguments pour battre en brèche cette représentation, n’hésitez pas à les avancer, c’est comme cela qu’on fait progresser la modélisation ^^.

[frm013]

Concernant les membres,

Dans mon diagramme ERD fourni ici, mais faux, l'entité-type MAIN-ADRESSE à pour clé primaire {actor_id, adresse_id} avec un contrainte d'unicité sur actor_id (copie d'écran du forum):

Donc un acteur n'a qu'une seule adresse principale.

Comme je vous disait, mon diagramme comporte une erreur malgré que le code SQL produit soit correct.

Citation:

Humm, je ne comprends pas, sur le diagramme ADDRESSE possède bien {actor_id, addresse_id} comme clé primaire...

Ce que je voulais dire, c’est que les cardinalités de votre diagramme disent autre chose :
[ACTOR]--|------------------O-|--[ADRESSE]

Oops!! Je devais être mal réveillé! Effectivement.

La correction que vous proposez est déjà faite, mais je ne l'avait pas re-publié pour ne pas alourdir le fil de discussion inutilement.

Voici cette partie du diagramme en l'état actuel :


A suivre...

[frm013]

Concernant la tva, aucune objections.

Ceci-dit, ces principes d'historisation me font tiquer quand à la manière de récupérer la bonne info en production.
Comment faites vous? Avez vous un exemple de requête SQL simple?
Par exemple la jointure entre un item_cde et son taux de tva en tenant compte de la date de la commande. En effet, on a le cas ou le taux est déjà présent dans l'historique, et le cas ou il ne l'est pas.

[fsmrel]

Concernant la TVA.

Voici un jeu d’essai :

TABLE TVA (TVA à ce jour)

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE TVA 
(
  TvaId              INT             NOT NULL,
  TvaLibelle         VARCHAR(45)     NOT NULL,
  TvaApplDebut       DATETIME        NOT NULL,
  TvaTaux            DECIMAL(5,2)    NOT NULL,
  CONSTRAINT TVA_PK PRIMARY KEY (TvaId)
) ;
INSERT INTO TVA (TvaId, TvaLibelle, TvaApplDebut, TvaTaux) VALUES (1, 'La TVA réduite...', '2014-01-01', 7.0)
INSERT INTO TVA (TvaId, TvaLibelle, TvaApplDebut, TvaTaux) VALUES (2, 'La TVA pour moi !', '2014-01-01', 20.0)
 
SELECT '' AS TVA,* FROM TVA ;

TABLE TVA_HISTO

Code SQL :

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
CREATE TABLE TVA_HISTO 
(
  TvaId              INT             NOT NULL,
  TvaApplDebut       DATETIME        NOT NULL,
  TvaApplFin         DATETIME        NOT NULL,
  TvaTaux            DECIMAL(5,2)    NOT NULL,
  CONSTRAINT TVA_HISTO_PK PRIMARY KEY (TvaId, TvaApplDebut),
  CONSTRAINT TVA_HISTO_TVA_FK FOREIGN KEY (TvaId)
    REFERENCES TVA
, CONSTRAINT TVA_HISTO_CHK01 CHECK (TvaApplFin > TvaApplDebut) 
) ;
INSERT INTO TVA_HISTO (TvaId, TvaApplDebut, TvaApplFin, TvaTaux) VALUES (1, '1954-01-01', '1990-12-20', 5)
INSERT INTO TVA_HISTO (TvaId, TvaApplDebut, TvaApplFin, TvaTaux) VALUES (1, '1990-12-21', '2013-12-31', 5.5)
INSERT INTO TVA_HISTO (TvaId, TvaApplDebut, TvaApplFin, TvaTaux) VALUES (2, '1990-01-01', '2013-12-31', 19.6)
 
SELECT '' AS TVA_HISTO,* FROM TVA_HISTO ;

TABLE PIECE

Code SQL :

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 PIECE 
(
  PieceId            INT             NOT NULL,
  PieceDesignation   VARCHAR(45)     NOT NULL,
  PiecePrixHT        INT             NOT NULL,
  TvaId              INT             NOT NULL,
  CONSTRAINT PIECE_PK PRIMARY KEY (PieceId),
  CONSTRAINT PIECE_TVA_FK FOREIGN KEY (TvaId)
    REFERENCES TVA
) ;
INSERT INTO PIECE (PieceId, TvaId, PieceDesignation, PiecePrixHT) VALUES (1, 1, 'pièce 1', 100) ;
INSERT INTO PIECE (PieceId, TvaId, PieceDesignation, PiecePrixHT) VALUES (2, 2, 'pièce 2', 200) ;
INSERT INTO PIECE (PieceId, TvaId, PieceDesignation, PiecePrixHT) VALUES (3, 1, 'pièce 3', 300) ;
 
SELECT '' AS PIECE,* FROM PIECE ;

TABLE ACTOR

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
TABLE ACTOR
CREATE TABLE ACTOR 
(
  ActorId        INT               NOT NULL,
  CONSTRAINT ACTOR_PK PRIMARY KEY (ActorId)
) ;
INSERT INTO ACTOR (ActorId) VALUES (1) ;
INSERT INTO ACTOR (ActorId) VALUES (2) ;
INSERT INTO ACTOR (ActorId) VALUES (3) ;
 
SELECT '' AS ACTOR,* FROM ACTOR ;

TABLE COMMANDE

Code SQL :

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
CREATE TABLE COMMANDE 
(
  ActorId            INT             NOT NULL,
  CommandeId         INT             NOT NULL,
  CommandeDate       DATETIME        NOT NULL,
  CommandeNo         VARCHAR(10)     NOT NULL,
  CONSTRAINT COMMANDE_PK PRIMARY KEY (ActorId, CommandeId),
  CONSTRAINT COMMANDE_AK UNIQUE (CommandeNo),
  CONSTRAINT COMMANDE_ACTOR_FK FOREIGN KEY (ActorId)
    REFERENCES ACTOR
) ;
INSERT INTO COMMANDE (ActorId, CommandeId, CommandeDate, CommandeNo) VALUES (1, 1, '1960-10-10', '1960-00021') ;
INSERT INTO COMMANDE (ActorId, CommandeId, CommandeDate, CommandeNo) VALUES (1, 2, '2000-11-15', '2000-00150') ;
INSERT INTO COMMANDE (ActorId, CommandeId, CommandeDate, CommandeNo) VALUES (1, 3, '2014-01-28', '2014-00007') ;
 
SELECT '' AS COMMANDE,* FROM COMMANDE ;

TABLE ITEM_CDE

Code SQL :

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
CREATE TABLE ITEM_CDE (
  ActorId            INT             NOT NULL,
  CommandeId         INT             NOT NULL,
  PieceId            INT             NOT NULL,
  Quantite           INT             NOT NULL,
  CONSTRAINT ITEM_CDE_PK PRIMARY KEY (ActorId, CommandeId, PieceId),
  CONSTRAINT ITEM_CDE_PIECE_FK FOREIGN KEY (PieceId)
    REFERENCES PIECE,
  CONSTRAINT ITEM_CDE_COMMANDE_FK FOREIGN KEY (ActorId , CommandeId)
    REFERENCES COMMANDE
) ;
INSERT INTO ITEM_CDE (ActorId, CommandeId, PieceId, Quantite) VALUES (1, 1, 1, 25) ;
INSERT INTO ITEM_CDE (ActorId, CommandeId, PieceId, Quantite) VALUES (1, 2, 2, 30) ;
INSERT INTO ITEM_CDE (ActorId, CommandeId, PieceId, Quantite) VALUES (1, 2, 1, 50) ;
INSERT INTO ITEM_CDE (ActorId, CommandeId, PieceId, Quantite) VALUES (1, 3, 1, 75) ;
 
SELECT '' AS ITEM_CDE,* FROM ITEM_CDE ;

Bien que ce ne soit pas nécessaire, je définis une vue permettant de regrouper les taux de TVA en vigueur et historisés :

Code SQL :

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

1
2
3
4
5
6
7
CREATE VIEW TVA_ALL (TvaId, TvaApplDebut, TvaApplFin, TvaTaux) 
    AS 
       SELECT TvaId, TvaApplDebut, '9999-12-31', TvaTaux
       FROM   TVA
    UNION ALL
       SELECT TvaId, TvaApplDebut, TvaApplFin, TvaTaux
       FROM   TVA_HISTO ;

Pour récupérer les taux successifs appliqués à l’ensemble des commandes de l'acteur 1 :

Code SQL :

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

1
2
3
4
5
6
SELECT CommandeNo, CommandeDate, PieceDesignation, Quantite, TvaTaux
FROM   ITEM_CDE AS x JOIN COMMANDE AS y ON x.ActorId = y.ActorId AND x.CommandeId = y.CommandeId 
                     JOIN PIECE AS z ON x.PieceId = z.PieceId
                     JOIN TVA_ALL AS t ON z.TvaId = t.TvaId
WHERE x.ActorId = 1 
  AND CommandeDate BETWEEN t.TvaApplDebut AND  t.TvaApplFin ;

=>

Code :

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

1
2
3
4
5
6
CommandeNo    CommandeDate    PieceDesignation    Quantite    TvaTaux

1960-00021    1960-10-10      pièce 1                   25      5.00
2000-00150    2000-11-15      pièce 1                   50      5.50
2000-00150    2000-11-15      pièce 2                   30     19.60
2014-00007    2014-01-28      pièce 1                   75      7.00