Corrigé exercice 4 Bases de données avancées Télécharger pdf

Corrigé exercice 4 Bases de données avancées Télécharger pdf

Télécharger PDF

Fragmentation Verticale de la Table Étudiant

Pour séparer la gestion des emprunts des étudiants (attribut nb_emprunts) de la gestion de scolarité (attributs nom, adresse, université, cursus), nous proposons une fragmentation verticale de la table ETUDIANT en deux fragments : Etudiant-Emprunts et Etudiant-Scolarité.

Expressions Algébriques :

Fragment Étudiant-Emprunts (Gestion des emprunts) :

Etudiant-Emprunts = πid_etu, nb_emprunts(ETUDIANT)

Fragment Étudiant-Scolarité (Gestion de scolarité) :

Etudiant-Scolarité = πid_etu, nom, adresse, université, cursus(ETUDIANT)

Allocation du Fragment Etudiant-Emprunts

Afin de permettre à chaque bibliothèque de mettre à jour le nombre d'emprunts (nb_emprunts) localement, le fragment Etudiant-Emprunts est dupliqué sur les trois sites (USTHB, ESI, Alger 01).

Pour assurer la cohérence de la base de données et maintenir un nb_emprunts global toujours à jour, chaque mise à jour effectuée sur une réplique doit être propagée et répercutée sur toutes les autres répliques du fragment.

Schéma d'Allocation :

• Site USTHB : Fragment Etudiant-Emprunts
• Site ESI : Fragment Etudiant-Emprunts
• Site Alger 01 : Fragment Etudiant-Emprunts

Un réseau de communication assure la synchronisation entre ces sites.

Fragmentation Horizontale du Fragment Etudiant-Scolarité

Le fragment Etudiant-Scolarité est fragmenté horizontalement en fonction de la valeur de l'attribut université, afin de localiser les données des étudiants selon leur université d'origine.

Expressions Algébriques :

Fragment ETUDIANT-USTHB :

ETUDIANT-USTHB = σuniversité = "USTHB"(Etudiant-Scolarité)

Fragment ETUDIANT-ESI :

ETUDIANT-ESI = σuniversité = "ESI"(Etudiant-Scolarité)

Fragment ETUDIANT-ALGER01 :

ETUDIANT-ALGER01 = σuniversité = "Alger 01"(Etudiant-Scolarité)

Schéma d'Allocation :

Chaque fragment ETUDIANT-i est alloué sur le site i correspondant :

• Site USTHB : Fragment Etudiant-USTHB
• Site ESI : Fragment Etudiant-ESI
• Site Alger 01 : Fragment Etudiant-ALGER01

Un réseau de communication assure les échanges entre ces sites.

Fragmentation Horizontale des Tables EMPLOYE et OUVRAGES

La fragmentation horizontale primaire est appliquée aux tables EMPLOYE et OUVRAGES pour distribuer les données selon des critères spécifiques à chaque entité.

Fragmentation de la Table EMPLOYE

La fragmentation est basée sur la valeur de l'attribut affectation.

Expressions Algébriques :

EMPLOYE-USTHB = σaffectation = "USTHB"(EMPLOYE)

EMPLOYE-ESI = σaffectation = "ESI"(EMPLOYE)

EMPLOYE-ALGER01 = σaffectation = "Alger 01"(EMPLOYE)

Code SQL (Exemple de partitionnement par liste) :

CREATE TABLE EMPLOYE (
  Id_pers NUMBER PRIMARY KEY,
  nom VARCHAR2(20),
  adresse VARCHAR2(50),
  statut VARCHAR2(20),
  affectation VARCHAR2(20)
)
PARTITION BY LIST (affectation) (
  PARTITION EMPLOYE-USTHB VALUES ('USTHB'),
  PARTITION EMPLOYE-ESI VALUES ('ESI'),
  PARTITION EMPLOYE-ALGER01 VALUES ('Alger 01')
);

Fragmentation de la Table OUVRAGES

La fragmentation est basée sur la valeur de l'attribut site.

Expressions Algébriques :

OUVRAGES-USTHB = σsite = "USTHB"(OUVRAGES)

OUVRAGES-ESI = σsite = "ESI"(OUVRAGES)

OUVRAGES-ALGER01 = σsite = "Alger 01"(OUVRAGES)

Code SQL (Exemple de partitionnement par liste) :

CREATE TABLE OUVRAGES (
  Id_ouv NUMBER PRIMARY KEY,
  titre VARCHAR2(20),
  éditeur VARCHAR2(20),
  année NUMBER,
  domaine VARCHAR2(20),
  stock NUMBER,
  site VARCHAR2(20)
)
PARTITION BY LIST (site) (
  PARTITION OUVRAGES-USTHB VALUES ('USTHB'),
  PARTITION OUVRAGES-ESI VALUES ('ESI'),
  PARTITION OUVRAGES-ALGER01 VALUES ('Alger 01')
);

Fragmentation Horizontale de la Table PRETS

La table PRETS peut être fragmentée horizontalement de manière dérivée (FHD), en se basant sur la fragmentation d'une autre table liée. Deux approches sont possibles :

1. Fragmentation Dérivée des OUVRAGES

Cette approche regroupe les prêts en fonction du site de l'ouvrage emprunté. Un prêt est stocké sur le même site que l'ouvrage associé.

Expressions Algébriques :

• PRETS-USTHB = PRETS ⋉ OUVRAGES-USTHB
• PRETS-ESI = PRETS ⋉ OUVRAGES-ESI
• PRETS-ALGER01 = PRETS ⋉ OUVRAGES-ALGER01

Code SQL (Exemple de partitionnement par référence) :

CREATE TABLE PRETS (
  Id_ouv NUMBER,
  Id_etu NUMBER,
  date_emprunt DATE,
  date_retour DATE,
  CONSTRAINT PK_PRETS PRIMARY KEY (Id_ouv, Id_etu, date_emprunt),
  CONSTRAINT FK_OUVRAGES FOREIGN KEY (Id_ouv) REFERENCES OUVRAGES(Id_ouv),
  CONSTRAINT FK_ETUDIANT FOREIGN KEY (Id_etu) REFERENCES Etudiant-Scolarité(Id_etu)
)
PARTITION BY REFERENCE (FK_OUVRAGES);

2. Fragmentation Dérivée des ETUDIANT-Scolarité

Cette approche regroupe les prêts en fonction du site de l'étudiant emprunteur. Un prêt est stocké sur le même site que l'étudiant qui a effectué le prêt.

Expressions Algébriques :

• PRETS-USTHB = PRETS ⋉ ETUDIANT-USTHB
• PRETS-ESI = PRETS ⋉ ETUDIANT-ESI
• PRETS-ALGER01 = PRETS ⋉ ETUDIANT-ALGER01

Code SQL (Exemple de partitionnement par référence) :

CREATE TABLE PRETS (
  Id_ouv NUMBER,
  Id_etu NUMBER,
  date_emprunt DATE,
  date_retour DATE,
  CONSTRAINT PK_PRETS PRIMARY KEY (Id_ouv, Id_etu, date_emprunt),
  CONSTRAINT FK_OUVRAGES FOREIGN KEY (Id_ouv) REFERENCES OUVRAGES(Id_ouv),
  CONSTRAINT FK_ETUDIANT FOREIGN KEY (Id_etu) REFERENCES Etudiant-Scolarité(Id_etu)
)
PARTITION BY REFERENCE (FK_ETUDIANT);

Quelle est la plus intéressante ?

La fragmentation dérivée basée sur les OUVRAGES est généralement plus intéressante pour une bibliothèque. Elle permet de gérer localement les ouvrages et leurs prêts, indépendamment de l'origine de l'étudiant, ce qui correspond mieux aux opérations quotidiennes d'une bibliothèque.

Schéma d'Allocation Final des Fragments

Le schéma d'allocation final répartit les différents fragments de tables sur les trois sites, en tenant compte des stratégies de fragmentation verticale et horizontale définies précédemment.

Allocation des Fragments par Site :

• Site USTHB :
  • Etudiant-Emprunts (Dupliqué)
  • Etudiant-USTHB
  • EMPLOYE-USTHB
  • OUVRAGES-USTHB
  • PRETS-USTHB (dérivé d'OUVRAGES)
• Site ESI :
  • Etudiant-Emprunts (Dupliqué)
  • Etudiant-ESI
  • EMPLOYE-ESI
  • OUVRAGES-ESI
  • PRETS-ESI (dérivé d'OUVRAGES)
• Site Alger 01 :
  • Etudiant-Emprunts (Dupliqué)
  • Etudiant-ALGER01
  • EMPLOYE-ALGER01
  • OUVRAGES-ALGER01
  • PRETS-ALGER01 (dérivé d'OUVRAGES)

Un réseau de communication assure la coordination et la réplication des données entre tous les sites.

Exécution de Requête Distribuée : Nombre Total d'Ouvrages "Informatique"

Un utilisateur situé à l'université d'Alger 01 souhaite obtenir le nombre total de livres du domaine « Informatique » disponibles dans les universités USTHB et ESI.

a. Requête SQL :

SELECT COUNT(id_ouv)
FROM OUVRAGES
WHERE domaine = 'Informatique' AND (site = 'USTHB' OR site = 'ESI');

b. Arbre Canonique (Plan Logique Initial) :

Représentation de l'opération de comptage sur la table globale avant fragmentation :

COUNT(id_ouv)
  └─ σ(domaine = "Informatique" AND (site = "USTHB" OR site = "ESI")) (OUVRAGES)

c. Arbre après Simplification et Optimisation Distribuée :

Après la décomposition de la requête sur les fragments et l'application des règles d'optimisation (notamment le "pushdown" des sélections), l'arbre logique devient :

COUNT(id_ouv)
  └─ ∪
      ├─ σ(domaine = "Informatique") (OUVRAGES-USTHB)
      └─ σ(domaine = "Informatique") (OUVRAGES-ESI)

Note : Le fragment OUVRAGES-ALGER01 est éliminé car il ne contient pas de données pertinentes pour la condition site = "USTHB" OR site = "ESI".

d. Stratégies d'Exécution :

Plusieurs stratégies peuvent être envisagées pour exécuter cette requête distribuée :

• Stratégie 01 : Envoyer les fragments OUVRAGES-USTHB et OUVRAGES-ESI entiers au site Alger 01, puis exécuter localement la sélection et le comptage.
• Stratégie 02 : Effectuer la sélection (domaine = 'Informatique') localement sur OUVRAGES-USTHB au site USTHB et sur OUVRAGES-ESI au site ESI. Envoyer les résultats filtrés à Alger 01, y effectuer l'union et le comptage.
• Stratégie 03 : Effectuer la sélection sur OUVRAGES-USTHB au site USTHB. Envoyer ce résultat au site ESI. Au site ESI, effectuer la sélection sur OUVRAGES-ESI, faire l'union avec les données reçues d'USTHB, puis le comptage. Enfin, envoyer le résultat final au site Alger 01.
• Stratégie 04 : Similaire à la Stratégie 03, mais en initiant le processus au site ESI et en envoyant à USTHB. Effectuer la sélection sur OUVRAGES-ESI au site ESI. Envoyer ce résultat au site USTHB. Au site USTHB, effectuer la sélection sur OUVRAGES-USTHB, faire l'union avec les données reçues d'ESI, puis le comptage. Enfin, envoyer le résultat final au site Alger 01.

Foire Aux Questions (FAQ)

Qu'est-ce que la fragmentation verticale et quel est son objectif ?

La fragmentation verticale consiste à diviser une table en plusieurs sous-tables (fragments) en sélectionnant un sous-ensemble de ses attributs. Chaque fragment contient l'identifiant primaire de la table originale ainsi qu'un ensemble d'attributs spécifiques. Son objectif principal est d'améliorer les performances en réduisant la quantité de données à lire lors de requêtes qui ne concernent qu'un sous-ensemble d'attributs, et de localiser les données fréquemment utilisées ensemble.

Pourquoi certains fragments sont-ils dupliqués sur plusieurs sites ?

La duplication d'un fragment, comme Etudiant-Emprunts dans cet exemple, est une stratégie d'allocation visant à améliorer la disponibilité et les performances de lecture. Chaque site peut accéder localement aux données sans nécessiter d'accès réseau. Cependant, cela implique des mécanismes de synchronisation complexes pour maintenir la cohérence des données lors des mises à jour.

Quelle est la différence entre une fragmentation horizontale primaire et dérivée ?

Une fragmentation horizontale primaire divise une table en fragments basés sur une condition prédicat appliquée à ses propres attributs (ex: EMPLOYE fragmenté par affectation). Une fragmentation horizontale dérivée divise une table en fragments en se basant sur la fragmentation d'une autre table à laquelle elle est liée par une jointure (ex: PRETS fragmenté selon le site des OUVRAGES ou des ETUDIANTS). L'objectif de la fragmentation dérivée est de colocaliser les enregistrements liés pour optimiser les jointures distribuées.