Question sur "Modéliser les données avec MySQL Workbench" de François de Sainte Marie"

**Mathaousse** · 20/07/2016, 17h30

Bonjour,

Je suis en train de consulter le cour sur workbench "Modéliser les données avec MySQL Workbench" de François de Sainte Marie présent sur développez.com. Merci d'avoir pris la peine de le rédiger déjà.
Je me pose une question au niveau de l'association ternaire figure 5.19 (http://fsmrel.developpez.com/basesre...kbench/?page=5). Si on a une cardinalité 0,n cela veut dire que LangageId peut être NULL. Pourtant il fait partie de la clé primaire de "UTILISATION". Il me semble qu'une clé primaire ne peut pas être null. Comment cela est gérer?

Merci.

**SQLpro** · 21/07/2016, 08h41

Lorsque l'on a une association n-aire (binaire, ternaire....) de type plusieurs à plusieurs, que la cardinalité soit en 1,n ou 0,n pour chaque branche, cela revient au même, car c'est la table d'association, créée lors du passage du MCD au MPD qui devient de fait le réceptacle des données associées.
Le fait de ne pas mettre de ligne dans cette table assume la cardinalité 0 qui doit être globale.
Autrement dit, pour respecter le 0 dans les notations de cardinalité du MCD, il suffit de ne rien insérer dans la table !

Un petit exemple dans le monde de l'assurance, avec les entités suivantes :

Personne
Conditions générales
Bien

La ternaire étant le contrat d'assurance, auquel on ajoutera par exemple la date de conclusion...
Il est évident que :

les conditions générales d'assurance sans aucune personne pour le payer ne sert à rien.
les conditions générales sans bien assuré n'a pas de sens
un bien et une personne sans les conditions générales, n'a pas de sens...

Il faudra donc que les 3 critères : Bien, Personne et conditions générales soient renseignés pour que le contrat naisse.

A lire en complément, le livre de Christian Soutou, annotés par moi même :
Nom : Soutou Brouard Modélisation bases de données.jpg
Affichages : 429
Taille : 40,3 Ko

Nom : Soutou Brouard Modélisation bases de données.jpg
Affichages : 429
Taille : 40,3 Ko

A +

**Artemus24** · 21/07/2016, 09h31

Salut Mathaousse.

Oui, en effet, le null est interdit dans la clef primaire.
Que ce soit sur un simple colonne ou sur une association de plusieurs colonnes.

Comment faire pour gérer le null si justement il n'est pas accepté dans la clef primaire ?
Il suffit de ne pas faire de clef primaire sur vos trois colonnes et de les transformer en un 'unique index', comme ci-après :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `langage`
--------------
 
--------------
CREATE TABLE IF NOT EXISTS `langage`
( `langage_id`   integer unsigned auto_increment not null primary key,
  `langage_nom`  varchar(255)                    not null
) engine=innoDB
  default charset=latin1 collate=latin1_general_ci
  row_format=compressed
--------------
 
--------------
insert into `langage` (`langage_nom`) value
  ('cobol'),('fortran'),('algol'),('basic'),('pl1')
--------------
 
--------------
select * from `langage`
--------------
 
+------------+-------------+
| langage_id | langage_nom |
+------------+-------------+
|          1 | cobol       |
|          2 | fortran     |
|          3 | algol       |
|          4 | basic       |
|          5 | pl1         |
+------------+-------------+
--------------
DROP TABLE IF EXISTS `personne`
--------------
 
--------------
CREATE TABLE IF NOT EXISTS `personne`
( `personne_id`   integer unsigned auto_increment not null primary key,
  `personne_nom`  varchar(255)                    not null
) engine=innoDB
  default charset=latin1 collate=latin1_general_ci
  row_format=compressed
--------------
 
--------------
insert into `personne` (`personne_nom`) value
  ('bill gates'),('Steve Jobs'),('Steve Wozniak')
--------------
 
--------------
select * from `personne`
--------------
 
+-------------+---------------+
| personne_id | personne_nom  |
+-------------+---------------+
|           1 | bill gates    |
|           2 | Steve Jobs    |
|           3 | Steve Wozniak |
+-------------+---------------+
--------------
DROP TABLE IF EXISTS `projet`
--------------
 
--------------
CREATE TABLE IF NOT EXISTS `projet`
( `projet_id`   integer unsigned auto_increment not null primary key,
  `projet_nom`  varchar(255)                    not null
) engine=innoDB
  default charset=latin1 collate=latin1_general_ci
  row_format=compressed
--------------
 
--------------
insert into `projet` (`projet_nom`) value
  ('Apple inc'),('Microsoft')
--------------
 
--------------
select * from `projet`
--------------
 
+-----------+------------+
| projet_id | projet_nom |
+-----------+------------+
|         1 | Apple inc  |
|         2 | Microsoft  |
+-----------+------------+
--------------
DROP TABLE IF EXISTS `utilisation`
--------------
 
--------------
CREATE TABLE IF NOT EXISTS `utilisation`
( `id`           integer unsigned auto_increment not null primary key,
  `personne_id`  integer unsigned                    null,
  `projet_id`    integer unsigned                    null,
  `langage_id`   integer unsigned                    null,
  CONSTRAINT `FK_PERSONNE` FOREIGN KEY (`personne_id`) REFERENCES `personne` (`personne_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `FK_PROJET`   FOREIGN KEY (`projet_id`)   REFERENCES `projet`   (`projet_id`)   ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `FK_LANGAGE`  FOREIGN KEY (`langage_id`)  REFERENCES `langage`  (`langage_id`)  ON DELETE RESTRICT ON UPDATE CASCADE,
  unique index `IDX` (`personne_id`,`projet_id`,`langage_id`)
) engine=innoDB
  default charset=latin1 collate=latin1_general_ci
  row_format=compressed
--------------
 
--------------
insert into `utilisation` (`personne_id`,`projet_id`,`langage_id`) values  (null, null, null)
--------------
 
--------------
insert into `utilisation` (`personne_id`,`projet_id`,`langage_id`) values  (null, null,    1)
--------------
 
--------------
insert into `utilisation` (`personne_id`,`projet_id`,`langage_id`) values  (null,    1,    1)
--------------
 
--------------
insert into `utilisation` (`personne_id`,`projet_id`,`langage_id`) values  (   1,    1,    1)
--------------
 
--------------
insert into `utilisation` (`personne_id`,`projet_id`,`langage_id`) values  (   1,    1,    1)
--------------
 
ERROR 1062 (23000) at line 121: Duplicata du champ '1-1-1' pour la clef 'IDX'
--------------
select * from `utilisation`
--------------
 
+----+-------------+-----------+------------+
| id | personne_id | projet_id | langage_id |
+----+-------------+-----------+------------+
|  1 |        NULL |      NULL |       NULL |
|  2 |        NULL |      NULL |          1 |
|  3 |        NULL |         1 |          1 |
|  4 |           1 |         1 |          1 |
+----+-------------+-----------+------------+
--------------
select t1.id,
       t2.personne_nom,
       t3.projet_nom,
       t4.langage_nom
 
from `utilisation` as t1
 
left outer join `personne` as t2
on t2.personne_id = t1.personne_id
 
left outer join `projet`   as t3
on t3.projet_id = t1.projet_id
 
left outer join `langage`  as t4
on t4.langage_id = t1.langage_id
--------------
 
+----+--------------+------------+-------------+
| id | personne_nom | projet_nom | langage_nom |
+----+--------------+------------+-------------+
|  1 | NULL         | NULL       | NULL        |
|  2 | NULL         | NULL       | cobol       |
|  3 | NULL         | Apple inc  | cobol       |
|  4 | bill gates   | Apple inc  | cobol       |
+----+--------------+------------+-------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

La question que vous devez vous poser concerne l'utilité de mettre NULL dans l'une de vos colonnes.

L'exemple de la figure 5.19 interdit justement de mettre NULL.
C'est pourquoi la clef primaire est constitué de vos trois colonnes.
Pour la cardinalité 0, la solution consiste à ne pas insérer la ligne dans la table "utilisation".
Ou bien mettre une valeur existante dans les tables respectives de vos trois colonnes.
C'est la contrainte formulée par la clef étrangère.

Comme vous le constatez, la clef étrangère accepte le NULL.

@+

**Mathaousse** · 21/07/2016, 10h00

Merci pour vos réponses.

**aieeeuuuuu** · 04/08/2016, 14h55

Bonjour,

Envoyé par Artemus24

Il suffit de ne pas faire de clef primaire sur vos trois colonnes et de les transformer en un 'unique index', comme ci-après :

Cela ne couvre toutefois pas tous les cas, puisqu'il restera posible d'insérer plusieurs fois le même couple (`personne_id`,`projet_id`) :

Code :

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

1
2
3
 
insert into `utilisation` (`personne_id`,`projet_id`,`langage_id`) values  (   1,    1,   null)
insert into `utilisation` (`personne_id`,`projet_id`,`langage_id`) values  (   1,    1,   null)

Il me semble que pour répondre totalement aux spécifications, il faudrait en fait deux tables associatives, l'une associant les personnes aux projets (clef primaire (`personne_id`,`projet_id`) ), l'autre permettant de spécifier le ou les langages le cas échéant (clef primaire (`personne_id`,`projet_id`,`langage_id`) )

**Artemus24** · 05/08/2016, 06h56

Salut à tous.

Envoyé par aieeeuuuuu

Cela ne couvre toutefois pas tous les cas, puisqu'il restera posible d'insérer plusieurs fois le même couple (`personne_id`,`projet_id`) :

Non, c'est faux ce que vous dites ! Vous n'avez pas bien regardé mon exemple. Dans la table "utilisateur", j'ai mis :

Code :

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

unique index `IDX` (`personne_id`,`projet_id`,`langage_id`)

Comme Mathaousse semblait tenir au marqueur NULL, j'ai préféré lui donner cette solution. Le triplé sera nécessairement unique.

A vrai dire, j'aurai dû mettre ceci :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE IF NOT EXISTS `utilisation`
( `personne_id`  integer unsigned   NOT NULL,
  `projet_id`    integer unsigned   NOT NULL,
  `langage_id`   integer unsigned   NOT NULL,
  CONSTRAINT `FK_PERSONNE` FOREIGN KEY (`personne_id`) REFERENCES `personne` (`personne_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `FK_PROJET`   FOREIGN KEY (`projet_id`)   REFERENCES `projet`   (`projet_id`)   ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `FK_LANGAGE`  FOREIGN KEY (`langage_id`)  REFERENCES `langage`  (`langage_id`)  ON DELETE RESTRICT ON UPDATE CASCADE,
  primary key (`personne_id`,`projet_id`,`langage_id`)
) engine=innoDB
  default charset=latin1 collate=latin1_general_ci
  row_format=compressed;

Ce qui correspond à une "relation identifiée" !

@+

**aieeeuuuuu** · 05/08/2016, 10h08

Envoyé par Artemus24

Vous n'avez pas bien regardé mon exemple

Non, c'est vous qui n'avez pas bien regardé le mien : en laissant langage_id à null, il est possible d'insérer plusieurs fois la même couple (personne_id`,`projet_id). En effet, selon la norme (que MySQL respecte sur ce point) il est possible d’insérer plusieurs fois NULL sur une contrainte d'unicité.

A noter que votre exemple fonctionnerait sous SQL Server qui n'autorise pas plusieurs NULL sur une contrainte d'unicité (on peut toutefois retrouver le comportement attendu par la norme en posant un index filtré)

**Artemus24** · 05/08/2016, 13h26

Salut aieeeuuuuu.

Erreur d'interprétation de ma part !
J'avais compris que je n'avais pas mis un "unique index" dans la table "utilisation".

En effet, MySql autorise les NULL multiples avec un unique index".

Pour revenir sur ce marqueur NULL, je pense que Mathaousse interprète mal le sens de la signification de :

Envoyé par Mathaousse

Si on a une cardinalité 0,n cela veut dire que LangageId peut être NULL.

Une cardinalité à zéro va signifier que la ligne ne sera pas insérée dans la table.

Il est alors logique de mettre ces colonnes en tant que clef primaire.

Envoyé par aieeeuuuuu

Il me semble que pour répondre totalement aux spécifications, il faudrait en fait deux tables associatives, l'une associant les personnes aux projets (clef primaire (`personne_id`,`projet_id`) ), l'autre permettant de spécifier le ou les langages le cas échéant (clef primaire (`personne_id`,`projet_id`,`langage_id`) )

Pourquoi faire deux tables associatives ?

Ce qui sous-tend qu'une personne appartient à un seul projet.

@+

**aieeeuuuuu** · 05/08/2016, 17h19

Envoyé par Artemus24

Pour revenir sur ce marqueur NULL, je pense que Mathaousse interprète mal le sens de la signification de :

Non, je pense qu'il interpréte bien les multiplicités définies, et que le problème qu'il soulève est intéressant

Envoyé par Artemus24

Une cardinalité à zéro va signifier que la ligne ne sera pas insérée dans la table.

Dans ce cas, il n'est alors pas possible d'affecter simplement un développeur à un projet sans spécifier au moins un langage, ce qui est contraire aux multiplicité définies dans l'article

Envoyé par Artemus24

Pourquoi faire deux tables associatives ?

Ce qui sous-tend qu'une personne appartient à un seul projet.

@+

justement non, il devient alors possible d'affecter un développeur à un ou plusieurs projets sans spécifier de langage :

- un développeur sur un projet sans langage spécifié --> une ligne dans DEVELOPPEUR_PROJET, aucune ligne dans DEVELOPPEUR_PROJET_LANGAGE
- un développeur sur un projet avec deux langages spécifiés --> une ligne dans DEVELOPPEUR_PROJET, deux lignes dans DEVELOPPEUR_PROJET_LANGAGE
- un développeur sur deux projets sans langage spécifié --> deux lignes dans DEVELOPPEUR_PROJET, aucune ligne dans DEVELOPPEUR_PROJET_LANGAGE

**Artemus24** · 05/08/2016, 19h37

Salut aieeeuuuuu.

Envoyé par aieeeuuuuu

Non, je pense qu'il interprète bien les multiplicités définies, et que le problème qu'il soulève est intéressant

Désolé, mais je ne comprends pas trop ce que vous me dites.

Dans la paragraphe "5.3. Retour sur les associations de plusieurs à plusieurs : associations ternaires" du lien donné par Mathaousse, il est expliqué comment manipuler Workbench afin de créer une table association ternaire.
Cette table association ternaire garde toute la souplesse que l'on peut obtenir en recherchant toutes les combinaisons possibles sur les liens entre les tables mères "langage", "projet" et "personne".

La seule question que l'on doit se poser est celle du marqueur NULL.
Si on autorise celui-ci alors les trois clefs étrangères ont comme contrainte d'être dans un "unique index" --> relation non identifiée.
Si celui-ci n'est pas autorisé alors les trois clefs étrangères appartiennent à la clef primaire --> relation identifiée.

Or la relation définissant le langage admet que l'on peut ne pas le renseigner dans la table "utilisation".
Nous sommes dans le cas où l'on utilise le marqueur NULL et de ce fait, un unique index.
Que je traduis ainsi :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE IF NOT EXISTS `utilisation`
( `id`           integer unsigned   NOT NULL auto_increment primary key,
  `personne_id`  integer unsigned   NOT NULL,
  `projet_id`    integer unsigned   NOT NULL,
  `langage_id`   integer unsigned       NULL,
  CONSTRAINT `FK_PERSONNE` FOREIGN KEY (`personne_id`) REFERENCES `personne` (`personne_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `FK_PROJET`   FOREIGN KEY (`projet_id`)   REFERENCES `projet`   (`projet_id`)   ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `FK_LANGAGE`  FOREIGN KEY (`langage_id`)  REFERENCES `langage`  (`langage_id`)  ON DELETE RESTRICT ON UPDATE CASCADE,
  unique index `idx` (`personne_id`,`projet_id`,`langage_id`)
) engine=innoDB
  default charset=latin1 collate=latin1_general_ci;

En rouge, le fait d'autoriser le NULL pour la colonne "langage_id".

Envoyé par aieeeuuuuu

Dans ce cas, il n'est alors pas possible d'affecter simplement un développeur à un projet sans spécifier au moins un langage, ce qui est contraire aux multiplicité définies dans l'article

En effet, vous avez raison. Du coup, je comprends mieux la solution à deux tables associatives que vous proposez.

Envoyé par aieeeuuuuu

justement non, il devient alors possible d'affecter un développeur à un ou plusieurs projets sans spécifier de langage :

Oui, je suis d'accord, sauf que l'exemple traite d'une table associative ternaire.

Avec mon exemple ci-dessus (en espérant que je ne me suis pas trompé), les colonnes "personne_id" et "projet_id" sont nécessairement renseignées (pas de NULL).
Tandis que la colonne "langage_id" autorise le marqueur NULL. Ce qui implique que nous avons une relation non identifiée.

Au fait, je vous ai mis un +1 car j'ai trouvé votre réponse pertinente.

@+

Question sur "Modéliser les données avec MySQL Workbench" de François de Sainte Marie"

MySQL

Discussions similaires

Partager

Partager