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

1. 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.htm E-mail : souheib.baarir@u-paris10.fr Université Paris Ouest Nanterre la Défense. 2009-2010.

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

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

4. 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] [,champ2type [(taille)] [NOT NULL]]* [,CONSTRAINT nom PRIMARY KEY (champi[, champj [, …]]) | UNIQUE (champk [, champl [, …]]) | FOREIGN KEY (champm [, champn [, …]]) REFERENCEStableE [(champE1 [,champE2 [, …]])]]* )

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 : • Les valeurs numériques. • Bit,YesNo,… • Numeric, Short, Int, Real, Double,… • Autoincrement,… • Les dates et les heures. • Date. • Time.  • 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 La clause FOREIGN KEYpermet, 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]) 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]) )

8. Modification de table : syntaxe générale (simplifié) ALTER TABLE table ADD COLUMN champ type[(taille)] [NOT NULL] | ALTER COLUMN champtype[(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) DROP TABLE article Supprime la table article de notre base de données. La suppression d’une table se fait par DROP TABLE : DROP TABLE table

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 à un seul fournisseur et un fournisseur peut fournir plusieurs articles. Association (1-N) Articles Fournisseurs Un article à plusieurs fournisseurs et un fournisseur fournit plusieurs articles. Association (N-N) !?

18. Les associations (n-n) : solution Association (N-N) Deux associations (1-N). Table(s) intermédiaires. Articles ArticlesFournisseurs Fournisseurs La clé primaire de la table ArticlesFournisseurs: (Réf Article, Réf fournisseur)

19. Création de la table Articles-Fournisseurs 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]) )