Discussion :

[fsmrel]

Bonsoir François,

Au passage, une question, en pratique, lors d'un héritage, on garde le nom de la clé primaire mère ou on la change pour lui donner un nom relatif à la table dans le modèle physique ? Ex : id_vendeur au lieu de id_entreprise.

Règle générale ès matière : A chacun ses manies...

=>

Faites selon votre bon plaisir !



Question :

Deux vendeurs peuvent-ils avoir même tribunal_rcs ? num_rcs ? siren ? tva_intra ?

Dans la négative avez-vous défini des clés alternatives ?


Je viens de survoler vos échanges avec Camille, ça y va à la manœuvre, mais je n’ai malheureusement pas le temps d’entrer dans la danse...

[frm013]

Bonsoir,

Dans la négative avez-vous défini des clés alternatives ?

C'est bien mon intention : num_rcs, siren et tva_intra doivent être uniques en effet. Je passerait à PowerArchitect pour le modèle définitif, car une syncro est possible avec Postgresql, pas avec MysqlWorkbench.

Je viens de survoler vos échanges avec Camille, ça y va à la manœuvre, mais je n’ai malheureusement pas le temps d’entrer dans la danse

Je n'ai malheureusement pas votre expérience et votre pédagogie pour expliquer les choses comme je les ressent... J'ai surtout travaillé seul sur des projets de longue haleine, cela doit être mon 5eme modèle sérieux.

J'espère que nous n'aurons pas à vous déranger.

Bon, je vais continuer à travailler sur la partie des commandes.

A très bientôt,
et encore merci !
François

[fsmrel]

Je passerai à PowerArchitect pour le modèle définitif, car une syncro est possible avec Postgresql, pas avec MysqlWorkbench.

Moi pas compris... Qu'entendez-vous par synchro avec le SGBD ?

[frm013]

Je peux modifier mon modèle et appliquer les modifications à la BDD, le modèle reste donc synchronisé avec la BDD.

[frm013]

Bonjour François,

Désolé pour le délai, quelques impératifs mon gardé éloigné d'ici ce mois ci.

J'ai travaillé sur les commandes, mais je voulais d'abord vérifier le modèle physique des acteurs avant d'aller plus loin.

J'en ai profité pour revoir votre article ainsi que l'implémentation de l'identification relative et me voici confronté à un problème lors du passage au modèle physique:



Si j'ai bien compris le principe des sur-clés, la clé primaire de EMPLOYE est une sur-clé!!
En effet, id_employe permet déjà d'identifier de façon unique une occurrence d'un employé, {id_employe, id_vendeur} est donc réductible.

Comment fait-on dans ce cas?
En effet l'identification relative nous aiderait bien à s'assurer que le directeur d'un MAGASIN travaille bien pour le VENDEUR qui possède celui-ci.

Merci d'avance de vos lumières... je sèche.

A vous lire,
François

[fsmrel]

Bonjour François,


Avant de descendre d’un cran, je suppose que d’un point de vue sémantique on peut interpréter ainsi votre modèle :

(1) Une personne (sous-entendu physique, puisqu’une personne a un prénom) est un membre.

(2) Une entreprise (personne morale) est un membre.

(3) On peut inférer qu’un membre ne peut pas être à la fois personne physique et personne morale. C’est bien cela ?

(4) Une entreprise peut être un vendeur et un vendeur est (toujours) une entreprise.

(5) Un vendeur peut être une société et une société est (toujours) un vendeur.

(6) Une personne peut être un employé et un employé est (toujours) une personne.

So far so good.

Maintenant, le rattachement de l’entité-type EMPLOYE à l’entité-type VENDEUR signifie que chaque employé dépend d’un vendeur. Si donc un directeur est un employé, il ne peut y avoir de directeur qui ne soit pas rattaché à un vendeur.

Dans ce qui précède, on ne dit pas qu’un employé est un composant d’un vendeur, sémantiquement ça n’aurait pas de sens, autrement dit il n’y a pas d’identification relative qui tienne et si l’on descend au niveau MySQL Workbench, le modèle est plutôt celui-ci :



Où clairement chaque employé est rattaché à au moins et au plus un vendeur.

[frm013]

Je me doutais que reprendre l'analyse après un mois ne serait pas facile.

Lors d'un précédent message, vous me disiez:
http://www.developpez.net/forums/d13...s/#post7539186

Ce qui fait que l’association donne lieu à une table au stade SQL, sinon c’est le bonhomme Null en liberté (et à forte dose côté EMPLOYE, tout le monde n’est pas gérant). Appelons par exemple DIRECTION cette table. l’attribut EntrepriseId doit être hérité des deux côtés, donc EMPLOYE est à identifier relativement à ENTREPRISE, tout en conservant le singleton {EmployeId} comme clé alternative...

Nous avions bien parlé d'identifier MAGASIN relativement à ENTREPRISE et EMPLOYE relativement à VENDEUR afin de résoudre la contrainte de chemin entre EMPLOYE et MAGASIN afin de ne pas pouvoir se trouver avec employe1_vendeur1 travaillant dans magasin_vendeur2.
Ou j'ai mal compris ?

Si je complète le schéma avec la table DIRECTION ( J'ai ajouté une table EMPLOYE_MAG car autant pouvoir identifier tout le personnel d'un magasin ), on obtient ce schéma :



Si je ne m'abuse, en cas de disparition d'un vendeur, ses magasins et ses employés doivent disparaitre non?

Je vous avoue que je suis en peu perdu là...

[frm013]

Bonsoir François,

A part cette question d'identification relative... ( Au pire, pour une fois, j'utiliserais un Trigger )

En travaillant sur le modèle logique, une idée m'est venue concernant la modélisant des entreprises et notre réflexion à ce sujet à partir de ce post : http://www.developpez.net/forums/d13...s/#post7531979

En effet, que la table SOCIETE hérite de VENDEUR m'a fait beaucoup tiquer ( si vous me permettez de reprendre votre expression ).
J'ai relu vos intervention et les aient intégrées avec cette idée.

Je vous disait que pour des raisons de souplesse pour l'utilisateur, seules les ENTREPRISE étant VENDEUR devaient obligatoirement saisir leur infos d'immatriculation.

On a donc :

Des entreprises sans immatriculation, des entreprises avec immatriculation et celles immatriculées en société.
Sur le précédent modèle, j'ai raisonné ( à tort peut-être ) en considérant une exclusion dans l'héritage, mais est-ce utile?
Ne peut-on pas dire qu'une instance d'ENTREPRISE pourra être sans ou avec immatriculation et / ou en société?

J'ai fait ce diagramme de classes:



Un VENDEUR est donc un type d'ENTREPRISE avec immatriculation ( ENT_IMMAT ) et éventuellement en société selon l'instance de ENT_IMMAT est une instance de SOCIETE ou non.

J'ai inclus votre proposition d'une classe ORGANISME.

Au final, n'importe quel membre qui est une entreprise à le choix de saisir ses infos juridiques ou pas, les vendeurs, quand à eux, y sont tenus.

Le modèle physique est, je pense, assez parlant:



pour récupérer les infos d'une ENTREPRISE on créé une vue :

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 OR REPLACE VIEW ent AS 
SELECT
	e.id_entreprise,
	e.rs,
	COALESCE(et.tribunal_rcs,''),
	COALESCE(et.num_rcs,''),
	COALESCE(et.siren,''),
	COALESCE(et.tva_intra,''),
	COALESCE(s.capital,''),
	COALESCE(st.lib,'')
FROM
	entreprise AS e
	LEFT JOIN ent_immat AS et ON (et.id_ent_immat = e.id_entreprise)
	LEFT JOIN societe AS s ON (s.id_ent_immat = e.id_ent_immat)
		JOIN statut AS st ON (st.id_statut = s.id_statut);

Une vue pour MRA:

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 OR REPLACE VIEW professionnel AS 
SELECT
	m.id_mra,
	mt.lib AS metier,
	e.rs,
	e.tribunal_rcs,
	e.num_rcs,
	e.siren,
	e.tva_intra,
	e.capital,
	e.lib
FROM
	ent AS e
	JOIN mra AS m ON (m.id_mra = e.id_entreprise)
	JOIN metier AS mt ON (mt.id_metier = m.id_metier);

Et de même pour VENDEUR ou tout autre type d'entreprise.
Il y a-t-il quelque chose de choquant dans ce que je propose ?

Merci d'avance pour votre avis,
François

[frm013]

Bonjour François,

Visiblement, mes modèles ne vous inspirent pas...

[fsmrel]

Bonsoir François,


Il n’y a rien à redire quant à votre dernier diagramme de classes, et le diagramme MWB dérivé est conforme.

Si je ne m'abuse, en cas de disparition d'un vendeur, ses magasins et ses employés doivent disparaitre non?

Ça serait quand même un peu brutal... J’aurais tendance à dire qu’on ne peut supprimer un vendeur que si les employés travaillant dans ses magasins ont d’abord été recasés chez d’autres vendeurs (clause ON DELETE NO ACTION (ou RESTRICT) de la contrainte référentielle entre EMPLOYE_MAG et MAGASIN).

Nous avions bien parlé d'identifier MAGASIN relativement à ENTREPRISE et EMPLOYE relativement à VENDEUR afin de résoudre la contrainte de chemin entre EMPLOYE et MAGASIN afin de ne pas pouvoir se trouver avec employe1_vendeur1 travaillant dans magasin_vendeur2.
Ou j'ai mal compris ?

Je récapépète et reprends là où grosso modo j’en étais, en aménageant un chouïa, mais le principe reste.

Identifier MAGASIN relativement à VENDEUR est possible, mais normalement on devrait pouvoir s’en passer, même si on déclare MAGASIN comme sous-classe de MEMBRE.

La contrainte qui mérite surtout pour le moment qu’on s’y arrête est celle qui veut qu’un magasin n’ait qu’un seul directeur. Comme il serait dangereux de brancher MAGASIN sur DIRECTEUR parce qu’on aurait alors un cycle, et comme à défaut garantir la contrainte à coup de triggers est lourd et pénible, je propose donc qu’on propage la colonne IdMagasin de MAGASIN jusqu'à la table DIRECTEUR pour en faire une clé alternative. Pour cela (dans le principe du moins) on déclare au SGBD la paire {IdEmploye, IdMagasin} comme clé alternative de la table EMPLOYE_MAG (en réalité c'est une surclé), mais surtout on déclare cette paire comme clé étrangère de la table DIRECTEUR et référençant EMPLOYE_MAG. Si le SGBD rouspétait parce qu’il imposerait que cette clé étrangère fît référence à la clé primaire de EMPLOYE_MAG, pas de problème, on aménagerait en conséquence (pour faire comme j’avais fait précédemment). La contrainte d’unicité du directeur de magasin est assurée par la clé alternative {IdMagasin} pour la table DIRECTEUR.

Diagramme MWB :




Pour fixer les idées, MS SQL Server ne détecte pas d’erreur pour le code qui suit, dans lequel je n’ai même pas déclaré la paire {IdEmploye, IdMagasin} en tant que clé alternative de la table EMPLOYE_MAG, ce qui ne l’a pas même ému malgré la présence de la clé étrangère {IdEmploye, IdMagasin} dans le CREATE de la table DIRECTEUR.

TABLE EMPLOYE_MAG

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 EMPLOYE_MAG 
(
      IdEmploye         INT       NOT NULL,
      IdMagasin         INT       NOT NULL,
  PRIMARY KEY (IdEmploye),
  CONSTRAINT EMPLOYE_MAG_EMPLOYE_FK
    FOREIGN KEY (IdEmploye) REFERENCES EMPLOYE (IdEmploye)
    ON DELETE CASCADE,
  CONSTRAINT EMPLOYE_MAG_MAGASIN_FK
    FOREIGN KEY (IdMagasin) REFERENCES MAGASIN (IdMagasin)
    ON DELETE NO ACTION
) ;

TABLE DIRECTEUR

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE DIRECTEUR 
(
      IdEmploye         INT       NOT NULL,
      IdMagasin         INT       NOT NULL,
  PRIMARY KEY (IdEmploye),
  UNIQUE (IdMagasin),
  CONSTRAINT DIRECTEUR_EMPLOYE_FK
    FOREIGN KEY (IdEmploye, IdMagasin) REFERENCES EMPLOYE_MAG (IdEmploye, IdMagasin)
    ON DELETE CASCADE
) ;

[frm013]

Bonjour François,

Juste un mot à propos de votre script de création des tables.

Pour fixer les idées, MS SQL Server ne détecte pas d’erreur pour le code qui suit, dans lequel je n’ai même pas déclaré la paire {IdEmploye, IdMagasin} en tant que clé alternative de la table EMPLOYE_MAG

je suis très étonné ! PostGresql ne laisse pas passer ça et me gratifie d'une erreur : " ERROR: there is no unique constraint matching given keys for referenced table "employe_mag"
SQL state: 42830'

Bien entendu, dès que j'ajoute cette contrainte ça passe.

Concernant le modèle :

Une entreprise doit pouvoir enregistrer ses employés sans que ceux-ci ne soit affectés à un magasin.
En effet, il n'y a pas que les vendeurs qui voudront savoir qui a passé telle commande ou qui à répondu à telle demande dans le cas d'un vendeur.
D'autre part, il peut y avoir des employés tels qu'un comptable, un responsable internet ou même le responsable de l'entreprise qui ne sont pas affectés à un magasin.

Ça serait quand même un peu brutal... J’aurais tendance à dire qu’on ne peut supprimer un vendeur que si les employés travaillant dans ses magasins ont d’abord été recasés chez d’autres vendeurs (clause ON DELETE NO ACTION (ou RESTRICT) de la contrainte référentielle entre EMPLOYE_MAG et MAGASIN).

Désolé d'insister, mais il me semble que dans la vie, quand une entreprise disparait, ses emplois aussi... tristement.

Quoi qu'il en soit, dans une base de données, si je supprime une entreprise, que faire des occurrences de EMPLOYE? de MAGASIN?

Que l'on identifie un magasin relativement ou pas à son entreprise, il faudra supprimer les magasins de celle-ci lors de sa disparition : dans votre modèle physique MAGASIN porte une FK qui référence ENTREPRISE.

Idem pour EMPLOYE, si l'on veut pouvoir créer un employe qui n'est pas affecté à un magasin, EMPLOYE devra avoir une foreign key vers entreprise, identification relative ou pas, les occurrences de EMPLOYE ne peuvent survire à la disparition d'une occurrence de ENTREPRISE.

Je vous avoue ne plus vous suivre François

Abstraction faîte du modèle physique, de mon point de vue, j'ai ce diagramme de classes :

Critique de ce diagramme juste après...



Ce qui coince c'est le passage au modèle physique.

Les problèmes que je vois qui peuvent causer ce souci :

EMPLOYE hérite de l'identifiant de PERSONNE, MAGASIN hérite de l'identifiant de ACTOR. Ces deux classes ont donc chacune leur identifiant.

Concernant ACTOR :

Au fur et à mesure de l'évolution du schéma, cette classe ne fait plus que "porter" les coordonnées diverses des membres. Pourquoi les membres en hériteraient-ils ?
Doit-on encore l'appeler ACTOR ? Pourquoi pas LOCALISATION ?
Cela deviendrait une classe utilisable par n'importe quelle classe devant avoir des coordonnées... qu'en pensez vous?

Concernant EMPLOYE :

Pourquoi hériter de PERSONNE ?

Nous pourrions dire qu'une entreprise emploie des personnes :

ENTREPRISE (0,n)----- créer ----- (1,1) EMPLOI (1,1)----- employer -----(0,1) ------PERSONNE

Suis-je complètement à coté de la plaque ?

A vous lire,
François

[fsmrel]

Critique de ce diagramme juste après...

Le diagramme ne s’affiche pas...

[frm013]

bizarre... je l'ai à l'écran, mais effectivement, aucune pièce joint en pied de post

Je vérifie...

[frm013]

Ca devrait être bon maintenant.

[fsmrel]

Merci, le diagramme s'affiche.

[frm013]

une erreur en dernière ligne :

ENTREPRISE (0,n)----- créer ----- (1,1) EMPLOI (1,1)----- employer -----(0,1) ------PERSONNE

il fallait écrire :
ENTREPRISE -- 0,n----- créer ----- 1,1 -- EMPLOI -- 1,1----- employer -----0,1 -- PERSONNE

[fsmrel]

Bonsoir François,

je suis très étonné ! PostGresql ne laisse pas passer ça et me gratifie d'une erreur : " ERROR: there is no unique constraint matching given keys for referenced table "employe_mag"
SQL state: 42830'

Disons que PostgreSQL colle de près à la théorie relationnelle. ^^

Désolé d'insister, mais il me semble que dans la vie, quand une entreprise disparait, ses emplois aussi... tristement.

Bien entendu, mais la façon de procéder dans une base de données est particulière. Par exemple, peut-on y supprimer un client sans prendre des précautions ? C'est-à-dire, supprimer ses factures sans autre forme de procès (CASCADE au lieu de NO ACTION) ? Qu’en penseront les comptables qui bien sûr n’auront pas été prévenus ?

Dans le cas des employés, si je fais référence à mon dernier diagramme, un employé-dans-un-magasin (table EMPLOYE_MAG) est un employé (table EMPLOYE), mais un employé n’est pas forcément employé dans un magasin : je dirais que si Raoul est employé dans un magasin puis ne l’est plus, alors on le supprimera de la table EMPLOYE_MAG mais pas de la table EMPLOYE, il reste un employé. Maintenant qu’on le recase dans un magasin d’une autre entreprise ou qu’il bascule à l’état de employé-pas-dans-un-magasin, cela dépend des règles de gestion retenues.

Évolution du diagramme que j’ai présenté (je ne fais figurer que les tables concernées), où un employé est dans un magasin ou non :




Code SQL. Dans la série « ceinture, bretelles et épingle à nourrice » (j’évite au mieux l’option CASCADE) :

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
CREATE TABLE ENTREPRISE 
(
    IdEntreprise       CHAR(3)    NOT NULL,
  CONSTRAINT ENTREPRISE_PK PRIMARY KEY (IdEntreprise),
) ;
 
CREATE TABLE ENT_IMMAT
(
    IdEntreprise       CHAR(3)    NOT NULL,
  CONSTRAINT ENT_IMMAT_PK PRIMARY KEY (IdEntreprise),
  CONSTRAINT ENT_IMMAT_ENTREPRISE_FK
    FOREIGN KEY (IdEntreprise) REFERENCES ENTREPRISE (IdEntreprise)
    ON DELETE CASCADE
) ;
 
CREATE TABLE VENDEUR
(
    IdEntreprise       CHAR(3)    NOT NULL,
  CONSTRAINT VENDEUR_PK PRIMARY KEY (IdEntreprise),
  CONSTRAINT VENDEUR_ENT_IMMAT_FK
    FOREIGN KEY (IdEntreprise) REFERENCES ENT_IMMAT (IdEntreprise)
    ON DELETE CASCADE
) ;
 
CREATE TABLE MAGASIN
(
    IdMagasin          CHAR(3)    NOT NULL,
    IdEntreprise       CHAR(3)    NOT NULL,
  CONSTRAINT MAGASIN_PK PRIMARY KEY (IdMagasin),
  CONSTRAINT MAGASIN_VENDEUR_FK
    FOREIGN KEY (IdEntreprise) REFERENCES VENDEUR (IdEntreprise)
) ;
 
CREATE TABLE PERSONNE
(
    IdPersonne         CHAR(3)    NOT NULL,
  CONSTRAINT PERSONNE_PK PRIMARY KEY (IdPersonne),
) ;
 
CREATE TABLE EMPLOYE 
(
    IdEmploye          CHAR(3)    NOT NULL,
  CONSTRAINT EMPLOYE_PK PRIMARY KEY (IdEmploye),
  CONSTRAINT EMPLOYE_PERSONNE_FK
    FOREIGN KEY (IdEmploye) REFERENCES PERSONNE (IdPersonne)
        ON DELETE CASCADE,
) ;
 
CREATE TABLE EMPLOYE_NON_MAG 
(
    IdEmploye          CHAR(3)    NOT NULL,
    IdEntreprise       CHAR(3)    NOT NULL,
  CONSTRAINT EMPLOYE_NON_MAG_PK PRIMARY KEY (IdEmploye),
  CONSTRAINT EMPLOYE_NON_MAG_EMPLOYE_FK
    FOREIGN KEY (IdEmploye) REFERENCES EMPLOYE (IdEmploye)
        ON DELETE CASCADE,
  CONSTRAINT EMPLOYE_NON_MAG_ENTREPRISE_FK
    FOREIGN KEY (IdEntreprise) REFERENCES ENTREPRISE (IdEntreprise)
) ;
CREATE TABLE EMPLOYE_MAG 
(
    IdEmploye          CHAR(3)    NOT NULL,
    IdMagasin          CHAR(3)    NOT NULL,
  CONSTRAINT EMPLOYE_MAG_PK PRIMARY KEY (IdEmploye),
  CONSTRAINT EMPLOYE_MAG_AK UNIQUE (IdEmploye, IdMagasin),
  CONSTRAINT EMPLOYE_MAG_EMPLOYE_FK
    FOREIGN KEY (IdEmploye) REFERENCES EMPLOYE (IdEmploye)
      ON DELETE CASCADE,
  CONSTRAINT EMPLOYE_MAG_MAGASIN_FK
    FOREIGN KEY (IdMagasin) REFERENCES MAGASIN (IdMagasin)
) ;
 
CREATE TABLE DIRECTEUR 
(
    IdEmploye          CHAR(3)    NOT NULL,
    IdMagasin          CHAR(3)    NOT NULL,
  CONSTRAINT DIRECTEUR_PK PRIMARY KEY (IdEmploye),
  CONSTRAINT DIRECTEUR_AK UNIQUE (IdMagasin),
  CONSTRAINT DIRECTEUR_EMPLOYE_FK
    FOREIGN KEY (IdEmploye, IdMagasin) REFERENCES EMPLOYE_MAG (IdEmploye, IdMagasin)
      ON DELETE CASCADE
) ; 
 
-------------------------------------------------------------------------------------------
 
INSERT INTO ENTREPRISE (IdEntreprise)  VALUES ('e01') ;
INSERT INTO ENTREPRISE (IdEntreprise)  VALUES ('e02') ;
INSERT INTO ENTREPRISE (IdEntreprise)  VALUES ('e03') ;
INSERT INTO ENTREPRISE (IdEntreprise)  VALUES ('e04') ;
INSERT INTO ENTREPRISE (IdEntreprise)  VALUES ('e05') ;
 
INSERT INTO ENT_IMMAT (IdEntreprise)  VALUES ('e01') ;
INSERT INTO ENT_IMMAT (IdEntreprise)  VALUES ('e02') ;
INSERT INTO ENT_IMMAT (IdEntreprise)  VALUES ('e03') ;
INSERT INTO ENT_IMMAT (IdEntreprise)  VALUES ('e04') ;
 
INSERT INTO VENDEUR (IdEntreprise)  VALUES ('e01') ;
INSERT INTO VENDEUR (IdEntreprise)  VALUES ('e02') ;
INSERT INTO VENDEUR (IdEntreprise)  VALUES ('e03') ;
 
INSERT INTO MAGASIN (IdEntreprise, IdMagasin)  VALUES ('e01', 'm01') ;
INSERT INTO MAGASIN (IdEntreprise, IdMagasin)  VALUES ('e01', 'm02') ;
INSERT INTO MAGASIN (IdEntreprise, IdMagasin)  VALUES ('e01', 'm03') ;
INSERT INTO MAGASIN (IdEntreprise, IdMagasin)  VALUES ('e01', 'm04') ;
INSERT INTO MAGASIN (IdEntreprise, IdMagasin)  VALUES ('e02', 'm05') ;
INSERT INTO MAGASIN (IdEntreprise, IdMagasin)  VALUES ('e02', 'm06') ;
INSERT INTO MAGASIN (IdEntreprise, IdMagasin)  VALUES ('e02', 'm07') ;
INSERT INTO MAGASIN (IdEntreprise, IdMagasin)  VALUES ('e03', 'm08') ;
INSERT INTO MAGASIN (IdEntreprise, IdMagasin)  VALUES ('e03', 'm09') ;
 
INSERT INTO PERSONNE (IdPersonne)  VALUES ('p01') ;
INSERT INTO PERSONNE (IdPersonne)  VALUES ('p02') ;
INSERT INTO PERSONNE (IdPersonne)  VALUES ('p03') ;
INSERT INTO PERSONNE (IdPersonne)  VALUES ('p04') ;
INSERT INTO PERSONNE (IdPersonne)  VALUES ('p05') ;
INSERT INTO PERSONNE (IdPersonne)  VALUES ('p06') ;
INSERT INTO PERSONNE (IdPersonne)  VALUES ('p07') ;
 
INSERT INTO EMPLOYE (IdEmploye)  VALUES ('p01') ;
INSERT INTO EMPLOYE (IdEmploye)  VALUES ('p02') ;
INSERT INTO EMPLOYE (IdEmploye)  VALUES ('p03') ;
INSERT INTO EMPLOYE (IdEmploye)  VALUES ('p04') ;
INSERT INTO EMPLOYE (IdEmploye)  VALUES ('p05') ;
INSERT INTO EMPLOYE (IdEmploye)  VALUES ('p06') ;
INSERT INTO EMPLOYE (IdEmploye)  VALUES ('p07') ;
 
SELECT '' AS 'EMPLOYE', * FROM EMPLOYE ; 
 
INSERT INTO EMPLOYE_NON_MAG (IdEntreprise, IdEmploye) VALUES ('e01', 'p01') ;
INSERT INTO EMPLOYE_NON_MAG (IdEntreprise, IdEmploye) VALUES ('e01', 'p02') ;
INSERT INTO EMPLOYE_NON_MAG (IdEntreprise, IdEmploye) VALUES ('e02', 'p03') ;
 
SELECT '' AS 'EMPLOYE_NON_MAG', * FROM EMPLOYE_NON_MAG ;
 
 
 
INSERT INTO EMPLOYE_MAG (IdEmploye, IdMagasin) VALUES ('p04', 'm01') ;
INSERT INTO EMPLOYE_MAG (IdEmploye, IdMagasin) VALUES ('p05', 'm01') ;
INSERT INTO EMPLOYE_MAG (IdEmploye, IdMagasin) VALUES ('p06', 'm02') ;
 
SELECT '' AS 'EMPLOYE_MAG', * FROM EMPLOYE_MAG ; 
 
INSERT INTO DIRECTEUR (IdEmploye, IdMagasin) VALUES ('p04', 'm01') ;
-- INSERT INTO DIRECTEUR (IdEmploye, IdMagasin) VALUES ('p05', 'm01') ; -- viol AK 
INSERT INTO DIRECTEUR (IdEmploye, IdMagasin) VALUES ('p06', 'm02') ;
 
SELECT '' AS 'DIRECTEUR', * FROM DIRECTEUR ;

La contrainte d'exclusion est à implémenter sous forme d'une assertion ou d'un trigger.

[fsmrel]

Version « contrainte de chemin » :

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
CREATE TABLE ENTREPRISE 
(
    IdEntreprise       CHAR(3)    NOT NULL,
  CONSTRAINT ENTREPRISE_PK PRIMARY KEY (IdEntreprise),
) ;
 
CREATE TABLE ENT_IMMAT
(
    IdEntreprise       CHAR(3)    NOT NULL,
  CONSTRAINT ENT_IMMAT_PK PRIMARY KEY (IdEntreprise),
  CONSTRAINT ENT_IMMAT_ENTREPRISE_FK
    FOREIGN KEY (IdEntreprise) REFERENCES ENTREPRISE (IdEntreprise)
    ON DELETE CASCADE
) ;
 
CREATE TABLE VENDEUR
(
    IdEntreprise       CHAR(3)    NOT NULL,
  CONSTRAINT VENDEUR_PK PRIMARY KEY (IdEntreprise),
  CONSTRAINT VENDEUR_ENT_IMMAT_FK
    FOREIGN KEY (IdEntreprise) REFERENCES ENT_IMMAT (IdEntreprise)
    ON DELETE CASCADE
) ;
 
CREATE TABLE MAGASIN
(
    IdEntreprise       CHAR(3)    NOT NULL,
    IdMagasin          INT        NOT NULL,
  CONSTRAINT MAGASIN_PK PRIMARY KEY (IdEntreprise, IdMagasin),
  CONSTRAINT MAGASIN_VENDEUR_FK
    FOREIGN KEY (IdEntreprise) REFERENCES VENDEUR (IdEntreprise)
) ;
 
CREATE TABLE PERSONNE
(
    IdPersonne         CHAR(3)    NOT NULL,
  CONSTRAINT PERSONNE_PK PRIMARY KEY (IdPersonne),
) ;
 
CREATE TABLE EMPLOYE 
(
    IdEntreprise       CHAR(3)    NOT NULL,
    IdEmploye          CHAR(3)    NOT NULL,
  CONSTRAINT EMPLOYE_PK PRIMARY KEY (IdEntreprise, IdEmploye),
  CONSTRAINT EMPLOYE_AK UNIQUE (IdEmploye),
  CONSTRAINT EMPLOYE_PERSONNE_FK
    FOREIGN KEY (IdEmploye) REFERENCES PERSONNE (IdPersonne)
        ON DELETE CASCADE,
  CONSTRAINT EMPLOYE_ENTREPRISE_FK
    FOREIGN KEY (IdEntreprise) REFERENCES ENTREPRISE (IdEntreprise)
) ;
 
CREATE TABLE EMPLOYE_MAG 
(
    IdEntreprise       CHAR(3)    NOT NULL,
    IdEmploye          CHAR(3)    NOT NULL,
    IdMagasin          INT        NOT NULL,
  CONSTRAINT EMPLOYE_MAG_PK PRIMARY KEY (IdEntreprise, IdEmploye),
  CONSTRAINT EMPLOYE_MAG_AK UNIQUE (IdEntreprise, IdEmploye, IdMagasin),
  CONSTRAINT EMPLOYE_MAG_EMPLOYE_FK
    FOREIGN KEY (IdEntreprise, IdEmploye) REFERENCES EMPLOYE (IdEntreprise, IdEmploye)
      ON DELETE CASCADE,
  CONSTRAINT EMPLOYE_MAG_MAGASIN_FK
    FOREIGN KEY (IdEntreprise, IdMagasin) REFERENCES MAGASIN (IdEntreprise, IdMagasin)
) ;
 
CREATE TABLE DIRECTEUR 
(
    IdEntreprise       CHAR(3)    NOT NULL,
    IdEmploye          CHAR(3)    NOT NULL,
    IdMagasin          INT        NOT NULL,
  CONSTRAINT DIRECTEUR_PK PRIMARY KEY (IdEntreprise, IdEmploye),
  CONSTRAINT DIRECTEUR_AK UNIQUE (IdEntreprise, IdMagasin),
  CONSTRAINT DIRECTEUR_EMPLOYE_FK
    FOREIGN KEY (IdEntreprise, IdEmploye, IdMagasin) REFERENCES EMPLOYE_MAG (IdEntreprise, IdEmploye, IdMagasin)
      ON DELETE CASCADE
) ;
 
-------------------------------------------------------------------------------------------
 
INSERT INTO ENTREPRISE (IdEntreprise)  VALUES ('e01') ;
INSERT INTO ENTREPRISE (IdEntreprise)  VALUES ('e02') ;
INSERT INTO ENTREPRISE (IdEntreprise)  VALUES ('e03') ;
INSERT INTO ENTREPRISE (IdEntreprise)  VALUES ('e04') ;
INSERT INTO ENTREPRISE (IdEntreprise)  VALUES ('e05') ;
 
INSERT INTO ENT_IMMAT (IdEntreprise)  VALUES ('e01') ;
INSERT INTO ENT_IMMAT (IdEntreprise)  VALUES ('e02') ;
INSERT INTO ENT_IMMAT (IdEntreprise)  VALUES ('e03') ;
INSERT INTO ENT_IMMAT (IdEntreprise)  VALUES ('e04') ;
 
INSERT INTO VENDEUR (IdEntreprise)  VALUES ('e01') ;
INSERT INTO VENDEUR (IdEntreprise)  VALUES ('e02') ;
INSERT INTO VENDEUR (IdEntreprise)  VALUES ('e03') ;
 
INSERT INTO MAGASIN (IdEntreprise, IdMagasin)  VALUES ('e01', 1) ;
INSERT INTO MAGASIN (IdEntreprise, IdMagasin)  VALUES ('e01', 2) ;
INSERT INTO MAGASIN (IdEntreprise, IdMagasin)  VALUES ('e01', 3) ;
INSERT INTO MAGASIN (IdEntreprise, IdMagasin)  VALUES ('e01', 4) ;
INSERT INTO MAGASIN (IdEntreprise, IdMagasin)  VALUES ('e02', 1) ;
INSERT INTO MAGASIN (IdEntreprise, IdMagasin)  VALUES ('e02', 2) ;
INSERT INTO MAGASIN (IdEntreprise, IdMagasin)  VALUES ('e02', 3) ;
INSERT INTO MAGASIN (IdEntreprise, IdMagasin)  VALUES ('e03', 1) ;
INSERT INTO MAGASIN (IdEntreprise, IdMagasin)  VALUES ('e03', 2) ;
 
INSERT INTO PERSONNE (IdPersonne)  VALUES ('p01') ;
INSERT INTO PERSONNE (IdPersonne)  VALUES ('p02') ;
INSERT INTO PERSONNE (IdPersonne)  VALUES ('p03') ;
INSERT INTO PERSONNE (IdPersonne)  VALUES ('p04') ;
INSERT INTO PERSONNE (IdPersonne)  VALUES ('p05') ;
INSERT INTO PERSONNE (IdPersonne)  VALUES ('p06') ;
INSERT INTO PERSONNE (IdPersonne)  VALUES ('p07') ;
 
INSERT INTO EMPLOYE (IdEntreprise, IdEmploye) VALUES ('e01', 'p01') ;
INSERT INTO EMPLOYE (IdEntreprise, IdEmploye) VALUES ('e01', 'p02') ;
INSERT INTO EMPLOYE (IdEntreprise, IdEmploye) VALUES ('e01', 'p03') ;
-- INSERT INTO EMPLOYE (IdEntreprise, IdEmploye) VALUES ('e02', 'p01') ;  -- viol AK
 
SELECT '' AS 'EMPLOYE', * FROM EMPLOYE ; 
 
INSERT INTO EMPLOYE_MAG (IdEntreprise, IdEmploye, IdMagasin) VALUES ('e01', 'p01', 1) ;
--INSERT INTO EMPLOYE_MAG (IdEntreprise, IdEmploye, IdMagasin) VALUES ('e01', 'p01', 2) ; -- viol PK
--INSERT INTO EMPLOYE_MAG (IdEntreprise, IdEmploye, IdMagasin) VALUES ('e02', 'p01', 1) ; -- viol FK vers EMPLOYE
INSERT INTO EMPLOYE_MAG (IdEntreprise, IdEmploye, IdMagasin) VALUES ('e01', 'p02', 2) ;
INSERT INTO EMPLOYE_MAG (IdEntreprise, IdEmploye, IdMagasin) VALUES ('e01', 'p03', 2) ;
 
SELECT '' AS 'EMPLOYE_MAG', * FROM EMPLOYE_MAG ; 
 
INSERT INTO DIRECTEUR (IdEntreprise, IdEmploye, IdMagasin) VALUES ('e01', 'p01', 1) ;
INSERT INTO DIRECTEUR (IdEntreprise, IdEmploye, IdMagasin) VALUES ('e01', 'p02', 2) ;
-- INSERT INTO DIRECTEUR (IdEntreprise, IdEmploye, IdMagasin) VALUES ('e01', 'p03', 2) ; -- viol AK 
 
SELECT '' AS 'DIRECTEUR', * FROM DIRECTEUR ;

[frm013]

Bonjour François,

Ah, vous me rassurez, avec mon schéma de ce post je n'étais pas si loin que ça...?
A quelques erreurs près il est vrai : composition de la clé primaire de EMPLOYE_MAG et le manque de la contrainte UNIQUE {id_vendeur, id_magasin} dans DIRECTION.

Juste pour clarifier mon esprit :

Concernant la table EMPLOYE par exemple, l'usage de {id_entreprise, id_employe} comme clé primaire alors que c'est une surclé est autorisé à la condition d'avoir la contrainte UNIQUE sur {id_employe} si j'ai bien compris ?

C'était le point qui me dérangeait lors du passage au modèle physique et la raison de mon post de début décembre.

Si je vous suis bien, nous ne sommes pas dans le cadre d'une identification relative, mais d'une solution d'implémentation permettant de résoudre la contrainte de chemin de façon élégante ?

Comme je le disais hier à dancom5, vous m'avez ouvert de nouvelles perspectives en matière de modélisation et d'implémentation au sein du SGBDR. Je ne maitrise malheureusement pas tous les aspect théoriques ni les implications pratiques de cette nouvelle façon (pour moi ) de procéder. Je relis donc régulièrement votre bible de la normalisation ici même sur DVP. Hélas, ce ne me suffit pas pour être complètement sûr de moi dans tous les cas, rien ne remplace l'expérience.

En tous cas je préfère de loin la solution "contrainte de chemin" à celle « ceinture, bretelles et épingle à nourrice »

Je vous soumets donc bientôt un modèle physique complet pour correction si vous le voulez bien.

Encore merci,
françois

[fsmrel]

Bonjour François,

je n'étais pas si loin que ça...?

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

Concernant la table EMPLOYE par exemple, l'usage de {id_entreprise, id_employe} comme clé primaire alors que c'est une surclé est autorisé à la condition d'avoir la contrainte UNIQUE sur {id_employe} si j'ai bien compris ?

Oui. Vous pouvez du reste permuter les rôles PK/AK comme ci-dessous, ce qui à la réflexion est moins perturbant dans le contexte SQL où il est fait un distinguo entre primaire et alternatif. C’est l’occasion de rappeler que la norme SQL permet qu’une clé étrangère fasse référence aussi bien à une clé alternative qu’à une clé primaire (pour le Modèle Relationnel de Données, aucun problème puisqu’il n’y a que des clés (candidates) sans distinction...)

TABLE EMPLOYE

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 EMPLOYE 
(
    IdEntreprise       CHAR(3)    NOT NULL,
    IdEmploye          CHAR(3)    NOT NULL,
  CONSTRAINT EMPLOYE_PK PRIMARY KEY (IdEmploye),
  CONSTRAINT EMPLOYE_AK UNIQUE (IdEntreprise, IdEmploye),
  CONSTRAINT EMPLOYE_PERSONNE_FK
    FOREIGN KEY (IdEmploye) REFERENCES PERSONNE (IdPersonne)
        ON DELETE CASCADE,
  CONSTRAINT EMPLOYE_ENTREPRISE_FK
    FOREIGN KEY (IdEntreprise) REFERENCES ENTREPRISE (IdEntreprise)
) ;

Si je vous suis bien, nous ne sommes pas dans le cadre d'une identification relative, mais d'une solution d'implémentation permettant de résoudre la contrainte de chemin de façon élégante ?

En quelque sorte. Disons qu’un MCD ou un diagramme de classes ne permettent pas d’aller aussi loin qu’on le voudrait dans l’expression des contraintes de chemin et autres, par exemple qu’un magasin n’ait qu’un directeur, sauf en l’occurrence à créer un cycle, mais ce qui ficherait la patouille au niveau opérationnel. Voyez encore le sort réservé à l'attribut ClientId_2 dans le MLD de Benallasiham en relation avec la contrainte de chemin. Avec MWB on s'en sort mieux (sic !), mais on est obligé d’intervenir sur le niveau physique (mise en œuvre des contraintes K1, K2 par le truchement des index) pour produire le code SQL qui va bien. Histoire de rigoler, disons que ça devient de la modélisation niveau 3...

Je vous soumets donc bientôt un modèle physique complet pour correction si vous le voulez bien.

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