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

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

Schéma Discussion :

Création d'un diagramme entité-association de formations au sein d'une entreprise. [Entité-Association]


Sujet :

Schéma

  1. #1
    Futur Membre du Club
    Femme Profil pro
    Étudiant
    Inscrit en
    Décembre 2020
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Âge : 30
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Décembre 2020
    Messages : 4
    Points : 6
    Points
    6
    Par défaut Création d'un diagramme entité-association de formations au sein d'une entreprise.
    Bonjour à tous,

    Je suis nouvelle sur ce forum, je suis étudiante en informatique et j'aurais besoin de votre aide. J'ai un devoir où je dois créer un diagramme entité-association, dont voici l'énoncé :

    Vous êtes nouvellement embauché dans le service administratif d’une entreprise. Votre travail consiste à mettre en place une base de données permettant de gérer des formations dans une entreprise. Votre objectif est d’optimiser l’accès et la gestion des formations pour les employés de l’entreprise.

    Dans votre entreprise, tous les employés ont accès à un catalogue de formation listant les formations internes auxquelles ils peuvent assister. Ils peuvent également participer à des formations externes si celles-ci sont acceptées par leur supérieur hiérarchique. En effet, les supérieurs hiérarchiques doivent valider l’intérêt de la formation pour leurs employés et vérifier qu’une formation similaire n’existe pas déjà dans le catalogue de formation interne. Toutes les formations suivies par un employé sont référencées dans la base de données et accessibles directement par leur supérieur direct. Un manager a donc accès à toutes les formations de ses employés. Cela fonctionne à un seul niveau : le responsable N+2 d’un employé n’a pas accès à ses formations, seulement à celles des employés du niveau hiérarchique N+1. Dans la base de données, les employés sont identifiés par un numéro unique qui leur est attribué lors de leur embauche. Ce numéro leur permet de se connecter à la base de formations et d’en ajouter de nouvelles si leur manager leur permet. Un employé ne peut suivre que 5 formations par an (internes ou externes) et a l’obligation de suivre au moins une formation interne chaque année.
    Construire un diagramme entité-association permettant de modéliser cette base de données.

    Nom : image_2020-12-13_192301.png
Affichages : 704
Taille : 39,9 Ko

    Pour le moment, j'ai fait ce schéma, mais je suis un peu bloquée. Je pense qu'il manque des choses comme le fait que le supérieur ait accès aux formations suivies, mais je ne sais pas comment l'intégrer. Si vous pouviez m'indiquer quelles sont les incohérences dans mon schéma et ce qu'il faudrait rajouter et/ou enlever pour qu'il ait du sens, ce serait top !!!

    Merci d'avance pour votre aide !!!

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

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

    Informations forums :
    Inscription : Juin 2019
    Messages : 650
    Points : 2 657
    Points
    2 657
    Par défaut
    Bonsoir,

    J'ai un petit souci de compréhension par apport au formalisme que vous utilisez, sorte de mélange entre schéma entité-association et diagramme de classes UML (d'ailleurs, vous appelez ça diagramme entité-association !).
    Quelle méthode avez-vous apprise ? Quel outil de modélisation utilisez-vous ?

    Dans tous les cas, la première étape de l'analyse consistera à énoncer les règles de gestion de votre système d'information.
    Patrick Bergougnoux - Professeur des Universités au Département Informatique de l'IUT de Toulouse III
    La simplicité est la sophistication suprême (Léonard de Vinci)
    LIVRE : Modélisation Conceptuelle de Données - Une Démarche Pragmatique
    Looping - Logiciel de modélisation gratuit et libre d'utilisation

  3. #3
    Futur Membre du Club
    Femme Profil pro
    Étudiant
    Inscrit en
    Décembre 2020
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Âge : 30
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Décembre 2020
    Messages : 4
    Points : 6
    Points
    6
    Par défaut
    Bonsoir,

    J'ai appris à faire ce que je pense être des schémas entité-association du coup. On a des entités (Employes, Formation Interne, Formation externe), des associations reliées par les flèches(Consulte, suit, ajoute etc...) ainsi que des cardinalités (par exemple : un employé peut suivre de 1 à 5 formations internes, donc j'ai écrit 1..5). Ensuite, les clés primaires de chaque entités sont soulignées. Par contre, je ne pense pas avoir appris à faire des diagrammes de classe ULM. J'espère que j'ai bien tout expliciter !

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

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

    Informations forums :
    Inscription : Juin 2019
    Messages : 650
    Points : 2 657
    Points
    2 657
    Par défaut
    Bonsoir,

    Vous ne respectez pas le formalisme des schémas entité-association :

    • Contrairement à UML, les cardinalités doivent être inversées.
    • Les associations doivent être représentées par des ovales.
    • Votre association "Validation" qui en regroupe deux, ne peut pas non plus se modéliser de cette manière.

    Avant de discuter sur le fond, il faudrait que la forme soit correcte.
    Pour cela, vous pouvez vous inspirer des nombreux modèles publiés sur ce forum (et éventuellement revoir vos cours, car je doute que votre professeur vous ait enseigné le formalisme de cette manière).
    Quelle formation suivez-vous ?
    Patrick Bergougnoux - Professeur des Universités au Département Informatique de l'IUT de Toulouse III
    La simplicité est la sophistication suprême (Léonard de Vinci)
    LIVRE : Modélisation Conceptuelle de Données - Une Démarche Pragmatique
    Looping - Logiciel de modélisation gratuit et libre d'utilisation

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

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : Mars 2010
    Messages : 10 088
    Points : 38 393
    Points
    38 393
    Billets dans le blog
    9
    Par défaut
    Bonjour,

    Outre le chapitre consacré au formalisme du modèle E/A, je vous propose de relire aussi le chapitre de votre cours consacré à l'héritage, il sera utile pour modéliser les formations

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

    Pizza, vous devriez commencer par la construction d’un vrai MCD, et pour cela le mieux est d’utiliser Looping, gracieusement proposé par Paprick. Attention, conformément à la modélisation conceptuelle selon Merise, les cardinalités sont inversées par rapport à celles de l’approche E/A de Chen).

    Comme dit Escartefigue, revoyez ce qu’on vous a enseigné en ce qui concerne l’héritage, en l’occurrence la généralisation/spécialisation des entités-types représentant les formations internes et externes.
    A ce sujet il existe fondamentalement l’ouvrage remarquable de D. Nanci et B. Espinasse Ingénierie des systèmes d'information : Merise deuxième génération (4e édition, 2001).
    c’est l’ouvrage de référence. Voyez pages 106 et suivantes le paragraphe « Types et sous-types d’entités : spécialisation/généralisation », au chapitre 7 (« Modélisation conceptuelle des données »).


    Citation Envoyé par pizza-le-hutt Voir le message
    Vérifier qu’une formation similaire n’existe pas déjà dans le catalogue de formation interne
    Voir si la mise en oeuvre d’une contrainte d’exclusion entre les eux types de formation répond à cette règle. Référez-vous à l’ouvrage cité.


    Hiérarchie des employés

    Vous avez utilisé une association réflexive pour représenter la hiérarchie des employés : d’accord. Toutefois les cardinalités posent un problème. En effet du fait de la cardinalité 1,N, tout employé est le supérieur d’un autre employé, or certains employés ne sont certainement pas des chefs. De même du fait de la cardinalité 1,1, tout employé a un supérieur, donc le grand chef aurait un chef...


    Citation Envoyé par pizza-le-hutt Voir le message
    Ils peuvent également participer à des formations externes si celles-ci sont acceptées par leur supérieur hiérarchique.
    Selon votre diagramme, n’importe quel employé peut valider ce genre de formation pour n’importe quel autre employé.

    Une possibilité pour être sûr de respecter la règle :

    (a) demander à Looping de transformer l’association de hiérarchie (appelons celle-ci par exemple EMP_HIERARCHIE) en entité-type. Dans ces conditions celle-ci est identifiée relativement à l’entité-type EMPLOYE (1,1(R)), voyez à ce propos à la page 130 de l’ouvrage cité (paragraphe « identifiant relatif »).

    (b) mettre en oeuvre une association (appelons-la par exemple EMP_FOR_EXT) connectant les entités-types EMP_HIERARCHIE et FORMATION_EXT. L’association EMP_FOR_EXT est à doter d’un attribut de type booléen permettant de savoir si le chef a donné son aval (appelons cet attribut validationParLeChef).

    Dans ces conditions, un employé ne peut effectivement suivre une formation externe que si son chef direct a donné son aval, et personne d’autre.


    Nom : pizza-le-hutt(hierarchie, formations externes).png
Affichages : 580
Taille : 13,6 Ko

    N.B.

    Votre association CONSULTE : ne conserver que si l’on veut savoir qui a consulté.


    L’attribut hierarchie_employe de votre entité-type EMPLOYE est redondant avec l’association réflexive. Cet attribut devrait disparaître.


    Citation Envoyé par pizza-le-hutt Voir le message
    Un employé ne peut suivre que 5 formations par an
    Selon votre diagramme, un employé ne peut en fait suivre que 5 formations toutes années confondues. Si c’est 5 par année, alors le concept d’année devrait être modélisé, ou bien vous partez du principe qu’à la fin de l’année on remet à zéro les formations suivies... Quelle est votre position ?
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

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

  7. #7
    Futur Membre du Club
    Femme Profil pro
    Étudiant
    Inscrit en
    Décembre 2020
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Âge : 30
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Décembre 2020
    Messages : 4
    Points : 6
    Points
    6
    Par défaut
    Citation Envoyé par Paprick Voir le message
    Bonsoir,

    Vous ne respectez pas le formalisme des schémas entité-association :

    • Contrairement à UML, les cardinalités doivent être inversées.
    • Les associations doivent être représentées par des ovales.
    • Votre association "Validation" qui en regroupe deux, ne peut pas non plus se modéliser de cette manière.

    Avant de discuter sur le fond, il faudrait que la forme soit correcte.
    Pour cela, vous pouvez vous inspirer des nombreux modèles publiés sur ce forum (et éventuellement revoir vos cours, car je doute que votre professeur vous ait enseigné le formalisme de cette manière).
    Quelle formation suivez-vous ?
    Bonsoir,

    Je suis en Master 1 Traitement automatique des langues à l'INALCO. Je vous envoie une capture d'écran de mon cours avec un exemple de "diagramme entité association" avec le schéma relationnel. Je suis un peu perdue car je suis novice en base de données, et ma professeure n'a jamais mentionné l'UML ou l'héritage... Je vais tout lire en détail, et je vous renvoie un nouveau schéma.

    Nom : image_2020-12-14_225117.png
Affichages : 659
Taille : 54,6 Ko

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

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

    Informations forums :
    Inscription : Juin 2019
    Messages : 650
    Points : 2 657
    Points
    2 657
    Par défaut
    Bonsoir,
    Je suis très inquiet de voir le contenu de votre cours...
    Ce diagramme ressemble à peu près à un diagramme de classes UML... sauf que :
    • le schéma relationnel associé est faux : les clés étrangères sont définies n'importe comment...
    • l'association "Correspond" a des cardinalités plutôt douteuses...
    • Le nom de l'hôtel est un très mauvais identifiant (les VARCHAR sont toujours à proscrire).
    • le losange censé représenté un identifiant relatif n'est pas du tout pris en compte dans le schéma relationnel...

    Bref, je n'aime pas critiqué les collègues, mais là, il y a un gros problème sur ce cours.
    Patrick Bergougnoux - Professeur des Universités au Département Informatique de l'IUT de Toulouse III
    La simplicité est la sophistication suprême (Léonard de Vinci)
    LIVRE : Modélisation Conceptuelle de Données - Une Démarche Pragmatique
    Looping - Logiciel de modélisation gratuit et libre d'utilisation

  9. #9
    Futur Membre du Club
    Femme Profil pro
    Étudiant
    Inscrit en
    Décembre 2020
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Âge : 30
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Décembre 2020
    Messages : 4
    Points : 6
    Points
    6
    Par défaut
    Bonsoir,

    D'abord, j'aime beaucoup votre logiciel Paprik, il est simple d'utilisation et très pratique ! Voilà la capture d'écran de ce que j'ai fait.

    Nom : image_2020-12-14_233928.png
Affichages : 689
Taille : 22,5 Ko

    J'ai lu le chapitre sur l'héritage et les sous-types, j'en ai déduis que je devais créer une entité formation, et la "scinder" en deux, en externes et internes. J'ai rajouté une contrainte d'exclusion au cas où une formation interne existe déjà.

    Pour ce qui est de la hiérarchie des employés, j'ai repris le schéma que vous avez proposé. J'ai compris les problèmes de cardinalités de mon schéma. En créant une nouvelle entité "Hiérarchie_Emp", est-il possible d'avoir la même clé que celle de l'entité Employés, étant donné qu'un supérieur est aussi un employé ?

    Pour finir, j'ai retiré l'association "consulte" comme vous me l'avez conseillé. Je ne pense pas qu'il soit important de savoir qui a consulté le catalogue des formations. J'ai également retiré l'attribut hiérarchie_employé

    Pour ce qui est des 5 formations, je pars du principe que les formations suivies sont remises à zéro à la fin de l'année, car ce n'est pas précisé dans l'énoncé.

    Merci encore pour votre aide à tous !

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


    D’accord, Pizza, pour la spécialisation de FORMATION, mais remontez le nom des formations dans cette entité-type, c’est justement un des avantages de la spécialisation que de permettre la factorisation des propriétés.


    Citation Envoyé par pizza-le-hutt Voir le message
    En créant une nouvelle entité "Hiérarchie_Emp", est-il possible d'avoir la même clé que celle de l'entité Employés, étant donné qu'un supérieur est aussi un employé ?
    Votre entité-type HIERARCHIE_EMP a un attribut identifiant, idEmploye. Par contre vous aurez observé que cet attribut est absent chez moi. Cela tient au fait que l’identification relative (1,1(R)) fait hériter HIERARCHIE_EMP de l’identifiant de l’entité-type EMPLOYE à savoir justement idEmploye. Votre attribut idEmploye constitue donc en réalité une redondance pour l’entité-type HIERARCHIE_EMP et doit passer au rasoir d’Ockham. Supprimez cet attribut dans HIERARCHIE_EMP et regardez le code SQL, CREATE TABLE HIERARCHIE_EMP, vous constaterez que Looping a bien généré l’attribut idEmploye nécessaire et suffisant, de surcroît clé primaire de la table..

    Ma réponse à votre question : il suffit d’identifier HIERARCHIE_EMP relativement à EMPLOYE pour qu’au stade SQL le singleton {idEmploye} soit clé primaire de la table HIERARCHIE_EMP.

    En passant, attention au vocabulaire, une entité-type (ou classe d’entités) n’a pas de clé, mais un identifiant. « Clé » est un terme du modèle relationnel de données (voir Codd, A Relational Model of Data for Large Shared Data Banks), repris par SQL.


    Citation Envoyé par pizza-le-hutt Voir le message
    Pour ce qui est des 5 formations, je pars du principe que les formations suivies sont remises à zéro à la fin de l'année, car ce n'est pas précisé dans l'énoncé.
    Alors précisez dans votre copie que vous faites l’impasse...


    N’oubliez le pouce en l’air quand vous trouvez qu’on vous aide.
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

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

  11. #11
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 965
    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 965
    Points : 30 777
    Points
    30 777
    Billets dans le blog
    16
    Par défaut Précision : utilisation de SQL Server
    Bonsoir,

    Il n’est pas inutile de voir comment les choses se passent concrètement dans la base de données, laquelle est la conséquence du MCD quant à sa structure. Il est intéressant par ailleurs de voir comment traduire les contraintes du genre « pas plus de 5 formations par employé ».


    Un jeu d’essai (utilisation de SQL Server)

    Les employés

    CREATE TABLE EMPLOYE
    (
       employeId INT,
       matricule CHAR(4) NOT NULL, 
       employeNom VARCHAR(32) NOT NULL,
       employePrenom VARCHAR(32) NOT NULL,
       CONSTRAINT EMPLOYE_PK PRIMARY KEY(employeId)
    );
    INSERT INTO EMPLOYE (employeId, matricule, employeNom, employePrenom)
    VALUES
        (1, 'lm', 'Le Mexicain', 'Louis')
      , (2, 'fn', 'Naudin', 'Fernand')
      , (3, 'ff', 'Folace', 'Francis')
      , (4, 'rv', 'Volfoni', 'Raoul')
      , (5, 'pv', 'Volfoni', 'Paul')
      , (6, 'jy', 'Yes-Sir', 'Jean')
      , (7, 'hh', 'Henri', 'Henri')
      , (8, 'pm', 'Monpetit', 'Patricia')
      , (9, 'ad', 'Delafoy', 'Antoine')
      , (10, 'pc', 'Cousin', 'Pascal')
      , (11, 'sc', 'Cousin', 'Sébastien')
    ;    
    SELECT  matricule, employeNom, employePrenom FROM EMPLOYE ; 
    =>

    matricule   employeNom    employePrenom
    
    lm          Le Mexicain   Louis
    fn          Naudin        Fernand
    ff          Folace        Francis
    rv          Volfoni       Raoul
    pv          Volfoni       Paul
    jy          Yes-Sir       Jean
    hh          Henri         Henri
    pm          Monpetit      Patricia
    ad          Delafoy       Antoine
    pc          Cousin        Pascal
    sc          Cousin        Sébastien
    

    La hiérarchie des employés

    CREATE TABLE EMP_HIERARCHIE
    (
       employeId INT,
       chefId INT NOT NULL,
       CONSTRAINT EMP_HIERARCHIE_PK PRIMARY KEY(employeId),
       CONSTRAINT EMP_HIERARCHIE_EMPLOYE_FK FOREIGN KEY(employeId) 
           REFERENCES EMPLOYE(employeId),
       CONSTRAINT EMP_HIERARCHIE_EMPLOYE_1_FK FOREIGN KEY(chefId) 
           REFERENCES EMPLOYE(employeId)
    );
    INSERT INTO EMP_HIERARCHIE (employeId, chefId) 
    VALUES
        ((SELECT employeId FROM EMPLOYE WHERE matricule = 'fn') 
       , (SELECT employeId FROM EMPLOYE WHERE matricule = 'lm'))
    ,
        ((SELECT employeId FROM EMPLOYE WHERE matricule = 'ff') 
       , (SELECT employeId FROM EMPLOYE WHERE matricule = 'fn'))
    ,
        ((SELECT employeId FROM EMPLOYE WHERE matricule = 'hh') 
       , (SELECT employeId FROM EMPLOYE WHERE matricule = 'ff'))
    ,
        ((SELECT employeId FROM EMPLOYE WHERE matricule = 'jy') 
       , (SELECT employeId FROM EMPLOYE WHERE matricule = 'ff'))
    ,
        ((SELECT employeId FROM EMPLOYE WHERE matricule = 'pm') 
       , (SELECT employeId FROM EMPLOYE WHERE matricule = 'fn'))
    ,
        ((SELECT employeId FROM EMPLOYE WHERE matricule = 'rv') 
       , (SELECT employeId FROM EMPLOYE WHERE matricule = 'lm'))
    ,
        ((SELECT employeId FROM EMPLOYE WHERE matricule = 'pv') 
       , (SELECT employeId FROM EMPLOYE WHERE matricule = 'lm'))
    ,
        ((SELECT employeId FROM EMPLOYE WHERE matricule = 'pc') 
       , (SELECT employeId FROM EMPLOYE WHERE matricule = 'rv'))
    ,
        ((SELECT employeId FROM EMPLOYE WHERE matricule = 'sc') 
       , (SELECT employeId FROM EMPLOYE WHERE matricule = 'rv'))
    ;
    SELECT CONCAT(y.employePrenom, ' ' , y.employeNom) as employe 
         , CONCAT(z.employePrenom, ' ' , z.employeNom) as chef 
    FROM   EMP_HIERARCHIE as x 
      JOIN EMPLOYE as y ON x.employeId = y.employeId
      JOIN EMPLOYE as z ON x.chefId = z.employeId
    ; 
    =>

    employe                chef
    
    Fernand Naudin         Louis Le Mexicain
    Francis Folace         Fernand Naudin
    Raoul Volfoni          Louis Le Mexicain
    Paul Volfoni           Louis Le Mexicain
    Jean Yes-Sir           Francis Folace
    Henri Henri            Francis Folace
    Patricia Monpetit      Fernand Naudin
    Pascal Cousin          Raoul Volfoni
    Sébastien Cousin       Raoul Volfoni
    

    Les formations

    CREATE TABLE FORMATION
    (
       formationId INT,
       formationCode CHAR(8) NOT NULL,
       formationNom VARCHAR(48) NOT NULL,
       CONSTRAINT FORMATION_PK PRIMARY KEY(formationId)
    );
    
    INSERT INTO FORMATION (formationId, formationCode, formationNom)
    VALUES
            (1, 'judo', 'judo')
          , (2, 'karate', 'karaté')
          , (3, 'jazzman', 'jazz manouche')
          , (4, 'dirorch', 'direction d''orchestre')
          , (5, 'philo', 'philosophie')
          , (6, 'compta', 'comptabilité')
          , (7, 'peint', 'peinture')
          , (8, 'sculpt', 'sculpture')
          , (11, 'droit', 'droit')
          , (12, 'moutons', 'élevage des moutons')
          , (13, 'pligne', 'pêche à la ligne')
          , (14, 'cheval', 'équitation')
          , (15, 'presti', 'prestidigitation')
          , (16, 'nage', 'natation')
          , (17, 'alchi', 'alchimie')
          , (18, 'cordes', 'théorie des cordes')
          , (19, 'trapvol', 'trapèze volant')
          , (20, 'agro', 'agronomie')
          , (21, 'astro', 'astronomie')
          , (22, 'geo', 'géographie')
          , (23, 'chimie', 'chimie')
          , (24, 'api', 'apiculture')
          , (25, 'huitres', 'conchyliculture')
          , (26, 'plongee', 'plongée')
    ;    
    SELECT formationCode, formationNom FROM FORMATION ;
    
    =>

    formationCode   formationNom
    
    judo            judo
    karate          karaté
    jazzman         jazz manouche
    dirorch         direction d'orchestre
    philo           philosophie
    compta          comptabilité
    peint           peinture
    sculpt          sculpture
    droit           droit
    moutons         élevage des moutons
    pligne          pêche à la ligne
    cheval          équitation
    presti          prestidigitation
    nage            natation
    alchi           alchimie
    cordes          théorie des cordes
    trapvol         trapèze volant
    agro            agronomie
    astro           astronomie
    geo             géographie
    chimie          chimie
    api             apiculture
    huitres         conchyliculture
    plongee         plongée
    

    Les formations internes

    CREATE TABLE FORMATION_INT
    (
       formationId INT,
       CONSTRAINT FORMATION_INT_PK PRIMARY KEY(formationId),
       CONSTRAINT FORMATION_INT_FORMATION_FK FOREIGN KEY(formationId) 
           REFERENCES FORMATION(formationId)
    );
    
    INSERT INTO FORMATION_INT (formationId)
    VALUES
        ((SELECT formationId FROM FORMATION WHERE formationCode = 'judo'))
      , ((SELECT formationId FROM FORMATION WHERE formationCode = 'karate'))
      , ((SELECT formationId FROM FORMATION WHERE formationCode = 'philo'))
      , ((SELECT formationId FROM FORMATION WHERE formationCode = 'compta'))
      , ((SELECT formationId FROM FORMATION WHERE formationCode = 'geo')) 
      , ((SELECT formationId FROM FORMATION WHERE formationCode = 'droit'))
      , ((SELECT formationId FROM FORMATION WHERE formationCode = 'peint'))
      , ((SELECT formationId FROM FORMATION WHERE formationCode = 'astro')) 
      , ((SELECT formationId FROM FORMATION WHERE formationCode = 'chimie')) 
    ;
    
    SELECT formationCode, formationNom as formationInterne
    FROM   FORMATION_INT as x
      JOIN FORMATION as y ON x.formationId = y.formationId
    ;
    
    =>

    formationCode      formationInterne
    judo               judo
    karate             karaté
    philo              philosophie
    compta             comptabilité
    peint              peinture
    droit              droit
    astro              astronomie
    geo                géographie
    chimie             chimie
    

    Les formations externes

    CREATE TABLE FORMATION_EXT
    (
       formationId INT,
       CONSTRAINT FORMATION_EXT_PK PRIMARY KEY(formationId),
       CONSTRAINT FORMATION_EXT_FORMATION_FK FOREIGN KEY(formationId) 
           REFERENCES FORMATION(formationId),
    ); 
    Une formation externe ne peut être créée que s’il n’existe pas de formation interne équivalente. A noter que l’énoncé proposé dans le post #1 est en l’occurrence une aporie, car que faire quand on veut créer une formation interne déjà existante en tant que formation externe et que des employés y sont inscrits (et l’ont évidemment suivie) ?

    Au moins pour respecter la contrainte formulée dans l’énoncé :

    GO  
    CREATE FUNCTION FORMATION_EXT_EXCLUE_FN()      
    RETURNS INT  
    AS   
    BEGIN  
       DECLARE @n INT  
       SELECT @n = COUNT(*) 
       FROM FORMATION_INT as x
         JOIN FORMATION_EXT as y ON x.formationId = y.formationId  
       RETURN @n  
    END ;  
    
    ALTER TABLE FORMATION_EXT  
    ADD CONSTRAINT FORMATION_EXT_EXCLUE CHECK (dbo.FORMATION_EXT_EXCLUE_FN() = 0) ;  
    GO  
    
    Les formations externes (suite)

    INSERT INTO FORMATION_EXT (formationId)
    VALUES
        ((SELECT formationId FROM FORMATION WHERE formationCode = 'jazzman'))
      , ((SELECT formationId FROM FORMATION WHERE formationCode = 'dirorch'))
      , ((SELECT formationId FROM FORMATION WHERE formationCode = 'pligne'))
      , ((SELECT formationId FROM FORMATION WHERE formationCode = 'moutons'))
      , ((SELECT formationId FROM FORMATION WHERE formationCode = 'agro'))
      , ((SELECT formationId FROM FORMATION WHERE formationCode = 'sculpt'))
      , ((SELECT formationId FROM FORMATION WHERE formationCode = 'api'))
      , ((SELECT formationId FROM FORMATION WHERE formationCode = 'huitres'))
      , ((SELECT formationId FROM FORMATION WHERE formationCode = 'nage'))
      , ((SELECT formationId FROM FORMATION WHERE formationCode = 'cheval'))
      , ((SELECT formationId FROM FORMATION WHERE formationCode = 'plongee'))
      , ((SELECT formationId FROM FORMATION WHERE formationCode = 'presti'))
    
    SELECT formationCode, formationNom as formationExterne
    FROM   FORMATION_EXT as x
      JOIN FORMATION as y ON x.formationId = y.formationId
    ; 
    =>

    formationCode      formationExterne
    
    jazzman            jazz manouche
    dirorch            direction d'orchestre
    sculpt             sculpture
    moutons            élevage des moutons
    pligne             pêche à la ligne
    cheval             équitation
    presti             prestidigitation
    nage               natation
    agro               agronomie
    api                apiculture
    huitres            conchyliculture
    plongee            plongée
    

    Formations suivies par les employés (structure des tables)

    CREATE TABLE EMP_FORM_INT
    (
       employeId INT,
       formationId INT,
       CONSTRAINT EMP_FORM_INT_PK PRIMARY KEY(employeId, formationId),
       CONSTRAINT EMP_FORM_INT_EMPLOYE_FK FOREIGN KEY(employeId) 
           REFERENCES EMPLOYE(employeId),
       CONSTRAINT EMP_FORM_INT_FORMATION_INT_FK FOREIGN KEY(formationId) 
           REFERENCES FORMATION_INT(formationId)
    );
    CREATE TABLE EMP_FORM_EXT
    (
       employeId INT,
       formationId INT,
       validationParLeChef BIT NOT NULL DEFAULT 0,
       CONSTRAINT EMP_FORM_EXT_PK PRIMARY KEY(employeId, formationId),
       CONSTRAINT EMP_FORM_EXT_EMP_HIERARCHIE_FK FOREIGN KEY(employeId) 
           REFERENCES EMP_HIERARCHIE(employeId),
       CONSTRAINT EMP_FORM_EXT_FORMATION_EXT_FK FOREIGN KEY(formationId) 
           REFERENCES FORMATION_EXT(formationId)
    );

    En prévision des opérations ultérieures : vue globale des formations suivies (internes et externes)

     
    GO
    CREATE VIEW FORM_VUE_GLOBALE 
        (matricule, employe, typeFormation, formation, accordDuChef)
    AS
    SELECT matricule
         , CONCAT(y.employePrenom, ' ' , y.employeNom) as employe 
         , 'interne' as 'type de formation'     
         , formationNom as formationInterne
         , 'sans objet' as 'accord du chef'
    FROM   EMP_FORM_INT as x
      JOIN EMPLOYE as y ON x.employeId = y.employeId 
      JOIN FORMATION as z ON x.formationId = z.formationId
      
    UNION
    
    SELECT matricule     
         , CONCAT(y.employePrenom, ' ' , y.employeNom) as employe 
         , 'externe'     
         , formationNom as formationExterne
         , "accord du chef" =
           CASE 
               WHEN validationParLeChef = 1
                   THEN 'oui'
               ELSE 'non'
           END           
    FROM   EMP_FORM_EXT as x
      JOIN EMPLOYE as y ON x.employeId = y.employeId 
      JOIN FORMATION as z ON x.formationId = z.formationId
    ;
    GO 

    Surveillance du nombre maximum de formations qu’un employé peut suivre.

    Dans ce qui suit on met en oeuvre des fonctions à cet effet. Si les tables étaient volumineuses, il serait préférable d’en passer par des triggers car alors le SGBD transmet seulement les lignes en cours de création ou de modification. Cette remarque vaut pour la fonction FORMATION_EXT_EXCLUE_FN.

    Un employé peut suivre au maximum 5 formations. Pour que le SGBD assure, on lui fournit les moyens ad-hoc.

    GO  
    CREATE FUNCTION EMP_FORM_COUNT_GLOBAL_FN()      
    RETURNS INT  
    AS   
    BEGIN  
       DECLARE @n INT
    
       SELECT @n = MAX(kount)
       FROM   
            (
              SELECT COUNT(*) as kount  
              FROM   FORM_VUE_GLOBALE
              WHERE  accordDuChef IN ('oui', 'sans objet')
              GROUP BY matricule
              HAVING COUNT(*) > 5
            ) as maxi
    
       RETURN @n  
    END ;  
    GO 
    
    ALTER TABLE EMP_FORM_INT  
    ADD CONSTRAINT EMP_FORM_INT_MAX_GLOBAL CHECK (dbo.EMP_FORM_COUNT_GLOBAL_FN() = 0) ;  
    
    ALTER TABLE EMP_FORM_EXT  
    ADD CONSTRAINT EMP_FORM_EXT_MAX_GLOBAL CHECK (dbo.EMP_FORM_COUNT_GLOBAL_FN() = 0) ;  
    
    Un employé peut suivre au maximum 5 formations internes. Pour que le SGBD assure, on lui fournit les moyens ad-hoc.

    CREATE FUNCTION EMP_FORM_INT_COUNT_FN()      
    RETURNS INT  
    AS   
    BEGIN  
       DECLARE @n INT
      
       SELECT @n = COUNT(*) 
       FROM EMP_FORM_INT
       GROUP BY employeId
       HAVING COUNT(*) > 5
      
       RETURN @n  
    
    END ;  
    GO  
    
    ALTER TABLE EMP_FORM_INT  
    ADD CONSTRAINT EMP_FORM_INT_MAX CHECK (dbo.EMP_FORM_INT_COUNT_FN() = 0) ;  
    
    Quelques formations internes concernant Francis Folace

    INSERT INTO EMP_FORM_INT (employeId, formationId) 
    VALUES
        ((SELECT employeId FROM EMPLOYE WHERE matricule = 'ff')
       , (SELECT x.formationId 
          FROM   FORMATION_INT as x
            JOIN FORMATION AS y ON x.formationId = y.formationId
          WHERE formationCode = 'compta'))
    , 
        ((SELECT employeId FROM EMPLOYE WHERE matricule = 'ff')
       , (SELECT x.formationId 
          FROM   FORMATION_INT as x
            JOIN FORMATION AS y ON x.formationId = y.formationId
          WHERE formationCode = 'philo'))
    , 
        ((SELECT employeId FROM EMPLOYE WHERE matricule = 'ff')
       , (SELECT x.formationId 
          FROM   FORMATION_INT as x
            JOIN FORMATION AS y ON x.formationId = y.formationId
          WHERE formationCode = 'droit'))
    , 
        ((SELECT employeId FROM EMPLOYE WHERE matricule = 'ff')
       , (SELECT x.formationId 
          FROM   FORMATION_INT as x
            JOIN FORMATION AS y ON x.formationId = y.formationId
          WHERE formationCode = 'geo'))
    

    Quelques formations internes concernant Jean Yes-Sir

    INSERT INTO EMP_FORM_INT (employeId, formationId) 
    VALUES
        ((SELECT employeId FROM EMPLOYE WHERE matricule = 'jy')
       , (SELECT x.formationId 
          FROM   FORMATION_INT as x
            JOIN FORMATION AS y ON x.formationId = y.formationId
          WHERE formationCode = 'compta'))
    , 
        ((SELECT employeId FROM EMPLOYE WHERE matricule = 'jy')
       , (SELECT x.formationId 
          FROM   FORMATION_INT as x
            JOIN FORMATION AS y ON x.formationId = y.formationId
          WHERE formationCode = 'judo'))
    , 
        ((SELECT employeId FROM EMPLOYE WHERE matricule = 'jy')
       , (SELECT x.formationId 
          FROM   FORMATION_INT as x
            JOIN FORMATION AS y ON x.formationId = y.formationId
          WHERE formationCode = 'peint'))
    , 
        ((SELECT employeId FROM EMPLOYE WHERE matricule = 'jy')
       , (SELECT x.formationId 
          FROM   FORMATION_INT as x
            JOIN FORMATION AS y ON x.formationId = y.formationId
          WHERE formationCode = 'geo'))
    , 
        ((SELECT employeId FROM EMPLOYE WHERE matricule = 'jy')
       , (SELECT x.formationId 
          FROM   FORMATION_INT as x
            JOIN FORMATION AS y ON x.formationId = y.formationId
          WHERE formationCode = 'astro'))
    
    =>

    matricule employe            formationInterne
    
    ff        Francis Folace     philosophie
    ff        Francis Folace     comptabilité
    ff        Francis Folace     droit
    ff        Francis Folace     géographie
    jy        Jean Yes-Sir       judo
    jy        Jean Yes-Sir       comptabilité
    jy        Jean Yes-Sir       peinture
    jy        Jean Yes-Sir       astronomie
    jy        Jean Yes-Sir       géographie
    

    Un employé peut suivre au maximum 4 formations externes, seules sont prises en compte les demandes validées par son chef. Pour que le SGBD assure, on lui fournit les moyens ad-hoc.

    CREATE FUNCTION EMP_FORM_EXT_COUNT_FN()      
    RETURNS INT  
    AS   
    BEGIN  
       DECLARE @n INT 
     
       SELECT @n = COUNT(*) 
       FROM   EMP_FORM_EXT
       WHERE  validationParLeChef = 1
       GROUP BY employeId
       HAVING COUNT(*) > 4 
    
       RETURN @n  
    END ;  
    GO  
    
    ALTER TABLE EMP_FORM_EXT  
    ADD CONSTRAINT EMP_FORM_EXT_MAX CHECK (dbo.EMP_FORM_EXT_COUNT_FN() = 0) ;  
    

    Quelques formations externes concernant Francis Folace (les formations non validées par son chef comptent pour du beurre.

    INSERT INTO EMP_FORM_EXT(employeId, formationId, validationParLeChef) 
    VALUES
        ((SELECT employeId FROM EMPLOYE WHERE matricule = 'ff')
       , (SELECT x.formationId 
          FROM   FORMATION_EXT as x
            JOIN FORMATION AS y ON x.formationId = y.formationId
          WHERE formationCode = 'jazzman')
       , 1)
    ,
        ((SELECT employeId FROM EMPLOYE WHERE matricule = 'ff')
       , (SELECT x.formationId 
          FROM   FORMATION_EXT as x
            JOIN FORMATION AS y ON x.formationId = y.formationId
          WHERE formationCode = 'pligne')
       , 0)
    ,
        ((SELECT employeId FROM EMPLOYE WHERE matricule = 'ff')
       , (SELECT x.formationId 
          FROM   FORMATION_EXT as x
            JOIN FORMATION AS y ON x.formationId = y.formationId
          WHERE formationCode = 'plongee')
       , 0)
    ;

    Quelques formations externes concernant Jean Yes-Sir

    INSERT INTO EMP_FORM_EXT(employeId, formationId, validationParLeChef) 
    VALUES
        ((SELECT employeId FROM EMPLOYE WHERE matricule = 'jy')
       , (SELECT x.formationId 
          FROM   FORMATION_EXT as x
            JOIN FORMATION AS y ON x.formationId = y.formationId
          WHERE formationCode = 'plongee')
       , 0)
    ,
        ((SELECT employeId FROM EMPLOYE WHERE matricule = 'jy')
       , (SELECT x.formationId 
          FROM   FORMATION_EXT as x
            JOIN FORMATION AS y ON x.formationId = y.formationId
          WHERE formationCode = 'presti')
       , 0)
    ;

    Voyons voir la situation des formations externes des employés.

    SELECT matricule     
         , CONCAT(y.employePrenom, ' ' , y.employeNom) as employe 
         , formationNom as formationExterne
         , accordDuChef =
           CASE 
               WHEN validationParLeChef = 1
                   THEN 'oui'
               ELSE 'non'
           END           
    FROM   EMP_FORM_EXT as x
      JOIN EMPLOYE as y ON x.employeId = y.employeId 
      JOIN FORMATION as z ON x.formationId = z.formationId
    ; 
    =>

     
    matricule      employe              formationExterne     accord du chef
    
    ff             Francis Folace       jazz manouche        oui
    ff             Francis Folace       pêche à la ligne     non
    ff             Francis Folace       natation             non
    ff             Francis Folace       plongée              non
    jy             Jean Yes-Sir         prestidigitation     non
    jy             Jean Yes-Sir         plongée              non
    

    Vue globale (situation de l’ensemble des formations des employés)

    SELECT employe, typeFormation, formation, accordDuChef
    FROM   FORM_VUE_GLOBALE
    ORDER BY matricule, typeFormation DESC, accordDuChef DESC, formation 
    ;
    =>

    employe              typeFormation      formation            accordDuChef
    
    Francis Folace       interne            comptabilité         sans objet
    Francis Folace       interne            droit                sans objet
    Francis Folace       interne            géographie           sans objet
    Francis Folace       interne            philosophie          sans objet
    Francis Folace       externe            jazz manouche        oui
    Francis Folace       externe            natation             non
    Francis Folace       externe            pêche à la ligne     non
    Francis Folace       externe            plongée              non
    Jean Yes-Sir         interne            astronomie           sans objet
    Jean Yes-Sir         interne            comptabilité         sans objet
    Jean Yes-Sir         interne            géographie           sans objet
    Jean Yes-Sir         interne            judo                 sans objet
    Jean Yes-Sir         interne            peinture             sans objet
    Jean Yes-Sir         externe            plongée              non
    Jean Yes-Sir         externe            prestidigitation     non
    

    Tentatives de délit :

    INSERT INTO EMP_FORM_EXT(employeId, formationId, validationParLeChef) 
    VALUES
        ((SELECT employeId FROM EMPLOYE WHERE matricule = 'ff')
       , (SELECT x.formationId 
          FROM   FORMATION_EXT as x
            JOIN FORMATION AS y ON x.formationId = y.formationId
          WHERE formationCode = 'cheval')
       , 1)
    ; 
    =>

    Msg 547, Niveau 16
    L'instruction INSERT est en conflit avec la contrainte CHECK "EMP_FORM_EXT_MAX_GLOBAL". 
    Le conflit s'est produit dans la base de données "temp", table "dbo.EMP_FORM_EXT".  

    INSERT INTO EMP_FORM_EXT(employeId, formationId, validationParLeChef) 
    VALUES
       ((SELECT employeId FROM EMPLOYE WHERE matricule = 'jy')
       , (SELECT x.formationId 
          FROM   FORMATION_EXT as x
            JOIN FORMATION AS y ON x.formationId = y.formationId
          WHERE formationCode = 'pligne')
       , 0)
    ; 
    =>

    Msg 547, Niveau 16
    L'instruction INSERT est en conflit avec la contrainte CHECK "EMP_FORM_EXT_MAX_GLOBAL". 
    Le conflit s'est produit dans la base de données "temp", table "dbo.EMP_FORM_EXT".  

    UPDATE EMP_FORM_EXT
        SET validationParLeChef = 1
        WHERE employeId = (SELECT employeId FROM EMPLOYE WHERE matricule = 'jy')
          AND formationId = (SELECT formationId FROM FORMATION WHERE formationCode = 'plongee') 
    ; 
    =>

    Msg 547, Niveau 16
    L'instruction UPDATE est en conflit avec la contrainte CHECK "EMP_FORM_EXT_MAX_GLOBAL". 
    Le conflit s'est produit dans la base de données "temp", table "dbo.EMP_FORM_EXT".  
    Désolé, chef !
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

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

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Réponses: 69
    Dernier message: 05/12/2019, 16h55
  2. diagramme entité association
    Par wperle dans le forum UML
    Réponses: 8
    Dernier message: 21/08/2013, 22h22
  3. Outils pour la conception d'un modèle Entités-Association
    Par heddicmi dans le forum Décisions SGBD
    Réponses: 3
    Dernier message: 05/04/2005, 11h04
  4. [MCD] MCD vs schéma entité-association
    Par Lyn2004 dans le forum Schéma
    Réponses: 2
    Dernier message: 10/11/2004, 17h20
  5. Générer automatiquement un schéma entité/association
    Par worldchampion57 dans le forum Outils
    Réponses: 3
    Dernier message: 03/06/2003, 18h11

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