DROP TABLE Fait_Partie;
DROP TABLE Orchestre;
DROP TABLE Representation;
DROP TABLE Musicien;
DROP TABLE Instrument;

create table Instrument(
	inst_id number,
	nom_inst varchar2(30),
	categorie varchar2(30),
    Constraint pk_i primary key(inst_id)
);
create table Musicien(
	mus_id number,
	nom_mus varchar2(30),
	prenom_mus varchar2(30),
	adresse varchar2(30),
	inst_id number,
	Constraint pk_m primary key(mus_id),
	Constraint fk_m foreign key (inst_id) references Instrument(inst_id)
);
create table Representation(
	rep_id number,
	lieu varchar2(30),
	ville varchar2(30),
	date_rep date,
	orch_id number,
	Constraint pk_r primary key(rep_id),
	Constraint fk_r foreign key (orch_id) references Musicien(mus_id)
);
create table Orchestre(
	orch_id number,
	nom_orch varchar2(30),
	nb_mus number,
	chef_id number,
	Constraint pk_o primary key(orch_id),
	Constraint fk_o foreign key (chef_id) references Musicien(mus_id)
);
create table Fait_Partie(
	orch_id number, 
	Constraint fk_f foreign key (orch_id)references Orchestre(orch_id),
	mus_id number,
	Constraint fk_f1 foreign key (mus_id) references Musicien(mus_id),
	Constraint pk_f2 primary key (orch_id,mus_id)
);