Discussion :

[fsmrel]

Bonsoir,


J’ai oublié de répondre au sujet des clés primaires, c’est peut-être un peu tard, mais bon...

Lorsqu'on ajoute une clé primaire sur chacun des champs d'une table de jonction, est-ce que ça permet bien de gérer une relation de plusieurs à plusieurs? Est ce qu'il faut bien supprimer le caractère d'unicité des champs?

Je ne suis pas sûr de comprendre exactement le sens de votre question. Je vais donc traiter des clés et pour illustrer je me servirai de la table SOCIETE_CLIENTE.

Une clé primaire est un ensemble (au sens de la théorie des ensembles). Dans le cas de la table SOCIETE_CLIENTE, cet ensemble K a pour éléments les noms des attributs de la table faisant que les deux contraintes suivantes sont garanties :

1. Unicité. Deux lignes distinctes de la table ne peuvent avoir simultanément la même valeur pour K.

2. Irréductibilité. Il n’existe pas de sous-ensemble strict K´ de K garantissant lui aussi la contrainte d’unicité.

Supposons que les clients soient les suivants :

Ets Naudin
Volfoni Frères
...

La structure de votre table SOCIETE_CLIENTE est la suivante :

SOCIETE_CLIENTE {SocieteId, Nom, SIREN}

Son extension (contenu) est aussi un ensemble (de lignes) :

SocieteId    Nom                SIREN
---------    ---------------    --------------
        1    Ets Naudin         123456782
        2    Volfoni Frères     234567899
      ...    ...                ... 

Il faut interdire la présence de doublons (les doublons n’ont pas de sens dans un ensemble) tels qu’ici :

SocieteId    Nom                SIREN
---------    ---------------    --------------
        1    Ets Naudin         123456782
        1    Ets Naudin         123456782
        2    Volfoni Frères     234567899
      ...    ... 

Apprendre deux fois que les établissements Naudin ont 123456782 pour numéro de SIREN et 1 pour SocieteId n’apporte vraiment rien, sinon un dysfonctionnement de l’algèbre relationnelle...

C’est pour cela qu’on déclare une clé primaire (voire des clés alternatives). Supposons que l’on retienne pour clé primaire le triplet :

{SocieteId, Nom, SIREN}

Alors le SGBD refusera la 2e ligne <1, Ets Naudin, 123456782> et la situation restera la suivante :

SocieteId    Nom                SIREN
---------    ---------------    --------------
        1    Ets Naudin         123456782
        2    Volfoni Frères     234567899
      ...    ...                ... 

Tant mieux pour l’algèbre... Néanmoins, à SocieteId = 1 ne doit correspondre qu’un client, mais vu sa constitution (triplet {SocieteId, Nom, SIREN}), la clé n’interdit pas la situation suivante :

SocieteId    Nom                SIREN
---------    ---------------    --------------
        1    Ets Naudin         123456782
        1    Duchesne & Cie     987654324
        2    Volfoni Frères     234567899
      ...    ...                ... 

En effet, à SocieteId = 1 correspondent manifestement deux noms (et deux SIREN).

On dit que la clé est réductible. Le triplet {SocieteId, Nom, SIREN} doit en fait être remplacé par le singleton {SocieteId} (notez en passant l’utilisation des accolades, puisque les clés sont des ensembles). Dans ces conditions, quand l’attribut SocieteId prend la valeur 1, un seul nom et un seul SIREN sont possibles :

SocieteId    Nom                SIREN
---------    ---------------    --------------
        1    Ets Naudin         123456782
        2    Volfoni Frères     234567899
      ...    ...                ...

Il est évident que les paires {SocieteId, Nom} et {SocieteId, SIREN} ne constituent pas des clés, puisque réductibles à {SocieteId}.

La paire {Nom, SIREN} représente un cas intéressant. Peut-elle constituer une clé ? En fait, comme deux sociétés (personnes juridiques) ne peuvent avoir le même SIREN (SIRET si on parlait des établissements), le singleton {SIREN} constitue une clé à lui seul. Pour distinguer celle-ci de la clé primaire, on dit qu’il s’agit d’une clé alternative (clause UNIQUE en SQL).

Déclaration en SQL :

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TABLE SOCIETE_CLIENTE
(
        SocieteId    INT            NOT NULL
      , Nom          VARCHAR(64)    NOT NULL
      , SIREN        INT            NOT NULL
    , CONSTRAINT SOCIETE_CLIENT_PK PRIMARY KEY (SocieteId)
    , CONSTRAINT SOCIETE_CLIENT_AK UNIQUE (SIREN)
) ;

Des sociétés différentes peuvent généralement avoir même nom, mais si cela ne devait pas être le cas, alors l’attribut Nom ferait lui aussi l’objet d’une clé alternative (clause UNIQUE).


Venons-en à la table de jonction EQUIPE_CLIENT (on utilise plutôt le terme jointure, mais jonction est tout à fait acceptable).

Partons de la situation suivante :

Table SOCIETE_CLIENTE (clé primaire {SocieteId})

SocieteId    Nom                SIREN
---------    ---------------    --------------
        1    Ets Naudin         123456782
        2    Volfoni Frères     234567899
      ...    ...                ...

Table PROJET (clé primaire {ProjetId})

ProjetId    Description          SocieteId    ...
--------    -----------------    ---------    ---
       1    Contrats                     1    ...
       2    Appros                       1    ...
       3    Commandes                    1    ...
       4    En-cours                     2    ...
       5    Commandes                    2    ...
       6    Facturation                  2    ...
       7    Catalogue Produits           2    ...

Table CONTACT_CLIENT (clé primaire {ContactId})

ContactId    Nom         SocieteId    ...
---------    --------    ---------    ---
        1    Fernand             1    ...
        2    Jean                1    ...
        3    Raoul               2    ...
        4    Paul                2    ...
        5    Pascal              2    ...
        6    Bastien             2    ...

L’en-tête (liste des attributs) de la table EQUIPE_CLIENT est constitué de la paire de noms d’attributs (quand je change les noms, ne vous sentez pas obligé d’en faire autant !) :

{ProjetId, ContactId}

Je rappelle qu’il s’agit d’un ensemble (d’où les accolades).

Cette paire a-t-elle les propriétés d’une clé primaire ? Comme à un projet peuvent participer plusieurs contacts et qu’un contact peut participer à plusieurs projets, si l'on retient comme clé primaire la paire {ProjetId, ContactId}, la situation suivante est tout à fait légale et légitime, il n’y a pas de doublons :

ProjetId    ContactId
--------    ---------
       1            1             (Fernand participe au projet Contrats des établissements Naudin dont il fait partie)
       3            1             (Fernand participe au projet Commandes des établissements Naudin dont il fait partie)
       2            2             (Jean participe au projet Appros des établissements Naudin dont il fait partie)
       3            2             (Jean participe au projet Commandes des établissements Naudin dont il fait partie)

Du fait de la présence de la clé, les doublons {ProjetId, ContactId} sont impossibles : la clé garantit la propriété d’unicité. Mais qu'en est-il de l'irréductibilité ?

Prenons le cas du singleton {ProjetId}. S’il constituait une clé, alors la ligne <3, 2> serait illégale, le projet 3 étant déjà présent (ligne <3, 1>) ; cela reviendrait à dire qu’à un projet donné n’aurait droit de participer qu’un seul contact, ce qui est évidemment contraire à ce que dit votre thésaurus de règles de gestion.

De la même façon si le singleton {ContactId} était déclaré comme clé primaire, cela reviendrait à dire qu’un contact donné ne pourrait participer qu’à un seul projet, ce qui une fois de plus serait contraire à ce que dit votre thésaurus de règles de gestion.

=> La clé de la table EQUIPE_CLIENT est bien la paire {ProjetId, ContactId}.

Je ne sais pas si j’ai répondu à votre question, n’hésitez pas à bombarder si des points restent obsurs...

Je reviendrai sur l'utilisation de l'identification relative, mais à toutes fins utiles j'en ai déjà parlé ici, et de son rôle pour éviter les erreurs d'affectation des contacts aux projets.


J’envoie déjà ce message, je vais regarder la suite.

[fsmrel]

J'ai rajouté les attributs Identifiant et Mot_de_Passe à la table Ressource mais je ne comprends pas pourquoi, SQLServer met cet attribut entre crochet.

Je viens seulement de voir votre message. Je réponds rapidement : Des noms d'attributs (et des noms en général) en plusieurs mots, SQL n'aime pas trop. A son tour, SQL Server pallie avec les crochets.

Rien ne vous empêche de remplacer « Mot_de passe » par « Mot_de_Passe » dans la description de la table (comme vous l'avez du reste orthographié dans votre message...)

A propos de l'attribut « Identifiant », je suppose qu'il s'agit de l'identifiant de connexion ?

[fsmrel]

je souhaite pouvoir gérer l'historique (par date) du statut du projet mais je ne sais pas comment le faire?

A la liste des attributs de la table PROJET vous ajoutez un attribut Statut_Projet_Depuis qui est de type date, ainsi l’attribut Id_Statut_Projet correspond au statut actuel du projet P.

Pour les statuts précédents du projet P, vous définissez une table historique permettant de savoir que pendant telle période le projet a eu tel statut. Comme MS SQL Server ne propose pas le type Période (en tout cas je ne l’ai pas vu), on va décomposer la période en date de début et date de fin :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE PROJET_STATUT_HISTO
(
        ID_Projet                INT         NOT NULL
      , Statut_Projet_Debut      DATE        NOT NULL
      , Statut_Projet_Fin        DATE        NOT NULL
      , Id_Statut_Projet         INT         NOT NULL
    , CONSTRAINT PROJET_STATUT_HISTO_PK PRIMARY KEY (ID_Projet, Statut_Projet_Debut)
    , CONSTRAINT PROJET_STATUT_HISTO_FK1 FOREIGN KEY (ID_Projet) REFERENCES PROJET
    , CONSTRAINT PROJET_STATUT_HISTO_FK2 FOREIGN KEY (Id_Statut_Projet) REFERENCES STATUT_PROJET
) ;

Notez la clé primaire...

On pourra éventuellement parler des contraintes concernant la date de fin.

[fsmrel]

Bonsoir Jim,


Je reviens sur le thème de l’identification relative. A ce sujet, vous pouvez aussi voir l’exemple de la motorisation et la finition des modèles de voitures.

Comme vous le faites actuellement, une possibilité est dans un premier temps d’utiliser l’identification absolue pour les projets, les contacts et les équipes des clients, selon laquelle la table PROJET a pour clé {Id_Projet}, la table CONTACT_CLIENT a pour clé {Id_Contact_Client} et la table EQUIPE_CLIENT a pour clé la paire {Id_Contact_Client, Id_Projet} :





Je reprends les scripts de déclaration des tables.

Table SOCIETE_CLIENT :

Code SQL :

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

1
2
3
4
5
6
CREATE TABLE SOCIETE_CLIENT
(
        Id_Societe_Client        INT          NOT NULL
      , Nom                      VARCHAR(48)  NOT NULL
    , CONSTRAINT SOCIETE_CLIENT_PK PRIMARY KEY (Id_Societe_Client)
) ;

Table PROJET :

Code SQL :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE PROJET
(
        Id_Societe_Client        INT          NOT NULL
      , Id_Projet                INT          NOT NULL
      , Description              VARCHAR(48)  NOT NULL
    , CONSTRAINT PROJET_PK PRIMARY KEY (Id_Projet)
    , CONSTRAINT PROJET_CLIENT_FK FOREIGN KEY (Id_Societe_Client) 
          REFERENCES SOCIETE_CLIENT
) ;

Table CONTACT_CLIENT :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE CONTACT_CLIENT
(
        Id_Societe_Client        INT          NOT NULL
      , Id_Contact_Client        INT          NOT NULL
      , Nom                      VARCHAR(48)  NOT NULL 
      , Prenom                   VARCHAR(48)  NOT NULL
    , CONSTRAINT CONTACT_CLIENT_PK PRIMARY KEY (Id_Contact_Client)
    , CONSTRAINT CONTACT_CLIENT_FK FOREIGN KEY (Id_Societe_Client) 
          REFERENCES SOCIETE_CLIENT
) ;

Table EQUIPE_CLIENT :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE EQUIPE_CLIENT
(
        Id_Contact_Client        INT          NOT NULL
      , Id_Projet                INT          NOT NULL
    , CONSTRAINT JONCTION_PK PRIMARY KEY (Id_Contact_Client, Id_Projet)
    , CONSTRAINT JONCTION_CONTACT_FK FOREIGN KEY (Id_Contact_Client) 
          REFERENCES CONTACT_CLIENT
    , CONSTRAINT JONCTION_PROJET_FK FOREIGN KEY (Id_Projet) 
          REFERENCES PROJET ON DELETE CASCADE
) ;

Mettons du contenu :

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
INSERT INTO SOCIETE_CLIENT (Id_Societe_Client, Nom) VALUES (1, 'Etablissements Naudin') ;
INSERT INTO SOCIETE_CLIENT (Id_Societe_Client, Nom) VALUES (2, 'Volfoni Frères') ;
 
INSERT INTO PROJET (Id_Societe_Client, Id_Projet, Description) VALUES (1, 1, 'projet Biglotron') ;
INSERT INTO PROJET (Id_Societe_Client, Id_Projet, Description) VALUES (1, 2, 'projet Pastis') ;
INSERT INTO PROJET (Id_Societe_Client, Id_Projet, Description) VALUES (2, 3, 'Apollo') ;
 
INSERT INTO CONTACT_CLIENT (Id_Societe_Client, Id_Contact_Client, Nom, Prenom) VALUES (1, 1, 'Trancène', 'Jean') ;
INSERT INTO CONTACT_CLIENT (Id_Societe_Client, Id_Contact_Client, Nom, Prenom) VALUES (1, 2, 'Quatre', 'Henri') ;
INSERT INTO CONTACT_CLIENT (Id_Societe_Client, Id_Contact_Client, Nom, Prenom) VALUES (2, 3, 'Paraboum', 'Pascal') ;
INSERT INTO CONTACT_CLIENT (Id_Societe_Client, Id_Contact_Client, Nom, Prenom) VALUES (2, 4, 'Piouf', 'Bastien') ;
 
INSERT INTO EQUIPE_CLIENT (Id_Projet, Id_Contact_Client) VALUES (1, 1) ;
INSERT INTO EQUIPE_CLIENT (Id_Projet, Id_Contact_Client) VALUES (2, 1) ;
INSERT INTO EQUIPE_CLIENT (Id_Projet, Id_Contact_Client) VALUES (3, 1) ;
INSERT INTO EQUIPE_CLIENT (Id_Projet, Id_Contact_Client) VALUES (3, 3) ;

On ne peut que constater qu’il est facile d’affecter Jean au projet Apollo de la société Volfoni Frères, alors que Jean fait partie des Etablissements Naudin. Une requête pour constater les dégâts :

Code SQL :

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

1
2
3
4
5
6
7
SELECT u.Nom, t.Description, t.Prenom, t.Nom
FROM   SOCIETE_CLIENT AS u 
       JOIN
          (SELECT y.Id_Societe_Client AS Id_Societe_Client, y.Description, z.Prenom, z.Nom    
           FROM   EQUIPE_CLIENT AS x JOIN PROJET AS y ON x.Id_Projet = y.Id_Projet
                                     JOIN CONTACT_CLIENT AS z ON x.Id_Contact_Client = z.Id_Contact_Client) AS t
       ON t.Id_Societe_Client = u.Id_Societe_Client ;

=>

Nom                      Description         Prenom    Nom
---------------------    ----------------    --------  ----------
Etablissements Naudin    projet Biglotron    Jean      Trancène
Etablissements Naudin    projet Pastis       Jean      Trancène
Volfoni Frères           Apollo              Jean      Trancène
Volfoni Frères           Apollo              Pascal    Paraboum 

C’est un bug classique, conséquence de l’existence de chemins collatéraux (conterminous paths) permettant d’aller de A à Z en passant par B ou par C (aller de SOCIETE_CLIENT à EQUIPE_CLIENT via PROJET ou CONTACT_CLIENT). Un moyen d’empêcher Jean de participer à un projet qui ne le concerne pas est de déclarer une contrainte, ce qui passe par la mise en œuvre de triggers en SQL Server (jusqu’au jour où l’on disposera de quelque chose de plus adéquat), pour contrôler les ajouts et les modifications dans la table EQUIPE_CLIENT.

Maintenant, on pourrait faire observer que Jean est un consultant indépendant qui a offert ses services aux établissements Naudin et à Volfoni Frères, et sa participation a priori aux deux projets ne serait donc pas anormale. Néanmoins les apparences sont trompeuses, car selon le contenu de la table CONTACT_CLIENT Jean fait seulement partie des établissements Naudin. Si dans les faits Jean participe aussi — en toute légalité — au projet Apollo pour le compte de Volfoni Frères, il devra figurer deux fois dans la table CONTACT_CLIENT, une fois par société cliente, ce qui évidemment fera qu’on devra « saisir » deux fois son nom, son prénom, ses numéros de téléphone, etc. On verra plus tard qu’on pourra éviter cette double saisie grâce à la généralisation.

Pour éviter d’avoir à programmer des triggers (ou quand même bien plus confortablement des assertions dans le cas de la norme SQL), et comme j’en ai déjà fait mention, on peut utiliser l’identification relative. Le but est que l’attribut Id_Societe_Client soit répercuté jusqu’à la table EQUIPE_CLIENT les relations de clé primaire à clé étrangère étant les vecteurs de propagation :



Dans ces conditions, pour un projet et un contact (table EQUIPE_CLIENT) l’attribut Id_Societe_Client prend de facto la même valeur. Vous noterez que la numérotation recommence à 1 dans le cas de l’attribut Id_Projet de la table PROJET quand on passe d’une société à l’autre (cf. projet Apollo), même chose en ce qui concerne l’attribut Id_Contact_Client de la table CONTACT_CLIENT). Les clés primaires sont enrichies :

Table PROJET (clé primaire : {Id_Societe_Client, Id_Projet}) :

Code SQL :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE PROJET
(
        Id_Societe_Client        INT          NOT NULL
      , Id_Projet                INT          NOT NULL
      , Description              VARCHAR(48)  NOT NULL
    , CONSTRAINT PROJET_PK PRIMARY KEY (Id_Societe_Client, Id_Projet)
    , CONSTRAINT PROJET_CLIENT_FK FOREIGN KEY (Id_Societe_Client) 
          REFERENCES SOCIETE_CLIENT
) ;

Table CONTACT_CLIENT (clé primaire : {Id_Societe_Client, Id_Contact_Client}) :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE CONTACT_CLIENT
(
        Id_Societe_Client        INT          NOT NULL
      , Id_Contact_Client        INT          NOT NULL
      , Nom                      VARCHAR(48)  NOT NULL
      , Prenom                   VARCHAR(48)  NOT NULL
    , CONSTRAINT CONTACT_CLIENT_PK PRIMARY KEY (Id_Societe_Client, Id_Contact_Client)
    , CONSTRAINT CONTACT_CLIENT_SOCIETE_FK FOREIGN KEY (Id_Societe_Client) 
          REFERENCES SOCIETE_CLIENT
) ;

Table EQUIPE_CLIENT (clé primaire : {Id_Societe_Client, Id_Contact_Client, Id_Projet}):

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE EQUIPE_CLIENT
(
        Id_Societe_Client        INT          NOT NULL
      , Id_Projet                INT          NOT NULL
      , Id_Contact_Client        INT          NOT NULL
    , CONSTRAINT EQUIPE_CLIENT_PK PRIMARY KEY (Id_Societe_Client, Id_Contact_Client, Id_Projet)
    , CONSTRAINT EQUIPE_CLIENT_CONTACT_FK FOREIGN KEY (Id_Societe_Client, Id_Contact_Client) 
          REFERENCES CONTACT_CLIENT
    , CONSTRAINT EQUIPE_CLIENT_PROJET_FK FOREIGN KEY (Id_Societe_Client, Id_Projet) 
          REFERENCES PROJET ON DELETE CASCADE
) ;

Mettons un peu de contenu :

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
INSERT INTO SOCIETE_CLIENT (Id_Societe_Client, Nom) VALUES (1, 'Etablissements Naudin') ;
INSERT INTO SOCIETE_CLIENT (Id_Societe_Client, Nom) VALUES (2, 'Volfoni Frères') ;
 
INSERT INTO PROJET (Id_Societe_Client, Id_Projet, Description) VALUES (1, 1, 'projet Biglotron') ;
INSERT INTO PROJET (Id_Societe_Client, Id_Projet, Description) VALUES (1, 2, 'projet Pastis') ;
INSERT INTO PROJET (Id_Societe_Client, Id_Projet, Description) VALUES (2, 1, 'Apollo') ;
 
INSERT INTO CONTACT_CLIENT (Id_Societe_Client, Id_Contact_Client, Nom, Prenom) VALUES (1, 1, 'Trancène', 'Jean') ;
INSERT INTO CONTACT_CLIENT (Id_Societe_Client, Id_Contact_Client, Nom, Prenom) VALUES (1, 2, 'Quatre', 'Henri') ;
INSERT INTO CONTACT_CLIENT (Id_Societe_Client, Id_Contact_Client, Nom, Prenom) VALUES (2, 1, 'Paraboum', 'Pascal') ;
INSERT INTO CONTACT_CLIENT (Id_Societe_Client, Id_Contact_Client, Nom, Prenom) VALUES (2, 2, 'Piouf', 'Bastien') ;
 
INSERT INTO EQUIPE_CLIENT (Id_Societe_Client, Id_Projet, Id_Contact_Client) VALUES (1, 1, 1) ;
INSERT INTO EQUIPE_CLIENT (Id_Societe_Client, Id_Projet, Id_Contact_Client) VALUES (1, 2, 1) ;
INSERT INTO EQUIPE_CLIENT (Id_Societe_Client, Id_Projet, Id_Contact_Client) VALUES (2, 1, 1) ;

Pour visualiser les affectations :

Code SQL :

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

1
2
3
4
5
6
7
SELECT t.Nom, y.Description, z.Prenom, z.Nom 
FROM   EQUIPE_CLIENT AS x JOIN PROJET AS y ON x.Id_Societe_Client = y.Id_Societe_Client
                                           AND x.Id_Projet = y.Id_Projet
                          JOIN CONTACT_CLIENT AS z ON x.Id_Societe_Client = z.Id_Societe_Client
                                           AND x.Id_Contact_Client = Z.Id_Contact_Client
                          JOIN SOCIETE_CLIENT AS t ON x.Id_Societe_Client = t.Id_Societe_Client
;

=>

Nom                      Description         Prenom    Nom
---------------------    ----------------       --------   ---------
Etablissements Naudin    projet Biglotron       Jean       Trancène
Etablissements Naudin    projet Pastis          Jean       Trancène
Volfoni Frères           Apollo                 Pascal     Paraboum 

N’hésitez pas à faire part de vos questions, des points qui ne vous paraissent pas clairs ou vous passent au-dessus de la tête...

[jim53]

fsmrel,

Je vous avoue que malgré ma bonne volonté j'ai encore du mal à comprendre toutes vos explications qui semblent pourtant particulièrement claires.

J'ai modifié ma base de données en appliquant l'identification relative comme vous me l'avez montré.
Désormais, je souhaite pour relier ma table produit et ma table spécification aux tables Projet et Société_Client sachant que :
- Une spécification est soit un document rédigé par un client, soit une norme issue d'un organisme tel que que l'AFNOR.
- Le but d'un projet est de concevoir pour un client un ou plusieurs produits devant répondre à une ou plusieurs spécifications.

J'ai tenté d'établir des relations entre les différentes tables Projet, Société_Client, Spécification, Classeur_Spécification et Produit mais je n'arrive pas à réaliser la dernière relation entre la table Produit et Projet car SQLServer m'indique que je n'ai pas le même nombre de colonne des deux cotés de la relation. Je vous joins l'image de mes relations.
Dans cette image n'apparait pas le fait qu'une spécification puisse être une norme parce que je ne vois pas comment l'intégrer.

Je vous remercie d'avance pour votre aide.

[fsmrel]

Bonsoir Jim,


Allons-y pour la partie spécifications...

Les spécifications sont hétérogènes, on y trouve donc des contrats (sans doute des avenants à ces contrats), des cahiers des charges, bref tous documents fournis par les clients, mais aussi des documents non fournis par les clients (normes du style AFNOR). Je propose qu’on isole ce qui ne vient pas de chez les clients.

Dans ces conditions, on met en œuvre une table des spécifications fournies par les clients (je l’ai nommée de mon côté SPEC_CLIENT, mais bien entendu vous utilisez pour votre part les noms qui vous conviennent), identifiée relativement à SOCIETE_CLIENT et une table des spécifications non fournies par les clients (SPEC_NON_CLIENT), identifiée de façon absolue (elle ne dépend d’aucune autre table).

La clé primaire de votre table PRODUIT ne va pas. Un produit peut être utilisé pour plusieurs clients ayant chacun leurs spécifs, en vertu de quoi on l’identifie de façon absolue et Id_Produit suffit pour la clé primaire.

Je vous propose de réfléchir au modèle ci-dessous, dans lequel les relations entre les produits et les spécifications tiennent compte de l’isolation des spécifications entre celles qui dépendent des clients et celles qui n’en dépendent pas.



Comme cette représentation graphique ne permet pas de rendre compte exactement des relations entre tables (entre autres insuffisances), je joins les CREATE TABLE.

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TABLE SOCIETE_CLIENT
(
        Id_Societe_Client        INT          NOT NULL
      , Nom                      VARCHAR(48)  NOT NULL
      , SIREN                    NUMERIC(9)   NOT NULL
    , CONSTRAINT SOCIETE_CLIENT_PK PRIMARY KEY (Id_Societe_Client)
    , CONSTRAINT SOCIETE_CLIENT_AK UNIQUE (SIREN)
) ;

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
CREATE TABLE PROJET
(
        Id_Societe_Client        INT          NOT NULL
      , Id_Projet                INT          NOT NULL
      , Num_DR                   INT          NOT NULL
      , Description              VARCHAR(48)  NOT NULL
      , Id_Type_Projet           INT          NOT NULL
      , Id_Statut_Projet         INT          NOT NULL
      , Statut_Projet_Depuis     DATETIME     NOT NULL
    , CONSTRAINT PROJET_PK PRIMARY KEY (Id_Societe_Client, Id_Projet)
    , CONSTRAINT PROJET_AK UNIQUE (Num_DR)
    , CONSTRAINT PROJET_CLIENT_FK FOREIGN KEY (Id_Societe_Client) 
          REFERENCES SOCIETE_CLIENT
    , CONSTRAINT TYPE_PROJET_FK FOREIGN KEY (Id_Type_Projet) 
          REFERENCES TYPE_PROJET
    , CONSTRAINT STATUT_PROJET_FK FOREIGN KEY (Id_Statut_Projet) 
          REFERENCES STATUT_PROJET
) ;

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TABLE PRODUIT
(
        Id_Produit               INT          NOT NULL
      , Produit_Code             CHAR(16)     NOT NULL
      , Produit_Designation      VARCHAR(48)  NOT NULL
    , CONSTRAINT PRODUIT_PK PRIMARY KEY (Id_Produit)
    , CONSTRAINT PRODUIT_AK UNIQUE (Produit_Code)
) ;

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
CREATE TABLE SPEC_CLIENT
(
        Id_Societe_Client        INT          NOT NULL
      , Id_Spec_Client         INT          NOT NULL
      , Spec_reference           CHAR(16)     NOT NULL
      , Spec_Designation         VARCHAR(48)  NOT NULL
      , Spec_Date_reception      DATETIME     NOT NULL
      , Id_Type_Spec_Client    INT          NOT NULL
    , CONSTRAINT SPEC_CLIENT_PK PRIMARY KEY (Id_Societe_Client, Id_Spec_Client)
    , CONSTRAINT SPEC_CLIENT_AK UNIQUE (Spec_reference)
    , CONSTRAINT SPEC_CLIENT_SOCIETE_FK FOREIGN KEY (Id_Societe_Client) 
          REFERENCES SOCIETE_CLIENT ON DELETE CASCADE
    , CONSTRAINT SPEC_CLIENT_TYPE_FK FOREIGN KEY (Id_Type_Spec_Client) 
          REFERENCES TYPE_SPEC_CLIENT ON DELETE CASCADE
) ;

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE CLASSEUR_SPEC_CLIENT
(
        Id_Societe_Client        INT          NOT NULL
      , Id_Spec_Client         INT          NOT NULL
      , Id_Produit               INT          NOT NULL
    , CONSTRAINT CLASSEUR_SPEC_CLIENT_PK PRIMARY KEY (Id_Societe_Client, Id_Spec_Client, Id_Produit)
    , CONSTRAINT CLASSEUR_SPEC_CLIENT_FK FOREIGN KEY (Id_Societe_Client, Id_Spec_Client) 
          REFERENCES SPEC_CLIENT ON DELETE CASCADE
    , CONSTRAINT CLASSEUR_SPEC_CLIENT_PRODUIT_FK FOREIGN KEY (Id_Produit) 
          REFERENCES PRODUIT
) ;

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE SPEC_CLIENT_PROJET
(
        Id_Societe_Client        INT          NOT NULL
      , Id_Spec_Client           INT          NOT NULL
      , Id_Produit               INT          NOT NULL
      , Id_Projet                INT          NOT NULL
    , CONSTRAINT SPEC_CLIENT_PROJET_PK PRIMARY KEY (Id_Societe_Client, Id_Spec_Client, Id_Produit, Id_Projet)
    , CONSTRAINT SPEC_CLIENT_PROJET_CLASSEUR_FK FOREIGN KEY (Id_Societe_Client, Id_Spec_Client, Id_Produit) 
          REFERENCES CLASSEUR_SPEC_CLIENT
    , CONSTRAINT SPEC_CLIENT_PROJET_PRODUIT_FK FOREIGN KEY (Id_Societe_Client, Id_Projet) 
          REFERENCES PROJET
) ;

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TABLE SPEC_NON_CLIENT
(
        Id_Spec                  INT          NOT NULL
      , Spec_Reference           CHAR(16)     NOT NULL
      , Spec_Designation         VARCHAR(48)  NOT NULL
    , CONSTRAINT SPEC_NON_CLIENT_PK PRIMARY KEY (Id_Spec)
    , CONSTRAINT SPEC_NON_CLIENT_AK UNIQUE (Spec_Reference)  
) ;

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE SPEC_NON_CLIENT_PRODUIT
(
        Id_Spec                INT          NOT NULL
      , Id_Produit             INT          NOT NULL
    , CONSTRAINT SPEC_NON_CLIENT_PRODUIT_PK PRIMARY KEY (Id_Spec, Id_Produit)
    , CONSTRAINT SPEC_NON_CLIENT_SPEC_FK FOREIGN KEY (Id_Spec) 
          REFERENCES SPEC_NON_CLIENT
    , CONSTRAINT SPEC_NON_CLIENT_PRODUIT_FK FOREIGN KEY (Id_Produit) 
          REFERENCES PRODUIT
) ;

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE SPEC_NON_CLIENT_PROJET
(
        Id_Societe_Client      INT          NOT NULL
      , Id_Projet              INT          NOT NULL
      , Id_Spec                INT          NOT NULL
      , Id_Produit             INT          NOT NULL
    , CONSTRAINT SPEC_NON_CLIENT_PROJET_PK PRIMARY KEY (Id_Societe_Client, Id_Projet, Id_Spec, Id_Produit)
    , CONSTRAINT PEC_NON_CLIENT_PROJET_PRODUIT_FK FOREIGN KEY (Id_Spec, Id_Produit) 
          REFERENCES SPEC_NON_CLIENT_PRODUIT
    , CONSTRAINT SPEC_NON_CLIENT_PROJET_FK FOREIGN KEY (Id_Societe_Client, Id_Projet) 
          REFERENCES PROJET
) ;

Je reviendrai sur le reste dans la journée (si possible...), il y a encore plein de choses à dire.

[jim53]

fsmrel,

Est-il correct que le champs ID_Société_CLient de la table Spécification_Non_Client_Projet ne soit pas indiqué comme clé primaire alors qu'il l'est dans la table Spécification_Client_Projet?

[fsmrel]

fsmrel,

Est-il correct que le champs ID_Société_CLient de la table Spécification_Non_Client_Projet ne soit pas indiqué comme clé primaire alors qu'il l'est dans la table Spécification_Client_Projet?

Par rapport au CREATE TABLE — qui fait foi — de la table SPEC_NON_CLIENT_PROJET, (cf. mon message précédent), la table Spécification_Non_Client_Projet n’est pas conforme : l’attribut ID_Société_CLient fait bien partie de la clé primaire, puisqu’il est hérité de la clé primaire de la table PROJET. Son absence sur la représentation graphique vient du fait que celle-ci n’a pas dû être rafraîchie lors de son affichage, voici la mise à jour :

[jim53]

Fsmrel,

Savez-vous comment créer une contrainte d'unicité sous SQLServer 2008R2 en mode graphique?
Je crois qu'on peut le faire en :
- faire un clic droit sur le champ de la table concernée
- sélectionner "Index/Clés..." dans le menu contextuel
- Cliquer sur le bouton "Ajouter"
- En remplissant le champ "Est unique" avec oui

Ensuite, pour le type, je ne sais pas s'il faut que je choisisse "Clé unique" ou 'Index"

[fsmrel]

Savez-vous comment créer une contrainte d'unicité sous SQLServer 2008R2 en mode graphique?
Je crois qu'on peut le faire en :
- faire un clic droit sur le champ de la table concernée
- sélectionner "Index/Clés..." dans le menu contextuel
- Cliquer sur le bouton "Ajouter"
- En remplissant le champ "Est unique" avec oui

Ensuite, pour le type, je ne sais pas s'il faut que je choisisse "Clé unique" ou 'Index"

En fait, je ne travaille pas directement en mode graphique, à l’inverse je déclare les tables à l’aide d’un script qui contient les instructions CREATE TABLE et ce n’est qu’une fois le script réputé valide que j’affiche le diagramme.

Quoi qu’il en soit, voilà comment est traduite la ligne CONSTRAINT PROJET_AK UNIQUE (Num_DR), pour signifier que {Num_DR} est clé alternative pour la table PROJET :



N.B. J’utilise SQL Server 2005.

P.-S. Si telle ou telle réponse vous paraît utile, n’hésitez pas à voter, sinon on ne sait pas très bien que penser de leur clarté, de leur intérêt, en dépit du soin qu’on y apporte...

[fsmrel]

A propos des statuts des projets


Pour reprendre ce que j’ai proposé concernant les statuts des projets (voyez l’instruction CREATE TABLE PROJET_STATUT_HISTO), votre diagramme doit être complété car il y manque la relation entre PROJET et PROJET_STATUT_HISTO, table par ailleurs identifiée relativement à sa maman PROJET et qui doit donc aussi comporter l’attribut Id_Societe_Client.


Script des CREATE TABLE concernés :

Code SQL :

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

1
2
3
4
5
6
CREATE TABLE STATUT_PROJET
(
        Id_Statut_Projet         INT          NOT NULL
      , Statut                   VARCHAR(48)  NOT NULL
    , CONSTRAINT STATUT_PROJET_PK PRIMARY KEY (Id_Statut_Projet)
) ;

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TABLE SOCIETE_CLIENT
(
        Id_Societe_Client        INT          NOT NULL
      , Nom                      VARCHAR(48)  NOT NULL
      , SIREN                    NUMERIC(9)   NOT NULL
    , CONSTRAINT SOCIETE_CLIENT_PK PRIMARY KEY (Id_Societe_Client)
    , CONSTRAINT SOCIETE_CLIENT_AK UNIQUE (SIREN)
) ;

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
CREATE TABLE PROJET
(
        Id_Societe_Client        INT          NOT NULL
      , Id_Projet                INT          NOT NULL
      , Num_DR                   INT          NOT NULL
      , Description              VARCHAR(48)  NOT NULL
      , Id_Type_Projet           INT          NOT NULL
      , Id_Statut_Projet         INT          NOT NULL
      , Statut_Projet_Depuis     DATETIME     NOT NULL
    , CONSTRAINT PROJET_PK PRIMARY KEY (Id_Societe_Client, Id_Projet)
    , CONSTRAINT PROJET_AK UNIQUE (Num_DR)
    , CONSTRAINT PROJET_CLIENT_FK FOREIGN KEY (Id_Societe_Client) 
          REFERENCES SOCIETE_CLIENT
    , CONSTRAINT TYPE_PROJET_FK FOREIGN KEY (Id_Type_Projet) 
          REFERENCES TYPE_PROJET
    , CONSTRAINT STATUT_PROJET_FK FOREIGN KEY (Id_Statut_Projet) 
          REFERENCES STATUT_PROJET) ;

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE PROJET_STATUT_HISTO
(
        Id_Societe_Client        INT         NOT NULL
      , ID_Projet                INT         NOT NULL
      , Statut_Projet_Debut      DATETIME    NOT NULL
      , Statut_Projet_Fin        DATETIME    NOT NULL
      , Id_Statut_Projet         INT         NOT NULL
    , CONSTRAINT PROJET_STATUT_HISTO_PK PRIMARY KEY (Id_Societe_Client, ID_Projet, Statut_Projet_Debut)
    , CONSTRAINT PROJET_STATUT_HISTO_FK1 FOREIGN KEY (Id_Societe_Client, ID_Projet) 
          REFERENCES PROJET ON DELETE CASCADE
    , CONSTRAINT PROJET_STATUT_HISTO_FK2 FOREIGN KEY (Id_Statut_Projet) 
          REFERENCES STATUT_PROJET
) ;

Diagramme obtenu à partir du script

[jim53]

Fsmrel,

Merci pour toutes ces informations, j'ai bien compléter mon diagramme avec vos remarques concernant le statut et l'historique du statut du projet.

J'ai quelques questions :
1) Pour que mes diagrammes soient plus lisibles, j'ai créé plusieurs schémas de base de données afin de regrouper les relations par sous-ensembles (Exemple : relation client Projet, Relation Projet Spécification, Relation Statut Projet, Relation ressource...) Est ce que cette méthode est adaptée?

2) Comment faites vous pour créer le code SQL des tables que vous insérer dans vos réponses à partir de SQLServer?

3) J'ai tenter de relier une table ressource à ma table projet, pouvez-vous me dire si des relations vous semblent incohérentes et notament au niveau de la relation entre le le chef de projet et la table Ressource?

[fsmrel]

Bonsoir Jim,


Dans l’ensemble, les réponses que j'ai faites ont visiblement l’air de vous satisfaire, donc on progresse . Mais je répète, si certaines d’entre elles méritent plus d’éclaircissements, n’hésitez pas à revenir à la charge.

Pour que mes diagrammes soient plus lisibles, j'ai créé plusieurs schémas de base de données afin de regrouper les relations par sous-ensembles (Exemple : relation client Projet, Relation Projet Spécification, Relation Statut Projet, Relation ressource...) Est ce que cette méthode est adaptée?

La méthode dont vous faites mention est non seulement adaptée, mais il faut l’utiliser sans modération ; vous aurez remarqué que je n’ai pas fait autrement et continuerai ainsi. On appelle cela urbaniser le modèle (ce qui dans les gros projets revient à l’urbanisation en domaines, sous-domaines, etc.). A défaut, imaginez un diagramme comportant 2000 tables (j’ai connu des projets pour lesquels le nombre de tables était de cet ordre). Même avec une centaine de tables, on peut arriver à des diagrammes totalement obscurs, du genre de celui-ci qui tient de l’art new-yorkais...

Comment faites vous pour créer le code SQL des tables que vous insérer dans vos réponses à partir de SQLServer?

Je saisis à la main les instructions CREATE TABLE (ou les génère à l’aide d’un outil de modélisation comme PowerAMC ou MySQL Workbench). Ces instructions font l’objet d’un script dans un fichier de type .TXT, nommé ici "jim53.sql" et que je n'ai plus qu'à ouvrir :




Une fois le fichier ouvert, je peux retoucher le script, toujours en mode texte. Ainsi je peux déclarer de nouvelles instructions, en modifier, créer des triggers, des fonctions, des procédures, etc., puis passer à l'exécution du script, corriger les erreurs détectées par SQL Server tant qu'il y en a :



Quand c’est bon, je demande la génération des diagrammes. J’ai bien essayé de créer les tables à partir des diagrammes mais j’ai trouvé que j’allais bien moins vite et que ça manquait de souplesse (et SQL Server se met à boucler puis finit en plantage... ) : j’ai vite laissé tomber.

J'ai tenter de relier une table ressource à ma table projet, pouvez-vous me dire si des relations vous semblent incohérentes et notament au niveau de la relation entre le le chef de projet et la table Ressource?

La tentative fut bonne, il n’y a pas d’incohérence.

Une question : Une personne peut-elle être chef de plusieurs projets en même temps ?

Une autre question : Un chef de projet peut-il en même temps faire partie d’une équipe d’un autre projet (sans y être chef) ?

A noter qu'il y a un risque lors de la composition des équipes de ressources : au hasard des saisies des données, tel chef de projet pourrait figurer dans l’équipe des ressources (table Equipe_Ressource) en temps que ressource de son projet, et tel autre pas. Pour éviter cela, soit on interdit qu’un chef figure dans l’équipe (ce qui est techniquement faisable par trigger), soit on l’impose, mais on débouche sur des problèmes délicats à résoudre pour garantir cette exigence. J’aurais tendance à dire que le mieux est de l’interdire.

[jim53]

Fsmrel,

Ci-dessous les réponses à vos questions :

Une personne peut-elle être chef de plusieurs projets en même temps ?

Effectivement, une personne peut être chef de plusieurs en même temps.

Un chef de projet peut-il en même temps faire partie d’une équipe d’un autre projet (sans y être chef) ?

Oui.

[fsmrel]

Bonjour Jim,


Pour résumer, les affectations des chefs de projets sont sujettes aux règles suivantes :

1. Une ressource peut être chef de plus d’un projet en même temps ;

2. Une ressource peut faire partie d’une équipe si elle n’est pas chef du projet auquel est rattachée cette équipe (mais elle peut être chef d'autres projets) ;

3. Corollaire : le chef d’un projet ne peut pas être en même temps un de ses collaborateurs au sein de l’équipe rattachée à ce projet.

La règle 2 (donc son corollaire) peut être inversée : un chef de projet doit aussi figurer au sein de la table EQUIPE_RESSOURCE, mais autant dans le cadre de la théorie relationnelle cette règle est facile à faire respecter (grâce à l’affectation multiple, multiple assignment), autant elle deviendrait délicate à faire respecter à 100% dans le cadre de la norme SQL, et encore bien plus dans le contexte SQL Server (pas d'instruction CREATE ASSERTION).


Diagramme correspondant



Structure des tables


TABLE PROJET

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
CREATE TABLE PROJET
(
        Id_Societe_Client        INT          NOT NULL
      , Id_Projet                INT          NOT NULL
      , Num_DR                   INT          NOT NULL
      , Description              VARCHAR(48)  NOT NULL
      , Id_Chef_de_Projet        INT          NOT NULL
      , Id_Type_Projet           INT          NOT NULL
      , Id_Statut_Projet         INT          NOT NULL
      , Statut_Projet_Depuis     DATETIME     NOT NULL
    , CONSTRAINT PROJET_PK PRIMARY KEY (Id_Societe_Client, Id_Projet)
    , CONSTRAINT PROJET_AK UNIQUE (Num_DR)
    , CONSTRAINT PROJET_CLIENT_FK FOREIGN KEY (Id_Societe_Client) REFERENCES SOCIETE_CLIENT
    , CONSTRAINT PROJET_CHEF_FK FOREIGN KEY (Id_Chef_de_Projet) REFERENCES RESSOURCE
    , CONSTRAINT TYPE_PROJET_FK FOREIGN KEY (Id_Type_Projet) REFERENCES TYPE_PROJET
    , CONSTRAINT STATUT_PROJET_FK FOREIGN KEY (Id_Statut_Projet) REFERENCES STATUT_PROJET
) ;

TABLE RESSOURCE

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
CREATE TABLE RESSOURCE
(
        Ressource_Id             INT          NOT NULL
      , Service_Id               INT          NOT NULL
      , Fonction_Id              INT          NOT NULL
      , Est_Utilisateur          BIT          NOT NULL
      , Identifiant              VARCHAR(48)  NOT NULL
      , Mot_de_Passe             VARCHAR(48)  NOT NULL
   , CONSTRAINT RESSOURCE_PK PRIMARY KEY (Ressource_Id)
   , CONSTRAINT RESSOURCE_AK UNIQUE (Identifiant)
   , CONSTRAINT RESSOURCE_INDIVIDU_FK FOREIGN KEY (Ressource_Id) REFERENCES INDIVIDU ON DELETE CASCADE
   , CONSTRAINT RESSOURCE_SERVICE_FK FOREIGN KEY (Service_Id) REFERENCES SERVICE
   , CONSTRAINT RESSOURCE_FONCTION_FK FOREIGN KEY (Fonction_Id)REFERENCES FONCTION
) ;

TABLE EQUIPE_RESSOURCE

Code SQL :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE EQUIPE_RESSOURCE
(
        Id_Societe_Client        INT          NOT NULL
      , Id_Projet                INT          NOT NULL
      , Ressource_Id             INT          NOT NULL
    , CONSTRAINT EQUIPE_RESSOURCE_PK PRIMARY KEY (Id_Societe_Client, Id_Projet, Ressource_Id)
    , CONSTRAINT EQUIPE_RESSOURCE_FK FOREIGN KEY (Ressource_Id) REFERENCES RESSOURCE ON DELETE CASCADE
    , CONSTRAINT EQUIPE_RESSOURCE_PROJET_FK FOREIGN KEY (Id_Societe_Client, Id_Projet) REFERENCES PROJET
) ;

Triggers pour faire respecter la règle 3

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
CREATE TRIGGER PROJET_CHEF_TRIGGER_01 ON PROJET INSTEAD OF INSERT, UPDATE AS
    DECLARE @N AS INT ;
    SET @N = (
              SELECT COUNT(*)
              FROM   INSERTED AS x JOIN EQUIPE_RESSOURCE AS y ON  x.Id_Societe_Client = y.Id_Societe_Client
                                                              AND x.Id_Projet = y.Id_Projet 
                                                              AND x.Id_Chef_de_Projet = y.Ressource_Id 
             ) ;
    IF @N > 0
        BEGIN
            RAISERROR ('Un chef de projet ne peut pas être en même temps un de ses collaborateurs',16,1) 
            ROLLBACK
            RETURN
        END ;
    INSERT INTO PROJET
        SELECT *
        FROM INSERTED ;

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
CREATE TRIGGER PROJET_EQUIPE_RESSOURCE_TRIGGER_01 ON EQUIPE_RESSOURCE INSTEAD OF INSERT, UPDATE AS
    DECLARE @N AS INT ;
    SET @N = (
              SELECT COUNT(*)
              FROM   INSERTED AS x JOIN PROJET AS y ON  x.Id_Societe_Client = y.Id_Societe_Client 
                                                    AND x.Id_Projet = y.Id_Projet 
                                                    AND x.Ressource_Id = y.Id_Chef_de_Projet 
             ) ;
    IF @N > 0
        BEGIN
            RAISERROR ('Une ressource d’une équipe ne peut pas être en même temps chef du projet concerné',16,1) 
            ROLLBACK
            RETURN ;
        END
    INSERT INTO EQUIPE_RESSOURCE
        SELECT *
        FROM INSERTED ;

[fsmrel]

Bonsoir Jim,

A propos de la généralisation des personnes physiques (individus)

Les contacts et les ressources devraient pouvoir faire l’objet d’une généralisation (factorisation). En effet, ce sont des personnes caractérisées par des propriétés communes d’une part (nom, prénom, etc.) et de propriétés spécifiques d’autre part (Est_Utilisateur). On reviendra sur ce sujet.

Effectivement, j'y avais un peu penser mais je n'ai pu le mettre en œuvre pour différencier les employées du client et les employés de ma société.

Pour aller dans le sens de la généralisation, on définit une table où sont rassemblées les propriétés communes aux contacts et aux ressources :




Et bien sûr, on conserve les tables RESSOURCE et CONTACT_CLIENT dans lesquelles sont conservées les seules spécificités propres aux contacts et aux ressources (attributs et relations) :



Pour mémoire, j’ai noté que certains attributs donnent lieu à des clés alternatives (AK = alternate key). C’est l’occasion de signaler que les clés primaires sont artificielles⁽¹⁾, non significatives (donc non modifiables).

Les CREATE TABLE à la source du diagramme :


Tables SERVICE et FONCTION

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE SERVICE
(
        Service_Id               INT          NOT NULL
      , Service_Nom              VARCHAR(48)  NOT NULL
    , CONSTRAINT SERVICE_PK PRIMARY KEY (Service_Id)
) ;
 
CREATE TABLE FONCTION
(
        Fonction_Id              INT          NOT NULL
      , Fonction_Nom             VARCHAR(48)  NOT NULL
    , CONSTRAINT FONCTION_PK PRIMARY KEY (Fonction_Id)
) ;

Table SOCIETE_INDIVIDU

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TABLE INDIVIDU
(
        Individu_Id               INT          NOT NULL
      , Individu_Nom              VARCHAR(48)  NOT NULL
      , Individu_Prenom           VARCHAR(48)  NOT NULL
      , Individu_Tel              VARCHAR(24)  NOT NULL
    , CONSTRAINT INDIVIDU_PK PRIMARY KEY (Individu_Id)
) ;

Table RESSOURCE

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE RESSOURCE
(
        Ressource_Id             INT          NOT NULL
      , Service_Id               INT          NOT NULL
      , Fonction_Id              INT          NOT NULL
      , Est_Utilisateur          BIT          NOT NULL
      , Identifiant              VARCHAR(48)  NOT NULL
      , Mot_de_Passe             VARCHAR(48)  NOT NULL
   , CONSTRAINT RESSOURCE_PK PRIMARY KEY (Ressource_Id)
   , CONSTRAINT RESSOURCE_AK UNIQUE (Identifiant)
   , CONSTRAINT RESSOURCE_INDIVIDU_FK FOREIGN KEY (Ressource_Id) REFERENCES INDIVIDU ON DELETE CASCADE
   , CONSTRAINT RESSOURCE_SERVICE_FK FOREIGN KEY (Service_Id) REFERENCES SERVICE
   , CONSTRAINT RESSOURCE_FONCTION_FK FOREIGN KEY (Fonction_Id) REFERENCES FONCTION) ;

Table SOCIETE_CLIENT

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TABLE SOCIETE_CLIENT
(
        Id_Societe_Client        INT          NOT NULL
      , Nom                      VARCHAR(48)  NOT NULL
      , SIREN                    NUMERIC(9)   NOT NULL
    , CONSTRAINT SOCIETE_CLIENT_PK PRIMARY KEY (Id_Societe_Client)
    , CONSTRAINT SOCIETE_CLIENT_AK UNIQUE (SIREN)
) ;

Table CONTACT_CLIENT

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE CONTACT_CLIENT
(
        Individu_Id              INT          NOT NULL
      , Id_Societe_Client        INT          NOT NULL
      , Id_Contact_Client        INT          NOT NULL
    , CONSTRAINT CONTACT_CLIENT_PK PRIMARY KEY (Id_Societe_Client, Id_Contact_Client)
    , CONSTRAINT CONTACT_CLIENT_AK UNIQUE (Individu_Id)
    , CONSTRAINT CONTACT_CLIENT_INDIVIDU_FK FOREIGN KEY (Individu_Id) 
          REFERENCES INDIVIDU ON DELETE CASCADE
    , CONSTRAINT CONTACT_CLIENT_SOCIETE_FK FOREIGN KEY (Id_Societe_Client) 
          REFERENCES SOCIETE_CLIENT ON DELETE CASCADE
) ;

Il est d’usage de prévoir une contrainte d’exclusion qui appliquée ici veut qu’une ressource et un contact ne peuvent être la même personne. Dans le cas de SQL Server, on passe par des triggers.

Concernant la mise à jour de la table RESSOURCE :

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
CREATE TRIGGER RESSOURCE_TRIGGER_01 ON RESSOURCE INSTEAD OF INSERT, UPDATE AS
    DECLARE @N AS INT
 
    SET @N = (
              SELECT COUNT(*)
              FROM   INSERTED AS x JOIN CONTACT_CLIENT AS y ON x.Ressource_Id = y.Individu_Id 
             ) ;
    IF @N > 0
        BEGIN
            RAISERROR ('Une ressource de chez nous ne peut pas être en même temps un contact d’un client',16,1) 
            ROLLBACK
            RETURN
        END ;
    INSERT INTO RESSOURCE
        SELECT *
        FROM INSERTED ;

Concernant la mise à jour de la table CONTACT_CLIENT :

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
CREATE TRIGGER CONTACT_CLIENT_TRIGGER_01 ON CONTACT_CLIENT INSTEAD OF INSERT, UPDATE AS
    DECLARE @N AS INT
 
    SET @N = (
              SELECT COUNT(*)
              FROM   INSERTED AS x JOIN RESSOURCE AS y ON x.Individu_Id = y.Ressource_Id
             ) ;
    IF @N > 0
        BEGIN
            RAISERROR ('Un contact d’un client ne peut pas être en même temps une ressource de chez nous',16,1) 
            ROLLBACK
            RETURN
        END ;
    INSERT INTO CONTACT_CLIENT
        SELECT *
        FROM INSERTED ;

_____________

⁽¹⁾Il y a plus de 25 ans, l’excellentissime Yves Tabourier à écrit ceci (De l’autre côté de MERISE, page 80), et c’est une règle d’or :

« ... La fonction d’une propriété est de décrire les objets (et les rencontres), alors que l’identifiant ne décrit rien. Son rôle fondamental est d’être sûr de distinguer deux jumeaux parfaits, malgré des descriptions identiques.
L’expérience montre d’ailleurs que l’usage des “identifiants significatifs” (ou “codes significatifs”) a pu provoquer des dégâts tellement coûteux que la sagesse est d’éviter avec le plus grand soin de construire des identifiants décrivant les objets ou, pis encore, leurs liens avec d’autres objets... »

Autrement dit, identifier un type d’entité-type tel qu’un véhicule automobile par un numéro d’immatriculation ou autres informations portées sur une carte grise est à éviter. De la même façon identifier une entreprise par son numéro Siren est à prohiber : en effet, quand l’INSEE procède à une modification, ça peut être extrêmement pénalisant.