Discussion :

[Paprick]

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

[escartefigue]

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.

[SQLpro]

J'ai simplifié, amélioré et corrigé le code de l'exemple proposé par FSMrel...

1) création de la base :

Code :

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

1
2
3
4
5
CREATE DATABASE DB_FSMREL
GO

USE DB_FSMREL;
GO

2) création des tables :

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
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)
);

--> Fred SQLpro : manque une contrainte dans cette dernière table !
ALTER TABLE Enseigner
   ADD UNIQUE (profId, matiereId);
GO
--< fin ajout SQLpro

3) création de la vue :

Code :

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

1
2
3
4
5
6
7
8
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 ;
GO

4) déclencheur bloquant les matières :
Correction pour ajouter les aspects ensemblistes...

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
CREATE OR ALTER TRIGGER Matiere_after_insert 
  ON Matiere 
  after insert 
AS
IF NOT EXISTS(SELECT *
              FROM   inserted AS i 
		     LEFT OUTER JOIN Enseigner AS E
			 ON i.matiereId = E.matiereId
		     WHERE  E.matiereId IS NULL)
   RETURN; --> jusqu'ici tout va bien !
DECLARE @ERRMSG VARCHAR(2048) = 'Les matières suivantes ne sont enseignées par aucun prof';
SELECT  @ERRMSG = @ERRMSG +  ', ' + M.matiereNom + ' ('+ M.matiereCode +')'
FROM  Matiere AS M
      JOIN inserted AS i 
	     ON M.matiereId = i.matiereId
      LEFT OUTER JOIN Enseigner AS E
		 ON i.matiereId = E.matiereId
WHERE  E.matiereId IS NULL;
throw 66666, @ERRMSG, 16
GO

CREATE OR ALTER TRIGGER Prof_after_insert 
  ON Prof 
  after insert 
AS
IF NOT EXISTS(SELECT *
              FROM   inserted AS i 
			         LEFT OUTER JOIN Prof AS P
					    ON i.profId = P.profId
		      WHERE  P.profId IS NULL)
   RETURN; --> jusqu'ici tout va bien !
DECLARE @ERRMSG VARCHAR(2048) = 'Les profs suivants n''enseignent aucune matiere';
SELECT  @ERRMSG = @ERRMSG +  ', ' + P.profNom + ' ('+ P.profMatricule +')'
FROM  Prof AS P
      JOIN inserted AS i 
	     ON P.profId = i.profId
      LEFT OUTER JOIN Enseigner AS E
		 ON i.profId = E.profId
WHERE  E.profId IS NULL;
throw 66666, @ERRMSG, 16
GO

5) déclencheur d'insertion sur la vue :

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 OR ALTER TRIGGER ProfMatiere_instead_Tr 
   ON ProfMatiere_V 
   INSTEAD OF INSERT 
AS
IF EXISTS(SELECT *
          FROM   inserted 
		  WHERE  matiereCode NOT IN (SELECT matiereCode FROM Matiere))
   DISABLE TRIGGER Matiere_after_insert ON Matiere;		  
INSERT INTO Matiere SELECT DISTINCT matiereId, matiereCode, matiereNom FROM inserted WHERE matiereCode NOT IN (SELECT matiereCode FROM Matiere);
IF EXISTS(SELECT *
          FROM   inserted 
		  WHERE  profMatricule NOT IN (SELECT profMatricule FROM Prof))
   DISABLE TRIGGER Prof_after_insert ON Prof;		
INSERT INTO Prof SELECT DISTINCT profId, profMatricule, profNom FROM inserted WHERE profMatricule NOT IN (SELECT profMatricule FROM Prof);
INSERT INTO Enseigner SELECT DISTINCT profId, matiereId FROM inserted;
ENABLE TRIGGER Matiere_after_insert ON Matiere;	
ENABLE TRIGGER Prof_after_insert ON Prof;
GO

6) insertion à travers la vue

Code :

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

1
2
3
4
5
6
7
8
9
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')
;
GO

A +

[SQLpro]

Bonjour,

Dans ce message, j’ai fourni un trigger ayant pour objet le contrôle des suppressions dans la table Enseigner.
 
Par la suite, j’ai évoqué la mise en oeuvre d’une procédure stockée. En fait, en attendant une solution optimale, élégante et tout ça, je propose de procéder ainsi :
 
Avant d’effectuer des delete dans la table Enseigner, l’utilisateur précise son intention. Si le prof et la matière sont à supprimer au cas où ils n’existent plus dans la table Enseigner, alors le trigger devra supprimer le prof dans la table Prof, ainsi que la matière dans la table Matiere : à cet effet, l’utilisateur précise son intention de la façon suivante, option 'cascade' dans une table de paramétrage (nommée Parametre ci-dessous).
Si le prof et la matière ne doivent pas être supprimés, alors le trigger devra annuler la suppression dans la table Enseigner (option 'no action' dans la table Parametre).
 
Exemple :
 
Table Parametre :
 

Code SQL :

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

1
2
3
4
5
CREATE TABLE Parametre
(
   action varchar(9) NOT NULL
 , constraint Parametre_VA check (action in ('cascade', 'no action')
) ;

 
Cette table ne contient pas de clé primaire, mais faudra trouver une astuce pour interdire qu’elle contienne plus d’une ligne (Hilarion traîne ses guêtres...) : à vot’ bon coeur, m’sieurs dames...

Code :

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

1
2
3
4
5
CREATE TABLE Parametre
(  action_id TINYINT NOT NULL PRIMARY KEY DEFAULT 1,
   action varchar(9) NOT NULL
 , constraint Parametre_VA check (action in ('cascade', 'no action')
) ;

[Paprick]

Bonjour Capitaine,

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.

Bien vu pour cette remarque de bon gestionnaire comptable !
Merci alors de considérer ce même exercice avec des devis ou des commandes .

[SQLpro]

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

Je ne suis pas du tout d'accord avec cette position. En effet :

1) les solutions d'auto incrémentations manuelles via tables de compteurs posent le même problème de trou possible en pire... Le mécanisme étant souvent bien moins protégé et souvent mal intégré...

2) dans tous les cas de trou il suffit d'avoir une table de nombre pour compenser les trous manquants...

Dans l'exemple que je montrais ici :
https://www.developpez.net/forums/d2.../#post12008908
Il suffit d'ajouter une jointure externe à droite avec la table des factures annulées pour les voir réapparaître...

A +

[fsmrel]

Merci Fred !

Je vais étudier ce que tu proposes ici.
 
Au sujet de la table Parametre, ta proposition consiste donc en l’ajout d’une clé primaire :
 

Code SQL :

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

1
2
3
4
5
CREATE TABLE Parametre
(  action_id TINYINT NOT NULL PRIMARY KEY DEFAULT 1,
   action varchar(9) NOT NULL
 , constraint Parametre_VA check (action in ('cascade', 'no action')
) ;

 
Le problème est qu’au lieu de coder :
 

Code SQL :

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

insert into Parametre values ('cascade') ;

 
L’utilisateur doit désormais coder :

Code SQL :

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

insert into Parametre values (1,'cascade') ;

 
C’est-à-dire fournir une valeur pour la clé primaire, or comme beaucoup, je préférerai que cela lui soit transparent (style IDENTITY).
 
Dans cette optique, pour le moment je n’ai que la solution trigger :
 

Code SQL :

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

1
2
3
4
5
CREATE TABLE Parametre
(
   action varchar(9) NOT NULL
 , constraint Parametre_VA check (action in ('cascade', 'no action')
) ;

 

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
CREATE TRIGGER ParametreAfterInsert ON Parametre after insert AS
begin 
  declare @kount as tinyint  ;
  declare @errId as int = 314120  -- numéro de l'erreur à afficher
  declare @errTexte as varchar (255) -- message d'erreur
 
  set @errTexte = 'La table Parametre doit contenir au moins et au plus une ligne' 
  set @kount = (select count(*) from Parametre) ;
 
  if @kount <> 1
    begin
      ; throw @errId, @errTexte, 16
    end
end ;
go

 
Fred, qu’aurais-tu de moins lourd en magasin ?
Encore merci de l’intérêt que tu portes au sujet !

[fsmrel]

pas d'identifiants attribués par le SGBD, dont chacun sait qu'il peuvent présenter des "trous".

Oui, mais comme chantait Gainsbourg (RIP), « des trous d’première classe, des trous s’seconde classe »

Et je trouve qu’un trou d’première classe ça vous pose quand même son trou, comme être de garenne, ça vous pose un lapin…

Pardon pour ce troll, mais j’ai besoin de décompresser...

[fsmrel]

--> Fred SQLpro : manque une contrainte dans cette dernière table !
ALTER TABLE Enseigner
ADD UNIQUE (prof Id, matiereId);
GO
--< fin ajout SQLpro

Quelle valeur ajoutée, puisque la clé primaire est définie avec les mêmes colonnes, prof Id, matiereId...
 
Dans mon 1er post, ici, j’ai précisé qu’Hilarion tenta de violer les règles, avec cette instruction :
 

Code SQL :

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

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

 
Et le système réagit :
 

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.

 
Mais Hilarion réussit son coup quand, Fred, on met en oeuvre ton trigger Prof_after_insert :
 

Code SQL :

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

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

 
En effet, bien que Paul n’enseigne aucune matière :
 

Code SQL :

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

select * from Prof ;

 
=>

Code :

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

1
2
3
4
5
profId	profMatricule	profNom
1	fenau		Fernand
2	ravol		Raoul
3	pat  		Patricia
4	rapol		Paul

 

Code SQL :

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

select * from Prof where profId not in (select profId from Enseigner)

 
=>

Code :

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

1
2
profId	profMatricule	profNom
4	rapol		Paul

QED

[fsmrel]

Fred, je fais référence à ton post ici.

En fait dans le cas de SQL Server les déclencheurs peuvent être récursif et réentrant.

Le trigger ProfMatiere_instead_Tr a pour objet la vue ProfMatiere_V ;

Le trigger Prof_after_insert a pour objet la table Prof.

Le premier effectue un insert dans la table Prof et fait donc réagir le second.

Ces triggers sont distincts, la récursivité n’est donc pas concernée.

La réentrance n’est pas non plus concernée, puisque le second ne fait pas réagir le premier (pas de ping-pong...)

Cela dit, la mise en oeuvre disable/enable ci-dessous (codée dans le premier trigger) peut elle être remise en cause ? (bis repetita...)

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 ;

[escartefigue]

Je ne suis pas du tout d'accord avec cette position. En effet :

1) les solutions d'auto incrémentations manuelles via tables de compteurs posent le même problème de trou possible en pire... Le mécanisme étant souvent bien moins protégé et souvent mal intégré...

2) dans tous les cas de trou il suffit d'avoir une table de nombre pour compenser les trous manquants...

Dans l'exemple que je montrais ici :
https://www.developpez.net/forums/d2.../#post12008908
Il suffit d'ajouter une jointure externe à droite avec la table des factures annulées pour les voir réapparaître...

A +

Pour éviter les trous, la table paramètre doit être commitée en même temps que la transaction. De plus, cette table doit avoir un verrou de niveau ligne si elle contient d'autres compteurs, ceci pour limiter les contentions.

[SQLpro]

Allez bon, comme vous avez été méchant avec moi, mais que je ne vous en tient pas rancune, je vous livre une version beaucoup beaucoup plus simplifiée qui ne nécessite aucune désactivation de trigger, puisque je n'en met plus qu'un seul, le déclencheur INSTEAD OF. Cette méthode est basées sur le dépersonnalisation propre à SQL Server, en matière de sécurité....

-- création de la base et ses objets

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
CREATE DATABASE DB_FSMREL
GO

USE DB_FSMREL;
GO

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)
);

--> Fred SQLpro : manque une contrainte dans cette dernière table !
ALTER TABLE Enseigner
   ADD UNIQUE (profId, matiereId);
GO
--< fin ajout SQLpro

-- création de la vue (comme précédemment)

Code :

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

1
2
3
4
5
6
7
8
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 ;
GO

Et là c'est nouveau.... Gestion des utilisateurs :

Code :

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

1
2
3
4
5
6
7
8
9
10
--> gestion des utilisateurs et privilèges...
CREATE USER USR_applicatif
   WITHOUT LOGIN;
GO

GRANT SELECT TO USR_applicatif
GO

GRANT INSERT, UPDATE, DELETE ON ProfMatiere_V TO USR_applicatif
GO

C'est tout ce qu'il y a à faire : un utilisateur applicatif qui a les privilèges de lecture écriture sur la vue, mais pas sur les tables.... Il peut juste lire les tables !

Et maintenant le clou du pestacle (comme dirais Coluche) c'est la déclencheur... et la petite ligne EXECUTE AS !!!

Code :

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

1
2
3
4
5
6
7
8
9
CREATE OR ALTER TRIGGER ProfMatiere_instead_Tr 
   ON ProfMatiere_V 
   WITH EXECUTE AS 'dbo'
   INSTEAD OF INSERT 
AS
INSERT INTO Matiere SELECT DISTINCT matiereId, matiereCode, matiereNom FROM inserted WHERE matiereCode NOT IN (SELECT matiereCode FROM Matiere);
INSERT INTO Prof SELECT DISTINCT profId, profMatricule, profNom FROM inserted WHERE profMatricule NOT IN (SELECT profMatricule FROM Prof);
INSERT INTO Enseigner SELECT DISTINCT profId, matiereId FROM inserted;
GO

J'ai pas testé, car donne un cours en belgique avec un réseau internet pourri (peut être la graisse de frites de bouche les fibres internet...)

Mais si vous voulez tester, utilisez la commande :

Code :

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

EXECUTE AS USER = 'USR_applicatif';

Avant d'envoyer la sauce !!!

A +

[fsmrel]

Salve,

Allez bon, comme vous avez été méchant avec moi, mais que je ne vous en tiens pas rancune, je vous livre une version beaucoup beaucoup plus simplifiée qui ne nécessite aucune désactivation de trigger, puisque je n'en mets plus qu'un seul, le déclencheur INSTEAD OF. Cette méthode est basée sur le dépersonnalisation propre à SQL Server, en matière de sécurité...

Code SQL :

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

1
2
3
4
5
6
7
8
--> gestion des utilisateurs et privilèges...
CREATE USER USR_applicatif
   WITHOUT LOGIN;
GO
GRANT SELECT TO USR_applicatif
GO
GRANT INSERT, UPDATE, DELETE ON ProfMatiere_V TO USR_applicatif
GO

 
Merci Fred pour ta patience ! De fait, pour les inserts, ça fonctionne, bien vu.

Pour les delete et update on n’échappera pas à des triggers.

Exemple :

Code :

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

1
2
3
4
5
6
7
après les inserts	profId	profMatricule	profNom		matiereId	matiereCode	matiereNom
			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

 
Tentative d’update :

Code SQL :

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

1
2
3
update ProfMatiere_V
set matiereid= 4, matiereCode = 'mus', matierenom ='musique' 
where profnom = 'Raoul' and matiereNom = 'chimie' ;

=>

Code :

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

1
2
Msg 4405, Niveau 16, État 1, Ligne 45
La vue ou la fonction 'ProfMatiere_V' ne peut pas être mise à jour car la modification porte sur plusieurs tables de base.

Tentative de delete :

Code SQL :

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

1
2
delete from ProfMatiere_V
where profnom = 'Raoul' and matiereNom = 'chimie' ;

=>

Code :

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

1
2
Msg 4405, Niveau 16, État 1, Ligne 53
La vue ou la fonction 'ProfMatiere_V' ne peut pas être mise à jour car la modification porte sur plusieurs tables de base.

Quoi qu’il en soit, même si j’ai utilisé SQL Server, la discussion se veut générale car dès le début, j’ai annoncé la couleur :
 

Comme je n’ai plus accès à DB2 depuis au moins 20 ans, j’utilise ici SQL Server.

Je ne peux pas me cantonner à un seul SGBD. J’aimerais avoir l’avis des collègues, escartefigue, CinePhil, Paprick, f-leb, et autres baroudeurs.

[CinePhil]

Salut les experts !

La lecture de ce sujet m'a fait replonger dans le modèle de données que j'avais réalisé pour une application d'inscription des étudiants il y a quelques années.

Je n'y ai pas trouvé d'associations de cardinalités 1,n - 1,n mais quelques trucs assez rigolos qui demandent du développement similaire quand même.

Sur ce projet, j'avais pas mal utilisé les procédures pour insérer, mettre à jour ou supprimer les données. Je n'avais cependant pas poussé la rigueur au point d'empêcher l'administrateur pirate Hilarion de jouer avec les tables sans passer par les procédures.

J'ai déjà expliqué le mécanisme mis en oeuvre dans mon premier message. La procédure :
- contrôle la qualité des données qui lui sont passées et donne des erreurs explicites si quelque chose n'est pas conforme (un étudiant né l'an dernier ou il y a 80 ans, par exemple) ;
- insère, met à jour ou supprime ce qui est nécessaire en fonction de l'existant.

Par exemple, dans cette base de données, j'avais une partie référentiel contenant notamment le référentiel des communes françaises mais pas celui des communes étrangères. La procédure d'enregistrement d'une adresse postale à l'étranger allait ainsi vérifier si la ville étrangère existait déjà dans la table des villes et l'insérait si elle n'existait pas en récupérant l'identifiant créé pour la clé étrangère dans l'adresse.

Sur les clés étrangères, il y avait des CASCADE quand c'était possible et pertinent mais aussi beaucoup de RESTRICT pour empêcher de supprimer ce qui ne devait pas l'être.

Enfin, il y avait quelques enchaînements d'associations que les CASCADE ne pouvaient pas couvrir.
Par exemple, des pièces justificatives étaient jointes à l'inscription pour attester de l'identité ou de l'obtention d'un diplôme. Une seule table enregistrait les fichiers mais ces fichiers pouvaient se rapporter à un étudiant ou à l'obtention d'un diplôme. Le fichier est une entité type faible car il n'a pas d'existence propre sans ce à quoi il se rapporte : un étudiant (pièce d'identité), un passage de diplôme (relevé de note et certificat du diplôme obtenu) ou une inscription (justificatif de paiement d'une contribution de vie étudiante et de campus - CVEC). La suppression du fichier ne peut pas se faire via la cascade de la suppression de ce qu'il justifie ; elle doit se faire par procédure.

Ça m'a beaucoup plu de développer ça en BDD épaisse !

Tout ça a été fait sous MariaDB mais je ne peux pas donner de code ici car l'application est encore en service.

À l'occasion, je me pencherait sur le cas soumis ici mais là j'ai d'autres chats à fouetter.

[fsmrel]

Salve,

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

 
Je rappelle ce que j’ai écrit dans mon 1er post :
 

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 a priorii plus simple des cardinalités 1,n/1,1, mais ça viendra.

 
Je crois qu’il est temps parler de ce type de cardinalités.
Je suis incorrigible et j’avais prévu de partir du MCD suivant avec double dose de (1,n) :
 

 

Mais soit, partons de quelque chose de moins compliqué :
 

 


A savoir un MCD produisant le code SQL cousin du tien (au bonhomme Null près et pas de cascade côté Produit, horresco referens) :
 

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
CREATE TABLE Produit
(
   produitId SMALLINT NOT NULL
 , produitCode CHAR(4) NOT NULL
 , produitNom VARCHAR(24) NOT NULL
 , produitMontant SMALLINT NOT NULL
 , CONSTRAINT Produit_PK PRIMARY KEY(produitId)
 , CONSTRAINT Produit_AK UNIQUE(produitCode)
) ;
 
CREATE TABLE Commande
(
   commandeId INT NOT NULL
 , commandeNumero VARCHAR(10) NOT NULL
 , commandeDate DATE NOT NULL
 , CONSTRAINT Commande_PK PRIMARY KEY(commandeId)
 , CONSTRAINT Commande_AK UNIQUE(commandeNumero)
);
 
CREATE TABLE LigneCommande
(
   commandeId INT NOT NULL
 , ligneCommandeId SMALLINT NOT NULL
 , quantite SMALLINT NOT NULL
 , produitId SMALLINT NOT NULL
 , CONSTRAINT LigneCommande_PK PRIMARY KEY(commandeId, ligneCommandeId)
 , CONSTRAINT LigneCommande_Commande_FK FOREIGN KEY(commandeId) 
     REFERENCES Commande(commandeId) ON DELETE CASCADE
 , CONSTRAINT LigneCommande_Produit_FK FOREIGN KEY(produitId) 
     REFERENCES Produit(produitId)
) ;

 
Je vais essayer de me lancer dans l’entreprise, mais il va falloir que je me relance dans les tests avec MySQL et là, y a du boulot, ça fait des lustres que je ne m’en suis pas servi...
Pour info, j’en suis resté à la version 5.7 de la bête.
Quand pense que j’ai écrit un article à propos de MySQL Workbench il y a une dizaine d’années, ça ne va pas me servir pour ce qui nous intéresse, mais j’y retrouve de-ci de-là (paragraphe 10-9 par exemple) le thème qui t’est cher ...

En attendant, bonne fin de semaine !

[JeitEmgie]

N'oubliez quand même pas que dans une application de gestion, modifier le prix d'un produit aujourd'hui n'est pas censé modifier les commandes et les factures du passé...

[escartefigue]

Je vais essayer de me lancer dans l’entreprise, mais il va falloir que je me relance dans les tests avec MySQL et là, y a du boulot, ça fait des lustres que je ne m’en suis pas servi...
Pour info, j’en suis resté à la version 5.7 de la bête.

Ça vaut le coup de passer à la V8 qui a enfin intégré les CTE et du coup les requêtes récursives, les fonctions OLAP et pas mal d'autres améliorations
Bon courage pour la suite

[fsmrel]

N'oubliez quand même pas que dans une application de gestion, modifier le prix d'un produit aujourd'hui n'est pas censé modifier les commandes et les factures du passé...

Bien entendu, mais on est là pour traiter des cardinalités (1,n). L’impact du prix des produits est un autre sujet. On ne va quand même pas ici faire le produit cartésien des problèmes et noyer les étudiants de Paprick. Le prix du produit est là histoire de meubler. On pourrait le dégager dans une entité-type ad-hoc et mettre en oeuvre un système synchronisant la date de facture et le prix des produits à date. Mais ceci est une autre histoire, dont on ne va pas s’encombrer ici.

Restons-en à un modèle simple afin de traiter du sujet qui nous concerne.

[Paprick]

Bonjour,

Bien entendu, mais on est là pour traiter des cardinalités (1,n). L’impact du prix des produits est un autre sujet. On ne va quand même pas ici faire le produit cartésien des problèmes et noyer les étudiants de Paprick. Le prix du produit est là histoire de meubler. On pourrait le dégager dans une entité-type ad-hoc et mettre en oeuvre un système synchronisant la date de facture et le prix des produits à date. Mais ceci est une autre histoire, dont on ne va pas s’encombrer ici.
Restons-en à un modèle simple afin de traiter du sujet qui nous concerne.

Merci François, tu as tout à fait compris ma volonté pour cet exercice !
Bien sûr que bien des choses seraient à compléter pour gérer correctement l'historisation des données : reprendre le prix du produit dans l'association, sur un modèle de facturation plus complet, reprendre les coordonnées du client dans la facture, etc...
Mais, comme le rappelle François, la démarche est avant tout pédagogique, et devant des apprenants débutants, il ne faut surtout pas traiter plusieurs problématiques dans un même exercice.
D'ailleurs François, c'est aussi la raison pour laquelle je n'ai pas introduit la problématique des identifiants significatifs (que je traite en cours bien plus tard), et que j'ai ainsi conservé le numéro de commande et la référence du produit en identifiants (ça, c'est une autre de nos "batailles" et un de nos très rares désaccords ).
Je propose donc d'écarter tout ce qui pourrait polluer le sujet principal et de se consacrer, avec une approche la plus simple et générique possible, à la prise en compte de ce fameux 1,n dont la seule ambition est de se distinguer de son copain 0,n.
N'étant pas un grand spécialiste des triggers et autres procédures stockées, je vous remercie de m'éclairer avec pour objectif : "le respect de la cardinalité 1,n pour les nuls" (et pas les NULL) !

[fsmrel]

Salve !
 

n'étant pas un grand spécialiste des triggers et autres procédures stockées, je vous remercie de m'éclairer avec pour objectif : "le respect de la cardinalité 1,n pour les nuls" (et pas les NULL) !

Aïe, aïe ! MySQL refuse les triggers sur des vues ! C’est quand même malheureux, bridage intentionnel de la part de l’éditeur ? 

Ça fait quand même plus de 15 ans qu’on peut le faire avec DB2 (version 9 du SGBD).
 
 

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.

 
Capitaine, je vais essayer de faire comme toi, mais je repars à zéro... Je ne promets rien, je ne sais plus si j’ai déjà utilisé des procédures stockées avec MySQL, mais au moins je vais me cultiver, à défaut de me rafraîchir les idées...