IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)

Vous êtes nouveau sur Developpez.com ? Créez votre compte ou connectez-vous afin de pouvoir participer !

Vous devez avoir un compte Developpez.com et être connecté pour pouvoir participer aux discussions.

Vous n'avez pas encore de compte Developpez.com ? Créez-en un en quelques instants, c'est entièrement gratuit !

Si vous disposez déjà d'un compte et qu'il est bien activé, connectez-vous à l'aide du formulaire ci-dessous.

Identifiez-vous
Identifiant
Mot de passe
Mot de passe oublié ?
Créer un compte

L'inscription est gratuite et ne vous prendra que quelques instants !

Je m'inscris !

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

Le , par fsmrel

75PARTAGES

13  0 
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 !

Avatar de fsmrel
Expert éminent sénior https://www.developpez.com
Le 20/03/2024 à 13:12
Buenos dias,

Pour changer à nouveau, voyons voir avec PostgreSQL. Pour ne rien rater, j’ai installé la V16.

Mutatis mutandis, Je reprends, ce que j’ai proposé ici
 
(1) Création des tables :
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
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 
);

 
(2) Création de quelques produits :
 
Code SQL : Sélectionner tout
1
2
3
4
5
6
7
insert into Produit (RefProduit, Designation, Prix) 
  values 
    ('p01', 'bonbons', 100) 
  , ('p02', 'caramels', 200) 
  , ('p03', 'esquimaux', 300) 
  , ('p04', 'chocolats', 400) 
;

 
Select * from Produit :
Code : Sélectionner tout
1
2
3
4
5
"refproduit"  "designation"  "prix" 
 "p01"         "bonbons"      100 
 "p02"         "caramels"     200 
 "p03"         "esquimaux"    300 
 "p04"         "chocolats"    400

 
(3) Création de la vue comportant la jointure des tables Devis et LigneDevis
 
Code SQL : Sélectionner tout
1
2
3
4
5
6
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 ;
 
(4) Création de la fonction appelée par le trigger
 
Code SQL : Sélectionner tout
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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 ;

 
(5) Création du trigger utilisé pour la vue
 
Code SQL : Sélectionner tout
1
2
3
create trigger devis_vue_trigger instead of insert on DevisVue  
  for each row execute function devis_vue_fonction()  
;

 
(6) Insert dans la vue :
Code SQL : Sélectionner tout
1
2
3
4
5
6
insert into DevisVue (NumDevis, DateDevis, RefProduit, Quantite) 
  values 
    (1, '2000-01-01', 'p01', 100)  
  , (1, '2000-01-01', 'p02', 150) 
  , (2, '2000-01-02', 'p01', 200) 
;

 
Au résultat
 
Code SQL : Sélectionner tout
select * from Devis ;

 
 
 
Code SQL : Sélectionner tout
select * from LigneDevis ;

 
 
 
 
4  0 
Avatar de fsmrel
Expert éminent sénior https://www.developpez.com
Le 03/03/2024 à 23:10
Bonsoir,

Citation 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.
3  0 
Avatar de fsmrel
Expert éminent sénior https://www.developpez.com
Le 14/04/2024 à 0:34
Citation Envoyé par Paprick Voir le message
Hello !
Toujours aussi vigilant François !
J'ai une autre solution qui pourrait vous plaire : si le devis est supprimé, on accepte la suppression de toutes les lignes.
Code : Sélectionner tout
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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();
Je pense avoir testé tous les cas, et ça a l'air de bien fonctionner ...
Mais est-ce à l'épreuve des balles de François ?
J’ai essayé de dézinguer tout ça avec mon char Patton, d’abord avec les 3 mitrailleuses, puis avec le canon de 90 :

Caramba ! ton trigger a tenu le choc et en me faisant des pieds-de-nez qui plus est...

C’est toi le meilleur...
3  0 
Avatar de SQLpro
Rédacteur https://www.developpez.com
Le 24/04/2024 à 19:24
Citation Envoyé par fsmrel Voir le message
...
Je ne suis pas fin connaisseur de PostgreSQL, mais je suis surpris par l’approche non ensembliste : en gros, le trigger se contente d’effectuer un « open/close » et balance les lignes une par une à la fonction à laquelle il fait appel.
D'autant que l'approche ensembliste peut être détournée par un curseur pour faire du ligne à ligne, mais pas l'inverse !!!
Mais tu n'as pas vu le meilleur si l'approche ligne à ligne de PostGreSQL... Démo :

Code : Sélectionner tout
1
2
3
CREATE TABLE T (C INT PRIMARY KEY);
INSERT INTO T VALUES (1), (2), (3);
UPDATE T SET C = C + 1;
Jolie réponse !!!!

Citation Envoyé par fsmrel Voir le message


@Fred,

En plus de l’approche archaïque, je subodore que sur le plan des performances ça doit être moins bon qu’avec SQL Server... Qu’en penses-tu ?

Accessoirement, MySQL (create trigger ... for each row) et PostgreSQL même combat (ligne par ligne).

Au plaisir
Ben oui... ça fait des années que je dis que PG est un veau ! À la caisse de retraite des cadres Agic Arco ils ont remplacé DB2 par PostGreSQL... Bilan des opérations la base qui faisait 2 To sous DB2 (compression activée) est passé à près de 10 To sous PG... Devenue tellement lente que embauche massive de dev et DBA pour tenter de résoudre un tonneau des danaïdes ! Un vrai scandale car cela coûte à nos retraites....

A +
3  0 
Avatar de Karadoc
Membre actif https://www.developpez.com
Le 03/03/2024 à 21:43
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 !
2  0 
Avatar de SQLpro
Rédacteur https://www.developpez.com
Le 04/03/2024 à 11:18
Je rappelle que normalement, le développement des applications ne devrait faire qu'accéder à des vues et des procédures et que les vues peuvent êtres mises à jour directement ou indirectement (déclencheurs INSTEAD OF au pire).
Ainsi la liste des factures devrait être une vue qui présente uniquement les factures ayant au moins une ligne de facturation...
Du genre :
Code : Sélectionner tout
1
2
3
4
5
6
7
CREATE VIEW V_FACTURE 
AS
SELECT *
FROM   T_FACTURE AS F
WHERE  EXISTS(SELECT 1/0 
              FROM   T_LIGNE_FACTURE AS LF
              WHERE  F.FAC_ID = LF.FAC_ID);
Point n'est donc besoin des usines à gaz d'Oracle pour ou des contraintes deferred que Chris Date qualifie d'abomination à cause dans ce cas de l'impossibilité pour l'optimiseur de faire de l'optimisation sémantique notamment... (SQL and the Relational Theory - Third Ed; O'Reilly 2015 pages 296 à 299)...

Cette vue optimale des factures correctes, doit évidemment être complétée par celle des factures annulées comme suit :

Code : Sélectionner tout
1
2
3
4
5
6
7
CREATE VIEW V_FACTURE_ANNULEE
AS
SELECT *
FROM   T_FACTURE AS F
WHERE  NOT EXISTS(SELECT 1/0 
                  FROM   T_LIGNE_FACTURE AS LF
                  WHERE  F.FAC_ID = LF.FAC_ID);
Surtout que l'id de facture doit être continu et sans trou... D’où l'interdiction du DELETE qui lui doit être contrôlé par déclencheur !

En effet, dans la vraie vie (celle du papier et du crayon d’antan que beaucoup ont oublié), un facturier est un carnet de feuille de papier duplicata ou triplicata, dans lequel les factures sont pré numérotées. EN cas d'erreur dans l'élaboration d'une facture avec son crayon, on ne supprime pas les feuilles. On les laisse et on met en travers des pages, la mention "annulée"...

A +
2  0 
Avatar de fsmrel
Expert éminent sénior https://www.developpez.com
Le 04/03/2024 à 13:15
Bonjour,

Citation Envoyé par JeitEmgie
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.


Dès que le temps est partie prenante (cf. le paragraphe 6.4 ici, on doit ici modéliser les règles suivantes :
 
— Un professeur enseigne telle matière depuis telle date ;
 
— Un professeur a enseigné telle matière entre telle date et telle date.
 
D’où l’enrichissement du MCD en conséquence :
 
 
 
Je ne vois donc pas en quoi le respect des cardinalités (1,n/1,n) est impliqué. La prise en compte des historiques est vieille comme l’informatique de gestion et j’y fus confronté dès 1965 quand j’avais programmé la mise à jour des fichiers du personnel de l’Assistance Publique à Paris.
Pour la petite histoire, mes programmes ont été opérationnels pendant 30 ans. C’était le bon temps...
2  0 
Avatar de SQLpro
Rédacteur https://www.developpez.com
Le 06/03/2024 à 13:04
En fait dans le cas de SQL Server les déclencheurs peuvent être récursif et réentrant.

Un trigger est dit récursif si le code du déclencheur lance la même action sur la même table ou vue.

Par exemple un déclencheur UPDATE sur une table des clients qui ferait un UPDATE sur la table des clients...

Mais par défaut les bases de données créées par SQL Server ont un paramètre "recurive triggers" défini à OFF. Il n'y a donc pas récursivité, sauf modification dudit paramètre et dans ce cas la limityation est par construction de 32 niveau et peut être suvie par @@NESTLEVEL.

La réentrance est pilotée par un autre paramètre de niveau instance "nested triggers" (modifiable via la procédure sp_configure) qui lui est à 1 (pour "ON". Ceci concerne des triggers que je qualifie de ping-pong (la mise à jour du client déclenche un trigger qui met à jour une facture qui déclenche un trigger qui met à jour le client...).

On peut donc désactiver cette fonctionnalité au niveau de l'instance....
Pourquoi au niveau de l'instance, tout simplement pour permettre à des déclencheurs d'atteindre les données d'une autre base (par exemple pour gérer une intégrité référentielle procédurale et non plus déclarative entre une base de données centrale et différentes bases locales - une base constiuant un référentiel pour les différentes autres bases...).

A +
2  0 
Avatar de Paprick
Membre émérite https://www.developpez.com
Le 06/03/2024 à 23:50
Bonsoir à tous,

Voici un sujet particulièrement intéressant !!!
En effet, lors du passage du MCD au MLD/LDD, on ne fait guère cas de la différence entre une cardinalité 1,n ou 0,n ...
J'aimerais ramener le sujet à un exemple plus simple et particulièrement significatif. En effet, autant il est fréquent qu'il soit acceptable de négliger cette différence (par exemple, créer un client avant de lui créer ses factures, ou créer un enseignant avant de lui affecter ses cours, ...), autant il n'est pas envisageable qu'une facture puisse exister sans au moins une ligne de facture.
J'aimerai donc traiter cette situation avec mes étudiants, et comme vous vous êtes lancés de manière efficace sur le sujet, pourriez-vous me proposer une solution la plus simple et la plus générique possible (et donc acceptable, entre autre, par MySQL) pour le cas standard suivant :

Looping génère le LDD suivant :
Code : Sélectionner tout
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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);
La CASCADE en cas de suppression de la facture est bien prise en compte par Looping, mais l'inverse reste à faire, et surtout l'insertion obligatoire d'une ligne n'est pas traitée.
Bref, on est en plein dans votre sujet, mais avec un seul côté à traiter, car un produit peut exister même s'il n'existe plus dans la facture.
J'ai donc rajouter une règle associée à LigneFac au sein de laquelle du code SQL peut être saisi afin que Looping l'intègre ensuite dans le LDD.

Je suis donc en quête d'une solution la plus simple et pédagogique possible pour nos jeunes étudiants avides de savoir (si, si, on peut rêver ), mais sans grande maîtrise de la bête SQL ?
Merci !
2  0 
Avatar de escartefigue
Modérateur https://www.developpez.com
Le 07/03/2024 à 8:23
Dans le cas canonique d'une facture qui ne saurait exister sans lignes de facture, j'opte pour une procédure stockée qui insère entête et détail, la transaction valide l'ensemble ou ne valide rien.
Comme les trous de numérotation sont interdits, le numéro de facture est piloté par une table de compostage (pas d'identifiants attribués par le SGBD, dont chacun sait qu'il peuvent présenter des "trous") et bien évidemment, la suppression physique est interdite : pas de DELETE qui vaille.
Si une facture doit être annulée, alors un statut est positionné, mais entête comme détails sont conservés dans la base.
2  0