Cours d’initiation à la gestion des Bases de données SQL

Introduction aux Bases de données et à SQL

Bibliographies: Pour la première partie de ce cours, une revue littéraire a été réalisée à partir des documents mentionnés ci-dessous. Comme la première partie n'est qu'un bref aperçu sur les Bases de données et SQL, je n’ai extrait donc que quelques transparents par ci par là. De ce fait, je vous encourage à jeter un coup d'œil sur chacun des documents pour avoir une vue plus profonde sur la question. Le document devra passer en premier car c'est des notes de cours avec des explications détaillées.

Les Bdds & SQL

Plusieurs transparents ont été extraits de ce lien:

Définitions

"Une base de données est un ensemble structuré de données enregistrées dans un ordinateur et accessibles de façon sélective par plusieurs utilisateurs."

"Une base de données =

- un (gros) ensemble d'informations    - stockées sur disque:

o) persistance 

            o) espace de stockage"

      espace de  stockage Cette définition suppose que nous pouvons organiser cette base en un (ou plusieurs) fichier(s) stockés sur mémoire.

Cette approche pose certains problèmes:

1.  "Lourdeur d'accès aux données. En pratique, pour chaque accès, même le plus simple, il faudrait écrire un programme."

2.  "Manque de sécurité. Si tout programmeur peut accéder directement aux fichiers, il est impossible de garantir la sécurité et l'intégrité des données."

3.  "Pas de contrôle de concurrence. Dans un environnement où plusieurs utilisateurs accèdent aux mêmes fichiers, des problèmes de concurrence d'accès se posent."

Solution => SGBD!

Système de Gestion de Base de Données(SGBD) (Database

Management System: DBMS)

"Un logiciel qui permet d'interagir avec une base de données s'appelle un système de gestion de base de données"

Les fonctionnalités d'un SGBD sont

-    "Décrire les données qui seront stockées"

-    "Manipuler ces données (ajouter, modifier, supprimer des informations)"

-    "Consulter les données et traiter les informations obtenues

(sélectionner, trier, calculer, agréger, )"

-    "Définir des contraintes d'intégrité sur les données (contraintes de domaines, d'existence, )"

-    "Définir des protections d'accès (mots de passe, autorisations, )" - "Résoudre les problèmes d'accès multiples aux données (blocages, interblocages)"

-    "Prévoir des       procédures       de reprise en cas    d'incident

(sauvegardes, journaux, )"

"En résumé, un SGBD est destiné à gérer un gros volume d'informations, persistantes (années) et fiables (protection sur pannes), partageables entre plusieurs utilisateurs et/ou programmes et manipulées indépendamment de leur représentation physique."

Le modèle relationnel

Le langage SQL

"SQL (Structured Query Langage ou bien Langage de requête structuré) est un langage déclaratif, permet d'interroger une base de données sans se soucier de:

la représentation interne (physique) des données, de leur localisation, des chemins d'accès, ou des algorithmes nécessaires."

"SQL s'adresse à une large communauté d'utilisateurs potentiels (pas seulement des informaticiens) et constitue un des atouts les plus spectaculaires (et le plus connu) des SGBDR (R pour Relationnelle)."

"SQL peut être utilisé:

-    manière interactive,

-    en association avec des interfaces graphiques,

-    ou, très généralement, des langages de programmation."

"SQL ne permet pas de faire de la programmation au sens courant du terme (faire une boucle par exemple) et doit donc être associé avec un langage comme le C, le COBOL ou JAVA pour réaliser des traitements complexes accédant à une base de données." Requêtes simples SQL

NomStation	capacité	lieu	région	tarif
Venusa	350	Guadeloupe	Antilles	1200
Farniente	200	Seychelles	Océan Indien	1500
Santalba	150	Martinique	Antilles	2000
Passac	400	Alpes	Europe	1000

La table Station

NomStation	Libellé	Prix
Venusa	Voile	150
Venusa	Plongée	120
Farniente	Plongée	130
Passac	Ski	200
Passac	Piscine	20
Santalba	Kayac	50

Les stations et leurs activités

La table Activité

SELECT NomStation FROM Station WHERE region = Antilles

Ce premier exemple montre la structure de base d'une requête SQL, avec les trois clauses SELECT, FROM et WHERE.

FROM indique la (ou les) tables dans lesquelles on trouve les attributs utiles à la requête. Un attribut peut être utile  de deux manières (non exclusives) :

(1)  on souhaite afficher son contenu,

(2)  on souhaite qu'il ait une valeur particulière (une constante ou la valeur d'un autre attribut).

Il est obligatoire avec SELECT.

SELECT indique la liste des attributs constituant le résultat. 

WHERE indique les conditions que doivent satisfaire les n-uplets de la base pour faire partie du résultat.

Pour afficher l'intégralité d'une table, et avoir ainsi toutes les lignes (on omet la clause WHERE), et toutes les colonnes, on peut au choix lister tous les attributs ou utiliser le caractère  *  qui a la même signification.

SELECT * FROM NomTable

SELECT * FROM Station

* remplace tous les attributs (champs) de la table Station. de ce fait la table station est reproduite.

SELECT NomStation, capacité FROM Station

On récupère  les deux colonnes de la table Station.

SELECT NomStation FROM Station WHERE region = Antilles

WHERE critère

NomStation

Venusa

Santalba

SELECT libelle, prix / 6.56,  Cours de l'euro =  , 6.56

FROM Activite

WHERE nomStation =  Santalba

libelle	prix / 6.56	Cours de l'euro =	6.56
Kayac	7.62	Cours de l'euro =	6.56

SELECT libelle FROM Activite

donnera autant de lignes dans le résultat que dans la table Activite.

libelle

Voile

Plongee

Plongee

Ski

Piscine

Kayac

Tri du résultat

Nous utilisons la clause ORDER BY pour trier les résultats d'une requête. Cette clause doit être suivie de la liste des attributs servant de critère au tri. Exemple :

SELECT * FROM Station ORDER BY tarif, nomStation

"Trie, en ordre ascendant, les stations par leur tarif, puis, pour un même tarif, présente les stations selon l'ordre lexicographique. Pour trier en ordre descendant, on ajoute le mot-clé DESC après la liste des attributs."

WHERE

"Dans la clause WHERE, on spécifie une condition booléenne portant sur les attributs des relations du FROM. On utilise pour cela de manière standard le AND, le OR, le NOT et les parenthèses pour changer l'ordre de priorité des opérateurs booléens. Par exemple :

SELECT nomStation, libelle

FROM Activite

WHERE nomStation =  Santalba  AND (prix >= 50 AND prix <= 120)

Les opérateurs de comparaison:                                  (   )

Pour obtenir une recherche par intervalle, on peut également utiliser le mot-clé BETWEEN. La requête précédente est équivalente à :

SELECT nomStation, libelle

FROM Activite

WHERE nomStation = Santalba  AND prix BETWEEN 50 AND 120

Pour créer une table, on utilise l’instruction CREATE TABLE. 

CREATE TABLE Station (nomStation VARCHAR (30) NOT NULL, capacite   INT  NOT NULL,                       lieu       VARCHAR(30) NOT NULL,                       region     VARCHAR (30),

tarif      FLOAT (10,2) DEFAULT 0,

CONSTRAINT cle_station PRIMARY KEY (nomStation),

CONSTRAINT cle_lieu_region UNIQUE (lieu, region),

CONSTRAINT nom_region

                 CHECK (region IN ('Ocean Indien',

'Antilles', 'Europe',

'Ameriques', 'Extreme Orient'))

);

Dans l’instruction, on peut définir les contraintes qui seront vérifiées par le SGBD.

Par exemple, la contrainte nommée cle_station, utilisant la clause PRIMARY KEY, indique que nomStation est la clé primaire, donc ne peut être nulle et sa valeur doit être unique.

La contrainte nommée nom_region définie un domaine de valeurs possibles pour le champ region grâce à la clause CHECK.

La clause NOT NULL permet d’imposer une contrainte d’existence.

La clause UNIQUE permet d’interdire les doublons, ce qui est utile pour imposer l’unicité des valeurs dans les champs qui ne sont pas une clé primaire.

CREATE TABLE Activite (nomStation   VARCHAR (30) NOT NULL,

libelle      VARCHAR(30) NOT NULL,                        prix         FLOAT (10,2) DEFAULT 0,                        PRIMARY KEY (nomStation, libelle),

FOREIGN KEY (nomStation) REFERENCES Station

ON DELETE CASCADE

);

La clause FOREIGN KEY permet de spécifier une clé étrangère, c’est-à-dire, le champ qui fait le lien avec la clé primaire d’une autre table.  Cette clause va permettre de faire respecter l’intégrité référentielle, en effet, toute nouvelle insertion dans cette table devra avoir une valeur dans ce champ (ici, nomStation) qui se trouve dans la clé primaire de la table liée (ici, Station).

Remarque : En MySQL, il y a plusieurs types de tables, le type par défaut est MyISAM, ce type ne génère pas d’erreur si, lors d’une insertion, l’intégrité référentielle n’est pas respectée, il faut utiliser le type INNODB pour les 2 tables en liaisons et s’assurer que la clé étrangère est indexée. 

ON DELETE CASCADE indique le traitement à faire lors d’une opération affectant l’intégrité référentielle (DELETE ou UPDATE), l’option CASCADE indique que la même opération doit être appliquée sur les enregistrements dont la clé étrangère correspond à la clé primaire de l’enregistrement touché.  L’autre option disponible, SET NULL. 

Concrètement, si une station de la table Station est supprimée, tous les enregistrements de la table Activite qui avait la même valeur dans leurs clés étrangères seront aussi supprimés.

Pour supprimer une table, utilisez l’instruction DROP TABLE.

Par exemple, DROP TABLE Station va supprimer la table Station et, comme la table Activite contient la clause ON DELETE CASCADE, cette dernière sera vidée (mais non supprimée). 

Pour ajouter un enregistrement dans une table, on utilise l’instruction INSERT.

INSERT INTO Station (NomStation, Capacite, Lieu, Region, tarif)        VALUES ('Venusa',350,'Guadeloupe','Antilles',1200);

Ou encore :

INSERT INTO Station VALUES ('Venusa',350,'Guadeloupe','Antilles',1200);

La seconde forme ne peut être utilisée que si des valeurs sont spécifiées pour chaque champ et ce, dans l’ordre de déclaration dans la table.

Une valeur qui serait omise (dans la première forme) se verrait affecter la valeur

NULL.