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 raccourcidouble
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
oumotDePasse
. - 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éenot 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 deidRé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 denot 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.