Cours Bases de Données Relationnelles PDF

# Part 2 - Bases de données relationnelles ## Stockage des données - Plusieurs applications nécessitent le stockage des données pour de longues périodes - L'information peut être stockée : - Dans des fichiers - Ne nécessite aucun logiciel supplémentaire - Facile à utiliser...

# Part 2 - Bases de données relationnelles ## Stockage des données - Plusieurs applications nécessitent le stockage des données pour de longues périodes - L'information peut être stockée : - Dans des fichiers - Ne nécessite aucun logiciel supplémentaire - Facile à utiliser - Ne nécessite pas beaucoup d'espace disque - Dans des bases de données - Plus sécurisées - Les données sont mieux organisées et donc plus faciles à manipuler - Possibilité de gérer les accès concurrents ## Inconvénients des fichiers - Redondance des données et incohérence - Même information dupliquée dans plusieurs fichiers - Mise à jour dans un fichier et pas dans les autres - Difficulté à accéder aux données - Isolation des données: plusieurs fichiers, plusieurs formats - Problèmes d'intégrité - Contraintes d'intégrité codées dans des programmes - Difficile d'en ajouter ou de les changer - Problèmes d'atomicité - Si crash du système durant l'exécution d'un programme, état incohérent - Problèmes de concurrence - Anomalies si accès concurrents ## Définitions - **Base de données (BD)** : - Gros ensemble persistant de données structurées et cohérentes exploitable simultanément. - Par rapport à la mémoire humaine - Notion mathématique, formalisme - Dans le temps, pannes - Organisées, liées - Informations, typées et multimédia - Contraintes d'intégrité déclarées et forcées - Interrogation, modifications, évolution - Parallélisme des accès, partage - **Système de gestion de bases de données (SGBD)** : - Logiciel assurant définition, structuration, stockage, maintenance, mise à jour et consultation de BDs ## Exemples de BD - Gestion des personnels, étudiants, cours, inscriptions... De l'université - Système de réservation de places d'avion chez Royal Air Maroc, de places de train à la ONCF - Gestion des comptes clients de la banque - Gestion des commandes chez Amazon.com - Gestion d'une bibliothèque - Gestion des pages Web chez google.com ## Utilisateurs de BD - Plusieurs types d'utilisateur: - Utilisateurs naïfs: accèdent à la BD à travers une interface (ex. sur le web) - Utilisateurs sophistiqués: accèdent à la BD en écrivant des requêtes en langage de manipulation de données (LMD) - Programmeurs d'application: accèdent à la BD en écrivant des programmes d'application - Administrateur BD ("DBA"): donne les droits d'accès aux utilisateurs, définit la structure de stockage et l'organisation physique de la BD, surveille les performances du système, etc. ## Différents types de bases de données - **Base hiérarchique** : Contenu organisé dans une structure arborescente. - **Base en réseau** : Hiérarchique mais avec des relations transverses. - **Base relationnelle** : Informations organisées dans des matrices appelées relations ou tables. - **Base XML** : S'appuie sur le modèle fourni par XML. ## Historique (1) - **Années 60**: - Le 1er SGBD à caractère général et basé sur le modèle réseau - IBM développe IMS qui est basé sur le modèle hiérarchique - **Années 70**: - Edgar Codd définit le modèle relationnel - Obtient en 1981 le prix "ACM Turing Award" - IBM lance le SGBD "System R" (prototype) - UC Berkeley lance le SGBD "Ingres" (prototype) - Peter Chen définit le modèle entité-association - **Années 80**: - Les SGBD relationnels prototypes deviennent des systèmes commercialisés, SQL devient un standard - SGBD parallèles et distribués et SGBD orientés objet ## Historique (2) - **Années 90**: - Applications de fouille de données ("data mining") - Grands entrepôts de données ("data warehouses") - Commerce électronique ("e-commerce") - **Années 2000**: - Modèle semi-structuré (XML, XQuery) - Nouveaux types de données dans les BDR (image, texte, etc) - Administration automatisée des BD - **Fin des années 2000**: - Modèle NoSQL (Not Only SQL) basé sur le notion clé-valeur - Technologie liée au Cloud Computing - Les principaux site du Web utilisent une BD NoSQL: Facebook, Google, MySpace, Gmail, Yahoo et Amazon ## BD relationnelle : Terminologie - Une BD relationnelle est un ensemble de données organisées sous forme de tables (appelée aussi relation) qui décrivent les types des données. - En utilisant un identifiant commun (clé) entre les tables il est possible de les "relier" entre elles. - Une relation est une table comportant des colonnes (dits aussi attributs) dont le nom et le type caractérise le contenu qui sera inséré dans la table. - Les tables contiennent à leur tour des lignes (appelées aussi des enregistrements ou tuples) qui sont les vraies données. ## BD relationnelle - La base de données est un ensemble de tables. - Une table est un ensemble de lignes. - Une ligne est un ensemble de valeurs. - Exemple: Base de données "Monde" - Table 1: "table_continents" - Colonnes de la Table 1: "continent", "description" - Table 2: "liste_pays" - Colonnes de la Table 2: "continent", "pays", "carte" - Table 3: "table_villes" - Colonnes de la Table 3: "pays", "ville", "nb_habitants" ## Exemple - Imaginons que l'on veuille stocker dans notre base de données notre carnet d'adresses. On va donc créer la relation (table) Personne qui aura pour attributs : nom, prénom, email, département. - Personnes - Nom - Prénom - Email - Département ## Relation - Chaque relation ou table a un nom unique dans la BD - Une relation représente soit : - Un type d'objet d'affaires spécifique sur lequel l'entreprise désire conserver de l'information - Ex: Coureur, Course - Une association logique entre deux objets - Ex: Résultat - représente l'association logique entre les coureurs et les courses auxquelles ils ont participé - Synonymes - Relation, table, entité ## Enregistrement - Chaque ligne correspond à un enregistrement. - Chaque enregistrement est unique dans une relation. - Un enregistrement est formé par l'ensemble des valeurs des attributs décrivant une occurrence particulière de l'objet d'affaire représenté par la relation. - Chaque enregistrement contient exactement une valeur pour chaque attribut. - L'ordre des enregistrements n'a pas d'importance. - Le nombre des enregistrements est variable dans le temps. - Synonymes - Enregistrement, tuple ## Attribut - Chaque colonne d'une relation correspond à un attribut et à ses valeurs. - L'ordre des colonnes dans une relation n'a pas d'importance. - Le nom d'un attribut est unique dans une relation. - Le nom d'attribut décrit un aspect de l'objet d'affaires. - Ex: "nomcoureur" dans la relation "coureur" - Un attribut prend des valeurs à partir d'un domaine donné. - Chaque valeur d'un attribut est atomique. - Synonymes - Attribut, colonne, champ ## Langages de BD - La plupart de SGBDR sont accessibles via le langage structuré de requêtes (SQL): - Langage de Définition de Données (LDD) - Créer, modifier, et supprimer des tables - Langage de Manipulation de Données (LMD) - Insérer, modifier, supprimer des enregistrements - Extraire des données (requêtes) - Langage de Contrôle de Données (LCD) - Créer, modifier, supprimer des droits d'accès - Un programme d'application accède à la BD par: - Un langage qui supporte du SQL imbriqué (ex: Pro*C) - Une API (Application Program Interface) (ex: JDBC) qui permet d'envoyer des requêtes SQL à la BD d'une application Java ## Exemple de SGBDR - **Sharewares:** - Oracle - DB2 (IBM) - Ingres - Informix - Sybase - SQL Server (Microsoft) - 02 - **Sur micro:** - Access - Paradox - FoxPro - 4D - Windev - **Freewares:** - MySQL - Postgres - InstantDB ## Exemple de SGBD: Oracle - **Avantages:** - Supporte les très grosses bases, pérennité (40% de part de marché) - Interface utilisateur très riche - Procédures en PL/SQL ou JAVA - Flashback query, système de vues, services web... - **Inconvénients:** - Coût excessif - Fort demandeur de ressources - Gestion des rôles et des privilèges instable... ## Exemple de SGBD: MySQL - **Avantages:** - Très courant chez les hébergeurs, très bonne intégration Apache/PHP - OpenSource - Version cluster (plusieurs serveurs reliés entre eux) - Facilité de prise en main - **Inconvénients:** - Faible richesse fonctionnelle - Peu robuste avec des gros volumes - Pas d'héritage de tables. - C'est le SGBD qu'on va utiliser dans le cadre de ce cours ! ## Exemple de SGBD: PostgreSQL - **Avantages:** - OpenSource - Fiable et relativement performant - Riche fonctionnellement - Simple d'utilisation et d'administration - Héritage de tables - **Inconvénients:** - Supporte les bases de moyenne importance - Sauvegardes peu évoluées - Gestion des permissions limitées ## Exemple de SGBD: SQLite - **Avantages:** - OpenSource - Le + petit SGBDR - Simple d'utilisation et d'administration - Installation aisée - **Inconvénients:** - Fonctionnalités minimales - Pas d'intégrité référentielle - LDD très limité (à part ajouter une colonne) ## Structured Query Language (SQL) ## SQL - Le Structured Query Language est un langage standardisé qui permet d'effectuer des opérations sur des bases de données. - Il se compose de: 1. Langage de Définition de Données (LDD) permettant de gérer les structures de la base 2. Langage de Manipulation de Données (LMD) pour interagir avec les données. 3. Langage de Contrôle de Données (LCD) pour Créer, modifier, supprimer des droits d'accès - Attention: certaines syntaxes ou fonctions sont propres au système de gestion de base de données utilisé. ## SQL - Langage de Définition de Données - Langage de manipulation de données ## Le langage de définition de données - Les commandes principales sont : - Pour les bases : create, alter, drop - Pour les tables : create, alter, rename, drop - Pour les indexes : create, drop - La gestion des vues, des triggers, des events - Ces fonctions sont assez peu utilisées car des outils tels que phpMyAdmin permettent d'effectuer directement ces opérations. ## Syntax: Identificateurs - Les noms de bases, relations, attributs, index et alias sont constitués de caractères alphanumériques plus _ et $. - Un nom comporte au maximum 64 caractères. - MySQL code le nom des bases et des tables directement dans le système de fichiers ! Attention donc à la casse.... - Le point "." est un caractère réservé utilisé comme séparateur entre le nom d'une base, celui d'une relation et celui d'un attribut: - Select basel.table2.attribut3 from basel.table2 ## Créer une BD (I) - Il est possible de créer autant de bases de données que nécessaires. - Généralement, une base regroupe toutes les données nécessaires pour un besoin fonctionnel précis. - En clair, on peut résumer : une application, une base de données. - L'interaction entre les bases de données est possible mais alourdit la syntaxe SQL. ## Créer une BD - Création d'une base de données : - CREATE DATABASE [IF NOT EXISTS] Nom_base [create_specification] - Les spécifications permettent notamment de définir le système d'encodage des caractères, et le moteur de la table (ENGINE). ## Suppression/Modification d'une BD - Suppression d'une base de données : - DROP DATABASE [IF EXISTS] db_name - Modification d'une base de données : - ALTER DATABASE db_name alter_specification [, alter_specification] ... ## Créer une table (I) - La création d'une relation utilise la commande CREATE TABLE selon la syntaxe suivante : - CREATE [TEMPORARY] TABLE nom_relation [IF NOT EXISTS] ( nom_attribut TYPE_ATTRIBUT [OPTIONS] ... ) - TEMPORARY donne pour durée de vie à la table : le temps de la connexion de l'utilisateur au serveur, après, elle sera détruite. 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 cette table que si une table de même nom n'existe pas encore. - A l'intérieur des parenthèses, il sera listé tous les attributs, clés et indexes de la table. ## Créer une table (II) - Exemple: - CREATE TABLE Personne ( nom VARCHAR(40), prenom VARCHAR(40), email VARCHAR(40), laboratoire VARCHAR(30) ) ## Types des attributs - Les attributs peuvent avoir des types très différents : - Nombre entier signé ou non - Nombre à virgule - Date et heure - Enumération - Ensemble - Il s'agit de choisir le type le plus adapté aux besoins - Les types requièrent une plus ou moins grande quantité de données à stocker: il vaut mieux choisir un type varchar pour stocker un nom qu'un type longtext. - Voir www.mysql.com pour plus de détailles ## Types des attributs (II) – entiers | nom | borne inférieure | borne supérieure | |------------------------|-------------------|-------------------| | TINYINT | -128 | 127 | | TINYINT UNSIGNED | 0 | 255 | | SMALLINT | -32768 | 32767 | | SMALLINT UNSIGNED | 0 | 65535 | | MEDIUMINT | -8388608 | 8388607 | | MEDIUMINT UNSIGNED | 0 | 16777215 | | INT\* | -2147483648 | 2147483647 | | INT UNSIGNED | 0 | 4294967295 | | BIGINT | -9223372036854775808 | 9223372036854775807 | | BIGINT UNSIGNED | 0 | 18446744073709551615 | (*) : INTEGER est un synonyme de INT. UNSIGNED permet d'avoir un type non signé. ## Types des attributs (III) – flottants - Type DECIMAL (precision, echelle) - Représente un nombre codé sur precision chiffres, avec echelle chiffres après la virgule. - echelle est optionnel et vaut 0 par défaut. - precision est optionnel si echelle n'est pas indiqué. - MySQL: valeur par défaut : 10 - Type FLOAT (precision) - Représente un nombre à virgule flottante. - precision est optionnel. - MySQL: precision en décimal, par défaut : 10 - Les types INTEGER, INT, DOUBLE,... sont des raccourcis pour des formes particulières de DECIMAL ou FLOAT ## Types des attributs (IV) – chaînes | nom | longueur | |-------------------|----------| | CHAR(M) | Chaîne de taille fixée à M, où 1<M<255, complétée avec des espaces si nécessaire. | | CHAR(M) BINARY | Idem, mais insensible à la casse lors des tris et recherches. | | VARCHAR(M) | Chaîne de taille variable, de taille maximum M, où 1<M<255, complété avec des espaces si nécessaire. | | VARCHAR(M) BINARY | Idem, mais insensible à la casse lors des tris et recherches. | | TINYTEXT | Longueur maximale de 255 caractères. | | TEXT | Longueur maximale de 65535 caractères. | | MEDIUMTEXT | Longueur maximale de 16777215 caractères. | | LONGTEXT | Longueur maximale de 4294967295 caractères. | ## Types des attributs (V) – chaînes - Les types TINYTEXT, TEXT, MEDIUMTEXT et LONGTEXT peuvent être judicieusement remplacés respectivement par TINYBLOB, BLOB, MEDIUMBLOB et LONGBLOB. - Ils ne diffèrent que par la sensibilité à la casse qui caractérise la famille des BLOB. Alors que la famille des TEXT sont insensibles à la casse lors des tris et recherches. - Les BLOB peuvent être utilisés pour stocker des données binaires. - Les VARCHAR, TEXT et BLOB sont de taille variable. Alors que les CHAR et DECIMAL sont de taille fixe. ## Types des attributs (VI) - dates et heures | nom | description | |-------------------|---------------------------------------------------------------------------------------------| | DATE | Date au format anglophone AAAA-MM-JJ. | | DATETIME | Date et heure au format anglophone AAAA-MM-JJ HH:MM:SS. | | TIMESTAMP | Affiche la date et l'heure sans séparateur: AAAAMMJJHHMMSS. | | TIMESTAMP(M) | Idem mais M vaut un entier pair entre 2 et 14. Affiche Is M premiers caractères de TIMESTAMP. | | TIME | Heure au format HH:MM:SS. | | YEAR | Année au format AAAA. | | nom | description | |-------------------|-------------| | TIMESTAMP(2) | AA | | TIMESTAMP(4) | AAMM | | TIMESTAMP(6) | AAMMJJ | | TIMESTAMP(8) | AAAAMMJJ | | TIMESTAMP(10) | AAMMJJHHMM | | TIMESTAMP(12) | AAMMJJHHMMSS | | TIMESTAMP(14) | AAAAMMJJHHMMSS | 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 ler janvier 1970), en MySQL, il est une chaîne de format comme indiqué ci-contre. ## Types des attributs (VII) - énumérations - Un attribut de type ENUM peut prendre des valeurs parmi celles définies lors de la création de la table. Ces valeurs sont exclusivement des chaines de caractères (insensibles à la casse) et peuvent être au maximum au nombre 65535. - Nom_attr ENUM ("valeur 1", "valeur 2'...") - Nom_attr ENUM ("valeur 1", "valeur 2'...") NULL - A chaque valeur est associée un index allant de 0 pour (chaine nulle) à N si N valeurs ont été définies. L'index NULL est associé à la valeur NULL - Si une sélection est faite dans un contexte numérique c'est l'index qui est renvoyé sinon c'est la valeur. ## Types des attributs (VIII) - ensembles - Un attribut de type SET peut prendre pour valeur une chaine vide, NULL ou une chaine contenant une liste de valeurs qui doivent être déclarées au moment de la définition de l'attribut. - Par exemple, un attribut déclaré comme ci: SET ("voiture","avion","train") NOT NULL - Peut prendre les valeurs suivantes : - "voiture, avion" - "train" - Mais pas NULL ni "TGV " - On ne peut définir que 64 éléments au maximum ## Options des attributs - **PRIMARY KEY** - Désigne l'attribut comme la clé primaire de la table - **NOT NULL** - Spécifie que l'attribut ne peut avoir de valeurs nulles - **UNIQUE** - Spécifie que les valeurs de cet attribut doivent être distinctes pour chaque couple de tuples. - **AUTO_INCREMENT** - Incrémentation automatique pour cet attribut si l'utilisateur ne spécifie pas de valeur pour cet attribut à l'insertion du tuple. - **DEFAULT** - Spécifie la valeur utilisée par défaut si l'utilisateur ne spécifie pas de valeur pour cet attribut à l'insertion du tuple. - **REFERENCES** - Spécifie que l'attribut correspond à la clé primaire d'une autre relation. ## Clé primaire (I) - Dans une base de données relationnelle, une clé primaire est une contrainte d'unicité qui permet d'identifier de manière unique un enregistrement dans une table. Une clé primaire peut être composée d'un ou de plusieurs attributs de la table. - Cet attribut devra ne jamais être vide, il faut donc préciser l'option NOT NULL pour le forcer à prendre une valeur de son domaine. - Il devra aussi être unique, c'est-à-dire que deux enregistrements ne pourront pas avoir une valeur identique. Il faut alors faire la déclaration suivante : UNIQUE (cle) à la suite de la liste des attributs. - Pour simplifier, on utilisera l'option PRIMARY KEY qui regroupe NOT NULL et UNIQUE en remplacement des deux dernières déclarations. - Et pour finir, il faut signifier que cette valeur doit s'incrémenter automatiquement à chaque insertion d'un enregistrement grâce à l'option AUTO_INCREMENT. ## Clé primaire (II) - Dans notre exemple, la liste des inscrits ne devrait pas excéder plusieurs centaines de personnes. Ainsi un attribut de type SMALLINT UNSIGNED devrait faire l'affaire. Nous le nommerons: id - Notre exemple devient: - CREATE TABLE Personne ( id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, nom VARCHAR(40), prenom VARCHAR(40), adresse VARCHAR(40), laboratoire VARCHAR(30) ) - La numérotation des clés primaires, débute à 1 et pas à 0. ## Clé primaire (III) - Notre clé primaire peut être associée simultanément à plusieurs attributs mais selon une syntaxe différente. - Si au lieu de créer un identifiant numérique unique, on souhaite simplement interdire d'avoir des doublons sur le couple (nom,prenom) et d'en interdire la nullité, on va créer une clé primaire sur ce couple. - La connaissance des seuls nom et prénom suffit à identifier sans ambiguïté un et un seul enregistrement. - Mauvaise syntaxe : - CREATE TABLE Personne ( nom VARCHAR(40) PRIMARY KEY, prenom VARCHAR(40) PRIMARY KEY, email VARCHAR(40), laboratoire VARCHAR(30) ) - Bonne syntaxe : - CREATE TABLE Personne ( nom VARCHAR(40), prenom VARCHAR(40), email VARCHAR(40), laboratoire VARCHAR(30), CONSTRAINT pk_nomprenom PRIMARY KEY (nom,prenom) ) ## Attribut non nul - Considérons que l'on souhaite que certains attributs aient obligatoirement une valeur. On utilisera l'option NOT NULL. - Dans ce cas, si malgré tout, aucune valeur n'est fournie, la valeur par défaut (si elle est déclarée à la création de la relation ) sera automatiquement affectée à cet attribut dans l'enregistrement. - Si aucune valeur par défaut n'est déclarée : - la chaîne vide "" sera affectée à l'attribut s'il est de type chaîne de caractères - la valeur zéro 0 s'il est de type nombre - la date nulle 0000-00-00 et/ou l'heure nulle 00:00:00 s'il est de type date, heure ou date et heure. ## Valeur par défaut - 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.. - Exemple : - telephone DECIMAL(10,0) DEFAULT '0123456789' ## Attribut sans doublon (I) - Pour interdire l'apparition de doublon pour un attribut, on utilise l'option UNIQUE. - Syntaxe: - UNIQUE [nomdelacontrainte](liste des attributs) - Pour interdire les doublons sur l'attribut 'nom' mais les interdire aussi sur 'prénom', tout en les laissant indépendants : - UNIQUE('nom') - UNIQUE('prénom') | nom | prénom | |-----------|---------| | Dupond | Marc | | Ducret | Pierre | | Martin | Marc |