Discussion :

[fsmrel]

Ave,

Comment mettre en oeuvre une contrainte imposant le respect des cardinalités 1,n définies dans un MCD ?

Je reviens sur les conclusions que l’on a pu tirer à partir d’une discussion antérieure traitant de ce sujet.

Avec SQL : sans insert dans une vue, sans trigger, sans bricolage, pas de salut, d’autant plus qu’Hilarion traîne en permanence ses guêtres pour ficher la zoubia. Les débats dans cette discussion furent animés, les baroudeurs s'y étaient mis (Paprick, escartefigue, CinePhil, SQLpro...)

Voyons comment les choses se passent dan le cadre du Modèle Relationnel de Données, c’est-à-dire avec Tutorial D.  

Prenons le cas des devis proposé par Patrick dans le post #46 de cette discussion (de plus de 70 échanges...) :
 
 

 

Le LDD SQL :

Code SQL :

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

1
2
3
4
5
6
CREATE TABLE Produit (RefProduit INT, Designation VARCHAR(50) NOT NULL, Prix INT NOT NULL, 
CONSTRAINT Produit_PK PRIMARY KEY (RefProduit)) ;
CREATE TABLE Devis (NumDevis INT, DateDevis DATE NOT NULL, CONSTRAINT Devis_PK PRIMARY KEY (NumDevis)) ;
CREATE TABLE LigneDevis (NumDevis INT, RefProduit INT, Quantite INT NOT NULL, CONSTRAINT LigneDevis_PK PRIMARY KEY (NumDevis, RefProduit),
CONSTRAINT LigneDevisProduit_FK FOREIGN KEY (RefProduit) REFERENCES Produit,
CONSTRAINT LigneDevisDevis_FK FOREIGN KEY (NumDevis) REFERENCES Devis ON DELETE CASCADE) ;

Le LDD façon Tutorial D :

Code Tutorial D :

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

1
2
3
4
 
VAR Produit BASE RELATION {RefProduit INT, Designation CHAR, Prix INT} KEY {RefProduit} ;
VAR Devis BASE RELATION {NumDevis INT, DateDevis CHAR} KEY {NumDevis} ;
VAR LigneDevis BASE RELATION {NumDevis INT, RefProduit CHAR, Quantite INT} KEY {NumDevis, RefProduit} ;

La contrainte imposant la cardinalité 1,n requise par le MCD :

Code Tutorial D :

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

CONSTRAINT CDL Devis {NumDevis} = LigneDevis {NumDevis} ;

Il s’agit d’une contrainte d’égalité faisant qu’un devis ne peut exister sans ligne de devis et qu’une ligne de devis ne peut exister sans devis. CDL est le nom de la contrainte, "Devis {NumDevis}" est la projection de Devis sur l’attribut NumDevis et "LigneDevis {NumDevis}" la projection de LigneDevis sur l’attribut NumDevis.

Il est inutile de doter LigneDevis d’une clé étrangère référençant Devis : la contrainte CDL fait office. Toutefois, une contrainte d’intégrité référentielle (nommée CLP ci-dessous) est nécessaire pour s’assurer qu’une ligne de devis référence un produit existant :

Code Tutorial D :

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

CONSTRAINT CLP LigneDevis {RefProduit} ⊆ Produit {RefProduit} ;

Testons ça avec un jeu d’essai.

Création de produits :

Code Tutorial D :

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

1
2
3
4
INSERT Produit RELATION 
  {  TUPLE {RefProduit 1, Designation "boulons de 8", Prix 100}
   , TUPLE {RefProduit 2, Designation "écrous", Prix 200}
  } ;

Tentons la création d’un devis :

Code Tutorial D :

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

1
2
INSERT Devis RELATION 
  {TUPLE {NumDevis 1, DateDevis "d01"}} ;

En l’absence d’une ligne de devis, il est évident que la contrainte CDL mettra en échec cette tentative...

Comme l’écrivait l’autre, que faire ? il est temps de parler de l’affectation multiple. Considérons l’instruction suivante :

Code Tutorial D :

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

1
2
3
4
5
6
7
8
9
10
11
12
   INSERT Devis RELATION 
  {
     TUPLE {NumDevis 1, DateDevis "d01"}
  }
 , 
   INSERT LigneDevis RELATION 
  {
     TUPLE {NumDevis 1, RefProduit 1, Quantite 100}
   , TUPLE {NumDevis 1, RefProduit 2, Quantite 150}
  }
 ;

On a deux affectations (par INSERT), la 1re portant sur Devis et la 2e portant sur LigneDevis.

Mais étant séparées par une virgule (que je fais rougeoyer), ces deux affectations, font partie d’une seule instruction, elle-même bornée par un point-virgule.
Ainsi, une instruction peut comporter un nombre quelconque d’affectations et elle est à considérer comme sémantiquement atomique (tout ou rien). Autrement dit, le système traite chaque affectation, disons séquentiellement, sans aucun contrôle (autre que syntaxique), jusqu’à ce que soit atteint le point-virgule marquant la fin de l’instruction. C’est seulement à ce moment-là que les contrôles d’intégrité de la base de données sont déclenchés pour vérification de la validité du résultat de cette instruction (immediate checking), et si l’intégrité de la base de données est violée, les effets l’affectant sont annulés et un message d’erreur est émis.

=> Bref, grâce à l’affectation multiple, le respect d’une contrainte telle que CDL ne pose aucun problème, et combien d’autres contraintes en seraient bénéficiaires ! ! 

Pour sa part, SQL méconnaît l’affectation multiple et c’est fort dommage.
Qu’il évolue ! que de simplifications en attendre, combien de triggers bons pour la poubelle !
 
A propos de la suppression des devis

Grâce à l’option CASCADE attachée à la clé étrangère LigneDevisDevis_FK définie plus haut, SQL permet que la suppression d’un devis entraîne la suppression des lignes de ce devis.

Avec Tutorial D, si on veut par exemple supprimer le devis 1 et ses lignes, on utilisera l’affectation multiple :

Code Tutorial D :

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

1
2
3
4
DELETE Devis WHERE NumDevis = 1
, 
DELETE LigneDevis WHERE NumDevis = 1
;

A suivre...

[fsmrel]

Ave,

Je reviens sur la discussion initiale.

Le MCD proposé était le suivant, avec deux cardinalités 1,n :

 

 

Dans le post #2, CinePhil écrit de façon très pertinente :

On ne dit jamais assez combien ces procédures d'insertion, mise à jour, suppression de données peuvent être puissantes. Au lieu que ce soit le programme applicatif qui interroge la BDD pour savoir si le prof et la matière sont déjà là et si, par exemple, le matricule n'est pas déjà attribué à un autre prof, ce qui fait autant d'allers-retours entre le programme et la BDD, avec les risques que la vérification fait à l'instant N ne soit plus valable à l'instant N+1 parce qu'une autre donnée à été insérée, il suffit que le programme applicatif envoie les données à la procédure et c'est cette dernière qui fait le boulot. L'intégrité des données est bien mieux assurée et il n'y a qu'un aller-retour entre application et SGBD.

D’aucuns écrivent qu’il est rare qu’une association ait plus d’une patte 1,n : je n’en disconviens pas, mais on en trouve. Je fournis ici le MCD représenté dans la figure 7.37 de l’ouvrage de D. Nanci et B. Espinasse Ingénierie des systèmes d'information - Merise deuxième génération (paragraphe II-D-3-s-i. Contraintes d'inclusion de relations sur d'autres relations) :
 

Revenons au 1er MCD. L’association Enseigner est dotée de deux pattes, chacune porteuse d’une cardinalité 1,n. je ne reviens pas sur le LDD SQL, mais passe au LDD Tutorial D :

Code Tutorial D :

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

1
2
3
4
 
VAR Matiere BASE RELATION {matiereId INT, matiereCode CHAR, matiereNom CHAR} KEY {matiereId} KEY {matiereCode} ;
VAR Prof BASE RELATION {profId INT, profMatricule CHAR, profNom CHAR} KEY {profId} KEY {profMatricule} ;
VAR Enseigner BASE RELATION {profId INT, matiereId INT} KEY {profId INT, matiereId} ;

Les contraintes imposant les cardinalités 1,n requises par le MCD :

Code Tutorial D :

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

1
2
CONSTRAINT EM Enseigner {matiereId} = Matiere {matiereId} ;
CONSTRAINT EP Enseigner {profId} = Prof {profId} ;

Il est inutile de doter Enseigner de clés étrangères : les contraintes EM et EP font office.

Un jeu d’essai avec affectation multiple :

Code Tutorial D :

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

1
2
3
4
5
INSERT Matiere RELATION 
{TUPLE {matiereId 1, matiereCode "phy", matiereNom "physique"},
 TUPLE {matiereId 2, matiereCode "chm", matiereNom "chimie"},
 TUPLE {matiereId 3, matiereCode "maths", matiereNom "mathématiques"},
 TUPLE {matiereId 4, matiereCode "mus", matiereNom "musique"}} ;

Code Tutorial D :

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

1
2
3
4
INSERT Prof RELATION 
{TUPLE {profId 1, profMatricule "fenau", profNom "Fernand"},
 TUPLE {profId 2, profMatricule "ravol", profNom "Raoul"},
 TUPLE {profId 3, profMatricule "pat", profNom "Patricia"}} ;

Code Tutorial D :

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

1
2
3
4
5
6
INSERT Enseigner RELATION 
{TUPLE {profId 1, matiereId 1},
 TUPLE {profId 1, matiereId 2},
 TUPLE {profId 2, matiereId 2},
 TUPLE {profId 2, matiereId 3},
 TUPLE {profId 3, matiereId 4}} ;

Cela dit, l’existence des contraintes EM et EP requiert un certain doigté lors des mises à jour. Le jeu d’essai qui précède peut paraître un peu lourd, car on charge la base de données en une fois. On peut préférer un chargement en plusieurs fois, mais des précautions sont à prendre. Par exemple (la base de données étant vide), si l’on veut se limiter à ne prendre en compte que Fernand enseigne la physique, cette matière n’existant pas encore, alors on codera (affectation multiple) :

Code Tutorial D :

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

1
2
3
4
5
6
INSERT Matiere RELATION {TUPLE {matiereId 1, matiereCode "phy", matiereNom "physique"}} 
,
INSERT Prof RELATION {TUPLE {profId 1, profMatricule "fenau", profNom "Fernand"}} 
,
INSERT Enseigner RELATION {TUPLE {profId 1, matiereId 1}} 
;

Si Raoul enseigne la chimie, là aussi on amorcera la pompe pour cette matière absente dans la base de données :

Code Tutorial D :

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

1
2
3
4
5
6
INSERT Matiere RELATION {TUPLE {matiereId 2, matiereCode "chm", matiereNom "chimie"}}
,
INSERT Prof RELATION {TUPLE {profId 2, profMatricule "ravol", profNom "Raoul"}}
,
INSERT Enseigner RELATION {TUPLE {profId 2, matiereId 2}}
;

Maintenant, si Fernand enseigne aussi la chimie, seule la variable relationnelle Enseigner est concernée :

Code Tutorial D :

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

INSERT Enseigner RELATION {TUPLE {profId 1, matiereId 2}} ;

Etc.

Si Fernand n’enseigne plus la chimie :

Code Tutorial D :

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

DELETE Enseigner WHERE profId = 1 AND matiereId = 2 ;

Si Fernand n’enseigne plus rien, il doit disparaître de la base de données (à condition bien sûr que les contraintes EM et EP soient respectées, c’est-à-dire qu’il reste au moins un prof enseignant la matière enseignée jusque là par Fernand) :

Code Tutorial D :

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

1
2
DELETE Prof WHERE profId = 1, 
DELETE Enseigner WHERE profId = 1 ;

Considérons le cas où la base de données est vide et insérons des n-uplets :.

Code Tutorial D :

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
INSERT Matiere RELATION 
{  TUPLE {matiereId 2, matiereCode "chm", matiereNom "chimie"}
 , TUPLE {matiereId 4, matiereCode "mus", matiereNom "musique"}
 , TUPLE {matiereId 5, matiereCode "grec", matiereNom "grec"}} 
,
INSERT Prof RELATION  
{  TUPLE {profId 2, profMatricule "ravol", profNom "Raoul"}
 , TUPLE {profId 3, profMatricule "pat", profNom "Patricia"} 
 , TUPLE {profId 4, profMatricule "tonio", profNom "Antoine"}} 
,
INSERT Enseigner RELATION 
{  TUPLE {profId 2, matiereId 2}
 , TUPLE {profId 2, matiereId 5}
 , TUPLE {profId 3, matiereId 4}
 , TUPLE {profId 3, matiereId 5}
 , TUPLE {profId 4, matiereId 2}
 , TUPLE {profId 4, matiereId 5}} 
;

Si l’on veut supprimer une matière, par exemple le grec, on codera :

Code Tutorial D :

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

1
2
3
4
DELETE Enseigner WHERE matiereId = 5
,
DELETE Matiere WHERE matiereId = 5
;

Mais attention, les professeurs qui enseignent cette matière doivent aussi en enseigner une autre, sinon la contrainte EM se manifestera...

Les cardinalités 1,n requièrent vraiment beaucoup de doigté et de vigilance de notre part lors des opérations de mise à jour...