USTL-Master Sciences et Technologies Mention MIP-1ère année 2006 / 2007 U.E. TABLEUR et BASES de DONNEES EXAMEN de BASES de DONNEES Durée : 1h30 Documents non autorisés Les exercices sont indépendants et peuvent être traités dans un ordre quelconque Exercice 1 : Conception d'une base de données Un laboratoire souhaite mettre en place une base de données bibliographiques simple pour tenir une liste des publications de ses membres. L'analyse du problème a fourni les contraintes suivantes : – il y a 3 types de publications : article de journal, présentation dans une conférence, et thèse. – une conférence publie un recueil d'actes (Proceedings en anglais) qui regroupe toutes les présentations qui ont été effectuées lors de cette conférence. – chaque personne peut être auteur de plusieurs publications, et une publication peut être écrite par plusieurs personnes. – plusieurs publications peuvent avoir été présentées dans la même conférence, plusieurs articles dans le même journal et plusieurs thèses dans la même université. En conséquence, afin de faciliter l'implémentation, les différentes sources (journal, conférence et thèse) seront regroupées dans une entité unique. – pour les 3 types de publications, on souhaite conserver les informations suivantes : – auteur: le ou les auteur(s) de la publication, avec leur rang (1er auteur, 2ème, ...) – titre : le titre de la publication – source : le journal (pour un article), la conférence (pour une présentation), ou l'université (pour une thèse) où est parue la publication – date : la date de soutenance (pour une thèse) ou de parution (pour un article de journal). Pour une conférence, la date doit être commune à toutes les publications qui y ont été présentées ; elle correspond aux jours où la conférence a eu lieu. – pages : les pages dans la source (journal ou conférence), ou le nombre de pages (pour une thèse) (Ex. : "1345-1354" ou "120") – on souhaite par ailleurs stocker les informations suivantes : – nom, prénom et adresse mail des personnes (Ex. : "Dupont", "Bernard", "[email protected]") – nom et adresse des universités (Ex. : "USTL", "Villeneuve d'Ascq") – nom et éditeur des journaux (Ex. : "Journal of Biomedical Engineering", "Biomedical Society") – titre des actes, dates et lieu des conférences (Ex. : "Proceedings IEEE/SMC International Conference on Systems, Man and Cybernetics", "6-9 oct 2002", "Hammamet, Tunisia") Faire l'analyse de ce problème en en proposant un modèle entité-association qui précisera les attributs des entités et des associations. On précisera avec soin, par exemple grâce à un tableau récapitulatif, pour quel(s) type(s) de source(s) sont utilisés les différents attributs (indiquer ce à quoi correspond chaque attribut pour chaque type de source, et s'il est utilisé). Traduire (en justifiant) ce modèle entité-association en modèle relationnel. Exercice 2 : Exploitation d'une base de données avec SQL Pour stocker les informations relatives aux employés d'une société, ainsi qu'aux projets auxquels ceux-ci participent, on a élaboré le schéma de base de données relationnelle suivant sous Access : 1) Expliquer ce schéma : quelles sont les tables de la base, que représentent les liaisons et les indications (1..∞) qui y figurent, qu'est-ce que cela implique pour les enregistrements des différentes tables, … (donner des exemples) ? 2) Écrire les requêtes SQL (en SQL normalisé) correspondant aux questions suivantes : a) Nom, dates de début et de fin des projets b) Liste des projets qui durent (ou ont duré) plus d'un an c) Nom des employés et de leur supérieur hiérarchique d) Pourcentage cumulé d'implication de chaque employé dans chaque projet en cours e) Salaire moyen des employés subordonnés directs de Mr. Colin f) Nom des employés impliqués dans aucun des projets