Bases de Données (BD3) Corrigé de l'examen

Université Paris Diderot - L2 Informatique 13 janvier 2017
Bases de Données (BD3) - Corrigé de l"examen (durée : 3 heures)
Documents autorisés : trois feuilles A4 recto-verso et
personnelles. Les ordinateurs et les téléphones mobiles sont interdits.
Le barême est donné à titre indicatif.
Exercice 1[Requêtes:12 points]
Soit la base de donnéesBANQUEcontenant les tables suivantes :
AGENCE (*Num_Agence, Nom, Ville, Actif)
CLIENT (*Num_Client, Nom, Prenom, Ville)
COMPTE (*Num_Compte, Num_Agence#, Num_Client#, Solde)
EMPRUNT (*Num_Emprunt, Num_Agence#, Num_Client#, Montant)
Les clefs primaires sont précédées d"une étoile (*) et les clefs étrangères sont suivies d"un
astérisque (#).
1.
Sans utiliser DISTINCT, donnez une requête équiv alenteen SQL :
SELECT DISTINCT Num_Client
FROM COMPTE
WHERE solde < 1000
OR solde > 100000 ;
Une première solution exploite le fait que l"opérateur d"union est un opérateur ensem-
bliste et élimine donc les doublons :
(SELECT Num_Client
FROM COMPTE
WHERE solde < 1000)
UNION
(SELECT Num_Client
FROM COMPTE
WHERE solde > 100000) ;
Une seconde solution détourne leGROUP BY(utilisé d"ordinaire dans le cadre des re-
quêtes d"agrégation) :
SELECT Num_Client
FROM COMPTE
WHERE solde < 1000
OR solde > 100000
GROUP BY Num_Client ;
Une troisième solution exploite le fait que Num_Client est clef primaire de client :
1
SELECT Num_Client
FROM CLIENT
WHERE Num_Client IN
(SELECT Num_Client
FROM COMPTE
WHERE solde < 1000
OR solde > 100000) ;
J"ai également vu cette solution, un peu laborieuse mais correcte (intersect est un
opérateur ensembliste et élimine donc les doublons) :
(SELECT Num_Client
FROM COMPTE
WHERE solde < 1000 OR solde > 100000)
INTERSECT
(SELECT Num_Client
FROM COMPTE
WHERE solde < 1000 OR solde > 100000) ;
Une erreur rencontrée fréquemment dans les copies :
SELECT Num_Client
FROM COMPTE
GROUP BY Num_Client
HAVING solde < 1000
OR solde > 100000;
Un attribut figurant dans leHAVINGdoit figurer dans leGROUP BYou bien être utilisé
dans un agrégat. À un même numéro de client peuvent en effet être associés plusieurs
soldes différents (un client peut avoir plusieurs comptes), il y a donc ambiguïté.
Attention : ce n"est pas parce que l"information concernant chaque compte d"un client
n"apparait qu"une seule fois qu"il n"y a pas de doublons (autre erreur fréquente). Un
client peut en effet avoir plusieurs comptes.
2.
Ecriv ezles requêtes SQL corresp ondantaux questions suiv antes:
(a)
Les clien tsn"a yantpas de compt edans la même agence que Liliane Bettencour t.
(Tableau résultat :Num_Client).
Attention, la question est plus difficile qu"il n"y parait. Malgré les apparences la
requête suivante par exemple ne convient pas :
SELECT Num_Client
FROM COMPTE
WHERE Num_Agence NOT IN
(SELECT Num_Agence
FROM COMPTE NATURAL JOIN CLIENT
WHERE Client.Nom='Bettencourt"
AND Client.Prenom='Liliane") ;
Cette requête retourne les clients qui ont un compte dans une agence dans laquelle
Liliane Bettencourt n"a pas de compte.
En fait, il fallait écrire quelque chose de plus compliqué, par exemple :
2
SELECT Num_Client
FROM Client
EXCEPT
(Select Num_Client
FROM Compte
WHERE Num_Agence IN
(SELECT Num_Agence
FROM COMPTE NATURAL JOIN CLIENT
WHERE Client.Nom='Bettencourt"
AND Client.Prenom='Liliane")) ;
ou bien
SELECT Num_Client
FROM Client
WHERE Num_Client NOT IN
(Select Num_Client
FROM Compte
WHERE Num_Agence IN
(SELECT Num_Agence
FROM COMPTE NATURAL JOIN CLIENT
WHERE Client.Nom='Bettencourt"
AND Client.Prenom='Liliane")) ;
Il existe bien sûr plusieurs variantes en fonction de la manière dont sont faites les
jointures, e.g., :
SELECT Num_Client
FROM Client
WHERE Num_Client NOT IN
(SELECT Num_Client
FROM Compte C1, Compte C2, Client C
WHERE C1.Num_Agence=C2.Num_Agence
AND C.Num_Client=C2.Num_Client
AND C.Nom='Bettencourt"
AND C.Prenom='Liliane")) ;
Il était également possible d"utiliser NOT EXISTS.
(b)
Les agences a yantu nactif plus élev éque toutes les ag encesde Sain t-Ouen.( Tableau
résultat :Num_Agence).
SELECT Num_Agence
FROM Agence
WHERE Actif > ALL
(SELECT Actif
FROM Agence
WHERE Ville='Saint Ouen") ;
Attention, utiliser> ANYau lieu de> ALLsélectionne les agences ayant un actif plus
élevé qu"au moins une agence de Saint Ouen.
Une solution équivalente à celle utilisant> ALL:
3
SELECT Num_Agence
FROM Agence
WHERE Actif >
(SELECT Max(Actif)
FROM Agence
WHERE Ville='Saint Ouen") ;
Attention, la syntaxe.... > MAX (SELECT Actif FROM...)est incorrecte.
(c)
Le solde mo yendes comptes clien ts,p ourc haqueagence don tle solde mo yenest
supérieur à 10000. (Tableau résultat :Num_Agence,Solde_Moyen).
SELECT AVG(Solde) as Solde_Moyen
FROM Compte
GROUP BY Num_Agence
HAVING AVG(Solde) > 10000 ;
Attention, la requête suivante, qui a l"air innocente, fonctionne avec MySQL, mais
pas avec Postgres (ERROR : column "Solde_Moyen" does not exist) :
SELECT AVG(Solde) as Solde_Moyen
FROM Compte
GROUP BY Num_Agence
HAVING Solde_Moyen > 10000 ;
J"ai évidemment compté tous les points, mais l"exemple permet de pointer que le
standard de SQL est implémenté différemment d"un système à l"autre.
J"ai compté la moitié des points (ce qui était déjà trop gentil) pour la requête
suivante, qui est incorrecte (jamais d"agrégat dans le WHERE) :
SELECT AVG(Solde) as Solde_Moyen
FROM Compte
GROUP BY Num_Agence
WHERE AVG(Solde) > 10000 ;
(d)
Le nom brede clien tsde l"agence de nom "P aris-BNF"don tla ville n"est pas rensei-
gnée dans la relation CLIENT. (Tableau résultat :Nombre).
SELECT COUNT(DISTINCT num_client) as Nombre
FROM Client, Compte, Agence
WHERE Client.Num_client=Compte.Num_client
AND Agence.Num-Agence=Compte.Num-Agence
AND Agence.Nom='Paris-BNF"
AND Client.Ville IS NULL ;
Attention à la jointure naturelle surClientetAgence. La jointure sera entre autres
faite sur les deux attributs ville, ce qui forcera une contrainte supplémentaire sur
les données (si un client possède un compte dans une agence domiciliée dans une
ville différente de celle où il habite, alors ce compte n"apparaitra pas). Attention
également à ne pas oublier le mot clefDistinct: nous ne voulons compter chaque
client ayant un compte dans l"agence "Paris-BNF" qu"une seule fois, même s"il y
possède plusieurs comptes. Une solution alternative sansDistinct:
4
SELECT SUM(num_client) as Nombre
FROM Client
WHERE Ville IS NULL
AND Num_Client IN
(SELECT Num_Client
FROM Compte NATURAL JOIN Agence
WHERE Agence.Nom='Paris-BNF") ;
Attention également à ne pas écrire de condition du type
Agence.ville not in (Select Client.ville from Client)(vu dans une
copie). Au delà du fait que ce n"est pas ce qu"on veut (la ville pourrait ne pas
être renseignée tout en n"appartenant pas à cette table), null not in (...) n"est ni
vrai, ni faux, c"est indéterminé, donc la condition ne sera pas satisfaite si l"attribut
est nul... Pour la même raison on n"écrit jamaisVILLE = NULL(qui est de toutes
façons syntaxiquement incorrect), maisVILLE IS NULL.
(e)
Les clien tsa yantun compte don tle solde est sup érieurà la somme totale de tous
les actifs des agences de Saint-Ouen. (Tableau résultat :Num_Client).
SELECT Num_Client
FROM Compte
WHERE Solde >
(SELECT SUM(Actif)
FROM Agence
WHERE Ville='Saint-Ouen") ;
Si l"on veut éviter les doublons liés au fait qu"un même client pourrait avoir plusieurs
comptes satisfaisant cette propriété, on peut utiliser le mot clefDISTINCT:
SELECT DISTINCT Num_Client
FROM Compte
WHERE Solde >
(SELECT SUM(Actif)
FROM Agence
WHERE Ville='Saint-Ouen") ;
(f)
Les clien tsdon tla somme du solde de tous les comptes est inférieure à l"actif de
chaque agence. (Tableau résultat :Num_Client).
SELECT Num_Client
FROM Compte
GROUP BY Num_Client
HAVING SUM(Solde) <
(SELECT MIN(Actif)
FROM Agence) ;
Un exemple parmi beaucoup d"autres d"erreur rencontrée dans les copies :
SELECT Num_Client
FROM Compte, Agence
GROUP BY Num_Client
HAVING SUM(Solde) < Actif ;
5
Plusieurs problèmes ici : le produit cartésien surCompteetAgence"multiplie les
soldes" et pour chaqueNum_Client,SUM(Solde)sera donc beaucoup plus élevé que
prévu. Par ailleurs,< Actifn"est syntaxiquement pas correct, carActifn"est qu"un
nom d"attribut et ne définit pas le résultat d"une requête. L"objet de l"opérateur de
comparaison n"est donc pas défini à droite.
(g)
Les clien tsa yantun compte dans toute sles agences de Sain t-Ouen.(T ableaurésul-
tat :Num_Client).
Une première solution sans utiliser l"agrégation, mais avec double imbrication et
négations :
SELECT Num_Client
FROM Client
WHERE NOT EXISTS
(SELECT * FROM Agence
WHERE Ville='Saint-Ouen"
AND NOT EXISTS
(SELECT * FROM Compte
WHERE Client.Num_Client=Compte.Num_Client
AND Compte.Num_Agence=Agence.Num_Agence));
Afin d"écrire cette requête, on commence par traduire "Pour toute agence de Saint-
Ouen, le client y a un compte" en "il n"existe pas d"agence de Saint-Ouen telle que
le client n"y a pas de compte" (en calcul relationnel :8x'(x) :(9x:'(x)), avec
'une formule quelconque du calcul relationnel).
Une requête équivalente avecNOT INau lieu du secondNOT EXISTS:
SELECT Num_Client
FROM Client
WHERE NOT EXISTS
(SELECT * FROM Agence
WHERE Ville='Saint-Ouen"
AND Num_Client NOT IN
(SELECT Num_Client FROM Compte
WHERE Compte.Num_Agence=Agence.Num_Agence));
Une autre solution sans négation, mais avec de l"agrégation :
SELECT Num_Client
FROM Compte, Agence
WHERE Compte.Num_Agence=Agence.Num_Agence
AND Ville='Saint-Ouen"
GROUP BY Num_Client
HAVING COUNT(DISTINCT Num_Agence)=
(SELECT COUNT(DISTICT Num_Agence)
FROM Agence
WHERE Ville='Saint-Ouen");
Cette dernière solution est basée sur le fait que si un client a un compte dans
chacune des agences de Saint-Ouen et qu"il y a exactementnagences à Saint-Ouen,
alors ce client a un compte dans exactementnagences à Saint-Ouen.
6
3.Ecriv ezen algèbre relationnelle l esrequêtes corresp ondantaux questions suiv antes:
(a)
Les clien tsrésidan tà P aris,a vecun compte don tle solde est sup érieurà 10000 et un
emprunt dont le montant est inférieur à 100000. (Tableau résultat :Num_Client.)
J"ai compté cette solution comme juste :

Num_Client(ville=`Paris0(Client)./ solde>1000(Compte)./ montant1000(Compte))
Num_Client(montant1000(Compte)Montant1000^Montant
LIF4 - TD9 Requêtes SQL Correction - CNRS 2.3.3 Exercices corrigé . Requêtes sur une base de données existante . Supposons que le serveur soit celui d'IMDb qui gère une base de données 
Travaux Dirigés de Base de données Correction. Exercice 1: Donner un schéma de base de données correspondant a ce diagramme On considere le Schéma de la base de données CINEMA
Exercices de conception de bases de données - Stéphane Crozat Exercice 1 : Consultation et sélection simple de données. La base de données est utilisée pour la gestion simplifiée des ventes
Initiation pratique a la macroeconomie, 2e edition. Etudes de cas Auto-manuel de macro-économie, cours, exercices et corrigés, Édition . Initiation à la macro Cas, Exercices Corriges Et Qcm. comment5 ITB - 2e année d'études Étudiant Les marchés de capitaux Examen. QCM Énoncé Durée 
COMPACT Physique : Cours et 250 exercices corriges de PCSI PCSI, MPSI, PT : le cours et 250 exercices corrigés / Chérif Zananiri,. 9 mars 2017 auto-adjoints avec exercices et solutions. 100 QCM COMPACT Physique : Cours Et 250 Exercices Corriges De PCSI - MPSI - PT
Sujets corriges du bac 99, sciences de la vie et de la terre terminale Sujets Corriges Du Bac 99, Sciences De La Vie Et De La Terre .s preuve et m thodes du bac exercice corrige tle s physique chimie . re simple Outil de traduction semi-automatique depuis un sujet de bac ou du code Python, .
Read eBook » automatique - systèmes linéaires, non linéaires événements discrets - cours et exercices corrigés (3e édition) CORRIGE (3EMEDITION) - To read automatique - systèmes linéaires
Pratique des normes IAS/IFRS : 40 cas d'application avec corrigés ment les travaux de l'I.R.Ma. sur le sujet les personnes Le P.C.S. apparaît plus que jamais comme l'instrument de gestion de toutes Cette deuxième édition intitulée « Plan Communal de Sauvegarde - S'organiser pour être prêt Parallèlement
à télécharger 1 - Faculté des Sciences Economiques, Commerciales meilleure maîtrise des techniques formulées par les normes IAS/IFRS. Elle subvention d'investissement virée au résultat de l'exercice. 6 000. Totaux Le swap est un instrument de couverture de l'emprunt et des intérêts à payer.
Les Zoom's. Exercice d'analyse financière - 5e édition La maitrise des instruments et des techniques de paiements internationaux. Herland M., auto-manuel de macroéconomie : cours, exercices et corrigés cote A
Corrigés TD-TP Sciences et techniques sanitaires EM consulte Exercices de comptabilité générale avec corrigés 2010/2011. maîtrise des principes et des techniques de l'analyse financière, également Le compte de résultat différentiel. Le seuil de rentabilité. Instruments de trésorerie .
COMMENT RAISONNER À L'INTERNATIONAL? TD 6 Les organismes complémentaires et les inégalités de couverture Il s'agit de mieux maîtriser les dépenses de santé en diminuant Le paiement à l'acte peut favoriser la multiplication des actes au-delà du nécessaire