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

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

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

    Informations forums :
    Inscription : Juin 2019
    Messages : 674
    Points : 2 706
    Points
    2 706
    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 : 91
Taille : 74,5 Ko

    Voici le MLD correspondant :
    Nom : Trigger Héritage MLD.jpg
Affichages : 82
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 ?
    Patrick Bergougnoux - Professeur des Universités au Département Informatique de l'IUT de Toulouse III
    La simplicité est la sophistication suprême (Léonard de Vinci)
    LIVRE : Modélisation Conceptuelle de Données - Une Démarche Pragmatique
    Looping - Logiciel de modélisation gratuit et libre d'utilisation

  2. #2
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    Mars 2010
    Messages
    10 123
    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 123
    Points : 38 498
    Points
    38 498
    Billets dans le blog
    9
    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 sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 996
    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 : 7 996
    Points : 30 886
    Points
    30 886
    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 sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 996
    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 : 7 996
    Points : 30 886
    Points
    30 886
    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 émérite
    Avatar de Paprick
    Homme Profil pro
    Professeur des Universités
    Inscrit en
    Juin 2019
    Messages
    674
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 61
    Localisation : France, Haute Garonne (Midi Pyrénées)

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

    Informations forums :
    Inscription : Juin 2019
    Messages : 674
    Points : 2 706
    Points
    2 706
    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é.
    Patrick Bergougnoux - Professeur des Universités au Département Informatique de l'IUT de Toulouse III
    La simplicité est la sophistication suprême (Léonard de Vinci)
    LIVRE : Modélisation Conceptuelle de Données - Une Démarche Pragmatique
    Looping - Logiciel de modélisation gratuit et libre d'utilisation

  6. #6
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 996
    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 : 7 996
    Points : 30 886
    Points
    30 886
    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 émérite
    Avatar de Paprick
    Homme Profil pro
    Professeur des Universités
    Inscrit en
    Juin 2019
    Messages
    674
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 61
    Localisation : France, Haute Garonne (Midi Pyrénées)

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

    Informations forums :
    Inscription : Juin 2019
    Messages : 674
    Points : 2 706
    Points
    2 706
    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 !
    Patrick Bergougnoux - Professeur des Universités au Département Informatique de l'IUT de Toulouse III
    La simplicité est la sophistication suprême (Léonard de Vinci)
    LIVRE : Modélisation Conceptuelle de Données - Une Démarche Pragmatique
    Looping - Logiciel de modélisation gratuit et libre d'utilisation

  8. #8
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 996
    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 : 7 996
    Points : 30 886
    Points
    30 886
    Billets dans le blog
    16
    Par défaut
    Citation Envoyé par Paprick Voir le message
    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...
    (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.

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

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

    Informations forums :
    Inscription : Juin 2019
    Messages : 674
    Points : 2 706
    Points
    2 706
    Par défaut
    Citation Envoyé par fsmrel Voir le message
    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 !!!
    Patrick Bergougnoux - Professeur des Universités au Département Informatique de l'IUT de Toulouse III
    La simplicité est la sophistication suprême (Léonard de Vinci)
    LIVRE : Modélisation Conceptuelle de Données - Une Démarche Pragmatique
    Looping - Logiciel de modélisation gratuit et libre d'utilisation

  10. #10
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 996
    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 : 7 996
    Points : 30 886
    Points
    30 886
    Billets dans le blog
    16
    Par défaut Variation sur le thème
    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 ?
    (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.

  11. #11
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    Mars 2010
    Messages
    10 123
    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 123
    Points : 38 498
    Points
    38 498
    Billets dans le blog
    9
    Par défaut
    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.

  12. #12
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 996
    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 : 7 996
    Points : 30 886
    Points
    30 886
    Billets dans le blog
    16
    Par défaut Madame Mado et le clandé de Tomate
    Ave Capitaine,

    Citation Envoyé par escartefigue Voir le message
    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).
    (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.

  13. #13
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    Mars 2010
    Messages
    10 123
    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 123
    Points : 38 498
    Points
    38 498
    Billets dans le blog
    9
    Par défaut
    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...)

  14. #14
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 996
    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 : 7 996
    Points : 30 886
    Points
    30 886
    Billets dans le blog
    16
    Par défaut
    Ave Capitaine, je te salue bien bas !
     
    Citation Envoyé par escartefigue Voir le message
    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.
    (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.

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