Discussion :

[jack_78]

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 :

Et voilà ma correction (désolé pour la taille, je suis pas très calé en informatique) :


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

***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.

[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.

…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…

… 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.

… 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.

[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 ???!!!
J'ai essayé tous les options : Integrité réferentielle, type de jointures ... mais en vain il veut rien savoir .
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 .

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 y'a beaucoup de boulot

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 !

[f-leb]

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

- 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!!:


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.


voila voila...y'a plus qu'à

[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 , il me dit de "ne pas fractionner" ma table Qu'est-ce que cela signifie ???



Lien de ma base actuelle

Bonne journée !

[f-leb]

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 , il me dit de "ne pas fractionner" ma table 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...

[jack_78]

C'est bon, j'ai reussi !!!
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é :


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 !

[jack_78]

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 sql :

Sélectionner tout - Visualiser dans une fenêtre à part

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

2)

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

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

3)

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

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 ...

Bonne soirée !

[f-leb]

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

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à ),
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 ?

[jack_78]

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

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à ),
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. .
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

[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

[jack_78]

Donc voilà où j'en suis :
[

Où est-ce que j'ai fait une erreur ?
J'ai utilisé la fonction HAVING, j'ai vu ça dans une leçon de SQL sur internet.

[f-leb]

marchera pas comme ça, 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:

reprenons:

- 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...

[jack_78]

Bonsoir,
J'ai donc suivi vos instructions, et après 4h30 de lutte acharné contre mon PC j'ai enfin réussi !!!




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

[f-leb]

Bonjour Jack,

…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 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°) :

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

Du style :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

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 :

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 sql :

Sélectionner tout - Visualiser dans une fenêtre à part

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 () et ça devrait être bon mais n’hésite pas à revenir au besoin…

[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.

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é."

Je vous montre ma base actuelle :
Lien

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

Bonne soirée !

[jack_78]

Rebonsoir,
Finalement en trichant un peu j'ai réussi à avoir le bon résultat .
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 . Cela n'influe en aucun cas les points.

Donc voilà, on y est enfin !

Now, je vais commencer le pourcentage

[jack_78]

Voilà où j'en suis pour les pourcentages, pomé ...
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é."

[f-leb]

Aïe ! … ma faute.

Quand j’écris :

…à coder en partie directement en SQL

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

(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

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…

[jack_78]

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

Voici une image :


Et ma base :
Lien

Bonne journée