Requetes complexes + relations à vérifier

Version imprimable

Voir 40 message(s) de cette discussion en une page

27/03/2010, 18h36
jack_78

Requetes complexes + relations à vérifier

Bonjour à tous,
Je suis lycéen et je dois faire un projet.
Donc voilà, mon sujet parle d'un Video club et je dois aider le propriétaire dans le domaine informatique et notamment sur ACCESS avec du SQL bien compliqué :? .
Donc voici les consignes que j'ai et ce que j'ai fait :

***1) CORRIGER LES RELATIONS***
L'original c'est ça :
http://img337.imageshack.us/img337/4670/98069668.jpg
Et voilà ma correction (désolé pour la taille, je suis pas très calé en informatique) :
http://img267.imageshack.us/img267/6364/51390948.jpg

Ma correction est-elle bonne d'après vous ? :oops:

***2) ETABLIR LES REQUETES***
Voici un extrait du dossier :
"J’aimerais pouvoir procéder au calcul automatisé de mon chiffre d’affaires sachant que le prix de location d’un DVD est de 3 € pour 2 jours avec 2 € par jour supplémentaire.

Il serait souhaitable d’améliorer la gestion de l’acquisition mensuelle de mes nouveaux DVD. Je suppose que l’utilisation de certaines fonctionnalités des logiciels devrait m’aider pour choisir chaque mois le nombre de DVD à acheter dans chaque genre (la répartition pourrait s’effectuer en fonction du pourcentage des locations des différents genres de DVD au cours du mois précédent, par exemple). J’aimerais déjà savoir quels sont ces pourcentages même si nous n’avons pas encore saisi dans la base de données toutes les locations de janvier.

Je connais bien mes clients et je sais que la plupart d’entre eux sont prêts à me donner pour ma base de données leur adresse électronique ou leur numéro de téléphone portable.
Est-il possible d’envoyer un message aux clients qui ont loué au moins trois DVD dans le genre qui a été le plus loué pendant le mois précédent pour les informer des nouveautés acquises ? Pourquoi ne pas ensuite essayer d’informer tous les clients des nouveautés acquises dans leur genre préféré."

Déja je pense qu'il y en a 3 :
- Le genre le plus loué le mois précédent
- Les clients qui ont loué au moins 3 DVD dans le genre le plus loué
- Les nouveautés acquises dans le genre préféré de chaque client

Mais là je patoge, on n'a jamais fait des requetes aussi compliqué :(:(:(

Merci d'avance pour votre aide.
27/03/2010, 20h27
f-leb

Salut Jack,

Je ne sais pas si tu as eu des notions de normalisation des bases de données en cours mais ta table Film n’est pas en 3è forme normale à cause de la dépendance fonctionnelle CodeGenre→IntituléGenre (il ne peut y avoir de dépendance entre deux attributs non clés). Il faut y remédier.

De plus au sens Merise, la table Louer est probablement issue d’une association entre les entités Client, Film et DateLocation.
Or tu as dû voir que la clé primaire d’une table associative est obtenue par concaténation de clés étrangères correspondant aux entités liées. Il faudrait donc définir la clé primaire de la table Louer en conséquence.

Citation:

…en fonction du pourcentage des locations des différents genres de DVD au cours du mois précédent, par exemple). J’aimerais déjà savoir quels sont ces pourcentages…

En voilà une avec des pourcentages de location par genre…

Citation:

… aux clients qui ont loué au moins trois DVD dans le genre qui a été le plus loué pendant le mois précédent …

En voilà une deuxième.

Citation:

… essayer d’informer tous les clients des nouveautés acquises dans leur genre préféré.

Et trois pour le genre préféré par client…

Dans certaines de ces requêtes, il faudra peut-être passer par une sous-requête.

Commence par en choisir une et si tu as des difficultés, tu postes le code SQL de ce que tu as essayé de faire.
On verra alors ce qu’on peut faire pour t’aider:ccool:.
28/03/2010, 13h18
jack_78

Bonjour f-leb,
Merci pour votre aide.

Tout d'abord, concernant CodeGenre et IntituléGenre j'ai crée une nouvelle table GENRE, j'ai mis CodeGenre en clé primaire et l'autre Codegenre dans FILM je l'ai laissé pour faire en faire une clé étrangère.
Ensuite j'ai fait copier/coller Codegenre et IntitiléGenre dans ma nouvelle table GENRE, il n'arrete pas de me dire ERREUR : RISQUE DE DOUBLONS ???!!! :aie:
J'ai essayé tous les options : Integrité réferentielle, type de jointures ... mais en vain il veut rien savoir :cry:.
Et puis quand j'ai fait la jointure entre les 2 Codegenre il me met pas le symbole "illimité", je pense que c'est dûe à ça :roll:.

Et pour Datelocation en cléprimaire, j'y pensais aussi, mais il veut pas me le mettre, il me dit aussi : ERREUR dans champ index ...

Pour les requetes, j'attends déja de finir la correction de mes relations :mrgreen: y'a beaucoup de boulot :calim2:

P.S : je vous donne ma base de données pour constater par vous-même les messages d'erreurs qu'il me dit :
Lien

Bonne journée ! :ccool:
28/03/2010, 14h47
f-leb

OK, voila comment on peut procéder (fait une copie de ta base avant, on ne sait jamais:aie:…):

- Dans la table Louer (champs codefilm et codeclient), enlever la propriété "valeur par défaut"=0
- Supprimer la table Genre (on va la recréer).

Sous Access 2007 (fichier au format accdb), tu as un outil sympa pour dénormaliser les bases.
Aller dans l’onglet "Outils de base de données" puis dans "Analyse table",
Dans l’assistant "Analyseur de table", sélectionne la table FILM et laisse l’assistant décider.
L’assistant te propose automatiquement une nouvelle table des genres (Table2) , je sais c'est magique!!:
http://upimages.free.fr/10/86/51202.png

En double-cliquant sur Table1 et Table2, tu peux les renommer en FILM2 (FILM existe déjà) et GENRE.
Lorsque tu cliques sur "suivant", l’assistant te propose de mettre en place les clés primaires. Dans la nouvelle table FILM2, tu sélectionnes CodeFilm et tu cliques sur le bouton avec la ‘tite clé.

Ensuite, il faut faire un peu de ménage dans la fenêtre des relations d’Access, supprimer l’ancienne table FILM, raccorder la nouvelle avec la table LOUER. La clé primaire sur le triplet {CodeClient,CodeFilm, DateLocation} ne pose plus de problème.:ccool:
http://upimages.free.fr/10/86/51203.png

voila voila...y'a plus qu'à8-)
28/03/2010, 15h19
jack_78

Merci infiniment pour ces explications ultra précises ;););)
Donc j'ai enfin reussi à refaire les tables FILM et GENRE, instaurer les clés primaires, relier les tables, mettre de l'ordre.

Mais par contre, la table LOUER reste récalcitrante, je n'arrive pas à mettre en place aucune des 3 clés primaire/ 2 étrangères : Datelocation, Codefilm, Codecli. J'ai essayé avec l'assistant magique 8-), il me dit de "ne pas fractionner" ma table :roll: Qu'est-ce que cela signifie ??? :?
http://img52.imageshack.us/img52/1764/70069713.jpg
http://img256.imageshack.us/img256/2830/33316770.jpg

Lien de ma base actuelle

Bonne journée ! :ccool:
28/03/2010, 15h31
f-leb

Citation:

Envoyé par jack_78

Mais par contre, la table LOUER reste récalcitrante, je n'arrive pas à mettre en place aucune des 3 clés primaire/ 2 étrangères : Datelocation, Codefilm, Codecli. J'ai essayé avec l'assistant magique 8-), il me dit de "ne pas fractionner" ma table :roll: Qu'est-ce que cela signifie ??? :?

bah ici, il ne faut plus passer par cet assistant car il ne s'agit pas de fractionner la table LOUER.

En fait, tu y es presque.
Dans l'ordre, et en mode création de table ,mettre la clé primaire sur le triplet {codeClient,codefilm,datelocation} (j'ai testé ça marche).
Dans la fenêtre des relations d'Access, supprime la relation Louer--FILM et reconstruit-là, ça devrait marcher...
28/03/2010, 16h51
jack_78

C'est bon, j'ai reussi !!! :P:P:P:P:P:P:P:P:P:P:P:P:P:P:P:P
En fait, quand j'avais cliqué gauche avec CTRL, je n'avais pas placé mon curseur dans la petite zone à gauche (celui qui est bleu-violet clair), c'est pour ça que je n'arrivais pas à sélectionner plusieurs champs.

Donc voilà mon SGBD enfin corrigé :
http://img38.imageshack.us/img38/4853/83967276.jpg

Il ne me reste plus que les requêtes "imbriquées", on les appele comme ça au lycée, je vais me lancer dès que j'ai du temps libre et je vous tiendrais au courant.

P.S : Et encore un grand merci F-LEB ! ;)

Rebonsoir,
Je viens de faire sur brouillon sur feuille plusieurs fois mes requêtes, j'en viens donc à ça :

1) On n'a jamais vu les pourcentage en language SQL, je patoge mais j'en suis arrivé là :

Code:

1
2
3
4
5
6
SELECT % Datelocation
FROM LOUER, GENRE
WHERE LOUER.Codefilm=FILM.Codefilm
AND FILM.Codegenre=GENRE.Codegenre
AND Datelocation BETWEEN 01/12/2008 AND 31/12/2008
GROUP BY Codegenre

Code:

1
2
3
4
5
6
7
8
9
SELECT Codecli, Nomcli, COUNT (Datelocation) AS [Nombredelocations], MAX 
(Nombredelocations) AS [GENRE LE PLUS LOUE]
FROM CLIENT, LOUER, FILM, GENRE
WHERE CLIENT.Codecli=LOUER.Codecli
AND LOUER.Codefilm=FILM.Codefilm
AND FILM.Codegenre=GENRE.Codegenre
AND Datelocation > 3
AND Datelocation BETWEEN 01/12/2008 AND 31/12/2008
GROUP BY Codegenre

Code:

1
2
3
4
5
6
7
SELECT Codegenre, Intitulégenre, COUNT(MAX:Datelocation) AS [Nombre de 
locations le plus élevé dans un genre] 
FROM CLIENT, LOUER, FILM, GENRE
WHERE CLIENT.Codecli=LOUER.Codecli
AND LOUER.Codefilm=FILM.Codefilm
AND FILM.Codegenre=GENRE.Codegenre
GROUP BY Codecli, Codegenre

Voilà :)
Dîtes moi ce que vous en pensez ... :mrgreen:

Bonne soirée ! :zoubi:

29/03/2010, 00h24
f-leb

bon, il y a de l'idée mais aucune ne va marcher:aie:

dis-moi, quelques questions:
- as-tu vu les jointures avec INNER JOIN, LEFT JOIN,... ?
- est-tu obligé d'utiliser du SQL standard (et donc éviter les fantaisies autorisées par Access) ?
- pourquoi ne t'aiderais-tu pas des assistants proposés par Access pour construire tes requêtes et récupérer le code SQL?

Concernant ta 1ère requête (les autres je verrais plus tard parce que là :zzz:),
le pourcentage est à calculer en divisant le nombre de location par genre, par le nombre total de location. Le nombre total étant obtenu par une sous-requête, tu vois ?
29/03/2010, 13h44
jack_78

Citation:

Envoyé par f-leb

bon, il y a de l'idée mais aucune ne va marcher:aie:

dis-moi, quelques questions:
- as-tu vu les jointures avec INNER JOIN, LEFT JOIN,... ?
- est-tu obligé d'utiliser du SQL standard (et donc éviter les fantaisies autorisées par Access) ?
- pourquoi ne t'aiderais-tu pas des assistants proposés par Access pour construire tes requêtes et récupérer le code SQL?

Concernant ta 1ère requête (les autres je verrais plus tard parce que là :zzz:),
le pourcentage est à calculer en divisant le nombre de location par genre, par le nombre total de location. Le nombre total étant obtenu par une sous-requête, tu vois ?

On n'a jamais vu Inner join ni Left join. :aie: .
En fait j'ai fait sur brouillon pour me mettre en tête ce que je devrais mettre sur ACCESS.
Je vais essayer de faire la 1ere requête sur ACCESS et je vous tiens au jus ;)
01/04/2010, 19h07
jack_78

Rebonsoir,
Donc là je viens de commencer les requêtes en m'aidant du site que vous m'avez passé, je commence par la 2), qui me semble être la plus facile par rapport aux autres.
Dès que j'ai fini je vous tiens au courant :ccool:
01/04/2010, 19h32
jack_78

Donc voilà où j'en suis :
[http://img40.imageshack.us/img40/516/sanstitremi.jpg

Où est-ce que j'ai fait une erreur ? :cry:
J'ai utilisé la fonction HAVING, j'ai vu ça dans une leçon de SQL sur internet.
01/04/2010, 22h06
f-leb

marchera pas comme ça:aie:, visualise le code SQL correspondant pour t'en convaincre (dans le requêteur graphique, le pt'it bouton [SQL] en bas à droite).
Pour les requêtes avec regroupement (GROUP BY), il faut activer le bouton:http://upimages.free.fr/10/90/51541.png

reprenons:

Citation:

Envoyé par jack_78

- Les clients qui ont loué au moins 3 DVD dans le genre le plus loué

le plus simple est de décomposer le problème:
1°) déterminer le nombre de locations par genre (GROUP BY et COUNT)
2°) retenir dans la requête du 1°), le genre le plus loué (requête retournant le codeGenre correspondant) (MAX)
3°) déterminer par client, le nombre de films loués (COUNT) dans le genre déterminé au 2° (WHERE). Ne retenir que ceux ayant le nbre de films >=3 (HAVING).

bref, avec les assistants on peut y arriver avec 3 requêtes pour obtenir le résultat.

courage...
02/04/2010, 19h48
jack_78

Bonsoir,
J'ai donc suivi vos instructions, et après 4h30 de lutte acharné contre mon PC j'ai enfin réussi !!! :mrgreen::mrgreen::mrgreen:
http://img691.imageshack.us/img691/8873/11159032.jpg
http://img220.imageshack.us/img220/9316/60901951.jpg
http://img220.imageshack.us/img220/2623/47009845.jpg

Donc maintenant il me reste encore la dernière requête :
Pourcentage des locations des différents genres de DVD au cours du mois de janvier.

Par contre là, je ne trouve pas de cours sur internet concernant les pourcentages :(
Comment demander à Access de calculer des % ?

Bonne soirée
03/04/2010, 18h55
f-leb
Bonjour Jack,

Citation:

…et après 4h30 de lutte acharné contre mon PC j'ai enfin réussi !!!

Bel effort dans la lutte camarade, mais…non, ça ne marche pas encore:aie: et la lutte doit continuer…
Edite le code SQL (bouton [SQL] en bas à droite), ton Max(CompteDeDateLocation) ne sert à rien…

Toutefois, je pense que tu as compris le truc, donc la victoire n’est pas loin. En fait, tu as juste grillé une étape intermédiaire (la 2°) :

Citation:

Envoyé par f-leb

2°) retenir dans la requête du 1°), le genre le plus loué (requête retournant le codeGenre correspondant) (MAX)

Du style :
Code:

1 2 SELECT CodeGenre as GenreLePlusLoué FROM R_NbrLocationsParGenre WHERE NbrLocations=(SELECT Max(TEMP.NbrLocationsParGenre) FROM R_NbrLocationsParGenre TEMP)
qui va t’obliger à coder en partie directement en mode SQL.

De plus, pense à ne mettre dans tes requêtes, que les tables indispensables. Dans ta 1ère requête, la table CLIENT n’est pas utile.

Citation:

Envoyé par jack_78

Par contre là, je ne trouve pas de cours sur internet concernant les pourcentages
Comment demander à Access de calculer des % ?

Plus haut j’avais écrit :

Citation:

Envoyé par f-leb

le pourcentage est à calculer en divisant le nombre de locations par genre, par le nombre total de locations. Le nombre total étant obtenu par une sous-requête, tu vois ?

un truc du genre:

Code:

SELECT..., Count(*)*100/(SELECT NbreLocationsTotal FROM R_NbrLocationsTotal) as Pourcentage FROM ...GROUP BY GENRE.CodeGenre

Là aussi à rédiger en partie directement en mode SQL.

Courage, encore quelques heures (:mrgreen:) et ça devrait être bon mais n’hésite pas à revenir au besoin…
03/04/2010, 20h16
jack_78

Grrrr, moi qui pensait avoir enfin réussi :?:?:?
En fait, j'ai pas réussi à le faire en 3 étapes, mais en 2.

Enfin bref,
En fait je viens de checker dans la relation, en effet le réultat obtenu n'est pas correct, le client BERNARD par exemple n'a pas loué + de 3 DVD dans le genre comédie. :aie:

J'ai donc mis cette formule SQL :
SELECT CodeGenre AS GenreLePlusLoué FROM R_CompteDeDateLocation
WHERE CompteDeDateLocation=(SELECT Max(TEMP.CompteDeDateLocation) FROM R_CompteDeDateLocation TEMP)

Mais en bas il m'affiche : "L'action ou l'évènement a été bloqué par le mode désactivé." :aie:

Je vous montre ma base actuelle :
Lien

Pour les pourcentages, je vais déja essayer de finir celui-là :mrgreen:

Bonne soirée ! :ccool:
03/04/2010, 20h43
jack_78

Rebonsoir,
Finalement en trichant un peu j'ai réussi à avoir le bon résultat :ccool: .
En fait j'ai conservé : Le genre le plus loué
Et j'ai ajouté : Les clients qui ont loué plus de 3 DVD dans le genre : COMEDIE.

Mais, cela ne pose pas de problème, je pourrais dire au jury que compte tenu de nos connaissances minimes en SQL, nous avons utiliser des méthodes barbares :lol:. Cela n'influe en aucun cas les points.

Donc voilà, on y est enfin ! :ccool:

Now, je vais commencer le pourcentage :mouarf:
03/04/2010, 21h10
jack_78

http://img528.imageshack.us/img528/642/36286124.jpg

Voilà où j'en suis pour les pourcentages, pomé ... :aie:
En mode SQL direct, j'y comprend rien du tout et il n'accepte rien du tout "L'action ou l'évènement a été bloqué par le mode désactivé."
03/04/2010, 22h01
f-leb

Aïe ! … ma faute:oops:.

Quand j’écris :

Citation:

…à coder en partie directement en SQL

tu as naturellement sélectionné "SQL direct" :

Citation:

Envoyé par Aide Access F1

(requête SQL directe : …utilisée pour envoyer des commandes directement à un serveur de base de données ODBC…

Ce qui n’a rien à voir avec notre problème :aie:

En fait je voulais parler du mode d’affichage de la requête : "Mode création" ou "Mode SQL" (dans l’outil Requête, le bouton "Affichage" en haut à gauche permettant la bascule d’un mode à l’autre).

Bonne fin de soirée…
04/04/2010, 10h25
jack_78

Bonjour,
Je pense que j'y suis presque, il m'affiche des dièses au lieu de pourcentage :roll:

Voici une image :
http://img130.imageshack.us/img130/4792/sanstitreuu.jpg

Et ma base :
Lien

Bonne journée :ccool:

Voir 40 message(s) de cette discussion en une page