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

**fsmrel** · 03/03/2024, 00h52

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 - 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
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 - Visualiser dans une fenêtre à part

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 - Visualiser dans une fenêtre à part

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 - 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
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 - 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
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 - 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
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 - Visualiser dans une fenêtre à part

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 - Visualiser dans une fenêtre à part

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

Table Matiere :

Code :

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

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

Table Enseigner :

Code :

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

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 - Visualiser dans une fenêtre à part

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 - Visualiser dans une fenêtre à part

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...

**CinePhil** · 03/03/2024, 22h01

Bonsoir François,

Je n'ai pas analysé ton code en détail.
Il me semble que ce que tu proposes pour SQL Server n'est pas possible encore pour MySQL et MariaDb. Sauf méconnaissance des dernières évolutions de ma part, il est impossible sur ces SGBD d'écrire un insert sur une vue. Et sur PostgreSQL ? Je ne sais pas.

La méthode que j'ai appliquée pour une application utilisant MariaDB était celle de la procédure d'insertion ou d'update, selon le même principe : avec les données passées à la procédure d'insertion, on commence par contrôler si le prof et la matière existent déjà dans les tables idoines en récupérant, le cas échéant, leur identifiant. Ensuite, on démarre une transaction. Si les deux sont déjà là, on se contente d'insérer dans la table associative, sinon on insère ce qui manque (le prof, la matière ou les deux) en récupérant leur identifiant généré par auto-incrément (LAST_INSERT_ID) puis on insère ces derniers dans la table associative. Enfin on commit si tout s'est bien passé, on rollback s'il y a un souci avec un beau message d'erreur en clair à l'attention du programme applicatif puis on ferme la transaction.

Selon les données objets de l'insertion (parce que, bien, sûr le cas prof / matière est un exemple mais ça peut être plus complexe que ça) on en profite pour vérifier dans la procédure que les données ne soient pas déconnantes... une date de naissance du prof, par exemple ou, dans ton exemple, un matricule qui soit cohérent avec le format de celui-ci, voire en le générant automatiquement.

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.

**JeitEmgie** · 03/03/2024, 23h01

Une solution qui nécessite une étape DISABLE TRIGGER n'est pas une bonne solution.
Pour info, avec ORACLE on peut résoudre ce genre de problème assez simplement un COMPOUND TRIGGER sur la table d'intersection, 2 tables qui contiendront les compteurs et initialisées par des triggers AFTER INSERT et les contraintes sur les cardinalités respectives qui seront définies DEFERRABLE INITIALLY DEFERRED, et c'est ainsi trivial d'implémenter une cardinalité différente pour chaque matière et/ou chaque professeur.
Notez quand même que dans le monde réel, la relation d'intersection contiendrait plus que probablement une notion d'intervalle de temps qui rendrait l'exercice encore plus amusant...

**fsmrel** · 04/03/2024, 00h10

Bonsoir,

Envoyé par Karadoc

Le souci des cardinalités (1,n) est que, la plupart du temps, quand on creuse elles vont plutôt être d'ordre (0,n).

On est bien d’accord ! Après 50 ans de modélisation, mise en oeuvre des base de données, audits et barouds en tous genres dans tous les secteurs d’activité, j’ai essentiellement eu à me frotter aux cardinalités (0,n). Mais il arrive que les malheureuses cardinalités (1,n/1,n) se manifestent timidement, donc j’ai voulu voir ce qu’il en était dans leur mise en oeuvre à l’étage SQL. Les cardinalités 1,n dans le contexte (1,n/1,1) pour leur part ne sont pas rares, en effet elles s’imposent par exemple dans le cas des factures et de leurs lignes, car en l’occurrence les cardinalités (0,n/1,1) n’ont pas de sens.

**fsmrel** · 04/03/2024, 01h27

Bonsoir,

Envoyé par JeitEmgie

Une solution qui nécessite une étape DISABLE TRIGGER n'est pas une bonne solution.

Etape brève s'il en est, mais je souhaiterais qu’un spécialiste comme SQLpro fournisse une solution alternative.

Envoyé par JeitEmgie

Notez quand même que dans le monde réel, la relation d'intersection contiendrait plus que probablement une notion d'intervalle de temps qui rendrait l'exercice encore plus amusant...

Je suis désolé, mais la dernière fois que j’ai utilisé Oracle, c’était à la fin des années quatre-vingt...

A part çà, qu’est-ce pour Oracle une relation d’intersection ? Dans le cadre du Modèle Relationnel de Données, je sais ce qu’est une relation, ce qu’est l’intersection de deux relations (r1 INTERSECT r2), mais sorti de là je donne ma langue au chat...
Par ailleurs, quel rôle jouerait l’intervalle de temps dans cette affaire ? Ce rôle est-il comparable avec celui de l’intervalle de temps qui est étudié (paragraphes 6.3.2,6.3.3) dans le cadre du Modèle Relationnel de Données ?

**fsmrel** · 04/03/2024, 01h38

Bonsoir Philippe,

Envoyé par CinePhil

On ne dit jamais assez combien ces procédures d'insertion, mise à jour, suppression de données peuvent être puissantes.

Hilarion ne pourra pas violer les règles ?

**JeitEmgie** · 04/03/2024, 08h39

Envoyé par fsmrel

Bonsoir,

Etape brève s'il en est, mais je souhaiterais qu’un spécialiste comme SQLpro fournisse une solution alternative.

Je suis désolé, mais la dernière fois que j’ai utilisé Oracle, c’était à la fin des années quatre-vingt...

A part çà, qu’est-ce pour Oracle une relation d’intersection ? Dans le cadre du Modèle Relationnel de Données, je sais ce qu’est une relation, ce qu’est l’intersection de deux relations (r1 INTERSECT r2), mais sorti de là je donne ma langue au chat...
Par ailleurs, quel rôle jouerait l’intervalle de temps dans cette affaire ? Ce rôle est-il comparable avec celui de l’intervalle de temps qui est étudié (paragraphes 6.3.2,6.3.3) dans le cadre du Modèle Relationnel de Données ?

**JeitEmgie** · 04/03/2024, 08h49

Envoyé par fsmrel

A part çà, qu’est-ce pour Oracle une relation d’intersection ?

çà n'a rien à vois avec ORACLE : votre table "enseigner"... pourrait contenir des dates debut-fin pour exprimer d'un prof a enseigné une matière dans cet interval de temps,
cela pimenterait un peu le problème, s'il faut que les cardinalités soient respectées pour chaque moment dans le temps.

Ce qui est spécifique à ORACLE c'est la possibilité d'avoid des contraintes "DEFERRABLE INITIALLY DEFERRED" == qui ne sont validées qu'au COMMIT, je suis pas certain que ce soit dispo dans SQLServer, et cette fonctionnalité facilite grandement l'implémentation.

**Karadoc** · 03/03/2024, 22h43

Bonsoir,
Le souci des cardinalités (1,n) est que, la plupart du temps, quand on creuse elles vont plutôt être d'ordre (0,n). Soit pour des raisons pratiques (par exemple, on peut se retrouver avec une vacance d'enseignant et donc avoir une matière enseignée par personne ; au contraire, on peut avoir un enseignant surnuméraire à qui il va falloir trouver un cours par un jeu de réaffectations), soit pour des raisons techniques (dans mon logiciel de planning, il faut bien que je crée d'abord l'enseignant, puis le cours, puis que je rattache l'un à l'autre).
L'utilisation de triggers est très bien au niveau purement technique mais, dans la confrontation à la réalité, on peut se retrouver à avoir d'autres contraintes (par exemple, une fiche enseignant va nécessiter des informations qui vont au-delà de ce qui va être saisissable depuis l'enregistrement d'un cours).
Après, j'admets qu'il y a des situations où on peut effectivement se retrouver avec des cardinalités (1,n), mais moins dans des modélisations du réel que dans des modélisations de mécanismes purement techniques (où on n'est pas liés à des considérations organisationnelles humaines ^^).
En tout cas, merci pour le tuto et les modalités de mise en œuvre !

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

Schéma

Vue hybride

Discussions similaires

Partager

Partager