Institut National des Télécommunications
Contrôle des connaissances
|
Bases de données |
BD21 |
|
Date : 12/2/2003 |
Durée : 1h30 |
|
Coordonnateur : Samir |
Documents autorisés: |
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 : 4 points
Question 2 : 6 points (Q2.1
= 2, Q2.2 = 2, Q2,3=2)
Question 3 : 4 points (Q3.1
= 2, Q3,2 = 2)
Question 4 : 4 points (Q4,1
= 2, Q4,2 = 2)
Question 5 : 2 points
______________________________________________________________________________
La mairie d'Evry dispose d'une base de données
dont le schéma est le suivant:
IMMEUBLE (Adresse, NbEtages, DateCons)
APPARTEMENT (Adresse, NumApp
, Type, Superficie, Etage, Occup, Proprio)
PERSONNE (Nom, Age, Profession,
Adresse, NumApp)
Les clés sont soulignées
et les clés étrangères sont en gras. La sémantique
associée à ce schéma est la suivante:
La relation IMMEUBLE (Adresse, NbEtages,
DateCons) est identifiée par l'adresse (on fait l'hypothèse
pour simplifier, que l'adresse identifie de manière unique un
immeuble). Elle est caractérisée par un nombre d'étages,
et une date de construction.
La relation APPARTEMENT (Adresse
, NumApp, Type, Superficie, Etage, Occup, Proprio)
est identifiée par l'adresse d'immeuble et le numéro d'appartement
(Adresse, NumApp). Elle est caractérisée par, le type de
l'appartement (Studio, F2,...), sa superficie, l'étage où
se situe l'appartement, l'occupant de l'appartement (nom de la personne
ayant signé le contrat de location, éventuellement aucun)
et le nom du propriétaire de l'appartement. Chaque appartement peut
héberger plusieurs personnes mais il y en a une qui est responsable
(par exemple la personne qui a signé le contrat de location) et
qui est désignée par l'attribut Occup. Si l'appartement est
inoccupé, cet attribut prend la valeur NULL. Adresse est une clé
étrangère sur la relation IMMEUBLE. Proprio est une clé
étrangère sur la relation PERSONNE. Occup est une clé
étrangère sur la relation PERSONNE.
La relation PERSONNE (Nom, Age, Profession,
Adresse, NumApp) est identifiée par un nom de personne (on
fait l'hypothèse pour simplifier, que ce nom est unique sur l'ensemble
des personnes que l'on considère dans la base). Elle est caractérisée
par l'âge de la personne, sa profession et son adresse (il s'agit d'un
immeuble et d'un numéro d'appartement). les attributs Adresse et NumApp
constituent une clé étrangère sur la relation APPARTEMENT.
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 2 questions suivantes, d'une part en algèbre
relationnelle sous la forme d'un arbre algébrique,
et d'autre part en SQL :
(Q 2.1) Donner l'adresse des immeubles ayant plus de 10 étages
et construits entre 1970 et 1980.
Corrigé
SELECT Adresse
FROM Immeuble
WHERE NBEtages >= 10 and DateCons >=1970 and DateCons<=1980
(Q 2.2) Donner les noms des personnes qui habitent des appartements de
type 'F2'.
Corrigé
SELECT Nom
FROM Personne P, Appartement A
WHERE P.Adresse = A.Adresse and P.NumApp=A.NumApp and A.Type='F2'
(Q 2.3) Donner les noms des personnes qui ne sont pas propriétaires.
Corrigé
SELECT Nom
FROM Personne P
MINUS
SELECT Proprio Nom
FROM Appartement
Question 3
Exprimer les questions suivantes en SQL :
(Q 3.1)Donner le nombre d'appartements de l'immeuble dont l'adresse est
'9 rue Charles Fourier'
Corrigé
SELECT Count(*)
FROM Appartement
WHERE Adresse='9 rue Charles Fourier'
(Q 3.2)Donner l'adresse de l'immeuble qui a le plus grand nombre d'appartements.
SELECT A1.Adresse
FROM Appartement A1
GROUP BY A1.Adresse
HAVING count(*) >= ALL (SELECT count(*) FROM Appartement A2 GROUP BY A2.Adresse)
Question 4
Exprimer les questions suivantes en algèbre relationnelle (si possible)
ou en SQL :
(Q 4.1) Donner pour chaque personne, le nombre d'appartements dont il
est propriétaire.
Remarque: il existe des personnes qui ne sont propriétaires
d'aucun appartement.
Corrigé
SELECT Nom, 0
FROM Personne P
WHERE Nom NOT INT (SELECT SELECT Proprio Nom FROM Appartement)
UNION
SELECT Proprio Nom, count(*)
FROM Appartement
GROUP BY Proprio
(Q 4.2) Donner les noms des personnes qui sont propriétaires de
tous les appartements d'au moins un immeuble.
Corrigé
SELECT Proprio Nom, A.Adresse
FROM Appartement A, Immeuble I
WHERE A.Adresse=I.Adresse
GROUP BY I.Adresse, Proprio
HAVING count(*) = (SELECT count(*) FROM Appartement A2 WHERE A2.Adresse=
I.Adresse)
Question 5
On désire prototyper une application au dessus du schéma
relationnel de la base de données de la mairie d'Evry. Pour cela
on décide d’utiliser l’environnement de développement
à base de formulaires HTML utilisé dans le TP conception
et développement d’applications. On veut construire un formulaire
permettant de générer un formulaire d’insertion d’un
tuple dans la relation PERSONNE. Le développeur écrit le
formulaire suivant:
<html>
<body>
<h1>Insertion dans la table personne</h1><p>
<form name="f1" action="http://mica/~oracle/cgi-bin/multi2.cgi" method="GET">
<input type ="hidden" name="login" value="binom25/iobd@TPC">
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=’PERSONNES">
Deuxième erreur, le nom de la relation est PERSONNE et non pas
PERSONNES
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>
A l’exécution le développeur n’obtient pas le
résultat escompté. Pouvez vous l’aider en trouvant
les 4 erreurs contenues dans ce formulaire.
Posté le 4 septembre 2009