Editions ENI Livres | Vidéos | e-Formations
La définition des données (LDD)
Les types de données
Nous abordons dans cette section les types de données les plus utilisés pour la description des colonnes d’une table. Il existe trois grandes familles de données : numérique, caractère (ou alphanumérique) et temporelle (dates et heures).
Chaque SGBDR a décliné des types spécifiques pour un besoin précis comme les types géographiques ou pour des problématiques de stockage. Le type choisi dépendra donc de la précision recherchée, tout en tenant compte de la taille nécessaire pour stocker la donnée. Un bon compromis permet d’accéder rapidement à la valeur.
1. Numériques
Les types numériques permettent de définir si l’on souhaite un entier, un décimal ou un nombre à virgule flottante.
Les nombres entiers :
|
Type |
Précision |
Stockage (octets) |
BDD |
|
TINYINT |
0 à 255 |
1 |
SQL Server |
|
TINYINT |
0 à 255 ou -128 à 127 |
1 |
MySQL |
|
SMALLINT |
-32 768 à 32 768 ou 0 à 65 535 |
2 |
SQL Server, PostgreSQL |
|
SMALLINT |
-32 768 à 32 768 ou 0 à 65 535 pour MySQL |
2 |
MySQL |
|
SMALLSERIAL |
1 à 32 767. Entier à incrémentation automatique |
2 |
PostgreSQL |
|
MEDIUMINT |
-8 388 608 à 8 388 607 ou 0 à 16 777 215 |
3 |
MySQL |
|
INT ou INTEGER |
-2 147 483 648 à 2 147 483 647 ou 0 à 4 294 967 295 |
4 |
SQL Server, PostgreSQL |
|
INT(p) |
-2 147 483 648 à 2 147 483 647 ou 0 à 4 294 967 295 où p désigne le nombre de chiffres maximum |
4 |
MySQL, Oracle |
|
SERIAL |
1 à 2 147 483 647. Entier à incrémentation automatique ou 1 à 9 223 372 036 854 775 807 pour MySQL |
4 |
PostgreSQL, MySQL |
|
BIGINT |
-9 223 372 036 854 775 808 à 9 223 372 036 854 775 807 ou 0 à 18 446 744 073 709 551 615 pour MySQL |
8 |
SQL Server, PostgreSQL, MySQL |
|
BIGSERIAL |
1 à 9 223 372 036 854 775 807. Entier à incrémentation automatique |
8 |
PostgreSQL |
|
BIT |
1, 0 ou NULL |
1 |
SQL Server |
Les nombres décimaux et flottants :
|
Type |
Précision |
Stockage (octets) |
BDD |
|
DECIMAL(p[,s]) ou NUMERIC(p[,s])... |
La création de tables
Dans cette section, nous allons voir comment créer une table, ajouter ou supprimer des colonnes, mettre des commentaires sur les colonnes et les tables, mais également la méthode pour copier une table dans une autre puis comment attribuer un synonyme à un nom de table.
1. L’ordre CREATE
CREATE est l’ordre de base en langage SQL pour créer un élément. Celui-ci sert à créer une table, un index, une vue ou un synonyme. En fonction du besoin, la syntaxe est différente.
Dans cette section, nous allons traiter de la création de tables. Une table se définit principalement par les colonnes qui la composent et les règles qui s’appliquent à ces colonnes.
Nous n’aborderons pas les aspects de stockage physique de la table. En effet, chaque SGBDR a sa propre syntaxe dans ce domaine. Dans la majorité des cas, ce sont les DBA (Database Administrator, administrateur de base de données) qui spécifient les normes de stockage et les options à appliquer sur les tables. Le stockage des tables est un élément déterminant en termes de performance et de sécurité de la base de données. Il est donc fortement conseillé de se rapprocher d’un administrateur avant de se lancer dans la création d’une table.
Pour supprimer une table, on utilise l’ordre DROP TABLE.
Avant de créer une table, il est préférable de se poser quelques questions et de respecter certaines règles.
Essayez de donner des noms parlants aux tables et aux colonnes afin de retrouver ensuite facilement une information. Les DBA définissent la plupart du temps les règles de nommage des tables et des colonnes. Ils fournissent quelquefois des scripts standards pour créer une table. Il est donc impératif de consulter les normes en vigueur dans l’entreprise avant toute création.
Les règles minimums à respecter dans le nommage d’une table ne sont pas très nombreuses et se résument ainsi : un nom de table commence par une lettre, il est unique et il ne doit pas dépasser 256 caractères.
Quand une colonne a la même signification dans plusieurs tables, il est conseillé de garder le même nom. En effet, si la colonne se retrouve dans...
La suppression de tables
La suppression de tables est une opération simple mais qu’il faut manier avec prudence. La suppression est le plus souvent définitive. Il n’y aura aucune possibilité de récupérer les données de la table une fois l’ordre lancé.
1. L’ordre DROP
L’ordre DROP permet de supprimer définitivement une table. La table et son contenu sont supprimés. L’ordre DROP est aussi utilisé sur d’autres objets de base de données comme une vue ou une base.
On utilise souvent l’ordre DROP juste avant la création d’une table. Ainsi, on évite les erreurs sur la table existante.
La commande détruit automatiquement les index et contraintes posés sur cette table ainsi que les commentaires. En revanche, l’ordre ne détruit pas les synonymes.
S’il s’agit d’une table sensible, il est préférable de la sauvegarder au préalable par un CREATE ... AS SELECT … par exemple.
Attention : la table ne doit pas être en cours d’utilisation par une autre personne.
Dans le cas d’une fausse manipulation, il ne sera pas possible de récupérer la table (ROLLBACK). Certaines versions de SGBDR autorisent la récupération après un DROP (Oracle à partir de la version 10g, par exemple).
Dans MySQL et SQL Server, les ordres...
Vérifier l’existence d’un objet
Un objet est un composant de la définition de données (LDD). Cela peut donc être une table, une colonne, une vue, un index…
Lorsque l’on travaille sur la définition de données, on teste couramment l’objet sur lequel on intervient. Cela fait partie des bonnes pratiques pour éviter des erreurs. Par exemple, on vérifie qu’une table existe avant de la supprimer ou qu’elle n’existe pas avant de la créer.
Il existe plusieurs manières de tester l’existence d’un objet.
IF OBJECT_ID ... IS NULL
Syntaxe (SQL Server)
IF OBJECT_ID('nom_objet') IS NULL CREATE...;
IF OBJECT_ID('nom_objet') IS NOT NULL DROP...; Exemple
IF OBJECT_ID('Hotels') IS NULL
CREATE TABLE Hotels (idHotel INTEGER,
Libelle VARCHAR(50),
Etoile VARCHAR(5));
IF OBJECT_ID('Hotels') IS NOT NULL DROP TABLE Hotels IF EXISTS
Syntaxe (SQL Server et PostgreSQL)
DROP TABLE IF EXISTS nom_objet; Exemple
DROP TABLE IF EXISTS...La modification de table
Une fois les tables créées, elles vont évoluer dans le temps et il faudra ajouter ou supprimer une colonne ou encore intervenir sur les contraintes des colonnes.
Dans certains cas, il faudra renommer une table. C’est ce que nous allons détailler dans les sections suivantes.
1. L’ordre ALTER
L’ordre ALTER est utilisé pour réaliser plusieurs actions. Il peut être utilisé pour supprimer ou ajouter une colonne d’une table, mais également pour ajouter ou supprimer une contrainte ou ajouter une valeur par défaut à une colonne.
Attention : il est interdit de changer le nom d’une colonne ainsi que son type et ses attributs NULL ou NOT NULL.
Certains SGBDR acceptent l’ordre MODIFY et permettent ainsi de modifier le type d’une colonne.
Attention néanmoins au contenu de cette colonne. Lors du passage d’un type VARCHAR à un type INTEGER, la conversion automatique réalisée par le SGBDR va modifier le contenu des données.
Il existe un risque de perdre de l’information ou de rendre certaines données incompatibles avec leur utilisation. D’une manière générale, il est déconseillé de modifier le type d’une colonne. Pour prévenir tout problème, il faut vider la table en amont avant de changer les types.
Syntaxe
ALTER TABLE nom_de_table...Vider une table
L’ordre TRUNCATE
L’ordre TRUNCATE est utilisé pour supprimer toutes les occurrences d’une table, sans restriction. Cet ordre occupera une seule ligne dans le journal. En cas de retour arrière, toutes les occurrences seront récupérées. Il ne sera pas possible de reprendre une partie des enregistrements.
L’intérêt de l’ordre TRUNCATE est qu’il libère l’espace disponible par la suppression des lignes, dans le fichier physique de la base. Un autre avantage de cet ordre est qu’il réinitialise l’auto-incrémentation si cette option est appliquée sur une colonne.
Les déclencheurs ne sont pas exécutés.
Cet ordre est généralement employé par les administrateurs de base de données.
Syntaxe
TRUNCATE TABLE <nom table> Exemple
TRUNCATE TABLE Chambres; Les vues
Dans cette section, nous allons voir comment créer ou supprimer des vues. Les vues sont des éléments très utilisés dans la programmation SQL. Elles permettent principalement de créer des tables « virtuelles » spécifiques pour un domaine ou pour une classe d’utilisateurs, sans occuper d’espace physique supplémentaire.
1. Pourquoi utiliser des vues ?
Dans une base de données, il y a les tables permanentes qui ont été définies après une analyse des besoins et une modélisation sous forme de tables.
Si l’on respecte le modèle relationnel, il n’y a pas de données redondantes dans les tables, à l’exception des clés utilisables pour les jointures. En revanche, les utilisateurs ou les développeurs ont des besoins d’extractions spécifiques de la base. Ces extractions se matérialisent sous forme de requêtes lancées manuellement ou incluses dans les programmes.
Si ces demandes sont répétitives ou communes à plusieurs utilisateurs, il peut être nécessaire de créer une vue. La vue est une représentation logique de la base qui résulte d’une requête pour un besoin spécifique et répétitif. Contrairement à une table, elle n’est pas stockée sur disque (sauf demande spécifique) mais en mémoire.
La vue peut également permettre de simplifier pour un utilisateur la base de données. Il n’a pas besoin de connaître l’ensemble du schéma mais simplement quelques éléments spécifiques utiles dans son métier.
Si vos tables contiennent des informations confidentielles, la vue permet de masquer les colonnes en cause. Ainsi, l’utilisateur ne voit que ce que l’on veut bien lui montrer.
La création d’une vue suit le même mécanisme que le CREATE TABLE … AS SELECT … expliqué dans les sections précédentes. En effet, la vue est une agrégation de colonnes issues d’une ou plusieurs tables.
L’avantage majeur d’une vue est qu’elle est en permanence mise à jour. En effet, une vue est mise à jour automatiquement lorsque les tables qu’elle utilise sont modifiées. En revanche...
Les index
Dans cette section, nous abordons une notion importante : les index. Toutes les bases de données utilisent des index. L’implémentation physique de ceux-ci diffère d’un SGBDR à un autre.
Il existe plusieurs types d’index et plusieurs méthodes pour traiter ces index. Nous verrons comment créer et supprimer ces index et pourquoi utiliser tel type d’index en fonction des besoins que l’on a.
1. Les index et la norme SQL
Tout d’abord, il faut préciser que les index ne font pas partie de la norme SQL. En effet, l’index est utilisé pour accélérer une recherche dans une table et s’appuie sur des fichiers physiques qui sont créés lors de la création d’un index.
Il s’agit donc d’une implémentation physique et, dans la norme SQL, à l’instar du stockage des tables, l’aspect physique n’est pas traité. Chaque SGBDR l’implémente à sa façon.
En revanche, les index sont quasiment indispensables dans une base de données relationnelle. Le temps d’accès aux données étant un paramètre très important pour tous les utilisateurs et les développeurs, l’utilisation ou non d’un index peut diminuer les temps de réponse de manière exponentielle.
Dans le cas de tables avec plusieurs millions de lignes, l’accès à une même donnée peut mettre plusieurs heures sans index et quelques secondes avec un index.
Sans index, l’ensemble de la table sera parcouru séquentiellement jusqu’à trouver l’enregistrement demandé.
C’est le SGBDR qui gère les fichiers d’index, l’utilisateur ne peut pas intervenir sur la technique de stockage.
Attention néanmoins à ne pas créer des index sur toutes les colonnes. La création d’un index doit découler d’une réflexion sur l’utilisation de la table par les programmes et les utilisateurs. Les index ralentissent les traitements lors des mises à jour, car le SGBDR doit recalculer les clés après chaque ajout, suppression ou modification de lignes.
Il faut cibler les colonnes, analyser les taux de mise à jour d’une table et se baser sur...
L’intégrité des données
Les contraintes d’intégrité permettent de maintenir la base cohérente. On confie au SGBDR les tâches de contrôle de la validité des données qui sont insérées.
Les contraintes se substituent aux contrôles réalisés par programme.
Il existe plusieurs types de contrôles. Il est possible d’indiquer au SGBDR :
-
quelle est la valeur par défaut à affecter à une colonne (DEFAULT),
-
qu’une colonne ne peut pas être null (NOT NULL),
-
qu’une colonne doit être unique (UNIQUE),
-
ou de coder un contrôle sur une ou plusieurs colonnes (CHECK).
Il existe également deux contraintes particulières qui sont la clé primaire et la clé étrangère. Nous allons détailler leurs fonctions.
1. La clé primaire (PRIMARY KEY)
Par définition, la clé primaire est la clé principale d’une table. Le SGBDR va contrôler systématiquement à chaque insertion ou modification que la clé est unique dans la table. Dans le cas contraire, il rejette la demande de modification avec un message d’erreur de ce type : « Violation constraint ... ».
La clé primaire est toujours une clé unique. Elle est composée d’une ou de plusieurs colonnes en fonction de la méthode de création, le plus important étant qu’il ne peut y avoir deux lignes de la table avec cette même clé.
Il s’agit souvent d’un numéro que l’on incrémente de 1 à chaque création d’une ligne dans la table.
On peut utiliser aussi des données métier, comme des numéros de sécurité sociale ou des numéros de permis de conduire, mais il faut être certain que toutes les lignes de la table ont une valeur pour cette ou ces colonnes. En effet, une clé primaire ne peut pas prendre la valeur NULL.
La création d’une clé primaire génère dans la plupart des SGBDR la création automatique d’un index sur cette colonne.
Il y a deux méthodes pour déclarer une clé primaire. Si la clé correspond à une seule colonne, il faut utiliser cette syntaxe :
Exemple : déclaration d’une...
Exercices
Premier exercice
Créer une base nommée CINEMA.
À partir du contenu du tableau suivant, écrire la syntaxe de création de la table FILM.
Mettre un index primaire sur la colonne IDENT_FILM puis un index non unique sur la colonne GENRE1 associée à PAYS.
|
IDENT_ FILM |
TITRE |
GENRE1 |
RECETTE |
DATE_ SORTIE |
Pays |
NB_ Entree |
DATE_ SAISIE |
RÉSUMÉ |
|
1 |
SUBWAY |
POLICIER |
390 659,52 |
10/04/85 |
1 |
2 917 562 |
25/05/11 11:31 |
Conte les aventures de la population souterraine dans les couloirs du métro parisien. |
|
2 |
NIKITA |
DRAME |
5 017 971,00 |
21/02/90 |
1 |
3 787 845 |
15/04/11 09:30 |
Nikita, condamnée à la prison à perpétuité, est contrainte à travailler secrètement pour le gouvernement en tant qu’agent hautement qualifié des services secrets. |
|
3 |
STAR WARS 6 - LE RETOUR DU JEDI |
ACTION |
191 648 000,00 |
19/10/83 |
2 |
4 263 000 |
01/01/10 08:00 |
L’empire galactique est plus puissant que jamais : la construction de la nouvelle arme, l’Étoile de la Mort, menace l’univers tout entier. |
Après avoir créé la table CASTING à l’aide du script de création de la base téléchargeable sur le site des Éditions ENI, ajouter le commentaire suivant à la table CASTING : ’liste des acteurs par film avec précision du rôle et du nombre...
Solutions des exercices
Premier exercice
Requête au format standard :
Requête de création de base :
CREATE DATABASE CINEMA; Requête de création de table :
CREATE TABLE FILM (IDENT_FILM INTEGER PRIMARY KEY,
TITRE VARCHAR(50),
GENRE1 VARCHAR(20),
RECETTE DECIMAL(15,2),
DATE_SORTIE DATE,
PAYS SMALLINT,
NB_ENTREE INTEGER,
RESUME VARCHAR(2000),
DATE_SAISIE TIMESTAMP
); Description Oracle de la table :
DESC FILM
Nom NULL ? Type
------------------------------------- -------- ---------------
IDENT_FILM NOT...
