2ème Année LMD informatique Université L’arbi Ben M’hidi - Oum El-Bouaghi - Algérie Cours de bases de données Dr. Chergui Leila Sommaire Chapitre 1 : Introduction générale aux bases de données……………..….1 Chapitre 2 : Le modèle relationnel……….……………........................….13 Chapitre 3 : Le calcul relationnel………………………………..………..34 Chapitre 4 : SQL : Interrogation d’une base de données………….…….41 Chapitre 5 : SQL : Définition et modification d’une base de données….48 Chapitre 1: Introduction générale aux bases de données Chapitre 1 : Introduction générale aux bases de données Introduction Auparavant, les données sont stockées sous forme de fichiers et ils sont gérées par un système de gestion de fichiers, il y avait deux types de fichiers : 1. Fichier des données : représentent des séquences d’enregistrements dont l’accès est séquentiel ou indexé. 2. Fichier de traitement : représentent un ensemble d’instructions servant à manipuler les données des fichiers (assembleur, Cobol). Mais cette approche a souffert des problèmes suivants : 3. Redondance d’information (Mise A Jours (MAJ) difficiles). 4. Problème d’incohérence, de fiabilité et de sécurité. 5. Manque de structuration des données. 6. Si un changement est effectué sur la structure d’un fichier de données tous les programmes qui l’utilisent doivent être modifiés et on va perdre du temps pour la maintenance. Solution : regrouper les fichiers de données en une seule entité= BD, dont les données et les traitements sont indépendants. 1.1. Concepts de base Dans ce qui suit on va expliquer le principe de quelques concepts de base. 1.1.1. Base de Données (BD) Une BD est faite pour enregistrer des faits, des opérations au sein d'un organisme (administration, banque, université, hôpital, ...), c’est un : Ensemble structuré de données. Enregistré sur des mémoires secondaires (disque dur). Créé et tenu à jour pour les besoins d’un ensemble d’utilisateurs ou de programmes. 1.1.2. Objectifs d’un Système de Gestion de Base de Données Un Système de Gestion de Bases de Données (SGBD) (en anglais DBMS pour Database Management System) est un système qui permet de créer et gérer une BD partagée par plusieurs utilisateurs simultanément. Il représente un ensemble de services (applications logicielles) permettant de : Dr. Chergui Leila 2 Chapitre 1 : Introduction générale aux bases de données Définir la structure d’une BD (de décrire les données et les liens entre elles d’une façon logique sans se soucier du comment cela va se faire physiquement dans les fichiers) par un Langage de Description de Données (LDD). Rechercher des données dans une BD (Langage de Manipulation des Données : LMD). Insérer, modifier, supprimer des données dans une BD (LMD). Assurer la cohérence et la confidentialité des données (LMD) et la non-redondance des données. Assurer la sécurité. Assurer la cohérence : les données sont soumises à certains nombre de contraintes d’intégrité qui définissent un état cohérent de la base. Ces contraintes sont décrites dans le langage de description de données (LDD). Ex : l’âge des employés ne peut pas être supérieur à 55. Si un utilisateur ajoute une entité employé, le SGBD doit vérifier l’attribut âge, s’il ne respecte pas cette contrainte, il est rejeté. Une contrainte d’intégrité est une propriété que les objets décrits par le schéma (entité, association, attribut) doivent respecter de manière à représenter le problème. Assurer la confidentialité : les données doivent pouvoir être protégées contre les accès non-autorisés. Pour cela, il faut associer à chaque utilisateur des droits d’accès aux données. Assurer la sécurité après panne : on peut tomber dans des situations où certains fichiers ne sont plus lisibles ou une panne au milieu d’une MAJ. Le SGBD doit assurer la reprise après panne. Il existe plusieurs méthodes : récupérer les données avant la modification, terminer l’opération interrompue, ou utiliser la journalisation qui consiste à mémoriser les états successifs de la BD. Permettre le partage des données : permettre à plusieurs utilisateurs d’accéder aux mêmes données au même moment de manière transparente, en contrôlant les accès concurrents. Ou on parle des transactions. Une transaction est une opération unitaire qui transforme le contenu de la BD d’un état A vers un état B. Assurer une efficacité d’accès : en terme du temps de réponse et de débit global ainsi que des modes d’accès simples (hachage, arbre blanchi). Le temps de réponse est le temps d’attente moyen pour une requête. Le débit global représente le nombre de transactions exécutées par second. Dr. Chergui Leila 3 Chapitre 1 : Introduction générale aux bases de données 1.1.3. Système d’Information (SI) Il permet de gérer les informations nécessaires au bon fonctionnement d’une entreprise. Les BDs sont au cœur d’un SI. La différence entre un SGBD et un SI est indiquée dans la figure 1.1. Figure 1.1. Différence entre un SGBD et un SI. La plupart des SGBDs fonctionnent selon un mode client/serveur, le serveur (la machine stockant les données) reçoit des requêtes de plusieurs clients et ceci de manière concurrente. Le serveur analyse la requête, le traite et retourne le résultat au client. 1.1.4. Domaines d’application d’un SGBD Les SGBD sont des logiciels complexes et stratégiques, utilisés dans de très nombreuses applications informatiques, parmi lesquelles : 1. Gestion d’entreprise (stock, personnel, client). 2. Banque (comptes, emprunte). 3. Système de réservation (avion, train). 4. Bibliothèque, vidéothèque. 5. Bureautique. 6. Géographie (carte routière : GPS). 7. Informatique (programmation, documentation). 8. Le E-commerce. 9. Les dossiers médicaux. 10. Les blogs et les wikis. Dr. Chergui Leila 4 Chapitre 1 : Introduction générale aux bases de données Remarques Un blog est un type de site web (ou une partie d'un site web) utilisé pour la publication périodique et régulière de nouveaux articles, généralement succincts, et rendant compte d'une actualité autour d'un sujet donné ou d'une profession. Un wiki est un site web dont les pages sont modifiables par les visiteurs afin de permettre l'écriture et l'illustration collaboratives des documents numériques qu'il contient. Il utilise un langage de balisage et son contenu est modifiable au moyen d’un navigateur web. 1.1.5. Niveaux d'abstraction On peut distinguer trois niveaux d’abstraction comme il est illustré dans la figure 1.2 : Niveau externe : vue partielle des données suivant l'utilisateur (utilisateur final ou programmeur d’application). Ex : l’utilisateur n° 1 est chargé de paye, donc il voit que les informations employé. Le programmeur n° 1 est chargé de faire les facturations, donc il s’intéresse aux tables client et commande. Il existe plusieurs schémas externes. Niveau conceptuel : vue globale de l'organisation des données, c’est la définition logique de la BD (représentation) via le modèle de données, elle est faite par l’administrateur de la BD qui est chargé d’identifier et décrire les regroupements de données et leurs interactions. Ex : nom, prénom, adresse, etc. Il existe un seul schéma logique. Niveau interne : organisation physique des données qui concerne le type de stockage et les modes d’accès. Ce niveau est réalisé par le SGBD. Ex : nom : 20 octets, prénom : 20 octets, adresse : 40 octets, salaire : 4 octets. Figure 1.2. Niveaux d’abstraction dans un SGBD. Dr. Chergui Leila 5 Chapitre 1 : Introduction générale aux bases de données 1.1.6. Indépendance de données L'architecture à trois niveaux définit ci-dessus permet de garantir l'indépendance des données par rapport aux programmes : elle permet de modifier le schéma de la base de données à un niveau sans restructurer les autres. On trouve deux types d’indépendance : 1. L’indépendance physique : est la possibilité de changer le schéma physique et de modifier l'organisation physique des fichiers, de rajouter ou supprimer des méthodes d'accès sans remettre en cause le schéma conceptuel. 2. L’indépendance logique : est la possibilité de modifier le niveau conceptuel sans changer le schéma externe. 1.1.7. Les modèles logiques 1. Le modèle hiérarchique : ou arbre, il lie les enregistrements dans une structure arborescente où chaque enregistrement n’a qu’un seul possesseur. Pour chaque nœud donné, un seul nœud père. Ex : ADABASE (1970), IMS (1966), System 2000 (1967). 2. Le modèle réseau : ou graphe, c’est un modèle hiérarchique, mais permet en plus d’établir des relations transverses. Ex : TOTAL (1978), IDMS (1978), IDMS2 (1978), SOCRATE. 3. Le modèle relationnel : il stocke les informations décomposées et organisées dans des tables. Ex : 80% des SGBD sont relationnelles, ORACLE (85% du marché), DB2, SQL Server, My SQL (libre), Postgre SQL (libre), ACCESS, PARADOX , DBASEV, INGRES, INFORMIX, RDB. 4. Le modèle orienté objet : il stocke les informations groupées sous forme de collections d’objets persistants dans des classes. Ex : Versan, Object store, O2, ONTOS, ORION. 5. Le modèle XML : il s’appuie sur le modèle de données fourni par XML. Ex : Academic search. Des exemples de modèle hiérarchique et modèle réseau sont affichés dans la figure 1.3. Figure 1.3. Exemples de modèles logiques. Dr. Chergui Leila 6 Chapitre 1 : Introduction générale aux bases de données Remarques 1. Donnée exhaustive : la base contient toutes les informations requises pour le service que l’on en attend, il n’ya pas de données manquantes. 2. Donnée persistent : elle doit survivre à la fin d’une application particulière sur la BD pour qu’elle puisse être réutilisée plus tard. 3. Interopérabilité : BD accessibles par différents systèmes. 4. Hétérogénéité : BD fondée sur des modèles distincts. 5. Le catalogue système ou dictionnaire de données : il contient toutes les méta-données utiles au système. Les méta-données sont les représentations permettant la description : Des données (type, taille, valeur autorisée, etc.). Des autorisations d’accès. Des vues et autres éléments système. Le catalogue renferme encor la description des différents schémas des trois niveaux ainsi que les règles de passage d’un schéma vers l’autre ; 1.2. Modélisation Entité/Association Entité/Association (EA) en français, ER en anglais (pour Entity Relationship) décrit l'aspect conceptuel des données à l’aide d’entités et d’associations. Le passage du monde réel vers le modèle logique est montré dans la figure 1.4. Monde réel MERISE Modèle conceptuel E/A Hiérarchique Modèle logique Relationnel Réseau Figure 1.4. Transformation de monde réel vers le modèle logique. Exemple : un client commande un article. Dr. Chergui Leila 7 Chapitre 1 : Introduction générale aux bases de données Client Article Numcli Nom 1,n Commander 0,n IDcom+IDadr Date, Qte Numarti Désignation Prix Figure 1.5. Exemple de modélisation E/A. A partir de la figure 1.5, chaque client réel est une occurrence de l’entité client. Le client nommé Omar est une instance ou occurrence de l’entité Client. Plusieurs composantes apparaissent dans ce modèle. Dans le paragraphe suivant, on va expliquer le principe de chacune d’elles. Entité : représentation d’un objet matériel ou immatériel. La figure 1.6 montre la représentation schématique d’une entité. Par exemple un employé, un projet, un bulletin de paie, etc. Propriétés : données élémentaires relatives à une entité. Par exemple, un numéro d’employé, une date de début de projet, etc. Figure 1.6. Modélisation d’une entité. Identifiant : propriété ou groupe de propriétés qui sert à identifier une entité. L’identifiant d’une entité est choisi par l’analyste de façon à ce que deux occurrences de cette entité ne puissent pas avoir le même identifiant. Par exemple, le numéro de client sera l’identifiant de l’entité Client. Associations : représentation d’un lien entre deux entités ou plus, une association peut avoir des propriétés particulières (date d’empreint dans la figure 1.7). Schématiquement, l’association prend la forme d’une ellipse. Figure 1.7. Exemple d’une association. Dr. Chergui Leila 8 Chapitre 1 : Introduction générale aux bases de données Une relation est binaire, si elle relie deux entités. Elle est ternaire si elle relie trois entités, naire, si elle relie un nombre d’entités supérieur à 2. Association cyclique (récursive) : elle lie une entité à elle même (cycle). On doit donc spécifier les rôles de l’association. Un rôle représente le rôle d’une entité dans une association. Un exemple est indiqué dans la figure 1.8. Figure 1.8. Exemple d’une association récursive. Cardinalités : la cardinalité d’une association pour une entité constituante est composée d’une borne minimale et d’une borne maximale : Minimale : nombre minimum de fois qu’une occurrence de l’entité participe aux occurrences de l’association, généralement 0 ou 1. Maximale : nombre maximum de fois qu’une occurrence de l’entité participe aux occurrences de l’association, généralement 1 ou n. Figure 1.9. Association des cardinalités. Dans la figure 1.9, la cardinalité 0,3 indique qu’un adhérent peut être associé à 0, 1, 2 ou 3 livres, c’est-à-dire qu’il peut emprunter au maximum 3 livres. A l’inverse un livre peut être emprunté par un seul adhérent, ou peut ne pas être emprunté. Remarques Les cardinalités maximum sont nécessaires pour concevoir le schéma de la base de données. Les cardinalités minimums sont nécessaires pour exprimer les contraintes d’intégrité. Dr. Chergui Leila 9 Chapitre 1 : Introduction générale aux bases de données En notant uniquement les cardinalités maximum, on distingue 3 types de liens : Lien fonctionnel 1:n (figure 1.10). Une instance de A ne peut être associée qu’à une seule instance de B. Dans l’exemple de la figure un employé ne peut travailler que dans un seul département. Lien hiérarchique n:1. Dans la figure 1.11, une instance de A peut être associée à plusieurs instances de B. Ici, un département emploie généralement plusieurs employés. Lien maillé n:m. Comme il est montré dans la figure 1.12, une instance de A peut être associée à plusieurs instances de B et inversement. L’exemple de cette figure indique qu’un employé peut participer à plusieurs projets. A B n 1 Employé Départ 1 Travaille n Figure 1.10. Exemple de liens fonctionnels. A B 1 n Départ Employé n Emploie 1 Figure 1.11. Exemple de liens hiérarchiques. A B m n Employé Projet n Participe m Figure 1.12. Exemple de liens maillés. Dr. Chergui Leila 10 Chapitre 1 : Introduction générale aux bases de données Un exemple de diagramme E/A est présenté dans la figure 1.13. Figure 1.13. Exemple d’un diagramme E/A. Dr. Chergui Leila 11 Chapitre 2: Le modèle relationnel Chapitre 2 : Le modèle relationnel Introduction Le modèle relationnel de données été défini en 1970 par le Edgar Frank Codd chercheur britannique pour surpasser les inconvénients des modèles hiérarchiques et réseaux (incapacité de gérer des BDs volumineuses, incapacité de traiter les redondances de données, la nongarantie de l’intégrité de données, etc.). Les premiers SGBDR commerciaux font leur apparition dans les années 80, avec des outils comme ORACLE principalement. Le modèle relationnel est simple, facile à comprendre même pour les non-spécialistes et repose sur des solides bases théoriques notamment la théorie des ensembles et la logique des prédicats du premier ordre qui permettent notamment de conduire à une amélioration des performances des outils. Les objectifs du modèle relationnel sont : - Proposer des schémas de données faciles à utiliser. - Améliorer l’indépendance logique et physique. - Mettre à la disposition des utilisateurs des langages de haut niveau. - Optimiser les accès à la base de données. - Améliorer l’intégrité et la confidentialité. - Fournir une approche méthodologique dans la construction des schémas. 2.1. Concepts de base Dans ce qui suit, on va introduire quelques concepts de base concernant le modèle relationnel. 2.1.1. Modèle relationnel Dans ce modèle, les objets et les associations sont représentés par un concept unique ; la relation. Les relations sont des tableaux à deux dimensions souvent appelées Tables dont les lignes sont appelées n-uplet ou tuples en anglais. Les données sont manipulées par des opérateurs de l’algèbre relationnelle et l’état cohérent de la base est défini par un ensemble de contraintes d’intégrité. Exemple 1: ETUDIANT Dr. Chergui Leila N°Etud Nom Prénom Age 10 Taleb Omar 20 20 Badaoui Mounir 21 100 Naser Fateh 19 13 Chapitre 2 : Le modèle relationnel Cet exemple représente une relation décrivant les étudiants. Le nom de la relation est ETUDIANT. Les entités dans les colonnes représentent les attributs qui sont : (N°Etud, Nom, Prénom, Age). Chaque ligne de la table correspond à une occurrence ou un tuple, par exemple : <100, Naser, Fateh, 19>. Exemple 2 : la table OUVRAGES décrit un ouvrage. Côte Titre Editeur Année NbExemplaire Thème 12TA1 Réseaux Eyrolles 1998 10 Réseaux, informatiques 13GO1 Internet Algorithmes Addison génétiques Wesley Système Eyrolles 15TA2 1994 5 Informatique évolutionnaire 1993 6 UNIX, SE d’exploitation Une table est composée d’un ensemble d’attributs et d’un ensemble de tuples. Un attribut est le nom donné à une colonne d’un tableau représentant une relation. 2.1.2. Domaine Un domaine est un ensemble de valeurs finies ou infinies que peut prendre un attribut. Ex : entier, chaine de caractères, réels, etc. Exemples : Le domaine des booléen : Db={0,1}. Le domaine des couleurs primaires : Dc= {jaune, rouge, bleu} Le domaine des prénoms : chaine de caractères. Le domaine des âges : entier entre 17 et 30. 2.1.3. Relation Une relation est un ensemble de tuples, déterminée par un nom. Une relation peut être exprimée en deux formats : 1. Relation en intension (schéma de relation) : ici, on représente la relation par son nom suivi de l’ensemble de ses attributs et de leurs domaines d’application. Parmi ces attributs, un sous-ensemble (un ou plusieurs attributs) constitue la clé de la relation et sera soulignée dans le schéma. Dr. Chergui Leila 14 Chapitre 2 : Le modèle relationnel Exemple 1 : ETUDIANT (N°Etud : entier, Nom : CC, Prénom : CC, Age : entier). Exemple 2 : OUVRAGES (Côte : texte, Titre: Texte, Editeur: Texte, NbExemplaire: Numérique, Année:Date, Thème:Texte). Pour alléger l’écriture, le schéma de relation se limite souvent au nom de la relation suivi de ses attributs. Exemple 1 : ETUDIANT (N°Etud, Nom, Prénom, Age). 2. Relation en extension : quand la relation est exprimée en intension, ses tuples ne sont pas visibles. Par contre, la présentation de la relation en extension permet de les lister. La relation en extension est représentée sous forme de tableau. Les lignes de la table sont les tuples. Le degré d’une relation désigne son nombre d’attributs. Ex : le degré de la relation ETUDIANT est 4 et le degré de la relation OUVRAGE est 6. La cardinalité d’une relation représente le nombre de ses tuples. L’ordre des lignes et de colonnes n’a pas d’importance car il n’y a pas d’ordre dans un ensemble. Exemple 3 : donner le degré et la cardinalité de la relation suivante ainsi que le domaine de chaque attribut. Id Nom Prénom Département Salaire 142046 Chirifi Nabil Comptabilité 55000 142100 Hadad Salim Marketing 33500 142190 Ferah Omar Ressources humaines 42000 Le domaine des salaires : réels positifs. A chaque fois une relation est représentée au moyen d’une table, les conditions suivantes doivent être satisfaites : La table a un nom unique. Chaque colonne de la table comporte un nom unique ; c’est-à-dire qu’il n’y a pas deux colonnes de la même table qui possèdent des noms identiques. L’ordre des colonnes dans la table est sans importance. Toutes les lignes de la table ont le même format et le même nombre d’entrées. Les valeurs de chaque colonne appartiennent au même domaine (chaine de caractères, entiers, etc.). Dr. Chergui Leila 15 Chapitre 2 : Le modèle relationnel Chaque entrée (l’intersection d’une ligne et d’une colonne) de chaque tuple de la relation doit être une valeur unique. Ceci signifie qu’aucune liste ou ensemble de valeurs n’y sont permis. L’ordre des tuples (lignes) est sans importance du fait qu’elles sont identifiées par leur contenu et non par leur position dans la table. Il n’ya pas deux tuples identiques dans toutes leurs entrées. Le nom d’un attribut peut apparaître dans plusieurs schémas de relations. Tout attribut peut prendre une valeur nulle excepté les attributs de la clé primaire. Il n’y a pas de « case vide » dans la table, donc toutes les valeurs de tous les attributs sont toujours connues. Exemple 4 : Considérant la relation DEPT et les lignes montrées à la suite. Expliquez si ces lignes peuvent être ou non insérées dans la relation DEPT. N°DEPT Nom Lieu Budget 20 Ventes Constantine 1 700 000 10 Marketing Alger 2 500 000 10 Recherche Alger 1 500 000 Comptabilité Oran 1 200 000 Informatique Constantine 1 500 000 15 1. La première ligne : non, elle viole la propriété d’unicité de la clé car le département N° 10 existe déjà. 2. La 2ème ligne : non, la clé ne peut pas être nulle. 3. 3ème ligne : oui. Remarques Du fait que la clé primaire identifie de manière unique les tuples d’une relation, aucune de ses attributs ne doit être nulle. Dans une relation, une valeur nulle représente des informations manquantes, inconnues ou des données inapplicables. Donc, la valeur NULL n’est pas une valeur zéro et elle ne représente pas une valeur particulière pour l’ordinateur. Dr. Chergui Leila 16 Chapitre 2 : Le modèle relationnel 2.1.4. Clé d’une relation C’est un attribut ou une composition minimale d’attributs dont chacune des valeurs permet de déterminer, d’une manière unique, un tuple de la relation. Ainsi, la valeur d’une clé ne peut exister qu’une seule fois dans la relation en extension. La clé de la relation, qu’elle soit décrite en intension ou extension, sera soulignée. Dans une relation, il peut y avoir plusieurs attributs ou compositions d’attributs qui permettent de déterminer, d’une manière unique, un tuple. Ces attributs forment des clés candidats, mais la relation doit avoir une seule clé primaire. Le choix est fait selon les traitements appliqués à la relation. Exemple 5 : 1. ETUDIANT (N°Etud, Nom, Prénom, Age). Il y a une seule clé candidat une seule clé primaire. 2. TRAVAILLEUR (N°Empl, N°poste, Designation de poste de travail), ici on a deux clés candidat : N°Empl, N°poste. On doit choisir une clé primaire. 2.1.5. Clé étrangère C’est un attribut d’une relation qui fait référence à un attribut clé primaire d’une autre relation. C’est un attribut qui sert à faire le lien entre deux relations d’une même BD. Exemple 6 : 2.1.6. Contraintes d’intégrité Trois types de contraintes d’intégrité sont obligatoires : 1. Contrainte de clé : une relation doit posséder une clé primaire. 2. Contrainte d’entité : un attribut d’une clé ne doit pas posséder de valeurs nulles (vides). 3. Contrainte de référence (pour les clés étrangères). Exemple 7 : on souhaite poser les contraintes suivantes : 1. Le nombre d’exemplaire de chaque OUVRAGE doit être supérieur à 0 (zéro). Dr. Chergui Leila 17 Chapitre 2 : Le modèle relationnel 2. Chaque OUVRAGE doit avoir au moins un auteur. 2.1.7. Base de données relationnelle C’est un ensemble exhaustif et cohérent de schémas de relations. Exemple 8 : la BD relationnelle des produits d’une entreprise industrielle pourra compter les schémas de relations suivants : - PRODUIT (N°Prod, Libellé, Datfabric, Prix). - Article (N°Art, Libellé, Quantstock). - NOMENCLATURE (N°Prod, N°Art, Qte). Le schéma relationnel ETUDIANT (N°Etud, Nom, Prénom, Age). N’y figurera pas. Elle ne fait pas partie du domaine d’étude. Exemple 9 : le schéma de la base de données permettant la gestion de notices bibliographiques est : - AUTEURS (NumAuteur, Nom, Prénom). - OUVRAGES (Côte, Titre, NbExemplaire, Année, NumEditeur, Thème). Contrainte de domaine : NbExemplaire >0. Contraintes référentielles : OUVRAGES.NumEditeur REFERENCE EDITEURS.NumEditeur - ECRIT (NumAuteur, Côte) Clé primaire : NumAuteur, cote. Contraintes référentielles : ECRIT.NumAuteur REFERENCE AUTEURS.NumAuteur ECRIT.Côte REFERENCE OUVRAGES.Côte 2.2. Opérateurs de l’algèbre relationnelle C’est l’ensemble des opérations qui peuvent être appliquées aux relations pour obtenir de nouvelles opérations résultantes. Ces opérations permettront de répondre à des besoins précis des utilisateurs. 2.2.1. Opérateurs unaires Ils manipulent des relations de même schémas. Projection : la projection d’une relation R consiste à créer une nouvelle relation, à partir de R mais en ne conservant que les attributs cités en opérande. Elle consiste à : 1. Supprimer, d’une relation, les attributs non-mentionnés en opérande. 2. Et à éliminer les tuples, en doublon, qui risque d’apparaître dans la nouvelle table. Dr. Chergui Leila 18 Chapitre 2 : Le modèle relationnel D’une manière formelle, soit un schéma de relation R(A1, A2, …,An) avec ∀ ( ), Ai étant un attribut dont les valeurs appartiennent à un domaine Di. La projection R’ de R sur A1, A2 s’écrira : R’= proj(R,A1,A2)=∏ 1, 2( ). La modélisation graphique est : R’ A1, A2 R Exemple 1 : considérant la relation CLIENT (N°CLI, Nom, Prenom, Datnais). On représente la relation en extension : CLIENT N°CLI Nom Prenom Datnais 101 Taleb Omar 14/03/1959 102 Mansouri Nabil 13/03/1946 230 Badaoui Khaled 14/05/1978 104 Salem Rida 11/12/1985 214 Ayad Fateh 11/11/1975 311 Ferah Nabil 15/04/1968 Requête : on voudrait connaître que les noms et prénoms des clients. PCLIENT1=proj(CLIENT, Nom, Prenom)=∏ , ( ). La représentation graphique est : PCLIENT1 Nom, Prenom CLIENT Donc, la relation en intension est : PCLIENT1 (Nom, Prenom). En extension : PCLIENT1 Dr. Chergui Leila Nom Prenom Taleb Omar Mansouri Nabil Badaoui Khaled Salem Rida Ayad Fateh Ferah Nabil 19 Chapitre 2 : Le modèle relationnel La deuxième étape consiste à supprimer les doublons, pour cette relation on n’a pas. Exemple 2 : considérant la relation CLIENT (N°CLI, Nom, Prenom, Datnais). On représente la relation en extension : CLIENT N°CLI Nom Prenom Datnais 101 Taleb Omar 14/03/1959 102 Mansouri Nabil 13/03/1946 230 Mansouri Nabil 14/05/1978 104 Salem Rida 11/12/1985 214 Ayad Fateh 11/11/1975 311 Ferah Nabil 15/04/1968 Requête : on voudrait connaître que les noms et prénoms des clients. PCLIENT2 en extension : PCLIENT2 Nom Prenom Taleb Omar Mansouri Nabil Mansouri Nabil Salem Rida Ayad Fateh Ferah Nabil Après la suppression des doublons, on aura : PCLIENT2 Nom Prenom Taleb Omar Mansouri Nabil Salem Rida Ayad Fateh Ferah Nabil Sélection (restriction) : elle consiste à créer une relation à partir d’une autre, en ne gardant que les tuples pour lesquels un attribut vérifie une certaine propriété. La sélection est l’opération qui consiste, à partir d’une relation R (A1, A2,…, An), à créer une nouvelle relation R’(A1, A2,…,An) dont tous les tuples vérifient une propriété d’un attribut Ai. On notera : R’= Dr. Chergui Leila < é >< > ( )∀ ∈ . 20 Chapitre 2 : Le modèle relationnel Ou R’=Restrict (R,Ai<opérateur><valeur>). Ou R’=R[Ai<opérateur><valeur>]. L’opérateur appartient à l’ensemble {=,<, >,≤, ≥, ≠}. La valeur appartient au domaine de l’attribut Ai. La modélisation graphique est : R’ Ai <opérateur> <val> R Exemple 1 : considérant la relation CLIENT (N°CLI, Nom, Prenom, Datnais). On représente la relation en extension : CLIENT N°CLI Nom Prenom Datnais 101 Taleb Omar 14/03/1959 102 Mansouri Nabil 13/03/1946 230 Badaoui Khaled 14/05/1978 104 Salem Rida 11/12/1985 214 Ayad Fateh 11/11/1975 311 Ferah Nabil 15/04/1968 Requête : on cherche les clients qui ont 35 ans ou plus au 1/01/2008. La condition se traduit mathématiquement : Datnais≤01/01/1973, il faut que tous les tuples vérifient cette condition et il faut conserver toutes les informations (attribut) concernant un client. SCLIENT1= ≤ ′01/01/1973′( ) = CLIENT [Datnais ≤’01/01/1973’]= =Restrict(CLIENT, Datnais ≤’01/01/1973’). La représentation graphique : SCLIENT1 Datnais≤’01/01/1973’ CLIENT Dr. Chergui Leila 21 Chapitre 2 : Le modèle relationnel La relation en intension s’écrira SCLIENT1 (N°CLI, Nom, Prenom, Datnais). Le schéma de la relation résultante reste identique à celui de la relation originale : même clé primaire, même attributs. En extension, elle sera : SCLIENT1 N°CLI Nom Prenom Datnais 101 Taleb Omar 14/03/1959 102 Mansouri Nabil 13/03/1946 311 Ferah Nabil 15/04/1968 Requête 2 : à partir de la table CLIENT, quels sont les clients ayant un prénom : Ahmed. SCLIENT2= = ′Ahmed′( ) = CLIENT [Prenom=’Ahmed’]= =Restrict(CLIENT, Prenom=’Ahmed’)=Ф. 2.2.2. Opérateurs binaires Ces opérateurs vont permettre, à partir de deux relations, d’en construire une troisième. La totalité des attributs de chacune des relations est conservée. Intersection : l’intersection de deux relations R1 et R2 est une nouvelle relation R dont les tuples appartiennent à R1 et R2. Les trois relations R1, R2 et R3 ont le même schéma. On notera : = 1 ∩ 2. La modélisation graphique de l’intersection est : Exemple 1 : supposant que deux bibliothèques B1 et B2 fusionnent et décident de rechercher les livres qu’elles ont en commun pour n’en garder qu’un exemplaire. Chacune des bibliothèques possède une relation LIVREB1 et LIVREB2 : LIVREB1 (NLIV, TITRE, NOMAUT). LIVREB2 (NLIV, TITRE, NOMAUT, PRENAUT). On ne peut pas faire l’union car les deux relations n’ont pas le même schéma. Exemple 2 : si maintenant on a : LIVREB1 (NLIV, TITRE, NOMAUT). Dr. Chergui Leila 22 Chapitre 2 : Le modèle relationnel LIVREB2 (NLIV, TITRE, NOMAUT). Et on veut répondre à la même question. Considérant LIVREB1 et LIVREB2 en extension : LIVREB1 LIVREB2 INTERLIVRE NLIV TITRE NOMAUT 101 Les fourmis Bertrand 102 Le soir des fourmis Bertrand 210 La révolte des fourmis Bertrand 104 Les 10 mousquetaires Artagnan NLIV TITRE NOMAUT 101 La basilique de Paris Victorien 102 Le soir des fourmis Bertrand 210 Le roi de la forêt Aiglon 104 Les 10 mousquetaires Artagnan NLIV TITRE NOMAUT 102 Le soir des fourmis Bertrand 104 Les 10 mousquetaires Artagnan Union : l’union de deux relations R1 et R2 est une nouvelle relation R dont les tuples appartiennent à R1 ou à R2 ou appartiennent à R1 et R2. Les trois relations ; R1, R2 et R3 ont le même schéma. On notera : = 1 ∪ 2. La modélisation graphique de l’union est : Exemple1 : concernant les deux relations : LIVREB1 (NLIV, TITRE, NOMAUT). LIVREB2 (NLIV, TITRE, NOMAUT, PRENAUT). On ne peut pas faire l’union car les deux relations n’ont pas le même schéma. Exemple 2 : si maintenant on a : LIVREB1 (NLIV, TITRE, NOMAUT). LIVREB2 (NLIV, TITRE, NOMAUT). Les deux bibliothèques décident de regrouper tous leurs livres. UNIONLIVRE= LIVREB1 ∪ LIVREB2. Le schéma de cette relation est : UNIONLIVRE (NLIV, TITRE, NOMAUT). Dr. Chergui Leila 23 Chapitre 2 : Le modèle relationnel Remarque La relation résultante n’a pas de clé primaire définie. Considérant LIVREB1 et LIVREB2 en extension : LIVREB1 LIVREB2 NLIV TITRE NOMAUT 101 Les fourmis Bertrand 102 Le soir des fourmis Bertrand 210 La révolte des fourmis Bertrand 104 Les 10 mousquetaires Artagnan NLIV TITRE NOMAUT 101 La basilique de Paris Victorien 102 Le soir des fourmis Bertrand 217 Le jardin en folie Rahhan On va visualiser cette union par étapes : 1. La relation UNIONLIVRE aura pour schéma (NLIV, TITRE, NOMAUT) (éliminer les tuples en doubles). 2. Puis, elle regroupera tous les tuples appartenant à LIVREB1 et LIVREB2. Il n’ya qu’un tuple qui vérifie cette condition. UNIONLIVRE NLIV TITRE NOMAUT 102 Le soir des fourmis Bertrand 3. On ajoutera les tuples restants de la relation LIVREB1. 4. On ajoutera les tuples restants de la relation LIVREB2. 5. Onrraura : UNIONLIVRE Dr. Chergui Leila NLIV TITRE NOMAUT 101 Les fourmis Bertrand 101 La basilique de Paris Victorien 102 Le soir des fourmis Bertrand 104 Les 10 mousquetaires Artagnan 210 La révolte des fourmis Bertrand 217 Le jardin en folie Rahhan 24 Chapitre 2 : Le modèle relationnel Différence : la différence R1-R2 de deux relations R1 et R2 est une nouvelle relation R dont les tuples appartiennent à R1 ET n’appartiennent pas à R2. Les trois relations R1, R2 et R3 ont le même schéma. On notera : R=R1-R2. Aucun exemplaire commun n’est conservé. La modélisation graphique de la différence est la suivant : Exemple : supposant qu’une grande librairie B1 achète une petite librairie B2 et décide de ne conserver en vente que les livres de B1 ; mais de plus, s’il y a un livre de B1 qui est référencé chez B2, il est retiré de la vente. Les relations en intension sont : LIVREB1 (NLIV, TITRE, NOMAUT). LIVREB2 (NLIV, TITRE, NOMAUT). La relation résultante DIFLIVRE aura pour schéma : (NLIV, TITRE, NOMAUT) et regroupera tous les tuples appartenant à LIVREB1 et n’appartenant pas à LIVREB2. DIFLIVRE= LIVREB1- LIVREB2. Considérant LIVREB1 et LIVREB2 en extension : LIVREB1 LIVREB2 NLIV TITRE NOMAUT 101 Les fourmis Bertrand 102 Le soir des fourmis Bertrand 210 La révolte des fourmis Bertrand 104 Les 10 mousquetaires Artagnan NLIV TITRE NOMAUT 101 La basilique de Paris Victorien 102 Le soir des fourmis Bertrand 217 Le jardin en folie Rahhan Nous allons visualiser cette différence par étapes : 1. La relation résultante DIFLIVRE aura pour schéma (NLIV, TITRE, NOMAUT). 2. Elle contiendra tous les tuples appartenant à LIVREB1. Dr. Chergui Leila 25 Chapitre 2 : Le modèle relationnel DIFLIVRE NLIV TITRE NOMAUT 101 Les fourmis Bertrand 102 Le soir des fourmis Bertrand 210 La révolte des fourmis Bertrand 104 Les 10 mousquetaires Artagnan 3. Puis, il faut ensuite éliminer de cet ensemble, les tuples qui appartiendraient aussi à LIVREB2. C'est-à-dire le tuple < 102, Le soir des fourmis, Bertrand >. Nous obtenons : DIFLIVRE NLIV TITRE NOMAUT 101 Les fourmis Bertrand 210 La révolte des fourmis Bertrand 104 Les 10 mousquetaires Artagnan Remarque 2 − 1 ≠ 1 − 2. Produit cartésien : le produit cartésien de deux relations R1 et R2 est la relation R, dont : Le schéma relationnel est constitué de la concaténation des attributs du schéma de R1 et du schéma de R2. Les tuples sont issus de toutes les combinaisons des tuples de R1 avec les tuples de R2. Les deux tables participant au produit cartésien n’ont pas forcément le même schéma. R On notera : R=R1xR2= R1*R2. Graphiquement on aura : Exemple 1 : LIVRE (NUML,TITRE, NBPAGE). x ANNEXE (NUMA, VIL). R1 LIVRE R2 ANNEXE NUML TITRE NBPAGE NUMA VIL 101 Les fourmis 375 367 CONSTANTINE 102 Le soir des fourmis 453 420 ALGER 210 La révolte des fourmis 423 600 ORAN En appliquant le produit cartésien, on aura une nouvelle relation contenant un tuple par ville possible et par livre possible. R1= LIVRE*ANNEXE. Dr. Chergui Leila 26 Chapitre 2 : Le modèle relationnel Son schéma est la concaténation de schéma de LIVRE et de schéma d’ANNEXE. R1 (NUML, NUMA,TITRE, NBPAGE, VIL). R1 en extension : NUML TITRE NBPAGE NUMA VIL 101 Les fourmis 375 367 CONSTANTINE 101 Les fourmis 375 420 ALGER 101 Les fourmis 375 600 ORAN 102 Le soir des fourmis 453 367 CONSTANTINE 102 Le soir des fourmis 453 420 ALGER 102 Le soir des fourmis 453 600 ORAN 210 La révolte des fourmis 423 367 CONSTANTINE 210 La révolte des fourmis 423 420 ALGER 210 La révolte des fourmis 423 600 ORAN Remarque La relation représentant la réalité sera généralement un sous-ensemble du produit cartésien. Jointure : la jointure est dérivée du produit cartésien avec, en plus, une condition permettant de comparer la valeur d’attributs. Il y aura une étape de concaténation d’attributs provenant des deux relations puis élimination des tuples ne vérifiant pas la condition de rapprochement. Ici encor, les deux tables n’ont pas forcément le même schéma. On trouve différents types ; les jointures internes (la -jointure, l’équi-jointure et la jointure naturelle), et les jointures externes (la jointure externe entière, la jointure externe gauche et la jointure externe droite). La jointure naturelle consiste donc à combiner deux tables ligne à ligne en vérifiant la concordance entre certaines colonnes des deux tables. Autrement dit, cela permet de relier deux tables ayant un champ commun et de faire correspondre les lignes qui ont une même valeur. R= R1 ⋈[ é é] R2. Elle porte sur des attributs de même nom, même domaine mais appartenant à des relations distinctes. Aussi, en pratique, nous ferons le plus souvent des jointures naturelles qui porteront sur une clé primaire et une clé étrangère. La modélisation graphique de la jointure est : R R1 Dr. Chergui Leila R2 27 Chapitre 2 : Le modèle relationnel Exemple 1 : LIVRE (NUML,TITRE, NomAUT, NUAN). ANNEXE (NUMA, VIL). Requête : on voudrait conservant que les livres dont le numéro d’annexe existe dans la relation annexe. LIVRE ANNEXE NUML TITRE NomAUT NUAN 101 Les fourmis Bertrand 375 102 Le soir des fourmis Bertrand 375 210 La révolte des fourmis Bertrand 600 NUMA VIL 367 CONSTANTINE 420 ALGER 600 ORAN On doit passer par les étapes suivantes : 1. Faire le produit cartésien des deux relations. 2. Eliminer les tuples qui ne vérifient pas la relation de jointure (NUAN=NUAN). 3. Supprimer l’attribut en double. La relation finale est : R2 NUML TITRE NomAUT NUAN VIL 210 La révolte des fourmis Bertrand 600 ORAN Division : c’est un opérateur binaire qui s’applique entre deux relations R1 et R2 pour donner une relation R= ÷ . Le schéma de la relation R2 doit être une partie du schéma de R1. La relation R obtenue aura le schéma de R1 moins les attributs de R2. Tout tuple de R sera tel que : quelque soit le tuple de R2 qui lui sera concaténé, il donnera un tuple de la relation initiale R1. Intérêt : 1. Elle permet de rechercher dans une table les sous-tables qui sont complétées par tous ceux d’une autre table. 2. Elle permet ainsi, de répondre à des requêtes de la forme : quelque soit X trouver Y. Dr. Chergui Leila 28 Chapitre 2 : Le modèle relationnel 2.2.3. Arbre algébrique Une expression algébrique peut être représentée sous forme d’arbre dont : La racine correspond à la requête. Les nœuds correspondent aux opérateurs algébriques. Les feuilles correspondent aux relations. L’objectif est de mieux comprendre l’optimisation logique de requête. Exemple 1 : LIVRE (NUML,TITRE, NomAUT, NUAN). ANNEXE (NUMA, VIL). Requête : on veut conserver que les titres des livres appartenant à une annexe et le nom de celle-ci. Donc, on applique une jointure naturelle suivie par une projection. R3= [ ]( ⋈[ . ). ] . L’arbre algébrique est : R3 Titre LIVRE ANNEXE LIVRE ANNEXE La relation R3 en extension est : TITRE VIL La révolte des fourmis ORAN Exemple 2 : LIVREB1 (NLIV, TITRE, NOMAUT). LIVREB2 (NLIV, TITRE, NOMAUT). Requête : on cherche les numéros et les titres des livres en commun des deux relations, dont le numéro de livre est inférieur à 200. Donc, on va faire une intersection suivie d’une projection suivie d’une sélection. R4= [ ] [ , ]( 1∩ 2). Donner l’arbre algébrique. Dr. Chergui Leila 29 Chapitre 2 : Le modèle relationnel 2.2.4. Fonctions et agrégats Tous les opérateurs précédents nous ont permis d’extraire les données brutes des tuples ; mais dans la gestion des BDs, ces informations seront insuffisantes pour répondre aux besoins des utilisateurs. En effet, nous n’avons pas traité les demandes d’informations de synthèse, telle que le calcul d’un chiffre d’affaires par département, le cumul des montants des achats d’un client, etc. C’est pour cela qu’il faut introduire les fonctions de calcul et d’agrégation dans l’algèbre relationnelle. Fonction de calcul : il est possible de remplacer, dans les conditions des opérations, un attribut utilisé en tant qu’argument par une composition des fonctions appliquées sur des attributs de la relation ou des constantes. Généralement, ce sont des fonctions arithmétiques qui seront utilisées. Ainsi, il est possible d’additionner des attributs, d’ajouter une constante à un attribut, etc. Remarque Les domaines des valeurs des attributs auxquels sont appliquées les fonctions doivent être compatibles avec celles-ci. Il ne sera pas possible d’additionner un attribut ville (chaîne de caractères) à une constante numérique. Exemple 1 : Considérant la relation CLIENT (NUMC, NOM, PRENOM, ACHATTOTAL). En extension : CLIENT NUMC NOM PRENOM ACHATTOTAL 101 Khaldi Nabil 802 102 Badaoui Fateh 50 210 Maarouf Samir 354 104 Seradj Omar 1098 214 Fareh Sami 950 310 Taleb Salim 900 Requête : on veut connaître les numéros des clients à qui il suffirait de faire un achat de 100DA pour égaliser ou dépasser un total d’achats de 1000DA. Il faut extraire les tuples dont le total d’achats effectués+100DA est supérieur à 1000DA. Arithmétiquement, cela s’écrira ACHATTOTAL+100 ≥1000. Ensuite, on fera la projection. Dr. Chergui Leila 30 Chapitre 2 : Le modèle relationnel La relation CLIENT [ACHATTOTAL+100≥1000] en extension : NUMC NOM PRENOM ACHATTOTAL 104 Seradj Omar 1098 214 Fareh Sami 950 310 Taleb Salim 900 La relation finale en extension : NUMC NOM 104 Seradj 214 Fareh 310 Taleb Fonctions d’agrégat : elles vont permettre de calculer une valeur simple à partir d’un ensemble de valeurs provenant d’un même attribut mais plusieurs tuples d’une relation. Ces fonctions pourront s’appliquer à tous les tuples ou à une sélection de tuples d’une relation. Les fonctions courantes, que l’on retrouvera en SQL, sont les suivantes : COMPTE : compter les valeurs d’un attribut d’une relation. SOMME : additionner les valeurs d’un attribut d’une relation. MOYENNE : effectuer la moyenne des valeurs d’un attribut d’une relation. MAXIMUM : chercher la valeur maximale d’un attribut d’une relation. MINIMUM : chercher la valeur minimale d’un attribut d’une relation. Exemple : considérant la relation CLIENT (NUMC, NOM, PRENOM, ACHATTOTAL). En extension : CLIENT NUMC NOM PRENOM ACHATTOTAL 101 Khaldi Nabil 802 102 Badaoui Fateh 50 210 Maarouf Samir 354 104 Seradj Omar 1098 214 Fareh Sami 950 310 Taleb Salim 900 Requête : on veut connaître le total des achats effectués par tous les clients. Dr. Chergui Leila 31 Chapitre 2 : Le modèle relationnel On va faire la somme de toutes les valeurs de l’attribut : ACHATOTAL. Et seul le résultat de cette fonction nous suffit, c’est-à-dire, les autres attributs doivent être éliminés du résultat. R= [ ( )] . Arbre algébrique : R Somme (ACHATTOTAL) CLIENT Dr. Chergui Leila 32 Chapitre 3: Le calcul relationnel Chapitre 3 : Le calcul relationnel Introduction Il existe une différence entre l’algèbre relationnel et le calcul relationnel : Algèbre relationnelle : langage procédural permettant d'expliciter une séquence d'opérations qui conduiront à un résultat désiré. Il est opérationnel. L’algèbre relationnelle permet de spécifier quelles sont les opérations à exécuter pour calculer le résultat de la requête. Calcul relationnel : langage non-procédural (déclaratif) permettant d'expliciter le résultat que l'on désire sans spécifier la séquence des opérations à effectuer. Il n’est pas opérationnel. C’est un langage prédicatif qui permet de ne spécifier que le résultat cherché (pas comment le calculer). Il s’agit de spécifier des prédicats qui doivent être vérifiés par les données pour former le résultat. On le qualifie de langage prédicatif car il est basé sur le calcul de prédicats (logique du 1er ordre). 3.1. Rappel sur le calcul des prédicats Le calcul relationnel est basé sur le calcul des prédicats. Pour cette raison, nous allons introduire un rappel sur la logique des prédicats. 3.1.1. Syntaxe L’alphabet du langage est composé des éléments suivants : Un ensemble de variables notées x, y, z, etc. Un ensemble de fonctions notées f, g, h ayant chacune une arité (nombre d’arguments). Un ensemble de symboles de prédicats notés P, Q, R, etc. ayant chacun une arité. Des parenthèses : ( ). Les connecteurs : (non), ∧ (et), ∨ (ou), → (implique). Les quantificateurs : (il existe) et (quel que soit). 3.1.2. Notification Terme : Chaque variable est un terme. Si t1, t2,…,tn sont des termes et f est un symbole de fonction alors f(t1, t2,…,tn) est un terme. Un terme est clos (fermé) s’il ne contient aucune variable. Dr. Chergui Leila 34 Chapitre 3 : Le calcul relationnel Atome : il est de la forme P(t1, t2,…,tn) où P est un symbole de prédicat d’arité n et t1, t2,…,tn sont des termes. Formule : Chaque atome est une formule. Si A est une formule alors A est une formule. Si A et B sont deux formules alors A∧ , A∨B et A→ Si A est une formule et x est une variable alors ∀ sont des formules. ( ) et ∃ ( ) sont des formules. Variable libre et variable liée : une variable est dite libre dans une formule A, si elle n’est pas quantifiée (elle n’apparaît pas après les quantificateurs ou ). Formalisation du langage naturel : le langage naturel peut être formalisé en utilisant le calcul de prédicat. Exemples : Tous les étudiants sont intelligents : x (Etudiant(x)Intelligent(x)). Seulement les étudiants sont intelligents : x (Intelligent (x) Etudiant (x)). Il existe un étudiant intelligent : x (Etudiant(x) ∧ Intelligent(x)). Il n’existe pas un étudiant intelligent : x (Etudiant(x) ∧ Intelligent(x)). Il existe des étudiants qui réussissent dans tous les modules : x (Etudiant(x)∧y(Module(y) Reussi(x,y)). 3.1.3. Sémantique Une formule prend les valeurs vrai ou faux. Si A est vrai alors A est faux. A∧B est vrai si et seulement si A est vrai et B est vrai. A∨B est vrai si et seulement si A est vrai ou B est vrai ou les deux en même temps. 3.2. Langage relationnel prédicatif On trouve deux types : 3.2.1. Calcule de tuples Dans ce type, les variables dans les expressions logiques portent sur les tuples des relations. Comme exemple de langage, on trouve : ALPHA, langage QUEL (SGBD INGRES), et SQL. Dr. Chergui Leila 35 Chapitre 3 : Le calcul relationnel C’est un langage d’interrogation de données formel permettant d’exprimer des questions à partir de formules bien formées, où les variables prennent leurs valeurs dans les tuples d’une relation, on parle de variable tuple. Ainsi la valeur d’une variable tuple peut être dans n’importe quelle ligne d’une relation sur laquelle elle est définie, on écrira : nom de la relation (nom de la variable tuple). Ex : x Etudiant. Exemple : Soit la relation : Module (Codem, Libellé, créd). En extension : Module Codem Libellé crédit ALGO Algorithmique 4 SEXP Système d’exploitation 5 BD Base de Données 3 Module (x) : signifie que x est une variable tuple de la relation Module et peut prendre l’une des valeurs suivantes : x=(‘ ALGO’, ‘Algorithmique’, 4) : x.Codem=‘ ALGO’, x.Libellé=‘Algorithmique’, x.créd=4. x=(‘ SEXP’, ‘Système d’exploitation’, 5). x=(‘ BD’, ‘Base de Données’, 3). Formulation d’une requête : une requête se présente sous la forme suivante : {Résultat de la requête/Prédicat(formule) à vérifier par les variables du résultat}. Le résulta de la requête s’écrit sous la forme : nom de variable.nom d’attribut. Formule élémentaire : elle s’écrit sous la forme : variable.nom d’attribut opérateur variable.nom d’attribut. L’opérateur est l’un des opérateurs de comparaison usuels : ‘=’, ‘>’, ‘<’, ‘<>’, ‘>=’, ‘<=’. Le prédicat à vérifier est une combinaison des formules élémentaires. Remarque Une variable tuple qui apparaît dans le résultat de la requête ne doit pas être liée dans le prédicat à vérifier, toutes les autres variables sont liées. Dr. Chergui Leila 36 Chapitre 3 : Le calcul relationnel Exemple : {x.Codem/x (Module (x)et x.créd=5)}est faux. Car x doit être libre dans le prédicat spécifié (elle ne doit pas être quantifiée). Exemple : soit la relation : Etudiant (Num, nom, prénom, datnais, adr). Requête 1 : quels sont les noms des étudiants qui sont nés après 1982. Réponse : {x.nom/Etudiant(x)∧x.datnais>=01/01/1982}. Remarque Si plusieurs attributs sont dans le résultat, ils seront séparés par des virgules. Requête 2 : quels sont les noms et les prénoms des étudiants qui résident à Alger. {x.nom, x.prénom/Etudiant(x)∧x.adr= « Alger »}. Requête multi-relation : elles opèrent sur des relations différentes, et donc il faut manipuler des variables tuples différentes. Exemple : soit les relations suivantes : Module (Codem, Libellé, créd, annétude). Enseignant (Num, nom, prénom, datnais, adr, grad, nbheur). Modens (Num, Codem). Requête 1 : quels sont les noms des enseignants qui interviennent dans les modules de 2ème année. Réponse : {x.nom/Enseignant(x)∧y, z (Module(y)∧ Modens(z) ∧ y.annétude=2 ∧ y.Codem=z.Codem ∧x.Num=z.Num)}. Requête 2 : quels sont les noms et les adresses des enseignants qui assurent tous les modules de 2ème année. Réponse : {x.nom, x.adr /Enseignant(x)∧y, z (Module(y)∧y.annétude=2z (Modens(z) ∧y.Codem=z.Codem ∧x.Num=z.Num)}. 3.2.2. Calcul de domaines Ici, les variables dans les expressions logiques portent sur les valeurs des attributs des tuples. Exemple de langage : langage QBE. Les formules du calcul de domaines sont définies comme pour le calcul de tuples, en remplaçant les variables tuples par les variables domaines. Ex : x Etudiant.nom. Dr. Chergui Leila 37 Chapitre 3 : Le calcul relationnel Remarques L’ordre des attributs devient important. Les requêtes sont spécifiées ainsi : {x1, x2, ………, xn / f(x1, x2, ………, xn)}. Exemple : soit la relation : Etudiant (Num, nom, prénom, datnais, adr). Requête 1 : quels sont les noms des étudiants qui sont nés après 1982. Réponse : {n/ a(Etudiant(nom :n, datnais :a)∧x.datnais>=01/01/1982)}. La différence essentielle entre le calcul relationnel des tuples et le calcul relationnel des domaines est l’ensemble dans lequel les variables prennent leurs valeurs : Tuples : chaque variable prend ses valeurs dans l’ensemble des tuples d’une relation particulière. Domaines : chaque variable prend ses valeurs dans un domaine particulier des attributs de la base. Remarque Le calcul relationnel et l’algèbre relationnelle ont une puissance d’expression équivalente (complétude relationnelle), donc, on peut traduire tout opérateur de l’algèbre relationnelle en calcul relationnel de tuples. 3.2.3. Passage de l’algèbre relationnelle au calcul relationnel tuple Une relation se traduit par le prédicat correspondant. On peut insérer les conditions des sélections directement dans les formules (en les combinant en général avec ∧). La projection correspond à ajouter des devant les variables tuple dont aucun attribut n’est projeté. L’union se traduit par un ⋁. La différence A - B se traduit par Le produit A x B se traduit par un FA ⋀ FB avec les variables libres de FA et FB ∧ où FA et FB ont les mêmes variables libres. disjointes. Le renommage peut, lorsque cela est nécessaire, se traduire via l’introduction d’une nouvelle variable tuple combinée avec des égalités. La jointure naturelle se traduit par l’ajout d’égalités entre les attributs communs aux relations constituant la jointure. Dr. Chergui Leila 38 Chapitre 3 : Le calcul relationnel 3.2.4. Transfert du calcul relationnel à l’algèbre relationnelle Un prédicat se traduit par la relation correspondante. Un se traduit en général par une projection. Un ∧ se traduit par un produit cartésien ou une intersection. Un ⋁ se traduit par une union. Une se traduit par une différence, mais la traduction n’est généralement pas directe. On peut utiliser le renommage en cas de conflit sur les attributs. On peut utiliser une jointure naturelle lorsque les attributs communs à plusieurs relations sont liés par des variables. Dr. Chergui Leila Une comparaison se traduit par une sélection. 39 Chapitre 4: SQL : Interrogation d’une base de données Chapitre 4 : SQL : Interrogation d’une base de données Introduction SQL (Structured Query Langage ou bien langage de requête structuré) est un langage informatique standard pour la communication avec les SGBDRs. Il a été défini par l’ANSI (American National Standard Institute) et l’ISO (International Standards Organization). SQL peut être vu comme une boite à outils comprenant à la fois un formalisme proche, du langage algébrique, et un autre proche du langage prédicatif. Il existe alors souvent, plus d’une façon d’exprimer une même requête. 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 la stratégie d’exécution. Le langage SQL est un langage déclaratif qui permet d'interroger une base de données sans se soucier de la représentation interne (physique) des données, de leur localisation, des chemins d'accès, ou des algorithmes nécessaires. 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, c’est-à-dire des ensembles) par l’intermédiaire de requêtes qui produisent également des tables. SQL peut être utilisé : D’une manière interactive. En association avec des interfaces graphiques. Ou, très généralement, des langages de programmation. Le tableau 4.1 présente un historique du langage SQL. SQL est le langage des bases de données relationnelles répondant à la fois à la problématique de création des objets de bases de données (modèle), de manipulation des données (algèbre relationnelle), de gestion de la sécurité (droits d’accès), de traitements locaux de données (procédures). De plus, il est désormais doté d’extensions objet. Dans ce chapitre, on va s’intéresser à l’interrogation d’une BD en utilisant le langage SQL. Dr. Chergui Leila 41 Chapitre 4 : SQL : Interrogation d’une base de données Année Appellations 1986 SQL-86-SQL87 Apports majeurs 1ère publication par l’ANSI. Ratification par l’ISO en 1987. 1989 SQL-89 1992 SQL92, SQL2 Révisions majeurs (ISO 9075). 1999 SQL99, SQL3 Expressions régulières. Révisions mineurs. Récursivité. Déclencheurs. Langage procédural. Types abstraits. Aspects objet. 2003 SQL:2003 Prise en compte de XML. Fonctions de fenêtrage. Séquences. Colonnes d’identité. 2006 Intégration plus forte avec XML (XQuerry). 2008 SQL2008 Correction de certains défauts et de petits manques des versions antérieures (fonctions, types, curseurs, etc.). Tableau 4.1. Historique du langage SQL. 4.1. Structure des instructions SQL : Langage de Manipulation de Données (LMD) Toute instruction ou command SQL est une combinaison d’une ou de plusieurs clauses. Les clauses sont généralement introduites par des mots-clés. SELECT attribut1, attribut2,.., attributn FROM Nom de la table WHERE condition booléenne; Dr. Chergui Leila 42 Chapitre 4 : SQL : Interrogation d’une base de données 4.1.1. Clauses de base On doit suivre les règles d’écriture suivantes : 1. Les instructions SQL ne sont pas sensibles à la casse. Toutefois, les mots clés qui débutent par une clause sont généralement écrits en majuscules pour améliorer la lisibilité des instructions SQL. 2. Les instructions SQL peuvent être écrites sur une ou plusieurs lignes. Il est habituel d’écrire chaque clause sur une ligne distincte. 3. Un mot-clé ne peut être réparti sur deux lignes. 4. Les instructions SQL se terminent par un point-virgule. Le principe des clauses utilisées pour manipuler les données est : SELECT : indique la liste des attributs constituant le résultat. FROM : indique la (ou les) tables dans lesquelles on trouve les attributs utiles à la requête. WHERE : indique les conditions que doivent satisfaire les n-uplets de la base pour faire partie du résultat. La comparaison peut être effectuée suivant l’un des opérateurs présentés dans la figure 4.1. Figure 4.1. Opérateurs de comparaison. Remarque Pour afficher l'intégralité d'une table, et avoir ainsi toutes les lignes (on omet la clause WHERE), et toutes les colonnes, on peut au choix lister tous les attributs ou utiliser le caractère * qui a la même signification. ORDER BY : est utilisée pour trier les résultats d'une requête. Cette clause doit être suivie de la liste des attributs servant de critère au tri. Pour trier en ordre descendant, on ajoute le motclé DESC après la liste des attributs. Et pour avoir un tri ascendant, on ajoute le mot-clé ASC. Par défaut les attributs seront triés en ordre ascendant. Dr. Chergui Leila 43 Chapitre 4 : SQL : Interrogation d’une base de données Remarques 1. Pour obtenir une recherche par intervalle, on peut également utiliser le mot-clé BETWEEN. 2. Lorsque le SGBD construit la réponse d’une requête, il rapatrie toutes les lignes qui satisfont la requête, généralement dans l’ordre ou il les trouve, même si ces dernières sont en double (comportement ALL par défaut). C’est pourquoi il est souvent nécessaire d’utiliser le mot clé DISTINCT qui permet d’éliminer les doublons dans la réponse. 3. Il est possible d’utiliser les opérateurs mathématiques de base (‘+’, ‘-’, ‘*’ et ‘/’) pour générer de nouvelles colonnes à partir, en générale, d’une ou plusieurs colonnes existantes. 4. Le mot clé AS permet de renommer une colonne, ou de nommer une colonne créée dans la requête. 5. L’opérateur || (double barre verticale) permet de concaténer des champs de type caractères. 6. L’opérateur IN spécifie un ensemble de valeurs possibles. 7. En pratique, il est possible d’avoir des valeurs non définies qui sont représentées par le mot clé NULL. On peut tester si une valeur n’est pas définie grâce à la condition IS NULL (ou son contraire IS NOT NULL). 8. Si on trouve plusieurs tables dans une requête, cela représente un produit cartésien entre ces différentes tables. 9. Si un attribut est présent dans plusieurs tables utilisées, on doit l’écrire : nom-table.att. 10. Dans les comparaisons des chaînes de caractères, il est possible d’utiliser l’opérateur LIKE : une chaîne commence par un ensemble de caractères, et les caractères génériques, ‘%’ qui remplace une chaîne de caractères de taille quelconque, et ‘ ?’ qui remplace un seul caractère. 11. En SQL, la jointure s’exprime comme une sélection sur le produit cartésien : SELECT att1, att2 FROM nom_table1, nom_table2 WHERE nom_table1.attx = nom_table2.attx; 4.1.2. Sous-requête C’est l’utilisation du résultat d’une requête dans une autre requête. Le but est l’augmentation de la puissance d’expression du langage. On peut utiliser les opérateurs suivants : 1. A IN (sous-requête), elle est vrai si A apparaît dans le résultat du sous-requête. Dr. Chergui Leila 44 Chapitre 4 : SQL : Interrogation d’une base de données 2. A (=; <; >;<=;>=) ANY (sous-requête), elle est vrai s’il existe un b parmi les lignes renvoyées par la sous-requête tel que A (=; <; >;<=;>=) b soit vrai. 3. A (=; <; >;<=;>=) ALL (sous-requête), elle est vrai si pour toutes les lignes b renvoyées par la sous-requête tel que A (=; <; >;<=;>=) b soit vrai. 4. EXISTS (sous-requête) (et son contraire NOT EXISTS (sous-requête)), elle est vrai si le résultat de la sous-requête n’est pas vide, avec au moins un tuple. 5. On peut tester l’inclusion entre les ensembles via le mot clé CONTAINS. 4.1.3. Fonctions d’agrégats et regroupement Fonctions d’agrégat : ensemble de fonctions qui permettent d’effectuer des statistiques sur le résultat d’une requête (MIN : minimum, MAX : maximum, SUM : somme, AVG : moyenne, COUNT : nombre, etc.). Regroupements : possibilité de regrouper plusieurs lignes d’une même requêtes (souvent associés aux fonctions d’agrégat). Pour cela, on utilise les clauses : GROUP BY : permet de subdiviser la table en groupes, chaque groupe étant l’ensemble des lignes ayant une valeur commune. HAVING : permet de sélectionner des groupes de la requête de regroupement. Elle permet d’introduire des conditions sur les groupes, afin d’éliminer du résultat les groupes n’obéissant pas à certains critères. Il faut respecter les règles suivantes : Les colonnes du SELECT doivent toutes apparaître dans le GROUP BY (sauf les opérations). Les noms de colonnes dans le HAVING doivent aussi être dans le GROUP BY (ou être fonction d’agrégat). La clause GROUP BY va regrouper les lignes qui sont identiques sur les colonnes mentionnées dans le GROUP BY. Différence entre WHERE et HAVING WHERE : sélectionne les lignes de la requête avant de faire les groupes. Elle agit donc avant les regroupements. HAVING : sélectionne les groupes une fois qu’ils sont constitués. Elle agit donc après les regroupements. Dr. Chergui Leila 45 Chapitre 4 : SQL : Interrogation d’une base de données 4.1.4. Opérateurs ensemblistes UNION : permet de fusionner deux tables ayant le même schéma. INTERSECT : permet d’obtenir les lignes communes de deux tables. MINUS : permet d’effectuer la différence entre deux tables. (EXCEPT pour la norme SQL2). Remarque SQL ne comporte pas d’opérateurs spécifiques à la division. Cependant, il est possible d’exprimer la sémantique de cet opérateur en se basant sur les opérateurs logiques. Dr. Chergui Leila 46 Chapitre 5: SQL : Définition et modification d’une base de données Chapitre 5 : SQL : Définition et modification d’une base de données Introduction SQL est un Langage de Définition de Données (LDD), c'est-à-dire qu'il permet de créer des tables dans une base de données relationnelle, ainsi que d'en modifier ou en supprimer. 5.1. Création d’une table Les tables sont à la base de stockage des données dans les SGBDRs, elles comportent toutes les données accessibles à l’utilisateur. Pour en créer une, il faut lui donner un nom et tous les attributs qu’elle doit comporter. De plus, pour chaque attribut, l’utilisateur doit définir le type de données concernées et, si nécessaire, les contraintes appropriées. Le nom d’une table l’identifie comme objet unique dans le SGBDR. Les noms de colonnes ou attributs doivent être uniques pour une table donnée. 5.1.1. Règles de création des tables avec SQL Les tables SQL sont créées par l’instruction ou commande CREATE TABLE comme suit : CREATE TABLE nom de la table ( Nom de la colonne 1 type de données [valeur par défaut] [contrainte], Nom de la colonne 2 type de données [valeur par défaut] [contrainte], type de données [valeur par défaut] [contrainte] .. Nom de la colonne n ); Exemple : créer une table nommée étudiant qui porte deux attributs, le nom et le prénom. Les contraintes définies en SQL sont : Non nullité des valeurs d'un attribut en se servant du mot clé NOT NULL qui permet de vérifier qu'il existe une valeur pour chaque élément de la colonne. Unicité de la valeur d'un attribut ou d'un groupe d'attributs en utilisant la clause UNIQUE qui permet de s'assurer qu'il n'existe pas de valeur dupliquée dans la colonne. Valeur par défaut pour un attribut. Contrainte de domaine en utilisant la clause CHECK qui permet de spécifier des conditions logiques portant sur une ou plusieurs colonnes d'une même table. Clé primaire (un attribut ou un groupe) par la clause PRIMARY KEY qui a le même rôle que la clause UNIQUE mais ne peut être spécifiée qu'une seule fois dans la table. Dr. Chergui Leila 48 Chapitre 5 : SQL : Définition et modification d’une base de données Intégrité référentielle "minimale" par la clause REFERENCES sur les colonnes qui matérialise une dépendance entre deux colonnes de la table. Et par la clause FOREIGN KEY sur la table qui matérialise une dépendance entre deux colonnes de deux tables Exemple 1 : CREATE TABLE étudiant ( Num INTEGER PRIMARY KEY, Nom CHAR(40) NOT NULL, Année INTEGER CONSTRAINT CAnnée CHECK (Année BETWEEN 1997 AND 2013), Moyenne Number(2,3) CONSTRAINT CMoyenne CHECK (Moyenne BETWEEN 00,000 AND 20,000)); Exemple 2 : CREATE TABLE Consommateur ( ID INTEGER CONSTRAINT PK_Consommateur PRIMARY KEY, Nom TEXT(50) NOT NULL, Prénom TEXT(50) NOT NULL, Tél TEXT(10), Email TEXT(50), Address TEXT(40)); Exemple 3 : CREATE TABLE Véhicules (Nom TEXT(30), Année TEXT(4), Prix CURRENCY); Types de données utilisés : voici un ensemble représentatif des types de données utilisés lors de la création d’une table en SQL dans ACCESS : Booléen : BIT. Nombre entier : SHORT (entier), SMALINT (entier), LONG (entier long), INTEGER (entier long). Nombre réel : SINGLE (réel simple), DOUBLE (réel double), Numeric (réel double). Monétaire : CURRENCY, MONEY. Date/heure : DATE, TIME, DATETIME. Texte: VARCHAR(255), CHAR(n) ou TEXT(n), où n est le nombre de caractères. Dr. Chergui Leila 49 Chapitre 5 : SQL : Définition et modification d’une base de données 5.1.2. Opérations sur les tables Avec SQL, on peut supprimer, modifier ou renommer des tables. Suppression d’une table : supprimer une table revient à éliminer sa structure et toutes les données qu’elle contient. Les index associés sont également supprimés. La syntaxe est la suivante : DROP TABLE nom_table; Il ne faut pas qu’une clé étrangère d’une autre table référence la table à supprimer. En Oracle, on peut ajouter à la fin le mot clé CASCADE pour déclencher la suppression des clés étrangères qui référencent la table à supprimer. Exemple : DROP TABLE étudiant; Modifier une table : en utilisant l’instruction : ALTER TABLE qui permet de modifier la structure de la table, elle consiste donc à ajouter ou modifier des colonnes de la table. Syntaxe : ALTER TABLE nom_table modification; On peut avoir les situations suivantes : ALTER TABLE nom table ADD att type NOT NULL; Ajouter à la table nom table un attribut att contenant des données correspondant à type. On peut optionnellement spécifier NOT NULL lorsque l’on souhaite interdire la valeur NULL. Ex : ALTER TABLE Véhicules ADD COLUMN État TEXT(10); ALTER TABLE nom table ALTER att nouveau type NOT NULL; Changer le type de l’attribut att, en spécifiant optionnellement NOT NULL. Ex : ALTER TABLE Véhicules ALTER État Text; ALTER TABLE nom table RENAME COLUMN att TO nouvel att; Changer le nom de att en nouvel att. ALTER TABLE nom table DROP COLUMN att; Supprimer l’attribut att de la table nom table.A Ex : ALTER TABLE Véhicules DROP COLUMN Nom; jouter ou ALTER TABLE nom table ADD CONSTRAINT nomc contrainte; Ajouter la contrainte contrainte sur la table nom table. CONSTRAINT nomc spécifie le nom optionnel de la contrainte. ALTER TABLE nom table DROP PRIMARY KEY; Supprimer la clé primaire. Dr. Chergui Leila 50 Chapitre 5 : SQL : Définition et modification d’une base de données ALTER TABLE nom table DROP FOREIGN KEY nom cle; Supprimer la clé étrangère nommée nom cle. Renommage d’une table : on peut renommer une table avec l’instruction suivante : RENAME ancien_nom TO nouveau_nom; 5.1.3. Manipulation sur les tables Dans SQL, on peut manipuler les lignes des tables par l’utilisation des trois instructions de langage de manipulation de données ; INSERT INTO, UPDATE et DELETE. Insertion de n-uplets : la commande INSERT INTO permet d’insérer une ligne dans une table en spécifiant les valeurs à insérer. La syntaxe est la suivante : INSERT INTO nom_table (nom_col_1, nom_col_2, ...) VALUES (val_1, val_2, ...) La liste des noms de colonne est optionnelle. Si elle est omise, la liste des colonnes sera par défaut la liste de l’ensemble des colonnes de la table dans l’ordre de la création de la table. Si une liste de colonnes est spécifiée, les colonnes ne figurant pas dans la liste auront la valeur NULL. Il est possible d’insérer dans une table des lignes provenant d’une autre table. La syntaxe est la suivante : INSERT INTO nom_table (nom_col1, nom_col2, ...) SELECT Exemple: INSERT INTO étudiant Values (‘123’, ‘Talebi’, ‘2010’,’12,54’); Modification des lignes : la commande UPDATE permet de modifier les valeurs d’une ou plusieurs colonnes, dans une ou plusieurs lignes existantes d’une table. La syntaxe est la suivante : UPDATE nom_table SET nom_col_1 = {expression_1 | ( SELECT ...) }, nom_col_2 = {expression_2 | ( SELECT ...) }, ... nom_col_n = {expression_n | ( SELECT ...) } WHERE predicat; Dr. Chergui Leila 51 Chapitre 5 : SQL : Définition et modification d’une base de données Les valeurs des colonnes nom_col_1, nom_col_2, ..., nom_col_n sont modifiées dans toutes les lignes qui satisfont le prédicat. En l’absence d’une clause WHERE, toutes les lignes sont mises à jour. Les expressions expression_1, expression_2, ..., expression_n peuvent faire référence aux anciennes valeurs de la ligne. Exemple : UPDATE TABLE étudiant SET Moyenne=’14,56’ WHERE Nom=’Talbi’; Suppression de n-uplets : la commande DELETE permet de supprimer des lignes d’une table. La syntaxe est la suivante : DELETE FROM nom_table WHERE predicat Toutes les lignes pour lesquelles le prédicat est évalué à vrai sont supprimées. En l’absence de clause WHERE, toutes les lignes de la table sont supprimées. Exemple : DELETE FROM TABLE étudiant WHERE Nom=’Talbi’; Dr. Chergui Leila 52