-- Create fencing action database

create table Event (ID serial,
	name varchar(80),
	event_date date,
	event_weapon enum("Foil","Epee","Saber","Mixed"),
	event_sex enum("M","F","Mixed"),	
	event_age_id bigint,
	venue_id bigint,
	event_type_id bigint,
	point_format_id bigint);

create table Venue (ID serial,
	name varchar(80),
	city varchar(80),
	state varchar(80),
	country varchar(80));

create table Action (action_id bigint,
	arousal_id bigint,
    	zone_id bigint,
    	time_clock time,
    	touch enum("scored","lost","none"), 
    	context enum("offensive","defensive"), 
    	bout_id bigint);

create table Fencer (ID serial,
	name varchar(80),
	rating enum("A","B","C","D","E","U"),
	primary_weapon enum ("Foil","Epee","Saber","Mixed"),
	rating_year year,
	club varchar(80),
	coach varchar(80),
	year_started year,
	year_of_birth year);

create table Tournament (ID Serial, event_name varchar(80), 
    	venue_id bigint, 
    	tournament_type_id bigint, 
    	point_format_id bigint);

create table Bout (ID serial,
	bout_start datetime,
	fencer_right bigint,
	fencer_right_touches_scored int,
	fencer_right_touches_received int,
	fencer_left_id bigint,
	fencer_left_touches_scored int,
	fencer_left_touches_received int,
	won_fencer_id bigint,
	tournament_id bigint,
	touches_scored int,
	touches_received int,
	Pool_bout boolean,
	video_pointer varchar(80));

create table Club (ID serial,
	name varchar(80),
	street_address varchar(80),
	city varchar(80),
	state_province varchar(80),
	country varchar(80),
	year_started year,
	primary_coach bigint,
	primary_weapon enum("Foil","Epee","Saber","Mixed") );

create table Coach (ID serial,
	name varchar(80),
	primary_weapon enum ("Foil","Epee","Saber","Mixed"),
	secondary_weapon enum ("Foil","Epee","Saber","Mixed"),
	year_of_birth year,
	year_started year,
	Education varchar(80) );

create table Action_name (ID serial,
	Name varchar(80));

create table Arousal_level (ID serial,
	level varchar(80));

create table Strip_zone (ID serial,
	zone_name varchar(80));

create table Tournament_type (ID serial,
	type_name varchar(80));

create table Point_format (ID serial, 
	format_name varchar(80));

create table Event_age (ID serial,
	age_name varchar(80));
