Tutoriel de Bases de Données Relationnelles

Corrigé du contrôle 2 pour les EI 03/04

Institut National des Télécommunications

Contrôle des connaissances

Bases de données	BD21
Date : 12/2/2003	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 : 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.