Bases de données Yann Loyer [email protected] Plan du cours 1. 2. 3. 4. 5. 6. Introduction Modèle relationnel Langage de manipulation de données Langage de définition de données Bases de la conception de schémas Problèmes courants Bases de données - Yann Loyer 2 Introduction Plan : I.1) Bases de données I.2) Systèmes de gestion de bases de données I.3) Niveaux d’abstractions Bases de données - Yann Loyer 3 Bases de données Une base de données est ensemble de données : – – – – représentant une partie du monde réel stockées en mémoire secondaire pouvant être interrogées et mises à jour servant de support à une ou plusieurs applications Mais… base de données banque de données Bases de données - Yann Loyer 4 Bases de données Une collection de données est appelée base de données si: – format connu (défini au niveau du système et non uniquement au niveau des programmes utilisant les données) et défini par des méta-données (données décrivant des données) – Données stockées, recherchées, modifiées uniquement par un type spécial de programmes appelé système de gestion de bases de données – Données manipulées sous le contrôle de transactions (ensemble de règles formelles assurant l’intégrité des données) Bases de données - Yann Loyer 5 Système de gestion de bases de données (SGBD) Un SGBD est un ensemble de logiciels-système permettant aux utilisateurs de: – – – – mettre en forme sauvegarder, mettre à jour (modifier, insérer, supprimer) rechercher efficacement des données spécifique dans une très grande masse d’informations partagées entre plusieurs utilisateurs Bases de données - Yann Loyer 6 Utilisation d’un SGBD • Définition du schéma de la base (modèle Entité/Association, normalisation) • schéma conceptuel (format des données et associations entre données) • schéma interne (correspondance entre les notions conceptuelles et celles liées à l’organisation physique des données – fichiers, index) • schémas externes (définition d’une vue pour chaque groupe d’utilisateur) • programmes d’application (code est constitué de commandes propres au SGBD intégrées dans un langage de programmation s’exécutent en référençant les schémas Bases de données - Yann Loyer 7 externes Fonctionnalités d’un SGBD (1) • Gestion de données persistantes • Gestion de grandes quantités de données • Fiabilité des données – Cohérence et contraintes d’intégrité – Sûreté du fonctionnement • Notion de transaction atomique • Techniques de sauvegarde et/ou de journalisation • Procédures de reprise sur panne Bases de données - Yann Loyer 8 Fonctionnalités d’un SGBD (2) • Sécurité d’accès – commandes d’autorisation • Partage et accès concurrents – techniques de verrouillage • Interrogation : langages de requêtes – déclaratifs et incomplets intégration du langage de requête et d’un langage de programmation dysfonctionnements (typage et traitement ensembliste) Bases de données - Yann Loyer 9 Niveaux d’abstraction • Plusieurs niveaux d’abstractions entre l’ordinateur (qui traite des bits) et l’utilisateur final (qui traite des concepts) • Dans le domaine des BD, on en considère 3: – Niveau interne – Niveau conceptuel – Niveau externe Bases de données - Yann Loyer 10 Niveau interne • Ensemble de fichiers et de structures utilisées pour permettre un accès efficace aux données (index) • Appelé base de données physique • Réside en mémoire auxiliaire • Plusieurs BD physiques peuvent être gérées par le même SGBD Bases de données - Yann Loyer 11 Niveau conceptuel (1) • Base de données conceptuelle: abstraction du monde réel • Langage de définition de données: – fourni par le SGBD – permet de définir la BD conceptuelle en terme de modèle de données – permet de réaliser son implémentation au niveau physique Bases de données - Yann Loyer 12 Niveau conceptuel (2) Principaux modèles de données: • Modèle hiérarchique – Structure de base: l’arbre • Modèle réseau – Structure de base: le graphe orienté • Modèle relationnel – Structure de base: la relation • Émergence de nouveaux modèles – Modèle orienté-objets Bases de données - Yann Loyer 13 Niveau conceptuel (3) • BD conceptuelle: description d’un ensemble cohérent réunissant les informations pertinentes d’une entreprise • Processus: ensemble de fichiers BD conceptuelle – appelé intégration de données – coordonné par une ou plusieurs personnes constituant l’administrateur de la base Bases de données - Yann Loyer 14 Niveau externe (1) • Appelé également Niveau des vues • Vue: abstraction de la BD conceptuelle représentant une partie de ses informations exemple: si une BD conceptuelle décrit des clients par leurs noms, dates de naissance et numéro de CB, alors on peut définir une vue décrivant les clients par leurs noms et ages • La plupart des SGBD fournissent: – un langage de définition de vues – un langage de manipulation de vues Bases de données - Yann Loyer 15 Niveau externe (2) • Construction de vues : processus inverse de l’intégration de données • Avantage de l’intégration de données: – – – – – vue globale des données de l’entreprise meilleur contrôle de la cohérence élimination des redondances meilleur contrôle d’accès aux données possibilité de faire des études statistiques plus facilement – etc. Bases de données - Yann Loyer 16 Vision globale d’une BD Programme d’application 1 Niveau externe Niveau Conceptuel Niveau Interne vue 1 Programme d’application n … vue n BD conceptuelle BD physique Bases de données - Yann Loyer 17 Atouts du modèle relationnel • Le modèle relationnel est très populaire dans l’industrie car: – conceptuellement simple (tout programmeur d’application peut aisément comprendre les concepts de base) – fondements mathématiques (les aspects opérationnels peuvent être prouvés et des propositions testées) Bases de données - Yann Loyer 18 II. Niveau conceptuel II.1 Définition d’une BD relationnelle La définition d’une BD suppose l’existence de: – un ensemble d’attributs – un ensemble de domaines (ou types, ou valeurs) – un ensemble de noms de tables Bases de données - Yann Loyer 20 Schéma de relation Définition: schéma de relation R = ensemble fini de la forme R = {A1:dom(A1),…, An: dom(An)} où Ai est un attribut et dom(Ai) le domaine de Ai noté R = {A1,…, An} Bases de données - Yann Loyer 21 N-uplets et relations Définition: un n-uplet t sur R est une fonction qui associe à chaque attribut de Ai de R une valeur de son domaine dom(Ai) – Si t(Ai) = ai pour tout i, alors t est représenté par ai… an Définition: une relation r sur R est un ensemble de n-uplets sur R – R est appelé schéma de r, et noté sch(r) Bases de données - Yann Loyer 22 Base de données Définition: un schéma de base de données S est un ensemble fini de tables, où chaque table T est associée à un schéma de relation sch(T) – T de schéma sch(T) = {A1,…, An} est notée T(A1,…,An) – Un attribut peut appartenir à plusieurs tables – Plusieurs tables peuvent avoir le même schéma Définition: Soit S = {T1,…, Tn}. Une base de données sur S est une fonction qui associe à chaque table Ti une relation finie (Ti) sur sch(Ti) Bases de données - Yann Loyer 23 Exemple de BD relationnelle • Soit S le schéma de base de données suivant: S = {E(EMP:char ; DEP:char) ; M(MGR:char ; DEP:char)} • Soit une base de données sur S définie par: (E) = {(Dupont;info); (Durand;info)} (M) = {(Martin;info); (Marton;RH)} Bases de données - Yann Loyer 24 II.2 Mises à jour • Insertion • Modification • Suppression d’un ou plusieurs n-uplets dans une relation donnée pas de difficulté au niveau conceptuel Bases de données - Yann Loyer 25 II.3 Consultation Deux types de langages: • langages fondés sur l’algèbre relationnelle – impératifs • langages fondés sur le calcul relationnel – déclaratifs Bases de données - Yann Loyer 26 II.3.1 Algèbre relationnelle 6 opérations de bases qui s’appliquent aux relations et retournent des relations: – – – – – – Union Différence Projection Sélection Produit Renommage Bases de données - Yann Loyer 27 UNION • argument : 2 relations r et s telles que sch(r) = sch(s) • résultat : une relation r s telle que: sch(r s) = sch(r) r s = {t | t r ou t s} Bases de données - Yann Loyer 28 DIFFERENCE • argument : 2 relations r et s telles que sch(r) = sch(s) • résultat : une relation r - s telle que: sch(r - s) = sch(r) r - s = {t | t r et t s} Bases de données - Yann Loyer 29 PROJECTION • argument : une relation r et un ensemble d’attributs X tel que X sch(r) • résultat : une relation X(r) telle que: sch(X(r)) = X X(r) = {t(X) | t r} où t(X) est la projection de t sur X Bases de données - Yann Loyer 30 SELECTION • argument : une relation r et une formule • formules : soient A et B des attributs, t et t’ des n-uplets et a une valeur de dom(A), alors : – t(A) = t’(B), t(A) < t’(B), t(A) = a … sont des formules – Si F et G sont des formules, alors (F), F, FG, FG sont des formules • résultat : une relation (r) telle que: sch((r)) = sch(r) (r) = {t | t r et } Bases de données - Yann Loyer 31 PRODUIT • argument : 2 relations r et s telles que sch(r) sch(s) = • résultat : une relation r s telle que: sch(r s) = sch(r) sch(s) r s = {t | t(sch(r)) r et t(sch(s)) s} Bases de données - Yann Loyer 32 RENOMMAGE • argument : une relation r et une fonction f qui à chaque attribut associe un attribut • résultat : une relation f(r) telle que: sch(f(r)) = f(sch(r)) f(r) = {s | t r et s f = t} Bases de données - Yann Loyer 33 Langage algébrique de requêtes Chaque schéma de BD est associé à un langage algébrique de requêtes: – Ensemble de toutes les expressions relationnelles que l’on peut former à partir des tables de S et des opérations de l’algèbre relationnelle – Chaque expression e est appelée requête sur S – Le résultat de l’évaluation de e sur est appelé réponse de e sur Bases de données - Yann Loyer 34 Évaluation d’une expression 1. Remplacer chaque table T de e par (T) 2. Effectuer les opérations de e 3. Renvoyer le résultat Traitement parallèle Cela équivaut à définir une fonction Fe : Fe () = eval(e, ) Bases de données - Yann Loyer 35 Requêtes équivalentes • Chaque requête équivaut à un programme Fe • L’évaluation de e sur une BD équivaut à l’exécution de ce programme avec en entrée et en sortie eval(e, ) • Deux expressions e et e’ sont équivalentes, e e’, ssi Fe = Fe’ – Exemple: X(R) = X(Y(R) ) si X Y Bases de données - Yann Loyer 36 Exercice Soit le schéma de BD suivant: • SALLE(nom,horaire,titre) • FILM(titre,réalisateur,acteur) • PRODUIT(producteur,titre) • VU(spectateur,titre) • AIME(spectateur,titre) Bases de données - Yann Loyer 37 Requêtes 1. 2. 3. 4. Quels sont les films réalisés par Coppola? Où et à quelle heure peut-on voir « Le parrain »? Où peut-on voir un film dans lequel joue Al Pacino? Après 23h, où peut-on voir un film dans lequel joue Marlon Brando? 5. Quels acteurs ont produit un film dans lequel ils jouent? 6. Quels sont les acteurs qui ont produit un film? 7. Quels acteurs ont produit un film qu’ils ont réalisé? 8. Quels sont les spectateurs n’aimant aucun film? 9. Quels spectateurs n’ont pas aimé un des films qu’ils ont vus? 10. Quels spectateurs ont aimé tous les films qu’ils ont vus? Bases de données - Yann Loyer 38 II.3.2 Calcul relationnel • Langage logique langage de requêtes relationnels (SQL) • Principe: – Chaque table T de S un prédicat – N-uplets de (T) valeurs satisfaisant le prédicat Bases de données - Yann Loyer 39 Calcul relationnel Formules: soit un ensemble de variables par domaine – Les valeurs et variables d’un domaine sont des termes – Si x1,…, xn sont des termes, alors • R(x1,…, xn) • x1 xn avec {=, ,,…} sont des formules – Si F et G sont des formules et x une variable libre de F, alors • (F), F, F G, F G, F G x F, x F sont des formules Bases de données - Yann Loyer 40 Calcul relationnel • Interprétation: la formule T(x1,…, xn) est vraie pour une valuation donnée si le n-uplet correspondant appartient à la relation (T) • Requête: {(x1,…, xn) | F(x1,…, xn)} où (x1,…, xn) sont les variables libres de F • Réponse: ensemble des valeurs de (x1,…, xn) pour lesquelles Bases F est vraie de données - Yann Loyer 41 II.3.3 SQL SQL : langage de requêtes fondé sur le calcul relationnel Forme de base: SELECT A1,…, An FROM T1,…, Tm WHERE ; les Ai sont des attributs des tables T1,…, Tm est une condition Bases de données - Yann Loyer 42 SQL SELECT A1,…, An FROM T1,…, Tm WHERE ; est équivalent à A1,…, An ( (T1 … Tm )) Toutes les expressions de l’algèbre et du calcul peuvent être écrite en SQL (union, diff, intersect, forall, exists, not,Bases in…) de données - Yann Loyer 43 SQL A1,…, An ( (T1 … Tm )) A1,…, An (’ (T’1 … T’p )) équivaut à (SELECT A1,…, An FROM T1,…, Tm WHERE ) UNION (SELECT A1,…, An FROM T’1,…, T’p WHERE ’) Bases de données - Yann Loyer 44 II.3.4 Optimisation des requêtes Preuves d’équivalences entre expressions à l’aide de: • Propriétés classiques (associativité, commutativité, …) • Définitions des opérations • Propriétés telles que : 1. t r || s t(R) r et t(S) s 2. t X (r) t’ r (t’(X) = t) 3. t r X R (t(X) X (r)) Bases de données - Yann Loyer 45