Discussion :

[Martin_75]

Bonjour a tous,

je viens chercher un peu d'aide, car cela fait bien longtemps que j'ai n'ai pas touché aux base de données et je commence a le sentir. :)

Voici la situation a laquelle je suis confrontée :


- Un laboratoire est composé d'employés, qui ne sont affectés qu'a un seul laboratoire.
- Un des employés du labo. dirige celui-ci.
- Un equipement est conçut par un service de conception employant plusieurs ingenieurs.
- L'ingenieur etablit un document "demande de procedure", qui concerne 1 ou plusieurs labo. et un seul équipement. CE DOCUMENT EST LE MEME POUR CHAQUE LABO CONCERNéS.
- Un des employés d'un des labo ( ayant recut un demande de proc. ) rédige une procedure qui concerne un equipement ( indirectement la procedure concerne une demande de procedure et un labo ).

Voila mon MCD ( les attributs ne sont pas complets, il n'y a ici que les id. )



Aux vues de ces règles, j'aurais souhaité un peu d'aide concernant la validité de mon MCD ( comme vous pouvez le voir il y a des associations 4-aires et ternaires ... ) et aussi quelques indications; a savoir,
comment faire en sorte d'obtenir une entrée unique pour une demande de procedure qui concerne les trois laboratoires ?
Car avec mon modele je vais obtenir : (num_demP,#id_labo,#id_inge,#id_eq,#date.......)
Pour une meme demande concernant deux laboratoires :
1 --> (1,labo1,ingé1,equipement1,date1...)
2 --> (2,labo2,ingé1,equipement1,date1...)

N'hésitez pas a me faire reformuler si je n'ai pas été trés clair.

MERCI...

[CinePhil]

- Un laboratoire est composé d'employés, qui ne sont affectés qu'a un seul laboratoire.
- Un des employés du labo. dirige celui-ci.
- Un equipement est conçut par un service de conception employant plusieurs ingenieurs.

jusque là, ça va.

- L'ingenieur etablit un document "demande de procedure", qui concerne 1 ou plusieurs labo. et un seul équipement. CE DOCUMENT EST LE MEME POUR CHAQUE LABO CONCERNéS.

Il faut une entité type pour ce document.

Un ingénieur peut établir plusieurs demandes de procédure et une demande de procédure est établie par un seul ingénieur
ingenieur -0,n----etablir----1,1- demande_procedure

Une demande de procédure concerne de 1 à plusieurs laboratoires et un laboratoire peut être concerné par plusieurs demandes de procédure.
demande_procedure -1,n----concerner----0,n- laboratoire

Une demande de procédure concerne un seul équipement et un équipement peut être concerné par plusieurs demandes de procédure.
demande_procedure -1,1----concerner----0,n- equipement

Un des employés d'un des labo ( ayant recut un demande de proc. ) rédige une procedure qui concerne un equipement ( indirectement la procedure concerne une demande de procedure et un labo ).

En fait, les règles de gestion sont :
- une procédure est rédigée par un seul employé et un employé peut rédiger plusieurs procédures.
employe -0,n----rediger----1,1- procedure

- une procédure référence une seule demande de procédure et une demande de procédure peut être référencée par plusieurs procédures.
=> Enfin il me semble puisque une demande de procèdure peut concerner plusieurs laboratoires, plusieurs employés de différents laboratoires peuvent rédiger chacun une procédure pour cette demande.
procedure -1,1----referencer----0,n- demande_procedure

Mets ça en forme avec ton logiciel de modélisation mais je pense qu'il va y avoir une boucle qu'il va falloir résoudre.
Au passage, je ne vois pas l'utilité de l'entité type calendrier. Les dates ne sont ici, à mon avis, que de simples attributs dans différentes entités types. Je ne pense pas que tu aies besoin de gérer un calendrier.
propose nous le schéma modifié.

[Martin_75]

Bonjour, et merci d'avoir été si rapide.

le Mcd modifié est en piece jointe.

Quand tu parles de boucle, de quoi s'agit-il exactement ?

[CinePhil]

Ça se voit sur ton schéma ! Tu as même trois boucles !

1) Travailler - diriger
Un employé peut diriger un laboratoire alors qu'il travaille dans un autre.

2) Rédiger - Référencer - Concerner_lab - Travailler
Un employé peut rédiger une procédure qui référence uen demande de procédure qui concerne un autre laboratoire que celui dans lequel il travaille.

3) Employer - Etablir - Concerner_eq - Concevoir
Un ingénieur peut rédiger une demande de procédure concernant un équipement qui n'est pas conçu par le service de conception qui l'emploie.

Ça ne veut pas forcément dire que ce modèle est faux mais il y manque des contraintes pour empêcher les phénomènes que je viens de mentionner.

[Martin_75]

Exact, j'avais pensé a ça, mais sans l'imaginer comme une boucle, enfin sans faire le lien avec les boucles du MCD

[Martin_75]

Salut,

bon je reviens a l’assaut, car j'ai manifestement besoin d'aide.

J'ai un peu plus précisé mon modèle, en prenant une nouvelle règle :

- Une procédure aboutie ( ou pas ) sur UN procès verbal, lequel est rédigé par un employé du laboratoire ( labo. qui avait émis la procédure ).

Une nouvelle boucle vient donc s'ajouter aux autres.

Problème : Résoudre les boucles ( avec 3-4 assoc. ) avec les CIF ?

Si quelqu'un peut m'indiquer comment m'y prendre ( le mieux étant au travers d'un exemple de mon MCD ), cela serait fantastique )
[ATTACH][/ATTACH]
MERCI.

[fsmrel]

Bonjour,

Problème : Résoudre les boucles ( avec 3-4 assoc. ) avec les CIF ?...

Voilà quelque chose qui est assez simple à appréhender en SQL (au moins à l'aune de la norme), mais il faut reconnaître qu'au niveau MCD c'est du brutal ...
En effet sur la base du jeu d’essai suivant :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
 
CREATE TABLE LABORATOIRE (
        LaboId        CHAR(04)        NOT NULL
      , LaboNom       VARCHAR(48)     NOT NULL
    , CONSTRAINT LAbo_PK PRIMARY KEY (LaboId)
) ;
CREATE TABLE EMPLOYE (
        EmpId         CHAR(04)        NOT NULL
      , LaboId        CHAR(04)        NOT NULL
      , EmpNom        VARCHAR(48)     NOT NULL
    , CONSTRAINT Emp_PK PRIMARY KEY (EmpId)
    , CONSTRAINT Emp_Lab_FK FOREIGN KEY (LaboId)
           REFERENCES LABORATOIRE (LaboId)
) ;
CREATE TABLE DIRIGER (
        LaboId        CHAR(04)        NOT NULL
      , EmpId         CHAR(04)        NOT NULL
    , CONSTRAINT Dir_PK PRIMARY KEY (LaboId)
    , CONSTRAINT Dir_AK UNIQUE (EmpId)
    , CONSTRAINT Dir_Lab_FK FOREIGN KEY (LaboId)
           REFERENCES LABORATOIRE (LaboId)
    , CONSTRAINT Dir_Emp_FK FOREIGN KEY (EmpId)
           REFERENCES EMPLOYE (EmpId)
) ;
CREATE TABLE PROCEDURE (
        ProcId        CHAR(04)        NOT NULL
      , EmpId         CHAR(04)        NOT NULL
    , CONSTRAINT Proc_PK PRIMARY KEY (ProcId)
    , CONSTRAINT Proc_Emp_FK FOREIGN KEY (EmpId)
           REFERENCES EMPLOYE (EmpId)
) ;
CREATE TABLE PV (
        PvId          CHAR(04)        NOT NULL
      , ProcId        CHAR(04)        NOT NULL
      , EmpId         CHAR(04)        NOT NULL
    , CONSTRAINT PV_PK PRIMARY KEY (PvId)
    , CONSTRAINT PV_AK UNIQUE (ProcId)
    , CONSTRAINT PV_Proc_FK FOREIGN KEY (ProcId)
           REFERENCES PROCEDURE (ProcId)
    , CONSTRAINT PV_Emp_FK FOREIGN KEY (EmpId)
           REFERENCES EMPLOYE (EmpId)
) ; 
 
INSERT INTO LABORATOIRE (LaboId, LaboNom) VALUES ('L1', 'Labo 1') ;
INSERT INTO LABORATOIRE (LaboId, LaboNom) VALUES ('L2', 'Labo 2') ;
INSERT INTO LABORATOIRE (LaboId, LaboNom) VALUES ('L3', 'Labo 3') ;
 
INSERT INTO EMPLOYE (EmpId, LaboId, EmpNom) VALUES ('E11', 'L1', 'Emp 11') ;
INSERT INTO EMPLOYE (EmpId, LaboId, EmpNom) VALUES ('E12', 'L1', 'Emp 12') ;
INSERT INTO EMPLOYE (EmpId, LaboId, EmpNom) VALUES ('E13', 'L1', 'Emp 13') ;
 
INSERT INTO EMPLOYE (EmpId, LaboId, EmpNom) VALUES ('E21', 'L2', 'Emp 21') ;
INSERT INTO EMPLOYE (EmpId, LaboId, EmpNom) VALUES ('E22', 'L2', 'Emp 22') ;
INSERT INTO EMPLOYE (EmpId, LaboId, EmpNom) VALUES ('E23', 'L2', 'Emp 23') ;
 
INSERT INTO EMPLOYE (EmpId, LaboId, EmpNom) VALUES ('E31', 'L3', 'Emp 31') ;
INSERT INTO EMPLOYE (EmpId, LaboId, EmpNom) VALUES ('E32', 'L3', 'Emp 32') ;
INSERT INTO EMPLOYE (EmpId, LaboId, EmpNom) VALUES ('E33', 'L3', 'Emp 33') ;
 
INSERT INTO DIRIGER (LaboId, EmpId) VALUES ('L1', 'E12') ;
INSERT INTO DIRIGER (LaboId, EmpId) VALUES ('L2', 'E11') ;
INSERT INTO DIRIGER (LaboId, EmpId) VALUES ('L3', 'E32') ;
 
INSERT INTO PROCEDURE (ProcId, EmpId) VALUES ('PR1', 'E11') ;
INSERT INTO PROCEDURE (ProcId, EmpId) VALUES ('PR2', 'E11') ;
 
INSERT INTO PV (PVId, ProcId, EmpId) VALUES ('PV1', 'PR1', 'E22') ; -- Délinquant !

Il est alors possible de mettre en œuvre une assertion traduisant la contrainte suivante :

L’employé qui a rédigé une procédure et celui qui en a rédigé le PV doivent appartenir au même laboratoire.

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
CREATE ASSERTION MARTIN_75_ASSERT_PV CHECK 
    (NOT EXISTS (
                 SELECT '' AS 'Err PV', x.PvId, z.ProcId, y.EmpId, y.LaboId, t.EmpId, t.LaboId 
                 FROM   PV AS x JOIN EMPLOYE AS y ON x.EmpId = y.EmpId 
                                JOIN PROCEDURE AS z ON x.ProcId = z.ProcId
                                JOIN EMPLOYE AS t ON  z.EmpId = t.EmpId 
                                                  AND t.LaboId <> y.LaboId 
    )) ;

Si le SGBD ne sait pas ce qu’est une assertion, on peut en passer par un trigger.

Accessoirement, je ferai remarquer qu’il n’y a pas ici de boucle à proprement parler, mais plutôt ce qu’il est convenu d’appeler une contrainte de chemin (conterminous path). A propos du niveau MCD, je cite Arnold Rochfeld et José Moréjon (La méthode Merise, Tome 3, Gamme Opératoire, page 103) :

Les contraintes d’intégrité peuvent être classées suivant différents types. [...] Dans cette catégorie nous classerons les contraintes de chemin qui traduisent le fait que par deux chemins différents dans un modèle de données on doit atteindre une même réalisation partant de deux réalisations de départ.

Quoi qu'il en soit, la représentation graphique suivante (Merise/2) où figure une contrainte d'inclusion censée traduire l'assertion devrait tendre vers la vérité. Si JPhi33 passe par là, merci à lui de donner son avis...




Dans le même ordre d’idée, pour assurer qu’un employé dirige un laboratoire seulement s’il en fait partie :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
CREATE ASSERTION MARTIN_75_ASSERT_DIR CHECK 
    (NOT EXISTS (
                 SELECT '' AS 'Err Direction', x.EmpId, y.EmpId AS 'Labo légal', x.LaboId AS 'Labo erroné'
                 FROM   DIRIGER AS x JOIN EMPLOYE AS y 
                        ON  x.EmpId = y.EmpId
                        AND x.LaboId <> y.LaboId 
    )) ;

[Martin_75]

Salut, et Ouaaw merci d'avoir pris la peine de me sortir le code )

Au niveau du SGBD, je sais comment traduire ces contraintres ( triggers, ... ), là ou je bloque c'est surtout sur la représentation des contraintes dans le MCD....

Merci.

[JPhi33]

Bonjour à tous,

Si JPhi33 passe par là, merci à lui de donner son avis...

Je ne connais pas les contraintes de chemin. Concernant les contraintes sémantiques, à ma connaissance, celles-ci s'expriment entre associations (CIF comprises). Dans le cas présent, c'est-à-dire la règle

Une procédure aboutie ( ou pas ) sur UN procès verbal, lequel est rédigé par un employé du laboratoire ( labo. qui avait émis la procédure ).

je n'ai pas trouvé de représentation graphique (ce qui ne veut pas dire qu'il n'en existe pas). La contrainte d'inclusion, telle que représentée par fsmrel (mais "pointant" vers la CIF "travailler" et non pas vers l'entité Laboratoire) signifierait simplement que les deux employés travaillent dans un Laboratoire (ce qui est l'évidence) mais pas obligatoirement le même.

Il faudrait donc se contenter d'une formulation textuelle de la règle en complément du MCD, qui pourrait être celle-ci :

Un procès-verbal ne peut être rédigé que par un employé du même laboratoire que celui de l'employé ayant rédigé la procédure dont est issu le procès-verbal.

[Martin_75]

Bon cher amis, je reviens a l'attaque.
Avant de (re)commencer, merci pour vos posts.

J'ai un peu plus avancé, et surtout, j'ai modifié et complété le MCD.
Pour ce qui est des règles de gestion portant sur les boucles, je pense en effet que des notes textuelles suffiront.

Voice donc les règles de gestions ( BAsiques ) :

Employé:
Un employé travaille dans un et un seul des laboratoires
Un des employés du laboratoire dirige celui-ci
Un employé redige 0,n procédures
Un employé redige 0,n PV

Equipement:
Un equipement est typé ( 1,1 type par equipement ) et appartient 1,1 affaire
un equipement est conçut par 1,1 ligne produit

Affaire:
Une affaire contient 0,n equipements

Ligne produit:
Une ligne produit conçoit un ou plusieurs equipements
Une ligne produit emploi des demandeurs

Demandeur:
Un demandeur travaille dans une et une seule ligne_produit
Un demandeur redige une ou plusieur demande de procédure

Demande de procédure:
Une demande de procédure
- Est rédigée par 1,1 demandeur
- Concerne 1,1 equipement
- S'adresse à 1,n laboratoires
- Référence 0,n Procédures ( La demande de procédure aboutira ou pas sur 1 ou plusieurs procédures ( chacunes venant d'un laboratoire distinct ) )
- Est documentée par 0,n documents

Document_dp:
Ce sont les documents associés à la demande de procédure
les documents_dp sont typés ( Type_doc)

Procédure:
Une procédure
- Est rédigéée par 1,1 employé
- Est appliquée par 0,n essais
- Est Référencée par 1,1 demande de procédure

PV:
Un PV
- Est rédigéée par 1,1 employé
- Décrit 1,1 essais

Essai:
un essai
- Est l'application de 1,1 procédure
- Est décrit par un PV


MCD : ( il manque quelques infos ... )

Aprés avoir essayé de générer un MPD, voici les erreurs :

1.[erreur] Références circulaires -> Référence 'Diriger',Référence 'Travailler'
2.[erreur] Références circulaires -> Référence 'Decrire',Référence 'Decrire'
3.[warning] Inclusion de l'index -> Index 'Adressée à.ADRESSEE_A_PK' inclut 'ADRESSEE_A_FK'
4.[warning] Inclusion de l'index -> Index 'Documenter.DOCUMENTER_PK' inclut 'DOCUMENTER_FK'

Alors :
1.???
2.Problème entre PV, et Essais ( Relation 1,1--décrire--1,1 ). Dois-je fusionner les deux entités ?
3.???
4.Peut etre devrais-je remplacer Document_dp --0.n---Documenter par Document_dp --1,n -- Documenter ?

Si vous pourriez m'orienter un peu ( plus ) ...

Merci et bonne journée a vous .

[Martin_75]

OUUUUUUUUUPS

pardonnez moi s'il vous plait ....
Simple petite erreur absurde :
Laboratoire----1,n---Travaille---1,1----Employé
Et pas Laboratoire----0,n---Travaille---1,1----Employé

Du coup, les message de warning sont :




Le MCD Vous parait-il correct ( avec la correction ci-dessus ) ?

[fsmrel]

Bonjour Martin,


C'est un plaisir de vous revoir.

PowerAMC ne nous facilite pas la tâche pour gérer des cycles car il fait dans le trop simple lors de la production du MLD et son algorithme mériterait d’être rendu plus intelligent. Mais bon, on va faire avec.

Le plus simple consiste à mettre en œuvre un attribut de type booléen dans l’en-tête de l’entité-type EMPLOYE, permettant de savoir si un employé est directeur du laboratoire qui l’emploie (appelons EtreDirecteur cet attribut) :




MLD :




Mais, au stade SQL, il y aura une contrainte à prévoir (sous forme d’assertion au sens de la norme SQL ou de trigger si votre SGBD ne vous permet pas de coder l’instruction CREATE ASSERTION), à savoir que ce booléen ne peut prendre qu’une seul fois la valeur VRAI pour un laboratoire (paire {LaboId, EtreDirecteur} de l’en-tête de la table EMPLOYE).

[Martin_75]

Bonjour, et merci pour votre répnse.

J'ai décidé d'utiliser ce que me proposé PowerAmc au niveau graphique, tout en rectifiant a l'aide d'une note textuelle cette proposition.

Pour ma documentation, je n'ai besoin que du MCD ( comme graphique ), par contre au niveau physique, j'ai retouché le code sql généré.

PowerAMC me proposé :

/* Table : EMPLOYE*/
create table EMPLOYE
(
ID_EMP int(8) not null auto_increment,
ID_LAB char(3),
LAB_ID_LAB char(3) not null,
NOM_EMP char(50),
PRENOM_EMP char(50),
FONCTION_EMP char(50),
TEL_EMP char(50),
primary key (ID_EMP)
);

/* Table : LABORATOIRE */
create table LABORATOIRE
(
ID_LAB char(3) not null,
ID_EMP int(8) not null,
NOM_LAB char(50),
primary key (ID_LAB)
);

alter table EMPLOYE add constraint FK_DIRIGER foreign key (ID_LAB)
references LABORATOIRE (ID_LAB) on delete restrict on update restrict;

alter table EMPLOYE add constraint FK_TRAVAILLER foreign key (LAB_ID_LAB)
references LABORATOIRE (ID_LAB) on delete restrict on update restrict;

alter table LABORATOIRE add constraint FK_DIRIGER2 foreign key (ID_EMP)
references EMPLOYE (ID_EMP) on delete restrict on update restrict;

alter table LABORATOIRE add constraint FK_TRAVAILLER2 foreign key ()
references EMPLOYE (ID_EMP) on delete restrict on update restrict;


J'ai donc simplement :
- supprimé l'attribut Employe.LAB_ID_LAB,
- supprimé la foreign key associée a Employe.LAB_ID_LAB,
- renommé la FK : FK_DIRIGER en FK_TRAVAILLER
- renommé la FK : FK_DIRIGER2 en FK_DIRIGER
- supprimé la FK_TRAVAILLER2

Avec un trigger je pourrais contrôler l'ajout ou l'update concernant la direction d'un laboratoire

[fsmrel]

Hum...

Avec un trigger je pourrais contrôler l'ajout ou l'update concernant la direction d'un laboratoire
Plus précisément ?

En attendant le détail de votre trigger, je constate que le bonhomme Null s’est introduit dans votre système, notamment en ce qui concerne la colonne ID_LAB de la table EMPLOYE (et qui fait partie de la clé étrangère qui référence LABORATOIRE). Je constate aussi qu’un employé peut diriger plein de laboratoires (sans même diriger le sien)...

[Martin_75]

BOnjour,

Effectivement : ..... Hummmmmmm...

Hum...
En attendant de détail de votre trigger, je constate que le bonhomme Null s’est introduit dans votre système, notamment en ce qui concerne la colonne ID_LAB de la table EMPLOYE (et qui fait partie de la clé étrangère qui référence LABORATOIRE).

/* Table : EMPLOYE*/
create table EMPLOYE
(
ID_EMP int(8) not null auto_increment,
ID_LAB char(3) not null,
NOM_EMP char(50),
PRENOM_EMP char(50),
FONCTION_EMP char(50),
TEL_EMP char(50),
primary key (ID_EMP)
);

Je constate aussi qu’un employé peut diriger plein de laboratoires (sans même diriger le sien)...

Je compte m'occuper de cela avec les triggers !

[fsmrel]

Bonjour,

Je compte m'occuper de cela avec les triggers !

Concernant la table LABORATOIRE, vous devriez déjà définir {ID_EMP} comme clé candidate de la table LABORATOIRE (clause UNIQUE de SQL), ça n’est évidemment suffisant en rien mais c’est au moins nécessaire.

La situation est la suivante :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
/* Table : LABORATOIRE */
 
create table LABORATOIRE
(
ID_LAB char(3) not null,
ID_EMP int(8) not null,
NOM_LAB char(50),
primary key (ID_LAB)
);
 
/* Table : EMPLOYE*/
 
create table EMPLOYE
(
ID_EMP int(8) not null auto_increment,
ID_LAB char(3) not null,
NOM_EMP char(50),
PRENOM_EMP char(50),
FONCTION_EMP char(50),
TEL_EMP char(50),
primary key (ID_EMP)
);

Histoire d’y voir plus clair dans vos modifications à tout va, au vu des clés étrangères, le MLD correspondant est celui-ci (j'ai ajouté les Not Null manquants) :



Un MCD par rétroconception :



Et le piège se referme, vous êtes maintenant dans la configuration dite « l’œuf et la poule » ou encore « étreinte fatale » :

Quand vous voudrez créer un laboratoire, le SGBD vous demandera d’abord de créer un employé (disons le directeur) et réciproquement. C’est mal parti, même avec une patience infinie.

[Martin_75]

« l’œuf et la poule »

Et oui ! Il y a des questions universelles ....

Le probleme avec le MCD que vous me montrer, est que tout les employés doivent diriger un laboratoire !

Concernant le modele conceptuel :
En laissant le MCD que j'ai posté ( complété par les regles de gestion que je n'arrive pas a transcrire et ajoutant les modifications (not null) ) est-ce que vous pensez que ça irait ?

COncernant le modele physique:
Comment puis-je faire pour eviter de me retrouver dans cette fameuse situation, que vous nommez si bien : "l'etreinte fatale" ?

Car en fait, je n'ai jamais su si la conception du MCD devait prendre en compte, ou pas ce style de contraintes !


C'est a dire que d'un point de vu de la gestion, on a :
laboratoire--1,1--diriger--0,1--employé
Ce qui correspond aux règles

Mais personnellement je suis systématiquement entre ce choix, et :
laboratoire--0,1--diriger--0,1--employé
Ce qui me permet les insertion dans la base, mais du coup je ne respecte plus les regles de gestion ...

[CinePhil]

C'est a dire que d'un point de vu de la gestion, on a :
laboratoire--1,1--diriger--0,1--employé
Ce qui correspond aux règles

Mais personnellement je suis systématiquement entre ce choix, et :
laboratoire--0,1--diriger--0,1--employé
Ce qui me permet les insertion dans la base, mais du coup je ne respecte plus les regles de gestion ...

Les règles de gestion sont parfois édictées de façon un peu trop strictes.

N'est-il pas possible par exemple qu'un directeur de laboratoire décède et que le laboratoire se retrouve sans directeur pendant un mois à cause d'un processus de nomination du nouveau directeur qui dure un temps non négligeable ?

Ou bien que le conseil d'administration décide de la création d'un nouveau laboratoire mais que la nomination du directeur de celui-ci arrive plus tard que la nécessité de créer le laboratoire en BDD ?

À noter quand même que la première association citée ci-dessus n'empêche aucunement les insertions en BDD. Il suffit de créer l'employé avant le laboratoire.

[fsmrel]

Bonsoir,

Le problème avec le MCD que vous me montrez, est que tous les employés doivent diriger un laboratoire !

Ce MCD est une traduction du MLD qui est lui-même la conséquence de l’aménagement que vous avez réalisé pour les tables LABORATOIRE et EMPLOYE (attributs ID_LAB et ID_EMP systématiquement Not Null).

Mais il est vrai qu’on peut préférer le MCD ci-dessous, correspondant lui aussi au MLD, et où l’on remplace 1,1 par 0,1 pour éviter la prolifération des directeurs :



Toutefois, le fait d’avoir aménagé le MLD manuellement ne résout en fait rien au niveau opérationnel en ce qui concerne l'oeuf et la poule...

Reprenons-le :




Ainsi que les règles de gestion des données sur lesquelles il faut se focaliser :

RG01 - Un employé travaille dans un et un seul laboratoire.

RG02 - Un laboratoire est dirigé par un et un seul employé.

RG03 - Un employé ne peut diriger un laboratoire que s’il y travaille.

La règle RG01 est respectée du fait de la clé étrangère FK_TRAVAILLER, laquelle ne pose pas de problème à l’occasion de sa mise en oeuvre.

La règle RG02 peut être respectée sans difficulté, à condition de définir {ID_EMP} comme clé alternative (mickey <ak> chez PowerAMC) :



Mais c’est bien la règle RG03 qui pose problème. Dans le cadre de la théorie relationnelle (donc avec un SGBD de type D), tout se passe pour le mieux, à condition que, lorsqu’ on crée un laboratoire, on prenne soin de créer simultanément l’employé qui en est le directeur :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
INSERT LABORATOIRE RELATION {TUPLE {ID_LAB 'L01', ID_EMP 'E03', NOM_LAB 'LABO 01'}} ,

INSERT EMPLOYE RELATION {TUPLE {ID_LAB 'L01', ID_EMP 'E03', NOM_EMP 'Carole', ...}} ;

Les deux INSERT sont séparés par une virgule, ce qui signifie qu’ils font partie d’un même lot et que la règle RG03 ne sera déclenchée qu’une fois détectée la fin de lot (en l’occurrence marqué par un point-virgule). Le procédé est aussi efficace que simple et élégant (et, en passant, rend caduc le principe non sans risque de différer les contrôles —deferrable mode— prôné par la norme SQL).

Mais pour que la règle RG03 soit respectée, la contrainte référentielle FK_TRAVAILLER n’est pas efficace (elle fiche même plutôt la patouille...), en effet ça n’est pas elle toute seule qui empêcherait qu’un laboratoire soit dirigé par un employé qui n’y travaille pas. Toujours dans le cadre de la théorie relationnelle, on met donc en œuvre la contrainte suivante (il s’agit de ce que l’on appelle une contrainte d’inclusion) :

CONSTRAINT RG03 LABORATOIRE {ID_LAB, ID_EMP} EMPLOYE {ID_LAB, ID_EMP} ;

Voilà pour la théorie, on tord le cou sans problème à la poule. Qu’en est-il avec SQL ?

Bien entendu, comme dans le cadre du Modèle Relationnel, la contrainte référentielle FK_TRAVAILLER ne suffit pas non plus dans le cadre de la norme SQL. Imaginons la représentation graphique suivante pour la contrainte d’inclusion RG03 ci-dessus :




Les valeurs prises par la paire d’attributs {ID_LAB, ID_EMP} de l’en-tête de la table LABORATOIRE doivent appartenir au sous-ensemble de valeurs {ID_LAB, ID_EMP} de l’en-tête de la table EMPLOYE.

Cette contrainte donne lieu à une assertion avec contrôle différé :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
CREATE ASSERTION RG03 CHECK
(NOT EXISTS (
 SELECT *
 FROM   LABORATOIRE AS x
 WHERE  NOT EXISTS  
       (SELECT *
        FROM   EMPLOYE AS y
        WHERE  x.ID_LAB = y.ID_LAB 
          AND  x.ID_EMP = y.ID_EMP) 
)) 
INITIALLY DEFERRED DEFERRABLE ;

Exemple de création d’un laboratoire et de son directeur, sans contrôle de la contrainte, suivie aussitôt du rétablissement du contrôle immédiat :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
INSERT INTO LABORATOIRE (ID_LAB, ID_EMP, NOM_LAB) VALUES ('L01', 'E03', 'LABO 01') ;
INSERT INTO EMPLOYE (ID_LAB, ID_EMP, NOM_EMP) VALUES ('L01', 'E03', 'Carole', ...) ;

SET CONSTRAINT RG03 IMMEDIATE ;

Si l’on doit créer une nouvelle paire {laboratoire, employé}, on fera d’abord repasser la contrainte dans le mode DEFERRED.


Mais nos SGBD du commerce ne proposent généralement pas l’instruction CREATE ASSERTION et il faut alors s’orienter sur une solution à base de triggers. Le hic est que les contrôles sont alors immédiats et l’on se retrouve coincé avec le problème de l’œuf et de la poule sur les bras.

Mais je vais revenir avec une solution qui pourra vous intéresser (si les triggers ne vous dérangent pas), histoire de tordre le cou à la poule...

[fsmrel]

Bonjour,


Suite de l'épisode précédent.

Revenons sur les structures des tables LABORATOIRE et EMPLOYE :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE LABORATOIRE
(
        ID_LAB       CHAR(3)              NOT NULL
      , ID_EMP       CHAR(3)              NOT NULL
      , NOM_LAB      VARCHAR(48)          NOT NULL
    , CONSTRAINT LABORATOIRE_PK PRIMARY KEY (ID_LAB)
    , CONSTRAINT LABORATOIRE_AK UNIQUE (ID_EMP)
) ;
 
CREATE TABLE EMPLOYE
(
        ID_EMP       CHAR(3)              NOT NULL
      , ID_LAB       CHAR(3)              NOT NULL
      , NOM_EMP      VARCHAR(48)          NOT NULL
      , FONCTION_EMP VARCHAR(48)          NOT NULL    DEFAULT ''
      , TEL_EMP      CHAR(20)             NOT NULL    DEFAULT ''
    , CONSTRAINT EMPLOYE_PK PRIMARY KEY (ID_EMP)
    , CONSTRAINT FK_TRAVAILLER FOREIGN KEY (ID_LAB)
                 REFERENCES LABORATOIRE (ID_LAB)
) ;

Une fois de plus, la contrainte référentielle FK_DIRIGER n’est pas mise en œuvre, puisqu’elle est inefficace, et en lieu et place on a défini la contrainte d’inclusion :

CONSTRAINT RG03 LABORATOIRE {ID_LAB, ID_EMP} EMPLOYE {ID_LAB, ID_EMP} ;

Tout en sachant qu’en SQL, il est vain de l’implanter si on ne dispose pas du mode différé (deferrable mode) défini par la norme SQL. Cela dit, considérons l’instruction suivante :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
CREATE VIEW LAB_EMP (ID_LAB, NOM_LAB, ID_EMP, NOM_EMP, FONCTION_EMP, TEL_EMP) AS
   SELECT x.ID_LAB, x.NOM_LAB, x.ID_EMP, y.NOM_EMP, y.FONCTION_EMP, y.TEL_EMP
   FROM   LABORATOIRE AS x JOIN EMPLOYE AS y
          ON  x.ID_LAB = y.ID_LAB
          AND x.ID_EMP = y.ID_EMP ;

On y définit une vue correspondant au prédicat :

(P1) - Le laboratoire ID_LAB a pour nom NOM_LAB, il est dirigé par l’employé ID_EMP qui a pour nom NOM_EMP, pour fonction FONCTION_EMP et pour téléphone TEL_EMP.

En fait ce prédicat est la conjonction de deux autres prédicats eux-mêmes traduisant la jointure LABORATOIRE JOIN EMPLOYE :

(P2) - Le laboratoire ID_LAB a pour nom NOM_LAB, il est dirigé par l’employé ID_EMP,

(P3) - L’employé ID_EMP travaille pour le laboratoire ID_LAB, il a pour nom NOM_EMP, pour fonction FONCTION_EMP et pour téléphone TEL_EMP.

P2 s’applique bien sûr à l’en-tête de la table LABORATOIRE et P3 à celui de la table EMPLOYE.

De la même façon, l’instruction suivante est la conjonction d’un insert dans la table LABORATOIRE et d’un insert dans la table EMPLOYE :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
INSERT INTO LAB_EMP (ID_LAB, NOM_LAB, ID_EMP, NOM_EMP, FONCTION_EMP, TEL_EMP) 
       VALUES ('L01', 'LABO 01', 'E03', 'Carole', 'DIR', 'BAL0001') ;

Du point de vue théorique, cette instruction est parfaitement légale, à charge au SGBD de mettre à jour les tables LABORATOIRE et EMPLOYE en conséquence.

Le point fondamental dans cet histoire est que les attributs ID_LAB et ID_EMP ne figurent qu’une fois, autrement dit la contrainte d’inclusion RG03 est vérifiée de facto. Si donc on s’impose de ne se servir que de cette vue pour créer un laboratoire, en même temps que son directeur, alors on sait que la base de données sera valide eu égard à la contrainte RG03.

Dans cette optique, il faut interdire la mise à jour directe de la table LABORATOIRE, et l’on pourra demander au DBA d’oeuvrer en conséquence, c'est-à-dire prévoir une instruction de gestion des droits du genre REVOKE INSERT, UPDATE, DELETE ON LABORATOIRE FROM <authorization-name> ;

Si le SGBD permet de mettre à jour une vue de jointure, alors on en reste là. Sinon, on met en œuvre un trigger branché sur la vue LAB_EMP et chargé de ventiler les données dans les tables LABORATOIRE et EMPLOYE. Exemple (SQL Server) :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TRIGGER LABO_EMP_INSERT_TR ON LAB_EMP INSTEAD OF INSERT AS
 
    DECLARE @Count AS INT, @FonctionEmp AS  VARCHAR(48), @Err AS VARCHAR(48)
 
    SET @Count = (SELECT COUNT(*) 
                  FROM   INSERTED) ;   
    IF @Count = 0 
        BEGIN
            SELECT 'Insert into vue LAB_EMP : Violation de la règle de gestion RG03' AS Viol_RG03, * FROM INSERTED
            RAISERROR (@Err, 15, 1) 
            RETURN 
        END
    ELSE
        BEGIN
            INSERT INTO LABORATOIRE (ID_LAB, ID_EMP, NOM_LAB)
                   SELECT  ID_LAB, ID_EMP, NOM_LAB 
                   FROM    INSERTED ;
 
            INSERT INTO EMPLOYE (ID_EMP, ID_LAB, NOM_EMP, FONCTION_EMP, TEL_EMP)
                   SELECT  ID_EMP, ID_LAB, NOM_EMP, FONCTION_EMP, TEL_EMP 
                   FROM    INSERTED ;
        END

Je crois savoir que la majorité des SGBD permettent de mettre en œuvre des triggers appliqués à des vues, à l’exception sans doute de MySQL.

En ce qui concerne les employés qui ne dirigent pas les laboratoires, les inserts sont classiques :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
INSERT INTO EMPLOYE (ID_LAB, ID_EMP, NOM_EMP, FONCTION_EMP, TEL_EMP) 
       VALUES ('L01', 'E01', 'Albert', 'chercheur', 'ODE8400') ;

Mais ils ne sont possibles qu’une fois créés les laboratoires.

Le trigger ci-dessus est du type ON INSERT, reste à prévoir les triggers du type ON UPDATE et ON DELETE.

Si le SGBD ne permet pas d’utiliser des triggers appliqués à des vues, on se rabat sur la mise à jour directe de la table LABORATOIRE, mais la règle de gestion RG03 peut alors être violée sans problème : à charge de l’application de la faire respecter, comme on faisait il y a 40 ans, avec IMS/VS ou IDMS. On peut aussi changer carrément de MLD et utiliser celui que j’avais déjà proposé comme alternatif :



Là encore, il y a du trigger pour faire respecter la règle : Au moins un et au plus un directeur par laboratoire, mais appliqué seulement à la table EMPLOYE.