DROP TABLE IF EXISTS eleve;
DROP TABLE IF EXISTS prof;
DROP TABLE IF EXISTS structure;
DROP TABLE IF EXISTS maitre_stage;
DROP TABLE IF EXISTS stage;

CREATE TABLE eleve
(
	no_elv int(5) NOT NULL auto_increment,
	nom_elv varchar(30) NOT NULL,
	pnom_elv varchar(30) NOT NULL,
	motdepasse_elv varchar(40) NOT NULL,
	tel_elv varchar(15),
	email_elv varchar(40),
	PRIMARY KEY (no_elv)
);


CREATE TABLE prof
(
	id_prof int(4) NOT NULL auto_increment,
	nom_prof varchar(60) NOT NULL,
	email_prof varchar(40) NOT NULL,
	PRIMARY KEY (id_prof)
);


CREATE TABLE structure
(
	no_struc int(5) NOT NULL,
	nom_struc varchar(50) NOT NULL,
	adr_struc varchar(50) NOT NULL,
	cp_struc int(5) NOT NULL,
	ville_struc varchar(30) NOT NULL,
	notel_struc varchar(15),
	PRIMARY KEY (no_struc)
);


CREATE TABLE maitre_stage
(
	no_mstage int(4) NOT NULL,
	nom_mstage varchar(30) NOT NULL,
	pnom_mstage varchar(30),
	titre_mstage varchar(5) NOT NULL,
	email_mstage varchar(40),
	PRIMARY KEY (no_mstage)
);


CREATE TABLE stage
(	no_stage int(5) NOT NULL auto_increment,
	date_deb_stage date NOT NULL,
	date_fin_stage date NOT NULL,
	projet_stage tinytext NOT NULL,
	type_devlpt_stage tinytext NOT NULL,
	observation_stage tinytext,
	no_elv int(5) REFERENCES eleve(no_elv),
	no_struc int(5) REFERENCES structure(no_struc),
	id_prof int(4) REFERENCES prof(id_prof),
	no_mstage int(4) REFERENCES maitre_stage(no_mstage),
	PRIMARY KEY (no_stage)
);