www.annales-exam.com Le site n°1 des annales ! 1           

www.annales-exam.com Le site n°1 des annales ! 1                                         Formation ouverte à distance NFP – 107 - IDF Note : / 20  ° Modélisation conceptuelle, Algèbre relationnelle, requêtes SQL & optimisation 4 pages Il est fortement conseillé de lire l’ensemble du sujet avant de commencer votre rédaction. N.B. : Dans tout le devoir le symbole de l’opérateur de jointure utilisé sera le suivant : ×Prédicat . Exercice n°1 Une entreprise de location de véhicules loue trois catégories d’automobiles : A, B ou C à ses clients qui sont soit des individus, soit des organisations. Le montant de la location est calculé de la façon suivante : • Chaque jour de location est facturé en fonction de la catégorie (tarif journalier de la catégorie) ; • Chaque kilomètre parcouru est facturé en fonction de la catégorie (prix km de la catégorie) ; • Chaque jour de location contient un forfait kilométrique dépendant de la catégorie (forfait kilométrique) ; • Chaque client possède un pourcentage de remise qui est appliqué à l’ensemble du montant de la location (remise client). Le pourcentage de remise au client est négocié chaque année avec les clients importants sur la base de leur chiffre d’affaires et s’applique à toutes leurs locations de l’année à venir. La base de données qui sera constituée doit permettre en particulier les manipulations suivantes : • Affichage de la liste des voitures disponibles selon le kilométrage, la marque, l’immatriculation, le modèle et la catégorie ; • Recherche de l’évolution annuelle des tarifs (tarif journalier, prix au km) ; • Liste des locations d’un client ordonnée chronologiquement et donnant pour chaque location, la date de début, la date de fin, le kilométrage parcouru, le modèle, la marque et la catégorie du véhicule. La recherche du client se fait à partir du nom et du numéro de téléphone. Annales-exam.com 2 Le schéma relationnel obtenu est le suivant : CLIENT (code_client, nom, adresse, téléphone, remise, type_client) VEHICULE (immatriculation, marque, modèle, kilométrage, #code_catégorie) CATEGORIE (code_catégorie) ANNEE (année) TARIF (code_categorie, année, prix_km, tarif_journalier, forfait) LOCATION (code_client, immatriculation, date_debloc, date_fin, km_parcourus, montant) 1°) Déterminer le schéma entité-relation correspondant au schéma relationnel ci-dessus. 2°) Ecrire la requête SQL permettant de déterminer tous les clients qui en une année ont parcouru plus de 30 000 km en utilisant un véhicule de catégorie B. 3°) Ecrire la requête SQL permettant de déterminer tous les véhicules de plus de 60 000 km classés par catégorie et par ordre alphabétique sur la marque. Exercice n°2 Vous gérez une équipe de vendeurs. Vous souhaitez étudier de plus près les notes de frais établies par leurs soins lors de leurs tournées à l’aide des tables suivantes : VENDEUR (code_vendeur, nom_vendeur, région) NOTE_DE_FRAIS (code_note, code_vendeur, date_note, montant_note, nature) L’attribut nature précise le type de dépense effectuée, par exemple, repas, carburant, fournitures de bureau, etc…. Un vendeur établit une note de frais pour chaque type de dépenses et pour chaque jour. Certains vendeurs ne sont pas affectés à une seule région. Dans ce cas, aucune région n’est indiquée pour eux. 1°) Déterminer la suite d’opérations algébriques permettant de trouver les noms de tous les vendeurs. 2°) En déduire la requête SQL correspondante. 3°) Déterminer la suite d’opérations algébriques permettant d’établir la liste des vendeurs de la région Aquitaine. 4°) En déduire la requête SQL correspondante. 5°) Déterminer la suite d’opérations algébriques permettant d’établir la liste des vendeurs ayant au moins une note de frais d’un montant supérieur à 1000€. Annales-exam.com 3 6 °) Ecrire la requête SQL permettant de déterminer les noms des vendeurs dont la somme des dépenses dépasse 3000 €. 7°) Ecrire la requête SQL permettant de déterminer le nom du vendeur ayant présenté la note de frais la plus élevée. 8°) Déterminer le résultat produit par la requête suivante (c'est-à-dire à quelle question répond t- elle ?). SELECT région FROM vendeur, note_de_frais WHERE note_de_frais.code_vendeur = vendeur.code_vendeur GROUP BY région HAVING SUM (montant_note) >= ALL ( SELECT SUM(montant_note) FROM vendeur, note_de_frais WHERE note_de_frais.code_vendeur = vendeur.code_vendeur GROUP BY région); Exercice n°3 : optimisation de requête EMPLOYE Idemployé Nom Ville Affectation 19 SILBERMANN Rungis D6 1 MIGEON Fontenay D3 7 HUMMER Brétigny D5 4 BRIQUET Fontenay D6 DEPARTEMENT Iddépartement Description D5 Personnel D3 Informatique D6 Finances 1°) A partir des tables EMPLOYE et DEPARTEMENT déterminer la requête permettant d’obtenir une liste des villes où habitent les employés du département informatique. 2°) Une suite possible d'opérations algébriques équivalente à la requête SQL précédente s'écrit : TABLE_Liste = πVille(σDescription=Informatique(EMPLOYE |×|Affectation=Iddépartement DEPARTEMENT)) Déterminer l’arbre d’interrogation correspondant cette suite algébrique. Annales-exam.com 4 3°) Une autre suite possible d'opérations algébriques équivalente à la requête SQL précédente s'écrit : TABLE_Liste = πVille (πAffectation,Ville(EMPLOYE) |×|Affectation=Iddépartement πIddépartement(σDescription=Informatique(DEPARTEMENT)) ) Déterminer l’arbre d’interrogation correspondant cette suite algébrique. 4°) En quoi cette dernière suite est-elle optimisée par rapport à la première (question 2). Annales-exam.com uploads/Geographie/ nfp107-annale-2006-2007-s1-fod-idf.pdf

Tags
Administrationdéterminer requête chaque permettant suite
Documents similaires
  • 9
  • 0
  • 0
Afficher les détails des licences
Licence et utilisation
Gratuit pour un usage personnel Attribution requise
Partager