Correction de l'activité 3.2 - Bases de données Avancées

Ce document présente la correction de l'activité 3.2 du cours de Bases de données Avancées, destinée aux étudiants de Mastère professionnel en logiciels libres. Il couvre la définition de types objets, la création de tables imbriquées, l'insertion de données complexes, ainsi que diverses requêtes SQL avancées et opérations de mise à jour sur ces structures.

Définition des types objets

La modélisation utilise des types objets pour représenter les données imbriquées. Voici les types créés :

CREATE TYPE Controle_t AS OBJECT(
     NumCtrl NUMBER(6),
     Type VARCHAR(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 VARCHAR(20),
     Prenom VARCHAR(20),
     Controles TAB_Controle)
/
CREATE TYPE TAB_Athlete AS TABLE OF Athlete_t
/
CREATE TYPE Equipe_t AS OBJECT(
     NumEq NUMBER(4),
     CodePays VARCHAR(3),
     Sport VARCHAR(15),
     Athletes TAB_Athlete)
/

Ces types permettent de représenter une équipe, composée d'athlètes, eux-mêmes associés à une collection de contrôles (tests antidopage par exemple).

Création de la table imbriquée

La table principale Equipe est créée à partir du type Equipe_t. Les collections imbriquées sont stockées dans des tables spécifiques :

CREATE TABLE Equipe OF Equipe_t (CONSTRAINT pk_equipe PRIMARY KEY(NumEq))
NESTED TABLE Athletes STORE AS table_imbriquee_athletes
     (NESTED TABLE Controles STORE AS table_imbriquee_controles);

Cette structure permet de gérer des données hiérarchiques avec des tables imbriquées pour les athlètes et leurs contrôles.

Insertion de données complexes

Les données sont insérées en utilisant les types objets et les collections imbriquées. Exemple d'insertion d'une équipe avec ses athlètes et leurs contrôles :

INSERT INTO Equipe VALUES(1, 'USA', '100 metres',
     TAB_Athlete(
          Athlete_t(1, 'Green', 'Maurice',
               TAB_Controle(Controle_t(1, 'Sanguin', '3/08/2019', 'N'))),
          Athlete_t(2, 'Lewis', 'Karl',
               TAB_Controle(Controle_t(2, 'Sanguin', '3/08/2019', 'N')))
     ));

Chaque équipe contient une table d'athlètes, chaque athlète une table de contrôles, illustrant la gestion de données imbriquées.

Requêtes SQL avancées sur les tables imbriquées

Extraction simple des athlètes

Pour obtenir le pays, le sport, le nom et le prénom de chaque athlète :

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

Cette requête déplie la table imbriquée Athletes pour chaque équipe.

Affichage avec curseur imbriqué

Pour afficher chaque équipe avec la liste de ses athlètes sous forme de curseur :

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

Cette requête affiche d'abord les informations de l'équipe, puis un curseur contenant les noms et prénoms des athlètes.

Tri des athlètes par pays et nom

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

Extraction des contrôles

Pour obtenir les numéros d'équipe, numéro de contrôle et date de contrôle :

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

Requête avec curseurs imbriqués pour contrôles

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

Cette requête affiche pour chaque équipe un curseur d'athlètes, eux-mêmes contenant un curseur de leurs contrôles.

Extraction ciblée d'athlètes et contrôles

Pour obtenir les athlètes d'une équipe donnée :

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

Pour obtenir les contrôles d'un athlète spécifique :

SELECT *
FROM TABLE (SELECT Controles
     FROM TABLE (SELECT Athletes FROM Equipe WHERE NumEq = 3)
     WHERE NumDossard = 4);

Comptages

Nombre d'athlètes dans une équipe :

SELECT COUNT(*)
FROM TABLE (SELECT Athletes FROM Equipe WHERE NumEq = 6);

Nombre d'athlètes par pays :

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

Nombre de contrôles par athlète :

SELECT a.Nom, a.Prenom, COUNT(*)
FROM Equipe e, TABLE(e.Athletes) a, TABLE(a.Controles)
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(*)
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;

Nombre de contrôles positifs par pays :

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

Date du dernier contrôle par athlète

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

Équipe avec le maximum de contrôles

SELECT e.NumEq, e.CodePays, e.Sport
FROM Equipe e, TABLE(e.Athletes) a, TABLE(a.Controles)
GROUP BY e.NumEq, e.CodePays, e.Sport
HAVING COUNT(*) =
     (SELECT MAX(COUNT(*))
     FROM Equipe e1, TABLE(e1.Athletes) a1, TABLE(a1.Controles)
     GROUP BY e1.NumEq);

Équipes sans contrôle positif

SELECT NumEq, CodePays, Sport
FROM Equipe e1
WHERE NOT EXISTS(
     SELECT *
     FROM Equipe e2, TABLE(e2.Athletes) a, TABLE(a.Controles) c
     WHERE e1.NumEq = e2.NumEq
     AND c.Resultat = 'P');

Mises à jour des tables imbriquées

Insertion d'un nouvel athlète dans une équipe

INSERT INTO TABLE (SELECT Athletes FROM Equipe WHERE NumEq = 6)
VALUES(30, 'Pietrus', 'Michael', TAB_Controle());

Ajout de contrôles à un athlète

INSERT INTO TABLE ( SELECT a.Controles FROM Equipe, TABLE(Athletes) a
                    WHERE NumEq = 6 AND NumDossard = 30)
VALUES(15, 'Urinaire', '23/08/2019', 'N');

INSERT INTO TABLE ( SELECT a.Controles FROM Equipe, TABLE(Athletes) a
                    WHERE NumEq = 6 AND NumDossard = 30)
VALUES(16, 'Sanguin', '23/08/2019', 'N');

Suppression des contrôles d'un athlète

DELETE FROM TABLE ( SELECT a.Controles FROM Equipe, TABLE(Athletes) a
                    WHERE NumEq = 3 AND NumDossard = 4);

Modification des résultats de contrôles

UPDATE TABLE ( SELECT a.Controles FROM Equipe, TABLE(Athletes) a
               WHERE NumEq = 4 AND NumDossard = 6)
SET Resultat = 'N'
WHERE DateCtrl < '04/10/2019';

Glossaire des termes clés

• Type objet : Structure définie par l'utilisateur pour représenter un ensemble de données liées, comme un athlète ou un contrôle.
• Table imbriquée (Nested Table) : Table stockée à l'intérieur d'une autre table, permettant de modéliser des collections d'objets.
• CURSOR : Type de données retournant un ensemble de résultats, utilisé ici pour afficher des collections imbriquées.
• INSERT INTO TABLE : Syntaxe SQL spécifique pour insérer des données dans une table imbriquée.
• DELETE FROM TABLE : Suppression de lignes dans une table imbriquée.
• UPDATE TABLE : Mise à jour des données dans une table imbriquée.
• GROUP BY : Clause SQL permettant de regrouper les résultats selon un ou plusieurs critères.
• HAVING : Clause SQL utilisée pour filtrer les groupes créés par GROUP BY.
• NOT EXISTS : Condition SQL vérifiant l'absence de résultats dans une sous-requête.

Points clés à retenir

• Les types objets permettent de modéliser des données complexes et imbriquées dans une base relationnelle.
• Les tables imbriquées facilitent la gestion de collections d'objets au sein d'une table principale.
• La syntaxe SQL pour manipuler ces tables imbriquées inclut des opérations spécifiques comme INSERT INTO TABLE, DELETE FROM TABLE et UPDATE TABLE.
• Les requêtes peuvent utiliser la fonction TABLE() pour déplier les collections imbriquées et accéder aux données internes.
• Les curseurs permettent d'afficher des résultats hiérarchiques, utiles pour représenter des listes imbriquées dans un résultat.
• Les opérations d'agrégation et de filtrage (GROUP BY, HAVING, NOT EXISTS) s'appliquent aussi aux données imbriquées.