IFT 3030 Bases de données
publicité
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:
constituent.
Créez le schéma de la base de données en créant les tables qui la
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
noClient
1001
1004
1011
1042
1033
nom
VidéoGamer++
Igor Bondartchuk
Mohamed Zadeh
Roco Ricotomalala
Petia Touparova
adresse
1025 Sherbrooke E., Montréal
2334 É. Montpetit, Montréal
2348 É. Montpetit, Montréal
1015 Pins, Montréal
312 Queen Mary, Montréal
Compte
noCompte
201
202
205
208
211
218
235
245
noClient
1001
1001
1011
1033
1004
1042
1001
1033
typeAcces
cableHauteV
téléphone
cableHauteV
cableBase
cableBase
cableHauteV
cableBase
téléphone
noPlan
22
33
11
44
22
11
44
11
Facture
noFacture
501
502
503
505
513
528
530
noCompte
201
202
211
218
208
235
245
dateFacturation
15/07/05
15/08/05
15/09/05
15/09/05
15/08/05
15/09/05
15/09/05
coutTotal
65.00
44.00
56.00
77.00
45.00
52.00
42.00
Consommation
noFacture
501
501
501
501
502
502
502
502
503
503
505
513
528
528
530
date
10/07/05
11/07/05
13/07/05
15/07/05
02/08/05
06/08/05
09/08/05
12/08/05
06/09/05
09/09/05
12/09/05
06/09/05
09/09/05
12/09/05
10/09/05
quantiteEntrant
1000
1200
2300
850
520
380
120
440
1800
1120
4240
1800
1420
2040
730
quantiteSortant
400
230
1500
450
380
200
30
370
1200
390
1700
1200
125b 0
1300
520
Plan
noPlan
11
22
33
44
appellation
À la carte
Comfort
Tout compris
Illimité
prixAbonementMensuel
25.00
35.00
50.00
70.00
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
