-- \i GenBDCine.sql

DROP TABLE jouer CASCADE;
DROP TABLE projection CASCADE;
DROP TABLE film CASCADE;
DROP TABLE personne CASCADE;
DROP TABLE cinema CASCADE;

CREATE TABLE personne (
  id_personne INTEGER PRIMARY KEY,
  nom VARCHAR NOT NULL,
  prenom VARCHAR NOT NULL );

CREATE TABLE film (
  id_film INTEGER PRIMARY KEY,
  id_realisateur INTEGER REFERENCES personne ON DELETE SET NULL,
  titre VARCHAR NOT NULL,
  genre VARCHAR,
  annee_production INTEGER
    CHECK(annee_production > 0 OR annee_production IS NULL));

CREATE TABLE cinema (
  id_cinema INTEGER PRIMARY KEY,
  nom VARCHAR NOT NULL,
  adresse VARCHAR);

CREATE TABLE jouer (
  id_acteur INTEGER REFERENCES personne ON DELETE CASCADE,
  id_film INTEGER REFERENCES film ON DELETE CASCADE,
  role VARCHAR,
  PRIMARY KEY (id_acteur, id_film));

CREATE TABLE projection (
  id_cinema INTEGER REFERENCES cinema ON DELETE CASCADE,
  id_film INTEGER REFERENCES film ON DELETE CASCADE,
  date_projection DATE,
  PRIMARY KEY (id_cinema, id_film, date_projection));

SET datestyle='DMY';

\copy personne FROM personne.txt
\copy cinema FROM cinema.txt
\copy film FROM film.txt
\copy jouer FROM jouer.txt
\copy projection FROM projection.txt
