Institut National des Télécommunications
Contrôle des connaissances
|
Bases de données |
Code : BD21 |
|
Date : 09/02/2005 |
Durée : 1h30 |
|
Coordonnateur : C. Lecocq et S. Tata |
Documents autorisés: ceux distribués en |
____________________________________________________
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 : 5 points (Q1 = 5)
Question 2 : 5 points (Q2.1 = 2, Q2.2 = 1,5, Q2.3 =
1,5)
Question 3 : 6 points (Q3.1 = 2, Q3.2 = 2, Q3.3 = 2)
Question 4 : 4 points (Q4.1 = 2, Q4.2 = 2)
On souhaite mettre en place une base de données pour
gérer l'ensemble des journaux édités en France. Cette base est définie
par le schéma suivant :
Journaliste (nom,
prenom, adresse, telephone)
Journal(nomJournal,
type, editeur, nomDirecteur, prenomDirecteur)
Numéro (numéro, nomJournal,
dateParution, nbpages)
Article (titre,numero,
nomJournal, type)
Ecriture (titre,
numero, nomJournal, NomAuteur, PrenomAuteur)
Les clés sont soulignées et les clés étrangères sont
en gras.
La sémantique des diverses relations est la suivante
:
Journaliste (nom,
prenom, adresse, telephone) : un journaliste est identifié d'une
manière unique par son nom et son prénom. Il est caractérisé par une
adresse et un téléphone.
Journal (nomJournal,
type, editeur NomDirecteur, PrenomDirecteur) : un journal est
identifié d'une manière unique par un nom (nomJournal). Il est
caractérisé par un type (quotidien, hebdomadaire, …), une maison
d'édition (editeur) et un directeur (nomDirecteur, prenomDirecteur).
(nomDirecteur, prenomDirecteur) est une clé étrangère qui référence
(nomJournaliste, prenomJournaliste) de la relation Journaliste.
Numéro (numero, nomJournal,
dateParution, nbpages) : un numéro d'un journal est identifié d'une
manière unique par un numéro et le nom du journal (numero, nomJournal)
et est caractérisé par une date de parution et un nombre de pages.
nomJournal est une clé étrangère qui référence nomJournal de la
relation Journal.
Article(titre,numero,
nomJournal, type) : un article est identifié d'une manière
unique par un titre et le numéro et le nom du journal auxquels
l'article appartient. Il est caractérisé par un type (politique,
société, sport, …). (numero, nomJournal) est une clé étrangère qui
référence (numero, nomJournal) de la relation Numéro.
Ecriture (titre,
numero, nomJournal, nomAuteur, prenomAuteur) un
tuple de la relation Ecriture désigne le
fait qu'un journaliste (nomAuteur, prenomAuteur) a participé à
l'écriture d'un article (titre, numéro, nomJournal). Un article est
écrit par un ou plusieurs journalistes. (nomAuteur, prenomAuteur) est
une clé étrangère qui référence (nomJournaliste, prenomJournaliste) de
la relation Journaliste. (titre, numero,
NomJournal) est une clé étrangère qui référence (titre, numero,
NomJournal) de la relation Article.
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.
(Q2.1) Quels sont les articles (titre, numéro
et nom du journal) écrits par Serge July en 2003?
Remarque : vous supposerez que les opérateurs
de comparaison classiques sont applicables aux types date. Il est donc
possible d’écrire :dateParution >= ‘01/01/2002’
SELECT titre, numero, nomJournal
FROM Ecriture E, Numero N
WHERE E.numero = N.numero AND
E.nomJournal =N.nomJournal AND
nomAuteur = 'July' AND prenomAuteur = 'Serge'
AND
dateParution >= '01/01/2003' AND
dateParution <= '31/12/2003'
(Q2.2) Quels sont les articles (titre, numéro
et nom du journal) qui sont écrits par au moins deux journalistes
différents?
SELECT titre, numero, nomJournal
FROM Ecriture E1, Ecriture E2
WHERE E1.titre = E2.titre AND E1.numero=E1.numero
AND E1.nomJournal=E2.nomJournal AND
E1.nomAuteur <>
E2.nomAuteur AND E1.prenomAuteur<>E2.prenomAuteur
(Q2.3) Quels sont les journalistes (nom et
prénom) qui n'écrivent que dans des journaux quotidiens?
SELECT nomAuteur, prenomAuteur
FROM Journal
J, Ecriture E
WHERE J.nomJournal
= E.nomJournal AND type = 'quotidien'
MINUS
SELECT nomAuteur, prenomAuteur
FROM Journal J, Ecriture E
WHERE J.nomJournal = E.nomJournal AND type
<> 'quotidien'
Question 3
Exprimer les 3 questions suivantes en SQL :
(Q3.1) Quels sont les journalistes (nom et
prénom) qui ont écrit plus de 100 articles?
SELECT nomAuteur, prenomAuteur
FROM Ecriture
GROUP BY nomAuteur,
prenomAuteur
HAVING count(*)
> 100
(Q3.2) Pour chaque journal (nom du journal),
donner le nombre moyen d'articles par numéro et le nombre de
journalistes qui ont écrit par numéro.
SELECT nomJounal, numero,
count(nomAuteur) nbAuteurs, count(titre)
FROM Ecriture
GROUP BY nomJounal,
numero
(Q3.3) Donner les noms des journalistes (nom
et prénom) qui n'écrivent que pour un seul journal.
SELECT nomAuteur, prenomAuteur
FROM Ecriture
GROUP BY nomAuteur,
prenomAuteur
HAVING
count(distinct nomJournal) = 1
Question
4
Exprimer les 2 questions suivantes en algèbre
relationnelle ou en SQL :
(Q4.1) Quel est le journaliste qui a écrit
le plus d'articles pour le journal 'L'équipe'.
SELECT nomAuteur, prenomAuteur
FROM Ecriture
WHERE nomJournal =
'L'équipe'
GROUP BY nomAuteur,
prenomAuteur
HAVING count(*)
>= ALL SELECT count(*) FROM Ecriture WHERE
nomJournal = 'L'équipe' GROUP BY nomAuteur, prenomAuteur
(Q4.2) Quels sont les journalistes (nom et
prénom) qui ont écrit dans tous les numéros du journal 'Libération'.
SELECT nomAuteur, prenomAuteur
FROM Ecriture
WHERE nomJournal =
'Libération'
GROUP BY
nomAuteur, prenomAuteur
HAVING
count(distinct numero) = SELECT count(distinct numero) FROM Ecriture
WHERE nomJournal = 'Libération'
Posté le 4 septembre 2009