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

Looping Discussion :

Demande d'aide modélisation héritage


Sujet :

Looping

  1. #21
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    7 688
    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 688
    Points : 29 430
    Points
    29 430
    Billets dans le blog
    16
    Par défaut
    Grâce à Looping, dans la foulée, voici les CREATE TABLE, the fingers in the nose :

    CREATE TABLE Famille
    (
       FamilleId INT
     , FamilleCode VARCHAR(2) NOT NULL
     , FamilleLibelle VARCHAR(48) NOT NULL
     , CONSTRAINT Famille_PK PRIMARY KEY(FamilleId)
     , CONSTRAINT Famille_AK UNIQUE(FamilleCode)
     , CONSTRAINT Famille_1_AK UNIQUE(FamilleLibelle)
    );
    
    CREATE TABLE Nomenclature
    (
       NomenId INT
     , FamilleId INT NOT NULL
     , CONSTRAINT Nomenclature_PK PRIMARY KEY(NomenId)
     , CONSTRAINT Nomenclature_Famille_FK FOREIGN KEY(FamilleId) 
            REFERENCES Famille(FamilleId)
    );
    
    CREATE TABLE NomenclatureVegetal
    (
       NomenclatureId INT
     , NomenId INT NOT NULL
     , NomenclatureLibelle VARCHAR(48) NOT NULL
     , CONSTRAINT NomenclatureVegetal_PK PRIMARY KEY(NomenclatureId)
     , CONSTRAINT NomenclatureVegetal_AK UNIQUE(NomenId)
     , CONSTRAINT NomenclatureVegetal_Nomenclature_FK 
           FOREIGN KEY(NomenId) REFERENCES Nomenclature(NomenId) 
    );
    
    CREATE TABLE NomenclatureViande
    (
       NomenclatureId INT
     , NomenId INT NOT NULL
     , NomenclatureLibelle VARCHAR(48) NOT NULL
     , CONSTRAINT NomenclatureViande_PK PRIMARY KEY(NomenclatureId)
     , CONSTRAINT NomenclatureViande_AK UNIQUE(NomenId)
     , CONSTRAINT NomenclatureViande_Nomenclature_FK 
           FOREIGN KEY(NomenId) REFERENCES Nomenclature(NomenId) 
    );
    
    CREATE TABLE NomenclatureOrigineAnimale
    (
       NomenclatureId INT
     , NomenId INT NOT NULL
     , NomenclatureLibelle VARCHAR(48) NOT NULL
     , CONSTRAINT NomenclatureOrigineAnimale_PK PRIMARY KEY(NomenclatureId)
     , CONSTRAINT NomenclatureOrigineAnimale_AK UNIQUE(NomenId)
     , CONSTRAINT NomenclatureOrigineAnimale_Nomenclature_FK 
           FOREIGN KEY(NomenId) REFERENCES Nomenclature(NomenId) 
    );
    
    CREATE TABLE Espece
    (
       EspeceId INT
     , NomenId INT NOT NULL
     , EspeceLibelle VARCHAR(48) NOT NULL
     , CONSTRAINT Espece_PK PRIMARY KEY(EspeceId)
     , CONSTRAINT Espece_AK UNIQUE(NomenId)
     , CONSTRAINT Espece_1_AK UNIQUE(EspeceLibelle)
     , CONSTRAINT Espece_Nomenclature_FK FOREIGN KEY(NomenId) 
           REFERENCES Nomenclature(NomenId)
    ) ;
    
    CREATE TABLE Marchandise
    (
       MarchandiseId INT
     , FamilleId INT NOT NULL
     , MarchandiseLibelle VARCHAR(48) NOT NULL
     , CONSTRAINT Marchandise_PK PRIMARY KEY(MarchandiseId)
     , CONSTRAINT Marchandise_Famille_FK 
           FOREIGN KEY(FamilleId) REFERENCES Famille(FamilleId)
    );
    
    CREATE TABLE Dossier
    (
       DossierId INT
     , DossierLibelle VARCHAR(24) NOT NULL
     , MarchandiseId INT NOT NULL
     , CONSTRAINT Dossier_PK PRIMARY KEY(DossierId)
     , CONSTRAINT Dossier_AK UNIQUE(MarchandiseId)
     , CONSTRAINT Dossier_1_AK UNIQUE(DossierLibelle)
     , CONSTRAINT Dossier_Marchandise_FK FOREIGN KEY(MarchandiseId) 
           REFERENCES Marchandise(MarchandiseId)
    );
    CREATE TABLE Lot
    (
       MarchandiseId INT
     , LotId INT
     , NomenId INT NOT NULL
     , Quantite INT NOT NULL
     , CONSTRAINT Lot_PK PRIMARY KEY(MarchandiseId, LotId)
     , CONSTRAINT Lot_Marchandise_FK FOREIGN KEY(MarchandiseId) 
           REFERENCES Marchandise(MarchandiseId) 
           ON DELETE CASCADE
     , CONSTRAINT Lot_Nomenclature_FK FOREIGN KEY(NomenId) 
           REFERENCES Nomenclature(NomenId)
    );
    
    CREATE TABLE LotViande
    (
       MarchandiseId INT
     , LotId INT
     , CONSTRAINT LotViande_PK PRIMARY KEY(MarchandiseId, LotId)
     , CONSTRAINT LotViande_Lot_FK FOREIGN KEY(MarchandiseId, LotId)
           REFERENCES Lot(MarchandiseId, LotId)
           ON DELETE CASCADE
    );
    
    CREATE TABLE LotVegetal
    (
       MarchandiseId INT
     , LotId INT
     , Etat VARCHAR(50) NOT NULL
     , CONSTRAINT LotVegetal_PK PRIMARY KEY(MarchandiseId, LotId)
     , CONSTRAINT LotVegetal_Lot_FK FOREIGN KEY(MarchandiseId, LotId) 
           REFERENCES Lot(MarchandiseId, LotId)
           ON DELETE CASCADE
    );
    
    CREATE TABLE LotAnimalVivant
    (
       MarchandiseId INT
     , LotId INT
     , Puce VARCHAR(24) NOT NULL
     , Sexe VARCHAR(4) NOT NULL
     , CONSTRAINT LotAnimalVivant_PK PRIMARY KEY(MarchandiseId, LotId)
     , CONSTRAINT LotAnimalVivant_AK UNIQUE(Puce)
     , CONSTRAINT LotAnimalVivant_Lot_FK FOREIGN KEY(MarchandiseId, LotId) 
           REFERENCES Lot(MarchandiseId, LotId)
           ON DELETE CASCADE
    );
    
    CREATE TABLE LotOrigineAnimale
    (
       MarchandiseId INT
     , LotId INT
     , CONSTRAINT LotOrigineAnimale_PK PRIMARY KEY(MarchandiseId, LotId)
     , CONSTRAINT LotOrigineAnimale_Lot_FK FOREIGN KEY(MarchandiseId, LotId) 
           REFERENCES Lot(MarchandiseId, LotId)
           ON DELETE CASCADE
    );
    (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à pout ça.

  2. #22
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    7 688
    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 688
    Points : 29 430
    Points
    29 430
    Billets dans le blog
    16
    Par défaut
    Un petit jeu d’essai :

    INSERT INTO Famille VALUES
        (1, 'A', 'Animaux vivants')
      , (2, 'P', 'Produits d''origine animale')
      , (3, 'PP', 'Végétaux et produits végétaux')
      , (4, 'D', 'Produits destinés à l''alimentation animale')
    ;
    SELECT '' AS Famille, * FROM Famille ;
    
    INSERT INTO Nomenclature VALUES
        (1, 2)  -- nomenId  familleId
      , (2, 2)  -- viande
      , (3, 2)  -- viande
      , (4, 3)  -- vegetal
      , (5, 3)  -- vegetal
      , (6, 3)  -- vegetal
    ;
    SELECT '' AS Nomenclature, * FROM Nomenclature ;
    
    INSERT INTO NomenclatureViande VALUES
        (1, 1, 'Viande V1') -- nomenclatureId, nomenId, libellé
      , (2, 2, 'Viande V2')
      , (3, 3, 'Viande V3')
    ;
    
    SELECT '' AS NomenclatureViande, v.*, FamilleLibelle 
    FROM NomenclatureViande AS v
    JOIN Nomenclature AS n ON v.NomenId = n.NomenId
    JOIN Famille AS f ON n.FamilleId = f.FamilleId 
    ;
    
    INSERT INTO NomenclatureVegetal VALUES
        (1, 4, '1MABG')  -- nomenclatureId, nomenId, libellé
      , (2, 5, 'SOLTU')
      , (3, 6, 'LYPES')
    ;
    SELECT '' AS NomenclatureVegetal,  v.*, FamilleLibelle  
    FROM NomenclatureVegetal as v
    JOIN Nomenclature AS n ON v.NomenId = n.NomenId
    JOIN Famille AS f ON n.FamilleId = f.FamilleId 
    ;
    INSERT INTO Marchandise VALUES
        (1, 2, 'M1')   -- MarchandiseId, FamilleId, libelle
      , (2, 2, 'M2')   -- marchandise viande
      , (3, 3, 'M3')   -- marchandise végétale
      , (4, 2, 'M4')   -- marchandise viande
      , (5, 3, 'M5')   -- marchandise végétale
      , (6, 3, 'M6')   -- marchandise végétale
    ;
    SELECT '' AS Marchandise, * FROM Marchandise ;
    SELECT '' AS Lot, o.*
      , m.MarchandiseLibelle
      , f.FamilleLibelle
      , n.FamilleId AS NomenFamilleId
      , f2.FamilleLibelle  AS NomenFamilleLibelle     
    FROM Lot as o
    JOIN Marchandise AS m ON m.MarchandiseId = o.MarchandiseId 
    JOIN Famille AS f on m.FamilleId = f.FamilleId
    JOIN Nomenclature AS n ON o.NomenId = n.NomenId
    JOIN Famille AS f2 ON n.FamilleId = f2.FamilleId
    ;
    
    -- Lots de type viande
    INSERT INTO Lot VALUES
        (1, 1, 1, 15)  -- MarchandiseId, LotId, NomenId, Quantite 
      , (1, 2, 1, 25)
      , (1, 3, 1, 36)
      , (2, 1, 1, 211)
      , (2, 2, 1, 221)
    ;
    SELECT '' AS Lot, o.*
      , m.MarchandiseLibelle
      , f.FamilleLibelle
      , n.FamilleId AS NomenFamilleId
      , f2.FamilleLibelle  AS NomenFamilleLibelle     
    FROM Lot as o
    JOIN Marchandise AS m ON m.MarchandiseId = o.MarchandiseId 
    JOIN Famille AS f on m.FamilleId = f.FamilleId
    JOIN Nomenclature AS n ON o.NomenId = n.NomenId
    JOIN Famille AS f2 ON n.FamilleId = f2.FamilleId
    ;
    INSERT INTO LotViande VALUES
        (1, 1)  -- MarchandiseId, LotId 
      , (1, 2)
      , (1, 3)
      , (2, 1)
      , (2, 2)
    ;
    SELECT DISTINCT '' AS LotViande, v.*
      , m.MarchandiseLibelle
      , f.FamilleLibelle
      , n.NomenclatureLibelle      
    FROM LotViande AS v
    JOIN Marchandise AS m ON m.MarchandiseId = v.MarchandiseId 
    JOIN Famille AS f ON m.FamilleId = f.FamilleId
    JOIN Lot AS o ON v.LotId = o.LotId 
                 AND v.MarchandiseId = o.MarchandiseId
    JOIN NomenclatureViande AS n ON o.NomenId = n.NomenId
    JOIN Nomenclature AS n2 ON n2.FamilleId = f.FamilleId
    
    -- Lots de type Vegetal
    INSERT INTO Lot VALUES
       (3, 1, 4, 314)  -- MarchandiseId, LotId, NomenId, Quantite
     , (3, 2, 1, 321)
    ;
    INSERT INTO LotVegetal VALUES
        (3, 1, 'bon état')       -- MarchandiseId, LotId, Etat 
      , (3, 2, 'état médiocre')  -- MarchandiseId, LotId, Etat 
    ;
    SELECT '' AS LotVegetal, v.*
      , m.MarchandiseLibelle
      , f.FamilleId as FamilleId
      , f.FamilleLibelle
      , o.NomenId AS NomenId
      , n.FamilleId AS NomenFamilleId
    FROM LotVegetal AS v
    JOIN Marchandise AS m ON m.MarchandiseId = v.MarchandiseId 
    JOIN Famille AS f ON m.FamilleId = f.FamilleId
    JOIN Lot AS o ON v.LotId = o.LotId
                 AND v.MarchandiseId = o.MarchandiseId
    JOIN Nomenclature AS n ON o.NomenId = n.NomenId 
    ; 
    Il se trouve que le lot pour lequel MarchandiseId = 3 et LotId = 1 fait référence (via sa famille) à la famille "Produits d'origine animale", mais ce lot fait par ailleurs référence à une nomenclature faisant elle-même référence à la famille "Végétaux et produits végétaux".

    Cette anomalie sera détectée et invalidée par un trigger qu’il me reste à coder. J’arrive !
    (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à pout ça.

  3. #23
    Membre à l'essai
    Homme Profil pro
    Technicien réseau
    Inscrit en
    janvier 2023
    Messages
    14
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 37
    Localisation : France, Pas de Calais (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Technicien réseau
    Secteur : High Tech - Matériel informatique

    Informations forums :
    Inscription : janvier 2023
    Messages : 14
    Points : 15
    Points
    15
    Par défaut
    Bonsoir fsmrel,

    J'ai pu analyser un peu (et non sans mal) les différentes proposition que vous avez soumises. Et effectivement le dernier MCD semble se rapprocher de la bonne solution.

    Si je comprends bien, l'entité Famille permet de différencier le type de marchandise (donc de dossier , pour rappel libellé dossier = P, PP, A ou D).


    Concernant l'entité Lot , un trigger afin de vérifier que marchandise.familleid = nomenclature.familleid à l'insertion ou l'update d'une ligne sera donc indispensable .

    Concernant la spécialisation / généralisation , si je comprends bien , tout se joue en niveau des PK et FK de part et autres des sur-types et sous-types ?

    Je constate que l'on défini des PK composites. Peut-on ensuite définir une contrainte de FK comme le montre l'extrait du MLD suivant crée grâce au script SQL fourni par vos soins ? :

    Nom : Capture d’écran 2023-01-18 233845.png
Affichages : 65
Taille : 23,5 Ko

    Question de débutant probablement, mais à laquelle je n'ai pas trouvé de réponse satisfaisante.

    Merci pour ces avancées !

  4. #24
    Membre à l'essai
    Homme Profil pro
    Technicien réseau
    Inscrit en
    janvier 2023
    Messages
    14
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 37
    Localisation : France, Pas de Calais (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Technicien réseau
    Secteur : High Tech - Matériel informatique

    Informations forums :
    Inscription : janvier 2023
    Messages : 14
    Points : 15
    Points
    15
    Par défaut
    Rebonsoir fsmrel,

    Je viens de voir votre dernier post après avoir envoyé le mien.

    C'est très prometteur, mais cela va nécessiter un bonne gymnastique mentale et un bonne compréhension de tout ces concepts pour être capable de reproduire ce type de modélisation en autonomie.

    Je serais plus disponible en fin de semaine pour tenter de finaliser tout cela avec vous,

    Je vous souhaite une bonne fin de soirée

  5. #25
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    7 688
    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 688
    Points : 29 430
    Points
    29 430
    Billets dans le blog
    16
    Par défaut
    Bonsoir CAymeric,

    J’ai vu vos messages seulement au moment où j’allais envoyer ce qui suit. Donc je balance quand même, et examinerai plus tard les points qui vous tracassent.

    Voici donc le trigger invalidant les inserts délinquants.

    /*
    La famille de la nomenclature référencée par un lot doit être celle de la famille de ce lot.
    */
    CREATE TRIGGER Lot_Coherence_Famille_trigger ON Lot
    AFTER INSERT, UPDATE AS
    DECLARE @n as INT ;
    DECLARE @Engueulade AS VARCHAR(254)
    SET @n =
     (
        SELECT COUNT(*)
        FROM INSERTED
        WHERE  EXISTS
       (
        SELECT 
                o.MarchandiseId
              , o.LotId
              , o.NomenId
              , f.FamilleId
              , n.FamilleId
        FROM INSERTED AS o
             JOIN Marchandise AS m ON m.MarchandiseId = o.MarchandiseId 
             JOIN Famille AS f ON m.FamilleId = f.FamilleId
             JOIN Nomenclature AS n ON o.NomenId = n.NomenId 
        WHERE f.FamilleId <> n.FamilleId
       )
     )
    ;
    IF @n > 0
      BEGIN
        -- Les délinquants 
        SELECT  '' AS 'Lot delinquant => '
              , o.MarchandiseId, o.LotId
              , f.FamilleId as MarchandiseFamilleId
              , o.NomenId AS NomenId
              , n.FamilleId AS NomenFamilleId
        FROM INSERTED AS o
             JOIN Marchandise AS m ON m.MarchandiseId = o.MarchandiseId 
             JOIN Famille AS f ON m.FamilleId = f.FamilleId
             JOIN Nomenclature AS n ON o.NomenId = n.NomenId 
        WHERE f.FamilleId <> n.FamilleId
    ;   
        SET @Engueulade = 'La famille de la nomenclature référencée par un lot doit être celle de la famille de ce lot.'
        RAISERROR (@Engueulade, 16,1)  
        ROLLBACK
      END ; 
    Chose intéressante : dans le post #16, j’ai précisé qu’il fallait quatre triggers pour contrôler l’ensemble des lots, à savoir celui que j’avais fourni, affecté à la table LotVegetal, et ses cousins affectés respectivement aux tables LotViande, LotOrigineAnimale et LotAnimalVivant.

    Eh bien ! comme le nouveau trigger est appliqué à la table Lot, il remplace les quatre premiers !

    Mais c’est du donnant donnant, vous n’échapperez pas aux triggers de contrôle de l’exclusion entre les quatre sous-types LotVegetal, LotViande, LotOrigineAnimale et LotAnimalVivant...

    Et pour faire bonne mesure vous m’en mettrez quatre en plus pour garantir l’exclusion entre NomenclatureVegetal, NomenclatureViande, NomenclatureOrigineAnimale et Espece, du fait de leur généralisation...

    Ces huit triggers se ressemblant, une fois qu’on en a créé un, pour les autres, à epsilon près, c’est du copier/coller et de l’huile de coude.

    En voici un, plutôt bourrin, mais je n’ai pas fait dans la dentelle :
    CREATE TRIGGER LotViande_Exclusion_trigger ON LotViande 
    FOR INSERT, UPDATE AS
    
    DECLARE @n1 as INT ;
    DECLARE @n2 as INT ;
    DECLARE @n3 as INT ;
    DECLARE @Engueulade AS VARCHAR(254)
    
    SET @n1 =
      (SELECT COUNT(*) 
       FROM INSERTED
       WHERE EXISTS 
        (
         SELECT ''
         FROM INSERTED AS i
         JOIN LotVegetal AS v
             ON i.MarchandiseId = v.MarchandiseId AND i.LotId = v.LotId 
        )
      )
    IF @n1 > 0
      BEGIN
         SELECT '' AS 'Viol exclusion', i.MarchandiseId, i.LotId
         FROM INSERTED AS i
         JOIN LotVegetal AS v
             ON i.MarchandiseId = v.MarchandiseId AND i.LotId = v.LotId
         ;
         SET @Engueulade = 'Table LotViande : lot déjà affecté à la table LotVegetal.'
         RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
         ROLLBACK
      END ;
    
    SET @n2 =
      (SELECT COUNT(*) 
       FROM INSERTED
       WHERE EXISTS 
        (
         SELECT i.MarchandiseId, i.LotId
         FROM INSERTED AS i
         JOIN LotOrigineAnimale AS v
             ON i.MarchandiseId = v.MarchandiseId AND i.LotId = v.LotId 
        )
      )
    IF @n2 > 0
      BEGIN
         SELECT '' AS 'Viol exclusion', i.MarchandiseId, i.LotId
         FROM INSERTED AS i
         JOIN LotOrigineAnimale AS v
             ON i.MarchandiseId = v.MarchandiseId AND i.LotId = v.LotId 
         ;
         SET @Engueulade = 'Table LotViande : lot déjà affecté à la table LotOrigineAnimale.'
         RAISERROR (@Engueulade, 16,1)
         ROLLBACK
      END ;
    
    SET @n3 =
      (SELECT COUNT(*) 
       FROM INSERTED
       WHERE EXISTS 
        (
         SELECT i.MarchandiseId, i.LotId
         FROM INSERTED AS i
         JOIN LotAnimalVivant AS v
             ON i.MarchandiseId = v.MarchandiseId AND i.LotId = v.LotId 
        )
      )
    IF @n3 > 0
      BEGIN
         SELECT '' AS 'Viol exclusion', i.MarchandiseId, i.LotId
         FROM INSERTED AS i
         JOIN LotAnimalVivant AS v
             ON i.MarchandiseId = v.MarchandiseId AND i.LotId = v.LotId 
         ;
         SET @Engueulade = 'Table LotViande : lot déjà affecté à la table LotAnimalVivant.'
         RAISERROR (@Engueulade, 16,1)
         ROLLBACK
      END ;
    Pour les autres, à vous de jouer

    Et n’oubliez pas de quand vous estimez qu’on progresse, même si tout n'est pas compréhensible tout de suite, patience.
    (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à pout ça.

  6. #26
    Membre à l'essai
    Homme Profil pro
    Technicien réseau
    Inscrit en
    janvier 2023
    Messages
    14
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 37
    Localisation : France, Pas de Calais (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Technicien réseau
    Secteur : High Tech - Matériel informatique

    Informations forums :
    Inscription : janvier 2023
    Messages : 14
    Points : 15
    Points
    15
    Par défaut
    Bonjour fsmrel,

    Je travaille actuellement sur le sujet. J'arrive à quelque chose de plutôt prometteur concernant la contrainte d'unicité entre les 4 sous-types de Lot

    Création d'un fonction générique appelée dans une contrainte "CHECK" définie sur chaque sous-type qui vérifie si la PK composite (machandiseid , lotid) existe déjà dans un des 4 sous-types.

    Ce qui éviterais la redondance des 4 triggers et bien plus lisible. Je vous fais un retour détaillé dès que je serais sûr de mon coup !



    Bonne journée

  7. #27
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    7 688
    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 688
    Points : 29 430
    Points
    29 430
    Billets dans le blog
    16
    Par défaut
    Bonjour CAymeric,  

    Je vois que vous ne perdez pas votre temps ! Manifestement vous progressez, bonne idée que votre proposition de trigger, restera à secouer. celui-ci...
    Votre sujet est bien intéressant, aussi j’y fais référence dans la discussion où l’on traite de l’évolution de Looping. Pour Paprick, ça devient un cas d’école 

    En fouillant dans mes archives, j’ai retrouvé des vieilles discussions où non seulement je me suis frotté à PostgreSQL, mais en plus j’y avais programmé des triggers (bien sûr bourrins) ! Par exemple ici ou .

    Encore une fois, courage !
    (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à pout ça.

  8. #28
    Membre à l'essai
    Homme Profil pro
    Technicien réseau
    Inscrit en
    janvier 2023
    Messages
    14
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 37
    Localisation : France, Pas de Calais (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Technicien réseau
    Secteur : High Tech - Matériel informatique

    Informations forums :
    Inscription : janvier 2023
    Messages : 14
    Points : 15
    Points
    15
    Par défaut
    Voila donc le résultat :

    Définition de la fonction :
    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
    
    CREATE FUNCTION contrainte_lot_unique(_soustype1 TEXT, _soustype2 TEXT, _soustype3 TEXT, _lotid INTEGER, _marchandiseid INTEGER) 
    RETURNS BOOLEAN
    LANGUAGE PLPGSQL
    AS $function$
    DECLARE
    result BOOLEAN;
    sql TEXT;
    BEGIN
    sql = 'SELECT NOT EXISTS(
    SELECT (lotid, marchandiseid) FROM ' ||_soustype1|| ' WHERE lotid = '||_lotid|| 'AND marchandiseid = '||_marchandiseid||
    ' UNION ALL
    SELECT (lotid, marchandiseid) FROM ' ||_soustype2|| ' WHERE lotid = '||_lotid|| 'AND marchandiseid = '||_marchandiseid||
    ' UNION ALL
    SELECT (lotid, marchandiseid) FROM ' ||_soustype3|| ' WHERE lotid = '||_lotid|| 'AND marchandiseid = '||_marchandiseid||
    ')';
    execute sql INTO result;
    RETURN result;
       END;
    $function$;


    Puis définition d'une contrainte CHECK sur la fonction pour chaque sous-type de l'entité Lot :
    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
    
    ALTER TABLE IF EXISTS public.lotanimalvivant
        ADD CONSTRAINT lot_subtype_unique CHECK (contrainte_lot_unique('lotorigineanimale'::text, 'lotviande'::text, 'lotvegetal'::text, lotid, marchandiseid))
        NOT VALID;
    
    ALTER TABLE IF EXISTS public.lotvegetal
        ADD CONSTRAINT lot_subtype_unique CHECK (contrainte_lot_unique('lotorigineanimale'::text, 'lotviande'::text, 'lotanimalvivant'::text, lotid, marchandiseid))
        NOT VALID;
    
    ALTER TABLE IF EXISTS public.lotorigineanimale
        ADD CONSTRAINT lot_subtype_unique CHECK (contrainte_lot_unique('lotvegetal'::text, 'lotviande'::text, 'lotanimalvivant'::text, lotid, marchandiseid))
        NOT VALID;
    
    ALTER TABLE IF EXISTS public.lotviande
        ADD CONSTRAINT lot_subtype_unique CHECK (contrainte_lot_unique('lotorigineanimale'::text, 'lotvegetal'::text, 'lotanimalvivant'::text, lotid, marchandiseid))
        NOT VALID;
    Si la fonction retourne TRUE -> la PK(lotid, marchandiseid) n'existe pas dans les 3 autres sous-types donc -> INSERT autorisé.

    Si la fonction retourne FALSE - la PK(lotid, marchandiseid) existe dans l'un des sous-type, donc violation de contrainte CHECK -> INSERT impossible

    Que pensez vous de cette approche ?

  9. #29
    Membre à l'essai
    Homme Profil pro
    Technicien réseau
    Inscrit en
    janvier 2023
    Messages
    14
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 37
    Localisation : France, Pas de Calais (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Technicien réseau
    Secteur : High Tech - Matériel informatique

    Informations forums :
    Inscription : janvier 2023
    Messages : 14
    Points : 15
    Points
    15
    Par défaut
    Citation Envoyé par fsmrel Voir le message
    Bonjour CAymeric,  

    Je vois que vous ne perdez pas votre temps ! Manifestement vous progressez, bonne idée que votre proposition de trigger, restera à secouer. celui-ci...
    Votre sujet est bien intéressant, aussi j’y fais référence dans la discussion où l’on traite de l’évolution de Looping. Pour Paprick, ça devient un cas d’école 

    En fouillant dans mes archives, j’ai retrouvé des vieilles discussions où non seulement je me suis frotté à PostgreSQL, mais en plus j’y avais programmé des triggers (bien sûr bourrins) ! Par exemple ici ou .

    Encore une fois, courage !
    Bonsoir fsmrel,

    Je ne manquerais pas de m'y attarder

  10. #30
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    7 688
    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 688
    Points : 29 430
    Points
    29 430
    Billets dans le blog
    16
    Par défaut
    Bonsoir CAymeric,

    Citation Envoyé par CAymeric
    Si la fonction retourne TRUE -> la PK(lotid, marchandiseid) n'existe pas dans les 3 autres sous-types donc -> INSERT autorisé.

    Si la fonction retourne FALSE - la PK(lotid, marchandiseid) existe dans l'un des sous-type, donc violation de contrainte CHECK -> INSERT impossible

    Que pensez vous de cette approche ?
    20/20 et en cotant vache !

    Je me suis dit que j’arriverais bien à trouver un petit update piégeux, du genre :

    UPDATE LotVegetal
    SET MarchandiseId = 9, LotId = 1
    WHERE MarchandiseId = 3 AND LotId = 2 ;
    Eh bien, je suis refait :  

    Citation Envoyé par PostgreSQL
    ERROR: ERREUR: la nouvelle ligne viole la contrainte de vérification « lotvegetal » de la relation «  lot_subtype_unique »
    Conclusion : Bravo !

    Cela dit, pour m’en assurer, il a fallu que j’aille rechercher un vieux PostgreSQL, mais c’est jpgAmin 4 qui m’en a fait voir, j’ai dû finalement le réinstaller...
    (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à pout ça.

  11. #31
    Membre à l'essai
    Homme Profil pro
    Technicien réseau
    Inscrit en
    janvier 2023
    Messages
    14
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 37
    Localisation : France, Pas de Calais (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Technicien réseau
    Secteur : High Tech - Matériel informatique

    Informations forums :
    Inscription : janvier 2023
    Messages : 14
    Points : 15
    Points
    15
    Par défaut
    Bonsoir fsmrel,

    J'avais rencontré un problème lors des updates après d'un premier jet de ma fonction, que j'ai pu corriger par la suite. Petit bémol sur mon travail : j'aimerais pouvoir passer les noms de table dans les paramètres de la fonction sans "bricoler" en concaténant, histoire que cela fasse plus propre (si toutefois c'est possible).

    Je vais reproduire cette même contrainte au niveau des nomenclatures. Et pourquoi pas même me lancer le défi de coupler ces contraintes CHECK avec le système d'héritage proposé par Postgresql. Qui me permettrait, si j'ai bien compris le principe, de récupérer les données unifiés du sur-type et du sous-type en effectuant une simple requête sur le sous-type

    Grâce à nos échanges, cette partie du MCD (marchandises) prends une tournure que je n'aurais pas soupçonnée , et c'est fort encourageant ! Les acquis sur cette partie vont très certainement servir pour le reste du projet ! Il y a encore beaucoup de chemin mais c'est en bonne voie et m'amène du coup à remettre en question certains choix qui ont été fais.

    Je devrais arriver à une version finale de cette partie "Marchandises" la semaine prochaine.



    Encore pour ce partage de connaissances

  12. #32
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    7 688
    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 688
    Points : 29 430
    Points
    29 430
    Billets dans le blog
    16
    Par défaut
    Muy bien!

    Peaufinez, peaufinez ! A la semaine prochain donc, avec, espérons-le, un MCD stabilisé. On croise les doigts
    (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à pout ça.

  13. #33
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    7 688
    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 688
    Points : 29 430
    Points
    29 430
    Billets dans le blog
    16
    Par défaut
    Bonjour CAymeric,

    Citation Envoyé par CAymeric
    Je devrais arriver à une version finale de cette partie "Marchandises" la semaine prochaine.
    On attend avec impatience !
    (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à pout ça.

  14. #34
    Membre à l'essai
    Homme Profil pro
    Technicien réseau
    Inscrit en
    janvier 2023
    Messages
    14
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 37
    Localisation : France, Pas de Calais (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Technicien réseau
    Secteur : High Tech - Matériel informatique

    Informations forums :
    Inscription : janvier 2023
    Messages : 14
    Points : 15
    Points
    15
    Par défaut
    Bonjour fsmrel,

    Les impératifs pro font que ca piétine un peu !!! Je n'ai pas eu le temps voulu à consacrer au projet ! Mais je ne vous oublie pas

Discussions similaires

  1. [Entité-Association] Demande d'aide sur la modélisation
    Par zik''zak dans le forum Schéma
    Réponses: 1
    Dernier message: 27/01/2022, 13h20
  2. Demande d'aide pour modéliser ma base de données
    Par nell57 dans le forum Modélisation
    Réponses: 2
    Dernier message: 03/01/2014, 23h03
  3. [MCD] Aide modélisation d'un héritage
    Par zBaptman dans le forum Schéma
    Réponses: 12
    Dernier message: 27/11/2008, 15h43
  4. demande d'aide sur samba
    Par marcoss dans le forum Développement
    Réponses: 5
    Dernier message: 04/12/2003, 20h38
  5. [TPW][cours]Demande d'aide pour finir un programme
    Par jf dans le forum Turbo Pascal
    Réponses: 21
    Dernier message: 16/06/2003, 19h10

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