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 !

[fsmrel]

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

 
Euh... Pas précisément, ça n’est pas l’approche qui a été initiée par Fred, mais c’est plutôt nous qui l’avons étés...
 
A cette occasion, j’ai corrigé l’accord du verbe initier dans mon message précédent... A défaut le capitaine m’aurait fait les gros yeux...

[Paprick]

Euh... Pas précisément, ça n’est pas l’approche qui a été initiée par Fred, mais c’est plutôt nous qui l’avons étés...

Exact, mauvaise tournure de phrase !!!

[fsmrel]

Encore à propos des contraintes d’exclusion.
 
Toujours dans Ingénierie des systèmes d'information - Merise deuxième génération, on trouve celle-ci (figure 13.40) : une personne qui possède des logements ne peut pas en être locataire :
 

 
En notant que chaque personne doit être propriétaire d’au moins un logement, mais il est préférable qu’il n’en soit pas ainsi, en conséquence de quoi il suffit de remplacer la cardinalité 1,n par 0,n.

A cette coquille près, au stade SQL, on nous propose deux triggers pour que la contrainte d’exclusion soit respectée.
 
Peut-on éviter ces triggers ? Il y a une possibilité. En l’occurrence faire de l’entité-type LOGEMENT une propriété multivaluée, c’est-à-dire une entité-type faible, dépendant de l’entité-type forte PERSONNE.
 
Je passe à Looping, en utilisant l’identification relative :
 

 
Au stade SQL, au moyen d’un alter table, on produit une contrainte de colonne attachée à la table Louer :
 

 

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
CREATE TABLE Personne
(
   NoPersonne SMALLINT,
   CONSTRAINT Personne_PK PRIMARY KEY(NoPersonne)
);
 
CREATE TABLE Logement
(
   NoPersonne SMALLINT,
   NoLogement SMALLINT,
   CONSTRAINT Logement_PK PRIMARY KEY(NoPersonne, NoLogement),
   CONSTRAINT Logement_Personne_FK FOREIGN KEY(NoPersonne) REFERENCES Personne(NoPersonne)
);
 
CREATE TABLE Louer
(
   NoPersonne SMALLINT,
   NoProprio  SMALLINT,
   NoLogement SMALLINT,
   CONSTRAINT Louer_PK PRIMARY KEY(NoPersonne, NoProprio, NoLogement),
   CONSTRAINT Louer_Personne_FK FOREIGN KEY(NoPersonne) REFERENCES Personne(NoPersonne) ON DELETE CASCADE,
   CONSTRAINT Louer_Logement_FK FOREIGN KEY(NoProprio, NoLogement) REFERENCES Logement(NoPersonne, NoLogement)
);

 
Ce à quoi Looping ajoute la contrainte :
 

Code SQL :

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

1
2
ALTER TABLE Louer
  ADD CONSTRAINT LouerExclusion CHECK (NoPersonne <> NoProprio) ;

 
Vos avis ?

[escartefigue]

Bonjour François

Le logement ne peut pas être identifié relativement à la personne, ce n'est pas une entité-type faible et ça interdirait tout changement de propriétaire.

[fsmrel]

Ave Capitaine,

Le logement ne peut pas être identifié relativement à la personne, ce n'est pas une entité-type faible et ça interdirait tout changement de propriétaire.

Se pose donc le problème du changement de propriétaire...

Je vais utiliser une méthode bourrin pour que cette opération puisse se faire, sans toucher à l'identification relative...

Raoul vend son logement "le Clandé de Tomate" à Fernand.
Le temps de l'opération, on en vire le locataire (Madame Mado).
Ceci fait, on effectue le changement de propriétaire, puis on réintègre Madame Mado chez elle.

Création des tables. Pour y voir plus clair, les identifiants principaux sont du type varchar.

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 Personne
(
   NoPersonne VARCHAR(16),
   CONSTRAINT Personne_PK PRIMARY KEY(NoPersonne)
);
 
CREATE TABLE Logement
(
   NoPersonne VARCHAR(16),
   NoLogement SMALLINT,
   NomLogement VARCHAR(24),
   CONSTRAINT Logement_PK PRIMARY KEY(NoPersonne, NoLogement),
   CONSTRAINT Logement_AK UNIQUE(NomLogement),
   CONSTRAINT Logement_Personne_FK FOREIGN KEY(NoPersonne) REFERENCES Personne(NoPersonne) ON UPDATE CASCADE
);
 
CREATE TABLE Louer
(
   NoPersonne VARCHAR(16),
   NoProprio  VARCHAR(16),
   NoLogement SMALLINT,
   CONSTRAINT Louer_PK PRIMARY KEY(NoPersonne, NoProprio, NoLogement),
   CONSTRAINT Louer_Personne_FK FOREIGN KEY(NoPersonne) REFERENCES Personne(NoPersonne) ON DELETE CASCADE,
   CONSTRAINT Louer_Logement_FK FOREIGN KEY(NoProprio, NoLogement) REFERENCES Logement(NoPersonne, NoLogement)
);
alter table Louer
  add Constraint LouerExclusion Check (NoPersonne <> NoProprio) ;

 
Quelques personnes

Code SQL :

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

1
2
3
insert into Personne (NoPersonne)
values
   ('Fernand'), ('Raoul'), ('Paul'), ('Pascal'), ('Bastien'), ('Madame Mado') ;

 
Quelques logements

Code SQL :

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

1
2
3
4
5
insert into Logement (NoPersonne, NoLogement, NomLogement)
values
   ('Fernand',1,'Usine de Montauban'), ('Fernand',2,'Le bowling')
 , ('Fernand',3,'La crèche du Mexicain')
 , ('Raoul',1,'Clandé de Tomate'), ('Raoul',2,'Dugoisneau') ;

 
Raoul loue le clandé de Tomate à Madame Mado

Code SQL :

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

1
2
insert into Louer (NoPersonne, NoProprio, NoLogement)
values ('Madame Mado', 'Raoul', 1)

 
Raoul vend le clandé de Tomate à Fernand ;
le temps de l'opération, pendant qu’elle fait ses courses, on en vire Madame Mado...
 

Code SQL :

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

delete from  Louer where NoProprio = 'Raoul' and NoLogement = 1 ;

 
Fernand devient propriétaire du logement vendu par Raoul
 

Code SQL :

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

1
2
3
update Logement 
  set NoPersonne = 'Fernand', NoLogement = 4  
    where NoPersonne = 'Raoul' and NoLogement =  1 ;

Madame Mado redevient locataire du clandé de Tomate :
 

Code SQL :

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

1
2
insert into Louer 
values ('Madame Mado', 'Fernand', 4) ;

 
Un coup d’oeil
 

Code SQL :

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

1
2
3
select a.NoPersonne as locataire, a.NoProprio as proprio, b.NomLogement 
from Louer  as a join Logement as b 
  on a.NoProprio = b.NoPersonne and a.NoLogement = b.NoLogement ;

 

Code :

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

1
2
Locataire     proprio   NomLogement
Madame Mado   Fernand   Clandé de Tomate

 
Bon d’accord, c’est un peu bourrin...

Au besoin, on pourrait bien sûr traiter de l’historisation des changements de propriétaires, mais c’est ici hors sujet (n’accablons pas les courageux étudiants toulousains).

[escartefigue]

C'est possible bien entendu.

Si je mets la casquette de l'exploitant ou du DBA, j'objecterai que, le plus souvent, l'identification relative fait l'objet d'un index cluster, ce faisant, si l'on modifie la valeur de la première colonne de l'index, on éparpille par petits bouts façon puzzle les lignes dans les tablespaces et indexspaces. Si la modification est exceptionnelle, l'impact reste marginal et point ne m'en formaliserai, si au contraire c'est chose courante, alors les performances vont s'en ressentir et une réorg fréquente devient nécessaire.
Bien évidemment, cette remarque s'applique aux tables enfant s'il y en a, ce qui n'est pas le cas ici dans cet exemple simplifié, mais qui peut rapidement provoquer des mises à jour très significatives (cas typique : les commandes, lignes de commande, engagements de commande, lignes de livraison...)

[fsmrel]

Ave Capitaine, je te salue bien bas !
 

Si je mets la casquette de l'exploitant ou du DBA, j'objecterai que, le plus souvent, l'identification relative fait l'objet d'un index cluster, ce faisant, si l'on modifie la valeur de la première colonne de l'index, on éparpille par petits bouts façon puzzle les lignes dans les tablespaces et indexspaces. Si la modification est exceptionnelle, l'impact reste marginal et point ne m'en formaliserai, si au contraire c'est chose courante, alors les performances vont s'en ressentir et une réorg fréquente devient nécessaire.
Bien évidemment, cette remarque s'applique aux tables enfant s'il y en a, ce qui n'est pas le cas ici dans cet exemple simplifié, mais qui peut rapidement provoquer des mises à jour très significatives (cas typique : les commandes, lignes de commande, engagements de commande, lignes de livraison...)

 
En tant que concepteur des MCD et/ou DBA DB2 (MVS) chez les « grands comptes », j’ai toujours eu à engager la responsabilité de mon entreprise (SSII) quant à la performance des applications. En ce sens j’ai fait le pari de l’identification relative à fond et donc la mise en oeuvre des index cluster qui s’ensuivent (avec évidemment des identifiants non significatifs, c’est la moindre des choses). Une fois les applications en production, j’ai suivi leur comportement (disons pendant au moins un an ou deux), et j’ai ainsi pu m’assurer que je ne m’étais pas planté. Et Dieu sait s’il y en avait des commandes, lignes de commande et tout le toutim. Pour que ça tienne la route, j’ai démontré chez ces clients le rôle fondamental du suivi des perfs et du RRR (Réorg, Runstats, Rebind). En plus de 20 ans, jamais de retour négatif, bien au contraire.