drop table performance; 
drop table concert;
drop table has_composed; 
drop table composition;
drop table plays_in;
drop table band;
drop table composer;
drop table performer; 
drop table musician;
drop table place; 

create table place
	(
	place_no		integer 		primary key,
	place_town		char(20),
	place_country	char(20)
	);

create table musician 
	(
	m_no 			integer 		primary key,
	m_name		char(20) not null,
	born		      date,
	died              date default null,
	born_in		integer 		references place (place_no),
	living_in		integer 		references place (place_no),
      check ((born IS NULL) OR 
             (TO_DATE ('1-Jan-1900', 'DD-Mon_YYYY') < born)),
	check ((born IS NULL) OR (died IS NULL) OR (died > born))
	);

create table performer
	(
	perf_no		integer		primary key,
	perf_is		integer not null	references musician (m_no),
	instrument		char(10) not null,
	perf_type		char(10)		default 'not known'
	);

create table composer
	(
	comp_no		integer 		primary key,
	comp_is		integer not null	references musician (m_no),
	comp_type		char(10)
	);

create table band
	(
	band_no		integer 		primary key,
	band_name		char(20),
	band_home		integer not null	references place (place_no),
	band_type		char(10),
	b_date	      date,
	band_contact	integer not null	references musician (m_no)
	);

create table plays_in
	(
	player		integer  		 references performer (perf_no),
	band_id		integer  		 references band (band_no),
	primary key ( player, band_id)
	);

create table composition
	(
	c_no			integer 		primary key,
	comp_date		date,
	c_title		char(40) not null,
	c_in			integer 		references place (place_no)
	);

create table has_composed
	(
	cmpr_no		integer 		references composer (comp_no),
	cmpn_no		integer 		references composition (c_no),
	primary key ( cmpr_no, cmpn_no )
	);

create table concert
	(
	concert_no	 	integer 		primary key,
	concert_venue	char(20),
	concert_in		integer not null	references place (place_no),
	con_date	      date,             
	concert_orgniser	integer 		references musician (m_no)
	);

create table performance
	(
	pfrmnc_no		integer 		primary key,
	gave			integer not null	references band (band_no),
	performed		integer not null 	references composition (c_no),
	conducted_by	integer 		references musician (m_no),
	performed_in	integer 		references concert (concert_no)
	);