Tutoriel de Bases de Données Relationnelles

Accueil > Supports pédagogiques > Annales > Ingénieurs > EI 03 corrigé

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>

Haut de page

[fil RSS du site]

Dernière mise à jour : 04/09/2009