adplus-dvertising

Exercices corrigés sur les triggers en Oracle - Exercice 1

Exercices corrigés sur les triggers en Oracle - Exercice 1

Pour gérer facilement la base de données de l'école, le gestionnaire de base de données décide de créer des déclencheurs. Voici un extrait du schéma de base de données:

  •  Etudiant(eid:integer, enom:string, age:integer, niveau:string, section:string)
  •  Cours(cnom:string, heure_cours:time, salle: string, #pid:integer)
  •  Inscription(#eid:integer, #cnom:string)
  •  Professeur(pid:integer, pnom:string, #depid:integer)
Recommandé :  Veuillez d'abord résoudre l'exercice, avant de passer à la solution.

Travail à faire

Exprimez les exigences suivantes en utilisant des déclencheurs :

  1. Chaque Cours a un effectif maximum de 30 étudiants.  Voir la réponse 
    CREATE OR REPLACE TRIGGER quest_1
    AFTER INSERT ON Inscription FOR EACH ROW
    DECLARE
        NB NUMBER;
    BEGIN
        SELECT COUNT(*) INTO NB FROM Inscription WHERE cnom=:NEW.cnom;
        IF NB>30 THEN
            RAISE_APPLICATION_ERROR(-20005,'Cours a un effectif maximum de 30 etudiants');
        END IF;
    END;
                                                
  2. Seuls les enseignants du département avec depid = 33 enseignent plus de trois cours.  Voir la réponse 
    CREATE OR REPLACE TRIGGER quest_2
    AFTER INSERT OR UPDATE ON Cours FOR EACH ROW
    DECLARE
        NB NUMBER;
        DEP NUMBER;
    BEGIN
        SELECT depid INTO DEP FROM Professeur WHERE pid=:NEW.pid;
        SELECT COUNT(*) INTO NB FROM Cours WHERE pid=:NEW.pid;
        IF DEP!=33 AND NB>3 THEN
            RAISE_APPLICATION_ERROR(-20005,'Seuls les enseignants du département avec depid = 33 enseignent plus de trois cours');
        END IF;
    END;
                                                
  3. Chaque étudiant doit être inscrit au cours "informatique théorique".  Voir la réponse 
    CREATE OR REPLACE TRIGGER quest_3
    AFTER INSERT ON Etudiant FOR EACH ROW
    BEGIN
        INSERT INTO Inscription VALUES(:NEW.eid,'Informatique théorique');
    END;
                                                
  4. Aucun département ne peut avoir plus de 10 enseignants.  Voir la réponse 
    CREATE OR REPLACE TRIGGER quest_4
    AFTER INSERT OR UPDATE ON Professeur FOR EACH ROW
    DECLARE
        NB NUMBER;
    BEGIN
        SELECT COUNT(*) INTO NB FROM Professeur WHERE depid=:NEW.depid;
        IF NB>10 THEN
            RAISE_APPLICATION_ERROR(-20005,'Aucun département ne peut avoir plus de 10 enseignants.');
        END IF;
    END;
                                                
  5. Le nombre d'élèves inscrits dans la section base de données doit être supérieur au nombre d'élèves dans la section Mathématiques.  Voir la réponse 
    CREATE OR REPLACE TRIGGER quest_5
    AFTER INSERT OR UPDATE ON Etudiant FOR EACH ROW
    WHEN NEW.niveau='Base de données' OR NEW.niveau='Mathématiques'
    DECLARE
        NB1 NUMBER;
        NB2 NUMBER;
    BEGIN
        SELECT COUNT(*) INTO NB1 FROM Etudiant WHERE niveau='Base de données';
        SELECT COUNT(*) INTO NB2 FROM Etudiant WHERE niveau='Mathématiques';
        IF NB2<NB1 THEN
            RAISE_APPLICATION_ERROR(-20005,'Le nombre d eleves inscrits dans la section base de donnees doit être superieur au nombre d eleves
    dans la section Mathematiques.');
        END IF;
    END;
                                                
  6. le nombre d'inscription aux cours enseignés par les enseignants du département avec depid = 33 est supérieur au nombre d'inscriptions dans la section mathématique.  Voir la réponse 
    CREATE OR REPLACE TRIGGER quest_6
    AFTER INSERT OR UPDATE ON Etudiant FOR EACH ROW
    WHEN NEW.niveau='Mathématiques'
    DECLARE
        NB1 NUMBER;
        NB1 NUMBER;
    BEGIN
        SELECT COUNT(*) INTO NB1 FROM Etudiant WHERE niveau='Mathématiques';
        SELECT COUNT(*) INTO NB2 FROM Professeur P INNER JOIN Cours C ON P.pid=C.pid INNER JOIN Inscription I ON I.cnom=C.cnom WHERE P.depid=33;
        IF NB1>NB2 THEN
            RAISE_APPLICATION_ERROR(-20005,'Nombre d inscription dans math depasse depid=33 ');
        END IF;
    END;
                                                
  7. Les professeurs de différents départements ne peuvent pas enseigner dans la même salle.  Voir la réponse 
    CREATE OR REPLACE TRIGGER quest_7
    BEFORE INSERT OR UPDATE ON Cours FOR EACH ROW
    WHEN NEW.pid>0
    DECLARE
        DEP1 NUMBER;
        NB NUMBER;
    BEGIN
        SELECT depid INTO DEP1 FROM Professeur WHERE pid=:NEW.pid;
        SELECT count(*) INTO NB FROM Cours C INNER JOIN Professeur P ON C.pid=P.pid WHERE C.salle=:NEW.salle AND P.depid!=DEP1;
        IF NB1>NB2 THEN
            RAISE_APPLICATION_ERROR(-20005,'Deux Courss ne peuvent pas se réunir dans la même salle en même temps.');
        END IF;
    END;
                                                
Partager ce cours avec tes amis :
 
Rédigé par ESSADDOUKI Mostafa
ESSADDOUKI
The education of the 21st century opens up opportunities to not merely teach, but to coach, mentor, nurture and inspire.