TD3 : Bases de données relationnelles
publicité
BDD1 TD3 : Bases de données relationnelles I) SQL avec jointure : Cas commande (multitables) Soit le schéma relationnel de la base de données GestCommande comportant les 4 tables : C_CLIENT(NCLI, NOM, ADRESSE, LOCALITE, CAT, COMPTE) C_COMMANDE(NCOM, NCLI, DATECOM) C_DETAIL(NCOM, NPRO, QCOM) C_PRODUIT(NPRO, LIBELLE, PRIX, QSTOCK) a) Soulignez les clés primaires, entourez les clés étrangères dans le schéma relationnel ci-dessus. b) Écrivez les requêtes suivantes en SQL 1) Numéro du client qui a passé la commande n° 30179. 2) Nom et localité du client qui a passé la commande n° 30179. 3) Liste des localités. 4) Liste des localités qui ont au moins un client (client ayant passé une commande). 5) Liste des clients (N°) qui ont commandé le produit CS464. 6) Liste des clients (N°, nom, localité) qui ont commandé le produit CS464. 7) Quel est le nombre de clients enregistrés qu'ils aient ou non commandés ? 8) Quel est le nombre de clients différents qui ont passé commande ? 9) Quel est le nombre de commandes effectuées par chaque client ? 10) Quels sont les produits en sapin qui font l'objet d'une commande ? 11) Pour chaque ligne (détail) de commande, calculer son prix total (prix unitaire multiplié par la quantité). 12) Calculer pour chaque commande son prix total (somme du prix unitaire multiplié par la quantité). 13) Par client, quel est le nombre de commandes effectuées, le nombre de lignes de commandes et le montant total de ses commandes ? 14) Idem 13 mais seulement pour les clients dont le montant total de commande est supérieur à 50 000 € C_CLIENT (certains n'ont pas passé de commande...) NCLI NOM ADRESSE B112 HANSENNE 23 A. DUMONT C123 MERCIER 25 RUE LEMAITRE B332 MONTI 112 RUE NEUVE F010 TOUSSAINT 5 RUE GODEFROID K111 VANBIST 180 RUE FLORIMONT S127 VANDERKA 3 AV. DES ROSES B512 GILLET 14 RUE DE L'ETE B062 GOFFIN 72 RUE DE LA GARE C400 FERARD 65 RUE. DU TERTRE C003 AVRON 8 CHEMIN DE LA CURE K729 NEUMAN 40 RUE BRANSART F011 PONCELET 17 CLOS DES ERABLES L422 FRANCK 60 RUE DE WEPION S712 GUILLAUME 14A CHEMIN DES ROSES D063 MERCIER 201 BD DU NORD F400 JACOB 78 CHEMIN DU MOULIN C_COMMANDE NCOM NCLI 30178 K111 30179 C400 30182 S127 30184 C400 30185 F011 30186 C400 30188 B512 LOCALITE POITIERS NAMUR GENEVE POITIERS LILLE NAMUR TOULOUSE NAMUR POITIERS TOULOUSE TOULOUSE TOULOUSE NAMUR PARIS TOULOUSE BRUXELLES DATECOM 2003-12-21 2003-12-22 2003-12-23 2003-12-23 2004-01-02 2004-01-02 2004-01-03 C_PRODUIT NPRO LIBELLE CS262 CHEV. SAPIN 200x6x2 CS264 CHEV. SAPIN 200x6x4 CS464 CHEV. SAPIN 400x6x4 PA45 POINTE ACIER 45 PA60 POINTE ACIER 60 PH222 PL. HETRE 200x20x2 PS222 PL. SAPIN 200x20x2 Université Paris-Est Marne-la-Vallée Informatique / F. Petit PRIX 75 120 220 105 95 230 185 QSTOCK 45 2690 450 580 134 782 1220 Février 2008 CAT C1 C1 B2 C1 B1 C1 B1 B2 B2 B1 B2 C1 B1 C2 COMPTE 1250.00 -2300.00 0.00 0.00 720.00 -4580.00 -8700.00 -3200.00 350.00 -1700.00 0.00 0.00 0.00 0.00 -2250.00 0.00 C_DETAIL NCOM NPRO 30178 CS464 30179 PA60 30179 CS262 30182 PA60 30184 CS464 30184 PA45 30185 PA60 30185 PS222 30185 CS464 30186 CS464 30188 PA60 30188 PH222 30188 CS464 30188 PA45 QCOM 25 20 60 30 120 20 15 600 260 3 70 92 180 22 BD1-TD-BDSQL3.doc 1/2 BDD1 II) Conception de base de données relationnelles (Dépendances fonctionnelles) Soit la base de données expo (num-expo, titre, prix, date_debut, date_fin, theme). Pour chaque cas ci-dessous : 1) Faites le graphe des dépendances fonctionnelles (DF). 2) Ecrivez le schéma relationnel de la base de données (clé primaire soulignée) 3) Entourez la clé étrangère. Cas à étudier : a) Chaque exposition a un seul responsable. Une personne peut être responsable de plusieurs expositions. On souhaite enregistrer pour les responsables : nom, prénom, téléphone et numéro de bureau. b) On souhaite enregistrer les libellés entiers correspondant au thème de l'exposition. c) On souhaite modifier le système de tarification. Chaque exposition peut avoir plusieurs tarifs. Le tarif d'une exposition dépend de la catégorie de clients (code et libellé, par exemple ETU pour "étudiant", SEN pour "senior", CHO pour "Chômeur", etc.). d) On souhaite pouvoir associer plusieurs thèmes à chaque exposition. e) On souhaite enrichir l'enregistrement des thèmes avec d'une part, le thème principal (unique), d'autre part, un ou plusieurs thèmes secondaires. III) Conception de base de données relationnelles (schéma Entité/association ou E/A) a) A partir du sujet Expo du chapitre II (enrichi de tous les cas précédents), faites le schéma Entité/Association (nommé aussi Modèle Conceptuel de Données ou Schéma Conceptuel de Données). b) Indiquez les cardinalités sur votre schéma E/A. c) A partir du schéma Entité/Association, écrivez le modèle relationnel en appliquant les règles de passage du modèle E/A au schéma relationnel. d) Vérifiez que vous obtenez bien le même schéma relationnel qu'en passant par la méthode des dépendances fonctionnelles (DF). Université Paris-Est Marne-la-Vallée Informatique / F. Petit Février 2008 BD1-TD-BDSQL3.doc 2/1
