TME 9 Vues

Les systèmes de gestion de bases de données relationnelles offrent un grand degré d'indépendance logique entre les données et les programmes d'application. Chaque utilisateur peut avoir sa description particulière de la partie de la base qu'il utilise. Cette fonction est assurée par la notion de Vue. Une vue est un ensemble de relations déduites d'une base de données, par composition des relations de la base, c'est à dire comme le résultat d'une requête d'interrogation. La déclaration d'une vue s'effectue par la commande suivante :

CREATE OR REPLACE VIEW <nom de vue> [(liste d'attribut)]
AS <requête SQL> ;

L'exemple utilisé lors de ce TP est celui d'une base de données BIBLIOTHEQUE gérant

• des lecteurs,
• des livres identifiés par un numéro et appartenant à une certaine catégorie d'ouvrages (roman policier, aventure, …) et
• les prêts de livres.

Nous travaillerons dans ce TP sur les trois tables suivantes qui permettent de décrire livres, lecteurs et les prêts :

• LIVRE (COTE, TITRE, CATEGORIE, AUTEUR)
• LECTEUR ( NOLECTEUR, NOM, ADRESSE)
• PRET (COTE, NOLECTEUR, DATEEMPRUNT, DATELIMITE, RENDU)

La clé primaire de chaque table est soulignée. Pour la table PRET, COTE,NOLECTEUR est une clé ; COTE et NOLECTEUR référencent LIVRE et LECTEUR respectivement.

Télécharger l'archive tme_vues_2020.zip et la décompresser dans votre dossier perso.

Lancer SQLWorkbench et se connecter à Oracle en suivant ces indications : Oracle avec SQLWorkbench

Dans un onglet (New Tab), ouvrir le fichier tme_vues.sql

Exécuter les commandes :

    @tme_vues_schema
    @tme_vues_donnees

Pour vérifier que votre base a été peuplée, exécuter les requêtes suivantes :

SELECT COUNT(*) AS Num_lecteurs FROM lecteur; // retourne 14
SELECT COUNT(*) AS Num_livres FROM livre; // retourne 712
SELECT COUNT(*) AS Num_prets FROM pret; // retourne 21

Entrez les commandes SQL permettant de créer les vues suivantes :

1. Les romans policiers :

    POLICIER (COTE, TITRE, AUTEUR)

   La vue contient 185 livres

2. Les lecteurs avec les catégories des livres qu'ils ont empruntés (rendu ou pas) :

    LECTEUR_CATEGORIE (NOLECTEUR, CATEGORIE) 

   La vue contient 21 nuplets (ou 9 nuplets si la clause SELECT a un DISTINCT)

3. Les lecteurs de romans policier :

    LECTEUR_POLICIER (NOLECTEUR, NOM, ADRESSE, COTE, DATEEMPRUNT, DATELIMITE) 

   La vue contient 18 nuplets (faisant référence à 6 lecteurs distincts)

4. Les livres qui n'ont pas été rendu à temps en supposant que nous sommes le premier janvier 2015 (la date s'écrit '01/01/2015').

    DEPASSEMENTS (NOLECTEUR, COTE, DATELIMITE) 

   La vue contient 4 dépassements

5. Une relation donnant pour chaque lecteur le nombre de ses emprunts en cours :

    STATISTIQUES (NOLECTEUR, NBLIVRES) 

   La vue contient 3 nuplets

Un utilisateur n'ayant que le droit d'interroger les vues précédentes, pose les questions suivantes :

1. Nom des lecteurs ayant emprunté des romans policiers à la date du 6 janvier 2016. (1 nuplet)
2. Noms des lecteurs ayant emprunté au moins deux romans policiers de 'Maxime Chattam' le même jour. (2 nuplets)
3. La liste des lecteurs de policier qui ont lu tous les romans policier lus par 'Gaspard Dupont'. (3 nuplets)
4. La liste des lecteurs de romans policiers qui n'ont jamais lu des romans écrits par 'Moore Viviane'. (6 nuplets)
5. La liste des lecteurs qui ont lu tous les romans policier de 'Anne Perry' (1 nuplet)
6. La liste des noms des lecteurs de romans policiers qui ont plus de deux livres en retard, la date limite ainsi que les noms de ces livres.(1 nuplet)
7. Exprimer les requêtes 1, 2 et 3 telle qu'elles devraient être posées sur les relations de base LECTEUR, LIVRE et PRET.

Il est parfois souhaitable de ne pas donner un accès direct aux tables d'une base de données. Lorsque l'utilisateur souhaite mettre des données à jour, il est possible d'utiliser certaines vues pour mettre à jour de manière indirecte les tables.

UPDATE <TABLE/vue>
SET attribut=valeur, attribut2=valeur, ...
WHERE <condition SQL>

1. Mettre à jour le roman policier écrit par 'Wilkie Collins' dont la cote est 'COLL05' en lui donnant le titre 'La dame en noir'

1. Essayez d'insérer un nouveau roman policier au moyen de la commande SQL INSERT et en utilisant seulement la vue POLICIER. Qu'observez vous et expliquez le problème.

INSERT INTO <nom de la TABLE/vue> VALUES (n1, n2, ...)

On verra une solution avec des triggers dans le prochain TME.

Si vous utilisez Oracle l'URL de connexion est :

   jdbc:oracle:thin:@localhost:1521:oracle

Si vous utilisez H2, l'URL de connexion est :

   jdbc:h2:tcp://localhost:9093/~/tmevues