Enseignement des Bases de Données

Institut National des Télécommunications

Contrôle des connaissances

Bases de données	Code : BD21
Date : 08/11/2005	Durée : 1h30
Coordonnateur : C. Lecocq et S. 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 (Q1 = 5
pts)

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

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

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

Question 5 : 1,5 point (Q5.1 = 1pt,
Q5.2 = 0,5pt)

Une ludothèque souhaite organiser les jeux proposés aux
enfants en fonction de leur but pédagogique et des compétences développées.
Afin de proposer des activités adéquates aux enfants clients de cette
ludothèque, le schéma de base de données suivant est défini :

FamilleJeux (nomFamille, butPedagogique)

Jeux (nomJeux, prix,
description, ageMini, ageMaxi, nomFamilleJeux)

Accessoire (nomJeux,
nomAccessoire, prix)

Compétence (nomCompetence,
description, ageMiniAdapte, ageMaxiAdapte)

Developper (nomJeux, nomCompetence)

Les clés sont soulignées et les clés étrangères sont en
gras.

La sémantique des diverses relations est la suivante :

FamilleJeux (nomFamille, butPedagogique) : une
famille de jeux est identifiée par son nom « nomFamille ».
Les seules valeurs possibles de nomFamille
sont : « jeux de société », « loisirs créatifs »,
« jeux d’imitation ». Le but pédagogique « butPedagogique » de cette famille est associé.

Jeux (nomJeux, prix, description, ageMini, ageMaxi, nomFamilleJeux) :
un jeux est identifié par son nom « nomJeux ».
Il est décrit par son prix « prix »,
une description textuelle « description »,
un age minimum « ageMini »,
un age maximum « ageMaxi »
et le nom de la famille de jeux auquel il appartient « nomFamilleJeux »; clé étrangère
sur la relation « FamilleJeux ».

Accessoire (nomJeux, nomAccessoire,
prix) : un accessoire est identifié par son nom « nomAccessoire » et le nom du jeux auquel il doit
être associé « nomJeux ». Il
possède un prix « prix ». « nomJeux » est une clé étrangère
sur la relation « Jeux ».

Compétence (nomCompetence, description,
ageMiniAdapte, ageMaxiAdapte) : une compétence est identifiée par son nom « nomCompétence » (« motricité
fine », « stratégie », « socialisation » par exemple).
Une compétence est décrite par un attribut « description »,
un âge minimum « ageMiniAdapte »
avant lequel cette compétence ne peut pas être développée, un âge maximum « ageMaxiAdapte » après lequel
cette compétence est acquise et n’a plus à être développée.

Developper (nomJeux, nomCompetence) : le jeux
de nom « nomJeux »
développe la compétence de nom « nomCompetence ».
« nomJeux » est une clé étrangère sur la relation « Jeux ». « nomCompetence » est une clé étrangère sur la relation « Competence ».

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 ou
sous forme textuelle), et d'autre part en SQL.

(Q2.1) Quels sont les jeux
(nom de jeux et prix) qui coûtent moins de 20 Euro ?

SELECT nomJeux, prix

FROM Jeux

WHERE prix < 20

 

(Q2.2) Quels sont les noms
des jeux et les noms des accessoires de jeux qui développent la compétence
« motricité fine » ? La compétence développée par un accessoire
est la même que celle du jeux auquel il est rattaché.

SELECT nomJeux

FROM Developper
D

WHERE
D.nomCompetence =’motricité fine’

UNION

SELECT nomAccessoire

FROM Developper
D, Accessoire A

WHERE D.nomJeux
= A.nomJeux

AND
D.nomCompetence =’motricité fine’

Question 3

Exprimer les 3 questions suivantes en SQL :

(Q3.1) Pour chaque famille
de jeux, donner le nom de la famille, le nombre de jeux appartenant à cette
famille et le prix moyen des jeux de cette famille.

SELECT nomFamilleJeux,
COUNT(*) , AVG (prix)

FROM Jeux J

GROUP BY
nomFamilleJeux

(Q3.2) Donner le nom des jeux
et leur prix chacun des accessoires est plus cher que le jeux lui-même (le
minimum du prix des accessoires est supérieur au prix du jeux).

SELECT nomJeux, prix

FROM Jeux J

WHERE prix <=
ALL

                (SELECT
prix

                FROM
Accessoire A

                WHERE A.nomJeux =
J. nomJeux)

2^ème solution

SELECT  nomJeux, prix

FROM Jeux J

WHERE prix <=

                (SELECT
MIN(prix)

                FROM
Accessoire A

                WHERE
A.nomJeux = J. nomJeux)

(Q3.3) Quel est le jeux
développant le maximum de compétences ?

SELECT nomJeux

FROM Developper
D

GROUP BY nomJeux

HAVING COUNT(*)
>=

                SELECT
MAX(COUNT(*))

                FROM
Developper

                GROUP
BY nomJeux)

Question 4

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

(Q4.1) Quels sont les jeux
(nom de jeux) développant toutes les compétences ?

SELECT nomJeux

FROM Developper

GROUP BY nomJeux

HAVING COUNT(*)
= select COUNT(*) FROM Competence

(Q4.2) Quels sont les jeux
(nom de jeux et description) qui ne sont pas de la famille « jeux
d’imitation » ?

SELECT nomJeux, description

FROM Jeux

Minus

SELECT nomJeux, description

FROM Jeux

WHERE nomFamilleJeux=’jeux
d’imitation’

2^ème solution

SELECT DISTINCT nomJeux,
description

FROM Jeux

WHERE nomFamilleJeux<>’jeux
d’imitation’

 

Question 5

(Q5.1) Ecrire l’ordre de création
de la relation « FamilleJeux » en SQL. Cette création doit contenir
la définition des contraintes d’intégrité (contrainte de clé, clé étrangère,
contraintes de domaine …).

Vous considérerez pour cela que
l’attribut « nomFamille » est une chaîne de 16 caractères et que
dont les valeurs possibles sont : « jeux de société »,
« loisirs créatifs », « jeux d’imitation ». L’attribut « butPedagogique » est une chaîne de 30 caractères.

CREATE TABLE FamilleJeux (

nomFamille
CHAR(16) PRIMARY KEY CHECK (nomFamille IN (‘jeux de société’,’loisirs
créatifs’,’jeux d’imitation’)),

butPedagogique
CHAR(30)) ;

(Q5.2) Que doit faire le
formulaire suivant ?

Interrogation du nombre d’accessoire par jeux. Le compte
interrogé est sur l’instance de base de données TANNA. Le login est titi et le
mot de passe titi.