Travaux Pratiques et Dirigés de Bases de Données n˚ 5

Année 2016/2017
Deuxième semestre
DUT année spéciale
Département INFORMATIQUE
Travaux Pratiques et Dirigés de Bases de Données n˚ 5
ACCESS et ODBC
Les exercices de ce TD font référence à la base de données « Les outils de Max » (dont le schéma est cidessous) ainsi qu’aux bases « Vidéothèque » et « Camping ».
Pret
Contact
numContact
numObjet
datePret
dateRetourPrevu
dateRetourReel
numContact
nom
prenom
surnom
referencePhoto
telephoneDomicile
telephonePortable
telephoneBureau
Objet
numObjet
nomObjet
dateAchat
prixAchat
referencePhoto
Photo
numPhoto
adresseJPG
Schéma relationnel de la base « Les outils de Max »
1. Les outils de Max : base de données sous ACCESS
a. Ouvrir ACCESS et créer une base de données vide. Pour ce faire :
i. Dans les modèles disponibles, choisir « Base de données vide » ;
ii. Choisir un nom (par exemple « MAX »), pour cette base dans l’espace « Nom de fichier » ;
iii. Cliquer sur « Créer ».
Page 1
Bases de données – TD 5
Année 2016/2017
Deuxième semestre
Département INFORMATIQUE
DUT année spéciale
b. Sous la colonne « Cliquer pour ajouter » saisir perceuse.jpg puis jules_scie_sauteuse.jpg.
c. Sous l’onglet « Fichier », faire « Enregistrer ». Choisir le nom photo pour la table.
d. Avec la touche droite de la souris, se positionner sur la colonne « N° », choisir « Renommer
le champ » et le renommer numPhoto. Renommer aussi la deuxième colonne adresseJPG.
e. Avec la touche « Affichage » en haut à gauche, passez au « Mode Création ». Vérifiez que
numPhoto est la clé primaire (il doit y être l’icône d’une clé à gauche de l’attribut, sinon il
faut cliquer sur la touche « Clé Primaire »). Vérifier aussi que cet attribut est de type « NuméroAuto » (numéro auto incrémentant).
f. Enregistrer la fenêtre nommée « photo ».
g. Choisir maintenant l’onglet « Créer » puis « Création de table ».
h. Créer la table objet en saisissant ces cinq attributs. Définir sa clé primaire et choisissez le
type « NuméroAuto » pout cet attribut. Choisissez aussi les bons types pour les autres quatre
attributs.
i.
j.
Créer de la même façon les tables contact et pret. Fermer toutes les tables à la fin.
Définir les contraintes d’intégrités référentielles entre les quatre tables en cliquant sur l’icône
« Relations » dans l’onglet « Outils de base de données ». Pour ce faire :
i. Faire afficher les quatre tables ;
ii. Trainer la souris en appuyant sur sa touche gauche pour dessiner les flèches. Dans la
boite de dialogue qui s’ouvre ainsi, cocher « Appliquer l’intégrité référentielle ».
k. Enregistrer et fermer la fenêtre « Relations ».
l. Remplir les tables avec les données ci-dessous.
Page 2
Bases de données – TD 5
Année 2016/2017
Deuxième semestre
DUT année spéciale
Département INFORMATIQUE
Défonceuse prêtée à Pierrot le 5 janvier promise pour le week-end du 1er mai 2006
Perceuse prêtée à Albert le 7 janvier promise pour le 20 janvier revenue le 8 janvier 2006
Perforateur prêté à Jules le 15 janvier promis pour le 20 janvier 2006
Perceuse prêtée à Patrick le 12 avril 2006
Brosse à maroufler prêtée à Jean le 15 avril revenue le 20 avril 2006
Table à tapisser, brosse à maroufler, cutters, brosse à encoller prêtés à Guy le 1 mai 2006
Scie sauteuse prêtée à Pierre Lehaut 7 janvier 2007
Un instantané de la base « Les outils de Max »
Page 3
Bases de données – TD 5
Année 2016/2017
Deuxième semestre
Département INFORMATIQUE
DUT année spéciale
2. Les outils de Max : les requêtes qui n’en sont pas
a. Cliquer sur « Création de requêtes » sous l’onglet « Créer ». S’en servir pour écrire une requête qui liste les amis de Max en indiquant seulement leur nom, prénom, surnom puis la référence à leur photo. Pour ce faire :
i. Faire afficher la table nécessaire ;
ii. Double-cliquer sur les attributs que l’on veut voir affichés.
b. Cliquer sur « Affichage », passer en « Mode SQL » et regarder le SQL généré. Est-ce vraiment le code d’une requête ?
c. Passer en « Mode Feuille de Données ». Directement dans la fenêtre de cette requête, ajouter
des lignes (ajoutez vous-même et votre binôme dans la liste des amis de Max). Est-ce vraiment une requête ? C’est-à-dire, le SQL généré a-t-il changé ? Enregistrer cette requête sous
le nom amis. Que se passe-t-il lorsque l’on actualise la table contact ?
d. Cliquer sur « Création de requêtes » sous l’onglet « Créer », créer une requête outils
avec seulement le nom et la référence photo des objets de la table objet.
e. Cliquer sur « Assistant Requête » sous l’onglet « Créer ». Puis choisir « Assistant Requête simple ». Créer deux requêtes avec cet assistant affichant les noms, prénoms et surnom, date de prêt et nom de l’objet des amis ayant emprunté au moins un objet depuis la
création de la base. Il faudra respecter les consignes suivantes :
i. En plus des tables contact et prêt, la première requête doit se servir de la table
objet ;
ii. En plus des tables contact et prêt, la deuxième requête doit se servir de la requête outils ;
f. Remarquer que ces requêtes définies de manière identiques ne donnent pas le même résultat.
g. Passer en mode création pour comprendre l’erreur (proposez votre interprétation à votre enseignant).
3. Vidéothèque : accès à des tables via ODBC
Pour interfacer des tables Oracle sous ACCESS via un driver ODBC, le chemin à suivre est le
suivant :
a. Créer une base de données vide sous ACCESS en choisissant un nom pour cette base.
b. Via l’onglet « Données externes » cliquer sur « Base de données ODBC ».
c. Dans la fenêtre qui s’affiche choisir « Importer les données sources dans une nouvelle table
de la base de données active » ;
d. Via l’onglet « Source de données machine », choisir la source « ETUDOM ». Si elle n’est
pas déjà présente :
i. Cliquer sur « Nouveau... » et répondre « OK » à l’avertissement ;
ii. Choisir « Source de données utilisateur (pour cette machine uniquement) », puis
« Suivant ».
iii. Choisir « Oracle in instantclient_12_2 », puis « Suivant », puis « Terminer ».
iv. Dans la fenêtre qui s’affiche mettre
− Data Source name : ETUDOM
− TNS Service Name : ETUDOM
− User ID : son login Oracle
v. Cliquer sur OK et puis à nouveau sur OK.
e. Taper son mot de passe Oracle dans la boîte de dialogue.
f.
Choisir les six tables ens2004.acteur, ens2004.exemplaire, ens2004.film, ens2004.genre, ens2004.genrefilm, ens2004.individu.
g. Ouvrir les tables via ACCESS, définir leur clé primaire.
h. Ouvrir l’icône « Relations », ajouter les relations entre les tables.
Page 4
Bases de données – TD 5
Année 2016/2017
Deuxième semestre
Département INFORMATIQUE
i.
DUT année spéciale
Créer une requête en mode création qui liste les réalisateurs dont on possède au moins un
exemplaire d’un film d’aventure.
j. Ecrire cette même requête sous Oracle et comparer les résultats et les sources SQL.
k. Ecrire une requête qui liste les titres des films pour lesquels Yves Montand est un acteur.
Ecrire cette même requête sous Oracle et comparer les résultats et les sources SQL. Cherchez à comprendre l’erreur.
Page 5
Bases de données – TD 5
Année 2016/2017
Deuxième semestre
Département INFORMATIQUE
DUT année spéciale
4. Camping : modification des tables via ODBC
a. Refaire le parcours de l’exercice précédent pour interfacer les tables de la base de données
« Camping » que vous avez créé dans un TD précédent (attention tentez d’interfacer vos
tables et non pas celles de quelqu’un d’autre).
b. La procédure ne fonctionne pas pourquoi ?
c. Passer sous SQL Developer, ajoutez les droits nécessaires pour rendre vos tables visibles et
modifiables via ODBC.
d. Faites les tests d’interfaçage de vues Oracle et des essais de modifications.
Page 6
Bases de données – TD 5