Initiation aux bases de données et à la programmation événementielle

Cours N°4 :

langage de définition de données.

Support de cours de Souheib BAARIR.

Page web : pagesperso-systeme.lip6.fr/Souheib.Baarir/bdvba/support.htmE-mail : souheib.baarir@u-paris10.fr

Université Paris Ouest Nanterre la Défense.2009-2010. 1

2

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).

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

4

Création de table : syntaxe générale (simplifiée)

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 [, …]])]]*

)

La création d’une table est prévue en SQL via l’instruction CREATE TABLE.

5

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,…

6

Création de table : Premier exemple

CREATE TABLE article ( [code article] Autoincrement NOT NULL, [prix article] INT, [date fabrication] DATE, CONSTRAINT C PRIMARY KEY ([code article]))

7

Création de table : 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,

CONSTRAINT C PRIMARY KEY ([code article]),

CONSTRAINT C1 FOREIGN KEY ([référence fournisseur]) REFERENCES fournisseur ([N° Siret])

)

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.

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])

8

Modification de table : syntaxe générale (simplifié)

ALTER TABLE table

ADD COLUMN champ type[(taille)] [NOT NULL] |     

ALTER COLUMN champ type[(size)] |

ADD CONSTRAINT … |   

DROP COLUMN champ |

DROP CONSTRAINT nom

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.

9

Modification de table : Premier exemple

ALTER TABLE article ALTER COLUMN [prix article] CHAR(25)

10

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.

11

Exercice : BD Articles-Fournisseurs

Un vendeur veut gérer, de façon automatique, les articles qu’il vend. Sachant • qu’un fournisseur est décrit par : un numéro de Siret, une description, et une adresse,•qu’un article est décrit par : un code alphanumérique, une description, un prix d’achat et un prix de vente,•qu’un article à un seul fournisseur et qu’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

12

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 C PRIMARY KEY ([N° Siret]))

13

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 C1 PRIMARY KEY ([Code Article]))

14

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 C2 FOREIGN KEY([Réf fournisseur]) REFERENCES Fournisseurs([N° Siret])

15

Exemple de violation d’ intégrité référentielle

16

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 C1 PRIMARY KEY ([Code Article]), CONSTRAINT C2 FOREIGN KEY ([Réf fournisseur])

REFERENCES Fournisseurs([N° Siret]))

17

Les associations (n-n) : problème

Un article à plusieurs fournisseurs et un fournisseur fournit plusieurs articles.

Un article à un seul fournisseur et un fournisseur peut fournir plusieurs articles.

Code Article …

1

…

2

4

6

8

9

N° Siret …

105230

…

105234

105237

ArticlesFournisseursRèf

fournisseur

105230

105234

105230

105234

105230

105237

Association (1-N)Association (1-N)

Association (N-N) !?Association (N-N) !?

18

Les associations (n-n) : solution

Code Article …

1

…

2

4

6

8

9

N° Siret …

105230

…

105234

105237

ArticlesFournisseurs

Association (N-N) Association (N-N) Deux associations (1-N).Deux associations (1-N).Table(s) intermédiaires. Table(s) intermédiaires.

Réf Article Réf fournisseur

1 105230

2 105234

1 105234

4 105230

6 105237

8 105230

8 105234

9 105234

ArticlesFournisseurs

La clé primaire de la table ArticlesFournisseurs : (Réf Article, Réf fournisseur)La clé primaire de la table ArticlesFournisseurs : (Réf Article, Réf fournisseur)

19

CREATE TABLE ArticlesFournisseurs ( [Réf Articles] Text(40), [Réf Fournisseurs] INT,   

CONSTRAINT C3 PRIMARY KEY ([Réf Articles], [Réf Fournisseurs]),

CONSTRAINT C4 FOREIGN KEY ([Réf Articles]) REFERENCES Articles([Code Article]),

CONSTRAINT C5 FOREIGN KEY ([Réf Fournisseurs]) REFERENCES Fournisseurs([N° Siret])

)

Création de la table Articles-Fournisseurs