3 SQL (Vues, intégrité et droits)

a) Définition de la vue POLICIER

create view POLICIER

as select Cote, Titre, Auteur

from LIVRE

where Catégorie = « roman policier »;

Cette vue est un exemple d’une définition de schéma externe où le sous-ensemble de la base de données est défini comme un sous-ensemble au niveau des occurrences.

b) Définition de la vue LECTEUR-DE-POLICIER

create view LECTEUR-DE-POLICIER

as select Numéro, Nom, Adresse, Cote, DateEmprunt, DateRetour

from LIVRE L, LECTEUR E, PRET P

where L.Cote = P.Cote and E.Numéro = P.NumLecteur   and L.Catégorie = « roman policier »;

Cette définition de vue est un exemple de restructuration de l’information. L’utilisateur ne voit dans la vue qu’une seule relation alors qu’en réalité la base est composée de trois relations. Cette possibilité permet de simplifier le travail des utilisateurs en leur présentant les informations sous le format qui leur convient le mieux, en leur évitant par exemple la formulation de jointures lors d’interrogations de la vue.

c) Définition de la vue LECTEUR-INTERDIT

create view LECTEUR-INTERDIT(Numéro,Nom)

as select Numéro, Nom

from LECTEUR E, PRET P

where E.Numéro = P.NumLecteur

       and (DateEmprunt + 15) < DateSystème

       and DateRetour is NULL;

On suppose que le système permet d’obtenir la date du jour en utilisant le mot-clé « date-système ». La définition de la vue est dynamique, en ce sens que son contenu varie tous les jours automatiquement en fonction de la date. Cette définition de vue permet également de changer les noms des attributs pour s’adapter aux habitudes des utilisateurs.

d) Définition de la vue STATS-DES-PRETS

create view STATS-DES-PRETS(NumLecteur,NombrePrêts,Nom)

as select Numéro, count(*)

from LECTEUR E, PRET P

where E.Numéro = P.Numlecteur

       and DateEmprunt > « d »

group by NumLecteur;

L’attribut NombrePrêts est une information déduite dans la mesure où elle n’est pas stockée en tant que telle dans la base de données, mais calculée à partir des informations stockées.

Question 2 : Utilisation d’une vue

a) La question utilisateur est

select distinct Nom

from LECTEUR-DE-POLICIER

where DateEmprunt = « d »;

La même question posée sur les relations de la base serait:

select Nom

from LIVRE L, LECTEUR E, PRET P

where L.Cote = P.Cote

and E.Numéro=P.Numlecteur

and L.Catégorie = « roman policier »

and DateEmprunt = « d »;

Cette question est beaucoup plus complexe à formuler car elle comporte deux jointures, alors que la question sur la vue est très simple puisqu’elle ne comporte qu’une restriction. C’est l’un des avantages des vues : simplifier le travail des utilisateurs.

b) La question de l’utilisateur exprimée en SQL est la suivante:

select distinct Nom

from LECTEUR-DE-POLICIER E, POLICIER P

where E.Cote = P.Cote and Auteur = « SIMENON »

group by NumLecteur, DateEmprunt

having count (Numlivre)>3;

La question telle qu’elle aurait dû être posée sur la base de données est la suivante:

select distinct Nom

from LECTEUR E, LIVRE L, PRET P

where P.Cote=L.Cote and Auteur= »Simenon »            

and L.Catégorie= »roman policier »              

and P.NumLecteur=E.Numéro

group by NumLecteur, DateEmprunt

having count (Numlivre) > 3;

Question 3: Traitement d’une question portant sur une vue

L’algorithme de transformation d’une question posée sur une ou plusieurs vues consiste à « additionner » la question utilisateur et les questions définissant chacune des vues. Le traitement est itératif car une vue peut être construite sur d’autres vues.

Tant que la question comporte l’utilisation de vues

• rechercher dans la métabase les arbres de définition des vues
• construire l’arbre syntaxique de la question posée
• changer les noms des attributs de la vue dans les noms d’attributs correspondants s’il y a re-nomination des attributs dans la vue
• transformer les arbres en un seul en faisant coïncider le résultat d’une vue avec le nom de la vue dans la question
• simplifier éventuellement l’arbre en éliminant les opérations redondantes

ftq

Passer l’arbre transformé de la question à l’optimiseur de question

Application de l’algorithme sur l’exemple :

1) L’arbre de la question est le suivant :

2) Les arbres des deux vues sont les suivants :

3) Ajout des arbres en faisant coïncider le résultat d’une vue et le nom de la vue :

4) Dans la définition des vues il n’y a pas de renomination des attributs.

5) Simplification de l’arbre.

On utilise deux fois la relation LIVRE pour une jointure sur n°livre. Il y a également deux restrictions identiques avec le critère <catégorie = « roman policier »>. On ne garde qu’une fois chacune de ces opérations.

Question 4: Mise à jour à travers une vue

La mise à jour à travers les vues est interdite dans le cas général car elle pose différents problèmes :

(1)       Attributs non définis dans la vue: toute insertion d’un tuple dans la vue entraîne l’insertion d’un tuple dans la base ayant une valeur indéterminée pour les attributs non visibles dans la vue. Par exemple l’insertion d’un nouveau tuple dans la vue LECTEUR-INTERDIT implique l’insertion d’un tuple LECTEUR dont l’adresse est inconnue. Toute vue incluant dans sa définition une projection entraîne ce type de problème.

(2)       Risques d’incohérence quand une relation d’une vue est obtenue par jointure de plusieurs relations réelles. Par exemple, l’insertion d’un tuple dans LECTEUR-DE-POLICIER signifie-t-elle l’ajout d’un prêt pour un lecteur existant, ou l’insertion d’un prêt et d’un nouveau lecteur, ou encore l’insertion d’un prêt, d’un livre et d’un lecteur ? 

L’ambiguïté est la même pour les suppressions de tuples.  La répercussion d’une mise à jour dépend de la sémantique de la vue et de l’application. Elle ne peut pas être automatique. Il faudrait décrire pour chaque vue la sémantique des opérations de mises à jour.

(3)       Perte de signification de la mise à jour: la suppression, par exemple, du tuple (1347, 45) de la vue STATS-DES-PRETS, ne peut pas être répercutée au niveau des relations de la base. Cette perte de signification est caractéristique des vues construites en utilisant des agrégats et/ou des fonctions de calcul somme ou moyenne.

En général les SGBD interdisent les mises à jour à travers les vues. Certains les autorisent dans le cas où la sémantique des mises à jour peut être définie sans ambiguïté. C’est le cas si la définition de la vue n’utilise que l’opérateur de restriction d’une relation (ni jointure, ni projection, ni agrégat, ni fonction de calcul). 

Dans ce cas un tuple de la vue correspond à un seul tuple de la base. On peut autoriser également l’opérateur de projection si le SGBD gère les valeurs indéterminées.

Question 5: Vues concrètes ou clichés

Le temps de calcul et de stockage d’un cliché peut être important si le sous-ensemble de la base de données correspondant est volumineux. Pour réduire ce temps d’exécution, qui pour un cliché est répétitif, le système peut utiliser un mécanisme de mise à jour différentiel. Seuls les tuples insérés, supprimés ou modifiés dans la base depuis le dernier calcul sont à leur tour insérés, supprimés ou modifiés dans le cliché.

Il est nécessaire que le SGBD garde les tuples insérés ou supprimés des relations de la base dans une relation spéciale. Les suppressions de tuples posent un problème particulier dans le cas général : un tuple du cliché peut provenir de plusieurs tuples de la base et la suppression d’un seul de ces tuples ne doit pas entraîner la suppression du tuple du cliché. 

Ce problème est étudié dans [Kerhervé 86]. L’idée est d’associer à chaque tuple de la vue un compteur indiquant le nombre de raisons pour sa présence. Lors d’une suppression d’un tuple de la base le compteur correspondant est décrémenté. Quand le compteur devient nul, le tuple du cliché est supprimé.