Déroulement 2 Plan: Chap. 1 - Notion de base de données; Chap. 2 - Base de données relationnelles; Chap. 3 - Langage SQL Cours +TP=33h Evaluation: 40% 60% : (Note CC1+ Note TP+Travaux à rendre) : Note Examen Ecrit. GESTION DES BASE DE DONNÉES ENCG-K Semestre 5 2014 Bases de données omniprésentes 3 Chap. 1 Notion de Bases de données 4 1. 2. 3. 4. 5. 6. Introduction: Exemple de gestion d’entreprise 5 Service clients : Fichier client : code client ; Nom client ; Adresse Service commercial : N°commande ; date de la commande ; total de la commande ; code client ; Nom client ; Adresse. Plan de la leçon: Introduction; Définition d’une base de données et d’un SGBD; Différents types de bases de données; Quelques SGBD existants; Objectifs et avantages des BD et SGBD; Conclusion. Introduction 6 Introduction: Deux approches: - Approche Fichiers: données en fonction du traitement risque d’incohérence; - Approche base de données : intégration des données dans un seul fichier/indépendance des traitements. 1 Introduction: BD/Excel? 7 Définition d’une BD et d’un SGBD 8 Une BD: - Ensemble structuré de données indépendant des traitements à effectuer. - Représente le monde réel. - Interrogée et mise à jour par des utilisateurs. Redondance : – Données répétées (info client si plusieurs commandes, info produit si plusieurs fois commandé…) • Réécriture – Orthographe, perte de temps, mise à jour difficile • Définition d’une BD et d’un SGBD 9 Différents types de BD 10 Un SGBD: - logiciel permettant à un utilisateur d’interagir avec une BD. - Interface entre la BD et les programmes d’applications qui communiquent avec la BD. - Permet: - Il existe quatre grands types de bases de données : -Les bases hiérarchiques; -Les bases réseaux; -Les bases relationnelles; -Les bases objets. la définition d'une BD (spécification des types de données à stocker), la construction d'une BD,(stockage des données) la manipulation des données (ajouter, supprimer, retrouver des données). Modèle hiérarchique Modèle hiérarchique 11 12 Appartient à la deuxième génération 1965 – 70 s’appelle aussi, SGBD navigationnelle; Présente les données sous forme d’un arbre; Une donnée peut référencer une autre donnée dans une relation père/fils; Lien entre enregistrements par des pointeurs Inconvénients : Redondance de données. difficile de données hétérogènes. Manipulation 2 Modèle réseau 13 Modèle relationnel 14 Appartient aussi à la 2ème génération; Possibilité des liens entre enregistrements dans différents sens 3ème génération 1969-… les données sont représentées dans des tables Manipulation suivant les règles mathématiques des ensembles Avantage par rapport aux SGBD hiérarchiques : la non redondance de données et la rapidité. Inconvénients : La schéma de la BD devient complexe quand la base de données est importante. Le modèle Objet 15 Différents types de BD 16 Apparition entre 1990 - 1999 Les données sont représentées en tant qu’instances de classes hiérarchisées Quelques SGBD existants 17 Aujourd’hui, le modèle relationnel est le plus utilisé il occupe environ 75% Quelques SGBD existants : Bases relationnelles 18 Hiérarchiques : IMS; Réseaux : IDS2, Socrate-Clio; Les petites BD: • FileMaker Pro • Access • FoxPro • 4e dimension • MySQL • Paradox Les grosses BD • Oracle • Informix • Sybase • ProsgreSQL • DB2 • Ingres • (SQLServer) 3 Quelques SGBD existants 19 Objectifs et avantages 20 Les principales fonctions d’un SGBD: - Indépendance données/programme (indépendance physique); - Indépendance logique; - Intégration des données sans redondance; - Partage de données; - Intégrité des données; - Sécurité des données et confidentialité. Bases objet : • O2 • Gemstone • ObjectStore • Jasmine Indépendance logique Indépendance physique 21 22 Ils ne sont pas apparents: Les disques, la machine, les méthodes d’accès, les modes de placement, les méthodes de tri, le codage des données. Le SGBD offre une structure canonique permettant la représentation des données réelles sans se soucier de l’aspect matériel du système. Chaque groupe de travail doit pouvoir se concentrer sur ce qui l’intéresse. Peut arranger les données comme il souhaite, même si d’autres utilisateurs ont une vue différente. L’administrateur peut faire évoluer le SI sans remettre en cause l’organisation de chaque groupe de travail. Exemple: Une base de données contient les informations suivantes: véhicule(num-véhicule, marque, type, couleur) personne(num-CIN, nom, prénom) propriétaire(num-CIN, num-véhicule, date-achat). Un groupe de travail ne s’intéressera qu’aux individus qui possèdent une voiture : Un autre groupe ne s’intéressera qu’aux véhicules vendus à une certaine date : voiture(num-véhicule, type, marque, date-achat). Cohérence des données Non-redondance des données 23 individus(num-CIN, nom, prénom, num-véhicule). 24 Le SGBD doit permettre d’éviter la duplication d’informations qui entraine: la perte de place mémoire, demande des moyens humains importants pour saisir et maintenir à jour plusieurs fois les mêmes données. Cohérence obtenue par la vérification des contraintes d’intégrité. Une contrainte d’intégrité est une contrainte sur les données de la base, qui doit toujours être vérifiée pour assurer la cohérence de cette base. Les systèmes d’information sont souvent remplis de telles contraintes ; le SGBD doit permettre une gestion automatique de ces contraintes d’intégrité sur les données. Par exemple : un identifiant doit toujours être saisi ; le salaire doit être positif ; 4 Concurrence d’accès aux données 25 Sécurité des données 26 Le SGBD doit permettre à plusieurs personnes (ou applications) d’accéder simultanément aux données tout en conservant l’intégrité de la base. Chacun doit avoir l’impression qu’il est seul à utiliser les données. Les données doivent être protégées des accès non autorisés ou mal intentionnés. Il doit exister des mécanismes permettant d’autoriser, contrôler et enlever des droits d’accès à certaines informations à n’importe quel usager. Exemple, un chef de service pourra connaître les salaires des personnes qu’il dirige, mais pas de toute l’entreprise. Le système doit aussi tolérer les pannes : si une panne pendant l’exécution d’une opération, le SGBD doit être capable de revenir à un état dans lequel les données sont cohérentes. Il en va de même en cas d’échec dans un programme. Le SGBD doit pouvoir revenir à un état cohérent, ce qui est rendu possible par la gestion des transactions. Exercice Manipulation possible par des non informaticiens 27 28 Indiquer quel est l’utilisateur qui doit exécuter les fonctions suivantes pour un système de paie d’une grande entreprise : Un programmeur, un administrateur ou l’utilisateur final? Le SGBD doit permettre d’obtenir les données par des langages non procéduraux. On doit pouvoir décrire ce que l’on souhaite sans décrire comment l’obtenir. a) Écrire un programme d’application pour générer et imprimer les chèques. b) Changer dans la base de données l’adresse d’un employé qui a déménagé. c) Créer un nouveau compte d’utilisateur pour un employé nouvellement embauché. Exercice 29 Solution de l’exercice N°2 Étant donné le modèle de données suivant d’un jardin, doit-il être hiérarchique, réseau ou relationnel ? 30 Ce doit être un modèle hiérarchique car il a un aspect arborescent. Pour déterminer la quantité de lumière nécessaire, il faut accéder d’abord au nom de la plante, puis aux conditions. Nom de la plante Il Instructions de la plantation Luminosité nécessaire serait difficile d’accéder aux instructions de plantation uniquement pour les plantes qui doivent être plantées en plein soleil. Conditions Coût Mais le modèle relationnel est aussi possible. La navigation se fait par valeur. Conditions de sol 5 Chap. 2 Modèle Relationnel Conclusion 31 32 Utilité de l’outil informatique dans l’entreprise (Automatiser une partie/tout le SI); Avantages apportés par l’utilisation des BDs ; Modèle relationnel est le plus utilisé. Plan Introduction Concepts de base relationnelle Règle de passage du MCD au MLD Algèbre Motivation d’un MLD 33 Origine du modèle relationnel 34 Un Modèle Conceptuel de Données n’est pas directement implantable car indépendant de tout choix technologique; Traduction nécessaire vers un Modèle Logique de Données: Suit une approche spécifique de modélisation (hiérarchique, objet, relationnelle, …) Utilise un langage formel de spécification de la structure logique des données. Base de données relationnelle 35 Exemple de tables 36 Une BD relationnelle est composée d’un ensemble de tables (ou relations) Une table possédant un nom et est composée de : Lignes qu’on appelle enregistrements (ou tuples) Colonnes représentant chacune un champ (ou un attribut) Proposé par Codd (Thèse de doctorat) en 1970 Application de la théorie des relations à la gestion de fichiers informatiques Le modèle relationnel fournit un support mathématique cohérent à la manipulation de données : l’algèbre relationnelle Modèle dominant dans les offres commerciales de SGBD Oracle, DB2, Sybase, Ingres, Postgres, MySQL, Access… Nom de la table Commande N°Comman DateCommande Montant de Chaque champ a un nom et un type Texte, numérique, date, … Manipulation par des opérateurs de l’algèbre relationnelle Cohérence des données gérée par des règles et normes (Vérifiée surtout au niveau MCD) Dépendances fonctionnelles Théorie de la normalisation 3 champs 27 13/2/2007 120 65 12/1/2008 34 2 10/06/2006 27 14/12/2007 1500 34 4 enregistrements N°Commande est du type numérique entier Montant est du type numérique réel DateCommande est du type date 6 Relation & attribut Relation & attribut 37 38 Chaque colonne d’une table appartient à un ensemble de valeurs possibles appelé son domaine. Le domaine désigne toutes les valeurs permises qui peuvent apparaître dans la colonne. Un domaine se définit soit : en extension : couleur_voiture = {bleu, vert, marron, noir, gris, rouge} en compréhension : âge_enfants = {x dans N tel que x <15} Soit l’attribut Ai est une variable qui prend ses valeurs dans un domaine Di Soit U = {A1 , A2, ..., An} Une relation(table) r définie sur U est un sous-ensemble du produit cartésien D1 × D2 × … × Dn Soient deux ensembles P et Q, on appelle Produit cartésien de P et Q, l'ensemble noté P×Q , des couples (x, y) où x P et y Q. Exemple: Tuple & clé Professeurs={ Abbad, Saaidi, Moursi} Étudiants= { Issam, Hamidi} Étudiants ×Professeurs={ (Issam, Abbad) , (Issam,Saaidi) , (Issam, Moursi) , (Hamidi, Abbad) , (Hamidi, Saaidi) , (Hamidi, Moursi) } Relation & attribut 39 40 Soit u un tuple de la relation R, on note u.Ai la valeur de l'attribut Ai du tuple u. Chaque tuple est distinct : u, v r, i 1,2,..., n u.Ai v.Ai si alors uv Une clé de la relation R est un ensemble minimal d'attributs K tels que les valeurs de ces attributs permettent de distinguer tout tuple des autres K A1, A2,..., An u, v r , si u.K v.K alors uv Le schéma de relation représente la structure invariante d’une relation. Constitué du nom de la relation suivi de la liste des attributs et de leurs domaines associés PRODUIT (N°PRODUIT : entier, NOM : chaîne, QTE EN STOCK : entier>0) Les attributs constituant la clé de r sont écrits en premier dans le schéma de relation et sont généralement soulignés. Une BD relationnelle est un ensemble de relations r1, r2,..., rm La clé retenue est dite clé primaire Relation & attribut 41 Exemple de relations 42 Le degré d’une table est le nombre de ses attributs. : La table Produit (code produit, libellé du produit, prix unitaire, quantité en stock). Exemple Le Le degré de la table « Produit » est égale à 4. nombre total des enregistrements présents dans une table est appelé la cardinalité de la table. Propriétaires numero nom prenom naissance 5 13 76 Madani Azzouz Daoud Hossame Mohcine Ali 04-févr-80 15-mai-76 29-nov Véhiculés immatriculation 3452 A 9835 B 1234 A 9878 A 13 72 2 4 marque BMW Renault Peugeot BMW proprietaire 13 76 5 76 7 Règles pour une relation 43 La valeur NULL 44 Dans une relation, la valeur NULL représente des données manquantes, inconnues ou des données inapplicables. La valeur NULL correspond à une entrée non renseignée (non saisie, non introduite, non obligatoire). Attention : Deux tables d’une même BD ne peuvent avoir le même nom Deux colonnes d’une même table ne peuvent avoir le même nom Un même champ peut être présent dans plusieurs tables L’ordre des colonnes est sans aucune importance. Toutes les lignes ont le même format et le même nombre d’entrée. Chaque entrée dans chaque ligne doit être une valeur unique. L’ordre des lignes est sans importance car elles sont identifiées par leur contenu. Il n’y a pas deux lignes identiques dans toutes leurs entrées. La valeur NULL n’est pas égale à 0 (zéro) et ne représente aucune valeur particulière pour l’ordinateur. LES RÈGLES D'INTÉGRITÉ 45 INTÉGRITÉ DE DOMAINE 46 INTÉGRITÉ DE DOMAINE INTÉGRITÉ DE RELATION INTÉGRITÉ DE RÉFÉRENCE Contrôle des valeurs des attributs: par exemple, 4.000 ≤ salaire ≤ 20.000 liste de couleurs possibles [bleu, rouge, vert, jaune] Contrôle entre valeurs des attributs: QTE_STOCK ≥ INTÉGRITÉ DE DOMAINE QTE_COMMANDE Contrôle des opérateurs entre attributs Intégrité de la relation 48 Unicité de la clé primaire; Aucun des attributs de la clé primaire ne doit être NULL (du fait que la clé primaire identifie de manière unique les tuples d’une relation). 8 Clé étrangère Clés étrangères et Contrainte d’Intégrité Référentielle 50 La connaissance d’un numéro de commande nous permet de retrouver de façon unique un numéro de client. Dépendance fonctionnelle Clé étrangère (ou clé extérieure) permet au SGBDR de maintenir la cohérence des lignes de deux relations ou des lignes de la même relation. Définition : une clé étrangère est un attribut appartenant à une table qui existe aussi en tant que clé primaire dans une autre table. Une clé étrangère d’une table référence une clé primaire d’une autre table. Les valeurs de clé étrangère qui apparaissent dans une table doivent être les mêmes que celles de clé primaire dans la table connexe. Exemples dans le monde réel : Il existe donc une dépendance fonctionnelle entre deux tables (commande et client). Pour matérialiser ce lien, une colonne (numcli) sera ajoutée à la table source de la dépendance fonctionnelle. Celle-ci sera appelée clé étrangère. n 1 Attribut commun aux deux tables matérialisant la dépendance fonctionnelle. Une commande doit provenir d’un client. Clés étrangère n Clés étrangère 1 Ce qui est nécessaire : 2 tables (ou requêtes ou une combinaison des deux) 1 champ en commun dans chacune des tables même type de champ (texte avec texte, numérique avec numérique ...) même longueur (pas un champ long de 15 caractères avec un autre long de 20 caractères) même genre d'information (Ex.: Code d'inventaire avec des codes d'inventaires, CNE avec CNE ...) Attribut commun aux deux tables matérialisant la dépendance fonctionnelle. CLIENT ( NumCli, Nomcli, Adresseruecli, AdresseCPcli, Adressevillecli) COMMANDE ( NumCom, datecom, # NumCli ) Si la relation comprend un attribut clé étrangère, celui-ci sera placé à la fin et précédé du signe #. 10 8 Clés étrangères et CIR 53 Clés étrangères et CIR 54 Un SGBDR doit appliquer les règles suivantes afin d’assurer les CIR : Ajout : lors de l’ajout d’une ligne contenant une valeur de clé étrangère, le SGBD vérifie que cette valeur existe aussi comme valeur de clé primaire dans la table connexe. Suppression : lors de la suppression d’une ligne, le SGBD vérifie qu’aucune clé étrangère dans les tables connexes ne possède la même valeur que la clé primaire de la ligne supprimée. Mise à jour (ou modification) : lors de la mise à jour d’une valeur de clé primaire, le SGBD vérifie qu’aucune clé étrangère dans les tables connexes n’a la même valeur. Dans le premier cas, le SGBD rejettera l’ajout d’une ligne contenant une valeur clé étrangère inconnue. Dans les deux derniers cas, un concepteur de BD conserve habituellement un certain contrôle sur la façon dont l’application référentielle est appliquée. Lorsqu’une ligne contenant une clé primaire est supprimée, le concepteur peut spécifier au SGBD de supprimer toutes les lignes des autres tables ayant des clés correspondantes ou de régler toutes les clés étrangères correspondantes à NULL. Un choix similaire est possible lors de la modification d’une valeur de clé primaire. 9 Les relations entre tables Les relations entre tables 55 56 Relation « un à un » : une ligne d’une table A est en relation avec une seule ligne d’une autre table B. Relation « un à plusieurs » : une ligne d’une table A peut être en relation avec plusieurs lignes d’une autre table B. Dans une BD, certaines tables sont en relation les unes avec les autres. Dans les BD relationnelles, les relations entre tables sont généralement établies par l’intermédiaire de clés primaires et étrangères. On répartie les données dans des tables que l’on relie pour réduire la redondance des données. Les relations entre tables Les relations entre tables 57 58 Exemples Un de relations entre tables : IDREC NOM 1 Benali Wail 2 Touimi Rahma recteur dirige une université : Relation de type un à un. Un propriétaire possède plusieurs voitures, une voiture appartient à un seul propriétaire : PRENOM Table « Recteur » Relation de type un à plusieurs. IDUNIV NOM VILLE IDREC 100 Mohamed V Rabat 1 200 Hassan II Mohamédia 2 Table « Université » Les relations entre tables 59 IDPROP NOM PRENOM 1 Razi Mohamed 2 Nafia Mostafa 3 Adili Said Exercice 60 Considérons la table Département et les lignes montrées à la suite. Expliquez si ces les lignes peuvent être ou non insérées dans cette table. Propriétaire N° département MATRICULE MODELE IDPROP 1234 ا1 Peugeot 205 1 7588 ا48 Renault 19 1 25254 هـ72 Hyundai Acc 2 25482 ب6 Toyota Cor 3 NOM LIEU Effectif 20 Ventes Rue FAR 10 10 Marketing Rue Fès 5 10 R&D Bd Oqba 15 Voiture 15 Informatique Anfa 13 Comptabilité Agdal 23 10 Normalisation Solution de l’exercice 61 10 R&D 15 Bd Oqba 15 Informatique Anfa 13 Comptabilité Agdal 23 62 Lors de la conception d’une BD, plusieurs possibilités de modélisation existent; La qualité d’un modèle dépend de plusieurs facteurs Non : cette ligne ne peut pas être insérée, car elle quantité d'information à stocker facilité d'expression des requêtes prévention d'erreur de mise à jour et d'incohérences viole le principe d’unicité de la clé primaire (la valeur 10 existe déjà dans la table). Non : cette ligne ne peut pas être insérée, car elle viole la contrainte d’intégrité de la clé primaire (la clé primaire ne peut pas être NULL). La normalisation d’une BDD implique le respect de critères de protection de l’intégrité des données Plusieurs formes normales(6) existent dont Oui : cette ligne peut être insérée sans problème, car 1ère, 2ème, 3ème sont largement suffisantes aucune contrainte n’est violée. La normalisation : exemple 63 Une entreprise de vente de bateaux qui souhaite constituer un SI relatif à son activité Comment normaliser ? 64 Achats(IdClient, NomClient, PrénomClient, AdresseClient, ImmatriculationBateau, ModèleBateau, LongueurBateau, DateAchat, MontantAchat) Décomposition des relations jusqu’à ce que toutes respectent les 3 Formes Normales suivantes : Première Forme Problèmes potentiels redondance de données : incohérence suite à une Mise à jour : relation est dite en Première Forme Normale si tous ses attributs sont atomiques ; une personne qui achète plusieurs bateaux une personne qui change d’adresse anomalies lors d’insertion/suppression : Normale Une aucun des attributs ne doit être une relation entre « sousattributs » pas de colonnes dans une colonne un client potentiel ne peut être enregistré dans la BD s’il n’a pas encore acheté de bateau lorsqu’un client vend son bateau, il est supprimé du système d’information Dépendance Fonctionnelle 65 Seconde Forme Normale 66 Définition Deux groupes d’attributs X et Y de la relation R sont en Dépendance Fonctionnelle si dans R, chaque valeur de X détermine une et une seule valeur de Y si je connais la valeur de X alors je connais la valeur de Y une DF est une assertion sur toutes les extensions possibles d’une relation et pas uniquement sur ses n-uplets actuels Condition Première Forme Normale tous les attributs n’appartenant pas à la clé sont en dépendance fonctionnelle élémentaire avec la clé dès qu’un attribut non-clé dépend d’une partie de la clé, la relation n’est pas en 2FN. Exemple : ensemble de DF pour une table 11 Seconde Forme Normale Troisième Forme Normale 67 68 Exemple Prêt_livre (numcli, numlivre, nomcli, adrcli, titre_livre, dateprêt) DF numcli nomcli, adrcli num_livre titre_livre numcli, numlivre dateprêt Seconde Forme Normale (2FN) Aucun de ses attributs non-clés ne dépend d’un autre attribut non-clé ; il n ’existe pas de DF entre 2 attributs non-clés Prêt_livre n ’est pas en 2 FN car nomcli dépend uniquement de numcli et pas de numcli et de numlivre … décomposition à l’aide des DF client (numcli, nomcli,adrcli) Livre (numklivre, titre_livre) prêt (numcli, numlivre, dateprêt) Créer une base de données Troisième Forme Normale • Possibilité de créer avec SQL, mais pas indispensable 69 • Base de données = ensemble de tables Exemple ville(num_ville, nom_ville, num_dept, nom_dept) DF num_ville nom_ville, num_dept, nom_dept num_dept nom_dept ville n’est pas en 3 FN car décomposition à l’aide des DF • Donner un nom à la Base de données extension .ACCdb nom_dept dépend de num_dept ville(num_ville, nom_ville, num_dept) dept(num_dept, nom_dept) Objets d’une base de données Objets d’une base de données 71 72 • Tables, Requêtes • Formulaires, États • Tables, entités/relations • Tables, entités/relations • Macros, automatiser des actions à exécuter • Requêtes, interrogations • Formulaires, visualisation de tous les attributs/Saisie • États, documents à publier • Requêtes, interrogations • Formulaires, visualisation de tous les attributs • États, documents à publier 12 Créer une table – création 73 Créer une table – création 74 Contrainte de clé primaire : Une fois que la clé primaire sera désignée et le champ déclaré sans doublons, les saisies de valeurs identiques seront impossibles. Lors de la création d’une table, la clé primaire et l’appartenance des données à un type doivent être obligatoirement définies. D’autres éléments peuvent être posés pour assurer le confort et la sécurité de la saisie. Choix du type : Il s’agit de préciser quel type de valeurs sera stocké dans le champ. Il existe trois grands types (numérique, date, texte). Pour les champs de type texte, il est possible d’intervenir sur la taille du champ, de manière à optimiser la place occupée sur le support de stockage. Contrôle de la saisie : Pour faciliter la saisie, un masque, une valeur par défaut, peuvent être mis en place. Contrainte de domaine : Il est possible, pour un champ donné, de limiter les valeurs autorisées à la saisie. Celles-ci sont indiquées dans la case « Valide si », un message d’erreur peut y être associé. Les types d’attributs 75 Les types d’attributs 76 1. 2. 3. 4. 5. 6. 7. 8. 9. Les masques de saisie 77 Texte max : 255 caractères Mémo max : 65 535 caractères Numérique entier, simple précision, nombre décimale, etc. Date/Heure année 1009999, 12 ou 24 h Monétaire nombres réels de 1 à 4 décimales NuméroAuto incrémentation automatique Oui/Non Vrai/Faux , Actif/inactif Objet OLE (Object Linking and Embedding) feuilles de calcul, sons, vidéos, graphiques… Lien Hypertexte chemin complet (local ou internet) Les masques de saisie 78 13 Les masques de saisie Les masques de saisie 79 Symbole Signification 0 Chiffre de 0 à 9 obligatoire masque sens exemples 9 Chiffre ou espace facultatif 00 00 00 00 00 Numéro de téléphone 06 12 34 56 78 # Chiffre ou espace ou + ou - 00 999 Deux chiffres obligatoires L Lettre de A à Z obligatoire Nom de maximum 20 caractères, converti en capitales ALI SAID ou BORD Prénom de maximum 20 caractères, 1re lettre capitale, lettres suivantes bas de casse Ali ou Said 75 123 ou 59 ? Lettre de A à Z non obligatoire A Lettre ou chiffre obligatoire a Lettre ou chiffre non obligatoire >CCCCCCCCCCCCCCC CCCCC >C<CCCCCCCCCCCCC CCCCCC & Caractère quelconque obligatoire 0 00 00 00 0000 Code sur précisément 11 chiffres 1 53 06 07 0557 C Caractère quelconque non obligatoire 00/00/0099 < Passe tout en minuscules (bas de casse) Date avec année possible sur 2 ou 4 chiffres 07/06/1953 ou 22/02/53 (000) 000-0000 > Passe tout en majuscules (haut de casse) Téléphone avec indice régional obligatoire ! Saisie à partir de la droite Les masques de saisie masque sens exemples (999) 000-0000! Téléphone avec indice régional facultatif – remplissage par la droite (207)555-0199 ou ()3452025 (000) AAA-AAAA Téléphone américain (derniers chiffres peuvent être des lettres) #999 Nombre positif ou négatif, composé de quatre caractères ou moins, sans séparateur de milliers ni séparateur décimal. >L????L?000L0 Combinaison de lettres obligatoires (L), lettres facultatives (?) et nombres obligatoires (0). Le signe « > » oblige les utilisateurs à entrer toutes les lettres en capitales. (000) 000-0000 Téléphone avec indice régional obligatoire (207)555-0199 ou (165)345-2025 Les listes déroulantes 82 (206) 555-TELE 2345 ou -20 GREENGR339M3 ou MAY R462B7 Monsieur, M., Mr, Dr, Me, Madame, Mme, Mlle, … (207)555-0199 ou (165)345-2025 Les listes déroulantes 83 Relation entre tables 84 Dans la table, créer le champ Titre Cliquer sur ‘Assistant Liste de choix’ Sélectionner "Je taperai les valeurs souhaitées" Dresser la liste dans Contenu GVdK = CUY 14 Les filtres Normalisation: Inconvénients 85 86 Les filtres permettent de limiter simplement, de façon temporaire, les enregistrements affichés dans une table. Les inconvénients de la normalisation sont : des temps d'accès potentiellement plus longs si les requêtes sont trop complexes (lectures plus lente) une plus grande fragilité des données étant donné la non redondance (lecture impossible) un manque de flexibilité au niveau de l'utilisation de l'espace disque On peut filtrer selon deux méthodes : - le filtre sur un seul critère : ex : les étudiants qui habitent Kénitra; - le filtre sur plusieurs critères : ex : les étudiants qui habitent Kénitra ou ceux qui s’appellent « Mohamed ». Algèbre relationnelle 87 Algèbre relationnelle 88 Définition: Collection d’opérations formelles qui agissent sur des relations et produisent une relation en résultat. Dans la plupart des systèmes relationnels, la réponse à une requête s’obtient par l’utilisation d’un ou plusieurs opérateurs relationnels. Deux types d’opérateurs: Opérateurs unaires : Opérateurs binaires : Opérateurs relationnels unaires Sélection (filtre / critères) Projection (choix de colonnes) Différence (suppression de lignes) Soit Combiner : Union, Intersection, Différence, Produits, Jointures, … une expression dans une algèbre dite algèbre relationnelle, i.e., des opérations sur des tables Soit Filtrer : Sélection, Projection, Complément 89 Motivation : Formuler des requêtes pour interroger des données Qu’est-ce qu’une requête ? Produit cartésien (toutes les possibilités) Jointure (complément d'information) Union (union d'ensembles de lignes)… Projection 90 Projection Seule une partie des attributs est considérée La projection d’une relation R sur un ensemble d’attributs (A1,…, Ak) se note : R A1,...,Ak Sélection Seule une partie des tuples est considérée Complément (noté –R) Tous les tuples possibles n’existant pas dans une relation sont construits 15 Sélection 91 La sélection sur une relation R selon une condition C se note: C (R) Sélection 92 Opérateurs de comparaison : , <, >,, ≤, BETWEEN (entre), IN (dans), LIKE(comme) =, Combinaison de plusieurs conditions reliées par des opérateurs logiques : and, Opérateurs relationnels binaires 93 or, not Valeurs NULL Union 94 Union Différence Produit cartésien Intersection (notée R R' ) Thêta-produit (noté R C R' ) L’union de deux relations R et R’ se note : R R' La fusion de deux relations est considérée Les tuples d’une relation qui n’existent pas dans une seconde relation sont considérés Toutes les combinaisons entre tuples de 2 relations sont considérées Seuls les tuples présents dans 2 relations sont considérés Produit cartésien suivi d’une sélection (Appelé également jointure) Différence 95 Produit cartésien 96 La différence de deux relations R et R’ se note : R R' Le produit cartésien de deux relations R et R’ se note : R R' 16 jointures Jointure naturelle 97 98 La jointure naturelle de deux relations R et R’ selon une condition C se note : Jointure naturelle Thêta-produit dont la condition est une égalité de valeurs entre des attributs de deux relations R C R' Jointure extérieure Jointure naturelle à laquelle on ajoute les tuples de chaque relation n’ayant pas trouvé de correspondance dans l’autre Semi-jointure Jointure naturelle à laquelle on ajoute les tuples de la première relation n’ayant pas trouvé de correspondance dans l’autre La jointure naturelle est l’opérateur le plus utilisé avec les sélections et projections, car le plus intéressant Combinaison d’opérateurs Exercice Algèbre relationnelle 99 100 La puissance de l'algèbre relationnelle s'exprime dans la combinaison d’opérateurs permettant d'exprimer des requêtes précises. Nom_imb Nomgérant Nom Alami 54 Architecte Atif 39 Médecin Barhou m 58 Avocat Dahbi 65 Retraité 22 Rue Azrou, Rabat 10 2009 Alami Firdaous 15 bd des Far, Fès 5 2000 Dahbi (vehicule nom"Ali " ( proprietaire))) proprietairenumero immatriculation Table Personne Ag Profession e Anné e Annakhil Ex: on chercher l’immatriculation des véhicules possédés par la personne dont le nom est « Ali » ( Table Immeuble Adresse Nb étage Nom_imb No_app Superficie Etag e Annakhil 1 150 1 Nom_imb No_app Nom_occ Année_h Annakhil 2 100 1 Annakhil 1 Alami 2009 Annakhil 10 90 9 Annakhil 2 Dahbi 2009 Firdaous 5 120 2 Firdaous 5 Atif 2001 Firdaous 10 100 3 Firdaous 10 Barhoum 2005 Firdaous 11 80 3 Table Appartement Table Occupant Exercice Exercice 101 102 Ecrire en algèbre relationnelle les requêtes: Soit la base de données simplifiée. Exprimer la signification et le résultat des requêtes suivantes: 1- La requête: Personne 2- Πnom-Imb(Immeuble); 3- Πnom-Imb, No_app(σsuperficie>100(Appartement)); 7. Dans quel immeuble habite un retraité? 4- Πnom-occ(σNom_imb=« Firdaous » ET Année>2001)(Occupant); 8. Qui habite un appartement de moins de 70 m 2? 5- Πnom-Imb, No_app(σNo_app=Etage (Appartement)); 9. Nom des personnes qui habitent au dernier étage de leur immeuble. 6-Πnomgérant, Superficie 10. Profession du gérant du Firdaous? 7-Πnom-occ, Année,Superficie ( Appartement Nom_ imb Nom_ imb No _ app No _ appOccupant ) 11. Age et profession des occupants de l’immeuble géré par Alami? 12. Qui habite, dans un immeuble de plus de 10 étages, un appartement de plus de 100 m2? n o m,Age (Im meuble Nom_ imb Nom_ imb Appartement ) 1- Nom des immeubles ayant strictement plus de 10 étages. 2. Nom des personnes ayant emménagé avant 1994. 3. Qui habite le Annakhil? 4. Nom des Architectes de plus de 25 ans. 5. Nom des immeubles ayant un appartement de plus de 150 m 2. 6. Qui gère l’appartement où habite Atif? 17 103 Sommaire du chapitre 3 104 Présentation du langage SQL; de données; Requêtes d’action; Requêtes de sélection; Requêtes de contrôle. Types CHAPITRE 3 : INTRODUCTION À SQL ENCG Kénitra**Gestion des bases de données Présentation du langage SQL 105 + Présentation du langage SQL 106 Le sigle SQL signifie "Structured Query Language", soit en français SQL a été normalisé par l'ANSI (American National Standards + Institute) et par l'ISO (International Organization for "Langage de requêtes structuré". + Il a été développé par IBM au cours des années 70 et son nom était SEQUEL, abréviation de Structured English QUEry Language + + SQL : langage proche de l’utilisateur et de sa façon de poser les requêtes (formulation proche du langage naturel) SQL : langage assertionnel (non procédural), facile à apprendre pour rédiger les requêtes, les lire et les comprendre. ENCG Kénitra**Gestion des bases de données Standardization). + Cette normalisation a donné naissance à une 1ère version (SQL1) en 1987 puis à une 2ème version (SQL2) en 1992 Une troisième norme (SQL3) est sortie en 2003 par l'ANSI et l'ISO. + 12-déc.-14 ENCG Kénitra**Gestion des bases de données 107 108 Malgré la normalisation ISO, il existe plusieurs variantes de SQL sur le marché car chaque éditeur de SGBDR tente d’étendre le standard pour accroître l’attrait commercial de son produit + Chaque requête SQL doit obligatoirement se terminer par un Point Virgule (SGBD ACCESS) + 12-déc.-14 Présentation du langage SQL Présentation du langage SQL + 12-déc.-14 Utilisation de SQL : En mode interactif : l’utilisateur écrit textuellement une commande SQL et récupère le résultat immédiatement. En mode intégré : une commande SQL est mélangée avec les instructions d’un programme en langage de haut niveau tel que C, C++, Visual basic, Pascal... On distingue 3 types de requêtes: Actions (Création et m.à.j), Sélections (Interrogation) et Contrôle (définir des permissions pour les utilisateurs). ENCG Kénitra**Gestion des bases de données 12-déc.-14 ENCG Kénitra**Gestion des bases de données 12-déc.-14 18 Types de données Types de données 109 110 INTEGER ou INT : entiers signés. TEXT(p) ou CHAR(p) : chaîne de caractères de longueur fixe de p caractères. Quelques propriétés des attributs en SQL : PRIMARY KEY : clé primaire. FOREIGN KEY : clé étrangère. NULL / NOT NULL : valeurs non obligatoires/obligatoires. DEFAULT = Valeur : pour définir une valeur par défaut (ne fonctionne pas sous Access). CHECK (Condition): pour contrôler la validité des valeurs (ne fonctionne pas sous Access). UNIQUE : permet de vérifier que la valeur saisie pour un champ n'existe pas déjà dans la table. CONSTRAINT : permet de donner un nom à une contrainte ce qui permet de la manipuler (Exemple effacement). VARCHAR (p): chaîne de caractères de longueur variable de p caractères maximum. DATE, TIME, DATETIME: dates et/ou heures. LOGICAL : valeur logique « oui » ou « non ». DECIMAL(p, q) : nombres décimaux de p chiffres dont q après le point décimal (par défaut, q = 0). FLOAT : nombre réel en virgule flottante(il y a aussi Single pour simple et Double pour réel double). CURRENCY, MONEY : Monétaire. COUNTER : Compteur (NuméroAuto). ENCG Kénitra**Gestion des bases de données 12-déc.-14 ENCG Kénitra**Gestion des bases de données 12-déc.-14 Requêtes d’action Conventions 111 112 Les conventions relatives aux noms des tables et des champs varient quelque peu d'un SGBD à l'autre: Le nombre de caractères ne doit pas être trop grand (64 dans Access, 18 à 30 dans d'autres SGBD) ; Seuls les lettres, les chiffres et le caractère de soulignement sont autorisés. Access admet les caractères accentués. Il admet aussi l'espace, mais le nom du champ doit être écrit entre crochets ; Certains SGBD requièrent que le nom d'un champ commence par une lettre, mais ce n'est pas le cas d'Access ; Les termes faisant partie du vocabulaire du langage SQL sont interdits ("date » par exemple). Ce sont les mots réservés. ENCG Kénitra**Gestion des bases de données ò Requêtes d’actions: Sont des requêtes qui permettent de créer des tables, d’ajouter, de supprimer des enregistrements d’une table, d’ajouter une colonne… Création d’une table : Cette opération donne comme résultat une table vide ne contenant aucun enregistrement. Il faut préciser : Le La nom de la table; description de ses colonnes : nom, type de données et contraintes. L’instruction CREATE TABLE permet de créer une nouvelle table. 12-déc.-14 ENCG Kénitra**Gestion des bases de données Création de table 12-déc.-14 Création de table: Exemples 113 114 Syntaxe SQL pour créer une nouvelle table avec une clé primaire : CREATE TABLE Nom_table (champ1 type [CONSTRAINT nom_contrainte] PRIMARY KEY, champ2 type [NOT NULL], …, champN type [NOT NULL]); Syntaxe SQL pour créer une nouvelle table avec une clé primaire composée : CREATE TABLE Nom_table (champ1 type [NOT NULL], champ2 type [NOT NULL], …, champN type, [CONSTRAINT nom_contrainte] PRIMARY KEY (champ1, champ2,…) ); ENCG Kénitra**Gestion des bases de données 12-déc.-14 CREATE TABLE Client (email VARCHAR (50) NOT NULL, nom VARCHAR (20) NOT NULL, prenom VARCHAR (20), motDePasse VARCHAR (60) NOT NULL, anneeNaiss DECIMAL (4)) CREATE TABLE Cinéma (nom VARCHAR (50) NOT NULL, adresse VARCHAR (50) DEFAULT ’Inconnue’) CREATE TABLE ETUDIANT(ID INTEGER CONSTRAINT Contrainte_ID PRIMARY KEY, NOM TEXT(15), PRENOM TEXT(15), AGE INTEGER CONSTRAINT Contrainte_AGE CHECK(age< 35), BOURSIER LOGICAL); ENCG Kénitra**Gestion des bases de données 12-déc.-14 19 Création de table: Exemples 115 Création d’une table 116 Donnez une requête SQL permettant de créer la table EMPLOYES ( Nemployé: entier, Nom: texte(25), Prénom: texte(20), Fonction: texte(15), Adresse: texte(50), Codeville: entier long) CREATE TABLE EMPLOYES ( Nemployé INTEGER CONSTRAINT nom_index PRIMARY KEY, Nom TEXT(25), Prénom TEXT(20), Fonction TEXT(15), Adresse TEXT(50), Codeville LONG ) ; ENCG Kénitra**Gestion des bases de données Créez une requête SQL permettant de créer la table DETAILS ( Ncommande: entier, Réf: texte(30), Pu: monétaire non nul, Quantité: entier non nul, Remise: réel simple ); CREATE TABLE DETAILS ( Ncommande Integer, Réf Char(30), Pu Currency NOT NULL, Quantité Integer NOT NULL, Remise Single, CONSTRAINT nom_index PRIMARY KEY ( Ncommande, Réf ) ) ; 12-déc.-14 ENCG Kénitra**Gestion des bases de données Création d’une table: Clé étrangère 117 12-déc.-14 Modification dans une table 118 Créez une requête SQL permettant de créer la table Participe( Numadh: entier, Numact: entier, AnneeParticipe: entier); Numadh est la clé primaire de la table Adherent et Numact est la clé primaire de la table Activite. CREATE TABLE Participe( Numadh integer, Numact integer, anneeParticipe integer, Primary key (Numadh, Numact, AnneeParticipe), ò ò L’instruction ALTER TABLE permet d’ajouter ou de supprimer un seul champ à une table. Elle permet aussi la création et la suppression des liens entre les tables d’une base de données. Foreign key (Numadh) references Adherent(Numadh), Foreign Key (Numact) references Activite(Numact)); ENCG Kénitra**Gestion des bases de données 12-déc.-14 ENCG Kénitra**Gestion des bases de données Modification dans une table 119 12-déc.-14 Modification dans une table 120 Modification de la structure d’une table : Il y a trois types d’actions concernant la modification de structure d’une table : Ajouter une ou plusieurs colonnes. une ou plusieurs colonnes. Modifier les propriétés d’une ou de plusieurs colonnes. Supprimer Les commandes SQL relatives à ces actions : ADD DROP MODIFY ENCG Kénitra**Gestion des bases de données 12-déc.-14 Modification de la structure d’une table : Pour modifier la structure d’une table, on précise tout d’abord le nom de celle-ci en écrivant la commande suivante : ALTER TABLE Nom_table En suite, on spécifie la nature de l’action de modification (ajout, suppression ou modification). Pour cela, il faut utiliser l’une des trois commandes : ADD, DROP ou MODIFY. ENCG Kénitra**Gestion des bases de données 12-déc.-14 20 Modification dans une table 121 Modification dans une table 122 Exemples d’ajout : Ajouter une colonne « AGE » (entier) dans la table « CLIENT » : ALTER TABLE CLIENT ADD AGE INTEGER; Ou ALTER TABLE CLIENT ADD Column AGE INTEGER; Ajouter une colonne « DATECOMPTE »(date) dans la table « COMPTE » : ALTER TABLE COMPTE ADD DATECOMPTE DATE; ENCG Kénitra**Gestion des bases de données Supprimer la colonne « DATECOMPTE » de la table « COMPTE » : ALTER TABLE COMPTE DROP DATECOMPTE; 12-déc.-14 Modification dans une table 123 Exemples de suppression : Supprimer la colonne « ADRAGENCE » de la table « AGENCE » : ALTER TABLE CLIENT DROP ADRAGENCE; ENCG Kénitra**Gestion des bases de données 12-déc.-14 Modification dans une table 124 Exemples de modification de propriétés : le type de la colonne « ADRAGENCE » pour qu’il soit un CHAR(100) au lieu de CHAR(40) : Créez une requête SQL permettant de créer la table VILLES ( Codeville: NuméroAuto, Ville: texte(20)) CREATE TABLE VILLES ( Codeville Counter CONSTRAINT nom_index PRIMARY KEY, Ville Text(20) ) ; Modifiez la table EMPLOYES en déclarant le champ "CodeVille" comme clé étrangère, puis créez un lien nommé lien_ville sur le champ CodeVille, en précisant que le côté 1 du lien est le champ CodeVille de la table VILLES Modifier ALTER TABLE AGENCE MODIFY ADRAGENCE CHAR(100); Modifier la colonne « AGE » de la table « CLIENT » pour qu’elle soit obligatoire : ALTER TABLE CLEINT MODIFY AGE NOT NULL; ENCG Kénitra**Gestion des bases de données 12-déc.-14 ENCG Kénitra**Gestion des bases de données Suppression d’une table Modification dans une table 125 12-déc.-14 126 ALTER TABLE EMPLOYES ADD CONSTRAINT Lien_ville FOREIGN KEY (CodeVille) REFERENCES VILLES (CodeVille); Supprimer le lien nommé lien_ville existant entre la table EMPLOYES et la table VILLES selon le champ Codeville ALTER TABLE EMPLOYES DROP CONSTRAINT Lien_ville; ENCG Kénitra**Gestion des bases de données 12-déc.-14 Suppression d’une table : La syntaxe SQL pour supprimer une table est très simple : DROP TABLE Nom_table Exemples : DROP TABLE CLIENT DROP TABLE AGENCE DROP TABLE COMPTE ENCG Kénitra**Gestion des bases de données 12-déc.-14 21 Insertion d’enregistrements Suppression d’une table 127 128 La clause CASCADE CONSTRAINS pour supprimer toutes les contraintes d’intégrités référentielles qui réfèrent à la relation supprimée. Exemple : DROP TABLE COMPTE CASCADE CONSTRAINS ; Insertion d’enregistrements dans une table : La commande qui permet d’insérer une ligne dans une table est la suivante : INSERT INTO Nom_table [(champ1, champ2, …)] VALUES (valeur1, valeur2, …) ; Dans la clause “INTO…”, on spécifie le nom de la table ainsi que les noms des colonnes. Si l’on veut ajouter une ligne contenant les valeurs pour tous les champs, dans ce cas on peut omettre les noms de colonnes. ENCG Kénitra**Gestion des bases de données 12-déc.-14 ENCG Kénitra**Gestion des bases de données Insertion d’enregistrements 129 12-déc.-14 Modification d’un enregistrement 130 Exemple: L’instruction UPDATE Créez une requête permettant d’ajouter l’enregistrement suivant dans la table EMPLOYES: (100, BEN AZOUZ, Aziz, Ingénieur, 90050) On modifie la valeur d’un champ d’un enregistrement qui vérifie une condition précise (critère pour accéder à la ligne qui sera le sujet de la modification); La table EMPLOYES ( Nemployé , Nom, Prénom, Fonction, Adresse, Codeville) INSERT INTO EMPLOYES (Nemployé, Nom, Prénom, Fonction, Codeville) permet la mise à jour d’une table; VALUES (100, "BEN AZOUZ", "Aziz", "Ingénieur", 90050) ; Syntaxe : UPDATE table SET nouvelles valeurs WHERE critères ENCG Kénitra**Gestion des bases de données 12-déc.-14 ENCG Kénitra**Gestion des bases de données Modification d’un enregistrement 131 12-déc.-14 Modification d’un enregistrement 132 Les champs à mettre à jour doivent être écrits dans la clause SET, l’un après l’autre (avec leurs valeurs) et séparés par des virgules. Les champs non spécifiés après la clause SET ne seront pas modifiés. Si la clause WHERE est absente, tous les enregistrements de la table seront affectées. ENCG Kénitra**Gestion des bases de données 12-déc.-14 Exemples : Donner une requête SQL pour modifier l’adresse de l'employé numéro 10 par la nouvelle adresse sera "10 Avenue Mohamed VI, Rabat". UPDATE EMPLOYES SET Adresse = "10 Avenue Mohamed VI, Rabat" WHERE Nemployé = 10 ; Donner une requête SQL pour augmenter de 5% le salaire de tous les acteurs : ACTEURS (N_act, Nom, Prénom, Nationalité, Salaire, Age, Films) UPDATE ACTEURS SET Salaire = Salaire * 1.05 ; ENCG Kénitra**Gestion des bases de données 12-déc.-14 22 Suppression d’enregistrements 133 Suppression d’enregistrement 134 L’instruction DELETE permet d’effacer des enregistrements d’une table. Effacez La syntaxe de la commande est : Ou Pour supprimer tous les enregistrements d’une table, il suffit de ne pas indiquer la clause WHERE. ENCG Kénitra**Gestion des bases de données tous les enregistrements de la table ACTEURS; * FROM ACTEURS ; DELETE DELETE * FROM Nom_table WHERE Condition; Exemples : DELETE N_act FROM ACTEURS ; tous les acteurs de nationalité marocaine; DELETE * FROM ACTEURS WHERE Nationalité= "marocaine"; Effacez 12-déc.-14 ENCG Kénitra**Gestion des bases de données Les requêtes de sélection 12-déc.-14 Les requêtes de sélection 135 136 Une requête de type SELECTION permet d'interroger une base de données en composant les projections, les restrictions, les jointures…. Le résultat d’une telle requête est renvoyé sous forme d’une table formée d’un ou plusieurs attributs. Sa syntaxe est : SELECT [Prédicat] {* / table.* /[table.attribut1 As alias1], [table.attribut2 As alias2], …} FROM Liste de table [WHERE Critère de sélection] [GROUP BY Liste d’attributs] [HAVING Critère de sélection] [ORDER BY Critère d’ordre] ENCG Kénitra**Gestion des bases de données Prédicat: L’un des prédicats suivants: ALL, DISTINCT, DISTINCTROW, TOP ou PERCENT. Les prédicats permettent de limiter le nombre d’enregistrements renvoyés. ALL est choisi par défaut 12-déc.-14 ENCG Kénitra**Gestion des bases de données Les requêtes de sélection 12-déc.-14 Les requêtes de sélection 137 Interprétation: Projection : SELECT… FROM… Restriction ou Jointure : WHERE … Tri : ORDER BY … ASC / DESC [ ] signale une clause optionnelle, c.à.d on peut utiliser la requête SELECT sans cette clause 138 * : Indique que tous les champs des tables spécifiées seront sélectionnés Table: Nom des tables séparées par des virgules. attribut1, attribut2: Noms des champs à extraire. alias1, alias2: Utilisés pour renommer un attribut. Exemples Soit la table ACTEURS : ACTEURS (N_act, Nom, Prénom, Nationalité, Salaire, Age, Films) •Donner une requête SQL pour afficher tous les champs et tous les enregistrements de la table ACTEURS SELECT * FROM ACTEURS; •Requête SQL pour afficher uniquement le Nom, Prénom et le salaire de chaque Acteur SELECT Nom, Prénom, Salaire FROM ACTEURS; •Afficher les différentes nationalités sans doublons même si plusieurs acteurs ont la même nationalité SELECT DISTINCT Nationalité FROM ACTEURS; ENCG Kénitra**Gestion des bases de données 12-déc.-14 ENCG Kénitra**Gestion des bases de données 12-déc.-14 23 Les requêtes de sélection 139 Les requêtes de sélection 140 •Requête SQL afficher les 5 premiers acteurs de la table ACTEURS •Afficher le nombre totale d’enregistrements de la table ACTEURS SELECT TOP 5 * FROM ACTEURS; SELECT COUNT(*) FROM ACTEURS; •Requête SQL afficher 30% des acteurs de la table ACTEURS •Afficher le nombre totale d’enregistrements de la table ACTEURS et nommer le champ retourné Nombre total SELECT TOP 30 PERCENT * FROM ACTEURS; + Avec l’instruction SELECT, il est possible d’utiliser les fonctions suivantes pour effectuer des calculs: SUM () renvoie la somme d’un champ AVG () renvoie la moyenne d’un champ MAX () renvoie la valeur maximale d’un champ MIN () renvoie la valeur minimale d’un champ SELECT COUNT(*) As [Nombre Total] FROM ACTEURS; • Afficher le nombre d’enregistrements de la table ACTEURS qui ont une entrée dans le champ Nom. Nommer le champ retourné Nombre d’entrée SELECT COUNT(Nom) As [Nombre d’entrée] FROM ACTEURS; renvoie le nombre d’enregistrements de la table COUNT (*) ENCG Kénitra**Gestion des bases de données 12-déc.-14 ENCG Kénitra**Gestion des bases de données Les requêtes de sélection 141 12-déc.-14 Les requêtes de sélection 142 •Afficher le total des salaires de la table ACTEURS et nommer le champ retourné Total des salaires SELECT SUM(Salaire) As [Total des salaires] FROM ACTEURS; •Afficher la moyenne des salaires de la table ACTEURS et nommer le champ retourné Moyenne des salaires Les éléments de la clause WHERE è Ils permettent de définir la condition dans cette clause. La clause peut être accompagnée des opérateurs logiques AND, OR ou NOT Comparaison à une valeur (=, <, >, >=, <=, <>) SELECT AVG(Salaire) As [Moyenne des salaires] FROM ACTEURS; •Afficher le salaire le plus élevé de la table ACTEURS et nommer le champ retourné Salaire maximal Comparaison à une fourchette de valeurs (BETWEEN … AND) Comparaison à une liste de valeur ( IN (. , . ,...) ) Comparaison à un filtre (LIKE) SELECT MAX(Salaire) As [Salaire maximal] FROM ACTEURS; Test "tous" ou "au moins" (ALL, ANY/SOME) Test existentiel (EXISTS) ENCG Kénitra**Gestion des bases de données 12-déc.-14 ENCG Kénitra**Gestion des bases de données Les requêtes de sélection 143 12-déc.-14 Les requêtes de sélection 144 Les opérateurs de comparaison : On peut utiliser les opérateurs suivants dans les conditions d’une requête : = : égal <> : différent < : inférieur strict > : supérieur strict <= : inférieur ou égal >= : supérieur ou égal Comment écrire les valeurs constantes : Numériques Chaînes : 125, -654, -7.54, etc. de caractères et dates : entre « ' ». Les opérateurs logiques : Ils permettent de construire des conditions plus complexes. Les opérateurs logiques de SQL sont : AND (ET) : conjonction. (OU) : disjonction NOT (NON) : négation OR L'opérateur AND réunit deux ou plusieurs conditions et sélectionne un enregistrement seulement si cet enregistrement satisfait toutes les conditions listées. L'opérateur OR réunit deux conditions, mais sélectionne un enregistrement si une des conditions listées est satisfaite. 24 Les requêtes de sélection 145 Les requêtes de sélection 146 •Afficher tous les noms d’acteurs dont l’âge est supérieur ou égal à 25 •Afficher tous les acteurs dont la nationalité est américaine, marocaine ou égyptienne SELECT Nom FROM ACTEURS WHERE Age >=25; • Afficher tous les noms d’acteurs dont la nationalité est américaine et l’âge est supérieur à 25 SELECT * FROM ACTEURS WHERE Nationalité IN ("Américaine", "Egyptienne", "marocaine"); •Afficher tous les noms d’acteurs qui commencent par "D" SELECT Nom FROM ACTEURS WHERE Nom LIKE "D*"; SELECT Nom FROM ACTEURS WHERE (Nationalité="américaine") AND (Age > 25); • Afficher tous les noms d’acteurs dont la nationalité est américaine ou l’âge est supérieur à 25 •Afficher tous les noms d’acteur ne commençant pas par D et ayant 4 lettres SELECT Nom FROM ACTEURS WHERE Nom NOT LIKE "D*" AND Nom LIKE "????"; SELECT Nom FROM ACTEURS WHERE (Nationalité="américaine") OR (Age >25); •Afficher les acteurs dont l’âge est entre 35 et 50 •Afficher le nom d’acteur le plus âgé SELECT Nom FROM ACTEURS WHERE Age >= ALL (SELECT Age FROM ACTEURS ); SELECT * FROM ACTEURS WHERE Age BETWEEN 35 AND 50; ENCG Kénitra**Gestion des bases de données 12-déc.-14 ENCG Kénitra**Gestion des bases de données Les requêtes de sélection Les requêtes de sélection 147 148 • Afficher le nom d’acteur le plus jeune Les jointures SELECT Nom FROM ACTEURS WHERE Age <= ALL (SELECT Age FROM ACTEURS); 12-déc.-14 Jointure interne: utilise INNER JOIN. Ne sont incluses dans le résultat final que les lignes qui se correspondent dans les deux tables. Exemple 2 la jointure externe gauche, dans laquelle INNER JOIN est remplacé par LEFT JOIN. Toutes les lignes de la première table sont incluses dans le résultat de la requête, même s'il n'existe pas de ligne correspondante dans la seconde table ; Soit les tables PRODUITS, COMMANDES, DETAILS et EMPLOYES créées sous ACCESS: PRODUITS (Réf , Nompr, Nfournisseur, Pu) COMMANDES(Ncommande, Codecli, Nemployé, Date commande) la jointure externe droite, dans laquelle INNER JOIN est remplacé par RIGHT JOIN. Toutes les lignes de la seconde table sont incluses dans le résultat de la requête, même s'il n'existe pas de ligne correspondante dans la première table. DETAILS(Ncommande, Réf , Quantité, Remise) EMPLOYES(Nemployé, Nom, Prénom, Fonction, Adresse, Ville) ENCG Kénitra**Gestion des bases de données 12-déc.-14 ENCG Kénitra**Gestion des bases de données Les requêtes de sélection 149 12-déc.-14 Les requêtes de sélection 150 Les éléments de la clause FROM •Afficher tous les produits (Réf, Nompr) qui ont été vendus par ordre décroissant selon le champ Réf Afficher tous les produits (Réf, Nompr) qui ont été vendus SELECT PRODUITS.Réf, Nompr FROM PRODUITS INNER JOIN DETAILS WHERE PRODUITS. Réf = DETAILS . Réf ORDER BY PRODUITS.Réf DESC; (Ou bien ) SELECT PRODUITS.Réf, Nompr FROM PRODUITS, DETAILS + Par défaut, le résultat d’une requête sélection est trié selon l’ordre croissant du premier attribut qui figure dans la clause SELECT WHERE PRODUITS.Réf = DETAILS.Réf ; ENCG Kénitra**Gestion des bases de données SELECT PRODUITS. Réf, Nompr FROM PRODUITS , DETAILS ON PRODUITS.Réf = DETAILS.Réf ; 12-déc.-14 ENCG Kénitra**Gestion des bases de données 12-déc.-14 25 Les requêtes de sélection 151 Les requêtes de sélection 152 •En utilisant la table détails, afficher pour chaque commande le total de quantités des produits vendus SELECT Ncommande, SUM(Quantité) AS [Totale quantités vendues] •Afficher les commandes qui contiennent plus que 3 produits (>=3) SELECT Ncommande, COUNT(Réf) AS [Nombre de produits] FROM DETAILS GROUP BY Ncommande FROM DETAILS GROUP BY Ncommande ; HAVING COUNT(Réf) >=3 ; •Afficher pour chaque commande le total de quantités des produits vendus et tel que ce total est > 60 •Afficher pour chaque commande le total de quantités des produits vendus avec une remise de 6% et tel que ce totale est > 60 SELECT Ncommande, SUM(Quantité) AS [Totale quantités vendues] SELECT Ncommande, SUM(Quantité) AS [Totale quantités vendues] FROM DETAILS GROUP BY Ncommande FROM DETAILS WHERE Remise=0.06 GROUP BY Ncommande HAVING SUM(Quantité) > 60 ; HAVING SUM(Quantité) > 60 ; ENCG Kénitra**Gestion des bases de données 12-déc.-14 ENCG Kénitra**Gestion des bases de données Les requêtes de sélection 153 12-déc.-14 Exercice Algèbre relationnelle 154 • Afficher les employés qui ont vendu plus de 100 commandes Nom_imb Adresse Nb étage Année #Nomgérant Nom Age Profession SELECT Nemployé, COUNT(Ncommande) AS [Nombre de commandes vendues] Annakhil 22 Rue Azrou, Rabat 10 2009 Alami Alami 54 Architecte FROM COMMANDES Firdaous 15 bd des Far, Fès 5 2000 Dahbi Atif 39 Médecin Barhoum 58 Avocat Dahbi 65 Retraité GROUP BY Nemployé Table Immeuble HAVING COUNT(Ncommande) > 100 ; Table Personne •Afficher les employés de la ville d’Asilah qui ont vendu plus de 100 commandes SELECT EMPLOYES.Nemployé, COUNT(*) AS [Nombre de commandes vendues] FROM COMMANDES, EMPLOYES WHERE EMPLOYES.Nemployé=COMMANDES.Nemployé AND Ville ="Asilah" GROUP BY EMPLOYES.Nemployé HAVING COUNT(*) > 100 ; ENCG Kénitra**Gestion des bases de données #Nom_imb No_app Superficie Etage Annakhil 1 150 1 #Nom_imb #No_app #Nom_occ Année_h Annakhil 2 100 1 Annakhil 1 Alami 2009 Annakhil 10 90 9 Firdaous 5 120 2 Annakhil 2 Dahbi 2009 Firdaous 10 100 3 Firdaous 5 Atif 2001 Firdaous 11 80 3 Firdaous 10 Barhoum 2005 Table Appartement 12-déc.-14 ENCG Kénitra**Gestion des bases de données Table Occupant 12-déc.-14 Exercice Exercice 155 156 7. Qui gère l’appartement où habite Atif? Select Nomgérant from Immeuble Inner Joint Occupant On Immeuble.Nom_imb=Occupant.Nom_imb where nom_occ=‘’Atif’’; 8. Dans quel immeuble habite un retraité? Select Nom_imb from Personne, Occupant where Personne.Nom=Occupant.nom_occ and profession=’’Retraité’’; 3. Nom des personnes ayant emménagé avant 1994. 9. Qui habite un appartement de moins de 70 m2? Select Nom_occ from Occupant where Année_h<1994; 4. Qui habite Annakhil? Select Nom_occ from Appartement, Occupant where Appartement.Nom_imb=Occupant.Nom_imb and Appartement.No_app=Occupant.No_app and superficie<70; Select Nom_occ from Occupant Where Nom_imb=‘’ Annakhil’’; 10. Noms des personnes qui habitent au dernier étage de leur immeuble 5. Nom des Architectes de plus de 25 ans. Select Nom, age From Personne Where Profession=‘’Architecte’’ And Age>25; Select Nom_occ from occupant, appartement where occupant.Nom_imb=Appartement.Nom_imb and Occupant.No_app=Appartement.No_app and Etage=Nb_etage ; 6. Nom des immeubles ayant un appartement de plus de 150 m 2. 11. Profession du gérant du Firdaous? Select distinct Nom_imb from Appartement where Superficie>150; Select Profession from Personne INNER JOIN Immeuble ON Nomgérant=Nom WHERE Nom_imb=‘’firdaouss’’; Ecrire en langage SQL les requêtes: 1. Afficher les noms des immeubles; Select Nom_imb from immeuble; 2. Nom des immeubles ayant strictement plus de 10 étages. Select Nom-imb from immeuble where nb_étage>10; ENCG Kénitra**Gestion des bases de données 12-déc.-14 NomENCG Kénitra**Gestion des bases de données 12-déc.-14 26 Exercice 157 12. Age et profession des occupants de l’immeuble géré par Alami? SELECT Age, Profession FROM Personne, Occupant, Immeuble WHERE Personne.Nom=Occupant.Nom_occ and Occupant.Nom_imb=Immeuble.nom_imb and Nomgérant=‘’Alami’’; 13. Qui habite dans un immeuble de plus de 10 étages et un appartement de plus de 100 m 2? SELECT Nom_occ from Occupant, Immeuble, Appartement WHERE Occupant.Nom_imb=Appartement.Nom_imb AND Occupant.No_app=Appartement.No_app AND Immeuble.Nom_imb=Appartement.Nom_imb AND Nb_étage>10 AND Superficie=100; 14. Afficher la superficie totale de chaque Immeuble; SELECT SUM(Superfice) FROM Appartement GROUP BY Nom_imb; 15. Afficher le nombre d’appartement par étage pour chaque immeuble; SELECT Nom_imb, Etage, COUNT(*) AS [nombre d’appartement] FROM Appartement GROUP BY Nom_imb, Etage; 16. Le prix de location par mois et par mètre carré est de 40DH. Afficher la recette par mois; Select SUM(Superficie*40) from Appartement, Occupant Where Appartement.Nom_imb= Occupant.Nom_imb And Appartement.No_app=Occupant.No_app; ENCG Kénitra**Gestion des bases de données 12/12/2014 27