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.