PARTIE A : Elaboration de requêtes SQL

- p 1/4 -
PREMIER CYCLE - PREMIERE ANNEE
Interrogation Ecrite « BASES DE DONNEES »
Durée: 1 h 30 mn - Tous documents autorisés.
le 30 novembre 2001
Les 3 parties sont indépendantes et peuvent être traitées dans l'ordre voulu.
PARTIE A : Formulation de requêtes à l’aide de l’algèbre relationnelle et SQL (12 points)
Soit la base de données représentée par le modèle logique suivant :
JOUEUR
#Numéro
Nom
Prénom
Age
Nationalité
RENCONTRE
#Numéro gagnant
#Numéro perdant
#Année
Lieu du tournoi
Score
SPONSOR
#Numéro sponsor
Nom
Adresse
Chiffre d’affaires
GAIN
#Numéro
lieu du tournoi
Année
Prime
#Numéro sponsor
Formuler les requêtes suivantes en algèbre relationnelle (quand tous les opérateurs sont disponibles)
puis en SQL. Pour l’écriture en SQL, vous utiliserez au choix syntaxe et opérateurs vus en cours, ou
ceux propres à SQL-Access.
1. Nom et prime des joueurs sponsorisés par Peugeot entre 1985 et 1990.
2. Nom et âge des joueurs ayant participé au tournoi Roland Garros de 1989.
3. Nom et nationalité des joueurs ayant participé à la fois aux tournois de Roland Garros et
Wimbledon en 1985 .
4. Nom des joueurs ayant toutes leurs primes des tournois de Roland Garros supérieures à 1MF.
5. Nom des joueurs ayant toujours perdu à Wimbledon
6. Moyennes des primes gagnées par année et triées par année.
PARTIE B : Interprétation de requêtes (5 points)
En vous basant sur les tables suivantes, donner le résultat des opérations relationnelles suivantes
ainsi qu’une interprétation (à quelles requêtes formulées en langage naturel répondent ces
opérations).
A1 =  (n°fabr, adr-fabr) (Fabricant)
A2 = n°pro(FAB-PRO)
A3 =  (n°serie, n°fabr, n°com) (Article)
A4 =  (n°com, n°cli) (Commande)
B1 = (adr-fabr=‘LYON’)(Fabricant)

- p 2/4 -
B2 =  (n°cli = 110)(A4)
C1 = B1 FAB-PRO
C2 = B2 A3
D1 = (n°fabr, adr-fabr) (B1)
D2 = A1 - D1
D3 = (n°fabr, nom-fabr) (adr-fabr = ‘Paris’ OR adr-fabr =‘Marseille’) (Fabricant)
D4 = D1  D2
D5 =  (n°fabr, nom-fabr) (adr-fabr = ‘Paris’ ) (Fabricant)
D6 = D3 - D5
Base de données
ARTICLE
n°série
n°pro
n°fabr
n°com
prix-vente
1000
1
1
1
5510,5
1001
2
3
1
23000,6
1008
3
20
1
15000,5
1015
1
3
5
5530
1020
3
3
5
11100,5
1030
2
11
6
21100,6
10300
1
5
3
5550,8
10310
2
3
7
23000
10520
1
10
6
5600,9
80610
2
10
3
27000,2
80700
2
3
5
23100,4
81720
2
10
2
22900,25
81800
2
3
4
23200,75
90000
2
11
8
25000
CLIENT
n°cli
nom-cli
tel-cli
adr-cli
10
Martin
0548900101
BOURGES
50
Durand
0141901205
PARIS
100
Dupont
0478952104
LYON
110
Adoc
0554663103
NEVERS
120
Gerto
0478634104
LYON
150
Legrand
0146625201
PARIS
PRODUIT
n°pro
nom_pro
1
écran super-VGA 17p
2
DVD 17 GO
3
Imprimante laser_A4 100p/mn
COMMANDE
n°com
n°cli
dat-com
1
100
1997-01-01
2
10
1997-01-21
3
110
1997-02-02
4
50
1997-02-23
5
100
1997-03-03
6
110
1997-04-04

- p 3/4 -
7
120
1997-07-05
8
150
1997-07-25
FABRICANT
n°fabr
nom_fabr
tel_fabr
adr_fabr
1
NF1
0478910001
LYON
3
NF3
0141820002
PARIS
5
NF5
0535830003
TOULOUSE
10
NF10
0141840004
PARIS
11
NF11
0423850005
MARSEILLE
15
NF15
0478580006
LYON
20
NF20
0145870007
PARIS
25
NF25
0228800008
LILLE
FAB_PRO
n°pro
n°fabr
prix-fabr
1
1
5010,5
1
3
5030
1
5
5050
1
10
5100,5
1
11
5110
1
15
5150
2
3
20300
2
11
21100
2
15
21500,5
2
20
22000
3
1
10100
3
3
10300,5
3
15
11500
3
20
12000

- p 4/4 -
PARTIE C : Modèle conceptuel de données (3 points)
Le modèle conceptuel de données (MCD) ci-dessous décrit une gestion de prêts avec conservation
de l'historique des prêts. On vous demande :
1. de donner le schéma relationnel ou le modèle logique correspondant à ce MCD.
2. de transformer ce modèle (MCD) pour pouvoir gérer et prendre en compte les demandes de
réservations de documents effectuées par les utilisateurs.
NB : ci-joint la description de chacune des entités :
Nom de l'entité
Liste des propriétés
AUTEUR
DOCUMENT
MOT-CLE
UTILISATEUR
#Ident_auteur, nom, prénom
#Id_doc, titre, date parution, nb exemplaires
#id_mot_cle, mot_cle
#id_user, nom, prenom
0-n
0-n
0-n
1-1
1-n
0-n
UTILISATEUR
DOCUMENT
AUTEUR
MOT-CLE
Décrit
Ecrit
Emprunte
Date début
Date fin