doc

MSBM
2002-2003
TP 4 : Introduction à la manipulation d'une base de
données à partir du SGBD ORACLE
Pour l'instant, nous avons créé, mis à jour et interrogé des bases de données gérées par le SGBD ACCESS.
Ce TP a pour but de mettre en application ce que nous avons déjà vu au cours des TD précédents, c'est à dire la
manipulation d'une base de données grâce au langage SQL (Structured Query Langage).
Nous verrons ainsi comment :
 on crée une base au moyen du langage de définition des données
 on met à jour les données (insertion, modification, suppression) avec le langage de définition des
données
 on interroge les données avec le langage de manipulation des données
 on met en place des contrôles portant sur l'accés aux données grâce au langage de contrôle des données
Nous travaillerons sur le SGBD ORACLE et nous pourrons ainsi voir que SQL compris par le SGBD
ORACLE est plus proche de la norme AINSI que ne l'est celui utilisé dans le SGBD ACCESS.
Pour pouvoir effectuer toutes ces opérations, nous allons taper les instructions au clavier. La saisie se fera dans
sqlplus, logiciel qui présente une interface assez peu conviviale. Nous verrons ainsi l'intérêt de saisir les
instructions dans un fichier texte au moyen d'un éditeur (WordPad, de préférence lorsque l'on se trouve sous le
système d'exploitation Windows). Pour lancer sqlplus, il suffit de double cliquer sur l'icône présente sur le
bureau Windows. Il vous sera indiqué en TP le login ainsi que le mot de passe nécessaires pour accéder au
SGBD.
Tout au long de ce TP, nous nous appuierons sur un élément du SGBD qui a une très grande importance et au
travers duquel nous pourrons vérifier de façon aisée et rapide si les instructions que nous avons soumises ont
bien été exécutées par le SGBD. Cet élément présent au niveau de tout SGBD, quel qu'il soit, est le dictionnaire
des données.
I Dictionnaire des données
Le dictionnaire de données est un ensemble de tables dans lesquelles sont stockées les descriptions des objets
de la base. Les tables de ce dictionnaire peuvent être consultées au moyen du langage SQL.
Des vues de ces tables permettent à l'utilisateur de voir les objets qui lui appartiennent ou sur lesquels il a des
droits. L'administrateur a pour sa part accés à toutes les vues.
Quelques vues et tables du dictionnaire de données :
USER_TABLES: tables et vues créées par l'utilisateur
USER_CATALOG (ou CAT): tables et vues sur lesquelles l'utilisateur a des droits à
l'exception des tables et vues du dictionnaire de données
USER_TAB_COLUMNS (ou COLS): colonne de chaque table ou vue créée par l'utilisateur
courant
USER_CONSTRAINTS: définition des contraintes pour les tables des utilisateurs
USER_CONS_COLUMNS: colonnes qui interviennent dans les définitions des contraintes
Exemple de consultation du dictionnaire de données :
Si vous voulez lister les caractéristiques des tables que vous avez créées en tant qu'utilisateur de la base (ayant
les droits de création de tables, bien entendu), vous poserez la requête suivante au SGBD :
SELECT * FROM USER_TABLES;
Question 1 : quels sont les attributs de la vue USER_TABLES ?
MSBM
2002-2003
Question 2 : combien de tables sont présentes ?
Si vous souhaitez lister uniquement les noms des tables que vous avez créées, vous poserez la requête suivante
SELECT TABLE_NAME FROM USER_TABLES;
Question 3 : quels sont les noms des tables qui ont été créées ?
Question 4 : comment procèderiez-vous pour obtenir la liste des contraintes que vous avez imposées sur les
tables créées ?
Question 5 : à votre avis, comment obtiendrez-vous la liste des noms des contraintes que vous avez imposées
sur les tables créées ?
II Description des tables de la base de données
Pour obtenir la description en terme d'attributs de chacune des tables, vous pouvez utiliser la commande sqlplus
suivante :
DESCRIBE nom_table
ou bien
DESC nom_table
Question 6 : donner le schéma relationnel de la relation COMMANDES ?
III Création d'un nouveau schéma de base de données
Nous allons maintenant manipuler la base de données sur les sociétés pharmaceutiques sur laquelle nous avons
travaillé en TD (TD 5).
Pour créer les différentes tables de la base de données, nous nous servons d'instructions dont la syntaxe est la
suivante :
CREATE TABLE nom_table
(
att1 type_att1,
att2 type_att2,
..... ,
atti type_atti,
..... ,
attn val_attn
);
Il est également possible de préciser les contraintes que l'on veut imposer. Reportez-vous au texte du TD 5 pour
la syntaxe utilisée dans les divers cas de contraintes.
Le script de création a été saisi. Vous le trouverez dans le répertoire indiqué au cours de la séance de TP.
Editez ce script. Copiez-en le contenu et collez-le dans sqlplus.
Question 7 : quel type d'affichage observez- vous dans l'inferface sqlplus ?
Question 8 : combien de tables sont désormais présentes ?
Question 9 : lister le nom et la date de dernière modification de chacune des nouvelles tables apparues.
Question 10 : donner la description de la relation SOIGNE.
MSBM
Question 11 : donner les noms ainsi que le type des contraintes posées sur ces nouvelles tables.
2002-2003
IV Insertion de données dans la base de données
Question 12 : quelle requête SQL poseriez-vous pour connaître le contenu de la table EMPLOYE ? Que
constatez-vous ?
Pour insérer des données dans une table
INSERT INTO nom_table VALUES (val_att1, val_att2, ......, val_atti, ......., val_attn);
Un script d'insertion d'un certain nombre de données a été saisi. Il vous sera indiqué durant la séance où vous
pourrez le trouver sur le disque dur de l'ordinateur. Procédez comme précédemment par copier/coller dans
l'interface sqlplus.
Question 13 : quel type d'affichage observez- vous dans l'inferface sqlplus ?
Question 14 : quel est désormais le contenu de la table EMPLOYE ?
Question 15 : quelle requête poseriez-vous pour connaître le nombre d'employés qui sont répertoriées dans la
base de données ?
Question 16 : insérez l'employé Toto qui est né le 09/12/66, a été embauché 01/01/01 par la société Sanofi
Synthélabo pour un salaire de 850 euros et avec pour responsable direct le PDG de la société.
Question 17 : insérez le médicament Solutricine qui a pour principe actif la tyrothricine et est fabriqué par
Aventis. La solutricine est distribuée sous forme de boîte de 72 comprimés à sucer. La tyrothricine soigne les
maux de gorge peu intenses mais également les aphtes (petites plaies de la bouche).
V Mise à jour des données
Pour faire des mises à jour des données, il faut écrire des instructions du type :
UPDATE nom_table SET atti = new_val WHERE attj = une_valeur;
Question 18 : les employés qui ne sont pas des PDG sont augmentés chacun de 10%. Ecrivez l'instruction
permet de faire cette modification des données de la base.
VI Suppression de données
La suppression de données se fait au moyen d'instructions dont la syntaxe est :
DELETE FROM nom_table WHERE attj = une_valeur;
Question 19 : supprimez l'employé qui est né le 09/12/66 et a été embauché 01/01/01 par la société Sanofi
Synthélabo.
VI Mise à jour du schéma
Il est possible de modifier les schémas relationnels que l'on a créés. Ainsi il est possible d'ajouter de nouvelles
tables, d'ajouter des attributs ou d'en modifier la définition, d'ajouter des contraintes ou d'en modifier, d'ajouter
des index, etc.
MSBM
2002-2003
Pour ajouter de nouvelles tables, il suffit d'utiliser des instructions suivant la syntaxe vue en III.
Pour supprimer une table, la syntaxe est :
DROP TABLE nom_table;
La syntaxe générale pour opérer des modifications sur le schéma relationnel est la suivante :
ALTER TABLE nom_table ACTION description;
avec ACTION : ADD ou MODIFY ou RENAME OR DROP
Question 20 : ajoutez les contraintes indiquées dans le TD 5 c'est-à-dire :
 Les salaires des employés sont supérieur ou égal au SMIC (on admet que tout employé travaille à temps
complet et donc touche au moins le SMIC).
 Les secteurs auxquels appartiennent les ARCs sont pris parmi l'ensemble suivant : cancérologie,
rhumatologie, cardiologie, dermatologie.
 Un employé doit avoir plus de 18 ans et moins de 65 ans.
Question 21 : Ajoutez également la contrainte qui permet d'imposer que le codeSociete de la table ARC fait
référence à une société de la table SOCIETE.
Question 22 : certaines contraintes n'ont pas été nommées. Cela pose des problèmes de manipulation des
contraintes et de compréhension des messages d'erreur produits par le SGBD, en particulier.
Essayez de renommer une des contraintes à laquelle il n'a pas été attribué un nom autre que celui donné en
interne par le SGBD. Par exemple, renommer la contrainte de clé primaire sur la table société.
 Pour cela, il vous faut connaître le nom que le système a affecté à cette contrainte. Comment faites-vous
pour récupérer ce nom ?
 Essayez ensuite de supprimer la contrainte. Que constatez-vous ?
 Désactivez les contraintes qui posent problème. La syntaxe de désactivation des contraintes est :
ACTIVATION :
DESACTIVATION :

ALTER TABLE nom_table ENABLE CONSTRAINT nom_contrainte;
ALTER TABLE nom_table DISABLE CONSTRAINT nom_contrainte;
Modifiez la table SOCIETE de façon à recréer la clé primaire mais cette fois-ci en lui affectant un nom.
VII Interrogation de données
Quesrion 23 : Nous avons vu dans le TD 5 quelques requêtes que l'on pourrait poser à la base.
Essayez de poser les requêtes demandées :
1. Donner les noms et matricules des employés de la société de code Siret 5.
2. Donner les noms et matricules des employés de la société Bayer Pharma.
3. Donner les noms et matricules des employés des sociétés Bayer Pharma, Pierre Fabre, Biogalénique et
Sanofi Synthelabo.
4. Lister les ARCs (noms et matricules) spécialisés en cardiologie.
5. Lister les ARCs (noms et matricules) qui ont été embauchés entre 1990 et 1992.
6. Donner la liste des pathologies triées par ordre alphabétique.
7. Donner les sociétés dont le siège social se trouve à Paris.
8. Donner le nombre de médicaments décrits dans la base de données.
9. Donner pour chaque société le nombre de molécules actives mises au point.
10. Donner les sociétés qui ont mis au point au moins deux molécules actives.
11. Quelle est la société qui a le plus de principes actifs à son actif ?
MSBM
2002-2003
12. Quelles sont les molécules actives qui ont un rôle thérapeutique dans l'hypertension artérielle ?
13. Combien de molécules actives ont un rôle dans l'hypertension artérielle?
14. Quels sont les médicaments qui sont impliqués dans le traitement de l'hypertension artérielle ?
15. Quelles sont les molécules qui ont un rôle thérapeutique dans au moins deux pathologies ?
16. Quelles sont les molécules qui agissent à la fois sur la grippe et sur le syndrome de Kawasaki ?
17. Quelles sont les pathologies qui peuvent être soulagées par plusieurs principes actifs ?
18. Quelles sont les molecules qui n'agissent que sur la grippe ?
19. Quelles sont les molécules qui agissent sur la grippe mais qui n'agissent pas sur l'hypertension
artérielle ?
20. Quels sont les principes actifs qui agissent dans toutes les pathologies ?
Question 24 : Donnez la moyenne, le minimum et le maximum des salaires des PDG des sociétés.
VIII Droits d'accés
Oracle permet à plusieurs utilisateurs de travailler sur la même base de données en toute sécurité. Deux
commandes sont à ce titre particulièrement importantes: GRANT et REVOKE et permettent de
définir les droits de chaque utilisateur sur les objets de la base.
Tout utilisateur accède à la base à l'aide de son nom utilisateur et de son mot de passe. C'est le nom utilisateur
qui permet de déterminer les droits d'accès aux objets de la base de données.
Deux utilisateurs dess1 et dess2 ont été créés par le DBA et sont autorisés à se connecter à la base Oracle. Ils
ont aussi les privilèges de créer des objets de schéma de base de données utilisateur (tables, vues, contraintes
etc).
Depuis le début de la séance, vous avez travaillé sous le nom d'utilisateur dess1 et vous avez donc été
<<en concurrence>> avec vous-même.
Nous allons vérifier que le SGBD gère la concurrence d'accès à des objets de la base entre plusieurs utlisateurs
différents.
Tout utilisateur qui crée des objets est propriétaire de ces objets (table\_name, owner de USER_tables dans le
dictionnaire des données).
Le créateur d'un objet peut décider de donner (ou de supprimer) certains droits d'accès à cet objet à tout autre
utilisateur de sa connaissance.
o L'ordre GRANT
GRANT privilege ON table/vue TO utilisateur [WITH GRANT OPTION] ;
Cet ordre permet de ``donner'' le privilège concerné sur la table ou la vue à l'utilisateur.
Exemple : dess1 a créé la table SOCIETE et veut autoriser dess2 à lire cette table. Il passe alors l'ordre :
GRANT SELECT ON SOCIETE TO dess2;
Les privilèges qui peuvent être donnés sont les suivants:
SELECT: droit de lecture, INSERT: droit d'insertion de lignes, UPDATE: droit de modification de lignes,
DELETE: droit de suppression de lignes, ALTER: droit de modification de la définition de la table, INDEX:
droit de création d'index, ALL: tous les droits ci-dessus.
Un utilisateur ayant reçu un privilège avec la mention facultative WITH GRANT OPTION peut les transmettre
à son tour à un autre utilisateur.
Exemples d'application :



dess1 (dess2) donne les droits de lecture de ``sa'' table concernant les sociétés à l'utilisateur dess2
(dess1)
dess1 (dess2) donne les droits de modification de ``sa'' table EMPLOYE à l'utilisateur dess2 (dess1)
Vérifier que les privilèges ont été bien accordés
MSBM
2002-2003
 Testez vos nouveaux droits (les objets que vous interrogerez et dont vous n'êtes pas propriétaire sont
désignés par leur nom complet nompropriétaire.nomobjet).
o L'ordre REVOKE
Un utilisateur ayant accordé un privilège peut le reprendre à tout moment à l'aide de l'ordre REVOKE.
REVOKE privilege ON table/vue FROM utilisateur ;
Exemples d'application :


Enlever les privilèges précédemment accordés
Vérifier que les privilèges ont bien été supprimés
IX Gestion des accès concurrents
Une transaction (ensemble d'ordres SQL) est atomique c'est-à-dire qu'elle ne peut se terminer que par un succés
(elle est alors validée) ou un échec (tous ses effets sont alors détruits).
En conséquence, en contexte multi-utilisateurs, les modifications effectuées par une transaction réalisée par un
utilisateur ne sont connues des autres utilisateurs que lorsque la transaction a été confirmée par un
COMMIT.
Oracle gère automatiquement les accès concurrents. Si une transaction est en train de modifier les lignes d'une
table, les autres transactions peuvent modifier les données telles qu'elles étaient avant ces dernières
modifications (pas de temps d'attente pour la lecture).
Pour rester ``simple'' nous dirons que toute transaction pose des verrous sur les objets qu'elle manipule et que
deux grands types de verrous existent :


en lecture (verrou passant plusieurs lectures simultanées peuvent avoir lieu)
en écriture (verrou bloquant la première écriture bloque les autres jusqu'à ce que le verrou soit relaché)
Commandes qui provoquent un blocage implicite sur les tables et les lignes impliquées : DELETE,
INSERT, UPDATE, ALTER TABLE, ...
Exemples d'application :

Faites des sélections sur les mêmes lignes des mêmes tables avec deux noms utilisateurs différents
Par exemple, dess1 et dess2 réalisent la même requête : ``Donner les noms des employés de la société Bayer
Pharma ''.
 Réessayez le même exercice mais avec des commandes provoquant des blocages
Par exemple, dess1 modifie la table SOCIETE : ``Modifiez le nom de la société Bayer Pharma en Bayer''. Puis
dess1 et dess2 réalisent la même requête : ```Donnez le nom de la société dirigée par Werner Wenning ''. Que
constatez-vous ?
Tester avec COMMIT et ROLLBACK.

Etreinte mortelle (DEADLOCK) :
 dess1 fait un UPDATE sur le tuple i de la table SOCIETE
 dess2 fait un UPDATE sur le tuple j de la table MEDICAMENT
 dess2 fait un UPDATE sur le tuple j de la table SOCIETE
 dess1 fait un UPDATE sur le tuple i de la table MEDICAMENT
Que constatez-vous. Quelle est la solution ? Quelles sont les opérations qui ont été effectivement effectuées sur
les tables concernées ?
MSBM
2002-2003