Examen TAD 2024 — 2025

1. Le sujet

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

2. Notions mobilisées

3. Correction — QCM (4 points)

Vue d'ensemble des 8 réponses, puis détail justifié sous chaque accordéon. Chaque énoncé est cité littéralement.

4. Correction — questions de réflexion (4 pts)

Réflexion 1 — Pourquoi des rôles plutôt que des droits directs ?

Réflexion · 1 Pourquoi est-il recommandé d'utiliser des rôles plutôt que d'attribuer directement des droits aux utilisateurs ?

Selon le CM1, un rôle est un ensemble nommé de privilèges. Trois avantages :

• Maintenance simplifiée : un nouvel utilisateur du profil X reçoit le rôle X en un seul GRANT. Sans rôles, on devrait répéter chaque privilège individuellement.
• Cohérence des permissions : tous les utilisateurs d'un même profil héritent exactement du même ensemble de droits.
• Réversibilité : REVOKE role FROM user retire d'un coup tout le bloc, sans toucher aux privilèges directs.

Réflexion 2 — Différence tablespace / table

Réflexion · 2 Quelle est la différence entre un tablespace et une table en base de données ?

Hiérarchie complète (CM2) : Base → Tablespace → Fichier → Segment → Extension → Bloc.

Réflexion 3 — Vue matérialisée et rafraîchissement

Réflexion · 3 Expliquez dans quel cas une vue matérialisée est plus performante qu'une vue simple et comment gérer son rafraîchissement de manière efficace dans un environnement transactionnel.

Cas où la VM gagne — La vue logique ré-exécute la requête à chaque appel. La VM stocke le résultat physiquement. Selon le CM1, c'est « un gain de performance sur les requêtes compliquées avec jointures » ou agrégations coûteuses : le résultat est déjà calculé.

Surtout, la VM devient nettement supérieure quand les données sont très demandées : si la même requête lourde (jointures, agrégats, sous-requêtes) est interrogée des milliers de fois pour quelques modifications sources, on amortit largement le coût du rafraîchissement. À l'inverse, sur des données peu lues mais souvent modifiées, la vue logique reste préférable.

Stratégies de rafraîchissement :

En environnement transactionnel intensif, la combinaison REFRESH FAST + ON COMMIT avec MATERIALIZED VIEW LOG sur les sources est le meilleur compromis.

Réflexion 4 — Stratégies de fragmentation BDDR

Réflexion · 4 Quelles sont les principales stratégies pour la fragmentation des données dans une base distribuée ?

Le CM4 distingue les stratégies suivantes :

• Horizontale (σ) — répartition des lignes. σ(R; Att = vAttᵢ) par site. Recomposition par UNION. Exemple : Client1 = σ Ville=Paris (Client).
• Verticale (π) — répartition des attributs. La clé doit être présente dans chaque fragment. Recomposition par JOINTURE sur la clé.
• Mixte — projection puis sélection : on découpe verticalement, puis chaque fragment vertical est découpé horizontalement.
• Réplication — la table (ou un fragment) est copiée à l'identique sur plusieurs sites. Améliore les performances en lecture et la disponibilité (tolérance aux pannes), au prix d'un coût de synchronisation lors des écritures. Souvent utilisée pour les tables de référence peu modifiées.

Réflexion 5 — Quand mettre en place un cluster ?

Réflexion · 5 Quand peut-on décider de la mise en place d'un cluster ?

On décide d'un cluster lorsqu'on a une relation Maître–Détails (associations (1,1)–(1,n) typiques : Facture↔Client, LigneCommande↔Commande) et qu'on fait souvent des requêtes de jointure naturelle entre les deux tables.

Le cluster colocalise sur disque les lignes maître et détails partageant la même clé : les jointures deviennent des lectures séquentielles au lieu d'allers-retours aléatoires. Selon le CM2 : le cluster remplace le tablespace et requiert un index obligatoire sur les colonnes communes (CREATE INDEX … ON CLUSTER nom_cluster;) sans lequel aucune opération n'est possible.

5. Problème — Partie A : PL/SQL

A.1 — Trigger gérant la clé de la location (1 pt)

A · 1 Écrire un trigger qui gère la clé de la location lors de sa création. On suppose qu'une séquence LocID existe.

Pattern auto-incrément (CM5) : BEFORE INSERT + assignation :new.<clé> via la séquence.

CREATE OR REPLACE TRIGGER trig_id_location
BEFORE INSERT ON Location
FOR EACH ROW
BEGIN
   :new.no_location := LocID.NEXTVAL;
END;

A.2 — Trigger vérifiant le permis du client (2 pts)

A · 2 Écrire un trigger qui vérifie qu'un client possède un permis de conduire valide avant de lui attribuer une location.

Garde-fou classique (CM5) : BEFORE INSERT + RAISE_APPLICATION_ERROR dans la plage [-20999, -20000]. Hypothèse : « permis valide » signifie Client.permis_conduire non nul (le sujet ne précise pas la sémantique).

CREATE OR REPLACE TRIGGER trig_check_permis
BEFORE INSERT ON Location
FOR EACH ROW
DECLARE
   v_permis Client.permis_conduire%TYPE;
BEGIN
   SELECT permis_conduire INTO v_permis
   FROM Client
   WHERE no_client = :new.no_client;

   IF v_permis IS NULL THEN
      RAISE_APPLICATION_ERROR(-20100,
         'Le client ne possede pas de permis de conduire valide');
   END IF;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20101, 'Client inconnu');
END;

A.3 — Procédure d'ajout d'un véhicule à une location (2 pts)

A · 3 Écrire une procédure qui permet d'ajouter un véhicule à une location existante. Cette procédure reçoit le numéro de location et le numéro du véhicule.

CREATE OR REPLACE PROCEDURE affecterVehicule(
   loc NUMBER, veh NUMBER)
IS
   loc_stat Location.statut%TYPE;
   v_dummy  Vehicule.no_vehicule%TYPE;
   cpt      NUMBER;
BEGIN
   -- 1) Le vehicule existe-t-il ? (NO_DATA_FOUND sinon)
   SELECT no_vehicule INTO v_dummy
   FROM Vehicule
   WHERE no_vehicule = veh;

   -- 2) La location existe-t-elle et est-elle en cours ?
   SELECT statut INTO loc_stat
   FROM Location
   WHERE no_location = loc;

   IF loc_stat != 'en cours' THEN
      RAISE_APPLICATION_ERROR(-20004, 'location terminee');
   END IF;

   -- 3) Le vehicule est-il libre ?
   SELECT COUNT(*) INTO cpt
   FROM Location l
   JOIN Vehicule_Location vl ON l.no_location = vl.no_location
   WHERE vl.no_vehicule = veh
     AND l.statut = 'en cours';

   IF cpt > 0 THEN
      RAISE_APPLICATION_ERROR(-20005, 'vehicule non libre');
   END IF;

   -- 4) Tout est integre, on attache le vehicule a la location
   INSERT INTO Vehicule_Location(no_location, no_vehicule)
   VALUES (loc, veh);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20003,
         'vehicule ou location non existant');
END;

Style « prof » : noms courts (loc, veh), pas de préfixe IN (implicite), un seul bloc EXCEPTION tout en bas qui attrape NO_DATA_FOUND pour les deux SELECT INTO qui peuvent échouer (véhicule ou location inexistants). Note de bas : les notes manuscrites du prof s'arrêtaient avant le INSERT final — on l'ajoute ici pour que la procédure ait du sens (sans cet INSERT, on a juste un valideur).

A.4 — Fonction affichant les n-ième et (n+1)-ième clients (2 pts)

A · 4 Écrire une fonction qui reçoit un entier n et affiche le n-ième et le (n+1)-ième client ayant loué le plus de véhicules dans l'année en cours.

CREATE OR REPLACE FUNCTION topClient(n INT)
RETURN VARCHAR2
IS
   result VARCHAR2(500) := '';
   cpt    NUMBER := 1;
   CURSOR listClient IS
      SELECT c.no_client, c.nom, c.prenom,
             COUNT(*) AS nbrloc
      FROM Client c
      JOIN Location l ON c.no_client = l.no_client
      GROUP BY c.no_client, c.nom, c.prenom
      ORDER BY nbrloc DESC
      FETCH FIRST n + 1 ROWS ONLY;
BEGIN
   FOR c IN listClient LOOP
      IF cpt = n OR cpt = n + 1 THEN
         result := result || c.nom || ' ' || c.prenom
                   || ' (' || c.nbrloc || ') ';
      END IF;
      cpt := cpt + 1;
   END LOOP;
   RETURN result;
END;

A.5 — Trigger sur Cumul_Depenses_Client (2 pts)

A · 5 Pourquoi doit-on associer un trigger à la gestion de la table Cumul_Depenses_Client ? Écrivez ce trigger.

Justification — La table Cumul_Depenses_Client(no_client, annee, montant_total) est un cumul agrégé. Elle doit rester cohérente avec les écritures dans Facture : à chaque nouvelle facture, le cumul correspondant doit être incrémenté. Sans trigger, on dépend des applicatifs pour faire ce UPDATE, et un oubli mène à une incohérence permanente — c'est exactement le cas d'usage d'un trigger de cohérence côté base (CM5).

CREATE OR REPLACE TRIGGER trig_maj_cumul_depenses
AFTER INSERT ON Facture
FOR EACH ROW
DECLARE
   v_no_client Location.no_client%TYPE;
   v_annee NUMBER;
   v_existe NUMBER;
BEGIN
   -- Recuperer le client a partir de la location liee
   SELECT no_client INTO v_no_client
   FROM Location
   WHERE no_location = :new.no_location;

   v_annee := TO_NUMBER(TO_CHAR(:new.date_facture, 'YYYY'));

   -- Existe-t-il deja une ligne pour ce client + annee ?
   SELECT COUNT(*) INTO v_existe
   FROM Cumul_Depenses_Client
   WHERE no_client = v_no_client AND annee = v_annee;

   IF v_existe = 0 THEN
      INSERT INTO Cumul_Depenses_Client(no_client, annee, montant_total)
      VALUES (v_no_client, v_annee, :new.montant);
   ELSE
      UPDATE Cumul_Depenses_Client
      SET montant_total = montant_total + :new.montant
      WHERE no_client = v_no_client AND annee = v_annee;
   END IF;
END;

AFTER INSERT car on agit après confirmation de la facture. FOR EACH ROW pour accéder à :new.

6. Problème — Partie B : BDDR

B.1 — Modèle réparti sur les agences (2 pts)

B · 1 Proposer un modèle de bases de données réparties sur différentes agences de location (exemples : Paris, Marseille, Lyon) en prenant en compte la répartition verticale et horizontale définies dans les règles de gestion. Justifier vos choix.

Allocation des tables (MLD par site) :

a) Côté agence — accéder aux clients (au siège)

Une agence a constamment besoin de noclient + permis pour enregistrer une location. La table Client varie peu, et on n'a pas besoin de tous les attributs (CB, adresse…) → projection verticale répliquée en vue matérialisée :

-- Sur chaque agence : VM repliquant la projection de Client
CREATE MATERIALIZED VIEW client
REFRESH ON DEMAND            -- ON COMMIT si beaucoup de modifs cote siege
AS SELECT noclient, permis
   FROM client@SiegeLink;

ON DEMAND suffit ici : les clients changent rarement, on rafraîchit manuellement. Si le siège enregistre énormément de nouveaux clients chaque heure, on passe à ON COMMIT pour la cohérence en temps réel.

b) Côté siège — recomposer les données des agences

Le siège a parfois besoin de voir l'historique global (factures, états comptables, audits). Comme cet accès est occasionnel, on utilise des vues simples qui font l'UNION à la demande sur les fragments distants. Une exception : Vehicule bouge peu et est consultée souvent → on la réplique en VM.

-- Sur le siege : recomposer Location depuis toutes les agences
CREATE VIEW location AS
   SELECT * FROM location@agence1link
   UNION
   SELECT * FROM location@agence2link;
   -- … UNION pour chaque agence

-- Idem pour Vehicule_Location et Cumul_Depenses_Client (vues simples)
CREATE VIEW vehicule_location AS
   SELECT * FROM vehicule_location@agence1link
   UNION SELECT * FROM vehicule_location@agence2link;

CREATE VIEW cumul_depenses_client AS
   SELECT * FROM cumul_depenses_client@agence1link
   UNION SELECT * FROM cumul_depenses_client@agence2link;

-- Exception : Vehicule varie peu mais est lue souvent -> VM
CREATE MATERIALIZED VIEW vehicule
REFRESH ON DEMAND
AS SELECT * FROM vehicule@agence1link
   UNION SELECT * FROM vehicule@agence2link;

Récap du choix view simple vs VM :

Justifications globales (CM4) :

• Fragmentation horizontale de Location, Vehicule, Vehicule_Location, Cumul_Depenses_Client par agence : 80 % du trafic est local à l'agence.
• Réplication par VM (projection (noclient, permis)) pour permettre à chaque agence de saisir des locations sans aller-retour permanent vers le siège.
• Vue simple côté siège pour les tables consultées rarement : pas de coût de stockage/sync, on accepte la latence des UNION distants.
• VM côté siège pour Vehicule uniquement : faible volume de modifs justifie la copie locale.

B.2 — Optimiser l'accès à la table Agence (1 pt)

B · 2 On suppose que dans la base de données de l'agence de Paris, il existe une table contenant la relation Agence. Que faut-il faire dans les bases de données des autres agences pour optimiser l'accès aux informations de cette table ?

-- Prealable : DB link vers la base de Paris
CREATE DATABASE LINK db_paris
CONNECT TO user_distant IDENTIFIED BY mdp
USING 'paris_service';

-- Sur Marseille et Lyon : VM repliquant Agence
CREATE MATERIALIZED VIEW Agence_local
REFRESH FAST
AS SELECT * FROM Agence@db_paris;

Bénéfices (CM4) :

• Disponibilité locale même en cas de réseau lent / indisponible
• Augmentation du parallélisme en lecture
• Diminution du coût des transmissions réseau

Compromis : cohérence seulement éventuelle (entre deux refresh), acceptable ici car les agences changent rarement.

★ Bilan rapide