Discussion :

[Rifton007]

Bonjour à tous et merci d'avance pour votre aide.

J'aimerais vous soumettre ces deux MCD et m'expliquer mes erreurs.
Vendeurs et clients peuvent faire des publications sur le site.
Néanmoins, chaque publication du vendeur est propre au magasin qu'il possède.

Pour un même concept, j'ai eu deux approches différentes.
Mon choix c'est orienté vers le MCD-2, mais sans conviction.



Merci à vous,

[fsmrel]

Bonsoir Rifton,


Le MCD MCD-2 ne convient pas car on ne sait pas à quel magasin se réfère une publication.

Dans le MCD MCD-1, il faut supprimer l'association entre PUBLICATION et VENDEUR car elle est redondante : en effet, la publication P détermine le magasin M, lequel détermine le vendeur V, on sait donc par transitivité à quel vendeur se rattache une publication.

De la même façon, comme un commentaire K détermine une publication P, on sait (par transitivité) qui a rédigé K, il faut évacuer les associations (redondantes) avec CLIENT et VENDEUR.

Par ailleurs il faudra mettre en oeuvre une contrainte d'exclusion entre les publications, car en l'état, une publication P peut référencer à la fois le magasin M et le client C.

[Rifton007]

Bonjour fsmrel, comment vas-tu ?

Pour le MCD-1
J'ai supprimé la relation doublon.

Mais du coup, je vois mal comment représenter une contrainte d'exclusion entre publications.
Car effectivement, je pourrais avoir une boutique et un client avec le même ID.
Ma première idée serait un attribut dans l'entité-type Publication avec comme valeur 0 ou 1 pour déterminer un client ou une boutique.
Mais encore une fois sans conviction.

Pour le MCD-2

Le MCD MCD-2 ne convient pas car on ne sait pas à quel magasin se réfère une publication.

Puis-je te demander pourquoi, car la table d'association reprend user_id et store_id ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE `post_vendor` (
	`post_id` INT(11) NOT NULL,
	`store_id` INT(11) NOT NULL,
	`user_id` INT(11) NOT NULL,
	PRIMARY KEY (`post_id`),
	UNIQUE INDEX `UNIQUE` (`store_id`, `user_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

Au plaisir de te lire et encore merci pour tout.

[fsmrel]

Bonjour Rifton,



Revenons sur notre discussion précédente.

J’avais fait remarquer qu’il y avait des attributs en trop dans les entités-types vendors et customers de votre MCD :





Et, comme il s’agissait de l’attribut id des entités-types vendors et customers, il fallait par conséquent passer à ceci :





Ensuite, J’avais fait observer que l’attribut store_id n’avait pas d’utilité dans l’entité-type vendors, tout comme l’attribut event_id dans l’entité-type customers, donc que ces deux attributs devaient disparaître :

Je vois mal comment représenter une contrainte d'exclusion entre publications.

Tout d’abord, selon votre MCD, chaque publication fait référence à un client :





En conséquence, pour dire qu’il existe des publications qui ne font pas référence à des clients, il faut transformer la cardinalité 1,1, en 0,1 (avec pour conséquence que l’association ne peut plus être identifiante) :





Maintenant, pour représenter une exclusion, la norme Merise propose ceci :




Ce qui veut dire qu’il faut passer à la notation merisienne pour mettre en œuvre la contrainte d’exclusion entre les associations, appelons-les PUB_MAG et PUB_CLI, connectant MAGASIN et PUBLICATION d’une part, CLIENT et PUBLICATION d’autre part :





Problème : PowerAMC ne propose rien pour mettre en œuvre la contrainte d’exclusion. En outre, lors du passage à SQL, on aura dans la table PUBLICATION une clé étrangère référençant MAGASIN et une autre clé étrangère référençant CLIENT, et les attributs les composant devront pouvoir être marqués NULL, c'est-à-dire que les requêtes SQL faisant référence à ces attributs pourront fournir des résultats erronés (je vous renvoie à SQL and Relational Theory au chapitre 4, « No Duplicates, no Nulls »).

Pour éloigner le bonhomme Null menteur, je vous propose de mettre e œuvre une entité-type PUBLICATION, spécialisée en sous-types PUB_MAG et PUB_CLI :





On sait si une publication est celle d’un client en « remontant » de PUB_CLI cers CLIENT, sinon elle est celle d’un vendeur, et pour savoir qui est-il, on « remonte » de PUB_MAG vers MAGASIN puis VENDEUR.

On pourrait en rester là, mais pour améliorer la performance du système, on peut établir un lien direct entre UTILISATEUR et PUBLICATION, tout en sachant que cela générera des redondances dans les tables SQL (l’attribut utilisateur_id est en cause) : on commencera donc d’abord par évacuer ce redondances dans le MLD, lequel est pour le moment le suivant quand on en a demandé la génération :





Prenons le cas de PUB_CLI : les colonnes utilisateur_id et PUB_utilisateur_id sont redondantes, on va donc en éliminer une, par exemple PUB_utilisateur_id, mais il faudra commencer par modifier le lien entre les tables PUBLICATION et PUB_CLI. Pour cela, dans le MLD, on clique sur ce lien, puis dans la fenêtre « Propriétés de la référence » qui a été ouverte, on passe à l’onglet « Jointures », et on remplace la colonne PUB_utilisateur_id par la colonne utilisateur_id :





Au résultat :





Dans un 2e temps, on remplace PUB_utilisateur_id par utilisateur_id dans la clé primaire de PUB_CLI :





Ce qui donne :





L’attribut PUB_utilisateur_id ne participant plus à aucune clé primaire ou étrangère, on peut maintenant le supprimer sans risque :





On vérifie aussi que les attributs utilisateur_id et publication_id sont dans le bon ordre dans la clé primaire :





Manifestement ça n’est pas le cas ici, donc on fait passer utilisateur_id devant publication_id (lequel, conceptuellement parlant, ne correspond qu’à un identifiant relatif par rapport à utilisateur_id) :





Les aménagements réalisés pour la table PUB_CLI valent évidemment pour la table PUB_MAG. Le MLD doit devenir le suivant :





En tenant compte des commentaires :


MCD




MLD






D’où le code SQL :

USE temp ;

DROP TABLE IF EXISTS PUB_MAG ;
DROP TABLE IF EXISTS PUB_CLI ;
DROP TABLE IF EXISTS COMMENTAIRE ;
DROP TABLE IF EXISTS PUBLICATION ;
DROP TABLE IF EXISTS EVENEMENT ;
DROP TABLE IF EXISTS CLIENT ;
DROP TABLE IF EXISTS MAGASIN ;
DROP TABLE IF EXISTS VENDEUR ;
DROP TABLE IF EXISTS UTILISATEUR ;

-------------------------------------------------------------------------------------------------

CREATE TABLE UTILISATEUR 
(
   utilisateur_id       INT             NOT NULL,
   username             VARCHAR(64)     NOT NULL,
   mail                 VARCHAR(64)     NOT NULL,
   password             VARCHAR(64)     NOT NULL,
   CONSTRAINT UTILISATEUR_PK PRIMARY KEY (utilisateur_id)
) ;

CREATE TABLE VENDEUR 
(
   utilisateur_id       INT             NOT NULL,
   CONSTRAINT VENDEUR_PK PRIMARY KEY (utilisateur_id),
   CONSTRAINT VENDEUR_UTILISATEUR_FK FOREIGN KEY (utilisateur_id)
      REFERENCES UTILISATEUR (utilisateur_id)
) ;

CREATE TABLE MAGASIN 
(
   utilisateur_id       INT             NOT NULL,
   magasin_id           INT             NOT NULL,
   magasin_nom          VARCHAR(64)     NOT NULL,
   CONSTRAINT MAGASIN_PK PRIMARY KEY (utilisateur_id, magasin_id),
   CONSTRAINT MAGASIN_VENDEUR_FK FOREIGN KEY (utilisateur_id)
      REFERENCES VENDEUR (utilisateur_id) ON DELETE CASCADE
) ;

CREATE TABLE CLIENT 
(
   utilisateur_id       INT             NOT NULL,
   CONSTRAINT CLIENT_PK PRIMARY KEY (utilisateur_id),
   CONSTRAINT CLIENT_UTILISATEUR_FK FOREIGN KEY (utilisateur_id)
      REFERENCES UTILISATEUR (utilisateur_id) ON DELETE CASCADE
) ;

CREATE TABLE EVENEMENT 
(
   utilisateur_id       INT             NOT NULL,
   datum                DATETIME        NOT NULL,
   CONSTRAINT EVENEMENT_PK PRIMARY KEY (utilisateur_id),
   CONSTRAINT EVENEMENT_CLIENT_FK FOREIGN KEY (utilisateur_id)
      REFERENCES CLIENT (utilisateur_id) ON DELETE CASCADE
) ;

CREATE TABLE PUBLICATION 
(
   utilisateur_id       INT             NOT NULL,
   publication_id       INT             NOT NULL,
   publication_date     DATETIME        NOT NULL,
   publication_libelle  VARCHAR(255)    NOT NULL,   
   CONSTRAINT PUBLICATION_PK PRIMARY KEY (utilisateur_id, publication_id),
   CONSTRAINT PUBLICATION_UTILISATEUR_FK FOREIGN KEY (utilisateur_id)
      REFERENCES UTILISATEUR (utilisateur_id)
) ;

CREATE TABLE COMMENTAIRE 
(
   utilisateur_id       INT             NOT NULL,
   publication_id       INT             NOT NULL,
   commentaire_id       INT             NOT NULL,
   commentaire_date     DATE            NOT NULL,   
   commentaire_texte    VARCHAR(255)    NOT NULL,
   CONSTRAINT COMMENTAIRE_PK PRIMARY KEY (utilisateur_id, publication_id, commentaire_id),
   CONSTRAINT COMMENTAIRE_PUBLICATION_FK FOREIGN KEY (utilisateur_id, publication_id)
      REFERENCES PUBLICATION (utilisateur_id, publication_id) ON DELETE CASCADE
) ;

CREATE TABLE PUB_CLI 
(
   utilisateur_id       INT             NOT NULL,
   publication_id       INT             NOT NULL,
   CONSTRAINT PUB_CLI_PK PRIMARY KEY (utilisateur_id, publication_id),
   CONSTRAINT PUB_CLI_CLIENT_FK FOREIGN KEY (utilisateur_id)
      REFERENCES CLIENT (utilisateur_id) ON DELETE CASCADE,
   CONSTRAINT PUB_CLI_PUBLICATION_FK FOREIGN KEY (utilisateur_id, publication_id)
      REFERENCES PUBLICATION (utilisateur_id, publication_id) ON DELETE CASCADE
) ;

CREATE TABLE PUB_MAG 
(
   utilisateur_id       INT             NOT NULL,
   publication_id       INT             NOT NULL,
   magasin_id           INT             NOT NULL,
   CONSTRAINT PUB_MAG_PK PRIMARY KEY (utilisateur_id, publication_id),
   CONSTRAINT PUB_MAG_MAGASIN_FK FOREIGN KEY (utilisateur_id, magasin_id)
      REFERENCES MAGASIN (utilisateur_id, magasin_id),
   CONSTRAINT PUB_MAG_PUBLICATION_FK FOREIGN KEY (utilisateur_id, publication_id)
      REFERENCES PUBLICATION (utilisateur_id, publication_id)
) ;

DELIMITER GO

CREATE TRIGGER VENDEUR_EXCLUSION_INSERT BEFORE INSERT ON VENDEUR 
FOR EACH ROW
    BEGIN
        SET @Kount = (SELECT COUNT(*) FROM CLIENT WHERE utilisateur_id = NEW.utilisateur_id) ;  
        IF @Kount > 0 THEN
            SET @Erreur = CONCAT('Le vendeur d''identifiant "', NEW.utilisateur_id, '" existe déjà en tant que client. Rejet.') ; 
            SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @Erreur ;
        END IF ;            
    END 
GO 

CREATE TRIGGER CLIENT_EXCLUSION_INSERT BEFORE INSERT ON CLIENT 
FOR EACH ROW
    BEGIN
        SET @Kount = (SELECT COUNT(*) FROM VENDEUR WHERE utilisateur_id = NEW.utilisateur_id) ;  
        IF @Kount > 0 THEN
            SET @Erreur = CONCAT('Le client d''identifiant "', NEW.utilisateur_id, '" existe déjà en tant que vendeur. Rejet.') ; 
            SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @Erreur ;
        END IF ;            
    END 
GO 

CREATE TRIGGER PUB_MAG_EXCLUSION_INSERT BEFORE INSERT ON PUB_MAG 
FOR EACH ROW
    BEGIN
        SET @Kount = (SELECT COUNT(*) FROM PUB_CLI WHERE utilisateur_id = NEW.utilisateur_id) ;  
        IF @Kount > 0 THEN
            SET @Erreur = CONCAT('La publication pour le vendeur d''identifiant "', NEW.utilisateur_id, '" existe déjà en tant que publication d''un client. Rejet.') ; 
            SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @Erreur ;
        END IF ;            
    END 
GO 

CREATE TRIGGER PUB_CLI_EXCLUSION_INSERT BEFORE INSERT ON PUB_CLI 
FOR EACH ROW
    BEGIN
        SET @Kount = (SELECT COUNT(*) FROM PUB_MAG WHERE utilisateur_id = NEW.utilisateur_id) ;  
        IF @Kount > 0 THEN
            SET @Erreur = CONCAT('La publication pour le client d''identifiant "', NEW.utilisateur_id, '" existe déjà en tant que publication d''un vendeur. Rejet.') ; 
            SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @Erreur ;
        END IF ;            
    END 
GO 

DELIMITER ;

INSERT INTO UTILISATEUR (utilisateur_id, username, mail, password) VALUES
    (1, 'Tryphon Tournesol', 'tryphon@moulinsart.be', '********') 
  , (2, 'Fernand Naudin',    'fernand@citron.fr',     '********') 
  , (3, 'Archibald Haddock', 'haddock@moulinsart.be', '********') 
  , (4, 'Raoul Volfoni',     'raoul@pamplemousse.fr', '********') 
  , (5, 'Paul Volfoni',      'paulo@pamplemousse.fr', '********') 
  , (6, 'Antoine Delafoy',   'antoine@mandarine.fr',  '********') 
  , (7, 'Séraphin Lampion',  'seraphin@dvp.be',       '********') 
;
    
INSERT INTO VENDEUR (utilisateur_id) VALUES
    (2), (4), (5), (6) 
;
INSERT INTO MAGASIN (utilisateur_id, magasin_id, magasin_nom) VALUES
    (2, 1, 'Chez Fernand')
  , (2, 2, 'Le clapier')    
  , (2, 3, 'Au bon coin')
  , (2, 4, 'Chez Mimile')   
  , (4, 1, 'La Closerie')
  , (4, 2, 'Chez René')    
  , (4, 3, 'Vive la vie')
  , (5, 1, 'La cave à Irène')
  , (5, 2, 'Le Villon')    
  , (6, 1, 'Pique à seaux')
  , (6, 2, 'Atout pique')    
;  
INSERT INTO CLIENT (utilisateur_id) VALUES
    (1), (3), (7) 
;
INSERT INTO EVENEMENT (utilisateur_id, datum) VALUES
    (1, '2016-01-05')
  , (3, '2016-02-29')
  , (7, '2016-01-05')
;  

-- vérif exclusion CLIENT - VENDEUR

INSERT INTO  CLIENT (utilisateur_id) VALUES (2), (6) ; 
INSERT INTO  VENDEUR (utilisateur_id) VALUES (3), (7) ;

-- Publications

INSERT INTO PUBLICATION (utilisateur_id, publication_id, publication_date, publication_libelle) VALUES
    (1, 1, '1939-08-11', 'Gtande course de lions landais')
  , (1, 2, '1939-11-17', 'Etude en sous-sol mineur') 
  , (2, 1, '1938-07-15', 'Le centenaire de la brosse à reluire')  
  , (2, 2, '1938-07-22', 'Libérez les ballons captifs !')  
;

INSERT INTO PUB_CLI (utilisateur_id, publication_id) VALUES
    (1, 1), (1, 2)
;

INSERT INTO PUB_MAG (utilisateur_id, publication_id, magasin_id) VALUES
    (2, 1, 2), (2, 2, 1)
;

-- vérif exclusion PUB_CLI - PUB_MAG

INSERT INTO PUB_CLI (utilisateur_id, publication_id) VALUES (2, 1) ;
INSERT INTO PUB_MAG (utilisateur_id, publication_id, magasin_id) VALUES (1, 1, 2) ;

Selon les AGL, pour passer d’un MCD à un MLD (ou MPD ici), il suffit de quelques clics de souris ^^ Qu’en pensez-vous ?

Le MCD MCD-2 ne convient pas car on ne sait pas à quel magasin se réfère une publication.

Puis-je te demander pourquoi, car la table d'association reprend user_id et store_id ?

Code SQL :

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

1
2
3
4
5
6
7
CREATE TABLE post_vendor (
    post_id INT(11) NOT NULL,
    store_id INT(11) NOT NULL,
    user_id INT(11) NOT NULL,
    PRIMARY KEY (post_id),
    UNIQUE INDEX UNIQUE (store_id, user_id)
) ;

Le 1er problème est que l’entité-type VENDEUR ne peut pas être dotée de l’attribut store_id (un vendeur peut en effet avoir plus d’un magasin). Un 2e problème : la table post_vendor ne comporte aucune contrainte référentielle (clé étrangère) que ce soit, donc on peut y faire figurer n’importe quelles valeurs pour store_id et user_id.

[Rifton007]

Bonjour fsmrel,

En tout grand merci pour ces moments consacrés sur le forum.
On ne peu espérer mieux comme explication pour quelqu'un comme moi.

J'ai pris connaissance de ton post et j'essaie depuis hier de mettre tout ça en pratique.
Mais ça me prend plus de temps que prévu. Je fais au plus vite pour te répondre.

A trés bientot et bonne journée à toi,

[fsmrel]

Merci Rifton, et à bientôt !