-- CREATE DATABASE cinema;
USE cinema;

create table individu(
	num_ind int(8) primary key,
	nom varchar(30) NOT NULL,
	prenom varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

create table film(
	num_film int(8) primary key,
	num_ind  int(8) NOT NULL,
	titre varchar(60) NOT NULL,
	genre varchar(20) DEFAULT 'Drame',
	annee int(4),
	constraint fk_film foreign key (num_ind)references individu(num_ind)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

create table jouer(
	num_ind  int(8) not null ,
	num_film  int(8) not null,
	role varchar(30),
	constraint pk_jouer primary key (num_ind, num_film, role),
	constraint fk1_jouer foreign key (num_ind) references individu(num_ind),
	constraint fk2_jouer foreign key (num_film) references film(num_film)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

create table cinema(
	num_cine  int(8) primary key,
	nom varchar(30) NOT NULL,
	adresse varchar(50)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

create table projection(
	num_cine int(8) not null,
	num_film  int(8) not null,
	pdate date,
	constraint pk_projection primary key (num_cine,num_film,pdate),
	constraint fk1_projection foreign key (num_cine) references cinema(num_cine),
	constraint fk2_projection foreign key (num_film) references film(num_film)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
