1 4 7 2 complements
Schémas relationnel ⅔ : compléments¶
Supports complémentaires
Pas de vidéo pour cette session qui présente quelques commandes utilitaires.
La clause check
¶
La clause check
exprime des contraintes portant soit sur un attribut,
soit sur un nuplet. La condition elle-même peut être toute expression
suivant la clause where
dans une requête SQL. Les contraintes les plus
courantes sont celles consistant à restreindre un attribut à un ensemble
de valeurs, comme expliqué ci-dessous. On peut trouver des contraintes
arbitrairement complexes, faisant référence à d'autres tables.
Voici un exemple simple qui restreint les valeurs possibles des
attributs année
et genre
dans la table Film.
create table Film (idFilm integer not null,
titre varchar (50) not null,
année integer
check (année between 1890 and 2020) not null,
idRéalisateur integer,
genre varchar (20) l
check (genre in ('Histoire','Western','Drame')) not null,
résumé varchar(255),
codePays varchar (4),
primary key (idFilm),
foreign key (idRéalisateur) references Artiste,
foreign key (codePays) references Pays)
Au moment d'une insertion dans la table Film, ou d'une modification
de l'attribut année
ou genre
, le SGBD vérifie que la valeur insérée
dans genre
appartient à l'ensemble énuméré défini par la clause
check
.
Une autre manière de définir, dans la base, l'ensemble des valeurs autorisées pour un attribut -- en d'autres termes, une codification imposée -- consiste à placer ces valeurs dans une table et la lier à l'attribut par une contrainte de clé étrangère. C'est ce que nous pouvons faire par exemple pour la table Pays.
create table Pays (code varchar(4) not null,
nom varchar (30) default 'Inconnu' not null,
langue varchar (30) not null,
primary key (code));
insert into Pays (code, nom, langue) values ('FR', 'France', 'Français');
insert into Pays (code, nom, langue) values ('USA', 'Etats Unis', 'Anglais');
insert into Pays (code, nom, langue) values ('IT', 'Italie', 'Italien');
insert into Pays (code, nom, langue) values ('GB', 'Royaume-Uni', 'Anglais');
insert into Pays (code, nom, langue) values ('DE', 'Allemagne', 'Allemand');
insert into Pays (code, nom, langue) values ('JP', 'Japon', 'Japonais');
Si on ne fait pas de vérification automatique, soit avec check
, soit
avec la commande foreign key
, il faut faire cette vérification dans
l'application, ce qui est plus lourd à gérer.
Modification du schéma¶
La création d'un schéma n'est qu'une première étape dans la vie d'une base de données. On est toujours amené par la suite à créer de nouvelles tables, à ajouter des attributs ou à en modifier la définition. La forme générale de la commande permettant de modifier une table est :
alter table <nomTable> <action> <description>
où action
peut être principalement add
, modify
, drop
ou rename
et description
est la commande de modification associée à action
. La
modification d'une table peut poser des problèmes si elle est
incompatible avec le contenu existant. Par exemple passer un attribut à
not null
implique que cet attribut a déjà des valeurs pour tous les
nuplets de la table.
Modification des attributs¶
Voici quelques exemples d'ajout et de modification d'attributs. On
peut ajouter un attribut region
à la table Internaute avec la
commande :
alter table Internaute add région varchar(10)
S'il existe déjà des données dans la table, la valeur sera à null
ou
à la valeur par défaut. La taille de région
étant certainement
insuffisante, on peut l'agrandir avec modify
, et la déclarer
not null
par la même occasion :
alter table Internaute modify région varchar(30) not null
Il est également possible de diminuer la taille d'un attribut, avec le
risque d'une perte d'information pour les données existantes. On peut
même changer son type, pour passer par exemple de varchar
à integer
,
avec un résultat imprévisible.
La commande alter table
permet d'ajouter une valeur par défaut.
alter table Internaute add région set default 'Corse'
Enfin, on peut détruire un attribut avec drop
.
alter table Internaute drop région
De plus, chaque système propose des commandes non normalisées. MySQL par
exemple dispose d'une commande truncate
pour "vider" une table
rapidement, sans effectuer de contrôle (!) À vous d'éplucher la
documentation pour ces aspects spécifiques.
Création d'index¶
Pour compléter le schéma d'une table, on peut définir des index. Un index offre un chemin d'accès aux nuplets d'une table qui est considérablement plus rapide que le balayage de cette table -- du moins quand le nombre de nuplets est très élevé. Les SGBD créent systématiquement un index sur la clé primaire de chaque table. Il y a plusieurs raisons à cela ;
- l'index permet de vérifier rapidement, au moment d'une insertion, que la clé n'existe pas déjà ;
- l'index permet également de vérifier rapidement la contrainte d'intégrité référentielle : la valeur d'une clé étrangère doit toujours être la valeur d'une clé primaire.
- beaucoup de requêtes SQL, notamment celles qui impliquent plusieurs tables (jointure), se basent sur les clés des tables pour reconstruire les liens. L'index peut alors être utilisé pour améliorer les temps de réponse.
Un index est également créé pour chaque clause unique
utilisée dans la
création de la table. On peut de plus créer d'autres index, sur un ou
plusieurs attributs, si l'application utilise des critères de recherche
autres que les clés primaire ou secondaires.
La commande pour créer un index est la suivante :
create [unique] index <nomIndex> on <nomTable> (<attribut1> [, ...])
L'option unique
indique qu'on ne peut pas trouver deux fois la même
clé dans l'index. La commande ci-dessous crée un index de nom idxNom
sur les attributs nom
et prénom
de la table Artiste. Cet index a
donc une fonction équivalente à la clause unique
déjà utilisée dans la
création de la table.
create unique index idxNom on Artiste (nom, prénom)
On peut créer un index, cette fois non unique, sur l'attribut genre
de la table Film.
create index idxGenre on Film (genre)
Cet index permettra d'exécuter très rapidement des requêtes SQL ayant comme critère de recherche le genre d'un film.
select * from Film where genre = 'Western'
Cela dit il ne faut pas créer des index à tort et à travers, car ils ont un impact négatif sur les commandes d'insertion et de destruction. À chaque fois, il faut en effet mettre à jour tous les index portant sur la table, ce qui représente un coût certain.
Pour en savoir plus sur les index, et en général sur la gestion de l'organisation des données, je vous renvoie à la seconde partie du cours disponible à http://sys.bdpedia.fr.