1. Création de la base de données (cours.
sql)
Voici la structure SQL correspondant au schéma relationnel donné :
sql
Copier le code
-- Table Matiere
CREATE TABLE Matiere (
idm SERIAL PRIMARY KEY,
intitule TEXT NOT NULL,
nbs INTEGER NOT NULL CHECK (nbs > 0)
);
-- Table Intervenant
CREATE TABLE Intervenant (
idi SERIAL PRIMARY KEY,
nom TEXT NOT NULL,
prenom TEXT NOT NULL,
statut CHAR(1) CHECK (statut IN ('P', 'V'))
);
-- Table Etudiant
CREATE TABLE Etudiant (
ide SERIAL PRIMARY KEY,
nom TEXT NOT NULL,
prenom TEXT NOT NULL,
groupe TEXT NOT NULL
);
-- Table Salle
CREATE TABLE Salle (
nos SERIAL PRIMARY KEY,
typs INTEGER NOT NULL CHECK (typs IN (1, 2)),
contenance INTEGER NOT NULL CHECK (contenance > 0)
);
-- Table Cours
CREATE TABLE Cours (
idm INTEGER NOT NULL REFERENCES Matiere(idm),
nums INTEGER NOT NULL,
idi INTEGER NOT NULL REFERENCES Intervenant(idi),
nos INTEGER NOT NULL REFERENCES Salle(nos),
groupe TEXT NOT NULL,
dates DATE NOT NULL,
phor CHAR(2) CHECK (phor IN ('AM', 'PM')),
PRIMARY KEY (idm, nums, dates)
);
-- Table Evaluation
CREATE TABLE Evaluation (
idm INTEGER NOT NULL REFERENCES Matiere(idm),
ide INTEGER NOT NULL REFERENCES Etudiant(ide),
note NUMERIC(4, 2) NOT NULL CHECK (note >= 0 AND note <= 20),
PRIMARY KEY (idm, ide)
);
2. Création de la table Moyennes
Pour mémoriser la moyenne des notes obtenues par groupe d’étudiants et par matière,
voici la table :
sql
Copier le code
CREATE TABLE Moyennes (
idm INTEGER NOT NULL REFERENCES Matiere(idm),
groupe TEXT NOT NULL,
moyenne NUMERIC(5, 2) NOT NULL CHECK (moyenne >= 0 AND moyenne <= 20),
PRIMARY KEY (idm, groupe)
);
Contraintes ajoutées :
idm est une clé étrangère pointant vers Matiere.
groupe est une chaîne qui identifie le groupe des étudiants.
La contrainte CHECK garantit que les moyennes sont entre 0 et 20.
3. Insertion des données dans la table Moyennes
Pour insérer les moyennes calculées, une seule requête suffit :
sql
Copier le code
INSERT INTO Moyennes (idm, groupe, moyenne)
SELECT
[Link],
[Link],
AVG([Link]) AS moyenne
FROM Evaluation e
JOIN Etudiant et ON [Link] = [Link]
GROUP BY [Link], [Link];
Explication :
La requête regroupe les évaluations (Evaluation) par matière (idm) et groupe
(groupe).
Elle calcule la moyenne des notes avec AVG([Link]).
Les résultats sont ensuite insérés dans la table Moyennes.
4. Explications des questions (a) et (b)
(a) Pourquoi l’attribut groupe n’est pas une clé étrangère dans Cours ?
L’attribut groupe dans Cours fait référence à un groupe d’étudiants, mais il n’y a
pas de table spécifique définissant les groupes comme une entité à part entière.
Les groupes sont simplement identifiés dans la table Etudiant comme une information
descriptive.
Si groupe était une clé étrangère, il faudrait créer une table Groupes séparée
contenant les noms des groupes.
(b) Pourquoi nums fait partie de la clé primaire de Cours ?
nums identifie le numéro de séance pour une matière donnée (idm).
Une matière peut avoir plusieurs séances. Le numéro de séance (avec la date et
matière) distingue chaque cours.
Proposition pour une table Seance
La table Seance représenterait chaque séance de cours distinctement. Structure :
sql
Copier le code
CREATE TABLE Seance (
idm INTEGER NOT NULL REFERENCES Matiere(idm),
nums INTEGER NOT NULL,
dates DATE NOT NULL,
phor CHAR(2) CHECK (phor IN ('AM', 'PM')),
PRIMARY KEY (idm, nums, dates)
);
Modifications nécessaires :
Supprimer nums, dates, et phor de la table Cours.
Ajouter une clé étrangère pointant vers Seance dans Cours :
sql
Copier le code
ALTER TABLE Cours
ADD CONSTRAINT fk_seance FOREIGN KEY (idm, nums, dates) REFERENCES Seance(idm,
nums, dates);
5. Triggers
(a) Trigger pour vérifier la cohérence des groupes
Ce trigger empêche l’insertion d’un cours avec un groupe inexistant dans la table
Etudiant.
sql
Copier le code
CREATE OR REPLACE FUNCTION verifier_groupe()
RETURNS TRIGGER AS $$
BEGIN
IF NOT EXISTS (
SELECT 1
FROM Etudiant
WHERE groupe = [Link]
) THEN
RAISE EXCEPTION 'Le groupe % n''existe pas', [Link];
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER tg_verifier_groupe
BEFORE INSERT OR UPDATE ON Cours
FOR EACH ROW
EXECUTE FUNCTION verifier_groupe();
Test :
Cas bloquant : Insérer un cours avec un groupe inexistant.
Cas non bloquant : Insérer un cours avec un groupe existant.
(b) Trigger pour éviter la double séance
Ce trigger empêche qu’un intervenant réalise deux cours à la même date et pendant
la même plage horaire.
sql
Copier le code
CREATE OR REPLACE FUNCTION verifier_double_seance()
RETURNS TRIGGER AS $$
BEGIN
IF EXISTS (
SELECT 1
FROM Cours
WHERE idi = [Link]
AND dates = [Link]
AND phor = [Link]
) THEN
RAISE EXCEPTION 'L''intervenant % a déjà un cours à cette date et plage
horaire', [Link];
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER tg_verifier_double_seance
BEFORE INSERT OR UPDATE ON Cours
FOR EACH ROW
EXECUTE FUNCTION verifier_double_seance();
Test :
Cas bloquant : Insérer un cours pour le même intervenant à la même date et plage
horaire.
Cas non bloquant : Insérer un cours pour un autre intervenant ou une autre plage
horaire.
TESTE:
3. Tester les triggers :
Une fois ces triggers créés dans la base de données, vous pouvez tester leur
fonctionnement en insérant ou en mettant à jour des données dans la table Cours.
Cas bloquant (trigger pour vérifier la cohérence des groupes) :
Essayez d'insérer un cours avec un groupe qui n'existe pas dans la table Etudiant.
Par exemple, si le groupe G4 n'existe pas, l'insertion échouera.
sql
Copier le code
-- Cas bloquant : Insérer un cours avec un groupe inexistant (par exemple "G4")
INSERT INTO Cours (idm, nums, idi, salle, groupe, dates, phor)
VALUES (11, 1, 8, 'E106', 'G4', '2021-9-1', 'PM');
Cela générera l'exception :
vbnet
Copier le code
ERREUR: Le groupe "G4" n'existe pas
Cas non bloquant (trigger pour vérifier la cohérence des groupes) :
Insérez un cours avec un groupe qui existe dans la table Etudiant. Cela
fonctionnera sans erreur.
sql
Copier le code
-- Cas non bloquant : Insérer un cours avec un groupe existant (par exemple "G1")
INSERT INTO Cours (idm, nums, idi, salle, groupe, dates, phor)
VALUES (11, 1, 8, 'E106', 'G1', '2021-9-1', 'PM');
Cela s'exécutera sans erreur, car le groupe G1 existe dans la table Etudiant.
Cas bloquant (trigger pour éviter la double séance) :
Essayez d'insérer un cours où un intervenant a déjà un cours à la même date et
plage horaire. Par exemple, si l'intervenant 1 a déjà un cours à la date 2021-9-1
pendant la plage horaire PM, une nouvelle insertion échouera :
sql
Copier le code
-- Cas bloquant : Insérer un cours avec un intervenant ayant déjà un cours à la
même date et plage horaire
INSERT INTO Cours (idm, nums, idi, salle, groupe, dates, phor)
VALUES (11, 2, 1, 'E107', 'G1', '2021-9-1', 'PM');
Cela générera l'exception :
vbnet
Copier le code
ERREUR: L'intervenant 1 a déjà un cours à cette date et plage horaire
Cas non bloquant (trigger pour éviter la double séance) :
Insérez un cours avec un intervenant à une date et plage horaire où il n'a pas
encore de cours. Cela ne déclenchera pas d'exception.
sql
Copier le code
-- Cas non bloquant : Insérer un cours pour un intervenant qui n'a pas encore de
cours à cette date et plage horaire
INSERT INTO Cours (idm, nums, idi, salle, groupe, dates, phor)
VALUES (12, 3, 2, 'E105', 'G2', '2021-9-1', 'AM');
Cela s'exécutera sans erreur, car l'intervenant n'a pas de cours prévu à cette date
et plage horaire.
4. Vérifier le bon fonctionnement :
Après avoir effectué les tests, vous pouvez vérifier que les triggers ont bien
fonctionné et que les règles sont respectées.
Pour voir si les triggers sont activés, vous pouvez consulter la liste des triggers
avec la commande :
sql
Copier le code
\dC Cours;
Cela affichera les triggers associés à la table Cours.
Conclusion :
Les triggers doivent être créés et exécutés avant d'ajouter des données. Si un
enregistrement ne respecte pas les règles imposées par les triggers, une exception
sera levée, empêchant l'insertion ou la mise à jour des données. Vous pouvez
ensuite ajuster vos données en fonction des erreurs rencontrées.