CONTROLE DE CONNAISSANCES 2012/2013
Etudiants 2ème année (EI2)
CSC4001 Contrôle final 2 – 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 points (Q2.1 = 1 pt, Q2.2 = 2 pt)
Question 3 : 3,5 points (Q3.1 = 1,5 pts, Q3.2 = 2 pts)
Question 4 : 3,5 points (Q4.1 = 1,5 pts, Q4.2 = 2 pts)
Question 5 : 2 points
Question 6 : 3,5 points (Q6.1 = 2,5 pts, Q6.2 = 1 pts)
Question 7 : 1,5 point (Q7.1 = 0,5 pts, Q7.2 = 1 pts)
L’organisme gérant l’organisation des coupes du monde de football (la FIFA) souhaite mettre en place une base de données pour mémoriser les scores des différentes rencontres disputées en phases finales de cette prestigieuse compétition afin de pouvoir retrouver efficacement ces données. A cette fin, le schéma relationnel suivant a été défini :
Pays(nomPays, superficie, continent)
EditionCoupeDuMonde(annee, paysOrganisateur, paysVainqueur)
ParticipationPays(nomPays, annee, entraineur, parcours)
Match(nomPays1, nomPays2, annee, nbreButsMarque1, nbreButsMarque2, phase)
Joueur (nomJoueur, nomPays, poste, dateNaiss)
StatJoueur (nomJoueur, nomPays, annee, nombreButs)
Les clés sont soulignées et les clés étrangères sont en gras.
La sémantique des diverses relations est la suivante :
Pays(nomPays, superficie, continent) : un pays, identifié par son nom, est décrit par sa superficie et le continent dans lequel il se situe. Cette relation mémorise tous les pays dotés d’une fédération nationale de football.
EditionCoupeDuMonde(annee, paysOrganisateur, paysVainqueur) : une édition de coupe du monde est identifiée par l’année de son organisation, et est organisée par un pays. Le pays vainqueur de l’édition est aussi mémorisé. L’attribut paysOrganisateur (resp. paysVainqueur) est une clé étrangère qui référence l’attribut nomPays de la relation Pays.
ParticipationPays(nomPays, annee, entraineur, parcours) : ne sont répertoriés dans cette relation que les pays ayant participé à une ou plusieurs éditions de coupes du monde. L’attribut entraineur désigne le nom de l’entraineur du pays à une édition donnée. L’attribut parcours mémorise la phase atteinte par le pays, et ne peut donc prendre que l’une des valeurs suivantes (‘poule’, ‘huitième’, ‘quart’, ‘demi’, ‘finales’). nomPays (resp. annee) est une clé étrangère qui référence l’attribut nomPays (resp. annee) de la relation Pays (resp. EditionCoupeDuMonde).
Match(nomPays1, nomPays2, annee, nbreButsMarque1, nbreButsMarque2, phase) : un match désigne la confrontation entre deux pays, le score final de la rencontre est mémorisé par le nombre de buts marqués par chaque équipe. Pour simplifier, les tirs aux buts sont considérés comme des buts marqués en cours du match. L’attribut phase prend les mêmes valeurs que l’attribut parcours. Pour simplifier, on suppose que deux pays ne se confrontent qu’une fois au plus dans chaque édition. Les attributs nomPays1 et nomPays2 sont des clés étrangères qui référencent l’attribut nomPays de la relation Pays. De même, l’attribut annee est une clé étrangère qui référence l’attribut annee de la relation EditionCoupeDuMonde.
Joueur (nomJoueur, nomPays, poste, dateNaiss) : cette relation mémorise les joueurs des différents pays, le poste occupé par chaque joueur dans l’équipe (‘gardien de but’, ‘défenseur’, ‘attaquant’, etc.) ainsi que son année de naissance. nomPays est une clé étrangère qui référence l’attribut nomPays de la relation Pays.
StatJoueur (nomJoueur, nomPays, annee, nombreButs) : cette relation indique, pour chaque joueur d’un pays participant, le nombre de buts inscrits à une édition donnée de coupe du monde. nomJoueur (resp. nomPays, annee) est une clé étrangère qui référence l’attribut nomJoueur (resp. nomPays, annee) de la relation Joueur (resp. Pays, EditionCoupeDuMonde).
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) Quels sont les pays ayant participé à la coupe du monde 1986 ? Le résultat doit donner une liste de noms de pays.
(Q2.2) Quelles sont les personnes ayant gagné une coupe du monde en étant entraineur d’une équipe européenne ?
Question 3 : Requêtes algèbre relationnelle ou SQL
Exprimer chacune des ces questions suivantes en algèbre relationnelle ou SQL .
(Q3.1) Quels sont les pays ayant participé à toutes les éditions de coupe du monde. Le résultat doit donner une liste de noms de pays avec leurs continents respectifs ?
(Q3.2) Quels sont les pays n’ayant jamais dépassé la phase des poules ? Le résultat doit inclure tous les attributs de la relation Pays.
Question 4 : Requêtes SQL d’interrogation
Exprimer les questions suivantes en SQL :
(Q4.1) Donner pour chaque joueur, en précisant le pays, le nombre total de buts inscrits.
(Q4.2) Pour chaque édition ultérieure à 1950, quels sont les entraineurs des joueurs ayant marqué le plus grand nombre de buts ?
Question 5 : Création de table SQL
Donner l’ordre de création complet de la relation EditionCoupeDuMonde(annee, paysOrganisateur, paysVainqueur). 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 et en particulier celle concernant l’attribut paysVainqueur vis-à-vis de la relation Match : le payVainqueur doit correspondre au pays ayant participé et gagné la finale de la même. annee est un entier. La première édition de la coupe du monde a eu lieu en 1930, le nom d’un pays est une chaine de 30 caractères. Un tuple est inséré dans cette table dès qu’on connait le pays organisateur avant même son déroulement.
Question 6 : Création d’une vue SQL et droit
(Q6.1) Créer une vue StatistiqueCoupeDuMonde(annee,
agemoyen, nombreTotalButs) qui permet d’avoir pour chaque édition de coupe du
monde l’âge moyen des joueurs butteurs
et le nombre total de buts inscrits. Seules les éditions à plus de 20 buts nous
intéressent.
(Q6.1) Donner les droits de mise à jour (INSERT, UPDATE et DELETE) à public sur cette vue.
Question 7 : questions de cours
(Q7.1) Une table peut-elle avoir plusieurs clés primaires ? Justifiez votre réponse
(Q7.2) Soient A (cleA, …) et B (cleB, cleA…) deux relations avec la contrainte référentielle suivante :
ALTER TABLE B ADD CONSTRAINT FOREIGN KEY (cleA) REFERENCES A(cleA) ON DELETE CASCADE. Que signifie cette contrainte?