Institut TELECOM ; TELECOM SudParis
Contrôle des
connaissances
|
Bases de données |
Code : CSC4001 |
|
Date : 09/11/2010 |
Durée : 1h30 |
|
Contrôle proposé |
Documents |
____________________________________________________
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 (Q1 = 4 pts)
Question 2 : 3 points (Q2.1 = 1 pt, Q2.2 = 1 pt, Q2.3 = 1 pts)
Question 3 : 6 points (Q3.1 = 2 pts, Q3.2 = 2 pts, Q3.3 = 2 pt)
Question 4 : 5 points (Q4.1 = 2 pts, Q4.2 = 3 pts)
Question 5 : 2 points
La fédération internationale de Football souhaite mettre en
place une base de données pour gérer les informations concernant les mondiaux
de football. Pour cela, on a défini le schéma relationnel suivant :
Pays (pays,
continent)
Joueur (nom,
prenom, pays, datenaissance)
Mondial
(annee, paysOrganisateur)
ParticipationEquipe (pays,
annee, groupe, entraineur, classement)
ParticipationJoueur (nom,
prenom, pays, annee, poste)
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 (pays, continent) : un pays est identifié
par son nom (l’attribut pays) et
caractérisé par un continent.
Joueur (nom, prenom, pays,
datenaissance) : un joueur est identifié par un
nom, un prénom et un pays. Il est caractérisé par une date de naissance. L’attribut
pays est une clé étrangère qui
référence la relation Pays.
Mondial (annee, paysOrganisateur) :
un mondial est identifié par l’année de son organisation et caractérisé par le
pays organisateur. L’attribut paysOrganisateur est une clé étrangère qui référence la
relation Pays.
ParticipationEquipe (pays, annee,
groupe, entraineur, classement) : la participation d’une équipe dans un
mondial est identifiée par le pays que cette équipe représente et l’année du
mondial. Elle est caractérisée par un groupe, un entraineur et un
classement à l’issue du mondial en question. L’attribut pays est une clé étrangère qui référence la relation Pays. L’attribut année est une clé étrangère qui référence la relation Mondial.
ParticipationJoueur (nom, prenom, pays, annee,
poste) : la participation d’un joueur dans un mondial est identifiée par
son nom, son prénom, son pays et l’année du mondial. Elle est caractérisée par
le poste (par exemple gardien ou défenseur) occupé par ce joueur pendant
ce mondial. Les attributs nom, prenom et pays constituent une clé étrangère qui référence la relation Joueur. L’attribut annee est une clé étrangère qui
référence la relation Mondial.
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.
Corrigé
Question 2
Exprimer chacune des 3 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 pays qui ont organisé au moins un
mondial ?
Corrigé :
SELECT paysOrganisateur
FROM Mondial
(Q2.2) Quelles sont les équipes nationales qui ont participé au
mondial de 1998 ? Donnez leurs noms, leurs entraineurs et leurs classements
dans ce mondial.
Corrigé :
SELECT pays, entreneur,
classement
FROM ParticipationEquipe
WHERE annee
= 1998
(Q2.3) Quel sont les
joueurs (nom, prénom et pays) qui ont participé à un mondial organisé en
France ?
Corrigé :
SELECT nom, prenom,
pays
FROM ParticipationJoueur
P, Mondial M.
WHERE M.payOrganisateur= "France"
AND M.annee=P.annee
Question 3
Exprimer les 3 questions suivantes en SQL :
(Q3.1) Pour chaque entraineur, donner le nom et le nombre de
participations dans des mondiaux.
Corrigé :
SELECT entraineur, count(*)
FROM ParticipationEquipe P
GROUP BY entraineur
(Q3.2) Donner le(s) entraineur(s) qui a (ont) dirigé le maximum
d’équipes (différentes)
Corrigé :
SELECT entraineur
FROM ParticipationEquipe P
GROUP BY entraineur
HAVING count(*) >= ALL (
SELECT
count(*)
FROM ParticipationEquipe
P
GROUP BY
entraineur
)
Ou
SELECT entraineur
FROM ParticipationEquipe P
GROUP BY entraineur
HAVING count(distinct pays) >= ALL (
SELECT
count(distinct pays)
FROM ParticipationEquipe
P
GROUP BY entraineur
)
(Q3.3) Donner le(s) joueur(s) qui ont occupé au moins deux postes
différents dans deux mondiaux différents ?
Corrigé :
SELECT J1.nom, J1.prenom,
J1.pays
FROM ParticipationJoueur
J1, ParticipationJoueur J2
WHERE J1.poste<>J2.poste AND
J1.nom=J2.nom AND J1.prenom=J2.prenom
AND J1.pays=J2.pays AND J1.annee <> J2.annee
Ou
SELECT nom, prenom,
pays
FROM ParticipationJoueur J
GROUP BY nom, prenom, pays
HAVING count (distinct poste) >=2
Question 4
Exprimer les 2 questions suivantes en algèbre relationnelle ou en SQL :
(Q4.1) Quelles sont les équipes qui n’ont jamais participé à un
mondial ?
Corrigé :
SELECT pays
FROM Pays
MINUS
SELECT pays
FROM ParticipationEquipe
(Q4.2) Quels sont les pays
qui ont participé à tous les mondiaux ?
Corrigé :
SELECT pays
FROM ParticipationEquipe
GROUP BY pays
HAVING count(annee) = (
SELECT
count(*)
FROM Mondial
)
Question 5
Donner la requête SQL de création de la relation ParticipationJoueur
sachant que
-
Les attributs nom, prenom, pays, et poste sont des chaînes de 50 caractères,
-
L’attribut annee est un entier supérieur à
1900,
-
les valeurs possibles de l’attribut poste sont gardien, défenseur, milieu et attaquant,
-
La clé de la relation ParticipationJoueur est composée par les attributs nom, prenom, pays et annee,
-
Les attributs nom, prenom
et pays constituent une clé étrangère
qui référence la relation Joueur.
L’attribut annee
est une clé étrangère qui référence la relation Mondial.
Corrigé :
CREATE table ParticipationJoueur
(
nom
VARCHAR(50),
prenom VARCHAR(50),
pyas
VARCHAR(50),
annee INTEGER,
poste
VARCHAR(50),
)
ALTER TABLE ParticipationJoueur
ADD CONSTRAINT Cannee
CHECK (annee>1900);
ALTER TABLE ParticipationJoueur
ADD
CONSTRAINT Cposte CHECK (poste IN
(‘gardien’,
‘défenseur’, ‘milieu’ , ‘attaquant’)) ;
ALTER TABLE ParticipationJoueur
ADD PRIMARY KEY (nom, prenom, pays, annee) ;
ALTER TABLE ParticipationJoueur
ADD CONSTRAINT refannee
FOREIGN KEY (annee)
REFERENCES
Mondial(annee) ON DELETE CASCADE ;
ALTER TABLE ParticipationJoueur
ADD CONSTRAINT refnomprenompays FOREIGN KEY (nom, prenom,
pays)
REFERENCES
Joueur(nom, prenom, pays) ON DELETE CASCADE ;
Posté le 13 septembre 2011