Correction examen SGBD

Ce document présente la correction d’un examen en Systèmes de Gestion de Bases de Données (SGBD). Il évalue des compétences en administration de bases de données, en SQL et PL/SQL, ainsi que la compréhension des mécanismes transactionnels et des procédures stockées.

Administration

Cette section teste les connaissances sur les transactions, la visibilité des données, les commandes SQL, les processus internes de la base, les vues du dictionnaire, les modes d’arrêt, et la gestion des profils utilisateurs.

1. Est-ce que l'administrateur peut voir les données en train d'être modifiées dans une transaction par les utilisateurs ?
   Réponse : Non. En effet, les modifications effectuées dans une transaction non validée ne sont pas visibles par d'autres sessions, y compris celles de l'administrateur.
2. Peut-on annuler partiellement une transaction ? Quelle commande permet cela ?
   Réponse : Oui, on peut annuler partiellement une transaction en utilisant la commande ROLLBACK TO SAVEPOINT (bien que la commande exacte ne soit pas précisée dans le texte, la réponse est affirmative).
3. Si deux sessions sont ouvertes avec le même utilisateur, une modification faite dans la première session est-elle visible dans la deuxième avant validation ?
   Réponse : Non, les modifications non validées dans une session ne sont pas visibles dans une autre session, même si c’est le même utilisateur.
4. Parmi les commandes SQL suivantes, lesquelles peuvent être annulées dans une transaction ?
   Commandes : INSERT, ALTER, CREATE, DROP, TRUNCATE, DELETE, UPDATE
   Réponse : Les commandes annulables sont INSERT, DELETE et UPDATE.
5. Quelles commandes valident automatiquement une transaction ?
   Réponse : ALTER, CREATE, DROP et TRUNCATE valident automatiquement la transaction.
6. Quand le processus DataBaseWriter « DBWn » écrit-il les données dans les fichiers de données ?
   Options :
   A. Après chaque validation de la transaction
   B. Avant valider la transaction
   C. Après le processus « LGWR »
   D. Avant ou après la validation de la transaction
   Réponse : La réponse exacte n’est pas explicitement donnée dans le texte. Cependant, la bonne réponse est généralement A (après validation), mais le document ne précise pas clairement.
7. Quels fichiers sont mis à jour par le processus DataBaseWriter « DBWn » ?
   Options :
   A. Fichiers de données
   B. Fichiers de données et fichiers de contrôles
   C. Fichiers de données et fichiers journaux
   D. Fichiers journaux et fichiers de contrôles
   Réponse : A. Le processus DBWn écrit dans les fichiers de données.
8. Qu'est-ce qui permet de récupérer les données non mises à jour dans les fichiers de données après un arrêt brutal ?
   Options :
   A. Fichiers journaux
   B. Segments UNDO
   C. Tablespace « SYSTEM »
   Réponse : A. Ce sont les fichiers journaux qui permettent la récupération.
9. Quelle vue du dictionnaire affiche la liste de tous les utilisateurs et leurs caractéristiques ?
   Options :
   - DBA_USERS
   - USER_USER
   - ALL_USER
   - V$SESSION
   Réponse : DBA_USERS.
10. Quelle vue affiche le nom de toutes les vues du dictionnaire de données ?
    Options :
    - DBA_NAMES
    - DBA_TABLES
    - DBA_DICTIONARY
    - DICTIONARY
    Réponse : DICTIONARY.
11. Quel mode d'arrêt choisir si un seul utilisateur effectue des manipulations critiques et que les autres ont fermé leur session ?
    Options :
    - SHUTDOWN
    - SHUTDOWN ABORT
    - SHUTDOWN NORMAL
    - SHUTDOWN IMMEDIATE
    - SHUTDOWN TRANSACTIONAL
    Réponse : SHUTDOWN TRANSACTIONAL, car ce mode attend la fin des transactions en cours avant d’arrêter.
12. Après cinq échecs de connexion, combien de temps doit-on attendre avant de pouvoir se reconnecter ? Quel paramètre l’indique ?
    Le profil utilisateur est configuré ainsi :
    FAILED_LOGIN_ATTEMPTS 5
    PASSWORD_LOCK_TIME 1/1440
    Réponse : Le paramètre PASSWORD_LOCK_TIME est fixé à 1/1440, ce qui correspond à 1 minute (car 1 jour = 1440 minutes). Donc, il faut attendre 1 minute avant de pouvoir se reconnecter.

SQL et PL/SQL - Partie I

Cette partie demande la création de vues SQL, de procédures PL/SQL, et de blocs PL/SQL pour manipuler et afficher des données.

1. Créer la vue LogicielsUnix contenant tous les logiciels de type 'Unix'.
   Commande :
   

   CREATE VIEW logicielsUnix AS
   SELECT * FROM logiciel
   WHERE UPPER(typeLog) = 'UNIX';

   Cette vue sélectionne toutes les colonnes de la table logiciel où le type est 'UNIX' (insensible à la casse).
2. Créer la vue Poste_0 avec la structure (nPos0, nomPoste0, nSalle0, TypePoste0, indIP, ad0) contenant tous les postes du rez-de-chaussée (étage=0).
   Commande :
   

   CREATE VIEW Poste_0 (nPos0, nomPoste0, nSalle0, TypePoste0, indIP, ad0) AS
   SELECT p.nPoste, p.nomPoste, p.nSalle, p.typePoste, p.indIP, p.ad
   FROM poste p, segment s
   WHERE p.indIP = s.indIP
   AND s.etage = 0;

   Cette vue joint les tables poste et segment pour ne garder que les postes situés au rez-de-chaussée.
3. Créer la vue SallePrix (nSalle, nomSalle, nbPoste, prixLocation) contenant les salles et leur prix de location calculé à 100 € par poste.
   Commande :
   

   CREATE VIEW SallePrix (nSalle, nomSalle, nbPoste, prixLocation) AS
   SELECT s.nSalle, s.nomSalle, s.nbPoste, s.nbPoste * 100
   FROM salle s;

   Le prix de location est calculé en multipliant le nombre de postes par 100 €.
4. Écrire une procédure PL/SQL qui affiche les salles dont le prix de location dépasse 150 €.
   Code :
   

   CREATE OR REPLACE PROCEDURE affiche IS
     CURSOR cur IS
       SELECT * FROM SallePrix
       WHERE prixLocation > 150;
   BEGIN
     DBMS_OUTPUT.PUT_LINE('Les salles dont le prix de location est supérieur à 150€');
     FOR c IN cur LOOP
       DBMS_OUTPUT.PUT_LINE(c.nSalle || ' ' || c.nomSalle || ' ' || c.nbPoste || ' ' || c.prixLocation);
     END LOOP;
   EXCEPTION
     WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(SQLERRM);
   END;

   Cette procédure utilise un curseur pour parcourir les salles avec un prix supérieur à 150 € et affiche leurs informations.
5. Écrire un bloc PL/SQL qui affiche les cinq salles les plus économiques à la location (ou toutes si moins de cinq).
   Code :
   

   SET SERVEROUTPUT ON;
   DECLARE
     CURSOR cur IS
       SELECT * FROM SallePrix
       ORDER BY prixLocation;
     i NUMBER := 1;
     c cur%ROWTYPE;
   BEGIN
     DBMS_OUTPUT.PUT_LINE('Les cinq salles les plus économiques à la location');
     OPEN cur;
     LOOP
       FETCH cur INTO c;
       EXIT WHEN cur%NOTFOUND OR i > 5;
       DBMS_OUTPUT.PUT_LINE(c.nSalle || ' ' || c.nomSalle || ' ' || c.prixLocation);
       i := i + 1;
     END LOOP;
   EXCEPTION
     WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(SQLERRM);
   END;

   Ce bloc affiche les cinq salles avec le prix de location le plus bas, ou moins si le nombre total est inférieur à cinq.

SQL et PL/SQL - Partie II

Cette partie porte sur des blocs PL/SQL plus complexes, incluant la saisie, le calcul de délais, et la gestion de déclencheurs.

1. Écrire un bloc PL/SQL qui saisit un numéro de salle et un type de poste, puis affiche le nombre de postes et d’installations de logiciels correspondantes.
   Code :
   

   SET SERVEROUTPUT ON;
   DECLARE
     ns salle.nSalle%TYPE := '&NumSalle';
     tp Poste.typePoste%TYPE := '&TypePoste';
     np NUMBER;
     ni NUMBER;
   BEGIN
     SELECT COUNT(p.nPoste)
     INTO np
     FROM poste p
     WHERE p.nSalle = ns
       AND UPPER(p.typePoste) = UPPER(tp);
   
     SELECT COUNT(i.numIns)
     INTO ni
     FROM installer i, poste p
     WHERE i.nPoste = p.nPoste
       AND UPPER(p.typePoste) = UPPER(tp)
       AND p.nSalle = ns;
   
     DBMS_OUTPUT.PUT_LINE('Numéro de Salle : ' || ns);
     DBMS_OUTPUT.PUT_LINE('Type de poste : ' || tp);
     DBMS_OUTPUT.PUT_LINE('G_NBPOSTE');
     DBMS_OUTPUT.PUT_LINE('----------');
     DBMS_OUTPUT.PUT_LINE(np);
     DBMS_OUTPUT.PUT_LINE('G_NBINSTALL');
     DBMS_OUTPUT.PUT_LINE('-----------');
     DBMS_OUTPUT.PUT_LINE(ni);
   EXCEPTION
     WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(SQLERRM);
   END;

   Ce bloc récupère et affiche le nombre de postes et d’installations de logiciels selon les critères saisis.
2. Écrire la procédure calculTemps qui calcule le délai (en jours) entre l’achat et l’installation de chaque logiciel, met à jour la table Installer, et affiche les incohérences.
   Code :
   

   CREATE OR REPLACE PROCEDURE calculTemp IS
     CURSOR cur IS
       SELECT l.nLog, dateAch, nomLog, p.nPoste, dateIns, nomPoste
       FROM logiciel l, poste p, installer i
       WHERE l.nLog = i.nLog
         AND p.nPoste = i.nPoste;
   BEGIN
     FOR c IN cur LOOP
       IF c.dateAch IS NULL THEN
         DBMS_OUTPUT.PUT_LINE('Date d''achat inconnue pour le logiciel ' || c.nomLog || ' sur le ' || c.nomPoste);
       ELSIF c.dateIns IS NULL THEN
         DBMS_OUTPUT.PUT_LINE('Pas de date d''installation pour le logiciel ' || c.nomLog || ' sur le ' || c.nomPoste);
       ELSIF c.dateAch > c.dateIns THEN
         DBMS_OUTPUT.PUT_LINE('Logiciel ' || c.nomLog || ' installé sur Poste ' || c.nomPoste || ' ' || (c.dateAch - c.dateIns) || ' jour(s) avant d''être acheté!');
       ELSE
         DBMS_OUTPUT.PUT_LINE('Logiciel ' || c.nomLog || ' sur Poste ' || c.nPoste || ', attente ' || (c.dateIns - c.dateAch) || ' jour(s).');
         UPDATE installer
         SET delai = c.dateIns - c.dateAch
         WHERE nLog = c.nLog
           AND nPoste = c.nPoste;
         COMMIT;
       END IF;
     END LOOP;
   EXCEPTION
     WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(SQLERRM);
   END;

   Cette procédure parcourt chaque installation, affiche les délais ou incohérences, et met à jour la colonne delai dans la table installer.
3. Écrire le déclencheur Trig_Après_DI_Installer sur la table Installer pour mettre à jour automatiquement les colonnes nbLog de Poste et nbInstall de Logiciel après insertion ou suppression.
   Code :
   

   CREATE OR REPLACE TRIGGER Trig_Après_DI_Installer
   AFTER INSERT OR DELETE ON installer
   FOR EACH ROW
   BEGIN
     IF INSERTING THEN
       UPDATE exam_Mai2015_poste SET nbLog = nbLog + 1
       WHERE nPoste = :NEW.nPoste;
   
       UPDATE logiciel SET nbPoste = nbPoste + 1
       WHERE nLog = :NEW.nLog;
   
       COMMIT;
     ELSIF DELETING THEN
       UPDATE poste SET nbLog = nbLog - 1
       WHERE nPoste = :OLD.nPoste;
   
       UPDATE logiciel SET nbPoste = nbPoste - 1
       WHERE nLog = :OLD.nLog;
   
       COMMIT;
     END IF;
   EXCEPTION
     WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(SQLERRM);
   END;

   Ce déclencheur gère l’incrémentation ou la décrémentation des compteurs lors d’insertions ou suppressions dans la table installer.

Méthode

Ce corrigé récompense la rigueur dans la compréhension des concepts transactionnels et des mécanismes internes de la base de données, ainsi que la maîtrise du SQL et PL/SQL pour la création de vues, procédures, blocs anonymes et déclencheurs.

• Il est essentiel de respecter les conventions syntaxiques et les noms exacts des objets et colonnes tels que définis dans l’énoncé.
• La présentation claire des étapes de raisonnement, notamment dans les blocs PL/SQL, est valorisée.
• Les erreurs fréquentes à éviter incluent la confusion entre visibilité des données dans les transactions, l’usage incorrect des commandes SQL dans les transactions, et l’omission des clauses COMMIT ou gestion des exceptions.
• Pour les procédures et déclencheurs, il faut bien gérer les cas d’exception et s’assurer que les mises à jour sont cohérentes et atomiques.
• Enfin, la compréhension des vues du dictionnaire et des modes d’arrêt est cruciale pour l’administration efficace de la base.