table.sql

Create TABLE IF NOT EXISTS genre 
(
    idGen Int not null primary key, 
    nom varchar(20)
);

Create TABLE IF NOT EXISTS film 
(
    tconst varchar(20) not null primary key,
    primaryTitle varchar(100),
    isAdult int,
    startYear int,
    runtimeMinutes int,
    averageRating float,
    numVotes int,
    titleFR varchar(250)
);

Create TABLE IF NOT EXISTS Utilisateur
(
    login varchar(20) not null primary key,
    nom varchar(20) not null,
    prenom varchar(20) not null,
    mdp varchar(100) not null,
    dateNais date not null default (current_date)

);
Create TABLE IF NOT EXISTS Personne
(
    nconst varchar(20) not null,
    primaryName varchar(50),
    birthYear int,
    deathYear int,
    primaryProfession varchar(120),
    PRIMARY KEY (nconst)
);


Create TABLE if not EXISTS FilmVue
(
    login varchar(20) not null,
    tconst varchar(20) not null,
    note int default (3),
    FOREIGN KEY (login) REFERENCES Utilisateur(login),
    FOREIGN KEY (tconst) REFERENCES film(tconst), 
    PRIMARY KEY (login,tconst)
);

Create TABLE if not EXISTS GenreFilm
(
    tconst varchar(20) not null,
    idGen int not null,
    FOREIGN KEY (idGen) REFERENCES genre(idGen),
    FOREIGN KEY (tconst) REFERENCES film(tconst), 
    PRIMARY KEY (idGen,tconst)
);

Create TABLE if not EXISTS Roles
(
    tconst varchar(20) not null,
    nconst varchar(20) not null,
    roles varchar(30),
    characters varchar(150),
    FOREIGN KEY (nconst) REFERENCES Personne(nconst),
    FOREIGN KEY (tconst) REFERENCES film(tconst), 
    PRIMARY KEY (nconst,tconst)
);