Université de Lorraine / Licence Informatique 2ème année (S3)
TP 1 - BDD1
Gestion d’un restaurant administratif
On considère la base de données relationnelle suivante destinée à gérer un restaurant administratif.
Ce restaurant est fréquenté par des usagers qui doivent être titulaires d’une carte d’accès ; cette carte
à puce permet d’enregistrer les versements d’argent et les dépenses. Ces informations sont
rassemblées pour l’ensemble des usagers dans une base de données centrale :
USAGER (numCarte, nom, numCateg, mtCaution, dateCarte)
DEPOT (numCarte, dateDepot, montant)
PRESTATION (numPrest, typePrest)
CATEGORIE (numCateg, libCateg)
TARIF (numPrest, numCateg, prix)
TICKET (numTicket, numCarte, dateAchat)
ACHAT (numTicket, numPrest, nbPrest)
Chaque usager est enregistré dans la relation USAGER, il est identifié par le numéro de sa carte et
appartient à une catégorie tarifaire.
Les catégories tarifaires auxquelles appartiennent les usagers sont enregistrées dans la relation
CATEGORIE.
Tous les versements d’argent effectués par les usagers sont enregistrés dans la relation DEPOT.
Toutes les prestations (repas, boisons, suppléments, …) que peuvent acheter les usagers du
restaurant administratif sont enregistrées dans la relation PRESTATION.
Les prestations sont proposées aux usagers à des tarifs différents suivant leur catégorie; ces tarifs
sont enregistrés dans la relation TARIF.
Lorsqu’il achète des prestations, l’usager obtient un ticket où sont décrits ses achats. Chaque ticket
est enregistré dans la relation TICKET, et le détail des achats est enregistré dans la relation ACHAT.
Une description en extension possible de cette base de données est déjà enregistrée sur PostgreSQL.
Les tables ci-dessous reproduisent le contenu de la BD.
Représentation graphique du modèle relationnel de la BD
Requêtes SQL
Ecrire et tester la requête SQL qui répond à chaque question. Une requête qui renvoie un résultat
n’est pas forcément correcte, vérifier avec l’extension de la BD fournie à la fin du sujet.
1. Contenu de la table PRESTATION.
2. Contenu de la table USAGER.
3. Liste des numéros et noms des usagers.
4. Moyenne du montant des dépôts. (depotMoyen)
5. Liste des usagers ayant payé 5 euros de caution. (numCarte, nom)
6. Liste des usagers de la catégorie ‘petits revenus’. (numCarte, nom)
7. Liste des usagers ayant déjà effectué au moins un dépôt supérieur ou égale à 20 euros. Trier
par le nom. (numCarte, nom)
8. Nombre de dépôts effectués par ‘Pierre’.
9. Liste des personnes ayant déjà acheté le ‘supplément chantilly’. (numCarte, nom)
10. Nombre de personnes venues au restaurant le 2 octobre 2015 et nombre de tickets produits.
Essayer d’écrire la requête en utilisant ce format de la date 02*10*2015. (nbPersonne,
nbTicket)
11. Quelle somme a été déposée sur sa carte par ‘Nathalie’ ? (montantNath)
12. Nombre de personnes ayant déjà acheté de la ‘bière’
13. Prix du ‘supplement chantilly’ pour les personnes de ‘petits revenus’
14. La dernière date où le restaurant était ouvert. (lastDate)
15. Liste des personnes venues au restaurant le dernier jour. (numCarte, nom)
16. La date et la liste des prestations achetées par ‘Pierre ’ la dernière fois qu’il est venu au
restaurant. (typePrest, dateAchat)
17. Somme dépensée par ‘Michel’, la première fois qu’il est venu au restaurant
18. La recette du 2 octobre 2015. (recette2Oct2015)
19. La recette du ‘supplement chantilly’.
20. Somme totale dépensée par ‘Suzanne’.
USAGER
numCarte Nom numCateg mtCaution dateCart
C1 Pierre T2 5 19/04/2014
C2 Suzanne T1 5 20/05/2014
C3 Michel T2 6 22/06/2015
C4 Nathalie T1 6 30/09/2015
C5 Gerard T2 6 31/01/2016
C6 Bernard T2 7 01/04/2017
PRESTATION CATEGORIE
numPrest typePrest numCateg libCateg
1 repas normal T1 petits revenus
2 quart de vin rouge T2 gros revenus
3 biere
4 supplement frites
5 supplement chantilly
TARIF DEPOT
numPrest numCateg Prix numCarte dateDepot montant
1 T1 5 C1 22/04/2014 10
2 T1 2 C2 24/05/2014 15
3 T1 2 C1 24/05/2014 10
4 T1 1 C3 22/06/2015 20
5 T1 1 C4 01/10/2015 20
1 T2 6 C1 01/10/2015 15
2 T2 3 C2 01/10/2015 10
3 T2 3 C5 02/02/2016 20
4 T2 2 C5 02/03/2016 10
5 T2 2 C1 03/03/2016 30
C5 02/05/2016 15
C6 01/04/2017 20
TICKET ACHAT
numTicket numCarte dateAchat numTicket numPrest nbPrest
TI1 C1 22/04/2014 TI1 1 1
TI2 C1 24/04/2014 TI2 1 1
TI3 C2 22/05/2014 TI2 4 1
TI4 C1 26/05/2014 TI3 1 1
TI5 C1 26/05/2014 TI3 5 2
TI6 C3 22/06/2015 TI4 1 1
TI7 C4 02/10/2015 TI4 4 1
TI8 C2 02/10/2015 TI5 2 1
TI9 C2 02/10/2015 TI6 1 1
TI10 C5 02/02/2016 TI7 1 1
TI11 C1 02/03/2015 TI8 1 1
TI12 C5 04/03/2016 TI9 5 3
TI13 C6 01/04/2017 TI10 1 1
TI14 C6 01/04/2017 TI10 4 3
TI11 1 1
TI11 4 1
TI12 1 1
TI12 4 3
TI12 5 2
TI13 1 1
TI13 2 2
TI14 2 1