Examen masters architecture bases de donnees usthb 2012 2013

Télécharger PDF

Gestion d'une Base de Données pour un Club Équestre : Sécurité, Cohérence et Performance

Cet article explore la gestion d'une base de données pour un club équestre, en abordant des aspects cruciaux tels que la sécurité des données, la cohérence et l'optimisation des performances. Nous allons examiner un schéma de base de données simple et discuter des solutions aux problèmes courants de gestion.

Modèle de Base de Données du Club Équestre

La base de données d'un club équestre est composée des relations suivantes :

Propriétaire (Num_prop, nom, adr) : Contient les informations sur les propriétaires de chevaux.
Jockey (Num_jockey, nom, adresse) : Stocke les détails sur les jockeys.
Cheval (Id_cheval, Num_prop) : Enregistre les chevaux et leur propriétaire.
Victoire (Nom_course, Date, Id_cheval, Num_jockey) : Détaille les victoires obtenues par un couple jockey-cheval dans une course donnée.

Les attributs soulignés représentent les clés primaires de chaque relation.

Gestion des Utilisateurs et Droits d'Accès

Création d'un Utilisateur AGENT avec des Droits Spécifiques

Pour assurer la sécurité et le contrôle d'accès, il est essentiel de définir des rôles et des permissions précis pour chaque utilisateur de la base de données.

L'administrateur de la base de données souhaite créer un utilisateur nommé AGENT qui aura la permission de lire les informations de la table Propriétaire et de modifier (insérer, mettre à jour, supprimer) les tables Jockey, Cheval et Victoire.

Les requêtes SQL nécessaires pour configurer ces droits sont les suivantes :

CREATE USER AGENT IDENTIFIED BY 'motdepasse_agent';

GRANT SELECT ON Propriétaire TO AGENT;

GRANT INSERT, UPDATE, DELETE ON Jockey TO AGENT;

GRANT INSERT, UPDATE, DELETE ON Cheval TO AGENT;

GRANT INSERT, UPDATE, DELETE ON Victoire TO AGENT;

Ces commandes créent l'utilisateur AGENT et lui attribuent les privilèges spécifiés. Il est recommandé de remplacer 'motdepasse_agent' par un mot de passe sécurisé.

Optimisation du Suivi des Victoires pour Jockeys et Chevaux

Afin d'éviter de calculer le nombre de victoires à chaque interrogation, il est judicieux d'ajouter des attributs dédiés aux relations Jockey et Cheval.

Ajout d'Attributs pour le Nombre de Victoires

Pour connaître directement le nombre de victoires d'un jockey et d'un cheval sans avoir à les calculer via des requêtes complexes, nous pouvons ajouter un attribut Nb_victoires à chaque table concernée.

Requêtes SQL pour modifier la structure des tables :

ALTER TABLE Jockey ADD COLUMN Nb_victoires INT DEFAULT 0;

ALTER TABLE Cheval ADD COLUMN Nb_victoires INT DEFAULT 0;

Ces modifications ajoutent une colonne Nb_victoires de type entier à chaque table, avec une valeur par défaut de 0.

Mise à Jour Automatique des Compteurs de Victoires via des Triggers

Pour maintenir la cohérence de ces nouveaux attributs, il est crucial d'implémenter des déclencheurs (triggers) qui mettront à jour Nb_victoires automatiquement chaque fois qu'une victoire est ajoutée, modifiée ou supprimée.

Les actions à associer sont des triggers sur la table Victoire. Ces triggers doivent être exécutés après chaque insertion (AFTER INSERT), suppression (AFTER DELETE) ou mise à jour (AFTER UPDATE) d'une victoire.

Trigger pour l'insertion d'une victoire :

CREATE TRIGGER trg_after_insert_victoire
AFTER INSERT ON Victoire
FOR EACH ROW
BEGIN
UPDATE Jockey SET Nb_victoires = Nb_victoires + 1 WHERE Num_jockey = NEW.Num_jockey;
UPDATE Cheval SET Nb_victoires = Nb_victoires + 1 WHERE Id_cheval = NEW.Id_cheval;
END;

Trigger pour la suppression d'une victoire :

CREATE TRIGGER trg_after_delete_victoire
AFTER DELETE ON Victoire
FOR EACH ROW
BEGIN
UPDATE Jockey SET Nb_victoires = Nb_victoires - 1 WHERE Num_jockey = OLD.Num_jockey;
UPDATE Cheval SET Nb_victoires = Nb_victoires - 1 WHERE Id_cheval = OLD.Id_cheval;
END;

Trigger pour la mise à jour d'une victoire (si jockey ou cheval change) :

CREATE TRIGGER trg_after_update_victoire
AFTER UPDATE ON Victoire
FOR EACH ROW
BEGIN
IF OLD.Num_jockey <> NEW.Num_jockey THEN
UPDATE Jockey SET Nb_victoires = Nb_victoires - 1 WHERE Num_jockey = OLD.Num_jockey;
UPDATE Jockey SET Nb_victoires = Nb_victoires + 1 WHERE Num_jockey = NEW.Num_jockey;
END IF;
IF OLD.Id_cheval <> NEW.Id_cheval THEN
UPDATE Cheval SET Nb_victoires = Nb_victoires - 1 WHERE Id_cheval = OLD.Id_cheval;
UPDATE Cheval SET Nb_victoires = Nb_victoires + 1 WHERE Id_cheval = NEW.Id_cheval;
END IF;
END;

Ces triggers garantissent que les compteurs de victoires sont toujours à jour, mais ils introduisent un surcoût lors des opérations d'insertion, de suppression ou de mise à jour sur la table Victoire.

Gestion de la Cohérence des Données : Cas de Dopage

Les contrôles de dopage ajoutent une complexité à la gestion des victoires. En cas de contrôle positif pour un gagnant (jockey et cheval), la victoire enregistrée doit être annulée et réattribuée au gagnant suivant.

Annulation et Réattribution d'une Victoire

Pour gérer ce scénario de manière cohérente, il faut non seulement annuler la victoire du couple dopé, mais aussi identifier et attribuer la victoire au participant suivant. Cela implique une série d'opérations :

Diminuer le compteur de victoires pour le jockey et le cheval dopés (si des compteurs existent).
Mettre à jour l'entrée de la victoire pour refléter le nouveau gagnant (jockey et cheval).
Augmenter le compteur de victoires pour le nouveau jockey et le nouveau cheval.

Voici les requêtes SQL possibles, en supposant que l'on connaisse l'Id_cheval et Num_jockey du couple dopé, ainsi que l'Nom_course et la Date de la victoire concernée. Il faut également pouvoir identifier le "gagnant suivant" (ce qui n'est pas directement supporté par le schéma actuel, il faudrait ajouter un attribut de classement ou s'appuyer sur l'ordre d'arrivée ou d'autres données pour la course).

Exemple de scénario simplifié (sans gestion du "gagnant suivant" directement dans la BD actuelle, juste une mise à jour d'une victoire existante) :

Supposons que la victoire à annuler est pour le jockey X et le cheval Y, pour la course C à la date D. Et le nouveau gagnant est le jockey A avec le cheval B.

-- 1. Diminuer les compteurs des anciens gagnants (si les triggers ne sont pas utilisés ou si l'on gère manuellement)
UPDATE Jockey SET Nb_victoires = Nb_victoires - 1 WHERE Num_jockey = 'Num_jockey_dopé';
UPDATE Cheval SET Nb_victoires = Nb_victoires - 1 WHERE Id_cheval = 'Id_cheval_dopé';

-- 2. Mettre à jour l'entrée de la victoire avec les nouveaux gagnants
UPDATE Victoire
SET Num_jockey = 'Num_jockey_nouveau_gagnant',
Id_cheval = 'Id_cheval_nouveau_gagnant'
WHERE Nom_course = 'Nom_course_concernée' AND Date = 'Date_course_concernée'
AND Num_jockey = 'Num_jockey_dopé' AND Id_cheval = 'Id_cheval_dopé';

-- 3. Augmenter les compteurs des nouveaux gagnants (si les triggers ne sont pas utilisés ou si l'on gère manuellement)
UPDATE Jockey SET Nb_victoires = Nb_victoires + 1 WHERE Num_jockey = 'Num_jockey_nouveau_gagnant';
UPDATE Cheval SET Nb_victoires = Nb_victoires + 1 WHERE Id_cheval = 'Id_cheval_nouveau_gagnant';

Si les triggers pour UPDATE sont bien configurés comme ci-dessus, les étapes 1 et 3 seraient gérées automatiquement par la seule requête UPDATE sur la table Victoire. La complexité réside principalement dans l'identification du "gagnant suivant", qui nécessiterait des informations supplémentaires sur le classement des participants à la course.

Optimisation des Performances de la Base de Données

Création d'Index pour Améliorer les Requêtes

Les index sont essentiels pour accélérer la récupération des données. Les clés primaires sont généralement indexées automatiquement, mais d'autres attributs fréquemment utilisés dans les clauses WHERE ou JOIN peuvent bénéficier d'indexation manuelle.

Pour la relation Jockey, si des requêtes fréquentes portent sur le nom du jockey, un index sur l'attribut nom serait très bénéfique.

Requête SQL pour créer un index :

CREATE INDEX idx_jockey_nom ON Jockey (nom);

Cet index permettra des recherches plus rapides basées sur le nom du jockey.

Analyse des Accès aux Données via les B-Arbres

Un B-arbre est une structure de données arborescente équilibrée utilisée pour indexer les données, minimisant le nombre d'accès disque nécessaires pour trouver une information. Si 'n' est le nombre de pages du fichier correspondant à la relation Jockey et 'h' la hauteur du B-arbre associé :

Lorsqu'une information est recherchée par le numéro du jockey (qui est la clé primaire et donc indexée) :

Le nombre d'accès aux informations est typiquement h (pour traverser le B-arbre jusqu'à la feuille) + 1 (pour accéder à la page de données réelle). Donc, environ h + 1 accès.

Lorsqu'une information est recherchée par le nom du jockey (si un index sur nom est créé) :

Le nombre d'accès serait environ h' (hauteur de l'index secondaire sur le nom) pour trouver la clé primaire correspondante, puis h (hauteur de l'index primaire) ou 1 (accès direct) pour récupérer l'enregistrement complet de la table principale. Dans une estimation simplifiée, cela peut être h' + 1 si l'index secondaire couvre les données nécessaires ou si la table est organisée de manière à optimiser l'accès via la clé primaire récupérée de l'index secondaire.

Amélioration des Accès Fréquents par Nom

Si la requête par nom est très fréquente, en plus de l'index idx_jockey_nom, on pourrait envisager un index couvrant si les attributs retournés par la requête sont tous inclus dans l'index. Par exemple, si la requête demande souvent Num_jockey et adresse pour un nom donné, un index composite sur (nom, Num_jockey, adresse) pourrait éviter un accès à la table principale.

Requête SQL pour un index composite couvrant (exemple si on recherche nom et adresse) :

CREATE INDEX idx_jockey_nom_adresse ON Jockey (nom, adresse);

Conséquences sur les catalogues : La création d'index ajoute des métadonnées dans les catalogues du SGBD (tables système). Ces catalogues stockent des informations sur la structure de l'index (nom, colonnes indexées, type d'index, etc.). Cela augmente légèrement la taille des catalogues et le temps nécessaire pour les maintenir, mais l'amélioration des performances des requêtes compense largement ce coût.

Rôle du Module du SGBD

Le module du SGBD qui prend en charge ces différents accès (gestion des index, recherche d'enregistrements, optimisation des requêtes) est le Gestionnaire de Fichiers et de Tampons (ou Gestionnaire de Stockage) en collaboration avec l'Optimiseur de Requêtes. Le gestionnaire de stockage est responsable de l'organisation physique des données sur le disque et de la gestion des tampons en mémoire, tandis que l'optimiseur de requêtes détermine le plan d'exécution le plus efficace pour une requête, en utilisant les index disponibles.

Questions Fréquentes sur la Gestion des Bases de Données Équestres

Q1: Pourquoi ajouter des compteurs de victoires plutôt que de les calculer à la volée ?

Ajouter des compteurs précalculés améliore considérablement les performances des requêtes qui demandent fréquemment le nombre de victoires. Bien que cela introduise une redondance et un coût de maintenance (via des triggers), le gain de temps lors des lectures surpasse généralement ce coût pour les bases de données soumises à de nombreuses consultations.

Q2: Quels sont les risques associés à l'utilisation de triggers ?

Les triggers peuvent introduire une complexité dans le système, rendant le débogage plus difficile en cas de comportements inattendus. Ils peuvent également impacter les performances lors des opérations d'écriture (INSERT, UPDATE, DELETE) car des actions supplémentaires sont exécutées. Une gestion prudente et des tests rigoureux sont essentiels.

Q3: Un index améliore-t-il toujours les performances ?

Non, pas toujours. Un index peut améliorer considérablement les performances de lecture (requêtes SELECT) en réduisant le nombre d'accès disque. Cependant, il ajoute un coût aux opérations d'écriture (INSERT, UPDATE, DELETE) car l'index doit également être mis à jour. Un trop grand nombre d'index inutiles peut même dégrader les performances globales du système.