introduction aux sgbd

Cours de Bases de Données

Christophe Vaudrymél : [email protected] blog : thegeekintheshell.blogspot.comsite Web : cvaudry.developpez.com

Introduction aux Bases de Données


Introduction aux Bases de DonnéesPlan de cours

IntroductionObjectifs du coursNotion de BDDUtilité des SGBD

Théorie des BDDConcepts de baseModèle E/AModèle relationnelConception des BDD

Pratique des BDDLangage SQLMySQL

− Présentation− Ce cours est une introduction aux bases de données dispensées à des étudiants d'IUT SRC en 2002-2004− La mise en pratique des concepts s'appuie sur MySQL


− Contenu et objectifs :− connaître le vocabulaire et les concepts essentiels des bases de données− savoir concevoir une base de données

− Connaître et savoir utiliser le modèle Entité-Association-Propriété− Connaître le modèle relationnel− Savoir passer d’un modèle E/A à un modèle relationnel

− connaître le langage SQL− être capable de réaliser de petites bases de données.

Objectifs de ce coursPlan de cours





− Dia− Logiciel de dessin vectoriel qui comprend des objets spéciaux pour dessiner les diagrammes Entité-Association

• http://live.gnome.org/Dia− Il faut noter qu'il existe une version « portable » qui peut être installé et lancé depuis une clé USB

• http://www.framakey.org/Portables/PortableDIA− Database Design Tool

− DDT est un outil permettant de dessiner des schémas de bases de données. Il permet de générer des fichiers SQL à partir des schémas et également d’importer des scripts SQL pour les convertir en schéma.

• http://gnuwin.epfl.ch/apps/DDT/fr/index.html• http://www.visi.com/~jjanssens/

− Les outils “ToadSoft”− Toad for MySQL

• http://www.toadsoft.com/toadmysql/Overview.htm− Toad Data Modeler

• http://www.toadsoft.com/toaddm/toad_data_modeler.htm

Quelques outils (1)Plan de cours




http://live.gnome.org/Dia

http://www.framakey.org/Portables/PortableDIA

http://gnuwin.epfl.ch/apps/DDT/fr/index.html

http://www.visi.com/~jjanssens/

http://www.toadsoft.com/toadmysql/Overview.htm

http://www.toadsoft.com/toaddm/toad_data_modeler.htm


− DBDesigner• Pour une description du logiciel : http://www.framasoft.net/article1911.html• Site officiel : http://fabforce.net/dbdesigner4/• Version portable : http://www.framakey.org/Portables/PortableDBDesigner4

− MySQL − Le SGBD utilisé dans la partie pratique de cette introduction.− Le logiciel avec PHPMyAdmin et PHP : http://www.easyphp.org/ − site officiel : http://www.mysql.com/− Distribution installable et utilisable depuis une clé USB

• Mov'AMP : http://www.movamp.net/• Portable ZMNS : http://www.framakey.org/Portables/PortableZMWS

Quelques outils (2)Plan de cours




http://www.framasoft.net/article1911.html

http://fabforce.net/dbdesigner4/

http://www.framakey.org/Portables/PortableDBDesigner4

http://www.easyphp.org/

http://www.mysql.com/

http://www.movamp.net/

http://www.framakey.org/Portables/PortableZMWS


− Bases de données− “Bases de données” de Georges Gardarin chez Eyrolles− “Conception des systèmes d’information” de Pascal André et Alain Vailly, chez Ellipses, collection TechnoSup.

− SQL / MySQL− "MySQL" de Pascal Dubois, chez CampusPress.− "Pratique de MySQL et PHP" , de Philippe Rigaux chez O’Reilly (voir http://www.oreilly.fr/catalogue/2841772373.html et http://oreilly.free.fr/mysqlphp/)− "La programmation SQL" par Cushman et Ramona Mata-Doledo, chez Schaum.− "PHP/ MySQL tome 1 : Première application avec PHP4 et MySQL". J.-P. Leboeuf, Eyrolles, collection «Les Cahiers du Programmeur »− "PHP/ MySQL tome 2 : Ateliers Web professionnels avec PHP/MySQL et JavaScript". P. Chaléat et D. Charnay, Eyrolles, collection «Les Cahiers du Programmeur »

Bibliographie Plan de cours





− Ressources sur le Web :− Cours en ligne sur l’altruiste : http://www.laltruiste.com/document.php?compteur=1&page=1&rep=18− Des liens vers des ressources en ligne sur les SGBD, SQL, MySQL, etc. : http://sgbd.developpez.com/cours/

WebographiePlan de cours





− Une base de données (BDD ou BD)− C'est un ensemble cohérent et structuré d'informations concernant un domaine particulier, interrogeables par requêtes, mémorisées sur un support informatique

Qu’est-ce qu’une base de données ? Plan de cours





− Une base de données (BDD ou BD)−C'est un ensemble cohérent et structuré d'informations concernant un domaine particulier, interrogeables par requêtes, mémorisées sur un support informatique

− Cohérent et structuré− Les données sont caractérisées par des propriétés et des relations (i.e. des liens) entre elles. − Ces données concernent un domaine particulier

− Les données modélisent une partie du « monde réel »− On retrouve dans le modèle de données, les concepts du domaine

− Si on fait une BDD pour un centre de documentation on retrouvera les concepts de livres, d'auteurs, de titres, etc.

Cohérent et structuré Plan de cours






− Interrogeable par requêtes− On doit pouvoir retrouver les données qui satisfont certains critères

− Par exemple on souhaite trouver tous les livres de Umberto Eco dans le fond d'un centre documentaire.− Ces interrogations doivent pouvoir être effectuées par rapport à n'importe quel critère.

− On doit par ailleurs pouvoir retrouver la structure des données

− Par exemple un livre possède un titre, un auteur, un éditeur, ...

Interrogeable par requêtes Plan de cours






− Support informatique− Représentation des données et des relations entre les données sous une forme « numérique », interprétable et manipulable par un ordinateur.

− La Base de Données est une partie de la science informatique− Les données sont stockées sur les mémoires secondaires (Disque dur, CDRom, etc.) et on y accède grâce à des logiciels.

Support informatique Plan de cours





− On souhaite créer une base de données pour un centre de documentation.− Quel est le domaine concerné ?

Exemple Plan de cours






− Un centre de documentation avec ce qu'il contient c'est-à-dire des livres, des revues, des magazines, des documents divers, etc.

− A qui et à quoi sert cette BDD ?








− A qui et à quoi sert cette BDD ?− Doit-elle être accessible juste aux documentalistes ou bien également aux utilisateurs ? − Doit-elle servir pour gérer les prêts de livres (ce qui implique de tenir compte des utilisateurs) ?

− Quels sont les concepts du domaine concernés ?








− A qui et à quoi sert cette BDD ?− Doit-elle être accessible juste aux documentalistes ou bien également aux utilisateurs ? − Doit-elle servir pour gérer les prêts de livres (ce qui implique de tenir compte des utilisateurs) ?

− Quels sont les concepts du domaine concernés ?− Il faut définir différentes entités informatiques correspondant aux livres, magazines, revues, utilisateurs, etc.− Il faut définir les relations entre ces différentes entités.

− Par exemple un livre est emprunté par un utilisateur






− Construire une base de données sur un domaine c'est avant tout construire un modèle

− Modèle d'un domaine = représentation abstraite de ce domaine− Abstraction signifie éloignement vis-à-vis des considérations matérielles et logicielles

− Pourquoi modéliser ?− Pour construire une représentation du domaine qui soit manipulable et interprétable par un ordinateur− La modélisation est une simplification de la réalité − Une modélisation relève toujours d'un point de vue ou d'un cadrage.

− On ne garde que les éléments pertinents par rapport à ce point de vue − Aussi complète soit-elle, une modélisation est nécessairement « limitée ».

Construire une BDD – notion de modèlePlan de cours





“Les sciences n’essaient pas d’expliquer, c’est tout juste si elles tentent d’interpréter ; elles font essentiellement des modèles. Par modèle, on entend une construction mathématique qui, à l’aide de certaines interprétations verbales, décrit les phénomènes observés. La justification d’une telle construction mathématique réside uniquement et précisément dans le fait qu’elle est censée fonctionner”(John von Neumann)Qu’en pensez-vous ?

Construire une BDD – notion de modèlePlan de cours





John von Neumann (1903-1957) était un mathématicien américain d'origine hongroise ayant apporté d'importantes contributions autant en physique quantique, qu'en théorie des ensembles, en informatique, en sciences économiques et encore dans beaucoup d'autres domaines.

Il est le père de la théorie des jeux et a publié Book games and economic behavior avec Oskar Morgenstern en 1944. Von Neumann a conçu l'architecture de von Neumann utilisée dans tous les ordinateurs modernes et a étudié les automates cellulaires afin de construire les premiers exemples d'automates auto-reproductibles (cf le jeu de la vie ).

Il est l'un des personnages clef de l'histoire de l'informatique, il peut être considéré comme le chef de file de l'époque des grands calculateurs.

Source : Wikipédia (http://fr.wikipedia.org/wiki/John_von_Neumann)

Aparté – Pour votre culture personnellePlan de cours





− Représenter implique de − choisir les objets, abstraits ou concrets, que l'on va décrire.− nommer ces objets pour « pouvoir en parler »

− Un modèle est avant tout un outil de communication

− définir clairement − Pourquoi on choisit de décrire ces « objets »− Les propriétés importantes de ces objets

− Un modèle doit pouvoir se justifier par rapport à des choix effectués par le concepteur, le client, etc.

Construire une BDD – choisir, nommer, définirPlan de cours





− Un modèle est : − une interprétation explicite par son utilisateur de la compréhension d’une situation ou plus exactement de l’idée que cet utilisateur se fait de la situation.

− Pour simplifier un modèle en informatique est : − une description d’entités et de relations entre ces entités− synonyme : représentation

− Modéliser, en quoi cela consiste ?− C’est d’une certaine manière élaborer une vue partielle, plus ou moins abstraite de l’existant

−Principe de pertinence : supprimer des détails et ne garder que ce qui est important par rapport à l’objectif de la BDD

− On fait un modèle dans un but précis, et on ne garde dans ce modèle que ce qui est pertinent par rapport à ce but.

− Un modèle est correct s’il permet de répondre aux questions qu’on se pose.

Concept de modèle – une petite synthèsePlan de cours





− La modélisation en informatique : − c’est le passage du domaine du problème à celui de sa solution informatique.

− La difficulté de modéliser− Il est difficile d’appréhender la sémantique du monde réel et de la transformer en signes manipulables par des ordinateurs

Concept de modèle – la difficulté de modéliserPlan de cours





− Qu’est-ce qu’une BDD ? − C’est un ensemble cohérent et structuré d’informations concernant un domaine particulier, interrogeables par requêtes, mémorisées sur un support informatique.

− Par quoi sont caractérisées les données ?− par des propriétés− et par des relations (i.e. des liens) entre elles.

− Que retrouve-t-on dans le modèle de données ?− les concepts du domaine

− Qu’est-ce que le modèle d’un domaine ?− la représentation abstraite de ce domaine

− Que signifie abstraction dans ce contexte ?− éloignement vis-à-vis des considérations matérielles et logicielles

− Pourquoi modéliser ?− pour construire une représentation du domaine qui soit manipulable et interprétable par un ordinateur.

Quizz – Qu’avez-vous retenu de ce qui précède ?Plan de cours





− Représenter implique au moins 3 actions : pouvez-vous les citer ?

− Choisir ce que l’on va décrire− Nommer ce que l’on décrit pour pouvoir en parler− Définir les propriétés pertinentes de ce que l’on décrit

− En quoi consiste l’action de modéliser ?− élaborer une vue partielle plus ou moins abstraite de l’existant

− Quel est la principale difficulté de la modélisation informatique ?

− Il est difficile d’appréhender la sémantique du monde réel et de la transformer en signes manipulables par des ordinateurs.






− Qu’est-ce qu’un SGBD ? − Un SGBD est un ensemble de logiciels qui permet de créer, de gérer et d'interroger efficacement une base de données indépendamment du domaine d'application.− Exemples :

− Access de Microsoft™− Oracle http://www.oracle.com/− MySQL http://www.mysql.com/− PostgreSQL http://www.postgresql.org/

Système de Gestion de Bases de Données (SGBD)Plan de cours





− En première approximation un SGBD se compose de 3 couches

− Le SGF : Systèmes de Gestion de Fichiers− Le SGBD interne− Le SGBD externe

Les 3 couches d’un SGBDPlan de cours



Pratique des BDDLangage SQLMySQL SGF

SGBD interne

SGBD externe

Première vue d'un SGBD


− Le Système de gestion de fichiers− Gestion des données sur les mémoires secondaires (disque dur, bande magnétique, etc.)

Les 3 couches d’un SGBD – le SGFPlan de cours



Pratique des BDDLangage SQLMySQL SGF

SGBD interne

SGBD externe


− Le SGBD Interne− Il comprend le système d'accès aux données. Il est quasiment invisible pour l'utilisateur, depuis les programmes d'application.− Il gère les données dans les fichiers− Il s'occupe du placement et de l'assemblage des données− Il gère les liens entre les données et la structure de la base

Les 3 couches d’un SGBD – le SGBD internePlan de cours




SGF

SGBD interne

SGBD externe


− Le SGBD externe assure − l'analyse et l'interprétation des requêtes des utilisateurs.− la mise en forme des données résultats.

Les 3 couches d’un SGBD – le SGBD externePlan de cours




SGF

SGBD interne

SGBD externe


− Les principaux objectifs des SGBD sont :− Indépendance physique des données− Indépendance logique− Manipulation des données par des non-informaticiens− Efficacité des accès aux données − Administration cohérente des données − Non redondance des données− Cohérence des données− Partageabilité des données − Sécurité de données

− Il faut noter que les SGBD, même récents, n'atteignent que partiellement l'ensemble de ses objectifs

Qualités attendues des SGBDPlan de cours





− Séparation entre le monde réel et le monde informatique− La représentation physique des informations au niveau des mémoires secondaires informatiques doit être indépendante de la représentation des informations dans le monde réel

− Dans le monde réel, les informations sont organisées d'une certaine manière.− Pour des raisons d'efficacité d'accès et de manipulation, les informations ne sont pas organisés sur les mémoires secondaires de la même manière qu'elles sont organisées dans le monde réel.

Qualités des SGBD - Indépendance physique (1) Plan de cours





− Exemple− Dans votre bibliothèque les livres sont rangées sur les étagères en fonction de leur taille, et/ou de leur thème, et/ou de leur auteur− L'organisation des données représentant les livres de votre bibliothèque dans votre base de données sur votre disque dur ne suivra pas ce mode de rangement mais suivra une organisation qui facilitera la recherche d'un titre et optimisera la place requise par votre fichier.

Qualités des SGBD - Indépendance physique (2) Plan de cours





− Toutes les informations n'ont pas la même importance selon l'utilisateur− Tous les utilisateurs n'ont pas les mêmes droits sur les informations − Par exemple

− la base de données d'un centre de documentation est accessible :

− par le documentaliste pour mettre à jour le contenu du fond documentaire et gérer les prêts.

− Le documentaliste peut ainsi ajouter ou supprimer des livres présents dans la base, avoir accès à l'ensemble des usagers ainsi que les informations les concernant.

− par un usager classique, par exemple un étudiant.− Pour lui, ce qui prime est de pouvoir consulter le contenu du fond. Il n’a pas besoin de pouvoir modifier la base

Qualités des SGBD - Indépendance logique Plan de cours





− Notion de vue− Chaque type d'utilisateur à une certaine « vue » des données de la base−Il est important qu'il y ait une indépendance entre cette « vue » propre à un utilisateur ou un groupe d'utilisateur et les données elle-même.

− C'est ce qu'on nomme l'indépendance logique.− Intérêt

− Chaque groupe d'utilisateur de la base a une vue sur les données correspondant à ces besoins− On peut faire évoluer les vues existantes ou créer de nouvelles vues sans modifier la structure des données dans la base et vice versa.

Qualités des SGBD - Indépendance logique et vue Plan de cours





− Critère de performance dans l'accès et le stockage des données

− Point de vue « utilisateur » du SGBD − Les bases de données doivent être « efficaces »− On utilise un SGBD pour gagner du temps. Si une recherche manuelle est plus rapide, le SGBD perd de son intérêt !− Il faut notamment des accès aux mémoires secondaires efficaces

− Point de vue « programmeur » − un SGBD doit pouvoir s'utiliser facilement avec un langage de programmation « classique »− Dans de nombreuses applications on peut avoir besoin d'accéder par programme à une base de données

− par exemple, les langages PHP ou ASP couplés avec une base de données dans la création d'un site Web dynamique.

Qualités des SGBD – Efficacité des accès Plan de cours





− Manipulation des données et de la base par des non-informaticiens

− Les utilisateurs des BDD ne sont pas nécessairement des informaticiens

− Par exemple : bibliothécaires, documentalistes, étudiants, etc.

− Il faut donc que le SGBD permette aux usagers d'accéder, de manipuler, de modifier, de mettre à jour les informations de la base facilement, sans une connaissance approfondie de la manière dont la base est « implémentée » sur la machine

− Utilisation de langage de requête « simple », non-procéduraux comme QBE ou SQL. − Utilisation de requêtes effectuées « graphiquement »

− « Manipulation directe » des données

Qualités des SGBD – Manipulation des données Plan de cours





− Deux fonctions essentielles liés à un SGBD− Définition des structures de stockage et des structures de données− Suivi de l'évolutions des données

− On parle d'administration des données− Liés à ces 2 fonctions, plusieurs points à assurer

− Contrôle efficace des données− Arbitrage des conflits entre des points de vue sur les données non nécessairement cohérents− Optimisation de l'accès aux données

− D'où une administration des données− Centralisée, régit par un groupe limitée de personnes hautement qualifiées : le(s) DBA (Data Base Administrator).

Qualités des SGBD – Administration des données Plan de cours





− La non-redondance est liée à une administration cohérente des données

− Il faut éviter la duplication « physique » des données− C'est-à-dire à la répétition en mémoire des mêmes informations

− La redondance d'information entraîne− De la perte de mémoire secondaire− La nécessité de faire des mises à jour multiples− Des incohérences dans la base de données

− La redondance d'information peut entraîner− Des incohérences dans la base et donc des incohérences dans les réponses aux requêtes− Des problèmes d'optimisation dans l'accès aux informations

Qualités des SGBD – Non-redondance Plan de cours





− Respects des contraintes de définition des données− La vérification de la non-redondance des informations (transparent précédent) permet d'assurer une cohérence « structurelle » de la base

− Les informations ne sont pas dupliquées et la structure de la base respecte certaines règles facilitant son administration.

− Il s'agit ici de vérifier la cohérence « sémantique » des informations, c'est-à-dire la cohérence de leur valeur.

− Chaque donnée stockée dans la base est définie par un certain nombre de propriétés elle-mêmes associées à des valeurs. − Ces valeurs ont un « domaine de définition », c'est-à-dire qu'elle respecte certaines contraintes

− Un age ne peut pas être un entier négatif− Le code postal d'une ville française ou le numéro de sécurité sociale respecte un certains nombres de contraintes.

Qualités des SGBD – Cohérence Plan de cours





− « Partagéabilité » des données− Partage des données simultanément/concurremment

− Problématique de la cohérence de la base, mise à jour en même temps, etc.

− Exemple courant : réservation d'une chambre d'hotel électroniquement.− Actuellement : base de données sur le Web et accès multiples sur cette base.

− Partage des données à des moments différents− Optimisation de la place mémoire utilisée− Problématique de cohérence des données, de non-duplication.− Il est important de savoir que plusieurs applications différentes peuvent modifier les mêmes données.

Qualités des SGBD – Partage des données Plan de cours





− Il est important de pouvoir protéger les données− Certaines données peuvent être « sensibles » et doivent n'être accessibles qu'à une certaine catégorie d'utilisateurs.

− Il faut se prémunir contre les accès non autorisés et/ou mal intentionnés

− C'est aussi un moyen de limiter les modifications accidentelles des données par des utilisateurs non avertis

− Il est donc nécessaire d'avoir une politique de sécurité− Problématique similaire à celle des systèmes d'exploitation− Présence de mécanismes pour autoriser, contrôler ou enlever les droits d'accès de n'importe quel usager (en général gérer par le(s) DBA)

Qualités des SGBD – Sécurité des données Plan de cours





− Qu’est-ce qu’un SGBD ?− Un SGBD est un ensemble de logiciels qui permet de créer de gérer et d’interroger efficacement une BDD indépendamment du domaine d’application

− Voyez-vous la différence entre une BDD et un SGBD ?− la BDD est l’ensemble structurée d’informations, le SGBD est l’ensemble des outils logiciels pour exploiter et gérer un ensemble d’information structurée

− Combien de couches composent un SGBD et comment se nomment-elles ?

− Il y en a 3 : le SGF, le SGBD interne et le SGBD externe− A quoi sert le SGF ?

− Il sert à la gestion des données sur les mémoires secondaires.

Quizz – qu’avez-vous retenu de ce qui précède ? Plan de cours





− Quels sont les principales qualités attendues d’un SGBD ?− Indépendance physique des données− Indépendance logique− Manipulation des données par des non-informaticiens− Efficacité des accès− Administration cohérente des données− Non-redondance des données− Cohérence des données− Partagéabilité des données − Sécurité des données

− A quoi correspond l’indépendance physique des données ?− La représentation physique des informations au niveau des mémoires secondaires doit être indépendantes de la représentation des informations dans le monde réel.

− A quoi correspond l’indépendance logique ?− Chaque utilisateur a une certaine “vue” des données de la base : l’indépendance logique correspond à l’indépendance entre les vues des différents utilisateurs.






− Quel est l’intérêt de l’indépendance logique et des vues ? − Tous les utilisateurs n’ont pas les mêmes besoins (et les mêmes droits) concernant les informations. Ainsi, chaque utilisateur a une vue qui correspond à ces besoins et à ces droits. Cela facilite la création de nouvelles vues et l’évolution des vues existantes sans modifier la base (et vice-versa).






− Principe− Toute description de données consiste à définir les caractéristiques d'ensembles d'objets modélisés dans la base de données.

− On modélise des ensembles d'objets - des « classes » d'objets - et non juste des objets particuliers.− Exemple : Pour la base de données d'un centre de documentation on modélise la classe des objets « livres » et non chaque livre en particulier

− Les objets particuliers sont définis par les programmes d'applications lorsque les utilisateurs insèrent ou mettent à jour des données.

− Les objets particuliers doivent alors vérifier les propriétés de l'ensemble auquel ils appartiennent.

Description des données - principe Plan de cours





− Rappel sur les ensembles− Intuitivement un ensemble est une collection d'objets

− On désigne souvent ces objets comme les éléments de l'ensemble− En général, un ensemble est formé d'éléments qui possèdent certaines propriétés communes.

− C'est notamment le cas des ensembles que nous définirons dans le cadre des Bases de Données

− En informatique pour désigner un ensemble on utilise également souvent le terme de « classe ».

Description des données – les ensembles (1)Plan de cours





Description des données – les ensembles (2)Plan de cours




x xx X

xx X

ZB

C

A

Y

Classe lettres« X » bleus

Classe des lettres Majusculesde l’alphabet

11 11

11 11Classe des nombres« 1 »violetElement

d'un ensemble

Intersection d'ensemble

Ensembles disjoints

xX

xXX

Classe lettres« X »

xx

Inclusion d’ensemble


− Notion de type d'objet− Ensemble d'objets (ou classe d'objets) possédant des caractéristiques similaires et manipulables par des opérations identiques

− exemple 1 : le type d'objet Entier− représente les entiers relatifs {0, +/-1, +/-2, ... , +/-N, ... +/-∞} − est associé aux opérations {+, -, /, *,}.

− exemple 2 : le type d'objet Livre− représente un livre dans la base d'un centre de documentation. − est caractérisé par son titre, son ou ses auteurs, un numéro identifiant, etc. − est associé des opérations de création, modification, destruction, consultation, duplication.

− Ces opérations sont des opérations standards sur de nombreux objets

Description des données – Type d’objetPlan de cours





− Notion d'instance d'objet− Une instance d'objet est un élément particulier d'un ensemble d'objets.

− Le terme “occurrence d'objet” est aussi parfois utilisé.− Par abus de langage on parle souvent directement d'« objet ».

− Une instance est caractérisée par un identifiant, des propriétés et des valeurs pour ces propriétés

− Exemple 1 : l'entier 10 est une instance du type d'objet Entier− Exemple 2 : le livre « Le nom de la rose » de Umberto Eco est une instance du type d'objet Livre.

− caractérisé par les propriétés “titre”, “auteur”, etc.− pour cette instance particulière de livre, les valeurs pour les propriétés “titre” et “auteur” sont “Le nom de la rose” et “Umberto Eco”.

Description des données – Instance d’objetPlan de cours





Description des données – Instance d’objetPlan de cours




xX

x

xx

xx X

ZB

C

A

Y

11 11

11 11

Un élément d'un ensemble = une instance de l’ensemble des nombres UN

Intersection d'ensemble

Ensembles disjoints


− Modèle de description des données (Data Model)− Décrire des données se ramène à définir un type d'objet

− Des éléments descriptifs permettent de décrire les propriétés d'ensembles d'objets :

− Ces éléments descriptifs sont nommés caractéristiques, propriétés ou attributs

− A partir de ces éléments descriptifs on compose un modèle de description de données qui peut se définir comme :

− Un ensemble de concepts, et de règles de composition de ces concepts, permettant de décrire des données.

Description des données – “Data Model”Plan de cours





− Langage de description de données (LDD)− On parle aussi de langage de définition de données− Le modèle de description des données doit être exprimée par un langage− Le modèle de description de données est souvent représenté de manière graphique

− Le modèle entité – association par exemple fournit un tel formalisme graphique permettant de décrire des modèles de données.

− Un langage de description de données est donc :− Un langage supportant un modèle et permettant de décrire les données d'une base de données d'une manière assimilable par une machine.

Description des données – LDDPlan de cours





− Schéma de données− La description d'un ensemble de données particulier, correspondant par exemple à une application, à l'aide d'un langage de description donne naissance à un schéma.− Un schéma est une description au moyen d'un langage déterminé d'un ensemble de données particulier.

− C'est donc une abstraction résultant de l'application d'un modèle de données à l'univers réel.

Description des données – schéma de donnéesPlan de cours



Pratique des BDDLangage SQLMySQL Univers réel à

modéliser Schéma de donnéesModèle de données


− Quel est le principe de la description des données ?− Toute description de données consiste à définir les caractéristiques d’ensembles d’objets modélisés dans la base de données.

− Qu’est-ce qu’un ensemble (intuitivement) ?− Une collection d’objets/d’éléments.

− Qu’est-ce qu’un type d’objet ?− C’est un ensemble d’objets possédant des caractéristiques similaires et manipulables par des opérations identiques.

− Qu’est-ce qu’un langage de description de données ?− Un langage supportant un modèle et permettant de décrire les données d’une base de données d’une manière assimilable par une machine.

− Qu’est-ce qu’un schéma de données ?− Une description au moyen d’un langage déterminé d’un ensemble de données particulier.






− Indépendance logique et physique− Afin d'assurer les objectifs d'indépendance logique et physique, on distingue 3 niveaux de description de données pour l'architecture fonctionnelle d'un SGBD

−Niveau externe−Niveau logique ou niveau conceptuel−Niveau interne ou niveau physique

− Note : ces 3 niveaux de description de données ont été distinguées par le Groupe ANSI/X3/Sparc au milieu des années soixante-dix.

Architecture fonctionnelle d’un SGBD (1)Plan de cours









Base conceptuelle

Base physique

Vue 1

Vue 2

Vue N

...

Niveau externe

Niveau logique ou conceptuel

Niveau interne ou physique

Groupe d'utilisateurs 1


Groupe d'utilisateurs N

− La base conceptuelle est une abstraction de la base physique− Les vues définies au niveau externe sont des abstractions de la base conceptuelle


− Niveau conceptuel− Il correspond à la structure canonique des données− Il décrit :

− les données qui sont actuellement stockées dans la base− les liens entre ces données, − ne se soucie pas de la manière dont cela doit être implantée sur la machine

− Il permet de définir :− Les types de données élementaires qui définissent les attributs des objets que l'on souhaite modéliser.

− Par exemple : nom, prénom, numéro de livre, titre, etc.

− Les types de données composées qui permettent de décrire les entités et les associations du monde réel

− Par exemple : Livre, Etudiants, Enseignants, Utilisateurs, etc.

− Les éventuelles règles et contraintes d'intégrité.






− Niveau physique− Il correspond à la structure de stockage des données− Il permet de décrire :

− Les fichiers avec leur nom, leur organisation, leur localisation, etc.− Les articles de ces fichiers (longueur, champs, composants, etc.)

−Les articles : ce qui est contenu dans ces fichiers− Les chemins d'accès aux données (index, chaînage, etc.)

− Attention : on est au niveau “implémentation”, on se pose des questions d’ordre “technique”






− Administrateur de données− La ou les personnes responsables de la définition de schémas de bases de données.− Elle regroupe 3 rôles distincts

− Administrateur de bases de données− rôle de définition du schéma interne (i.e. de la base physique) et des règles de correspondance entre les schémas interne et conceptuel.

− Administrateur d'entreprise− rôle de définition du schéma conceptuel.

− Administrateur d'application− rôle de définition des schémas externes et des règles de correspondances entre les schémas externe et conceptuel.










Base (ou schéma) conceptuelle

Base (ou schéma)

physique

Vue 1(schéma externe 1)

Vue 2(schéma externe 2)

Vue N(schéma

externe N)

...

Niveau externe Niveau conceptuel Niveau interne




Administrateur d'application

Administrateur d'entreprise

Administrateur de bases de données

Administrateur de données


− Dictionnaire des données− Ensemble des schémas et des règles de passage entre les schémas associés à une base de donnés, combinés à une description de la signification des données

− Métabase− Dictionnaire de données organisé sous forme de base de données qui décrit les autres bases.






− Un seul schéma interne, un seul schéma conceptuel, plusieurs schémas externes

− Pour une BDD particulière, il existe un seul schéma interne et un seul schéma conceptuel, mais par contre il peut exister plusieurs schémas externes.

Résumé : architecture fonctionnelle d’un SGBDPlan de cours




Base conceptuelle

Base physique

Vue 1

Vue 2

Vue N

...

Schémaexterne

Schémaconceptuel

Schéma interne





− Schéma conceptuel−Description des données d'une entreprise ou d'un domaine en terme de types d'objets et de liens logiques, indépendamment de toute représentation en machine. − Cette description correspond à une vue canonique globale de l'entreprise ou du domaine modélisé.

− canonique signifie qui se conforme à un ensemble de règles. − vue canonique signifie donc

− une vue qui se conforme à un ensemble de règles propres à l'entreprise ou au domaine.

Résumé : schéma conceptuelPlan de cours




Schéma conceptuelle

Schémainterne

Vue 1

Vue 2

Vue N

...





− Schéma interne− Description des données d'une base en termes de représentation physique en machine. − Cette description correspond à une spécification des structures de mémorisation et des méthodes de stockage et d'accès utilisées pour ranger et retrouver les données sur disques

− Note : Nous ne nous préocupperons pas du schéma interne cette année.

Résumé : schéma internePlan de cours





Schéma interne

Vue 1

Vue 2

Vue N

...





− Schéma externe− Description d'une partie de la base de données,− Les vues sont extraites ou calculées à partir de la base physique,

− Elles correspondent à la vision d'un programme ou d'un utilisateur− Un schéma externe correspond donc à un arrangement particulier de certaines données.

Résumé : schéma externePlan de cours





Schéma interne

Vue 1

Vue N

...




Vue 2


− Quels sont les 3 niveaux de description de données pour l’architecture fonctionnelle d’un SGBD ?

− Niveau physique ou interne− Niveau conceptuel ou logique− Niveau externe

− Qu’est-ce q’un dictionnaire des données ? − C’est l’ensemble des schémas et des règles de passage entre les schémas associés à une BDD et combinés à une description de la signification des données.

− Qu’est-ce qu’une métabase ?− C’est un dictionnaire de données organisés sous la forme d’une BDD qui décrit les autres bases.

− Qu’est-ce qu’un schéma conceptuel ?− Description des données d’un domaine en terme de type d’objets et de liens logiques indépendamment de toute représentation en machine.

− Qu’est-ce qu’un schéma externe ?−Description d’une partie de la base de données. Cela correspond à un arrangement particulier des données.






− Entité, association et propriété− Le modèle Entité-Association ou Entité-Association-Propriété est un modèle assez ancien

− proposé par Chen en 1976− en abrégé en E/A, E-A-P ou E/R (pour Entity/Relationship)

− Il permet de modéliser simplement des situations décrites en langage naturel.− Il est basé sur une perception du monde qui consiste à distinguer

− des propriétés ou données élémentaires− des entités qui sont des agrégations de ces données élémentaires − des associations qui sont des liens « logiques » entre les entités.

− C'est un « bon » premier langage de modélisation même si maintenant on utilise plus volontiers des langages de modélisation comme UML− Le modèle E/A est lié à la méthode Merise.

Le modèle E/A : un outil de modélisationPlan de cours





− Notion de méthode− Une méthode est à la fois

− une philosophie dans l’approche des problèmes− une démarche ou un fil conducteur dans la résolution de ces problèmes− un formalisme ou des normes

Une méthode ?Plan de cours





− Base de Données d'un centre de documentation− Reprenons l'exemple introduit au début du cours − On souhaite créer une base de données pour un centre de documentation.

− Nous allons ici volontairement nous limiter à une base de données contenant uniquement des livres. − Ces livres peuvent être empruntés par des utilisateurs.

− Les éléments importants ?

Exemple introductifPlan de cours







− Les éléments importants ?− Les livres : comment définir un livre ?

− Des utilisateurs qui vont emprunter les livres : comment les définir ?








− Les éléments importants ?− Les livres : comment définir un livre ?

− un titre, un auteur, un numéro d'identification pour le centre de documentation, ...

− Des utilisateurs qui vont emprunter les livres : comment les définir ?

− un nom, un prénom, une adresse, un numéro de téléphone, ...






− Données élémentaires et composées− une donnée élémentaire décrit un événement ou un élément « atomiques » du domaine « réel » que l'on souhaite modéliser

− un nom, un prénom sont des données élémentaires− Plus précisément, « nom » va être un type de données élémentaires et par exemple « Jean » est une instance du type élémentaire « nom »

−« utilisateur » est au contraire un objet composé− Une donnée du type d'objet « Utilisateur » est formé par l'agrégation des types de données élémentaires « nom », « prénom » et « adresses »

− Parfois la distinction peut-être arbitraire en fonction de la modélisation :

−Par exemple une adresse peut être vue comme un type de données élémentaires ou comme un type de données composées.

Exemple introductif Plan de cours





− Agrégation− Les données élémentaires (en fait des instances de type de données élémentaires) décrivent des événements atomiques du monde réel− Dans les bases de données, des instances de types élémentaires peuvent être groupées ensembles pour constituer un type d'objet composé

− On parle d'agrégation− Une agrégation est une abstraction qui consiste à grouper des objets pour constituer des objets composés d'une concaténation d'objets composants

Concepts centraux du modèle E/A - Agrégation Plan de cours





− Entité− Dans le modèle E/A, les entités sont des agrégations de données élémentaires. − Elles sont définies par un nom et une liste de propriétés.− Un type d'entité définit un ensemble d'entités constitué par des données élémentaires de même type.

− les types de données agrégés sont appelés les attributs de l'entité. Ils définissent les propriétés de l'entité.

− En résumé− une entité est un modèle d'objet identifié du monde réel dont le type est défini par un nom et une liste de propriétés− un attribut est une propriété d'une entité caractérisée par un nom et un type élémentaire.

Concepts centraux du modèle E/A - Entité Plan de cours





− Liens entre les entités− Les différentes entités sont « liées » logiquement les unes aux autres

− Un livre est écrit par un ou plusieurs auteurs.− Un livre est emprunté par un utilisateur.− Un utilisateur habite à une adresse. − Un livre est publié par un éditeur.

Retour sur l’exemple introductifPlan de cours





− Association− Le 3ème concept central du modèle E/A, après les concepts d'attribut et d'entité, est le concept d'association.− Une association correspond à un lien logique entre deux entités ou plus.

− Elle est souvent définit par un verbe, là où les entités sont souvent définis par un nom.− Une association peut avoir des propriétés particulières définies par des attributs spécifiques.

− En résumé− une association est un lien logique entre entités dont le type est défini par un verbe et une liste éventuelle de propriétés.− un attribut est la propriété d'une entité ou d'une association caractérisée par un nom et un type élémentaire.

Concepts centraux du modèle E/A - Association Plan de cours





− Le modèle E/A permet une représentation graphique− La représentation graphique classique est :

− Les entités sont représentés par des rectangles− Les associations sont représentées par des losanges. Elles sont connectées aux entités par des lignes− Les attributs sont représentés par des ellipses connectées aux entités ou aux associations par des lignes.

− Cette représentation graphique est celle que l’on retrouve dans DIA

Représentation graphiquePlan de cours




Utilisateur habite Adresse

Nom Prénom Numéro Voie Ville


− Une représentation graphique assez courante est :− Les entités sont représentés par des rectangles− Les associations sont représentées par ellipses− La liste des attributs est ajoutée dans le rectangle de l'entité ou l'ellipse de l'association, séparée du nom de l'entité ou de l'association par un trait horizontal.

Autre représentation graphiquePlan de cours



Pratique des BDDLangage SQLMySQL Utilisateur AdresseHabite

NomPrénom

NuméroVoieVille


− Les valeurs liées à un attributs− Chaque instance d'une entité ou d'une association a une valeur particulière associée à chacun de ces attributs

− Valeurs identiques d'attributs d'instances différentes− Plusieurs instances différentes d'une même entité ou association peuvent avoir des valeurs identiques pour un attribut

− Dans ce cas l'attribut concerné ne permet pas d'identifier les instances de manières uniques

− Cela entraine généralement la nécessité d'avoir suffisamment d'attributs pour pouvoir identifier chaque instance.

Valeurs des attributsPlan de cours





− 3 concepts secondaires− La clé d'une entité− Le type d'association− La cardinalité d'un couple entité-association

Concepts secondairesPlan de cours





− Clé d'entité− Attribut ou ensemble d'attributs permettant d'identifier de manière unique les instances d'une entité.− Une entité peut avoir plusieurs clés possibles− Représentation graphique : l'attribut ou les attributs constituants la clef sont soulignés

Concepts secondaires – clé d’entitéPlan de cours




Livre

TitreNuméro...


− Type d'association− couple de valeurs entières déterminé par le nombre d'instances possibles de part et d'autres de l'association.

− Association un à un (1:1)− A une instance de l'entité E1 ne peut correspondre par l'association A1 qu'une seule instance de l'entité E2, et réciproquement

− Association un à plusieurs (1:n) − A une instance de l'entité E1, l'association A1 peut faire correspondre plusieurs instance de l'entité E2

− Association plusieurs à plusieurs (n:m)− A une occurrence de E1 peuvent être associées plusieurs occurrences de E2. Réciproquement, à une occurrence de E2 peuvent être associées plusieurs occurrence de E1.

Concepts secondaires – type d’associationPlan de cours





− Cardinalité d'un couple entité-association− Couple d'entiers (x,y) tel que, x représente le nombre minimum d'instances de l'association pouvant exister pour une instance donnée de l'entité et y le nombre maximum de l'association pouvant exister pour une instance donnée de l'entité.− Il existe autant de tel couple d'entiers représentant la cardinalité d'un couple entité-association, qu'il y a d'entités liées à l'association− Lorsqu'il n'existe pas de contrainte maximale particulière, la valeur générique « n » est retenue par défaut.

− Exemple : −association un à plusieurs (1:n) « emprunte »

− Un utilisateur peut emprunter de 0 à n livres− Un livre est emprunté par 0 ou 1 utilisateur

Concepts secondaires – cardinalitéPlan de cours




Utilisateur LivreEmprunte(0,n) (0,1)


Concepts secondaires – Récapitulatif cardinalitéPlan de cours




Entité E1 Entité E2Association A1

(1,1) (1,1) Relation 1 à 1

A une instance de E1 est associée une instance de E2.Réciproquement, à une instance de E2 est associée une instance de E1


(1,1) (0,n) Relation 1 à n

A une instance de E1 est associée une instance de E2.A une instance de E2 est associée zéro, une ou plusieurs instances de E1


(0,n) Relation m à n

A une instance de E1 est associée zéro, une ou plusieurs instances de E2.Réciproquement, à une instance de E2 est associée zéro, une, plusieurs instances de E1.

(0,n)


− Avantages− Une approche relativement naturelle et facile à comprendre− Une représentation graphique et claire− On retrouve certains éléments de ce modèle conceptuel dans des formalisme graphique récent tel que UML.− Sur le principe, le modèle E/A est proche de technologies autour de XML telles que RDF (Ressource Description Framework).− En France, le modèle E/A est un des éléments de la méthode Merise (qui tends néamoins à disparaître au profit de méthodes définies autour du langage UML)

Avantages et limites du modèle E/A – avantagesPlan de cours





− Limites− Le modèle E/A est un modèle de données et non de traitements

− Il ne permet pas de décrire les modalités de déclenchement d'une création, d'une supression ou d'une consultation d'entités ou d'association d'entités.

− Absence de contraintes de mises à jour− Absences de contrôles de validation

− contrôle indépendamment des valeurs enregistrées (validation statique) ou au contraire en fonction des valeurs enregistrées.

Avantages et limites du modèle E/A – avantagesPlan de cours





− Modélisation d’un bookmark/signet− On souhaite réaliser une petite base de données pour stocker des liens (Web) intéressant.

− Un lien regroupe des informations sur l’URL correspondant au lien, à la date à laquelle le lien a été trouvé, et éventuellement à la description de ce que référence ce lien.− Un lien est associé à une thématique. Une thématique regroupe des informations telles que le nom et la description de cette thématique.− Plusieurs personnes pouvant consulter et mettre à jour cette base, on souhaite également conserver des informations sur la personne ayant trouvé ce liens.

− Proposer le modèle E/A de cette BDD de liens

Exercice : Modéliser une BDD de liens Plan de cours





Correction : Modéliser une BDD de liens Plan de cours





− BDD de citations− On souhaite réaliser une base de données pour stocker des citations issues de livres, de BDs, de films.

− Une citation est composée au moins d’un contenu. Elle possède un ou 0 auteur et une ou 0 origine. − Un auteur possède un nom, un prénom et éventuellement une date de naissance et de mort.− Une origine correspond à la provenance de la citation (livre, film, série, etc.). Il faut donc pouvoir en déterminer le type.− Une citation est associée à une thématique. Une thématique regroupe des informations telles que le nom et la description de cette thématique.−Proposer le modèle E/A de cette BDD de citations

Exercice : Modéliser une BDD de citations Plan de cours





Correction : Modéliser une BDD de citations Plan de cours





− Un modèle ancien qui a fait ses preuves− Introduit par Codd dans les années 70− C'est un modèle ensembliste simple et rigoureux− représentation des données sous formes tabulaires, assez naturelle pour des non informaticiens− basé sur des outils mathématiques (théorie des ensembles) particulièrement adapté à la description de schéma conceptuel de données.

Modèle relationnel – introduction Plan de cours





− Objectifs initiaux du modèle relationnel d'après Codd− Permettre un haut degré d'indépendance entre les programmes d'application et la représentation interne des données− Proposer des éléments pour traiter les problèmes de cohérence et de redondance des données.

− Objectifs atteints grâce à− La simplicité des vues relationnelles qui permettent de voir les données sous forme de tables à 2 dimensions.− Les règles d'intégrité supportées par le modèle et ses fondements logiques.

Modèle relationnel – Objectifs initiaux de Codd Plan de cours





− Objectifs non initialement prévus mais atteints− Développement de langages de manipulation des données non procéduraux basés sur des théories( mathématiques) solides− Algèbre relationnelle et logique− Modélisation et manipulation de données tabulaires mais extensibilité pour pouvoir modéliser et manipuler des données complexes− Développement de standard pour la description et la manipulation des données complexes.

− Le langage SQL qui a été normalisé au niveau international

−L'avenir− Des extensions « objets » au modèle relationnel et au langage SQL existent ou sont en cours de développement.

Modèle relationnel – Serendipity Plan de cours





− Théorie des relations− Le modèle relationnel est fondé sur la théorie mathématique des relations− Cette théorie se construit à partir de la théorie des ensembles.

−3 notions importantes− Domaine (de valeurs)− Relation− Attribut

− Notions complémentaires− Produit cartésien− Tuple

Concepts de base – théorie des relations Plan de cours





− Domaine− Ensemble de valeurs (atomiques) caractérisé par un nom

− Les domaines sont les ensembles dans lesquels les données prennent valeur

− Comme un ensemble un domaine peut être défini :− En extension : on donne la liste des valeurs qui le composent

− domaine des « éditeurs de BD » = {Delcourt, Les Humanoïdes Associés, Soleil, ...}

− En intension : on définit le domaine par une propriété caractéristique que respecte les éléments appartenant au domaine.

− domaine des « entiers positifs » = {ensemble des entiers x tel que x >= 0}

Concepts de base – Domaine Plan de cours





− Domaine et produit cartésien− Associé à la notion de domaine, on a la notion de produit cartésien

− Le produit cartésien d'un ensemble de domaines D1, D2, ... ,Dn , noté D1 x D2 x ... x Dn, est l'ensemble des vecteurs <v1, v2, ..., vn> tel que pour i variant de 1 à n, vi est une valeur du domaine Di.

− Exemple : − D1 : domaine Fonte = {Courier, Arial, Times}− D2 : domaine ElémentTexte = {Titre 1, Titre 2, Paragraphe}

Concepts de base – Domaine et produit cartésien Plan de cours



Pratique des BDDLangage SQLMySQL fonte élémentTexte

Courier Titre 1Courier Titre 2Courier Paragraphe

Arial Titre 1Arial Titre 2Arial Paragraphe

Times Titre 1Times Titre 2Times Paragraphe

Produit cartésien des domaines Fontes X ElémentTextes

Un exemple de vecteur <Courier, Titre 1> ou <Arial, Paragraphe>


− Qu’est-ce qu’une relation ?− Une relation est un sous-ensemble du produit cartésien d'une liste de domaines− Une relation est caractérisée par un nom− Une relation peut être vue comme un tableau à deux dimensions dont les colonnes correspondent aux domaines.

− On utilise le terme relation ou table− Les lignes d'une relation correspondent à des n-uplets de valeurs que l'on nomme en général tuple

− un tuple est une ligne d'une relation qui correspond à un enregistrement;

− Afin de permettre la définition de plusieurs colonnes sur le même domaine et de rendre l'ordre des colonnes sans signification, on associe un nom à chaque colonne : c'est la notion d'attribut.

Concepts de base – Relation, n-uplets et tuples Plan de cours





− Exemple de relation− on considère les domaines D1 et D2, respectivement « Titre de BD » et « Editeurs de BD » suivant

− D1 = {Trolls dans la brume, La porte du ciel, Sueurs froides}−D2 = {Soleil, Les Humanoïdes Associés, Delcourt}

− La relation ou table « Titres chez Editeurs » associant les titres et les éditeurs est la suivante :

Concepts de base – relation :exemple Plan de cours




Titre de BD Editeur de BDTitre chez Editeurs Trolls dans la brume Soleil

La porte du ciel Les Humanoïdes AssociésSueurs froides Delcourt

Représente un tuple, i.e.une ligne de

la table.


− Exemple de relation n-aire− Dans le cas précédent nous avions une relation binaire qui représentait un ensemble de paires ordonnées.− Le cas général est une relation n-aire qui est un ensemble de n-uplets, i.e. un vecteur ou un tuple à n éléments.− Si on reprend l'exemple précédent en ajoutant un domaine D3 qui représente les dessinateurs, la table représentera une relation 3-aire :

− D3 = {Alberti, Bajram,Mourier}

− On pourrait également avoir une relation 4-aire avec en plus le scénariste.

Concepts de base – relation : exemple Plan de cours



Pratique des BDDLangage SQLMySQL Titre de BD Editeur de BD Dessinateur

Titre_Editeur_Dessinateur Trolls dans la brume Soleil MourierLa porte du ciel Les Humanoïdes Associés AlbertiSueurs froides Delcourt Bajram


− Relation et attribut− Comme nous l'avons souligné précédemment afin de permettre la définition de plusieurs colonnes sur le même domaine et de rendre l'ordre des colonnes sans signification, on associe un nom à chaque colonne

− c'est la notion d'attribut.− Un attribut est la colonne d'une relation caractérisée par un nom.

Concepts de base – relation et attributPlan de cours





− Variation des valeurs dans le temps− Le modèle relationnel permet de décrire des données dont les valeurs évoluent dans le temps

− Des tuples vont êtres ajoutés, d'autres supprimés et certains verront certaines de leurs valeurs modifiées.

− Néanmoins, la structure d'une relation caractérisée par les trois concepts de domaine, relation et attribut est invariante

− Cette structure est capturée dans le schéma de la relation− Le schéma de la relation est le nom de la relation suivi de la liste des attributs et de la définition de leurs domaines.− Un schéma de relation est notée sous la forme :

− R (A1 : D1, A2 : D2, ..., Ai : Di,..., An : Dn) avec R le nom de la relation, Ai le nom de l'attribut i et Di le domaine i.

Concepts de base – Variation des valeursPlan de cours





− Intension et extension− Le schéma d'une relation représente son intention, i.e. les propriétés (en tous les cas au moins une partie) communes et invariantes des tuples qu'elle contient ou va contenir

−l'intention est une description des données− Une table représente au contraire l'extension d'une relation

− i.e. une vue des tables qu'elle contient à un moment donné− l'extension représente un état de la base− L'extension d'une relation R est également appelée instance de R

− Exemple de schéma de relation− BD (TITRE: String, EDITEUR: String, SCENARISTE : String, DESSINATEUR : String, NUMERO: Integer)

Concepts de base – Schéma d’une relationPlan de cours





− Règles d’intégrité − Ce sont les contraintes que doivent vérifiées les données de la base.

− Règle minimale : unicité des clés− Il ne peut exister 2 fois le même tuple dans une relation

− afin d'assurer cette unicité des tuples, la notion de clé est utilisée :

− Une clé est l'ensemble minimal d'attributs dont la connaissance des valeurs permet d'identifier un tuple unique de la relation considérée.− Toute relation possède au moins une clé, car la connaissance de tous les attributs permet d'identifier un tuple unique.− S'il existe plusieurs clés, on en choisit « arbitrairement » une qui est appelée clé primaire. Les autres clés possibles sont appelés clés secondaires ou alternatives.

Règles d’intégrité – unicité d’une clé Plan de cours





− 3 autres types de règles d'intégrité sont souvent ajoutés− les contraintes de références

− Une contrainte de référence existe à chaque fois qu'une relation comprend des attributs définis sur le même domaine avec un lien sémantique avec les attributs d'une autre relation

− les contraintes d'entité− Elles imposent de vérifier qu'une clé primaire est unique et ne possède pas de valeurs « nulles » (c'est-à-dire des valeurs indéterminées ou inconnues)

− les contraintes de domaine− Elles font référence au type de définition des attributs. Elle concerne le contrôle synatxique et sémantique d'une donnée.

Règles d’intégrité – autres contraintesPlan de cours





− DF : Dépendance Fonctionnelle− Il y a dépendance fonctionnelle d'un attribut (ou groupe d'attributs) A d'une relation R vers un attribut (ou groupe d'attributs) B si à une valeur de A correspond au plus une valeur de B

− On dit que A détermine B− Cette dépendance fonctionnelle est noté A -> B

− Plus formellement, A-> B (lire « A détermine B ») si et seulement si pour tous tuples t1 et t2 de la relation R

si t1[A] = t2 [A] alors t1[B] = t2[B] avec ti[X] le sous-tuple de R composé des valeurs de l'attribut X.

− Autre formulation − Un attribut (ou groupe d’attributs) B dépend fonctionnellement d’un attribut (ou groupe d’attributs) A, si, étant donné une valeur de A, il lui correspond une valeur unique de B (quel que soit l’instant considéré).

Règles d’intégrité – dépendance fonctionnellePlan de cours





− Clés candidates, primaires et secondaires− Formellement la clé d'une relation est le sous-ensemble X des attributs d'une relation R(A1, A2, ...,An) tel que

− X -> A1, A2, ..., An

− Il n'existe pas de sous-ensemble Y X⊂ tel que Y -> A1, A2, ..., An

− cette dernière condition implique qu'une clé doit être la plus « petite possible » en nombre d'attributs, qu'il ne peut y avoir que des attributs réellement utiles pour définir l'unicité du tuple.

− Une clé candidate est un attribut (ou groupes d'attributs) dont les valeurs déterminent de façon univoque chaque tuple d'une relation− Une clé primaire est la clé candidate choisie pour identifier chaque tuple d'une relation. − Les clés candidates non retenues sont appelées clés secondaires.

Règles d’intégrité – clés, le retourPlan de cours




avion(N°AV, NomAV)pilote (N°Pilote, Nom, Adresse)


− Clé étrangère− Une clé étrangère est un attribut ou un groupe d'attributs défini sur un domaine primaire et qui n'est pas une clé primaire.

− Une clé étrangère fait toujours référence à une clé primaire définie sur le même domaine− En clair, une clé étrangère dans une relation est un attribut (ou un groupe d'attributs) qui est une clé primaire dans une autre relatio

− Clés étrangères et contraintes de références− Une contrainte de référence existe à chaque fois qu'une relation comprend des attributs sémantiques définis sur le même domaine avec lien sémantique avec les autres attributs d'une autre relation.

− Ce type de contrainte doit être vérifié lors de la mise à jour des données

Règles d’intégrité – clé étrangèrePlan de cours




avion(N°AV, NomAV)pilote (N°Pilote, Nom, Adresse)vol(N°Vol, N°Pilote#, N°Avion#, Durée)


− Exemple−La contrainte de référence entre les relation pilotes et vol peut être exprimée par :

− « A chaque fois qu'une valeur de N°Pilote# apparaît dans le tuple de vol, cette même valeur doit exister dans le tuple pilote ». − vol est la relation « référençante »− pilote est la relation « référencée »

Règles d’intégrité – exemplePlan de cours




avion(N°AV, NomAV)pilote (N°Pilote, Nom, Adresse)vol(N°Vol, N°Pilote#, N°Avion#, Durée)


− Contraintes de références / référentielles− Les clés étrangères sont un des moyens pour représenter des contraintes de références (ou contraintes référentielles).

− Une contrainte référentielle est une contrainte d'intégrité portant sur une relation R1, consistant à imposer que la valeur d'un groupe d'attributs de R1 aparaisse comme valeur de clé dans une autre relation R2

Règles d’intégrité – Contraintes de référencesPlan de cours





− Valeur nulle− Lors de l'insertion de tuple dans une relation il arrive que la valeur pour un attribut soit inconnu ou inapplicable.

− On est alors amené à introduire dans la relation une valeur conventionnelle, nommée valeur nulle.

− La valeur nulle est une valeur conventionnelle introduite dans une relation pour représenter une information inconnue ou non applicable

− Contrainte d'entité− La contrainte d'entité est une contrainte d'intégrité qui impose que toute relation possède une clé primaire et que tout attribut participant à cette clé primaire soit non nul.

Règles d’intégrité – Valeur nulle et entitéPlan de cours





− Un objectif − structurer le domaine d'application pour pouvoir le représenter sous forme de types et de tables.− La représentation doit être

− juste pour éviter les erreurs sémantiques (notamment dans les réponses aux requêtes)− complète pour permettre les développements des programmes d’application souhaités− évolutive pour pouvoir supporter la prise en compte rapide de nouvelles demandes, l'expression de nouveaux besoins

Conception du schéma des basesPlan de cours





− Une démarche en 5 étapes− G.Gardarin (voir les références bibliographiques) proposent une démarche en étapes. − L'idée est de procéder par abstractions successives, du problème vers l'utilisateur

(1) Perception du monde réel et capture des besoins(2) Elaboration du schéma conceptuel(3) Conception du schéma logique(4) Affinement du schéma logique(5) Elaboration du schéma physique

Démarche de conceptionPlan de cours





− Etude des problèmes des utilisateurs et compréhension de leur besoin

− Relève du « Génie Logiciel ». Cela fait partie des phases recueil des besoins et rédaction de cahier des charges.

− Dans le cadre de ce cours consiste à construire un ou plusieurs modèle Entité-Association exprimant les vues externes de la base

− C'est une phase particulièrement importante ; elle consiste dans des cas réels à plus qu'une simple construction d'un modèle E/A.− Remarque : ici nous utilisons le modèle E/A. Cependant d'autres modèles existent pour cette étape.

− Actuellement on utilise le langage de modélisation UML et de ce qui est nommé les cas d'utilisation (Use Case en anglais)

Démarche de conception – Besoin utilisateurPlan de cours





− Elaboration du schéma conceptuel− Il s'agit ici d'intégrer en un seul schéma conceptuel global, complet, cohérent et sans redondance, l'ensemble des schémas produits à l'étape précédente− Des aller-retours avec l'étape précédente sont souvent nécessaires.

Démarche de conception – Schéma conceptuelPlan de cours





− Conception du schéma logique− Dans cette étape, on réalise la transformation du schéma conceptuel produit à l'étape précédente en structures de données implémentables sur le système choisi.− Cela consiste pour nous à passer du modèle E/A au modèle relationnel. Nous verrons la méthode de transformation dans la suite de ce cours.

− Affinement du schéma logique− Il faut vérifier que le schéma produit est un bon schéma

− En première approximation un « bon schéma » est un schéma sans oublis ni redondances d'informations

− Pour définir des « bons » schémas (i.e. des schémas respectant certaines propriétés particulières), le modèle relationnel s'appuie sur la « normalisation ».

Démarche de conception – Schéma logiquePlan de cours





− Elaboration du schéma physique− Définition des structures physiques de stockage des données.

− Cette étape n’est pas du tout abordée dans le cadre de ce cours (même si c'est un point important du point de vue informatique)

Démarche de conception – Schéma physiquePlan de cours





− Règle de conversion d’une entité− Toute entité se transforme en relation

− Les propriétés de l'entité deviennent les attributs de la relation− La clé (ou identifiant) de l'entité devient la clé primaire de la relation.

Passage du modèle E/A au modèle relationnel (1)Plan de cours




Fournisseur

N°FournisseurNomAdresse

Fournisseur (N°Fournisseur, Nom, Adresse)


− Règle de conversion d’une association binaire (1-n)− L'association « se transforme » en clé étrangère

− La clé primaire de la relation issue de l'entité ayant la cardinalité maximum n devient une clé étrangère dans la relation issue de l'entité de cardinalité maximum 1.

− Règle de conversion d'une association réflexive (1-n)− L'association « se transforme » en clé étrangère − La clé primaire de la relation issue de l'entité est dupliqué sous forme de clé étrangère pour traduire l'association.






− Règle de conversion d’une association binaire (1-n)−L'association « se transforme » en clé étrangère

− La clé primaire de la relation issue de l'entité ayant la cardinalité maximum n devient une clé étrangère dans la relation issue de l'entité de cardinalité maximum 1.





Fournisseur


Fournisseur (N°Fournisseur, Nom, Adresse,N°Service)

Est en relation1,1 N°ServiceActivité

Service

1,n

Service (N°Service, Activité)


− Règle de conversion d'une association réflexive (1-n)− L'association « se transforme » en clé étrangère − La clé primaire de la relation issue de l'entité est dupliqué sous forme de clé étrangère pour traduire l'association.





N°EmployéNomAdresse

Est encadré

0,1

0,n

Employé (N°Employé, Nom, Adresse, N°Employé-Encadrant)

Employé


− Règle de conversion d'une association binaire ou n-aire (n-n)

− L'association se transforme en relation− Les propriétés de l'association deviennent les attributs de la relation.− La clé primaire de la relation qui représente l'association est constituée par la concaténation des clés primaires des relations issues des entités liées à l'association.





Fournisseur


Fournisseur (N°Fournisseur, Nom, Adresse)

Fournit1,n N°ArticleDésignation

Article

1,n

Article (N°Article, Désignation)

DateQuantité

Fournir (N°Fournisseur, N°Article, Date, Quantité)


− Règle de conversion d'une association réflexive n-aire (n-n)





Article

N°ArticleDésignation

Composant

0,n

0,n

Article (N°Article, Désignation)

Quantité

Composant (N°Article-composant, N°Article-composé, Quantité)


− Règle de conversion d'une association ternaire n-aire (n-n)





Bus

N°BusCapacité

Affectation (N°Bus, N°Chauffeur, N°Trajet )

Affecter à1,n N°TrajetDépart

Trajet

1,n

Bus (N°Bus, Capacité)

1,n

Chauffeur (N°Chauffeur, Nom, Adresse)

Arrivée

Trajet (N°Trajet, Départ, Arrivée)

Chauffeur

N°ChauffeurNomAdresse


− Reprendre le modèle entité / association de la BDD de liens et le convertir en modèle relationnel

Exercice : modèle E/A et modèle relationnel (1)Plan de cours





− Reprendre le modèle entité / association “liens” est le convertir en modèle relationnel

− Personne (num_personne, nom, prénom)− Liens (URL, date, description, num_personne)− Est_Associé_A (URL, num_thematique)− Thematique(num_thematique, nom, description)






− Reprendre le modèle entité / association “citations” est le convertir en modèle relationnel






− Une mauvaise conception pose des problèmes de− Redondance− Anomalies de mises à jour− Anomalies d'insertion− Anomalies de suppression

− Exemple avec la relation− fournisseur(nom,adresse, produit, prix)

Problèmes de conceptionPlan de cours





− fournisseur(nom, adresse, produit, prix)− Redondance

− L'adresse et le nom de chaque fournisseur sont répétés autant de fois qu'il y a de produits fournis par ce fournisseur.

− Anomalies de mises à jour− Si l’adresse du fournisseur change, il faut la modifier pour chacune des entrées nom/adresse/produit/prix. Il peut y avoir des oublis.

− Anomalies d'insertion− On ne peut insérer dans la base qu’un fournisseur ayant des produits référencés

− Anomalies de suppression− Si on supprime les produits d’un fournisseur, on risque de perdre l’adresse de ce fournisseur dans la base

Problèmes de conception – RedondancePlan de cours





− Un langage adapté au modèle relationnel− Le langage SQL est un langage normalisé pour interroger une base de données.−Ce langage est non procédural, déclaratif et est accessible pour le non-informaticien.− Il implémente les opérations de base de l'algèbre relationnelle.− Il permet la manipulation « logique » des données.− SQL signifie Structured Query Language

Introduction – le langage SQL (1)Plan de cours





− 3 langages en 1− SQL peut être décomposé en 3 « parties » :

− DDL (Data Definition Language) qui permet la définition et la création de la base de données

− mots-clés associés : « create », « alter », « drop » et « rename »

− DML (Data Manipulation Language) qui s'occupe de l'interrogation et de la mise à jour du contenu de la base de données.

− mots-clés associés : « select », « insert », « update », « delete »

− DCL (Data Control Language) qui fournit les fonctions permettant d'assurer l'intégrité des données et de leur sécurité.

− mots-clés associés : « grant », « revoke », « commit », « rollback »






− DDL et DML− Nous nous intéresserons essentiellement dans le cadre du cours aux aspects DDL et DML du langage SQL.− L'algèbre relationnelle ne sera pas directement abordée

− elle sera traiter de manière indirecte à travers l'utilisation de SQL

− MySQL sera utilisé pour la partie application dans le cadre des TP

− MySQL est basée sur la norme ANSI SQL 92 avec quelques fonctions supplémentaires.

− Interface de manipulation − Actuellement, il existe des interfaces (par menus, formulaire, fenêtre, ...) en « surcouche » de SQL.

− On trouve également le langage d'interrogation par « grille » QBE (Query By Example) proposé comme surcouche de SQL.

− Avec MySQL nous ferons nos requêtes directement en SQL ou en utilisant l’interface PHPMyAdmin.






− DDL et DML− Nous nous intéresserons essentiellement dans le cadre du cours aux aspects DDL et DML du langage SQL.− L'algèbre relationnelle ne sera pas directement abordée

− elle sera traiter de manière indirecte à travers l'utilisation de SQL

− MySQL sera utilisé pour la partie application dans le cadre des TP

− SQL et MySQL− Nous allons voir 2 choses, d'une part la syntaxe de SQL en général et d'autre part des éléments propre à MySQL (que l'on retrouve mais parfois différemment, dans les autres SGBD)






− Convention d'écriture−en fonte courier, gras : les mots clés en SQL ou MySQL− en fonte courier normal entre « < » et « > » les éléments correspondant au nom de table, de colonnes, etc. que l'utilisateur doit définir.− encadré par des crochets « [ » et « ] » des éléments optionnels.






− create table <nom_table> (<elt_table1>, <elt_table2>, ...)

− <elt_table1> correspond soit à la définition d'une colonne soit à la définition d'une « contrainte » sur la table− La définition d'une colonne est comme suit:

− <nom_colonne><type_donnée> [<clause_defaut>][<contrainte_colonne>]

− <type_donnée> : en général on a au moins les types entier, décimal, caractère, date,...

− Nous verrons plus tard les types disponibles avec MySQL− <clause default> permet de définir une valeur par défaut de la manière suivante− default <valeur_par_défaut>

DDL – Création de table (1)Plan de cours






− <elt_table1> correspond soit à la définition d'une colonne soit à la définiton d'une « contrainte » sur la table− La définition d'une colonne est comme suit:

− <nom_colonne><type_donnée> [<clause_defaut>][<contrainte_colonne>]−<contrainte_colonne> : on peut préciser des contraintes d'intégrité portant sur un seul attribut

− valeur « null » impossible : not null− unicité de l'attribut : unique ou primary key− contraintes référentielles : references <table_reférencée>[(<colonne_référencée>)]− clé étrangère : foreign key− contrainte générale : check <condition>







− <elt_table1> correspond soit à la définition d'une colonne soit à la définiton d'une « contrainte » sur la table− La définition d'une colonne est comme suit:

− <nom_colonne><type_donnée> [<clause_defaut>][<contrainte_colonne>]−<contrainte_colonne> : on peut définir des contraintes sur plusieurs attributs

− Contraintes d'unicité− unique <att1>[<att2>...]

− Clés primaires− primary key (<att1>[,<att2>, ...])

− Clés étrangères− foreign key (<att1>[,<att2>, ...]) references <table_référencée> [(<colonne_référencée>)]

− Contrainte générale−check <condition>






− création d’une table, exemple avec MySQL





CREATE TABLE contact(num_contact INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, nom VARCHAR(20), prenom VARCHAR(20),type_contact ENUM("Personnel" , "Professionnel" , "Familial" , "Autre"));


− Suppresion de table− drop table <nom_table_a_supprimer>

− Modifier une table− alter table <nom_table_a_modifier>

DDL – Suppression et modification de table (3)Plan de cours





− Algèbre relationnelle − Elle regroupe l’ensemble des opérations sur les relations

− Projection− on ne sélectionne qu’un ou plusieurs attributs d’une relation (on ignore les autres). − Par exemple on n’affiche afficher que les colonnes nom et prénom de la table Etudiant.

Algèbre relationnelle – projectionPlan de cours




SophieFonsec3

AgatheZeblouse2AlainTerieur1

prénomnomnum_etudiant

SELECT nom, prénomFROM Etudiant

On projette la table Etudiant sur les colonnes nom et prénom.

SophieFonsecAgatheZeblouseAlainTerieur

prénomnom

Etudiant


− Projection en SQL : select … from …− Pour résumer, une projection effectue l’extraction des colonnes (colonne == attribut) spécifiées d’une relation puis élimine les tuples en double. − En SQL, cela s’exprime par une clause de type

− avec les doublons non-supprimés SELECT <Liste d’attributs> FROM <Nom de relation>

− avec suppression des doublons SELECT UNIQUE <Liste d’attributs> FROM <Nom de relation>

Algèbre relationnelle – projection en SQLPlan de cours





−Jointure− on construit une nouvelle relation à partir de 2 ou plusieurs autres relations

− pour se faire, on prend comme pivot 1 ou plusieurs attributs. − Par exemple, on concatène la table Etudiant et la table Mel en fonction du numéro d’étudiant.

Algèbre relationnelle – jointure en SQLPlan de cours





−Jointure : select … from … where− on construit une nouvelle relation grâce à au moins 2 relations. Il faut que chaque couple de relations ait un attribut en commun, cet attribut servant en quelque sorte de pivot pour réunir (“joindre”) les 2 tables. − C’est dans la close “where” que l’on précise la ou les colonnes “pivot”.

SELECT <liste des attributs>FROM <liste des tables>WHERE <liste des colonnes devant être égales>

Algèbre relationnelle – jointure (1)Plan de cours





Algèbre relationnelle – jointure (2)Plan de cours




SophieFonsec3

AgatheZeblouse2

AlainTerieur1


SELECT nom, adresseFROM Etudiant,MelWHERE Etudiant.num_etudiant = Mel.num_etudiant

[email protected]

[email protected]

[email protected]_etudiant

Etudiant

Mel

[email protected]

[email protected]

[email protected]

On joint les deux tables, grâce à la colonne num_etudiant.Cette jointure est combinée à une projection sur les attributs nom et adresse.

Attention à lever toute ambi-guïté sur les noms d’attribut dans le cas où deux tables possèdent des colonnes de même nom.


− Sélection− on sélectionne tous les tuples (ou bien seulement une partie de chaque tuple) en fonction de critères de sélection qui portent sur les valeurs des attributs. − Par exemple n’afficher que les valeurs de la colonne nom de la table Etudiant des lignes qui vérifient la condition suivante : le prénom est “Alain”.

Algèbre relationnelle – sélectionPlan de cours




SophieFonsec3

AgatheZeblouse2

AlainTerieur1

prénomnomnum_etudiantEtudiant

SELECT nomFROM EtudiantWHERE prénom = “Alain” Terieur

nom


− Restriction et Sélection en SQL −Une sélection est une combinaison d’une restriction suivie d’une projection. − Une restriction consiste à récupérer une relation ne contenant que des attributs répondant à certaines qualifications. − Une restriction s’exprime en SQL par le bloc suivant, où " * " désigne l’ensemble des attributs :

SELECT * FROM <Nom de relation> WHERE <qualification>

Algèbre relationnelle – sélection en SQL (1)Plan de cours





− Restriction et Sélection en SQL − La combinaison d’une restriction avec une projection pour obtenir une sélection s’obtient en remplaçant " * " par la liste d’attributs de projection dans la clause SELECT :

SELECT <Liste d’attributs> FROM <Nom de relation> WHERE <qualification>

− La qualification dans la clause WHERE peut inclure les opérateurs de comparaison =, <>, >, >=, <, <= pour composer les qualifications atomiques.

− Les compositions de qualifications s’obtiennent à l’aide des opérateurs booléens AND, OR, NOT éventuellement parenthésées.

− La restriction peut-être vue comme une forme de sélection

Algèbre relationnelle – sélection en SQL (2)Plan de cours





− Sélection et restriction

Algèbre relationnelle – sélection et restrictionPlan de cours




SophieFonsec3

AgatheZeblouse2

AlainTerieur1


SELECT *FROM EtudiantWHERE prénom = “Alain”

Etudiant

AlainTerieur1


SELECT nomFROM EtudiantWHERE prénom = “Alain”

Terieur

nom

restrictionsélection


− MySQL dérive directement de SQL − Il en reprend l’essentiel de la syntaxe

− certaines fonctionnalités de SQL n’apparaissent pas dans MySQL (sélections imbriquées notamment)

− Le serveur de base de données MySQL est très souvent utilisé avec le langage PHP pour la création de pages web dynamiques

− Cependant il est également accessible à travers la majorité des langages de développement (C/C++/Java/PERL/Python/…)

Introduction – MySQL (1)Plan de cours





− MySQL dérive directement de SQL − MySQL est un SGBDR (Système de Gestion de Bases de Données Relationnelles) client-serveur :

− MySQL est un SGBDR client-serveur− le serveur est une application installée sur l'ordinateur où sont stockées les données.

− il attend des requêtes des clients, accède à la base pour exécuter la requête du client et lui fournir une réponse.

− le client est un programme installé sur votre ordinateur qui se connecte par l'intermédiaire d'un réseau au serveur pour effectuer une requête ;

− MySQL comporte− un serveur SQL,− des programmes clients pour accéder au serveur (mysql, mysqldump, mysqlimport),− des outils d'administration,− une interface de programmation pour écrire des applications.

Introduction – MySQL (2)Plan de cours





− MySQL est − rapide,− multi-utilisateurs,− disponible sur Internet (http://www.mysql.com) et gratuit pour la plupart des usages (sauf si vous commercialisez un service ou un logiciel qui utilise MySQL),− facile à installer,− facile à utiliser, un manuel de référence est fourni.− portable

−MySQL fonctionne sous : Unix et Linux, Solaris, Mac Os X server, Windows.

− “open source”− le code source est disponible

− les bugs sont corrigés par la communauté et des nouvelles versions sont disponibles sur Internet dès que les corrections sont effectuées

− Il comporte en plus de l’implémentation de SQL− des interfaces de programmation (API) pour de nombreux langages : C, Perl, PHP, Python, Java, …

MySQL – AvantagesPlan de cours





− MySQL est moins complet que les grands SGBDR du marché

− Pas de gestion des clés étrangères (intégrité référentielle assurée par la couche applicative)− Pas de triggers (déclencheurs)− Pas de procédures stockées− Pas de support transactionnel− Support partiel de la norme SQL

− Au fur et à mesure des nouvelles versions ces “manques” sont corrigés.

MySQL – InconvénientsPlan de cours





− Types des attributs− Chaque colonne représente un élément caractéristique de notre relation et contiend des valeurs d’un certain type

− Nombre entier signé ou non (température, quantité commandée, âge, etc.)− Nombre réel (prix, taille, masse, poids, etc.)− Chaîne de caractères (nom, prénom, adresse, etc.)− Date et heure (date de naissance, heure de parution)− Énumération (une couleur parmi une liste prédéfinie par exemple)− Ensemble (une ou des monnaies parmi une liste prédéfinie par exemple)

− Ces types de données requièrent une plus ou moins grande quantité de place en mémoire.

−Il s’agit de choisir un type de donner adapté au besoin (ni trop ni trop peu!).

MySQL – Type des colonnesPlan de cours





− Les entiers avec MySQL− on peut préciser un type entier non-signé en ajoutant le mot-clé UNSIGNED.

MySQL – Types numériques entiers Plan de cours




-32768 à 32767 / 0 à 65535

Entier petitSMALLINT

-263 à 263 – 1 / 0 à 264 -1

Grand entierBIGINT

-231 à 231 – 1 / 0 à 232 -1

Entier standardINT

-223 à 223 – 1 / 0 à 224 -1

Entier « moyen »MEDIUMINT

-128 à + 127 / 0 à 255Entier très petitTINYINT

Intervalle de valeursSignificationNom du type


− Les flottants (ou nombres réels) avec MySQL− Contrairement aux entiers, leur domaine n’est pas continu

−impossibilité de les représenter avec une précision absolue.

MySQL – Types numériques réels Plan de cours




VariableRéel sous forme de chaîne

DECIMAL

Min : ±2.2250738585072014E-308Max : ±1.797693134862315E308

Réel double précisionDOUBLE

Min : ±1.175494351E-38Max : ±3.402823466E38

Réel simple précisionFLOAT

Intervalle de valeursSignificationNom du type

0-3.402823466E+38

-1.175494351E-38

1.175494351E-38

3.402823466E+38


− Les chaînes avec MySQL

MySQL – Types chaîne de caractères Plan de cours



Pratique des BDDLangage SQLMySQL Longueur maximale de 16777215 caractères.MEDIUMTEXT

Longueur maximale de 65535 caractères.TEXT

Longueur maximale de 4294967295 caractères.

LONGTEXT

Longueur maximale de 255 caractères.TINYTEXT

Simule un nombre flottant de D chiffres (1<D<30, D < M-2) après la virgule et de M (1<M<255) chiffres au maximum. Chaque chiffre ainsi que la virgule et le signe moins (pas le plus) occupe un caractère.

DECIMAL

Chaîne de taille variable, de taille maximum M, où 1<M<255.

VARCHAR(M)

Chaîne de taille fixée à M, où 1<M<255, complétée avec des espaces si nécessaire.

CHAR(M)

CaractéristiqueNom du type


− Les chaînes et les blob avec MySQL− Les types “BLOB”

− TINYBLOB, BLOB, MEDIUMBLOB et LONGBLOB− Les BLOB peuvent être utilisés pour stocker des données binaires− Ils peuvent remplacer les types “TEXT” correspondant (TINYTEXT, TEXT, MEDIUMTEXT et LONGTEXT).− seule “grosse” diffèrence les types “BLOB” sont sensibles à la casse lors des tris et recherches contrairement au type “TEXT”.

− Les types VARCHAR, TEXT et BLOB sont de taille variable. Alors que les CHAR et DECIMAL sont de taille fixe.

MySQL – Types chaîne et BLOBPlan de cours





− Les BLOB avec MySQL

MySQL – Types BLOB Plan de cours




Longueur maximale de 16777215 octets (224 –1).

MEDIUMBLOBLongueur maximale de 65535 octets (216 –1).BLOB

Longueur maximale de 4294967295 octets (232 –1).

LONGBLOB

Longueur maximale de 255 octets (28 –1).TINYBLOB

CaractéristiqueNom du type


− Les dates et les heures avec MySQL

MySQL – Types date et heure (1)Plan de cours



Pratique des BDDLangage SQLMySQL Affiche la date et l’heure sans séparateur :

AAAAMMJJHHMMSS. Intervalle 19700101000000 jusqu’à un moment en 2037

TIMESTAMP

Idem mais M vaut un entier pair entre 2 et 14. Affiche les M premiers caractères de TIMESTAMP.

TIMESTAMP(M)

Année au format AAAA. Intervalle de 1901 à 2155.

YEAR

Date et heure au format anglophone AAAA-MM-JJ HH:MM:SS. Intervalle “1000-01-01 00:00:00” à “9999-12-31 23:59:59”

DATETIME

Heure au format HH:MM:SS. Intervalle “-838:59:59” à “838:59:59”

TIME

Date au format anglophone AAAA-MM-JJ. Intervalle “1000-01-01” à “9999-12-31”

DATECaractéristiqueNom du type


− Le format de date AAAA-MM-JJ− année sur 4 chiffres, mois sur 2 chiffres et jour sur 2 chiffres avec pour séparateur le tiret.

− Le format d’heure HH:MM:SS− heure, minute et seconde chacune sur 2 chiffres, avec pour séparateur les deux points.

− Le format étendu − la date et l’heure sont séparées par un espace.

− Il est possible de mettre n’importe quel caractère qui ne soit pas un chiffre en guise de séparateur ou de ne pas en mettre du tout.





AAAAMMJJHHMMSS.TIMESTAMPAffiche les M premiers caractères de TIMESTAMP. TIMESTAMP(M)AAAA. YEAR

AAAA-MM-JJ HH:MM:SS. DATETIMEHH:MM:SS. TIMEAAAA-MM-JJ. DATECaractéristiqueNom du type


− Les TIMESTAMP− En cas d’insertion d’un enregistrement en laissant vide un attribut de type TIMESTAMP, celui-ci prendra automatiquement la date et heure de l’insertion. − Contrairement à Unix (où le timestamp est le nombre de secondes écoulées depuis le 1er janvier 1970), en MySQL, il est une chaîne de caractères avec le format indiqué ci-après.





AAMMJJHHMMSSTIMESTAMP(12)

AAAAMMJJTIMESTAMP(8)AAMMJJHHMMTIMESTAMP(10)

AAAAMMJJHHMMSSTIMESTAMP(14)

AAMMJJTIMESTAMP(6)AAMMTIMESTAMP(4)AATIMESTAMP(2)CaractéristiqueNom du type


− Un attribut de type ENUM − Prend une valeur parmi celles définies lors de la création de la table, plus la chaîne vide et la valeur NULL si la définition le permet. − Ces valeurs sont exclusivement des chaînes de caractères. − Une énumération contient 65535 éléments au maximum.

− Définition d’un attribut de type ENUM − nom_attribut ENUM(‘’valeur 1’’,’’valeur 2’’…)− nom_attribut ENUM(‘’valeur 1’’,’’valeur 2’’…) NULL

− A chaque valeur est associée − un index allant de 0 à N, si N valeurs ont été définies.− L’index 0 est associé à la chaîne nulle, l’index 1 à la première valeur… − L’index NULL est associé à la valeur NULL.

− Si une sélection (SELECT ou WHERE) est faite dans un contexte numérique, cet index est renvoyé. Sinon, c’est la valeur définie dans l’énumération qui est retournée.

MySQL – Type énumerationPlan de cours





− Un attribut de type SET− peut prendre pour valeur la chaîne vide, NULL ou une chaîne contenant une liste de valeurs qui doivent être déclarées lors de la définition de l’attribut lors de la création de la relation.− Il ne peut être défini que 64 éléments maximum.

− Par exemple− un attribut déclaré comme ceci …

− SET(“voiture”, “moto”, “vélo”) NOT NULL− … peut prendre les valeurs suivantes

− “” (chaîne vide)− “voiture,moto”− “vélo,voiture,moto”− et autres combinaisons de listes des trois valeurs définies plus haut.

− Un attribut déclaré comme suit …− SET(‘‘voiture’’, ‘’moto’’, ’’vélo’’) NULL

− … peut prendre, en plus de celles précédentes, la valeur NULL.

MySQL – Type ensemblePlan de cours





− Les noms des bases, relations, attributs, index et alias − sont constitués de caractères alphanumériques et des caractères _ et $ − un nom comporte au maximum 64 caractères.

− Sensibilité à la casse− Comme les bases de données et les relations sont codées directement dans le système de fichiers, la sensibilité à la casse de MySQL dépend de celle du système d’exploitation sur lequel il repose.

− Sous Windows, la casse n’a pas d’importance, alors que sous Unix, elle en a.

− Le caractère point “.”− Le point “.” est un caractère réservé utilisé comme séparateur entre le nom d’une base et celui d’une relation, entre le nom d’une relation et celui d’un attribut. − Exemple :

SELECT base1.table25.attribut5FROM base1.table25

MySQL – IdentificateursPlan de cours





− Retour sur la Base “Liens” − Imaginons que l’on souhaite créer des tables sous MySQL correspondant au modèle relationnel de la base “Liens” que nous avons vu précédemment− Nous avons les tables suivantes :

− Liens (URL, date_creation, description, num_personne)− Personne (num_personne, nom, prenom)− Est_Associe_A (URL, num_thematique)− Thematique (num_thematique, nom, description)

− Table Personne− On considère que le nom et le prénom d’une Personne ne dépassera pas les 50 caractères (normalement on prévoit là assez large!), on utilise donc des VARCHAR(50) − Le num_personne est un entier non signé. On prend ici un MEDIUMINT mais normalement même un SMALLINT aurait pu faire l’affaire. Le num_personne est généré de manière automatique.

MySQL – Exemple (1)Plan de cours





− La Base “Liens” : la table Personne− On considère que le nom et le prenom d’une Personne ne dépassera pas les 50 caractères (normalement on prévoit là assez large!), on utilise donc des VARCHAR(50) − Le num_personne est un entier non signé. On prend ici un MEDIUMINT mais normalement même un SMALLINT aurait pu faire l’affaire. Le num_personne est généré de manière automatique (AUTO_INCREMENT).





CREATE TABLE Personne ( num_personne MEDIUMINT UNSIGNED AUTO_INCREMENT NOT NULL, nom VARCHAR(50), prenom VARCHAR(50), PRIMARY KEY(num_personne));


− La Base “Liens” : la table Lien− L’URL peut-être assez longue, nous prévoyons donc large (VARCHAR(255)).− La description est de type TEXT (encore une fois on prévoit large!), la date_création est bien sûr une DATE. num_personne est une clé étrangère référençant l’attribut num_personne de la table Personne.





CREATE TABLE Lien ( URL VARCHAR(255) NOT NULL, date_creation DATE, description TEXT, num_personne MEDIUMINT UNSIGNED, PRIMARY KEY(URL), FOREIGN KEY(num_personne) REFERENCES Personne(num_personne));


− La Base “Liens” : la table Lien et la table Thematique (1)− On peut remarquer que plutôt que d’avoir une table thématique, on pourrait introduire dans la table Lien, un attribut thematique de TYPE ENUM comme suit :





CREATE TABLE Lien ( URL VARCHAR(255) NOT NULL, date_creation DATE, description TEXT, num_personne MEDIUMINT UNSIGNED, thematique ENUM (‘Informatique’,‘Sport’,‘Travail’), PRIMARY KEY(URL), FOREIGN KEY(num_personne) REFERENCES Personne(num_personne));


− La Base “Liens” : la table Lien et la table Thematique (2)− La définition précédente apporte certaines limitations : le nombre et le nom des thématiques sont fixés à la création de la relation (CREATE TABLE). − Bien sûr, il est toujours possible de modifier la définition de la table (par ALTER TABLE) pour modifier ou ajouter une thématique. Cependant cela ne sera pas aisé.

− Utilisation de tables supplémentaires− On peut ainsi imaginer que le client souhaite une interface web qui permette par exemple à un administrateur d’ajouter, de renommer ou de supprimer des thématiques. − Pour cela on passe par la création d’une nouvelle relation, la table Thematique, comme prévu dans notre modèle relationnel.− Pour faire le lien entre la table Lien et la table Thematique, nous aurons une 3ème relation Est_Associe_A.






− La Base “Liens” : les tables Thematique et Est_Associe_A





CREATE TABLE Thematique ( num_thematique MEDIUMINT UNSIGNED AUTO_INCREMENT NOT NULL, nom VARCHAR(50), description TEXT, PRIMARY KEY(num_thematique));CREATE TABLE Est_Associe_A ( URL VARCHAR(255) NOT NULL, num_thematique MEDIUMINT UNSIGNED NOT NULL, PRIMARY KEY(URL, num_thematique), FOREIGN KEY(URL) REFERENCES Liens(URL), FOREIGN KEY(num_thematique) REFERENCES Thematique (num_thematique));


− CREATE TABLE− La création d’une relation utilise la commande CREATE TABLE (comme nous venons de le voir)− On peut indiquer que l’on ne souhaite créer qu’une table “temporaire”, qui n’existera que le temps de la connexion au serveur (après, elle sera détruite).

− Mot-clé : TEMPORARY. − En l’absence de cette option, la table sera permanente à moins d’être détruite par la commande DROP TABLE.

− L’option IF NOT EXIST permet de ne créer une table que si une table de même nom n’existe pas déjà.− Il est possible de donner la liste de tous les attributs, clés et index de la table entre parenthèse.

MySQL – Création de table (1)Plan de cours




CREATE [TEMPORARY] TABLE <nom_relation> [IF NOT EXISTS] (<nom_attribut> TYPE_ATTRIBUT [OPTIONS],…

)


− CREATE TABLE … SELECT− Il est possible de créer une table dynamiquement à partir des résultats d’une requête SELECT :

CREATE TABLE <new-tabl> SELECT <col1,…,coln> FROM <nom_tabl> WHERE <contrainte_a_respecter> ;

− On peut copier la totalité du contenu d’une table (pas de clause WHERE) :

CREATE TABLE <new_tabl> SELECT * FROM <nom_tabl>;

− On peut créer une copie vide en ajoutant une clause WHERE “toujours fausse”.

CREATE TABLE <new-tabl> SELECT <col1,…,coln> FROM nom_tabl WHERE 1 = 0;






− CREATE TEMPORARY TABLE … SELECT−On peut également créer des tables temporaires de la sorte, par exemple pour faire une copie temporaire d’une table ou en créer une copie “vide”

CREATE TEMPORARY TABLE <new-tabl> SELECT * FROM <nom_tabl>;CREATE TABLE <new-tabl> SELECT * FROM nom_tabl WHERE 1 = 0;− ALIAS

− Une erreur risque de se produire si on sélectionne des colonnes de même nom dans des tables différentes.− mot-clé : AS

− On considère 2 tables t1 et t2, comportant toutes les 2 une colonne c. On veut créer une table représentant toutes les combinaisons de leurs lignes. On utilise des alias pour attribuer un nom unique aux colonnes de la nouvelle table :

CREATE TABLE t3 SELECT t1.c AS c1, t2.c AS c2 FROM t1, t2;






CREATE TABLE Personne ( num_personne MEDIUMINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nom VARCHAR(50), prenom VARCHAR(50));

− PRIMARY KEY− Une clé primaire doit toujours être non-nulle et unique− Mot-clé : PRIMARY KEY utilisable de 2 manières

MySQL – Clé primaire (1) Plan de cours



Pratique des BDDLangage SQLMySQL CREATE TABLE Personne

( num_personne MEDIUMINT UNSIGNED AUTO_INCREMENT, nom VARCHAR(50), prenom VARCHAR(50), PRIMARY KEY(num_personne));


− Cas d’une clé primaire multi-attributs− Quand on a plus de 2 attributs ‘clé primaire’, on utilise la forme suivante :

MySQL – Clé primaire (2) Plan de cours




CREATE TABLE Est_Associe_A ( URL VARCHAR(255) NOT NULL , num_thematique MEDIUMINT UNSIGNED NOT NULL , PRIMARY KEY(URL, num_thematique), FOREIGN KEY(URL) REFERENCES Liens (URL), FOREIGN KEY(num_thematique) REFERENCES Thematique(num_thematique));


CREATE TABLE Personne ( num_personne MEDIUMINT UNSIGNED AUTO_INCREMENT UNIQUE NOT NULL, nom VARCHAR(50), prenom VARCHAR(50));

− Unicité− Pour définir qu’un attribut est unique :

− Mot-clé : UNIQUE− Valeur Nulle

− Pour imposer qu’un attribut ne peux jamais prendre la valeur nulle :

− Mot-clé : NOT NULL− Remarque

− Quand on définit un attribut comme clé primaire avec PRIMARY KEY, il est considéré comme UNIQUE et NOT NULL.

MySQL – Unicité et valeur nulle (1)Plan de cours





− Valeur Nulle − Pour imposer qu’un attribut ne peux jamais prendre la valeur nulle on utilise le mot-clé NOT NULL− Dans ce cas, si malgré tout, aucune valeur n’est fournie, c’est une valeur par défaut qui est utilisée

− celle déclarée lors de la création de la relation est automatiquement affectée à cet attribut dans l’enregistrement, si elle existe.− Si aucune valeur par défaut n’est déclarée :

− la chaîne vide ‘’’’ sera affectée à l’attribut s’il est d’un type “chaîne de caractères” − la valeur zéro 0 s’il est d’un type numerique− la date nulle 0000-00-00 et/ou l’heure nulle 00:00:00 s’il est d’un type Date ou heure

− Si on autorise la valeur nulle on utilise le mot clé NULL

MySQL – Unicité et valeur nulle (2)Plan de cours





− Valeur par défaut : DEFAULT− Pour donner une valeur par défaut à un attribut, on utilise l’option DEFAULT.− Lors de l’ajout d’un enregistrement cette valeur sera affectée à l’attribut si aucune valeur n’est donnée.− Remarque :

−Les attributs de type chaîne de caractères”des familles TEXT et BLOB ne peuvent pas avoir de valeur par défaut.

MySQL – Valeur par défautPlan de cours



Pratique des BDDLangage SQLMySQL CREATE TABLE Personne

( num_personne MEDIUMINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nom VARCHAR(50), prenom VARCHAR(50), telephone DECIMAL(10,0) DEFAULT ‘0123456789’ );


− Unicité d’un attribut ou d’un groupe d’attribut : UNIQUE− Pour donner une valeur par défaut à un attribut, on utilise l’option DEFAULT.− Pour interdire l’apparition de doublon pour un attribut, on utilise l’option UNIQUE.− Syntaxe :

− UNIQUE(<liste des attributs>)− Pour interdire tout doublon de l’attribut nom dans la table Personne : UNIQUE(nom)

MySQL – Unicité (1)Plan de cours




AlexTerieurAgatheZeblouseSophieFonsecAlainTerieurprenomnom

Enregistrement impossible car le nom Terieur est un doublon pour la colonne nom


− Unicité d’un attribut ou d’un groupe d’attribut : UNIQUE− Pour interdire les doublons sur l’attribut nom mais les interdire aussi sur prenom, tout en les laissant indépendants :

UNIQUE(nom)UNIQUE(prenom)





CREATE TABLE Personne ( num_personne MEDIUMINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nom VARCHAR(50), prenom VARCHAR(50), UNIQUE(nom), UNIQUE(prenom));

AlexTerieurAgatheZepoheur

AgatheZeblouseSophieFonsecAlainTerieurprenomnom

Ajout impossible car le prénomAgathe est un doublon

Ajout impossible car le nomTerieur est un doublon


− Unicité d’un attribut ou d’un groupe d’attribut : UNIQUE− Pour interdire tout doublon à un ensemble d’attributs (tuple), on passe en paramètre à UNIQUE la liste des attributs concernés.− Par exemple :

UNIQUE(nom,prenom)





CREATE TABLE Personne ( num_personne MEDIUMINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nom VARCHAR(50), prenom VARCHAR(50), UNIQUE(nom,prenom));

AlexTerieurAlainTerieur

AgatheZeblouseSophieFonsecAlainTerieurprenomnom

Enregistrement interdit car Le tuple (Terieur, Alain) est un doublon


− Indexation et recherche d’informations− Lors de la recherche d’informations dans une relation, MySQL parcours la table correspondante dans n’importe quel ordre.

−Dans le cas d’un grand nombre de lignes, cette recherche peut-être très très longue du fait du parcours de TOUTE la table.

− Pour pallier ce problème, une optimisation possible et fortement recommandée, est l’utilsiation d’index.− La création d’un index associé à un attribut ou à un ensemble ordonné d’attributs crée une liste ordonnée des valeurs de ces attributs et de l’adresse de la ligne associée.

− C’est sur les valeurs de cette liste que se font les recherches et les tris. − Les algorithmes de recherche et de tri sur des ensembles ordonnées sont beaucoup plus rapides.

MySQL – Index (1)Plan de cours





− Indexation et recherche d’informations− Grâce aux index, on gagne donc en temps d’accès aux informations

− En contrepartie les mises à jour (insertion, suppression, modification de clé) sont plus coûteuses en temps.

− On crée des index sur les attributs qui sont les plus sollicités par les recherches ou utilisés comme critère de jointure. − Par contre, on évitera d’indexer les attributs qui contiennent peu de valeurs différentes les unes des autres et ceux dont les valeurs sont très fréquemment modifiées.

− Clé primaire, attribut unique et index−Il faut savoir que le fait de déclarer des attributs uniques ou clés primaires crée des index. Néanmoins on peut indexer une colonne sans pour autant qu’elle soit UNIQUE ou PRIMARY KEY.






− Ajout d’un index dans une table existante− ALTER TABLE

ALTER TABLE <nom_tabl> ADD INDEX <nom_index> (<liste_colonnes>);ALTER TABLE <nom_tabl> ADD UNIQUE <nom_index> (<liste_colonnes>);ALTER TABLE <nom_tabl> ADD PRIMARY KEY (<liste_colonnes>);

− CREATE INDEX CREATE UNIQUE INDEX <nom_index> ON <nom_tabl> (<liste_colonnes>);

CREATE INDEX <nom_index> ON <nom_tabl> (<liste_colonnes>);






− Ajout d’un index à la création d’une table− 3 manières de le faire





CREATE TABLE <nom_tabl>( … INDEX <nom_index> (<liste_colonnes>), UNIQUE <nom_index> (<liste_colonnes>), PRIMARY KEY (<liste_colonnes>) …); CREATE TABLE Personne

( num_personne MEDIUMINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nom VARCHAR(50), prenom VARCHAR(50), INDEX i_prenom (prenom), UNIQUE(nom));


− Ajout d’un index à la création d’une table− Pour les types chaines de caractères différents de ENUM et SET, on peut ne prendre en compte pour l’index que les “n” caractères de gauche de la colonne. − INDEX idx_nom (nom(3))

− A savoir − Un index peut porter sur 15 colonnes maximum.− Une table peut posséder au maximum 16 index.− Un index peut avoir une taille d’au maximum 256 octets et ne doit porter que sur des attributs NOT NULL.






− Supprimer une table− Commande DROP TABLE − Elle prend en paramètre le nom de la table à supprimer.

− Sa définition et toutes les données qu’elle contient sont alors supprimées.

− Syntaxe− DROP TABLE <relation>

− Exemple :− DROP TABLE Personne

− Supprimer pour corriger ?− Quand on s’aperçoit qu’une relation a été mal définie on peut la supprimer et la reconstruire correctement − On peut également la modifier.

− Cela peut éviter de perdre les données qu’elle contient.

MySQL – Suppression de relationPlan de cours





− Modifier une table− Commande ALTER TABLE que nous avons déjà vu− Comme nous l’avons vu dans les transparents précédents il est possible d’ajouter un index ou une clé primaire mais également de :

− ajouter/supprimer un attribut− supprimer une clé primaire− ajouter une contrainte d’unicité (interdire les doublons)− changer la valeur par défaut d’un attribut− changer totalement la définition d’un attribut− changer le nom de la relation− supprimer un index

MySQL – Modification de relationPlan de cours





− Modifier une table : ajout d’un attribut− Syntaxe

ALTER TABLE <relation> ADD <colonne> <definition_colonne> [FIRST | AFTER attribut];

− Exemple ALTER TABLE Personne ADD telephone DECIMAL(10,0);

ALTER TABLE Personne ADD telephone DECIMAL(10,0) FIRST;

ALTER TABLE Personne ADD telephone DECIMAL(10,0) AFTER prenom ;

MySQL – Ajouter un attributPlan de cours





− Modifier une table : suppression d’un attribut− Syntaxe

ALTER TABLE <relation> DROP <colonne>;− Exemple

ALTER TABLE Personne DROP prenom ;

MySQL – Supprimer un attributPlan de cours




CREATE TABLE Personne ( num_personne MEDIUMINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nom VARCHAR(50), prenom VARCHAR(50), UNIQUE(nom,prenom));

AlexTerieurAgatheZeblouseSophieFonsecAlainTerieurprenomnom

ALTER TABLE Personne DROP prenom

TerieurZeblouseFonsecTerieurnom

Refus d’opérer la suppression, car cela contredirait la contrainte d’unicité qui reste sur l’attribut nom.


− Modifier une table : ajout d’une clé primaire− On ne peut ajouter une clé primaire que si la relation n’en possède pas déjà une.−Syntaxe

ALTER TABLE <relation> ADD PRIMARY KEY (<colonne>);

− Exemple (si on considère qu’elle n’a pas encore de clé primaire)

ALTER TABLE Personne ADD PRIMARY KEY (nom,prenom);

MySQL – Ajouter une clé primairePlan de cours





− Modifier une table : suppression d’une clé primaire− Comme une clé primaire est unique, il n’y a aucune ambiguïté lors de la suppression.−Syntaxe

ALTER TABLE <relation> DROP PRIMARY KEY;− Exemple

ALTER TABLE Personne DROP PRIMARY KEY;− S’il n’y a aucune clé primaire lorsque cette commande est exécutée, aucun message d’erreur ne sera généré

− La commande est simplement ignorée.

MySQL – Supprimer une clé primairePlan de cours





− Modifier une table : ajout d’une contrainte d’unicité− Facultativement, on peut donner un nom à la contrainte d’unicité. − Cette contrainte peut s’appliquer à plusieurs attributs.− Si les valeurs déjà présentes dans la relation sont en contradiction avec cette nouvelle contrainte, alors cette dernière n’est pas appliquée et une erreur est générée.− Syntaxe

ALTER TABLE <relation> ADD UNIQUE [<nom_contrainte>](<attributs>);

− Par exemple pour interdire les doublons sur le numéro de téléphone

ALTER TABLE Personne ADD UNIQUE u_tel (telephone);

MySQL – Ajouter une contrainte d’unicitéPlan de cours





− Modifier une table : modifier la valeur par défaut d’un attribut

− Attention aux types qui n’acceptent pas de valeur par défaut (les type des familles BLOB et TEXT).− Syntaxe

ALTER TABLE <relation> ALTER <attribut> SET DEFAULT <valeur>;ALTER TABLE <relation> ALTER <attribut> DROP DEFAULT;

− Par exemple − pour changer la valeur par défaut sur le numéro de téléphone

ALTER TABLE Personne ALTER telephone SET DEFAULT “9999999999”;

− pour supprimer la valeur par défaut sur le numéro de téléphone

ALTER TABLE Personne ALTER telephone DROP DEFAULT;− Remarque : le changement ou la suppression n’affecte en rien les enregistrements qui ont eu recours à cette valeur lors de leur insertion.

MySQL – Changer la valeur par défautPlan de cours





− Modifier une table : changer la définition d’un attribut− Pour changer la définition d’un attribut sans le renommer

ALTER TABLE <relation> MODIFY <nom_attribut> <definition>;

− Pour changer la définition de l’attribut en le renommantALTER TABLE <relation> MODIFY <nom_attribut> <nouveau_nom_attribut> <definition>;

− ExempleALTER TABLE Personne MODIFY nom VARCHAR(40);ALTER TABLE Personne MODIFY telephone num_phone DECIMAL(12,0);− Attention

− Si le nouveau type appliqué à l’attribut est incompatible avec les valeurs des enregistrements déjà présents dans la relation, alors elles risquent d’êtres modifiées ou remises à zéro.

MySQL – Modifier la définition d’un attributPlan de cours





− Modifier une table : changer son nom− Pour changer le nom d’une table

ALTER TABLE <relation> RENAME <nouveau_nom_relation>;

− ExempleALTER TABLE Personne RENAME People;

MySQL – Changer le nom d’une relationPlan de cours





− Modifier une table : supprimer un index− Syntaxe

ALTER TABLE <relation> DROP INDEX <index>;− Exemple

ALTER TABLE Personne DROP INDEX i_prenom;

MySQL – Supprimer un indexPlan de cours





− Ajouter un enregistrement− Ajouter un enregistrement revient à ajouter une ligne à la table.− Pour chacun des attributs de la relation, il faudra préciser une valeur correspondante.

− Si certaines valeurs sont omises lors de la création, ce sont les valeurs par défaut qui sont utilisées.− Si aucune valeur par défaut n’a été définie, MySQL met :

− 0 pour un attribut correspondant à un type nombre, − la chaine vide “” pour attrribut d’un type une chaîne, − 0000-00-00 pour attribut de type date − 00:00:00 pour une heure− 00000000000000 pour un timestamp.

MySQL – Ajouter un enregistrement (1)Plan de cours





− Ajouter un enregistrement : INSERT− Ajouter un enregistrement revient à ajouter une ligne à la table.− Pour chacun des attributs de la relation, il faudra préciser une valeur correspondante.

INSERT [IGNORE] [INTO] <relation>(<Liste_attributs>) VALUES (<liste_valeurs>);

− ExempleINSERT INTO Personne(num_personne,nom,prenom) VALEURS (NULL,”Terieur”,”Alain”);INSERT IGNORE INTO Personne(num_personne,nom,prenom) VALEURS (NULL,”Terieur”,”Alex”);− Option IGNORE

− Si IGNORE est indiquée, les tuples dupliquant les valeurs pour des clés UNIQUE dans des tuples existants sont rejetées. Si IGNORE n’apparait dans cette situation, une erreur se produit et plus aucun tuple n’est inséré.






− Ajouter un enregistrement : REPLACE− On peut utiliser également REPLACE qui est identique à INSERT sauf si le tuple à ajouter contient une valeur qui existe déjà dans un autre tuple pour un index UNIQUE.

− Dans ce cas, l’ancienne valeur est supprimée avant que la nouvelle ne soit insérée. − Pour cette raison il n’y a pas d’option IGNORE chacun des attributs de la relation, il faudra préciser une valeur correspondante.

REPLACE [INTO] <relation>(<Liste_attributs>) VALUES (<liste_valeurs>);

− ExempleREPLACE INTO Personne(num_personne,nom,prenom) VALEURS (NULL,”Terieur”,”Alain”);REPLACE Personne(num_personne,nom,prenom) VALEURS (NULL,”Terieur”,”Alex”);− Note : l’insertion d’une valeur NULL dans une “colonne AUTO_INCREMENT” permet à MySQL de générer automatiquement la séquence de nombres suivantes et de l’insérer dans le champ correspondant. L’insertion de la valeur 0 ou de ne pas spécifier de valeur à le même effet.






− Modifier un enregistrement : UPDATE− Pour modifier une ou plusieurs valeurs de colonne dans un ou plusieurs tuples d’une relation, on utilise UPDATE.

− On précise les éventuels critères de sélection des tuples avec WHERE, − On indique les valeurs de colonnes à modifier avec SET

− Syntaxe :UPDATE <relation> SET <attribut1=valeur1> [ ,<attributn=valeurn>] [ WHERE <condition> ][LIMIT <n>];

− Exemple :UPDATE Personne SET num_phone=‘0505151515’ WHERE nom=‘Terieur’ AND prenom=‘Alex’;UPDATE Personne SET num_phone=‘0406151515’, num_fax=‘0406151510’ WHERE nom=‘Terieur’ AND prenom=‘Alain’;

MySQL – Modifier un enregistrement (1)Plan de cours





− Modifier un enregistrement : UPDATE− Pour appliquer la modification à tous les enregistrements de la relation, il suffit de ne pas mettre de clause WHERE.− LIMIT <n>, où n est un chiffre entier permet de n’appliquer la commande qu’aux n premiers enregistrements satisfaisant la condition définie par WHERE. − Il est possible de modifier la valeur d’un attribut relativement à sa valeur déjà existante.− Exemple :

− UPDATE Personne SET age=age+1;

MySQL – Modifier un enregistrement (2)Plan de cours





− Supprimer un enregistrement : DELETE− Rappelons que la suppression est définitive …− Syntaxe :

DELETE FROM <relation> [ WHERE <condition> ][LIMIT <n> ];

− Exemple :DELETE FROM Personne WHERE nom=‘Fonsec’ AND prenom=‘Sophie’;

− Pour vider une table de tous ces éléments, on donne pas de clause WHERE. − Exemple :

DELETE FROM Personne ;

MySQL – Supprimer un enregistrement Plan de cours





− Sélectionner un enregistrement : SELECT− Syntaxe générale

SELECT [ DISTINCT ] <liste_selection> [ INTO OUTFILE <fichier> ] [ FROM <liste_table> ] [ WHERE <critere_principale> ] [ GROUP BY <critere_groupement_col>

[ ASC | DESC ] ] [ HAVING <condition> ] [ ORDER BY <critere_tri_col> ] [ LIMIT [<a>,] <b> ]

− ExempleSELECT nom,prenom FROM PersonneWHERE prenom LIKE ‘%Alex%’

MySQL – Sélectionner un enregistrement (1) Plan de cours





− Signification des éléments du SELECTMySQL – Sélectionner un enregistrement (2)

Plan de cours




Permet de limiter le nombre de lignes du résultatLIMIT

Permet de définir l’ordre (ASCendant par défaut ou DESCendant) des résultats comment trier les résultats

ORDER BY

Définit un ou des critères de sélection sur des ensembles de valeurs d’attributs après groupement.

HAVING

Permet de grouper les lignes de résultats selon un ou des attributs Comment regrouper les lignes

GROUP BY

Définit le(s) condition(s) que les tuples doi(ven)t satisfaire

WHERE

Spécifie la ou les relations sur lesquelles effectuer la sélection où sélectionner les tuples

FROMSpécifie le fichier sur lequel effectuer la sélection.INTO OUTFILEPermet d’ignorer les doublons de ligne de résultat.DISTINCT

Spécifie les attributs dont on souhaite connaître les valeurs les colonnes à sélectionner

SELECTDescriptionNom


− Exemple progressif (1)− Pour sélectionner tous les enregistrements d’une relation

SELECT * FROM <une_relation> − Pour sélectionner toutes les valeurs pour une suel colonne d’une relation

SELECT <une_colonne> FROM <une_relation>− Pour en plus éliminer les doublons

SELECT DISTINCT <une_colonne> FROM <une_relation>

− Pour en plus trier les résultats par ordre croissantSELECT DISTINCT <une_colonne> FROM <une_relation> ORDER BY <une_colonne> ASC

− Pour se limiter en plus au <n> premier résultatSELECT DISTINCT <une_colonne> FROM <une_relation> ORDER BY <une_colonne> ASC LIMIT <n>






− Exemple progressif (2)− Pour se limiter en plus au <n> premier résultat

SELECT DISTINCT <une_colonne> FROM <une_relation> ORDER BY <une_colonne> ASC LIMIT <n>

− Pour ne récupérer que les résultats venant de tuple satisfaisant à certaines conditions

SELECT DISTINCT <une_colonne> FROM <une_relation> WHERE <condition> ORDER BY <une_colonne> ASC LIMIT <n>






− Exemple





26AgatheZepoheur5

36AgatheZeblouse4

18SophieFonsec3

21AlexTerieur2

21AlainTerieur1

ageprenomnomid_personne

Personne

Zepoheur

Zeblouse

Fonsec

Terieur

Terieur

nom

SELECT nom FROM Personne


− Exemple





26AgatheZepoheur5

36AgatheZeblouse4

18SophieFonsec3

21AlexTerieur2

21AlainTerieur1


Personne

Zepoheur

Zeblouse

Fonsec

Terieur

nom

SELECT DISTINCT nom FROM Personne


− Exemple





26AgatheZepoheur5

36AgatheZeblouse4

18SophieFonsec3

21AlexTerieur2

21AlainTerieur1


Personne

Zepoheur

Zeblouse

Terieur

Fonsec

nom

SELECT DISTINCT nom FROM Personne ORDER BY nom ASC


− Exemple





26AgatheZepoheur5

36AgatheZeblouse4

18SophieFonsec3

21AlexTerieur2

21AlainTerieur1


Personne

Terieur

Fonsec

nom

SELECT DISTINCT nom FROM Personne ORDER BY nom ASC LIMIT 2


− Exemple





26AgatheZepoheur5

36AgatheZeblouse4

18SophieFonsec3

21AlexTerieur2

21AlainTerieur1


Personne

Fonsec

nom

SELECT DISTINCT nom FROM Personne WHERE nom <>’Terieur’ ORDER BY nom ASC

LIMIT 2


− Optimisation − Après la suppression de nombreux tuples d’une table contenant des index, les index des tuples supprimés sont conservés

− Cela a un impact sur les sélections et la vitesse à laquelle elle se réalise.

−Pour supprimer ces index obsolètes et vider les « trous », il faut “optimiser” la relation.− Syntaxe

OPTIMIZE TABLE <relation>− Exemple

OPTIMIZE TABLE Personne

MySQL – Optimisation Plan de cours





− Jointure interne avec MySQL − Nous avons vu un exemple de jointure, dans la partie de ce cours sur l’algèbre relationnelle :

− Une jointure permet de concaténer deux relations (ou plus) en prenant un attribut comme pivot.

− Il est possible de concaténer deux relations sur plusieurs attributs à la fois, ou même de concaténer X relations sur Y attributs.

− La jointure étant une opération très classique, il a été créé en MySQL une syntaxe spéciale plus rapide que celle utilisant le WHERE :

− JOIN

MySQL – Jointure (1) Plan de cours




SELECT nom, adresseFROM Etudiant,MelWHERE Etudiant.num_etudiant = Mel.num_etudiant


− Jointure interne avec MySQL − Nous avons vu un exemple de jointure, dans la partie de ce cours sur l’algèbre relationnelle :

− Ainsi la jointure précédente peut s’écrire avec le JOIN :SELECT nom, adresseFROM Etudiant INNER JOIN MelUSING (num_etudiant);

− ce qui signifie que les deux relations Etudiant et Mel sont concaténées (INNER JOIN) en utilisant (USING) l’attribut num_etudiant. − La syntaxe USING permet de lister les attributs devant servir de pivot. −Dans ce cas, ces attributs doivent porter le même nom dans chacune des tables devant être concaténées.





SELECT nom, adresseFROM Etudiant,MelWHERE Etudiant.num_etudiant = Mel.num_etudiant ;


− Jointure interne avec MySQL − Si les attributs ne portent pas le même nom, il faut utiliser ON :−Ainsi la jointure précédente peut s’écrire avec le JOIN :

SELECT nom, adresseFROM Etudiant INNER JOIN MelON Etudiant.num_etudiant = Mel.num_etudiant ;

− Remarque − la jointure interne (INNER JOIN) élimine tous les enregistrements de la table Etudiant pour lesquels il n’existe pas d’enregistrement dans la table Mel.− Pour pallier cet incovénient , il faut utiliser une jointure dite externe.






− Jointure externe avec MySQL − sa syntaxe est :

SELECT < attributs>FROM <table gauche> LEFT | RIGHT | FULL OUTER JOIN <table droite> ON <condition de jointure>

− Ainsi la jointure précédente s’écrit avec une jointure externe :

SELECT Etudiant.nom, Mel.adresseFROM Etudiant LEFT OUTER JOIN MelON Etudiant.num_etudiant = Mel.num_etudiant

− Elle renvoie tous les enregistrements d’Etudiant, même ceux qui n’ont pas de correspondance dans la table Mel.






− Jointure gauche avec MySQL − C’est un cas particuliers de jointure externe− sa syntaxe est :

SELECT < attributs>FROM <table_gauche> LEFT [OUTER] JOIN <table_droite> ON <condition_de_jointure>

− La jointure gauche est très pratique pour extraire de la table gauche uniquement les lignes qui n’apparaissent pas dans la table droite.

− Il suffit d’ajouter une clause WHERE qui recherche dans la table de droite les lignes contenant des valeurs nulles

− Ainsi, si on recherche tous les étudiants qui n’ont pas de mél on écrira :

SELECT Etudiant.nomFROM Etudiant LEFT JOIN MelON Etudiant.num_etudiant = Mel.num_etudiant WHERE Mel.adresse IS NULL






− Jointure et subselect (1)− Il manque à MySQL jusqu’à la version 3.24 les subselects.

− Un subselect est une requête du type SELECT < attributs>FROM <table_X> WHERE <attribut> IN (SELECT < attributs>

FROM <table_Y> WHERE <condition> )

− On peut réécrire ces requêtes en subselect par une requête utilsant une jointure.






− Jointure et subselect (2)− Si la requête en subselect est de la forme :

SELECT * FROM table_1 WHERE col1 IN (SELECT col2a

FROM table_2 WHERE col2b = valeur)

− On peut la réécrire avec une jointure comme suit SELECT table1.* FROM table_1, table_2 WHERE table1.col1 = table2.col2a

AND table_2.col2b = valeur






− Jointure et subselect (3)− Si la requête en subselect est de la forme :

SELECT * FROM table_1 WHERE col1 NOT IN | NOT EXISTS (SELECT col2

FROM table_2);− On peut la réécrire avec une jointure comme suit

SELECT table1.* FROM table_1 LEFT JOIN table2 ON table1.colonne1 = table2.colonne2 WHERE table2.col2 IS NULL;

− Ce code suppose que table2.colonne2 est déclaré NOT NULL.






− Les fonctions de MySQL − Ces fonctions sont propres à MySQL

− On en trouve l’équivalence la plupart du temps dans les autres SGBD mais avec des syntaxes différentes.

− On se contente ici d’énumérer les fonctions les plus courantes.

− Le manuel de MySQL peut vous fournir en cas de besoin la liste détaillée de toutes les fonctions disponibles dans votre version du serveur MySQL.− Voir par exemple le fichier docMySQL3_1_23_v4.zip sur http://up.univ-mrs.fr/wws/d_read/pedago-arles/Enseignements/BDD/ pour ce manuel.

− Ces fonctions sont à ajouter à vos requêtes dans un SELECT, WHERE, GROUP BY ou encore HAVING.

MySQL – Les fonctions (1) Plan de cours





− Les fonctions et opérateurs de base de MySQL − les parenthèses ( ), − les opérateurs arithmétiques (+, -, *, /, %), − les opérateurs binaires (<, << , >, >>, |, &), − les opérateurs logiques qui retournent 0 (faux) ou 1 (vrai)

− AND, OR, NOT, BETWEEN, IN, − les opérateurs relationnels (<, <=, =, >, >=, <>).− Les opérateurs et les fonctions peuvent êtres composés entre eux pour donner des expressions complexes.






− Exemple d’utilisation des fonctions et opérateurs de base − Ensemble des nom de produit dont le prix est inférieur à 100.5 euros

SELECT Produit.nomFROM ProduitWHERE Produit.prix <= 100.5

− Ensemble des noms des étudiants dont l’âge est compris entre 20 et 24 ans

SELECT nom, prenomFROM EtudiantWHERE age BETWEEN 20 AND 16






− Exemple d’utilisation des fonctions et opérateurs de base − Ensemble des modèles de voiture dont la couleur est dans la liste rouge, blanc, noir.

SELECT modeleFROM VoitureWHERE couleur IN(‘rouge’,’blanc’,’noir’)

−Ensemble des modèles dont la couleur est tout sauf rose ou violet

SELECT modeleFROM VoitureWHERE couleur NOT IN (‘rose’,’violet’)






− Comparaison de chaînes de caractères : LIKE− LIKE permet de comparer 2 chaînes.

− le caractère % a une signification spéciale : il remplace 0, 1 ou plusieurs caractères (un peu comme le caratcère * dans les expressions régulières) n’importe lesquels.− le caractère _ est lui aussi spécial : il remplace 1 seul caractère, n’importe lequel.− Par exemple, si on recherche tous les étudiants don’t le prénom commence par ‘Jean’ comme Jean-Pierre, Jean-Paul, etc.

SELECT prenom, nomFROM EtudiantWHERE prenom LIKE ‘Jean%’;

− Pour utiliser les caractères spéciaux, il faut les despécialiser en les faisant précéder par l’anti-slash \ :

SELECT *FROM ProduitWHERE code LIKE ‘\_XE%’;

− Recherche de tous les produits dont le code commence par _XE






− Les fonctions mathématiques (1)





Tronque le nombre X à la Yème décimaleTRUNCATE(X,Y)

Renvoie une valeur réelle aléatoire entre 0.0 et 1.0.

RAND(),RAND(X)Retourne X à la puissance YPOW(X,Y)

Fonctions mathématiques trigonométriques, l’exponentielle et le logarithme. Nombre pi (PI())

EXP(X),LOG(X),SIN(X), COS(X),TAN(X),PI()

Arrondi à l’entier le plus procheROUND(X)Arrondi à l’entier supérieurCEILING(X)Arrondi à l’entier inférieurFLOOR(X)Signe de X, retourne -1,0 ou 1SIGN(X)Valeur absolue de XABS(X)

DescriptionFonction


− Les fonctions mathématiques (2) : exemple− Pour trouver toutes les fillilales dont le chiffre d’affaire est négatif.

SELECT nomFROM FililaleWHERE SIGN(chiffre_affaire) = -1






− Les fonctions sur les chaînes de caractères (1)





Renvoie une représentation phonétique de str.

SOUNDEX(str)

Renvoie les n derniers éléments de str à partir de la position i

SUBSTRING(str,i,n)

Concaténation des argumentsCONCAT(str1, str2,…)

Position de la dernière occurrence de substr dans str, renvoie 0 si elle n’y ait pas. pos permet de préciser la position de départ de la recherche

LOCATE(substr,str)LOCATE(substr,str,pos)

Longueur de la chaine strLENGTH(str)Conversion en majusculeUPPER(str)Conversion en minusculeLOWER(str)

Suppression des espaces de début et de fin de chaîne

TRIM(str)DescriptionFonction


− Les fonctions sur les chaînes (2) : exemple− Pour afficher en majuscule le nom de tous les étudiants dont le nom ressemble à ‘dupond’

SELECT UPPER(nom)FROM EtudiantWHERE SOUNDEX(nom)= SOUNDEX(‘Dupond’);






− Les fonctions sur les dates et les heures (1)





Renvoie respectivement la date et l’heure courante dans un chaine au format ‘YYYY-MM-DD’/ ’hh:mm:ss’ ou comme un nombre au format YYYYMMDD/hhmmss selon le contexte d’utilisation.

CURDATE(), CURTIME()

TO_DAYS envoie le nombre de jour depuis l’année 0 et FROM_DAYS fait l’opération inverse.

TO_DAYS(d)FROM_DAYS(nb_jours)

Renvoie la date et l’heure courante sous forme de chaine ou de nombre selon le contexte

NOW(d)

DescriptionFonction


− Les fonctions sur les dates et les heures (2)





Pour extraire respectivement les secondes, minutes, heures, mois, année, numéro du mois dans l’année.

SECOND(d), MINUTE(d), HOUR(d), MONTH(d), YEAR(d), WEEK(d).

Retourne le jour de l’année (entre 1 et 366)

DAYOFYEAR(d)Retourne le jour du mois (entre 1 et 31)DAYOFMONTH(d)

Renvoie le jour de la semaine en chiffre (dimanche=1, lundi =2, etc.)

DAYOFWEEK(d)DescriptionFonction


− Les fonctions sur les dates et les heures (3) : exemple− Pour afficher les tires des articles parus depuis moins de 30 jours

SELECT titreFROM ArticleWHERE (TO_DAYS(NOW()) – TO_DAYS(parution)) < 30;






− Les fonctions récapitulatives (1)





Renvoie la valeur minimale ou maximale de expr pour toutes les valeurs non NULL dans les rangées sélectionnées. Elles peuvent être également utilisées avec les chaînes (renvoie la valeur la plus petite/plus grande alphabétiquement)

MIN(expr), MAX(expr)

Avec tout argument autre que « * », retourne le compte du nombre de valeurs non NULL contenues dans l’ensmeble du résultat. Avec un argument « * », renvoie le compte de toutes les rangées dans le résultat, avec des valeurs NULL ou non

COUNT([DISTINCT]col1, col2,…)

DescriptionFonction


− Les fonctions récapitulatives (2)





Renvoie la somme des expr pour toutes les valeurs non NULL dans les rangées sélectionnées.

SUM(expr)

Renvoie la valeur moyenne de expr pour toutes les valeurs non NULL dans les rangées sélectionnées.

AVG(expr)DescriptionFonction


− Les fonctions récaptitulatives (3) : exemple− Pour afficher les modèles de voitures existant en plus de 10 couleurs.

SELECT DISTINCT(modele)FROM VoitureGROUP BY modeleHAVING COUNT(couleur) > 10;

− Pour afficher le nombre d’étudiants dans la table Etudiant.SELECT COUNT(*)FROM Etudiant;

− Pour afficher le classement des produits par la valeur totale vendue.

SELECT DISTINCT Produit.nom, SUM(vente.qt*Produit.prix) AS totalFROM Produit, VenteWHERE Produit.id = Vente.produit_idxGROUP BY Produit.nom ORDER BY total;





introduction aux sgbd

Technology