Personne physique, morale et utilisateur

**fsmrel** · 23/01/2014, 19h23

Concernant mon message précédent :

J’ai collé la figure 9 qui va bien (le copié/collé précédent n’était pas le bon). Par ailleurs j’ai modifié la ligne 11 du code SQL de la table REPRESENTATION (clé alternative).

**fsmrel** · 23/01/2014, 22h03

Envoyé par fsmrel

On regardera si le MLD (que je proposerai) continue à garantir la règle RG040, autrement dit si le dernier MCD vaut le coup...

Voici d’abord le MCD global :

A propos du login

On observera que selon ce MCD, rien n’empêche qu’une personne physique utilise le même login qu’un représentant d’une personne morale : au stade SQL il y aura donc une assertion (ou à défaut un trigger) à mettre en œuvre pour l’interdire. Mais on observera par ailleurs que, selon ce MCD, deux personnes physiques ne peuvent pas utiliser le même login (l’attribut Login de l’entité-type P_PHYSIQUE faisant l’objet d’un identifiant alternatif). De même, deux comptes de représentation ne peuvent pas utiliser le même login puisque l’attribut Login de l’entité-type COMPTE faisant lui aussi l’objet d’un identifiant alternatif.

Le MLD dérivé du MCD :

Comme pour toute contrainte d’exclusion il faudra s’assurer par assertion ou trigger que les colonnes PmorId (table P_MORALE) et PphyId (table P_PHYSIQUE) prennent des valeurs différentes.

Indépendamment de cela, si on examine le code SQL (SQL Server 2005 en l’occurrence) dérivé de ce MLD, on se rend compte que la règle RG040 ne peut pas être violée. Il suffit de comparer la composition de la clé primaire de la table REPRESENTATION et celle de la clé étrangère REPRESENTATION_COMPTE_FK appartenant à cette même table (attribut PmorId en commun), puis de dévider l’écheveau, tirer sur les ficelles pour remonter jusqu’à PERSONNE par les différents chemins :

Code SQL :

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

1
2
CONSTRAINT REPRESENTATION_PK PRIMARY KEY (PmorId, FonctionId, PphyId),
CONSTRAINT REPRESENTATION_COMPTE_FK FOREIGN KEY (PmorId, AdrCourrielId, CompteId)

En résumé, un ensemble possible des tables :

TABLE 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 FONCTION (
   FonctionId           INT                  NOT NULL,
   FonctionLibelle      VARCHAR(64)          NOT NULL,
   CONSTRAINT FONCTION_PK PRIMARY KEY (FonctionId)
) ;
 
INSERT INTO FONCTION (FonctionId, FonctionLibelle) VALUES (1, 'président') ;
INSERT INTO FONCTION (FonctionId, FonctionLibelle) VALUES (2, 'trésorier') ;
INSERT INTO FONCTION (FonctionId, FonctionLibelle) VALUES (3, 'secrétaire') ;
INSERT INTO FONCTION (FonctionId, FonctionLibelle) VALUES (4, 'adhérent') ;
INSERT INTO FONCTION (FonctionId, FonctionLibelle) VALUES (5, 'non précisée') ;
 
SELECT ''AS FONCTION,* FROM FONCTION ;

TABLE PERSONNE

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
CREATE TABLE PERSONNE (
   PsnId                INT                  NOT NULL,
   PsnNom               VARCHAR(64)          NOT NULL,
   CONSTRAINT PERSONNE_PK PRIMARY KEY (PsnId)
) ;
 
INSERT INTO PERSONNE (PsnId, PsnNom) VALUES (1, 'Raoul') ;
INSERT INTO PERSONNE (PsnId, PsnNom) VALUES (3, 'Paul') ;
INSERT INTO PERSONNE (PsnId, PsnNom) VALUES (5, 'Pascal') ;
INSERT INTO PERSONNE (PsnId, PsnNom) VALUES (7, 'Bastien') ;
INSERT INTO PERSONNE (PsnId, PsnNom) VALUES (9, 'Albert') ;
INSERT INTO PERSONNE (PsnId, PsnNom) VALUES (11, 'Bernard') ;
 
INSERT INTO PERSONNE (PsnId, PsnNom) VALUES (2, 'Volfoni SA') ;
INSERT INTO PERSONNE (PsnId, PsnNom) VALUES (4, 'Les lutins bleus') ;
INSERT INTO PERSONNE (PsnId, PsnNom) VALUES (6, 'Ets Dugoineau') ;
INSERT INTO PERSONNE (PsnId, PsnNom) VALUES (8, 'Les boulistes du XVe') ;
 
SELECT ''AS PERSONNE,* FROM PERSONNE ;

TABLE ADR_COURRIEL

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 ADR_COURRIEL (
   PsnId                INT                  NOT NULL,
   AdrCourrielId        INT                  NOT NULL,
   AdresseCourriel      VARCHAR(64)          NOT NULL,
   CONSTRAINT ADR_COURRIEL_PK PRIMARY KEY (PsnId, AdrCourrielId),
   CONSTRAINT ADR_COURRIEL_AK UNIQUE (AdresseCourriel),
   CONSTRAINT ADR_COURRIEL_PERSONNE_FK FOREIGN KEY (PsnId)
      REFERENCES PERSONNE
) ;
 
INSERT INTO ADR_COURRIEL (PsnId, AdrCourrielId, AdresseCourriel) VALUES (1, 1, 'raoul@cezigue.com') ;
INSERT INTO ADR_COURRIEL (PsnId, AdrCourrielId, AdresseCourriel) VALUES (2, 1, 'volfo1@volfo.com') ;
INSERT INTO ADR_COURRIEL (PsnId, AdrCourrielId, AdresseCourriel) VALUES (2, 2, 'pascal@volfo.net') ;
INSERT INTO ADR_COURRIEL (PsnId, AdrCourrielId, AdresseCourriel) VALUES (3, 1, 'Paul@cezigue.com') ;
INSERT INTO ADR_COURRIEL (PsnId, AdrCourrielId, AdresseCourriel) VALUES (4, 1, 'loulou@lutinsbleus.net') ;
INSERT INTO ADR_COURRIEL (PsnId, AdrCourrielId, AdresseCourriel) VALUES (4, 2, 'paul@lutinsdbleus.net') ;
INSERT INTO ADR_COURRIEL (PsnId, AdrCourrielId, AdresseCourriel) VALUES (5, 1, 'pascal@volfo.com') ;
INSERT INTO ADR_COURRIEL (PsnId, AdrCourrielId, AdresseCourriel) VALUES (6, 1, 'dugoi@dugoineau.com') ;
 
SELECT ''AS ADR_COURRIEL,* FROM ADR_COURRIEL ;

TABLE COMPTE

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 TABLE COMPTE (
   PsnId                INT                  NOT NULL,
   AdrCourrielId        INT                  NOT NULL,
   CompteId             INT                  NOT NULL,
   Login                VARCHAR(64)          NOT NULL,
   MotDePasse           VARCHAR(64)          NOT NULL,
   CONSTRAINT COMPTE_PK PRIMARY KEY (PsnId, AdrCourrielId, CompteId),
   CONSTRAINT COMPTE_AK UNIQUE (Login),
   CONSTRAINT COMPTE_ADR_COURRIEL_FK FOREIGN KEY (PsnId, AdrCourrielId)
      REFERENCES ADR_COURRIEL
) ;
INSERT INTO COMPTE (PsnId, AdrCourrielId, CompteId, Login, MotDePasse) VALUES (2, 1, 1, 'Volfo01', '3510') ;
INSERT INTO COMPTE (PsnId, AdrCourrielId, CompteId, Login, MotDePasse) VALUES (2, 1, 2, 'Volfo02', '35j7') ;
INSERT INTO COMPTE (PsnId, AdrCourrielId, CompteId, Login, MotDePasse) VALUES (4, 1, 1, 'lutin01', 'bgty') ; 
 
SELECT ''AS COMPTE,* FROM COMPTE ;

TABLE P_MORALE

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 P_MORALE (
   PmorId               INT                  NOT NULL,
   Siren                CHAR(9)              NOT NULL,
   CONSTRAINT P_MORALE_PK PRIMARY KEY (PmorId),
   CONSTRAINT P_MORALE_AK UNIQUE (Siren),
   CONSTRAINT P_MORALE_PERSONNE_FK FOREIGN KEY (PmorId)
      REFERENCES PERSONNE ON DELETE CASCADE
) ;
 
INSERT INTO P_MORALE (PmorId, Siren) VALUES (2, '234567890') ;
INSERT INTO P_MORALE (PmorId, Siren) VALUES (4, '456789012') ;
INSERT INTO P_MORALE (PmorId, Siren) VALUES (6, '678901234') ;
INSERT INTO P_MORALE (PmorId, Siren) VALUES (8, '890123456') ;
 
SELECT ''AS P_MORALE,* FROM P_MORALE ;

TABLE P_PHYSIQUE

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 P_PHYSIQUE (
   PphyId               INT                  NOT NULL,
   AdrCourrielId        INT                  NOT NULL,
   CompteId             INT                  NOT NULL,
   Login                VARCHAR(64)          NOT NULL,
   MotDePasse           VARCHAR(64)          NOT NULL,
   CONSTRAINT P_PHYSIQUE_PK PRIMARY KEY (PphyId),
   CONSTRAINT P_PHYSIQUE_AK UNIQUE (Login),
   CONSTRAINT P_PHYSIQUE_PERSONNE_FK FOREIGN KEY (PphyId)
      REFERENCES PERSONNE  ON DELETE CASCADE,
   CONSTRAINT P_PHYSIQUE_ADR_COURRIEL_FK FOREIGN KEY (PphyId, AdrCourrielId)
      REFERENCES ADR_COURRIEL (PsnId, AdrCourrielId)
) ;
 
INSERT INTO P_PHYSIQUE (PphyId, AdrCourrielId, CompteId, Login, MotDePasse) VALUES (1, 1, 1, 'LeRaoul', 'ksvfty4') ;
INSERT INTO P_PHYSIQUE (PphyId, AdrCourrielId, CompteId, Login, MotDePasse) VALUES (3, 1, 1, 'Paupaul', 'cwdno6s') ;
 
SELECT ''AS P_PHYSIQUE,* FROM P_PHYSIQUE ;

TABLE MANDAT

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
CREATE TABLE MANDAT (
   PmorId               INT                  NOT NULL,
   FonctionId           INT                  NOT NULL,
   DateDebut            DATETIME             NOT NULL,
   DateFin              DATETIME             NOT NULL DEFAULT '2014-12-31',
   CONSTRAINT MANDAT_PK PRIMARY KEY (PmorId, FonctionId),
   CONSTRAINT MANDAT_P_MORALE_FK FOREIGN KEY (PmorId)
      REFERENCES P_MORALE ON DELETE CASCADE,
   CONSTRAINT MANDAT_FONCTION_FK FOREIGN KEY (FonctionId)
      REFERENCES FONCTION
) ;
 
INSERT INTO MANDAT (PmorId, FonctionId, DateDebut) VALUES (2, 1, '2013-09-01') ;
INSERT INTO MANDAT (PmorId, FonctionId, DateDebut) VALUES (2, 2, '2013-09-01') ;
INSERT INTO MANDAT (PmorId, FonctionId, DateDebut) VALUES (2, 3, '2013-10-15') ;
INSERT INTO MANDAT (PmorId, FonctionId, DateDebut) VALUES (6, 1, '2013-01-01') ;
INSERT INTO MANDAT (PmorId, FonctionId, DateDebut) VALUES (4, 2, '2013-09-20') ;
INSERT INTO MANDAT (PmorId, FonctionId, DateDebut) VALUES (4, 4, '2013-09-21') ;
INSERT INTO MANDAT (PmorId, FonctionId, DateDebut) VALUES (8, 5, '2013-09-21') ;
 
SELECT ''AS MANDAT,* FROM MANDAT ;
 
SELECT ''AS MANDAT, PsnNom, Fonctionlibelle, DateDebut AS Depuis  
         FROM MANDAT AS x JOIN PERSONNE AS y ON x.PmorId = y.PsnId
                          JOIN FONCTION AS z ON x.FonctionId = z.FonctionId ;

TABLE REPRESENTATION

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 TABLE REPRESENTATION 
(
   PmorId               INT                  NOT NULL,
   FonctionId           INT                  NOT NULL,
   PphyId               INT                  NOT NULL,
   AdrCourrielId        INT                  NOT NULL,
   CompteId             INT                  NOT NULL,
   DateDebut            DATETIME             NOT NULL,
   DateFin              DATETIME             NOT NULL DEFAULT '2014-12-31',
   CONSTRAINT REPRESENTATION_PK PRIMARY KEY (PmorId, FonctionId, PphyId),
   CONSTRAINT REPRESENTATION_AK UNIQUE (PmorId, AdrCourrielId, CompteId),
   CONSTRAINT REPRESENTATION_P_PHYSIQUE_FK FOREIGN KEY (PphyId)
      REFERENCES P_PHYSIQUE,
   CONSTRAINT REPRESENTATION_MANDAT_FK FOREIGN KEY (PmorId, FonctionId)
      REFERENCES MANDAT ON DELETE CASCADE,
   CONSTRAINT REPRESENTATION_COMPTE_FK FOREIGN KEY (PmorId, AdrCourrielId, CompteId)
      REFERENCES COMPTE
) ;
 
INSERT INTO REPRESENTATION (PphyId, PmorId, FonctionId, AdrCourrielId, CompteId, DateDebut) VALUES (1, 2, 1, 1, 1, '2013-09-03') ;
 
SELECT ''AS REPRESENTATION, * FROM REPRESENTATION ;

A titre d’exemple et de complément, avec la requête suivante, on apprend que Raoul exerce la fonction de Président chez Volfoni SA depuis le 03/09/2013 et que pour cette fonction il utilise le compte (login) Volfo01 et l’adresse courriel "volfo1@volfo.com" :

Code SQL :

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

1
2
3
4
5
6
SELECT ''AS REPRESENTATION, y.PsnNom AS IndividuNom, z.PsnNom AS EntiteNom, Fonctionlibelle, DateDebut AS Depuis, u.Login, v.AdresseCourriel  
         FROM REPRESENTATION AS x JOIN PERSONNE AS y ON x.PphyId = y.PsnId
                                  JOIN PERSONNE AS z ON x.PmorId = z.PsnId 
                                  JOIN FONCTION AS t ON x.FonctionId = t.FonctionId
                                  JOIN COMPTE AS u ON x.PmorId = u.PsnId AND x.AdrCourrielId = u.AdrCourrielId AND x.CompteId = u.CompteId
                                  JOIN ADR_COURRIEL AS v ON u.PsnId = v.PsnId AND u.AdrCourrielId = v.AdrCourrielId ;

**fsmrel** · 24/01/2014, 16h01

A propos des contraintes d’exclusion

Il serait redoutable qu’une personne morale et une personne physique aient le même identifiant, d’où la contrainte d’exclusion :

En SQL, à défaut d’assertion, on utilisera des triggers pour contrôler les opérations d’INSERT et UPDATE.

Version SQL Server 2005 (on peut évidemment préférer utiliser EXISTS ou autre parfum ou turbo au lieu de INTERSECT) :

Contrôle des ajouts dans la table P_MORALE :

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
CREATE TRIGGER P_MORALE_TRIGGER_INSERT ON P_MORALE INSTEAD OF INSERT AS
       DECLARE @N AS INT
 
SET @N = (SELECT COUNT(*) 
          FROM      
             (SELECT PphyId
              FROM   P_PHYSIQUE
             INTERSECT
              SELECT PmorId 
              FROM   INSERTED
             ) AS x
        )    
IF @N > 0 
    BEGIN
        RAISERROR ('Une personne morale ne peut pas avoir un identifiant déjà attribué à une personne physique...',15,1) 
    END
ELSE
    BEGIN
        INSERT INTO P_MORALE 
            SELECT * FROM INSERTED
    END ;

Contrôle des ajouts dans la table P_PHYSIQUE :

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
CREATE TRIGGER P_PHYSIQUE_TRIGGER_INSERT ON P_PHYSIQUE INSTEAD OF INSERT AS
       DECLARE @N AS INT
 
SET @N = (SELECT COUNT(*) 
          FROM      
             (SELECT PmorId
              FROM   P_MORALE
             INTERSECT
              SELECT PphyId 
              FROM   INSERTED
             ) AS x
        )    
IF @N > 0 
    BEGIN
        RAISERROR ('Une personne physique ne peut pas avoir un identifiant déjà attribué à une personne morale...',15,1) 
    END
ELSE
    BEGIN
        INSERT INTO P_PHYSIQUE 
            SELECT * FROM INSERTED
    END ;

On pourra vérifier que tenter un insert délictueux dans la table PERSONNE est voué à l’échec.

Personne physique, morale et utilisateur

Schéma

Discussions similaires

Partager

Partager