Université internationale de Rabat 2024 / 2025
Bases de données avancées – Feuille des exercices n° 1
3ème année de la filière "Ingénierie en Informatique"
(Réponses)
Nous considérons le script du fichier "[Link]" de création de tables représentant
les informations concernant le déroulement des enseignements du semestre en cours.
1) Exécuter le script de création de la table "PROFESSEUR".
2) Donner en suite la requête permettant d’ajouter la condition que "NumProf" est la clé
primaire de la table "PROFESSEUR".
ALTER TABLE Professeur
ADD ( PRIMARY KEY(NumProf) );
ALTER TABLE Professeur
ADD PRIMARY KEY(NumProf) ;
3) Donner la requête permettant d’ajouter à la table "PROFESSEUR" une colonne "bureau".
ALTER TABLE Professeur
ADD (bureau VARCHAR(10));
ALTER TABLE Professeur
ADD bureau VARCHAR(10);
ALTER TABLE Professeur
ADD COLUMN bureau VARCHAR(10);
ALTER TABLE Professeur
ADD COLUMN (bureau VARCHAR(10) );
4) Donner la requête permettant de modifier le type de l’attribut "Specialite" de la table
"PROFESSEUR" en "varchar2(80)".
ALTER TABLE Professeur
MODIFY specialte VARCHAR2(80);
5) Exécuter le script de création des autres tables.
6) Créer une vue "MODSANSNOTE(NumMod, NomMod)" avec les modules pour lesquels il
n’y a pas encore eu de notes.
CREATE VIEW Modsansnote(NumMod, NomMod) AS
SELECT NumMod, NomMod
FROM Module m
WHERE NOT EXISTS (SELECT NumMod
FROM ParticipantMod pm
WHERE [Link] = [Link] AND Note is NOT NULL);
CREATE VIEW Modsansnote2(NumMod, NomMod) AS
SELECT NumMod, NomMod
FROM Module m
WHERE NumMod NOT IN (SELECT NumMod
FROM ParticipantMod
WHERE Note is NOT NULL);
7) Créer une vue "PROFMOY(NumProf, NomProf, PrenomProf, Moyenne)" donnant pour
chaque professeur la moyenne des notes obtenues dans tous ses modules.
CREATE VIEW PROFMOY(NumProf, NomProf, PrenomProf, Moyenne) AS
SELECT NumProf, NomProf, PrenomProf , AVG(note) AS Moyenne
FROM Professeur NATURAL JOIN ProfMod NATURAL JOIN ParticipantMod
WHERE note IS NOT NULL
GROUP BY NumProf
8) Insérer des lignes dans toutes les tables en respectant les différentes contraintes d’intégrité.
La liste des modules doit contenir les modules "Java" et "XML" et la liste des participants
à ces modules ne doit pas être vide.
ALTER TABLE Professeur
DROP COLUMN DateNaiss;
INSERT INTO professeur VALUES(1001,'Mourid', 'Ali', 45, 'Machine Learning',
42000.00, 'E410');
INSERT INTO professeur VALUES(1031,'LATIFI', 'Nora', 35, 'Programmation',
52000.00, 'E415');
INSERT INTO professeur VALUES(1015,'Ramini', 'Laila', 61, 'Bases de donnees',
72000.00, 'E420');
…
INSERT INTO Module VALUES(510,'Java', 'Programmation en Java');
INSERT INTO Module VALUES(520,'XML', 'XML');
INSERT INTO Module VALUES(530,'Php', 'Php');
…
INSERT INTO Etudiant VALUES(7001,'Farouk', 'Ahmed',"1999-10-28");
INSERT INTO Etudiant VALUES(7002,'Malaki', 'Bochra',"1999-10-02");
INSERT INTO Etudiant VALUES(7003,'Hakik', 'Bli',"2000-05-17");
…
INSERT INTO profmod VALUES(520,1001);
INSERT INTO profmod VALUES(510,1031);
…
INSERT INTO participantmod VALUES(7001,510, 10);
INSERT INTO participantmod VALUES(7001,520, 13);
INSERT INTO participantmod VALUES(7003,530, 15.25);
….
9) Donner la requête permettant d’ajouter tous les étudiants à la liste des participants au
module “Java”. La table "PARTICIPANTMOD" contient les informations concernant
quels étudiants participent à quels modules.
On insert une ligne pour chaque étudiant non inscrit au module "Java" :
INSERT INTO Participantmod
SELECT NumET, NumMod, NULL
FROM Etudiant, Module m
WHERE NomMod = 'Java'
AND NumEt NOT IN (SELECT NumEt
FROM Participantmod
WHERE NumMod = [Link]);
10) Donner la requête permettant d’ajouter 2 points à la note obtenue par chaque étudiant au
module "Java".
UPDATE Participantmod
SET Note = Note +2
WHERE Note IS NOT NULL AND NumMod IN (
SELECT NumMod
FROM Module
WHERE NomMod = 'Java');
11) Donner la requête permettant d’imposer le fait que personne ne participe au module
"Java".
DELETE FROM Participantmod
WHERE NumMod IN (
SELECT NumMod
FROM Module
WHERE NomMod = 'Java');
12) Créer une table "PARTICIPANTMOD_ANC" pour y stocker les notes anciennes qui ont
subi un changement (On suppose que seul le champ note puisse subir des changements).
Cette table doit contenir les mêmes champs que la table PARTICIPANTMOD ainsi qu’un
champ représentant la date et l’heure du changement.
CREATE TABLE ParticipantMod_Anc(
NumEt integer REFERENCES Etudiant(NumEt),
NumMod integer REFERENCES Module(NumMod),
Note float,
DateChange timestamp
);
13) Créer un déclencheur permettant l’insertion d’une note (ancienne) dans la table
PARTICIPANTMOD_ANC avant qu’elle ne subisse un changement. Tester votre
déclencheur.
DELIMITER //
CREATE TRIGGER Note_Trig
BEFORE UPDATE ON Participantmod
FOR EACH ROW
INSERT INTO Participantmod_Anc VALUES ([Link], [Link],
[Link], current_timestamp() );
//
Tests :
DELIMITER ;
SELECT * FROM [Link];
SELECT * FROM uni.participantmod_anc;
UPDATE Participantmod
SET Note = NOTE + 1;
SELECT * FROM [Link];
SELECT * FROM uni.participantmod_anc;
14) Créer un déclencheur permettant d’imposer qu’à chaque changement du salaire d’un
professeur, le nouveau montant du salaire doit être réduit au double de l'ancien montant si ce
nouveau salaire dépasse le double de l’ancien
DELIMITER //
CREATE TRIGGER profsalaire_trig
BEFORE UPDATE ON professeur
FOR EACH ROW
BEGIN
IF ([Link]> 2*[Link]) THEN SET [Link] = 2*
[Link];
END IF;
END;
//
Tests :
DELIMITER ;
SELECT * FROM Professeur
WHERE NomProf = 'Latifi';
UPDATE Professeur
SET salaire = 110000.00
WHERE NomProf = 'Latifi';
SELECT * FROM Professeur
WHERE NomProf = 'Latifi';
