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?
Posté le 9 septembre 2013