Corrigé du contrôle pour les RISR 02/03

Institut National des Télécommunications

Contrôle des connaissances

Bases de données	ISR75
Date : 2/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 : 7 points

Question 2 : 4 points (Q2.1 = 2, Q2.2 = 2)

Question 3 : 6 points (Q3.1 = 1, Q3.2 = 1,
Q3.3 = 2, Q3.4 = 2)

Question 4 : 3 points

______________________________________________________________________________

Une mairie souhaite disposer d'une base de
données afin de gérer ses différentes données. Le schéma proposé pour cette
base de données est le suivant:

IMMEUBLE (Adresse, NbEtages, DateCons,
Proprio)

APPARTEMET (Adresse, NumApp,
Occup, Type, Superficie, Etage)

PERSONNE (Nom, Age, Profession, Adresse,
NumApp)

ECOLE (NomEc, AdresseEc, NbClasses,
Directeur)

CLASSE (NomEc, NomCl, Maitre,
NbEl)

ENFANT (NomP, Prenom, An, NomEc,
NomCl)

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:

Larelation IMMEUBLE (Adresse, NbEtages,
DateCons, Proprio) est identifiée par Adresse d'immeuble (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, une da date de
construction et le nom du propriétaire de l'immeuble. Proprio est une clé
étrangère sur la relation PERSONNE.

La relation APPARTEMET (Adresse,
NumApp, Occup, Type, Superficie, Etage) est identifiée par l'adresse
d'immeuble et le numéro d'appartement (Adresse, NumApp). Elle est caractérisée
par l'occupant de l'appartement (nom de la personne ayant signe le contrat
de location, eventuellement aucun), le type de l'appartement (Studio, F2,
...), sa superficie et l'étage où se situe l'appartement. Chaque appartement
peut héberger plusieurs personnes mais il y en a une qui est responsable
(par exemple la personne qui a signe le contrat de location) et qui est désignée
par le constituant Occup. Si l'appartement est inoccupé, ce constituant prend
la valeur NULL. Adresse est une clé étrangère sur la relation IMMEUBLE et
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 son adresse de la résidence d'une personne, il s'agit d'un
immeuble et d'un numéro d'appartement. Adresse et NumApp est une clé étrangère
sur la relation APPARTEMENT.

La relation ECOLE (NomEc, AdresseEc,
NbClasses, Directeur) est identifiée par un nom et caractérisée
par adresse de l'école, le nombre de classes qu'elle comporte et le nom de
son directeur. Directeur est une clé étrangère sur la relation PERSONNE.

La relation CLASSE (NomEc, NomCl,
Maitre, NbEl) est identifiée par le nom d'une école et le nom de
la classe, e.g., CP1, CE2, CE3, etc... Elle est caractérisée par le nom de
l'instituteur et le nombre d'élèves dans la classe. Maître est une clé étrangère
sur la relation PERSONNE.

La relation ENFANT (NomP, Prenom,
An, NomEc, NomCl) est identifiée par le nom de la personne responsable
de l'enfant (e.g., père, mère etc...) et le prénom de l'enfant. Elle est
caractérisée par l'année de naissance de l'enfant, le nom de son école et
le nom de sa classe. NomEc et NomCl est une clé étrangère sur la relation
CLASSE.

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 :

(Q 2.1) Donner l'adresse des immeubles ayant
plus de 10 étages et construits avant 1970.

Corrigé

SELECT Adresse
FROM Immeuble
WHERE NBEtages >= 10 and DateCons <=1970

(Q 2.2) Donner les noms des personnes qui
habitent dans un immeuble dont ils sont propriétaires.

Corrigé

SELECT P.Nom
FROM Appartement A, Immeuble I, Personne P
WHERE A.Adresse=I.Adresse and
                A.Adresse=P.Adresse and
                A.NumApp=P.numApp and
                P.Nom=I.Proprio

Question 3

Exprimer les questions suivantes en algèbre
relationnelle ou en SQL :

(Q 3.1) Donner les noms des personnes qui
ne sont pas propriétaires.

Corrigé

SELECT Nom
FROM Personne P
MINUS
SELECT Proprio Nom
FROM Immeuble

(Q 3.2) Donner pour chaque école, son nom
et le nombre d'élève quelle comporte.

Corrigé

SELECT E.NomEc, sum(NBEl)
FROM Ecole E, Classe C
WHERE E.NomEc=C.NomEc
GROUP BY E.NomEc

(Q 3.3) Donner les noms des maîtres qui habitent
dans le même immeuble qu'au moins un de leurs élèves (on suppose que les
enfants vivent sous le même toit que leur parents).

Corrigé
SELECT C.Maitre
FROM Classe C Enfant E, Personne P1, PersonneP2
WHERE E.NomEc=C.NomEc and E.NomCl=C.NomCl
and E.Nom=P1.Nom and C.Maitre=P2.Nom
and P1.Adresse=P2.adresse

(Q 3.4) Donner pour chaque personne, le nombre
d'appartements dont il est propriétaire.

Corrigé

SELECT Nom, 0
FROM Personne P
WHERE Nom NOT INT (SELECT SELECT Proprio Nom FROM Immeuble)
UNION
SELECT Proprio Nom, count(*)
FROM Immeuble I, Appartement A
WHERE I.Adresse=A.Adresse
GROUP BY Proprio

Question 5

Etant donné un numéro de vin, le formulaire
suivant vous permet de supprimer le tuple du vin correspondant.

<html>

<body>

<h1>SUPPRESSION DANS LA BASE DES VINS</h1>

<hr>

Numéro de vin à supprimer : <input name="numero"
value=""><p>

</form>

</body>

</html>

Adapter ce code pour supprimer une classe
étant donnés le nom de l'école et le nom de la classe correspondants.

<html>

<body>

<h1>SUPPRESSION DANS LA BASE DES CLASSES</h1>
<hr>

<form name="supclasse" action="http://mica.int-evry.fr/cgi-bin/multi2.cgi"
target="result" method="POST">

<input type="hidden" name="uid" value=" citcom/citcom@MICA ">

<input type="hidden" name="mode" value="SUP">

<input type="hidden" name="temp1" value=" select * from classe
where NomEc=">

<input type="hidden" name="temp2" value=" and NomCl = ">

<input type="hidden" name="sqlstatement" value="">

Numérom de la discipline à supprimer : <input
name="NomEc" value="><p>

Numéro de la salle à supprimer : <input
name="NomCl" value="><p>

<input type="button" value="supprimer" onClick = "supclasse.sqlstatement.value
= supepreuve.temp1.value + supclasse.NomEc.value + supclasse.temp2.value
+ supclasse.NomCl.value;supclasse.submit(); ">

</form>

</body>

</html>

Posté le 4 septembre 2009

Enseignement des Bases de Données

Ressources pédagogiques

Menu