CONTROLE DE CONNAISSANCES 2012/2013
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 : 4 points (Q3.1 = 2 pts, Q3.2 = 2 pts)
Question 4 : 4 points (Q4.1 = 2 pts, Q4.2 = 2 pts)
Question 5 : 2,5 points
Question 6 : 2 points
Question 7 : 1 point
L’organisme gérant l’attribution des prix Nobel souhaite mettre en place une base de données pour mémoriser les attributions de prix Nobel et pouvoir interroger efficacement ces données. A cette fin, le schéma relationnel suivant a été défini :
Discipline (nomDiscipline, description)
PrixNobel(nomDiscipline, annee, motivation)
PersonneOrganisme(numPO, nom, prenom, date, pays)
AttributionNobel(nomDiscipline, annee, numPO, accepte, montant, age)
Parente(numPO1, numPO2, parente)
Pays(pays, population)
Les clés sont soulignées et les clés étrangères sont en gras.
La sémantique des diverses relations est la suivante :
Discipline (nomDiscipline, description) : une discipline a un nom (‘Prix Nobel de la paix’, ‘Prix Nobel de Physique’, ‘Prix Nobel de médecine’, ‘Prix Nobel de Chimie’) qui l’identifie. Une description plus complète de la discipline est aussi mémorisée.
PrixNobel(nomDiscipline, annee, motivation) : un prix Nobel particulier est identifié par la discipline concernée et l’année d’attribution du prix. Une motivation, justifiant les raisons du choix d’attribution de ce prix, est aussi mémorisée. nomDiscipline est une clé étrangère qui référence l’attribut nomDiscipline de la relation Discipline.
PersonneOrganisme(numPO, nom, prenom, date, pays) : une personne ou un organisme recevant le prix Nobel est identifié par un numéro (numPO). L’attribut nom conserve le nom de famille de la personne ou le nom de l’organisme. Le prénom (attribut prenom) n’est renseigné que s’il s’agit d’une personne. L’attribut date correspond à la date de création de l’organisme ou à la date de naissance de la personne. L’attribut pays correspond à la nationalité principale de la personne ou de l’organisme (en cas de multi-nationalité, la personne ou l’organisme choisit une nationalité). pays est une clé étrangère sur l’attribut pays de la relation Pays.
AttributionNobel(nomDiscipline, annee, numPO, accepte, montant, age) : cette relation permet de mémoriser l’attribution d’un prix Nobel (dans une discipline donnée, pour une année donnée) à une personne ou un organisme donné. L’attribut accepte permet de savoir si ce prix a été accepté ou non. Le montant en couronnes suédoises est aussi mémorisé. Enfin, l’âge de la personne ou de l’organisme au moment de l’attribution du prix est renseigné. Le couple d’attributs (nomDiscipline, annee) est une clé étrangère qui référence les attributs nomDispline et annee de la relation PrixNobel. numPO est une clé étrangère qui référence numPo de la relation PersonneOrganisme.
Parente(numPO1, numPO2, parente) : il s’avère que de nombreux prix Nobel ont des liens de parenté. Par exemple, Pierre et Marie Curie (mari et femme) ont reçu le prix Nobel de physique. Irène Joliot-Curie, leur fille, a reçu le prix Nobel de chimie. La relation Parente mémorise pour un couple de personnes leur lien de parente (‘mari/femme’, ‘parent/enfant’, ‘frère/sœur’). NumPO1 et NumPO2 sont deux clés étrangères qui référencent numPO de la relation PersonneOrganisme.
Pays(pays, population) : les pays sont listés dans cette relation. Un pays est identifié par son nom et caractérisé par sa population en millions d’habitants.
Question 1 : Retro-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) Quelles sont les disciplines (nom de la discipline) pour lesquelles des prix Nobels ont été attribués à des personnes ou des organismes de moins de 30 ans ?
(Q2.2) Quels sont les petits pays (moins de 30 millions d’habitants) ayant des organismes ou des ressortissants récompensés par un prix Nobel ? Le résultat doit donner une liste de noms de pays.
Il faudrait pouvoir ajouter les conditions de jointure
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 pays qui ont reçu des prix Nobel dans toutes les disciplines au travers de leurs organismes ou ressortissants ? Le résultat doit donner une liste de noms de pays.
(Q3.2) Quels sont les pays qui n’ont jamais reçu le prix Nobel de médecine ? Le résultat doit donner une liste de noms de pays.
Question 4 : Requêtes SQL d’interrogation
Exprimer les questions suivantes en SQL :
(Q4.1) Donner pour chaque pays et chaque discipline le nombre de prix Nobel acceptés.
(Q4.2) Quel est le lien de parenté le plus fréquent entre les personnes ayant reçu des prix Nobel ?
Question 5 : Création de table SQL
Donner l’ordre de création complet de la relation AttributionNobel(nomDiscipline, annee, numPO, accepte, montant, age). 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.
nomDiscipline est une chaine de 30 caractères. annee est un entier. La première année d’attribution du prix est 1901. numPo est un entier. accepte est une chaine de 3 caractères qui peut prendre deux valeurs (‘oui’ ou’non’). Cette valeur est obligatoire. montant est un entier. age est un entier entre 18 et 130. Cet attribut est obligatoire.
Question 6 : Création d’une vue SQL et droit
(Q6.1) Créer une vue StatistiqueNobel(discipline, montantMoyen, ageMoyen) qui permet d’avoir pour chaque discipline le montant moyen attribué et l’age moyen des bénéficiaires, en ne considérant que les disciplines qui ont récompensé plus de 50 fois des personnes ou des organismes.
(Q6.1) Donner le droit d’interrogation à public sur cette vue.
Question 7 : question de cours
Pourquoi dit-on qu’une transaction est atomique ? Que se passe t-il si une panne survient au milieu d’une transaction ?