TP1 « Conception de Systèmes d'Information » - création et modification de tables

L'objectif de ce TP est de développer une base de données représentant l'application modélisé lors du TD précédent. Pour cela, prenez votre schéma relationnel construit à partir des règles de transformation.

Création du schéma et des tables

Commencez par lancer un serveur MySQL puis PHPmyAdmin expliqué dans les instructions de prise en main.

La première étape consiste à créer une nouvelle base de données qui contiendra l'ensemble de vos tables. Cliquez sur Bases de données dans le menu. Donnez un nom à votre base (par exemple, spectacle) puis cliquez sur Créer sans modifier aucun autre paramètre.

Maintenant que la base de données est créée, son nom doit apparaître à l'extême gauche de l'interface. Cliquez sur le nom de votre base de données. Vous devez alors obtenir quelque chose qui ressemble à ceci :

base de données créée

Cliquez sur le bouton SQL dans le menu. Vous allez maintenant créer les tables de votre base de données à l'aide de commandes SQL. Pour créer une table, il suffit de taper :

CREATE TABLE nom_de_la_table (
nom_clé type_clé PRIMARY KEY,
nom_attribut type_attribut,
…);

Par exemple, une table Livre(num_livre: entier, nom_livre: chaîne, num_editeur: entier) ayant comme clé primaire num_livre: entier et deux autres attributs peut être créée avec la commande :

CREATE TABLE Livre (
num_livre INT PRIMARY KEY,
nom_livre VARCHAR(50),
num_editeur INT);

On remarque que l'attribut de type chaîne de caractères a le type VARCHAR(50). Le nombre 50 est le nombre de caractères que l'on réserve pour l'attribut : dans ce cas, on n'autorise pas de titre de livre ayant plus de cinquante caractères.

Si la clé primaire d'une table comporte plus d'un attribut, il faut écrire :

CREATE TABLE nom_de_la_table (
nom_clé1 type_clé1,
nom_clé2 type_clé2,
nom_attribut type_attribut,
…,
PRIMARY KEY(nom_clé1,nom_clé2));

Par exemple, la table Ecrire(num_livre: entier, num_auteur: entier) peut être générée comme suit :

CREATE TABLE Livre (
num_livre INT,
num_auteur INT,
PRIMARY KEY(num_livre,num_auteur);

La liste des tables existantes dans la base de données est affichée dans la portion gauche de l'interface. Il est également possible de voir les tables existantes en tapant la commande SQL SHOW TABLES. Il est possible de voir les attributs d'une table à l'aide de la commande SHOW COLUMNS FROM nom_de_la_table.

Clé étrangère

Lorsqu'un attribut d'une table sert à faire référence à la clé primaire d'une autre table (par exemple, num_editeur dans la table Livre peut faire référence à la clé primaire d'une table Editeur), on dit que cet attribut est une clé étrangère. Si un attribut est une clé étrangère, alors la valeur de cette attribut pour toute ligne de la table doit obligatoirement être égale à une valeur existante de la clé primaire référencée. Par exemple, dans la table Livre, la valeur de num_editeur doit exister comme clé primaire dans la table Editeur. On peut indiquer cette contrainte en SQL :

CREATE TABLE nom_de_la_table (
nom_clé type_clé PRIMARY KEY,
nom_attribut type_attribut,
…,
nom_contrainte FOREIGN KEY (nom_attribut) REFERENCES nom_table_référencée(nom_clé_table2));

Il faut noter que l'on doit donner un nom à la contrainte de clé étrangère. Le nom est arbitraire et peut être choisi n'importe comment à condition de ne pas utiliser deux fois le même nom de contrainte dans toute la base de données. Le nom de la contrainte n'est pas le nom de l'attribut qui compose la clé étrangère.

Modification et suppression de tables

Il est possible de modifier ou de supprimer une table avec des commandes SQL. En particulier, durant ce TP, il est possible que vous fassiez des erreurs lors de la création de table, qui vous obligent à vous débarasser de tables existantes ou bien à les modifier.

On supprime une table en tapant :

DROP TABLE nom_de_la_table

Pour ajouter, supprimer ou changer le type d'une colonne d'une table donnée, on peut utiliser les commandes :

ALTER TABLE nom_table ADD nom_col type;
ALTER TABLE nom_table MODIFY nom_col type;
ALTER TABLE nom_table DROP nom_col type;

Pour modifier les clés primaires ou étrangères d'un table, on peut utiliser les commandes suivantes :

ALTER TABLE nom_table ADD PRIMARY KEY (nom_col1,…);
ALTER TABLE nom_table DROP PRIMARY KEY;
ALTER TABLE nom_table ADD CONSTRAINT nom_contrainte FOREIGN KEY (attr1) REFERENCES table2(attr2);
ALTER TABLE nom_table DROP FOREIGN KEY nom_contrainte;

Pour la dernière commande, il faut bien indiquer le nom de la contrainte et non pas le nom de l'attribut qui compose la clé étrangère.

Insertion et suppression de données

Lorsqu'une table a été créée, il est possible d'y ajouter des données (donc d'ajouter des lignes). On peut le faire à l'aide d'une commande INSERT INTO avec pour syntaxe :

INSERT INTO nom_table(nom_col1, nom_col2, …) VALUES (val1, val2, …);

nom_table doit correspondre au nom d'une table existante (donc créée au préalable avec une commande CREATE TABLE. nom_col1 doit correspondre au nom d'une colonne de la table, mais il n'est pas obligatoire de nommer toutes les colonnes car certaines colonnes peuvent avoir une valeur par défaut. val1, val2, etc. correspond aux valeurs respectives des colonnes mentionnées avant. Par exemple :

INSERT INTO Livre(num_livre, nom_livre, nom_editeur) VALUES (25698745, "À la recherche du temps perdu", 524568);

La suppression d'une ligne se fait par l'instruction DELETE avec pour syntaxe :

DELETE FROM nom_table WHERE condition;

condition exprime le critère que doivent satisfaire les lignes à supprimer. En général, on utiliser un condition du type nom_colonne = valeur, par exemple :

DELETE FROM Livre WHERE num_livre = 25698745;

Saisie du modèle relationnel complet

Finissez le TP en définissant l'ensemble des relations du modèle relationnel construit en TD. Ajoutez ensuite au minimum une ligne à chaque table. À la fin du TP assurez-vous de bien récupérer votre travail tel que cela est décrit dans les instruction relatives à PHPmyAdmin.

td1.html: last modified 2013/11/17 00:42:42 by Antoine Zimmermann.

Valid XHTML 1.0! Valid CSS!