[MCD] Schéma d'ameublement de maisons

[popy67]

Bonjour,

Dans mon problème un client achète une maison (association client_maison), une même maison contient toujours les mêmes chambres (relation maison_chambres), le nom des chambres ne change jamais
quelque soit la maison. Un client peut acheter un ou plusieurs objets (relation client_objet) et le placer une seule fois dans une chambre (relation ctot_ctmn).

Dans le modèle 1 que j'ai fait le nombre de tupple dans maison_chambres ne chane jamais; le client place les objets achetés (cient_objet) dans une maison via l'association ctot_CTMN puis
choisit la pièce dans lequel l'objet doit etre placé via le champ id_mncs (correspondant à l'id de relation entre maison et chambre).
est-ce bon ?


ou faut il mieux des qu'un client achete une maison créer autant d'enregistrement maison_chambres qu'il n'y a de pièceS dans chaque maison puis
faire le modèle 2, ainsi dès qu'un client achète un objet (client_objet) il le place dans l'association (ctot_mncs) ?

Merci d'avance pour votre aide, je m'embrouille un peu les pédales et ... Joyeux Noël ......

[fsmrel]

Bonsoir,


Pour vos schémas vous utilisez la notation Codasyl, pourquoi pas. Néanmoins on ne sait pas quels attributs participent aux clés des tables, les cardinalités portées par les liens sont absentes, etc. Pour évacuer les ambiguïtés il est préférable d’utiliser un outil de modélisation tel que MySQL Workbench (gratuit, voyez chez JogodePau un exemple d'utilisation de l'ouil).

Cela dit, une interprétation de vos schémas est la suivante :

— Un client peut acheter plusieurs maisons et la même maison peut être achetée par plusieurs clients : feriez-vous dans la copropriété ?
— Une maison peut comporter plusieurs chambres, c’est normal, mais une chambre peut faire partie de plusieurs maisons. Bizarre...
— Un client peut acheter plusieurs objets, pas de problème, mais le même objet peut être acheté par plusieurs clients. Hum...

Le caractère étrange de la situation est nettement amplifié dès qu’il s’agit de placer les objets...

Supposons que vous ne donniez pas dans la copropriété. La relation entre une maison et un client est alors la suivante (représentation MySQL Workbench) :



Ce qui se lit :

— Un client a acheté de 0 à N maisons (0,* se lit : 0,N).
— Une maison a pu être achetée.
— Une maison achetée est la possession exactement d’un client (au moins et au plus un).

A noter que la table MAISON_ACHETÉE hérite de la clé {MaisonId} de la table MAISON. Le losange rougeâtre signifie que {ClientId} est clé étrangère (en l’occurrence par référence à la table CLIENT).

Une chambre n’est jamais qu’une propriété multivaluée d’une maison, ce qui se modélise ainsi :



Ce qui se lit :

— Une maison comporte au moins une chambre (1,*).
— Une chambre appartient exactement à une maison (au moins et au plus une).

Par la suite, on peut produire le schéma suivant :


Où l’on observe qu’un objet ne peut être placé dans une chambre que si celle-ci fait partie d’une maison qui a été achetée.

Il faut par ailleurs s’assurer que cet objet est placé dans une maison qui a été achetée par le client qui a acheté l’objet, sinon il risque d’y avoir de la bagarre. On verra un peu plus tard comment mettre cette contrainte en œuvre.


Joyeux Noël à vous aussi !

[fsmrel]

Bonsoir,


A propos de la contrainte :

Un client ne peut pas placer ses propres objets chez les autres clients.

Au niveau SQL il faudra en passer par un trigger pour les inserts (et les updates).

A noter que j’ai ajouté une table TYPE_OBJET dans le schéma :



Au stade SQL (avec SQL Server) :

Création des tables

Code SQL :

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
68
69
CREATE TABLE CLIENT
(
        ClientNom         VARCHAR(64)   NOT NULL
      , ClientId          INT           NOT NULL
      , CONSTRAINT CLIENT_PK PRIMARY KEY (ClientId)
) 
;
CREATE TABLE MAISON
(
        MaisonId          INT          NOT NULL
      , MaisonNom         VARCHAr(64)  NOT NULL
    ,  CONSTRAINT MAISON_PK PRIMARY KEY (MaisonId)
) ;
CREATE TABLE CHAMBRE
(
        MaisonId        INT             NOT NULL
      , ChambreId       INT             NOT NULL
      , ChambreNom      VARCHAR(64)     NOT NULL
    , PRIMARY KEY (MaisonId, ChambreId)
    , CONSTRAINT CHAMBRE_MAISON_FK FOREIGN KEY (MaisonId) 
          REFERENCES MAISON (MaisonId)
) ;
CREATE TABLE MAISON_ACHETÉE 
(
        MaisonId        INT             NOT NULL
      , ClientId        INT             NOT NULL
    , CONSTRAINT MAISON_ACHETÉE_PK PRIMARY KEY (MaisonId)
    , CONSTRAINT MAISON_CLIENT_FK FOREIGN KEY (ClientId)
           REFERENCES CLIENT (ClientId)
    , CONSTRAINT MAISON_ACHETÉE_MAISON_FK FOREIGN KEY (MaisonId)
           REFERENCES MAISON (MaisonId)
) ;
CREATE TABLE TYPE_OBJET 
(
        TypeObjetId     INT             NOT NULL
      , ObjetNom        VARCHAR(64)     NOT NULL
    , CONSTRAINT TYPE_OBJET_PK PRIMARY KEY (TypeObjetId)
) ;
CREATE TABLE OBJET 
(
        ObjetId         INT             NOT NULL
      , TypeObjetId     INT             NOT NULL
      , CONSTRAINT OBJET_PK PRIMARY KEY (ObjetId)
      , CONSTRAINT TYPE_OBJET_FK FOREIGN KEY (TypeObjetId)
           REFERENCES TYPE_OBJET (TypeObjetId)
) ;
CREATE TABLE OBJET_ACHETÉ
(
        ObjetId         INT             NOT NULL
      , ClientId        INT             NOT NULL
      , CONSTRAINT OBJET_ACHETÉ_PK PRIMARY KEY (ObjetId)
      , CONSTRAINT OBJET_ACHETÉ_OBJET_FK FOREIGN KEY (ObjetId)
           REFERENCES OBJET (ObjetId)
      , CONSTRAINT OBJET_ACHETÉ_CLIENT_FK FOREIGN KEY (ClientId)
            REFERENCES CLIENT (ClientId)
) ;
CREATE TABLE OBJET_PLACÉ 
(
        ObjetId         INT             NOT NULL
      , MaisonId        INT             NOT NULL
      , ChambreId       INT             NOT NULL
    , CONSTRAINT OBJET_PLACÉ_PK PRIMARY KEY (ObjetId)
    , CONSTRAINT OBJET_PLACÉ_OBJET_ACHETÉ_FK FOREIGN KEY (ObjetId)
          REFERENCES OBJET_ACHETÉ (ObjetId)
    , CONSTRAINT OBJET_PLACÉ_CHAMBRE_FK FOREIGN KEY (MaisonId, ChambreId)
          REFERENCES CHAMBRE (MaisonId, ChambreId)
    , CONSTRAINT OBJET_PLACÉ_MAISON_ACHETÉE_FK FOREIGN KEY (MaisonId) 
          REFERENCES MAISON_ACHETÉE (MaisonId)
) ;

Le trigger mermettant de s’assurer que Raoul ne place pas ses objets (explosifs) chez Fernand :

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TRIGGER R_TRIGGER_INSERT ON OBJET_PLACÉ INSTEAD OF INSERT, UPDATE AS
DECLARE @N AS INT, @Err AS VARCHAR(254)
 
SET @N = (SELECT COUNT(*) 
          FROM INSERTED AS u JOIN OBJET_ACHETÉ AS x ON x.ObjetId = u.ObjetId
                             JOIN MAISON_ACHETÉE AS s ON u.MaisonId = s.MaisonId 
                                                      AND x.ClientId = s.ClientId) ;
IF @N > 0 
    BEGIN
        INSERT INTO OBJET_PLACÉ SELECT * FROM INSERTED
    END
ELSE
    BEGIN
        SET @Err = 'OBJET_PLACÉ - Insert refusé. Un client ne peut pas placer ses objets chez les autres.'
        SELECT '' AS 'OBJET_PLACÉ - Insert refusé.', * FROM INSERTED
        RAISERROR (@Err, 10, 1) 
        RETURN 
    END
GO

Un début de jeu d’essai :

Code SQL :

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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
INSERT INTO CLIENT (ClientId, ClientNom) VALUES (1, 'Fernand') ;
INSERT INTO CLIENT (ClientId, ClientNom) VALUES (2, 'Raoul') ;
INSERT INTO CLIENT (ClientId, ClientNom) VALUES (3, 'Paul') ;
 
INSERT INTO MAISON (MaisonId, MaisonNom) VALUES (1, 'Vert Cottage') ;
INSERT INTO MAISON (MaisonId, MaisonNom) VALUES (2, 'El Pilariguet') ;
INSERT INTO MAISON (MaisonId, MaisonNom) VALUES (3, 'Mon repos') ;
INSERT INTO MAISON (MaisonId, MaisonNom) VALUES (91, 'Les volets rouges') ;
 
INSERT INTO CHAMBRE (MaisonId, ChambreId, ChambreNom) VALUES (1, 1, 'cuisine') ;
INSERT INTO CHAMBRE (MaisonId, ChambreId, ChambreNom) VALUES (1, 2, 'chambre 1') ;
INSERT INTO CHAMBRE (MaisonId, ChambreId, ChambreNom) VALUES (1, 3, 'chambre 2') ;
INSERT INTO CHAMBRE (MaisonId, ChambreId, ChambreNom) VALUES (1, 4, 'salon') ;
 
INSERT INTO CHAMBRE (MaisonId, ChambreId, ChambreNom) VALUES (2, 1, 'salon') ;
INSERT INTO CHAMBRE (MaisonId, ChambreId, ChambreNom) VALUES (2, 2, 'chambre 1') ;
INSERT INTO CHAMBRE (MaisonId, ChambreId, ChambreNom) VALUES (2, 3, 'chambre 2') ;
INSERT INTO CHAMBRE (MaisonId, ChambreId, ChambreNom) VALUES (2, 4, 'chambre 3') ;
INSERT INTO CHAMBRE (MaisonId, ChambreId, ChambreNom) VALUES (2, 5, 'cuisine') ;
 
INSERT INTO CHAMBRE (MaisonId, ChambreId, ChambreNom) VALUES (3, 1, 'bureau') ;
INSERT INTO CHAMBRE (MaisonId, ChambreId, ChambreNom) VALUES (3, 2, 'chambre 1') ;
INSERT INTO CHAMBRE (MaisonId, ChambreId, ChambreNom) VALUES (3, 3, 'chambre 2') ;
INSERT INTO CHAMBRE (MaisonId, ChambreId, ChambreNom) VALUES (3, 4, 'buanderie') ;
 
INSERT INTO CHAMBRE (MaisonId, ChambreId, ChambreNom) VALUES (91, 1, 'cuisine') ;
INSERT INTO CHAMBRE (MaisonId, ChambreId, ChambreNom) VALUES (91, 2, 'chambre 1') ;
INSERT INTO CHAMBRE (MaisonId, ChambreId, ChambreNom) VALUES (91, 3, 'chambre 2') ;
INSERT INTO CHAMBRE (MaisonId, ChambreId, ChambreNom) VALUES (91, 4, 'salon') ;
 
INSERT INTO MAISON_ACHETÉE (MaisonId, ClientId) VALUES (1, 1) ; -- Vert Cottage, Fernand 
INSERT INTO MAISON_ACHETÉE (MaisonId, ClientId) VALUES (3, 1) ; -- Mon repos, Fernand 
INSERT INTO MAISON_ACHETÉE (MaisonId, ClientId) VALUES (2, 2) ; --Pilariguet Raoul 
 
INSERT INTO TYPE_OBJET (TypeObjetId, ObjetNom) VALUES (1, 'bar') ;
INSERT INTO TYPE_OBJET (TypeObjetId, ObjetNom) VALUES (2, 'fauteuil') ;
INSERT INTO TYPE_OBJET (TypeObjetId, ObjetNom) VALUES (3, 'lit') ;
INSERT INTO TYPE_OBJET (TypeObjetId, ObjetNom) VALUES (4, 'table') ;
INSERT INTO TYPE_OBJET (TypeObjetId, ObjetNom) VALUES (5, 'frigo') ;
INSERT INTO TYPE_OBJET (TypeObjetId, ObjetNom) VALUES (6, 'placard') ;
INSERT INTO TYPE_OBJET (TypeObjetId, ObjetNom) VALUES (7, 'tapis') ;
INSERT INTO TYPE_OBJET (TypeObjetId, ObjetNom) VALUES (8, 'chaise') ;
 
INSERT INTO OBJET (ObjetId, TypeObjetId) VALUES (1, 1) ; -- bar 
INSERT INTO OBJET (ObjetId, TypeObjetId) VALUES (2, 1) ; -- bar
INSERT INTO OBJET (ObjetId, TypeObjetId) VALUES (3, 1) ; -- bar
INSERT INTO OBJET (ObjetId, TypeObjetId) VALUES (45, 1) ; -- bar
INSERT INTO OBJET (ObjetId, TypeObjetId) VALUES (4, 2) ; -- fauteuil
INSERT INTO OBJET (ObjetId, TypeObjetId) VALUES (5, 2) ; -- fauteuil
INSERT INTO OBJET (ObjetId, TypeObjetId) VALUES (6, 2) ; -- fauteuil
INSERT INTO OBJET (ObjetId, TypeObjetId) VALUES (7, 2) ; -- fauteuil
INSERT INTO OBJET (ObjetId, TypeObjetId) VALUES (8, 3) ; -- lit
INSERT INTO OBJET (ObjetId, TypeObjetId) VALUES (9, 3) ; -- lit
INSERT INTO OBJET (ObjetId, TypeObjetId) VALUES (10, 3) ; -- lit
INSERT INTO OBJET (ObjetId, TypeObjetId) VALUES (11, 3) ; -- lit
INSERT INTO OBJET (ObjetId, TypeObjetId) VALUES (15, 4) ; -- table
INSERT INTO OBJET (ObjetId, TypeObjetId) VALUES (16, 4) ; -- table
INSERT INTO OBJET (ObjetId, TypeObjetId) VALUES (17, 4) ; -- table
INSERT INTO OBJET (ObjetId, TypeObjetId) VALUES (18, 4) ; -- table
INSERT INTO OBJET (ObjetId, TypeObjetId) VALUES (20, 5) ; -- frigo
INSERT INTO OBJET (ObjetId, TypeObjetId) VALUES (21, 5) ; -- frigo
INSERT INTO OBJET (ObjetId, TypeObjetId) VALUES (25, 6) ; -- placard
INSERT INTO OBJET (ObjetId, TypeObjetId) VALUES (26, 6) ; -- placard
INSERT INTO OBJET (ObjetId, TypeObjetId) VALUES (30, 7) ; -- tapis
INSERT INTO OBJET (ObjetId, TypeObjetId) VALUES (40, 8) ; -- chaise
INSERT INTO OBJET (ObjetId, TypeObjetId) VALUES (41, 8) ; -- chaise
INSERT INTO OBJET (ObjetId, TypeObjetId) VALUES (42, 8) ; -- chaise
INSERT INTO OBJET (ObjetId, TypeObjetId) VALUES (43, 8) ; -- chaise
 
INSERT INTO OBJET_ACHETÉ (ObjetId, ClientId) VALUES (1, 1) ; -- bar, Fernand
INSERT INTO OBJET_ACHETÉ (ObjetId, ClientId) VALUES (2, 2) ; -- bar, Raoul
INSERT INTO OBJET_ACHETÉ (ObjetId, ClientId) VALUES (3, 1) ; -- bar, Fernand
INSERT INTO OBJET_ACHETÉ (ObjetId, ClientId) VALUES (4, 1) ; -- bar, Fernand
INSERT INTO OBJET_ACHETÉ (ObjetId, ClientId) VALUES (40, 1) ; -- chaise, Fernand
INSERT INTO OBJET_ACHETÉ (ObjetId, ClientId) VALUES (41, 1) ; -- chaise, Fernand
INSERT INTO OBJET_ACHETÉ (ObjetId, ClientId) VALUES (42, 1) ; -- chaise, Fernand
INSERT INTO OBJET_ACHETÉ (ObjetId, ClientId) VALUES (43, 2) ; -- chaise, Raoul
INSERT INTO OBJET_ACHETÉ (ObjetId, ClientId) VALUES (20, 2) ; -- frigo, Raoul
INSERT INTO OBJET_ACHETÉ (ObjetId, ClientId) VALUES (30, 2) ; -- tapis, Raoul
INSERT INTO OBJET_ACHETÉ (ObjetId, ClientId) VALUES (25, 3) ; -- placard, Paul
 
INSERT INTO OBJET_PLACÉ (ObjetId, MaisonId, ChambreId) VALUES (3, 1, 1) ; -- bar, Vert Cottage, salon (Fernand)
 
-- L'insert suivant est refusé, car la maison "Les volets rouges" n'a pas éré achetée.
INSERT INTO OBJET_PLACÉ (ObjetId, MaisonId, ChambreId) VALUES (45, 91, 1) ; -- bar, volets rouges 
 
-- L'insert suivant est refusé, car on veut placer un objet acheté par Fernand dans une chambre 
-- de la maison "El Pilariguet" qui n'a pas été achetée par Fernand, mais par Raoul.
INSERT INTO OBJET_PLACÉ (ObjetId, MaisonId, ChambreId) VALUES (1, 2, 1) ; -- bar, Pilariguet, salon (Fernand vs Raoul) 
 
SELECT '' AS 'OK', x.ObjetId, ClientNom, MaisonNom, ChambreNom, ObjetNom
FROM   OBJET_ACHETÉ AS x 
           JOIN OBJET AS y ON x.ObjetId = y.ObjetId
           JOIN TYPE_OBJET AS z ON y.TypeObjetId = z.TypeObjetId
           JOIN CLIENT AS t ON x.ClientId = t.ClientId
           JOIN OBJET_PLACÉ AS u ON x.ObjetId = u.ObjetId
           JOIN CHAMBRE AS v ON u.MaisonId = v.MaisonId AND u.ChambreId = v.ChambreId   
           JOIN MAISON AS w ON v.MaisonId = w.MaisonId
 
DELETE FROM OBJET_PLACÉ WHERE ObjetId = 1 ;
 
-- Fernand vs Raoul, l'update doit être rejeté :
 
UPDATE OBJET_PLACÉ 
    SET ObjetId = 1, MaisonId = 2 WHERE ObjetId = 3 
 
SELECT '' AS 'OK', x.ObjetId, ClientNom, MaisonNom, ChambreNom, ObjetNom
FROM   OBJET_ACHETÉ AS x 
           JOIN OBJET AS y ON x.ObjetId = y.ObjetId
           JOIN TYPE_OBJET AS z ON y.TypeObjetId = z.TypeObjetId
           JOIN CLIENT AS t ON x.ClientId = t.ClientId
           JOIN OBJET_PLACÉ AS u ON x.ObjetId = u.ObjetId
           JOIN CHAMBRE AS v ON u.MaisonId = v.MaisonId AND u.ChambreId = v.ChambreId   
           JOIN MAISON AS w ON v.MaisonId = w.MaisonId ;

A vous de jouer.

[popy67]

Bonjour,

Merci beaucoup pour votre réponse.

Envoyé par fsmrel

Pour vos schémas vous utilisez la notation Codasyl, pourquoi pas. Néanmoins on ne sait pas quels attributs participent aux clés des tables, les cardinalités portées par les liens sont absentes, etc. Pour évacuer les ambiguïtés il est préférable d’utiliser un outil de modélisation tel que MySQL Workbench

Merci du conseil, en effet, j'ai l'habitude d'utiliser la vieille méthode du papier crayon pour mes schémas et je n'ai pour le moment jamais utilisé d'outil de modélisation, j'ai très rapidement "gribouillé" un bricabrac avec le bon vieux Word... Merci du tuyau en tout cas je vais jeter un coup oeil à ce logiciel !

Envoyé par fsmrel

une interprétation de vos schémas est la suivante :
— Un client peut acheter plusieurs maisons et la même maison peut être achetée par plusieurs clients : feriez-vous dans la copropriété ?
— Une maison peut comporter plusieurs chambres, c’est normal, mais une chambre peut faire partie de plusieurs maisons. Bizarre...

Oui mais non En fait il s'agit d'un logiciel d'architecture, les maisons sont donc fictives et préfabriquées, un exemple :

- La maison "A" comporte 5 pièces parmi lesquelles un salon, une cuisine, une salle de bain, des toilettes et une chambre à coucher.
- La maison "B" comporte toujours 6 pièces, elles sont identiques à la maison A et comporta en plus une seconde salle de bain.
- La maison "C" comporte toujours 8 pièces, elles sont identiques à "B" mais avec en plus une salle de gym et un sauna.

J'ai dans mon exemple créer l'association "maison_chambre" car je souhaite que la table "chambre" comporte une description de chaque pièce (Description type : le salon peut accueillir une télé, un canapé, etc...) pour éviter qu'une personne ne place par exemple une lunette de WC au milieu du salon même si l'action ne serait pas interdite par le logiciel. La pièce "salon" de la maison A peut donc être la même que dans la maison B étant donné que la description ne change pas ... voyez-vous ??

Pour la suite je vais prendre votre schéma car je le trouve bien plus clair ^^
Vous créez une association entre objet_acheté->objet_placé<-chambre

Sachant qu'un objet une fois acheté est toujours placé dans une et une seule chambre et que une chambre peut comporter 0 ou n objets on obtient les cardinalités suivantes objet_acheté(1,1)->objet_placé<-(0,n)chambre; ainsi l'association objet_placé ne peut-elle pas être supprimée au profit de : objet_achete(pk_objet, fk_ObjetId, fk_ClientId, fk_MaisonId, fk_ChambreId) ?

Qu'en pensez-vous ?

[fsmrel]

Bonsoir Popy,


Si je comprends bien, on ne fait pas dans le sur-mesure : plutôt que décrire des pièces personnalisées comme je l’ai fait, on a des pièces-types (salon, chambre à coucher, etc.) peuplées d’objets-types (télé, canapé, etc.). A partir de là on compose l’organisation-type d’une maison à partir des pièces-types ; c’est cela ? Si oui, cela conduit à une modélisation différente de celle que j’ai proposée : les pièces d’une maison n’en sont pas des propriétés multivaluées, mais il y a association entre maison et pièce (table COMPOSITION_MAISON), même principe pour les objets-types entrant dans la composition d’une pièce-type (table COMPOSITION_PIÈCE).

Une proposition (aménageable !) de représentation (dans laquelle j’ai renommé CHAMBRE en PIÈCE) :