Bases de données relationnelles
publicité
Université de Lille – Sciences et Technologies, Masters ASE et GI Bases de Données Relationnelles Bases de données relationnelles - Travaux dirigés Conception de bases de données relationnelles Exercice 1 : Relevé de notes Sujet : On souhaite élaborer une base de données restreinte sur une promotion d'étudiants qui suivent les différentes matières d'un diplôme, les cours étant assurés par un ensemble d'enseignants. Voici le type de documents que l'on souhaite pouvoir éditer à partir de cette base : N° étud. 1 2 3 ... Liste des étudiants Nom étudiant Date de naissance Dupont, Charles 18/03/1991 Dubois, Jules 02/11/1992 Favier, Isabelle 02/02/1989 ... ... N° matière 1 2 3 4 5 Sexe M M F ... Liste des enseignants N° Nom enseignant Grade Ancien neté 10 Bertrand, Pierre ASS 2 11 Dupont, Auguste MCF 3 ... ... ... ... Relevé de notes N° étudiant : 1 Dupont, Charles Nom matière Coefficient Programmation 3 Bases de données 2 G.P.A.O. 2 Logique 1 Statistiques 1 Moyenne Générale Matière Logique G.P.A.O. ... Note sur 20 10 9 12,5 13 7 10,3 On suppose qu'une matière n'est enseignée que par un seul enseignant (qui peut en revanche enseigner plusieurs matières) et qu'un étudiant n'a qu'une note par matière. Questions : 1) Effectuer l'analyse du problème en recensant les données élémentaires (attributs) avec leurs propriétés (nom, libellé, type de donnée, contraintes de validité, règle de calcul). 2) Étudier les anomalies de mutation dues aux redondances, puis les dépendances fonctionnelles, dans la relation suivante. Normaliser ensuite celle-ci en proposant un schéma de remplacement en 2 tables conformes à la forme normale de Boyce-Codd : ENSEIGNEMENTS(num_mat, nom_mat, coef, num_ens, nom_ens) 3) Proposer un modèle Entité-Association pour ce problème, en précisant la clé de chaque entité, les attributs des entités et des associations, ainsi que les cardinalités de chaque association. 4) Effectuer la traduction en modèle relationnel et en donner une représentation graphique qui fait apparaître les liaisons par clé étrangère. 1/4 Université de Lille – Sciences et Technologies, Masters ASE et GI Bases de Données Relationnelles Exercice 2 : Parc informatique Sujet : Une entreprise souhaite mettre en place une base de données recensant les ordinateurs qu'elle possède dans différents locaux, et les logiciels qui sont installés sur ces machines. L'analyse du problème a fourni les contraintes suivantes : – une salle peut héberger plusieurs ordinateurs ; – tous les ordinateurs sont reliés en réseau, et un ordinateur possède une adresse réseau I.P. (Internet Protocol) qui lui est propre ; – plusieurs logiciels peuvent être installés sur un ordinateur ; – un même logiciel (d'une version donnée) peut être installé sur plusieurs ordinateurs. On souhaite conserver dans la base toutes les versions acquises de tous les logiciels que possède l'entreprise, mais pas l'historique de leurs installations sur les différents ordinateurs. Par ailleurs, les informations suivantes seront aussi mémorisées : – nom, date et prix d'achat d'un logiciel ; – désignation d'une salle ; – nom d'un ordinateur. Questions : 1) Faire l'analyse de ce problème en en proposant un modèle entité-association où figureront les attributs des entités et des associations. On précisera bien l'identifiant de chaque entité, ainsi que les cardinalités des associations, en les justifiant par une phrase. 2) Traduire ce modèle entité-association en modèle relationnel. 3) Expliciter les contraintes d'intégrité que doit respecter ce modèle. Exercice 3 : Généalogie Sujet : On souhaite élaborer une base de données décrivant un arbre généalogique. Pour chaque individu, on désire : son nom, ses prénoms, son sexe, sa date et son lieu de naissance (et éventuellement de décès), et (s'il y a lieu) l'historique de ses mariages : date, lieu, nom et prénoms du conjoint, le divorce (s'il y a lieu), ses parents (la mère et le père), ses enfants. Questions : 1) Proposer un modèle Entité-Association pour ce problème, en précisant la clé de chaque entité, les attributs des entités et des associations, ainsi que les cardinalités de chaque association. 2) Expliciter les contraintes d'intégrité de ce modèle. 2/4 Université de Lille – Sciences et Technologies, Masters ASE et GI Bases de Données Relationnelles Interrogation d'une base de données avec SQL Pour chaque question, il s'agit de trouver la requête SQL permettant d'afficher le résultat énoncé. La base de données concernée est celle conçue au premier exercice ; son schéma relationnel est donné ci-contre. Exercice 4 : Sélection de données. 1) toute la table etudiants 2) nom, numéro et date de naissance des étudiants 3) liste des étudiantes 4) liste des enseignants par ordre alphabétique des noms 5) liste des enseignants par grade et par ordre alphabétique décroissant des noms 6) nom, grade et ancienneté des enseignants qui ont strictement plus de 2 ans d'ancienneté 7) nom, grade et ancienneté des maîtres de conférences qui ont 4 ans d'ancienneté ou plus 8) nom et date de naissance des étudiants masculins nés après 1990 9) lignes de la table notes correspondant à une note inconnue 10) nom des enseignants professeurs ou associés, en utilisant l'opérateur IN 11) nom des enseignants dont le prénom ne commence pas par un J 12) nom et date de naissance des étudiants nés en 1990 13) nom et âge (en années) des étudiants de 23 ans ou plus Exercice 5 : Jointures. 1) notes obtenues par l'étudiant Dupont, Charles 2) note obtenue par l'étudiant Dupont, Charles en G.P.A.O. 3) nom et date de naissance des étudiants plus jeunes que l'étudiant Dupont, Charles 4) nom des étudiants ayant eu la moyenne dans une des matières enseignées par Simon, Etienne 5) numéro des étudiants qui ont eu une note dans la matière n°1 inférieure à celle de la matière n°2 6) nom des étudiants ayant eu une plus mauvaise note en Programmation qu'en Bases de données 7) nom et numéro des étudiants n'ayant eu aucune note 3/4 Université de Lille – Sciences et Technologies, Masters ASE et GI Bases de Données Relationnelles Exercice 6 : Regroupements. 1) grades différents existant dans la table des enseignants 2) par sexe, afficher les différents âges (en années) représentés parmi les étudiants 3) nombre total d'étudiants 4) date de naissance de l'étudiant le plus jeune et de celui le plus âgé 5) pour chaque matière identifiée par son numéro, nombre d'étudiants qui ont une note 6) pour chaque étudiant identifié par son numéro, moyenne obtenue (avec 2 décimales) 7) numéro des étudiants n'ayant eu que 4 notes ou moins Exercice 7 : SQL avancé. 1) noms des matières (et de leur enseignant) pour lesquelles la moyenne (non coefficientée) des notes est inférieure à 10 2) pour chaque étudiant ayant eu une note dans chacune des 5 matières, le nom (par ordre alphabétique), le numéro et la moyenne coefficientée obtenue 3) nom des enseignants ayant le même grade que Bertrand, Pierre 4) pour chaque étudiant, nom et nombre d'étudiants se trouvant avant lui sur la liste alphabétique des noms 4/4
