PARTIE A : Elaboration de requêtes SQL

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 :
SPONSOR
GAIN
JOUEUR
RENCONTRE
#Numéro sponsor
Nom
Adresse
Chiffre d’affaires
#Numéro
lieu du tournoi
Année
Prime
#Numéro sponsor
#Numéro
Nom
Prénom
Age
Nationalité
#Numéro gagnant
#Numéro perdant
#Année
Lieu du tournoi
Score
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 1/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
1000
1001
1008
1015
1020
1030
10300
10310
10520
80610
80700
81720
81800
90000
n°pro
1
2
3
1
3
2
1
2
1
2
2
2
2
2
n°fabr
1
3
20
3
3
11
5
3
10
10
3
10
3
11
n°com
1
1
1
5
5
6
3
7
6
3
5
2
4
8
prix-vente
5510,5
23000,6
15000,5
5530
11100,5
21100,6
5550,8
23000
5600,9
27000,2
23100,4
22900,25
23200,75
25000
CLIENT
n°cli
10
50
100
110
120
150
nom-cli
Martin
Durand
Dupont
Adoc
Gerto
Legrand
tel-cli
0548900101
0141901205
0478952104
0554663103
0478634104
0146625201
adr-cli
BOURGES
PARIS
LYON
NEVERS
LYON
PARIS
PRODUIT
n°pro
1
2
3
nom_pro
écran super-VGA 17p
DVD 17 GO
Imprimante laser_A4 100p/mn
COMMANDE
n°com
1
2
3
4
5
6
n°cli
100
10
110
50
100
110
- p 2/4 -
dat-com
1997-01-01
1997-01-21
1997-02-02
1997-02-23
1997-03-03
1997-04-04
7
8
120
150
1997-07-05
1997-07-25
FABRICANT
n°fabr
1
3
5
10
11
15
20
25
nom_fabr
NF1
NF3
NF5
NF10
NF11
NF15
NF20
NF25
tel_fabr
0478910001
0141820002
0535830003
0141840004
0423850005
0478580006
0145870007
0228800008
adr_fabr
LYON
PARIS
TOULOUSE
PARIS
MARSEILLE
LYON
PARIS
LILLE
FAB_PRO
n°pro
1
1
1
1
1
1
2
2
2
2
3
3
3
3
n°fabr
1
3
5
10
11
15
3
11
15
20
1
3
15
20
- p 3/4 -
prix-fabr
5010,5
5030
5050
5100,5
5110
5150
20300
21100
21500,5
22000
10100
10300,5
11500
12000
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é
AUTEUR
DOCUMENT
MOT-CLE
UTILISATEUR
Liste des propriétés
#Ident_auteur, nom, prénom
#Id_doc, titre, date parution, nb exemplaires
#id_mot_cle, mot_cle
#id_user, nom, prenom
UTILISATEUR
0-n
Emprunte
Date début
Date fin
0-n
AUTEUR
0-n
Ecrit
1-1
DOCUMENT
- p 4/4 -
1-n
Décrit
0-n
MOT-CLE