Discussion :

[fsmrel]

Salve Capitaine,

OK, quoi qu'il en soit, une contrainte d'inclusion se traduit le plus souvent par une contrainte SQL de type "reference" qui ne saurait vérifier qu'une période est incluse dans une autre, un trigger s'imposera de toute façon ici.

Peux-tu donner un exemple ?

[escartefigue]

Je pense à un schéma comme celui-ci, dans lequel la contrainte d'inclusion ne saurait être résolue d'un point de vue SQL par une simple contrainte de type "REFERENCE" :






Le professeur P1 maitrise la matière M1 de D1 à D2, une simple contrainte REFERENCE ne pourra pas vérifier qu'il enseigne M1 dans telle classe pour une période comprise entre D1 et D2

Il faudra donc créer un TRIGGER ou une UDF associée à une contrainte CHECK

[fsmrel]

Ave Capitaine,

Relis la définition « canonique » de la contrainte d’inclusion, fournie par l’afcet (cf. post #1), et celle présente dans l’ouvrage de Dominique Nanci et Bernard Espinasse, Ingénierie des Systèmes d’information : Merise, au chapitre 13, page 305 (où l’on cause des triggers pour garantir la contrainte), vois Chez DVP qui fournit l’ouvrage : « III-C-3-ac. Contraintes d'inclusion de relations sur d'autres relations ».

Maintenant, examinons le code SQL produit par Looping (j’ai repris ton MCD à un pouième près) :

CREATE TABLE PR_prof(
   PR_ident INT,
   PR_nom VARCHAR(24) NOT NULL,
   CONSTRAINT PR_prof_PK PRIMARY KEY(PR_ident)
);

CREATE TABLE MA_matiere(
   MA_ident INT,
   MA_code CHAR(4) NOT NULL,
   MA_libelle VARCHAR(24) NOT NULL,
   CONSTRAINT MA_matiere_PK PRIMARY KEY(MA_ident),
   CONSTRAINT MA_matiere_AK UNIQUE(MA_code)
);

CREATE TABLE CL_classe(
   CL_ident INT,
   CL_code CHAR(4) NOT NULL,
   CONSTRAINT CL_classe_PK PRIMARY KEY(CL_ident),
   CONSTRAINT CL_classe_AK UNIQUE(CL_code)
);

CREATE TABLE EN_enseigner(
   PR_ident INT,
   MA_ident INT,
   CL_ident INT,
   CA_date DATE,
   EN_dtfin DATE NOT NULL,
   CONSTRAINT EN_enseigner_PK PRIMARY KEY(PR_ident, MA_ident, CL_ident, CA_date),
   CONSTRAINT EN_enseigner_PR_prof_FK FOREIGN KEY(PR_ident) REFERENCES PR_prof(PR_ident),
   CONSTRAINT EN_enseigner_MA_matiere_FK FOREIGN KEY(MA_ident) REFERENCES MA_matiere(MA_ident),
   CONSTRAINT EN_enseigner_CL_classe_FK FOREIGN KEY(CL_ident) REFERENCES CL_classe(CL_ident)
);

CREATE TABLE MT_maitriser(
   PR_ident INT,
   MA_ident INT,
   CA_date DATE,
   MT_dtfin DATE,
   CONSTRAINT MT_maitriser_PK PRIMARY KEY(PR_ident, MA_ident, CA_date),
   CONSTRAINT MT_maitriser_PR_prof_FK FOREIGN KEY(PR_ident) REFERENCES PR_prof(PR_ident),
   CONSTRAINT MT_maitriser_MA_matiere_FK FOREIGN KEY(MA_ident) REFERENCES MA_matiere(MA_ident)
);

Si je respecte stricto sensu les définitions des auteurs que j’ai cités, en terme de contrainte d’inclusion, je suis conforme quand je passe au code suivant, dans lequel les deux clés étrangères de la table EN_enseigner faisant référence aux tables PR_prof et MA_matiere ont été remplacées par une seule clé étrangère faisant référence à la table MT_maitriser :

CREATE TABLE PR_prof(
   PR_ident INT,
   PR_nom VARCHAR(24) NOT NULL,
   CONSTRAINT PR_prof_PK PRIMARY KEY(PR_ident)
);

CREATE TABLE MA_matiere(
   MA_ident INT,
   MA_code CHAR(4) NOT NULL,
   MA_libelle VARCHAR(24) NOT NULL,
   CONSTRAINT MA_matiere_PK PRIMARY KEY(MA_ident),
   CONSTRAINT MA_matiere_AK UNIQUE(MA_code)
);

CREATE TABLE CL_classe(
   CL_ident INT,
   CL_code CHAR(4) NOT NULL,
   CONSTRAINT CL_classe_PK PRIMARY KEY(CL_ident),
   CONSTRAINT CL_classe_AK UNIQUE(CL_code)
);

CREATE TABLE MT_maitriser(
   PR_ident INT,
   MA_ident INT,
   CA_date DATE,
   MT_dtfin DATE,
   CONSTRAINT MT_maitriser_PK PRIMARY KEY(PR_ident, MA_ident, CA_date),
   CONSTRAINT MT_maitriser_PR_prof_FK FOREIGN KEY(PR_ident) REFERENCES PR_prof(PR_ident),
   CONSTRAINT MT_maitriser_MA_matiere_FK FOREIGN KEY(MA_ident) REFERENCES MA_matiere(MA_ident)
);

CREATE TABLE EN_enseigner(
   PR_ident INT,
   MA_ident INT,
   CL_ident INT,
   CA_date DATE,
   EN_dtfin DATE NOT NULL,
   CONSTRAINT EN_enseigner_PK PRIMARY KEY(PR_ident, MA_ident, CL_ident, CA_date),
   CONSTRAINT EN_enseigner_CL_classe_FK FOREIGN KEY(CL_ident) REFERENCES CL_classe(CL_ident),
   CONSTRAINT EN_enseigner_MT_maitriser_FK 
      FOREIGN KEY(PR_ident, MA_ident, CA_date) REFERENCES  MT_maitriser(PR_ident, MA_ident, CA_date)
);

Dans ces conditions, pas besoin de triggers. Quand tu écris :

« Le professeur P1 maîtrise la matière M1 de D1 à D2, une simple contrainte REFERENCE ne pourra pas vérifier qu'il enseigne M1 dans telle classe pour une période comprise entre D1 et D2. »

C’est évident qu’une clé étrangère ne suffira pas, parce qu’en fait la contrainte à prendre en compte n’est plus une contrainte d'inclusionau sens afcet, mais une contrainte temporelle. Les contraintes temporelles sont traitées dans l’ouvrage de Chris Date, Database Design and Relational Theory Normal Forms and All That Jazz, ou dans Temporal Data and the Relational Model, en collaboration avec Hugh Darwen et Nikos Lorentzos. De mon côté, j’ai rédigé une synthèse dans le chapitre 6 de mon article Bases de données relationnelles et normalisation : de la première à la sixième forme normale.

Il est clair qu’une contrainte temporelle doit faire l’objet d’un trigger quand on utilise SQL !

[fsmrel]

Salve,

Je reprends un exemple de participation, Figure 13.37 dans Ingénierie des Systèmes d’information : Merise.

Dans l’ouvrage, le MCD est conforme. Si je le reprends avec Looping en remplaçant malicieusement une cardinalité 0,1 (voire les deux), dans le MCD ci-dessous, y a comme une contradiction. On a une contrainte de partitionnement, pourtant Looping n’interdit pas qu’une commande soit passée à la fois par un client e un service...

[escartefigue]

Il est clair qu’une contrainte temporelle doit faire l’objet d’un trigger quand on utilise SQL !

Nous sommes d'accord, tu es toujours plus précis que moi sur le vocabulaire et je te remercie de cette précision sémantique (même si ça peut sembler paradoxal dans la mesure où je suis également correcteur orthographique )

Quoique - je me rattrape comme je peux - d'un trigger ou d'une UDF

[fsmrel]

Revenons à une situation saine, la cardinalité 0,1 retrouve sa place sur la patte connectant COMMANDE et PASSER :



Code SQL produit par Looping :

CREATE TABLE CLIENT(
   ClientId INT,
   ClientNom VARCHAR(32) NOT NULL,
   CONSTRAINT CLIENT_PK PRIMARY KEY(ClientId)
);

CREATE TABLE SERVICE(
   ServiceId INT,
   ServiceNom VARCHAR(32) NOT NULL,
   CONSTRAINT SERVICE_PK PRIMARY KEY(ServiceId),
   CONSTRAINT SERVICE_AK UNIQUE(ServiceNom)
);

CREATE TABLE COMMANDE(
   CommandeId INT,
   CommandeNumero VARCHAR(16) NOT NULL,
   ClientId INT,
   ServiceId INT,
   CONSTRAINT COMMANDE_PK PRIMARY KEY(CommandeId),
   CONSTRAINT COMMANDE_AK UNIQUE(CommandeNumero),
   CONSTRAINT COMMANDE_CLIENT_FK FOREIGN KEY(ClientId) REFERENCES CLIENT(ClientId),
   CONSTRAINT COMMANDE_SERVICE_FK FOREIGN KEY(ServiceId) REFERENCES SERVICE(ServiceId)
);

Pour garantir la contrainte de partitionnement, il suffit d’jouter une contrainte CHECK (SQL) :

Code :

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

1
2
3
4
ALTER TABLE COMMANDE
    ADD CONSTRAINT COMMANDE_PARTITION CHECK 
     (ClientId is not null AND ServiceId is null
        OR ClientId is  null AND ServiceId is not null) ;

Cela dit, si on remplace au moins une des deux cardinalités 0,1 par 0,n, on est refaits, il faudra un trigger, j’y reviendrai.

[fsmrel]

Salve,

Supposons maintenant que les clients et les services puissent faire des commandes groupées. Les cardinalités 0,1 portées par les pattes connectant COMMANDE et les associations PASSER et PROVENIR deviennent 0,n :

La contrainte entre les associations PASSER et PREVENIR est ici une contrainte d’exclusion. Cette fois-ci, il faut jouer du trigger.

Les tables :

CREATE TABLE CLIENT
(
    ClientId INT
  , ClientNom VARCHAR(32) NOT NULL
  , CONSTRAINT CLIENT_PK PRIMARY KEY(ClientId)
);

CREATE TABLE SERVICE
(
    ServiceId INT
 ,  ServiceNom VARCHAR(32) NOT NULL
 ,  CONSTRAINT SERVICE_PK PRIMARY KEY(ServiceId)
 ,  CONSTRAINT SERVICE_AK UNIQUE(ServiceNom)
);

CREATE TABLE COMMANDE
(
    CommandeId INT
 ,  CommandeNumero VARCHAR(32) NOT NULL
 ,  CONSTRAINT COMMANDE_PK PRIMARY KEY(CommandeId)
 ,  CONSTRAINT COMMANDE_AK UNIQUE(CommandeNumero)
);

CREATE TABLE PROVENIR
(
    CommandeId INT
 ,  ServiceId INT
 ,  CONSTRAINT PROVENIR_PK PRIMARY KEY(ServiceId, CommandeId)
 ,  CONSTRAINT PROVENIR_COMMANDE_FK FOREIGN KEY(CommandeId) REFERENCES COMMANDE(CommandeId)
 ,  CONSTRAINT PROVENIR_SERVICE_FK FOREIGN KEY(ServiceId) REFERENCES SERVICE(ServiceId)
);

CREATE TABLE PASSER
(
   CommandeId INT
 , ClientId INT
 , CONSTRAINT PASSER_PK PRIMARY KEY(ClientId, CommandeId)
 , CONSTRAINT PASSER_CLIENT_FK FOREIGN KEY(ClientId) REFERENCES CLIENT(ClientId)
 , CONSTRAINT PASSER_COMMANDE_FK FOREIGN KEY(CommandeId) REFERENCES COMMANDE(CommandeId)
); 

Les triggers pour contrôler les INSERT :

GO

CREATE TRIGGER EXCLUSION_TRIGGER_INSERT_PASSER ON PASSER 
AFTER INSERT AS

DECLARE @n as INT ;
DECLARE @Engueulade AS VARCHAR(254)

SET @n =
(SELECT COUNT(*) 
FROM INSERTED
WHERE CommandeId IN (SELECT CommandeId FROM PROVENIR))

IF @n > 0
  BEGIN
    SET @Engueulade = 'Commande déjà associée à un service.'
    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
    ROLLBACK
  END ;
GO

CREATE TRIGGER EXCLUSION_TRIGGER_INSERT_PROVENIR ON PROVENIR 
AFTER INSERT AS

DECLARE @n as INT ;
DECLARE @Engueulade AS VARCHAR(254)

SET @n =
(SELECT COUNT(*) 
FROM INSERTED
WHERE CommandeId IN (SELECT CommandeId FROM PASSER))

IF @n > 0
  BEGIN
    SET @Engueulade = 'Commande déjà associée à un client.'
    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
    ROLLBACK
  END ;
GO 

Pour tester :

INSERT INTO CLIENT VALUES
    (1, 'Fernand')
  , (2, 'Raoul')
  , (3, 'Paul')

SELECT * FROM CLIENT ;

INSERT INTO SERVICE VALUES
    (1, 'Le bowling')
  , (2, 'Dugoineau')
  , (3, 'Chez Tomate')

SELECT * FROM SERVICE ;

INSERT INTO COMMANDE VALUES
    (1, 'cde01_scotch')
  , (2, 'cde02_scotch')
  , (3, 'cde03_char Patton')
  , (4, 'cde04_jus de pomme')
  , (5, 'cde05_scotch')

SELECT * FROM COMMANDE ;

INSERT INTO PASSER VALUES
    (2, 1)   -- Fernand passe commande de scotch 
 ,  (3, 1)   -- Fernand passe commande de char Patton 

SELECT * FROM PASSER ;

INSERT INTO PROVENIR VALUES
    (1, 2)   -- Le bowling passe commande de scotch 
-- ,  (2, 2)   -- Le bowling passe la même commande de scotch que Fernand, viol de la contrainte

SELECT * FROM PROVENIR ;

INSERT INTO PASSER VALUES
    (1, 2)   -- Raoul passe commande de scotch, viol de la contrainte 

SELECT * FROM PROVENIR ;

[escartefigue]

Bonjour François,

Toutes ces réflexions mériteraient un billet de blog ou mieux, un tuto.

J'avais d'ailleurs moi même envisagé de faire un document sur les différentes façons de traduire une contrainte au niveau conceptuel en code SQL, mais c'est un travail de longue haleine.

[fsmrel]

Bonjour Capitaine,

Tu as raison, ces réflexions mériteraient quelque chose du genre tuto, mais je me livre d’abord ici à un travail de fond, pour voir jusqu’où on peut aller pour inciter Paprick à intégrer dans Looping la génération de triggers (ou clés étrangères ) quand c’est possible, pour les contraintes dont il a fourni la liste. Cela restera peut être un rêve, une utopie, va savoir, mais au moins on aura essayé. Ce bel outil qu’est Looping mérite d’être l’objet de tous nos soins.

N.B. Dans mon post précédent, j’ai donc remplacé les cardinalités 0,1 par 0,n, mais si je passe à 1,n, Looping ne bronche pas et conserve sans sourciller la contrainte d’exclusion contradictoire. Je parie une poignée de mains contre poignée de porte que ça ne demeurera pas.

[fsmrel]

Bonsoir,

Dans le post #27, j’ai proposé des triggers pou les INSERT, mais ils sont valables pour les UPDATE, donc :

Code :

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

1
2
3
4
5
6
CREATE TRIGGER EXCLUSION_TRIGGER_PASSER ON PASSER 
AFTER INSERT, UPDATE AS
...
CREATE TRIGGER EXCLUSION_TRIGGER_PROVENIR ON PROVENIR 
AFTER INSERT, UPDATE AS
...

[Paprick]

Bonsoir François, bonsoir Capitaine !

Je reviens sur nos histoires de codage de la contrainte Inclusion : il me semble que vous aviez mis en œuvre un cas où un simple ALTER TABLE avec modification d'une clé étrangère suffisait à traiter cette contrainte d'Inclusion ...

Est-ce que cela vous rappelle quelque chose ?

[escartefigue]

Bonjour Paprick

Je ne vois pas à quoi tu fais référence.
Comme dit plus haut, les contraintes temporelles sont à traiter par trigger.
Les contraintes d'inclusion simples sont traitées par ALTER TABLE sans besoin de modifier la FK.

[Paprick]

Bonsoir Capitaine,

Les contraintes d'inclusion simples sont traitées par ALTER TABLE sans besoin de modifier la FK.

Peux-tu me redonner un exemple ?
Merci !

[escartefigue]

Voir la réponse n°4 de ce fil de discussion :

https://www.developpez.net/forums/d2.../#post11906078

[Paprick]

Merci Capitaine pour cet exemple.
J'avais cependant souvenir d'un autre exemple pour lequel la clé étarngère avait été adaptée pour gérer une contrainte d'inclusion... mais peut-être me trompe-je...
François : ça ne te parle pas non plus ?

[fsmrel]

Merci Capitaine pour cet exemple.
J'avais cependant souvenir d'un autre exemple pour lequel la clé étarngère avait été adaptée pour gérer une contrainte d'inclusion... mais peut-être me trompe-je...
François : ça ne te parle pas non plus ?

 
J'ai déjà fouillé dans mes fichiers (c'est le b... désorganisé) et n'ai rien trouvé...

En contrepartie je ferai des propositions (honnêtes) pour l'élimination des triggers (inclusion) chez Nanci (figures 13.38, 13.39).

[fsmrel]

@Paprick

Dans cette discussion, j'ai écrit (cf. post #16) :

Il est évident que le plus simple eut été de déclarer une étrangère dans le CREATE TABLE de la table ENSEIGNER (cf. post #1)

On se rapproche ?

[Paprick]

On se rapproche ?

Oui ! C'est très intéressant !
En résumé, peut-on généraliser la règle suivante ?

Lorsque 2 classes d'entités E1 et E2 sont reliées par 2 associations A1 et A2 multiples de part et d'autre, en cas de contrainte d'inclusion de A1 vers A2, les 2 clés étrangères de A1 (provenant de E1 et E2) sont supprimées et remplacées par la clé étrangère provenant de A2 (qui reprend, en les concaténant, les 2 clés étrangères supprimées).

Sachant qu'un bon ALTER TABLE peut régler ça suite à la génération basique de Looping (à condition, bien sûr, de nommer les contraintes pour pouvoir les supprimer).
J'ai bon ?

[fsmrel]

Hello !

peut-on généraliser la règle suivante ?

Lorsque 2 classes d'entités E1 et E2 sont reliées par 2 associations A1 et A2 multiples de part et d'autre, en cas de contrainte d'inclusion de A1 vers A2, les 2 clés étrangères de A1 (provenant de E1 et E2) sont supprimées et remplacées par la clé étrangère provenant de A2 (qui reprend, en les concaténant, les 2 clés étrangères supprimées).

Très bonne question On ne peut pas invalider ta proposition.

Partons en effet de la proposition de l’AFCET à prendre comme axiome :
 

 
Dans cet exemple (que j’ai envie de qualifier de canonique), le pivot est ici composé des entités-types ENSEIGNANT et MATIERE. L’association ENSEIGNE (portée) est soumise à une inclusion dans l’association SAIT_ENSEIGNER (cible) vis-à-vis du pivot {ENSEIGNANT, MATIERE}. Aucune occurrence du pivot ne peut participer à la portée ENSEIGNE sans participer à la cible SAIT_ENSEIGNER. Pour la petite histoire, le pivot contient l’ensemble des entités-types et il est donc implicite.
 
A cette occasion, illustrons avec les exemples proposés par Tabourier et Nanci :
 

 

 
Pour en venir au passage au MLD et à SQL :
 
Sans tenir compte de la contrainte d’inclusion :
 

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
CREATE TABLE Classe
(
   ClasseId SMALLINT,
   CONSTRAINT Classe_PK PRIMARY KEY(ClasseId)
);
CREATE TABLE Enseignant
(
   EnseignantId SMALLINT,
   CONSTRAINT Enseignant_PK PRIMARY KEY(EnseignantId)
);
CREATE TABLE Matiere
(
   MatiereId SMALLINT,
   CONSTRAINT Matiere_PK PRIMARY KEY(MatiereId)
);
CREATE TABLE Sait_Enseigner
(
   EnseignantId SMALLINT,
   MatiereId SMALLINT,
   CONSTRAINT Sait_Enseigner_PK PRIMARY KEY(EnseignantId, MatiereId),
   CONSTRAINT Sait_Enseigner_Enseignant_FK FOREIGN KEY(EnseignantId) REFERENCES Enseignant(EnseignantId),
   CONSTRAINT Sait_Enseigner_Matiere_FK FOREIGN KEY(MatiereId) REFERENCES Matiere(MatiereId)
);
CREATE TABLE Enseigne
(
   EnseignantId SMALLINT,
   ClasseId SMALLINT,
   MatiereId SMALLINT,
   CONSTRAINT Enseigne_PK PRIMARY KEY(EnseignantId, ClasseId, MatiereId),
   CONSTRAINT Enseigne_Enseignant_FK FOREIGN KEY(EnseignantId) REFERENCES Enseignant(EnseignantId),
   CONSTRAINT Enseigne_Classe_FK FOREIGN KEY(ClasseId) REFERENCES Classe(ClasseId),
   CONSTRAINT Enseigne_Matiere_FK FOREIGN KEY(MatiereId) REFERENCES Matiere(MatiereId)
);

 
En tenant compte de la contrainte d’inclusion :
 

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
CREATE TABLE Enseignant(
   EnseignantId SMALLINT,
   CONSTRAINT Enseignant_PK PRIMARY KEY(EnseignantId)
);
 
CREATE TABLE Classe(
   ClasseId SMALLINT,
   CONSTRAINT Classe_PK PRIMARY KEY(ClasseId)
);
 
CREATE TABLE Matiere(
   MatiereId SMALLINT,
   CONSTRAINT Matiere_PK PRIMARY KEY(MatiereId)
);
 
CREATE TABLE Sait_Enseigner(
   MatiereId SMALLINT,
   EnseignantId SMALLINT,
   CONSTRAINT Sait_Enseigner_PK PRIMARY KEY(MatiereId, EnseignantId),
   CONSTRAINT Sait_Enseigner_Matiere_FK FOREIGN KEY(MatiereId) REFERENCES Matiere(MatiereId),
   CONSTRAINT Sait_Enseigner_Enseignant_FK FOREIGN KEY(EnseignantId) REFERENCES Enseignant(EnseignantId)
);
 
CREATE TABLE Enseigne(
   ClasseId SMALLINT,
   MatiereId SMALLINT,
   EnseignantId SMALLINT,
   CONSTRAINT Enseigne_PK PRIMARY KEY(ClasseId, MatiereId, EnseignantId),
   CONSTRAINT Enseigne_Classe_FK FOREIGN KEY(ClasseId) REFERENCES Classe(ClasseId),
   CONSTRAINT Enseigne_Sait_Enseigner_FK FOREIGN KEY(MatiereId, EnseignantId) REFERENCES Sait_Enseigner(MatiereId, EnseignantId)
);

 
Une autre façon de produire le même code SQL :
 

 
Alea jacta est. On croise les doigts...

[Paprick]

Bonsoir,

Effectivement, cette autre façon de modéliser démontre bien la véracité de la proposition : l'association entre "enseigner" et "savoir enseigner" est tout à fait logique au niveau conceptuel.
Je vais même me permettre de simplifier cet exemple en retirant la classe d'entités "Classe" qui n'apporte rien au débat et qui pourrait même le brouiller.
Prenons donc le MCD suivant :

Avec l'ALTER TABLE codé dans la contrainte d'inclusion, on obtient le DDL suivant :

Code :

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
CREATE TABLE Enseignant(
   Matricule INTEGER,
   Nom VARCHAR(50),
   Prenom VARCHAR(50),
   CONSTRAINT PK_Enseignant PRIMARY KEY(Matricule)
);

CREATE TABLE Matiere(
   CodeMatiere INTEGER,
   Libelle VARCHAR(50),
   Description TEXT,
   CONSTRAINT PK_Matiere PRIMARY KEY(CodeMatiere)
);

CREATE TABLE Enseigner(
   Matricule INTEGER,
   CodeMatiere INTEGER,
   CONSTRAINT PK_Enseigner PRIMARY KEY(Matricule, CodeMatiere),
   CONSTRAINT FK_Enseigner_Enseignant FOREIGN KEY(Matricule) REFERENCES Enseignant(Matricule),
   CONSTRAINT FK_Enseigner_Matiere FOREIGN KEY(CodeMatiere) REFERENCES Matiere(CodeMatiere)
);

CREATE TABLE Maitriser(
   Matricule INTEGER,
   CodeMatiere INTEGER,
   CONSTRAINT PK_Maitriser PRIMARY KEY(Matricule, CodeMatiere),
   CONSTRAINT FK_Maitriser_Enseignant FOREIGN KEY(Matricule) REFERENCES Enseignant(Matricule),
   CONSTRAINT FK_Maitriser_Matiere FOREIGN KEY(CodeMatiere) REFERENCES Matiere(CodeMatiere)
 );

ALTER TABLE Cours DROP CONSTRAINT FK_Cours_Enseignant;
ALTER TABLE Cours DROP CONSTRAINT FK_Cours_Matiere;
ALTER TABLE Cours ADD CONSTRAINT FK_Cours_Maitrise 
   FOREIGN KEY(Matricule, CodeMatiere) REFERENCES Maitrise(Matricule, CodeMatiere);

Décomposons maintenant l'association "Maîtriser" afin d'y associer "Enseigner" :

On obtient directement le bon DDL :

Code :

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
CREATE TABLE Enseignant(
   Matricule INTEGER,
   Nom VARCHAR(50),
   Prenom VARCHAR(50),
   CONSTRAINT PK_Enseignant PRIMARY KEY(Matricule)
);

CREATE TABLE Matiere(
   CodeMatiere INTEGER,
   Libelle VARCHAR(50),
   Description TEXT,
   CONSTRAINT PK_Matiere PRIMARY KEY(CodeMatiere)
);

CREATE TABLE Maitriser(
   CodeMatiere INTEGER,
   Matricule INTEGER,
   CONSTRAINT PK_Maitriser PRIMARY KEY(Matricule, CodeMatiere),
   CONSTRAINT FK_Maitriser_Enseignant FOREIGN KEY(Matricule) REFERENCES Enseignant(Matricule),
   CONSTRAINT FK_Maitriser_Matiere FOREIGN KEY(CodeMatiere) REFERENCES Matiere(CodeMatiere)
);

CREATE TABLE Enseigner(
   CodeMatiere INTEGER,
   Matricule INTEGER,
   CONSTRAINT PK_Enseigner PRIMARY KEY(Matricule, CodeMatiere),
   CONSTRAINT FK_Enseigner_Maitriser FOREIGN KEY(Matricule, CodeMatiere) REFERENCES Maitriser(Matricule, CodeMatiere)
);

On peut ensuite associer "Classe" à "Enseigner" avec 1,1(R)---1,n pour retrouver le DDL du modèle de Tabourier et Nanci.
Mais bon, c'est quand plus classe et plus lisible avec la modélisation de la contrainte d'inclusion .