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.
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 le nom, le prénom et l’année de naissance des artistes enregistrés dans la base
- Donnez le titre des films sortis en 1999
- Donnez la liste des films policiers, fantastiques ou avec suspense, triée sur le titre
- Donnez le titre et le genre 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 le nom et le prénom des internautes présents dans la base et qui ont fait au
moins une évaluation de film
- Donnez la liste des films dans lesquels Bruce Willis a tourné
- Donnez le titre des films dont le metteur en scène est né entre 1940 et 1960.
- Formulez une requête de votre choix en français qui mette en œuvre les tables pays,
artiste 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 films par année de sortie
- Donnez la note moyenne donnée pour chaque film ainsi que les min et max de ses
notations. (Vous pouvez ajouter des n-uplets dans les relations Internautes et
Notation).
- Donnez le genre des films pour lesquels on a plus de 2 films dans la base, avec le
nombre de films et l’année de sortie du premier 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.