Ministère de l’Enseignement Supérieur, et de la Recherche Scientifique Direction Générale des Etudes Technologiques Institut Supérieur des Etudes Technologiques de Djerba Support de cours Bases de Données Elaboré par : Maèl SALAH JRAD Public cible : Classe de 2ème année Licence Appliquée : Technologies de l’Informatique Option: Développement des Systèmes d'Information Année Universitaire : 2016-2017 Support de cours bases de données : Fiche Matière Fiche Matière Unité d'enseignement - Système d'information 1 Pré-requis - UE : Programmation structurée - UE : Programmation et structures dynamiques Public cible - Etudiants des classes de 2ème année : L2-TI - Licence : Technologies de l’Informatique (Tronc commun) Volume horaire - -1h 30 de cours intégré (cours + TD) - Soit en total 22,5h Moyens pédagogiques - Tableau - Micro-ordinateurs - Vidéo projecteur - Polycopiés des Travaux Dirigés Evaluation - Coefficient :2 - Crédits :2 - Note de devoir de contrôle (mi semestre) : 32% - Note de devoir de synthèse (fin de semestre) : 48% - Note de travail personnalisé (compte rendus, mini projet) : 20% Support de cours Base de Données Enoncé de l'objectif général 1 : Le cours vise à: Initier l'étudiant à comprendre des concepts généraux sur une BD et un SGBD Elément de contenu/Thème Objectifs spécifiques Introduction Identifier et expliquer l'intérêt d'un Méthodologies et moyens système de stockage de données Question de Tableau Décrire le système de stockage à fichiers, Tableau et identifier ses limites Exemples Définir une base de données et décrire Tableau ses besoins. Exemples Définition et Objectifs d'un Définir un système de gestion de base Tableau SGBD. de données et énumérer ses fonctions Exemples Les niveaux d'abstraction Expliquer l'intérêt de différenciation Tableau entre les niveau d'abstraction lors de la Exemples Les bases de données conception d'une base de données Durée Formative pré-requis Les système de fichiers Evaluation Formative 3h Formative Formative Formative Support de cours Base de Données Enoncé de l'objectif général 2 : Le cours vise à: Rendre l'étudiant apte à concevoir une BD avec le modèle E/A Elément de contenu/Thème Objectifs spécifiques Généralités Expliquer l'utilité du modèle E/A et Méthodologies et moyens énumérer ses avantages dans un processus de conception d'un schéma de Evaluation Durée Question de pré-requis Formative Tableau Décrire une association de base Tableau :Attributs, Entités,... Exemples Décrire une association. Tableau Formative Définir les cardinalités, distinguer entre Exemples Sommative les différents types de cardinalités sur un TD base de données. Concepts de base Association et cardinalité Formative modèle. Créer et lire un modèle E/A Analyser une étude de cas donné Démarche d'élaboration Produire un schéma E/A suivant une Tableau Formative d'un schéma BD démarche méthodique. Exemples Sommative TD 3h Support de cours Base de Données Enoncé de l'objectif général 3 : Le cours vise à: Rendre l'étudiant capable de traduire un modèle E/A en un modèle relationnel normalisé. Elément de contenu/Thème Objectifs spécifiques Généralités Expliquer l'utilité d'un relationnel et Question de pré-requis énumérer ses objectifs Tableau Définir et Décrire les concepts de base : Tableau Relation, Tuple, contrainte d'intégrité Exemples TD Interpréter les règles de traduction du Tableau Formative Traduction E/A>Relationnel modèle E/A vers le modèle relationnel. Exemples Sommative Concepts de base Méthodologies et moyens Evaluation Formative Formative Appliquer ces règles sur un exemple. Les dépendances Décrire une dépendance fonctionnelle, et Tableau Formative fonctionnelles énumérer ses propriétés Exemples Sommative Expliquer l'utilité et les objectifs de la Tableau Formative normalisation. Exemples Sommative Appliquer à travers un exemple, le TD Normalisation passage vers chacune de ces formes normales Durée 3h Support de cours Base de Données Enoncé de l'objectif général 4 : Le cours vise à: Fournir à l'étudiant capable les notions du modèle théorique "Algèbre Relationnelle", nécessaires à la manipulation des données d'un modèle relationnel. Elément de contenu/Thème Objectifs spécifiques Définition Définir l'algèbre relationnelle et Question de pré-requis énumérer ses objectifs. Tableau Enumérer les opérateurs ensemblistes et Tableau Formative décrire leurs notations et leurs Exemples Sommative utilisations. TD Enumérer les opérateurs spécifiques et Tableau Formative décrire leurs notations et leurs Exemples Sommative utilisations . TD Appliquer les différents opérateurs de Tableau Opérateurs ensemblistes Opérateurs spécifiques Exercice d'application l'algèbre relationnelle pour manipuler les données Méthodologies et moyens Evaluation Durée Formative Formative Sommative 3h Support de cours Base de Données Enoncé de l'objectif général 5 : Le cours vise à: Fournir présenter le langage SQL Rendre l'étudiant capable d'interagir avec les données de la base avec ce langage Elément de contenu/Thème Objectifs spécifiques Présentation du SQL Définir langage SQL et le décrire en Question de pré-requis utilisant ses principales caractéristiques. Tableau Langage de manipulation Concevoir et écrire les requêtes SQL Tableau Formative de données LDD permettant la création et la modification de Exemples Sommative la base de données(table, vue,..). TD Langage de manipulation Concevoir et écrire des requêtes SQL Tableau Formative de données LMD permettant la manipulation des données de Exemples Sommative la base (Ajout, modification, suppression). TD Traduire un besoin de sélection des Tableau Formative données en une requête SQL exécutable par Exemples Sommative le SGBD. TD Langage de contrôle de Concevoir et écrire des requêtes SQL Tableau Formative données LCD permettant de gérer les utilisateurs ,les Exemples Sommative privilèges ainsi que leurs affectations. TD Interrogation des données Méthodologies et moyens Evaluation Durée Formative 3h Table des matières Chapitre1. Introduction aux bases de données .......................................................... 2 1. 2. Introduction................................................................................................................... 2 Bases de Données et Systèmes de Gestion de Bases de Données ............................. 2 2.1. Définition d’une base de données (BD) ..................................................................... 2 2.2. Modèle de base de données.......................................................................................... 2 2.3. Définition d’un Système de gestion de base de données (SGBD) ...................... 3 2.4. Principes de fonctionnement d'un SGBD ................................................................. 3 2.5. Architecture des SGBD ................................................................................................ 4 2.6. Quelques SGBD ............................................................................................................. 5 3. Processus de conception d'une base de données Relationnelle ................................. 5 3.1. Cycle de vie d’une base de donnée ............................................................................. 6 3.2. Avantages de la modélisation conceptuelle ............................................................. 7 3.3. Critères de qualité pour les modèles conceptuels .................................................. 7 Chapitre2. Modèle Entités/Associations ..................................................................... 9 1. 2. Introduction................................................................................................................... 9 Schéma entités/associations ........................................................................................ 9 2.1. Entité ............................................................................................................................... 9 2.2. Association ou relation ............................................................................................... 10 2.3. Types d’associations .................................................................................................... 12 3. Démarche à suivre pour produire un schéma E/A .................................................. 15 3.1. Démarche ...................................................................................................................... 15 Chapitre 3. Le modèle relationnel ............................................................................. 19 1. Introduction au modèle relationnel ........................................................................... 19 1.1. Présentation ................................................................................................................. 19 1.2. Éléments du modèle relationnel .............................................................................. 19 2. Passage du modèle entités-associations au modèle relationnel.............................. 21 2.1. Règles de passage ........................................................................................................ 21 2.2. Exemple d’une association du type 1 vers 1 .......................................................... 22 2.3. Cas particulier d’un type-entité sans attribut autre que sa clé ........................ 22 2.4. Exemple ......................................................................................................................... 23 Chapitre 4. Dépendance fonctionnelle et Normalisation ........................................ 25 1. 2. Introduction................................................................................................................. 25 Dépendances fonctionnelles et graphe des dépendances fonctionnelles ................ 25 2.1. Définition....................................................................................................................... 25 2.2. DF Élémentaire ........................................................................................................... 26 2.3. DF totale / Partielle .................................................................................................... 26 2.4. DF directe / transitive ................................................................................................ 26 2.5. Propriétés des dépendances fonctionnelles ........................................................... 27 2.6. Dépendance fonctionnelle élémentaire ................................................................... 27 2.7. Dépendance fonctionnelle directe ............................................................................ 27 3. Forme normale ............................................................................................................ 27 3.1. Première forme normale ............................................................................................ 27 3.2. Deuxième forme normale........................................................................................... 28 3.3. Troisième forme normale .......................................................................................... 28 4. 3.4. La forme normale de BOYCE-CODD...................................................................... 29 Conclusion ................................................................................................................... 29 Chapitre 5. L’algèbre Relationnelle........................................................................... 31 1. Introduction..................................................................................................................... 31 2. Les opérations spécifiques ............................................................................................. 31 2.1. Projection (π)...................................................................................................................... 31 2.2. Sélection (σ) ........................................................................................................................ 32 2.2. La jointure (⋈) ................................................................................................................... 33 4. Les opérations ensemblistes ...................................................................................... 34 3.1. L’union (∪) .................................................................................................................... 34 3.2. La différence (-) ............................................................................................................ 35 3.3. L’intersection (∩) ......................................................................................................... 36 3.4. Produit cartésien ( ).................................................................................................. 36 5. Expressions d'algèbre ................................................................................................. 37 Chapitre 6. Le langage SQL ....................................................................................... 39 1. 2. 3. Introduction................................................................................................................. 39 Catégories d’instructions............................................................................................ 39 SQL en tant que LDD ................................................................................................. 40 3.1. Création de table ......................................................................................................... 40 3.2. Modification de la structure d’une table ................................................................ 43 3.3. Suppression de table ................................................................................................... 43 3.4. La création des vues ................................................................................................... 44 3.5. Création d'index ........................................................................................................... 45 4. SQL en tant que LMD ................................................................................................ 46 4.1. Insertion de données ................................................................................................... 46 4.2. Modification de données............................................................................................. 47 4.3. Suppression des données ........................................................................................... 48 5. Interrogation de la base de données en LMD ........................................................... 49 5.1. Requêtes simples ......................................................................................................... 49 5.2. Requêtes sur plusieurs tables ................................................................................... 51 5.3. Fonctions d'agrégation ............................................................................................... 54 6. Le langage de contrôle de données (LCD)................................................................. 57 6.1. Gestion d'utilisateurs ................................................................................................. 57 6.2. Les droits d’accès ......................................................................................................... 58 6.3. Les transactions dans SQL : COMMIT et ROLLBACK ..................................... 60 Bibliographie ............................................................................................................... 61 Liste des figures Figure 1: Architecture d’un SGBD ..................................................................................... 5 Figure 2: Processus de conception d'une base de données Relationnelle .................. 6 Figure 3: Représentation graphique d’un exemple de type-entité ............................ 10 Figure 4:Représentation graphique d’un exemple ....................................................... 11 Figure 5:Représentation graphique des cardinalités d’un type-association........... 11 Figure 6:Exemple d’associations plurielles .................................................................... 12 Figure 7:Exemple d’associations réflexives sur le type-entité Employés ................ 13 Figure 8:Exemple d’association ternaire ........................................................................ 13 Figure 9:Type-association ternaire de la figure 8 corrigé en deux typeassociations binaires. .......................................................................................................... 14 Figure 10:Modèle représentant un type-association ternaire Vol liant trois typeentités Avion, Trajet et Pilote ........................................................................................... 14 Figure 11:Transformation du type-association ternaire de la Figure 10 en un type-entité et trois type associations binaires ............................................................... 15 Figure 12:Modèle de la Figure 10 corrigé au niveau des cardinalités ..................... 15 Figure 13:Modèle E/A de l'exemple illustratif ............................................................... 17 Figure 14:Exemple de relation de schéma Personne ................................................... 20 Figure 15 :Exemple d’une association Etre où toutes les cardinalités maximales sont de 1 ................................................................................................................................. 22 Figure 16:Ici, l’entité Date ne doit pas se matérialiser par une relation. ............... 23 Figure 17:Exemple très simplifié de modélisation entités-associations .................. 23 Chapitre 1 Introduction aux bases de données Objectifs Spécifiques A la fin de ce chapitre, l’étudiant doit être capable de : - Définir une base de données - Définir un Système de Gestion de Base de Données (SGBD) - Enumérer les fonctions d’un SGBD - Différencier entre les niveaux d'abstraction lors de la conception d'une base de données. Plan du chapitre - Introduction - Bases de Données et Systèmes de Gestion de Bases de Données - Système de gestion de base de données (SGBD) Volume horaire - 3 heures 1 Bases de données Introduction aux bases de données Chapitre1. Introduction aux bases de données 1. Introduction Dès le début de l’informatique, les fichiers ont été largement utilisés dans les applications scientifiques et de gestion : fichiers de données statistiques, fichiers de produits, fichiers de clients... De nombreux problèmes apparurent dans la gestion des fichiers tels que la lourdeur d’exploitation, les incohérences, la redondance. Ils ont conduit à l’avènement des bases de données regroupant l’ensemble des fichiers nécessaires à une ou plusieurs applications et gérées par un Système de Gestion de Bases de Données. 2. Bases de Données et Systèmes de Gestion de Bases de Données 2.1. Définition d’une base de données (BD) Une base de données (BD) est un ensemble structuré de données, généralement volumineux et partagé entre plusieurs utilisateurs (ou programmes). Une base de données informatisée est un ensemble structuré de données enregistrées sur des supports accessibles par l’ordinateur, représentant des informations du monde réel et pouvant être interrogées et mises à jour par une communauté d’utilisateurs. La gestion et l’accès à une base de données sont assurés par un ensemble de programmes qui constituent le Système de gestion de base de données (SGBD). Un SGBD est caractérisé par le modèle de description des données qu’il supporte (hiérarchique, réseau, relationnel, objet). 2.2. Modèle de base de données Une BD doit être le reflet d’une réalité ; par exemple, elle peut contenir des données décrivant : Les produits commercialisés par une entreprise, Les fournisseurs de ces produits. La BD contient donc des objets (descripteurs des produits et des fournisseurs) ainsi que des liens entre ces objets (ici, les associations entre les produits et leurs fournisseurs). 2 Bases de données Introduction aux bases de données La BD doit représenter le plus fidèlement possible la réalité. Système de gestion de base de données (SGBD). 2.3. Définition d’un Système de gestion de base de données (SGBD) Un SGBD est un logiciel qui joue le rôle d'interface entre les utilisateurs et la Base de Données. Un SGBD permet de décrire, manipuler et interroger les données d'une Base de Données. Il est chargé de tous les problèmes liés aux accès concurrents, à la sauvegarde et la restauration des données. Il doit de plus veiller au contrôle, à l'intégrité et la sécurité des données. 2.4. Principes de fonctionnement d'un SGBD La gestion et l’accès à une base de données sont assurés par un ensemble de programmes qui constituent le Système de gestion de base de données (SGBD). Un SGBD doit permettre l’ajout, la modification et la recherche de données. Il héberge généralement plusieurs bases de données, qui sont destinées à des logiciels ou des thématiques différents. Un SGBD fournit les fonctionnalités suivantes: Définition du schéma de Langage de définition de données (LDD) données permettant de décrire le système d'information, définir ses structures de données tout en incluant des contraintes. Définition des opérations Langage de manipulation de données (LMD) sur les données pour appliquer des opérations aux données: insertion, modification ou mise-à-jour, destruction et recherche. Partager les données entre Langage de contrôle permettant la gestion plusieurs utilisateurs d'accès concurrents: - Gérer les conflits au cas de mises-à-jour simultanées. - Offrir un mécanisme de retour en arrière si on décide d’annuler des modifications en cours. 3 Bases de données Introduction aux bases de données - Donner une image cohérente des données si l’un fait des requêtes et l’autre des mises-à-jour. Optimiser les performances L’optimiseur, tient compte de l'organisation physique des données et des caractéristiques de la requête pour choisir le meilleur séquencement des opérations. 2.5. Architecture des SGBD Un SGBD (DBMS) est organisé en niveaux. Chaque niveau réalise un certain nombre de fonctions : - Niveau physique ou interne: il regroupe la gestion des données sur mémoire secondaire, du schéma et des index, le partage de données et la gestion de la concurrence d’accès, de la reprise après pannes (fiabilité), de la distribution des données et l'interopérabilité (accès aux réseaux). - Niveau logique ou conceptuel : il regroupe la définition de la structure de données via le Langage de Description de Données (LDD), la consultation et mise à Jour des données via le Langage de Manipulation de Données (LMD), la gestion de la confidentialité (sécurité) et le maintien de l’intégrité. - Niveau externe ou vues d'utilisateurs: il regroupe les vues, l'environnement de programmation (intégration avec un langage de programmation), les Interfaces conviviales, les outils d’aides, les outils de saisie et les outils d’impression d’états. Cette architecture de niveaux a été définie en 1975 par American National Standards Institute (ANSI), Standards Planning And Requirements Committee (SPARC) comme un standard normalisé de toute conception de SGBD. Voici le schéma de l'architecture: 4 Bases de données Introduction aux bases de données Figure 1: Architecture d’un SGBD 2.6. Quelques SGBD Il existe de nombreux systèmes de gestion de bases de données, en voici une liste : - PostgreSQL : http ://www.postgresql.org/ - MySQL : http ://www.mysql.org/ - Oracle : http ://www.oracle.com/ - IBM DB2 : http ://www-306.ibm.com/software/data/db2/ - Microsoft SQL : http ://www.microsoft.com/sql/ - Sybase : http ://www.sybase.com/linux - Informix : http ://www-306.ibm.com/software/data/informix/ 3. Processus de conception d'une base de données Relationnelle Pour concevoir une BD relationnelle, il faut suivre les étapes suivantes 5 Bases de données Introduction aux bases de données Figure 2: Processus de conception d'une base de données Relationnelle 3.1. Cycle de vie d’une base de donnée Le cycle de vie d’une base de donnée est de 4 phases : - Conception de la BD - Implémentation des données (ajout) - Utilisation (Interrogation et MJ) - Maintenance Il est difficile de modéliser un domaine sous une forme directement utilisable par un SGBD. Une ou plusieurs modélisations intermédiaires sont donc utiles, le modèle entités-associations constitue l’une des premières et des plus courantes. Ce modèle, présenté par Chen (1976), permet une description naturelle du monde réel à partir des concepts d’entité et d’association. Ce modèle, utilisé pour la phase de conception, s’inscrit notamment dans le cadre d’une méthode plus générale et très répandue : Merise. MERISE (Méthode d’Étude et de Réalisation Informatique pour les Systèmes d’Entreprise) est certainement le langage de spécification le plus répandu dans la communauté de l’informatique des systèmes d’information, et plus particulièrement dans le domaine des bases de données. Une représentation Merise permet de valider des choix par rapport aux objectifs, de quantifier les solutions retenues, de mettre en œuvre des techniques d’optimisation et enfin de guider jusqu’à l’implémentation. Reconnu comme standard. Un des concepts clés de la méthode Merise est la séparation 6 Bases de données Introduction aux bases de données des données et des traitements. Cette méthode est donc parfaitement adaptée à la modélisation des problèmes abordés d’un point de vue fonctionnel2. Les données représentent la statique du système d’information et les traitements sa dynamique. L’expression conceptuelle des données conduit à une modélisation des données en entités et en associations. 3.2. Avantages de la modélisation conceptuelle Indépendance des technologies (portabilité, longévité, etc.) (Durée de vie) Orienté utilisateur - Compréhensibilité - Support de dialogue concepteurs/utilisateurs - Collaboration et validation par les utilisateurs 3.3. Critères de qualité pour les modèles conceptuels Complétude (Description de tous les phénomènes courants) Fiabilité (formellement défini) Orientation utilisateur (compréhensible, clair et lisible) Orthogonalité (indépendance des concepts) Implémentabilité (traduit dans un SGBD existant) 7 Chapitre 2 Modèle Entité/Associations Objectifs Spécifiques A la fin de ce chapitre, l’étudiant doit être capable de : - Assimiler la sémantique du modèle Entités /Associations - Utiliser le formalisme du modèle Entités /Associations - Distinguer entre les différents types d'attributs - Analyser une étude de cas donné - Modéliser en Entités/Associations Plan du chapitre - Introduction - Schéma entités/associations - Démarche à suivre pour produire un schéma E/A. Volume horaire - 3 heures 8 Bases de données Modèle Entités/Associations Chapitre2. Modèle Entités/Associations 1. Introduction Le modèle entités/associations, généralement noté MEA ou E/A, est conçu en 1976. Il est l’origine des travaux de Chen (USA) et de Tardieu(France). Son succès est dû à plusieurs facteurs dont : Il est un langage graphique Il se base sur deux concepts simples : entités et association. Le MEA est à la base de la plupart des méthodes de conception. La syntaxe employée dans ce support est celle relative à la méthode MERISE principalement utilisée en France. 2. Schéma entités/associations La représentation du modèle entités-associations s’appuie sur trois concepts de base : L’objet ou entité, L’association, La propriété. 2.1. Entité Une entité : est un objet, une chose concrète ou abstraite qui peut être reconnue distinctement et qui est caractérisée par son unicité. Ex : Mael SALAH Type-entité : Un type-entité désigne un ensemble d’entités qui possèdent une sémantique et des propriétés communes. - Exemple : Personne - Exemple d’entités : Articles et Produits peuvent être regroupés par contre Articles et Clients non. Attribut : Un attribut (ou une propriété) est une caractéristique associée à un type-entité ou à un type-association. On présente les attributs par des ellipse s contenants leurs noms. - Exemples d’attribut : le nom d’une personne, le titre d’un livre, la puissance d’une voiture. 9 Bases de données Modèle Entités/Associations Valeur : Au niveau du type-entité ou du type-association, chaque attribut possède un domaine qui définit l’ensemble des valeurs possibles qui peuvent être choisies pour lui (entier, booléen, . . .). Au niveau de l’entité, chaque attribut possède une valeur compatible avec son domaine - Règle 1 : Un attribut ne peut en aucun cas être partagé par plusieurs typeentités ou type-associations. - Règle 2 : Un attribut est une donnée élémentaire, ce qui exclut des données calculées ou dérivées. - Règle 3 : Un type-entité et ses attributs doivent être cohérents entre eux (i.e. ne traiter que d’un seul sujet). Par exemple, si le modèle doit comporter des informations relatives à des articles et à leur fournisseur, ces informations ne doivent pas coexister au sein d’un même type-entité. Il est préférable de mettre les informations relatives aux articles dans un type-entité Article et les informations relatives aux fournisseurs dans un type-entité Fournisseur. Identifiant, clé : Un identifiant (ou clé) d’un type-entité ou d’un type-association est constitué par un ou plusieurs de ses attributs qui doivent avoir une valeur unique pour chaque entité ou association de ce type. Exemples d’identifiant : le numéro de sécurité sociale pour une personne, le numéro d’immatriculation pour une voiture, le code ISBN d’un livre pour un livre (mais pas pour un exemplaire). Figure 3: Représentation graphique d’un exemple de type-entité 2.2. Association ou relation Association : Une association (ou une relation) est un lien entre plusieurs entités. 10 Bases de données Modèle Entités/Associations Type-Association : Un type-association (ou un type-relation) désigne un ensemble de relations qui possèdent les mêmes caractéristiques. Le type-association décrit un lien entre plusieurs type-entités. Les associations de ce type-association lient des entités de ces type-entités. Un attribut peut être placé dans un type-association uniquement lorsqu’il dépend de toutes les entités liées par le type-association. Exemples de type-association : l’emprunt d’un livre à la bibliothèque. Une association est souvent nommée occurrence ou instance de son type-association. Figure 4:Représentation graphique d’un exemple de type-association liant deux type-entités Cardinalité : La cardinalité qui relie un type-association et un type-entité précise le nombre de fois minimal et maximal d’interventions d’une entité du type-entité dans une association du type association. La cardinalité minimale doit être inférieure ou égale à la cardinalité maximale. Exemple de cardinalité : une personne peut être l’auteur de 0 à n livre, mais un livre ne peut être écrit que par une personne. Figure 5:Représentation graphique des cardinalités d’un type-association. 11 Bases de données Modèle Entités/Associations Type de Cardinalités: Les cardinalités utilisées sont: o 0,1 au plus un(e) o 1,1 un(e) et un(e) seul(e) o 1,n un(e) ou plusieurs o 0,n zéro ou plusieurs 2.3. Types d’associations 2.3.1. Associations plurielles Deux mêmes entités peuvent être plusieurs fois en association. Figure 6:Exemple d’associations plurielles Dans cet exemple issu d’une agence immobilière, une personne peut être propriétaire, résider principalement ou résider secondairement dans un logement géré par l’agence. Les logements qui ne sont pas gérés par l’agence ne figurent pas dans l’entité des logements, ce qui explique certaines cardinalités 0 du schéma. Nous supposons également qu’un logement n’est détendu que par une seule personne et que ce propriétaire figure obligatoirement dans l’entité des personnes. 2.3.2. Association réflexive Une association réflexive est une association reliant des occurrences de la même entité. Ces associations sont quasiment toujours binaire (2 branches). 12 Bases de données Modèle Entités/Associations Pour lire une association réflexive, il est faut connaître le rôle attribué à chaque branche de l'association, c'est-à-dire le rôle de chaque occurrence dans le lien. Figure 7:Exemple d’associations réflexives sur le type-entité Employés Dans cet exemple, tout employé est dirigé par un autre employé (sauf le directeur général) et un employé peut diriger plusieurs autres employés. 2.3.3. Association ternaire Une association peut relier plus de 2 entités ensemble, le plus souvent trois. On parle alors d'association ternaire (dimension 3). On utilise une association ternaire quand on a besoin de connaître une occurrence de chaque entité pour avoir une information. Exemple: Figure 8:Exemple d’association ternaire Le type-association ternaire Contient associant les type-entités Facture, Produit et Client représenté sur la Figure 8 est inapproprié puisqu’une facture donnée est toujours adressée au même client. En effet, cette modélisation implique pour les associations (instances du type-association) Contient une répétition du numéro de client pour chaque produit d’une même facture. 13 Bases de données Modèle Entités/Associations Figure 9:Type-association ternaire de la figure 8 corrigé en deux type-associations binaires. La solution consiste à éclater le type-association ternaire Contient en deux typeassociations binaires comme représenté sur la Figure 9 Imaginons un autre exemple de type-association ternaire Vol liant trois type-entités Avion, Trajet et Pilote comme représenté sur la Figure 10 Figure 10:Modèle représentant un type-association ternaire Vol liant trois type-entités Avion, Trajet et Pilote La transformation consistant à supprimer le type-association ternaire du modèle de la Figure 10 produit le modèle de la Figure 11. Ce modèle fait immédiatement apparaître une erreur de conception qui était jusque là difficile à diagnostiquer : généralement, à un vol donné sont affectés plusieurs pilotes (par exemple le commandant de bord et un copilote) et non pas un seul. Le modèle correct modélisant cette situation est celui de la Figure 12 où le type-entité Vol ne peut être transformé en un type-association ternaire Vol comme sur la figure 9 14 Bases de données Modèle Entités/Associations Figure 11:Transformation du type-association ternaire de la Figure 10 en un type-entité et trois type associations binaires Figure 12:Modèle de la Figure 10 corrigé au niveau des cardinalités 3. Démarche à suivre pour produire un schéma E/A 3.1. Démarche Afin de pouvoir produire un schéma E/A relatif aux spécifications d'une étude de cas ,on procède comme suit: 1. Recueil des besoins et identification des différents attributs. 2. Regrouper les attributs par entités. 3. Identifier les associations entre les entités ainsi que les attributs y associés 4. Evaluer les cardinalités des associations. Spécifications: Les clients sont caractérisés par un numéro de client, un non ,un prénom, une adresse postale et un numéro de téléphone .Il commandent une quantité donnée des produits à une date donnée. Les produits sont caractérisés par un code de produit, un libellé , un prix unitaire ,quantité -seuil et une quantité de stock. 15 Bases de données Modèle Entités/Associations Chaque produit est fourni par un fournisseur unique (mais un fournisseur peut fournir plusieurs produits ). Les fournisseurs sont caractérisés par un code de fournisseur ,un nom, une adresse postale et un numéro de téléphone Solutions: Les différent attributs associés à ces spécifications peuvent être résumés comme suit : Nom de l'attributs Nci-cl Nom Prénom AdrCl Num-telCl Num-cmd Date_cmd Etat Code-prd Libelle Prix Qte-seuil Qtesctk Qte-cmd Num-lg-cmd Code-f NomF AdresseF Désignation de l'attribut Numéro de client Nom du client Prénom du client Adresse postale de client Numéro de téléphone du Numéro de la commande Date de la commande Etat de la commande Code du produit Libellé du produit Prix unitaire du produit Quantité -seuil Quantité du stock Quantité de produits commandés Numéro d'une ligne de commande Code du fournisseur Nom du fournisseur Adresse postale du fournisseur Num-telF Numéro de téléphone de fournisseur Les entités avec leurs attributs sont: Nom de l'entité Client Produit Fournisseur Commande Attributs de l'entité Nci-cl, Nom, Prénom, AdrCl ,Num-telCl Code-prd, Libelle, Prix, Qte-seuil, Qtesctk Code-f, NomF, AdresseF, Num-telF Num-cmd, Date_cmd, Etat,Qte-cmd Les associations entre les entités : Nom de l'association Entités participantes lance contient LigneC fournit Client et Commande Produit et Ligne_cmde Commande et Ligne-Cmde Produit et Fournisseur Attributs associés Qte-cmd 16 Bases de données Modèle Entités/Associations Enfin, le modèle E/A se présente comme suit : Figure 13:Modèle E/A de l'exemple illustratif 17 Chapitre 3 Le modèle relationnel Objectifs Spécifiques A la fin de ce chapitre, l'étudiant doit être capable de: - Apprendre les notions de base du modèle relationnel - Identifier les correspondances avec le modèle E/A - Comprendre l’utilité du modèle relationnel - Etre capable de traduire un modèle E/A en un modèle relationnel Plan du chapitre - Introduction - Introduction au modèle relationnel - Passage du modèle entités-associations au modèle relationnel Volume horaire - 3 heures 18 Bases de données Le modèle relationnel Chapitre 3. Le modèle relationnel 1. Introduction au modèle relationnel 1.1. Présentation C'est un modèle LOGIQUE de donnée (MLD), celui qui correspond à l'organisation des données dans les bases de données relationnelles. Un modèle relationnel est composé de relations, encore appelée tables. Ces tables sont décrites par des attributs ou champs (noms de colonnes). Pour décrire une relation, on indique tout simplement son nom en majuscule, suivi du nom de ses attributs entre parenthèses. L'identifiant d'une relation est composé d'un ou plusieurs attributs qui forment la clé primaire. Une relation peut faire référence à une autre en utilisant une clé étrangère, qui correspond à la clé primaire de la relation référencée. Il n'y a pas de notation officielle pour repérer les clés primaires et étrangères. C'est à vous d'en adopter une et de l'expliquer en légende. Une notation que nous utiliserons: On souligne la clé primaire d'un seul trait On fait précéder (ou suivre) les clés étrangères du symbole # Chaque ligne (tuple ou enregistrement) d'une table représente une occurrence de l'entité ou de l'association correspondante. 1.2. Éléments du modèle relationnel Un attribut est un identificateur (un nom) décrivant une information stockée dans une base. Exemples d’attribut : l’âge d’une personne, le nom d’une personne, le numéro de sécurité sociale. Domaine: Le domaine d’un attribut est l’ensemble, fini ou infini, de ses valeurs possibles. Par exemple, l’attribut numéro de sécurité sociale a pour domaine l’ensemble des combinaisons de quinze chiffres et nom a pour domaine l’ensemble des combinaisons de lettres (une combinaison comme 19 Bases de données Le modèle relationnel cette dernière est généralement appelée chaîne de caractères ou, plus simplement, chaîne). Relation: Une relation est un sous-ensemble du produit cartésien de n domaines d’attributs (n > 0). Une relation est représentée sous la forme d’un tableau à deux dimensions dans lequel les n attributs correspondent aux titres des n colonnes. Schéma de relation: Un schéma de relation précise le nom de la relation ainsi que la liste des attributs avec leurs domaines. N°Sécu 3543333 3453337 1433546 9735642 Nom Touati Salah Jrad Salah Prénom Rabeb Wiem Ismail Roua Figure 14:Exemple de relation de schéma Personne Degré: Le degré d’une relation est son nombre d’attributs. Occurrence ou n-uplets ou tuples: Une occurrence, ou n-uplets, ou tuples, est un élément de l’ensemble figuré par une relation. Autrement dit, une occurrence est une ligne du tableau qui représente la relation. Cardinalité: La cardinalité d’une relation est son nombre d’occurrences. Clé primaire: La clé primaire d’une relation est une de ses clés candidates. Pour signaler la clé primaire, ses attributs sont généralement soulignés. Clé étrangère: Une clé étrangère dans une relation est formée d’un ou plusieurs attributs qui constituent une clé primaire dans une autre relation. Schéma relationnel: Un schéma relationnel est constitué par l’ensemble des schémas de relation. Base de données relationnelle: Une base de données relationnelle est constituée par l’ensemble des n-uplets des différentes relations du schéma relationnel. 20 Bases de données Le modèle relationnel 2. Passage du modèle entités-associations au modèle relationnel 2.1. Règles de passage 2.1.1. Transformation des entités Toute entité devient une relation ayant pour clé primaire son identifiant. Chaque propriété se transforme en attribut. Personne (N°securiteSocial,Nom,Prénom,Adresse) 2.1.2. Transformation des relation binaires de types(x,1)-(x,1) L’identifiant de E1 est utilisé comme clé étrangère dans la relation E2 ou inversement. Les propriétés de l’association suivent la clé étrangère E1(idE1,…) E2(idE2, #idE1,…) 2.1.3. Transformation des relation binaires de types (x,n)-(x,1) 21 Bases de données Le modèle relationnel L’identifiant de E2 est utilisé comme clé étrangère dans la relation E1. Les propriétés de l’association suivent la clé étrangère E1(idE1, …, #idE2,attri…) E2(idE2, ,…) 2.1.4. Règle 3 : Transformation des relation binaires de types (x,n)-(x,m) L’association donne lieu à une relation. La clé primaire de la relation A1 est composée des clés des relations E1 et E2. E1(idE1,…) E2(idE2,…) A1(idE1, idE2, attri,…) 2.2. Exemple d’une association du type 1 vers 1 Figure 15 :Exemple d’une association Etre où toutes les cardinalités maximales sont de 1 L’application des règles de passage du modèle entités-associations au modèle relationnel énoncées ci-dessus nous donnerait : – Citoyen(Num-Citoyen, #Num-Candidat, Nom, Prénom, Adresse) – Candidat (Num-Candidat, Parti) 2.3. Cas particulier d’un type-entité sans attribut autre que sa clé Lorsqu’une entité ne possède pas d’attribut en dehors de sa clé, il ne faut pas nécessairement en faire une relation. 22 Bases de données Le modèle relationnel Figure 16:Ici, l’entité Date ne doit pas se matérialiser par une relation. Par exemple, l’entité Date ne doit pas se traduire par une relation. Le schéma relationnel adéquat correspondant au modèle entités-associations de la Figure 16 est donc : – Exemplaire(Num-Exemplaire, date-achat) – Personne(Num-Personne, nom, prénom, adresse) – Emprunter(Num-Exemplaire, Num-Personne, Date, date-retour) 2.4. Exemple Figure 17:Exemple très simplifié de modélisation entités-associations Comme exemple d’application, voici les relations déduites du schéma entitésassociations de la Figure 17 : – Patient(Num-Patient, Nom-Patient, Num-Mutuelle) – Mutuelle(Num-Mutuelle, Nom-Mutuelle) – Médecin(Num-Médecin, Nom-Médecin, Prénom-Médecin) – Affection(Num-Affection, Nom-Affection) – Hospitaliser(Num-Patient, Num-Affection, Num-Médecin, Date-Entrée,Chambre, Durée-Hospitalisation) 23 Bases de données Le modèle relationnel Chapitre 4 Dépendance fonctionnelle et Normalisation Objectifs Spécifiques A la fin de ce chapitre, l'étudiant doit être capable de: - Dégager les dépendances fonctionnel - Normaliser une relation Plan du chapitre - Introduction - Dépendances fonctionnelles et graphe des dépendances fonctionnelles - Notions de base - Première et deuxième formes normales - Troisième forme normale - Relations à plusieurs identifiants: forme normale de Boyce Codd - Dépendances multi-ensembles - Quatrième forme normale - Conclusion Volume horaire - 3 heures 24 Bases de données Dépendance fonctionnelle et Normalisation Chapitre 4. Dépendance fonctionnelle et Normalisation 1. Introduction Pour qu’un modèle relationnel soit normalisé, il faut qu’il respecte certaines contraintes appelées les formes normales. Les formes normales sont différents stades de qualité qui permettent d’éviter la redondance dans les bases de données relationnelles afin d’éviter ou de limiter : les pertes de données, les incohérences au sein des données, l’effondrement des performances des traitements. Elles s’appuient sur les dépendances fonctionnelles entre attributs. La normalisation d’une base de données est une manifestation observable des dépendances observées dans le monde réel. La dépendance fonctionnelle permet de définir les premières formes normales jusqu’à la forme normale de Boyce-Codd (1FN, 2FN, 3FN et BCNF). 2. Dépendances fonctionnelles et graphe des dépendances fonctionnelles La notion de dépendance fonctionnelle est introduite dès le début du relationnel afin de caractériser des relations pouvant être décomposées sans perte d’informations. 2.1. Définition Deux données sont en dépendances fonctionnelles (DF), si la connaissance d’une valeur de la première (source) permet de déterminer la connaissance d’une et une seule valeur de la seconde (but). Exemple : pour la relation Produit (NP, NomP, Poids, Couleur), il y a les DF suivantes en supposant qu'il n'existe pas deux produits de même nom : NP NomP, NomPNP, NPPoids NomPPoids, NP Couleur, NomPCouleur NP NomP, Poids, Couleur) (NP, NomP) Poids, (NP, NomP) Couleur … 25 Bases de données Dépendance fonctionnelle et Normalisation 2.2. DF Élémentaire une DF, X B, est une dépendance fonctionnelle élémentaire si B est un attribut unique, et si X est un ensemble minimum d'attributs (ou un attribut unique). Exemples : Dans la relation Produit, les DF : NP (couleur, poids) et (NP, NomP) Poids ne sont pas élémentaires. Mais les DF : NPCouleur, NP Poids, NP NomP NomPCouleur, NomPPoids, NomPNP sont élémentaires. La DF : (NP, NF, date) Qté de la relation Livraison est élémentaire. Chaque DF traduit un fait du monde réel. Les DF élémentaires traduisent des faits atomiques. La DF, NP Couleur, signifie que chaque produit, identifié par son numéro, est d'une seule couleur. La DF, (NP, NF, date) Qté, signifie qu'un même fournisseur ne peut livrer plusieurs fois le même jour le même produit avec des quantités différentes. 2.3. DF totale / Partielle Un attribut B est lié par une dépendance fonctionnelle totale à un attribut A, si aucune partie de A ne détermine B. Dans le cas contraire, on dit qu’il s’agit d’une dépendance fonctionnelle partielle. Exemple : - C, D, E B : Dépendance fonctionnelle totale - Si seulement C et D B la dépendance fonctionnelle est alors partielle - Si l’attribut de gauche n’est pas composé (élémentaire), la dépendance est totale. 2.4. DF directe / transitive Un attribut B est lié par une dépendance fonctionnelle directe à un attribut A, s’il n’existe pas un attribut C tel que A C et C B. Dans le cas contraire, on dit qu’il s’agit d’une dépendance fonctionnelle Transitive. 26 Bases de données Dépendance fonctionnelle et Normalisation 2.5. Propriétés des dépendances fonctionnelles - La réflexivité :AA et s'il existe B ⊆ A alors AB L'augmentation : Si AB et qu'il existe C / A ⊆ C donc C-B - La transitivité : Si AB et BC alors AC - La pseudo-transitivité : Si AB et C, BD alors A, CD - L'union :union AB et AC alors AB,C La fermeture transitive F+ d'un ensemble F de dépendances fonctionnelles est l'ensemble des DF qui peuvent être produites par application des axiomes d'Amstrong sur l'ensemble F. 2.6. Dépendance fonctionnelle élémentaire Une DF est dite élémentaire (AB), si et seulement si, il n'existe aucun A'⊆ A / A'B Exemple : Cod_emp, Cod_dip desc_dip : n'est pas élémentaire car Cod_dip desc_dip 2.7. Dépendance fonctionnelle directe Une DF AB est dite directe si et seulement s’il n'existe aucun attribut X de R tel que AX et XB. Exemple : Cod_p taux_tva : n'est pas directe car Cod_p cod_tva et cod_tva taux_tva 3. Forme normale Une forme normale désigne un type de relation particulier entre les entités. La forme normale stipule que les valeurs des différents attributs soient bien en dépendance fonctionnelle avec la clé primaire (complètement déterminés par la clé primaire). Les différentes formes normales sont dépendantes et structurées. En effet une relation ne peut être en deuxième forme normale que si elle est déjà en première forme normale. 3.1. Première forme normale Une relation R est dite en 1FN si et seulement si : 27 Bases de données Dépendance fonctionnelle et Normalisation 1FN Elle possède une clé. Tous ses attributs sont atomiques ---> Un attribut atomique est un attribut n'ayant à un instant donné qu'une seule valeur et ne regroupant pas un ensemble de valeurs. 3.2. Deuxième forme normale Une relation R est dite en deuxième forme normale si et seulement si : 2FN Elle est en 1FN. Toutes ses DF sont élémentaires. ---->Tout attribut non clé est totalement dépendant de toute la clé. Autrement dit, aucun des attributs ne dépend que d’une partie de la clé. La 2FN n'est à vérifier que pour les relations ayant une clé composée. Une relation en 1FN n'ayant qu'un seul attribut clé est toujours en 2FN. 3.3. Troisième forme normale Une relation est en 3FN si et seulement si : 3FN Elle est en 2FN. Toutes ses DF sont directes. ----> Tout attribut doit dépendre directement de la clé, c'est-à-dire qu’aucun attribut ne doit dépendre de la clé par transitivité. Autrement dit, aucun attribut ne doit dépendre d’un autre attribut non clé. 28 Bases de données Dépendance fonctionnelle et Normalisation Exemple1 : R(k, x,y,z) kxyz xz =>R1(k,x,y) kxy R2(x,z) xz x non clé, z non clé 3.4. La forme normale de BOYCE-CODD Une relation est dite en BCNF si et seulement si : BCNF Elle est en 3FN. Tout attribut non clé de la relation n'est pas source de DF vers une partie de la clé. 4. Conclusion Si l’une des 3 règles n’est pas vérifiée, cela indique une erreur dans le modèle relationnel et il faut alors modifier pour que les 3 règles soient vérifiées pour toutes les relations. On vérifie les règles dans l’ordre. Si la première forme normale n’est pas respectée, pas la peine de vérifier la 2FN. Et si la 2FN n’est pas vérifiée, inutile de vérifier la 3FN. Il existe d’autres formes normales mais on admet couramment que ces formes normales sont suffisantes pour permettre de construire des modèles fiables et cohérents. Modèle normalisé = relations avec : Une clé, qui permet de distinguer chaque occurrence Des attributs élémentaires (1FN) En dépendance de TOUTE la clé (2FN), Et RIEN QUE de la clé (3FN) 29 Chapitre 5 L'Algèbre Relationnelle Objectifs Spécifiques A la fin de ce chapitre, l'étudiant doit être capable de: - Reconnaître l'utilité des opérateurs ensemblistes et spécifiques - Analyser des requêtes plus au moins complexes - Appliquer les opérateurs appropriés dans l'expression des requêtes Plan du chapitre - Introduction - Les opérations spécifiques - Les opérations ensemblistes - Expressions d'algèbre Volume horaire - 3 heures 30 Bases de données L'algèbre Relationnelle Chapitre 5. L’algèbre Relationnelle 1. Introduction C’est une collection d’opérations permettant d’exprimer des requêtes sur une BD. Elle permet de sélectionner certains enregistrements d’une relation satisfaisant une condition ou encore de regrouper des enregistrements de relations différentes. Le résultat de toute opération de l’algèbre est une nouvelle relation. Cette propriété implique notamment qu’il n’y a pas de doublons dans le résultat et permet l’écriture d’expressions de calcul. Étant donnée, que le modèle relationnel est basé sur la théorie des ensembles, l’algèbre relationnelle utilise les opérateurs classiques de manipulation des ensembles (opérations ensemblistes) : union, intersection, différence et produit cartésien et introduit des opérateurs spécifiques aux BD (opérations spécifiques) : sélection, projection et jointure 2. Les opérations spécifiques 2.1. Projection (π) But : Ne retenir que certains attributs dans une relation Syntaxe : π [attributs] R Sémantique : Créer une nouvelle relation de population l’ensemble des tuples de R réduits aux seuls attributs de la liste spécifié. Schéma (résultat) < = Schéma (R) . Nbre tuples(résultat) < = Nbre tuples(R). L’effet de bord de la projection vient de l’élimination des doubles. Bases de données L'algèbre Relationnelle Exemple : Soit la relation Personne ( nom , prénom , jour-nais , mois-nais , an-nais , sexe) Dupont Talon Rochat Martin Picard Martin Jean Achille Marie Régis Anne Jules 30 20 13 27 10 05 07 11 05 03 10 03S 72 75 72 74 76 74 M M F M F M On construit l'ensemble des noms et prénoms des personnes avec l'opération: NP := π [nom, prénom] Personne On obtient :NP ( nom , prénom ) Dupont Talon Rochat Martin Picard Martin L'opération : NA := Jean Achille Marie Régis Anne Jules π [nom, an-nais] Personne ,donnera en résultat : NA ( nom , an-nais ) Dupont Talon Rochat Martin Picard 72 75 72 74 76 soit une population de 5 tuples au lieu des 6 tuples de la relation Personne. 2.2. Sélection (σ) Cet opérateur construit une relation résultat où n'apparaissent que certains tuples de la relation opérande (en termes de tableau, cela revient à extraire certaines Bases de données L'algèbre Relationnelle lignes). Les tuples retenus sont ceux satisfaisant une condition explicite, appelée prédicat de sélection. Définition: Soit R (A1, A2, ...., An) une relation, la sélection de R selon un prédicat p, notée: σ[p] R crée une nouvelle relation, temporaire, de schéma identique à celui de R, et de population l'ensemble des tuples de R pour lesquels le prédicat p est vrai. Exemple: Pour créer une relation Femmes contenant l'ensemble des personnes de sexe féminin, on écrira: Femmes := σ [sexe = 'F'] Personne ce qui donne en résultat : Femmes ( nom, prénom , jour-nais , mois-nais , an-nais , sexe) Rochat Picard Marie Anne 13 10 05 10 72 76 F F 2.2. La jointure (⋈) Les lignes de la relation résultat sont formées par la concaténation des tuples des relations d’origine qui vérifient la condition de jointure. Cette condition porte sur l’égalité d’un ou de plusieurs attributs. Relation 1 ⋈condition Relation 2 ou bien jointure (R1,R2,condition) Exemple : Personne Nom Prénom Année_naiss Sexe Dupont Jean 62 M Talent Achille 65 M Martin Anne 63 F Martin Régis 65 M Durant Marie 64 F Bases de données L'algèbre Relationnelle Q1 : Afficher toutes les informations concernant les personnes étudiantes Personne ⋈Nom=Nom_E ,Prénom=Prénom_E Etudiant Etudiant = Num- Nom Prénom Année_naiss Age Sexe 136 etud Talent Achille 65 40 M 101 Martin Régis 65 40 M 253 Durant Marie 64 41 F 4. Les opérations ensemblistes 3.1. L’union (∪) L’union porte sur deux relations de même schéma et produit une relation contenant les tupes de la 1ère relation et ceux de la 2ème relation. Exemple : Fournisseur 1 Code Nom Adresse 11 1 1 001 IBM TUNIS 002 HP TUNIS 004 ALCATEL FRANCE Fournisseur 2 Code Nom Adresse 11 1 1 003 Simens Allemagne 005 Sagem TUNIS ∪ = Bases de données L'algèbre Relationnelle Fournisseur Code Nom Adresse 11 1 1 001 IBM TUNIS 002 HP TUNIS 004 ALCATEL FRANCE 003 Simens Allemagne 005 Sagem TUNIS 3.2. La différence (-) La différence porte sur deux relations de même schéma et produit une relation contenant les tupes qui existent dans la 1ère relation et pas dans la 2ème relation. Exemple : Fournisseur 1 Code Nom Adresse 11 1 1 001 IBM TUNIS 002 HP TUNIS 004 ALCATEL FRANCE Fournisseur 2 Code Nom Adresse 11 1 1 003 Simens Allemagne 004 ALCATEL FRANCE 005 Sagem TUNIS Fournisseur Code Nom Adresse 11 1 1 001 IBM TUNIS 002 HP TUNIS - = 35 Bases de données L'algèbre Relationnelle 3.3. L’intersection (∩) L’intersection porte sur deux relations de même schéma et produit une relation contenant les tuples qui existent à la fois dans la 1ère relation et dans la 2ème relation. Exemple : Fournisseur 1 Code Nom Adresse 11 1 1 001 IBM TUNIS 002 HP TUNIS 004 ALCATEL FRANCE Fournisseur 2 Code Nom Adresse 11 1 1 003 Simens Allemagne 004 ALCATEL FRANCE 005 Sagem TUNIS Fournisseur Code Nom Adresse 11 1 1 004 ALCATEL FRANCE ∩ = 3.4. Produit cartésien () Le produit cartésien à comme but de construire toutes les combinaisons de tuples des deux relations. 36 Bases de données L'algèbre Relationnelle Exemple : Personne Nom Prénom Fourt Lisa Juny Carole Article Prix livre 45 poupée 25 montre 87 Cadeau = Personne Cadeau Nom Prénom Article Prix Fourt Lisa livre 45 Fourt Lisa poupée 25 Fourt Lisa montre 87 Juny Carole livre 45 Juny Carole poupée 25 Juny Carole montre 87 5. Expressions d'algèbre Les opérateurs de l'algèbre peuvent être combinés dans des expressions pour exprimer des requêtes non élémentaires. Exemple: On obtient la liste des noms et prénoms des hommes nés avant 1975 par l’expression : H := π [nom, prénom] σ [sexe = 'M' an-nais < 75] Personne H(non,prénom) Dupont Martin Martin Jean Régis Jules 37 Chapitre 6 Le Langage SQL Objectifs Spécifiques A la fin de ce chapitre, l'étudiant doit être capable de: - Apprendre à créer une base de données en tenant compte des contraintes d'intégrité - Savoir ajouter ,modifier ,supprimer des enregistrements d'une table - Construire des requêtes d'interrogations correspondant à des critères plus ou moins complexes - Comprendre la notion des vues et savoir les manipuler - Savoir la normalisation de SQL - Appliquer des droits d'accès à une base de données Plan du chapitre - Introduction - Introduction - Catégories d’instructions - SQL en tant que LDD - SQL en tant que LMD - Interrogation de la base de données en LMD Volume horaire - 3 heures 38 Base de donnée Le langage SQL Chapitre 6. Le langage SQL 1. Introduction Le langage SQL (Structured Query Language) peut être considéré comme le langage d’accès normalisé aux bases de données. Il est aujourd’hui supporté par la plupart des produits commerciaux que ce soit par les systèmes de gestion de bases de données micro tel que Access ou par les produits plus professionnels tels que Oracle. Le succès du langage SQL est dû essentiellement à sa simplicité et au fait qu’il s’appuie sur le schéma conceptuel pour énoncer des requêtes en laissant le SGBD responsable de l’exécution. Le langage SQL propose un langage de requêtes ensembliste. Néanmoins, le langage SQL ne possède pas la puissance d’un langage de programmation : entrées/sorties, instructions conditionnelles, boucles et affectations. Pour certains traitements il est donc nécessaire de coupler le langage SQL avec un langage de programmation plus complet. De manière synthétique, on peut dire que SQL est un langage relationnel, il manipule donc des tables (des relations) par l’intermédiaire de requêtes qui produisent également des tables. Terminologie: Modèle Relationnel Standard SQL Relation Table Attribut Champ ou colonne Identifiant ou clé Clé primaire primaire Clé étrangère Clé étrangère Tuple ou ligne occurrence 2. Catégories d’instructions Les instructions SQL sont regroupées en catégories en fonction de leur utilité et des entités manipulées. On peut distinguer cinq catégories, qui permettent: 39 Base de donnée Le langage SQL - La définition des éléments d’une base de données (tables, colonnes, clefs, index, contraintes, . . .) via le Langage de Définition de Données (LDD). - La manipulation des données (insertion, suppression, modification, extraction, . . .) via le Langage de Manipulation de Données (LMD). - La gestion des droits d’accès aux données via le Langage de Protection d’Accès (LPA): acquisition et révocation des droits d'accès aux tables. - La gestion des transactions via le Langage de Contrôle des Transactions (LCT): gérer la validation et l’annulation des modifications faites sur les données. - Le SQL intégré qui permet d’utiliser SQL dans un langage de troisième génération (C, Java, Cobol, etc.) 3. SQL en tant que LDD 3.1. Création de table 3.1.1. Syntaxe La création de tables se fait à l'aide du couple de mots-clés CREATE TABLE. La syntaxe de définition d'une table est la suivante : Syntaxe : CREATE TABLE Nom_de_la_table ( Nom_de_colonne1 Type_de_donnée, Nom_de_colonne2 Type_de_donnée, ...); 3.1.2. Les types de données Pour chaque colonne que l'on crée, il faut préciser le type de données que le champ va contenir. Celui-ci peut être un des types suivants : Type de donnée Type Syntaxe char(n) alphanumérique Type alphanumérique Type numérique varchar(n) number(n,[d]) Description Chaîne de caractères de longueur fixe n (n<16383) Chaîne de caractères de n caractères maximum (n<16383) Nombre de n chiffres [optionnellement d après la virgule] 40 Base de donnée Le langage SQL Type numérique Smallint Entier signé de 16 bits (-32768 à 32757) Type numérique integer Entier signé de 32 bits (-2E31 à 2E31-1) Type numérique float Nombre à virgule flottante Type horaire date Date sous la forme jj/mm/aa Type horaire timestamp Date et Heure 3.1.3. Création de la contrainte de clé primaire La clé primaire se définit grâce à la clause PRIMARY KEY suivie de la liste de colonnes, séparées par des virgules, entre parenthèses. Ces colonnes ne peuvent alors plus prendre la valeur NULL et doivent être telles que deux lignes ne puissent avoir simultanément la même combinaison de valeurs pour ces colonnes. Lors de la création d’une table, il n’y aura la création de la contrainte clé primaire qu’une seule fois, ceci veut dire que si la clé primaire est formée de plusieurs champs, alors il y aura une seule contrainte avec regroupement des champs. Syntaxe :constraint pk_nomTable PRIMARY KEY (colonne1 [, colonne2, ...]) 3.1.4. Création de la contrainte de clé étrangère La clé étrangère est définie grâce à la clause FOREIGN KEY suivie de la liste de colonnes de la table en cours de définition, séparées par des virgules, entre parenthèses, puis de la clause REFERENCES suivie du nom de la table étrangère et de la liste de ses colonnes correspondantes, séparées par des virgules, entre parenthèses. Lors de la création d’une table contenant des clés étrangères, il y aura autant de création de contrainte que de nombre de clé(s) étrangère(s). Syntaxe : constraint fk_nomTable FOREIGN KEY (colonne) REFERENCES Nom_de_la_table_etrangere(colonne) 41 Base de donnée Le langage SQL 3.1.5. Création de la contrainte de domaine Contrainte de non nullité Le mot clé NOT NULL permet de spécifier qu'un champ doit être saisi, c'est-à-dire que le SGBD exige à ce qu’on insère une valeur à un champ comportant la clause NOT NULL. Contrainte de valeur par défaut Le langage SQL permet de définir une valeur par défaut à un champ de la base grâce à la clause DEFAULT. Cela permet de garantir qu'un champ ne sera pas vide. La clause DEFAULT doit être suivie par la valeur à affecter. Cette valeur peut être un des types suivants: constante numérique constante alphabétique (chaîne de caractères) constante Sysdate (date système) Contrainte d’unicité La clause UNIQUE permet de vérifier que la valeur saisie pour un champ n'existe pas déjà dans la table. Cela permet de garantir que toutes les valeurs d'une colonne d'une table seront différentes. Il est à signaler qu’il y aura autant de contraintes « unique » que de colonnes définies en tant que unique. Syntaxe : constraint uniq_nom Table UNIQUE (colonne) Contrainte check Il est possible de faire un test sur un champ grâce à la clause CHECK() comportant une condition logique portant sur une valeur entre les parenthèses. Si la valeur saisie est différente de NULL, le SGBD va effectuer un test grâce à la condition logique. Le test avec check se base sur des opérateurs. Ils sont résumés dans ce qui suit : Opérateurs logiques AND, OR, NOT Comparateurs de chaines IN, BETWEEN, LIKE Opérateurs arithmétiques +, -, *, /, % Comparateurs arithmétiques =, !=(<>), >, <, >=, <= 42 Base de donnée Le langage SQL 3.1.6. Création avec Insertion de données On peut insérer des données dans une table lors de sa création par la commande suivante : CREATE TABLE nom_table [(nom_col1, nom_col2, ...)] AS SELECT ... En un seul ordre SQL, on peut créer une table et la remplir avec des données provenant du résultat d’un SELECT. Cette instruction sera vue ultérieurement. 3.2. Modification de la structure d’une table Action Standard SQL Ajout ou modification de colonne ALTER TABLE nom_table {ADD/MODIFY}([nom_colonne type[contrainte], ...]) ; ALTER TABLE nom_table Ajout d'une contrainte de table ADD CONSTRAINT nom_contrainte contrainte ; Renommage d’une colonne ALTER TABLE nom_table RENAME COLUMN ancien_nom TO nouveau_nom ; ALTER TABLE nom_table Renommage d’une table RENAME TO nouveau_nom ; ALTER TABLE Nom_de_la_table Suppression de colonne DROP COLUMN Nom_de_la_colonne ; Suppression d’une contrainte ALTER TABLE Nom_de_la_table DROP constraint Nom_Contrainte ; 3.3. Suppression de table Supprimer une table revient à éliminer sa structure et toutes les données qu'elle contient. La syntaxe est la suivante : DROP TABLE nom_table ; Si une table a des contraintes d’intégrité référentielles, il faut tout d’abord les supprimer avant de supprimer la table. 43 Base de donnée Le langage SQL 3.4. La création des vues Une vue est une table virtuelle, c'est-à-dire dont les données ne sont pas stockées dans une table de la base de données, et dans laquelle il est possible de rassembler des informations provenant de plusieurs tables. On parle de "vue" car il s'agit simplement d'une représentation des données dans le but d'une exploitation visuelle. Les données présentes dans une vue sont définies grâce à une clause SELECT. La création d'une vue se fait grâce à la clause CREATE VIEW suivie du nom que l'on donne à la vue, puis du nom des colonnes dont on désire agrémenter cette vue (il faut autant de redéfinitions de colonne qu'il y en aura en sortie), puis enfin d'une clause AS précédant la sélection. La syntaxe d'une vue ressemble donc à ceci : CREATE VIEW Nom_de_la_Vue (colonnes) AS SELECT ... Voici ce que cela pourrait donner : CREATE VIEW Vue (colonneA, colonneB, colonneC, colonneD) AS SELECT colonne1,colonne2,colonneI,colonneII FROM Nom_table1 Alias1,Nom_tableII AliasII WHERE Alias1.colonne1 = AliasII.colonneI AND Alias1.colonne2 = AliasII.colonneII Les vues ainsi créées peuvent être l'objet de nouvelles requêtes en précisant le nom de la vue au lieu d'un nom de table dans un ordre SELECT... La vue représente de cette façon une sorte d'intermédiaire entre la base de données et l'utilisateur. Cela à de nombreuses conséquences : 44 Base de donnée Le langage SQL une sélection des données à afficher une restriction d'accès à la table pour l'utilisateur, c'est-à-dire une sécurité des données accrue un regroupement d'informations au sein d'une entité Exemple : créer une vue sur les bons clients dont le chiffre d’affaire est supérieur à 1000 Create view bon_client as select * from client where ca >1000; A partir de cet instant pour afficher tous les bons clients (vérifiant la contrainte), il suffit d’écrire : Select * from bon_client ; 3.5. Création d'index Un index est un objet complémentaire (mais non indispensable) à la base de données permettant d'"indexer" certaines colonnes dans le but d'améliorer l'accès aux données par le SGBDR, au même titre qu'un index dans un livre ne vous est pas indispensable mais vous permet souvent d'économiser du temps lorsque vous recherchez une partie spécifique de ce dernier... Toutefois la création d'index utilise de l'espace mémoire dans la base de données, et, étant donné qu'il est mis à jour à chaque modification de la table à laquelle il est rattaché, peut alourdir le temps de traitement du SGBDR lors de la saisie de données. Par conséquent il faut que la création d'index soit justifiée et que les colonnes sur lesquelles il porte soient judicieusement choisies (de telle façon à minimiser les doublons). De cette façon certains SGBDR créent automatiquement un index lorsqu'une clé primaire est définie. La création d'index en SQL se fait grâce à la clause INDEX précédée de la clause CREATE. Elle permet de définir un index désigné par son nom, portant sur certains champs d'une table. La syntaxe est la suivante : CREATE [UNIQUE] INDEX Nom_de_l_index ON Nom_de_la_table (Nom_de_champ [ASC/DESC], ...) 45 Base de donnée Le langage SQL L'option UNIQUE permet de définir la présence ou non de doublons pour les valeurs de la colonne Les options ASC/DESC permettent de définir un ordre de classement des valeurs présentes dans la colonne Créer un index composé nommé ind_client sur les colonnes nom et ville selon l’ordre croissant : Create index ind_clt on client(nom,vill) desc ; Pour supprimer l’index spécifié : Drop index ind_clt ; Remarque : dans le cas de suppression d’une table, ses index ne seront pas automatiquement supprimés mais plutôt il faut les supprimer explicitement. 4. SQL en tant que LMD 4.1. Insertion de données La commande INSERT permet d’insérer une ligne dans une table en spécifiant les valeurs à insérer. On peut l'appliquer de 2 manières: Syntaxe 1 INSERT INTO table (col1,..., coln ) VALUES (val1,...,valn ) ; Syntaxe 2 INSERT INTO table (col1,..., coln ) SELECT ... ; Exemple: Soit la table EMPLOYES créée suivant la requête suivante : CREATE TABLE EMPLOYES ( NumEmp number(8) not null, Nom varchar2(20), Fonction varchar2(10), Sal number(8,2), Code_Service char(3), Date_Embauche date, Comm number(8,2) ); 46 Base de donnée Le langage SQL INSERT INTO EMPLOYES (NumEmp, Nom, Sal, Fonction, Code_Service) VALUES (12, SALHI, 1800, 'comptable', 2) ; INSERT INTO EMPLOYES VALUES (13,MELKI, 'comptable', 1800, 2, NULL, 200); INSERT INTO EMPLOYES2 SELECT * FROM EMPLOYES WHERE Sal>1000; Remarque: - On n’est pas obligé de respecter l'ordre initial des colonnes, ni de les indiquer toutes. - Les colonnes qui ont une valeur par défaut ou qui peuvent avoir une valeur nulle, si elles n'apparaissent pas, sont mises soit à NULL, soit à leur valeur par défaut. - Les colonnes clés étrangères doivent contenir des valeurs qui figurent déjà dans la table référencée. - Si on n’indique pas les noms de colonnes, il faut absolument respecter l'ordre dans lequel elles ont été créées et les valoriser toutes (ou écrire explicitement NULL pour les colonnes qu’on ne veut pas remplir) 4.2. Modification de données La commande UPDATE permet de modifier les valeurs d'un ou plusieurs champs, dans un ou plusieurs tuples existants dans une table. On peut l'appliquer de 3 manières: Syntaxe 1 UPDATE table SET col1 = exp1, col2 = exp2, ... ; Syntaxe 2 UPDATE table SET col1 = exp1, col2 = exp2, ... WHERE prédicat ; Syntaxe 3 UPDATE table SET (col1, col2,...) = (SELECT ...) WHERE prédicat ; Exemples o Augmenter le salaire de tous les employés de 1% o Augmenter le salaire de tous les informaticiens de 10% 47 Base de donnée Le langage SQL o Faire passer Mr SALHI dans le service 10 : o Donner à Mr MELKI un salaire 10 % au dessus de celui de Mr SALHI o Enlever (plus exactement, mettre à la valeur NULL) la commission de Mr SALHI 4.3. Suppression des données L'ordre DELETE permet de supprimer des lignes d'une table. La syntaxe est : DELETE FROM table WHERE prédicat; La clause WHERE indique quelles lignes doivent être supprimées. Cette clause est facultative, si elle n'est pas précisée, toutes les lignes de la table sont supprimées Exemples o Supprimer les employés du service numéro 2 o Supprimer les employés embauchés en 1970 Remarque Si la suppression concerne un tuple d’une table (table1) ayant une valeur de clé primaire étant une clé étrangère dans une autre table (table2), le système ne va pas accepter cet effacement et va indiquer une erreur. Les clés étrangères permettent de définir les colonnes d'une table garantissant la validité d'une autre table. Ainsi, il existe des éléments appelés triggers, ou en français gâchettes ou déclencheurs permettant de garantir l'ensemble de ces contraintes que l'on désigne par le terme d'intégrité référentielle, c'est-à-dire notamment de s'assurer qu'un tuple utilisé à partir d'une autre table existe réellement. Parmi ces triggers on cite ON DELETE. ON DELETE est suivi de l'action à réaliser en cas d'effacement d'une ligne de la table faisant partie de la clé étrangère : - CASCADE indique la suppression en cascade des lignes de la table étrangère dont les clés étrangères correspondent aux clés primaires des lignes effacées - RESTRICT indique une erreur en cas d'effacement d'une valeur correspondant à la clé 48 Base de donnée Le langage SQL - SET NULL place la valeur NULL dans la ligne de la table étrangère en cas d'effacement d'une valeur correspondant à la clé - SET DEFAULT place la valeur par défaut (qui suit ce paramètre) dans la ligne de la table étrangère en cas d'effacement d'une valeur correspondant à la clé 5. Interrogation de la base de données en LMD 5.1. Requêtes simples 5.1.1. Projection Syntaxe: SELECT liste_champs | * FROM nom_table; Exemples : Soit la BDR suivante: Produits (CodeP, Libelle, Prix, QteStock, Taxe, Couleur) Clients ( CodeClt, Nom, Prenom, Adresse) Commandes (NC, DateC, CodeClt#) Ligne_Com (NC#, CodeP#, QteCom) Répondre aux requêtes suivantes: i. Afficher tous les produits. ii. Afficher les noms et les prénoms des clients. iii. Afficher les numéros et les dates des commandes. 5.1.2. Projection avec élimination des doublons Syntaxe: SELECT DISTINCT liste_champs FROM nom_table ; Exemples: Répondre aux requêtes suivantes: i. Les codes des clients à partir de la table commandes. ii. La liste des codes produits à partir de la table Ligne_com. iii. La liste des numéros commandes à partir de la table Ligne_com. iv. La liste des couleurs des produits du magasin. 49 Base de donnée Le langage SQL 5.1.3. Sélection Syntaxe: SELECT * FROM nom_table WHERE qualification; OU SELECT DISTINCT Liste_champs FROM nom_table WHERE qualification; Exemples : Répondre aux requêtes suivantes: i. Liste des Produits de Couleur 'Rouge'. ii. Liste des Numéros de produits et Libellés des Produits de quantité supérieure à 10. iii. La liste des codes des clients portant le prénom Mohamed. 5.1.4. Qualification La condition de sélection introduite par la clause WHERE peut être constituée d’une expression booléenne de conditions élémentaires (opérateurs AND, OR, NOT et parenthèses). Une condition élémentaire peut porter sur: - un opérateur de comparaison : =, <, >, !=, <=, >= - l’appartenance à une liste : IN Exemples: 1) Liste des clients de Tunis, de Djerba ou de Kef. 2) Liste des produits de prix 100, 200, 300 ou 400. - l’appartenance à un intervalle : BETWEEN Exemples: 1) Liste des produits de prix compris entre 10 et 100. 2) Liste des numéros de commandes passées en 2010. - la présence de certains caractères dans une valeur : LIKE. Le littéral qui suit LIKE doit être une chaîne de caractères éventuellement avec des caractères jokers _ (un caractère quelconque) et % (une chaîne de caractères quelconque). Exemples: 1) Liste des clients dont le nom commence par D. 2) Liste des clients dont le nom se termine par e et contient 6 lettres. 50 Base de donnée Le langage SQL - la présence de la valeur NULL. La valeur NULL est une valeur “spéciale” qui représente une valeur inconnue. Exemples: 1) Liste des codes des clients dont l’adresse est inconnue. 2) Liste des numéros et dates de commandes dont la quantité commandée est indéterminée. 5.1.5. TRI des données Syntaxe: ORDER BY expression [ ASC | DESC ] [, ...] Avec: - expression désigne une colonne - ASC spécifie l’ordre ascendant et DESC l’ordre descendant du tri - En l’absence de précision ASC ou DESC, c’est l’ordre ascendant qui est par défaut - Quand plusieurs expressions, ou colonnes sont mentionnées, le tri se fait d’abord selon la première colonne, s'il ya égalité, on passe à faire le tri suivant la 2ème colonne et ainsi de suite. Exemples: 1) Liste des produits triés par ordre décroissant du prix. 2) Liste des produits rouges triés par ordre croissant des prix et ordre décroissant des quantités en stock. 5.2. Requêtes sur plusieurs tables 5.2.1. Jointure Produit cartésien (jointure sans qualification) Syntaxe: Exemples: SELECT * FROM table1 as alias1, table2 as alias2 ; 1) Produit cartésien des tables Clients et Commandes 2) produit cartésien des tables Ligne_Com et Commandes jointure avec qualification Syntaxe: SELECT * FROM table1 as alias1, table2 as alias2 WHERE qualification; 51 Base de donnée Le langage SQL Exemples: 1) Donner les commandes commandées par des clients de Tunis entre le 01/01/2000 et le 01/01/2002. 2) Donner le code et le libellé des produits commandés par le client CLT1. 3) Donner les produits dont le prix est > 10 et commandés par des clients de Tunis après le 01/01/98. 4) Donner les clients qui ont passé des commandes après le 27/02/2003. 5) Donner le nom et le prénom des clients qui ont commandé des produits rouges. 6) Donner les numéros des commandes qui contiennent des produits dont le libellé se termine par la lettre e et qui existent en quantité <100 et qui sont commandées par des clients de Tunis. 7) Donner le nom des clients ayant passé une commande supérieure ou égale à 10000 vis, par ordre alphabétique. 5.2.2. Opérateur UNION Syntaxe: SELECT Liste_champs | * FROM liste_tables WHERE condition UNION SELECT Liste_champs | * FROM liste_tables WHERE condition; Remarque: L’union élimine les dupliqués Exemples: 1) Libellés des Produits de Couleur rouge ou dont la quantité est inférieure à 10. 2) Liste des produits de prix plus que 1000DT ou ceux qui sont commandés par Samir Smari. 5.2.3. Opérateur INTERSECT Syntaxe: SELECT Liste_champs | * FROM liste_tables WHERE condition INTERSECT 52 Base de donnée Le langage SQL SELECT Liste_champs | * FROM liste_tables WHERE condition; Remarque: l’intersection élimine les dupliqués Exemples: 1) Numéro des commandes passées en 2011 et passés par des clients de Tunis. 2) Numéro des produits de prix compris entre 10 et 100 et qui sont passés dans des commandes en 01/05/2010. 5.2.4. Opérateur MINUS ou EXCEPT Syntaxe: SELECT Liste_champs | * FROM liste_tables WHERE condition MINUS | EXCEPT SELECT Liste_champs | * FROM liste_tables WHERE condition; Remarque: la différence élimine les dupliqués Exemples: 1) Liste des produits qui n’ont jamais été commandés. 2) Liste des numéros des clients qui n’ont pas passé des commandes après le 01/02/2010. 5.2.5. Prédicats in / not in Syntaxe : SELECT Liste_champs | * FROM liste_tables WHERE champ [NOT] IN ( SELECTListe_champs | * FROM liste_tables WHERE condition ); Exemples: 1) Libellés des produits dont le prix est égal à l’un des prix des produits commandés en 2010 par des clients de Tunis. 53 Base de donnée Le langage SQL 2) Numéros des commandes qui sont passées en une date égale à l’une des dates des commandes passées par des clients de Djerba et contenant des produits de prix 100DT. 5.2.6. Prédicats EXISTS/ NOT EXISTS Syntaxe : SELECT Liste_champs | * FROM liste_tables WHERE [NOT] EXISTS ( SELECT Liste_champs | * FROM liste_tables WHERE condition ); Exemples: 1) Les clients qui ont passé au moins une commande en février 2010. 2) Les codes des produits qui figurent dans au moins une commande. 3) Les n° des clients qui ont commandé au moins un produit. 5.2.7. Prédicats ANY/ ALL Syntaxe : SELECT Liste_champs | * FROM liste_tables WHERE champ op ANY |ALL ( SELECT champ FROM liste_tables WHERE condition); Exemples: 1) La liste des produits qui ont un prix plus élevé par rapport à tous les produits commandés par Tarek Triki. 2) Numéro des clients qui ont commandé au moins un produit commandées par le client n° 1. 5.3. Fonctions d'agrégation 5.3.1. Définition Les fonctionnalités d’agrégation de SQL permettent d’exprimer des conditions sur la totalité de la table ou des groupes de tuples, et de constituer le résultat par agrégation de valeurs au sein de chaque groupe. Les fonctions d’agrégation s’appliquent à une colonne, en général de type numérique. Elles sont: 1. COUNT qui compte le nombre de valeurs non nulles (NOT NULL) 54 Base de donnée Le langage SQL 2. MAX et MIN: cherche le minimum et le maximum dans un ensemble de valeurs 3. AVG qui calcule la moyenne des valeurs de la colonne 4. SUM qui effectue le cumul (la somme ou le total) Syntaxe : SELECT fonction_ag(DISTINCT champ) | fonction_ag(*) FROM liste_tables WHERE condition; Exemples 1) La somme des quantités en stock de tous les produits 2) La moyenne des quantités en stock de tous les produits 3) Le libellé du produit qui a le prix le plus élevé 4) Le libellé du produit qui a le prix le moins élevé 5) Le nombre de produits dans le magasin 6) Le nombre de commandes passées en 2003 7) Le nombre de clients de Tunis 8) Combien de fois le produit de code 1 est commandé en 2010 9) Quantité totale de Briques commandées 10) Coût moyen de Briques fournies 11) Le prix des briques le moins chère et le plus chère 12) Le nombre de commandes passées par Ali Atia en février 2010 et contenant des produits de prix compris entre 10 et 100 13) Liste des produits qui ont un prix en dessous des prix moyens de tous les produits 14) Afficher le prix unitaire moyen des produits dont le libellé commence par c 5.3.2. Clause GROUP BY Dans les requêtes précédentes, on appliquait la fonction d’agrégation à l’ensemble de la table elle-même. On peut partitionner ce résultat en groupes, et à appliquer la ou les fonction(s) à chaque groupe. On construit les groupes en associant les tuples partageant la même valeur pour une ou plusieurs colonnes. Syntaxe : SELECT fonction_ag(DISTINCT champ) | fonction_ag(*) FROM liste_tables WHERE condition GROUP BY champ; 55 Base de donnée Le langage SQL Exemples 1) Nombre de clients par ville 2) Nombre de produits par couleur 3) Nombre de commandes par date 4) Quantité totale commandée par chaque client 5) Nombre de produits différents commandés par chaque clients 6) Afficher pour chaque commande le nombre de produits commandés 7) Afficher, pour chaque produit, le nombre de commandes dans lesquelles il figure 8) Afficher la quantité commandée minimale, maximale et moyenne pour chaque produit commandé 9) Donner, pour chaque produit, le nombre de clients qui l'ont commandé 5.3.3. Clause HAVING On peut faire porter des conditions sur les groupes avec la clause HAVING. Cette dernière ne figure qu’avec une fonction de groupe. Une requête de groupe peut contenir à la fois une clause WHERE et une clause HAVING. La clause WHERE sera d’abord appliquée pour sélectionner les lignes, puis les groupes seront constitués à partir des lignes sélectionnées. Les fonctions de groupe seront ensuite évaluées et la clause HAVING sera enfin appliquée pour sélectionner les groupes. Syntaxe : SELECT fonction_ag(DISTINCT champ) | fonction_ag(*) FROM liste_tables WHERE condition GROUP BY Autre_champ HAVING condition_de_groupe; Exemples 1) Nombre de clients par ville, ne laisser que les nombres qui sont >10 2) Nombre de produits par couleur, ne laisser que les nombres qui sont compris entre 5 et 15 3) Nombre de commandes par date, ne laisser que les nombres 100, 120, et 130 4) Afficher pour chaque commande le nombre de produits commandés et qui est >3 5) Donner, pour chaque produit, le nombre de clients qui l'ont commandé et qui est <=10 56 Base de donnée Le langage SQL 6) Donner le libellé et le prix moyen des produits figurant dans des commandes passées en 2010 et dont le prix minimum est supérieur à 1000 DT 7) Donner la quantité moyenne commandée pour les produits faisant l’objet de plus de 3 commandes 8) Donner les numéros des produits achetés par plus de 100 clients 6. Le langage de contrôle de données (LCD) 6.1. Gestion d'utilisateurs Plusieurs personnes peuvent travailler simultanément sur une base de données, toutefois ces personnes n'ont pas forcément les mêmes besoins: certaines peuvent par exemple nécessiter de modifier des données dans la table, tandis que les autres ne l'utiliseront que pour la consulter. Ainsi, il est possible de définir des permissions pour chaque personne en leur accordant un mot de passe. C’est la tâche de l'administrateur de la base de données (en anglais DBA, DataBase Administrator). Il doit dans un premier temps définir les besoins de chacun, puis les appliquer à la base de données sous forme de permissions. Le langage SQL permet d'effectuer ces opérations grâce à deux clauses : GRANT permet d'accorder des droits à un (parfois plusieurs sur certains SGBD) utilisateur REVOKE permet de retirer des droits à un (ou plusieurs sur certains SGBD) utilisateur Les permissions (appelées aussi droits ou privilèges) peuvent être définies pour chaque (un grand nombre) clause. D'autre part il est aussi possible de définir des rôles c'est-à-dire de permettre à d’autres utilisateurs d'accorder des permissions. 6.1.1. Création d’un utilisateur La création d’un utilisateur s’effectue par l’ordre CREATE USER, de syntaxe : CREATE USER nom_utilisateur IDENTIFIED BY mot de passe ; 57 Base de donnée Le langage SQL 6.1.2. Modification d’un utilisateur La clause ALTER USER permet de modifier le mot de passe de syntaxe : ALTER USER nom_utilisateur … IDENTIFIED BY mot de passe 6.1.3. Suppression d’un utilisateur La clause BROP USER permet de supprimer l’utilisateur de syntaxe : DROP USER nom_utilisateur [CASCADE] ; Sans l’option CASCADE, l’utilisateur n’est pas supprimé s’il est propriétaire d’objets. Avec l’option CASCADE, l’utilisateur est toujours supprimé et tous les objets dont il est le propriétaire sont aussi supprimés. 6.1.4. Privilèges Les privilèges sont les clauses qui peuvent être autorisées/retirées à un utilisateur. Les principales sont : DELETE: privilège de supprimer les données d'une table INSERT: privilège d'ajouter des données à une table SELECT: privilège d'accéder aux données d'une table UPDATE: privilège de mettre à jour les données d'une table 6.2. Les droits d’accès L'unique personne pouvant accorder ou retirer des droits sur un élément (table, vue ou index) est la personne qui l'a créé. Toutefois, il lui est possible de transmettre ce droit d'accorder/retirer des droits, auquel cas la personne recevant cet "honneur" aura le droit de transmettre ce "pouvoir" sur ces éléments 6.2.1. Accorder des droits La clause GRANT permet d'attribuer des permissions à un ou plusieurs utilisateurs sur un ou plusieurs éléments de la base de données. La syntaxe de cette clause est la suivante : GRANT Liste_de_permissions PRIVILEGES TO Liste_d_utilisateurs [WITH GRANT OPTION]; 58 Base de donnée Le langage SQL L'option WITH GRANT OPTION permet de définir si l'utilisateur peut lui-même accorder à un autre utilisateur les permissions qu'on lui accorde sur les éléments Afin d'éviter à avoir à saisir l'ensemble des utilisateurs dans le cas d'une autorisation collective ou bien de citer l'ensemble des permissions il est possible d'utiliser des mots clés : Le mot clé PUBLIC en lieu et place de la liste d'utilisateurs permet d'accorder les privilèges sur le ou les objets à l'ensemble des utilisateurs Le mot clé ALL en lieu et place de la liste de permissions permet d'accorder tous les privilèges aux utilisateurs présents dans la liste En précisant entre parenthèses un nom de colonne pour un privilège, il est possible de limiter le privilège à la colonne (ou la liste de colonnes) entre parenthèses. Exemple GRANT UPDATE(Nom,Prenom) ON Etudiants TO Jerome,Francois,Georges WITH GRANT OPTION; L'option WITH GRANT OPTION autorise donc plusieurs utilisateurs à accorder des permissions à un même utilisateur, il y a donc des règles à respecter lors du retrait des permissions à un utilisateur... 6.2.2. Retirer des droits La clause REVOKE permet de retirer des permissions à un ou plusieurs utilisateurs sur un ou plusieurs éléments de la base de données. La syntaxe de cette clause est la suivante : REVOKE [GRANT OPTION FOR] Liste_de_permissions ON Liste_d_objets FROM Liste_d_utilisateurs; L'option GRANT OPTION FOR permet de supprimer le droit d'un utilisateur à accorder des permissions à un autre utilisateur. 59 Base de donnée Le langage SQL Afin d'éviter d'avoir à saisir l'ensemble des utilisateurs dans le cas d'une autorisation collective ou bien de citer l'ensemble des permissions il est possible d'utiliser des mots clés : Le mot clé PUBLIC en lieu et place de la liste d'utilisateurs permet de retirer les privilèges sur le ou les objets à l'ensemble des utilisateurs Le mot clé ALL en lieu et place de la liste de permissions permet de retirer tous les privilèges aux utilisateurs présents dans la liste En précisant entre parenthèses un nom de colonne pour un privilège, il est possible de limiter la restriction de privilège à la colonne (ou la liste de colonnes) entre parenthèses. Exemple : REVOKE [GRANT OPTION FOR] UPDATE(Nom,Prenom) ON Etudiants FROM PUBLIC L'attribution et la révocation de droits pose deux problème : lorsque l'on retire un droit à un utilisateur, il faut que ce droit soit retiré aux utilisateurs auxquels il a accordé le droit un utilisateur peut avoir reçu un droit de plusieurs utilisateurs Il s'agit donc de retirer les droits des utilisateurs l'ayant obtenu de quelqu'un qui ne l'a plus en prenant en compte le fait qu'il peut l'avoir de plusieurs personnes simultanément... 6.3. Les transactions dans SQL : COMMIT et ROLLBACK L'utilisateur peut à tout moment valider (et terminer) la transaction en cours par la commande COMMIT. Les modifications deviennent alors définitives et visibles à toutes les autres transactions. L'utilisateur peut annuler (et terminer) la transaction en cours par la commande ROLLBACK. Toutes les modifications depuis le début de la transaction sont annulées. 60 Base de donnée Bibliographie Bibliographie - George Gardarin, Bases de Données - objet/relationnel, Eyrolles, 1999,ISBN: 2212-09060-9 - George Gardarin, Maîtriser les Bases de Données: modèles et langages, Eyrolles, 1993, ISBN: 2-212-08727-6 - Rim Chaabane, Base de données , L3-Informatique Paris 61