Tutoriel de Bases de Données Relationnelles

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

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 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 : 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'