Bonsoir,
Envoyé par
JPhi33
Les tables du MLD sont donc :
ENQUETEUR(Id Enqueteur, ..., #Id Equipe)
EQUIPE(Id Equipe, ..., #Id Enqueteur)
D’accord, mais...
Modélisons la chose, par exemple avec DB-MAIN (gratuit), en procédant par rétroconception.
On part d’un script SQL conforme :
CREATE TABLE EQUIPE
(
Id_Equipe INT NOT NULL
, Id_Enqueteur INT NOT NULL
, Nom_Equipe VARCHAR(32) NOT NULL
, CONSTRAINT EQUIPE_PK PRIMARY KEY (Id_Equipe)
, CONSTRAINT EQUIPE_AK UNIQUE (Id_Enqueteur)
) ;
CREATE TABLE ENQUETEUR
(
Id_Enqueteur INT NOT NULL
, Nom_Enqueteur VARCHAR(32) NOT NULL
, Id_Equipe INT NOT NULL
, CONSTRAINT ENQUETEUR_PK PRIMARY KEY (Id_Enqueteur)
) ;
ALTER TABLE EQUIPE ADD CONSTRAINT EQUIPE_ENQUETEUR_FK FOREIGN KEY (Id_Enqueteur)
REFERENCES ENQUETEUR (Id_Enqueteur) ;
ALTER TABLE ENQUETEUR ADD CONSTRAINT ENQUETEUR_EQUIPE_FK FOREIGN KEY (Id_Equipe)
REFERENCES EQUIPE (Id_Equipe) ;
MLD obtenu par rétroconception :
Ça sent à plein nez le deadlock, le verrou mortel, l’étreinte fatale...
Mais bon, poursuivons la rétroconception. Sans surprise, le MCD obtenu est le suivant :
MCD semblable à celui proposé par conspiration :
Cela dit, le MLD illustre parfaitement le problème de l’œuf et de la poule :
Si on veut insérer une ligne dans la table EQUIPE, on doit y fournir une valeur <a> pour l’attribut Id_Enqueteur, ce qui implique l’existence dans la table ENQUETEUR d’une ligne où l’attribut Id_Enqueteur contient la valeur <a> (intégrité référentielle oblige) : il faut donc d’abord insérer une ligne dans la table ENQUETEUR contenant cette valeur <a>, mais cette insertion n’est possible que si l’attribut Id_Equipe de cette table contient une valeur <b> déjà présente dans la table EQUIPE (attribut Id_Equipe)...
Dans le contexte des SGBD SQL traditionnels, on est coincé.
Pour s’en sortir, L’association RESPONSABLE du MCD précédent doit être transformée en entité-type :
MLD dérivé :
Script SQL :
CREATE TABLE EQUIPE
(
Id_Equipe INT NOT NULL
, Nom_Equipe VARCHAR(32) NOT NULL
, CONSTRAINT EQUIPE_PK PRIMARY KEY (Id_Equipe)
) ;
CREATE TABLE ENQUETEUR
(
Id_Enqueteur INT NOT NULL
, Nom_Enqueteur VARCHAR(32) NOT NULL
, Id_Equipe INT NOT NULL
, CONSTRAINT ENQUETEUR_PK PRIMARY KEY (Id_Enqueteur)
, CONSTRAINT ENQUETEUR_EQUIPE_FK FOREIGN KEY (Id_Equipe)
REFERENCES EQUIPE (Id_Equipe)
) ;
CREATE TABLE RESPONSABLE
(
Id_Enqueteur INT NOT NULL
, Id_Equipe INT NOT NULL
, CONSTRAINT RESPONSABLE_PK PRIMARY KEY (Id_Enqueteur)
, CONSTRAINT RESPONSABLE_AK UNIQUE (Id_Equipe)
, CONSTRAINT RESPONSABLE_ENQUETEUR_FK FOREIGN KEY (Id_Enqueteur)
REFERENCES ENQUETEUR (Id_Enqueteur)
, CONSTRAINT RESPONSABLE_EQUIPE_FK FOREIGN KEY (Id_Equipe)
REFERENCES EQUIPE (Id_Equipe)
) ;
Si le responsable d’une équipe doit appartenir à celle-ci, il faudra mettre en oeuvre un trigger pour que cette contrainte soit garantie.
Exemple avec MySQL :
CREATE TRIGGER RESPONSABLE_BEFORE_INSERT BEFORE INSERT ON RESPONSABLE
FOR EACH ROW
BEGIN
IF EXISTS (SELECT '' FROM ENQUETEUR WHERE Id_Enqueteur = new.Id_Enqueteur AND Id_Equipe <> new.Id_Equipe) THEN
SET @Erreur = CONCAT ('L''enquêteur ''', new.Id_Enqueteur, ''' peut être responsable de son équipe mais pas de l''équipe ''', new.Id_Equipe, '''.') ;
SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @Erreur ;
END IF ;
END
Un bout de jeu d’essai pour vérifier que la contrainte sera bien garantie :
INSERT INTO EQUIPE (Id_Equipe, Nom_Equipe) VALUES
(1, 'équipe 1'), (2, 'équipe 2'), (3, 'équipe 3'), (4, 'équipe 4') ;
INSERT INTO ENQUETEUR (Id_Enqueteur, Nom_Enqueteur, Id_Equipe) VALUES
(1, 'Fernand', 1), (2, 'Raoul', 2), (3, 'Paul', 2), (4, 'Mado', 2) ;
INSERT INTO RESPONSABLE (Id_Enqueteur, Id_Equipe) VALUES
(1, 1) , (2, 3)
Partager