Ave,

Prenons le cas du thème suivant, à partir d’une discussion créée par deedolith :

Un utilisateur peut rencontrer des formations de monstres et acquérir des trophées (initialement frénésies), possédés par ces derniers et proposés par les formations.

Un MCD :

Figure 1

Code SQL produit par Looping :

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
CREATE TABLE Formation(
   FormationId SMALLINT,
   FormationNom VARCHAR(50) NOT NULL,
   CONSTRAINT Formation_PK PRIMARY KEY(FormationId),
   CONSTRAINT Formation_AK UNIQUE(FormationNom)
);
CREATE TABLE Utilisateur(
   UtilisateurId SMALLINT,
   UtilisateurNom VARCHAR(50) NOT NULL,
   CONSTRAINT Utilisateur_PK PRIMARY KEY(UtilisateurId)
);
CREATE TABLE Trophee(
   TropheeId SMALLINT,
   TropheeNom VARCHAR(50) NOT NULL,
   CONSTRAINT Trophee_PK PRIMARY KEY(TropheeId)
);
CREATE TABLE Proposer(
   FormationId SMALLINT,
   TropheeId SMALLINT,
   CONSTRAINT Proposer_PK PRIMARY KEY(FormationId, TropheeId),
   CONSTRAINT Proposer_Formation_FK FOREIGN KEY(FormationId) REFERENCES Formation(FormationId),
   CONSTRAINT Proposer_Trophee_FK FOREIGN KEY(TropheeId) REFERENCES Trophee(TropheeId)
);
CREATE TABLE Rencontrer(
   FormationId SMALLINT,
   UtilisateurId SMALLINT,
   CONSTRAINT Rencontrer_PK PRIMARY KEY(FormationId, UtilisateurId),
   CONSTRAINT Rencontrer_Formation_FK FOREIGN KEY(FormationId) REFERENCES Formation(FormationId),
   CONSTRAINT Rencontrer_Utilisateur_FK FOREIGN KEY(UtilisateurId) REFERENCES Utilisateur(UtilisateurId)
);
CREATE TABLE AcqTrophee(
   FormationId SMALLINT,
   UtilisateurId SMALLINT,
   TropheeId SMALLINT,
   CONSTRAINT AcqTrophee_PK PRIMARY KEY(FormationId, UtilisateurId, TropheeId),
   CONSTRAINT AcqTrophee_Formation_FK FOREIGN KEY(FormationId) REFERENCES Formation(FormationId),
   CONSTRAINT AcqTrophee_Utilisateur_FK FOREIGN KEY(UtilisateurId) REFERENCES Utilisateur(UtilisateurId),
   CONSTRAINT AcqTrophee_Trophee_FK FOREIGN KEY(TropheeId) REFERENCES Trophee(TropheeId)
);

Règle de gestion :

(RG1) Pour être acquis, un trophée doit avoir été proposé par une formation.

Au vu de la figure 1, un utilisateur peut acquérir des trophées qui ne sont pas proposés par des formations. Pour que soit respectée la règle de gestion, mettons en oeuvre une contrainte d’inclusion ayant pour source AcqTrophee, pour cible Proposer et pour pivot la paire {Formation, Trophée} :

Figure 2
 

Autre règle de gestion :

(RG2) Pour acquérir un trophée, l’utilisateur doit avoir rencontré une formation proposant ce trophée.

Au vu de la figure 2, l’utilisateur peut acquérir des trophées auprès de formations qu’il n’a pas rencontrées. Pour que soit respectée la règle de gestion, ajoutons une contrainte d’inclusion ayant pour source AcqTrophee, pour cible Rencontrer et pour pivot la paire {Formation, Utilisateur} :

Figure 3
 

Côté MCD tout va bien, mais le code SQL reste muet quant à ces règles de gestion...

A nous de faire quelque chose, retroussons nos manches. Proposition concernant la règle RG1 :

Dans l’instruction "CREATE TABLE AcqTrophee", remplacer les clés étrangères :

AcqTrophee_Formation_FK FOREIGN KEY(FormationId) REFERENCES Formation(FormationId),
AcqTrophee_Trophee_FK FOREIGN KEY(TropheeId) REFERENCES Trophee(TropheeId)

Par la clé étrangère suivante :

AcqTrophee_Proposer_FK FOREIGN KEY(FormationId, TropheeId) REFERENCES Proposer(FormationId, TropheeId).

Ceci est possible, car cette nouvelle clé étrangère traduit la contrainte suivante :

(CT1) AcqTrophee{FormationId, TropheeId} ⊆ Proposer{FormationId, TropheeId}

Par projection (propriété de l’algèbre relationnelle), il existe une contrainte

(CT2) AcqTrophee{FormationId} ⊆ Proposer{FormationId}

Par ailleurs, la contrainte de clé étrangère Proposer_Formation_FK (cf. l’instruction "CREATE TABLE Proposer") traduit la contrainte suivante :

(CT3) Proposer {FormationId} ⊆ Formation{FormationId}

Et par transitivité

(CT4) AcqTrophee{FormationId} ⊆ Proposer{FormationId} ⊆ Formation{FormationId}

=>

(CT5) AcqTrophee{FormationId} ⊆ Formation{FormationId}

Où Formation{FormationId} est la traduction de la clé étrangère Proposer_Formation_FK de la table Proposer ; ainsi la clé étrangère AcqTrophee_Formation_FK de la table AcqTrophee fait double emploi avec Proposer_Formation_FK et peut donc être passée au rasoir d’Occam).

Par ailleurs, de la contrainte CT1, encore par projection on obtient la contrainte

(CT6) AcqTrophee{TropheeId} ⊆ Proposer{TropheeId}

En outre, la contrainte de clé étrangère Proposer_Trophee_FK (cf. l’instruction "CREATE TABLE Proposer") traduit la contrainte suivante :

(CT7) Proposer {TropheeId} ⊆ Trophee{TropheeId}

Et par transitivité

(CT8) AcqTrophee{TropheeId} ⊆ Proposer{TropheeId} ⊆ Trophee{TropheeId}

=>

(CT9) AcqTrophee{TropheeId} ⊆ Trophee{TropheeId}

Où Trophee{TropheeId} est la traduction de la clé étrangère Proposer_Trophee_FK de la table Proposer ; ainsi la clé étrangère AcqTrophee_Trophee_FK de la table AcqTrophee fait double emploi avec Proposer_Trophee_FK et peut donc être passée au rasoir d’Occam).

Ainsi, du fait de CT5 et CT9, les clés étrangères AcqTrophee_Formation_FK et AcqTrophee_Trophee_FK présentes dans la table AcqTrophee peuvent y être remplacées par la seule clé étrangère AcqTrophee_Proposer_FK initialement proposée.

Cas de la règle de gestion RG2 :

Dans l’instruction "CREATE TABLE AcqTrophee", remplacer les clés étrangères :

AcqTrophee_Formation_FK FOREIGN KEY(FormationId) REFERENCES Formation(FormationId) /* déjà fait */
AcqTrophee_Utilisateur_FK FOREIGN KEY(UtilisateurId) REFERENCES Utilisateur(UtilisateurId)

Par la clé étrangère suivante :

AcqTrophee_Rencontrer_FK FOREIGN KEY(FormationId, UtilisateurId) REFERENCES Rencontrer(FormationId, UtilisateurId).

Ceci est possible, car cette nouvelle clé étrangère traduit la contrainte suivante :

(CU1) AcqTrophee{FormationId, UtilisateurId} ⊆ Rencontrer{FormationId, UtilisateurId}

Et par projection, il existe une contrainte

(CU2) AcqTrophee{FormationId} ⊆ Rencontrer{FormationId}

Par ailleurs, la contrainte de clé étrangère Rencontrer_Formation_FK (cf. l’instruction "CREATE TABLE Rencontrer") traduit la contrainte suivante :

(CU3) Rencontrer{FormationId} ⊆ Formation{FormationId}

Et par transitivité

(CU4) AcqTrophee{FormationId} ⊆ Rencontrer{FormationId} ⊆ Formation{FormationId}

=>

[indent](CU5) AcqTrophee{FormationId} ⊆ Formation{FormationId}

Où Formation{FormationId} est la traduction de la clé étrangère Recontrer_Formation_FK de la table Rencontrer ; ainsi la clé étrangère AcqTrophee_Formation_FK de la table AcqTrophee fait double emploi avec Rencontrer_Formation_FK et peut donc être passée au rasoir d’Occam.

Par ailleurs, de la contrainte CU1, encore par projection on obtient la contrainte

(CU6) AcqTrophee{UtilisateurId} ⊆Rencontrer{UtilisateurId}

En outre , la contrainte de clé étrangère Rencontrer_Utilisateur_FK (cf. l’instruction "CREATE TABLE Rencontrer") traduit la contrainte suivante :

(CU7) Rencontrer{UtilisateurId} ⊆ Utilisateur{UtilisateurId}

Et par transitivité

(CU8) AcqTrophee{UtilisateurId} ⊆ Rencontrer{UtilisateurId} ⊆ Utilisateur{UtilisateurId}

=>

(CU9) AcqTrophee{UtilisateurId} ⊆ Utilisateur{UtilisateurId}

Où Utilisateur{UtilisateurId} est la traduction de la clé étrangère Rencontrer_Utilisateur_FK de la table Rencontrer ; ainsi la clé étrangère AcqTrophee_Utilisateur_FK de la table AcqTrophee fait double emploi avec Rencontrer_Utilisateur_FK et peut donc être passée au rasoir d’Occam).

Ainsi, du fait de CU5 et CU9, les clés étrangères Rencontrer_Formation_FK et Rencontrer_Utilisateur_FK présentes dans la table AcqTrophee peuvent y être remplacées par la seule clé étrangère AcqTrophee_Rencontrer_FK initialement proposée.

Conclusion :

L’instruction CREATE TABLE AcqTrophee devient la suivante

Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
CREATE TABLE AcqTrophee(
   UtilisateurId SMALLINT,
   FormationId SMALLINT,
   TropheeId SMALLINT,
   CONSTRAINT AcqTrophee_PK PRIMARY KEY(UtilisateurId, FormationId, TropheeId),
   CONSTRAINT AcqTrophee_Proposer_FK FOREIGN KEY(TropheeId, FormationId) REFERENCES Proposer(TropheeId, FormationId),
   CONSTRAINT AcqTrophee_Rencontrer_FK FOREIGN KEY(UtilisateurId, FormationId) REFERENCES Rencontrer(UtilisateurId, FormationId)
);

Et les règles de gestion RG1 et RG2 sont respectées, sans mise en oeuvre de triggers. 


Il y a une règle de gestion supplémentaire :

(RG3) Un utilisateur peut acquérir plusieurs trophées, mais pour un utilisateur et un trophée donnés une acquisition ne concerne qu’une seule formation parmi celles que l’utilisateur a rencontrées.
Le MCD devient (mise en oeuvre d’une CIF) :
 

Figure 4
 

L’instruction CREATE TABLE AcqTrophee devient la suivante (clé primaire limitée à la paire {UtilisateurId, TropheeId]) :

Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
CREATE TABLE AcqTrophee(
   UtilisateurId SMALLINT,
   FormationId SMALLINT,
   TropheeId SMALLINT,
   CONSTRAINTAcqTrophee_PK PRIMARY KEY(UtilisateurId, TropheeId),
   CONSTRAINT AcqTrophee_Rencontrer_FK FOREIGN KEY(UtilisateurId, FormationId) REFERENCES Rencontrer(UtilisateurId, FormationId),
   CONSTRAINT AcqTrophee_Proposer_FK FOREIGN KEY(TropheeId, FormationId) REFERENCES Proposer(TropheeId, FormationId)
);

J’espère ne pas m’être planté dans mes copier/coller...

Une fois de plus si vous avec connaissance d’autres types de cas où interviennent les contraintes d’inclusion, merci de me les proposer !

François