Une base de données est une entité dans laquelle il est possible de stocker des données de façon structurée et avec le moins de redondance possible. Ces données doivent pouvoir être utilisées par des programmes, par des utilisateurs différents. Ainsi, la notion de base de données est généralement couplée à celle de réseau, afin de pouvoir mettre en commun ces informations, d'o~ le nom de base.

Le concept permet de stocker et d'organiser une grande quantité d'information. Les SGBD (Système de Gestion de Base de Données) permettent de naviguer dans ces données et d'extraire (ou de mettre à jour) les informations voulues au moyen d'une requête.

Les données apparaissent comme stockées dans des tables qu'on peut mettre en relation. Une table elle-même est une relation, mais entre les différents champs qui la composent.

Ce système se démarque donc totalement en termes d'interface des bases de données de type hiérarchique, même si au plan de l'implémentation, et en fonction des statistiques d'accès à la base, un modèle hiérarchique sera utilisé, qui n'aura jamais besoin d'être pris en compte par l'utilisateur. De plus les données d'une table sont souvent subordonnées à un des champs (une clé).

Ce modèle relationnel conduit à :

Les tables possèdent un certain nombre de colonnes ou champs permettant de décrire des n-uplets (lignes ou enregistrements). La non-duplication (absence de redondance) des n-uplets est théoriquement assurée par le SGBDR.

Dans les relations, nous avons vu qu'il est possible de définir deux types de clés :

Pour accéder aux données, on utilise différents opérateurs logiques, notamment la sélection (ou projection), mais aussi les jointures (dont il existe différents types). Les opérations sont communiquées sous forme de requêtes aux SGBDR (Système de Gestion de Base de Données Relationnelle). La plupart utilisent le langage normé SQL.

Dans une base de données relationnelle, le but est de séparer les informations au maximum pour éviter les doublons et la redondance, et d'empêcher la perte de qualité d'information (par exemple, l'adresse d'un fournisseur n'est mise à jour qu'une et une seule fois : la modification sera alors prise en compte sur l'ensemble des courriers). Cette bonne organisation des données nous est normalement garanti si le Modèle relationnel conçu est valide ...

MySQL est créé en 1995 par une société suédoise. Il est aujourd'hui développé et distribué par la société commerciale MySQL AB.

MySQL est passé en Open Source (GPL) depuis sa version 3.3, mais il est également proposé en version licencié.

MySQL AB fournit différents types de logiciels :

Extrait du manuel de référence de MySQL :

Nous verrons dans la partie consacré aux privilèges que les autorisations de connexions au serveur sont définies en fonction du couple login/password des utilisateurs MySQL, mais aussi en fonction du poste appelant. Néanmoins, vous devriez pouvoir vous connecter sur votre environnement de développement en utilisant les comptes par défaut (root, que nous n'avons pas encore sécurisé).

La création d'une base de données va nécessiter l'exécution d'une requête sur le serveur, nous allons devoir nous y connecter, soit en utilisant l'outil graphique mysql-query-browser soit en ligne de commande avec mysql.

ikare@kaitan:~$ mysql -h arrakis -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.1.17-beta FreeBSD port: mysql-server-5.1.17
mysql> 

Créer une base de données sous MySQL se fait via une requête normalisée SQL. Lors de la création, MySQL effetue les opérations suivantes :

La requête de création de base CREATE DATABASE est la suivante :

CREATE DATABASE [IF NOT EXISTS] db_name
    [create_specification [, create_specification] ...]

create_specification:
    [DEFAULT] CHARACTER SET charset_name
  | [DEFAULT] COLLATE collation_name

Les options permettent de ne ne créer la base de données que si elle n'existe pas (sinon, la requête retourne une erreur en cas de conflit), et de préciser les jeux de caractères utilisés dans les tables. Par défault, il s'agit de latin1, qui représente l'encodage ISO 8859-1 West European. La collation (les règles qui régissent les méthodes de comparaison et de classement des caractères) qui y est associée est latin1_swedish_ci.

Tout au long de ce cours, nous allons travailler sur un exemple de base de données, relatif à la gestion d'un bibliothèque personnelle. Nous allons donc créer la base de données bibliotheque.

mysql> CREATE DATABASE bibliotheque;
Query OK, 1 row affected (0.00 sec)

Dans ce premier exemple de requête SQL, on peut noter l'emploi de convention de nommage de la base (pas d'accents, de caractères spéciaux, d'espace, etc..), et un premier apperçu de la structure du langage SQL, avec en particulier le point- virgule qui termine chaque instructions du langage.

La liste des bases de données créées sur le serveur peut être obtenues en utilisant la commande SHOW DATABASES :

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| bibliotheque       | 
| database           | 
| location_planche   | 
| mysql              | 
| test               | 
+--------------------+
6 rows in set (0.00 sec)

Le serveur MySQL pouvant héberger plusieurs bases de données, il est nécessaire avant toute manipulation de données de sélectionner la base de travail, opération réalisée par l'instruction USE :

mysql> mysql> USE bibliotheque
Database changed

Le tableau ci-dessous cite les différents types de données numériques sous MySQL. M indique la taille d'affichage, et D (valable pour les nombres en virgule flottante), représente le nombre de chiffres après la virgule.

Un attribut de type ENUM peut prendre une valeur parmi celles déinies lors de la crétion de la table plus la chaîe vide ainsi que NULL si la déinition le permet. Ces valeurs sont exclusivement des chaînes de caract~res. Une énumération peut contenir 65535 éléments au maximum.

La définition d'un tel attribut se fait de la manière suivante :

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, lindex NULL est associé à la valeur NULL.

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. Il ne peut être défini que 64 éléments maximum.

Par exemple, un attribut déclaré comme suit :

SET("dvd",vhs","divx")

peut prendre les valeurs suivantes :

"" (chaîne vide)
"dvd,divx"
"dvd",vhs","divx"

et autres combinaisons de listes des trois valeurs définie plus haut, avec en plus la valeur NULL.

La base de données que l'on a créé dans le chapitre précédent relative à la gestion d'une bibliothèque personnelle doit reprendre le modèle relationnel suivant :

LIVRE(_LIV_NUM_, LIV_TITRE, LIV_SS_TITRE, #COL_NUM)
COLLECTION(_COL_NUM_, COL_NOM, COL_EDITEUR)
AUTEUR(_AUT_NUM_, AUT_NOM, AUT_PRENOM, AUT_DATE_NAISSANCE)
ECRIT(_ #LIV_NUM, #AUT_NUM_, ECR_ANNEE)

LIV_NUM : Entier de 0 à 65536
LIV_TITRE :  chaîne de caractères de longueur maximale 60
LIV_SS_TITRE : chaîne de caractères de longueur maximale 60
COL_NUM : Entier de 0 à 255
COL_NOM : chaîne de caractères de longueur maximale 60
COL_EDITEUR : chaîne de caractères de longueur maximale 60
AUT_NUM : Entier de 0 à 65536
AUT_NOM : chaîne de caractères de longueur maximale 60
AUT_PRENOM : chaîne de caractères de longueur maximale 60
AUT_DATE_NAISSANCE : Date
ECR_ID : Entier de 0 à 65536
ECR_ANNEE : Année

Il est à remarquer la convention de nommage des champs des relations par rapport aux noms des relations.. Pour des raisons de mise en page, les identifiants ne sont pas soulignés, mais encacdrés de signe "_".

Exercice : Donnez le MCD correspondant à ce modèle relationnel

La création d'une table en SQL se fait via la requête CREATE TABLE. La documentation de MySQL vous donnera la syntaxe complète de cette instruction, mais en voici un synopsys simplifié :

CREATE TABLE nom_table (
   nom_champ1 type_champ [NULL|NOT NULL] [AUTO_INCREMENT] [PRIMARY KEY],
   nom_champ1 type_champ [NULL|NOT NULL] [AUTO_INCREMENT],
 ...
) TYPE= {BDB|HEAP|ISAM|InnoDB|MERGE|MRG_MYISAM|MYISAM};

Les options NOT NULL/NULL (possibilité que la valeur d'un champ n'existe pas pour un enregistrement ou non) et AUTO_INCREMENT (incrémentation automatique d'un identifiant) sont facultatives. La clause PRIMARY KEY permet de spécifier que le champ joue le rôle de clef primaire.

Le type de la table a sont importance : en effet, selon les types choisi, la manière de stocker les informations pour MySQL sera différente (organisation de fichiers, modes d'acès, ..). Les types les plus couramment utilisés sont :

De manière générale, nous utiliserons les types de tables InnoDB.

Nous allons maintenant créer une première table de la base de données bibliothèque, la table COLLECTION :

mysql> CREATE TABLE COLLECTION (
    -> COL_NUM TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> COL_NOM VARCHAR(60) NOT NULL,
    -> COL_EDITEUR VARCHAR(60) NOT  NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

Nous pouvons vérifier la bonne création de la table en utilisant les instructions SQL SHOW TABLES et DESC :

mysql> SHOW TABLES;
+------------------------+
| Tables_in_bibliotheque |
+------------------------+
| COLLECTION             | 
+------------------------+
1 row in set (0.00 sec)

mysql> DESC COLLECTION;
+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| COL_NUM     | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment | 
| COL_NOM     | varchar(60)         | NO   |     |         |                | 
| COL_EDITEUR | varchar(60)         | NO   |     |         |                | 
+-------------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Exercice : En respectant les types de données indiquée plus haut, créez la table AUTEUR

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 est très longue du fait du parcours de toute la table. Pour y remédier, une optimisation possible et fortement recommandée, est d'utiliser des index.

La création d'un index associé à un attribut ou à un ensemble ordonné d'attributs va créer 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 feront les recherches et les tris. Les algorithmes de recherche et de tri sur des ensembles ordonnées sont énormément plus rapides !

Ainsi, d'une recherche à coût prohibitif, on passe à une recherche sur un ensemble déjà trié. On gagne donc énormément en temps d'accès aux informations. Bien que cela ralentisse les mises à jour (insertion, suppression, modification de clé).

On choisira de créer des index sur les attributs qui seront les plus sollicités par les recherches ou utilisés comme critère de jointure. Par contre, on épargnera 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.

Pour créer un index, on utilise l'instruction INDEX. Un index peut porter sur 15 colonnes maximum. Une table peut posséder au maximum 16 indexes. Un index peut avoir une taille d'au maximum 256 octets et ne doit porter que sur des attributs NOT NULL.Un exemple de création d'index vous est proposé dans le paragraphe suivant

Nous avons vu toute l'importance des contraintes d'intégrité référentielle, et des clefs étrangères en particulier. Pour créer des clefs étrangères lors de la création des tables, il faut que :

Ainsi, la création de la table LIVRE de notre exemple se ferait avec l'instruction suivante :

mysql> CREATE TABLE LIVRE (
    -> LIV_NUM SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
    -> LIV_TITRE VARCHAR(60) NOT NULL,
    -> LIV_SS_TITRE VARCHAR(60) NULL,
    -> COL_NUM TINYINT UNSIGNED ,
    -> PRIMARY KEY (LIV_NUM),
    -> FOREIGN KEY(COL_NUM ) REFERENCES COLLECTION(COL_NUM) ON DELETE RESTRICT  ON UPDATE CASCADE,
    -> INDEX(COL_NUM)
    -> );
Query OK, 0 rows affected (0.00 sec)

Dans le précédent exemple :

La création d'une relation par CREATE TABLE n'en rend pas définitives les spécifications. Il est possible d'en modifier la définition par la suite, à tout moment par la commande ALTER TABLE :

La syntaxe générale de l'instruction est :

ALTER TABLE relation MODIFY attribut definition_relative
ALTER TABLE relation ALTER attribut { SET DEFAULT valeur | DROP DEFAULT }
ALTER TABLE relation ADD UNIQUE  (attributs)
ALTER TABLE relation DROP attribut
ALTER TABLE relation ADD definition
ALTER TABLE relation ADD INDEX index (attributs)
ALTER TABLE relation DROP INDEX index

Exercice : Modifiez la table LIVRE de manière à faire apparître un nouveau champ : LIV_GENRE, qui soit une énumération ayant pour valeurs possible : "policier", "science-fiction", "historique"

Supprimer une table se fait par l'instruction DROP TABLE nom_table.

Nous avons vu dans les exercices du cours sur la modélisation le MCD d'une base de données pour la gestion d'un hôtel. Nous utiliserons cette base et son contenu dans plusieurs exercices au long de ce cours.

En utilisant l'interface de votre choix, créez en local la base de données HOTEL, qui hébergera les données de gestion de l'hôtel.

Pour que nous partions tous sur la même base de données, je vous propose d'exécuter le script cree_hotel.sql, qui contient la définition de toutes les tables de la base de données

Téléchargez le ici et exécutez le sur votre serveur. Donnez la liste complète des instructions qui permettent de vérifier que les tables et les colonnes ont bien été créées.

On souhaite rajouter les informations suivantes :

Donnez les requêtes SQL permettant de faire ces modifications dans la structure de la base.

On vous fournit le MR suivant :

FILM(CodeFilm, TitreFilm, Annee, Duree, Resume, #CodeGenre)

GENRE(CodeGenre, IntGenre)

ACTEUR(CodeActeur, NomActeur, PrenomActeur)

JOUER_UN_ROLE(#CodeFilm, #CodeActeur)

SUPPORT(CodeSupport, IntSupport)

FILM_SUPPORT(#CodeFilm,#CodeSupport)

EMPRUNTEUR(CodePers, NomPers, PrenomPers, AdrPers, TelPers)

EMPRUNT(#CodeFilm, #CodePers, DatePret, DateRetour)

Vous devez me présenter