Institut TELECOM ; TELECOM SudParis
Contrôle des connaissances
|
Bases de données |
Code : CSC4001 |
|
Date : 09/11/2010 |
Durée : 1h30 |
|
Contrôle proposé par Samir Tata |
Documents autorisés: ceux distribués en cours |
____________________________________________________
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 ;