Les Systèmes de Gestion de Bases de Données (SGBD)

Les vues

2006-2007

B.Sadeg Le Havre

Bibliographie S. Abiteboul, R. Hull, V. Vianu, Foundations of Databases, Addison-

Wesley J.C. Date, A Guide to the SQL Standard, Addison-Wesley

J.C. Date, A Guide to DB2, Addison-Wesley R. Elmasri, S. Navathe, Conception et architecture des bases de

données, 4ème ed., publié par Pearson Education. H. Garcia-Molina, J. Ullman and J. Widom, Implementation of

Database Systems, Prentice Hall, 1999. G. GARDARIN, Bases de Données, Eyrolles, 6ème tirage, 2005. R. Ramakrishnan et J. Gehrke DATABASE MANAGEMENT

SYSTEMS, MacGraw Hill M. SCHOLL, B. AMANN, P. RIGAUX, V. CHRISTOPHIDES, D.

VODISLAV, Polycopié de Bases de Données, librairie des Arts et Métiers.

Ullman J.D. and Widom J. A First Course in Database Systems, Prentice Hall, 1997

Ullman J.D. Principles of Database and Knowledge-Base Systems, 2 volumes, Computer Science Press

SQL : LES VUES

2006-2007

B.Sadeg Le Havre

Avantages Facilité d ’accès à la base sécurité des données

Genèse : une BD normalisée = BD éclatée en plusieurs tables => mauvaise perception de la base par les utilisateurs => beaucoup de jointures sont nécessaires pour obtenir des

résultats exploitables. De plus, les tables contiennent souvent des infos supplémentaires (dates de création, auteur, ..) inutiles pour l ’utilisateur.

=> Le concept de vue : permet d ’avoir une perception simplifiée de la BD

Les vues : reconstituent une image globale de la base, plus simple et claire.

Les vues : utiles pour implanter le concept de schéma externe.

2006-2007

B.Sadeg Le Havre

Sécurité des données La notion de vue liée au concept de privilège d ’accès.

Permet une grande souplesse d ’accès aux informations. Un groupe d ’utilisateurs -> une vue sur laquelle les

membres du groupe reçoivent des droits spécifiques.

Caractère dynamique d ’une vue Une vue est une table virtuelle. Elle n ’a pas d ’existence

propre. En général, une vue = un nom + l’ordre SQL associé,

stockés dans le DD. Lors de la création d ’une vue, aucun ordre SQL n ’est

exécuté. Par définition, la vue est dynamique. Ce n ’est pas une table

temporaire que l ’on crée, qui, elle, persiste après suppression de la table de base. Une vue ne peut pas exister si la (ou les) table(s) dont elle est issue sont supprimées (Toute modif. sur les tables y est répercutée immédiatement).

2006-2007

B.Sadeg Le Havre

Création d ’une vue CREATE VIEW nom_vue [ (liste_de_colonnes) ] AS table_expression [WITH {CASCADE | LOCAL | CHECK OPTION}];

où table_expression est la requête SQL associée (SELECT qui peut comporter UNION, INTERSECT, …) sauf ORDER BY.

Si la liste_colonnes n ’est absente, la vue hérite des noms de colonnes de la table associée. Parfois la liste des colonnes est obligatoire. Exemples :

1. CREATE VIEW client_paris (ref_cl, nom_cl) AS select clt_num,

clt_nom FROM clients WHERE clt_loc = ‘PARIS’; 2. CREATE VIEW chif_affaire (ref_client, chiffre_a)

AS SELECT cmd_clt, sum(lcd_qte * lcd_pu)

FROM commandes WHERE cmd_num = lcd_cmd

GROUP BY cmd_clt;

2006-2007

B.Sadeg Le Havre

Suppression d ’une vue

DROP VIEW nom_vue [RESTRICT | CASCADE] où RESTRICT : si cette vue intervient dans la définition d ’une

autre vue, la commande est rejetée. CASCADE : spécifie que la vue sera supprimée ainsi que

toutes les vues qui la référencent.

Extraction de données à partir d ’une vue : idem qu’à partir d ’une table.

Select * from clients_paris where nom_cl like ‘ D% ’; => le moteur SQL convertit cette requête en une requête sur la

table de base.

2006-2007

B.Sadeg Le Havre

Beaucoup de vues ne sont pas modifiables :

présence d ’une fonction de groupe et d ’une group by :

create view nb_ventes as select count(*) from commandes;

présence de la clause DISTINCT

create view i_c

as select distinct article_fourn, art_coul from articles;

présence d ’une fonction arithmétique :

create view articles_fb (num, prix_fb)

as select art_num, art_pv/0.154 from articles;

select * from articles_fb;

Modification d ’une vue

2006-2007

B.Sadeg Le Havre

l ’expression SQL associée à la vue doit être un simple SELECT : pas de jointure, ni intersect, ni union, ni distinct

la clause FROM ne peut contenir qu’une table ou une vue, elles-mêmes modifiables

pas de DISTINCT dans la clause SELECT si le SELECT contient un SELECT imbriqué, celui-ci

ne peut pas faire référence à la même table que le SELECT principal

pas de GROUP BY, ni de HAVING

Règles à respecter pour qu’une vue soit modifiable (norme SQL-92, mais différentes selon les sgbd):

2006-2007

B.Sadeg Le Havre

pour une vue modifiable uniquement. Exemple :

create view cl2_paris as select clt_num, clt_nom, clt_loc, clt_pays

from clienst where clt_loc=‘ PARIS ’; Cette une vue est modifiable.

Mais considérons l ’insertion clt_loc, clt_pays :

INSERT INTO cl2_paris (clt_num, clt_nom, clt_loc, clt_pays)

VALUES (‘ C11111 ’, ‘ TOTO ’, ‘ LILLE ’, ‘ F ’); Le moteur SQL l ’accepte et l ’exécute, mais le nouveau tuple

n ’apparaît pas dans la vue (car pas de contrôle sur la définition de la vue).

=> solution : clause WITH CHECK OPTION.

Create view …….. where clt_loc=‘PARIS’ with check option;

=> Toute insertion dans la vue est contrôlée par rapport à la définition.

Clause WITH CHECK OPTION

2006-2007

B.Sadeg Le Havre

=> pour la mise en œuvre de schémas externes (SE).

Un SE est un ensemble de vues au travers desquelles l ’utilisateur voit la base.

Avantages : – simplification et personnalisation de la BD,

– confidentialité,

– indépendance entre applications et schémas logiques,

– introduction de contraintes d ’intégrité.

Utilisation des vues

2006-2007

B.Sadeg Le Havre

Simplification : chaque groupe d ’utilisateurs voit des tables simples (pas besoin de jointures complexes)

confidentialité : un groupe d ’utilisateurs peut se voir interdire des accès à des parties de la base (sans qu’il le sache) car il a accès seulement à certaines vues (pour effectuer des opérations sur lesquelles il a eu des droits également)

indépendance application/schémas conceptuels :

Exemple : on modifie (on ajoute un num. téléph. aux clients de paris

=> on modifie la table de base par ALTER, mais la vue cl_paris ne change pas.

2006-2007

B.Sadeg Le Havre

Introduction aux CI :

Exemple : insertion de nouvelles lignes de commandes:

create view lignes_de_commandes_validees

as select * from ligne_commande where lcd_qte > 0

and lcd_liv >= 0 and lcd_liv <= lcd_qte

and lcd_pu > 0.8 * (select art_pv from articles

where art_num=lcd_art)

and lcd_cmd IN (select cmd_num from commandes)

with check option; => cette vue est modifiable (elle concerne 3 tables mais pas

de jointures). => en passant par cette vue, on est sûr que seules les

commandes validées sont rentrées dans la table lig_cmd.

2006-2007

B.Sadeg Le Havre

On s ’arrange à ne donner l ’accès qu ’à la vue ci-dessus.

La clause WITH CHECK OPTION garantit que :– la quantité commandée est >0

– la quantité livrée est > 0 et bornée par la quantité commandée

– le prix_v unitaire > à 80% du prix catalogue (remise < 20%)

– l ’article commandé doit exister.

=> insertion d ’un tuple dans la table (à travers la vue) :

insert into lignes_commandes_validées

values (‘C94823’, ‘A1456’, 35, 0, 25, ‘940603’);

2006-2007

B.Sadeg Le Havre

Les triggers (déclencheurs)

2006-2007

B.Sadeg Le Havre

Egalement appelés Règles E-C-A ‘Evénement-Condition-Action’.– Evénement : un changement dans la BD,

Ex : ‘ Insert into TABLE1 … ’– Condition : un test comme dans la clause Where, si le trigger

peut être déclenché ou non

– Action : une ou plusieurs instructions SQL

Disponible dans Oracle et dans SQL-3 (pas dans SQL-2)

2006-2007

B.Sadeg Le Havre

A chaque insertion d ’un nouveau tuple dans la relation VENDRE, on s ’assure que la bière mentionnée se trouve aussi dans la relation BIERE.

Relations Vendre (bar, biere, prix), Biere (name)

CREATE OR REPLACE TRIGGER BiereTrigg

AFTER INSERT ON Vendre

FOR EACH ROW

WHEN (new.biere NOT IN (SELECT name FROM Biere))

BEGIN

INSERT INTO Biere(name) VALUES(:new.biere);

END;

.

Run; Rmq : on peut mettre AFTER, BEFORE ou INSTEAD (s ’il s ’agit d ’une

vue) Au lieu de INSERT, on peut mettre DELETE ou UPDATE OF <attributs> FOR EACH ROW : s ’il est omis, l ’action est exécuté une seule fois

effectuant tous les changements

Exemple

2006-2007

B.Sadeg Le Havre

Il y a deux variables spéciales : new et old, qui représentent la nouvelle et l ’ancienne valeur du tuple à changer,

Mais ‘old’ n ’a pas de sens pour une insertion et ‘new’ n ’a pas de sens pour une suppression

Dans un WHEN, on les utilise sans les faire précéder de ‘:’, mais dans une action, les 2 points sont nécessaires.