ALM - Respect au niveau SQL des cardinalités 1,n du MCD merisien,

Un article de fsmrel

Le 2 mars 2024 à 23:52, par fsmrel

88PARTAGES

Bonsoir,

Je traite ici des cardinalités 1,n de Merise, lesquelles ne sont pas souvent respectées quand on déboule au niveau logique, c’est-à-dire dans l’univers des tables SQL.

Prenons l’exemple suivant dans lequel des professeurs enseignent des matières, avec les contraintes suivantes :

— un professeur enseigne au moins une matière ;

— une matière est enseignée par au moins un professeur.

MCD correspondant, créé avec Looping, gracieusement proposé par le professeur Patrick Bergougnoux (encore une fois, merci Paprick !) :

Code SQL des tables correspondantes :

Code SQL :

Sélectionner tout

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
CREATE TABLE Prof 
( 
   profId SMALLINT NOT NULL 
 , profMatricule CHAR(5) NOT NULL 
 , profNom VARCHAR(24) NOT NULL 
 , CONSTRAINT Prof_PK PRIMARY KEY(profId) 
 , CONSTRAINT Prof_AK UNIQUE(profMatricule) 
); 
  
CREATE TABLE Matiere 
( 
   matiereId SMALLINT NOT NULL 
 , matiereCode VARCHAR(5) NOT NULL 
 , matiereNom VARCHAR(24) NOT NULL 
 , CONSTRAINT Matiere_PK PRIMARY KEY(matiereId) 
 , CONSTRAINT Matiere_AK1 UNIQUE(matiereCode) 
 , CONSTRAINT Matiere_AK2 UNIQUE(matiereNom) 
); 
  
CREATE TABLE Enseigner 
( 
   profId SMALLINT NOT NULL 
 , matiereId SMALLINT NOT NULL 
 , CONSTRAINT Enseigner_PK PRIMARY KEY(profId, matiereId) 
 , CONSTRAINT Enseigner_Prof_FK FOREIGN KEY(profId) REFERENCES Prof(profId) 
 , CONSTRAINT Enseigner_Matiere_FK FOREIGN KEY(matiereId) REFERENCES Matiere(matiereId) 
);

Pour que les contraintes soient systématiquement respectées, on ne peut pas se contenter de la séquence suivante :

— (a) Créer des profs (table Prof) ;

— (b) Créer des matières (table Matiere) ;

— (c) Créer les liens profs/matières (table Enseigner).

Pour ma part, je préfère en passer par des triggers.

Par exemple, un 1er trigger aura pour objet de ventiler dans les tables les données présentées à une vue créée à cet effet.

Soit ProfMatiere_V le nom de cette vue. Sa structure est définie selon le code suivant :

Code SQL :

Sélectionner tout

1
2
3
4
5
6
7
create view ProfMatiere_V 
(profId, profMatricule, profNom, matiereId, matiereCode, matiereNom) 
as  
select e.profId, profMatricule, profNom, e.matiereId, matiereCode, matiereNom  
from Prof as p  
     join Enseigner as e on p.profId = e.profId 
     join Matiere as m on e.matiereId = m.matiereId ;

Quelques insertions dans la vue :

Code SQL :

Sélectionner tout

1
2
3
4
5
6
7
8
insert into ProfMatiere_V 
values 
   (1, 'fenau', 'Fernand', 1, 'phy', 'physique') 
 , (1, 'fenau', 'Fernand', 2, 'chm', 'chimie') 
 , (2, 'ravol', 'Raoul', 2, 'chm', 'chimie') 
 , (2, 'ravol', 'Raoul', 3, 'maths', 'mathématiques') 
 , (3, 'pat', 'Patricia', 4, 'mus', 'musique') 
;

Ainsi, Fernand enseigne la physique et la chimie, Raoul enseigne pour sa part les maths et la chimie, tandis que Patricia enseigne la musique. On constate à cette occasion certaines redondances, mais ceci serait l’objet d’un autre débat.

Comme je n’ai plus accès à DB2 depuis au moins 20 ans, j’utilise ici SQL Server. Le trigger qui suit a pour mission de ventiler dans les tables concernées les lignes proposées à la vue.

Code SQL :

Sélectionner tout

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
CREATE TRIGGER ProfMatiere_instead_Tr ON ProfMatiere_V INSTEAD OF INSERT AS 
  
begin 
  declare @nProf as smallint ; 
  declare @nMatiere as smallint ; 
  
  declare @tableProf table  
          (  
              profId smallint 
            , profMatricule varchar(24) 
            , profNom varchar(24) 
          ) ;  
  declare @tableMatiere table  
          (  
              matiereId smallint not null 
            , matiereCode varchar(5) not null 
            , matiereNom varchar(24) not null 
          ) ;  
  declare @tableEnseigner table  
          (  
              profId smallint not null 
            , matiereId smallint not null 
          ) ;  
  
  insert into @tableProf 
    select distinct i.profId, i.profMatricule, i.profNom  
    from inserted as i join Prof as p on i.profId = p.profId 
  
  set @nProf = (select count(a.profId)  
                from Prof as a join @tableProf as b on a.profId = b.profId 
               ) ; 
  
-- pour debug : 
  print '@nProf = ' + cast(@nProf as varchar) ; 
  
  if @nProf = 0 
    begin ; 
      disable trigger Prof_after_insert on Prof ; 
      -- print 'insert into Prof' ;      
      insert into Prof  
        select distinct profId, profMatricule, profNom  
        from inserted ; 
      enable trigger Prof_after_insert on Prof ; 
    end 
  
  insert into @tableMatiere 
    select distinct i.matiereId, i.matiereCode, i.matiereNom  
    from inserted as i join Matiere as m on i.matiereId = m.matiereId 
  
  set @nMatiere = (select count(a.matiereId)  
                   from Matiere as a  
                   join @tableMatiere as b on a.matiereId = b.matiereId 
                  ) ; 
  
-- pour debug : 
  print '@nMatiere = ' + cast(@nMatiere as varchar) ; 
  
  if @nMatiere = 0 
    begin ; 
      disable trigger Matiere_after_insert on Matiere ; 
      -- print 'insert into Matiere' ;      
      insert into Matiere  
        select distinct matiereId, matiereCode, matiereNom  
        from inserted ; 
      enable trigger Matiere_after_insert on Matiere ; 
    end 
  
  insert into Enseigner 
    select profId, matiereId 
    from inserted ; 
  
print 'the end!' ; 
  
end

Quelques commentaires :

Le code est très vraisemblablement simplifiable, mais peu importe ici. L’idée est la suivante :

Fernand fait l’objet de deux lignes et l’on ne peut pas le créer deux fois dans la table Prof, on se ferait jeter par le SGBD au motif de viol de la contrainte de clé primaire. On n’insère donc que la 1re ligne si Fernand n’existe pas encore dans la table, sinon on s’abstient.

Concernant les matières (table Matiere), le principe est le même.

Quant à la table Enseigner, on ne contrôle rien, le SGBD se chargera de faire respecter la contrainte de clé primaire (voire les contraintes d’intégrité référentielle).

Le trigger qu’on vient de définir n’est pas le seul à mettre en oeuvre. En effet, Hilarion Lefuneste pourrait violer les règles, d’où la nécessité de définir un trigger de contrôle des inserts dans la table Prof et un 2e trigger pour contrôle les inserts dans la table Matiere.

Exemple de trigger pour contrôler les inserts dans la table Prof :

Code SQL :

Sélectionner tout

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
CREATE TRIGGER Prof_after_insert on Prof after insert as 
begin 
  declare @n varchar(24) -- int ; 
  declare @tableProfInserted table (profNom varchar(24)) ;  
  declare @errId as int = 314115  -- numéro de l'erreur à afficher 
  declare @errTexte as varchar (255) -- message d'erreur 
  declare @profNom as varchar(24) 
  
  insert into @tableProfInserted (profNom) 
    select distinct profNom   
    from inserted 
    where profId not in (select profId from Enseigner) ; 
  
  set @n = (select count(profNom) from @tableProfInserted) ; 
  if @n > 0 
    begin 
      set @profNom = (select top (1) profNom from @tableProfInserted) 
      set @errTexte =  
          char(13) 
        + 'Un prof doit enseigner au moins une matière.'  
        + char(13) 
        + 'Le prof ''' 
        + @profNom 
        + ''' ne répond pas à cette obligation.'  
        + char(13) 
      ; throw @errId, @errTexte, 16 
    end ; 
end

Exemple de trigger pour contrôler les inserts dans la table Matiere :

Code SQL :

Sélectionner tout

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
CREATE TRIGGER Matiere_after_insert on Matiere after insert as 
begin 
  declare @n varchar(24) -- int ; 
  declare @tableMatiereInserted table (matiereNom varchar(24)) ;  
  declare @errId as int = 314116  -- numéro de l'erreur à afficher 
  declare @errTexte as varchar (255) -- message d'erreur 
  declare @matiereNom as varchar(24) 
  
  insert into @tableMatiereInserted (matiereNom) 
    select distinct matiereNom   
    from inserted 
    where matiereId not in (select matiereId from Enseigner) ; 
  
  set @n = (select count(matiereNom) from @tableMatiereInserted) ; 
  if @n > 0 
    begin 
      set @matiereNom = (select top(1) matiereNom from @tableMatiereInserted) 
      set @errTexte =  
          char(13) 
        + 'Une matière doit être enseignée par au moins un prof.'  
        + char(13) 
        + 'La matière ''' 
        + @matiereNom 
        + ''' ne répond pas à cette obligation.'  
        + char(13) 
      ; throw @errId, @errTexte, 16 
    end ; 
end

Mais ces deux triggers prennent le contrôle dès qu’on effectue un insert, aussi faut-il les désactiver (le plus brièvement possible, à cause d’Hilarion toujours à l’affût !) quand cet insert est effectué dans le trigger ProfMatiere_instead_Tr. A cet effet, on utilise l’instruction disable trigger juste avant insert et l’instruction enable trigger juste après l’insert. Ainsi, dans le cas de la table Prof :

Code SQL :

Sélectionner tout

1
2
3
4
5
disable trigger Prof_after_insert on Prof ; 
insert into Prof  
  select distinct profId, profMatricule, profNom  
  from inserted ; 
enable trigger Prof_after_insert on Prof ;

Après exécution de l’insert dans la vue ProfMatiere_V, au résultat :

Table Prof :

Code :

Sélectionner tout

1
2
3
Prof	profId	profMatricule	profNom 
	1	fenau		Fernand 
	2	ravol		Raoul

Table Matiere :

Code :

Sélectionner tout

1
2
3
4
Matiere	matiereId	matiereCode	matiereNom 
	1		phy		physique 
	2		chm		chimie 
	3		maths		mathématiques

Table Enseigner :

Code :

Sélectionner tout

1
2
3
4
5
Enseigner	profId	matiereId 
		1	1 
		1	2 
		2	2 
		2	3

Hilarion tentera de violer les règles, avec des instructions du genre :

Code SQL :

Sélectionner tout

1
2
insert into Prof values 
  (3, 'paulo', 'Paul') ;

Réaction du système :

Msg 314115, Niveau 16, État 16, Procédure Prof_after_insert, Ligne 30 [Ligne de départ du lot 237]

Un prof doit enseigner au moins une matière.
Le prof 'Paul' ne répond pas à cette obligation.

Dans le même genre :

Code SQL :

Sélectionner tout

1
2
insert into Matiere values 
  (99, 'info', 'informatique') ;

Msg 314116, Niveau 16, État 16, Procédure Matiere_after_insert, Ligne 27 [Ligne de départ du lot 237]

Une matière doit être enseignée par au moins un prof.
La matière 'informatique' ne répond pas à cette obligation.

Je n’ai fait ici qu’une 1re tentative à propos du respect des cardinalités 1,n/1,n, nul doute qu’Hilarion reviendra à la charge.

J’ai essayé de traiter des insert, mais concernant les update et delete, le chantier reste bien entendu à ouvrir.
Je n’ai pas traité du cas plus simple des cardinalités 1,n/1,1, mais ça viendra.

Par ailleurs les triggers Prof_after_insert et Matiere_after_insert sont du type AFTER INSERT, l’alternative INSTEAD OF est à étudier.

En tout cas, merci de votre indulgence...

Une erreur dans cette actualité ? Signalez-nous-la !

Votre nom : Votre e-mail :

Décrivez l'erreur que vous souhaitez porter à notre connaissance :

CREATE TABLE Produit 
( 
   RefProduit CHAR(3), 
   Designation VARCHAR(50) NOT NULL, 
   Prix SMALLINT NOT NULL, 
   CONSTRAINT Produit_PK PRIMARY KEY(RefProduit), 
   CONSTRAINT Produit_AK UNIQUE(Designation) 
); 
  
CREATE TABLE Devis 
( 
   NumDevis char(3) not null, 
   DateDevis DATE NOT NULL, 
   CONSTRAINT Devis_PK PRIMARY KEY(NumDevis) 
); 
  
CREATE TABLE LigneDevis 
( 
   RefProduit CHAR(3), 
   NumDevis char(3), 
   Quantite SMALLINT NOT NULL, 
   CONSTRAINT LigneDevis_PK PRIMARY KEY(RefProduit, NumDevis), 
   CONSTRAINT LigneDevis_Produit_FK FOREIGN KEY(RefProduit) REFERENCES Produit(RefProduit) ON DELETE CASCADE, 
   CONSTRAINT LigneDevis_Devis_FK FOREIGN KEY(NumDevis) REFERENCES Devis(NumDevis) ON DELETE CASCADE 
);

create view DevisVue 
  (NumDevis, DateDevis, RefProduit, Quantite) 
as  
  select x.NumDevis, x.DateDevis, y.RefProduit, y.Quantite   
  from Devis as x  
     join LigneDevis as y on x.NumDevis = y.NumDevis ;

CREATE FUNCTION devis_vue_fonction() RETURNS trigger AS $$ 
begin 
  if 
    (select count(NumDevis) 
     from devis 
     where NumDevis = new.NumDevis) = 0 
   then 
     insert into Devis 
       values (new.NumDevis, new.DateDevis)  ; 
  end if ; 
  
  insert into LigneDevis 
    values (new.NumDevis, new.RefProduit, new.Quantite) 
  ; 
  return null ; 
end ; 
$$ LANGUAGE plpgsql ;

CREATE FUNCTION Delete_Ligne() RETURNS TRIGGER AS '
BEGIN
  IF ((SELECT COUNT(*) FROM LigneDevis WHERE NumDevis=OLD.NumDevis) > 1)
  OR ((SELECT COUNT(*) FROM Devis WHERE NumDevis=OLD.NumDevis) = 0)
  THEN
     RETURN OLD;
  ELSE
     RAISE integrity_constraint_violation;
     RETURN NULL;
   END IF;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER T_Delete_Ligne BEFORE DELETE ON LigneDevis
FOR EACH ROW EXECUTE FUNCTION Delete_Ligne();

CREATE TABLE Facture(
   NumFac SMALLINT,
   DateF DATE,
   PRIMARY KEY(NumFac)
);

CREATE TABLE Produit(
   Réf CHAR(3),
   Désignation VARCHAR(50),
   Prix DECIMAL(19,4),
   PRIMARY KEY(Réf)
);

CREATE TABLE LigneFac(
   NumFac SMALLINT,
   Réf CHAR(3),
   Quantité INT,
   PRIMARY KEY(NumFac, Réf),
   FOREIGN KEY(NumFac) REFERENCES Facture(NumFac) ON DELETE CASCADE ON UPDATE CASCADE,
   FOREIGN KEY(Réf) REFERENCES Produit(Réf) ON DELETE CASCADE ON UPDATE CASCADE);

Identifiez-vous

Créer un compte

ALM - Respect au niveau SQL des cardinalités 1,n du MCD merisien,

Un article de fsmrel

Identifiant
Mot de passe

Mot de passe oublié ?

Identifiez-vous

Créer un compte

ALM - Respect au niveau SQL des cardinalités 1,n du MCD merisien, Un article de fsmrel

ALM - Respect au niveau SQL des cardinalités 1,n du MCD merisien,

Un article de fsmrel