vendredi 6 janvier 2012

La description des données : les types de données SQL - Création base de données et tables SQL SGBD

1 - Introduction

2 - Présentation du modèle du cours

3 - Les types de données

3.1- Les types numériques exacts
3.2- Les types numériques non exacts
3.3- Les types chaîne de caractère
3.4- Les types bits
3.5- Les types temporels
3.6- Les conversions de type

4 - La création des tables

4.1- Création de la base de donnée
4.2- Création des tables
4.3- Assurer la cohérence père - fils

5 - Les commandes d'effacement

5.1- Effacement de table
5.2- Effacement de base

6 - La modification des tables

6.1- Modification sur la table
6.2- Modification sur les champs
6.3- Modification sur les contraintes



1 Introduction

Les cours précédents nous ont montré les moyens d'accéder aux données d'une base existante, et de mise à jour de ces données. Il nous reste à voir un élément important, à savoir la création et la modification de la structure de la base de donnée. Cette création fait également appel à des commandes SQL regroupées sous l'acronyme de LDD(Langage de Définition des Données).

La définition des données va permettre de définir des bases de données, des tables, des contraintes et des index, d'en modifier ou supprimer tout ou partie.

Cette partie s'appuie sur le modèle relationnel. Tous les attributs, et plus particulièrement leur type (entier, chaîne…) doivent être définis.

2 Présentation du modèle du cours

Pour les exemples de ce cours, nous nous appuierons sur la base de données RESTO vue lors de l'évaluation. Cette base est plus riche en terme d'organisation et permet de voir les différents cas de figure.

Modèle Conceptuel


Modèle relationnel


3 Les types de données

Les types de données sont directement dépendant des bases utilisées. Certains termes ont également évolués avec le temps (NUMERIC => NUMBER), néanmoins on peut retenir les types les plus standards à savoir :


3.1 Les types numériques exacts

o INTEGER (ou INT) : Type entier, la taille (8, 16 ou 32 bis) dépendant de l'implémentation
o SMALLINT : Valeur entière de taille inférieure à INTEGER
o NUMERIC (p, s) : Définit un nombre non entier. p définit le nombre de caractères, s le nombre de chiffres après la virgule.
o DECIMAL (p,s) : Idem
o NUMBER (t,d) : Idem NUMERIC


3.2 Les types numériques non exacts

o REAL : Définit un type réel, la précision dépendant de l'implémentation
o DOUBLE PRECISION : Idem précédemment mais avec une précision double.
o FLOAT (p) : Idem avec un paramètre spécifiant la précision.


3.3 Les types chaîne de caractère

o CHAR (x) : Définit une chaîne de x caractère
o VARCHAR (x) : Idem mais la longueur stockée dans la base est variable et limitée à x. Cela permet d'optimiser la taille de la base.
o NCHAR ou NVARCHAR : Idem précédemment mais les caractères sont codés en unicode.


3.4 Les types bits :

Ce type de données permet de stocker des chaînes de bits ne répondant pas à un codage standard (image, son numérisé…). La taille peut all à plusieurs giga-bits

o BIT(x)
o BIT VARYING(x) : Idem mais stockage variable en fonction de la taille.


3.5 Les types temporels
Les types temporels représentent tout ce qui a trait aux dates et heures. Attention, le format de stockage diffère en fonction des SGBD, et plus particulièrement en ce qui concerne l'ordre.

o DATE : Type date : 2 caractères pour le jour, 2 pour le mois, 4 pour l'année
o TIME (x) : Type heure : 2 caractères pour l'heure (0 => 23), 2 pour les minutes
(0 => 59) et 2 pour les secondes. Le paramètre x permet de définir le nombre
de chiffres après la virgule pour les secondes.
o TIMESTAMP (x) : Type complet date et heure. Par défaut, x = 6.


3.6 Les conversions de type
Comme la plupart des langages, SQL effectue la conversion de certain type de façon automatique (addition d'un entier et d'un réel). D'autres conversions sont obtenues à l'aide de fonctions. Ce point sera vu ultérieurement.

4 La création des tables

Une table est contenue dans une base de données. L'opération initiale est donc la création de la base de données. Dans tous les cas, une création ne peut se faire que si l'objet est inexistant.


4.1 Création de la base de données

Cette opération s'effectue à l'aide de la commande SQL CREATE. La syntaxe est la suivante :

CREATE DATABASE
[ [ W ITH ] [ OW NER [=]
[ TEMPLATE [=] ]
[ ENCODING [=] ]
[ TABLESPACE [=] tablespace ] ];

Les différents paramètres, excepté le nom de la base sont optionnels et peuvent être définis au sein de la base.

  • OWNER est l’utilisateur propriétaire de la base (super utilisateur), par défaut, c’est celui qui crée la base.
  • TEMPLATE est le nom d’une base de données existante dont on reprend les paramètres.
  • ENCODING permet de définir l’encodage des caractères pour la base.
  • TABLESPACE : Le tablespace permet à l’administrateur de la base de données d’indiquer l’endroit dans le système de fichier dans lequel sont stockés les fichiers représentant la base de données. Un tablespace est représenté par un nom et peut être utilisé pour plusieurs bases.

Exemple : Création de la base resto :

CREATE DATABASE RESTO
W ITH OW NER = postgres
ENCODING = 'SQL_ASCII'
TABLESPACE = pg_default;
GRANT ALL ON DATABASE test TO publi

La commande GRANT permet d'allouer des droits sur la base.


4.2 Création des tables

Cette opération s'effectue à l'aide de la commande SQL CREATE. La syntaxe simplifiée est la suivante :

CREATE TABLE [nom de base.] nom de table
(colonne1 type1 [default valeur1] [NOT NULL] ,
colonne2 type2 [default valeur] [NOT NULL] ,
….
[CONSTRAINT nom contrainte1 type contrainte1] …
[ON DELETE action delete] [ON UPDATE actions update]
);


  • nom de base : Le nom de la base est optionnel. Dans ce cas, la table sera créée au niveau de la base courante.
  • nom de table : Attention, les règles de nommage s'appliquent (pas de caractère particulier, pas d'accent …).
  • colonne : Spécifie le nom de la colonne (champ) à créer
  • type se réfère aux types de données existant dans la base (voir paragraphe sur les types de données)
  • CONSTRAINT est un mot clé listant les contraintes à appliquer sur la table. Ces contraintes peuvent être :
    • PRIMARY KEY : déclaration d'une clé primaire (un index sur cette clé est automatiquement créé.
    • FOREIGN KEY : déclaration d'une clé étrangère. Dans ce cas, le mot clé REFERENCES sera utilisé pour dire à quel champ de quelle table cette clé est référencée. Cette contrainte peut être accompagnée d'une directive ON DELETE, ON UPDATE afin de respecter l'intégrité référentielle en cas d'effacement ou de mise à jour de clé primaire. Il faut définir alors l'opération à effectuer.
    • CHECK permet de définir un domaine de valeur autorisé
    • UNIQUE impose une valeur unique pour une colonne au niveau de la table


Concernant les contraintes, il est conseillé de leur donner un nom explicite qui reprend le nom de la table afin de pouvoir les modifier par la suite ou les identifier en cas de message d'erreur.

Du fait de l'intégrité référentielle, les tables doivent être créées dans l’ordre à savoir :

o Les tables ne disposant que de clé primaire en premier
o Les tables avec clé étrangère ensuite.
Si ces points ne sont pas respectés, une erreur sera générée au niveau de la base et la table correspondante ne sera pas créée.

Afin de voir la syntaxe de création de table, nous allons créer l'ensemble des tables de la base resto.

Création de la table Menu

CREATE TABLE MENU
(
Id_Menu integer NOT NULL,
entree varchar(50),
plat varchar(50),
dessert varchar(50),
CONSTRAINT menu_pkey PRIMARY KEY (Id_Menu)
);

Autre solution sans nommer la contrainte de clé primaire :

CREATE TABLE MENU
(Id_Menu Integer NOT NULL PRIMARY KEY,
Entree Varchar(50),
Plat Varchar(50),
Dessert Varchar(50)
);


Création de la table RESTO avec une contrainte de domaine sur les étoiles :
CREATE TABLE resto
(
Id_resto integer NOT NULL,
Nomresto char(50),
Adresse varchar(100),
Telephone varchar(15),
NomChef varchar(50),
NbEtoile integer,
CONSTRAINT resto_pkey PRIMARY KEY (Id_resto),
CONSTRAINT Resto_nbetoile CHECK (nbetoile IN (0,1,2,3))
);

la syntaxe sur nbetoile pourrait aussi être :
o <=3
o BETW EEN 0 AND 3


Création de la table BOISSON:

CREATE TABLE BOISSON
(Id_Boisson Integer,
NomBoisson Varchar(50),
Temperature NUMERIC (4,2),
DegreeAlcool NUMERIC (2,1),
CONSTRAINT Boisson_pkey PRIMARY KEY (Id_Boisson)
);


Création de la table CONTENANT avec nom de contenant unique:

CREATE TABLE CONTENANT
(Id_Contenant Integer,
NomContenant Varchar(50),

CONSTRAINT Contenant_pkey PRIMARY KEY (Id_Contenant),
CONSTRAINT contenant_nom UNIQUE (NomContenant)
);

Création de la table MENURESTO

CREATE TABLE MENURESTO
(Id_Resto Integer,
Id_Menu Integer,
Prix Numeric(6,2),

CONSTRAINT MenuResto_pkey PRIMARY KEY (Id_Resto, Id_Menu),
CONSTRAINT MenuResto_Idresto_Fkey FOREIGN KEY (Id_Resto)
REFERENCES RESTO (Id_Resto),
CONSTRAINT MenuResto_IDmenu_Fkey FOREIGN KEY (Id_Menu)
REFERENCES MENU (Id_Menu)
);


Création de la table BOISSONSERVI

CREATE TABLE BOISSONSERVI
(Id_Resto Integer,
Id_Boisson Integer,
Id_Contenant Integer,
Prix Numeric(6,2),
CONSTRAINT MenuBoisson_pkey PRIMARY KEY (Id_Resto, Id_Boisson, Id_Contenant),
CONSTRAINT MenuBoisson_IdResto_Fkey FOREIGN KEY (Id_Resto)
REFERENCES RESTO (Id_Resto),
CONSTRAINT MenuResto_IDBoisson_Fkey FOREIGN KEY (Id_Boisson)
REFERENCES BOISSON (Id_Boisson),
CONSTRAINT MenuResto_IDContenant_Fkey FOREIGN KEY
(Id_Contenant) REFERENCES CONTENANT (Id_Contenant)
);

Autre écriture sans nommer toutes les contraintes :

CREATE TABLE BOISSONSERVI
(Id_Resto Integer REFERENCES RESTO (Id_Resto),
Id_Boisson Integer REFERENCES BOISSON (Id_BOISSON),
Id_Contenant Integer REFERENCES CONTENANT (Id_Contenant),
Prix Numeric(6,2),
CONSTRAINT MenuBoisson_pkey PRIMARY KEY (Id_Resto,
Id_Boisson,Id_Contenant)
);


4.3 Assurer la cohérence père - fils

Les instructions ON DELETE et ON UPDATE ont pour rôle d'assurer la cohérence des données et surtout de mettre en place des mécanismes permettant de garder cette cohérence.

Par exemple, on veut pouvoir effacer un resto, mais dans ce cas pour assurer l'intégrité référentielle, il est indispensable d'effacer les enregistrements correspondant à cet Idresto dans les tables MENURESTO et BOISSON SERVI
  • Par défaut, le ON DELETE est restrictif, c'est à dire qu'il va empêcher l'effacement du père (idresto).
  • ON DELETE CASCADE propage la suppression des enregistrements du père vers le fils
  • ON DELETE SET NULL propage l'affectation de la valeur nulle aux clés étrangères.

Attention, dans le cas ou une clause NOT NULL est sur la clé étrangère, cela ne sera pas possible, ce qui est le cas de notre exemple.

Attention, les expressions ON DELETE et ON UPDATE se positionnent au niveau des clés étrangères. Le mécanisme est identique pour la mise à jour.

Les actions possibles sont :

o NO ACTION
o CASCADE
o SET NULL
o SET DEFAULT

CREATE TABLE BOISSONSERVI
(
id_resto INTEGER NOT NULL,
id_boisson INTEGER NOT NULL,
id_contenant INTEGER NOT NULL,
prix NUMERIC (6,2),
CONSTRAINT menuboisson_pkey PRIMARY KEY (id_resto, id_boisson, id_contenant),
CONSTRAINT boissonservi_id_boisson_fkey FOREIGN KEY (id_boisson)
REFERENCES boisson (id_boisson) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT boissonservi_id_contenant_fkey FOREIGN KEY
(id_contenant)
REFERENCES contenant (id_contenant) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
);

5 Les commandes d'effacement


5.1 Effacement de table

La commande d'effacement est une instruction SQL dont la syntaxe est :

DROP TABLE nom de la table [CASCADE]

Un effacement de table consiste à effacer tous les éléments concernant la table à savoir
o Les données
o La structure
o Les contraintes attachées (index).

Un effacement de table est soumis à la contrainte l'intégrité référentielle. Le mot clé CASCADE, supprime cette contrainte avec toutes les conséquences que cela comporte.

5.2 Effacement de base

De la même manière, il est possible d'effacer une base de données. La syntaxe est :

DROP DATABASE nom de la base

Cette commande ne peut se faire si l'on est connecté à la base.

6 La modification des tables


La modification consiste à tout ce qui peut être changé dans la structure d'une table :

o Ajout ou suppression d'un champ
o Modification d'un type de champs
o Ajout ou suppression d'une clé primaire ou étrangère
o Ajout ou suppression d'une contrainte de domaine
o Changement du nom d'un champ
o

Ces modifications sont d'autant plus importantes que l'on peut être amené à les effectuer sans détruire les données existantes. Il faudra donc être vigilant afin de vérifier si la modification peut effectivement être réalisée que vu des données contenues dans la table.

La syntaxe de la commande est :

ALTER TABLE action à réaliser

L'action à réaliser peut être :

o ADD [ COLUMN ]
o DROP [ COLUMN ]
o ALTER [ COLUMN ]
o ADD
o DROP CONSTRAINT [ RESTRICT | CASCADE ]
o



6.1 Modification sur la table

Modification du nom d'une table :

ALTER TABLE RESTO
RENAME TO RESTAURANT;


6.2 Modification sur les champs
Ajout du champs e-mail dans la Table RESTO
ALTER TABLE RESTO
ADD E_mail VARCHAR(30) DEFAULT ';

Suite au ADD, plusieurs champs peuvent être rajoutés :

ADD (E-mail VARCHAR(30), Directeur VARCHAR(30))

Suppression d'un champ :

ALTER TABLE RESTO
DROP E_mail

Modification de la taille ou du type d'un champ

ALTER TABLE RESTO
MODIFY E_mail VARCHAR(50);

Attention, la syntaxe MODUIFY ne fonctionne pas sur toutes les tables.

6.3 Modification sur les contraintes

Suppression d'une contrainte

ALTER TABLE RESTO

DROP CONSTRAINT resto_nbetoil (suppression de la contrainte sur le nb d'étoiles)

Ajout d'une contrainte

ALTER TABLE RESTO
ADD CONSTRAINT resto_nbetoile CHECK (nbetoile IN (0,1,2,3));

Suppression d'une clé primaire

ALTER TABLE RESTO
DROP CONSTRAINT resto_Pkey CASCADE;

De la même façon, du fait de l'intégrité référentielle, la suppression de la clé primaire n'est pas possible. Le mot clé CASCADE force cette suppression avec tous les risques que cela comporte.

Attention, la suppression d'une contrainte ne supprime pas la colonne correspondante. Ici, le champ correspondant à la clé primaire est conservé.

ajout d'une clé primaire :

ALTER TABLE RESTO
ADD CONSTRAINT resto_pkey PRIMARY KEY (Id_resto);

Ce travail sur les clés peut être nécessaire dans certain cas : Imaginez que vous vouliez agrandir un champ qui est clé primaire et clé étrangère dans une autre table. Les opérations à réaliser dans l'ordre seront :


o Suppression de la contrainte de clé étrangère (avec l'option CASCADE)
o Suppression de la contrainte de clé primaire (avec l'option CASCADE)
o modification des tailles de champs
o rétablissement des contraintes

0 commentaires:

Enregistrer un commentaire

Share

Twitter Delicious Facebook Digg Stumbleupon Favorites

 

IP