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).
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).
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 ;
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.
Vous avez un bloqueur de publicités installé.
Le Club Developpez.com n'affiche que des publicités IT, discrètes et non intrusives.
Afin que nous puissions continuer à vous fournir gratuitement du contenu de qualité, merci de nous soutenir en désactivant votre bloqueur de publicités sur Developpez.com.
Partager