projet 1 : base de données reparties

P RO J E T 1 : BA S E D E
D O N N É E S R E PA RT I E S
G ESTION
Elèves :
David Bréchet
Frédéric Jacot
Charles Secrétan
D ’ UNE BANQUE
DONNÉES DU PROJET
SSC - Bases de Données II Laboratoire de Bases de Données
BD réparties 1
Projet 1: Bases de données réparties
Un dossier est à rendre le 11/01/00 lors de la soutenance.
Le but de ce premier projet est de développer une application simulant le
fonctionnement d’un système de gestion de bases de données réparties (SGBDR).
Il s’agit de reprendre l’application vue en cours (système bancaire) de l’étoffer, et de
considérer la répartition sur trois sites (SSCX1, SSCX2 et SSCX3).
Le travail fait intervenir les étapes suivantes:
1) Définir le modèle conceptuel (EA) Définir deux vues externes: 1.
les clients et 2. les gestionnaires des comptes
2) Définir le modèle logique relationnel. Attention à ne pas oublier la
définition des vues.
3) Identifier et écrire en SQL les requêtes les plus fréquentes et les
plus critiques (en terme de performance).
4) Définir le schéma de fragmentation en fonction de ces requêtes
5) Définir le schéma de localisation (justifiez les répliques en fonction
des problèmes d’accessibilité et de performance)
6) Répartir les requêtes identifiées au 3) en deux sous-groupes :
„
Globales (faisant intervenir plusieurs sites)
„
Locales (faisant intervenir un site local)
Quelle est l’incidence des vues sur les schémas de fragmentation et d’allocation?
Il est à noter que l’application doit permettre la formulation des 4 commandes
SQL suivantes: SELECT, INSERT, UPDATE et DELETE (bien sûr de façon
transparente à l’utilisateur) et doit mettre en œuvre le protocole de validation de
transactions à deux phases.
2
B A S E D E D O N N É E S R E PA R T I E S
G ESTION D ’ UNE BANQUE .
INTRODUCTION
Le but de ce projet est de développer une application simulant le
fonctionnement d’un système de gestion bancaire. Il met en œuvre un système de
gestion de base de données réparties (SGBDR) et pour le rendre convivial, nous
utilisons la méthode « embeded SQL » qui consiste à intégrer des commandes SQL
dans un environnement de programmation C.
La base de donnée est répartie sur 3 sites, qui sont la centrale et deux agences A
et B. Pour nous la centrale se trouve à Genève et les agences sont à Lausanne et à
Neuchâtel.
La centrale est un site, qui n’est accessible que par le gestionnaire. Il peut
interroger un certain type de compte (courant, étudiant…) sur un certain type
d’opération (retrait ou versement). Il peut aussi gérer un employé, c’est à dire qu’il
ajoute ou supprime un employé de la base de donnée.
Dans les agences, les employés peuvent faire des versements ou des retraits sur
un compte, et l’interroger, ils peuvent aussi changer un compte d’agence.
3
MODÈLE CONCEPTUEL DE LA BASE DE DONNÉE
LE SCHEMA ENTITE ASSOCIATION
No_emp
Nom_em
Prénom
Employé
No_op
Type_op
Opération
travaille
Date_op
Type_compte
No_compte
No_agence
Compte
Est dans
Somme
Agence
Nom_agence
Addr_agence
possède
No_client
Nom
Client
Age
Adresse
Prénom
Ce schéma entité association va nous permettre de déduire le schéma relationnel
de la base de donnée.
4
LE SCHÉMA RELATIONNEL
Grâce a ce schéma relationnel on va pouvoir visualiser les tables nécessaires à la
base de donnée. Ces tables serons ensuite fragmentées sur les différents sites.
Domaines
Dch20 : chaînes de caractères de longueur inférieure à 20.
Dch30 : chaînes de caractères de longueur inférieure à 30.
Dnum : entier.
Relation : Client
Attributs :NoClient : Dnum sans nul
Nom : Dch20 sans nul
Prenom : Dch20 sans nul
Adresse : Dch30 sans nul
Age : Dnum sans nul
Identifiant : (NoClient)
Définition : toute personne ayant un compte dans la banque
CLIENT
NoClient
213
456
Nom
Bouana
Legros
Prenom
Doudou
Hervé
Age
23
55
Adresse
Ch. Soleil 13 1240 Bled
R. centrale 33 1512 Ville
Relation : Compte
Attributs : NoClient :Dnum sans nul
NoCompte :Dnum sans nul
TypeCompte : Dnum sans nul
Somme : Dnum sans nul
NoAgence : Dnum sans nul
Identifiant :
(NoCompte + NoAgence)
Identifiant externe: NoAgence référence une Agence
NoClient référence un Client
Définition : Compte du client dans la banque
COMPTE
NoClient
213
456
NoCompte
1234
5678
TypeCompte
courant
étudiant
5
Somme
12000
45000
NoAgence
1
2
Relation : Agence
Attributs :NoAgence : Dnum sans nul
Nom : Dch20 sans nul
Adresse : Dch30 sans nul
Identifiant :
(NoAgence)
Définition : Succursale de la banque
AGENCE
NoAgence
1
2
Nom
Lausanne
Neuchatel
Adresse
Av. de la gare 22 1230 Bled
Av. de Genève 33 1530 Ville
Relation : Employé
Attributs :NoEmp : Dnum sans nul
Nom_Emp : Dch20 sans nul
Prenom_Emp : Dch20 sans nul
NoAgence : Dnum sans nul
Identifiant :
(NoEmp + NoAgence)
Identifiant externe: NoAgence référence une agence
Définition : Employé travaillant dans une agence
EMPLOYE
NoEmp
567
786
Nom_Emp
Ducon
Lepetit
Prenom_Emp
Jules
André
NoAgence
1
2
Relation : Opération
Attributs :NoOp : Dnum sans nul
NoEmp : Dnum sans nul
NoCompte : Dnum sans nul
Montant : Dnum sans nul
TypeOp: Dch20 sans nul
dDate : Dch20 sans nul
Identifiant :
(NoOp)
Identifiant externe: NoCompte référence un compte
NoEmp référence un Employé
Définition : Opération effectuée par un employé sur un compte
6
OPERATION
NoCompte
NoOp
1234
7890
5678
7891
Montant
1000
7000
TypeOp
retrait
versement
dDate
23/01/99 1034
25/01/99 1430
NoEmp
567
567
REQUETES FREQUENTES
Nous avons essayé de séparer les différentes requêtes utilisées dans le cadre du
fonctionnement d’une banque et nous n’avons considérer que les plus fréquentes et
les plus critiques pour la base de donnée. Voici donc les requêtes :
VERSEMENT ET RETRAIT
Lors d’un versement ou d’un retrait, l’employé a besoin du numéro de compte
du client et de l’agence dans lequel le compte se trouve. Lors de l’opération,
l’attribut « somme » ainsi que la table « opération » sont mis à jour.
Issu de la table Opération
NoOp
NoEmp
NoCompte
Montant
TypeOp
Issu de la table Compte
NoCompte
Somme
NoAgence
Les requêtes en SQL pour un versement sont :
NoAgence
FROM Compte
WHERE NoCompte = xxx
if NOT FOUND {
maj_copie_compte();
maj_copie_operation(NoAgence);
}
else {
Update(Compte.somme);
Update(Operation.*);
}
SELECT
7
dDate
Pour un retrait c’est la même requête avec une opération différente retrait au
lieu de versement.
INTERROGATION
Lorsque le client désire consulter les dernières opérations effectuées sur son
compte, l’employé a besoin du numéro de compte et de l’agence dans lequel le
compte se trouve. L’attribut « somme » indique la solde du compte au moment de
la requête. Avec l’attribut « Montant », il peut déterminer la solde du compte au
moment de l’opération. Les autres attributs de la table « Opération » n’intéressent
que le gestionnaire.
Table I1
NoCompte
Montant
TypeOp
dDate
Table V R-I2
NoCompte
Somme
NoAgence
Les requêtes en SQL sont :
NoAgence
FROM Compte
WHERE NoCompte = input in no_cpte
if NOT FOUND {
SELECT NoCompte, Somme, NoAgence
FROM Compte_copie
WHERE NoCompte = no_cpte
SELECT Montant, TypeOp, dDate
FROM Operation_copie
WHERE NoCompte = no_cpte
}
else {
SELECT NoCompte, Somme, NoAgence
FROM Compte_copie
WHERE NoCompte = no_cpte
SELECT Montant, TypeOp, dDate
SELECT
8
Operation
WHERE NoCompte = no_cpte
FROM
}
INTERROGATION PAR LE GESTIONNAIRE
Le Gestionnaire analyse le type d’opération effectuée sur un type de compte. Ce
type de compte est de l’agence Lausanne et Neuchâtel. Pour ce faire, il a besoin des
informations suivantes issues des tables « opération » et « compte ».
Issu de la table opération
No_op
No_emp
No_compte
Montant Type_op
Date_op
Issu de la table compte
No_compte
Somme
Type_compte
No_agence
Voici la requête en SQL :
*
FROM TableOp
WHERE TypeOp=’:type_op’ AND
NoCompte IN (SELECT NoCompte,
FROM Compte
WHERE TypeCompte = ‘:type_cpte’);
SELECT
GÉRER LES EMPLOYÉS PAR LE GESTIONNAIRE
Le gestionnaire gère les employé, il ajoute ou supprime un employé. Pour ce
faire, il a besoin des informations suivantes de la table « employé ».
Issu de la table employé
NoEmp
Nom_Emp
Prenom_Emp
9
NoAgence
Voici les requêtes en SQL :
Supprimer un employé :
TableEmp
WHERE NoEmp =:noemp;
DELETE FROM
Ajouter un employé :
TableEmp,
VALUES (:noemp, :Nom_Emp, :Prenom_Emp, :NoAgeance);
INSERT INTO
SCHEMA DE FRAGMENTATION
Voici le schéma de fragmentation des tables en fonction des requêtes effectuées
sur les attributs. Remarquons que la localisation des fragments a été faite par
rapport aux poids des requêtes. Pour faire la répartition sur les trois sites, nous
avons décidé d’avoir une centrale et deux agences et tout notre travail découle de
cette répartition.
Table Client
NoClient
1
2
3
Nom
Jacot
Secretan
Brechet
Prenom
Frederic
Charles
David
Somme
1000
2000
3000
NoAgence
0
1
1
Age
23
56
24
Adresse
Rue a 1
Rue b 2
Rue c 3
Table Compte
NoCompte
10
11
12
TypeCompte
Etudiant
Epargne
Etudiant
NoClient
1
2
3
Table Opération
NoOp
NoEmp
20
21
22
31
30
32
NoCompte
10
11
12
Montant
TypeOp
dDate
100
200
300
Versement
Virement
Retrait
12/05/99-…
13/06/99-…
18/11/99-…
10
Table Agence
NoAgence
Nom
Adresse
0
Genève
Rue du cucu 3
1
Lausanne
Rue des pins 1
2
Neuchâtel
Rue de la gare 3
Table Employé
NoEmp
30
31
32
Nom_Emp
Dupond
Smith
Ducommun
Prenom_Emp
Jean
John
Georges
NoAgence
0
1
1
Localisation
Données relatives aux client / compte agence A
Données relatives aux client / compte agence B
Versement, retrait, interrogation et gestionnaire / compte agence A
Versement, retrait, interrogation et gestionnaire / compte agence B
Gestionnaire / compte agence A
Gestionnaire / compte agence B
Versement, retrait et gestionnaire / opération agence A
Versement, retrait et gestionnaire / opération agence B
Versement, retrait, interrogation et gestionnaire / opération agence A
Versement, retrait, interrogation et gestionnaire / opération agence B
Données relatives à l’employé / agence A
Données relatives à l’employé / agence B
Données générales
original
A
B
A
B
Centrale
Centrale
A
B
A
B
A
B
Centrale
copie
B
A
B
A
A
B
Centrale
Centrale
Centrale
Centrale
Centrale
Centrale
C’est avec ce schéma de fragmentation que nous avons fait le schéma
d’allocation des fragments. Cela a été fait en regardant quelles étaient les requêtes
critiques et celles qui étaient le plus fréquemment utilisée. C’est aussi par cette
méthode que nous avons placé les copies des fragments aux sites que nous jugions
adéquat.
Dans l’agence A, les fragments des tables Compte et Client sont copiés dans
l’Agence B. Dans l’agence B ces fragments sont copié dans l’agence A. Cela permet
d’avoir toutes les informations nécessaires à disposition en local afin de minimiser
le temps nécessaire pour les requêtes. Le fragment de la table opération est copié
dans la centrale car c’est le gestionnaire qui en à le plus besoin pour faire ses
requêtes.
En plus la copie du fragment de la table Compte qui se trouve à la centrale
11
SCHÉMA D’ALLOCATION
Voici la localisations sur les différents sites des différents fragments et de leur
copie tels qu’ils ont été implémenté dans l’application.
CENTRALE :
Frag_Centr_Compte
NoCompte TypeCompte
Ageances
NoAgeance Nom
Adresse
FragA_TableEmp_CP
Nom_Emp
NoEmp
Prenom_Emp NoAgeance
FragB_TableEmp_CP
Nom_Emp
NoEmp
Prenom_Emp NoAgeance
FragA_TableOp_CP
NoEmp
NoOp
NoCompte
Montant
TypeOp
Date
FragB_TableOp_CP
NoEmp
NoOp
NoCompte
Montant
TypeOp
Date
AGENCE A :
FragA_Compte
NoCompte NoClient
NoAgeance Somme
FragA_Client
Nom char
NoClient
Prenom
Adresse
Age
FragA_TableOp
NoEmp
NoOp
NoCompte
Montant
TypeOp
FragB_Client_CP
Nom
NoClient
Prenom
Adresse
Age
12
Date
FragB_Compte_CP
NoCompte NoClient
NoAgeance Somme
FragC_Compte_CP
NoCompte TypeCompte
FragA_TableEmp
Nom_Emp
NoEmp
Prenom_Emp NoAgeance
AGENCE B :
FragB_Compte
NoCompte NoClient
NoAgeance Somme
FragB_Client
NoClient
Nom char
Prenom
Adresse
Age
FragB_TableOp
NoEmp
NoOp
NoCompte
Montant
TypeOp
FragA_Client_CP
Nom
NoClient
Prenom
Adresse
Age
FragA_Compte_CP
NoCompte NoClient
NoAgeance Somme
FragC_Compte_CP
NoCompte TypeCompte
FragB_TableEmp
Nom_Emp
NoEmp
Prenom_Emp NoAgeance
13
Date
REALISATION DE LA BASE DE DONNÉE
STRATÉGIE UTILISEE
Nous avions trois sites pour faire notre banque. Nous avons donc fait deux
agences et une centrale. C’est sur ces sites que sont situés les différents fragments
de la base de données. Tous les fragments sont dupliqué et mis sur un autre site
afin de pouvoir travailler même si un site est en panne. Chaque copie est utilisable
sur le site où elle se trouve. Cela permet de gagner du temps lorsque des requêtes
utilisent les fragments originaux qui sont localisés sur d’autres sites. Pour toute
requête qui fait une mise à jour sur un autre site, il faut d’abord vérifier que les
éléments de la requêtes sur le sites soient correctes avant de les envoyer sur le site
voulu, cela s’appelle le « commit à deux phase ».
Nous avons fait trois applications différentes pour faire cette base de donnée
répartie, l’initialisation, la centrale et l’agence. les applications sont implémentées en
C avec des commandes SQL dans le code. Nous utilisons Oracle comme interface
entre la base de donnée et le langage C.
La première application sert à initialiser la base de donnée et à la peupler afin
d’avoir un minimum de données pour faire nos requêtes.
La deuxième application est la centrale. Elle permet au gestionnaire de faire son
travail de vérification des comptes. Il interroge un type de compte donné et peut y
voir l’opération de versement ou de retrait, effectuée. Il peut gérer les employés,
c’est à dire qu’il peut ajouter et enlever un employé de la base de donnée en
vérifiant que cet employé existe.
La troisième application est l’agence. Elle fait toutes les requêtes nécessaire à la
gestion des clients et des comptes de la banque. L’employé peut ouvrir ou fermer
un compte, interroger, faire des versement et des retraits sur un compte. Il peut
aussi modifier des données relatives au client. Si le client change d’adresse, il est
alors transféré sur l’autre agence ainsi que son ou ses comptes.
Un journal se trouve sur chaque site et est mis à jour à chaque fois qu’une
requête SQL modifie un fragment de table.
Pour chaque requête impliquant une mise à jour, nous vérifions à chaque étape
qu’il n’y a pas d’erreur générée par la requête. Arrivé à la fin de celle ci, on essaie
d’écrire dans le journal, si on y arrive, on fait le « commit », sinon on annule tout.
14
DESCRIPTION DE L’APPLICATION
CENTRALE
Une fois le mot de passe du gestionnaire validé, les connexions avec les
différents sites sont établies. Un menu propose au gestionnaire différents choix :
interroger un type de compte , gérer un employé, soit quitter l’application.
Pour gérer un employé, le gestionnaire à le choix d’ajouter ou de supprimer un
employé. Pour l’ajout d’un employé dans l’agence A, on prend les données
nécessaire, on teste si le numéro d’employé existe dans une des deux agences, si
non mise à jour du fragment FragA_TableEmp sur le site A, ainsi que sa copie
FragA_TableEmp_CP sur le site Centrale. Ecriture sur les journaux de l’agence A
et de la Centrale, si pas d’erreur, validation des mise à jour par un commit.
Supprimer un employé, on demande le numéro d’employé, on recherche le
numéro si le numéro existe et on supprime l’entrée correspondante dans le
fragment FragA_TableEmp si l’employé travail à l’agence A, ainsi que l’entrée dans
la copie FragA_TableEmp_CP, après l’écriture dans les journaux correspondants
on valide la suppression.
Pour interroger un compte qui se trouve dans l’agence A, on demande le type
de compte et la transaction (versement ou retrait) et on interroge le fragment
FragA_TableOP_CP avec le type d’opération et le type de transaction. Ce fragment
se trouve sur le site de la central ce qui permet de rester en local et de gagner du
temps. Pour interroger la base de donnée, on utilise un tampon qui prend les
données requises et les transmet à l’application qui les affiches. On ne peut voir que
les transactions sur un type de compte et non sur un compte donné. Une fois
l’opération faite, retour au choix initial.
AGENCE
Une fois le mot de passe de l’employé validé, les connexions avec les différents
sites sont établies. Un menu propose à l’employé différents choix : versement,
retrait, interrogation d’un compte, soit changement d’adresse d’un client
Avant chaque opération, on vérifie l’existence et la localisation du compte.
Pour faire un versement-retrait, suivant sa localisation, par exemple à l’agence A,
on travail en local sur le fragment FragA_Compte ou sur FragB_Compte_CP.
Pour interroger un compte on fait une simple requête.
Pour changer d’adresse on prend le compte concerné, on le déplace. Les
éléments de la table opération qui concerne le compte à déplacé et les données du
client sont aussi déplacés. Une fois les éléments déplacés, on les efface des tables
15
d’origines. Tout cela nécessite de faire des mises à jour dans les tables, donc on
ecrit dans les différents journaux
Une fois connecté choix de l’opération). Puis demande du numéro de compte
ou l’opération doit avoir lieu. Vérification de l’endroit où se trouve le compte si le
compte est en local, on travail sur le fragment même, sinon travail sur la copie du
fragment, ensuite l’opération voulue est effectuée.
Pour l’interrogation, requête SQL par un tampon à la base de données pour
ressortir les informations voulues.
Pour le versement ou le retrait, demande du montant et requête de mise a jour
des tables opérations et compte. Ces mises à jour entraînent une écriture dans les
différents journaux. Une fois ces écritures réussies, il y a validation des mises à jour
par un commit.
Pour le changement d’adresse, on demande la nouvelle adresse et on met à jour
les fragments concernés. Si le compte change d’agence, les données du client, des
opérations et du compte, qui appartiennent au compte, sont aussi transférées. Les
mises à jour implique d’ajouter des données dans certains fragment et de les enlever
dans d’autres, toutes ces transactions sont reportées dans les journaux, si toutes les
écritures sont effectuées correctement, il y a alors validation des mises à jour.
DESCRIPTION DES FONCTIONS
AGENCE :
void connexion(char *u)
{Introduction et vérification du login et du mot de passe ;
connexion au site u du SGBDR, avec permissions d’écriture et de lecture ;}
void deconnexion()
{Déconnexion des différents sites ;
}
int input()
{Introduction du numéro de compte ;
Vérification de la localisation du compte ( location_check(compte);
Si ‘compteur’ =0,
Teste les valeurs de ‘rvi’, et suivant la valeur, aller dans interrogation(compte) ou
retrait_versement(compte) ou location_change(compte)
return 1;
}
int JournalC(char texte[])
{teste si ouverture du journal possible ;
si oui, écriture de la date et du texte dans le journal ;
16
fermeture du journal ;
}
même chose pour
int JournalA(char texte[]){
int JournalB(char texte[]){
int location_check(int compte)
{Interrogation des éléments de la table FragA_Compte, où NoCompte = :compte
et mise des éléments dans la structure inter_cp;
teste si erreur, cela veut dire que le compte n’est pas dans FragA_Compte ;
alors Interrogation des éléments de la table FragB_Compte_CP,
où NoCompte = :compte et mise des éléments dans la structure inter_cp2 ;
si erreur, ce compte n’existe pas ;
location =1 ;
interrogation des éléments de la table FragB_Client_CP,
où NoClient =:inter_cp2.no_client et mise dans la structure client;
affichage des éléments de client.
Sinon si pas d’erreur, cela veut dire que le compte est dans FragA_Compte ;
location =0 ;
interrogation des éléments de la table FragA_Client,
où NoClient =:inter_cp2.no_client et mise dans la structure client;
affichage des éléments de client.
}
int interrogation(int compte)
{teste si location =0
alors affichage que le compte est sur A ;
exécution sur ssc8a ;
déclaration d’un tampon pour
interrogation de typeop et de dDate de la table FragA_TableOp,
où NoCompte = :compte et mise dans la structure inter _op ;
ouverture du tampon ;
tant qu’il n’y a pas d’erreur
recherche des éléments dans le tampon;
fermeture du tampon ;
affichage du nb d’opération qui est le nb de tuple dans le tampon ;
si le nb de tuple =0
alors compte inxistant ;
input() ;
sortie de la fonction ;
ouverture du tampon
tant qu’il y a des tuples
recherche des éléments dans le tampon et mise dans la structure inter_op ;
affichage des éléments sortis du tampon , le montant, le type d’op, la date de l’Op;
fermeture du tampon
affichage de la somme ducompte ;
sinon affichage que le compte est sur B ;
exécution sur ssc8b ;
déclaration d’un tampon pour
interrogation de typeop et de dDate de la table FragB_TableOp,
17
où NoCompte = :compte et mise dans la structure inter _op ;
ouverture du tampon ;
tant qu’il n’y a pas d’erreur
recherche des éléments dans le tampon;
fermeture du tampon ;
affichage du nb d’opération qui est le nb de tuple dans le tampon ;
si le nb de tuple =0
alors compte inexistant ;
input() ;
sortie de la fonction ;
ouverture du tampon
tant qu’il y a des tuples
recherche des éléments dans le tampon et mise dans la structure inter_op ;
affichage des éléments sortis du tampon , le montant, le type d’op, la date de l’Op;
fermeture du tampon
affichage de la somme du compte ;
compteur=1;
}
PROBLEMES RENCONTRÉ
Difficulté à la compilation par le moteur d’oracle.
AMELIORATIONS POSSIBLE
CONCLUSION
Ce projet nous a permis de nous familiariser avec les bases de données répartie,
ainsi que de plonger des requêtes SQL dans un langage comme le C.
Ce projet nous a pris énormément de temps pour implémenter les applications.
Le fait que le compilateur d’Oracle ne fonctionne pas correctement a engendrer
beaucoup de temps perdu.
ANNEXES
Listing des applications.
18