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
  • QCM
  • Introduction
  • Commandes de bases
  • Langage de définition de données
    • Structuration des données
    • Création de fonctions
    • Evolution de modèles de données
      • Modification des tables
      • Modification des colonnes
    • Exercice récapitulatif
  • Types de données
  • Requêtes
  • Présentation
  • Calcul relationnel
  • Algèbre relationnelle
  • Division relationnelle
  • Dépendances fonctionnelles
  • Décomposition de relations
  • Inférence logique
  • Normalisation
  • Aux pays des bières
  • Modélisation
  • Exercices
  • Présentation
  • Aux pays des bières
  • Au Tournoi des six nations
  • Salles de concerts
  • Définitions
  • Langage SQL
  • Modèle relationnel
  • Films
  • Repas
  • Repas
Index

Téléchargements

  • Site
  • Sources
  • EniBook
logo

Crédits

© Your Copyright

Aide

En-tête

MenuContenu
Sommaire,
Téléchargements
Aide sur les outils

Pied de page

ChevronAction
Aller en haut de la page courante
Aller en bas de la page courante
Passer à la page précédente
Passer à la page suivante

Langage de définition de données

Structuration des données

Nous présentons ici les commandes SQL pour:

  • créer des ensembles
  • définir des espaces de nommage
  • encapsuler des requêtes dans des vues
  • créer des index sur des colonnes de tables pour accélérer la recherche d’informations
  • créer des domaines (type de données avec des contraintes optionnelles)sur l’ensemble de valeurs autorisées)
  • créer de nouveaux types de données SQL

Attention : Toutes ces commandes n’existent pas en SQLite mais pourront être mises en œuvre en PostgreSQL

CREATE TABLE 

1
CREATE TABLE personnes (id integer, nom text);

Création d’un ensemble (vide) de personnes

CREATE SCHEMA 

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;

Définir un schéma (espage de nommage) pour créer ses entités (tables) qui seront distinctes d’autres entités (tabbles) de même nom et qui seraient créées sour un autre schéma.

Attention : cette commande n’existe pas en SQLite, il faut créer une nouvelle base (un nouveau fichier) :

1
2
3
4
$ sqlite3 ENIB
sqlite> CREATE TABLE personnes (id integer, nom text);
$ sqlite3 NEDELEC
sqlite> CREATE TABLE personnes (id integer, nom text);

CREATE VIEW 

1
2
CREATE VIEW lesnoms AS SELECT nom FROM personnes;
SELECT * FROM lesnoms;

« Encapsuler » une requête SQL dans une vue.

CREATE INDEX 

CREATE INDEX age_capitaine ON personnes(age);

Créer un index pour rechercher plus efficacement l’âge des personnes.

  • CREATE DOMAIN

CREATE DOMAIN 

CREATE DOMAIN colors VARCHAR(10) CHECK (VALUE IN ('red', 'green', 'blue'));

Créer un domaine pour définir un ensemble de couleurs.

CREATE TYPE 

CREATE TYPE addresse AS (numero SMALLINT, rue VARCHAR(100), ville VARCHAR(50));

Créer un nouveau type de données pour définir une adresse.

Création de fonctions

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
  • …

N’existe pas en standard en SQLite qui n’est pas prévu pour fonctionner en mode client-serveur sur le réseau.

CREATE FUNCTION

implémenter 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';
  • CREATE TRIGGER : déclencheurs sur modification de tables

CREATE TRIGGER

gestion d’un trigger pour la destruction automatique de « adresses » d’une personne.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE personnes (id integer, nom text);
CREATE TABLE adresses (id_personne integer, adresse text);

CREATE FUNCTION delete_adresses() RETURNS TRIGGER AS
$$
BEGIN
  DELETE FROM adresses WHERE id_personne = OLD.id;
  RETURN OLD;
END
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER delete_personne_adresse
BEFORE DELETE ON personnes
FOR EACH ROW EXECUTE PROCEDURE delete_adresses();

INSERT INTO personnes(nom) VALUES ('Dupond');
INSERT INTO adresses(adresse) VALUES ('Brest');

DELETE FROM personnes WHERE nom = 'Dupond';

Evolution de modèles 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

ALTER TABLE

On peut renommer une table :

CREATE TABLE T(a integer, b text);
INSERT INTO T VALUES (1,"toto");
SELECT * FROM T;
ALTER TABLE T RENAME TO personnes;
SELECT * FROM personnes;

Ajouter une colonne (connaître l’âge des personnes)

ALTER TABLE personnes ADD COLUMN age SMALLINT;

la commande ALTER TABLE en SQLite ne permet que de renommer une table ou ajouter une nouvelle colonne.

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;

D’autres langages comme PostgreSQL permettent de faire ce genre d’actions sur une base de données de la manière suivante :

DROP TABLE

Détruire l’ensemble des personnes

DROP TABLE personnes;

Modification des colonnes

DEFAULT

Associer une valeur par défaut sur la colonne âge des personnes

ALTER TABLE personnes ALTER COLUMN age SET DEFAULT 18;

la commande ALTER TABLE en SQLite ne permet que de renommer une table ou ajouter une nouvelle colonne.

ALTER TABLE personnes ADD COLUMN age DEFAULT 18;

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;

NOT NULL

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);

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;

UNIQUE

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);

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;

PRIMARY KEY

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);
-- CREATE  TABLE personnes (id integer NOT NULL UNIQUE, nom text, age smallint);

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;

FOREIGN KEY

Associer deux tables en référençant sur une clé primaire (PRIMARY KEY) d’une autre table.

Exemple : comment associer plusieurs adresses à une même personnes ?

1
2
3
4
CREATE TABLE adresses (id_adresse integer PRIMARY KEY,
                       id_personne integer,
                       adresse text,
                       FOREIGN KEY(id_personne) REFERENCES personnes);

CHECK

Définir des contraintes de validité de valeurs sur la colonne age des personnes

ALTER TABLE personnes ADD CHECK (age > 16 AND age < 70);

Exercice récapitulatif

On se propose de :

  1. Créer une table T contenant deux colonnes (a,b) de type (integer,text)
  2. Insérer dans cette table les 2-uplets (1,'Jojo'),(2,'Bibi').
  3. Renommer la table T en personnes.
  4. Renommer les colonnes a,b en id,nom.
  5. Ajouter une colonne age de type integer.
  6. Donner une valeur par défaut de 20 pour la colonne age aux éléments de la table personnes.
personnes
  • Question
  • Solution
Votre réponse :
sql : essai.sql 

    

        

            >
            
                
                
            
        

        

            Output
            

                

                

                

                

                
            
 
        

    

    
        
        
        
        
    
    





    
Interpréteur

    

        
MenuAction

        
            

                
                Ré-initialiser les sorties
            

            

                
                Faire apparaître le menu d'aide
            

            

                
                Interpréter le programme
            

        
    

    
Editeur

    

        
MenuRaccouciAction

        
            

                
                Ctrl+N
                Initialiser l'éditeur
            

            

                
                Ctrl+O
                Charger le contenu d'un fichier dans l'éditeur
            

            

                
                Ctrl+S
                Sauvegarder le contenu de l'éditeur dans un fichier
            

            

                
                Ctrl+P
                Imprimer le contenu de l'éditeur
            

            

                
                Ctrl+Z
                Annuler la dernière modification
            

            

                
                Maj+Ctrl+Z
                Rétablir la modification précedente
            

            

                
                Ctrl+F
                Chercher une expression dans l'éditeur
            

            

                
                Maj+Ctrl+F
                Chercher et remplacer une expression par une autre
            

            
            

                
                F10
                Ouvrir une documentation du langage
            

        
    

    

    

        
RaccourciAction

        
            

                F1
                Afficher cette aide
            

            

                Tab
                Indenter la sélection
            

            

                Maj+Tab
                Désindenter la sélection
            

            

                Ctrl+A
                Sélectionner le contenu de l'éditeur
            

            

                Ctrl+C
                Copier la sélection dans le presse-papier
            

            

                Ctrl+V
                Remplacer la sélection par le contenu du presse-papier
            

            

                Ctrl+X
                Supprimer la sélection et la copier dans le presse-papier
            

            

                Maj+Ctrl+R
                Chercher et remplacer une expression par une autre dans tout l'éditeur
            

        
    












    
Une solution possible : 





    
    sql : 
    essai.sql




    

        
            
                
                
                
                
            
            
                
                
                
                
            
            
                
                
            
        
    

    

        
        
        
    

    

    

    

    

        

            >
            
                
                
            
        

        

            Output
            

                

                

                

                

                
            
 
        

    

    
        
        
        
        
    
    





    
Interpréteur

    

        
MenuAction

        
            

                
                Ré-initialiser les sorties
            

            

                
                Faire apparaître le menu d'aide
            

            

                
                Interpréter le programme
            

        
    

    
Editeur

    

        
MenuRaccouciAction

        
            

                
                Ctrl+N
                Initialiser l'éditeur
            

            

                
                Ctrl+O
                Charger le contenu d'un fichier dans l'éditeur
            

            

                
                Ctrl+S
                Sauvegarder le contenu de l'éditeur dans un fichier
            

            

                
                Ctrl+P
                Imprimer le contenu de l'éditeur
            

            

                
                Ctrl+Z
                Annuler la dernière modification
            

            

                
                Maj+Ctrl+Z
                Rétablir la modification précedente
            

            

                
                Ctrl+F
                Chercher une expression dans l'éditeur
            

            

                
                Maj+Ctrl+F
                Chercher et remplacer une expression par une autre
            

            
            

                
                F10
                Ouvrir une documentation du langage
            

        
    

    

    

        
RaccourciAction

        
            

                F1
                Afficher cette aide
            

            

                Tab
                Indenter la sélection
            

            

                Maj+Tab
                Désindenter la sélection
            

            

                Ctrl+A
                Sélectionner le contenu de l'éditeur
            

            

                Ctrl+C
                Copier la sélection dans le presse-papier
            

            

                Ctrl+V
                Remplacer la sélection par le contenu du presse-papier
            

            

                Ctrl+X
                Supprimer la sélection et la copier dans le presse-papier
            

            

                Maj+Ctrl+R
                Chercher et remplacer une expression par une autre dans tout l'éditeur
            

        
    










    

    

        
            
    





On 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)







 

    

    
    

        Systèmes d'Information : Langage de définition de données, 12 févr. 2018.