Examen TAD 2022 — 2023

1. Le sujet

📥 Télécharger  ·  ↗ Ouvrir en plein écran

2. Notions mobilisées

3. Partie I — administration de la base (5 pts)

1.a — Créer deux utilisateurs (0.5 pt)

I · 1a Créer deux utilisateurs : Dirk Projet (Directeur de Projet, de schéma « dproje ») et Gérard Reen (Chef de Division, de schéma « green »). Ils ont comme mot de passe leur nom de schéma.

CREATE USER dproje IDENTIFIED BY dproje;
CREATE USER green  IDENTIFIED BY green;

-- Sans ces grants, les utilisateurs ne pourront meme pas se connecter
GRANT CREATE SESSION TO dproje, green;

1.b — Créer deux rôles (1 pt)

I · 1b Créer les rôles suivants :
— Rôle « ChefDivision » qui a tous les accès à toutes les tables.
— Rôle « ChefProjet » qui peut consulter toutes les tables et ajouter/supprimer des salariés à un projet.

-- Rolle ChefDivision : tous les droits sur les 5 tables
CREATE ROLE ChefDivision;
GRANT ALL ON organisation.DIVISION     TO ChefDivision;
GRANT ALL ON organisation.PROJET       TO ChefDivision;
GRANT ALL ON organisation.SERVICE      TO ChefDivision;
GRANT ALL ON organisation.SALARIE      TO ChefDivision;
GRANT ALL ON organisation.TRAVAILLESUR TO ChefDivision;

-- Rolle ChefProjet : SELECT partout + INSERT/DELETE sur TRAVAILLESUR
CREATE ROLE ChefProjet;
GRANT SELECT ON organisation.DIVISION     TO ChefProjet;
GRANT SELECT ON organisation.PROJET       TO ChefProjet;
GRANT SELECT ON organisation.SERVICE      TO ChefProjet;
GRANT SELECT ON organisation.SALARIE      TO ChefProjet;
GRANT SELECT, INSERT, DELETE ON organisation.TRAVAILLESUR TO ChefProjet;

1.c — Affecter les rôles (0.5 pt)

I · 1c Affecter les rôles aux utilisateurs.

GRANT ChefProjet   TO dproje;
GRANT ChefDivision TO green;

1.d — Questions de raisonnement (1 pt)

2 — Vue sal_Drag_MandMs (2 pts)

I · 2 Écrire la vue « sal_Drag_MandMs » permettant de ne montrer que les salariés travaillant sur le projet de nom « MandMs », appartenant à la division de nom « Dragées » et installé dans le service « 27 ».

CREATE OR REPLACE VIEW sal_Drag_MandMs AS
   SELECT s.numsal, s.nomsal, s.adresse, s.qualification
   FROM SALARIE s
   JOIN TRAVAILLESUR t  ON s.numsal   = t.numsal
   JOIN PROJET p        ON t.numproj  = p.numproj
   JOIN SERVICE serv    ON s.codeserv = serv.codeserv
   JOIN DIVISION d      ON p.codediv  = d.codediv
   WHERE p.nomproj  = 'MandMs'
     AND d.nomdiv   = 'Dragees'
     AND serv.codeserv = 27;

La division est référencée à la fois par PROJET et SERVICE — on choisit ici de la lier via PROJET. Si l'on voulait vérifier en plus que le service appartient à la même division que le projet, on rajouterait AND serv.codediv = p.codediv.

4. Partie II — PL/SQL (8 pts)

1 — Génération automatique de clés (1 pt)

II · 1 Rappeler comment les clés de certaines tables peuvent être générées automatiquement.

Selon le CM5, le pattern Oracle est en deux étapes :

1. Créer une séquence, générateur d'entiers monotones :

CREATE SEQUENCE seq_projet
   START WITH 1
   INCREMENT BY 1
   NOCACHE NOCYCLE;

2. Créer un trigger BEFORE INSERT qui pose la valeur de la séquence sur :new.<clé> :

CREATE OR REPLACE TRIGGER trig_id_projet
BEFORE INSERT ON PROJET
FOR EACH ROW
BEGIN
   :new.numproj := seq_projet.NEXTVAL;
END;

Depuis Oracle 12c, la clause GENERATED BY DEFAULT AS IDENTITY dans le CREATE TABLE remplit le même rôle — mais elle n'est pas couverte par nos CMs.

2 — Fonction NbProjets (2 pts)

II · 2 Écrire une fonction « NbProjets », avec le numéro de matricule du salarié en paramètre, qui renvoie le nombre de projets sur lequel travaille ce salarié.

Pattern fonction stockée avec agrégat (CM6) — la fonction respecte ici la sémantique stricte : elle retourne une valeur, n'a aucun effet de bord.

CREATE OR REPLACE FUNCTION NbProjets(p_matricule IN NUMBER)
RETURN NUMBER
IS
   v_nb NUMBER;
BEGIN
   SELECT COUNT(*) INTO v_nb
   FROM TRAVAILLESUR
   WHERE numsal = p_matricule;

   RETURN v_nb;
END;

Appel : SELECT NbProjets(123) FROM dual;

3 — Trigger : projet dans la division du salarié (2 pts)

II · 3 Écrire un trigger qui, lorsqu'on affecte un salarié à un projet, vérifie que le projet est bien affecté à la division à laquelle appartient le salarié et remonte une erreur sinon.

Garde-fou : il faut joindre SALARIE → SERVICE → DIVISION et PROJET → DIVISION, puis comparer.

CREATE OR REPLACE TRIGGER trig_coherence_division
BEFORE INSERT ON TRAVAILLESUR
FOR EACH ROW
DECLARE
   v_div_sal  NUMBER;
   v_div_proj NUMBER;
BEGIN
   -- Division du salarie (via son service)
   SELECT serv.codediv INTO v_div_sal
   FROM SALARIE sal
   JOIN SERVICE serv ON sal.codeserv = serv.codeserv
   WHERE sal.numsal = :new.numsal;

   -- Division du projet
   SELECT codediv INTO v_div_proj
   FROM PROJET
   WHERE numproj = :new.numproj;

   IF v_div_sal <> v_div_proj THEN
      RAISE_APPLICATION_ERROR(-20300,
         'Le salarie n''appartient pas a la division du projet');
   END IF;
END;

4 — Procédure infoDivision avec curseurs (3 pts)

II · 4 Écrire une procédure « infoDivision » qui prend en entrée un code division et liste les projets qui lui sont soumis et, pour chacun, les salariés qui travaillent dessus.

Pattern curseur imbriqué (CM6) : un curseur sur les projets, et dans la boucle, un autre curseur sur les salariés du projet courant.

CREATE OR REPLACE PROCEDURE infoDivision(p_codediv IN NUMBER)
IS
   CURSOR c_proj IS
      SELECT numproj, nomproj, budget
      FROM PROJET
      WHERE codediv = p_codediv;

   CURSOR c_sal(p_proj NUMBER) IS
      SELECT s.numsal, s.nomsal, s.qualification
      FROM SALARIE s
      JOIN TRAVAILLESUR t ON s.numsal = t.numsal
      WHERE t.numproj = p_proj;
BEGIN
   FOR p IN c_proj LOOP
      DBMS_OUTPUT.PUT_LINE('-- Projet ' || p.numproj
                       || ' : ' || p.nomproj
                       || ' (budget ' || p.budget || ')');

      FOR s IN c_sal(p.numproj) LOOP
         DBMS_OUTPUT.PUT_LINE('   . ' || s.numsal
                          || ' - ' || s.nomsal
                          || ' (' || s.qualification || ')');
      END LOOP;
   END LOOP;
END;

Le curseur paramétré c_sal(p_proj NUMBER) est exactement le pattern montré au CM6. Les boucles FOR … IN cur LOOP gèrent OPEN, FETCH et CLOSE automatiquement.

5. Partie III — bases de données réparties (7 pts)

1 — Modèle de bases réparties (3 pts)

III · 1 Proposer un modèle de bases de données réparties sur les 3 sites, et justifier vos choix.

Justifications globales (CM4) :

• Locality of access — les sessions, inscriptions, évaluations vivent au site qui les organise → fragmentation H par site = traffic réseau minimal.
• Source unique de vérité pour le catalogue — un seul site édite les Formations et Modules → centralisation maître/esclave.
• Site = référentiel quasi statique → réplication par vue matérialisée (voir question 3).

2 — Accès distant (1 pt)

III · 2 Que faut-il faire pour qu'un apprenant ou un formateur d'un site puisse accéder à la base de données sur un autre site ?

-- Sur le site Bordeaux, on cree un link vers Paris
CREATE DATABASE LINK db_paris
CONNECT TO user_distant IDENTIFIED BY mdp
USING 'paris_service';

-- Puis on requete les formations centralisees comme localement :
SELECT * FROM Formation@db_paris;

3 — Optimiser la consultation du catalogue (2 pts)

III · 3 Sur un site quelconque, les formateurs seront souvent amenés à afficher le catalogue de formation. Que préconisez-vous pour optimiser cette requête ?

-- Sur chaque site (Bordeaux, Lille), repliquer le catalogue
CREATE MATERIALIZED VIEW catalogue_local
REFRESH FAST ON DEMAND
AS
   SELECT f.idFormation, f.nom, f.descriptif,
          m.idModule, m.nom AS module, m.nbHeures
   FROM Formation@db_paris f
   JOIN FormationModule@db_paris fm ON f.idFormation = fm.idFormation
   JOIN Module@db_paris m   ON fm.idModule    = m.idModule;

Bénéfices (CM4) :

• Lecture locale ultra-rapide
• Disponibilité même si le réseau est indisponible
• Coût réseau minimal (seulement aux rafraîchissements)

4 — Pourquoi pas tout indexer (1 pt)

III · 4 Pourquoi n'indexe-t-on pas tous les champs d'une Base de données ?

D'après le CM2, un index a trois coûts qui interdisent de tout indexer :

Règle pratique du CM2 : indexer les colonnes utilisées dans les clauses WHERE, JOIN, ORDER BY — pas les autres.

★ Bilan rapide