PROSIT 4- Algèbre relationnelle & 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 4- Algèbre relationnelle & SQL
TABLES COLONNES DESCRIPTIONS TYPES CONDITIONS
Intitule du deal Chaine de 150 Clé primaire
INTITULE
caractères
Description du deal Chaine de 150 Obligatoire
DESCRIPTION_D
caractères
Prix initial du deal avant Nombre -
PRIX_I
remise
Prix du deal après Nombre -
PRIX_D
Remise
DEALS Le pourcentage de Nombre Obligatoire
REDUCTION
réduction
La date du début du Date Obligatoire
DATE_D
deal
Période de validité d’un Nombre Obligatoire
PERIOD_V deal
(En nombre de jours)
Note moyenne attribuée Nombre Valeur entière
NOTED au Deal entre 0 et 5
(Note sur 5)
Détermine si le deal a Chaine de 3 Egale à « OUI »
expiré ou non caractères ou « NON »
EXPIRE
Valeur par défaut
« NON »
Chaine de 20
NOM_CATG Nom de la catégorie Clé étrangère
caractères
Chaine de 50
NOM_PRESTATAIRE Le prestataire service Clé étrangère
caractères
Le nom de la catégorie Chaine de 20
NOM_CATEGORIE Clé primaire
du service caractères
CATEGORIES Description de la Chaine de 80
DESCRIPTIONC -
catégorie caractères
- Clé primaire
Identifiant du membre Chaine de 15 - Différent du nom
LOGIN
MEMBRES caractères et du prénom
Chaine de 50
MDP Mot de passe Obligatoire
caractères
Chaine de 50 Obligatoire
NOM Nom du membre
caractères
2/6
PROSIT 4- Algèbre relationnelle & SQL
Chaine de 50 Obligatoire
PRENOM Prénom du membre
caractères
Nom du prestataire Chaine de 50 Clé primaire
NOM_PREST
service caractères
NUM_ADRESSE Numéro de l’adresse Nombre -
Chaine de 20 -
RUE_ADRESSE Rue de l’adresse
caractères
PRESTATAIRES VILLE Ville de l’adresse
Chaine de 20 Obligatoire
caractères
_SERVICES CP Code postal Nombre -
TEL Téléphone Nombre Obligatoire
Chaine de 50 Adresse Email
EMAIL Adresse Email
caractères valide
Chaine de 150 - Unique
PAGE_FB Page Facebook caractères - Commence par
« https:// »
Chaine de 150 Clé primaire + clé
INTITULE Intitulé du Deal
caractères étrangère
ACHATS Chaine de 15 Clé primaire + clé
LOGIN Login du membre
caractères étrangère
Nombre de coupons Nombre Obligatoire
NBCOUPON
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 4- Algèbre relationnelle & 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 »
NUM
NOM_PREST RUE_ADRESSE VILLE CP TEL EMAIL PAGE_FB
_ADRESSE
Square Résidence Mesk [Link]
[Link]
Optical Jinen Ain Ariana 2036 71100001 Square-optical-
2 @[Link]
L'Aouina zaghouan 396558407144821/?fref=ts
[Link]
Le Parador la Immeuble [Link]
9 Tunis 2060 71893425 Parador-La-Goulette-
Goulette Labrise Tour @[Link]
Restaurant/725375497514325?fref=ts
rue Taher el
Forever [Link]@ [Link]
2 Memmi 1 er Tunis 2091 71234098
Beauty [Link] foreverbeautycenter/?fref=ts
étage
Table « CATEGORIES »
NOM_CATEGORIE DESCRIPTIONC
Deals relatifs aux restaurants et cafés et
Restaurant et café
salons de thé
Beauté Deals relatifs aux salons de coiffure et SPA
Hôtel Deals relatifs aux hôtels
Deals relatifs aux accessoires bijoux
Life style et accessoires
lunettes montres ...
4/6
PROSIT 4- Algèbre relationnelle & SQL
Table « DEALS »
INTITULE DESCRIPTION_D PRIX_I REDUCTION DATE_D PERIOD_V NOTED EXPIRE NOM_PREST
Square L'offre comprend :
Optical - Un bon d'achat de
L'Aouina : Un valeur de 250 DT
Square Optical
bon d'achat - 30% de réduction 250 60 12/10/2016 5 - NON
L'Aouina
de valeur de sur tout achat des 9h
250 DT lentilles de couleur
Le Parador la
L'offre vous
Goulette : un
propose des mets
menu de
qui vont vous ouvrir
déjeuner ou
l’appétit et donner Le Parador la
de dîner à 131 63 10/10/2016 3 - NON
à vos papilles de Goulette
partir de 49 9h
grandes envies!
DT
Choisissez l'offre
Seulement
qui vous convient...
Table « ACHATS »
INTITULE LOGIN DATE_ACHAT NBCOUPON
Square Optical L'Aouina : Un bon
d'achat de valeur de 250 DT Hello2017 13/10/2016 15h10 2
Square Optical L'Aouina : Un bon
d'achat de valeur de 250 DT Ahmed1617 14/10/2016 10h03 4
Square Optical L'Aouina : Un bon
d'achat de valeur de 250 DT Daddou123 12/10/2016 11h00 3
Le Parador la Goulette : un menu
de déjeuner ou de dîner à partir de Hello2017 12/10/2016 14h05 5
49 DT Seulement
2. Mettre à jour les enregistrements suivants :
Table « PRESTATAIRES_SERVICES »
NOM_PRES NUM
RUE_ADRESSE VILLE CP TEL EMAIL PAGE_FB
T _ADRESSE
Square Résidence Mesk [Link]
Ariana La [Link]
Optical 2 Jinen Ain 2036 71100123 Square-optical-
soukra @[Link]
L'Aouina zaghouan 396558407144821/?fref=ts
[Link]
Le Parador Immeuble Tunis La [Link]
9 2060 71899425 Parador-La-Goulette-
la Goulette Labrise Tour goulette @[Link]
Restaurant/725375497514325?fref=ts
5/6
PROSIT 4- Algèbre relationnelle & SQL
Table « MEMBRES »
LOGIN MDP NOM PRENOM
Hello2017 H198test gharbi salma
Ahmed1617 A987Tc ben chaabene ali
3. Sachant le prestataire de service « SQUARE OPTICAL L'AOUINA » n’est plus dans la
liste de nos prestataires services, 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 faire, 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és 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
