Structure base de données & récuperation de données par jointures

**Sebi57** · 18/03/2010, 18h44

Bonsoirs cher amis !

J'ai un petit problème en SQL, j'espère que vous pourrez me filer un petit coup de main.
Je travaille actuellement sur mon projet qui récupère des donnés sur des films et les stock dans ma base 'cinema' pour ensuite les ré-afficher a de futurs visiteurs. (Site de critiques sur les films, donc il me faut au moins posséder des infos)
Le problème est que la structure de ma base de données était assez mal conçu,
Je n'utilisais qu'une seule table, où chaque ligne correspondait a un film, et je pensais mettre des tableaux php (serializés?) dans les champs.
Par exemple un tableau contenant les acteurs dans le champ 'acteurs'.

Mais un problème est apparu, comment rechercher alors un acteur par exemple dans un champ contenant un tableau de plusieurs acteurs?
C'est alors qu'on ma suggérer de créer plusieurs tables (une table acteurs pour les acteurs par exemple)
Ainsi j'ai suivis le conseil de cette personne qui m'as beaucoup aidé,
et j'ai refait entièrement la structure de ma base (j'ai pas encore optimisé les champs par contre.)

J'en suis donc venu à cette structure: http://pastebin.com/zUqHETKf

Dans chaque table, il y a une colonne 'id_ext' qui est un ID unique qui renvoi au film (de la table films par ex), tous les infos en rapport avec ce film (ex: Avatar) sont marqué par cet ID (ex: 61282). Ainsi je pensais faire une Jointure entre ces 11 tables via la colonne 'ext_id'.

Dans un premier temps, que pensez-vous de ma structure ? C'est alors que je me suis posé la question, comment récupérer toutes les informations concernant un film, avec les jointures je supposes, mais qu'en est il pour récupérer les différents acteurs, genres, etc des tables ? Car je vois pas comment par exemple récupérer toutes les infos du film Avatar en une seule requête et des jointures

Merci encore pour votre aide si précieuse !
Bonne soirée à tous

**fsmrel** · 19/03/2010, 01h12

Bonsoir,

Structure moyenne. Par exemple, un acteur ne peut pas jouer dans plus d’un film, sauf à dupliquer son nom et son prénom autant de fois que nécessaire dans la table acteurs, ce qui n’est pas sain. Tags : même punition, même motif.

Il est temps pour vous de produire un Modèle Conceptuel de Données (MCD) et de définir les relations qui unissent les différentes entités-types.

Par exemple, si un film a exactement un réalisateur, alors cela apparaîtra clairement :

Ce qui se lit :

Un réalisateur a réalisé au moins un film ;
Un film a été réalisé par exactement un réalisateur.

Ensuite, vous passerez à la dérivation du MCD en Modèle Logique de Données (MLD) :

Et enfin, lors du passage à SQL (dans le style SQL Server, je n’ai pas MySQL) :

Code SQL :

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
CREATE TABLE Realisateur
(
        RealisateurId       INT           NOT NULL
      , RealisateurNom      VARCHAR(48)   NOT NULL
      , RealisateurPrenom   VARCHAR(48)   NOT NULL
  , PRIMARY KEY  (RealisateurId)
) ;
CREATE TABLE Film
(
        FilmId              INT           NOT NULL
      , FilmDuree           INT           NOT NULL
      , RealisateurId       VARCHAR(48)   NOT NULL
  , PRIMARY KEY  (FilmId)
  , FOREIGN KEY (RealisateurId) 
          REFERENCES REALISATEUR (RealisateurId)
) ;

Quant aux INSERT, on peut les voir ainsi :

[

Code SQL :

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

1
2
3
4
5
INSERT INTO Realisateur VALUES (1, 'Sheridan', 'Jim') ;
INSERT INTO Realisateur VALUES (2, 'Cameron', 'James') ;
 
INSERT INTO Film VALUES (130950, 105, 1, ...) ;
INSERT INTO Film VALUES (61282,  161, 2, ...) ;

Vous aurez remarqué la présence de la clause FOREIGN KEY pour garantir l’intégrité des relations entre tables. A défaut, vous produirez des résultats faux.

Le principe est le même pour le reste des entités-types : définir chacune d’elle (avec ses attributs) : Acteur, Affiche, Bande_Annonce, Genre, Nationalite, Photo, Tag, Titre.

Compléter avec d’éventuelles entités-types manquantes (par exemple Distributeur).

Définir les relations entre toutes ces entités-types, à l’instar de la relation Realiser ci-dessus, sans oublier les cardinalités. Par exemple : Un acteur peut jouer dans plusieurs films et un film peut faire intervenir plusieurs acteurs. Dans le MLD, la relation Jouer entre les entités-types Acteur et Film fera l'objet d'une table Jouer, du fait des liens de plusieurs à plusieurs.

Pour sa part, je suppose que Similaire traduit une relation réflexive entre films.

Sinon pour votre requête « Tout en un », voici un exemple (toujours SQL Server) :

Code SQL :

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
SELECT  b.vo, b.vf
      , l.vo AS 'similaire(vo)', l.vf AS 'similaire(vf)'
      , d.genre
      , g.tag
      , e.nationalite
      , f.nom AS 'Nom réalisateur', f.prenom AS 'Prénom réalisateur'
      , a. sortie, a.classification, a.duree, a.production, a.distributeur, a.synopsis
      , c.Nom AS 'Nom acteur', c.Prenom AS 'Prénom acteur'
      , h.miniature, h.original
      , i.titre AS 'Bande annonce', i.hd, i.md, i.bd
      , j.miniature, j.originale
FROM    films a INNER JOIN  titres b  ON  a.ext_id = b.ext_id 
                INNER JOIN  acteurs c ON a.ext_id = c.ext_id
                INNER JOIN  genres d ON a.ext_id = d.ext_id
                INNER JOIN  nationalites e ON a.ext_id = e.ext_id
                INNER JOIN  realisateurs f ON a.ext_id = f.ext_id
                INNER JOIN  tags g ON a.ext_id = g.ext_id
                INNER JOIN  affiches h ON a.ext_id = h.ext_id
                INNER JOIN  bandes_annonces i ON a.ext_id = i.ext_id
                INNER JOIN  photos j ON a.ext_id = j.ext_id
                INNER JOIN  similaires k ON a.ext_id = k.ext_id
                INNER JOIN  titres l ON k.sim_id = l.ext_id ;

Et bon film.

**Sebi57** · 19/03/2010, 21h47

Bonsoir, merci beaucoup de votre réponse.
J'ai fait autant que j'ai pu pour comprendre, je n'ai l'habitude que de structure de base de données simple. Par contre je ne comprends pas la présence d'un champ 'RealisateurId', car si il y a plusieurs réalisateurs, comment dois-je faire (pour faire référence aux deux ou trois ou etc.?)

De plus, je ne comprends pas votre structure proposé qui n'as plus le champ 'ext_id' et la requete en jointures qui elle en a besoin.

Pouvez-vous m'apporter un peu plus d'informations ?
la Syntaxe SQL Serer et mySQL semble être la même, la base de données se crée avec succès, la requête de même mais ne retourne aucun résultats malgré la présence de données. Le problème est que j'arrive pas suivre votre exemple avec les acteurs pour les autres tables

J'espère que vous pourrez m'éclairer, merci beaucoup

**fsmrel** · 20/03/2010, 03h38

Bonsoir,

Voici la 2e séance...

Envoyé par Sebi57

je ne comprends pas la présence d'un champ 'RealisateurId', car si il y a plusieurs réalisateurs, comment dois-je faire (pour faire référence aux deux ou trois ou etc.?)

Un réalisateur possède un certain nombre de propriétés qui font l’objet d’attributs au niveau de la base de données :

Un réalisateur a un nom, un prénom, une date de naissance, etc. Ces propriétés sont dites sont dites naturelles . A sein de la base de données, on dote le réalisateur d’un attribut supplémentaire, artificiel, sans signification, invariant, que l’on appelle identifiant au niveau du modèle conceptuel et qui prendra le nom de clé primaire au stade SQL. Cet identifiant a pour objet de distinguer chaque réalisateur, d’imposer une contrainte d’unicité et d’éviter ainsi d’éventuels doublons (en supposant que deux réalisateurs puissent avoir le même nom, le même prénom, etc.) Appelons RealisateurId l’attribut correspondant à cet attribut identifiant. En principe, les identifiants sont de type INTEGER, mais pour des raisons didactiques, je retiendrai le type CHARACTER. Selon l’usage, je soulignerai tout attribut identifiant.

La structure sous forme tabulaire est donc la suivante :

Code :

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

1
2
3
4
Realisateur {RealisateurId  RealisateurNom  RealisateurPrenom  DateNaissance}
                 sheri        Sheridan           Jim            1949/02/06
                 camer        Cameron            James          1954/08/16
                 carne        Carné              Marcel         1906/08/18

Prenons le cas des films. Ce qui vaut pour les réalisateurs vaut pour les films. Ainsi, outre les attributs correspondant au propriétés naturelles, on définit un attribut identifiant FilmId. La structure tabulaire est la suivante (je ne retiens que quelques attributs et intègre l’attribut Titre, correspondant à l’attribut VF de votre table Titres) :

Code :

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

1
2
3
4
Film {FilmId  Titre          Production  Duree}
       brot   Brothers          2008      105
       hote   Hotel du Nord     1938       95
       avat   Avatar            2009      161

Passons aux relations entre les réalisateurs et les films.

1er cas de figure. Si un film a un réalisateur unique, la table Film est alors enrichie de l’attribut RealisateurId, ce qui permet d’établir une relation entre les deux tables (je mets cet attribut, en italiques pour mettre en évidence une contrainte dite référentielle, qui veut que l’attribut RealisateurId ne puisse pas prendre dans la table Film des valeurs ne figurant pas dans la table Realisateur) :

Code :

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

1
2
3
4
Film {FilmId  Titre          Production  Duree   RealisateurId}
       brot   Brothers          2008      105        sheri
       hote   Hotel du Nord     1938       95        carne
       avat   Avatar            2009      161        camer

Vous noterez le caractère associatif de ce mode de représentation, extrêmement simple mais puissant. Au niveau des opérations, l’opérateur JOIN (jointure) permet alors de connaître le nom le prénom, et tout ce qui caractérise le réalisateur des films, disons produits après 1970. Par exemple, en SQL :

Code SQL :

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

1
2
3
4
SELECT x.Titre, y.RealisateurNom, y.RealisateurPrenom
FROM   Film AS x JOIN Realisateur AS y 
                      ON  x.RealisateurId = y.RealisateurId
WHERE  x.Production > 1970 ;

(JOIN est l’opérateur de jointure naturelle, que certains appellent INNER JOIN, par opposition à une verrue appelée OUTER JOIN, mais qui ne nous intéresse pas directement ici).

2e cas de figure. Supposons qu’un film a pu être réalisé par plusieurs réalisateurs. Par exemple, que Brothers ait pu être co-réalisé par Sheridan et Cameron. Une première possibilité serait de considérer que l’attribut RealisateurId de la table Film puisse prendre plusieurs valeurs :

Code :

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

1
2
3
4
Film {FilmId  Titre          Production  Duree   RealisateurId}
       brot   Brothers          2008      105     sheri, cmar
       hote   Hotel du Nord     1938       95     carne
       avat   Avatar            2009      161     camer

Mais manque de chance, conformément à la théorie relationnelle, pour un attribut une ligne ne peut prendre qu’une valeur (on dit que l’on respecte la première forme normale (1FN ou 1NF)). Pour respecter la théorie relationnelle, la solution retenue est la suivante : on met en œuvre une table supplémentaire, qui met en relation les tables Realisateur et Film. Appelons-la Realisation. Le système devient le suivant :

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
Film {FilmId  Titre          Production  Duree}
       brot   Brothers          2008      105
       hote   Hotel du Nord     1938       95
       avat   Avatar            2009      161

Realisation {FilmId  RealisateurId}
              brot       sheri
              brot       camer
              hote       carne
              avat       camer

Realisateur {RealisateurId  RealisateurNom  RealisateurPrenom  DateNaissance}
                 sheri        Sheridan           Jim            1949/02/06
                 camer        Cameron            James          1954/08/16
                 carne        Carné              Marcel         1906/08/18

A noter que la clé primaire de la table Realisation est composée de la paire {FilmId, RealisateurId} est que les attributs qui la composent participent simultanément aux contraintes référentielles d’usage.

La requête qui permet de connaître les réalisateurs des films réalisés après 1970 devient :

Code SQL :

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

1
2
3
4
5
6
SELECT x.Titre, z.RealisateurNom, z.RealisateurPrenom
FROM   Film AS x JOIN Realisation AS y 
                      ON x.FilmId = y.FilmId
                 JOIN Realisateur AS z
                      ON y.RealisateurId = z.RealisateurId
WHERE  x.Production > 1970 ;

Incidemment, vous noterez que les attributs artificiels FilmId et RealisateurId sont utilisés pour les opérations de jointure, mais n'ont aucunement besoin de figurer dans le résultat.

Ai-je répondu à votre question ?

Envoyé par Sebi57

je ne comprends pas votre structure proposé qui n'as plus le champ 'ext_id' et la requete en jointures qui elle en a besoin

Dans le système (on ne peut plus classique) que je propose, l’attribut ext_id fait, disons, double emploi avec l’attribut id de votre table Films et que j’ai renommé FilmId.

Envoyé par Sebi57

la base de données se crée avec succès, la requête de même mais ne retourne aucun résultats malgré la présence de données.

Ceci est la conséquence de la présence de la table Similaires, car j’ai procédé comme si l’attribut sim_id contenait des valeurs qui fussent aussi des valeurs de l’attribut ext_id de la table Films. Si on complète la table Films avec ces valeurs, ça doit donner les résultats escomptés. En attendant, vous pouvez soumettre la requête débarassée de la table Similaires :

Code SQL :

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
SELECT  b.vo, b.vf
      , d.genre
      , g.tag
      , e.nationalite
      , f.nom AS 'Nom réalisateur', f.prenom AS 'Prénom réalisateur'
      , a. sortie, a.classification, a.duree, a.production, a.distributeur, a.synopsis
      , c.Nom AS 'Nom acteur', c.Prenom AS 'Prénom acteur'
      , h.miniature, h.original
      , i.titre AS 'Bande annonce', i.hd, i.md, i.bd
      , j.miniature, j.originale
FROM    films a INNER JOIN  titres b  ON  a.ext_id = b.ext_id 
                INNER JOIN  acteurs c ON a.ext_id = c.ext_id
                INNER JOIN  genres d ON a.ext_id = d.ext_id
                INNER JOIN  nationalites e ON a.ext_id = e.ext_id
                INNER JOIN  realisateurs f ON a.ext_id = f.ext_id
                INNER JOIN  tags g ON a.ext_id = g.ext_id
                INNER JOIN  affiches h ON a.ext_id = h.ext_id
                INNER JOIN  bandes_annonces i ON a.ext_id = i.ext_id
                INNER JOIN  photos j ON a.ext_id = j.ext_id ;

Rendez-vous à la prochaine séance...

**Sebi57** · 20/03/2010, 20h08

Bonsoir, un grand merci pour vos réponses vraiment très professionnelles !
J'ai vu que les FOREIGN KEY ne marchaient pas sur le moteur de stockage par défaut de MySQL (à savoir, MyISAM) le moteur ne les gérait pas, je suis alors passé à un autre moteur de stockage, InnoDB.

C'est alors que j'ai mis en pratique avec votre exemple pour voir si tout marchait bien:

(Cliquez sur l'image pour l'agrandir)

Et j'ai effectué la requête:

Code :

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

1
2
3
4
SELECT x.Titre, y.RealisateurNom, y.RealisateurPrenom
FROM   Film AS x JOIN Realisateur AS y 
                      ON  x.RealisateurId = y.RealisateurId
WHERE  x.Production > 1970 ;

Tout passe avec succès.

Maintenant je m'attaque à la base entière, j'ai complètement refait la structure comme vous me l'aviez suggéré, qu'en pensez vous ?

(Cliquez sur l'image pour l'agrandir)

J'espère que c'est comme ça qu'il faut faire

Je vais essayer de récupérer les données de la fiche d'un film avec les jointures s'y j'y parviens.
Merci de votre aide
Amicalement.

**al1_24** · 20/03/2010, 20h17

Une vidéo, une affiche ou une photo pourrait être attachée à plusieurs films ?

**Sebi57** · 20/03/2010, 20h31

Envoyé par al1_24

Une vidéo, une affiche ou une photo pourrait être attachée à plusieurs films ?

Non, chaque photo, vidéo ou affiche sera exclusivement destiné à un film, mais un film peut regrouper plusieurs vidéos, affiches, photos.

PS: Vidéos veut dire / signifie Bande-Annonces, c'était surtout pour le coté esthétique du nom de la table dans la base.
PS²: J'ai d'ailleurs fait des petites erreurs de champ dans les tables situées aux extrémitées du cercle, je vais modifier la structure et mettre à jour l'image.

**Sebi57** · 20/03/2010, 22h02

Voilà j'ai modifié la structure de ma base de données qui est dorénavant:

(Cliquez sur l'image pour l'agrandir)

J'ai supprimé une colonne des tables les plus à l'extérieur du cercle, elle n'avait pas d'utilités réelles.

**Sebi57** · 20/03/2010, 23h35

Je viens d'essayer de récupérer toutes les données concernant un film grâce aux jointures mais sans grand succès, comment dois je faire étant données qu'un film retournera plusieurs acteurs, photos, vidéos ?

Merci de votre aide, si vous avez besoin de la base de données actuelle de test contenant les données d'exemple, elle est disponible ici : http://pastebin.com/KQ1PQNNQ

Merci encore, Amicalement.

**al1_24** · 21/03/2010, 00h01

Envoyé par Sebi57

Non, chaque photo, vidéo ou affiche sera exclusivement destiné à un film, mais un film peut regrouper plusieurs vidéos, affiches, photos.

Il y a donc une relation de 1 à n entre films et affiches, videos ou photos et non n à m comme le montre ton modèle.
Les tables _affiches, _photos et _videos sont inutiles et leurs informations doivent être rapportées aux tables affiches, photos et videos.

**Sebi57** · 21/03/2010, 00h12

Envoyé par al1_24

Il y a donc une relation de 1 à n entre films et affiches, videos ou photos et non n à m comme le montre ton modèle.
Les tables _affiches, _photos et _videos sont inutiles et leurs informations doivent être rapportées aux tables affiches, photos et videos.

Oui c'est exact ! J'avais pas vu, merci ! Ça va alléger la base de 3 tables

**fsmrel** · 22/03/2010, 18h44

Bonjour,

Attribut Allocine (tables films, similaires, etc.) : Est-ce votre application qui détermine les valeurs de cet attribut ou bien récupérez-vous ces valeurs chez Allociné (ou autre) ?

**Sebi57** · 22/03/2010, 19h41

Envoyé par fsmrel

Bonjour,

Attribut Allocine (tables films, similaires, etc.) : Est-ce votre application qui détermine les valeurs de cet attribut ou bien récupérez-vous ces valeurs chez Allociné (ou autre) ?

Pour le moment les informations du site sont prisent sur allociné pour un but de développement, par la suite elle seront rédigées à la main via une interface d'administration. J'ai plusieurs contact avec des distributeur qui me font parvenir les résumés de leurs films et autres infos. Comme pour le moment je ne peux pas traiter tout ce que je reçois par manque de temps et surtout de personnes pour m'aider. Je me base sur les informations d'allociné pour récupérer les informations valables seulement pour le développement du site, dans les phases de test et de mise en service au public, nous générerons nos propres informations issues des distributeurs. Comme le disent certaines personnes il serait trop facile de se servir vulgairement sur le site d'allociné. Qui de plus est strictement interdit. Enfin voilà j'espère que je répond à la question ? Cela me permet de tester plusieurs films dans mon système en évitant de tout rentrer manuellement, je gagne donc beaucoup de temps pour ainsi développer mon site. Mais bon après le début du site, certains distributeurs m'ont affirmés me donner accès a leur base de données concentrant les informations de leur films.

Bonne soirée.

**fsmrel** · 23/03/2010, 01h13

Bonsoir,

Merci de votre réponse. Ma question a pu vous paraître brutale, j’en suis désolé, et je me serais contenté d’une réponse à la al1_24, tenant en une ou deux lignes !

Ce que je souhaitais, c’était seulement m’assurer que vous n’eussiez pas de problème de mise à jour de votre base de données en cas de changement de valeur pour l’attribut Allocine.

Actuellement, pour le film Avatar, la valeur de cet attribut est égale à 61282. Quelles sont les conséquences sur la base de données si, pour une raison ou autre, cette valeur est à remplacer par 3141593 ? (vous ne pouvez pas savoir ce qu’il y a dans le crâne de votre partenaire...) Vous serez obligé de mettre à jour toutes les tables comportant un attribut Allocine. Certes, d’après ce que je comprends, la structure que vous mettez en place est provisoire, mais il y a du provisoire qui parfois dure et il est bon de partir sur des bases et principes sains.

Selon ces principes, il est très fortement recommandé de retenir pour les clés primaires des attributs pour lesquels vous aurez la certitude qu’ils seront invariants et la meilleure façon de vous en assurer est que ce soit vous et vous seul qui fournissiez ces valeurs prises par ces clés.

Prenons le cas de votre table Films. Vous avez utilisé l’instruction suivante pour la décrire :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE films (
  id int(11) NOT NULL AUTO_INCREMENT,
  allocine int(11) NOT NULL,
  titre_vo varchar(250) NOT NULL,
  titre_vf varchar(250) NOT NULL,
  sortie date NOT NULL,
  avertissement varchar(250) NOT NULL,
  duree time NOT NULL,
  production varchar(250) NOT NULL,
  distributeur varchar(250) NOT NULL,
  synopsis varchar(250) NOT NULL,
  PRIMARY KEY (allocine),
  UNIQUE KEY id (id)) ;

Dans cette table, c’est justement vous qui définissez les valeurs prises par l’attribut id (qu’il serait préférable de renommer en FilmId même si ça n’est pas une nécessité), puisque vous avez décidé qu’il serait UNIQUE et que vous avez codé :

id int(11) NOT NULL AUTO_INCREMENT
...

Actuellement, cet attribut joue le rôle de clé alternative : il faut permuter les rôles (pour plus de clarté, je renomme id en FilmId) :

Code SQL :

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

1
2
3
4
5
6
7
CREATE TABLE films (
  FilmId int(11) NOT NULL AUTO_INCREMENT,
  Allocine int(11) NOT NULL,
  ...
  PRIMARY KEY (FilmId),
  UNIQUE (Allocine)
  ... ;

Désormais, seule la table Films comportera l’attribut Allocine qui sera un point d’entrée dans la base de données (si tant est qu’il soit utile...), les relations entre tables se faisant désormais uniquement par le canal de l’attribut FilmId.

En l'occurrence, prenons par exemple le cas de la table _acteurs :

Code SQL :

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

1
2
3
4
5
6
7
CREATE TABLE _acteurs (
  id int(11) NOT NULL AUTO_INCREMENT,
  allocine int(11) NOT NULL,
  acteur int(11) NOT NULL,
  PRIMARY KEY (allocine, acteur),
  UNIQUE KEY id (id),
  KEY realisateur (acteur)) ;

Elle doit être ainsi transformée (l’attribut id est parfaitement inutile, je le supprime) :

Code SQL :

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

1
2
3
4
5
CREATE TABLE _acteurs (
  FilmId int(11) NOT NULL,
  acteur int(11) NOT NULL,
  PRIMARY KEY (FilmId, acteur),
  KEY realisateur (acteur)) ;

N.B. le terme 'realisateur' est sans doute inapproprié dans le cas de cette table.

Consequence sur les clés étrangères (elles ne feront plus référence à l’attribut Allocine) :

Code SQL :

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

1
2
3
ALTER TABLE _acteurs
  ADD CONSTRAINT _acteurs_ibfk_2 FOREIGN KEY (acteur) REFERENCES acteurs (id),
  ADD CONSTRAINT _acteurs_ibfk_1 FOREIGN KEY (FilmId) REFERENCES films (FilmId);

Et n’oubliez pas de mettre en œuvre une table pour les distributeurs.

Courage, vous êtes sur la bonne voie.

**Sebi57** · 27/03/2010, 18h51

Merci de vos conseils, j'ai modifié ma base de données comme vous me l'aviez suggéré. Je suis maintenant confronté à un autre problèmes, je récupère les informations d'un film comme vous me l'avez expliqué plus haut via des jointures.

J'effectue par exemple cette requête (de démonstration sans alias)

Code SQL :

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
SELECT films.titre_vf,
       films.titre_vo,
       films.allocine,
       films.sortie,
       films.avertissement,
       films.duree,
       films.production,
       films.distributeur,
       films.synopsis,
       realisateurs.nom,
       realisateurs.prenom,
       acteurs.nom,
       acteurs.prenom
FROM   films
       JOIN _realisateurs
         ON films.id = _realisateurs.id
       JOIN realisateurs
         ON _realisateurs.id = realisateurs.id
       JOIN _acteurs
         ON films.id = _acteurs.id
       JOIN acteurs
         ON _realisateurs.id = acteurs.id
WHERE  films.id = 1;

Le problème est que comme vous vous en doutez, un film a plusieurs acteurs (et dans certains cas plusieurs réalisateurs), et cette requête ne récupère que le premier acteur parmi les 3 possibles.
La question est comment puis-je procéder pour les récupérer tous les trois?

J'ai étudié la question et je me suis dis:
- Soit on peu récupérer tout les informations d'un coup mais là je ne vois aps comment faire. (Le serveur peut t'il renvoyer un tableau des différents acteurs?)
- Soit effectuer ceci en plusieurs requêtes, une pour les données du film unique, et une pour les acteurs (puis les vidéos, les réalisateurs, etc)

Auriez-vous une idée pour me mettre sur le bon chemin?
Merci de votre aide que vous m'avez apporté tout au long de notre discussion

**fsmrel** · 29/03/2010, 10h31

Bonjour Seb,

Quand je vois ça, j’ai des doutes :

Code SQL :

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

1
2
3
films
       JOIN _realisateurs
         ON films.id = _realisateurs.id

Pourriez-vous joindre le script de création des tables ?

Merci,

François

**Sebi57** · 29/03/2010, 21h06

Bonsoir François,

Voici les instructions SQL pour la réalisation de la base de donnée (toutes les tables, avec relations) avec données d'exemples.

http://pastebin.com/p3WfpR63

Merci pour l'attention que tu portes à mon problème.

Seb

**fsmrel** · 30/03/2010, 02h48

Bonsoir Seb,

En ce qui concerne votre requête, il y a quelques erreurs de casting...

Quand vous écrivez :

films JOIN _realisateurs ON films.id = _realisateurs.id

vous comparez l’attribut id de la table films (en l’occurrence l'identifiant d'un film) avec l’identifiant de la table _realisateurs (qui du reste ne sert strictement à rien), c'est-à-dire des choux et des navets. Pour comparer des attributs comparables, il faut écrire :

films JOIN _realisateurs ON films.id = _realisateurs.film

Ceci, en vertu de la contrainte d’intégrité référentielle que vous avez définie pour la table _realisateurs :

FOREIGN KEY (film) REFERENCES films (id)

Et qui dit bien que ça n’est pas l’attribut id mais l’attribut film de la table _realisateurs qui est en relation avec l’attribut id de la table films.

De la même façon, vous avez défini la contrainte d’intégrité référentielle suivante mettant en relation la table _realisateurs avec la table realisateurs :

FOREIGN KEY (realisateur) REFERENCES realisateurs (id)

en conséquence de quoi, pour les mêmes raisons :

ON _realisateurs.id = realisateurs.id

est à remplacer par :

ON _realisateurs.realisateur = realisateurs.id

Ce sont évidemment des considérations identiques qui valent pour retrouver les acteurs des films.

La partie jointure de votre requête doit être aménagée ainsi :

Code :

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

1
2
3
4
5
6
7
8
9
FROM   films
       JOIN _realisateurs
         ON films.id = _realisateurs.film
       JOIN realisateurs
         ON _realisateurs. realisateur = realisateurs.id
       JOIN _acteurs
         ON films.id = _acteurs.film
       JOIN acteurs
         ON _acteurs.acteur = acteurs.id

Quelques observations complémentaires :

Table films : permuter les PK et UNIQUE (c’est id qui est clé primaire et non pas allocine dont vous n’avez pas la maîtrise).
L’attribut id que vous avez défini pour toutes vos tables de relation (_Acteur, etc.) ne sert strictement à rien et doit disparaître de ces tables.
Toujours pour toutes vos tables de relation (_Acteur, etc.) n’effectuez les INSERT qu’après déclaration des clés étrangères.

**CinePhil** · 30/03/2010, 08h53

J'arrive un peu tard mais je viens mettre mon grain de sel comme je suis en train de faire une BDD perso similaire pour répertorier mon abondante documentation cinématographique.

Dans ce que vous proposez, les réalisateurs et les acteurs sont séparés. Comme un acteur peut aussi être réalisateur et pas forcément pour le même film, vous aurez deux fois ces personnes dans votre BDD donc redondance d'information.

La solution que j'ai adoptée consiste à avoir une entité Personne, une entité Fonction et une association ternaire entre Film, Personne, Fonction :
Personne -0,n----Agir----0,n- Film
Fonction -0,n--------|

Ainsi Woody Allen peut être à la fois acteur et réalisateur de la plupart de ses films en ne figurant qu'une seule fois dans la table des personnes.

Quant aux affiches, vidéos, photos... ce sont tous des documents (entité document) relatifs à un film (ou à une personne, un festival, un genre... le tout étant chez moi pluriel et plus divers : articles, dossiers de presse, revues, livres, films-annonce...).

Structure base de données & récuperation de données par jointures

Langage SQL

Discussions similaires

Partager

Partager