Exercice 1 Dépendances fonctionnelles et normalisation
Produire le modèle relationnel dérivé de ce schéma entités-associations. ...
Ecrire la requête c en algèbre relationnelle ou sous forme d'arbre algébrique.
part of the document
L2 informatique Systèmes dinformation et bases de données
1e session 2008
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
Soit le schéma entités-associations suivant, qui modélise la base de données des sports enseignés dans une université.
un étudiant est caractérisé par un identificateur, son nom, son prénom, son adresse et sa date de naissance.
un diplôme est caractérisé par un identificateur, le domaine (ex : informatique), et le niveau du diplôme (ex : L2).
un sport est caractérisé par un identificateur, un libellé (ex : natation), un niveau (ex : débutant), et le prix (en euros), qui représente le coût annuel de ce sport pour létudiant.
un créneau hebdomadaire est caractérisé par un identificateur, le jour de la semaine (ex : mardi) et la tranche horaire (ex : 16h-18h).
On considère que les types des attributs sont :
id_étudiant, id_dipl, id_sport, id_créneau, nom, prénom, adresse, domaine, niveau_dip, libellé, niveau, jour, horaire : chaînes dau plus 20 caractères
date_naiss : date
prix : nombre décimal à cinq chiffres, dont 2 après la virgule.
Sachant que lattribut « id_creneau » nest pas un identifiant dans lassociation « pratique », un étudiant peut-il pratiquer le même sport (pour un niveau donné) à plusieurs créneaux horaires différents ?
Produire le modèle relationnel dérivé de ce schéma entités-associations.
Justifier les ajouts de tables et/ou de clés étrangères en fonction des cardinalités des associations. Pour chaque table donner linstruction SQL « create table » associée en précisant la clé primaire et les clés étrangères.
Ecrire les requêtes suivantes en SQL :
Liste des étudiants nés le premier trimestre de lannée 1985.
Noms des étudiants pratiquant la natation.
Identificateurs des étudiants pratiquant un sport le mardi de 16h à 18h.
Nombre détudiants pratiquant un sport le mardi de 16h à 18h.
Pour chaque étudiant, donner le montant total quil paye pour lensemble des sports pratiqués.
Donner le diplôme où il y a le plus détudiants inscrits qui pratiquent au moins un sport.
1.4. Ecrire la requête c en algèbre relationnelle ou sous forme darbre algébrique.
Exercice 2 Dépendances fonctionnelles et normalisation
On considère une relation R dattributs : animateur, sport , lieu, groupe, créneau horaire. Un nuplet (a,s,l, g,c) a la signification suivante : lanimateur a enseigne le sport s dans le lieu l au groupe g pendant le créneau horaire c.
Soit F lensemble des dépendances fonctionnelles (DF) suivantes :
. a -> s
. a, c -> g
. c, l -> a
. c, g -> l
2.1. A partir de linterprétation des DF de F, répondre aux questions suivantes :
un animateur donné peut-il enseigner des sports différents ?
un animateur donné peut-il encadrer des groupes différents ?
deux sports différents peuvent-ils avoir lieu simultanément au même endroit ?
2.2. . Soit G lensemble des DF suivantes :
. a -> s
. a, c -> l
. c, l -> g
. c, g -> a
Les ensembles F et G sont-ils équivalents ? Faire une démonstration formelle en utilisant les axiomes dArmstrong.
2.3. Soit la relation universelle R (a, s, l, g, c).
donner une clé minimale de la relation R en justifiant formellement
en quelle forme normale est la relation R ?
2.4.Donner si besoin une décomposition de R en relations en troisième forme normale.
