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.¹

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.