Fichier de rejeu Close
Indication Close
A propos de... Close
Commentaire Close
Systèmes d'Information
- Notions mathématiques
- Calcul relationnel
- Algèbre relationnelle
- Langage de requêtes
- Arbre de requêtes
- Exercices
- Introduction
- Commandes de bases
- Langage de définition de données (LDD)
- Langage de manipulation de données (LMD)
- Types de données
- Exercice
- Dépendances fonctionnelles
- Décomposition de relations
- Inférence logique
- Normalisation
- Aux pays des bières
- Modélisation
- Exercices
© Your Copyright
Aide
Langage de définition de données
Nous présentons dans cette partie les commandes SQL pour:
- Structurer l’information dans la base de données
- Modifier et faire évoluer les modèle de données
- Créer des fonctions (procédures stockées) sur la base de données
La possibilité de création de procédures stockées n’existe pas en SQLite.
Le lecteur intéressé pourra s’exercer sur la création de fonctions en installant chez lui le SGBDR PostgreSQL.
Structuration des données
Nous présentons ici les commandes SQL de:
- création des ensembles
- définition des espaces de nommage
- encapsulation de requêtes dans des vues
- création d’index sur des colonnes de tables pour accélérer la recherche d’informations
- création de domaines (type de données avec contraintes optionnelles sur l’ensemble de valeurs autorisées)
- création de nouveaux types de données SQL
Attention : Toutes ces commandes n’existent pas en SQLite mais pourront être mises en œuvre avec d’autres SGBDR comme PostgreSQL
1 2 3 4 | CREATE TABLE personnes (
id integer,
nom text
);
|
Création d’un ensemble (vide) de personnes auxquelles on attribuera :
- un identifiant (une valeur entière)
- un nom (une chaîne de caractères)
1 2 3 4 | CREATE SCHEMA IF NOT EXISTS ENIB AUTHORIZATION nedelec;
CREATE TABLE ENIB.personnes (id integer, nom text);
SELECT * FROM ENIB.personnes;
SELECT * FROM personnes;
|
Tout comme on peut créer deux fichiers de même nom sous un répertoire différent, la création de schéma permettra de manipuler des entités (tables) de même nom qui seront distinguées par l’espace de nommage sous lequel elles auront été créées.
L’accès à ces schémas pourra être donné seulement à certains aux utilisateurs (AUTHORIZATION).
Attention : cette commande n’existe pas en SQLite, il faut créer une nouvelle base (un nouveau fichier) :
1 2 3 4 | $ sqlite3 ENIB.db
sqlite> CREATE TABLE personnes (id integer, nom text);
$ sqlite3 UBO.db
sqlite> CREATE TABLE personnes (id integer, nom text);
|
1 2 3 | CREATE VIEW lesnoms AS
SELECT nom
FROM personnes;
|
Cette commande SQL permet d“« encapsuler » une requête SQL dans une vue.
Tout comme avec un langage de programmation on encapsule du code dans une fonction que l’on peut ensuite utiliser pour exécuter le code qu’elle contient, en SQL on pourra utiliser une vue dans une requête en l’appelant par son nom.
SELECT * FROM lesnoms;
CREATE INDEX age_capitaine ON personnes(age);
Cette commande SQL permet de créer un index sur une colonne de table pour accélérer l’exécution d’une requête SQL qui lit des données et ainsi améliorer les performances d’une application utilisant une base de données.
CREATE DOMAIN colors VARCHAR(10) CHECK (VALUE IN ('red', 'green', 'blue'));
Cette commande SQL permet de créer un domaine pour définir un ensemble de couleurs.
CREATE TYPE addresse AS (numero SMALLINT, rue VARCHAR(100), ville VARCHAR(50));
Cette commande SQL de définir un nouveau type de données pour définir une adresse.
Dans le cadre de ce cours les créations de tables (CREATE TABLE) et de vues (CREATE VIEW) constituent les notions principales à retenir.
Modification du modèle de données
Il existe en SQL un certain nombre de commandes pour faire évoluer les modèles de données existants
- modifier les ensembles (tables)
- définir, modifier des contraintes sur les colonnes des tables
Modification des tables
Après avoir créé et alimenté une base de données il faut être capable de pouvoir faire évoluer le modèle de données sans avoir à détruire et recréer l’information existante. C’est le rôle de la commande SQL :
Après avoir créé une table :
CREATE TABLE T(a integer, b text);
INSERT INTO T VALUES (1,"toto");
SELECT * FROM T;
la commande SQL ALTER TABLE permettra de renommer la table :
ALTER TABLE T RENAME TO personnes;
SELECT * FROM personnes;
Cette commande permet aussi d’ajouter une colonne (connaître l’âge des personnes)
ALTER TABLE personnes ADD COLUMN age SMALLINT DEFAULT 20;
La commande ALTER TABLE en SQLite ne permet que de renommer une table ou ajouter une nouvelle colonne.
D’autres langages comme PostgreSQL permettrons de renommer les colonnes dans une table sur une base de données de la manière suivante :
En SQlite, pour modifier une colonne dans une table il est nécessaire de le faire dans une transaction en suivant les étapes:
- créer une nouvelle table
- copier dans cette table les informations de la table dont on veut modifier les colonnes
- détruire l’ancienne table
1 2 3 4 5 6 7 8 9 10 11 | CREATE TABLE T(a integer, b text);
INSERT INTO T VALUES (1,'toto');
SELECT * FROM T;
BEGIN TRANSACTION;
CREATE TABLE personnes (id integer, nom text, age integer);
INSERT INTO personnes(id,nom) SELECT a,b FROM T;
UPDATE personnes SET age=20;
DROP TABLE T;
COMMIT;
SELECT * FROM personnes;
|
On peut aussi utiliser des tables temporaires en suivant les étapes:
- créer une table temporaire
- sauvegarder l’ensemble dans une table temporaire
- détruire l’ensemble sauvegardé
- créer le nouvel ensemble
- copier les élements sauvegardés
- mettre à jour les nouvelles informations sur les élements du nouvel ensemble
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | CREATE TABLE T(a integer, b text);
INSERT INTO T VALUES (1,'toto');
SELECT * FROM T;
BEGIN TRANSACTION;
CREATE TEMPORARY TABLE T_backup(a,b);
INSERT INTO T_backup SELECT a,b FROM T;
DROP TABLE T;
CREATE TABLE personnes (id integer, nom text, age integer);
INSERT INTO personnes(id,nom) SELECT a,b FROM T_backup;
UPDATE personnes SET age=20;
DROP TABLE T_backup;
COMMIT;
SELECT * FROM personnes;
|
On peut aussi détruire une table au lieu de l modfier par la commande SQL :
Cette commande permet de détruire l’ensemble des personnes
DROP TABLE personnes;
Attention : toute l’information stockée sera définitivement perdue.
Modification des colonnes
En créant ou après avoir créé une table on peut aussi définir des contraintes sur les colonnes de tables pour :
- obliger à insérer de l’information sur une colonne
- obliger à affecter des valeurs différentes sur une colonne
- affecter des valeurs par défaut sur une colonne
Ce sont les rôles des commandes SQL suivantes :
Donner obligatoirement une valeur à un identifiant lors de l’insertion d’un élément dans l’ensemble des personnes
ALTER TABLE personnes ALTER COLUMN id SET NOT NULL;
-- CREATE TABLE personnes (id integer NOT NULL, nom text, age smallint);
La commande ALTER TABLE en SQLite ne permet que de renommer une table ou ajouter une nouvelle colonne.
En SQLite pour modifier une colonne dans une table il faut :
- créer une table temporaire
- sauvegarder l’ensemble dans une table temporaire
- détruire l’ensemble sauvegardé
- créer le nouvel ensemble
- copier les élements sauvegardés
- mettre à jour les informations sur la colonne modifiée
1 2 3 4 5 6 7 8 | BEGIN TRANSACTION;
CREATE TEMPORARY TABLE personnes_backup(id,nom);
INSERT INTO personnes_backup SELECT id,nom FROM personnes;
DROP TABLE personnes;
CREATE TABLE personnes (id integer NOT NULL, nom text, age smallint DEFAULT 18);
INSERT INTO personnes(id,nom) SELECT id,nom FROM personnes_backup;
DROP TABLE personnes_backup;
COMMIT;
|
Donner des valeurs nécessairement différentes un identifiant lors de l’insertion d’un élément dans l’ensemble des personnes
ALTER TABLE personnes ADD CONSTRAINT personnes_id_unique UNIQUE(id);
-- CREATE TABLE personnes (id integer UNIQUE, nom text, age smallint);
La commande ALTER TABLE en SQLite ne permet que de renommer une table ou ajouter une nouvelle colonne.
En SQLite pour modifier une colonne dans une table il faut :
- créer une table temporaire
- sauvegarder l’ensemble dans une table temporaire
- détruire l’ensemble sauvegardé
- créer le nouvel ensemble
- copier les élements sauvegardés
- mettre à jour les informations sur la colonne modifiée
1 2 3 4 5 6 7 8 | BEGIN TRANSACTION;
CREATE TEMPORARY TABLE personnes_backup(id,nom);
INSERT INTO personnes_backup SELECT id,nom FROM personnes;
DROP TABLE personnes;
CREATE TABLE personnes (id integer UNIQUE, nom text, age smallint DEFAULT 18);
INSERT INTO personnes(id,nom) SELECT id,nom FROM personnes_backup;
DROP TABLE personnes_backup;
COMMIT;
|
Associer une valeur par défaut sur la colonne âge des personnes
ALTER TABLE personnes ALTER COLUMN age SET DEFAULT 18;
-- CREATE TABLE personnes (id integer NOT NULL, nom text, age SMALLINT DEFAULT 18);
La commande ALTER TABLE en SQLite ne permet que de renommer une table ou ajouter une nouvelle colonne.
En SQLite pour modifier une colonne dans une table il faut :
- créer une table temporaire
- sauvegarder l’ensemble dans une table temporaire
- détruire l’ensemble sauvegardé
- créer le nouvel ensemble
- copier les éléments sauvegardés
- mettre à jour les informations sur la colonne modifiée
1 2 3 4 5 6 7 8 | BEGIN TRANSACTION;
CREATE TEMPORARY TABLE personnes_backup(id,nom);
INSERT INTO personnes_backup SELECT id,nom FROM personnes;
DROP TABLE personnes;
CREATE TABLE personnes (id integer, nom text, age integer DEFAULT 18);
INSERT INTO personnes(id,nom) SELECT id,nom FROM personnes_backup;
DROP TABLE personnes_backup;
COMMIT;
|
Définir des contraintes de validité de valeurs sur la colonne age des personnes
ALTER TABLE personnes ADD CHECK (age > 16 AND age < 70);
-- CREATE TABLE personnes (id integer UNIQUE, nom text, age SMALLINT CHECK (age > 16 AND age < 70));
La commande ALTER TABLE en SQLite ne permet que de renommer une table ou ajouter une nouvelle colonne.
En SQLite pour modifier une colonne dans une table il faut :
- créer une table temporaire
- sauvegarder l’ensemble dans une table temporaire
- détruire l’ensemble sauvegardé
- créer le nouvel ensemble
- copier les élements sauvegardés
- mettre à jour les informations sur la colonne modifiée
1 2 3 4 5 6 7 8 | BEGIN TRANSACTION;
CREATE TEMPORARY TABLE personnes_backup(id,nom);
INSERT INTO personnes_backup SELECT id,nom FROM personnes;
DROP TABLE personnes;
CREATE TABLE personnes (id integer PRIMARY KEY, nom text, age smallint DEFAULT 18 CHECK (age > 16 AND age < 70);
INSERT INTO personnes(id,nom) SELECT id,nom FROM personnes_backup;
DROP TABLE personnes_backup;
COMMIT;
|
Clé primaire, étrangère
En créant ou après avoir créé une table on peut aussi définir des contraintes sur les colonnes de tables pour :
- définir une clé primaire (qui pourra être constitué d’une ou plusieurs colonnes de la table)
- définir une clé étrangère qui référence une clé primaire d’une autre table
La contrainte de clé primaire permettra de retrouver toute les autres informations sur un élément de l’ensemble et de vérifier à l’insertion d’un élément qu’il n’existe pas déjà dans l’ensemble (unicité d’un élément dans l’ensemble).
La contrainte de clé étrangère permettra d’associer les informations liées entre deux tables et de mettre à jour ces informations entre les deux tables (par exemple,s’il faut détruire tous les éléments d’un ensemble liés à un élément d’un autre ensemble en cas de destruction de ce dernier).
Ce sont les rôles des commandes SQL suivantes :
Définir une ou plusieurs colonnes comme clé primaire de l’ensemble des personnes
ALTER TABLE personnes ADD PRIMARY KEY(id);
-- CREATE TABLE personnes (id integer PRIMARY KEY, nom text, age smallint);
La commande ALTER TABLE en SQLite ne permet que de renommer une table ou ajouter une nouvelle colonne.
En SQLite pour modifier une colonne dans une table il faut :
- créer une table temporaire
- sauvegarder l’ensemble dans une table temporaire
- détruire l’ensemble sauvegardé
- créer le nouvel ensemble
- copier les élements sauvegardés
- mettre à jour les informations sur la colonne modifiée
1 2 3 4 5 6 7 8 | BEGIN TRANSACTION;
CREATE TEMPORARY TABLE personnes_backup(id,nom);
INSERT INTO personnes_backup SELECT id,nom FROM personnes;
DROP TABLE personnes;
CREATE TABLE personnes (id integer PRIMARY KEY, nom text, age smallint DEFAULT 18);
INSERT INTO personnes(id,nom) SELECT id,nom FROM personnes_backup;
DROP TABLE personnes_backup;
COMMIT;
|
Associer deux tables en référençant sur une clé primaire (PRIMARY KEY) d’une autre table.
Exemple : Associer plusieurs adresses à une même personne ?
1 2 3 4 5 6 | CREATE TABLE adresses (
id_adresse integer PRIMARY KEY,
id_personne integer,
adresse text,
FOREIGN KEY(id_personne) REFERENCES personnes
);
|
Remarque :
- dans une association un-à-plusieurs entre deux entités (une personne peut avoir plusieurs adresses) on pourra fera apparaître une clé étrangère du côté plusieurs de l’association.
Exercice récapitulatif
On se propose dans cet exercice de :
- Créer une table
Tcontenant deux colonnes(a,b)de type(integer,text)- Insérer dans cette table les 2-uplets
(1,'Jojo'),(2,'Bibi').- Renommer la table
Tenpersonnes.- Renommer les colonnes
a,benid,nom.- Ajouter une colonne
agede typeinteger.- Donner une valeur par défaut de 20 pour la colonne
ageaux éléments de la tablepersonnes.personnesVotre réponse :
sql : essai.sqlOutputUne solution possible :
sql : essai.sqlOutputOn obtient ainsi une ensemble de personnes. Chaque personne est identifiée de manière unique par la colonne
id.L’intérêt de cet exercice est de montrer qu’on peut faire évoluer un modèle de données relationnelles avec les éléments (informations) qu’il contient sans avoir à remettre en cause l’existant.
Nous invitons le lecteur intéressé à tester les autres commandes du langage (du moins celles supportées par SQLite)
Pour aller plus loin …
Le lecteur intéressé trouvera dans cette partie des informations sur la création de fonctions en langage de programmation SQL (PL/SQL) et de triggers (déclencheurs, fonctions réflexes).
Nous détaillerons partiellement cette partie dans le cadre de ce cours. En effet SQLite n’offre pas la possibilité de création de fonctions ou procédures stockés pour le développement d’application en mode client-serveur. Il faut dans ce cas utiliser un SGBD relationnel comme PostgreSQL, MySQL, ORACLE. Cependant SQLite permet de gérer les déclencheurs comme nous le verrons sur des exemples de triggers associés à des mises à jour (UPDATE) et destructions (DELETE) d’enregistrements dans une table.
Création de fonctions
Langages de création de fonctions (procédures stockées sur un serveur de base de données)
- PL/SQL : Programming Language en ORACLE
- PL/PGSQL : Programming Language en PostgreSQL
- …
Le langage de programmation de fonctions n’existe pas en standard en SQLite qui n’est pas prévu pour être utilisé en mode client-serveur sur le réseau.
Nous présentons donc uniquement une simple création de fonctions qui pourrait être mis en oeuvre avec le SGBD relationnel PostgreSQL .
Implémentation en PL/PGSQL d’une fonction pour calculer le pric TTC
1 2 3 4 5 6 7 | CREATE OR REPLACE
FUNCTION prix_ttc (hors_taxe numeric) RETURNS numeric AS
$$
BEGIN
RETURN hors_taxe*1.2;
END
$$ LANGUAGE 'plpgsql';
|
On peut alors appeler la fonction dans du code SQL :
SELECT prix_ttc(10);
SELECT * from prix_ttc(10) ;
On peut également supprimer la fonction :
DROP FUNCTION prix_ttc (hors_taxe numeric);
Création de triggers
Une fonction peut-être associée à un trigger pour effectuer les traitements lors d’une manipulation de la base par un utilisateur. On peut considérer alors cette association, fonction-déclencheur, comme une fonction réflexe (callback) lors d’une action utilisateur, ce qui constitue les fondements de la programmation événementielle.
Le premier exemple proposé illustre ce mécanisme qui pourra être testé avec le SGBD relationnel PostgreSQL malheureusement SQLite ne propose pas encore cette possibilité, cependant il permet de gérer les déclencheurs comme nous le verrons dans un deuxième exemple.
Gestion d’un trigger pour la destruction automatique des « adresses » d’une personne.
On créé d’abord la base de données des personnes et de leurs adresses
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | CREATE TABLE personnes (id integer NOT NULL UNIQUE, nom text);
CREATE TABLE adresses (personne_id integer, adresse text);
INSERT INTO personnes(id,nom) VALUES (1,'Dupond');
INSERT INTO personnes(id,nom) VALUES (2,'Durand');
INSERT INTO adresses(personne_id,adresse) VALUES (
(SELECT id FROM Personnes WHERE nom='Dupond'),
'Brest'
);
INSERT INTO adresses(personne_id,adresse) VALUES (
(SELECT id FROM Personnes WHERE nom='Dupond'),
'Quimper'
);
INSERT INTO adresses(personne_id,adresse) VALUES (
(SELECT id FROM Personnes WHERE nom='Durand'),
'Brest'
);
|
On implémente ensuite la fonction qui sera déclenchée (trigger) automatiquement pour mettre à jour la base de données en cas de destruction d’une personne.
Cette fonction a pour rôle de supprimer toutes les adresses de la personne que l’on veut enlever de la base de données.
1 2 3 4 5 6 7 | CREATE OR REPLACE FUNCTION delete_adresses() RETURNS TRIGGER AS
$$
BEGIN
DELETE FROM adresses WHERE personne_id = OLD.id;
RETURN OLD;
END
$$ LANGUAGE 'plpgsql';
|
On créé alors un déclencheur (trigger) qui appelera cette fonction avant chaque destruction d’un élément dans l’ensemble des personnes.
1 2 3 | CREATE TRIGGER delete_personne_adresse
BEFORE DELETE ON personnes
FOR EACH ROW EXECUTE PROCEDURE delete_adresses();
|
Le lecteur ayant installé PostgreSQL pourra alors tester les effets du déclenchement de ce trigger sur la base de données existante en exécutant le code suivant :
1 2 3 4 5 6 7 | SELECT * FROM personnes;
SELECT * FROM adresses;
DELETE FROM personnes WHERE nom = 'Dupond';
SELECT * FROM personnes;
SELECT * FROM adresses;
|
Cet exemple illustre le mécanisme de destruction en cascade de clés étrangères mis en oeuvre dans les différents SGBD relationnels.
Pour supprimer le trigger :
DROP TRIGGER delete_personne_adresse ON personnes;
Le langage SQLite intègre la mise en place de déclencheurs (triggers) qui permettent de faire des traitements sur la base de données lorsque l’utilisateur lance des commandes SQL. Nous présentons ici la mise en oeuvre de deux triggers.
On remarquera sur la première ligne des exemples de code SQLite la nécessité d’activer la gestion des triggers avec la la commande :
PRAGMA recursive_triggers=1;
Le premier exemple sert à faire une mise à jour sur une table associée (T2) à celle (T1) où l’on utilisera la commande SQL UPDATE. Le deuxième servira à mettre à jour des références sur un enregistrement lors d’une destruction de l’enregistrement (DELETE).
sql : essai.sqlOutput
Pour supprimer le trigger :
DROP TRIGGER T1_shared_updated;
L’exemple suivant permettra de détruire dans une table (T2) les enregistrements ayant des références associées à un enregistrement dans une table (T1) lorsque ce dernier est supprimé.
On remarquera l’utilisation du « pseudo-record » OLD à la place du nouveau (NEW) étant donné qu’il n’y a pas de nouvelle valeur du fait de la destruction de l’enregistrement dans la table T1.
sql : essai.sqlOutput