TD3 : Bases de données relationnelles

BDD1
Université Paris-Est Marne-la-Vallée BD1-TD-BDSQL3.doc
Informatique / F. Petit Février 2008 1 / 2
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 LOCALITE CAT COMPTE
B112 HANSENNE 23 A. DUMONT POITIERS C1 1250.00
C123 MERCIER 25 RUE LEMAITRE NAMUR C1 -2300.00
B332 MONTI 112 RUE NEUVE GENEVE B2 0.00
F010 TOUSSAINT 5 RUE GODEFROID POITIERS C1 0.00
K111 VANBIST 180 RUE FLORIMONT LILLE B1 720.00
S127 VANDERKA 3 AV. DES ROSES NAMUR C1 -4580.00
B512 GILLET 14 RUE DE L'ETE TOULOUSE B1 -8700.00
B062 GOFFIN 72 RUE DE LA GARE NAMUR B2 -3200.00
C400 FERARD 65 RUE. DU TERTRE POITIERS B2 350.00
C003 AVRON 8 CHEMIN DE LA CURE TOULOUSE B1 -1700.00
K729 NEUMAN 40 RUE BRANSART TOULOUSE 0.00
F011 PONCELET 17 CLOS DES ERABLES TOULOUSE B2 0.00
L422 FRANCK 60 RUE DE WEPION NAMUR C1 0.00
S712 GUILLAUME 14A CHEMIN DES ROSES PARIS B1 0.00
D063 MERCIER 201 BD DU NORD TOULOUSE -2250.00
F400 JACOB 78 CHEMIN DU MOULIN BRUXELLES C2 0.00
C_COMMANDE
NCOM NCLI DATECOM
30178 K111 2003-12-21
30179 C400 2003-12-22
30182 S127 2003-12-23
30184 C400 2003-12-23
30185 F011 2004-01-02
30186 C400 2004-01-02
30188 B512 2004-01-03
C_PRODUIT
NPRO LIBELLE PRIX QSTOCK
CS262 CHEV. SAPIN 200x6x2 75 45
CS264 CHEV. SAPIN 200x6x4 120 2690
CS464 CHEV. SAPIN 400x6x4 220 450
PA45 POINTE ACIER 45 105 580
PA60 POINTE ACIER 60 95 134
PH222 PL. HETRE 200x20x2 230 782
PS222 PL. SAPIN 200x20x2 185 1220
C_DETAIL
NCOM NPRO QCOM
30178 CS464 25
30179 PA60 20
30179 CS262 60
30182 PA60 30
30184 CS464 120
30184 PA45 20
30185 PA60 15
30185 PS222 600
30185 CS464 260
30186 CS464 3
30188 PA60 70
30188 PH222 92
30188 CS464 180
30188 PA45 22

BDD1
Université Paris-Est Marne-la-Vallée BD1-TD-BDSQL3.doc
Informatique / F. Petit Février 2008 2 / 1
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).