Bases de Données (BD3) – Examen Session 2 (durée : 3 heures)

Université Paris Diderot – L2 Informatique
1er juillet 2015
Bases de Données (BD3) – Examen Session 2 (durée : 3 heures)
Documents autorisés : trois feuilles A4 recto-verso et
personnelles. Les ordinateurs et les téléphones mobiles sont interdits.
Le barême est donné à titre indicatif et pourra être réévalué afin de favoriser la majorité des étudiants.
Exercice 1 [Requêtes SQL et algébre relationnelle (2-3 points)]
On considère les trois tables suivantes :
Vendeur
Livre
id_vend
nom_vend
ville Chronopost point_relais retrait_magasin
V1
Temps-Livres
Lyon
9
2
oui
V2
Un regard moderne Paris
8
4
oui
V3
Gibert Joseph
Paris
6
4
oui
V4
Joseph Gilbert
Paris
20
10
non
ISBN
titre
auteur
editeur
format nb_pages
2070360539
Le cycle de Fondation, I : Fondation
Asimov
Folio
broché
416
2070396401 Le Cycle de Fondation, I à V - coffret Asimov
Folio
broché
2608
2277217395
Le Congrès de futurologie
Lem
J’ai Lu
poche
158
9782702100
Le Congrès de futurologie
Lem
Calmann-Levy broché
222
Exemplaire
id_ex
E1
E1
E2
E1
E1
E1
E1
E1
ISBN
2070396401
2070396401
2070396401
2277217395
2277217395
2070360539
2070360539
9782702100
id_vend
V2
V3
V3
V1
V2
V1
V3
V4
état
1
2
3
2
1
1
2
3
prix
28
32
15
66
56
8
12
72
Donnez les résultats des requêtes suivantes :
1. πtitre (σauteur=‘Asimov0 (Livre) ./ σid_vend=‘V 40 (Exemplaire))
2. πediteur (σauteur=‘Lem0 (Livre)) − πediteur (σf ormat=‘poche0 (Livre))
3. SELECT id_vend, AVG(prix)
FROM Exemplaire
WHERE état >= 2
GROUP BY id_vend
HAVING sum(prix) = 72 ;
4. SELECT V.nom_vend
FROM Vendeur V
WHERE NOT EXISTS
(SELECT L.auteur FROM Livre L, Exemplaire E
WHERE L.ISBN=E.ISBN
AND auteur=‘ASIMOV’
AND E.id_vend=V.id_vend);
1
Exercice 2 [Requêtes SQL et algébre relationnelle (10-12 points)]
On considère une base de données de livres d’occasion proposés à la vente par différents
vendeurs. Les clefs primaires sont précédées d’une étoile (*) et les clefs étrangères sont suivies
d’un astérisque (#).
• Vendeur(*id_vend, nom_vend, ville, Chronopost, point_relais, retrait_magasin) : liste des différents vendeurs, avec leur localisation géographique,
ainsi que leur politique de facturation en matière de frais de port (identifiant ; nom ;
ville ; frais d’envoi par Chronopost en euros ; frais d’envoi en point relais en euros ;
possibilité de retrait gratuit en magasin : oui ou non).
• Livre(*ISBN, titre, auteur, editeur, format, nb_pages) : liste des livres (identifiant ISBN ; titre ; auteur ; éditeur ; format, au choix : poche, broché, relié ; nombre
de pages).
• Exemplaire(*id_ex, *ISBN#, *id_vend#, état, prix) : liste de chacun des
exemplaires de livres proposés à la vente par les différents vendeurs (la clef primaire est
composite et est formée à partir d’un identifiant d’exemplaire, ainsi que d’un identifiant
de livre et d’un identifiant de vendeur ; l’attribut état consiste en une note de 1, 2 ou
3 qui représente respectivement un état “comme neuf”, “bon” ou “correct” ; le prix
correspond à une somme ronde en euros). Le premier attribut correspond au fait qu’un
même vendeur peut proposer à la vente plusieurs exemplaires d’un même ouvrage.
Lorsqu’il vous est demandé dans la question 2 de donner une requête en algèbre relationnelle, pour plus de lisibilité, vous pouvez écrire la requête en plusieurs étapes, par exemple :
A = πval (R) ./ S
B = σatt=5 (T ∪ R)
reponse = A ∩ B
Par ailleurs, si vous avez besoin de distinguer deux utilisations d’une même table, vous pouvez
utiliser un numéro pour distinguer les deux utilisations de la table. Voici un exemple, dans
lequel la table T est utilisée deux fois :
C = σatt=5 (T1 ./T1 .val<>T2 .val T2 )
A défaut de répondre à la question 2 avec des requêtes en algèbre relationnelle,
vous pouvez proposer des requêtes SQL (et vice versa, lorsque cela est possible,
i.e., pour les requêtes de la question 1 qui ne font pas appel à l’aggrégation).
Sachez néanmoins que dans ce cas, seule la moitié des points vous sera comptée.
1. Ecrivez les requêtes SQL correspondant aux questions suivantes. Un bonus sera accordé
si vous prenez bien soin d’éviter les doublons.
(a) Les titres des livres dont le nombre de pages est supérieur à 1000 et qui sont proposés
pour moins de 15 euros par un vendeur acceptant le retrait gratuit en magasin.
Tableau résultat : titre, nom_vend, prix.
(b) Le nombre de livres différents (on compte un livre par ISBN) écrits par Asimov et
dont le titre contient le mot “Fondation”.
Tableau résultat : nombre.
2
(c) Le(s) livre(s) le(s) moins cher(s) parmi ceux proposés par les vendeurs acceptant le
retrait gratuit en magasin.
Tableau résultat : titre, id_vend, prix.
(d) Pour chaque vendeur, le nombre d’exemplaires en stock correspondant aux livres
dont l’ISBN est “9782702100” et dont l’état a une note d’au plus 2 (i.e., l’état de
l’exemplaire est soit “comme neuf”, soit “bon”).
Tableau résultat : nom_vend, nb_ex.
(e) Les paires de vendeurs qui proposent au moins un livre en commun (par “livre”,
nous entendons ici deux ouvrages dont l’ISBN est identique).
Tableau résultat : V1.nom_vend, V2.nom_vend.
Suggestion : vous pouvez utiliser des alias de table.
(f) Les vendeurs qui proposent tous les livres de Lem présents dans la base de données.
Tableau résultat : nom_vend.
Suggestion : vous pouvez utiliser une sous requête corrélée ou bien la condition
“having”.
(g) Les éditeurs n’ayant édité aucun livre de plus de 500 pages présent dans la base, ni
aucun livre proposé dans la base pour plus de 30 euros.
Tableau résultat : editeur.
2. Ecrivez en algèbre relationnelle les requêtes correspondant aux questions suivantes.
(a) Les auteurs dont au moins un des ouvrages est vendu pour plus de 100 euros, ainsi
que le nom des auteurs qui ont été édités par “La Pléiade”.
Tableau résultat : auteur.
(b) Le nom des vendeurs proposant des livres dont l’envoi par Chronopost revient à
moins de 10 euros (prix + frais d’envoi par Chronopost).
Tableau résultat : nom_vend, titre.
(c) Les auteurs dont aucun livre n’est proposé en format relié.
Tableau résultat : auteur.
(d) Les livres dont seulement un unique exemplaire se trouvant dans l’état 1 est proposé
dans la base.
Tableau résultat : nom_vend, ISBN.
Suggestion : il pourra ici vous être utile soit de distinguer deux utilisations d’une
même table avant d’en considérer le produit cartésien, soit d’utiliser le renommage.
Exercice 3 [Modélisation, normalisation et langage de définition de données (5-7 points)]
Dans cet exercice on considère la base de l’exercice 2.
1. On suppose que le type de l’attribut “état” est INTEGER. Les contraintes de clef primaire
et étrangère listées dans l’exercice 2 ne suffisent pas à garantir que les valeurs de cet
attribut sont bien comprises entre 1 et 3. Expliquez comment vous pouvez imposer ce
type de contrainte lors de la création de vos tables en SQL.
2. Proposez un autre schéma que celui de l’exercice 2, construit cependant à partir du
même ensemble d’attributs et pouvant donner lieu à des anomalies de suppression,
insertion, ou bien mise à jour (veillez à pointer ces anomalies et en particulier à pointer
de possibles problèmes de redondances).
3
3. On souhaite incorporer à notre base de données un certain nombre d’informations
concernant la langue d’origine des ouvrages, ainsi que, éventuellement, la langue dans
laquelle ils ont été traduits, avec le nom du traducteur et sa note (représentée par un
entier de 1 à 3 correspondant à un nombre d’étoiles : plus un traducteur a d’étoiles,
meilleur il est). Chaque langue est associée à un alphabet. On souhaite également classer
les ouvrages par thème et année de parution. Vous pouvez évidemment aussi reprendre
le schéma de la question 2 afin de l’améliorer à votre convenance (e.g., introduire un
identifiant, un attribut nom et un attribut prénom distincts pour chaque auteur, etc.).
(a) Proposez une première modélisation dans laquelle à chaque traducteur correspond
un unique couple langue source langue cible. (Si un traducteur traduit de l’anglais
vers le français on considère par exemple qu’il ne traduit ni du français vers l’anglais,
ni de l’italien vers l’anglais, ni du russe vers le chinois.) Chaque traducteur reçoit
une note unique évaluant ses compétences pour cet unique couple langue source
langue cible. Expliquez bien vos choix de modélisation.
(b) Proposez une seconde modélisation plus libérale permettant à un traducteur de
traduire plus ou moins bien dans plusieurs langues différentes (et, éventuellement,
depuis plusieurs langues différentes). Précisez bien vos choix (vous pouvez décider
qu’à un même traducteur correspond une seule langue source, ou bien qu’il peut
traduire depuis plusieurs langues sources). Chaque traducteur reçoit donc une note
différente évaluant ses compétences pour chaque couple langue source langue cible
lui correspondant. Expliquez bien vos choix de modélisation.
4. Proposer deux schémas correspondant respectivement à chacune de vos deux modélisations. Vous veillerez à éviter autant que possible les valeurs nulles. Vos schémas devront
également être dans l’une des deux formes normales vues en cours (FNBC ou 3FN).
Précisez les dépendances fonctionnelles que vous souhaitez satisfaire (par exemple, dans
le cas de l’exercice 2, un ISBN détermine un titre, un auteur, un éditeur et un nombre
de pages) et expliquez pourquoi vos schémas sont bien dans l’une et/ou l’autre forme. Il
n’est pas nécessaire de fournir le code SQL nécessaire à la création de vos tables (vous
pouvez les présenter en utilisant les mêmes conventions que celles dont il est fait usage
dans l’exercice 2).
Exercice 4 [Normalisation (bonus - 2 points)]
On rappelle qu’on utilise pour désigner un ensemble de la forme {A, B} l’abbréviation AB.
On considère l’ensemble d’attributs R = {N, S, C, T } (où N correspondant à un nom de
traducteur, S à une langue source, C à une langue cible et T à un tarif) et l’ensemble de
dépendances fonctionnelles F = {N → SC, C → N T, S → T }.
1. Calculer la clôture de chacun des trois ensembles N , C et ST .
2. Lister toutes les clefs candidates.
4