Introduction aux bases de données Grégory Beurier LIRMM – Laboratoire d'Informatique de Robotique et de Microélectronique de Montpellier CNRS – Université Montpellier II Définitions Une Base de Données (BD) est un ensemble structuré d'informations mémorisées sur un support permanent et mises à disposition d'un ensemble d'utilisateurs, informaticiens ou non. Un Système de Gestion de Base de Données (SGBD) est un logiciel général qui permet à l'utilisateur de manipuler les données dans des termes abstraits, sans tenir compte de la façon dont l'ordinateur les représente Janvier 2004 Introduction aux Bases de Données 2 Objectifs Fidélité: image fidèle de la réalité Unicité: Pas de redondances d’information Indépendance: SGBD - modèle de stockage Concurrence: Plusieurs accès simultanés Performance Confidentialité Intégrité: fiabilité et cohérence Robustesse: tolérance aux problèmes Janvier 2004 Introduction aux Bases de Données 3 Historique Systèmes hiérarchiques et réseaux Systèmes relationnels Systèmes objets Janvier 2004 Introduction aux Bases de Données 4 Les intervenants humains Utilisateurs naïfs Utilisateurs traditionnels Utilisateurs confirmés Concepteurs de BD Développeurs d’applications Administrateurs de BD Développeurs de BD Janvier 2004 Introduction aux Bases de Données 5 Niveaux d'abstraction Niveau Conceptuel Niveau Logique Niveau Physique Schéma externe 1 Schéma externe 2 Schéma externe N Niveau Externe réel Schéma conceptuel Schéma logique Niveau interne Schéma physique Janvier 2004 Introduction aux Bases de Données 6 Plan du cours Le modèle entité association Le modèle relationnel Les langages d'interrogation Janvier 2004 Introduction aux Bases de Données 7 Le modèle entité association Défini dans la méthode MERISE Joue un rôle primordial en matière de modélisation conceptuelle de BD 2 concepts principaux Entité Association Janvier 2004 Introduction aux Bases de Données 8 Concept d'entité Une entité est définie comme tout concept concret ou abstrait que l'on reconnaît comme individualisable Une entité possède une liste de propriétés appelées attributs Le regroupement d'entités de même nature ayant les mêmes propriétés structurelles constitue un niveau générique appelé classe d'entités ou type d'entités Exemples (classe d'entité : entité, …) Voiture : 206, clio, … Departement : Informatique, Math, Physique, Chimie, … Janvier 2004 Introduction aux Bases de Données 9 Identifiant d'une entité Il faut prévoir un moyen de distinguer les entités : identifiant A une valeur d'un identifiant correspond au plus une entité du type Cette propriété est un invariant de la base, et constitue une contrainte d'intégrité Exemples d'identifiants : Pour une personne : le numéro de sécurité sociale Pour une voiture : le numéro d'immatriculation Janvier 2004 Introduction aux Bases de Données 10 Représentation d'une classe d'entité Nom de la classe Identifiant Liste d'attributs Nom de la classe Identifiant Attribut 1 Attribut 2 … Janvier 2004 Introduction aux Bases de Données 11 Concept d'association (1) On admet que les entités sont en association les unes avec les autres Dans le modèle de base, une association est définie comme un groupe de deux ou plusieurs entités, chacune jouant un rôle dans ce groupe Une classe d'association est un regroupement d'associations de même sémantique Janvier 2004 Introduction aux Bases de Données 12 Concept d'association (2) Une association peut avoir des attributs Exemples d'associations : Une personne conduit une voiture Une personne travaille dans un département Un acteur joue dans un film Janvier 2004 Introduction aux Bases de Données 13 Représentation d'une association (1) Le nom du rôle PERSONNES VOITURES N_SS NOM ADRESSE TEL Janvier 2004 N_Immatriculation POSSEDE Introduction aux Bases de Données TYPE MARQUE COULEUR 14 Représentation d'une association (2) L'association peut avoir un ou plusieurs attributs ACTEUR FILM N_SS NOM ADRESSE TEL Janvier 2004 N_Film JOUE Rôle Introduction aux Bases de Données GENRE DUREE PRODUCTION 15 Identifiant d'une association Il faut prévoir un moyen de distinguer les associations d'une même classe : l'identifiant Souvent on peut identifier une association à partir des entités qu'elle met en jeu Une personne possède une voiture Pas toujours Un acteur joue dans un film : un même acteur peut jouer plusieurs rôles dans un même film… On identifie l'association avec le l'acteur, le rôle et le film Janvier 2004 Introduction aux Bases de Données 16 Cardinalité Permettent d'affiner la modélisation en en précisant le nombre d'association dans lesquelles une entité peut apparaître Cardinalité minimum Cardinalité maximum Janvier 2004 Introduction aux Bases de Données 17 Notation des cardinalités Valeurs : 0, 1, N On peut donc avoir : (0,1) ou (1,1) ou (0,N) ou (1,N) A B A_id A1 A2 A3 Janvier 2004 B_id (min,max) Association (min,max) Introduction aux Bases de Données B1 B2 B3 18 Exemple de cardinalité (1) PERSONNES VOITURES N_SS NOM ADRESSE TEL Janvier 2004 N_Immatriculation (0,N) POSSEDE (1,1) Introduction aux Bases de Données TYPE MARQUE COULEUR 19 Exemple de cardinalité (2) ACTEUR FILM N_SS NOM ADRESSE TEL Janvier 2004 N_Film (0,N) JOUE (0,N) Rôle Introduction aux Bases de Données GENRE DUREE PRODUCTION 20 Construction d'un schéma entité association A partir d'un texte écrit par un non spécialiste Recenser toutes les informations pertinentes pour le problème Recenser les classes d'entités Définir les attributs des classes d'entités Créer des classes d'associations pour relier les classes d'entités Définir les attributs des associations Ajouter les cardinalités Janvier 2004 Introduction aux Bases de Données 21 Exemple de problème : modélisation d'une bibliothèque universitaire La bibliothèque possède une collection de livres. Pour chaque livre on connaît le titre, le ou les auteurs, la maison d'édition, l'année. Pour chaque auteur on connaît le nom et le prénom, ainsi que la nationalité. (On note aussi la langue dans lequel le livre est écrit). La bibliothèque possède plusieurs exemplaires d'un même livre, et on connaît la date d'achat. Les étudiants peuvent emprunter plusieurs livres à la fois. On note la date d'emprunt. Pour chaque étudiant, on connaît le nom, le prénom, le numéro de carte d'étudiant ainsi que l'adresse. On associe aussi une catégorie à chaque livre. Janvier 2004 Introduction aux Bases de Données 22 Schéma E/A de la bibliothèque universitaire LIVRE EXEMPLAIRE N_LIVRE N_EXEMPLAIRE 1,N TITRE LANGUE 1,1 EXISTE DATE_ACHAT 1,1 1,N ECRIT EMPRUNTE DATE_EMPRUNT 1,N 1,N AUTEUR ETUDIANT N_AUTEUR N_ETUDIANT NOM PRENOM NATIONALITE Janvier 2004 NOM_ETUDIANT PRENOM ADRESSE Introduction aux Bases de Données 23 Le modèle relationnel Proposé par Boyce CODD en 1970 Concept clé : la RELATION Normalisation Beaucoup de SGBD commerciaux Langage d'interrogation : SQL Janvier 2004 Introduction aux Bases de Données 24 Concepts de base (1) Un ATTRIBUT est une colonne d'une relation, caractérisée par un nom Gérard Labrador Bureau Louis XV Un DOMAINE est un ensemble de valeurs, caractérisé par un nom Ensemble d'entiers Ensemble de chaînes de caractères {rouge, vert, bleu} Janvier 2004 Introduction aux Bases de Données 25 Concepts de base (2) Une RELATION est un sous ensemble du produit cartésien d'une liste de domaine, caractérisée par un nom Arité : nombre de domaines Cardinal : nombre de tuples Le SCHEMA D'UNE RELATION est le nom de la relation suivie de la liste de ses attributs Par exemple LIVRE(N_LIVRE, TITRE, LANGUE) Le SCHEMA D'UNE BASE DE DONNEES est l'ensemble des schémas des relations de la base et les contraintes associées Une relation est appelée TABLE dans les SGBD Janvier 2004 Introduction aux Bases de Données 26 Les contraintes CLE PRIMAIRE : ensemble d'attributs identifiant la relation Correspond à la notion d'identifiant du modèle E/A Plusieurs notations : LIVRE(N_LIVRE, TITRE, LANGUE) LIVRE(N_LIVRE, TITRE, LANGUE, clé : N_LIVRE) CLE ETRANGERE : spécifie que la valeur de l'attribut doit exister dans l'extension d'une autre relation Permet de représenter une association du modèle E/A quand la cardinalité maximale est 1 Plusieurs notations : EXEMPLAIRE(N_EXEMPLAIRE, DATE_ACHAT, N_ETUDIANT) EXEMPLAIRE(N_EXEMPLAIRE, DATE_ACHAT, N_ETUDIANT, clé : N_EXEMPLAIRE, clé étrangère : N_ETUDIANT) Janvier 2004 Introduction aux Bases de Données 27 Valeurs nulles Valeur particulière, notée NULL Signifie pas de valeur (Différente de 0) Contrainte NOT NULL Janvier 2004 Introduction aux Bases de Données 28 Construction d'un schéma relationnel à partir d'un schéma E/A Chaque classe d'entité devient une relation La clé la chaque relation est définie à partir de l'identifiant de la classe d'entité Chaque classe d'association devient : Si la cardinalité maximum est à 1 : une clé étrangère Si la cardinalité maximum est à N : une relation Janvier 2004 La clé de la relation est définie à partir de l'identifiant de la classe d'association Introduction aux Bases de Données 29 Construction du schéma relationnel de la bibliothèque LIVRE(N_LIVRE, TITRE, LANGUE) EXEMPLAIRE(N_EXEMPLAIRE, DATE_ACHAT, N_ETUDIANT, N_LIVRE) AUTEUR(N_AUTEUR, NOM_AUT, PRENOM_AUT, NATIONALITE) ETUDIANT(N_ETUDIANT, NOM_ETU, PRENOM_ETU, ADRESSE) ECRIT(N_AUTEUR, N_LIVRE) Janvier 2004 Introduction aux Bases de Données 30 Les langages d'interrogation Langage d'interrogation "théorique" : algèbre relationnelle Utilise les opérateurs ensemblistes Langage d'interrogation réels SQL (Simple Query Language) Langage standard pour le modèle relationnel Supporté par la majorité des SGBD QBE (Query By Example) Langage graphique, simple pour l'utilisateur Utilisé par Access (SGBD Microsoft étudié en TP) Janvier 2004 Introduction aux Bases de Données 31 Union Opération ensembliste notée Deux relations avec le même schéma Résultat = les tuples des deux relations (sans les doublons) Janvier 2004 Introduction aux Bases de Données 32 Intersection Opération ensembliste notée Deux relations A et B avec le même schéma Résultat = les tuples qui sont dans A et dans B Janvier 2004 Introduction aux Bases de Données 33 Différence Opération ensembliste notée - Deux relations A et B avec le même schéma : Résultat = les tuples de A qui ne sont pas dans B Janvier 2004 Introduction aux Bases de Données 34 Projection Opération unaire, notée Elimination des attributs non désirés Exemple : ETUDIANT, NUM_ETUDIANT, NOM_ETU Résultat : R(NUM_ETUDIANT, NOM_ETU) Janvier 2004 Introduction aux Bases de Données 35 Sélection Opération unaire notée Elimination des tuples non désirés Exemple : ETUDIANT, ADRESSE="MONTPELLIER" Résultat : tous les étudiants de Montpellier Janvier 2004 Introduction aux Bases de Données 36 Produit cartésien Opération binaire notée Composition de deux relations sur un domaine commun Exemple : AUTEUR LIVRE Résultat : R(NUM_AUTEUR, NOM_AUT, PRENOM_AUT, NATIONALITE, NUM_ETU, NOM_ETU, PRENOM_ETU, ADRESSE) contenant tous les auteurs et tous les livres Janvier 2004 Introduction aux Bases de Données 37 Jointure Opération binaire notée predicat Composition de deux relations sur un domaine commun, avec un prédicat de jointure Exemple : LIVRE LIVRE.NUM_LIVRE=EXEMPLAIRE.NUM_LIVRE EXEMPLAIRE Résultat : R(même schéma que le produit cartésien) contenant tous les tuples satisfaisant le prédicat Janvier 2004 Introduction aux Bases de Données 38 Exemple de requête en langage algébrique Trouver les titres des livres écrits par "Gardarin" R1 = SELECTION(AUTEUR, NOM_AUT="Gardarin") R2 = JOINTURE(ECRIT, R1, ECRIT.NUM_AUTEUR=R1.NUM_AUTEUR) R3 = JOINTURE(R2, LIVRE, R2.NUM_LIVRE=LIVRE.NUM_LIVRE) R = PROJECTION(R3, TITRE) On peut aussi utiliser une notation graphique Janvier 2004 Introduction aux Bases de Données 39 Définition du schéma avec SQL CREATE TABLE permet de créer des tables On peut aussi définir des contraintes (PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE,…) On peut aussi créer des tables par recopie (en utilisant d'autres tables) D'autres fonctionalités : Effacer, modifier des tables Ajouter des contraintes Definir des index … Janvier 2004 Introduction aux Bases de Données 40 Requête SQL Forme générale d'une requête : SELECT…FROM…WHERE SELECT : attributs de la relation du résultat FROM : tables utilisées par la requête WHERE : (optionnelle) conditions Permet de sélectionner des valeurs Permet de faire des jointures Une requête peut contenir aussi : Des requêtes imbriquées Des regroupements Des fonctions d'agrégation Des opérateurs ensemblistes Janvier 2004 Introduction aux Bases de Données 41 Exemple de requête SQL (1) Retrouver les titres des livres écrits par Gardarin SELECT L.TITRE FROM LIVRE L, ECRIT E, AUTEUR A, WHERE A.NOM_AUT = "Gardarin" AND L.NUM_LIVRE = E.NUM_LIVRE AND E.NUM_AUTEUR = A.NUM_AUTEUR; Janvier 2004 Introduction aux Bases de Données 42 Exemple de requête SQL (2) Requêtes imbriquées (titres des livres écrits par Gardarin) SELECT L.TITRE FROM LIVRE L WHERE L.NUM_LIVRE IN (SELECT E.NUM_LIVRE FROM ECRIT E, AUTEUR A WHERE E.NUM_AUTEUR = A.NUM_AUTEUR AND A.NOM_AUT = "Gardarin"); Janvier 2004 Introduction aux Bases de Données 43 Exemple de requête SQL (3) Fonction de comptage (Le nombre d'exemplaire de la bibliothèque) SELECT COUNT(*) FROM EXEMPLAIRE Janvier 2004 Introduction aux Bases de Données 44 Exemple de requête SQL (4) Fonction de groupement et de comptage (le nombre d'exemplaire de chaque livre) SELECT L.TITRE, COUNT(NUM_EXEMPLAIRE) FROM LIVRE L, EXEMPLAIRE E WHERE L.NUM_LIVRE = E.NUM_LIVRE GROUP_BY L.TITRE Janvier 2004 Introduction aux Bases de Données 45 Microsoft Access SGBD Microsoft Simple d'utilisation, suffisant pour une utilisation mono-poste avec des données de taille raisonnable Interface graphique : Création du schéma Création de formulaire Création de requêtes (QBE) Création d'état (impression) Possibilité de programmer en VBA (Visual Basic for Application) Janvier 2004 Introduction aux Bases de Données 46 QBE Tous les étudiants de Montpellier Janvier 2004 Introduction aux Bases de Données 47