Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

M1 MIASHS BD avanc´ ees BD relationnelles – Agence de voyages Gestion des voyag

M1 MIASHS BD avanc´ ees BD relationnelles – Agence de voyages Gestion des voyages et des inscriptions Un organisme de voyages sp´ ecialis´ e dans les voyages ` a l’´ etranger a informatis´ e les inscrip- tions. L’organisme propose ` a ses clients des circuits r´ epertori´ es dans des catalogues (table CA- TALOGUES). Chaque circuit poss` ede une ville de d´ epart (en g´ en´ eral, en France, table VILLES), une premi` ere destination (la premi` ere ville du circuit, table DESTINATIONS), un intitul´ e, une dur´ ee et plusieurs lieux de passage (table PASSAGES). Chaque circuit est propos´ e ` a diﬀ´ erentes p´ eriodes de l’ann´ ee. Les prix d´ ependent du circuit et de la p´ eriode. La dur´ ee d’un circuit est ﬁxe. Un voyage est la r´ ealisation d’un circuit ` a une certaine date. Chaque voyage est accompagn´ e par un animateur. Une inscription est eﬀectu´ ee pour chaque participant ` a un voyage. Un responsable de l’inscription est associ´ e ` a chaque inscription (par exemple, une personne inscrivant plusieurs membres d’une mˆ eme famille). Chaque inscription est associ´ ee ` a un vendeur. Les clients peuvent avoir un pourcentage de r´ eduction li´ ee ` a une adh´ esion ` a un partenaire de l’organisme ou ` a sa par- ticipation ` a d’autres voyages. Le montant ` a payer est calcul´ e en fonction du montant du voyage et de cette r´ eduction. Pour cette gestion, le mod` ele relationnel suivant a ´ et´ e d´ eﬁni (les cl´ es primaires sont en gras, les cl´ es ´ etrang` eres en italique, elles sont de type INTEGER) : – CATALOGUES(numcatalogue, dated´ ebutvalidit´ e, dateﬁnvalidit´ e, nomvoyagiste) – CIRCUITS(numcircuit, intitul´ e, dur´ ee, refvilledepart, refpremdestination, refcatalogue) – VILLES(numville, ville, pays) – DESTINATIONS(numdestination, nomdestination, pays, continent) – PASSAGES(refcircuit, refdestination) – VOYAGES(numvoyage, dated´ epart, refp´ eriode, refcircuit, refanimateur) – TARIFS(refcircuit, refp´ eriode, prix) – ANIMATEURS(numanimateur, nom, pr´ enom) – PERIODES(nump´ eriode, dated´ ebut, dateﬁn) – INSCRIPTIONS(numinscription, dateinscription, montantdu, acomptevers´ e, datepaiement- solde, montantsolde, refvoyage, refvendeur, refclientparticipant, refclientresponsableinscrip- tion) – VENDEURS(numvendeur, nom, pr´ enom, postetel, m` el) – CLIENTS(numclient, r´ eduction, nom, pr´ enom, adresse, codepostal, ville, r´ egion, pays, tel, fax, m` el) Q1 Donner un mod` ele conceptuel des donn´ ees (entit´ e-association) compatible avec ce mod` ele logique. Q2 le mod` ele permet-il d’avoir des p´ eriodes diﬀ´ erentes selon les circuits pour leur tariﬁcation ? Q3 Le mod` ele permet-il de g´ erer les r´ eservations ? Les annulations ? Q4 Expliquer pourquoi le montant ` a payer ne devrait pas ﬁgurer dans le mod` ele relationnel. Pourquoi est-il ajout´ e ? Voyez-vous un autre champ redondant ? Q5 Donner les contraintes d’int´ egrit´ e li´ ees ` a la table INSCRIPTIONS, en particulier les contraintes de r´ ef´ erence et l’autorisation de la valeur NULL et les contraintes li´ ees ` a la d´ enormalisation. Q6 ´ Ecrire une requˆ ete SQL qui aﬃche les num´ eros de voyage, intitul´ e, date de d´ epart et date de retour (la dur´ ee d’un voyage est celle du circuit correspondant) avec un d´ epart en janvier 2014. Q7 Mˆ eme question en ajoutant le nombre d’inscrits par voyage (donner les modiﬁcations ` a eﬀectuer). Q8 Mˆ eme question en se limitant aux voyages avec moins de 4 inscrits dont le d´ epart du voyage ne pourrait ˆ etre assur´ e (donner les modiﬁcations ` a eﬀectuer). Q9 Dire si les deux requˆ etes sont ´ equivalentes. Si oui dire celle qui vous semble la plus eﬃcace en fonction des index existants. 1 SELECT V.numvoyage, V.datedepart, Ci.intitul´ e FROM (((VOYAGES V JOIN CIRCUITS Ci ON V.refcircuit=Ci.numcircuit) JOIN INSCRIPTIONS I ON I.refvoyage=V.numvoyage) JOIN CLIENTS Cl ON I.refclientparticipant=Cl.numclient) WHERE Cl.pays <>"France" AND YEAR(V.datedepart)=2013 AND MONTH(V.datedepart)=8 SELECT V.numvoyage, V.datedepart, Ci.intitul´ e FROM ((VOYAGES V JOIN CIRCUITS Ci ON V.refcircuit=Ci.numcircuit) WHERE V.numvoyage IN (SELECT VOYAGES.numvoyage FROM ((VOYAGES JOIN INSCRIPTIONS ON INSCRIPTIONS.refvoyage=VOYAGES.numvoyage) JOIN CLIENTS ON INSCRIPTIONS.refclientparticipant=CLIENTS.numclient) WHERE YEAR(VOYAGES.datedepart)=2013 AND MONTH(VOYAGES.datedepart)=8 AND CLIENTS.pays <>"France") Q10 Trouver (au moins) deux fa¸ cons d’´ ecrire la requˆ ete suivante : aﬃcher les voyages partant en d´ ecembre 2013 pour lesquels il n’y a pas d’inscrit. Am´ eliorer le service et les ventes Description du domaine L’agence souhaite am´ eliorer ses performances et ses relations client. Pour cela, elle souhaite cibler ses envois de catalogue en fonction des proﬁls de client, elle souhaite ´ etoﬀer son oﬀre vers les destinations les plus recherch´ ees, elle souhaite proposer ses voyages aux meilleures p´ eriodes. Pour cela, il faut pouvoir facilement ´ etudier les ventes ` a partir d’un historique, c’est-` a-dire pouvoir produire des documents faciles ` a lire, permettant d’analyser les r´ esultats. Il faut, en particulier, pouvoir produire des tableaux crois´ es et des graphiques permettant de visualiser les r´ esultats selon diﬀ´ erentes dimensions : la destination, la p´ eriode, le prix, le voyagiste, et autres. Mod` ele d´ ecisionnel Le mod` ele relationnel pr´ ec´ edent n’´ etant pas adapt´ e, nous allons proposer une autre organisation des donn´ ees ` a l’aide d’un mod` ele en ´ etoile. Nous choisissons de prendre comme grain le voyage d’un client. Il y aura un seul fait qui est le prix du voyage. Les dimensions seront : le temps, le lieu, le client, le vendeur et le type de voyage. On d´ ecide que tout voyage correspond ` a une destination principale (tr` es souvent la premi` ere destination) et que tout voyage correspond ` a une date qui est le jour de d´ epart. Toutes les autres informations seront dans la dimension VOYAGES, par exemple, s´ ejour ou circuit, week-end ou court ou long, ... Une version pr´ eliminaire du mod` ele est : – FAITVOYAGE(refjour, reﬂieu, refclient, refvendeur, refvoyage, prixvoyage) – JOURS(numjour, nomjour, d´ ebutWE ?, d´ ebutvacances ?, numsemaine, nummois, nommois, numtrimestre, numsemestre, saison, ann´ ee) – LIEUX(numlieu, ` a d´ eﬁnir) – CLIENTS(numclient, nom, pr´ enom, adresse, codepostal, ville, d´ epartement, r´ egion, pays, ...) – VENDEURS(numvendeur, nomvendeur, pr´ enomvendeur) – VOYAGES(numvoyage, intitul´ e, dur´ ee, numcatalogue, dated´ ebutvalidit´ e, dateﬁnvalidit´ e, nomvoyagiste, typevoyage, niveauconfort, gammeprix, ...) Donner la cl´ e primaire de la table FAITVOYAGE. Proposer une dimension LIEUX sachant que les analyses pourront se faire par pays, par continent, par r´ egion du monde (Moyen Orient, Europe du Nord est, ...), par type de climat. Quelles sont les informations sur les clients qu’il serait int´ eressant de pouvoir r´ ecup´ erer pour les analyses de l’agence ? Faire une repr´ esentation graphique du mod` ele d´ ecisionnel. Que voyez-vous comme diﬀ´ erences principales avec le mod` ele relationnel ? Calculer le total des ventes pour les voyages en Chine au premier trimestre 2013. Mˆ eme question avec le total des ventes pour chacun des mois de 2012 aupr` es du voyagiste “anciennes fronti` eres”. ´ Ecrire une requˆ ete qui aﬃche le total des ventes de l’agence par voyagiste pour les voyages de janvier 2012, de janvier 2013, et le pourcentage d’´ evolution entre les deux p´ eriodes 2 uploads/Voyage/ controlebdrel-pdf.pdf