ExerciceED Base de donnes - B6 CNAM page 5CONSERVATOIRE NATIONAL DES ARTS ET METIERSFilire Informatique dEntrepriseE DBASES DE DONNEES B6Partie I: Modlisation ConceptuelleExercice I.1On souhaite mettre en place une Base de donnes relationnelle assurant la gestion des commandes clients, ainsi que la gestion de stock des matires premires de l'entreprise Lambda.On dispose des informations suivantes :1- Numro client (NOCLI)2- Raison sociale client (RSCLI)3- Adresse de livraison (ADRLIV)4- Numro commande (NOCDE)5- Date de la commande (DATCDE)6- Numro produit fini (NOPRD) = produit faisant l'objet des commandes clients.7- Libell du produit fini (LIBPRD)8- Code matire premire (CODMP)9- Libell matire premire (LIBMP)10- Quantit en stock matire premire (QTEMP)11- Quantit matire premire ncessaire la fabrication de produits finis (QTFAB)12- Numro fournisseur (NOFRS)13- Raison sociale fournisseur ( RSFRS)14- Adresse fournisseur (ADRFRS)15- Quantit commande par le client pour un produit donn (QTECDE)16- Quantit en stock produit fini (QTEPRD)Les informations ci-aprs sont prendre en compte :-Un client peut passer plusieurs commandes, mais une commande ne concerne qu'un seul client.-Une commande peut regrouper plusieurs produits, mais un produit peut tre rfrenc dans plusieurs commandes.-L'adresse de Livraison est connue au moment de la prise de la commande.-Les produits finis sont fabriqus partir de matires premires. La quantit dune matire premire ncessaire la fabrication dun produit fini est dfinie au niveau du couple Produit fini - Matire premire.-Une matire premire peut tre dlivre par plusieurs fournisseurs. La quantit en stock matire premire est indpendante des fournisseurs-Une commande est mise en attente si une des quantits de produits commands est suprieure celle du produit en stock.Question :Q1) Dterminer et discuter le modle conceptuel de donnes selon les concepts du modle E-R (Entits relations).Exercice I.2Dans le cadre de mise en uvre dune de gestion commerciale, le Concepteur doit dfinir un Modle Conceptuel de Donnes prenant en compte:a) les rgles de gestion suivantes :-Un Client peut passer plusieurs commandes.-Une commande correspond un client et un seul.-Une commande peut avoir plusieurs lignes, chaque ligne faisant partie rfrence un produit diffrent.-Un article peut tre rfrenc dans plusieurs commandes.-Une facture est tablie pour une commande et une seule.-A une commande correspond obligatoirement une et une seule condition de paiement.b) et les besoins applicatifs suivants:Les deux traitements de rfrence pour la construction de la base sont la prise de commandes et la facturation.-Dans le cas de la prise de commandes, on saisira lensemble des lignes de commandes, et, sil ny a pas derreur, on imprimera la commande.-Dans le cas de la facturation, on recherche les commandes pour le client, on dite les factures.QuestionsQ1) Compte tenu des rgles de gestion, dterminer les entits ainsi que les associations liant les entits entre elles. Prciser les cardinalits et discuter leurs rles .Q2) Proposer pour chaque entit plusieurs attributs (minimum deux) dont un qui doit tre lidentifiant de lentitQ3) Quels sont les identifiants des associations obtenues en Q1). Justifier la rponsePartie II: Du Modle E-R au modle RelationnelExercice II.1Reprendre lexercice I-1 QuestionQ1) Elaborer le schma relationnel correspondant par application des rgles de passage du modle Conceptuel E-R au modle Relationnel. Expliquer la dmarche suivieExercice II.2Reprendre lexercice I-2 QuestionsQ1) Elaborer le schma relationnel correspondant par application des rgles de passage du modle Conceptuel E-R au modle Relationnel.Pour chaque Relation obtenue, prciser sa cl primaire, sa (ou ses) cl (s) trangre (s) sil y a lieu.Q2) Evolution des modles :2-1) Les clients prcisant rarement leur numro (identifiant) sur les bons de commande. Celui-ci est recherch partir de la raison sociale.Cela vous conduit-il modifier quelque chose dans le Modle Conceptuel de Donnes ? Si oui, quoi ? Si non , pourquoi ? Cela vous conduit-il modifier quelque chose dans les relations du modle relationnel ? Si oui, quoi ? Si non , pourquoi ? 2-2) On veut pouvoir diter, pour une commande donne, les lignes dans lordre de saisie du bon de commande.Cela vous conduit-il modifier quelque chose dans le Modle Conceptuel de Donnes ? Si oui, quoi ? Si non , pourquoi ? Cela vous conduit-il modifier quelque chose dans les relations du modle relationnel ? Si oui, quoi ? Si non , pourquoi ? Exercice II.3Soit le schma Entit-Relation suivant :Le tableau suivant donne un chantillon reprsentatif de valeurs observes dans lassociation enseigner.Id_profId_ClasseId_MatDupont10MathMartin10InfoDurand30MathLouvier40Physavec les valeurs suivantes pour chacune des entits Id_Prof = {Louis, Martin,Dupont,Durand,Louvier}Id_Classe = {10, 30, 40}Id_Mat = {Math, Info, Phys, Chimie}QuestionsQ1) Dterminer les cardinalits minimales et maximales de chaque objet participant cette association ?Q2) Dduire le schma relationnel correspondant partir du schma entit-association prcdent. Expliquer la dmarche suivie.Q3) Les relations obtenues prcdemment sont-elles toutes en troisime forme normale ? pourquoi ? Partie III: Dpendances fonctionnellesExercice III.1Soit la relation universelle suivante:R(no_emp, nom_emp, AGE, fonct, no_dept, nom_dept, no_projet, Nom_projet, duree) dans laquelleno_emp est le matricule de lemploy,nom_emp est le nom de lemploy, AGE est lge de lemploy,fonct est la fonction de lemploy, no_dept est le numro du dpartement de lentreprise dans lequel lemploy travaille,nom_dept est le nom du dpartement, no_projet est le numro du projet, Nom_projet est le nom du projet, duree reprsente le temps pass exprim en heures par employ pour un projet donn.On admet les rgles suivantes:un employ identifi par son numro ne travaille dans un dpartement (identifi par son numro) et noccupe quune fonction,un employ peut travailler sur plusieurs projets,plusieurs employs travaillent sur un mme projet,la dure du travail effectu par un employ sur un projet donn doit tre toujours multiple de 2 heures,les employs ayant un ge infrieur ou gal 30 ans ne peuvent pas occuper la fonction conducteur des travaux.QuestionsQ1) Quelles sont les dpendances fonctionnelles existant entre ces attributs?Q2) Donner le graphe de ces dpendances fonctionnelles et expliquer la dmarche suivie En dduire la cl de la relation R.Q3) Expliquer les inconvnients de la relation R en cas d'une cration, modification, suppression d'une occurrence (ou Tuple ou Ligne) de R.Q4) Dcomposer la relation R en relations en troisime forme normale (schma de la base de donnes) et rappeler lintrt de la normalisationPartie IV: Mise en oeuvre Exercice IV-1 : Arbres algbriquesReprendre lexercice III-1QuestionsQ1) Construire larbre algbrique des requtes suivantes: a)Donner les noms des employs ayant travaills sur tous les projetb)Donner les noms des employs ayant travaills sur au moins un projetExercice IV-2 : Graphe des dpendances fonctionnelles et arbres algbriquesUn rseau de voies ferres est constitu d'un ensemble de lignes identifies par un numro (NUMLIGNE). Une ligne permet de relier une gare de dpart (GAREDEP) une gare terminus (GARETERM). Entre le gare de dpart et la gare terminus plusieurs itinraires (lignes) sont possibles. Les lignes sont distingues par la mention "VIA" qui indique la gare intermdiaire.Exemples : - Ligne no. 10 ParisLyonVia Dijon- Ligne no. 11 Lyon ParisVia Dijon- Ligne no. 12 Paris LyonVia Moulin- Ligne no.13 ParisToulouseVia Limoges- Ligne no. 14 Paris ToulouseVia Capdenac- ..Une ligne reliant une gare de dpart une gare terminus dessert galement un certain nombre de gares appeles gares de parcours. A chaque ligne correspond donc une liste de gares parcourues (GAREPARCOURS). Mais comme le rseau est fortement maill, une mme gare peut se trouver l'intersection de plusieurs lignes. Par ligne, un numro d'ordre (NUMORD) est associ chaque gare desservie par cette ligne.Exemple : Ligne no. 10ParisNUMORD = 0Gare de dpartDijon NUMORD = 1 Via MconNUMORD = 2Lyon NUMORD = 3 Gare de TerminusLe trafic sur le rseau peut tre dfini par un ensemble de trains rfrencs par des numros (NUMTRAIN) et effectuant un trajet sur une ligne donne, avec un horaire dtermin unique : Heure de dpart (HEUREDEP) et heure d'arrive (HEURE ARR).Un train circule toujours sur la mme ligne. Mais une ligne peut tre le trajet de plusieurs trains.Le mme train "physique circule souvent sur la ligne "aller" et sur la ligne "retour", mais au niveau logique il s'agit de deux trains diffrents circulant sur deux lignes diffrentes.Un train est constitu d'un ensemble de wagons ayant chacun son propre numro (NUMWAGON), son type (TYPEWAGON), son poids vide (POIDSV) et sa capacit (CAPACITE).Un wagon peut tre utilis dans le temps par plusieurs trains du moment ou sa disponibilit le permet. (Exemple : le wagon numro W2456 compose le train T575 avec un dpart de Paris 5h00 et arrive Toulouse 11h00. Le mme wagon compose le train numro T655 avec un dpart de Toulouse 13h00 et arrive Montpellier 16h00).Dans chaque train, un wagon occupe une position particulire (POSITION).Questions :Q1)Dterminer les constituants (attributs) de la relation universelle reprsentant cet univers selon les concepts du modle relationnel.Dterminer la cl de cette relation. Justifiez votre rponse.Expliquez les inconvnients d'une telle relation avec des exemples simples, pour les trois oprations d'insertion, de modification et de suppression.Q2)Dterminer les dpendances fonctionnelles et tracer le graphe de celles-ci.Donner le schma relationnel correspondant cette base de donnes. Justifiez votre rponse en prcisant la dmarche suivie.Exercice IV-3 : SQLSoit le schma relationnel suivant:IMMEUBLE (ADI, NBETAGES, DATEC, PROP)APPIM (ADI, NAP, TYPE, SUPER, OCCUP, ETAGE)PERSONNE (NOM, AGE, PROF, ADR, NAPR)ECOLE (NOMEC, ADEC, NBCLASSES, DIR)CLASSE (NOMEC, NCL, MAITRE, NBEL)ENFANT (NOMP, PRENOM, AN, NOMEC, NCL)AvecADI: Adresse dimmeubleNBETAGES: Nombre dtagesDATEC: Date (Anne) de constructionPROP: Nom du propritaireNAP : Numro dappartementTYPE: Type dappartement (F1, F2, F3,)SUPER: Superficie en m2OCCUP: Nom de loccupantETAGE: Etage de lappartementNOM: Nom de personneAGE: AgePROF: ProfessionADR: Adresse dhabitationNAPR: Nom dappartement dhabitationNOMEC: Nom dcoleADEC: Adresse dcoleNBCLASSES: Nombre des classesDIR: Nom du DirecteurNCL: Numro de la classe.MAITRE: Nom du matreNBEL: Nombre dlvesNOMP: Nom de parentPRENOM: Prnom de lenfantAN : Anne de naissance de lenfantQuestionsQ1) Adresses des immeubles ayant plus de 10 tages et construits avant 1970?Q2) Adresses des immeubles possds par des informaticiens dont lge est infrieur 40 ans,Q3) Noms des personnes qui ne sont pas propritaires?Q4) Liste des occupants (nom, age, profession) des immeubles possds par DUPONT,Q5) Noms des personnes qui habitent dans un immeuble dont ils sont propritaires,Q6) Noms et professions des propritaires dimmeubles o il y a des appartements vides,Q7) Quels sont les matres qui habitent dans le mme immeuble quau moins u n de leurs lves (on suppose que les enfants vivent sous le mme toit que leur responsable)?Q8) Adresse de limmeuble, date de construction et types dappartements et tages o habitent chacun des matres des enfants de DUPONT?Q9) Nom et ge des matres qui habitent dans un immeuble dont le propritaire est responsable dun de leurs lves?Q10) Nom et ge des personnes qui sont propritaires mais qui ne sont pas ni matre ni directeur dcole?Exercice IV-4 : Interprtation des ordres SQLOn considre le schma relationnel suivant:Oeuvre (Code-oeuvre, titre)Morceau (Code-morceau, dure, code-disque, code-oeuvre)Disque (Code-disque, marque, type)Interprte (Nom, prnom, adresse)Excute (Code-morceau, nom, prnom)NOTA: Les cls primaires de relations sont soulignes.Les tables seront reprsentes dans les requtes par des lettres(alias) : Oeuvre O, Morceau M, Disque D, Interprte I, Excute E.QuestionsExprimer en franais les requtes SQL suivantes:Q1) select nom, prnom, count(code-morceau)from E group by nom, prnom;Q2)Create View Total-Disq (code-disque, total)as select code-disque, sum(dure)from Mgroup by code-disque;select D.*from D, Total-Disq TDwhere D.code-disque = TD.code-disqueand total = (select max(total) from Total-Disq);Q3) select code-oeuvre,titrefrom O, Mwhere O.code-oeuvre = M.code-oeuvregroup by code-oeuvre, titrehaving count(code-disque) >=2;Q4) select I.*from Iwhere not exists (select *from E, M, Dwhere E.nom = I.nom and E.prnom = I.prnomand E.code-morceau = M.code-morceauand M.code-disque = D.code-disqueand type =JAZZ);Partie V: Rpartition des donnes Exercice V-1 :A la suite de diffrents changements, la base de donnes initialement implante dans un seul hpital a volu et gre maintenant plusieurs hpitaux de la rgion Alsace. Le schma de la base est devenu le suivant :Service (Snum, SNom, Shopital, SBtiment, SDirecteur)Sale(Snum, SAnum, SASurveillant, SANbLits)Employ(ENum, ENom, EAdresse, ETlphone)Docteur(Dnum, DSpcialit)Infirmier(Inum, Snum, IRotation, ISalaire)Patient (Pnum, Snum, SAnum, PLit, PNom, PAdresse, PTlphone, PMutuelle, PPrise_en_charge) Acte(Dnum, Pnum, Date, ADesrciption, ACoefficient)Dans ce schma, le directeur d'un service est un docteur dsign par son numro. Le numro de la sale est local un service dans un mme hpital (Il y a plusieurs salles numro 12). Un surveillant de salle est un infirmier dsign par son numro. Pour une salle, on connat son nombre de lits. Docteurs et infirmiers sont des employs. Leur identification en tant que docteurs ou infirmiers est la mme qu'en tant qu'employs.QuestionsQ1 : Exprimez en SQL la requte suivante : "donnez le nom des cardiologues qui ont traits un ou plusieurs patients hospitaliss dans un service de grontologie".Q2: En fait, cette base de donnes est rpartie sur trois sites informatiques correspondant aux valeurs de l'attribut "hpital" de la relation Service :Nom du siteValeur de l'attribut "hpital"StrasbourgAmbroise-ParColmarColmarRgionalToutes les autres valeursProposez et justifiez une bonne dcomposition de la base hospitalire sur ces trois sites. Pour cela, utilisez la dcomposition horizontale et/ou verticale ainsi que la rplication, sachant que :Les sites Strasbourg et Colmar ne grent que les hpitaux correspondants.Les infirmiers sont employs dans un service donn.Les docteurs travaillent le plus souvent sur plusieurs hpitaux ou cliniques.La gestion des lits d'hpitaux est locale chaque hpital.On dsire regrouper la gestion des frais d'hospitalisation au centre rgional.Pour chaque fragment obtenu, on donnera sa dfinition sous la forme d'un calcul de l'algbre relationnelle partir du schma initial (global).Q3 : Proposez une dcomposition et un plan d'excution rpartie possible pour la requte de la question 1.Q4 : On suppose que l'hpital de Belfort est attach la base de donnes hospitalires aprs son implmentation rpartie. L'hpital de Belfort possde son propre site informatique qui se retrouve donc connect aux autres sites.Le schma de la base de Belfort avant son intgration est le suivant : Service (Snum, SNom, SBtiment, SDirecteur)Sale(Snum, SAnum, SASurveillant, SANbLits)Docteur(Dnum, DNom, DAdresse, Dtlphone, DSpcialit)Infirmier(Inum, INom, IAdresse, ITlphone Snum, ISalaire)Patient (Pnum, Snum, SAnum, PLit, PNom, PAdresse, PTlphone, PMutuelle, PPrise_en_charge) Acte(Dnum, Pnum, Date, ADesrciption, ACode)Discutez les problmes et proposez des solutions pour intgrer la base de Belfort au systme rparti dj dfini.L'intgration devra se faire sans transferts d'informations.Q5 : Proposez une dcomposition et un plan d'excution pour la requte SQL de la question 1 aprs intgration de la base de Belfort.Exercice V-2 :Une grande entreprise de transactions immobilires a dcid d'implanter son systme d'information sur une base de donnes relationnelle.SECTEURRECHERCHETYPEOFFRECLIENTConcerneEtre 2SoumettreVenteEtre 11,10,10,11,10,N0,N0,N1,10,N0,NLa modlisation du systme d'informations a donn le MCD suivant :L'entit "client" concerne les clients ou les prospects de l'entreprise et comporte les proprits suivantes :- CliNumnumro du client (identifiant de l'entit)- CliNomnom du client- CliAdrNumnumro de la voie du client- CliAdrVoienom de la voie o se trouve le client- CliAdrTypetype de la voie o se trouve le client- CliAdrVilleville- CliCAChiffre d'affaires cumul du clientL'entit "recherche" dcrit les recherches confies par les clients l'entreprise et comporte : - RecRefla rfrence de la recherche- RecSurMinsurface minimum- RecSurMaxsurface maximum - RecPrixprix au maximumL'entit "secteur" se rapporte aux secteurs gographiques sur lesquels l'entreprise exerce son activit. Elle comporte :- SecRefrfrence du secteur (identifiant de l'entit)- SecLiblibell du secteur- SecStatPrixprix moyen au m2 du secteur - SecStatNbenombre de ventes dans ce secteur- SecStatNb1information statistique relative au secteur- SecStatNb2information statistique relative au secteur- SecStatNb3information statistique relative au secteurL'entit "Type" concerne les types de recherches ou d'offres (bureaux, magazins, etc.). Elle comporte :- TypCodecode du type- TypLiblibell du type- TypStatPrixprix moyen au m2 des ventes de ce type- TypStatNbenombre de ventes de ce type- TypStatNb1information statistique relative au type- TypStatNb2information statistique relative au typeL'entit "offre" concerne les offres qui sont faites l'entreprise et comporte :- OfrRefrfrence de l'offre (identifiant de l'entit)- OfrSurla surface exacte- OfrPrixMinprix minimum souhait- OfrPrixprix souhaitL'association "Ventes" est porteuse des donnes relative la vente, savoir :- VteDatela date de la vente- VteMntle montant exact de la transactionL'association "Concerne" permet de savoir quels secteurs gographiques correspondent les recherchesL'association "Soumettre" associe les clients et leurs recherchesL'association "Etre 1" permet de connatre le type des offres L'association "Etre 2" permet de connatre le type des recherchesQ2.1 Quel schma de base de donnes relationnelle proposez-vous ? Justifiez votre rponse en prsentant la dmarche suivie.Q2.2L'entreprise est organise en fonction de ses mtiers selon les recherches qui peuvent tre de plusieurs types :-bureaux-magazins-surfaces industrielles-habitations en appartement-habitations en maisonLes diffrentes divisions et services ont des tches clairement identifies :- Paris Opra sont gres les recherches sur les bureaux et les magazins-au sige sont gres les recherches sur les surfaces industrielles- Paris Clignancourt sont gres les recherches sur habitations en appartement- Versailles sont gres les recherches sur habitations en maison-le service tudes qui s'occupe de la vente et de faire des tudes de marchs fondes sur les statistiques et les principaux indicateurs grs par le systme, se trouve la Dfense.-le service marketing s'occupe de suivre les clients ayant le plus important chiffre d'affaires pour les inviter des manifestations de prestige. Ce service ainsi que les autres services de l'entreprise se trouve au sige.Q2.2.1Proposez et justifiez une bonne dcomposition de la base sur ces diffrents sites.Pour cela on pourra utiliser la dcomposition (fragmentation horizontale et/ou verticale) ainsi que la rplication.Pour chaque fragment obtenu, on donnera sa dfinition sous la forme d'un calcul de l'algbre relationnelle partir du schma initial (global).Inversement, on indiquera comment se calcul chaque relation de la base partir des fragments.Partie VI: Administration des donnesExercice VI-1 :Soit les tables suivantes :SOC (idsocCHAR(5)NOT NULL,-- identifiant de lentrepriselibsocCHAR(60)NOT NULL,-- Nom de lentreprisevillescoCHAR(60)NOT NULL,-- Localit de lentreprisesigleCHAR(20)NOT NULL,-- Sigle de lentreprisenbempNUMBER(4)NULL-- Nombre demployes )EMP (idempCHAR(5)NOT NULL,-- identifiant interne employnomCHAR(50)NOT NULL,-- Nom de lemployprenom CHAR(50)NULL,-- Prnom de lemploydanaisDATENOT NULL,-- Date de naissancevilledomCHAR(60)NULL,-- Localit de rsidencesalaireNUMBERNULL,-- salaire en coursdatembDATENOT NULL,-- date dembauchefonctionCHAR(15)NULL,-- Fonction dans lentrepriseidsocCHAR(5)NOT NULL-- entreprise de rattachement )Les clefs primaires sont signales par des noms de colonne en gras/soulign.idsoc : Clef trangre dans la table EMP vers la table SOC.QuestionsQ1) Une requte SQL doit retourner dans un programme le nom de lemploy le plus pay.a) Parmi les quatre requtes suivantes, seules deux dentre elles donnent le rsultat souhait.Indiquer lesquelles:1/ select nom from emp where salaire=(select max(salaire) from emp)2/ select nom,max(salaire) from emp3/ select nom,max(salaire) group by nom from emp4/ select nom from emp where salaire >= all (select salaire from emp)b) Sachant quun index a t plac sur la colonne SALAIRE, indiquer parmi les deux requtes trouves dans la question prcdente, laquelle est la plus performante.Justifier votre rponse.Q2) lattribut NBEMP concerne le nombre demploys pour la socit.Sachant que ce compteur doit tre toujours cohrent par rapport lajout et la suppression demploys dans la table EMP, proposer un mcanisme permettant de rpondre ce besoin.Q3) Contrainte dintgritIl faut mettre en place les contrles de saisie sur la table EMP, tel que :a. Lge lors de lembauche doit tre compris entre 18 et 65 ans , bornes incluses;b. Le salaire doit dans tous les cas tre suprieur ou gal zro;c. Le salaire ne doit pas tre suprieur au maximum des salaires des employs exerant la mme fonction dans la mme entreprise.Quelle solution proposer (uniquement les principes) pour les trois rgles de gestion prcdentes.Justifier la rponse.Q4) Cration de vuesa) Donner lordre de cration dune vue permettant dobtenir la masse salariale de lentreprise (somme de tous les salaires des employs)b) Donner lordre de cration dune vue permettant de lister les noms demploys avec le pourcentage de leur salaire par rapport la masse salariale globale.Exercice VI - 2Dans une entreprise du secteur alimentaire, une application de gestion de rsultats d'analyses bactriologiques sur des produits utilise une base de donnes relationnelle.Le schma de la base est le suivant :ANALYSE (AN_ANNEE, AN_NUMERO, AN_DATE, LI_CODE, PR_CODE)RESULTAT (AN_ANNEE, AN_NUMERO, CR_CODE, RE_RESULTAT, RE_CONCLUSION)CRITERE(CR_CODE, CR_NOM)STANDARD(CR_CODE, PR_CODE, ST_VALEUR)PRODUIT(PR_CODE, PR_NOM) LIEU (LI_CODE, LI_NOM)Remarques:Seuls les index des cl primaires sont crs.Le dtail de chaque zone est propos en annexe.Les informations de volumtrie sont les suivantes:TABLENOMBRE DE RANGEECROISSANCE/MOIS ANALYSE l0000400RSULTAT 5000020CRITERE200STANDARD 4000020PRODUIT 20001LIEU500ADMINISTRATION (lexique)ANALYSEAN-ANNEE : anne laquelle lanalyse a t ralise,AN-NUMERO : numro dordre de lanalyse lintrieur dune anne,AN-DATE:date laquelle lanalyse a t ralise,LI-CODE: code du lieu de ralisation de lanalyse,PR-CODE: code du produit analys,RESULTATAN-ANNEE : anne laquelle lanalyse a t ralise,AN-NUMERO : numro dordre de lanalyse lintrieur dune anne,CR-CODE:code du critre qui est analys (ex: BACO1: bactrie N1),RE-RESULTAT: valeur rsultant de lanalyse pour un critre,RE-CONCLUSION: conclusion (conforme/non conforme) du rsultat en fonction des standardsCRITERECR-CODE:code du critre qui est analys (ex: BACO1: bactrie N1),CR-NOM: dsignation du critre,STANDARDCR-CODE:code du critre qui est analys (ex: BACO1: bactrie N1),PR-CODE: code du produit analys,ST-VALEUR: valeur standard pour un produit et un critre,PRODUITPR-CODE: code du produit analys,PR-NOM: dsignation du produit,LIEULI-CODE: code du lieu de ralisation de lanalyse,LI-NOM: dsignation du code,Lutilisation de la base peut tre synthtis par le schma suivant:LABORATOIRE4 x laborantinsDIRECTION LABORATOIRE1 directeurDIRECTION QUALITE1 directeurBASE DE DONNEESRemarque: Tous les programmes travaillant sur la base utilisent des transactions.Vous venez d'tre nomm administrateur de cette base de donnes. Votre objectif est de rsoudre les problmes gnant la bonne exploitation de l'application.Les problmes perus par les diffrents utilisateurs sont les suivants : Le directeur du laboratoire :Un problme matriel sur la machine supportant la base de donnes a entran la perte de jours de saisies car le redmarrage de la base n'a pu se faire que sur une sauvegarde effectue en fin de semaineDes standards d'analyse ont t modifis sans l'accord pralable du directeur du laboratoire.Le directeur souhaite une accs facile touts les analyses de la journe en cours, avec leurs rsultats.Le directeur de la qualit :Il dsire suivre l'volution de la qualit des produits en listant rgulirement touts les analyses dont la conclusion est 'NON-CONFORME'.Actuellement le directeur de la qualit utilise des requtes SQL qui lui semblent compliques et dont il se plaint de la lenteur.Les laborantins :A chaque dition d'une analyse, il faut ajouter manuellement le numro de lot du produit sur le listing. Ce numro n'est connu qu'au moment o un laborantin effectue l'analyse.Le traitement de saisie des rsultats faisant appel toutes les tables par jointure, a de mauvais temps de rponse.Lors de la saisie du rsultat d'une analyse, il est possible de saisir par inadvertance un critre qui n'existe pas.Tous les vendredis, un programme purge la base des analyses dont la date est antrieure un AN? Ce traitement est de plus en plus long.Questions :Q1) Identifiez les problmes en terme de base de donnes et proposez une liste (non dtaille) des actions mener en les justifiant.Q2) Pour chaque action propose prcdemment, dtaillez leur mise en uvre, au besoin en utilisant des ordres SQL.Q3) En prenant plusieurs scnarii catastrophes, expliquez les oprations de reprise.Q4) Optimisation des requtes SQL A dvelopper en sance.Exercice VI - 3Supposons que nous ayons effectuer la jointure entre les tables R1 (10000 tuples) et R2 (1500 000 tuples) selon les prdicats R1.A = R2.B o A est la cl primaire de R1 et B est une cl trangre dans R2. B dans R2 a 5000 valeurs distinctes. Exposer les diffrentes mthodes dexcution des jointures pouvant tre utilises par les SGBDR et illustrer par un calcul simple la mthode que vous jugez la plus efficace (performante).Exercice VI - 4Concurrence d'accs :Deux transactions T1 et T2 s'effectuent sur la base de donnes pounr modifier des informations contenues sans les tables. Elles effectuent les oprations suivantes :TRANSACTION T1TRANSACTION T2T11Lire AT21Lire BT12Lire BT22Lire AT13Lire CT23Ecrire B = B + AT14Ecrire A = B + CT15Ecrire C = B Questions :Q1) En l'absence d'un mcanisme de contrle de concurrence d'accs, exposez les situations d'incohrence pouvant survenir.Montrez les rsultats possibles en prenant l'hypothse suivante : A=100, B=300 et C=500.Q2) En cas de panne ou d'annulation d'une transition en cours, prsentez un droulement des transactions qui mne une situation incohrente.Q3) Avec un mcanisme de contrle de concurrence, quelles sont les valeurs prises par A, B et C aprs la complte excution de ce deux transactions ?Peut-il y avoir plus d'un rsultat possible pour A, B et C.Q4) L'unit d'accs aux bases de donnes est la 'granule'. A votre avis, qu'elle est la meilleure taille de cette unit ?Partie VII: Rvision Exercice VII-1On souhaite grer le planning d'un centre de formation l'aide d'une base de donnes relationnelle.On dispose des informations suivantes :1- Numstagiaire = Numro du stagiaire 2- Nomstagiaire = Nom du stagiaire3- Prestagiaire = Prnom du stagiaire4- Datnastagiaire = Date de naissance du stagiaire5- Numstage = Numro stage6- Intstage = Intitul du stage7- Domstage = Domaine du stage (I=Informatique, D=Droit international, O=Organisation)8- Datdebstage = Date de dbut dune session d'un stage9- Datfinstage = Date de fin dune session d'un stage (exemple : Stage no. 1234 d'intitul " Initiation la micro-informatique pour cadres", dont une session dmarre le 29/03 et se termine le 12/04)10- Numprof = Numro du professeur11- Nomprof = Nom du professeur12- Preprof = Prnom du professeur13- Nummod = Numro module14- Intmod = Intitul du module15- Numsess = Numro session16- Nbreheure = Nombre dheures denseignement effectu17- Tauxhor = Taux horaire denseignementLes informations ci-aprs sont en prendre en compte :- un stagiaire suit une session d'un stage (et une seule),- un module peut tre assur par un ou plusieurs professeurs,- un module est un cours susceptible d'tre incorpor dans un ou plusieurs stages,- un professeur peut enseigner plusieurs modules,- une session de stage se compose d'une succession de modules, chacun de ces modules tant pris en charge par un professeur entre une date de dbut et une date de fin,- un stage peut tre ralis dans plusieurs sessions.- lattribut Nbreheure correspond au nombre dheures denseignement effectu par un professeur donn, pour un module donn et une session donne.- le taux horaire denseignement (Tauxhor) est fonction du domaine de stage (Informatique = 500FFHT, Droit international = 600 FFHT, Organisation = 550 FFHT)Questions :Q1) Dterminer le schma relationnel en troisime forme normale de cette base de donnes et expliquer la dmarche suivie.Q2) La direction du centre dcide de dcentraliser la gestion des stages sur trois agences Paris, Lille et Nantes.Proposer et justifier une bonne dcomposition de cette base de donnes sur ces trois sites. Pour cela on pourra utiliser la dcomposition horizontale et/ou verticale ainsi que la rplication (duplication), sachant que :- Lagence de Paris est responsable de la planification des stages du domaine Informatique et de la gestion administrative des intervenants enseignants (recrutement et paiement).- Lagence de Lille est responsable de la planification des stages du domaine Droit International et de la synchronisation des interventions des enseignants (un professeur peut intervenir sur plusieurs stages).- Et enfin lagence de Nantes gre le planning des stages du domaine Organisation.pour chaque fragment obtenu, on donnera sa dfinition sous la forme dun calcul de lalgbre relationnelle partir du schma initial (cf. Q1).Inversement, on indiquera comment se calcule chaque relation de la base partir des fragments.Dterminer le schma relationnel en troisime forme normale par application des rgles de passage du modle Conceptuel de Donnes v ers le modle Relationnel. Expliquer la dmarche suivie.Q3) Doit-on dfinir pour cette base de donnes des contraintes d'intgrit rfrentielles ou autres (dclaratives) ? si oui lesquelles ? Justifier votre rponse.Q4) Indiquer les index dont la cration vous semble indispensable. Justifier votre rponse et dtailler le rle des index.Q5) Dceler de ce schma relationnel les diffrentes contraintes dintgrit (dclaratives et rfrentielle) devant tre prises en compte lors de limplmentation physique de la base de donnes. Justifier votre rponse et expliquer les diffrentes manires de programmer les contraintes dintgrit rfrentielles.