Tutoriel de Bases de Données Relationnelles

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)