Problèmes BD. Bases de données - Yann Loyer2 Problèmes BD Ensemble de problèmes couramment...

Problèmes BD

Bases de données - Yann Loyer 2

Problèmes BD

• Ensemble de problèmes couramment rencontrés lors du développement d’applications de bases de données

• Il est nécessaire :– de comprendre les mécanismes au cœur de ces problèmes

– de savoir détecter en pratique les situations pouvant entraîner ces problèmes

– de connaître les outils appropriés fournis par les SGBD

– de savoir tirer le meilleur parti du SGBD pour les résoudre

Bases de données - Yann Loyer 3

Exemple de situation

• Développement d’une application pour la gestion de réservations de places de trains:– les clients effectuent des réservations– les employés gèrent les informations sur les trains

Résa(client,date,num_train,num_place)Train(num_train,départ,arrivée,hd,ha,distance, nb_places_libres, nb_places_total)

Bases de données - Yann Loyer 4

Problème 1 : intégrité

• Une distance ne peut être négative• Pour un train donné, le nombre de réservations

ajouté au nombre de places libres doit être égal au nombre total de places

• Un numéro de train doit être unique

il faut empêcher les données absurdes ou incohérentes !

Bases de données - Yann Loyer 5

Problème 2 : indépendance des niveaux

• Une fois l’application développée, on s’aperçoit qu’il faut réorganiser les données

on souhaite éviter de devoir réécrire l’application

à chaque modification du schéma ou à chaque

réorganisation des données en mémoire!

Bases de données - Yann Loyer 6

Problème 3 : confidentialité

• Un client ne doit pas pouvoir modifier l’horaire d’un train ou les réservations d’autres clients

• Un employé doit pouvoir le faire

n’importe qui ne doit pas pouvoir faire

n’importe quoi !

Bases de données - Yann Loyer 7

Problème 4 : persistance

• Le client effectue sa réservation par internet, puis se déconnecte :– sa réservation ne doit pas disparaître, donc doit

être stockée– Son numéro de carte bancaire doit disparaître

après le paiement, donc ne doit pas être stocké

Bases de données - Yann Loyer 8

Problème 5 : reprise sur panne

1. La demande de réservation et le numéro de CB sont saisis par le client

2. Le paiement est effectué

3. Une panne de courant survient avant que la réservation ne soit définitivement stockée

le client paie une réservation imaginaire !

Bases de données - Yann Loyer 9

Problème 6 : concurrence

1. Il reste une place dans le Paris-Marseille de 13h

2. Deux clients se connectent en parallèle et tentent de réserver une place dans ce train

les deux clients ne doivent pas avoir cette

même place !

Bases de données - Yann Loyer 10

Problème 7 : grandes quantités

• Quantités d’informations >>> mémoire vive

accès disques : lenteur de l’application !

Bases de données - Yann Loyer 11

Problème 8 : répartition

• Pour effectuer une réservation Paris-Berlin,

votre application doit dialoguer avec

l’application allemande

tous les problèmes précédents doivent être

gérer en coopération par les deux applications !

Bases de données - Yann Loyer 12

Liste des problèmes BD

1. Intégrité des données

2. Indépendance des niveaux

3. Confidentialité

4. Persistance

5. Reprise sur panne

6. Concurrence

7. Grandes quantités de données

8. BD distribuées

Bases de données - Yann Loyer 13

Intégrité des données

• Problème : – données absurdes– données incohérentes entre elles

• Objectifs : – Autoriser uniquements des données intègres– Restreindre les valeurs autorisées dans les colonnes

• Outils : – définition de contraintes d’intégrité– détection et rejet automatiques des mises à jour violant les

contraintes

Bases de données - Yann Loyer 14

Contraintes d’intégrité

• Le SGBD doit permettre à l'utilisateur de définir des règles (ou contraintes) sur les données.

• Ces contraintes d’intégrité sont spécifiées lors de la définition du schéma de la BD.

• Elles constituent des assertions qui doivent être satisfaites à tout moment par le contenu de la base.

• Toute mise à jour entraînant la non-satisfaction d’une contrainte est rejetée.

• Une contrainte peut être désignée par un nom lors de sa création

Bases de données - Yann Loyer 15

Types de contraintes courantes

• Domaine de variation ou type, ex : l'attribut NOM est un texte (obligatoire)• Non nullité, ex : l'attribut NUM ne peut être nul• Plages de valeurs, ex : une note est comprise entre 0 et 20• Unicité, ex : l'attribut NUM est clé de la relation EMPLOYES• Dépendance fonctionnelle, ex : CODE_POSTAL -> VILLE• Dépendance référentielle, ex : tout employé de la base doit être affecté à un

département de la base• Condition générale sur la relation, ex: la COMMISSION est au plus 2 fois le

SALAIRE• Contrainte temporelle, ex : Le SALAIRE ne peut pas décroître• Contrainte avec agrégat, ex : la moyenne des Salaires doit être supérieure à

9000

Bases de données - Yann Loyer 16

Déclaration des contraintes d'intégrité

CREATE TABLE <nom de table> ( <nomatt1> TYPE [DEFAULT <expression>] [<contrainte sur attribut1>] [,<nomatt2> TYPE [DEFAULT <expression>] [<contrainte sur attribut2>] … [<1ère contrainte sur relation> [,<2ème contrainte sur relation>] ...] )

contrainte sur attribut = [ NULL | NOT NULL [ CONSTRAINT <nom_contrainte1>] ] | [ {UNIQUE | PRIMARY KEY} [ CONSTRAINT <nom_contrainte2>] ] | [ REFERENCES <relation> [(<attribut>)] [ CONSTRAINT

<nom_contrainte3>] [ON DELETE CASCADE] ] | [ CHECK ( <condition sur attribut> ) [ CONSTRAINT <nom_contrainte4>] ]

Bases de données - Yann Loyer 17

Déclaration des contraintes d'intégrité

CREATE TABLE <nom de table> ( <nomatt1> TYPE [DEFAULT <expression>] [<contrainte sur attribut1>] [,<nomatt2> TYPE [DEFAULT <expression>] [<contrainte sur attribut2>] … [<1ère contrainte sur relation> [,<2ème contrainte sur relation>] ...] )

contrainte sur relation = [{UNIQUE | PRIMARY KEY (<attribut1> [,<attribut2>]…)} [CONSTRAINT

<nom_contrainte2>]] | [ FOREIGN KEY (<att1> [,<att2>]...) REFERENCES <relation> [(<att3> [,<att4>]... )]

[CONSTRAINT <nom_contrainte3>] [ON DELETE CASCADE]] | [ CHECK ( <condition sur relation> ) [CONSTRAINT <nom_contrainte4>] ]

Bases de données - Yann Loyer 18

Exemples de contraintes d’intégrité

CREATE TABLE entraîneurs ( num_entraîneur integer PRIMARY KEY, nom_entraîneur varchar(20) NOT NULL, email_entraîneur varchar(20) UNIQUE tél_entraîneur number(10) );

CREATE TABLE équipes ( num_équipe integer PRIMARY KEY, nom_équipe varchar(20) NOT NULL, nb_inscrits integer CHECK(nb_inscrits >= 0) entraîneur integer REFERENCES entraîneurs(num_entraîneur) ON DELETE CASCADE);

Bases de données - Yann Loyer 19

Confidentialité et indépendance

• La résolution des problèmes de confidentialité et d’indépendance logique nécessitent l’utilisation de vues

• Nous allons donc étudier le niveau des vues appelé niveau externe

Niveau externe

Bases de données - Yann Loyer 21

Vision globale d’une BD Programme Programme

d’application 1 d’application n

Niveau vue 1 … vue n

externe

Niveau BD conceptuelle

Conceptuel

Niveau BD physique

Interne

Bases de données - Yann Loyer 22

Niveau externeAppelé également niveau des vues• Un groupe d’utilisateurs d’une base de données conceptuelle

peut avoir besoin :– d’une partie seulement des informations de la base de données,

et/ou– de ces informations structurées différemment

• i.e. d’une BD conceptuelle différente de la base de départ, mais dépendant de celle-ci :

1. Tout attribut figurant dans la nouvelle base est aussi dans la base de départ ou dépend des attributs de celle-ci

2. Les instances de chaque table de la nouvelle base sont calculées à partir de celle de la base de départ

Bases de données - Yann Loyer 23

Vues

• Toute base de données conceptuelle satisfaisant ces deux conditions est une vue

Exemple : S = {E(emp,dep);M(dep,mgr);S(emp,sal)}Vue V : • tables = {EM(emp,mgr);BS(emp,sal)}• Calcul des instances :

emp,mgr(E M) pour les instances de EM sal<1000 (S) pour les instances de BS

Bases de données - Yann Loyer 24

Vues

• Chaque table d’une vue est entièrement déterminée par la donnée d’une expression nommée T = e, où T est une table et e une expression relationnelle sur la base de départ– sch(T) = sch(e)

– Les instances de T sont calculées par e

Exemple : définition de la vue :– EM = emp,mgr(E M)

– BS = sal<1000 (S)

Bases de données - Yann Loyer 25

Définition de vue

• Tout ensemble d’expressions nommées de la forme T = e sur le schéma S de BD est une vue sur S

• Vue : BD conceptuelle plus abstraite– les données de la vue sont construites à partir

de celles de la BD conceptuelle mais peuvent ne pas être présentes dans celle-ci

– les données de la vue n’ont pas d’existence indépendante de celles de la BD conceptuelle

Bases de données - Yann Loyer 26

Gestion des vues

• Une vue devrait pouvoir être interrogée et mise à jour comme n’importe quelle BD conceptuelle, mais cela dépend de l’implémentation choisie :

1. Vues virtuelles

2. Vues matérialisées

Bases de données - Yann Loyer 27

Vues virtuelles• Vue virtuelle

– les relations de la vue ne sont pas stockées– seule sa définition est stockée– le SGBD doit traduire les requêtes et mises à jour sur la vue en

requêtes et mises à jour sur la BD conceptuelle

• Traduction des requêtes – Chaque table de la vue dans la requête est remplacée par

l’expression relationnelle qui la traduit (exemple : emp(BS) remplacée par emp(sal<1000 (S)) )

• Traduction des mises à jour– Problèmes dès que la vue est définie à partir de plusieurs tables

Bases de données - Yann Loyer 28

Vues matérialisées

• Stockées physiquement (ex: entrepôts de données)

• Les requêtes sont évaluées sur la vue

• Nécessité de propager les mises à jour effectuées sur la base au niveau des vues pour le maintien de la cohérence

Bases de données - Yann Loyer 29

Utilisation des vues

• Au niveau de l’utilisateur (indépendance logique) :– Indépendance logique (protection des programmes

d’application contre les modifications du schéma)

– Exemple : le remplacement de E et M par la table EDM(emp,dep,mgr) n’implique pas la réécriture des programmes définis sur EM

• Au niveau du système (confidentialité) :– Protection des données (exemple : BS)

Bases de données - Yann Loyer 30

Les vues en SQL

Création d'une vue

CREATE VIEW <nom de vue> [(att1[, att2] ...)]

AS <REQUÊTE>

Suppression d'une vue

DROP VIEW <nom de vue>

Bases de données - Yann Loyer 31

Confidentialité

• Principes :– objectifs :

• protéger les données d’accès intempestifs

• autoriser certains accès

– outils :• déclaration de l’utilisateur à la connexion

• catégories de pouvoir (mises à jour du schéma, mises à jour des données, consultation)

• autorisations locales à chaque table

• vues

Bases de données - Yann Loyer 32

Confidentialité

Commande SQL pour donner des droits : GRANT privilèges ON table (ou vue) TO user [WITH GRANT OPTION]

avec :– privilèges = ALL ou

{select,insert,update,delete,alter,index} – table = train, ou Félix.train (pour la table d’un autre utilisateur Félix)– utilisateur = Thérèse, ou PUBLIC (pour tous les utilisateurs)– with grant option : possibilité de transmettre ces droits

Bases de données - Yann Loyer 33

Confidentialité

Commande SQL pour supprimer des droits : REVOKE privilèges ON table (ou vue) FROM user

– Si l’option « with grant option » a été utilisée alors

les droits sont révoquées en cascade