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 :

Gestion de blindtests; comment faire ?


Sujet :

Schéma

  1. #1
    Membre régulier
    Profil pro
    Inscrit en
    décembre 2003
    Messages
    439
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : décembre 2003
    Messages : 439
    Points : 106
    Points
    106
    Par défaut Gestion de blindtests; comment faire ?
    Salut !


    Je vais concevoir une appli pour la gestion de blindtests. Pour le moment, j'en suis au MCD.

    Les entités en présence sont :

    - Blindeur
    - Blindtest
    - Extrait

    Pour résumer le fonctionnement :

    - Un blindeur peut soit être l'animateur du blindtest, soit un des joueurs qui participent. Il faut au minimum 2 joueurs pour qu'un blindtest soit joué. Un blindtest n'est animé que par une seule personne.
    - Un blindtest est composé d'un certain nombre d'extraits (min 5 - max 70).
    - Un extrait peut être trouvé par un ou plusieurs joueurs, mais aussi par aucun.

    Le problème avec tout ça, c'est que je me retrouve avec un modèle circulaire... Et je vois pas trop comment faire autrement...
    Auriez-vous une idée ?

  2. #2
    Membre régulier
    Profil pro
    Inscrit en
    décembre 2003
    Messages
    439
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : décembre 2003
    Messages : 439
    Points : 106
    Points
    106
    Par défaut
    Finalement, je me demande si l'entité blindeur a vraiment raison d'exister... les animateurs peuvent être des attributs de l'entité blindtest et les joueurs peuvent être des attributs de l'entité extrait.
    Qu'en pensez-vous ?

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


    Citation Envoyé par Mike888 Voir le message
    Le problème avec tout ça, c'est que je me retrouve avec un modèle circulaire...
    Un modèle circulaire n’est pas toujours une mauvaise chose et ça se maîtrise.


    Citation Envoyé par Mike888 Voir le message
    les animateurs peuvent être des attributs de l'entité blindtest
    Si un animateur peut participer à plusieurs blindtests, au stade tabulaire (SQL) il va y avoir de la redondance dans la table BLINDTEST, donc viol des formes normales (2NF etc.), ce qui est fâcheux...


    Citation Envoyé par Mike888 Voir le message
    les joueurs peuvent être des attributs de l'entité extrait
    Si un extrait peut être associé à plus d’un joueur, alors il y a viol de la première forme normale (1NF), et là c’est carrément peccamineux…


    Merci de présenter un MCD (par exemple avec looping).



     
    Faites simple, mais pas plus simple ! (A. Einstein)
    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 »)

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

  4. #4
    Membre régulier
    Profil pro
    Inscrit en
    décembre 2003
    Messages
    439
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : décembre 2003
    Messages : 439
    Points : 106
    Points
    106
    Par défaut
    Merci pour ton message; pour les points 2 et 3, tu as tout à fait raison !

    Voici le MCD tel que je peux l'imaginer :

    Ce qui m'embête, ce sont les 2 rôles que peut prendre un blindeur...

    Pièce jointe 495637
    Images attachées Images attachées  

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


    Dans ce qui suit, j’utilise des concepts de Merise/2 (datant de 30 ans...) tels que l’héritage (spécialisation/généralisation), les contraintes inter-associations (inclusion, exclusion, ...), l’identification relative. Tout ceci est décrit dans l’ouvrage de référence de Dominique Nanci (RIP) et Bernard Espinasse Ingénierie des systèmes d'information : Merise deuxième génération (4e édition, 2001). Concernant certains termes utilisés pour les contraintes, tels que pivot, je joins le glossaire fourni par l’Afcet en 1990.


    Citation Envoyé par Mike888 Voir le message
    Un blindeur peut soit être l'animateur du blindtest, soit un des joueurs qui participent.
    Autant utiliser la technique de la spécialisation, en mettant en oeuvre une entité-type BLINDER, et les sous-types ANIMATEUR et JOUEUR. La lettre "T" figurant dans le triangle symbolisant l’héritage signifie qu’un blindeur est au moins un animateur ou un joueur. On suppose ici qu’un animateur peut aussi être un joueur : si ça ne peut jamais être le cas, alors on remplace "T" par "XT", la lettre "X" symbolisant l’exclusion. Si un animateur peut malgré tout être joueur dans un blindtest mais à condition de ne pas en être l’animateur, on met en oeuvre une contrainte d’exclusion (X) entre les associations ANIMER et PARTICIPER, avec les entités-types ANIMATEUR et BLINDTEST jouant le rôle de pivot. Looping connaît.

    Pour en venir au problème de circularité, dans le cas de votre MCD, du fait de la boucle BLINDEUR - TROUVE - EXTRAIT - CONTIENT - BLINDTEST - PARTICIPE - BLINDEUR, rien n’empêche effectivement qu’un joueur trouve des extraits de blindtests auxquels il n’a pas participé. Dans le diagramme ci-dessous, la ligne Maginot consiste à mettre en oeuvre une contrainte d’inclusion (I) entre les associations PARTICIPER et TROUVER pour signifier la règle suivante :

    Si un joueur a trouvé un extrait, c'est qu'il a participé au blindtest dont cet extrait fait partie.

    Nom : mike888_mcd_classique.png
Affichages : 205
Taille : 17,1 Ko

    Le problème est que, côté SQL, Looping ne génère rien pour garantir la contrainte. De son côté, on trouve dans l’ouvrage cité (Nanci et Espinasse) l’utilisation d’assertions, lesquelles sont prévues par la norme SQL, mais non proposées par les SGBD. Page 291, on voit l’utilisation d’un trigger, mais pour ORACLE seulement (avec un copier/coller malencontreux, faisant qu’on y parle de contrainte d’exclusion au lieu de contrainte d’inclusion, mais le lecteur est à même de rectifier...)

    Pour bétonner la ligne Maginot, on change carrément de tactique.

    On commence par reprendre le MCD en transformant les associations COMPOSER et PARTICIPER en entités-types associatives (utilisation des identifiants relatifs, cf. page 130 de l’ouvrage de référence). Ceci fait, l’association TROUVER est établie entre ces nouvelles entités-types : au stade MLD (puis SQL) il en résulte une table TROUVER comportant deux attributs blindtestId et blindtestId1, mais la kolossale finesse consiste à les fusionner manuellement en un seul attribut blindtestId, ce qui fait que quel que soit le chemin emprunté, on constate que pour un blindtest donné, un joueur ne peut trouver que des extraits de ce blindtest.


    MCD aménagé en vue de résoudre le problème de circularité

    Nom : mike888_conterminous_mcd.png
Affichages : 195
Taille : 15,6 Ko


    MLD (avec une seule colonne blindtestId pour la table TROUVER). Désolé, ça n’est pas du Looping !

    Nom : mike888_conterminous_mld.png
Affichages : 195
Taille : 16,4 Ko

    SQL

    
    CREATE TABLE BLINDEUR 
    (
            blindeurId       INT                NOT NULL
          , pseudo           VARCHAR(16)        NOT NULL
        , CONSTRAINT BLINDEUR_PK PRIMARY KEY (blindeurId)
        , CONSTRAINT BLINDEUR_AK UNIQUE (pseudo)
    ) ;
    
    CREATE TABLE ANIMATEUR 
    (
            animateurId      INT                NOT NULL 
        , CONSTRAINT ANIMATEUR_PK PRIMARY KEY (animateurId)
        , CONSTRAINT ANIMATEUR_BLINDEUR_FK FOREIGN KEY (animateurId)
              REFERENCES BLINDEUR (blindeurId) ON DELETE CASCADE
    ) ;
    
    CREATE TABLE BLINDTEST 
    (
            blindtestId      INT                NOT NULL
          , animateurId      INT                NOT NULL 
        , CONSTRAINT BLINDTEST_PK PRIMARY KEY (blindtestId)
        , CONSTRAINT BLINDTEST_ANIMATEUR_FK FOREIGN KEY (animateurId)
              REFERENCES ANIMATEUR (animateurId)
    ) ;
    
    CREATE TABLE EXTRAIT 
    (
            extraitId        INT                NOT NULL 
          , titre            VARCHAR(48)        NOT NULL
        , CONSTRAINT EXTRAIT_PK PRIMARY KEY (extraitId)
    ) ;
    
    CREATE TABLE COMPOSER 
    (
            blindtestId      INT                NOT NULL
          , extraitId        INT                NOT NULL
        ,  CONSTRAINT COMPOSER_PK PRIMARY KEY (blindtestId, extraitId)
        , CONSTRAINT COMPOSER_BLINDTEST_FK FOREIGN KEY (blindtestId)
              REFERENCES BLINDTEST (blindtestId) ON DELETE CASCADE
        , CONSTRAINT COMPOSER_EXTRAIT_FK FOREIGN KEY (extraitId)
              REFERENCES EXTRAIT (extraitId) ON DELETE CASCADE
    ) ;
    
    CREATE TABLE JOUEUR 
    (
            joueurId         INT                NOT NULL
       CONSTRAINT JOUEUR_PK PRIMARY KEY (joueurId),
       CONSTRAINT JOUEUR_BLINDEUR_FK FOREIGN KEY (joueurId)
              REFERENCES BLINDEUR (blindeurId) ON DELETE CASCADE
    ) ;
    
    CREATE TABLE PARTICIPER 
    (
            joueurId         INT                NOT NULL
          , blindtestId      INT                NOT NULL
        , CONSTRAINT PARTICIPER_PK PRIMARY KEY (joueurId, blindtestId)
        , CONSTRAINT PARTICIPER_JOUEUR_FK FOREIGN KEY (joueurId)
              REFERENCES JOUEUR (joueurId)
        , CONSTRAINT PARTICIPER_BLINDTEST_FK FOREIGN KEY (blindtestId)
              REFERENCES BLINDTEST (blindtestId) ON DELETE NO ACTION
    ) ;
    
    CREATE TABLE TROUVER 
    (
            joueurId         INT                NOT NULL
          , blindtestId      INT                NOT NULL
          , extraitId        INT                NOT NULL 
        , CONSTRAINT TROUVER_PK PRIMARY KEY (joueurId, blindtestId, extraitId) 
        , CONSTRAINT TROUVER_PARTICIPER_FK FOREIGN KEY (joueurId, blindtestId)
              REFERENCES PARTICIPER (joueurId,blindtestId) ON DELETE CASCADE
        , CONSTRAINT TROUVER_COMPOSER_FK FOREIGN KEY (blindtestId, extraitId)
              REFERENCES COMPOSER (blindtestId, extraitId) ON DELETE NO ACTION
    ) ;
    

     
    Faites simple, mais pas plus simple ! (A. Einstein)
    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 »)

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

  6. #6
    Membre régulier
    Profil pro
    Inscrit en
    décembre 2003
    Messages
    439
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : décembre 2003
    Messages : 439
    Points : 106
    Points
    106
    Par défaut
    Alors là, je suis époustouflé par ton développement !
    Es-tu prof dans la vie, pour avoir ces connaissances ? J'admire ta capacité d'abstraction...


    Il y a juste une chose qui me chiffonne (en gras ci-dessous) ; je crois qu'on ne s'est pas bien compris sur ce point-là :

    -> Un Blindtest possède d'office un et un seul animateur, et celui-ci ne fait qu'animer; en aucun cas il ne peut jouer au blindtest qu'il anime ! Mais cet animateur peut devenir un simple joueur lors d'un autre blindtest !

    Est-ce que ça change beaucoup de choses dans le MCD que tu as mis ci-dessus ?


    Sinon, 2 autres questions :

    - Pourquoi avoir relié l'association "Trouver" vers l'entité "Composer" plutôt que vers l'entité "Extrait" ?
    - Je ne saisis pas trop pourquoi l'entité "Joueur" ne participe pas au pivot ?

    Dernière question : quels outils (autre que looping) as-tu utilisé ?

    Encore merci pour ton aide !

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


    Citation Envoyé par Mike888 Voir le message
    Un Blindtest possède d'office un et un seul animateur, et celui-ci ne fait qu'animer; en aucun cas il ne peut jouer au blindtest qu'il anime ! Mais cet animateur peut devenir un simple joueur lors d'un autre blindtest !
    C’est ce que symbolise la contrainte d’exclusion (X) du 1er MCD de mon post précédent. Mais cette fois-ci pour mettre en oeuvre cette contrainte au stade SQL, il faut prévoir une assertion :

    CREATE ASSERTION ASSERT01 CHECK
        (NOT EXISTS (
                     SELECT y.animateurId
                     FROM   PARTICIPER AS x
                       JOIN BLINDTEST AS y  
                            ON x.blindtestId = y.blindtestId
                            AND x.joueurId = y.animateurId
                   )
        )
    ;
    
    Cette assertion provoque le rejet de toute opération tentant de faire participer un joueur à un blindtest qu’il anime.

    Mais à ce jour, aucun SGBD SQL ne nous propose l’instruction CREATE ASSERTION pourtant prévue par la norme SQL. Il faudra donc en passer par la solution lourde, c’est-à-dire la mise en oeuvre d’un trigger, sachant que la syntaxe de l’instruction CREATE TRIGGER varie d’un SGBD à l’autre (au fait, quel est le vôtre ?).


    Citation Envoyé par Mike888 Voir le message
    Pourquoi avoir relié l'association "Trouver" vers l'entité "Composer" plutôt que vers l'entité "Extrait" ?
    Comme je l’ai écrit dans mon post précédent, lors de la dérivation du MCD en MLD, Looping (et tout autre AGL) pond légitimement pour la table TROUVER deux attributs blindtestId et blindtestId1, l’un qui est la conséquence du chemin TROUVER → PARTICIPER → BLINDTEST tandis que l’autre est la conséquence du chemin TROUVER → COMPOSER → BLINDTEST ; pour garantir la contrainte d’inclusion, il est impératif que blindtestId = blindtestId1 et pour cela le plus simple est bien sûr de fondre les deux attributs en un seul.

    Si maintenant l’association TROUVER connecte PARTICIPER et EXTRAIT, alors on ne peut plus garantir la contrainte d’inclusion, car via le chemin TROUVER → EXTRAIT → COMPOSER → BLINDTEST on peut obtenir une liste de blindtests à partir de laquelle on ne peut rien affirmer quant à leur (il)légitimité...

    Pour résumer, d’une façon générale, en cas de circularité avec deux chemins permettant d’aller de A à B : A → C → B et A → D → B, la table A doit comporter la clé K de B (en seul exemplaire !), héritée à la fois de C et D (K doit donc être incluse dans la clé de C et dans celle de D).

    Si A est représentée par TROUVER, B par BLINDTEST, C par PARTICIPER et D par COMPOSER, on sait garantir la contrainte.

    Si A est représentée par TROUVER, B par BLINDTEST, C par PARTICIPER et D par EXTRAIT, on ne sait pas garantir la contrainte.

    A cette occasion, je signale que, suite à un copier/coller non contrôlé, mon code SQL précédent comporte une erreur en ce qui concerne la création de la table TROUVER. J’ai donc modifié le post en conséquence. Voici le code corrigeant la partie erronée :

    CREATE TABLE TROUVER 
    (
            joueurId         INT                NOT NULL
          , blindtestId      INT                NOT NULL
          , extraitId        INT                NOT NULL 
        , CONSTRAINT TROUVER_PK PRIMARY KEY (joueurId, blindtestId, extraitId) 
        , CONSTRAINT TROUVER_PARTICIPER_FK FOREIGN KEY (joueurId, blindtestId)
              REFERENCES PARTICIPER (joueurId,blindtestId) ON DELETE CASCADE
        , CONSTRAINT TROUVER_COMPOSER_FK FOREIGN KEY (blindtestId, extraitId)
              REFERENCES COMPOSER (blindtestId, extraitId) ON DELETE NO ACTION
    ) ;
    

    Citation Envoyé par Mike888 Voir le message
    Je ne saisis pas trop pourquoi l'entité "Joueur" ne participe pas au pivot
    Je suppose qu’il s’agit de la contrainte d’exclusion. On peut effectivement faire participer l’entité-type JOUEUR, cela dit c’est au stade SQL que tout se joue et seules les tables BLINDTEST et PARTICIPER sont parties prenantes.


    Citation Envoyé par Mike888 Voir le message
    Es-tu prof dans la vie ?
    Ben non... Je suis retraité depuis longtemps. Du temps où j’étais actif, je modélisais, je faisais le DBA, j’auditais, conseillais, réparais (on m’appelait le docteur des bases), engueulais, consolais, échangeais avec la Faculté et de temps en temps montais des cours. Aujourd’hui je préfère rendre service plutôt que rester avachi, passif, devant la télé. En tant qu’outil de modélisation et pour faire de jolis dessins, j’ai conservé celui que j’utilisais encore il y a 20 ans, à savoir AMC. pour les MLD, j’utilise aussi MySQL Workbench.


    A l’ occasion, il n’est pas interdit de liker et aussi de voter pour les compétences (profil pro) !




     
    Faites simple, mais pas plus simple ! (A. Einstein)
    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 »)

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

  8. #8
    Membre régulier
    Profil pro
    Inscrit en
    décembre 2003
    Messages
    439
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : décembre 2003
    Messages : 439
    Points : 106
    Points
    106
    Par défaut
    Waoow, j'ai du relire quelques fois pour bien tout comprendre ! Mais c'est très instructif ces explications.
    Merci d'avoir pris de votre temps pour me répondre, même si ça fait partie de vos occupations d'après ce que vous dites...

    Je vais utiliser MS-SQL Server 2017.
    Un trigger, j'en ai déjà entendu parler bien sûr; mais je n'en ai jamais utilisé à vrai dire (y'a surement de bons tutos sur le net).

    Bon ben j'ai plus qu'à me lancer !


    Edit : Est-ce que, avec la structure que vous avez mise sur pied, je peux régler le compteur pour chaque clé primaire sur incrémentation automatique (1) ? Ne risque-t-il pas d'y avoir des problèmes de dépendance ?

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


    A propos des triggers.

    Reprenons l’assertion précédente :

    CREATE ASSERTION ASSERT01 CHECK
        (NOT EXISTS (
                     SELECT y.animateurId
                     FROM   PARTICIPER AS x
                       JOIN BLINDTEST AS y  
                            ON x.blindtestId = y.blindtestId
                            AND x.joueurId = y.animateurId
                   )
        )
    ;
    
    Avec SQL Server on peut reprendre l’instruction SELECT pour l’héberger dans un trigger. La table fondamentalement en cause dans un viol de contrainte d’exclusion est la table PARTICIPER, elle va donc faire l’objet d’un trigger chargé de faire respecter la loi.

    Pour ma part, je préfère effectuer les contrôles (et les éventuels rejets) non pas sur des tables, mais sur des vues (voyez ce qu’en pense ordigil à ce sujet), mais rien ne vous empêche de votre côté de faire comme vous préférez.

    Je commence donc par créer la vue qui me convient, en l’espèce PARTICIPER_V :

    DROP VIEW IF EXISTS PARTICIPER_V ;
    
    GO
    
    CREATE VIEW PARTICIPER_V (joueurId, blindtestId)
    AS
        SELECT joueurId, blindtestId
        FROM   PARTICIPER
    ;
    
    GO
    
    Et ensuite je crée le trigger ad-hoc, appelons-le PARTICIPER_TR. Ce trigger servira aussi bien pour les inserts que pour les updates.

    Dans cet exercice, SQL Server transmet au trigger une table virtuelle, nommée INSERTED contenant les données que je lui ai moi-même transmises via un INSERT ou un UPDATE (dans le cas d’UPDATE, on a aussi une table virtuelle DELETED contenant les données avant update).

    Ce trigger est de type INSTEAD OF, c’est-à-dire que SQL Server ne fera que ce que je lui demande de faire.

    DROP TRIGGER IF EXISTS PARTICIPER_TR ;
    GO
    CREATE TRIGGER PARTICIPER_TR ON PARTICIPER_V INSTEAD OF INSERT, UPDATE
    AS
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @n as INT ;
    
    ---- pour debug ---- select '' as INSERTED, * from INSERTED  
    
    SET @N = 
        (SELECT COUNT(*) 
         FROM   INSERTED AS x
                  JOIN BLINDTEST AS y  
                       ON x.blindtestId = y.blindtestId
                       AND x.joueurId = y.animateurId)
    
    ---- pour debug ---- select @n as '@n' 
    
    ----------------------------------------------------
    -- Si on constate une tentative de viol de la
    -- contrainte d'exclusion, on rouspète 
    -- et on s'en va.
    ----------------------------------------------------
    IF @n > 0
        BEGIN
            SET @Engueulade = 'Un blinder ne peut pas participer au blindtest qu''il anime !'
            SELECT @Engueulade AS Engueulons 
            RAISERROR (@Engueulade, 0,1)  -- state = 0 pour les tests
     ----        RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
            RETURN  
        END
    ----------------------------------------------------
    -- On n'a pas constaté d'anomalie : 
    -- on met la table PARTICIPER à jour.
    ----------------------------------------------------
    INSERT INTO PARTICIPER (joueurId, blindtestId)
        SELECT joueurId, blindtestId 
        FROM INSERTED
    ;
    GO
    
    Un bout de jeu d’essai :

    ---------------------------
    --- Quelques blindeurs
    ---------------------------
    INSERT INTO BLINDEUR (blindeurId, pseudo) VALUES
        (1, 'fernand'), (2, 'raoul'), (3, 'paul'), (4,'mado')
      , (11, 'folace'), (12, 'jean'), (13, 'antoine'), (14, 'patricia') 
    ;
    ---------------------------
    --- Quelques animateurs
    ---------------------------
    INSERT INTO ANIMATEUR (animateurId) VALUES
        (1), (2), (3), (4) 
    ;
    ---------------------------
    --- Quelques joueurs
    ---------------------------
    INSERT INTO JOUEUR (joueurId) VALUES
        (1)
      , (4)
      , (11), (12), (13), (14) 
    ;
    ---------------------------
    --- Quelques blindtests
    ---------------------------
    INSERT INTO BLINDTEST (blindtestId, animateurId) VALUES
        (1, 1), (2, 4) 
    ;
    ---------------------------
    --- Quelques extraits
    ---------------------------
    INSERT INTO EXTRAIT (extraitId, titre) VALUES
        (1, 'Le dénicheur'), (2, 'Le vieux Léon'), (3, 'Marquise')
      , (4, 'Le scaphandrier'), (5, 'Le plat pays')
    ;
    ---------------------------
    --- Quelques COMPOSER
    ---------------------------
    INSERT INTO COMPOSER (blindtestId, extraitId) VALUES
        (1, 2), (1, 5)
      , (2, 2), (2, 3)
    ;
    ---------------------------------------------------------
    -- Le blindeur 1 anime le blindtest 1 et ne peut donc
    -- y participer, le trigger rouspétera. 
    ---------------------------------------------------------
    INSERT INTO PARTICIPER_V (blindtestId, joueurId) VALUES 
        (1, 1)
    ;
    -------------------------------------------------
    -- Le blindeur 4 peut participer au blindtest 1
    -------------------------------------------------
    INSERT INTO PARTICIPER_V (blindtestId, joueurId) VALUES 
        (1, 4)
    ;
    --------------------------------------------------------
    -- Le blindeur 4 ne peut participer au blindtest 2 
    -- car il l'anime, le trigger rouspétera.
    --------------------------------------------------------
    UPDATE PARTICIPER_V
        SET blindtestId = 2 WHERE joueurId = 4
    ;
    

    Citation Envoyé par Mike888 Voir le message
    Est-ce que, avec la structure que vous avez mise sur pied, je peux régler le compteur pour chaque clé primaire sur incrémentation automatique (1) ?
    Oui. Par exemple pour la table BLINDEUR :

     
    SET IDENTITY_INSERT BLINDEUR OFF ;
    DBCC CHECKIDENT (BLINDEUR, RESEED, 1) ;
    
    Cela suppose au préalable l’utilisation du paramètre IDENTITY :

    CREATE TABLE BLINDEUR 
    (
            blindeurId       INT IDENTITY       NOT NULL
          , pseudo           VARCHAR(16)        NOT NULL
        , CONSTRAINT BLINDEUR_PK PRIMARY KEY (blindeurId)
        , CONSTRAINT BLINDEUR_AK UNIQUE (pseudo)
    ) ;
    

    Citation Envoyé par Mike888 Voir le message
    Ne risque-t-il pas d'y avoir des problèmes de dépendance ?
    C’est-à-dire ? En tout cas, une chose est sûre, il faut éviter d’attribuer une valeur sémantique aux clés de type IDENTITY, les déboires sont très fréquents (voir dans le forum SQL Server). Il ne faut considérer que ce qui est garanti (unicité des clés et intégrité référentielle).


    Pour avoir une bonne connaissance de SQL Server, je vous recommande l’acquisition de l’ouvrage de Frédéric Brouard et al : SQL Server 2014 Développer et administrer pour la performance. (Comme tout ne tient pas dans l’ouvrage, Frédéric a fourni les liens vers la partie manquante).


     
    Faites simple, mais pas plus simple ! (A. Einstein)
    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 »)

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

  10. #10
    Membre régulier
    Profil pro
    Inscrit en
    décembre 2003
    Messages
    439
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : décembre 2003
    Messages : 439
    Points : 106
    Points
    106
    Par défaut
    Oulalah... Là, ça devient beaucoup, beaucoup trop compliqué pour moi; je n'ai pas compris grand chose... malheureusement !
    En fait, je n'ai que de très maigrelettes connaissances dans ce domaine...

    Je suis navré parce que vous vous investissez pas mal dans mon problème, et je vous en remercie.
    Faudrait peut-être que j'achète un bon bouquin pour me mettre à niveau.

  11. #11
    Membre régulier
    Profil pro
    Inscrit en
    décembre 2003
    Messages
    439
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : décembre 2003
    Messages : 439
    Points : 106
    Points
    106
    Par défaut
    En fait, lorsque j'exécute tout ceci à la suite, j'obtiens une erreur (lors de la création de la table Animateur) :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    CREATE TABLE BLINDEUR 
    (
            blindeurId       INT IDENTITY       NOT NULL
          , pseudo           VARCHAR(16)        NOT NULL
        , CONSTRAINT BLINDEUR_PK PRIMARY KEY (blindeurId)
        , CONSTRAINT BLINDEUR_AK UNIQUE (pseudo)
    ) ;
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    SET IDENTITY_INSERT BLINDEUR OFF ;
    DBCC CHECKIDENT (BLINDEUR, RESEED, 1) ;
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    CREATE TABLE ANIMATEUR 
    (
            animateurId      INT IDENTITY                NOT NULL 
        , CONSTRAINT ANIMATEUR_PK PRIMARY KEY (animateurId)
        , CONSTRAINT ANIMATEUR_BLINDEUR_FK FOREIGN KEY (animateurId)
              REFERENCES BLINDEUR (blindeurId) ON DELETE CASCADE
    ) ;
    Message d'erreur :

    Msg*1788, Niveau*16, État*0, Ligne*1
    Impossible de créer la clé étrangère en cascade 'ANIMATEUR_BLINDEUR_FK' lorsque la colonne de référence 'ANIMATEUR.animateurId' est une colonne d'identité.
    Msg*1750, Niveau*16, État*1, Ligne*1
    Impossible de créer la contrainte ou l'index. Voir les erreurs précédentes.

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


    Citation Envoyé par Mike888 Voir le message
    Là, ça devient beaucoup, beaucoup trop compliqué pour moi
    Au départ on est toujours largué, puis un beau jour ça fait comme pour la mayonnaise, ça prend et tout devient naturel, tout s’éclaire (ou quasiment...)



    Citation Envoyé par Mike888 Voir le message
    Message d’ereur
    Citation Envoyé par SQL Server
    Msg*1788, Niveau*16, État*0, Ligne*1
    Impossible de créer la clé étrangère en cascade 'ANIMATEUR_BLINDEUR_FK' lorsque la colonne de référence 'ANIMATEUR.animateurId' est une colonne d'identité.
    Msg*1750, Niveau*16, État*1, Ligne*1
    Impossible de créer la contrainte ou l'index.
    SQL Server a raison... Créons d’abord une ligne pour la table BLINDEUR dotée du paramètre IDENTITY :

    INSERT INTO BLINDEUR (pseudo) VALUES
        ('Fernand')
    ;
    
    On n’a pas fourni de valeur pour l’attribut blindeurId, puisque c’est le SGBD qui désormais s’en occupe. Le blindeur Fernand est donc désormais identifié par la valeur attribuée par le SGBD, supposons que cette valeur soit le nombre 314.

    Passons à la table ANIMATEUR et occupons-nous de Fernand. Je rappelle la création de la table :

    CREATE TABLE ANIMATEUR 
    (
            animateurId      INT                NOT NULL 
        , CONSTRAINT ANIMATEUR_PK PRIMARY KEY (animateurId)
        , CONSTRAINT ANIMATEUR_BLINDEUR_FK FOREIGN KEY (animateurId)
              REFERENCES BLINDEUR (blindeurId) ON DELETE CASCADE
    ) ;
    Examinons la contrainte portant le doux nom de ANIMATEUR_BLINDEUR_FK. Elle se lit ainsi :

    Si l’attribut animateurId vaut x dans la table ANIMATEUR, il doit nécessairement faire référence à l’attribut blindeurId valant x dans la table BLINDEUR.

    Ainsi pour Fernand :

    Pour que Fernand soit animateur, il est nécessaire que dans la table ANIMATEUR, l’attribut animateurId prenne la valeur 314.

    En ce qui concerne la table ANIMATEUR, le paramètre IDENTITY n’a pas à être présent, il ne pourrait que semer la pagaille : seules les clés primaires des tables pour lesquelles on crée ex nihilo sont à affubler de ce paramètre, à savoir BLINDEUR, BLINDTEST, EXTRAIT.

    Je vous quitte, car j’ai une activité musicale qui va m’occuper pendant quelques heures. ♪ ♫

     
    Faites simple, mais pas plus simple ! (A. Einstein)
    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 »)

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

  13. #13
    Membre régulier
    Profil pro
    Inscrit en
    décembre 2003
    Messages
    439
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : décembre 2003
    Messages : 439
    Points : 106
    Points
    106
    Par défaut
    Une fois encore, merci pour vos lumières.
    Donc, là, comme c'est là, je peux commencer à programmer et à utiliser la base de données ou il y a encore des choses à faire ?
    Je vais essayer d'employer LINQ pour manipuler la DB depuis C#. Etes-vous partisan de cette méthode ?


    Hors-sujet :

    Vous jouez d'un instrument ?

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


    Avant de foncer tête baissée, il y a quelques précautions à prendre. Ainsi il n’est pas bon de faire figurer dans les requêtes les valeurs de clés primaires générées par le SGBD quand le paramètre IDENTITY est dans le coup, car comme je l’ai précisé dans le post #9, ce paramètre peut être source de déboires.

    Dans ce même post, reprenons cette partie du jeu d’essai :

    ---------------------------
    --- Quelques blindeurs
    ---------------------------
    INSERT INTO BLINDEUR (blindeurId, pseudo) VALUES
        (1, 'fernand'), (2, 'raoul'), (3, 'paul'), (4,'mado')
      , (11, 'folace'), (12, 'jean'), (13, 'antoine'), (14, 'patricia') 
    ;
    ---------------------------
    --- Quelques animateurs
    ---------------------------
    INSERT INTO ANIMATEUR (animateurId) VALUES
        (1), (2), (3), (4) 
    ;
    
    Pour créer des animateurs, on a repris les valeurs affectées par nos soins à la colonne blindeurId de la table BLINDEUR : 1, 2, 3, 4.

    Si on utilise le paramètre IDENTITY pour la table BLINDEUR :

    CREATE TABLE BLINDEUR 
    (
            blindeurId       INT IDENTITY       NOT NULL
          , pseudo           VARCHAR(16)        NOT NULL
        , CONSTRAINT BLINDEUR_PK PRIMARY KEY (blindeurId)
        , CONSTRAINT BLINDEUR_AK UNIQUE (pseudo)
    ) ;
    
    Alors le code de création des blindeurs se simplifie :

    ---------------------------
    --- Quelques blindeurs
    ---------------------------
    INSERT INTO BLINDEUR (blindeurId, pseudo) VALUES
        ('fernand'), ('raoul'), ('paul'), ('mado')
      , ('folace'), ('jean'), ('antoine'), ('patricia')
    ;
    
    So far, so good. Mais on peut se demander quelles valeurs ont été affectées cette fois-ci à blindeurId par SQL Server, il va falloir effectuer des recherches.
    Afin d’éviter cela, pour créer les animateurs il est raisonnable de coder :

    INSERT INTO ANIMATEUR (animateurId) 
        SELECT blindeurId 
        FROM BLINDEUR 
        WHERE pseudo IN ('fernand', 'raoul', 'paul', 'mado')
    ;
    
    Ou à l’unité :

    INSERT INTO ANIMATEUR (animateurId) 
        SELECT blindeurId 
        FROM BLINDEUR 
        WHERE pseudo = 'fernand'
    ;
    INSERT INTO ANIMATEUR (animateurId) 
        SELECT blindeurId 
        FROM BLINDEUR 
        WHERE pseudo = 'raoul'
    ;
    Etc.
    
    Ainsi on n’a nul besoin de la connaissance des valeurs de la clé primaire, calculées par le SGBD, elles restent sous le capot et ne nous intéressent pas. En réalité, l’objectif est de ne se servir que des propriétés naturelles des objets, celles dont on a la maîtrise, et d’ignorer les propriétés artificielles calculées uniquement par le SGBD. Ainsi est-on amené à doubler chaque clé primaire de type IDENTITY d’une clé alternative telle que {pseudo} dans le cas de la table BLINDEUR : grâce à cette clé alternative, on a pu insérer sans problème dans la table ANIMATEUR. De façon générale les valeurs des clés primaires peuvent se retrouver en tant que valeurs de clés étrangères dans les autres tables (intégrité référentielle oblige), mais en aucun cas les valeurs des clés alternatives, elles n’existent que comme points d’entrée dans le système, d’accès au données, comme illustré dans l’exemple des blindeurs et des animateurs : les valeurs 'fernand', 'raoul', 'paul', 'mado' ne sont présentes que dans la table BLINDEUR, ce qui ne nous empêche pas, comme on vient de le voir, de nous en servir pour créer les animateurs.

    Pour savoir qui sont les animateurs :

    SELECT pseudo AS Animateur 
    FROM ANIMATEUR AS x
    JOIN BLINDEUR AS y on x.animateurId = y.blindeurId
    ;
    
    Réponse du SGBD :

    Animateur
    ---------
    fernand
    raoul
    paul
    mado
    
    Passons aux joueurs. La technique est la même :

    INSERT INTO JOUEUR (joueurId)
        SELECT blindeurId 
        FROM BLINDEUR 
        WHERE pseudo IN ('fernand', 'mado', 'folace', 'jean', 'antoine', 'patricia')
    ;
    
    Pour savoir qui sont les joueurs :

    SELECT pseudo AS Joueur 
    FROM JOUEUR AS x
    JOIN BLINDEUR AS y on x.joueurId = y.blindeurId
    ;
    
    La table BLINDTEST n’ayant pas de clé alternative que nous maîtrisions, on lui en attribue une, disons {blindtestCode} :

    CREATE TABLE BLINDTEST 
    (
            blindtestId      INT IDENTITY       NOT NULL
          , blindtestCode     VARCHAR(8)         NOT NULL
          , animateurId      INT                NOT NULL 
        , CONSTRAINT BLINDTEST_PK PRIMARY KEY (blindtestId)
        , CONSTRAINT BLINDTEST_AK UNIQUE (blindtestCode)
        , CONSTRAINT BLINDTEST_ANIMATEUR_FK FOREIGN KEY (animateurId)
              REFERENCES ANIMATEUR (animateurId)
    ) ;
    

    Affectons les animateurs aux blindtests (on peut by-passer la table ANIMATEUR car par transitivité un blindtest détermine un blindeur). fernand anime le blindtest 'blindt01' et mado anime le blindtest 'blindt02' :

    INSERT INTO BLINDTEST (blindtestCode, animateurId)
        SELECT 'blindt01', blindeurId
        FROM    BLINDEUR
        WHERE pseudo = 'fernand'    
    ;
    INSERT INTO BLINDTEST (blindtestCode, animateurId)
        SELECT 'blindt02', blindeurId
        FROM    BLINDEUR
        WHERE pseudo = 'mado'    
    
    Pour voir le résultat :

    SELECT '' as blindtest, blindtestCode, pseudo 
    FROM BLINDTEST AS x
    JOIN BLINDEUR AS y ON x.animateurId = y.blindeurId
    ;
    
    Réponse du SGBD :

    blindtest  blindtestCode    pseudo
    ---------  -------------    ------
               blindt01         fernand
               blindt02         mado
    
    Le cas des participations des joueurs est intéressant. En effet, la table PARTICIPER est dotée de la vue PARTICIPER_V laquelle a pour attributs joueurId, blindtestId, mais ceux-ci sont artificiels, et on voudrait les remplacer par leurs homologues naturels : pseudo et blindtestCode...

    Qu’à cela ne tienne, changeons le code de la vue :

    CREATE VIEW PARTICIPER_V (pseudo, blindtestCode)
    AS
        SELECT  pseudo, blindtestCode
        FROM    PARTICIPER as x 
           JOIN BLINDEUR as y ON x.joueurId = y.blindeurId  ------ pour avoir le pseudo
           JOIN BLINDTEST as z ON x.blindtestId = z.blindtestId  ------ pour avoir le blindtestCode
    ;
    
    La vue permet évidemment d’accéder à la table PARTICIPER, c’est sa finalité 1re. Mais il faut avoir accès le pseudo du joueur, d’où le JOIN avec la table BLINDEUR, et il faut avoir accès au blindtestCode, d’où la jointure avec la table BLINDTEST.

    Le trigger PARTICIPER_TR est évidemment impacté, puisqu’il s’attend jusqu’ici à lire une table temporaire INSERTED ayant pour en-tête celui qu’avait la vue avant son changement, à savoir {joueurId, blindtestId}, alors que désormais la table INSERTED a pour en-tête {pseudo, blindtestCode}. Une façon d’aménager le trigger est la suivante :

    (1) On déclare une variable de type table et d’en-tête {joueurId, blindtestId}, nommons-la @inserted :

    DECLARE @inserted TABLE
    (
        joueurId     INT
      , blindtestId  INT
    ) ;
    
    (2) On insère dans @inserted les valeurs de joueurId, blindtestId en fonction des valeurs de pseudo et blindtestCode fournies par le SGBD dans INSERTED :

    INSERT INTO @inserted (joueurId, blindtestId)
        SELECT DISTINCT y.blindeurId, z.blindtestId
        FROM   INSERTED AS x
          JOIN BLINDEUR AS y ON x.pseudo = y.pseudo
          JOIN BLINDTEST AS z ON x.blindtestCode = z.blindtestCode
    
    Contenu de INSERTED, transmis au trigger par le SGBD :

    pseudo    blindtestCode
    ------    -------------
    folace    blindt01
    folace    blindt02
    jean      blindt01
    
    A titre de curiosité, contenu de @inserted après INSERT :

    joueurId   blindtestId
    --------   -----------
    317        9
    317        10
    318        9
    
    Mais les valeurs affichées nous indiffèrent complètement, puisque seules les valeurs des propriétés naturelles nous intéressent, d’autant que les valeurs artificielles affichées peuvent parfaitement changer dans le temps...

    (3) Si l’on n’a pas constaté de viol de la contrainte d’exclusion, on met à jour la table PARTICIPER, non plus à partir de la table INSERTED mais à partir de la table @inserted :

    INSERT INTO PARTICIPER (joueurId, blindtestId)
        SELECT joueurId, blindtestId 
        FROM @inserted
    ;
    
    Le code du trigger :

    DROP TRIGGER IF EXISTS PARTICIPER_TR ;
    GO
    CREATE TRIGGER PARTICIPER_TR ON PARTICIPER_V INSTEAD OF INSERT, UPDATE
    AS
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @n as INT ;
    DECLARE @inserted TABLE
    (
        joueurId     INT
      , blindtestId  INT
    ) ;
    ---- pour debug ---- select '' as INSERTED, * from INSERTED  
    
    INSERT INTO @inserted (joueurId, blindtestId)
        SELECT DISTINCT y.blindeurId, z.blindtestId
        FROM   INSERTED AS x
          JOIN BLINDEUR AS y ON x.pseudo = y.pseudo
          JOIN BLINDTEST AS z ON x.blindtestCode = z.blindtestCode
    ;
    ---- pour debug ---- select '' as '@inserted', * from @inserted 
    
    SET @N = 
        (SELECT COUNT(*) 
         FROM   @inserted AS x
                  JOIN BLINDTEST AS y  
                       ON x.blindtestId = y.blindtestId
                       AND x.joueurId = y.animateurId)
    
    ---- pour debug ---- select @n as '@n' 
    
    ----------------------------------------------------
    -- Si on constate une tentative de viol de la
    -- contrainte d'exclusion, on rouspète 
    -- et on s'en va.
    ----------------------------------------------------
    IF @n > 0
        BEGIN
            SET @Engueulade = 'Un blinder ne peut pas participer au blindtest qu''il anime !'
            SELECT @Engueulade AS Engueulons  --, * FROM INSERTED
            RAISERROR (@Engueulade, 0,1)  -- state = 0 pour les tests
     ----        RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
            RETURN  
        END
    ----------------------------------------------------
    -- On n'a pas constaté d'anomalie : 
    -- on met la table PARTICIPER à jour.
    ----------------------------------------------------
    
    INSERT INTO PARTICIPER (joueurId, blindtestId)
        SELECT joueurId, blindtestId 
        FROM @inserted
    ;
    GO
    
    Insérons quelques participations (avec des valeurs « naturelles » c’est quand même plus simple !) :

    INSERT INTO PARTICIPER_V (pseudo, blindtestCode) VALUES 
        ('folace', 'blindt01') 
      , ('folace', 'blindt02') 
      , ('jean', 'blindt01') 
    
    Interrogeons le SGBD quant au résultat :

    SELECT '' as participer, blindtestCode, pseudo 
    from PARTICIPER_V
    ;
    
    Ce qu’il en dit :

    participer    blindtestCode    pseudo
    ----------    -------------    ------
                  blindt01         folace
                  blindt02         folace
                  blindt01         jean
    
    Une tentative de viol, contrée par le trigger :

    -- Le blindeur fernand anime le blindtest blindt01 
    -- et ne pourra donc y participer, le trigger veillant au grain 
    
    INSERT INTO PARTICIPER_V (pseudo, blindtestCode) VALUES 
        ('fernand', 'blindt01')
    ;
    
    Au résultat :

    Un blinder ne peut pas participer au blindtest qu'il anime !  
    
    Une autre tentative de viol, par UPDATE cette fois-ci :

    -- Le blindeur mado a le droit de participer
    -- au blindtest blindt01.
    -- Par contre, mado anime le  blindtest blindt02 
    -- et ne pourra donc y participer, le trigger veillant au grain. 
    
    INSERT INTO PARTICIPER_V (pseudo, blindtestCode) VALUES 
        ('mado', 'blindt01')
    ;
    UPDATE PARTICIPER_V
        SET blindtestCode = 'blindt02' WHERE pseudo = 'mado'
    ;
    
    Attention ! Le trigger est à compléter, car en cas d’update et s’il n’y a pas de viol, ça n’est pas un INSERT qu’l faut exécuter pour la table PARTICIPER mais un UPDATE. On fera ça plus tard.

    Maintenant, le trigger PARTICIPER_TR est-il la panacée pour garantir la contrainte d’exclusion ? Par exemple, que se passe-t-il si par mise à jour de la table BLINDTEST, le joueur du blindtest t1 devient animateur de ce blindtest à la place de son animateur actuel ?


    Je fais une pause et reviendrai pour traiter des extraits et tout ce qui s’en suit.


    Citation Envoyé par Mike888
    Je vais essayer d'employer LINQ pour manipuler la DB depuis C#.
    Désolé, mais d’une part on sort du domaine de la modélisation des données et d’autre part je ne connais ni C# ni LINQ (quand je programmais, c’était en assembleur...)

    N.B.
    Dans le post #9, j’ai corrigé une erreur de rédaction et remplacé "inclusion" par "exclusion".


    Citation Envoyé par Mike888
    Hors-sujet :
    Vous jouez d'un instrument ?
    Oui, guitare classique ♪ ♫.


     
    Faites simple, mais pas plus simple ! (A. Einstein)
    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 »)

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

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


    Concernant les extraits, {titre} peut servir de clé alternative pour la table EXTRAIT, mais peut-être des extraits différents ont-ils le même titre ? Peu importe, prenons la peine de mettre en oeuvre un attribut extraitCode et créons une clé alternative {extraitCode} :

    CREATE TABLE EXTRAIT 
    (
            extraitId        INT  IDENTITY      NOT NULL 
          , extraitCode      VARCHAR(8)         NOT NULL
          , titre            VARCHAR(48)        NOT NULL
        , CONSTRAINT EXTRAIT_PK PRIMARY KEY (extraitId)
        , CONSTRAINT EXTRAIT_AK UNIQUE (extraitCode)
    ) ;
    
    Création de quelques extraits

    INSERT INTO EXTRAIT (extraitCode, titre) VALUES
        ('denich', 'Le dénicheur'), ('oldleon', 'Le vieux Léon'), ('marquise', 'Marquise')
      , ('scaph', 'Le scaphandrier'), ('platpays', 'Le plat pays')
    ;
    
    Au résultat

    extraitCode    titre
    -----------    ---------------
    denich        Le dénicheur
    oldleon       Le vieux Léon
    marquise      Marquise
    scaph         Le scaphandrier
    platpays      Le plat pays
    

    Associons maintenant des blindtests et des extraits (on s’accroche !)

    INSERT INTO COMPOSER (blindtestId, extraitId) VALUES
          ((select blindtestId from BLINDTEST where blindtestCode = 'blindt01')
         , (select extraitId from EXTRAIT where extraitCode = 'oldleon'))
    
       , ((select blindtestId from BLINDTEST where blindtestCode = 'blindt01') 
         , (select extraitId from EXTRAIT where extraitCode = 'platpays'))
    
       , ((select blindtestId from BLINDTEST where blindtestCode = 'blindt02') 
         , (select extraitId from EXTRAIT where extraitCode = 'oldleon'))
    
       , ((select blindtestId from BLINDTEST where blindtestCode = 'blindt02') 
         , (select extraitId from EXTRAIT where extraitCode = 'marquise'))
       ;
    
    Au résultat :

    SELECT blindtestCode, extraitCode, titre 
    FROM   COMPOSER as x
      JOIN BLINDTEST as y ON x.blindtestId = y.blindtestId
      JOIN EXTRAIT as z ON x.extraitId = z.extraitId
    ;
    
    =>

    blindtestCode    extraitCode    titre
    -------------    -----------    -------------
    blindt01         oldleon        Le vieux Léon
    blindt01         platpays       Le plat pays
    blindt02         oldleon        Le vieux Léon
    blindt02         marquise       Marquise
    
    Si les inserts paraissent lourds, on pourra mettre en oeuvre une vue COMPOSER_V dont l’en-tête contiendra les attributs naturels blindtestCode et extraitCode :

    DROP VIEW IF EXISTS COMPOSER_V ;
    GO
    CREATE VIEW COMPOSER_V (blindtestCode, extraitCode)
    AS
        SELECT  blindtestCode, extraitCode
        FROM    COMPOSER as x 
           JOIN BLINDTEST as y ON x.blindtestId = y.blindtestId  -- pour avoir le blindtestCode
           JOIN EXTRAIT as z ON x.extraitId = z.extraitId  -- pour avoir le extraitCode
    ;
    GO
    
    Le trigger correspondant (à mettre à niveau pour traiter des updates)

    DROP TRIGGER IF EXISTS COMPOSER_TR ;
    GO
     CREATE TRIGGER COMPOSER_TR ON COMPOSER_V INSTEAD OF INSERT, UPDATE
    AS
    
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @n as INT ;
    DECLARE @inserted TABLE
    (
        blindtestId  INT
      , extraitId    INT
    ) ;
    ---- pour debug ---- select '' as INSERTED, * from INSERTED  
    ---- pour debug ---- select '' as DELETED, * from DELETED  
    
    INSERT INTO @inserted (blindtestId, extraitId)
        SELECT DISTINCT y.blindtestId, z.extraitId
        FROM   INSERTED AS x
          JOIN BLINDTEST AS y ON x.blindtestCode = y.blindtestCode
          JOIN EXTRAIT AS z ON x.extraitCode = z.extraitCode
    ;
    ---- pour debug ---- select '' as '@inserted', * from @inserted 
    
    if (select count (*) from deleted) = 0
        begin
            INSERT INTO COMPOSER (blindtestId, extraitId)
                SELECT blindtestId, extraitId 
                FROM @inserted
            ;
        end
    /*
    else
        begin 
            UPDATE PARTICIPER
                ...
        end
    */
    GO
    
    Les inserts :

    INSERT INTO COMPOSER_V (blindtestCode, extraitCode) VALUES
          ('blindt01', 'oldleon')
        , ('blindt01', 'platpays')
        , ('blindt02', 'oldleon')
        , ('blindt02', 'marquise')
       ;
    
    Au résultat :

    SELECT blindtestCode, extraitCode
    FROM   COMPOSER_V 
    ; 
    
    =>

    blindtestCode    extraitCode
    -------------    -----------
    blindt01         oldleon 
    blindt01         platpays
    blindt02         oldleon 
    blindt02         marquise
    
    Pour voir aussi le titre, comme avant :

    SELECT blindtestCode, x.extraitCode, titre
    FROM   COMPOSER_V as x
      JOIN EXTRAIT as y ON x.extraitCode = y.extraitCode 
    
    =>

    blindtestCode    extraitCode    titre
    -------------    -----------    -------------
    blindt01         oldleon        Le vieux Léon
    blindt01         platpays       Le plat pays
    blindt02         oldleon        Le vieux Léon
    blindt02         marquise       Marquise
    
    Les trouvailles de folace :

    insert into TROUVER (joueurId, blindtestId, extraitId) values
          ((select blindeurId from BLINDEUR where pseudo = 'folace')
         , (select blindtestId from BLINDTEST where blindtestCode = 'blindt01')
         , (select extraitId from EXTRAIT where extraitCode = 'oldleon'))
    
       ,  ((select blindeurId from BLINDEUR where pseudo = 'folace')
         , (select blindtestId from BLINDTEST where blindtestCode = 'blindt02')
         , (select extraitId from EXTRAIT where extraitCode = 'marquise'))
    ;
    
    Au résultat :

    SELECT pseudo, blindtestCode, extraitCode, titre 
    FROM   TROUVER as x
      JOIN BLINDEUR as y ON x.joueurId = y.blindeurId
      JOIN BLINDTEST as z ON x.blindtestId = z.blindtestId
      JOIN EXTRAIT as t ON x.extraitId = t.extraitId
    ;
    
    =>

    pseudo    blindtestCode    extraitCode    titre
    ------    -------------    -----------    -------------
    folace    blindt01         oldleon        Le vieux Léon
    folace    blindt02         marquise       Marquise
    

    Nom : hubuc.jpg
Affichages : 157
Taille : 30,9 Ko



     
    Faites simple, mais pas plus simple ! (A. Einstein)
    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 »)

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

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


    En prime : les triggers mis à niveau (à d’éventuelles erreurs de copier/coller près, vous me direz...) pour prendre en compte les updates. Si le code vous paraît abscons, n’hésitez pas à poser des questions...


    Trigger COMPOSER_TR :

    DROP TRIGGER IF EXISTS COMPOSER_TR ;
    GO
    CREATE TRIGGER COMPOSER_TR ON COMPOSER_V INSTEAD OF INSERT, UPDATE
    AS
    
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @n as INT ;
    DECLARE @inserted TABLE
    (
        blindtestId  INT
      , extraitId    INT
    ) ;
    DECLARE @deleted TABLE
    (
        blindtestId  INT
      , extraitId    INT
    ) ;
    ---- pour debug ---- select '' as INSERTED, * from INSERTED  
    ---- pour debug ---- select '' as DELETED, * from DELETED  
    
    ------------------------------------------------------
    -- traduction des clés alternatives (naturelles) en
    -- clés primaires (artificielles)
    ------------------------------------------------------
    INSERT INTO @inserted (blindtestId, extraitId)
        SELECT DISTINCT y.blindtestId, z.extraitId
        FROM   INSERTED AS x
          JOIN BLINDTEST AS y ON x.blindtestCode = y.blindtestCode
          JOIN EXTRAIT AS z ON x.extraitCode = z.extraitCode
    ;
    ---- pour debug ---- select '' as '@inserted', * from @inserted 
    
    ---------------------------------------------------
    -- S’il y a quelque chose dans DELETED
    -- alors c’est qu’on effectue un UPDATE,
    -- on commence par faire le ménage dans COMPOSER.
    ---------------------------------------------------
    IF (SELECT COUNT(*) FROM DELETED) > 0
        BEGIN
            INSERT INTO @deleted (blindtestId, extraitId)
                SELECT DISTINCT y.blindtestId, z.extraitId
                FROM   DELETED AS x
                  JOIN BLINDTEST AS y ON x.blindtestCode = y.blindtestCode
                  JOIN EXTRAIT AS z ON x.extraitCode = z.extraitCode
            ;
    ---- pour debug ---- select '' as '@deleted' , * from @deleted
    
            DELETE FROM COMPOSER
                WHERE blindtestId IN (SELECT blindtestId FROM @deleted)
                  AND extraitId IN (SELECT extraitId FROM @deleted)  
           ;
    
    ---- pour debug ---- select '' as 'composer après delete', * from COMPOSER
    
        END
    
    ---------------------------------
    -- On est en INSERT ou UPDATE
    ---------------------------------
    INSERT INTO COMPOSER (blindtestId, extraitId)
        SELECT blindtestId, extraitId 
        FROM @inserted
    ;
    GO
    
    A noter qu’avec ce trigger, je n’utilise pas l’instruction UPDATE pour mettre à jour la table COMPOSER. Je préfère d’abord y supprimer ce que propose la table temporaire DELETED puis insérer ce que propose la table temporaire INSERTED En effet, supposons qu’on ait par exemple la situation suivante, l’affaire devient scabreuse... :

    INSERTED    blindtestCode    extraitCode
                -------------    -----------
                blindt03         marquise
                blindt04         vieuxleo
    
    DELETED     blindtestCode    extraitCode
                -------------    -----------
                blindt01         oldleon
                blindt02         oldleon
    

    Trigger PARTICIPER_TR (en espérant à nouveau qu’il n’y a pas d’erreur de copier/coller...) :

    DROP TRIGGER IF EXISTS PARTICIPER_TR ;
    GO
    CREATE TRIGGER PARTICIPER_TR ON PARTICIPER_V INSTEAD OF INSERT, UPDATE
    AS
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @n as INT ;
    
    DECLARE @inserted TABLE
    (
        joueurId     INT
      , blindtestId  INT
    ) ;
    DECLARE @deleted TABLE
    (
        joueurId     INT
      , blindtestId  INT
    ) ;
    ---- pour debug ---- select '' as INSERTED, * from INSERTED  
    ---- pour debug ---- select '' as DELETED, * from DELETED  
    
    ------------------------------------------------------
    -- traduction des clés alternatives (naturelles) en
    -- clés primaires (artificielles)
    ------------------------------------------------------
    
    INSERT INTO @inserted (joueurId, blindtestId)
        SELECT DISTINCT y.blindeurId, z.blindtestId
        FROM   INSERTED AS x
          JOIN BLINDEUR AS y ON x.pseudo = y.pseudo
          JOIN BLINDTEST AS z ON x.blindtestCode = z.blindtestCode
    ;
    ---- pour debug ---- select '' as '@inserted', * from @inserted 
    
    -------------------------------------------------------
    -- comptage des viols de la contrainte d'exclusion
    -------------------------------------------------------
    SET @n = 
        (SELECT COUNT(*) 
         FROM   @inserted AS x
                  JOIN BLINDTEST AS y  
                       ON x.blindtestId = y.blindtestId
                       AND x.joueurId = y.animateurId)
    
    ---- pour debug ---- select @n as '@n' 
    
    ----------------------------------------------------
    -- Si on constate une tentative de viol de la
    -- contrainte d'exclusion, on rouspète 
    -- et on s'en va.
    ----------------------------------------------------
    IF @n > 0
        BEGIN
            SET @Engueulade = 'Un blinder ne peut pas participer au blindtest qu''il anime !'
            SELECT @Engueulade AS Engueulons  --, * FROM INSERTED
            RAISERROR (@Engueulade, 0,1)  -- state = 0 pour les tests
     ----        RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
            RETURN  
        END
    
    ----------------------------------------------------
    -- On n'a pas constaté d'anomalie : 
    -- on met la table PARTICIPER à jour.
    ----------------------------------------------------
    
    ---------------------------------------------------
    -- S’il y a quelque chose dans DELETED
    -- alors c’est qu’un UPDATE est en cours,
    -- on commence par faire le ménage dans COMPOSER.
    ---------------------------------------------------
    IF (select COUNT(*) FROM DELETED) > 0
        BEGIN 
            INSERT INTO @deleted (joueurId, blindtestId)
                SELECT DISTINCT y.blindeurId, z.blindtestId
                FROM   DELETED AS x
                  JOIN BLINDEUR AS y ON x.pseudo = y.pseudo
                  JOIN BLINDTEST AS z ON x.blindtestCode = z.blindtestCode
            ;
    ---- pour debug ----
            select '' as '@deleted' , * from @deleted
    
            DELETE FROM PARTICIPER
                WHERE joueurId IN (SELECT joueurId FROM @deleted)
                  AND blindtestId IN (SELECT blindtestId FROM @deleted) 
    
    ---- pour debug ---- 
            select '' as 'participer après delete', * from PARTICIPER
    
        END
    
    ---------------------------------
    -- On est en INSERT ou UPDATE
    ---------------------------------
    
    INSERT INTO PARTICIPER (joueurId, blindtestId)
        SELECT joueurId, blindtestId 
        FROM @inserted
    ;
    
    GO
    
    Bon courage !

     
    Faites simple, mais pas plus simple ! (A. Einstein)
    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 »)

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

  17. #17
    Membre régulier
    Profil pro
    Inscrit en
    décembre 2003
    Messages
    439
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : décembre 2003
    Messages : 439
    Points : 106
    Points
    106
    Par défaut
    Ce topic devient un peu spaghetti, je ne sais plus par quel bout le prendre...

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


    Citation Envoyé par Mike888 Voir le message
    Ce topic devient un peu spaghetti, je ne sais plus par quel bout le prendre...
    D’accord. On va mettre les choses dans le bon ordre. Comme c’est assez copieux, je vais couper le message en plusieurs.


    (1) Acquisition du bouquin de Frédéric Brouard (cf. post #8).

    (2) Création d’une base de données, appelons-la Mike :

    DROP DATABASE IF EXISTS Mike ;
    GO
    CREATE DATABASE Mike
      ON PRIMARY
       (NAME = 'Mike'
      , FILENAME = 'D:\Developpez\Mike888\Mike.mdf'
      , SIZE = 4MB , MAXSIZE =16MB, FILEGROWTH = 2MB)
      LOG ON 
       (NAME = 'Mike_log'
      , FILENAME = 'D:\Developpez\Mike888\Mike_log.ldf'
      , SIZE = 2MB , MAXSIZE = 12MB , FILEGROWTH = 2MB)
    GO
    

    (3) Création des tables :

    USE Mike ;
    
    DROP TABLE IF EXISTS TROUVER ;
    DROP TABLE IF EXISTS PARTICIPER ;
    DROP TABLE IF EXISTS JOUEUR ;
    DROP TABLE IF EXISTS COMPOSER ;
    DROP TABLE IF EXISTS EXTRAIT ;
    DROP TABLE IF EXISTS BLINDTEST ;
    DROP TABLE IF EXISTS ANIMATEUR ;
    DROP TABLE IF EXISTS BLINDEUR ;
    
    -------------------------------------------------------------------------------------------------
    
    CREATE TABLE BLINDEUR 
    (
            blindeurId       INT IDENTITY       NOT NULL
          , pseudo           VARCHAR(16)        NOT NULL
        , CONSTRAINT BLINDEUR_PK PRIMARY KEY (blindeurId)
        , CONSTRAINT BLINDEUR_AK UNIQUE (pseudo)
    ) ;
    
    CREATE TABLE ANIMATEUR 
    (
            animateurId      INT                NOT NULL 
        , CONSTRAINT ANIMATEUR_PK PRIMARY KEY (animateurId)
        , CONSTRAINT ANIMATEUR_BLINDEUR_FK FOREIGN KEY (animateurId)
              REFERENCES BLINDEUR (blindeurId) ON DELETE CASCADE
                                               ON UPDATE CASCADE
    ) ;
    
    CREATE TABLE BLINDTEST 
    (
            blindtestId      INT  IDENTITY      NOT NULL
          , blindtestCode    VARCHAR(8)         NOT NULL
          , animateurId      INT                NOT NULL 
        , CONSTRAINT BLINDTEST_PK PRIMARY KEY (blindtestId)
        , CONSTRAINT BLINDTEST_AK UNIQUE (blindtestCode)
        , CONSTRAINT BLINDTEST_ANIMATEUR_FK FOREIGN KEY (animateurId)
              REFERENCES ANIMATEUR (animateurId)
    ) ;
    
    CREATE TABLE EXTRAIT 
    (
            extraitId        INT  IDENTITY      NOT NULL 
          , extraitCode      VARCHAR(8)         NOT NULL
          , titre            VARCHAR(48)        NOT NULL
        , CONSTRAINT EXTRAIT_PK PRIMARY KEY (extraitId)
        , CONSTRAINT EXTRAIT_AK UNIQUE (extraitCode)
    ) ;
    
    CREATE TABLE COMPOSER 
    (
            blindtestId      INT                NOT NULL
          , extraitId        INT                NOT NULL
        ,  CONSTRAINT COMPOSER_PK PRIMARY KEY (blindtestId, extraitId)
        , CONSTRAINT COMPOSER_BLINDTEST_FK FOREIGN KEY (blindtestId)
              REFERENCES BLINDTEST (blindtestId) ON DELETE CASCADE
        , CONSTRAINT COMPOSER_EXTRAIT_FK FOREIGN KEY (extraitId)
              REFERENCES EXTRAIT (extraitId) ON DELETE CASCADE
    ) ;
    
    CREATE TABLE JOUEUR 
    (
            joueurId         INT                NOT NULL
       CONSTRAINT JOUEUR_PK PRIMARY KEY (joueurId),
       CONSTRAINT JOUEUR_BLINDEUR_FK FOREIGN KEY (joueurId)
              REFERENCES BLINDEUR (blindeurId) ON DELETE CASCADE
                                               ON UPDATE CASCADE
    ) ;
    
    CREATE TABLE PARTICIPER 
    (
            joueurId         INT                NOT NULL
          , blindtestId      INT                NOT NULL
        , CONSTRAINT PARTICIPER_PK PRIMARY KEY (joueurId, blindtestId)
        , CONSTRAINT PARTICIPER_JOUEUR_FK FOREIGN KEY (joueurId)
              REFERENCES JOUEUR (joueurId)
        , CONSTRAINT PARTICIPER_BLINDTEST_FK FOREIGN KEY (blindtestId)
              REFERENCES BLINDTEST (blindtestId) ON DELETE NO ACTION
    ) ;
     
    CREATE TABLE TROUVER 
    (
            joueurId         INT                NOT NULL
          , blindtestId      INT                NOT NULL
          , extraitId        INT                NOT NULL 
        , CONSTRAINT TROUVER_PK PRIMARY KEY (joueurId, blindtestId, extraitId) 
        , CONSTRAINT TROUVER_PARTICIPER_FK FOREIGN KEY (joueurId, blindtestId)
              REFERENCES PARTICIPER (joueurId,blindtestId) ON DELETE CASCADE
        , CONSTRAINT TROUVER_COMPOSER_FK FOREIGN KEY (blindtestId, extraitId)
              REFERENCES COMPOSER (blindtestId, extraitId) ON DELETE NO ACTION
    ) ;
    
    Faites simple, mais pas plus simple ! (A. Einstein)
    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 »)

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

  19. #19
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    6 824
    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 : 6 824
    Points : 25 276
    Points
    25 276
    Billets dans le blog
    16
    Par défaut
    (4) Création des vues :

    USE Mike ;
    
    -----------------------------------------------------
    -- vue associée à la table PARTICIPER
    -----------------------------------------------------
    
    DROP VIEW IF EXISTS PARTICIPER_V ;
    
    GO
    
    CREATE VIEW PARTICIPER_V (pseudo, blindtestCode)
    AS
        SELECT  pseudo, blindtestCode
        FROM    PARTICIPER as x 
           JOIN BLINDEUR as y ON x.joueurId = y.blindeurId  -- pour avoir le pseudo
           JOIN BLINDTEST as z ON x.blindtestId = z.blindtestId  -- pour avoir le blindtestCode
    ;
    GO
    
    -----------------------------------------------------
    -- vue associée à la table COMPOSER
    -----------------------------------------------------
    
    DROP VIEW IF EXISTS COMPOSER_V ;
    
    GO
    
    CREATE VIEW COMPOSER_V (blindtestCode, extraitCode)
    AS
        SELECT  blindtestCode, extraitCode
        FROM    COMPOSER as x 
           JOIN BLINDTEST as y ON x.blindtestId = y.blindtestId  -- pour avoir le blindtestCode
           JOIN EXTRAIT as z ON x.extraitId = z.extraitId  -- pour avoir le extraitCode
    ;
    GO
    

     
    Faites simple, mais pas plus simple ! (A. Einstein)
    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 »)

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

  20. #20
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    6 824
    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 : 6 824
    Points : 25 276
    Points
    25 276
    Billets dans le blog
    16
    Par défaut
    (5) Création des triggers sur les vues.

    (51) Trigger sur la vue COMPOSER_V :

    USE Mike ;
    
    DROP TRIGGER IF EXISTS COMPOSER_TR ;
    GO
    
    CREATE TRIGGER COMPOSER_TR ON COMPOSER_V INSTEAD OF INSERT, UPDATE
    AS
    
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @n as INT ;
    
    -----------------------------------------------------------
    -- tables temporaires pour les clés primaires
    -----------------------------------------------------------
    
    DECLARE @inserted TABLE
    (
        blindtestId  INT
      , extraitId    INT
    ) ;
    DECLARE @deleted TABLE
    (
        blindtestId  INT
      , extraitId    INT
    ) ;
    ---- pour debug ---- select '' as INSERTED, * from INSERTED  
    ---- pour debug ---- select '' as DELETED, * from DELETED  
    
    ------------------------------------------------------
    -- traduction des clés alternatives (naturelles) en
    -- clés primaires (artificielles)
    ------------------------------------------------------
    INSERT INTO @inserted (blindtestId, extraitId)
        SELECT DISTINCT y.blindtestId, z.extraitId
        FROM   INSERTED AS x
          JOIN BLINDTEST AS y ON x.blindtestCode = y.blindtestCode
          JOIN EXTRAIT AS z ON x.extraitCode = z.extraitCode
    ;
    ---- pour debug ---- select '' as '@inserted', * from @inserted 
    
    ---------------------------------------------------
    -- S’il y a quelque chose dans DELETED
    -- alors c’est qu’on effectue un UPDATE,
    -- on commence par faire le ménage dans COMPOSER.
    ---------------------------------------------------
    IF (SELECT COUNT(*) FROM DELETED) > 0
        BEGIN
            INSERT INTO @deleted (blindtestId, extraitId)
                SELECT DISTINCT y.blindtestId, z.extraitId
                FROM   DELETED AS x
                  JOIN BLINDTEST AS y ON x.blindtestCode = y.blindtestCode
                  JOIN EXTRAIT AS z ON x.extraitCode = z.extraitCode
            ;
    ---- pour debug ---- select '' as '@deleted' , * from @deleted
    
            DELETE FROM COMPOSER
                WHERE blindtestId IN (SELECT blindtestId FROM @deleted)
                  AND extraitId IN (SELECT extraitId FROM @deleted)  
           ;
    
    ---- pour debug ---- select '' as 'composer après delete', * from COMPOSER
    
        END
    
    ---------------------------------
    -- On est en INSERT ou UPDATE :
    -- on charge.
    ---------------------------------
    INSERT INTO COMPOSER (blindtestId, extraitId)
        SELECT blindtestId, extraitId 
        FROM @inserted
    ;
    GO
    

     
    Faites simple, mais pas plus simple ! (A. Einstein)
    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 »)

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

Discussions similaires

  1. [AC-2010] Gestion d'évaluation comment faire
    Par moniteur41 dans le forum Modélisation
    Réponses: 18
    Dernier message: 26/12/2012, 19h20
  2. Réponses: 9
    Dernier message: 23/06/2011, 19h34
  3. [Gestion des Exceptions] Comment faire ?
    Par caballero dans le forum Services Web
    Réponses: 3
    Dernier message: 21/02/2008, 10h39
  4. Réponses: 7
    Dernier message: 02/01/2007, 18h22
  5. Réponses: 4
    Dernier message: 05/06/2006, 12h34

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