Exercice SQL, requête

Bonsoir,

Soit le modèle conceptuel de données suivant

http://img10.hostingpics.net/pics/304528model.jpg


Donner le numéro et le titre de CD du style Rap

Code:

---

1 2 3

SELECT Style_musique.Code_Style, CD_Audio.Numero_CD, CD_Audio.Titre FROM Style_musique, CD_Audio WHERE Style_musique.Libelle = "Rap" AND Style_musique.Code_Style = CD_Audio.Code_Style;

---

Donner le numéro et le titre de CD du style Rap ou bien Pop

Code:

---

1 2 3

SELECT Style_musique.Code_Style, CD_Audio.Numero_CD, CD_Audio.Titre FROM Style_musique, CD_Audio WHERE Style_musique.Code_Style = CD_Audio.Code_Style AND Style_musique.Libelle = Rap" OR Style_musique.Libelle = "Pop";

---

Donner les chansons dont la durée est supérieure à 7min

Code:

---

SELECT * FROM Chansons WHERE Duree > 7;

---

Donner les chansons du style Rap et dont la durée est supérieure à 7 mins

Code:

---

SELECT * FROM Chansons WHERE Duree > 7 AND Libelle = "Rap";

---

Donner pour chaque style, ceux qui lui sont de même famille


Je n'ai pas compris.

Merci pour votre aide

Remarque préalable :

Vos requêtes doivent porter sur des tables et non pas sur des entités-types ou associations-types du MCD : commencez par procéder à la dérivation de ce MCD en MLD.

Citation:

---

Envoyé par machua

Donner le numéro et le titre de CD du style Rap

SELECT Style_musique.Code_Style, CD_Audio.Numero_CD, CD_Audio.Titre
FROM Style_musique, CD_Audio
WHERE Style_musique.Libelle = "Rap" AND Style_musique.Code_Style = CD_Audio.Code_Style;

---

Code_Style n’est pas un attribut de CD_Audio, donc le prédicat Style_musique.Code_Style = CD_Audio.Code_Style est invalide.

Citation:

---

Envoyé par machua

Donner le numéro et le titre de CD du style Rap ou bien Pop

SELECT Style_musique.Code_Style, CD_Audio.Numero_CD, CD_Audio.Titre
FROM Style_musique, CD_Audio
WHERE Style_musique.Code_Style = CD_Audio.Code_Style AND Style_musique.Libelle = Rap" OR Style_musique.Libelle = "Pop";

---

Même punition, même motif. En outre, conformément à l’énoncé ambigu, votre WHERE est de la forme a ET b OU c, mais n’est-il pas plus raisonnable de le considérer comme étant de la forme a ET (b OU c) ? Connaissez-vous votre algèbre de Boole ?

Citation:

---

Envoyé par machua

Donner pour chaque style, ceux qui lui sont de même famille

Je n'ai pas compris.

---

Quand vous aurez généré les tables (notamment la table correspondant à l'association-type Être de même famille), vous y verrez peut-être plus clair...

Citation:

---

Envoyé par fsmrel

Remarque préalable :

Vos requêtes doivent porter sur des tables et non pas sur des entités-types ou associations-types du MCD : commencez par procéder à la dérivation de ce MCD en MLD.

---

Bonsoir fsmrel:)

Mon MLD est il correcte ?

Chansons(Code_chanson,Libellé, Version, Durée)
Style musique(Code_Style,Libellé)
CD_audio(Numéro_CD, Titre, CD_Live, CD_compilation, CD_maxi, Nbre_CDs, Date_sortie)
Être de même famille(Code_Style)

Citation:

---

Envoyé par fsmrel

Code_Style n’est pas un attribut de CD_Audio, donc le prédicat Style_musique.Code_Style = CD_Audio.Code_Style est invalide.

---

J'ai pas compri comment relier les deux tables..

Merci:)

Bonsoir machua,


Clairement, vous n’avez pas compris le rôle des associations-types. Il faudrait que vous jetiez un coup d’œil à la FAQ Merise.

Par exemple, l’association-type CONTENIR permet d’établir les relations entre les CD et les chansons :
Un CD contient au moins uns chanson et une chanson peut faire partie d’un CD. Cette règle est la traduction des cardinalités portées par les pattes connectant CD et CONTENIR d’une part, CHANSON et CONTENIR d’autre part. Quand je dis qu’un CD contient au moins uns chanson, c’est parce que la patte connectant CD et CONTENIR est porteuse d’une cardinalité 1,n. Quand je dis qu’une chanson peut faire partie d’un CD, cela veut dire qu’on peut avoir des chansons ne faisant partie d’aucun CD, et cela traduit la cardinalité 0,n portée par la patte connectant CHANSON et CONTENIR.
On dit encore en Merise qu’un CD participe au moins une fois à l’association-type CONTENIR et qu’une chanson peut ne pas participer à l’association-type CONTENIR, mais elle peut aussi y participer plusieurs fois.

Cette association-type CONTENIR fait l’objet d’une table lors du passage au MLD :

CONTENIR (NumeroCD, CodeChanson, Position)

Vous observez que la clé de la table CONTENIR est définie par la paire {NumeroCD, CodeChanson}. En effet, une association-type merisienne n’a pas d’identifiant propre, celui-ci est virtuel et peut être vu comme l’union des identifiants des entités-types qui participent à l’association-type. En tout cas, lors du passage au MLD, la table CONTENIR a bien pour clé la paire {NumeroCD, CodeChanson}. Pour sa part, l’attribut Position de la table CONTENIR est simplement hérité de l’association-type. Quant à ce que vous nommez « Quel CD ? », je considère cela comme une scorie et l’élimine, à moins que vous n’ayez des choses à dire à son sujet.

Il existe par ailleurs une association-type APPARTENIR entre CD et STYLE : je vous laisse le soin d’en faire une table.

Quant à l’association-type Être de même famille, que je renomme en APPARENTER, le principe reste valable, à ceci près que l’on met en relation des styles avec des styles.

J’attends votre nouveau MLD.

N.B. Vous subodorez que les tables dérivées des associations-types du MCD vont être impliquées dans les requêtes, et participer aux opérations de jointure.

Bonjour fsmrel,

Merci pour l'explication du rôle des associations-types. Je vous suis très reconnaissante:)

Voici mon MLD:

Chansons(CodeChanson,Libellé, Version, Durée)
Style musique(CodeStyle,Libellé)
CD_audio(NuméroCD, Titre, CDLive, CDCompilation, CDMaxi, NbreCDs, DateSortie)
Contenir(NumeroCD, CodeChanson, Position)
Appartenir(CodeStyle, NuméroCD)
Apparenter(CodeStyle)

Et mes requêtes:

1. Donner le numéro et le titre de CD du style Rap

Code:

---

1 2 3 4

SELECT CodeStyle, NumeroCD,Titre FROM StyleMusique, CDAudio, Appartenir WHERE StyleMusique.CodeStyle = Appartenir.CodeStyle AND StyleMusique.Libellé = "Rap";

---

2. Donner le numéro et le titre de CD du style Rap ou bien Pop

Code:

---

1 2 3

SELECT CodeStyle, NumeroCD,Titre FROM StyleMusique, CDAudio, Appartenir WHERE StyleMusique.CodeStyle = Appartenir.CodeStyle AND (StyleMusique.Libellé = "Rap" OR StyleMusique.Libellé = "Pop");

---

3. Donner les chansons dont la durée est supérieure à 7min

Code:

---

SELECT * FROM Chansons WHERE Duree > 7;

---

4. Donner les chansons du style Rap et dont la durée est supérieure à 7 mins

Code:

---

1 2 3

SELECT * FROM Chansons, StyleMusique, Appartenir WHERE StyleMusique.CodeStyle = Appartenir.CodeStyle AND Libellé = "Rap" AND Duree > 7;

---

5. Donner pour chaque style, ceux qui lui sont de même famille

Code:

---

1 2 3

SELECT CodeStyle FROM StyleMusique, Apparenter WHERE StyleMusique.CodeStyle = Apparenter.CodeStyle

---

2 remarques rapides sans juger de la pertinence de vos requêtes :

1) En SQL, les valeurs textuelles s'entourent d'apostrophes, pas de guillemets.

2) La syntaxe normalisée depuis 1992 pour les jointures utilise le mot clé JOIN. Il serait temps que les profs s'y mettent !
Et une requête multi-jointures est tellement plus facile à lire et à déboguer avec cette syntaxe que je ne comprends pas pourquoi on s'obstine dans l'obsolescence !

Et pas seulement les profs d'ailleurs mais aussi certains auteurs de livres. Je viens de parcourir le livre "Merise - Guide Pratique (nouvelle édition)", de Jean-Luc Baptiste aux éditions ENI et la brève présentation de SQL présente les jointures avec l'ancienne syntaxe !
J'y ai de plus relevé une erreur de cardinalités/multiplicités dans la transformation d'un MCD en diagramme de classes UML !
Deux raisons qui font que je ne recommanderai sûrement pas ce livre ! :aie:

Je suis également aller mettre en commentaire à la critique du livre mes remarques de ce matin.

Bonjour,


L‘en-tête de la table APPARENTER est incomplet :

On doit y trouver deux attributs faisant référence à l’attribut CodeStyle de la table STYLE (que vous appelez Style Musique, peu importe). Par exemple :

APPARENTER (CodeStyle1 , CodeStyle2) ;

Certaines de vos requêtes SQL seront rejetées par un analyseur syntaxique. Je n’ai pas le temps d’expliquer, mais nombreux sont ceux qui se feront un plaisir de vous éclairer.

Merci fsmrel encore une fois pour votre réponse..

J'ai besoin d'un précieux coup de main dans les requêtes:) Pouvez vous m'aidez SVP?

Citation:

---

Envoyé par machua

Donner le numéro et le titre de CD du style Rap

---

La table APPARTENIR représente le pont permettant d’établir la connexion entre les tables STYLE et CD. Ainsi, pour rapprocher ces deux tables, il faut en passer par APPARTENIR, au moyen de l’opérateur relationnel par excellence, à savoir la JOINTURE.

Si l’on détaille, dans un 1er temps on effectue une jointure entre les tables STYLE et APPARTENIR. Dans le style SQL :

Code:

---

1 2	STYLE JOIN APPARTENIR ON STYLE.CodeStyle = APPARTENIR.CodeStyle

---

Dans un 2e temps, on effectue une jointure entre les tables CD et APPARTENIR :

Code:

---

1 2	CD JOIN APPARTENIR ON CD.NumeroCD = APPARTENIR.NumeroCD

---

Ces deux jointures devant être effectuées simultanément, on écrit :

Code:

---

1 2 3 4

STYLE JOIN APPARTENIR ON STYLE.CodeStyle = APPARTENIR.CodeStyle JOIN CD ON APPARTENIR.NumeroCD = CD.NumeroCD

---

(peu importe l’ordre des jointures).

Ensuite, on vous demande d’effectuer l’opération relationnelle de RESTRICTION, qui se traduit en SQL par un WHERE :

WHERE STYLE.Libelle = 'rap'

Et enfin l’opération relationnelle de PROJECTION, qui se traduit en SQL par la liste des attributs demandés :

CD.NumeroCD, CD.Titre

Notez qu’on écrit CD.NumeroCD mais pas simplement NumeroCD, car SQL rouspète dès que le même nom d’attribut peut figurer dans plus d’une table participant à la requête.

En assemblant les morceaux, on écrit la requête sous la forme d’un SELECT :

Code:

---

1 2 3 4 5 6

SELECT CD.NumeroCD, CD.Titre FROM STYLE JOIN APPARTENIR ON STYLE.CodeStyle = APPARTENIR.CodeStyle JOIN CD ON APPARTENIR.NumeroCD = CD.NumeroCD WHERE STYLE.Libelle = 'rap' ;

---

Merci infiniment :rose: fsmrel pour votre aide et l'explication de syntaxe.

Mon MLD:

Chansons(CodeChanson,Libelle,Version, Duree)
StyleMusique(CodeStyle,Libelle)
CDAudio(NumeroCD, Titre, CDLive, CDCompilation, CDMaxi, NbreCDs, DateSortie)
Contenir(NumeroCD, CodeChanson, Position)
Appartenir(CodeStyle, NumeroCD)
Apparenter(CodeStyle1,CodeStyle2)

Mes Requêtes:

1. Donner le numéro et le titre de CD du style Rap

Code:

---

1 2 3 4 5 6

SELECT CDAudio.NumeroCD, CDAudio.Titre FROM StyleMusique JOIN Appartenir ON StyleMusique.CodeStyle = Appartenir.CodeStyle JOIN CDAudio ON APPARTENIR.NumeroCD = CDAudio.NumeroCD WHERE StyleMusique.Libelle = 'rap';

---

2. Donner le numéro et le titre de CD du style Rap ou bien pop

Code:

---

1 2 3 4 5 6 7

SELECT CDAudio.NumeroCD, CDAudio.Titre FROM StyleMusique JOIN Appartenir ON StyleMusique.CodeStyle = Appartenir.CodeStyle JOIN CDAudio ON APPARTENIR.NumeroCD = CDAudio.NumeroCD WHERE StyleMusique.Libelle = 'rap' OR StyleMusique.Libelle = 'pop';

---

3. Donner les chansons dont la durée est supérieure à 7mins

Code:

---

SELECT Libelle FROM Chansons WHERE Chansons.Duree > 420;

---

(En supposant que la durée est en secondes)

4. Donner les chansons du style Rap et dont la durée est supérieure à 7 mins

Code:

---

1 2 3 4 5 6 7

SELECT Chansons.Libelle FROM Chansons JOIN Contenir ON Chansons.CodeChanson = Contenir.CodeChanson JOIN Appartenir ON Contenir.NumeroCD = Appartenir.NumeroCD WHERE StyleMusique.Libelle = 'rap' AND Chansons.Duree > 420;

---

5. Donner pour chaque style, ceux qui lui sont de même famille

Code:

---

1 2 3 4 5

SELECT StyleMusique.CodeStyle FROM StyleMusique JOIN Apparenter ON StyleMusique.CodeStyle = Apparenter.CodeStyle WHERE StyleMusique.CodeStyle1 = Apparenter.CodeStyle2;

---

Bonne soirée et encore merci pour votre aide!

Merci pour la jolie fleur.

Et maintenant, au tableau !

Citation:

---

4. Donner les chansons du style Rap et dont la durée est supérieure à 7 mins

---

Vous écrivez : WHERE StyleMusique.Libelle = 'rap', mais il y a un problème : la table StyleMusique n’est pas mentionnée dans la clause FROM, autrement dit il manquerait comme une jointure...

Citation:

---

5. Donner pour chaque style, ceux qui lui sont de même famille

---

Il y a une difficulté supplémentaire, car la table STYLE doit figurer deux fois dans la requête.

Il faut d’abord joindre la table STYLE et la table APPARENTER :

Code:

---

1 2 3

STYLE JOIN APPARENTER ON STYLE.CodeStyle = APPARENTER.CodeStyle1

---

Puis joindre à nouveau avec STYLE pour trouver le style apparenté (CodeStyle2) :

Code:

---

1 2 3

JOIN STYLE ON APPARENTER.CodeStyle2 = STYLE.CodeStyle

---

Mais il y a un problème, car SQL trouvera deux fois le nom de table STYLE dans la clause FROM et il horreur de cela (problème d’ambiguïté).

Pour remédier à cela, SQL nous propose d’utiliser des noms de corrélation : on fait comme s’il y avait deux tables STYLE, disons S1 et S2.

La clause FROM prend alors l’allure suivante :

Code:

---

1 2 3 4 5

FROM STYLE AS S1 JOIN APPARENTER ON S1.CodeStyle = APPARENTER.CodeStyle1 JOIN STYLE AS S2 ON APPARENTER.CodeStyle2 = S2.CodeStyle

---

Et la requête devient :

Code:

---

1 2 3 4 5 6

SELECT S1.Libelle, S2.Libelle FROM STYLE AS S1 JOIN APPARENTER ON S1.CodeStyle = APPARENTER.CodeStyle1 JOIN STYLE AS S2 ON APPARENTER.CodeStyle2 = S2.CodeStyle ;

---

Et voilà.

Un grand merci à vous fsmrel:rose:, Vous venez de me "sauver ma vie".