Initiation aux bases de donnes et la programmation

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. lip 6. fr/Souheib. Baarir/bdvba/support. htm E-mail : souheib. baarir@u-paris 10. fr 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 : E d’interrogation (LID). E de manipulation (LMD). E 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 table ( champ 1 type [(taille)] [NOT NULL] [, champ 2 type [(taille)] [NOT NULL]]* [, CONSTRAINT nom PRIMARY KEY (champi [, champj [, …]]) | UNIQUE (champk [, champl [, …]]) | FOREIGN KEY (champm [, champn [, …]]) REFERENCES table. E [(champ. E 1 [, champ. E 2 [, …]])]]* ) 4

Création de table : types de données • Un type de données définit le genre de contenu d'un champ. E les opérations pouvant être effectuées sur ce champ. • Trois catégories de types : 1. Les valeurs numériques. E Bit, Yes. No, … E Numeric, Short, Int, Real, Double, … E Autoincrement, … 2. Les dates et les heures. E Date. E Time. 3. Les chaînes de caractères. E Text, Memo, … E Char, … 5

Création de table : Premier exemple CREATE TABLE article ( [code article] Autoincrement NOT NULL, [prix article] INT, [date fabrication] DATE, CONSTRAINT a 1 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 a 1 PRIMARY KEY ([code article]), CONSTRAINT a 2 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 F 1 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 A 1 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 A 2 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 A 1 PRIMARY KEY ([Code Article]), CONSTRAINT A 2 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 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. Fournisseurs Réf Article Réf fournisseur 1 1 105230 2 2 105234 105230 1 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 Articles. Fournisseurs : (Réf Article, Réf fournisseur) 18

Création de la table Articles-Fournisseurs CREATE TABLE Articles. Fournisseurs ( [Réf Articles] Text(40), [Réf Fournisseurs] INT, CONSTRAINT AF 1 PRIMARY KEY ([Réf Articles], [Réf Fournisseurs]), CONSTRAINT AF 2 FOREIGN KEY ([Réf Articles]) REFERENCES Articles([Code Article]), CONSTRAINT AF 3 FOREIGN KEY ([Réf Fournisseurs]) REFERENCES Fournisseurs([N° Siret]) ) 19