Tutoriel de Bases de Données Relationnelles
Corrigé du contrôle pour les EI 01/02
INSTITUT NATIONAL DES TELECOMMUNICATIONS
CONTROLE DES CONNAISSANCES
Bloc : Bases de données Code : BD21
Durée : 1h30 Date : 11/01/2002
Documents autorisés : Coordonnateurs :
ceux distribués en cours Mme Carpentier
____________________________________________________
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 : 6 points (Q3.1 = 2 pts, Q3.2 = 2 pts, Q3.3 = 2 pts)
Question 4 : 3 points (Q4.1 = 1,5 pts, Q4.2 = 1,5 pts)
Question 5 : 2 points (Q5 = 2pts)
Un pépiniériste souhaite disposer d'une bases de données permettant de gérer ses espèces d'arbres. Le schéma proposé pour cette base de données est le suivant :
FamilleArbre(nomFamille, caduque, sol)
EspeceArbre(nomFamille, nomEspece, ageMaturite, taille, descriptif)
Climat(nomClimat, tempMin, tempMax)
Region(nomRegion, ville, climat)
Peuplement(nomFamille, nomEspece, region, txCouverture)
Les clés sont soulignées et les clés étrangères sont en gras.
La sémantique des diverses relations est la suivante :
FamilleArbre(nomFamille, caduque, sol) : une famille d'arbre est identifiée par son nom ("Pin" par exemple), le fait que les feuilles soient caduques ou non et le type de sol sur lequel elle pousse ("Sable" par exemple) ;
EspeceArbre(nomFamille, nomEspece, ageMaturite, taille, descriptif) : au sein d'une même famille ("Pin" par exemple), on distingue plusieurs espèce ("Parasol", "Maritime", "Alep", "Laricio" pour n'en citer que quelques unes). Chaque espèce est décrite par l'age de sa maturité, la taille moyenne des sujets adultes et un descriptif ;
Climat(nomClimat, tempMin, tempMax) : un climat est identifié par son nom("Océanique" par exemple) et est décrit par sa température minimale et sa température maximale ;
Region(nomRegion, ville, climat) : Une région est identifiée par son nom ("Bretagne" par exemple) et est décrite par la ville principale de cette région ("Rennes" par exemple) et son climat ;
Peuplement(nomFamille, nomEspece, region, txCouverture) : pour chaque espèce (définie par son nom de famille et son nom d'espèce) présente dans une région, le taux de couverture de cette espèce est précisé ("43", signifiant 43 %, par exemple).
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.
(Q2.1) Quelles sont les espèces (nom de famille, nom d'espece et descriptif) qui pousse sur un sol de type "Argile" ?
SELECT nomFamille, nomEspece, descriptif
FROM EspeceArbre E, FamilleArbre F
WHERE E.nomFamille = F.nomFamille
AND sol='Argile'
(Q2.2) Quelles sont
les espèces (nom de famille, nom d'espece) qui peuplent les régions
ayant un climat avec une amplitude thermique (différence entre la
température maximale et la température minimale) supérieure à 45 ° ?
SELECT nomFamille, nomEspece
FROM Peuplement P, Region R, Climat C
WHERE P.nomRegion = R.nomRegion
AND R.nomClimat = C.nomclimat
AND (tempMax - tempMin) > 45
Question 3
Exprimer les 3 questions suivantes en SQL :
(Q3.1) Quel est le nombre de régions peuplées par des pins parasols et des pins maritimes ?
SELECT count(*)
FROM Peuplement
WHERE nomFamille='Pin'
AND (nomEspece='Parasol' OR nomEspece='Maritime')
(Q3.2) Donner pour chaque région et pour chaque famille d'arbre, le taux de couverture moyen et le nombre d'espèces de cette famille.
SELECT nomregion, nomFamille, AVG(txCouverture), COUNT(*)
FROM Peuplement
GROUP BY nomRegion, nomFamille
(Q3.3) Quelle est
la région proposant le plus de diversité d'espèce. On souhaite avoir le
nom de la région, la ville principale et son climat.
SELECT R.nomRegion, R.ville, R.climat
FROM Region R, Peuplement P
WHERE R.nomRegion=P.region
GROUP BY R.nomRegion, R.ville, R.climat
HAVING COUNT(*) = (
SELECT MAX(COUNT(*))
FROM Peuplement
GROUP BY nomRegion)
Question 4
Exprimer les 2 questions suivantes en algèbre relationnelle ou en SQL :
(Q4.1) Quelles sont les espèces (nom de Famille, nom d'espèce et descriptif) qui ne peuplent aucune région ?
SELECT nomFamille, nomEspece, descriptif
FROM EspeceArbre
WHERE nomFamille, nomEspece IN
(SELECT nomFamille, nomEspece
FROM EspeceArbre
MINUS
SELECT nomFamille, nomEspece
FROM Peuplement)
(Q4.2) Quels sont les espèces (nom de famille et nom d'espèce) présentes dans tous les climats ?
SELECT nomFamille, nomEspece
FROM Peuplement P, Region R
WHERE P.nomRegion=R.nomRegion
GROUP BY nomFamille, nomEspece
HAVING COUNT(DISTINCT nomClimat) =
(SELECT COUNT(*)
FROM Climat)
Question 5 /* non corrigé */
Détachez cette feuille, inscrivez vos noms et prénoms et insérez-la dans votre copie.
Répondez au QCM suivante. Plusieurs propositions peuvent être vraies, plusieurs peuvent être fausses.
Q5.1) XML est un langage :
|
Vrai |
Faux |
|
Vrai |
Faux |
|
Vrai |
Faux |
|
Vrai |
Faux |
Q5.2) HTML permet de :
|
Vrai |
Faux |
|
Vrai |
Faux |
|
Vrai |
Faux |
Q5.3) Un CGI (Common Gateway Interface) est :
|
Vrai |
Faux |
|
Vrai |
Faux |
|
Vrai |
Faux |
Q5.4) Un navigateur web est :
|
Vrai |
Faux |
|
Vrai |
Faux |
|
Vrai |
Faux |
|
Vrai |
Faux |