CONTROLE DE CONNAISSANCES 2015/2016
Etudiants 2ème année (EI2)
CSC4001 Contrôle final 1 – Durée : 1h30
Coordonnateur : Claire LECOCQ et Amel MAMMAR
Documents autorisés : tous les documents distribués en cours et les notes de cours
Calculatrices et téléphones portables non autorisés
Avertissements
1/ Lisez attentivement le sujet.
2/ Les questions sont indépendantes les unes des autres.
3/ Soyez clair et précis.
4/ Soignez la présentation, dans la mesure du possible.
5/ Barème indicatif
Question 1 : 3 points
Question 2 : 3,5 points (Q2.1 = 1,5 pt, Q2.2 = 2 pt)
Question 3 : 3 points (Q3.1 = 1,5 pts, Q3.2 = 1,5 pts)
Question 4 : 4 points (Q4.1 = 2 pts, Q4.2 = 2 pts)
Question 5 : 2,5 points
Question 6 : 2 points (Q6.1 = 1,5 pts, Q6.2 = 0,5 pts)
Question 7 : 2 points
1
Le ministère de l’Education Nationale et de la Recherche rassemble régulièrement les données qui
concernent la formation en France. En particulier, il recense les établissements de l’enseignement
supérieur, les diplômes délivrés et les effectifs correspondants. Ce ministère souhaite pouvoir stocker
ces données dans une base de données pour pouvoir les interroger efficacement et construire des
tableaux de bord.
Après une phase de conception, le modèle relationnel suivant a été défini :
Communes(codeCommune, nomCommune, departement, region)
Etablissements(IdentifiantEtablissement, codeCommune, libelleEtablissement, sigle,
secteurEtablissement, siteInternet)
Disciplines(codeDiscipline, grandeDiscipline, discipline, secteurDisciplinaire)
Diplômes(codeDiplome, codeDiscipline, libelleDiplome, niveau)
DelivrePar(codeDiplome, IdentifiantEtablissement)
EffectifsDiplomes(codeDiplome, annee, effectifHomme, effectifFemme)
Les clés sont soulignées et les clés étrangères sont en gras.
La sémantique des diverses relations est la suivante :
Communes(codeCommune, nomCommune, departement, region) : une commune est identifiée par un
code. Elle possède un nom (nomCommune) et est située dans un département et une région. Un tuple
de cette relation est (91228, ‘Evry’, ‘Essonne’, ‘Ile-de-France’).
Etablissements(IdentifiantEtablissement, codeCommune, libelleEtablissement, sigle,
secteurEtablissement, siteInternet) : un établissement de formation possède un identifiant
(IdentifiantEtablissement) et est situé dans une commune. codeCommune est une clé
étrangère qui référence la clé de la relation Commune. Un établissement possède un libellé et un sigle.
Il est par ailleurs caractérisé par son secteur (‘Public’ ou ‘Privé’). L’adresse de son site internet est
enfin précisée. Un tuple de cette relation est (‘0755661W’, 91228, ‘Télécom SudParis’, ‘TSP’,
‘Public’, ‘[Link]
Disciplines(codeDiscipline, grandeDiscipline, discipline, secteurDisciplinaire) : chaque discipline
enseignée en France est identifiée par un code. Les disciplines sont structurées en grandes disciplines
(e.g., Sciences et sciences de l'ingénieur), puis en disciplines (e.g., Sciences fondamentales et
applications) et enfin en secteurs disciplinaires (e.g., Informatique). Un tuple de cette relation est
(‘SI1514’, ‘Sciences et sciences de l'ingénieur’, ‘Sciences fondamentales et applications’,
‘Informatique’).
Diplômes(codeDiplome, codeDiscipline, libelleDiplome, niveau) : un diplôme est identifié par un
code et est délivré dans une discipline. codeDiscipline est une clé étrangère qui référence la clé
de la relation Discipline. Un diplôme est également décrit par un libellé et un niveau (‘L’, ‘M’ ou
‘D’ respectivement pour Licence, Maitrise et Doctorat). Un tuple de cette relation est (‘TSP_inge’,
‘SI1514’, ‘Ingénieur de Télécom SudParis’, ‘M’).
DelivrePar(codeDiplome, IdentifiantEtablissement) : un tuple de cette relation permet d’associer à
un diplôme un établissement délivrant ce diplôme. Un tuple de cette relation est (‘Inge_TSP’,
‘0755661W’).
EffectifsDiplomes(codeDiplome, annee, effectifHomme, effectifFemme) : tous les ans, les effectifs
diplômés sont décomptés pour chaque diplôme en différenciant les effectifs d’hommes et de femmes.
Un tuple de cette relation est (‘TSP_inge’, 2014, 140, 70).
2
Question 1 : Rétro-conception
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.
Question 2 : Algèbre relationnelle
Exprimer chacune des ces questions suivantes en algèbre relationnelle.
(Q2.1) Quels sont les promotions (code de diplôme et année) qui ont plus de diplômées
femmes que d’hommes ?
(Q2.2) Quels sont les diplômes (code de diplôme, libellé de diplôme et libellé établissement)
délivrés par des établissements en région Ile-de-France ?
Question 3 : Requêtes algèbre relationnelle ou SQL
Exprimer chacune des ces questions suivantes en algèbre relationnelle ou en SQL .
(Q3.1) Quels sont les établissements (code d’établissement et libellé d’établissement) qui
proposent des diplômes dans toutes les grandes disciplines ?
(Q3.2) Quels sont les diplômes qui n’ont pas de diplômés en 2015. Précisez le code de
diplôme, le libellé et la discipline (grande discipline, discipline et secteur disciplinaire).
Question 4 : Requêtes SQL d’interrogation
Exprimer les questions suivantes en SQL :
(Q4.1) Donner pour chaque établissement diplômant des femmes et des hommes, le libellé de
l’établissement, le sigle, l’adresse de son site Web, le nombre de diplômes proposés et le
nombre de diplômés total (quelle que soit l’année)
(Q4.2) Quels sont les diplômes délivrés par plusieurs établissements ? Précisez le code du
diplôme, son libellé, le nombre d’établissements, le nombre de promotions diplômées et la
date de la première promotion.
3
Question 5 : Création de table SQL
Donner l’ordre de création complet (comprenant la définition des attributs, domaines, contraintes
d’intégrité, clé et clés étrangères) de la relation Etablissements(IdentifiantEtablissement,
codeCommune, libelleEtablissement, sigle, secteurEtablissement, siteInternet) :
• IdentifiantEtablissement est une chaîne de 50 caractères ;
• codeCommune est un nombre sur 5 positions, dont aucune après la virgule. codeCommune
est une clé étrangère qui référence un tuple de la relation Communes ;
• libelleEtablissement est une chaîne de 100 caractères. Il est obligatoire de donner un
libellé pour tout établissement ;
• sigle est une chaîne de 10 caractères ;
• secteurEtablissement est une chaîne de 6 caractères. Cette information est obligatoire.
Elle peut prendre deux valeurs : ‘Public’ ou ‘Privé’ ;
• siteInternet est une chaîne de 100 caractères qui doit commencer par ‘[Link]
Question 6 : Création d’une vue SQL et droit
(Q6.1) Créer une vue DiplomesParDisciplines(grandeDiscipline, discipline,
secteurDisciplinaire, nbDiplomes, nbDiplomés, PremierePromotion, DernièrePromotion) qui
permet d’avoir pour chaque secteur disciplinaire le nombre de diplômes proposés, l’année de
la première promotion diplômées et l’année de la dernière promotion diplômée. Les
disciplines ayant moins de 100 diplômés sont ignorées.
(Q6.2) Donner le droit d’interrogation avec le droit de transmettre ce droit à une application
identifiée par le login ministere sur la vue que vous venez de créer.
Question 7 : Question de cours
Répondre par un texte court (moins de 10 lignes) à la question de cours suivante.
Dans le cahier des charges, la règle de gestion suivante a été exprimée : « Les établissements publics
sont tenus d’accueillir des hommes et des femmes, et ce chaque année. Cette vérification doit être faite
tous diplômes confondus ».
Cette règle peut-elle être exprimée en SQL. Si oui, comment ? Si non, comment la prendre en charge ?
