CREATE DATABASE videotheque;
use videotheque;
CREATE TABLE GENRE(
GEN_CODE TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
GEN_INT CHAR(20));

CREATE TABLE FILM (
FLM_CODE SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
FLM_TITRE VARCHAR(80) NOT NULL,
FLM_ANNEE YEAR  NULL,
FLM_DUREE INT NULL,
FLM_RESUME TEXT,
GEN_CODE TINYINT UNSIGNED,
FOREIGN KEY (GEN_CODE) REFERENCES GENRE(GEN_CODE) ON UPDATE CASCADE ON DELETE SET NULL,
INDEX(GEN_CODE));

CREATE TABLE ACTEUR(
ACT_CODE SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
ACT_NOM CHAR(25),
ACT_PRENOM CHAR(25));

CREATE TABLE SUPPORT(
SUP_CODE TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
SUP_INT CHAR(20));

CREATE TABLE EMPRUNTEUR(
PER_CODE SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
PER_NOM CHAR(25),
PER_PRENOM CHAR(25),
PER_ADR VARCHAR(50),
PER_TEL CHAR(14));

CREATE TABLE FILM_SUPPORT(
FLM_CODE SMALLINT UNSIGNED,
SUP_CODE TINYINT UNSIGNED,
FOREIGN KEY (FLM_CODE) REFERENCES FILM(FLM_CODE) ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY (SUP_CODE) REFERENCES SUPPORT(SUP_CODE) ON UPDATE CASCADE ON DELETE RESTRICT,
index(FLM_CODE,SUP_CODE),
PRIMARY KEY (FLM_CODE,SUP_CODE));

CREATE TABLE EMPRUNT(
FLM_CODE SMALLINT UNSIGNED,
PER_CODE SMALLINT UNSIGNED,
EMP_DEBUT DATE NOT NULL,
EMP_FIN DATE NULL,
FOREIGN KEY (FLM_CODE) REFERENCES FILM(FLM_CODE) ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY (PER_CODE) REFERENCES EMPRUNTEUR(PER_CODE) ON UPDATE CASCADE ON DELETE RESTRICT,
index(FLM_CODE,PER_CODE),
PRIMARY KEY (FLM_CODE,PER_CODE));

CREATE TABLE JOUER_UN_ROLE(
FLM_CODE SMALLINT UNSIGNED,
ACT_CODE SMALLINT UNSIGNED,
FOREIGN KEY (FLM_CODE) REFERENCES FILM(FLM_CODE) ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY (ACT_CODE) REFERENCES ACTEUR(ACT_CODE) ON UPDATE CASCADE ON DELETE RESTRICT,
index(FLM_CODE,ACT_CODE),
PRIMARY KEY (FLM_CODE,ACT_CODE));

ALTER TABLE ACTEUR MODIFY ACT_PRENOM NOT NULL;
ALTER TABLE ACTEUR ADD DATE_NAISSANCE DATE NULL;
ALTER TABLE FILM ADD INDEX (FLM_TITRE);

CREATE TABLE REALISATEUR(
REA_CODE SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
REA_NOM CHAR(25),
REA_PRENOM CHAR(25),
REA_SEXE ENUM("M","F") DEFAULT "M");

CREATE TABLE TOURNE(
FLM_CODE SMALLINT UNSIGNED,
REA_CODE SMALLINT UNSIGNED,
TOU_DATE YEAR,
FOREIGN KEY (FLM_CODE) REFERENCES FILM(FLM_CODE) ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY (REA_CODE) REFERENCES REALISATEUR(REA_CODE) ON UPDATE CASCADE ON DELETE RESTRICT,
index(FLM_CODE,REA_CODE),
PRIMARY KEY (FLM_CODE,REA_CODE));