Remerciez-le!

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

L3 MIAGE – 2015/2016 Projet de Bases de Donn´ees septembre 2015 Auto-´ecole Ave

L3 MIAGE – 2015/2016 Projet de Bases de Donn´ees septembre 2015 Auto-´ecole Avertissement : Ce projet intervient en grande partie dans la note de TP. Cette note compte pour les 2 sessions. Un projet non rendu p´enalise donc fortement votre note de premi`ere et de seconde session. 1 Travail `a r´ealiser Cette section peut ˆetre amend´ee par votre enseignante de TD. Vous devez terminer ce travail par binˆome pour le 20 novembre. 1. rendre la premi`ere partie pour le vendredi 16 octobre 19h via l’interface PROF : rendre un script SQL avec tous les ordres de cr´eation des tables. 2. Vous rendrez avant le vendredi 20 novembre 19h via l’interface PROF un fichier contenant le code SQL r´epondant ` a chaque question (cr´eation du sch´ema : tables, triggers, pro- grammes, vues), ainsi que le script permettant de tester (insertion/suppression/modification de donn´ees, appels de proc´edures, ...), en pr´ecisant bien quel compte Oracle vous avez utilis´e. 2 Sujet Une auto-´ecole veut s’informatiser afin de g´erer plus facilement les ´el`eves inscrits, les le¸cons de conduite, le suivi de la conduite accompagn´ee, et le passage des examens. On ne s’int´eressera qu’au permis B, permettant de conduire une voiture. Quand un ´el`eve arrive ` a l’auto-´ecole pour la premi`ere fois, on ouvre un dossier et il commence par apprendre le code. Une fois qu’il a obtenu le code, on m´emorise la date d’obtention du code et l’´el`eve peut apprendre ` a conduire. S’offrent `a lui 2 possibilit´es : 1. L’apprentissage ”classique” de la conduite, avec des le¸ cons prises sous le contrˆ ole d’un moni- teur. Dans ce cas l’´el`eve doit avoir plus de 18 ans. 2. La conduite accompagn´ee, qui combine des le¸cons prises avec un moniteur et de la conduite sous contrˆole d’un titulaire du permis B. Il existe 2 formes de conduite accompagn´ee : l’apprentissage anticip´e de la conduite, pour les plus de 15 ans, et la conduite supervis´ee pour les plus de 18 ans. Pour apprendre ` a conduire, un ´el`eve va devoir s’inscrire en choisissant le type d’apprentissage (”classique”, anticip´e ou supervis´e). Une fois qu’il sera apte ` a passer son permis, on lui fixera une date de passage de l’examen, puis une fois qu’il aura pass´e l’examen, on en m´emorisera le r´esultat. S’il a rat´e son permis, il devra reprendre une inscription, donc choisir `a nouveau un type d’apprentissage, etc ... S’il rate 5 fois l’examen de la conduite, l’´el`eve doit repasser le code avant de pouvoir `a nouveau passer le permis. 2 L3 MIAGE – 2015/2016 : Projet de Bases de Donn´ees Voici le Mod`ele Conceptuel des Donn´ees (MCD) imagin´e pour cette base de donn´ees : Informations compl´ementaires : • TRAJET : un ´el`eve effectue un trajet `a une certaine date, en parcourant un certain nombre de kilom`etres sur un type de route (route, ville, autoroute). Pour am´eliorer la pr´ecision, l_nb_km sera exprim´e avec un 1 chiffre apr`es la virgule. • ELEVE : e_date_code est la date d’obtention du code de la route, e_date_dossier est la date d’ouverture du dossier ` a l’auto-´ecole. Lorsqu’un ´el`eve ouvre un dossier il n’a en g´en´eral pas le code. • LECON : un ´el`eve suit une le¸con de conduite avec un moniteur, ` a une certaine date. La dur´ee de la le¸con est exprim´ee en heures (ce n’est pas toujours un nombre entier, si on autorise des le¸cons d’une heure trente par exemple). • INSCRIPTION : un ´el`eve prend une inscription `a l’apprentissage de la conduite. cela sup- pose qu’il a d´ej`a obtenu le code. Cette inscription est prise en vue de passer l’examen de la conduite (date inconnue au moment o` u l’´el`eve s’inscrit). Le type d’inscription varie selon la m´ethode d’apprentissage : classique, supervis´e ou anticip´e (cf description du sujet). i_num permet de m´emoriser le nombre d’inscriptions donc de passage de l’examen du permis (i_num = 1 pour une premi`ere inscription). On peut remarquer qu’il n’y a pas plus d’une ligne avec i_date_d’examen ou i_resultat ` a null pour un ´el`eve donn´e. 2.1 Partie `a rendre pour le vendredi 16 octobre Tables Question 2.1 : Cr´eer les tables en pensant aux contraintes d’int´egrit´e : tout ce qui sera d´efini dans le sch´ema ne sera plus `a v´erifier par les programmes stock´es. Auto-´ecole 3 2.2 Partie `a rendre pour le vendredi 20 novembre Par la suite, vous utiliserez des s´equences pour g´en´erer les cl´es primaires. Triggers Question 2.2 : Cr´eer un trigger qui contrˆ ole certaines donn´ees de la table ELEVE : • on souhaite que les nom et pr´enom soient toujours en majuscule • on souhaite que la date de cr´eation du dossier soit toujours renseign´ee, en choisissant ´eventuellement la date courante si aucune date n’est fournie `a l’insertion, ou en conservant l’ancienne date si elle est remplac´ee par ”null” lors d’une mise `a jour. • on utilise une s´equence pour g´en´erer la cl´e primaire. On ne veut pas que cette cl´e primaire puisse ˆetre modifi´ee une fois qu’elle est affect´ee `a un ´el`eve. Question 2.3 : Ajouter/modifier des ´el`eves pour v´erifier le bon fonctionnement du trigger. Pour avoir le droit de passer le permis, un ´el`eve doit comptabiliser un certain nombre d’heures de le¸cons et un certain nombre de kilom`etres s’il est en conduite accompagn´ee. Pour ´eviter de refaire toujours les mˆemes calculs, on d´ecide de stocker l’information dans la table ELEVE. Question 2.4 : A l’aide de l’instruction SQL ALTER TABLE, ajouter 2 colonnes `a la table ELEVE : une colonne e_nb_km qui donne le nombre total de kilom`etres parcourus en conduite accompagn´ee, et une colonne e_nbh_lecons qui donne le nombre total d’heures de le¸cons de conduite. Question 2.5 : Si on avait d´ej` a ins´er´e des donn´ees dans les tables LECON et TRAJET, que faudrait-il poser comme requˆete SQL (update sur ELEVE) pour que les 2 colonnes e_nb_km et e_nbh_lecons contiennent les bonnes donn´ees ? Question 2.6 : Ecrire un trigger sur TRAJET et un trigger sur LECON afin de g´erer la redondance d’information introduite par l’ajout de ces deux colonnes dans la table ELEVE. On n’utilisera pas les requˆetes de la question pr´ec´edente, parce que ce serait trop coˆ uteux de recalculer ` a chaque fois le nombre de kilom`etres et le nombre d’heures de le¸ cons d’un (ou de tous les) ´el`eve(s). Question 2.7 : Tester les triggers pr´ec´edents en faisant ´evoluer les instances de TRAJET et LECON (en ayant au pr´ealable ajout´e des moniteurs). Paquetage PL/SQL On d´ecide d’´ecrire un package stock´e pour g´erer les inscriptions `a la conduite, et les passages de l’´epreuve pratique du permis de conduire. Voici la sp´ecification de ce package : create or replace package paq_conduite as -- inscription `a des cours de conduite en vue de passer le permis procedure inscrire_conduite(id_eleve eleve.e_id%type, la_date_inscr inscription.i_date_inscr%type := sysdate, le_type inscription.i_type%type ); 4 L3 MIAGE – 2015/2016 : Projet de Bases de Donn´ees -- inscription `a l’examen du permis d’un ´el`eve qui suit d´ej`a des le¸cons procedure inscrire_examen(id_eleve eleve.e_id%type, la_date_examen inscription.i_date_exam%type ); -- on met `a jour les r´esultats de l’examen du permis procedure resultat_examen(id_eleve eleve.e_id%type, le_resultat inscription.i_resultat%type ); ELEVE_INCONNU Exception ; pragma exception_init(ELEVE_INCONNU, -20099); PAS_DE_CODE Exception ; pragma exception_init(PAS_DE_CODE, -20100); PB_AGE Exception ; pragma exception_init(PB_AGE, -20101); PB_CONDITION Exception ; pragma exception_init(PB_CONDITION, -20102); DATE_DEJA_PRISE Exception ; pragma exception_init(PB_CONDITION, -20103); end paq_conduite; Question 2.8 : Ecrire et tester la proc´edure inscrire_conduite qui permet d’inscrire un ´el`eve `a l’apprentissage de la conduite, en vue de passer l’examen. Cette proc´edure : • d´eclenche l’exception ELEVE_INCONNU si l’identifiant id_eleve pass´e en param`etre est incor- rect. • v´erifie les conditions d’ˆ age en fonction du type d’inscription (pour l’inscription classique et l’apprentissage supervis´e, il faut avoir plus de 18 ans, pour l’apprentissage anticip´e, il faut avoir plus de 15 ans). Si l’ˆ age n’est pas conforme, la proc´edure d´eclenche PB_AGE. • v´erifie que l’´el`eve a le code, c’est `a dire que la date de l’examen du code est bien renseign´e. Sinon, la proc´edure d´eclenche PAS_DE_CODE. • v´erifie que l’´el`eve n’est pas d´ej`a inscrit en vue de passer l’examen (auquel cas on ne fait rien). • ins`ere une ligne dans la table INSCRIPTION, en ayant donc calcul´e le nombre total d’inscriptions de cet ´el`eve.A cette ´etape, les champs i_date_exam et i_resultat ne sont pas renseign´es. Question 2.9 : Ecrire et tester la proc´edure inscrire_examen qui permet d’inscrire un ´el`eve `a l’examen. Cette proc´edure : • d´eclenche l’exception ELEVE_INCONNU si l’identifiant id_eleve pass´e en param`etre est incor- rect. • v´erifie que l’´el`eve aura 18 ans `a la date de passage de l’examen, sinon d´eclenche PB_AGE • v´erifie que l’´el`eve satisfait les conditions concernant l’apprentissage : – Il a fait au moins 20 heures de le¸ cons (donc il est inscrit `a la conduite) – il a fait au moins 1000 km dans le cas d’apprentissage supervis´e, et au moins 3000 km dans le cas d’apprentissage anticip´e. sinon, d´eclenche PB_CONDITION • modifie la ligne de la table INSCRIPTION qui correspond `a cet ´el`eve et qui a une date d’examen ` a null, afin de renseigner cette date de passage de l’examen. Si une telle ligne n’existe pas, plus pr´ecis´ement uploads/S4/ projet-de-bases-de-donn-ees-auto-ecole-l3-miage-2015-2016.pdf