Discussion :

[prgasp77]

Bonjour,
je révise les bases de données, notamment ce soir le langage SQL. Il m'est demandé d'extraire en une requête tous les sculpteurs qui ont au moins une œuvre au Louvre et aucune à Orsay.

Voici la structure de la base considérée :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
musee (
  nomM varchar(32) collate latin1_general_ci NOT NULL,
  ville varchar(64) collate latin1_general_ci NOT NULL,
  pays varchar(32) collate latin1_general_ci NOT NULL,
  N_Obj int(11) NOT NULL,
  Theme varchar(128) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (nomM,N_Obj),
  KEY N_Obj (N_Obj)
 
sculpteurs (
  N_Auteur int(10) unsigned zerofill NOT NULL auto_increment,
  nom varchar(32) collate latin1_general_ci NOT NULL,
  prenom varchar(32) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (N_Auteur)
)
 
sculptures (
  N_Obj int(10) unsigned zerofill NOT NULL auto_increment,
  titre varchar(32) collate latin1_general_ci NOT NULL,
  description varchar(128) collate latin1_general_ci NOT NULL,
  datecreation date NOT NULL,
  prix int(11) NOT NULL,
  valeurachat int(11) NOT NULL,
  dateachat date NOT NULL,
  N_Auteur int(10) unsigned zerofill NOT NULL,
  PRIMARY KEY  (N_Obj),
  KEY titre (titre,N_Auteur)

Faut-il utiliser un outil particulier autre que les select, from, where, distinct et group by ? Si oui pouvez-vous me donner cette fameuse requête ?

D'avance merci.




Edit :
J'ai bien une solution, mais je la trouve très peu élégante (j'ai fini par me souvenir de la clause having

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT nom, prenom
FROM sculpteurs AS a, (
   SELECT DISTINCT N_Auteur, nomM
   FROM sculptures AS s, (
      SELECT N_Obj, nomM
      FROM musee
      WHERE nomM = "Louvre"
         OR nomM = "Orsay"
      ) AS a
   WHERE s.N_Obj = a.N_Obj
   GROUP BY N_Auteur
   HAVING nomM <> "Orsay"
) AS s
WHERE a.N_Auteur = s.N_Auteur

[tatayo]

Bonjour,
Perso j'aurai fait ainsi:

Code :

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

1
2
3
4
5
6
7
8
9
10
 
select distinct sculpteur.nom
from sculpteur
inner join scultpure on sculpture.n_auteur = sculpteur.n_auteur
inner join musee on musee.n_objet sculpture.n_objet
where musee.nomN = 'LOUVRE'
and not exists(select * from from sculpteur
inner join scultpure on sculpture.n_auteur = sculpteur.n_auteur
inner join musee on musee.n_objet sculpture.n_objet
where musee.nomN = 'ORSAY')

Tu peux aussi le faire avec un Except au lieu du not exists.

Tatayo

[CinePhil]

Il me semble qu'il y a une erreur de conception...

'N_Obj' est la clé primaire de la table 'sculptures'. On retrouve cette colonne 'N_Obj' dans la table 'musee' associée à nomM pour en former la clé primaire.

Alors ou bien chaque musée ne contient qu'une seule oeuvre, ce qui me semble peu, ou bien les tables musee et sculptures sont redondantes puisqu'on va retrouver autant d'objets dans musee qu'il y en a dans sculptures !

Revenons en arrière vers la conception :
Règle de gestion :
Une sculpture est exposée dans un seul musée et un musée expose plusieurs sculptures.

MCD :
Musee -0,n----Exposer----1,1- Sculpture

La clé primaire du musée devient clé étrangère dans la table sculptures et pas l'inverse !

Je vous conseille aussi de ne pas utiliser le nom du musée comme clé primaire mais un identifiant de type entier auto-incrémenté, comme vous l'avez fait pour sculptures.

[hazamor]

La remarque de CinePhil est correcte, càd "Id_musee" l'identifiant de musée est un clé étrangère dans sculpture et cen'est pas l'inverse.
Supposons que vous avez corrigé cette faut de conception, je vous propose:

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
 
select sculpteur.nom
from sculpteur S
where not exists(select 1 from  sculpture SE 
                     where SE.n_auteur = S.n_auteur
                     and musee.Id_musee =SE.Id_musee
                     and musee.nom = 'ORSAY')
and exists(select 1 from  sculpture SE 
                     where SE.n_auteur = S.n_auteur
                     and musee.Id_musee =SE.Id_musee
                     and musee.nom = 'LOUVRE')

[SQLpro]

SELECT nom, prenom
FROM sculpteurs AS a, (
SELECT DISTINCT N_Auteur, nomM
FROM sculptures AS s, (
SELECT N_Obj, nomM
FROM musee
WHERE nomM = "Louvre"
OR nomM = "Orsay"
) AS a
WHERE s.N_Obj = a.N_Obj
GROUP BY N_Auteur
HAVING nomM <> "Orsay"
) AS s
WHERE a.N_Auteur = s.N_Auteur

Dans votre requête l'utilisation des clauses GROUP BY et HAVING est stupide car vous n'avez pas de calculs d'agrégats. En aucun cas ces clauses ne doivent être utilisé sans les fonctions MAX, MIN, AVG, COUNT...

La réponse étant :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
 
SELECT S.nom
FROM   sculpteur S
       INNER JOIN sculpture SC
             ON S.N_auteur = SC.N_auteur
       INNER JOIN musee M 
             ON SC.N_obj = M.N_obj
WHERE  M.NomM = 'Louvre'
AND    NOT EXISTS(SELECT *
                  FROM   sculpture SC2
                         INNER JOIN musee M2
                               ON SC2.N_obj = M2.N_obj
                  WHERE  M2.nomM = 'ORSAY'
                  AND    SC2.N_auteur = SC.N_auteur)

A +

[prgasp77]

Bonjour.
Merci de vous pencher un peu sur mon problème. En effet, la base est redondante ... mais elle est donnée telle quelle dans l'énoncé. L'exercice suivant est, si je me souviens bien, de l'optimiser.

Ensuite, concernant votre proposition, ne demande-t-elle pas plus de travail au SGDB qui doit se taper deux fois une double inner join sans restriction avant ?

Merci encore.

[CinePhil]

Ensuite, concernant votre proposition, ne demande-t-elle pas plus de travail au SGDB qui doit se taper deux fois une double inner join sans restriction avant ?

Il ne faut pas prendre l'ordre d'écriture de la requête pour l'ordre que suivra le SGBD pour l'exécuter.
L'optimiseur du SGBD se débrouille avec la structure de la requête pour trouver l'ordre adéquat pour faire les choses le plus rapidement possible.

Dans le cas présent il est probable qu'il commence par appliquer la restriction

Code :

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

WHERE  M.NomM = 'Louvre'

Afin de limiter le nombre de lignes dans la jointure.

[prgasp77]

En gros j'ai un chat pitre entier sur l'optimisation des requetes ... qui ne sert à rien \o/ cours de merde !

Bon, j'ai passé mon exam aujourd'hui, ça s'est plutôt bien déroulé (il était facile). Au programme, normalisation et arbres d'index ... pas de SQL.

Merci à vous pour vos réponses.

[pacmann]

Salut !

Non, les cours ne sont pas (complètement) inutiles.
Malgré les progrès constants des optimiseurs, connaître les principes de base permet aussi de comprendre plus facilement l'optimiseur. Car celui-ci peut prendre les mauvaises décisions.
Par ailleurs, entre plusieurs écritures possibles pour une requête, même si le résultat est le même, le plan d'exécution choisi ne sera pas forcément le même : la manière dont on écrit les requêtes à (pour l'instant) toujours encore un impact sur les temps d'exécution !

[prgasp77]

Ha ?
Et est-il possible de comparer deux version d'une même requête (outre le temps d'exécution qui est assez aléatoire) ?

Merci.

[al1_24]

En regardant les plans d'exécution (EXPLAIN PLAN)

[SQLpro]

Mieux que les plans de requête le plus simple est de demander les statistiques de consommation des ressources. Le premier niveau consiste à comparer globalement les IO de cheque requête. Exemple :

Code :

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

SET STATISTICS IO ON

Sur SQL Server.

Ensuite à nombre d'IO comparable, il faut voir la consommation CPU de la requête.

A +

[prgasp77]

#1193 - Unknown system variable 'STATISTICS'

Je crois que MySql ne l'autorise pas ... et je n'ai pas dee serveur sql sous la main .

Merci tout de même.

[SQLpro]

MySQL est très pauvre en outils de tuning !

A +