1 BASES DE DONNÉES Pr. : Mohamed BASLAM Niveau : S4 BCG Année : 2016/2017 2 Plan • Chap 1 : Initiation à l’informatique • Chap 2 : Démarche de construction d’une BD : • Concepts de base (Entité Association , Relations, Propriétés, Cardinalités,…) • Passage du modèle Entité/Association au modèle relationnel • Chap 3 : Modèles Conceptuel et Logique de données • Modèle Conceptuel de données (Entité/Relation) • Modèle Logique de données (MLD) • Chap 4 : Langage SQL : • LDD : Langage de Définition de Données • LMD : Langage de Manipulation de Données 3 Volume horaire et Évaluation de module • 20 heurs Cours • 12 heurs Travaux dirigés • 20 heurs Travaux pratiques • Examen sur cours + TD 75% (vers Novembre) • Examen TP 25% (vers Novembre) • Note finale = 25% Examen TP + 75% Examen final 4 CHAP1 : INITIATION À L’INFORMATIQUE Pr. : Mohamed BASLAM Niveau : S4 BCG Année : 2016/2017 5 Introduction • Information • Elément de connaissance représenté à l’aide de conventions en vue d’être conservé, traité et communiqué • Différentes formes : son, image, texte, vidéo ... • Traitement • Passage d’informations appelées données à d’autres informations dites résultats • Exemples : addition, multiplication, traduction... • Informatique • Science dont l’objet est le traitement automatique de l’information, c’est-à-dire, l’exécution du traitement par des machines • Ordinateur • Machine qui saisit (périphériques d’entrée), stocke (mémoire), traite (programmes) et restitue (périphériques de sortie) des informations 6 L’Ordinateur • Composants matériels (Hardware) • Tout ce qui compose l’ordinateur et ses accessoires • Incapables de traiter les informations sans un ensemble de règles (partie logicielle) pour les guider • Chaque composant possède une fonction particulière • calcul • stockage des données • affichage vidéo • gestion du clavier… 7 L’Ordinateur • Logiciel (Software) • Immatériel • ensemble de programmes exécutables par l’ordinateur • 3 types de logiciels • Système d’exploitation (MS-DOS, Windows, Unix) • Logiciels standards comme Word, Excel... • Progiciels : logiciels spécifiques (paye, comptabilité, ...) 8 L’Ordinateur Saisie Données Instructions Scanner CD-ROM Modem Restitution UC Micro Résultats Périphériques de sortie Périphériques d’entrée Joystick Traitement Mémoires auxiliaires Souris Caméra Clavier Disquette Ecran Disque dur Haut parleur Modem Imprimante 9 La Mémoire • Définition • Dispositif capable d’enregistrer, de stocker et de restituer des informations • Trois types • RAM ou mémoire vive • ROM ou mémoire morte • Mémoire de masse ou secondaire • Unité de stockage • BIT (Binary DigiT) : unité de stockage élémentaire • Toutes les informations sont codées sous forme de nombres binaires composés de 0 et de 1 • Les bits sont regroupés en octets (8 bits) • Selon l’ordinateur, un mot mémoire est composé de 2 (16 bits) ou 4 (32 bits) octets 10 La Mémoire • Unités de mesure 1Kio (kibiooctet) = 1 024 octets 1Mio (mébioctet) = 1 048 576 octets 1Gio (gibioctet) = 1 073 741 824 octets 1Tio (tébioctet) = 1 099 511 627 776octets 11 La Mémoire • Unités de mesure 1Ko (kilo octet) = 1 000 octets (exactement 103 octets) 1Mo (méga octet) = 1 000 000 octets 1Go (giga octet) = 1 000 000 000 octets 1To (téra octet) = 1 000 000 000 000 octets 12 La Mémoire • 2 Modes d’accès à la mémoire • En lecture : aucun effet sur le contenu • En écriture : modifie son contenu • Caractéristiques • Capacité : nombre d’octets • Accès • direct : grâce à l’adresse, accès immédiat à l’information (on parle de support adressable) • séquentiel : pour accéder à une information, il faut avoir lu toutes les précédentes (ex : cassette audio) • Temps d’accès : temps écoulé entre l’instant où l’information est demandée et celui où elle est trouvée (en ms) 13 La Mémoire • Le contenu de la mémoire est composé • de données • et d’instructions • code de l’opération élémentaire • donnée(s) ou adresse des données • Programme • Ensemble d’instructions et de données 14 L’Unité centrale • Fonctions • Sélectionner et exécuter les instructions du programme en cours • Partie de l’ordinateur qui contient les circuits de base • la mémoire principale • la mémoire vive (RAM) • la mémoire morte (ROM) • la mémoire cache • le microprocesseur • les circuits de calcul (UAL) • l’unité de contrôle (ou de commande) • l’unité d’entrée-sortie 15 L’Unité centrale • La mémoire vive ou RAM (Random Access Memory) • mémoire à accès direct à taille limitée • son contenu est volatile, i.e. il est perdu à chaque fois que l’ordinateur ne fonctionne pas. • endroit où l’ordinateur stocke temporairement les données et instructions (programmes) qu’il est en train d’utiliser et d’exécuter • contient tous les programmes en cours d’exécution • si le programme ne contient pas en mémoire vive, seule une partie est chargée, l’autre étant stockée en mémoire auxiliaire. Le temps d’accès de la mémoire auxiliaire étant plus grand que celui de la mémoire vive, plus la RAM est importante, plus l’exécution des programmes est rapide • capacité standard : 1Go, 2Go ou 4Go 16 L’Unité centrale • La mémoire morte (Read Only Memory) • mémoire permanente • contient des petits programmes écrits par le constructeur pour la mise en route de l’ordinateur 17 L’Unité centrale • Le mémoire cache • La transmission entre la RAM et le microprocesseur est plus lente que le potentiel de vitesse du microprocesseur • Mémoire cache • zone de mémoire ultra-rapide où sont conservées les données et instructions qui reviennent le plus souvent • mémoire interne de petite taille (dizaines de Ko) • Capacité standard : 256Ko ou 512Ko 18 L’Unité centrale • Le microprocesseur • Le cœur de l’ordinateur : il traite et fait circuler les instructions et les données • Composé des éléments suivants • Unité Arithmétique et Logique (UAL) • Ensemble de circuits qui exécutent les opérations arithmétiques et logiques de base • Unité de contrôle (ou de commande) • Son rôle est d’extraire une instruction du programme en MC, de la faire exécuter par l’UAL ou un périphérique et de chercher l’instruction suivante • Elle décode les instructions et trouve les données pour l’UAL • Produits • Intel (Pentium 166Mhz), Cyrix (P200+), AMD (K6200) 19 L’Unité centrale • L’unité d’entrée-sortie • Contrôle et gère le transfert d’informations entre l’UC et les périphériques • Exemples • carte graphique (écran) • carte contrôleur (disque dur) • carte son (micro, haut-parleur) 20 Les Périphériques • Définition • Tout ce qui gravite autour de l’UC c’est-à-dire l’écran, le clavier, la souris, les mémoires auxiliaires, l’imprimante, le scanner, le micro, les haut-parleurs.... • 3 Catégories de périphériques • d’entrée (clavier, souris, scanner…) • de sortie (écran, imprimante, haut-parleur) • les mémoires auxiliaires (disque dur, disquette, CD-ROM) 21 Le Système d’Exploitation • Définition • Ensemble des programmes chargés de toutes les fonctions relatives à l’exploitation des composants de l’ordinateur • Exemples de produits • MS-DOS, OS/2, UNIX, Windows 95, Windows 8 • Objectifs • faciliter la tâche de l’utilisateur en lui présentant une machine plus simple • assurer une exploitation efficace et économique des ressources critiques de l’ordinateur 22 À l’allumage... • Mise sous tension • Test par les programmes de la ROM • présence des périphériques (beep, le lecteur de disquette s’allume...) • Recherche du système d’exploitation en mémoire auxiliaire • sur le lecteur de disquette • sur le disque dur • Chargement en MC d’une partie du SE • noyau du SE : contient les instructions indispensables pour le bon fonctionnement de l’ordinateur • Recherche des fichiers • système : configuration du matériel.... • batch pour lancer par exemple automatiquement Windows 23 Systèmes de numération V 5 101 Five Cinq plusieurs représentations différentes du nombre 5 24 Systèmes de numération Systèmes de numération positionnels ex.: décimal non positionnels ex.: romain 1000 Mille M Un système de numération positionnel est caractérisé par sa base et par le nombre de symboles Base = valeur attribuée à 10 25 • Système décimal base : 10 symboles : 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 4693 = 4*103 + 6*102 + 9*101 + 3*100 • Système hexadécimal base : 16 { 1016 = 1610 } symboles : 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F A3C16 = A16*10162 + 3*10161 + C*10160 = 10*162 + 3*161 + 12*160 • Système binaire base : {102 = 210 } symboles : 0, 1 1012 = 12*1022 + 02*1021 + 12*1020 = 1*22 + 0*21 + 1*20 26 Conversion décimal hexadécimal 5438 5424 14 E16 16 339 336 3 316 16 21 16 5 516 16 1 0 1 16 0 STOP 116 543810 = 153E16 Méthode des divisions successives par 16 27 Conversion décimal binaire 149 74 37 18 9 4 2 1 0 1 0 1 0 1 0 0 1 14910 = 1001 01012 Méthode des divisions successives par 2 28 Conversion hexadécimal décimal 1 *16 16 5 + 3 E 16 5 *16 336 + 3 *16 5424 + 14 153E16 = 543810 = 543810 29 Conversion hexadécimal décimal autre méthode 1 5 3 E16 = 1016 = 1610 0 E * 16 1016 10 110 14 1 + 3 * 16 1016 10 1610 48 2 + 5 * 16 1016 10 25610 1280 3 + 1 * 16 1016 10 409610 4096 543810 30 Conversion binaire décimal 1 0 0 1 0 1 0 1 2 *2 2+ 0 *2 4+ 0 *2 8+ 1 *2 18 + 0 *2 36 + 1 *2 74 + 0 *2 148 + 1 = 14910 31 Conversion binaire décimal autre méthode 1 0 1 12 102 = 210 = 1 * 2101020 110 1 + 1 * 2101021 210 2 + 0 * 2101022 410 0 3 + 1 * 210 102 810 8 1110 32 Conversion hexadécimal binaire 16 = 24 Chaque chiffre hexadécimal 016 116 216 316 416 516 616 716 816 00002 00012 00102 00112 01002 01012 01102 01112 10002 916 A16 B16 C16 D16 E16 F16 4 chiffres binaires 10012 10102 10112 11002 11012 11102 11112 33 Conversion hexadécimal binaire A 4 8 C 16 1010 0100 1000 1100 2 Remplacer chaque chiffre hexadécimal par son équivalent binaire (en 4 chiffres) Remarque : on peut supprimer les 0 en début de nombre Exemple : 39D16 = X 0011 1001 11012 34 Conversion binaire hexadécimal 1010 0100 1000 11002 A 4 8 C 10100100100011002 = A48C16 Regrouper les chiffres binaires par 4 (en commençant par la fin du nombre) Remplacer chaque groupe de 4 chiffres binaires par son équivalent hexadécimal Exemple : 00 11 1001 10112 3 9 B 11100110112 = 39B16 35 Exercices Exercice 1 Représentez le nombre 24810 dans les bases 2, 3, 8, 9 et 16. (Utilisez la technique des divisions successives pour les bases 2, 3 et 16.) Exercice 2 Représentez les nombres 2810, 12910, 14710, 25510 sous leur forme binaire par une autre méthode que les divisions successives. À partir de cette représentation binaire, vous en déduirez leur représentation hexadécimale. 36 Exercices Exercice 3 1.Les nombres 110000102, 100101002, 111011112, 100000112, 101010002 sontils pairs ou impairs ? 2.Lesquels sont divisibles par 4, 8 ou 16 ? 3.Donnez le quotient et le reste d’une division entière par 2, 4 et 8 de ces nombres. Exercice 4 Donnez les valeurs décimales, minimales et maximales, que peuvent prendre des nombres signés et non signés codés sur 4, 8, 16, 32 et n bits. 37 CHAP. 2 : INTRODUCTION AUX BASES DE DONNÉES Pr. : Mohamed BASLAM Niveau : S4 BCG Année : 2015/2016 38 Qu’est-ce qu’une base de données ? • Définition Intuitive : Ensemble organisé d’informations partagé par plusieurs personnes. Peu importe le support utilisé pour rassembler et stocker les données (papier, fichiers, etc.), dès lors que des données sont rassemblées et stockées d’une manière organisée dans un but spécifique, on parle de base de données. Plus précisément, on appelle base de données un ensemble structuré et organisé permettant le stockage de grandes quantités d’informations afin d’en faciliter l’exploitation. • Base de données informatisée : Ensemble structuré de données enregistrées sur des supports accessibles par l’ordinateur, représentant des informations du monde réel et pouvant être interrogées et mises à jour par une communauté d’utilisateurs. 39 Qu’est-ce qu’un SGBD? • La gestion et l’accès à une base de données sont assurés par un ensemble de programmes qui constituent le Système de gestion de base de données (SGBD). • Un SGBD un ensemble de programmes permettant l’ajout, la modification, la recherche et la suppression des données. • La plupart des SGBD fonctionnent selon un mode client/serveur. • Le serveur reçoit des requêtes de plusieurs clients et ceci de manière concurrente, Le serveur analyse la requête, la traite et retourne le résultat au client. 40 Qu’est-ce qu’un SGBD? • Objectifs : Afin de résoudre les problèmes causés par la démarche classique, objectifs principaux ont été fixés aux SGBD : • Indépendance • • • • physique : Données sont définies indépendamment des structures de stockage utilisées. Indépendance logique : Un même ensemble de données peut être vu différemment par des utilisateurs différents. Administration centralisée des données (intégration) : Toutes les données doivent être centralisées dans un réservoir unique commun à toutes les applications. Non redondance des données : Afin d'éviter les problèmes lors des mises à jour, chaque donnée ne doit être présente qu’une seule fois dans la base. Sécurité des données : Les données doivent pouvoir être protégées contre les accès non autorisés. Pour cela, il faut pouvoir associer à chaque utilisateur des droits d’accès aux données. 41 Qu’est-ce qu’un SGBD? • Quelques SGBD connus et utilisés : • PostgreSQL : http ://www.postgresql.org/ – dans le domaine public ; • MySQL : http ://www.mysql.org/ – dans le domaine public ; • Oracle : http ://www.oracle.com/ – de Oracle Corporation ; • IBM DB2 : http ://www-306.ibm.com/software/data/db2/ • Microsoft SQL : http ://www.microsoft.com/sql/ • Sybase : http ://www.sybase.com/linux • Informix : http ://www-306.ibm.com/software/data/informix/ 42 Conception des bases de données : le modèle entités associations. • Pourquoi une modélisation préalable ? • Il est difficile de modéliser un domaine sous une forme directement utilisable par un SGBD. • Un SGBD oblige certaines règles pour répondre au objectifs tracé dans le slide 39 (indépendance données-programme, Non redondance des données .. ). • Le modèle entités-associations permet une description naturelle du monde réel à partir des concepts d’entité et d’association. • Ce modèle, utilisé pour la phase de conception, s’inscrit notamment dans le cadre d’une méthode plus générale et très répandue : Merise. 43 Conception des bases de données : le modèle entités associations. • MERISE : Méthode d’Etude et de Réalisation Informatique pour les • • • • Systèmes d’Entreprise. Langage (resp Modèle) de spécification (resp conception) le plus répandu dans le domaine des bases de données. Un des concepts clés de la méthode MERISE est la séparation des données et des traitements. Les données représentent la statique du bases de données et les traitements sa dynamique. La méthode MERISE nous conduit à une modélisation des données en entités et en associations. 44 Conception des bases de données : le modèle entités associations. • MERISE propose une démarche, dite par niveaux, de trois ordres : la conception, l’organisation et la technique : • Niveau conceptuel : Le modèle conceptuel des données (MCD) décrit les entités du monde réel, en terme d’objets, de propriétés et de relations. • Niveau logique : Le modèle logique des données (MLD) précise le modèle conceptuel par des choix organisationnels. Il s’agit d’une transcription du MCD dans un formalisme adapté à une implémentation ultérieure. • Niveau physique : le modèle physique des données (MPD) permet d’établir la manière concrète dont le système sera mis en place (SGBD retenu). 45 Eléments constitutifs du modèle entitésassociations. • La représentation du modèle entités-associations s’appuie sur trois concepts de base : • Objet ou entité : ayant une existence propre. • L’association : un lien ou relation entre objets sans existence propre (abstrait). • La propriété : la plus petite donnée d’information décrivant un objet ou une association. 46 Eléments constitutifs du modèle entitésassociations. Représentation graphique d’un exemple de type-entité. • Définitions : • Entité : désigne un ensemble d’instances qui possèdent une sémantique et des propriétés communes (exemple : Personne, Livre..). • Instance : Un objet, une chose concrète ou abstraite qui peut être reconnue distinctement et qui est caractérisée par son unicité (exemple : Ali, Younes .. sont des instance de l’entité Personne). 47 Eléments constitutifs du modèle entitésassociations. • Attribut ou propriété, valeur : Un exemple de type-entité comportant trois attributs (propriétés) • Définitions : • Attribut (propriété) : est une caractéristique associée à une entité ou à une association. Exemples d’attribut : le nom d’une personne, le titre d’un livre, la puissance d’une voiture. • Chaque attribut (propiété) d'une entité possède un domaine qui définit l’ensemble des valeurs possibles qui peuvent être choisies pour lui (entier, chaîne de caractères, booléen, . . .). 48 Eléments constitutifs du modèle entitésassociations. • Identifiant ou clé : Entité comportant quatre attributs dont un est un identifiant : deux personnes peuvent avoir le même nom, le même prénom et le même âge, mais pas le même numéro de sécurité sociale. • Identifiant, clé : Un identifiant (ou clé) d’un entité ou d’un association est constitué par un ou plusieurs de ses attributs qui doivent avoir une valeur unique pour chaque entité ou association de ce type. • Règle : Chaque entité possède au moins un identifiant, qui peut être éventuellement formé de plusieurs attributs. 49 Eléments constitutifs du modèle entitésassociations. • Association ou relation : Représentation graphique d’un exemple d’association liant deux entités. Définitions : • Instance d'une association (ou une relation) : est un lien entre plusieurs entités. Exemples d’association : l’emprunt par l’étudiant Younes du 3e exemplaire du livre « Abrège de Chimie organique ». • L’Association : désigne un ensemble de relations qui possèdent les mêmes caractéristiques. L’association décrit un lien entre plusieurs entités. 50 Eléments constitutifs du modèle entitésassociations. • Association ou relation : Définitions : • Participant : Les entités intervenant dans une association sont appelés les participants de cette association (Personne et Livre sont des participants de l’association Emprunter). • Collection : L’ensemble des participants d’une association est appelé la collection de cette association( {Personne + Livre} forment la collection de l’association Emprunter) . • dimension ou arité d’une association : c'est le nombre d'entités contenu dans la collection (la dimension de Emprunter = 2) . 51 Eléments constitutifs du modèle entitésassociations. • Cardinalité : Représentation graphique des cardinalités d’une association. Dans cet exemple pédagogique, on suppose qu’un livre ne peut posséder qu’un auteur Définitions : • Cardinalité : La cardinalité d’une patte reliant une association et une entité précise le nombre de fois minimal et maximal d’interventions d’un istance d'une entité dans un instance d'une association. • Exemple de cardinalité : une personne peut être l’auteur de 0 à n livre, mais un livre ne peut être écrit que par une personne. • Règle 1 : L’expression de la cardinalité est obligatoire pour chaque patte d’une association. • Règle 2 : Une cardinalité minimal est toujours 0 ou 1 et une cardinalité maximale est toujours 1 ou n. 52 Eléments constitutifs du modèle entitésassociations. • Associations plurielles : Exemple d’associations plurielles entre Personne et Livre. Sur ce schéma, une association permet de modéliser que des personnes écrivent des livres et un autre que des personnes critiquent (au sens de critique littéraire) des livres. Deux mêmes entités peuvent être plusieurs fois en association 53 Eléments constitutifs du modèle entitésassociations. • Association réflexive : Exemple d’associations réflexives sur Personne. La premier association permet de modéliser la relation parent/enfant et la deuxième association la relation de fraternité. L'association Etre frère est dite symétrique alors que la relation Etre parent est non symétrique. 54 Eléments constitutifs du modèle entitésassociations. • Association n-aire (n > 2) : Exemple de type-association ternaire inapproprié. Problématique : Ce genre d’associations sont plus difficiles à manipuler et à interpréter, notamment au niveau des cardinalités. 55 Eléments constitutifs du modèle entitésassociations. • Association n-aire (n > 2) : Association ternaire de la figure sur la gauche corrigée en deux associations binaires (figure sur la droite) Solution : consiste à éclater l’association ternaire Contient en deux associations binaires comme représenté sur la figure ci-dessus 56 Règles de bonne formation d'un modele entitéassociation • Règles portant sur les noms: • Règle 1 Dans un modèle entités-associations, le nom d’une entité, d’une association ou d’un attribut doit être unique. Les deux entités Enseignant et Etudiant doivent être fusionnés en une unique entité Personne. 57 Règles de bonne formation d'un modele entitéassociation • Règles de normalisation des attributs: • Règle 2 Il faut remplacer un attribut multiple en une association et une entité supplémentaires. Remplacement des attributs multiples en une association et une entité et décomposition des attributs composites. 58 Règles de bonne formation d'un modele entitéassociation • Règles de fusion/suppression d’entités/associations • Règle 3 Il faut factoriser les entités et les associations quand c’est possible. Il faut factoriser les entités quand c’est possible, éventuellement en introduisant un nouvel attribut. . 59 Règles de bonne formation d'un modele entitéassociation • Règles de fusion/suppression d’entités/associations • Règle 3 Il faut factoriser les entités et les associations quand c’est possible Il faut factoriser les type-entités quand c’est possible, éventuellement en introduisant un nouvel attribut. . 60 Règles de bonne formation d'un modele entitéassociation • Normalisation des entités et des associations • Les formes normales sont des lois standards qui permettent d’éviter la redondance, source d’anomalies modélisation entité/association. et garantissent la bonne • La normalisation s’applique sur le modèle entités-associations, que sur un modèle relationnel. • Il existe 5 formes normales principales et deux extensions. Plus le niveau de normalisation est élevé, plus le modèle est exempte de redondances 61 Règles de bonne formation d'un modele entitéassociation • Première forme normale (1FN) Première forme normale (1FN) - Une entité ou une association est en première forme normale si tous ses attributs sont élémentaires, c’est-à-dire non décomposables. Exemple de normalisation en première forme normale. 62 Règles de bonne formation d'un modele entitéassociation • Deuxième forme normale (2FN) • Deuxième forme normale (2FN) - Une entité ou une association est en deuxième forme normale si, et seulement si, il est en première forme normale et si tout attribut n’appartenant pas à la clé dépend de la totalité de cette clé. Exemple de normalisation en deuxième forme normale. On suppose qu’un même fournisseur peut fournir plusieurs produits et qu’un même produit peut être fourni par différents fournisseurs. • Autrement dit, les attributs doivent dépendre de l’ensemble des attributs participant à la clé, et que cette dernière définie d’un manière unique son entité. 63 Règles de bonne formation d'un modele entitéassociation • Troisième forme normale (3FN) • Troisième forme normale (3FN) – Une entité ou une association est en troisième forme normale si, et seulement si, il est en deuxième forme normale et si tous ses attributs dépendent directement de sa clé et pas d’autres attributs. Exemple de normalisation en troisième forme normale. Dans cet exemple, l’attribut Adresse fournisseur dépend de l’attribut Fournisseur. 64 Élaboration d’un modèle entités-associations • Pour concevoir un modèle entités-associations, vous devrez certainement passer par une succession d’étapes. Nous les décrivons ci-dessous dans l’ordre chronologique. Sachez cependant que la conception d’un modèle entitésassociations est un travail non linéaire. Vous devrez régulièrement revenir à une étape précédente et vous n’avez pas besoin d’en avoir terminé avec une étape pour commencer l’étape suivante : Recueil des besoins : Collectez l’ensemble des données à partir des documents de l’entreprise, d’un éventuel cahier des charges Tri de l’information : Faites le tri dans les données recueillies. Il faut faire attention aux problèmes de synonymie/polysémie. En effet, les attributs ne doivent pas être redondants. Identification des entités : Repérage des entités et leurs attribut. Identification des associations : Repérage des repéré dans l’étape précédente. Vérification du modèle : Vérifiez que le modèle respecte bien les règles de la normalisation associations entre les entités 65 Élaboration d’un modèle entités-associations • Exemple « Gestion d’une banque »: Une banque désire posséder un SGBD pour suivre ses clients. Elle désire ainsi stocker les coordonnées de chaque client (nom, prénom adresse), et les comptes dont elle dispose ainsi que leur solde (sachant par ailleurs que certains compte ont plusieurs bénéficiaires). On stockera également les opérations relatives à ces comptes (retrait et dépôt, avec leur date et le montant). Elaborer le model entité/Association correspondant 66 CHAP. 3 : LE MODÈLE RELATIONNEL Pr. : Mohamed BASLAM Niveau : S4 BCG Année : 2015/2016 67 Démarche de construction d’une BD Interview Document Modélisation Entité association Schéma logique en Entité-Association Transformation en relationnel Schéma conceptuel en relationnel 68 Généralités • Notions de modèle de données : • Un modèle est un ensemble d’outils utilisés pour décrire et manipuler des données • Modèle relationnel • Créé par CODD (IBM 69/70). • La majorité des SGBD actuels sont basés sur ce modèle. • Dispose d’un Langage de Description des Données (LDD) et d’un Langage de Manipulation des Données (LMD). • Principe simple : 1 seul concept (relation ou table) pour décrireles données et les liens entre ses données. • Rigoureusement défini par la notion d’ensemble • SQL : langage standard de description et de manipulation des données.. 69 Concepts du modèle • Table (relation) : Vision tabulaire du relationnel : • Le données (le schéma logique) sont représentées dans une table • Exemple : la table OUVRAGES décrit un ouvrage Modèle relationnel OUVRAGES Côte Titre Editeur Année NbExe mplaire T hème 12TA1 Réseaux informatiques Eyrolles 1998 10 Interconnexion, réseaux Internet 13GO1 Algorithmes génétiques Addison W esley 1994 5 AG , informatique évolutionniste, 15TA2 Système d’exploitation Eyrolles 1993 6 UNIX , SE, ordinateur 70 Concepts du modèle • Attribut : nom donné à une colonne d'une table (exemple cote, Titre, • • • • • • • Editeur, etc.). La première ligne de la table comporte ses attributs. Nom de la table (ou de la relation) : OUVRAGES. Tuple (ou n-uplet) : nom donné à une ligne comportant des valeurs saisies. (tuple : 12TA ; Réseaux informatiques ; Eyrolles ; 1998 ; 10 ; Interconnexion réseau Internet). Extension d’une table : le contenu de la table tous les tuples. Cardinalité : nombre de tuples de la relation. Exemple la cardinalité dans OUVRAGES est 3. L'ordre des lignes et des colonnes n ’est pas significatif. Pas de lignes identiques. Une case une valeur (voir redondance des informations). 71 Concepts du modèle • Attribut • nom donné à une colonne d'une relation • prend ses valeurs dans un domaine • Domaine : ensemble de valeurs possibles prises par lesattributs • Exemples • ENTIER, REEL, CHAINES DE CARACTERES • Dirham • SALAIRE = {4 000..100 000} • COULEUR= {BLEU, BLANC, ROUGE} • POINT = {(X:REEL,Y:REEL)} • TRIANGLE = {(P1:POINT,P2:POINT,P3:POINT)} 72 Notions de clé primaire • Clé primaire : Groupe d’attributs minimum qui détermine un tuple d’une manière unique dans la table. • Exemple de clés : • Le numéro de la SECU • Le numéro étudiant • La clé de la table OUVRAGES est l’attribut « cote », car la cote permet de déterminer de façon unique une ligne de la table • ATTENTION : la clé se détermine par rapport à toutes les valeurs possibles de l’attribut (ou les attributs) formant la clé primaire, et surtout pas par rapport aux valeurs déjà saisies • Remarque : toute table doit obligatoirement avoir une clé primaire. 73 Schéma d’une table • Le schéma d’une table, appelé aussi le schéma en intention, comporte le nom de la relation, ses attributs + format et la clé primaire. • La clé primaire est souvent soulignée (et/ou mise en gras) • Exemple : le schéma de la table OUVRAGES est : OUVRAGES (cote: texte, Titre: Texte, Editeur: NbExemplaire: Numérique, Année: Date, Thème: Texte ) Texte, 74 Problème de Redondance des données OUVRAGES Côte Titre Année NbExe mplaire T hème NomAuteur PrénomAuteur 12TA1 Réseaux informatiques 1998 10 Interconnexion, réseaux Internet Tanenbaum Henri 13GO1 Algorithmes génétiques 1994 5 AG , informatique évolutionniste, Goldberg Stephen 13GO1 Algorithmes génétiques 1994 5 AG , informatique évolutionniste, Holland John 15TA2 Système d’exploitation 1993 6 UNIX , SE, ordinateur Cardy Ronald 15TA2 Système d’exploitation 1993 6 UNIX , SE, ordinateur Dumar Eric 15TA2 Système d’exploitation 1993 6 UNIX , SE, ordinateur Tannenbaum Henri • La redondance = répétition des informations. • Un des objectifs des SGBD est (de nous permettre) de représenter les données avec le moins de redondance possible • Comment éliminer les redondances ? 75 Éliminer les redondances • Pour éliminer les répétitions nous allons dans premier temps construire une table auteur comportant tous les auteurs • La table auteur est décrite par AUTEURS (NumAuteur, NomAuteur,PrénomAuteur). Nous avons rajouté l’attribut NumAuteur pour représenter la clé. NumAuteur est un numéro qui peut être donné automatiquement par le SGBD. AUTEURS NumAuteur NomAuteur PrénomAuteur 1 Tanenbaum Henri 2 Goldberg Stephen 3 Holland John 4 Cardy Ronald 5 Dumar Eric 76 Éliminer les redondances OUVRAGES Côte Titre Année NbExempl aire T hème NumAuteur 12TA1 Réseaux informatiques 1998 10 Interconnexion, réseaux Internet 1 13GO1 Algorithmes génétiques 1994 5 AG , informatique évolutionniste, 2 13GO1 Algorithmes génétiques 1994 5 AG , informatique évolutionniste, 3 15TA2 Système d’exploitation 1993 6 UNIX , SE, ordinateur 4 15TA2 Système d’exploitation 1993 6 UNIX , SE, ordinateur 5 15TA2 Système d’exploitation 1993 6 UNIX , SE, ordinateur 1 Cette représentation nous permet effectivement de réduire la table OUVRAGES il n y a que le numéro de l’auteur au lieu du nom et du prénom, mais Il y a toujours des redondances. La redondance provient du fait qu’un OUVRAGE peut avoir plusieurs auteurs. 77 Éliminer les redondances • Pour éliminer ces redondances, nous allons construire une table ECRIT qui permet de relier les OUVRAGES et leurs AUTEURS. • Rappelons qu’un des intérêts d’un SGBD est sa possibilité de créer des liens entre les objets. • Le schéma de la table ECRIT est : ECRIT (cote, NumAuteur) , il suffit donc de prendre les clés primaires des tables OUVRAGES et AUTEURS et former une nouvelle nouvelle table, en l’occurrence ECRIT. ECRIT Côte NumAuteur 12TA1 1 13GO1 2 13GO1 3 15TA2 4 15TA2 5 15TA2 1 78 Éliminer les redondances OUVRAGES . Côte Titre Année NbExempl aire T hème 12TA1 Réseaux informatiques 1998 10 Interconnexion, réseaux Internet 13GO1 Algorithmes génétiques 1994 5 AG , informatique évolutionniste, 15TA2 Système d’exploitation 1993 6 UNIX , SE, ordinateur AUTEURS ECRIT Côte NumAuteur NumAuteur NomAuteur PrénomAuteur 12TA1 1 1 Tanenbaum Henri 13GO1 2 2 Goldberg Stephen 13GO1 3 3 Holland John 15TA2 4 15TA2 5 4 Cardy Ronald 15TA2 1 5 Dumar Eric 79 Éliminer les redondances • La base de données décrivant les OUVRAGES sera composé des tables suivantes : AUTEURS(NumAuteur, Nom, Prénom) OUVRAGES(cote, Titre, NbExemplaire, Année, Editeur, Thème) ECRIT (cote, NumAuteur) • Noter que nous avons supprimé NumAuteur de la table OUVRAGES l’attribut 80 Clé Étrangère • Les attributs cote et NumAuteur de la table ECRIT proviennent en fait respectivement des tables OUVRAGES et AUTEURS. Ces deux Attributs sont clés primaires dans chacune de ces tables. • Définition : Nous appelons Clé étrangère toute clé primaire apparaissant dans une autre table. • Exemple : • NumAuteur est une clé étrangère dans la table ECRIT • Cote est aussi une clé étrangère dans ECRIT Par convention, Une clé étrangère commence par le symbole “#” • Attention : la notion de clé est toujours liée à une table, un attribut (ou groupe d’attributs) est clé primaire, ou clé étrangère dans une table donnée. 81 Contraintes d’intégrités • Un des avantages des bases de données par rapport à une gestion de fichiers traditionnelle réside dans la possibilité d’intégrer des contraintes que doivent vérifier les données à tout instant. • Exemple : on souhaite poser les contraintes suivantes : • le nombre d’exemplaire de chaque OUVRAGE doit être supérieur à 0 (zéro) • chaque OUVRAGE doit avoir au moins un auteur • Etc. • Ceci est possible grâce à la notion de contraintes d’intégrité • Définition : • Contraintes d’intégrité «sont des assertions qui doivent être vérifiées à tout moment par les données contenues dans la base de données» 82 Contraintes d’intégrités Trois types de C.I. Obligatoires : • Contrainte de clé : une relation doit posséder une clé primaire • Contrainte d’entité : un attribut d’une clé ne doit pas posséder de valeurs nulles (vides) • Contrainte de référence (pour les clés étrangères), c’est une contrainte exprimée entre deux tables. Tout tuple d’une relation faisant référence à une autre relation doit se référer à un tuple qui existe. • Intuitivement, cela consiste à vérifier que l'information utilisée dans un tuple pour désigner un autre tuple est valide, notamment si le tuple désigné existe bien • En d’autre terme, quand on désigne un attribut comme clé étrangère, les seules valeurs que peut prendre cet attribut sont celles qui sont déjà saisies dans la table qu’il référence (voir exemple sur la page suivante). 83 Contraintes d’intégrités • Contrainte optionnelle • Contrainte de domaine : liée au domaine de définition d'un attribut. Exemple: NbExemplaire > 0 • Les contraintes d’intégrité sont vérifiées (exécutées) à chaque mise à jour de la base de données (ajout, suppression ou modification d’un tuple). Si, lors d’une mise à jour une contrainte n’est pas satisfaite, cette mise à jour ne peut pas avoir lieu. 84 Contraintes d’intégrités (Exemlpe) • Schéma de la relation AUTEURS • AUTEURS (NumAuteur, Nom, Prénom) • Schéma de la relation OUVRAGES (avec la modification de l’exercice précédent) OUVRAGES(cote, Titre, NbExemplaire, Année, NumEditeur, Thème) clé primaire : cote Contrainte de domaine : NbExemplaire >0 Contrainte référentielle : OUVRAGES.NumEditeur est une clé étrangère et fait référence à EDITEURS.NumEditeur Il suffit d’écrire : Contrainte référentielle : OUVRAGES.NumEditeur REFERENCE EDITEURS.NumEditeur 85 Contraintes d’intégrités (Exemlpe) • Schéma de la table ECRIT ECRIT (NumAuteur,cote) Clé primaire : NumAuteur, cote Contraintes référentielles : • ECRIT.NumAuteur REFERENCE AUTEURS.NumAuteur • ECRIT.cote REFERENCE OUVRAGES.cote • Le fait d’écrire ECRIT.cote REFERENCE OUVRAGES.cote, c’est à dire définir l’attribut cote dans ECRIT comme clé étrangère, implique une contrainte référentielle. Ceci se traduit par ; les seules valeurs que peut prendre cote dans ECRIT sont celles qui sont déjà saisies dans cote d’OUVRAGES (c’est à dire dire 12TA1, 13GO1,15TA2) 86 Contraintes d’intégrités (Exemlpe) • Le schéma d’une base de données est composé de l’ensemble des schémas des tables (relations) définies dans cette BD • Exemple schéma de la base de données permettant la gestion de notices bibliographiques est : AUTEURS (NumAuteur, Nom, Prénom) OUVRAGES(cote, Titre, NbExemplaire, Année, NumEditeur, Thème) • Contrainte de domaine : NbExemplaire >0 • Contrainte référentielle : OUVRAGES.NumEditeur REFERENCE EDITEURS.NumEditeur ECRIT (NumAuteur, cote) • Contraintes référentielles : • ECRIT.NumAuteur REFERENCE AUTEURS.NumAuteur • ECRIT.cote REFERENCE OUVRAGES.cote 87 Bilan Relation ou Table Attribut ou Colonne Domaine ou Type Clé primaire Clé étrangère Contrainte d’intégrité Schéma d’une relation Schéma d’une base de données 88 CHAP. 4 : MODÈLE LOGIQUE DES DONNÉES –MLDPr. : Mohamed BASLAM Niveau : S4 BCG Année : 2015/2016 89 Plan • MLD • Modèle Logique de Données Relationnelles • Tables, lignes et colonnes • Clés primaires et clés étrangères • Schéma relationnel • Traduction d’un MCD en MLD. 90 Etapes de conception Une fois le MCD établi, nous sommes en mesure de le traduire en système logique : MLD 91 Modèle Logique de Données Relationnelles • Comment est constitué un MLD et comment l’établir ? • Tables, lignes et colonnes • Clés primaires et clés étrangères • Schéma relationnel • Lien entre MCD et MLD 92 Modèle Logique de Données Relationnelles • Tables, lignes et colonnes Lorsque les données ont la même structure (par ex. renseignements relatifs à un client), on peut alors les organiser en tables dans lesquelles : • les colonnes décrivent les champs en commun • les lignes contiennent les valeurs de ces champs pour chaque enregistrement 93 Modèle Logique de Données Relationnelles • Tables, lignes et colonnes Exemple : Contenu de la table Clients 94 Modèle Logique de Données Relationnelles • Clés primaires et clés étrangères • Clé primaire : Les lignes d’une table sont uniques il existe au moins une colonne qui sert à identifier les lignes : il s’agit de la clé primaire de la table. Propriétés requises : • la valeur vide (NULL) est interdite • la valeur de la clé primaire d’une ligne ne devrait pas changer au cours du temps 95 Modèle Logique de Données Relationnelles • Clés primaires et clés étrangères • Clé étrangère : 96 Modèle Logique de Données Relationnelles • Clés primaires et clés étrangères • Conventions : • on souligne les clés primaires • on fait précéder d’un # les clés étrangères Par exemple dans la description des colonnes d’une table: a) Clients(numclient, nom client, prénom, adresse) b) Commandes(numcommande, date commande, #numclient (non vide)). 97 Modèle Logique de Données Relationnelles • Clés primaires et clés étrangères Remarque : une même table peut avoir plusieurs clés étrangères mais une seule clé primaire (éventuellement composée de plusieurs colonnes) une clé étrangère peut aussi être primaire (dans la même table) une clé étrangère peut être composée (c’est le cas si la clé primaire référencée est composée) implicitement chaque colonne qui compose une clé primaire ne peut pas recevoir la valeur vide (NULL interdit) par contre, si une clé étrangère ne doit pas recevoir la valeur vide, alors il faut le préciser dans la description des colonnes 98 Modèle Logique de Données Relationnelles • Clés primaires et clés étrangères • Schéma relationnel les tables sont appelées relations les liens entre les clés étrangères et leur clé primaire sont symbolisés par un connecteur • Notations On dit qu’une association binaire (entre deux entités ou réflexive) est de type : 1:1 (un à un) si aucune des 2 cardinalités maximales n’est n 1:n (un à plusieurs) si une des 2 cardinalités maximales est n n :m (plusieurs à plusieurs) si les 2 cardinalités maximales sont n 99 Modèle Logique de Données Relationnelles • Passage d’un MCD (modèle E/A) en MLD (Modèle relationnel) • Règle 1 Chaque entité donne naissance à une relation du même nom. Chaque propriété d’une entité devient un attribut de la relation. L’identifiant de l’entité devient la clé de la relation. 100 Modèle Logique de Données Relationnelles • Passage d’un MCD (modèle E/A) en MLD (Modèle relationnel) • Règle 2 Si une association n’a aucune patte de cardinalité maximale égale à 1(Une association binaire de type n :m), alors : Cette association devient une relation (s’appelle table de jonction) Chaque propriété de l’association devient un attribut de la relation La clé primaire de cette association est composée des deux clés étrangères. 101 Modèle Logique de Données Relationnelles • Passage d’un MCD (modèle E/A) en MLD (Modèle relationnel) • Règle 2 (Exemple) 102 Modèle Logique de Données Relationnelles • Passage d’un MCD (modèle E/A) en MLD (Modèle relationnel) • Règle 3 Une association binaire de type 1 : n disparaît, au profit d’une clé étrangère dans la table coté 0,1 ou 1,1 qui référence la clé primaire de l’autre table. Cette clé étrangère ne peut pas recevoir la valeur vide si la cardinalité est 1,1 . 103 Modèle Logique de Données Relationnelles • Passage d’un MCD (modèle E/A) en MLD (Modèle relationnel) • Règle 4 Une association non binaire est traduite par une table supplémentaire dont la clé primaire est composée d’autant de clés étrangères que d’entité en association. Les attributs de l’association deviennent les colonnes de cette nouvelle table. Enseignant NoEnseignant Nom Prénom Date_de_naissance 1,n Enseigne 0,n Matière Code_Matière Libellé ENSEIGNANT ENSEIGNE NOENSEIGNANT NOM PRÉNOM DATE_DE_NAISSANCE NoEnseignant Code_Matière Nom_Classe 1,n . Classe Nom_Classe Cycle CLASSE NOM_CLASSE CYCLE MATIÈRE CODE_MATIÈRE LIBELLÉ 104 Exercice Réalisateur N° Réalisateur Nom Réal 1,n A réalisé . Agent Id Agent Nom Agent 1,n Manager 1,n 1,1 FILM N° Film Nom Film Acteur N° Acteur Nom Acteur 1,n 1,n A tourné dans N° Contrat 105 Solution Réalisateur N° Réalisateur Nom Réal Agent Id Agent Nom Agent Réaliser #N° Réalisateur #N° Film Acteur N° Acteur #Id Agent Nom Acteur FILM N° Film Nom Film Tourner #N° Film #N° Acteur N° contrat 106 CHAP. 5 : LANGAGE SQL (STRUCTURED QUERY LANGUAGE) Pr. : Mohamed BASLAM Niveau : S4 BCG Année : 2015/2016 107 Plan • Généralités • Langage de Définition des Données (LDD) • Langage de Manipulation des données LMD 108 Etapes de conception Une fois le MLD établi, nous sommes en mesure de créer la base de données à l’aide de SQL (Etape MPD) 109 Généralités • SQL (Structured Query Language) • Interface de communication entre un développeur et un SGBD relationnel. • Langage non procédural. • Plusieurs versions de SQL • 1989 : plus ancien standard • 1992 : SQL-92 ou SQL-2 • 1999 : SQL-99 ou SQL-3 ((un peu) orienté objet) • 2003 : SQL-2003 (fonctions pour XML) 110 Généralités • SQL permet : • de définir le schéma de la base de données (LDD) • de gérer la base de données (LDD) : sécurité, organisation physique • de charger les tables relationnelles (LMD) • de manipuler les données stockées (LMD) Ici : aperçu de la partie LDD Plus loin : le LMD 111 Langage de Définition de Données • Le LDD : • partie de SQL qui permet de créer et décrire les bases, les tables et autres objets manipulés par les SGBD. • Cette partie contient les commandes : • La commande CREATE • La commande ALTER TABLE • La commande DROP TABLE 112 Langage de Définition de Données • Types SQL: Type Description Taille INTEGER les entiers signés 4 Octs BIGINT les entiers signés 8 Octs REAL les réels comportant 6 chiffres significatifs 4 Octs DOUBLE PRECISION les réels comportant 15 chiffres significatifs 8 Octs NUMERIC[(P, [L])] les données numériques à la fois entières et réelles avec une précision de 1000 chiffres significatifs. L précise le nombre maximum de chiffres significatifs stockés et P donne le nombre maximum de chiffres après la virgule. P Octs 113 Langage de Définition de Données • Types SQL: Type Description Taille CHAR(L) les chaînes de caractères de longueur fixe. L doit être inférieur à 255, sa valeur par défaut est 1. L Octs VARCHAR(L) les chaînes de caractères de longueur variable. longueur L Octs doit être inférieur à 2000, il n’y a pas de valeur par défaut. DATE les données constituées d’une date 4 Octs TIMESTAMP les données constituées d’une date et d’une heure 8 Octs TEXT les chaînes de caractères de longueur variable. - ... ... … 114 Langage de Définition de Données • La commande CREATE : Création d’une base de donnée: Pour créer une base de données qui sera appelée ≪ma_base≫ il suffit d’utiliser la requête suivante qui est très simple : CREATE DATABASE ma_base 115 Langage de Définition de Données • La commande CREATE : Création d’une Table : CREATE TABLE nom_de_la_table ( colonne1 type_donnees, colonne2 type_donnees, colonne3 type_donnees, colonne4 type_donnees ); • 4 colonnes ont été définies. • Le mot-cle ≪ type_donnees ≫ sera a remplacer par un mot-cle pour définir le type de données (INT, DATE, TEXT …). 116 Langage de Définition de Données • La commande CREATE : Création d’une Table : Exemple 1 CREATE TABLE Internaute ( email VARCHAR (50) NOT NULL, nom VARCHAR (20) NOT NULL, prenom VARCHAR (20), motDePasse VARCHAR (60) NOT NULL, anneeNaiss DECIMAL (4)); • il est également possible de définir des options telles que (liste non-vide): • NOT NULL : empêche d’enregistrer une valeur nulle pour une colonne. • DEFAULT : attribuer une valeur par défaut si aucune données n’est indiquée pour cette colonne lors de l’ajout d’une ligne dans la table. • PRIMARY KEY : indiquer si cette colonne est considérée comme clé primaire pour un index. 117 Langage de Définition de Données • La commande CREATE : Création d’une Table : Exemple 2 CREATE TABLE utilisateur ( id INT PRIMARY KEY NOT NULL, nom VARCHAR(100), prenom VARCHAR(100), email VARCHAR(255), date_naissance DATE, pays VARCHAR(255), ville VARCHAR(255), code_postal VARCHAR(5), nombre_achat INT ); 118 Langage de Définition de Données • Les Clés: Clé primaire « PRIMARY KEY » (autre méthode) CREATE TABLE Internaute ( email VARCHAR (50) NOT NULL, nom VARCHAR (20) NOT NULL, prenom VARCHAR (20), motDePasse VARCHAR (60) NOT NULL, anneeNaiss DECIMAL (4), PRIMARY KEY (email) ); 119 Langage de Définition de Données • Les Clés: Clé primaire (Clé constituée de plusieurs attributs) CREATE TABLE Notation ( idFilm INTEGER NOT NULL, email VARCHAR (50) NOT NULL, note INTEGER DEFAULT 0, titre VARCHAR(80) NOT NULL, PRIMARY KEY (titre, email) ); 120 Langage de Définition de Données • Les Clés: Clé secondaire : On spécifie que la valeur d’un attribut est unique pour l’ensemble de la colonne. CREATE TABLE Artiste ( id INTEGER NOT NULL, nom VARCHAR (30) NOT NULL, prenom VARCHAR (30) NOT NULL, anneeNaiss INTEGER, PRIMARY KEY (ID), UNIQUE (nom, prenom) ); 121 Langage de Définition de Données • Les Clés: Clé étrangère (Commande : FOREIGN KEY) Attributs qui font référence à une ligne dans une autre table. Référence la clé primaire de la table Artiste CREATE TABLE Film ( idFilm INTEGER NOT NULL, nom VARCHAR (50) NOT NULL, année INTEGER NOT NULL, idMES INTEGER, codePays INTEGER, PRIMARY KEY (ideFilm), FOREIGN KEY (idMES) REFERENCE Artiste, FOREIGN KEY (codePays) REFERENCE Pays); Le SGBD vérifiera, pour toute modification pouvant affecter le lien entre les deux tables, que la valeur de idMES correspond bien à une ligne de Artiste. 122 Langage de Définition de Données • La commande ALTER: Modification d’une Table : permet de modifier une table existante. Il est ainsi possible d’ajouter une colonne, d’en supprimer une ou de modifier une colonne existante, par exemple pour changer le type. Syntaxe : ALTER TABLE nom_table instruction ; Ou instruction peut etre ADD, MODIFY, DROP ou RENAME 123 Langage de Définition de Données • La commande ALTER: Modification d’une Table : Différents types d’instructions sont possibles : • Ajout d’une colonne (ADD COLUMN) • Modification de la définition d’une colonne (MODIFY COLUMN) • Suppression d’une colonne (DROP COLUMN) • Modification du nom de la table ou d’une colonne (RENAME TO, RENAM COLUMN). 124 Langage de Définition de Données • La commande ALTER: Modification d’une Table : ADD COLUMN (Ajouter colonne) ALTER TABLE Internaute ADD COLUMN region VARCHAR(10); DROP COLUMN (Supprimer une colonne) ALTER TABLE Internaute DROP COLUMN region VARCHAR(10); MODIFY COLUMN (modifier une colonne) ALTER TABLE Internaute MODIFY COLUMN anneeNaiss VARCHAR(30) NOT NULL; RENAME (Renommer une table ou une colonne) ALTER TABLE Livre RENAME TO Livre2; ALTER TABLE Livre RENAME COLUMN Titre TO Titre2; 125 Langage de Définition de Données • La commande DROP: Suppression d’une Table : permet de supprimer définitivement une table d’une base de données. Cela supprime en même temps les éventuels index, relations, contraintes et permissions associées a cette table. Syntaxe : DROP TABLE nom_table S’il y a une dépendance avec une autre table, il est recommande de les supprimer avant de supprimer la table. C’est le cas par exemple s’il y a des clés étrangères. Exemple : DROP TABLE Internaute 126 Langage de Manipulation des Données • Le LMD : • partie de SQL qui permet la manipulation et la mise à jour des tables. • Cette partie contient les commandes : • La commande SELECT • La commande UPDATE • La commande INSERT • La commande DELETE 127 Langage de Manipulation des Données • La commande SELECT: L’utilisation la plus courante de SELECT consiste a lire des données issues de la base de données. Cela s’effectue grâce a la commande SELECT, qui retourne des enregistrements dans un tableau de résultat. Cette commande peut sélectionner une ou plusieurs colonnes d’une table. Syntaxe : SELECT nom_du_champ FROM nom_du_tableau ; 128 Langage de Manipulation des Données • La commande SELECT: Exemple : SELECT ville FROM client ; Table Client 129 Langage de Manipulation des Données • La commande SELECT: Obtenir plusieurs colonnes : SELECT prenom, nom FROM client ; Table client 130 Langage de Manipulation des Données • La commande SELECT: Obtenir toutes les colonnes d’un tableau (*): SELECT * FROM client ; Table client 131 Langage de Manipulation des Données • La commande DISTINCT: Cette requête sélectionne le champ ≪ ma_colonne ≫ de la table ≪ nom_du_tableau ≫ en évitant de retourner des doublons (en évitant la redondance). Syntaxe: SELECT DISTINCT ma_colonne FROM nom_du_tableau ; 132 Langage de Manipulation des Données • La commande DISTINCT: Exemple : SELECT DISTINCT prenom FROM client ; Table client 133 Langage de Manipulation des Données • La commande WHERE: Permet d’extraire les lignes d’une base de données qui respectent une condition. Cela permet d’obtenir uniquement les informations désirées. Syntaxe: SELECT nom_colonnes FROM nom_table WHERE condition ; 134 Langage de Manipulation des Données • La commande WHERE: Opérateurs de comparaisons: Il existe plusieurs operateurs de comparaisons. La liste ci-jointe présente quelques uns : 135 Langage de Manipulation des Données • La commande AND et OR: Les operateurs sont à ajoutés dans la condition WHERE. Ils peuvent être combinés a l’infini pour filtrer les données comme souhaités. • AND : permet de s’assurer que la condition1 ET la condition2 sont vrai. SELECT nom_colonnes FROM nom_table WHERE condition1 AND condition2; • OR : vérifie quant a lui que la condition1 OU la condition2 est vrai. SELECT nom_colonnes FROM nom_table WHERE condition1 OR condition2; • Ces operateurs peuvent être combines a l’infini et mélangés. L’exemple ci- dessous filtre les résultats de la table ≪ nom_table ≫ si condition1 ET condition2 OU condition3 est vrai : SELECT nom_colonnes FROM nom_table WHERE condition1 AND (condition2 OR condition3); 136 Langage de Manipulation des Données • La commande IN: S’utilise avec la commande WHERE pour vérifier si une colonne est égale à une des valeurs comprise dans set de valeurs déterminés, c’est une méthode simple pour vérifier si une colonne est égale a une valeur OU une autre valeur OU une autre valeur et ainsi de suite, sans avoir a utiliser de multiple fois l’operateur OR Syntaxe: SELECT nom_colonne FROM table WHERE nom_colonne IN ( valeur1, valeur2, valeur3, ... ); 137 Langage de Manipulation des Données • La commande IN: Exemple : SELECT prenom FROM utilisateur WHERE prenom = 'Maurice' OR prenom = 'Marie' OR prenom = 'Thimote' ; SELECT prenom FROM utilisateur WHERE prenom IN ( 'Maurice', 'Marie', 'Thimote' ); 138 Langage de Manipulation des Données • La commande IN: Exemple : SELECT * FROM adresse WHERE addr_ville IN ( 'Paris', 'Graimbouville' ); Table adresse 139 Langage de Manipulation des Données • La commande IN: Exemple : (résultat de la requête) SELECT * FROM adresse WHERE addr_ville IN ( 'Paris', 'Graimbouville' ); Table adresse 140 Langage de Manipulation des Données • La commande BETWEEN: L’operateur BETWEEN est utlilisé pour sélectionner un intervalle de données dans une requête utilisant WHERE. L’intervalle peut être constitue de chaines de caractères, de nombres ou de dates. L’exemple le plus concret consiste par exemple a récupérer uniquement les enregistrements entre 2 dates définies. Syntaxe: SELECT * FROM table WHERE nom_colonne BETWEEN 'valeur1' AND 'valeur2' ; 141 Langage de Manipulation des Données • La commande BETWEEN: Exemple : SELECT * FROM utilisateur WHERE date_inscription BETWEEN ’2012-04-01′ AND ’2012-04-20′ ; Table utilisateur 142 Langage de Manipulation des Données • La commande GROUP BY: Est utilisée pour grouper plusieurs résultats et utiliser une fonction de totaux sur un groupe de résultat. Sur une table qui contient toutes les ventes d’un magasin, il est par exemple possible de liste regrouper les ventes par clients identiques et d’obtenir le coût total des achats pour chaque client. Syntaxe: SELECT colonne1, fonction(colonne2) FROM table GROUP BY colonne1 ; 143 Langage de Manipulation des Données • La commande GROUP BY: Exemple : SELECT client, SUM(tarif) FROM achat GROUP BY client ; Table adresse 144 Langage de Manipulation des Données • La commande GROUP BY: Utilisation d’autres fonctions de statistiques : • AVG() pour calculer la moyenne d’une colonne. Permet de connaitre le prix • • • • du panier moyen pour de chaque client COUNT() pour compter le nombre de lignes concernées. Permet de savoir combien d’achats a été effectue par chaque client MAX() pour récupérer la plus haute valeur. Pratique pour savoir l’achat le plus cher MIN() pour récupérer la plus petite valeur. Utile par exemple pour connaitre la date du premier achat d’un client SUM() pour calculer la somme de plusieurs lignes. Permet par exemple de connaitre le total de tous les achats d’un client 145 Langage de Manipulation des Données • La commande HAVING: La condition HAVING est presque similaire à WHERE à la seule différence que HAVING permet de filtrer en utilisant des fonctions telles que SUM(), COUNT(), AVG(), MIN() ou MAX(). Syntaxe: SELECT colonne1, fonction(colonne2) FROM nom_table GROUP BY colonne1 HAVING fonction(colonne2) operateur valeur ; 146 Langage de Manipulation des Données • La commande HAVING: Exemple : SELECT client, SUM(tarif) FROM achat GROUP BY client HAVING SUM(tarif) > 40 ; 147 Langage de Manipulation des Données • La commande ORDER BY : La commande ORDER BY permet de trier les lignes dans un résultat d’une requête. Syntaxe: SELECT colonne1, colonne2 FROM table ORDER BY colonne1 ; 148 Langage de Manipulation des Données • La commande ORDER BY : Exemple : SELECT id, nom, prenom FROM utilisateur ORDER BY nom ; 149 Langage de Manipulation des Données • La commande UPDATE: Cette commande permet d’effectuer des modifications sur des lignes existantes Syntaxe: UPDATE nom_table SET nom_colonne_1 = 'nouvelle valeur' WHERE condition ; 150 Langage de Manipulation des Données • La commande UPDATE: Exemple : UPDATE client SET rue = '49 Rue Ameline', ville = 'Saint-Eustache-la-Foret', code_postal = '76210' WHERE id = 2 ; 151 Langage de Manipulation des Données • La commande INSERT INTO : Insertion d’un tuple dans une table : permet au choix d’inclure une seule ligne a la base existante ou plusieurs lignes d’un coup. Syntaxe : INSERT INTO table VALUES ('valeur 1', 'valeur 2', ...) ; Exemple : INSERT INTO Livre VALUES (‘HUGO’, ‘HERNANT’, 1830, ‘THEATRE’, 120.00); 152 Langage de Manipulation des Données • La commande INSERT INTO : Insertion d’un tuple dans une table : Exemple : INSERT INTO Livre VALUES (‘HUGO’, ‘HERNANT’, 1830, ‘THEATRE’, 120.00); Insère un nouveau tuple en utilisant un ordre différent de l’ordre de définition. INSERT INTO Livre (Auteur, Titre, Année, Prix, Genre) VALUES (‘Balzac, ‘Le père Goriot’, 1834, 148.5, ‘Roman’); Insère un nouveau tuple sans initialiser une colonne (colonne prix). INSERT INTO Livre (Auteur, Titre, Année, Genre) VALUES (‘Balzac, ‘Le père Goriot’, 1834, ‘Roman’); 153 Langage de Manipulation des Données • La commande DELETE: La commande DELETE en SQL permet de supprimer des lignes dans une table. En utilisant cette commande associe a WHERE il est possible de sélectionner les lignes concernées qui seront supprimées. Syntaxe: DELETE FROM table WHERE condition ; 154 Langage de Manipulation des Données • La commande DELETE: Exemple : . DELETE FROM utilisateur WHERE date_inscription < '2012-04-10' ; Table utilisateur 155 CHAP. 5 (SUITE): LANGAGE SQL Pr. : Mohamed BASLAM Niveau : S4 BCG Année : 2015/2016 156 Plan • Langage de Manipulation des Données (suite) • Les Jointures entre tables • Introduction • Modélisation d’une relation • Qu’est ce qu’une jointure? • Les Jointures Internes • Les Jointures Externes 157 Langage de Manipulation des Données • La commande CASE: La commande ≪ CASE … WHEN … ≫ permet d’utiliser des conditions de type ≪ si / sinon ≫ (cf. if / else) similaire à un langage de programmation pour retourner un résultat disponible entre plusieurs possibilités Syntaxe: CASE WHEN a=b THEN 'A egal a B' WHEN a>b THEN 'A superieur a B' ELSE 'A inferieur a B' END 158 Langage de Manipulation des Données • La commande CASE : Exemple : . SELECT id, nom, surcharge, prix_unitaire, quantite, CASE WHEN surcharge =1 THEN 'Prix ordinaire' WHEN surcharge >1 THEN 'Prix superieur a la normale' ELSE 'Prix inferieur a la normale' END FROM achat; Table achat 159 Langage de Manipulation des Données • La commande CASE : Résultat de : . SELECT id, nom, surcharge, prix_unitaire, quantite, CASE WHEN surcharge =1 THEN 'Prix ordinaire' WHEN surcharge >1 THEN 'Prix superieur a la normale' ELSE 'Prix inferieur a la normale' END FROM achat ; Table achat 160 Langage de Manipulation des Données • La commande CASE : UPDATE avec CASE : Le CASE peut être utilisé dans n’importe quelle instruction ou clause, telle que SELECT, UPDATE, DELETE, WHERE, ORDER BY ou HAVING. UPDATE achat SET quantite = ( CASE WHEN surcharge < 1 THEN quantite + 1 WHEN surcharge > 1 THEN quantite - 1 ELSE quantite END ); 161 Langage de Manipulation des Données • La commande CASE : Question : Réfléchissez au résultat de cette requête UPDATE achat SET quantite = ( CASE WHEN surcharge < 1 THEN quantite + 1 WHEN surcharge > 1 THEN quantite - 1 ELSE quantite Table achat END ); 162 Langage de Manipulation des Données • La commande UNION: Elle permet de concaténer les résultats de 2 requêtes ou plus. Pour l’utiliser il est nécessaire que chacune des requêtes à concaténer retournes le même nombre de colonnes, avec les mêmes types de données et dans le même ordre. Syntaxe: SELECT * FROM table1 UNION SELECT * FROM table2; Union de 2 ensembles : A = Résultat de la première requête B = Résultat de la deuxième requête 163 Langage de Manipulation des Données • La commande UNION: Exemple : Table magasin1_client Table magasin2_client SELECT * FROM magasin1_client UNION SELECT * FROM magasin2_client; 164 Langage de Manipulation des Données • La commande UNION: Resultat de : SELECT * FROM magasin1_client UNION SELECT * FROM magasin2_client; Union des deux tables : magasin1_client et table magasin2_client 165 Langage de Manipulation des Données • La commande INTERSECT Elle permet donc de récupérer les enregistrements communs a 2 requêtes. Cela peut être utile lorsqu’il faut trouver s’il y a des données similaires sur 2 tables distinctes. Syntaxe: SELECT * FROM table1 INTERSECT SELECT * FROM table2; Intersection de 2 ensembles : A = Résultat de la première requête B = Résultat de la deuxième requête 166 Langage de Manipulation des Données • La commande INTERSECT : Exemple : Table magasin1_client Table magasin2_client SELECT * FROM magasin1_client INTERSECT SELECT * FROM magasin2_client; 167 Langage de Manipulation des Données • La commande INTERSECT : Resultat de : SELECT * FROM magasin1_client INTERSECT SELECT * FROM magasin2_client; Intersection des deux tables : magasin1_client et table magasin2_client 168 Langage de Manipulation des Données • La commande EXCEPT : Elle s’utilise entre 2 instructions pour récupérer les enregistrements de la première instruction sans inclure les résultats de la seconde requête. Syntaxe: SELECT * FROM table1 EXCEPT SELECT * FROM table2 ; Exception entre 2 ensembles : A = Résultat de la première requête B = Résultat de la deuxième requête 169 Langage de Manipulation des Données • La commande EXCEPT : Exemple : Table magasin1_client Table magasin2_client SELECT * FROM magasin1_client EXCEPT SELECT * FROM magasin2_client; 170 Langage de Manipulation des Données • La commande EXCEPT : Resultat de : SELECT * FROM magasin1_client EXCEPT SELECT * FROM magasin2_client; Exception entre deux tables : magasin1_client et table magasin2_client 171 Les Jointures entre tables Introduction • Une Base de donnée est constituée d’un ensemble des tables. Nécessité d’introduire une communication entre les tables, une relation entre les tables. • Pour le moment, nous n'avons travaillé que sur une seule table à la fois. Dans la pratique, vous aurez certainement plusieurs tables dans votre base, dont la plupart seront interconnectée. Tout ça permet de mieux découper vos informations, d'éviter des répétitions et de rendre ainsi vos données plus faciles à gérer. 172 Les Jointures entre tables introduction • Par exemple la table OUVRAGES que nous avons vu dans le Chap-3 OUVRAGES Cote Titre NbExem Année Thème Titre NbExem NomAuteur PreAuteur NomAuteur PreAuteur AUTEUR OUVRAGES Cote Editeur Année Thème #NumAut ECRIT #Cote #NumAut NumAut 173 Les Jointures entre tables Modélisation d’une relation • Si je voulais stocker les Nom, Prénom et numéro de telephone de chaque propriétaire de jeux vidéo dans notre table jeux_video, il n'y aurait pas d'autre solution que de dupliquer ces informations sur chaque ligne… Cependant ce serait bien trop répétitif, regardez ce que ça donnerait sur le tableau suivante : Jeux_video Id Nom Prenom_Prop. Nom_Prop. telephone prix Nbre_joueur _max commentaire 1 Super Mario Ahmed Farid 0144772133 4 1 Jeu d’anthologie! 2 Sonic Younes Elalami 0322174122 2 1 Meilleur jeu 3 Zelda Ahmed Farid 0144772133 15 1 Jeu comlet 4 Mario Ahmed Farid 0144772133 25 4 Excellent jeu 5 Super Smash Youssef Omari 0411780200 55 4 Jeu délirant 174 Les Jointures entre tables Modélisation d’une relation • Ce que je vous propose, c'est de créer une autre table, que l'on nommera par exemple propriétaires, qui centralisera les informations des propriétaires des jeux (tableau suivante). Propriétaires Id Prenom Nom tel 1 Ahmed Farid 0144772133 2 Younes Elalami 0322174122 3 Youssef Omari 0411780200 • Cette table liste tous les propriétaires de jeux connus et attribue à chacun un ID. Les propriétaires n'apparaissant qu'une seule fois, il n'y a pas de redondance. 175 Les Jointures entre tables Modélisation d’une relation • Maintenant, il faut modifier la structure de la table jeux_video pour faire référence aux propriétaires. Pour cela, le mieux est de créer un champ ID_proprietaire qui indique le numéro du propriétaire dans l'autre table (tableau suivante). Jeux_video Id Nom #ID_proprietaire prix Nbre_joueur_ max commentaire 1 Super Mario 1 4 1 Jeu d’anthologie! 2 Sonic 2 2 1 Meilleur jeu 3 Zelda 1 15 1 Jeu comlet 4 Mario 1 25 4 Excellent jeu 5 Super Smash 3 55 4 Jeu délirant • Le nouveau champ ID_proprietaire est de type INT. Il permet de faire référence à une entrée précise de la table proprietaires 176 Les Jointures entre tables Modélisation d’une relation • On peut maintenant considérer que les tables sont reliées à travers ces ID de propriétaires, comme le suggère la figure suivante. Propriétaires Id Prenom Nom tel 1 Ahmed Farid 0144772133 2 Younes Elalami 0322174122 3 Youssef Omari 0411780200 Jeux_video Id Nom ID_proprietaire prix Nbre_joueur_ max commentaire 1 Super Mario 1 4 1 Jeu d’anthologie! 2 Sonic 2 2 1 Meilleur jeu 3 Zelda 1 15 1 Jeu comlet 4 Mario 1 25 4 Excellent jeu 5 Super Smash 3 55 4 Jeu délirant 177 Les Jointures entre tables Modélisation d’une relation • SQL sait donc que l'ID_proprietaire no 1 dans la table jeux_video correspond à Ahmed? • Non, il ne le sait pas. Il ne voit que des nombres et il ne fait pas la relation entre les deux tables. Il va falloir lui expliquer cette relation dans une requête SQL : on va faire ce qu'on appelle une jointure entre les deux tables 178 Les Jointures entre tables Qu'est-ce qu'une jointure ? • Lorsqu'on récupère la liste des jeux, si on souhaite obtenir le nom du propriétaire, il va falloir adapter la requête pour récupérer aussi les informations issues de la table proprietaires. Pour cela, on doit faire ce qu'on appelle une jointure. • Les jointure : permettent d’associer plusieurs tables dans une même requête. • Deux types de jointures : • les jointures internes : elles ne sélectionnent que les données qui ont une correspondance entre les deux tables ; • les jointures externes : elles sélectionnent toutes les données, même si certaines n'ont pas de correspondance dans l'autre table 179 Les Jointures entre tables Qu'est-ce qu'une jointure ? • Exemple : Propriétaires Id Prenom Nom tel 1 Ahmed Farid 0144772133 2 Younes Elalami 0322174122 3 Youssef Omari 0411780200 4 Laila Anas 0588123569 • Imaginons que nous ayons une 4e personne dans la table des propriétaires (Laila Anas) et que cette personne n’apparait pas dans la table jeux_video : Si vous récupérez les données des deux tables à l'aide : • Jointure Interne : Laila Anas n'apparaîtra pas dans les résultats de la requête. La jointure interne force les données d'une table à avoir une correspondance dans l'autre. • Jointure externe : vous aurez toutes les données de la table des propriétaires, même s'il n'y a pas de correspondance dans l'autre table des jeux vidéo ; donc Laila Anas, qui pourtant ne possède aucun jeu vidéo, apparaîtra. 180 Les Jointures entre tables Les jointures internes • Une jointure interne peut être effectuée de deux façons différentes : • à l'aide du mot-clé WHERE : c'est l'ancienne syntaxe, toujours utilisée aujourd'hui, qu'il faut donc connaître mais que vous devriez éviter d'utiliser si vous avez le choix ; SELECT nom, prenom FROM proprietaires, jeux_video ; • Problème : champ nom est dans les deux tables, donc ça ne fonctionnera pas car ce n'est pas claire si on veut le nom de la personne ou du jeu . • Solution : marquer le nom de la table devant le nom du champ: SELECT jeux_video.nom, proprietaires.prenom FROM proprietaires, jeux_video ; 181 Les Jointures entre tables Les jointures internes • Bonne solution, mais les tables ne sont pas encore liées, En effet, les jeux et leurs propriétaires ont une correspondance via le champ ID_proprietaire (de la table jeux_video) et le champ ID (de la table proprietaires). On va indiquer cette liaison dans un WHERE, comme ceci : SELECT jeux_video.nom, proprietaires.prenom FROM proprietaires, jeux_video WHERE jeux_video.ID_proprietaire = proprietaires.ID ; 182 Les Jointures entre tables Les jointures externes • Les jointures externes permettent de récupérer toutes les données, même celles qui n'ont pas de correspondance. On pourra ainsi obtenir Laila Anas dans la liste même s'il ne possède pas de jeu vidéo • La syntaxe disponible est à base de JOIN. Il y a deux écritures à connaître : LEFT JOIN et RIGHT JOIN. Cela revient pratiquement au même, avec une subtile différence que nous allons voir 183 Les Jointures entre tables Les jointures externes • LEFT JOIN : récupérer toute la table de gauche : SELECT jeux_video.nom as nom_jeu, proprietaires.prenom as prenom_prop FROM proprietaires LEFT JOIN jeux_video ON jeux_video.ID_proprietaire = proprietaires.ID ; • proprietaires est appelée la « table de gauche » et jeux_video la « table de droite ». Le LEFT JOIN demande à récupérer tout le contenu de la table de gauche, donc tous les propriétaires, même si ces derniers n'ont pas d'équivalence dans la table jeux_video. 184 Les Jointures entre tables Les jointures externes • LEFT JOIN : récupérer toute la table de gauche : SELECT jeux_video.nom, proprietaires.prenom FROM proprietaires LEFT JOIN jeux_video ON jeux_video.ID_proprietaire = proprietaires.ID ; • Résultat de cette requête nom (Nom_jeu) prenom (prenom_prop) Super Mario Ahmed Sonic Younes Zelda Ahmed Mario Ahmed Super Smash Youssef NULL Laila 185 Les Jointures entre tables Les jointures externes • RIGHT JOIN : récupérer toute la table de droite : SELECT jeux_video.nom as nom_jeu, proprietaires.prenom as prenom_prop FROM proprietaires RIGHT JOIN jeux_video ON jeux_video.ID_proprietaire = proprietaires.ID ; • La table de droite est « jeux_video ». On récupèrerait donc tous les jeux, même ceux qui n'ont pas de propriétaire associé. 186 Fin de cours de Bases de données