Examen TAD 2023 — 2024

1. Le sujet

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

2. Notions mobilisées

3. Exercice 1 — bibliothèques universitaires (10 pts)

1.1 — Découpage de la BDDR & justifications

Ex1 · Q1 Proposez un découpage de cette base de données sur les différents sites. Justifiez vos choix de découpage.

Les hypothèses du sujet imposent une fragmentation horizontale (CM4) sur les tables qui ont un attribut site :

Justifications (CM4) :

• Locality of access — les bibliothèques accèdent majoritairement à leurs employés, ouvrages, auteurs, prêts → fragmentation H sur le site = données locales = trafic réseau minimal.
• Étudiants par université — règle explicite « chaque université gère ses propres étudiants ». Fragmentation H par université.
• nb_emprunts global — un étudiant peut emprunter dans n'importe quelle bibliothèque, mais nb_emprunts est l'agrégat global → nécessite un accès cross-site (DB link ou vue globale, voir question 3).
• AUTEURS suit OUVRAGES — fragmentation dérivée par semi-jointure sur Id_ouv.

1.2 — Requêtes de création des vues

Ex1 · Q2 Écrivez les requêtes de création des vues que vous utilisez.

Pré-requis : un database link par site distant (CM4) :

-- Sur le site Cergy
CREATE DATABASE LINK db_pau
CONNECT TO user_distant IDENTIFIED BY mdp
USING 'pau_service';

-- Sur le site Pau (symetrique)
CREATE DATABASE LINK db_cergy
CONNECT TO user_distant IDENTIFIED BY mdp
USING 'cergy_service';

Vues globales (sur chaque site, ou côté direction) :

-- Vue globale des etudiants : necessaire pour la verification de nb_emprunts
CREATE OR REPLACE VIEW V_ETUDIANT AS
   SELECT * FROM ETUDIANT
   UNION
   SELECT * FROM ETUDIANT@db_pau;

-- Vue globale des ouvrages : un etudiant peut emprunter dans toute biblio
CREATE OR REPLACE VIEW V_OUVRAGES AS
   SELECT * FROM OUVRAGES
   UNION
   SELECT * FROM OUVRAGES@db_pau;

-- Vue globale des prets (pour mise a jour de nb_emprunts)
CREATE OR REPLACE VIEW V_PRETS AS
   SELECT * FROM PRETS
   UNION
   SELECT * FROM PRETS@db_pau;

1.3 — Reconstruction pour la direction

Ex1 · Q3 Écrivez les requêtes qui permettent la reconstruction du résultat total des tables afin que toutes les informations soient disponibles pour la direction.

La direction veut le résultat « total » de toutes les tables. C'est l'application du principe de transparence de fragmentation (CM4) : on recompose par UNION pour les fragments horizontaux.

SELECT * FROM EMPLOYE   UNION   SELECT * FROM EMPLOYE@db_pau;
SELECT * FROM ETUDIANT  UNION   SELECT * FROM ETUDIANT@db_pau;
SELECT * FROM OUVRAGES  UNION   SELECT * FROM OUVRAGES@db_pau;
SELECT * FROM AUTEURS   UNION   SELECT * FROM AUTEURS@db_pau;
SELECT * FROM PRETS     UNION   SELECT * FROM PRETS@db_pau;

On utilise UNION et non UNION ALL par sécurité, bien qu'en théorie deux fragments H disjoints n'aient pas de doublons.

4. Exercice 2 — permis & véhicules (5 pts)

2.1 — Fonction de saisie d'un conducteur

Ex2 · Q1 Écrivez une fonction qui prend en entrée les données d'un conducteur (personnels + permis), vérifie la conformité des informations et insère ces derniers dans les tables correspondantes.

CREATE OR REPLACE FUNCTION ajouter_conducteur(
   p_no_cond IN NUMBER,
   p_civilite IN VARCHAR2,
   p_nom IN VARCHAR2,
   p_prenom IN VARCHAR2,
   p_no_permis IN NUMBER,
   p_type_permis IN VARCHAR2)
RETURN NUMBER
IS
   v_existe NUMBER;
BEGIN
   -- Conformite : civilite renseignee, nom non vide, type de permis valide
   IF p_civilite IS NULL OR p_nom IS NULL
      OR p_type_permis NOT IN ('A', 'B', 'C', 'D') THEN
      RETURN 0;
   END IF;

   -- Le permis existe-t-il deja avec ce type ? Sinon on le cree.
   SELECT COUNT(*) INTO v_existe
   FROM Permis WHERE no = p_no_permis;

   IF v_existe = 0 THEN
      INSERT INTO Permis(no, type) VALUES (p_no_permis, p_type_permis);
   END IF;

   -- Insertion du conducteur
   INSERT INTO Conducteur(no, civilite, nom, prenom)
   VALUES (p_no_cond, p_civilite, p_nom, p_prenom);

   -- Lien titulaire
   INSERT INTO titulaire(no_conducteur, no_permis)
   VALUES (p_no_cond, p_no_permis);

   RETURN 1;
EXCEPTION
   WHEN OTHERS THEN
      RETURN 0;
END;

2.2 — Trigger : véhicule libre lors d'une location

Ex2 · Q2 Écrivez le trigger qui vérifie qu'un véhicule est libre au moment de sa location.

Pattern garde-fou (CM5). Un véhicule est libre au moment :new.début si aucune location existante ne chevauche cet intervalle.

CREATE OR REPLACE TRIGGER trig_vehicule_libre
BEFORE INSERT ON louer
FOR EACH ROW
DECLARE
   v_conflit NUMBER;
BEGIN
   SELECT COUNT(*) INTO v_conflit
   FROM louer
   WHERE no_vehicule = :new.no_vehicule
     AND :new.debut <= fin
     AND :new.fin   >= debut;

   IF v_conflit > 0 THEN
      RAISE_APPLICATION_ERROR(-20200,
         'Vehicule deja loue sur cette periode');
   END IF;
END;

Test de chevauchement classique : deux intervalles [a,b] et [c,d] se chevauchent ssi a ≤ d ET c ≤ b.

2.3 — Trigger : conducteur a le permis requis

Ex2 · Q3 Écrivez le trigger qui permet, à l'insertion et à la modification d'une Location, de vérifier que le conducteur spécifié correspond à un conducteur ayant parmi ses permis celui requis pour conduire le véhicule.

Double vérification : (1) lister les types de permis requis par nécessiter pour ce véhicule ; (2) s'assurer que tous ces types sont détenus par le conducteur via titulaire. Si un seul manque → erreur.

CREATE OR REPLACE TRIGGER trig_permis_requis
BEFORE INSERT OR UPDATE ON louer
FOR EACH ROW
DECLARE
   v_manquant NUMBER;
BEGIN
   -- Combien de types de permis exiges par ce vehicule
   -- ne sont PAS detenus par le conducteur ?
   SELECT COUNT(*) INTO v_manquant
   FROM necessiter n
   JOIN Permis p_req ON n.no_permis = p_req.no
   WHERE n.no_vehicule = :new.no_vehicule
     AND NOT EXISTS (
        SELECT 1
        FROM titulaire t
        JOIN Permis p_det ON t.no_permis = p_det.no
        WHERE t.no_conducteur = :new.no_conducteur
          AND p_det.type = p_req.type
     );

   IF v_manquant > 0 THEN
      RAISE_APPLICATION_ERROR(-20201,
         'Le conducteur ne possede pas tous les permis requis');
   END IF;
END;

Le BEFORE INSERT OR UPDATE couvre les deux événements demandés par le sujet (CM5).

5. Exercice 3 — tours du monde récursifs (5 pts)

Ex3 · Q1 Écrivez une procédure PL/SQL capable de faire des propositions de tours du monde, prenant en entrée la ville de départ (qui est aussi la destination finale) et deux bornes (supérieure et inférieure) pour le nombre d'escales. Il n'y a pas de liste prédéfinie d'escales et on ne s'intéresse pas à la durée des escales. La procédure doit afficher les vols pour chaque tour du monde proposé. Cherchez à utiliser une procédure ou fonction récursive.

Approche : on construit le tour pas à pas. À chaque étape, on choisit un vol partant de la ville courante. Quand on atteint la ville d'origine après au moins bornes_inf escales, on a un tour candidat ; on s'arrête à bornes_sup escales.

CREATE OR REPLACE PACKAGE pkg_tours AS
   PROCEDURE propose_tour(
      p_origine IN VARCHAR2,
      p_borne_inf IN NUMBER,
      p_borne_sup IN NUMBER);
END;

CREATE OR REPLACE PACKAGE BODY pkg_tours AS

   -- Procedure recursive interne : prolonge un chemin partiel
   PROCEDURE etendre(
      p_origine IN VARCHAR2,
      p_ville_courante IN VARCHAR2,
      p_chemin IN VARCHAR2,
      p_escales IN NUMBER,
      p_borne_inf IN NUMBER,
      p_borne_sup IN NUMBER)
   IS
   BEGIN
      -- Cas terminal : tour ferme et nb d'escales OK
      IF p_ville_courante = p_origine
         AND p_escales >= p_borne_inf
         AND p_escales <= p_borne_sup THEN
         DBMS_OUTPUT.PUT_LINE('Tour : ' || p_chemin);
         RETURN;
      END IF;

      -- Arret si trop d'escales
      IF p_escales >= p_borne_sup THEN RETURN; END IF;

      -- Explorer chaque vol partant de la ville courante
      FOR v IN (SELECT numvol, ville_arrivee
                FROM VOL
                WHERE ville_depart = p_ville_courante) LOOP
         etendre(p_origine, v.ville_arrivee,
                 p_chemin || ' -> vol ' || v.numvol
                          || ' (' || v.ville_arrivee || ')',
                 p_escales + 1,
                 p_borne_inf, p_borne_sup);
      END LOOP;
   END;

   PROCEDURE propose_tour(
      p_origine IN VARCHAR2,
      p_borne_inf IN NUMBER,
      p_borne_sup IN NUMBER)
   IS
   BEGIN
      etendre(p_origine, p_origine, p_origine, 0,
              p_borne_inf, p_borne_sup);
   END;

END;

Utilisation :

SET SERVEROUTPUT ON;
BEGIN
   pkg_tours.propose_tour('Paris', 3, 5);
END;

★ Bilan rapide