Exercice 2 - Grenoble INP

INPG Institut National Polytechnique de Grenoble ENS Génie Industriel 2ème Année Enseignement de
Management des Systèmes d'Information et réseaux (MSI) Examen : 1ère session 2006
Le 23 janvier 2006 Durée 2 heures Tous documents et notes de cours autorisés Lisez préalablement l'intégralité du sujet.
Précisez soigneusement les hypothèses que vous faites pour utiliser,
interpréter ou compléter les informations du texte.
Michel TOLLENAERE Les trois exercices sont indépendants
[pic] Exercice 1 : Note de frais (5 points) 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. Vous
récupérez les tables suivantes :
VENDEUR (code-vendeur, nom-vendeur, région)
NOTE-DE-FRAlS (code-vendeur, code note, 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.1. À l'aide du langage SQL, vous interrogez cette base de données. Ecrire
les requêtes SQL suivantes : 1. Noms des vendeurs.
2. Liste des vendeurs triés par région.
3. Liste des vendeurs de la région Aquitaine.
4. Les noms des vendeurs ayant au moins une note de frais d'un montant
supérieur à 1 000E.
5. Les noms des vendeurs ayant des notes de frais d'un montant compris
entre 1 000E et 10 000E.
6. Les noms des vendeurs n'ayant pas de dépenses de carburant.
7. Liste des vendeurs dont le nom commence par « Magou ».
8. Liste des notes de frais du jour par vendeur, et pour un même vendeur,
par montant décroissant. 1.2. Expliciter en français les requêtes SQL suivantes : A. SELECT * FROM vendeur WHERE région lS NULL; B. SELECT nom-vendeur FROM vendeur WHERE code-vendeur lN (SELECT code-
vendeur FROM note-de-frais WHERE montant-note >=1000 AND
nature='carburant'); C. SELECT nom-vendeur FROM vendeur WHERE code-vendeur NOT IN (SELECT
code-vendeur FROM note-de-frais WHERE nature='carburant' OR
nature='hôtel');
Exercice 2 : articles - sous ensembles - fournisseur (5 points) Proposez un schéma de base de données pour le modèle statique de classes
suivant :
Exercice 3 : Location de gites (10 points) LOCAGITE est une association qui permet à divers propriétaires ruraux de
mettre en location, à la semaine, des gîtes meublés. Elle publie
annuellement un catalogue contenant les gîtes proposés par les
propriétaires. Les gîtes doivent répondre à un certain nombre de critères
qualité, correspondant à un nombre d'étoiles, qui sont vérifiés lors de
l'adhésion du gîte et une fois tous les trois ans lors d'une visite de
contrôle. Le propriétaire reçoit tous les ans un catalogue des gîtes, et peut
modifier les informations qui le concernent (prix par saison, photo du
gîte, nombre de personnes, de chambres, terrain, etc.). LOCAGITE regroupe 450 gîtes en France, pour une moyenne de 12 semaines de
réservation par gîte et par an. LOCAGITE propose aux propriétaires qui le souhaitent, un service central de
réservation. Tous les ans, les propriétaires qui veulent utiliser ce
service signent un contrat avec LOCAGITE, qui spécifie les périodes
ouvertes à la location et la rémunération de la centrale de réservation en
pourcentage de chaque location, ce dernier taux étant valable pour l'année
et pour l'ensemble des gîtes. Le propriétaire, en signant le contrat, joint
un relevé d'identité bancaire. Le propriétaire ayant signé ce contrat de réservation central reçoit chaque
mois un état des réservations fermes. Il reçoit aussi tous les mois un état
des sommes encaissées par la centrale de réservation. Le virement bancaire
des sommes dues, correspondant à l'état précédent, est envoyé en milieu du
mois suivant. Un client potentiel (que l'on peut appeler client réservataire) téléphone à
la centrale de réservation pour réserver un gîte sur la base du catalogue.
La centrale de réservation prend en compte la demande, et lui envoie un
contrat de location ainsi qu'une demande d'acompte si un accord a été
trouvé sur les dates de réservation. Le client réservataire renvoie le
contrat signé accompagné de l'acompte: la réservation devient ferme. Un
mois avant le séjour, le client locataire envoie le solde du paiement ; il
reçoit alors une confirmation de séjour lui donnant les coordonnées de la
personne à contacter pour convenir de son arrivée. Le client peut à tout
moment annuler son séjour, 30 % des sommes versées ne sont pas remboursées.
En cas de non-retour d'un contrat signé après 15 jours, la pré-réservation
est automatiquement annulée. Principales informations portées par les documents échangés |Catalogue | |
| |Capacité (nb de chambres) |
|Année du catalogue |Description du gîte (texte) |
|N° du gîte |Adresse et tel du propriétaire (pour |
|Nom de la commune |la réservation) ou tél du service de |
|Adresse du gîte |réservation |
|Animaux acceptés (0, N) |Tarifs semaine (HS, juin/sept/Vac |
|NB d'étoiles |Scol) |
|NB de personnes acceptées |Activités disponibles et distance |
| |(ex. : piscine à 3 km). | |Contrat propriétaire |État mensuel des locations |
|N° de contrat propriétaire |N° propriétaire, nom du propriétaire,|
|N° propriétaire |adresse et tel du propriétaire. |
|Nom du propriétaire |Par contrat, par gîte et par |
|Adresse et tel du propriétaire |réservation: |
|Référence du gîte |N° de réservation NB d'adultes |
|Description du gîte (voir ci-dessus) |Date d'arrivée NB d'enfants |
| |Date de départ Animaux (0, N) |
|Tarifs semaine (HS, juin/sept/Vac |NB de nuits Montant reçu |
|Scol) |Nom et adresse du locataire |
|Périodes de location |Montant à recevoir |
Elaborer les diagrammes UML suivants :
Pour des raisons de simplicité et de temps, les diagrammes de classes
seront représentés sans mentionner les attributs des classes. . les use cases correspondant à cette application.
. le diagramme de séquence du cas « Imprimer Catalogue »
. le diagramme de classes (ne représentez pas les attributs des
classes)
. un diagramme d'état transition jugé pertinent.
Eléments de corrigé : SQL 1. SELECT nom-vendeur FROM vendeur; 2. SELECT * FROM vendeur ORDER BY région; 3. SELECT * FROM vendeur WHERE région='Aquitaine'; 4. SELECT * FROM vendeur WHERE région='Aquitaine'; 5. SELECT * FROM vendeur WHERE région IS NULL; 6. SELECT nom-vendeur FROM vendeur WHERE code-vendeur IN (SELECT code-
vendeur FROM note-de-frais WHERE montant-note >=1000); ou bien:
SELECT nom-vendeur FROM vendeur, INNERJOIN note-de-frais ON Vendeurs.Code-
vendeur = Note-de-frais.Code-vendeur WHERE vendeur.code-vendeur=note-de-
frais.code-vendeur AND montant-note >= 1000; 7. SELECT nom-vendeur FROM vendeur WHERE code-vendeur IN (SELECT code-
vendeur FROM note-de-frais WHERE montant-note )>=1000 AND montant-
note= 1000 AND montant-note