Bases de données relationnelles

Université de Lille – Sciences et Technologies, Master GI
Bases de Données Relationnelles
Bases de données relationnelles - Travaux pratiques
Access
Toutes les manipulations sont réalisées sous Access, le SGBD « grand public » inclus dans la suite
Microsoft Office.
Exercice 1 : Importation de la base MySQL
Dans un premier temps, nous allons transférer sous Access la base créée sous MySQL, grâce à
l’interface ODBC (Open Database Connectivity).
1) Ouvrir Access et créer la base base_etudiants. Enregistrer ce fichier dans votre compte utilisateur.
2) Dans cette base, choisir Fichier/Données externes/Importer ..., puis comme type de fichiers ODBC
Databases(). Dans la boîte de dialogue, définir une nouvelle Source de données machine, qui sera une
source de données utilisateur utilisant le driver MySQL ODBC 5.1 Driver. Les paramètres de connexion
à notre base de données MySQL sont les suivants :
Importer alors toutes les tables de la base MySQL sous Access.
3) Ouvrir chaque table, d’abord en mode Feuille de données afin de vérifier que les données ont bien été
importées, puis en mode Création en cliquant sur le bouton . Visualiser ainsi le type de chaque champ
et ses propriétés (cf. partie inférieure de la fenêtre en mode Création).
Compte-rendu : comparer les types de chaque champ de chaque table sous MySQL et sous Access, en
dressant un tableau de correspondance. Commenter ces résultats :
◦ Access permet-il de représenter tous les types de MySQL ?
◦ La « taille » des champs textes est-elle conservée lors de l’importation ?
◦ Les autres propriétés (clé, index, contraintes, valeur par défaut) des champs MySQL sont-elles
correctement importées sous Access ?
◦ L’intégrité référentielle de la base importée est-elle assurée (justifier par une manipulation) ?
1/6
Université de Lille – Sciences et Technologies, Master GI
Bases de Données Relationnelles
Exercice 2 : Clé primaire et intégrité référentielle
Pour que la base Access soit définie de manière aussi proche que possible que la base MySQL, il nous
faut réaliser quelques manipulations supplémentaires.
1) Modifier chaque table pour en redéfinir la clé primaire, en cliquant sur le bouton
après avoir
sélectionné le (ou les) champ(s) concerné(s). Enregistrer cette modification, puis passer en mode Feuille
de données et insérer un nouvel enregistrement (dans la dernière ligne, marquée
) pour s’assurer que
la clé primaire est à présent bien définie comme telle.
Compte-rendu : expliquer et justifier la manipulation réalisée pour vérifier la propriété de clé.
2) Redéfinir les contraintes d’intégrité référentielle dans le menu Outils/Relations … : ajouter les 4 tables
au schéma de relations en cliquant sur le bouton
, puis définir les « relations » en glissant-déposant
chaque clé étrangère sur la clé primaire qu’elle référence, par exemple :
À l’issue de cette manipulation, le schéma des relations devrait être exactement celui présenté en page 3
des énoncés de TD. Fermer ce schéma en l’enregistrant.
Compte-rendu : l’intégrité référentielle de la base est-elle à présent assurée (justifier par plusieurs
manipulations) ?
3) Rouvrir le schéma des « relations », puis modifier les propriétés de la jointure (en double-cliquant
dessus) entre les tables etudiants et notes en cochant les 2 autres cases dans la boîte de dialogue cidessus.
Compte-rendu : à quelle propriété SQL de clés étrangères correspond chacune de ces 2 fonctionnalités ?
Justifier leur utilité respective par des manipulations que vous décrirez.
Exercice 3 : Premières requêtes
Access offre la possibilité de formuler des requêtes SQL de manière graphique. Nous allons ici
découvrir comment fonctionne cette interface, en faisant le lien avec notre connaissance de SQL.
Sous Access, une requête peut être visualisée sous 3 modes : sa conception graphique se fait en mode
Création
, sa définition en SQL en mode , son résultat est visualisé en mode Feuille de données . Ces 3
modes sont accessibles sur le premier bouton de la barre d’outils. Pour le compte-rendu des questions suivantes,
vous ferez le lien entre la définition graphique des requêtes et leur code SQL.
Dans tous les exercices qui suivent, afin de conserver une trace de chaque requête, il est conseillé de la
sauvegarder sous un nom spécifique (cf. menu Fichier/Enregistrer sous …) avant de la modifier.
2/6
Université de Lille – Sciences et Technologies, Master GI
Bases de Données Relationnelles
1) Créer un nouvel objet Requête (en mode Création, c’est-à-dire sans utiliser d’assistant). Ajouter
uniquement la table etudiants, puis double-cliquer sur le champ num_etu. Dans la ligne Critères de la
grille d’interrogation, ajouter la condition >=10. Double-cliquer ensuite sur le « champ » * pour
l’ajouter aussi à la requête. Afficher le résultat de cette première requête, puis son code SQL.
Compte-rendu : à quelles clauses SQL correspondent les lignes Champ, Table et Critères de la grille
d’interrogation ? Quelles remarques pouvez-vous faire sur la requête SQL générée par Access ? D’où
vient le Champ0 apparaissant dans le résultat ?
Désormais, il est demandé de reproduire le code SQL « épuré » des requêtes, c’est-à-dire le code
débarrassé des parenthèses excédentaires et des noms de tables inutiles (préfixes de champs).
2) Dans la colonne num_etu de la requête précédente, décocher la case Afficher et fixer un Tri décroissant.
Compte-rendu : à quelle clause SQL correspond la ligne Tri de la grille ? Dans quelles conditions doiton inclure un champ dans une requête sans afficher celui-ci ?
3) Cliquer sur l’en-tête de colonne grisé du champ num_etu et supprimer celui-ci de la requête. Ajouter le
champ sexe et définir un critère pour ne sélectionner que les étudiantes. Ajouter aussi le champ
date_naiss et définir le critère >=1/1/90, d’abord dans la ligne Critères, puis dans la ligne Ou.
Compte-rendu : comment Access formate-t-il les dates ? Comment les critères sont-ils combinés
logiquement, selon qu’ils sont écrits sur la même ligne de la grille ou sur des lignes différentes (décrire
le résultat obtenu ici dans chaque cas) ?
4) En cherchant dans l’aide d’Access, trouver comment utiliser les opérateurs SQL BETWEEN … AND …
et LIKE, et l’opérateur logique NOT. Utiliser ceux-ci dans une requête pour retrouver les étudiants dont
le prénom ne commence pas par un P et dont la date de naissance est soit inconnue, soit dans l’année
1990.
Compte-rendu : comment Access francise-t-il ces opérateurs ? Donner la grille d’interrogation et le
code SQL de la requête, ainsi que les enregistrements qu’elle retourne.
5) Ajouter la table notes à la requête en cliquant sur le bouton
trouver les notes des étudiantes nées en 1990 ou après.
. Définir alors une requête permettant de
Compte-rendu : la jointure entre ces tables est-elle définie et, si oui, comment expliquer cela ? Donner
le code SQL généré par Access et commenter sa syntaxe.
Exercice 4 : Fonctionnalités de requêtes avancées
Nous étudions ici comment d’autres aspects connus de SQL sont intégrés dans la grille d’interrogation
d’Access, ainsi que quelques fonctionnalités propres à ce SGBD.
1) Modifier la requête précédente (3.5) en utilisant la fonction YEAR() pour fixer un critère non plus sur
la date, mais sur l’année de naissance des étudiantes.
Compte-rendu : comment Access transforme-t-il la formule saisie dans la ligne Champ de la grille
(expliquer en détail) ? En examinant le code SQL correspondant, déduire comment donner un alias à un
champ, et proposer effectivement un alias pour ce champ calculé.
2) Supprimer (si ce n’est déjà fait) le champ etudiants.* de la requête, puis afficher la ligne Opération en
cliquant sur le bouton . Modifier la requête pour obtenir la moyenne des notes de chaque étudiante
née en 1990 ou après.
Compte-rendu : reproduire la grille d’interrogation, avec notamment les champs et l’opération de
groupe choisie pour chacun d’eux. Quelle est l’opération de groupe (Regroupement ou Où) la plus
3/6
Université de Lille – Sciences et Technologies, Master GI
Bases de Données Relationnelles
adaptée aux champs sur lesquels un critère est posé ? Pour bien justifier votre réponse, donner le code
SQL épuré de la requête dans chacun de ces deux cas, et commenter la différence.
3) Modifier la requête pour ne garder que les groupes correspondant à une moyenne supérieure ou égale
à 10.
Compte-rendu : où fixer ce critère (sur quel champ et quelle opération de groupe) ? À quelle clause
SQL correspond-il ?
4) Access offre la possibilité de créer des requêtes paramétrées. Leur résultat dépend d’un paramètre dont
la valeur est fixée par l’utilisateur (dans une boîte de dialogue) avant exécution de la requête.
Utiliser cette fonctionnalité pour paramétrer la moyenne minimale dans la requête précédente. Pour cela,
il faut, dans le menu Requête/Paramètres …, définir un paramètre (nommé par exemple Moyenne min)
de type réel simple, puis utiliser ce paramètre dans la grille d’interrogation en entourant son nom de
crochets.
Compte-rendu : reproduire la grille d’interrogation correspondant à la requête. Donner plusieurs
exemples de valeurs différentes du paramètre, ainsi que le nombre de lignes de résultat correspondant.
5) Créer une nouvelle requête utilisant les tables enseignants et matieres. Double-cliquer sur la jointure
pré-existant entre ces tables et choisir la propriété :
Grâce au mode SQL, déduire l’effet de ce changement de propriété. Formuler alors une requête pour
trouver les enseignants qui n’enseignent aucune matière.
Compte-rendu : quel est l’effet de ce changement de propriété de la jointure ? Faire une copie d’écran
de la fenêtre d’interrogation pour la requête demandée.
Exercice 5 : Modification et suppression de données
Nous étudions ici d’autres types de requêtes que la sélection : mise à jour, suppression et insertion de
données. Pour ces requêtes, le bouton Feuille de données
permet simplement de visualiser les futures
données, tandis que le bouton Exécuter
modifie effectivement les données.
1) Créer une nouvelle requête basée sur la table notes. Sur le bouton , choisir une requête de type « Mise
à jour » . Définir alors une requête permettant de remplacer toutes les notes non valuées par 0.
Compte-rendu : donner le code épuré de cette requête. Combien de lignes sont ainsi modifiées ? Quelle
différence existe-t-il entre les données d’origine et les nouvelles, par exemple lors de calculs statistiques
(illustrez votre réponse) ?
4/6
Université de Lille – Sciences et Technologies, Master GI
Bases de Données Relationnelles
2) Créer une nouvelle requête permettant d’ajouter 1 point à toutes les notes des matières enseignées par
Dupond, Auguste.
Compte-rendu : reproduire la fenêtre d’interrogation, le code épuré de cette requête, ainsi que son
équivalent sans clause INNER JOIN (tester ce code SQL). Combien de lignes sont ainsi modifiées ?
3) Créer une nouvelle requête de type « Suppression »
permettant de supprimer les matières enseignées
par Dupond, Auguste. Pour spécifier la table contenant les enregistrements à supprimer, il faut ajouter
son « champ » * à la grille d’interrogation.
Compte-rendu : donner et commenter le code SQL épuré de cette requête (comparer avec MySQL).
Quel problème pose cette suppression (reproduire le message d’erreur) ? Comment y remédier ?
Combien d’enregistrements sont alors supprimés (donner le détail par table) ?
Exercice 6 : Fonctionnalités avancées d’Access
Nous étudions ici plusieurs autres fonctionnalités d’Access concernant les tables, l’intégrité référentielle
et les requêtes.
1) Access offre la possibilité d’afficher, dans un champ clé étrangère, un champ (ou une expression)
quelconque au lieu de la valeur de la clé primaire référencée. Pour illustrer cette fonctionnalité, modifier
la table matieres de la manière suivante. Dans le champ _num_ens, définir les propriétés suivantes (cf.
onglet Liste de Choix) :
◦
Afficher le contrôle : Zone de liste ;
◦
Contenu : SELECT num_ens, nom_ens FROM enseignants ;
◦
Nombre de colonnes : 2 ;
◦
Largeurs colonnes : 1cm;5cm.
Compte-rendu : comment se présentent alors les données de ce champ clé étrangère ? À quoi
correspondent ses différentes propriétés ? Faire le parallèle entre cette fonctionnalité d’Access et son
équivalent sous phpMyAdmin.
2) Créer une nouvelle requête, de type « Analyse croisée »
, permettant de visualiser, pour chaque
étudiant, la moyenne des notes obtenues dans les matières enseignées par chaque enseignant.
Compte-rendu : reproduire et expliquer la fenêtre d’interrogation et celle du résultat de cette requête.
Comment interpréter les cellules vides du résultat ? Le code SQL généré est-il disponible sous MySQL ?
Comment est-il possible de créer une table contenant les mêmes données que le résultat ?
3) Créer une nouvelle requête sans spécifier de table source. En mode , coller le code SQL trouvé lors
du TP MySQL comme solution de l’exercice 7.4 de TD (nombre d’étudiants se trouvant avant chaque
étudiant dans la liste alphabétique des noms). Prendre connaissance de la grille d’interrogation
correspondante.
5/6
Université de Lille – Sciences et Technologies, Master GI
Bases de Données Relationnelles
Compte-rendu : reproduire la fenêtre d’interrogation et expliquer les différentes caractéristiques de la
grille d’interrogation : tables, alias, opérations, critères, ...
4) Créer une nouvelle requête pour trouver les étudiants dont la moyenne coefficientée est supérieure ou
égale à 10. On affichera cette moyenne avec 2 décimales, grâce au bouton Propriétés .
Compte-rendu : donner la grille d’interrogation et le code épuré de cette requête. Peut-on utiliser un
alias dans la clause HAVING sous Access ?
Exercice 7 : Sous-requêtes
La complexité de certaines requêtes nécessite d’imbriquer une sous-requête dans une requête principale.
(cf. document complet du cours http://lagis-vi.univ-lille1.fr/~lo/ens/commun/bd/bd_cours.pdf, pp. 23-24).
1) Créer une requête calculant l’âge de chaque étudiant grâce à la fonction Now(), puis transformer cette
requête pour calculer l’âge moyen des étudiants. Enregistrer cette dernière requête.
Compte-rendu : donner le code SQL permettant de calculer l’âge moyen des étudiants.
2) Créer une nouvelle requête trouvant les étudiants plus âgés que la moyenne. Dans un premier temps,
baser cette requête sur la précédente et sur la table etudiants. Dans un second temps, supprimer la
requête source précédente, mais utiliser son code SQL comme sous-requête (en l’entourant de
parenthèses) dans le critère sur l’âge des étudiants.
Compte-rendu : donner le code SQL de la requête contenant la sous-requête. Est-il possible d’utiliser de
telles sous-requêtes sous MySQL ? Si oui, donner le code MySQL correspondant.
3) Certaines fonctions propres à Access réalisent elles-mêmes une sous-requête, facilitant ainsi l’écriture
de requêtes complexes. Modifier la requête précédente en utilisant la fonction DAvg() au lieu d’une
sous-requête.
Compte-rendu : donner le code SQL de cette requête. Expliquer à quoi sert et comment s’utilise la
fonction DAvg().
6/6