Bases de données relationnelles

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 :
Liste des étudiants Liste des enseignants
N°
étud.
Nom étudiant Date de
naissance
Sexe N° Nom enseignant Grade Ancien
neté
Matière
1 Dupont, Charles 18/03/1991 M 10 Bertrand, Pierre ASS 2 Logique
2 Dubois, Jules 02/11/1992 M 11 Dupont, Auguste MCF 3 G.P.A.O.
3 Favier, Isabelle 02/02/1989 F ... ... ... ... ...
... ... ... ...
Relevé de notes
N° étudiant : 1 Dupont, Charles
N° matière Nom matière Coefficient Note sur 20
1 Programmation 3 10
2 Bases de données 2 9
3 G.P.A.O. 2 12,5
4 Logique 1 13
5 Statistiques 1 7
Moyenne Générale 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