Institut National des Télécommunications
Contrôle des connaissances
|
Bases de données |
Code : BD21 |
|
Date : 12/01/2007 |
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 (Q1 = 5 pts)
Question 2 : 4 points (Q2.1 = 2 pts, Q2.2 = 2 pts)
Question 3 : 5 points (Q3.1 = 1 pt, Q3.2 = 2 pts, Q3.3 = 2 pts)
Question 4 : 4 points (Q4.1 = 2 pts, Q4.2 = 2 pts)
Question 5 : 2 points (Q5.1 = 1 pt, Q5.2 = 1 pt)
Afin de réaliser un site Web sur les planètes et astres de notre galaxie, on a
créé une base de données qui permet de stocker des informations diverses les
concernant. Pour cela, le schéma de base de données suivant est défini :
Astre (nomAstre,
diamètre)
Planète (nomAstre,nomPlanete, diamètre, masse, tempsRevolution)
Astrophysicien (nom, prenom, pays)
Astéroïde (nomAstéroïde,
masse, nom, prenom)
Collision (nomAstre,
nomPlanete, nomAsteroide,
date)
Astre (nomAstre,
diamètre) : un astre (par exemple le soleil) est identifié par un nom et
caractérisé par un diamètre.
Planète (nomAstre,
nomPlanete, diamètre, masse, tempsRévolution) :
une planète (par exemple la terre) est identifiée par un nom et le nom de l’astre
autour duquel elle tourne. Elle est caractérisée par un diamètre, une masse et
son temps de révolution, c’est-à-dire, le temps mis pour faire le tour de
l’astre autour duquel elle est en rotation. Par exemple, la terre met 365 jours
environ pour faire le tour du soleil. L’attribut nomAstre
est une clé étrangère sur la relation Astre.
Astrophysicien (nom, prenom,
pays) : un astrophysicien est identifié par un nom et un prénom. Il est
caractérisé par un pays d’origine.
Astéroïde (nomAsteroide,
masse, nom, prenom) : un astéroïde est
identifié par un nom et caractérisé par une masse et le nom et le prénom de
l’astrophysicien qu’il l’a découvert. nom et prénom constituent une clé étrangère sur la relation Astrophysicien.
Collision (nomAstre,
nomPlanète, nomAstéroïde,
date) : une collusion est identifiée par le nom d’une planète (nomPlanète), le nom de l’astre autour duquel la planète
tourne (nomAstre) et le nom d’un astéroïde (nomAstéroïde). Elle est caractérisée par la date de
collusion. nomAstre et
nomPlanète constituent une clé étrangère sur la
relation Planète et nomAstéroïde est une clé
étrangère sur la relation Astéroïde.
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.
Corrigé
Question 2
Exprimer chacune des 3 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
astres (nomAstre et diamètre) dont le diamètre
dépasse 1 million de kilomètre ?
Corrigé :
SELECT nomAstre, diamètre
FROM Astre
WHERE diamètre >=1 000 000
(Q2.2) Quelles sont les
planètes (nomPlanète) qui tournent autour de l’astre
soleil et dont le temps de révolution dépasse 500 jours?
Corrigé :
SELECT nomPlanète
FROM Astre A, Planète P
WHERE A.nomAstre = P.nomPlanète
AND tempsRévolution >= 500
Question 3
Exprimer les 2 questions suivantes en SQL :
(Q3.1) Pour chaque astrophysicien,
donner son prénom, son nom et le nombre d’astéroïdes qu’il a découvert.
Corrigé :
SELECT prenom,
nom, count (DISTINCT nomAstéroïde)
FROM Astéroïde
GROUP BY prenom, nom
(Q3.2) Quels sont les
astres possédant le maximum de planètes ?
Corrigé :
SELECT nomAstre
FROM Planète
GROUP BY nomAstre
HAVING count (*) >= ALL (SELECT count (*)
FROM Planète
GROUP BY nomAstre)
(Q3.3) Quels sont les
astrophysiciens qui n’ont découvert aucun astéroïde ?
Corrigé :
SELECT prenom, nom
FROM Astrophysicien
MINUS
SELECT prenom, nom
FROM Asteroide
Question 4
Exprimer les 2 questions suivantes en SQL ou en
algèbre relationnelle si possible:
(Q4.1) Quelles sont les
planètes qui sont concernées par le maximum de collusions possibles ?
Corrigé :
SELECT nomPlanète,
nomAstre
FROM Collusion
GROUP BY nomPlanète, nomAstre
HAVING count(*) >= ALL (SELECT count (*)
FROM
Collusion
GROUP BY nomPlanète, nomAstre)
(Q4.2) Quels sont les
astres (nomAstre) dont toutes les planètes sont
concernées par des collusions.
Corrigé :
SELECT P.nomAstre
FROM Planete P
GROUP BY P.nomAstre
HAVING count(DISTINCT P.nomPlanete)
= (SELECT count(nomPlanète)
FROM Collusion C
WHERE P.nomAstre
= C.nomAstre)
Question 5
(Q5.1) Ecrire la requête
SQL de création de la relation Astre. Vous considérerez pour cela que
l’attribut nomAstre est
une chaîne de 16 caractères et l’attribut diamètre est un réel positif.
Corrigé :
CREATE
TABLE Astre (nomAstre VARCHAR(16) PRIMARY KEY diametre
NUMBER)
(Q5.1) Ecrire l’ordre SQL
d’insertion du tuple ( ‘Soleil’, 1392530) dans la
relation Astre
Corrigé :
INSERT INTO
Astre VALUES (‘Soleil’, 1392530)
Posté le 4 septembre 2009