Tutoriel de Bases de Données Relationnelles

Accueil > Supports pédagogiques > Annales > Ingénieurs > EI 98 corrigé

Corrigé du contrôle pour les EI 98/99

INSTITUT NATIONAL DES TELECOMMUNICATIONS

CONTROLE DES CONNAISSANCES

Bloc : Bases de données Code : BD21

Durée : 1h30 Date : 10/03/99

Documents autorisés : Coordonnateur :

ceux distribués en cours 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 : 6 points (Q1.1 = 5 pts, Q1.2 = 1 pt)

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 : 4 points (Q4.1 = 2 pts, Q4.2 = 2 pts)

On veut gérer un système de messagerie électronique dans une base de données dont le schéma est le suivant :

MESSAGE(nomsg, sujet, date, corps, adremetteur)

DESTINATAIRE(nomsg, adrdestinataire)

REPONSE(nomsgrep, nomsginit)

LOCUTEUR(adremail, nom, type)

COMPOSITION(adremailgroupe, adremailpers)

Les clés sont soulignées et les clés étrangères sont en gras. Schéma Entité-Association

1.2. Contraintes d’intégrité

un message de réponse ne peut être envoyé avant le message initial

un locuteur de type groupe ne peut être l’émetteur d’un message

l’émetteur d’un message de réponse doit être un des destinatairesdu message initial

un groupe n’est composé que de locuteurs de type personne

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 locuteurs qui ont envoyé un message au locuteur d’adresse email ‘liste-ig2.int-evry.fr’.

SELECT DISTINCT L.nom

FROM LOCUTEUR L, MESSAGE M, DESTINATAIRE D

WHERE M.nomsg=D.nomsg AND L.adremail=M.adremetteur

AND D.adrdestinataire=‘liste-ig2.int-evry.fr’

Remarque : le DISTINCT évite les doublons puique L.nom n’est pas une clé. La réponse obtenue ne donne pas forcément que des locuteurs de type personne. Par contre s’il s’agit d’un locuteur de type groupe, on donne juste son nom et pas le nom des locuteurs de type personne qui le compose.

(Q2.2) Donner l’adresse email et le nom des locuteurs qui composent le groupe d’adresse email ‘liste-adm.int-evry.fr’.

SELECT L.adremail, L.nom

FROM COMPOSITION C, LOCUTEUR L

WHERE C.adrmailpers=L.adremail AND

C.adremailgroupe=‘liste-adm.int-evry.fr’

Question 3 Exprimer les 3 questions suivantes en SQL : (Q3.1) Donner le nombre de messages reçus par le locuteur d’adresse email ‘defude@int-evry.fr’.

SELECT COUNT(*)

FROM DESTINATAIRE

WHERE adrdestinataire=‘defude@int-evry.fr’

(Q3.2) Donner pour chaque groupe (adresse email et nom) le nombre de personnes qui le compose.

SELECT L.adremail, L.nom, COUNT(*)

FROM COMPOSITION C, LOCUTEUR L

WHERE C.adremailgroupe=L.adremail

GROUP BY L.adremail, L.nom

Attention : les attributs du SELECT doivent tous apparaitre dans le GROUP BY. (Q3.3) Donner l’adresse email de la personne qui appartient au plus grand nombre de groupes.

SELECT adremailpers

FROM COMPOSITION

GROUP BY adremailpers

HAVING COUNT(*) =

(SELECT MAX(COUNT(*)) FROM COMPOSITION GROUP BY adremailpers)

Remarque : autre écriture possible pour le HAVING :

HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM COMPOSITION GROUP BY adremailpers)

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

(Q4.1) Donner les messages (nomsg et sujet) qui n’ont fait l’objet d’aucune réponse.

SELECT nomsg, sujet

FROM MESSAGE

MINUS

SELECT M.nomsg, M.sujet

FROM MESSAGE M, REPONSE R

WHERE M.nomsg=R.nomsginit

Attention : on utilise un opérateur ensembliste donc il faut que les schémas produits par les deux requêtes SQL soient compatibles (notamment qu’ils aient même nombre d’attributs!). (Q4.2) Donner les locuteurs (adremail et nom) qui appartiennent à tous les groupes.