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