IFT 3030 Bases de données

IFT 3030 Bases de données
Automne 2005
Travail pratique #1
Professeur: Petko Valtchev
Démonstrateur: C. Frambourg,
1. Objectif
 Créer une base de données en utilisant le SGBD Oracle.
 Écrire des requêtes avec l’algèbre relationnelle.
2. Conditions de réalisation
Groupe de deux. Travail à remettre le vendredi, le 7 octobre, à midi.
Partie 1 : LDD & LMD
Sujet
Imaginons la base de données suivante qui modélise l’activité d’affaires d’un
fournisseur d’accès Internet. Les diverses tables représentent les clients (particuliers
ou compagnies), leurs comptes, les plans souscrits (en fonction du type d’accès), les
factures émises et les consommations journalières auxquelles ces dernières font
référence:
Client(noClient, nom, adresse)
Compte(noCompte, typeAcces, noClient, noPlan)
Facture(noFacture, dateFacturation, coutTotal, noCompte)
Consommation(date, noFacture, quantiteEntrant, quantiteSortant)
Plan(noPlan, appellation, prixAbonementMensuel)
Les clés primaires de chaque relation sont soulignées. Les autres contraintes d’intégrité
sont :
 les contraintes référentielles : tout attribut d’une relation ayant le même nom
qu’une clé primaire dans une relation est considéré comme une clé étrangère qui
fait référence à la clé primaire en question,
 les contraintes sur les clés candidates : leurs valeurs sont uniques et non nulles,
 l’attribut typeAcces prend ses valeurs dans {‘téléphone’, ‘cableBase’,
‘cableHauteV’},
 le reste des attributs ont une sémantique intuitive compte tenu de leurs noms et
portent, par conséquent, le type SQL le plus approprié, par exemple, nom,
appellation et adresse sont de type chaîne de caractères alors que coutTotal, et
prixAbonementMensuel sont des nombres réels,
 tous les attributs à types numériques prennent des valeurs non-négatives.

La totalité des tables mentionnées ci-dessus sont données en annexe. Pour chaque
table, un ensemble de n-uplets est fourni. La base ainsi constituée est cohérente, dans
la mesure où les contraintes d'intégrité définies sont vérifiées.
Question 1: Créez le schéma de la base de données en créant les tables qui la
constituent.
Remarque : Étant donné les restrictions des droits d’accès sur le serveur Oracle, la
création de la base de données se limitera à la création des diverses tables et donc ne
cherchera pas à créer un nouveau schéma de base au préalable.
Question 2: Peuplez la base de données en utilisant les données des tables en annexe.
Question 3: Ajouter les colonnes numéro de téléphone et nature de type entier et
chaîne de caractère, respectivement, à la table Client, avec la contrainte que la valeur
de l’attribut nature soit « particulier » ou « organisation ».
Question 4: Augmenter le prix du plan numéro ‘22’ de 5 ($).
Question 5: Ajouter un nouveau plan, de nom « 3 en 1 » et au prix de 60$/mois.
Question 6: Supprimer la compte ‘218’. Expliquez votre démarche (en commentaire).
Partie 2 : Algèbre relationnelle
En vous servant du schéma défini ci-dessus, écrire les requêtes suivantes en algèbre
relationnelle. Vous pouvez utiliser pour ceci la forme textuelle des opérateurs.
Question 1: Numéros des clients ayant un compte à accès par téléphone qui n’est pas
affecté au plan « À la carte »?
Question 2: Noms des clients qui ont fait des consommations journalières de plus de
2000 (MB) en entrée.
Question 3: Prix mensuels des plans pour lesquels aucun des comptes affectés n’est
facturé (il n’existe pas de facture pour un compte affecté à ce plan dans la base).
Question 4: Numéros des clients qui ont au moins un compte pour chacun des plans à
plus de 30 $ (de frais mensuels).
Question 5: Adresses des clients dont pour tous les comptes sont associés
uniquement à des factures de moins de 60 $ (de coût total).

Annexe
1. Schéma des table de la base e-commerce :
Client
Compte
Facture
Consommation
noClient
nom
adresse
1001
VidéoGamer++
1025 Sherbrooke E., Montréal
1004
Igor Bondartchuk
2334 É. Montpetit, Montréal
1011
Mohamed Zadeh
2348 É. Montpetit, Montréal
1042
Roco Ricotomalala
1015 Pins, Montréal
1033
Petia Touparova
312 Queen Mary, Montréal
noCompte
noClient
typeAcces
noPlan
201
1001
cableHauteV
22
202
1001
téléphone
33
205
1011
cableHauteV
11
208
1033
cableBase
44
211
1004
cableBase
22
218
1042
cableHauteV
11
235
1001
cableBase
44
245
1033
téléphone
11
noFacture
noCompte
dateFacturation
coutTotal
501
201
15/07/05
65.00
502
202
15/08/05
44.00
503
211
15/09/05
56.00
505
218
15/09/05
77.00
513
208
15/08/05
45.00
528
235
15/09/05
52.00
530
245
15/09/05
42.00
noFacture
date
quantiteEntrant
quantiteSortant
501
10/07/05
1000
400
501
11/07/05
1200
230
501
13/07/05
2300
1500
501
15/07/05
850
450
502
02/08/05
520
380
502
06/08/05
380
200
502
09/08/05
120
30
502
12/08/05
440
370
503
06/09/05
1800
1200
503
09/09/05
1120
390
505
12/09/05
4240
1700
513
06/09/05
1800
1200
528
09/09/05
1420
125b 0
528
12/09/05
2040
1300
530
10/09/05
730
520

Plan
2. Comment répondre ?
1. Partie 1 :
• Pour cela vous constituerez un fichier tp1.sql qui contiendra les réponses.
• Ce fichier devra être directement exécutable en tapant @ tp1 depuis oracle.
• Utilisez deux tirets pour indiquer les lignes de commentaires.
• Précisez également dans l’en-tête les noms des partenaires du TP.
• N’hésitez pas à commenter vos réponses.
2. Partie 2 :
• Fournir un fichier texte voire Word ou éventuellement sur support papier.
3. Procédure de remise électronique :
 remise ift3030 tp1 <votre fichier>
 Exemple de fichier.sql
-------------------------------------------------------------
-- IFT3030
-- TP1 : Fait par Maria Tereza F.T. Alves
-- -----------------------------------------------------------
-- Question1 : Création des tables
votre réponse ici
-- Question4
Ainsi de suite
noPlan
appellation
prixAbonementMensuel
11
À la carte
25.00
22
Comfort
35.00
33
Tout compris
50.00
44
Illimité
70.00