IMPLANTATION ET EXPLOITATION D’UNE BASE DE DONNEES SOUS ACCESS II) Mode opératoire à partir d'un exemple concret a) Sujet La direction de la cinémathèque Saint Antonin a organisé un sondage auprès des habitants de la Commune pour savoir quelles étaient les demandes d'amélioration demandées. Un nombre important de sondés a répondu qu'ils désiraient pouvoir retrouver des renseignements sur les films à partir des éléments dont ils pouvaient se souvenir et qu'ils acceptaient qu'il y ait un intermédiaire si cela simplifiait l'obtention du résultat. La directrice a donc décidé de mettre en place une base de données de films, acteurs et réalisateurs surMs Access. Elle vous confie le développement et la mise en œuvre de cette application TRAVAIL A FAIRE 1 °) En vous référant au dictionnaire des données (annexe 1) réalisez le modèle conceptuel de données, le modèle relationnel et le modèle physique créez la base de données ainsi que les quatre tables qui la composent (seulement la structure, pas les données elles-mêmes). 2 °) Déclarez les relations entre les tables. Ou, encore mieux, utiliser « l’assistant liste de choix » dans le type de données pour la création de toutes les clés secondaires) 3°) Créez les formulaires de saisie pour les tables « réalisateur, films et acteurs » (en mode colonnes simples et avec l’assistant formulaire) Créez le formulaire principal et le sous formulaire associé à l’aide des données des tables « films, contrats et acteurs » Dans l’assistant formulaire, Sélectionnez toutes les données de la table « films », puis les données « num acteur » et « role » de la table « contrat », et enfin les données « nom acteur » et « prenom acteur » de la table « acteur » 4°) Saisissez les données de chaque table à partir des formulaires de saisie (voir annexe 3). 5°) Ecrivez les requêtes suivantes sur papier et en SQL sur la base que vous venez de créer. Audouin Jack cinémathèque page 1/30 ANNEXE 1 : DICTIONNAIRE DES DONNEES Nom des propriétés Type Format Nom -Réalisateur Prénom-Réalisateur Date - naissance réalisateur Nom acteur Prénom acteur Titre Date fin de tournage Rôle Num film Num acteur Num réalisateur texte texte Date Texte Texte texte Date Texte Num Num num 50 caractères 50 caractères jj/mm/aa 50 caractères 50 caractères 50 caractères JJ/mm/aa 50 caractères Entier Entier Entier ANNEXE 2 : MCD ET SCHEMA RELATIONNEL Modèle relationnel Films(Num-Film, Titre, Date fin tournage, #Num-Réalisateur) Réalisateurs(Num-Réalisateur, Nom-Réalisateur, Prénom -Réalisateur, datenaissance) Acteurs(Num-Acteur, Nom-Acteur, Prénom-Acteur) Contrats(#Num-Film, #Num-Acteur, Rôle) Audouin Jack cinémathèque page 2/30 Annexe 3 : Les données Table acteurs Table réalisateurs Table films : Audouin Jack cinémathèque page 3/30 Tables contrats Audouin Jack cinémathèque page 4/30 b) Mode opératoire 1) Créer une nouvelle base de données Démarrez le logiciel Access (démarrez+programmes+Access) Choisir nouvelle base de données vide Choisir le répertoire et attribuer un nom à la nouvelle base de données + créer 2) Création d'une table Avant de saisir les données d'une table, il faut d'abord créer sa structure * Créer la structure de la table Cliquer sur l’onglet créer +création de table Audouin Jack cinémathèque page 5/30 1. 2. 3. Indiquer le nom du champ (exemple num-acteurs), choisir le type de données (exemple texte, numérique, date... Il faut préférer le type numérique pour les champs faisant l'objet d'un calcul) et éventuellement une courte description du champ Indiquer les propriétés du champ Explication des principales propriétés Taille du champ : indiquer la taille maximale que peut prendre un champ Format : préciser le format du champ Légende : permet d’écrire le nom du champ tel qu’il devra figurer sur les formulaires et les états (exemple : NUMEROS DES ACTEURS) Valeur par défaut : Pour les champs qui contiennent des valeurs répétitives Propriété valide si, message si erreur : Permet de limiter la saisie des champs Indexé sans doublons : à utiliser uniquement pour la clé primaire Indexé avec doublons : à utiliser pour les clés primaires concaténées Non indexé : pour les autres champs Nul interdit : si oui, ce champ doit toujours être rempli 4. 5. 6. 7. 8. 9. 10. Indiquer la clé primaire (sur un champ unique pour une association hiérarchique, sur deux ou plusieurs champs pour une association non hiérarchique). Pour définir la clé : mettre le ou les champs en surbrillance et cliquer sur l'icône représentant une clé) Fermer et donner un nom à la table 3) RELATIONS ENTRE LES TABLES Avant de faire des requêtes sur des tables ou d'établir des formulaires, il est recommandé de préciser les liens existant entre ces tables. Mode opératoire à privileger : Lors de la créations des tables, à chaque fois que vous devez définir un champ qui est une clé externe (qu’elles soient issues d’une association hiérarchique ou non), je vous conseille de choisir le type « assistant liste de choix » et de suivre l’assistant, aller chercher la clé primaire correspondante dans la table associée et faites passer les champs souhaités dans la partie de droite (je vous conseille de choisir au moins la clé primaire et pourquoi pas un ou deux autres champs plus compréhensible) Triez si vous le souhaitez et laissez ou non la clé cachée (je vous conseille l’inverse de Microsoft). Suivez l’assistant et terminez. La relation est créé, vous pouvez vérifier dans l’onglet outil de la base de donnée+relation. Double cliquez sur la relation pour appliquer l’intégrité référentielle. Audouin Jack cinémathèque page 6/30 Autre mode mode opératoire: Outils de la base de données + relations Ajouter les table (clic droit) Pour les tables ayant un champ commun (clé primaire dans l’un, clé étrangère dans l’autre), cliquer sur la clé primaire et la glisser vers la clé étrangère Dans le tableau suivant, cochez sur "appliquez l'intégrité référentielle " Important : Les champs communs doivent avoir la même structure! Audouin Jack cinémathèque page 7/30 REQUETES EN MODE GRAPHIQUE (Cas Cinémathéque) 3 ) Requêtes du cas Cinémathèque Onglet créer+ création de requête a) liste des films tournés entre le 01/01/85 et le 31/12/94. (numéro, titre, date de fin de tournage) Entre #01/01/1985# et #31/12/1994# Num-film titre date fin tournage 1 Horreur 10/10/90 6 Les aventures 05/05/85 7 Les blagues du régiment 06/06/86 8 La découverte 07/07/87 9 Encore un effort 08/08/88 10 C'est bientôt fini 09/09/89 11 Truc et machin en vacances 12/12/92 12 Bonjour les dégâts 13/01/93 13 Grève ou marche 14/01/94 14 Le sanglot des phoques 15/08/91 b) Quels sont les films dont le titre contient le mot " phoque " (numéro, titre, date de fin de tournage) Num-film titre 14 Le sanglot des phoques 15 Les phoques et les mouettes Audouin Jack cinémathèque page 8/30 date fin tournage 15/08/91 31/12/98 c) Quels sont les films dirigés par John Huston ? (numéro, titre, date de fin de tournage, nom et prénom du réalisateur) Num-film titre date fin tournage 2 Terreur 01/01/81 3 Sueur 02/02/82 4 Angoisse 03/03/83 Nom réalisateur Huston Huston Huston prenom-realisateur John John John d) Quels sont les films dans lesquels Brigitte Bardot a joué ? (Numéro, titre, date de fin de tournage, rôle. nom et prénom de l'actrice) Num-film titre date fin tournage 4 Angoisse 03/03/83 6 Les aventures 05/05/85 8 La découverte 07/07/87 9 Encore un effort 08/08/88 12 Bonjour les dégâts 13/01/93 14 Le sanglot des phoques 15/08/91 Audouin Jack rôle Julie Juliette Miss Tique Pimprenelle Sœur Theresa La bombe La pleureuse cinémathèque Nom Acteur Bardot Bardot Bardot Bardot Bardot Bardot Prénom Acteur Brigitte Brigitte Brigitte Brigitte Brigitte Brigitte page 9/30 e) Quels sont les films dans lesquels Brigitte Bardot a Joué et dont le titre contient le mot phoque ? (numéro, titre, date de fin de tournage, rôle. nom et prénom de l'actrice) Num-film titre date fin tournage rôle Nom Acteur Prénom Acteur 14 Le sanglot des phoques 15/08/91 La pleureuse Bardot Brigitte f) Quels sont les acteurs qui ont joué le rôle de "colonel John " et dans quels films étaitce ? (numéro, nom et prénom de l'acteur, numéro, titre du film et date de fin tournage, rôle joué. A trier dans l'ordre croissant des noms d'acteurs. NUMEROS DES ACTEURS Audouin Jack Nom Acteur 1 Brando 2 Quinn Prénom Acteur Marlon Anthony Numfilm titre 4 Angoisse 12 Bonjour les dégâts cinémathèque date fin rôle tournage 03/03/83 Colonel John 13/01/93 Colonel John page 10/30 g) Quels sont les acteurs qui ont été dirigés par Alun Péchynet (numéro, nom et prénom du réalisateur, titre du film, numéro, nom et prénom de l'acteur. A effectuer dans l'ordre croissant des noms d'acteur NumNom prenomrealisateur réalisateur realisateur 3 Pechynet Alun 3 Pechynet Alun 3 Pechynet Alun 3 Pechynet Alun 3 Pechynet Alun titre Grève ou marche Grève ou marche Grève ou marche Le sanglot des phoques Le sanglot des phoques NUMEROS DES Nom Prénom ACTEURS Acteur Acteur 2 Quinn Anthony 4 Wayne John 5 The Kid Billy 1 Brando Marlon 3 Bardot Brigitte h ) Indiquer tous les rôles joués par Marlon Brando Nom Acteur Brando Brando Brando Brando Brando Brando Brando Brando Audouin Jack cinémathèque rôle Alex Thorm Mike Mouse Colonel John Paul Ho Antoine Antony Bonzy L'esquimaud Le capitaine crochet page 11/30 i) Indiquer tous films dirigés par Fellini. Nom réalisateur Fellini Fellini Fellini Fellini titre La découverte C'est bientôt fini Truc et machin en vacances Bonjour les dégâts 4 )Autres exemples de requêtes sur la base de données Cinémathèque a ) Requête paramètre - Rôle d'un acteur quelconque Audouin Jack cinémathèque page 12/30 b ) Requête impliquant une opération Pour faire apparaitre la ligne opération sur une requête sélection il faut cliquez sur le symbole sigma du groupe afficher/masquer - Nombre de réalisateurs nés après le 01/01/50 nombre réalisateurs 2 C ) Requête avec regroupement - Nombre de rôles par film. Num-film nombre de rôles 1 2 2 2 3 2 4 3 5 2 6 3 7 2 Audouin Jack cinémathèque page 13/30 AUTRES TYPES DE REQUÊTES 1) REQUETES ACTIONS Les requêtes actions agissent directement sur les tables, alors que les requêtes sélections que nous avons étudié jusqu'ici n'affichent qu'un résultat issu des dites tables. Avec une requête action on peut : - Créer une table à partir d'une requête sélection - Mettre à jour une table à partir d'une requête - Rajouter des enregistrements dans une table -Supprimer des enregistrements dans une ou plusieurs tables Exemple 1 : Création d'une nouvelle table(voir cas cinémathéque ) On veut créer une nouvelle table qui s'appellera "films récents " à partir de la table films. Elle ne comportera que les films qui ont été réalisés à partir du 01/01/90 Mode opératoire : - Créer une requête sélection normalement - Créer + Requête création de table, dans le ruban choisir « Création de table » à la place de « sélection » - Donner le nom de la nouvelle table (attention de ne pas lui donner le même nom que la requete)+ ok - Exécuter la requête. Une nouvelle table " Films récents "est créée avec sa propre structure. Num-film titre date fin tournage Num-realisateur 1 Horreur 10/10/90 2 11 Truc et machin en vacances 12/12/92 4 12 Bonjour les dégâts 13/01/93 4 Audouin Jack Cinémathèque Page 14/30/ Num-film titre date fin tournage Num-realisateur 13 Grève ou marche 14/01/94 3 14 Le sanglot des phoques 15/08/91 3 15 Les phoques et les mouettes 31/12/98 2 Audouin Jack Cinémathèque Page 15/30/ Exemple 2 : Mettre à jour une table Le titre du film "horreur" s'appelle en réalité "horreur en ville" Num-film titre date fin tournage Num-realisateur 1 horreur en ville 10/10/90 2 2 Terreur 01/01/81 1 3 Sueur 02/02/82 1 4 Angoisse 03/03/83 1 5 Blanche Neige 04/04/84 6 6 Les aventures 05/05/85 2 7 Les blagues du régiment 06/06/86 5 8 La découverte 07/07/87 4 9 Encore un effort 08/08/88 6 10 C'est bientôt fini 09/09/89 4 11 Truc et machin en vacances 12/12/92 4 12 Bonjour les dégâts 13/01/93 4 13 Grève ou marche 14/01/94 3 14 Le sanglot des phoques 15/08/91 3 15 Les phoques et les mouettes 31/12/98 2 Audouin Jack Cinémathèque Page 16/30/ Exemple 3 : Rajouts de lignes Dans la table Films récents, nous voulons rajouter les films tournés entre le 01/01/85 et le 31/12/89 Num-film titre date fin tournage Num-realisateur 1 Horreur 10/10/90 2 11 Truc et machin en vacances 12/12/92 4 12 Bonjour les dégâts 13/01/93 4 13 Grève ou marche 14/01/94 3 14 Le sanglot des phoques 15/08/91 3 15 Les phoques et les mouettes 31/12/98 2 6 Les aventures 05/05/85 2 7 Les blagues du régiment 06/06/86 5 8 La découverte 07/07/87 4 9 Encore un effort 08/08/88 6 10 C'est bientôt fini 09/09/89 4 6 Les aventures 05/05/85 2 7 Les blagues du régiment 06/06/86 5 8 La découverte 07/07/87 4 9 Encore un effort 08/08/88 6 10 C'est bientôt fini 09/09/89 4 Audouin Jack Cinémathèque Page 17/30/ Exemple 4 : Suppression Dans la table films récents, nous voulons supprimer le film n° 13 Num-film titre date fin tournage Num-realisateur 1 Horreur 10/10/90 2 11 Truc et machin en vacances 12/12/92 4 14 Le sanglot des phoques 15/08/91 3 15 Les phoques et les mouettes 31/12/98 2 6 Les aventures 05/05/85 2 7 Les blagues du régiment 06/06/86 5 9 Encore un effort 08/08/88 6 10 C'est bientôt fini 09/09/89 4 6 Les aventures 05/05/85 2 7 Les blagues du régiment 06/06/86 5 9 Encore un effort 08/08/88 6 10 C'est bientôt fini 09/09/89 4 Audouin Jack Cinémathèque Page 18/30/ 2) REQUÊTE ANALYSE CROISEE La requête analyse croisée permet de présenter le résultat d’une requête sous la forme d’un tableau à doubles entrées. Supposons que nous voulions présenter le nombre de rôles par acteur et par réalisateur sous la forme d’un tableau : - Créer +création de requête - Afficher les tables concernées (ici acteurs film, réalisateur et contrats) Choisir « analyse croisé croisée » - Indiquer les champs devant figurer en en – tête de ligne et de colonne - Dans opération, préciser regroupement - Indiquer le champ devant figurer dans le tableau et son type de calcul (compte, somme, etc…). Dans la case Analyse, choisir valeur - Lancer la requête Audouin Jack Cinémathèque Page 19/30/ Audouin Jack Cinémathèque Page 20/30/ LES ETATS Les états permettent de présenter des documents imprimables sous un aspect agréable et professionnel. I) Etats en mode instantané - Sélectionnez une table ou une requete - Onglet Créer + clic sur la commande « état » Exemple : Réalisez un état à partir de la table films. Audouin Jack Cinémathèque Page 21/30/ II) états en mode assistant - Onglet Créer + clic sur la commande « assistant état » - Choisir la table ou la requête source + ok - Sélectionner les champs concernés + suivant - Choisir le type de regroupement. Dans option de regroupement, choisir "normal " - Choisir le ou les critères de tri. A ce niveau, on peut également choisir les options de synthèse pour certains champs ( somme, moyenne, Max, min, etc.. ) - Choisir le type de présentation, le style + suivant - Donner un nom à l'état + terminer Exemple : état à partir de la table films Audouin Jack Cinémathèque Page 22/30/ Audouin Jack Cinémathèque Page 23/30/ LES ETIQUETTES Les Etiquettes à coller sur les enveloppes ou tout autre type de documents peuvent être réalisés à partir du menu Etat. Nous voulons par exemple créer des étiquettes indiquant les noms, prénoms et dates de naissances réalisateurs : Etape 1 : Choisir la table concernée et l’assistant étiquette Etape 2 : Indiquer la mise en forma du texte Etape 3 : Insérer vos champs Etape 4 : Triez si vous le souhaitez Audouin Jack Cinémathèque Page 24/30/ Résultat : Audouin Jack Cinémathèque Page 25/30/ EXPORTATION D’ACCESS VERS D’AUTRES LOGICIELS MICROSOFT 2) ACCESS VERS EXCEL ou WORD Données Externes + Excel ou + Fichier texte PUBLIPOSTAGE ENTRE ACCESS ET MS WORD - Sélectionnez une table ou une requête et donnés externe + fusion avec Word Suivez l’assistant Exemple : Envoyez la lettre suivante à tous les réalisateurs : Cher Monsieur (nom prénom ) Le cinéma est un lieu incontournable pour chacun d’entre nous aujourd’hui. Grâce à vous, nous avons cultivé nos rêves et oublié nos soucis. Cher Monsieur (nom), vous avez vu le jour le (date de naissance), Vous recevrez à chacun de vos prochains anniversaires un bouquet de roses rouges. Les cinéphiles anonymes Audouin Jack Cinémathèque Page 26/30/ FORMULAIRES ET ETATS PERSONNALISES LES OUTILS DE CONSTRUCTION FORMULAIRE Audouin Jack Cinémathèque Page 27/30/ Création formulaire principal et sous formulaire à l’aide de l’assistant Ordre dans lequel les champs doivent être sélectionnés (de 1 à 10) à laide de l’assistant et, dans l’image du bas, le résultat obtenu. L’ordre dans lequel vous sélectionnez les champs est très important : Il faut commencer par les champs du formulaire principal, sélectionner tous les champs voulus et suivre les relations sans jamais prendre deux fois le même champ. Par exemple dans l’exemple suivant : Audouin Jack Cinémathèque Page 28/30/ Il faut prendre tous les champs de la table « Film », Puis si on le souhaite, le nom et le prénom du réalisateur (attention de ne pas prendre le numéro de réalisateur puisque nous avons déjà celui de la table « film »), Choisissez ensuite, mais lors du même assistant les champs du sous formulaire : Dans la table « jouer » le numéro acteur et le rôle (attention de ne pas prendre le numéro de film puisque nous avons déjà celui de la table « film »), Pour finir, sélectionnez les champs nom acteur et prénom acteur (attention de ne pas prendre le numéro d’acteur puisque nous avons déjà celui de la table « Jouer »). A réaliser Simple FORMULAIRE PRINCIPAL Acteurs Films Réalisateur Acteur SOUS FORMULAIRE rôle Acteur et rôle (exemple page précédente) Film, titre et date fin tournage Films et rôles Plus difficile Audouin Jack Cinémathèque Page 29/30/ FORMULAIRE PRINCIPAL Réalisateur PREMIER SOUS FORMULAIRE Films DEUXIEME SOUS FORMULAIRE Acteur Faire Réalisateur+Films, puis Réalisateur+Acteur. Insérer le sous-formulaire de l’un des deux dans le formulaire principal de l’autre (par exemple, insérer le sous formulaire acteur a coté du sous formulaire film dans le formulaire principal Réalisateur, (bouton sous formulaire/sous état de la boite à outils) FORMULAIRE PRINCIPAL Réalisateur SOUS FORMULAIRE Films SOUS FORMULAIRE (Sous formulaire du sous formulaire) Acteur Faire Films+Acteur, puis l’insérer. (Bouton sous formulaire/sous état de la boite à outils) en tant que sous formulaire dans un formulaire principal Réalisateur simple (champs père : Réalisateur.num-realisateur ; champs fils : Films.num-realisateur). Audouin Jack Cinémathèque Page 30/30/