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

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Schéma Discussion :

Contrainte eXclusivité sur Héritage


Sujet :

Schéma

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre Expert
    Avatar de Paprick
    Homme Profil pro
    Professeur des Universités
    Inscrit en
    Juin 2019
    Messages
    762
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 62
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Professeur des Universités
    Secteur : Enseignement

    Informations forums :
    Inscription : Juin 2019
    Messages : 762
    Par défaut Contrainte eXclusivité sur Héritage
    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 :
    Nom : Trigger Héritage.jpg
Affichages : 243
Taille : 74,5 Ko

    Voici le MLD correspondant :
    Nom : Trigger Héritage MLD.jpg
Affichages : 237
Taille : 74,5 Ko

    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 ?

  2. #2
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    Mars 2010
    Messages
    10 604
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : Mars 2010
    Messages : 10 604
    Billets dans le blog
    10
    Par défaut
    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

  3. #3
    Expert éminent
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    8 213
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 8 213
    Billets dans le blog
    16
    Par défaut
    Citation Envoyé par Paprick Voir le message
    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
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  4. #4
    Expert éminent
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    8 213
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 8 213
    Billets dans le blog
    16
    Par défaut Zorro est arrivé !
    Citation Envoyé par Paprick Voir le message
    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
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  5. #5
    Membre Expert
    Avatar de Paprick
    Homme Profil pro
    Professeur des Universités
    Inscrit en
    Juin 2019
    Messages
    762
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 62
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Professeur des Universités
    Secteur : Enseignement

    Informations forums :
    Inscription : Juin 2019
    Messages : 762
    Par défaut
    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é.

  6. #6
    Expert éminent
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    8 213
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 8 213
    Billets dans le blog
    16
    Par défaut
    Ave Paprick,

    Content que le schmilblick avance.
     
    Citation Envoyé par Paprick Voir le message
    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 !
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  7. #7
    Membre Expert
    Avatar de Paprick
    Homme Profil pro
    Professeur des Universités
    Inscrit en
    Juin 2019
    Messages
    762
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 62
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Professeur des Universités
    Secteur : Enseignement

    Informations forums :
    Inscription : Juin 2019
    Messages : 762
    Par défaut
    Citation Envoyé par Paprick Voir le message
    Merci également à Fred pour avoir initié cette approche
    Citation Envoyé par fsmrel Voir le message
    Euh... c’est plutôt Fred qui nous a initié à cette approche...
    Euh... c'est bien ce que j'ai écrit !

Discussions similaires

  1. Contrainte "d'exclusivité" sur plusieurs colonnes
    Par b_lob dans le forum MS SQL Server
    Réponses: 3
    Dernier message: 06/08/2009, 12h03
  2. [Oracle 9i] contrainte CHECK sur le type date
    Par zchallal dans le forum Langage SQL
    Réponses: 2
    Dernier message: 20/06/2006, 11h34
  3. Réponses: 2
    Dernier message: 17/02/2006, 11h20
  4. [SYBASE ASE] Rajouter une contrainte null sur une colonne
    Par Little_Goldo dans le forum Sybase
    Réponses: 1
    Dernier message: 09/02/2005, 10h48
  5. Exclusivité sur un fichier sous linux
    Par ggnore dans le forum Administration système
    Réponses: 4
    Dernier message: 03/11/2004, 09h46

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo