Base en etoile avec MySql

**ratatam25** · 18/12/2015, 12h43

Bonjour,
Dans le cadre d'une formation, je dois repondre à l'exercice suivant:
construire une base de donnée en étoile avec MySql.
La base doit contenir 5 tables (dans chaque table id est la primary key) :
t_nom(id, nom)
t_prenom(id, prenom)
t_annee_naissance(id, annee)
t_statut(id, statut)
t_assemblage(id, id_nom, id_prenom, id_annee, id_statut).
(la cinquieme table regroupe les identifiants des 4 premieres).

La création de la base et des tables ne me pose pas de problème (dans une BDD relationnelle) mais c'est le peuplement de t_assemblage que je ne sais pas résoudre.
J'ai regardé le tutoriel de Y. GRIM: conception d'un entrepot... sur dvp.com, mais je ne suis pas plus avancé.
Quelle(s) piste(s) pouvez vous me proposer ?
Je vous remercie de votre aide.

**Artemus24** · 20/12/2015, 20h00

Salut ratatam25.

Envoyé par Ratatam25

c'est le peuplement de t_assemblage que je ne sais pas résoudre.

Tes quatre premières tables sont simples :
--> t_nom(id, nom)
--> t_prenom(id, prenom)
--> t_annee_naissance(id, annee)
--> t_statut(id, statut)
A chaque table, tu dois insérer son jeu d'essai.
Vu quelles sont indépendantes, il n'y a aucun problème.
Tu peux même commencer par la quatrième et finir par la troisième.
Autrement dit, l'ordre de création et d'insertion n'ont pas d'importance.

Inversement, pour la cinquième table, vu que c'est ton exercice, tu dois définir quatre clef étrangères.
Tu dois, par exemple, pour la première table, ajouter ceci dans la cinquième table :

Code :

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

CONSTRAINT `FK_01` FOREIGN KEY (`id_nom`) REFERENCES `t_nom` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

Cela signifie que tu dois créer un lien entre la table père de nom "t_nom" avec la table fils de nom "t_assemblage".
Ce qui suppose que pour insérer dans la table de nom "t_assemblage", la clef étrangère doit déjà exister.
Autrement dit, tu dois d'abord remplir la table de nom "t_nom" avant la table de nom "t_assemblage".
Et oui, dans l'usage d'une "foreign key", la table référencée doit d'abord être créé et remplie avant celle qui utilise cette référence.

Vu que je viens de te donner la "foreign key" pour la première table, tu dois faire de même pour les trois autres tables.
Je le répète, tu dois le faire dans la cinquième table, vu que c'est là que tu dois définir les liens (foreign key) !

@+

**ratatam25** · 20/12/2015, 23h55

Merci Artemus24 pour ta réponse.

Je te présente ce que j'ai fait après de multiples essais depuis mon post et quelques heures(!!!) de réflexion ce WE.
En fait c'est bien car je découvre tes lignes après avoir "fini" mes explorations.
Ainsi j'ai appris de nouvelles choses et en plus en venant sur le forum j'ai une solution.. et des fonctions que je ne connaissais pas... le top.

Effectivement je suis passé par les FK mais il me restait le pb du "peuplement" de ma table 5 (assemblage).

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
 
--PARTIE 1:CREATION DE LA TABLE ASSEMBLAGE AVEC FOREIGN KEY
 
CREATE TABLE assemblage (
	id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
	id_nom INT,
			FOREIGN KEY (id_nom)
			REFERENCES nom(id),
	id_prenom INT,
			FOREIGN KEY (id_prenom)
			REFERENCES prenom(id),
	id_annee INT,
			FOREIGN KEY (id_annee)
			REFERENCES annee_naissance(id),
	id_statut INT,
			FOREIGN KEY (id_statut)
			REFERENCES statut(id)
)
ENGINE=InnoDB;

Dans le code que tu me proposes il y a "CONSTRAINT FK_01". A quoi sert cette commande ?

Pour peupler ma table assemblage je ne connaissais pas "...Cascade...".
J'ai retenue la solution suivante qui suppose que je relance ce script à chaque mises à jours de mes 4 autres tables. Je pense que Cascade évite ce pb.
Je vais reprendre dans ce sens.

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
 
--PARTIE 2: Je remplie le premier champ id_nom 
INSERT INTO assemblage(id_nom,id_prenom,id_annee,id_statut)
		-- choix des valeur dans la table nom
		SELECT nom.id
		FROM nom, prenom
		WHERE nom.id=prenom.id; 
 
--PARTIE 3: puis je fait update de la table assemblage		
UPDATE assemblage, prenom
SET assemblage.id_prenom=prenom.id
WHERE assemblage.id_nom=prenom.id;	--coherence en gardant tjs id_nom
 
UPDATE assemblage, annee_naissance
SET assemblage.id_annee=annee_naissance.id
WHERE assemblage.id_nom=annee_naissance.id;
 
UPDATE assemblage, statut
SET assemblage.id_statut=statut.id
WHERE assemblage.id_nom=statut.id;	--coherence en gardant tjs id_nom

C'est vraiment sympa de t'être intéressé à mon problème.
@+

**Artemus24** · 21/12/2015, 03h28

Salut ratatam25.

Envoyé par ratatam25

Dans le code que tu me proposes il y a "CONSTRAINT FK_01". A quoi sert cette commande ?

Ce n'est pas une commande, mais juste pour indiquer comment va se nommer ta "foreign key".
A chaque "foreign key", tu devras indiquer un nouveau nom.

Envoyé par ratatam25

Pour peupler ma table assemblage je ne connaissais pas "...Cascade...".

Je ne sais pas ce que tu as compris de "cascade" mais je pense que tu es dans l'erreur.
"update cascade" signifie que si tu viens à modifier une clef de ta table père, cette modification sera répercutée dans la table fils.
"delete cascade" signifie que si tu viens à supprimer une clef de ta table père, cette suppression sera répercutée dans la table fils.

Est-ce cela que tu voulais ?

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
194
195
196
197
198
--------------
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 `t_nom`
--------------
 
--------------
create table `t_nom` (
  `id`  int      NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `nom` char(10) NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `t_nom` (`nom`) values ('einstein'),('lavoisier')
--------------
 
--------------
select * from t_nom
--------------
 
+----+-----------+
| id | nom       |
+----+-----------+
|  1 | einstein  |
|  2 | lavoisier |
+----+-----------+
--------------
DROP TABLE IF EXISTS `t_prenom`
--------------
 
--------------
create table `t_prenom` (
  `id`     int      NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `prenom` char(10) NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `t_prenom` (`prenom`) values ('albert'),('antoine')
--------------
 
--------------
select * from t_prenom
--------------
 
+----+---------+
| id | prenom  |
+----+---------+
|  1 | albert  |
|  2 | antoine |
+----+---------+
--------------
DROP TABLE IF EXISTS `t_naissance`
--------------
 
--------------
create table `t_naissance` (
  `id`        int   NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `naissance` date  NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `t_naissance` (`naissance`) values ('1879-03-14'),('1743-08-26')
--------------
 
--------------
select * from t_naissance
--------------
 
+----+------------+
| id | naissance  |
+----+------------+
|  1 | 1879-03-14 |
|  2 | 1743-08-26 |
+----+------------+
--------------
DROP TABLE IF EXISTS `t_statut`
--------------
 
--------------
create table `t_statut` (
  `id`     int       NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `statut` char(10)  NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `t_statut` (`statut`) values ('décédé')
--------------
 
--------------
select * from t_statut
--------------
 
+----+--------+
| id | statut |
+----+--------+
|  1 | décédé |
+----+--------+
--------------
DROP TABLE IF EXISTS `t_assemblage`
--------------
 
--------------
create table `t_assemblage` (
  `id`           int       NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `id_nom`       int       NOT NULL,
  `id_prenom`    int       NOT NULL,
  `id_naissance` int       NOT NULL,
  `id_statut`    int       NOT NULL,
  `commentaire`  char(10)  NOT NULL,
  CONSTRAINT `FK_01` FOREIGN KEY (`id_nom`)       REFERENCES `t_nom`       (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_02` FOREIGN KEY (`id_prenom`)    REFERENCES `t_prenom`    (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_03` FOREIGN KEY (`id_naissance`) REFERENCES `t_naissance` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_04` FOREIGN KEY (`id_statut`)    REFERENCES `t_statut`    (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `t_assemblage` (`id_nom`,`id_prenom`,`id_naissance`,`id_statut`,`commentaire`) values
  (1, 1, 1, 1, 'Physicien'),
  (2, 2, 2, 1, 'Chimiste')
--------------
 
--------------
select * from t_assemblage
--------------
 
+----+--------+-----------+--------------+-----------+-------------+
| id | id_nom | id_prenom | id_naissance | id_statut | commentaire |
+----+--------+-----------+--------------+-----------+-------------+
|  1 |      1 |         1 |            1 |         1 | Physicien   |
|  2 |      2 |         2 |            2 |         1 | Chimiste    |
+----+--------+-----------+--------------+-----------+-------------+
--------------
select t1.nom,
       t2.prenom,
       year(t3.naissance) as 'année',
       t4.statut,
           t5.commentaire
from       t_assemblage as t5
inner join t_nom        as t1
on t5.id_nom       = t1.id
 
inner join t_prenom     as t2
on t5.id_prenom    = t2.id
 
inner join t_naissance  as t3
on t5.id_naissance = t3.id
 
inner join t_statut     as t4
on t5.id_statut    = t4.id
--------------
 
+-----------+---------+-------+--------+-------------+
| nom       | prenom  | année | statut | commentaire |
+-----------+---------+-------+--------+-------------+
| einstein  | albert  |  1879 | décédé | Physicien   |
| lavoisier | antoine |  1743 | décédé | Chimiste    |
+-----------+---------+-------+--------+-------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

@+

**pier.antoine** · 23/12/2015, 13h31

Bonjour

Je ne comprends pas trop

le peuplement de t_assemblage que je ne sais pas résoudre

Concrètement qu'est-ce qui vous pose problème?

Dans l'absolu, il suffit de renseigner la table t_assemblage, avec les valeurs voulues pour les différentes colonne de cette table.
Exemple avec :

Nom : 1-Zorglub / 2-Ninjago
Prénom : 1-Casimir / 2-Agamemnon
Année : 1- 1900 / 2-2000
Statut : 1-Actif / 2-Non actif

Je renseigne la table t_assemblage avec 1-2-2-1: ce sera Zorglub Agamemnon 2000 Actif

Comme nous sommes dans une base relationnelle, il faut spécifier ces relations avec les clé étrangères comme l'a signalé Artemus. Mais au niveau de Mysql (on m'arrête si je me trompe) ces clé étrangères permettent juste d'assurer l'intégrité de la base, c'est à dire prendre un nom qui est déjà référencé dans la table nom.
Mysql ne permettra pas de choisir le nom parmi la liste de ceux disponible dans la table Nom. Pour cela, il faudrait passer par une interface graphique, soit un site, soit un client graphique (possible avec HeidiSQL).

Ceci étant dit, je trouve l'exercice complétement stupide : créer une base client en saisissant d'abord le nom dans une table, le prénom dans une autre, la fonction dans une troisième, la date de naissance dans une quatrième.... et assembler le tout dans une table de jointure... je trouve cela fulgurant.
Je pense qu'il y a mieux pour apprendre les jointures......

Pierre

**ratatam25** · 31/12/2015, 01h11

Merci Artemus24 pour ta réponse.
La mienne vient tardivement (presque 10 jours sans ordi... les vacances...).

Oui c'est bien ce type de base.
Mais la difficulté est en fait le peuplement de t_assemblage.
La création de cette base est la première partie d'un exercice dont je ne connais pas la suite... (les mystères des formations universitaires...)
Toute fois je pense qu'il va nous être demandé de peupler les tables nom, prenom, annee, statut avec différents fichiers txt, la jointure se fera avec t_assemblage... qui devra être elle rempli mais sans fichier txt, uniquement avec les clefs des 4 autres tables... sans que j'ai à saisir manuellement quoi que ce soit dans t_assemblage
C'est pour cela que j'ai essayé avec update.
Méthode qui fonctionne.

Je n'avais pas compris le sens de cascade. Un bon tour dans la doc et maintenant c'est plus clair.

Merci à toi.

**ratatam25** · 31/12/2015, 01h24

Pier.antoine,

Oui, je suis bien d'accord avec vous.
Ce schéma en étoile pour apprendre les jointures, c'est pas le top.

Le peuplement de t_assemblage ne doit pas se faire avec une interface graphique.

Dans ma réponse à Artemus24, je disais que ce base était la première partie d'un exercice dont je ne connaissais pas la suite.
Mais je pense que mes 4 tables seront remplies avec des fichiers txt.

Pour moi le peuplement de t_assemblage ne pourra se faire qu'avec 'update' ce qui me permettra de récupérer mes numéros de clefs auto incrémentées.

L'exercice n'est pas très clair.

Quoi qu'il en soit merci de participer à la discussion.

Quand j'en sais plus je vous tiendrai au courant.

@+

**Artemus24** · 31/12/2015, 12h06

Salut ratatam25.

En vous lisant, j'ai l'impression que vous n'avez même pas daigner jeter un coup d’œil à l'exemple que je vous ai concocté.

Pour remplir la cinquième table (t_assemblage), vous devez au préalable créer et remplir vos quatre autres tables. Pourquoi ?
Car la cinquième table, vous impose d'insérer une nouvelle ligne, que si et seulement si, la valeur de la clef étrangère existe déjà.

Cela signifie que vous ne pouvez pas insérer la valeur '1', de l'identifiant 'id_nom' de la table 't_assemblage' que si cette valeur '1' est déjà présente dans l'identifiant 'id' de la table 't_nom'.
C'est la clef étrangère (foreign key) qui vous impose cela. C'est une contrainte !

@+

Base en etoile avec MySql

MySQL

Vue hybride

Discussions similaires

Partager

Partager