Discussion :

[Paprick]

Bonsoir à tous,

Dans la droite ligne des cas traités récemment de prise en compte des contraintes dans le DDL, voici un cas assez classique que je soumets à votre sagacité !
Il s'agit de définir les triggers nécessaires à la gestion de l'eXclusivité dans le cadre d'un héritage.
Voici un petit MCD pour traiter le sujet le plus simplement possible :


Voici le MLD correspondant :


et le DDL (PostgreSQL) :

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
CREATE TABLE Personnel(
   Matricule INTEGER,
   Prénom VARCHAR(50),
   Nom VARCHAR(50),
   Date_Naissance DATE,
   CONSTRAINT PK_Personnel PRIMARY KEY(Matricule)
);

CREATE TABLE Administratif(
   Matricule INTEGER,
   Fonction VARCHAR(50),
   Salaire_Mensuel MONEY,
   CONSTRAINT PK_Administratif PRIMARY KEY(Matricule),
   CONSTRAINT FK_Administratif_Personnel FOREIGN KEY(Matricule) REFERENCES Personnel(Matricule)
);

CREATE TABLE Enseignant(
   Matricule INTEGER,
   Grade VARCHAR(30),
   Échelon SMALLINT,
   CONSTRAINT PK_Enseignant PRIMARY KEY(Matricule),
   CONSTRAINT FK_Enseignant_Personnel FOREIGN KEY(Matricule) REFERENCES Personnel(Matricule)
);

Il s'agit donc de s'assurer qu'un enseignant n'apparait pas dans la table des administratifs, et vice-versa, dans le cadre d'une insertion ou d'une modification (si possible en PostgreSQL).
Est-ce que cela vous inspire ?

[escartefigue]

Bonjour Patrick

Pour ce genre de besoins, je verrais bien une contrainte check associée à une UDF.
Fredéric Bouard a écrit un article sur ce sujet. Voir ICI

[fsmrel]

Dans la droite ligne des cas traités récemment de prise en compte des contraintes dans le DDL, voici un cas assez classique que je soumets à votre sagacité !
Il s'agit de définir les triggers nécessaires à la gestion de l'eXclusivité dans le cadre d'un héritage.

 
J’arrive !
 
Zorro

[fsmrel]

Dans la droite ligne des cas traités récemment de prise en compte des contraintes dans le DDL, voici un cas assez classique que je soumets à votre sagacité !
Il s'agit de définir les triggers nécessaires à la gestion de l'eXclusivité dans le cadre d'un héritage.

 
Un exemple avec PostgreSQL.
 
(1) Création des tables
 

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
CREATE TABLE Personnel
(
   Matricule INTEGER
 , Prenom VARCHAR(50)
 , Nom VARCHAR(50)
 , Date_Naissance DATE
 , CONSTRAINT PK_Personnel PRIMARY KEY(Matricule)
);
CREATE TABLE Administratif
(
   Matricule INTEGER
 , Fonction VARCHAR(50)
 , Salaire_Mensuel INTEGER
 , CONSTRAINT PK_Administratif PRIMARY KEY(Matricule)
 , CONSTRAINT FK_Administratif_Personnel FOREIGN KEY(Matricule) REFERENCES Personnel(Matricule)
);
CREATE TABLE Enseignant
(
   Matricule INTEGER
 , Grade VARCHAR(30)
 , Echelon SMALLINT
 , CONSTRAINT PK_Enseignant PRIMARY KEY(Matricule)
 , CONSTRAINT FK_Enseignant_Personnel FOREIGN KEY(Matricule) REFERENCES Personnel(Matricule)
);

 
(2) Fonctions
 
Le capitaine a fait un renvoi vers un article fort intéressant du camarade Fred, merci à eux deux. Fred nous propose un exemple de fonction nous permettant d’assurer l’exclusion. Je crée donc une fonction par table spécialisée.
 
Chaque fonction récupère le paramètre qu’on lui transmet (LeMatricule) et qui dans la requête est nommé $1.
 
(a) Fonction administratif_exclu_fonction :
 
Si le matricule transmis n’est pas présent dans la table Administratif, la fonction répond TRUE, l’insert d’un enseignant est acceptée.
 

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
CREATE OR REPLACE FUNCTION administratif_exclu_fonction (LeMatricule integer)
RETURNS BOOLEAN  
AS  
$$  
BEGIN
RETURN CASE
           WHEN EXISTS(SELECT 0 AS I
                       FROM   Administratif
                       WHERE  matricule = $1)
              THEN FALSE
           ELSE TRUE
        END;    
END;  
$$ LANGUAGE 'plpgsql'

 
(b) Fonction enseignant_exclu_fonction :
 
Si le matricule transmis n’est pas présent dans la table Enseignant, la fonction répond TRUE, l’insert d’un administratif est acceptée.
 

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
CREATE OR REPLACE FUNCTION enseignant_exclu_fonction (LeMatricule integer)
RETURNS BOOLEAN  
AS  
$$  
BEGIN
RETURN CASE
           WHEN EXISTS(SELECT 0 AS I
                       FROM   enseignant
                       WHERE  matricule = $1)
              THEN FALSE
           ELSE TRUE
        END;    
END;  
$$ LANGUAGE 'plpgsql'

 
(3) les alter table
 
Chaque table spécialisée est dotée de la contrainte qui va bien :
 

Code SQL :

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

1
2
3
4
5
6
7
alter TABLE Administratif
  add constraint admin_exclusion
    check (enseignant_exclu_fonction (Matricule)) ;
  
alter TABLE Enseignant
  add constraint enseignant_exclusion
    check (administratif_exclu_fonction (Matricule)) ;

 
(4) Création d’enseignants
 

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
insert into Personnel (Matricule, Prenom, Nom, Date_Naissance)
values
   (1, 'Fernand', 'Naudin', '1919-07-14')
 , (2, 'Raoul', 'Volfoni', '1916-01-11')
;
insert into enseignant (Matricule, Grade, Echelon)
values
   (1, 'g01', 1)
 , (2, 'g02', 1) 
;

Il serait évidemment préférable que la création des enseignants soit faite au moyen d’une vue de jointure Personnel – Enseignant, mais ça n’est pas le sujet du jour.
 
(5) Création d’administratifs
 

Code SQL :

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

1
2
3
4
5
insert into Personnel (Matricule, Prenom, Nom, Date_Naissance)
values    (3, 'Paul', 'Volfoni', '1919-10-03') ;
 
insert into administratif (Matricule, Fonction, Salaire_Mensuel)
values (3, 'f01', 2000)  ;

 
(6) Viol de la contrainte d’exclusion
 

Code SQL :

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

1
2
insert into enseignant (Matricule, Grade, Echelon)
values (3, 'g01', 1) ;

 
Au résultat :
 

ERROR: ERREUR: la nouvelle ligne de la relation « enseignant » viole la contrainte de vérification « enseignant_exclusion »
DETAIL: La ligne en échec contient (3, g01, 1).
SQL state: 23514

[Paprick]

Merci Zorro !
Cette solution est parfaitement adaptée à ce petit cas d'école. Merci également à Fred pour avoir initié cette approche, et au capitaine pour l'avoir dénichée.
J'ai intégré les fonctions et les ALTER TABLE dans l'emplacement prévu par Looping pour la saisie du code SQL des contraintes d'héritage, et le DDL est parfaitement généré.
PostgreSQL a avalé le tout sans problème et les tests de violation de la contrainte d'exclusion ont parfaitement fonctionné.

[fsmrel]

Ave Paprick,

Content que le schmilblick avance.
 

Merci également à Fred pour avoir initié cette approche

 
Euh... c’est plutôt Fred qui nous a initiés à cette approche...

Bon courage pour la suite !

[Paprick]

Merci également à Fred pour avoir initié cette approche

Euh... c’est plutôt Fred qui nous a initié à cette approche...

Euh... c'est bien ce que j'ai écrit !