create table

Initiation aux bases de données et
à la programmation événementielle
Cours N°4 :
Langage de Définition de Données.
Souheib BAARIR.
Page web :
http://pagesperso-systeme.lip6.fr/Souheib.Baarir/bdvba/support.htm
E-mail :
[email protected]
Université Paris Ouest Nanterre la Défense.
1
Tables Vs. Schémas des tables
• Une base de données comporte des Tables (relations),
dont les formats sont décrits par les Schémas des
tables…
• La plupart des SGBD permettent de créer ces schémas
de manière interactive à l’aide de fenêtres de dialogue et
d’assistants (Access, Query,…).
• Cependant, SQL prévoit des instructions particulières
pour gérer les schémas…C’est ce qu’on appel le
Langage de Définition de Données (LDD).
2
LDD en SQL
•Plusieurs types de requêtes :
 d’interrogation (LID).
 de manipulation (LMD).
 de définition (LDD).
• CREATE
TABLE… : créer une table.
• ALTER TABLE… : modifier une table.
• DROP TABLE…
: supprimer une table.
3
Création de table :
syntaxe générale (simplifiée)
La création d’une table est prévue en SQL via l’instruction CREATE TABLE.
CREATE TABLE table (
champ1 type [(taille)] [NOT NULL]
[,champ2 type [(taille)] [NOT NULL]]*
[,CONSTRAINT nom PRIMARY KEY (champi [, champj [, …]]) |
UNIQUE
(champk [, champl [, …]]) |
FOREIGN KEY (champm [, champn [, …]])
REFERENCES tableE [(champE1 [,champE2 [, …]])]]*
)
4
Création de table :
types de données
• Un type de données définit le genre de contenu d'un champ.
 les opérations pouvant être effectuées sur ce champ.
• Trois catégories de types :
1. Les valeurs numériques.
 Bit,YesNo,…
 Numeric, Short, Int, Real, Double,…
 Autoincrement,…
2. Les dates et les heures.
 Date.
 Time.
3. Les chaînes de caractères.
 Text, Memo,…
 Char,…
5
Création de table :
Premier exemple
CREATE TABLE article (
[code article]
Autoincrement NOT NULL,
[prix article]
INT,
[date fabrication] DATE,
CONSTRAINT a1 PRIMARY KEY ([code article])
)
6
Création de table :
Intégrité référentielle
La clause FOREIGN KEY permet, pour les valeurs du champ indiqué, de faire
référence à des valeurs existantes dans un champ d'une autre table. Ce
mécanisme s'appelle intégrité référentielle.
CREATE TABLE article (
[code article]
Autoincrement NOT NULL,
[prix article]
INT,
[date fabrication] DATE,
[référence fournisseur] INT NOT NULL,
Une valeur insérée dans
le champ
[référence fournisseur]
de la table article doit
obligatoirement exister
comme clé primaire de
la table fournisseur
(le champ [N° Siret])
CONSTRAINT a1 PRIMARY KEY ([code article]),
CONSTRAINT a2 FOREIGN KEY ([référence fournisseur])
REFERENCES fournisseur ([N° Siret])
)
7
Modification de table :
syntaxe générale (simplifié)
SQL (de ACCESS) permet de modifier le schéma d’une table par l’instruction
ALTER TABLE, suivie de la spécification de l’opération à effectuer.
ALTER TABLE table
ADD COLUMN champ type[(taille)] [NOT NULL] |
ALTER COLUMN champ type[(size)] |
ADD CONSTRAINT … |
DROP COLUMN champ |
DROP CONSTRAINT nom
8
Modification de table :
Premier exemple
ALTER TABLE article ALTER COLUMN [prix article] CHAR(25)
9
Suppression de table :
syntaxe générale (simplifiée)
La suppression d’une table se fait par DROP TABLE :
DROP TABLE table
DROP TABLE article 
Supprime la table article de notre base de données.
10
Exercice :
BD Articles-Fournisseurs
Un vendeur veut gérer, de façon automatique, les articles qu’il vend. Sachant que :
• un fournisseur est décrit par : un numéro de Siret, une description, et une adresse,
• un article est décrit par : un code alphanumérique, une description, un prix d’achat et
un prix de vente,
• un article à seul fournisseur et un fournisseur peut fournir plusieurs articles.
Comment construire la base de données correspondante à son besoin ?
Pour décrire les fournisseurs
 Une table Fournisseurs.
Pour décrire les articles
 Une table Articles.
Pour décrire le lien entre un article et un fournisseur  Clé primaire/clé externe
11
La table Fournisseurs
Un fournisseur est décrit par : un numéro de Siret, une description, et une adresse.
CREATE TABLE Fournisseurs (
[N° Siret]
INT NOT NULL,
[Description]
Text(30),
[Adresse]
Text(50),
CONSTRAINT F1 PRIMARY KEY ([N° Siret])
)
12
La table Articles
Un article est décrit par : un code alphanumérique, une description,
un prix d’achat et un prix de vente.
CREATE TABLE Articles (
[Code Article]
Text(40) NOT NULL,
[Description]
Text(30),
[Prix achat]
Real,
[Prix vente]
Real,
CONSTRAINT A1 PRIMARY KEY ([Code Article])
)
13
Insertion du lien :
Modification de la table Articles
Un article à un seul fournisseur et un fournisseur peut fournir plusieurs articles.
ALTER TABLE Articles ADD COLUMN [Réf fournisseur] INT
ALTER TABLE Articles ADD CONSTRAINT A2 FOREIGN KEY
([Réf fournisseur]) REFERENCES Fournisseurs([N° Siret])
14
Exemple de violation
d’ intégrité référentielle
15
La table Articles complète, dés la création
CREATE TABLE Articles (
[Code Article]
Text(40) NOT NULL,
[Description]
Text(30),
[Prix achat]
Real,
[Prix vente]
Real,
[Réf fournisseur]
INT,
CONSTRAINT A1 PRIMARY KEY ([Code Article]),
CONSTRAINT A2 FOREIGN KEY ([Réf fournisseur])
REFERENCES Fournisseurs([N° Siret])
)
16
Les associations (N-N) : problème
Un article à un seul fournisseur et un fournisseur peut fournir plusieurs articles.
Association (1-N)
Articles
Code
Article
…
Rèf
fournisseur
Fournisseurs
N° Siret
1
105230
2
105234
105230
105230
105234
6
105234
105237
8
105230
9
105237
4
…
…
…
Un article à plusieurs fournisseurs et un fournisseur fournit plusieurs articles.
Association (N-N) !?
17
Les associations (N-N) : solution
Association (N-N)
Deux associations (1-N).
Table(s) jonction(s).
Articles
ArticlesFournisseurs
Fournisseurs
Réf Article
Réf fournisseur
1
1
105230
2
2
105234
105230
1
105234
105234
6
4
105230
105237
8
6
105237
9
8
105230
8
105234
9
105234
Code
Article
4
…
N° Siret
…
…
…
La clé primaire de la table ArticlesFournisseurs : (Réf Article, Réf fournisseur)
18
Création de la table Articles-Fournisseurs
CREATE TABLE ArticlesFournisseurs (
[Réf Articles]
Text(40),
[Réf Fournisseurs]
INT,
CONSTRAINT AF1 PRIMARY KEY ([Réf Articles], [Réf Fournisseurs]),
CONSTRAINT AF2 FOREIGN KEY ([Réf Articles])
REFERENCES Articles([Code Article]),
CONSTRAINT AF3 FOREIGN KEY ([Réf Fournisseurs])
REFERENCES Fournisseurs([N° Siret])
)
19