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