Précédent Remonter Suivant

Chapitre 7  Introduction sommaire au monde des bases de données

On doit être préoccupé uniquement de l'impression ou de l'idée à traduire. Les yeux de l'esprit sont tournés au dedans, il faut s'efforcer de rendre avec la plus grande fidélité possible le modèle intérieur. Un seul trait ajouté (pour briller, ou pour ne pas trop briller, pour obéir à un vain désir d'étonner, ou à l'enfantine volonté de rester classique) suffit à compromettre le succès de l'expérience et la découverte d'une loi. On n'a pas trop de toutes ses forces de soumission au réel, pour arriver à faire passer l'impression la plus simple en apparence, du monde de l'invisible dans celui si différent du concret où l'ineffable se résout en claires formules. Marcel Proust
Nous avons abordé au chapitre 5 la notion de modèle de données, que nous avons définie comme une abstraction d'un type donné d'information, spécifiant les valeurs que peut prendre cette information, et les opérations qui permettent de la manipuler. Si les langages de programmation tels que Java offrent des mécanismes de représentation permettant de construire des structures de données et des classes représentant certains modèles de données, ils manquent cependant de souplesse quand les informations à manipuler comportent de nombreuses relations entre elles, par exemple. Nous avons vu au chapitre 6 comment la représentation d'un ensemble de comptes bancaires -- problème que nous avons pourtant beaucoup simplifié par rapport à la réalité -- nécessite la mise en place de fichiers, de mécanismes de sauvegarde et de chargement, etc. On imagine aisément que dans la réalité, on ne s'amuse pas, pour chaque application nécessitant la gestion d'informations un peu complexes, à remettre en place de tels mécanismes au moyen de lignes de code telles que nous les avons vues en Java. Au contraire, on recourt alors aux systèmes de gestion de bases de données1.

On peut définir sommairement une base de données comme un ensemble de données représentant l'information sur un domaine d'application particulier, pour lequel on aura pris soin de spécifier les propriétés de ces données, ainsi que les relations qu'elles ont les unes avec les autres.

Un système de gestion de bases de données (SGBD) est un ensemble logiciel qui permet de créer, de gérer, d'enrichir, de maintenir de manière rémanente, et d'interroger efficacement des bases de données. Les SGBD sont des outils génériques, indépendants des domaines d'application des bases de données. Ils fournissent un certain nombre de services: De nombreux SGBD existent, de complexité et de capacités variables. On peut citer des produits tels que Oracle dans le haut de gamme, le logiciel Access de Microsoft, plus rudimentaire, mais omniprésent en micro-informatique, ou des logiciels libres tels que MySQL3.

7.1  Le modèle relationnel

Le modèle relationnel a été proposé en 1970 par Ted Codd, un chercheur chez IBM, sur la base de la théorie des ensembles et de l'algèbre relationnelle. Il correspond à une vision tabulaire des données: les attributs des données, et les relations entre elles, sont représentés dans des tables. Présentons rapidement ses principaux éléments : Complétons maintenant notre schéma de base de données exemple, en nous inspirant de l'exemple bancaire qui nous accompagne tout au long de ce polycopié. On peut imaginer les schémas de relation suivants (la clé primaire est mise en exergue pour chaque schéma). Il va sans dire que l'exemple reste très sommaire par rapport à la réalité bancaire... 
  Client(\textbf{idClient} : entier, nom : chaîne(30), prénom : chaîne(30))
  Compte(\textbf{numCompte} : entier, idClient : entier, solde : réel, codeType : caractère)
  Adresse(\textbf{idClient} : entier, rue : chaîne(50), codePostal : entier, commune : chaîne(40))
  TypeCompte(\textbf{code} : caractère, nom : chaîne(40), tauxAgios : réel, tauxIntérêts : réel)
On pourrait alors avoir les instances suivantes pour les autres schémas de relation (les noms des attributs ont été abrégés pour des questions de place sur la page) :
Client Compte
 
id nom prénom
27384 Tombre Karl
39827 Bonfante Guillaume
73625 Lamiroy Bart
98362 Chirac Jacques
99375 Gates Bill
num id solde code
12536 39827 345,40 D
16783 99375 63703,40 E
18374 39827 2500,45 E
26472 98362 3746,23 D
28374 73625 837,23 D
37468 27384 -981,34 D
37470 27384 1345,34 E
48573 73625 1673,87 E
57367 27384 938,34 E
 
Adresse TypeCompte
 
id rue cp com
27384 53 Grande rue 54280 Sornéville
39827 3 avenue des tandems 54000 Nancy
73625 Bureau 498 à l'ENSMN 54000 Nancy
98362 Palais de l'Élysée 75000 Paris
99375 1 rue des fenêtres 99999 Seattle
code nom txAg txInt
D Dépôts 14,3 0,0
E Épargne 0,0 4,4

7.2  SQL

7.2.1  Introduction

SQL (Structured Query Language) est un langage conçu à partir des études sur le modèle relationnel, pour définir et accéder de manière normalisée aux bases de données relationnelles. Il est actuellement supporté par la plupart des SGBD du commerce et fait l'objet d'une norme. On peut donc le considérer comme une référence essentielle dans le domaine des bases de données.

SQL est un langage structuré, permettant d'exprimer de manière simple et lisible (proche de la langue anglaise) des requêtes qui traduisent les opérations de manipulation de données dans le modèle relationnel. Il permet d'une part de définir des schémas de bases de données relationnelles, ainsi que les données qui composent une base de données, et d'autre part de définir des requêtes, c'est-à-dire des manipulations dans une base de données pour en extraire les informations recherchées.

7.2.2  Création de schémas et insertion de données

Plutôt que de grandes explications théoriques, illustrons l'emploi de ce langage en créant tout d'abord les tables données précédemment, grâce à l'instruction CREATE TABLE. À noter que pour détruire une table, on utilise l'instruction DROP, et que pour modifier un schéma de relation, on utilise l'instruction ALTER TABLE. 
CREATE TABLE CLIENT(IDCLIENT INTEGER NOT NULL, NOM CHAR(30), PRENOM CHAR(30),
                    PRIMARY KEY (IDCLIENT))
CREATE TABLE COMPTE(NUMCOMPTE INTEGER NOT NULL, IDCLIENT INTEGER NOT NULL, 
                    SOLDE DECIMAL(15,2), CODETYPE CHAR,
                    PRIMARY KEY (NUMCOMPTE))
CREATE TABLE ADRESSE(IDCLIENT INTEGER NOT NULL, RUE CHAR(50), CODEPOSTAL INTEGER,
                     COMMUNE CHAR(40),
                     PRIMARY KEY (IDCLIENT))
CREATE TABLE TYPECOMPTE(CODE CHAR NOT NULL, NOM CHAR(40), TAUXAGIOS DECIMAL(4,2),
                        TAUXINTERETS DECIMAL(4,2),
                        PRIMARY KEY (CODE))
Peuplons maintenant ces tables avec l'instruction INSERT (l'instruction UPDATE permet ultérieurement de modifier ces valeurs, et l'instruction DELETE de supprimer certaines relations d'une table -- dans les deux cas, ces instruction se servent de la clause WHERE, que nous illustrons par la suite) : 
INSERT INTO CLIENT VALUES(27384, "Tombre", "Karl")
INSERT INTO CLIENT VALUES(39827, "Bonfante", "Guillaume")
INSERT INTO CLIENT VALUES(73625, "Lamiroy", "Bart")
INSERT INTO CLIENT VALUES(98362, "Chirac", "Jacques")
INSERT INTO CLIENT VALUES(99375, "Gates", "Bill")

INSERT INTO COMPTE VALUES(12536, 39827, 345.40, 'D')
INSERT INTO COMPTE VALUES(16783, 99375, 63703.40, 'E')
INSERT INTO COMPTE VALUES(18374, 39827, 2500.45, 'E')
INSERT INTO COMPTE VALUES(26472, 98362, 3746.23, 'D')
INSERT INTO COMPTE VALUES(28374, 73625, 837.23, 'D')
INSERT INTO COMPTE VALUES(37468, 27384, -981.34, 'D')
INSERT INTO COMPTE VALUES(37470, 27384, 1345.34, 'E')
INSERT INTO COMPTE VALUES(48573, 73625, 1673.87, 'E')
INSERT INTO COMPTE VALUES(57367, 27384, 938.34, 'E')

INSERT INTO ADRESSE VALUES(27384, "53 Grande rue", 54280, "Sornéville")
INSERT INTO ADRESSE VALUES(39827, "3 avenue des tandems", 54000, "Nancy")
INSERT INTO ADRESSE VALUES(73625, "Bureau 498 à l'ENSMN", 54000, "Nancy")
INSERT INTO ADRESSE VALUES(98362, "Palais de l'Élysée", 75000, "Paris")
INSERT INTO ADRESSE VALUES(99375, "1 rue des fenêtres", 99999, "Seattle")

INSERT INTO TYPECOMPTE VALUES('D', "Dépôts", 14.3, 0.0)
INSERT INTO TYPECOMPTE VALUES('E', "Épargne", 0.0, 4.4)

7.2.3  Projections et sélections

Passons maintenant à la manipulation de données. On exprime habituellement une requête de la manière suivante : SELECT ... FROM ... WHERE .... La clause SELECT indique que l'on effectue une opération sur la base de données, en lui appliquant certains opérateurs ou fonctions, afin d'obtenir un résultat. La clause FROM donne la liste des tables utilisées dans l'opération en cours. La clause optionnelle WHERE, quant à elle, donne une liste de conditions que doivent respecter les données sélectionnées.

L'opération la plus simple consiste à faire une projection, c'est-à-dire d'extraire un ou plusieurs attributs d'un schéma. Ainsi, voici une requête simple de type projection, et le résultat obtenu sur l'exemple développé : 
SELECT NOM FROM CLIENT

Tombre
Bonfante
Lamiroy
Chirac
Gates
Voici un deuxième exemple dans lequel on extrait deux attributs : 
SELECT CODEPOSTAL, COMMUNE FROM ADRESSE

54280, Sornéville
54000, Nancy
54000, Nancy
75000, Paris
99999, Seattle
Un cas particulier est le caractère *, qui correspond à l'extraction de tous les attributs : 
SELECT * FROM TYPECOMPTE

D, Dépôts, 14.30, 0.00
E, Épargne, 0.00, 4.40
La deuxième opération est la sélection, dans laquelle on limite la projection aux données qui vérifient une certaine condition (spécifiée grâce à la clause WHERE). Ainsi, pour extraire les identificateurs de tous les clients qui habitent à Nancy, on pourra écrire : 
SELECT IDCLIENT FROM ADRESSE WHERE COMMUNE="NANCY"

39827
73625

7.2.4  Jointure

On peut réaliser le produit cartésien de deux (ou plus) tables. Par exemple, pour sélectionner l'ensemble des données obtenues par le produit des tables COMPTE et TYPECOMPTE, il suffit d'écrire : 
SELECT * FROM COMPTE, TYPECOMPTE

12536, 39827, 345.40, D, D, Dépôts, 14.3, 0.0
16783, 99375, 63703.40, E, D, Dépôts, 14.3, 0.0
18374, 39827, 2500.45, E, D, Dépôts, 14.3, 0.0
26472, 98362, 3746.23, D, D, Dépôts, 14.3, 0.0
28374, 73625, 837.23, D, D, Dépôts, 14.3, 0.0
37468, 27384, -981.34, D, D, Dépôts, 14.3, 0.0
37470, 27384, 1345.34, E, D, Dépôts, 14.3, 0.0
48573, 73625, 1673.87, E, D, Dépôts, 14.3, 0.0
57367, 27384, 938.34, E, D, Dépôts, 14.3, 0.0
12536, 39827, 345.40, D, E, Épargne, 0.0, 4.4
16783, 99375, 63703.40, E, E, Épargne, 0.0, 4.4
18374, 39827, 2500.45, E, E, Épargne, 0.0, 4.4
26472, 98362, 3746.23, D, E, Épargne, 0.0, 4.4
28374, 73625, 837.23, D, E, Épargne, 0.0, 4.4
37468, 27384, -981.34, D, E, Épargne, 0.0, 4.4
37470, 27384, 1345.34, E, E, Épargne, 0.0, 4.4
48573, 73625, 1673.87, E, E, Épargne, 0.0, 4.4
57367, 27384, 938.34, E, E, Épargne, 0.0, 4.4
On comprend aisément que ce produit cartésien brut a peu d'intérêt; il en a néanmoins beaucoup plus si on ne retient que les enregistrements cohérents, typiquement, pour notre exemple, ceux dont les codes de type correspondent entre les deux tables. Cette opération s'appelle la jointure et s'écrit de la manière suivante : 
SELECT * FROM COMPTE, TYPECOMPTE WHERE COMPTE.CODETYPE = TYPECOMPTE.CODE

12536, 39827, 345.40, D, D, Dépôts, 14.3, 0.0
26472, 98362, 3746.23, D, D, Dépôts, 14.3, 0.0
28374, 73625, 837.23, D, D, Dépôts, 14.3, 0.0
37468, 27384, -981.34, D, D, Dépôts, 14.3, 0.0
16783, 99375, 63703.40, E, E, Épargne, 0.0, 4.4
18374, 39827, 2500.45, E, E, Épargne, 0.0, 4.4
37470, 27384, 1345.34, E, E, Épargne, 0.0, 4.4
48573, 73625, 1673.87, E, E, Épargne, 0.0, 4.4
57367, 27384, 938.34, E, E, Épargne, 0.0, 4.4
Si on associe maintenant une projection et une jointure, on voit qu'on peut extraire des informations intéressantes, comme par exemple le nom, le code postal et la ville des clients qui ont un compte d'épargne : 
SELECT NOM, CODEPOSTAL, COMMUNE FROM CLIENT, COMPTE, ADRESSE
  WHERE CLIENT.IDCLIENT = ADRESSE.IDCLIENT AND CLIENT.IDCLIENT = COMPTE.IDCLIENT
  AND CODETYPE='E'

Gates, 99999, Seattle
Bonfante, 54000, Nancy
Tombre, 54280, Sornéville
Lamiroy, 54000, Nancy
Tombre, 54280, Sornéville
La présence de plusieurs lignes identiques dans le résultat ci-dessus n'est pas une erreur, mais résulte du produit cartésien, qui a trouvé plusieurs comptes d'épargne pour le même client. Si on souhaite éliminer de tels doublons, on peut utiliser la clause DISTINCT : 
SELECT DISTINCT NOM, CODEPOSTAL, COMMUNE FROM CLIENT, COMPTE, ADRESSE
  WHERE CLIENT.IDCLIENT = ADRESSE.IDCLIENT AND CLIENT.IDCLIENT = COMPTE.IDCLIENT
  AND CODETYPE='E'

Gates, 99999, Seattle
Bonfante, 54000, Nancy
Tombre, 54280, Sornéville
Lamiroy, 54000, Nancy

7.2.5  Quelques autres clauses et fonctions

Une fois de plus, nous n'avons pas la prétention de donner un cours complet sur les bases de données relationnelles, ni même sur SQL, mais simplement de vous faire goûter à la puissance de ce type de modèle et de langage. Nous illustrons donc sans beaucoup de commentaires quelques autres constructions possibles en SQL...

Pour connaître le nombre de comptes de chaque type, on peut utiliser la fonction COUNT, associée à la clause GROUP BY pour regrouper les comptes de même type dans le produit cartésien : 
SELECT NOM, COUNT(*) FROM COMPTE, TYPECOMPTE 
  WHERE COMPTE.CODETYPE = TYPECOMPTE.CODE
  GROUP BY CODE

Dépôts, 4
Épargne, 5
Pour connaître le nombre de clients dans les différentes communes de Meurthe-et-Moselle, on pourra écrire 
SELECT COMMUNE, COUNT(*) FROM CLIENT, ADRESSE
  WHERE CLIENT.IDCLIENT = ADRESSE.IDCLIENT
  AND CODEPOSTAL >= 54000 AND CODEPOSTAL < 55000
  GROUP BY COMMUNE

Nancy, 2
Sornéville, 1
Si maintenant on veut limiter la recherche précédente aux communes ayant plus d'un client, on ne peut plus recourir simplement à la clause WHERE, car ce genre de condition ne porte pas sur les lignes individuelles sélectionnées, mais sur les groupes constitués par la clause GROUP BY. C'est à cela que sert la clause HAVING : 
SELECT COMMUNE, COUNT(*) FROM CLIENT, ADRESSE
  WHERE CLIENT.IDCLIENT = ADRESSE.IDCLIENT
  AND CODEPOSTAL >= 54000 AND CODEPOSTAL < 55000
  GROUP BY COMMUNE
  HAVING COUNT(*) > 1

Nancy, 2
Pour calculer le solde cumulé de tous les comptes de chaque client, on utilisera la fonction SUM -- à noter que d'autres fonctions existent, notamment MAX, MIN et AVG: 
SELECT PRENOM, NOM, SUM(SOLDE) FROM CLIENT, COMPTE
  WHERE CLIENT.IDCLIENT = COMPTE.IDCLIENT
  GROUP BY CLIENT.IDCLIENT

Karl, Tombre, 1302.34
Guillaume, Bonfante, 2845.85
Bart, Lamiroy, 2511.10
Jacques, Chirac, 3746.23
Bill, Gates, 63703.40
Enfin, si je veux présenter la liste ci-dessus dans l'ordre alphabétique des noms de famille, on peut ajouter la clause ORDER BY: 
SELECT PRENOM, NOM, SUM(SOLDE) FROM CLIENT, COMPTE
  WHERE CLIENT.IDCLIENT = COMPTE.IDCLIENT
  GROUP BY CLIENT.IDCLIENT
  ORDER BY NOM

Guillaume, Bonfante, 2845.85
Jacques, Chirac, 3746.23
Bill, Gates, 63703.40
Bart, Lamiroy, 2511.10
Karl, Tombre, 1302.34

7.3  JDBC

Jusqu'ici, nous avons vu les bases de données de manière complètement déconnectée de la programmation. On peut bien évidemment accéder directement à une base de données via un interprète du langage SQL ou par une interface Web, par exemple. Cependant, l'intérêt principal reste bien entendu d'intégrer directement l'accès à ces bases aux programmes écrits par ailleurs, pour remplacer en particulier les manipulations lourdes de fichiers illustrées par exemple au § 6.3.2, par une gestion directe des données au moyen d'une base de données.

En Java, l'interface (API) JDBC a été justement conçue pour permettre aux applications écrites en Java de communiquer avec les SGBD, en exploitant notamment SQL. Nous nous contentons ici d'illustrer très sommairement l'emploi de cette API dans un programme Java; le lecteur qui voudrait en savoir plus se reportera à l'un des nombreux ouvrages techniques disponibles, tel que [9]. À noter que les classes JDBC se trouvent dans le package java.sql. On n'oubliera donc pas de commencer par dire: 
import java.sql.*;
Il faut noter que le SGBD peut très bien s'exécuter sur une autre machine que celle sur laquelle s'exécute l'application qui l'utilise. Il faut donc examiner ce qui se passe aussi bien du côté du serveur, en l'occurrence le SGBD, que du côté du client, à savoir l'application. En supposant qu'un SGBD s'exécute bien du côté serveur, il faut un pilote4 pour y accéder. Les pilotes sont bien entendu propres à chaque SGBD; si on imagine par exemple que l'on accède au SGBD MySql, on invoquera l'un des pilotes disponibles pour ce SGBD. Chaque pilote a ses règles propres pour le lancement; celui que nous utilisons en TD à l'École, par exemple, est lancé par la commande suivante : 
Class.forName("org.gjt.mm.mysql.Driver").newInstance();
La première chose à effectuer, une fois le pilote lancé, est de se connecter à la base de données. On utilise pour cela une méthode de la classe DriverManager du package java.sql, dont le rôle est de tenir à jour une liste des implantations de pilotes et de transmettre à l'application toute information utile pour se connecter aux bases. Ainsi, on pourra écrire : 
try {
    Connection c = DriverManager.getConnection(
        "jdbc:mysql://cesar.mines.inpl-nancy.fr/cours_1a?
        user=cours_1a_user&
        password=mines");
} catch (SQLException err) {
    System.out.println("SQLException: " + err.getMessage());
    System.out.println("SQLState:     " + err.getSQLState());
    System.out.println("VendorError:  " + err.getErrorCode());
}
à condition bien entendu que le SGBD tourne bien sur la machine cesar, avec les informations d'authentification données ici. Vous noterez au passage que comme nombre de méthodes des classes du package java.sql, getConnection est susceptible de provoquer une exception, et que je prévois d'afficher un maximum d'informations dans ce cas.

La méthode getConnection rend une référence à un objet de type Connection; cette dernière classe symbolise une transaction avec une base de données. C'est en utilisant une méthode de cette classe que l'on crée un objet de type requête SQL, représenté par la classe Statement: 
try {
    Statement stmt = c.createStatement();
} catch (SQLException err) { ... }
Nous sommes maintenant prêts pour récupérer le résultat d'une requête, grâce à une méthode de cette dernière classe : 
try {
    ResultSet rs = stmt.executeQuery(
    "SELECT * FROM COMPTE, TYPECOMPTE WHERE COMPTE.CODETYPE = TYPECOMPTE.CODE");
} catch (SQLException err) { ... }
Vous comprendrez aisément qu'à la place de la chaîne de caractères constante ci-dessus, la requête peut être constituée d'une chaîne construite par le programme d'application, à partir par exemple des données fournies par un utilisateur dans une partie interactive, ou de la logique propre à l'application. Nous vous laissons d'ailleurs le soin de le vérifier dans l'exercice qui vous est proposé dans le TD accompagnant ce cours.

Le résultat de la requête est une instance de la classe ResultSet; les différents éléments peuvent être récupérés pour affichage, ou pour exploitation dans les calculs propres à l'application, par l'intermédiaire de l'interface de cette classe. Le résultat de la requête est une table, et les méthodes getXXX de la classe ResultSet permettent de lire le contenu des différentes colonnes de cette table, colonnes qui sont numérotées à partir de 1. Ainsi, dans l'exemple donné ci-dessus, si on veut afficher dans l'ordre le type de compte (dépôts ou épargne), le numéro de compte, et le solde, on pourra par exemple écrire : 
try {
    while (rs.next()) {
        System.out.print("Compte de type " + rs.getString(6) + " numéro "); 
        System.out.print(rs.getInt(1) + " -- solde : "); 
        System.out.println(rs.getDouble(3));
    }
    // On ferme proprement
    rs.close();
} catch (SQLException err) {
    ...
}
ce qui donnera une sortie du genre : 
Compte de type Dépôts numéro 12536 -- solde : 345.40
Compte de type Dépôts numéro 26472 -- solde : 3746.23
Compte de type Dépôts numéro 28374 -- solde : 837.23
Compte de type Dépôts numéro 37468 -- solde : -981.34
Compte de type Épargne numéro 16783 -- solde : 63703.40
Compte de type Épargne numéro 18374 -- solde : 2500.45
Compte de type Épargne numéro 37470 -- solde : 1345.34
Compte de type Épargne numéro 48573 -- solde : 1673.87
Compte de type Épargne numéro 57367 -- solde : 938.34
1
La présentation qui suit reste très sommaire, et correspond juste à une première sensibilisation. Le lecteur qui voudrait en savoir plus peut se reporter à des ouvrages de référence comme [8], ou à des cours en ligne tel que celui d'Hervé Martin, à l'adresse
http://www-lsr.imag.fr/Les.Personnes/Herve.Martin/HTML/FenetrePrincipale.htm.
2
Imaginez vos sentiments si un programme pirate pouvait accéder à vos informations médicales ou bancaires, par exemple, ou si une panne de courant entraînait la remise à zéro de votre compte bancaire...
3
Logiciel libre, très facile à installer sous Linux et sous Windows. Voir http://www.mysql.com/. Le serveur MySQL de l'École se trouve à l'adresse https://cesar.mines.inpl-nancy.fr/phpmyadmin
4
Driver en anglais.
Précédent Remonter Suivant