Base de données en exemple
La base de données sera la gestion_film_c13.
Voici le DEA.
Créez la base de données gestion_film_c13.
Exécutez le script de création des tables.
CREATE TABLE IF NOT EXISTS genres( genre_id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, nom VARCHAR(50) NOT NULL CHECK(nom <> ''), description TEXT, creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, modification TIMESTAMP NULL);CREATE TABLE IF NOT EXISTS pays( code CHAR(2) NOT NULL PRIMARY KEY, nom VARCHAR(50) NOT NULL UNIQUE CHECK(nom <> ''), creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, modification TIMESTAMP NULL);CREATE TABLE IF NOT EXISTS acteurs( acteur_id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, nom VARCHAR(30) NOT NULL CHECK(nom <> ''), prenom VARCHAR(30) NOT NULL CHECK(prenom <> ''), nationalite_pays_code CHAR(2) NOT NULL, biographie TEXT, url_imbd VARCHAR(2000) NOT NULL CHECK(url_imbd LIKE 'https://%'), creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, modification TIMESTAMP NULL, FOREIGN KEY(nationalite_pays_code) REFERENCES pays(Code) ON DELETE RESTRICT ON UPDATE CASCADE);CREATE TABLE IF NOT EXISTS productions( production_id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, nom_compagnie VARCHAR(100) NOT NULL CHECK(nom_compagnie <> ''), annee_fondation SMALLINT, siege_social_pays_code CHAR(2) NOT NULL, creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, modification TIMESTAMP NULL, FOREIGN KEY(siege_social_pays_code) REFERENCES pays(Code) ON DELETE RESTRICT ON UPDATE CASCADE);CREATE TABLE IF NOT EXISTS films( film_id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, titre VARCHAR(150) NOT NULL CHECK(titre <> ''), description TEXT DEFAULT 'Description non disponible', production_id INTEGER NOT NULL, date_sortie DATE, url_imbd VARCHAR(2000) NOT NULL CHECK(url_imbd LIKE 'https://%'), genre_id INTEGER NOT NULL, creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, modification TIMESTAMP NULL, FOREIGN KEY (production_id) REFERENCES productions(production_id) ON DELETE RESTRICT ON UPDATE CASCADE, FOREIGN KEY(genre_id) REFERENCES genres(genre_id) ON DELETE RESTRICT ON UPDATE CASCADE);CREATE TABLE IF NOT EXISTS films_acteurs( film_id INTEGER NOT NULL, acteur_id INTEGER NOT NULL, nom_personnage VARCHAR(100) NOT NULL CHECK(nom_personnage <> ''), creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, modification TIMESTAMP NULL, PRIMARY KEY (film_id, acteur_id), FOREIGN KEY(film_id) REFERENCES films(film_id) ON DELETE RESTRICT ON UPDATE CASCADE, FOREIGN KEY(acteur_id) REFERENCES acteurs(acteur_id) ON DELETE RESTRICT ON UPDATE CASCADE);
Voici le script d'insertion.
INSERT INTO pays(code, nom)VALUES ('CA', 'Canada'), ('FR', 'France'), ('AU', 'Australie'), ('IL', 'Israël'), ('US', 'États-Unis'), ('JP', 'Japon');INSERT INTO productions(production_id, nom_compagnie, annee_fondation, siege_social_pays_code)VALUES (1, 'Disney', 1923, 'US'), (2, 'Sony Pictures', 1987, 'JP'), (3, 'Paramount Picture', 1912, 'US');INSERT INTO genres(genre_id, 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 films(film_id, titre, description, production_id, genre_id, date_sortie, url_imbd)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(acteur_id, nom, prenom, nationalite_pays_code, biographie, url_imbd)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');INSERT INTO films_acteurs(acteur_id, film_id, nom_personnage)VALUES (1, 1, 'Thor'), (1, 2, 'Thor'), (1, 3, 'James Hunt'), (2, 1, 'Jane Foster'), (2, 2, 'Jane Foster'), (3, 3, 'Suzy Miller'), (4, 4, 'Martin Dubois');
Vue - VIEW
Une vue consiste à créer une table virtuelle à partir d'une requête. La vue permet de simplifier la requête pour son utilisateur et il est possible d'ajouter des jointures et des conditions à la vue. Une vue peut être utilisée partout où une table peut l'être. Il est par contre interdit d'insérer ou d'effacer des enregistrements dans une vue.
Il est important de comprendre qu'une vue peut sembler simple, mais que la requête qui la définit peut est très complexe (plusieurs jointures avec des agrégations, des conditions complexes et des sous-requêtes). Il ne faut donc pas s'étonner si une requête est très lente lors de l'utilisation d'une vue.
Pour plus d'information : https://www.postgresql.org/docs/current/sql-createview.html
Demande
Voici une demande de requête.
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_pays_acteur) et de la compagnie de production (nom_compagnie). Il faut que la liste soit seulement pour les acteurs du Canada. Il faut trier la liste par le titre du film.
SELECT films.titre, films_acteurs.nom_personnage, acteurs.prenom AS prenom_acteur, acteurs.nom AS nom_acteur, pays_acteur.nom AS nom_pays_acteur, productions.nom_compagnieFROM films_acteursINNER JOIN acteurs ON acteurs.acteur_id = films_acteurs.acteur_idINNER JOIN films ON films.film_id = films_acteurs.film_idINNER JOIN Pays pays_acteur ON acteurs.nationalite_pays_code = pays_acteur.CodeINNER JOIN productions on films.production_id = productions.production_idWHERE acteurs.nationalite_pays_code = 'CA'ORDER BY films.titre;
Le lendemain, il faut refaire une requête pour les acteurs des États-Unis.
SELECT films.titre, films_acteurs.nom_personnage, acteurs.prenom AS prenom_acteur, acteurs.nom AS nom_acteur, pays_acteur.nom AS nom_pays_acteur, productions.nom_compagnieFROM films_acteursINNER JOIN acteurs ON acteurs.acteur_id = films_acteurs.acteur_idINNER JOIN films ON films.film_id = films_acteurs.film_idINNER JOIN Pays pays_acteur ON acteurs.nationalite_pays_code = pays_acteur.CodeINNER JOIN productions on films.production_id = productions.production_idWHERE acteurs.nationalite_pays_code = 'US'ORDER BY films.titre;
Création d'une vue - CREATE VIEW
Il serait plus simple de créer une vue sans aucune condition et aucun tri.
Pour créer une vue, il faut utiliser la commande CREATE VIEW. Il est possible d'ajouter OR REPLACE également.
La syntaxe est CREATE OR REPLACE VIEW nom_de_la_vue AS. Il faut faire la requête après le AS.
CREATE OR REPLACE VIEW info_film_completASSELECT films.titre, films_acteurs.nom_personnage, acteurs.prenom AS prenom_acteur, acteurs.nom AS nom_acteur, pays_acteur.nom AS nom_pays_acteur, productions.nom_compagnieFROM films_acteursINNER JOIN acteurs ON acteurs.acteur_id = films_acteurs.acteur_idINNER JOIN films ON films.film_id = films_acteurs.film_idINNER JOIN Pays pays_acteur ON acteurs.nationalite_pays_code = pays_acteur.CodeINNER JOIN productions on films.production_id = productions.production_id;
Utilisation d'une vue avec tri et condition
Pour utiliser la vue, il faut tout simplement effectuer un SELECT sur la vue info_film_complet.
SELECT * FROM info_film_complet;
Il est possible d'ajouter un tri sur la vue.
SELECT *FROM info_film_completORDER BY prenom_acteur, nom_acteur;
Il est possible d'ajouter une condition. Remarquez la condition ci-dessous. Il n'est pas possible de le faire sur le code du pays, car le code n'est pas dans les champs du SELECT de la vue. Seulement les champs disponibles dans la vue sont utilisables pour les conditions.
SELECT *FROM info_film_completWHERE nom_pays_acteur = 'Canada';
Il est possible de combiner les deux.
SELECT *FROM info_film_completWHERE nom_pays_acteur = 'Australie'ORDER BY Titre;
Supprimer une vue - DROP VIEW
Pour supprimer une vue, il faut utiliser la commande DROP VIEW. Il est possible d'ajouter le IF EXISTS également.
DROP VIEW IF EXISTS info_film_complet;
Utilisation d'une vue avec une jointure
Il serait intéressant de faire une jointure pour avoir à l'occasion l'information sur le genre du film. Par contre, il faut que la clé genre_id de la table films soit dans les champs de la vue.
Il peut être pratique de rendre disponibles toutes les clés dans une vue pour permettre leur réutilisation.
Voici la vue modifiée.
CREATE OR REPLACE VIEW info_film_completASSELECT films.film_id, --Ajout films.titre, films.genre_id, --Ajout films_acteurs.nom_personnage, acteurs.acteur_id, --Ajout acteurs.prenom AS prenom_acteur, acteurs.nom AS nom_acteur, acteurs.nationalite_pays_code AS nationalite_pays_code, --Ajout pays_acteur.nom AS nom_pays_acteur, productions.production_id, --Ajout productions.nom_compagnieFROM films_acteursINNER JOIN acteurs ON acteurs.acteur_id = films_acteurs.acteur_idINNER JOIN films ON films.film_id = films_acteurs.film_idINNER JOIN Pays pays_acteur ON acteurs.nationalite_pays_code = pays_acteur.CodeINNER JOIN productions on films.production_id = productions.production_id;
Voici la vue avec la jointure.
SELECT info_film_complet.*, --Affiche tous les champs de la vue genres.nom as nom_genre, genres.description as description_genreFROM info_film_completINNER JOIN genres ON info_film_complet.genre_id = genres.genre_id;
Dans le cas qu'il faudrait ajouter le nom du pays de la compagnie de production, il n'est pas possible de faire une jointure sur la table pays, car le champ productions.siege_social_pays_code n'est pas dans la vue. Il est par contre possible de faire une jointure sur la table productions et de faire une 2e jointure sur la table pays pour avoir le nom du pays.
SELECT info_film_complet.*, pays_production.nom AS siege_social_pays_code_productionFROM info_film_completINNER JOIN productions ON info_film_complet.production_id = productions.production_idINNER JOIN Pays pays_production ON productions.siege_social_pays_code = pays_production.code
Utilisation d'une vue avec l'agrégation
Il est possible d'utiliser les fonctions d'agrégation avec une vue. Par exemple, il faut connaître le nombre par acteurs.
SELECT info_film_complet.acteur_id, info_film_complet.prenom_acteur, info_film_complet.nom_acteur, Count(*) AS nb_filmFROM info_film_completGROUP BY info_film_complet.acteur_id, info_film_complet.prenom_acteur, info_film_complet.nom_acteur;
Il est possible d'ajouter un HAVING pour avoir les acteurs avec plus de 1 film.
SELECT info_film_complet.acteur_id, info_film_complet.prenom_acteur, info_film_complet.nom_acteur, Count(*) AS nb_filmFROM info_film_completGROUP BY info_film_complet.acteur_id, info_film_complet.prenom_acteur, info_film_complet.nom_acteurHAVING Count(*) > 1;
Intégrer l'agrégation dans une vue
Il est possible de faire une vue avec un GROUP BY intégré.
Par exemple, il faut avoir une vue qui affiche le nombre de films par pays de production.
CREATE OR REPLACE VIEW nb_film_par_pays_productionASSELECT pays_production.code AS siege_social_pays_code_production, pays_production.nom AS nom_pays_production, Count(*) AS nb_filmsFROM filmsINNER JOIN productions on films.production_id = productions.production_idINNER JOIN Pays pays_production ON productions.siege_social_pays_code = pays_production.CodeGROUP BY pays_production.code, pays_production.nom;
Il est possible d'utiliser la vue directement.
SELECT *FROM nb_film_par_pays_production;
Par contre, s'il faut afficher uniquement les pays avec plus de 2 films, il faut utiliser un WHERE et non un HAVING. La vue est considérée comme une table, donc, il faut utiliser le WHERE pour les conditions, même si c'est pour un champ d'agréation.
SELECT *FROM nb_film_par_pays_productionWHERE nb_films > 2;
Transaction
Pour une base de données, une transaction consiste à effectuer plusieurs opérations et de les considérer comme étant une seule opération.
La transaction permet de s'assurer de l'intégrité des données.
Par exemple, vous devez effectuer un virement bancaire de 100$ à un ami.
Il y aura 2 opérations dans la base de données. Le retrait de 100$ dans votre compte et l'ajout de 100$ dans le compte de votre ami.
Pour s'assurer de l'intégrité de l'opération, il faut que les 2 opérations soient effectuées.
Si le serveur tombe en panne après le retrait, il faut que l'argent soit de retour dans votre compte. Sinon, vous allez être à -100$ et votre ami n'aura pas son argent.
Il y a 2 notions dans le principe de transaction.
- COMMIT
Cette action consiste à confirmer la transaction. Toutes les opérations qui ont été effectuées dans la transaction seront confirmées dans la base de données. - ROLLBACK
Cette action consiste à annuler la transaction. Toutes les opérations qui ont été effectuées dans la transaction seront annulées de la base de données. Les données modifiées seront de retour avec leur valeur avant la transaction. Les données ajoutées seront supprimées et les données supprimées seront réintégrées.
Exemple
Ouvrez une nouvelle fenêtre de requête dans pgAdmin.
Le mot-clé BEGIN; indique au SGBD que les prochaines commandes sont dans une transaction.
Exécutez le code ci-dessous.
BEGIN;DELETE FROM films_acteurs;DELETE FROM acteurs;DELETE FROM films;
Effectuez les projections ci-dessous.
SELECT * FROM acteurs;SELECT * FROM films;SELECT * FROM films_acteurs;
Les enregistrements ne sont plus là, mais ils ne sont pas confirmés.
Exécutez la commande ci-dessous pour annuler la transaction et la terminer.
ROLLBACK;
Effectuez les projections ci-dessous.
SELECT * FROM acteurs;SELECT * FROM films;SELECT * FROM films_acteurs;
Les enregistrements sont de retour.
Pour confirmer la transaction, il faut appeler la commande COMMIT à la place d'un ROLLBACK.
Refaites le même exercice, mais au lieu de faire la commande ROLLBACK;, fermez l'onglet de la console. Le résultat sera le même, car le SGBD détectera la fin de la connexion initiée par la fenêtre de console. Cette connexion n'a pas fait un COMMIT, donc le SGBD déduira que la transaction est incomplète et il va initier lui-même le ROLLBACK.
Bloc à utiliser
Pour votre TP, voici le bloc à utiliser pour que les transactions fonctionnent automatiquement.
BEGIN; --Mettre les commandes ici --En cas d'exception, la transaction effectue AUTOMATIQUEMENT un ROLLBACK --Sinon le COMMIT est exécutéCOMMIT;
Voici un exemple pour insérer un acteur et ses films dans une seule transaction.
Il faut ajouter Robert Downey Jr, les films Iron Man et Iron Man 2. Il faut également faire l'association dans la table pivot films_acteurs.
Le code ci-dessous a un problème de clé primaire à la ligne 23.
BEGIN; --Mettre les commandes ici INSERT INTO acteurs(acteur_id, Nom, Prenom, nationalite_pays_code, biographie, url_imbd) VALUES (5, 'Robert', 'Downey', 'US', 'Acteur américain...', 'https://www.imdb.com/name/nm0000375/?ref_=tt_cl_t_1'); INSERT INTO films(film_id, Titre, description, production_id, genre_id, date_sortie, url_imbd) VALUES (5, 'Iron man', 'Après avoir été retenu prisonnier dans une grotte afghane, l''ingénieur milliardaire Tony Stark crée une armure unique pour lutter contre le mal.', 1, 3, '2008-05-02', 'https://www.imdb.com/title/tt0371746/?ref_=nv_sr_srsg_0'), (6, 'Iron man 2', 'Le monde étant maintenant conscient de son identité d''Iron Man, Tony Stark doit faire face à la fois à sa santé qui se détériore et à un vengeur fou ayant des liens avec l''héritage de son père.', 1, 3, '2010-05-07', 'https://www.imdb.com/title/tt1228705/?ref_=fn_al_tt_1'); INSERT INTO films_acteurs(film_id, acteur_id, nom_personnage) VALUES (5, 5, 'Tony Stark'), (5, 5, 'Tony Stark'); --Doublon pour la clé primaire. Devrait être (6, 5, 'Tony Stark') --En cas d'exception, la transaction effectue AUTOMATIQUEMENT un ROLLBACK --Sinon le COMMIT est exécutéCOMMIT;
Effectuez le code ci-dessous pour voir s’il a eu de nouveaux enregistrements.
SELECT * FROM acteurs;SELECT * FROM films;SELECT * FROM films_acteurs;
Les enregistrements n'ont pas été enregistrés dans la base de données grâce au ROLLBACK automatique en cas d'erreur.
Voici le bloc avec la correction.
BEGIN; --Mettre les commandes ici INSERT INTO acteurs(acteur_id, Nom, Prenom, nationalite_pays_code, biographie, url_imbd) VALUES (5, 'Robert', 'Downey', 'US', 'Acteur américain...', 'https://www.imdb.com/name/nm0000375/?ref_=tt_cl_t_1'); INSERT INTO films(film_id, Titre, description, production_id, genre_id, date_sortie, url_imbd) VALUES (5, 'Iron man', 'Après avoir été retenu prisonnier dans une grotte afghane, l''ingénieur milliardaire Tony Stark crée une armure unique pour lutter contre le mal.', 1, 3, '2008-05-02', 'https://www.imdb.com/title/tt0371746/?ref_=nv_sr_srsg_0'), (6, 'Iron man 2', 'Le monde étant maintenant conscient de son identité d''Iron Man, Tony Stark doit faire face à la fois à sa santé qui se détériore et à un vengeur fou ayant des liens avec l''héritage de son père.', 1, 3, '2010-05-07', 'https://www.imdb.com/title/tt1228705/?ref_=fn_al_tt_1'); INSERT INTO films_acteurs(film_id, acteur_id, nom_personnage) VALUES (5, 5, 'Tony Stark'), (6, 5, 'Tony Stark'); --Enregistrement corrigé --En cas d'exception, la transaction effectue AUTOMATIQUEMENT un ROLLBACK --Sinon le COMMIT est exécutéCOMMIT;
Effectuez le code ci-dessous pour voir si il a eu de nouveaux enregistrements.
SELECT * FROM acteurs;SELECT * FROM films;SELECT * FROM films_acteurs;
Les enregistrements ont été enregistrés dans la base de données grâce au COMMIT et qu'il n'y a pas eu d'erreur.
Produit à partir des notes de Benoit Desrosiers
Version modifiée en 2022 par François St-Hilaire
Version modifiée en 2023 pour PostgreSQL par Pierre-Luc Boulanger