Tutoriel de Bases de Données Relationnelles

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.