CREATE TABLE Clubs (
	short_club_name	VARCHAR(12),
	club_code	VARCHAR(4) NOT NULL,
	long_club_name	VARCHAR(30) NOT NULL,
	club_rep_name	TEXT,
	email_address	TEXT,
	PRIMARY KEY(short_club_name),
	UNIQUE (club_code),
	UNIQUE (long_club_name)
);

CREATE TABLE Sessions (
	session_number	INTEGER,
	date	TEXT NOT NULL,
	start_time	INTEGER,
	PRIMARY KEY(session_number)
);

CREATE TABLE Events (
	event_number	INTEGER,
	distance	INTEGER NOT NULL,
	stroke	TEXT NOT NULL,
	sex	TEXT NOT NULL,
	no_of_swimmers	INTEGER NOT NULL,
	event_type	TEXT NOT NULL,
	no_of_lanes	INTEGER NOT NULL,
	seeding_method	TEXT NOT NULL,
	heat_events_number	INTEGER,
	entry_fee	REAL(5,2) NOT NULL,
	time_interval	INTEGER NOT NULL,
	session_number	INTEGER NOT NULL,
	PRIMARY KEY(event_number),
	FOREIGN KEY(heat_events_number) REFERENCES Events(event_number),
	FOREIGN KEY(session_number) REFERENCES Sessions(session_number)
);

CREATE TABLE Meet (
	licence_number	VARCHAR(30),
	meet_name	TEXT NOT NULL,
	meet_type	TEXT NOT NULL,
	start_date	TEXT NOT NULL,
	end_date	TEXT NOT NULL,
	location	TEXT NOT NULL,
	course	INTEGER NOT NULL,
	PRIMARY KEY(licence_number)
);

CREATE TABLE Qualifying_Times (
	event_number	INTEGER,
	min_age	INTEGER,
	max_age	INTEGER,
	lower_time	INTEGER,
	upper_time	INTEGER,
	PRIMARY KEY(event_number, min_age, max_age),
	FOREIGN KEY(event_number) REFERENCES Events(event_number)
);

CREATE TABLE Swimmers (
	asa_number	INTEGER,
	forename	TEXT NOT NULL,
	surname	TEXT NOT NULL,
	nickname	TEXT,
	dob	TEXT,
	age	INTEGER NOT NULL,
	sex	TEXT NOT NULL,
	short_club_name	VARCHAR(12) NOT NULL,
	disability_status	TEXT,
	PRIMARY KEY(asa_number),
	FOREIGN KEY(short_club_name) REFERENCES Clubs(short_club_name)
);