Bases de données Avancées – Activité 3.2

Ce TP propose d’implanter sous Oracle une base de données orientée objet pour le suivi antidopage de sportifs lors de compétitions internationales multisports. L’objectif est de modéliser les données selon un diagramme de classes UML, de créer des types et tables imbriqués, puis d’effectuer des requêtes complexes et des mises à jour sur ces structures. Ce travail nécessite un accès à une base Oracle et des connaissances de base en SQL et PL/SQL.

Objectifs

• Créer des types Oracle correspondant à un modèle UML avec collections multiniveaux.
• Définir et manipuler des tables d’objets imbriqués.
• Peupler les tables avec des données structurées.
• Réaliser des requêtes de désemboîtement (pseudojointures, curseurs imbriqués).
• Effectuer des mises à jour sur des tables imbriquées.
• Analyser et interpréter les résultats obtenus.

Prérequis et installation

• Base Oracle installée et accessible.
• Connaissances de base en SQL, PL/SQL et modélisation objet.
• Accès à un outil client Oracle (SQL*Plus, SQL Developer, etc.).
• Familiarité avec les types objets Oracle et les collections.

Création des types Oracle

Il faut créer un ensemble de types correspondant aux classes du diagramme UML : Equipe, Athlete, Controle. Les associations 1–N seront traduites en collections multiniveaux. La convention de nommage impose le suffixe _t pour les types et le préfixe TAB_ pour les collections.

CREATE TYPE Controle_t AS OBJECT (
  NumCtrl NUMBER(6),
  Type VARCHAR2(15),
  DateCtrl DATE,
  Resultat CHAR(1)
);
/

CREATE TYPE TAB_Controle AS TABLE OF Controle_t;
/

CREATE TYPE Athlete_t AS OBJECT (
  NumDossard NUMBER(5),
  Nom VARCHAR2(20),
  Prenom VARCHAR2(20),
  Controles TAB_Controle
);
/

CREATE TYPE TAB_Athlete AS TABLE OF Athlete_t;
/

CREATE TYPE Equipe_t AS OBJECT (
  NumEq NUMBER(4),
  CodePays VARCHAR2(3),
  Sport VARCHAR2(15),
  Athletes TAB_Athlete
);
/

CREATE TYPE TAB_Equipe AS TABLE OF Equipe_t;
/

Cette étape permet de structurer les données en objets imbriqués, facilitant la modélisation des relations 1–N entre équipes, athlètes et contrôles.

Définition de la table Equipe

Créer une table d’objets de type Equipe_t pour stocker les équipes et leurs athlètes imbriqués.

CREATE TABLE Equipe OF Equipe_t
NESTED TABLE Athletes STORE AS Athletes_NT;

Pour afficher la structure de la table et de ses tables imbriquées :

DESC Equipe;
SELECT TABLE_NAME, COLUMN_NAME, NESTED_TABLE_NAME
FROM USER_NESTED_TABLES;

Cette étape confirme la bonne création des tables imbriquées et leur organisation physique.

Peuplement de la table Equipe

Insérer les données des équipes, athlètes et contrôles selon le tableau fourni. Chaque équipe contient une collection d’athlètes, eux-mêmes contenant une collection de contrôles.

INSERT INTO Equipe VALUES (
  Equipe_t(1, 'USA', '100 mètres',
    TAB_Athlete(
      Athlete_t(1, 'Green', 'Maurice', TAB_Controle(
        Controle_t(1, 'Sanguin', TO_DATE('03/08/2019','DD/MM/YYYY'), 'N'),
        Controle_t(2, 'Sanguin', TO_DATE('03/08/2019','DD/MM/YYYY'), 'N')
      )),
      Athlete_t(2, 'Lewis', 'Carl', TAB_Controle(
        Controle_t(3, 'Urinaire', TO_DATE('03/08/2019','DD/MM/YYYY'), 'N'),
        Controle_t(4, 'Urinaire', TO_DATE('03/08/2019','DD/MM/YYYY'), 'N')
      ))
    )
  )
);

-- Répéter pour les autres équipes (UK, FRA) et leurs athlètes avec contrôles

Après insertion, afficher le contenu :

SELECT * FROM Equipe;

Le résultat doit montrer les équipes avec leurs athlètes et contrôles imbriqués.

Requêtes de désemboîtement

Ces requêtes permettent d’extraire les données imbriquées sous forme tabulaire classique.

Liste des équipes avec athlètes (pseudojointure)

SELECT e.CodePays, e.Sport, a.Nom, a.Prenom
FROM Equipe e, TABLE(e.Athletes) a;

Cette requête affiche chaque équipe avec ses athlètes.

Liste des équipes avec athlètes (curseur imbriqué)

DECLARE
  CURSOR c_equipe IS SELECT * FROM Equipe;
  CURSOR c_athlete (athletes TAB_Athlete) IS SELECT * FROM TABLE(athletes);
BEGIN
  FOR e IN c_equipe LOOP
    DBMS_OUTPUT.PUT_LINE('Equipe: ' || e.CodePays || ', ' || e.Sport);
    FOR a IN c_athlete(e.Athletes) LOOP
      DBMS_OUTPUT.PUT_LINE('  Athlete: ' || a.Nom || ' ' || a.Prenom);
    END LOOP;
  END LOOP;
END;
/

La différence avec la pseudojointure est que cette méthode traite les collections en PL/SQL, permettant plus de contrôle sur le parcours.

Autres requêtes importantes

• Nom et prénom des athlètes triés par pays et ordre alphabétique :

SELECT a.Nom, a.Prenom, e.CodePays
FROM Equipe e, TABLE(e.Athletes) a
ORDER BY e.CodePays, a.Nom, a.Prenom;

• Numéro et date des contrôles par équipe (pseudojointure) :

SELECT e.NumEq, c.NumCtrl, c.DateCtrl
FROM Equipe e,
     TABLE(e.Athletes) a,
     TABLE(a.Controles) c;

• Nom et prénom des athlètes de l’équipe n° 2 :

SELECT a.Nom, a.Prenom
FROM Equipe e, TABLE(e.Athletes) a
WHERE e.NumEq = 2;

• Liste des contrôles de l’athlète n° 4 de l’équipe n° 3 :

SELECT c.NumCtrl, c.Type, c.DateCtrl, c.Resultat
FROM Equipe e,
     TABLE(e.Athletes) a,
     TABLE(a.Controles) c
WHERE e.NumEq = 3 AND a.NumDossard = 4;

• Nombre d’athlètes dans l’équipe n° 6 :

SELECT COUNT(*)
FROM Equipe e, TABLE(e.Athletes) a
WHERE e.NumEq = 6;

• Nombre total d’athlètes par pays :

SELECT e.CodePays, COUNT(a.NumDossard)
FROM Equipe e, TABLE(e.Athletes) a
GROUP BY e.CodePays;

• Nombre de contrôles par athlète :

SELECT a.Nom, a.Prenom, COUNT(c.NumCtrl)
FROM Equipe e,
     TABLE(e.Athletes) a,
     TABLE(a.Controles) c
GROUP BY a.Nom, a.Prenom
ORDER BY a.Nom, a.Prenom;

• Nombre de contrôles positifs par athlète :

SELECT a.Nom, a.Prenom, COUNT(c.NumCtrl)
FROM Equipe e,
     TABLE(e.Athletes) a,
     TABLE(a.Controles) c
WHERE c.Resultat = 'P'
GROUP BY a.Nom, a.Prenom
ORDER BY a.Nom, a.Prenom;

• Date du dernier contrôle pour chaque athlète :

SELECT a.Nom, a.Prenom, MAX(c.DateCtrl) AS DernierControle
FROM Equipe e,
     TABLE(e.Athletes) a,
     TABLE(a.Controles) c
GROUP BY a.Nom, a.Prenom
ORDER BY a.Nom, a.Prenom;

Mises à jour des tables imbriquées

Ces opérations modifient les collections imbriquées dans la table Equipe.

Insertion d’un nouvel athlète dans l’équipe n° 6

DECLARE
  v_athlete Athlete_t := Athlete_t(30, 'Pietrus', 'Michael', TAB_Controle());
BEGIN
  UPDATE Equipe
  SET Athletes = Athletes MULTISET UNION TAB_Athlete(v_athlete)
  WHERE NumEq = 6;
END;
/

Insertion de contrôles pour l’athlète n° 30 de l’équipe n° 6

DECLARE
  v_controles TAB_Controle := TAB_Controle(
    Controle_t(15, 'Urinaire', TO_DATE('23/08/2019','DD/MM/YYYY'), 'N'),
    Controle_t(16, 'Sanguin', TO_DATE('23/08/2019','DD/MM/YYYY'), 'N')
  );
BEGIN
  UPDATE Equipe e
  SET Athletes = CAST(
    MULTISET(
      SELECT CASE WHEN a.NumDossard = 30 THEN
        Athlete_t(a.NumDossard, a.Nom, a.Prenom, a.Controles MULTISET UNION v_controles)
      ELSE a END
      FROM TABLE(e.Athletes) a
    ) AS TAB_Athlete
  )
  WHERE e.NumEq = 6;
END;
/

Suppression de tous les contrôles de l’athlète n° 4 de l’équipe n° 3

DECLARE
  v_new_athletes TAB_Athlete;
BEGIN
  SELECT CAST(
    MULTISET(
      SELECT CASE WHEN a.NumDossard = 4 THEN
        Athlete_t(a.NumDossard, a.Nom, a.Prenom, TAB_Controle())
      ELSE a END
      FROM TABLE(e.Athletes) a
    ) AS TAB_Athlete
  )
  INTO v_new_athletes
  FROM Equipe e
  WHERE e.NumEq = 3;

  UPDATE Equipe
  SET Athletes = v_new_athletes
  WHERE NumEq = 3;
END;
/

Modification du résultat des contrôles de l’athlète n° 6 de l’équipe n° 4 passés avant le 04/10/2019

DECLARE
  v_new_athletes TAB_Athlete;
BEGIN
  SELECT CAST(
    MULTISET(
      SELECT CASE WHEN a.NumDossard = 6 THEN
        Athlete_t(
          a.NumDossard,
          a.Nom,
          a.Prenom,
          CAST(
            MULTISET(
              SELECT CASE WHEN c.DateCtrl < TO_DATE('04/10/2019','DD/MM/YYYY') THEN
                Controle_t(c.NumCtrl, c.Type, c.DateCtrl, 'N')
              ELSE c END
              FROM TABLE(a.Controles) c
            ) AS TAB_Controle
          )
        )
      ELSE a END
      FROM TABLE(e.Athletes) a
    ) AS TAB_Athlete
  )
  INTO v_new_athletes
  FROM Equipe e
  WHERE e.NumEq = 4;

  UPDATE Equipe
  SET Athletes = v_new_athletes
  WHERE NumEq = 4;
END;
/

Résultats attendus

• Les types et tables imbriqués sont créés sans erreur, avec la structure conforme au modèle UML.
• Les données insérées sont visibles dans la table Equipe avec leurs collections imbriquées.
• Les requêtes de désemboîtement affichent correctement les informations des équipes, athlètes et contrôles.
• Les mises à jour modifient les collections imbriquées sans perte de données non ciblées.
• Les résultats des requêtes de synthèse (nombre d’athlètes, contrôles positifs, dates) correspondent aux données insérées.

Pièges courants

• Ne pas respecter la convention de nommage des types et collections peut entraîner des erreurs de compilation.
• Oublier de stocker les tables imbriquées avec la clause NESTED TABLE lors de la création de la table Equipe.
• Confondre les collections imbriquées et les tables classiques dans les requêtes, ce qui provoque des erreurs de syntaxe.
• Lors des mises à jour, ne pas utiliser correctement les opérateurs MULTISET UNION ou CAST peut corrompre les données.
• Les dates doivent être manipulées avec TO_DATE et un format explicite pour éviter les erreurs de conversion.
• Les curseurs imbriqués en PL/SQL nécessitent une bonne gestion des boucles et des sorties pour afficher les résultats.