TP2 base de données

TP2 base de données
Mineure 2A
Ce TP se compose de deux parties indépendantes. La première partie est à réaliser lors des 3
heures de TP.
A la fin de la séance, vous transmettrez le compte rendu aux enseignants sous la forme papier
ou par e-mail adressé à [email protected] (UNIQUEMENT au format texte ou
html).
Objectifs :
-
écrire des requêtes SQL faisant intervenir une ou plusieurs relations
comprendre les opérations sur les relations et les agrégats
modifier le contenu puis le schéma d’une relation
1. Requêtes sur une base MySQL
Nous allons reprendre la base de données proposée par Ph Rigaux consacrée aux films.
Depuis le TP 1 www.enst-bretagne.fr/yvon.kermarrec/TPBD/TP1/MySQL, vous avez créé
des relations et inséré du contenu. Nous allons donc passer aux phases d’utilisation de la base
et de son interrogation
Requêtes sur une seule relation
-
donnez les noms et prénoms des internautes déjà enregistrés dans la base
donnez les titres des films présents dans la base
donnez la liste des films policiers triée sur le titre
Donnez les titres des films qui évoquent New York (il s’agit ici de rechercher dans des
chaînes de caractères).
Requêtes sur une plusieurs relations
-
donnez les titres des films présents dans la base et qui ont fait l’objet d’une évaluation
par au moins un internaute
donnez l’ensemble des acteurs ayant tourné dans le film « Shining »
donnez les titres des films dont au moins un des acteurs mentionnés est né entre 1940
et 1960.
Formulez une requête qui mette en œuvre les tables pays et film et traduisez la en
SQL. (Pour cela, vous devez insérer des n-uplets dans la relation Pays).
Quelques agrégats
-
Donnez le nombre de film par genre
-
Donnez la note moyenne donnée par internaute ainsi que les min et max de ses
notations.
Donnez les genres de films pour lesquels on a plus de 3 films dans la base avec le
nombre de films et l’année de sortie du dernier film.
Modifications du contenu de la base
Vous avez déjà vu comment insérer des éléments dans la base avec la commande
INSERT :e.g ; INSERT into pays values (‘EN’, ‘UK’, ‘Royaume Uni’) ;
Il existe aussi une variation de ‘insert’ qui permet d’insérer dans une table le résultat d’une
requête.
CREATE TABLE Extrait (titre VARCHAR(50) NOT NULL, annee INTEGER) ;
INSERT INTO Extrait select titre, annee from Film;
On peut aussi détruire du contenu avec la commande :
DELETE FROM table where condition
(cette commande retire tous les nuplets édités par select * from relation where condition).
On peut aussi modifier le contenu à l’aide des commandes UPDATE (regarder la
documentation en ligne pour la syntaxe de cette commande).
Procédez à des insertions, retraits et modifications de n-uplets de la base
2 Modifier une base existante
Cette partie du TP ne fait pas l’objet d’un compte rendu ! Le but de cette partie est de
montrer comment on peut procéder à la définition d’une nouvelle relation et à l’expression
de certaines propriétés.
La création d’un schéma d’une base de données n’est qu’une première étape dans la vie
d’une base de données. On peut être par la suite amené à créer de nouvelles tables, à
ajouter des attributs ou à en modifier la définition. Le but de cette seconde partie du TP est
donc de procéder à différentes modifications.
Pour réaliser les différentes opérations demandées, vous devrez également consulter les
documentations techniques en ligne.
2.1 Ajouter une relation
Regarder le schéma d’une relation de la base Cinéma avec la commande MySQL
« desc ». Identifiez un attribut clé et essayer d’insérer un n-uplet qui aura au moins la
même valeur sur les attributs clés qu’un n-uplet déjà présent dans la base. Que se passe t
il ? Pourquoi ?
2.2 Ajouter une relation
On souhaite compléter la base avec des informations sur le ou les producteurs du film ?
Formulez des contraintes sur cette nouvelle relation (clés, clés étrangères, domaine de valeurs,
etc…) et traduisez-les en SQL. Donnez cette modélisation et insérez des n-uplets dans cette
nouvelle relation afin de pouvoir l’interroger.
2.2 Modification du schéma d’une relation existante
A partir de la documentation disponible sur la commande « ALTER TABLE », modifiez le
schéma de la base afin que la région (un attribut de la relation internaute) soit de 30 caractères
et par défaut fixé à la région Bretagne. Visualiser le résultat des opérations à l’aide de la
commande « desc internaute ».
Toujours à l’aide de la documentation, proposez la création d’un index sur le genre du film. A
quoi peut servir un tel index ? pourquoi le rajouter sur cet attribut genre ? quels impacts
(positifs et négatifs) a cette modification du schéma ?
Vous avez défini une clé étrangère (« foreign key »). Vérifiez que cette information est prise
ou non en compte par votre système.
Comment détruire de l’information de la base : essayer de réduire un champ d’information et
d’enlever un attribut ? quelles sont les instructions que vous avez exécutées ? quels sont les
résultats de ces instructions sur le contenu précédent de la base ?
3 Rappels du TP 1
Chaque binôme dispose d’une base de données propre afin de faire les travaux pratiques tout
au long de ce module. Un numéro à 2 chiffres XY vous a été attribué. Chaque utilisateur est
répéré par un login ‘userXY’ et un mot de passe identique (ie, ‘user XY’). Chaque utilisateur
dispose aussi de sa propre base de données repérées par ‘BDYY’
Obtenir de l’aide sur MySQL
-
http://hotwired.lycos.com/webmonkey/programming/php/tutorials/tutorial4.html
http://www.freewebmasterhelp.com/tutorials/phpmysql/
http://www.mysql.com/doc/en/index.html
1.1 Connexion au serveur
Pour pouvoir envoyer des requêtes à MySQL, il faut en premier lieu y avoir accès, c'est-à-dire
avoir été enregistré par l'administrateur de la base mysql.
Il faut ensuite établir une connexion avec le serveur, en envoyant la commande :
mysql -h hôte -u userXY -p
Cela tentera d'établir une connexion avec la base MySQL dont le serveur tourne sur l'hôte
spécifié, sous le nom d'utilisateur userXY. Cette connexion ne sera effective que lorsque
l'utilisateur aura fourni un mot de passe valide.
Une fois que la connexion est établie, vous pouvez donner des commandes en MySQL.
Toutes ces commandes devront impérativement se terminer par un point-virgule pour que
l'interprète essaie de les exécuter.
1.2 Déconnexion du serveur
La déconnexion se fait simplement par l'une des deux comandes "quit" ou "exit" :
quit ;
1.3 Opérations sur les bases de données
Pour commencer il faut créer une base de données. Cette étape a déjà été réalisée pour vous
mais nous l’indiquons à titre d’information.
Une base de données est constituée d'un ensemble de tables, les tables contenant les données.
La base de données correspond donc en quelque sorte à un dossier, qui contient des fichiers
(les tables).
Création d'une base de nom BDXY
CREATE DATABASE BDXY;
Suppression d'une base existante
DROP DATABASE BDXY;
Dans cette commande, vous pouvez ajouter l'option IF EXISTS pour éviter d'avoir une erreur
si la base n'existait pas.
Sélection de la base de travail
USE BDXY;
Cette commande définit la base BDXY comme étant celle dans laquelle vont se faire toutes
les opérations.
Cela évite, pour les commandes ultérieures, d'avoir à préciser que les tables auxquelles on fait
référence sont celles de cette même base.
Consultation des bases existantes
SHOW DATABASES;
Création d’une table
Une fois que base a été définie, il faut ensuite définir les tables qui la composeront avec leur
structure (liste des champs qui la composent avec leurs spécificités).
Création d'une table
CREATE TABLE CD (
CD_ID int DEFAULT '0' NOT NULL auto_increment,
CD_TITLE text,
PRIMARY KEY (CD_ID) );
Nous avons donc créé une table appelée CD, contenant 2 champs :
- l'identifiant CD_ID qui permet de différencier les données.
- Le nom CD_TITLE du CD, qui est une chaîne de caractères.
L'identifiant CD_ID est défini avec la propriété auto-increment grâce à laquelle sa valeur
augmentera d'une unité à chaque ajout d'un nouvel élément dans la table. A défaut d'éléments
dans la table, sa valeur sera 0.
Il est également défini comme clé primaire de la table, c'est grâce à elle que les lignes de la
table seront définies de façon unique.
Consultation de la liste des tables
SHOW TABLES ;
Cette commande affiche simplement la liste des tables qui existent dans la base.
Consultation de la structure d'une table
SHOW COLUMNS FROM CD ;
Cette commande affiche simplement la liste des champs qui ont été définis lors de la création
de la base.
Elle affiche également la nature de chaque champ.