Skip to content

1 4 7 1 creation

Schémas relationnel ⅓ : création d'un schéma SQL

Ce chapitre présente le langage de définition de données (LDD) qui permet de spécifier le schéma d'une base de données relationnelle. Ce langage correspond à une partie de la norme SQL (structured query language), l'autre partie étant relative à la manipulation des données (LMD).

La définition d'un schéma comprend essentiellement deux parties : d'une part la description des tables, d'autre part les contraintes qui portent sur leur contenu. La spécification des contraintes est souvent placée au second plan bien qu'elle soit en fait très importante : elle permet d'assurer, au niveau de la base, des contrôles sur l'intégrité des données qui s'imposent à toutes les applications accédant à cette base. Un dernier aspect de la définition d'un schéma, rapidement survolé ici, est la description de la représentation dite "physique", celle qui décrit l'organisation des données. Il est toujours possible de réorganiser une base, et on peut donc tout à fait adopter initialement l'organisation choisie par défaut pour le système.

Création d'un schéma SQL

Supports complémentaires

Passons aux choses concrètes : vous avez maintenant un serveur de base de données en place, vous disposez d'un compte d'accès, vous avez conçu votre base de données et vous voulez concrètement la mettre en œuvre. Nous allons prendre pour fil directeur la base des films. La première chose à faire est de créer une base spécifique avec la commande suivante :

create database Films

Il est d'usage de créer un utilisateur ayant les droits d'administration de cette base.

grant all on Films.* to philippe identified by 'motdepasse'

Voilà, maintenant il est possible d'ouvrir une connexion à la base Film sous le compte philippe et de créer notre schéma.

Types SQL

La norme SQL ANSI propose un ensemble de types dont les principaux sont donnés dans le tableau ci-dessous. Ce tableau présente également la taille, en octets, des instances de chaque type, cette taille n'étant donnée ici qu'à titre indicatif car elle peut varier selon les systèmes.

Type Description Taille
integer Type des entiers relatifs 4 octets
smallint idem 2 octets
bigint idem 8 octets
float Flottants simple précision 4 octets
double Flottants double précision 8 octets
real Flottant simple ou double 8 octets
numeric (M, D) Numérique avec précision fixe. M octets
decimal(M, D) Idem. M octets
char(M) Chaînes de longueur fixe M octets
varchar*(M*) Chaînes de longueur variable L+1 avec \(L \leq M\)
bit varying Chaînes d'octets Longueur de la chaîne.
date Date (jour, mois, an) env. 4 octets
time Horaire (heure, minutes, secondes) env. 4 octets
datetime Date et heure 8 octets
year Année 2 octets

Types numériques exacts

La norme SQL ANSI distingue deux catégories d'attributs numériques : les numériques exacts, et les numériques flottants. Les types de la première catégorie (essentiellement integer et decimal) permettent de spécifier la précision souhaitée pour un attribut numérique, et donc de représenter une valeur exacte. Les numériques flottants correspondent aux types couramment utilisés en programmation (float, double) et ne représentent une valeur qu'avec une précision limitée.

Le type integer permet de stocker des entiers, sur 4 octets. Il existe deux variantes du type integer : smallint et bigint. Ces types diffèrent par la taille utilisée pour le stockage : voir le tableau des types SQL.

Le type decimal(M, D) correspond à un numérique de taille maximale M, avec un nombre de décimales fixé à D. numeric est un synonyme de decimal. Ces types sont surtout utiles pour manipuler des valeurs dont la précision est connue, comme les valeurs monétaires. Afin de préserver cette précision, les instances de ces types sont stockées comme des chaînes de caractères.

Types numériques flottants

Ces types s'appuient sur la représentation des numériques flottants propre à la machine, en simple ou double précision. Leur utilisation est donc analogue à celle que l'on peut en faire dans un langage de programmation comme le C.

  • Le type float correspond aux flottants en simple précision.
  • Le type double precision correspond aux flottants en double précision ; le raccourci double est accepté.

Caractères et chaînes de caractères

Les deux types principaux de la norme ANSI sont char et varchar. Ces deux types permettent de stocker des chaînes de caractères d'une taille maximale fixée par le paramètre M. Les syntaxes sont identiques. Pour le premier, char(M), et varchar(M) pour le second. La différence essentielle est qu'une valeur char a une taille fixée, et se trouve donc complétée avec des blancs si sa taille est inférieure à M. En revanche une valeur varchar a une taille variable et est tronquée après le dernier caractère non blanc.

Quand on veut stocker des chaînes de caractères longues (des textes, voire des livres), dont la taille dépasse, typiquement, 255 caractères, le type varchar ne suffit plus. La norme SQL propose un type bit varying qui correspond à de très longues chaînes de caractères. Souvent les systèmes proposent des variantes de ce type sous le nom text ou blob (pour Binary Long Object).

Dates

Un attribut de type date stocke les informations jour, mois et année (sur 4 chiffres). La représentation interne n'est pas spécifiée par la norme. Tous les systèmes proposent de nombreuses opérations de conversion (non normalisées) qui permettent d'obtenir un format d'affichage quelconque.

Un attribut de type time représente un horaire avec une précision à la seconde. Le type datetime permet de combiner une date et un horaire.

Création des tables

D'une manière générale, les objets du schéma sont créés avec create, modifiés avec alter et détruits avec drop, alors que les données, instances du schéma sont créées, modifiées et détruites avec, respectivement, insert, update et delete.

Voici un premier exemple avec la commande de création de la table Internaute.

create table Internaute (email varchar (40) not null, 
                        nom varchar (30) not null ,
                        prénom varchar (30) not null,
                        région varchar (30),
                        primary key (email));

La syntaxe se comprend aisément. La seule difficulté est de choisir correctement le type de chaque attribut.

Conventions : noms des tables, des attributs, mots-clé SQL
On dispose, comme dans un langage de programmation, d'une certaine liberté. La seule recommandation est d'être cohérent pour des raisons de lisibilité. D'une manière générale, SQL n'est pas sensible à la casse. Quelques propositions :

  • Le nom des tables devrait commencer par une majuscule, le nom des attributs par une minuscule ;
  • quand un nom d'attribut est constitué de plusieurs mots, on peut soit les séparer par des caractères '_', soit employer la convention CamelCase : minuscule au premier mot, majuscule aux suivants. Exemple : mot_de_passe ou motDePasse.
  • Majuscule ou minuscule pour les mots-clés SQL ? Quand on inclut une commande SQL dans un langage de programmation, il est peut-être plus lisible d'utiliser des majuscules pour les mots-clés.
  • Les accents et caractères diacritiques sont-ils acceptés ? En principe oui, c'est ce que nous faisons ici. Cela implique de pouvoir aussi utiliser des accents dans les programmes qui incluent des commandes SQL et donc d'utiliser un encodage de type UTF8. Il faut vérifier si c'est possible dans l'environnement de développement que vous utilisez. Dans le doute, il vaut peut-être mieux sacrifier les accents.

Le not null dans la création de table Internaute indique que l'attribut correspondant doit toujours avoir une valeur. Il s'agit d'une différence importante entre la pratique et la théorie : on admet que certains attributs peuvent ne pas avoir de valeur, ce qui est très différent d'une chaîne vide ou de 0. Il est préférable d'ajouter la contrainte not null quand c'est pertinent : cela renforce la qualité de la base et facilite le travail des applications par la suite. L'option suivante permet ainsi de garantir que tout internaute a un mot de passe.

motDePasse  varchar(60) not null

Le SGBD rejettera alors toute tentative d'insérer un nuplet dans Internaute sans donner de mot de passe.

Important
La clé primaire doit toujours être déclarée not null.

Une autre manière de forcer un attribut à toujours prendre une valeur est de spécifier une valeur par défaut avec l'option default.

create table Cinéma (id integer not null, 
                     nom varchar (30) not null ,
                     adresse varchar(255) default 'Inconnue',
                     primary key (id));

Quand on insérera un nuplet dans la table Cinéma sans indiquer d'adresse, le système affectera automatiquement la valeur 'Inconnue' à cet attribut. En général on utilise comme valeur par défaut une constante, sauf pour quelques variables fournies par le système (par exemple sysdate pour indiquer la date courante).

Contraintes

La création d'une table telle qu'on l'a vue précédemment est assez sommaire car elle n'indique que le contenu de la table sans spécifier les contraintes que doit respecter ce contenu. Or il y a toujours des contraintes et il est indispensable de les inclure dans le schéma pour assurer (dans la mesure du possible) l'intégrité de la base.

Voici les règles (ou contraintes d'intégrité) que l'on peut demander au système de garantir :

  • La valeur d'un attribut doit être unique au sein de la table.
  • Un attribut doit toujours avoir une valeur. C'est la contrainte not null vue précédemment.
  • Un attribut (ou un ensemble d'attributs) constitue(nt) la clé de la table.
  • Un attribut dans une table est liée à la clé primaire d'une autre table (intégrité référentielle).
  • Enfin toute règle s'applique à la valeur d'un attribut (min et max par exemple).

Les contraintes sur les clés (unicité et intégrité référentielle) doivent être systématiquement spécifiées.

Clés d'une table

Il peut y avoir plusieurs clés dans une table (les clés "candidates") mais l'une d'entre elles doit être choisie comme clé primaire. Ce choix est important : la clé primaire est la clé utilisée pour référencer un nuplet et un seul à partir d'autres tables. Il est donc très délicat de la remettre en cause après coup. En revanche les clés secondaires peuvent être créées ou supprimées beaucoup plus facilement.

La clé primaire est spécifiée avec l'option primary key.

create table Pays (code varchar(4) not null,
                   nom  varchar (30) not null,
                   langue varchar (30) not null,
                   primary key (code));

Il doit toujours y avoir une clé primaire dans une table. Elle sert à garantir l'absence de doublon et à désigner un nuplet de manière univoque. Une clé peut être constituée de plusieurs attributs :

create table Notation (idFilm integer not null,
                       email  varchar (40) not null,
                       note  integer not null,
                       primary key (idFilm, email));

Tous les attributs figurant dans une clé doivent être déclarés not null. Cela n'a pas de sens d'identifier des nuplets par des valeurs absentes.

Comme nous l'avons déjà expliqué à plusieurs reprises, la méthode recommandée pour gérer la clé primaire est d'utiliser un attribut id, sans aucune signification particulière autre que celle de contenir la valeur unique identifiant un nuplet. Voici un exemple typique :

create table Artiste (id integer not null,
                      nom varchar (30) not null,
                      prénom varchar (30) not null,
                      annéeNaiss integer,
                      primary key (id))

La valeur de cet identifiant peut même être automatiquement engendrée à chaque insertion, ce qui soulage d'avoir à implanter un mécanisme de génération d'identifiant. La méthode varie d'un système à l'autre, et repose de manière générale sur la notion de séquence. Voici la syntaxe MySQL pour indiquer qu'une clé est auto-incrémentée.

create table Artiste (id integer not null auto increment,
                     ...,
                      primary key (id))

L'utilisation d'un identifiant artificiel n'apporte rien pour le contrôle des redondances. Il est possible d'insérer des centaines de nuplets dans la table Artiste ci-dessus ayant tous exactement les mêmes valeurs, et ne différant que par la clé.

Les contraintes empêchant la redondance (et plus généralement assurant la cohérence d'une base) sont spécifiées indépendamment de la clé par la clause unique. On peut par exemple indiquer que deux artistes distincts ne peuvent avoir les mêmes nom et prénom.

create table Artiste  (idArtiste integer not null,
                      nom varchar (30) not null,
                      prénom varchar (30) not null,
                      annéeNaiss integer,
                      primary key (idArtiste),
                      unique (nom, prénom))

Il est facile de supprimer cette contrainte (dite de "clé secondaire") par la suite. Ce serait beaucoup plus difficile si on avait utilisé la paire (nom, prénom) comme clé primaire puisqu'elle serait alors utilisée pour référencer un artiste dans d'autres tables.

La clause unique ne s'applique pas aux valeurs null.

Clés étrangères

SQL permet d'indiquer quelles sont les clés étrangères dans une table, autrement dit, quels sont les attributs qui font référence à un nuplet dans une autre table. On peut spécifier les clés étrangères avec l'option foreign key.

create table Film  (idFilm integer not null, 
                   titre    varchar (50) not null,
                   année    integer not null,
                   idRéalisateur    integer not null,
                   genre varchar (20) not null,
                   résumé      varchar(255),
                   codePays    varchar (4),
                   primary key (idFilm),
                   foreign key (idRéalisateur) references Artiste(idArtiste),
                   foreign key (codePays) references Pays(code));

La commande

foreign key (idRéalisateur) references Artiste(idArtiste),

indique que idRéalisateur référence la clé primaire de la table Artiste. Le SGBD vérifiera alors, pour toute modification pouvant affecter le lien entre les deux tables, que la valeur de idRéalisateur correspond bien à un nuplet de Artiste. Ces modifications sont :

  • l'insertion dans Film avec une valeur inconnue pour idRéalisateur ;
  • la destruction d'un artiste ;
  • la modification de id dans Artiste ou de idRéalisateur dans Film.

En d'autres termes on a la garantie que le lien entre Film et Artiste est toujours valide. Cette contrainte est importante pour s'assurer qu'il n'y a pas de fausse référence dans la base, par exemple qu'un film ne fait pas référence à un artiste qui n'existe pas. Il est beaucoup plus confortable d'écrire une application par la suite quand on sait que les informations sont bien là où elles doivent être.

Il faut noter que l'attribut codePays n'est pas déclaré not null, ce qui signifie que l'on s'autorise à ne pas connaître le pays de production d'un film. Quand un attribut est à null, la contrainte d'intégrité référentielle ne s'applique pas. En revanche, on impose de connaître le réalisateur d'un film. C'est une contrainte forte, qui d'un côté améliore la richesse et la cohérence de la base, mais de l'autre empêche toute insertion, même provisoire, d'un film dont le metteur en scène est inconnu. Ces deux situations correspondent respectivement aux associations 0..* et 1..* dans la modélisation entité/association.

Note
On peut facilement passer un attribut de not null à null. L'inverse n'est pas vrai s'il existe déjà des valeurs à null dans la base.

Que se passe-t-il quand la violation d'une contrainte d'intégrité est détectée par le système ? Par défaut, la mise à jour est rejetée, mais il est possible de demander la répercussion de cette mise à jour de manière à ce que la contrainte soit respectée. Les événements que l'on peut répercuter sont la modification ou la destruction du nuplet référencé, et on les désigne par on update et on delete respectivement. La répercussion elle-même consiste soit à mettre la clé étrangère à null (option set null), soit à appliquer la même opération aux nuplets de l'entité composante (option cascade).

Voici comment on indique que la destruction d'un pays déclenche la mise à null de la clé étrangère codePays pour tous les films de ce pays.

create table Film  (idFilm integer not null, 
                   titre    varchar (50) not null,
                   année    integer not null,
                   idRéalisateur    integer not null,
                   genre varchar (20) not null,
                   résumé      varchar(255),
                   codePays    varchar (4),
                   primary key (idFilm),
                   foreign key (idRéalisateur) references Artiste(idArtiste),
                   foreign key (codePays) references Pays(code)
                      on delete set null)

Dans le cas d'une entité faible, on décide en général de détruire le composant quand on détruit le composé. Par exemple, quand on détruit un cinéma, on veut également détruire les salles ; quand on modifie la clé d'un cinéma, on veut répercuter la modification sur ses salles (la modification d'une clé est très déconseillée, mais malgré tout autorisée). Dans ce cas c'est l'option cascade qui s'impose.

create table Salle  (idCinéma integer not null, 
                     no    integer not null,
                     capacité    integer not null,
                     primary key (idCinéma, noSalle),
                     foreign key (idCinéma) references Cinéma(idCinéma)
                       on delete cascade,
                       on update cascade)

L'attribut idCinema fait partie de la clé et ne peut donc pas être null. On ne pourrait donc pas spécifier ici on delete set null.

La spécification des actions on delete et on update simplifie la gestion de la base par la suite : on n'a plus par exemple à se soucier de détruire les salles quand on détruit un cinéma.