la page du cours
On s'intéresse dans ces exercices (indépendants les uns des autres, et à faire
dans un ordre quelconque) à gérer
certains des "problèmes bases de données" rencontrés par les applications.
Ces problèmes se rencontrent en gros au moment de la conception de la base.
Pour ceux d'entre vous qui choisiront le projet dans ce cours, prévoyez de les
faire avant la deuxième séance de projet.
1 Contraintes d'intégrité
La base ne doit pas contenir de valeurs absurdes ou incohérentes
entre elles. Oracle propose un mécanisme de gestion de
contraintes. Une contrainte est définie par l'utilisateur puis
assurée par le serveur lors des mises à jour.
create table t (
a integer primary key,
b char(20),
c integer not null,
foreign key (b) references t2(c),
check (a+c>7)
);
On considère ici une application naturelle simple de Bibliothèque
dont le schéma est le suivant, avec le sens intuitif pour les
attributs (les dates sont ismplement des entiers).
Livre : titre, auteur, nb_ex_disponibles, nb_ex_total
Client : nom, age , adresse
Emprunt : nom_client, titre, date_retour
-
Le nombre d'exemplaires est toujours défini.
- Le nombre d'exemplaires total d'un livre est toujours supérieur
à zéro.
- Le champ nom de la table Client est une clé
primaire.
- Assurer que le client qui emprunte un livre existe dans la
base en tant que client.
-
Concevez et effectuez un jeu de tests pertinent pour tester vos
contraintes.
2 Confidentialité
N'importe qui ne doit pas pouvoir faire n'importe quoi sur n'importe
quelles données. Oracle nous fournit des outils pour gérer ce
problème comme suit.
Un utilisateur qui crée une table ou une vue a tous les droits sur
celle-ci. Les autres utilisateurs (à l'exception de l'administrateur
de la base) n'ont aucun droit.
Le créateur peut accorder des droits aux autres utilisateurs au moyen
de la commande GRANT; et les supprimer au moyen de la commande REVOKE.
grant privilege
on table
to user
[with grant option] : transitivite
revoke privilege
on table
from user
privileges, ex : select, insert, update, delete
table : train ou waller.train si necessaire
Dans cet exercice, on accorde et retire des droits sur nos tables et
celles d'autres à d'autres utilisateurs Oracle (ex : votre
voisin).
Après chaque question, vous vérifierez (comment ?) que l'ordre
demandé a bien eu l'effet souhaité.
-
Autorisez votre voisin à insérer des n-uplets dans votre
table Voyage.
- Autorisez votre voisin à supprimer des n-uplets dans votre
table Voyage. Autorisez cette opération à tout le monde.
- Supprimez ce droit à tout le monde.
- Autorisez votre voisin à mettre à jour la colonne Destination de
la table Voyage. Permettez lui de donner ce droit à d'autres
utilisateurs. Demandez-lui de le faire.
- Autorisez maintenant à votre voisin la lecture, et rien
d'autre, sur votre table Voyage.
3 Les vues
Les vues servent en particulier à donner un nom à une requête
éventuellement complexe (pour utilisateur non expert SQL), à
restreindre les droits sur une table (colonnes et n-uplets non
sélectionnés inaccessibles), et à assurer une indépendance des
niveaux (entre les tables concrètes et les applications les
utilisant). Une vue peut être matérialisée ou recalculée à
chaque accès.1
create view Paris as
select *
from Voyage
where destination like 'Paris*'
On souhaite développer une fonctionnalité qui permet d'envoyer des
lettres de rappel pour les clients retardataires, dans le cadre du
schéma ci-dessus.
-
Écrire la requête qui liste le nom et l'adresse des
retardataires
-
Écrire (sur papier dans un premier temps) un programme Java qui
prend en paramètre la date courante et envoie une lettre de rappel
pour chaque retardataire.
-
Le concepteur de l'application bibliothèque a décidé de
réorganiser les informations stockées dans la base de la
manière suivante :
Livre : titre, auteur, nb_ex_disponibles, nb_ex_total
Emprunt : nom_client, age, adresse, titre, date-retour
-
Quelles sont les conséquences de ce changement sur votre
programme d'application ?
-
Proposex une solution qui protège les programmes d'application
contre une éventuelle réorganisation des informations stockées
dans la base. Expliquez.
4 Dictionnaire de données et ``tables système''
Il s'agit ici d'un problème BD géré par le SGBD, celui de l'"indépendance des
niveaux". Le dictionnaire de données fait en effet le lien entre les niveaux
logique et physique (mais cet aspect concerne peu le programmeur d'application).
Son deuxième rôle est de stocker certaines informations.
Oracle stocke les informations concernant utilisateurs, tables,
etc. dans... des tables realtionnelles ! Explorons-en un peu le
principe.
-
Tables contenant la description des tables (colonnes) auxquelles vous
avez accès :
ALL_TABLES(owner, table_name,...)
ALL_TAB_COLUMNS(owner,table_name,column_name,data_type,nullable,...)
- Tables contenant la description des tables (colonnes) que vous
avez créées:
USER_TABLES(table_name,...)
USER_TAB_COLUMNS(table_name,column_name,data_type,nullable,...)
- Table contenant tous les utilisateurs de la base mde données
:
ALL_USERS(user_name, user_id,created)
- Tables contenant toutes les tables (colonnes) sur lesquelles vous
avez des privilèges :
TABLE_PRIVILEGES(grantee,owner,table_name,grantor,select_priv,
insert_priv, delete_priv, update_priv, alter_priv, created,...)
COLUMN_PRIVILEGES(grantee, owner, table_name, column_name, grantor,
update_priv, created,...)
- Tables contenant toutes les tables (colonnes) systèmes :
DICTIONNARY(table_name, comments)
DICT_COLUMNS(table_name,column_name,comments)
-
Donnez le nom des tables que vous avez créées.
- Donnez le nom et le type des colonnes que vous avez créées
ainsi que le nom des tables auxquelles elles appartiennent.
- Donnez le nom des tables auxquelles vous avez accès et leur
propriétaire.
- Donnez le nom et le propriétaire de toutes les tables
auxquelles vous avez accès mais que vous n'avez pas créées.
- En dehors des tables systèmes, quelles sont les tables sur
lesquelles vous avez des privilèges.
- Parcourez la table DICTIONARY et pour les tables dont vous ne
voyez pas l'utilité, allez lire le commentaire associé.
5 Redirection des entrées/sorties d'un processus client en mode
interactif
Pas du tout un probème BD, juste
un petit exercice pour confirmer certains concepts autour de la
persistance...
On considère une table T(A,B : char) contenant
2 n-uplets ab et ac. Tout ordre dessus est
autorisé à tout utilisateur. On considère la séquence
d'actions suivantes où on suppose que tout se déroule
correctement.
-
l'utilisateur Unix toto crée et sauvegarde le fichier
unix f.sql contenant les ordres insert cd, select *, insert
fg;
- l'utilisateur Oracle toto éxécute la commande Sql*Plus
spool g;
- l'utilisateur Oracle toto éxécute la commande Sql*Plus
start f;
- l'utilisateur Unix toto modifie et sauvegarde le fichier
Unix f.sql en remplaçant l'ordre insert cd par insert gh;
- l'utilisateur Unix toto détruit le fichier Unix f.sql;
- l'utilisateur Oracle toto éxécute la commande Sql*Plus
spool off.
Donnez immédiatement après chaque action :
-
l'état de la base,
- l'état du fichier d'entrée,
- l'état du fichier de sortie.
- 1
- Sous Oracle (et en recherche) ily a quelques
subtilités lors par exemple d'une insertion par un utilisateur
extérieur de la vue.
©S. Abiteboul, E. Waller, B. Amann