Université Lumière Lyon 2, UFR Sciences Économiques et Gestion M1 Économie & Société – M1 Finance – Année 2015-2016 Informatique – Bases de données : Épreuve sur machine (1re session) – SUJET 1 J. Darmont (http://eric.univ-lyon2.fr/~jdarmont), 08/01/2016 4. Compléter la table PLANTE en établissant le lien de chaque plante avec sa famille, sachant que : • les plantes d’identifiant 52 à 55 sont des onagracées ; • les plantes d’identifiant 57 à 59 et 64 sont des crassulacées ; • les plantes d’identifiant 60 à 63 sont des apiacées. 5. Définir toutes les relations entre les tables de la base de données. Notes manuscrites autorisées – Durée : 1h30 – Barème fourni à titre indicatif Formulaire (1 point) Base de données Les éditeurs d’un nouveau site web souhaitent mettre en ligne une référence nationale relative aux plantes, qui est actuellement stockée dans une base de données Access. Cette base de données herbier1.accdb vous est fournie, incomplète, à l’adresse suivante. Elle est protégée par le mot de passe « chanvre161 ». Concevoir un ensemble formulaire/sous-formulaire permettant d’afficher, pour chaque milieu, les plantes qui y poussent. Le résultat devra avoir l’apparence ci-dessous à l’exécution. Nommer le formulaire maître Plantes par milieu. http://eric.univ-lyon2.fr/accdb/herbier1.accdb Le schéma relationnel de la base de données est le suivant. FAMILLE (id_famille, libellé) MILIEU (id_milieu, libellé) PLANTE (id_plante, nomLatin, nomCommun, altitudeRécolte, dateRécolte, sols, id_milieu#, lieu, protégé, médicinal, aromatique, remarques, id_famille#) OUVRAGE (id_ouvrage, année, titre, auteur, éditeur, pages) Clés primaires REFERENCER (id_ouvrage#, id_plante#, page) Clés étrangères# Achèvement de la base de données (4 points) 1. Saisir votre numéro d’étudiant, votre nom et votre prénom, le numéro de la salle dans laquelle vous vous trouvez et le numéro de série de l’ordinateur que vous utilisez dans la table _ID. NB : Cette table ne fait pas partie de la base de données. L’ignorer dans le reste du sujet. 2. La table FAMILLE manque dans la base de données. Créer sa structure en se basant sur le dictionnaire de données suivant. Si le libellé de la famille n’est pas valide, prévoir le message d’erreur « Libellé incorrect ». Champ id_famille libellé Type Taille Numérique Octet Texte 30 Requêtes (10 points) NB : Dans la table PLANTE, les champs de type « Oui/Non » protégé, médicinal et aromatique prennent la valeur -1 lorsqu’ils sont cochés (valeur Oui) et 0 lorsqu’ils ne le sont pas (valeur Non). Valide si Se termine par « cées » 3. Peupler la table FAMILLE avec les enregistrements ci-dessous. 1. Liste des plantes, triée par altitude de récolte décroissante (afficher toutes les caractéristiques des plantes). 2. Noms latin et commun des plantes poussant sur sols calcaires. 3. Nom commun, nom latin et libellé de milieu de chaque plante référencée dans un ouvrage (classer par ordre alphabétique de nom commun). 4. Liste des ouvrages qui concernent des plantes protégées et ayant des propriétés médicinales. 5. Nom commun des plantes poussant sur sol sec ou en milieu sec. 6. Nom latin des plantes dont le libellé de milieu est saisi au clavier. 7. Nombre de plantes référencées dans chaque ouvrage (indiquer le titre des ouvrages). 1/3 2/3 8. Altitude moyenne de récolte par milieu. Indiquer le libellé du milieu et classer le résultat de la plus grande à la plus petite altitude moyenne. 9. Pour chaque famille de plante, écart maximum entre la date de récolte et le 1er janvier 2005 (indiquer le libellé de la famille). Rappel : une opération arithmétique sur des dates donne un résultat en jours. 10. Identifiant des plantes référencées dans un ouvrage à une page incorrecte. VBA (5 points) 1. Créer un nouveau module nommé Calculs. 2. Dans l’optique de valoriser la base de données dans le monde anglo-saxon, écrire dans le module Calculs une fonction nommée AltiPieds permettant de convertir une altitude exprimée en mètres en pieds, sachant que 1 mètre = 3,2808 pieds. 3. Dans une requête (requête n° 11), lister toutes les caractéristiques de chaque plante en y ajoutant l’altitude en pieds calculée à l’aide de la fonction AltiPieds. Bien différencier les deux altitudes (en mètres d’une part, en pieds d’autre part). 4. Écrire dans le module Calculs une fonction nommée CalculIndice permettant de calculer l’indice de rareté d’une plante selon les spécifications suivantes : • l’indice de rareté de base d’une plante est égal à son altitude de récolte (en mètres) divisée par 2500 ; • si la plante est aromatique, l’indice de rareté est augmenté de 0,1 ; • si la plante est protégée, l’indice de rareté est augmenté de 0,3 ; • si la plante est médicinale, l’indice de rareté est augmenté de 0,5. 5. Dans une requête (requête n° 12), lister pour chaque plante son nom commun et son indice de rareté calculé à l’aide de la fonction CalculIndice. Trier le résultat par indice de rareté croissant. 3/3