ALM - Respect au niveau SQL des cardinalités 1,n du MCD merisien,
Un article de fsmrel
Le 2024-03-02 23:52:28, par fsmrel, Expert éminent sénior
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 :
MCD correspondant, créé avec Looping, gracieusement proposé par le professeur Patrick Bergougnoux (encore une fois, merci Paprick !) :
Code SQL des tables correspondantes :
Pour que les contraintes soient systématiquement respectées, on ne peut pas se contenter de la séquence suivante :
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 :
Quelques insertions dans la vue :
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.
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 :
Exemple de trigger pour contrôler les inserts dans la table Matiere :
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 :
Après exécution de l’insert dans la vue ProfMatiere_V, au résultat :
Table Prof :
Table Matiere :
Table Enseigner :
Hilarion tentera de violer les règles, avec des instructions du genre :
Réaction du système :
Dans le même genre :
=>
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...
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.
— 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 : |
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).
— (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 : |
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 : |
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 : |
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 : |
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 : |
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 : |
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 : |
1 2 3 | Prof profId profMatricule profNom 1 fenau Fernand 2 ravol Raoul |
Table Matiere :
Code : |
1 2 3 4 | Matiere matiereId matiereCode matiereNom 1 phy physique 2 chm chimie 3 maths mathématiques |
Table Enseigner :
Code : |
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 : |
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.
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 : |
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.
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...
-
fsmrelExpert éminent séniorBuenos 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 : 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25CREATE 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 : 1
2
3
4
5
6
7insert into Produit (RefProduit, Designation, Prix) values ('p01', 'bonbons', 100) , ('p02', 'caramels', 200) , ('p03', 'esquimaux', 300) , ('p04', 'chocolats', 400) ;
Select * from Produit :Code : 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 : 1
2
3
4
5
6create 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 : 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17CREATE 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 : 1
2
3create trigger devis_vue_trigger instead of insert on DevisVue for each row execute function devis_vue_fonction() ;
(6) Insert dans la vue :
Code SQL : 1
2
3
4
5
6insert 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 : select * from Devis ;
Code SQL : select * from LigneDevis ;
le 20/03/2024 à 13:12 -
fsmrelExpert éminent séniorBonsoir,
Envoyé par Karadoc le 03/03/2024 à 23:10 -
fsmrelExpert éminent séniorJ’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...le 14/04/2024 à 0:34 -
SQLproRédacteurD'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 : 1
2
3CREATE TABLE T (C INT PRIMARY KEY); INSERT INTO T VALUES (1), (2), (3); UPDATE T SET C = C + 1;
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 +le 24/04/2024 à 19:24 -
KaradocMembre actifBonsoir,
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 !le 03/03/2024 à 21:43 -
SQLproRédacteurJe 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 : 1
2
3
4
5
6
7CREATE 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);
Cette vue optimale des factures correctes, doit évidemment être complétée par celle des factures annulées comme suit :Code : 1
2
3
4
5
6
7CREATE 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);
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 +le 04/03/2024 à 11:18 -
fsmrelExpert éminent séniorBonjour,
Envoyé par JeitEmgie
— 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...le 04/03/2024 à 13:15 -
SQLproRédacteurEn 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 +le 06/03/2024 à 13:04 -
PaprickMembre émériteBonsoir à 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 : 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20CREATE 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);
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 !le 06/03/2024 à 23:50 -
escartefigueModérateurDans 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.le 07/03/2024 à 8:23