Exercice 1 Dépendances fonctionnelles et normalisation
Exercice 2 Dépendances fonctionnelles et normalisation. Soit la relation R (A, B,
C, D) et F l'ensemble des dépendances fonctionnelles suivantes : A, B C.
part of the document
L2 informatique Bases de données
Session dautomne 2008-2009
Durée 2h
Documents interdits.
Les exercices 1 et 2 sont indépendants
NB : toutes les réponses doivent être justifiées
Exercice 1 Modélisation entités-associations et production du schéma de la base
On souhaite créer la base de données modélisant de façon simplifiée le fonctionnement dun collège :
Chaque élève est caractérisé par un identifiant, son nom son prénom, son adresse et sa date de naissance.
Chaque élève est inscrit dans une seule classe. Une classe est caractérisée par un identifiant, un niveau (ex :sixième), un numéro (ex : 3) et une section (ex :bilingue).
Chaque professeur est caractérisé par un identifiant, son nom, son prénom, son adresse et son salaire. Un professeur enseigne dans plusieurs classes, et dans chaque classe interviennent plusieurs professeurs.
Un professeur enseigne une seule matière. Une matière est caractérisée par un identifiant, un nom (ex : arts plastiques) et un niveau (ex : cinquième). Une même matière peut être enseignée par plusieurs professeurs.
On considère pour simplifier que pour chacune des matières suivies, un élève obtient une seule note.
Modéliser lénoncé précédent sous forme dun schéma entités-associations, en précisant les cardinalités des différents types dassociations.
Produire le modèle relationnel dérivé de ce schéma entités-associations, en expliquant la traduction de chaque type dassociations en fonction de sa cardinalité.
Représenter chaque table obtenue sous la forme dune relation, en précisant :
son nom et ses attributs,
la clé primaire,
les éventuelles clés étrangères (dans ce cas faire apparaître explicitement les liens entre les clés étrangères et les clés primaires référencées).
Remarque : il nest pas demandé de donner les instructions « create table » ni de préciser les types des attributs.
NB : tout le reste de lexercice dépendant de votre modélisation, celle-ci devra être très lisible et soigneusement expliquée, afin que les requêtes SQL soient compréhensibles.
En particulier veiller à choisir des noms lisibles (i.e proches de la sémantique) pour les relations et leurs attributs.
Ecrire les requêtes suivantes en SQL :
Identifiant et nom de tous les professeurs de mathématiques.
Identifiant du professeur ayant le meilleur salaire.
Identifiants des matières enseignées par plus de deux professeurs.
Identifiants des élèves de la sixième 5, accompagnés de leur note de français.
Identifiant de lenseignant qui intervient dans le plus de classes.
Moyenne générale des élèves de troisième.
Exercice 2 Dépendances fonctionnelles et normalisation
Soit la relation R (A, B, C, D) et F lensemble des dépendances fonctionnelles suivantes :
A, B ( C
B ( D
C ( A
a. Donner toutes les clés minimales possibles en justifiant formellement daprès les axiomes dArmstrong.
b. R est elle en deuxième forme normale ? En troisième forme normale ?
c. Proposer si besoin une décomposition de R en relations en troisième forme normale. Cette décomposition dépend-elle du choix de la clé obtenue à la question a ?
d. Les relations obtenues à la question c sont-elles en forme normale de Boyce Codd (BCNF) ? Si ce nest pas le cas proposer une décomposition en BCNF. Cette décomposition induit-elle des pertes de données ? Des pertes de dépendances fonctionnelles ?
