Chapitre1 : LE LANGAGE DE DEFINITION DE DONNEES SQL

(LDD)

Introduction
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 logique comprend essentiellement deux parties : d’une part la description
des tables et de leur contenu, d’autre part les contraintes qui portent sur les données de la base. 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 physique.
Il existe plusieurs versions de SQL. Le plus ancien standard date de 1989. Il a été révisé de manière
importante en 1992 : la norme résultant de cette révision est SQL-92 ou SQL2. Une extension (SQL3)
comprenant l’introduction de caractéristiques orientées-objet est en cours de discussion depuis très
longtemps, et certains systèmes ont déjà anticipé en proposant de nouvelles fonctionnalités. Le matériel
présenté dans ce cours est essentiellement SQL2, sauf pour quelques nouveautés explicitement
indiquées.

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

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 en général, mais la taille
du stockage n’est pas spécifiée par la norme. Il existe deux variantes du type
INTEGER : SMALLINT et BIGINT. Ces types différents par la taille utilisée pour le
stockage :

Le type DECIMAL (M, D) correspond à un numérique de taille maximale M, avec un

nombre de décimales fixé à D. Le type NUMERIC est un synonyme pour 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.
1. Le type FLOAT correspond aux flottants en simple précision.
2. Le type DOUBLE PRECISION correspond aux flottants en double précision. Le
raccourci DOUBLE est accepté.
3. Le type REAL est un synonyme pour DOUBLE.
Caractères et chaînes de caractères
Les deux types principaux de la norme ANSI, disponibles dans la plupart des SGBD
relationnels, 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 pour le second VARCHAR(M).
La différence essentielle entre les deux types 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 très longues (des textes, voire des
livres), 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 BLOB (pour Binary Long Object) ou
LONG.
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 stocke les informations heure, minute et seconde. L’affichage
se fait par défaut au format HH:MM:SS. Le type DATETIME permet de combiner une
date et un horaire, l’affichage se faisant au format AAAA-MM-JJ HH:MM:SS.
2- Création des tables
La commande principale est CREATE TABLE. Voici la commande de création de la
table Internaute.
CREATE TABLE Internaute (email VARCHAR (50) NOT NULL,
nom VARCHAR (20) NOT NULL, prenom VARCHAR (20), motDePasse VARCHAR
(60) NOT NULL, anneeNaiss DECIMAL (4))
La syntaxe se comprend aisément. La seule difficulté est de choisir correctement le
type de chaque attribut. 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. Quand on parle
de valeur NULL en SQL2, il s’agit en fait d’une absence de valeur. En conséquence :

1. on ne peut pas faire d’opération incluant un NULL ;

2. on ne peut pas faire de comparaison avec un NULL.
L’option NOT NULL oblige à toujours indiquer une valeur. 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 une ligne dans Internaute sans
donner de mot de passe. Si les valeurs à NULL sont autorisées, il faudra en tenir
compte quand on interroge la base. Cela peut compliquer les choses, voire donner
des résultats surprenants : il est préférable de forcer les attributs importants à avoir
une valeur. 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 (nom VARCHAR (50) NOT NULL, adresse VARCHAR (50)
DEFAULT ’Inconnue’)
Quand on insérera une ligne dans la table Cinéma sans indiquer d’adresse, le système
affectera automatiquement la valeur ’Inconnu’ à 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 qui peut indiquer la date du jour).
3- Contraintes
La création d’une table telle qu’on l’a vue précédemment est extrêmement 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 :
1. Un attribut doit toujours avoir une valeur. C’est la contrainte NOT NULL vue
précédemment.
2. Un attribut (ou un ensemble d’attributs) constitue(nt) la clé de la relation.
3. Un attribut dans une table est lié à la clé primaire d’une autre table (intégrité
référentielle).
4. La valeur d’un attribut doit être unique au sein de la relation.
5. Enfin toute règle s’appliquant à la valeur d’un attribut (min et max par exemple).
Les contraintes sur les clés doivent être systématiquement spécifiées. La dernière
(clause CHECK) s’appuie en grande partie sur la connaissance du langage
d’interrogation de SQL et sera vue ultérieurement.
1- Clés d’une table
Une clé est un attribut (ou un ensemble d’attributs) qui identifie(nt) de manière unique
un tuple d’une relation. Il peut y avoir plusieurs clés 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 une ligne et une seule à partir d’autres tables. Il est donc assez 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 Internaute (email VARCHAR (50) NOT NULL, nom VARCHAR (20)
NOT NULL, prenom VARCHAR (20), motDePasse VARCHAR (60) NOT NULL,
anneeNaiss INTEGER, PRIMARY KEY (email). Il devrait toujours y avoir une
PRIMARY KEY dans une table pour ne pas risquer d’insérer involontairement deux
lignes strictement identiques. Une clé peut être constituée de plusieurs attributs :
CREATE TABLE Notation (idFilm INTEGER NOT NULL, email VARCHAR (50) NOT
NULL, note INTEGER DEFAULT 0, PRIMARY KEY (titre, email))

Tous les attributs figurant dans une clé doivent être déclarés NOT NULL. Cela n’a pas
vraiment de sens en effet d’identifier des lignes par des valeurs absentes. On peut
également spécifier que la valeur d’un attribut est unique pour l’ensemble de la
colonne. Cela permet d’indiquer des clés secondaires. On peut par exemple indiquer
que deux artistes ne peuvent avoir les mêmes nom et prénom avec l’option UNIQUE.
CREATE TABLE Artiste(id INTEGER NOT NULL, nom VARCHAR (30) NOT NULL,
prenom VARCHAR (30) NOT NULL, anneeNaiss INTEGER, PRIMARY KEY (id),
UNIQUE (nom, prenom));
Il est facile de supprimer cette contrainte de clé secondaire par la suite. Ce serait
beaucoup plus difficile si on avait utilisé la paire (nom, prenom) comme clé primaire
puisqu’elle serait alors utilisée pour référencer un artiste dans d’autres tables. Voici
un autre exemple d’utilisation d’une clé secondaire : on indique ci-dessous qu’on ne
peut pas trouver deux cinémas à la même adresse. Ce deuxième exemple montre
que l’on peut placer une contrainte comme UNIQUE sur la ligne de l’attribut auquel
elle se rapporte. Ce n’est bien entendu possible que quand cette contrainte ne
concerne qu’un seul attribut.
CREATE TABLE Cinema
VARCHAR (30) NOT
(nom
NULL,VARCHAR(50)
adresse
UNIQUE,
PRIMARY KEY (nomCinema))
La clause UNIQUE ne s’applique pas aux valeurs NULL : il peut y avoir plusieurs
cinémas d’adresse inconnue. En revanche le nom du cinéma est obligatoire (clause
NOT NULL) et il est unique (clause PRIMARY KEY).
Clés étrangères
La norme SQL ANSI permet d’indiquer quelles sont les clés étrangères dans une
table, autrement dit, quels sont les attributs qui font référence à une ligne 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, annee INTEGER NOT NULL, idMES INTEGER,
codePays INTEGER, PRIMARY KEY (idFilm), FOREIGN KEY (idMES)
REFERENCES Artiste, FOREIGN KEY (codePays) REFERENCES Pays);

La commande
FOREIGN KEY (idMES) REFERENCES Artiste indique que idMES 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 idMES correspond bien à une
ligne de Artiste. Ces modifications sont :
1. l’insertion dans Film avec une valeur inconnue pour idMES ;
2. la destruction d’un artiste ;
3. la modification de id dans Artiste ou de idMES dans Film.
En d’autres termes le lien entre Film et Artiste est toujours valide. Cette contrainte est
importante pour garantir 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 idMES n’est pas déclaré NOT NULL, ce qui signifie que l’on
s’autorise à ne pas connaître le metteur en scène d’un film. Quand un attribut est à
NULL, la contrainte d’intégrité référentielle ne s’applique pas.
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 de la
ligne référencée, 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 lignes de l’entité composante (option
CASCADE).
Voici comment on indique que la destruction d’un metteur en scène déclenche la mise
à NULL de la clé étrangère idMES pour tous les films qu’il a réalisés.
CREATE TABLE Film (titre VARCHAR (50) NOT NULL,
annee INTEGER NOT NULL, idMES INTEGER, codePays INTEGER, PRIMARY KEY
(titre),
FOREIGN KEY (idMES) REFERENCES Artiste ON DELETE SET NULL,
FOREIGN KEY (codePays) REFERENCES Pays);
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.
CREATE TABLE Salle (nomCinema VARCHAR (30) NOT NULL,
no INTEGER NOT NULL,
capacite INTEGER, PRIMAR KEY (nomCinema, no), FOREIGN KEY (nomCinema)
REFERENCES Cinema
ON DELETE CASCADE ON UPDATE CASCADE )
Il est important de noter que nomCinema 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 considérablement
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.

2- Énumération des valeurs possibles avec CHECK

La norme SQL comprend une option CHECK (condition) pour exprimer des
contraintes portant soit sur un attribut, soit sur une ligne. 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 relations. Nous reviendrons sur
cet aspect après avoir étudié le langage d’interrogation SQL.
Voici un exemple simple qui restreint les valeurs possibles des attributs annee et
genre dans la table Film.
CREATE TABLE Film (titre VARCHAR (50) NOT NULL,
annee INTEGER
CHECK (annee BETWEEN 1890 AND 2000) NOT NULL, genre VARCHAR (10)
CHECK (genre IN (’Histoire’,’Western’,’Drame’)), idMES INTEGER,
codePays INTEGER, PRIMARY KEY (titre), FOREIGN KEY (idMES) REFERENCES
Artiste, FOREIGN KEY (codePays) REFERENCES Pays);
Au moment d’une insertion dans la table Film, ou d’une modification de l’attribut annee
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) DEFAULT 0 NOT NULL,
nom VARCHAR (30) NOT NULL, langue VARCHAR (30) NOT NULL, PRIMARY KEY
(code))
INSERT INTO Pays VALUES (0, ’Inconnu’, ’Inconnue’);
INSERT INTO Pays VALUES (1, ’France’, ’Français’);
INSERT INTO Pays VALUES (2, ’USA’, ’Anglais’);
INSERT INTO Pays VALUES (3, ’Allemagne’, ’Allemand’);
INSERT INTO Pays VALUES (4, ’Angleterre’, ’Anglais’);
...
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.
4- 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 toutes les lignes de la table.

I) 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 region 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 region étant certainement insuffisante, on peut l’agrandir avec
MODIFY, et la déclarer NOT NULL par la même occasion :
ALTER TABLE Internaute MODIFY region VARCHAR(30) NOT NULL;
Il est également possible de diminuer la taille d’une colonne, 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.
L’option ALTER TABLE permet d’ajouter une valeur par défaut.
ALTER TABLE Internaute ALTER region SET DEFAULT ’PACA’; Enfin on peut
détruire un attribut avec DROP.
ALTER TABLE Internaute DROP region;
II) 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 lignes d’une table qui est considérablement plus rapide que le
balayage de cette table – du moins quand le nombre de lignes est très élevé. Les
SGBDL créent systématiquement un index sur la clé primaire de chaque table. Il y a
deux raisons à cela ;
1. l’index permet de vérifier rapidement, au moment d’une insertion, que la clé n’existe
pas déjà ;
2. beaucoup de requêtes SQL, notamment celles qui impliquent plusieurs tables
(jointures), 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 [, ...])
La clause UNIQUE indique qu’on ne peut pas trouver deux fois la même clé. La
commande ci-dessous crée un index de nom idxNom sur les attributs nom et prenom
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, prenom);
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.