Rapport sur les TPs TP1 : Soit la base de données relationnelle Comptes composée des relations suivantes : Client (#No, Nom, Prénom, Adresse, Ville) Agence (#No, Nom, Adresse, Ville) Compte (#No, Tpe!Compte!No, "ateuverture, "ecouvert!autorise, Solde, Client!No, Agence!No) Tpe!Compte Tpe!Compte (#No, (#No, Nom, "escription) "escription) peration (#No, Tpe!peration!No, Compte!No) Tpe!peration Tpe!peration (#No, "escription) "escription) n a créé le sc$éma de cette base de données gr%ce au script creationBaseComptes.sql
1- Répartition Réparti tion des données donné es : défnition et création cré ation des ragments sur les deux sites A partir de la base Comptes centralisée dé&' mise en oeuvre sur la base NSAS* de la mac$ine Serveur*, on désire construire une base de données répartie sur les deu+ sites : Serveur* et Serveur.es r/gles de répartition ou de 0ragmentation ont été dé1nies en 0onction de certains crit/res d2utilisation et de manipulation des donnéesa. Fragmentation horizontale pour la table lient : - Sur Serveur* : la table Client!* contenant les clients de Casablanca sans la colonne VilleCREATE TABLE Client_1 (Nom, Prénom, Adresse) AS SELECT Nom, Prénom, Prénom, Adresse FROM Client WERE !ille " #C$s$%l$n&$#' - Sur Serveur : la table Client! contenant les clients de 3abat sans la colonne Ville-
COP FROM m$dAnoir*+$ssensi$s1 m$dAnoir*+$ssensi$s1 TO m$dAnoir*+$ssensi$sm$dAnoir*+$ssensi$sREPLACE Client_- (Nom, Prénom, Adresse) .SN/ SELECT Nom, Prénom, Adresse FROM Client WERE !ille " #R$%$t#' b. Fragmentation horizontale horizontal e pour la table ompte : appartenant au+ clients - Sur Serveur* : la table Compte!* avec les comptes appartenant de CasablancaCREATE TABLE Com+te_1 (No, T0+e_Com+te_No, $teO2!ert2re, e&o2!ert_$2torise, e&o2!ert_$2torise, Solde, Client_No, A3en&e_No) AS SELECT No, T0+e_Com+te_No, $teO2!ert2re, e&o2!ert_$2torise, Solde,
Client_No, A3en&e_No FROM Com+te WERE Client_No N (SELECT No FROM Client_1)'
- Sur Serveur : la table Compte! avec les comptes appartenant au+ clients de 3abat-
COP FROM m$dAnoir*+$ssensi$s1 TO m$dAnoir*+$ssensi$sREPLACE TABLE Com+te_- (No, T0+e_Com+te_No, $teO2!ert2re, e&o2!ert_$2torise, Solde, Client_No, A3en&e_No) .SN/ SELECT No, T0+e_Com+te_No, $teO2!ert2re, e&o2!ert_$2torise, Solde, Client_No, A3en&e_No FROM Com+te WERE Client_No N (SELECT No FROM Client WERE !ille " #R$%$t#)' c. Fragmentation horizontale pour la table !peration : - Sur Serveur* : la table peration!* correspondant au+ opérations des comptes de la table compte!*CREATE TABLE O+er$tion_1 (No, T0+e_O+er$tion_No, Com+te_No) AS SELECT No, T0+e_O+er$tion_No, Com+te_No FROM O+er$tion WERE Com+te_No N (SELECT Com+te_No FROM Com+te_1)' - Sur Serveur : la table peration! correspondant au+ opérations des comptes de la table Compte!-
COP FROM m$dAnoir*+$ssensi$s1 TO m$dAnoir*+$ssensi$sREPLACE O+er$tion_-(No, T0+e_O+er$tion_No, Com+te_No) .SN/ SELECT No, T0+e_O+er$tion_No, Com+te_No FROM O+er$tion W4ere Com+te_No N (SELECT Com+te_No FROM Com+te_-)' d. "éplacement complet de la table T#pe$ompte sur %er&eur' : T#pe$ompte$' COP FROM m$dAnoir*+$ssensi$s1 TO m$dAnoir*+$ssensi$sREPLACE T0+e_Com+te_-(no,li%elle_&om+te,des&ri+tion) .SN/ SELECT no,li%elle_&om+te, des&ri+tion FROM T0+e_Com+te' ROP TABLE T0+e_Com+te' e. "éplacement complet de la table T#pe$!peration sur %er&eur' : T#pe$!peration$' COP FROM m$dAnoir*+$ssensi$s1 TO m$dAnoir*+$ssensi$sREPLACE T0+e_O+er$tion_-(no, li%elle_o+er$tion, de&ri+tion) .SN/ SELECT no, li%elle_o+er$tion, des&ri+tion 5rom T0+e_O+er$tion' ROP TABLE T0+e_O+er$tion' . (es %é)uences restent sur %er&eur1. Elles le sont dé678
'- réation du lien inter * base +database lin, Sur c$a4ue base (Serveur* et Serveur), créer deu+ database lin5 (dbl!nsias* et dbl!nsias) permettant d6accéder au+ ob&ets distants-
CREATE ATABASE LN9 d%l_ensi$s1 CONNECT TO ENTFE B +$ss .SN/ #ensi$s1#' CREATE ATABASE LN9 d%l_ensi$s- CONNECT TO m$dAnoir ENTFE B +$ss .SN/ #ensi$s-#' Tester les liens établis sur c$a4ue base en accédant au+ ob&ets distants dans les deu+ sensCONNECT m$dAnoir*+$ssensi$s-' SELECT : FROM Client_1d%l_ensi$s1' CONNECT m$dAnoir*+$ssensi$s1' SELECT : FROM Client_-d%l_ensi$s-'
- /0out des contraintes de base A&outer, sur c$a4ue 0ragment, les contraintes initiales 4ui ont disparues : (*) .es contraintes de clé primaire, ALTER TABLE Client_- A PRMAR 9E No' ALTER TABLE A3en&e_- A PRMAR 9E No' ALTER TABLE Com+te_- A PRMAR 9E No' ALTER TABLE T0+e_Com+te_- A PRMAR 9E No' ALTER TABLE O+er$tion_- A PRMAR 9E No' ALTER TABLE T0+e_O+er$tion_- A PRMAR 9E No' () .es contraintes de ré0érences classi4ues si la table 7m/re2 est sur le m8me site, ALTER TABLE Com+te_- A CONSTRANT 5;1 FORE/N 9E(Client_No) REFERENCES Client(No)' ALTER TABLE Com+te_- A CONSTRANT 5;- FORE/N 9E(A3en&e_No) REFERENCES A3en&e(No)' ALTER TABLE O+er$tion_- A CONSTRANT 5;< FORE/N 9E(Com+te_No) REFERENCES Com+te(No)' (9) .es contraintes de ré0érences par 7trigger2 si la table 7m/re2 est sur un site distant "eu+ triggers : un trigger sur la 71lle2 remplaant la ;3, un trigger sur la 7m/re2 interdisant de supprimer une ligne ré0érencéePour la table Agence : CONNECT m$dAnoir*+$ssensi$s1' CREATE OR REPLACE TR//ER tri3_$3en&e_mot4er BEFORE ELETE OR .PATE OF No ON A3en&e FOR EAC ROW ECLARE = n2m%er >" ?' BE/N SELECT &o2nt(:) NTO = 5rom Com+te_-d%l_ensi$sWERE A3en&e_No " >OL8No'
F =@? TEN RASE_APPLCATON_ERROR(-?1,#Cette $3en&e est 2tilisée8#)' EN F' EN' * CONNECT m$dAnoir*+$ssensi$s-' CREATE OR REPLACE TR//ER tri3_$3en&e_d$234ter BEFORE NSERT OR .PATE OF A3en&e_No ON Com+te_FOR EAC ROW ECLARE = n2m%er >" ?' BE/N SELECT &o2nt(:) NTO = FROM A3en&ed%l_ensi$s1 WERE No " >NEW8A3en&e_No' F ="? TEN RASE_APPLCATON_ERROR(-?1,#Cette $3en&e est ine=ist$nte8#)' EN F' EN' *
- réation de la base répartie (a) crire les re4u8tes permettant de créer les ob&ets virtuels répartis (vie? ou snonm) dans les deu+ dictionnaires : Serveur* et ServeurCONNECT m$dAnoir*+$ssensi$s1' CREATE DEW Client (No,Nom,Prenom,Adresse,Dille) AS SELECT No,Nom,Prenom,Adresse,#C$s$%l$n&$# FROM Client_1 .NON SELECT No,Nom,Prenom,Adresse,#R$%$t# FROM Client_-d%l_ensi$s-' CONNECT m$dAnoir*+$ssensi$s-' CREATE DEW Client (No,Nom,Prenom,Adresse,Dille) AS SELECT No,Nom,Prenom,Adresse,#C$s$%l$n&$# FROM Client_1d%l_ensi$s1 .NON SELECT No,Nom,Prenom,Adresse,#R$%$t# FROM Client_-' (b) crire les re4u8tes de consultation de ces ob&ets virtuels sur Serveur* et sur ServeurCONNECT m$dAnoir*+$ssensi$s1' SELECT : FROM Client' CONNECT m$dAnoir*+$ssensi$s-' SELECT : FROM Client'
2- 3ise 4 0our en réparti : le commit +ou rollbac, 4 deux phases (a) Sur la base de Serveur*, insérer deu+ nouveau+ clients : un dans la table Client!* et un dans la table Client! distanteNSERT NTO Client_1 DAL.ES(#nom1#, #+renom1#, #$dresse1#)'
NSERT NTO Client_-d%l_ensi$s- DAL.ES(#nom-#, +renom-#, #$dresse-#)' Véri1er dans c$a4ue table la présence du nouveau clientSELECT : FROM Client_1' SELECT : FROM Client_-d%l_ensi$s-' (b) Sur la base de Serveur, insérer deu+ autres clients : un dans la table Client! et un dans la table Client!* distanteNSERT NTO Client_- DAL.ES(#nom<#, #+renom<#, #$dresse<#)' NSERT NTO Client_1d%l_ensi$s1 DAL.ES(#nomG, #+renom#, #$dresse#)' Véri1er dans c$a4ue table la présence du nouveau clientSELECT : FROM Client_-' SELECT : FROM Client_1d%l_ensi$s1' (c) Sur la base de Serveur*, 0aites un C@@TCOMMT' (d) Sur la base de Serveur, 0aites un 3..AC=ROLLBAC9' Véri1er dans les deu+ tables des deu+ sites et commenterLe ROLLBAC9 sim2le 2ne +$nne8 To2tes les insertions des &lients ét$ient $nn2lées 7 &$2se de &e ROLLBAC9, mHme s#il n#$ été 5$it I2e s2r l$ %$se de Ser!e2r-8
TP' : 1- 3ise en oeu&re de la réplication s#nchrone a- Copier la table centrale APPA3. dans le site de 3abat (Serveur)CONNECT m$dAnoir*+$ssensi$s1' CREATE TABLE A++$reil(no_$++$reil n2m%er() PRMAR 9E, desi3n$tion !$r&4$r( CREATE TR//ER insert_$++ AFTER NSERT ON A++$reil FOR EAC ROW
BE/N NSERT NTO A++$reil_Co+0d%l_ensi$sDAL.ES(>NEW8no_$++$reil, >NEW8desi3n$tion, >NEW8+ri=, >NEW8&$r$&teristiI2es_te&4niI2es)' EN' * Po2r l$ s2++ression > CREATE TR//ER delete_$++ AFTER ELETE ON A++$reil FOR EAC ROW BE/N ELETE FROM A++$reil_Co+0d%l_ensi$s- WERE no_$++$reil " >OL8no_$++$reil' EN' * Po2r l$ mise 7 6o2r > CREATE TR//ER 2+d$te_$++ AFTER .PATE ON A++$reil FOR EAC ROW BE/N .PATE A++$reil_Co+0 d%l_ensi$sSET no_$++$reil " >NEW8no_$++$reil, desi3n$tion " >NEW8desi3n$tion, +ri= " >NEW8+ri=, &$r$&teristiI2es_te&4niI2es " >NEW8&$r$&teristiI2es_te&4niI2es WERE no_$++$reil " >OL8no_$++$reil' EN' * c- TesterTester le tri33er de l#insertion > NSERT NTO A++$reil(1,#$++1#, ELETE FROM A++$reil WERE No " 1' SELECT : FROM A++$reil_Co+0 WERE No " 1' Tester le tri33er de l$ mise 7 6o2r > .PATE A++$reil SET +ri= " 1?? WERE No " 1' SELECT : FROM A++$reil_Co+0 WERE No " 1'
B 55 - 3ise en oeu&re de la réplication as#nchrone a- Créer une image (clic$é) de la table centrale APPA3. dans c$acun des autres sites- .e ra0rac$issement doit 8tre rapide et sa mise ' &our doit 8tre eDectuée toutes les 9E minutesCREATE SNAPSOT LO/ ON A++$reil' CREATE SNAPSOT im$3e_$++$reil REFRES FAST START WT SSATE NEJT SSATE K
Po2r +o2!oir 5$ire le test, on &rée 2ne im$3e I2i 5$it 2n r$5r$&4issement &4$I2e - min2tes +$r e=em+le8
