/*
-- =========================================================================== A
Activité : IFT187
Trimestre : 2016-3
Encodage : UTF-8, sans BOM; fin de ligne Unix (LF)
Plateforme : PostgreSQL 9.5.1
Responsable : Luc.Lavoie@USherbrooke.ca
Version : 0.1.3a
Statut : en vigueur
Résumé : Création des tables du schéma Films (boutique de films en ligne).
-- =========================================================================== A
*/
/*
-- =========================================================================== B
Modélisation du schéma
~~~~~~~~~~~~~~~~~~~~~~
La présente modélisation découle du document de vision [ddv].

** Pays d'origine
Le pays d'origine d'un film est un attribut fréquemment utilisé, mais flou.
D'une part, les co-productions se multiplient et d'autre part la base sur
laquelle les «pays d'origine» sont établis varie beaucoup : est-ce la
nationalité du réalisateur, du producteur, du scénariste, des principaux
comédiens, des principaux bailleurs de fonds, des studios ? Pour cette raison,
il a été jugé préférable de ne pas modéliser cet attribut pour les films, mais
plutot de permettre de le définir sur la base des nationalités des autres
entités qui llui sont associés.

** Version originale, doublage et sous-titrage
Dans le contexte d'une boutique en ligne qui diffusera ou offrira le téléchargement
de la copie demandée, il faut prendre soin de ne pas «imposer» les contraintes
des supports physiques utilisés autrefois (cassette, CD, DVD, BR).

Par exemple, à l’époque des supports physiques, les versions disponibles dans
le commerce comprenaient des collections de doublages et de sous-titres pré-établis
en fonction de l’aire de distribution. Il était souvent difficile de trouver
des oeuvres doublées ou sous-titrées en allemand au Québec, alors qu'elles étaient
généralement disponibles en Europe centrale.

Compte tenu de la nature des produits de notre boutique et du mode
d'acheminement proposé (diffusion ou téléversement), on pourrait espérer que
le client puisse commander une version avec les langues doublées et les langues
sous-titrées de son choix.

Par exemple, je commande «Pirates des Caraïbes» en anglais américain, doublé
en français québécois, mais sous-titré en arabe (parce que la version doublée
n’est pas disponible). En résumé, je choisis ce que je veux (dans ce qui est
disponible) et on me le livre.

Notes de mise en oeuvre
~~~~~~~~~~~~~~~~~~~~~~~
Les clés artificielles sont généralement représentées par CHAR(n) où n a été
choisi en fonction de la cardinalité pressentie des données d'essai.
Par ailleurs, le caractère initial de la clé sera une lettre (majuscule)
permettant de rappeler la nature de l'entité représentée (F pour film,
A pour artisan, etc.). Dans une BD plus réaliste, ces clés seraient
vraisemblablement représentées à l'aide d'une séquence obtenue par un type
spécialisé (serial) ou un séquenceur.

On pourrait présumer que les codes internationaux des pays et des langues sont
si fréquemment utilisés qu'un schéma du SGBD les contient déjà, au bénéfice
des autres schémas. Dans le cadre du présent exercice, et pour rendre l'exemple
autonome, nous les avons redéfinies localement. Leur initialisation utilisera
toutefois des tables existantes dont le contenu est conforme aux normes ISO.

-- =========================================================================== B
*/

/**
 * Un pays est identifié par le code ISO 3166-1 "idPays" et porte le nom français "pays".
 * Le code à deux lettres a été retenu (il existe des codes à trois lettres).
 * Par convention, les codes de pays sont en lettres majuscules.
 * Source : http://www.iso.org/iso/fr/french_country_names_and_code_elements#y.
 */
CREATE TABLE Pays
(
  idPays CHAR(2) NOT NULL,
  pays VARCHAR(60) NOT NULL,
  CONSTRAINT Pays_cc0 PRIMARY KEY (idPays),
  CONSTRAINT Pays_cc1 UNIQUE (pays),
  CONSTRAINT Pays_id CHECK (idPays SIMILAR TO '[A-Z]{2}'),
  CONSTRAINT Pays_nom CHECK (LENGTH(pays) > 0)
);

/**
 * La langue identifiée par le code ISO 639-1 "idLangue" porte le nom français "langue".
 * Le code à deux lettres a été retenu (il existe des codes à trois lettres).
 * Par convention, les codes de langue sont en lettres minuscules.
 * Source : https://www.loc.gov/standards/iso639-2/php/code_list.php
 */
CREATE TABLE Langue
(
  idLangue CHAR(2) NOT NULL,
  langue VARCHAR(80) NOT NULL,
  CONSTRAINT Langue_cc0 PRIMARY KEY (idLangue),
  CONSTRAINT Langue_cc1 UNIQUE (langue),
  CONSTRAINT Langue_id CHECK (idLangue SIMILAR TO '[a-z]{2}'),
  CONSTRAINT Langue_description CHECK (LENGTH(langue) > 0)
);

/**
 * Le film identifié par "idFilm" porte le titre "titre", est sorti en
 * version (langue) originale "vo" au cours de l'année "parution" et a une durée
 * de "duree" (en secondes).
 */
CREATE TABLE Film
(
  idFilm CHAR(4) NOT NULL,
  titre VARCHAR(120) NOT NULL,
  vo CHAR(2) NOT NULL,
  parution NUMERIC(4) NOT NULL,
  duree NUMERIC(6) NOT NULL,
  CONSTRAINT Film_cc0 PRIMARY KEY (idFilm),
  CONSTRAINT Film_ce0 FOREIGN KEY (vo) REFERENCES Langue (idLangue),
  CONSTRAINT Film_id CHECK (idFilm SIMILAR TO 'F[0-9]{3}'),
  CONSTRAINT Film_titre CHECK (LENGTH(titre) > 0),
  CONSTRAINT Film_parution CHECK (parution > 1870),
  CONSTRAINT Film_duree CHECK (duree>0)
) ;

/**
 * Le film "idFilm" est disponible en la langue "idLangue" selon le mode "mode".
 * Il y a deux modes : 'D' pour doublé et 'S' pour sous-titré.
 */
CREATE TABLE VersionDisponible
(
  idFilm CHAR(4) NOT NULL,
  idLangue CHAR(2) NOT NULL,
  mode CHAR(1) NOT NULL,
  CONSTRAINT VersionDisponible_cc0 PRIMARY KEY (idFilm, idLangue, mode),
  CONSTRAINT VersionDisponible_ce0 FOREIGN KEY (idFilm) REFERENCES Film,
  CONSTRAINT VersionDisponible_ce1 FOREIGN KEY (idLangue) REFERENCES Langue,
  CONSTRAINT VersionDisponible_mode CHECK (mode IN ('D', 'S'))
);

/**
 * Le genre cinématographique  identifié par "idGenre" porte le nom français "genre".
 * Source : https://fr.wikipedia.org/wiki/Genre_cinématographique *** trouver mieux!
 */
CREATE TABLE Genre
(
  idGenre CHAR(4)  NOT NULL,
  genre VARCHAR(40) NOT NULL,
  CONSTRAINT Genre_cc0 PRIMARY KEY (idGenre),
  CONSTRAINT Genre_cc1 UNIQUE (genre),
  CONSTRAINT Genre_id CHECK (idGenre SIMILAR TO 'G[0-9]{3}'),
  CONSTRAINT Genre_genre CHECK (LENGTH(genre) > 0)
);

/**
 * Le film "idFilm" appartient au genre idGenre; un film peut appartenir à plus d'une genre.
 */
CREATE TABLE FilmGenre
(
  idFilm CHAR(4) NOT NULL,
  idGenre CHAR(4) NOT NULL,
  CONSTRAINT FilmGenre_cc0 PRIMARY KEY (idFilm, idGenre),
  CONSTRAINT FilmGenre_ce0 FOREIGN KEY (idFilm) REFERENCES Film,
  CONSTRAINT FilmGenre_ce1 FOREIGN KEY (idGenre) REFERENCES Genre
);

/**
 * Le studio identifié par "idStudio" porte le nom "studio" et est localisé à "localisation".
 */
CREATE TABLE Studio
(
  idStudio CHAR(4) NOT NULL,
  studio VARCHAR(60) NOT NULL,
  localisation CHAR(2) NOT NULL,
  CONSTRAINT Studio_cc0 PRIMARY KEY (idStudio),
  CONSTRAINT Studio_cc1 UNIQUE(studio,localisation),
  CONSTRAINT Studio_ce0 FOREIGN KEY (localisation) REFERENCES Pays(idPays),
  CONSTRAINT Studio_id CHECK (idStudio SIMILAR TO 'S[0-9]{3}'),
  CONSTRAINT Studio_nom CHECK (LENGTH(studio) > 0)
);

/**
 * Le film "idFilm" est principalement produit par le studio "idStudio"
 */
CREATE TABLE Production
(
  idFilm CHAR(4) NOT NULL,
  idStudio CHAR(4) NOT NULL,
  CONSTRAINT Production_cc0 PRIMARY KEY (idFilm, idStudio),
  CONSTRAINT Production_ce0 FOREIGN KEY (idFilm) REFERENCES Film,
  CONSTRAINT Production_ce1 FOREIGN KEY (idStudio) REFERENCES Studio
);

/**
 * L'artisan identifié par "idArtisan", de sexe "sexe", porte le nom "nom" et le prénom "prénom".
 */
CREATE TABLE Artisan
(
  idArtisan CHAR(4) NOT NULL,
  nom VARCHAR(30) NOT NULL,
  prenom VARCHAR(30) NOT NULL,
  sexe CHAR (1) NOT NULL,
  CONSTRAINT Artisan_cc0 PRIMARY KEY (idArtisan),
  CONSTRAINT Artisan_id CHECK (idArtisan SIMILAR TO 'A[0-9]{3}'),
  CONSTRAINT Artisan_sexe CHECK (sexe IN ('F', 'M', 'I')),
  CONSTRAINT Artisan_nom CHECK (LENGTH(nom) > 0),
  CONSTRAINT Artisan_prenom CHECK (LENGTH(prenom) > 0)
);

/**
 * L'artisan "idArtisan" est né le "naissance".
 */
CREATE TABLE Naissance
(
  idArtisan CHAR(4) NOT NULL,
  naissance DATE NOT NULL,
  CONSTRAINT Naissance_cc0 PRIMARY KEY (idArtisan),
  CONSTRAINT Naissance_ce0 FOREIGN KEY (idArtisan) REFERENCES Artisan,
  CONSTRAINT Naissance_naissance CHECK (EXTRACT(YEAR FROM naissance) > 1770)
);

/**
 * L'artisan "idArtisan" est décédé le "deces".
 */
CREATE TABLE Deces
(
  idArtisan CHAR(4) NOT NULL,
  deces DATE NOT NULL,
  CONSTRAINT Deces_cc0 PRIMARY KEY (idArtisan),
  CONSTRAINT Deces_ce0 FOREIGN KEY (idArtisan) REFERENCES Artisan,
  CONSTRAINT Deces_deces CHECK (EXTRACT(YEAR FROM deces) > 1870)
);

/**
 * L'artisan "idArtisan" est notationalité "nationalite"; il peut en cumuler plusieurs.
 */
CREATE TABLE Nationalite
(
  idArtisan CHAR(4) NOT NULL,
  nationalite CHAR(2) NOT NULL,
  CONSTRAINT Nationalite_cc0 PRIMARY KEY (idArtisan,nationalite),
  CONSTRAINT Nationalite_ce0 FOREIGN KEY (idArtisan) REFERENCES Artisan,
  CONSTRAINT Nationalite_ce1 FOREIGN KEY (nationalite) REFERENCES Pays(idPays)
);

/**
 * Le poste identifié par "idPoste" porte le nom français "poste".
 */
CREATE TABLE Poste
(
  idPoste CHAR(4) NOT NULL,
  poste VARCHAR(40) NOT NULL,
  CONSTRAINT Poste_cc0 PRIMARY KEY (idPoste),
  CONSTRAINT Poste_id CHECK (idPoste SIMILAR TO 'P[0-9]{3}'),
  CONSTRAINT Poste_poste CHECK (LENGTH(poste) > 0)
);

/**
 * L'artisan "idArtisan" occupe le poste "idPoste" dans le film "idFilm".
 */
CREATE TABLE Participation
(
  idArtisan CHAR(4) NOT NULL,
  idPoste CHAR(4) NOT NULL,
  idFilm CHAR(4) NOT NULL,
  CONSTRAINT Participation_cc0 PRIMARY KEY (idArtisan, idPoste, idFilm),
  CONSTRAINT Participation_ce0 FOREIGN KEY (idArtisan) REFERENCES Artisan,
  CONSTRAINT Participation_ce1 FOREIGN KEY (idPoste) REFERENCES Poste,
  CONSTRAINT Participation_ce2 FOREIGN KEY (idFilm) REFERENCES Film
);

/**
 * Une recette du film "idFilm" à l'année "annee" est de "revenu" USD.
 */
CREATE TABLE Recette
(
  idFilm CHAR(4) NOT NULL,
  annee NUMERIC(4) NOT NULL,
  revenu NUMERIC(9) NOT NULL,
  CONSTRAINT Recette_cc0 PRIMARY KEY (idFilm, annee),
  CONSTRAINT Recette_ce0 FOREIGN KEY (idFilm) REFERENCES Film,
  CONSTRAINT Recette_annee CHECK (annee > 1870),                -- on pourrait faire mieux, comment ?
  CONSTRAINT Recette_revenu CHECK (revenu >= 0)
);

/*
-- =========================================================================== Z
Contributeurs :
  (CK) Christina.Khnaisser@USherbrooke.ca,
  (LL) Luc.Lavoie@USherbrooke.ca

Adresse, droits d'auteur et copyright :
  Groupe Metis
  Département d'informatique
  Faculté des sciences
  Université de Sherbrooke
  Sherbrooke (Québec)  J1K 2R1
  Canada
  http://info.usherbrooke.ca/llavoie/
  [CC-BY-NC-4.0 (http://creativecommons.org/licenses/by-nc/4.0)]

Tâches projetées :
  NIL

Tâches réalisées :
  2016-09-18 (LL) : Retrait du pays d'origine du film.
  2016-09-17 (LL) : Ajout des versions sous-titrées.
  2016-09-16 (CK) : Création

Références :
[ddv] http://info.usherbrooke.ca/llavoie/enseignement/Exemples/Films

-- -----------------------------------------------------------------------------
-- fin de Exemples/Films/Films_cre.sql
-- =========================================================================== Z
*/

/*
-- =========================================================================== A
Activité : IFT187
Trimestre : 2016-3
Encodage : UTF-8, sans BOM; fin de ligne Unix (LF)
Plateforme : PostgreSQL 9.5.1
Responsable : Luc.Lavoie@USherbrooke.ca
Version : 0.1.0b
Statut : en vigueur
Résumé : Destruction des tables du schéma.
-- =========================================================================== A
*/

/*
-- =========================================================================== B
Destruction des tables du schéma correspondant au problème de boutique en ligne
de films. Pour plus d'information, voir Films_cre.sql

Notes de mise en oeuvre
(a) aucune.
-- =========================================================================== B
*/

DELETE FROM FilmGenre;
DELETE FROM Production;
DELETE FROM Deces;
DELETE FROM Naissance;
DELETE FROM Nationalite;
DELETE FROM Participation;
DELETE FROM Artisan;
DELETE FROM Recette ;
DELETE FROM VersionDisponible ;
DELETE FROM Film ;
DELETE FROM Langue;
DELETE FROM Pays;
DELETE FROM Genre;
DELETE FROM Poste;
DELETE FROM Studio;

/*
-- =========================================================================== Z
Contributeurs :
  (CK) Christina.Khnaisser@USherbrooke.ca,
  (LL) Luc.Lavoie@USherbrooke.ca

Adresse, droits d'auteur et copyright :
  Groupe Metis
  Département d'informatique
  Faculté des sciences
  Université de Sherbrooke
  Sherbrooke (Québec)  J1K 2R1
  Canada
  http://info.usherbrooke.ca/llavoie/
  [CC-BY-NC-4.0 (http://creativecommons.org/licenses/by-nc/4.0)]

Tâches projetées :
NIL

Tâches réalisées :
2016-09-16 (CK) :Création

Références :
[mod] http://info.usherbrooke.ca/llavoie/enseignement/Modules/

-- -----------------------------------------------------------------------------
-- fin de Exemples/Evaluation/Evaluation_drop.sql
-- =========================================================================== Z
*/

/*
-- =========================================================================== A
Activité : IFT187
Trimestre : 2016-3
Encodage : UTF-8, sans BOM; fin de ligne Unix (LF)
Plateforme : PostgreSQL 9.5.1
Responsable : Luc.Lavoie@USherbrooke.ca
Version : 0.1.0b
Statut : en vigueur
Résumé : Destruction des tables du schéma.
-- =========================================================================== A
*/

/*
-- =========================================================================== B
Destruction des tables du schéma correspondant au problème de boutique en ligne
de films. Pour plus d'information, voir Films_cre.sql

Notes de mise en oeuvre
(a) aucune.
-- =========================================================================== B
*/

DROP TABLE Langue CASCADE;
DROP TABLE Pays CASCADE;
DROP TABLE Film CASCADE;
DROP TABLE VersionDisponible CASCADE;
DROP TABLE Genre CASCADE;
DROP TABLE FilmGenre CASCADE;
DROP TABLE Studio CASCADE;
DROP TABLE Production CASCADE;
DROP TABLE Artisan CASCADE;
DROP TABLE Deces CASCADE;
DROP TABLE Naissance CASCADE;
DROP TABLE Nationalite CASCADE;
DROP TABLE Poste CASCADE;
DROP TABLE Participation CASCADE;
DROP TABLE Recette CASCADE;

  /*
-- =========================================================================== Z
Contributeurs :
  (CK) Christina.Khnaisser@USherbrooke.ca,
  (LL) Luc.Lavoie@USherbrooke.ca

Adresse, droits d'auteur et copyright :
  Groupe Metis
  Département d'informatique
  Faculté des sciences
  Université de Sherbrooke
  Sherbrooke (Québec)  J1K 2R1
  Canada
  http://info.usherbrooke.ca/llavoie/
  [CC-BY-NC-4.0 (http://creativecommons.org/licenses/by-nc/4.0)]

Tâches projetées :
NIL

Tâches réalisées :
2016-09-16 (CK) :Création

Références :
[mod] http://info.usherbrooke.ca/llavoie/enseignement/Modules/

-- -----------------------------------------------------------------------------
-- fin de Exemples/Evaluation/Evaluation_drop.sql
-- =========================================================================== Z
*/

/*
-- =========================================================================== A
Activité : IFT187
Trimestre : 2016-3
Encodage : UTF-8, sans BOM; fin de ligne Unix (LF)
Plateforme : PostgreSQL 9.5.1
Responsable : Luc.Lavoie@USherbrooke.ca
Version : 0.1.0b
Statut : en vigueur
Résumé : Insertions pour tester le bon fonctionnement de nos requêtes
-- =========================================================================== A
*/
/*
-- =========================================================================== B
Notes de mise en oeuvre
~~~~~~~~~~~~~~~~~~~~~~~
...

-- =========================================================================== B
*/

--Tests pour la requête no 1:

INSERT INTO Film (idFilm, titre, vo, parution, duree) VALUES --Insertion d'un film inventé
  ('F021','Comment faire un TP un dimanche matin en 2 étapes faciles', 'fr', 2016, 2000);

INSERT INTO Participation(idArtisan, idFilm, idPoste) VALUES --Insertion de plusieurs artisans dans le film inventé pour voir si le nombre
                                 --correspond bien au nombre de tuples implémentées (Ça fonctionne).
  ('A000','F021','P002'),
  ('A001','F021','P002'),
  ('A002','F021','P002'),
  ('A003','F021','P002'),
  ('A004','F021','P002');

/*******************************************************************************************************************************/

--Tests pour la requête no2:

INSERT INTO Film (idFilm, titre, vo, parution, duree) VALUES --Insertion de plusieurs films sortis dans la décennie 1950 (non-utilisée
                                 --initialement) pour voir si le nombre correspond bien au nombre de tuples
                                 --implémentées (Ça fonctionne).
  ('F022','Comment adorer faire un TP un dimanche matin en 2 étapes faciles', 'fr', 1956, 2000),
  ('F023','Comment prouver que le film F022 a un titre impossible en 2 étapes faciles', 'fr', 1951, 2000),
  ('F024','Comment trouver des idées de titre en 2 étapes faciles', 'fr', 1952, 2000),
  ('F025','Eh bien, il faut faire comme je fais', 'fr', 1950, 2000),
  ('F026','C est-à-dire des choses assez farfelues', 'fr', 1959, 2000),
  ('F027','Parce que je suis clairement en manque d idée', 'fr', 1960, 2000), --Les deux dernières ne devraient pas y être
  ('F028','Et que c est tout ce qui me vient en tête', 'fr', 1949, 2000);

/*******************************************************************************************************************************/

--Tests pour la requête no3:

--Ici, je n'insère rien, car maintenant que j'ai inséré 5 films dans la décennie 1950, cela devrait être
--celle-ci qui comporte le plus de films. (Ça fonctionne).

/*******************************************************************************************************************************/

--Tests pour la requête no4:

INSERT INTO Artisan (idArtisan, nom, prenom, sexe) VALUES --Insertion d'artisans qui me seront utile pour le reste du fichier
 ('A100', 'Lavoie', 'Luc', 'M'),
 ('A102', 'Vaugeois', 'Frédéric', 'M'),
 ('A103', 'Khoa', 'Patrick', 'M'),
 ('A104', 'Khnaisser', 'Christina', 'M'),
 ('A105', 'Codd', 'Edgar Frank', 'M'),
 ('A106', 'Graton', 'Elvis', 'M'),
 ('A107', 'Presley', 'Elvis', 'M');

INSERT INTO Participation (idFilm, idArtisan, idPoste) VALUES --Insertion d'artisans ayant participé à des films avec des postes qui étaient
                                  --initialement inoccupés selon vos propres insertions (P001,P004 et P007 à P016)
                                  --en ne laissant que P016 (doubleur) d'inoccupé, qui devrait être le seul affiché.
                                  --(Ça fonctionne).
 ('F021','A100','P001'),
 ('F022','A100','P004'),
 ('F023','A100','P007'),
 ('F024','A100','P008'),
 ('F025','A100','P009'),
 ('F026','A100','P010'),
 ('F027','A100','P011'),
 ('F028','A100','P012'),
 ('F021','A100','P013'),
 ('F022','A100','P014'),
 ('F023','A100','P015');

/*******************************************************************************************************************************/

--Tests pour la requête no5:

INSERT INTO Participation (idFilm, idArtisan, idPoste) VALUES --Insertions de plusieurs Artisans dans participations qui testeront toutes
                                  --les variantes de notre requête. (Ça fonctionne).
 ('F021','A102','P000'),
 ('F021','A102','P003'),
 ('F022','A102','P003'), --Ici, comme cet homme a été réalisateur-producteur une fois, mais qu'il n'a pas toujours produit ses films, il
                     --Ne devrait pas apparaître.
 ('F024','A103','P000'),
 ('F025','A103','P000'), --Ici, comme cet homme n'a jamais été réalisateur, il ne devrait pas apparaître même s'il a toujours produit ses films.

 ('F026','A104','P000'),
 ('F027','A104','P003'),
 ('F027','A104','P000'),
 ('F028','A104','P003'), --Ici, comme cette femme a été réalisatrice-productrice une fois et qu'il a produit au moins un autre film, elle
             --n'a pas produit TOUS ses films, donc elle ne devrait pas apparaître.
 ('F023','A105','P000'),
 ('F023','A105','P003'),
 ('F024','A105','P000'),
 ('F024','A105','P001'),
 ('F025','A105','P000'),
 ('F025','A105','P001'),
 ('F026','A105','P001'),
 ('F026','A105','P000'); --Ici, comme cet homme a été réalisateur-producteur une fois et qu'il a produit tous ses films, il devrait apparaître.

/*******************************************************************************************************************************/

--Tests pour la requête no6:

--Ici, je n'insère rien non plus, car avec mes dernières insertion pour la requête no5, on ne devrait pas voir apparaître 'A102' car il n'a eu
--que deux postes sur un seul des deux films dans lequel il a joué. On ne devrait pas voir 'A103', car il n'a jamais eu deux postes dans les films
--auxquels il a participé. On ne devrait pas voir 'A104', car elle n'a eu deux postes que sur un film sur les trois auxquels elle a participé.
--Finalement, on devrait voir apparaître 'A105', car il a toujours eu deux postes dans les films pour lesquels il a joué. (Ça fonctionne).
/*
-- =========================================================================== Z
Contributeurs :
  (CK) Christina.Khnaisser@USherbrooke.ca,
  (LL) Luc.Lavoie@USherbrooke.ca

Adresse, droits d'auteur et copyright :
  Groupe Metis
  Département d'informatique
  Faculté des sciences
  Université de Sherbrooke
  Sherbrooke (Québec)  J1K 2R1
  Canada
  http://info.usherbrooke.ca/llavoie/
  [CC-BY-NC-4.0 (http://creativecommons.org/licenses/by-nc/4.0)]

Tâches projetées :
  NIL

Tâches réalisées :
  2016-09-25 (LL) : Création

Références :
[ddv] http://info.usherbrooke.ca/llavoie/enseignement/Exemples/Films

-- -----------------------------------------------------------------------------
-- fin de Films_ess03.sql
-- =========================================================================== Z
*/

/*
-- =========================================================================== A
Activité : IFT187
Trimestre : 2016-3
Encodage : UTF-8, sans BOM; fin de ligne Unix (LF)
Plateforme : PostgreSQL 9.5.1
Responsable : Luc.Lavoie@USherbrooke.ca
Version : 0.1.0b
Statut : en vigueur
Résumé : Initialisation des tables du schéma.
-- =========================================================================== A
*/
/*
-- =========================================================================== B
Initialisation du schéma correspondant au problème de boutique de films en ligne.
...

Notes de mise en oeuvre
L'initialisation des tables Pays et Langue repose sur la dispoibilité des tables
du schéma ISO. Pour le moment elles ont été crées dans le schéma Film, pour
simplifier la référence.
-- =========================================================================== B
*/

/**
 * Un pays est identifié par le code ISO 3166-1 "idPays" et son nom français est "pays".
 * Le code a deux lettres a été retenu (il existe des codes à trois lettres).
 * Par convention, les codes de pays sont en lettres majuscules.
 * Source : http://www.iso.org/iso/fr/french_country_names_and_code_elements#y.
 */
INSERT INTO Pays (idPays, pays)
  SELECT UPPER(code2) AS idPays, fr AS pays FROM ISO_3166_1;

/**
 * La langue identifiée par le code ISO 639-1 "idLangue" porte le nom  français "langue".
 * Le code a deux lettres a été retenu (il existe des codes à trois lettres).
 * Par convention, les codes de langue sont en lettres minuscules.
 * Source : https://www.loc.gov/standards/iso639-2/php/code_list.php
 * Note : certaines descriptions de langue (ou de groupe linguistique) sont
 *        parfois très longues; pour le vérifier :
 *        SELECT code3, fr, length(fr) FROM ISO_639_3 WHERE length(fr) > 60;
 */
INSERT INTO Langue (idLangue, langue)
  SELECT LOWER(code2) AS idLangue, fr AS langue FROM ISO_639_2 JOIN ISO_639_3 USING (code3);

/**
 * Poste
 */
INSERT INTO Poste(idPoste, poste) VALUES('P000','Producteur');
INSERT INTO Poste(idPoste, poste) VALUES('P001','Assistant producteur');
INSERT INTO Poste(idPoste, poste) VALUES('P002','Scénariste');
INSERT INTO Poste(idPoste, poste) VALUES('P003','Réalisateur');
INSERT INTO Poste(idPoste, poste) VALUES('P004','Assistant réalisateur');
INSERT INTO Poste(idPoste, poste) VALUES('P005','Acteur');
INSERT INTO Poste(idPoste, poste) VALUES('P006','Monteur');
INSERT INTO Poste(idPoste, poste) VALUES('P007','Cameraman');
INSERT INTO Poste(idPoste, poste) VALUES('P008','Preneur de son');
INSERT INTO Poste(idPoste, poste) VALUES('P009','Chef décorateur');
INSERT INTO Poste(idPoste, poste) VALUES('P010','Maquilleur');
INSERT INTO Poste(idPoste, poste) VALUES('P011','Costumier');
INSERT INTO Poste(idPoste, poste) VALUES('P012','Directeur technique');
INSERT INTO Poste(idPoste, poste) VALUES('P013','Cascadeur');
INSERT INTO Poste(idPoste, poste) VALUES('P014','Auteur de doublage');
INSERT INTO Poste(idPoste, poste) VALUES('P015','Auteur de sous-titrage');
INSERT INTO Poste(idPoste, poste) VALUES('P016','Doubleur');
INSERT INTO Poste(idPoste, poste) VALUES('P017','Compositeur');

/**
 * Genre
 */
INSERT INTO Genre(idGenre, genre) VALUES('G000','Action');
INSERT INTO Genre(idGenre, genre) VALUES('G001','Animation');
INSERT INTO Genre(idGenre, genre) VALUES('G002','Aventure');
INSERT INTO Genre(idGenre, genre) VALUES('G003','Catastrophe');
INSERT INTO Genre(idGenre, genre) VALUES('G004','Comédie');
INSERT INTO Genre(idGenre, genre) VALUES('G005','Danse');
INSERT INTO Genre(idGenre, genre) VALUES('G006','Documentaire');
INSERT INTO Genre(idGenre, genre) VALUES('G007','Dramatique');
INSERT INTO Genre(idGenre, genre) VALUES('G008','Espionnage');
INSERT INTO Genre(idGenre, genre) VALUES('G009','Guerre');
INSERT INTO Genre(idGenre, genre) VALUES('G010','Historique');
INSERT INTO Genre(idGenre, genre) VALUES('G011','Horreur');
INSERT INTO Genre(idGenre, genre) VALUES('G012','Comédie musicale');
INSERT INTO Genre(idGenre, genre) VALUES('G013','Mystère');
INSERT INTO Genre(idGenre, genre) VALUES('G014','Policier');
INSERT INTO Genre(idGenre, genre) VALUES('G015','Politique');
INSERT INTO Genre(idGenre, genre) VALUES('G016','Romantique');
INSERT INTO Genre(idGenre, genre) VALUES('G017','Science-fiction');
INSERT INTO Genre(idGenre, genre) VALUES('G018','Western');
INSERT INTO Genre(idGenre, genre) VALUES('G019','Suspense');
INSERT INTO Genre(idGenre, genre) VALUES('G020','Thriller');
INSERT INTO Genre(idGenre, genre) VALUES('G021','Fantastique');

/**
 * Studio
 */
INSERT INTO Studio(idStudio, studio, localisation) VALUES
 ('S000', 'LGM Productions', 'FR'),
 ('S001', 'Gaumont', 'FR'),
 ('S002', 'Walt Disney Pictures', 'US'),
 ('S003', 'Walt Disney Animation', 'US'),
 ('S004', 'Malposo Productions', 'US'),
 ('S005', 'Village Roadshow Pictures', 'US'),
 ('S006', 'Cinecittà', 'IT'),
 ('S007', 'Pathé Consortium Cinéma', 'FR'),
 ('S008', 'Universal', 'US'),
 ('S009', 'United Artists', 'US'),
 ('S010', 'Pathé', 'FR');

/**
 * Insertion des informations d'un artisan
 * Artisan, Naissance (si connue), Deces (s'il y a lieu) et nationnalité
 */
 -- Régis Wargnier
INSERT INTO Artisan(idArtisan, nom, prenom, sexe) VALUES
 ('A000', 'Wargnier', 'Régis', 'M');
INSERT INTO Naissance(idArtisan, naissance) VALUES
 ('A000', '1948-04-18');
INSERT INTO nationalite(idartisan, nationalite) VALUES
 ('A000', 'FR');
-- José Garcia
INSERT INTO Artisan(idArtisan, nom, prenom, sexe) VALUES
 ('A001', 'Garcia', 'José', 'M');
INSERT INTO Naissance(idArtisan, naissance) VALUES
 ('A001', '1966-03-17');
INSERT INTO nationalite(idartisan, nationalite) VALUES
 ('A001', 'FR'),
 ('A001', 'ES');
INSERT INTO Artisan(idArtisan, nom, prenom, sexe) VALUES
 ('A101', 'Garcia', 'José', 'M');
INSERT INTO Naissance(idArtisan, naissance) VALUES
 ('A101', '1946-09-01');
INSERT INTO nationalite(idartisan, nationalite) VALUES
 ('A101', 'AR');

-- Lucas Belvaux
INSERT INTO Artisan(idArtisan, nom, prenom, sexe) VALUES
 ('A002', 'Belvaux', 'Lucas', 'M');
INSERT INTO Naissance(idArtisan, naissance) VALUES
 ('A002', '1961-11-14');
INSERT INTO nationalite(idartisan, nationalite) VALUES
 ('A002', 'BE');
 -- Maris Gillian
INSERT INTO Artisan(idArtisan, nom, prenom, sexe) VALUES
 ('A003', 'Gillain', 'Marie', 'F');
INSERT INTO Naissance(idArtisan, naissance) VALUES
 ('A003', '1975-06-18');
INSERT INTO nationalite(idartisan, nationalite) VALUES
 ('A003', 'BE');
-- Michel Serrault
INSERT INTO Artisan(idArtisan, nom, prenom, sexe) VALUES
 ('A004', 'Serrault', 'Michel', 'M');
INSERT INTO Naissance(idArtisan, naissance) VALUES
 ('A004', '1928-01-24');
INSERT INTO Deces(idArtisan, deces) VALUES
 ('A004', '2007-08-17');
INSERT INTO nationalite(idartisan, nationalite) VALUES
 ('A004', 'FR');
-- Yann Malcore
INSERT INTO Artisan(idArtisan, nom, prenom, sexe) VALUES
 ('A005', 'Malcor', 'Yann', 'M');
-- Cyril Colbeau-Justin,
INSERT INTO Artisan(idArtisan, nom, prenom, sexe) VALUES
 ('A006', 'Colbeau-Justin', 'Cyril', 'M');
INSERT INTO nationalite(idartisan, nationalite) VALUES
 ('A006', 'FR');
-- Jean-Baptiste Dupont
INSERT INTO Artisan(idArtisan, nom, prenom, sexe) VALUES
 ('A007', 'Dupont', 'Jean-Baptiste', 'M');
INSERT INTO nationalite(idartisan, nationalite) VALUES
 ('A007', 'FR');
-- Patrick Doyle
INSERT INTO Artisan(idArtisan, nom, prenom, sexe) VALUES
 ('A008', 'Doyle', 'Patrick', 'M');
INSERT INTO Naissance(idArtisan, naissance) VALUES
 ('A008', '1953-04-06');
INSERT INTO nationalite(idartisan, nationalite) VALUES
 ('A008', 'GB');
-- Chris Buck
INSERT INTO Artisan(idArtisan, nom, prenom, sexe) VALUES
 ('A009', 'Buck', 'Chris', 'M');
INSERT INTO Naissance(idArtisan, naissance) VALUES
 ('A009', '1960-01-01');
INSERT INTO nationalite(idartisan, nationalite) VALUES
 ('A009', 'US');
INSERT INTO Artisan(idArtisan, nom, prenom, sexe) VALUES
 ('A109', 'Buck', 'Chris', 'M');
INSERT INTO Deces(idArtisan, deces) VALUES
 ('A109', '1920-12-01');
INSERT INTO nationalite(idartisan, nationalite) VALUES
 ('A109', 'AU');
-- Jennifer Lee
INSERT INTO Artisan(idArtisan, nom, prenom, sexe) VALUES
 ('A010', 'Lee', 'Jennifer', 'F');
INSERT INTO Naissance(idArtisan, naissance) VALUES
 ('A010', '1971-01-01');
INSERT INTO nationalite(idartisan, nationalite) VALUES
 ('A010', 'US');
-- Peter Del Vecho
INSERT INTO Artisan(idArtisan, nom, prenom, sexe) VALUES
 ('A011', 'Del Vecho', 'Peter', 'M');
INSERT INTO Naissance(idArtisan, naissance) VALUES
 ('A011', '1958-04-06');
INSERT INTO nationalite(idartisan, nationalite) VALUES
 ('A011', 'US');
-- Clint Eastwood
INSERT INTO Artisan(idArtisan, nom, prenom, sexe) VALUES
 ('A012', 'Eastwood', 'Clint', 'M');
INSERT INTO Naissance(idArtisan, naissance) VALUES
 ('A012', '1930-04-30');
INSERT INTO nationalite(idartisan, nationalite) VALUES
 ('A012', 'US');
-- Andrew Lazar
INSERT INTO Artisan(idArtisan, nom, prenom, sexe) VALUES
 ('A013', 'Lazar', 'Andrew', 'M');
INSERT INTO nationalite(idartisan, nationalite) VALUES
 ('A013', 'US');
-- Ken Kaufman
INSERT INTO Artisan(idArtisan, nom, prenom, sexe) VALUES
 ('A014', 'Kaufman', 'Ken', 'F');
-- Haword Klausner
INSERT INTO Artisan(idArtisan, nom, prenom, sexe) VALUES
 ('A015', 'Klausner', 'Haword', 'M');
-- Tommy Lee Jones
INSERT INTO Artisan(idArtisan, nom, prenom, sexe) VALUES
 ('A016', 'Lee Jones', 'Tommy', 'M');
INSERT INTO Naissance(idArtisan, naissance) VALUES
 ('A016', '1946-09-15');
INSERT INTO nationalite(idartisan, nationalite) VALUES
 ('A016', 'US');
-- Donald Sutherland
INSERT INTO Artisan(idArtisan, nom, prenom, sexe) VALUES
 ('A017', 'Sutherland', 'Donald', 'M');
INSERT INTO Naissance(idArtisan, naissance) VALUES
 ('A017', '1935-07-17');
INSERT INTO nationalite(idartisan, nationalite) VALUES
 ('A017', 'CA');
-- James Garner
INSERT INTO Artisan(idArtisan, nom, prenom, sexe) VALUES
 ('A018', 'Garner', 'James', 'M');
INSERT INTO Naissance(idArtisan, naissance) VALUES
 ('A018', '1928-04-07');
INSERT INTO nationalite(idartisan, nationalite) VALUES
 ('A018', 'US');
-- Maria Gay Harden
INSERT INTO Artisan(idArtisan, nom, prenom, sexe) VALUES
 ('A019', 'Harden', 'Maria Gay', 'F');
INSERT INTO Naissance(idArtisan, naissance) VALUES
 ('A019', '1959-08-14');
INSERT INTO nationalite(idartisan, nationalite) VALUES
 ('A019', 'US');
-- Ingrid Bergman
INSERT INTO Artisan(idArtisan, nom, prenom, sexe) VALUES
 ('A020', 'Bergman', 'Ingrid', 'F');
INSERT INTO Naissance(idArtisan, naissance) VALUES
 ('A020', '1915-08-29');
INSERT INTO Deces(idArtisan, deces) VALUES
 ('A020', '1982-08-29');
INSERT INTO nationalite(idartisan, nationalite) VALUES
 ('A020', 'SE');
-- Marcello Mastroianni
INSERT INTO Artisan(idArtisan, nom, prenom, sexe) VALUES
 ('A021', 'Mastroianni', 'Marcello', 'M');
INSERT INTO Naissance(idArtisan, naissance) VALUES
 ('A021', '1924-09-28');
INSERT INTO Deces(idArtisan, deces) VALUES
 ('A021', '1996-12-19');
INSERT INTO nationalite(idartisan, nationalite) VALUES
 ('A021', 'IT');

-- Charlie Chaplin
INSERT INTO Artisan(idArtisan, nom, prenom, sexe) VALUES
 ('A022', 'Chaplin', 'Charlie', 'M');
INSERT INTO Naissance(idArtisan, naissance) VALUES
 ('A022', '1889-04-16');
INSERT INTO Deces(idArtisan, deces) VALUES
 ('A022', '1977-12-25');
INSERT INTO nationalite(idartisan, nationalite) VALUES
 ('A022', 'GB');

-- Jackie Coogan
INSERT INTO Artisan(idArtisan, nom, prenom, sexe) VALUES
 ('A023', 'Coogan', 'Jackie', 'M');
INSERT INTO Naissance(idArtisan, naissance) VALUES
 ('A023', '1914-10-26');
INSERT INTO Deces(idArtisan, deces) VALUES
 ('A023', '1984-03-01');
INSERT INTO nationalite(idartisan, nationalite) VALUES
 ('A023', 'US');

-- Georgia Hale
INSERT INTO Artisan(idArtisan, nom, prenom, sexe) VALUES
 ('A024', 'Hale', 'Georgia', 'F');
INSERT INTO Naissance(idArtisan, naissance) VALUES
 ('A024', '1905-06-24');
INSERT INTO Deces(idArtisan, deces) VALUES
 ('A024', '1985-06-07');
INSERT INTO nationalite(idartisan, nationalite) VALUES
 ('A024', 'US');

-- Moroni "Mack" Swain
INSERT INTO Artisan(idArtisan, nom, prenom, sexe) VALUES
 ('A025', 'Swain', 'Moroni', 'M');
INSERT INTO Naissance(idArtisan, naissance) VALUES
 ('A025', '1876-02-16');
INSERT INTO Deces(idArtisan, deces) VALUES
 ('A025', '1935-08-25');
INSERT INTO nationalite(idartisan, nationalite) VALUES
 ('A025', 'US');

-- Tom Murray
INSERT INTO Artisan(idArtisan, nom, prenom, sexe) VALUES
 ('A026', 'Murray', 'Tom', 'M');
INSERT INTO Naissance(idArtisan, naissance) VALUES
 ('A026', '1874-09-08');
INSERT INTO Deces(idArtisan, deces) VALUES
 ('A026', '1935-08-27');
INSERT INTO nationalite(idartisan, nationalite) VALUES
 ('A026', 'US');

-- Paulette Goddard
INSERT INTO Artisan(idArtisan, nom, prenom, sexe) VALUES
 ('A027', 'Goddard', 'Marion Pauline "Paulette"', 'F');
INSERT INTO Naissance(idArtisan, naissance) VALUES
 ('A027', '1910-06-10');
INSERT INTO Deces(idArtisan, deces) VALUES
 ('A027', '1990-04-23');
INSERT INTO nationalite(idartisan, nationalite) VALUES
 ('A027', 'US');


/**
 * Insertion des informations d'un film
 * Film, Versiondisponible, FilmGenre, Production, Participation, recette
 */
 -- Film Pars vite et reviens tard
INSERT INTO Film(idFilm, titre, vo, parution, duree) VALUES
  ('F000', 'Pars vite et reviens tard', 'fr', 2007, 4500);
INSERT INTO Versiondisponible(idFilm, idLangue, mode) VALUES
  ('F000', 'en', 'S'),
  ('F000', 'es', 'S');
INSERT INTO FilmGenre(idFilm, idGenre) VALUES
  ('F000', 'G014'),
  ('F000', 'G020');
INSERT INTO Production(idFilm, idStudio) VALUES
  ('F000', 'S000'),
  ('F000', 'S001');
INSERT INTO Participation(idArtisan, idPoste, idFilm) VALUES
  ('A000', 'P002', 'F000'),
  ('A000', 'P003', 'F000'),
  ('A001', 'P005', 'F000'),
  ('A002', 'P005', 'F000'),
  ('A003', 'P005', 'F000'),
  ('A004', 'P005', 'F000'),
  ('A005', 'P006', 'F000'),
  ('A006', 'P000', 'F000'),
  ('A007', 'P000', 'F000'),
  ('A008', 'P017', 'F000');
--Recette introuvable.

-- Film Frozen SAUF les acteurs.
 INSERT INTO Film(idFilm, titre, vo, parution, duree) VALUES
  ('F001', 'Frozen', 'en', 2013, 4800);
INSERT INTO Versiondisponible(idFilm, idLangue, mode) VALUES
  ('F001', 'fr', 'S'),
  ('F001', 'fr', 'D'),
  ('F001', 'es', 'S'),
  ('F001', 'es', 'D');
INSERT INTO FilmGenre(idFilm, idGenre) VALUES
  ('F001', 'G001'),
  ('F001', 'G002'),
  ('F001', 'G004'),
  ('F001', 'G021');
INSERT INTO Production(idFilm, idStudio) VALUES
  ('F001', 'S002'),
  ('F001', 'S003');
INSERT INTO Participation(idFilm, idArtisan, idPoste) VALUES
  ('F001', 'A009', 'P000'),
  ('F001', 'A009', 'P003'),
  ('F001', 'A010', 'P003'),
  ('F001', 'A009', 'P002'),
  ('F001', 'A010', 'P002'),
  ('F001', 'A011', 'P000');
INSERT INTO Recette(idFilm, annee, revenu)
  VALUES ('F001', 2013, 67391326);

 -- Film Space Cowboys
 INSERT INTO Film(idFilm, titre, vo, parution, duree) VALUES
  ('F002', 'Space Cowboys', 'en', 2000, 5400);
INSERT INTO FilmGenre(idFilm, idGenre) VALUES
  ('F002', 'G003'),
  ('F002', 'G007');
INSERT INTO Production(idFilm, idStudio) VALUES
  ('F002', 'S004'),
  ('F002', 'S005');
INSERT INTO Participation(idFilm, idArtisan, idPoste) VALUES
  ('F002', 'A012', 'P000'),
  ('F002', 'A012', 'P003'),
  ('F002', 'A012', 'P005'),
  ('F002', 'A013', 'P000'),
  ('F002', 'A014', 'P002'),
  ('F002', 'A015', 'P002'),
  ('F002', 'A016', 'P005'),
  ('F002', 'A017', 'P005'),
  ('F002', 'A018', 'P005'),
  ('F002', 'A019', 'P005');
INSERT INTO Recette(idFilm, annee, revenu)
  VALUES ('F002', 2000, 18093776);

-- Le Kid
INSERT INTO Film(idFilm, titre, vo, parution, duree) VALUES
 ('F003', 'The Kid', 'en', 1921, 68*60);
INSERT INTO FilmGenre(idFilm, idGenre) VALUES
 ('F003', 'G004'),
 ('F003', 'G007');
INSERT INTO Production(idFilm, idStudio) VALUES
 ('F003', 'S009');        -- United Artists
INSERT INTO Participation(idFilm, idArtisan, idPoste) VALUES
 ('F003', 'A022', 'P000'), -- Chaplin : réalisateur, scénariste, producteur, acteur, compositeur...
 ('F003', 'A022', 'P002'),
 ('F003', 'A022', 'P003'),
 ('F003', 'A022', 'P005'),
 ('F003', 'A022', 'P017'),
 ('F003', 'A023', 'P005'); -- Coogan : acteur

-- La ruée vers l'or
INSERT INTO Film(idFilm, titre, vo, parution, duree) VALUES
 ('F004', 'The Gold Rush', 'en', 1925, 82*60);
INSERT INTO FilmGenre(idFilm, idGenre) VALUES
 ('F004', 'G004'),
 ('F004', 'G007');
INSERT INTO Production(idFilm, idStudio) VALUES
 ('F004', 'S009');        -- United Artists
INSERT INTO Participation(idFilm, idArtisan, idPoste) VALUES
 ('F004', 'A022', 'P000'), -- Chaplin : réalisateur, scénariste, producteur, acteur, compositeur...
 ('F004', 'A022', 'P002'),
 ('F004', 'A022', 'P003'),
 ('F004', 'A022', 'P005'),
 ('F004', 'A022', 'P017'),
 ('F004', 'A024', 'P005'), -- Gorgia Hale
 ('F004', 'A025', 'P005'), -- Mack Swain
 ('F004', 'A026', 'P005'); -- Tom Murray

-- L'opinion publique
INSERT INTO Film(idFilm, titre, vo, parution, duree) VALUES
 ('F005', 'A Woman of Paris', 'en', 1923, 93*60);
INSERT INTO FilmGenre(idFilm, idGenre) VALUES
 ('F005', 'G007');
INSERT INTO Production(idFilm, idStudio) VALUES
 ('F005', 'S009');        -- United Artists
INSERT INTO Participation(idFilm, idArtisan, idPoste) VALUES
 ('F005', 'A022', 'P000'), -- Chaplin : réalisateur, scénariste, producteur.
 ('F005', 'A022', 'P002'),
 ('F005', 'A022', 'P003');
 -- Edna Purviance : Marie Saint Clair
 -- Clarence Geldart : le père de Marie
 -- Carl Miller : Jean Millet
 -- Lydia Knott : la mère de Jean
 -- Charles K. French : le père de Jean
 -- Adolphe Menjou : Pierre Revel
 -- Betty Morrissey : Fifi
 -- Malvina Polo : Paulette
 -- Harry Northrup (non crédité) : le valet de Revel

-- Les Temps modernes
INSERT INTO Film(idFilm, titre, vo, parution, duree) VALUES
 ('F006', 'The Gold Rush', 'en', 1936, 87*60);
INSERT INTO FilmGenre(idFilm, idGenre) VALUES
 ('F006', 'G004'),
 ('F006', 'G007');
INSERT INTO Production(idFilm, idStudio) VALUES
 ('F006', 'S009');        -- United Artists
INSERT INTO Participation(idFilm, idArtisan, idPoste) VALUES
 ('F006', 'A022', 'P000'), -- Chaplin : réalisateur, scénariste, producteur, acteur, compositeur...
 ('F006', 'A022', 'P002'),
 ('F006', 'A022', 'P003'),
 ('F006', 'A022', 'P005'),
 ('F006', 'A022', 'P017'),
 ('F006', 'A027', 'P005'); -- Paulette Goddard


-- Film La Dolce Vita...

/*
-- =========================================================================== Z
Contributeurs :
  (CK) Christina.Khnaisser@USherbrooke.ca,
  (LL) Luc.Lavoie@USherbrooke.ca

Adresse, droits d'auteur et copyright :
  Groupe Metis
  Département d'informatique
  Faculté des sciences
  Université de Sherbrooke
  Sherbrooke (Québec)  J1K 2R1
  Canada
  http://info.usherbrooke.ca/llavoie/
  [CC-BY-NC-4.0 (http://creativecommons.org/licenses/by-nc/4.0)]

Tâches projetées :
  NIL

Tâches réalisées :
2016-09-16 (LL) : Création

Références :
[film] http://info.usherbrooke.ca/llavoie/enseignement/Exemples/Films
[ISO] http://info.usherbrooke.ca/llavoie/enseignement/Exemples/ISO

-- -----------------------------------------------------------------------------
-- fin de Exemples/Films/Films_ess02.sql
-- =========================================================================== Z
*/

/*
-- =========================================================================== A
Activité : IFT187
Trimestre : 2016-3
Encodage : UTF-8, sans BOM; fin de ligne Unix (LF)
Plateforme : PostgreSQL 9.5.1
Responsable : Luc.Lavoie@USherbrooke.ca
Version : 0.1.0b
Statut : en vigueur
Résumé : Requête X01
-- =========================================================================== A
*/
/*
-- =========================================================================== B
Notes de mise en oeuvre
~~~~~~~~~~~~~~~~~~~~~~~
...

-- =========================================================================== B
*/

/**
 * X01.
 * Calculer le nombre d’artisans par film.
 * Donner la clé du film, le titre du film et le nombre d’artisans. Trier en ordre de titre.
**/

WITH
    GroupementArtisansFilms AS --Nombre d'artisans par films
    (
    SELECT DISTINCT  idFilm, COUNT (idArtisan) AS NombreArtisansFilms
    FROM Artisan JOIN Participation USING (idArtisan)
    GROUP BY idFilm
    )
SELECT DISTINCT idFilm, titre, NombreArtisansFilms --Agencement pour la beauté et pour répondre à la question
FROM Film JOIN GroupementArtisansFilms USING (idFilm)
ORDER BY titre
;



/*
-- =========================================================================== Z
Contributeurs :
  (CK) Christina.Khnaisser@USherbrooke.ca,
  (LL) Luc.Lavoie@USherbrooke.ca

Adresse, droits d'auteur et copyright :
  Groupe Metis
  Département d'informatique
  Faculté des sciences
  Université de Sherbrooke
  Sherbrooke (Québec)  J1K 2R1
  Canada
  http://info.usherbrooke.ca/llavoie/
  [CC-BY-NC-4.0 (http://creativecommons.org/licenses/by-nc/4.0)]

Tâches projetées :
  NIL

Tâches réalisées :
  2016-09-25 (LL) : Création

Références :
[ddv] http://info.usherbrooke.ca/llavoie/enseignement/Exemples/Films

-- -----------------------------------------------------------------------------
-- fin de Films_X01.sql
-- =========================================================================== Z
*/

/*
-- =========================================================================== A
Activité : IFT187
Trimestre : 2016-3
Encodage : UTF-8, sans BOM; fin de ligne Unix (LF)
Plateforme : PostgreSQL 9.5.1
Responsable : Luc.Lavoie@USherbrooke.ca
Version : 0.1.0b
Statut : en vigueur
Résumé : Requête X02
-- =========================================================================== A
*/
/*
-- =========================================================================== B
Notes de mise en oeuvre
~~~~~~~~~~~~~~~~~~~~~~~
...

-- =========================================================================== B
*/

/**
 * X02.
 * Sur la base de la date de parution, calculer le tableau du nombre de films par décennie.
 * Présenter le résultat de façon appropriée.
**/

WITH
    CalculerDecennie(idFilm, Decennie) AS --Sert à changer l'annee de tous les films sortis dans une décennie
                          -- par une decennie (Ex. 1993 devient 1990).
    (
    SELECT DISTINCT idFilm, SUBSTRING (CAST (parution AS VARCHAR(4)),1,3) || '0' AS Decennie
    FROM Film
    )
SELECT Decennie, COUNT(*) AS NombreDeFilm --Nombre de films par décennie
FROM CalculerDecennie
GROUP BY Decennie
ORDER BY Decennie ASC   --Arrangement pour la beauté et pour répondre à la question
;


/*
-- =========================================================================== Z
Contributeurs :
  (CK) Christina.Khnaisser@USherbrooke.ca,
  (LL) Luc.Lavoie@USherbrooke.ca

Adresse, droits d'auteur et copyright :
  Groupe Metis
  Département d'informatique
  Faculté des sciences
  Université de Sherbrooke
  Sherbrooke (Québec)  J1K 2R1
  Canada
  http://info.usherbrooke.ca/llavoie/
  [CC-BY-NC-4.0 (http://creativecommons.org/licenses/by-nc/4.0)]

Tâches projetées :
  NIL

Tâches réalisées :
  2016-09-25 (LL) : Création

Références :
[ddv] http://info.usherbrooke.ca/llavoie/enseignement/Exemples/Films

-- -----------------------------------------------------------------------------
-- fin de Films_X02.sql
-- =========================================================================== Z
*/

/*
-- =========================================================================== A
Activité : IFT187
Trimestre : 2016-3
Encodage : UTF-8, sans BOM; fin de ligne Unix (LF)
Plateforme : PostgreSQL 9.5.1
Responsable : Luc.Lavoie@USherbrooke.ca
Version : 0.1.0b
Statut : en vigueur
Résumé : Requête X03
-- =========================================================================== A
*/
/*
-- =========================================================================== B
Notes de mise en oeuvre
~~~~~~~~~~~~~~~~~~~~~~~
...

-- =========================================================================== B
*/

/**
 * X03.
 * Quelle est la décennie comportant le plus de films ?
 * Présenter le résultat de façon appropriée.
**/

WITH
    CalculerDecennie(idFilm, Decennie) AS --Sert à changer l'annee de tous les films sortis dans une décennie
                          -- par une decennie (Ex. 1993 devient 1990).
    (
    SELECT DISTINCT idFilm, SUBSTRING (CAST (parution AS VARCHAR(4)),1,3) || '0' AS Decennie
    FROM Film
    ),
    NombreFilmsDecennie AS --Compte le nombre de films par decennie
    (
    SELECT DISTINCT Decennie, COUNT (idFilm) AS NombreFilms
    FROM CalculerDecennie
    GROUP BY Decennie
    )

SELECT Decennie, NombreFilms
FROM NombreFilmsDecennie
WHERE NombreFilms = (SELECT MAX(NombreFilms) FROM NombreFilmsDecennie) --La decennie qui a eu le plus de films
;


/*
-- =========================================================================== Z
Contributeurs :
  (CK) Christina.Khnaisser@USherbrooke.ca,
  (LL) Luc.Lavoie@USherbrooke.ca

Adresse, droits d'auteur et copyright :
  Groupe Metis
  Département d'informatique
  Faculté des sciences
  Université de Sherbrooke
  Sherbrooke (Québec)  J1K 2R1
  Canada
  http://info.usherbrooke.ca/llavoie/
  [CC-BY-NC-4.0 (http://creativecommons.org/licenses/by-nc/4.0)]

Tâches projetées :
  NIL

Tâches réalisées :
  2016-09-25 (LL) : Création

Références :
[ddv] http://info.usherbrooke.ca/llavoie/enseignement/Exemples/Films

-- -----------------------------------------------------------------------------
-- fin de Films_X03.sql
-- =========================================================================== Z
*/

/*
-- =========================================================================== A
Activité : IFT187
Trimestre : 2016-3
Encodage : UTF-8, sans BOM; fin de ligne Unix (LF)
Plateforme : PostgreSQL 9.5.1
Responsable : Luc.Lavoie@USherbrooke.ca
Version : 0.1.0b
Statut : en vigueur
Résumé : Requête X04
-- =========================================================================== A
*/
/*
-- =========================================================================== B
Notes de mise en oeuvre
~~~~~~~~~~~~~~~~~~~~~~~
...

-- =========================================================================== B
*/

/**
 * X04.
 * Quels sont les postes qui n’ont jamais été utilisés ?
 * Présenter le résultat de façon appropriée.
 * Note: Nous ne pensons pas que cela vaille la peine de mettre
 * le nombre 0 dans une colonne nommée Nombre D'artisans. Ainsi,
 * nous mettons juste la clé ainsi que le poste.
**/

WITH
    PosteQuiOntArtisan AS --Tous les postes occupés
    (
    SELECT DISTINCT idArtisan, idPoste, poste
    FROM Participation JOIN Poste USING (idPoste)
    )

SELECT DISTINCT idPoste, poste --Tous les postes existants
FROM Poste
EXCEPT
SELECT DISTINCT idPoste, poste --Moins ceux qui ont été occupés au moins une fois
FROM PosteQuiOntArtisan
ORDER BY idPoste
;


/*
-- =========================================================================== Z
Contributeurs :
  (CK) Christina.Khnaisser@USherbrooke.ca,
  (LL) Luc.Lavoie@USherbrooke.ca

Adresse, droits d'auteur et copyright :
  Groupe Metis
  Département d'informatique
  Faculté des sciences
  Université de Sherbrooke
  Sherbrooke (Québec)  J1K 2R1
  Canada
  http://info.usherbrooke.ca/llavoie/
  [CC-BY-NC-4.0 (http://creativecommons.org/licenses/by-nc/4.0)]

Tâches projetées :
  NIL

Tâches réalisées :
  2016-09-25 (LL) : Création

Références :
[ddv] http://info.usherbrooke.ca/llavoie/enseignement/Exemples/Films

-- -----------------------------------------------------------------------------
-- fin de Films_X04.sql
-- =========================================================================== Z
*/

/*
-- =========================================================================== A
Activité : IFT187
Trimestre : 2016-3
Encodage : UTF-8, sans BOM; fin de ligne Unix (LF)
Plateforme : PostgreSQL 9.5.1
Responsable : Luc.Lavoie@USherbrooke.ca
Version : 0.1.0b
Statut : en vigueur
Résumé : Requête R06
-- =========================================================================== A
*/
/*
-- =========================================================================== B
Notes de mise en oeuvre
~~~~~~~~~~~~~~~~~~~~~~~
...

-- =========================================================================== B
*/

/**
 * X06.
 * Quels sont les artisans qui ont occupé aux moins deux postes dans tous les
 * films auxquels ils ont participé ?
 * Présenter le résultat de façon appropriée.
**/

WITH
    ArtisansFilms AS --Tous les artisans qui ont participé à un film
    (
    SELECT idArtisan, idFilm
    FROM Participation
    ),






/*
-- =========================================================================== Z
Contributeurs :
  (CK) Christina.Khnaisser@USherbrooke.ca,
  (LL) Luc.Lavoie@USherbrooke.ca

Adresse, droits d'auteur et copyright :
  Groupe Metis
  Département d'informatique
  Faculté des sciences
  Université de Sherbrooke
  Sherbrooke (Québec)  J1K 2R1
  Canada
  http://info.usherbrooke.ca/llavoie/
  [CC-BY-NC-4.0 (http://creativecommons.org/licenses/by-nc/4.0)]

Tâches projetées :
  NIL

Tâches réalisées :
  2016-09-25 (LL) : Création

Références :
[ddv] http://info.usherbrooke.ca/llavoie/enseignement/Exemples/Films

-- -----------------------------------------------------------------------------
-- fin de Films_X06.sql
-- =========================================================================== Z
*/

/*
-- =========================================================================== A
Activité : IFT187
Trimestre : 2016-3
Encodage : UTF-8, sans BOM; fin de ligne Unix (LF)
Plateforme : PostgreSQL 9.5.1
Responsable : Luc.Lavoie@USherbrooke.ca
Version : 0.1.0b
Statut : en vigueur
Résumé : Requête R07
-- =========================================================================== A
*/
/*
-- =========================================================================== B
Notes de mise en oeuvre
~~~~~~~~~~~~~~~~~~~~~~~
...

-- =========================================================================== B
*/

/**
 * X07.
 * Déterminer les films qui ont un nombre d’acteurs supérieur à la moyenne du nombre
 * d’acteurs des films de la même décennie.
 * Présenter le résultat de façon appropriée.
**/


WITH
    CalculerDecennie(idFilm, Decennie) AS --Sert à changer l'année de tous les films sortis dans une décennie
                          -- par une decennie (Ex. 1993 devient 1990).
    (
    SELECT DISTINCT idFilm, SUBSTRING (CAST (parution AS VARCHAR(4)),1,3) || '0' AS Decennie
    FROM Film
    GROUP BY idFilm
    ),
    NombreDartisanParFilm AS --Nombre d'acteurs par film
    (
    SELECT DISTINCT idFilm, COUNT (DISTINCT idArtisan)  AS NombreActeursParFilm
    FROM CalculerDecennie JOIN Participation USING (idFilm)
    WHERE (idPoste = 'P005')
    GROUP BY idFilm
    ),
    FilmsNbrActeursDecennie (idFilm, NombreActeursParFilm, Decennie) AS --Nombre d'acteurs par film, groupé selon la décennie
    (
    SELECT idFilm, NombreActeursParFilm, Decennie
    FROM CalculerDecennie JOIN NombreDartisanParFilm USING (idFilm)
    ),
    MoyenneActeursParFilmAvecDecennie AS --Moyenne du nombre d'acteurs par film selon chaque décennie
    (
    SELECT DISTINCT Decennie, AVG (NombreActeursParFilm) AS MoyenneActeursParFilm
    FROM FilmsNbrActeursDecennie
    GROUP BY Decennie
    )
SELECT DISTINCT idFilm, titre, NombreActeursParFilm, Decennie, trunc(MoyenneActeursParFilm,3) AS Moyenne --Arrangement pour la beauté et pour répondre à la question
FROM FilmsNbrActeursDecennie JOIN Film USING (idFilm)
                 JOIN MoyenneActeursParFilmAvecDecennie USING (Decennie)
WHERE (FilmsNbrActeursDecennie.Decennie = MoyenneActeursParFilmAvecDecennie.Decennie) --Tous les films dont la moyenne d'acteurs est supérieure à la moyenne d'acteurs
                                              -- d'acteurs par films de leur décennie
    AND (MoyenneActeursParFilmAvecDecennie.MoyenneActeursParFilm < FilmsNbrActeursDecennie.NombreActeursParFilm)
;


/*
-- =========================================================================== Z
Contributeurs :
  (CK) Christina.Khnaisser@USherbrooke.ca,
  (LL) Luc.Lavoie@USherbrooke.ca

Adresse, droits d'auteur et copyright :
  Groupe Metis
  Département d'informatique
  Faculté des sciences
  Université de Sherbrooke
  Sherbrooke (Québec)  J1K 2R1
  Canada
  http://info.usherbrooke.ca/llavoie/
  [CC-BY-NC-4.0 (http://creativecommons.org/licenses/by-nc/4.0)]

Tâches projetées :
  NIL

Tâches réalisées :
  2016-09-25 (LL) : Création

Références :
[ddv] http://info.usherbrooke.ca/llavoie/enseignement/Exemples/Films

-- -----------------------------------------------------------------------------
-- fin de Films_X07.sql
-- =========================================================================== Z
*/

/*
-- =========================================================================== A
Activité : IFT187
Trimestre : 2016-3
Encodage : UTF-8, sans BOM; fin de ligne Unix (LF)
Plateforme : PostgreSQL 9.5.1
Responsable : Luc.Lavoie@USherbrooke.ca
Version : 0.1.0b
Statut : en vigueur
Résumé : Requête R08
-- =========================================================================== A
*/
/*
-- =========================================================================== B
Notes de mise en oeuvre
~~~~~~~~~~~~~~~~~~~~~~~
...

-- =========================================================================== B
*/

/**
 * X08.
 * Quelles sont les trois paires d’acteurs ayant le plus souvent joué ensemble ?
 * Pour chacune des paires, donner le nombre d’occurrences.
**/

WITH
    ArtisansActeursFilms AS --Tous les acteurs avec leur film
    (
    SELECT idArtisan, idFilm
    FROM Participation
    WHERE idPoste = 'P005'
    ),
    PairesDacteurs AS --Toutes les paires d'acteurs avec le nombre de films dans lesquels
              -- ils ont joué ensemble.
    (
    SELECT A.idArtisan AS pA, B.idArtisan AS pB, COUNT (A.idFilm) AS nbrFilms
    FROM ArtisansActeursFilms AS A JOIN ArtisansActeursFilms AS B USING (idFilm)
    WHERE A.idArtisan < B.idArtisan --Évite les doublons (José Garcia a joué avec José Garcia est invalide)
    GROUP BY A.idArtisan, B.idArtisan
    )
SELECT (pA,pb) AS PaireiD, (A.nom, A.prenom) AS nom1_prenom1, (B.nom, B.prenom)AS nom2_prenom2, nbrFilms --Arrangement pour beauté
FROM PairesDacteurs JOIN Artisan AS A ON (pA = A.idArtisan)
            JOIN Artisan AS B ON (pB = B.idArtisan)
ORDER BY nbrFilms DESC --On met la paire d'acteurs en ordre décroissant de nombre de films en commun dans lesquels ils ont joué
FETCH FIRST 3 ROWS ONLY --On prends les trois premières tuples de cette table décroissante pour avoir les trois paires avec le plus
            -- de films dans lesquels ils ont joué ensemble.
;






/*
-- =========================================================================== Z
Contributeurs :
  (CK) Christina.Khnaisser@USherbrooke.ca,
  (LL) Luc.Lavoie@USherbrooke.ca

Adresse, droits d'auteur et copyright :
  Groupe Metis
  Département d'informatique
  Faculté des sciences
  Université de Sherbrooke
  Sherbrooke (Québec)  J1K 2R1
  Canada
  http://info.usherbrooke.ca/llavoie/
  [CC-BY-NC-4.0 (http://creativecommons.org/licenses/by-nc/4.0)]

Tâches projetées :
  NIL

Tâches réalisées :
  2016-09-25 (LL) : Création

Références :
[ddv] http://info.usherbrooke.ca/llavoie/enseignement/Exemples/Films

-- -----------------------------------------------------------------------------
-- fin de Films_X08.sql
-- =========================================================================== Z
*/