Discussion :

[Miko95]

Bonsoir,

J'ai un devoir à rendre sur la conception de base de données.
La base de données représente un réseau d'écoles. Chaque école est divisée en deux parties : les classes et les laboratoires. Donc chaque classe ou laboratoire appartient à une école seulement.
Le groupe des professeurs se découpe en 3 parties:
1) Les professeurs enseignants, chacun appartient à une classe seulement et peuvent aussi appartenir à un laboratoire seulement.
2) Les professeurs chercheurs, chacun appartient à un laboratoire seulement et peuvent aussi appartenir à une classe seulement.
3) Les professeurs extérieurs, ils n'appartiennent ni à une classe ni à un laboratoire.

Ma question est, est ce que pour implémenter ca, je dois créer une seule table PROFESSEUR ou je dois créer une table pour chaque type de PROFESSEUR?
Dans le cas ou je crée seulement une table, est ce que je dois ajouter à la table les attributs NUM_CLASSE et NUM_LAB comme clés etrangéres sachant qu'elles peuvent prendre la valeur NULL?
Ou existe t'il un autre moyen(meilleur biensur) d'implémenter ca?

Merci d'avance

[fsmrel]

Bonsoir,


Pour une plus grande clarté, il est préférable de mettre en évidence la typologie des professeurs, d’où les tables Enseignant, Chercheur et Exterieur. Conceptuellement parlant, les entités-types correspondantes — Enseignant, Chercheur et Exterieur — sont des spécialisations de l’entité-type Professeur, laquelle concentre les propriétés communes à tous les professeurs.

Par ailleurs, le défi est d’éviter la présence du bonhomme NULL dans les tables. Ainsi, les tables Enseignant, Chercheur et Exterieur seront dotées au besoin des attributs correspondant aux propriétés spécifiques à chacune des catégories.

Toujours à cause du bonhomme NULL, On mettra en œuvre une table ChercheurClasse pour mettre en relation un chercheur affecté à une classe quand cela se produit, ainsi qu’une table EnsLabo pour mettre en relation un enseignant affecté à un labo.

Dans le schéma ci-dessous (Power AMC), les clés primaires sont soulignées et accompagnées du mickey <pk> (primary key). Les clés étrangères sont accompagnées d’un mickey <fk> (foreign key). Vous noterez qu’un clé primaire peut aussi être clé étrangère. Ainsi, l’attribut ProfId de la table Professeur est propagé un peu partout.

[Miko95]

Bonjour,
Tout d'abord merci pour la réponse, je vois donc qu'il est préférable d'utiliser une sorte d'héritage(je sais pas si cette notion existe dans le modèle relationnel).
Mais j'ai d'autres question, quand on définis le schéma d'une entité, on a appris que le schéma est un couple de deux valeurs:
1) L'ensemble des couples (NomAttribut, Domaine) pour tous les attributs de l'entité
2) L'ensemble des contraintes sur l'entité

Premierement, comment définir le domaine de l'attribut pour le 1), est ce qu'on se base sur des types de données existant par exemple si c'est un entier, est ce que je dois préciser dans le domaine le nombre de chiffres ou plutot dans les contraintes; ou un autre exemple si c'est une String de max 30 caractères, comment définir le domaine, est ce que j'utilise le type Varchar(30) ou je donne seulement String puis le nombre de caractères sera spécifié dans les contraintes? Autrement dis, est ce qu'il existe une norme standard pour définir ce schéma?

Deuxièmement, je veux juste m'assurer, une clé primaire est toujours différent de NULL, c'est a dire que cette contrainte n'est pas à spécifier dans l'ensemble des contraintes? et pour une clé étrangére, est ce qu'il est nécéssaire de spécifier si elle doit etre different de NULL ou non?

Ma derniere question est au sujet de l'Active Domain d'un attribut, quand on a une clé etrangére dans une entité B correspondant à une clé primaire dans une entité A, est ce qu'il doit etre précisé dans les contraintes de B que l'Active Domain de la clé etrangére de B doit etre un sous ensemble de l'Active Domain de la clé primaire de A?

Merci d'avance

[fsmrel]

Bonsoir,

je vois donc qu'il est préférable d'utiliser une sorte d'héritage (je sais pas si cette notion existe dans le modèle relationnel).

Au niveau conceptuel, on a bien un héritage :



L’entité-type Professeur représente un surtype et les autres des sous-types.

La demi-lune comporte une croix, mickey qui — avec Power AMC — signifie l’exclusion : un professeur est soit enseignant, soit chercheur, soit extérieur. Mais, si un professeur peut être à la fois l’un et l’autre, alors le mickey d’exclusion est à ôter.

La demi-lune est soulignée, ce qui signifie la totalité : il ne peut y avoir de professeur qui ne soit ni enseignant, ni chercheur, ni extérieur.

Concernant le Modèle Relationnel de Données — c'est-à-dire la théorie relationnelle —, l’héritage existe évidemment, mais concerne les types : types scalaires, type tuple, type relation. Je ne peux malheureusement pas développer ici, car pour en discuter, il faut d’abord étudier les chapitres 6, 12 à 16 de l’ouvrage de référence Databases, Types And the Relational Model: The Third Manifesto (Third Edition).

Néanmoins, si l’entité-type Professeur fait l’objet d’un surtype au niveau conceptuel, il ne faut pas inférer que l’on puisse en faire une « supertable » SQL, de même que des sous-types on puisse faire des « subtables ». La norme SQL fournit les concepts de supertable et subtable, mais Date et Darwen montrent que cela est inutile et ne simplifie pas les choses (voir l’annexe G, pages 467-471 de l’ouvrage cité), autrement dit, ces concepts peuvent sans problème passer au fil du rasoir d’Ockham. Si l’on veut manipuler (et pouvoir créer et mettre à jour) un « objet » Enseignant, on peut définir une vue qui soit la jointure naturelle des tables Professeur et Enseignant. Voyez à ce sujet la discussion avec Heledir, notamment Le pavé du jour.

comment définir le domaine de l'attribut

Dans ce qui suit, j’utilise le terme « type » à la place de celui de « domaine » (qui est synonyme). Considérons le cas de la table Ecole (cf. le schéma que j’ai proposé dans mon message précédent). Si vous écrivez par exemple :

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 Ecole
(
     EcoleId        INTEGER       NOT NULL
.. , EcoleNom       VARCHAR(48)   NOT NULL
     ...
) ; 
CREATE TABLE Professeur
(
     ProfId         INTEGER       NOT NULL
.. , ProfNom        VARCHAR(48)   NOT NULL
     ...
) ;

Vous signifiez que les attributs EcoleId et ProfId sont du même type, à savoir INTEGER, donc sémantiquement parlant de même nature, autrement dit, on peut les comparer et les tricoter au moyen de l’arithmétique. Certes, vous ne le ferez pas, mais du point de vue théorique c’est possible. Cette réflexion vaut aussi pour les attributs EcoleNom et ProfNom.

Dans le cadre du Modèle Relationnel de Données, si vous voulez éviter les amalgames, vous définirez vos propres types, par exemple :

Code :

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

1
2
3
4
5
6
7
8
9
TYPE EcoleId POSSREP {INTEGER} ;
TYPE EcoleNom POSSREP {CHAR} ;
...
VAR Ecole RELATION 
  {
     EcoleId        EcoleId
     EcoleNom       EcoleNom
     ...
  } ;

Et cette fois-ci, vous ne pourrez plus tricoter les attributs EcoleId et EcoleNom avec des attributs qui ne sont pas du même type.
A noter que le terme POSSREP signifie POSSIBLE REPRESENTATION. Prenez par exemple le cas du type POINT (utilisé dans le cas par exemple des points d’un espace géométrique à deux dimensions). Vous pouvez préférer présenter les coordonnées des points soit comme cartésiennes, soit comme polaires :

Code :

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

1
2
3
TYPE POINT  
                POSSREP CARTESIAN {X RATIONAL, Y RATIONAL}
                POSSREP POLAR {R RATIONAL, θ RATIONAL}

C’est une affaire de goût, et le Modèle Relationnel de Données vous en offre la possibilité.
Dans le cadre du modèle SQL (qui n’est pas le Modèle Relationnel de Données), vous pouvez écrire :

CREATE TYPE EcoleId AS INTEGER FINAL ;
CREATE TYPE ProfId AS INTEGER FINAL ;

Mais vérifiez si votre SGBD vous le permet à son tour.

si c'est un entier, est ce que je dois préciser dans le domaine le nombre de chiffres ou plutôt dans les contraintes; ou un autre exemple si c'est une String de max 30 caractères, comment définir le domaine, est ce que j'utilise le type Varchar(30) ou je donne seulement String puis le nombre de caractères sera spécifié dans les contraintes?

Prenons l’exemple du type POIDS. Dans le cadre du Modèle Relationnel de Données, vous pouvez définir des contraintes pour les valeurs possibles :

Code :

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

1
2
3
TYPE POIDS 
     POSSREP {G DECIMAL (7,1)
             CONSTRAINT G > 0.0 AND G < 150000.0}

Alors qu’avec le modèle SQL, les contraintes ne peuvent pas être prises en compte au niveau du type :

Code :

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

 CREATE TYPE POIDS AS INTEGER FINAL ;

Il faudra agir au niveau de chaque attribut du type POIDS. Pas très pratique.

De la même façon, écrire CHAR(n) ou VARCHAR(n) est impératif en SQL à ceci près que CHAR tout court est équivalent à CHAR(1). Au contraire, dans le cadre du Modèle Relationnel de Données, on écrit CHAR et c’est tout (CHAR représente en l’occurrence l’ensemble des chaînes de caractères).

je veux juste m'assurer, une clé primaire est toujours différent de NULL, c'est a dire que cette contrainte n'est pas à spécifier dans l'ensemble des contraintes?

Dans le cadre du Modèle Relationnel de Données, NULL ne veut rien dire, il est interdit de séjour. Dans le cadre de la norme SQL, les attributs participant à une clé primaire ne font pas nécessairement l’objet d’une clause NOT NULL explicite : dans ce cas particulier, elle est implicite (intégrité d’entité oblige).

pour une clé étrangère, est ce qu'il est nécessaire de spécifier si elle doit être différente de NULL ou non?

Si pour un attribut participant à une clé étrangère (et ne participant pas à une clé primaire) on n’a pas codé la clause NOT NULL, alors cet attribut peut être sans problème marqué NULL (à vos risques et périls).

Ma dernière question est au sujet de l'Active Domain d'un attribut, quand on a une clé étrangère dans une entité B correspondant à une clé primaire dans une entité A, est ce qu'il doit être précisé dans les contraintes de B que l'Active Domain de la clé étrangère de B doit être un sous ensemble de l'Active Domain de la clé primaire de A?

Je ne sais pas ce que signifie l’adjectif « Active » : il y aurait des Inactive Domains ? Quoi qu’il en soit, le SGBD doit vérifier que chaque valeur de clé étrangère de la table B est une valeur de la clé référencée (primaire ou alternative) dans la table A. C’est le principe même de l’intégrité référentielle. Je ne vois donc pas pourquoi vous vous posez des questions à ce sujet.


Revenons maintenant sur les règles de gestion que vous aviez proposées. Elles sont muettes concernant les professeurs extérieurs. En conséquence, selon la représentation graphique que je vous avais proposée, on ne sait pas répondre à la question : « A quelle(s) école(s) est affecté un professeur extérieur ». A supposer qu’un professeur extérieur soit affecté à au plus une école, vous devrez établir une relation (une contrainte référentielle) entre les tables Exterieur et Ecole.

Deuxième remarque : toujours avec la même représentation graphique, rien n’empêche qu’un professeur soit chercheur dans une école et enseignant ou extérieur dans une autre.

Le schéma ci-dessous permet de pallier les carences :



le mickey <ak> signifie clé alternative. En réalité, la clé primaire des tables Professeur, Enseignant, Chercheur, CherCheurClasse et EnsLabo doit être le singleton {ProfId}, tandis que le couple {EcoleId, LaboId} est une surclé (un surensemble de la clé primaire}. Power AMC ne permet que d’avoir le couple comme clé primaire et le singleton comme clé alternative. Du point de vue du fonctionnement, il n’y a pas de problème, on pourrait laisser les choses en l’état, mais dans le jeu de création des tables ci-dessous, j’ai permuté, car les choses sont ainsi plus logiques.

Concernant les contraintes d’exclusion, il faudra prévoir une assertion au sens de la norme SQL. Si votre SGBD ne permet pas, il faudra en passer par un trigger.

Ci-joint le jeu de création des tables (SGBD : SQL Server)

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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
 
Create Table Ecole (
   EcoleId              Int                  Not null,
   EcoleNom             Varchar(48)          Not null,
   Constraint Ecole_PK Primary Key  (EcoleId)
) ;
Create Table Classe (
   EcoleId              Int                  Not null,
   ClasseId             Int                  Not null,
   ClasseNom            Varchar(48)          Not null,
   Constraint Classe_PK Primary Key  (EcoleId, ClasseId),
   Constraint Classe_Ecole_FK Foreign Key (EcoleId)
      References Ecole (EcoleId)
) ;
Create Table Labo (
   EcoleId              Int                  Not null,
   LaboId               Int                  Not null,
   LaboNom              Varchar(48)          Not null,
   Constraint Labo_PK Primary Key  (EcoleId, LaboId),
   Constraint Labo_Ecole_FK Foreign Key (EcoleId)
      References Ecole (EcoleId)
) ;
Create Table Professeur (
   ProfId               Int                  Not null,
   EcoleId              Int                  Not null,
   ProfNom              Varchar(48)          Not null,
   Constraint Professeur_PK Primary Key  (ProfId),
   Constraint Professeur_AK Unique (EcoleId, ProfId),
   Constraint Professeur_Ecole_FK Foreign Key (EcoleId)
      References Ecole (EcoleId)
) ;
Create Table Enseignant (
   ProfId               Int                  Not null,
   EcoleId              Int                  Not null,
   ClasseId             Int                  Not null,
   Constraint Enseignant_PK Primary Key  (ProfId),
   Constraint Enseignant_AK Unique (EcoleId, ProfId),
   Constraint Enseignant_Professeur_FK Foreign Key (EcoleId, ProfId)
      References Professeur (EcoleId, ProfId),
   Constraint Enseignant_Classe_FK Foreign Key (EcoleId, ClasseId)
      References Classe (EcoleId, ClasseId)
) ;
Create Table Chercheur (
   ProfId               Int                  Not null,
   EcoleId              Int                  Not null,
   LaboId               Int                  Not null,
   Constraint Chercheur_PK Primary Key  (ProfId),
   Constraint Chercheur_AK Unique  (EcoleId, ProfId),
   Constraint Chercheur_Labo_FK Foreign Key (EcoleId, LaboId)
      References Labo (EcoleId, LaboId),
   Constraint Chercheur_Professeur_FK Foreign Key (EcoleId, ProfId)
      References Professeur (EcoleId, ProfId)
) ;
Create Table Exterieur (
   ProfId               Int                  Not null,
   Constraint Exterieur_PK Primary Key  (ProfId)
)
;
Create Table CherCheurClasse (
   ProfId               Int                  Not null,
   EcoleId              Int                  Not null,
   ClasseId             Int                  Not null,
   Constraint CherCheurClasse_PK Primary Key  (ProfId),
   Constraint CherCheurClasse_FK1 Foreign Key (EcoleId, ProfId)
      References Chercheur (EcoleId, ProfId),
   Constraint CherCheurClasse_FK2 Foreign Key (EcoleId, ClasseId)
      References Classe (EcoleId, ClasseId)
)
;
Create Table EnsLabo (
   ProfId               Int                  Not null,
   EcoleId              Int                  Not null,
   LaboId               Int                  Not null,
   Constraint EnsLabo_PK Primary Key  (ProfId),
   Constraint EnsLabo_FK1 Foreign Key (EcoleId, ProfId)
      References Enseignant (EcoleId, ProfId),
   Constraint EnsLabo_FK2 Foreign Key (EcoleId, LaboId)
      References Labo (EcoleId, LaboId)
)
;

[fsmrel]

A propos de mon message précédent.


1) Vous aurez noté que, quelle que soit la table, aucun attribut ne peut être marqué NULL.

2) Concernant l'exclusion des rôles, à titre d'exemple, je joins les triggers (SQL Server) permettant d'assurer qu'un enseignant ne peut être ni chercheur ni extérieur, etc.

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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
CREATE TRIGGER Exclusion_Role_Enseignant ON Enseignant INSTEAD OF INSERT, UPDATE AS
    IF EXISTS 
            (
             SELECT   ''     
             FROM     Chercheur AS x JOIN Inserted AS y
                        ON x.ProfId = y.ProfId     
             UNION ALL
             SELECT   ''
             FROM     Exterieur AS x JOIN Inserted AS y
                        ON x.ProfId = y.ProfId     
            )     
       BEGIN 
           Raiserror ('Insert Enseignant : Contrainte d''exclusion non respectée !',16,1) 
       RETURN
    END
    INSERT INTO Enseignant 
        SELECT *
        FROM   INSERTED 
GO
CREATE TRIGGER Exclusion_Role_Chercheur ON Chercheur INSTEAD OF INSERT, UPDATE AS
    IF EXISTS 
            (
             SELECT   ''     
             FROM     Enseignant AS x JOIN Inserted AS y
                        ON x.ProfId = y.ProfId     
             UNION ALL
             SELECT   ''
             FROM     Exterieur AS x JOIN Inserted AS y
                        ON x.ProfId = y.ProfId     
            )     
       BEGIN 
           Raiserror ('Insert Chercheur : Contrainte d''exclusion non respectée !',16,1) 
       RETURN
    END
    INSERT INTO Chercheur 
        SELECT *
        FROM   INSERTED 
GO
CREATE TRIGGER Exclusion_Role_Exterieur ON Exterieur INSTEAD OF INSERT, UPDATE AS
    IF EXISTS 
            (
             SELECT   ''     
             FROM     Enseignant AS x JOIN Inserted AS y
                        ON x.ProfId = y.ProfId     
             UNION ALL
             SELECT   ''
             FROM     Chercheur AS x JOIN Inserted AS y
                        ON x.ProfId = y.ProfId     
            )     
       BEGIN 
           Raiserror ('Insert Exterieur : Contrainte d''exclusion non respectée !',16,1) 
       RETURN
    END
    INSERT INTO Exterieur 
        SELECT *
        FROM   INSERTED 
GO

[Miko95]

Merci fsmrel pour les réponses très détaillées
J'ai une question au sujet de l'héritage ici, dans mon cas, les entités Enseignant, Exterieur et Chercheur hérite de Professeur.
Dans mon modèle, tout professeur doit appartenir à une seule école.
Aussi, un Enseignant appartient à une seule Classe qui appartient elle à une seule Ecole. De meme, un Chercheur appartient à un seul Laboratoire qui appartient lui à une seule Ecole; de là, je peux voir indirectement à quelle école appartient un Enseignant ou un Chercheur. Mais comme vous l'avez dis, on ne peut pas répondre à la question A quelle ecole appartient un professeur exterieur?. Pour cela, vous avez proposé de lier les entités Professeur et Ecole.
Ma question est, est ce que cela ne crée pas des doubles informations? Est ce qu'au final il y a deux moyens de savoir à quelle école appartiennent un Chercheur ou un Enseignant?

D'autre part, pourquoi la clé primaire de l'entité Classe est composée de EcoleID et de Classe ID, ClasseID permet à lui seul d'identifier une classe parmi d'autres? Meme question pour le Laboratoire.

[fsmrel]

Bonsoir,

Mais comme vous l'avez dis, on ne peut pas répondre à la question A quelle école appartient un professeur extérieur?. Pour cela, vous avez proposé de lier les entités Professeur et Ecole.

On peut se contenter de tirer seulement le lien entre Exterieur et Ecole, puisque pour les deux autres catégories de professeurs on peut retrouver l’école via la classe ou le laboratoire.
Dans l’esprit de la généralisation/spécialisation qui caractérise l’héritage, le modèle que j’ai représenté factorise les propriétés communes à tous les professeurs, dont l’école à laquelle chacun d’eux est affecté.

Ma question est, est ce que cela ne crée pas des doubles informations? Est ce qu'au final il y a deux moyens de savoir à quelle école appartiennent un Chercheur ou un Enseignant?

Tout dépend de ce que vous entendez par double information. S’il s’agit de consulter la base de données pour savoir à quelle école appartient le professeur Mickael, le chercheur bien connu, il est un fait que l’on peut consulter la table Professeur, mais aussi la table Chercheur (parce que dans ce cas, on aimerait aussi savoir à quel laboratoire il est rattaché), voire encore la table CherCheurClasse (parce que dans ce cas, on souhaiterait savoir dans quelle classe il est accessoirement enseignant). Mais le plus important est d’être sûr que Mickael ne pourra jamais être rattaché à deux écoles différentes. Pour vous en assurer, vous pouvez secouer le jeu de tables que j’ai fourni.

A propos de ce jeu de tables, notez bien que la clé primaire des tables Professeur, Chercheur, CherCheurClasse, Enseignant et EnsLabo ne comporte qu’un seul élément, à savoir l’attribut ProfId, ce qui garantit de facto que dans tous les cas Mickael ne peut effectivement être rattaché qu’à une seule école.

D'autre part, pourquoi la clé primaire de l'entité Classe est composée de EcoleID et de ClasseID, ClasseID permet à lui seul d'identifier une classe parmi d'autres? Meme question pour le Laboratoire.

Disons que d’un point de vue sémantique, une classe n’a pas d’existence propre, elle n’existe que parce que l’école existe (si une école disparaît, les classes disparaissent avec). Du point de vue de la modélisation conceptuelle, une école est une entité-type forte (regular entity) et une classe est une entité-type faible (weak entity). Une école est composée de plusieurs classes et une classe est un composant d’une école et en tant qu’entité-type faible, l’identifiant d’une classe est celui de l’école plus un attribut technique (disons un numéro relatif), permettant de distinguer chaque classe dans une école.
Par exemple, si l’école 1 comporte 3 classes et l’école 2 en comporte 4, au niveau tabulaire l’attribut ClasseId correspondra à cet attribut technique, dont la numérotation commence à 1 pour chaque école (donc indépendamment du nom officiel de la classe au sein de l’école) :

Code :

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

1
2
3
4
5
6
7
8
9
 
Classe {EcoleId,  ClasseId, ClasseNom, ...}
          1          1        Sup A 
          1          2        Sup B
          1          3        Spé 
          2          1        Flotte
          2          2        Spé A
          2          3        Spé B
          2          4        Agro

Pour en revenir au choix du modèle qui vous convient, peut-être préférerez-vous celui-ci, selon lequel l’école d'affectation d'un enseignant est connue au niveau de la table Enseignant :



Ou, si vous préférez utiliser l’identification absolue pour les table Classe et Labo, il faudra « remonter » d'Enseignant à Classe pour connaître l'école d'affectation du professeur :


Bref, vous avez le choix.

Un conseil : pour blinder le système, vous pouvez mettre en œuvre une vue ChercheurV qui soit la jointure naturelle des tables Professeur et Chercheur et sur laquelle porteront les INSERT (le principe vaut évidemment pour les tables Enseignant et Exterieur). En effet, supposons que vous effectuiez un INSERT dans la table Professeur et que pour une raison x ou y, l’INSERT complémentaire dans la table Chercheur passe à la trappe : on enfreint la règle qui veut qu’un professeur (chercheur) soit systématiquement affecté à un labo.

Création de la vue (dans le contexte du modèle que je vous ai proposé dans mon message du 18/12) :

Code SQL :

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

1
2
3
4
Create View ChercheurV (ProfId, ProfNom, EcoleId, LaboId)
  AS Select  x.ProfId, x.ProfNom, y.EcoleId, y.LaboId  
     FROM    Professeur AS x JOIN Chercheur AS y
                ON x.ProfId = y.ProfId

Trigger de mise à jour correspondant :

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 TRIGGER ChercheurV_INSERT ON ChercheurV INSTEAD OF INSERT AS
 
  INSERT INTO Professeur
         SELECT    ProfId
                 , EcoleId
                 , ProfNom
         FROM    INSERTED ;
 
  INSERT INTO Chercheur
         SELECT    ProfId
                 , EcoleId
                 , LaboId
         FROM    INSERTED ;

A propos du métabolisme des données

Supposons que vous vouliez supprimer le professeur Albert. Dans l’état actuel de l’instruction CREATE TABLE Chercheur, il faudra d’abord supprimer Albert de la table Chercheur et seulement après le supprimer de la table Professeur. Pour effectuer l’opération en une fois :

DELETE FROM Professeur WHERE ProfNom = 'Albert'

vous utiliserez l’option « 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 Chercheur (
   ProfId               Int                  Not null,
   EcoleId              Int                  Not null,
   LaboId               Int                  Not null,
   Constraint Chercheur_PK Primary Key  (ProfId),
   Constraint Chercheur_AK Unique  (EcoleId, ProfId),
   Constraint Chercheur_Professeur_FK Foreign Key (EcoleId, ProfId)
      References Professeur (EcoleId, ProfId) ON DELETE CASCADE,
   Constraint Chercheur_Labo_FK Foreign Key (EcoleId, LaboId)
      References Labo (EcoleId, LaboId) 
) ;

Dans le même sens, puisqu’une classe est une propriété d’une école, on devrait utiliser l’option « ON DELETE CASCADE » pour la table Classe :

Code SQL :

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

1
2
3
4
5
6
7
8
Create Table Classe (
   EcoleId              Int                  Not null,
   ClasseId             Int                  Not null,
   ClasseNom            Varchar(48)          Not null,
   Constraint Classe_PK Primary Key  (EcoleId, ClasseId),
   Constraint Classe_Ecole_FK Foreign Key (EcoleId)
      References Ecole (EcoleId) ON DELETE CASCADE
) ;

Vous me direz, mais qu’advient-il des professeurs ?

Si un professeur est affecté à une école que l’on veut supprimer, l’opération échouera.

Si vous utilisez la représentation selon laquelle les chercheurs et les enseignants ne sont pas directement rattachés aux écoles, ce sont les tables Enseignant et Chercheur qui seront impliquées dans l’opération, laquelle ne réussira que si aucun chercheur n’est affecté à un labo et aucun enseignant n’est affecté à une classe. (Une école bien vide, aux élèves près...)

Je vous laisse le soin de définir le métabolisme table par table.