4. Les jointures - Partie 2

Retour au menu

Base de données

La base de données utilisées pour les exemples ci-dessous celle d'une base de données pour les films.

La base de données se nomme bd_films.sqlite. Elle se retrouve sur LEA.

Voici le diagramme.

Voici la description des tables.

TablesDescription
PaysContient la liste des pays dans le monde.
ProductionsContient l'information sur les compagnies de production des films.
GenresContient la liste des genres cinématographiques des films.
FilmsContient l'information des films.
ActeursContient l'information des acteurs.
FilmsActeursContient l'information de la distribution d'un film.

Insertion

Voici des insertions. Il faut exécuter au début le PRAGMA foreign_keys = ON; pour que la base de données valide les contraintes.

Est-ce que l'ordre d'insertion est important entre les tables ? Bien sûr, ceci a été présenté au dernier cours.

Le premier niveau d'insertion est pour les tables Productions, Genres et Pays, car ces tables sont uniquement parents.

Le deuxième niveau d'insertion est pour les tables Films et Acteurs, car ces tables sont les enfants des tables du premier niveau.

Finalement, le dernier niveau est pour la table FilmsActeurs, car elle a comme table parent celles du 2e niveau.

Exécutez ces commandes d'insertion pour avoir un minimum de données.

        

            Copié
            
        

        --Activation des contraintes de clé étrangère
PRAGMA foreign_keys = ON;

--Niveau 1
INSERT INTO Productions(ProductionId, NomCompagnie, AnneeFondation)
VALUES
    (1, 'Disney', 1923),
    (2, 'Sony Pictures', 1987),
    (3, 'Paramount Picture', 1912);

INSERT INTO Genres(GenreId, Nom, Description)
VALUES
    (1, 'Action', 'Ces films comptent de nombreuses scènes de bagarres, de poursuites, de cascades diverses.'),
    (2, 'Comédie', 'Ces films sont destinés à faire rire le public. Le scénario multiplie les gags, les quiproquos'),
    (3, 'Fantastique', 'Ces films comportent des faits extraordinaires qui ne peuvent pas arriver dans la réalité.');

INSERT INTO Pays(Code, Nom)
VALUES
    ('CA', 'Canada'),
    ('FR', 'France'),
    ('AU', 'Australie'),
    ('IL', 'Israël'),
    ('US', 'États-Unis');

--Niveau 2
INSERT INTO Films(FilmId, Titre, Description, ProductionId, GenreId, DateSortie, UrlIMDB)
VALUES
    (1, 'Thor: Love and Thunder', 'Thor demande l''aide de Valkyrie, Korg et de l''ex-petite amie Jane Foster pour combattre Gorr le dieu boucher, qui a l''intention de les faire disparaître.', 1, 3, '2022-06-23', 'https://www.imdb.com/title/tt10648342/?ref_=nv_sr_srsg_0'),
    (2, 'Thor', 'Thor, Dieu tout puissant mais arrogant, est exilé d''Asgard et envoyé sur à Midgard sur Terre, où il devient rapidement l''un de leurs meilleurs guerriers.', 1, 3, '2011-04-17', 'https://www.imdb.com/title/tt0800369/?ref_=fn_al_tt_1'),
    (3, 'Rush', 'L''impitoyable rivalité des années 1970 entre James Hunt et Niki Lauda, deux pilotes de Formule 1.', 3, 1, '2013-09-02', 'https://www.imdb.com/title/tt1979320/?ref_=nm_flmg_act_32'),
    (4, 'Le guide de la famille parfaite', 'Un couple Québécois fait face aux pièges, à la pression et aux attentes d''élever des enfants dans une société obsédée par le succès et l''image sur les médias sociaux.', 2, 2, '2021-05-19', 'https://www.imdb.com/title/tt11991786/?ref_=nm_flmg_act_4');

INSERT INTO Acteurs(ActeurId, Nom, Prenom, PaysCode, Biographie, UrlIMDB)
VALUES
    (1, 'Hemsworth', 'Chris', 'AU', 'Acteur australien...', 'https://www.imdb.com/name/nm1165110/?ref_=tt_cl_t_2'),
    (2, 'Portman', 'Nathalie', 'IL', 'Actrice née en 1981, connue pour ...', 'https://www.imdb.com/name/nm0000204/?ref_=tt_cl_t_3'),
    (3, 'Wilde', 'Olivia', 'US', 'Actrice née en 1984, connue pour son rôle dans Dr. House...', 'https://www.imdb.com/name/nm1312575/?ref_=tt_cl_t_3'),
    (4, 'Morissette', 'Louis', 'CA', 'Acteur québécois...', 'https://www.imdb.com/name/nm1747867/?ref_=nv_sr_srsg_0'),
    (5, 'Boulanger', 'Pierre-Luc', 'CA', 'Acteur québécois ne sachant pas ce qu''il veut', 'Aucune');

Table pivot - Relation plusieurs à plusieurs

La table FilmsActeurs ne représente pas un concept propre. Elle consiste à une table pivot pour décrire la relation entre 2 autres concepts (table).

Dans ce cas-ci, c'est entre la table Films et la table Acteurs.

Un acteur peut jouer dans un ou plusieurs films.

Un film a un ou plusieurs acteurs sa distribution.

Il n'est pas possible de mettre une contrainte de clés étrangères directement dans les tables Films et Acteurs. Si la clé étrangère était dans la table Films, la table parent serait Acteurs. Donc, un film peut avoir uniquement un acteur en fonction de cette relation. À l'inverse, si la clé étrangère était dans la table Acteurs, la table parent serait Films. Un acteur pourrait jouer uniquement dans un film. Si les 2 tables avaient une clé étrangère de l'autre table, un acteur serait dans un film, mais ce même film n'aurait pas nécessairement le même acteur.

La table pivot permet de représenter la relation entre les 2 tables et de permettre un nombre non défini de relations.

La table FilmsActeurs a 3 champs.

  • FilmId
  • ActeurId
  • NomPersonnage

Le champ FilmId est la clé étrangère de la table Films.

Le champ ActeurId est la clé étrangère de la table Acteurs

Le champ NomPersonnage consiste à indiquer le nom du personnage d'un acteur précis qui joue dans un film spécifique.

Les champs FilmId et ActeurId sont des clés primaires. Il n'est donc pas possible d'avoir le même acteur dans le même film plus d'une fois.

Insertion

L'acteur #1 "Chris Hemsworth" a interprété le personnage de "Thor" dans les films #1 "Thor: Love and Thunder" et #2 "Thor".

L'acteur #1 "Chris Hemsworth" a interprété le personnage de "James Hunt" dans le film #3 "Rush".

L'actrice #2 "Nathalie Portman" a interprété le personnage de "Jane Foster" dans les films #1 "Thor: Love and Thunder" et #2 "Thor".

L'actrice #3 "Olivia Wilde" a interprété le personnage de "Suzy Miller" dans le film #3 "Rush".

L'acteur #4 "Louis Morisette" a interprété le personnage de "Martin Dubois" dans le film #4 "Le guide de la famille parfaite".

L'acteur #5 "Pierre-Luc Boulanger" attend toujours son premier rôle.

Il faut ajouter dans la table FilmsActeurs les insertions suivantes.

        

            Copié
            
        

        INSERT INTO FilmsActeurs(ActeurId, FilmId, NomPersonnage)
VALUES
    (1, 1, 'Thor'), --Chris Hemsworth, Thor : Love and Thunder
    (1, 2, 'Thor'), --Chris Hemsworth, Thor
    (1, 3, 'James Hunt'), --Chris Hemsworth, Rush
    (2, 1, 'Jane Foster'), --Nathalie Portman, Thor : Love and Thunder
    (2, 2, 'Jane Foster'), --Nathalie Portman, Thor
    (3, 3, 'Suzy Miller'), --Olivia Wilde, Rush
    (4, 4, 'Martin Dubois'); --Louis Morissette, Le guide de la famille parfaite

Nomenclature

Généralement, une table pivot a comme nom la fusion des noms des autres tables utilisées dans sa relation. Dans notre exemple, FilmsActeurs explique la relation entre la table Films et la table Acteurs.

Parfois, il est possible d'avoir un nom spécifique pour une table pivot. Dans notre exemple, la table FilmsActeurs aurait pu se nommer Distributions.

Quelques projections

Affichez la liste de tous les personnages avec l'information du film(Titre), le nom du personnage, de l'acteur (prénom, nom), du pays (nom), du genre (nom, description) et de la compagnie de production (NomCompagnie). Triez la liste par le titre du film, par le prénom de l'acteur, et le nom de l'acteur.

        

            Copié
            
        

        SELECT Films.Titre, FilmsActeurs.NomPersonnage, Acteurs.Prenom AS PrenomActeur, Acteurs.Nom AS NomActeur,
       Pays.Nom AS NomPays, Genres.Nom AS NomGenre, Genres.Description AS DescriptionGenre, Productions.NomCompagnie
FROM FilmsActeurs
INNER JOIN Acteurs ON Acteurs.ActeurId = FilmsActeurs.ActeurId
INNER JOIN Films ON Films.FilmId = FilmsActeurs.FilmId
INNER JOIN Pays ON Acteurs.PaysCode = Pays.Code
INNER JOIN Genres on Films.GenreId = Genres.GenreId
INNER JOIN Productions on Films.ProductionId = Productions.ProductionId
ORDER BY Films.Titre, Acteurs.Prenom, Acteurs.Nom;

Affichez la liste des tous les acteurs (prénom et nom) qui n'ont aucun film.

        

            Copié
            
        

        SELECT Acteurs.Prenom, Acteurs.Nom
FROM Acteurs
LEFT OUTER JOIN FilmsActeurs ON Acteurs.ActeurId = FilmsActeurs.ActeurId
WHERE
    FilmsActeurs.FilmId IS NULL;

Affichez la liste des tous les acteurs (concaténation du prénom et du nom) qui n'ont aucun film et avec le nom de leur pays.

        

            Copié
            
        

        SELECT Acteurs.Prenom || ' ' || Acteurs.Nom AS NomCompletActeur, Pays.Nom AS NomPays
FROM Acteurs
INNER JOIN Pays ON Acteurs.PaysCode = Pays.Code
LEFT OUTER JOIN FilmsActeurs ON Acteurs.ActeurId = FilmsActeurs.ActeurId
WHERE
    FilmsActeurs.FilmId IS NULL;

Affichez le titre de tous les films qui ont un acteur qui provient de l'Australie ou de Israël. Il faut afficher uniquement le titre du film et le nom de la compagnie de production et sans aucun doublon. Il faut trier la liste par titre de film.

        

            Copié
            
        

        SELECT DISTINCT Films.Titre, Productions.NomCompagnie
FROM Films
INNER JOIN  Productions ON Films.ProductionId = Productions.ProductionId
INNER JOIN FilmsActeurs ON Films.FilmId = FilmsActeurs.FilmId
INNER JOIN Acteurs ON FilmsActeurs.ActeurId = Acteurs.ActeurId
INNER JOIN Pays ON Acteurs.PaysCode = Pays.Code
WHERE
    Pays.Nom = 'Australie' OR
    Pays.Nom = 'Israël'
ORDER BY Films.Titre

Version originale par François St-Hilaire

Mise à jour 2023 par Pierre-Luc Boulanger

Table des matières