Corrigé du contrôle pour les EI 03/04

Institut National des Télécommunications

Contrôle des connaissances

Bases de données	Code : BD21
Date : 19/12/2003	Durée : 1h30
Coordonnateur : 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 : 5 points

Question 2 : 4 points (Q2.1 = 2, Q2.2
= 2)

Question 3 : 5,5 points (Q3.1 = 2, Q3.2
= 1,5, Q3.3 = 2)

Question 4 : 3,5 points (Q4.1 = 1,5,
Q4.2 = 2)

Question 5 : 2 points

______________________________________________________________________________

Le comité d'organisation des jeux olympiques d'Athènes
souhaite disposer d'une base de données afin de gérer les
différentes épreuves. Le schéma proposé pour
cette base de données est le suivant:

Pays(codePays, pays, continent)

Athletes(numAthlete, nom, prenom, codePays)

Discipline(nomDiscipline, dateDebut, dateFin)

Epreuve(nomDiscipline, numEpreuve, nomEpreuve,
sexe)

Medaille(nomDiscipline, numEpreuve, numAthlete,
couleur)

Les clés sont soulignées et les clés
étrangères sont en gras. La sémantique associée
à ce schéma est la suivante:

Pays(codePays, pays, continent) un pays est identifié
d'une manière unique par un code et caractérisé par
un nom officiel (‘France’ par exemple) et un continent (‘Europe’
par exemple).

Athletes(numAthlete, nom, prenom, codePays) un athlète
est identifié d'une manière unique par un numéro et
caractérisé par un nom, un prénom et le code de son
pays. codePays est une clé étrangère sur la relation
Pays.

Discipline(nomDiscipline, dateDebut, dateFin) une
discipline est identifiée d'une manière unique par un nom
('gymnastique' par exemple) et caractérisée par une date de
début et une date de fin.

Epreuve(nomDiscipline, numEpreuve, nomEpreuve,
sexe) une épreuve est identifiée d'une manière unique
par le nom de la discipline dont elle fait partie et un numéro d’épreuve.
Elle est caractérisée par un nom ('100m' par exemple) et un
sexe ('homme' ou 'femme'). nomDiscipline est une clé étrangère
sur la relation Discipline.

Medaille(nomDiscipline, numEpreuve, numAthlete,
couleur) une médaille est identifiée d'une manière
unique par le nom de la discipline, le numéro de l'épreuve
et le numéro de l’athlète. Elle est caractérisée
par une couleur ('or', 'argent' ou 'bronze'). nomDiscipline et numEpreuve
sont une clé étrangère sur la relation Ep

reuve et numAthlete est une clé étrangère
sur la relation Athletes. Un tuple dans cette relation indique qu'un athlète
(identifié par son numéro) a remporté une médaille
(caractérisée par une couleur) dans une épreuve (identifiée
par un nom de discipline et un numéro d'épreuve).

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.

Question 2

Exprimer chacune des 2 questions suivantes, d'une part
en algèbre relationnelle sous la forme d'un arbre algébrique
, et d'autre part en SQL :

(Q 2.1) Donner les noms des épreuves destinées
aux femmes dans la discipline ‘gymnastique’.

Select nomEpreuve
From Epreuve
Where E.sexe='femme' and nomDiscipline='gymnastique'

(Q 2.2) Donner le prénom et le nom de l'athlète
ayant gagné la médaille d'or pour l'épreuve de nom
‘marathon’ destinée aux hommes.

Select A.prenom, A.nom
From Athletes A, Epreuve E, Medaille M
Where A.numAthlete=E.numAthlete and
E.nomDiscipline=M.nomDiscipline and
E.numEpreuve=M.numEpreuve and
M.couleur='or' and
E.nomEpreuve='marathon' and E.sexe='homme'

Question 3

Exprimer les 3 questions suivantes en SQL
:

(Q 3.1) Donner
les numéros des athlètes qui n’ont pas remporté
de médaille d'or.

Select numAthlete
From Athletes
Minus
Select numAthlete
From Medaille
Where couleur='or'

(Q 3.2) Quel est le nombre de médailles gagnées
par l'athlète ‘El Guerouch’?

Select count(*)
From Medaille M, Athlete A
Where A.nom='El Guerouch' and M.numAthlète= A.numAthlète

(Q 3.3) Donner pour chaque discipline son nom et le nombre
d'épreuves qu'elle comporte.

Select nomDiscipline, 0
From Discipline
Where nomDiscipline not in ( select nomDicipline from Epreuve)
Union
Select nomDiscipline, count(*)
From Epreuve
Group by nomDiscipline

Question
4

Exprimer les 2
questions suivantes en algèbre relationnelle ou en SQL :

(Q 4.1) Donner
les numéros des athlètes qui ont remporté aux moins
deux médailles d'or.

Select numAthlete
From Medaille
Where couleur = 'or'
Group by numAtlete
Having count(*) >=2

(Q 4.2) Donner
les numéros des athlètes qui ont remporté une médaille
dans toutes les épreuves de la discipline 'gymnastique'.

Select M.numAthlete
From Epreuve E, Medaille M
Where M.nomDiscipline = E.nomDiscipline and
M.numEpreuve=E.numEpreuve and
E.nomDiscipline = 'gymnastique'
Gourp by M.numAthlete
Having count(*) = ( Select count(*) From Epreuve where nomDiscipline
= 'gymnastique' )

Question5

Etant donné un numéro
de vin, le formulaire suivant vous permet de supprimer le tuple du vin correspondant.

<html>

<body>

<h1>SUPPRESSION DANS LA BASE DES VINS</h1>

<hr>

Numéro de vin à supprimer
: <input name="numero" value=""><p>

</form>

</body>

</html>

Adapter
ce code pour supprimer une épreuve étant donnés le numéro
de la discipline et le numéro de l’épreuve correspondants.

<html>

<body>

<h1>SUPPRESSION DANS LA BASE DES
EPREUVES</h1> <hr>

<form name="supepreuve" action="http://mica.int-evry.fr/cgi-bin/multi2.cgi"
target="result" method="POST">

<input type="hidden" name="uid" value="
citcom/citcom@MICA ">

<input type="hidden" name="mode" value="SUP">

<input type="hidden" name="temp1" value="
select * from epreuve where nomDiscipline=">

<input type="hidden" name="temp2" value="
and numEpreuve = ">

<input type="hidden" name="sqlstatement"
value="">

Numérom de la discipline à
supprimer : <input name="nomDiscipline" value="><p>

Numéro de la salle à
supprimer : <input name="numEpreuve" value="><p>

<input type="button" value="supprimer"
onClick = "supepreuve.sqlstatement.value = supepreuve.temp1.value + supepreuve.nomDiscipline.value
+ supepreuve.temp2.value + supepreuve.numEpreuve.value;supepreuve.submit();
">

</form>

</body>

</html>

Posté le 4 septembre 2009

Enseignement des Bases de Données

Ressources pédagogiques

Menu