TELECOM
SudParis
Contrôle des
connaissances
|
Bases de données |
Code : CSC4001 |
|
Date : 09/11/2009 |
Durée : 1h30 |
|
Coordonnateur : C. Lecocq et S. Tata |
Documents autorisés: ceux distribués en cours |
____________________________________________________
Avertissements
1/ Lisez
attentivement le sujet.
2/ Les questions sont
indépendantes les unes des autres.
3/ Essayez d'être
clair et précis dans vos réponses.
4/ Soignez la
présentation, dans la mesure du possible.
5/ Barème indicatif
Question 1 : 4 points
Question 2 : 5 points (Q2.1 = 1 pt, Q2.2 = 2 pts, Q2.3 = 2 pts)
Question 3 : 4 points (Q3.1 = 2 pts, Q3.2 = 2 pts)
Question 4 : 6 points (Q4.1 = 2 pts, Q4.2 = 2 pts, Q4.3 = 2 pts)
Question 5 : 1 point
Un ensemble d’écoles vous
demande de gérer une base de données servant de support a leur plateforme
d’apprentissage (de type moodle). Le schéma de bases de données est le suivant
:
Cours(codeCours, titre,
volumeHoraire)
Themes(codeCours, theme, presentation)
Activites(codeCours, theme, activite, ressourceAssociee)
Promotions (idPromotion,
ecole, annee)
Etudiants(numEtudiant,
nom, prenom, adresse, idPromotion )
SuivreActivites(numEtudiant, codeCours, theme, activite,
dureeTotale, evaluation)
Les clés sont soulignées et les clés étrangères sont en
gras.
La sémantique des diverses relations est la suivante :
Cours(codeCours, titre, volumeHoraire) : un
cours est identifié par un code (« CSC4001 » par exemple). Il est
décrit par un titre(« Introduction aux bases de données relationnelles par
exemple) et un volume horaire (le temps estimé pour suivre ce cours, i.e. pour
réaliser toutes les activités liées à ce cours).
Themes(codeCours,
theme, presentation) : un cours est divisé en thèmes. Un thème est
identifié par un couple constitué du code du cours et du theme (le titre du
thème (« Le langage SQL » par exemple). CodeCours est une clé
étrangère référençant un cours. Un thème est décrit par une présentation, petit
texte sur 100 caractères au maximum.
Activites(codeCours,
theme, activite, ressourceAssociee) : au sein d'un thème
existent des activités. Une activité est identifiée par un triplet constitué du
code du cours, du thème et de l'activité. Les activités possibles sont
« Cours », « Exercice », « TP » et
«Expérimentation ». Le couple codeCours, theme est une clé
étrangère référençant un tuple de la relation Themes. Une activité est
décrite par une ressource associée (URL de la ressource mémorisée sous forme de
chaîne de caractère).
Promotions (idPromotion, ecole, annee) : une
promotion est identifiée par un idPromotion. Elle est caractérisée par l'école
à laquelle elle appartient (« Telecom SudParis » par exemple), et une
année (2 par exemple pour signifier les étudiants de 2ème année).
Etudiants(numEtudiant, nom, prenom, adresse, idPromotion) : un étudiant est
identifié par un numéro. Il a un nom, un prénom et une adresse. Il appartient à
une promotion. IdPromotion est une clé étrangère référençant un tuple de
la relation Promotions.
SuivreActivites(numEtudiant,
codeCours, theme, activite, dureeTotale, evaluation) : la
plateforme enregistre les activités que suivent les étudiants. Un tuple dans
cette relation donne pour un étudiant donné (identifié par son numéro numEtudiant)
et pour une activité donnée (identifiée par le triplet codeCours, theme,
activite), la durée totale que l'étudiant a passé à réaliser cette activité
et une note évaluant les acquis à l'issue de l'activité (attribut evaluation).
numEtudiant est une clé étrangère référençant un tuple de la relation Etudiants.
codeCours, theme, activite est une
clé étrangère référençant un tuple de la relation Activites.
Question 1
En vous aidant des règles de passage d'une modélisation
Entité/Association à une modélisation relationnelle "à l'envers",
proposer un schéma Entité/Association équivalent à ce schéma relationnel. Ce
schéma devra comporter la description des entités (avec leurs propriétés), des
entités faibles (s'il y a lieu), des associations (avec leurs propriétés) ainsi
que les cardinalités minimum et maximum des associations binaires.
Question 2
Exprimer chacune des questions suivantes, d'une part en algèbre relationnelle
sous la forme d'un arbre algébrique, et d'autre part en SQL.
(Q2.1) Quels sont les cours (code de cours) et leurs thèmes qui
proposent des activités « TP » ?
(Q2.2) Quels sont les cours (code et titre de cours) suivis par les
étudiants de l’école « Telecom SudParis » ?
(Q2.3) Quels sont les cours (code et
titre de cours) qui ne sont suivis par aucun étudiant ?
Question 3
Exprimer les questions suivantes en SQL :
(Q3.1) Pour chaque étudiant
et pour chaque thème de cours, donner le numéro de l’étudiant, le titre du
cours, le thème et la durée totale passée à effectuer les activités liées à ce
thème.
(Q3.2)
Pour chaque thème et activité du cours « CSC4001 », donner le
thème, l’activité et la moyenne des évaluations si le nombre d’évaluations
existant pour l’activité est supérieur ou égal à 10.
Question 4
(Q4.1) Quels sont étudiants (nom et prénom) qui ont suivi toutes
les activités « TP » proposées dans le cours « CSC4001 » ?
Exprimer cette question en algèbre relationnelle ou en SQL.
(Q4.2) Donner la définition complète de la relation Activites dans le langage SQL. Par
définition complète, on entend la définition des attributs et de leurs
domaines, mais aussi la définition de toutes les contraintes d'intégrité
associées. L’attribut codeCours est
une chaîne de 10 caractères, le theme
est une chaîne de 30 caractères au plus, l’activite
est une chaîne de 20 caractères, la ressourceAssociee
est une chaîne de 50 caractères. Les valeurs possibles de activite sont « Cours », « Exercice »,
« TP », «Expérimentation ». La clé de la relation est le triplet
codeCours, theme, activite. Le couple
codeCours, theme est une clé
étrangère référençant un tuple de la relation Themes.
(Q4.3) Quels sont les cours (code et titre) pour lesquels la durée totale
moyenne passée par étudiant dépasse le volume horaire prévu initialement pour le cours. Vous devez
pour cela faire dans un premier temps une vue donnant pour chaque cours et pour
chaque étudiant la durée totale passée par l'étudiant sur ce cours. Cette vue
pourra avoir le schéma suivant activiteTotale(numEtudiant, codeCours,
dureeTotale). Vous interrogerez ensuite cette vue pour répondre à la
requête.
Question 5
Expliquer en quelques lignes ce
que signifient les propriétés transactionnelles A C I D.
Posté le 13 novembre 2009