L'objectif de ce TP est d'apprendre à utiliser le langage SQL pour la définition de données.
Les exercices de ce TP portent sur une BdD référençant un ensemble de livres, d'auteurs et d'éditeurs. Le schéma E/A de cette base de données est donné ci-dessous.
Les règles de transformation nous permettent d'obtenir les relations suivantes:
La première étape consiste à créer les tables correspondant à ces relations.
Démarrez un serveur MySQL et MySQL Workbench. Ouvrez une connexion au serveur et créez un nouveau schéma. Sélectionnez ce schéma par défaut.
Les instructions SQL sont à saisir dans l'écran SQL Query comme dans l'exemple ci-dessous.
La création de tables se fait par l'instruction CREATE TABLE selon la syntaxe suivante:
CREATE TABLE nom_table (nom_col1 type1 [contraintes], nom_col2 type2 [contraintes]);
Un ensemble de type de données et de contraintes simples est donné dans le livret de cours. Des contraintes référençant plusieurs attributs peuvent aussi être indiquées à la suite des attributs comme si c'était un autre attribut (c'est-à-dire dans les parenthèses et séparé de ce qui précède par une virgule). C'est notamment le cas pour créer des clés primaires composées de plusieurs attributs ou pour définir des clés étrangères.
PRIMARY KEY(attr1, attr2)attr1 et attr2 sont les attributs composant la clé primaireCONSTRAINT nom_contrainte FOREIGN KEY (attr1) REFERENCES table2(attr2)nom_contrainte est un nom associé à la contrainte, attr1 l'attribut représentant la clé étrangère, table2 la table ayant pour attribut attr2 qui est l'attribut auquel lier la clé étrangère.Créez les tables correspondant aux 4 relations données ci-dessus en définissant bien les clés primaires et étrangères. Attention à l'ordre de création des tables de manière à pouvoir créer des clés étrangères se référant à des attributs (et donc des tables) existants.
La structure des tables peut être modifiée après leur création par l'instruction ALTER TABLE. Pour ajouter, modifier ou supprimer des attributs à une table on utilise respectivement
ALTER TABLE nom_table ADD nom_col type [contraintes];ALTER TABLE nom_table MODIFY nom_col type [contraintes];ALTER TABLE nom_table DROP nom_col type [contraintes];Il est également possible d'ajouter ou de supprimer des clés primaires et étrangères en utilisant respectivement :
ALTER TABLE nom_table ADD CONSTRAINT nom_contrainte PRIMARY KEY nom_contrainte [contrainte];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 CONSTRAINT nom_contrainte;Modifiez la table Livre pour :
Il serait plus logique d'utiliser le numero ISBN des livres comme clé plutot que d'introduire un numéro de livre artificiel. Essayez de modifier la table pour que ISBN en devienne la clé. Que se passe-t-il ?
Cette modification n'est pas autorisée car il existe déjà une clé primaire pour cette table. Essayez de supprimer l'attribut num_livre pour pouvoir définir une nouvelle clé primaire. Que se passe-t-il ?
Une clé étrangère de Ecrire faisant référence à num_livre, il est impossible de supprimer cet attribut. Il faut auparavant supprimer toute relation entre une clé étrangère et num_livre.
Pour réaliser le changement de clé primaire de Livre, écrivez les instructions suivantes :
Ecrire se référant à num_livrenum_livreisbnEcrire et isbnLes requêtes d'insertion d'enregistrements se font par l'instruction INSERT INTO avec pour syntaxe :
INSERT INTO nom_table[(nom_col1, nom_col2...)] VALUES (val1, val2, ...);
Ajoutez 2 enregistrements à la table Editeur correspondant aux editeurs suivants :
| num_editeur | nom_editeur |
| 1 | Gallimard |
| 2 | Plon |
Ajoutez 1 enregistrement à la table Livre correspondant au livre suivant :
| isbn | nom_livre | num_editeur | nb_pages |
| 123456 | Introduction a SQL | 2 | 350 |
Vérifiez dans les tables correspondantes que ces enregistrements existent en utilisant l'instruction SELECT * FROM table où table est le nom de la table correspondante.
Les requêtes de suppression d'enregistrements se font par l'instruction DELETE avec pour syntaxe :
DELETE FROM nom_table WHERE condition;
où condition exprime le critère que doivent satisfaire les enregistrements à supprimer.
Supprimer de la table Editeur l'enregistrement pour lequel num_editeur = 1. Vérifiez que la suppression a bien été effectuée.
Supprimer de la table Editeur l'enregistrement pour lequel num_editeur = 2. Que se passe-t-il ? Interprétez le message d'erreur pour comprendre ce qui empêche la suppression. En dernier recours si vous ne comprenez pas, vous pouvez demander à un enseignant chargé de TP...