INSTITUT NATIONAL DES TELECOMMUNICATIONS
CONTROLE DES CONNAISSANCES
Bloc : Bases de données Code : BD21
Durée : 1h30 Date : 22/02/2000
Documents autorisés : Coordonnateurs :
ceux distribués en cours Mme Carpentier, Mr Defude
____________________________________________________
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)
La région Ile de France nous demande de gérer une base de données dont le schéma est le suivant :
DEPARTEMENT(numdept, nomdept, superficie)
VILLE(numdept, nomville, nbhabitants)
SITE(nomsite, descriptif, type, prix)
LOCALISATIONSITE(nomsite, numdept, nomville)
HOTEL(numdept, nomville, nomhotel, adresse, nbétoiles)
Les clés sont soulignées et les clés étrangères sont en gras.
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) Donner le nom des villes (ainsi que leur nombre d’habitants) du département de nom ‘Essonne’.
SELECT V.nomville, V.nbhabitants
FROM VILLE V, DEPARTEMENT D
WHERE V.numdept=D.numdept AND D.nomdept=’Essonne’
(Q2.2) Donner le nom et le prix des sites situés dans la ville de nom ‘Versailles’ et de numéro de département 78.
SELECT S.nomsite, S.prix
FROM SITE S, LOCALISATIONSITE L
WHERE S.nomsite=L.nomsite AND L.numdept=78 AND L.nomville=’Versailles’
Question 3
Exprimer les 3 questions suivantes en SQL :
(Q3.1) Donner le nombre de sites d’accès gratuit du département de numéro 92.
SELECT COUNT(*)
FROM SITE S, LOCALISATIONSITE L
WHERE S.nomsite=L.nomsite AND L.numdept=92 AND S.prix=0
(Q3.2) Donner pour chaque département (numéro et nom) le nombre d’hôtels 2 étoiles qui le compose.
SELECT D.numdept, D.nomdept, COUNT(*)
FROM DEPARTEMENT D, HOTEL H
WHERE D.numdept=H.numdept AND H.nbétoiles=2
GROUP BY D.numdept, D.nomdept
Bien penser à grouper par nomdept bien que cela ne
soit pas nécessaire au niveau du groupement, mais cela permet de
pouvoir le sélectionner.
(Q3.3) Donner le numéro du département qui possède le plus grand nombre de sites.
SELECT numdept
FROM LOCALISATIONSITE
GROUP BY numdept
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
FROM LOCALISATIONSITE GROUP BY numdept)
Autre écriture possible :
SELECT numdept
FROM LOCALISATIONSITE
GROUP BY numdept
HAVING COUNT(*) >= ALL (SELECT COUNT(*)
FROM LOCALISATIONSITE GROUP BY numdept)
Question 4
Exprimer les 2 questions suivantes en algèbre relationnelle ou en SQL :
(Q4.1) Donner les villes (nom et nombre d’habitants) qui ne possèdent aucun hôtel.
SELECT nomville, nbhabitants
FROM VILLE
MINUS
SELECT V.nomville, V.nbhabitants
FROM VILLE V, HOTEL H
WHERE V.nomville=H.nomville AND V.numdept=H.numdep
On fait la différence entre les villes et les villes
qui possèdent un hôtel. Ici faire attention à bien se ramener au même
schéma entre les deux requêtes (nomville et nbhabitants pour les deux).
(Q4.2) Donner les départements (numéro et nom) qui possèdent au moins un site de chaque type.
SELECT D.numdept, D.nomdept
FROM DEPARTEMENT D, LOCALISATIONSITE L, SITE S
WHERE D.numdept=L.numdept AND L.nomsite=S.nomsite
GROUP BY D.numdept, D.nomdept
HAVING COUNT(DISTINCT S.type)=(SELECT COUNT(DISTINCT type) FROM SITE)
Il faut penser à utiliser le DISTINCT pour ne pas compter plusieurs fois le même type de site.
Pour la division il faut d’une part calculer le
diviseur (tous les types de sites) et d’autre part le dénominateur (les
triplets numdept, nomdept et type de site). Le résultat est l’ensemble
des couples (numdept et nomdept) qui sont associés à tous les types de
site.
Question 5
Voici les erreurs dans le formulaire.
<html>
<body>
<h1>Insertion dans site</h1><p>
<form name=’f1’ action=’http://mica/~oracle/cgi-bin/mowi_sql.cgi’method=’GET’>
<input type =’hidden’ name=’login’ value=’conc5/conc@MICA’>
Première erreur, le nom de la variable doit être uid et non pas login
<input type=’hidden’ name=’sqlstatement’ value=’’>
<input type=’hidden’ name=’table’ value=’SITES’>
Deuxième erreur le nom de la relation est SITE et non pas SITES.
Troisième erreur, il manque la déclaration de la variable de nom mode avec comme valeur INS.
<input type=’button’ value=’inserer’ onClick=’for1.submit() ;’>
Quatrième erreur, le formulaire s’appelle f1 et on soumet un formulaire de nom for1.
</form>
</body>
</html>
Posté le 4 septembre 2009