Affectation hebdomadaire des salles

**win_ubuntu** · 17/02/2021, 00h35

Bonjour tout le monde.
J'aimerais établir un MCD concernant l'énoncé suivant:
On veut réaliser l’informatisation de l’affectation hebdomadaire des salles d’un collège, et la gestion des absences des élèves.

Le collège est composé de 20 salles de classe, dont 2 équipées de rideaux noirs et de projecteurs vidéo, de 2 salles de laboratoire et d’une salle de sports. D’autres matériels (rétroprojecteurs, écrans, triples tableaux...) sont également affectés à certaines salles.

Les salles ont une capacité variant de 20 à 45 places. La salle de sports a une capacité de 150 places.

Le collège gère 5 classes de 6°, et 4 classes de 5°, 4° et 3°. Chaque classe est numérotée.

Les élèves d’une même classe suivent tous les mêmes cours : il n’y a pas d’options. La liste des élèves de chaque classe est fixée en début d’année (on admet qu’un élève ne change pas de classe, et qu’il n’y a pas d’arrivées en cours d’année). Les cours se répètent à l’identique sur toutes les semaines de l’année scolaire.

Un enseignant peut assurer des cours pour plusieurs classes. Pour affecter un cours à une salle et à un enseignant, on veut pouvoir vérifier que l’enseignant n’a pas de cours simultanément pour une autre classe.

Un cours ne peut pas être assuré par plusieurs enseignants. Les cours sont repérés par le nom de l’enseignant. Certains enseignants assurent plusieurs types de cours (Français, Latin...).

En ce qui concerne l’affectation hebdomadaire des salles, on ne veut pas réaliser un système d’aide à la décision, mais pouvoir obtenir la liste des salles permettant d’accueillir un cours, qui ne sont pas déjà affectées à un autre cours à l’horaire voulu. Il n’y a pas de cours commun à plusieurs classes. Certains cours nécessitent des matériels spécifiques (de la vidéo, un labo ou la salle de sports...). Mais un cours qui n’a pas besoin de vidéo peut néanmoins avoir lieu dans la salle de vidéo. Pour une même classe, on peut avoir un TP de chimie le mardi, nécessitant le labo, et un cours de chimie ne le nécessitant pas. On admet que les affectations sont décidées à l’année : un cours (repéré par un jour, une heure et un enseignant, ex : lundi 10h, Mr Dupont) a lieu dans la même salle et pour la même classe toutes les semaines.

En ce qui concerne la gestion des absences, le traitement manuel actuel est le suivant : une feuille de présence quotidienne, marquée du n° de semaine et du nom du jour, circule d’enseignant à enseignant en cours de journée. Lorsque la “Vie scolaire” est avertie d’une absence d’un élève avant le début des cours, elle reporte la mention “Excusé” sur la ligne de l’élève, pour chaque cours concerné. Chaque enseignant reporte la liste et le nombre d’absences non excusées et constatées à son cours. La feuille de présence retourne à la vie scolaire en fin de journée. Toutes les absences injustifiées font l’objet d’un courrier adressé aux parents, indiquant les jours et heures d’absence de l’élève. Un élève peut être absent à un cours sans être absent sur la journée. Une absence à un cours unique peut être justifiée à l’avance (rendez-vous dentiste...).

voila ma solution:
Nom : MCD.png
Affichages : 1623
Taille : 49,2 Ko

Nom : MCD.png
Affichages : 1623
Taille : 49,2 Ko

l'association emplois du temps contient plusieurs CIF: par exemple
salle, horaire -> un seul cours, une seule classe.
si vous avez des remarques à propos de mon essai n'hésiter pas.
merci d'avance

**fsmrel** · 17/02/2021, 04h14

Bonsoir win_ubuntu,

Nous avons déjà eu le plaisir de parler des MCD il y a quelque temps.

Je n’ai pas regardé de près celui-ci, mais j’ai noté votre remarque concernant les CIF. Le problème est que vous utilisez AMC, lequel ne connaît pas les CIF et nous oblige donc à nous contorsionner dans tous les sens pour pallier.

Le mieux est en fait de passer à Looping, pétri de qualités et qui a l’avantage (entre autres) de permettre de modéliser les CIF, et qui les traduit automatiquement lors du passage à SQL.

Je pense que le papa de Looping ainsi que le Capitaine Escartefigue ne manqueront pas d’expertiser votre travail.

A noter que Looping est gratuit, on peut donc remercier chaleureusement son très sympathique et désintéressé géniteur, le Professeur Patrick Bergougnoux.

Pour voir comment modéliser une CIF sans perdre de temps, voyez par exemple ici.

A noter encore : il y a désormais un forum Looping.

Voilà...

**Paprick** · 17/02/2021, 11h04

Bonjour,

Effectivement, le problème de PowerAMC, c'est qu'il ne règle les contraintes liées au CIF qu'au niveau logique en vous permettant d'exclure, dans le MLD, certaines rubriques de la clé primaire composée des différentes classes d'entités associées... Ce n'est donc pas une bonne solution quand on cherche une solution conceptuelle valable pour un système d'information.
Par ailleurs, comme vous l'avez mentionné, l'association "emplois_du_temps" méritera qu'on s'y attarde... Cette quadri-patte m'inquiète quelque peu et les CIF s'imposeront forcément pour parvenir à un modèle cohérent.

**escartefigue** · 17/02/2021, 19h28

Bonsoir win_ubuntu

A chaque fois que vous modélisez des associations à plus de deux pattes, et surtout quatre ou plus comme dans le cas de votre asso "emploi_du_temps", il convient de poser le stylo pour vérifier que tous les acteurs liés interviennent bien dans cette association et se poser la question de ce que représente concrètement cette association.
Quand une asso est légitime, on lui trouve assez facilement un verbe ou un groupe verbal pour la désigner.
"emploi du temps" n'est pas un verbe, ceci combiné à sa quadrupédie plaide en sa défaveur.

Concernant les enseignants et les élèves, on constate que leurs attributs sont communs.
On peut donc utiliser l'héritage : une entité-type commune, par exemple "personne", sera porteuse des attributs partagés (nom, prénom, date de naissance...) et des sous-types seront utilisés pour ce qui est spécifique (matricule employé par exemple). Certaines associations pourront concerner le surtype "personne" d'autre l'un ou l'autre des sous-types.

Au sujet des cardinalités minimales, l'énoncé n'est pas suffisamment détaillé, mais si l'on veut pouvoir connaître des enseignants non affectés à des cours (retraités, en année sabbatique, arrêt de travail...), alors un mimimum de zéro s'impose.

Pour la CIF qui nous intéresse, à savoir "une salle, à un instant t, n'est affectée qu'à un seul cours", voici un exemple de mise en œuvre avec Looping.

Pièce jointe 591279

Le script correspondant est le 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
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
81
82
83
84
CREATE TABLE SA_salle(
   SA_ident INT IDENTITY,
   PRIMARY KEY(SA_ident)
);

CREATE TABLE PE_personne(
   PE_ident INT IDENTITY,
   PE_nom VARCHAR(50) NOT NULL,
   PE_prenom VARCHAR(50) NOT NULL,
   PE_date_nais DATE NOT NULL,
   PRIMARY KEY(PE_ident)
);

CREATE TABLE ES_enseignant(
   PE_ident INT,
   ES_matricule CHAR(6) NOT NULL,
   PRIMARY KEY(PE_ident),
   UNIQUE(ES_matricule),
   FOREIGN KEY(PE_ident) REFERENCES PE_personne(PE_ident)
);

CREATE TABLE EL_eleve(
   PE_ident INT,
   PRIMARY KEY(PE_ident),
   FOREIGN KEY(PE_ident) REFERENCES PE_personne(PE_ident)
);

CREATE TABLE CL_classe(
   CL_ident INT IDENTITY,
   CL_code CHAR(5) NOT NULL,
   PRIMARY KEY(CL_ident),
   UNIQUE(CL_code)
);

CREATE TABLE YM_type_mat(
   YM_ident INT IDENTITY,
   YM_code CHAR(4) NOT NULL,
   YM_libelle VARCHAR(50) NOT NULL,
   PRIMARY KEY(YM_ident),
   UNIQUE(YM_code)
);

CREATE TABLE MA_materiel(
   MA_ident INT IDENTITY,
   MA_date_acq DATE NOT NULL,
   YM_ident INT NOT NULL,
   SA_ident INT NOT NULL,
   PRIMARY KEY(MA_ident),
   FOREIGN KEY(YM_ident) REFERENCES YM_type_mat(YM_ident),
   FOREIGN KEY(SA_ident) REFERENCES SA_salle(SA_ident)
);

CREATE TABLE CO_cours(
   CO_ident INT IDENTITY,
   PE_ident INT NOT NULL,
   PRIMARY KEY(CO_ident),
   FOREIGN KEY(PE_ident) REFERENCES ES_enseignant(PE_ident)
);

CREATE TABLE AF_affecter(
   SA_ident INT,
   CA_date DATETIME2,
   CO_ident INT NOT NULL,
   PRIMARY KEY(SA_ident, CA_date),
   FOREIGN KEY(SA_ident) REFERENCES SA_salle(SA_ident),
   FOREIGN KEY(CO_ident) REFERENCES CO_cours(CO_ident)
);

CREATE TABLE IN_inscrire(
   PE_ident INT,
   CL_ident INT,
   CA_date DATETIME2,
   PRIMARY KEY(PE_ident, CL_ident, CA_date),
   FOREIGN KEY(PE_ident) REFERENCES EL_eleve(PE_ident),
   FOREIGN KEY(CL_ident) REFERENCES CL_classe(CL_ident)
);

CREATE TABLE SU_suivre(
   CO_ident INT,
   CL_ident INT,
   PRIMARY KEY(CO_ident, CL_ident),
   FOREIGN KEY(CO_ident) REFERENCES CO_cours(CO_ident),
   FOREIGN KEY(CL_ident) REFERENCES CL_classe(CL_ident)
);

Grâce à la CIF, Looping a évacué l'identifiant CO_ident de la PK de la table associative AF_affecter. De fait, la CIF est vérifiée

**fsmrel** · 18/02/2021, 04h50

Bonsoir,

@win_ubuntu :

J’ai regardé d’un peu plus près votre MCD. Comme dit Paprick, l’association EMPLOI_DU_TEMPS est pour le moins suspicieuse, elle disparaîtra.

Comme dans le contexte AMC, avec Looping, l’entité-type HORAIRE ne mérite pas de donner lieu à une table dans le MLD, donc lors de sa création, il faut cocher la case « Classe d’entités fictive ». Pour mémoire, cette entité-type est dotée du seul attribut jourHeure, de type DATETIME au sens SQL.

Vu ses attributs, votre entité-type COURS permet en fait de décrire les types de cours (français, latin, maths, chimie, etc.) Il y a là une ambiguïté à lever aussi faudrait-il renommer cette entité-type par exemple en TYPE_COURS. Je suppose par ailleurs qu’il y a plus d’un professeur enseignant le français, le latin, etc., autrement dit il faudrait mettre en oeuvre une association, nommons-la A_LA_COMPETENCE permettant de définir les compétences de chaque enseignant. Une entité-type COURS toute neuve voit alors le jour, qui fait référence à l’entité-type TYPE_COURS et qui permet d’associer un enseignant aux cours qu’il assure.

il est écrit dans votre énoncé : « Les cours sont repérés par le nom de l’enseignant », en conséquence de quoi, sémantiquement parlant, il est légitime de dire que la nouvelle entité-type COURS est une entité-type faible, relativement à l’entité-type ENSEIGNANT, ce qui techniquement conduit (avec Looping) à cocher la case « Identifiant relatif » pour la patte d’association connectant l’entité-type COURS et l’association ASSURE. L’identification relative est notée « 1,1(R) », sachant qu’avec AMC, elle est notée « (1,1) ».

A son tour, l’entité-type COURS peut aussi être perçue comme une association entre ENSEIGNANT et CLASSE, il s’ensuit que la patte d’association entre COURS et SUIVRE est porteuse d’une cardinalité 1,1.

Dans ce que je suis en train de proposer, un enseignant a la compétence pour des types de cours et assure certains cours : il va de soi qu’il y une contrainte d’inclusion impliquant les associations ASSURE et A_LA_COMPETENCE : un enseignant ne peut assurer que des cours pour lesquels il est qualifié.

Nom : win_ubuntu(cif)enseignant_cours.png
Affichages : 1426
Taille : 20,8 Ko

L’affaire se corse avec l’occupation des salles, et les CIF montrent le bout de leur museau.. Ainsi, à une date et une heure données, un cours n'a lieu dans une seule salle : Il existe la dépendance fonctionnelle (DF) COURS X HORAIRE → SALLE, impliquant la mise en oeuvre d’une association ternaire, nommons-la poétiquement, par exemple, COHS. Ceci fait, on ajoute la CIF traduisant cette DF, mais pour ne pas surcharger le modèle, demandons à Looping de la représenter par une simple pointe de flèche (en cliquant sur la patte d’association connectant COHS et SALLE et en y cochant la case « Entité ciblée par CIF ») :

Nom : win_ubuntu(cif)cours_heure_salle.png
Affichages : 1328
Taille : 24,4 Ko

Comme vous le signalez, il existe d’autres DF, donnant lieu à la modélisation d’autant de CIF (donc d’associations ternaires).

Par exemple :

A un instant donné une salle n’héberge qu’un seul cours ;

A un instant donné un enseignant ne peut être présent que dans une seule salle ;

A un instant donné une classe ne peut être présente que dans une seule salle ;

Etc. !

@Captaine Escartefigue :

Selon ton MCD, un cours donné peut être suivi par plusieurs classes, ton entité-type CO_cours correspondrait alors plutôt à un type de cours (français, latin, maths, etc.) c’est-à-dire à l’entité-type que j’ai nommée de mon côté TYPE_COURS. Mais dans ces conditions la cardinalité 1,1 entre l’entité-type CO_cours et l’association Asso_3 fait qu’un seul enseignant peut assurer un cours d’un type donné : par exemple, seul M. Raoul assurerait les cours de français...

Qui plus est, selon ta CIF, à un instant donné dans une salle donnée on ne peut y donner qu’un cours donné, mais vu ma remarque, il s’agirait là encore d’un type de cours donné, par exemple un cours de français, et les classes où l’on enseigne le français pourraient toutes se retrouver en même temps dans cette salle, qui n’est quand même pas aussi extensible que l’hôtel de Hilbert...

**escartefigue** · 18/02/2021, 07h21

Envoyé par fsmrel

@Captaine Escartefigue :

Selon ton MCD, un cours donné peut être suivi par plusieurs classes, ton entité-type CO_cours correspondrait alors plutôt à un type de cours (français, latin, maths, etc.) c’est-à-dire à l’entité-type que j’ai nommée de mon côté TYPE_COURS. Mais dans ces conditions la cardinalité 1,1 entre l’entité-type CO_cours et l’association Asso_3 fait qu’un seul enseignant peut assurer un cours d’un type donné : par exemple, seul M. Raoul assurerait les cours de français....

Effectivement, je n'ai pas mis la bonne cardinalité, c'est bien sûr 1 de [COURS] vers (suivre)

Pour le reste, il s'agit d'un exercice scolaire dont l'énoncé est très simplifié. Si la qualification des enseignants pour telle ou telle matière est pertinente dans la vraie vie, elle n'est pas abordée dans l'énoncé. De même il est bien mentionné que les élèves ne changent jamais de classe en cours d'année, le matériel ne change jamais de salle etc.

**win_ubuntu** · 19/02/2021, 02h04

Merci infiniment à tous.
@fsmrel ,@escartefigue
Normalement, un cours est suivi par plusieurs classes de même niveau: nous avons comme exemple 5 classes de 6° (6°N1, 6°N2,...,6N°5).
si le cours math est programmé pour le niveau 6° alors toutes les classes de la 6° (6°N1, 6°N2,...,6N°5) doivent suivre ce cours. c'est pour cela, je vois que la participation de l'entité "classe" à l'association: emploi_du_temps est nécessaire.

**fsmrel** · 19/02/2021, 04h47

Bonsoir,

J’ai renommé TYPE_COURS en MATIERE, là encore le nom est un peu plus pertinent.

Identifier un cours à partir du nom de l’enseignant n’est pas recommandé ! Ça fait 35 ans qu’Yves Tabourier l’a expliqué...

Cas de la contrainte d’inclusion entre les associations ASSURE et A_LA_COMPETENCE : concernant la génération du code SQL, Looping ne prend aucune initiative pour ce genre de contrainte (même chose pour les contraintes d’exclusion).

En conséquence la balle est dans notre camp pour assurer. Je propose ceci (contexte SQL Server) :

(1) Suite à la création de la table A_LA_COMPETENCE, création d’une fonction ad-hoc ;

(2) Ajout d’une contrainte pour la table COURS, faisant appel à cette fonction :

-----------------------------------------------
-- Un enseignant ne peut dispenser que des
-- cours pour lesquels il est qualifié.
-- La fonction COURS_COMPETENCE_INCLUSION
-- a pour objet de s'en assurer.
-- En entré de la fonction :
-- l'identifiant de l'enseignant et celui
-- de la matière enseignée.
-- Si cet enseignant n'est pas qualifié,
-- la fonction fournit en sortie la valeur 0. 
-----------------------------------------------
GO
CREATE FUNCTION COURS_COMPETENCE_INCLUSION(@enseignantId INT, @matiereId INT)
RETURNS INT  
AS   
BEGIN  
   DECLARE @n INT  

   SELECT @n = COUNT(*) 
   FROM   A_LA_COMPETENCE 
   WHERE  enseignantId = @enseignantId AND matiereId = @matiereId  
   RETURN @n  
END ;  
GO  

ALTER TABLE COURS  
    ADD CONSTRAINT COURS_COMPETENCE_CHK 
        CHECK (dbo.COURS_COMPETENCE_INCLUSION(enseignantId, matiereId) > 0) ;

Si ce code est incorporé à une règle, alors à notre demande, Looping l’ajoutera à la suite des CREATE TABLE.

Dans l’énoncé initial, il est écrit :

Un cours ne peut pas être assuré par plusieurs enseignants

La patte d’association entre COURS et ASSURE est 1,1, donc tout va bien. Tordons un peu la règle et énonçons :

Pour une classe et une matière données, il ne peut y avoir qu'un seul enseignant.

win_ubuntu, vous nous direz si cette règle vous semble réaliste. Elle paraît anodine, mais quoi qu’il en soit, il y a là une DF à garantir : MATIERE X CLASSE → ENSEIGNANT.

Qui dit DF, dit CIF, donc association ternaire à laquelle participeraient MATIERE, CLASSE et ENSEIGNANT, mais il suffit en fait de définir la clé candidate {matiereId, classeId} pour la table COURS. Là encore, fendons-nous d’une règle Looping, comportant le code suivant :

ALTER TABLE COURS  
     ADD  CONSTRAINT COURS_AK 
        UNIQUE (matiereId, classeId) ;

Envoyé par escartefigue

Effectivement, je n'ai pas mis la bonne cardinalité, c'est bien sûr 1 de [COURS] vers (suivre)

Le remplacement est bénéfique

Cela dit, passer de 0,1 à 1,1 est à privilégier car sémantiquement, COURS peut être vu comme une association entre ELEVE et MATIERE (anciennement TYPE_COURS).

Je pense que tu seras d’accord que la mise en oeuvre de l’entité-type MATIERE est une bonne chose.

Envoyé par escartefigue

Pour le reste, il s'agit d'un exercice scolaire dont l'énoncé est très simplifié. Si la qualification des enseignants pour telle ou telle matière est pertinente dans la vraie vie, elle n'est pas abordée dans l'énoncé. De même il est bien mentionné que les élèves ne changent jamais de classe en cours d'année, le matériel ne change jamais de salle etc.

Exercice scolaire simplifié certes, mais soulevant quand même quelques problèmes intéressants. En fait, on retrouve ici une variante du schéma relationnel (relational scheme) CTHRSG de Jeffrey Ullman (Universités de Princeton et Stanford), que celui-ci utilise beaucoup dans les chapitres 7 (Design Theory for Relational Databases) et 9 (The Universal Relation As A User Interface) de son ouvrage Principles of Database Systems, Second Edition, paru en 1982, et qui me fut offert en 1987 (j’ai l’impression que c’est hier...). Je l’ai tellement crayonné que j’ai dû faire l’acquisition d’un 2e exemplaire il n’y a pas si longtemps... Je signale qu’en 1988, Ullman a sorti une nouvelle version de son ouvrage, mais en deux volumes (avec une modification du titre, Principles of Database and Knowledge-Base Systems. Le 1er volume est téléchargeable : Principles of Database and Knowledge-Base Systems, Volume I. Le chapitre 7 est toujours à sa place, mais le chapitre 9 a été transféré dans le volume II (où il est devenu le chapitre 17).

Envoyé par win_ubuntu

si le cours math est programmé pour le niveau 6° alors toutes les classes de la 6° (6°N1, 6°N2,...,6N°5) doivent suivre ce cours. c'est pour cela, je vois que la participation de l'entité "classe" à l'association: emploi_du_temps est nécessaire.

En fait, si l’entité-type CLASSE détermine l’entité-type NIVEAU (association CL_NIV avec cardinalité 1,1), et si vous établissez une association, nommons-la MAT_NIV, entre les entités-types NIVEAU et MATIERE (cf. ci-dessous), alors toutes les classes d’un niveau donné auront de facto à leur programme les matières définies par l’association MAT_NIV. Reste à voir l’impact sur le reste du MCD (cohérence entre COURS et MAT_NIV).

Nom : win_ubuntu(cif)classe_niveau.png
Affichages : 1386
Taille : 41,3 Ko

**escartefigue** · 19/02/2021, 09h08

Bonjour François

Envoyé par fsmrel

Je pense que tu seras d’accord que la mise en oeuvre de l’entité-type MATIERE est une bonne chose.

Oui, le cours doit se rapporter à une matière ! Sinon ce n'est plus un cours, mais de l'étude surveillée

**fsmrel** · 01/03/2021, 03h46

Bonsoir,

Je reviens sur les matières programmées pour chaque niveau :

Envoyé par win_ubuntu

si le cours math est programmé pour le niveau 6° alors toutes les classes de la 6° (6°N1, 6°N2,...,6N°5) doivent suivre ce cours. c'est pour cela, je vois que la participation de l'entité "classe" à l'association: emploi_du_temps est nécessaire.

Si « math » est un cours, alors il est préférable de dire que c’est une matière, sinon demeure l’ambiguïté sémantique que j’ai déjà évoquée.

En reprenant votre MCD initial, au stade relationnel on obtient la structure suivante :

EMPLOI_DU_TEMPS {NoClasse, NoCours, NoSalle, JourHeure}

Correspondant au prédicat :

La classe C suit le cours M dans la salle S à l’instant T

A reformuler ainsi :

La classe C étudie la matière M dans la salle S à l’instant T

En fait, EMPLOI_DU_TEMPS fait sans problème l’objet d’une vue, dont la création est fournie plus loin (code SQL).

So far so good ?

Renommons NoCours en NoMatiere. Selon votre MCD, l’association SUIVRE est utilisée pour définir les paires {NoClasse, NoMatiere}. Vous observerez que dans le MCD que je propose, l’association SUIVRE a été renommée en COURS et a été transformée en entité-type associative pour pouvoir établir une association avec ENSEIGNANT, afin que l’on sache quel enseignant enseigne quoi à quelles classes (mon association ASSURE). Incidemment, je pourrais sans problème éviter de renommer SUIVRE...

Ce que je reproche à votre propre association ASSURE, c’est qu’il n’y a qu’un seul enseignant par matière, quelles que soient les classes ! Si M. Antoine Delafoy enseigne effectivement le français, aucun autre enseignant ne pourra enseigner cette matière : si les 17 classes ont le français à leur programme, à raison d’une heure par jour (et 4 jours par semaine) et par classe, M Antoine Delafoy devra enseigner 17 x 4 = 68 heures par semaine, du stakhanovisme en quelque sorte ! Et si en plus il enseignait le latin, il serait confiné à l’école ...

Ainsi, votre association ASSURE doit plutôt associer votre association SUIVRE et votre entité-type ENSEIGNANT, mais comme Merise ne permet d’associer que des entités-types, SUIVRE doit devenir entité-type, c’est que j’ai fait pour ma part en la renommant en COURS.

De mon côté je m’en suis tenu à la DF :

COURS X HORAIRE → SALLE

impliquant comme je l’ai écrit (post #5) la mise en oeuvre d’une association ternaire, que j’ai nommée COHS et dont le prédicat est le suivant :

Le cours K a lieu à l’heure H dans la salle S

Selon mon MCD, la traduction de ce prédicat en schéma relationnel est celle-ci :

COHS {coursId, jourHeure, salleId}

Ayant pour clé primaire la paire {coursId, jourHeure}, conséquence de la DF.

Si j’avais mis en oeuvre une association EMPLOI_DU_TEMPS, celle-ci aurait été dérivée ainsi en SQL :

EMPLOI_DU_TEMPS{classeId, coursId, salleId, jourHeure}

Mais il existe la DF coursId → classeId, et par application du théorème de Heath, EMPLOI_DU_TEMPS est décomposable sans perte en :

CLCO {coursId, classeId}

COHS {coursId, jourHeure, salleId}

Où COHS est connu et CLCO n’est qu’une projection de

COURS {coursId, enseignantId, matiereId, classeId}

qui a pour clé primaire {coursId}.

Dans ces conditions, EMPLOI_DU_TEMPS se ramène à une vue de jointure des tables COURS et COHS :

SELECT x.classeid, x.coursId, y.salleId , jourHeure
FROM   COURS as x 
  JOIN COHS as y ON x.coursId = y.coursId ;

Le MCD que je propose ci-dessous reprend le second MCD du post #8, avec quelques aménagements :

(1) L’attribut jourHeure de HORAIRE est renommé en jourHeureDebut (en effet, on va devoir contrôler des périodes, il y aura donc date de début et date de fin).

(2) Mise en oeuvre d’une contrainte d’inclusion (Inclusion2) :

Un enseignant ne peut enseigner une matière à une classe que si cette matière est au programme de la classe.

La modélisation que j’ai faite de cette contrainte est probablement à aménager (connexion sans doute non indispensable de l’association CO_TC avec la contrainte). Si une bonne âme...

(3) L’entité-type COURS n’est plus identifiée relativement à ENSEIGNANT. Après tout, que se passera-t-il, par exemple, si un enseignant E1 tombe malade ? C’est un autre enseignant, E2, qui assurera ses cours. On n’est pas dans la logique imparable des lignes de facture : une ligne de facture de la facture F1 ne peut pas être remplacée par la ligne d’une autre facture, F2... Cela dit, si un concepteur estime que COURS doit rester identifié coûte que coûte relativement à ENSEIGNANT, qu’il présente ses arguments, on peut toujours discuter et faire marche arrière.

Au sujet des dépendances fonctionnelles

La prise en compte du temps (entité-type HORAIRE) nécessite la mise en oeuvre de quelques DF. Outre HORAIRE, les entités-types suivantes sont concernées : SALLE, COURS, ENSEIGNANT, CLASSE. D’où les DF à garantir :

COHS : COURS X HORAIRE → SALLE
SHCO : SALLE X HORAIRE → COURS
EHS : ENSEIGNANT X HORAIRE → SALLE
SHE : SALLE X HORAIRE → ENSEIGNANT
CLHS : CLASSE X HORAIRE → SALLE
SHCL : SALLE X HORAIRE → CLASSE

Comme chaque DF doit en théorie faire l’objet d’une association ternaire, cela fait 6 associations, donc autant de tables SQL et on va surtout chercher à réduire...

Ainsi la DF SHCO n’a pas besoin d’être implémentée car, bien que ça n’apparaisse pas dans le MCD, mais au stade SQL, la paire {salleId, jourHeureDebut} sera identifiante (clé alternative) pour la table COHS (qui absorbe SHCO puisqu’ayant même en-tête).

Même chose pour la DF SHE. En effet, puisque SALLE X HORAIRE → COURS et COURS → ENSEIGNANT, par transitivité SALLE X HORAIRE → ENSEIGNANT.

Même chose pour la DF SHCL. En effet, puisque SALLE X HORAIRE → COURS et COURS → CLASSE, par transitivité SALLE X HORAIRE → CLASSE.

Restent à implémenter les DF suivantes : COHS, EHS et CLHS.

Prise en compte de la durée

Jusqu’ici on a raisonné par rapport à des dates, mais l’occupation des salles nécessite que l’on passe au stade supérieur, celui de la durée : au cours d’un intervalle de temps donné, deux cours ne peuvent pas avoir lieu dans la même salle, un enseignant ne peut pas se trouver en même temps dans deux salles distinctes, etc.

L’association COHS est donc à doter d’un attribut, nommons-le jourHeureFin, pour qu’on puisse établir la fin de l’occupation des salles.

Pour leur part, les associations EHS et CLHS n’auront pas à donner lieu à des tables SQL. Etant donné que la gestion des intervalles nécessite avec SQL Server la mise en oeuvre de fonctions ou de triggers, je pense que le mieux est de se limiter à la seule table COHS et d’exploiter un trigger à tout faire, façon « couteau suisse ». Je vous en proposerai un, avec le code SQL des tables à venir.

Le MCD :

Nom : win_ubuntu(cif)mnimal en cifs.png
Affichages : 1273
Taille : 60,5 Ko

**fsmrel** · 01/03/2021, 17h24

Bonsoir,

Envoyé par win_ubuntu

On admet que les affectations sont décidées à l’année : un cours (repéré par un jour, une heure et un enseignant, ex : lundi 10h, Mr Dupont) a lieu dans la même salle et pour la même classe toutes les semaines.

Je rappelle que j’ai pris l’initiative de faire intervenir la durée des cours, car il n’est pas précisé s’ils ont tous la même durée. Evidemment, ça complique un peu les choses, mais ça n’est pas insurmontable. J’en ai rajouté un peu, afin de pouvoir suivre les affectations de salles au fil des ans bien que ce ne soit pas demandé, en tout cas qui peut le plus peut le moins. Avec SQL Server, connaissant la date d’un cours, pour afficher le nom du jour (lundi, mardi, etc.) on peut utiliser la fonction DATENAME (exemple : DATENAME(WEEKDAY, jourHeureDebut)). La vue EMPLOI_DU_TEMPS permet de savoir qui enseigne quoi, où et quand.

Les CREATE TABLE (SQL Server).

J’ai été amené à retoucher le code généré par Looping :

(a) Ajout de la clé alternative {matiereId, classeId} (contrainte COURS_AK).

(b) Suppression des tables EHS et CLHS comme envisagé dans mon précédent message, du fait que le trigger RECOUVREMENT_PERIODE_COHS_INSERT_TR les rend inutiles.

Code Looping aménagé :

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
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
----------------------------------
-- Les salles
----------------------------------

CREATE TABLE SALLE
(
        salleId        INT IDENTITY
      , salleCode      CHAR(4)        NOT NULL
      , salleNom       VARCHAR(32)    NOT NULL
    , CONSTRAINT SALLE_PK PRIMARY KEY(salleId)
    , CONSTRAINT SALLE_AK UNIQUE(salleCode)
    , CONSTRAINT SALLE_1_AK UNIQUE(salleNom)
) ;
 
--------------------------------------------
-- Niveau de chaque classe (6e, 5e, 4e, 3e)
--------------------------------------------
 
CREATE TABLE NIVEAU
(
   niveauId INT IDENTITY,
   niveauCode CHAR(1) NOT NULL,
   CONSTRAINT NIVEAU_PK PRIMARY KEY(niveauId),
   CONSTRAINT NIVEAU_AK UNIQUE(niveauCode)
);
  
--------------------------------------------
-- Les classes (du niveau 6e, 5e, 4e, 3e)
--------------------------------------------

CREATE TABLE CLASSE
(
        classeId      INT IDENTITY 
      , classeNumero  INT            NOT NULL 
      , classeNom     VARCHAR(16)    NOT NULL 
      , niveauId      INT            NOT NULL
    , CONSTRAINT CLASSE_PK PRIMARY KEY(classeId) 
    , CONSTRAINT CLASSE_AK UNIQUE(classeNumero) 
    , CONSTRAINT CLASSE_NOM_AK UNIQUE(classeNom)
    , CONSTRAINT CLASSE_NIVEAU_FK FOREIGN KEY(niveauId) 
          REFERENCES NIVEAU(niveauId)
) ;

----------------------------------------------------
-- Les matières enseignées (français, maths, ...)
----------------------------------------------------

CREATE TABLE MATIERE
(
        matiereId     INT IDENTITY 
      , matiereCode   CHAR(8)        NOT NULL
      , matiereNom    VARCHAR(32)    NOT NULL
    , CONSTRAINT MATIERE_PK PRIMARY KEY(matiereId)
    , CONSTRAINT MATIERE_AK UNIQUE(matiereCode)
    , CONSTRAINT MATIERE_NOM_AK UNIQUE(matiereNom)
) ;
-------------------------------------------------
-- Les matières enseignées par niveau
-------------------------------------------------

CREATE TABLE MAT_NIV
(
        matiereId     INT  
      , niveauId      INT            
    , CONSTRAINT MAT_NIV_PK PRIMARY KEY(MatiereId, niveauId)
    , CONSTRAINT MAT_NIV_MATIERE_FK FOREIGN KEY(MatiereId) 
          REFERENCES MATIERE(MatiereId)
    , CONSTRAINT MAT_NIV_NIVEAU_FK FOREIGN KEY(niveauId) 
          REFERENCES NIVEAU(niveauId)
);

---------------------------------------
-- Les enseignants 
---------------------------------------

CREATE TABLE ENSEIGNANT
(
        enseignantId   INT IDENTITY 
      , enseignantCode CHAR(5)        NOT NULL
      , enseignantNom  VARCHAR(32)    NOT NULL
    , CONSTRAINT ENSEIGNANT_PK PRIMARY KEY(enseignantId)
    , CONSTRAINT ENSEIGNANT_AK UNIQUE(enseignantCode)
    , CONSTRAINT ENSEIGNANT_NOM_AK UNIQUE(enseignantNom)
);

-------------------------------------------------
-- Les matières pour lesquelles les enseignants 
-- sont qualifiés
-------------------------------------------------

CREATE TABLE A_LA_COMPETENCE
(
        enseignantId  INT            NOT NULL 
      , matiereId     INT            NOT NULL 
    , CONSTRAINT A_LA_COMPETENCE_PK PRIMARY KEY(enseignantId, matiereId)
    , CONSTRAINT A_LA_COMPETENCE_ENSEIGNANT_FK FOREIGN KEY(enseignantId) 
          REFERENCES ENSEIGNANT(enseignantId)
    , CONSTRAINT A_LA_COMPETENCE_MATIERE_FK FOREIGN KEY(matiereId) 
          REFERENCES MATIERE(matiereId)
) ;

----------------------------------------------------
-- les matières enseignées dans les classes
-- par les enseignants.
-- A noter la DF : (matiereId, classeId) -> coursId
-----------------------------------------------------

CREATE TABLE COURS
(
        coursId       INT IDENTITY
      , enseignantId  INT            NOT NULL 
      , matiereId     INT            NOT NULL
      , classeId      INT            NOT NULL
    , CONSTRAINT COURS_PK PRIMARY KEY(coursId)
    , CONSTRAINT COURS_AK UNIQUE (matiereId, classeId)
    , CONSTRAINT COURS_ENSEIGNANT_FK FOREIGN KEY(enseignantId) 
          REFERENCES ENSEIGNANT(enseignantId)
    , CONSTRAINT COURS_MATIERE_FK FOREIGN KEY(matiereId) 
          REFERENCES MATIERE(matiereId)
    , CONSTRAINT COURS_CLASSE_FK FOREIGN KEY(classeId) 
          REFERENCES CLASSE(classeId)
) ;

------------------------------------------------
-- Occupation des salles par les cours.
------------------------------------------------

CREATE TABLE COHS
(
        coursId        INT              NOT NULL
      , jourHeureDebut DATETIME2        NOT NULL
      , jourHeureFin   DATETIME2        NOT NULL
      , salleId        INT              NOT NULL 
    , CONSTRAINT COHS_PK PRIMARY KEY(coursId, jourHeureDebut)
    , CONSTRAINT COHS_AK UNIQUE (salleId, jourHeureDebut) -- DF : SALLE X HORAIRE -> COURS
    , CONSTRAINT COHS_COURS_FK FOREIGN KEY(coursId)
          REFERENCES COURS(coursId)
    , CONSTRAINT COHS_SALLE_FK FOREIGN KEY(salleId) 
          REFERENCES SALLE(salleId)
    , CONSTRAINT COHS_HH_FIN_SUP_CHK CHECK  (jourHeureFin > jourHeureDebut)
    , CONSTRAINT COHS_MEME_JOUR_CHK CHECK  (DATEDIFF(DAY, jourHeureFin, jourHeureDebut) = 0)
);

Des vues.

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
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
--------------------------------------------------
-- Une vue pour simplifier les requêtes
-- mettant en jeu les classes, les matières 
-- et les niveaux.
--------------------------------------------------

GO
CREATE VIEW CLASSE_MAT
AS
SELECT DISTINCT classeId, t.matiereId, classeNom, t.matiereCode
FROM   CLASSE as x
  JOIN NIVEAU as y ON x.niveauId = y.niveauId
  JOIN MAT_NIV as z ON y.niveauId = z.niveauId
  JOIN MATIERE as t ON t.matiereId = z.matiereId
;
GO

--------------------------------------------------
-- Une vue pour simplifier les requêtes
-- mettant en jeu les cours, les classes, 
-- les matières, les niveaux et les enseignants.
--------------------------------------------------

GO

CREATE VIEW COURS_V (enseignantId, coursId, classeId, matiereId
                   , enseignantCode, enseignantNom, matiereCode, classeNom) 
AS

SELECT x.enseignantId, x.coursId, x.classeId, x.matiereId
     , enseignantCode, enseignantNom, matiereCode, classeNom
FROM   COURS as x
  JOIN CLASSE as y ON x.classeId = y.classeId
  JOIN MAT_NIV as z ON y.niveauId = z.niveauId
  JOIN MATIERE as t ON x.matiereId = t.matiereId          
  JOIN ENSEIGNANT as u ON x.enseignantId = u.enseignantId
;
GO

-----------------------------------------------------
-- Une vue pour voir l'emploi du temps
-----------------------------------------------------

CREATE VIEW EMPLOI_DU_TEMPS (enseignantId, classeId, coursId
                           , salleId
                           , salleNom
                           , enseignantNom
                           , classeNom
                           , matiereNom
                           , jourNom
                           , jourHeureDebut
                           , jourHeureFin)
AS
SELECT y.enseignantId, x.classeid, y.coursId
     , z.salleId
     , salleNom
     , enseignantNom 
     , classeNom
     , matiereNom
     , DATENAME(WEEKDAY, jourHeureDebut) 
     , CAST(jourHeureDebut as VARCHAR(19))
     , CAST(jourHeureFin as VARCHAR(19))
FROM   CLASSE as x
  JOIN COURS as y ON x.classeId = y.classeId
  JOIN COHS as z ON y.coursId = z.coursId
  JOIN SALLE as t ON t.salleId = z.salleId
  JOIN ENSEIGNANT as u ON y.enseignantId = u.enseignantId
  JOIN MATIERE as v ON y.matiereId = v.matiereId
;
GO

Des fonctions, accompagnées des ALTER TABLE si besoin.

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
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
81
82
83
84
85
86
87
88
89
90
----------------------------------------------------
-- Une fonction à utiliser pour s'assurer que 
-- les matières enseignées dans les classes
-- font partie du programme défini pour chaque 
-- niveau de classe.
----------------------------------------------------
GO
CREATE FUNCTION COURS_COHERENCE()
RETURNS INT  
AS   
BEGIN  
   DECLARE @n INT  

   SELECT @n = COUNT(*)
   FROM   COURS_V as x 
   WHERE  NOT EXISTS
         (
          SELECT ''
          FROM   CLASSE_MAT as y
          WHERE  x.classeId = y.classeId
            AND  x.matiereId = y.matiereId 
         )
   RETURN @n  
END ; 
GO  

 
-----------------------------------------------
-- Un enseignant ne peut dispenser que des
-- cours pour lesquels il est qualifié.
-- La fonction COURS_COMPETENCE_INCLUSION
-- a pour objet de s'en assurer.
-- En entrée de la fonction :
-- l'identifiant de l'enseignant et celui
-- de la matière enseignée.
-- Si cet enseignant n'est pas qualifié,
-- la fonction fournit en sortie la valeur 0. 
-- Cette fonction répond à la contrainte
-- Inclusion 1 du MCD.
-----------------------------------------------
GO
CREATE FUNCTION COURS_COMPETENCE_INCLUSION(@enseignantId INT, @matiereId INT)
RETURNS INT  
AS   
BEGIN  
   DECLARE @n INT  

   SELECT @n = COUNT(*) 
   FROM   A_LA_COMPETENCE 
   WHERE  enseignantId = @enseignantId AND matiereId = @matiereId  
   RETURN @n  
END ;  
GO  

---------------------------------------------------
-- Prise en compte de la fonction pour la 
-- contrainte ad-hoc
--------------------------------------------------

ALTER TABLE COURS  
    ADD CONSTRAINT COURS_COMPETENCE_CHK 
        CHECK (dbo.COURS_COMPETENCE_INCLUSION(enseignantId, matiereId) > 0) ;  

---------------------------------------------------
-- un enseignant ne peut enseigner une matière à
-- une classe que si cette matière est au programme
-- de la classe (contrainte Inclusion 2 du MCD). 
---------------------------------------------------
GO

CREATE FUNCTION COURS_NIVEAU_INCLUSION(@matiereId INT, @classeId INT)
RETURNS INT  
AS   
BEGIN  
   DECLARE @n INT  

   SELECT @n = COUNT(*) 
   FROM   CLASSE as x 
   JOIN   MAT_NIV as y ON x.niveauId = y.niveauId 
   WHERE  y.matiereId = @matiereId AND x.classeId = @classeId
   RETURN @n  
END ; 
GO

-----------------------------------
-- activation de la contrainte
-----------------------------------
ALTER TABLE COURS  
    ADD CONSTRAINT COURS_NIVEAU_CHK 
        CHECK (dbo.COURS_NIVEAU_INCLUSION(matiereId, classeId) > 0) ;

Affectation des salles : le trigger pour contrôler les INSERT

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
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
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
---------------------------------------------------------
-- Une salle ne peut accueillir un cours
-- à un moment et pour une durée donnés 
-- qu'à la condition de ne pas être 
-- occupée à ce moment-là. 
-- Les CIF du MCD sont là pour attirer
-- l'attention, mais en l'absence de types 
-- du genre intervalle, SQL Server ne permet
-- pas de contrôler les recouvrements :
-- à nous de développer le code ad-hoc.
-- Le trigger RECOUVREMENT_PERIODE_COHS_INSERT_TR
-- est là pour ça et a donc 
-- pour objet de bloquer les INSERT
-- en infraction (reste à développer un trigger
-- pour les UPDATE).
---------------------------------------------------------
GO
CREATE TRIGGER RECOUVREMENT_PERIODE_COHS_INSERT_TR ON COHS INSTEAD OF INSERT
AS
DECLARE @theTrigger as VARCHAR(64) = 'RECOUVREMENT_PERIODE_COHS_INSERT_TR'
DECLARE @EngueuladeIntro AS VARCHAR(128) = @theTrigger 
                                         + ' - Affectation des salles aux cours : '  
                                         + CHAR(13) + '        '  ;
DECLARE @Engueulade AS VARCHAR(512) ;
DECLARE @errorCountInserted AS VARCHAR(64) = 'Une seule affectation à la fois SVP.' ;
DECLARE @erreurEntreeNull AS VARCHAR(64) = 'Le bonhomme NULL est interdit ' ;
DECLARE @erreurDebutFin AS VARCHAR(64) = 'La date de début doit être > à la date de fin'
DECLARE @erreurMemeJour AS VARCHAR(64) = 'L''heure de début et l''heure doivent être du même jour'
DECLARE @salleId AS INT ; 
DECLARE @salleNom AS VARCHAR(32) ; 
DECLARE @enseignantId AS INT ; 
DECLARE @enseignantNom AS VARCHAR(32) ; 
DECLARE @enseignantNomDejaInserted AS VARCHAR(32) ; 
DECLARE @coursId AS INT ; 
DECLARE @coursNom AS VARCHAR(2) ; 
DECLARE @matiereId AS INT ;
DECLARE @matiereCode AS CHAR(8) ; 
DECLARE @matiereNom AS VARCHAR(32) ;
DECLARE @classeId AS INT ; 
DECLARE @classeNom AS VARCHAR(32) ;
DECLARE @debut AS DATETIME2 ;
DECLARE @debutDejaInserted AS DATETIME2 ;
DECLARE @fin DATETIME2 ;
DECLARE @finDejaInserted DATETIME2 ;
DECLARE @countSalleCOHS AS INT = 0  ;
DECLARE @countEnseignantEHS AS INT = 0  ;
DECLARE @countClasseCLHS AS INT = 0  ;
DECLARE @coursDejaAffecteId as INT ;
DECLARE @coursDejaAffecteNom as VARCHAR(2) ;
DECLARE @countJumeaux AS INT ;
DECLARE @erreurDetectee AS INT = 0 ;
DECLARE @cr AS CHAR(1) = CHAR(13) ;
--------------------------------------
-- Des tables temporaires pour se 
-- simplifier la vie 
--------------------------------------
DECLARE @salleDejaAffecteeAcours AS TABLE 
       (enseignantId INT, coursId INT
      , debut DATETIME2, fin DATETIME2, salleId INT) ;

DECLARE @enseignantDejaAffecte AS TABLE 
       (enseignantId INT, coursId INT
      , debut DATETIME2, fin DATETIME2, salleId INT) ;

DECLARE @salleDejaAffecteeAclasse AS TABLE 
       (enseignantId INT, coursId INT
      , debut DATETIME2, fin DATETIME2, salleId INT) ;


-------------------------------------------------------
-- Pour le moment on interdit le multi-insert
-------------------------------------------------------

IF (SELECT COUNT(*) from INSERTED) > 1
   BEGIN
         SET @Engueulade = @EngueuladeIntro + @errorCountInserted
         SELECT TOP 5 @Engueulade AS Engueulons, * FROM INSERTED
 --------        RAISERROR (@Engueulade,16,1)  -- state = 16 pour bloquer  
         RAISERROR (@Engueulade,0,1)  -- state = 0 pour les tests 
         RETURN
   END

-------------------------------------------------
-- Récup des données fournies par INSERTED
-------------------------------------------------
SET @salleId = (SELECT salleId FROM INSERTED) ;
SET @salleNom = (SELECT salleNom FROM SALLE WHERE salleId = @salleId) ;
SET @enseignantId = (SELECT enseignantId FROM INSERTED as x JOIN COURS as y ON x.coursId = y.coursId) ;
SET @enseignantNom = (SELECT enseignantNom FROM ENSEIGNANT WHERE enseignantId = @enseignantId) ;
SET @coursId = (SELECT coursId FROM INSERTED) ;
SET @coursNom = CAST(@coursId as VARCHAR(2)) ;
SET @matiereId = (SELECT matiereId FROM INSERTED as x JOIN COURS as y ON x.coursId = y.coursId) ;
SET @matiereNom = (SELECT matiereNom FROM MATIERE WHERE matiereId = @matiereId) ;
SET @classeId = (SELECT classeId FROM COURS WHERE coursId = @coursId) ;
SET @classeNom = (SELECT classeNom FROM CLASSE WHERE classeId = @classeId) ;
SET @debut = (SELECT jourHeureDebut FROM INSERTED) ;
SET @fin = (SELECT jourHeureFin FROM INSERTED) ;

----------------------------------------------------------
-- Une donnée en entrée ne doit être marquée NULL
----------------------------------------------------------
 
IF @coursId IS NULL       
    BEGIN
        SET @Engueulade = TRIM(@EngueuladeIntro) 
                        + @erreurEntreeNull + '(coursId)'
        SELECT @Engueulade AS Engueulons, * FROM INSERTED
---------------         RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
        RAISERROR (@Engueulade, 0,1)  -- state = 0 pour les tests
        RETURN  
    END
 
IF @salleId IS NULL       
    BEGIN
        SET @Engueulade = @EngueuladeIntro + @erreurEntreeNull + '(salle).'
        SELECT @Engueulade AS Engueulons, * FROM INSERTED
---------------         RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
        RAISERROR (@Engueulade, 0,1)  -- state = 0 pour les tests
        RETURN  
    END
 
IF @enseignantId IS NULL       
    BEGIN
        SET @Engueulade = @EngueuladeIntro + @erreurEntreeNull + '(enseignant).'
        SELECT @Engueulade AS Engueulons, * FROM INSERTED
---------------         RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
        RAISERROR (@Engueulade, 0,1)  -- state = 0 pour les tests
        RETURN  
    END
  
IF @classeId IS NULL       
    BEGIN
        SET @Engueulade = @EngueuladeIntro + @erreurEntreeNull + '(classe).'
        SELECT @Engueulade AS Engueulons, * FROM INSERTED
---------------         RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
        RAISERROR (@Engueulade, 0,1)  -- state = 0 pour les tests
        RETURN  
    END

IF @debut IS NULL  
    BEGIN
        SET @Engueulade = @EngueuladeIntro + @erreurEntreeNull + '(heure de début).'
        SELECT @Engueulade AS Engueulons, * FROM INSERTED
---------------         RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
        RAISERROR (@Engueulade, 0,1)  -- state = 0 pour les tests
        RETURN  
    END
 
IF @fin IS NULL  
    BEGIN
        SET @Engueulade = @EngueuladeIntro + @erreurEntreeNull + '(heure de fin).'
        SELECT @Engueulade AS Engueulons, * FROM INSERTED
---------------         RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
        RAISERROR (@Engueulade, 0,1)  -- state = 0 pour les tests
        RETURN  
    END

----------------------------------------------------------
-- Quelques contrôles de cohérence des dates
----------------------------------------------------------

IF @debut > @fin 
    BEGIN
        SET @Engueulade = @EngueuladeIntro + @erreurDebutFin
        SELECT @Engueulade AS Engueulons, * FROM INSERTED
---------------         RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
        RAISERROR (@Engueulade, 0,1)  -- state = 0 pour les tests
        RETURN  
    END

IF DATEDIFF(DAY, @fin, @debut) <> 0 
    BEGIN
        SET @Engueulade = @EngueuladeIntro + @erreurMemeJour
        SELECT @Engueulade AS Engueulons, * FROM INSERTED
---------------         RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
        RAISERROR (@Engueulade, 0,1)  -- state = 0 pour les tests
        RETURN  
    END

-----------------------------------------------
-- Une salle ne peut pas héberger deux cours
-- en même temps (DF COHS : C x H -> S)
-----------------------------------------------

SET @countSalleCOHS = 
    (SELECT COUNT(*)  
     FROM   COHS as x 
     WHERE  x.salleId = @salleId
       AND  DAY(@debut) = DAY(x.jourHeureDebut)
       AND  
           (
            (@fin BETWEEN x.jourHeureDebut and x.jourHeureFin
                  OR @debut BETWEEN x.jourHeureDebut and x.jourHeureFin)
               OR @debut <= x.jourHeureDebut AND @fin >= x.jourHeureFin 
           )
    ) ;
IF @countSalleCOHS > 0
    BEGIN
        ----------------------------------------------------------
        -- Savoir à quel cours la salle à affecter l'est déjà
        -----------------------------------------------------------

        INSERT INTO @salleDejaAffecteeAcours
            SELECT TOP 1 enseignantId, x.coursId, jourHeureDebut, jourHeureFin, salleId  
            FROM   COHS as x JOIN COURS as y ON x.coursId = y.coursId 
            WHERE  salleId = @salleId
              AND  DAY(@debut) = DAY(jourHeureDebut)
            AND  
                 (
                   (@fin BETWEEN jourHeureDebut and jourHeureFin
                         OR @debut BETWEEN jourHeureDebut and jourHeureFin)
                    OR @debut <= jourHeureDebut AND @fin >= jourHeureFin 
                 )
        ; 
        SET @coursdejaAffecteNom = 
            CAST((SELECT coursid 
                  FROM   @salleDejaAffecteeAcours) as VARCHAR(2)) 
        ;
        SELECT 'Oh ! ' as 'Oh !', @theTrigger AS 'Trigger' ;
        SET @Engueulade =  
              'Rejet de l''affectation de la salle ''' + @salleNom + ''''
            + ', pour le cours ' + @coursNom 
            + ' (''' 
            + @matiereNom + '''' 
            + ', donné par ' + @enseignantNom
            + ', à la classe de ' + @classeNom
            + ', prévu le ' 
            + LEFT(CAST(@debut as VARCHAR(19)), 10)  
            + ', de ' 
            + RIGHT(CAST(@debut as VARCHAR(19)), 8) 
            + ' à ' 
            + RIGHT(CAST(@fin as VARCHAR(19)), 8) 
            + ')'
        ;
        SELECT @Engueulade AS 'Hé !'
        SET @Engueulade =  
              'Car cette salle est déjà réservée pour le cours '
            + @coursdejaAffecteNom 
            + ' ('''  
            + (SELECT matiereNom 
               FROM   @salleDejaAffecteeAcours as x 
                 JOIN COURS as y ON x.enseignantId = y.enseignantId
                                AND x.coursId = y.coursId
                 JOIN MATIERE as z ON y.matiereId = z.matiereId) + '''' 
            + ', donné ce jour-là par ' 
            + (SELECT enseignantNom 
               FROM   @salleDejaAffecteeAcours as x 
                 JOIN ENSEIGNANT as y ON x.enseignantId = y.enseignantId)
            + ', à la classe de ' 
            + (SELECT classeNom 
               FROM   @salleDejaAffecteeAcours as x 
                 JOIN COURS as y ON  x.enseignantId = y.enseignantId 
                                 AND x.coursId = y.coursId
                 JOIN CLASSE as z ON y.classeId =z.classeId)
           + ', de '
            + RIGHT(CAST((SELECT debut FROM @salleDejaAffecteeAcours) as VARCHAR(19)), 8) 
            + ' à ' 
            + RIGHT(CAST((SELECT fin FROM @salleDejaAffecteeAcours) as VARCHAR(19)), 8) 
            + ')'
         ;
        SELECT @Engueulade AS 'Hein !'
---------------         RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
        RAISERROR (@Engueulade, 0,1)  -- state = 0 pour les tests
        SET @erreurDetectee = 1        
    END

--------------------------------------------------------------
-- Un enseignant ne peut pas être dans 2 salles
-- en même temps DF EHS :  enseignant x horaire -> salle 
--------------------------------------------------------------

SET @countEnseignantEHS = 
         (SELECT COUNT(*)  
          FROM   COHS as x 
            JOIN COURS as y ON x.coursId = y.coursId
          WHERE  y.enseignantId = @enseignantId
            AND  DAY(@debut) = DAY(x.jourHeureDebut)
            AND  
                 (
                   (@fin BETWEEN x.jourHeureDebut and x.jourHeureFin
                    OR @debut BETWEEN x.jourHeureDebut and x.jourHeureFin)
                   OR @debut <= x.jourHeureDebut AND @fin >= x.jourHeureFin 
                 )
         ) ; 

IF @countEnseignantEHS > 0
    BEGIN
        INSERT INTO @enseignantDejaAffecte
            SELECT TOP 1 enseignantId, x.coursId, jourHeureDebut, jourHeureFin, salleId  
            FROM   COHS as x JOIN COURS as y ON x.coursId = y.coursId 
            WHERE  y.enseignantId = @enseignantId
              AND  DAY(@debut) = DAY(jourHeureDebut)
            AND  
                 (
                   (@fin BETWEEN jourHeureDebut AND jourHeureFin
                    OR @debut BETWEEN jourHeureDebut AND jourHeureFin)
                   OR @debut <= jourHeureDebut AND @fin >= jourHeureFin 
                 )
    ;

-----------------------------------------------
-- Si à la fois recouvrement de Toto par Toto 
-- et donc bilocation pour Toto,
-- inutile de rouspéter 2 fois. 
----------------------------------------------

        SET @countJumeaux =
           (SELECT COUNT(*) 
            FROM   (SELECT * 
                    FROM   @salleDejaAffecteeAcours 
                   INTERSECT 
                    SELECT * 
                    FROM   @enseignantDejaAffecte) as x)
        ;       
        IF @countJumeaux = 0
            BEGIN
                SELECT 'Oh ! ' as 'Oh !', @theTrigger AS 'Trigger'
                SET @Engueulade =  
                      'Rejet de l''affectation de la salle ''' + @salleNom + ''''
                    + ', pour le cours ' + @coursNom 
                    + ''' (' 
                    + @matiereNom + '''' 
                    + ', donné par ' + @enseignantNom
                    + ', à la classe de ' + @classeNom
                    + ', prévu le ' 
                    + LEFT(CAST(@debut as VARCHAR(19)), 10)  
                    + ', de ' 
                    + RIGHT(CAST(@debut as VARCHAR(19)), 8) 
                    + ' à ' 
                   + RIGHT(CAST(@fin as VARCHAR(19)), 8) 
                   + ')'
                ;        
                SELECT @Engueulade AS 'Hé !'

                SET @coursDejaAffecteId = (SELECT coursid 
                                           FROM  @enseignantDejaAffecte)
                SET @coursDejaAffecteNom = CAST(@coursDejaAffecteId as VARCHAR(2))
                ;
                SET @Engueulade =  
                      'Car pour cet enseignant il y a déjà une affectation, salle '''
                    + (SELECT salleNom 
                       FROM   SALLE as x 
                         JOIN @enseignantDejaAffecte as y ON x.salleId = y.salleId)   
                    + ''', pour le cours '
                    + @coursDejaAffecteNom 
                    + ' ('''
                    + (SELECT DISTINCT matiereNom 
                       FROM   @enseignantDejaAffecte as x 
                         JOIN COURS as y ON x.enseignantId = y.enseignantId
                                        AND x.coursId = y.coursId
                         JOIN MATIERE as z ON y.matiereId = z.matiereId
                       WHERE x.enseignantId = @enseignantId)
                    + '''' 
                    + ', donné à la classe de ' 
                    + (SELECT DISTINCT classeNom 
                       FROM   @enseignantDejaAffecte as x 
                         JOIN COURS as y ON  x.enseignantId = y.enseignantId 
                                         AND x.coursId = y.coursId
                         JOIN CLASSE as z ON y.classeId =z.classeId) + ''''
                    + ', de ' 
                    + RIGHT(CAST((SELECT debut FROM @enseignantDejaAffecte) as VARCHAR(19)), 8) 
                    + ' à ' 
                    + RIGHT(CAST((SELECT fin FROM @enseignantDejaAffecte) as VARCHAR(19)), 8)
                    + ')' 
                ;
                SELECT @Engueulade AS 'Hein !'
       ------         RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
                RAISERROR (@Engueulade, 0,1)  -- state = 0 pour les tests
            END
        SET @erreurDetectee = 1        
    END

--------------------------------------------------------------
-- Une classe ne peut pas être dans 2 salles
-- en même temps. DF CLHS :  classe x horaire -> salle 
--------------------------------------------------------------

SET @countClasseCLHS = 
     (SELECT COUNT(*)  
      FROM   COHS as x 
        JOIN COURS as y ON x.coursId = y.coursId
      WHERE  y.classeId = @classeId
        AND  DAY(@debut) = DAY(x.jourHeureDebut)
        AND  
             (
               (@fin BETWEEN x.jourHeureDebut and x.jourHeureFin
                OR @debut BETWEEN x.jourHeureDebut and x.jourHeureFin)
               OR @debut <= x.jourHeureDebut AND @fin >= x.jourHeureFin 
             )
     ) ; 

IF @countClasseCLHS > 0
    BEGIN
        INSERT INTO @salleDejaAffecteeAclasse
            SELECT TOP 1 enseignantId, x.coursId, jourHeureDebut, jourHeureFin, salleId  
            FROM   COHS as x JOIN COURS as y ON x.coursId = y.coursId 
            WHERE  classeId = @classeId
              AND  DAY(@debut) = DAY(jourHeureDebut)
            AND  
                 (
                   (@fin BETWEEN jourHeureDebut and jourHeureFin
                         OR @debut BETWEEN jourHeureDebut and jourHeureFin)
                    OR @debut <= jourHeureDebut AND @fin >= jourHeureFin 
                 )
        ;
 
        SET @coursDejaAffecteId = (SELECT coursid 
                                   FROM   @salleDejaAffecteeAclasse)
        SET @coursDejaAffecteNom = CAST(@coursDejaAffecteId as VARCHAR(2))
        ;
        SELECT 'Oh ! ' as 'Oh !', @theTrigger AS 'Trigger'
        SET @Engueulade =  
              'Rejet de l''affectation de la salle ''' + @salleNom + ''''
            + ', pour le cours '
            + @coursNom 
            + ' (''' 
            + @matiereNom + '''' 
            + ', donné par ' + @enseignantNom
            + ', à la classe de ' + @classeNom
            + ', prévu le ' 
            + LEFT(CAST(@debut as VARCHAR(19)), 10)  
            + ', de ' 
            + RIGHT(CAST(@debut as VARCHAR(19)), 8) 
            + ' à ' 
            + RIGHT(CAST(@fin as VARCHAR(19)), 8) 
            + ')'
        ;

        SELECT @Engueulade AS 'Hé !'

        SET @Engueulade =  
              'Car pour cette classe, cette salle est déjà réservée pour le cours '
            + @coursDejaAffecteNom 
            + ' (''' 
            + (SELECT matiereNom 
               FROM   @salleDejaAffecteeAclasse as x 
                 JOIN COURS as y ON x.enseignantId = y.enseignantId
                                AND x.coursId = y.coursId
                 JOIN MATIERE as z ON y.matiereId = z.matiereId) + '''' 
            + ', donné ce jour-là par ' 
            + (SELECT enseignantNom 
               FROM   @salleDejaAffecteeAclasse as x 
                 JOIN ENSEIGNANT as y ON x.enseignantId = y.enseignantId)
            + ' de '
            + RIGHT(CAST((SELECT debut FROM @salleDejaAffecteeAclasse) as VARCHAR(19)), 8) 
            + ' à '   
            + RIGHT(CAST((SELECT fin FROM @salleDejaAffecteeAclasse) as VARCHAR(19)), 8) 
            + ')'
       ;

        SELECT @Engueulade AS 'Hein !'
---------------         RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
        RAISERROR (@Engueulade, 0,1)  -- state = 0 pour les tests
        SET @erreurDetectee = 1        
    END

---------------------------------   
-- Si pas d'anomalie, on insère
---------------------------------

IF @erreurDetectee = 0
    BEGIN
        INSERT INTO COHS (coursId, jourHeureDebut, jourHeureFin, salleId)
          (SELECT coursId, jourHeureDebut, jourHeureFin, salleId 
           FROM   INSERTED) 
    END
;
GO

En espérant qu’il y a un minimum d’erreurs de copier/coller...

A suivre : un jeu d’essai.

**fsmrel** · 03/03/2021, 05h18

Bonsoir,

J’ai modifié mon message précédent, j’y ai aménagé la présentation des erreurs interceptées par le trigger, c’est-à-dire les erreurs concernant l’affectation des salles.

Un jeu d’essai :

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
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
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
-----------------------------------------------
-- Les salles
-----------------------------------------------

INSERT INTO SALLE (salleCode, salleNom)
VALUES 
    ('sa01', 'salle 1'),  ('sa02', 'salle 2'),  ('sa03', 'salle 3')  
  , ('sa04', 'salle 4'),  ('sa05', 'salle 5'),  ('sa06', 'salle 6')  
  , ('sa07', 'salle 7'),  ('sa08', 'salle 8'),  ('sa09', 'salle 9')  
  , ('sa10', 'salle 10'),  ('sa11', 'salle 11'),  ('sa12', 'salle 12')  
  , ('sa13', 'salle 13'),  ('sa14', 'salle 14'),  ('sa15', 'salle 15')  
  , ('sa16', 'salle 16'),  ('sa17', 'salle 17'),  ('sa18', 'salle 18')  
  , ('sa19', 'salle 19'),  ('sa20', 'salle 20')  
;

SELECT salleCode, salleNom FROM SALLE ; 

-----------------------------------------------
-- Les niveaux
-----------------------------------------------

INSERT INTO NIVEAU (niveauCode)
VALUES 
--    ((0)), ((1)), ((2)), ((3)), ((4)), ((5)), ((6)) ;
    ((3)), ((4)), ((5)), ((6)) ;

INSERT INTO CLASSE(classeNumero, classeNom, niveauId)
VALUES 
    (61, '6e A', (SELECT niveauId FROM NIVEAU WHERE niveauCode = '6'))
  , (62, '6e B', (SELECT niveauId FROM NIVEAU WHERE niveauCode = '6'))
  , (63, '6e C', (SELECT niveauId FROM NIVEAU WHERE niveauCode = '6'))
  , (64, '6e D', (SELECT niveauId FROM NIVEAU WHERE niveauCode = '6'))
  , (65, '6e E', (SELECT niveauId FROM NIVEAU WHERE niveauCode = '6'))

  , (51, '5e A', (SELECT niveauId FROM NIVEAU WHERE niveauCode = '5'))
  , (52, '5e B', (SELECT niveauId FROM NIVEAU WHERE niveauCode = '5'))
  , (53, '5e C', (SELECT niveauId FROM NIVEAU WHERE niveauCode = '5'))
  , (54, '5e D', (SELECT niveauId FROM NIVEAU WHERE niveauCode = '5'))

  , (41, '4e A', (SELECT niveauId FROM NIVEAU WHERE niveauCode = '4'))
  , (42, '4e B', (SELECT niveauId FROM NIVEAU WHERE niveauCode = '4'))
  , (43, '4e C', (SELECT niveauId FROM NIVEAU WHERE niveauCode = '4'))
  , (44, '4e D', (SELECT niveauId FROM NIVEAU WHERE niveauCode = '4'))

  , (31, '3e A', (SELECT niveauId FROM NIVEAU WHERE niveauCode = '3'))
  , (32, '3e B', (SELECT niveauId FROM NIVEAU WHERE niveauCode = '3'))
  , (33, '3e C', (SELECT niveauId FROM NIVEAU WHERE niveauCode = '3'))
  , (34, '3e D', (SELECT niveauId FROM NIVEAU WHERE niveauCode = '3'))

SELECT classeNumero, classeNom, niveauCode 
FROM   CLASSE as x
  JOIN NIVEAU as y ON x.niveauId = y.niveauId
; 

---------------------------------------------
-- Les matières
---------------------------------------------

INSERT INTO MATIERE(matiereCode, matiereNom)
VALUES 
    ('français', 'français'), ('maths', 'mathématiques'),  ('philo', 'philosophe')   
  , ('latin', 'latin'), ('histoire', 'histoire'), ('géo', 'géographie') 
  , ('physique', 'physique'), ('chimie', 'chimie'), ('grec', 'grec')   
  , ('allemand', 'allemand'), ('espagnol', 'espagnol'), ('anglais', 'anglais')
  , ('italien', 'italien'), ('gym', 'gymnastique'), ('musique', 'musique') 
  , ('économie', 'science économique')
;

SELECT matiereCode, matiereNom FROM MATIERE ; 

--------------------------------------------
-- Les matières par niveau
--------------------------------------------

INSERT INTO MAT_NIV (matiereId, niveauId)
VALUES
    (  (SELECT matiereId FROM MATIERE WHERE matiereCode = 'français')
     , (SELECT niveauId FROM NIVEAU WHERE niveauCode = 3)
    )
,
    (  (SELECT matiereId FROM MATIERE WHERE matiereCode = 'maths')
     , (SELECT niveauId FROM NIVEAU WHERE niveauCode = 3)
    )
,
    (  (SELECT matiereId FROM MATIERE WHERE matiereCode = 'anglais')
     , (SELECT niveauId FROM NIVEAU WHERE niveauCode = 3)
    )
,
    (  (SELECT matiereId FROM MATIERE WHERE matiereCode = 'histoire')
     , (SELECT niveauId FROM NIVEAU WHERE niveauCode = 3)
    )
,
    (  (SELECT matiereId FROM MATIERE WHERE matiereCode = 'géo')
     , (SELECT niveauId FROM NIVEAU WHERE niveauCode = 3)
    )
,
    (  (SELECT matiereId FROM MATIERE WHERE matiereCode = 'gym')
     , (SELECT niveauId FROM NIVEAU WHERE niveauCode = 3)
    )
,
    (  (SELECT matiereId FROM MATIERE WHERE matiereCode = 'latin')
     , (SELECT niveauId FROM NIVEAU WHERE niveauCode = 3)
    )
,
    (  (SELECT matiereId FROM MATIERE WHERE matiereCode = 'physique')
     , (SELECT niveauId FROM NIVEAU WHERE niveauCode = 3)
    )
,
    (  (SELECT matiereId FROM MATIERE WHERE matiereCode = 'chimie')
     , (SELECT niveauId FROM NIVEAU WHERE niveauCode = 3)
    )
,
    (  (SELECT matiereId FROM MATIERE WHERE matiereCode = 'espagnol')
     , (SELECT niveauId FROM NIVEAU WHERE niveauCode = 3)
    )
;

INSERT INTO MAT_NIV (matiereId, niveauId)
VALUES
    (  (SELECT matiereId FROM MATIERE WHERE matiereCode = 'français')
     , (SELECT niveauId FROM NIVEAU WHERE niveauCode = 4)
    )
,
    (  (SELECT matiereId FROM MATIERE WHERE matiereCode = 'maths')
     , (SELECT niveauId FROM NIVEAU WHERE niveauCode = 4)
    )
,
    (  (SELECT matiereId FROM MATIERE WHERE matiereCode = 'anglais')
     , (SELECT niveauId FROM NIVEAU WHERE niveauCode = 4)
    )
,
    (  (SELECT matiereId FROM MATIERE WHERE matiereCode = 'histoire')
     , (SELECT niveauId FROM NIVEAU WHERE niveauCode = 4)
    )
,
    (  (SELECT matiereId FROM MATIERE WHERE matiereCode = 'géo')
     , (SELECT niveauId FROM NIVEAU WHERE niveauCode = 4)
    )
,
    (  (SELECT matiereId FROM MATIERE WHERE matiereCode = 'gym')
     , (SELECT niveauId FROM NIVEAU WHERE niveauCode = 4)
    )
,
    (  (SELECT matiereId FROM MATIERE WHERE matiereCode = 'espagnol')
     , (SELECT niveauId FROM NIVEAU WHERE niveauCode = 4)
    )
;

INSERT INTO MAT_NIV (matiereId, niveauId)
VALUES
    (  (SELECT matiereId FROM MATIERE WHERE matiereCode = 'français')
     , (SELECT niveauId FROM NIVEAU WHERE niveauCode = 5)
    )
,
    (  (SELECT matiereId FROM MATIERE WHERE matiereCode = 'maths')
     , (SELECT niveauId FROM NIVEAU WHERE niveauCode = 5)
    )
,
    (  (SELECT matiereId FROM MATIERE WHERE matiereCode = 'anglais')
     , (SELECT niveauId FROM NIVEAU WHERE niveauCode = 5)
    )
,
    (  (SELECT matiereId FROM MATIERE WHERE matiereCode = 'histoire')
     , (SELECT niveauId FROM NIVEAU WHERE niveauCode = 5)
    )
,
    (  (SELECT matiereId FROM MATIERE WHERE matiereCode = 'géo')
     , (SELECT niveauId FROM NIVEAU WHERE niveauCode = 5)
    )
,
    (  (SELECT matiereId FROM MATIERE WHERE matiereCode = 'gym')
     , (SELECT niveauId FROM NIVEAU WHERE niveauCode = 5)
    )
;

INSERT INTO MAT_NIV (matiereId, niveauId)
VALUES
    (  (SELECT matiereId FROM MATIERE WHERE matiereCode = 'français')
     , (SELECT niveauId FROM NIVEAU WHERE niveauCode = 6)
    )
,
    (  (SELECT matiereId FROM MATIERE WHERE matiereCode = 'maths')
     , (SELECT niveauId FROM NIVEAU WHERE niveauCode = 6)
    )
,
    (  (SELECT matiereId FROM MATIERE WHERE matiereCode = 'anglais')
     , (SELECT niveauId FROM NIVEAU where niveauCode = 6)
    )
,
    (  (SELECT matiereId FROM MATIERE WHERE matiereCode = 'histoire')
     , (SELECT niveauId FROM NIVEAU WHERE niveauCode = 6)
    )
,
    (  (SELECT matiereId FROM MATIERE WHERE matiereCode = 'géo')
     , (SELECT niveauId FROM NIVEAU WHERE niveauCode = 6)
    )
;

SELECT matiereCode, niveauCode 
FROM   MAT_NIV as x
  JOIN MATIERE as y ON x.matiereId = y.matiereId  
  JOIN NIVEAU as z ON x.niveauId = z.niveauId
;

----------------------------------------------
-- Les enseignants
----------------------------------------------

INSERT INTO ENSEIGNANT (enseignantCode, enseignantNom)
VALUES 
    ('FENAU', 'Fernand Naudin'), ('FRFOL', 'Francis Folace')
  , ('RAVOL', 'Raoul Volfoni'), ('PAVOL', 'Paul Volfoni'), ('MMADO', 'Mme Mado')
  , ('PALOU', 'Patricia Louis'),('THCOU', 'Theo Courant')
  , ('ANDEL', 'Antoine Delafoy'), ('ADADE', 'Adolphe Amédée Delafoy')
  , ('CHTO', 'Jean Yes Sir'), ('JEYS', 'Charles Tomate')
  , ('NIBOH', 'Niels Bohr'), ('ALEIN', 'Albert Einstein')
;
SELECT enseignantCode, enseignantNom FROM ENSEIGNANT ; 

-----------------------------------------------------
-- Les compétences des enseignants
-----------------------------------------------------

INSERT INTO A_LA_COMPETENCE (enseignantId, matiereId)
VALUES 
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'NIBOH')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'physique')
    )
  ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'ALEIN')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'espagnol')
    )
  ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'ALEIN')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'physique')
    )
    ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'ALEIN')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'géo')
    )
,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'FENAU')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'gym')
    )
  ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'FENAU')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'italien')
    )
  ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'FENAU')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'philo')
    )
  ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'FENAU')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'maths')
    )
,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'FRFOL')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'français')
    )
,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'FRFOL')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'maths')
    )
  ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'FRFOL')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'philo')
    )
,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'FRFOL')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'économie')
    )
,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'RAVOL')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'chimie')
    )
,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'RAVOL')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'économie')
    )
   ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'RAVOL')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'physique')
    )
  ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'RAVOL')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'français')
    )
,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'RAVOL')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'espagnol')
    )
,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'PAVOL')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'maths')
    )
,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'PAVOL')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'anglais')
    )
,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'PAVOL')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'histoire')
    )
  ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'PAVOL')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'physique')
    )
,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'PALOU')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'histoire')
    )
  ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'PALOU')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'géo')
    )
  ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'PALOU')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'maths')
    )
,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'PALOU')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'allemand')
    )
  ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'THCOU')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'anglais')
    )
  ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'THCOU')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'allemand')
    )
  ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'THCOU')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'gym')
    )
   ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'ANDEL')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'français')
    )
   ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'ANDEL')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'musique')
    )
   ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'ANDEL')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'latin')
    )
   ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'ANDEL')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'grec')
    )
   ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'ADADE')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'français')
    )
   ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'ADADE')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'latin')
    )
   ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'ADADE')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'gym')
    )
   ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'ADADE')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'histoire')
    )
   ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'ADADE')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'géo')
    )
   ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'MMADO')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'français')
    )
  ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'MMADO')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'histoire')
    )
   ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'MMADO')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'anglais')
    )
   ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'JEYS')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'maths')
    )
   ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'JEYS')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'chimie')
    )
   ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'JEYS')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'espagnol')
    )
   ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'JEYS')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'histoire')
    )
   ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'JEYS')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'géo')
    )
   ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'CHTO')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'histoire')
    )
   ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'CHTO')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'géo')
    )
   ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'CHTO')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'physique')
    )
   ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'CHTO')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'chimie')
    )
   ,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'CHTO')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'latin')
    )
;

SELECT enseignantCode, matiereCode 
FROM   A_LA_COMPETENCE as x   
  JOIN ENSEIGNANT as y ON x.enseignantId = y.enseignantId
  JOIN MATIERE as z ON x.matiereId = z.matiereId
;

----------------------------------------
-- Les cours donnés par les enseignants
-- aux classes
----------------------------------------

INSERT INTO COURS (enseignantId, matiereId, classeId)
VALUES
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'NIBOH')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'physique')
     , (SELECT classeId FROM CLASSE WHERE classeNom = '3e C')
    )
,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'ALEIN')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'physique')
     , (SELECT classeId FROM CLASSE WHERE classeNom = '3e D')
    )
,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'ALEIN')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'espagnol')
     , (SELECT classeId FROM CLASSE WHERE classeNom = '4e A')
    )
,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'ALEIN')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'géo')
     , (SELECT classeId FROM CLASSE WHERE classeNom = '4e D')
    )
,

    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'FENAU')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'gym')
     , (SELECT classeId FROM CLASSE WHERE classeNom = '4e B')
    )
,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'FENAU')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'maths')
     , (SELECT classeId FROM CLASSE WHERE classeNom = '6e A')
    )
,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'FENAU')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'maths')
     , (SELECT classeId FROM CLASSE WHERE classeNom = '5e A')
    )
;

INSERT INTO COURS (enseignantId, matiereId, classeId)
VALUES
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'ADADE')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'gym')
     , (SELECT classeId FROM CLASSE WHERE classeNom = '5e A')
    )
;

INSERT INTO COURS (enseignantId, matiereId, classeId)
VALUES
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'RAVOL')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'physique')
     , (SELECT classeId FROM CLASSE WHERE classeNom = '3e A')
    )
;

INSERT INTO COURS (enseignantId, matiereId, classeId)
VALUES
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'RAVOL')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'espagnol')
     , (SELECT classeId FROM CLASSE WHERE classeNom = '4e C')
    )
;
INSERT INTO COURS (enseignantId, matiereId, classeId)
VALUES
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'RAVOL')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'espagnol')
     , (SELECT classeId FROM CLASSE WHERE classeNom = '3e B')
    )
;
INSERT INTO COURS (enseignantId, matiereId, classeId)
VALUES
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'RAVOL')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'français')
     , (SELECT classeId FROM CLASSE WHERE classeNom = '6e A')
    )
;
INSERT INTO COURS (enseignantId, matiereId, classeId)
VALUES
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'RAVOL')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'français')
     , (SELECT classeId FROM CLASSE WHERE classeNom = '6e B')
    )
;
INSERT INTO COURS (enseignantId, matiereId, classeId)
VALUES
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'MMADO')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'histoire')
     , (SELECT classeId FROM CLASSE WHERE classeNom = '4e C')
    )
;
INSERT INTO COURS (enseignantId, matiereId, classeId)
VALUES
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'MMADO')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'anglais')
     , (SELECT classeId FROM CLASSE WHERE classeNom = '4e A')
    )
;
INSERT INTO COURS (enseignantId, matiereId, classeId)
VALUES
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'PALOU')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'histoire')
     , (SELECT classeId FROM CLASSE WHERE classeNom = '5e A')
    )
;
INSERT INTO COURS (enseignantId, matiereId, classeId)
VALUES
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'PALOU')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'géo')
     , (SELECT classeId FROM CLASSE WHERE classeNom = '4e A')
    )
;
INSERT INTO COURS (enseignantId, matiereId, classeId)
VALUES
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'PALOU')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'géo')
     , (SELECT classeId FROM CLASSE WHERE classeNom = '4e C')
    )
;
INSERT INTO COURS (enseignantId, matiereId, classeId)
VALUES
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'PALOU')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'géo')
     , (SELECT classeId FROM CLASSE WHERE classeNom = '5e A')
    )
;

INSERT INTO COURS (enseignantId, matiereId, classeId)
VALUES

    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'PAVOL')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'physique')
     , (SELECT classeId FROM CLASSE WHERE classeNom = '3e B')
    )
,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'PAVOL')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'histoire')
     , (SELECT classeId FROM CLASSE WHERE classeNom = '6e B')
    )
,
    (
       (SELECT enseignantId FROM ENSEIGNANT WHERE enseignantCode = 'PAVOL')
     , (SELECT matiereId FROM MATIERE WHERE matiereCode = 'anglais')
     , (SELECT classeId FROM CLASSE WHERE classeNom = '6e A')
    )
;

-----------------------------------------------------------------
-- Voir les matières enseignées aux classes par les enseignants
-----------------------------------------------------------------

SELECT DISTINCT '' as THE_COURS_V,
                enseignantCode, enseignantNom
              , matiereCode, classeNom  
FROM   COURS_V ;

---------------------------------------
-- Occupation des salles
--------------------------------------- 

SELECT '' as 'ce qui suit doit être accepté'

INSERT INTO COHS (coursId, jourHeureDebut, jourHeureFin, salleId)
VALUES
    (
          (SELECT DISTINCT coursId 
           FROM   COURS_V 
           WHERE  enseignantCode = 'PAVOL'
              AND matiereCode ='physique'
              AND classeNom = '3e B')
        , '2021-02-19 17:00:00', '2021-02-19 17:59:00'
        , (SELECT salleId from SALLE where salleCode = 'sa01')
    )
;

SELECT '' as edt, * FROM EMPLOI_DU_TEMPS
ORDER BY salleId, jourHeureDebut ;

SELECT '' as 'ce qui suit doit être accepté'

INSERT INTO COHS (coursId, jourHeureDebut, jourHeureFin, salleId)
VALUES
    (
          (SELECT DISTINCT coursId 
           FROM   COURS_V 
           WHERE  enseignantCode = 'PAVOL'
              AND matiereCode ='physique'
              AND classeNom = '3e B')
        , '2021-02-19 07:10:00', '2021-02-19 07:30:00'
        , (SELECT salleId from SALLE where salleCode = 'sa01')
    )
;

SELECT '' as edt, * FROM EMPLOI_DU_TEMPS
ORDER BY salleId, jourHeureDebut ;

SELECT '' as 'ce qui suit doit être rejeté (salle déjà occupée)'

INSERT INTO COHS (coursId, jourHeureDebut, jourHeureFin, salleId)
VALUES
    (
          (SELECT DISTINCT coursId 
           FROM   COURS_V 
           WHERE  enseignantCode = 'RAVOL'
              AND matiereCode ='français'
              AND classeNom = '6e A')
      , '2021-02-19 16:10:00', '2021-02-19 17:29:00'
      , (SELECT salleId from SALLE where salleCode = 'sa01')
    )
;

SELECT '' as edt, * FROM EMPLOI_DU_TEMPS
ORDER BY salleId, jourHeureDebut ;

SELECT '' as 'ce qui suit doit être rejeté (salle déjà occupée)'

INSERT INTO COHS (coursId, jourHeureDebut, jourHeureFin, salleId)
VALUES
    (
          (SELECT DISTINCT coursId 
           FROM   COURS_V 
           WHERE  enseignantCode = 'RAVOL'
              AND matiereCode ='français'
              AND classeNom = '6e B')
        , '2021-02-19 06:30:00', '2021-02-19 08:00:00'
        , (SELECT salleId from SALLE where salleCode = 'sa01')
    )
;

SELECT '' as edt, * FROM EMPLOI_DU_TEMPS
ORDER BY salleId, jourHeureDebut ;

SELECT '' as 'ce qui suit doit être accepté'
   
INSERT INTO COHS (coursId, jourHeureDebut, jourHeureFin, salleId)
VALUES
    (
          (SELECT DISTINCT coursId 
           FROM   COURS_V 
           WHERE  enseignantCode = 'RAVOL'
              AND matiereCode ='français'
              AND classeNom = '6e A')
      , '2021-02-19 18:00:00', '2021-02-19 18:59:00'
      , (SELECT salleId from SALLE where salleCode = 'sa01')
    )
;

SELECT '' as edt, * FROM EMPLOI_DU_TEMPS
ORDER BY salleId, jourHeureDebut ;

SELECT '' as 'ce qui suit doit être accepté'

INSERT INTO COHS (coursId, jourHeureDebut, jourHeureFin, salleId)
VALUES
    (
          (SELECT DISTINCT coursId 
           FROM   COURS_V 
           WHERE  enseignantCode = 'RAVOL'
              AND matiereCode ='français'
              AND classeNom = '6e B')
      , '2021-02-19 16:00:00', '2021-02-19 16:59:00'
      , (SELECT salleId from SALLE where salleCode = 'sa01')
    )
;

SELECT '' as edt, * FROM EMPLOI_DU_TEMPS
ORDER BY salleId, jourHeureDebut ;

SELECT '' as 'ce qui suit est à rejeter : bilocation => viol EHS, enseignant x horaire -> salle'

INSERT INTO COHS (coursId, jourHeureDebut, jourHeureFin, salleId)
VALUES
    (
          (SELECT DISTINCT coursId 
           FROM   COURS_V 
           WHERE  enseignantCode = 'RAVOL'
              AND matiereCode ='français'
              AND classeNom = '6e B')
      , '2021-02-19 17:30:00', '2021-02-19 19:59:59'
      , (SELECT salleId from SALLE where salleCode = 'sa02')
    )
;

SELECT '' as edt, * FROM EMPLOI_DU_TEMPS
ORDER BY salleId, jourHeureDebut ;

SELECT '' as 'ce qui suit doit être accepté'

INSERT INTO COHS (coursId, jourHeureDebut, jourHeureFin, salleId)
VALUES
    (
          (SELECT DISTINCT coursId 
           FROM   COURS_V 
           WHERE  enseignantCode = 'NIBOH'
              AND matiereCode ='physique'
              AND classeNom = '3e C')
      , '1924-10-27 15:00:00', '1924-10-27 15:59:59'
      , (SELECT salleId from SALLE where salleCode = 'sa03')
    )
;

SELECT '' as edt, * FROM EMPLOI_DU_TEMPS
ORDER BY salleId, jourHeureDebut ;

SELECT 'ce qui suit viole COHS, cours x horaire -> salle (salle occupée par Niels)'

INSERT INTO COHS (coursId, jourHeureDebut, jourHeureFin, salleId)
VALUES
    (
          (SELECT DISTINCT coursId 
           FROM   COURS_V 
           WHERE  enseignantCode = 'ALEIN'
              AND matiereCode ='physique'
              AND classeNom = '3e D')
      , '1924-10-27 15:31:00', '1924-10-27 16:30:30'
      , (SELECT salleId FROM SALLE WHERE salleCode = 'sa03')
    )
;

SELECT '' as edt, * FROM EMPLOI_DU_TEMPS
ORDER BY salleId, jourHeureDebut ;

SELECT 'ce qui suit viole la DF COHS, cours x horaire -> salle (salle tjrs occupée par Niels)'

INSERT INTO COHS (coursId, jourHeureDebut, jourHeureFin, salleId)
VALUES
    (
          (SELECT DISTINCT coursId 
           FROM   COURS_V 
           WHERE  enseignantCode = 'ALEIN'
              AND matiereCode ='espagnol'
              AND classeNom = '4e A')
      , '1924-10-27 15:10:00', '1924-10-27 15:59:59'
      , (SELECT salleId from SALLE where salleCode = 'sa03')
    )
;

SELECT '' as edt, * FROM EMPLOI_DU_TEMPS
ORDER BY salleId, jourHeureDebut ;

SELECT 'ce qui suit doit passer (année 1924)'

INSERT INTO COHS (coursId, jourHeureDebut, jourHeureFin, salleId)
VALUES
    (
          (SELECT DISTINCT coursId 
           FROM   COURS_V 
           WHERE  enseignantCode = 'ALEIN'
              AND matiereCode ='espagnol'
              AND classeNom = '4e A')
      , '1924-10-27 15:33:00', '1924-10-27 17:40:00'
      , (SELECT salleId from SALLE where salleCode = 'sa01')
    )

SELECT '' as edt, * FROM EMPLOI_DU_TEMPS
ORDER BY salleId, jourHeureDebut ;
;

SELECT 'ce qui suit doit passer  (1924, salle 3)'

INSERT INTO COHS (coursId, jourHeureDebut, jourHeureFin, salleId)
VALUES
    (
          (SELECT DISTINCT coursId 
           FROM   COURS_V 
           WHERE  enseignantCode = 'ALEIN'
              AND matiereCode ='espagnol'
              AND classeNom = '4e A')
      , '1924-10-27 21:30:00', '1924-10-27 22:40:00'
      , (SELECT salleId from SALLE where salleCode = 'sa03')
    )

SELECT '' as edt, * FROM EMPLOI_DU_TEMPS
ORDER BY salleId, jourHeureDebut ;
;

SELECT 'ce qui suit ne doit pas passer (salle occupée et auto bilocation interceptée !)'

INSERT INTO COHS (coursId, jourHeureDebut, jourHeureFin, salleId)
VALUES
    (
          (SELECT DISTINCT coursId 
           FROM   COURS_V 
           WHERE  enseignantCode = 'ALEIN'
              AND matiereCode ='espagnol'
              AND classeNom = '4e A')
      , '1924-10-27 17:35:00', '1924-10-27 18:40:00'
      , (SELECT salleId FROM SALLE WHERE salleCode = 'sa01')
    )

SELECT '' as edt, * FROM EMPLOI_DU_TEMPS
ORDER BY salleId, jourHeureDebut ;
;

SELECT 'ce qui suit ne doit pas passer (salle occupée (COHS) et bilocation(EHS))'

INSERT INTO COHS (coursId, jourHeureDebut, jourHeureFin, salleId)
VALUES
    (
          (SELECT DISTINCT coursId 
           FROM   COURS_V 
           WHERE  enseignantCode = 'ALEIN'
              AND matiereCode ='géo'
              AND classeNom = '4e D')
      , '1924-10-27 15:40:00', '1924-10-27 19:58:58'
      , (SELECT salleId from SALLE where salleCode = 'sa03')
    )
;

SELECT '' as edt, * FROM EMPLOI_DU_TEMPS
ORDER BY salleId, jourHeureDebut ;
;

SELECT 'ce qui suit doit passer '

INSERT INTO COHS (coursId, jourHeureDebut, jourHeureFin, salleId)
VALUES
    (
          (SELECT DISTINCT coursId 
           FROM   COURS_V 
           WHERE  enseignantCode = 'ALEIN'
              AND matiereCode ='espagnol'
              AND classeNom = '4e A')
      , '1924-10-27 18:39:00', '1924-10-27 18:57:57'
      , (SELECT salleId from SALLE where salleCode = 'sa03')
    )

SELECT '' as edt, * FROM EMPLOI_DU_TEMPS
ORDER BY salleId, jourHeureDebut ;
;

SELECT 'ce qui suit ne doit pas passer (salle occupée et quasi bilocation)'

INSERT INTO COHS (coursId, jourHeureDebut, jourHeureFin, salleId)
VALUES
    (
          (SELECT DISTINCT coursId 
           FROM   COURS_V 
           WHERE  enseignantCode = 'ALEIN'
              AND matiereCode ='espagnol'
              AND classeNom = '4e A')
      , '1924-10-27 15:39:00', '1924-10-27 17:04:00'
      , (SELECT salleId from SALLE where salleCode = 'sa03')
    )

SELECT '' as edt, * FROM EMPLOI_DU_TEMPS
ORDER BY salleId, jourHeureDebut ;
;

SELECT 'ce qui suit : rejeter (salle occupée, viol COHS cours x horaire -> salle)'

INSERT INTO COHS (coursId, jourHeureDebut, jourHeureFin, salleId)
VALUES
    (
          (SELECT DISTINCT coursId 
           FROM   COURS_V 
           WHERE  enseignantCode = 'ALEIN'
              AND matiereCode ='espagnol'
              AND classeNom = '4e A')
      , '2021-02-19 07:00:00', '2021-02-19 07:39:00'
      , (SELECT salleId from SALLE where salleCode = 'sa01')
    )

SELECT '' as edt, * FROM EMPLOI_DU_TEMPS
ORDER BY jourHeureDebut ;
;

SELECT 'SHC salle x horaire -> cours : couvert par COHS, ça doit d''abord passer pour Mado '

INSERT INTO COHS (coursId, jourHeureDebut, jourHeureFin, salleId)
VALUES
    (
          (SELECT DISTINCT coursId 
           FROM   COURS_V 
           WHERE  enseignantCode = 'MMADO'
              AND matiereCode ='anglais'
              AND classeNom = '4e A')
      , '2021-02-19 21:35:00', '2021-02-19 21:50:00'
      , (SELECT salleId FROM SALLE WHERE salleCode = 'sa03')
    )

SELECT '' as edt, * FROM EMPLOI_DU_TEMPS
ORDER BY salleId, jourHeureDebut ;

SELECT 'SHC salle x horaire -> cours : couvert par COHS, là ça ne doit pas passer pour Mado '

-- Mado veut donner un cours en salle S déjà occupée par elle-même

INSERT INTO COHS (coursId, jourHeureDebut, jourHeureFin, salleId)
VALUES
    (
          (SELECT DISTINCT coursId 
           FROM   COURS_V 
           WHERE  enseignantCode = 'MMADO'
              AND matiereCode ='histoire'
              AND classeNom = '4e C')
      , '2021-02-19 21:37:00', '2021-02-19 21:51:00'
      , (SELECT salleId from SALLE where salleCode = 'sa03')
    )   

SELECT '' as edt, * FROM EMPLOI_DU_TEMPS
ORDER BY salleId, jourHeureDebut ;

SELECT 'Mado veut donner un cours d''anglais en salle 4 occupée par Pat pour son cours d''histoire'

INSERT INTO COHS (coursId, jourHeureDebut, jourHeureFin, salleId)
VALUES
    (
          (SELECT DISTINCT coursId 
           FROM   COURS_V 
           WHERE  enseignantCode = 'PALOU'
              AND matiereCode ='histoire'
              AND classeNom = '5e A')
      , '2021-02-19 05:37:00', '2021-02-19 05:48:00'
      , (SELECT salleId from SALLE where salleCode = 'sa04')
    )
;

SELECT '' as edt, * FROM EMPLOI_DU_TEMPS
ORDER BY salleId, jourHeureDebut ;

INSERT INTO COHS (coursId, jourHeureDebut, jourHeureFin, salleId)
VALUES
    (
          (SELECT DISTINCT coursId 
           FROM   COURS_V 
           WHERE  enseignantCode = 'MMADO'
              AND matiereCode ='anglais'
              AND classeNom = '4e A')
      , '2021-02-19 05:35:00', '2021-02-19 05:50:00'
      , (SELECT salleId from SALLE where salleCode = 'sa04')
    )

SELECT  'vérifier blocage SHE ; salle x heure -> enseignant'

INSERT INTO COHS (coursId, jourHeureDebut, jourHeureFin, salleId)
VALUES
    (
          (SELECT DISTINCT coursId 
           FROM   COURS_V 
           WHERE  enseignantCode = 'MMADO'
              AND matiereCode ='anglais'
              AND classeNom = '4e A')
      , '2021-02-19 05:35:00', '2021-02-19 05:50:00'
      , (SELECT salleId from SALLE where salleCode = 'sa04')
    )
;
SELECT 'une classe biloquant dans deux salles, Pat doit être refusée'

INSERT INTO COHS (coursId, jourHeureDebut, jourHeureFin, salleId)
VALUES
    (
          (SELECT DISTINCT coursId 
           FROM   COURS_V 
           WHERE  enseignantCode = 'PALOU'
             AND matiereCode ='géo'
              AND classeNom = '4e A')
      , '2021-02-19 21:40:00', '2021-02-19 21:55:00'
      , (SELECT salleId from SALLE where salleCode = 'sa04')
    )
;
---------------------------------------
-- Voir l'emploi du temps
---------------------------------------

SELECT enseignantId as ensid, classeId as clasId
     , coursId, matiereId as matid, salleId
     , salleNom as salle, enseignantNom
     , classeNom as classe, matiereNom
     , jourHeureDebut, jourHeureFin 
FROM EMPLOI_DU_TEMPS
ORDER BY coursId, jourHeureDebut
;

---------------------------------------
-- Voir l'emploi du temps
---------------------------------------

SELECT enseignantId as ensid, classeId as clasId
     , coursId, matiereId as matid, salleId
     , salleNom as salle, enseignantNom
     , classeNom as classe, matiereNom
     , jourHeureDebut, jourHeureFin 
FROM EMPLOI_DU_TEMPS
ORDER BY coursId, jourHeureDebut ;

Au résultat :

ensid  clasId  coursId  matid  salleId  salle    enseignantNom    classe  matiereNom  jourHeureDebut        jourHeureFin
12     16      1        7      3        salle 3  Niels Bohr       3e C    physique    1924-10-27 15:00:00   1924-10-27 15:59:59
13     10      3       11      1        salle 1  Albert Einstein  4e A    espagnol    1924-10-27 15:33:00   1924-10-27 17:40:00
13     10      3       11      3        salle 3  Albert Einstein  4e A    espagnol    1924-10-27 18:39:00   1924-10-27 18:57:57
13     10      3       11      3        salle 3  Albert Einstein  4e A    espagnol    1924-10-27 21:30:00   1924-10-27 22:40:00
3      1       12      1       1        salle 1  Raoul Volfoni    6e A    français    2021-02-19 18:00:00   2021-02-19 18:59:00
3      2       13      1       1        salle 1  Raoul Volfoni    6e B    français    2021-02-19 16:00:00   2021-02-19 16:59:00
5      10      15      12      3        salle 3  Mme Mado         4e A    anglais     2021-02-19 21:35:00   2021-02-19 21:50:00
6      6       16      5       4        salle 4  Patricia Louis   5e A    histoire    2021-02-19 05:37:00   2021-02-19 05:48:00
4      15      20      7       1        salle 1  Paul Volfoni     3e B    physique    2021-02-19 07:10:00   2021-02-19 07:30:00
4       15     20      7       1        salle 1  Paul Volfoni     3e B    physique    2021-02-19 17:00:00   2021-02-19 17:59:00

Merci de me signaler les viols de CIF qui m’auront échappé

**fsmrel** · 03/03/2021, 16h18

Bonsoir,

@martinbrait : en relation avec les posts #10, #11 et #12, les résultats ci-dessous vous conviennent-ils ?

Les salles :

salleCode salleNom
--------- -----------
sa01      salle 1
sa02      salle 2
sa03      salle 3
sa04      salle 4
sa05      salle 5
sa06      salle 6
sa07      salle 7
sa08      salle 8
sa09      salle 9
sa10      salle 10
sa11      salle 11
sa12      salle 12
sa13      salle 13
sa14      salle 14
sa15      salle 15
sa16      salle 16
sa17      salle 17
sa18      salle 18
sa19      salle 19
sa20      salle 20

Les classes :

classeNumero    classeNom        niveauCode
------------    ----------       ----------
61              6e A             6
62              6e B             6
63              6e C             6
64              6e D             6
65              6e E             6
51              5e A             5
52              5e B             5
53              5e C             5
54              5e D             5
41              4e A             4
42              4e B             4
43              4e C             4
44              4e D             4
31              3e A             3
32              3e B             3
33              3e C             3
34              3e D             3

Les matières :

matiereCode   matiereNom
-----------   ----------------------
français      français
maths         mathématiques
philo         philosophe
latin         latin
histoire      histoire
géo           géographie
physique      physique
chimie        chimie
grec          grec
allemand      allemand
espagnol      espagnol
anglais       anglais
italien       italien
gym           gymnastique
musique       musique
économie      science économique

Les matières enseignées par niveau de classe :

matiereCode   niveauCode
-----------   ----------
anglais       3
anglais       4
anglais       5
anglais       6
chimie        3
espagnol      3
espagnol      4
français      3
français      4
français      5
français      6
géo           3
géo           4
géo           5
géo           6
gym           3
gym           4
gym           5
histoire      3
histoire      4
histoire      5
histoire      6
latin         3
maths         3
maths         4
maths         5
maths         6
physique      3

Les enseignants :

enseignantCode      enseignantNom
-------------- ---- --------------------------------
FENAU               Fernand Naudin
FRFOL               Francis Folace
RAVOL               Raoul Volfoni
PAVOL               Paul Volfoni
MMADO               Mme Mado
PALOU               Patricia Louis
THCOU               Theo Courant
ANDEL               Antoine Delafoy
ADADE               Adolphe Amédée Delafoy
CHTO                Jean Yes Sir
JEYS                Charles Tomate
NIBOH               Niels Bohr
ALEIN               Albert Einstein

Les compétences des enseignants :

enseignantCode      matiereCode
--------------      -----------
ADADE               français
ADADE               latin   
ADADE               histoire
ADADE               géo     
ADADE               gym     
ALEIN               géo     
ALEIN               physique
ALEIN               espagnol
ANDEL               français
ANDEL               latin   
ANDEL               grec    
ANDEL               musique 
CHTO                latin   
CHTO                histoire
CHTO                géo     
CHTO                physique
CHTO                chimie  
FENAU               maths   
FENAU               philo   
FENAU               italien 
FENAU               gym     
FRFOL               français
FRFOL               maths   
FRFOL               philo   
FRFOL               économie
JEYS                maths   
JEYS                histoire
JEYS                géo     
JEYS                chimie  
JEYS                espagnol
MMADO               français
MMADO               histoire
MMADO               anglais 
NIBOH               physique
PALOU               maths   
PALOU               histoire
PALOU               géo     
PALOU               allemand
PAVOL               maths   
PAVOL               histoire
PAVOL               physique
PAVOL               anglais 
RAVOL               français
RAVOL               physique
RAVOL               chimie  
RAVOL               espagnol
RAVOL               économie
THCOU               allemand
THCOU               anglais 
THCOU               gym

Les cours donnés par les enseignants aux classes (vue COURS_V) :

enseignantCode      enseignantNom                    matiereCode      classeNom
--------------      -------------------------------- -----------      ----------------
ADADE               Adolphe Amédée Delafoy           gym              5e A
ALEIN               Albert Einstein                  espagnol         4e A
ALEIN               Albert Einstein                  géo              4e D
ALEIN               Albert Einstein                  physique         3e D
FENAU               Fernand Naudin                   gym              4e B
FENAU               Fernand Naudin                   maths            5e A
FENAU               Fernand Naudin                   maths            6e A
MMADO               Mme Mado                         anglais          4e A
MMADO               Mme Mado                         histoire         4e C
NIBOH               Niels Bohr                       physique         3e C
PALOU               Patricia Louis                   géo              4e A
PALOU               Patricia Louis                   géo              4e C
PALOU               Patricia Louis                   géo              5e A
PALOU               Patricia Louis                   histoire         5e A
PAVOL               Paul Volfoni                     anglais          6e A
PAVOL               Paul Volfoni                     histoire         6e B
PAVOL               Paul Volfoni                     physique         3e B
RAVOL               Raoul Volfoni                    espagnol         3e B
RAVOL               Raoul Volfoni                    espagnol         4e C
RAVOL               Raoul Volfoni                    français         6e A
RAVOL               Raoul Volfoni                    français         6e B
RAVOL               Raoul Volfoni                    physique         3e A

Occupation des salles (vue EMPLOI_DU_TEMPS, cf. post #12) :

ensid  clasId  coursId  matid  salleId  salle    enseignantNom    classe  matiereNom  jourHeureDebut        jourHeureFin
12     16      1        7      3        salle 3  Niels Bohr       3e C    physique    1924-10-27 15:00:00   1924-10-27 15:59:59
13     10      3       11      1        salle 1  Albert Einstein  4e A    espagnol    1924-10-27 15:33:00   1924-10-27 17:40:00
13     10      3       11      3        salle 3  Albert Einstein  4e A    espagnol    1924-10-27 18:39:00   1924-10-27 18:57:57
13     10      3       11      3        salle 3  Albert Einstein  4e A    espagnol    1924-10-27 21:30:00   1924-10-27 22:40:00
3      1       12      1       1        salle 1  Raoul Volfoni    6e A    français    2021-02-19 18:00:00   2021-02-19 18:59:00
3      2       13      1       1        salle 1  Raoul Volfoni    6e B    français    2021-02-19 16:00:00   2021-02-19 16:59:00
5      10      15      12      3        salle 3  Mme Mado         4e A    anglais     2021-02-19 21:35:00   2021-02-19 21:50:00
6      6       16      5       4        salle 4  Patricia Louis   5e A    histoire    2021-02-19 05:37:00   2021-02-19 05:48:00
4      15      20      7       1        salle 1  Paul Volfoni     3e B    physique    2021-02-19 07:10:00   2021-02-19 07:30:00
4       15     20      7       1        salle 1  Paul Volfoni     3e B    physique    2021-02-19 17:00:00   2021-02-19 17:59:00

Je rappelle que EMPLOI_DU_TEMPS n’est pas une table (dérivée d’une association quaternaire du MCD), mais seulement une vue (cf. post #12).

**fsmrel** · 09/03/2021, 04h07

Bonsoir,

Dans la version que j’ai proposée dan mes précédents posts, EMPLOI_DU_TEMPS n’est pas une table (dérivée d’une association quaternaire du MCD), mais seulement une vue (cf. post #12).

Mais après tout, étudions l’aménagement du MCD faisant qu’EMPLOI_DU_TEMPS soit au stade du MLD une table on ne peut plus respectable.

Le MCD aménagé pourrait être le suivant :

Nom : win_ubuntu(cif)(quaternaire_CMHS).png
Affichages : 1230
Taille : 140,9 Ko

Les lettres C, M, E, H, S symbolisent respectivement les entités-types CLASSE, MATIERE, ENSEIGNANT, HORAIRE, ainsi que leurs identifiants selon les besoins.

En théorie, l’identifiant implicite de l’association EMPLOI_DU_TEMPS est le quadruplet CMHS et les AGL produiront un MLD dans lequel ce quadruplet sera donc clé primaire de la table EMPLOI_DU_TEMPS. Problème : cette clé est réductible.

En effet, comme on n’est pas dans un univers quantique il existe les règles de gestion suivantes :

A une heure donnée, une classe n’est présente que dans une seule salle et n’étudie qu’une matière
A une heure donnée, une salle n’accueille qu’une seule classe et on n’y étudie qu’une seule matière.

Ces règles se traduisent par les dépendances fonctionnelles suivantes :

CH → MS
SH → MC

DF qui donnent lieu aux clés candidates CH et SH. Le code SQL sera donc à aménager impérativement en conséquence (clé primaire à réduire et ajout d’une clé alternative).

Autres aménagements à apporter :

(a) Un cours ne peut être donné à une classe que si l’enseignant donnant ce cours est qualifié, on retrouve ainsi la contrainte d’inclusion connectant les associations ASSURE et A_LA_COMPETENCE présentes dans le MCD que j’avais proposé (cf. post #10), connectant cette fois-ci les associations COURS et QUALIF. Problème : les AGL ignorent la contrainte au stade du MLD (et du code SQL). A nous de jouer (aménagement du MCD, trigger ou fonction SQL...)

(b) Une paire CM dans EMPLOI_DU_TEMPS peut très bien être absente dans COURS, donc ipso facto être délinquante : une fois de plus contrainte d’inclusion en vue, mais comme d’habitude ignorée dans le MLD et dans le code SQL : à nous encore de nous débrouiller (trigger, fonction...)

(c) Rappel : l’enseignement d’une matière à une classe ne peut être donné que par un seul enseignant. Au stade SQL, modifier la clé primaire de la table COURS (réduire cette clé à la paire CM).

(d) Prise en compte des règles de gestion suivantes (non prises en compte dans le MCD afin de ne pas alourdir celui-ci) :

A une heure donnée, un enseignant ne peut être que dans une seule salle ;
A une heure donnée, une salle n’accueille qu’un enseignant.

=> Same player shoots again, triggers ou fonctions en perspective...

Conclusion : si on prend toutes les précautions nécessaires, l’association EMPLOI_DU_TEMPS du MCD est légale, et au stade relationnel la table qui en est inférée est légale elle aussi. A chacun de voir s’il préfère la table ou la vue, mais après production des triggers et fonctions garantissant le strict respect des règles de gestion.

N.B. L’AGL (qu’il s’agisse de Looping ou AMC) ne doit pas générer de table HORAIRE (effectivement avec les deux AGL cités, il n’y a pas de problème).

**Paprick** · 09/03/2021, 04h28

Bonsoir François,

Tu fais des MCD avec des contraintes d'inclusion en mode "Paint" maintenant ?

**fsmrel** · 09/03/2021, 19h59

Salve,

Envoyé par Paprick

Tu fais des MCD avec des contraintes d'inclusion en mode "Paint" maintenant ?

Yes, Sir ! Mais rassure-toi, il ne s’agit ici que d’un cartoon élaboré à partir de la version initiale proposée par win_ubuntu, pour montrer les transformations qu'il doit effectuer dans son MCD pour parvenir à un modèle dans lequel EMPLOI_DU_TEMPS reste une association quaternaire tout à fait respectable et légale, mais où il s’agit de prendre en compte certaines contraintes, en conformité avec les règles de gestion des données.

La version initiale façon cartoon est certes bien bariolée et grabouillée, mais équivalente à la version Looping que je joins :

Nom : win_ubuntu(cif) (quaternaire CMHS)looping_initial.png
Affichages : 1209
Taille : 21,1 Ko

Quoi qu’il en soit, dans tous les cas, les contraintes d’inclusion donnent lieu à pas mal de boulot pour que le code SQL résultant soit correct.

Par exemple, on peut commencer par demander à Looping de transformer QUALIF et COURS en entités-types :

Nom : win_ubuntu(cif) (quaternaire CMHS)looping_transfo 1.png
Affichages : 1219
Taille : 24,1 Ko

Dans un 2e temps on va associer ces deux entités-types, et on va déconnecter COURS de MATIERE, l’association C2 étant inférable par transitivité (COURS → QUALIF et QUALIF → MATIERE) :

Nom : win_ubuntu(cif) (quaternaire CMHS)looping_transfo 2(inclusion i1).png
Affichages : 1216
Taille : 25,1 Ko

A ce stade, on satisfait à la contrainte d’inclusion I1, mais il ne faut pas perdre de vue que le code SQL correspondant à la création de la table COURS sera à nettoyer, car il s’agit de respecter la dépendance fonctionnelle MC → E (MATIERE X CLASSE → ENSEIGNANT), donc réduire la clé primaire de la table COURS à la paire {matiereId, classeId}.

Une fois traitée la contrainte d’inclusion I1, on peut s’attaquer à sa soeur I2, c’est-à-dire débrancher CLASSE et EMPLOI_DU_TEMPS ainsi que MATIERE et EMPLOI_DU_TEMPS et brancher EMPLOI_DU_TEMPS sur COURS :

Nom : win_ubuntu(cif) (quaternaire CMHS)looping_transfo 3(inclusion i2).png
Affichages : 1209
Taille : 25,8 Ko

Comme demandé ci-dessus, au stade SQL on procède à la réduction de la clé primaire de la table COURS à la paire {matiereId, classeId} ; comme prévu dans le post #14, il s’agit aussi de réduire la clé primaire de la table EMPLOI_DU_TEMPS à la paire {classeId, jourHeureDebut} ou à la paire {salleId, jourHeureDebut}, sachant que la paire non retenue comme clé primaire fera l’objet d’une clé alternative. Ayant été éliminé de la clé primaire de la table COURS, l’attribut enseignantId doit disparaître de la table EMPLOI_DU_TEMPS.

Exemple :

CREATE TABLE COURS(
   matiereId INT,
   classeId INT,
   enseignantId INT,
   CONSTRAINT COURS_PK PRIMARY KEY(matiereId, classeId),
   CONSTRAINT COURS_QUALIF_FK FOREIGN KEY(matiereId, enseignantId) 
       REFERENCES QUALIF(matiereId, enseignantId),
   CONSTRAINT COURS_CLASSE_FK FOREIGN KEY(classeId) 
       REFERENCES CLASSE(classeId)
);
CREATE TABLE EMPLOI_DU_TEMPS
(
   matiereId INT,
   classeId INT,
   salleId INT,
   jourHeureDebut DATETIME2,
   jourHeureFin DATETIME2 NOT NULL,
   CONSTRAINT EMPLOI_DU_TEMPS_PK PRIMARY KEY (classeId, jourHeureDebut),
   CONSTRAINT EMPLOI_DU_TEMPS_AK UNIQUE (salleId, jourHeureDebut),
   CONSTRAINT EMPLOI_DU_TEMPS_SALLE_FK FOREIGN KEY(salleId) 
       REFERENCES SALLE(salleId),
   CONSTRAINT EMPLOI_DU_TEMPS_COURS_FK FOREIGN KEY(matiereId, classeId) 
       REFERENCES COURS(matiereId, classeId)
);

Tout ceci fait, reste à développer les triggers et fonctions contrôlant la bilocation des classes et des enseignants ainsi que les affectations simultanées des salles aux cours, etc., à l’instar, toutes choses égales, de ce qui été fait dans la version où EMPLOI_DU_TEMPS est une vue. Je vous renvoie en l’occurrence aux posts #10 et suivants.

Affectation hebdomadaire des salles

Schéma

Discussions similaires

Partager

Partager