340 likes | 463 Views
Programme. Introduction aux BD et aux SGBD Le modèle relationnel Le langage de requête SQL La conception d’une BD relationnelle Protection des informations Perspectives des BD. Protection des informations. Respect des règles de gestion des données ( contraintes d’intégrité )
E N D
Programme • Introduction aux BD et aux SGBD • Le modèle relationnel • Le langage de requête SQL • La conception d’une BD relationnelle • Protection des informations • Perspectives des BD
Protection des informations • Respect des règles de gestion des données (contraintes d’intégrité) • Définition de schémas externes (vues relationnelles) • Définition de droits d’accès en fonction des utilisateurs
Contraintes d’intégrité • Règles de gestion des données • Etat cohérent de la base • Vérifiées en permanence
Modèle relationnel : Unicité de la clé (num dans vins) CI référentielle (nvin vers num) Domaine : Année entre 1970 et 2000 Région : ‘ Bourgogne ’, ‘ Beaujolais ’, … Non nullité : Nom de cru de vin obligatoire Comportementale: Le degré d’un vin est supérieur à 7 Le degré d’un vin augmente Exemples de contraintes d’intégrité Schéma de la BD Vins Vins(num, cru, annee, degre) Producteurs(num, nom, prénom, region) Recoltes(nprod, nvin, quantité)
Les CIs dans les SGBD • Quand les déclarer ? • À la création du schéma de BD • Au cours de la vie de la BD • Comment les exprimer? • SQL • Extensions de SQL • Qu’offrent les SGBD ? • Peu de CIs en général • Autres CIs vérifiées par programme
Les CIs dans les SGBD (2) • Normes : • SQL 86 : unicité, non nullité, vue avec « check option » • SQL89 : domaine, clé, intégrité référentielle avec « rejet » • SQL2 (SQL92) : intégrité référentielle avec «cascade delete et update » • Définition d ’une CI • 2 possibilités: • À chaque définition d’attribut • À la fin de la définition d’une relation
CREATE TABLE VINS ( num integerPRIMARY KEY, cru char (40) NOT NULL, annee integer CONSTRAINT Cannee CHECK (annee between 1970 and 2000), degre number(4,2) CONSTRAINT Cdegre CHECK (degre between 9.0 and 15.0)) CREATE TABLE PRODUCTEURS ( num integerPRIMARY KEY, nom char(40), prenom char(40), region char(40)) ALTER TABLE PRODUCTEURS add CONSTRAINT Cregioncheck (region in ('Bourgogne', 'Beaujolais', 'Alsace', 'Jura', 'Corse')) CREATE TABLE RECOLTES( nprod integer, nvin integer, quantite integer) ALTER TABLE RECOLTES add primary key (nprod, nvin) ALTER TABLE RECOLTES add constraint refVIN foreign key (nvin) references VINS(num) on delete cascade ALTER TABLE RECOLTES add constraint refREP foreign key (nprod) references PRODUCTEURS(num) on delete cascade Exemple
CI et transactions • BD cohérente : toutes les CIs sont vérifiées • Transaction = ensemble de traitements sur la BD • Fait passer la BD d’un état cohérent à un autre état cohérent • Il se peut que ces contraintes d’intégrité ne soit pas vérifiées au cours de la transaction • Exécution atomique BD incohérente ?
Problèmes liés aux CIs • Cohérence • Pas de règles contradictoires • Redondance • Age > 18 et âge > 21 • Optimisation • Nombre minimal de données mises à jour en jeu pour la vérification • Vérification sur certains types de màj uniquement
Protection des informations • Respect des règles de gestion des données (contraintes d’intégrité) • Définition de schémas externes (vues relationnelles) • Définition de droits d’accès en fonction des utilisateurs
Vues relationnelles (2) • Objectifs : • Indépendance logique • Adaptation aux applications • Intégration des applications existantes • Dynamique du schéma de la base • Confidentialité et sécurité • Décentralisation de l’administration d’une BD • Hétérogénéité des modèles
Vues relationnelles • Relation virtuelle • Ensemble de tuples n ’existe pas physiquement • Calculable à l ’exécution • Définie par une requête SQL • Utilisable comme une relation • Utilisable pour définir une autre vue
Syntaxe CREATE VIEW nom_vue [{nom_attribut}] AS requête [WITH CHECK OPTION ] • “ WITH CHECK OPTION ” interdit : • d’insérer des tuples qui ne respectant pas les restrictions de la vue • de modifier un tuple en violant les contraintes définies sur la vue Exemple CREATE VIEW vins_beaujolais (no, nom, degre) AS SELECT num, cru, degre FROM vins WHERE cru=‘Beaujolais ’;
Create view crus (nom) as select distinct cru from vins; Create view buveurs_beaujolais_paris (num, nom, qté_cdée) as select B. nb, B.nom, sum(qté) from buveurs B, cdes C, Vins V where B. nb = C.nb and C.nv=V.nv and V.cru = ‘ Beaujolais’ and B.ville = ‘Paris’ group by B. nb, B.nom; Exemples
Relation : Parent (asc, dsc) create view grand_parent (asc, dsc)as select P1.asc, P2.dsc from parent P1, parent P2 where P1.dsc = P2.asc; create view arr_grd_parent (asc, dsc) as select P.asc, GP.dsc from parent P, grand_parent GP where P.dsc=GP.asc; Exemples (2)
Manipulation de la BD au travers des vues • Consultation • Toujours possible • Nom de la vue dans la clause from SELECT * FROM vins_beaujolais; • Mises à jour : • Rarement possible
La clause DISTINCT ne doit pas être utilisée dans la requête La clause FROM ne doit contenir qu'une seule relation; si la relation source est une vue, celle-ci doit vérifier cette contrainte La clause SELECT ne doit pas faire référence à des expressions, ou des fonctions La clause WHERE ne doit pas contenir de requête imbriquée La requête ne doit pas contenir de clause GROUP BY ni de clause HAVING Mises à jour
Create view Vins_bordeaux as select nv, mil, deg from vins where cru = ‘Bordeaux ’ create view deg_moy_par_cru as select cru, avg (deg) from vins group by cru Mises à jour (2) Tuples identifiables Augmenter la moyenne des degrés de 1?
Create view buveurs_beaujolais_paris (num, nom, qté,_cdée) as select B. nb, B.nom, sum(qté) from buveurs B, cdes C, Vins V where B. nb = C.nb and C.nv=V.nv and V.cru = ‘ Beaujolais ’ and B.ville = ‘ Paris ’ group by B. nb, B.nom Mises à jour (2) Ajouter 10 à la quantité commandée par le buveur 10?
Suppressions et renommage • Destruction: DROP VIEW • Toutes les vues qui utilisent cette vue sont automatiquement détruites • Relation de base détruite => toutes les vues définies sur cette relation sont automatiquement détruites • Renommage: RENAME ancien_nom TO nouveau_nom
Traitements des vues par un SGBD • Techniques: • Modification de question (Ingres) • Restructuration d ’arbre algébriques (System/R) • Matérialisation de la vue (Sabre)
Modification de question • Principe: • modification de la question posée sur la vue en réintégrant les relations de base • Exemple create view recoltes10 as select nvin select * from recoltes from recoltes10 where nprod = 10 where qte > 250 select nvin from recoltes where nprod = 10 and qte > 250
Restructuration algébrique • Nécessite une compilation préalable des vues • Principe : • fusionner les arbres algébriques • optimiser le nouvel arbre
Matérialisation des vues • Réalisée à l’exécution de la requête • Principe : • Matérialisation de la vue par exécution de la question la spécifiant • Optimisation et exécution de la requête sur la vue, considérée comme une relation de base
Sécurité Différentes façons de voir la BD Indépendance logique Faciliter la manipulation Mise à jour impossibles en général Critiques des vues
Protection des informations • Respect des règles de gestion des données (contraintes d’intégrité) • Définition de schémas externes (vues relationnelles) • Définition de droits d’accès en fonction des utilisateurs
Autorisations • Techniques pour assurer la confidentialité: • Définition de contraintes d'autorisation • Contrôle de flux de données • Contrôle d'inférence • Cryptographie
Concepts SQL • Notion d'utilisateur : User_name + mot de passe • Notion de BD logique == propriétaire • Classes d'utilisateurs hiérarchisées • Utilisateur final : CONNECT • Programmeur : RESSOURCE • Administrateur : DBA
Privilèges • Créateur d'une relation ou d'une vue = propriétaire • Peut donner un droit • Peut autoriser à transmettre un droit • Droits sur un objet : • SELECT : consultation DELETE : suppression • INSERT : insertion UPDATE : modification
GRANT <privilège> ON <objet> TO <utilisateur> [WITH GRANT OPTION]; Defude GRANT SELECT ON vins TO carpentier; GRANT SELECT, UPDATE(cru) ON vins TO carpentier, assar WITH GRANT OPTION; GRANT ALL ON vins TO carpentier; Langage de définition de contraintes d'autorisation Carpentier select * on defude.vins Synonyme défini vins=defude.vins select * on vins
Droit 3 (lect. sur prod.) Droit 3 (lect. sur prod.) Droit 1 (lect. sur vins) Droit 1 (lect. sur vins) Droit 2 (lect. sur récoltes) Droit 2 (lect. sur récoltes) Rôle Rôles
Droits et vues • Donner des droits de lecture non pas sur la relation entière, mais sur une vue basée sur la relation • => restreindre l’accès à quelques attributs • CREATE VIEW vins_beaujolais ASSELECT num, cru, degreFROM vinsWHERE cru=‘ Beaujolais ’; • GRANT SELECT ON vins_beaujolais TO producteurs_beaujolais; • Droits + vues => bonne protection des données
REVOKE <privilèges> ON <objet> FROM <utilisateurs> Retire les droits transmis REVOKE ALL ON vins FROM defude; Retirer des droits