TP et suite TD sur les Transactions et Bases de Données

Université de La Rochelle
Département Informatique
Licence –L3 - Informatique
UE –Bases De Données
Année Universitaire 2010/2011
M. EBOUEYA
TP et suite TD
sur les Transactions et Bases de Données Réparties
Vous devez soit avoir suivi le cours SQL, chapitres portant sur le dictionnaire de données Oracle et les
droits d’accès, ou l’avoir accessible dans une fenêtre. (Ce cours n’a pas été dispensé du fait des grèves.
Le texte de ce cours est en ligne.)
Le but du jeux est de réaliser les exercices suivants, éventuellement en vous aidant d’un exemple de
réalisation succinct et incomplet, mais surtout en justifiant/commentant vos actions. Il y aura les
copies écrans de vos réalisations, et un compte-rendu personnel chacun. Vous déposez sur Moodle
(chacun et séparément) a 11h30 ce que vous avez pu faire.
Exercice 7 : Transactions, suite et pas fin
Ajoutez, si nécessaire, les énoncés SQL de spécification du mode de contrôle de concurrence les plus appropriés
pour les transactions suivantes qui portent sur le schéma de VentesPleinDeFoin du cours SQL (reprise cidessous) et dont le schema est en ligne aussi..
On tient à ce que les ordonnancements produits soient sérialisables.
a)
SELECT * FROM Article;
SELECT * FROM Article
WHERE quantitéEnStock = 0;
COMMIT;
b)
UPDATE Article
SET prixUnitaire = prixUnitaire * 1.1;
COMMIT;
Courtoisie de Emmanuel Coquery,
1
Université de La Rochelle
Département Informatique
c)
SELECT * FROM Article
WHERE noArticle = 10;
UPDATE Article
SET prixUnitaire = prixUnitaire * 0.9
WHERE noArticle = 10;
COMMIT;
d)
UPDATE Client
SET noTéléphone = ‘(123)456-7890’
WHERE noClient = 10;
COMMIT ;
Courtoisie de Emmanuel Coquery,
2
Université de La Rochelle
Département Informatique
Exercice 8 : Manipulation d'une base de données à partir d'une autre
Vous avez travaillé jusqu'à maintenant dans la base de données masterin avec le compte L3Infoxx.
Vous avez aussi accès à la base de données L3Info, et à son compte L3Infoxx. (à vérifier).
Quels rôles jouez vous dans ces deux bases de données. Si necessaire, crée des comptes utilisateurs
ayants moins de droits globaux ou partiels.
Connectez-vous à la base o L3Info. Pour accéder à la base masterin, il suffit d'écrire
CONNECT L3Infoxx/L3Infoxx@masterin
Attention l'instruction ci-dessus vous déconnecte de L3Info. Pour revenir à L3Infoxx il faut faire un
nouveau CONNECT.
Les comptes sur masterin et sur L3Info possèdent les mêmes privilèges. En particulier, vous avez le
droit CREATE TABLE pour y créer des tables.
1. Créer un lien de base de données à utilisateur connecté de votre compte dans L3Info à votre
compte dans masterin par la commande SQL suivante émise dans votre compte de L3Info :
CREATE DATABASE LINK masterin USING 'masterin'
masterin suivant immédiatement DATABASE LINK est le nom (partiel ici) du lien de base de
données créée,
masterin entre guillemets simples suivant USING spécifie le nom de service d'une base de
données distante. Ce type de lien suppose que compte interrogateur et compte accédé aient le
même nom (dans leur base respective) et le même mot de passe au moment de la création du lien
et au moment de l'utilisation du lien. Ce lien de base de données va vous permettre d'accéder à
partir de votre compte de masterin aux tables de votre compte d'L3Info. Les propriétés de ce lien
sont accessibles par la vue USER_DB_LINKS.
2. Accéder à la structure de votre table dans la base de données masterin par la commande DESC
ou DESCRIBE de SQL*Plus. La notation TABLE@LIEN permet de préciser le lien à utiliser.
On constatera que le nom du compte peut être indiqué ou non et que le nom de la base de
données ou de son lien peut être global.
3. Accéder de votre compte de la base L3Info au contenu de votre table dans la base de données
masterin par la commande SELECT de SQL.
4. Réaliser une jointure de votre table dans masterin avec une table que vous créez dans votre
compte de L3Info. Cette jointure peut inclure agrégats et sous-requêtes.
5. Mettre à jour votre table dans la base de données masterin à partir de votre compte dans
L3Info. Comparer la validation avec la validation des parties précédentes. Autre conclusion.
6. A contrario, on notera qu'Oracle n'autorise pas les commandes CREATE, ALTER et DROP de
définition de données distantes.
Courtoisie de Emmanuel Coquery,
3
Université de La Rochelle
Département Informatique
Exercice 9 : la réplication dans une base de données répartie
Au lieu de manipuler les tables d'un compte de base de données à partir d'une base de données
distante, on va maintenant dupliquer une table dans une base de données distante.
Assurez-vous que votre compte de L3Info contient deux seuls objets. Lequel en particulier ?
Supprimer toutes les clés sur vos tables dans masterin.
1. Définir dans votre compte L3Info une vue matérialisée ou snapshot ou cliché ou copie de
votre table de la base de données masterin par la commande
CREATE MATERIALIZED VIEW TableVM REFRESH NEXT SYSDATE + 1/480 AS SELECT *
FROM monCompteActuel.Table@masterin; Est-ce possible ? Si cela ne l'est pas, demander à
l'administrateur de base de données le droit système CREATE SNAPSHOT. Est-ce alors possible ?
2. Lister les différents objets créés.
3. Accéder aux propriétés de la vue matérialisée en utilisant la vue USER_MVIEWS.
4. Interroger la vue matérialisée.
5. Mettre à jour la table maître (appartenant à la base de données MASTERIN) de votre vue
matérialisée.
6. Au bout de 3 mn (pourquoi 3 mn ?), constater que la vue matérialisée n'a pas été rafraîchie.
7. Consulter la vue USER_JOBS des travaux possédés par l'utilisateur et notamment ses
colonnes JOB (le processus de rafraîchissement), FAILURES (le nombre de démarrages et
d'échecs du travail depuis son dernier succès) et WHAT. Combien d'échecs a connu le
processus de rafraîchissement ? En fait, la procédure de rafraîchissement ne peut pas accéder à
votre compte, n'en connaissant pas le mot de passe.
8. Rafraîchir manuellement le vue matérialisée en émettant la commande EXECUTE
DBMS_REFRESH.REFRESH('VueMatérialisée');
9. Détruire le lien de base de données MASTERIN et construire un nouveau lien plus souple que
le lien de base de données à utilisateur connecté, dit lien de base de données à utilisateur fixe,
de votre compte dans L3Info à votre compte dans masterin (et plus généralement à un compte
quelconque avec un mot de passe qui ne soit pas le mot de passe de votre compte dans L3Info)
par la commande SQL suivante émise dans votre compte de L3Info: CREATE DATABASE
LINK MASTERIN CONNECT TO M1IFxx IDENTIFIED BY motDePasse USING
'MASTERIN' ; Utiliser à nouveau USER_DB_LINKS.
10. Constater et préciser le processus de rafraîchissement.
Courtoisie de Emmanuel Coquery,
4
Université de La Rochelle
Département Informatique
Exercice 10 : Manipulation d’une base de données répartie en deux autres.
Avant propos : la une suite suivante est une proposition de manipulations et ne doivent pas
nécessairement être suivie à la lettre. Il est plus important de comprendre les manipulations effectuées
et justifier /commenter les resultats obtenus (copies d’ecrans, utilisation des instructions
d’interrogation du dictionnaire de données et du contexte).
Les résultats obtenus lors de ces manipulations devraient susciter des questions dont la réponse peut
se trouver dans une manipulation supplémentaire.
Il est recommandé de visualiser le résultat des manipulations au fur et a mesure dans le dictionnaire.
Manipulations
Mettez en place le système suivant :
bd2. esnig .cifom .ch
BD avec
schéma
global
es52
es14.world
dilbert .esnig .cifom .ch
es21.world
artem is .cpln .ch
es14
es21
Tester les connexions (tnsping).
Créer tous les objets nécessaires à la mise en place d'une relation avec
une fragmentation horizontale.
une fragmentation verticale.
Visualiser les requêtes globales avec l'analyseur
Créer les objets nécessaires à la mise à jour (INSERT/UPDATE/DELETE) des relations.
Tester la mise en place d’un schéma avec une clé primaire globale (Non nulle et unique) à partir d'une
numérotation automatique et une contrainte de domaine sur différents fragments.
Courtoisie de Emmanuel Coquery,
5
Université de La Rochelle
Département Informatique
--Dans base es21
CREATE TABLE EmpNE (numero Number(9) CONSTRAINT pk_EmpNE PRIMARY KEY ,
nom
Varchar(20) ,
prenom Varchar(20),
salaire Number(5)) ;
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
EmpNE
EmpNE
EmpNE
EmpNE
VALUES
VALUES
VALUES
VALUES
(1,'LeRiche','Jean',15000 ) ;
(2,'LeMoyen','Bernard',6000 );
(3,'LePauvre','Henri',2000 );
(4,'LeSDF','Perdu',0 ) ;
-- Dans es_14
CREATE TABLE EmpBE (numero Number(9) CONSTRAINT pk_EmpBE PRIMARY KEY ,
nom
Varchar(20) ,
prenom Varchar(20),
salaire Number(5)) ;
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
EmpBE
EmpBE
EmpBE
EmpBE
VALUES
VALUES
VALUES
VALUES
(1,'LeBernois','Hans',12000 );
(2,'LeBiennois','Fritz',9000);
(3,'LeJBernois','Fred',6500 );
(4,'DeThoune','Joanna',5750 );
--Dans base es52 (la base globale)
-- Creation des db_link
CREATE DATABASE LINK es14.world
CONNECT TO berkani IDENTIFIED BY berkani
USING 'es14.world' ;
CREATE DATABASE LINK es21.world
CONNECT TO berkani IDENTIFIED BY berkani
USING 'es21.world';
--independance à la localisation
CREATE SYNONYM EmpBE FOR [email protected] ;
CREATE SYNONYM EmpNE FOR [email protected] ;
--Fragmentation horizontale
--Création d'une vue qui regroupe les employés de NE et de BE
CREATE OR REPLACE VIEW EmpNE_et_BE AS
SELECT nom, prenom, salaire
FROM EmpNE
UNION
SELECT nom,prenom,salaire
FROM EmpBE ;
--Vérification
ES52>select * from empne_et_be;
NOM
-------------------DeThoune
LeBernois
LeBiennois
LeJBernois
LeMoyen
LePauvre
LeRiche
LeSDF
PRENOM
SALAIRE
-------------------- ---------Joanna
5750
Hans
12000
Fritz
9000
Fred
6500
Bernard
6000
Henri
2000
Jean
15000
Perdu
0
--Dans base es14
Courtoisie de Emmanuel Coquery,
6
Université de La Rochelle
Département Informatique
CREATE TABLE EmpJU (numero Number(9) CONSTRAINT pk_EmpJU PRIMARY KEY ,
nom
Varchar(20) ,
prenom Varchar(20),
salaire Number(5),
Localite Varchar(20)) ;
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
EmpJU
EmpJU
EmpJU
EmpJU
VALUES
VALUES
VALUES
VALUES
(1,'Dutronc','Alain',9000,'Porrentruy');
(2,'Lachat','Pierre',15000,'Courgenay');
(3,'Argentier','Dorée',8000,'Glovelier');
(4,'Muller','Alfred',6000,'Les Bois');
-- es52
CREATE SYNONYM EmpJU FOR [email protected] ;
--Fragmentation verticale
--Création d'une vue qui regroupe les employés de NE et de JU
--ayant les mêmes salaires.
CREATE OR REPLACE VIEW MemeSalaireNE_JU AS
SELECT tNE.nom, tNE.prenom, tNE.salaire,tJU.localite
FROM EmpNE tNE,EmpJU tJU
WHERE tNE.salaire = tJU.salaire ;
--Vérification
ES52>select * from memesalairene_ju;
NOM
-------------------LeMoyen
LeRiche
PRENOM
SALAIRE
LOCALITE
-------------------------------------Bernard
6000
Les Bois
Jean
15000
Courgenay
--Fragmentation mixte
--Création d'une vue qui regoupe les employés de NE et de BE ayant
--les mêmes salaires que ceux de JU.
CREATE OR REPLACE VIEW MemeSalaireNE_BE_JU AS
SELECT tNEBE.nom nomNEBE, tNEBE.prenom prenomNEBE,
tNEBE.salaire, tJU.nom nomJU,
tJU.prenom prenomJU,tJU.localite
FROM empNE_et_BE tNEBE, EmpJU tJU
WHERE tNEBE.salaire = tJU.salaire ;
--Vérification
ES52>select * from MemeSalaireNE_BE_JU;
NOMNEBE
PRENOMNEBE
SALAIRE
NOMJU
PRENOMJU
LOCALITE
---------------------------------------------------------------------LeMoyen
Bernard
6000
Muller
Alfred
Les Bois
LeBiennois Fritz
9000
Dutronc
Alain
Porrentruy
LeRiche
Jean
15000
Lachat
Pierre
Courgenay
Courtoisie de Emmanuel Coquery,
7