Tutoriel de Bases de Données Relationnelles

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

Institut National des Télécommunications

Contrôle des connaissances

Bases de données	Code : BD21
Date : 10/12/2002	Durée : 1h30
Coordonnateur : C. Carpentier	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 pts)

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

Question 5 : 2 points (Q5.1 = 0,5pts, Q5.2 = 0,5pts, Q5.3 = 0,5pts, Q5.4 = 0,5pts)

 

Le service culturel du conseil général de l'Essonne souhaite disposer d'une base de données afin de gérer les représentations théâtrales. Le schéma proposé pour cette base de données est le suivant :

Organisateur (nomEquipe, adresse, organisme)

Festival (nomFestival, dateDébut, dateFin, nomEquipe)

Œuvre (titre, auteur, année, résumé)

Pièce (troupe, titre, durée, critique)

Représentation (nomFestival, troupe, œuvre, prix)

Les clés sont soulignées et les clés étrangères sont en gras.

La sémantique des diverses relations est la suivante :

Organisateur (nomEquipe, adresse, organisme) : l'organisateur d'un festival est identifié par un nom d'équipe ("Splendid" par exemple). Il est caractérisé par son adresse et l'organisme auquel il est rattaché ("INT" par exemple).

Festival (nomFestival, dateDébut, dateFin, nomEquipe) : un festival est identifié par son nom ("LITIGE" par exemple). Il est caractérisé par ses dates de début et de fin et le nom de l'équipe organisatrice. "nomEquipe" est une clé étrangère sur la relation "Organisateur".

Œuvre (titre, auteur, année, résumé) : une œuvre est identifiée par son titre ("Un air de famille" par exemple). Elle est caractérisée par son auteur principal ("A. Jaoui" par exemple), l'année de sa parution et un résumé.

Pièce (troupe, titre, durée, critique) : une pièce est l'interprétation d'une œuvre par une troupe de théâtre. Elle est donc identifiée par le nom de la troupe l'interprétant et par le nom de l'œuvre interprétée. "titre" est une clé étrangère sur la relation "Œuvre". Elle est caractérisée par sa durée (en minutes) et une critique.

Représentation(nomFestival, troupe, œuvre, prix) : une représentation est la présentation d'une pièce à un festival. Elle est donc identifiée par le nom du festival où elle est présentée, par le nom de la troupe interprétant la pièce et par le titre de l'œuvre interprétée. Une représentation peut être distinguée par un prix ("prix de la meilleure mise en scène" par exemple). "nomFestival" est une clé étrangère sur la relation "Festival", "troupe, œuvre" est une clé étrangère sur la relation "Pièce".

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 festivals (nom de festival et nom d'équipe organisatrice) durant plus de trois jours ?

Hypothèse : la différence entre deux dates donne un nombre de jours

SELECT nomFestival, nomEquipe

FROM Festival

WHERE dateFin – dateDebut >= 3

 

(Q2.2) Quelles sont les pièces (titre, nom de troupe et durée) correspondant à des œuvres écrites par "A. Jaoui" ?

SELECT P.titre, troupe, durée

FROM Pièce P, Œuvre O

WHERE P.titre = O.titre

AND auteur = 'A. Jaoui'

 

Question 3

Exprimer les 3 questions suivantes en SQL :

(Q3.1) Quels est le temps total des représentations (en minutes) proposées au festival "LITIGE" ?

SELECT SUM(duree)

FROM Pièce P, Representation R

WHERE P.titre = R.oeuvre

AND P.troupe = R.troupe

AND nomFestival = 'LITIGE'

(Q3.2) Donner pour chaque festival la durée du festival (en jours), le nombre de représentations et le nombre de prix décernés.

SELECT nomFestival, dateFin-dateDebut, COUNT(*), COUNT(prix)

FROM Representation R

GROUP BY nomFestival, dateDebut, dateFin

(Q3.3) Quelle est l'œuvre (titre et auteur de l'œuvre) la plus représentée ?

SELECT O.titre, O.auteur

FROM Oeuvre O, Representation R

WHERE O.titre = R.oeuvre

GROUP BY O.titre, O.auteur

HAVING COUNT(*) = (

SELECT MAX(COUNT(*))

FROM Representation

GROUP BY oeuvre)

Question 4

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

(Q4.1) Quelles sont les œuvres contemporaines (dont l'année de parution est après 1990) qui n'ont pas été jouées à une des éditions de "LITIGE" ?

SELECT titre

FROM Œuvre

WHERE année >= 1990

MINUS

SELECT oeuvre

FROM Representation

WHERE nomFestival = 'LITIGE'

(Q4.2) Quels sont les auteurs ayant des œuvres représentées à tous les festivals ?

SELECT auteur

FROM Oeuvre O, Representation R

WHERE O.titre = R.oeuvre

GROUP BY auteur

HAVING COUNT(DISTINCT nomFestival) =

(SELECT COUNT(*)

FROM Festival)

Question 5

Répondez au QCM suivant. Entourez les bonnes réponses. Plusieurs propositions peuvent être vraies. Toutes les questions s’inscrivent dans le cadre du TP3-4 (partie application web).

Q5.1) Le navigateur exécute des requêtes SQL :

Vrai

Faux

Q5.2) Le source XML décrivant l'application exécute des requêtes SQL  :

Vrai

Faux

Q5.3) Le programme passerelle de type CGI est situé :

Sur la machine où s'exécute le navigateur

Sur la machine où s'exécute le serveur web

Sur la machine où s'exécute le SGBD

Q5.4) La balise <form> permet de définir un formulaire. Elle contient les informations suivantes :

Le nom du SGBD (Oracle7 par exemple)

Le nom du formulaire

L'adresse réseau (IP) du SGBD

Le nom du CGI à invoquer