Quelques requêtes en SQL concernant MERISE 2

**gautier1er** · 04/09/2017, 18h30

Bonjour amis du Club ,etant débutant
j'ai besoin de votre aide pour créer les tables COURRIER , COURRIER DEPART et COURRIER ARRIVE ; ainsi que quelques requete d'insertion , modification et suppression sur les tables COURRIER , COURRIER DEPART et COURRIER ARRIVE en SQL car c'est la premiere fois que je tombe sur un MCD avec heritage ,en plus je n'ai jamais utilisé triggers .
aidez moi SVP!

voici mon MCD ci-joint et quelque relations

COURRIER (NumCour , NumOrdreCourArriv, NumOrdreCourDep ,ObjetCour , DateEcritureCour,
# CodePers)

COURRIER ARRIVE (NumOrdreCourArriv , NumCour , DateArrivCourrServCour , NumOrgCourArriv )

COURRIER DEPART (NumOrdreCourDep , NumCour , DateExpedCourDep , NatureCourDep , NumCourServDest )

REPONDRE (NumOrdreCourArriv ,#NumOrdreCourDep)

cordialement

**al1_24** · 04/09/2017, 19h15

Quelles requêtes as-tu déjà écrites ?
Où rencontres-tu exactement un problème ?
Des triggers ? Pour quoi faire ?

**Cincinnatus** · 07/09/2017, 10h30

Si le problème se situe au niveau de l'héritage, la solution est à trouver au niveau du MLD ou du MPD : faut-il fusionner ou séparer les type de courriers ?
Donc :
soit une table regroupant COURRIER / COURRIER ARRIVE / COURRIER DEPART + la table REPONDRE
soit une table COURRIER + les tables COURRIER ARRIVE, COURRIER DEPART et la table REPONDRE, avec les contraintes d'intégrité qui vont bien,
soit des tables (COURRIER + COURRIER ARRIVE), (COURRIER + COURRIER DEPART), REPONDRE.

Le SQL découle de ce choix au niveau logique.

**escartefigue** · 12/09/2017, 10h55

Bonjour,

Si votre modèle de données a été créé à l'aide d'un logiciel de modélisation, alors le script (DDL) de création des tables sera créé automatiquement lors de la génération du MPD (après avoir choisi le SGBD qui va bien)
Vous aurez donc une base de DDL qu'il ne restera plus qu'à customiser

Pour les requêtes d'insertion, modification et suppression, il faut justement ce DDL pour pouvoir écrire les requêtes correctement

**gautier1er** · 12/09/2017, 14h42

J'ai modifié mon MCD.MCD AMELIORE.pdf
voici la liste de mes tables conçernées .

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
CREATE TABLE ville
(
IdVille TINYINT(3) NOT NULL AUTO_INCREMENT,
NomVille VARCHAR(50) NOT NULL,
UNIQUE NomVille(NomVille),
PRIMARY KEY(IdVille)
)ENGINE=InnoDB;
 
 
 
CREATE TABLE direction
(
IdDirect CHAR(10) NOT NULL,
NomDirect VARCHAR(255) NOT NULL,
SigleDirect CHAR(20) NOT NULL,
IdVille TINYINT(3) NOT NULL ,
PRIMARY KEY (IdDirect),
FOREIGN KEY(IdVille) REFERENCES ville (IdVille)
ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB;
 
CREATE TABLE service
(
IdService CHAR(10) NOT NULL,
NomService VARCHAR(255) NOT NULL, 
SigleService CHAR(20) NOT NULL,
TelService  DECIMAL(8,0) NOT NULL,
EmailService VARCHAR(100) NOT NULL,
IdDirect CHAR(10) NOT NULL,
PRIMARY KEY (IdService),
FOREIGN KEY(IdDirect) REFERENCES direction(IdDirect)
ON DELETE CASCADE ON UPDATE CASCADE	
)ENGINE=InnoDB;
 
 
CREATE TABLE agent
(
MatAgent INT  NOT NULL ,
NomAgent VARCHAR(50) NOT NULL ,
PrenAgent VARCHAR(100) NOT NULL ,
TelAgent DECIMAL(8,0)  NOT NULL,
SexeAgent ENUM('homme','femme') NOT NULL,
EmailAgent VARCHAR(50) DEFAULT NULL ,
DateNaisAgent DATE DEFAULT NULL,
CodePostAgent CHAR(10) DEFAULT NULL,
BoitePostAgent CHAR(10) DEFAULT NULL,
IdService CHAR(10) NOT NULL ,
IdVille TINYINT(3) NOT NULL,
FOREIGN KEY(IdVille) REFERENCES ville (IdVille)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(IdService) REFERENCES service(IdService)
ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY(MatAgent),
INDEX index_nom_prenom_ag (NomAgent,PrenAgent) /*pour les recherches   */
)ENGINE=InnoDB;
 
 
CREATE TABLE correspondante
(
CodeCoresp BIGINT(10) NOT NULL AUTO_INCREMENT,
NomCoresp VARCHAR(50) NOT NULL,
StatutCoresp ENUM('expediteur','destinataire') NOT NULL,
IdVille TINYINT(3) NOT NULL,
FOREIGN KEY(IdVille) REFERENCES ville (IdVille)
ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY(CodeCoresp)
)ENGINE=InnoDB;
 
CREATE TABLE personne_morale
(
CodeCoresp BIGINT(10) NOT NULL, 
SiglePers CHAR(20) NOT NULL,
RaisonSocial VARCHAR(255) NOT NULL,
FOREIGN KEY(CodeCoresp) REFERENCES  correspondante(CodeCoresp)
ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY(CodeCoresp)
)ENGINE=InnoDB;
 
 
CREATE TABLE personne_physique
(
CodeCoresp BIGINT(10) NOT NULL, 
PrenPers VARCHAR(255) NOT NULL,
EmailPers VARCHAR(100) DEFAULT NULL,
DateNaisPers DATE DEFAULT NULL,
SexePers ENUM('homme','femme') NOT NULL,
TelPers  DECIMAL(8,0) NOT NULL,
BoitePostPers  CHAR(10) DEFAULT NULL,
CodePostPers CHAR(10) DEFAULT NULL,
 
FOREIGN KEY(CodeCoresp) REFERENCES  correspondante(CodeCoresp)
ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY(CodeCoresp)
)ENGINE=InnoDB;
 
 
 
 
CREATE TABLE courrier
(
NumCour BIGINT(10)  NOT NULL AUTO_INCREMENT, 
ObjetCour VARCHAR(255) NOT NULL,
DateEcritureCour DATE NOT NULL, 
annee  YEAR NOT NULL,
MatAgent INT  NOT NULL ,
CodeCoresp BIGINT(10) NOT NULL ,
FOREIGN KEY(CodeCoresp) REFERENCES  correspondante(CodeCoresp)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(MatAgent) REFERENCES  agent(MatAgent)
ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY (NumCour)
)ENGINE=InnoDB;
 
 
 
CREATE TABLE courrierDepart
(
NumOrdreCourDep CHAR(10)  NOT NULL ,
DateExpedCourDep DATE NOT NULL, 
NumCourServDest CHAR(10) DEFAULT NULL, 
NatureCourDep ENUM('ordinaire','special') DEFAULT "ordinaire",
 NumCour BIGINT(10) NOT NULL,
 FOREIGN KEY (NumCour) REFERENCES courrier (NumCour)
 ON DELETE CASCADE ON UPDATE CASCADE, 
  UNIQUE NumCour(NumCour), 
  PRIMARY KEY (NumOrdreCourDep)
 
)ENGINE=InnoDB;
 
CREATE TABLE courrierArrive
(
NumOrdreCourArriv CHAR(10)  NOT NULL ,
DateArrivCourServCo DATE NOT NULL, 
NumOrgCourArriv  CHAR(10) DEFAULT NULL, 
DateRetCourServCo DATE DEFAULT NULL,
DateRecepServDest DATE DEFAULT NULL,
 NumCour BIGINT(10) NOT NULL,
 IdDirect CHAR(10) DEFAULT NULL,
 FOREIGN KEY (NumCour) REFERENCES courrier (NumCour)
 ON DELETE CASCADE ON UPDATE CASCADE, 
 FOREIGN KEY(IdDirect) REFERENCES direction(IdDirect)
ON DELETE CASCADE ON UPDATE CASCADE,	
 UNIQUE NumCour(NumCour), 
 PRIMARY KEY (NumOrdreCourArriv)
)ENGINE=InnoDB;
 
 
CREATE TABLE repondre
(
NumOrdreCourArriv CHAR(10)  NOT NULL ,
NumOrdreCourDep CHAR(10)  NOT NULL ,
  FOREIGN KEY(NumOrdreCourArriv) REFERENCES courrierArrive(NumOrdreCourArriv)
ON DELETE CASCADE ON UPDATE CASCADE,
 FOREIGN KEY(NumOrdreCourDep) REFERENCES courrierDepart(NumOrdreCourDep)
ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY(NumOrdreCourArriv)
 
)ENGINE=InnoDB;

J'utilise le logiciel POWER AMC et quand je genère le MPD je remarque que la structure de d'autres tables sont différentes de la mienne par exemple :
la table courrier arrivée avec POWER AMC :

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
create table COURRIER_ARRIVE
(
   NUMCOUR              numeric(10,0) not null,
   NUMORDRECOURARRIV    varchar(10) not null,
   IDDIRECT             varchar(10) not null,
   COU_NUMCOUR          numeric(10,0),
   NUMORDRECOURDEP      varchar(10),
   CODECORESP           numeric(10,0),
   MATAGENT             varchar(20),
   COR_CODECORESP       numeric(10,0),
   OBJETCOUR            varchar(255),
   DATEECRITURECOUR     date,
   DATEARRIVCOURSERVCO  date,
   NUMORGCOURARRIV      numeric(10,0),
   DATERETCOURSERVCO    date,
   DATERECEPSERVDEST    date,
   primary key (NUMCOUR, NUMORDRECOURARRIV)
);

quelle est la bonne methode ?

**Cincinnatus** · 13/09/2017, 10h07

Envoyé par gautier1er

J'utilise le logiciel POWER AMC et quand je genère le MPD je remarque que la structure de d'autres tables sont différentes de la mienne par exemple :
la table courrier arrivée avec POWER AMC

Là, Power AMC a utilisé la troisième possibilité avec la fusion des tables mère et dérivée : (COURRIER + COURRIER ARRIVE)
Il me semble que dans l'outil il est possible de préciser le type d'héritage souhaité. Sinon, il faut reprendre le SQL à la main.

Envoyé par gautier1er

quelle est la bonne methode ?

La bonne méthode est celle qui convient le mieux à votre besoin.

**escartefigue** · 13/09/2017, 14h34

Envoyé par Cincinnatus

Là, Power AMC a utilisé la troisième possibilité avec la fusion des tables mère et dérivée : (COURRIER + COURRIER ARRIVE)
Il me semble que dans l'outil il est possible de préciser le type d'héritage souhaité. Sinon, il faut reprendre le SQL à la main.

En effet, il faut double-cliquer sur le symbole de l'héritage, (le demi-cercle) puis choisir l'onglet "génération" de la fenêtre "propriété de l'héritage" et enfin activer le bouton "n'hériter que des attributs primaires"

Pièce jointe 311430

**gautier1er** · 14/09/2017, 11h48

Envoyé par Cincinnatus

Là, Power AMC a utilisé la troisième possibilité avec la fusion des tables mère et dérivée : (COURRIER + COURRIER ARRIVE)
Il me semble que dans l'outil il est possible de préciser le type d'héritage souhaité. Sinon, il faut reprendre le SQL à la main.
.

j'ai fais le SQL a la main puis j'ai comparé avec celle généré par POWER AMC

voir en haut

**gautier1er** · 14/09/2017, 11h54

Envoyé par escartefigue

En effet, il faut double-cliquer sur le symbole de l'héritage, (le demi-cercle) puis choisir l'onglet "génération" de la fenêtre "propriété de l'héritage" et enfin activer le bouton "n'hériter que des attributs primaires"

Pièce jointe 311430

j'ai fais exactement ça mais le problème est que la clé primaire de la fille est constituée de :id de la fille + id de la mère c'est le cas avec le logiciel power AMC
or moi j'ai déclaré id de la mère en tant que clé étrangère

**escartefigue** · 14/09/2017, 12h06

Ici il s'agit d'un héritage, donc les sous-type héritent de la PK du sur-type : nul besoin d'un identifiant dans les sous-type "courrier départ" et "courrier arrivée"

De plus, vous avez défini des identifiants PK de type varchar, ce qu'il ne faut surtout pas faire, les identifiants PK doivent être idéalement de type integer (small, integer ou bigint selon le nombre d'occurrences maxi estimé), et de préférence attribués par le SGBD. Cette solution est la plus pérenne et la plus performante.

**gautier1er** · 14/09/2017, 12h42

Envoyé par escartefigue

Ici il s'agit d'un héritage, donc les sous-type héritent de la PK du sur-type : nul besoin d'un identifiant dans les sous-type "courrier départ" et "courrier arrivée"

j'ai fais une généralisation et non une spécialisation

Envoyé par escartefigue

De plus, vous avez défini des identifiants PK de type varchar, ce qu'il ne faut surtout pas faire, les identifiants PK doivent être idéalement de type integer (small, integer ou bigint selon le nombre d'occurrences maxi estimé), et de préférence attribués par le SGBD. Cette solution est la plus pérenne et la plus performante.

voici mon idée
le courrier arrivée ou départ doit avoir une réference tel : 2017-13 si le numéro d'ordre du courrier est 13 , parce que chaque année les numéros des courriers sont rémis a zéro en debut d'année pour commencer . on peut avoir un courrier numero 13 mais pour l'année 2018 donc la reference sera 2018-13.

en plus les courriers departs et les courriers arrivées ne sont pas numérotés dans le meme ordre .

**escartefigue** · 14/09/2017, 15h56

Envoyé par gautier1er

j'ai fais une généralisation et non une spécialisation

L'un et l'autre vont de pair

la généralisation concerne ce qui est commun à tous les courriers, c'est l'objet de la table mère "courrier" et la spécialisation concerne les deux autres tables.

Envoyé par gautier1er

le courrier arrivée ou départ doit avoir une réference tel : 2017-13 si le numéro d'ordre du courrier est 13 , parce que chaque année les numéros des courriers sont rémis a zéro en debut d'année pour commencer . on peut avoir un courrier numero 13 mais pour l'année 2018 donc la reference sera 2018-13.
en plus les courriers departs et les courriers arrivées ne sont pas numérotés dans le meme ordre .

Ces règles sont purement fonctionnelles, elles ne doivent aucunement piloter l'identifiant primaire de vos tables.
Si vous avez besoin d'un identifiant fonctionnel construit à partir de la date et d'un numéro d'ordre, stockez simplement les composantes de cet identifiant fonctionnel dans des attributs non PK (une colonne année, une colonne n° d'ordre). Rien ne vous empêche de positionner une contrainte d'unicité sur un identifiant non PK

Pour l'identifiant primaire, j'insiste, utilisez une colonne technique attribuée par le SGBD et surtout pas du (n)char et encore moins du (n)varchar !

**CinePhil** · 14/09/2017, 16h33

Pour compléter la réponse de escartefigue, lire ce billet de SQLPro.

**gautier1er** · 20/09/2017, 12h45

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
CREATE TABLE courrier
(
NumCour BIGINT(10)  NOT NULL AUTO_INCREMENT, 
ObjetCour VARCHAR(255) NOT NULL,
DateEcritureCour DATE NOT NULL, 
annee  YEAR NOT NULL,
MatAgent INT  NOT NULL ,
CodeCoresp BIGINT(10) NOT NULL ,
FOREIGN KEY(CodeCoresp) REFERENCES  correspondante(CodeCoresp)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(MatAgent) REFERENCES  agent(MatAgent)
ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY (NumCour)
)ENGINE=InnoDB;
 
 
 
CREATE TABLE courrierDepart
(
NumCour BIGINT(10) NOT NULL,
NumOrdreCourDep SMALLINT(5)  NOT NULL ,
DateExpedCourDep DATE NOT NULL, 
NumCourServDest CHAR(10) DEFAULT NULL, 
NatureCourDep ENUM('ordinaire','special') NOT NULL,
 
 FOREIGN KEY (NumCour) REFERENCES courrier (NumCour)
 ON DELETE CASCADE ON UPDATE CASCADE, 
  PRIMARY KEY (NumCour),
   UNIQUE NumOrdreCourDep (NumOrdreCourDep),
 
 
 
)ENGINE=InnoDB;
 
CREATE TABLE courrierArrivee
(
 NumCour BIGINT(10) NOT NULL,
NumOrdreCourArriv SMALLINT(5)  NOT NULL ,
DateArrivCourServCo DATE NOT NULL, 
NumOrgCourArriv  CHAR(10) DEFAULT NULL, 
DateRetCourServCo DATE DEFAULT NULL,
DateRecepServDest DATE DEFAULT NULL,
IdDirect TINYINT(3) NOT NULL,
 FOREIGN KEY (NumCour) REFERENCES courrier (NumCour)
 ON DELETE CASCADE ON UPDATE CASCADE, 
 FOREIGN KEY(IdDirect) REFERENCES direction(IdDirect)
ON DELETE CASCADE ON UPDATE CASCADE,	 
 PRIMARY KEY (NumCour),
  UNIQUE NumOrdreCourArriv (NumOrdreCourArriv)
 
)ENGINE=InnoDB;

Je veux créer un index sur deux colonnes de tables différentes pour faire des recherches .
L'utilisateur peut chercher un courrier à travers l'année (table mère) et le numéro d'ordre (table fille).
C’est possible ?

**escartefigue** · 20/09/2017, 13h25

Il vous faut d'abord corriger votre DDL

La contrainte suivante de la table COURRIER doit à mon avis être modifiée.
FOREIGN KEY(CodeCoresp) REFERENCES correspondante(CodeCoresp) ON DELETE CASCADE ON UPDATE CASCADE,
Car je pense que même si vous supprimez physiquement un correspondant (ce qui doit être assez rare) il faut conserver quand même le courrier qu'il a émis.
Un courrier est une chose importante, il est très certainement nécessaire de le conserver en fonction d'autres paramètres fonctionnels (par exemple la durée légale ou réglementaire de conservation, ou le fait que ce courrier est une pièce d'un dossier non clôturé etc... )
Les règles de conservation et de purge sont des règles de gestion, c'est donc à vos experts métier de vous les communiquer
C'est un point extrêmement important : sauf erreur de ma part, le non respect de certaines règles de conservation des pièces règlementaires est d'ailleurs passible d'amendes et/ou de peines de prison, attention donc.

La contrainte suivante dans la table COURRIER_DEPART est erronée :
PRIMARY KEY (NumCour), UNIQUE NumOrdreCourDep (NumOrdreCourDep),
En effet, vous créez une contrainte PK et donc unique, sur un chrono dont vous avez expliqué précédemment que la valeur est remise à zéro chaque année !
Vos contraintes sur la table COURRIER_DEPART devraient donc être :
CONSTRAINT COUR_DEPART_PK PRIMARY KEY (NumCour) CONSTRAINT COUR_DEPART_FK1 FOREIGN KEY (NumCour) REFERENCES COURRIER(NumCour) ON DELETE CASCADE ON UPDATE CASCADE CONSTRAINT COUR_DEPART_AK1 UNIQUE (Annee, NumOrdreCourDep )
Il faut évidemment faire de même avec les courriers en arrivée
Attention : la syntaxe du DDL varie en fonction des SGBD, à adapter donc à votre cas

Ensuite, votre index doit porter sur des colonnes de la table, il faut donc que la colonne année soit présente dans les tables COURRIER_DEPART et COURRIER_ARRIVEE
vous créerez les index correspondant à votre recherche, c'est à dire année+séquence, comme suit (à adpater là aussi selon le SGBD) :
CREATE UNIQUE INDEX IX1_COUR_DEP ON COURRIER_DEPART (Annee ASC, NumOrdreCourDep ASC)

**gautier1er** · 22/09/2017, 15h12

Merci

Voici une fonction qui génère le numéro d’ordre du courrier départ

Code php :

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
 
      function    referenceCourrierDepart()
       {
            $bdd=connection_db();
         //on commpte le nombre d'enregistrement dans les courriers depart de l'année en cours
       $req=$bdd->prepare('SELECT COUNT(NumOrdreCourDep)
       FROM  courrierDepart
       WHERE  AnneeCD=?');
       $req->execute(array(date('Y')) );      //l'année en cours
         $nbreLigne=$req->rowCount();
         if($nbreLigne==0)
         {
           //(s'il n'ya pas de courrier départ enregistré dans l'année en cours ) alors le n° d'ordre du courrier aura la valeur 1
          $id=1;
         }
         else{
           //dans le cas contraire  on recupère le dernier n°d'ordre du courrier départ
          $id=$nbreLigne+1;
 
          }
 
 
 
           return $id;
       }

**SQLpro** · 24/09/2017, 11h17

Envoyé par gautier1er

Merci

Voici une fonction qui génère le numéro d’ordre du courrier départ

Code php :

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

1
2
 
      function    referenceCourrierDepart()...

Votre fonction est stupide car elle ne supporte pas les accès concurrents. En effet, si deux utilisateurs lancent cette même fonction au même moment, vous obtiendrez le même numéro.

Si vous voulez être sûr d'obtenir un numéro unique, il faut écrire une procédure stockée qui calcule le n° en verrouillant de manière exclusive tout accès à la table. Comme un verrouillage exclusif total de la table est la pire des choses à faire, on utilise un compteur externe à la table (SEQUENCE par exemple) ou interne (IDENTITY), ces deux mécansimes étant normalisés.

A +

**gautier1er** · 04/10/2017, 10h57

Je vais utiliser une procédure stockée pour calculer le numéro d’ordre,mais j’aimerai savoir aussi , pour les requêtes d’insertion , de suppression et de modification dans les tables : courrier , courrier_depart et courrier_arrivee , j’utilise aussi les procédures stockées ?

Quelques requêtes en SQL concernant MERISE 2

Langage SQL

Discussions similaires

Partager

Partager