Tutoriel de Bases de Données Relationnelles

Accueil > Supports pédagogiques > Annales > Ingénieurs > RISR 02 corrigé

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>

Haut de page

[fil RSS du site]

Dernière mise à jour : 04/09/2009