PROSIT 1-SQL
INTRODUCTION
La dernière phase de la conception de votre base de données consiste à implémenter
physiquement votre modèle relationnel normalisé déjà élaboré.
Pour ce faire, votre mission serait de :
- Travailler en groupe
- Installer votre SGBD, à savoir Oracle Express 10g
- Commencer par la création de vos objets (tables) via le langage SQL (LDD)
- Manipuler les enregistrements de vos tables (LMD)
- Introduire le langage LID par des notions algébriques
- Maitriser le langage d’interrogation de données de SQL (LID) pour alimenter vos
tableaux de bord.
Soit le Modèle Relationnel suivant, élaboré pour une application Web de gestion des Deals :
DEALS (intitule, description_D, prix_I, prix_D, Reduction, date_D, period_V, NoteD, expire, nom_prest#,
nom_catg#)
CATEGORIES (nom_categorie, descriptionC)
MEMBRES (Login, mdp, nom, prenom)
PRESTATAIRES_SERVICES (nom_prest, num_adresse, rue_adresse, ville, CP, tel, Email,
page_fb) ACHATS (intitule#, Login#, nbCoupon )
Partie 1 : Langage de Définition de Données
1. Créer les tables du modèle relationnel ci-dessus en prenant en considération la
description suivante de chacune des relations :
1/6
PROSIT 1-SQL
TABLES COLONNES DESCRIPTIONS TYPES CONDITIONS
INTITULE Intitule du deal Chaine de 50 Clé primaire
DEALS
caractères
DESCRIPTION_D Description du deal Chaine de 50 Obligatoire
caractères
PRIX_I Prix initial du deal Nombre -
avant remise
PRIX_D Prix du deal après Remise Nombre -
REDUCTION Le pourcentage de Nombre Obligatoire
réduction
DATE_D La date du début du deal Date Obligatoire
PERIOD_V Période de validité Nombre Obligatoire
d’un deal
(En nombre de jours)
NOTED Note moyenne Nombre Valeur entière
attribuée au Deal entre 0 et 5
(Note sur 5)
EXPIRE Détermine si le deal a Chaine de 3 Egale à « OUI »
expiré ou non caractères ou « NON »
Valeur par
défaut « NON
»
NOM_CATG Nom de la catégorie Chaine de 20 Clé étrangère
caractères
NOM_PRESTATAIRE Le prestataire service Chaine de 50 Clé étrangère
caractères
NOM_CATEGORIE Le nom de la catégorie Chaine de 20 Clé primaire
CATEGORIES
du service caractères
DESCRIPTIONC Description de la Chaine de 80 -
catégorie caractères
LOGIN Identifiant du membre Chaine de 15 - Clé primaire
MEMBRES
caractères - Différent du
nom et du
prénom
MDP Mot de passe Chaine de 50 - Contient au
caractères moins 1
chiffre, 1
caractère
minuscule et
majuscule
2/6
PROSIT 1-SQL
NOM Nom du membre Chaine de 50 Obligatoire
caractères
PRENOM Prénom du membre Chaine de 50 Obligatoire
caractères
NOM_PREST Nom du prestataire Chaine de 50 Clé primaire
PRESTATAIRES
service caractères
_SERVICES NUM_ADRESSE Numéro de l’adresse Nombre -
RUE_ADRESSE Rue de l’adresse Chaine de 20 -
caractères
VILLE Ville de l’adresse Chaine de 20 Obligatoire
caractères
CP Code postal Nombre -
TEL Téléphone Nombre Obligatoire
EMAIL Adresse Email Chaine de 30 Adresse Email
caractères valide
PAGE_FB Page Facebook Chaine de 50 - Unique
caractères - Commence par
« http:// »
INTITULE Intitulé du Deal Chaine de 50 Clé primaire +
ACHATS
caractères clé étrangère
LOGIN Login du membre Chaine de 15 Clé primaire +
caractères clé étrangère
NBCOUPON Nombre de coupons Nombre Obligatoire
achetés
2. Ecrire les requêtes SQL permettant d’apporter les modifications suivantes :
a. Le prix initial du Deal est obligatoire
b. Le mot de passe (MDP) ne contient ni le nom ni le prénom du membre
c. Etant donné que la colonne « PRIX_D » est calculée comme suit :
« PRIX_I - PRIX_I * Reduction », supprimez cette colonne.
d. La date d’achat du Deal est importante à enregistrer, ajoutez cette colonne. e.
Un achat est désormais identifié par l’intitulé du deal, le login du membre ainsi
que la date d’achat, faites le nécessaire.
f. Sachant que le nombre de coupons qu’un membre peut acheter varie entre 1 à
5 coupons, faites le nécessaire.
3/6
PROSIT 1-SQL Partie 2 : Langage de Manipulation des Données
Ecrire les requêtes SQL permettant de :
1. Ajouter les enregistrements suivants dans les tables correspondantes :
Table « MEMBRES »
LOGIN MDP NOM PRENOM
Hello2017 H17test gharbi salma
Ahmed1617 A132bc ben chaabene ali
Daddou123 B098tt Ben mahmoud taoufik
Table « PRESTATAIRES_SERVICES »
NOM_PREST NUM RUE_ADRESSE VILLE CP TEL EMAIL PAGE_FB
_ADRESSE
Square 2 Résidence Ariana 2036 71100001 [Link] [Link]
Optical Mesk Jinen @[Link] Square-optical
L'Aouina Ain 396558407144821/?fref=ts
zaghouan
Le Parador la 9 Immeuble Tunis 2060 71893425 [Link] [Link]
Goulette Labrise Tour tte es/ Parador-La-Goulette
@[Link] Restaurant/725375497514325?fref=ts
Forever 2 rue Taher el Tunis 2091 71234098 [Link] [Link]
Beauty Memmi 1 er y@ foreverbeautycenter/?fref=ts
étage [Link]
Table « CATEGORIES »
NOM_CATEGORIE DESCRIPTIONC
Restaurant et café Deals relatifs aux restaurants et cafés
et salons de thé
Beauté Deals relatifs aux salons de coiffure et SPA
Hôtel Deals relatifs aux hôtels
Life style et accessoires Deals relatifs aux accessoires
bijoux lunettes montres ...
4/6
PROSIT 1-SQL Table « DEALS »
INTITULE DESCRIPTION_D PRIX_I REDUCTIO DATE_D PERIOD_V NOTED EXPIRE NOM_PREST
N
Square L'offre 250 60 12/10/2016 5 - NON Square Optical
Optical comprend : - Un 9h L'Aouina
L'Aouina : bon d'achat de
Un bon valeur de 250 DT
d'achat - 30% de
de valeur réduction sur
de 250 tout achat des
DT lentilles de
couleur
Le Parador L'offre vous 131 63 10/10/2016 3 - NON Le Parador la
la propose des 9h Goulette
Goulette : mets qui vont
un menu vous ouvrir
de l’appétit et
déjeuner donner à vos
ou de papilles de
dîner à grandes envies!
partir de 49 Choisissez l'offre
DT qui vous convient...
Seulement
Table « ACHATS »
INTITULE LOGIN DATE_ACHAT NBCOUPON
Square Optical Hello2017 13/10/2016 15h10 2
L'Aouina : Un bon
d'achat de valeur de
250 DT
Square Optical Ahmed1617 14/10/2016 10h03 4
L'Aouina : Un bon
d'achat de valeur de
250 DT
Square Optical Daddou123 12/10/2016 11h00 3
L'Aouina : Un bon
d'achat de valeur de
250 DT
Le Parador la Goulette : Hello2017 12/10/2016 14h05 5
un menu de déjeuner
ou de dîner à partir de
49 DT
Seulement
2. Mettre à jour les enregistrements suivants :
Table « PRESTATAIRES_SERVICES »
NOM_PREST NUM RUE_ADRESSE VILLE CP TEL EMAIL PAGE_FB
_ADRESSE
Square 2 Résidence Ariana La 2036 71100123 [Link] [Link]
Optical Mesk Jinen soukra @[Link] Square-optical
L'Aouina Ain 396558407144821/?fref=ts
zaghouan
Le Parador la 9 Immeuble Tunis La 2060 71899425 [Link] [Link]
Goulette Labrise Tour goulette tte es/ Parador-La-Goulette
@[Link] Restaurant/725375497514325?fref=ts
m
5/6
PROSIT 1-SQL
Table « MEMBRES »
LOGIN MDP NOM PRENOM
Hello2017 H198test gharbi salma
Ahmed1617 A987Tc ben chaabene ali
3. Sachant que le deal du prestataire de service « SQUARE OPTICAL L'AOUINA » est
expiré, faites le nécessaire.
Partie 3 : Algèbre Relationnelle
Après la création de votre schéma et l’ajout de vos données, vous aurez besoin d’afficher vos
tableaux de bord.
Pour ce fait, vous aurez besoin d’une théorie des ensembles qui définit des opérations qui
peuvent être effectuées sur des relations : c’est l’algèbre relationnelle.
Demandé :
Ecrire les requêtes algébriques permettant de :
1. Afficher la liste des catégories du deal. Afficher le nom de la catégorie. 2. Afficher la
liste des Deals dont la période de validité dépasse les 2 jours. 3. Afficher les détails des
opérations d’achat effectuées le 12/10. Afficher le nom du membre, l’intitulé du Deal et
la date d’achat.
Partie 4 : Langage d’interrogation des Données
1. Traduire les requêtes algébriques 1 et 2 de la PARTIE 3 en requêtes SQL.
2. Ecrire les requêtes SQL permettant de :
a. Afficher la liste des prestataires de service situé dans la ville de Tunis b.
Afficher la liste des achats selon un ordre descendant de la date d’achat. c.
Afficher pour chaque deal les informations suivantes : le jour, le mois et
l’heure du deal, le prix initial du deal et le prix après réduction.
BON TRAVAIL
6/6
