• 20 heures
  • Moyenne

Ce cours est visible gratuitement en ligne.

course.header.alt.is_video

course.header.alt.is_certifying

J'ai tout compris !

Mis à jour le 22/11/2019

Créez votre base de données entièrement avec SQL

Connectez-vous ou inscrivez-vous gratuitement pour bénéficier de toutes les fonctionnalités de ce cours !

Dans la partie précédente, je vous ai montré comment modéliser dans le détail votre schéma de base de données.

C'est désormais le moment de le créer pour de bon dans votre base de données !

Pour cela, vous allez utiliser le langage SQL : Structured Query Language.

Générez le script SQL de création

Une fois votre MPD terminé, vous allez pouvoir demander à SQL Power Architect de générer automatiquement le script SQL de création du schéma de la base de données. Et en plus, il respectera les spécificités du SGBD de destination. C'est pas magnifique !?

Je vais reprendre mon exemple de gestion de tickets. Voici pour rappel le MPD que j'ai créé :

MPD Gestion de tickets

Pour générer le script SQL :

  1. Cliquez sur l'icône .

  2. Une fenêtre s'ouvre vous permettant de configurer la génération.

    • Create in : sélectionnez PlayPen Database.

    • Generate DDL for Database Type : sélectionnez le SGBD de destination, ici PostgreSQL.

    • Schema : le nom du schéma dans la base de données. Le schéma par défaut dans une base PostgreSQL est public.

  3. Cliquez sur le bouton OK.

  4. Il se peut que votre MPD comporte des erreurs, dans ce cas, une fenêtre les listant sera affichée :

    Dans cet exemple, il n'y a que des erreurs portant sur des éléments qui utilisent le même nom. Il s'agit ici de colonnes avec des noms de séquences identiques. Après vérification, vous remarquez que les erreurs portent sur des colonnes non auto-incrémentées ! (Il s'agit d'un bug dans le logiciel, car les colonnes n'étant pas auto-incrémentées, les séquences ne seront pas créées. Cela ne posera donc pas de problème.)

    Vous pouvez donc ignorer les erreurs et cliquer sur le bouton Ignore Warnings.

  5. Une dernière fenêtre s'ouvre avec le contenu du script SQL ! Il ne vous reste plus qu'à l'enregistrer dans un fichier create_db.sql par exemple (remarquez l'utilisation de l'extension .sql).

Analysez le script SQL généré

Je vais vous expliquer les différentes instructions SQL que vous allez trouver dans le script.

Mais avant cela, voici quelques éléments pour comprendre la syntaxe SQL.

La syntaxe SQL

Comme je vous l'ai déjà dit, un script SQL est un fichier contenant un ensemble d'instructions. Les instructions sont exécutées les unes après les autres, dans l'ordre où elles apparaissent dans le script SQL.

Chaque instruction se termine par un ;.

Voici un script SQL avec 2 instructions :

CREATE SEQUENCE public.niveau_bug_id_seq;
CREATE SEQUENCE public.statut_id_seq;

Il est possible d'ajouter des commentaires qui seront ignorés par le SGBD, avec -- ou /* */ :

-- Voici un commentaire sur une ligne

/*  Voici un commentaire
    sur plusieurs lignes */

Les chaînes de caractères sont mises entre simples guillemets ' :

SELECT * FROM projet
WHERE nom = 'Mon super projet';

Les éléments sont nommés en utilisant la notation pointée element.sous_element :

--- afficher la table "projet" se trouvant dans le schéma "public" :
SELECT * FROM public.projet;

Par habitude, bien que cela ne soit pas obligatoire, les commandes SQL, et plus généralement tous les mots clés du langage, sont écrits en majuscules. Cela améliore la lisibilité des requêtes.

Les espaces multiples et les retours à la ligne n'ont pas de signification en SQL. N'hésitez pas à les utiliser pour obtenir une requête SQL plus lisible :

-- je suis sur que vous trouvez celle-ci plus lisible :

SELECT
    projet.nom,
    ticket.numero,
    ticket.titre
FROM
    public.ticket
    JOIN public.projet ON projet.id = ticket.projet_id
WHERE
    projet_id = 10
    AND date >= '2016-01-01'
    AND date <= '2016-01-20';


-- que celle là :

select projet.nom,ticket.numero,ticket.titre from
public.ticket join public.projet on projet.id=ticket.projet_id
where projet_id=10 and date>='2016-01-01' and date<='2016-01-20';

Création des séquences

CREATE SEQUENCE public.niveau_bug_id_seq;

Cela va créer la séquence niveau_bug_id_seq dans le schéma public.

ALTER SEQUENCE public.niveau_bug_id_seq OWNED BY public.niveau_bug.id;

Cela va associer la séquence niveau_bug_id_seq à la colonne id de la table niveau_bug. Ainsi, si la colonne est supprimée, la séquence sera automatiquement supprimée.

Création des tables

CREATE TABLE public.niveau_bug (
    id INTEGER NOT NULL DEFAULT nextval('public.niveau_bug_id_seq'),
    ordre INTEGER NOT NULL,
    libelle VARCHAR(100) NOT NULL,
    CONSTRAINT niveau_bug_pk PRIMARY KEY (id)
);

Ceci va créer la table niveau_bug dans le schéma public avec les colonnes suivantes :

nom

type

nullable

valeur par défaut

id

INTEGER

non

nextval('public.niveau_bug_id_seq')

ordre

INTEGER

non

 

libelle

VARCHAR(100)

non

 

La clé primaire de la table sera composée de la colonne id et sera associée à une contrainte d'unicité nommée niveau_bug_pk. Ce sera également le nom de l'index de clé primaire créé automatiquement par le SGBD.

La valeur par défaut dans la colonne id est nextval('public.niveau_bug_id_seq'). Cela veut dire que si on ne précise rien, lors de l'ajout d'une ligne, la valeur suivante de la séquence public.niveau_bug_id_seq sera utilisée dans la colonne id. C'est comme cela que sont gérées les colonnes auto-incrémentées avec PostgreSQL.

Un autre exemple de création de table :

CREATE TABLE public.historique_statut (
    ticket_numero INTEGER NOT NULL,
    statut_id INTEGER NOT NULL,
    date TIMESTAMP NOT NULL,
    commentaire_id INTEGER,
    utilisateur_id INTEGER NOT NULL,
    CONSTRAINT historique_statut_pk PRIMARY KEY (ticket_numero, statut_id)
);

Ici, la clé primaire de la table historique_statut est constituée des colonnes ticket_numero et statut_id. La colonne commentaire_id est nullable (car la multiplicité de l'association est 0..1).

Création de relations (clés étrangères)

Les clés étrangères sont ajoutées une fois que toutes les tables ont été créées.

ALTER TABLE public.commentaire ADD CONSTRAINT utilisateur_commentaire_fk
FOREIGN KEY (utilisateur_id)
REFERENCES public.utilisateur (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE;

Ceci modifie la table commentaire du schéma public pour y ajouter une contrainte de clé étrangère nommée utilisateur_commentaire_fk, sur la colonne utilisateur_id et pointant sur la colonne id de la table utilisateur du schéma public.

ON DELETE NO ACTION signifie qu'en cas de suppression d'une ligne dans la table utilisateur, si une ligne de la table commentaire utilise la valeur de la clé primaire de cette ligne dans sa clé étrangère, alors une erreur est produite.

ON UPDATE NO ACTION signifie qu'en cas de modification de la valeur d'une clé primaire dans la table utilisateur, si une ligne de la table commentaire utilise cette valeur dans sa clé étrangère, alors une erreur est produite.

NOT DEFERRABLE signifie que les erreurs (dues au ON DELETE NO ACTION et ON UPDATE NO ACTION) sont remontées immédiatement et non à la validation de la transaction. (Nous verrons ce qu'est une transaction plus loin dans ce cours.)

ALTER TABLE public.bug_version_affectee ADD CONSTRAINT version_bug_version_affectee_fk
FOREIGN KEY (version_affectee_projet_id, version_affectee_numero)
REFERENCES public.version (projet_id, numero)
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE;

Ici la clé étrangère est constituée d'un couple de colonnes (version_affectee_projet_id;version_affectee_numero) pointant sur la clé primaire elle aussi constituée de deux colonnes (projet_id;numero).

Exécutez le script SQL de création du schéma

Nous arrivons à l'avant-dernière étape de création de la base de données : la création du schéma.

Il suffit pour cela de faire exécuter le script SQL par PostgreSQL.

Eh bien, là encore, vous allez utiliser pgAdmin (ou SQuirreL SQL...) :

  1. Connectez-vous au serveur PostgreSQL en utilisant l'utilisateur propriétaire de la base de données.

  2. Cliquez sur l'icône SQL.

    Une fenêtre s'ouvre vous permettant de saisir des requêtes SQL.

  3. Ouvrez le fichier create_db.sql que vous avez enregistré depuis SQL Power Architect.

  4. Exécutez le script SQL en cliquant sur l'icône .

Félicitations, vous venez de créer votre première base de données !

Dernière étape : ajouter des données dans la base de données et utiliser cette base.

C'est ce que nous allons voir maintenant.

Exemple de certificat de réussite
Exemple de certificat de réussite